namespace MMDAL.Query.Inspection { public static class InspectionQB { // ── Single-record header GET ─────────────────────────────────────────── public const string GET_INSPECTION = @" SELECT I.INSPECTIONID AS InspectionId, I.INSPECTIONDATE AS InspectionDate, I.OUID AS OUId, I.PERIODID AS PeriodId, I.STAGEID AS StageId, I.PARTYID AS PartyId, I.SHIFTID AS ShiftId, I.BIZTRANSACTIONTYPEID AS BizTransactionTypeId, I.REFERENCENUMBER AS InspectionReferenceNumber, I.REFERENCEDATE AS InspectionReferenceDate, I.WORKCENTERID AS WorkCenterId, I.FROMSTOREID AS FromStoreId, I.TOSTOREID AS ToStoreId, I.INSPECTIONNUMBER AS InspectionNumber, I.INSPECTIONAREAID AS InspectionAreaId, IA.INSPECTIONAREA AS InspectionAreaArea, I.INSPECTIONTYPE AS InspectionInspectionType, I.INSPECTIONTYPEID AS InspectionTypeId, IT.INSPECTIONTYPECODE AS InspectionTypeCode, IT.INSPECTIONTYPENAME AS InspectionTypeName, I.LOADBIZTRANSACTIONTYPEID AS LoadBizTransactionTypeId, I.LOADHEADEROBJECTTYPEID AS LoadHeaderObjectTypeId, I.LOADHEADEROBJECTID AS InspectionLoadHeaderObjectId, I.TOTALPACKQUANTITY AS InspectionTotalPackQuantity, I.TOTALQUANTITY AS InspectionTotalQuantity, I.REMARKS AS InspectionRemarks, I.CREATEDBYID AS InspectionCreatedById, I.CREATEDON AS InspectionCreatedOn, I.MODIFIEDBYID AS InspectionModifiedById, I.MODIFIEDON AS InspectionModifiedOn, I.STATUS AS InspectionStatus, I.VERSION AS InspectionVersion FROM TINSPECTION I LEFT JOIN MINSPECTIONTYPE IT ON IT.INSPECTIONTYPEID = I.INSPECTIONTYPEID LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = I.OUID LEFT JOIN MPERIOD P ON P.PERIODID = I.PERIODID LEFT JOIN MBIZTRANSACTIONTYPE BTT ON BTT.BIZTRANSACTIONTYPEID = I.BIZTRANSACTIONTYPEID LEFT JOIN MWORKCENTER WC ON WC.WORKCENTERID = I.WORKCENTERID LEFT JOIN MSTORE FS ON FS.STOREID = I.FROMSTOREID LEFT JOIN MSTORE TS ON TS.STOREID = I.TOSTOREID LEFT JOIN MINSPECTIONAREA IA ON IA.INSPECTIONAREAID = I.INSPECTIONAREAID WHERE I.INSPECTIONID = @InspectionId AND I.STATUS <> 2;"; // ── Save header (INSERT) ─────────────────────────────────────────────── // FIX [QB-SAVE-1]: The DTO is passed directly, so Dapper sends ALL DTO // properties as parameters. The @Param names must exactly match DTO property // names (case-insensitive). Column names in the INSERT must match the DB table. // // FIX [QB-SAVE-2]: RESULTREMARKS column added to TINSPECTIONRESULT INSERT // (see SAVE_INSPECTION_RESULT below) — was missing, causing silent data loss. // // FIX [QB-SAVE-3]: CREATEDBYID/CREATEDON added to TINSPECTIONDETAIL INSERT // (see SAVE_INSPECTION_DETAIL below) — were missing. // // If STAGEID/PARTYID/SHIFTID do not exist in your TINSPECTION table, run: // ALTER TABLE TINSPECTION ADD STAGEID INT NULL; // ALTER TABLE TINSPECTION ADD PARTYID INT NULL; // ALTER TABLE TINSPECTION ADD SHIFTID INT NULL; public const string SAVE_INSPECTION = @" INSERT INTO TINSPECTION (INSPECTIONID, INSPECTIONDATE, OUID, PERIODID, STAGEID, PARTYID, SHIFTID, BIZTRANSACTIONTYPEID, REFERENCENUMBER, REFERENCEDATE, WORKCENTERID, FROMSTOREID, TOSTOREID, INSPECTIONNUMBER, INSPECTIONAREAID, INSPECTIONTYPE, INSPECTIONTYPEID, LOADBIZTRANSACTIONTYPEID, LOADHEADEROBJECTTYPEID, LOADHEADEROBJECTID, TOTALPACKQUANTITY, TOTALQUANTITY, REMARKS, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON) VALUES (@InspectionId, @InspectionDate, @OUId, @PeriodId, @StageId, @PartyId, @ShiftId, @BizTransactionTypeId, @InspectionReferenceNumber, @InspectionReferenceDate, @WorkCenterId, @FromStoreId, @ToStoreId, @InspectionNumber, @InspectionAreaId, @InspectionInspectionType, @InspectionTypeId, @LoadBizTransactionTypeId, @LoadHeaderObjectTypeId, @InspectionLoadHeaderObjectId, @InspectionTotalPackQuantity, @InspectionTotalQuantity, @InspectionRemarks, @InspectionStatus, @InspectionVersion, @InspectionCreatedById, @InspectionCreatedOn, @InspectionModifiedById, @InspectionModifiedOn);"; // ── Update header ────────────────────────────────────────────────────── public const string UPDATE_INSPECTION = @" UPDATE TINSPECTION SET INSPECTIONDATE = @InspectionDate, OUID = @OUId, PERIODID = @PeriodId, STAGEID = @StageId, PARTYID = @PartyId, SHIFTID = @ShiftId, BIZTRANSACTIONTYPEID = @BizTransactionTypeId, REFERENCENUMBER = @InspectionReferenceNumber, REFERENCEDATE = @InspectionReferenceDate, WORKCENTERID = @WorkCenterId, FROMSTOREID = @FromStoreId, TOSTOREID = @ToStoreId, INSPECTIONAREAID = @InspectionAreaId, INSPECTIONTYPE = @InspectionInspectionType, INSPECTIONTYPEID = @InspectionTypeId, LOADBIZTRANSACTIONTYPEID = @LoadBizTransactionTypeId, LOADHEADEROBJECTTYPEID = @LoadHeaderObjectTypeId, LOADHEADEROBJECTID = @InspectionLoadHeaderObjectId, TOTALPACKQUANTITY = @InspectionTotalPackQuantity, TOTALQUANTITY = @InspectionTotalQuantity, REMARKS = @InspectionRemarks, STATUS = @InspectionStatus, VERSION = @InspectionVersion, MODIFIEDBYID = @InspectionModifiedById, MODIFIEDON = @InspectionModifiedOn WHERE INSPECTIONID = @InspectionId;"; // ── Soft delete ──────────────────────────────────────────────────────── public const string DELETE_INSPECTION = @" UPDATE TINSPECTION SET STATUS = 2, MODIFIEDBYID = @InspectionModifiedById, MODIFIEDON = @InspectionModifiedOn WHERE INSPECTIONID = @InspectionId;"; // ── Picklist ─────────────────────────────────────────────────────────── public const string GET_PICKLIST_INSPECTION = @" SELECT DISTINCT I.INSPECTIONID AS InspectionId, I.INSPECTIONDATE AS InspectionDate, I.INSPECTIONNUMBER AS InspectionNumber, IT.INSPECTIONTYPEID AS InspectionTypeId, IT.INSPECTIONTYPECODE AS InspectionTypeCode, IT.INSPECTIONTYPENAME AS InspectionTypeName FROM TINSPECTION I LEFT JOIN MINSPECTIONTYPE IT ON IT.INSPECTIONTYPEID = I.INSPECTIONTYPEID WHERE I.STATUS <> 2 {DYNAMIC_WHERE};"; // ── Composite full-get (6 result sets for QueryMultipleAsync) ────────── // Result set index: // 0 = Header 1 = Detail 2 = Parameter // 3 = Result 4 = Defect 5 = Worker // // KEY FIX — Parameter query (index 2) and GET_INSPECTION_PARAMETER_BY_DETAIL: // INPUTVALUE is a numeric (DECIMAL/FLOAT) column. Dapper cannot implicitly // cast numeric → string, so InspectionParameterInputValue arrives null. // CAST(P.INPUTVALUE AS NVARCHAR(50)) fixes this — Dapper maps it cleanly. // Without this cast, CollapseParameterSamples cannot rebuild the // comma-joined sample string for the UI. // // FIX [QB-GET-1]: GET_INSPECTION_FULL header now includes PARTYID, SHIFTID, // STAGEID in the SELECT (they were present in GET_INSPECTION but absent // here — caused those fields to always return null on full-get). public const string GET_INSPECTION_FULL = @" -- 0: Header SELECT I.INSPECTIONID AS InspectionId, I.INSPECTIONDATE AS InspectionDate, I.OUID AS OUId, OU.ORGANIZATIONUNITCODE AS OUCode, OU.ORGANIZATIONUNITNAME AS OUName, I.PERIODID AS PeriodId, P.PERIODCODE AS PeriodCode, P.PERIODNAME AS PeriodName, I.STAGEID AS StageId, GCM.GCMCODE AS StageCode, GCM.GCMNAME AS StageName, I.PARTYID AS PartyId, MP.PARTYCODE AS PartyCode, MP.PARTYNAME AS PartyName, I.SHIFTID AS ShiftId, MS.SHIFTCODE AS ShiftCode, MS.SHIFTDESCRIPTION AS ShiftName, I.BIZTRANSACTIONTYPEID AS BizTransactionTypeId, BTT.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode, BTT.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName, BTT.BIZTRANSACTIONCLASSID AS BizTransactionTypeBIZTransactionTypeClassId, I.REFERENCENUMBER AS InspectionReferenceNumber, I.REFERENCEDATE AS InspectionReferenceDate, I.WORKCENTERID AS WorkCenterId, WC.WORKCENTERCODE AS WorkCenterCode, WC.WORKCENTERNAME AS WorkCenterName, I.FROMSTOREID AS FromStoreId, FS.STORECODE AS FromStoreCode, FS.STORENAME AS FromStoreName, I.TOSTOREID AS ToStoreId, TS.STORECODE AS ToStoreCode, TS.STORENAME AS ToStoreName, I.INSPECTIONNUMBER AS InspectionNumber, I.INSPECTIONAREAID AS InspectionAreaId, IA.INSPECTIONAREA AS InspectionAreaArea, I.INSPECTIONTYPE AS InspectionInspectionType, I.INSPECTIONTYPEID AS InspectionTypeId, IT.INSPECTIONTYPECODE AS InspectionTypeCode, IT.INSPECTIONTYPENAME AS InspectionTypeName, I.LOADBIZTRANSACTIONTYPEID AS LoadBizTransactionTypeId, I.LOADHEADEROBJECTTYPEID AS LoadHeaderObjectTypeId, HOT.ENTITYCODE AS LoadHeaderObjectTypeCode, HOT.ENTITYNAME AS LoadHeaderObjectTypeName, I.LOADHEADEROBJECTID AS InspectionLoadHeaderObjectId, I.TOTALPACKQUANTITY AS InspectionTotalPackQuantity, I.TOTALQUANTITY AS InspectionTotalQuantity, I.REMARKS AS InspectionRemarks, I.CREATEDBYID AS InspectionCreatedById, I.CREATEDON AS InspectionCreatedOn, I.MODIFIEDBYID AS InspectionModifiedById, I.MODIFIEDON AS InspectionModifiedOn, I.STATUS AS InspectionStatus, I.VERSION AS InspectionVersion FROM TINSPECTION I LEFT JOIN MINSPECTIONTYPE IT ON IT.INSPECTIONTYPEID = I.INSPECTIONTYPEID LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = I.OUID LEFT JOIN MPERIOD P ON P.PERIODID = I.PERIODID LEFT JOIN MBIZTRANSACTIONTYPE BTT ON BTT.BIZTRANSACTIONTYPEID = I.BIZTRANSACTIONTYPEID LEFT JOIN MWORKCENTER WC ON WC.WORKCENTERID = I.WORKCENTERID LEFT JOIN MSTORE FS ON FS.STOREID = I.FROMSTOREID LEFT JOIN MSTORE TS ON TS.STOREID = I.TOSTOREID LEFT JOIN MINSPECTIONAREA IA ON IA.INSPECTIONAREAID = I.INSPECTIONAREAID LEFT JOIN MPARTY MP ON MP.PARTYID = I.PARTYID LEFT JOIN MSHIFT MS ON MS.SHIFTID = I.SHIFTID LEFT JOIN MGCM GCM ON GCM.GCMID = I.STAGEID LEFT JOIN MENTITY HOT ON HOT.ENTITYID = I.LOADHEADEROBJECTTYPEID WHERE I.INSPECTIONID = @InspectionId AND I.STATUS <> 2; -- 1: Detail SELECT D.INSPECTIONDETAILID AS InspectionDetailId, D.INSPECTIONID AS InspectionId, D.SLNO AS InspectionDetailSlNo, D.LOADOBJECTTYPEID AS LoadObjectTypeId, LOT.ENTITYCODE AS LoadObjectTypeCode, LOT.ENTITYNAME AS LoadObjectTypeName, D.LOADOBJECTID AS InspectionDetailLoadObjectId, D.ITEMID AS ItemId, MI.ITEMCODE AS ItemCode, MI.ITEMNAME AS ItemName, D.SKUID AS SkuId, MSK.SKUCODE AS SkuCode, MSK.SKUNAME AS SkuName, D.LOTID AS LotId, ML.LOTNUMBER AS LotLotNumber, D.SPECIFICATIONREFERENCE AS SpecificationReference, D.BINID AS BinId, MB.BINNUMBER AS BinCode, D.BINIDS AS BinIds, D.PARAMETERSETID AS ParameterSetId, PS.PARAMETERSETCODE AS ParameterSetCode, PS.PARAMETERSETNAME AS ParameterSetName, D.ROUTINGPARAMETERSETID AS RoutingParameterSetId, MR.ROUTINGCODE AS RoutingCode, MR.ROUTINGNAME AS RoutingName, D.NUMBEROFSAMPLES AS InspectionDetailNumberOfSamples, D.DOCUMENTQUANTITY AS InspectionDetailDocumentQuantity, D.INSPECTEDQUANTITY AS InspectionDetailInspectedQuantity, D.SHORTAGEQUANTITY AS InspectionDetailShortageQuantity, D.EXCESSQUANTITY AS InspectionDetailExcessQuantity, D.GOODQUANTITY AS InspectionDetailGoodQuantity, D.REJECTEDQUANTITY AS InspectionDetailRejectedQuantity, D.REWORKQUANTITY AS InspectionDetailReworkQuantity, D.OTHERQUANTITY AS InspectionDetailOtherQuantity, D.TOTALPOINTS AS InspectionDetailTotalPoints, D.TOTALPOINTSAFTER AS InspectionDetailTotalPointsAfter, D.QCCOMPARISONSTATUS AS InspectionDetailQcComparisonStatus, D.PACKNUMBERID AS PackNumberId, MP2.PACKCODE AS PackCode, MP2.PACKNAME AS PackName, D.PICK AS InspectionDetailPick FROM TINSPECTIONDETAIL D LEFT JOIN MPARAMETERSET PS ON PS.PARAMETERSETID = D.PARAMETERSETID LEFT JOIN MPACK MP2 ON MP2.PACKID = D.PACKNUMBERID LEFT JOIN MROUTING MR ON MR.ROUTINGID = D.ROUTINGPARAMETERSETID LEFT JOIN MITEM MI ON MI.ITEMID = D.ITEMID LEFT JOIN MSKU MSK ON MSK.SKUID = D.SKUID LEFT JOIN TLOT ML ON ML.LOTID = D.LOTID LEFT JOIN MENTITY LOT ON LOT.ENTITYID = D.LOADOBJECTTYPEID LEFT JOIN MBIN MB ON MB.BINID = D.BINID WHERE D.INSPECTIONID = @InspectionId ORDER BY D.SLNO; -- 2: Parameter -- CAST(P.INPUTVALUE AS NVARCHAR(50)): -- Dapper cannot auto-cast numeric DB column → string DTO property. -- This explicit cast ensures InspectionParameterInputValue is populated -- so CollapseParameterSamples can rebuild comma-joined sample strings. -- ParameterSection reads P.SECTION directly (persisted at save time) — -- NOT re-derived via a join to MPARAMETERSETDETAIL. That join is -- one-to-many whenever a ParameterId is configured under more than one -- section in the master ParameterSet, which fans out and duplicates -- every saved row into each section it's configured in. Falls back to -- the master's section only for legacy rows saved before SECTION existed -- (NULL on old rows) via the PSD join, so old data still renders once -- rather than showing blank — new rows always use P.SECTION. SELECT P.INSPECTIONPARAMETERID AS InspectionParameterId, P.INSPECTIONDETAILID AS InspectionDetailId, P.SLNO AS InspectionParameterSlNo, P.SAMPLENUMBER AS InspectionParameterSampleNumber, P.PARAMETERSLNO AS InspectionParameterParameterSlNo, P.PARAMETERID AS ParameterId, MP.PARAMETERCODE AS ParameterCode, MP.PARAMETERNAME AS ParameterName, P.UOMID AS UomId, ISNULL(MU.UOMCODE, '') AS UomCode, ISNULL(MU.UOMNAME, '') AS UomName, ISNULL(P.SECTION, ISNULL(PSD.SECTION, '')) AS ParameterSection, P.AVERAGE AS InspectionParameterAverage, P.RESULT AS InspectionParameterResult, CAST(P.INPUTVALUE AS NVARCHAR(50)) AS InspectionParameterInputValue, P.INPUTTEXT AS InspectionParameterInputText, P.INPUTDATE AS InspectionParameterInputDate, P.FINALVALUE AS InspectionParameterFinalValue, ISNULL(P.MINVALUE, 0) AS MinVal, ISNULL(P.MAXVALUE, 0) AS MaxVal FROM TINSPECTIONPARAMETER P INNER JOIN TINSPECTIONDETAIL D ON D.INSPECTIONDETAILID = P.INSPECTIONDETAILID AND D.INSPECTIONID = @InspectionId INNER JOIN MPARAMETER MP ON MP.PARAMETERID = P.PARAMETERID LEFT JOIN MUOM MU ON MU.UOMID = P.UOMID LEFT JOIN MPARAMETERSETDETAIL PSD ON PSD.PARAMETERSETID = D.PARAMETERSETID AND PSD.PARAMETERID = P.PARAMETERID AND P.SECTION IS NULL LEFT JOIN MSKU SK ON SK.SKUID = D.SKUID LEFT JOIN MMATERIALGRADE MG ON MG.MATERIALGRADEID = TRY_CAST(SK.GRADE AS INT) LEFT JOIN MSPECIFICATION MS ON MS.OBJECTID = MG.MATERIALGRADEID AND MS.STATUS <> 2 LEFT JOIN MSPECIFICATIONDETAIL SD ON SD.SPECIFICATIONID = MS.SPECIFICATIONID AND SD.PARAMETERID = P.PARAMETERID ORDER BY P.INSPECTIONDETAILID, P.SLNO, P.SAMPLENUMBER; -- 3: Result -- FIX [QB-GET-2]: Added APPROVEDBYID + RESULTREMARKS — were missing from -- original GET_INSPECTION_RESULT_BY_DETAIL, causing those fields to return -- null even though the DB stores them. SELECT R.INSPECTIONRESULTID AS InspectionResultId, R.INSPECTIONDETAILID AS InspectionDetailId, R.SLNO AS InspectionResultSlNo, R.ITEMID AS ItemId, MI.ITEMCODE AS ItemCode, MI.ITEMNAME AS ItemName, R.SKUID AS SkuId, MSK.SKUCODE AS SkuCode, MSK.SKUNAME AS SkuName, R.LOTID AS LotId, ML.LOTNUMBER AS LotLotNumber, R.QUANTITY AS InspectionResultQuantity, R.INSPECTIONRESULT AS InspectionResultInspectionResults, R.APPROVEDBYID AS ApprovedById, UA.USERNAME AS ApprovedByName, R.RESULTREMARKS AS ResultRemarks FROM TINSPECTIONRESULT R INNER JOIN TINSPECTIONDETAIL D ON D.INSPECTIONDETAILID = R.INSPECTIONDETAILID AND D.INSPECTIONID = @InspectionId LEFT JOIN MITEM MI ON MI.ITEMID = R.ITEMID LEFT JOIN MSKU MSK ON MSK.SKUID = R.SKUID LEFT JOIN TLOT ML ON ML.LOTID = R.LOTID LEFT JOIN MUSER UA ON UA.USERID = R.APPROVEDBYID ORDER BY R.INSPECTIONDETAILID, R.SLNO; -- 4: Defect SELECT DF.INSPECTIONDEFECTID AS InspectionDefectId, DF.INSPECTIONDETAILID AS InspectionDetailId, DF.SLNO AS InspectionDefectSlNo, DF.QUANTITYMARK AS InspectionDefectQuantityMark, DF.DEFECTID AS DefectId, MDF.DEFECTCODE AS DefectCode, MDF.DEFECTNAME AS DefectName, DF.CATEGORY AS DefectCategory, DF.NUMBEROFDEFECTS AS InspectionDefectNumberOfDefects, DF.POINTS AS InspectionDefectPoints, DF.NUMBEROFDEFECTSAFTER AS InspectionDefectNumberOfDefectsAfter, DF.POINTSAFTER AS InspectionDefectPointsAfter FROM TINSPECTIONDEFECT DF INNER JOIN TINSPECTIONDETAIL D ON D.INSPECTIONDETAILID = DF.INSPECTIONDETAILID AND D.INSPECTIONID = @InspectionId LEFT JOIN MDEFECT MDF ON MDF.DEFECTID = DF.DEFECTID ORDER BY DF.INSPECTIONDETAILID, DF.SLNO; -- 5: Worker SELECT W.INSPECTIONWORKERDETAILID AS InspectionWorkerDetailId, W.INSPECTIONID AS InspectionId, W.SLNO AS InspectionWorkerDetailSlNo, W.USERID AS UserId, U.USERCODE AS UserCode, U.USERNAME AS UserName FROM TINSPECTIONWORKERDETAIL W LEFT JOIN MUSER U ON U.USERID = W.USERID WHERE W.INSPECTIONID = @InspectionId ORDER BY W.SLNO;"; // ── Paginated list ───────────────────────────────────────────────────── // FIX [QB-LIST-1]: Added WHERE I.STATUS <> 2 filter (was missing — deleted // records appeared in the list). // FIX [QB-LIST-2]: @InspectionTypeId, @FromDate, @ToDate use IS NULL guard // so passing null skips that filter (already correct — preserved as-is). public const string GET_INSPECTION_LIST = @" SELECT I.INSPECTIONID AS InspectionId, I.INSPECTIONNUMBER AS InspectionNumber, I.INSPECTIONDATE AS InspectionDate, IT.INSPECTIONTYPECODE AS InspectionTypeCode, IT.INSPECTIONTYPENAME AS InspectionTypeName, I.REFERENCENUMBER AS ReferenceNumber, I.TOTALQUANTITY AS TotalQuantity, STRING_AGG(U.USERNAME, ', ') WITHIN GROUP (ORDER BY W.SLNO) AS InspectorNames, COUNT(*) OVER() AS TotalCount FROM TINSPECTION I LEFT JOIN MINSPECTIONTYPE IT ON IT.INSPECTIONTYPEID = I.INSPECTIONTYPEID LEFT JOIN TINSPECTIONWORKERDETAIL W ON W.INSPECTIONID = I.INSPECTIONID LEFT JOIN MUSER U ON U.USERID = W.USERID WHERE I.OUID = @OUId AND I.PERIODID = @PeriodId AND I.STATUS <> 2 AND (@InspectionTypeId IS NULL OR I.INSPECTIONTYPEID = @InspectionTypeId) AND (@FromDate IS NULL OR I.INSPECTIONDATE >= @FromDate) AND (@ToDate IS NULL OR I.INSPECTIONDATE <= @ToDate) GROUP BY I.INSPECTIONID, I.INSPECTIONNUMBER, I.INSPECTIONDATE, IT.INSPECTIONTYPECODE, IT.INSPECTIONTYPENAME, I.REFERENCENUMBER, I.TOTALQUANTITY ORDER BY I.INSPECTIONDATE DESC, I.INSPECTIONID DESC OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;"; // ── Inspection number generation ─────────────────────────────────────── public const string GET_INSPECTION_TYPE_CODE = @" SELECT INSPECTIONTYPECODE FROM MINSPECTIONTYPE WHERE INSPECTIONTYPEID = @InspectionTypeId AND STATUS <> 2;"; // FIX [QB-LASTNUM-1]: Returns the most recent InspectionNumber string // (format: "INS-0000001") for the given type+OU in the current year. // Used by BLL.GenerateInspectionNumber for preview only — the actual // saved number is derived from the AutoNumber ID (no race condition). public const string GET_LAST_INSPECTION_NUMBER = @" SELECT TOP 1 INSPECTIONNUMBER FROM TINSPECTION WHERE INSPECTIONTYPEID = @InspectionTypeId AND OUID = @OuId AND YEAR(INSPECTIONDATE) = YEAR(GETUTCDATE()) AND STATUS <> 2 ORDER BY INSPECTIONID DESC;"; // ── Detail child CRUD ────────────────────────────────────────────────── // FIX [QB-DETAIL-1]: Added CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON // to the INSERT — were missing in original, causing audit columns to be null. public const string SAVE_INSPECTION_DETAIL = @" INSERT INTO TINSPECTIONDETAIL (INSPECTIONDETAILID, INSPECTIONID, SLNO, LOADOBJECTTYPEID, LOADOBJECTID, ITEMID, SKUID, LOTID, SPECIFICATIONREFERENCE, PARAMETERSETID, ROUTINGPARAMETERSETID, NUMBEROFSAMPLES, DOCUMENTQUANTITY, INSPECTEDQUANTITY, SHORTAGEQUANTITY, EXCESSQUANTITY, GOODQUANTITY, REJECTEDQUANTITY, REWORKQUANTITY, OTHERQUANTITY, TOTALPOINTS, TOTALPOINTSAFTER, QCCOMPARISONSTATUS, BINIDS, BINID, PACKNUMBERID, PICK, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON) VALUES (@InspectionDetailId, @InspectionId, @InspectionDetailSlNo, @LoadObjectTypeId, @InspectionDetailLoadObjectId, @ItemId, @SkuId, @LotId, @SpecificationReference, @ParameterSetId, @RoutingParameterSetId, @InspectionDetailNumberOfSamples, @InspectionDetailDocumentQuantity, @InspectionDetailInspectedQuantity, @InspectionDetailShortageQuantity, @InspectionDetailExcessQuantity, @InspectionDetailGoodQuantity, @InspectionDetailRejectedQuantity, @InspectionDetailReworkQuantity, @InspectionDetailOtherQuantity, @InspectionDetailTotalPoints, @InspectionDetailTotalPointsAfter, @InspectionDetailQcComparisonStatus, @BinIds, @BinId, @PackNumberId, @InspectionDetailPick, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn);"; public const string DELETE_INSPECTION_DETAIL_BY_INSPECTION = @" DELETE FROM TINSPECTIONDETAIL WHERE INSPECTIONID = @InspectionId;"; // Cascade deletes — used by update path (delete all then re-insert) public const string DELETE_INSPECTION_PARAMETER_BY_INSPECTION = @" DELETE P FROM TINSPECTIONPARAMETER P INNER JOIN TINSPECTIONDETAIL D ON D.INSPECTIONDETAILID = P.INSPECTIONDETAILID WHERE D.INSPECTIONID = @InspectionId;"; public const string DELETE_INSPECTION_RESULT_BY_INSPECTION = @" DELETE R FROM TINSPECTIONRESULT R INNER JOIN TINSPECTIONDETAIL D ON D.INSPECTIONDETAILID = R.INSPECTIONDETAILID WHERE D.INSPECTIONID = @InspectionId;"; public const string DELETE_INSPECTION_DEFECT_BY_INSPECTION = @" DELETE DF FROM TINSPECTIONDEFECT DF INNER JOIN TINSPECTIONDETAIL D ON D.INSPECTIONDETAILID = DF.INSPECTIONDETAILID WHERE D.INSPECTIONID = @InspectionId;"; // ── Parameter child CRUD ─────────────────────────────────────────────── // INPUTDATE is nullable in DB (confirmed by DateTime? in DTO). // @InspectionParameterInputDate will be NULL when not provided — safe. // SECTION persists the section the client actually saved this row under // (e.g. "Mechanical"). Required because a ParameterId can be configured // under more than one section in the master ParameterSet — deriving the // section at GET time via a join to MPARAMETERSETDETAIL is one-to-many // and duplicates the row into every section it's configured in. public const string SAVE_INSPECTION_PARAMETER = @" INSERT INTO TINSPECTIONPARAMETER (INSPECTIONPARAMETERID, INSPECTIONDETAILID, SLNO, SAMPLENUMBER, PARAMETERSLNO, PARAMETERID, UOMID, AVERAGE, RESULT, INPUTVALUE, INPUTTEXT, INPUTDATE, FINALVALUE, MINVALUE, MAXVALUE, SECTION, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON) VALUES (@InspectionParameterId, @InspectionDetailId, @InspectionParameterSlNo, @InspectionParameterSampleNumber, @InspectionParameterParameterSlNo, @ParameterId, @UomId, @InspectionParameterAverage, @InspectionParameterResult, @InspectionParameterInputValue, @InspectionParameterInputText, @InspectionParameterInputDate, @InspectionParameterFinalValue, @MinVal, @MaxVal, @ParameterSection, 1, 1, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn);"; public const string DELETE_INSPECTION_PARAMETER_BY_DETAIL = @" DELETE FROM TINSPECTIONPARAMETER WHERE INSPECTIONDETAILID = @InspectionDetailId;"; // ── Result child CRUD ────────────────────────────────────────────────── // FIX [QB-RESULT-1]: Added APPROVEDBYID and RESULTREMARKS to INSERT. // These fields exist in the DB (confirmed by GET queries) but were // missing from the INSERT — silently saving null every time. public const string SAVE_INSPECTION_RESULT = @" INSERT INTO TINSPECTIONRESULT (INSPECTIONRESULTID, INSPECTIONDETAILID, SLNO, ITEMID, SKUID, LOTID, APPROVEDBYID, RESULTREMARKS, QUANTITY, INSPECTIONRESULT, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON) VALUES (@InspectionResultId, @InspectionDetailId, @InspectionResultSlNo, @ItemId, @SkuId, @LotId, @ApprovedById, @ResultRemarks, @InspectionResultQuantity, @InspectionResultInspectionResults, 1, 1, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn);"; public const string DELETE_INSPECTION_RESULT_BY_DETAIL = @" DELETE FROM TINSPECTIONRESULT WHERE INSPECTIONDETAILID = @InspectionDetailId;"; // ── Defect child CRUD ────────────────────────────────────────────────── public const string SAVE_INSPECTION_DEFECT = @" INSERT INTO TINSPECTIONDEFECT (INSPECTIONDEFECTID, INSPECTIONDETAILID, SLNO, QUANTITYMARK, DEFECTID, NUMBEROFDEFECTS, POINTS, NUMBEROFDEFECTSAFTER, POINTSAFTER,CATEGORY, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON) VALUES (@InspectionDefectId, @InspectionDetailId, @InspectionDefectSlNo, @InspectionDefectQuantityMark, @DefectId, @InspectionDefectNumberOfDefects, @InspectionDefectPoints, @InspectionDefectNumberOfDefectsAfter, @InspectionDefectPointsAfter,@DefectCategory, 1, 1, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn);"; public const string DELETE_INSPECTION_DEFECT_BY_DETAIL = @" DELETE FROM TINSPECTIONDEFECT WHERE INSPECTIONDETAILID = @InspectionDetailId;"; // ── Worker child CRUD ────────────────────────────────────────────────── public const string SAVE_INSPECTION_WORKER = @" INSERT INTO TINSPECTIONWORKERDETAIL (INSPECTIONWORKERDETAILID, INSPECTIONID, SLNO, USERID, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON) VALUES (@InspectionWorkerDetailId, @InspectionId, @InspectionWorkerDetailSlNo, @UserId, 1, 1, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn);"; public const string DELETE_INSPECTION_WORKER_BY_INSPECTION = @" DELETE FROM TINSPECTIONWORKERDETAIL WHERE INSPECTIONID = @InspectionId;"; // ── Individual child GET endpoints ───────────────────────────────────── public const string GET_INSPECTION_DETAIL_BY_INSPECTION = @" SELECT D.INSPECTIONDETAILID AS InspectionDetailId, D.INSPECTIONID AS InspectionId, D.SLNO AS InspectionDetailSlNo, D.LOADOBJECTTYPEID AS LoadObjectTypeId, D.LOADOBJECTID AS InspectionDetailLoadObjectId, D.ITEMID AS ItemId, MI.ITEMCODE AS ItemCode, MI.ITEMNAME AS ItemName, D.SKUID AS SkuId, MSK.SKUCODE AS SkuCode, MSK.SKUNAME AS SkuName, D.LOTID AS LotId, ML.LOTNUMBER AS LotLotNumber, D.SPECIFICATIONREFERENCE AS SpecificationReference, D.BINID AS BinId, D.BINIDS AS BinIds, D.PARAMETERSETID AS ParameterSetId, PS.PARAMETERSETCODE AS ParameterSetCode, PS.PARAMETERSETNAME AS ParameterSetName, D.ROUTINGPARAMETERSETID AS RoutingParameterSetId, D.NUMBEROFSAMPLES AS InspectionDetailNumberOfSamples, D.DOCUMENTQUANTITY AS InspectionDetailDocumentQuantity, D.INSPECTEDQUANTITY AS InspectionDetailInspectedQuantity, D.SHORTAGEQUANTITY AS InspectionDetailShortageQuantity, D.EXCESSQUANTITY AS InspectionDetailExcessQuantity, D.GOODQUANTITY AS InspectionDetailGoodQuantity, D.REJECTEDQUANTITY AS InspectionDetailRejectedQuantity, D.REWORKQUANTITY AS InspectionDetailReworkQuantity, D.OTHERQUANTITY AS InspectionDetailOtherQuantity, D.TOTALPOINTS AS InspectionDetailTotalPoints, D.TOTALPOINTSAFTER AS InspectionDetailTotalPointsAfter, D.QCCOMPARISONSTATUS AS InspectionDetailQcComparisonStatus, D.PACKNUMBERID AS PackNumberId, D.PICK AS InspectionDetailPick FROM TINSPECTIONDETAIL D LEFT JOIN MPARAMETERSET PS ON PS.PARAMETERSETID = D.PARAMETERSETID LEFT JOIN MITEM MI ON MI.ITEMID = D.ITEMID LEFT JOIN MSKU MSK ON MSK.SKUID = D.SKUID LEFT JOIN TLOT ML ON ML.LOTID = D.LOTID WHERE D.INSPECTIONID = @InspectionId ORDER BY D.SLNO;"; // CAST fix: same as GET_INSPECTION_FULL parameter query public const string GET_INSPECTION_PARAMETER_BY_DETAIL = @" SELECT P.INSPECTIONPARAMETERID AS InspectionParameterId, P.INSPECTIONDETAILID AS InspectionDetailId, P.SLNO AS InspectionParameterSlNo, P.SAMPLENUMBER AS InspectionParameterSampleNumber, P.PARAMETERSLNO AS InspectionParameterParameterSlNo, P.PARAMETERID AS ParameterId, MP.PARAMETERCODE AS ParameterCode, MP.PARAMETERNAME AS ParameterName, P.UOMID AS UomId, ISNULL(MU.UOMCODE, '') AS UomCode, ISNULL(MU.UOMNAME, '') AS UomName, ISNULL(P.SECTION, ISNULL(PSD.SECTION, '')) AS ParameterSection, P.AVERAGE AS InspectionParameterAverage, P.RESULT AS InspectionParameterResult, CAST(P.INPUTVALUE AS NVARCHAR(50)) AS InspectionParameterInputValue, P.INPUTTEXT AS InspectionParameterInputText, P.INPUTDATE AS InspectionParameterInputDate, P.FINALVALUE AS InspectionParameterFinalValue, ISNULL(P.MINVALUE, 0) AS MinVal, ISNULL(P.MAXVALUE, 0) AS MaxVal FROM TINSPECTIONPARAMETER P INNER JOIN TINSPECTIONDETAIL D ON D.INSPECTIONDETAILID = P.INSPECTIONDETAILID INNER JOIN MPARAMETER MP ON MP.PARAMETERID = P.PARAMETERID LEFT JOIN MUOM MU ON MU.UOMID = P.UOMID LEFT JOIN MPARAMETERSETDETAIL PSD ON PSD.PARAMETERSETID = D.PARAMETERSETID AND PSD.PARAMETERID = P.PARAMETERID AND P.SECTION IS NULL LEFT JOIN MSKU SK ON SK.SKUID = D.SKUID LEFT JOIN MSPECIFICATION MS ON MS.OBJECTID = SK.MATERIALGRADEID AND MS.STATUS <> 2 LEFT JOIN MSPECIFICATIONDETAIL SD ON SD.SPECIFICATIONID = MS.SPECIFICATIONID AND SD.PARAMETERID = P.PARAMETERID WHERE P.INSPECTIONDETAILID = @InspectionDetailId ORDER BY P.SLNO, P.SAMPLENUMBER;"; // FIX [QB-RESULT-GET-1]: Added APPROVEDBYID, APPROVEDBYNAME, RESULTREMARKS // — were missing in the original standalone GET, causing nulls on read-back. public const string GET_INSPECTION_RESULT_BY_DETAIL = @" SELECT R.INSPECTIONRESULTID AS InspectionResultId, R.INSPECTIONDETAILID AS InspectionDetailId, R.SLNO AS InspectionResultSlNo, R.ITEMID AS ItemId, MI.ITEMCODE AS ItemCode, MI.ITEMNAME AS ItemName, R.SKUID AS SkuId, MSK.SKUCODE AS SkuCode, MSK.SKUNAME AS SkuName, R.LOTID AS LotId, ML.LOTNUMBER AS LotLotNumber, R.QUANTITY AS InspectionResultQuantity, R.INSPECTIONRESULT AS InspectionResultInspectionResults, R.APPROVEDBYID AS ApprovedById, UA.USERNAME AS ApprovedByName, R.RESULTREMARKS AS ResultRemarks FROM TINSPECTIONRESULT R LEFT JOIN MITEM MI ON MI.ITEMID = R.ITEMID LEFT JOIN MSKU MSK ON MSK.SKUID = R.SKUID LEFT JOIN TLOT ML ON ML.LOTID = R.LOTID LEFT JOIN MUSER UA ON UA.USERID = R.APPROVEDBYID WHERE R.INSPECTIONDETAILID = @InspectionDetailId ORDER BY R.SLNO;"; public const string GET_INSPECTION_DEFECT_BY_DETAIL = @" SELECT DF.INSPECTIONDEFECTID AS InspectionDefectId, DF.INSPECTIONDETAILID AS InspectionDetailId, DF.SLNO AS InspectionDefectSlNo, DF.QUANTITYMARK AS InspectionDefectQuantityMark, DF.DEFECTID AS DefectId, MDF.DEFECTCODE AS DefectCode, MDF.DEFECTNAME AS DefectName, DF.NUMBEROFDEFECTS AS InspectionDefectNumberOfDefects, DF.POINTS AS InspectionDefectPoints, DF.NUMBEROFDEFECTSAFTER AS InspectionDefectNumberOfDefectsAfter, DF.POINTSAFTER AS InspectionDefectPointsAfter FROM TINSPECTIONDEFECT DF LEFT JOIN MDEFECT MDF ON MDF.DEFECTID = DF.DEFECTID WHERE DF.INSPECTIONDETAILID = @InspectionDetailId ORDER BY DF.SLNO;"; public const string GET_INSPECTION_WORKER_BY_INSPECTION = @" SELECT W.INSPECTIONWORKERDETAILID AS InspectionWorkerDetailId, W.INSPECTIONID AS InspectionId, W.SLNO AS InspectionWorkerDetailSlNo, W.USERID AS UserId, U.USERCODE AS UserCode, U.USERNAME AS UserName FROM TINSPECTIONWORKERDETAIL W LEFT JOIN MUSER U ON U.USERID = W.USERID WHERE W.INSPECTIONID = @InspectionId ORDER BY W.SLNO;"; } }