HEGII.ss.mes.packingline/wwwroot/App_Code/QualifiedPercent.cs

using Curtain.DataAccess;
using System;
using System.Data;

/// <summary>
/// 获取合格率
/// </summary>
public class QualifiedPercent
{
    /// <summary>
    /// 获取合格率
    /// </summary>
    /// <param name="conn">连接</param>
    /// <param name="planDate">计划日期</param>
    /// <returns></returns>
    public static DataTable GetQualifiedPercent(IDataAccess conn, DateTime planDate)
    {
        return conn.ExecuteDatatable(@"
            SELECT T.GOODSID,
                   CASE WHEN QUALIFIEDPERCENT <= 0 OR QUALIFIEDPERCENT > 1 THEN 1 ELSE QUALIFIEDPERCENT END AS QUALIFIEDPERCENT
              FROM (SELECT QDGROUP.GOODSID,
                           DECODE(QDGROUP.OUTKILNCOUNT, 0, 1, ROUND((QDGROUP.GOODCOUNT + QDGROUP.BADCOUNT) / QDGROUP.OUTKILNCOUNT, 4)) QUALIFIEDPERCENT
                      FROM (SELECT MSTGOODS.GOODSID,
                                   NVL(SUM(DECODE(PPCC.GOODSLEVELTYPEID, 0, 1, 0)), 0) OUTKILNCOUNT,
                                   NVL(SUM(DECODE(PPCC.GOODSLEVELTYPEID, 5, 1, 0)), 0) BADCOUNT,
                                   NVL(SUM(DECODE(PPCC.GOODSLEVELTYPEID, 0, 1, 5, -1, 6, -1, 7, -1, 0)), 0) GOODCOUNT
                              FROM (SELECT 0                   GOODSLEVELTYPEID,
                                           PD.BARCODE,
                                           PD.PRODUCTIONLINEID,
                                           PD.GOODSID,
                                           PD.KILNID,
                                           PD.KILNCARBATCHNO,
                                           1                   CC
                                      FROM TP_PM_PRODUCTIONDATA PD
                                     WHERE PD.ACCOUNTID = 1
                                       AND ((11 = 11 AND
                                           ((PD.PROCEDUREID = 11 AND PD.VALUEFLAG = '1') OR
                                           (PD.PROCEDUREID = 104 AND PD.ISREFIRE = '0' AND PD.CHECKFLAG = '1'))) OR
                                           (11 = 58 AND
                                           ((PD.PROCEDUREID = 58 AND PD.VALUEFLAG = '1') OR
                                           (PD.PROCEDUREID = 104 AND PD.ISREFIRE = '6' AND PD.CHECKFLAG = '1'))) OR
                                           (11 NOT IN (11, 58) AND PD.VALUEFLAG = '1' AND PD.PROCEDUREID = 11))
                                       AND PD.CREATETIME >= @BEGINDATE@
                                       AND PD.CREATETIME < @ENDDATE@
                                    UNION ALL
                                    SELECT DISTINCT PD.GOODSLEVELTYPEID,
                                                    PD.BARCODE,
                                                    PD.PRODUCTIONLINEID,
                                                    PD.GOODSID,
                                                    PD.KILNID,
                                                    PD.KILNCARBATCHNO,
                                                    1 CC
                                      FROM TP_PM_PRODUCTIONDATA PD
                                     WHERE PD.VALUEFLAG = '1'
                                       AND PD.MODELTYPE = -1
                                       AND PD.ACCOUNTID = 1
                                       AND LENGTH(PD.KILNCARBATCHNO) > 0
                                       AND PD.CREATETIME >= @BEGINDATE@
                                       AND PD.CREATETIME < @ENDDATE@
                                       AND ((11 <> 58 AND PD.ISREFIRE = '0') OR (11 = 58 AND PD.ISREFIRE = '6'))) PPCC
                             INNER JOIN TP_PC_PRODUCTIONLINE PCPL
                                ON PCPL.PRODUCTIONLINEID = PPCC.PRODUCTIONLINEID
                             INNER JOIN TP_MST_KILN MSTKILN
                                ON MSTKILN.KILNID = PPCC.KILNID
                             INNER JOIN TP_MST_GOODS MSTGOODS
                                ON MSTGOODS.GOODSID = PPCC.GOODSID
                             INNER JOIN TP_MST_GOODSTYPE MSTGOODSTYPE
                                ON MSTGOODSTYPE.GOODSTYPEID = MSTGOODS.GOODSTYPEID
                               AND MSTGOODSTYPE.ACCOUNTID = MSTGOODS.ACCOUNTID
                             INNER JOIN TP_MST_GOODSTYPE MSTGOODSTYPE2
                                ON MSTGOODSTYPE2.GOODSTYPECODE = SUBSTR(MSTGOODSTYPE.GOODSTYPECODE, 0, 6)
                               AND MSTGOODSTYPE.ACCOUNTID = MSTGOODSTYPE2.ACCOUNTID
                             GROUP BY MSTGOODS.GOODSID) QDGROUP) T ",
             new CDAParameter("BEGINDATE", planDate.AddDays(-7), DataType.DateTime),
             new CDAParameter("ENDDATE", planDate, DataType.DateTime)
         );
    }
}