using Dapper;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Threading.Tasks;
|
using VueWebCoreApi.Models;
|
using VueWebCoreApi.Models.WorkData;
|
using VueWebCoreApi.Tools;
|
|
namespace VueWebCoreApi.DLL.DAL
|
{
|
public class WorkOrderDAL
|
{
|
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[ERP订单查询]
|
public static ToMessage ErpOrderSearch(string erporderstus, string erpordercode, string saleordercode, string partcode, string partname, string partspec, int startNum, string paydatestartdate, string paydateenddate, string paydatestartdate1, string paydateenddate2, string creatuser, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (erporderstus != "" && erporderstus != null)
|
{
|
search += "and A.status=@erporderstus ";
|
dynamicParams.Add("@erporderstus", erporderstus);
|
}
|
if (erpordercode != "" && erpordercode != null)
|
{
|
search += "and A.wo like '%'+@erpordercode+'%' ";
|
dynamicParams.Add("@erpordercode", erpordercode);
|
}
|
if (saleordercode != "" && saleordercode != null)
|
{
|
search += "and A.saleOrderCode like '%'+@saleordercode+'%' ";
|
dynamicParams.Add("@saleordercode", saleordercode);
|
}
|
if (partcode != "" && partcode != null)
|
{
|
search += "and A.materiel_code like '%'+@partcode+'%' ";
|
dynamicParams.Add("@partcode", partcode);
|
}
|
if (partname != "" && partname != null)
|
{
|
search += "and B.partname like '%'+@partname+'%' ";
|
dynamicParams.Add("@partname", partname);
|
}
|
if (partspec != "" && partspec != null)
|
{
|
search += "and B.partspec like '%'+@partspec+'%' ";
|
dynamicParams.Add("@partspec", partspec);
|
}
|
if (paydatestartdate != "" && paydatestartdate != null)
|
{
|
search += "and A.planstartdate between @paydatestartdate and @paydateenddate ";
|
dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00");
|
dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59");
|
}
|
if (paydatestartdate1 != "" && paydatestartdate1 != null)
|
{
|
search += "and A.planenddate between @paydatestartdate1 and @paydateenddate2 ";
|
dynamicParams.Add("@paydatestartdate1", paydatestartdate1);
|
dynamicParams.Add("@paydateenddate2", paydateenddate2 + " 23:59:59");
|
}
|
if (creatuser != "" && creatuser != null)
|
{
|
search += "and U.username like '%'+@creatuser+'%' ";
|
dynamicParams.Add("@creatuser", creatuser);
|
}
|
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.id, A.status,A.wo,A.materiel_code as partcode,B.partname,B.partspec,A.qty,A.relse_qty,A.wkshp_code,C.torg_name as wkshp_name,
|
A.stck_code,D.name as stck_name,A.saleOrderCode,A.saleOrderDeliveryDate,A.planstartdate,A.planenddate,U.username as createuser,A.createdate,A.sbid
|
from TKimp_Ewo A
|
left join TMateriel_Info B on A.materiel_code=B.partcode
|
left join TOrganization C on A.wkshp_code=C.torg_code
|
left join TSecStck D on A.stck_code=D.code
|
left join TUser U on A.createuser=U.usercode
|
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[ERP订单下达]
|
public static ToMessage MarkSaveErpOrder(string erporderid, string sbid, string erpordercode, string saleordercode, string partcode, string wkshopcode, string warehousecode, string erpqty, string markqty, string ordernum, string relse_qty, string saleOrderDeliveryDate, User us)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
|
try
|
{
|
list.Clear();
|
//获取拆单数量:向下取整
|
decimal cdqty = Math.Floor(decimal.Parse(markqty) / decimal.Parse(ordernum));
|
//定义累计下单数量
|
decimal sumqty = 0;
|
//定义最新生成的工单号
|
string wo = "";
|
//定义工单流水号
|
int num = 0;
|
//循环下单单数(生成对应几张MES工单)
|
for (int i = 1; i <= Convert.ToInt32(ordernum); i++)
|
{
|
sumqty += cdqty;
|
//获取最大单据号
|
if (i == 1) //首单获取工单号
|
{
|
sql = @"select isnull(max(cast(substring(wo_code,charindex('_',wo_code)+1,len(wo_code)-charindex('_',wo_code)) as numeric)),0)+1 as worknumb
|
from TK_Wrk_Man where m_po=@erpordercode";
|
dynamicParams.Add("@erpordercode", erpordercode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
num = Convert.ToInt32(data.Rows[0]["WORKNUMB"].ToString());
|
wo = erpordercode + "_" + num;
|
}
|
else
|
{
|
num = num + 1;
|
wo = erpordercode + "_" + num;
|
}
|
if (i == Convert.ToInt32(ordernum)) //最后一单时
|
{
|
sql = @"insert into TK_Wrk_Man(wo_code,wotype,status,wkshp_code,plan_qty,stck_code,sbid,materiel_code,sourceid,m_po,lm_user,lm_date,saleOrderCode,saleOrderDeliveryDate,data_sources,isstep) values(@wo_code,@wotype,@status,@wkshp_code,@plan_qty,@stck_code,@sbid,@materiel_code,@sourceid,@m_po,@username,@CreateDate,@saleOrderCode,@saleOrderDeliveryDate,@data_sources,@isstep)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = wo,
|
wotype = "PO",
|
status = "NEW",
|
wkshp_code = wkshopcode,
|
plan_qty = cdqty + (decimal.Parse(markqty) - sumqty), //末单下单数量=切分数量+(下单数量-累计切分下单数量)
|
stck_code = warehousecode,
|
sbid = sbid,
|
materiel_code = partcode,
|
sourceid = erporderid,
|
m_po = erpordercode,
|
username = us.usercode,
|
CreateDate = DateTime.Now.ToString(),
|
saleOrderCode = saleordercode,
|
saleOrderDeliveryDate = Convert.ToDateTime(saleOrderDeliveryDate),
|
data_sources = "ERP",
|
isstep="N" //是否关联工序
|
}
|
});
|
sumqty = sumqty + (decimal.Parse(markqty) - sumqty);
|
}
|
else
|
{
|
|
sql = @"insert into TK_Wrk_Man(wo_code,wotype,status,wkshp_code,plan_qty,stck_code,sbid,materiel_code,sourceid,m_po,lm_user,lm_date,saleOrderCode,saleOrderDeliveryDate,data_sources,isstep) values(@wo_code,@wotype,@status,@wkshp_code,@plan_qty,@stck_code,@sbid,@materiel_code,@sourceid,@m_po,@username,@CreateDate,@saleOrderCode,@saleOrderDeliveryDate,@data_sources,@isstep)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = wo,
|
wotype = "PO",
|
status = "NEW",
|
wkshp_code = wkshopcode,
|
plan_qty = cdqty,
|
stck_code = warehousecode,
|
sbid = sbid,
|
materiel_code = partcode,
|
sourceid = erporderid,
|
m_po = erpordercode,
|
username = us.usercode,
|
CreateDate = DateTime.Now.ToString(),
|
saleOrderCode = saleordercode,
|
saleOrderDeliveryDate = Convert.ToDateTime(saleOrderDeliveryDate),
|
data_sources = "ERP",
|
isstep= "N"//是否关联工序
|
}
|
});
|
}
|
}
|
if (decimal.Parse(erpqty) == decimal.Parse(markqty) + decimal.Parse(relse_qty)) //如果ERP订单=下单数量+已下单数量,则更新ERP订单表状态为CREATED:已创建
|
{
|
sql = @"update TKimp_Ewo set status='CREATED',saleOrderDeliveryDate=@saleOrderDeliveryDate,relse_qty=relse_qty+@sumqty where wo=@wo and id=@erporderid";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo = erpordercode,
|
erporderid = erporderid,
|
sumqty = sumqty,
|
saleOrderDeliveryDate = Convert.ToDateTime(saleOrderDeliveryDate)
|
}
|
});
|
}
|
else //更新ERP订单表状态为CREATING:创建中
|
{
|
sql = @"update TKimp_Ewo set status='CREATING',saleOrderDeliveryDate=@saleOrderDeliveryDate,relse_qty=relse_qty+@sumqty where wo=@wo and id=@erporderid";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo = erpordercode,
|
erporderid = erporderid,
|
sumqty = sumqty,
|
saleOrderDeliveryDate = Convert.ToDateTime(saleOrderDeliveryDate)
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "下达", "下达了工单:" + wo, us.usertype);
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "下达MES工单成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "下达MES工单成功失败!";
|
mes.data = null;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[ERP订单关闭]
|
public static ToMessage ClosedErpOrder(string erporderid, string erpordercode, User us)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select * from TK_Wrk_Man where m_po=@erpordercode and status<> 'CLOSED'";
|
dynamicParams.Add("@erpordercode", erpordercode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "当前订单有下达未关闭的MES工单,订单不允许关闭,请先删除或关闭相关工单!";
|
mes.data = null;
|
}
|
else
|
{
|
//关闭订单
|
sql = @"update TKimp_Ewo set status='CLOSED' where wo=@wo and id=@erporderid";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo = erpordercode,
|
erporderid = erporderid
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "关闭", "关闭了订单:" + erpordercode, 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[ERP订单删除]
|
public static ToMessage DeleteErpOrder(string erporderid, string erpordercode, User us)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
sql = @"select * from TK_Wrk_Man where m_po=@erpordercode and sourceid=@erporderid and status<>'NEW'";
|
dynamicParams.Add("@erpordercode", erpordercode);
|
dynamicParams.Add("@erporderid", erporderid);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "当前订单下有工单已派发或已开工或已完工(关闭),不允许删除!";
|
mes.data = null;
|
return mes;
|
}
|
else
|
{
|
//删除工单
|
sql = @"delete TK_Wrk_Man where m_po=@wo and sourceid=@erporderid";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo = erpordercode,
|
erporderid = erporderid
|
}
|
});
|
//删除订单
|
sql = @"delete TKimp_Ewo where wo=@wo and id=@erporderid";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo = erpordercode,
|
erporderid = erporderid
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
LogHelper.WriteLogData(aa.ToString());
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除", "删除了订单:" + erpordercode, 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[MES工单查询]
|
public static ToMessage MesOrderSearch(string mesorderstus, string mesordercode, string sourceorder, string saleordercode, string ordertype, string partcode, string partname, string partspec, int startNum, string creatuser, string createdate, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (mesorderstus != "" && mesorderstus != null)
|
{
|
search += "and A.status=@mesorderstus ";
|
dynamicParams.Add("@mesorderstus", mesorderstus);
|
}
|
if (mesordercode != "" && mesordercode != null)
|
{
|
search += "and A.wo_code like '%'+@mesordercode+'%' ";
|
dynamicParams.Add("@mesordercode", mesordercode);
|
}
|
if (sourceorder != "" && sourceorder != null)
|
{
|
search += "and A.m_po like '%'+@sourceorder+'%' ";
|
dynamicParams.Add("@sourceorder", sourceorder);
|
}
|
if (saleordercode != "" && saleordercode != null)
|
{
|
search += "and W.saleOrderCode like '%'+@saleordercode+'%' ";
|
dynamicParams.Add("@saleordercode", saleordercode);
|
}
|
if (ordertype != "" && ordertype != null)
|
{
|
search += "and A.wotype like '%'+@ordertype+'%' ";
|
dynamicParams.Add("@ordertype", ordertype);
|
}
|
if (partcode != "" && partcode != null)
|
{
|
search += "and A.materiel_code like '%'+@partcode+'%' ";
|
dynamicParams.Add("@partcode", partcode);
|
}
|
if (partname != "" && partname != null)
|
{
|
search += "and B.partname like '%'+@partname+'%' ";
|
dynamicParams.Add("@partname", partname);
|
}
|
if (partspec != "" && partspec != null)
|
{
|
search += "and B.partspec like '%'+@partspec+'%' ";
|
dynamicParams.Add("@partspec", partspec);
|
}
|
if (createdate != "" && createdate != null)
|
{
|
search += "and CONVERT(varchar(100),A.lm_date,23)=@createdate ";
|
dynamicParams.Add("@createdate", createdate);
|
}
|
if (creatuser != "" && creatuser != null)
|
{
|
search += "and U.username like '%'+@creatuser+'%' ";
|
dynamicParams.Add("@creatuser", creatuser);
|
}
|
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.id, A.status,A.wotype,A.wo_code,A.materiel_code as partcode,B.partname,B.partspec,A.plan_qty,A.wkshp_code,C.torg_name as wkshp_name,
|
A.stck_code,D.name as stck_name,A.plan_startdate,A.plan_enddate,A.piroque,A.sourceid,A.m_po,A.saleOrderDeliveryDate,W.saleOrderCode,U.username as lm_user,A.lm_date
|
from TK_Wrk_Man A
|
left join TKimp_Ewo W on A.m_po=W.wo and A.materiel_code=W.materiel_code
|
left join TMateriel_Info B on A.materiel_code=B.partcode
|
left join TOrganization C on A.wkshp_code=C.torg_code
|
left join TSecStck D on A.stck_code=D.code
|
left join TUser U on A.lm_user=U.usercode
|
left join TOrganization L on C.parent_id=L.id
|
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[MES报废补单工单查询]
|
public static ToMessage MesBadOrderSearch(string mesordercode, string sourceorder, string saleordercode, string partcode, string partname, string partspec, int startNum, string creatuser, string createdate, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (mesordercode != "" && mesordercode != null)
|
{
|
search += "and A.wo_code like '%'+@mesordercode+'%' ";
|
dynamicParams.Add("@mesordercode", mesordercode);
|
}
|
if (sourceorder != "" && sourceorder != null)
|
{
|
search += "and A.m_po like '%'+@sourceorder+'%' ";
|
dynamicParams.Add("@sourceorder", sourceorder);
|
}
|
if (saleordercode != "" && saleordercode != null)
|
{
|
search += "and W.saleOrderCode like '%'+@saleordercode+'%' ";
|
dynamicParams.Add("@saleordercode", saleordercode);
|
}
|
if (partcode != "" && partcode != null)
|
{
|
search += "and A.materiel_code like '%'+@partcode+'%' ";
|
dynamicParams.Add("@partcode", partcode);
|
}
|
if (partname != "" && partname != null)
|
{
|
search += "and B.partname like '%'+@partname+'%' ";
|
dynamicParams.Add("@partname", partname);
|
}
|
if (partspec != "" && partspec != null)
|
{
|
search += "and B.partspec like '%'+@partspec+'%' ";
|
dynamicParams.Add("@partspec", partspec);
|
}
|
if (createdate != "" && createdate != null)
|
{
|
search += "and CONVERT(varchar(100),A.lm_date,23)=@createdate ";
|
dynamicParams.Add("@createdate", createdate);
|
}
|
if (creatuser != "" && creatuser != null)
|
{
|
search += "and U.username like '%'+@creatuser+'%' ";
|
dynamicParams.Add("@creatuser", creatuser);
|
}
|
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.id, A.status,A.wotype,A.wo_code,A.materiel_code as partcode,B.partname,B.partspec,A.plan_qty,A.wkshp_code,C.torg_name as wkshp_name,
|
A.stck_code,D.name as stck_name,A.plan_startdate,A.plan_enddate,A.piroque,A.sourceid,A.m_po,A.saleOrderDeliveryDate,W.saleOrderCode,U.username as lm_user,A.lm_date,S.laborbad_qty,S.materielbad_qty
|
from TK_Wrk_Man A
|
left join TKimp_Ewo W on A.m_po=W.wo and A.materiel_code=W.materiel_code
|
left join (select wo_code,isnull(sum(laborbad_qty),0) as laborbad_qty,isnull(sum(materielbad_qty),0) as materielbad_qty from TK_Wrk_Step where (laborbad_qty+materielbad_qty)>0 group by wo_code) S on A.wo_code=S.wo_code
|
left join TMateriel_Info B on A.materiel_code=B.partcode
|
left join TOrganization C on A.wkshp_code=C.torg_code
|
left join TSecStck D on A.stck_code=D.code
|
left join TUser U on A.lm_user=U.usercode
|
where A.is_delete<>'1' and (S.laborbad_qty+S.materielbad_qty)>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[MES工单新增、获取工单号]
|
public static ToMessage AddMesOrderCodeSearch()
|
{
|
string sql = "";
|
string wo_code = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//获取单据号
|
sql = @"SELECT 'SGPO'+CONVERT(varchar(12) , getdate(), 112 )+'_'+cast(isnull(max(cast(substring(wo_code,charindex('_',wo_code)+1,len(wo_code)-charindex('_',wo_code)) as numeric)),0)+1 as varchar) as numct
|
FROM TK_Wrk_Man where wo_code like '%SGPO%'";
|
var data = DapperHelper.selecttable(sql);
|
mes.code = "200";
|
mes.Message = "查询成功!";
|
mes.data = data.Rows[0]["numct"].ToString();
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[MES工单编辑获取工序数据]
|
public static ToMessage UpdateMesOrderStepSearch(string sourceid, string sourcewo, string wocode, string data_sources)
|
{
|
string sql = "";decimal canupdate_qty = 0;
|
var dynamicParams = new DynamicParameters();
|
Dictionary<object, object> dir = new Dictionary<object, object>();
|
try
|
{
|
if (data_sources == "ERP") //数据来源ERP
|
{
|
//查询当前工单可修改数量=订单总数-已下达工单总数
|
sql = @"select max(qty) as qty,isnull(sum(M.plan_qty),0) as plan_qty from TKimp_Ewo E
|
inner join TK_Wrk_Man M on E.wo=M.m_po and E.id=M.sourceid
|
where E.wo=@sourcewo and E.id=@sourceid and M.wo_code<>@wocode";
|
dynamicParams.Add("@sourceid", sourceid);
|
dynamicParams.Add("@sourcewo", sourcewo);
|
dynamicParams.Add("@wocode", wocode);
|
var data = DapperHelper.selectdata(sql,dynamicParams);
|
//当前工单可修改数量=订单数量-非当前工单总下达工单数量
|
canupdate_qty = decimal.Parse(data.Rows[0]["qty"].ToString()) - decimal.Parse(data.Rows[0]["plan_qty"].ToString());
|
}
|
if (data_sources == "MES") //数据来源MES
|
{
|
if (sourceid == "" || sourceid == null) //无源单
|
{
|
//查询当前工单可修改数量=工单总数
|
sql = @"select plan_qty from TK_Wrk_Man where wo_code=@wo_code";
|
dynamicParams.Add("@wo_code", wocode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
//当前工单可修改数量=工单数量
|
canupdate_qty = decimal.Parse(data.Rows[0]["plan_qty"].ToString());
|
}
|
else //有源单(报废补单)
|
{
|
//不控制 标识为-1
|
canupdate_qty = -1;
|
}
|
}
|
//获取工序信息
|
sql = @"select S.wo_code,S.seq,S.step_code,T.stepname,S.stepprice,(isnull(S.good_qty,0)+isnull(S.ng_qty,0)+isnull(S.laborbad_qty,0)+isnull(S.materielbad_qty,0)) as produceq_qty,
|
S.good_qty,S.ng_qty,S.laborbad_qty,S.materielbad_qty,(isnull(S.plan_qty,0)-(isnull(S.good_qty,0)+isnull(S.ng_qty,0)+isnull(S.laborbad_qty,0)+isnull(S.materielbad_qty,0))) as delive_qty,S.isbott,S.isend
|
from TK_Wrk_Step S
|
left join TStep T on S.step_code=T.stepcode
|
where S.wo_code=@wocode order by S.seq ";
|
dynamicParams.Add("@wocode", wocode);
|
var data1 = DapperHelper.selectdata(sql, dynamicParams);
|
dir.Add("canupdate_qty", canupdate_qty);
|
dir.Add("stepdata", data1);
|
mes.code = "200";
|
mes.count = data1.Rows.Count;
|
mes.Message = "查询成功";
|
mes.data = dir;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[MES工单新增、编辑提交]
|
public static ToMessage AddUpdateMesOrder(string operType, WorkList json, User us)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
try
|
{
|
if (operType == "Add")
|
{
|
//写入工单表
|
sql = @"insert into TK_Wrk_Man(wo_code,wotype,status,wkshp_code,plan_qty,lm_user,lm_date,materiel_code,sourceid,m_po,saleOrderDeliveryDate,piroque,isaps,data_sources,isstep)
|
values(@wo_code,@wotype,@status,@wkshp_code,@plan_qty,@lm_user,@lm_date,@materiel_code,@m_po,@saleOrderDeliveryDate,@orderlev,@isaps,@data_sources,@isstep)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = json.wocode,
|
wotype = json.wotype,
|
status = json.wostatus,
|
wkshp_code = json.wkshopcode,
|
plan_qty = json.woqty,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString(),
|
materiel_code = json.partcode,
|
sourceid = json.sourceid==""?"NULL": json.sourceid, //无源单时赋值NULL
|
m_po = json.sourcewo,
|
saleOrderDeliveryDate = json.deliverydate,
|
orderlev = "3",//优先级：特级（1） 紧急（2） 正常（3）
|
isaps = "N", //是否排产,默认N Y=是 N=否
|
data_sources = json.data_sources,
|
isstep= json.isstep //是否关联工序
|
}
|
});
|
//写入工序任务表
|
for (int i = 0; i < json.WorkListSub.Count; i++)
|
{
|
sql = @"insert into TK_Wrk_Step(wo_code,seq,step_code,stepprice,plan_qty,status,isbott,isend,lm_user,lm_date)
|
values(@wo_code,@seq,@step_code,@stepprice,@plan_qty,@status,@isbott,@isend,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = json.wocode,
|
seq = json.WorkListSub[i].stepseq,
|
step_code = json.WorkListSub[i].stepcode,
|
stepprice = json.WorkListSub[i].stepprice,
|
plan_qty = json.woqty,
|
status = json.wostatus,
|
isbott = json.WorkListSub[i].isbott,
|
isend = json.WorkListSub[i].isend,
|
username = us.usercode,
|
CreateDate = DateTime.Now.ToString()
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "新增", "新增了工单:" + json.wocode, us.usertype);
|
mes.code = "200";
|
mes.count = 0;
|
mes.Message = "MES工单新建成功!";
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "MES工单新建失败!";
|
mes.data = null;
|
}
|
}
|
if (operType == "Update")
|
{
|
//修改工单表
|
sql = @"update TK_Wrk_Man set wotype=@wotype,wkshp_code=@wkshp_code,plan_qty=@plan_qty,lm_user=@lm_user,lm_date=@lm_date,
|
materiel_code=@materiel_code,sourceid=@sourceid,m_po=@m_po,saleOrderDeliveryDate=@saleOrderDeliveryDate,isstep=@isstep
|
where wo_code=@wo_code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = json.wocode,
|
wotype = json.wotype,
|
plan_qty = json.woqty,
|
materiel_code = json.partcode,
|
sourceid = json.sourceid == "" ? "NULL" : json.sourceid, //无源单时赋值NULL
|
m_po = json.sourcewo,
|
saleOrderDeliveryDate = json.deliverydate,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString(),
|
isstep=json.isstep //是否关联工序
|
}
|
});
|
//删除工单工序表
|
sql = @"delete TK_Wrk_Step where wo_code=@wo_code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = json.wocode
|
}
|
});
|
//写入工单工序表
|
for (int i = 0; i < json.WorkListSub.Count; i++)
|
{
|
sql = @"insert into TK_Wrk_Step(wo_code,seq,step_code,stepprice,plan_qty,status,isbott,isend,lm_user,lm_date)
|
values(@wo_code,@seq,@step_code,@stepprice,@plan_qty,@status,@isbott,@isend,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = json.wocode,
|
seq = json.WorkListSub[i].stepseq,
|
step_code = json.WorkListSub[i].stepcode,
|
stepprice = json.WorkListSub[i].stepprice,
|
plan_qty = json.woqty,
|
status = json.wostatus,
|
isbott = json.WorkListSub[i].isbott,
|
isend = json.WorkListSub[i].isend,
|
username = us.usercode,
|
CreateDate = DateTime.Now.ToString()
|
}
|
});
|
}
|
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "修改", "修改了工单:" + json.wocode, 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[MES工单删除]
|
public static ToMessage DeleteMesOrder(string souceid, string wocode, string m_po, string orderqty, User us)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//判断工单是否为未开始状态或者已派发或待排程状态(满足其中一种都可删除，否则不允许删除)
|
sql = @"select * from TK_Wrk_Man where wo_code=@wocode and status in('NEW','ALLO','NOSCHED')";
|
dynamicParams.Add("@wocode", wocode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
if (m_po != "" && m_po != null)
|
{
|
//查询生产订单表数据
|
sql = @"select * from TKimp_Ewo where wo=@m_po and id=@souceid";
|
dynamicParams.Add("@m_po", m_po);
|
dynamicParams.Add("@souceid", souceid);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
decimal relse_qty = decimal.Parse(data0.Rows[0]["RELSE_QTY"].ToString());//以下单数量
|
if ((relse_qty - decimal.Parse(orderqty)) == 0) //全部撤销 订单状态回写未开始,已下单数量为0
|
{
|
//回写订单表状态及已下单数量
|
sql = @"update TKimp_Ewo set status='NEW',relse_qty=0 where wo=@m_po and id=@souceid";
|
list.Add(new { str = sql, parm = new { m_po = m_po, souceid = souceid } });
|
}
|
else
|
{
|
//回写订单表状态及已下单数量
|
sql = @"update TKimp_Ewo set status='CREATING',relse_qty=relse_qty-@orderqty where wo=@m_po and id=@souceid";
|
list.Add(new { str = sql, parm = new { m_po = m_po, souceid = souceid, orderqty = decimal.Parse(orderqty) } });
|
}
|
}
|
//删除工单工序表
|
sql = @"delete TK_Wrk_Step where wo_code=@wocode";
|
list.Add(new { str = sql, parm = new { wocode = wocode } });
|
|
//删除加工单用料表(子件)
|
//sql = @"delete TK_Wrk_Allo where wo_code=@wocode";
|
//list.Add(new { str = sql, parm = new { wocode = wocode } });
|
|
//删除工单表
|
sql = @"update TK_Wrk_Man set is_delete='1' where wo_code=@wocode";
|
list.Add(new { str = sql, parm = new { wocode = wocode } });
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.Message = "工单执行中或已关闭，不允许删除!";
|
mes.data = null;
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "删除", "删除了工单:" +wocode, 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[工单关闭列表查询]
|
public static ToMessage MesOrderClosedSearch(string mesorderstus, string mesordercode, string sourceorder, string saleordercode, string ordertype, string partcode, string partname, string partspec, int startNum, string creatuser, string createdate, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (mesorderstus == "已关闭")
|
{
|
search += "and A.status=@mesorderstus ";
|
dynamicParams.Add("@mesorderstus","CLOSED");
|
}
|
if (mesorderstus == "未关闭")
|
{
|
search += "and A.status<>@mesorderstus ";
|
dynamicParams.Add("@mesorderstus", "CLOSED");
|
}
|
if (mesordercode != "" && mesordercode != null)
|
{
|
search += "and A.wo_code like '%'+@mesordercode+'%' ";
|
dynamicParams.Add("@mesordercode", mesordercode);
|
}
|
if (sourceorder != "" && sourceorder != null)
|
{
|
search += "and A.m_po like '%'+@sourceorder+'%' ";
|
dynamicParams.Add("@sourceorder", sourceorder);
|
}
|
if (saleordercode != "" && saleordercode != null)
|
{
|
search += "and W.saleOrderCode like '%'+@saleordercode+'%' ";
|
dynamicParams.Add("@saleordercode", saleordercode);
|
}
|
if (ordertype != "" && ordertype != null)
|
{
|
search += "and A.wotype like '%'+@ordertype+'%' ";
|
dynamicParams.Add("@ordertype", ordertype);
|
}
|
if (partcode != "" && partcode != null)
|
{
|
search += "and A.materiel_code like '%'+@partcode+'%' ";
|
dynamicParams.Add("@partcode", partcode);
|
}
|
if (partname != "" && partname != null)
|
{
|
search += "and B.partname like '%'+@partname+'%' ";
|
dynamicParams.Add("@partname", partname);
|
}
|
if (partspec != "" && partspec != null)
|
{
|
search += "and B.partspec like '%'+@partspec+'%' ";
|
dynamicParams.Add("@partspec", partspec);
|
}
|
if (createdate != "" && createdate != null)
|
{
|
search += "and CONVERT(varchar(100),A.lm_date,23)=@createdate ";
|
dynamicParams.Add("@createdate", createdate);
|
}
|
if (creatuser != "" && creatuser != null)
|
{
|
search += "and U.username like '%'+@creatuser+'%' ";
|
dynamicParams.Add("@creatuser", creatuser);
|
}
|
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select A.id, A.status,A.wotype,A.wo_code,A.materiel_code as partcode,B.partname,B.partspec,A.plan_qty,A.wkshp_code,C.torg_name as wkshp_name,
|
A.stck_code,D.name as stck_name,A.plan_startdate,A.plan_enddate,A.piroque,A.sourceid,A.m_po,A.saleOrderDeliveryDate,W.saleOrderCode,U.username as lm_user,A.lm_date
|
from TK_Wrk_Man A
|
left join TKimp_Ewo W on A.m_po=W.wo and A.materiel_code=W.materiel_code
|
left join TMateriel_Info B on A.materiel_code=B.partcode
|
left join TOrganization C on A.wkshp_code=C.torg_code
|
left join TSecStck D on A.stck_code=D.code
|
left join TUser U on A.lm_user=U.usercode
|
left join TOrganization L on C.parent_id=L.id
|
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[MES工单批量关闭提交]
|
public static ToMessage MesOrderBitchClosedSeave(User us, DataTable dt)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
//关闭工单对应工序任务
|
sql = @"update TK_Wrk_Step set status='CLOSED' where wo_code=@wocode";
|
list.Add(new { str = sql, parm = new { wocode = dt.Rows[i]["WO_CODE"].ToString() } });
|
//回写工单表状态为(关闭)
|
sql = @"update TK_Wrk_Man set status='CLOSED',closeuser=@username,closedate=@closedate where wo_code=@wocode";
|
list.Add(new { str = sql, parm = new { wocode = dt.Rows[i]["WO_CODE"].ToString(), username = us.usercode, closedate = 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
|
}
|
}
|
