<%@ 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 para = new List(); 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","
")); //context.Response.Write("
"); //for(int i=0;i"); //} 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; } } }