HEGII.ss.mes.web/wwwroot/mes/rpt/rpt341/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"]);
                string totalMaster = context.Request["totalMaster"].ToString();

                string groutinglineId = context.Request["groutinglineMaster[]"] is object ? context.Request["groutinglineMaster[]"].ToString() : "0";
                if (groutinglineId == "0" && (context.Request["groutinglineMaster"] is object)) groutinglineId = context.Request["groutinglineMaster"].ToString();
                if (groutinglineId == "") groutinglineId = "0";
                if (!groutinglineId.Equals("0"))
                {
                    List<string> list = new List<string>(groutinglineId.Split(','));
                    List<string> newlist = new List<string>();
                    foreach (string item in list)
                    {
                        if (IsNumber(item))
                        {
                            newlist.Add(item);
                        }
                    }
                    if (newlist != null && newlist.Count < 1)
                    {
                        groutinglineId = "0";
                    }
                    else
                    {
                        groutinglineId = string.Join(",", newlist);
                    }
                }

                string goodsId = context.Request["goodsMaster[]"] is object ? context.Request["goodsMaster[]"].ToString() : "0";
                if (goodsId == "0" && (context.Request["goodsMaster"] is object)) goodsId = context.Request["goodsMaster"].ToString();
                if (goodsId == "") goodsId = "0";
                if (!goodsId.Equals("0")) {
                    List<string> list= new List<string>(goodsId.Split(','));
                    List<string> newlist = new List<string>();
                    foreach (string item in list) {
                        if (IsNumber(item)) {
                            newlist.Add(item);
                        }
                    }
                    if (newlist != null && newlist.Count < 1) {
                        goodsId = "0";
                    }
                    else{
                        goodsId = string.Join(",", newlist);
                    }
                }

                string highFlag = context.Request["highFlagMaster"];
                if (highFlag == "") highFlag = "-1";
                //商标id
			    string logoId = context.Request["LOGONAME"] is object ? context.Request["LOGONAME"].ToString() : "";
			    logoId = logoId==""?string.Empty:"AND GDD.LOGOID=" + logoId;

                string procedureId = context.Request["procedureidMaster[]"] is object ? context.Request["procedureidMaster[]"].ToString() : "0";
                if (procedureId == "0" && context.Request["procedureidMaster"] is object) procedureId = context.Request["procedureidMaster"].ToString();
                if (procedureId == "") procedureId = "0";
                string goodstypeCode = context.Request["goodstypecodeMaster"] is object ? context.Request["goodstypecodeMaster"].ToString() : "";

                string workshopcodeMaster = context.Request["workshopcodeMaster"] is object ? context.Request["workshopcodeMaster"].ToString() : "0";
                if (workshopcodeMaster == "") workshopcodeMaster = "0";

                string dataStr = "";
                if (totalMaster == "YYYY-MM-DD") {
                    int day = DateDiffDay(dateBegin,dateEnd);
                    DateTime beginItem = dateBegin;
                    if (day >= 0) {
                        for (int i = 0; i <= day; i++) {
                            dataStr += "'" + beginItem.Year.ToString() + "-" + beginItem.Month.ToString().PadLeft(2, '0') + "-" + beginItem.Day.ToString().PadLeft(2, '0') + "' AS \"" + beginItem.Year.ToString() + "-" + beginItem.Month.ToString() + "-" + beginItem.Day.ToString() + "\"";
                            if (i < day) {
                                dataStr += ",";
                            }else {
                                dataStr += ", '累计' AS 累计";
                            }
                            beginItem = beginItem.AddDays(1);
                        }
                    }
                } else if (totalMaster == "YYYY-MM") {
                    int month = (dateEnd.Year - dateBegin.Year) * 12 + (dateEnd.Month - dateBegin.Month);
                    DateTime beginItem = dateBegin;
                    if (month >= 0) {
                        for (int i = 0; i <= month; i++) {
                            dataStr += "'" + beginItem.Year.ToString() + "-" + beginItem.Month.ToString().PadLeft(2, '0') + "' AS \"" + beginItem.Year.ToString() + "-" + beginItem.Month.ToString() + "\"";
                            if (i < month) {
                                dataStr += ",";
                            }else {
                                dataStr += ", '累计' AS 累计";
                            }
                            beginItem = beginItem.AddMonths(1);
                        }
                    }
                }else if (totalMaster == "YYYY") {
                    DateTime beginItem = dateBegin;
                    while (beginItem.Year <= dateEnd.Year) {
                        dataStr += "'" + beginItem.Year.ToString() + "' AS \"" + beginItem.Year.ToString() + "\"";
                        if (beginItem.Year < dateEnd.Year)
                        {
                            dataStr += ",";
                        }
                        else {
                            dataStr += ", '累计' AS 累计";
                        }
                        beginItem = beginItem.AddYears(1);
                    }
                }

                //读取报表数据
                string sqlStr = @"
                    WITH TMP_FINISHPACK AS (
                        SELECT
	                        P.GOODSNAME,
	                        L.LOGONAME,
	                        GT2.goodstypecode goodstypecode2,
	                        GT2.goodstypename goodstypename2,
	                        TO_CHAR( P.CREATETIME, 'YYYY-MM-DD' ) 时间,
                            COUNT(1) COUNTNUM
                        FROM
	                        TP_PM_PRODUCTIONDATA P
                            INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID
	                        INNER JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID
	                        INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID
	                        INNER JOIN TP_MST_GOODSTYPE GT2 ON GT2.goodstypecode = substr(GT.goodstypecode, 0, 6)
	                        INNER JOIN TP_PC_GROUTINGLINE GL ON P.GROUTINGLINEID = GL.GROUTINGLINEID
	                        LEFT JOIN TP_MST_LOGO L ON L.LOGOID = P.LOGOID	
                        WHERE
                            P.PROCEDUREID IN (107,13)
	                        AND P.CREATETIME >= @DATEBEGIN@
	                        AND P.CREATETIME <= @DATEEND@
                            AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0') 
                            AND (@USERCODE@ IS NULL OR INSTR(GDD.USERCODE, @USERCODE@) > 0)
						    AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0') 
						    AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
						    {LOGOID}
						    AND ( INSTR( GT.GOODSTYPECODE, '{GOODSTYPECODE}' ) = 1 OR '{GOODSTYPECODE}' IS NULL OR '{GOODSTYPECODE}' = '') 
                            AND ( '{WORKSHOPCODE}' =  '0' 
                                        OR ( '{WORKSHOPCODE}' =  '1'AND (INSTR( GDD.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1) ) 
                                        OR ( '{WORKSHOPCODE}' =  '2'AND ((INSTR( GDD.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( GDD.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1)) 
                                        OR ( '{WORKSHOPCODE}' =  '3'AND (INSTR( GDD.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( GDD.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( GDD.GROUTINGLINECODE, 'C06B' ) = 1)))
                            GROUP BY P.GOODSNAME, L.LOGONAME, GT2.goodstypecode , GT2.goodstypename ,TO_CHAR( P.CREATETIME, 'YYYY-MM-DD' ) 
                       UNION ALL
                       SELECT
	                        P.GOODSNAME,
	                        L.LOGONAME,
	                        GT2.goodstypecode goodstypecode2,
	                        GT2.goodstypename goodstypename2,
	                        TO_CHAR( P.CREATETIME, 'YYYY-MM-DD' ) 时间,
                            -1*COUNT(1) COUNTNUM
                        FROM
	                        TP_PM_PRODUCTIONDATA P
                            INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = P.GROUTINGDAILYDETAILID
	                        INNER JOIN TP_MST_GOODS G ON G.GOODSID = P.GOODSID
	                        INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID
	                        INNER JOIN TP_MST_GOODSTYPE GT2 ON GT2.goodstypecode = substr(GT.goodstypecode, 0, 6)
	                        INNER JOIN TP_PC_GROUTINGLINE GL ON P.GROUTINGLINEID = GL.GROUTINGLINEID
	                        LEFT JOIN TP_MST_LOGO L ON L.LOGOID = P.LOGOID	
                        WHERE
	                        P.PROCEDUREID IN (107,13)
	                        AND P.BACKOUTTIME >= @DATEBEGIN@
	                        AND P.BACKOUTTIME <= @DATEEND@
                            AND (G.GOODSID IN ({GOODSID}) OR '{GOODSID}' = '0') 
                            AND (@USERCODE@ IS NULL OR INSTR(GDD.USERCODE, @USERCODE@) > 0)
						    AND (GL.GROUTINGLINEID IN ({GROUTINGLINEID}) OR '{GROUTINGLINEID}' = '0') 
						    AND (GL.HIGHPRESSUREFLAG = @HIGHPRESSUREFLAG@ OR @HIGHPRESSUREFLAG@ = '-1')
                            {LOGOID}
						    AND ( INSTR( GT.GOODSTYPECODE, '{GOODSTYPECODE}' ) = 1 OR '{GOODSTYPECODE}' IS NULL OR '{GOODSTYPECODE}' = '') 
                            AND ( '{WORKSHOPCODE}' =  '0' 
                                        OR ( '{WORKSHOPCODE}' =  '1'AND (INSTR( GDD.GROUTINGLINECODE, 'A' ) = 1 OR INSTR( GT.GOODSTYPECODE, '001002' ) = 1)  ) 
                                        OR ( '{WORKSHOPCODE}' =  '2'AND ((INSTR( GDD.GROUTINGLINECODE, 'B' ) = 1 OR INSTR( GDD.GROUTINGLINECODE, 'D' ) = 1) AND INSTR( GT.GOODSTYPECODE, '001001' ) = 1)) 
                                        OR ( '{WORKSHOPCODE}' =  '3'AND (INSTR( GDD.GROUTINGLINECODE, 'C' ) = 1 AND INSTR( GDD.GROUTINGLINECODE, 'A' ) = 4 OR INSTR( GDD.GROUTINGLINECODE, 'C06B' ) = 1)))
                            GROUP BY P.GOODSNAME, L.LOGONAME, GT2.goodstypecode , GT2.goodstypename ,TO_CHAR( P.CREATETIME, 'YYYY-MM-DD' )
                    )




                    SELECT * FROM (
                        SELECT decode(GID,3,'总计',0,GOODSNAME, '合计【' || to_char(goodstypename2) || '】') 产品名称,
	                        LOGONAME 产品商标,
	                        GOODSTYPECODE2,
	                        NVL(时间,'累计') 时间,
	                        COUNTNUM,
	                        GID
                        FROM(
                            SELECT
	                            grouping_id(GOODSNAME,goodstypecode2) GID,
	                            GOODSNAME,
	                            LOGONAME,
	                            goodstypecode2,
	                            goodstypename2,
	                            时间 ,
	                            SUM(COUNTNUM) COUNTNUM
                            FROM
	                            TMP_FINISHPACK	
                            GROUP BY GROUPING SETS ((GOODSNAME, LOGONAME, goodstypecode2, goodstypename2, 时间),(GOODSNAME, LOGONAME, goodstypecode2, goodstypename2),(goodstypecode2, goodstypename2, 时间),(goodstypecode2, goodstypename2),(时间),())
                        ) 
                    )
                    PIVOT(SUM(COUNTNUM) FOR 时间 IN({日期}))
                    ORDER BY goodstypecode2,GID
                ".Replace("{日期}", dataStr)
                 .Replace("YYYY-MM-DD",totalMaster)
                 .Replace("{GOODSID}",goodsId)
                 .Replace("{GROUTINGLINEID}",groutinglineId)
                 .Replace("{PROCEDUREID}",procedureId)
                 .Replace("{WORKSHOPCODE}",workshopcodeMaster)
                 .Replace("{GOODSTYPECODE}",goodstypeCode)
                 .Replace("{LOGOID}", logoId);



                //获取查询条件
                List<CDAParameter> sqlPara = new List<CDAParameter>();
                sqlPara.Add(new CDAParameter("DATEBEGIN", dateBegin, DataType.DateTime));
                sqlPara.Add(new CDAParameter("DATEEND", dateEnd, DataType.DateTime));
                sqlPara.Add(new CDAParameter("HIGHPRESSUREFLAG",highFlag));
                sqlPara.Add(new CDAParameter("USERCODE", context.Request["usercodeMaster"]));
                //直接获取不分页数据
                DataTable dt = conn.ExecuteDatatable(sqlStr, sqlPara.ToArray());

                //去掉无关列
                dt.Columns.Remove("GID");
                dt.Columns.Remove("GOODSTYPECODE2");

                for (int i = 2; i < dt.Columns.Count; i++) {
                    foreach (DataRow r in dt.Rows) {
                        if (Convert.IsDBNull(r[i])) {
                            r[i] = 0;
                        }
                    }
                }
                context.Response.Write(new JsonResult(dt) { total = dt.Rows.Count }.ToJson());
            }

        }

    }

    //相差天数
    private int DateDiffDay(DateTime dateStart, DateTime dateEnd)
    {
        DateTime start = Convert.ToDateTime(dateStart.ToShortDateString());
        DateTime end = Convert.ToDateTime(dateEnd.ToShortDateString());
        TimeSpan sp = end.Subtract(start);
        return sp.Days;
    }

    public static bool IsNumber(string s)
    {
        try {
            int.Parse(s);
            return true;
        }
        catch (Exception) {
            return false;
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
    /// <summary>
    /// 执行sql语句
    /// </summary>
    /// <param name="conn">连接</param>
    /// <param name="sql">sql语句</param>
    /// <param name="sqlPara">参数</param>
    /// <returns>json数据</returns>
    private DataTable ExecuteData(IDataAccess conn, string sql, ref List<CDAParameter> sqlPara)
    {
        DataTable dt;
        int total = 0;
        if (HttpContext.Current.Request["page"] is object)
        {
            //获取分页数据
            int page = HttpContext.Current.Request["page"] is object ? Convert.ToInt32(HttpContext.Current.Request["page"]) : 1;
            int rows = HttpContext.Current.Request["rows"] is object ? Convert.ToInt32(HttpContext.Current.Request["rows"]) : 10;
            string sort = HttpContext.Current.Request["sort"] is object ? HttpContext.Current.Request["sort"] : " createtime ";
            string order = HttpContext.Current.Request["order"] is object ? HttpContext.Current.Request["order"] : "";
            dt = conn.SelectPages(page, rows, out total, sql, sqlPara.ToArray());
        }
        else
        {
            //直接获取不分页数据
            dt = conn.ExecuteDatatable(sql, sqlPara.ToArray());
            total = dt.Rows.Count;
        }
        return dt;
    }

}