using Dapper;
|
using Microsoft.AspNetCore.Http;
|
using Newtonsoft.Json.Linq;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.IO;
|
using System.Linq;
|
using System.Threading.Tasks;
|
using VueWebCoreApi.Models;
|
using VueWebCoreApi.Tools;
|
|
namespace VueWebCoreApi.DLL.DAL
|
{
|
public class SopManageMentDAL
|
{
|
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[系统设置,设备SOP查询]
|
public static ToMessage DeviceSopSearch(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.eqptypecode,''),isnull(AA.eqptypename,''),isnull(AA.eqpcode,''),isnull(AA.eqpname,''),isnull(AA.filetypecode,''),isnull(AA.filetypename,''),isnull(AA.filename,''),isnull(AA.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.eqptypecode,AA.eqptypename,AA.eqpcode,AA.eqpname,AA.filetypecode,AA.filetypename,AA.filename,AA.version,AA.filepath,
|
AA.username,AA.lm_date,AA.rn from(
|
select top 100 A.id,A.eqptypecode,T.name as eqptypename,A.eqpcode,E.name as eqpname,A.filetypecode,
|
(case when A.filetypecode='P001' then '作业指导书' when A.filetypecode='P002' then '检验指导书' when A.filetypecode='P003' then '图纸' end) as filetypename,
|
A.filename,A.version,A.filepath,U.username,A.lm_date,
|
row_number() over(partition by A.eqptypecode,A.eqpcode,A.filetypecode order by replace(A.version,'V','') desc) rn
|
from TDeviceSop A
|
left join TEqpInfo E on A.eqpcode=E.code
|
left join TEqpType T on E.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 DeviceSopAddSeave(string eqptypecode, string device, string filename, string filetypecode, IFormFile file, User us)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
try
|
{
|
list.Clear();
|
if (file == null || file.Length <= 0)
|
{
|
mes.code = "300";
|
mes.message = "文件不能为空！";
|
}
|
else
|
{
|
//获取上传文件,组合存储路径
|
string FileName = "SOP" + DateTime.Now.ToString("yyyyMMddhhmmss");
|
string filePath = "wwwroot/DeviceSopFile/" + FileName + file.FileName; //通过此对象获取文件名(存文件地址)
|
string filePath1 = "DeviceSopFile/" + FileName + file.FileName; //通过此对象获取文件名(存数据表地址)
|
|
// 保存文件var stream = new FileStream(filePath, FileMode.Create)
|
using (var stream = new FileStream(filePath, FileMode.Create))
|
{
|
file.CopyTo(stream);
|
}
|
|
//获取设备集合
|
string[] devicecode = Array.ConvertAll<string, string>(device.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
for (int i = 0; i < devicecode.Length; i++)
|
{
|
string version = "";
|
//查询当前设备是否存在同文件类型的SOP文件,存在就增加版本号
|
sql = @"select isnull(max(substring(version,charindex('V',version)+1,len(version)-charindex('V',version))),0)+1 as version
|
from TDeviceSop
|
where eqptypecode=@eqptypecode and eqpcode=@eqpcode and filetypecode=@filetypecode";
|
dynamicParams.Add("@eqptypecode", eqptypecode);
|
dynamicParams.Add("@eqpcode", devicecode[i].ToString());
|
dynamicParams.Add("@filetypecode", filetypecode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
version = "V" + data.Rows[0]["version"].ToString();
|
}
|
else
|
{
|
version = "V1";
|
}
|
//写入设备SOP表
|
sql = @"insert into TDeviceSop(eqptypecode,eqpcode,filename,filetypecode,filepath,version,lm_user,lm_date)
|
values(@eqptypecode,@eqpcode,@filename,@filetypecode,@filepath,@version,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
eqptypecode = eqptypecode,
|
eqpcode = devicecode[i].ToString(),
|
filename = file.FileName,
|
filetypecode = filetypecode,
|
filepath = filePath1,
|
version = version,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "新增设备SOP", "SOP文件:" + file.FileName + "设备:" + device.ToString(), us.usertype);
|
mes.code = "200";
|
mes.count = 0;
|
mes.message = "新增设备SOP成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "新增设备SOP失败!";
|
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 DeviceSopDeleteSeave(string id, string eqpcode, string filepath, User us)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
try
|
{
|
list.Clear();
|
//查询当前文件出去当前删除的,是否还被其他引用
|
sql = @"select * from TDeviceSop where id<>@id and filepath=@filepath";
|
dynamicParams.Add("@id", id);
|
dynamicParams.Add("@filepath", filepath);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count <= 0)
|
{
|
// 构建完整的文件路径
|
var filePath = Path.Combine("wwwroot", filepath);
|
// 删除文件
|
System.IO.File.Delete(filePath);
|
}
|
sql = @"delete TDeviceSop where id=@id";
|
list.Add(new { str = sql, parm = new { id = id } });
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除设备SOP", "设备:" + eqpcode, us.usertype);
|
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 ProcessSopSearch(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.materielcode,''),isnull(AA.materielname,''),isnull(AA.routecode,''),isnull(AA.routename,''),isnull(AA.stepcode,''),isnull(AA.stepname,''),isnull(AA.filetypecode,''),isnull(AA.filetypename,''),isnull(AA.filename,''),isnull(AA.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.materielcode,AA.materielname,AA.routecode,AA.routename,AA.stepcode,AA.stepname,AA.filetypecode,AA.filetypename,AA.filename,AA.version,AA.filepath,
|
AA.username,AA.lm_date,AA.rn from(
|
select top 100 A.id,A.materielcode,M.partname as materielname,A.routecode,R.name as routename,A.stepcode,S.stepname,A.filetypecode,
|
(case when A.filetypecode='P001' then '作业指导书' when A.filetypecode='P002' then '检验指导书' when A.filetypecode='P003' then '图纸' end) as filetypename,
|
A.filename,A.version,A.filepath,U.username,A.lm_date,
|
row_number() over(partition by A.materielcode,A.routecode,A.stepcode,A.filetypecode order by replace(A.version,'V','') desc) rn
|
from TProcessSop A
|
left join TMateriel_Info M on A.materielcode=M.partcode
|
left join TFlw_Rout R on A.routecode=R.code
|
left join TStep S on A.stepcode=S.stepcode
|
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 ProcessSopMaxVersion(string partcode, string routecode, string stepcode, string filetypecode, User us)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
string sech = "";
|
try
|
{
|
dynamic dynObj = JObject.Parse(us.mesSetting);
|
bool route = dynObj.route;
|
if (route) //按工艺路线模式
|
{
|
sech += " and routecode=@routecode and stepcode=@stepcode";
|
dynamicParams.Add("@routecode", routecode);
|
dynamicParams.Add("@stepcode", stepcode);
|
}
|
else //工序模式
|
{
|
sech += " and stepcode=@stepcode";
|
dynamicParams.Add("@stepcode", stepcode);
|
}
|
//获取SOP文件作业文件上传获取最大版本号
|
sql = @"select isnull(max(substring(version,charindex('V',version)+1,len(version)-charindex('V',version))),0)+1 as file_version
|
from TProcessSop where materielcode=@materielcode and filetypecode=@filetypecode "+sech;
|
dynamicParams.Add("@materielcode", partcode);
|
dynamicParams.Add("@filetypecode", filetypecode);
|
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 ProcessSopAddSeave(string partcode, string routecode, string stepcode, string filename, string filetypecode, string version, IFormFile file, User us)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
try
|
{
|
list.Clear();
|
if (file == null || file.Length <= 0)
|
{
|
mes.code = "300";
|
mes.message = "文件不能为空！";
|
}
|
else
|
{
|
//获取上传文件,组合存储路径
|
string FileName = "SOP" + DateTime.Now.ToString("yyyyMMddhhmmss");
|
string filePath = "wwwroot/ProcessSopFile/" + FileName + file.FileName; //通过此对象获取文件名(存文件地址)
|
string filePath1 = "ProcessSopFile/" + FileName + file.FileName; //通过此对象获取文件名(存数据表地址)
|
|
// 保存文件var stream = new FileStream(filePath, FileMode.Create)
|
using (var stream = new FileStream(filePath, FileMode.Create))
|
{
|
file.CopyTo(stream);
|
}
|
//写入设备SOP表
|
sql = @"insert into TProcessSop(materielcode,routecode,stepcode,filetypecode,filename,filepath,version,lm_user,lm_date)
|
values(@materielcode,@routecode,@stepcode,@filetypecode,@filename,@filepath,@version,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
materielcode = partcode,
|
routecode = routecode,
|
stepcode = stepcode,
|
filetypecode = filetypecode,
|
filename = file.FileName,
|
filepath = filePath1,
|
version = version,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "新增工艺SOP", "产品："+partcode+" SOP文件:" + file.FileName + " 工艺路线:" + routecode ?? "" + " 工序:" + stepcode.ToString(), us.usertype);
|
mes.code = "200";
|
mes.count = 0;
|
mes.message = "新增设备SOP成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "新增设备SOP失败!";
|
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 ProcessSopDeleteSeave(string id, string partcode, string routecode, string stepcode, string filepath, User us)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
try
|
{
|
list.Clear();
|
//查询当前文件出去当前删除的,是否还被其他引用
|
sql = @"select * from TProcessSop where id<>@id and filepath=@filepath";
|
dynamicParams.Add("@id", id);
|
dynamicParams.Add("@filepath", filepath);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count <= 0)
|
{
|
// 构建完整的文件路径
|
var filePath = Path.Combine("wwwroot", filepath);
|
// 删除文件
|
System.IO.File.Delete(filePath);
|
}
|
sql = @"delete TProcessSop where id=@id";
|
list.Add(new { str = sql, parm = new { id = id } });
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除工艺SOP", "产品:" + partcode+" SOP文件地址："+filepath+" 工艺路线:"+ routecode ?? "" + " 工序:"+stepcode, us.usertype);
|
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
|
|
}
|
}
|
