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 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)
                                AND (@LOGOID@ IS NULL OR instr(gdd.logoid , @LOGOID@) > 0)

							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)
                                AND (@LOGOID@ IS NULL OR instr(gdd.logoid , @LOGOID@) > 0)
							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)
                                AND (@LOGOID@ IS NULL OR instr(gdd.logoid , @LOGOID@) > 0)
							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("LOGOID", context.Request["LOGONAME"].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 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) " +
                   "		AND (@LOGOID@ IS NULL OR instr(gdd.LOGOID , @LOGOID@) > 0) " +
                   "		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  
                                    UNION ALL
                                    select 	p.goodsid
                                        ,0 AS OUTflag
                                        ,0 b_out_num
                                        ,nvl(sum(case when p.goodsleveltypeid in(6,7) and p.isrefire='0' then 1 else 0 end), 0) b_bad_num -- 本烧不合格数
                                        ,0 c_out_num
                                        ,nvl(sum(case when p.goodsleveltypeid in(6,7)and p.isrefire='6' then 1 else 0 end), 0) c_bad_num -- 重烧不合格数
                                      from (
			                                select distinct 				p.goodsleveltypeid
                                                               ,p.barcode
                                                               ,p.goodsid
															                                 ,p.ISREFIRE
                                                  from tp_pm_productiondata p
                                                 inner join tp_pm_groutingdailydetail gdd on gdd.groutingdailydetailid = p.groutingdailydetailid
								                                LEFT JOIN TP_PC_GROUTINGLINE gl on gl.GROUTINGLINEID=p.GROUTINGLINEID
                                                 INNER JOIN TP_PM_DEFECT dfc on dfc.productiondataid=p.productiondataid
								                                INNER JOIN tp_mst_goods g ON p.goodsid = g.goodsid
								                                LEFT JOIN TP_MST_GOODSTYPE GT ON g.GOODSTYPEID=GT.GOODSTYPEID
		                                 where p.accountid = @accountid@
                                         AND P.VALUEFLAG = 1
                                         and p.modeltype IN (-1, -4, -5)
                                         and p.CHECKBATCHNO = 1
                                         and length(p.kilncarbatchno) > 0
                                         and p.createtime >= @datebegin@
                                         and p.createtime <  @dateend@
				                                AND (@BUILDINGNO@ IS NULL OR instr(gl.BUILDINGNO , @BUILDINGNO@) > 0)   
				                                AND (@LOGOID@ IS NULL OR instr(gdd.LOGOID , @LOGOID@) > 0)    
				                                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("LOGOID", context.Request["LOGONAME"].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);

                    context.Response.Write(jsonresult.ToJson());
                }
            }
        }
    }

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