using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace CRMDAL.Query.Call { public static class CallQB { public const string GET_CALL_ID = @"SELECT A.CALLID AS CallId, A.OUID AS OUId, OU.ORGANIZATIONUNITCODE AS OUCode, OU.ORGANIZATIONUNITNAME AS OUName, A.BIZTRANSACTIONTYPEID AS BizTransactionTypeId, BTT.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode, BTT.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName, A.PERIODID AS PeriodId, P.PERIODCODE AS PeriodCode, P.PERIODNAME AS PeriodName, P.FROMDATE AS PeriodFromDate, P.TODATE AS PeriodToDate, A.CALLNUMBER AS CallNumber, A.CALLDATE AS CallDate, A.REFERENCENUMBER AS CallReferenceNumber, A.REFERENCEDATE AS CallReferenceDate, A.OBJECTTYPEID AS ObjectTypeId, OT.ENTITYCODE AS ObjectTypeCode, OT.ENTITYNAME AS ObjectTypeName, A.OBJECTID AS ObjectId, A.PARTYID AS PartyId, PR.PARTYCODE AS PartyCode, PR.PARTYNAME AS PartyName, A.PARTYBRANCHID AS PartyBranchId, PB.PARTYBRANCHCODE AS PartyBranchCode, PB.PARTYBRANCHNAME AS PartyBranchName, A.EMPLOYEEID AS EmployeeId, E.EMPLOYEECODE AS EmployeeCode, E.EMPLOYEENAME AS EmployeeName, A.CONTACTID AS ContactId, C.NAME AS ContactName, A.ADDRESSID AS AddressId, ADR.ADDRESSLINE1 AS AddressLine1, ADR.ADDRESSLINE2 AS AddressLine2, ADR.ADDRESSLINE3 AS AddressLine3, ADR.ADDRESSLINE4 AS AddressLine4, ADR.ADDRESSLINE5 AS AddressLine5, ADR.ZIPCODE AS AddressZipCode, ADR.MAIL AS AddressMail, A.REPORTEDBY AS ReportedByName, A.REPORTEDBYID AS ReportedById, RB.CONTACTNATURE AS ReportedByCode, RB.NAME AS ReportedByContactName, A.RECEIVEDBYID AS ReceivedById, RCV.EMPLOYEECODE AS ReceivedByCode, RCV.EMPLOYEENAME AS ReceivedByName, A.CALLRECEIVEDMODE AS CallReceivedMode, A.CALLTYPEID AS CallTypeId, CT.GCMCODE AS CallTypeCode, CT.GCMNAME AS CallTypeName, A.CALLGENERATIONTYPE AS CallGenerationType, A.CONTRACTID AS CallContract, A.CONTRACTCYCLENUMBER AS CallContractCycleNumber, A.CALLNATUREID AS CallNatureId, CN.GCMCODE AS CallNatureCode, CN.GCMNAME AS CallNatureName, A.CALLPRIORITYID AS CallPriorityId, CP.GCMCODE AS CallPriorityCode, CP.GCMNAME AS CallPriorityName, A.PROBLEMDATE AS CallProblemDate, A.PROBLEMTIME AS CallProblemTime, A.PROMISSEDDATE AS CallPromissedDate, A.ALLOTEDTOTYPE AS CallAllotedToType, A.ALLOTEDTOEMPLOYEEID AS AllotedToEmployeeId, AEMP.EMPLOYEECODE AS AllotedToEmployeeCode, AEMP.EMPLOYEENAME AS AllotedToEmployeeName, A.ALLOTEDTOCONTACTID AS AllotedToContactId, ACNT.NAME AS AllotedToContactName, A.ALLOTEDTOPARTYID AS AllotedToPartyId, AP.PARTYCODE AS AllotedToPartyCode, AP.PARTYNAME AS AllotedToPartyName, A.CALLSTATUS AS CallCallOriginalStatus, CASE A.CALLSTATUS WHEN 0 THEN 'Pending' WHEN 1 THEN 'Alloted' WHEN 2 THEN 'Completed' WHEN 3 THEN 'PostPoned' WHEN 4 THEN 'CallBack' WHEN 5 THEN 'Closed' WHEN 6 THEN 'Cancel' WHEN 7 THEN 'Request For Cancellation' WHEN 8 THEN 'Waiting For Spares' WHEN 9 THEN 'Pending For Approval' END AS MailCallOriginalStatus, CASE A.CALLSTATUS WHEN 0 THEN 'Pending' WHEN 1 THEN 'Alloted' WHEN 2 THEN 'Completed' WHEN 3 THEN 'PostPoned' WHEN 4 THEN 'CallBack' ELSE 'Closed' END AS OriginalStatus, A.CALLONOBJECTTYPEID AS CallOnObjectTypeId, COOT.GCMCODE AS CallOnObjectTypeCode, COOT.GCMNAME AS CallOnObjectTypeName, A.CALLONOBJECTID AS CallCallOnObjectId, A.CALLONNAME AS CallCallOnName, A.CALLONOBJECTSTATUS AS CallCallOnObjectStatus, A.COVERAGESTATUS AS CallCoverageStatus, A.REPORTEDPROBLEM AS CallReportedProblem, A.ACTIONTAKEN AS CallActionTaken, A.CUSTOMERREMARKS AS CallCustomerRemarks, A.PROBLEMNATUREID AS ProblemNatureId, PN.GCMCODE AS ProblemNatureCode, PN.GCMNAME AS ProblemNatureName, A.PLANDATE AS CallPlanDate, A.POSSIBLEDATE AS CallPossibleDate, A.ESTIMATEDLABOURAMOUNT AS CallEstimatedLabourAmount, A.ESTIMATEDSPARESAMOUNT AS CallEstimatedSparesAmount, A.ESTIMATEDOTHERSAMOUNT AS CallEstimatedOthersAmount, A.ESTIMATEDTOTALAMOUNT AS CallEstimatedTotalAmount, A.ESTIMATEDBYID AS EstimatedById, EB.EMPLOYEECODE AS EstimatedByCode, EB.EMPLOYEENAME AS EstimatedByName, A.LABOURAMOUNT AS CallLabourAmount, A.SPARESAMOUNT AS CallSparesAmount, A.OTHERAMOUNT AS CallOtherAmount, A.TOTALAMOUNT AS CallTotalAmount, A.PERSONID AS PersonId, PERS.EMPLOYEECODE AS PersonCode, PERS.EMPLOYEENAME AS PersonName, A.INDOOROUTDOOR AS CallIndoorOutdoor, A.ISITEMRECEIVED AS CallIsItemReceived, A.ITEMRECEIVEDREMARKS AS CallItemReceivedRemarks, A.ISSTANDBYGIVEN AS CallIsStandByGiven, A.STANDBYITEMID AS StandByItemId, ITM.ITEMCODE AS StandByItemCode, ITM.ITEMNAME AS StandByItemName, A.STANDBYREMARKS AS CallStandByRemarks, A.COMPLETEDBYID AS CompletedById, CB.EMPLOYEECODE AS CompletedByCode, CB.EMPLOYEENAME AS CompletedByName, A.COMPLETEDON AS CallCompletedOn, A.COMPLETEDONTIME AS CallCompletedOnTime, A.CUSTOMERFEEDBACK AS CallCustomerFeedback, A.ISCLOSED AS CallIsClosed, A.CLOSEDBYID AS ClosedById, CL.EMPLOYEECODE AS ClosedByCode, CL.EMPLOYEENAME AS ClosedByName, A.CLOSEDON AS CallClosedOn, A.CLOSEDONTIME AS CallClosedOnTime, A.REASONID AS ReasonId, RS.REASONCODE AS ReasonCode, RS.REASONNAME AS ReasonName, A.ISACCOUNTED AS CallIsAccounted, A.STATUS AS CallStatus, A.VERSION AS CallVersion, A.CREATEDBYID AS CallCreatedById, CRU.EMPLOYEENAME AS CallCreatedByName, A.CREATEDON AS CallCreatedOn, A.MODIFIEDBYID AS CallModifiedById, MDU.EMPLOYEENAME AS CallModifiedByName, A.MODIFIEDON AS CallModifiedOn, A.ALLOCATIONID AS AllocationId, AL.ALLOCATIONNAME AS AllocationName, AL.ALLOCATIONSHORTNAME AS AllocationShortName, A.ROOTCAUSEID AS RootCauseId, RC.REASONCODE AS RootCauseCode, RC.REASONNAME AS RootCauseName, A.ROOTCAUSEDESCRIPTION AS RootCauseDescription, A.MANUFACTURERSLNO AS CallManufacturerSlno, A.ALLOTEDMODE AS CallAllotedMode, D.CALLVSDEFECTSID AS CallVsDefectsId, D.CALLID AS CallId, D.SLNO AS CallVsDefectsSlNo, D.PROBLEMID AS ProblemId, PRO.PROBLEMCODE AS ProblemCode, PRO.PROBLEMNAME AS ProblemName, mas.ASSETID AS AssetId, ISNULL(mas.ASSETNAME, '') AS EquipmentName, ISNULL(mas.ASSETCODE, '') AS EquipmentCode, vcalldetail.Eqtstatus AS EquipmentRunningStatus, vcalldetail.Serialnumber AS EquipmentSerialNumber, loc.PARTYBRANCHLOCATIONNAME AS Location, vcalldetail.Warratypstatus AS WarrantyStatus FROM TCALL A LEFT JOIN MOrganizationUnit OU ON A.OUID = OU.OUID LEFT JOIN MBizTransactionType BTT ON A.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID LEFT JOIN MPeriod P ON A.PERIODID = P.PERIODID LEFT JOIN MEntity OT ON A.OBJECTTYPEID = OT.ENTITYID LEFT JOIN MParty PR ON A.PARTYID = PR.PARTYID LEFT JOIN MPartyBranch PB ON A.PARTYBRANCHID = PB.PARTYBRANCHID LEFT JOIN MAddress ADR ON A.ADDRESSID = ADR.ADDRESSID LEFT JOIN MEmployee E ON A.EMPLOYEEID = E.EMPLOYEEID LEFT JOIN MContact C ON A.CONTACTID = C.CONTACTID LEFT JOIN MContact RB ON A.REPORTEDBYID = RB.CONTACTID LEFT JOIN MEmployee RCV ON A.RECEIVEDBYID = RCV.EMPLOYEEID LEFT JOIN MGCM CT ON A.CALLTYPEID = CT.GCMID LEFT JOIN MGCM CN ON A.CALLNATUREID = CN.GCMID LEFT JOIN MGCM CP ON A.CALLPRIORITYID = CP.GCMID LEFT JOIN MEmployee AEMP ON A.ALLOTEDTOEMPLOYEEID = AEMP.EMPLOYEEID LEFT JOIN MContact ACNT ON A.ALLOTEDTOCONTACTID = ACNT.CONTACTID LEFT JOIN MParty AP ON A.ALLOTEDTOPARTYID = AP.PARTYID LEFT JOIN MGCM COOT ON A.CALLONOBJECTTYPEID = COOT.GCMID LEFT JOIN MGCM PN ON A.PROBLEMNATUREID = PN.GCMID LEFT JOIN MEmployee EB ON A.ESTIMATEDBYID = EB.EMPLOYEEID LEFT JOIN MEmployee PERS ON A.PERSONID = PERS.EMPLOYEEID LEFT JOIN MItem ITM ON A.STANDBYITEMID = ITM.ITEMID LEFT JOIN MEmployee CB ON A.COMPLETEDBYID = CB.EMPLOYEEID LEFT JOIN MEmployee CL ON A.CLOSEDBYID = CL.EMPLOYEEID LEFT JOIN MReason RS ON A.REASONID = RS.REASONID LEFT JOIN MEmployee CRU ON A.CREATEDBYID = CRU.EMPLOYEEID LEFT JOIN MEmployee MDU ON A.MODIFIEDBYID = MDU.EMPLOYEEID LEFT JOIN MAllocation AL ON A.ALLOCATIONID = AL.ALLOCATIONID LEFT JOIN MReason RC ON A.ROOTCAUSEID = RC.REASONID LEFT JOIN TCALLVSDEFECTS D ON A.CALLID = D.CALLID LEFT JOIN MPROBLEM PRO ON D.PROBLEMID = PRO.PROBLEMID LEFT JOIN MASSET mas ON A.CALLONOBJECTID = mas.ASSETID AND A.CALLONOBJECTTYPEID = -1200000000 LEFT JOIN vcalldetail ON vcalldetail.CALLID = A.CALLID LEFT JOIN MPARTYBRANCHLOCATION loc ON loc.PARTYBRANCHLOCATIONID = vcalldetail.locationid WHERE A.CALLID = @callid;"; // ── Save / Update ────────────────────────────────────────────────────────────── public const string SAVE_CALL = @" INSERT INTO TCALL ( CALLID, OUID, BIZTRANSACTIONTYPEID, PERIODID, CALLNUMBER, CALLDATE, REFERENCENUMBER, REFERENCEDATE, OBJECTTYPEID, OBJECTID, PARTYID, PARTYBRANCHID, EMPLOYEEID, CONTACTID, ADDRESSID, REPORTEDBY, REPORTEDBYID, RECEIVEDBYID, CALLRECEIVEDMODE, CALLTYPEID, CALLGENERATIONTYPE, CONTRACTID, CONTRACTCYCLENUMBER, CALLNATUREID, CALLPRIORITYID, PROBLEMDATE, PROBLEMTIME, PROMISSEDDATE, ALLOTEDTOTYPE, ALLOTEDTOEMPLOYEEID, ALLOTEDTOCONTACTID, ALLOTEDTOPARTYID, CALLSTATUS, CALLONOBJECTTYPEID, CALLONOBJECTID, CALLONNAME, COVERAGESTATUS, REPORTEDPROBLEM, ACTIONTAKEN, CUSTOMERREMARKS, PROBLEMNATUREID, PLANDATE, POSSIBLEDATE, ESTIMATEDLABOURAMOUNT, ESTIMATEDSPARESAMOUNT, ESTIMATEDOTHERSAMOUNT, ESTIMATEDTOTALAMOUNT, ESTIMATEDBYID, LABOURAMOUNT, SPARESAMOUNT, OTHERAMOUNT, TOTALAMOUNT, PERSONID, INDOOROUTDOOR, ISITEMRECEIVED, ITEMRECEIVEDREMARKS, ISSTANDBYGIVEN, STANDBYITEMID, STANDBYREMARKS, COMPLETEDBYID, COMPLETEDON, COMPLETEDONTIME, CUSTOMERFEEDBACK, ISCLOSED, CLOSEDBYID, CLOSEDON, CLOSEDONTIME, REASONID, ISACCOUNTED, ROOTCAUSEID, ROOTCAUSEDESCRIPTION, CALLONOBJECTSTATUS, ALLOCATIONID, ALLOTEDMODE, MANUFACTURERSLNO, VERSION, STATUS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON ) VALUES ( @CallId, @OUId, @BIZTransactionTypeId, @PeriodId, @CallNumber, @CallDate, @CallReferenceNumber, @CallReferenceDate, @ObjectTypeId, @CallObjectId, @PartyId, @PartyBranchId, @EmployeeId, @ContactId, @AddressId, @CallReportedByName, @ReportedById, @ReceivedById, @CallCallReceivedMode, @CallTypeId, @CallCallGenerationType, @CallContract, @CallContractCycleNumber, @CallNatureId, @CallPriorityId, @CallProblemDate, @CallProblemTime, @CallPromissedDate, @CallAllotedToType, @AllotedToEmployeeId, @AllotedToContactId, @AllotedToPartyId, @CallCallOriginalStatus, @CallOnObjectTypeId, @CallCallOnObjectId, @CallCallOnName, @CallCoverageStatus, @CallReportedProblem, @CallActionTaken, @CallCustomerRemarks, @ProblemNatureId, @CallPlanDate, @CallPossibleDate, @CallEstimatedLabourAmount, @CallEstimatedSparesAmount, @CallEstimatedOthersAmount, @CallEstimatedTotalAmount, @EstimatedById, @CallLabourAmount, @CallSparesAmount, @CallOtherAmount, @CallTotalAmount, @PersonId, @CallIndoorOutdoor, @CallIsItemReceived, @CallItemReceivedRemarks, @CallIsStandByGiven, @StandByItemId, @CallStandByRemarks, @CompletedById, @CallCompletedOn, @CallCompletedOnTime, @CallCustomerFeedback, @CallIsClosed, @ClosedById, @CallClosedOn, @CallClosedOnTime, @ReasonId, @CallIsAccounted, @RootCauseId, @RootCauseDescription, @CallCallonObjectStatus, @AllocationId, @CallAllotedMode, @CallManufacturerSlno, @CallVersion, @CallStatus, @CallCreatedById, @CallCreatedOn, @CallModifiedById, @CallModifiedOn );"; public const string UPDATE_CALL = @" UPDATE TCALL SET OUID = @OUId, BIZTRANSACTIONTYPEID = @BIZTransactionTypeId, PERIODID = @PeriodId, CALLNUMBER = @CallNumber, CALLDATE = @CallDate, REFERENCENUMBER = @CallReferenceNumber, REFERENCEDATE = @CallReferenceDate, OBJECTTYPEID = @ObjectTypeId, OBJECTID = @CallObjectId, PARTYID = @PartyId, PARTYBRANCHID = @PartyBranchId, EMPLOYEEID = @EmployeeId, CONTACTID = @ContactId, ADDRESSID = @AddressId, REPORTEDBY = @CallReportedByName, REPORTEDBYID = @ReportedById, RECEIVEDBYID = @ReceivedById, CALLRECEIVEDMODE = @CallCallReceivedMode, CALLTYPEID = @CallTypeId, CALLGENERATIONTYPE = @CallCallGenerationType, CONTRACTID = @CallContract, CONTRACTCYCLENUMBER = @CallContractCycleNumber, CALLNATUREID = @CallNatureId, CALLPRIORITYID = @CallPriorityId, PROBLEMDATE = @CallProblemDate, PROBLEMTIME = @CallProblemTime, PROMISSEDDATE = @CallPromissedDate, ALLOTEDTOTYPE = @CallAllotedToType, ALLOTEDTOEMPLOYEEID = @AllotedToEmployeeId, ALLOTEDTOCONTACTID = @AllotedToContactId, ALLOTEDTOPARTYID = @AllotedToPartyId, CALLSTATUS = @CallCallOriginalStatus, CALLONOBJECTTYPEID = @CallOnObjectTypeId, CALLONOBJECTID = @CallCallOnObjectId, CALLONNAME = @CallCallOnName, COVERAGESTATUS = @CallCoverageStatus, REPORTEDPROBLEM = @CallReportedProblem, ACTIONTAKEN = @CallActionTaken, CUSTOMERREMARKS = @CallCustomerRemarks, PROBLEMNATUREID = @ProblemNatureId, PLANDATE = @CallPlanDate, POSSIBLEDATE = @CallPossibleDate, ESTIMATEDLABOURAMOUNT = @CallEstimatedLabourAmount, ESTIMATEDSPARESAMOUNT = @CallEstimatedSparesAmount, ESTIMATEDOTHERSAMOUNT = @CallEstimatedOthersAmount, ESTIMATEDTOTALAMOUNT = @CallEstimatedTotalAmount, ESTIMATEDBYID = @EstimatedById, LABOURAMOUNT = @CallLabourAmount, SPARESAMOUNT = @CallSparesAmount, OTHERAMOUNT = @CallOtherAmount, TOTALAMOUNT = @CallTotalAmount, PERSONID = @PersonId, INDOOROUTDOOR = @CallIndoorOutdoor, ISITEMRECEIVED = @CallIsItemReceived, ITEMRECEIVEDREMARKS = @CallItemReceivedRemarks, ISSTANDBYGIVEN = @CallIsStandByGiven, STANDBYITEMID = @StandByItemId, STANDBYREMARKS = @CallStandByRemarks, COMPLETEDBYID = @CompletedById, COMPLETEDON = @CallCompletedOn, COMPLETEDONTIME = @CallCompletedOnTime, CUSTOMERFEEDBACK = @CallCustomerFeedback, ISCLOSED = @CallIsClosed, CLOSEDBYID = @ClosedById, CLOSEDON = @CallClosedOn, CLOSEDONTIME = @CallClosedOnTime, REASONID = @ReasonId, ISACCOUNTED = @CallIsAccounted, ROOTCAUSEID = @RootCauseId, ROOTCAUSEDESCRIPTION = @RootCauseDescription, CALLONOBJECTSTATUS = @CallCallonObjectStatus, ALLOCATIONID = @AllocationId, ALLOTEDMODE = @CallAllotedMode, MANUFACTURERSLNO = @CallManufacturerSlno, VERSION = @CallVersion, STATUS = @CallStatus, MODIFIEDBYID = @CallModifiedById, MODIFIEDON = @CallModifiedOn WHERE CALLID = @CallId;"; public const string SAVE_CALL_VS_DEFECTS = @" INSERT INTO TCALLVSDEFECTS ( CALLVSDEFECTSID, CALLID, SLNO, PROBLEMID ) VALUES ( @CallVsDefectsId, @CallId, @CallVsDefectsSlNo, @ProblemId );"; public const string DELETE_CALL_VS_DEFECTS = @" DELETE FROM TCALLVSDEFECTS WHERE CALLID = @CallId;"; public const string UPDATE_COMPLETED_STATUS_FIELDS = @" UPDATE TCALL SET COMPLETEDBYID = @CompletedById, COMPLETEDON = CAST(GETDATE() AS DATE), COMPLETEDONTIME = @CompletedOnTime WHERE CALLID = @CallId;"; public const string UPDATE_CLOSED_STATUS_FIELDS = @" UPDATE TCALL SET CLOSEDBYID = @ClosedById, CLOSEDON = CAST(GETDATE() AS DATE), CLOSEDONTIME = @ClosedOnTime WHERE CALLID = @CallId;"; // ── Lookup queries used during save ──────────────────────────────────────────── // Pre-flight check for the FK_TCALL_BIZTRANSACTIONTYPEID_OUID_PERIODID constraint — // MBIZTRANSACTIONTYPE rows are scoped to one specific OU + Period, so a // BIZTransactionTypeId selected under a different OU/Period than the one the call is // being saved under fails the FK at INSERT time. Checking first turns that into a // clear validation error instead of a raw SQL 547 surfacing to the caller. public const string CHECK_BIZTRANSACTIONTYPE_EXISTS = @" SELECT COUNT(1) FROM MBIZTRANSACTIONTYPE WHERE BIZTRANSACTIONTYPEID = @BizTransactionTypeId AND OUID = @OUId AND PERIODID = @PeriodId;"; public const string GET_CALL_CREATED_BY_ID = @" SELECT CREATEDBYID FROM TCALL WHERE CALLID = @CallId;"; public const string GET_EMPLOYEE_INFO = @" SELECT E.PRIMARYMOBILE AS PrimaryMobile, E.PRIMARYEMAILID AS PrimaryEMailId, R.PRIMARYMOBILE AS ReportingMobile, R.PRIMARYEMAILID AS ReportingEmail, ISNULL(R.EMPLOYEEID,0) AS ReportingEmployeeId FROM MEMPLOYEE E LEFT JOIN MEMPLOYEE R ON R.EMPLOYEEID = E.REPORTINGTOEMPLOYEEID WHERE E.EMPLOYEEID = @EmployeeId;"; public const string GET_ASSET_CONTACT_INFO = @"SELECT PI.PRIMARYMOBILE AS InchargeMobile, PI.PRIMARYEMAILID AS InchargeEmail, PS.PRIMARYMOBILE AS SalesInchargeMobile, PS.PRIMARYEMAILID AS SalesInchargeEmail, L.LOCATIONNAME AS LocationName, PB.INCHARGEID AS InchargeId, PB.SALESINCHARGEID AS SalesInchargeId FROM MASSET A JOIN MPARTYBRANCH PB ON PB.PARTYBRANCHID = A.PARTYBRANCHID LEFT JOIN MEMPLOYEE PI ON PI.EMPLOYEEID = PB.INCHARGEID -- Fixed: MEMPLOYEE instead of MCONTACT LEFT JOIN MEMPLOYEE PS ON PS.EMPLOYEEID = PB.SALESINCHARGEID -- Fixed: MEMPLOYEE instead of MCONTACT LEFT JOIN MLOCATION L ON L.LOCATIONID = A.PARTYBRANCHLOCATIONID WHERE A.ASSETID = @AssetId;"; public const string GET_CONTACT_INFO = @" SELECT MOBILENO AS MobileNo, MAILID AS MailId FROM MCONTACT WHERE CONTACTID = @ContactId;"; public const string GET_USER_CONTACT_INFO = @" SELECT PRIMARYMOBILE AS PrimaryMobile, PRIMARYMAIL AS PrimaryMail FROM MUSER WHERE USERID = @UserId;"; // ── Party Product ────────────────────────────────────────────────────────────── public const string GET_PARTY_PRODUCT = @" SELECT PARTYPRODUCTID FROM MPARTYPRODUCT WHERE PARTYID = @PartyId AND ITEMID = @ItemId;"; public const string INSERT_PARTY_PRODUCT = @" INSERT INTO MPARTYPRODUCT (PARTYID, ITEMID, PARTYBRANCHID, ADDRESSID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, STATUS, VERSION) SELECT PARTYID, STANDBYITEMID, PARTYBRANCHID, ADDRESSID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, 1, 1 FROM TCALL WHERE CALLID = @CallId;"; public const string UPDATE_CALL_AFTER_PARTY_PRODUCT = @" UPDATE TCALL SET CALLONOBJECTID = (SELECT TOP 1 PARTYPRODUCTID FROM MPARTYPRODUCT WHERE PARTYID = TCALL.PARTYID AND ITEMID = TCALL.STANDBYITEMID), CALLONOBJECTTYPEID = -1399998989 WHERE CALLID = @CallId;"; public const string VALIDATE_PARTY_PRODUCT_ADDRESS = @" SELECT ADR.MOBILE AS Mobile, ADR.ZIPCODE AS ZipCode FROM MADDRESS ADR JOIN MPARTYPRODUCT PP ON PP.CURRENTADDRESSID = ADR.ADDRESSID WHERE PP.PARTYPRODUCTID = @PartyProductId;"; // ── Report Queries ───────────────────────────────────────────────────────────── /// /// Full Call Register Report. /// Required params: @OUId, @PeriodFromDate, @PeriodToDate, @AllotedToEmployeeId, /// @AllotedToContactId, @AllotedToPartyId, @CallOnObjectTypeId, @PartyProductId, /// @CallId (0 = all), @CallStatusFilterOff (1 = ignore @CallStatus / return all statuses), /// @CallStatus (a real IEnumerable<int> of status codes — Dapper's own "IN @Param" /// list expansion, NOT STRING_SPLIT: STRING_SPLIT needs compat level 130+ and throws /// "Invalid object name 'STRING_SPLIT'" on compat-100 tenant databases, e.g. UNISOFTGB5). /// Optional filters appended dynamically by the BLL (WHERE clause additions). /// NOTE: currently unreferenced by any DAL method — not yet wired up to a caller. /// public const string GET_CALLREGISTER_REPORT = @" SELECT tcall.CALLID AS CallId, tcall.CREATEDBYID AS CreatedById, ucreated.USERNAME AS CreatedbyName, tcall.CALLONOBJECTSTATUS AS CallOnObjectStatus, tcall.OUID AS OUId, ou.ORGANIZATIONUNITCODE AS UnitCode, ou.ORGANIZATIONUNITNAME AS UnitName, tcall.BIZTRANSACTIONTYPEID AS BizTransactionTypeId, biztype.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode, biztype.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName, tcall.PERIODID AS PeriodId, tcall.CALLNUMBER AS CallNumber, tcall.CALLDATE AS CallDate, tcall.REFERENCENUMBER AS ReferenceNumber, tcall.REFERENCEDATE AS ReferenceDate, tcall.OBJECTTYPEID AS ObjectTypeId, tcall.OBJECTID AS ObjectId, tcall.PARTYID AS PartyId, mparty.PARTYCODE AS PartyCode, mparty.PARTYNAME AS PartyName, tcall.ALLOCATIONID AS AllocationId, mallocation.ALLOCATIONSHORTNAME AS AllocationShortName, mallocation.ALLOCATIONNAME AS AllocationName, tcall.PARTYBRANCHID AS PartyBranchId, mpartybranch.PARTYBRANCHCODE AS PartyBranchCode, mpartybranch.PARTYBRANCHNAME AS PartyBranchName, tcall.EMPLOYEEID AS EmployeeId, memployee.EMPLOYEECODE AS EmployeeCode, memployee.EMPLOYEENAME AS EmployeeName, memployee.DEPARTMENTID AS DepartmentId, dept.DEPARTMENTCODE AS DepartmentCode, dept.DEPARTMENTNAME AS DepartmentName, tcall.CONTACTID AS ContactId, mcontact.NAME AS ContactName, tcall.ADDRESSID AS AddressId, maddress.ADDRESSLINE1 AS AddressLine1, maddress.ADDRESSLINE2 AS AddressLine2, maddress.ADDRESSLINE3 AS AddressLine3, maddress.MOBILE AS MobileNumber, maddress.PHONE AS AddressPhone, tcall.REPORTEDBY AS ReportedBy, tcall.REPORTEDBYID AS ReportedById, tcall.RECEIVEDBYID AS ReceivedById, reciveemp.EMPLOYEECODE AS ReceivedByCode, reciveemp.EMPLOYEENAME AS ReceivedByName, tcall.CALLRECEIVEDMODE AS CallReceivedMode, tcall.CALLTYPEID AS CallTypeId, calltypegcm.GCMCODE AS CallTypeCode, calltypegcm.GCMNAME AS CallTypeName, tcall.CALLGENERATIONTYPE AS CallGenerationType, tcall.CALLNATUREID AS CallNatureId, callnaturegcm.GCMCODE AS CallNatureCode, callnaturegcm.GCMNAME AS CallNatureName, tcall.CALLPRIORITYID AS CallPriorityId, callprioritygcm.GCMCODE AS CallPriorityCode, callprioritygcm.GCMNAME AS CallPriorityName, tcall.PROBLEMDATE AS ProblemDate, tcall.PROBLEMTIME AS ProblemTime, tcall.PROMISSEDDATE AS PromissedDate, tcall.ALLOTEDTOTYPE AS AllotedToType, CASE WHEN tcall.ALLOTEDTOTYPE = 0 THEN allotedtoperson.EMPLOYEEID WHEN tcall.ALLOTEDTOTYPE = 1 THEN allotedcontact.CONTACTID WHEN tcall.ALLOTEDTOTYPE = 2 THEN allotedtoparty.PARTYID ELSE -1 END AS AllotedToId, CASE WHEN tcall.ALLOTEDTOTYPE = 0 THEN allotedtoperson.EMPLOYEECODE WHEN tcall.ALLOTEDTOTYPE = 1 THEN allotedcontact.NAME WHEN tcall.ALLOTEDTOTYPE = 2 THEN allotedtoparty.PARTYCODE ELSE 'None' END AS AllotedToCode, CASE WHEN tcall.ALLOTEDTOTYPE = 0 THEN allotedtoperson.EMPLOYEENAME WHEN tcall.ALLOTEDTOTYPE = 1 THEN allotedcontact.NAME WHEN tcall.ALLOTEDTOTYPE = 2 THEN allotedtoparty.PARTYNAME ELSE 'None' END AS AllotedToName, tcall.CALLSTATUS AS CallStatus, CASE tcall.CALLSTATUS WHEN 0 THEN 'Pending' WHEN 1 THEN 'Allotted' WHEN 2 THEN 'Completed' WHEN 3 THEN 'Postponed' WHEN 4 THEN 'CallBack' WHEN 5 THEN 'Closed' WHEN 6 THEN 'Cancel' WHEN 7 THEN 'Request For Cancellation' WHEN 8 THEN 'Waiting For Spares' WHEN 9 THEN 'Pending For Approval' ELSE 'Others' END AS CallStatusName, tcall.COVERAGESTATUS AS CoverageStatus, tcall.REPORTEDPROBLEM AS ReportedProblem, tcall.ACTIONTAKEN AS ActionTaken, tcall.CUSTOMERREMARKS AS CustomerRemarks, tcall.PROBLEMNATUREID AS ProblemNatureId, problemnature.GCMCODE AS ProblemNatureCode, problemnature.GCMNAME AS ProblemNatureName, tcall.PLANDATE AS PlanDate, tcall.POSSIBLEDATE AS PossibleDate, tcall.ESTIMATEDLABOURAMOUNT AS EstimatedLabourAmount, tcall.ESTIMATEDSPARESAMOUNT AS EstimatedSparesAmount, tcall.ESTIMATEDOTHERSAMOUNT AS EstimatedOthersAmount, tcall.ESTIMATEDTOTALAMOUNT AS EstimatedTotalAmount, tcall.ESTIMATEDBYID AS EstimatedById, estimatedby.EMPLOYEECODE AS EstimatedByCode, estimatedby.EMPLOYEENAME AS EstimatedByName, tcall.LABOURAMOUNT AS LabourAmount, tcall.SPARESAMOUNT AS SparesAmount, tcall.OTHERAMOUNT AS OtherAmount, tcall.TOTALAMOUNT AS TotalAmount, tcall.PERSONID AS PersonId, person.EMPLOYEECODE AS PersonCode, person.EMPLOYEENAME AS PersonName, tcall.INDOOROUTDOOR AS InDoorOutDoor, tcall.ISITEMRECEIVED AS IsItemReceived, tcall.ITEMRECEIVEDREMARKS AS ItemReceivedRemarks, tcall.ISSTANDBYGIVEN AS IsStandByGiven, item.ITEMCODE AS ItemCode, item.ITEMNAME AS ItemName, tcall.STANDBYREMARKS AS StandByRemarks, tcall.COMPLETEDBYID AS CompletedById, completedby.EMPLOYEECODE AS CompletedByCode, completedby.EMPLOYEENAME AS CompletedByName, tcall.COMPLETEDON AS CompletedOn, tcall.COMPLETEDONTIME AS CompletedOnTime, tcall.CUSTOMERFEEDBACK AS CustomerFeedback, tcall.ISCLOSED AS IsClosed, tcall.CLOSEDBYID AS ClosedById, closedby.EMPLOYEECODE AS ClosedByCode, closedby.EMPLOYEENAME AS ClosedByName, tcall.CLOSEDON AS ClosedOn, tcall.CLOSEDONTIME AS ClosedOnTime, tcall.REASONID AS ReasonId, mreason.REASONCODE AS ReasonCode, mreason.REASONNAME AS ReasonName, tcall.ISACCOUNTED AS IsAccounted, tcall.CREATEDON AS CreatedOn, CONVERT(DATE, tcall.CREATEDON) AS CreatedOnDate, CONVERT(VARCHAR(8), CONVERT(TIME, tcall.CREATEDON)) AS CreatedOnTime, tcall.MODIFIEDON AS ModifiedOn, CONVERT(DATE, tcall.MODIFIEDON) AS ModifiedOnDate, CONVERT(VARCHAR(8), CONVERT(TIME, tcall.MODIFIEDON)) AS ModifiedOnTime, umodified.USERNAME AS ModifiedByName, umodified.USERCODE AS ModifiedByCode, tcall.CALLONNAME AS CallOnName, tcall.CALLONOBJECTID AS CallOnObjectId, tcall.CALLONOBJECTTYPEID AS CallOnObjectTypeId, tcall.ROOTCAUSEID AS RootCauseId, rootreason.REASONCODE AS RootCauseCode, rootreason.REASONNAME AS RootCauseName, tcall.ROOTCAUSEDESCRIPTION AS RootCauseDescription, tcall.VERSION AS CallVersion, tcall.MANUFACTURERSLNO AS ManufacturerSlNo, -- Time calculations DATEDIFF(MINUTE, DATEADD(MINUTE, tcall.PROBLEMTIME, tcall.PROBLEMDATE), DATEADD(MINUTE, tcall.COMPLETEDONTIME, tcall.COMPLETEDON)) AS DownTime, DATEDIFF(MINUTE, DATEADD(MINUTE, tcall.PROBLEMTIME, tcall.PROBLEMDATE), tcall.CREATEDON) AS DelayTime, DATEADD(MINUTE, tcall.PROBLEMTIME, tcall.PROBLEMDATE) AS FinalProblemTime, DATEADD(MINUTE, tcall.COMPLETEDONTIME, tcall.COMPLETEDON) AS FinalCompletedTime, DATEDIFF(MINUTE, tcall.CALLDATE, tcall.COMPLETEDON) AS BreakDownTime, ABS(DATEDIFF(DAY, tcall.CREATEDON, GETDATE())) AS AgeDays, ABS(DATEDIFF(HOUR, tcall.CREATEDON, GETDATE())) AS AgeTime, ABS(DATEDIFF(HOUR, tcall.CREATEDON, tcall.MODIFIEDON)) AS DifferenceTime, DATEDIFF(DAY, tcall.CALLDATE, @PeriodToDate) AS NoOfDays, DATEDIFF(DAY, tcall.CALLDATE, tcall.CLOSEDON + 1) AS ClosedNoOfDays, CASE WHEN tcall.CALLSTATUS = 2 THEN DATEDIFF(DAY, tcall.CALLDATE, tcall.COMPLETEDON) WHEN tcall.CALLSTATUS = 5 THEN DATEDIFF(DAY, tcall.CALLDATE, tcall.CLOSEDON) ELSE DATEDIFF(DAY, tcall.CALLDATE, GETDATE()) END AS CompletedNoofDays, CASE WHEN tcall.CALLSTATUS = 2 THEN tcall.COMPLETEDON WHEN tcall.CALLSTATUS = 5 THEN tcall.CLOSEDON ELSE tcall.CALLDATE END AS CallCompletedOn, CASE WHEN tcall.CALLSTATUS = 2 THEN DATEDIFF(DAY, tcall.PROBLEMDATE, tcall.COMPLETEDON) ELSE DATEDIFF(DAY, tcall.PROBLEMDATE, GETDATE()) END AS PendingNoofDays, -- Asset fields mas.ASSETID AS AssetId, ISNULL(mas.ASSETNAME, '') AS EquipmentName, ISNULL(mas.ASSETCODE, '') AS EquipmentCode, ISNULL(mas.MFRSLNO, '') AS EquipmentSerialNumber, mas.DESCRIPTION AS AssetDescription, mas.ASSETBATCH AS AssetBatch, mas.ASSETSTATUS AS AssetStatus, CASE mas.ASSETSTATUS WHEN 0 THEN 'Request' WHEN 1 THEN 'Ordered' WHEN 2 THEN 'WIP' WHEN 3 THEN 'Capitalized' WHEN 4 THEN 'Retired' ELSE ' ' END AS AssetStatusName, asdept.DEPARTMENTCODE AS AssetDepartmentCode, asdept.DEPARTMENTNAME AS AssetDepartmentName, -- Item / Product fields itemmodel.MODELID AS ItemModelId, itemmodel.MODELCODE AS ItemModelCode, itemmodel.MODELNAME AS ItemModelName, machine.MACHINECODE AS MachineCode, machine.MACHINENAME AS MachineName, category.ITEMCATEGORYCODE AS ItemCategoryCode, category.ITEMCATEGORYNAME AS ItemCategoryName, subcategory.ITEMSUBCATEGORYCODE AS ItemSubCategoryCode, subcategory.ITEMSUBCATEGORYNAME AS ItemSubCategoryName, model.MODELCODE AS ModelCode, model.MODELNAME AS ModelName, mgroup.ITEMGROUPCODE AS ItemGroupCode, mgroup.ITEMGROUPNAME AS ItemGroupName, brand.ITEMBRANDCODE AS ItemBrandCode, brand.ITEMBRANDNAME AS ItemBrandName, -- Address / Location reg.REGIONCODE AS RegionCode, reg.REGIONNAME AS RegionName, maddress.STANDARDREGIONID AS RegionId, city.CITYCODE AS CityCode, city.CITYNAME AS CityName, mloc.LOCATIONNAME AS LocationName, loc.PARTYBRANCHLOCATIONNAME AS Location, -- Previous call prev.CALLNUMBER AS PreviousCallNumber, prev.PROBLEMDATE AS PreviousProblemDate, prev.REPORTEDPROBLEM AS PreviousReportedProblem, prev.ACTIONTAKEN AS PreviousActionTaken, -- Maintenance task prevTask.ACTUALENDON AS LastMaintenanceDoneOn, -- Action detail actiondetail.REMARKS AS ActionRemarks, actionTaken.firststart AS FirstStartOn, actionTaken.Lastend AS LastEndOn, DATEDIFF(MINUTE, actionTaken.firststart, actionTaken.Lastend) AS Duration, -- Approver fields (via asset party branch) assetbranch_sales.EMPLOYEECODE AS FirstApproverCode, assetbranch_sales.EMPLOYEENAME AS FirstApproverName, assetbranch_incharge.EMPLOYEECODE AS SecondApproverCode, assetbranch_incharge.EMPLOYEENAME AS SecondApproverName FROM TCALL tcall -- Asset party branch lookup OUTER APPLY ( SELECT ass.PARTYBRANCHID FROM MASSET ass WHERE ass.ASSETID = tcall.CALLONOBJECTID AND tcall.CALLONOBJECTTYPEID = -1200000000 ) asspb OUTER APPLY ( SELECT g.EMPLOYEECODE, g.EMPLOYEENAME FROM MPARTYBRANCH e JOIN MEMPLOYEE g ON e.SALESINCHARGEID = g.EMPLOYEEID WHERE e.PARTYBRANCHID = asspb.PARTYBRANCHID ) assetbranch_sales OUTER APPLY ( SELECT g.EMPLOYEECODE, g.EMPLOYEENAME FROM MPARTYBRANCH e JOIN MEMPLOYEE g ON e.INCHARGEID = g.EMPLOYEEID WHERE e.PARTYBRANCHID = asspb.PARTYBRANCHID ) assetbranch_incharge -- Previous call on same object OUTER APPLY ( SELECT TOP 1 prev.CALLNUMBER, prev.PROBLEMDATE, prev.REPORTEDPROBLEM, prev.ACTIONTAKEN FROM TCALL prev WHERE prev.CALLONOBJECTID = tcall.CALLONOBJECTID AND prev.CALLONOBJECTTYPEID = -1200000000 AND prev.CALLDATE <= tcall.CALLDATE ORDER BY prev.CALLDATE DESC ) prev -- Last maintenance task OUTER APPLY ( SELECT TOP 1 prevTask.ACTUALENDON FROM TTASK prevTask WHERE prevTask.ASSETID = tcall.CALLONOBJECTID AND prevTask.ACTUALENDON <= tcall.CALLDATE ORDER BY prevTask.ACTUALENDON DESC ) prevTask -- Action detail remarks (concatenated) OUTER APPLY ( SELECT STUFF(( SELECT ',' + CAST(REMARKS AS VARCHAR(MAX)) FROM TACTIONDETAIL WHERE CALLID = tcall.CALLID FOR XML PATH('') ), 1, 1, '') AS REMARKS ) actiondetail -- Action time range OUTER APPLY ( SELECT MIN(PERIODFROM) AS firststart, MAX(PERIODTO) AS Lastend FROM TACTIONDETAIL WHERE CALLID = tcall.CALLID ) actionTaken -- Reference joins LEFT JOIN MBIZTRANSACTIONTYPE biztype ON tcall.BIZTRANSACTIONTYPEID = biztype.BIZTRANSACTIONTYPEID LEFT JOIN MPARTY mparty ON tcall.PARTYID = mparty.PARTYID LEFT JOIN MPARTYBRANCH mpartybranch ON tcall.PARTYBRANCHID = mpartybranch.PARTYBRANCHID LEFT JOIN MADDRESS maddress ON maddress.ADDRESSID = mpartybranch.DEFAULTADDRESSID LEFT JOIN MLOCATION mloc ON maddress.LOCATIONID = mloc.LOCATIONID LEFT JOIN MREGION reg ON maddress.STANDARDREGIONID = reg.REGIONID LEFT JOIN MCITY city ON maddress.CITYID = city.CITYID LEFT JOIN MEMPLOYEE memployee ON tcall.EMPLOYEEID = memployee.EMPLOYEEID LEFT JOIN MDEPARTMENT dept ON memployee.DEPARTMENTID = dept.DEPARTMENTID LEFT JOIN MCONTACT mcontact ON tcall.CONTACTID = mcontact.CONTACTID LEFT JOIN MEMPLOYEE reciveemp ON tcall.RECEIVEDBYID = reciveemp.EMPLOYEEID LEFT JOIN MGCM calltypegcm ON tcall.CALLTYPEID = calltypegcm.GCMID LEFT JOIN MGCM callnaturegcm ON tcall.CALLNATUREID = callnaturegcm.GCMID LEFT JOIN MGCM callprioritygcm ON tcall.CALLPRIORITYID = callprioritygcm.GCMID LEFT JOIN MEMPLOYEE allotedtoperson ON tcall.ALLOTEDTOEMPLOYEEID = allotedtoperson.EMPLOYEEID LEFT JOIN MCONTACT allotedcontact ON tcall.ALLOTEDTOCONTACTID = allotedcontact.CONTACTID LEFT JOIN MPARTY allotedtoparty ON tcall.ALLOTEDTOPARTYID = allotedtoparty.PARTYID LEFT JOIN MGCM problemnature ON tcall.PROBLEMNATUREID = problemnature.GCMID LEFT JOIN MEMPLOYEE estimatedby ON tcall.ESTIMATEDBYID = estimatedby.EMPLOYEEID LEFT JOIN MEMPLOYEE person ON tcall.PERSONID = person.EMPLOYEEID LEFT JOIN MEMPLOYEE completedby ON tcall.COMPLETEDBYID = completedby.EMPLOYEEID LEFT JOIN MEMPLOYEE closedby ON tcall.CLOSEDBYID = closedby.EMPLOYEEID LEFT JOIN MREASON mreason ON tcall.REASONID = mreason.REASONID LEFT JOIN MREASON rootreason ON tcall.ROOTCAUSEID = rootreason.REASONID LEFT JOIN MALLOCATION mallocation ON tcall.ALLOCATIONID = mallocation.ALLOCATIONID LEFT JOIN MORGANIZATIONUNIT ou ON tcall.OUID = ou.OUID LEFT JOIN MUSER ucreated ON tcall.CREATEDBYID = ucreated.USERID LEFT JOIN MUSER umodified ON tcall.MODIFIEDBYID = umodified.USERID -- Object-type-specific joins LEFT JOIN MMACHINE machine ON tcall.CALLONOBJECTID = machine.MACHINEID AND tcall.CALLONOBJECTTYPEID = -1199999999 LEFT JOIN MPARTYPRODUCT partyproduct ON tcall.CALLONOBJECTID = partyproduct.PARTYPRODUCTID AND tcall.CALLONOBJECTTYPEID = -1399998989 LEFT JOIN MITEM item ON partyproduct.ITEMID = item.ITEMID LEFT JOIN MMODEL itemmodel ON item.MODELID = itemmodel.MODELID LEFT JOIN MITEMCATEGORY category ON item.CATEGORYID = category.ITEMCATEGORYID LEFT JOIN MITEMSUBCATEGORY subcategory ON item.SUBCATEGORYID = subcategory.ITEMSUBCATEGORYID LEFT JOIN MITEMGROUP mgroup ON tcall.CALLONOBJECTID = mgroup.ITEMGROUPID LEFT JOIN MMODEL model ON item.MODELID = model.MODELID LEFT JOIN MITEMBRAND brand ON item.ITEMBRANDID = brand.ITEMBRANDID LEFT JOIN MASSET mas ON tcall.CALLONOBJECTID = mas.ASSETID AND tcall.CALLONOBJECTTYPEID = -1200000000 LEFT JOIN MDEPARTMENT asdept ON mas.DEPARTMENTID = asdept.DEPARTMENTID LEFT JOIN MPARTYBRANCHLOCATION loc ON loc.PARTYBRANCHLOCATIONID = mas.LOCATIONID WHERE 1 = 1 AND tcall.OUID = @OUId AND tcall.CALLDATE >= @PeriodFromDate AND tcall.CALLDATE <= @PeriodToDate AND (@CallId = 0 OR tcall.CALLID = @CallId) AND (@CallStatusFilterOff = 1 OR tcall.CALLSTATUS IN @CallStatus) AND (@AllotedToEmployeeId = -1 OR tcall.ALLOTEDTOEMPLOYEEID = @AllotedToEmployeeId) AND (@AllotedToContactId = -1 OR tcall.ALLOTEDTOCONTACTID = @AllotedToContactId) AND (@AllotedToPartyId = -1 OR tcall.ALLOTEDTOPARTYID = @AllotedToPartyId) AND (@CallOnObjectTypeId = -1 OR tcall.CALLONOBJECTTYPEID = @CallOnObjectTypeId) AND (@PartyProductId = -1 OR tcall.CALLONOBJECTID = @PartyProductId)"; } }