namespace MMDAL.Query.Inspection
{
    public static class InspectionAnalyticsQB
    {
        public const string GET_KPI = @"
            WITH InspBase AS (
                SELECT D.INSPECTIONDETAILID,
                    D.INSPECTEDQUANTITY, D.GOODQUANTITY, D.REJECTEDQUANTITY, D.REWORKQUANTITY
                FROM TINSPECTIONDETAIL D
                INNER JOIN TINSPECTION I ON I.INSPECTIONID = D.INSPECTIONID
                WHERE I.OUID = @OUId AND I.PERIODID = @PeriodId AND I.STATUS <> 2
            )
            SELECT
                (SELECT COUNT(DISTINCT I.INSPECTIONID)
                 FROM TINSPECTION I
                 WHERE I.OUID = @OUId AND I.PERIODID = @PeriodId AND I.STATUS <> 2)
                    AS TotalInspections,
                ISNULL(SUM(B.INSPECTEDQUANTITY), 0)  AS TotalPiecesInspected,
                ISNULL(SUM(B.GOODQUANTITY), 0)       AS GoodQty,
                ISNULL(SUM(B.REJECTEDQUANTITY), 0)   AS RejectedQty,
                ISNULL(SUM(B.REWORKQUANTITY), 0)     AS ReworkQty,
                CASE WHEN SUM(B.INSPECTEDQUANTITY) > 0
                     THEN ROUND(SUM(B.GOODQUANTITY) * 100.0 / SUM(B.INSPECTEDQUANTITY), 2)
                     ELSE 0 END                      AS YieldPercent,
                CASE WHEN SUM(B.INSPECTEDQUANTITY) > 0
                     THEN ROUND(SUM(B.REJECTEDQUANTITY) * 100.0 / SUM(B.INSPECTEDQUANTITY), 2)
                     ELSE 0 END                      AS RejectionPercent,
                (SELECT COUNT(1) FROM TINSPECTIONRESULT R
                 INNER JOIN InspBase IB ON IB.INSPECTIONDETAILID = R.INSPECTIONDETAILID
                 WHERE R.INSPECTIONRESULT = 1)        AS PassCount,
                (SELECT COUNT(1) FROM TINSPECTIONRESULT R
                 INNER JOIN InspBase IB ON IB.INSPECTIONDETAILID = R.INSPECTIONDETAILID
                 WHERE R.INSPECTIONRESULT = 2)        AS FailCount,
                (SELECT COUNT(1) FROM TINSPECTIONRESULT R
                 INNER JOIN InspBase IB ON IB.INSPECTIONDETAILID = R.INSPECTIONDETAILID
                 WHERE R.INSPECTIONRESULT = 3)        AS ReworkCount
            FROM InspBase B;";

        public const string GET_TREND = @"
            SELECT
                FORMAT(I.INSPECTIONDATE, 'yyyy-MM') AS PeriodLabel,
                ISNULL(SUM(D.INSPECTEDQUANTITY), 0) AS TotalInspected,
                ISNULL(SUM(D.GOODQUANTITY), 0)      AS GoodQuantity,
                ISNULL(SUM(D.REJECTEDQUANTITY), 0)  AS RejectedQuantity,
                ISNULL(SUM(D.REWORKQUANTITY), 0)    AS ReworkQuantity,
                CASE WHEN SUM(D.INSPECTEDQUANTITY) > 0
                     THEN ROUND(SUM(D.GOODQUANTITY) * 100.0 / SUM(D.INSPECTEDQUANTITY), 2)
                     ELSE 0 END                     AS YieldPercent
            FROM TINSPECTION I
            INNER JOIN TINSPECTIONDETAIL D ON D.INSPECTIONID = I.INSPECTIONID
            WHERE I.OUID = @OUId
              AND I.STATUS <> 2
              AND I.INSPECTIONDATE >= DATEADD(MONTH, -@Months, GETUTCDATE())
            GROUP BY FORMAT(I.INSPECTIONDATE, 'yyyy-MM')
            ORDER BY FORMAT(I.INSPECTIONDATE, 'yyyy-MM');";

