using Dapper;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Web;
|
using VueWebApi.Tools;
|
|
namespace VueWebApi.DLL.DAL
|
{
|
public class SystemSettingDAL
|
{
|
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 EncodingRules(string rightname, string prefix, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (rightname != "" && rightname != null)
|
{
|
search += "and A.right_name like '%'+@rightname+'%' ";
|
dynamicParams.Add("@rightname", rightname);
|
}
|
if (prefix != "" && prefix != null)
|
{
|
search += "and B.prefix like '%'+@prefix+'%' ";
|
dynamicParams.Add("@prefix", prefix);
|
}
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
//search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
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 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 = "查询成功!";
|
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 SaveEncodingRules(string rightcode, string rightname, string prefix, string filingdate, string incbit, string lm_user)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select rightcode from T_CodeRules where rightcode=@rightcode";
|
dynamicParams.Add("@rightcode", rightcode);
|
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 rightcode=@rightcode";
|
dynamicParams.Add("@prefix", prefix);
|
dynamicParams.Add("@filingdate", filingdate);
|
dynamicParams.Add("@incbit", incbit);
|
dynamicParams.Add("@lm_user", lm_user);
|
dynamicParams.Add("@CreateDate", DateTime.Now.ToString());
|
dynamicParams.Add("@rightcode", rightcode);
|
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;
|
}
|
}
|
else
|
{
|
//首次写入前按位数补充0
|
string value = "";
|
for (int i = 0; i < int.Parse(incbit); i++)
|
{
|
value = value + "0";
|
}
|
//写入规则表
|
sql = @"insert into T_CodeRules(rightcode,rightname,prefix,filingdate,incbit,value,lm_user,lm_date)
|
values(@rightcode,@rightname,@prefix,@filingdate,@incbit,@value,@lm_user,@lm_date)";
|
dynamicParams.Add("@rightcode", rightcode);
|
dynamicParams.Add("@rightname", rightname);
|
dynamicParams.Add("@prefix", prefix);
|
dynamicParams.Add("@filingdate", filingdate);
|
dynamicParams.Add("@incbit", incbit);
|
dynamicParams.Add("@value", value);
|
dynamicParams.Add("@lm_user", lm_user);
|
dynamicParams.Add("@lm_date", 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 NewEncodingRules(string rightcode)
|
{
|
mes = SeachEncode.EncodingSeach(rightcode);
|
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
|
|
}
|
}
|
