using Dapper;
|
using Newtonsoft.Json.Linq;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Text.Json;
|
using System.Threading.Tasks;
|
using VueWebCoreApi.Models;
|
using VueWebCoreApi.Models.ErpOrder;
|
using VueWebCoreApi.Models.InventoryModel;
|
using VueWebCoreApi.Models.ReportVerify;
|
using VueWebCoreApi.Models.UpdateReport;
|
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 wkshopcode, string erpordercode, string saleordercode, string partcode, string partname, string partspec, int startNum, string datatype, string paydatestartdate, string paydateenddate, 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 (wkshopcode != "" && wkshopcode != null)
|
{
|
string[] wkshoplist = Array.ConvertAll<string, string>(wkshopcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
search += "and A.wkshp_code in @wkshoplist ";
|
dynamicParams.Add("@wkshoplist", wkshoplist);
|
}
|
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)
|
{
|
switch (datatype)
|
{
|
case "PS":
|
search += "and A.planstartdate between @paydatestartdate and @paydateenddate ";
|
dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00");
|
dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59");
|
break;
|
case "PE":
|
search += "and A.planenddate between @paydatestartdate and @paydateenddate ";
|
dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00");
|
dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59");
|
break;
|
case "ED":
|
search += "and A.saleOrderDeliveryDate between @paydatestartdate and @paydateenddate ";
|
dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00");
|
dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59");
|
break;
|
default:
|
break;
|
}
|
}
|
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.idTopInventory,A.TopInventoryCode,A.TopInventoryName,A.qty,A.relse_qty,A.dept_id,A.dept_code,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,A.clerkuser,
|
B.priuserdefnvc1,B.priuserdefnvc2,B.priuserdefnvc3,B.priuserdefnvc4,B.priuserdefnvc5,B.priuserdefnvc6
|
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 deptcode, string wkshopcode, string warehousecode, string erpqty, string markqty, string ordernum, string relse_qty, string idTopInventory, string TopInventoryCode, string TopInventoryName, string saleOrderDeliveryDate, string paystartdate, string payenddate, string clerkuser, User us)
|
{
|
var sql = "";
|
string orderstatus = "", isstep = ""; //工单状态、是否绑定工艺
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
DataTable data0 = new DataTable();
|
try
|
{
|
list.Clear();
|
//获取token中流程配置信息
|
JObject jsonObj = JObject.Parse(us.mesSetting);
|
var isroute = (string)jsonObj["route"].ToString().ToLower();// 获取route键的值
|
//判断当前工艺流程
|
if (isroute == "true")
|
{
|
//获取当前物料的工艺路线工艺信息
|
sql = @"select M.default_route,T.seq,T.step_code,T.first_choke,T.last_choke,S.unprice
|
from TMateriel_Info M
|
inner join TFlw_Rtdt T on M.default_route=T.rout_code
|
left join TPrteEqp_Stad S on M.partcode=S.materiel_code and M.default_route=S.route_code and T.step_code=S.step_code
|
where partcode=@partcode";
|
dynamicParams.Add("@partcode", partcode);
|
data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
orderstatus = "ALLO";
|
isstep = "Y";
|
}
|
else
|
{
|
orderstatus = "NEW";
|
isstep = "N";
|
}
|
}
|
else
|
{
|
//获取当前物料的工序工艺信息
|
sql = @"select M.default_route,T.step_seq as seq,T.step_code,T.isbott as first_choke,T.isend as last_choke,S.unprice
|
from TMateriel_Info M
|
inner join TMateriel_Step T on M.partcode=T.materiel_code
|
left join TPrteEqp_Stad S on M.partcode=S.materiel_code and T.step_code=S.step_code
|
where partcode=@partcode";
|
dynamicParams.Add("@partcode", partcode);
|
data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
orderstatus = "ALLO";
|
isstep = "Y";
|
}
|
else
|
{
|
orderstatus = "NEW";
|
isstep = "N";
|
}
|
}
|
|
//获取拆单数量:向下取整
|
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,dept_code,wkshp_code,plan_qty,stck_code,sbid,materiel_code,route_code,sourceid,m_po,lm_user,lm_date,saleOrderCode,saleOrderDeliveryDate,plan_startdate,plan_enddate,data_sources,isstep,clerkuser,idTopInventory,TopInventoryCode,TopInventoryName)
|
values(@wo_code,@wotype,@status,@dept_code,@wkshp_code,@plan_qty,@stck_code,@sbid,@materiel_code,@route_code,@sourceid,@m_po,@username,@CreateDate,@saleOrderCode,@saleOrderDeliveryDate,@plan_startdate,@plan_enddate,@data_sources,@isstep,@clerkuser,@idTopInventory,@TopInventoryCode,@TopInventoryName)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = wo,
|
wotype = "PO",
|
status = orderstatus, //"NEW"
|
dept_code= deptcode,
|
wkshp_code = wkshopcode,
|
plan_qty = cdqty + (decimal.Parse(markqty) - sumqty), //末单下单数量=切分数量+(下单数量-累计切分下单数量)
|
stck_code = warehousecode,
|
sbid = sbid,
|
materiel_code = partcode,
|
route_code = data0.Rows.Count <= 0 ? "" : data0.Rows[0]["default_route"].ToString(),
|
sourceid = erporderid,
|
m_po = erpordercode,
|
username = us.usercode,
|
CreateDate = DateTime.Now.ToString(),
|
saleOrderCode = saleordercode,
|
saleOrderDeliveryDate = Convert.ToDateTime(saleOrderDeliveryDate),
|
plan_startdate = Convert.ToDateTime(paystartdate),
|
plan_enddate = Convert.ToDateTime(payenddate),
|
data_sources = "ERP",
|
isstep = isstep, //是否关联工序 "N"
|
clerkuser = clerkuser, //销售订单业务员
|
idTopInventory = idTopInventory,
|
TopInventoryCode = TopInventoryCode,
|
TopInventoryName = TopInventoryName
|
}
|
});
|
//判断是否写入工单工序表(当前工单物料是否指定了工艺)
|
if (orderstatus == "ALLO" && isstep == "Y")
|
{
|
//写入工单工序表
|
for (int j = 0; j < data0.Rows.Count; j++)
|
{
|
sql = @"insert into TK_Wrk_Step(wo_code,seq,step_code,route_code,stepprice,plan_quantity,plan_qty,ratio,status,isbott,isend,lm_user,lm_date)
|
values(@wo_code,@seq,@step_code,@route_code,@stepprice,@plan_quantity,@plan_qty,@ratio,@status,@isbott,@isend,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = wo,
|
seq = data0.Rows[j]["seq"].ToString(),
|
step_code = data0.Rows[j]["step_code"].ToString(),
|
route_code = data0.Rows[j]["default_route"].ToString(),
|
stepprice = decimal.Parse(data0.Rows[j]["unprice"].ToString() == "" || data0.Rows[j]["unprice"].ToString() == null ? "0" : data0.Rows[j]["unprice"].ToString()),
|
plan_quantity = cdqty + (decimal.Parse(markqty) - sumqty), //末单下单数量=切分数量+(下单数量-累计切分下单数量),
|
plan_qty = cdqty + (decimal.Parse(markqty) - sumqty), //末单下单数量=切分数量+(下单数量-累计切分下单数量),
|
ratio = 0,
|
status = orderstatus,
|
isbott = data0.Rows[j]["first_choke"].ToString(),
|
isend = data0.Rows[j]["last_choke"].ToString(),
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
}
|
sumqty = sumqty + (decimal.Parse(markqty) - sumqty);
|
}
|
else
|
{
|
|
sql = @"insert into TK_Wrk_Man(wo_code,wotype,status,dept_code,wkshp_code,plan_qty,stck_code,sbid,materiel_code,route_code,sourceid,m_po,lm_user,lm_date,saleOrderCode,saleOrderDeliveryDate,plan_startdate,plan_enddate,data_sources,isstep,clerkuser,idTopInventory,TopInventoryCode,TopInventoryName)
|
values(@wo_code,@wotype,@status,@dept_code,@wkshp_code,@plan_qty,@stck_code,@sbid,@materiel_code,@route_code,@sourceid,@m_po,@username,@CreateDate,@saleOrderCode,@saleOrderDeliveryDate,@plan_startdate,@plan_enddate,@data_sources,@isstep,@clerkuser,@idTopInventory,@TopInventoryCode,@TopInventoryName)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = wo,
|
wotype = "PO",
|
status = orderstatus, //"NEW"
|
dept_code=deptcode,
|
wkshp_code = wkshopcode,
|
plan_qty = cdqty,
|
stck_code = warehousecode,
|
sbid = sbid,
|
materiel_code = partcode,
|
route_code = data0.Rows.Count <= 0 ? "" : data0.Rows[0]["default_route"].ToString(),
|
sourceid = erporderid,
|
m_po = erpordercode,
|
username = us.usercode,
|
CreateDate = DateTime.Now.ToString(),
|
saleOrderCode = saleordercode,
|
saleOrderDeliveryDate = Convert.ToDateTime(saleOrderDeliveryDate),
|
plan_startdate = Convert.ToDateTime(paystartdate),
|
plan_enddate = Convert.ToDateTime(payenddate),
|
data_sources = "ERP",
|
isstep = isstep,//是否关联工序 "N"
|
clerkuser = clerkuser,
|
idTopInventory = idTopInventory,
|
TopInventoryCode = TopInventoryCode,
|
TopInventoryName = TopInventoryName
|
}
|
});
|
//判断是否写入工单工序表(当前工单物料是否指定了工艺)
|
if (orderstatus == "ALLO" && isstep == "Y")
|
{
|
//写入工单工序表
|
for (int j = 0; j < data0.Rows.Count; j++)
|
{
|
sql = @"insert into TK_Wrk_Step(wo_code,seq,step_code,route_code,stepprice,plan_quantity,plan_qty,ratio,status,isbott,isend,lm_user,lm_date)
|
values(@wo_code,@seq,@step_code,@route_code,@stepprice,@plan_quantity,@plan_qty,@ratio,@status,@isbott,@isend,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = wo,
|
seq = data0.Rows[j]["seq"].ToString(),
|
step_code = data0.Rows[j]["step_code"].ToString(),
|
route_code = data0.Rows[j]["default_route"].ToString(),
|
stepprice = decimal.Parse(data0.Rows[j]["unprice"].ToString() == "" || data0.Rows[j]["unprice"].ToString() == null ? "0" : data0.Rows[j]["unprice"].ToString()),
|
plan_quantity = cdqty + (decimal.Parse(markqty) - sumqty), //末单下单数量=切分数量+(下单数量-累计切分下单数量),
|
plan_qty = cdqty + (decimal.Parse(markqty) - sumqty), //末单下单数量=切分数量+(下单数量-累计切分下单数量),
|
ratio = 0,
|
status = orderstatus,
|
isbott = data0.Rows[j]["first_choke"].ToString(),
|
isend = data0.Rows[j]["last_choke"].ToString(),
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
}
|
}
|
}
|
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[订单批量下达]
|
public static ToMessage MarkBatchSaveErpOrder(List<ErpOrderBatch> obj, User us)
|
{
|
var sql = "";
|
string orderstatus = "", isstep = ""; //工单状态、是否绑定工艺
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
DataTable data0 = new DataTable();
|
try
|
{
|
list.Clear();
|
string result = "";
|
//var groupedModels = obj.GroupBy(m => m.erpordercode);
|
var groupedModels = obj.GroupBy(s => s.erpordercode).Select(g => new { erpordercode = g.Key }).ToList();
|
|
//获取token中流程配置信息
|
JObject jsonObj = JObject.Parse(us.mesSetting);
|
var isroute = (string)jsonObj["route"].ToString().ToLower();// 获取route键的值
|
|
foreach (var group in groupedModels)
|
{
|
string erpordercode = group.erpordercode;
|
int count = 1;
|
foreach (var model in obj.Where(s => s.erpordercode == erpordercode).ToList())
|
{
|
//判断当前工艺流程
|
if (isroute == "true")
|
{
|
//获取当前物料的工艺路线工艺信息
|
sql = @"select M.default_route,T.seq,T.step_code,T.first_choke,T.last_choke,S.unprice
|
from TMateriel_Info M
|
inner join TFlw_Rtdt T on M.default_route=T.rout_code
|
left join TPrteEqp_Stad S on M.partcode=S.materiel_code and M.default_route=S.route_code and T.step_code=S.step_code
|
where partcode=@partcode";
|
dynamicParams.Add("@partcode", model.partcode);
|
data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
orderstatus = "ALLO";
|
isstep = "Y";
|
}
|
else
|
{
|
orderstatus = "NEW";
|
isstep = "N";
|
}
|
}
|
else
|
{
|
//获取当前物料的工序工艺信息
|
sql = @"select M.default_route,T.step_seq as seq,T.step_code,T.isbott as first_choke,T.isend as last_choke,S.unprice
|
from TMateriel_Info M
|
inner join TMateriel_Step T on M.partcode=T.materiel_code
|
left join TPrteEqp_Stad S on M.partcode=S.materiel_code and T.step_code=S.step_code
|
where partcode=@partcode";
|
dynamicParams.Add("@partcode", model.partcode);
|
data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
orderstatus = "ALLO";
|
isstep = "Y";
|
}
|
else
|
{
|
orderstatus = "NEW";
|
isstep = "N";
|
}
|
}
|
|
//获取当前最大工单号
|
sql = @"select isnull(max(cast(substring(wo_code,charindex('_',wo_code)+1,len(wo_code)-charindex('_',wo_code)) as numeric)),0) as worknumb
|
from TK_Wrk_Man where m_po=@erpordercode";
|
dynamicParams.Add("@erpordercode", model.erpordercode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
int num = Convert.ToInt32(data.Rows[0]["WORKNUMB"].ToString());
|
string wo = model.erpordercode + "_" + (num + count);
|
result += wo.ToString() + ",";
|
//写入工单表
|
sql = @"insert into TK_Wrk_Man(wo_code,wotype,status,dept_code,wkshp_code,plan_qty,stck_code,sbid,materiel_code,route_code,sourceid,m_po,lm_user,lm_date,saleOrderCode,saleOrderDeliveryDate,plan_startdate,plan_enddate,data_sources,isstep,clerkuser,idTopInventory,TopInventoryCode,TopInventoryName)
|
values(@wo_code,@wotype,@status,@dept_code,@wkshp_code,@plan_qty,@stck_code,@sbid,@materiel_code,@route_code,@sourceid,@m_po,@username,@CreateDate,@saleOrderCode,@saleOrderDeliveryDate,@plan_startdate,@plan_enddate,@data_sources,@isstep,@clerkuser,@idTopInventory,@TopInventoryCode,@TopInventoryName)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = wo,
|
wotype = "PO",
|
status = orderstatus, //"NEW"
|
dept_code=model.deptcode,
|
wkshp_code = model.wkshopcode,
|
plan_qty = decimal.Parse(model.erpqty), //订单数量
|
stck_code = model.warehousecode,
|
sbid = model.sbid,
|
materiel_code = model.partcode,
|
route_code=data0.Rows.Count<=0?"": data0.Rows[0]["default_route"].ToString(),
|
sourceid = model.erporderid,
|
m_po = model.erpordercode,
|
username = us.usercode,
|
CreateDate = DateTime.Now.ToString(),
|
saleOrderCode = model.saleordercode,
|
saleOrderDeliveryDate = Convert.ToDateTime(model.saleOrderDeliveryDate),
|
plan_startdate = Convert.ToDateTime(model.paystartdate),
|
plan_enddate = Convert.ToDateTime(model.payenddate),
|
data_sources = "ERP",
|
isstep = isstep, //是否关联工序 "N"
|
clerkuser = model.clerkuser, //销售订单业务员
|
idTopInventory =model.idTopInventory,
|
TopInventoryCode = model.TopInventoryCode,
|
TopInventoryName = model.TopInventoryName
|
}
|
});
|
//判断是否写入工单工序表(当前工单物料是否指定了工艺)
|
if (orderstatus == "ALLO" && isstep == "Y")
|
{
|
//写入工单工序表
|
for (int i = 0; i < data0.Rows.Count; i++)
|
{
|
sql = @"insert into TK_Wrk_Step(wo_code,seq,step_code,route_code,stepprice,plan_quantity,plan_qty,ratio,status,isbott,isend,lm_user,lm_date)
|
values(@wo_code,@seq,@step_code,@route_code,@stepprice,@plan_quantity,@plan_qty,@ratio,@status,@isbott,@isend,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = wo,
|
seq = data0.Rows[i]["seq"].ToString(),
|
step_code = data0.Rows[i]["step_code"].ToString(),
|
route_code = data0.Rows[i]["default_route"].ToString(),
|
stepprice = decimal.Parse(data0.Rows[i]["unprice"].ToString()==""|| data0.Rows[i]["unprice"].ToString() ==null? "0":data0.Rows[i]["unprice"].ToString()),
|
plan_quantity = decimal.Parse(model.erpqty), //订单数量
|
plan_qty = decimal.Parse(model.erpqty), //订单数量
|
ratio = 0,
|
status = orderstatus,
|
isbott = data0.Rows[i]["first_choke"].ToString(),
|
isend = data0.Rows[i]["last_choke"].ToString(),
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
}
|
//更新订单状态
|
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 = model.erpordercode,
|
erporderid = model.erporderid,
|
sumqty = decimal.Parse(model.markqty),
|
saleOrderDeliveryDate = Convert.ToDateTime(model.saleOrderDeliveryDate)
|
}
|
});
|
count++;
|
}
|
}
|
|
|
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "批量下达", "批量下达了工单:" + result.TrimEnd(','), 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 sourceid=@sourceid";
|
dynamicParams.Add("@erpordercode", erpordercode);
|
dynamicParams.Add("@sourceid", erporderid);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
// 使用LINQ和lambda表达式来转换wo_code字段的值为逗号隔开的字符串数组
|
string[] result = data.AsEnumerable().Select(row => "" + row.Field<string>("wo_code") + "").ToArray();
|
//关闭工序任务
|
sql = @"update TK_Wrk_Step set status='CLOSED',closebeforestatus=status where wo_code in @wocode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wocode = result
|
}
|
});
|
//关闭工单
|
sql = @"update TK_Wrk_Man set status='CLOSED',closebeforestatus=status where m_po=@erpordercode and sourceid=@sourceid";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
erpordercode = erpordercode,
|
sourceid = erporderid
|
}
|
});
|
}
|
//关闭订单
|
sql = @"update TKimp_Ewo set status='CLOSED',closebeforestatus=status 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 ReverseClosedErpOrder(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=@sourceid";
|
dynamicParams.Add("@erpordercode", erpordercode);
|
dynamicParams.Add("@sourceid", erporderid);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
// 使用LINQ和lambda表达式来转换wo_code字段的值为逗号隔开的字符串数组
|
string[] result = data.AsEnumerable().Select(row => "" + row.Field<string>("wo_code") + "").ToArray();
|
//关闭工序任务
|
sql = @"update TK_Wrk_Step set status=closebeforestatus where wo_code in @ordercode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
ordercode = result
|
}
|
});
|
//关闭工单
|
sql = @"update TK_Wrk_Man set status=closebeforestatus where m_po=@erpordercode and sourceid=@sourceid";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
erpordercode = erpordercode,
|
sourceid = erporderid
|
}
|
});
|
}
|
//反关闭订单
|
sql = @"update TKimp_Ewo set status=closebeforestatus 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 wkshopcode, string mesordercode, string sourceorder, string saleordercode, string ordertype, string partcode, string partname, string partspec, int startNum, string creatuser, string datatype, string paydatestartdate, string paydateenddate, 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 (wkshopcode != "" && wkshopcode != null)
|
{
|
string[] wkshoplist = Array.ConvertAll<string, string>(wkshopcode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string分割转string[]
|
search += "and A.wkshp_code in @wkshoplist ";
|
dynamicParams.Add("@wkshoplist", wkshoplist);
|
}
|
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 (creatuser != "" && creatuser != null)
|
{
|
search += "and U.username like '%'+@creatuser+'%' ";
|
dynamicParams.Add("@creatuser", creatuser);
|
}
|
if (paydatestartdate != "" && paydatestartdate != null)
|
{
|
switch (datatype)
|
{
|
case "PS":
|
search += "and A.plan_startdate between @paydatestartdate and @paydateenddate ";
|
dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00");
|
dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59");
|
break;
|
case "PE":
|
search += "and A.plan_enddate between @paydatestartdate and @paydateenddate ";
|
dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00");
|
dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59");
|
break;
|
case "ED":
|
search += "and A.saleOrderDeliveryDate between @paydatestartdate and @paydateenddate ";
|
dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00");
|
dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59");
|
break;
|
case "CT":
|
search += "and A.lm_date between @paydatestartdate and @paydateenddate ";
|
dynamicParams.Add("@paydatestartdate", paydatestartdate + " 00:00:00");
|
dynamicParams.Add("@paydateenddate", paydateenddate + " 23:59:59");
|
break;
|
default:
|
break;
|
}
|
}
|
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.idTopInventory,A.TopInventoryCode,A.TopInventoryName,A.route_code,B.default_route,R.name as route_name,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,A.data_sources,A.isstep,A.clerkuser,
|
B.priuserdefnvc1,B.priuserdefnvc2,B.priuserdefnvc3,B.priuserdefnvc4,B.priuserdefnvc5,B.priuserdefnvc6,A.printcount
|
from TK_Wrk_Man A
|
left join TKimp_Ewo W on A.m_po=W.wo and A.materiel_code=W.materiel_code and A.sbid=W.sbid
|
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
|
left join TFlw_Rout R on A.route_code=R.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[MES报废补单工单查询]
|
public static ToMessage MesBadOrderSearch(string wkshopcode, 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 (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and A.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
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.idTopInventory,A.TopInventoryCode,A.TopInventoryName,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,
|
B.priuserdefnvc1,B.priuserdefnvc2,B.priuserdefnvc3,B.priuserdefnvc4,B.priuserdefnvc5,B.priuserdefnvc6
|
from TK_Wrk_Man A
|
left join TKimp_Ewo W on A.m_po=W.wo and A.materiel_code=W.materiel_code and A.sbid=W.sbid
|
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 qty,relse_qty from TKimp_Ewo where id=@sourceid and wo=@sourcewo";
|
dynamicParams.Add("@sourceid", sourceid);
|
dynamicParams.Add("@sourcewo", sourcewo);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
//查询当前工单可修改数量=订单总数-已下达工单总数
|
sql = @"select isnull(sum(plan_qty),0) as plan_qty from TK_Wrk_Man
|
where sourceid=@sourceid and m_po=@sourcewo and 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(data0.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,S.ratio,(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,S.plan_qty,(isnull(S.plan_quantity,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 = "", route_code = "";
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
try
|
{
|
dynamic dynObj = JObject.Parse(us.mesSetting);
|
bool route = dynObj.route;
|
if (route) //工艺路线版
|
{
|
route_code = json.routecode;
|
}
|
else //工序版
|
{
|
route_code = null;
|
}
|
if (operType == "Add")
|
{
|
//写入工单表
|
sql = @"insert into TK_Wrk_Man(wo_code,wotype,status,wkshp_code,plan_qty,lm_user,lm_date,materiel_code,route_code,sourceid,m_po,saleOrderDeliveryDate,plan_startdate,plan_enddate,piroque,isaps,data_sources,isstep)
|
values(@wo_code,@wotype,@status,@wkshp_code,@plan_qty,@lm_user,@lm_date,@materiel_code,@route_code,@sourceid,@m_po,@saleOrderDeliveryDate,@plan_startdate,@plan_enddate,@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,
|
route_code = route_code,
|
sourceid = json.sourceid == "" ? null : json.sourceid, //无源单时赋值NULL
|
m_po = json.sourcewo,
|
saleOrderDeliveryDate = json.deliverydate,
|
plan_startdate = json.paystartdate,
|
plan_enddate = json.payenddate,
|
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,route_code,stepprice,plan_quantity,plan_qty,ratio,status,isbott,isend,lm_user,lm_date)
|
values(@wo_code,@seq,@step_code,@route_code,@stepprice,@plan_quantity,@plan_qty,@ratio,@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,
|
route_code = route_code,
|
stepprice = json.WorkListSub[i].stepprice,
|
plan_quantity = json.woqty,
|
plan_qty = json.WorkListSub[i].sumqty,
|
ratio=json.WorkListSub[i].ratio,
|
status = json.wostatus,
|
isbott = json.WorkListSub[i].isbott,
|
isend = json.WorkListSub[i].isend,
|
lm_user = us.usercode,
|
lm_date = 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,route_code=@route_code,sourceid=@sourceid,m_po=@m_po,saleOrderDeliveryDate=@saleOrderDeliveryDate,plan_startdate=@plan_startdate,plan_enddate=@plan_enddate,isstep=@isstep
|
where wo_code=@wo_code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = json.wocode,
|
wotype = json.wotype,
|
wkshp_code = json.wkshopcode,
|
plan_qty = json.woqty,
|
materiel_code = json.partcode,
|
route_code = route_code,
|
sourceid = json.sourceid == "" ? null : json.sourceid, //无源单时赋值NULL
|
m_po = json.sourcewo,
|
saleOrderDeliveryDate = json.deliverydate,
|
plan_startdate = json.paystartdate,
|
plan_enddate = json.payenddate,
|
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,route_code,stepprice,plan_quantity,plan_qty,ratio,status,isbott,isend,lm_user,lm_date)
|
values(@wo_code,@seq,@step_code,@route_code,@stepprice,@plan_quantity,@plan_qty,@ratio,@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,
|
route_code = route_code,
|
stepprice = json.WorkListSub[i].stepprice,
|
plan_quantity = json.woqty,
|
plan_qty = json.WorkListSub[i].sumqty,
|
ratio=json.WorkListSub[i].ratio,
|
status = json.wostatus,
|
isbott = json.WorkListSub[i].isbott,
|
isend = json.WorkListSub[i].isend,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
//判断源头单据是否来源ERP
|
if (json.data_sources == "ERP")
|
{
|
//判断工单修改数量差值是否为0
|
if (json.difference != "0")
|
{
|
string staus = "";
|
//查询订单总数,已下达数量
|
sql = @"select qty,relse_qty from TKimp_Ewo where id=@sourceid and wo=@sourcewo";
|
dynamicParams.Add("@sourceid", json.sourceid);
|
dynamicParams.Add("@sourcewo", json.sourcewo);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
//当前工单可修改数量=订单数量-非当前工单总下达工单数量
|
decimal qty = decimal.Parse(data0.Rows[0]["qty"].ToString());//订单总数
|
decimal relse_qty = decimal.Parse(data0.Rows[0]["relse_qty"].ToString());//订单已下达总数
|
relse_qty = relse_qty + decimal.Parse(json.difference);//新的下达数量=原始下达数量+差值(正负)
|
if (qty == relse_qty)
|
{
|
staus = "CREATED"; //全部下达
|
}
|
else
|
{
|
staus = "CREATING";//部分下达
|
}
|
//更新订单表状态、已下达数量
|
sql = @"update TKimp_Ewo set status=@status,relse_qty=@relse_qty where id=@sourceid and wo=@sourcewo";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
status = staus,
|
relse_qty = relse_qty,
|
sourceid = json.sourceid,
|
sourcewo = json.sourcewo
|
}
|
});
|
}
|
}
|
|
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 UpdateMesOrderStepListSearch(List<WorkStepList> worksteplist)
|
{
|
string sql = ""; decimal canupdate_qty = 0;
|
var dynamicParams = new DynamicParameters();
|
List<Dictionary<object, object>> dir = new List<Dictionary<object, object>>();
|
try
|
{
|
for (int i = 0; i < worksteplist.Count; i++)
|
{
|
// 创建一个新的字典
|
Dictionary<object, object> dict = new Dictionary<object, object>();
|
|
if (worksteplist[i].data_sources == "ERP") //数据来源ERP
|
{
|
|
//查询当前工单可修改数量=订单总数-已下达工单总数
|
sql = @"select isnull(plan_qty,0) as plan_qty from TK_Wrk_Man
|
where sourceid=@sourceid and m_po=@sourcewo and wo_code=@wocode";
|
dynamicParams.Add("@sourceid", worksteplist[i].sourceid);
|
dynamicParams.Add("@sourcewo", worksteplist[i].sourcewo);
|
dynamicParams.Add("@wocode", worksteplist[i].wocode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
//当前工单数量
|
canupdate_qty = decimal.Parse(data.Rows[0]["plan_qty"].ToString());
|
}
|
if (worksteplist[i].data_sources == "MES") //数据来源MES
|
{
|
if (worksteplist[i].sourceid == "" || worksteplist[i].sourceid == null) //无源单
|
{
|
//查询当前工单可修改数量=工单总数
|
sql = @"select plan_qty from TK_Wrk_Man where wo_code=@wo_code";
|
dynamicParams.Add("@wo_code", worksteplist[i].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 as stepcode,T.stepname,S.stepprice,S.ratio,(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,S.plan_qty,(isnull(S.plan_quantity,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", worksteplist[i].wocode);
|
var data1 = DapperHelper.selectdata(sql, dynamicParams);
|
|
// 向字典中添加数据
|
dict.Add("canupdate_qty", canupdate_qty);
|
dict.Add("stepdata", data1);
|
// 将字典添加到列表中
|
dir.Add(dict);
|
mes.code = "200";
|
mes.count = dir.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 AddUpdateMesOrderList(string operType, List<WorkList> json, User us)
|
{
|
string sql = "", route_code = "";
|
var dynamicParams = new DynamicParameters();
|
List<object> list = new List<object>();
|
try
|
{
|
dynamic dynObj = JObject.Parse(us.mesSetting);
|
bool route = dynObj.route;
|
if (!route) //工序版
|
{
|
route_code = null;
|
}
|
if (operType == "Add")
|
{
|
for (int i = 0; i < json.Count; i++)
|
{
|
route_code = json[i].routecode;
|
//写入工单表
|
sql = @"insert into TK_Wrk_Man(wo_code,wotype,status,wkshp_code,plan_qty,lm_user,lm_date,materiel_code,route_code,sourceid,m_po,saleOrderDeliveryDate,plan_startdate,plan_enddate,piroque,isaps,data_sources,isstep)
|
values(@wo_code,@wotype,@status,@wkshp_code,@plan_qty,@lm_user,@lm_date,@materiel_code,@route_code,@sourceid,@m_po,@saleOrderDeliveryDate,@plan_startdate,@plan_enddate,@orderlev,@isaps,@data_sources,@isstep)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = json[i].wocode,
|
wotype = json[i].wotype,
|
status = json[i].wostatus,
|
wkshp_code = json[i].wkshopcode,
|
plan_qty = json[i].woqty,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString(),
|
materiel_code = json[i].partcode,
|
route_code = route_code,
|
sourceid = json[i].sourceid == "" ? null : json[i].sourceid, //无源单时赋值NULL
|
m_po = json[i].sourcewo,
|
saleOrderDeliveryDate = json[i].deliverydate,
|
plan_startdate = json[i].paystartdate,
|
plan_enddate = json[i].payenddate,
|
orderlev = "3",//优先级：特级（1） 紧急（2） 正常（3）
|
isaps = "N", //是否排产,默认N Y=是 N=否
|
data_sources = json[i].data_sources,
|
isstep = json[i].isstep //是否关联工序
|
}
|
});
|
//写入工序任务表
|
for (int j = 0; j < json[i].WorkListSub.Count; j++)
|
{
|
sql = @"insert into TK_Wrk_Step(wo_code,seq,step_code,route_code,stepprice,plan_quantity,plan_qty,ratio,status,isbott,isend,lm_user,lm_date)
|
values(@wo_code,@seq,@step_code,@route_code,@stepprice,@plan_quantity,@plan_qty,@ratio,@status,@isbott,@isend,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = json[i].wocode,
|
seq = json[i].WorkListSub[j].stepseq,
|
step_code = json[i].WorkListSub[j].stepcode,
|
route_code = route_code,
|
stepprice = json[i].WorkListSub[j].stepprice,
|
plan_quantity = json[i].woqty,
|
plan_qty = json[i].WorkListSub[j].sumqty,
|
ratio=json[i].WorkListSub[j].ratio,
|
status = json[i].wostatus,
|
isbott = json[i].WorkListSub[j].isbott,
|
isend = json[i].WorkListSub[j].isend,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "新增", "新增了工单:" + json[0].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")
|
{
|
for (int i = 0; i < json.Count; i++)
|
{
|
route_code = json[i].routecode;
|
//修改工单表
|
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,route_code=@route_code,sourceid=@sourceid,m_po=@m_po,saleOrderDeliveryDate=@saleOrderDeliveryDate,plan_startdate=@plan_startdate,plan_enddate=@plan_enddate,isstep=@isstep
|
where wo_code=@wo_code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = json[i].wocode,
|
wotype = json[i].wotype,
|
wkshp_code = json[i].wkshopcode,
|
plan_qty = json[i].woqty,
|
materiel_code = json[i].partcode,
|
route_code = route_code,
|
sourceid = json[i].sourceid == "" ? null : json[i].sourceid, //无源单时赋值NULL
|
m_po = json[i].sourcewo,
|
saleOrderDeliveryDate = json[i].deliverydate,
|
plan_startdate = json[i].paystartdate,
|
plan_enddate = json[i].payenddate,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString(),
|
isstep = json[i].isstep //是否关联工序
|
}
|
});
|
//删除工单工序表
|
sql = @"delete TK_Wrk_Step where wo_code=@wo_code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = json[i].wocode
|
}
|
});
|
//写入工单工序表
|
for (int j = 0; j < json[i].WorkListSub.Count; j++)
|
{
|
sql = @"insert into TK_Wrk_Step(wo_code,seq,step_code,route_code,stepprice,plan_quantity,plan_qty,ratio,status,isbott,isend,lm_user,lm_date)
|
values(@wo_code,@seq,@step_code,@route_code,@stepprice,@plan_quantity,@plan_qty,@ratio,@status,@isbott,@isend,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = json[i].wocode,
|
seq = json[i].WorkListSub[j].stepseq,
|
step_code = json[i].WorkListSub[j].stepcode,
|
route_code = route_code,
|
stepprice = json[i].WorkListSub[j].stepprice,
|
plan_quantity = json[i].woqty,
|
plan_qty = json[i].WorkListSub[j].sumqty,
|
ratio=json[i].WorkListSub[j].ratio,
|
status = json[i].wostatus,
|
isbott = json[i].WorkListSub[j].isbott,
|
isend = json[i].WorkListSub[j].isend,
|
lm_user = us.usercode,
|
lm_date = DateTime.Now.ToString()
|
}
|
});
|
}
|
//判断源头单据是否来源ERP
|
if (json[i].data_sources == "ERP")
|
{
|
//判断工单修改数量差值是否为0
|
if (json[i].difference != "0")
|
{
|
string staus = "";
|
//查询订单总数,已下达数量
|
sql = @"select qty,relse_qty from TKimp_Ewo where id=@sourceid and wo=@sourcewo";
|
dynamicParams.Add("@sourceid", json[i].sourceid);
|
dynamicParams.Add("@sourcewo", json[i].sourcewo);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
//当前工单可修改数量=订单数量-非当前工单总下达工单数量
|
decimal qty = decimal.Parse(data0.Rows[0]["qty"].ToString());//订单总数
|
decimal relse_qty = decimal.Parse(data0.Rows[0]["relse_qty"].ToString());//订单已下达总数
|
relse_qty = relse_qty + decimal.Parse(json[i].difference);//新的下达数量=原始下达数量+差值(正负)
|
if (qty == relse_qty)
|
{
|
staus = "CREATED"; //全部下达
|
}
|
else
|
{
|
staus = "CREATING";//部分下达
|
}
|
//更新订单表状态、已下达数量
|
sql = @"update TKimp_Ewo set status=@status,relse_qty=@relse_qty where id=@sourceid and wo=@sourcewo";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
status = staus,
|
relse_qty = relse_qty,
|
sourceid = json[i].sourceid,
|
sourcewo = json[i].sourcewo
|
}
|
});
|
}
|
}
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "修改", "修改了工单:" + string.Join(",", json.Select(j => j.wocode.ToString())), 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 UpdateMesOrderPrintCount(string wo_code)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//更新工单打印次数
|
sql = @"update TK_Wrk_Man set printcount=printcount+1 where wo_code=@wo_code";
|
list.Add(new { str = sql, parm = new { wo_code = wo_code } });
|
|
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[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 = @"delete TK_Wrk_Man 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[MES工单查找历史引用最新工序信息]
|
public static ToMessage MesOrderNewStepContent(string wkshopcode, string routecode, string partcode, User us)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
dynamic dynObj = JObject.Parse(us.mesSetting);
|
bool route = dynObj.route;
|
if (route) //工艺路线版
|
{
|
//MES工单查找历史引用最新工序信息
|
sql = @"select S.wo_code,S.seq,S.step_code,T.stepname as step_name,S.stepprice,S.isbott,S.isend,S.ratio
|
from TK_Wrk_Step S
|
inner join (
|
select top 1 A.wo_code,A.route_code from TK_Wrk_Man A
|
inner join TK_Wrk_Step S on A.wo_code=S.wo_code and A.route_code=S.route_code
|
where A.materiel_code=@partcode and A.wkshp_code=@wkshopcode and A.route_code=@routecode
|
order by A.lm_date desc
|
) as W on S.wo_code=W.wo_code and S.route_code=W.route_code
|
left join TStep T on S.step_code=T.stepcode
|
order by S.seq";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
dynamicParams.Add("@partcode", partcode);
|
dynamicParams.Add("@routecode", routecode);
|
}
|
else
|
{
|
//MES工单查找历史引用最新工序信息
|
sql = @"select S.wo_code,S.seq,S.step_code,T.stepname as step_name,S.stepprice,S.isbott,S.isend,S.ratio
|
from TK_Wrk_Step S
|
inner join (
|
select top 1 A.wo_code from TK_Wrk_Man A
|
inner join TK_Wrk_Step S on A.wo_code=S.wo_code
|
where A.materiel_code=@partcode and A.wkshp_code=@wkshopcode
|
order by A.lm_date desc
|
) as W on S.wo_code=W.wo_code
|
left join TStep T on S.step_code=T.stepcode
|
order by S.seq";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
dynamicParams.Add("@partcode", partcode);
|
}
|
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
mes.code = "200";
|
mes.count = data.Rows.Count;
|
mes.data = data;
|
mes.message = "查询成功!";
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[MES工单查看工单SOP]
|
public static ToMessage MesOrderSopSearch(string wocode, string materielcode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//获取SOP文件信息
|
sql = @"select filename,filepath,version from TWrkOrderSop
|
where wo=@wocode and materielcode=@materielcode
|
order by version";
|
dynamicParams.Add("@wocode", wocode);
|
dynamicParams.Add("@materielcode", materielcode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.data = data;
|
}
|
else
|
{
|
mes.code = "300";
|
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[MES工单查看工艺SOP]
|
public static ToMessage MesOrderProcessSopSearch(string materielcode, string routecode, string stepcode, User us)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
dynamic dynObj = JObject.Parse(us.mesSetting);
|
bool route = dynObj.route;
|
if (route) //工艺路线版
|
{
|
//获取SOP文件信息
|
sql = @"select filename,filepath,version from TProcessSop
|
where materielcode=@materielcode and routecode=@routecode and stepcode=@stepcode
|
order by version";
|
dynamicParams.Add("@materielcode", materielcode);
|
dynamicParams.Add("@routecode", routecode);
|
dynamicParams.Add("@stepcode", stepcode);
|
}
|
else
|
{
|
//获取SOP文件信息
|
sql = @"select filename,filepath,version from TProcessSop
|
where materielcode=@materielcode and stepcode=@stepcode
|
order by version";
|
dynamicParams.Add("@materielcode", materielcode);
|
dynamicParams.Add("@stepcode", stepcode);
|
}
|
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
mes.code = "200";
|
mes.count = data.Rows.Count;
|
mes.data = data;
|
mes.message = "查询成功!";
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
|
#region[MES工单派发提交]
|
public static ToMessage MesOrderDistribution(string[] wocodelist, User us)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
try
|
{
|
//更新工单表状态
|
sql = @"update TK_Wrk_Man set status=@status,distributionuser=@distributionuser,distributiontime=@distributiontime where wo_code in @wocode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wocode = wocodelist,
|
status = "ALLO",
|
distributionuser=us.usercode,
|
distributiontime= DateTime.Now.ToString()
|
}
|
});
|
//更新工序任务表状态
|
sql = @"update TK_Wrk_Step set status=@status where wo_code in @wocode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wocode = wocodelist,
|
status = "ALLO"
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "派发", "派发了工单:" + string.Join(",", wocodelist), 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[工单关闭列表查询]
|
public static ToMessage MesOrderClosedSearch(string mesorderstus, string wkshopcode, 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 == "CLOSED")
|
{
|
search += "and A.status=@mesorderstus ";
|
dynamicParams.Add("@mesorderstus", "CLOSED");
|
}
|
if (mesorderstus == "NOCLOSED")
|
{
|
search += "and A.status<>@mesorderstus ";
|
dynamicParams.Add("@mesorderstus", "CLOSED");
|
}
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and A.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
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,A.data_sources,
|
B.priuserdefnvc1,B.priuserdefnvc2,B.priuserdefnvc3,B.priuserdefnvc4,B.priuserdefnvc5,B.priuserdefnvc6
|
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, string[] wocodelist)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
for (int i = 0; i < wocodelist.Length; i++)
|
{
|
//关闭工单对应工序任务
|
sql = @"update TK_Wrk_Step set status='CLOSED' where wo_code in @wocode";
|
list.Add(new { str = sql, parm = new { wocode = wocodelist } });
|
//回写工单表状态为(关闭)
|
sql = @"update TK_Wrk_Man set status='CLOSED',closeuser=@username,closedate=@closedate where wo_code in @wocode";
|
list.Add(new { str = sql, parm = new { wocode = wocodelist, username = us.usercode, closedate = DateTime.Now.ToString() } });
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "关闭", "关闭了工单:" + string.Join(",", wocodelist), 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 MesOrderBitchReverseClosedSeave(User us, string[] wocodelist)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
for (int i = 0; i < wocodelist.Length; i++)
|
{
|
//关闭工单对应工序任务
|
sql = @"update TK_Wrk_Step set status=closebeforestatus where wo_code in @wocode";
|
list.Add(new { str = sql, parm = new { wocode = wocodelist } });
|
//回写工单表状态为(关闭)
|
sql = @"update TK_Wrk_Man set status=closebeforestatus,closeuser=@username,closedate=@closedate where wo_code in @wocode";
|
list.Add(new { str = sql, parm = new { wocode = wocodelist, username = us.usercode, closedate = DateTime.Now.ToString() } });
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "反关闭", "关闭了工单:" + string.Join(",", wocodelist), 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 MesOrderStepSearch(string wkshopcode, string wocode, string partcode, string partname, string partspec, int startNum, int endNum, string prop, string order)
|
{
|
var sql = "";
|
string search = "";
|
string ordercode = "";
|
string stepcode = "";
|
var dynamicParams = new DynamicParameters();
|
var total = 0; //总条数
|
try
|
{
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and B.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
if (wocode != "" && wocode != null)
|
{
|
string[] arra = wocode.Split(';');
|
if (arra.Length == 1) //工单号二维码
|
{
|
ordercode = arra[0]; //获取指定字符串前面的字符
|
}
|
if (arra.Length == 2) //工单号+工序号二维码
|
{
|
ordercode = arra[0]; //获取指定字符串前面的字符
|
stepcode = arra[1]; //获取指定字符串前面的字符
|
}
|
|
if (ordercode != "" && (stepcode == null || stepcode == "")) //工单号不为空,工序号为空
|
{
|
search += "and A.wo_code=@ordercode ";
|
dynamicParams.Add("@ordercode", ordercode);
|
}
|
if (ordercode != "" && stepcode != "") //工单号不为空,工序号不为空
|
{
|
search += "and A.wo_code=@ordercode ";
|
dynamicParams.Add("@ordercode", ordercode);
|
search += "and S.stepcode=@stepcode ";
|
dynamicParams.Add("@stepcode", stepcode);
|
}
|
}
|
if (stepcode != "")
|
{
|
//查找当前工序属性
|
sql = @"select * from TStep where stepcode=@stepcode";
|
dynamicParams.Add("@stepcode", stepcode);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
if (data0.Rows[0]["FLWTYPE"].ToString() == "W")
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前工序任务为外协工序任务,请前往外协操作页签执行!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
}
|
if (partcode != "" && partcode != null)
|
{
|
search += "and M.partcode like '%'+@partcode+'%' ";
|
dynamicParams.Add("@partcode", partcode);
|
}
|
if (partname != "" && partname != null)
|
{
|
search += "and M.partname like '%'+@partname+'%' ";
|
dynamicParams.Add("@partname", partname);
|
}
|
if (partspec != "" && partspec != null)
|
{
|
search += "and M.partspec like '%'+@partspec+'%' ";
|
dynamicParams.Add("@partspec", partspec);
|
}
|
//根据条件查询工单工序任务(自制工序)
|
sql = @"select A.id,A.status,B.wkshp_code,T.torg_name as wkshp_name,A.wo_code,M.partcode,M.partname,M.partspec,A.seq,A.isbott,A.isend,
|
S.stepcode,S.stepname,S.descr,A.plan_quantity,A.plan_qty,A.good_qty,A.ng_qty,A.laborbad_qty,A.materielbad_qty,B.lm_date,
|
M.priuserdefnvc1,M.priuserdefnvc2,M.priuserdefnvc3,M.priuserdefnvc4,M.priuserdefnvc5,M.priuserdefnvc6
|
from TK_Wrk_Step A
|
left join TK_Wrk_Man B on A.wo_code=B.wo_code
|
left join TMateriel_Info M on B.materiel_code=M.partcode
|
left join TStep S on A.step_code=S.stepcode
|
left join TOrganization T on B.wkshp_code=T.torg_code
|
where A.status in('ALLO','START') and S.flwtype='Z' " + search;
|
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
|
mes.code = "200";
|
mes.count = total;
|
mes.message = "查询成功!";
|
mes.data = data.ToList();
|
return mes;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[生产开报工扫码获取工单对应工序任务(外协)]
|
public static ToMessage MesOrderWxStepSearch(string wkshopcode, string wocode, string partcode, string partname, string partspec, int startNum, int endNum, string prop, string order)
|
{
|
var sql = "";
|
string search = "";
|
string ordercode = "";
|
string stepcode = "";
|
var dynamicParams = new DynamicParameters();
|
var total = 0; //总条数
|
try
|
{
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and B.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
if (wocode != "" && wocode != null)
|
{
|
string[] arra = wocode.Split(';');
|
if (arra.Length == 1) //工单号二维码
|
{
|
ordercode = arra[0]; //获取指定字符串前面的字符
|
}
|
if (arra.Length == 2) //工单号+工序号二维码
|
{
|
ordercode = arra[0]; //获取指定字符串前面的字符
|
stepcode = arra[1]; //获取指定字符串前面的字符
|
}
|
|
if (ordercode != "" && (stepcode == null || stepcode == "")) //工单号不为空,工序号为空
|
{
|
search += "and A.wo_code=@ordercode ";
|
dynamicParams.Add("@ordercode", ordercode);
|
}
|
if (ordercode != "" && stepcode != "") //工单号不为空,工序号不为空
|
{
|
search += "and A.wo_code=@ordercode ";
|
dynamicParams.Add("@ordercode", ordercode);
|
search += "and S.stepcode=@stepcode ";
|
dynamicParams.Add("@stepcode", stepcode);
|
}
|
}
|
if (stepcode != "")
|
{
|
//查找当前工序属性
|
sql = @"select * from TStep where stepcode=@stepcode";
|
dynamicParams.Add("@stepcode", stepcode);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count > 0)
|
{
|
if (data0.Rows[0]["FLWTYPE"].ToString() == "Z")
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前工序任务为自制工序任务,请前往自制操作页签执行!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
}
|
if (partcode != "" && partcode != null)
|
{
|
search += "and M.partcode like '%'+@partcode+'%' ";
|
dynamicParams.Add("@partcode", partcode);
|
}
|
if (partname != "" && partname != null)
|
{
|
search += "and M.partname like '%'+@partname+'%' ";
|
dynamicParams.Add("@partname", partname);
|
}
|
if (partspec != "" && partspec != null)
|
{
|
search += "and M.partspec like '%'+@partspec+'%' ";
|
dynamicParams.Add("@partspec", partspec);
|
}
|
//根据条件查询工单工序任务(自制工序)
|
sql = @"select A.id,A.status,B.wkshp_code,T.torg_name as wkshp_name,A.wo_code,M.partcode,M.partname,M.partspec,A.seq,A.isbott,A.isend,
|
S.stepcode,S.stepname,S.descr,A.plan_quantity,A.plan_qty,A.good_qty,A.ng_qty,
|
(select isnull(sum(fqty),0) as fqty from TK_Wrk_OutRecord where wo_code=A.wo_code and step_code=A.step_code and style='F') as fqty,
|
A.laborbad_qty,A.materielbad_qty,A.plan_startdate,A.plan_enddate,B.lm_date,
|
M.priuserdefnvc1,M.priuserdefnvc2,M.priuserdefnvc3,M.priuserdefnvc4,M.priuserdefnvc5,M.priuserdefnvc6
|
from TK_Wrk_Step A
|
left join TK_Wrk_Man B on A.wo_code=B.wo_code
|
left join TMateriel_Info M on B.materiel_code=M.partcode
|
left join TStep S on A.step_code=S.stepcode
|
left join TOrganization T on B.wkshp_code=T.torg_code
|
where A.status in('ALLO','START') and S.flwtype='W' " + search;
|
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
|
mes.code = "200";
|
mes.count = total;
|
mes.message = "查询成功!";
|
mes.data = data.ToList();
|
return mes;
|
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[生产开报工扫码获取工单对应工序任务(不良)]
|
public static ToMessage MesOrderNgStepSearch(string wkshopcode, string wocode, string partcode, string partname, string partspec, int startNum, int endNum, string prop, string order)
|
{
|
var sql = "";
|
string search = "";
|
string ordercode = "";
|
string stepcode = "";
|
var dynamicParams = new DynamicParameters();
|
var total = 0; //总条数
|
try
|
{
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and B.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
if (wocode != "" && wocode != null)
|
{
|
string[] arra = wocode.Split(';');
|
if (arra.Length == 1) //工单号二维码
|
{
|
ordercode = arra[0]; //获取指定字符串前面的字符
|
}
|
if (arra.Length == 2) //工单号+工序号二维码
|
{
|
ordercode = arra[0]; //获取指定字符串前面的字符
|
stepcode = arra[1]; //获取指定字符串前面的字符
|
}
|
|
if (ordercode != "" && (stepcode == null || stepcode == "")) //工单号不为空,工序号为空
|
{
|
search += "and A.wo_code=@ordercode ";
|
dynamicParams.Add("@ordercode", ordercode);
|
}
|
if (ordercode != "" && stepcode != "") //工单号不为空,工序号不为空
|
{
|
search += "and A.wo_code=@ordercode ";
|
dynamicParams.Add("@ordercode", ordercode);
|
search += "and S.stepcode=@stepcode ";
|
dynamicParams.Add("@stepcode", stepcode);
|
}
|
}
|
if (stepcode != "")
|
{
|
//查找当前工序任务
|
sql = @"select * from TK_Wrk_Step where step_code=@stepcode and wo_code=@ordercode";
|
dynamicParams.Add("@stepcode", stepcode);
|
dynamicParams.Add("@ordercode", ordercode);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count <= 0)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前工序任务不存在,无效条码!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
if (partcode != "" && partcode != null)
|
{
|
search += "and M.partcode like '%'+@partcode+'%' ";
|
dynamicParams.Add("@partcode", partcode);
|
}
|
if (partname != "" && partname != null)
|
{
|
search += "and M.partname like '%'+@partname+'%' ";
|
dynamicParams.Add("@partname", partname);
|
}
|
if (partspec != "" && partspec != null)
|
{
|
search += "and M.partspec like '%'+@partspec+'%' ";
|
dynamicParams.Add("@partspec", partspec);
|
}
|
//根据条件查询工单工序任务(自制工序)
|
sql = @"select A.id,B.wkshp_code,T.torg_name as wkshp_name,A.wo_code,M.partcode,M.partname,M.partspec,A.seq,A.isend,
|
S.stepcode,S.stepname,S.descr,A.plan_quantity,A.plan_qty,A.good_qty,A.ng_qty,A.laborbad_qty,A.materielbad_qty,B.lm_date,
|
M.priuserdefnvc1,M.priuserdefnvc2,M.priuserdefnvc3,M.priuserdefnvc4,M.priuserdefnvc5,M.priuserdefnvc6
|
from TK_Wrk_Step A
|
left join TK_Wrk_Man B on A.wo_code=B.wo_code
|
left join TMateriel_Info M on B.materiel_code=M.partcode
|
left join TStep S on A.step_code=S.stepcode
|
left join TOrganization T on B.wkshp_code=T.torg_code
|
where A.status in('ALLO','START') and A.ng_qty>0 " + search;
|
var data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
|
mes.code = "200";
|
mes.count = total;
|
mes.message = "查询成功!";
|
mes.data = data.ToList();
|
return mes;
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[生产开报工扫码获取工单对应工序任务(不良明细)]
|
public static ToMessage MesOrderNgSubStepSearch(string wocode, string stepcode)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
var dynamicParams1 = new DynamicParameters();
|
Dictionary<string, object> list = new Dictionary<string, object>();
|
ScanStartReportData rt = new ScanStartReportData();
|
try
|
{
|
//1.根据工单+工序查找当前工序是否首道工序
|
sql = @"select A.wo_code,P.partcode,P.partname,P.partspec, T.stepcode,T.stepname,A.seq,T.flwtype,T.descr,A.status,
|
A.plan_quantity,A.plan_qty,A.good_qty,A.ng_qty,A.laborbad_qty,A.materielbad_qty,A.isbott,A.isend
|
from TK_Wrk_Step A
|
left join TStep T on A.step_code=T.stepcode
|
left join TK_Wrk_Man M on A.wo_code=M.wo_code
|
left join TMateriel_Info P on M.materiel_code=P.partcode
|
where A.wo_code=@ordercode and A.step_code=@stepcode";
|
dynamicParams.Add("@ordercode", wocode);
|
dynamicParams.Add("@stepcode", stepcode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
rt.wo_code = data.Rows[0]["wo_code"].ToString(); //工单号
|
rt.partnumber = data.Rows[0]["partcode"].ToString(); //产品编码
|
rt.partname = data.Rows[0]["partname"].ToString(); //产品名称
|
rt.partspec = data.Rows[0]["partspec"].ToString(); //产品规格
|
rt.stepcode = data.Rows[0]["stepcode"].ToString(); //工序编码
|
rt.stepname = data.Rows[0]["stepname"].ToString(); //工序名称
|
rt.stepdesc = data.Rows[0]["descr"].ToString(); //工序描述
|
rt.planqty = decimal.Parse(data.Rows[0]["plan_qty"].ToString()); //任务超产总数量
|
rt.planquantity= decimal.Parse(data.Rows[0]["plan_quantity"].ToString()); //任务数量
|
rt.noreportqty = decimal.Parse(data.Rows[0]["good_qty"].ToString()); //报工数量
|
rt.noputqty = decimal.Parse(data.Rows[0]["ng_qty"].ToString()); //不良数量
|
string isend = data.Rows[0]["isend"].ToString();//末道工序
|
rt.seq = data.Rows[0]["seq"].ToString();//工序序号
|
|
//获取当前工序下道工序
|
sql = @"select A.isbott,A.isend,T.stepcode,T.stepname from TK_Wrk_Step A
|
left join TStep T on A.step_code=T.stepcode
|
where A.wo_code=@ordercode and A.seq=@seq+1 ";
|
dynamicParams.Add("@ordercode", wocode);
|
dynamicParams.Add("@seq", decimal.Parse(data.Rows[0]["seq"].ToString()));
|
var dt0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt0.Rows.Count > 0) //有下道工序
|
{
|
rt.nextstepcode = dt0.Rows[0]["stepcode"].ToString();//下道工序编码
|
rt.nextstepname = dt0.Rows[0]["stepname"].ToString();//下道工序名称
|
}
|
if (isend == "Y") //当前工序是末道工序
|
{
|
rt.nextstepcode = "";//赋空
|
rt.nextstepname = "";//赋空
|
}
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前工序任务不存在!";
|
mes.data = null;
|
return mes;
|
}
|
//根据条件查询工单工序报工(收料)记录,且不良数量大于0
|
//存储过程名
|
sql = @"h_p_IFCLD_MesReportDefectHandleSelect";
|
dynamicParams1.Add("@ordercode", wocode);
|
dynamicParams1.Add("@stepcode", stepcode);
|
DataTable dt = DapperHelper.selectProcedure(sql, dynamicParams1);
|
if (dt.Rows.Count > 0)
|
{
|
list.Add("data1", rt);
|
list.Add("data2", dt);
|
mes.code = "200";
|
mes.count = 0;
|
mes.message = "查询成功!";
|
mes.data = list;
|
}
|
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 MesOrderStepStart(string OperType, string SelectType, string wocode, string stepcode, User us)
|
{
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
dynamic dynObj = JObject.Parse(us.mesSetting);
|
bool isOrder = dynObj.isOrder;
|
switch (OperType)
|
{
|
case "ZZ":
|
if (isOrder) //按序生产
|
{
|
mes = ScanStartReport.ZZEncodingSeach(wocode, stepcode);
|
}
|
else //不按序生产
|
{
|
mes = ScanStartReport.NoZZEncodingSeach(wocode, stepcode);
|
}
|
break;
|
case "WX":
|
if (isOrder) //按序收发料
|
{
|
mes = ScanStartReport.WXEncodingSeach(SelectType, wocode, stepcode);
|
}
|
else //不按序收发料
|
{
|
mes = ScanStartReport.NoWXEncodingSeach(SelectType, wocode, stepcode);
|
}
|
break;
|
default:
|
break;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[生产开报工,报工提交]
|
public static ToMessage SavaMesOrderStepReport(string mesordercode, string partcode, string stepseq, string stepcode, string stepprice, string eqpcode, string inbarcode, string reckway, string usergroupcode, string reportuser,string payrate, string taskqty, string startqty, string reportqty, List<ReportDefectList> defectobjs, string remarks, User us)
|
{
|
var sql = "";
|
decimal ngqty = 0;
|
string[] arra1 = new string[] { };
|
List<object> list0 = new List<object>();
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
string date = DateTime.Now.ToString(); //获取系统时间
|
//获取报工人员、分配比例
|
//JArray arra = (JArray)Newtonsoft.Json.JsonConvert.DeserializeObject(reportuser);
|
//判断是否有不良数据
|
var groupedItems = defectobjs.GroupBy(item => item.defect_code) // 按编码code分组
|
.Select(group => new { defect_code = group.Key, badqty = group.Sum(item => Convert.ToDecimal(item.badqty)) }).ToList(); // 汇总不良数量
|
if (groupedItems.Count <= 0)
|
{
|
ngqty = 0;
|
}
|
else
|
{
|
//汇总不良数量
|
ngqty = groupedItems.Sum(item => Convert.ToDecimal(item.badqty));
|
}
|
list.Clear();
|
//判断是否有开工记录(无新增)
|
sql = @"select * from TK_Wrk_Record where wo_code=@wo_code and step_code=@step_code and style='S' and eqp_code=@eqpcode";
|
dynamicParams.Add("@wo_code", mesordercode);
|
dynamicParams.Add("@step_code", stepcode);
|
dynamicParams.Add("@eqpcode", eqpcode);
|
var data0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (data0.Rows.Count <= 0)
|
{
|
//写入开报工记录表
|
sql = @"insert into TK_Wrk_Record(wo_code,step_seq,step_code,step_price,eqp_code,materiel_code,open_person,open_time,task_qty,start_qty,style,lm_user,lm_date)
|
values(@mesordercode,@stepseq,@stepcode,@step_price,@eqpcode,@partcode,@username,@opentime,@taskqty,@startqty,@style,@lm_user,@lm_date)";
|
list0.Add(new { str = sql, parm = new { mesordercode = mesordercode, stepseq = stepseq, stepcode = stepcode, step_price = stepprice, eqpcode = eqpcode, partcode = partcode, username = us.usercode, opentime = date, taskqty = taskqty, startqty = startqty, style = "S", lm_user = us.usercode, lm_date = date } });
|
//回写工单工序表状态为START: 开工
|
sql = @"update TK_Wrk_Step set status='START' where wo_code=@mesordercode and step_code=@stepcode";
|
list0.Add(new { str = sql, parm = new { mesordercode = mesordercode, stepcode = stepcode } });
|
|
//回写工单表状态为： 开工：START
|
sql = @"update TK_Wrk_Man set status='START' where wo_code=@mesordercode";
|
list0.Add(new { str = sql, parm = new { mesordercode = mesordercode } });
|
bool st = DapperHelper.DoTransaction(list0);
|
if (st)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "开工", "开工了工单:" + mesordercode + "工序:" + 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;
|
}
|
}
|
|
//判断是否有报工记录(有：修改 无：新增)
|
sql = @"select * from TK_Wrk_Record where wo_code=@wo_code and step_code=@step_code and style='B'";
|
dynamicParams.Add("@wo_code", mesordercode);
|
dynamicParams.Add("@step_code", stepcode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
////获取开工记录的默认选中的设备(产线)与报工时的设备产线做对比判断
|
//sql = @"select A.eqp_code,B.name from TK_Wrk_Record A
|
// inner join TEqpInfo B on A.eqp_code=B.code
|
// where A.wo_code=@wo_code and A.step_code=@step_code and eqp_code=@eqpcode and A.style='S'";
|
//dynamicParams.Add("@wo_code", mesordercode);
|
//dynamicParams.Add("@step_code", stepcode);
|
//dynamicParams.Add("@eqpcode", eqpcode);
|
//var da = DapperHelper.selectdata(sql, dynamicParams);
|
//if (da.Rows[0]["EQP_CODE"].ToString() != eqpcode)
|
//{
|
// mes.code = "300";
|
// mes.count = 0;
|
// mes.message = "操作失败,当前报工产线应为：" + da.Rows[0]["NAME"].ToString() + "!";
|
// mes.data = null;
|
// return mes;
|
//}
|
if (data.Rows.Count > 0)
|
{
|
//获取主表最大ID
|
sql = @"select ISNULL(IDENT_CURRENT('TK_Wrk_Record')+1,1) as id";
|
var dt = DapperHelper.selecttable(sql);
|
|
//写入开报工记录表
|
sql = @"insert into TK_Wrk_Record(wo_code,step_seq,step_code,step_price,eqp_code,materiel_code,task_qty,start_qty,good_qty,ng_qty,style,lm_user,lm_date,inbarcode)
|
values(@mesordercode,@stepseq,@stepcode,@step_price,@eqpcode,@partcode,@taskqty,@startqty,@reportqty,@ngqty,@style,@lm_user,@lm_date,@inbarcode)";
|
list.Add(new { str = sql, parm = new { mesordercode = mesordercode, stepseq = stepseq, stepcode = stepcode, step_price = stepprice, eqpcode = eqpcode, partcode = partcode, taskqty = taskqty, startqty = startqty, reportqty = reportqty, ngqty = ngqty, style = "B", lm_user = us.usercode, lm_date = date, inbarcode = inbarcode } });
|
|
//写入子表
|
sql = @"insert into TK_Wrk_RecordSub(m_id,eqp_code,payrate,report_person,report_date,report_qty,reckway,usergroup_code,ratio,ng_qty,style,lm_user,lm_date)
|
values(@m_id,@eqp_code,@payrate,@report_person,@report_date,@report_qty,@reckway,@usergroup_code,@ratio,@ng_qty,@style,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
m_id = int.Parse(dt.Rows[0]["ID"].ToString()),
|
eqp_code = eqpcode,
|
payrate= payrate,
|
report_person = reportuser,
|
report_date = date,
|
report_qty = reportqty,
|
reckway = reckway,
|
usergroup_code = usergroupcode,
|
ratio = 0, //分配比例
|
ng_qty = ngqty,
|
style = "B",
|
lm_user = us.usercode,
|
lm_date = date
|
}
|
});
|
if (groupedItems.Count > 0)
|
{
|
//写入缺陷记录表
|
for (int i = 0; i < groupedItems.Count; i++)
|
{
|
sql = @"insert into CSR_WorkRecord_Defect(record_id,wo_code,partnumber,step_seq,step_code,defect_qty,defect_pendqty,defect_code,remarks,style,lm_user,lm_date)
|
values(@record_id,@wo_code,@partcode,@stepseq,@stepcode,@ngqty,@defect_pendqty,@defect_code,@remarks,@style,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new { record_id = int.Parse(dt.Rows[0]["ID"].ToString()), wo_code = mesordercode, partcode = partcode, stepseq = stepseq, stepcode = stepcode, ngqty = groupedItems[i].badqty, defect_pendqty = groupedItems[i].badqty, defect_code = groupedItems[i].defect_code, remarks = remarks, style = "B", lm_user = us.usercode, lm_date = date } });
|
|
}
|
}
|
}
|
else
|
{
|
//获取主表最大ID
|
sql = @"select ISNULL(IDENT_CURRENT('TK_Wrk_Record')+1,1) as id";
|
var dt = DapperHelper.selecttable(sql);
|
//写入开报工记录表
|
sql = @"insert into TK_Wrk_Record(wo_code,step_seq,step_code,step_price,eqp_code,materiel_code,task_qty,start_qty,good_qty,ng_qty,style,lm_user,lm_date,inbarcode)
|
values(@mesordercode,@stepseq,@stepcode,@step_price,@eqpcode,@partcode,@taskqty,@startqty,@reportqty,@ngqty,@style,@lm_user,@lm_date,@inbarcode)";
|
list.Add(new { str = sql, parm = new { mesordercode = mesordercode, stepseq = stepseq, stepcode = stepcode, step_price = stepprice, eqpcode = eqpcode, partcode = partcode, taskqty = taskqty, startqty = startqty, reportqty = reportqty, ngqty = ngqty, style = "B", lm_user = us.usercode, lm_date = date, inbarcode = inbarcode } });
|
|
//写入子表
|
sql = @"insert into TK_Wrk_RecordSub(m_id,eqp_code,payrate,report_person,report_date,report_qty,reckway,usergroup_code,ratio,ng_qty,style,lm_user,lm_date)
|
values(@m_id,@eqp_code,@payrate,@report_person,@report_date,@report_qty,@reckway,@usergroup_code,@ratio,@ng_qty,@style,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
m_id = int.Parse(dt.Rows[0]["ID"].ToString()),
|
eqp_code = eqpcode,
|
payrate= payrate,
|
report_person = reportuser,
|
report_date = date,
|
report_qty = reportqty,
|
reckway = reckway,
|
usergroup_code = usergroupcode,
|
ratio = 0, //分配比例
|
ng_qty = ngqty,
|
style = "B",
|
lm_user = us.usercode,
|
lm_date = date
|
}
|
});
|
if (groupedItems.Count > 0)
|
{
|
//写入缺陷记录表
|
for (int i = 0; i < groupedItems.Count; i++)
|
{
|
sql = @"insert into CSR_WorkRecord_Defect(record_id,wo_code,partnumber,step_seq,step_code,defect_qty,defect_pendqty,defect_code,remarks,style,lm_user,lm_date)
|
values(@record_id,@wo_code,@partcode,@stepseq,@stepcode,@ngqty,@defect_pendqty,@defect_code,@remarks,@style,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new { record_id = int.Parse(dt.Rows[0]["ID"].ToString()), wo_code = mesordercode, partcode = partcode, stepseq = stepseq, stepcode = stepcode, ngqty = groupedItems[i].badqty, defect_pendqty = groupedItems[i].badqty, defect_code = groupedItems[i].defect_code, remarks = remarks, style = "B", lm_user = us.usercode, lm_date = date } });
|
|
}
|
}
|
}
|
|
|
//回写工单工序表合格数量、不良数量
|
sql = @"update TK_Wrk_Step set good_qty=good_qty+@reportqty,ng_qty=ng_qty+@ngqty where wo_code=@mesordercode and step_code=@stepcode";
|
list.Add(new { str = sql, parm = new { mesordercode = mesordercode, stepcode = stepcode, reportqty = reportqty, ngqty = ngqty } });
|
|
//回写工单表合格数量、不良数量
|
//sql = @"update TK_Wrk_Man set good_qty=good_qty+@reportqty,ng_qty=ng_qty+@ngqty where wo_code=@mesordercode";
|
//list.Add(new { str = sql, parm = new { mesordercode = mesordercode, reportqty = reportqty, ngqty = ngqty } });
|
|
//写入ERP入库单
|
|
//判断是否末道工序完工报工(自动关闭工单及工序任务)
|
//list = AutosCloseOrder.AutosColseOrderReport(list,mesordercode, partcode, stepseq,stepcode,reportqty,ngqty);
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "报工", "报工了工单:" + mesordercode + "工序:" + 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
|
|
#region[生产开报工,发料提交]
|
public static ToMessage SavaMesOrderStepOut(string mesordercode, string partcode, string stepseq, string stepcode, string wxcode, string outuser, string taskqty, string fqty, User us)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
string date = DateTime.Now.ToString(); //获取系统时间
|
list.Clear();
|
//判断是否有发料记录(有(同工单+工序+外协供方修改) 无：新增)
|
sql = @"select * from TK_Wrk_OutRecord where wo_code=@wo_code and step_code=@step_code and wx_code=@wx_code and style='F'";
|
dynamicParams.Add("@wo_code", mesordercode);
|
dynamicParams.Add("@step_code", stepcode);
|
dynamicParams.Add("@wx_code", wxcode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count > 0)
|
{
|
//修改发料记录
|
sql = @"update TK_Wrk_OutRecord set fqty=fqty+@fqty,lm_user=@username,lm_date=@CreateDate
|
where wo_code=@mesordercode and step_code=@stepcode and wx_code=@wx_code and style='F'";
|
list.Add(new { str = sql, parm = new { mesordercode = mesordercode, stepcode = stepcode, wx_code = wxcode, fqty = decimal.Parse(fqty), username = us.usercode, CreateDate = date } });
|
//写入子表
|
sql = @"insert into TK_Wrk_OutRecordSub(m_id,wx_code,out_person,out_time,fqty,style,lm_user,lm_date)
|
values(@m_id,@wx_code,@out_person,@out_time,@fqty,@style,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new { m_id = int.Parse(data.Rows[0]["ID"].ToString()), wx_code = wxcode, out_person = outuser, out_time = date, fqty = fqty, style = 'F', lm_user = us.usercode, lm_date = date } });
|
}
|
else
|
{
|
//获取主表最大ID
|
sql = @"select ISNULL(IDENT_CURRENT('TK_Wrk_OutRecord')+1,1) as id";
|
var dt = DapperHelper.selecttable(sql);
|
//写入外协记录主表
|
sql = @"insert into TK_Wrk_OutRecord(wo_code,step_seq,step_code,wx_code,materiel_code,style,fqty,lm_user,lm_date)
|
values(@mesordercode,@stepseq,@stepcode,@wx_code,@partcode,@style,@fqty,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new { mesordercode = mesordercode, stepseq = stepseq, stepcode = stepcode, wx_code = wxcode, partcode = partcode, style = 'F', fqty = fqty, lm_user = us.usercode, lm_date = date } });
|
|
//写入子表
|
sql = @"insert into TK_Wrk_OutRecordSub(m_id,wx_code,out_person,out_time,fqty,style,lm_user,lm_date)
|
values(@m_id,@wx_code,@out_person,@out_time,@fqty,@style,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new { m_id = int.Parse(dt.Rows[0]["ID"].ToString()), wx_code = wxcode, out_person = outuser, out_time = date, fqty = fqty, style = 'F', lm_user = us.usercode, lm_date = date } });
|
}
|
//回写工单工序表状态为START: 开工
|
sql = @"update TK_Wrk_Step set status='START' where wo_code=@mesordercode and step_code=@stepcode";
|
list.Add(new { str = sql, parm = new { mesordercode = mesordercode, stepcode = stepcode } });
|
|
|
//回写工单表状态为： 开工：START
|
sql = @"update TK_Wrk_Man set status='START' where wo_code=@mesordercode";
|
list.Add(new { str = sql, parm = new { mesordercode = mesordercode } });
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "外协发料", "外协发料工单:" + mesordercode + "工序:" + 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
|
|
#region[生产开报工,收料提交]
|
public static ToMessage SavaMesOrderStepIn(string mesordercode, string partcode, string stepseq, string stepcode, string wxcode, string inbarcode, string inuser, string taskqty, string sqty, List<ReportDefectList> defectobjs, string remarks, User us)
|
{
|
var sql = "";
|
string[] arra1 = new string[] { };
|
decimal ngqty = 0;
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
string date = DateTime.Now.ToString(); //获取系统时间
|
//判断是否有不良数据
|
var groupedItems = defectobjs.GroupBy(item => item.defect_code) // 按编码code分组
|
.Select(group => new { defect_code = group.Key, badqty = group.Sum(item => Convert.ToDecimal(item.badqty)) }).ToList(); // 汇总数量qty
|
if (groupedItems.Count <= 0)
|
{
|
ngqty = 0;
|
}
|
else
|
{
|
//汇总不良数量
|
ngqty = groupedItems.Sum(item => Convert.ToDecimal(item.badqty));
|
}
|
|
list.Clear();
|
//判断是否有收料记录(有：（同工单+工序+外协供方修改） 无：新增)
|
sql = @"select * from TK_Wrk_OutRecord where wo_code=@wo_code and step_code=@step_code and wx_code=@wx_code and style='S'";
|
dynamicParams.Add("@wo_code", mesordercode);
|
dynamicParams.Add("@step_code", stepcode);
|
dynamicParams.Add("@wx_code", wxcode);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
//获取发料记录的默认选中的外协供应商与收料时的外协供应商做对比判断
|
sql = @"select A.wx_code,B.name,A.fqty from TK_Wrk_OutRecord A
|
inner join TCustomer B on A.wx_code=B.code
|
where A.wo_code=@wo_code and A.step_code=@step_code and wx_code=@wx_code and A.style='F' ";
|
dynamicParams.Add("@wo_code", mesordercode);
|
dynamicParams.Add("@step_code", stepcode);
|
dynamicParams.Add("@wx_code", wxcode);
|
var da = DapperHelper.selectdata(sql, dynamicParams);
|
if (da.Rows.Count <= 0)
|
{
|
sql = @"select A.wx_code,B.name,A.fqty from TK_Wrk_OutRecord A
|
inner join TCustomer B on A.wx_code=B.code
|
where A.wo_code=@wo_code and A.step_code=@step_code and A.style='F' ";
|
dynamicParams.Add("@wo_code", mesordercode);
|
dynamicParams.Add("@step_code", stepcode);
|
var da1 = DapperHelper.selectdata(sql, dynamicParams);
|
var dr = da1.AsEnumerable().ToList().Select(x => x.Field<string>("NAME")).ToList();
|
string wxstring = (string.Join(",", dr.Select(x => x.ToString()).ToArray()));
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "操作失败,当前收料外协供方与发料外协供应商不匹配,应为：【" + wxstring + "】!";
|
mes.data = null;
|
return mes;
|
}
|
if ((decimal.Parse(sqty) + ngqty) > decimal.Parse(da.Rows[0]["FQTY"].ToString())) //收料数量+不良数量>发料数量
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "操作失败,当前收料数量+不良数量,不能大于发料数量:" + da.Rows[0]["FQTY"].ToString() + "!";
|
mes.data = null;
|
return mes;
|
}
|
//if (da.Rows[0]["WX_CODE"].ToString() != wxcode)
|
//{
|
// mes.code = "300";
|
// mes.count = 0;
|
// mes.message = "操作失败,当前收料外协供方应为：" + da.Rows[0]["NAME"].ToString() + "!";
|
// mes.data = null;
|
// return mes;
|
//}
|
if (data.Rows.Count > 0)
|
{
|
decimal sum_sqty = data.AsEnumerable().Select(d => d.Field<decimal>("SQTY")).Sum(); //获取同单号,同工序,同外协供应商收料总数量
|
decimal sum_fqty = da.AsEnumerable().Select(d => d.Field<decimal>("FQTY")).Sum(); //获取同单号,同工序,同外协供应商发料总数量
|
if ((sum_sqty + decimal.Parse(sqty) + ngqty) > sum_fqty) //已收料总数+当前收料数量+不良数量>总发料数量
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "操作失败,当前收料数量+不良数量,不能大于待收数量:" + (sum_fqty - sum_sqty) + "!";
|
mes.data = null;
|
return mes;
|
}
|
|
|
//获取主表最大ID
|
sql = @"select ISNULL(IDENT_CURRENT('TK_Wrk_OutRecord')+1,1) as id";
|
var dt = DapperHelper.selecttable(sql);
|
//写入外协记录主表
|
sql = @"insert into TK_Wrk_OutRecord(wo_code,step_seq,step_code,wx_code,materiel_code,style,sqty,ng_qty,lm_user,lm_date,inbarcode)
|
values(@mesordercode,@stepseq,@stepcode,@wx_code,@partcode,@style,@sqty,@ngqty,@lm_user,@lm_date,@inbarcode)";
|
list.Add(new { str = sql, parm = new { mesordercode = mesordercode, stepseq = stepseq, stepcode = stepcode, wx_code = wxcode, partcode = partcode, style = 'S', sqty = sqty, ngqty = ngqty, lm_user = us.usercode, lm_date = date, inbarcode = inbarcode } });
|
|
//写入外协记录子表
|
sql = @"insert into TK_Wrk_OutRecordSub(m_id,wx_code,in_person,in_time,sqty,ng_qty,style,lm_user,lm_date)
|
values(@m_id,@wxcode,@in_person,@in_time,@sqty,@ng_qty,@style,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new { m_id = int.Parse(dt.Rows[0]["ID"].ToString()), wxcode = wxcode, in_person = inuser, in_time = date, sqty = sqty, ng_qty = ngqty, style = "S", lm_user = us.usercode, lm_date = date } });
|
|
if (groupedItems.Count > 0)
|
{
|
//写入缺陷记录表
|
for (int i = 0; i < groupedItems.Count; i++)
|
{
|
sql = @"insert into CSR_WorkRecord_Defect(record_id,wo_code,partnumber,step_seq,step_code,defect_qty,defect_pendqty,defect_code,remarks,style,lm_user,lm_date)
|
values(@record_id,@wo_code,@partcode,@stepseq,@stepcode,@ngqty,@defect_pendqty,@defect_code,@remarks,@style,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new { record_id = int.Parse(dt.Rows[0]["ID"].ToString()), wo_code = mesordercode, partcode = partcode, stepseq = stepseq, stepcode = stepcode, ngqty = groupedItems[i].badqty, defect_pendqty = groupedItems[i].badqty, defect_code = groupedItems[i].defect_code, remarks = remarks, style = "S", lm_user = us.usercode, lm_date = date } });
|
|
}
|
}
|
}
|
else
|
{
|
//获取主表最大ID
|
sql = @"select ISNULL(IDENT_CURRENT('TK_Wrk_OutRecord')+1,1) as id";
|
var dt = DapperHelper.selecttable(sql);
|
//写入外协记录主表
|
sql = @"insert into TK_Wrk_OutRecord(wo_code,step_seq,step_code,wx_code,materiel_code,style,sqty,ng_qty,lm_user,lm_date,inbarcode)
|
values(@mesordercode,@stepseq,@stepcode,@wx_code,@partcode,@style,@sqty,@ngqty,@lm_user,@lm_date,@inbarcode)";
|
list.Add(new { str = sql, parm = new { mesordercode = mesordercode, stepseq = stepseq, stepcode = stepcode, wx_code = wxcode, partcode = partcode, style = 'S', sqty = sqty, ngqty = ngqty, lm_user = us.usercode, lm_date = date, inbarcode = inbarcode } });
|
|
//写入外协记录子表
|
sql = @"insert into TK_Wrk_OutRecordSub(m_id,wx_code,in_person,in_time,sqty,ng_qty,style,lm_user,lm_date)
|
values(@m_id,@wxcode,@in_person,@in_time,@sqty,@ng_qty,@style,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new { m_id = int.Parse(dt.Rows[0]["ID"].ToString()), wxcode = wxcode, in_person = inuser, in_time = date, sqty = sqty, ng_qty = ngqty, style = "S", lm_user = us.usercode, lm_date = date } });
|
|
if (groupedItems.Count > 0)
|
{
|
//写入缺陷记录表
|
for (int i = 0; i < groupedItems.Count; i++)
|
{
|
sql = @"insert into CSR_WorkRecord_Defect(record_id,wo_code,partnumber,step_seq,step_code,defect_qty,defect_pendqty,defect_code,remarks,style,lm_user,lm_date)
|
values(@record_id,@wo_code,@partcode,@stepseq,@stepcode,@ngqty,@defect_pendqty,@defect_code,@remarks,@style,@lm_user,@lm_date)";
|
list.Add(new { str = sql, parm = new { record_id = int.Parse(dt.Rows[0]["ID"].ToString()), wo_code = mesordercode, partcode = partcode, stepseq = stepseq, stepcode = stepcode, ngqty = groupedItems[i].badqty, defect_pendqty = groupedItems[i].badqty, defect_code = groupedItems[i].defect_code, remarks = remarks, style = "S", lm_user = us.usercode, lm_date = date } });
|
|
}
|
}
|
}
|
//回写工单工序表合格数量、不良数量
|
sql = @"update TK_Wrk_Step set good_qty=good_qty+@sqty,ng_qty=ng_qty+@ngqty where wo_code=@mesordercode and step_code=@stepcode";
|
list.Add(new { str = sql, parm = new { mesordercode = mesordercode, stepcode = stepcode, sqty = sqty, ngqty = ngqty } });
|
|
//回写工单表合格数量、不良数量
|
//sql = @"update TK_Wrk_Man set good_qty=good_qty+@sqty,ng_qty=ng_qty+@ngqty where wo_code=@mesordercode";
|
//list.Add(new { str = sql, parm = new { mesordercode = mesordercode, sqty = sqty, ngqty = ngqty } });
|
|
////写入ERP入库单
|
|
//判断是否末道工序完工报工(自动关闭工单及工序任务)
|
//list = AutosCloseOrder.AutosColseOrderReport(list, mesordercode, partcode, stepseq, stepcode, sqty, ngqty);
|
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "外协收料", "外协收料工单:" + mesordercode + "工序:" + 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
|
|
#region[不良处理,提交]
|
public static ToMessage EditOrderNgStepSeave(ReportDefectHandle json, User us)
|
{
|
var sql = "";
|
string[] arra1 = new string[] { };
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
decimal sumrepair_qty = 0, sumlaborbad_qty = 0, summaterielbad_qty = 0; //累计维修数量、累计工废数量、累计料废数量
|
try
|
{
|
string date = DateTime.Now.ToString(); //获取系统时间
|
list.Clear();
|
|
|
//循环json数据
|
for (int i = 0; i < json.Data.Rows.Count; i++)
|
{
|
//自制工序
|
if (json.Data.Rows[i]["STYLE"].ToString() == "Z")
|
{
|
//回写对应的报工记录子表合格数量、不良数量、工废数量、料废数量
|
sql = @"update TK_Wrk_RecordSub set report_qty=report_qty+@repair_qty,ng_qty=ng_qty-@repair_qty-@laborbad_qty-@materielbad_qty,laborbad_qty=laborbad_qty+@laborbad_qty,materielbad_qty=materielbad_qty+@materielbad_qty
|
where m_id=@m_id and style='B'";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
m_id = int.Parse(json.Data.Rows[i]["M_ID"].ToString()),
|
repair_qty = decimal.Parse(json.Data.Rows[i]["REPAIR_QTY"].ToString()),
|
laborbad_qty = decimal.Parse(json.Data.Rows[i]["LABORBAD_QTY"].ToString()),
|
materielbad_qty = decimal.Parse(json.Data.Rows[i]["MATERIELBAD_QTY"].ToString())
|
}
|
});
|
//回写对应的报工记录主表合格数量、不良数量、报废数量
|
sql = @"update TK_Wrk_Record set good_qty=good_qty+@repair_qty,ng_qty=ng_qty-@repair_qty-@laborbad_qty-@materielbad_qty,laborbad_qty=laborbad_qty+@laborbad_qty,materielbad_qty=materielbad_qty+@materielbad_qty
|
where wo_code=@wo_code and step_code=@step_code and id=@id and style='B'";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
repair_qty = decimal.Parse(json.Data.Rows[i]["REPAIR_QTY"].ToString()),
|
laborbad_qty = decimal.Parse(json.Data.Rows[i]["LABORBAD_QTY"].ToString()),
|
materielbad_qty = decimal.Parse(json.Data.Rows[i]["MATERIELBAD_QTY"].ToString()),
|
wo_code = json.Data.Rows[i]["WO_CODE"].ToString(),
|
step_code = json.Data.Rows[i]["STEP_CODE"].ToString(),
|
id = int.Parse(json.Data.Rows[i]["M_ID"].ToString())
|
}
|
});
|
//回写缺陷记录表的待处理数量
|
sql = @"update CSR_WorkRecord_Defect set defect_qty=defect_qty-@repair_qty-@laborbad_qty-@materielbad_qty, defect_pendqty=defect_pendqty-@repair_qty-@laborbad_qty-@materielbad_qty,laborbad_qty=laborbad_qty+@laborbad_qty,materielbad_qty=materielbad_qty+@materielbad_qty
|
where wo_code=@wo_code and step_code=@step_code and id=@id and style='B'";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
repair_qty = decimal.Parse(json.Data.Rows[i]["REPAIR_QTY"].ToString()),
|
laborbad_qty = decimal.Parse(json.Data.Rows[i]["LABORBAD_QTY"].ToString()),
|
materielbad_qty = decimal.Parse(json.Data.Rows[i]["MATERIELBAD_QTY"].ToString()),
|
wo_code = json.Data.Rows[i]["WO_CODE"].ToString(),
|
step_code = json.Data.Rows[i]["STEP_CODE"].ToString(),
|
id = int.Parse(json.Data.Rows[i]["ID"].ToString())
|
}
|
});
|
//判断缺陷记录处理表是否存在记录
|
sql = @"select * from CSR_WorkRecord_DefectHandle where wo_code=@wo_code and step_code=@step_code and defect_id=@defect_id";
|
dynamicParams.Add("@wo_code", json.Data.Rows[i]["WO_CODE"].ToString());
|
dynamicParams.Add("@step_code", json.Data.Rows[i]["STEP_CODE"].ToString());
|
dynamicParams.Add("@defect_id", json.Data.Rows[i]["ID"].ToString());
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count <= 0)
|
{
|
//写入报工缺陷处理记录表
|
sql = @"insert into CSR_WorkRecord_DefectHandle(defect_id,wo_code,partnumber,step_seq,step_code,repair_qty,laborbad_qty,materielbad_qty,defect_code,style,lm_user,lm_date)
|
values(@defect_id,@wo_code,@partcode,@stepseq,@stepcode,@repair_qty,@laborbad_qty,@materielbad_qty,@defect_code,@style,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
defect_id = int.Parse(json.Data.Rows[i]["ID"].ToString()),
|
wo_code = json.Data.Rows[i]["WO_CODE"].ToString(),
|
partcode = json.Data.Rows[i]["MATERIEL_CODE"].ToString(),
|
stepseq = json.Data.Rows[i]["SEQ"].ToString(),
|
stepcode = json.Data.Rows[i]["STEP_CODE"].ToString(),
|
repair_qty = decimal.Parse(json.Data.Rows[i]["REPAIR_QTY"].ToString()),
|
laborbad_qty = decimal.Parse(json.Data.Rows[i]["LABORBAD_QTY"].ToString()),
|
materielbad_qty = decimal.Parse(json.Data.Rows[i]["MATERIELBAD_QTY"].ToString()),
|
defect_code = json.Data.Rows[i]["DEFECT_CODE"].ToString(),
|
style = "B",
|
lm_user = us.usercode,
|
lm_date = date
|
}
|
});
|
}
|
else
|
{
|
//更新报工缺陷处理记录表
|
sql = @"update CSR_WorkRecord_DefectHandle set repair_qty=repair_qty+@repair_qty,laborbad_qty=laborbad_qty+@laborbad_qty,materielbad_qty=materielbad_qty+@materielbad_qty
|
where wo_code=@wo_code and step_code=@step_code and defect_id=@defect_id";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
defect_id = int.Parse(json.Data.Rows[i]["ID"].ToString()),
|
wo_code = json.Data.Rows[i]["WO_CODE"].ToString(),
|
step_code = json.Data.Rows[i]["STEP_CODE"].ToString(),
|
repair_qty = decimal.Parse(json.Data.Rows[i]["REPAIR_QTY"].ToString()),
|
laborbad_qty = decimal.Parse(json.Data.Rows[i]["LABORBAD_QTY"].ToString()),
|
materielbad_qty = decimal.Parse(json.Data.Rows[i]["MATERIELBAD_QTY"].ToString())
|
}
|
});
|
}
|
|
sumrepair_qty = sumrepair_qty + decimal.Parse(json.Data.Rows[i]["REPAIR_QTY"].ToString());
|
sumlaborbad_qty = sumlaborbad_qty + decimal.Parse(json.Data.Rows[i]["LABORBAD_QTY"].ToString());
|
summaterielbad_qty = summaterielbad_qty + decimal.Parse(json.Data.Rows[i]["MATERIELBAD_QTY"].ToString());
|
}
|
//外协工序
|
if (json.Data.Rows[i]["STYLE"].ToString() == "S")
|
{
|
//回写对应的收料记录子表收料数量、不良数量、报废数量
|
sql = @"update TK_Wrk_OutRecordSub set sqty=sqty+@repair_qty,ng_qty=ng_qty-@repair_qty-@laborbad_qty-@materielbad_qty,laborbad_qty=laborbad_qty+@laborbad_qty,materielbad_qty=materielbad_qty+@materielbad_qty
|
where m_id=@m_id and style='S'";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
m_id = int.Parse(json.Data.Rows[i]["M_ID"].ToString()),
|
repair_qty = decimal.Parse(json.Data.Rows[i]["REPAIR_QTY"].ToString()),
|
laborbad_qty = decimal.Parse(json.Data.Rows[i]["LABORBAD_QTY"].ToString()),
|
materielbad_qty = decimal.Parse(json.Data.Rows[i]["MATERIELBAD_QTY"].ToString())
|
}
|
});
|
//回写对应的收料记录主表合格数量、不良数量、报废数量
|
sql = @"update TK_Wrk_OutRecord set sqty=sqty+@repair_qty,ng_qty=ng_qty-@repair_qty-@laborbad_qty-@materielbad_qty,laborbad_qty=laborbad_qty+@laborbad_qty,materielbad_qty=materielbad_qty+@materielbad_qty
|
where wo_code=@wo_code and step_code=@step_code and id=@id and style='S'";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
repair_qty = decimal.Parse(json.Data.Rows[i]["REPAIR_QTY"].ToString()),
|
laborbad_qty = decimal.Parse(json.Data.Rows[i]["LABORBAD_QTY"].ToString()),
|
materielbad_qty = decimal.Parse(json.Data.Rows[i]["MATERIELBAD_QTY"].ToString()),
|
wo_code = json.Data.Rows[i]["WO_CODE"].ToString(),
|
step_code = json.Data.Rows[i]["STEP_CODE"].ToString(),
|
id = int.Parse(json.Data.Rows[i]["M_ID"].ToString())
|
}
|
});
|
//回写缺陷记录表的待处理数量
|
sql = @"update CSR_WorkRecord_Defect set defect_qty=defect_qty-@repair_qty-@laborbad_qty-@materielbad_qty,defect_pendqty=defect_pendqty-@repair_qty-@laborbad_qty-@materielbad_qty,laborbad_qty=laborbad_qty+@laborbad_qty,materielbad_qty=materielbad_qty+@materielbad_qty
|
where wo_code=@wo_code and step_code=@step_code and id=@id and style='S'";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
repair_qty = decimal.Parse(json.Data.Rows[i]["REPAIR_QTY"].ToString()),
|
laborbad_qty = decimal.Parse(json.Data.Rows[i]["LABORBAD_QTY"].ToString()),
|
materielbad_qty = decimal.Parse(json.Data.Rows[i]["MATERIELBAD_QTY"].ToString()),
|
wo_code = json.Data.Rows[i]["WO_CODE"].ToString(),
|
step_code = json.Data.Rows[i]["STEP_CODE"].ToString(),
|
id = int.Parse(json.Data.Rows[i]["ID"].ToString())
|
}
|
});
|
//判断缺陷记录处理表是否存在记录
|
sql = @"select * from CSR_WorkRecord_DefectHandle where wo_code=@wo_code and step_code=@step_code and defect_id=@defect_id";
|
dynamicParams.Add("@wo_code", json.Data.Rows[i]["WO_CODE"].ToString());
|
dynamicParams.Add("@step_code", json.Data.Rows[i]["STEP_CODE"].ToString());
|
dynamicParams.Add("@defect_id", json.Data.Rows[i]["ID"].ToString());
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
if (data.Rows.Count <= 0)
|
{
|
//写入报工缺陷处理记录表
|
sql = @"insert into CSR_WorkRecord_DefectHandle(defect_id,wo_code,partnumber,step_seq,step_code,repair_qty,laborbad_qty,materielbad_qty,defect_code,style,lm_user,lm_date)
|
values(@defect_id,@wo_code,@partcode,@stepseq,@stepcode,@repair_qty,@laborbad_qty,@materielbad_qty,@defect_code,@style,@lm_user,@lm_date)";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
defect_id = int.Parse(json.Data.Rows[i]["ID"].ToString()),
|
wo_code = json.Data.Rows[i]["WO_CODE"].ToString(),
|
partcode = json.Data.Rows[i]["MATERIEL_CODE"].ToString(),
|
stepseq = json.Data.Rows[i]["SEQ"].ToString(),
|
stepcode = json.Data.Rows[i]["STEP_CODE"].ToString(),
|
repair_qty = decimal.Parse(json.Data.Rows[i]["REPAIR_QTY"].ToString()),
|
laborbad_qty = decimal.Parse(json.Data.Rows[i]["LABORBAD_QTY"].ToString()),
|
materielbad_qty = decimal.Parse(json.Data.Rows[i]["MATERIELBAD_QTY"].ToString()),
|
defect_code = json.Data.Rows[i]["DEFECT_CODE"].ToString(),
|
style = "S",
|
lm_user = us.usercode,
|
lm_date = date
|
}
|
});
|
}
|
else
|
{
|
//更新报工缺陷处理记录表
|
sql = @"update CSR_WorkRecord_DefectHandle set repair_qty=repair_qty+@repair_qty,laborbad_qty=laborbad_qty+@laborbad_qty,materielbad_qty=materielbad_qty+@materielbad_qty
|
where wo_code=@wo_code and step_code=@step_code and defect_id=@defect_id";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
defect_id = int.Parse(json.Data.Rows[i]["ID"].ToString()),
|
wo_code = json.Data.Rows[i]["WO_CODE"].ToString(),
|
step_code = json.Data.Rows[i]["STEP_CODE"].ToString(),
|
repair_qty = decimal.Parse(json.Data.Rows[i]["REPAIR_QTY"].ToString()),
|
laborbad_qty = decimal.Parse(json.Data.Rows[i]["LABORBAD_QTY"].ToString()),
|
materielbad_qty = decimal.Parse(json.Data.Rows[i]["MATERIELBAD_QTY"].ToString())
|
}
|
});
|
}
|
sumrepair_qty = sumrepair_qty + decimal.Parse(json.Data.Rows[i]["REPAIR_QTY"].ToString());
|
sumlaborbad_qty = sumlaborbad_qty + decimal.Parse(json.Data.Rows[i]["LABORBAD_QTY"].ToString());
|
summaterielbad_qty = summaterielbad_qty + decimal.Parse(json.Data.Rows[i]["MATERIELBAD_QTY"].ToString());
|
}
|
}
|
|
//回写工单工序表合格数量、不良数量
|
sql = @"update TK_Wrk_Step set good_qty=good_qty+@sumrepair_qty,ng_qty=ng_qty-@sumrepair_qty-@sumlaborbad_qty-@summaterielbad_qty,laborbad_qty=laborbad_qty+@sumlaborbad_qty,materielbad_qty=materielbad_qty+@summaterielbad_qty
|
where wo_code=@wo_code and step_code=@stepcode";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
wo_code = json.Data.Rows[0]["WO_CODE"].ToString(),
|
stepcode = json.Data.Rows[0]["STEP_CODE"].ToString(),
|
sumrepair_qty = sumrepair_qty,
|
sumlaborbad_qty = sumlaborbad_qty,
|
summaterielbad_qty = summaterielbad_qty
|
}
|
});
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "不良处理", "工单:" + json.Data.Rows[0]["WO_CODE"].ToString() + "工序:" + json.Data.Rows[0]["STEP_CODE"].ToString(), 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 MesOrderStepVerifySearch(string wkshopcode, string wo_code, string partnumber, string partname, string partspec,string stepcode, string reportuser, string reportdateopendate, string reportdateclosedate, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and AA.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
if (wo_code != "" && wo_code != null)
|
{
|
search += "and AA.wo_code like '%'+@wo_code+'%' ";
|
dynamicParams.Add("@wo_code", wo_code);
|
}
|
if (partnumber != "" && partnumber != null)
|
{
|
search += "and AA.partnumber like '%'+@partnumber+'%' ";
|
dynamicParams.Add("@partnumber", partnumber);
|
}
|
if (partname != "" && partname != null)
|
{
|
search += "and AA.partname like '%'+@partname+'%' ";
|
dynamicParams.Add("@partname", partname);
|
}
|
if (partspec != "" && partspec != null)
|
{
|
search += "and AA.partspec like '%'+@partspec+'%' ";
|
dynamicParams.Add("@partspec", partspec);
|
}
|
if (stepcode != "" && stepcode != null)
|
{
|
search += "and AA.step_code=@stepcode ";
|
dynamicParams.Add("@stepcode", stepcode);
|
}
|
if (reportuser != "" && reportuser != null)
|
{
|
search += "and AA.usercode like '%'+@reportuser+'%' ";
|
dynamicParams.Add("@reportuser", reportuser);
|
}
|
if (reportdateopendate != "" && reportdateopendate != null)
|
{
|
search += "and AA.report_date between @reportdateopendate and @reportdateclosedate ";
|
dynamicParams.Add("@reportdateopendate", reportdateopendate + " 00:00:00");
|
dynamicParams.Add("@reportdateclosedate", reportdateclosedate + " 23:59:59");
|
}
|
|
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定自制报工外协收料数据--------------
|
var total = 0; //总条数
|
var sql = @"select * from(
|
select A.id,B.id as sbid,A.wo_code,A.materiel_code as partnumber,P.partname,P.partspec,K.plan_quantity,K.plan_qty as task_qty,M.wkshp_code,T.torg_name as wkshp_name,A.eqp_code,E.name as eqp_name,
|
A.step_seq,A.step_code,S.stepname,S.flwtype as steptype,A.style,k.isbott as first_choke,k.isend as last_choke,A.step_price,B.reckway,B.usergroup_code,G.usergroupname as usergroup_name,
|
B.report_person as usercode,
|
STUFF((SELECT ',' + U.username
|
FROM TUser U
|
WHERE CHARINDEX(',' + U.usercode + ',', ',' + B.report_person + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS username,
|
B.report_date,B.report_qty,B.ng_qty,B.laborbad_qty,B.materielbad_qty,'' as wx_code,'' as wx_name
|
from TK_Wrk_Record A
|
inner join TK_Wrk_RecordSub B on A.id=B.m_id
|
left join TK_Wrk_Man M on A.wo_code=M.wo_code
|
left join TK_Wrk_Step K on M.wo_code=K.wo_code and A.step_code=K.step_code
|
left join TStep S on A.step_code=S.stepcode
|
left join TMateriel_Info P on A.materiel_code=P.partcode
|
left join TOrganization T on M.wkshp_code=T.torg_code
|
left join TEqpInfo E on A.eqp_code=E.code
|
left join TGroup G on G.usergroupcode=B.usergroup_code
|
where A.style='B' and B.style='B' and M.status<>'CLOSED' and A.verify='N'
|
union all
|
select A.id,B.id as sbid,A.wo_code,A.materiel_code as partnumber,P.partname,P.partspec,K.plan_quantity,K.plan_qty as task_qty,M.wkshp_code,T.torg_name as wkshp_name,A.wx_code as eqp_code,E.name as eqp_name,
|
A.step_seq,A.step_code,S.stepname,S.flwtype as steptype,A.style,k.isbott as first_choke,k.isend as last_choke,A.step_price,'person' as reckway,'' as usergroup_code,'' as usergroup_name,
|
B.in_person as usercode,
|
STUFF((SELECT ',' + U.username
|
FROM TUser U
|
WHERE CHARINDEX(',' + U.usercode + ',', ',' + B.in_person + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS username,
|
B.in_time as report_date,B.sqty as report_qty,B.ng_qty,B.laborbad_qty,B.materielbad_qty,A.wx_code,C.name as wx_name
|
from TK_Wrk_OutRecord A
|
inner join TK_Wrk_OutRecordSub B on A.id = B.m_id
|
left join TK_Wrk_Man M on A.wo_code = M.wo_code
|
left join TK_Wrk_Step K on M.wo_code=K.wo_code and A.step_code=K.step_code
|
left join TStep S on A.step_code = S.stepcode
|
left join TMateriel_Info P on A.materiel_code = P.partcode
|
left join TOrganization T on M.wkshp_code = T.torg_code
|
left join TCustomer E on A.wx_code = E.code
|
left join TCustomer C on A.wx_code=C.code
|
where A.style = 'S' and B.style = 'S' and M.status<>'CLOSED' and A.verify='N'
|
) as AA where" + 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 MesOrderStepModelSearch(string wo_code, string step_code, string step_type, string isbott, string isend, string id, string sbid)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (step_type == "Z")
|
{
|
sql = @"select D.id,D.record_id,D.defect_code,
|
STUFF((SELECT ',' + F.name
|
FROM TDefect F
|
WHERE CHARINDEX(',' + F.code + ',', ',' + D.defect_code + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS defect_name,
|
D.defect_qty,laborbad_qty,materielbad_qty
|
from CSR_WorkRecord_Defect D
|
where wo_code=@wo_code and step_code=@step_code and record_id=@id";
|
dynamicParams.Add("@wo_code", wo_code);
|
dynamicParams.Add("@step_code", step_code);
|
dynamicParams.Add("@id", id);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
mes.code = "200";
|
mes.count = 0;
|
mes.message = "查询成功!";
|
mes.data = data;
|
|
}
|
if (step_type == "W")
|
{
|
sql = @"select D.id,D.record_id,D.defect_code,
|
STUFF((SELECT ',' + F.name
|
FROM TDefect F
|
WHERE CHARINDEX(',' + F.code + ',', ',' + D.defect_code + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS defect_name,
|
D.defect_qty,laborbad_qty,materielbad_qty
|
from CSR_WorkRecord_Defect D
|
where wo_code=@wo_code and step_code=@step_code and record_id=@id";
|
dynamicParams.Add("@wo_code", wo_code);
|
dynamicParams.Add("@step_code", step_code);
|
dynamicParams.Add("@id", id);
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
mes.code = "200";
|
mes.count = 0;
|
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 MesOrderStepUpdateSeave(User us, List<UpdateProductReport> json)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
dynamic dynObj = JObject.Parse(us.mesSetting);
|
bool isOrder = dynObj.isOrder;
|
//获取此次报工调整提交的数据(合格数量、不良数量、工废数量、料废数量)
|
decimal this_reportqty = decimal.Parse(json[0].report_qty.ToString()); //报工数量(合格)
|
decimal this_ngqty = json[0].children.Sum(item => decimal.Parse(item.ng_qty));//不良数量汇总
|
decimal this_laborbadqty = json[0].children.Sum(item => decimal.Parse(item.laborbad_qty));//工废数量汇总
|
decimal this_materielbadqty = json[0].children.Sum(item => decimal.Parse(item.materielbad_qty));//料废数量汇总
|
decimal this_ng_dvalue = json[0].children.Sum(item => decimal.Parse(item.ng_dvalue));//不良数量差值汇总
|
decimal this_laborbad_dvalue = json[0].children.Sum(item => decimal.Parse(item.laborbad_dvalue));//工废数量差值汇总
|
decimal this_materielbad_dvalue = json[0].children.Sum(item => decimal.Parse(item.materielbad_dvalue));//料废数量差值汇总
|
string date = DateTime.Now.ToString(); //获取系统时间
|
if (isOrder) //按序
|
{
|
//控制逻辑：首道工序调整-> 本道工序当前调整总数(合格+不良+报废)+本道工序非当前报工总数(合格+不良+报废)>任务数量 ==不能大于任务数量
|
//控制逻辑：首道工序调整-> (本道工序当前调整合格数+本道工序非当前报工合格总数)<下道工序报工总数(合格+不良+报废) ==不能小于下道报工总数
|
//控制逻辑：末道工序调整-> 本道工序当前调整总数(合格+不良+报废)+本道工序非当前报工总数(合格+不良+报废)>上道工序报工合格总数 ==不能大于上道工序报工合格总数
|
//控制逻辑：中间工序调整-> 本道工序当前调整总数(合格+不良+报废)+本道工序非当前报工总数(合格+不良+报废)>上道工序报工合格总数 ==不能大于上道工序报工合格总数
|
//控制逻辑：中间工序调整-> (本道工序当前调整合格数+本道工序非当前报工合格总数)<下道工序报工总数(合格+不良+报废) ==不能小于下道报工总数
|
|
|
list.Clear();
|
//获取当前工序上道工序及属性
|
sql = @"select T.stepcode,T.stepname,T.flwtype from TK_Wrk_Step A
|
left join TStep T on A.step_code=T.stepcode
|
where A.wo_code=@ordercode and A.seq=@seq-1 ";
|
dynamicParams.Add("@ordercode", json[0].wo_code);
|
dynamicParams.Add("@seq", json[0].step_seq);
|
var pre = DapperHelper.selectdata(sql, dynamicParams);
|
//获取当前工序下道工序及属性
|
sql = @"select T.stepcode,T.stepname,T.flwtype from TK_Wrk_Step A
|
left join TStep T on A.step_code=T.stepcode
|
where A.wo_code=@ordercode and A.seq=@seq+1 ";
|
dynamicParams.Add("@ordercode", json[0].wo_code);
|
dynamicParams.Add("@seq", json[0].step_seq);
|
var next = DapperHelper.selectdata(sql, dynamicParams);
|
//判断当前工序是自制工序还是外协工序
|
if (json[0].flw_type.ToString() == "Z")//自制工序
|
{
|
//首道工序的报工
|
if (json[0].first_choke == "Y")
|
{
|
//查询当前首道报工工序非此次报工：总报工数量、总不良数量、总工废数量、总料废数量
|
sql = @"select isnull(sum(good_qty),0) as good_qty,isnull(sum(ng_qty),0) as ng_qty,isnull(sum(laborbad_qty),0) as laborbad_qty,isnull(sum(materielbad_qty),0) as materielbad_qty
|
from TK_Wrk_Record where wo_code=@wo_code and style='B' and id<>@id and step_code=@step_code";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@id", json[0].id);
|
dynamicParams.Add("@step_code", json[0].step_code);
|
var dt = DapperHelper.selectdata(sql, dynamicParams);
|
decimal notthis_reportqty = decimal.Parse(dt.Rows[0]["good_qty"].ToString()); //当前工序非本次报工总数
|
decimal notthis_ngqty = decimal.Parse(dt.Rows[0]["ng_qty"].ToString()); //当前工序非本次报工总不良数
|
decimal notthis_laborbadqty = decimal.Parse(dt.Rows[0]["laborbad_qty"].ToString()); //当前工序非本次报工总工废数
|
decimal notthis_materielbadqty = decimal.Parse(dt.Rows[0]["materielbad_qty"].ToString()); //当前工序非本次报工总料废数
|
//判断：当前工序报工记录：本次报工数量+本次不良数量+本次工废数量+本次料废数量+当前工序非本次报工总数+当前工序非本次不良总数+当前工序非本次工废总数+当前工序非本次料废总数>工单任务数量
|
decimal updatereportsumqty = this_reportqty + this_ngqty + this_laborbadqty + this_materielbadqty + notthis_reportqty + notthis_ngqty + notthis_laborbadqty + notthis_materielbadqty;
|
if (updatereportsumqty > decimal.Parse(json[0].task_qty.ToString()))
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前首道工序修改报工总数量：【" + updatereportsumqty + "】不能大于任务超产总数量：【" + json[0].task_qty.ToString() + "】!";
|
mes.data = null;
|
return mes;
|
}
|
//判断是否存在下道工序及属性
|
if (next.Rows.Count > 0)
|
{
|
if (next.Rows[0]["flwtype"].ToString() == "Z")
|
{
|
//查询当前工序下道工序：总报工数量、总不良数量、总报废数量
|
sql = @"select isnull(sum(good_qty),0) as good_qty,isnull(sum(ng_qty),0) as ng_qty,isnull(sum(laborbad_qty),0) as laborbad_qty,isnull(sum(materielbad_qty),0) as materielbad_qty
|
from TK_Wrk_Record where wo_code=@wo_code and style='B' and step_seq=@step_seq+1";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@step_seq", json[0].step_seq);
|
var dt0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt0.Rows.Count > 0)
|
{
|
//判断当前工序：报工总数数量+不良总数数量+工废总数量+料废总数量<下道工序报工总数量+下道工序不良总数量+下道工序工废总数量+下道工序料废总数量
|
decimal last_reportqty = decimal.Parse(dt0.Rows[0]["good_qty"].ToString()); //下道工序报工总数量
|
decimal last_ngqty = decimal.Parse(dt0.Rows[0]["ng_qty"].ToString()); //下道工序不良总数量
|
decimal last_laborbad_qty = decimal.Parse(dt0.Rows[0]["laborbad_qty"].ToString()); //下道工序工废总数量
|
decimal last_materielbad_qty = decimal.Parse(dt0.Rows[0]["materielbad_qty"].ToString()); //下道工序料废总数量
|
decimal last_updatereportsumqty = last_reportqty + last_ngqty + last_laborbad_qty + last_materielbad_qty;
|
//判断（当前非本次报工总合格数+本次报工调整合格数）<下道工序报工总数
|
if ((notthis_reportqty + this_reportqty) < last_updatereportsumqty)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前首道工序修改报工总合格数量：【" + (notthis_reportqty + this_reportqty) + "】不能小于下道自制工序报工总数量：【" + last_updatereportsumqty + "】!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
}
|
else
|
{
|
//查询当前工序下道工序：总发料数量
|
sql = @"select isnull(sum(fqty),0) as good_qty
|
from TK_Wrk_OutRecord where wo_code=@wo_code and style='S' and step_seq=@step_seq+1";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@step_seq", json[0].step_seq);
|
var dt0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt0.Rows.Count > 0)
|
{
|
//判断当前工序：发料总数数量
|
decimal last_reportqty = decimal.Parse(dt0.Rows[0]["good_qty"].ToString()); //下道工序发料总数量
|
//判断（当前非本次报工总合格数+本次报工调整合格数）<下道工序发料总数
|
if ((notthis_reportqty + this_reportqty) < last_reportqty)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前首道工序修改报工总合格数量：【" + (notthis_reportqty + this_reportqty) + "】不能小于下道外协工序发料总数量：【" + last_reportqty + "】!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
}
|
}
|
|
}
|
//末道工序的报工
|
else if (json[0].last_choke == "Y")
|
{
|
//查询当前末道报工工序非此次报工：总报工数量、总不良数量、总工废数量、总料废数量
|
sql = @"select isnull(sum(good_qty),0) as good_qty,isnull(sum(ng_qty),0) as ng_qty,isnull(sum(laborbad_qty),0) as laborbad_qty,isnull(sum(materielbad_qty),0) as materielbad_qty
|
from TK_Wrk_Record where wo_code=@wo_code and style='B' and id<>@id and step_code=@step_code";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@id", json[0].id);
|
dynamicParams.Add("@step_code", json[0].step_code);
|
var dt = DapperHelper.selectdata(sql, dynamicParams);
|
decimal notthis_reportqty = decimal.Parse(dt.Rows[0]["good_qty"].ToString()); //当前末道工序非本次报工总数
|
decimal notthis_ngqty = decimal.Parse(dt.Rows[0]["ng_qty"].ToString()); //当前末道工序非本次报工总数
|
decimal notthis_laborbad_qty = decimal.Parse(dt.Rows[0]["laborbad_qty"].ToString()); //当前末道工序非本次报工工废总数
|
decimal notthis_materielbad_qty = decimal.Parse(dt.Rows[0]["materielbad_qty"].ToString()); //当前末道工序非本次报工料废总数
|
//获取当前末道工序报工总数量：本次修改报工数量+本次修改不良数量+本次修改工废数量+本次修改报工料废数量+当前末道工序非本次报工总数+当前末道工序非本次不良总数+当前末道工序非本次工废总数+当前末道工序非本次料废总数
|
decimal updatereportsumqty = this_reportqty + this_ngqty + this_laborbadqty + this_materielbadqty + notthis_reportqty + notthis_ngqty + notthis_laborbad_qty + notthis_materielbad_qty;
|
|
//判断是否存在上道工序及属性
|
if (pre.Rows.Count > 0)
|
{
|
if (pre.Rows[0]["flwtype"].ToString() == "Z")
|
{
|
//查询当前末道工序上道工序：总报工数量、总不良数量、总报废数量
|
sql = @"select isnull(sum(good_qty),0) as good_qty,isnull(sum(ng_qty),0) as ng_qty,isnull(sum(laborbad_qty),0) as laborbad_qty,isnull(sum(materielbad_qty),0) as materielbad_qty
|
from TK_Wrk_Record where wo_code=@wo_code and style='B' and step_seq=@step_seq-1";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@step_seq", json[0].step_seq);
|
var dt0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt0.Rows.Count > 0)
|
{
|
decimal last_reportqty = decimal.Parse(dt0.Rows[0]["good_qty"].ToString()); //上道工序报工总数量
|
decimal last_ngqty = decimal.Parse(dt0.Rows[0]["ng_qty"].ToString()); //上道工序不良总数量
|
decimal last_laborbad_qty = decimal.Parse(dt0.Rows[0]["laborbad_qty"].ToString()); //上道工序工废总数量
|
decimal last_materielbad_qty = decimal.Parse(dt0.Rows[0]["materielbad_qty"].ToString()); //上道工序料废总数量
|
decimal last_updatereportsumqty = last_reportqty + last_ngqty + last_laborbad_qty + last_materielbad_qty;
|
//判断：当前末道工序报工记录：当前末道工序报工总数量>上道工序报工合格总数
|
if (updatereportsumqty > last_reportqty)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前末道工序修改报工总数量：【" + updatereportsumqty + "】不能大于上道工序报工总合格数量：【" + last_reportqty + "】!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
}
|
else
|
{
|
//查询当前工序上道工序：总收料数量
|
sql = @"select isnull(sum(sqty),0) as good_qty
|
from TK_Wrk_OutRecord where wo_code=@wo_code and style='S' and step_seq=@step_seq-1";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@step_seq", json[0].step_seq);
|
var dt0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt0.Rows.Count > 0)
|
{
|
//判断当前工序：收料总数数量
|
decimal last_reportqty = decimal.Parse(dt0.Rows[0]["good_qty"].ToString()); //下道工序发料总数量
|
//判断：当前末道工序报工记录：当前末道工序报工总数量>上道工序收料合格总数
|
if (updatereportsumqty > last_reportqty)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前末道工序修改报工总数量：【" + updatereportsumqty + "】不能大于上道工序收料总合格数量：【" + last_reportqty + "】!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
}
|
}
|
}
|
else //中间工序的报工
|
{
|
//查询当前中间报工工序非此次报工：总报工数量、总不良数量、总报废数量
|
sql = @"select isnull(sum(good_qty),0) as good_qty,isnull(sum(ng_qty),0) as ng_qty,isnull(sum(laborbad_qty),0) as laborbad_qty,isnull(sum(materielbad_qty),0) as materielbad_qty
|
from TK_Wrk_Record where wo_code=@wo_code and style='B' and id<>@id and step_code=@step_code";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@id", json[0].id);
|
dynamicParams.Add("@step_code", json[0].step_code);
|
var dt = DapperHelper.selectdata(sql, dynamicParams);
|
decimal notthis_reportqty = decimal.Parse(dt.Rows[0]["good_qty"].ToString()); //当前工序非本次报工总数
|
decimal notthis_ngqty = decimal.Parse(dt.Rows[0]["ng_qty"].ToString()); //当前工序非本次报工总数
|
decimal notthis_laborbad_qty = decimal.Parse(dt.Rows[0]["laborbad_qty"].ToString()); //当前工序非本次报工工费总数
|
decimal notthis_materielbad_qty = decimal.Parse(dt.Rows[0]["materielbad_qty"].ToString()); //当前工序非本次报工料废总数
|
//获取当前中间工序报工总数量：本次修改报工数量+本次修改不良数量+本次修改工废数量+本次修改料废总数+当前工序非本次报工总数+当前工序非本次不良总数+当前工序非本次工废总数+当前工序非本次料废总数
|
decimal updatereportsumqty = this_reportqty + this_ngqty + this_laborbadqty + this_materielbadqty + notthis_reportqty + notthis_ngqty + notthis_laborbad_qty + notthis_materielbad_qty;
|
|
//判断是否存在上道工序及属性
|
if (pre.Rows.Count > 0)
|
{
|
if (pre.Rows[0]["flwtype"].ToString() == "Z")
|
{
|
//查询当前工序上道工序：总报工数量、总不良数量、总工废数量、总料废数量
|
sql = @"select isnull(sum(good_qty),0) as good_qty,isnull(sum(ng_qty),0) as ng_qty,isnull(sum(laborbad_qty),0) as laborbad_qty,isnull(sum(materielbad_qty),0) as materielbad_qty
|
from TK_Wrk_Record where wo_code=@wo_code and style='B' and step_seq=@step_seq-1";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@step_seq", json[0].step_seq);
|
var dt0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt0.Rows.Count > 0)
|
{
|
decimal last_reportqty = decimal.Parse(dt0.Rows[0]["good_qty"].ToString()); //上道工序报工总合格数量
|
decimal last_ngqty = decimal.Parse(dt0.Rows[0]["ng_qty"].ToString()); //上道工序不良总数量
|
decimal last_laborbad_qty = decimal.Parse(dt0.Rows[0]["laborbad_qty"].ToString()); //上道工序工废总数量
|
decimal last_materielbad_qty = decimal.Parse(dt0.Rows[0]["materielbad_qty"].ToString());//上道工序料废总数量
|
decimal last_updatereportsumqty = last_reportqty + last_ngqty + last_laborbad_qty + last_materielbad_qty;
|
//判断：当前工序报工记录：当前工序报工总数>上道工序报工总合格数
|
if (updatereportsumqty > last_reportqty)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前工序修改报工总数量：【" + updatereportsumqty + "】不能大于上道工序报工总合格数量：【" + last_reportqty + "】!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
}
|
else
|
{
|
//查询当前工序上道工序：总收料数量
|
sql = @"select isnull(sum(sqty),0) as good_qty
|
from TK_Wrk_OutRecord where wo_code=@wo_code and style='S' and step_seq=@step_seq-1";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@step_seq", json[0].step_seq);
|
var dt0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt0.Rows.Count > 0)
|
{
|
//判断当前工序：收料总数数量
|
decimal last_reportqty = decimal.Parse(dt0.Rows[0]["good_qty"].ToString()); //上道工序收料总合格数量
|
//判断：当前末道工序报工记录：当前工序报工总数量>上道工序收料总合格数量
|
if (updatereportsumqty > last_reportqty)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前工序修改报工总数量：【" + updatereportsumqty + "】不能大于上道工序收料总合格数量：【" + last_reportqty + "】!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
}
|
}
|
//判断是否存在下道工序属性
|
if (next.Rows.Count > 0)
|
{
|
if (next.Rows[0]["flwtype"].ToString() == "Z")
|
{
|
//查询当前工序下道工序：总报工数量、总不良数量、总报废数量
|
sql = @"select isnull(sum(good_qty),0) as good_qty,isnull(sum(ng_qty),0) as ng_qty,isnull(sum(laborbad_qty),0) as laborbad_qty,isnull(sum(materielbad_qty),0) as materielbad_qty
|
from TK_Wrk_Record where wo_code=@wo_code and style='B' and step_seq=@step_seq+1";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@step_seq", json[0].step_seq);
|
var dt1 = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt1.Rows.Count > 0)
|
{
|
decimal last_reportqty = decimal.Parse(dt1.Rows[0]["good_qty"].ToString()); //下道工序报工总数量
|
decimal last_ngqty = decimal.Parse(dt1.Rows[0]["ng_qty"].ToString()); //下道工序不良总数量
|
decimal last_laborbad_qty = decimal.Parse(dt1.Rows[0]["laborbad_qty"].ToString()); //下道工序工废总数量
|
decimal last_materielbad_qty = decimal.Parse(dt1.Rows[0]["materielbad_qty"].ToString()); //下道工序料废总数量
|
decimal last_updatereportsumqty = last_reportqty + last_ngqty + last_laborbad_qty + last_materielbad_qty;
|
//判断（当前非本次报工总合格数+本次报工调整合格数）<下道工序报工总数
|
if ((notthis_reportqty + this_reportqty) < last_updatereportsumqty)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前工序修改报工总合格数量：【" + (notthis_reportqty + this_reportqty) + "】不能小于下道工序报工总数量：【" + last_updatereportsumqty + "】!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
}
|
else
|
{
|
//查询当前工序下道工序：总发料数量
|
sql = @"select isnull(sum(fqty),0) as good_qty
|
from TK_Wrk_OutRecord where wo_code=@wo_code and style='F' and step_seq=@step_seq+1";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@step_seq", json[0].step_seq);
|
var dt0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt0.Rows.Count > 0)
|
{
|
//判断当前工序：发料总数数量
|
decimal last_reportqty = decimal.Parse(dt0.Rows[0]["good_qty"].ToString()); //下道工序发料总合格数量
|
//判断（当前非本次报工总合格数+本次报工调整合格数）<下道工序发料总数
|
if ((notthis_reportqty + this_reportqty) < last_reportqty)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前工序修改报工总数量：【" + (notthis_reportqty + this_reportqty) + "】不能大于下道工序发料总数量：【" + last_reportqty + "】!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
}
|
}
|
}
|
}
|
if (json[0].flw_type.ToString() == "W")//外协工序
|
{
|
//查询当前首道工序非此次供应商收料：总收料数量
|
sql = @"select isnull(sum(sqty),0) as sqty
|
from TK_Wrk_OutRecord where wo_code=@wo_code and style='S' and id<>@id and step_code=@step_code and wx_code<>@wx_code";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@id", json[0].id);
|
dynamicParams.Add("@step_code", json[0].step_code);
|
dynamicParams.Add("@wx_code", json[0].wxcode);
|
var dt_c = DapperHelper.selectdata(sql, dynamicParams);
|
|
//获取当前工序、供应商对应的发料数量
|
sql = @"select isnull(sum(fqty),0) as fqty
|
from TK_Wrk_OutRecord where wo_code=@wo_code and style='F' and id<>@id and step_code=@step_code and wx_code=@wx_code";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@id", json[0].id);
|
dynamicParams.Add("@step_code", json[0].step_code);
|
dynamicParams.Add("@wx_code", json[0].wxcode);
|
var dt_0 = DapperHelper.selectdata(sql, dynamicParams);
|
//首道工序的收料
|
if (json[0].first_choke == "Y")
|
{
|
//查询当前首道工序非此次收料：总收料数量、总不良数量、总工废数量、总料废数量
|
sql = @"select isnull(sum(sqty),0) as good_qty,isnull(sum(ng_qty),0) as ng_qty,isnull(sum(laborbad_qty),0) as laborbad_qty,isnull(sum(materielbad_qty),0) as materielbad_qty
|
from TK_Wrk_OutRecord where wo_code=@wo_code and style='S' and id<>@id and step_code=@step_code and wx_code=@wx_code";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@id", json[0].id);
|
dynamicParams.Add("@step_code", json[0].step_code);
|
dynamicParams.Add("@wx_code", json[0].wxcode);
|
var dt = DapperHelper.selectdata(sql, dynamicParams);
|
decimal notthis_reportqty = decimal.Parse(dt.Rows[0]["good_qty"].ToString()); //当前工序非本次收料总数
|
decimal notthis_ngqty = decimal.Parse(dt.Rows[0]["ng_qty"].ToString()); //当前工序非本次报工总不良数
|
decimal notthis_laborbadqty = decimal.Parse(dt.Rows[0]["laborbad_qty"].ToString()); //当前工序非本次报工总工废数
|
decimal notthis_materielbadqty = decimal.Parse(dt.Rows[0]["materielbad_qty"].ToString()); //当前工序非本次报工总料废数
|
//判断：当前工序报工记录：本次收料数量+本次不良数量+本次工废数量+本次料废数量+当前工序非本次收料总数+当前工序非本次不良总数+当前工序非本次工废总数+当前工序非本次料废总数>发料数量
|
decimal updatereportsumqty = this_reportqty + this_ngqty + this_laborbadqty + this_materielbadqty + notthis_reportqty + notthis_ngqty + notthis_laborbadqty + notthis_materielbadqty;
|
if (updatereportsumqty > decimal.Parse(dt_0.Rows[0]["fqty"].ToString()))
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前外协工序对应供应商收料总数量：【" + updatereportsumqty + "】不能大于发料数量：【" + decimal.Parse(dt_0.Rows[0]["fqty"].ToString()) + "】!";
|
mes.data = null;
|
return mes;
|
}
|
//判断是否存在下道工序及属性
|
if (next.Rows.Count > 0)
|
{
|
if (next.Rows[0]["flwtype"].ToString() == "Z")
|
{
|
//查询当前工序下道工序：总报工数量、总不良数量、总报废数量
|
sql = @"select isnull(sum(good_qty),0) as good_qty,isnull(sum(ng_qty),0) as ng_qty,isnull(sum(laborbad_qty),0) as laborbad_qty,isnull(sum(materielbad_qty),0) as materielbad_qty
|
from TK_Wrk_Record where wo_code=@wo_code and style='B' and step_seq=@step_seq+1";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@step_seq", json[0].step_seq);
|
var dt0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt0.Rows.Count > 0)
|
{
|
//判断当前工序：报工总数数量+不良总数数量+工废总数量+料废总数量<下道工序报工总数量+下道工序不良总数量+下道工序工废总数量+下道工序料废总数量
|
decimal last_reportqty = decimal.Parse(dt0.Rows[0]["good_qty"].ToString()); //下道工序报工总数量
|
decimal last_ngqty = decimal.Parse(dt0.Rows[0]["ng_qty"].ToString()); //下道工序不良总数量
|
decimal last_laborbad_qty = decimal.Parse(dt0.Rows[0]["laborbad_qty"].ToString()); //下道工序工废总数量
|
decimal last_materielbad_qty = decimal.Parse(dt0.Rows[0]["materielbad_qty"].ToString()); //下道工序料废总数量
|
decimal last_updatereportsumqty = last_reportqty + last_ngqty + last_laborbad_qty + last_materielbad_qty;
|
//判断（当前非本次收料数+本次收料调整数+非当前供应商同工序收料总数）<下道工序报工总数
|
if ((notthis_reportqty + this_reportqty + decimal.Parse(dt_c.Rows[0]["sqty"].ToString())) < last_updatereportsumqty)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前外协工序收料数量：【" + (notthis_reportqty + this_reportqty + decimal.Parse(dt_c.Rows[0]["sqty"].ToString())) + "】不能小于下道自制工序报工总数量：【" + last_updatereportsumqty + "】!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
}
|
else
|
{
|
//查询当前工序下道工序：总发料数量
|
sql = @"select isnull(sum(fqty),0) as good_qty
|
from TK_Wrk_OutRecord where wo_code=@wo_code and style='F' and step_seq=@step_seq+1";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@step_seq", json[0].step_seq);
|
var dt0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt0.Rows.Count > 0)
|
{
|
//判断当前工序：发料总数数量
|
decimal last_reportqty = decimal.Parse(dt0.Rows[0]["good_qty"].ToString()); //下道工序发料总数量
|
//判断（当前非本次收料数+本次报工调整收料数）<下道工序发料总数
|
if ((notthis_reportqty + this_reportqty) < last_reportqty)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前外协工序收料总数量：【" + (notthis_reportqty + this_reportqty) + "】不能小于下道外协工序发料总数量：【" + last_reportqty + "】!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
}
|
}
|
|
}
|
//末道工序的报工
|
else if (json[0].last_choke == "Y")
|
{
|
//查询当前末道报工工序非此次收料：总收料数量、总不良数量、总工废数量、总料废数量
|
sql = @"select isnull(sum(sqty),0) as good_qty,isnull(sum(ng_qty),0) as ng_qty,isnull(sum(laborbad_qty),0) as laborbad_qty,isnull(sum(materielbad_qty),0) as materielbad_qty
|
from TK_Wrk_OutRecord where wo_code=@wo_code and style='S' and id<>@id and step_code=@step_code and wx_code=@wx_code";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@id", json[0].id);
|
dynamicParams.Add("@step_code", json[0].step_code);
|
var dt = DapperHelper.selectdata(sql, dynamicParams);
|
decimal notthis_reportqty = decimal.Parse(dt.Rows[0]["good_qty"].ToString()); //当前末道工序非本次收料总数
|
decimal notthis_ngqty = decimal.Parse(dt.Rows[0]["ng_qty"].ToString()); //当前末道工序非本次报工总数
|
decimal notthis_laborbad_qty = decimal.Parse(dt.Rows[0]["laborbad_qty"].ToString()); //当前末道工序非本次报工工废总数
|
decimal notthis_materielbad_qty = decimal.Parse(dt.Rows[0]["materielbad_qty"].ToString()); //当前末道工序非本次报工料废总数
|
//获取当前末道工序收料总数量：本次修改收料数量+本次修改不良数量+本次修改工废数量+本次修改报工料废数量+当前末道工序非本次收料总数+当前末道工序非本次不良总数+当前末道工序非本次工废总数+当前末道工序非本次料废总数
|
decimal updatereportsumqty = this_reportqty + this_ngqty + this_laborbadqty + this_materielbadqty + notthis_reportqty + notthis_ngqty + notthis_laborbad_qty + notthis_materielbad_qty;
|
//判断当前工序供应商收料总数>当前工序供应商对应发料数量
|
if (updatereportsumqty > decimal.Parse(dt_0.Rows[0]["fqty"].ToString()))
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前工序供应商收料总数量：【" + updatereportsumqty + "】不能大于工序供应商发料总数量：【" + decimal.Parse(dt_0.Rows[0]["fqty"].ToString()) + "】!";
|
mes.data = null;
|
return mes;
|
}
|
|
}
|
else //中间工序的报工
|
{
|
|
//查询当前首道工序供应商非此次收料：总收料数量、总不良数量、总工废数量、总料废数量
|
sql = @"select isnull(sum(sqty),0) as good_qty,isnull(sum(ng_qty),0) as ng_qty,isnull(sum(laborbad_qty),0) as laborbad_qty,isnull(sum(materielbad_qty),0) as materielbad_qty
|
from TK_Wrk_OutRecord where wo_code=@wo_code and style='S' and id<>@id and step_code=@step_code and wx_code=@wx_code";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@id", json[0].id);
|
dynamicParams.Add("@step_code", json[0].step_code);
|
dynamicParams.Add("@wx_code", json[0].wxcode);
|
var dt = DapperHelper.selectdata(sql, dynamicParams);
|
decimal notthis_reportqty = decimal.Parse(dt.Rows[0]["good_qty"].ToString()); //当前工序非本次收料总数
|
decimal notthis_ngqty = decimal.Parse(dt.Rows[0]["ng_qty"].ToString()); //当前工序非本次报工总不良数
|
decimal notthis_laborbadqty = decimal.Parse(dt.Rows[0]["laborbad_qty"].ToString()); //当前工序非本次报工总工废数
|
decimal notthis_materielbadqty = decimal.Parse(dt.Rows[0]["materielbad_qty"].ToString()); //当前工序非本次报工总料废数
|
//判断：当前工序报工记录：本次收料数量+本次不良数量+本次工废数量+本次料废数量+当前工序非本次收料总数+当前工序非本次不良总数+当前工序非本次工废总数+当前工序非本次料废总数>发料数量
|
decimal updatereportsumqty = this_reportqty + this_ngqty + this_laborbadqty + this_materielbadqty + notthis_reportqty + notthis_ngqty + notthis_laborbadqty + notthis_materielbadqty;
|
if (updatereportsumqty > decimal.Parse(dt_0.Rows[0]["fqty"].ToString()))
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前外协工序对应供应商收料总数量：【" + updatereportsumqty + "】不能大于发料数量：【" + decimal.Parse(dt_0.Rows[0]["fqty"].ToString()) + "】!";
|
mes.data = null;
|
return mes;
|
}
|
//判断是否存在下道工序及属性
|
if (next.Rows.Count > 0)
|
{
|
if (next.Rows[0]["flwtype"].ToString() == "Z")
|
{
|
//查询当前工序下道工序：总报工数量、总不良数量、总报废数量
|
sql = @"select isnull(sum(good_qty),0) as good_qty,isnull(sum(ng_qty),0) as ng_qty,isnull(sum(laborbad_qty),0) as laborbad_qty,isnull(sum(materielbad_qty),0) as materielbad_qty
|
from TK_Wrk_Record where wo_code=@wo_code and style='B' and step_seq=@step_seq+1";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@step_seq", json[0].step_seq);
|
var dt0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt0.Rows.Count > 0)
|
{
|
//判断当前工序：报工总数数量+不良总数数量+工废总数量+料废总数量<下道工序报工总数量+下道工序不良总数量+下道工序工废总数量+下道工序料废总数量
|
decimal last_reportqty = decimal.Parse(dt0.Rows[0]["good_qty"].ToString()); //下道工序报工总数量
|
decimal last_ngqty = decimal.Parse(dt0.Rows[0]["ng_qty"].ToString()); //下道工序不良总数量
|
decimal last_laborbad_qty = decimal.Parse(dt0.Rows[0]["laborbad_qty"].ToString()); //下道工序工废总数量
|
decimal last_materielbad_qty = decimal.Parse(dt0.Rows[0]["materielbad_qty"].ToString()); //下道工序料废总数量
|
decimal last_updatereportsumqty = last_reportqty + last_ngqty + last_laborbad_qty + last_materielbad_qty;
|
//判断（当前非本次收料数+本次收料调整数+非当前供应商同工序收料总数）<下道工序报工总数
|
if ((notthis_reportqty + this_reportqty + decimal.Parse(dt_c.Rows[0]["sqty"].ToString())) < last_updatereportsumqty)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前外协工序收料数量：【" + (notthis_reportqty + this_reportqty + decimal.Parse(dt_c.Rows[0]["sqty"].ToString())) + "】不能小于下道自制工序报工总数量：【" + last_updatereportsumqty + "】!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
}
|
else
|
{
|
//查询当前工序下道工序：总发料数量
|
sql = @"select isnull(sum(fqty),0) as good_qty
|
from TK_Wrk_OutRecord where wo_code=@wo_code and style='F' and step_seq=@step_seq+1";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@step_seq", json[0].step_seq);
|
var dt0 = DapperHelper.selectdata(sql, dynamicParams);
|
if (dt0.Rows.Count > 0)
|
{
|
//判断当前工序：发料总数数量
|
decimal last_reportqty = decimal.Parse(dt0.Rows[0]["good_qty"].ToString()); //下道工序发料总数量
|
//判断（当前非本次收料数+本次报工调整收料数+非当前供应商同工序收料总数）<下道工序发料总数
|
if ((notthis_reportqty + this_reportqty + decimal.Parse(dt_c.Rows[0]["sqty"].ToString())) < last_reportqty)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前外协工序收料总数量：【" + (notthis_reportqty + this_reportqty + decimal.Parse(dt_c.Rows[0]["sqty"].ToString())) + "】不能小于下道外协工序发料总数量：【" + last_reportqty + "】!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
else //不按序
|
{
|
//控制逻辑：当前工序报工调整-> (本道工序当前调整合格数+本道工序非当前报工合格总数)<下道工序报工总数(合格+不良+报废) ==不能小于下道报工总数
|
list.Clear();
|
//判断当前工序是自制工序还是外协工序
|
if (json[0].flw_type.ToString() == "Z")
|
{
|
|
//查询当前报工工序非此次报工：总报工数量、总不良数量、总工废数量、总料废数量
|
sql = @"select isnull(sum(good_qty),0) as good_qty,isnull(sum(ng_qty),0) as ng_qty,isnull(sum(laborbad_qty),0) as laborbad_qty,isnull(sum(materielbad_qty),0) as materielbad_qty
|
from TK_Wrk_Record where wo_code=@wo_code and style='B' and id<>@id and step_code=@step_code";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@id", json[0].id);
|
dynamicParams.Add("@step_code", json[0].step_code);
|
var dt = DapperHelper.selectdata(sql, dynamicParams);
|
decimal notthis_reportqty = decimal.Parse(dt.Rows[0]["good_qty"].ToString()); //当前工序非本次报工总数
|
decimal notthis_ngqty = decimal.Parse(dt.Rows[0]["ng_qty"].ToString()); //当前工序非本次报工总不良数
|
decimal notthis_laborbadqty = decimal.Parse(dt.Rows[0]["laborbad_qty"].ToString()); //当前工序非本次报工总工废数
|
decimal notthis_materielbadqty = decimal.Parse(dt.Rows[0]["materielbad_qty"].ToString()); //当前工序非本次报工总料废数
|
//判断：当前工序报工记录：本次报工数量+本次不良数量+本次工废数量+本次料废数量+当前工序非本次报工总数+当前工序非本次不良总数+当前工序非本次工废总数+当前工序非本次料废总数>工单任务数量
|
decimal updatereportsumqty = this_reportqty + this_ngqty + this_laborbadqty + this_materielbadqty + notthis_reportqty + notthis_ngqty + notthis_laborbadqty + notthis_materielbadqty;
|
if (updatereportsumqty > decimal.Parse(json[0].task_qty.ToString()))
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前工序修改报工总数量：【" + updatereportsumqty + "】不能大于任务超产总数量：【" + json[0].task_qty.ToString() + "】!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
if (json[0].flw_type.ToString() == "W")
|
{
|
//获取当前工序、供应商对应的总发料数量
|
sql = @"select isnull(sum(fqty),0) as fqty
|
from TK_Wrk_OutRecord where wo_code=@wo_code and style='F' and id<>@id and step_code=@step_code and wx_code=@wx_code";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@id", json[0].id);
|
dynamicParams.Add("@step_code", json[0].step_code);
|
dynamicParams.Add("@wx_code", json[0].wxcode);
|
var dt_0 = DapperHelper.selectdata(sql, dynamicParams);
|
|
//查询当前工序非此次收料：总收料数量、总不良数量、总工废数量、总料废数量
|
sql = @"select isnull(sum(sqty),0) as good_qty,isnull(sum(ng_qty),0) as ng_qty,isnull(sum(laborbad_qty),0) as laborbad_qty,isnull(sum(materielbad_qty),0) as materielbad_qty
|
from TK_Wrk_OutRecord where wo_code=@wo_code and style='S' and id<>@id and step_code=@step_code and wx_code=@wx_code";
|
dynamicParams.Add("@wo_code", json[0].wo_code);
|
dynamicParams.Add("@id", json[0].id);
|
dynamicParams.Add("@step_code", json[0].step_code);
|
var dt = DapperHelper.selectdata(sql, dynamicParams);
|
decimal notthis_reportqty = decimal.Parse(dt.Rows[0]["good_qty"].ToString()); //当前末道工序非本次收料总数
|
decimal notthis_ngqty = decimal.Parse(dt.Rows[0]["ng_qty"].ToString()); //当前末道工序非本次报工总数
|
decimal notthis_laborbad_qty = decimal.Parse(dt.Rows[0]["laborbad_qty"].ToString()); //当前末道工序非本次报工工废总数
|
decimal notthis_materielbad_qty = decimal.Parse(dt.Rows[0]["materielbad_qty"].ToString()); //当前末道工序非本次报工料废总数
|
//获取当前末道工序收料总数量：本次修改收料数量+本次修改不良数量+本次修改工废数量+本次修改报工料废数量+当前工序非本次收料总数+当前工序非本次不良总数+当前工序非本次工废总数+当前工序非本次料废总数
|
decimal updatereportsumqty = this_reportqty + this_ngqty + this_laborbadqty + this_materielbadqty + notthis_reportqty + notthis_ngqty + notthis_laborbad_qty + notthis_materielbad_qty;
|
//判断当前工序供应商收料总数>当前工序供应商对应发料数量
|
if (updatereportsumqty > decimal.Parse(dt_0.Rows[0]["fqty"].ToString()))
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "当前工序供应商收料总数量：【" + updatereportsumqty + "】不能大于工序供应商发料总数量：【" + decimal.Parse(dt_0.Rows[0]["fqty"].ToString()) + "】!";
|
mes.data = null;
|
return mes;
|
}
|
}
|
}
|
|
switch (json[0].flw_type.ToString())
|
{
|
case "Z":
|
///////////////////////////////修改报工//////////////////////////////
|
|
//回写对应的报工记录子表合格数量、不良数量、报废数量
|
sql = @"update TK_Wrk_RecordSub set report_qty=report_qty+@repair_qty,ng_qty=ng_qty+@ng_qty,laborbad_qty=laborbad_qty+@laborbad_qty,materielbad_qty=materielbad_qty+@materielbad_qty,
|
updatereportuser=@updatereportuser,updatereportdate=@updatereportdate
|
where m_id=@m_id and id=@id and style='B'";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
m_id = int.Parse(json[0].id),
|
id = int.Parse(json[0].sbid),
|
repair_qty = decimal.Parse(json[0].report_dvalue),
|
ng_qty = this_ng_dvalue,
|
laborbad_qty = this_laborbad_dvalue,
|
materielbad_qty = this_materielbad_dvalue,
|
//bad_money = decimal.Parse(json[i].badmoney_dvalue),
|
updatereportuser = us.usercode,
|
updatereportdate = date
|
}
|
});
|
//回写对应的报工记录主表合格数量、不良数量、报废数量
|
sql = @"update TK_Wrk_Record set step_price=@step_price,start_qty=start_qty+@good_qty, good_qty=good_qty+@good_qty,ng_qty=ng_qty+@ng_qty,laborbad_qty=laborbad_qty+@laborbad_qty,materielbad_qty=materielbad_qty+@materielbad_qty,
|
updatereportuser=@updatereportuser,updatereportdate=@updatereportdate
|
where wo_code=@wo_code and step_code=@step_code and id=@id and style='B'";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
step_price=decimal.Parse(json[0].unprice),
|
good_qty = decimal.Parse(json[0].report_dvalue),
|
ng_qty = this_ng_dvalue,
|
laborbad_qty = this_laborbad_dvalue,
|
materielbad_qty = this_materielbad_dvalue,
|
wo_code = json[0].wo_code,
|
step_code = json[0].step_code,
|
id = int.Parse(json[0].id),
|
updatereportuser = us.usercode,
|
updatereportdate = date
|
}
|
});
|
//回写工单工序表
|
sql = @"update TK_Wrk_Step set good_qty=good_qty+@good_qty,ng_qty=ng_qty+@ng_qty,laborbad_qty=laborbad_qty+@laborbad_qty,materielbad_qty=materielbad_qty+@materielbad_qty
|
where wo_code=@wo_code and step_code=@step_code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
good_qty = decimal.Parse(json[0].report_dvalue),
|
ng_qty = this_ng_dvalue,
|
laborbad_qty = this_laborbad_dvalue,
|
materielbad_qty = this_materielbad_dvalue,
|
wo_code = json[0].wo_code,
|
step_code = json[0].step_code
|
}
|
});
|
|
for (int i = 0; i < json[0].children.Count; i++)
|
{
|
//回写不良
|
sql = @"update CSR_WorkRecord_Defect set defect_qty=defect_qty+@ng_qty,defect_pendqty=defect_pendqty+@ng_qty,laborbad_qty=laborbad_qty+@laborbad_qty,materielbad_qty=materielbad_qty+@materielbad_qty,
|
updatereportuser=@updatereportuser,updatereportdate=@updatereportdate
|
where wo_code=@wo_code and step_code=@step_code and id=@ng_id and record_id=@record_id and style='B'";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
ng_qty = decimal.Parse(json[0].children[i].ng_dvalue),
|
laborbad_qty = decimal.Parse(json[0].children[i].laborbad_dvalue),
|
materielbad_qty = decimal.Parse(json[0].children[i].materielbad_dvalue),
|
wo_code = json[0].wo_code,
|
step_code = json[0].step_code,
|
ng_id = int.Parse(json[0].children[i].ng_id),
|
record_id = json[0].id,
|
updatereportuser = us.usercode,
|
updatereportdate = date
|
}
|
});
|
//回写不良处理
|
sql = @"update CSR_WorkRecord_DefectHandle set laborbad_qty=laborbad_qty+@laborbad_qty,materielbad_qty=materielbad_qty+@materielbad_qty
|
where wo_code=@wo_code and step_code=@step_code and defect_id=@defect_id and style='B'";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
laborbad_qty = decimal.Parse(json[0].children[i].laborbad_dvalue),
|
materielbad_qty = decimal.Parse(json[0].children[i].materielbad_dvalue),
|
wo_code = json[0].wo_code,
|
step_code = json[0].step_code,
|
defect_id = int.Parse(json[0].children[i].ng_id),
|
updatereportuser = us.usercode,
|
updatereportdate = date
|
}
|
});
|
}
|
break;
|
case "W":
|
///////////////////////////////修改报工//////////////////////////////
|
|
//回写对应的外协记录子表收料数量、不良数量、报废数量
|
sql = @"update TK_Wrk_OutRecordSub set sqty=sqty+@repair_qty,ng_qty=ng_qty+@ng_qty,laborbad_qty=laborbad_qty+@laborbad_qty,materielbad_qty=materielbad_qty+@materielbad_qty,
|
updatereportuser=@updatereportuser,updatereportdate=@updatereportdate
|
where m_id=@m_id and id=@id and style='S' and wx_code=@wx_code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
m_id = int.Parse(json[0].id),
|
id = int.Parse(json[0].sbid),
|
repair_qty = decimal.Parse(json[0].report_dvalue),
|
ng_qty = this_ng_dvalue,
|
laborbad_qty = this_laborbad_dvalue,
|
materielbad_qty = this_materielbad_dvalue,
|
wx_code = json[0].wxcode,
|
//bad_money = decimal.Parse(json[i].badmoney_dvalue),
|
updatereportuser = us.usercode,
|
updatereportdate = date
|
}
|
});
|
//回写对应的收料记录主表收料数量、不良数量、报废数量
|
sql = @"update TK_Wrk_OutRecord set step_price=@step_price,sqty=sqty+@good_qty,ng_qty=ng_qty+@ng_qty,laborbad_qty=laborbad_qty+@laborbad_qty,materielbad_qty=materielbad_qty+@materielbad_qty,
|
updatereportuser=@updatereportuser,updatereportdate=@updatereportdate
|
where wo_code=@wo_code and step_code=@step_code and id=@id and style='S' and wx_code=@wx_code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
step_price=decimal.Parse(json[0].unprice),
|
good_qty = decimal.Parse(json[0].report_dvalue),
|
ng_qty = this_ng_dvalue,
|
laborbad_qty = this_laborbad_dvalue,
|
materielbad_qty = this_materielbad_dvalue,
|
wx_code = json[0].wxcode,
|
wo_code = json[0].wo_code,
|
step_code = json[0].step_code,
|
id = int.Parse(json[0].id),
|
updatereportuser = us.usercode,
|
updatereportdate = date
|
}
|
});
|
//回写工单工序表
|
sql = @"update TK_Wrk_Step set good_qty=good_qty+@good_qty,ng_qty=ng_qty+@ng_qty,laborbad_qty=laborbad_qty+@laborbad_qty,materielbad_qty=materielbad_qty+@materielbad_qty
|
where wo_code=@wo_code and step_code=@step_code";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
good_qty = decimal.Parse(json[0].report_dvalue),
|
ng_qty = this_ng_dvalue,
|
laborbad_qty = this_laborbad_dvalue,
|
materielbad_qty = this_materielbad_dvalue,
|
wo_code = json[0].wo_code,
|
step_code = json[0].step_code
|
}
|
});
|
|
for (int i = 0; i < json[0].children.Count; i++)
|
{
|
//回写不良
|
sql = @"update CSR_WorkRecord_Defect set defect_qty=defect_qty+@ng_qty,defect_pendqty=defect_pendqty+@ng_qty,laborbad_qty=laborbad_qty+@laborbad_qty,materielbad_qty=materielbad_qty+@materielbad_qty,
|
updatereportuser=@updatereportuser,updatereportdate=@updatereportdate
|
where wo_code=@wo_code and step_code=@step_code and id=@ng_id and record_id=@record_id and style='S'";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
ng_qty = decimal.Parse(json[0].children[i].ng_dvalue),
|
laborbad_qty = decimal.Parse(json[0].children[i].laborbad_dvalue),
|
materielbad_qty = decimal.Parse(json[0].children[i].materielbad_dvalue),
|
wo_code = json[0].wo_code,
|
step_code = json[0].step_code,
|
ng_id = int.Parse(json[0].children[i].ng_id),
|
record_id = json[0].id,
|
updatereportuser = us.usercode,
|
updatereportdate = date
|
}
|
});
|
//回写不良处理
|
sql = @"update CSR_WorkRecord_DefectHandle set laborbad_qty=laborbad_qty+@laborbad_qty,materielbad_qty=materielbad_qty+@materielbad_qty
|
where wo_code=@wo_code and step_code=@step_code and defect_id=@defect_id and style='S'";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
laborbad_qty = decimal.Parse(json[0].children[i].laborbad_dvalue),
|
materielbad_qty = decimal.Parse(json[0].children[i].materielbad_dvalue),
|
wo_code = json[0].wo_code,
|
step_code = json[0].step_code,
|
defect_id = int.Parse(json[0].children[i].ng_id),
|
updatereportuser = us.usercode,
|
updatereportdate = date
|
}
|
});
|
}
|
break;
|
default:
|
break;
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "报工调整", "工单:" + json[0].wo_code + "工序:" + json[0].step_code, 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 MesOrderStepPriceBatchUpdateSeave(User us, List<BatchPrice> json)
|
{
|
string sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (json.Count > 0)
|
{
|
// 使用Lambda表达式判断类型分布:全部报工或者全部收料
|
bool allB = json.All(p => p.type == "B");
|
bool allS = json.All(p => p.type == "S");
|
if (allB)//全部报工
|
{
|
//将List<BatchPrice>中的id字段转换为字符串数组
|
string[] idArray = json.Select(bp => bp.id).ToArray();
|
//回写对应的报工记录主表工价
|
sql = @"update TK_Wrk_Record set step_price=@step_price where id in @idArray and style='B'";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
step_price = decimal.Parse(json[0].price),
|
idArray = idArray
|
}
|
});
|
}
|
else if (allS)//全部收料
|
{
|
//将List<BatchPrice>中的id字段转换为字符串数组
|
string[] idArray = json.Select(bp => bp.id).ToArray();
|
//回写对应的报工记录主表工价
|
sql = @"update TK_Wrk_OutRecord set step_price=@step_price where id in @idArray and style='S'";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
step_price = decimal.Parse(json[0].price),
|
idArray = idArray
|
}
|
});
|
}
|
else // 混合情况
|
{
|
//将List<BatchPrice>中为报工的id字段转换为字符串数组
|
var bList = json.Where(p => p.type == "B").ToList();
|
string[] idBArray = bList.Select(bp => bp.id).ToArray();
|
//回写对应的报工记录主表工价
|
sql = @"update TK_Wrk_Record set step_price=@step_price where id in @idBArray and style='B'";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
step_price = decimal.Parse(json[0].price),
|
idBArray = idBArray
|
}
|
});
|
//将List<BatchPrice>中为收料的id字段转换为字符串数组
|
var sList = json.Where(p => p.type == "S").ToList();
|
string[] idSArray = sList.Select(bp => bp.id).ToArray();
|
//回写对应的报工记录主表工价
|
sql = @"update TK_Wrk_OutRecord set step_price=@step_price where id in @idSArray and style='S'";
|
list.Add(new
|
{
|
str = sql,
|
parm = new
|
{
|
step_price = decimal.Parse(json[0].price),
|
idSArray = idSArray
|
}
|
});
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "批量修改工价", "报工ID:" + string.Join(",",json.Select(bp => bp.id).ToArray())+",操作类型:"+ string.Join(",", json.Select(bp => bp.type).ToArray()), 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;
|
}
|
}
|
else
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = "请选择需要批量调整工价的报工记录!";
|
mes.data = null;
|
return mes;
|
}
|
return mes;
|
}
|
catch (Exception)
|
{
|
|
throw;
|
}
|
}
|
#endregion
|
|
|
|
#region[生产执行,报工审核列表数据查询接口]
|
public static ToMessage MesOrderStepReportVerifySearch(string reviewstatus, string wkshopcode, string wo_code, string partnumber, string partname, string partspec, string stepname, string reportuser, string reportdateopendate, string reportdateclosedate, int startNum, int endNum, string prop, string order)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (reviewstatus != "" && reviewstatus != null)
|
{
|
search += "and AA.verify=@reviewstatus ";
|
dynamicParams.Add("@reviewstatus", reviewstatus);
|
}
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and AA.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
if (wo_code != "" && wo_code != null)
|
{
|
search += "and AA.wo_code like '%'+@wo_code+'%' ";
|
dynamicParams.Add("@wo_code", wo_code);
|
}
|
if (partnumber != "" && partnumber != null)
|
{
|
search += "and AA.partnumber like '%'+@partnumber+'%' ";
|
dynamicParams.Add("@partnumber", partnumber);
|
}
|
if (partname != "" && partname != null)
|
{
|
search += "and AA.partname like '%'+@partname+'%' ";
|
dynamicParams.Add("@partname", partname);
|
}
|
if (partspec != "" && partspec != null)
|
{
|
search += "and AA.partspec like '%'+@partspec+'%' ";
|
dynamicParams.Add("@partspec", partspec);
|
}
|
if (stepname != "" && stepname != null)
|
{
|
search += "and AA.stepname like '%'+@stepname+'%' ";
|
dynamicParams.Add("@stepname", stepname);
|
}
|
if (reportuser != "" && reportuser != null)
|
{
|
search += "and AA.usercode like '%'+@reportuser+'%' ";
|
dynamicParams.Add("@reportuser", reportuser);
|
}
|
if (reportdateopendate != "" && reportdateopendate != null)
|
{
|
search += "and AA.report_date between @reportdateopendate and @reportdateclosedate ";
|
dynamicParams.Add("@reportdateopendate", reportdateopendate + " 00:00:00");
|
dynamicParams.Add("@reportdateclosedate", reportdateclosedate + " 23:59:59");
|
}
|
|
|
if (search == "")
|
{
|
search = "and 1=1 ";
|
}
|
search = search.Substring(3);//截取索引2后面的字符
|
// --------------查询指定自制报工外协收料数据--------------
|
var total = 0; //总条数
|
var sql = @"select * from(
|
select A.id,B.id as sbid,A.wo_code,A.materiel_code as partnumber,P.partname,P.partspec,K.plan_quantity,K.plan_qty as task_qty,M.wkshp_code,T.torg_name as wkshp_name,A.eqp_code,E.name as eqp_name,
|
A.step_seq,A.step_code,S.stepname,S.flwtype as steptype,k.isbott as first_choke,k.isend as last_choke,A.step_price,B.reckway,B.usergroup_code,G.usergroupname as usergroup_name,
|
B.report_person as usercode,
|
STUFF((SELECT ',' + U.username
|
FROM TUser U
|
WHERE CHARINDEX(',' + U.usercode + ',', ',' + B.report_person + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS username,
|
B.report_date,B.report_qty,B.ng_qty,B.laborbad_qty,B.materielbad_qty,'' as wx_code,'' as wx_name,A.verify
|
from TK_Wrk_Record A
|
inner join TK_Wrk_RecordSub B on A.id=B.m_id
|
left join TK_Wrk_Man M on A.wo_code=M.wo_code
|
left join TK_Wrk_Step K on M.wo_code=K.wo_code and A.step_code=K.step_code
|
left join TStep S on A.step_code=S.stepcode
|
left join TMateriel_Info P on A.materiel_code=P.partcode
|
left join TOrganization T on M.wkshp_code=T.torg_code
|
left join TEqpInfo E on A.eqp_code=E.code
|
left join TGroup G on G.usergroupcode=B.usergroup_code
|
where A.style='B' and B.style='B' and M.status<>'CLOSED'
|
union all
|
select A.id,B.id as sbid,A.wo_code,A.materiel_code as partnumber,P.partname,P.partspec,K.plan_quantity,K.plan_qty as task_qty,M.wkshp_code,T.torg_name as wkshp_name,A.wx_code as eqp_code,E.name as eqp_name,
|
A.step_seq,A.step_code,S.stepname,S.flwtype as steptype,k.isbott as first_choke,k.isend as last_choke,A.step_price,'person' as reckway,'' as usergroup_code,'' as usergroup_name,
|
B.in_person as usercode,
|
STUFF((SELECT ',' + U.username
|
FROM TUser U
|
WHERE CHARINDEX(',' + U.usercode + ',', ',' + B.in_person + ',') > 0
|
FOR XML PATH('')), 1, 1, '') AS username,
|
B.in_time as report_date,B.sqty as report_qty,B.ng_qty,B.laborbad_qty,B.materielbad_qty,A.wx_code,C.name as wx_name,A.verify
|
from TK_Wrk_OutRecord A
|
inner join TK_Wrk_OutRecordSub B on A.id = B.m_id
|
left join TK_Wrk_Man M on A.wo_code = M.wo_code
|
left join TK_Wrk_Step K on M.wo_code=K.wo_code and A.step_code=K.step_code
|
left join TStep S on A.step_code = S.stepcode
|
left join TMateriel_Info P on A.materiel_code = P.partcode
|
left join TOrganization T on M.wkshp_code = T.torg_code
|
left join TCustomer E on A.wx_code = E.code
|
left join TCustomer C on A.wx_code=C.code
|
where A.style = 'S' and B.style = 'S' and M.status<>'CLOSED'
|
) as AA where " + 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 MesOrderStepReportVerifySeave(User us, DataModel json)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (json.zdata.Count > 0)
|
{
|
//回写报工记录主表审核状态、审核人、审核时间
|
sql = @"update TK_Wrk_Record set verify='Y',verifyuser=@verifyuser,verifydate=@verifydate where id in @id";
|
list.Add(new { str = sql, parm = new { verifyuser = us.usercode, verifydate = DateTime.Now.ToString(), id = json.zdata } });
|
//回写报工记录子表审核状态、审核人、审核时间
|
sql = @"update TK_Wrk_RecordSub set verify='Y',verifyuser=@verifyuser,verifydate=@verifydate where m_id in @id";
|
list.Add(new { str = sql, parm = new { verifyuser = us.usercode, verifydate = DateTime.Now.ToString(), id = json.zdata } });
|
}
|
if (json.wdata.Count > 0)
|
{
|
//回写外协记录主表审核状态、审核人、审核时间
|
sql = @"update TK_Wrk_OutRecord set verify='Y',verifyuser=@verifyuser,verifydate=@verifydate where id in @id";
|
list.Add(new { str = sql, parm = new { verifyuser = us.usercode, verifydate = DateTime.Now.ToString(), id = json.wdata } });
|
//回写外协记录子表审核状态、审核人、审核时间
|
sql = @"update TK_Wrk_OutRecordSub set verify='Y',verifyuser=@verifyuser,verifydate=@verifydate where m_id in @id";
|
list.Add(new { str = sql, parm = new { verifyuser = us.usercode, verifydate = DateTime.Now.ToString(), id = json.wdata } });
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "报工审核", "自制报工记录id:" + string.Join(",", json.zdata), us.usertype);
|
LogHelper.DbOperateLog(us.usercode, "报工审核", "外协收料记录id:" + string.Join(",", json.wdata), 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 MesOrderStepReportNotVerifySeave(User us, string id, string steptype)
|
{
|
var sql = "";
|
List<object> list = new List<object>();
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
if (steptype == "Z")//自制工序
|
{
|
//回写报工记录主表审核状态、审核人、审核时间
|
sql = @"update TK_Wrk_Record set verify='N',verifyuser=@verifyuser,verifydate=@verifydate where id=@id";
|
list.Add(new { str = sql, parm = new { verifyuser = "", verifydate = "", id = id } });
|
//回写报工记录子表审核状态、审核人、审核时间
|
sql = @"update TK_Wrk_RecordSub set verify='N',verifyuser=@verifyuser,verifydate=@verifydate where m_id=@id";
|
list.Add(new { str = sql, parm = new { verifyuser = "", verifydate = "", id = id } });
|
}
|
if (steptype == "W")//外协工序
|
{
|
//回写外协记录主表审核状态、审核人、审核时间
|
sql = @"update TK_Wrk_OutRecord set verify='N',verifyuser=@verifyuser,verifydate=@verifydate where id=@id";
|
list.Add(new { str = sql, parm = new { verifyuser = "", verifydate = "", id = id } });
|
//回写外协记录子表审核状态、审核人、审核时间
|
sql = @"update TK_Wrk_OutRecordSub set verify='N',verifyuser=@verifyuser,verifydate=@verifydate where m_id=@id";
|
list.Add(new { str = sql, parm = new { verifyuser = "", verifydate = "", id = id } });
|
}
|
bool aa = DapperHelper.DoTransaction(list);
|
if (aa)
|
{
|
if (steptype == "Z")
|
{
|
//写入操作记录表
|
LogHelper.DbOperateLog(us.usercode, "报工弃审", "自制报工记录id:" + string.Join(",", id), us.usertype);
|
}
|
if (steptype == "W")
|
{
|
LogHelper.DbOperateLog(us.usercode, "报工弃审", "外协收料记录id:" + string.Join(",", id), 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 ProductInHouseLabCode(string ordercode)
|
{
|
string sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//获取末道工序报工条码数据
|
sql = @"select * from(
|
select A.inbarcode,A.wo_code,P.partcode,P.partname,P.partspec,
|
A.good_qty,U.username,A.lm_date
|
from TK_Wrk_Record A
|
inner join TK_Wrk_Step S on A.wo_code=S.wo_code and A.step_code=S.step_code
|
inner join TMateriel_Info P on A.materiel_code=P.partcode
|
inner join TUser U on A.lm_user=U.usercode
|
where A.style='B' and S.isend='Y' and A.good_qty>0 and A.inbarcode<>''
|
union all
|
select A.inbarcode,A.wo_code,P.partcode,P.partname,P.partspec,
|
A.sqty as sqty,U.username,A.lm_date
|
from TK_Wrk_OutRecord A
|
inner join TK_Wrk_Step S on A.wo_code=S.wo_code and A.step_code=S.step_code
|
inner join TMateriel_Info P on A.materiel_code=P.partcode
|
inner join TUser U on A.lm_user=U.usercode
|
where A.style='S' and S.isend='Y' and A.sqty>0 and A.inbarcode<>''
|
) as AA where AA.wo_code=@ordercode";
|
dynamicParams.Add("@ordercode", ordercode);
|
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 ProductInHouseOrderSearch(string issouceorder, string deptno, string saleordercode, string wkshopcode, string erpordercode, string mesordercode, string partcode, string partname, string partspec)
|
{
|
var dynamicParams = new DynamicParameters();
|
string search = "";
|
try
|
{
|
if (issouceorder != "" && issouceorder != null)
|
{
|
switch (issouceorder)
|
{
|
case "Y":
|
search += " and AA.m_po<>'' ";
|
break;
|
case "N":
|
search += " and AA.m_po='' ";
|
break;
|
default:
|
break;
|
}
|
}
|
if (deptno != "" && deptno != null)
|
{
|
search += "and AA.dept_code=@deptno ";
|
dynamicParams.Add("@deptno", deptno);
|
}
|
if (saleordercode != "" && saleordercode != null)
|
{
|
search += "and AA.saleOrderCode like '%'+@saleordercode+'%' ";
|
dynamicParams.Add("@saleordercode", saleordercode);
|
}
|
if (wkshopcode != "" && wkshopcode != null)
|
{
|
search += "and AA.wkshp_code=@wkshopcode ";
|
dynamicParams.Add("@wkshopcode", wkshopcode);
|
}
|
if (erpordercode != "" && erpordercode != null)
|
{
|
search += "and AA.m_po like '%'+@erpordercode+'%' ";
|
dynamicParams.Add("@erpordercode", erpordercode);
|
}
|
if (mesordercode != "" && mesordercode != null)
|
{
|
search += "and AA.wo_code like '%'+@mesordercode+'%' ";
|
dynamicParams.Add("@mesordercode", mesordercode);
|
}
|
if (partcode != "" && partcode != null)
|
{
|
search += "and AA.partcode like '%'+@partcode+'%' ";
|
dynamicParams.Add("@partcode", partcode);
|
}
|
if (partname != "" && partname != null)
|
{
|
search += "and AA.partname like '%'+@partname+'%' ";
|
dynamicParams.Add("@partname", partname);
|
}
|
if (partspec != "" && partspec != null)
|
{
|
search += "and AA.partspec like '%'+@partspec+'%' ";
|
dynamicParams.Add("@partspec", partspec);
|
}
|
// --------------查询指定数据--------------
|
var total = 0; //总条数
|
var sql = @"select * from(
|
select A.inbarcode,E.saleOrderid,isnull(E.saleOrderCode,'') as saleOrderCode,E.saleOrderDetailId,E.woid as mpoid,M.m_po,E.sbid,M.id as wo_id,A.wo_code,E.materiel_id,P.partcode,P.partname,P.partspec,E.unitid,E.unitcode,E.unitname,
|
A.step_code,T.stepname,M.wkshp_code,O.torg_name as wkshp_name,COALESCE(K.noid, E.stck_id) as stockid,COALESCE(K.code, E.stck_code) as stockcode,k.name as stockname,E.dept_id,E.dept_code,
|
E.saleOrderqty,E.qty,M.plan_qty,A.good_qty,isnull(A.inhouseqty,0) as inhouseqty,A.good_qty-isnull(A.inhouseqty,0) as stinhouseqty,M.lm_date,A.style,
|
(case when E.voucherdate is null then M.lm_date else E.voucherdate end) as voucherdate
|
from TK_Wrk_Record A
|
inner join TK_Wrk_Step S on A.wo_code=S.wo_code and A.step_code=S.step_code
|
inner join TK_Wrk_Man M on S.wo_code=M.wo_code
|
inner join TMateriel_Info P on M.materiel_code=P.partcode
|
left join TKimp_Ewo E on M.m_po=E.wo and M.sourceid= E.id
|
left join TSecStck K on COALESCE(P.idwarehouse, E.stck_code)=K.code
|
left join TStep T on A.step_code=T.stepcode
|
left join TOrganization O on M.wkshp_code=O.torg_code
|
where A.style='B' and A.inbarcode<>'' and S.isend='Y' and A.good_qty>0
|
union all
|
select A.inbarcode,E.saleOrderid,isnull(E.saleOrderCode,'') as saleOrderCode,E.saleOrderDetailId,E.woid as mpoid,M.m_po,E.sbid,M.id as wo_id,A.wo_code,E.materiel_id,P.partcode,P.partname,P.partspec,E.unitid,E.unitcode,E.unitname,
|
A.step_code,T.stepname,M.wkshp_code,O.torg_name as wkshp_name,COALESCE(K.noid, E.stck_id) as stockid,COALESCE(K.code, E.stck_code) as stockcode,k.name as stockname,E.dept_id,E.dept_code,
|
E.saleOrderqty,E.qty,M.plan_qty,A.sqty as sqty,isnull(A.inhouseqty,0) as inhouseqty,A.sqty-isnull(A.inhouseqty,0) as stinhouseqty,M.lm_date,A.style,
|
(case when E.voucherdate is null then M.lm_date else E.voucherdate end) as voucherdate
|
from TK_Wrk_OutRecord A
|
inner join TK_Wrk_Step S on A.wo_code=S.wo_code and A.step_code=S.step_code
|
inner join TK_Wrk_Man M on S.wo_code=M.wo_code
|
inner join TMateriel_Info P on M.materiel_code=P.partcode
|
left join TKimp_Ewo E on M.m_po=E.wo and M.sourceid= E.id
|
left join TSecStck K on COALESCE(P.idwarehouse, E.stck_code)=K.code
|
left join TStep T on A.step_code=T.stepcode
|
left join TOrganization O on M.wkshp_code=O.torg_code
|
where A.style='S' and A.inbarcode<>'' and S.isend='Y' and A.sqty>0
|
) as AA where AA.good_qty>AA.inhouseqty " + search;
|
var data = DapperHelper.selectdata(sql, dynamicParams);
|
mes.code = "200";
|
mes.message = "查询成功!";
|
mes.count = total;
|
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 ProductInHouseOrderCodeSearch(string rightcode)
|
{
|
try
|
{
|
mes = SeachEncodeJob.EncodingSeach(rightcode);
|
return mes;
|
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[MES生产入库提交-存储过程版]
|
public static ToMessage InHouseOrderSpSeave(InReptModel data, User us)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//存储过程名
|
sql = @"h_p_IFCLD_InProductOrder";
|
dynamicParams.Add("@RecordMin", dbType: DbType.Object, value: data.TableData[0]);
|
dynamicParams.Add("@RecordSub", dbType: DbType.Object, value: data.TableData[1]);
|
dynamicParams.Add("@rightcode", data.rightcode);
|
dynamicParams.Add("@incbit", data.incbit);
|
dynamicParams.Add("@username", us.usercode);
|
// 添加输出参数
|
dynamicParams.Add("@StatusCode", dbType: DbType.Int32, direction: ParameterDirection.Output);
|
dynamicParams.Add("@Message", dbType: DbType.String, size: 255, direction: ParameterDirection.Output);
|
bool a = DapperHelper.IsProcedure(sql, dynamicParams);
|
// 获取输出参数的值
|
var statusCode = dynamicParams.Get<int>("@StatusCode");
|
var message = dynamicParams.Get<string>("@Message");
|
if (a)
|
{
|
mes.code = statusCode.ToString();
|
mes.count = 0;
|
mes.message = message;
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = statusCode.ToString();
|
mes.count = 0;
|
mes.message = message;
|
mes.data = null;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
|
#region[畅捷通T+生产入库提交-存储过程版(适用同一台服务器，同一个数据库)]
|
public static ToMessage TProductInHouseOrderSpSeave(InReptModel data, User us)
|
{
|
var sql = "";
|
var dynamicParams = new DynamicParameters();
|
try
|
{
|
//存储过程名
|
sql = @"h_p_IFCLD_TCloudInProductOrder";
|
dynamicParams.Add("@RecordMin", dbType: DbType.Object, value: data.TableData[0]);
|
dynamicParams.Add("@RecordSub", dbType: DbType.Object, value: data.TableData[1]);
|
dynamicParams.Add("@rightcode", data.rightcode);
|
dynamicParams.Add("@incbit", data.incbit);
|
dynamicParams.Add("@username", us.usercode);
|
// 添加输出参数
|
dynamicParams.Add("@StatusCode", dbType: DbType.Int32, direction: ParameterDirection.Output);
|
dynamicParams.Add("@Message", dbType: DbType.String, size: 255, direction: ParameterDirection.Output);
|
bool a = DapperHelper.IsProcedure(sql, dynamicParams);
|
// 获取输出参数的值
|
var statusCode = dynamicParams.Get<int>("@StatusCode");
|
var message = dynamicParams.Get<string>("@Message");
|
if (a)
|
{
|
mes.code = statusCode.ToString();
|
mes.count = 0;
|
mes.message = message;
|
mes.data = null;
|
}
|
else
|
{
|
mes.code = statusCode.ToString();
|
mes.count = 0;
|
mes.message = message;
|
mes.data = null;
|
}
|
}
|
catch (Exception e)
|
{
|
mes.code = "300";
|
mes.count = 0;
|
mes.message = e.Message;
|
mes.data = null;
|
}
|
return mes;
|
}
|
#endregion
|
}
|
}
|
