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

namespace AdminDAL.Query.OrganizationGroup
{
    public static class OrganizationGroupQB
    {
        public const string GetSelectList_OrganizationGroup = @"
  SELECT
og.ORGANIZATIONGROUPID   AS Id,
og.ORGANIZATIONGROUPCODE AS Code,
og.ORGANIZATIONGROUPNAME AS Name
FROM MORGANIZATIONGROUP og;";


        public const string GET_ORGANIZATIONGROUP = @"SELECT
    OG.ORGANIZATIONGROUPID              AS OrganizationGroupId,
    OG.ORGANIZATIONGROUPCODE            AS OrganizationGroupCode,
    OG.ORGANIZATIONGROUPNAME            AS OrganizationGroupName,
    OG.SORTORDER                        AS OrganizationGroupSortOrder,
    OG.STATUS                           AS OrganizationGroupStatus,
    OG.VERSION                          AS OrganizationGroupVersion,
    OG.CREATEDBYID                      AS OrganizationGroupCreatedById,
    CB.USERNAME                         AS OrganizationGroupCreatedByName,
    OG.CREATEDON                        AS OrganizationGroupCreatedOn,
    OG.MODIFIEDBYID                     AS OrganizationGroupModifiedById,
    MB.USERNAME                         AS OrganizationGroupModifiedByName,
    OG.MODIFIEDON                       AS OrganizationGroupModifiedOn,
    OGD.ORGANIZATIONGROUPDETAILID       AS OrganizationGroupDetailId,
    OGD.ORGANIZATIONGROUPID             AS OrganizationGroupId,
    OG.ORGANIZATIONGROUPCODE            AS OrganizationGroupCode,
    OG.ORGANIZATIONGROUPNAME            AS OrganizationGroupName,
    OGD.SLNO                            AS OrganizationGroupDetailSlNo,
    OGD.OPERATIONTYPE                   AS OrganizationGroupDetailOperationType,
    OGD.GROUPTYPE                       AS OrganizationGroupDetailGroupType,
    OGD.OUID                            AS OrganizationUnitId,
    OU.ORGANIZATIONUNITCODE             AS OrganizationUnitCode,
    OU.ORGANIZATIONUNITNAME             AS OrganizationUnitName,
    OU.ORGANIZATIONUNITSHORTNAME        AS OrganizationUnitShortName,
    OGD.COMPANYID                       AS CompanyId,
    C.COMPANYCODE                       AS CompanyCode,
    C.COMPANYNAME                       AS CompanyName,
    C.COMPANYSHORTNAME                  AS CompanyShortName,
    OGD.BRANCHID                        AS BranchId,
    B.BRANCHCODE                        AS BranchCode,
    B.BRANCHNAME                        AS BranchName,
    B.BRANCHSHORTNAME                   AS BranchShortName,
    OGD.DIVISIONID                      AS DivisionId,
    D.DIVISIONCODE                      AS DivisionCode,
    D.DIVISIONNAME                      AS DivisionName,
    D.DIVISIONSHORTNAME                 AS DivisionShortName,
    OGD.ORGANISATIONGROUPPARENTID       AS OrganizationGroupParentId,
    POG.ORGANIZATIONGROUPCODE           AS OrganizationGroupParentCode,
    POG.ORGANIZATIONGROUPNAME           AS OrganizationGroupParentName

FROM MORGANIZATIONGROUP OG
LEFT JOIN MORGANIZATIONGROUPDETAIL OGD ON OG.ORGANIZATIONGROUPID = OGD.ORGANIZATIONGROUPID
LEFT JOIN MORGANIZATIONUNIT OU ON OGD.OUID = OU.OUID
LEFT JOIN MCOMPANY C ON OGD.COMPANYID = C.COMPANYID
LEFT JOIN MBRANCH B ON OGD.BRANCHID = B.BRANCHID
LEFT JOIN MDIVISION D ON OGD.DIVISIONID = D.DIVISIONID
LEFT JOIN MORGANIZATIONGROUP POG ON OGD.ORGANISATIONGROUPPARENTID = POG.ORGANIZATIONGROUPID
LEFT JOIN MUSER CB ON OG.CREATEDBYID = CB.USERID
LEFT JOIN MUSER MB ON OG.MODIFIEDBYID = MB.USERID
WHERE OG.ORGANIZATIONGROUPID = @organizationgroupid
";

        public const string DELETE_ORGANIZATIONGROUP = @"DELETE FROM MORGANIZATIONGROUP
WHERE ORGANIZATIONGROUPID = @OrganizationGroupId;
";

        public const string SAVE_ORGANIZATIONGROUP = @"
INSERT INTO MORGANIZATIONGROUP (
    ORGANIZATIONGROUPID,
    ORGANIZATIONGROUPCODE,
    ORGANIZATIONGROUPNAME,
    SORTORDER,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
) VALUES (
    @OrganizationGroupId,
    @OrganizationGroupCode,
    @OrganizationGroupName,
    @OrganizationGroupSortOrder,
    @OrganizationGroupStatus,
    @OrganizationGroupVersion,
    @OrganizationGroupCreatedById,
    @OrganizationGroupCreatedOn,
    @OrganizationGroupModifiedById,
    @OrganizationGroupModifiedOn
);";

