namespace MMDAL.Query.Indent { // Required indexes (Migration 000 — already applied via Phase 0): // TPROCESSGROUP: IX_TPROCESSGROUP_OUID_TYPE (OUID, GROUPTYPE, STATUS, TENANTID) // TPROCESSGROUPDETAIL: (no additional index needed — FK on PROCESSGROUPID covers lookup) public static class ProcessGroupTransactionQB { // ── Get CWO header ──────────────────────────────────────────────────── public const string GET_CWO_SQL = @" SELECT pg.PROCESSGROUPID AS ProcessGroupId, pg.PROCESSGROUPNUMBER AS ProcessGroupNumber, pg.PROCESSGROUPDATE AS ProcessGroupDate, pg.BIZTRANSACTIONTYPEID AS BizTransactionTypeId, bt.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName, pg.OUID AS OUId, ou.ORGANIZATIONUNITNAME AS OUName, pg.PERIODID AS PeriodId, pd.PERIODNAME AS PeriodName, pg.PROCESSID AS ProcessId, ISNULL(pr.PROCESSNAME,'') AS ProcessName, pg.GROUPEDBYID AS GroupedById, pg.GROUPTYPE AS GroupType, pg.PRIORITY AS Priority, ISNULL(pg.REMARKS,'') AS Remarks, pg.TOTALGROUPINPUTQUANTITY AS TotalGroupInputQuantity, pg.TOTALGROUPOUTPUTQUANTITY AS TotalGroupOutputQuantity, pg.STATUS AS Status, pg.VERSION AS Version, pg.CREATEDON AS CreatedOn, pg.MODIFIEDON AS ModifiedOn FROM TPROCESSGROUP pg JOIN MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = pg.BIZTRANSACTIONTYPEID JOIN MORGANIZATIONUNIT ou ON ou.OUID = pg.OUID JOIN MPERIOD pd ON pd.PERIODID = pg.PERIODID LEFT JOIN MPROCESS pr ON pr.PROCESSID = pg.PROCESSID WHERE pg.PROCESSGROUPID = @ProcessGroupId AND pg.STATUS <> 2;"; public const string GET_CWO_PG = @" SELECT pg.PROCESSGROUPID AS ""ProcessGroupId"", pg.PROCESSGROUPNUMBER AS ""ProcessGroupNumber"", pg.PROCESSGROUPDATE AS ""ProcessGroupDate"", pg.BIZTRANSACTIONTYPEID AS ""BizTransactionTypeId"", bt.BIZTRANSACTIONTYPENAME AS ""BizTransactionTypeName"", pg.OUID AS ""OUId"", ou.OUNAME AS ""OUName"", pg.PERIODID AS ""PeriodId"", pd.PERIODNAME AS ""PeriodName"", pg.PROCESSID AS ""ProcessId"", COALESCE(pr.PROCESSNAME,'') AS ""ProcessName"", pg.GROUPEDBYID AS ""GroupedById"", pg.GROUPTYPE AS ""GroupType"", pg.PRIORITY AS ""Priority"", COALESCE(pg.REMARKS,'') AS ""Remarks"", pg.TOTALGROUPINPUTQUANTITY AS ""TotalGroupInputQuantity"", pg.TOTALGROUPOUTPUTQUANTITY AS ""TotalGroupOutputQuantity"", pg.STATUS AS ""Status"", pg.VERSION AS ""Version"", pg.CREATEDON AS ""CreatedOn"", pg.MODIFIEDON AS ""ModifiedOn"" FROM TPROCESSGROUP pg JOIN MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = pg.BIZTRANSACTIONTYPEID JOIN MORGANIZATIONUNIT ou ON ou.OUID = pg.OUID JOIN MPERIOD pd ON pd.PERIODID = pg.PERIODID LEFT JOIN MPROCESS pr ON pr.PROCESSID = pg.PROCESSID WHERE pg.PROCESSGROUPID = :ProcessGroupId AND pg.STATUS <> 2;"; // ── Get CWO detail lines ────────────────────────────────────────────── public const string GET_CWO_DETAILS_SQL = @" SELECT pgd.PROCESSGROUPDETAILID AS ProcessGroupDetailId, pgd.PROCESSGROUPID AS ProcessGroupId, pgd.SLNO AS SlNo, pgd.DOCUMENTDETAILID AS DocumentDetailId, pgd.INDENTMATERIALID AS IndentMaterialId, pgd.INDENTDETAILID AS IndentDetailId, pgd.SKUID AS SkuId, pgd.PROCESSINPUTQUANTITY AS ProcessInputQuantity, pgd.PROCESSOUTPUTQUANTITY AS ProcessOutputQuantity FROM TPROCESSGROUPDETAIL pgd WHERE pgd.PROCESSGROUPID = @ProcessGroupId ORDER BY pgd.SLNO;"; public const string GET_CWO_DETAILS_PG = @" SELECT pgd.PROCESSGROUPDETAILID AS ""ProcessGroupDetailId"", pgd.PROCESSGROUPID AS ""ProcessGroupId"", pgd.SLNO AS ""SlNo"", pgd.DOCUMENTDETAILID AS ""DocumentDetailId"", pgd.INDENTMATERIALID AS ""IndentMaterialId"", pgd.INDENTDETAILID AS ""IndentDetailId"", pgd.SKUID AS ""SkuId"", pgd.PROCESSINPUTQUANTITY AS ""ProcessInputQuantity"", pgd.PROCESSOUTPUTQUANTITY AS ""ProcessOutputQuantity"" FROM TPROCESSGROUPDETAIL pgd WHERE pgd.PROCESSGROUPID = :ProcessGroupId ORDER BY pgd.SLNO;"; // ── Paged CWO list ──────────────────────────────────────────────────── public const string GET_CWO_LIST_SQL = @" SELECT pg.PROCESSGROUPID AS ProcessGroupId, pg.PROCESSGROUPNUMBER AS ProcessGroupNumber, pg.PROCESSGROUPDATE AS ProcessGroupDate, bt.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName, ou.ORGANIZATIONUNITNAME AS OUName, ISNULL(pr.PROCESSNAME,'') AS ProcessName, pg.GROUPTYPE AS GroupType, pg.PRIORITY AS Priority, pg.TOTALGROUPINPUTQUANTITY AS TotalGroupInputQuantity, pg.TOTALGROUPOUTPUTQUANTITY AS TotalGroupOutputQuantity, pg.STATUS AS Status, COUNT(pgd.PROCESSGROUPDETAILID) AS DetailCount, pg.CREATEDON AS CreatedOn FROM TPROCESSGROUP pg JOIN MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = pg.BIZTRANSACTIONTYPEID JOIN MORGANIZATIONUNIT ou ON ou.OUID = pg.OUID LEFT JOIN MPROCESS pr ON pr.PROCESSID = pg.PROCESSID LEFT JOIN TPROCESSGROUPDETAIL pgd ON pgd.PROCESSGROUPID = pg.PROCESSGROUPID WHERE pg.OUID = @OUId AND pg.GROUPTYPE = 2 AND pg.STATUS <> 2 GROUP BY pg.PROCESSGROUPID, pg.PROCESSGROUPNUMBER, pg.PROCESSGROUPDATE, bt.BIZTRANSACTIONTYPENAME, ou.ORGANIZATIONUNITNAME, pr.PROCESSNAME, pg.GROUPTYPE, pg.PRIORITY, pg.TOTALGROUPINPUTQUANTITY, pg.TOTALGROUPOUTPUTQUANTITY, pg.STATUS, pg.CREATEDON ORDER BY pg.PROCESSGROUPDATE DESC, pg.PROCESSGROUPID DESC OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY;"; public const string GET_CWO_LIST_COUNT_SQL = @" SELECT COUNT(*) FROM TPROCESSGROUP pg WHERE pg.OUID = @OUId AND pg.GROUPTYPE = 2 AND pg.STATUS <> 2;"; public const string GET_CWO_LIST_PG = @" SELECT pg.PROCESSGROUPID AS ""ProcessGroupId"", pg.PROCESSGROUPNUMBER AS ""ProcessGroupNumber"", pg.PROCESSGROUPDATE AS ""ProcessGroupDate"", bt.BIZTRANSACTIONTYPENAME AS ""BizTransactionTypeName"", ou.OUNAME AS ""OUName"", COALESCE(pr.PROCESSNAME,'') AS ""ProcessName"", pg.GROUPTYPE AS ""GroupType"", pg.PRIORITY AS ""Priority"", pg.TOTALGROUPINPUTQUANTITY AS ""TotalGroupInputQuantity"", pg.TOTALGROUPOUTPUTQUANTITY AS ""TotalGroupOutputQuantity"", pg.STATUS AS ""Status"", COUNT(pgd.PROCESSGROUPDETAILID) AS ""DetailCount"", pg.CREATEDON AS ""CreatedOn"" FROM TPROCESSGROUP pg JOIN MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = pg.BIZTRANSACTIONTYPEID JOIN MORGANIZATIONUNIT ou ON ou.OUID = pg.OUID LEFT JOIN MPROCESS pr ON pr.PROCESSID = pg.PROCESSID LEFT JOIN TPROCESSGROUPDETAIL pgd ON pgd.PROCESSGROUPID = pg.PROCESSGROUPID WHERE pg.OUID = :OUId AND pg.GROUPTYPE = 2 AND pg.STATUS <> 2 GROUP BY pg.PROCESSGROUPID, pg.PROCESSGROUPNUMBER, pg.PROCESSGROUPDATE, bt.BIZTRANSACTIONTYPENAME, ou.OUNAME, pr.PROCESSNAME, pg.GROUPTYPE, pg.PRIORITY, pg.TOTALGROUPINPUTQUANTITY, pg.TOTALGROUPOUTPUTQUANTITY, pg.STATUS, pg.CREATEDON ORDER BY pg.PROCESSGROUPDATE DESC, pg.PROCESSGROUPID DESC LIMIT :MaxResult OFFSET :FirstNumber;"; public const string GET_CWO_LIST_COUNT_PG = @" SELECT COUNT(*) FROM TPROCESSGROUP pg WHERE pg.OUID = :OUId AND pg.GROUPTYPE = 2 AND pg.STATUS <> 2;"; // ── Save CWO header ─────────────────────────────────────────────────── public const string SAVE_CWO_SQL = @"INSERT INTO TPROCESSGROUP ( PROCESSGROUPID, BIZTRANSACTIONTYPEID, OUID, PERIODID, PROCESSGROUPNUMBER, PROCESSGROUPDATE, REFERENCENUMBER, REFERENCEDATE, PROCESSID, GROUPEDBYID, GROUPTYPE, REMARKS, TOTALGROUPINPUTQUANTITY, TOTALGROUPOUTPUTQUANTITY, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, PRIORITY ) VALUES ( @ProcessGroupId, @BizTransactionTypeId, @OUId, @PeriodId, @ProcessGroupNumber, @ProcessGroupDate, @ReferenceNumber, @ReferenceDate, @ProcessId, @GroupedById, @GroupType, @Remarks, @TotalGroupInputQuantity, @TotalGroupOutputQuantity, @Status, @Version, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @Priority );"; public const string SAVE_CWO_PG = @" INSERT INTO TPROCESSGROUP (PROCESSGROUPID, BIZTRANSACTIONTYPEID, OUID, PERIODID, PROCESSGROUPNUMBER, PROCESSGROUPDATE, REFERENCENUMBER, REFERENCEDATE, PROCESSID, GROUPEDBYID, GROUPTYPE, PRIORITY, TOTALGROUPINPUTQUANTITY, TOTALGROUPOUTPUTQUANTITY, REMARKS, STATUS, VERSION, TENANTID) VALUES (:ProcessGroupId, :BizTransactionTypeId, :OUId, :PeriodId, :ProcessGroupNumber, :ProcessGroupDate, :ReferenceNumber, :ReferenceDate, :ProcessId, :GroupedById, :GroupType, :Priority, :TotalGroupInputQuantity, :TotalGroupOutputQuantity, :Remarks, 1, 0, :TenantId);"; public const string UPDATE_CWO_SQL = @"UPDATE TPROCESSGROUP SET BIZTRANSACTIONTYPEID = @BizTransactionTypeId, OUID = @OUId, PERIODID = @PeriodId, PROCESSGROUPNUMBER = @ProcessGroupNumber, PROCESSGROUPDATE = @ProcessGroupDate, REFERENCENUMBER = @ReferenceNumber, REFERENCEDATE = @ReferenceDate, PROCESSID = @ProcessId, GROUPEDBYID = @GroupedById, GROUPTYPE = @GroupType, REMARKS = @Remarks, TOTALGROUPINPUTQUANTITY = @TotalGroupInputQuantity, TOTALGROUPOUTPUTQUANTITY = @TotalGroupOutputQuantity, STATUS = @Status, VERSION = @Version, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn, PRIORITY = @Priority WHERE PROCESSGROUPID = @ProcessGroupId;"; public const string UPDATE_CWO_PG = @" UPDATE TPROCESSGROUP SET BIZTRANSACTIONTYPEID = :BizTransactionTypeId, PERIODID = :PeriodId, PROCESSGROUPDATE = :ProcessGroupDate, REFERENCENUMBER = :ReferenceNumber, REFERENCEDATE = :ReferenceDate, PROCESSID = :ProcessId, GROUPEDBYID = :GroupedById, PRIORITY = :Priority, TOTALGROUPINPUTQUANTITY = :TotalGroupInputQuantity, TOTALGROUPOUTPUTQUANTITY = :TotalGroupOutputQuantity, REMARKS = :Remarks, VERSION = VERSION + 1 WHERE PROCESSGROUPID = :ProcessGroupId;"; // ── Save CWO detail ─────────────────────────────────────────────────── public const string SAVE_CWO_DETAIL_SQL = @"INSERT INTO TPROCESSGROUPDETAIL ( PROCESSGROUPDETAILID, PROCESSGROUPID, SLNO, DOCUMENTDETAILID, INDENTMATERIALID, PROCESSINPUTQUANTITY, PROCESSOUTPUTQUANTITY, INDENTDETAILID, SKUID, SEQUENCEORDER ) VALUES ( @ProcessGroupDetailId, @ProcessGroupId, @SlNo, @DocumentDetailId, @IndentMaterialId, @ProcessInputQuantity, @ProcessOutputQuantity, @IndentDetailId, @SkuId, @SequenceOrder );"; public const string SAVE_CWO_DETAIL_PG = @" INSERT INTO TPROCESSGROUPDETAIL (PROCESSGROUPDETAILID, PROCESSGROUPID, SLNO, DOCUMENTDETAILID, INDENTMATERIALID, INDENTDETAILID, SKUID, PROCESSINPUTQUANTITY, PROCESSOUTPUTQUANTITY, SEQUENCEORDER, TENANTID) VALUES (:ProcessGroupDetailId, :ProcessGroupId, :SlNo, :DocumentDetailId, :IndentMaterialId, :IndentDetailId, :SkuId, :ProcessInputQuantity, :ProcessOutputQuantity, :SequenceOrder, :TenantId);"; // ── Soft delete CWO ─────────────────────────────────────────────────── public const string SOFT_DELETE_CWO_SQL = @" UPDATE TPROCESSGROUP SET STATUS = 2 WHERE PROCESSGROUPID = @ProcessGroupId;"; public const string SOFT_DELETE_CWO_PG = @" UPDATE TPROCESSGROUP SET STATUS = 2 WHERE PROCESSGROUPID = :ProcessGroupId;"; public const string DELETE_CWO_DETAILS_SQL = @" DELETE FROM TPROCESSGROUPDETAIL WHERE PROCESSGROUPID = @ProcessGroupId;"; public const string DELETE_CWO_DETAILS_PG = @" DELETE FROM TPROCESSGROUPDETAIL WHERE PROCESSGROUPID = :ProcessGroupId;"; } }