        public const string GET_DEFECT_PARETO = @"
            SELECT
                DF.DEFECTID                     AS DefectId,
                MD.DEFECTNAME                   AS DefectName,
                MD.DEFECTCATEGORY               AS Category,
                SUM(DF.NUMBEROFDEFECTS)         AS TotalCount,
                COUNT(DISTINCT DF.INSPECTIONDETAILID) AS PiecesAffected
            FROM TINSPECTIONDEFECT DF
            INNER JOIN TINSPECTIONDETAIL D ON D.INSPECTIONDETAILID = DF.INSPECTIONDETAILID
            INNER JOIN TINSPECTION I ON I.INSPECTIONID = D.INSPECTIONID
            LEFT  JOIN MDEFECT MD ON MD.DEFECTID = DF.DEFECTID
            WHERE I.OUID = @OUId AND I.PERIODID = @PeriodId AND I.STATUS <> 2
            GROUP BY DF.DEFECTID, MD.DEFECTNAME, MD.DEFECTCATEGORY
            ORDER BY SUM(DF.NUMBEROFDEFECTS) DESC;";

        public const string GET_PROCESS_YIELD = @"
            SELECT
                I.INSPECTIONTYPEID                          AS InspectionTypeId,
                IT.INSPECTIONTYPECODE                       AS InspectionTypeCode,
                IT.INSPECTIONTYPENAME                       AS InspectionTypeName,
                COUNT(DISTINCT I.INSPECTIONID)              AS InspectionCount,
                ISNULL(SUM(D.INSPECTEDQUANTITY), 0)         AS PiecesQuantity,
                ISNULL(SUM(D.GOODQUANTITY), 0)              AS GoodQuantity,
                ISNULL(SUM(D.REJECTEDQUANTITY), 0)          AS RejectedQuantity,
                ISNULL(SUM(D.REWORKQUANTITY), 0)            AS ReworkQuantity
            FROM TINSPECTION I
            INNER JOIN TINSPECTIONDETAIL D ON D.INSPECTIONID = I.INSPECTIONID
            INNER JOIN MINSPECTIONTYPE IT ON IT.INSPECTIONTYPEID = I.INSPECTIONTYPEID
            WHERE I.OUID = @OUId AND I.PERIODID = @PeriodId AND I.STATUS <> 2
            GROUP BY I.INSPECTIONTYPEID, IT.INSPECTIONTYPECODE, IT.INSPECTIONTYPENAME
            ORDER BY IT.INSPECTIONTYPECODE;";

        public const string GET_PARAM_SUMMARY = @"
            SELECT
                P.PARAMETERID                   AS ParameterId,
                MP.PARAMETERNAME                AS ParameterName,
                ISNULL(MU.UOMNAME, '')          AS UomName,
                ISNULL(P.MINVALUE, 0)    AS MinVal,
                ISNULL(P.MAXVALUE, 0)    AS MaxVal,
                P.FINALVALUE                    AS InspectionParameterFinalValue,
                P.INPUTVALUE                    AS InspectionParameterInputValue,
                P.SAMPLENUMBER                  AS InspectionParameterSampleNumber,
                P.PARAMETERSLNO                 AS InspectionParameterParameterSlNo
            FROM TINSPECTIONPARAMETER P
            INNER JOIN TINSPECTIONDETAIL D ON D.INSPECTIONDETAILID = P.INSPECTIONDETAILID
            INNER JOIN TINSPECTION I ON I.INSPECTIONID = D.INSPECTIONID
            INNER JOIN MPARAMETER MP ON MP.PARAMETERID = P.PARAMETERID
            LEFT  JOIN MUOM MU ON MU.UOMID = P.UOMID
            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 I.INSPECTIONID = @InspectionId
              AND I.STATUS <> 2
            ORDER BY D.SLNO, P.PARAMETERSLNO, P.SAMPLENUMBER;";
    }
}