HEGII.ss.mes.web/wwwroot/mes/pm/fqcitems/fqcitemsdetail.ashx

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

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 Newtonsoft.Json;
using System.Collections;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

/// <summary>
/// TP_PM_FQCITEMSDETAIL
/// xuwei create 2023-09-07
/// </summary>
public class fqcitemsdetail : 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.fqcitemsdetailButtonIndex = true;
                b.fqcitemsdetailButtonInsert = false;
                b.fqcitemsdetailButtonInsertBatch = true;
                b.fqcitemsdetailButtonCopy = true;
                b.fqcitemsdetailButtonUpdate = false;
                b.fqcitemsdetailButtonDelete = false;
                b.fqcitemsdetailButtonCancel = false;
                b.fqcitemsdetailButtonSearch = false;
                b.fqcitemsdetailButtonDetail = true;
                b.fqcitemsdetailButtonCheckbox = false;
                b.fqcitemsdetailButtonExport = true;
                b.fqcitemsdetailButtonReload = true;
                b.fqcitemsdetailButtonBatch = true;
                b.fqcitemsdetailButtonImport = true;
            };
            switch (context.Request["m"].ToString().ToLower())
            {
                case "b":
                    {
                        //按钮
                        context.Response.Write(new JsonResult(b).ToJson());
                        break;
                    }
                case "a":
                    {
                        //添加
                        if (b.fqcitemsdetailButtonDetail && context.Request["id"] is object)
                            context.Response.Write(detail());
                        else
                        {
                            List<xRecord> list = new List<xRecord>();
                            xRecord r = new xRecord();
                            if (context.Request["pid"] is object) r.ITEMSID = context.Request["pid"];
                            list.Add(r);
                            context.Response.Write(new JsonResult(list).ToJson());
                        }
                        break;
                    }
                case "s":
                    {
                        //搜索
                        if (b.fqcitemsdetailButtonIndex){
                            string itemsid = context.Request["ITEMSIDS"].ToString();
                            context.Response.Write(search(context.Request.Form,itemsid));
                        } else{
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        }
                        break;

                    }
                case "gs":
                    {
                        //搜索
                        if (b.fqcitemsdetailButtonIndex)
                            context.Response.Write(searchgs(context.Request.Form));
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "t":
                    {
                        //详细
                        if (b.fqcitemsdetailButtonDetail)
                            context.Response.Write(detail());
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "i":
                    {
                        //插入
                        if (b.fqcitemsdetailButtonInsert)
                            context.Response.Write(insert(context.Request.Form));
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "u":
                    {
                        //修改
                        if (b.fqcitemsdetailButtonUpdate)
                            context.Response.Write(update(context.Request.Form));
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "d":
                    {
                        //删除
                        if (b.fqcitemsdetailButtonDelete)
                            context.Response.Write(delete());
                        else
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        break;
                    }
                case "e":
                    {
                        //导出
                        if (b.fqcitemsdetailButtonExport)
                        {
                            context.Response.Write(export());
                        }
                        else
                        {
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        }
                        break;
                    }
                case "batch":
                    {
                        //批量插入修改删除处理
                        if (b.fqcitemsdetailButtonBatch)
                        {
                            context.Response.Write(batch(context.Request["data"], context.Request["id"]));
                        }
                        else
                        {
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        }
                        break;
                    }
                case "copy":
                    {
                        //批量插入修改删除处理
                        if (b.fqcitemsdetailButtonCopy)
                        {
                            context.Response.Write(copy(context.Request["data"], context.Request["INSPECTTYPE"], context.Request["INSPECTNAME"],
                                context.Request["goodstypecodeMaster"], context.Request["INSPECTVERSION"], context.Request["REMARKS"]));
                        }
                        else
                        {
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        }
                        break;
                    }
                case "import":
                    {
                        //批量插入修改删除处理
                        if (b.fqcitemsdetailButtonImport)
                        {   //获取导入的Execl文件 并写入到upload 文件夹下
                            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 + "x");
                            string diskFileName = context.Server.MapPath(fileName);
                            if (System.IO.File.Exists(diskFileName)) System.IO.File.Delete(diskFileName);
                            context.Request.Files[0].SaveAs(diskFileName);
                            //文件转Table
                            var detailTable = ExcelToDatatable(diskFileName);
                            //DataTable detailTable = Import.ExcelToDataTable(diskFileName);
                            int itemsID = Convert.ToInt32(context.Request["id"]);
                            context.Response.Write(Imports(detailTable, itemsID));
                        }
                        else
                        {
                            context.Response.Write(new JsonResult(JsonStatus.rightError).ToJson());
                        }
                        break;
                    }

                default:
                    {
                        break;
                    }
            }
        }
        else
        {
            context.Response.Write(new JsonResult(JsonStatus.loginError).ToJson());
        }
    }

    /// <summary>
    /// 批量插入修改删除处理方法 2023-09-11 xuwei add
    /// </summary>
    /// <param name="jsondata"></param>
    /// <returns></returns>
    private string batch(string jsondata, string sid)
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            conn.BeginTransaction();
            int itemsID = Convert.ToInt32(sid);
            DataTable detailTable = JsonConvert.DeserializeObject<DataTable>(jsondata);

            if (detailTable != null && detailTable.Rows.Count > 0)
            {

                DataTable itemsdetailsTable = conn.ExecuteDatatable(@" SELECT 
                        1
                    FROM
                        TP_PM_FQCITEMSDETAIL m 
                    WHERE 
                        m.VALUEFLAG = '1' 
                        AND m.ITEMSID = @ITEMSID@
                    ",
                new CDAParameter("ITEMSID", itemsID)
                );

                if (itemsdetailsTable != null && itemsdetailsTable.Rows.Count > 0)
                {
                    DataTable itemsTable = conn.ExecuteDatatable(@" SELECT 
                        m.ITEMSID,
                        m.INSPECTTYPE,
                        m.INSPECTTYPE,
                        m.INSPECTNAME,
                        m.GOODSID,
                        m.GOODSCODE,
                        m.INSPECTVERSION,
                        m.REMARKS,
                        m.VALUEFLAG,
                        m.ACCOUNTID
                    FROM
                        TP_PM_FQCITEMS m 
                    WHERE 
                        m.VALUEFLAG = '1' 
                        AND m.ITEMSID = @ITEMSID@
                    ",
                        new CDAParameter("ITEMSID", itemsID)
                    );

                    //产品明细
                    DataTable goodsTable = conn.ExecuteDatatable(@" SELECT 
                        m.ITEMSID, 
                        m.GOODSID,
                        m.GOODSCODE,
                        m.ACCOUNTID,
                        m.REMARKS
                    FROM
                        TP_PM_FQCITEMSGOODS m 
                    WHERE 
                        m.VALUEFLAG = '1' 
                        AND m.ITEMSID = @ITEMSID@
                    ",
                    new CDAParameter("ITEMSID", itemsID)
                    );

                    //根据ID删除之前的版本
                    int result = conn.ExecuteNonQuery(@"
                    UPDATE TP_PM_FQCITEMS 
                    SET
                        VALUEFLAG = 0,
                        UPDATEUSERID = @UPDATEUSERID@,
                        UPDATETIME = sysdate
                    WHERE 
                        ITEMSID = @ITEMSID@
                    ",
                        new CDAParameter("ITEMSID", itemsID),
                        new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"])
                    );

                    //查询原数据，新建一条版本号加一的数据，主表，明细，产品
                    int resultaddItems = conn.ExecuteNonQuery(@"
                    INSERT INTO TP_PM_FQCITEMS (
                        INSPECTTYPE,
                        INSPECTNAME,
                        GOODSCODE,
                        INSPECTVERSION,
                        REMARKS,
                        ACCOUNTID,
                        CREATEUSERID,
                        UPDATEUSERID
                    ) VALUES (
                        @INSPECTTYPE@,
                        @INSPECTNAME@,
                        @GOODSCODE@,
                        @INSPECTVERSION@,
                        @REMARKS@,
                        @ACCOUNTID@,
                        @CREATEUSERID@,
                        @UPDATEUSERID@
                    )
                    ",
                       new CDAParameter("INSPECTTYPE", itemsTable.Rows[0]["INSPECTTYPE"].ToString()),
                       new CDAParameter("INSPECTNAME", itemsTable.Rows[0]["INSPECTNAME"].ToString()),
                       new CDAParameter("GOODSCODE", itemsTable.Rows[0]["GOODSCODE"].ToString()),
                       new CDAParameter("INSPECTVERSION", Convert.ToInt32(itemsTable.Rows[0]["INSPECTVERSION"]) + 1),
                       new CDAParameter("REMARKS", itemsTable.Rows[0]["REMARKS"]),
                       new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]),
                       new CDAParameter("CREATEUSERID", HttpContext.Current.Session["userId"]),
                       new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"])
                   );
                    object newID = conn.ExecuteScalar(@"SELECT SEQ_PM_FQCITEMS_ID.currval from dual");

                    //产品
                    for (int i = 0; i < goodsTable.Rows.Count; i++)
                    {
                        int goodsresult = conn.ExecuteNonQuery(@"
                    INSERT INTO TP_PM_FQCITEMSGOODS ( ITEMSID, GOODSID, GOODSCODE, ACCOUNTID, CREATEUSERID, UPDATEUSERID, REMARKS )
                        VALUES ( @ITEMSID@, @GOODSID@, @GOODSCODE@, @ACCOUNTID@, @CREATEUSERID@, @UPDATEUSERID@, @REMARKS@ )",
                        new CDAParameter("ITEMSID", newID),
                        new CDAParameter("GOODSID", goodsTable.Rows[i]["GOODSID"].ToString()),
                        new CDAParameter("GOODSCODE", goodsTable.Rows[i]["GOODSCODE"].ToString()),
                        new CDAParameter("REMARKS", goodsTable.Rows[i]["REMARKS"]),
                        new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]),
                        new CDAParameter("CREATEUSERID", HttpContext.Current.Session["userId"]),
                        new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"])
                      );
                    }

                    //明细
                    for (int i = 0; i < detailTable.Rows.Count; i++)
                    {
                        int detailresult = conn.ExecuteNonQuery(@"
                        INSERT INTO TP_PM_FQCITEMSDETAIL ( 
                            ITEMSID,
                            INSPECTITEM,
                            INSPECTTOOL,
                            INSPECTMARK,
                            INSPECTWAY,
                            ISINSPECT,
                            INSPECTBASIS,
                            DEFECTGRADE,
                            TECHNICALREQUIREMENT,
                            LOWERLIMIT,
                            UPPERLIMIT,
                            INSPECTCOUNT,
                            ISAVERAGE,  
                            UNITNAME,
                            REMARKS,XUHAO,
                            ACCOUNTID,CREATEUSERID,UPDATEUSERID
                        ) VALUES ( 
                            @ITEMSID@,
                            @INSPECTITEM@,
                           (SELECT DISTINCT T.DICTIONARYID AS ID FROM TP_MST_DATADICTIONARY T WHERE T.VALUEFLAG = 1 AND T.DICTIONARYTYPE = 'TPC022' AND T.DICTIONARYVALUE=@INSPECTTOOL@),
                           (SELECT DISTINCT T.DICTIONARYID AS ID FROM TP_MST_DATADICTIONARY T WHERE T.VALUEFLAG = 1 AND T.DICTIONARYTYPE = 'TPC023' AND T.DICTIONARYVALUE=@INSPECTMARK@),
                            @INSPECTWAY@,
                            @ISINSPECT@,
                            @INSPECTBASIS@,
                            @DEFECTGRADE@,
                            @TECHNICALREQUIREMENT@,
                            @LOWERLIMIT@,
                            @UPPERLIMIT@,
                            @INSPECTCOUNT@,
                            @ISAVERAGE@,
                            @UNITNAME@,
                            @REMARKS@,@XUHAO@,
                            @ACCOUNTID@,@CREATEUSERID@,@UPDATEUSERID@
                        )
                        ",
                           new CDAParameter("ITEMSID", newID),
                           new CDAParameter("INSPECTITEM", detailTable.Rows[i]["INSPECTITEM"]),
                           new CDAParameter("INSPECTTOOL", detailTable.Rows[i]["INSPECTTOOL"].ToString()),
                           new CDAParameter("INSPECTMARK", detailTable.Rows[i]["INSPECTMARK"].ToString()),
                           new CDAParameter("INSPECTWAY", detailTable.Rows[i]["INSPECTWAY"]),
                           new CDAParameter("ISINSPECT", detailTable.Rows[i]["ISINSPECT"]),
                           new CDAParameter("INSPECTBASIS", detailTable.Rows[i]["INSPECTBASIS"]),
                           new CDAParameter("DEFECTGRADE", detailTable.Rows[i]["DEFECTGRADE"]),
                           new CDAParameter("TECHNICALREQUIREMENT", detailTable.Rows[i]["TECHNICALREQUIREMENT"]),
                           new CDAParameter("LOWERLIMIT", detailTable.Rows[i]["LOWERLIMIT"]),
                           new CDAParameter("UPPERLIMIT", detailTable.Rows[i]["UPPERLIMIT"]),
                           new CDAParameter("INSPECTCOUNT", detailTable.Rows[i]["INSPECTCOUNT"]),
                           new CDAParameter("ISAVERAGE", detailTable.Rows[i]["ISAVERAGE"]),
                           new CDAParameter("UNITNAME", detailTable.Rows[i]["UNITNAME"]),
                           new CDAParameter("REMARKS", detailTable.Rows[i]["REMARKS"]),
                           new CDAParameter("XUHAO", detailTable.Rows[i]["XUHAO"]),
                           new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]),
                           new CDAParameter("CREATEUSERID", HttpContext.Current.Session["userId"]),
                           new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"])
                        );
                    }
                }
                else
                {
                    //明细
                    for (int i = 0; i < detailTable.Rows.Count; i++)
                    {
                        int detailresult = conn.ExecuteNonQuery(@"
                        INSERT INTO TP_PM_FQCITEMSDETAIL ( 
                            ITEMSID,
                            INSPECTITEM,
                            INSPECTTOOL,
                            INSPECTMARK,
                            INSPECTWAY,
                            ISINSPECT,
                            INSPECTBASIS,
                            DEFECTGRADE,
                            TECHNICALREQUIREMENT,
                            LOWERLIMIT,
                            UPPERLIMIT,
                            INSPECTCOUNT,
                            ISAVERAGE,  
                            UNITNAME,
                            REMARKS,XUHAO,
                            ACCOUNTID,CREATEUSERID,UPDATEUSERID
                        ) VALUES ( 
                            @ITEMSID@,
                            @INSPECTITEM@,
                           (SELECT DISTINCT T.DICTIONARYID AS ID FROM TP_MST_DATADICTIONARY T WHERE T.VALUEFLAG = 1 AND T.DICTIONARYTYPE = 'TPC022' AND T.DICTIONARYVALUE=@INSPECTTOOL@),
                           (SELECT DISTINCT T.DICTIONARYID AS ID FROM TP_MST_DATADICTIONARY T WHERE T.VALUEFLAG = 1 AND T.DICTIONARYTYPE = 'TPC023' AND T.DICTIONARYVALUE=@INSPECTMARK@),
                            @INSPECTWAY@,
                            @ISINSPECT@,
                            @INSPECTBASIS@,
                            @DEFECTGRADE@,
                            @TECHNICALREQUIREMENT@,
                            @LOWERLIMIT@,
                            @UPPERLIMIT@,
                            @INSPECTCOUNT@,
                            @ISAVERAGE@,
                            @UNITNAME@,
                            @REMARKS@,@XUHAO@,
                            @ACCOUNTID@,@CREATEUSERID@,@UPDATEUSERID@
                        )
                        ",
                           new CDAParameter("ITEMSID", itemsID),
                           new CDAParameter("INSPECTITEM", detailTable.Rows[i]["INSPECTITEM"]),
                           new CDAParameter("INSPECTTOOL", detailTable.Rows[i]["INSPECTTOOL"]),
                           new CDAParameter("INSPECTMARK", detailTable.Rows[i]["INSPECTMARK"]),
                           new CDAParameter("INSPECTWAY", detailTable.Rows[i]["INSPECTWAY"]),
                           new CDAParameter("ISINSPECT", detailTable.Rows[i]["ISINSPECT"]),
                           new CDAParameter("INSPECTBASIS", detailTable.Rows[i]["INSPECTBASIS"]),
                           new CDAParameter("DEFECTGRADE", detailTable.Rows[i]["DEFECTGRADE"]),
                           new CDAParameter("TECHNICALREQUIREMENT", detailTable.Rows[i]["TECHNICALREQUIREMENT"]),
                           new CDAParameter("LOWERLIMIT", detailTable.Rows[i]["LOWERLIMIT"]),
                           new CDAParameter("UPPERLIMIT", detailTable.Rows[i]["UPPERLIMIT"]),
                           new CDAParameter("INSPECTCOUNT", detailTable.Rows[i]["INSPECTCOUNT"]),
                           new CDAParameter("ISAVERAGE", detailTable.Rows[i]["ISAVERAGE"]),
                           new CDAParameter("UNITNAME", detailTable.Rows[i]["UNITNAME"]),
                           new CDAParameter("REMARKS", detailTable.Rows[i]["REMARKS"]),
                           new CDAParameter("XUHAO", detailTable.Rows[i]["XUHAO"]),
                           new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]),
                           new CDAParameter("CREATEUSERID", HttpContext.Current.Session["userId"]),
                           new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"])
                        );
                    }
                }
            }
            else
            {
                string jsonStr = new JsonResult(JsonStatus.error) { message = "未保存任何数据！" }.ToJson();
                return jsonStr;
            }
            conn.Commit();
            return new JsonResult(JsonStatus.success).ToJson();
        }
    }

    /// <summary>
    /// 复制 20230925 qq
    /// </summary>
    /// <param name="jsondata"></param>
    /// <returns></returns>
    private string copy(string jsondata, string inspecttype,string inspectname,string goodstypecodeMaster,string inspectvision ,string remarks)
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            conn.BeginTransaction();
            //总单
            string[] goodscode = goodstypecodeMaster.ToString().Split(',');
            ArrayList  al = new ArrayList(goodscode);
            string goods = null;
            string goodstypes = null;
            DataTable goodstype = null;
            int flags = 0;
            for (int i = 0; i < al.Count; i++) {
                string test = al[i].ToString();
                string test2 = test;
                if (test.Substring(0, 3) == "001") {
                    al.RemoveAt(i);
                    continue;
                }
                if (flags == 0)
                {
                    goodstypes += "'" + al[i] + "'";
                }
                else {
                    goodstypes += ",'" + al[i]+"'";
                }
                flags++;

            }

            goodstype = conn.ExecuteDatatable(@"SELECT GOODSID,GOODSCODE FROM TP_MST_GOODS WHERE VALUEFLAG=1 and GOODSCODE in(" +goodstypes+")", new CDAParameter(null, null));

            if (goodstype.Rows.Count  == 1 )
            {
                goods = goodstype.Rows[0]["GOODSCODE"].ToString();
            }
            else if (goodstype.Rows.Count > 1 )
            {
                goods += goodstype.Rows[0]["GOODSCODE"]+"...";
            }

            //查询是否产品保存过记录
            DataTable goodsTable = new DataTable();
            if (goodstypecodeMaster != null && goodstypecodeMaster != "" && !goodstypecodeMaster.Contains(","))
            {
                goodsTable = conn.ExecuteDatatable(@" SELECT 
                    f.INSPECTTYPE,
                    f.GOODSCODE as itemsgoods,
                    m.ITEMSID, 
                    m.GOODSID,
                    m.GOODSCODE,
                    m.ACCOUNTID,
                    m.REMARKS
                FROM
                    TP_PM_FQCITEMSGOODS m 
                    left join  TP_PM_FQCITEMS  f on m.ITEMSID = f.ITEMSID
                WHERE 
                    m.VALUEFLAG = '1' 
                    AND f.VALUEFLAG = '1' 
                    AND f.INSPECTTYPE = @INSPECTTYPE@
                    AND m.GOODSCODE = @GOODSCODE@
                ",
                new CDAParameter("GOODSCODE", goods),
                new CDAParameter("INSPECTTYPE", inspecttype)
                );
            }
            else if (goodstype.Rows.Count > 0)
            {
                string newgoods = "";
                for (int i = 0; i < goodstype.Rows.Count; i++)
                {
                    if (i == 0)
                    {
                        newgoods += "'"+goodstype.Rows[i]["GOODSCODE"]+"'";
                    }
                    newgoods += ",'" + goodstype.Rows[i]["GOODSCODE"]+"'";
                }

                goodsTable = conn.ExecuteDatatable(@" SELECT 
                    f.INSPECTTYPE,
                    f.GOODSCODE as itemsgoods,
                    m.ITEMSID, 
                    m.GOODSID,
                    m.GOODSCODE,
                    m.ACCOUNTID,
                    m.REMARKS
                FROM
                    TP_PM_FQCITEMSGOODS m 
                    left join  TP_PM_FQCITEMS  f on m.ITEMSID = f.ITEMSID
                WHERE 
                    m.VALUEFLAG = '1' 
                    AND f.VALUEFLAG = '1' 
                    AND f.INSPECTTYPE = @INSPECTTYPE@
                    AND  m.GOODSCODE in(" +newgoods+")",
                    new CDAParameter("INSPECTTYPE", inspecttype)
                );
            }
            //之前保存过，把之前的置为无效
            if (goodsTable != null && goodsTable.Rows.Count > 0)
            {
                for (int i = 0; i < goodsTable.Rows.Count; i++)
                {
                    if (goodsTable.Rows[i]["itemsgoods"].ToString().Contains("..."))
                    {
                        //多个
                        int result1 = conn.ExecuteNonQuery(@"
                        UPDATE TP_PM_FQCITEMSGOODS 
                        SET
                            VALUEFLAG = 0,
                            UPDATEUSERID = @UPDATEUSERID@,
                            UPDATETIME = sysdate
                        WHERE 
                            ITEMSID = @ITEMSID@
                            and GOODSID = @GOODSID@
                        ",
                            new CDAParameter("ITEMSID", goodsTable.Rows[i]["ITEMSID"]),
                            new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"]),
                            new CDAParameter("GOODSID", goodsTable.Rows[i]["GOODSID"])
                        );

                        //多个
                        int result2 = conn.ExecuteNonQuery(@"
                        update TP_PM_FQCITEMS t set t.valueflag = '0' ,t.UPDATEUSERID = @UPDATEUSERID@,
                            t.UPDATETIME = sysdate
                        WHERE not exists(SELECT 1 FROM TP_PM_FQCITEMSGOODS g WHERE  g.itemsid =@ITEMSID@ and g.valueflag = '1')
                        and t.itemsid = @ITEMSID@
                        ",
                            new CDAParameter("ITEMSID", goodsTable.Rows[i]["ITEMSID"]),
                            new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"]),
                            new CDAParameter("GOODSID", goodsTable.Rows[i]["GOODSID"])
                        );


                    }
                    else
                    {
                        //单个
                        //根据ID删除之前的版本
                        int result1 = conn.ExecuteNonQuery(@"
                        UPDATE TP_PM_FQCITEMS 
                        SET
                            VALUEFLAG = 0,
                            UPDATEUSERID = @UPDATEUSERID@,
                            UPDATETIME = sysdate
                        WHERE 
                            ITEMSID = @ITEMSID@
                        ",
                            new CDAParameter("ITEMSID", goodsTable.Rows[i]["ITEMSID"]),
                            new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"])
                        );
                    }
                }

                if (true)
                {

                }
            }

            int itemsID = 0;
            if (!string.IsNullOrWhiteSpace(goods))
            {
                int result = conn.ExecuteNonQuery(@"
                    INSERT INTO TP_PM_FQCITEMS (
                        INSPECTTYPE,
                        INSPECTNAME,
                        GOODSCODE,
                        INSPECTVERSION,
                        REMARKS,
                        ACCOUNTID,
                        CREATEUSERID,
                        UPDATEUSERID
                    ) VALUES (
                        @INSPECTTYPE@,
                        @INSPECTNAME@,
                        @GOODSCODE@,
                        @INSPECTVERSION@,
                        @REMARKS@,
                        @ACCOUNTID@,
                        @CREATEUSERID@,
                        @UPDATEUSERID@
                    )
                    ",
                    new CDAParameter("INSPECTTYPE", inspecttype),
                    new CDAParameter("INSPECTNAME", inspectname),
                    new CDAParameter("GOODSCODE", goods.ToString()),
                    new CDAParameter("INSPECTVERSION", inspectvision),
                    new CDAParameter("REMARKS", remarks),
                    new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]),
                    new CDAParameter("CREATEUSERID", HttpContext.Current.Session["userId"]),
                    new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"])
                );
                itemsID = Convert.ToInt32(conn.ExecuteScalar(@"SELECT SEQ_PM_FQCITEMS_ID.currval from dual"));
                if (goodstypecodeMaster != "" && goodstypecodeMaster != null) {
                    for (int i = 0; i < goodstype.Rows.Count; i++) {
                        int flag = conn.ExecuteNonQuery(@"
                      INSERT INTO TP_PM_FQCITEMSGOODS ( ITEMSID, GOODSID, GOODSCODE, ACCOUNTID, CREATEUSERID, UPDATEUSERID, REMARKS )
                         VALUES ( @ITEMSID@, @GOODSID@, @GOODSCODE@, @ACCOUNTID@, @CREATEUSERID@, @UPDATEUSERID@, @REMARKS@ )",
                      //new CDAParameter("ITEMSID",primaryKey),
                      new CDAParameter("ITEMSID",itemsID),
                      new CDAParameter("GOODSID", goodstype.Rows[i]["GOODSID"].ToString()),
                      new CDAParameter("GOODSCODE", goodstype.Rows[i]["GOODSCODE"].ToString()),
                      new CDAParameter("REMARKS", ""),
                      new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]),
                      new CDAParameter("CREATEUSERID", HttpContext.Current.Session["userId"]),
                      new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"])
                  );
                    }
                }

            }

            //明细 
            DataTable detailTable = JsonConvert.DeserializeObject<DataTable>(jsondata);

            if (detailTable != null && detailTable.Rows.Count > 0)
            {
                //明细
                for (int i = 0; i < detailTable.Rows.Count; i++)
                {
                    int detailresult = conn.ExecuteNonQuery(@"
                        INSERT INTO TP_PM_FQCITEMSDETAIL ( 
                            ITEMSID,
                            INSPECTITEM,
                            INSPECTTOOL,
                            INSPECTMARK,
                            INSPECTWAY,
                            ISINSPECT,
                            INSPECTBASIS,
                            DEFECTGRADE,
                            TECHNICALREQUIREMENT,
                            LOWERLIMIT,
                            UPPERLIMIT,
                            INSPECTCOUNT,
                            ISAVERAGE,  
                            UNITNAME,
                            REMARKS,XUHAO,
                            ACCOUNTID,CREATEUSERID,UPDATEUSERID
                        ) VALUES ( 
                            @ITEMSID@,
                            @INSPECTITEM@,
                           (SELECT DISTINCT T.DICTIONARYID AS ID FROM TP_MST_DATADICTIONARY T WHERE T.VALUEFLAG = 1 AND T.DICTIONARYTYPE = 'TPC022' AND T.DICTIONARYVALUE=@INSPECTTOOL@),
                           (SELECT DISTINCT T.DICTIONARYID AS ID FROM TP_MST_DATADICTIONARY T WHERE T.VALUEFLAG = 1 AND T.DICTIONARYTYPE = 'TPC023' AND T.DICTIONARYVALUE=@INSPECTMARK@),
                            @INSPECTWAY@,
                            @ISINSPECT@,
                            @INSPECTBASIS@,
                            @DEFECTGRADE@,
                            @TECHNICALREQUIREMENT@,
                            @LOWERLIMIT@,
                            @UPPERLIMIT@,
                            @INSPECTCOUNT@,
                            @ISAVERAGE@,
                            @UNITNAME@,
                            @REMARKS@,@XUHAO@,
                            @ACCOUNTID@,@CREATEUSERID@,@UPDATEUSERID@
                        )
                        ",
                       new CDAParameter("ITEMSID", itemsID),
                       new CDAParameter("INSPECTITEM", detailTable.Rows[i]["INSPECTITEM"]),
                       new CDAParameter("INSPECTTOOL", detailTable.Rows[i]["INSPECTTOOL"]),
                       new CDAParameter("INSPECTMARK", detailTable.Rows[i]["INSPECTMARK"]),
                       new CDAParameter("INSPECTWAY", detailTable.Rows[i]["INSPECTWAY"]),
                       new CDAParameter("ISINSPECT", detailTable.Rows[i]["ISINSPECT"]),
                       new CDAParameter("INSPECTBASIS", detailTable.Rows[i]["INSPECTBASIS"]),
                       new CDAParameter("DEFECTGRADE", detailTable.Rows[i]["DEFECTGRADE"]),
                       new CDAParameter("TECHNICALREQUIREMENT", detailTable.Rows[i]["TECHNICALREQUIREMENT"]),
                       new CDAParameter("LOWERLIMIT", detailTable.Rows[i]["LOWERLIMIT"]),
                       new CDAParameter("UPPERLIMIT", detailTable.Rows[i]["UPPERLIMIT"]),
                       new CDAParameter("INSPECTCOUNT", detailTable.Rows[i]["INSPECTCOUNT"]),
                       new CDAParameter("ISAVERAGE", detailTable.Rows[i]["ISAVERAGE"]),
                       new CDAParameter("UNITNAME", detailTable.Rows[i]["UNITNAME"]),
                       new CDAParameter("REMARKS", detailTable.Rows[i]["REMARKS"]),
                       new CDAParameter("XUHAO", detailTable.Rows[i]["XUHAO"]),
                       new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]),
                       new CDAParameter("CREATEUSERID", HttpContext.Current.Session["userId"]),
                       new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"])
                    );
                }
                //if (detailTable.Rows.Count == 92 &&itemsID > 0)
                //{
                //    //更新序号 
                //    int result2 = conn.ExecuteNonQuery(@"
                //        UPDATE TP_PM_FQCITEMSDETAIL C SET C.XUHAO = (
                //        SELECT DISTINCT A.XUHAO FROM TP_PM_FQCITEMSDETAIL A
                //        WHERE A.INSPECTITEM = C.INSPECTITEM 
                //         AND A.ITEMSID = 164 ) WHERE C.ITEMSID = @ITEMSID@
                //    ",
                //        new CDAParameter("ITEMSID", itemsID)
                //    );
                //}
                //else if (itemsID > 0 && (detailTable.Rows.Count == 84 || detailTable.Rows.Count == 85))
                //{
                //    //更新序号 
                //    int result2 = conn.ExecuteNonQuery(@"
                //        UPDATE TP_PM_FQCITEMSDETAIL C SET C.XUHAO = (
                //        SELECT DISTINCT A.XUHAO FROM TP_PM_FQCITEMSDETAIL A
                //        WHERE A.INSPECTITEM = C.INSPECTITEM 
                //         AND A.ITEMSID = 247 ) WHERE C.ITEMSID = @ITEMSID@
                //    ",
                //        new CDAParameter("ITEMSID", itemsID)
                //    ); 
                //}
                //else if (detailTable.Rows.Count == 80 &&itemsID > 0)
                //{
                //    //更新序号 
                //    int result2 = conn.ExecuteNonQuery(@"
                //        UPDATE TP_PM_FQCITEMSDETAIL C SET C.XUHAO = (
                //        SELECT DISTINCT A.XUHAO FROM TP_PM_FQCITEMSDETAIL A
                //        WHERE A.INSPECTITEM = C.INSPECTITEM 
                //         AND A.ITEMSID = 303 ) WHERE C.ITEMSID = @ITEMSID@
                //    ",
                //        new CDAParameter("ITEMSID", itemsID)
                //    );
                //}

            }
            else
            {
                string jsonStr = new JsonResult(JsonStatus.error) { message = "未保存任何数据！" }.ToJson();
                return jsonStr;
            }
            conn.Commit();
            return new JsonResult(JsonStatus.success).ToJson();
        }
    }


    /// <summary>
    /// TP_PM_FQCITEMSDETAIL 查询
    /// </summary>
    /// <returns>json</returns>
    private string search(NameValueCollection form,string itemsid)
    {
        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.ITEMSDETAILID AS SID,
                    m.ITEMSDETAILID,
                    m.ITEMSID,
                    m.INSPECTITEM,
					TOOL.DICTIONARYVALUE as INSPECTTOOL,
					MARK.DICTIONARYVALUE as INSPECTMARK,
                    m.INSPECTWAY,
                    m.ISINSPECT,
                    m.INSPECTBASIS,
                    m.DEFECTGRADE,
                    m.TECHNICALREQUIREMENT,
                    TO_CHAR( m.LOWERLIMIT) LOWERLIMIT,
                    TO_CHAR( m.UPPERLIMIT) UPPERLIMIT,
                    TO_CHAR( m.INSPECTCOUNT) INSPECTCOUNT,
                    m.ISAVERAGE,  
                    m.UNITNAME,
                    m.REMARKS,
                    TO_CHAR(m.VALUEFLAG) VALUEFLAG,
                    TO_CHAR(m.ACCOUNTID) ACCOUNTID,
                    TO_CHAR(m.CREATEUSERID) CREATEUSERID,
                    m.CREATETIME,
                    TO_CHAR(m.UPDATEUSERID) UPDATEUSERID,
                    m.UPDATETIME,
                    m.XUHAO
                FROM
                    TP_PM_FQCITEMSDETAIL m
                    left join TP_PM_FQCITEMS fi on fi.itemsid = m.itemsid 
                    left join TP_MST_DATADICTIONARY TOOL on TOOL.DICTIONARYID=m.INSPECTTOOL
					left join TP_MST_DATADICTIONARY  MARK ON MARK.DICTIONARYID=m.INSPECTMARK
                WHERE 
                    m.VALUEFLAG = '1'
                    and fi.valueflag = '1'
                    AND m.ACCOUNTID = @ACCOUNTID@
            ";
            List<CDAParameter> sqlPara = new List<CDAParameter>();
            sqlPara.Add(new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]));
            if (!string.IsNullOrEmpty(itemsid)) {
                sqlStr += " AND m.ITEMSID = @ITEMSID@ ";
                sqlPara.Add(new CDAParameter("ITEMSID", itemsid.ToString()));
            }
            if (!string.IsNullOrEmpty(form["ITEMSDETAILID"]))
            {
                sqlStr += " AND m.ITEMSDETAILID = @ITEMSDETAILID@ ";
                sqlPara.Add(new CDAParameter("ITEMSDETAILID", form["ITEMSDETAILID"]));
            }
            if (!string.IsNullOrEmpty(form["ITEMSID"]))
            {
                sqlStr += " AND m.ITEMSID = @ITEMSID@ ";
                sqlPara.Add(new CDAParameter("ITEMSID", form["ITEMSID"]));
            }
            if (!string.IsNullOrEmpty(form["INSPECTITEM"]))
            {
                sqlStr += " AND INSTR( m.INSPECTITEM, @INSPECTITEM@ ) > 0 ";
                sqlPara.Add(new CDAParameter("INSPECTITEM", form["INSPECTITEM"]));
            }
            if (!string.IsNullOrEmpty(form["INSPECTTOOL"]))
            {
                sqlStr += " AND INSTR( m.INSPECTTOOL, @INSPECTTOOL@ ) > 0 ";
                sqlPara.Add(new CDAParameter("INSPECTTOOL", form["INSPECTTOOL"]));
            }
            if (!string.IsNullOrEmpty(form["INSPECTMARK"]))
            {
                sqlStr += " AND INSTR( m.INSPECTMARK, @INSPECTMARK@ ) > 0 ";
                sqlPara.Add(new CDAParameter("INSPECTMARK", form["INSPECTMARK"]));
            }
            if (!string.IsNullOrEmpty(form["INSPECTWAY"]))
            {
                sqlStr += " AND INSTR( m.INSPECTWAY, @INSPECTWAY@ ) > 0 ";
                sqlPara.Add(new CDAParameter("INSPECTWAY", form["INSPECTWAY"]));
            }
            if (!string.IsNullOrEmpty(form["ISINSPECT"]))
            {
                sqlStr += " AND INSTR( m.ISINSPECT, @ISINSPECT@ ) > 0 ";
                sqlPara.Add(new CDAParameter("ISINSPECT", form["ISINSPECT"]));
            }
            if (!string.IsNullOrEmpty(form["INSPECTBASIS"]))
            {
                sqlStr += " AND INSTR( m.INSPECTBASIS, @INSPECTBASIS@ ) > 0 ";
                sqlPara.Add(new CDAParameter("INSPECTBASIS", form["INSPECTBASIS"]));
            }
            if (!string.IsNullOrEmpty(form["DEFECTGRADE"]))
            {
                sqlStr += " AND INSTR( m.DEFECTGRADE, @DEFECTGRADE@ ) > 0 ";
                sqlPara.Add(new CDAParameter("DEFECTGRADE", form["DEFECTGRADE"]));
            }
            if (!string.IsNullOrEmpty(form["TECHNICALREQUIREMENT"]))
            {
                sqlStr += " AND INSTR( m.TECHNICALREQUIREMENT, @TECHNICALREQUIREMENT@ ) > 0 ";
                sqlPara.Add(new CDAParameter("TECHNICALREQUIREMENT", form["TECHNICALREQUIREMENT"]));
            }
            if (!string.IsNullOrEmpty(form["LOWERLIMIT"]))
            {
                sqlStr += " AND INSTR( m.LOWERLIMIT, @LOWERLIMIT@ ) > 0 ";
                sqlPara.Add(new CDAParameter("LOWERLIMIT", form["LOWERLIMIT"]));
            }
            if (!string.IsNullOrEmpty(form["UPPERLIMIT"]))
            {
                sqlStr += " AND INSTR( m.UPPERLIMIT, @UPPERLIMIT@ ) > 0 ";
                sqlPara.Add(new CDAParameter("UPPERLIMIT", form["UPPERLIMIT"]));
            }
            if (!string.IsNullOrEmpty(form["UNITNAME"]))
            {
                sqlStr += " AND INSTR( m.UNITNAME, @UNITNAME@ ) > 0 ";
                sqlPara.Add(new CDAParameter("UNITNAME", form["UNITNAME"]));
            }
            if (!string.IsNullOrEmpty(form["REMARKS"]))
            {
                sqlStr += " AND INSTR( m.REMARKS, @REMARKS@ ) > 0 ";
                sqlPara.Add(new CDAParameter("REMARKS", form["REMARKS"]));
            }

            sqlStr += "ORDER BY XUHAO,SID DESC";

            int total = 0;
            DataTable dt = conn.SelectPages(page, rows, out total, sqlStr, sqlPara.ToArray());
            return new JsonResult(dt) { total = total }.ToJson();
        }
    }

    /// <summary>
    /// TP_PM_FQCITEMSDETAIL 查询
    /// </summary>
    /// <returns>json</returns>
    private string searchgs(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.GOODSCODE,
                    m.GOODSID,
                    m.ITEMSID,
                    m.ITEMSGOODSID as SID
                FROM
                    TP_PM_FQCITEMSGOODS m
                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["ITEMSID"]))
            {
                sqlStr += " AND m.ITEMSID = @ITEMSID@ ";
                sqlPara.Add(new CDAParameter("ITEMSID", form["ITEMSID"]));
            }
            //int total = 0;
            DataTable dt = conn.ExecuteDatatable( sqlStr, sqlPara.ToArray());
            return new JsonResult(dt).ToJson();
        }
    }


    /// <summary>
    /// 详细 TP_PM_FQCITEMSDETAIL
    /// </summary>
    /// <returns>json</returns>
    private string detail()
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            DataTable dt = conn.ExecuteDatatable(@"
                SELECT 
                    m.ITEMSDETAILID,
                    m.ITEMSID,
                    m.INSPECTITEM,
                    m.INSPECTTOOL,
                    m.INSPECTMARK,
                    m.INSPECTWAY,
                    m.ISINSPECT,
                    m.INSPECTBASIS,
                    m.DEFECTGRADE,
                    m.TECHNICALREQUIREMENT,
                    m.LOWERLIMIT,
                    m.UPPERLIMIT,
                    m.INSPECTCOUNT,
                    m.ISAVERAGE,  
                    m.UNITNAME,
                    m.REMARKS,
                    m.VALUEFLAG,
                    m.ACCOUNTID,
                    m.CREATEUSERID,
                    m.CREATETIME,
                    m.UPDATEUSERID,
                    m.UPDATETIME,
                    m.XUHAO
                FROM
                    TP_PM_FQCITEMSDETAIL m
                WHERE 
                    m.VALUEFLAG = '1'
                    AND m.ACCOUNTID = @ACCOUNTID@
                    AND m.ITEMSDETAILID = @ITEMSDETAILID@
                    order by m.XUHAO,m.ITEMSDETAILID
                ",
                new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]),
                new CDAParameter("ITEMSDETAILID", HttpContext.Current.Request["id"])
            );
            return new JsonResult(dt).ToJson();
        }
    }

    /// <summary>
    /// 插入 TP_PM_FQCITEMSDETAIL
    /// </summary>
    /// <returns>json</returns>
    private string insert(NameValueCollection form)
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            //string primaryKey = conn.GetSequenceNextval("SEQ_TP_PM_FQCITEMSDETAIL_ID").ToString();
            //string primaryKey = Guid.NewGuid().ToString().Replace("_", "");
            int result = conn.ExecuteNonQuery(@"
                INSERT INTO TP_PM_FQCITEMSDETAIL (
                    --ITEMSDETAILID,
                    ITEMSID,
                    INSPECTITEM,
                    INSPECTTOOL,
                    INSPECTMARK,
                    INSPECTWAY,
                    ISINSPECT,
                    INSPECTBASIS,
                    DEFECTGRADE,
                    TECHNICALREQUIREMENT,
                    LOWERLIMIT,
                    UPPERLIMIT,
                    INSPECTCOUNT,
                    ISAVERAGE,  
                    UNITNAME,
                    REMARKS,
                    ACCOUNTID,CREATEUSERID,UPDATEUSERID
                ) VALUES (
                    --@ITEMSDETAILID@,
                    @ITEMSID@,
                    @INSPECTITEM@,
                    @INSPECTTOOL@,
                    @INSPECTMARK@,
                    @INSPECTWAY@,
                    @ISINSPECT@,
                    @INSPECTBASIS@,
                    @DEFECTGRADE@,
                    @TECHNICALREQUIREMENT@,
                    @LOWERLIMIT@,
                    @UPPERLIMIT@,
                    @INSPECTCOUNT@,
                    @ISAVERAGE@,
                    @UNITNAME@,
                    @REMARKS@,
                    @ACCOUNTID@,@CREATEUSERID@,@UPDATEUSERID@
                )
                ",
                //new CDAParameter("ITEMSDETAILID",primaryKey),
                new CDAParameter("ITEMSID", form["ITEMSID"]),
                new CDAParameter("INSPECTITEM", form["INSPECTITEM"]),
                new CDAParameter("INSPECTTOOL", form["INSPECTTOOL"]),
                new CDAParameter("INSPECTMARK", form["INSPECTMARK"]),
                new CDAParameter("INSPECTWAY", form["INSPECTWAY"]),
                new CDAParameter("ISINSPECT", form["ISINSPECT"]),
                new CDAParameter("INSPECTBASIS", form["INSPECTBASIS"]),
                new CDAParameter("DEFECTGRADE", form["DEFECTGRADE"]),
                new CDAParameter("TECHNICALREQUIREMENT", form["TECHNICALREQUIREMENT"]),
                new CDAParameter("LOWERLIMIT", form["LOWERLIMIT"]),
                new CDAParameter("UPPERLIMIT", form["UPPERLIMIT"]),
                new CDAParameter("INSPECTCOUNT", form["INSPECTCOUNT"]),
                new CDAParameter("ISAVERAGE", form["ISAVERAGE"]),
                new CDAParameter("UNITNAME", form["UNITNAME"]),
                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_FQCITEMSDETAIL
    /// </summary>
    /// <returns>json</returns>
    private string update(NameValueCollection form)
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            int result = conn.ExecuteNonQuery(@"
                UPDATE TP_PM_FQCITEMSDETAIL 
                SET
                    --ITEMSID = @ITEMSID@,
                    INSPECTITEM = @INSPECTITEM@,
                    INSPECTTOOL = @INSPECTTOOL@,
                    INSPECTMARK = @INSPECTMARK@,
                    INSPECTWAY = @INSPECTWAY@,
                    ISINSPECT = @ISINSPECT@,
                    INSPECTBASIS = @INSPECTBASIS@,
                    DEFECTGRADE = @DEFECTGRADE@,
                    TECHNICALREQUIREMENT = @TECHNICALREQUIREMENT@,
                    LOWERLIMIT = @LOWERLIMIT@,
                    UPPERLIMIT = @UPPERLIMIT@,
                    UNITNAME = @UNITNAME@,
                    REMARKS = @REMARKS@,
                    UPDATEUSERID = @UPDATEUSERID@,
                    UPDATETIME = sysdate
                WHERE 
                    ITEMSDETAILID = @ITEMSDETAILID@
                ",
                //new CDAParameter("ITEMSID",form["ITEMSID"]),
                new CDAParameter("INSPECTITEM", form["INSPECTITEM"]),
                new CDAParameter("INSPECTTOOL", form["INSPECTTOOL"]),
                new CDAParameter("INSPECTMARK", form["INSPECTMARK"]),
                new CDAParameter("INSPECTWAY", form["INSPECTWAY"]),
                new CDAParameter("ISINSPECT", form["ISINSPECT"]),
                new CDAParameter("INSPECTBASIS", form["INSPECTBASIS"]),
                new CDAParameter("DEFECTGRADE", form["DEFECTGRADE"]),
                new CDAParameter("TECHNICALREQUIREMENT", form["TECHNICALREQUIREMENT"]),
                new CDAParameter("LOWERLIMIT", form["LOWERLIMIT"]),
                new CDAParameter("UPPERLIMIT", form["UPPERLIMIT"]),
                new CDAParameter("UNITNAME", form["UNITNAME"]),
                new CDAParameter("REMARKS", form["REMARKS"]),
                new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"]),
                new CDAParameter("ITEMSDETAILID", HttpContext.Current.Request["id"])
            );
            return new JsonResult(JsonStatus.success).ToJson();
        }
    }

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

    private string Imports(DataTable detailTable, int itemsID)
    {

        using (IDataAccess conn = DataAccess.Create())
        {
            try
            {
                conn.BeginTransaction();
                if (detailTable != null && detailTable.Rows.Count > 0)
                {

                    DataTable itemsdetailsTable = conn.ExecuteDatatable(@"
                    SELECT 
                             1
                    FROM TP_PM_FQCITEMSDETAIL m 
                     WHERE m.VALUEFLAG = '1' 
                           AND m.ITEMSID = @ITEMSID@ ",
                    new CDAParameter("ITEMSID", itemsID)
                    );

                    if (itemsdetailsTable != null && itemsdetailsTable.Rows.Count > 0)
                    {
                        DataTable itemsTable = conn.ExecuteDatatable(@" 
                        SELECT 
                            m.ITEMSID,
                            m.INSPECTTYPE,
                            m.INSPECTTYPE,
                            m.INSPECTNAME,
                            m.GOODSID,
                            m.GOODSCODE,
                            m.INSPECTVERSION,
                            m.REMARKS,
                            m.VALUEFLAG,
                            m.ACCOUNTID
                         FROM
                         TP_PM_FQCITEMS m 
                         WHERE 
                            m.VALUEFLAG = '1' 
                            AND m.ITEMSID = @ITEMSID@
                            ",
                            new CDAParameter("ITEMSID", itemsID)
                        );

                        //产品明细
                        DataTable goodsTable = conn.ExecuteDatatable(@"
                        SELECT 
                             m.ITEMSID, 
                             m.GOODSID,
                             m.GOODSCODE,
                             m.ACCOUNTID,
                             m.REMARKS
                         FROM
                         TP_PM_FQCITEMSGOODS m 
                         WHERE 
                             m.VALUEFLAG = '1' 
                             AND m.ITEMSID = @ITEMSID@ ",
                        new CDAParameter("ITEMSID", itemsID)
                        );

                        //根据ID删除之前的版本
                        int result = conn.ExecuteNonQuery(@"
                          UPDATE TP_PM_FQCITEMS 
                            SET
                            VALUEFLAG = 0,
                            UPDATEUSERID = @UPDATEUSERID@,
                            UPDATETIME = sysdate
                          WHERE 
                            ITEMSID = @ITEMSID@
                          ",
                            new CDAParameter("ITEMSID", itemsID),
                            new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"])
                        );

                        //查询原数据，新建一条版本号加一的数据，主表，明细，产品
                        int resultaddItems = conn.ExecuteNonQuery(@"
                                                    INSERT INTO TP_PM_FQCITEMS (
                                                        INSPECTTYPE,
                                                        INSPECTNAME,
                                                        GOODSCODE,
                                                        INSPECTVERSION,
                                                        REMARKS,
                                                        ACCOUNTID,
                                                        CREATEUSERID,
                                                        UPDATEUSERID
                                                    ) VALUES (
                                                         @INSPECTTYPE@,
                                                         @INSPECTNAME@,
                                                         @GOODSCODE@,
                                                         @INSPECTVERSION@,
                                                         @REMARKS@,
                                                         @ACCOUNTID@,
                                                         @CREATEUSERID@,
                                                         @UPDATEUSERID@ 
                                                    )",
                           new CDAParameter("INSPECTTYPE", itemsTable.Rows[0]["INSPECTTYPE"].ToString()),
                           new CDAParameter("INSPECTNAME", itemsTable.Rows[0]["INSPECTNAME"].ToString()),
                           new CDAParameter("GOODSCODE", itemsTable.Rows[0]["GOODSCODE"].ToString()),
                           new CDAParameter("INSPECTVERSION", Convert.ToInt32(itemsTable.Rows[0]["INSPECTVERSION"]) + 1),
                           new CDAParameter("REMARKS", itemsTable.Rows[0]["REMARKS"]),
                           new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]),
                           new CDAParameter("CREATEUSERID", HttpContext.Current.Session["userId"]),
                           new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"])
                       );
                        object newID = conn.ExecuteScalar(@"SELECT SEQ_PM_FQCITEMS_ID.currval from dual");

                        //产品
                        for (int i = 0; i < goodsTable.Rows.Count; i++)
                        {
                            int goodsresult = conn.ExecuteNonQuery(@"
                                                    INSERT INTO TP_PM_FQCITEMSGOODS ( ITEMSID, GOODSID, GOODSCODE, ACCOUNTID, CREATEUSERID, UPDATEUSERID, REMARKS )
                                                        VALUES ( @ITEMSID@, @GOODSID@, @GOODSCODE@, @ACCOUNTID@, @CREATEUSERID@, @UPDATEUSERID@, @REMARKS@ )",
                            new CDAParameter("ITEMSID", newID),
                            new CDAParameter("GOODSID", goodsTable.Rows[i]["GOODSID"].ToString()),
                            new CDAParameter("GOODSCODE", goodsTable.Rows[i]["GOODSCODE"].ToString()),
                            new CDAParameter("REMARKS", goodsTable.Rows[i]["REMARKS"]),
                            new CDAParameter("ACCOUNTID", HttpContext.Current.Session["accountId"]),
                            new CDAParameter("CREATEUSERID", HttpContext.Current.Session["userId"]),
                            new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"])
                          );
                        }

                        //明细
                        for (int i = 0; i < detailTable.Rows.Count; i++)
                        {
                            int detailresult = conn.ExecuteNonQuery(@"
                                                    INSERT INTO TP_PM_FQCITEMSDETAIL ( 
                                                        ITEMSID,
                                                        INSPECTITEM,
                                                        INSPECTTOOL,
                                                        INSPECTMARK,
                                                        INSPECTWAY,
                                                        ISINSPECT,
                                                        INSPECTBASIS,
                                                        DEFECTGRADE,
                                                        TECHNICALREQUIREMENT,
                                                        LOWERLIMIT,
                                                        UPPERLIMIT,
                                                        INSPECTCOUNT,
                                                        ISAVERAGE,  
                                                        UNITNAME,
                                                        REMARKS,
                                                        ACCOUNTID,CREATEUSERID,UPDATEUSERID
                                                    ) VALUES ( 
                                                        @ITEMSID@,
                                                        @INSPECTITEM@,
                                                       (SELECT DISTINCT T.DICTIONARYID AS ID FROM TP_MST_DATADICTIONARY T WHERE T.VALUEFLAG = 1 AND T.DICTIONARYTYPE = 'TPC022' AND T.DICTIONARYVALUE=@INSPECTTOOL@),
                                                       (SELECT DISTINCT T.DICTIONARYID AS ID FROM TP_MST_DATADICTIONARY T WHERE T.VALUEFLAG = 1 AND T.DICTIONARYTYPE = 'TPC023' AND T.DICTIONARYVALUE=@INSPECTMARK@),
                                                        @INSPECTWAY@,
                                                        @ISINSPECT@,
                                                        @INSPECTBASIS@,
                                                        @DEFECTGRADE@,
                                                        @TECHNICALREQUIREMENT@,
                                                        @LOWERLIMIT@,
                                                        @UPPERLIMIT@,
                                                        @INSPECTCOUNT@,
                                                        @ISAVERAGE@,
                                                        @UNITNAME@,
                                                        @REMARKS@,
                                                        @ACCOUNTID@,@CREATEUSERID@,@UPDATEUSERID@
                                                    )
                                                    ",
                                                 new CDAParameter("ITEMSID", newID),
                                                 new CDAParameter("INSPECTITEM", detailTable.Rows[i]["检验项目"]),
                                                 new CDAParameter("INSPECTTOOL", detailTable.Rows[i]["检验工具"]),
                                                 new CDAParameter("INSPECTMARK", detailTable.Rows[i]["检验标识"]),
                                                 new CDAParameter("INSPECTWAY", detailTable.Rows[i]["检验方式"]),
                                                 new CDAParameter("ISINSPECT", detailTable.Rows[i]["是否必检0否1是"]),
                                                 new CDAParameter("INSPECTBASIS", detailTable.Rows[i]["检验依据"]),
                                                 new CDAParameter("DEFECTGRADE", detailTable.Rows[i]["缺陷等级"]),
                                                 new CDAParameter("TECHNICALREQUIREMENT", detailTable.Rows[i]["技术要求"]),
                                                 new CDAParameter("LOWERLIMIT", detailTable.Rows[i]["规格下限"]),
                                                 new CDAParameter("UPPERLIMIT", detailTable.Rows[i]["规格上限"]),
                                                 new CDAParameter("INSPECTCOUNT", detailTable.Rows[i]["检验次数"]),
                                                 new CDAParameter("ISAVERAGE", detailTable.Rows[i]["是否统计平均值0否1是"]),
                                                 new CDAParameter("UNITNAME", detailTable.Rows[i]["单位名称"]),
                                                 new CDAParameter("REMARKS", detailTable.Rows[i]["备注"]),
                                                 new CDAParameter("ACCOUNTID", 1),
                                                 new CDAParameter("CREATEUSERID", HttpContext.Current.Session["userId"]),
                                                 new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"])
                                              );
                        }
                    }
                    else
                    {
                        //明细
                        for (int i = 0; i < detailTable.Rows.Count; i++)
                        {
                            int detailresult = conn.ExecuteNonQuery(@"
                            INSERT INTO TP_PM_FQCITEMSDETAIL ( 
                                ITEMSID,
                                INSPECTITEM,
                                INSPECTTOOL,
                                INSPECTMARK,
                                INSPECTWAY,
                                ISINSPECT,
                                INSPECTBASIS,
                                DEFECTGRADE,
                                TECHNICALREQUIREMENT,
                                LOWERLIMIT,
                                UPPERLIMIT,
                                INSPECTCOUNT,
                                ISAVERAGE,  
                                UNITNAME,
                                REMARKS,
                                ACCOUNTID,CREATEUSERID,UPDATEUSERID
                            ) VALUES ( 
                                @ITEMSID@,
                                @INSPECTITEM@,
                               (SELECT DISTINCT T.DICTIONARYID AS ID FROM TP_MST_DATADICTIONARY T WHERE T.VALUEFLAG = 1 AND T.DICTIONARYTYPE = 'TPC022' AND T.DICTIONARYVALUE=@INSPECTTOOL@),
                               (SELECT DISTINCT T.DICTIONARYID AS ID FROM TP_MST_DATADICTIONARY T WHERE T.VALUEFLAG = 1 AND T.DICTIONARYTYPE = 'TPC023' AND T.DICTIONARYVALUE=@INSPECTMARK@),
                                @INSPECTWAY@,
                                @ISINSPECT@,
                                @INSPECTBASIS@,
                                @DEFECTGRADE@,
                                @TECHNICALREQUIREMENT@,
                                @LOWERLIMIT@,
                                @UPPERLIMIT@,
                                @INSPECTCOUNT@,
                                @ISAVERAGE@,
                                @UNITNAME@,
                                @REMARKS@,
                                @ACCOUNTID@,@CREATEUSERID@,@UPDATEUSERID@
                            )
                            ",
                               new CDAParameter("ITEMSID", itemsID),
                               new CDAParameter("INSPECTITEM", detailTable.Rows[i]["检验项目"]),
                               new CDAParameter("INSPECTTOOL", detailTable.Rows[i]["检验工具"]),
                               new CDAParameter("INSPECTMARK", detailTable.Rows[i]["检验标识"]),
                               new CDAParameter("INSPECTWAY", detailTable.Rows[i]["检验方式"]),
                               new CDAParameter("ISINSPECT", detailTable.Rows[i]["是否必检0否1是"]),
                               new CDAParameter("INSPECTBASIS", detailTable.Rows[i]["检验依据"]),
                               new CDAParameter("DEFECTGRADE", detailTable.Rows[i]["缺陷等级"]),
                               new CDAParameter("TECHNICALREQUIREMENT", detailTable.Rows[i]["技术要求"]),
                               new CDAParameter("LOWERLIMIT", detailTable.Rows[i]["规格下限"]),
                               new CDAParameter("UPPERLIMIT", detailTable.Rows[i]["规格上限"]),
                               new CDAParameter("INSPECTCOUNT", detailTable.Rows[i]["检验次数"]),
                               new CDAParameter("ISAVERAGE", detailTable.Rows[i]["是否统计平均值0否1是"]),
                               new CDAParameter("UNITNAME", detailTable.Rows[i]["单位名称"]),
                               new CDAParameter("REMARKS", detailTable.Rows[i]["备注"]),
                               new CDAParameter("ACCOUNTID", 1),
                               new CDAParameter("CREATEUSERID", HttpContext.Current.Session["userId"]),
                               new CDAParameter("UPDATEUSERID", HttpContext.Current.Session["userId"])
                            );
                        }
                    }
                }
                conn.Commit();
                return new JsonResult(JsonStatus.error) { message = "导入成功 请手动刷新总单数据!" }.ToJson();
            }
            catch (Exception e)
            {
                conn.Rollback();
                return new JsonResult(JsonStatus.error) { message = "导入失败 请验证文件格式及模板正确性!" }.ToJson();
            }
        }
    }

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

    private class Button
    {
        public bool fqcitemsdetailButtonIndex = false;
        public bool fqcitemsdetailButtonInsert = false;
        public bool fqcitemsdetailButtonInsertBatch = false;
        public bool fqcitemsdetailButtonCopy = false;
        public bool fqcitemsdetailButtonUpdate = false;
        public bool fqcitemsdetailButtonDelete = false;
        public bool fqcitemsdetailButtonCancel = false;
        public bool fqcitemsdetailButtonSearch = false;
        public bool fqcitemsdetailButtonDetail = false;
        public bool fqcitemsdetailButtonCheckbox = false;
        public bool fqcitemsdetailButtonExport = false;
        public bool fqcitemsdetailButtonReload = false;
        public bool fqcitemsdetailButtonBatch = false;
        public bool fqcitemsdetailButtonImport = false;
    }

    private class xRecord
    {
        public string sid { get; set; }
        public string ITEMSID { get; set; }
        public string INSPECTITEM { get; set; }
        public string INSPECTTOOL { get; set; }
        public string INSPECTMARK { get; set; }
        public string INSPECTWAY { get; set; }
        public string ISINSPECT { get; set; }
        public string INSPECTBASIS { get; set; }
        public string DEFECTGRADE { get; set; }
        public string TECHNICALREQUIREMENT { get; set; }
        public string LOWERLIMIT { get; set; }
        public string UPPERLIMIT { get; set; }
        public string UNITNAME { get; set; }
        public string REMARKS { get; set; }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
    #region 读取excel数据
    /// <summary>
    /// 读取excel数据
    /// </summary>
    /// <param name="fileName">地址</param>
    /// 李士越 2024-07-01
    /// <returns></returns>
    public static DataTable ExcelToDatatable(string fileName)
    {
        ISheet sheet = null;
        DataTable datatable = new DataTable();
        int startRow = 0;
        FileStream fs;
        IWorkbook workbook = null;
        int cellCount = 0;
        int rowCount = 0;
        try
        {
            fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
            if (fileName.IndexOf(".xlsx") > 0)
            {
                workbook = new XSSFWorkbook(fs);
            }
            else if (fileName.IndexOf(".xls") > 0)
            {
                workbook = new HSSFWorkbook(fs);
            }
            sheet = workbook.GetSheetAt(0);
            if (sheet != null)
            {
                IRow firstRow = sheet.GetRow(0);
                cellCount = firstRow.LastCellNum;
                for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                {
                    DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
                    datatable.Columns.Add(column);
                }
                startRow = sheet.FirstRowNum + 1;
                rowCount = sheet.LastRowNum;
                for (int i = startRow; i <= rowCount; ++i)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null)
                    {
                        continue;
                    }
                    DataRow dataRow = datatable.NewRow();
                    for (int y = 0; y <= cellCount; y++)
                    {
                        if (row.GetCell(y) != null && row.GetCell(y).ToString() != String.Empty && row.GetCell(y).ToString() != "" && row.GetCell(y).ToString().Trim() != "")
                        {
                            dataRow[y] = row.GetCell(y).ToString();
                        }
                    }
                    datatable.Rows.Add(dataRow);
                }
            }
            return datatable;
        }
        catch (Exception ex)
        {
            Console.WriteLine("Exception: " + ex.Message);
            return null;
        }
    }
    #endregion
}