From 376bb6cf439eaa3b2867825fe22a14e381dd9213 Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期三, 14 八月 2024 17:55:48 +0800
Subject: [PATCH] 1.新增工序找设备(工位)接口 2.新增物料清单查询、新增、修改接口
---
VueWebCoreApi/DLL/DAL/ProductModelDAL.cs | 699 +++++++++++++++++++++++++++++++++++++++++++++++++++++----
1 files changed, 646 insertions(+), 53 deletions(-)
diff --git a/VueWebCoreApi/DLL/DAL/ProductModelDAL.cs b/VueWebCoreApi/DLL/DAL/ProductModelDAL.cs
index 368e313..aff4bdf 100644
--- a/VueWebCoreApi/DLL/DAL/ProductModelDAL.cs
+++ b/VueWebCoreApi/DLL/DAL/ProductModelDAL.cs
@@ -8,6 +8,7 @@
using System.Linq;
using System.Threading.Tasks;
using VueWebCoreApi.Models;
+using VueWebCoreApi.Models.Bom;
using VueWebCoreApi.Models.UnitMaterial;
using VueWebCoreApi.Tools;
@@ -21,6 +22,7 @@
public static string strProcName = ""; //瀹氫箟鍏ㄥ眬sql鍙橀噺
public static List<SqlParameter> listStr = new List<SqlParameter>(); //瀹氫箟鍏ㄥ眬鍙傛暟闆嗗悎
public static SqlParameter[] parameters; //瀹氫箟鍏ㄥ眬SqlParameter鍙傛暟鏁扮粍
+ public static string sqlServerConnectString = AppSetting.GetAppSetting("DBServer");
#region[璁￠噺鍗曚綅缁勪俊鎭痌
@@ -228,7 +230,7 @@
if (aa)
{
//鍐欏叆鎿嶄綔璁板綍琛�
- LogHelper.DbOperateLog(us.usercode, "鏂板", "鏂板浜嗚閲忓崟浣嶏細"+ tunittype == "S" ? ((List<string>)mes.data)[0].ToString() : string.Join(",", listjson[0].children.Select(m => m.unitcode)), us.usertype);
+ LogHelper.DbOperateLog(us.usercode, "鏂板", "鏂板浜嗚閲忓崟浣嶏細" + tunittype == "S" ? ((List<string>)mes.data)[0].ToString() : string.Join(",", listjson[0].children.Select(m => m.unitcode)), us.usertype);
mes.code = "200";
mes.count = 0;
mes.message = "鏂板鎿嶄綔鎴愬姛!";
@@ -475,7 +477,7 @@
if (aa)
{
//鍐欏叆鎿嶄綔璁板綍琛�
- LogHelper.DbOperateLog(us.usercode, "鏂板", "鏂板浜嗚閲忓崟浣嶏細" + tunittype == "S" ? tunitcode : tunittype == "M"&& (tunitgroupcode != "" || tunitgroupcode != null) && (tunitcode == "" || tunitcode == null)? tunitgroupcode: tunitgroupcode+"-->"+tunitcode, us.usertype);
+ LogHelper.DbOperateLog(us.usercode, "鏂板", "鏂板浜嗚閲忓崟浣嶏細" + tunittype == "S" ? tunitcode : tunittype == "M" && (tunitgroupcode != "" || tunitgroupcode != null) && (tunitcode == "" || tunitcode == null) ? tunitgroupcode : tunitgroupcode + "-->" + tunitcode, us.usertype);
mes.code = "200";
mes.count = 0;
mes.message = "鍒犻櫎鎿嶄綔鎴愬姛!";
@@ -594,7 +596,7 @@
if (aa)
{
//鍐欏叆鎿嶄綔璁板綍琛�
- LogHelper.DbOperateLog(us.usercode, "鏂板", "鏂板浜嗗瓨璐у垎绫�:"+ inventoryclasscode, us.usertype);
+ LogHelper.DbOperateLog(us.usercode, "鏂板", "鏂板浜嗗瓨璐у垎绫�:" + inventoryclasscode, us.usertype);
mes.code = "200";
mes.count = 0;
mes.message = "鏂板鎿嶄綔鎴愬姛!";
@@ -642,7 +644,7 @@
if (aa)
{
//鍐欏叆鎿嶄綔璁板綍琛�
- LogHelper.DbOperateLog(us.usercode, "淇敼", "淇敼浜嗗瓨璐у垎绫�:"+ inventoryclasscode, us.usertype);
+ LogHelper.DbOperateLog(us.usercode, "淇敼", "淇敼浜嗗瓨璐у垎绫�:" + inventoryclasscode, us.usertype);
mes.code = "200";
mes.count = 0;
mes.message = "淇敼鎿嶄綔鎴愬姛!";
@@ -703,7 +705,7 @@
if (aa)
{
//鍐欏叆鎿嶄綔璁板綍琛�
- LogHelper.DbOperateLog(us.usercode, "鍒犻櫎", "鍒犻櫎浜嗗瓨璐у垎绫�"+classcode, us.usertype);
+ LogHelper.DbOperateLog(us.usercode, "鍒犻櫎", "鍒犻櫎浜嗗瓨璐у垎绫�" + classcode, us.usertype);
mes.code = "200";
mes.count = 0;
mes.message = "鍒犻櫎鎿嶄綔鎴愬姛!";
@@ -740,42 +742,58 @@
if (inventoryclasscode != "" && inventoryclasscode != null)
{
string[] classcode = Array.ConvertAll<string, string>(inventoryclasscode.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString()); //string鍒嗗壊杞瑂tring[]
- search += "and M.idinventoryclass in @classcode ";
+ search += "and AA.idinventoryclass in @classcode ";
dynamicParams.Add("@classcode", classcode);
}
if (partcode != "" && partcode != null)
{
- search += "and M.partcode like '%'+@partcode+'%' ";
+ search += "and AA.partcode like '%'+@partcode+'%' ";
dynamicParams.Add("@partcode", partcode);
}
if (partname != "" && partname != null)
{
- search += "and M.partname like '%'+@partname+'%' ";
+ search += "and AA.partname like '%'+@partname+'%' ";
dynamicParams.Add("@partname", partname);
}
if (partspec != "" && partspec != null)
{
- search += "and M.partspec like '%'+@partspec+'%' ";
+ search += "and AA.partspec like '%'+@partspec+'%' ";
dynamicParams.Add("@partspec", partspec);
}
- sql = @"select M.partcode,M.partname,M.partspec,M.idunit as idunitcode,T.name as idunitname,M.idunitgroup as idunitgroupcode,G.name as idunitgroupname,
- M.isSingleUnit,M.idSubUnitByReport as idSubUnitByReportcode,R.name as idSubUnitByReportname,M.idUnitByStock as idUnitByStockcode,K.name as idUnitByStockname,
- M.idUnitByPurchase as idUnitByPurchasecode,P.name as idUnitByPurchasename,M.idUnitBySale as idUnitBySalecode,S.name as idUnitBySalename,
- M.idunitbymanufacture as idunitbymanufacturecode,F.name as idunitbymanufacturename,M.idinventoryclass as idinventoryclasscode,C.name as idinventoryclassname,
- M.isPurchase,M.isSale,M.isMadeSelf,M.isMaterial,M.isMadeRequest,M.idwarehouse as idwarehousecode,H.name as idwarehousename,M.lowQuantity,M.topQuantity,M.safeQuantity,
- M.status,U.username as lm_user,M.lm_date,M.data_sources,M.is_retdproc,M.default_route
- from TMateriel_Info M
- left join TUnit T on M.idunit=T.code
- left join TUnit R on M.idSubUnitByReport=R.code
- left join TUnit K on M.idUnitByStock=K.code
- left join TUnit P on M.idUnitByPurchase=P.code
- left join TUnit S on M.idUnitBySale=S.code
- left join TUnit F on M.idunitbymanufacture=F.code
- left join TMateriel_Class C on M.idinventoryclass=C.code
- left join TUnitGroup G on M.idunitgroup=G.code
- left join TSecStck H on M.idwarehouse=H.code
- left join TUser U on M.lm_user=U.usercode
- where 1=1 " + search;
+ sql = @"select * from (
+ select
+ M.partcode,M.partname,M.partspec,M.idinventoryclass,M.idunit as idunitcode,T.name as idunitname,M.idunitgroup as idunitgroupcode,G.name as idunitgroupname,
+ M.isSingleUnit,M.idSubUnitByReport as idSubUnitByReportcode,R.name as idSubUnitByReportname,M.idUnitByStock as idUnitByStockcode,K.name as idUnitByStockname,
+ M.idUnitByPurchase as idUnitByPurchasecode,P.name as idUnitByPurchasename,M.idUnitBySale as idUnitBySalecode,S.name as idUnitBySalename,
+ M.idunitbymanufacture as idunitbymanufacturecode,F.name as idunitbymanufacturename,M.idinventoryclass as idinventoryclasscode,C.name as idinventoryclassname,
+ M.isPurchase,M.isSale,M.isMadeSelf,M.isMaterial,M.isMadeRequest,M.idwarehouse as idwarehousecode,H.name as idwarehousename,M.lowQuantity,M.topQuantity,M.safeQuantity,
+ M.status,U.username as lm_user,M.lm_date,M.data_sources,M.is_retdproc,M.default_route
+ from TMateriel_Info M
+ left join TUnit T on M.idunit=T.code
+ left join TUnit R on M.idSubUnitByReport=R.code
+ left join TUnit K on M.idUnitByStock=K.code
+ left join TUnit P on M.idUnitByPurchase=P.code
+ left join TUnit S on M.idUnitBySale=S.code
+ left join TUnit F on M.idunitbymanufacture=F.code
+ left join TMateriel_Class C on M.idinventoryclass=C.code
+ left join TUnitGroup G on M.idunitgroup=G.code
+ left join TSecStck H on M.idwarehouse=H.code
+ left join TUser U on M.lm_user=U.usercode
+ where (M.isSingleUnit = '1' and (T.isSingleUnit = '1' OR T.isSingleUnit IS NULL))
+ or (M.isSingleUnit = '0' AND(
+ (T.isSingleUnit = '0' AND M.idunitgroup = T.idunitgroup)
+ OR
+ (R.isSingleUnit = '0' AND M.idunitgroup = R.idunitgroup)
+ OR
+ (K.isSingleUnit = '0' AND M.idunitgroup = K.idunitgroup)
+ OR
+ (P.isSingleUnit = '0' AND M.idunitgroup = P.idunitgroup)
+ OR
+ (S.isSingleUnit = '0' AND M.idunitgroup = S.idunitgroup)
+ OR
+ (F.isSingleUnit = '0' AND M.idunitgroup = F.idunitgroup)
+ ))
+ ) as AA where 1=1" + search;
// --------------鏌ヨ鎸囧畾鏁版嵁--------------
var total = 0; //鎬绘潯鏁�
data = DapperHelper.GetPageList<object>(sql, dynamicParams, prop, order, startNum, endNum, out total);
@@ -851,25 +869,25 @@
str = sql,
parm = new
{
- partcode=partcode,
- partname=partname,
- partspec=partspec,
- idunit=unittypcode=="1"?unitcode:unitsubcode,
- idunitgroup = unittypcode == "0"?"1":"0",
- isSingleUnit= unittypcode,
- idSubUnitByReport=idSubUnitByReport,
- idUnitByStock= idUnitByStock,
- idUnitByPurchase= idUnitByPurchase,
- idUnitBySale= idUnitBySale,
- idunitbymanufacture= idunitbymanufacture,
- idinventoryclass= inventoryclasscode,
- isPurchase= isPurchase,
- isSale= isSale,
- isMadeSelf= isMadeSelf,
- isMaterial= isMaterial,
- isMadeRequest= isMadeRequest,
- idwarehouse= idwarehouse,
- status= status,
+ partcode = partcode,
+ partname = partname,
+ partspec = partspec,
+ idunit = unittypcode == "1" ? unitcode : unitsubcode,
+ idunitgroup = unittypcode == "0" ? "1" : "0",
+ isSingleUnit = unittypcode,
+ idSubUnitByReport = idSubUnitByReport,
+ idUnitByStock = idUnitByStock,
+ idUnitByPurchase = idUnitByPurchase,
+ idUnitBySale = idUnitBySale,
+ idunitbymanufacture = idunitbymanufacture,
+ idinventoryclass = inventoryclasscode,
+ isPurchase = isPurchase,
+ isSale = isSale,
+ isMadeSelf = isMadeSelf,
+ isMaterial = isMaterial,
+ isMadeRequest = isMadeRequest,
+ idwarehouse = idwarehouse,
+ status = status,
lm_user = us.usercode,
lm_date = DateTime.Now.ToString(),
data_sources = data_sources
@@ -879,7 +897,7 @@
if (aa)
{
//鍐欏叆鎿嶄綔璁板綍琛�
- LogHelper.DbOperateLog(us.usercode, "鏂板", "鏂板浜嗗瓨璐ф。妗�:"+ partcode, us.usertype);
+ LogHelper.DbOperateLog(us.usercode, "鏂板", "鏂板浜嗗瓨璐ф。妗�:" + partcode, us.usertype);
mes.code = "200";
mes.count = 0;
mes.message = "鏂板鎿嶄綔鎴愬姛!";
@@ -957,7 +975,7 @@
if (aa)
{
//鍐欏叆鎿嶄綔璁板綍琛�
- LogHelper.DbOperateLog(us.usercode, "淇敼", "淇敼浜嗗瓨璐ф。妗�:"+ partcode, us.usertype);
+ LogHelper.DbOperateLog(us.usercode, "淇敼", "淇敼浜嗗瓨璐ф。妗�:" + partcode, us.usertype);
mes.code = "200";
mes.count = 0;
mes.message = "淇敼鎿嶄綔鎴愬姛!";
@@ -1023,14 +1041,14 @@
parm = new
{
partcode = partcode,
- data_sources= data_sources
+ data_sources = data_sources
}
});
bool aa = DapperHelper.DoTransaction(list);
if (aa)
{
//鍐欏叆鎿嶄綔璁板綍琛�
- LogHelper.DbOperateLog(us.usercode, "鍒犻櫎", "鍒犻櫎浜嗗瓨璐ф。妗�:"+partcode, us.usertype);
+ LogHelper.DbOperateLog(us.usercode, "鍒犻櫎", "鍒犻櫎浜嗗瓨璐ф。妗�:" + partcode, us.usertype);
mes.code = "200";
mes.count = 0;
mes.message = "鍒犻櫎鎿嶄綔鎴愬姛!";
@@ -1056,7 +1074,7 @@
#endregion
#region[瀛樿揣妗ｆ鍏宠仈宸ヨ壓(宸ヨ壓璺嚎鎴栧伐搴�)鏌ヨ]
- public static ToMessage TMaterielFileAssociationRoute(string partcode,User us)
+ public static ToMessage TMaterielFileAssociationRoute(string partcode, User us)
{
string sql = "";
var dynamicParams = new DynamicParameters();
@@ -1176,7 +1194,7 @@
//string鍒嗗壊杞瑂tring[]
string[] routcode = Array.ConvertAll<string, string>(string.Join(",", json[0].children.Select(s => s.code)).Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries), s => s.ToString());
sql = @"delete TPrteStep_Price where materiel_code=@partcode and route_code not in @routecode";
- list.Add(new { str = sql, parm = new { partcode = json[0].partcode, routecode= routcode } });
+ list.Add(new { str = sql, parm = new { partcode = json[0].partcode, routecode = routcode } });
}
}
else //宸ュ簭鐗�
@@ -1233,7 +1251,7 @@
if (aa)
{
//鍐欏叆鎿嶄綔璁板綍琛�
- LogHelper.DbOperateLog(us.usercode, "瀛樿揣鍏宠仈宸ヨ壓", "浜у搧锛�"+json[0].partcode+"鍏宠仈浜嗗伐鑹�:" + string.Join(",", json[0].children.Select(s => s.code)), us.usertype);
+ LogHelper.DbOperateLog(us.usercode, "瀛樿揣鍏宠仈宸ヨ壓", "浜у搧锛�" + json[0].partcode + "鍏宠仈浜嗗伐鑹�:" + string.Join(",", json[0].children.Select(s => s.code)), us.usertype);
mes.code = "200";
mes.count = 0;
mes.message = "鎿嶄綔鎴愬姛!";
@@ -1259,6 +1277,581 @@
#endregion
+ #region[鐗╂枡娓呭崟淇℃伅鍒楄〃]
+ public static ToMessage BomMainData(string parentpartcode, string parentpartname, string parentpartspec, string version, string createuser, string operopendate, string operclosedate, string status, int startNum, int endNum, string prop, string order)
+ {
+ var dynamicParams = new DynamicParameters();
+ List<BomDate> parents = new List<BomDate>();
+ string search = "", sql = "";
+ try
+ {
+ if (parentpartcode != "" && parentpartcode != null)
+ {
+ search += "and AA.materiel_code like '%'+@parentpartcode+'%' ";
+ dynamicParams.Add("@parentpartcode", parentpartcode);
+ }
+ if (parentpartname != "" && parentpartname != null)
+ {
+ search += "and AA.materiel_name like '%'+@parentpartname+'%' ";
+ dynamicParams.Add("@parentpartname", parentpartname);
+ }
+ if (parentpartspec != "" && parentpartspec != null)
+ {
+ search += "and AA.materiel_spec like '%'+@parentpartspec+'%' ";
+ dynamicParams.Add("@parentpartspec", parentpartspec);
+ }
+ if (version != "" && version != null)
+ {
+ search += "and AA.version like '%'+@version+'%' ";
+ dynamicParams.Add("@version", version);
+ }
+ if (createuser != "" && createuser != null)
+ {
+ search += "and AA.lm_user like '%'+@createuser+'%' ";
+ dynamicParams.Add("@createuser", createuser);
+ }
+ if (operopendate != "" && operopendate != null)
+ {
+ search += "and AA.lm_date between @operopendate and @operclosedate ";
+ dynamicParams.Add("@operopendate", operopendate + " 00:00:00");
+ dynamicParams.Add("@operclosedate", operclosedate + " 23:59:59");
+ }
+ var total = 0; //鎬绘潯鏁�
+ //鏌ヨBom涓昏〃淇℃伅
+ sql = @"select * from(
+ select B.id,B.materiel_code,M.partname as materiel_name,M.partspec as materiel_spec,
+ M.idinventoryclass as materiel_typecode,C.name as materiel_typename,M.idunit as unitcode,T.name as unitname,
+ B.quantity,B.status,B.startdate,B.version,B.isdefaultbom,B.bomdepth,U.username as lm_user,B.lm_date
+ from TBom_Main B
+ left join TMateriel_Info M on B.materiel_code=M.partcode
+ left join TUnit T on M.idunit=T.code
+ left join TMateriel_Class C on M.idinventoryclass=C.code
+ left join TUser U on B.lm_user=U.usercode
+ where (M.isSingleUnit = '1' and (T.isSingleUnit = '1' OR T.isSingleUnit IS NULL))
+ or (M.isSingleUnit = '0' AND(
+ (T.isSingleUnit = '0' AND M.idunitgroup = T.idunitgroup)
+ ))
+ ) as AA " + search;
+ parents = DapperHelper.GetPageListData<BomDate>(sql, dynamicParams, prop, order, startNum, endNum, out total);
+ for (int i = 0; i < parents.Count; i++)
+ {
+ //閫氳繃Bom涓昏〃id鏌ユ壘瀛愪欢淇℃伅
+ sql = @"select * from(
+ select S.id,S.m_id,S.seq,S.materiel_code,M.partname as materiel_name,M.partspec as materiel_spec,
+ M.idunit as unitcode,T.name as unitname,S.base_quantity,S.loss_quantity,S.total_quantity,S.idchildbom
+ from TBom_Deta S
+ left join TMateriel_Info M on S.materiel_code=M.partcode
+ left join TUnit T on M.idunit=T.code
+ where (M.isSingleUnit = '1' and (T.isSingleUnit = '1' OR T.isSingleUnit IS NULL))
+ or (M.isSingleUnit = '0' AND(
+ (T.isSingleUnit = '0' AND M.idunitgroup = T.idunitgroup)
+ ))
+ ) as AA where AA.m_id=@m_id";
+ dynamicParams.Add("@m_id", parents[i].id);
+ var children = DapperHelper.select<BomDateSub>(sql, dynamicParams);
+ parents[i].children = children.ToList();
+ }
+ mes.code = "200";
+ mes.message = "鏌ヨ鎴愬姛!";
+ mes.data = parents;
+ }
+ catch (Exception e)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.message = e.Message;
+ mes.data = null;
+ }
+ return mes;
+ }
+ #endregion
+
+ #region[鐗╂枡娓呭崟鏂板淇敼鑾峰彇鐖朵欢淇℃伅-T+鐣呮嵎閫氭ā寮廬
+ public static ToMessage AddUpdateTBomParentData(User us)
+ {
+ string sql = "";
+ var dynamicParams = new DynamicParameters();
+ try
+ {
+ //鏍规嵁瀛樿揣淇℃伅,鏌ユ壘鑷埗灞炴�ф垨濮斿灞炴�х墿鏂�
+ sql = @"select * from (
+ select M.partcode,M.partname,M.partspec,M.idunit as unitcode,U.name as unitname,M.isMadeSelf,M.isMadeRequest,
+ (case when M.idunitgroup='0' then NUll else M.idunitgroup end) as idunitgroup
+ from TMateriel_Info M
+ left join TUnit U on M.idunitgroup=U.idunitgroup and M.idunit=U.code and M.IsSingleUnit=U.IsSingleUnit
+ where M.isSingleUnit='0' and U.IsMainUnit='1' and M.Status='0'
+ ) as AA where AA.isMadeSelf='1' or AA.isMadeRequest='1'
+ union all
+ select * from (
+ select M.partcode,M.partname,M.partspec,M.idunit as unitcode,U.name as unitname,M.isMadeSelf,M.isMadeRequest,
+ (case when M.idunitgroup='0' then NUll else M.idunitgroup end) as idunitgroup
+ from TMateriel_Info M
+ inner join TUnit U on M.idunit=U.code and M.IsSingleUnit=U.IsSingleUnit
+ where M.IsSingleUnit='1' and U.IsMainUnit='0' and M.Status='0'
+ ) as AA where AA.isMadeSelf='1' or AA.isMadeRequest='1'";
+ var data = DapperHelper.selecttable(sql);
+ mes.code = "200";
+ mes.message = "鏌ヨ鎴愬姛!";
+ mes.data = data;
+ }
+ catch (Exception e)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.message = e.Message;
+ mes.data = null;
+ }
+ return mes;
+ }
+ #endregion
+
+ #region[鐗╂枡娓呭崟鏂板淇敼鏍规嵁鐖朵欢淇℃伅鐨勮閲忓崟浣嶇粍缂栫爜鏌ユ壘璁￠噺鍗曚綅缁勪俊鎭�-T+鐣呮嵎閫氭ā寮廬
+ public static ToMessage AddUpdateTBomUnitGroup(string idunitgroup)
+ {
+ var dynamicParams = new DynamicParameters();
+ string sql = "";
+ try
+ {
+ //鑾峰彇璁￠噺鍗曚綅缁勪俊鎭�
+ sql = @"select code as unitcode,name as unitname
+ from TUnit where idunitgroup=@idunitgroup";
+ dynamicParams.Add("@idunitgroup", idunitgroup);
+ 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 AddUpdateTBomMaxVsion(string parentpartcode)
+ {
+ string sql = "";
+ var dynamicParams = new DynamicParameters();
+ try
+ {
+ sql = @"select isnull(max(substring(version,charindex('V',version)+1,len(version)-charindex('V',version))),0)+1 as version
+ from TBom_Main where materiel_code=@parentpartcode";
+ dynamicParams.Add("@parentpartcode", parentpartcode);
+ var data = DapperHelper.selectdata(sql, dynamicParams);
+ if (data.Rows.Count > 0)
+ {
+ mes.code = "200";
+ mes.message = "鑾峰彇鐗堟湰鍙锋垚鍔�!";
+ mes.data = "V" + data.Rows[0]["version"].ToString();
+ }
+ else
+ {
+ mes.code = "300";
+ mes.message = "鑾峰彇鐗堟湰鍙峰け璐�!";
+ mes.data = null;
+ }
+
+ }
+ catch (Exception e)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.message = e.Message;
+ mes.data = null;
+ }
+ return mes;
+ }
+ #endregion
+
+ #region[鐗╂枡娓呭崟鏂板淇敼鑾峰彇瀛愪欢淇℃伅-T+鐣呮嵎閫氭ā寮廬
+ public static ToMessage AddUpdateTBomDetailsData(User us)
+ {
+ string sql = "";
+ var dynamicParams = new DynamicParameters();
+ try
+ {
+ //鏍规嵁瀛樿揣淇℃伅,鏌ユ壘鐢熶骇鑰楃敤鐨勭墿鏂�
+ sql = @"select * from (
+ select M.partcode,M.partname,M.partspec,M.idunit as unitcode,U.name as unitname,M.IsMaterial,
+ (case when M.idunitgroup='0' then NUll else M.idunitgroup end) as idunitgroup
+ from TMateriel_Info M
+ left join TUnit U on M.idunitgroup=U.idunitgroup and M.idunit=U.code and M.IsSingleUnit=U.IsSingleUnit
+ where M.isSingleUnit='0' and U.IsMainUnit='1' and M.Status='0'
+ ) as AA where AA.IsMaterial='1'
+ union all
+ select * from (
+ select M.partcode,M.partname,M.partspec,M.idunit as unitcode,U.name as unitname,M.IsMaterial,
+ (case when M.idunitgroup='0' then NUll else M.idunitgroup end) as idunitgroup
+ from TMateriel_Info M
+ inner join TUnit U on M.idunit=U.code and M.IsSingleUnit=U.IsSingleUnit
+ where M.IsSingleUnit='1' and U.IsMainUnit='0' and M.Status='0'
+ ) as AA where AA.IsMaterial='1' ";
+ var data = DapperHelper.selecttable(sql);
+ mes.code = "200";
+ mes.message = "鏌ヨ鎴愬姛!";
+ mes.data = data;
+ }
+ catch (Exception e)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.message = e.Message;
+ mes.data = null;
+ }
+ return mes;
+ }
+ #endregion
+
+ #region[鐗╂枡娓呭崟鏂板淇敼鑾峰彇瀛愪欢Bom-T+鐣呮嵎閫氭ā寮廬
+ public static ToMessage AddUpdateTBomDetailsBomData(string materiel_code, User us)
+ {
+ string sql = "";
+ var dynamicParams = new DynamicParameters();
+ try
+ {
+ //鏍规嵁瀛愪欢鐗╂枡缂栫爜鏌ヨ瀛愪欢瀵瑰簲鐨凚om淇℃伅
+ sql = @"select B.id,B.materiel_code,M.partname as materiel_name,M.partspec as materiel_spec,B.version
+ from TBom_Main B
+ left join TMateriel_Info M on B.materiel_code=M.partcode
+ where B.materiel_code=@materiel_code";
+ dynamicParams.Add("@materiel_code", materiel_code);
+ 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[鐗╂枡娓呭崟鏂板淇敼鎻愪氦-T+鐣呮嵎閫氭ā寮廬
+ public static ToMessage TBomAddUpdate(string operType, BomDate bom, User us)
+ {
+ string sql = "";
+ List<object> list = new List<object>();
+ var dynamicParams = new DynamicParameters();
+ using (var conn = new SqlConnection(sqlServerConnectString))
+ {
+ conn.Open();
+ using (var transaction = conn.BeginTransaction())
+ {
+ try
+ {
+ if (operType == "Add")
+ {
+ // 鍋囪浣犲凡缁忔湁浜嗘彃鍏ヤ富琛ㄥ拰瀛愯〃鐨凷QL璇彞
+ string masterUpdateversionSql = @"update TBom_Main set isdefaultbom='0' where materiel_code=@materiel_code";
+ string masterInsertSql = @"insert into TBom_Main(materiel_code,unitcode,bomdepth,isdefaultbom,quantity,status,startdate,version,lm_user,lm_date)
+ values(@materiel_code,@unitcode,@bomdepth,@isdefaultbom,@quantity,@status,@startdate,@version,@lm_user,@lm_date); SELECT CAST(SCOPE_IDENTITY() AS INT)";
+ string detailInsertSql = @"insert into TBom_Deta(m_id,seq,materiel_code,unitcode,base_quantity,loss_quantity,total_quantity,idchildbom)
+ values(@m_id,@seq,@materiel_code,@unitcode,@base_quantity,@loss_quantity,@total_quantity,@idchildbom)";
+ string detailUpdateSql = @"update TBom_Deta set idchildbom=@idchildbom where materiel_code=@materiel_code";
+
+
+ //鏌ヨ褰撳墠鐖朵欢鐗╂枡+鐗堟湰鍙锋槸鍚﹀瓨鍦�
+ sql = @"select * from TBom_Main where materiel_code=@materiel_code and version=@version";
+ dynamicParams.Add("@materiel_code", bom.materiel_code);
+ dynamicParams.Add("@Version", bom.version);
+ var data0 = DapperHelper.selectdata(sql, dynamicParams);
+ if (data0.Rows.Count > 0)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.message = "鍚屼竴鐗╂枡鐗堟湰鍙锋湁閲嶅!";
+ mes.data = null;
+ return mes;
+ }
+ //鏌ヨ褰撳墠鐖朵欢鐗╂枡鏄惁瀛樺湪
+ sql = @"select * from TBom_Main where materiel_code=@materiel_code";
+ dynamicParams.Add("@materiel_code", bom.materiel_code);
+ var data1 = DapperHelper.selectdata(sql, dynamicParams);
+ if (data1.Rows.Count > 0) //瀛樺湪褰撳墠鐗╂枡鐨勫叾浠栫増鏈珺OM
+ {
+ if (bom.isdefaultbom == "1") //榛樿Bom涓�:鏄�
+ {
+ //鏇存柊鐗╂枡娓呭崟瀵瑰簲鍏朵粬闈炴鐗堟湰鐨勬槸鍚﹂粯璁OM
+ var updateversionParam = new DynamicParameters();
+ updateversionParam.Add("@materiel_code", bom.materiel_code);
+ conn.Execute(masterUpdateversionSql, updateversionParam, transaction);
+ }
+ }
+ else
+ {
+ if (bom.isdefaultbom == "0") //榛樿Bom涓�:鍚�
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.message = "涓�涓骇鍝佸彧鑳芥湁涓�涓笖蹇呴』鏈変竴涓粯璁OM!";
+ mes.data = null;
+ return mes;
+ }
+ }
+
+ // 鎻掑叆涓昏〃骞惰幏鍙栨柊鎻掑叆鐨処D
+ var masterParam = new DynamicParameters();
+ masterParam.Add("@materiel_code", bom.materiel_code);
+ masterParam.Add("@unitcode", bom.unitcode);
+ masterParam.Add("@bomdepth", bom.bomdepth);
+ masterParam.Add("@isdefaultbom", bom.isdefaultbom);
+ masterParam.Add("@quantity", bom.quantity);
+ masterParam.Add("@status", bom.status);
+ masterParam.Add("@startdate", bom.startdate);
+ masterParam.Add("@version", bom.version);
+ masterParam.Add("@lm_user", us.usercode);
+ masterParam.Add("@lm_date", DateTime.Now);
+
+ int masterId = Convert.ToInt32(conn.ExecuteScalar(masterInsertSql, masterParam, transaction));
+
+ // 鎻掑叆瀛愯〃
+ foreach (var detail in bom.children)
+ {
+ var detailParam = new DynamicParameters();
+ detailParam.Add("@m_id", masterId);
+ detailParam.Add("@seq", detail.seq);
+ detailParam.Add("@materiel_code", detail.materiel_code);
+ detailParam.Add("@unitcode", detail.unitcode);
+ detailParam.Add("@base_quantity", detail.base_quantity);
+ detailParam.Add("@loss_quantity", detail.loss_quantity);
+ detailParam.Add("@total_quantity", detail.total_quantity);
+ detailParam.Add("@idchildbom", string.IsNullOrEmpty(detail.idchildbom) ? null : detail.idchildbom);
+ conn.Execute(detailInsertSql, detailParam, transaction);
+ }
+ //鏇存柊瀛愯〃idchildbom
+ var detailUpdateParam = new DynamicParameters();
+ detailUpdateParam.Add("@idchildbom", masterId);
+ detailUpdateParam.Add("@materiel_code", bom.materiel_code);
+ conn.Execute(detailUpdateSql, detailUpdateParam, transaction);
+
+ // 鎵�鏈夋彃鍏ユ垚鍔燂紝鎻愪氦浜嬪姟
+ transaction.Commit();
+ //鏇存柊Bom灞傜骇
+ var bomdepthParam = new DynamicParameters();
+ bomdepthParam.Add("@bomId", masterId);
+ bomdepthParam.Add("@isAllFlag", 1);
+ var dt = DapperHelper.selectProcedure("AA_SP_UpdateBomDepth", bomdepthParam);
+ mes.code = "200";
+ mes.count = 0;
+ mes.message = "鏂板鎿嶄綔鎴愬姛!";
+ mes.data = null;
+ //鍐欏叆鎿嶄綔璁板綍琛�
+ LogHelper.DbOperateLog(us.usercode, "鏂板", "鏂板浜嗙墿鏂欐竻鍗曪細" + bom.materiel_code + "锛岀増鏈彿涓�:" + bom.version, us.usertype);
+ }
+ if (operType == "Update")
+ {
+ // 鍋囪浣犲凡缁忔湁浜嗘彃鍏ョ殑SQL璇彞
+ string masterUpdateversionSql = @"update TBom_Main set isdefaultbom='0' where materiel_code=@materiel_code and id<>@id";
+ string detailInsertSql = @"insert into TBom_Deta(m_id,seq,materiel_code,unitcode,base_quantity,loss_quantity,total_quantity,idchildbom)
+ values(@m_id,@seq,@materiel_code,@unitcode,@base_quantity,@loss_quantity,@total_quantity,@idchildbom)";
+ string detailUpdateSql = @"update TBom_Deta set seq=@seq,materiel_code=@materiel_code,base_quantity=@base_quantity,loss_quantity=@loss_quantity,
+ total_quantity=@total_quantity,unitcode=@unitcode,idchildbom=@idchildbom where materiel_code=@materiel_code and m_id=@m_id";
+ string detailDeleteSql = @"delete TBom_Deta where materiel_code=@materiel_code and m_id=@m_id";
+ string masterUpdateSql = @"update TBom_Main set materiel_code=@materiel_code,unitcode=@unitcode,quantity=@quantity,status=@status,startdate=@startdate,
+ version=@version,isdefaultbom=@isdefaultbom,lm_user=@lm_user,lm_date=@lm_date where materiel_code=@materiel_code and id=@ID";
+
+ //鏌ヨ褰撳墠鐖朵欢鐗╂枡+鐗堟湰鍙锋槸鍚﹀瓨鍦�
+ sql = @"select * from TBom_Main where materiel_code=@materiel_code and version=@version and id=@ID";
+ dynamicParams.Add("@ID", bom.id);
+ dynamicParams.Add("@materiel_code", bom.materiel_code);
+ dynamicParams.Add("@version", bom.version);
+ var data0 = DapperHelper.selectdata(sql, dynamicParams);
+ if (data0.Rows.Count > 0 && data0.Rows.Count > 1)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.message = "鍚屼竴鐗╂枡鐗堟湰鍙锋湁閲嶅!";
+ mes.data = null;
+ return mes;
+ }
+ //鏍规嵁Bomid鏌ヨ鏄惁琚伐鍗曞紩鐢�
+ sql = @"select * from TK_Wrk_Man where bom_id=@bom_id";
+ dynamicParams.Add("@bom_id", bom.id);
+ var data_1 = DapperHelper.selectdata(sql, dynamicParams);
+ if (data_1.Rows.Count > 0)
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.message = "淇敼鍓岯OM宸茬粡琚伐鍗曞紩鐢�,涓嶅彲淇敼!";
+ mes.data = null;
+ return mes;
+ }
+ //鏌ヨ褰撳墠鐖朵欢鐗╂枡闈炴鐗堟湰鏄惁瀛樺湪
+ sql = @"select * from TBom_Main where materiel_code=@materiel_code";
+ dynamicParams.Add("@materiel_code", bom.materiel_code);
+ var data1 = DapperHelper.selectdata(sql, dynamicParams);
+ if (data1.Rows.Count > 0) //瀛樺湪褰撳墠鐗╂枡鐨勫叾浠栫増鏈珺OM
+ {
+ // 浣跨敤LINQ鏌ヨDataTable
+ var BomDefault = from row in data1.AsEnumerable()
+ where row.Field<int>("isdefaultbom") == 1
+ select row;
+
+ // 妫�鏌ユ槸鍚︽湁缁撴灉
+ if (BomDefault.Any())
+ {
+ if (bom.isdefaultbom == "1") //榛樿Bom涓�:鏄�
+ {
+ //鏇存柊鐗╂枡娓呭崟瀵瑰簲鍏朵粬闈炴鐗堟湰鐨勬槸鍚﹂粯璁OM
+ var updateversionParam = new DynamicParameters();
+ updateversionParam.Add("@materiel_code", bom.materiel_code);
+ updateversionParam.Add("@id", bom.id);
+ conn.Execute(masterUpdateversionSql, updateversionParam, transaction);
+ }
+ else
+ {
+ if (bom.isdefaultbom == "0") //榛樿Bom涓�:鏄�
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.message = "涓�涓骇鍝佸彧鑳芥湁涓�涓笖蹇呴』鏈変竴涓粯璁OM!";
+ mes.data = null;
+ return mes;
+ }
+ }
+ }
+ else
+ {
+ if (bom.isdefaultbom == "0") //榛樿Bom涓�:鏄�
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.message = "涓�涓骇鍝佸彧鑳芥湁涓�涓笖蹇呴』鏈変竴涓粯璁OM!";
+ mes.data = null;
+ return mes;
+ }
+ }
+
+ }
+ else
+ {
+ if (bom.isdefaultbom == "0") //榛樿Bom涓�:鍚�
+ {
+ mes.code = "300";
+ mes.count = 0;
+ mes.message = "涓�涓骇鍝佸彧鑳芥湁涓�涓笖蹇呴』鏈変竴涓粯璁OM!";
+ mes.data = null;
+ return mes;
+ }
+ }
+ //閫氳繃鐖朵欢id鏌ユ壘瀛愪欢淇℃伅
+ sql = @"select * from TBom_Deta where m_id=@m_id";
+ dynamicParams.Add("@m_id", bom.id);
+ var list2 = DapperHelper.select<BomDateSub>(sql, dynamicParams);
+ // 浣跨敤LINQ鐨凣roupJoin鏉ユ壘鍑哄樊寮�
+ var query = from item1 in bom.children
+ join item2 in list2 on new { item1.m_id, item1.materiel_code }
+ equals new { item2.m_id, item2.materiel_code } into gj
+ from subitem in gj.DefaultIfEmpty()
+ select new { Item1 = item1, Item2 = subitem };
+ var toAdd = query.Where(q => q.Item2 == null).Select(q => q.Item1).ToList();
+ var toUpdate = query.Where(q => q.Item2 != null && q.Item1.m_id == q.Item2.m_id && q.Item1.materiel_code == q.Item2.materiel_code).Select(q => q.Item1).ToList();
+ var toDelete = list2
+ .Where(item2 => !bom.children.Any(item1 =>
+ item1.m_id == item2.m_id &&
+ item1.materiel_code == item2.materiel_code
+ ))
+ .ToList();
+ //鏂板
+ for (int i = 0; i < toAdd.Count; i++)
+ {
+ //鍐欏叆鐗╂枡娓呭崟瀛愯〃
+ var detailParam = new DynamicParameters();
+ detailParam.Add("@m_id", toAdd[i].m_id);
+ detailParam.Add("@seq", toAdd[i].seq);
+ detailParam.Add("@materiel_code", toAdd[i].materiel_code);
+ detailParam.Add("@unitcode", toAdd[i].unitcode);
+ detailParam.Add("@base_quantity", toAdd[i].base_quantity);
+ detailParam.Add("@loss_quantity", toAdd[i].loss_quantity);
+ detailParam.Add("@total_quantity", toAdd[i].total_quantity);
+ detailParam.Add("@idchildbom", string.IsNullOrEmpty(toAdd[i].idchildbom) ? null : toAdd[i].idchildbom);
+ conn.Execute(detailInsertSql, detailParam, transaction);
+ }
+ //鏇存柊
+ for (int i = 0; i < toUpdate.Count; i++)
+ {
+ //鏇存柊鐗╂枡娓呭崟瀛愯〃
+ var detailUpdateParam = new DynamicParameters();
+ detailUpdateParam.Add("@m_id", toUpdate[i].m_id);
+ detailUpdateParam.Add("@seq", toUpdate[i].seq);
+ detailUpdateParam.Add("@materiel_code", toUpdate[i].materiel_code);
+ detailUpdateParam.Add("@unitcode", toUpdate[i].unitcode);
+ detailUpdateParam.Add("@base_quantity", toUpdate[i].base_quantity);
+ detailUpdateParam.Add("@loss_quantity", toUpdate[i].loss_quantity);
+ detailUpdateParam.Add("@total_quantity", toUpdate[i].total_quantity);
+ detailUpdateParam.Add("@idchildbom", string.IsNullOrEmpty(toUpdate[i].idchildbom) ? null : toUpdate[i].idchildbom);
+ conn.Execute(detailUpdateSql, detailUpdateParam, transaction);
+ }
+ //鍒犻櫎
+ for (int i = 0; i < toDelete.Count; i++)
+ {
+ //鍒犻櫎鐗╂枡娓呭崟瀛愯〃
+ var detailDeleteParam = new DynamicParameters();
+ detailDeleteParam.Add("@materiel_code", toDelete[i].materiel_code);
+ detailDeleteParam.Add("@m_id", toDelete[i].m_id);
+ conn.Execute(detailDeleteSql, detailDeleteParam, transaction);
+ }
+ //鏇存柊鐗╂枡娓呭崟涓昏〃
+ var masterUpdateParam = new DynamicParameters();
+ masterUpdateParam.Add("@ID", bom.id);
+ masterUpdateParam.Add("@materiel_code", bom.materiel_code);
+ masterUpdateParam.Add("@unitcode", bom.unitcode);
+ masterUpdateParam.Add("@quantity", bom.quantity);
+ masterUpdateParam.Add("@status", bom.status);
+ masterUpdateParam.Add("@startdate", bom.startdate);
+ masterUpdateParam.Add("@version", bom.version);
+ masterUpdateParam.Add("@isdefaultbom", bom.isdefaultbom);
+ masterUpdateParam.Add("@lm_user", us.usercode);
+ masterUpdateParam.Add("@lm_date", DateTime.Now);
+ conn.Execute(masterUpdateSql, masterUpdateParam, transaction);
+ // 鎵�鏈夋彃鍏ユ垚鍔燂紝鎻愪氦浜嬪姟
+ transaction.Commit();
+ //鏇存柊Bom灞傜骇
+ var bomdepthParam = new DynamicParameters();
+ bomdepthParam.Add("@bomId", bom.id);
+ bomdepthParam.Add("@isAllFlag", 1);
+ var dt = DapperHelper.selectProcedure("AA_SP_UpdateBomDepth", bomdepthParam);
+ mes.code = "200";
+ mes.count = 0;
+ mes.message = "淇敼鎿嶄綔鎴愬姛!";
+ mes.data = null;
+ //鍐欏叆鎿嶄綔璁板綍琛�
+ LogHelper.DbOperateLog(us.usercode, "淇敼", "淇敼浜嗙墿鏂欐竻鍗曪細" + bom.materiel_code + "锛岀増鏈彿涓�:" + bom.version, us.usertype);
+ }
+ }
+ catch (Exception e)
+ {
+ // 鎹曡幏寮傚父锛屽洖婊氫簨鍔�
+ transaction.Rollback();
+ mes.code = "300";
+ mes.count = 0;
+ mes.message = e.Message;
+ mes.data = null;
+ }
+ }
+ }
+ return mes;
+ }
+ #endregion
+
+ #region[鐗╂枡娓呭崟鍒犻櫎鎻愪氦-T+鐣呮嵎閫氭ā寮廬
+ public static ToMessage TBomDelete(BomDate bom, User us)
+ {
+ throw new NotImplementedException();
+ }
+ #endregion
+
+
#region[宸ュ崟浜у搧淇℃伅涓嬫媺妗嗘煡璇
public static ToMessage PartSelect()
{
--
Gitblit v1.9.3