HEGII.ss.mes.web/wwwroot/mes/rpt/rpt135/rpt.ashx

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

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

public class rpt : IHttpHandler, IReadOnlySessionState
{
    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "text/plain";
        using (IDataAccess conn = DataAccess.Create())
        {
            //主表
            if (context.Request["m"].ToString() == "master")
            {
                //获取查询条件
                DateTime dateBegin = Convert.ToDateTime(context.Request["datebeginMaster"]);
                DateTime dateEnd = Convert.ToDateTime(context.Request["dateendMaster"]).AddDays(1);
                List<CDAParameter> sqlPara = new List<CDAParameter>();
                sqlPara.Add(new CDAParameter("accountid", context.Session["accountId"] is object ? Convert.ToInt32(context.Session["accountId"]) : 1));
                sqlPara.Add(new CDAParameter("datebegin", dateBegin, DataType.DateTime));
                sqlPara.Add(new CDAParameter("dateend", dateEnd, DataType.DateTime));

                //主表
                if (context.Request["n"].ToString() == "0")
                {
                    string logoId = context.Request["LOGONAME"] is object ? context.Request["LOGONAME"].ToString() : "";
                    string logoIds = context.Request["LOGONAME[]"] is object ? context.Request["LOGONAME[]"].ToString() : "";
                    logoId = logoId==""?logoIds==""?"":" AND gdd.logoid in ("+logoIds+")":" AND gdd.logoid in ("+logoId+")";

                    //读取报表数据
                    string sqlStr = @"
                     SELECT
						g.goodscode AS 产品编码,
						tt.d_num AS 交坯数,
						tt.x_num AS 干补数,
						tt.b_num AS 半检不合格数,
						tt.f_num AS 复检不合格数,
						tt.s_num AS 损坯数,
						CASE
								WHEN ( tt.b_num + tt.f_num + tt.s_num - tt.x_num ) < 0 THEN
								'100%' ELSE to_char(decode(tt.d_num,0,to_number( 0 ),round( ( tt.b_num + tt.f_num + tt.s_num - tt.x_num ) / tt.d_num, 4 ) * 100.00 ),'fm99990.09' ) || '%' 
						END 报废率,
						CASE
							WHEN 1- ( tt.b_num + tt.f_num + tt.s_num - tt.x_num ) > 0 THEN
							'100%' ELSE to_char(decode(tt.d_num,0,to_number( 0 ),round( 1- ( tt.b_num + tt.f_num + tt.s_num - tt.x_num ) / tt.d_num, 4 ) * 100.00 ),'fm99990.09' ) || '%' 
						END 合格率 
					FROM
						(
						SELECT
							t.goodsid,
							nvl( sum( decode( t.numtype, 'A', t.id_num, 0 ) ), 0 ) d_num,-- 交坯
							nvl( sum( decode( t.numtype, 'B', t.id_num, 0 ) ), 0 ) x_num,-- 干补数
							nvl( sum( decode( t.numtype, '2', t.id_num, 0 ) ), 0 ) b_num,-- 半检不合格数
							nvl( sum( decode( t.numtype, '3', t.id_num, 0 ) ), 0 ) f_num,-- 复检不合格数
							nvl( sum( decode( t.numtype, '0', t.id_num, 0 ) ), 0 ) s_num -- 损坯数
						FROM
							(
							SELECT
								gdd.goodsid,
								'A' numtype,
								count( gdd.groutingdailydetailid ) id_num 
							FROM
								tp_pm_groutingdailydetail gdd 
								LEFT JOIN TP_PC_GROUTINGLINE gl on gl.GROUTINGLINEID=gdd.GROUTINGLINEID
							WHERE
								gdd.groutingflag = '1' 
								AND gdd.valueflag = '1' 
								AND gdd.accountid = 1
								AND gdd.deliverflag = '1' 
								AND gdd.delivertime >=  @datebegin@
								AND gdd.delivertime <  @dateend@
								AND (@BUILDINGNO@ IS NULL OR instr(gl.BUILDINGNO , @BUILDINGNO@) > 0)
								AND (@HIGHPRESSUREFLAG@ IS NULL OR instr(gl.HIGHPRESSUREFLAG , @HIGHPRESSUREFLAG@) > 0)"+logoId;

                    sqlStr+=  @"  GROUP BY
								gdd.goodsid 
							UNION ALL-- 干补数
							SELECT
								p.goodsid,
								'B' numtype,
								count( p.productiondataid ) id_num 
							FROM
								TP_PM_PRODUCTIONDATA p 
									LEFT JOIN TP_PC_GROUTINGLINE gl on gl.GROUTINGLINEID=p.GROUTINGLINEID
                                left join tp_pm_groutingdailydetail gdd on gdd.groutingdailydetailid = p.groutingdailydetailid
							WHERE
								p.VALUEFLAG = 1 
								AND p.accountid = 1
								AND p.PROCEDUREID = 18 
								AND p.createtime >= @datebegin@
								AND p.createtime < @dateend@
								AND (@BUILDINGNO@ IS NULL OR instr(gl.BUILDINGNO , @BUILDINGNO@) > 0)
								AND (@HIGHPRESSUREFLAG@ IS NULL OR instr(gl.HIGHPRESSUREFLAG , @HIGHPRESSUREFLAG@) > 0)"+logoId; 
							sqlStr+=  @" GROUP BY
								p.goodsid 
							UNION ALL-- 半检不合格数  复检不合格数  损坯数
							SELECT
								sp.goodsid,
								sp.SCRAPTYPE numtype,
								count( sp.scrapproductid ) id_num 
							FROM
								tp_pm_scrapproduct sp 
									LEFT JOIN TP_PC_GROUTINGLINE gl on gl.GROUTINGLINEID=sp.GROUTINGLINEID
                                left join tp_pm_groutingdailydetail gdd on gdd.groutingdailydetailid = sp.groutingdailydetailid
							WHERE
								sp.valueflag = '1' 
								AND sp.accountid = 1
								AND sp.auditstatus = 1 
								AND sp.KILNID IS NULL 
								AND sp.SCRAPTYPE <> '1' 
								AND sp.AUDITDATE >= @datebegin@
								AND sp.AUDITDATE < @dateend@
								AND (@BUILDINGNO@ IS NULL OR instr(gl.BUILDINGNO , @BUILDINGNO@) > 0)
								AND (@HIGHPRESSUREFLAG@ IS NULL OR instr(gl.HIGHPRESSUREFLAG , @HIGHPRESSUREFLAG@) > 0)"+logoId; 
							sqlStr+=  @" GROUP BY
								sp.goodsid,
								sp.SCRAPTYPE 
							) t 
						GROUP BY
							t.goodsid 
						) tt
						INNER JOIN tp_mst_goods g ON tt.goodsid = g.goodsid
						LEFT JOIN TP_MST_GOODSTYPE GT ON g.GOODSTYPEID=GT.GOODSTYPEID
						WHERE (@goodstypecode@ IS NULL OR instr(gt.goodstypecode,@goodstypecode@) = 1) ";

                    if (!string.IsNullOrWhiteSpace(context.Request["goodscode"] + ""))
                    {
                        sqlStr += " AND instr(g.goodscode,  @goodscode@) > 0 \n";
                        sqlPara.Add(new CDAParameter("goodscode", context.Request["goodscode"]));
                    }
                    sqlStr += " order by g.goodscode";

                    sqlPara.Add(new CDAParameter("BUILDINGNO", context.Request["workshopcodeMaster"].ToString()));
                    sqlPara.Add(new CDAParameter("HIGHPRESSUREFLAG", context.Request["HIGHPRESSUREFLAG"]));
                    sqlPara.Add(new CDAParameter("goodstypecode", context.Request["goodstypecode"].ToString()));
                    //直接获取不分页数据
                    JsonResult jsonresult = Easyui.ExecuteJsonResult(conn, sqlStr, sqlPara);
                    context.Response.Write(jsonresult.ToJson());
                }

                //子表
                if (context.Request["n"].ToString() == "1")
                {
                    string logoId = context.Request["LOGONAME"] is object ? context.Request["LOGONAME"].ToString() : "";
                    string logoIds = context.Request["LOGONAME[]"] is object ? context.Request["LOGONAME[]"].ToString() : "";
                    logoId = logoId==""?logoIds==""?"":" AND gdd.logoid in ("+logoIds+")":" AND gdd.logoid in ("+logoId+")";

                    string sqlStr = "WITH ppp_details AS (  " +
                        "select  TO_CHAR(g.goodscode) 产品编码\n" +
                   "          ,ppp.b_out_num 本烧出窑数\n" +
                   "          ,(ppp.b_out_num - ppp.b_bad_num) 本烧合格数\n" +
                   "          ,(ppp.c_out_num - ppp.c_bad_num) 重烧合格数\n" +
                   "          ,decode(ppp.b_out_num,0,to_number( 0 ),round( ( ppp.b_out_num - ppp.b_bad_num + ppp.c_out_num - ppp.c_bad_num ) / ppp.b_out_num, 4 ) )  *100 ||'%' 综合合格率\n" +
                   "from (\n" +
                   "      SELECT pp.goodsid ,\n" +
                   "      nvl(sum(decode(pp.OUTflag, 1, b_out_num, 0)), 0) b_out_num,\n" +
                   "      nvl(sum(decode(pp.OUTflag, 0, b_bad_num, 0)), 0) b_bad_num,\n" +
                   "      nvl(sum(decode(pp.OUTflag, 1, c_out_num, 0)), 0) c_out_num,\n" +
                   "      nvl(sum(decode(pp.OUTflag, 0, c_bad_num, 0)), 0) c_bad_num\n" +
                   "      FROM (\n" +
                   "      select p.goodsid\n" +
                   "         ,1 AS OUTflag\n" +
                   "         ,nvl(sum(case when   ((p.procedureid = 11 AND p.valueflag = '1') OR (p.procedureid =104 AND p.isrefire = '0' AND p.checkflag = '1')) then 1 else 0 end), 0) b_out_num -- 本烧出窑数\n" +
                   "         ,0 b_bad_num -- 本烧不合格数\n" +
                   "         ,nvl(sum(case when   ((p.procedureid = 58 AND p.valueflag = '1') OR (p.procedureid =104 AND p.isrefire = '6' AND p.checkflag = '1')) then 1 else 0 end), 0) c_out_num -- 重烧出窑数\n" +
                   "         ,0 c_bad_num -- 重烧不合格数\n" +
                   "      from tp_pm_productiondata p\n" +
                   "       INNER JOIN tp_mst_goods g ON p.goodsid = g.goodsid\n" +
                    "       LEFT JOIN TP_MST_GOODSTYPE GT ON g.GOODSTYPEID=GT.GOODSTYPEID\n" +
                    "       LEFT JOIN TP_PC_GROUTINGLINE gl on gl.GROUTINGLINEID=p.GROUTINGLINEID\n" +
                    "       LEFT JOIN tp_pm_groutingdailydetail gdd on gdd.groutingdailydetailid = p.groutingdailydetailid\n" +
                   "      where p.accountid = @accountid@\n" +
                   "         and p.procedureid in (11, 58, 104)\n" +
                   "         and p.createtime >= @datebegin@\n" +
                   "         and p.createtime <  @dateend@\n" +
                   "		AND (@BUILDINGNO@ IS NULL OR instr(gl.BUILDINGNO , @BUILDINGNO@) > 0) " + logoId +
                   "		AND (@HIGHPRESSUREFLAG@ IS NULL OR instr(gl.HIGHPRESSUREFLAG , @HIGHPRESSUREFLAG@) > 0)" +
                   "        AND (@groutinglineMaster@ IS NULL OR instr(gl.GROUTINGLINEID , @groutinglineMaster@) > 0)" +
                   "		AND (@goodstypecode@ IS NULL OR instr(gt.goodstypecode,@goodstypecode@) = 1)";
                    // 产品编码条件修正 fenglinyong modify 2022-05-10
                    if (!string.IsNullOrWhiteSpace(context.Request["goodscode"] + ""))
                    {
                        sqlStr += " and instr(p.goodscode,@GOODSCODE@) > 0 \n";
                        sqlPara.Add(new CDAParameter("GOODSCODE", context.Request["goodscode"]));
                    }
                    sqlStr += "  GROUP BY 	p.goodsid \n" +  
                                  "   UNION ALL\n" +
                                  "   select 	p.goodsid\n" +
                                    "     ,0 AS OUTflag\n" +
                                    "     ,0 b_out_num\n" +
                                    "     ,nvl(sum(case when p.goodsleveltypeid in(6,7) and p.isrefire='0' then 1 else 0 end), 0) b_bad_num -- 本烧不合格数\n" +
                                    "     ,0 c_out_num\n" +
                                    "     ,nvl(sum(case when p.goodsleveltypeid in(6,7)and p.isrefire='6' then 1 else 0 end), 0) c_bad_num -- 重烧不合格数\n" +
                                    "   from (\n" +
			                            "     select distinct 				p.goodsleveltypeid\n" +
                                          "                      ,p.barcode\n" +
                                          "                      ,p.goodsid\n" +
										" 					                                 ,p.ISREFIRE\n" +
                                           "        from tp_pm_productiondata p\n" +
                                          "        inner join tp_pm_groutingdailydetail gdd on gdd.groutingdailydetailid = p.groutingdailydetailid\n" +
								          "                       LEFT JOIN TP_PC_GROUTINGLINE gl on gl.GROUTINGLINEID=p.GROUTINGLINEID\n" +
                                         "         INNER JOIN TP_PM_DEFECT dfc on dfc.productiondataid=p.productiondataid and dfc.barcode = p.barcode\n" +
								         "                        INNER JOIN tp_mst_goods g ON p.goodsid = g.goodsid\n" +
								        "                         LEFT JOIN TP_MST_GOODSTYPE GT ON g.GOODSTYPEID=GT.GOODSTYPEID\n" +
		                                "  where p.accountid = @accountid@ \n" +
                                        "  and length(p.kilncarbatchno) > 0\n" +
                                        "  AND P.VALUEFLAG = 1\n" +
                                        "  and p.CHECKBATCHNO = 1\n" +
                                        "  and p.modeltype IN (-1, -4, -5)\n" +
                                        "  and p.createtime >= @datebegin@\n" +
                                        "  and p.createtime <  @dateend@\n" +
				                               "  AND (@BUILDINGNO@ IS NULL OR instr(gl.BUILDINGNO , @BUILDINGNO@) > 0)   "+logoId+
                                                " AND (@HIGHPRESSUREFLAG@ IS NULL OR instr(gl.HIGHPRESSUREFLAG , @HIGHPRESSUREFLAG@) > 0)	"+
                                                "AND (@groutinglineMaster@ IS NULL OR instr(gl.GROUTINGLINEID , @groutinglineMaster@) > 0)"+
                                                "AND (@goodstypecode@ IS NULL OR instr(gt.goodstypecode,@goodstypecode@) = 1) ";
                    // 产品编码条件修正 fenglinyong modify 2022-05-10
                    if (!string.IsNullOrWhiteSpace(context.Request["goodscode"] + ""))
                    {
                        sqlStr += " and instr(p.goodscode,@GOODSCODE@) > 0 \n";
                        sqlPara.Add(new CDAParameter("GOODSCODE", context.Request["goodscode"]));
                    }

                    sqlStr += " )P GROUP BY 	p.goodsid ) pp group by pp.goodsid) ppp\n" +
                    "     inner join tp_mst_goods g\n" +
                    "        on g.goodsid = ppp.goodsid\n" +
                    "     order by g.goodscode) ";
                    sqlStr += "SELECT * FROM ppp_details\n" +
                "UNION ALL\n" +
                "SELECT '合计' AS 产品编码," +
                "SUM(本烧出窑数) AS 本烧出窑数," +
                "SUM(本烧合格数) AS 本烧合格数," +
                "SUM(重烧合格数) AS 重烧合格数," +
                "ROUND((SUM(本烧合格数) + SUM(重烧合格数)) / NULLIF(SUM(本烧出窑数), 0), 4) * 100 || '%' AS 综合合格率\n " +
                "FROM ppp_details ";


                    sqlPara.Add(new CDAParameter("BUILDINGNO", context.Request["workshopcodeMaster"].ToString()));
                    sqlPara.Add(new CDAParameter("HIGHPRESSUREFLAG", context.Request["HIGHPRESSUREFLAG"]));
                    sqlPara.Add(new CDAParameter("groutinglineMaster", context.Request["groutinglineMaster"]));
                    sqlPara.Add(new CDAParameter("goodstypecode", context.Request["goodstypecode"].ToString()));

                    //直接获取不分页数据
                    //JsonResult jsonresult = Easyui.ExecuteJsonResult(conn, sqlStr, sqlPara);

                    DataTable dt = conn.ExecuteDatatable(sqlStr, sqlPara.ToArray());
                    //context.Response.Write(jsonresult.ToJson());
                    context.Response.Write(   new JsonResult(dt) { total = dt.Rows.Count }.ToJson());
                }
            }
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}