<%@ 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 list = new List(groutinglineId.Split(',')); List newlist = new List(); 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 list= new List(goodsId.Split(',')); List newlist = new List(); 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() : ""; string logoIds = context.Request["LOGONAME[]"] is object ? context.Request["LOGONAME[]"].ToString() : ""; logoId = logoId==""?logoIds==""?string.Empty:"AND GDD.LOGOID IN (" + logoIds +") ":"AND GDD.LOGOID=" + logoId; //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 sqlPara = new List(); 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; } } ///

/// 执行sql语句 ///

/// 连接 /// sql语句 /// 参数 /// json数据 private DataTable ExecuteData(IDataAccess conn, string sql, ref List 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; } }