设备清单查询、新增、编辑、删除接口开发 设备类型(新增、删除)、设备组(新增、删除)接口开发 仓库定义(新增、编辑、删除)、库位定义(新增、编辑、删除...

yl

2022-06-22 7a0d98456b4ecaf1867cd62ecb7c8169ec15d042

 VueWebApi/DLL/DAL/DeviceManagerDAL.cs

@@ -1,54 +1,619 @@
using System;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using VueWebApi.Models;
using VueWebApi.Tools;

namespace VueWebApi.DLL.DAL
{
    public class DeviceManagerDAL
    {
        public static DataTable dt;    //定义全局变量dt
        public static bool res;       //定义全局变量dt
        public static ToMessage mes = new ToMessage(); //定义全局返回信息对象
        public static string strProcName = ""; //定义全局sql变量
        public static List<SqlParameter> listStr = new List<SqlParameter>(); //定义全局参数集合
        public static SqlParameter[] parameters; //定义全局SqlParameter参数数组

        #region[设备类型查询]
        public static ToMessage DeviceTypeSearch(int startNum, int endNum, string prop, string order)
        {
            throw new NotImplementedException();
            var dynamicParams = new DynamicParameters();
            try
            {
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select id, code,name,remark 
                            from TEqpType 
                            where  is_delete<>'1' ";
                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 AddUpdateDeviceType(string devicetypecode, string devicetypename, string devicetypedescipt, string usercode)
        public static ToMessage AddUpdateDeviceType(List<ObjectDataCont> json, string username)
        {
            throw new NotImplementedException();
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //循环写入设备类型表
                for (int i = 0; i < json.Count; i++)
                {
                    sql = @"insert into TRoleType(code,name,lm_user,lm_date,remark) values(@code,@name,@username,@createdate,@description)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = json[i].code,
                            name = json[i].name,
                            username = username,
                            createdate = DateTime.Now.ToString(),
                            description = json[i].description
                        }
                    });
                }
                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 DeleteDeviceType(string devicetypeid)
        public static ToMessage DeleteDeviceType(string devicetypecode)
        {
            throw new NotImplementedException();
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //判断设备类型下是否关联设备组
                sql = @"select *  from TEqpGroup where eqptype_code=@devicetypecode and is_delete<>'1'";
                dynamicParams.Add("@devicetypecode", devicetypecode);
                var data = DapperHelper.select<TreeObejct>(sql, dynamicParams);
                if (data.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "当前设备类型已有关联设备组,不允许删除!";
                    mes.data = null;
                }
                else
                {
                    //判断设备有无关联设备类型
                    sql = @"select *  from TEqpInfo where eqptype_code=@devicetypecode and is_delete<>'1'";
                    dynamicParams.Add("@devicetypecode", devicetypecode);
                    var data0 = DapperHelper.select<TreeObejct>(sql, dynamicParams);
                    if (data0.Count > 0)
                    {
                        mes.code = "300";
                        mes.count = 0;
                        mes.Message = "当前设备类型已有关联设备组,不允许删除!";
                        mes.data = null;
                    }
                    else
                    {
                        sql = @"delete TEqpType where code=@devicetypecode";
                        list.Add(new { str = sql, parm = new { devicetypecode = devicetypecode } });
                        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 DeviceTypeSelect()
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            List<TreeObejct> list = new List<TreeObejct>();
            try
            {
                //获取设备类型数据
                sql = @"select code,name from TEqpType 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 DeviceGroupSearch(int startNum, int endNum, string prop, string order)
        {
            var dynamicParams = new DynamicParameters();
            try
            {
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select A.code,A.name,A.eqptype_code,B.name as eqptype_name,A.remark  
                            from TEqpGroup A
                            inner join TEqpType B on A.eqptype_code=B.code 
                            where  A.is_delete<>'1' ";
                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 AddUpdateDeviceGroup(List<ObjectDataCont> json, string username)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //循环写入设备类型表
                for (int i = 0; i < json.Count; i++)
                {
                    sql = @"insert into TEqpGroup(code,name,lm_user,lm_date,remark,eqptype_code) values(@code,@name,@username,@createdate,@description,@eqptype_code)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            code = json[i].code,
                            name = json[i].name,
                            username = username,
                            createdate = DateTime.Now.ToString(),
                            description = json[i].description,
                            eqptype_code = json[i].group
                        }
                    });
                }
                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 DeleteDeviceGroup(string devicegroupcode)
        {
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //判断设备组下是否关联设备
                sql = @"select *  from TEqpInfo where eqpgroup_code=@devicegroupcode and is_delete<>'1'";
                dynamicParams.Add("@devicegroupcode", devicegroupcode);
                var data = DapperHelper.select<TreeObejct>(sql, dynamicParams);
                if (data.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "当前设备组已有关联设备组,不允许删除!";
                    mes.data = null;
                }
                else
                {
                    //删除设备组
                    sql = @"delete TEqpGroup where code=@devicegroupcode";
                    list.Add(new { str = sql, parm = new { devicegroupcode = devicegroupcode } });
                    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 DeviceTypeSelectGroup(string eqptypecode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            List<TreeObejct> list = new List<TreeObejct>();
            try
            {
                //获取设备类型数据
                sql = @"select code,name from TEqpGroup where eqptype_code=@eqptypecode and is_delete<>'1'";
                dynamicParams.Add("@eqptypecode", eqptypecode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                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 WorkShopSelect()
        {
            string sql = "";
            try
            {
                //获取设备类型数据
                sql = @"select org_code as code,org_name as name from TOrganization where description='W' and 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 DeviceMangerSearch(string deviceCode, string deviceName, string status, string workShop, string deviceType, string deviceGroup, int startNum, int endNum, string prop, string order)
        {
            throw new NotImplementedException();
            var dynamicParams = new DynamicParameters();
            string search = "";
            try
            {
                if (deviceCode != "" && deviceCode != null)
                {
                    search += "and A.code like '%'+@deviceCode+'%' ";
                    dynamicParams.Add("@deviceCode", deviceCode);
                }
                if (deviceName != "" && deviceName != null)
                {
                    search += "and A.name like '%'+@deviceName+'%' ";
                    dynamicParams.Add("@deviceName", deviceName);
                }
                if (status != "" && status != null)
                {
                    search += "and A.status=@status ";
                    dynamicParams.Add("@status", status);
                }
                if (workShop != "" && workShop != null)
                {
                    search += "and A.wksp_code=@workShop ";
                    dynamicParams.Add("@workShop", workShop);
                }
                if (deviceType != "" && deviceType != null)
                {
                    search += "and A.eqptype_code=@deviceType ";
                    dynamicParams.Add("@deviceType", deviceType);
                }
                if (deviceGroup != "" && deviceGroup != null)
                {
                    search += "and A.eqpgroup_code=@deviceGroup ";
                    dynamicParams.Add("@deviceGroup", deviceGroup);
                }
                if (search == "")
                {
                    search = "and 1=1 ";
                }
                //search = search.Substring(3);//截取索引2后面的字符
                // --------------查询指定数据--------------
                var total = 0; //总条数
                var sql = @"select 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
                            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
                            where A.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 AddUpdateDeviceManger(string deviceid, string devicecode, string devicename, string devicetype, string devicegroup, string importdate, string workshop, string status, string ratio, string usercode, string operType)
        public static ToMessage AddUpdateDeviceManger(string deviceid, string devicecode, string devicename, string devicetype, string devicegroup, string importdate, string workshop, string linecode, string status, string ratio, string usercode, string operType)
        {
            throw new NotImplementedException();
            var dynamicParams = new DynamicParameters();
            try
            {
                if (operType == "Add")
                {
                    var sql = @"insert into TEqpInfo(code,name,eqptype_code,eqpgroup_code,wksp_code,line_code,input_date,operation_ration,enable,opc_uom,lm_user,lm_date) 
                            values(@devicecode,@devicename,@devicetype,@devicegroup,@workshop,@linecode,@importdate,@ratio,@status,@uom,@usercode,@CreateDate)";
                    dynamicParams.Add("@devicecode", devicecode);
                    dynamicParams.Add("@devicename", devicename);
                    dynamicParams.Add("@devicetype", devicetype);
                    dynamicParams.Add("@devicegroup", devicegroup);
                    dynamicParams.Add("@workshop", workshop);
                    dynamicParams.Add("@linecode", linecode);
                    dynamicParams.Add("@importdate", importdate);
                    dynamicParams.Add("@ratio", ratio);
                    dynamicParams.Add("@status", status);
                    dynamicParams.Add("@uom", "台");
                    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 TEqpInfo set name=@devicename,eqptype_code=@devicetype,eqpgroup_code=@devicegroup,input_date=@importdate,
                                wksp_code=@workshop,line_code=@linecode,enable=@status,operation_ration=@ratio,lm_user=@usercode,lm_date=@CreateDate,
                                where id=@deviceid";
                    dynamicParams.Add("@deviceid", deviceid);
                    dynamicParams.Add("@devicename", devicename);
                    dynamicParams.Add("@devicetype", devicetype);
                    dynamicParams.Add("@devicegroup", devicegroup);
                    dynamicParams.Add("@workshop", workshop);
                    dynamicParams.Add("@linecode", linecode);
                    dynamicParams.Add("@importdate", importdate);
                    dynamicParams.Add("@ratio", ratio);
                    dynamicParams.Add("@status", status);
                    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 DeleteDeviceManger(string deviceid)
        public static ToMessage DeleteDeviceManger(string devicecode)
        {
            throw new NotImplementedException();
            var sql = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                //判断设备是否有关联工艺路线的生产订单(未关闭),不允许删除
                sql = @"select A.code,M.wo_code,R.name  from TEqpInfo A
                        inner join TFlw_Rteqp B on A.code=B.eqp_code 
                        inner join TStep C on B.step_code=C.stepcode 
                        inner join TFlw_Rtdt D on C.stepcode=D.step_code
                        inner join TFlw_Rout R on D.rout_code=R.code 
                        inner join TK_Wrk_Man M on R.code=M.route_code 
                        where  A.code=@devicecode";
                dynamicParams.Add("@devicecode", devicecode);
                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 = @"select *  from TFlw_Rteqp
                        where eqp_code=@devicecode";
                dynamicParams.Add("@devicecode", devicecode);
                var data0 = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "当前设备已关联工序,不允许删除!";
                    mes.data = null;
                    return mes;
                }
                //判断设备是否指定节拍工价,不允许删除
                sql = @"select *  from TPrteEqp_Stad
                        where eqp_code=@devicecode";
                dynamicParams.Add("@devicecode", devicecode);
                var data1 = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "当前设备已指定节拍工价,不允许删除!";
                    mes.data = null;
                    return mes;
                }
                //删除设备
                sql = @"update TEqpInfo set is_delete='1' where code=@devicecode";
                list.Add(new { str = sql, parm = new { devicecode = devicecode } });
                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
    }