using Dapr.Client.Autogen.Grpc.v1; namespace MMDAL.Query.Production { // Required indexes (add via DB migration — already in Phase 0 migration): // TPRODUCTION: IX_TPRODUCTION_OUID_DATE (OUID, PRODUCTIONDATE, TENANTID) // TPRODUCTIONDETAIL: IX_TPRODUCTIONDETAIL_PRODUCTION (PRODUCTIONID, TENANTID) // TPRODUCTIONRESOURCE: IX_TPRODUCTIONRESOURCE_PRODUCTION (PRODUCTIONID, TENANTID) public static class ProductionQB { // ── Get production header ───────────────────────────────────────────── public const string GET_PRODUCTION_SQL = @" SELECT p.PRODUCTIONID AS ProductionId, p.PRODUCTIONNUMBER AS ProductionNumber, p.PRODUCTIONDATE AS ProductionDate, 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.SHIFTID AS ShiftId, sh.SHIFTDESCRIPTION AS ShiftName, p.WORKCENTERID AS WorkCenterId, wc.WORKCENTERNAME AS WorkCenterName, p.INCHARGEID AS InChargeId, ISNULL(emp.EMPLOYEENAME, '') AS InChargeName, p.DEPARTMENTID AS DepartmentId, ISNULL(dep.DEPARTMENTNAME, '') AS DepartmentName, p.FROMSTOREID AS FromStoreId, ISNULL(fs.STORENAME, '') AS FromStoreName, p.TOSTOREID AS ToStoreId, ISNULL(ts.STORENAME, '') AS ToStoreName, ISNULL(p.REFERENCENUMBER, '') AS ReferenceNumber, p.SELECTTYPE AS SelectType, p.TYPE AS Type, p.ISSTOCKPOST AS IsStockPost, ISNULL(p.REMARKS, '') AS Remarks, p.STATUS AS Status, p.VERSION AS Version, p.CREATEDON AS CreatedOn, p.MODIFIEDON AS ModifiedOn FROM TPRODUCTION 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 JOIN MSHIFT sh ON sh.SHIFTID = p.SHIFTID JOIN MWORKCENTER wc ON wc.WORKCENTERID = p.WORKCENTERID LEFT JOIN MEMPLOYEE emp ON emp.EMPLOYEEID = p.INCHARGEID LEFT JOIN MDEPARTMENT dep ON dep.DEPARTMENTID = p.DEPARTMENTID LEFT JOIN MSTORE fs ON fs.STOREID = p.FROMSTOREID LEFT JOIN MSTORE ts ON ts.STOREID = p.TOSTOREID WHERE p.PRODUCTIONID = @ProductionId AND p.STATUS <> 2;"; public const string GET_PRODUCTION_PG = @" SELECT p.PRODUCTIONID AS ""ProductionId"", p.PRODUCTIONNUMBER AS ""ProductionNumber"", p.PRODUCTIONDATE AS ""ProductionDate"", 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.SHIFTID AS ""ShiftId"", sh.SHIFTDESCRIPTION AS ""ShiftName"", p.WORKCENTERID AS ""WorkCenterId"", wc.WORKCENTERNAME AS ""WorkCenterName"", p.INCHARGEID AS ""InChargeId"", COALESCE(emp.EMPLOYEENAME,'') AS ""InChargeName"", p.DEPARTMENTID AS ""DepartmentId"", COALESCE(dep.DEPARTMENTNAME,'') AS ""DepartmentName"", p.FROMSTOREID AS ""FromStoreId"", COALESCE(fs.STORENAME,'') AS ""FromStoreName"", p.TOSTOREID AS ""ToStoreId"", COALESCE(ts.STORENAME,'') AS ""ToStoreName"", COALESCE(p.REFERENCENUMBER,'') AS ""ReferenceNumber"", p.SELECTTYPE AS ""SelectType"", p.TYPE AS ""Type"", p.ISSTOCKPOST AS ""IsStockPost"", COALESCE(p.REMARKS,'') AS ""Remarks"", p.STATUS AS ""Status"", p.VERSION AS ""Version"", p.CREATEDON AS ""CreatedOn"", p.MODIFIEDON AS ""ModifiedOn"" FROM TPRODUCTION 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 JOIN MSHIFT sh ON sh.SHIFTID = p.SHIFTID JOIN MWORKCENTER wc ON wc.WORKCENTERID = p.WORKCENTERID LEFT JOIN MEMPLOYEE emp ON emp.EMPLOYEEID = p.INCHARGEID LEFT JOIN MDEPARTMENT dep ON dep.DEPARTMENTID = p.DEPARTMENTID LEFT JOIN MSTORE fs ON fs.STOREID = p.FROMSTOREID LEFT JOIN MSTORE ts ON ts.STOREID = p.TOSTOREID WHERE p.PRODUCTIONID = :ProductionId AND p.STATUS <> 2;"; // ── Get production details ──────────────────────────────────────────── public const string GET_PRODUCTION_DETAILS_SQL = @" SELECT pd.PRODUCTIONDETAILID AS ProductionDetailId, pd.PRODUCTIONID AS ProductionId, pd.SLNO AS SlNo, pd.LINKTYPEID AS LinkTypeId, pd.PROCESSGROUPID AS ProcessGroupId, pd.DOCUMENTDETAILID AS DocumentDetailId, pd.INDENTDETAILID AS IndentDetailId, pd.ITEMID AS ItemId, ISNULL(mi.ITEMNAME,'') AS ItemName, pd.SKUID AS SkuId, pd.UOMID AS UomId, pd.ALLOCATIONID AS AllocationId, pd.QUANTITY AS Quantity, pd.PRODUCTIONQUANTITY AS ProductionQuantity, pd.GOODQUANTITY AS GoodQuantity, pd.REJECTEDQUANTITY AS RejectedQuantity, pd.REWORKQUANTITY AS ReworkQuantity, pd.OTHERQUANTITY AS OtherQuantity, pd.EXCESSSHORTAGEQUANTITY AS ExcessShortageQuantity, pd.OUTPUTWEIGHT AS OutputWeight, pd.STOCKPOSTTYPE AS StockPostType, pd.READINGFROM AS ReadingFrom, pd.READINGTO AS ReadingTo, pd.STARTTIME AS StartTime, pd.ENDTIME AS EndTime FROM TPRODUCTIONDETAIL pd LEFT JOIN MITEM mi ON mi.ITEMID = pd.ITEMID WHERE pd.PRODUCTIONID = @ProductionId ORDER BY pd.SLNO;"; public const string GET_PRODUCTION_DETAILS_PG = @" SELECT pd.PRODUCTIONDETAILID AS ""ProductionDetailId"", pd.PRODUCTIONID AS ""ProductionId"", pd.SLNO AS ""SlNo"", pd.LINKTYPEID AS ""LinkTypeId"", pd.PROCESSGROUPID AS ""ProcessGroupId"", pd.DOCUMENTDETAILID AS ""DocumentDetailId"", pd.INDENTDETAILID AS ""IndentDetailId"", pd.ITEMID AS ""ItemId"", COALESCE(mi.ITEMNAME,'') AS ""ItemName"", pd.SKUID AS ""SkuId"", pd.UOMID AS ""UomId"", pd.ALLOCATIONID AS ""AllocationId"", pd.QUANTITY AS ""Quantity"", pd.PRODUCTIONQUANTITY AS ""ProductionQuantity"", pd.GOODQUANTITY AS ""GoodQuantity"", pd.REJECTEDQUANTITY AS ""RejectedQuantity"", pd.REWORKQUANTITY AS ""ReworkQuantity"", pd.OTHERQUANTITY AS ""OtherQuantity"", pd.EXCESSSHORTAGEQUANTITY AS ""ExcessShortageQuantity"", pd.OUTPUTWEIGHT AS ""OutputWeight"", pd.STOCKPOSTTYPE AS ""StockPostType"", pd.READINGFROM AS ""ReadingFrom"", pd.READINGTO AS ""ReadingTo"", pd.STARTTIME AS ""StartTime"", pd.ENDTIME AS ""EndTime"" FROM TPRODUCTIONDETAIL pd LEFT JOIN MITEM mi ON mi.ITEMID = pd.ITEMID WHERE pd.PRODUCTIONID = :ProductionId ORDER BY pd.SLNO;"; // ── Paged production list ───────────────────────────────────────────── public const string GET_PRODUCTION_LIST_SQL = @" SELECT p.PRODUCTIONID AS ProductionId, p.PRODUCTIONNUMBER AS ProductionNumber, p.PRODUCTIONDATE AS ProductionDate, bt.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName, ou.ORGANIZATIONUNITNAME AS OUName, sh.SHIFTDESCRIPTION AS ShiftName, wc.WORKCENTERNAME AS WorkCenterName, ISNULL(emp.EMPLOYEENAME,'') AS InChargeName, p.SELECTTYPE AS SelectType, p.TYPE AS Type, p.ISSTOCKPOST AS IsStockPost, p.STATUS AS Status, COUNT(pd.PRODUCTIONDETAILID) AS DetailCount, SUM(pd.PRODUCTIONQUANTITY) AS TotalProductionQuantity, SUM(pd.GOODQUANTITY) AS TotalGoodQuantity, p.CREATEDON AS CreatedOn FROM TPRODUCTION p JOIN MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = p.BIZTRANSACTIONTYPEID JOIN MORGANIZATIONUNIT ou ON ou.OUID = p.OUID JOIN MSHIFT sh ON sh.SHIFTID = p.SHIFTID JOIN MWORKCENTER wc ON wc.WORKCENTERID = p.WORKCENTERID LEFT JOIN MEMPLOYEE emp ON emp.EMPLOYEEID = p.INCHARGEID LEFT JOIN TPRODUCTIONDETAIL pd ON pd.PRODUCTIONID = p.PRODUCTIONID WHERE p.OUID = @OUId AND p.STATUS <> 2 AND (@FromDate IS NULL OR p.PRODUCTIONDATE >= @FromDate) AND (@ToDate IS NULL OR p.PRODUCTIONDATE <= @ToDate) GROUP BY p.PRODUCTIONID, p.PRODUCTIONNUMBER, p.PRODUCTIONDATE, bt.BIZTRANSACTIONTYPENAME, ou.ORGANIZATIONUNITNAME, sh.SHIFTDESCRIPTION, wc.WORKCENTERNAME, emp.EMPLOYEENAME, p.SELECTTYPE, p.TYPE, p.ISSTOCKPOST, p.STATUS, p.CREATEDON ORDER BY p.PRODUCTIONDATE DESC, p.PRODUCTIONID DESC OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY;"; public const string GET_PRODUCTION_LIST_COUNT_SQL = @" SELECT COUNT(*) FROM TPRODUCTION p WHERE p.OUID = @OUId AND p.STATUS <> 2 AND (@FromDate IS NULL OR p.PRODUCTIONDATE >= @FromDate) AND (@ToDate IS NULL OR p.PRODUCTIONDATE <= @ToDate);"; public const string GET_PRODUCTION_LIST_PG = @" SELECT p.PRODUCTIONID AS ""ProductionId"", p.PRODUCTIONNUMBER AS ""ProductionNumber"", p.PRODUCTIONDATE AS ""ProductionDate"", bt.BIZTRANSACTIONTYPENAME AS ""BizTransactionTypeName"", ou.ORGANIZATIONUNITNAME AS ""OUName"", sh.SHIFTDESCRIPTION AS ""ShiftName"", wc.WORKCENTERNAME AS ""WorkCenterName"", COALESCE(emp.EMPLOYEENAME,'') AS ""InChargeName"", p.SELECTTYPE AS ""SelectType"", p.TYPE AS ""Type"", p.ISSTOCKPOST AS ""IsStockPost"", p.STATUS AS ""Status"", COUNT(pd.PRODUCTIONDETAILID) AS ""DetailCount"", SUM(pd.PRODUCTIONQUANTITY) AS ""TotalProductionQuantity"", SUM(pd.GOODQUANTITY) AS ""TotalGoodQuantity"", p.CREATEDON AS ""CreatedOn"" FROM TPRODUCTION p JOIN MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = p.BIZTRANSACTIONTYPEID JOIN MORGANIZATIONUNIT ou ON ou.OUID = p.OUID JOIN MSHIFT sh ON sh.SHIFTID = p.SHIFTID JOIN MWORKCENTER wc ON wc.WORKCENTERID = p.WORKCENTERID LEFT JOIN MEMPLOYEE emp ON emp.EMPLOYEEID = p.INCHARGEID LEFT JOIN TPRODUCTIONDETAIL pd ON pd.PRODUCTIONID = p.PRODUCTIONID WHERE p.OUID = :OUId AND p.STATUS <> 2 AND (:FromDate IS NULL OR p.PRODUCTIONDATE >= :FromDate) AND (:ToDate IS NULL OR p.PRODUCTIONDATE <= :ToDate) GROUP BY p.PRODUCTIONID, p.PRODUCTIONNUMBER, p.PRODUCTIONDATE, bt.BIZTRANSACTIONTYPENAME, ou.ORGANIZATIONUNITNAME, sh.SHIFTDESCRIPTION, wc.WORKCENTERNAME, emp.EMPLOYEENAME, p.SELECTTYPE, p.TYPE, p.ISSTOCKPOST, p.STATUS, p.CREATEDON ORDER BY p.PRODUCTIONDATE DESC, p.PRODUCTIONID DESC LIMIT :MaxResult OFFSET :FirstNumber;"; public const string GET_PRODUCTION_LIST_COUNT_PG = @" SELECT COUNT(*) FROM TPRODUCTION p WHERE p.OUID = :OUId AND p.TENANTID = :TenantId AND p.STATUS <> 2 AND (:FromDate IS NULL OR p.PRODUCTIONDATE >= :FromDate) AND (:ToDate IS NULL OR p.PRODUCTIONDATE <= :ToDate);"; // ── Save production header ──────────────────────────────────────────── public const string SAVE_PRODUCTION_SQL = @"INSERT INTO TPRODUCTION ( PRODUCTIONID, BIZTRANSACTIONTYPEID, OUID, PERIODID, PRODUCTIONDATE, PRODUCTIONNUMBER, REFERENCEDATE, REFERENCENUMBER, SELECTTYPE, INCHARGEID, SHIFTID, DEPARTMENTID, REMARKS, WORKCENTERID, FROMSTOREID, TOSTOREID, TYPE, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, ISSTOCKPOST, PATTERNID ) VALUES ( @ProductionId, @BizTransactionTypeId, @OUId, @PeriodId, @ProductionDate, @ProductionNumber, @ReferenceDate, @ReferenceNumber, @SelectType, @InChargeId, @ShiftId, @DepartmentId, @Remarks, @WorkCenterId, @FromStoreId, @ToStoreId, @Type, @Status, @Version, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @IsStockPost, @PatternId );"; public const string SAVE_PRODUCTION_PG = @" INSERT INTO TPRODUCTION ( PRODUCTIONID, BIZTRANSACTIONTYPEID, OUID, PERIODID, PRODUCTIONDATE, PRODUCTIONNUMBER, REFERENCEDATE, REFERENCENUMBER, SELECTTYPE, INCHARGEID, SHIFTID, DEPARTMENTID, REMARKS, WORKCENTERID, FROMSTOREID, TOSTOREID, TYPE, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, ISSTOCKPOST, PATTERNID ) VALUES ( :ProductionId, :BizTransactionTypeId, :OUId, :PeriodId, :ProductionDate, :ProductionNumber, :ReferenceDate, :ReferenceNumber, :SelectType, :InChargeId, :ShiftId, :DepartmentId, :Remarks, :WorkCenterId, :FromStoreId, :ToStoreId, :Type, :Status, :Version, :CreatedById, :CreatedOn, :ModifiedById, :ModifiedOn, :IsStockPost, :PatternId );"; public const string UPDATE_PRODUCTION_SQL = @"UPDATE TPRODUCTION SET BIZTRANSACTIONTYPEID = @BizTransactionTypeId, OUID = @OUId, PERIODID = @PeriodId, PRODUCTIONDATE = @ProductionDate, PRODUCTIONNUMBER = @ProductionNumber, REFERENCEDATE = @ReferenceDate, REFERENCENUMBER = @ReferenceNumber, SELECTTYPE = @SelectType, INCHARGEID = @InChargeId, SHIFTID = @ShiftId, DEPARTMENTID = @DepartmentId, REMARKS = @Remarks, WORKCENTERID = @WorkCenterId, FROMSTOREID = @FromStoreId, TOSTOREID = @ToStoreId, TYPE = @Type, STATUS = @Status, VERSION = @Version, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn, ISSTOCKPOST = @IsStockPost, PATTERNID = @PatternId WHERE PRODUCTIONID = @ProductionId;"; public const string UPDATE_PRODUCTION_PG = @"UPDATE TPRODUCTION SET BIZTRANSACTIONTYPEID = :BizTransactionTypeId, OUID = :OUId, PERIODID = :PeriodId, PRODUCTIONDATE = :ProductionDate, PRODUCTIONNUMBER = :ProductionNumber, REFERENCEDATE = :ReferenceDate, REFERENCENUMBER = :ReferenceNumber, SELECTTYPE = :SelectType, INCHARGEID = :InChargeId, SHIFTID = :ShiftId, DEPARTMENTID = :DepartmentId, REMARKS = :Remarks, WORKCENTERID = :WorkCenterId, FROMSTOREID = :FromStoreId, TOSTOREID = :ToStoreId, TYPE = :Type, STATUS = :Status, VERSION = :Version, MODIFIEDBYID = :ModifiedById, MODIFIEDON = :ModifiedOn, ISSTOCKPOST = :IsStockPost, PATTERNID = :PatternId WHERE PRODUCTIONID = :ProductionId;"; public const string SOFT_DELETE_PRODUCTION_SQL = @" UPDATE TPRODUCTION SET STATUS = 2 WHERE PRODUCTIONID = @ProductionId;"; public const string SOFT_DELETE_PRODUCTION_PG = @" UPDATE TPRODUCTION SET STATUS = 2 WHERE PRODUCTIONID = :ProductionId;"; // Delete child rows when deleting a production (call before header soft-delete) public const string DELETE_PRODUCTION_DETAILS_SQL = @" DELETE FROM TPRODUCTIONDETAIL WHERE PRODUCTIONID = @ProductionId;"; public const string DELETE_PRODUCTION_DETAILS_PG = @" DELETE FROM TPRODUCTIONDETAIL WHERE PRODUCTIONID = :ProductionId;"; public const string DELETE_PRODUCTION_PROCESSES_SQL = @" DELETE pp FROM TPRODUCTIONPROCESS pp INNER JOIN TPRODUCTIONDETAIL pd ON pd.PRODUCTIONDETAILID = pp.PRODUCTIONDETAILID WHERE pd.PRODUCTIONID = @ProductionId;"; public const string DELETE_PRODUCTION_PROCESSES_PG = @" DELETE FROM TPRODUCTIONPROCESS pp USING TPRODUCTIONDETAIL pd WHERE pd.PRODUCTIONDETAILID = pp.PRODUCTIONDETAILID AND pd.PRODUCTIONID = :ProductionId;"; public const string DELETE_PRODUCTION_RESOURCES_SQL = @" DELETE FROM TPRODUCTIONRESOURCE WHERE PRODUCTIONID = @ProductionId;"; public const string DELETE_PRODUCTION_RESOURCES_PG = @" DELETE FROM TPRODUCTIONRESOURCE WHERE PRODUCTIONID = :ProductionId;"; public const string DELETE_PRODUCTION_STOPPAGES_SQL = @" DELETE FROM TPRODUCTIONSTOPPAGE WHERE PRODUCTIONID = @ProductionId;"; public const string DELETE_PRODUCTION_STOPPAGES_PG = @" DELETE FROM TPRODUCTIONSTOPPAGE WHERE PRODUCTIONID = :ProductionId;"; // ── Save production detail ──────────────────────────────────────────── public const string SAVE_PRODUCTION_DETAIL_SQL = @"INSERT INTO TPRODUCTIONDETAIL ( PRODUCTIONDETAILID, PRODUCTIONID, SLNO, LINKTYPEID, PRODUCTIONPLANDETAILID, PROCESSGROUPID, DOCUMENTDETAILID, INDENTDETAILID, ITEMID, SKUID, QUANTITY, UOMID, ALLOCATIONID, PRODUCTIONQUANTITY, GOODQUANTITY, REJECTEDQUANTITY, REWORKQUANTITY, OTHERQUANTITY, PROCESSCOST, MATERIALCOST, STOCKPOSTTYPE, READINGFROM, READINGTO, STARTTIME, ENDTIME, EXCESSSHORTAGEQUANTITY, OUTPUTWEIGHT, SKUWEIGHT, MATERIALINHOUSEPROCESSCOST, MATERIALSUBCONTRACTPROCESSCOST, MATERIALPROCESSCOST, MATERIALCHARGESCOST, MATERIALREVENUE, MATERIALTOTALCOST, INHOUSEPROCESSCOST, SUBCONTRACTPROCESSCOST, CHARGESCOST, REVENUE, TOTALCOST ) VALUES ( @ProductionDetailId, @ProductionId, @SlNo, @LinkTypeId, @ProductionPlanDetailId, @ProcessGroupId, @DocumentDetailId, @IndentDetailId, @ItemId, @SkuId, @Quantity, @UomId, @AllocationId, @ProductionQuantity, @GoodQuantity, @RejectedQuantity, @ReworkQuantity, @OtherQuantity, @ProcessCost, @MaterialCost, @StockPostType, @ReadingFrom, @ReadingTo, @StartTime, @EndTime, @ExcessShortageQuantity, @OutputWeight, @SkuWeight, @MaterialInHouseProcessCost, @MaterialSubContractProcessCost, @MaterialProcessCost, @MaterialChargesCost, @MaterialRevenue, @MaterialTotalCost, @InHouseProcessCost, @SubContractProcessCost, @ChargesCost, @Revenue, @TotalCost );"; public const string SAVE_PRODUCTION_DETAIL_PG = @"INSERT INTO TPRODUCTIONDETAIL ( PRODUCTIONDETAILID, PRODUCTIONID, SLNO, LINKTYPEID, PRODUCTIONPLANDETAILID, PROCESSGROUPID, DOCUMENTDETAILID, INDENTDETAILID, ITEMID, SKUID, QUANTITY, UOMID, ALLOCATIONID, PRODUCTIONQUANTITY, GOODQUANTITY, REJECTEDQUANTITY, REWORKQUANTITY, OTHERQUANTITY, PROCESSCOST, MATERIALCOST, STOCKPOSTTYPE, READINGFROM, READINGTO, STARTTIME, ENDTIME, EXCESSSHORTAGEQUANTITY, OUTPUTWEIGHT, SKUWEIGHT, MATERIALINHOUSEPROCESSCOST, MATERIALSUBCONTRACTPROCESSCOST, MATERIALPROCESSCOST, MATERIALCHARGESCOST, MATERIALREVENUE, MATERIALTOTALCOST, INHOUSEPROCESSCOST, SUBCONTRACTPROCESSCOST, CHARGESCOST, REVENUE, TOTALCOST ) VALUES ( :ProductionDetailId, :ProductionId, :SlNo, :LinkTypeId, :ProductionPlanDetailId, :ProcessGroupId, :DocumentDetailId, :IndentDetailId, :ItemId, :SkuId, :Quantity, :UomId, :AllocationId, :ProductionQuantity, :GoodQuantity, :RejectedQuantity, :ReworkQuantity, :OtherQuantity, :ProcessCost, :MaterialCost, :StockPostType, :ReadingFrom, :ReadingTo, :StartTime, :EndTime, :ExcessShortageQuantity, :OutputWeight, :SkuWeight, :MaterialInHouseProcessCost, :MaterialSubContractProcessCost, :MaterialProcessCost, :MaterialChargesCost, :MaterialRevenue, :MaterialTotalCost, :InHouseProcessCost, :SubContractProcessCost, :ChargesCost, :Revenue, :TotalCost );"; // ── Save production process ─────────────────────────────────────────── public const string SAVE_PRODUCTION_PROCESS_SQL = @" INSERT INTO TPRODUCTIONPROCESS (PRODUCTIONPROCESSID, PRODUCTIONDETAILID, SLNO, PROCESSID, STARTDATE, STARTTIME, ENDDATE, ENDTIME, TENANTID) VALUES (@ProductionProcessId, @ProductionDetailId, @SlNo, @ProcessId, @StartDate, @StartTime, @EndDate, @EndTime, @TenantId);"; public const string SAVE_PRODUCTION_PROCESS_PG = @" INSERT INTO TPRODUCTIONPROCESS (PRODUCTIONPROCESSID, PRODUCTIONDETAILID, SLNO, PROCESSID, STARTDATE, STARTTIME, ENDDATE, ENDTIME, TENANTID) VALUES (:ProductionProcessId, :ProductionDetailId, :SlNo, :ProcessId, :StartDate, :StartTime, :EndDate, :EndTime, :TenantId);"; // ── Save production operation ───────────────────────────────────────── public const string SAVE_PRODUCTION_OPERATION_SQL = @" INSERT INTO TPRODUCTIONOPERATION (PRODUCTIONOPERATIONID, PRODUCTIONPROCESSID, SLNO, OPERATIONID, STARTDATE, STARTTIME, ENDDATE, ENDTIME, TENANTID) VALUES (@ProductionOperationId, @ProductionProcessId, @SlNo, @OperationId, @StartDate, @StartTime, @EndDate, @EndTime, @TenantId);"; public const string SAVE_PRODUCTION_OPERATION_PG = @" INSERT INTO TPRODUCTIONOPERATION (PRODUCTIONOPERATIONID, PRODUCTIONPROCESSID, SLNO, OPERATIONID, STARTDATE, STARTTIME, ENDDATE, ENDTIME, TENANTID) VALUES (:ProductionOperationId, :ProductionProcessId, :SlNo, :OperationId, :StartDate, :StartTime, :EndDate, :EndTime, :TenantId);"; // ── Save production resource ────────────────────────────────────────── public const string SAVE_PRODUCTION_RESOURCE_SQL = @"INSERT INTO TPRODUCTIONRESOURCE ( PRODUCTIONRESOURCEID, PRODUCTIONID, PRODUCTIONDETAILID, PRODUCTIONPROCESSID, PRODUCTIONOPERATIONID, SLNO, RESOURCEID, RESOURCETYPEID, MACHINEID, EMPLOYEEID, DURATION, STARTDATE, STARTTIME, ENDDATE, ENDTIME, QUANTITY, UOMID, MACHINESPEED ) VALUES ( @ProductionResourceId, @ProductionId, @ProductionDetailId, @ProductionProcessId, @ProductionOperationId, @SlNo, @ResourceId, @ResourceTypeId, @MachineId, @EmployeeId, @Duration, @StartDate, @StartTime, @EndDate, @EndTime, @Quantity, @UomId, @MachineSpeed );"; public const string SAVE_PRODUCTION_RESOURCE_PG = @"INSERT INTO TPRODUCTIONRESOURCE ( PRODUCTIONRESOURCEID, PRODUCTIONID, PRODUCTIONDETAILID, PRODUCTIONPROCESSID, PRODUCTIONOPERATIONID, SLNO, RESOURCEID, RESOURCETYPEID, MACHINEID, EMPLOYEEID, DURATION, STARTDATE, STARTTIME, ENDDATE, ENDTIME, QUANTITY, UOMID, MACHINESPEED ) VALUES ( :ProductionResourceId, :ProductionId, :ProductionDetailId, :ProductionProcessId, :ProductionOperationId, :SlNo, :ResourceId, :ResourceTypeId, :MachineId, :EmployeeId, :Duration, :StartDate, :StartTime, :EndDate, :EndTime, :Quantity, :UomId, :MachineSpeed );"; // ── Save production stoppage ────────────────────────────────────────── public const string SAVE_PRODUCTION_STOPPAGE_SQL = @"INSERT INTO TPRODUCTIONSTOPPAGE ( PRODUCTIONSTOPPAGEID, PRODUCTIONID, PRODUCTIONDETAILID, PRODUCTIONPROCESSID, PRODUCTIONOPERATIONID, PRODUCTIONRESOURCEID, SLNO, STOPPAGEREASONID, STARTDATE, STARTTIME, ENDDATE, ENDTIME, STOPPAGEDURATION, REMARKS ) VALUES ( @ProductionStoppageId, @ProductionId, @ProductionDetailId, @ProductionProcessId, @ProductionOperationId, @ProductionResourceId, @SlNo, @StoppageReasonId, @StartDate, @StartTime, @EndDate, @EndTime, @StopPageDuration, @Remarks );"; public const string SAVE_PRODUCTION_STOPPAGE_PG = @"INSERT INTO TPRODUCTIONSTOPPAGE ( PRODUCTIONSTOPPAGEID, PRODUCTIONID, PRODUCTIONDETAILID, PRODUCTIONPROCESSID, PRODUCTIONOPERATIONID, PRODUCTIONRESOURCEID, SLNO, STOPPAGEREASONID, STARTDATE, STARTTIME, ENDDATE, ENDTIME, STOPPAGEDURATION, REMARKS ) VALUES ( :ProductionStoppageId, :ProductionId, :ProductionDetailId, :ProductionProcessId, :ProductionOperationId, :ProductionResourceId, :SlNo, :StoppageReasonId, :StartDate, :StartTime, :EndDate, :EndTime, :StopPageDuration, :Remarks );"; // ── Insert TSTOCKLEDGER row from production output (delta pattern) ──── // Called per TPRODUCTIONDETAIL row where STOCKPOSTTYPE > 0. // STOCKPOSTTYPE in TSTOCKLEDGER: 0=ADD (inward/output), 1=LESS (issue/consumption) // PostSign: +1 for output to stock, -1 for consumption from stock. // Cost fields (POSTEDCOST, POSTEDVALUE) are filled by StockLedgerCostBLL.UpdateCostAsync() // called separately within the same DB transaction. public const string INSERT_STOCK_LEDGER_FROM_PRODUCTION_SQL = @" INSERT INTO TSTOCKLEDGER (STOCKLEDGERNUMBER, STOCKLEDGERDATE, REFERENCENUMBER, REFERENCEDATE, OUID, BIZTRANSACTIONTYPEID, OBJECTTYPEID, OBJECTID, LOCATIONTYPE, ALLOCATIONID, STOREID, WORKCENTERID, PARTYBRANCHID, ITEMID, SKUID, STOCKPOSTTYPE, MATERIALOWNERSHIPTYPE, TRANSACTIONQUANTITY, UOMID, CONVERSIONFACTOR, QUANTITY, PACKQUANTITY, PACKID, WEIGHT, CURRENCYID, RATECONVERSIONFACTOR, RATE, RATEPERUOMID, COST, POSTEDCOST, POSTEDVALUE, GOODQUANTITY, REJECTEDQUANTITY, REWORKQUANTITY, OTHERQUANTITY, TRANSACTIONACTUALQUANTITY, ACTUALQUANTITY, TRANSACTIONUOMID, OBJECTDETAILTYPEID, OBJECTDETAILID, DELIVERYTYPE, INDENTDETAILID, MATERIALCOST, PROCESSCOST, GCCURRENCYCONVERSION, POSTEDCOSTGC, POSTEDVALUEGC) VALUES (@StockLedgerNumber, @ProductionDate, @ProductionNumber, @ProductionDate, @OUId, @BizTransactionTypeId, @ObjectTypeId, @ProductionId, 0, @AllocationId, @StoreId, @WorkCenterId, -1, @ItemId, @SkuId, @StockPostType, 0, @ProductionQuantity, @UomId, 1, @ProductionQuantity, 0, -1, @OutputWeight, -1, 1, 0, @UomId, 0, 0, 0, @GoodQuantity, @RejectedQuantity, @ReworkQuantity, @OtherQuantity, @ProductionQuantity, @ProductionQuantity, @UomId, @ObjectDetailTypeId, @ProductionDetailId, 0, @IndentDetailId, 0, 0, 1, 0, 0);"; public const string INSERT_STOCK_LEDGER_FROM_PRODUCTION_PG = @" INSERT INTO TSTOCKLEDGER (STOCKLEDGERNUMBER, STOCKLEDGERDATE, REFERENCENUMBER, REFERENCEDATE, OUID, BIZTRANSACTIONTYPEID, OBJECTTYPEID, OBJECTID, LOCATIONTYPE, ALLOCATIONID, STOREID, WORKCENTERID, PARTYBRANCHID, ITEMID, SKUID, STOCKPOSTTYPE, MATERIALOWNERSHIPTYPE, TRANSACTIONQUANTITY, UOMID, CONVERSIONFACTOR, QUANTITY, PACKQUANTITY, PACKID, WEIGHT, CURRENCYID, RATECONVERSIONFACTOR, RATE, RATEPERUOMID, COST, POSTEDCOST, POSTEDVALUE, GOODQUANTITY, REJECTEDQUANTITY, REWORKQUANTITY, OTHERQUANTITY, TRANSACTIONACTUALQUANTITY, ACTUALQUANTITY, TRANSACTIONUOMID, OBJECTDETAILTYPEID, OBJECTDETAILID, DELIVERYTYPE, INDENTDETAILID, MATERIALCOST, PROCESSCOST, GCCURRENCYCONVERSION, POSTEDCOSTGC, POSTEDVALUEGC) VALUES (:StockLedgerNumber, :ProductionDate, :ProductionNumber, :ProductionDate, :OUId, :BizTransactionTypeId, :ObjectTypeId, :ProductionId, 0, :AllocationId, :StoreId, :WorkCenterId, -1, :ItemId, :SkuId, :StockPostType, 0, :ProductionQuantity, :UomId, 1, :ProductionQuantity, 0, -1, :OutputWeight, -1, 1, 0, :UomId, 0, 0, 0, :GoodQuantity, :RejectedQuantity, :ReworkQuantity, :OtherQuantity, :ProductionQuantity, :ProductionQuantity, :UomId, :ObjectDetailTypeId, :ProductionDetailId, 0, :IndentDetailId, 0, 0, 1, 0, 0);"; // ── Update TINDENTDETAIL.RELEASESTATUS after production save ────────── // 2=InProgress after first production posted; 5=Complete when fully produced. // Called within the same transaction as SaveProduction. public const string UPDATE_WO_RELEASESTATUS_SQL = @" UPDATE TINDENTDETAIL SET RELEASESTATUS = @ReleaseStatus WHERE INDENTDETAILID = @IndentDetailId;"; public const string UPDATE_WO_RELEASESTATUS_PG = @" UPDATE TINDENTDETAIL SET RELEASESTATUS = :ReleaseStatus WHERE INDENTDETAILID = :IndentDetailId;"; // ── Get stoppages for a production ─────────────────────────────────── public const string GET_PRODUCTION_STOPPAGES_SQL = @" SELECT ps.PRODUCTIONSTOPPAGEID AS ProductionStoppageId, ps.PRODUCTIONID AS ProductionId, ps.PRODUCTIONDETAILID AS ProductionDetailId, ps.PRODUCTIONPROCESSID AS ProductionProcessId, ps.PRODUCTIONOPERATIONID AS ProductionOperationId, ps.PRODUCTIONRESOURCEID AS ProductionResourceId, ps.SLNO AS SlNo, ps.STOPPAGEREASONID AS StoppageReasonId, ISNULL(r.REASONNAME, '') AS StoppageReasonName, ps.STARTDATE AS StartDate, ps.STARTTIME AS StartTime, ps.ENDDATE AS EndDate, ps.ENDTIME AS EndTime, ps.STOPPAGEDURATION AS StopPageDuration, ISNULL(ps.REMARKS, '') AS Remarks FROM TPRODUCTIONSTOPPAGE ps LEFT JOIN MREASON r ON r.REASONID = ps.STOPPAGEREASONID WHERE ps.PRODUCTIONID = @ProductionId ORDER BY ps.PRODUCTIONDETAILID, ps.SLNO;"; public const string GET_PRODUCTION_STOPPAGES_PG = @" SELECT ps.PRODUCTIONSTOPPAGEID AS ""ProductionStoppageId"", ps.PRODUCTIONID AS ""ProductionId"", ps.PRODUCTIONDETAILID AS ""ProductionDetailId"", ps.PRODUCTIONPROCESSID AS ""ProductionProcessId"", ps.PRODUCTIONOPERATIONID AS ""ProductionOperationId"", ps.PRODUCTIONRESOURCEID AS ""ProductionResourceId"", ps.SLNO AS ""SlNo"", ps.STOPPAGEREASONID AS ""StoppageReasonId"", COALESCE(r.REASONNAME,'') AS ""StoppageReasonName"", ps.STARTDATE AS ""StartDate"", ps.STARTTIME AS ""StartTime"", ps.ENDDATE AS ""EndDate"", ps.ENDTIME AS ""EndTime"", ps.STOPPAGEDURATION AS ""StopPageDuration"", COALESCE(ps.REMARKS,'') AS ""Remarks"" FROM TPRODUCTIONSTOPPAGE ps LEFT JOIN MREASON r ON r.REASONID = ps.STOPPAGEREASONID WHERE ps.PRODUCTIONID = :ProductionId ORDER BY ps.PRODUCTIONDETAILID, ps.SLNO;"; public const string GET_SELECTLIST_PRODUCTION_SQL = @"WITH Paged AS ( SELECT p.PRODUCTIONID AS Id, p.PRODUCTIONDATE AS Date, p.PRODUCTIONNUMBER AS Number, p.REFERENCEDATE AS ReferenceDate, p.REFERENCENUMBER AS ReferenceNumber, ROW_NUMBER() OVER (ORDER BY p.PRODUCTIONID DESC) AS RowNum FROM TPRODUCTION p WHERE p.STATUS = 1 AND p.PRODUCTIONID <> -1 ) SELECT Id, Date, Number, ReferenceDate, ReferenceNumber FROM Paged WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);"; public const string GET_SELECTLIST_PRODUCTION_PG = @"WITH Paged AS ( SELECT p.PRODUCTIONID AS ""Id"", p.PRODUCTIONDATE AS ""Date"", p.PRODUCTIONNUMBER AS ""Number"", p.REFERENCEDATE AS ""ReferenceDate"", p.REFERENCENUMBER AS ""ReferenceNumber"", ROW_NUMBER() OVER (ORDER BY p.PRODUCTIONID DESC) AS ""RowNum"" FROM TPRODUCTION p WHERE p.STATUS = 1 AND p.PRODUCTIONID <> -1 ) SELECT ""Id"", ""Date"", ""Number"", ""ReferenceDate"", ""ReferenceNumber"" FROM Paged WHERE (@firstnumber = -1 AND @maxresult = -1) OR (""RowNum"" BETWEEN @firstnumber AND @maxresult);"; } }