IMEX.mes.web/wwwroot/mes/pm/celsiusrecord/celsiusrecord.ashx

<%@ WebHandler Language="C#" Class="celsiusrecord" %>

using System;
using System.Web;
using System.Web.SessionState;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using Curtain.DataAccess;
using DK.XuWei.WebMes;

/// <summary>
/// TP_PM_CELSIUSRECORD
/// xuwei create 2020-04-22
/// </summary>
public class celsiusrecord : IHttpHandler, IReadOnlySessionState
{
    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "text/plain";

        if (mes.LoginCheck() && context.Request["m"] is object)
        {
            Button b = new Button();
            if(mes.RightCheck("系统管理"))
            {
                b.btnIndex = true;
                b.btnInsert = true;
                b.btnInsertBatch = true;
                b.btnCopy = true;
                b.btnUpdate = true;
                b.btnDelete = true;
                b.btnCancel = true;
                b.btnSearch = true;
                b.btnDetail = true;
                b.btnCheckbox = true;
                b.btnExport = true;
                b.btnImport = true;
                b.btnReload = true;
            };
            switch (context.Request["m"].ToString().ToLower())
            {
                case "b":
                    {
                        //按钮
                        context.Response.Write(new JsonResult(b).ToJson());
                        break;
                    }
                case "a":
                    {
                        //添加
                        if (b.btnDetail && context.Request["id"].ToString() != "")
                            context.Response.Write(detail());
                        else
                        {
                            xRecord r = new xRecord();
                            context.Response.Write(new JsonResult(r).ToJson());
                        }
                        break;
                    }
                case "s":
                    {
                        //搜索
                        if (b.btnIndex)
                            context.Response.Write(search(context.Request.Form));
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "t":
                    {
                        //详细
                        if (b.btnDetail)
                            context.Response.Write(detail());
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "i":
                    {
                        //插入
                        if (b.btnInsert)
                            context.Response.Write(insert(context.Request.Form));
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "im":
                    {
                        //导入数据
                        if (b.btnImport)
                        {
                            //上传文件
                            string filePath = "/mes/upload/"+DateTime.Now.ToString("yyyy-MM-dd");
                            System.IO.Directory.CreateDirectory(context.Server.MapPath(filePath));
                            string fileName = filePath + "/温湿度记录" + DateTime.Now.ToString("yyyy-MM");
                            fileName += System.IO.Path.GetExtension(context.Request.Files[0].FileName);
                            string diskFileName = context.Server.MapPath(fileName);
                            if (System.IO.File.Exists(diskFileName)) System.IO.File.Delete(diskFileName);
                            context.Request.Files[0].SaveAs(diskFileName);

                            //读取Excel数据
                            DataTable excelTable = Import.ExcelToDataTable(diskFileName);

                            //写入数据
                            using(IDataAccess conn = DataAccess.Create())
                            {
                                try
                                {
                                    conn.BeginTransaction();

                                    //按温湿计编码删除全部数据
                                    conn.ExecuteNonQuery(@"
                                        DELETE 
                                            TP_PM_CELSIUSRECORD 
                                        WHERE
                                            THERMOMETERID = @THERMOMETERID@
                                        ",
                                        new CDAParameter("THERMOMETERID", context.Request["THERMOMETERID"])
                                     );

                                    for (int i=0; i<excelTable.Rows.Count; i++)
                                    {
                                        //合并日期
                                        string dateStr = Convert.ToDateTime(excelTable.Rows[i]["日期"]).ToString("yyyy-MM-dd");
                                        string timeStr = Convert.ToDateTime(excelTable.Rows[i]["时间"]).ToString("HH:mm:ss");
                                        string dateTimeStr = dateStr + " " + timeStr;

                                        //逐行删除数据
                                        //conn.ExecuteNonQuery(@"
                                        //    DELETE 
                                        //        TP_PM_CELSIUSRECORD 
                                        //    WHERE
                                        //        THERMOMETERID = @THERMOMETERID@
                                        //        AND RECORDDATE = @RECORDDATE@
                                        //    ",
                                        //    new CDAParameter("THERMOMETERID", context.Request["THERMOMETERID"]),
                                        //    new CDAParameter("RECORDDATE", Convert.ToDateTime(dateTimeStr))
                                        // );

                                        //写入数据库
                                        conn.ExecuteNonQuery(@"
                                            INSERT INTO TP_PM_CELSIUSRECORD (
                                                THERMOMETERID,
                                                RECORDERID,
                                                RECORDDATE,
                                                CELSIUS,
                                                HUMIDITY,
                                                ACCOUNTID,CREATEUSERID,UPDATEUSERID
                                            ) VALUES (
                                                @THERMOMETERID@,
                                                @RECORDERID@,
                                                @RECORDDATE@,
                                                @CELSIUS@,
                                                @HUMIDITY@,
                                                @ACCOUNTID@,@CREATEUSERID@,@UPDATEUSERID@
                                            )
                                            ",
                                            new CDAParameter("THERMOMETERID",context.Request["THERMOMETERID"]),
                                            new CDAParameter("RECORDERID",context.Session["userId"]),
                                            new CDAParameter("RECORDDATE",Convert.ToDateTime(dateTimeStr)),
                                            new CDAParameter("CELSIUS",excelTable.Rows[i]["温度"]),
                                            new CDAParameter("HUMIDITY",excelTable.Rows[i]["湿度"]),
                                            new CDAParameter("ACCOUNTID",context.Session["accountId"]),
                                            new CDAParameter("CREATEUSERID",context.Session["userId"]),
                                            new CDAParameter("UPDATEUSERID",context.Session["userId"])
                                         );
                                    }
                                    conn.Commit();
                                }
                                catch
                                {
                                    conn.Rollback();
                                }
                            }


                            context.Response.Write(new JsonResult(JsonStatus.success).ToJson());
                        }
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "u":
                    {
                        //修改
                        if (b.btnUpdate)
                            context.Response.Write(update(context.Request.Form));
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "d":
                    {
                        //删除
                        if (b.btnDelete)
                            context.Response.Write(delete());
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "e":
                    {
                        //导出
                        if (b.btnExport)
                        {
                            context.Response.Write(export());
                        }
                        else
                        {
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        }
                        break;
                    }
                default:
                    {
                        break;
                    }
            }
        }
        else
        {
            context.Response.Write(new JsonResult(JsonStatus.loginError).ToJson());
        }
    }

    /// <summary>
    /// TP_PM_CELSIUSRECORD 查询
    /// </summary>
    /// <returns>json</returns>
    private string search(NameValueCollection form)
    {
        using(IDataAccess conn=DataAccess.Create())
        {
            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"] : "";
            string order = HttpContext.Current.Request["order"] is object ? HttpContext.Current.Request["order"] : "";

            string sqlStr = @"
                SELECT 
                    m.RECORDID AS SID,
                    m.RECORDID,
                    m.THERMOMETERID,
                    t.THERMOMETERCODE,
                    m.RECORDERID,
                    u.USERCODE AS RECORDERCODE,
                    m.RECORDDATE,
                    m.CELSIUS,
                    m.HUMIDITY,
                    m.REMARKS,
                    m.VALUEFLAG,
                    m.ACCOUNTID,
                    m.CREATEUSERID,
                    m.CREATETIME,
                    m.UPDATEUSERID,
                    m.UPDATETIME
                FROM
                    TP_PM_CELSIUSRECORD m
                    LEFT JOIN TP_MST_THERMOMETER t ON m.THERMOMETERID = t.THERMOMETERID
                    LEFT JOIN TP_MST_USER u ON m.RECORDERID = u.USERID
                WHERE 
                    m.VALUEFLAG = '1'
                    AND m.ACCOUNTID = @ACCOUNTID@
            ";
            List<CDAParameter> sqlPara = new List<CDAParameter>();
            sqlPara.Add(new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]));

            if(!string.IsNullOrEmpty(form["RECORDID"]))
            {
                sqlStr += " AND m.RECORDID = @RECORDID@ ";
                sqlPara.Add(new CDAParameter("RECORDID", form["RECORDID"]));
            }
            if(!string.IsNullOrEmpty(form["THERMOMETERID"]))
            {
                sqlStr += " AND INSTR( m.THERMOMETERID, @THERMOMETERID@ ) > 0 ";
                sqlPara.Add(new CDAParameter("THERMOMETERID", form["THERMOMETERID"]));
            }
            if(!string.IsNullOrEmpty(form["RECORDERID"]))
            {
                sqlStr += " AND INSTR( m.RECORDERID, @RECORDERID@ ) > 0 ";
                sqlPara.Add(new CDAParameter("RECORDERID", form["RECORDERID"]));
            }
            if(!string.IsNullOrEmpty(form["RECORDDATE"]))
            {
                sqlStr += " AND INSTR( m.RECORDDATE, @RECORDDATE@ ) > 0 ";
                sqlPara.Add(new CDAParameter("RECORDDATE", form["RECORDDATE"]));
            }
            if(!string.IsNullOrEmpty(form["CELSIUS"]))
            {
                sqlStr += " AND INSTR( m.CELSIUS, @CELSIUS@ ) > 0 ";
                sqlPara.Add(new CDAParameter("CELSIUS", form["CELSIUS"]));
            }
            if(!string.IsNullOrEmpty(form["HUMIDITY"]))
            {
                sqlStr += " AND INSTR( m.HUMIDITY, @HUMIDITY@ ) > 0 ";
                sqlPara.Add(new CDAParameter("HUMIDITY", form["HUMIDITY"]));
            }
            if(!string.IsNullOrEmpty(form["REMARKS"]))
            {
                sqlStr += " AND INSTR( m.REMARKS, @REMARKS@ ) > 0 ";
                sqlPara.Add(new CDAParameter("REMARKS", form["REMARKS"]));
            }
            if(sort != "")
            {
                sqlStr += " ORDER BY " + sort + " " + order;
            }
            int total = 0;
            DataTable dt = conn.SelectPages(page, rows,out total, sqlStr, sqlPara.ToArray());
            return new JsonResult(dt) { total = total}.ToJson();
        }
    }

    /// <summary>
    /// 详细 TP_PM_CELSIUSRECORD
    /// </summary>
    /// <returns>json</returns>
    private string detail()
    {
        using(IDataAccess conn= DataAccess.Create())
        {
            DataTable dt = conn.ExecuteDatatable(@"
                SELECT 
                    m.RECORDID AS SID,
                    m.RECORDID,
                    m.THERMOMETERID,
                    t.THERMOMETERCODE,
                    m.RECORDERID,
                    u.USERCODE AS RECORDERCODE,
                    m.RECORDDATE,
                    m.CELSIUS,
                    m.HUMIDITY,
                    m.REMARKS,
                    m.VALUEFLAG,
                    m.ACCOUNTID,
                    m.CREATEUSERID,
                    m.CREATETIME,
                    m.UPDATEUSERID,
                    m.UPDATETIME
                FROM
                    TP_PM_CELSIUSRECORD m
                    LEFT JOIN TP_MST_THERMOMETER t ON m.THERMOMETERID = t.THERMOMETERID
                    LEFT JOIN TP_MST_USER u ON m.RECORDERID = u.USERID
                WHERE 
                    m.VALUEFLAG = '1'
                    AND m.ACCOUNTID = @ACCOUNTID@
                    AND m.RECORDID = @RECORDID@
                ",
                new CDAParameter("ACCOUNTID",HttpContext.Current.Session["accountId"]),
                new CDAParameter("RECORDID",HttpContext.Current.Request["id"])
            );
            return new JsonResult(dt).ToJson();
        }
    }

    /// <summary>
    /// 插入 TP_PM_CELSIUSRECORD
    /// </summary>
    /// <returns>json</returns>
    private string insert(NameValueCollection form)
    {
        using(IDataAccess conn= DataAccess.Create())
        {
            //string primaryKey = conn.GetSequenceNextval("SEQ_TP_PM_CELSIUSRECORD").ToString();
            int result = conn.ExecuteNonQuery(@"
                INSERT INTO TP_PM_CELSIUSRECORD (
                    --RECORDID,
                    THERMOMETERID,
                    RECORDERID,
                    RECORDDATE,
                    CELSIUS,
                    HUMIDITY,
                    REMARKS,
                    ACCOUNTID,CREATEUSERID,UPDATEUSERID
                ) VALUES (
                    --@RECORDID@,
                    @THERMOMETERID@,
                    @RECORDERID@,
                    @RECORDDATE@,
                    @CELSIUS@,
                    @HUMIDITY@,
                    @REMARKS@,
                    @ACCOUNTID@,@CREATEUSERID@,@UPDATEUSERID@
                )
                ",
                //new CDAParameter("RECORDID",primaryKey),
                new CDAParameter("THERMOMETERID",form["THERMOMETERID"]),
                new CDAParameter("RECORDERID",form["RECORDERID"]),
                new CDAParameter("RECORDDATE",Convert.ToDateTime(form["RECORDDATE"])),
                new CDAParameter("CELSIUS",form["CELSIUS"]),
                new CDAParameter("HUMIDITY",form["HUMIDITY"]),
                new CDAParameter("REMARKS",form["REMARKS"]),
                new CDAParameter("ACCOUNTID",HttpContext.Current.Session["accountId"]),
                new CDAParameter("CREATEUSERID",HttpContext.Current.Session["userId"]),
                new CDAParameter("UPDATEUSERID",HttpContext.Current.Session["userId"])
            );
            return new JsonResult(JsonStatus.success).ToJson();
        }
    }

    /// <summary>
    /// 更新 TP_PM_CELSIUSRECORD
    /// </summary>
    /// <returns>json</returns>
    private string update(NameValueCollection form)
    {
        using(IDataAccess conn=DataAccess.Create())
        {
            int result = conn.ExecuteNonQuery(@"
                UPDATE TP_PM_CELSIUSRECORD 
                SET
                    THERMOMETERID = @THERMOMETERID@,
                    RECORDERID = @RECORDERID@,
                    RECORDDATE = @RECORDDATE@,
                    CELSIUS = @CELSIUS@,
                    HUMIDITY = @HUMIDITY@,
                    REMARKS = @REMARKS@,
                    UPDATEUSERID = @UPDATEUSERID@,
                    UPDATETIME = sysdate
                WHERE 
                    RECORDID = @RECORDID@
                ",
                new CDAParameter("THERMOMETERID",form["THERMOMETERID"]),
                new CDAParameter("RECORDERID",form["RECORDERID"]),
                new CDAParameter("RECORDDATE",Convert.ToDateTime(form["RECORDDATE"])),
                new CDAParameter("CELSIUS",form["CELSIUS"]),
                new CDAParameter("HUMIDITY",form["HUMIDITY"]),
                new CDAParameter("REMARKS",form["REMARKS"]),
                new CDAParameter("UPDATEUSERID",HttpContext.Current.Session["userId"]),
                new CDAParameter("RECORDID",HttpContext.Current.Request["id"])
            );
            return new JsonResult(JsonStatus.success).ToJson();
        }
    }

    /// <summary>
    /// 删除 TP_PM_CELSIUSRECORD
    /// </summary>
    /// <returns>json</returns>
    private string delete()
    {
        using(IDataAccess conn= DataAccess.Create())
        {
            if (HttpContext.Current.Request["id"] is object)
            {
                int result = conn.ExecuteNonQuery(@"
                    DELETE 
                        TP_PM_CELSIUSRECORD 
                    WHERE 
                        INSTR(',' || @RECORDID@ || ',' , ',' || RECORDID || ',') > 0 
                    ",
                    new CDAParameter("RECORDID", HttpContext.Current.Request["id"])
                );
                return new JsonResult(JsonStatus.success).ToJson();
            }
            else
            {
                return new JsonResult(JsonStatus.otherError).ToJson();
            }
        }
    }

    /// <summary>
    /// 导出 TP_PM_CELSIUSRECORD
    /// </summary>
    /// <returns>json</returns>
    private string export()
    {
        return search(new NameValueCollection());
    }

    private class Button
    {
        public bool btnIndex = false;
        public bool btnInsert = false;
        public bool btnInsertBatch = false;
        public bool btnCopy = false;
        public bool btnUpdate = false;
        public bool btnDelete = false;
        public bool btnCancel = false;
        public bool btnSearch = false;
        public bool btnDetail = false;
        public bool btnCheckbox = false;
        public bool btnExport = false;
        public bool btnImport = false;
        public bool btnReload = false;
    }

    private class xRecord
    {
        public string sid { get; set; }
        public string THERMOMETERID { get; set; }
        public string RECORDERID { get; set; }
        public string RECORDDATE { get; set; }
        public string CELSIUS { get; set; }
        public string HUMIDITY { get; set; }
        public string REMARKS { get; set; }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}