using Common;
|
using Model;
|
using System;
|
using System.Collections;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Text;
|
|
namespace BLL
|
{
|
public class DALErpIn : IDALErpIn
|
{
|
public IList<ErpIn> GetList()
|
{
|
try
|
{
|
IList<ErpIn> ls = new List<ErpIn>();
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("Select RoleName from Roles where IsDel !=1");
|
|
IDataReader dt = DataFactory.SqlDataBase().GetDataReaderBySQL(strSql);
|
ls = ModelConvertHelper<ErpIn>.DataReaderToModel(dt);
|
|
return ls;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
public IList<Erp> GetList(AjaxErpInList Json, ref PageInfo page)
|
{
|
try
|
{
|
IList<Erp> list = new List<Erp>();
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("SELECT DISTINCT guid,OrdNo,BatchNo,BatchDemo,Statu,CompletionTime,CreateUser,CreateTime,");
|
strSql.Append($"UpdateUser,UpdateTime,Demo FROM View_ErpIn where 1=1 and DepartGuid='{Json.DepartGuid}' ");
|
|
List<SqlParam> para = new List<SqlParam>();
|
if (!string.IsNullOrEmpty(Json.OrdNo))
|
{
|
strSql.Append($"and OrdNo like '%{Json.OrdNo}%' ");
|
//para.Add(new SqlParam("@OrdNo", Json.OrdNo));
|
}
|
if (!string.IsNullOrEmpty(Json.Statu))
|
{
|
strSql.Append($"and StatuCode= '{Json.Statu}' ");
|
//para.Add(new SqlParam("@Statu", Json.Statu));
|
}
|
if (!string.IsNullOrEmpty(Json.BatchNo))
|
{
|
strSql.Append($"and BatchNo like '%{Json.BatchNo}%' ");
|
//para.Add(new SqlParam("@BatchNo", Json.BatchNo));
|
}
|
if (!string.IsNullOrEmpty(Json.BatchDemo))
|
{
|
strSql.Append($"and BatchDemo like '%{Json.BatchDemo}%' ");
|
//para.Add(new SqlParam("@BatchDemo", Json.BatchDemo));
|
}
|
if (!string.IsNullOrEmpty(Json.DepartGuid)) // 部门
|
{
|
strSql.Append($"and DepartGuid= '{Json.DepartGuid}' ");
|
//para.Add(new SqlParam("@DepartGuid", Json.DepartGuid));
|
}
|
// 根据物料属性检索入库单
|
if (!string.IsNullOrEmpty(Json.MatNo))
|
{
|
strSql.Append($"and MatNo like '%{Json.MatNo}%' ");
|
//para.Add(new SqlParam("@MatNo", Json.MatNo));
|
}
|
if (!string.IsNullOrEmpty(Json.MatName))
|
{
|
strSql.Append($"and MatName like '%{Json.MatName}%' ");
|
//para.Add(new SqlParam("@MatName", Json.MatName));
|
}
|
if (!string.IsNullOrEmpty(Json.LingNo))
|
{
|
strSql.Append($"and LingNo like '%{Json.LingNo}%' ");
|
//para.Add(new SqlParam("@LingNo", Json.LingNo));
|
}
|
if (!string.IsNullOrEmpty(Json.TuNo))
|
{
|
strSql.Append($"and TuNo like '%{Json.TuNo}%' ");
|
//para.Add(new SqlParam("@TuNo", Json.TuNo));
|
}
|
if (Json.BeginTime != DateTime.MinValue && Json.BeginTime != null && Json.BeginTime != DateTime.MaxValue)
|
{
|
strSql.Append("and CreateTime >= @CreateTime1 ");
|
para.Add(new SqlParam("@CreateTime1", Convert.ToDateTime(Json.BeginTime).ToShortDateString()));
|
}
|
if (Json.EndTime != DateTime.MinValue && Json.EndTime != null && Json.EndTime != DateTime.MaxValue)
|
{
|
strSql.Append("and CreateTime <= @CreateTime2 ");
|
para.Add(new SqlParam("@CreateTime2", Convert.ToDateTime(Json.EndTime).ToShortDateString() + " 23:59:59.999"));
|
}
|
|
SqlParam[] param = null;
|
if (para != null)
|
{
|
param = para.ToArray();
|
}
|
|
DataTable dt = DataFactory.SqlDataBase().GetPageList(strSql.ToString(), param, "CreateTime", "desc", ref page);
|
return list = ModelConvertHelper<Erp>.DataTableToModel(dt);
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
public Erp GetModel(string OrdNo)
|
{
|
try
|
{
|
Erp us = null;
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append(" Select * from View_ErpIn where OrdNo = @OrdNo;");
|
SqlParam[] para = new SqlParam[]
|
{
|
new SqlParam("@OrdNo", OrdNo),
|
};
|
|
IDataReader dt = DataFactory.SqlDataBase().GetDataReaderBySQL(strSql, para);
|
|
us = ModelConvertHelper<Erp>.ReaderToModel(dt);
|
|
return us;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
public ErpIn GetModel(string OrdNo, ref IList<ErpMat> erpMats)
|
{
|
try
|
{
|
|
|
ErpIn us = null;
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append(" Select * from View_ErpIn where ");
|
strSql.Append("OrdNo = @OrdNo ");
|
//strSql.Append("and IsDel != 1");
|
SqlParam[] para = new SqlParam[]
|
{
|
new SqlParam("@OrdNo", OrdNo),
|
};
|
|
IDataReader dt = DataFactory.SqlDataBase().GetDataReaderBySQL(strSql, para);
|
|
us = ModelConvertHelper<ErpIn>.ReaderToModel(dt);
|
|
IDALErpInDetail erpInDetail = new DALErpInDetail();
|
erpMats = erpInDetail.GetList(OrdNo);
|
|
|
return us;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
|
public bool Add(Erp model, List<ErpDetails> Mats)
|
{
|
bool bl = false;
|
try
|
{
|
// 获取入库单号
|
string ordNo = GetOrdNo();
|
if (ordNo != "")
|
{
|
model.OrdNo = ordNo;
|
|
// 保存入库表
|
Hashtable ht = new Hashtable();
|
ht.Add("OrdNo", "'" + model.OrdNo + "'");
|
ht.Add("Statu", "'" + model.Statu + "'");
|
ht.Add("BatchNo", "'" + model.BatchNo + "'");
|
ht.Add("BatchDemo", "'" + model.BatchDemo + "'");
|
ht.Add("DepartGuid", "'" + model.DepartGuid + "'");
|
ht.Add("Demo", "'" + model.Demo + "'");
|
ht.Add("CreateUser", "'" + model.CreateUser + "'");
|
|
int rowCount = DataFactory.SqlDataBase().InsertByHashtableNullParam("Erp_In", ht);
|
if (rowCount == 1)
|
{
|
// 保存入库明细表
|
foreach (ErpDetails detail in Mats)
|
{
|
Hashtable htitems = new Hashtable();
|
htitems.Add("OrdNo", "'" + model.OrdNo + "'");
|
htitems.Add("MatNo", "'" + detail.MatGuid + "'");
|
htitems.Add("DepartGuid", "'" + model.DepartGuid + "'");
|
htitems.Add("PlanQuant", "'" + detail.PlanQuant + "'");
|
htitems.Add("CurQuant", "'" + detail.CurQuant + "'");
|
htitems.Add("LingNo", "'" + detail.LingNo + "'");
|
htitems.Add("TuNo", "'" + detail.TuNo + "'");
|
htitems.Add("PageNo", "'" + detail.PageNo + "'");
|
htitems.Add("Certificate", "'" + detail.Certificate + "'");
|
htitems.Add("Demo", "'" + detail.DetailDemo + "'");
|
htitems.Add("CreateUser", "'" + model.CreateUser + "'");
|
htitems.Add("BatchNo", "'" + detail.BatchNo + "'");
|
htitems.Add("BatchDemo", "'" + detail.BatchDemo + "'");
|
DataFactory.SqlDataBase().InsertByHashtableNullParam("ErpInDetail", htitems);
|
}
|
bl = true;
|
}
|
else
|
{
|
bl = false;
|
}
|
}
|
|
return bl;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
public bool Update(Erp model, List<ErpDetails> Mats)
|
{
|
bool result = false;
|
try
|
{
|
// 保存入库单
|
Hashtable ht = new Hashtable();
|
ht.Add("BatchNo", "'" + model.BatchNo + "'");
|
ht.Add("BatchDemo", "'" + model.BatchDemo + "'");
|
ht.Add("Demo", "'" + model.Demo + "'");
|
ht.Add("UpdateTime", "getdate()");
|
ht.Add("UpdateUser", "'" + model.CreateUser + "'");
|
string OrdNo = "'" + model.OrdNo + "'";
|
int rowCount = DataFactory.SqlDataBase().UpdateByHashtable("Erp_In", nameof(model.OrdNo), OrdNo, ht);
|
|
// 保存入库单明细
|
if (rowCount == 1)
|
{
|
foreach (ErpDetails detail in Mats)
|
{
|
Hashtable htitems = new Hashtable();
|
|
if (string.IsNullOrEmpty(detail.guid))
|
{
|
htitems.Add("OrdNo", "'" + model.OrdNo + "'");
|
htitems.Add("MatNo", "'" + detail.MatGuid + "'");
|
htitems.Add("DepartGuid", "'" + model.DepartGuid + "'");
|
htitems.Add("PlanQuant", "'" + detail.PlanQuant + "'");
|
htitems.Add("CurQuant", "'" + detail.CurQuant + "'");
|
htitems.Add("LingNo", "'" + detail.LingNo + "'");
|
htitems.Add("TuNo", "'" + detail.TuNo + "'");
|
htitems.Add("PageNo", "'" + detail.PageNo + "'");
|
htitems.Add("Certificate", "'" + detail.Certificate + "'");
|
htitems.Add("Demo", "'" + detail.DetailDemo + "'");
|
htitems.Add("CreateUser", "'" + detail.CreateUser + "'");
|
htitems.Add("BatchNo", "'" + detail.BatchNo + "'");
|
htitems.Add("BatchDemo", "'" + detail.BatchDemo + "'");
|
DataFactory.SqlDataBase().InsertByHashtableNullParam("ErpInDetail", htitems);
|
}
|
else
|
{
|
htitems.Add("PlanQuant", "'" + detail.PlanQuant + "'");
|
htitems.Add("CurQuant", "'" + detail.CurQuant + "'");
|
htitems.Add("LingNo", "'" + detail.LingNo + "'");
|
htitems.Add("TuNo", "'" + detail.TuNo + "'");
|
htitems.Add("PageNo", "'" + detail.PageNo + "'");
|
htitems.Add("Certificate", "'" + detail.Certificate + "'");
|
htitems.Add("Demo", "'" + detail.DetailDemo + "'");
|
htitems.Add("UpdateTime", "getdate()");
|
htitems.Add("UpdateUser", "'" + detail.CreateUser + "'");
|
htitems.Add("BatchNo", "'" + detail.BatchNo + "'");
|
htitems.Add("BatchDemo", "'" + detail.BatchDemo + "'");
|
string guid = "'" + detail.guid + "'";
|
DataFactory.SqlDataBase().UpdateByHashtable("ErpInDetail", nameof(model.guid), guid, htitems);
|
}
|
}
|
|
result = true;
|
}
|
|
return result;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
public void DelOrdDetail(string guid)
|
{
|
try
|
{
|
if (!string.IsNullOrEmpty(guid))
|
{
|
DataFactory.SqlDataBase().DeleteData("ErpInDetail", "guid", guid);
|
}
|
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
public ErpDetails GetMatNo(string matNo)
|
{
|
try
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("select guid as matGuid,MatNo,MatName,MatType ");
|
strSql.Append("from view_Material where IsDel = '0' and MatNo = '" + matNo + "';");
|
|
IDataReader dt = DataFactory.SqlDataBase().GetDataReaderBySQL(strSql);
|
IList<ErpDetails> matLists = new List<ErpDetails>();
|
matLists = ModelConvertHelper<ErpDetails>.DataReaderToModel(dt);
|
|
return matLists[0];
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
|
public bool UpdateAudit(string[] OrdNo, string AuditFlag, string User)
|
{
|
bool result = false;
|
try
|
{
|
Hashtable ht = new Hashtable();
|
|
ht["AuditFlag"] = string.IsNullOrEmpty(AuditFlag) ? "'AD01'" : "'" + AuditFlag + "'";
|
// ht["Statu"] = "'ER02'";
|
ht["AuditUser"] = string.IsNullOrEmpty(User) ? "''" : "'" + User + "'"; ;
|
ht["AuditTime"] = "getdate()";
|
|
|
int _ret = DataFactory.SqlDataBase().UpdateByHashtableA("Erp_In", nameof(OrdNo), OrdNo, ht);
|
if (_ret == 1) result = true;
|
|
return result;
|
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
|
|
public bool UpdateClose(string[] OrdNo, string Statu)
|
{
|
bool result = false;
|
try
|
{
|
Hashtable ht = new Hashtable();
|
ht["Statu"] = "'" + Statu + "'";
|
ht["COMDAT"] = "getDate()";
|
int dt = DataFactory.SqlDataBase().IsExist("Erp_In", "OrdNo", OrdNo);
|
if (dt >= OrdNo.Length)
|
{
|
int _ret = DataFactory.SqlDataBase().UpdateByHashtable("Erp_In", "OrdNo", OrdNo, ht);
|
if (_ret >= OrdNo.Length) result = true;
|
}
|
return result;
|
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
public bool IsExist(string name, string value)
|
{
|
bool result = false;
|
try
|
{
|
string[] para = new string[] { value };
|
int dt = DataFactory.SqlDataBase().IsExist("ErpIn", name, para);
|
if (dt > 0) result = true;
|
return result;
|
}
|
catch
|
{
|
return result;
|
}
|
|
}
|
|
|
public DataTable GetDataTable(string[] strWhere)
|
{
|
DataTable dt = null;
|
try
|
{
|
int index = 0;
|
string str = "@RoleNum" + index;
|
SqlParam[] param = new SqlParam[strWhere.Length];
|
StringBuilder sql = new StringBuilder();
|
sql.Append("Select RoleNum as 角色编号, RoleName as 角色名,CreatUser as 创建人,CreatTime as 创建时间,UpdateUser as 更新人,UpdateTime as 更新时间,Demo as 备注 FROM View_GetRoles where RoleNum in (");
|
|
for (int i = 0; i < param.Length - 1; i++)
|
{
|
string obj2 = strWhere[i];
|
str = "@RoleNum" + index;
|
sql.Append(str).Append(",");
|
param[index] = new SqlParam(str, obj2);
|
index++;
|
}
|
str = "@RoleNum" + index;
|
sql.Append(str);
|
param[index] = new SqlParam(str, strWhere[index]);
|
sql.Append(")");
|
|
dt = DataFactory.SqlDataBase().GetDataTableBySQL(sql, param, "");
|
|
return dt;
|
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
|
}
|
public bool BatchDelete(string[] OrdNo)
|
{
|
bool result = false;
|
try
|
{
|
Hashtable ht = new Hashtable();
|
ht["Statu"] = "01";
|
|
int dt = DataFactory.SqlDataBase().IsExist("Erp_In", "OrdNo", OrdNo, ht);
|
if (dt >= OrdNo.Length)
|
{
|
int _ret = DataFactory.SqlDataBase().BatchDeleteData("Erp_In", "OrdNo", OrdNo);
|
if (_ret >= OrdNo.Length)
|
{
|
int dt1 = DataFactory.SqlDataBase().IsExist("ErpInDetail", "OrdNo", OrdNo);
|
if (dt1 > 0)
|
{
|
int _ret1 = DataFactory.SqlDataBase().BatchDeleteData("ErpInDetail", "OrdNo", OrdNo);
|
if (_ret1 >= dt1) result = true;
|
}
|
}
|
}
|
return result;
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
|
public bool DeleteOrdNo(string[] OrdNo)
|
{
|
try
|
{
|
bool bl = false;
|
Hashtable ht = new Hashtable();
|
ht.Add("Statu", "01");
|
int rowCount = DataFactory.SqlDataBase().IsExist("Erp_In", "OrdNo", OrdNo, ht);
|
if (rowCount >= OrdNo.Length)
|
{
|
StringBuilder sbStr = new StringBuilder();
|
sbStr.Append("Update Erp_In set IsDel = '1' where OrdNo='" + OrdNo[0] + "';");
|
sbStr.Append("Update ErpInDetail set IsDel = '1' where OrdNo='" + OrdNo[0] + "';");
|
int rowNum = DataFactory.SqlDataBase().ExecuteBySql(sbStr);
|
if (rowNum > 0)
|
{
|
bl = true;
|
}
|
}
|
|
return bl;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
public DataSet GetPrintData(string OrdNo)
|
{
|
DataSet ds = new DataSet(); ;
|
try
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append(" Select DISTINCT Ordno,BatchNo,BatchDemo,Demo,CreateTime,CreateUser ");
|
strSql.Append("from View_ErpIn where OrdNo = @OrdNo ");
|
|
SqlParam[] para = new SqlParam[]
|
{
|
new SqlParam("@OrdNo", OrdNo),
|
};
|
DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(strSql, para, "table1");
|
if (dt != null) ds.Tables.Add(dt);
|
|
DALErpInDetail purDetail = new DALErpInDetail();
|
DataTable dts = purDetail.GetPrintData(OrdNo);
|
if (dts != null) ds.Tables.Add(dts);
|
|
return ds;
|
}
|
catch
|
{
|
return null;
|
}
|
}
|
/// <summary>
|
/// 订单状态查询
|
/// </summary>
|
/// <returns></returns>
|
public IList<ErpStatu> GetErpStatuList()
|
{
|
try
|
{
|
IList<ErpStatu> ls = new List<ErpStatu>();
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("Select StatuNo,StatuName from ErpStatu ");
|
|
IDataReader dt = DataFactory.SqlDataBase().GetDataReaderBySQL(strSql);
|
ls = ModelConvertHelper<ErpStatu>.DataReaderToModel(dt);
|
|
return ls;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
///<summary>
|
///根据货位查询托盘
|
/// </summary>
|
public IList<ErpMat> GetList(string addre)
|
{
|
IList<ErpMat> ls = new List<ErpMat>();
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("select Palno from log_Store_Palno where Addre='" + addre + "'");
|
IDataReader dt = DataFactory.SqlDataBase().GetDataReaderBySQL(strSql);
|
ls = ModelConvertHelper<ErpMat>.DataReaderToModel(dt);
|
return ls;
|
}
|
///<summary>
|
///将DataTable中数据写入数据库中
|
///
|
/// </summary>
|
public static string ImportExcel(DataTable dt, string CreateUser)
|
{
|
int ret = 0;
|
if (dt == null || dt.Rows.Count == 0)
|
{
|
return "Excel无内容";
|
}
|
|
//获取要插入列的名字（为动态，由Excel列数决定）
|
string colNames = "";
|
|
//循环获取列名
|
for (int i = 0; i < dt.Columns.Count; i++)
|
{
|
if (dt.Columns[i].ColumnName.Equals("单号") || dt.Columns[i].ColumnName.Equals("生产令号") ||
|
dt.Columns[i].ColumnName.Equals("物料编码") || dt.Columns[i].ColumnName.Equals("图号") ||
|
dt.Columns[i].ColumnName.Equals("序号") || dt.Columns[i].ColumnName.Equals("类型") ||
|
dt.Columns[i].ColumnName.Equals("合格证") || dt.Columns[i].ColumnName.Equals("单数") ||
|
dt.Columns[i].ColumnName.Equals("总数") || dt.Columns[i].ColumnName.Equals("备注"))
|
{
|
if (dt.Columns[i].ColumnName.Equals("单号"))
|
{
|
dt.Columns[i].ColumnName = "OrdNo";
|
}
|
if (dt.Columns[i].ColumnName.Equals("生产令号"))
|
{
|
dt.Columns[i].ColumnName = "LingNo";
|
}
|
if (dt.Columns[i].ColumnName.Equals("物料编码"))
|
{
|
dt.Columns[i].ColumnName = "MatNo";
|
}
|
if (dt.Columns[i].ColumnName.Equals("图号"))
|
{
|
dt.Columns[i].ColumnName = "TuNo";
|
}
|
if (dt.Columns[i].ColumnName.Equals("序号"))
|
{
|
dt.Columns[i].ColumnName = "PageNo";
|
}
|
if (dt.Columns[i].ColumnName.Equals("类型"))
|
{
|
dt.Columns[i].ColumnName = "MatType";
|
}
|
if (dt.Columns[i].ColumnName.Equals("合格证"))
|
{
|
dt.Columns[i].ColumnName = "HeGeZheng";
|
}
|
if (dt.Columns[i].ColumnName.Equals("单数"))
|
{
|
dt.Columns[i].ColumnName = "PlanQuant";
|
}
|
if (dt.Columns[i].ColumnName.Equals("总数"))
|
{
|
dt.Columns[i].ColumnName = "CurQuant";
|
}
|
if (dt.Columns[i].ColumnName.Equals("备注"))
|
{
|
dt.Columns[i].ColumnName = "Demo";
|
}
|
|
}
|
else
|
{
|
//删除多余的列
|
dt.Columns.Remove(dt.Columns[i]);
|
i--;
|
}
|
}
|
|
int ordno1 = 0;
|
//判断入库单单号是否已经存在
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
StringBuilder hql = new StringBuilder();
|
hql.Append("select * from Erp_in where Ordno = '" + dt.Rows[i]["OrdNo"].ToString() + "'");
|
DataTable dd = DataFactory.SqlDataBase().GetDataTableBySQL(hql);
|
if (dd.Rows.Count > 0)
|
{
|
ordno1 = 1;
|
}
|
}
|
if (ordno1 == 1)
|
{
|
return "已有入库单号存在，请核实后重新导入";
|
}
|
|
string MatNoName = "";
|
//第一个循环，遍历每一行
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
int sum = 0;
|
StringBuilder hql = new StringBuilder();
|
hql.Append("select * from Material where MatNo='" + dt.Rows[i]["MatNo"].ToString() + "'");
|
DataTable Mat = DataFactory.SqlDataBase().GetDataTableBySQL(hql);
|
if (Mat.Rows.Count > 0)
|
{
|
StringBuilder eql = new StringBuilder();
|
eql.Append("insert into ErpInDetail(OrdNo,MatNo,PlanQuant,CurQuant,Demo,LingNo,TuNo,PageNo,MatType,HeGeZheng)" +
|
"values('" + dt.Rows[i]["OrdNo"].ToString() + "','" + dt.Rows[i]["MatNo"].ToString() + "','" + dt.Rows[i]["PlanQuant"].ToString() + "'," +
|
"'" + dt.Rows[i]["CurQuant"].ToString() + "','" + dt.Rows[i]["Demo"].ToString() + "','" + dt.Rows[i]["LingNo"].ToString() + "'," +
|
"'" + dt.Rows[i]["TuNo"].ToString() + "','" + dt.Rows[i]["PageNo"].ToString() + "','" + dt.Rows[i]["MatType"].ToString() + "','" + dt.Rows[i]["HeGeZheng"].ToString() + "')");
|
sum = DataFactory.SqlDataBase().ExecuteBySql(eql);
|
|
}
|
else
|
{
|
MatNoName += dt.Rows[i]["MatNo"].ToString() + ',';
|
}
|
StringBuilder sql = new StringBuilder();
|
//向主表Erp_In插入数据
|
if (sum != 0)
|
{
|
sql.Append("select Ordno from Erp_in where Ordno='" + dt.Rows[i]["OrdNo"].ToString() + "'");
|
DataTable ordno = DataFactory.SqlDataBase().GetDataTableBySQL(sql);
|
if (ordno.Rows.Count <= 0)
|
{
|
sql.Clear();
|
sql.Append("insert into Erp_In(Ordno,Statu,CreateUser) values('" + dt.Rows[i]["OrdNo"] + "','01','" + CreateUser + "')");
|
ret = DataFactory.SqlDataBase().ExecuteBySql(sql);
|
}
|
|
}
|
|
}
|
try
|
{
|
if (MatNoName != "")
|
{
|
return "该物料" + MatNoName + "无法找到请先添加物料明细，其余物料导入成功！";
|
}
|
}
|
catch (Exception e)
|
{
|
return "导入失败，请检查匹配";
|
}
|
return "导入成功";
|
throw new NotImplementedException();
|
}
|
/// <summary>
|
/// 关单时向库存表更新数量
|
/// </summary>
|
/// <param name="list1"></param>
|
/// <returns></returns>
|
public bool inMat(List<ErpMat> ErpMat)
|
{
|
for (int i = 0; i < ErpMat.Count; i++)
|
{
|
StringBuilder hql = new StringBuilder();
|
hql.Append("select * from Log_Stroe_Mat where MatNo='" + ErpMat[i].MatNo + "'");
|
DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(hql);
|
if (dt.Rows.Count > 0)
|
{
|
int quant = int.Parse(dt.Rows[0]["Quant"].ToString()) + int.Parse(ErpMat[i].CurQuant);
|
hql.Append("Update Log_Stroe_Mat set Quant='" + quant + "' where MatNo='" + ErpMat[i].MatNo + "'");
|
DataFactory.SqlDataBase().ExecuteBySql(hql);
|
}
|
else
|
{
|
|
hql.Append("insert into Log_Stroe_Mat (MatNo,Quant)values('" + ErpMat[i].MatNo + "','" + ErpMat[i].CurQuant + "')");
|
DataFactory.SqlDataBase().ExecuteBySql(hql);
|
}
|
}
|
return true;
|
|
}
|
|
public bool IsExist(string MatGuid)
|
{
|
bool result = false;
|
try
|
{
|
StringBuilder sbStr = new StringBuilder();
|
sbStr.Append("select count(id) from Material ");
|
sbStr.Append("where IsDel = 0 ");
|
sbStr.Append(" and guid = '" + MatGuid + "';");
|
DataRow dr = DataFactory.SqlDataBase().GetDataRowBySQL(sbStr);
|
if (dr[0].ToString() == "0")
|
{
|
result = true;
|
}
|
|
return result;
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
|
/// <summary>
|
/// 便携组盘成功
|
/// </summary>
|
/// <param name="PalletNo"></param>
|
/// <param name="OrdNo"></param>
|
/// <returns></returns>
|
public string AddIPalletBind(string PalletNo, string OrdNo, string user, string LoginDepartNum)
|
{
|
try
|
{
|
string messAgestr = "";
|
DataTable dt = this.GetOrdNoDetails(OrdNo);
|
if (dt.Rows.Count > 0)
|
{
|
int rowCount = 0;
|
StringBuilder sqlString = new StringBuilder();
|
//sqlString.Append("select GETDATE() as CreateTime;");
|
//DataTable dtt = DataFactory.SqlDataBase().GetDataTableBySQL(sqlString);
|
//string CreateTime = dtt.Rows[0][0].ToString();
|
|
foreach (DataRow row in dt.Rows)
|
{
|
sqlString.Clear();
|
sqlString.Append($@"
|
INSERT INTO IPalletBind
|
(
|
Palno,
|
MatNo,
|
MatGuid,
|
MatCount,
|
OrdNo,
|
LocationCode,
|
Statu,
|
Demo,
|
DepartGuid,
|
CreateUser,
|
CreateTime,
|
IsDel
|
)
|
SELECT '{PalletNo}',
|
'{row["MatNo"].ToString()}',
|
'{row["MatGuid"].ToString()}',
|
'{row["CurQuant"].ToString()}',
|
'{OrdNo}',
|
'',
|
'01',
|
'',
|
'{LoginDepartNum}',
|
'{user}',
|
GETDATE(),
|
'0';
|
");
|
//sqlString.Append("Insert into IPalletBind (PalletNo,MatNo,MatCount,OrdNo,LingNo,");
|
//sqlString.Append("TuNo,GoodsPos,Statu,CreateUser,CreateTime,isDel) values ");
|
//sqlString.Append("('" + PalletNo + "','" + row["MatNo"].ToString() + "','");
|
//sqlString.Append(row["CurQuant"].ToString() + "','" + row["OrdNo"].ToString() + "','");
|
//sqlString.Append(row["LingNo"].ToString() + "','" + row["TuNo"].ToString() + "','");
|
//sqlString.Append("','01','" + user + "','" + CreateTime + "','0');");
|
rowCount += DataFactory.SqlDataBase().ExecuteBySql(sqlString);
|
}
|
|
if (rowCount >= dt.Rows.Count)
|
{
|
sqlString.Clear();
|
sqlString.Append("update Erp_in set Statu = '02' where Ordno = '" + OrdNo + "'");
|
DataFactory.SqlDataBase().ExecuteBySql(sqlString);
|
messAgestr = "组盘成功！";
|
}
|
else
|
{
|
messAgestr = "组盘失败，请检查入库单。！";
|
}
|
}
|
else
|
{
|
messAgestr = "组盘失败，请检查入库单。";
|
}
|
|
return messAgestr;
|
}
|
catch
|
{
|
return "系统异常，请重试!";
|
}
|
}
|
|
public DataTable GetOrdNoDetails(string Ordno)
|
{
|
try
|
{
|
StringBuilder sqlString = new StringBuilder();
|
sqlString.Append($@"
|
SELECT tb2.MatNo AS MatGuid,
|
tb3.MatNo,
|
tb2.CurQuant,
|
tb1.Ordno,
|
tb2.LingNo,
|
tb2.TuNo,
|
tb2.PageNo
|
FROM Erp_in AS tb1
|
LEFT JOIN ErpInDetail AS tb2
|
ON tb1.Ordno = tb2.OrdNo
|
LEFT JOIN Material AS tb3
|
ON tb3.guid = tb2.MatNo
|
WHERE tb1.Statu = '01'
|
AND tb1.Ordno = '{Ordno}';
|
");
|
//sqlString.Append("select tb2.MatNo,tb2.CurQuant,tb1.Ordno,tb2.LingNo,tb2.TuNo,tb2.PageNo ");
|
//sqlString.Append("from Erp_in as tb1 left join ErpInDetail as tb2 on tb1.Ordno = tb2.OrdNo ");
|
//sqlString.Append("where tb1.Statu = '01' and tb1.Ordno = '" + Ordno + "'");
|
|
return DataFactory.SqlDataBase().GetDataTableBySQL(sqlString);
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
|
/// <summary>
|
/// 判断是否存在正在执行的组盘信息
|
/// </summary>
|
/// <param name="ordNo"></param>
|
/// <returns></returns>
|
public bool IsIpallets(string ordNo)
|
{
|
bool result = false;
|
try
|
{
|
StringBuilder sbStr = new StringBuilder();
|
sbStr.Append("select COUNT(*) from IPalletBind where OrdNo = '" + ordNo + "' ");
|
sbStr.Append("and Statu = '2' and isdel = '0';");
|
DataRow dr = DataFactory.SqlDataBase().GetDataRowBySQL(sbStr);
|
if (dr[0].ToString() == "0")
|
{
|
result = true;
|
}
|
|
return result;
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
|
/// <summary>
|
/// 删除指定单据的组盘信息
|
/// </summary>
|
/// <param name="ordNo"></param>
|
/// <returns></returns>
|
public void DelIpallets(string ordNo)
|
{
|
StringBuilder sbStr = new StringBuilder();
|
sbStr.Append("update IPalletBind set IsDel = '1',Demo='关单移除' ");
|
sbStr.Append("where OrdNo = '" + ordNo + "' and Statu = '1' and IsDel = '0';");
|
DataFactory.SqlDataBase().ExecuteBySql(sbStr);
|
}
|
|
private string GetOrdNo()
|
{
|
try
|
{
|
string ordNo = "";
|
Hashtable ht1 = new Hashtable();
|
ht1.Add("sCode", "IDNO");
|
ht1.Add("OUT_sResult", "");
|
|
Hashtable rs = new Hashtable();
|
if (DataFactory.SqlDataBase().ExecuteByProcReturn("GetSerialNo", ht1, ref rs) != 0)
|
{
|
ordNo = rs["OUT_sResult"].ToString();
|
}
|
|
return ordNo;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
}
|
}
|
