From 7a0d98456b4ecaf1867cd62ecb7c8169ec15d042 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期三, 22 六月 2022 18:20:34 +0800
Subject: [PATCH] 设备清单查询、新增、编辑、删除接口开发 设备类型(新增、删除)、设备组(新增、删除)接口开发 仓库定义(新增、编辑、删除)、库位定义(新增、编辑、删除) 存货类型、存货档案接口开发
---
VueWebApi/DLL/DAL/DeviceManagerDAL.cs | 587 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++-
1 files changed, 576 insertions(+), 11 deletions(-)
diff --git a/VueWebApi/DLL/DAL/DeviceManagerDAL.cs b/VueWebApi/DLL/DAL/DeviceManagerDAL.cs
index 2dce225..1745424 100644
--- a/VueWebApi/DLL/DAL/DeviceManagerDAL.cs
+++ b/VueWebApi/DLL/DAL/DeviceManagerDAL.cs
@@ -1,54 +1,619 @@
-锘縰sing System;
+锘縰sing 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
}
--
Gitblit v1.9.3