IMEX.mes.wcf/DK.Service/ReportModuleLogic/ReportDetailedLogic.cs

/*******************************************************************************
 * Copyright(c) 2014 DongkeSoft All rights reserved. / Confidential
 * 类的信息：
 *      1.程序名称：ReportModuleLogic.cs
 *      2.功能描述：生产线报表分析
 * 编辑履历：
 *      作者            日期                版本            修改内容
 *      宋扬            2014/11/08          1.00            新建
 *******************************************************************************/




using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Curtain.DataAccess;
using Dongke.IBOSS.PRD.Basics.BaseResources;
using Dongke.IBOSS.PRD.Basics.DataAccess;
using Dongke.IBOSS.PRD.Service.DataModels;
using Dongke.IBOSS.PRD.WCF.DataModels;
using Oracle.ManagedDataAccess.Client;

namespace Dongke.IBOSS.PRD.Service.ReportModuleLogic
{
    /// <summary>
    /// 明细报表
    /// </summary>
    public class ReportDetailedLogic
    {
        /// <summary>
        /// 取得RPT080109（半成检缺陷对照表）的查询数据
        /// </summary>
        /// <param name="user">登录用户信息</param>
        /// <param name="se">查询条件</param>
        /// <returns>查询结果</returns>
        public static ServiceResultEntity GetRPT080109DData(SUserInfo user, RPT030106_SE se)
        {
            IDataAccess dataAccess  = null; 
            try
            {
                dataAccess = DataAccess.Create();

                List<CDAParameter> parameters = new List<CDAParameter>();

                DataTable data = dataAccess.ExecuteDatatable(ReportDetailedLogic.GetRPT080109DSQL(user, se, ref parameters), parameters.ToArray());

                ServiceResultEntity sre = new ServiceResultEntity();
                if (data == null || data.Rows.Count == 0)
                {
                    sre.Status = Constant.ServiceResultStatus.NoSearchResults;
                    return sre;
                }
                sre.Data = new DataSet();
                sre.Data.Tables.Add(data);
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dataAccess?.Close();
            }
        }

