using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace AdminDAL.Query.Allocation { public class AllocationQB { public const string GET_ALLOCATION = @"SELECT -- Parent: MALLOCATION (Allocation) -- =================================== A.ALLOCATIONID AS MAllocationId, A.ALLOCATIONNAME AS MAllocationName, A.ALLOCATIONSHORTNAME AS MAllocationShortName, A.DESCRIPTION AS MAllocationDescription, A.ISCAPITAL AS MAllocationIsCapital, A.FROMDATE AS MAllocationFromDate, A.TODATE AS MAllocationToDate, A.OBJECTID AS MAllocationObjectId, A.PARENTALLOCATIONID AS ParentAllocationId, PA.ALLOCATIONNAME AS ParentAllocationName, PA.ALLOCATIONSHORTNAME AS ParentAllocationShortName, A.OBJECTTYPEID AS ObjectTypeId, E.ENTITYNAME AS ObjectTypeName, E.ENTITYCODE AS ObjectTypeCode, A.INCHARGEID AS InchargeId, IU.USERNAME AS InchargeName, IU.USERCODE AS InchargeCode, IU.PRIMARYMAIL AS InchargePrimaryMail, A.ALLOCATIONTYPEID AS AllocationTypeId, AT.ALLOCATIONTYPENAME AS AllocationTypeName, AT.ALLOCATIONTYPECODE AS AllocationTypeCode, A.COSTCENTERID AS MAllocationCostCenterId, A.PARTYID AS MAllocationPartyId, P.PARTYNAME AS MAllocationPartyName, P.PARTYCODE AS MAllocationPartyCode, A.SORTORDER AS MAllocationSortOrder, A.STATUS AS MAllocationStatus, A.VERSION AS MAllocationVersion, A.CREATEDBYID AS MAllocationCreatedById, A.CREATEDON AS MAllocationCreatedOn, CU.USERNAME AS MAllocationCreatedByName, A.MODIFIEDBYID AS MAllocationModifiedById, A.MODIFIEDON AS MAllocationModifiedOn, MU.USERNAME AS MAllocationModifiedByName, A.RATE AS MAllocationRate, A.CURRENCYID AS CurrencyId, CR.CURRENCYNAME AS CurrencyName, CR.CURRENCYCODE AS CurrencyCode, A.NUMBEROFDATA AS MAllocationNumberOfData, A.EXPECTEDPERHOUR AS MAllocationExpectedPerHour, A.RATEPERUOM AS MAllocationRateperUOM, A.INCHARGEID AS InchargeId, IU.USERNAME AS InchargeName, IU.USERCODE AS InchargeCode, A.OUID AS OrganizationUnitId, OU.ORGANIZATIONUNITNAME AS OrganizationUnitName, OU.ORGANIZATIONUNITCODE AS OrganizationUnitCode, A.PARTYBRANCHID AS PartyBranchId, PB.PARTYBRANCHNAME AS PartyBranchName, PB.PARTYBRANCHCODE AS PartyBranchCode, A.REFERENCEPARTYBRANCHID AS ReferencePartyBranchId, RPB.PARTYBRANCHNAME AS ReferencePartyBranchName, RPB.PARTYBRANCHCODE AS ReferencePartyBranchCode, P.PARTYNAME AS ReferencePartyName, A.BROKERPARTYBRANCHID AS BrokerPartyBranchId, BPB.PARTYBRANCHNAME AS BrokerPartyBranchName, BPB.PARTYBRANCHCODE AS BrokerPartyBranchCode, P.PARTYNAME AS BrokerPartyName, A.INCHARGEEMPLOYEEID AS InchargeEmployeeId, IE.EMPLOYEENAME AS InchargeEmployeeName, IE.EMPLOYEECODE AS InchargeEmployeeCode, A.PORTFOLIOID AS PortFolioId, PF.PORTFOLIONAME AS PortFolioName, PF.PORTFOLIOCODE AS PortFolioCode, A.PMSTATUS AS MAllocationPMStatus, A.WORKLEVELTYPE AS MAllocationWorkLevelType, A.PROJECTVALUE AS MAllocationProjectValue, A.PROJECTGCVALUE AS MAllocationProjectGCValue, A.PROJECTBUDGET AS MAllocationProjectBudget, A.PROJECTGCBUDGET AS MAllocationProjectGCBudget, A.BILLTYPEID AS BillTypeId, BT.GCMNAME AS BillTypeName, BT.GCMCODE AS BillTypeCode, A.BILLMETHODID AS BillMethodId, BM.GCMNAME AS BillMethodName, BM.GCMCODE AS BillMethodCode, A.REMARKS AS MAllocationRemarks, ISNULL(DV1.BIZDIMENSIONVALUEID,-1) AS L1Id, ISNULL(DV1.BIZDIMENSIONVALUECODE,'NONE') AS L1Code, ISNULL(DV1.BIZDIMENSIONVALUENAME,'NONE') AS L1Name, ISNULL(DV2.BIZDIMENSIONVALUEID,-1) AS L2Id, ISNULL(DV2.BIZDIMENSIONVALUECODE,'NONE') AS L2Code, ISNULL(DV2.BIZDIMENSIONVALUENAME,'NONE') AS L2Name, ISNULL(DV3.BIZDIMENSIONVALUEID,-1) AS L3Id, ISNULL(DV3.BIZDIMENSIONVALUECODE,'NONE') AS L3Code, ISNULL(DV3.BIZDIMENSIONVALUENAME,'NONE') AS L3Name, ISNULL(DV4.BIZDIMENSIONVALUEID,-1) AS L4Id, ISNULL(DV4.BIZDIMENSIONVALUECODE,'NONE') AS L4Code, ISNULL(DV4.BIZDIMENSIONVALUENAME,'NONE') AS L4Name, ISNULL(DV5.BIZDIMENSIONVALUEID,-1) AS L5Id, ISNULL(DV5.BIZDIMENSIONVALUECODE,'NONE') AS L5Code, ISNULL(DV5.BIZDIMENSIONVALUENAME,'NONE') AS L5Name -- Child: CONTACT (ContactDTO) -- =========================== --C.CONTACTID AS ContactId, --C.OBJECTTYPEID AS ObjectTypeId, --C.OBJECTID AS ContactObjectId, --C.NAME AS ContactName, --C.FIRSTNAME AS ContactFirstName, --C.MIDDLENAME AS ContactMiddleName, --C.LASTNAME AS ContactLastName, --C.MOBILENO AS ContactMobileNo, --C.MAILID AS ContactMailId, --C.DESIGNATION AS ContactDesignation, --C.COMPANY AS ContactCompany, --C.RELATIONSHIP AS ContactRelationShip, --C.STATUS AS ContactStatus, --C.CREATEDBYID AS ContactCreatedById, --C.CREATEDON AS ContactCreatedOn, --C.MODIFIEDBYID AS ContactModifiedById, --C.MODIFIEDON AS ContactModifiedOn FROM MALLOCATION A LEFT JOIN MALLOCATION PA ON PA.ALLOCATIONID = A.PARENTALLOCATIONID LEFT JOIN MENTITY E ON E.ENTITYID = A.OBJECTTYPEID LEFT JOIN MCOSTCENTER CC ON CC.COSTCENTERID = A.COSTCENTERID LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = A.OUID LEFT JOIN MPARTY P ON P.PARTYID = A.PARTYID LEFT JOIN MPARTYBRANCH PB ON PB.PARTYBRANCHID = A.PARTYBRANCHID LEFT JOIN MPARTYBRANCH RPB ON RPB.PARTYBRANCHID = A.REFERENCEPARTYBRANCHID LEFT JOIN MPARTYBRANCH BPB ON BPB.PARTYBRANCHID = A.BROKERPARTYBRANCHID LEFT JOIN MUSER IU ON IU.USERID = A.INCHARGEID LEFT JOIN MEMPLOYEE IE ON IE.EMPLOYEEID = A.INCHARGEEMPLOYEEID LEFT JOIN MALLOCATIONPORTFOLIO PF ON PF.PORTFOLIOID = A.PORTFOLIOID LEFT JOIN MALLOCATIONTYPE AT ON AT.ALLOCATIONTYPEID = A.ALLOCATIONTYPEID LEFT JOIN MCURRENCY CR ON CR.CURRENCYID = A.CURRENCYID LEFT JOIN MGCM BT ON BT.GCMID = A.BILLTYPEID LEFT JOIN MGCM BM ON BM.GCMID = A.BILLMETHODID LEFT JOIN MUSER CU ON CU.USERID = A.CREATEDBYID LEFT JOIN MUSER MU ON MU.USERID = A.MODIFIEDBYID LEFT JOIN TBIZDIMENSION TD ON TD.OBJECTTYPEID = A.OBJECTTYPEID AND TD.OBJECTID = A.ALLOCATIONID LEFT JOIN MBIZDIMENSIONVALUESET VS ON VS.BIZDIMENSIONVALUESETID = TD.BIZDIMENSIONVALUESETID LEFT JOIN MBIZDIMENSIONVALUE DV1 ON DV1.BIZDIMENSIONVALUEID = VS.V1 LEFT JOIN MBIZDIMENSIONVALUE DV2 ON DV2.BIZDIMENSIONVALUEID = VS.V2 LEFT JOIN MBIZDIMENSIONVALUE DV3 ON DV3.BIZDIMENSIONVALUEID = VS.V3 LEFT JOIN MBIZDIMENSIONVALUE DV4 ON DV4.BIZDIMENSIONVALUEID = VS.V4 LEFT JOIN MBIZDIMENSIONVALUE DV5 ON DV5.BIZDIMENSIONVALUEID = VS.V5 --CHILD JOIN LEFT JOIN MCONTACT C ON C.OBJECTID = A.ALLOCATIONID AND C.OBJECTTYPEID = A.OBJECTTYPEID WHERE A.ALLOCATIONID = @mallocationid;"; public const string SAVE_ALLOCATION = @"INSERT INTO MALLOCATION ( ALLOCATIONID, ALLOCATIONNAME, ALLOCATIONSHORTNAME, ALLOCATIONTYPEID, DESCRIPTION, INCHARGEID, ISCAPITAL, FROMDATE, TODATE, PARENTALLOCATIONID, OBJECTTYPEID, OBJECTID, COSTCENTERID, OUID, PARTYID, SORTORDER, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID, RATE, CURRENCYID, NUMBEROFDATA, EXPECTEDPERHOUR, RATEPERUOM, PARTYBRANCHID, REFERENCEPARTYBRANCHID, BROKERPARTYBRANCHID, INCHARGEEMPLOYEEID, PORTFOLIOID, PMSTATUS, WORKLEVELTYPE, PROJECTVALUE, PROJECTGCVALUE, PROJECTBUDGET, PROJECTGCBUDGET, BILLTYPEID, BILLMETHODID, REMARKS ) VALUES ( @MAllocationId, @MAllocationName, @MAllocationShortName, @AllocationTypeId, @MAllocationDescription, @InchargeId, @MAllocationIsCapital, @MAllocationFromDate, @MAllocationToDate, @ParentAllocationId, @ObjectTypeId, @MAllocationObjectId, @MAllocationCostCenterId, @OrganizationUnitId, @MAllocationPartyId, @MAllocationSortOrder, @MAllocationStatus, @MAllocationVersion, @MAllocationCreatedById, @MAllocationCreatedOn, @MAllocationModifiedById, @MAllocationModifiedOn, @SourceType, @TenantId, @MAllocationRate, @CurrencyId, @MAllocationNumberOfData, @MAllocationExpectedPerHour, @MAllocationRatePerUOM, @PartyBranchId, @ReferencePartyBranchId, @BrokerPartyBranchId, @InchargeEmployeeId, @PortFolioId, @MAllocationPMStatus, @MAllocationWorkLevelType, @MAllocationProjectValue, @MAllocationProjectGCValue, @MAllocationProjectBudget, @MAllocationProjectGCBudget, @BillTypeId, @BillMethodId, @MAllocationRemarks );"; public const string UPDATE_ALLOCATION = @"UPDATE MALLOCATION SET ALLOCATIONNAME = @MAllocationName, ALLOCATIONSHORTNAME = @MAllocationShortName, ALLOCATIONTYPEID = @AllocationTypeId, DESCRIPTION = @MAllocationDescription, INCHARGEID = @InchargeId, ISCAPITAL = @MAllocationIsCapital, FROMDATE = @MAllocationFromDate, TODATE = @MAllocationToDate, PARENTALLOCATIONID = @ParentAllocationId, OBJECTTYPEID = @ObjectTypeId, OBJECTID = @MAllocationObjectId, COSTCENTERID = @MAllocationCostCenterId, OUID = @OrganizationUnitId, PARTYID = @MAllocationPartyId, SORTORDER = @MAllocationSortOrder, STATUS = @MAllocationStatus, VERSION = @MAllocationVersion, MODIFIEDBYID = @MAllocationModifiedById, MODIFIEDON = @MAllocationModifiedOn, SOURCETYPE = @SourceType, TENANTID = @TenantId, RATE = @MAllocationRate, CURRENCYID = @CurrencyId, NUMBEROFDATA = @MAllocationNumberOfData, EXPECTEDPERHOUR = @MAllocationExpectedPerHour, RATEPERUOM = @MAllocationRatePerUOM, PARTYBRANCHID = @PartyBranchId, REFERENCEPARTYBRANCHID = @ReferencePartyBranchId, BROKERPARTYBRANCHID = @BrokerPartyBranchId, INCHARGEEMPLOYEEID = @InchargeEmployeeId, PORTFOLIOID = @PortFolioId, PMSTATUS = @MAllocationPMStatus, WORKLEVELTYPE = @MAllocationWorkLevelType, PROJECTVALUE = @MAllocationProjectValue, PROJECTGCVALUE = @MAllocationProjectGCValue, PROJECTBUDGET = @MAllocationProjectBudget, PROJECTGCBUDGET = @MAllocationProjectGCBudget, BILLTYPEID = @BillTypeId, BILLMETHODID = @BillMethodId, REMARKS = @MAllocationRemarks WHERE ALLOCATIONID = @mallocationid;"; public const string DELETE_ALLOCATION = @"DELETE FROM MALLOCATION WHERE ALLOCATIONID = @mallocationid;"; public const string GET_SELECTLIST_ALLOCATION = @"WITH PagedAllocation AS ( SELECT A.ALLOCATIONID AS Id, A.ALLOCATIONNAME AS Name, A.ALLOCATIONSHORTNAME AS ShortName, A.DESCRIPTION AS Description, A.PARTYID AS PartyId, P.PARTYNAME AS PartyName, P.PARTYCODE AS PartyCode, ROW_NUMBER() OVER (ORDER BY A.ALLOCATIONNAME) AS RowNum FROM MALLOCATION A LEFT JOIN MPARTY P ON P.PARTYID = A.PARTYID ) SELECT Id, Name, ShortName, Description, PartyId, PartyName, PartyCode FROM PagedAllocation WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);"; public const string GET_SELECTLIST_MALLOCATION_SPLIT_BASE = @" SELECT SP.ALLOCATIONSPLITID AS Id, SP.SPLITCODE AS Code, SP.SPLITNAME AS Name FROM MALLOCATIONSPLIT SP WHERE 1=1"; } }