HEGII.ss.mes.web/wwwroot/mes/pm/radtestformgoods/testformgoods.ashx

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

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_TESTFORM2_GOODS
/// xuwei create 2021-08-31
/// </summary>
public class testformgoods : 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.testformgoodsButtonIndex = true;
                b.testformgoodsButtonInsert = true;
                b.testformgoodsButtonInsertBatch = true;
                b.testformgoodsButtonCopy = true;
                b.testformgoodsButtonUpdate = true;
                b.testformgoodsButtonDelete = true;
                b.testformgoodsButtonCancel = true;
                b.testformgoodsButtonSearch = true;
                b.testformgoodsButtonDetail = true;
                b.testformgoodsButtonCheckbox = true;
                b.testformgoodsButtonExport = true;
                b.testformgoodsButtonReload = true;
            };
            switch (context.Request["m"].ToString().ToLower())
            {
                case "b":
                    {
                        //按钮
                        context.Response.Write(new JsonResult(b).ToJson());
                        break;
                    }
                case "a":
                    {
                        //添加
                        //if (b.testformgoodsButtonDetail && context.Request["id"] is object)
                        if (b.testformgoodsButtonDetail)
                            context.Response.Write(detail());
                        else
                        {
                            List<xRecord> list = new List<xRecord>();
                            xRecord r = new xRecord();
                            list.Add(r);
                            context.Response.Write(new JsonResult(list).ToJson());
                        }
                        break;
                    }
                case "s":
                    {
                        //搜索
                        if (b.testformgoodsButtonIndex)
                            context.Response.Write(search(context.Request.Form));
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "t":
                    {
                        //详细
                        if (b.testformgoodsButtonDetail)
                            context.Response.Write(detail());
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "i":
                    {
                        //插入
                        if (b.testformgoodsButtonInsert)
                            context.Response.Write(insert(context.Request.Form));
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "u":
                    {
                        //修改
                        if (b.testformgoodsButtonUpdate)
                            context.Response.Write(update(context.Request.Form));
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "d":
                    {
                        //删除
                        if (b.testformgoodsButtonDelete)
                            context.Response.Write(delete());
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "e":
                    {
                        //导出
                        if (b.testformgoodsButtonExport)
                        {
                            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_TESTFORM2_GOODS 查询
    /// </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 right = "ALL";
            string sqlStr = @"
            WITH 
            --取测试产品
            GOODS AS
            (
	            SELECT
		            TF.NAME,
		            TFG.GUID,
		            TFG.TESTFORMGUID,
		            TFG.BARCODE,
		            TFG.CREATEUSERID,
		            TFG.CREATETIME
	            FROM
		            TP_PM_TESTFORM2_GOODS TFG
		            INNER JOIN TP_PM_TESTFORM2 TF ON TFG.TESTFORMGUID = TF.ID
	            WHERE
		            TFG.VALUEFLAG = 1
		            AND TF.VALUEFLAG = 1
		            AND TFG.TESTFORMGUID = @TESTFORMGUID@
            ),
            --取注浆信息
            GROUTING AS 
            (
	            SELECT
		            GDD.BARCODE,
		            GDD.GOODSCODE,
		            GLT.GOODSLEVELTYPENAME,
		            GDD.GROUTINGDATE,
		            GDD.GROUTINGLINECODE,
		            GDD.MOULDCODE,
		            GDD.USERCODE,
		            GDD.DELIVERTIME,
		            GDD.SCRAPFLAG,
		            GDD.SPECIALREPAIRFLAG 
	            FROM
		            GOODS
		            LEFT JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GOODS.BARCODE = GDD.BARCODE
		            LEFT JOIN TP_SYS_GOODSLEVELTYPE GLT ON GDD.GOODSLEVELTYPEID = GLT.GOODSLEVELTYPEID 
	            ),
            --取生产数据
            PRODUCTIONDATA AS 
            (
	            SELECT 
		            P.*
	            FROM
		            GOODS G
		            LEFT JOIN TP_PM_PRODUCTIONDATA P ON P.BARCODE = G.BARCODE
	            WHERE
		            P.VALUEFLAG = 1
            ),
            --取工序信息
            PROCEDUREDATA AS 
            (
	            SELECT 
		            P.PRODUCTIONDATAID,
		            P.BARCODE,
		            P.PROCEDURENAME,
		            P.USERCODE,
		            P.ISREFIRE,
		            P.ISREWORKED
	            FROM
		            PRODUCTIONDATA P
	            WHERE
		            P.PRODUCTIONDATAID IN (SELECT MAX( MID.PRODUCTIONDATAID ) FROM PRODUCTIONDATA MID GROUP BY MID.BARCODE)
	            GROUP BY
		            P.PRODUCTIONDATAID,
		            P.BARCODE,
		            P.PROCEDURENAME,
		            P.USERCODE,
		            P.ISREFIRE,
		            P.ISREWORKED
            ),
            --取半检信息（一检 88：3#交坯 91：3#毛坯库入）
            SEMICHECK1 AS
            (
	            SELECT 
		            S.BARCODE,
		            LISTAGG(TO_CHAR(D.DEFECTNAME) || '_' ||TO_CHAR(D.DEFECTPOSITIONNAME), ',') WITHIN GROUP (ORDER BY S.BARCODE) AS DEFECTNAME
	            FROM 
		            GOODS G
		            LEFT JOIN TP_PM_SEMICHECK S ON S.BARCODE = G.BARCODE
		            LEFT JOIN TP_PM_SEMICHECKDEFECT D ON D.SEMICHECKID = S.SEMICHECKID
	            WHERE
		            S.VALUEFLAG = 1
		            AND D.VALUEFLAG = 1
                    AND S.PROCEDUREID IN (88,91)
	            GROUP BY
		            S.BARCODE
            ),
            --取半检信息（二检 117：3#二检[半检]）
            SEMICHECK2 AS
            (
	            SELECT 
		            S.BARCODE,
		            LISTAGG(TO_CHAR(D.DEFECTNAME) || '_' ||TO_CHAR(D.DEFECTPOSITIONNAME), ',') WITHIN GROUP (ORDER BY S.BARCODE) AS DEFECTNAME
	            FROM 
		            GOODS G
		            LEFT JOIN TP_PM_SEMICHECK S ON S.BARCODE = G.BARCODE
		            LEFT JOIN TP_PM_SEMICHECKDEFECT D ON D.SEMICHECKID = S.SEMICHECKID
	            WHERE
		            S.VALUEFLAG = 1
		            AND D.VALUEFLAG = 1
                    AND S.PROCEDUREID IN (117)
	            GROUP BY
		            S.BARCODE
            ),
            --取成检信息
            PRODUCTIONCHECK AS 
            (
	            SELECT 
		            P.BARCODE,
		            LISTAGG(TO_CHAR(D.DEFECTNAME) || '_' ||TO_CHAR(D.DEFECTPOSITIONNAME), ',') WITHIN GROUP (ORDER BY P.BARCODE) AS DEFECTNAME
	            FROM
		            GOODS G
		            --3#质量登记 105
		            INNER JOIN PRODUCTIONDATA P ON G.BARCODE = P.BARCODE AND P.PROCEDUREID = 105
		            LEFT JOIN TP_SYS_GOODSLEVELTYPE GLT ON P.GOODSLEVELTYPEID = GLT.GOODSLEVELTYPEID 
		            LEFT JOIN TP_PM_DEFECT D ON P.PRODUCTIONDATAID = D.PRODUCTIONDATAID
	            WHERE
		            D.VALUEFLAG = 1
	            GROUP BY
		            P.BARCODE
            ),
            --取入窑信息
            KILN AS 
            (
	            SELECT 
		            P.BARCODE,
		            P.KILNCODE,
		            P.KILNCARCODE,
		            P.CREATETIME
	            FROM
		            GOODS G 
		            --3#入窑 102
		            INNER JOIN PRODUCTIONDATA P ON G.BARCODE = P.BARCODE AND P.PROCEDUREID = 102
            )

            SELECT
                DISTINCT
	            G.GUID,
	            G.GUID AS SID,
	            G.TESTFORMGUID,
	            G.NAME,
	            GR.GOODSCODE,
	            GR.GOODSLEVELTYPENAME AS LASTLEVEL,
	            GR.GROUTINGDATE,
	            GR.GROUTINGLINECODE,
	            GR.MOULDCODE,
	            GR.DELIVERTIME,
	            GR.SCRAPFLAG,
	            GR.SPECIALREPAIRFLAG,
	            P.PRODUCTIONDATAID,
	            P.BARCODE,
	            P.PROCEDURENAME,
	            P.USERCODE,
	            P.ISREFIRE,
	            P.ISREWORKED,
	            K.KILNCODE,
	            K.KILNCARCODE,
	            K.CREATETIME AS KILNCREATETIME,
	            SC1.DEFECTNAME AS SEMICHECK1DEFECT,
	            SC2.DEFECTNAME AS SEMICHECK2DEFECT,
	            PC.DEFECTNAME AS CHECKDEFECT
            FROM
	            GOODS G
	            LEFT JOIN GROUTING GR ON GR.BARCODE = G.BARCODE
	            LEFT JOIN SEMICHECK1 SC1 ON SC1.BARCODE = G.BARCODE
	            LEFT JOIN SEMICHECK2 SC2 ON SC2.BARCODE = G.BARCODE
	            LEFT JOIN PROCEDUREDATA P ON P.BARCODE = G.BARCODE
	            LEFT JOIN PRODUCTIONCHECK PC ON PC.BARCODE = G.BARCODE
	            LEFT JOIN KILN K ON K.BARCODE = G.BARCODE
            ";

            List<CDAParameter> sqlPara = new List<CDAParameter>();
            sqlPara.Add(new CDAParameter("TESTFORMGUID", HttpContext.Current.Request["TESTFORMGUID"]));
            //sqlPara.Add(new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]));
            //sqlPara.Add(new CDAParameter("CREATEUSERID", HttpContext.Current.Session["userId"]));
            //暂时开放所有权限
            //sqlPara.Add(new CDAParameter("RIGHT", right));

            //if(!string.IsNullOrEmpty(form["GUID"]))
            //{
            //    sqlStr += " AND m.GUID = @GUID@ ";
            //    sqlPara.Add(new CDAParameter("GUID", form["GUID"]));
            //}
            //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_TESTFORM2_GOODS
    /// </summary>
    /// <returns>json</returns>
    private string detail()
    {
        using(IDataAccess conn= DataAccess.Create())
        {
            DataTable dt = conn.ExecuteDatatable(@"
               SELECT 
                    m.GUID,
                    m.TESTFORMGUID,
                    m.BARCODE,
                    m.VALUEFLAG,
                    m.ACCOUNTID,
                    m.CREATEUSERID,
                    m.CREATETIME,
                    m.UPDATEUSERID,
                    m.UPDATETIME
                FROM
                    TP_PM_TESTFORM2_GOODS m
                    LEFT JOIN 
                    TP_PM_GROUTINGDAILYDETAIL GDD
                    ON m.BARCODE = GDD.BARCODE
                WHERE 
                    m.VALUEFLAG = '1'
                    AND m.ACCOUNTID = @ACCOUNTID@
                    AND m.GUID = @GUID@
                ",
                new CDAParameter("ACCOUNTID",HttpContext.Current.Session["accountId"]),
                new CDAParameter("GUID",HttpContext.Current.Request["id"])
            );
            return new JsonResult(dt).ToJson();
        }
    }

    /// <summary>
    /// 插入 TP_PM_TESTFORM2_GOODS
    /// </summary>
    /// <returns>json</returns>
    private string insert(NameValueCollection form)
    {
        using(IDataAccess conn= DataAccess.Create())
        {
            //添加产品处理
            object result0 = conn.ExecuteScalar(@"
                SELECT DISTINCT 1 FROM TP_PM_TESTFORM2_GOODS WHERE TESTFORMGUID = @TESTFORMID@ AND BARCODE =@BARCODE@ AND VALUEFLAG = 1
                ",
                new CDAParameter("BARCODE", form["BARCODE"]),
                new CDAParameter("TESTFORMID", form["TESTFORMGUID"])
            );
            string str = result0 + "";
            if (str == "1")
            {
                return new JsonResult(JsonStatus.otherError){ success = false, message = "条码重复", total = 0, rows = "" }.ToJson();
            }
            object result1 = conn.ExecuteScalar(@"
                SELECT DISTINCT 1 FROM TP_PM_GROUTINGDAILYDETAIL WHERE BARCODE =@BARCODE@ AND VALUEFLAG = 1
                ",
              new CDAParameter("BARCODE", form["BARCODE"])
          ) ;
            string str1 = result1+ "";
            if (str1 == "")
            {
                return new JsonResult(JsonStatus.otherError){ success = false, message = "条码不存在", total = 0, rows = "" }.ToJson();
            }

            //string primaryKey = conn.GetSequenceNextval("SEQ_TP_PM_TESTFORM2_GOODS_ID").ToString();
            //string primaryKey = Guid.NewGuid().ToString().Replace("_", "");
            int result = conn.ExecuteNonQuery(@"
                INSERT INTO TP_PM_TESTFORM2_GOODS (
                    TESTFORMGUID,
                    BARCODE,
                    ACCOUNTID,CREATEUSERID,UPDATEUSERID
                ) VALUES (
                    @TESTFORMGUID@,
                    @BARCODE@,
                    @ACCOUNTID@,@CREATEUSERID@,@UPDATEUSERID@
                )
                ",
                //new CDAParameter("GUID",primaryKey),
                new CDAParameter("TESTFORMGUID",form["TESTFORMGUID"]),
                new CDAParameter("BARCODE",form["BARCODE"]),
                new CDAParameter("ACCOUNTID",HttpContext.Current.Session["accountId"]),
                new CDAParameter("CREATEUSERID",HttpContext.Current.Session["userId"]),
                new CDAParameter("UPDATEUSERID",HttpContext.Current.Session["userId"])
            );
            int resultup = conn.ExecuteNonQuery(@"
                UPDATE TP_PM_GROUTINGDAILYDETAIL 
                SET
                    TESTFORMFLAG =2
                WHERE 
                     BARCODE = @BARCODE@
                ",
            new CDAParameter("BARCODE",form["BARCODE"])
        );
            return new JsonResult(JsonStatus.success).ToJson();
        }
    }

    /// <summary>
    /// 更新 TP_PM_TESTFORM2_GOODS
    /// </summary>
    /// <returns>json</returns>
    private string update(NameValueCollection form)
    {
        using(IDataAccess conn=DataAccess.Create())
        {
            int result = conn.ExecuteNonQuery(@"
                UPDATE TP_PM_TESTFORM2_GOODS 
                SET
                    TESTFORMGUID = @TESTFORMGUID@,
                    BARCODE = @BARCODE@,
                    UPDATEUSERID = @UPDATEUSERID@,
                    UPDATETIME = sysdate
                WHERE 
                    GUID = @GUID@
                ",
                new CDAParameter("TESTFORMGUID",form["TESTFORMGUID"]),
                new CDAParameter("BARCODE",form["BARCODE"]),
                new CDAParameter("UPDATEUSERID",HttpContext.Current.Session["userId"]),
                new CDAParameter("GUID",HttpContext.Current.Request["id"])
            );
            return new JsonResult(JsonStatus.success).ToJson();
        }
    }

    /// <summary>
    /// 删除 TP_PM_TESTFORM2_GOODS
    /// </summary>
    /// <returns>json</returns>
    private string delete()
    {
        using(IDataAccess conn= DataAccess.Create())
        {
            if (HttpContext.Current.Request["id"] is object)
            {
               string guidStr = "'" + HttpContext.Current.Request["id"].Replace(",", "','") + "'";
                DataTable dt = conn.ExecuteDatatable(@"
               SELECT 
                BARCODE
                FROM
                     TP_PM_TESTFORM2_GOODS
                WHERE 
                    GUID IN ( {GUID} )
                ".Replace("{GUID}", guidStr)
   );
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    int resultup = conn.ExecuteNonQuery(@"
                UPDATE TP_PM_GROUTINGDAILYDETAIL 
                SET
                    TESTFORMFLAG =0
                WHERE 
                     BARCODE = @BARCODE@
                ",
                  new CDAParameter("BARCODE", dt.Rows[i]["BARCODE"])
              );
                }
                //int result = conn.ExecuteNonQuery(@"
                //    DELETE 
                //        TP_PM_TESTFORM2_GOODS 
                //    WHERE 
                //        INSTR(',' || @GUID@ || ',' , ',' || GUID || ',') > 0 
                //    ",
                //    new CDAParameter("GUID", HttpContext.Current.Request["id"])
                //);
                int result = conn.ExecuteNonQuery(@"
                    DELETE 
                        TP_PM_TESTFORM2_GOODS 
                    WHERE 
                        GUID IN ( {GUID} )
                    ".Replace("{GUID}", guidStr)
                );
                return new JsonResult(JsonStatus.success).ToJson();
            }
            else
            {
                return new JsonResult(JsonStatus.otherError).ToJson();
            }
        }
    }

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

    private class Button
    {
        public bool testformgoodsButtonIndex = false;
        public bool testformgoodsButtonInsert = false;
        public bool testformgoodsButtonInsertBatch = false;
        public bool testformgoodsButtonCopy = false;
        public bool testformgoodsButtonUpdate = false;
        public bool testformgoodsButtonDelete = false;
        public bool testformgoodsButtonCancel = false;
        public bool testformgoodsButtonSearch = false;
        public bool testformgoodsButtonDetail = false;
        public bool testformgoodsButtonCheckbox = false;
        public bool testformgoodsButtonExport = false;
        public bool testformgoodsButtonReload = false;
    }

    private class xRecord
    {
        public string sid { get; set; }
        public string TESTFORMGUID { get; set; }
        public string BARCODE { get; set; }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}