HEGII.ss.mes.web/wwwroot/mes/rpt/rpt423/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())
        {
            DateTime dateBegin;
            DateTime dateEnd;
            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 goodsidMaster = context.Request["goodsidMaster[]"] is object ? context.Request["goodsidMaster[]"] : "0";
            if (goodsidMaster == "0" && context.Request["goodsidMaster"] is object) goodsidMaster = context.Request["goodsidMaster"].ToString();
            if (goodsidMaster == "") goodsidMaster = "0";
            //主表
            if (context.Request["m"].ToString() == "0")
            {
                dateBegin = Convert.ToDateTime(context.Request["datebeginMaster"]);
                dateEnd = dateBegin.AddDays(1);

                string sqlwhere = "";
                if (!string.IsNullOrEmpty(goodstypeCode) && goodstypeCode != "0")
                {
                    sqlwhere += " and gt.goodstypeCode=" + goodstypeCode;
                }
                if (!string.IsNullOrEmpty(goodsidMaster) && goodsidMaster != "0")
                {
                    sqlwhere += " and g.GOODSID in(" + goodsidMaster + ")";
                }
                var timespan = dateEnd - dateBegin;
                //if (timespan.TotalHours > 48 || timespan.TotalHours <= 0 || procedureId == "0")
                //{
                //    //context.Response.Write(new JsonResult(JsonStatus.loginError) { message = "必须选择数据来源和生产工序!" }.ToJson());
                //    return;
                //}
                string lie = "";
                string sum = "";
                string casestr = "";
                string heji = "";
                string sumheji = "";
                for (int i = 1; i <= timespan.TotalHours; i++)
                {
                    int begin = ((i + dateBegin.Hour > 24 ? (i + dateBegin.Hour - 24) : (i + dateBegin.Hour)) - 1);
                    lie += i + ",";
                    sum += "sum(点" + i + ") \"" + begin + "点至" + (begin + 1) + "点\",";
                    casestr += "case when createtime<@DATEBEGIN@+ INTERVAL '" + i + "' HOUR and createtime>@DATEBEGIN@ + INTERVAL '" + (i - 1) + "' HOUR then 1 else 0 end as 点" + i + ",";
                    heji += "\"" + begin + "点至" + (begin + 1) + "点\",";
                    sumheji += "sum(\"" + begin + "点至" + (begin + 1) + "点\") as \"" + begin + "点至" + (begin + 1) + "点\",";
                }
                lie = lie.Trim(',');
                sum = sum.Trim(',');
                casestr = casestr.Trim(',');
                heji = heji.Trim(',');
                sumheji = sumheji.Trim(',');

                //读取报表数据
                string sqlStr = @"
                    select  										
										decode(pdg.gid, 3, '合计', 0, pdg.productionlineid, '--') 生产线,       
										decode(pdg.gid, 0, pdg.goodstypename, '--') ""产品类别"",
				decode(pdg.gid, 0, pdg.goodscode, '--') ""产品编码"",
       decode(pdg.gid, 0, pdg.goodsspecification, '--') ""产品规格"",
       decode(pdg.gid, 0, pdg.GLAZETYPE, '--') ""釉料类别"",
 合计,{heji}
 from
 (select   grouping_id(productionlineid,goodscode) gid,       
  productionlineid,       
 goodstypename,       
 goodscode,       
 goodsspecification,       
 GLAZETYPE,
 sum(合计) as 合计,{sumheji} from
 (select   productionlineid,
       goodstypename ,
       goodscode ,
       goodsspecification ,
       GLAZETYPE ,
			 sum(合计) 合计,
            {sum}
			 from (
select  b.*,1 合计,
			{casestr}
from ( select
       pd.productionlineid,
       gt2.goodstypename goodstypename2,
       gt.goodstypename,
       g.goodscode,
       g.goodsspecification,
       g.GOODSMODEL,
       dd.dictionaryvalue GLAZETYPE,
			 pd.createtime			 
  from tp_pm_productiondata pd
 inner join tp_mst_goods g
    on g.goodsid = pd.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)
   and gt2.accountid = gt.accountid
 inner join tp_mst_datadictionary dd
    on dd.dictionaryid = g.GLAZETYPEID
 where  pd.accountid = 1
   and pd.productionlineid = 1
   and pd.createtime >= @DATEBEGIN@
   and pd.createtime < @DATEEND@
   {sqlwhere}
   AND((pd.procedureid = 11 AND pd.valueflag = '1' AND(pd.checkflag = '1' OR pd.checkflag IS NULL)) OR 
    (pd.procedureid = 104 AND pd.checkflag = '1') OR (pd.procedureid NOT IN(11,104) AND pd.valueflag = '1'))   and instr(',' || {procedureId} || ',', ',' || pd.procedureid || ',') > 0
	 and pd.procedureid={procedureId}
    AND exists (Select 1  from TP_MST_UserPurview up where up.PurviewType= 7 and up.UserID = 1828 and (up.PurviewID = -1 or up.PurviewID= pd.ProductionLineId))) b 
		left join (
select  {lie}  from dual) numlie on 1=1 ) detail
 GROUP BY 
       productionlineid,
       goodstypename,
       goodstypename,
       goodscode,
       goodsspecification,
       GOODSMODEL,
       GLAZETYPE) group by grouping sets ( (productionlineid,
       goodstypename,
       goodscode,
       goodsspecification,
       GLAZETYPE),()) ) pdg
                ".Replace("{lie}", lie)
                .Replace("{sum}", sum)
                .Replace("{sqlwhere}", sqlwhere)
                .Replace("{heji}", heji)
                .Replace("{sumheji}", sumheji)
                .Replace("{procedureId}", procedureId)
                .Replace("{casestr}", casestr);

                //获取查询条件
                List<CDAParameter> sqlPara = new List<CDAParameter>();
                sqlPara.Add(new CDAParameter("DATEBEGIN", dateBegin, DataType.DateTime));
                sqlPara.Add(new CDAParameter("DATEEND", dateEnd, DataType.DateTime));

                JsonResult data = Easyui.ExecuteJsonResult(conn, sqlStr, sqlPara);
                context.Response.Write(data.ToJson());
            }
        }

    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}