namespace QMSDAL.Query.PcsCard { // INDEX RECOMMENDATION: // IX_TPCSCARD_OUID_PERIOD_STATUS ON TPCSCARD (OUID, PERIODID, OVERALLSTATUS, STATUS) // INCLUDE (CARDNO, SERIALNO, ITEMID, SKUID, CUSTOMERID, COMPLETEDSTAGES, TOTALSTAGES, // CURRENTSTAGE, PLANNEDEND) — covers primary dashboard + list view // IX_TPCSCARD_SERIALNO ON TPCSCARD (SERIALNO, TENANTID) // — handheld serial-number scan, must be sub-millisecond // IX_TPCSCARD_CUSTOMERID ON TPCSCARD (CUSTOMERID, TENANTID, OVERALLSTATUS) public static class PcsCardQB { public const string GET_PCSCARD = @" SELECT c.PCSCARDID AS PcsCardId, c.OUID AS OuId, c.PERIODID AS PeriodId, c.BIZTRANSACTIONTYPEID AS BizTransactionTypeId, c.CARDNO AS CardNo, c.INDENTID AS IndentId, c.INDENTDETAILID AS IndentDetailId, c.ITEMID AS ItemId, c.SKUID AS SkuId, c.ROUTINGID AS RoutingId, c.ROUTINGVERSIONID AS RoutingVersionId, c.SERIALNO AS SerialNo, c.CUSTOMERSERIAL AS CustomerSerial, c.SETSLNO AS SetSlNo, c.CUSTOMERID AS CustomerId, c.PROJECTNAME AS ProjectName, c.CUSTOMERPO AS CustomerPo, c.PLANNEDQUANTITY AS PlannedQuantity, c.UOMID AS UomId, c.COLOURCODE AS ColourCode, c.DRAWINGNO AS DrawingNo, c.DRAWINGREV AS DrawingRev, c.PLANNEDSTART AS PlannedStart, c.PLANNEDEND AS PlannedEnd, c.ACTUALSTART AS ActualStart, c.ACTUALEND AS ActualEnd, c.TOTALSTAGES AS TotalStages, c.COMPLETEDSTAGES AS CompletedStages, c.CURRENTSTAGE AS CurrentStage, c.OVERALLSTATUS AS OverallStatus, c.OVERALLRESULT AS OverallResult, c.REMARKS AS Remarks, c.VERSION AS Version, c.STATUS AS Status, c.TENANTID AS TenantId, c.CREATEDBYID AS CreatedById, c.CREATEDON AS CreatedOn, c.MODIFIEDBYID AS ModifiedById, c.MODIFIEDON AS ModifiedOn, i.ITEMNAME AS ItemName, s.SKUCODE AS VariantCode, p.PARTYNAME AS CustomerName, u.UOMNAME AS UomName FROM TPCSCARD c INNER JOIN MITEM i ON c.ITEMID = i.ITEMID INNER JOIN MSKU s ON c.SKUID = s.SKUID INNER JOIN MPARTY p ON c.CUSTOMERID = p.PARTYID LEFT JOIN MUOM u ON c.UOMID = u.UOMID WHERE c.PCSCARDID = @pcscardid AND c.TENANTID = @tenantid AND c.STATUS <> 2 "; public const string GET_PCSCARD_BY_SERIALNO = @" SELECT c.PCSCARDID AS PcsCardId, c.OUID AS OuId, c.PERIODID AS PeriodId, c.BIZTRANSACTIONTYPEID AS BizTransactionTypeId, c.CARDNO AS CardNo, c.INDENTID AS IndentId, c.INDENTDETAILID AS IndentDetailId, c.ITEMID AS ItemId, c.SKUID AS SkuId, c.ROUTINGID AS RoutingId, c.ROUTINGVERSIONID AS RoutingVersionId, c.SERIALNO AS SerialNo, c.CUSTOMERSERIAL AS CustomerSerial, c.SETSLNO AS SetSlNo, c.CUSTOMERID AS CustomerId, c.PROJECTNAME AS ProjectName, c.CUSTOMERPO AS CustomerPo, c.PLANNEDQUANTITY AS PlannedQuantity, c.UOMID AS UomId, c.COLOURCODE AS ColourCode, c.DRAWINGNO AS DrawingNo, c.DRAWINGREV AS DrawingRev, c.PLANNEDSTART AS PlannedStart, c.PLANNEDEND AS PlannedEnd, c.ACTUALSTART AS ActualStart, c.ACTUALEND AS ActualEnd, c.TOTALSTAGES AS TotalStages, c.COMPLETEDSTAGES AS CompletedStages, c.CURRENTSTAGE AS CurrentStage, c.OVERALLSTATUS AS OverallStatus, c.OVERALLRESULT AS OverallResult, c.REMARKS AS Remarks, c.VERSION AS Version, c.STATUS AS Status, c.TENANTID AS TenantId, c.CREATEDBYID AS CreatedById, c.CREATEDON AS CreatedOn, c.MODIFIEDBYID AS ModifiedById, c.MODIFIEDON AS ModifiedOn, i.ITEMNAME AS ItemName, s.SKUCODE AS VariantCode, p.PARTYNAME AS CustomerName, u.UOMNAME AS UomName FROM TPCSCARD c INNER JOIN MITEM i ON c.ITEMID = i.ITEMID INNER JOIN MSKU s ON c.SKUID = s.SKUID INNER JOIN MPARTY p ON c.CUSTOMERID = p.PARTYID LEFT JOIN MUOM u ON c.UOMID = u.UOMID WHERE c.SERIALNO = @SerialNo AND c.TENANTID = @TenantId AND c.STATUS <> 2"; // Dynamic list query — sort column and direction injected by DAL after whitelist check. // Whitelist: CARDNO, SERIALNO, PLANNEDEND, OVERALLSTATUS, COMPLETEDSTAGES public const string GET_PCSCARD_LIST = @" SELECT c.PCSCARDID AS PcsCardId, c.CARDNO AS CardNo, c.SERIALNO AS SerialNo, c.CUSTOMERSERIAL AS CustomerSerial, i.ITEMNAME AS ItemName, s.SKUCODE AS VariantCode, p.PARTYNAME AS CustomerName, c.PROJECTNAME AS ProjectName, c.COLOURCODE AS ColourCode, ti.INDENTNUMBER AS WoRef, c.PLANNEDEND AS PlannedEnd, c.TOTALSTAGES AS TotalStages, c.COMPLETEDSTAGES AS CompletedStages, c.CURRENTSTAGE AS CurrentStage, c.OVERALLSTATUS AS OverallStatus, c.OVERALLRESULT AS OverallResult, COUNT(*) OVER() AS TotalCount FROM TPCSCARD c INNER JOIN MITEM i ON c.ITEMID = i.ITEMID INNER JOIN MSKU s ON c.SKUID = s.SKUID INNER JOIN MPARTY p ON c.CUSTOMERID = p.PARTYID INNER JOIN TINDENT ti ON c.INDENTID = ti.INDENTID WHERE c.TENANTID = @TenantId AND c.STATUS <> 2 AND c.OUID = @OuId AND c.PERIODID = @PeriodId {DYNAMIC_FILTERS} ORDER BY {SORT_COL} {SORT_DIR} OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY"; public const string SAVE_PCSCARD = @" INSERT INTO TPCSCARD ( PCSCARDID, OUID, PERIODID, BIZTRANSACTIONTYPEID, CARDNO, INDENTID, INDENTDETAILID, ITEMID, SKUID, ROUTINGID, ROUTINGVERSIONID, SERIALNO, CUSTOMERSERIAL, SETSLNO, CUSTOMERID, PROJECTNAME, CUSTOMERPO, PLANNEDQUANTITY, UOMID, COLOURCODE, DRAWINGNO, DRAWINGREV, PLANNEDSTART, PLANNEDEND, ACTUALSTART, ACTUALEND, TOTALSTAGES, COMPLETEDSTAGES, CURRENTSTAGE, OVERALLSTATUS, OVERALLRESULT, REMARKS, VERSION, STATUS, SORTORDER, TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE ) VALUES ( @PcsCardId, @OuId, @PeriodId, @BizTransactionTypeId, @CardNo, @IndentId, @IndentDetailId, @ItemId, @SkuId, @RoutingId, @RoutingVersionId, @SerialNo, @CustomerSerial, @SetSlNo, @CustomerId, @ProjectName, @CustomerPo, @PlannedQuantity, @UomId, @ColourCode, @DrawingNo, @DrawingRev, @PlannedStart, @PlannedEnd, NULL, NULL, @TotalStages, 0, 1, 0, 0, @Remarks, 0, 1, 9999, @TenantId, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, 5 )"; public const string UPDATE_PCSCARD = @" UPDATE TPCSCARD SET INDENTID = @IndentId, INDENTDETAILID = @IndentDetailId, CUSTOMERID = @CustomerId, PROJECTNAME = @ProjectName, CUSTOMERPO = @CustomerPo, PLANNEDQUANTITY = @PlannedQuantity, UOMID = @UomId, COLOURCODE = @ColourCode, DRAWINGNO = @DrawingNo, DRAWINGREV = @DrawingRev, PLANNEDSTART = @PlannedStart, PLANNEDEND = @PlannedEnd, SERIALNO = @SerialNo, CUSTOMERSERIAL = @CustomerSerial, SETSLNO = @SetSlNo, REMARKS = @Remarks, VERSION = VERSION + 1, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETUTCDATE() WHERE PCSCARDID = @PcsCardId AND TENANTID = @TenantId AND VERSION = @Version"; public const string UPDATE_PCSCARD_PROGRESS = @" UPDATE TPCSCARD SET COMPLETEDSTAGES = @CompletedStages, OVERALLSTATUS = @OverallStatus, OVERALLRESULT = @OverallResult, ACTUALEND = @ActualEnd, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETUTCDATE() WHERE PCSCARDID = @PcsCardId AND TENANTID = @TenantId"; public const string UPDATE_PCSCARD_ACTUALSTART = @" UPDATE TPCSCARD SET ACTUALSTART = GETUTCDATE(), OVERALLSTATUS = 1, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETUTCDATE() WHERE PCSCARDID = @PcsCardId AND TENANTID = @TenantId AND ACTUALSTART IS NULL"; public const string DELETE_PCSCARD = @" BEGIN TRANSACTION; BEGIN TRY /* Delete Materials */ DELETE M FROM TPCSCARDMATERIAL M INNER JOIN TPCSCARDSTAGE S ON S.PCSCARDSTAEID = M.PCSCARDSTAEID WHERE S.PCSCARDID = @PcsCardId; /* Delete Stages */ DELETE FROM TPCSCARDSTAGE WHERE PCSCARDID = @PcsCardId; /* Delete PCS Card */ DELETE FROM TPCSCARD WHERE PCSCARDID = @PcsCardId AND TENANTID = @TenantId; COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK TRANSACTION; THROW; END CATCH;"; public const string SET_CARD_STATUS = @" UPDATE TPCSCARD SET OVERALLSTATUS = @OverallStatus, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETUTCDATE() WHERE PCSCARDID = @PcsCardId AND TENANTID = @TenantId"; } }