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

namespace CRMDAL.Query.CallAllocation
{
    public class CallAllocationQB
    {

        public const string GET_CALLALLOCATION = @"SELECT
                                                        CA.CALLALLOCATIONID            AS CallAllocationId,
                                                        CA.BRANDID                     AS BrandId,
                                                        IB.ITEMBRANDCODE               AS BrandCode,
                                                        IB.ITEMBRANDNAME               AS BrandName,

                                                        CA.CATEGORYID                  AS CategoryId,
                                                        IC.ITEMCATEGORYCODE            AS CategoryCode,
                                                        IC.ITEMCATEGORYNAME            AS CategoryName,

                                                        CA.SUBCATEGORYID               AS SubCategoryId,
                                                        ISC.ITEMSUBCATEGORYCODE        AS SubCategoryCode,
                                                        ISC.ITEMSUBCATEGORYNAME        AS SubCategoryName,

                                                        CA.OUID                        AS OUId,
                                                        OU.ORGANIZATIONUNITCODE        AS OUCode,
                                                        OU.ORGANIZATIONUNITNAME        AS OUName,

                                                        CA.COUNTRYID                   AS CountryId,
                                                        CO.COUNTRYCODE                 AS CountryCode,
                                                        CO.COUNTRYNAME                 AS CountryName,

                                                        CA.STATEID                     AS StateId,
                                                        ST.STATECODE                   AS StateCode,
                                                        ST.STATENAME                   AS StateName,

                                                        CA.CITYID                      AS CityId,
                                                        CI.CITYCODE                    AS CityCode,
                                                        CI.CITYNAME                    AS CityName,

                                                        CA.ZIPCODE                     AS CallAllocationZipCode,

                                                        CA.LOCATIONID                  AS LocationId,
                                                        L.LOCATIONCODE                 AS LocationCode,
                                                        L.LOCATIONNAME                 AS LocationName,

                                                        CA.PROBLEMCATEGORYID           AS ProblemCategoryId,
                                                        PC.GCMCODE                     AS ProblemCategoryCode,
                                                        PC.GCMNAME                     AS ProblemCategoryName,

                                                        CA.ALLOTMENTTYPE               AS CallAllocationAllotmentType,

                                                        CA.PARTYID                     AS PartyId,
                                                        P.PARTYCODE                    AS PartyCode,
                                                        P.PARTYNAME                    AS PartyName,

                                                        CA.PARTYBRANCHID               AS PartyBranchId,
                                                        PB.PARTYBRANCHCODE             AS PartyBranchCode,
                                                        PB.PARTYBRANCHNAME             AS PartyBranchName,

                                                        CA.EMPLOYEEID                  AS EmployeeId,
                                                        E.EMPLOYEECODE                 AS EmployeeCode,
                                                        E.EMPLOYEENAME                 AS EmployeeName,

                                                        CA.TECHNICIANID                AS TechnicianId,
                                                        C.NAME                         AS TechnicianName,

                                                        CA.WORKTYPEID                      AS WorkTypeId,
                                                        WT.GCMCODE                     AS WorkTypeCode,
                                                        WT.GCMNAME                     AS WorkTypeName,

                                                        CA.USERGROUPID                 AS UserGroupId,
                                                        UG.USERGROUPNAME               AS UserGroupName,
                                                        UG.USERGROUPCODE               AS UserGroupCode,

                                                        CA.CALLTYPEID                  AS CallTypeId,
                                                        CT.GCMCODE                     AS CallTypeCode,
                                                        CT.GCMNAME                     AS CallTypeName,

                                                        CA.CALLNATUREID                AS CallNatureId,
                                                        CN.GCMCODE                     AS CallNatureCode,
                                                        CN.GCMNAME                     AS CallNatureName,

                                                        CA.RULENAME                    AS RuleName,
                                                        CA.RULEDESCRIPTION             AS Description

