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 = Convert.ToDateTime(context.Request["dateendMaster"]);

                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 = "";
                for (int i = 1; i <= timespan.TotalHours; i++)
                {
                    lie += i + ",";
                    sum += "sum(点" + i + ") \"" + ((i + dateBegin.Hour > 24 ? (i + dateBegin.Hour - 24) : (i + dateBegin.Hour)) - 1) + "点至" + (i + dateBegin.Hour > 24 ? (i + dateBegin.Hour - 24) : (i + dateBegin.Hour)) + "点\",";
                    casestr += "case when createtime<@DATEBEGIN@+ INTERVAL '" + i + "' HOUR and createtime>@DATEBEGIN@ + INTERVAL '" + (i - 1) + "' HOUR then 1 else 0 end as 点" + i + ",";
                }
                lie = lie.Trim(',');
                sum = sum.Trim(',');
                casestr = casestr.Trim(',');

                //读取报表数据
                string sqlStr = @"
				select   productionlineid 生产线,
       goodstypename 产品类别,
       goodscode 产品编码,
       goodsspecification 产品介绍,
       GOODSMODEL 产品型号,
       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
                ".Replace("{lie}", lie)
                .Replace("{sum}", sum)
                .Replace("{sqlwhere}", sqlwhere)
                .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;
        }
    }

}