        public const string UPDATE_ORGANIZATIONGROUP = @"
UPDATE MORGANIZATIONGROUP SET
    ORGANIZATIONGROUPCODE = @OrganizationGroupCode,
    ORGANIZATIONGROUPNAME = @OrganizationGroupName,
    SORTORDER             = @OrganizationGroupSortOrder,
    STATUS                = @OrganizationGroupStatus,
    VERSION               = @OrganizationGroupVersion,
    MODIFIEDBYID          = @OrganizationGroupModifiedById,
    MODIFIEDON            = @OrganizationGroupModifiedOn
WHERE ORGANIZATIONGROUPID = @OrganizationGroupId;";

        public const string DELETE_ORGANIZATIONGROUP_DETAILS = @"
DELETE FROM MORGANIZATIONGROUPDETAIL
WHERE ORGANIZATIONGROUPID = @OrganizationGroupId;";

        public const string SAVE_ORGANIZATIONGROUP_DETAIL = @"
INSERT INTO MORGANIZATIONGROUPDETAIL (
    ORGANIZATIONGROUPDETAILID,
    ORGANIZATIONGROUPID,
    SLNO,
    OPERATIONTYPE,
    GROUPTYPE,
    OUID,
    COMPANYID,
    BRANCHID,
    DIVISIONID,
    ORGANISATIONGROUPPARENTID
) VALUES (
    @OrganizationGroupDetailId,
    @OrganizationGroupId,
    @OrganizationGroupDetailSlNo,
    @OrganizationGroupDetailOperationType,
    @OrganizationGroupDetailGroupType,
    @OrganizationUnitId,
    @CompanyId,
    @BranchId,
    @DivisionId,
    @OrganizationGroupParentId
);";

        public const string GET_OU_DETAILS_FROM_OUGROUP = @"
DECLARE @Skip INT = CASE WHEN @FirstNumber < 0 THEN 0 ELSE @FirstNumber END;
DECLARE @Take INT = CASE WHEN @MaxResult < 0 THEN 2147483647 ELSE @MaxResult END;

SELECT
    OGD.ORGANIZATIONGROUPDETAILID   AS OrganizationGroupDetailId,
    OGD.ORGANIZATIONGROUPID         AS OrganizationGroupId,
    OG.ORGANIZATIONGROUPCODE        AS OrganizationGroupCode,
    OG.ORGANIZATIONGROUPNAME        AS OrganizationGroupName,
    OGD.SLNO                        AS OrganizationGroupDetailSlNo,
    OGD.OPERATIONTYPE               AS OrganizationGroupDetailOperationType,
    OGD.GROUPTYPE                   AS OrganizationGroupDetailGroupType,
    OGD.OUID                        AS OrganizationUnitId,
    OU.ORGANIZATIONUNITCODE         AS OrganizationUnitCode,
    OU.ORGANIZATIONUNITNAME         AS OrganizationUnitName,
    OU.ORGANIZATIONUNITSHORTNAME    AS OrganizationUnitShortName,
    OGD.COMPANYID                   AS CompanyId,
    C.COMPANYCODE                   AS CompanyCode,
    C.COMPANYNAME                   AS CompanyName,
    C.COMPANYSHORTNAME              AS CompanyShortName,
    OGD.BRANCHID                    AS BranchId,
    B.BRANCHCODE                    AS BranchCode,
    B.BRANCHNAME                    AS BranchName,
    B.BRANCHSHORTNAME               AS BranchShortName,
    OGD.DIVISIONID                  AS DivisionId,
    D.DIVISIONCODE                  AS DivisionCode,
    D.DIVISIONNAME                  AS DivisionName,
    D.DIVISIONSHORTNAME             AS DivisionShortName,
    OGD.ORGANISATIONGROUPPARENTID   AS OrganizationGroupParentId,
    POG.ORGANIZATIONGROUPCODE       AS OrganizationGroupParentCode,
    POG.ORGANIZATIONGROUPNAME       AS OrganizationGroupParentName
FROM MORGANIZATIONGROUPDETAIL OGD
LEFT JOIN MORGANIZATIONGROUP OG     ON OGD.ORGANIZATIONGROUPID = OG.ORGANIZATIONGROUPID
LEFT JOIN MORGANIZATIONUNIT OU      ON OGD.OUID = OU.OUID
LEFT JOIN MCOMPANY C                ON OGD.COMPANYID = C.COMPANYID
LEFT JOIN MBRANCH B                 ON OGD.BRANCHID = B.BRANCHID
LEFT JOIN MDIVISION D               ON OGD.DIVISIONID = D.DIVISIONID
LEFT JOIN MORGANIZATIONGROUP POG    ON OGD.ORGANISATIONGROUPPARENTID = POG.ORGANIZATIONGROUPID
WHERE OGD.ORGANIZATIONGROUPID = @OrganizationGroupId
ORDER BY OGD.SLNO
OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;";
    }
}