Jjie
/
HGSRM
1
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1 Commit
1 Branch
0 Tags
399 MiB
 
 
 
 
 
Branch: master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
HGSRM/WMS-BS/NFine.Application/DHAY/ICSSDNApp.cs

756 lines
37 KiB
Raw Permalink Blame History

using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using NFine.Code;
using NFine.Data.Extensions;
using NFine.Domain._03_Entity.SRM;
using NFine.Repository;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Threading;
using System.Reflection.Emit;
using NFine.Application.WMS;
using System.Net;
using System.IO;
using System.Reflection;
namespace NFine.Application.DHAY
{
public class ICSSDNApp : RepositoryFactory<ICSVendor>
{
private static object _locker = new object();
public static DataTable Invmes = new DataTable();
PickMaterialApp App = new PickMaterialApp();
ICSAccessoriesMOIApplyApp iCSAccessoriesMOIApplyApp = new ICSAccessoriesMOIApplyApp();
#region 删除销售发货单
public string DeleteICSSDN(string keyValue)
{
//站点信息
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
string msg = "";
keyValue = keyValue.Substring(1, keyValue.Length - 2);
string sql = string.Empty;
sql += string.Format(@"If EXISTS (SELECT ID FROM ICSSDN WHERE Isnull(SDNQuantity,0)>0 AND WorkPoint = '{WorkPoint}' and SDNCode = '{data.Code}' )
BEGIN
RAISERROR('标准出库单已发料不能删除', 16, 1);
RETURN
END;
UPDATE b SET b.SOQuantity=b.SOQuantity-a.Quantity FROM ICSSO b
INNER JOIN (SELECT Sum(Quantity) Quantity,SOCode,SOSequence,WorkPoint,SDNCode FROM ICSSDN WHERE SDNCode = '{data.Code}'
GROUP BY SOCode,SOSequence,SDNCode,WorkPoint ) a ON a.SOCode=b.SOCode AND a.SOSequence=b.Sequence AND a.WorkPoint=b.WorkPoint WHERE a.SDNCode = '{data.Code}';
DELETE FROM dbo.ICSSDN WHERE SDNCode IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
try
{
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
{
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return msg;
}
#endregion
#region 修改
public string UpdateICSSDN(string ICSASN)
{
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
string UserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string msg = "";
try
{
// 获取解析后的数据
JsonData data = JsonConvert.DeserializeObject<JsonData>(ICSASN);
string sql = $@"If EXISTS (SELECT ID FROM ICSSDN WHERE Isnull(SDNQuantity,0)>0 AND WorkPoint = '{WorkPoint}' and SDNCode = '{data.Code}' )
BEGIN
RAISERROR('标准出库单已发料不能修改', 16, 1);
RETURN
END;
UPDATE b SET b.SOQuantity=b.SOQuantity-a.Quantity FROM ICSSO b
INNER JOIN (SELECT Sum(Quantity) Quantity,SOCode,SOSequence,WorkPoint,SDNCode FROM ICSSDN WHERE SDNCode = '{data.Code}'
GROUP BY SOCode,SOSequence,SDNCode,WorkPoint ) a ON a.SOCode=b.SOCode AND a.SOSequence=b.Sequence AND a.WorkPoint=b.WorkPoint WHERE a.SDNCode = '{data.Code}';
delete from ICSSDN where SDNCode = '{data.Code}'; ";
string str1 = "";
List<string> ExtensionIDList = new List<string>();
string cus = string.Empty;
string date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
foreach (var detail in data.Detail)
{
if (cus.IsEmpty())
{
cus = detail.CusCode;
}
else
{
if (cus != detail.CusCode)
{
throw (new Exception("请选择相同的客户！"));
}
}
//获取没有修改之前的物料的数量（该数量为库存可用数量）
var checkQty = $@"SELECT sum(Quantity) Quantity FROM ICSSDN WHERE SDNCode='{data.Code}' AND InvCode='{detail.InvCode}' AND WorkPoint='{WorkPoint}' GROUP BY InvCode ";
var dtQty = SqlHelper.CmdExecuteDataTable(checkQty);
decimal qty = 0;
if (dtQty.Rows.Count != 0)
{
qty = dtQty.Rows[0]["Quantity"].ToDecimal();
}
//获取物料的可用数量，判断当前创建单据数量
decimal invCount = iCSAccessoriesMOIApplyApp.GetInvCodeCount(detail.InvCode, "", "");
//获取创建单据的所有的物料信息
decimal allCount = data.Detail.Where(e => e.InvCode == detail.InvCode).Select(f => f.Quantity).Sum();
if (invCount + qty < allCount)
{
throw new Exception($"物料{detail.InvCode}创建单据数量：{allCount}不能大于库存可用量：{invCount + qty}");
}
string Colspan = "~" + detail.Batch + "~~~~~~~~~~~~";
string sqls = string.Format(@"select ID from ICSExtension where Colspan='{0}' and WorkPoint='{1}'", Colspan, WorkPoint);
object ExtensionID = SqlHelper.ExecuteScalar(sqls);
bool flag = true;
foreach (var item in ExtensionIDList)
{
if (item == Colspan + WorkPoint)
{
flag = false;
}
}
if (ExtensionID == null && flag == true)
{
str1 = Guid.NewGuid().ToString();
sql += string.Format(@"Insert into ICSExtension(ID, Colspan, ProjectCode, BatchCode, Version, Brand, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, MTIME, MUSER, MUSERName, WorkPoint)
Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}',getdate(),'{16}','{17}','{18}' )",
str1, Colspan, "", detail.Batch, "", "", "", "", "", "", "", "", "", "", "", "",
data.User, UserName, WorkPoint);
}
else if (ExtensionID != null)
{
str1 = ExtensionID.ToString();
}
ExtensionIDList.Add(Colspan + WorkPoint);
//If EXISTS(SELECT ID FROM ICSSDN WHERE SDNCode = '{1}' AND Sequence = '{3}' AND WorkPoint = '{8}' AND SOCode = '{17}' AND SOSequence = '{18}')
// BEGIN
// UPDATE ICSSO
// SET SOQuantity = isnull(SOQuantity, 0) - (SELECT Quantity FROM ICSSDN WHERE SDNCode = '{1}' AND Sequence = '{3}' AND WorkPoint = '{8}' AND SOCode = '{17}' AND SOSequence = '{18}')
// WHERE
// SOCode = '{17}'
// AND Sequence = '{18}'
// AND WorkPoint = '{8}'
// END;
sql += @"If EXISTS (SELECT ID FROM ICSSO WHERE SOCode='{17}' AND Sequence='{18}' AND Quantity < Isnull(SOQuantity,0)+'{5}' AND WorkPoint = '{8}')
BEGIN
RAISERROR('创建销售出库单出库数量不能大于订单数量', 16, 1);
RETURN
END ;
INSERT INTO ICSSDN(ID,SDNCode,WHCode,Sequence,InvCode,Quantity,CreatePerson,CreateDateTime,WorkPoint,Status,ExtensionID,MUSER,MUSERName,MTIME,EATTRIBUTE1,CusCode,CusName,SOCode,SOSequence,EATTRIBUTE2,EATTRIBUTE3,ArriveDate,EATTRIBUTE10,Type,SDNID,SDNDetailID,SDNQuantity,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7)" + " select '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','销售出库','1',NEWID(),NEWID(),0,'{22}','{23}','{24}','{25}' ; UPDATE ICSSO SET ArriveDate='{7}', SOQuantity=isnull(SOQuantity,0)+'{5}' WHERE SOCode='{17}' AND Sequence='{18}' And WorkPoint='{8}' ; ";
sql = string.Format(sql, detail.ID, data.Code, detail.WHCode, detail.Sequence, detail.InvCode, detail.Quantity, data.User, date, WorkPoint, '2', str1, data.User, UserName, date, data.CodeType, detail.CusCode, detail.CusName, detail.SOCode, detail.SOSequence, data.Remarks, detail.DetailReamrk, date, data.Remarks2, detail.DetailReamrk2, detail.Project, detail.CusDoc);
}
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
{
}
}
catch (Exception ex)
{
msg = ex.Message;
}
return msg;
}
#endregion
#region 保存发料信息
public string SaveICSSDN(string ICSASN)
{
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
string UserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
string msg = "";
JsonData data = JsonConvert.DeserializeObject<JsonData>(ICSASN);
string sql = "";
string Colspan = "";
string str1 = "";
string cus = string.Empty;
List<string> ExtensionIDList = new List<string>();
string date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
// 获取解析后的数据
foreach (var detail in data.Detail)
{
if (cus.IsEmpty())
{
cus = detail.CusCode;
}
else
{
if (cus != detail.CusCode)
{
throw (new Exception("请选择相同的客户！"));
}
}
//获取物料的可用数量，判断当前创建单据数量
decimal invCount = iCSAccessoriesMOIApplyApp.GetInvCodeCount(detail.InvCode, "", "");
//获取创建单据的所有的物料信息
decimal allCount = data.Detail.Where(e => e.InvCode == detail.InvCode).Select(f => f.Quantity).Sum();
if (invCount < allCount)
{
throw new Exception($"物料{detail.InvCode}创建单据总数量：{allCount}不能大于库存可用量：{invCount}");
}
Colspan = "~" + detail.Batch + "~~~~~~~~~~~~";
string sqls = string.Format(@"select ID from ICSExtension where Colspan='{0}' and WorkPoint='{1}'", Colspan, WorkPoint);
object ExtensionID = SqlHelper.ExecuteScalar(sqls);
bool flag = true;
foreach (var item in ExtensionIDList)
{
if (item == Colspan + WorkPoint)
{
flag = false;
}
}
if (ExtensionID == null && flag == true)
{
str1 = Guid.NewGuid().ToString();
sql += string.Format(@"Insert into ICSExtension(ID, Colspan, ProjectCode, BatchCode, Version, Brand, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, MTIME, MUSER, MUSERName, WorkPoint)
Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}',getdate(),'{16}','{17}','{18}' )",
str1, Colspan, "", detail.Batch, "", "", "", "", "", "", "", "", "", "", "", "",
data.User, UserName, WorkPoint);
}
else if (ExtensionID != null)
{
str1 = ExtensionID.ToString();
}
ExtensionIDList.Add(Colspan + WorkPoint);
sql += @"If EXISTS (SELECT ID FROM ICSSO WHERE SOCode='{17}' AND Sequence='{18}' AND Quantity < Isnull(SOQuantity,0)+'{5}')
BEGIN
RAISERROR('创建销售出库单出库数量不能大于订单数量', 16, 1);
RETURN
END ;
INSERT INTO ICSSDN(ID,SDNCode,WHCode,Sequence,InvCode,Quantity,CreatePerson,CreateDateTime,WorkPoint,Status,ExtensionID,MUSER,MUSERName,MTIME,EATTRIBUTE1,CusCode,CusName,SOCode,SOSequence,EATTRIBUTE2,EATTRIBUTE3,ArriveDate,EATTRIBUTE10,Type,SDNID,SDNDetailID,SDNQuantity,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7)" + " select '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','销售出库','1',NEWID(),NEWID(),0,'{22}','{23}','{24}','{25}' ; UPDATE ICSSO SET ArriveDate='{7}', SOQuantity=isnull(SOQuantity,0)+'{5}' WHERE SOCode='{17}' AND Sequence='{18}' And WorkPoint='{8}' ; ";
sql = string.Format(sql, detail.ID, data.Code, detail.WHCode, detail.Sequence, detail.InvCode, detail.Quantity, data.User, date, WorkPoint, '2', str1, data.User, UserName, date, data.CodeType, detail.CusCode, detail.CusName, detail.SOCode, detail.SOSequence, data.Remarks, detail.DetailReamrk, date, data.Remarks2, detail.DetailReamrk2, detail.Project, detail.CusDoc);
}
try
{
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
{
}
}
catch (Exception ex)
{
msg = ex.Message;
}
return msg;
}
#endregion
#region 销售发货单主表信息查询
public DataTable GetICSSDN(ref Pagination jqgridparam, string queryJson)
{
DataTable dt = new DataTable();
List<DbParameter> parameter = new List<DbParameter>();
var queryParam = queryJson.ToJObject();
string sql = @" SELECT max(a.ID) ID,
a.SDNCode
,a.Status
,a.CreatePerson
,a.CreateDateTime
,a.MUSER
,a.MUSERName
,a.CusCode
,a.CusName,
a.EATTRIBUTE2,
a.EATTRIBUTE4,
a.EATTRIBUTE5
from dbo.ICSSDN a
LEFT JOIN (SELECT case WHEN SUM(Quantity)=SUM(SDNQuantity) THEN '已发完' else '未发完' end as Status,SDNQuantity,SDNCode
FROM ICSSDN GROUP BY SDNCode,SDNQuantity) c ON a.SDNCode=c.SDNCode
left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint where type='1' and a.EATTRIBUTE10='销售出库' ";
if (!string.IsNullOrWhiteSpace(queryJson))
{
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
{
sql += " and a.SDNCode like '%" + queryParam["POCode"].ToString() + "%' ";
}
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
{
sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
}
if (!string.IsNullOrWhiteSpace(queryParam["CreatedBy"].ToString()))
{
sql += " and a.CreatePerson like '%" + queryParam["CreatedBy"].ToString() + "%' ";
}
if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
{
sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
}
if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
{
sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
}
if (queryParam["Status"].ToString() == "1")
{
sql += " AND c.Status='已发完' ";
}
else if (queryParam["Status"].ToString() == "2")
{
sql += " AND c.Status='未发完'";
}
}
sql += @" GROUP BY a.SDNCode,a.Status,a.CreatePerson,a.CreateDateTime,a.MUSER,a.MUSERName
,a.CusCode,a.CusName,a.EATTRIBUTE2,a.EATTRIBUTE4,a.EATTRIBUTE5";
sql = string.Format(sql);
DataTable dttest = Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
}
#endregion
#region 销售发货单主表信息查询
public DataTable GetICSSDNBySDNCode(string SDNCode)
{
DataTable dt = new DataTable();
List<DbParameter> parameter = new List<DbParameter>();
string sql = @" SELECT DISTINCT a.ID,a.SDNCode,a.WHCode,a.Sequence,a.InvCode,a.Quantity,a.CreatePerson,a.CreateDateTime,a.WorkPoint,a.Status,a.ExtensionID,a.MUSER,a.MUSERName,a.MTIME,a.EATTRIBUTE1,a.CusCode,a.CusName,a.SOCode,a.SOSequence,a.EATTRIBUTE2,a.EATTRIBUTE3,a.ArriveDate,a.EATTRIBUTE10,a.Type,a.SDNID,a.SDNDetailID ,b.WarehouseCode,b.WarehouseName,a.EATTRIBUTE4,a.EATTRIBUTE5
from dbo.ICSSDN a
left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
WHERE a.SDNCode='{0}'";
sql = string.Format(sql, SDNCode);
return Repository().FindTableBySql(sql.ToString());
}
#endregion
#region 主页面子表信息查询
public DataTable GetICSSDNDetail(string SDNCode, string queryJson, ref Pagination jqgridparam)
{
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
DataTable dt = new DataTable();
var queryParam = queryJson.ToJObject();
//var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
string sql = @" select ROW_NUMBER() OVER (ORDER BY CAST(a.Sequence AS INT ) ) AS Num,a.ID
,a.SDNCode
,a.Sequence
,a.InvCode
,a.Quantity
,a.Amount
,a.ExtensionID
,a.MUSER
,a.MUSERName
,a.SOCode
,a.MTIME
,a.EATTRIBUTE3
,a.EATTRIBUTE5
,a.EATTRIBUTE7 as CusDoc
,SDNQuantity
,b.EATTRIBUTE2 AS U8InvCode
,f.Colspan
,f.ProjectCode
,f.BatchCode
,f.Version
,f.Brand
,f.cFree1
,f.cFree2
,f.cFree3
,f.cFree4
,f.cFree5
,f.cFree6
,f.cFree7
,f.cFree8
,f.cFree9
,f.cFree10
from ICSSDN a
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
LEFT JOIN ICSInventory b on a.InvCode = b.InvCode AND a.WorkPoint = b.WorkPoint
WHERE a.SDNCode='" + SDNCode + "' ";
if (!string.IsNullOrWhiteSpace(queryJson))
{
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
{
sql += " and a.SDNCode like '%" + queryParam["POCode"].ToString() + "%' ";
}
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
{
sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
}
if (!string.IsNullOrWhiteSpace(queryParam["CreatedBy"].ToString()))
{
sql += " and a.CreatePerson like '%" + queryParam["CreatedBy"].ToString() + "%' ";
}
if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
{
sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
}
if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
{
sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
}
}
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
}
#endregion
#region
public string DeleteICSSDNByCode(string keyValue)
{
//站点信息
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
string msg = "";
keyValue = keyValue.Substring(1, keyValue.Length - 2);
string sql = string.Empty;
sql += string.Format(@"IF EXISTS( select ID from ICSSDN where SDNCode IN ({0}) and WorkPoint ='{1}' AND SDNQuantity>0)
BEGIN
RAISERROR('该单据已发料，不能删除！',16,1);
RETURN
END
UPDATE b SET b.SOQuantity=b.SOQuantity-a.Quantity FROM ICSSDN a
INNER JOIN ICSSO b ON a.SOCode=b.SOCode AND a.SOSequence=b.Sequence AND a.WorkPoint=b.WorkPoint WHERE a.SDNCode IN ({0}) and a.WorkPoint ='{1}';
DELETE FROM dbo.ICSSDN WHERE SDNCode IN ({0}) and WorkPoint ='{1}' ;", keyValue.TrimEnd(','), WorkPoint);
try
{
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
{
}
}
catch (Exception ex)
{
msg = ex.Message;
}
return msg;
}
#endregion
#region 行删除
public string DeleteICSSDNById(string keyValue)
{
//站点信息
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
string msg = "";
keyValue = keyValue.Substring(1, keyValue.Length - 2);
string sql = string.Empty;
sql += string.Format(@"IF EXISTS( select ID from ICSSDN where ID IN ({0}) and WorkPoint ='{1}' AND SDNQuantity>0)
BEGIN
RAISERROR('该单据已发料，不能删除！',16,1);
RETURN
END
UPDATE b SET b.SOQuantity=b.SOQuantity-a.Quantity FROM ICSSDN a
INNER JOIN ICSSO b ON a.SOCode=b.SOCode AND a.SOSequence=b.Sequence AND a.WorkPoint=b.WorkPoint WHERE a.ID IN ({0}) and a.WorkPoint ='{1}';
DELETE FROM dbo.ICSSDN WHERE ID IN ({0}) and WorkPoint ='{1}' ;", keyValue.TrimEnd(','), WorkPoint);
try
{
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
{
}
}
catch (Exception ex)
{
msg = ex.Message;
}
return msg;
}
#endregion
#region
public DataTable GetICSReturnTemporary(string rfqno)
{
string sql = @"SELECT
a.ID,a.SDNCode,a.WHCode,a.Sequence,a.InvCode as INVCODE,a.Quantity,a.CreatePerson,a.CreateDateTime,a.WorkPoint,a.Status,a.ExtensionID,a.MUSER,a.MUSERName,a.MTIME,a.EATTRIBUTE1,a.CusCode,a.CusName,a.SOCode,a.SOSequence,a.EATTRIBUTE2,a.EATTRIBUTE3 as DetailReamrk,a.EATTRIBUTE5 as DetailReamrk2,a.EATTRIBUTE6 as Project,a.EATTRIBUTE7 as CusDoc,a.ArriveDate,a.EATTRIBUTE10,a.Type,a.SDNID,a.SDNDetailID,b.InvName as INVNAME, b.InvStd,
f.BatchCode as Batch
,c.WareHouseName
FROM ICSSDN a
LEFT JOIN ICSInventory b ON a.InvCode = b.InvCode
left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
LEFT JOIN dbo.ICSWarehouse c ON a.WHCode=c.WarehouseCode AND a.WorkPoint=c.WorkPoint
where a.SDNCode='" + rfqno + "'";
DataTable table = Repository().FindDataSetBySql(sql).Tables[0];
DataTable dtCloned = table.Clone();
foreach (DataColumn col in dtCloned.Columns)
{
col.DataType = typeof(string);
}
foreach (DataRow row in table.Rows)
{
DataRow newrow = dtCloned.NewRow();
foreach (DataColumn column in dtCloned.Columns)
{
newrow[column.ColumnName] = row[column.ColumnName].ToString();
}
dtCloned.Rows.Add(newrow);
}
if (Invmes.Rows.Count > 0)
{
dtCloned.Merge(Invmes, false);
}
return dtCloned;
}
#endregion
#region 获取销售订单信息
public DataTable GetItemList(string queryJson, ref Pagination jqgridparam)
{
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
string sql = @"SELECT
a.SOCode,
a.Sequence,
a.CusCode,
a.CusName,
a.ArriveDate,
a.WHCode,
a.InvCode AS INVCODE,
a.EATTRIBUTE1 as Project,
Isnull(a.SOQuantity,0)+Isnull(temp.quantity,0) as SOQuantity,
a.Quantity-Isnull(a.SOQuantity,0)-Isnull(temp.quantity,0) as SQuantity,
a.Quantity-Isnull(a.SOQuantity,0)-Isnull(temp.quantity,0) as Quantity,
b.INVNAME,
b.InvStd,
a.Quantity AS ZQuantity ,
b.EATTRIBUTE2 AS U8InvCode,
a.EATTRIBUTE2 as CusInvName,
a.EATTRIBUTE3 as CusInvCode,
a.EATTRIBUTE4 as CusVersion,
a.EATTRIBUTE5 as CusDoc,
a.EATTRIBUTE7,
ISnull(d.Quantity,0)-ISnull(c.Quantity,0) AS KQuantity
FROM ICSSO a
LEFT JOIN so_temp temp ON a.SOCode=temp.socode AND a.Sequence=temp.socodeseq
LEFT JOIN ICSInventory b on a.InvCode = b.InvCode AND a.WorkPoint = b.WorkPoint
LEFT JOIN (
SELECT SUM(c.Quantity)AS Quantity,c.InvCode,c.WorkPoint FROM
(SELECT Sum(ISnull(Quantity,0)) as Quantity,WorkPoint,INVCode FROM (SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(OutQuantity, 0)),0) as Quantity,WorkPoint,INVCode FROM ICSOtherOut GROUP BY WorkPoint,INVCode
UNION ALL
SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(IssueQuantity, 0)),0) as Quantity,WorkPoint,INVCode FROM ICSMOApply GROUP BY WorkPoint,INVCode
UNION ALL
SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(SDNQuantity, 0)),0) as Quantity,WorkPoint,INVCode FROM ICSSDN where EATTRIBUTE10='销售出库' GROUP BY WorkPoint,INVCode
UNION ALL
SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(IssueQuantity, 0)),0) as Quantity,WorkPoint,INVCode from ICSMOIssue WHERE Status<>'3'
GROUP BY WorkPoint,INVCode)d
GROUP BY d.INVCode,d.WorkPoint) c GROUP BY c.InvCode,c.WorkPoint
) c on b.InvCode = c.InvCode and b.WorkPoint = c.WorkPoint
left JOIN (SELECT Sum(ISnull(Quantity,0)) AS Quantity,InvCode, WorkPoint FROM ICSWareHouseLotInfo where Freeze<>1
";
string WhCodesql = $@"SELECT F_Define1
FROM Sys_SRM_Items a
INNER JOIN Sys_SRM_ItemsDetail b ON a.F_Id=b.F_ItemId
WHERE F_EnCode='UseWHCode' ";
var whcode = SqlHelper.CmdExecuteDataTable(WhCodesql);
if (whcode.Rows.Count > 0)
{
sql += " and WareHouseCode in (" + whcode.Rows[0]["F_Define1"].ToString() + ")";
}
sql += " GROUP BY InvCode, WorkPoint) d ON d.InvCode = a.INVCode AND d.WorkPoint = a.WorkPoint where a.Status = '2' ";
if (!string.IsNullOrEmpty(queryJson))
{
if (!string.IsNullOrWhiteSpace(queryParam["SOCode"].ToString()))
sql += " and a.SOCode like '%" + queryParam["SOCode"].ToString() + "%'";
if (!string.IsNullOrWhiteSpace(queryParam["Sequence"].ToString()))
sql += " and a.Sequence like '%" + queryParam["Sequence"].ToString() + "%'";
if (!string.IsNullOrWhiteSpace(queryParam["NewInv"].ToString()))
sql += " and a.InvCode like '%" + queryParam["NewInv"].ToString() + "%'";
if (!string.IsNullOrWhiteSpace(queryParam["OldInv"].ToString()))
sql += " and b. EATTRIBUTE5 like '%" + queryParam["OldInv"].ToString() + "%'";
if (!string.IsNullOrWhiteSpace(queryParam["CusCode"].ToString()))
sql += " and a.CusCode like '%" + queryParam["CusCode"].ToString() + "%'";
if (!string.IsNullOrWhiteSpace(queryParam["CusName"].ToString()))
sql += " and a.CusName like '%" + queryParam["CusName"].ToString() + "%'";
if (!string.IsNullOrWhiteSpace(queryParam["CusDoc"].ToString()))
sql += " and a.EATTRIBUTE5 like '%" + queryParam["CusDoc"].ToString() + "%'";
}
//string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
sql += " and a.WorkPoint=('" + WorkPoint + "') ";
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
}
#endregion
#region 获取单据类型
/// <summary>
/// 获取U9单据类型
/// </summary>
/// <returns></returns>
public DataTable GetU9CodeType()
{
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
string sql = @" select '' as Code,'' as Name
union all
SELECT a.Code,b.Name FROM {0}{1}.dbo.SM_ShipDocType a
LEFT JOIN {0}{1}.dbo.SM_ShipDocType_Trl b ON a.ID=b.ID
WHERE Effective_DisableDate>=GETDATE()";
sql = string.Format(sql, DbHelper.GetErpIpU9(), DbHelper.GetErpNameU9());
DataTable dt = SqlHelper.GetDataTableBySql(sql);
return dt;
}
#endregion
public string GetBidCode(string WorkPoint)
{
lock (_locker)
{
WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
string BidCide = string.Empty;
if (!string.IsNullOrEmpty(WorkPoint))
{
string Date = DateTime.Now.ToString("yyyyMMdd");
string Pre = "BZCH" + Date;
BidCide = GetSerialCode(WorkPoint, "ICSSDN", "SDNCode", Pre, 4);
}
if (!string.IsNullOrWhiteSpace(BidCide))
{
string sqlISHave = @"SELECT OutCode FROM ICSOtherOut a
WHERE a.OutCode = '{0}'";
sqlISHave = string.Format(sqlISHave, BidCide);
DataTable dtIsHave = SqlHelper.GetDataTableBySql(sqlISHave);
if (dtIsHave.Rows.Count > 0)
{
throw new Exception("单号已存在！");
}
}
return BidCide;
}
}
public string GetSerialCode(string workPointCode, string tbName, string colName, string Pre, int numLen)
{
string sql = "EXEC Addins_GetSerialCode '{0}','{1}','{2}','{3}',{4}";
sql = string.Format(sql, new object[] { workPointCode, tbName, colName, Pre, numLen });
//return Repository().FindTableBySql(sql.ToString());
return SqlHelper.ExecuteScalar(sql).ToString();
//return DbHelper.ExecuteScalar(CommandType.Text, sql).ToString();
}
#region 获取批次信息
public DataTable GetBatchList(string queryJson, ref Pagination jqgridparam)
{
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
var queryParam = queryJson.ToJObject();
List<DbParameter> parameter = new List<DbParameter>();
string sql = $@"select distinct c.BatchCode from ICSWareHouseLotInfo a
INNER JOIN ICSInventoryLot b ON a.LotNo=b.LotNo AND a.WorkPoint=b.WorkPoint
INNER JOIN ICSExtension c ON b.ExtensionID=c.ID
WHERE a.InvCode='{queryParam["InvCode"]}' AND a.WorkPoint='{WorkPoint}'";
if (!string.IsNullOrEmpty(queryJson))
{
if (!string.IsNullOrWhiteSpace(queryParam["BatchCode"].ToString()))
sql += " and c.BatchCode like '%" + queryParam["BatchCode"].ToString() + "%'";
}
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
}
#endregion
#region
public DataTable GetU9Status(string SOCode)
{
try
{
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
string sqlClass = @"SELECT a.So,c.DOCNO FROM SM_SOModify a
INNER JOIN Base_Organization b ON a.Org = b.ID
INNER JOIN SM_SO c ON c.Id=a.so
WHERE a.Status != 2 AND c.DOCNO = '{0}' AND b.Code = '{1}'";
sqlClass = string.Format(sqlClass, SOCode, WorkPoint);
DataTable dt = ERPSqlServerHelper.GetDataTableBySql(sqlClass);
if (dt.Rows.Count > 0)
{
throw new Exception($"销售订单{SOCode}，存在未审核销售订单变更单，单号：{dt.Rows[0]["DocNO"]}");
}
else
{
return dt;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
#endregion
#region 定义一个类来表示 JSON 数据的结构
public class JsonData
{
public string Dept { get; set; }
public string CodeType { get; set; }
public string ApplyNegCode { get; set; }
public string Remarks { get; set; }
public string Remarks2 { get; set; }
public string Code { get; set; }
public string MTIME { get; set; }
public string User { get; set; }
public string SYproject { get; set; }
public List<JsonDetail> Detail { get; set; }
}
public class JsonDetail
{
public string ID;
public int Sequence { get; set; }
public string InvCode { get; set; }
public decimal Quantity { get; set; }
public string WHCode { get; set; }
public string DetailReamrk { get; set; }
public string DetailReamrk2 { get; set; }
public string Batch { get; set; }
/// <summary>
/// 客户编码
/// </summary>
public string CusCode { get; set; }
/// <summary>
/// 客户名称
/// </summary>
public string CusName { get; set; }
/// <summary>
/// 客户订单
/// </summary>
public string CusDoc { get; set; }
/// <summary>
/// 销售订单
/// </summary>
public string SOCode { get; set; }
/// <summary>
/// 销售订单行
/// </summary>
public string SOSequence { get; set; }
/// <summary>
/// 发货日期
/// </summary>
public string ArriveDate { get; set; }
/// <summary>
/// 项次
/// </summary>
public string Project { get; set; }
}
#endregion
}
}