HEGII.ss.mes.web/wwwroot/mes/rpt/rpt151/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())
        {
            string GoodsTypeCode = context.Request["goodstypecode"];//产品类别
            //string GoodsCode = context.Request["goodsidMaster"];//产品编码
            string LineCode = context.Request["linecode"];//成型线号
            string UserCode = context.Request["usercode"];//成型工号
                                                          //存坯汇总表(DataGridDetail1)中的值
            string GoodsID = context.Request["goodsid"];
            string LineID = context.Request["groutinglineid"] == "undefined" ? null : context.Request["groutinglineid"];//成型线号
            string UserID = context.Request["userid"] == "undefined" ? null : context.Request["userid"];
            //商标id
            string logoId = context.Request["LOGONAME"] is object ? context.Request["LOGONAME"].ToString() : "";
            DateTime? dateBegin;
            if (context.Request["chkStar"] != null)
            {
                dateBegin = Convert.ToDateTime(context.Request["datebeginMaster"]);
            }
            else
            {
                dateBegin = null;
            }
            DateTime? dateEnd;
            if (context.Request["chkStar"] != null)
            {
                dateEnd = Convert.ToDateTime(context.Request["dateendMaster"]);
            }
            else
            {
                dateEnd = null;
            }

            string GoodsCode = context.Request["goodsidMaster[]"];
            if (context.Request["goodsidMaster"] is object) GoodsCode = context.Request["goodsidMaster"].ToString();

            if (!String.IsNullOrWhiteSpace(GoodsCode)) {
                List<string> list= new List<string>(GoodsCode.Split(','));
                List<string> newlist = new List<string>();
                foreach (string item in list) {
                    if (!String.IsNullOrWhiteSpace(item)) {
                        newlist.Add("'"+item+"'");
                    }
                }
                if (newlist != null && newlist.Count > 1) {
                    GoodsCode = string.Join(",", newlist);
                }
                else
                {
                    GoodsCode = "'" + GoodsCode + "'";
                }
            }

            //获取查询条件
            List<CDAParameter> sqlPara = new List<CDAParameter>();
            sqlPara.Add(new CDAParameter("accountid", int.Parse(context.Session["accountId"].ToString()), DataType.Int32));
            sqlPara.Add(new CDAParameter("DateBegin", dateBegin, DataType.DateTime));
            sqlPara.Add(new CDAParameter("DateEnd", dateEnd, DataType.DateTime));
            sqlPara.Add(new CDAParameter("GoodsTypeCode", GoodsTypeCode, DataType.VarChar));
            sqlPara.Add(new CDAParameter("GoodsCode", GoodsCode, DataType.VarChar));
            sqlPara.Add(new CDAParameter("LineCode", LineCode, DataType.VarChar));
            sqlPara.Add(new CDAParameter("UserCode", UserCode, DataType.VarChar));
            sqlPara.Add(new CDAParameter("LOGOID", logoId, DataType.VarChar));
            //主表
            if (context.Request["m"].ToString() == "master")
            {
                //读取报表数据
                StringBuilder sqlStr = new StringBuilder();
                sqlStr.Append(@"
                    SELECT 
                      decode(grouping_id(t.goodstypecode2, t.goodsid),
                              3,
                              '总计',
                              1,
                              '合计[' || t.goodstypename2 || ']',
                              t.goodstypename2) AS 产品大类
                      ,t.goodstypename AS 产品类别
                      ,t.goodsid AS 产品ID
                      ,t.goodscode AS 产品编码
                      ,SUM(t.rdcount) AS 存坯数
                      ,t.gldcount AS 模具数
                      ,t.rddays AS 存坯天数
                      ,t.reserveddays AS 应存天数
                      ,SUM(t.ProfitNum) AS 盈亏坯数
                      --,grouping_id(t.goodstypecode2, t.goodsid) gid
                      --,t.goodstypecode2
                  FROM (SELECT gt2.goodstypecode goodstypecode2
                              ,gt2.goodstypename goodstypename2
                              ,gdrd.goodsid
                              ,gdrd.goodscode
                              ,gdrd.goodstypename
                              ,gdrd.gddc rdcount -- 存坯数
                              ,rdd.gldcount -- 模具数
                              , CASE
                                  WHEN rdd.gldcount IS NOT NULL AND rdd.gldcount > 0 THEN
                                   round(gdrd.gddc / rdd.gldcount, 2)
                                  ELSE
                                   NULL
                                END rddays -- 存坯天数
                              ,gdrd.reserveddays -- 应存天数
                              ,(gdrd.gddc - gdrd.reserveddays * rdd.gldcount) ProfitNum --盈亏坯数
                          FROM (SELECT gdd.goodsid
                                      ,gdd.goodscode
                                      ,g.reserveddays
                                      ,substr(gt.goodstypecode, 1, 6) goodstypecode2
                                      ,gt.goodstypename
                                      ,COUNT(*) gddc
                                  FROM tp_pm_groutingdailydetail gdd
                                 INNER JOIN tp_mst_goods g
                                    ON g.goodsid = gdd.goodsid
                                 INNER JOIN tp_mst_goodstype gt
                                    ON g.goodstypeid = gt.goodstypeid
                 left JOIN tp_pm_inproduction inp
                    ON inp.barcode = gdd.barcode
                                left JOIN tp_pc_procedure p
                                   ON inp.procedureid = p.procedureid
                                 WHERE gdd.accountid = @accountid@
                                   AND gdd.valueflag = '1'
                                   AND gdd.groutingflag = '1'
                                   AND gdd.barcode IS NOT NULL
                                   AND gdd.scrapflag = '0'
                                   AND gdd.deliverflag = '0'
                   AND (gdd.beginningflag='0' or inp.barcode is not null)
                                   AND (@LineCode@ IS NULL OR
                                       instr(gdd.groutinglinecode, @LineCode@) > 0)
                                   AND (@UserCode@ IS NULL OR
                                       instr(gdd.UserCode, @UserCode@) > 0)
                                    AND (@LOGOID@ IS NULL OR
                                       instr(gdd.LOGOID, @LOGOID@) > 0) ");
                if (!String.IsNullOrEmpty(GoodsCode))
                {
                    sqlStr.Append(@" AND gdd.GoodsCode IN ("+GoodsCode+") ");
                }
                sqlStr.Append(@" 
                                   AND (@GoodsTypeCode@ IS NULL OR
                                       instr(gt.GoodstypeCode, @GoodsTypeCode@) = 1)
                                   AND (@DateBegin@ IS NULL OR
                                       gdd.groutingdate BETWEEN @DateBegin@ AND @DateEnd@)
                                   AND (P.procedurename IN('3#阴干区(入)','3#预干区(入)','3#烘干区(入)','3#烘干区(出)') or p.procedurename IS NULL)
                                 GROUP BY gt.goodstypecode,gt.goodstypename
                                         ,gdd.goodsid
                                         ,gdd.goodscode
                                         ,g.reserveddays) gdrd
                          LEFT JOIN (SELECT gld.goodsid
                                          ,COUNT(*) gldcount
                                      FROM tp_pc_groutinglinedetail gld
                                     INNER JOIN tp_pc_groutingline gl
                                        ON gl.groutinglineid = gld.groutinglineid
                                     WHERE gld.valueflag = '1'
                                       AND gld.gmouldstatus = 1
                                       AND gl.accountid = @accountid@
                                       AND gl.valueflag = '1'
                                       AND gl.gmouldstatus = 1
                                     GROUP BY gld.goodsid
                                     ) rdd
                            ON rdd.goodsid = gdrd.goodsid
                         INNER JOIN tp_mst_goodstype gt2
                            ON gt2.accountid = @accountid@
                           AND gt2.goodstypecode = gdrd.goodstypecode2
                         ORDER BY gt2.goodstypecode
                                 ,gdrd.goodscode
                                 ) t
                 GROUP BY GROUPING SETS((t.goodstypecode2, t.goodstypename2,t.goodstypename, t.goodsid, t.goodscode, t.gldcount, t.rddays, t.reserveddays),(t.goodstypecode2, t.goodstypename2),())");
                //直接获取数据
                JsonResult jsonresult = Easyui.ExecuteJsonResult(conn, sqlStr.ToString(), sqlPara);
                context.Response.Write(jsonresult.ToJson());
            }

            //子表1
            if (context.Request["m"].ToString() == "detail1")
            {
                //读取报表数据
                string sqlStr = @"
                    SELECT decode(grouping_id(t.goodstypecode2, t.goodsid),
                             3,
                             '总计',
                             1,
                             '合计[' || t.goodstypename2 || ']',
                             t.goodstypename2) AS 产品类别
                     ,t.goodsid AS 产品ID
                     ,t.goodscode AS 产品编码
                     ,t.groutinglineid AS 成型线号ID
                     ,t.groutinglinecode AS 成型线号
                     ,t.userid AS 工号ID
                     ,t.usercode AS 成型工号
                     ,t.MONITORName AS 成型班长
                     ,SUM(t.rdcount) AS 存坯数
                     ,t.gldcount AS 模具数
                     ,t.rddays AS 存坯天数
                     ,t.reserveddays AS 应存天数
                     ,SUM(t.ProfitNum) AS 盈亏坯数
                    --,grouping_id(t.goodstypecode2, t.goodsid) gid
                     --,t.goodstypecode2
                 FROM (SELECT gt2.goodstypecode goodstypecode2
                             ,gt2.goodstypename goodstypename2
                             ,gdrd.goodsid
                             ,gdrd.goodscode
                             ,gdrd.groutinglineid
                             ,gdrd.groutinglinecode
                             ,gdrd.userid
                             ,gdrd.usercode
                             ,mu.username MONITORName
                             ,gdrd.gddc rdcount -- 存坯数
                             ,rdd.gldcount -- 模具数
                             , CASE
                                 WHEN rdd.gldcount IS NOT NULL AND rdd.gldcount > 0 THEN
                                  round(gdrd.gddc / rdd.gldcount, 2)
                                 ELSE
                                  NULL
                               END rddays -- 存坯天数
                             ,gdrd.reserveddays -- 应存天数
                             ,(gdrd.gddc - gdrd.reserveddays * rdd.gldcount) ProfitNum --盈亏坯数
                         FROM (SELECT gdd.goodsid
                                     ,gdd.goodscode
                                     ,gdd.groutinglineid
                                     ,gdd.groutinglinecode
                                     ,gdd.userid
                                     ,gdd.usercode
                                     ,g.reserveddays
                                     ,substr(gt.goodstypecode, 1, 6) goodstypecode2
                                     ,COUNT(*) gddc
                                 FROM tp_pm_groutingdailydetail gdd
                                INNER JOIN tp_mst_goods g
                                   ON g.goodsid = gdd.goodsid
                                INNER JOIN tp_mst_goodstype gt
                                   ON g.goodstypeid = gt.goodstypeid
                left JOIN tp_pm_inproduction inp
                   ON inp.barcode = gdd.barcode
                               left JOIN tp_pc_procedure p
                                  ON inp.procedureid = p.procedureid
                                WHERE gdd.accountid = @accountid@
                                  AND gdd.valueflag = '1'
                                  AND gdd.groutingflag = '1'
                                  AND gdd.barcode IS NOT NULL
                                  AND gdd.scrapflag = '0'
                                  AND gdd.deliverflag = '0'
                  AND (gdd.beginningflag='0' or inp.barcode is not null)
                                  AND (@LineCode@ IS NULL OR
                                      instr(gdd.groutinglinecode, @LineCode@) > 0)
                                  AND (@UserCode@ IS NULL OR
                                      instr(gdd.UserCode, @UserCode@) > 0)
                                    AND (@LOGOID@ IS NULL OR
                                       instr(gdd.LOGOID, @LOGOID@) > 0) ";
                if (!String.IsNullOrEmpty(GoodsCode))
                {
                    sqlStr += @" AND gdd.GoodsCode IN ("+GoodsCode+") ";
                }
                sqlStr += @" 
                                  AND (@GoodsTypeCode@ IS NULL OR
                                      instr(gt.GoodstypeCode, @GoodsTypeCode@) = 1)
                                  AND (@DateBegin@ IS NULL OR
                                      gdd.groutingdate BETWEEN @DateBegin@ AND @DateEnd@)
                                  AND (P.procedurename IN('3#阴干区(入)','3#预干区(入)','3#烘干区(入)','3#烘干区(出)') or p.procedurename IS NULL)
                                GROUP BY gt.goodstypecode
                                        ,gdd.goodsid
                                        ,gdd.goodscode
                                        ,gdd.groutinglineid
                                        ,gdd.groutinglinecode
                                        ,gdd.userid
                                        ,gdd.usercode
                                        ,g.reserveddays) gdrd
                         LEFT JOIN (SELECT gld.goodsid
                                         ,gld.groutinglineid
                                         ,gl.MONITORID
                                         ,COUNT(*) gldcount
                                     FROM tp_pc_groutinglinedetail gld
                                    INNER JOIN tp_pc_groutingline gl
                                       ON gl.groutinglineid = gld.groutinglineid
                                    WHERE gld.valueflag = '1'
                                      AND gld.gmouldstatus = 1
                                      AND gl.accountid = @accountid@
                                      AND gl.valueflag = '1'
                                      AND gl.gmouldstatus = 1
                                    GROUP BY gld.goodsid, gld.groutinglineid,gl.MONITORID) rdd
                           ON rdd.goodsid = gdrd.goodsid
                          AND rdd.groutinglineid = gdrd.groutinglineid
                LEFT JOIN tp_mst_user mu
                   ON mu.userid = rdd.MONITORID
                        INNER JOIN tp_mst_goodstype gt2
                           ON gt2.accountid = @accountid@
                          AND gt2.goodstypecode = gdrd.goodstypecode2
                        ORDER BY gt2.goodstypecode
                                ,gdrd.goodscode
                                ,gdrd.groutinglinecode
                                ,gdrd.usercode) t
                GROUP BY GROUPING SETS((t.goodstypecode2, t.goodstypename2, t.goodsid, t.goodscode, t.groutinglineid, t.groutinglinecode, t.userid, t.usercode,t.MONITORName, t.gldcount, t.rddays, t.reserveddays),(t.goodstypecode2, t.goodstypename2),())
                ";
                JsonResult jsonresult = Easyui.ExecuteJsonResult(conn, sqlStr.ToString(), sqlPara);
                context.Response.Write(jsonresult.ToJson());
            }

            //子表2
            if (context.Request["m"].ToString() == "detail2")
            {
                //读取报表数据
                string sqlStr = @"
                   SELECT gdd.barcode AS 产品条码
                      ,gdd.groutinglinecode AS 成型线号
                      ,gdd.GroutingMouldCode AS 模具编号
                      ,gdd.goodscode AS 产品编码
                      ,gdd.usercode AS 成型工号
                      ,gdd.groutingdate AS 注浆日期
                      ,p.procedurename AS 当前工序
                       ,mu.username AS 成型班长
                  FROM tp_pm_groutingdailydetail gdd
                 INNER JOIN tp_mst_goods g
                    ON g.goodsid = gdd.goodsid
                 LEFT JOIN tp_pc_groutingline gl
                    ON gl.groutinglineid = GDD.GroutingLineID
                 LEFT JOIN tp_mst_user mu
                    ON mu.userid = gl.MONITORID 
                 INNER JOIN tp_mst_goodstype gt
                    ON g.goodstypeid = gt.goodstypeid
                 left JOIN tp_pm_inproduction inp
                    ON inp.barcode = gdd.barcode
                 left JOIN tp_pc_procedure p
                    ON inp.procedureid = p.procedureid
                 WHERE gdd.accountid = @accountid@
                   AND gdd.valueflag = '1'
                   AND gdd.groutingflag = '1'
                   AND gdd.barcode IS NOT NULL
                   AND gdd.scrapflag = '0'
                   AND gdd.deliverflag = '0'
                   AND (gdd.beginningflag='0' or inp.barcode is not null)
                   AND (@LineID@ IS NULL OR gdd.groutinglineid = @LineID@)
                   AND (@UserID@ IS NULL OR gdd.UserID = @UserID@)
                   AND (@GoodsID@ IS NULL OR gdd.goodsid = @GoodsID@)
                   AND (@LineCode@ IS NULL OR instr(gdd.groutinglinecode, @LineCode@) > 0)
                   AND (@UserCode@ IS NULL OR instr(gdd.UserCode, @UserCode@) > 0)
                    AND (@LOGOID@ IS NULL OR
                                       instr(gdd.LOGOID, @LOGOID@) > 0)
                   
                   ";
                if (!String.IsNullOrEmpty(GoodsCode))
                {
                    sqlStr += @" AND gdd.GoodsCode IN ("+GoodsCode+") ";
                }
                sqlStr += @" 
                   AND (@GoodsTypeCode@ IS NULL OR
                       instr(gt.GoodstypeCode, @GoodsTypeCode@) = 1)
                   AND (@DateBegin@ IS NULL OR
                       gdd.groutingdate BETWEEN @DateBegin@ AND @DateEnd@)
                   AND (P.procedurename IN('3#阴干区(入)','3#预干区(入)','3#烘干区(入)','3#烘干区(出)') or p.procedurename IS NULL)
                 ORDER BY gdd.barcode
                
                ";
                sqlPara.Add(new CDAParameter("GoodsID", GoodsID, DataType.Int32));
                sqlPara.Add(new CDAParameter("LineID", LineID, DataType.Int32));
                sqlPara.Add(new CDAParameter("UserID", UserID, DataType.Int32));
                JsonResult jsonresult = Easyui.ExecuteJsonResult(conn, sqlStr.ToString(), sqlPara);
                context.Response.Write(jsonresult.ToJson());
            }


        }

    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}