HEGII.ss.mes.web/wwwroot/mes/mst/workstationuser/workstationuser.ashx

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

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_MST_WORKSTATIONUSER
/// xuwei create 2020-05-25
/// </summary>
public class workstationuser : IHttpHandler, IRequiresSessionState
{
    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 = false;
                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"] != null && 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 "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_MST_WORKSTATIONUSER 查询
    /// </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.WORKSTATIONUSERID AS SID,
                    m.WORKSTATIONUSERID,
                    m.WORKSTATIONID,
                    w.WORKSTATIONNAME,
                    m.USERID,
                    m.USERCODE,
                    w.UJOBSID,
                    j.JOBSNAME,
                    m.REMARKS,
                    m.ORGANIZATIONID,
                    o.ORGANIZATIONNAME,
                    m.VALUEFLAG,
                    m.ACCOUNTID,
                    m.CREATEUSERID,
                    m.CREATETIME,
                    m.UPDATEUSERID,
                    m.UPDATETIME
                FROM
                    TP_MST_WORKSTATIONUSER m
                    LEFT JOIN TP_MST_WORKSTATION w ON w.WORKSTATIONID = m.WORKSTATIONID
                    LEFT JOIN TP_MST_ORGANIZATION o ON o.ORGANIZATIONID = m.ORGANIZATIONID
                    LEFT JOIN TP_MST_JOBS j ON j.JOBSID = w.UJOBSID
                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["WORKSTATIONUSERID"]))
            {
                sqlStr += " AND m.WORKSTATIONUSERID = @WORKSTATIONUSERID@ ";
                sqlPara.Add(new CDAParameter("WORKSTATIONUSERID", form["WORKSTATIONUSERID"]));
            }
            if (!string.IsNullOrEmpty(form["WORKSTATIONID"]))
            {
                sqlStr += " AND m.WORKSTATIONID = @WORKSTATIONID@ ";
                sqlPara.Add(new CDAParameter("WORKSTATIONID", form["WORKSTATIONID"]));
            }
            if (!string.IsNullOrEmpty(form["USERID"]))
            {
                sqlStr += " AND INSTR( m.USERID, @USERID@ ) > 0 ";
                sqlPara.Add(new CDAParameter("USERID", form["USERID"]));
            }
            if (!string.IsNullOrEmpty(form["USERCODE"]))
            {
                sqlStr += " AND INSTR( m.USERCODE, @USERCODE@ ) > 0 ";
                sqlPara.Add(new CDAParameter("USERCODE", form["USERCODE"]));
            }
            if (!string.IsNullOrEmpty(form["REMARKS"]))
            {
                sqlStr += " AND INSTR( m.REMARKS, @REMARKS@ ) > 0 ";
                sqlPara.Add(new CDAParameter("REMARKS", form["REMARKS"]));
            }
            if (!string.IsNullOrEmpty(form["ORGANIZATIONID"]))
            {
                sqlStr += " AND INSTR( m.ORGANIZATIONID, @ORGANIZATIONID@ ) > 0 ";
                sqlPara.Add(new CDAParameter("ORGANIZATIONID", form["ORGANIZATIONID"]));
            }
            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_MST_WORKSTATIONUSER
    /// </summary>
    /// <returns>json</returns>
    private string detail()
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            DataTable dt = conn.ExecuteDatatable(@"
                SELECT 
                    m.WORKSTATIONUSERID AS SID,
                    m.WORKSTATIONUSERID,
                    m.WORKSTATIONID,
                    w.WORKSTATIONNAME,
                    m.USERID,
                    m.USERCODE,
                    w.UJOBSID,
                    j.JOBSNAME,
                    m.REMARKS,
                    m.ORGANIZATIONID,
                    o.ORGANIZATIONNAME,
                    m.VALUEFLAG,
                    m.ACCOUNTID,
                    m.CREATEUSERID,
                    m.CREATETIME,
                    m.UPDATEUSERID,
                    m.UPDATETIME
                FROM
                    TP_MST_WORKSTATIONUSER m
                    LEFT JOIN TP_MST_WORKSTATION w ON w.WORKSTATIONID = m.WORKSTATIONID
                    LEFT JOIN TP_MST_ORGANIZATION o ON o.ORGANIZATIONID = m.ORGANIZATIONID
                    LEFT JOIN TP_MST_JOBS j ON j.JOBSID = w.UJOBSID
                WHERE 
                    m.VALUEFLAG = '1'
                    AND m.ACCOUNTID = @ACCOUNTID@
                    AND m.WORKSTATIONUSERID = @WORKSTATIONUSERID@
                ",
                new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]),
                new CDAParameter("WORKSTATIONUSERID", HttpContext.Current.Request["id"])
            );
            return new JsonResult(dt).ToJson();
        }
    }

    /// <summary>
    /// 插入 TP_MST_WORKSTATIONUSER
    /// </summary>
    /// <returns>json</returns>
    private string insert(NameValueCollection form)
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            #region 校验重复
            // ADD BY fubin 2020/6/16
            string ID = form["WORKSTATIONID"];
            string[] ListID = ID.Split(',');
            object isExists = null;
            for (int i = 0; i < ListID.Length; i++)
            {
                 isExists = conn.ExecuteScalar(
                    @"SELECT 1
					FROM TP_MST_WORKSTATIONUSER WSU
				   WHERE WSU.VALUEFLAG = 1
					 AND WSU.USERID = @USERID@
					 AND WSU.WORKSTATIONID = @WORKSTATIONID@",
                    new CDAParameter("USERID", form["USERID"]),
                    new CDAParameter("WORKSTATIONID", ListID[i])
                );
                if (isExists != null)
                {
                    return new JsonResult(JsonStatus.otherError) { message = "当前第" + (i+1) + "工位已绑定" }.ToJson();
                }
            }

            #endregion
            //读取别用户ID
            string userCode = conn.ExecuteScalar(@"
                SELECT 
                    USERCODE 
                FROM 
                    TP_MST_USER 
                WHERE 
                    USERID = @USERID@
            "
            , new CDAParameter("USERID", form["USERID"])
            ).ToString();

            //读取用户所属部门
            string organizationId = conn.ExecuteScalar(@"
                SELECT 
                    o.ORGANIZATIONID 
                FROM 
                    TP_MST_ORGANIZATION o 
                    LEFT JOIN TP_MST_USER u ON o.ORGANIZATIONID = u.ORGANIZATIONID
                WHERE 
                    u.USERID = @USERID@
            "
            , new CDAParameter("USERID", form["USERID"])
            ).ToString();
            try
            {
                int result = 0;
                for (int i = 0; i < ListID.Length; i++) {
                    //string primaryKey = conn.GetSequenceNextval("SEQ_TP_MST_WORKSTATIONUSER").ToString();
                    result = conn.ExecuteNonQuery(@"
                INSERT INTO TP_MST_WORKSTATIONUSER (
                    --WORKSTATIONUSERID,
                    WORKSTATIONID,
                    USERID,
                    USERCODE,
                    REMARKS,
                    ORGANIZATIONID,
                    ACCOUNTID,CREATEUSERID,UPDATEUSERID
                ) VALUES (
                    --@WORKSTATIONUSERID@,
                    @WORKSTATIONID@,
                    @USERID@,
                    @USERCODE@,
                    @REMARKS@,
                    @ORGANIZATIONID@,
                    @ACCOUNTID@,@CREATEUSERID@,@UPDATEUSERID@ ) ",
                    //new CDAParameter("WORKSTATIONUSERID",primaryKey),
                    new CDAParameter("WORKSTATIONID", ListID[i]),
                    new CDAParameter("USERID", form["USERID"]),
                    new CDAParameter("USERCODE", userCode),
                    new CDAParameter("REMARKS", form["REMARKS"]),
                    new CDAParameter("ORGANIZATIONID", organizationId),
                    new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]),
                    new CDAParameter("CREATEUSERID", HttpContext.Current.Session["userId"]),
                    new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"])
                );
                    if (result <= 0) {
                        conn.Transaction.Rollback();
                        return new JsonResult(JsonStatus.error).ToJson();
                    }
                }

            }
            catch (Exception)
            {
                conn.Transaction.Rollback();
                return new JsonResult(JsonStatus.error).ToJson();
            }

            return new JsonResult(JsonStatus.success).ToJson();
        }


    }

    /// <summary>
    /// 更新 TP_MST_WORKSTATIONUSER
    /// </summary>
    /// <returns>json</returns>
    private string update(NameValueCollection form)
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            #region 校验重复
            // ADD BY fubin 2020/6/16
            object isExists = conn.ExecuteScalar(
                @"SELECT 1
					FROM TP_MST_WORKSTATIONUSER WSU
				   WHERE WSU.VALUEFLAG = 1
					 AND WSU.USERID = @USERID@
					 AND WSU.WORKSTATIONID = @WORKSTATIONID@
					 AND WSU.WORKSTATIONUSERID <> @WORKSTATIONUSERID@",
                new CDAParameter("USERID", form["USERID"]),
                new CDAParameter("WORKSTATIONID", form["WORKSTATIONID"]),
                new CDAParameter("WORKSTATIONUSERID", form["WORKSTATIONUSERID"])
            );
            if (isExists != null)
            {
                return new JsonResult(JsonStatus.otherError) { message="当前工号工位已绑定"}.ToJson();
            }
            #endregion

            //读取别用户ID
            string userCode = conn.ExecuteScalar(@"
                SELECT 
                    USERCODE 
                FROM 
                    TP_MST_USER 
                WHERE 
                    USERID = @USERID@
            "
            , new CDAParameter("USERID", form["USERID"])
            ).ToString();

            //读取用户所属部门
            string organizationId = conn.ExecuteScalar(@"
                SELECT 
                    o.ORGANIZATIONID 
                FROM 
                    TP_MST_ORGANIZATION o 
                    LEFT JOIN TP_MST_USER u ON o.ORGANIZATIONID = u.ORGANIZATIONID
                WHERE 
                    u.USERID = @USERID@
            "
            , new CDAParameter("USERID", form["USERID"])
            ).ToString();

            int result = conn.ExecuteNonQuery(@"
                UPDATE TP_MST_WORKSTATIONUSER 
                SET
                    WORKSTATIONID = @WORKSTATIONID@,
                    USERID = @USERID@,
                    USERCODE = @USERCODE@,
                    REMARKS = @REMARKS@,
                    ORGANIZATIONID = @ORGANIZATIONID@,
                    UPDATEUSERID = @UPDATEUSERID@,
                    UPDATETIME = sysdate
                WHERE 
                    WORKSTATIONUSERID = @WORKSTATIONUSERID@
                ",
                new CDAParameter("WORKSTATIONID", form["WORKSTATIONID"]),
                new CDAParameter("USERID", form["USERID"]),
                new CDAParameter("USERCODE", userCode),
                new CDAParameter("REMARKS", form["REMARKS"]),
                new CDAParameter("ORGANIZATIONID", organizationId),
                new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"]),
                new CDAParameter("WORKSTATIONUSERID", HttpContext.Current.Request["id"])
            );
            return new JsonResult(JsonStatus.success).ToJson();
        }
    }

    /// <summary>
    /// 删除 TP_MST_WORKSTATIONUSER
    /// </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_MST_WORKSTATIONUSER 
                    WHERE 
                        INSTR(',' || @WORKSTATIONUSERID@ || ',' , ',' || WORKSTATIONUSERID || ',') > 0 
                    ",
                    new CDAParameter("WORKSTATIONUSERID", HttpContext.Current.Request["id"])
                );
                return new JsonResult(JsonStatus.success).ToJson();
            }
            else
            {
                return new JsonResult(JsonStatus.otherError).ToJson();
            }
        }
    }

    /// <summary>
    /// 导出 TP_MST_WORKSTATIONUSER
    /// </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 btnReload = false;
    }

    private class xRecord
    {
        public string sid { get; set; }
        public string WORKSTATIONID { get; set; }
        public string USERID { get; set; }
        public string USERCODE { get; set; }
        public string REMARKS { get; set; }
        public string ORGANIZATIONID { get; set; }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}