namespace MMDAL.Query.ProductionPlan { // Required indexes (add via DB migration): // TPRODUCTIONPLAN: IX_TPRODUCTIONPLAN_OUID_DATE (OUID, PRODUCTIONPLANDATE, TENANTID) // TPRODUCTIONPLANDETAIL: IX_TPRODUCTIONPLANDETAIL_PLAN (PRODUCTIONPLANID, TENANTID) public static class ProductionPlanQB { // ── Get header ───────────────────────────────────────────────────────── public const string GET_PRODUCTION_PLAN_SQL = @" SELECT p.PRODUCTIONPLANID AS ProductionPlanId, p.BIZTRANSACTIONTYPEID AS BizTransactionTypeId, bt.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName, p.OUID AS OUId, ou.ORGANIZATIONUNITNAME AS OUName, p.PERIODID AS PeriodId, pd.PERIODNAME AS PeriodName, p.PROCESSID AS ProcessId, ISNULL(pr.PROCESSNAME, '') AS ProcessName, p.WORKCENTERID AS WorkCenterId, ISNULL(wc.WORKCENTERNAME, '') AS WorkCenterName, p.PRODUCTIONPLANNUMBER AS ProductionPlanNumber, p.PRODUCTIONPLANDATE AS ProductionPlanDate, p.REFERENCENUMBER AS ReferenceNumber, p.REFERENCEDATE AS ReferenceDate, p.PLANNEDBYID AS PlannedById, ISNULL(emp.EMPLOYEENAME, '') AS PlannedByName, p.TOTALNUMBEROFBATCHES AS TotalNumberOfBatches, p.TOTALBATCHQUANTITY AS TotalBatchQuantity, ISNULL(p.REMARKS, '') AS Remarks, p.STATUS AS Status, p.VERSION AS Version, p.CREATEDON AS CreatedOn, p.MODIFIEDON AS ModifiedOn FROM TPRODUCTIONPLAN p JOIN MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = p.BIZTRANSACTIONTYPEID JOIN MORGANIZATIONUNIT ou ON ou.OUID = p.OUID JOIN MPERIOD pd ON pd.PERIODID = p.PERIODID LEFT JOIN MPROCESS pr ON pr.PROCESSID = p.PROCESSID LEFT JOIN MWORKCENTER wc ON wc.WORKCENTERID = p.WORKCENTERID LEFT JOIN MEMPLOYEE emp ON emp.EMPLOYEEID = p.PLANNEDBYID WHERE p.PRODUCTIONPLANID = @ProductionPlanId AND p.STATUS <> 2"; public const string GET_PRODUCTION_PLAN_PG = @" SELECT p.PRODUCTIONPLANID AS ""ProductionPlanId"", p.BIZTRANSACTIONTYPEID AS ""BizTransactionTypeId"", bt.BIZTRANSACTIONTYPENAME AS ""BizTransactionTypeName"", p.OUID AS ""OUId"", ou.ORGANIZATIONUNITNAME AS ""OUName"", p.PERIODID AS ""PeriodId"", pd.PERIODNAME AS ""PeriodName"", p.PROCESSID AS ""ProcessId"", COALESCE(pr.PROCESSNAME, '') AS ""ProcessName"", p.WORKCENTERID AS ""WorkCenterId"", COALESCE(wc.WORKCENTERNAME, '') AS ""WorkCenterName"", p.PRODUCTIONPLANNUMBER AS ""ProductionPlanNumber"", p.PRODUCTIONPLANDATE AS ""ProductionPlanDate"", p.REFERENCENUMBER AS ""ReferenceNumber"", p.REFERENCEDATE AS ""ReferenceDate"", p.PLANNEDBYID AS ""PlannedById"", COALESCE(emp.EMPLOYEENAME, '') AS ""PlannedByName"", p.TOTALNUMBEROFBATCHES AS ""TotalNumberOfBatches"", p.TOTALBATCHQUANTITY AS ""TotalBatchQuantity"", COALESCE(p.REMARKS, '') AS ""Remarks"", p.STATUS AS ""Status"", p.VERSION AS ""Version"", p.CREATEDON AS ""CreatedOn"", p.MODIFIEDON AS ""ModifiedOn"" FROM TPRODUCTIONPLAN p JOIN MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = p.BIZTRANSACTIONTYPEID JOIN MORGANIZATIONUNIT ou ON ou.OUID = p.OUID JOIN MPERIOD pd ON pd.PERIODID = p.PERIODID LEFT JOIN MPROCESS pr ON pr.PROCESSID = p.PROCESSID LEFT JOIN MWORKCENTER wc ON wc.WORKCENTERID = p.WORKCENTERID LEFT JOIN MEMPLOYEE emp ON emp.EMPLOYEEID = p.PLANNEDBYID WHERE p.PRODUCTIONPLANID = :ProductionPlanId AND p.STATUS <> 2"; // ── Get details ───────────────────────────────────────────────────────── public const string GET_PRODUCTION_PLAN_DETAILS_SQL = @" SELECT d.PRODUCTIONPLANDETAILID AS ProductionPlanDetailId, d.PRODUCTIONPLANID AS ProductionPlanId, d.SLNO AS SlNo, d.SKUID AS SKUId, ISNULL(s.SKUNAME, '') AS SKUName, d.ITEMID AS ItemId, ISNULL(i.ITEMNAME, '') AS ItemName, d.MACHINEID AS MachineId, ISNULL(m.MACHINENAME, '') AS MachineName, d.MACHINETYPEID AS MachineTypeId, ISNULL(mt.MACHINETYPENAME, '') AS MachineTypeName, d.BATCHSIZE AS BatchSize, d.NUMBEROFBATCHES AS NumberOfBatches, d.TOTALBATCHQUANTITY AS BatchTotalQuantity, d.NUMBEROFDAYS AS NumberOfDays, d.FROMDATE AS FromDate, d.TODATE AS ToDate, d.PRIORITY AS Priority, d.PROCESSGROUPID AS ProcessGroupId, d.DOCUMENTDETAILID AS DocumentDetailId FROM TPRODUCTIONPLANDETAIL d LEFT JOIN MSKU s ON s.SKUID = d.SKUID LEFT JOIN MITEM i ON i.ITEMID = d.ITEMID LEFT JOIN MMACHINE m ON m.MACHINEID = d.MACHINEID LEFT JOIN MMACHINETYPE mt ON mt.MACHINETYPEID = d.MACHINETYPEID WHERE d.PRODUCTIONPLANID = @ProductionPlanId ORDER BY d.SLNO"; public const string GET_PRODUCTION_PLAN_DETAILS_PG = @" SELECT d.PRODUCTIONPLANDETAILID AS ""ProductionPlanDetailId"", d.PRODUCTIONPLANID AS ""ProductionPlanId"", d.SLNO AS ""SlNo"", d.SKUID AS ""SKUId"", COALESCE(s.SKUNAME, '') AS ""SKUName"", d.ITEMID AS ""ItemId"", COALESCE(i.ITEMNAME, '') AS ""ItemName"", d.MACHINEID AS ""MachineId"", COALESCE(m.MACHINENAME, '') AS ""MachineName"", d.MACHINETYPEID AS ""MachineTypeId"", COALESCE(mt.MACHINETYPENAME, '') AS ""MachineTypeName"", d.BATCHSIZE AS ""BatchSize"", d.NUMBEROFBATCHES AS ""NumberOfBatches"", d.TOTALBATCHQUANTITY AS ""BatchTotalQuantity"", d.NUMBEROFDAYS AS ""NumberOfDays"", d.FROMDATE AS ""FromDate"", d.TODATE AS ""ToDate"", d.PRIORITY AS ""Priority"", d.PROCESSGROUPID AS ""ProcessGroupId"", d.DOCUMENTDETAILID AS ""DocumentDetailId"" FROM TPRODUCTIONPLANDETAIL d LEFT JOIN MSKU s ON s.SKUID = d.SKUID LEFT JOIN MITEM i ON i.ITEMID = d.ITEMID LEFT JOIN MMACHINE m ON m.MACHINEID = d.MACHINEID LEFT JOIN MMACHINETYPE mt ON mt.MACHINETYPEID = d.MACHINETYPEID WHERE d.PRODUCTIONPLANID = :ProductionPlanId ORDER BY d.SLNO"; // ── Paged list ───────────────────────────────────────────────────────── public const string GET_PRODUCTION_PLAN_LIST_SQL = @" SELECT p.PRODUCTIONPLANID AS ProductionPlanId, p.PRODUCTIONPLANNUMBER AS ProductionPlanNumber, p.PRODUCTIONPLANDATE AS ProductionPlanDate, ou.ORGANIZATIONUNITNAME AS OUName, ISNULL(wc.WORKCENTERNAME, '') AS WorkCenterName, p.TOTALNUMBEROFBATCHES AS TotalNumberOfBatches, p.TOTALBATCHQUANTITY AS TotalBatchQuantity, p.STATUS AS Status FROM TPRODUCTIONPLAN p JOIN MORGANIZATIONUNIT ou ON ou.OUID = p.OUID LEFT JOIN MWORKCENTER wc ON wc.WORKCENTERID = p.WORKCENTERID WHERE p.STATUS <> 2 ORDER BY p.PRODUCTIONPLANDATE DESC, p.PRODUCTIONPLANID DESC OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY"; public const string GET_PRODUCTION_PLAN_LIST_COUNT_SQL = @" SELECT COUNT(1) FROM TPRODUCTIONPLAN p WHERE p.STATUS <> 2"; public const string GET_PRODUCTION_PLAN_LIST_PG = @" SELECT p.PRODUCTIONPLANID AS ""ProductionPlanId"", p.PRODUCTIONPLANNUMBER AS ""ProductionPlanNumber"", p.PRODUCTIONPLANDATE AS ""ProductionPlanDate"", ou.ORGANIZATIONUNITNAME AS ""OUName"", COALESCE(wc.WORKCENTERNAME, '') AS ""WorkCenterName"", p.TOTALNUMBEROFBATCHES AS ""TotalNumberOfBatches"", p.TOTALBATCHQUANTITY AS ""TotalBatchQuantity"", p.STATUS AS ""Status"" FROM TPRODUCTIONPLAN p JOIN MORGANIZATIONUNIT ou ON ou.OUID = p.OUID LEFT JOIN MWORKCENTER wc ON wc.WORKCENTERID = p.WORKCENTERID WHERE p.STATUS <> 2 ORDER BY p.PRODUCTIONPLANDATE DESC, p.PRODUCTIONPLANID DESC LIMIT :MaxResult OFFSET :FirstNumber"; public const string GET_PRODUCTION_PLAN_LIST_COUNT_PG = @" SELECT COUNT(1) FROM TPRODUCTIONPLAN p WHERE p.STATUS <> 2"; // ── Save ─────────────────────────────────────────────────────────────── public const string SAVE_PRODUCTION_PLAN_SQL = @"INSERT INTO TPRODUCTIONPLAN ( PRODUCTIONPLANID, PRODUCTIONPLANDATE, OUID, BIZTRANSACTIONTYPEID, PRODUCTIONPLANNUMBER, REFERENCENUMBER, REFERENCEDATE, PROCESSID, WORKCENTERID, PLANNEDBYID, TOTALNUMBEROFBATCHES, TOTALBATCHQUANTITY, REMARKS, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, PERIODID ) VALUES ( @ProductionPlanId, @ProductionPlanDate, @OUId, @BizTransactionTypeId, @ProductionPlanNumber, @ReferenceNumber, @ReferenceDate, @ProcessId, @WorkCenterId, @PlannedById, @TotalNumberOfBatches, @TotalBatchQuantity, @Remarks, @Status, @Version, @CreatedById, GETDATE(), @ModifiedById, GETDATE(), @PeriodId );"; public const string SAVE_PRODUCTION_PLAN_PG = @"INSERT INTO TPRODUCTIONPLAN ( PRODUCTIONPLANID, PRODUCTIONPLANDATE, OUID, BIZTRANSACTIONTYPEID, PRODUCTIONPLANNUMBER, REFERENCENUMBER, REFERENCEDATE, PROCESSID, WORKCENTERID, PLANNEDBYID, TOTALNUMBEROFBATCHES, TOTALBATCHQUANTITY, REMARKS, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, PERIODID ) VALUES ( :ProductionPlanId, :ProductionPlanDate, :OUId, :BizTransactionTypeId, :ProductionPlanNumber, :ReferenceNumber, :ReferenceDate, :ProcessId, :WorkCenterId, :PlannedById, :TotalNumberOfBatches, :TotalBatchQuantity, :Remarks, :Status, :Version, :CreatedById, NOW(), :ModifiedById, NOW(), :PeriodId ); "; public const string UPDATE_PRODUCTION_PLAN_SQL = @"UPDATE TPRODUCTIONPLAN SET PRODUCTIONPLANDATE = @ProductionPlanDate, OUID = @OUId, BIZTRANSACTIONTYPEID = @BizTransactionTypeId, PRODUCTIONPLANNUMBER = @ProductionPlanNumber, REFERENCENUMBER = @ReferenceNumber, REFERENCEDATE = @ReferenceDate, PROCESSID = @ProcessId, WORKCENTERID = @WorkCenterId, PLANNEDBYID = @PlannedById, TOTALNUMBEROFBATCHES = @TotalNumberOfBatches, TOTALBATCHQUANTITY = @TotalBatchQuantity, REMARKS = @Remarks, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETDATE(), PERIODID = @PeriodId WHERE PRODUCTIONPLANID = @ProductionPlanId;"; public const string UPDATE_PRODUCTION_PLAN_PG = @"UPDATE TPRODUCTIONPLAN SET PRODUCTIONPLANDATE = :ProductionPlanDate, OUID = :OUId, BIZTRANSACTIONTYPEID = :BizTransactionTypeId, PRODUCTIONPLANNUMBER = :ProductionPlanNumber, REFERENCENUMBER = :ReferenceNumber, REFERENCEDATE = :ReferenceDate, PROCESSID = :ProcessId, WORKCENTERID = :WorkCenterId, PLANNEDBYID = :PlannedById, TOTALNUMBEROFBATCHES = :TotalNumberOfBatches, TOTALBATCHQUANTITY = :TotalBatchQuantity, REMARKS = :Remarks, MODIFIEDBYID = :ModifiedById, MODIFIEDON = NOW(), PERIODID = :PeriodId WHERE PRODUCTIONPLANID = :ProductionPlanId;"; public const string SAVE_PRODUCTION_PLAN_DETAIL_SQL = @"INSERT INTO TPRODUCTIONPLANDETAIL ( PRODUCTIONPLANDETAILID, PRODUCTIONPLANID, SLNO, PRODUCTIONPLANTYPE, INDENTMATERIALID, PROCESSGROUPID, SKUID, MACHINEPLANTYPE, MACHINETYPEID, MACHINEID, BATCHSIZE, NUMBEROFBATCHES, TOTALBATCHQUANTITY, NUMBEROFDAYS, FROMDATE, TODATE, PRIORITY, ITEMID, PARTYBRANCHID, DURATION, DOCUMENTDETAILID ) VALUES ( @ProductionPlanDetailId, @ProductionPlanId, @SlNo, @ProductionPlanType, @IndentMaterialId, @ProcessGroupId, @SKUId, @MachinePlanType, @MachineTypeId, @MachineId, @BatchSize, @NumberOfBatches, @BatchTotalQuantity, @NumberOfDays, @FromDate, @ToDate, @Priority, @ItemId, @PartyBranchId, @Duration, @DocumentDetailId );"; public const string SAVE_PRODUCTION_PLAN_DETAIL_PG = @"INSERT INTO TPRODUCTIONPLANDETAIL ( PRODUCTIONPLANDETAILID, PRODUCTIONPLANID, SLNO, PRODUCTIONPLANTYPE, INDENTMATERIALID, PROCESSGROUPID, SKUID, MACHINEPLANTYPE, MACHINETYPEID, MACHINEID, BATCHSIZE, NUMBEROFBATCHES, TOTALBATCHQUANTITY, NUMBEROFDAYS, FROMDATE, TODATE, PRIORITY, ITEMID, PARTYBRANCHID, DURATION, DOCUMENTDETAILID ) VALUES ( :ProductionPlanDetailId, :ProductionPlanId, :SlNo, :ProductionPlanType, :IndentMaterialId, :ProcessGroupId, :SKUId, :MachinePlanType, :MachineTypeId, :MachineId, :BatchSize, :NumberOfBatches, :BatchTotalQuantity, :NumberOfDays, :FromDate, :ToDate, :Priority, :ItemId, :PartyBranchId, :Duration, :DocumentDetailId );"; public const string DELETE_PRODUCTION_PLAN_DETAILS_SQL = @" DELETE FROM TPRODUCTIONPLANDETAIL WHERE PRODUCTIONPLANID = @ProductionPlanId"; public const string DELETE_PRODUCTION_PLAN_DETAILS_PG = @" DELETE FROM TPRODUCTIONPLANDETAIL WHERE PRODUCTIONPLANID = :ProductionPlanId"; public const string SOFT_DELETE_PRODUCTION_PLAN_SQL = @" UPDATE TPRODUCTIONPLAN SET STATUS = 2, MODIFIEDON = GETUTCDATE(), MODIFIEDBYID = @ModifiedById WHERE PRODUCTIONPLANID = @ProductionPlanId"; public const string SOFT_DELETE_PRODUCTION_PLAN_PG = @" UPDATE TPRODUCTIONPLAN SET STATUS = 2, MODIFIEDON = NOW() AT TIME ZONE 'UTC', MODIFIEDBYID = :ModifiedById WHERE PRODUCTIONPLANID = :ProductionPlanId"; // ── Generate WO from plan ────────────────────────────────────────────── // Creates TINDENT header rows from TPRODUCTIONPLANDETAIL (one WO per detail line) public const string GET_PLAN_DETAILS_FOR_WO_GENERATION_SQL = @" SELECT d.PRODUCTIONPLANDETAILID AS ProductionPlanDetailId, d.SKUID AS SKUId, d.ITEMID AS ItemId, d.BATCHSIZE AS BatchSize, d.NUMBEROFBATCHES AS NumberOfBatches, d.TOTALBATCHQUANTITY AS BatchTotalQuantity, d.FROMDATE AS FromDate, d.TODATE AS ToDate, d.PRIORITY AS Priority FROM TPRODUCTIONPLANDETAIL d WHERE d.PRODUCTIONPLANID = @ProductionPlanId"; public const string GET_PLAN_DETAILS_FOR_WO_GENERATION_PG = @" SELECT d.PRODUCTIONPLANDETAILID AS ""ProductionPlanDetailId"", d.SKUID AS ""SKUId"", d.ITEMID AS ""ItemId"", d.BATCHSIZE AS ""BatchSize"", d.NUMBEROFBATCHES AS ""NumberOfBatches"", d.TOTALBATCHQUANTITY AS ""BatchTotalQuantity"", d.FROMDATE AS ""FromDate"", d.TODATE AS ""ToDate"", d.PRIORITY AS ""Priority"" FROM TPRODUCTIONPLANDETAIL d WHERE d.PRODUCTIONPLANID = :ProductionPlanId"; public const string LINK_PLAN_DETAIL_TO_INDENT_SQL = @" UPDATE TPRODUCTIONPLANDETAIL SET INDENTDETAILID = @IndentDetailId WHERE PRODUCTIONPLANDETAILID = @ProductionPlanDetailId"; public const string LINK_PLAN_DETAIL_TO_INDENT_PG = @" UPDATE TPRODUCTIONPLANDETAIL SET INDENTDETAILID = :IndentDetailId WHERE PRODUCTIONPLANDETAILID = :ProductionPlanDetailId"; // ── Select list — plan header (picklist: search plan by number) ──────── // TPRODUCTIONPLAN has no TENANTID column — tenant scoping goes via OUID -> MORGANIZATIONUNIT.TENANTID. public const string GET_SELECTLIST_PRODUCTION_PLAN_SQL = @" SELECT p.PRODUCTIONPLANID AS Id, p.PRODUCTIONPLANNUMBER AS Code, p.PRODUCTIONPLANNUMBER AS Number, p.PRODUCTIONPLANDATE AS ProductionPlanDate, ou.ORGANIZATIONUNITNAME AS OUName FROM TPRODUCTIONPLAN p JOIN MORGANIZATIONUNIT ou ON ou.OUID = p.OUID WHERE p.STATUS <> 2 AND ou.TENANTID = @TenantId ORDER BY p.PRODUCTIONPLANDATE DESC"; public const string GET_SELECTLIST_PRODUCTION_PLAN_PG = @" SELECT p.PRODUCTIONPLANID AS ""Id"", p.PRODUCTIONPLANNUMBER AS ""Code"", p.PRODUCTIONPLANNUMBER AS ""Number"", p.PRODUCTIONPLANDATE AS ""ProductionPlanDate"", ou.ORGANIZATIONUNITNAME AS ""OUName"" FROM TPRODUCTIONPLAN p JOIN MORGANIZATIONUNIT ou ON ou.OUID = p.OUID WHERE p.STATUS <> 2 AND ou.TENANTID = :TenantId ORDER BY p.PRODUCTIONPLANDATE DESC"; // ── Select list — plan detail (picklist: pick a specific item/qty/date line) ─ public const string GET_SELECTLIST_PRODUCTION_PLAN_DETAIL_SQL = @" SELECT d.PRODUCTIONPLANDETAILID AS Id, CONCAT(p.PRODUCTIONPLANNUMBER, '-', d.SLNO) AS Code, CONCAT(ISNULL(i.ITEMNAME, ''), ' | Qty: ', d.TOTALBATCHQUANTITY, ' | ', CONVERT(VARCHAR(10), d.FROMDATE, 23), ' to ', CONVERT(VARCHAR(10), d.TODATE, 23)) AS Name, p.PRODUCTIONPLANID AS ProductionPlanId, p.PRODUCTIONPLANNUMBER AS PlanNumber, d.ITEMID AS ItemId, ISNULL(i.ITEMNAME, '') AS ItemName, d.TOTALBATCHQUANTITY AS BatchTotalQuantity, d.FROMDATE AS FromDate, d.TODATE AS ToDate FROM TPRODUCTIONPLANDETAIL d JOIN TPRODUCTIONPLAN p ON p.PRODUCTIONPLANID = d.PRODUCTIONPLANID JOIN MORGANIZATIONUNIT ou ON ou.OUID = p.OUID LEFT JOIN MITEM i ON i.ITEMID = d.ITEMID WHERE p.STATUS <> 2 AND ou.TENANTID = @TenantId ORDER BY p.PRODUCTIONPLANDATE DESC, d.SLNO"; public const string GET_SELECTLIST_PRODUCTION_PLAN_DETAIL_PG = @" SELECT d.PRODUCTIONPLANDETAILID AS ""Id"", CONCAT(p.PRODUCTIONPLANNUMBER, '-', d.SLNO) AS ""Code"", CONCAT(COALESCE(i.ITEMNAME, ''), ' | Qty: ', d.TOTALBATCHQUANTITY, ' | ', TO_CHAR(d.FROMDATE, 'YYYY-MM-DD'), ' to ', TO_CHAR(d.TODATE, 'YYYY-MM-DD')) AS ""Name"", p.PRODUCTIONPLANID AS ""ProductionPlanId"", p.PRODUCTIONPLANNUMBER AS ""PlanNumber"", d.ITEMID AS ""ItemId"", COALESCE(i.ITEMNAME, '') AS ""ItemName"", d.TOTALBATCHQUANTITY AS ""BatchTotalQuantity"", d.FROMDATE AS ""FromDate"", d.TODATE AS ""ToDate"" FROM TPRODUCTIONPLANDETAIL d JOIN TPRODUCTIONPLAN p ON p.PRODUCTIONPLANID = d.PRODUCTIONPLANID JOIN MORGANIZATIONUNIT ou ON ou.OUID = p.OUID LEFT JOIN MITEM i ON i.ITEMID = d.ITEMID WHERE p.STATUS <> 2 AND ou.TENANTID = :TenantId ORDER BY p.PRODUCTIONPLANDATE DESC, d.SLNO"; } }