                                                    FROM MCALLALLOCATION CA
                                                    LEFT JOIN MITEMBRAND IB ON CA.BRANDID = IB.ITEMBRANDID
                                                    LEFT JOIN MITEMCATEGORY IC ON CA.CATEGORYID = IC.ITEMCATEGORYID
                                                    LEFT JOIN MITEMSUBCATEGORY ISC ON CA.SUBCATEGORYID = ISC.ITEMSUBCATEGORYID
                                                    LEFT JOIN MORGANIZATIONUNIT OU ON CA.OUID = OU.OUID
                                                    LEFT JOIN MCOUNTRY CO ON CA.COUNTRYID = CO.COUNTRYID
                                                    LEFT JOIN MSTATE ST ON CA.STATEID = ST.STATEID
                                                    LEFT JOIN MCITY CI ON CA.CITYID = CI.CITYID
                                                    LEFT JOIN MLOCATION L ON CA.LOCATIONID = L.LOCATIONID
                                                    LEFT JOIN MGCM PC ON CA.PROBLEMCATEGORYID = PC.GCMID
                                                    LEFT JOIN MPARTY P ON CA.PARTYID = P.PARTYID
                                                    LEFT JOIN MPARTYBRANCH PB ON CA.PARTYBRANCHID = PB.PARTYBRANCHID
                                                    LEFT JOIN MEMPLOYEE E ON CA.EMPLOYEEID = E.EMPLOYEEID
                                                    LEFT JOIN MCONTACT C ON CA.TECHNICIANID = C.CONTACTID
                                                    LEFT JOIN MGCM WT ON CA.WORKTYPEID = WT.GCMID
                                                    LEFT JOIN MUSERGROUP UG ON CA.USERGROUPID = UG.USERGROUPID
                                                    LEFT JOIN MGCM CT ON CA.CALLTYPEID = CT.GCMID
                                                    LEFT JOIN MGCM CN ON CA.CALLNATUREID = CN.GCMID
                                                        WHERE CA.CALLALLOCATIONID = @callallocationid;";

        public const string SAVE_CALLALLOCATION = @"INSERT INTO MCALLALLOCATION
                                                    (
                                                        CALLALLOCATIONID,
                                                        BRANDID,
                                                        CATEGORYID,
                                                        SUBCATEGORYID,
                                                        OUID,
                                                        COUNTRYID,
                                                        STATEID,
                                                        CITYID,
                                                        ZIPCODE,
                                                        LOCATIONID,
                                                        PROBLEMCATEGORYID,
                                                        ALLOTMENTTYPE,
                                                        PARTYID,
                                                        PARTYBRANCHID,
                                                        EMPLOYEEID,
                                                        TECHNICIANID,
                                                        WORKTYPEID,
                                                        CALLTYPEID,
                                                        CALLNATUREID,
                                                        USERGROUPID,
                                                        RULENAME,
                                                        RULEDESCRIPTION
                                                    )
                                                    VALUES
                                                    (
                                                        @CallAllocationId,
                                                        @BrandId,
                                                        @CategoryId,
                                                        @SubCategoryId,
                                                        @OUId,
                                                        @CountryId,
                                                        @StateId,
                                                        @CityId,
                                                        @CallAllocationZipCode,
                                                        @LocationId,
                                                        @ProblemCategoryId,
                                                        @CallAllocationAllotmentType,
                                                        @PartyId,
                                                        @PartyBranchId,
                                                        @EmployeeId,
                                                        @TechnicianId,
                                                        @WorkTypeId,
                                                        @CallTypeId,
                                                        @CallNatureId,
                                                        @UserGroupId,
                                                        @RuleName,
                                                        @Description
                                                    );";

        public const string UPDATE_CALLALLOCATION = @"UPDATE MCALLALLOCATION
                                                    SET
                                                        BRANDID = @BrandId,
                                                        CATEGORYID = @CategoryId,
                                                        SUBCATEGORYID = @SubCategoryId,
                                                        OUID = @OUId,
                                                        COUNTRYID = @CountryId,
                                                        STATEID = @StateId,
                                                        CITYID = @CityId,
                                                        ZIPCODE = @CallAllocationZipCode,
                                                        LOCATIONID = @LocationId,
                                                        PROBLEMCATEGORYID = @ProblemCategoryId,
                                                        ALLOTMENTTYPE = @CallAllocationAllotmentType,
                                                        PARTYID = @PartyId,
                                                        PARTYBRANCHID = @PartyBranchId,
                                                        EMPLOYEEID = @EmployeeId,
                                                        TECHNICIANID = @TechnicianId,
                                                        WORKTYPEID = @WorkTypeId,
                                                        CALLTYPEID = @CallTypeId,
                                                        CALLNATUREID = @CallNatureId,
                                                        USERGROUPID = @UserGroupId,
                                                        RULENAME = @RuleName,
                                                        RULEDESCRIPTION = @Description
                                                    WHERE
                                                        CALLALLOCATIONID = @CallAllocationId;";

        public const string GET_SELECTLIST_CALLALLOCATION = @"SELECT
                                                            CA.CALLALLOCATIONID               AS Id,
                                                            CA.RULENAME                       AS RuleName,
                                                            CA.RULEDESCRIPTION                AS Description
                                                    FROM MCALLALLOCATION CA;";

        public const string DELETE_CALLALLOCATION = @"DELETE FROM MCALLALLOCATION
                                                    WHERE CALLALLOCATIONID = @callallocationid;";
    }
}