From 10efbcfce9f0f17982f4714e76e8cad24fb8f982 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期二, 06 十二月 2022 09:25:16 +0800
Subject: [PATCH] SOP文件下拉列表排序
---
VueWebApi/DLL/DAL/SystemSettingDAL.cs | 426 ++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 420 insertions(+), 6 deletions(-)
diff --git a/VueWebApi/DLL/DAL/SystemSettingDAL.cs b/VueWebApi/DLL/DAL/SystemSettingDAL.cs
index 0f1d7f0..8bdcfbb 100644
--- a/VueWebApi/DLL/DAL/SystemSettingDAL.cs
+++ b/VueWebApi/DLL/DAL/SystemSettingDAL.cs
@@ -35,7 +35,7 @@
{
search += "and B.prefix like '%'+@prefix+'%' ";
dynamicParams.Add("@prefix", prefix);
- }
+ }
if (search == "")
{
search = "and 1=1 ";
@@ -43,9 +43,9 @@
//search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
- var sql = @"select A.right_code,A.right_name,B.prefix,B.filingdate,B.incbit from TRight A
+ var sql = @"select A.right_code,A.right_name,B.prefix,B.filingdate,B.incbit,A.Type from TRight A
left join T_CodeRules B on A.right_code=B.rightcode
- where "+search+" and A.Type='PC' and A.parent_id<>'0' order by A.right_code,A.right_seq ";
+ where A.parent_id<>'0' and A.is_delete<>'1' " + search + "";
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
mes.code = "200";
mes.Message = "鏌ヨ鎴愬姛!";
@@ -64,7 +64,7 @@
#endregion
#region[缂栫爜瑙勫垯缂栬緫淇濆瓨]
- public static ToMessage SaveEncodingRules(string rightcode,string rightname, string prefix, string filingdate, string incbit, string lm_user)
+ public static ToMessage SaveEncodingRules(string rightcode, string rightname, string prefix, string filingdate, string incbit, string lm_user)
{
var sql = "";
List<object> list = new List<object>();
@@ -76,7 +76,7 @@
var data = DapperHelper.selectdata(sql, dynamicParams);
if (data.Rows.Count > 0)
{
- sql = @"update T_CodeRules set prefix=@prefix,filingdate=@filingdate,incbit=@incbit,lm_user=@lm_user,lm_date=@CreateDate where right_code=@rightcode";
+ sql = @"update T_CodeRules set prefix=@prefix,filingdate=@filingdate,incbit=@incbit,lm_user=@lm_user,lm_date=@CreateDate where rightcode=@rightcode";
dynamicParams.Add("@prefix", prefix);
dynamicParams.Add("@filingdate", filingdate);
dynamicParams.Add("@incbit", incbit);
@@ -99,7 +99,7 @@
mes.data = null;
}
}
- else
+ else
{
//棣栨鍐欏叆鍓嶆寜浣嶆暟琛ュ厖0
string value = "";
@@ -153,5 +153,419 @@
return mes;
}
#endregion
+
+
+ #region[杞﹂棿鍏憡鍒楄〃]
+ public static ToMessage SystemAnnouncementSearch(string wkspcode, string ancetitle, string ancecont, string cancel, string level, int startNum, int endNum, string prop, string order)
+ {
+ var dynamicParams = new DynamicParameters();
+ string search = "";
+ try
+ {
+ if (wkspcode != "" && wkspcode != null)
+ {
+ search += "and A.wksp_code=@wkspcode ";
+ dynamicParams.Add("@wkspcode", wkspcode);
+ }
+ if (ancetitle != "" && ancetitle != null)
+ {
+ search += "and A.title like '%'+@ancetitle+'%' ";
+ dynamicParams.Add("@ancetitle", ancetitle);
+ }
+ if (ancecont != "" && ancecont != null)
+ {
+ search += "and A.contents like '%'+@ancecont+'%' ";
+ dynamicParams.Add("@ancecont", ancecont);
+ }
+ if (cancel != "" && cancel != null)
+ {
+ search += "and A.if_cancel=@cancel ";
+ dynamicParams.Add("@cancel", cancel);
+ }
+ if (level != "" && level != null)
+ {
+ search += "and A.priority=@level ";
+ dynamicParams.Add("@level", level);
+ }
+ if (search == "")
+ {
+ search = "and 1=1 ";
+ }
+ //search = search.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select A.id,A.wksp_code,T.org_name as wksp_name,A.title,A.contents,U.username as lm_user,A.lm_date,A.if_cancel,A.cancel_date,A.priority
+ from TSystemAnnouncement A
+ left join TOrganization T on A.wksp_code=T.org_code
+ left join TUser U on A.lm_user=U.usercode
+ where T.description='W' and A.is_delete='0' " + 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 SystemAnnouncementAddUpdate(string id, DataTable dt, string ancetitle, string ancecont, string cancel, string level, string username, string opertype)
+ {
+ var sql = "";
+ var dynamicParams = new DynamicParameters();
+ List<object> list = new List<object>();
+ string cancel_date = "";
+ try
+ {
+ if (cancel == "Y")
+ {
+ cancel_date = DateTime.Now.ToString();
+ }
+ if (opertype == "Add")
+ {
+ for (int i = 0; i < dt.Rows.Count; i++)
+ {
+ //鍐欏叆宸ュ崟琛�
+ sql = @"insert into TSystemAnnouncement(wksp_code,title,contents,if_cancel,cancel_date,lm_user,lm_date,priority)
+ values(@wksp_code,@title,@contents,@if_cancel,@cancel_date,@lm_user,@lm_date,@priority)";
+ list.Add(new
+ {
+ str = sql,
+ parm = new
+ {
+ wksp_code = dt.Rows[i]["WKSP_CODE"].ToString(),
+ title = ancetitle,
+ contents = ancecont,
+ if_cancel = cancel,
+ cancel_date = cancel_date,
+ lm_user = username,
+ lm_date = DateTime.Now.ToString(),
+ priority = level
+ }
+ });
+ }
+
+ 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 = @"update TSystemAnnouncement set wksp_code=@wksp_code,title=@title,contents=@contents,if_cancel=@if_cancel,cancel_date=@cancel_date,lm_user=@lm_user,lm_date=@lm_date,priority=@priority where id=@id";
+ list.Add(new
+ {
+ str = sql,
+ parm = new
+ {
+ id = id,
+ wksp_code = dt.Rows[0]["WKSP_CODE"].ToString(),
+ title = ancetitle,
+ contents = ancecont,
+ if_cancel = cancel,
+ cancel_date = cancel_date,
+ lm_user = username,
+ lm_date = DateTime.Now.ToString(),
+ priority = level
+ }
+ });
+ 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 SystemAnnouncementDelete(string id)
+ {
+ var sql = "";
+ List<object> list = new List<object>();
+ var dynamicParams = new DynamicParameters();
+ try
+ {
+ list.Clear();
+ sql = @"delete TSystemAnnouncement where id=@id";
+ list.Add(new { str = sql, parm = new { id = id } });
+
+ 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[SOP鍒楄〃鏌ヨ]
+ public static ToMessage SystemSopSearch(string search, string check, int startNum, int endNum, string prop, string order)
+ {
+ var dynamicParams = new DynamicParameters();
+ string sech = "";
+ try
+ {
+ if (search != "" && search != null)
+ {
+ sech += "and concat(isnull(AA.partcode,''),isnull(AA.partname,''),isnull(AA.partspec,''),isnull(AA.eqptype_name,''),isnull(AA.filetype_name,''),isnull(AA.define_name,''),isnull(AA.file_version,''),isnull(AA.username,'')) like '%'+@search+'%' ";
+ dynamicParams.Add("@search", search);
+ }
+ if (check == "Y")
+ {
+ sech += "and AA.rn=1 ";
+ dynamicParams.Add("@rn", 1);
+ }
+
+ if (sech == "")
+ {
+ sech = "and 1=1 ";
+ }
+ sech = sech.Substring(3);//鎴彇绱㈠紩2鍚庨潰鐨勫瓧绗�
+ // --------------鏌ヨ鎸囧畾鏁版嵁--------------
+ var total = 0; //鎬绘潯鏁�
+ var sql = @"select AA.id,AA.partcode,AA.partname,AA.partspec,AA.eqptype_code,AA.eqptype_name,AA.filetype_code,AA.filetype_name,AA.file_name,AA.define_name,AA.file_version,AA.file_path,
+ AA.username,AA.lm_date,AA.rn from(
+ select top 100 A.id,M.partcode,M.partname,M.partspec,A.eqptype_code,T.name as eqptype_name,A.filetype_code,
+ (case when A.filetype_code='P001' then '浣滀笟鎸囧涔�' when A.filetype_code='P002' then '妫�楠屾寚瀵间功' when A.filetype_code='P003' then '鍥剧焊' end) as filetype_name,
+ A.file_name,A.define_name,A.file_version,A.file_path,U.username,A.lm_date,
+ row_number() over(partition by A.materiel_code,A.filetype_code order by replace(A.file_version,'V','') desc) rn
+ from TK_Sop_Matfile A
+ left join TMateriel_Info M on A.materiel_code=M.partcode
+ left join TEqpType T on A.eqptype_code=T.code
+ left join TUser U on A.lm_user=U.usercode
+ ) as AA where " + sech + "";
+ 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[SOP鏂囦欢涓婁紶鏌ユ壘璁惧绫诲瀷鏁版嵁]
+ public static ToMessage SystemSopDeviceSearch()
+ {
+ string sql = "";
+ try
+ {
+ //鑾峰彇璁惧绫诲瀷
+ sql = @"select code,name from TEqpType order by code";
+ 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[SOP鏂囦欢浣滀笟鏂囦欢涓婁紶鑾峰彇鏈�澶х増鏈彿]
+ public static ToMessage SystemSopMaxVersion(string devicetypecode, string filetypecode, string partcode)
+ {
+ string sql = "";
+ var dynamicParams = new DynamicParameters();
+ try
+ {
+ //鑾峰彇SOP鏂囦欢浣滀笟鏂囦欢涓婁紶鑾峰彇鏈�澶х増鏈彿
+ sql = @"select isnull(max(substring(file_version,charindex('V',file_version)+1,len(file_version)-charindex('V',file_version))),0)+1 as file_version
+ from TK_Sop_Matfile where materiel_code=@partcode and eqptype_code=@devicetypecode and filetype_code=@filetypecode ";
+ dynamicParams.Add("@devicetypecode", devicetypecode);
+ dynamicParams.Add("@filetypecode", filetypecode);
+ dynamicParams.Add("@partcode", partcode);
+ var data = DapperHelper.selectdata(sql, dynamicParams);
+ if (data.Rows.Count > 0)
+ {
+ mes.code = "200";
+ mes.Message = "鑾峰彇鐗堟湰鍙锋垚鍔�!";
+ mes.data = "V" + data.Rows[0]["file_version"].ToString();
+ }
+ else
+ {
+ mes.code = "300";
+ 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[SOP鏂囦欢涓婁紶鎻愪氦]
+ public static ToMessage SystemSopSava(string devicetypecode, string filetypecode, string partcode, string fileversion,string file_name, string defilename, string username, string strPath)
+ {
+ var sql = "";
+ List<object> list = new List<object>();
+ try
+ {
+ //鍐欏叆SOP鏂囦欢琛�
+ sql = @"insert into TK_Sop_Matfile(materiel_code,eqptype_code,filetype_code,file_name,define_name,file_version,file_path,lm_user,lm_date)
+ values(@materiel_code,@eqptype_code,@filetype_code,@file_name,@define_name,@file_version,@file_path,@lm_user,@lm_date)";
+ list.Add(new
+ {
+ str = sql,
+ parm = new
+ {
+ materiel_code = partcode,
+ eqptype_code = devicetypecode,
+ filetype_code = filetypecode,
+ file_name = file_name,
+ define_name = defilename,
+ file_version= fileversion,
+ file_path= strPath,
+ 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[SOP鏂囦欢鍒犻櫎]
+ public static ToMessage SystemSopDelete(string id, string filepath)
+ {
+ var sql = "";
+ List<object> list = new List<object>();
+ try
+ {
+ list.Clear();
+ sql = @"delete TK_Sop_Matfile where id=@id";
+ list.Add(new { str = sql, parm = new { id = id } });
+ bool aa = DapperHelper.DoTransaction(list);
+ string FilePath = System.Web.HttpContext.Current.Server.MapPath(filepath);//鑾峰彇鏂囦欢璺緞
+ if (System.IO.File.Exists(FilePath))//鍒ゆ柇鏂囦欢鏄惁瀛樺湪
+ {
+ System.IO.File.Delete(FilePath);//鎵цIO鏂囦欢鍒犻櫎,闇�寮曞叆鍛藉悕绌洪棿System.IO;
+ }
+ 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
+
}
}
\ No newline at end of file
--
Gitblit v1.9.3