HEGII.ft.mes.dashboard/wwwroot/main/Report/api/Get_Report_Defect_Detail.ashx


<%@ WebHandler Language="C#" Class="Get_Report_Defect_Detail" %>

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.SessionState;
using System.Configuration;
using System.Data;
using Newtonsoft.Json;
using Curtain.DataAccess;
using Curtain.Log;
using DK.XuWei.WebMes;

public class Get_Report_Defect_Detail : IHttpHandler,IRequiresSessionState
{
    public void ProcessRequest(HttpContext context)
    {
        int pageNumber = context.Request["pageNumber"] is object ? Convert.ToInt32(context.Request["pageNumber"]) : 0;
        int pageSize = context.Request["pageSize"] is object ? Convert.ToInt32(context.Request["pageSize"]) : 10;
        List<CDAParameter> para = new List<CDAParameter>();

        if (HttpContext.Current.Session["userCode"] is object)
        {
            //统计语句
            string sqlStr = @"
				SELECT
					pmd.productionlinename AS 生产线,
					pmd.procedurename AS 检验工序,
					pmd.DefectProcedureCode AS 责任工序编码,
					pmd.DefectProcedureName AS 责任工序,
					pmd.DefectUserCode AS 责任工号,
					pmd.defectusername AS 责任工号名称,
					jobs.jobsname AS 责任工种,
					dt.DefectTypeName AS 缺陷类别,
					mstd.Remarks AS 缺陷备注,
					pd.Remarks AS 产品备注,
					pmd.defectcode AS 缺陷编码,
					d.alldefectcode AS 缺陷编码全,
					pmd.defectname AS 缺陷名称,
					pmd.UserCode AS 检验工号,
					pmd.username AS 工号名称,
					gt.Goodstypename AS 产品类别,
					pmd.goodscode AS 产品编码,
					pmd.barcode AS 产品条码,
					pd.groutingdate AS 注浆日期,
					pd.groutingnum AS 注浆次数,
					pd.groutingMouldCode AS 模具编码,
					pd.GroutingLineCode AS 成型线编码,
					pd.KilnCarCode AS 窑车编号,
					pd.Procedureid AS 工序ID,
					gl.goodslevelname AS 产品分级,
					pmd.DefectPositionCode AS 缺陷位置编码,
					pmd.DefectPositionName AS 缺陷位置名称,
					pd.GroutingUserCode AS 成型工号,
				  CASE WHEN logo.logoid IS NULL THEN NULL ELSE logo.Logoname || '[' || logo.Logocode || ']' END AS 商标,
					pd.CheckTime AS 成检登记时间,
					pmd.CheckTime AS 缺陷登记时间,
					mstdf.DefectFineCode AS 缺陷扣罚,
					pmd.DefectDeductionNum AS 缺陷扣除数,
					hopd.createtime AS 交接时间,
					(
					SELECT
						listagg ( to_char( pduc.UserCode ), ',' ) within GROUP ( ORDER BY pduc.ProductionDataID ) AS WorkUserCodes 
					FROM
						tp_pm_ProductionData pduc 
					WHERE
						pduc.barcode = pmd.barcode 
						AND pduc.valueflag = '1' 
					) AS 生产工号,
					(
					SELECT
						listagg ( to_char( u.UserCode ), ',' ) within GROUP ( ORDER BY pdcu.ProductionDataID ) AS CreateUserCodes 
					FROM
						tp_pm_ProductionData pdcu
						LEFT JOIN tp_mst_user u ON u.userid = pdcu.createuserid 
					WHERE
						pdcu.barcode = pmd.barcode 
						AND pdcu.valueflag = '1' 
					) AS 操作工号,
					(
					SELECT
						listagg ( to_char( s.StaffName ), ',' ) within GROUP ( ORDER BY s.Staffid ) AS DefectStaffNames 
					FROM
						TP_PM_DefectResponsible dr
						LEFT JOIN TP_HR_Staff s ON s.Staffid = dr.Staffid 
					WHERE
						dr.ProductionDefectID = pmd.ProductionDefectID --dr pdcu.valueflag = '1'
		
					) AS 责任员工,
				CASE
						WHEN pd.isrefire <> '0' THEN
						(
						SELECT
							listagg ( to_char( d.defectname || '[' || d.defectpositionname || ']' ), ',' ) within GROUP ( ORDER BY d.productiondefectid ) bsqx 
						FROM
							tp_pm_productiondata p
							INNER JOIN tp_pm_defect d ON d.productiondataid = p.productiondataid 
						WHERE
							p.valueflag = '1' 
							AND d.valueflag = '1' 
							AND p.barcode = pd.barcode 
							AND p.modeltype = - 1 
							AND p.isrefire = '0' 
						) ELSE NULL 
					END AS 本烧缺陷,
				CASE
						WHEN EXISTS (
						SELECT
							1 
						FROM
							tp_pm_ProductionData pdud 
						WHERE
							pdud.barcode = pmd.barcode 
							AND pdud.modeltype = - 1 
							AND pdud.procedureid = pd.procedureid 
							AND pdud.kilncarbatchno = pd.kilncarbatchno 
							AND pdud.valueflag = '0' 
							) THEN
							1 ELSE 0 
						END AS 改判标识,
						semid.DEFECTNAME AS 半检缺陷,
						semid.DEFECTPOSITIONNAME AS 半检位置,
						semi.SEMIREWORKUSERCODE AS 半检工号
					FROM
						tp_pm_defect pmd
						LEFT JOIN TP_PC_Procedure p ON p.procedureid = pmd.procedureid
						LEFT JOIN TP_PC_Procedure pdd ON pdd.procedureid = pmd.defectprocedureid
						LEFT JOIN TP_MST_Goods g ON pmd.Goodsid = g.Goodsid
						LEFT JOIN TP_MST_GoodsType gt ON g.GoodsTypeID = gt.GoodsTypeID
						LEFT JOIN TP_MST_Defect mstd ON pmd.DefectID = mstd.DefectID
						LEFT JOIN TP_MST_Jobs jobs ON jobs.jobsid = pmd.Defectjobs
						LEFT JOIN TP_MST_DefectType dt ON mstd.DefectTypeID = dt.DefectTypeID
						INNER JOIN tp_pm_productiondata pd ON pmd.ProductionDataID = pd.ProductionDataID
						LEFT JOIN tp_mst_goodslevel gl ON pd.goodslevelid = gl.goodslevelid
						INNER JOIN tp_pm_productiondata hopd ON hopd.kilncarbatchno = pd.kilncarbatchno 
						AND hopd.barcode = pd.barcode 
						AND hopd.valueflag = '1' 
						AND hopd.procedureid = ( SELECT procedureid FROM tp_mst_rptsprocedure t WHERE t.rptprocedureid = @RPTPROCEDUREID@ )
						LEFT JOIN TP_MST_DefectFine mstdf ON pmd.DefectFine = mstdf.DefectFineID
						LEFT JOIN tp_mst_logo logo ON pd.logoid = logo.logoid
						LEFT JOIN (
						SELECT
							def.barcode,
							wmsys.wm_concat ( to_char( def.defectcode ) ) alldefectcode 
						FROM
							tp_pm_defect def 
						WHERE
							def.valueflag = '1' 
						GROUP BY
							def.barcode 
						) d ON d.barcode = pmd.barcode 
						LEFT JOIN TP_PM_SEMIREWORK semi ON semi.GROUTINGDAILYDETAILID = hopd.GROUTINGDAILYDETAILID AND semi.VALUEFLAG = '1' AND semi.BACKOUTFLAG = '0'
						LEFT JOIN TP_PM_SEMIREWORKDEFECT semid ON semi.SEMIREWORKID = semid.SEMIREWORKDEFECTID AND semid.VALUEFLAG = '1'
						--INNER JOIN TP_PM_SEMIREWORK semi ON semi.GROUTINGDAILYDETAILID = hopd.GROUTINGDAILYDETAILID AND semi.VALUEFLAG = '1' AND semi.BACKOUTFLAG = '0'
						--INNER JOIN TP_PM_SEMIREWORKDEFECT semid ON semi.SEMIREWORKID = semid.SEMIREWORKDEFECTID AND semid.VALUEFLAG = '1'
					WHERE
						pmd.valueflag = '1' 
						AND pd.ModelType = - 1 
						AND pmd.AccountID = 1 
            ";

            //登记时间测试 2017-12-09 至 2017-12-10
            //sqlStr += @"
            //	AND pd.CheckTime >= Date'2017-12-10'
            //	AND pd.CheckTime <= Date'2017-12-11' 
            //";

            //拼合搜索参数

            //此参数必选
            if (!string.IsNullOrEmpty(context.Request["交接工序"]))
            {
                para.Add(new CDAParameter("RPTPROCEDUREID", Convert.ToInt32(context.Request["交接工序"].ToString()), DataType.Int32, ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(context.Request["登记时间开始"]))
            {
                sqlStr += " AND pd.CheckTime >= @登记开始时间@ ";
                para.Add(new CDAParameter("登记开始时间", Convert.ToDateTime(context.Request["登记时间开始"].ToString()), DataType.Date, ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(context.Request["登记时间结束"]))
            {
                sqlStr += " AND pd.CheckTime <= @登记时间结束@ ";
                para.Add(new CDAParameter("登记时间结束", Convert.ToDateTime(context.Request["登记时间结束"].ToString()), DataType.Date, ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(context.Request["窑炉编码"]))
            {
                sqlStr += " AND instr(pd.KilnCode,@窑炉编码@) >0 ";
                para.Add(new CDAParameter("窑炉编码", context.Request["窑炉编码"].ToString(), DataType.NVarChar, ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(context.Request["产品类别"]))
            {
                sqlStr += " AND instr(gt.GOODSTYPECode,@产品类别@)=1 ";
                para.Add(new CDAParameter("产品类别", context.Request["产品类别"].ToString(), DataType.NVarChar, ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(context.Request["缺陷位置"]))
            {
                sqlStr += " AND instr(pmd.DefectPositionName,@缺陷位置@)>0 ";
                para.Add(new CDAParameter("缺陷位置", context.Request["缺陷位置"].ToString(), DataType.NVarChar, ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(context.Request["缺陷名称"]))
            {
                sqlStr += " AND instr(pmd.DefectName,@缺陷名称@)>0 ";
                para.Add(new CDAParameter("缺陷名称", context.Request["缺陷名称"].ToString(), DataType.NVarChar, ParameterDirection.Input));
            }

            //加排序
            sqlStr += @"
				ORDER BY
					pmd.productionlinename,
					p.DISPLAYNO,
					pdd.DISPLAYNO,
					dt.DISPLAYNO,
					mstd.DISPLAYNO,
					pmd.defectusercode,
					pmd.barcode
			";

            //输出结果
            //context.Response.Write(sqlStr.Replace("\n","<br>"));
            //context.Response.Write("<br>");
            //for(int i=0;i<para.Count;i++)
            //{
            //    context.Response.Write(para[i].ParameterName + "=" + para[i].Value + "<br>");
            //}


            using (IDataAccess conn = DataAccess.Create())
            {
                DataTable dt = conn.SelectPages(pageNumber, pageSize, sqlStr, para.ToArray());
				string jsonStr = new JsonResult(dt).ToJson();
				context.Response.AddHeader("Content-Length",jsonStr.Length.ToString());
				context.Response.Write(jsonStr);
			}


        }
        else
        {
            context.Response.Write(new JsonResult(-1).ToJson());
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}