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

namespace AdminDAL.Query.OrganizationUnit
{
    public static class OrganizationUnitQB
    {
        public const string GET_ORGANIZATION_UNIT = @"
SELECT
    OU.OUID     AS OrganizationUnitId,
    OU.ORGANIZATIONUNITCODE   AS OrganizationUnitCode,
    OU.ORGANIZATIONUNITNAME   AS OrganizationUnitName,
    OU.ORGANIZATIONUNITSHORTNAME AS OrganizationUnitShortName,

    OU.COMPANYID              AS CompanyId,
    C.COMPANYCODE             AS CompanyCode,
    C.COMPANYNAME             AS CompanyName,
    C.COMPANYSHORTNAME        AS CompanyShortName,

    OU.BRANCHID               AS BranchId,
    B.BRANCHCODE              AS BranchCode,
    B.BRANCHNAME              AS BranchName,
    B.BRANCHSHORTNAME         AS BranchShortName,

    OU.DIVISIONID             AS DivisionId,
    D.DIVISIONCODE            AS DivisionCode,
    D.DIVISIONNAME            AS DivisionName,
    D.DIVISIONSHORTNAME       AS DivisionShortName,

    OU.OVERLOCKDATE           AS OrganizationUnitOverLockDate,

    OU.CURRENCYID             AS CurrencyId,
    CUR.CURRENCYCODE          AS CurrencyCode,
    CUR.CURRENCYNAME          AS CurrencyName,

    OU.COLORID                AS ColorId,
    COL.COLORCODE             AS ColorCode,
    COL.COLORNAME             AS ColorName,

    OU.CALENDARID             AS CalendarId,
    CAL.CALENDARCODE          AS CalendarCode,
    CAL.CALENDARNAME          AS CalendarName,

    OU.OULOGO1                AS OrganizationUnitLogo1,
    OU.OULOGO2                AS OrganizationUnitLogo2,
    OU.DISPLAYNAME            AS OrganizationUnitDisplayName,
    OU.DEFAULTADDRESSID       AS DefaultAddressId,

    OU.CREATEDBYID            AS OrganizationUnitCreatedById,
    CB.EMPLOYEENAME           AS OrganizationUnitCreatedByName,
    OU.CREATEDON              AS OrganizationUnitCreatedOn,

    OU.MODIFIEDBYID           AS OrganizationUnitModifiedById,
    MB.EMPLOYEENAME           AS OrganizationUnitModifiedByName,
    OU.MODIFIEDON             AS OrganizationUnitModifiedOn,

    OU.SORTORDER              AS OrganizationUnitSortOrder,
    OU.STATUS                 AS OrganizationUnitStatus,
    OU.VERSION                AS OrganizationUnitVersion,
    OU.SOURCETYPE             AS OrganizationUnitSourceType,

    OU.PERIODGROUP            AS PeriodGroup

    --addon.ORGANIZATIONUNITID AS Id,
    --addon.DIRECTORNAME       AS DIRECTORNAME,
    --addon.DIRECTORDESIGNATION AS DIRECTORDESIGNATION,
    --addon.OUTAMILNAME        AS OUTAMILNAME

FROM MORGANIZATIONUNIT OU
LEFT JOIN MCOMPANY C      ON OU.COMPANYID = C.COMPANYID
LEFT JOIN MBRANCH B       ON OU.BRANCHID = B.BRANCHID
LEFT JOIN MDIVISION D     ON OU.DIVISIONID = D.DIVISIONID
LEFT JOIN MCURRENCY CUR   ON OU.CURRENCYID = CUR.CURRENCYID
LEFT JOIN MCOLOR COL      ON OU.COLORID = COL.COLORID
LEFT JOIN MCALENDAR CAL   ON OU.CALENDARID = CAL.CALENDARID
LEFT JOIN MEMPLOYEE CB    ON OU.CREATEDBYID = CB.EMPLOYEEID
LEFT JOIN MEMPLOYEE MB    ON OU.MODIFIEDBYID = MB.EMPLOYEEID
--LEFT JOIN MORGANIZATIONUNITADDON addon ON OU.OUID = addon.OrganizationUnitId
WHERE OU.OUID = @OrganizationUnitId;
";


        public const string GetSelectList_OrganizationUnit = @"
  SELECT
ou.OUID   AS Id,
ou.ORGANIZATIONUNITCODE AS Code,
ou.ORGANIZATIONUNITNAME AS Name,
ou.ORGANIZATIONUNITSHORTNAME AS ShortName
FROM MORGANIZATIONUNIT ou, MUSER us
where 1=1
and ou.OUID=us.WORKOUID
and us.USERID= @userid";

        public const string GetSelectList_OrganizationUnit_ByUserAccessRights = @" 
              SELECT
            ou.OUID   AS Id,
            ou.ORGANIZATIONUNITCODE AS Code,
            ou.ORGANIZATIONUNITNAME AS Name,
            ou.ORGANIZATIONUNITSHORTNAME AS ShortName
            FROM MORGANIZATIONUNIT ou, MUSERACCESSRIGHTS us
            where 1=1
            and ou.OUID=us.OUID
            and us.USERID= @userid
            ";


        public const string DELETE_ORGANIZATION_UNIT = @"BEGIN TRY
    BEGIN TRAN;

    EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';

    DELETE FROM MORGANIZATIONUNITADDON
    WHERE ORGANIZATIONUNITID = @OrganizationUnitId;

    DELETE FROM MORGANIZATIONUNIT
    WHERE OUID = @OrganizationUnitId;

    EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL';

    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL';
    THROW;
END CATCH;

";

        public const string SAVE_ORGANIZATION_UNIT = @"
INSERT INTO MORGANIZATIONUNIT (
    OUID,
    ORGANIZATIONUNITCODE,
    ORGANIZATIONUNITNAME,
    ORGANIZATIONUNITSHORTNAME,
    COMPANYID,
    BRANCHID,
    DIVISIONID,
    OVERLOCKDATE,
    CURRENCYID,
    COLORID,
    CALENDARID,
    OULOGO1,
    OULOGO2,
    DISPLAYNAME,
    DEFAULTADDRESSID,
    SORTORDER,
    STATUS,
    VERSION,
    SOURCETYPE,
    PERIODGROUP,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
) VALUES (
    @OrganizationUnitId,
    @OrganizationUnitCode,
    @OrganizationUnitName,
    @OrganizationUnitShortName,
    @CompanyId,
    @BranchId,
    @DivisionId,
    @OrganizationUnitOverLockDate,
    @CurrencyId,
    @ColorId,
    @CalendarId,
    @OrganizationUnitLogo1,
    @OrganizationUnitLogo2,
    @OrganizationUnitDisplayName,
    @DefaultAddressId,
    @OrganizationUnitSortOrder,
    @OrganizationUnitStatus,
    @OrganizationUnitVersion,
    @OrganizationUnitSourceType,
    @PeriodGroup,
    @OrganizationUnitCreatedById,
    @OrganizationUnitCreatedOn,
    @OrganizationUnitModifiedById,
    @OrganizationUnitModifiedOn
);";

        public const string UPDATE_ORGANIZATION_UNIT = @"
UPDATE MORGANIZATIONUNIT SET
    ORGANIZATIONUNITCODE      = @OrganizationUnitCode,
    ORGANIZATIONUNITNAME      = @OrganizationUnitName,
    ORGANIZATIONUNITSHORTNAME = @OrganizationUnitShortName,
    COMPANYID                 = @CompanyId,
    BRANCHID                  = @BranchId,
    DIVISIONID                = @DivisionId,
    OVERLOCKDATE              = @OrganizationUnitOverLockDate,
    CURRENCYID                = @CurrencyId,
    COLORID                   = @ColorId,
    CALENDARID                = @CalendarId,
    OULOGO1                   = @OrganizationUnitLogo1,
    OULOGO2                   = @OrganizationUnitLogo2,
    DISPLAYNAME               = @OrganizationUnitDisplayName,
    DEFAULTADDRESSID          = @DefaultAddressId,
    SORTORDER                 = @OrganizationUnitSortOrder,
    STATUS                    = @OrganizationUnitStatus,
    VERSION                   = @OrganizationUnitVersion,
    SOURCETYPE                = @OrganizationUnitSourceType,
    PERIODGROUP               = @PeriodGroup,
    MODIFIEDBYID              = @OrganizationUnitModifiedById,
    MODIFIEDON                = @OrganizationUnitModifiedOn
WHERE OUID = @OrganizationUnitId;";

        public const string GET_OU_CITY = @"
SELECT
    OU.OUID                     AS OrganizationUnitId,
    OU.ORGANIZATIONUNITCODE     AS OrganizationUnitCode,
    OU.ORGANIZATIONUNITNAME     AS OrganizationUnitName,
    A.ADDRESSID                 AS AddressId,
    A.ADDRESSLINE1              AS AddressLine1,
    A.ADDRESSLINE2              AS AddressLine2,
    A.ADDRESSLINE3              AS AddressLine3,
    A.CITYID                    AS CityId,
    CI.CITYNAME                 AS CityName,
    CI.CITYCODE                 AS CityCode,
    A.STATEID                   AS StateId,
    S.STATENAME                 AS StateName,
    S.STATECODE                 AS StateCode,
    A.COUNTRYID                 AS CountryId,
    CO.COUNTRYNAME              AS CountryName,
    A.PINCODE                   AS PinCode
FROM MORGANIZATIONUNIT OU
LEFT JOIN MADDRESS A    ON OU.DEFAULTADDRESSID = A.ADDRESSID
LEFT JOIN MCITY CI      ON A.CITYID = CI.CITYID
LEFT JOIN MSTATE S      ON A.STATEID = S.STATEID
LEFT JOIN MCOUNTRY CO   ON A.COUNTRYID = CO.COUNTRYID
WHERE OU.OUID = @OUId;";

        public const string GET_SELECT_LIST_DELIVERY_ORGANIZATION_UNIT = @"
SELECT
    ou.OUID                     AS Id,
    ou.ORGANIZATIONUNITCODE     AS Code,
    ou.ORGANIZATIONUNITNAME     AS Name,
    ou.ORGANIZATIONUNITSHORTNAME AS ShortName,
    ou.PERIODGROUP              AS PeriodGroup
FROM MORGANIZATIONUNIT ou
ORDER BY ou.ORGANIZATIONUNITNAME
OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY;";
    }
}