HEGII.ss.mes.web/wwwroot/mes/pm/fqcconfig/fqcconfig.ashx

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

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;
using System.Linq;


/// <summary>
/// TP_PM_FQCCONFIG
/// xuwei create 2023-09-07
/// </summary>
public class fqcconfig : 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.fqcconfigButtonIndex = true;
                b.fqcconfigButtonInsert = true;
                b.fqcconfigButtonInsertBatch = true;
                b.fqcconfigButtonCopy = true;
                b.fqcconfigButtonUpdate = true;
                b.fqcconfigButtonDelete = true;
                b.fqcconfigButtonCancel = true;
                b.fqcconfigButtonSearch = true;
                b.fqcconfigButtonDetail = true;
                b.fqcconfigButtonCheckbox = true;
                b.fqcconfigButtonExport = true;
                b.fqcconfigButtonReload = true;
            };
            switch (context.Request["m"].ToString().ToLower())
            {
                case "b":
                    {
                        //按钮
                        context.Response.Write(new JsonResult(b).ToJson());
                        break;
                    }
                case "a":
                    {
                        //添加
                        if (b.fqcconfigButtonDetail && context.Request["id"] is object)
                            context.Response.Write(detail());
                        else
                        {
                            List<xRecord> list = new List<xRecord>();
                            xRecord r = new xRecord();
                            r.DISPLAYNO = "0";
                            list.Add(r);
                            context.Response.Write(new JsonResult(list).ToJson());
                        }
                        break;
                    }
                case "s":
                    {
                        //搜索
                        if (b.fqcconfigButtonIndex)
                            context.Response.Write(search(context.Request.Form));
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "t":
                    {
                        //详细
                        if (b.fqcconfigButtonDetail)
                            context.Response.Write(detail());
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "i":
                    {
                        //插入
                        if (b.fqcconfigButtonInsert)
                            context.Response.Write(insert(context.Request.Form));
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "u":
                    {
                        //修改
                        if (b.fqcconfigButtonUpdate)
                            context.Response.Write(update(context.Request.Form));
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "d":
                    {
                        //删除
                        if (b.fqcconfigButtonDelete)
                            context.Response.Write(delete());
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "e":
                    {
                        //导出
                        if (b.fqcconfigButtonExport)
                        {
                            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_FQCCONFIG 查询
    /// </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 sql = @" WITH RankedEmployees AS ( 
                    SELECT 
                    m.CONFIGID AS SID,
                    m.CONFIGID,   
                    m.OUTQUANTITY,
                    m.INSPECTIONQUANTITY,
                    m.MINOUTQUANTITY,
                    m.DISPLAYNO,
                    m.REMARKS,
                    m.VALUEFLAG,
                    m.ACCOUNTID, 
                    m.CREATETIME, 
                    m.UPDATETIME,
                    usr.USERCODE AS 更新工号,
                    us.USERCODE AS 创建工号, 
                    CASE WHEN  m.CLASSES = 1 THEN '一天一班' ELSE '一天二班' END AS  CLASSES,
                    m.INSPECTTYPES, 
                    ROW_NUMBER() OVER (ORDER BY  m.CONFIGID) AS rn 
                FROM
                    TP_PM_FQCCONFIG m 
					LEFT JOIN TP_MST_USER us ON us.USERID=M.CREATEUSERID
					LEFT JOIN TP_MST_USER usr ON usr.USERID=M.CREATEUSERID
                WHERE 
                    m.VALUEFLAG = '1'
                    AND m.ACCOUNTID = @ACCOUNTID@";
            sql += form["CONFIGID"] == "" || form["CONFIGID"] == null ? "" : " AND m.CONFIGID = '" + form["CONFIGID"] + "'";
            sql += form["INSPECTTYPES"] == "" || form["INSPECTTYPES"] == null ? "" : " AND INSTR( m.INSPECTTYPES," + form["INSPECTTYPES"] + " ) > 0 ";
            sql += form["OUTQUANTITY"] == "" || form["OUTQUANTITY"] == null ? "" : " AND INSTR( m.OUTQUANTITY," + form["OUTQUANTITY"] + " ) > 0 ";
            sql += form["INSPECTIONQUANTITY"] == "" || form["INSPECTIONQUANTITY"] == null ? "" : " AND INSTR( m.INSPECTIONQUANTITY," + form["INSPECTIONQUANTITY"] + " ) > 0 ";
            sql += form["DISPLAYNO"] == "" || form["DISPLAYNO"] == null ? "" : " AND INSTR( m.DISPLAYNO," + form["DISPLAYNO"] + " ) > 0 ";
            sql += form["REMARKS"] == "" || form["REMARKS"] == null ? "" : " AND INSTR( m.REMARKS," + form["REMARKS"] + " ) > 0 ";
            sql += sort != "" ? sort != "INSPECTTYPENAME"?" ORDER BY " + sort + " " + order : "": "";
            sql += ") SELECT * FROM RankedEmployees WHERE rn  BETWEEN " + (page == 0 ? (page - 1) * 10 : (page - 1) * 10 + 1) + " AND " + rows * page;
            DataTable bt = conn.ExecuteDatatable(sql, new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]));
            //添加检验类型中文名称
            bt.Columns.Add("INSPECTTYPENAME", typeof(string));
            //添加大小件
            bt.Columns.Add("CONFIGTYPE", typeof(string));
            //查找主键id
            var columnValues = bt.AsEnumerable().Select(row => row.Field<decimal>("CONFIGID")).ToList();
            //当没有数据时跳出
            if (columnValues.Count == 0)
            {
                return "";
            }
            //依据主键id查找副表TP_PM_FQCCONFIG_DETAIL
            DataTable lbt = conn.ExecuteDatatable(@"SELECT * FROM TP_PM_FQCCONFIG_DETAIL WHERE CONFIGID IN(" + string.Join(",", bt.AsEnumerable().Select(row => row.Field<decimal>("CONFIGID")).ToList()) + ") AND VALUEFLAG = 1");
            //依据检验类型通过逗号分割去查找TP_MST_DATADICTIONARY中的数据
            DataTable dbt = conn.ExecuteDatatable(@"SELECT * FROM TP_MST_DATADICTIONARY WHERE DICTIONARYID IN(" + string.Join(",", bt.AsEnumerable().Select(row => row.Field<string>("INSPECTTYPES")).ToList()) + ") AND VALUEFLAG = 1");
            //循环总数居
            for (int i = 0; i < bt.Rows.Count; i++)
            {
                //查找当前数据的检验类型并按逗号分割
                var xbt = bt.AsEnumerable().Where(ex => ex.Field<decimal>("CONFIGID") == decimal.Parse(bt.Rows[i][1].ToString())).Select(ex => ex.Field<string>("INSPECTTYPES")).FirstOrDefault().Split(',').ToList();
                //根据TP_MST_DATADICTIONARY包含当前检验类型的数据查找检验类型中文名称
                var query = (from row in dbt.AsEnumerable()
                             where xbt.Contains(row.Field<decimal>("DICTIONARYID").ToString())
                             select row).Select(row => row.Field<string>("DICTIONARYVALUE")).ToList();
                //添加检验类型中文名称(将集合按照逗号分割变为字符串)
                bt.Rows[i][16] = string.Join(",", query);
                //添加大小件(根据主键id查找副表第一条的计划类型)
                bt.Rows[i][17] = lbt.AsEnumerable().Where(ex => ex.Field<decimal>("CONFIGID") == decimal.Parse(bt.Rows[i][1].ToString())).Select(row => row.Field<string>("CONFIGTYPE")).FirstOrDefault();
            }
            //检验类型进行排序时
            if (sort != "" && sort == "INSPECTTYPENAME")
            {
                //转化为视图
                DataView dv = new DataView(bt);
                //排序
                dv.Sort = "INSPECTTYPENAME  " + order;
                //将视图转换成表
                bt = dv.ToTable(true, "SID", "CONFIGID", "OUTQUANTITY", "INSPECTIONQUANTITY", "MINOUTQUANTITY", "DISPLAYNO", "REMARKS", "VALUEFLAG", "ACCOUNTID", "CREATETIME", "UPDATETIME", "更新工号", "创建工号", "CLASSES", "rn", "INSPECTTYPENAME", "CONFIGTYPE");

            }
            //查找总数
            DataTable dt = conn.ExecuteDatatable(@"SELECT COUNT(* )FROM TP_PM_FQCCONFIG WHERE  VALUEFLAG = 1");
            return new JsonResult(bt) { total = int.Parse(dt.Rows[0][0].ToString()) }.ToJson();
        }
    }

    /// <summary>
    /// 详细 TP_PM_FQCCONFIG
    /// </summary>
    /// <returns>json</returns>
    private string detail()
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            //通过id查找总数据
            DataTable dt = conn.ExecuteDatatable(@"
                 SELECT 
                    m.CONFIGID,
                    m.INSPECTTYPES, 
                    m.OUTQUANTITY,
                    m.INSPECTIONQUANTITY,
                    m.MINOUTQUANTITY,
                    m.DISPLAYNO,
                    m.REMARKS,
                    m.VALUEFLAG,
                    m.ACCOUNTID,
                    m.CREATEUSERID,
                    m.CREATETIME,
                    m.UPDATEUSERID,
                    CASE WHEN  m.CLASSES = 1 THEN '一天一班' ELSE '一天二班' END AS  CLASSES,
                    m.UPDATETIME
                FROM
                    TP_PM_FQCCONFIG m 
                WHERE 
                    m.VALUEFLAG = '1'
                    AND m.ACCOUNTID = @ACCOUNTID@
                    AND m.CONFIGID = @CONFIGID@
                ",
                new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]),
                new CDAParameter("CONFIGID", HttpContext.Current.Request["id"])
            );
            //通过主表id查找副表数据
            DataTable bt = conn.ExecuteDatatable(@"SELECT * FROM TP_PM_FQCCONFIG_DETAIL WHERE CONFIGID=@CONFIGID@ AND VALUEFLAG = 1", new CDAParameter("CONFIGID", HttpContext.Current.Request["id"]));
            //添加大小件
            dt.Columns.Add("CONFIGTYPE");
            //添加检验类型中文名称
            dt.Columns.Add("INSPECTTYPENAME");
            //通过检验类型id查找检验类型中文名称
            DataTable dbt = conn.ExecuteDatatable(@"SELECT DICTIONARYVALUE FROM TP_MST_DATADICTIONARY WHERE DICTIONARYID IN(" + string.Join(",", dt.AsEnumerable().Select(row => row.Field<string>("INSPECTTYPES")).ToList()) + ") AND VALUEFLAG = 1");
            //定义临时变量
            string linshi = "";
            //循环查找的数据
            for (int i = 0; i < dbt.Rows.Count; i++)
            {
                //进行拼接
                linshi += dbt.Rows[i][0];
            }
            //补齐大小件数据(通过副表查到的数据)
            dt.Rows[0][14] = bt.Rows[0][3];
            //补齐检验类型中文名称
            dt.Rows[0][15] = linshi;
            return new JsonResult(dt).ToJson();
        }
    }

    /// <summary>
    /// 插入 TP_PM_FQCCONFIG
    /// </summary>
    /// <returns>json</returns>
    private string insert(NameValueCollection form)
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            //string primaryKey = conn.GetSequenceNextval("SEQ_TP_PM_FQCCONFIG_ID").ToString();
            //string primaryKey = Guid.NewGuid().ToString().Replace("_", "");
            //查找(提前拿到)下一条id
            DataTable dt = conn.ExecuteDatatable(@"SELECT SEQ_PM_FQCCONFIG_ID.nextval  FROM dual");
            //添加数据
            int result = conn.ExecuteNonQuery(@"
                INSERT INTO TP_PM_FQCCONFIG ( 
                    CONFIGID,
                    OUTQUANTITY,
                    MINOUTQUANTITY,
                    INSPECTIONQUANTITY,
                    DISPLAYNO,
                    REMARKS,
                    ACCOUNTID,CREATEUSERID,UPDATEUSERID,CLASSES,INSPECTTYPES 
                ) VALUES ( 
                    @CONFIGID@,
                    @OUTQUANTITY@,
                    @MINOUTQUANTITY@,
                    @INSPECTIONQUANTITY@,
                    @DISPLAYNO@,
                    @REMARKS@,
                    @ACCOUNTID@,@CREATEUSERID@,@UPDATEUSERID@, @CLASSES@,'" + form["INSPECTTYPES"] + "' ) ",
            new CDAParameter("CONFIGID", dt.Rows[0][0]),
            new CDAParameter("OUTQUANTITY", form["OUTQUANTITY"]),
            new CDAParameter("MINOUTQUANTITY", form["MINOUTQUANTITY"]),
            new CDAParameter("INSPECTIONQUANTITY", form["INSPECTIONQUANTITY"]),
            new CDAParameter("DISPLAYNO", form["DISPLAYNO"]),
            new CDAParameter("REMARKS", form["REMARKS"]),
            new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]),
            new CDAParameter("CREATEUSERID", HttpContext.Current.Session["userId"]),
             //班次
             new CDAParameter("CLASSES", int.Parse(form["CLASSES"])),
            new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"])

        );
            //通过检验类型去添加副表
            foreach (var item in form["INSPECTTYPES"].Split(','))
            {
                int result2 = conn.ExecuteNonQuery(@"
                INSERT INTO TP_PM_FQCCONFIG_DETAIL ( 
                    CONFIGID,
                    INSPECTTYPE,
                    CONFIGTYPE,
                    VALUEFLAG 
                ) VALUES ( 
                    @CONFIGID@,
                    @INSPECTTYPE@,
                    @CONFIGTYPE@,
                    @VALUEFLAG@)",
               new CDAParameter("CONFIGID", dt.Rows[0][0]),
               new CDAParameter("INSPECTTYPE", item),
               new CDAParameter("CONFIGTYPE", form["CONFIGTYPE"]),
               new CDAParameter("VALUEFLAG", 1)
           );
            }
            return new JsonResult(JsonStatus.success).ToJson();
        }
    }

    /// <summary>
    /// 更新 TP_PM_FQCCONFIG
    /// </summary>
    /// <returns>json</returns>
    private string update(NameValueCollection form)
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            //通过主表id删除当前副表所关联的所有值
            int result = conn.ExecuteNonQuery(@"
                UPDATE TP_PM_FQCCONFIG_DETAIL 
                SET VALUEFLAG = 0 WHERE CONFIGID = @CONFIGID@ ",
          new CDAParameter("CONFIGID", HttpContext.Current.Request["id"])
      );
            //更新主表
            int result2 = conn.ExecuteNonQuery(@"
                UPDATE TP_PM_FQCCONFIG 
                SET
                    INSPECTTYPES = '" + form["INSPECTTYPES"] + "'," +
                    "OUTQUANTITY = @OUTQUANTITY@, " +
                    "MINOUTQUANTITY = @MINOUTQUANTITY@," +
                    " INSPECTIONQUANTITY = @INSPECTIONQUANTITY@," +
                    " DISPLAYNO = @DISPLAYNO@," +
                    " REMARKS = @REMARKS@," +
                    " UPDATEUSERID = @UPDATEUSERID@," +
                    " CLASSES = @CLASSES@," +
                    "CONFIGTYPE=@CONFIGTYPE@," +
                    " UPDATETIME = sysdate" +
                    "  WHERE CONFIGID = @CONFIGID@ ",
                new CDAParameter("OUTQUANTITY", form["OUTQUANTITY"]),
                new CDAParameter("MINOUTQUANTITY", form["MINOUTQUANTITY"]),
                new CDAParameter("INSPECTIONQUANTITY", form["INSPECTIONQUANTITY"]),
                new CDAParameter("DISPLAYNO", form["DISPLAYNO"]),
                new CDAParameter("REMARKS", form["REMARKS"]),
                //班次
                new CDAParameter("CLASSES", form["CLASSES"]),
                 //大小件
                 new CDAParameter("CONFIGTYPE", form["CONFIGTYPE"]),
                new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"]),
                new CDAParameter("CONFIGID", HttpContext.Current.Request["id"])
            );
            //通过检验类型去添加副表
            foreach (var item in form["INSPECTTYPES"].Split(','))
            {
                int result3 = conn.ExecuteNonQuery(@"
                INSERT INTO TP_PM_FQCCONFIG_DETAIL ( 
                    CONFIGID,
                    INSPECTTYPE,
                    CONFIGTYPE,
                    VALUEFLAG 
                ) VALUES ( 
                    @CONFIGID@,
                    @INSPECTTYPE@,
                    @CONFIGTYPE@,
                    @VALUEFLAG@)",
               new CDAParameter("CONFIGID", HttpContext.Current.Request["id"]),
               new CDAParameter("INSPECTTYPE", item),
               new CDAParameter("CONFIGTYPE", form["CONFIGTYPE"]),
               new CDAParameter("VALUEFLAG", 1)
           );
            }
            return new JsonResult(JsonStatus.success).ToJson();
        }
    }

    /// <summary>
    /// 删除 TP_PM_FQCCONFIG
    /// </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_FQCCONFIG 
                    WHERE 
                        INSTR(',' || @CONFIGID@ || ',' , ',' || CONFIGID || ',') > 0 
                    ",
                    new CDAParameter("CONFIGID", HttpContext.Current.Request["id"])
                );
                int result2 = conn.ExecuteNonQuery(@"
                    DELETE 
                        TP_PM_FQCCONFIG_DETAIL 
                    WHERE 
                        INSTR(',' || @CONFIGID@ || ',' , ',' || CONFIGID || ',') > 0 
                    ",
                new CDAParameter("CONFIGID", HttpContext.Current.Request["id"])
            );
                return new JsonResult(JsonStatus.success).ToJson();
            }
            else
            {
                return new JsonResult(JsonStatus.otherError).ToJson();
            }
        }
    }

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

    private class Button
    {
        public bool fqcconfigButtonIndex = false;
        public bool fqcconfigButtonInsert = false;
        public bool fqcconfigButtonInsertBatch = false;
        public bool fqcconfigButtonCopy = false;
        public bool fqcconfigButtonUpdate = false;
        public bool fqcconfigButtonDelete = false;
        public bool fqcconfigButtonCancel = false;
        public bool fqcconfigButtonSearch = false;
        public bool fqcconfigButtonDetail = false;
        public bool fqcconfigButtonCheckbox = false;
        public bool fqcconfigButtonExport = false;
        public bool fqcconfigButtonReload = false;
    }

    private class xRecord
    {
        public string sid { get; set; }
        public string INSPECTTYPE { get; set; }
        public string OUTQUANTITY { get; set; }
        public string INSPECTIONQUANTITY { get; set; }
        public string DISPLAYNO { get; set; }
        public string REMARKS { get; set; }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}