        /// <summary>
        /// 获取RPT080109画面（半成检缺陷对照表）的信息查询sql
        /// </summary>
        /// <returns>sql</returns>
        private static string GetRPT080109DSQL(SUserInfo user, RPT030106_SE se, ref List<CDAParameter> parameters)
        {
            // 质量登记改判时，缺陷的登记时间是当前时间，不是交接或前次登记时间。
            // 追加交接时间，改判标识。
            string selSql =
                "SELECT pmd.productionlinename\n" +
                "      ,pmd.procedurename\n" +
                "      ,pmd.DefectProcedureCode\n" +
                "      ,pmd.DefectProcedureName\n" +
                "      ,pmd.DefectUserCode\n" +
                "      ,pmd.defectusername\n" +
                "      ,jobs.jobsname defectjobsname\n" +
                "      ,dt.DefectTypeName\n" +
                "      ,mstd.Remarks DefectRemarks\n" +
                "      ,pd.Remarks barcodeRemarks\n" +
                "      ,pmd.defectcode\n" +
                "      ,d.alldefectcode\n" +
                "      ,pmd.defectname\n" +
                "      ,pmd.UserCode\n" +
                "      ,pmd.username\n" +
                "      ,gt.Goodstypename\n" +
                "      ,pmd.goodscode\n" +
                "      ,pmd.barcode\n" +
                "      ,pd.groutingdate\n" +
                "      ,pd.groutingnum\n" +
                "      ,pd.groutingMouldCode\n" +
                "      ,pd.GroutingLineCode\n" +
                "      ,pd.KilnCarCode\n" +
                "      ,pd.Procedureid\n" +
                "      ,gl.goodslevelname Goodsleveltype\n" +
                "      ,pmd.DefectPositionCode\n" +
                "      ,pmd.DefectPositionName\n" +
                "      ,pd.GroutingUserCode\n" +
                "      , CASE\n" +
                "          WHEN logo.logoid IS NULL THEN\n" +
                "           NULL\n" +
                "          ELSE\n" +
                "           logo.Logoname || '[' || logo.Logocode || ']'\n" +
                "        END AS LogoCodeName\n" +
                //"      ,pmd.CreateTime\n" +
                "      ,pd.CheckTime pdchecktime\n" +
                "      ,pmd.CheckTime\n" +
                "      ,mstdf.DefectFineCode\n" +
                "      ,pmd.DefectDeductionNum\n" +
                //"      ,to_char(pmd.DefectDeductionNum, '990.00') DefectDeductionNum\n" +
                "      ,hopd.createtime handovertime\n" +
                "      ,(SELECT listagg(to_char(pduc.UserCode), ',') within GROUP(ORDER BY pduc.ProductionDataID) AS WorkUserCodes\n" +
                "          FROM tp_pm_ProductionData pduc\n" +
                "         WHERE pduc.barcode = pmd.barcode\n" +
                "           AND pduc.valueflag = '1') AS WorkUserCodes\n" +
                "      ,(SELECT listagg(to_char(u.UserCode), ',') within GROUP(ORDER BY pdcu.ProductionDataID) AS CreateUserCodes\n" +
                "          FROM tp_pm_ProductionData pdcu\n" +
                "          Left join tp_mst_user u on u.userid = pdcu.createuserid\n" +
                "         WHERE pdcu.barcode = pmd.barcode\n" +
                "           AND pdcu.valueflag = '1') AS CreateUserCodes\n" +
                "      ,(SELECT listagg(to_char(s.StaffName), ',') within GROUP(ORDER BY s.Staffid) AS DefectStaffNames\n" +
                "          FROM TP_PM_DefectResponsible dr\n" +
                "          Left join TP_HR_Staff s on s.Staffid = dr.Staffid\n" +
                "         WHERE dr.ProductionDefectID = pmd.ProductionDefectID --dr pdcu.valueflag = '1'\n" +
                "           ) AS DefectStaffNames\n" +
                "      , CASE\n" +
                "          WHEN pd.isrefire <> '0' THEN\n" +
                "               (select listagg(to_char(d.defectname || '[' || d.defectpositionname || ']')\n" +
                "                             ,',') within group(order by d.productiondefectid) bsqx\n" +
                "                  from tp_pm_productiondata p\n" +
                "                 inner join tp_pm_defect d\n" +
                "                    on d.productiondataid = p.productiondataid\n" +
                "                 where p.valueflag = '1'\n" +
                "                   and d.valueflag = '1'\n" +
                "                   and p.barcode = pd.barcode\n" +
                "                   and p.modeltype = -1\n" +
                "                   and p.isrefire = '0')\n" +
                "          ELSE\n" +
                "           null\n" +
                "        END AS bsqx\n" +
                "      , CASE\n" +
                "          WHEN EXISTS (SELECT 1\n" +
                "                  FROM tp_pm_ProductionData pdud\n" +
                "                 WHERE pdud.barcode = pmd.barcode\n" +
                "                   AND pdud.modeltype = -1\n" +
                "                   AND pdud.procedureid = pd.procedureid\n" +
                "                   AND pdud.kilncarbatchno = pd.kilncarbatchno\n" +
                "                   AND pdud.valueflag = '0') THEN\n" +
                "           1\n" +
                "          ELSE\n" +
                "           0\n" +
                "        END AS undoflag\n" +
                "        ,semid.DEFECTNAME AS 半检缺陷\n" +
                //"        ,'[' || semid.DEFECTPOSITIONCODE || ']' ||semid.DEFECTPOSITIONNAME AS 半检位置\n" +
                "        ,semid.DEFECTPOSITIONCODE AS 位置编码\n" +
                "        ,semid.DEFECTPOSITIONNAME AS 半检位置\n" +
                "        ,semi.SEMIREWORKUSERCODE AS 半检工号\n" +
                "  FROM tp_pm_defect pmd\n" +
                "  LEFT JOIN TP_PC_Procedure p\n" +
                "    ON p.procedureid = pmd.procedureid\n" +
                "  LEFT JOIN TP_PC_Procedure pdd\n" +
                "    ON pdd.procedureid = pmd.defectprocedureid\n" +
                "  LEFT JOIN TP_MST_Goods g\n" +
                "    ON pmd.Goodsid = g.Goodsid\n" +
                "  LEFT JOIN TP_MST_GoodsType gt\n" +
                "    ON g.GoodsTypeID = gt.GoodsTypeID\n" +
                "  LEFT JOIN TP_MST_Defect mstd\n" +
                "    ON pmd.DefectID = mstd.DefectID\n" +
                "  LEFT JOIN TP_MST_Jobs jobs\n" +
                "    ON jobs.jobsid = pmd.Defectjobs\n" +
                "  LEFT JOIN TP_MST_DefectType dt\n" +
                "    ON mstd.DefectTypeID = dt.DefectTypeID\n" +
                " INNER JOIN tp_pm_productiondata pd\n" +
                "    ON pmd.ProductionDataID = pd.ProductionDataID\n" +
                "  LEFT JOIN tp_mst_goodslevel gl\n" +
                "    ON pd.goodslevelid = gl.goodslevelid\n" +
                //"-- 交接工序\n" +
                //" INNER JOIN tp_pm_productiondata hopd\n" +
                //"    ON hopd.procedureid = (select procedureid from tp_mst_rptsprocedure t where t.rptprocedureid = :HandOverProcedureID) \n" +
                //"   AND hopd.valueflag = '1'\n" +
                //"   AND hopd.barcode = pd.barcode\n" +
                //"   AND hopd.kilncarbatchno = pd.kilncarbatchno\n" +
                " INNER JOIN tp_pm_productiondata hopd\n" +
                "    ON hopd.kilncarbatchno = pd.kilncarbatchno\n " +
                "   AND hopd.barcode = pd.barcode\n" +
                "   AND hopd.valueflag = '1'\n" +
                "   AND hopd.procedureid = (select procedureid from tp_mst_rptsprocedure t where t.rptprocedureid = @HandOverProcedureID@) \n" +
                "  LEFT JOIN TP_MST_DefectFine mstdf\n" +
                "    ON pmd.DefectFine = mstdf.DefectFineID\n" +
                "  LEFT JOIN tp_mst_logo logo\n" +
                "    ON pd.logoid = logo.logoid\n" +
                "  LEFT JOIN (SELECT def.barcode, wmsys.wm_concat( to_char(def.defectcode)) alldefectcode\n" +
                "               FROM tp_pm_defect def\n" +
                "              WHERE def.valueflag = '1'\n" +
                //def.createtime
                "              group by def.barcode ) d\n" +
                "    ON d.barcode = pmd.barcode\n" +
                "  INNER JOIN TP_PM_SEMIREWORK semi ON semi.GROUTINGDAILYDETAILID = hopd.GROUTINGDAILYDETAILID AND semi.VALUEFLAG = '1' AND semi.BACKOUTFLAG = '0' \n" +
                "  INNER JOIN TP_PM_SEMIREWORKDEFECT semid ON semi.SEMIREWORKID = semid.SEMIREWORKDEFECTID AND semid.VALUEFLAG = '1' \n" +

                " WHERE pmd.valueflag = '1' \n" +
                "   AND pd.ModelType = -1\n" +
                "   AND pmd.AccountID = @AccountID@\n";

            parameters.Add(new CDAParameter("@AccountID@", user.AccountID));
            parameters.Add(new CDAParameter("@HandOverProcedureID@", se.HandOverProcedureID));

            //selSql = selSql +
            //        "  Where TP_PM_Defect.valueflag='1' and tp_pm_productiondata.ModelType=-1" +
            //        "  and TP_PM_Defect.AccountID=:AccountID ";
            if (!string.IsNullOrEmpty(se.GoodsTypeCode))
            {
                selSql = selSql + " AND instr(gt.GOODSTYPECode,@GoodsTypeCode@)=1 ";
                parameters.Add(new CDAParameter("@GoodsTypeCode@", se.GoodsTypeCode));
            }
            if (!string.IsNullOrEmpty(se.LineIDS))
            {
                selSql = selSql + " AND instr(','||@ProductionLineIDS@||',',','||pmd.ProductionLineId||',')>0 ";
                parameters.Add(new CDAParameter("@ProductionLineIDS@", se.LineIDS));
            }
            if (!string.IsNullOrEmpty(se.DefectProcedureIDS))
            {
                //selSql = selSql + " AND (instr(','||:DefectProcedureIDS||',',','||TP_PM_Defect.DefectProcedureID||',')>0 or TP_PM_Defect.DefectProcedureID  is null ) ";
                selSql = selSql + " AND (instr(','||@DefectProcedureIDS@||',',','||pmd.DefectProcedureID||',')>0 ) ";
                parameters.Add(new CDAParameter("@DefectProcedureIDS@", se.DefectProcedureIDS));
            }
            else
            {
                // modify by chenxy ：没有选择时，应全查询出来。
                //selSql = selSql + " AND TP_PM_Defect.DefectProcedureID  is null ";//因为有无责任工序
            }

            if (!string.IsNullOrEmpty(se.CheckProcedureIDS))
            {
                selSql = selSql + " AND instr(','||@ProcedureIDS@||',',','||pmd.ProcedureID||',')>0 ";
                parameters.Add(new CDAParameter("@ProcedureIDS@", se.CheckProcedureIDS));
            }

            if (!string.IsNullOrEmpty(se.KilnCode))
            {
                selSql = selSql + " AND instr(pd.KilnCode,@KilnCode@) >0 ";
                parameters.Add(new CDAParameter("@KilnCode@", se.KilnCode));
            }

            if (!string.IsNullOrEmpty(se.GoodsIDS))
            {
                selSql = selSql + " AND instr(','||@GoodsIDS@||',',','||pmd.Goodsid||',')>0 ";
                parameters.Add(new CDAParameter("@GoodsIDS@", se.GoodsIDS));
            }

            if (!string.IsNullOrEmpty(se.GoodsCode))
            {
                selSql = selSql + " AND instr(pmd.goodsCode,@GoodsCode@) >0 ";
                parameters.Add(new CDAParameter("@GoodsCode@", se.GoodsCode));
            }
            if (!string.IsNullOrEmpty(se.GoodsCodeS2D))
            {
                selSql = selSql + " AND pmd.goodsCode = @GoodsCodeS2D@ ";
                parameters.Add(new CDAParameter("@GoodsCodeS2D@", se.GoodsCodeS2D));
            }

            if (!string.IsNullOrEmpty(se.DefectUserCode))
            {
                selSql = selSql + " AND instr(pmd.DefectUserCode,@DefectUserCode@) >0 ";
                parameters.Add(new CDAParameter("@DefectUserCode@", se.DefectUserCode));
            }

            if (!string.IsNullOrEmpty(se.DefectTypeIDS))
            {
                selSql = selSql + " AND instr(','||@DefectTypeIDS@||',',','||dt.DefectTypeID||',')>0 ";
                parameters.Add(new CDAParameter("@DefectTypeIDS@", se.DefectTypeIDS));
            }

            if (!string.IsNullOrEmpty(se.DefectCode))
            {
                selSql = selSql + " AND instr(pmd.DefectCode,@DefectCode@) >0 ";
                parameters.Add(new CDAParameter("@DefectCode@", se.DefectCode));
            }
            if (!string.IsNullOrEmpty(se.DefectCodeS2D))
            {
                selSql = selSql + " AND pmd.DefectCode = @DefectCodeS2D@ ";
                parameters.Add(new CDAParameter("@DefectCodeS2D@", se.DefectCodeS2D));
            }

            if (!string.IsNullOrEmpty(se.DefectName))
            {
                selSql = selSql + " AND instr(pmd.DefectName,@DefectName@) >0 ";
                parameters.Add(new CDAParameter("@DefectName@", se.DefectName));
            }

            if (!string.IsNullOrEmpty(se.CheckUserCode))
            {
                selSql = selSql + " AND instr(pmd.UserCode,@UserCode@) >0";
                parameters.Add(new CDAParameter("@UserCode@", se.CheckUserCode));
            }
            if (!string.IsNullOrEmpty(se.CheckUserCodeS2D))
            {
                selSql = selSql + " AND pmd.UserCode = @CheckUserCodeS2D@ ";
                parameters.Add(new CDAParameter("@CheckUserCodeS2D@", se.CheckUserCodeS2D));
            }

            // 成检登记时间
            if (se.CreateTimeStart.HasValue)
            {
                selSql = selSql + "  AND pd.checktime>= @CreateTimeStart@  ";
                parameters.Add(new CDAParameter("@CreateTimeStart@", se.CreateTimeStart.Value));
            }
            if (se.CreateTimeEnd.HasValue)
            {
                selSql = selSql + "  AND pd.checktime<= @CreateTimeEnd@  ";
                parameters.Add(new CDAParameter("@CreateTimeEnd@", se.CreateTimeEnd.Value));
            }
            // 缺陷登记时间
            if (se.CheckTimeStart.HasValue)
            {
                selSql = selSql + "  AND pmd.checktime>= @CheckTimeStart@  ";
                parameters.Add(new CDAParameter("@CheckTimeStart@", se.CheckTimeStart.Value));
            }
            if (se.CheckTimeEnd.HasValue)
            {
                selSql = selSql + "  AND pmd.checktime<= @CheckTimeEnd@  ";
                parameters.Add(new CDAParameter("@CheckTimeEnd@", se.CheckTimeEnd.Value));
            }
            // 交接时间
            if (se.HandOverTimeStart.HasValue)
            {
                selSql = selSql + "  AND hopd.CreateTime>= @HandOverTimeStart@  ";
                parameters.Add(new CDAParameter("@HandOverTimeStart@", se.HandOverTimeStart.Value));
            }
            if (se.HandOverTimeEnd.HasValue)
            {
                selSql = selSql + "  AND hopd.CreateTime<= @HandOverTimeEnd@  ";
                parameters.Add(new CDAParameter("@HandOverTimeEnd@", se.HandOverTimeEnd.Value));
            }

            //selSql = selSql + " order by pmd.productionlinename,pmd.procedurename,pmd.defectprocedurename,pmd.defectusercode ";
            selSql = selSql + " order by pmd.productionlinename,p.DISPLAYNO,pdd.DISPLAYNO,dt.DISPLAYNO,mstd.DISPLAYNO,pmd.defectusercode,pmd.barcode ";
            return selSql;
        }

    }
}