设备点检标准(查询、新增、编辑、删除、查看)接口开发

yl

2022-08-17 3e783ac35c550b1d823f11dacb029885a6c760fe

 VueWebApi/DLL/DAL/DeviceManagerDAL.cs

@@ -454,12 +454,13 @@
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.id,A.code,A.name,A.eqptype_code,B.name as eqptype_name,A.eqpgroup_code,G.name as eqpgroup_name,A.enable,
                            A.input_date,A.wksp_code,T.org_name as wksp_name,A.Line_code,L.org_name as Line_name,A.opc_uom,A.operation_ration,A.lm_user,A.lm_date
                            A.input_date,A.wksp_code,T.org_name as wksp_name,A.Line_code,L.org_name as Line_name,A.opc_uom,A.operation_ration,U.username as lm_user,A.lm_date
                            from TEqpInfo A
                            left join TEqpType B on A.eqptype_code=B.code
                            left join TEqpGroup G on A.eqpgroup_code=G.code
                            left join TOrganization T on A.wksp_code=T.org_code
                            left join TOrganization L on A.Line_code=L.org_code
                            left join TUser U on A.lm_user=U.usercode
                            where A.is_delete<>'1' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
@@ -643,5 +644,668 @@
            return mes;
        }
        #endregion



        #region[设备点检项列表查询]
        public static ToMessage DeviceCheckItemSearch(string checkitemcode, string checkitemname, string checkdescr, string isqrcode, string cycle, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (checkitemcode != "" && checkitemcode != null)
                {
                    search += "and code like '%'+@checkitemcode+'%' ";
                    dynamicParams.Add("@checkitemcode", checkitemcode);
                }
                if (checkitemname != "" && checkitemname != null)
                {
                    search += "and name like '%'+@checkitemname+'%' ";
                    dynamicParams.Add("@checkitemname", checkitemname);
                }
                if (checkdescr != "" && checkdescr != null)
                {
                    search += "and description=@checkdescr ";
                    dynamicParams.Add("@checkdescr", checkdescr);
                }
                if (isqrcode != "" && isqrcode != null)
                {
                    search += "and isscan=@isqrcode ";
                    dynamicParams.Add("@isqrcode", isqrcode);
                }
                if (cycle != "" && cycle != null)
                {
                    search += "and isscan=@cycle ";
                    dynamicParams.Add("@cycle", cycle);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select id,code,name,description,isscan,cycle,lm_user,lm_date 
                            from TEqpchk_Item where is_delete<>'1' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[设备点检项新增编辑]
        public static ToMessage AddUpdateDeviceCheckItem(string checkitemid, string checkitemcode, string checkitemname, string checkitemdescr, string cycle, string isqrcode, string usercode, string operType)
        {
            var dynamicParams = new DynamicParameters();
            try
            {
                if (operType == "Add")
                {
                    var sql = @"insert into TEqpchk_Item(code,name,description,isscan,cycle,lm_user,lm_date) 
                              values(@checkitemcode,@checkitemname,@checkitemdescr,@isqrcode,@cycle,@usercode,@CreateDate)";
                    dynamicParams.Add("@checkitemcode", checkitemcode);
                    dynamicParams.Add("@checkitemname", checkitemname);
                    dynamicParams.Add("@checkitemdescr", checkitemdescr);
                    dynamicParams.Add("@isqrcode", isqrcode);
                    dynamicParams.Add("@cycle", cycle);
                    dynamicParams.Add("@usercode", usercode);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "新增操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "新增操作失败!";
                        mes.data = null;
                    }
                }
                if (operType == "Update")
                {
                    var sql = @"update TEqpchk_Item set name=@checkitemname,description=@checkitemdescr,isscan=@isqrcode,cycle=@cycle,
                                lm_user=@usercode,lm_date=@CreateDate
                                where id=@checkitemid";
                    dynamicParams.Add("@checkitemid", checkitemid);
                    dynamicParams.Add("@checkitemname", checkitemname);
                    dynamicParams.Add("@checkitemdescr", checkitemdescr);
                    dynamicParams.Add("@isqrcode", isqrcode);
                    dynamicParams.Add("@cycle", cycle);
                    dynamicParams.Add("@usercode", usercode);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "修改操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "修改操作失败!";
                        mes.data = null;
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[设备点检项删除]
        public static ToMessage DeleteDeviceCheckItem(string checkitemcode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //判断点检标准项目是否有关联点检标准,不允许删除
                sql = @"select *  from TEqpchk_Deta
                        where  code=@checkitemcode";
                dynamicParams.Add("@checkitemcode", checkitemcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "当前点检项目已关联标准,不允许删除!";
                    mes.data = null;
                    return mes;
                }

                //删除点检项目
                sql = @"delete TEqpchk_Item  where code=@checkitemcode";
                list.Add(new { str = sql, parm = new { checkitemcode = checkitemcode } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "删除成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "删除失败!";
                    mes.data = null;
                }

            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion



        #region[设备保养项列表查询]
        public static ToMessage DeviceMaiItemSearch(string maiitemcode, string maiitemname, string maidescr, string isqrcode, string cycle, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (maiitemcode != "" && maiitemcode != null)
                {
                    search += "and code like '%'+@maiitemcode+'%' ";
                    dynamicParams.Add("@maiitemcode", maiitemcode);
                }
                if (maiitemname != "" && maiitemname != null)
                {
                    search += "and name like '%'+@maiitemname+'%' ";
                    dynamicParams.Add("@maiitemname", maiitemname);
                }
                if (maidescr != "" && maidescr != null)
                {
                    search += "and description=@maidescr ";
                    dynamicParams.Add("@maidescr", maidescr);
                }
                if (isqrcode != "" && isqrcode != null)
                {
                    search += "and isscan=@isqrcode ";
                    dynamicParams.Add("@isqrcode", isqrcode);
                }
                if (cycle != "" && cycle != null)
                {
                    search += "and isscan=@cycle ";
                    dynamicParams.Add("@cycle", cycle);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select id,code,name,description,isscan,cycle,lm_user,lm_date 
                            from TEqpmai_Item where is_delete<>'1' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[设备保养项新增编辑]
        public static ToMessage AddUpdateDeviceMaiItem(string maiitemid, string maiitemcode, string maiitemname, string maiitemdescr, string cycle, string isqrcode, string usercode, string operType)
        {
            var dynamicParams = new DynamicParameters();
            try
            {
                if (operType == "Add")
                {
                    var sql = @"insert into TEqpmai_Item(code,name,description,isscan,cycle,lm_user,lm_date) 
                              values(@maiitemcode,@maiitemname,@checkitemdescr,@isqrcode,@cycle,@usercode,@CreateDate)";
                    dynamicParams.Add("@maiitemcode", maiitemcode);
                    dynamicParams.Add("@maiitemname", maiitemname);
                    dynamicParams.Add("@maiitemdescr", maiitemdescr);
                    dynamicParams.Add("@isqrcode", isqrcode);
                    dynamicParams.Add("@cycle", cycle);
                    dynamicParams.Add("@usercode", usercode);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "新增操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "新增操作失败!";
                        mes.data = null;
                    }
                }
                if (operType == "Update")
                {
                    var sql = @"update TEqpmai_Item set name=@maiitemname,description=@maiitemdescr,isscan=@isqrcode,cycle=@cycle,
                                lm_user=@usercode,lm_date=@CreateDate
                                where id=@maiitemid";
                    dynamicParams.Add("@maiitemid", maiitemid);
                    dynamicParams.Add("@maiitemname", maiitemname);
                    dynamicParams.Add("@maiitemdescr", maiitemdescr);
                    dynamicParams.Add("@isqrcode", isqrcode);
                    dynamicParams.Add("@cycle", cycle);
                    dynamicParams.Add("@usercode", usercode);
                    dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
                    int cont = DapperHelper.SQL(sql, dynamicParams);
                    if (cont > 0)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "修改操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "修改操作失败!";
                        mes.data = null;
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[设备保养项删除]
        public static ToMessage DeleteDeviceMaiItem(string maiitemcode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //判断保养标准项目是否有关联保养标准,不允许删除
                sql = @"select *  from TEqpmai_Deta
                        where  code=@maiitemcode";
                dynamicParams.Add("@maiitemcode", maiitemcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "当前保养项目已关联标准,不允许删除!";
                    mes.data = null;
                    return mes;
                }

                //删除保养项目
                sql = @"delete TEqpmai_Item  where code=@maiitemcode";
                list.Add(new { str = sql, parm = new { maiitemcode = maiitemcode } });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "删除成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "删除失败!";
                    mes.data = null;
                }

            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion



        #region[设备点检标准列表查询]
        public static ToMessage DeviceCheckStandArdSearch(string checkstandcode, string checkstandname, string checkcontr, int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (checkstandcode != "" && checkstandcode != null)
                {
                    search += "and code like '%'+@checkstandcode+'%' ";
                    dynamicParams.Add("@checkstandcode", checkstandcode);
                }
                if (checkstandname != "" && checkstandname != null)
                {
                    search += "and name like '%'+@checkstandname+'%' ";
                    dynamicParams.Add("@checkstandname", checkstandname);
                }
                if (checkcontr != "" && checkcontr != null)
                {
                    search += "and iscontr=@checkcontr ";
                    dynamicParams.Add("@checkcontr", checkcontr);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select id,code,name,description,iscontr,is_checkeqp,lm_user,lm_date 
                            from TEqpchk_Main where is_delete<>'1' " + search;
                var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.count = total;
                mes.data = data.ToList();
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[设备点检标准新增、编辑获取检验项目下拉列表]
        public static ToMessage DeviceCheckItemSelect()
        {
            string sql = "";
            try
            {
                //获取设备点检项目数据
                sql = @"select code,name,description from TEqpchk_Item where is_delete<>'1' ";
                var data = DapperHelper.selecttable(sql);
                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = data;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[设备点检标准编辑/查看获取数据]
        public static ToMessage ViewDeviceCheckStanedSearch(string checkstand_code)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //获取设备点检标准信息
                sql = @"select code,name,description,iscontr
                        from TEqpchk_Main
                        where code=@checkstand_code and is_delete<>'1'";
                dynamicParams.Add("@checkstand_code", checkstand_code);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                RoutEdit rout = new RoutEdit();
                if (data.Rows.Count > 0)
                {
                    rout.code = data.Rows[0]["CODE"].ToString();
                    rout.name = data.Rows[0]["NAME"].ToString();
                    rout.description = data.Rows[0]["DESCRIPTION"].ToString();
                    rout.enable = data.Rows[0]["ISCONTR"].ToString();
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "无点检标准信息!";
                    mes.data = null;
                    return mes;
                }
                //根据设备点检标准编码获取关联的检验项目信息
                sql = @"select B.code,B.name,A.chkdesc,A.isscan,A.cycle  from TEqpchk_Deta A
                        left join TEqpchk_Item  B on A.code=B.code
                        where  A.eqpcheck_main_code=@checkstaned_code order by A.seq asc";
                dynamicParams.Add("@checkstaned_code", rout.code);
                var data0 = DapperHelper.selectdata(sql, dynamicParams);
                rout.Data = data0;

                mes.code = "200";
                mes.Message = "查询成功!";
                mes.data = rout;
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[设备点检标准新增编辑]
        public static ToMessage AddUpdateDeviceCheckStandArd(string opertype, RoutEdit json, string username)
        {
            var sql = "";
            var dynamicParams = new DynamicParameters();
            List<object> list = new List<object>();
            try
            {
                if (opertype == "Add")
                {
                    //新增设备点检标准主表
                    sql = @"insert into TEqpchk_Main(code,name,description,iscontr,lm_user,lm_date) values(@code,@name,@descr,@lm_user,@lm_date)";
                    list.Add(new { str = sql, parm = new { code = json.code, name = json.name, descr = json.description, iscontr = json.enable, lm_user = username, lm_date = DateTime.Now.ToString() } });
                    for (int i = 0; i < json.Data.Rows.Count; i++)
                    {
                        //新增设备点检标准关联点检项子表
                        sql = @"insert TEqpchk_Deta (seq,code,name,eqpchk_main_code,cycle,chkdesc,isscan,lm_user,lm_date) 
                                values(@checkitem_seq,@checkitem_code,@checkitem_name,@code,@cycle,@chkdesc,@isscan,@lm_user,@lm_date)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                checkitem_seq = Convert.ToInt32(json.Data.Rows[i]["CHECKITEM_SEQ"].ToString()),
                                checkitem_code = json.Data.Rows[i]["CHECKITEM_CODE"].ToString(),
                                checkitem_name = json.Data.Rows[i]["CHECKITEM_NAME"].ToString(),
                                code = json.code,
                                cycle = json.Data.Rows[i]["CYCLE"].ToString(),
                                chkdesc= json.Data.Rows[i]["CHECKITEM_DESCR"].ToString(),
                                isscan= json.Data.Rows[i]["ISSCAN"].ToString(),
                                lm_user = username,
                                lm_date = DateTime.Now.ToString()
                            }
                        });
                    }
                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "操作失败!";
                        mes.data = null;
                    }
                }
                if (opertype == "Update")
                {
                    //删除点检标准关联点检项目子表
                    sql = @"delete from TEqpchk_Deta where eqpchk_main_code=@checkstaned_code";
                    list.Add(new { str = sql, parm = new { checkstaned_code = json.code } });
                    //修改点检标准主表
                    sql = @"update  TEqpchk_Main set name=@checkstaned_name,description=@checkstaned_desc,iscontr=@iscontr where code=@checkstaned_code";
                    list.Add(new { str = sql, parm = new { checkstaned_code = json.code, checkstaned_name = json.name, checkstaned_desc = json.description } });
                    //新增点检标准关联点检项目子表
                    for (int i = 0; i < json.Data.Rows.Count; i++)
                    {
                        sql = @"insert TEqpchk_Deta (seq,code,name,eqpchk_main_code,cycle,chkdesc,isscan,lm_user,lm_date) 
                                values(@checkitem_seq,@checkitem_code,@checkitem_name,@code,@cycle,@chkdesc,@isscan,@lm_user,@lm_date)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                checkitem_seq = Convert.ToInt32(json.Data.Rows[i]["CHECKITEM_SEQ"].ToString()),
                                checkitem_code = json.Data.Rows[i]["CHECKITEM_CODE"].ToString(),
                                checkitem_name = json.Data.Rows[i]["CHECKITEM_NAME"].ToString(),
                                code = json.code,
                                cycle = json.Data.Rows[i]["CYCLE"].ToString(),
                                chkdesc = json.Data.Rows[i]["CHECKITEM_DESCR"].ToString(),
                                isscan = json.Data.Rows[i]["ISSCAN"].ToString(),
                                lm_user = username,
                                lm_date = DateTime.Now.ToString()
                            }
                        });
                    }

                    bool aa = DapperHelper.DoTransaction(list);
                    if (aa)
                    {
                        mes.code = "200";
                        mes.count = 0;
                        mes.Message = "操作成功!";
                        mes.data = null;
                    }
                    else
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "操作失败!";
                        mes.data = null;
                    }
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[设备点检标准删除]
        public static ToMessage DeleteDeviceCheckStaned(string checkstand_code)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                sql = @"select * from  TEqpchk_Proc_Main  where eqpchkmain_code=@checkstand_code";
                dynamicParams.Add("@checkstand_code", checkstand_code);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "当前点检标准已生成点检记录,不允许删除!";
                    mes.data = null;
                    return mes;
                }
                //删除设备点检标准主表
                sql = @"delete TEqpchk_Main  where code=@checkstand_code";
                list.Add(new { str = sql, parm = new { checkstand_code = checkstand_code } });
                //删除设备点检标准关联点检项目子表
                sql = @"delete TEqpchk_Deta  where eqpchk_main_code=@checkstand_code";
                list.Add(new { str = sql, parm = new { checkstand_code = checkstand_code } });

                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "设备点检标准删除成功!";
                    mes.data = null;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "设备点检标准删除失败!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion
    }
}