HEGII.ss.mes.web/wwwroot/mes/mst/CommonModelGood/goodscommonmodel.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 = 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"] != 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;
                    }
                case "o":
                    {
                        context.Response.Write(getOUTLETDISTANCE());
                        break;
                    }
                case "logo":
                    {
                        context.Response.Write(getPLATELIMITNUM());
                        break;
                    }
                case "lo":
                    {
                        context.Response.Write(getLogo());
                        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
                TMCM.COMMONGOODSID SID,
                TMCM.GOODSCODE,
                TML.LOGONAME,
                TMCM.OUTLETDISTANCE,
                TMCM.MATERIALCODE,
                TMCM.PLATELIMITNUM1,
                TMCM.PLATELIMITNUM2,
                TMCM.COMMONFLAG,
                CASE WHEN TO_CHAR(TMCM.EFFECTIVETIME,'yyyy') = '2000' THEN NULL ELSE TMCM.EFFECTIVETIME END EFFECTIVETIME,
				CASE WHEN TO_CHAR(TMCM.ENDTIME,'yyyy') = '2099' THEN NULL ELSE TMCM.ENDTIME END ENDTIME
                FROM TP_MST_COMMONMODEL TMCM
                LEFT JOIN TP_MST_LOGO TML ON TMCM.LOGOID = TML.LOGOID
             WHERE
	                TMCM.VALUEFLAG = '1'
	                AND TMCM.ACCOUNTID = @ACCOUNTID@
            ";
            List<CDAParameter> sqlPara = new List<CDAParameter>();
            sqlPara.Add(new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]));

            if (!string.IsNullOrEmpty(form["GOODS"]))
            {
                sqlStr += " AND instr(','||@GOODS@||',',','||TMCM.GOODSID||',')>0 ";
                sqlPara.Add(new CDAParameter("GOODS", form["GOODS"]));
            }
            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();
        }
    }

    private string getLogo()
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            DataTable dt = conn.ExecuteDatatable(@"
                SELECT
                DISTINCT
                T.LOGOID LOGOID,
                T.LOGONAME LOGONAME
                FROM(
                SELECT
	                G.LOGOID,
	                TML.LOGONAME 
                FROM
	                TP_MST_GOODS G
	                LEFT JOIN TP_MST_LOGO TML ON G.LOGOID = TML.LOGOID 
                WHERE
	                G.GOODSID = @GOODSID@ UNION ALL
                SELECT
	                GLS.LOGOID,
	                L.LOGONAME 
                FROM
	                TP_MST_GOODSLOGOSAP GLS
	                INNER JOIN TP_MST_LOGO L ON L.LOGOID = GLS.LOGOID 
                WHERE
	                GLS.GOODSID = @GOODSID@ 
	                ) T
                ",
                new CDAParameter("GOODSID", HttpContext.Current.Request["goodsid"])
            );
            return new JsonResult(dt).ToJson();
        }
    }

    private string getOUTLETDISTANCE()
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            DataTable dt = conn.ExecuteDatatable(@"
                SELECT OUTLETDISTANCE FROM TP_MST_GOODS WHERE GOODSCODE = @GOODSCODE@
                ",
            new CDAParameter("GOODSCODE", HttpContext.Current.Request["goodsid"])
        );
            string jsonStr = new JsonResult(dt).ToJson();
            return jsonStr;
        }
    }

    private string getPLATELIMITNUM()
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            DataTable dt = conn.ExecuteDatatable(@"
                SELECT
	                PLATELIMITNUM,
	                PLATELIMITNUMNEW,
	                MATERIALCODE
                FROM
	                (
	                SELECT
		                1 id,
		                SAP.GOODSID,
		                SAP.LOGOID,
		                SAP.GOODSCODE,
		                SAP.MATERIALCODE,
		                SAP.PLATELIMITNUM,
		                SAP.PLATELIMITNUMNEW 
	                FROM
		                TP_MST_GOODSLOGOSAP SAP 
	                WHERE
		                GOODSID = (SELECT GOODSID FROM TP_MST_GOODS WHERE GOODSCODE = @GOODSCODE@)
		                AND LOGOID = (SELECT LOGOID FROM TP_MST_LOGO WHERE LOGONAME = @LOGONAME@)
                    UNION
	                SELECT
		                2 id,
		                G.GOODSID,
		                G.LOGOID,
		                G.GOODSCODE,
		                G.MATERIALCODE,
		                G.PLATELIMITNUM,
		                G.PLATELIMITNUMNEW 
	                FROM
		                TP_MST_GOODS G 
	                WHERE
		                GOODSID = (SELECT GOODSID FROM TP_MST_GOODS WHERE GOODSCODE = @GOODSCODE@)
		                AND LOGOID = (SELECT LOGOID FROM TP_MST_LOGO WHERE LOGONAME = @LOGONAME@)
	                ORDER BY
		                GOODSCODE,
		                LOGOID DESC 
	                ) 
                WHERE
	                ROWNUM < 2 
                ORDER BY
	                id DESC
                ",
            new CDAParameter("GOODSCODE", HttpContext.Current.Request["goodsid"]),
            new CDAParameter("LOGONAME", HttpContext.Current.Request["logoid"])
        );
            string jsonStr = new JsonResult(dt).ToJson();
            return jsonStr;
        }
    }

    /// <summary>
    /// 详细 TP_MST_WORKSTATIONUSER
    /// </summary>
    /// <returns>json</returns>
    private string detail()
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            DataTable dt = conn.ExecuteDatatable(@"
                SELECT 
                TMCM.COMMONGOODSID SID,
                TMCM.GOODSCODE GOODS,
                TML.LOGONAME,
                TMCM.OUTLETDISTANCE,
                TMCM.MATERIALCODE,
                TMCM.PLATELIMITNUM1,
                TMCM.PLATELIMITNUM2,
                TMCM.COMMONFLAG,
                TMCM.EFFECTIVETIME,
                TMCM.ENDTIME
                FROM TP_MST_COMMONMODEL TMCM
                LEFT JOIN TP_MST_LOGO TML ON TMCM.LOGOID = TML.LOGOID
                WHERE
                 TMCM.COMMONGOODSID = @COMMONGOODSID@
                 AND TMCM.ACCOUNTID = @ACCOUNTID@
                ",
                new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]),
                new CDAParameter("COMMONGOODSID", HttpContext.Current.Request["id"])
            );
            return new JsonResult(dt).ToJson();
        }
    }

    /// <summary>
    /// 插入 TP_MST_WORKSTATIONUSER
    /// </summary>
    /// <returns>json</returns>
    private string insert(NameValueCollection form)
    {
        var EFFECTIVETIME = "";
        var ENDTIME = "";
        using (IDataAccess conn = DataAccess.Create())
        {
            //判断是否存在
            string count = conn.ExecuteScalar(@"
              SELECT
              count(*)
            FROM
              TP_MST_COMMONMODEL 
            WHERE
              VALUEFLAG = '1'
	          AND GOODSID = @GOODSID@
	          AND LOGOID = @LOGOID@
            "
            , new CDAParameter("GOODSID", form["GOODS"]),
            new CDAParameter("LOGOID", form["LOGONAME"])
            ).ToString();
            try
            {
                if (count == "0")
                {
                    int result = 0;
                    if (form["EFFECTIVETIME"] == "")
                    {
                        EFFECTIVETIME = "2000-01-01";
                    }
                    else
                    {
                        EFFECTIVETIME = form["EFFECTIVETIME"];
                    }
                    if (form["ENDTIME"] == "")
                    {
                        ENDTIME = "2099-12-31";
                    }
                    else
                    {
                        ENDTIME = form["ENDTIME"];
                    }
                    result = conn.ExecuteNonQuery(@"
               INSERT INTO TP_MST_COMMONMODEL 
                    (GOODSCODE,GOODSID,LOGOID,OUTLETDISTANCE,MATERIALCODE,PLATELIMITNUM1,PLATELIMITNUM2,COMMONFLAG,EFFECTIVETIME,ENDTIME,CREATEUSERID,UPDATEUSERID,ACCOUNTID)
                    VALUES
	                (
	                (SELECT GOODSCODE FROM TP_MST_GOODS WHERE GOODSID = @GOODSID@),
	                @GOODSID@,
	                @LOGONAME@,
	                @OUTLETDISTANCE@,
	                @MATERIALCODE@,
	                @PLATELIMITNUM1@,
	                @PLATELIMITNUM2@,
	                @COMMONFLAG@,
	                @EFFECTIVETIME@,
	                @ENDTIME@,
	                @CREATEUSERID@,
	                @UPDATEUSERID@,
	                @ACCOUNTID@
	                )",
                    new CDAParameter("GOODSID", form["GOODS"]),
                    new CDAParameter("LOGONAME", form["LOGONAME"]),
                    new CDAParameter("OUTLETDISTANCE", form["OUTLETDISTANCE"]),
                    new CDAParameter("MATERIALCODE", form["MATERIALCODE"]),
                    new CDAParameter("PLATELIMITNUM1", form["PLATELIMITNUM1"]),
                    new CDAParameter("PLATELIMITNUM2", form["PLATELIMITNUM2"]),
                    new CDAParameter("COMMONFLAG", form["COMMONFLAG"]),
                    new CDAParameter("EFFECTIVETIME", EFFECTIVETIME, DataType.DateTime),
                    new CDAParameter("ENDTIME", ENDTIME, DataType.DateTime),
                    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)
    {
             var EFFECTIVETIME = "";
        var ENDTIME = "";
        using (IDataAccess conn = DataAccess.Create())
        {
                 if (form["EFFECTIVETIME"] == "")
                    {
                        EFFECTIVETIME = "2000-01-01";
                    }
                    else
                    {
                        EFFECTIVETIME = form["EFFECTIVETIME"];
                    }
                    if (form["ENDTIME"] == "")
                    {
                        ENDTIME = "2099-12-31";
                    }
                    else
                    {
                        ENDTIME = form["ENDTIME"];
                    }
            int result = conn.ExecuteNonQuery(@"
                UPDATE TP_MST_COMMONMODEL 
                SET
                    GOODSCODE = @GOODSCODE@,
                    GOODSID = (SELECT GOODSID FROM TP_MST_GOODS WHERE GOODSCODE = @GOODSCODE@),
                    LOGOID = (SELECT LOGOID FROM TP_MST_LOGO WHERE LOGONAME = @LOGOID@),
                    OUTLETDISTANCE = @OUTLETDISTANCE@,
                    MATERIALCODE = @MATERIALCODE@,
                    PLATELIMITNUM1 = @PLATELIMITNUM1@,
                    PLATELIMITNUM2 = @PLATELIMITNUM2@,
                    COMMONFLAG = @COMMONFLAG@,
                    EFFECTIVETIME = @EFFECTIVETIME@,
                    ENDTIME = @ENDTIME@,
                    UPDATEUSERID = @UPDATEUSERID@,
                    UPDATETIME = sysdate
                WHERE 
                    COMMONGOODSID = @COMMONGOODSID@
                ",
                new CDAParameter("GOODSCODE", form["GOODS"]),
                new CDAParameter("LOGOID", form["LOGONAME"]),
                new CDAParameter("OUTLETDISTANCE", form["OUTLETDISTANCE"]),
                new CDAParameter("MATERIALCODE", form["MATERIALCODE"]),
                new CDAParameter("PLATELIMITNUM1", form["PLATELIMITNUM1"]),
                new CDAParameter("PLATELIMITNUM2", form["PLATELIMITNUM2"]),
                new CDAParameter("COMMONFLAG", form["COMMONFLAG"]),
                new CDAParameter("EFFECTIVETIME", EFFECTIVETIME, DataType.DateTime),
                new CDAParameter("ENDTIME", ENDTIME, DataType.DateTime),
                new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"]),
                new CDAParameter("COMMONGOODSID", 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;
        }
    }

}