using System;
|
using System.Collections.Generic;
|
using System.Text;
|
using Model;
|
using Common;
|
using System.Data;
|
using System.Collections;
|
using Commom.Utility;
|
using System.Linq;
|
using System.Threading.Tasks;
|
using System.Reflection;
|
|
namespace BLL
|
{
|
public class DALCheckData : IDALCheckData
|
{
|
public IList<CheckData> GetList(AjaxCheckDataList Json, ref PageInfo page)
|
{
|
try
|
{
|
IList<CheckData> list = new List<CheckData>();
|
StringBuilder strSql = new StringBuilder();
|
List<SqlParam> para = new List<SqlParam>();
|
strSql.Append("Select * from View_CheckData_User where IsDel = 0 " + $" AND DepartGuid='{Json.DepartGuid}' ");
|
|
if (!string.IsNullOrEmpty(Json.CDNo))
|
{
|
strSql.Append("and CDNo like '%' + @CDNo + '%' ");
|
para.Add(new SqlParam("@CDNo", Json.CDNo));
|
}
|
|
if (!string.IsNullOrEmpty(Json.Statu))
|
{
|
strSql.Append("and StatuId = @Statu ");
|
para.Add(new SqlParam("@Statu", Json.Statu));
|
}
|
|
if (Json.BeCreateTime != DateTime.MinValue && Json.BeCreateTime != null && Json.BeCreateTime != DateTime.MaxValue)
|
{
|
strSql.Append("and CreatTime >= @CreateTime1 ");
|
para.Add(new SqlParam("@CreateTime1", Convert.ToDateTime(Json.BeCreateTime).ToShortDateString()));
|
}
|
|
if (Json.EnCreateTime != DateTime.MinValue && Json.EnCreateTime != null && Json.EnCreateTime != DateTime.MaxValue)
|
{
|
strSql.Append("and CreatTime <= @CreateTime2 ");
|
para.Add(new SqlParam("@CreateTime2", Convert.ToDateTime(Json.EnCreateTime).ToShortDateString() + " 23:59:59.999"));
|
}
|
|
SqlParam[] param = null;
|
if (para != null)
|
param = para.ToArray();
|
|
DataTable dt = DataFactory.SqlDataBase().GetPageList(strSql.ToString(), param, "CreatTime", "desc", ref page);
|
list = ModelConvertHelper<CheckData>.DataTableToModel(dt);
|
|
return list;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
|
}
|
|
public CheckData GetModel(string CDNo)
|
{
|
try
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append($"SELECT * FROM dbo.View_CheckData_User WHERE CDNO='{CDNo}'");
|
IDataReader dt = DataFactory.SqlDataBase().GetDataReaderBySQL(strSql);
|
|
return ModelConvertHelper<CheckData>.ReaderToModel(dt);
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
|
public DataTable GetCheckData(string CDNo)
|
{
|
try
|
{
|
StringBuilder cdStr = new StringBuilder();
|
cdStr.Append("select tb1.CDNo,tb2.TypeName as Statu,tb1.Demo,tb1.CreatTime,tb3.RealName as Creatuser ");
|
cdStr.Append("from CheckData as tb1 ");
|
cdStr.Append("left join Dictionary as tb2 on tb1.Statu = tb2.code ");
|
cdStr.Append("left join UserInfo as tb3 on tb1.CreatUser = tb3.UserCode ");
|
cdStr.Append("where tb1.isdel = 0 and tb2.topCode = 'CDStatu' and tb1.CDNo = " + CDNo.AddQuotes());
|
DataTable dtt = DataFactory.SqlDataBase().GetDataTableBySQL(cdStr);
|
|
return dtt;
|
}
|
catch
|
{
|
return null;
|
}
|
}
|
|
public CheckData GetModel(string CDNo, ref IList<CheckMat> erpMats)
|
{
|
try
|
{
|
CheckData us = null;
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append(" Select * from CheckData where ");
|
strSql.Append("CDNo = @CDNo and IsDel = 0 ");
|
SqlParam[] para = new SqlParam[]
|
{
|
new SqlParam("@CDNo", CDNo),
|
};
|
|
IDataReader dt = DataFactory.SqlDataBase().GetDataReaderBySQL(strSql, para);
|
|
us = ModelConvertHelper<CheckData>.ReaderToModel(dt);
|
|
DALCheckDetail erpInDetail = new DALCheckDetail();
|
erpMats = erpInDetail.GetList(CDNo);
|
|
return us;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
|
public bool Add(CheckData model, List<CheckMat> Mats)
|
{
|
bool result = false;
|
try
|
{
|
Hashtable ht1 = new Hashtable();
|
Hashtable rs = new Hashtable();
|
ht1["sCode"] = "CDNO";
|
ht1["OUT_sResult"] = "";
|
int num = DataFactory.SqlDataBase().ExecuteByProcReturn("GetSerialNo", ht1, ref rs);
|
if (num == 0)
|
{
|
return result;
|
}
|
string CDNo = rs["OUT_sResult"].ToString();
|
|
Hashtable ht = new Hashtable();
|
ht["CDNo"] = CDNo.AddQuotes();
|
ht["Statu"] = "'01'";
|
ht["AccessCode"] = model.AccessCode.AddQuotes();
|
ht["DepartGuid"] = model.DepartGuid.AddQuotes();
|
ht["CreatUser"] = model.CreatUser.AddQuotes();
|
ht["Demo"] = model.Demo.AddQuotes();
|
int _ret = DataFactory.SqlDataBase().InsertByHashtableNullParam("CheckData", ht);
|
if (_ret != 1)
|
{
|
return result;
|
}
|
|
result = this.SetCDDetail(CDNo, Mats, model.CreatUser);
|
|
return result;
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
|
public bool Update(CheckData model, List<CheckMat> Mats)
|
{
|
bool result = false;
|
try
|
{
|
// 修改总表
|
Hashtable ht = new Hashtable();
|
ht["Demo"] = string.IsNullOrEmpty(model.Demo) ? "''" : "'" + model.Demo + "'";
|
ht["AccessCode"] = model.AccessCode.AddQuotes();
|
int _ret = DataFactory.SqlDataBase().UpdateByHashtable("CheckData", "CDNo", model.CDNo.AddQuotes(), ht);
|
if (_ret == 1)
|
{
|
StringBuilder sqlStr = new StringBuilder();
|
sqlStr.Append("Delete From CheckDataDetail where CDNo = " + model.CDNo.AddQuotes() + ";");
|
int rowCount = DataFactory.SqlDataBase().ExecuteBySql(sqlStr);
|
if (rowCount > 0)
|
{
|
result = this.SetCDDetail(model.CDNo, Mats, model.CreatUser);
|
}
|
}
|
|
return result;
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
|
public bool UpdateAudit(CheckData movePos)
|
{
|
bool result = false;
|
try
|
{
|
Hashtable ht = new Hashtable();
|
|
|
ht["AuditStatu"] = "'AD01'";
|
ht["Statu"] = "'ER04'";
|
ht["CDNo"] = "'" + movePos.CDNo + "'";
|
|
|
Hashtable ht1 = new Hashtable();
|
ht1["AuditStatu"] = "'" + movePos.AuditStatu + "'";
|
|
ht1["AuditUser"] = "'" + movePos.AuditUser + "'";
|
|
|
int _ret = DataFactory.SqlDataBase().UpdateByHashtable("CheckData", ht1, ht);
|
if (_ret == 1) result = true;
|
|
return result;
|
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
|
public bool UpdateClose(string[] CDNo, string Statu)
|
{
|
bool result = false;
|
try
|
{
|
Hashtable ht = new Hashtable();
|
ht["Statu"] = "ER03";
|
|
int dt = DataFactory.SqlDataBase().IsExist("CheckData", "CDNo", CDNo, ht);
|
if (dt >= CDNo.Length)
|
{
|
int _ret = DataFactory.SqlDataBase().BatchDeleteData("CheckData", "CDNo", CDNo);
|
if (_ret >= CDNo.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("CheckData", 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[] CDNo)
|
{
|
bool result = false;
|
try
|
{
|
foreach (string cn in CDNo)
|
{
|
StringBuilder sql = new StringBuilder();
|
sql.Append("update CheckData set IsDel=1 where CDNO=" + cn.AddQuotes() + ";");
|
sql.Append("Update CheckDataDetail set IsDel = 1 where CDNO=" + cn.AddQuotes() + ";");
|
int _ret = DataFactory.SqlDataBase().ExecuteBySql(sql);
|
if (_ret > 0)
|
{
|
result = true;
|
}
|
}
|
|
return result;
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
|
public IList<AjaxStoreSel> GetMaterial(AjaxStoreSel Json)
|
{
|
try
|
{
|
StringBuilder stringBuilder = new StringBuilder();
|
stringBuilder.Append("select * from View_Store_Mat where ISNULL(MatNo,'0') <> '0' " + $" AND DepartGuid='{Json.DepartGuid}' ");
|
// 库区
|
if (!string.IsNullOrEmpty(Json.MatNo))
|
{
|
stringBuilder.Append(" and MatNo like '%" + Json.MatNo + "%'");
|
}
|
if (!string.IsNullOrEmpty(Json.MatName))
|
{
|
stringBuilder.Append(" and MatName like '%" + Json.MatName + "%'");
|
}
|
if (!string.IsNullOrEmpty(Json.LingNo))
|
{
|
stringBuilder.Append(" and LingNo like '%" + Json.LingNo + "%'");
|
}
|
if (!string.IsNullOrEmpty(Json.LocationCode))
|
{
|
stringBuilder.Append(" and LocationCode like '%" + Json.LocationCode + "%'");
|
}
|
if (!string.IsNullOrEmpty(Json.BatchNo))
|
{
|
stringBuilder.Append(" and BatchNo like '%" + Json.BatchNo + "%'");
|
}
|
if (!string.IsNullOrEmpty(Json.BatchName))
|
{
|
stringBuilder.Append(" and BatchName like '%" + Json.BatchName + "%'");
|
}
|
|
|
DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(stringBuilder);
|
IList<AjaxStoreSel> list = ModelConvertHelper<AjaxStoreSel>.DataTableToModel(dt);
|
|
return list;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
|
public bool SetCDDetail(string CDNo, List<CheckMat> EMats, string LoginUserCode)
|
{
|
bool result = false;
|
try
|
{
|
List<SqlParam> para = new List<SqlParam>();
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append(" Insert into CheckDataDetail ");
|
strSql.Append("(CDNO,MatNo,MatName,PackFormat,Unit,Quant,LocationCode,Palno,LingNo,CreatUser)");
|
strSql.Append(" Values ");
|
|
foreach (CheckMat emat in EMats)
|
{
|
strSql.Append("(" + CDNo.AddQuotes() + "," + emat.MatNo.AddQuotes());
|
strSql.Append("," + emat.MatName.AddQuotes() + "," + emat.PackFormat.AddQuotes());
|
strSql.Append("," + emat.UnitFrist.AddQuotes() + "," + emat.Quant.AddQuotes());
|
strSql.Append("," + emat.LocationCode.AddQuotes() + "," + emat.Palno.AddQuotes());
|
strSql.Append("," + emat.LingNo.AddQuotes() + "," + LoginUserCode.AddQuotes() + "),");
|
}
|
|
SqlParam[] param = null;
|
if (EMats.Count > 0)
|
{
|
strSql = strSql.Remove(strSql.Length - 1, 1);
|
int rowCount = DataFactory.SqlDataBase().ExecuteBySql(strSql, param);
|
if (EMats.Count == rowCount)
|
{
|
result = true;
|
}
|
}
|
|
return result;
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
|
/// <summary>
|
/// 生成盘库任务
|
/// </summary>
|
/// <param name="CDNo"></param>
|
/// <param name="loginUserCode"></param>
|
/// <returns></returns>
|
public bool BuildTask(string CDNo, string loginUserCode, string departGuid)
|
{
|
try
|
{
|
StringBuilder cdStr = new StringBuilder();
|
cdStr.Append("select LocationCode,Palno,Demo from [dbo].[CheckDataDetail] ");
|
cdStr.Append("where CDNO = " + CDNo.AddQuotes());
|
cdStr.Append("group by LocationCode,Palno,Demo;");
|
DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(cdStr);
|
if (dt.Rows.Count <= 0)
|
{
|
return false;
|
}
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append(@"
|
INSERT INTO dbo.CheckTask
|
(
|
CDNO,
|
LocationCode1,
|
Palno1,
|
statu,
|
Demo,
|
DepartGuid,
|
CreatTime,
|
CreatUser
|
)
|
VALUES
|
");
|
foreach (DataRow dataRow in dt.Rows)
|
{
|
strSql.Append($@"
|
(
|
'{CDNo}',
|
'{dataRow["LocationCode"].ToString()}',
|
'{dataRow["Palno"].ToString()}',
|
'01',
|
'{dataRow["Demo"].ToString()}',
|
'{departGuid}',
|
GETDATE(),
|
'{loginUserCode}'
|
),");
|
}
|
|
strSql = strSql.Remove(strSql.Length - 1, 1);
|
strSql.Append(";Update CheckData set Statu = '02' where CDNo=" + CDNo.AddQuotes() + ";");
|
int rowCount = DataFactory.SqlDataBase().ExecuteBySql(strSql);
|
if (rowCount > 0)
|
{
|
return true;
|
}
|
|
return false;
|
}
|
catch
|
{
|
return false;
|
}
|
}
|
|
/// <summary>
|
/// 根据盘库单更改库存数量
|
/// </summary>
|
/// <param name="CDNo"></param>
|
/// <param name="loginUserCode"></param>
|
/// <returns></returns>
|
public bool AdjustInventory(string CDNo, string loginUserCode)
|
{
|
bool result = false;
|
try
|
{
|
// 获取盘点明细
|
StringBuilder cdStr = new StringBuilder();
|
cdStr.Append("select MatNo,LingNo,LocationCode,Palno,Quant,guid,AQuant ");
|
cdStr.Append("from CheckDataDetail ");
|
cdStr.Append("where case isnull(AQuant,'') when '' then '-1' else AQuant end <> -1 and IsDel = 0 and CDNO = " + CDNo.AddQuotes() + ";");
|
DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(cdStr);
|
|
// 循环取得每种物料的盘点量
|
int rowcount = 0;
|
int rowNum = 0;
|
StringBuilder setSql = new StringBuilder();
|
foreach (DataRow row in dt.Rows)
|
{
|
int quant = int.Parse(row["Quant"].ToString());
|
int aQuant = int.Parse(row["AQuant"].ToString());
|
string value = (aQuant - quant).ToString();
|
|
// 插入出入库明细表
|
setSql.Append("Insert Into InOutDetails (");
|
setSql.Append("OrdNo,FkGuid,LingNo,LocationCode,Palno,MatNo,Quant,CreateUser)");
|
setSql.Append(" values (" + CDNo.AddQuotes() + "," + row["guid"].ToString().AddQuotes() + ",");
|
setSql.Append(row["LingNo"].ToString().AddQuotes() + ",");
|
setSql.Append(row["LocationCode"].ToString().AddQuotes() + ",");
|
setSql.Append(row["Palno"].ToString().AddQuotes() + ",");
|
setSql.Append(row["MatNo"].ToString().AddQuotes() + ",");
|
setSql.Append(value.AddQuotes() + "," + loginUserCode.AddQuotes() + "); ");
|
// 修改库存明细表
|
setSql.Append("Update log_Store set Quant = Quant + " + value);
|
setSql.Append(" where LocationCode = " + row["LocationCode"].ToString().AddQuotes());
|
setSql.Append(" and isnull(LingNo,'') = " + row["LingNo"].ToString().AddQuotes());
|
setSql.Append(" and Palno = " + row["Palno"].ToString().AddQuotes());
|
setSql.Append(" and isnull(MatNo,'') = " + row["MatNo"].ToString().AddQuotes() + "; ");
|
// 修改库存总表
|
if (row["MatNo"].ToString() != "")
|
{
|
setSql.Append("Update Log_Stroe_Mat set Quant = Quant + " + value);
|
setSql.Append(" where MatNo = " + row["MatNo"].ToString().AddQuotes() + "; ");
|
}
|
|
rowcount += 1;
|
if (rowcount == 10)
|
{
|
rowNum = DataFactory.SqlDataBase().ExecuteBySql(setSql);
|
if (rowNum > 0)
|
{
|
setSql.Clear();
|
rowcount = 0;
|
rowNum = 0;
|
}
|
}
|
}
|
|
if (rowcount > 0)
|
{
|
rowNum = DataFactory.SqlDataBase().ExecuteBySql(setSql);
|
if (rowNum > 0)
|
{
|
setSql.Clear();
|
rowNum = 0;
|
}
|
}
|
|
// 更改盘点单状态 、逻辑删除指令
|
setSql.Append("Update CheckData set Statu = '03',COMTime=getdate() where CDNO =" + CDNo.AddQuotes() + "; ");
|
// 删除任务状态。
|
setSql.Append("Update CheckTask set isDel = '1' where CDNO =" + CDNo.AddQuotes() + "; ");
|
// 删除相关任务指令
|
//setSql.Append("Update WH_CMD set IsDel = '1' where NumberNo =" + CDNo.AddQuotes() + ";");
|
rowNum = DataFactory.SqlDataBase().ExecuteBySql(setSql);
|
if (rowNum > 0)
|
{
|
result = true;
|
}
|
}
|
catch
|
{
|
return false;
|
}
|
|
return result;
|
}
|
}
|
}
|
