using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AccountsDAL.Query.CostCenterType
{
    public class CostCenterTypeQB
    {
        public const string GET_COSTCENTERTYPE = @"SELECT
                                                    CCT.COSTCENTERTYPEID              AS CostCenterTypeId,
                                                    CCT.COSTCENTERTYPECODE            AS CostCenterTypeCode,
                                                    CCT.COSTCENTERTYPENAME            AS CostCenterTypeName,
                                                    CCT.TYPE                          AS CostCenterTypeType,
                                                    CCT.ISHIERACHICALAPPLICABLE       AS CostCenterTypeIsHierachicalApplicable,
                                                    CCT.OUID                          AS OrganizationUnitId,
                                                    OU.ORGANIZATIONUNITCODE           AS OrganizationUnitCode,
                                                    OU.ORGANIZATIONUNITNAME           AS OrganizationUnitName,
                                                    CCT.ISCODEREQUIRED                AS CostCenterTypeIsCodeRequired,
                                                    CCT.SORTORDER                     AS CostCenterTypeSortOrder,
                                                    CCT.STATUS                        AS CostCenterTypeStatus,
                                                    CCT.VERSION                       AS CostCenterTypeVersion,
                                                    CCT.CREATEDBYID                   AS CostCenterTypeCreatedById,
                                                    CCT.CREATEDON                     AS CostCenterTypeCreatedOn,
                                                    CCT.MODIFIEDBYID                  AS CostCenterTypeModifiedById,
                                                    CCT.MODIFIEDON                    AS CostCenterTypeModifiedOn,
                                                    CCT.SOURCETYPE                    AS CostCenterTypeSourceType,
                                                    CCT.TENANTID                      AS CostCenterTypeTenantId
                                                FROM MCOSTCENTERTYPE CCT
                                                LEFT JOIN MORGANIZATIONUNIT OU ON CCT.OUID = OU.OUID
                                                WHERE CCT.COSTCENTERTYPEID =@costcentertypeid;";

        // Full-DTO cost center type list — same column set/joins as GET_COSTCENTERTYPE (returns
        // CostCenterTypeDTO for every matching row instead of one), criteria-filtered like
        // AccountQB.GET_ACCOUNT_LIST rather than paged (no FirstNumber/MaxResult, whole-CriteriaDTO
        // List call, not a SelectList).
        public const string GET_COSTCENTERTYPE_LIST = @"SELECT
                                                    CCT.COSTCENTERTYPEID              AS CostCenterTypeId,
                                                    CCT.COSTCENTERTYPECODE            AS CostCenterTypeCode,
                                                    CCT.COSTCENTERTYPENAME            AS CostCenterTypeName,
                                                    CCT.TYPE                          AS CostCenterTypeType,
                                                    CCT.ISHIERACHICALAPPLICABLE       AS CostCenterTypeIsHierachicalApplicable,
                                                    CCT.OUID                          AS OrganizationUnitId,
                                                    OU.ORGANIZATIONUNITCODE           AS OrganizationUnitCode,
                                                    OU.ORGANIZATIONUNITNAME           AS OrganizationUnitName,
                                                    CCT.ISCODEREQUIRED                AS CostCenterTypeIsCodeRequired,
                                                    CCT.SORTORDER                     AS CostCenterTypeSortOrder,
                                                    CCT.STATUS                        AS CostCenterTypeStatus,
                                                    CCT.VERSION                       AS CostCenterTypeVersion,
                                                    CCT.CREATEDBYID                   AS CostCenterTypeCreatedById,
                                                    CCT.CREATEDON                     AS CostCenterTypeCreatedOn,
                                                    CCT.MODIFIEDBYID                  AS CostCenterTypeModifiedById,
                                                    CCT.MODIFIEDON                    AS CostCenterTypeModifiedOn,
                                                    CCT.SOURCETYPE                    AS CostCenterTypeSourceType,
                                                    CCT.TENANTID                      AS CostCenterTypeTenantId
                                                FROM MCOSTCENTERTYPE CCT
                                                LEFT JOIN MORGANIZATIONUNIT OU ON CCT.OUID = OU.OUID
                                                WHERE
                                                    (@SearchText IS NULL OR @SearchText = ''
                                                     OR CCT.COSTCENTERTYPECODE LIKE '%' + @SearchText + '%')
                                                    AND (@StatusEquals IS NULL OR CCT.STATUS = @StatusEquals)
                                                    AND (@StatusNotEquals IS NULL OR CCT.STATUS <> @StatusNotEquals)
                                                ORDER BY CCT.COSTCENTERTYPECODE;";

        public const string SAVE_COSTCENTERTYPE = @"INSERT INTO MCOSTCENTERTYPE
                                                        (
                                                            COSTCENTERTYPEID,
                                                            COSTCENTERTYPECODE,
                                                            COSTCENTERTYPENAME,
                                                            TYPE,
                                                            ISHIERACHICALAPPLICABLE,
                                                            OUID,
                                                            ISCODEREQUIRED,
                                                            SORTORDER,
                                                            STATUS,
                                                            VERSION,
                                                            CREATEDBYID,  
                                                            CREATEDON,
                                                            MODIFIEDBYID,
                                                            MODIFIEDON
                                                        )
                                                        VALUES
                                                        (
                                                            @CostCenterTypeId,          
                                                            @CostCenterTypeCode,
                                                            @CostCenterTypeName,
                                                            @CostCenterTypeType,
                                                            @CostCenterTypeIsHierachicalApplicable,
                                                            @OrganizationUnitId,
                                                            @CostCenterTypeIsCodeRequired,
                                                            @CostCenterTypeSortOrder,
                                                            @CostCenterTypeStatus,
                                                            @CostCenterTypeVersion,
                                                            @CostCenterTypeCreatedById,
                                                            @CostCenterTypeCreatedOn,
                                                            @CostCenterTypeModifiedById,
                                                            @CostCenterTypeModifiedOn
                                                        );";

        public const string UPDATE_COSTCENTERTYPE = @"UPDATE MCOSTCENTERTYPE
                                                    SET
                                                        COSTCENTERTYPECODE        = @CostCenterTypeCode,
                                                        COSTCENTERTYPENAME        = @CostCenterTypeName,
                                                        TYPE                      = @CostCenterTypeType,
                                                        ISHIERACHICALAPPLICABLE   = @CostCenterTypeIsHierachicalApplicable,
                                                        OUID                      = @OrganizationUnitId,
                                                        ISCODEREQUIRED            = @CostCenterTypeIsCodeRequired,
                                                        SORTORDER                 = @CostCenterTypeSortOrder,
                                                        STATUS                    = @CostCenterTypeStatus,
                                                        VERSION                   = @CostCenterTypeVersion,
                                                        MODIFIEDBYID              = @CostCenterTypeModifiedById,
                                                        MODIFIEDON                = @CostCenterTypeModifiedOn
                                                    WHERE
                                                        COSTCENTERTYPEID = @CostCenterTypeId;";

        public const string DELETE_COSTCENTERTYPE = @"DELETE FROM MCOSTCENTERTYPE
                                                        WHERE COSTCENTERTYPEID = @costcentertypeid;";

        public const string GET_SELECTLIST_COSTCENTERTYPE = @"SELECT
                                                            CCT.COSTCENTERTYPEID           AS Id,
                                                            CCT.COSTCENTERTYPECODE         AS Code,
                                                            CCT.COSTCENTERTYPENAME         AS Name,
                                                            ROW_NUMBER() OVER (ORDER BY CCT.COSTCENTERTYPENAME) AS RowNum
                                                        FROM 
                                                            MCOSTCENTERTYPE CCT;";
    }
}