|
|
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;
namespace NFine.Application.DHAY{ public class ICSCustomerSuppliedInApp : RepositoryFactory<ICSVendor> { public static DataTable Invmes = new DataTable();
#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.InvDoc_MiscRcvDocType a LEFT JOIN {0}{1}.dbo.InvDoc_MiscRcvDocType_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 string DeleteICSOtherIn(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.ICSOtherIn WHERE InCode 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 SaveICSOtherIn(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) { #region 验证指定类型的单据类型批次必填
string typecheckString = @"SELECT b.F_ItemCode FROM Sys_SRM_Items a
INNER JOIN Sys_SRM_ItemsDetail b ON a.F_Id = b.F_ItemId WHERE a.F_EnCode = 'ICSOherIn' and b.F_EnabledMark='1' ";
DataTable datatable = SqlHelper.CmdExecuteDataTable(typecheckString); if (datatable.Rows.Count > 0) { var typecheck = datatable.Select("F_ItemCode = '" + data.CodeType + "'"); if (typecheck.Length > 0 && String.IsNullOrEmpty(detail.Batch)) { throw new Exception("请填写批号"); } } #endregion
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 ICSOtherIn(ID,InCode,WHCode,Sequence,InvCode,Quantity,CreatePerson,CreateDateTime,WorkPoint,Status,InID,InDetailID,ExtensionID,MUSER,MUSERName,MTIME,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE8,EATTRIBUTE9,EATTRIBUTE10,InQuantity,EATTRIBUTE11,EATTRIBUTE12,EATTRIBUTE13)" + " select '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',newID(),newID(),'{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','客供入库',0,'{23}','{24}','{25}' "; 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, data.ApplyNegCode, data.Custmer, data.Remarks,detail.DetailReamrk, detail.SYproject, data.Remarks2,detail.DetailReamrk2,data.CodeTypeName.Trim(),data.DeptName.Trim(),data.CustmerName.Trim()); } try { if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0) {
} } catch (Exception ex) { msg=ex.Message; } return msg; } #endregion
#region 杂收单主表信息查询
public DataTable GetICSOtherIn(ref Pagination jqgridparam,string queryJson) { DataTable dt = new DataTable(); var queryParam = queryJson.ToJObject(); List<DbParameter> parameter = new List<DbParameter>(); string sql = @" SELECT DISTINCT
a.InCode ,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.EATTRIBUTE13 from dbo.ICSOtherIn 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.InCode 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 GetICSOtherInByInCode(string InCode) { DataTable dt = new DataTable(); List<DbParameter> parameter = new List<DbParameter>(); string sql = @" SELECT DISTINCT
a.InCode ,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.EATTRIBUTE13 from dbo.ICSOtherIn a left join ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint WHERE a.InCode='{0}'";
sql = string.Format(sql, InCode); return Repository().FindTableBySql(sql.ToString()); } #endregion
#region 主页面子表信息查询
public DataTable GetICSOtherInDetail(string InCode,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 a.ID
,a.InCode ,a.Sequence ,a.InvCode ,a.Quantity ,a.Amount ,a.ExtensionID ,a.MUSER ,a.MUSERName ,a.MTIME ,a.InQuantity ,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 from ICSOtherIn a left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint WHERE a.InCode='" + InCode + "' ";
if (!string.IsNullOrWhiteSpace(queryJson)) { if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString())) { sql += " and a.InCode 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
public string DeleteICSOtherInByCode(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.ICSOtherIn WHERE InCode 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 DateString = DateTime.Now.ToString("yyyyMMdd"); string Pre = "ZS" + DateString; BidCide = GetSerialCode(WorkPoint, "ICSOtherIn", "InCode", Pre, 4); } if (!string.IsNullOrWhiteSpace(BidCide)) { string sqlISHave = @"SELECT InCode FROM ICSOtherIn a
WHERE a.InCode = '{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();
}
/// <summary>
/// 更新客供入库单
/// </summary>
/// <param name="ICSASN"></param>
/// <returns></returns>
public string UpdateICSOtherIn(string ICSASN) { string msg = ""; try { string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(','); string UserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName; JsonData data = JsonConvert.DeserializeObject<JsonData>(ICSASN); List<string> ExtensionIDList = new List<string>(); //已经生成条码的单据，修改单据前请先删除条码信息
string checksql = $@"SELECT * FROM ICSInventoryLotDetail WHERE TransCode='{data.Code}';"; DataTable checkdt = SqlHelper.CmdExecuteDataTable(checksql); if (checkdt.Rows.Count > 0) { throw new Exception("该单据已经生成条码，请先删除已经生成的条码信息再修改单据信息！"); } // 获取解析后的数据
string sql = $@"delete from ICSOtherIn where INCode='{data.Code}';"; foreach (var detail in data.Detail) { #region 验证指定类型的单据类型批次必填
string typecheckString = @"SELECT b.F_ItemCode FROM Sys_SRM_Items a
INNER JOIN Sys_SRM_ItemsDetail b ON a.F_Id = b.F_ItemId WHERE a.F_EnCode = 'ICSOherIn' ";
DataTable datatable = SqlHelper.CmdExecuteDataTable(typecheckString); if (datatable.Rows.Count > 0) { var typecheck = datatable.Select("F_ItemCode = '" + data.CodeType + "'"); if (typecheck.Length > 0 && String.IsNullOrEmpty(detail.Batch)) { throw new Exception("请填写批号"); } } #endregion
string str1 = ""; 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);
sql += "INSERT INTO ICSOtherIn(ID,InCode,WHCode,Sequence,InvCode,Quantity,CreatePerson,CreateDateTime,WorkPoint,Status,InID,InDetailID,ExtensionID,MUSER,MUSERName,MTIME,EATTRIBUTE1,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE8,EATTRIBUTE9,EATTRIBUTE10,InQuantity,EATTRIBUTE11,EATTRIBUTE12,EATTRIBUTE13)" + " select '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',newID(),newID(),'{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','客供入库',0,'{23}','{24}','{25}' "; sql = string.Format(sql, detail.ID, data.Code, detail.WHCode, detail.Sequence, detail.InvCode, detail.Quantity, data.User, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), WorkPoint, '1', str1, data.User, UserName, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), data.Dept, data.CodeType, data.ApplyNegCode, data.Custmer, data.Remarks, detail.DetailReamrk, detail.SYproject, data.Remarks2, detail.DetailReamrk2,data.CodeTypeName.Trim(),data.DeptName.Trim(), data.CustmerName.Trim()); }
if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0) {
} } catch (Exception ex) { msg = ex.Message; } return msg; } public DataTable GetICSReturnTemporary(string rfqno) { string sql = @"SELECT
a.ID, a.InCode, a.InvCode, b.InvName as INVNAME, b.InvStd, a.Quantity, a.WHCode, a.WHCode as WHCodeHHH, a.EATTRIBUTE6, a.EATTRIBUTE7 as SYproject, a.EATTRIBUTE8, a.EATTRIBUTE9, a.Quantity ,f.BatchCode as Batch ,c.WarehouseName as WHCodeName FROM ICSOtherIn 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.InCode='" + 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; }
#region 定义一个类来表示 JSON 数据的结构
/// <summary>
/// 主表数据
/// </summary>
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 CustmerName { 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; }
/// <summary>
/// 详情信息
/// </summary>
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; } /// <summary>
/// 备注1
/// </summary>
public string DetailReamrk { get; set; }
/// <summary>
/// 备注2
/// </summary>
public string DetailReamrk2 { get; set; } public string Batch { get; set; }
/// <summary>
/// 受益项目
/// </summary>
public string SYproject { get; set; } } #endregion
}}
|
