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.
609 lines
29 KiB
609 lines
29 KiB
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;
|
|
|
|
namespace NFine.Application.DHAY
|
|
{
|
|
public class ICSCustomerSuppliedReturnApp : RepositoryFactory<ICSVendor>
|
|
{
|
|
public static DataTable Invmes = new DataTable();
|
|
ICSAccessoriesMOIApplyApp iCSAccessoriesMOIApplyApp = new ICSAccessoriesMOIApplyApp();
|
|
#region 获取单据类型
|
|
/// <summary>
|
|
/// 获取U9单据类型
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetU9CodeType(string key1)
|
|
{
|
|
//{
|
|
// 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.InvDoc_MiscshipDocType a
|
|
// LEFT JOIN {0}{1}.dbo.InvDoc_MiscshipDocType_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;
|
|
string msg = "";
|
|
string APIURL = ConfigurationManager.ConnectionStrings["ERPAPIURL"].ConnectionString + "/GetU9DocType";
|
|
string result = DeciliterApp.HttpPost(APIURL, key1);
|
|
DataTable dt = JsonConvert.DeserializeObject<DataTable>(result);
|
|
DataRow newRow = dt.NewRow();
|
|
newRow["Code"] = "";
|
|
newRow["Name"] = "";
|
|
dt.Rows.InsertAt(newRow, 0);
|
|
|
|
return dt;
|
|
}
|
|
#endregion
|
|
|
|
#region 获取部门
|
|
/// <summary>
|
|
/// 获取U9单据类型
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetU9Department()
|
|
{
|
|
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.CBO_Department a
|
|
LEFT JOIN {0}{1}.dbo.CBO_Department_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
|
|
|
|
#region 获取客户
|
|
/// <summary>
|
|
/// 获取U9单据类型
|
|
/// </summary>
|
|
/// <returns></returns>
|
|
public DataTable GetU9Customer()
|
|
{
|
|
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.CBO_Customer a
|
|
LEFT JOIN {0}{1}.dbo.CBO_Customer_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
|
|
#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 INVCODE ,INVNAME , InvStd
|
|
FROM ICSINVENTORY WHERE 1=1 ";
|
|
if (!string.IsNullOrEmpty(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["INVCODE"].ToString()))
|
|
sql += " and INVCODE like '%" + queryParam["INVCODE"].ToString() + "%'";
|
|
if (!string.IsNullOrWhiteSpace(queryParam["INVNAME"].ToString()))
|
|
sql += " and INVNAME like '%" + queryParam["INVNAME"].ToString() + "%'";
|
|
}
|
|
//string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
sql += " and WorkPoint=('" + WorkPoint + "')";
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
#endregion
|
|
|
|
#region 获取仓库信息
|
|
public DataTable GetWHCodeList(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 WarehouseCode ,WarehouseName
|
|
FROM ICSWarehouse WHERE 1=1 ";
|
|
if (!string.IsNullOrEmpty(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["WarehouseCode"].ToString()))
|
|
sql += " and WarehouseCode like '%" + queryParam["WarehouseCode"].ToString() + "%'";
|
|
if (!string.IsNullOrWhiteSpace(queryParam["WarehouseName"].ToString()))
|
|
sql += " and WarehouseName like '%" + queryParam["WarehouseName"].ToString() + "%'";
|
|
}
|
|
//string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
sql += " and WorkPoint=('" + WorkPoint + "')";
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
#endregion
|
|
#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,a.InvCode FROM ICSWareHouseLotInfo a
|
|
LEFT JOIN ICSInventoryLot b ON a.lotNO=b.LOtNO
|
|
LEFT JOIN ICSExtension c ON b.ExtensionID=c.ID
|
|
LEFT JOIN ICSInventory d ON a.InvCode=d.INVCode
|
|
WHERE 1=1";
|
|
if (!string.IsNullOrEmpty(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["BatchCode"].ToString()))
|
|
sql += " and c.BatchCode like '%" + queryParam["BatchCode"].ToString() + "%'";
|
|
if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
|
|
sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%'";
|
|
}
|
|
//string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
sql += " and a.WorkPoint in ('" + WorkPoint + "')";
|
|
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
#endregion
|
|
|
|
#region 获取仓库信息
|
|
public DataTable Select_ICSWHCode()
|
|
{
|
|
string sql = string.Empty;
|
|
|
|
DataTable dt = null;
|
|
sql = @"SELECT ''as WarehouseCode,'' as WarehouseName FROM ICSWarehouse
|
|
union
|
|
SELECT WarehouseCode,WarehouseName FROM ICSWarehouse ";
|
|
dt = SqlHelper.GetDataTableBySql(sql);
|
|
return dt;
|
|
}
|
|
#endregion
|
|
|
|
#region 删除杂发单
|
|
public string DeleteICSOtherOut(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(@"DELETE FROM dbo.ICSOtherOut WHERE OutCode 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 SaveICSOtherOut(string ICSASN)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string UserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string msg = "";
|
|
List<JsonData> parsedData = JsonConvert.DeserializeObject<List<JsonData>>(ICSASN);
|
|
string sql = "";
|
|
string Colspan = "";
|
|
string str1 = "";
|
|
List<string> ExtensionIDList = new List<string>();
|
|
// 获取解析后的数据
|
|
JsonData data = parsedData[0];
|
|
foreach (var detail in data.Detail)
|
|
{
|
|
//获取物料的可用数量，判断当前创建单据数量
|
|
decimal invCount = iCSAccessoriesMOIApplyApp.GetInvCodeCount(detail.InvCode, "", "");
|
|
if (invCount < detail.Quantity)
|
|
{
|
|
throw new Exception($"物料{detail.InvCode}创建单据数量：{detail.Quantity}不能大于库存可用量：{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 += "INSERT INTO ICSOtherOut(ID,OutCode,WHCode,Sequence,InvCode,Quantity,CreatePerson,CreateDateTime,WorkPoint,Status,OutID,OutDetailID,ExtensionID,MUSER,MUSERName,MTIME,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE10,OutQuantity,EATTRIBUTE11,EATTRIBUTE12)" +
|
|
" select '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',newID(),newID(),'{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','客供退货',0,'{21}','{22}'";
|
|
sql = string.Format(sql, detail.ID, data.Code, detail.WHCode, detail.Sequence, detail.InvCode, detail.Quantity, data.User, data.MTIME, WorkPoint, '1', str1, data.User, UserName, data.MTIME, data.Dept.TrimStart(' '), data.CodeType, data.ApplyNegCode, data.Custmer.TrimStart(), data.Remarks, detail.DetailReamrk, detail.SYproject.TrimStart(), data.DeptName.TrimStart(), data.CodeTypeName.TrimStart());
|
|
}
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
#region 杂发单主表信息查询
|
|
public DataTable GetICSOtherOut(ref Pagination jqgridparam, string queryJson)
|
|
{
|
|
var queryParam = queryJson.ToJObject();
|
|
DataTable dt = new DataTable();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" SELECT DISTINCT
|
|
a.OutCode
|
|
,a.Status
|
|
,a.CreatePerson
|
|
,a.CreateDateTime
|
|
,a.MUSER
|
|
,a.MUSERName
|
|
,a.EATTRIBUTE1
|
|
,a.EATTRIBUTE2
|
|
,a.EATTRIBUTE3
|
|
,a.EATTRIBUTE4
|
|
,a.EATTRIBUTE5
|
|
--,a.EATTRIBUTE7
|
|
,a.EATTRIBUTE8
|
|
,a.EATTRIBUTE9
|
|
,a.EATTRIBUTE10
|
|
,a.EATTRIBUTE11
|
|
,a.EATTRIBUTE12
|
|
,a.MUSERName AS CreatePersonName
|
|
from dbo.ICSOtherOut a
|
|
left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint WHERE 1=1 ";
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
|
|
{
|
|
sql += " and a.OutCode 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() + "' ";
|
|
}
|
|
}
|
|
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 GetICSOtherOutByOutCode(string OutCode)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" SELECT DISTINCT
|
|
a.OutCode
|
|
,a.Status
|
|
,a.CreatePerson
|
|
,a.CreateDateTime
|
|
,a.MUSER
|
|
,a.MUSERName
|
|
,a.EATTRIBUTE1
|
|
,a.EATTRIBUTE2
|
|
,a.EATTRIBUTE3
|
|
,a.EATTRIBUTE4
|
|
,a.EATTRIBUTE5
|
|
,a.EATTRIBUTE7
|
|
,a.EATTRIBUTE8
|
|
,a.EATTRIBUTE9
|
|
,a.EATTRIBUTE10
|
|
from dbo.ICSOtherOut a
|
|
left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
|
|
WHERE a.OutCode='{0}'";
|
|
sql = string.Format(sql, OutCode);
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
#endregion
|
|
#region 主页面子表信息查询
|
|
public DataTable GetICSOtherOutDetail(string OutCode, ref Pagination jqgridparam)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @" select a.ID
|
|
,a.OutCode
|
|
,a.Sequence
|
|
,a.InvCode
|
|
,a.Quantity
|
|
,a.Amount
|
|
,a.ExtensionID
|
|
,a.MUSER
|
|
,a.MUSERName
|
|
,a.MTIME
|
|
,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
|
|
,a.EATTRIBUTE1
|
|
,a.EATTRIBUTE2
|
|
,a.EATTRIBUTE3
|
|
,a.EATTRIBUTE4
|
|
,a.EATTRIBUTE5
|
|
,a.EATTRIBUTE6
|
|
,a.EATTRIBUTE7
|
|
,a.EATTRIBUTE8
|
|
,a.EATTRIBUTE9
|
|
,a.EATTRIBUTE10
|
|
,c.WarehouseName
|
|
,b.InvName AS InvName
|
|
,b.InvStd AS InvSTD
|
|
from ICSOtherOut a
|
|
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
|
|
LEFT JOIN ICSInventory b on b.InvCode=a.InvCode and b.WorkPoint=a.WorkPoint
|
|
WHERE a.OutCode='" + OutCode + "' ";
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
#endregion
|
|
|
|
public string DeleteICSOtherOutByCode(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(@"DELETE FROM dbo.ICSOtherOut WHERE OutCode IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
|
|
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
return msg;
|
|
}
|
|
public string GetBidCode(string WorkPoint)
|
|
{
|
|
WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
|
|
string BidCide = string.Empty;
|
|
if (!string.IsNullOrEmpty(WorkPoint))
|
|
{
|
|
string Date = DateTime.Now.ToString("yyyy");
|
|
string Muoth = DateTime.Now.ToString("MM");
|
|
string Day = DateTime.Now.ToString("dd");
|
|
string Pre = "ZF" + Date + Muoth + Day;
|
|
BidCide = GetSerialCode(WorkPoint, "ICSOtherOut", "OutCode", 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();
|
|
}
|
|
public string UpdateICSOtherOut(string ICSASN)
|
|
{
|
|
string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
|
|
string UserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
string msg = "";
|
|
List<JsonData> parsedData = JsonConvert.DeserializeObject<List<JsonData>>(ICSASN);
|
|
string sql = "";
|
|
string Colspan = "";
|
|
string str1 = "";
|
|
List<string> ExtensionIDList = new List<string>();
|
|
// 获取解析后的数据
|
|
JsonData data = parsedData[0];
|
|
|
|
foreach (var detail in data.Detail)
|
|
{
|
|
//获取物料的可用数量，判断当前创建单据数量
|
|
decimal invCount = iCSAccessoriesMOIApplyApp.GetInvCodeCount(detail.InvCode, "", "");
|
|
if (invCount < detail.Quantity)
|
|
{
|
|
throw new Exception($"物料{detail.InvCode}创建单据数量：{detail.Quantity}不能大于库存可用量：{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 NOT EXISTS(SELECT * FROM dbo.ICSOtherOut WHERE ID='{0}')
|
|
BEGIN
|
|
INSERT INTO ICSOtherOut(ID,OutCode,WHCode,Sequence,InvCode,Quantity,CreatePerson,CreateDateTime,WorkPoint,Status,OutID,OutDetailID,ExtensionID,MUSER,MUSERName,MTIME,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE10,OutQuantity,EATTRIBUTE11,EATTRIBUTE12)
|
|
select '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',newID(),newID(),'{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','客供退货',0,'{21}','{22}'
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.ICSOtherOut
|
|
SET WHCode='{2}',InvCode='{4}',Quantity='{5}',CreatePerson='{6}',
|
|
CreateDateTime='{7}' ,MTIME='{13}',EATTRIBUTE1='{14}',EATTRIBUTE2='{15}',EATTRIBUTE3='{16}',EATTRIBUTE4='{17}',EATTRIBUTE5='{18}',EATTRIBUTE6='{19}',EATTRIBUTE7='{20}',EATTRIBUTE11='{21}',EATTRIBUTE12='{22}'
|
|
WHERE ID='{0}'
|
|
END";
|
|
//sql += "INSERT INTO ICSOtherOut(ID,OutCode,WHCode,Sequence,InvCode,Quantity,CreatePerson,CreateDateTime,WorkPoint,Status,OutID,OutDetailID,ExtensionID,MUSER,MUSERName,MTIME,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7)" +
|
|
// " select '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',newID(),newID(),'{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}'";
|
|
sql = string.Format(sql, detail.ID, data.Code, detail.WHCode, detail.Sequence, detail.InvCode, detail.Quantity, data.User, data.MTIME, WorkPoint, '1', str1, data.User, UserName, data.MTIME, data.Dept, data.CodeType.TrimStart(), data.ApplyNegCode, data.Custmer.TrimStart(), data.Remarks, detail.DetailReamrk, detail.SYproject.TrimStart(), data.DeptName.TrimStart(), data.CodeTypeName.TrimStart());
|
|
}
|
|
try
|
|
{
|
|
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
|
|
{
|
|
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
}
|
|
public DataTable GetICSReturnTemporary(string rfqno)
|
|
{
|
|
string sql = @"SELECT
|
|
a.ID,
|
|
a.OutCode,
|
|
a.InvCode,
|
|
b.InvName,
|
|
b.InvStd,
|
|
a.Quantity,
|
|
a.WHCode,
|
|
a.WHCode as WHCodeHHH,
|
|
a.EATTRIBUTE6,
|
|
a.Quantity
|
|
,f.BatchCode as Batch
|
|
,c.WarehouseName as WHCodeName
|
|
,a.EATTRIBUTE7 as SYprojectHidden
|
|
,a.EATTRIBUTE7 as SYproject
|
|
FROM ICSOtherOut 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.OutCode='" + 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;
|
|
}
|
|
// 定义一个类来表示 JSON 数据的结构
|
|
public class JsonData
|
|
{
|
|
public string Dept { get; set; }
|
|
public string DeptName { get; set; }
|
|
public string CodeType { get; set; }
|
|
public string CodeTypeName { get; set; }
|
|
public string ApplyNegCode { get; set; }
|
|
public string Custmer { get; set; }
|
|
public string Remarks { get; set; }
|
|
public string Code { get; set; }
|
|
public string MTIME { get; set; }
|
|
public string User { 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 Batch { get; set; }
|
|
public string SYproject { get; set; }
|
|
}
|
|
#endregion
|
|
|
|
}
|
|
}
|