|
using Model;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Text;
|
using Common;
|
using System.Collections;
|
using Commom.Utility;
|
|
namespace BLL
|
{
|
public class DALStore : IDALStore
|
{
|
public bool IsExist(string name, string value)
|
{
|
bool result = false;
|
try
|
{
|
string[] para = new string[] { value };
|
int dt = DataFactory.SqlDataBase().IsExist("PaTask", name, para);
|
if (dt > 0) result = true;
|
return result;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
public bool IsExist(Hashtable ht)
|
{
|
bool result = false;
|
try
|
{
|
int dt = DataFactory.SqlDataBase().IsExist("PaTask", ht);
|
if (dt > 0) result = true;
|
return result;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
public bool Add(Store model)
|
{
|
bool result = false;
|
try
|
{
|
Hashtable ht = new Hashtable();
|
|
//ht["UserCode"] = "'" + model.UserCode + "'";
|
//ht["Username"] = "'" + model.Username + "'";
|
//ht["Pwd"] = "'" + Md5Helper.Md5(model.Pwd, 32) + "'";
|
//ht["RealName"] = "'" + model.RealName + "'";
|
//ht["RoleNum"] = "(select RoleNum from Roles where RoleName ='" + model.RoleName + "')";
|
//ht["DepartNum"] = "(select DepartNum from DepartMent where DepartName ='" + model.DepartName + "')";
|
|
//ht["Mobile"] = "'"+model.Mobile + "'";
|
//ht["Phone"] = "'" + model.Phone + "'";
|
//ht["Email"] = "'" + model.email + "'";
|
//ht["Ord"] = "(select Max(Ord) from PaTask) + 1";
|
//ht["IsDel"] = 0;
|
//ht["CreatUser"] = "'"+ model.CreatUser + "'";
|
//ht["UpdateTime"] = "GetDate()";
|
//ht["CreatTime"] = "GetDate()";
|
//ht["Demo"] = "'" + model.Demo + "'";
|
//ht["UpdateUser"] = "'" + model.CreatUser + "'";
|
|
ht["guid"] = "NEWID()";
|
|
int _ret = DataFactory.SqlDataBase().InsertByHashtableNullParam("PaTask", ht);
|
|
if (_ret == 1) result = true;
|
|
return result;
|
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
|
public bool Update(Store model)
|
{
|
bool result = false;
|
try
|
{
|
Hashtable ht = new Hashtable();
|
|
//ht["RealName"] = string.IsNullOrEmpty(model.RealName) ? "''" : "'" + model.RealName+"'" ;
|
//ht["RoleNum"] = "(select RoleNum from Roles where RoleName ='" + model.RoleName + "')";
|
//ht["DepartNum"] = "(select DepartNum from DepartMent where DepartName ='" + model.DepartName + "')";
|
//ht["Mobile"] = string.IsNullOrEmpty(model.Mobile) ? "''" : "'" + model.Mobile + "'";
|
//ht["Phone"] = string.IsNullOrEmpty(model.Phone) ? "''" : "'" + model.Phone + "'";
|
//ht["Email"] = string.IsNullOrEmpty(model.email) ? "''" : "'" + model.email + "'";
|
//ht["UpdateTime"] = "GetDate()";
|
//ht["UpdateUser"] = string.IsNullOrEmpty(model.UpdateUser) ? "''" : "'" + model.UpdateUser + "'";
|
//ht["Demo"] = string.IsNullOrEmpty(model.Demo) ? "''" : "'" + model.Demo + "'";
|
|
//string UserCode = "'" + model.UserCode + "'";
|
|
//int _ret = DataFactory.SqlDataBase().UpdateByHashtable("PaTask", "UserCode", UserCode, ht);
|
|
//if (_ret == 1) result = true;
|
|
return result;
|
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
public bool Update(string UserCode, string Pwd, string UpdateUser)
|
{
|
bool result = false;
|
try
|
{
|
Hashtable ht = new Hashtable();
|
|
ht["Pwd"] = string.IsNullOrEmpty(Pwd) ? "''" : "'" + Md5Helper.Md5(Pwd, 32) + "'";
|
if (UpdateUser != null)
|
{
|
ht["UpdateUser"] = "'" + UpdateUser + "'";
|
ht["UpdateTime"] = "getdate()";
|
UserCode = "'" + UserCode + "'";
|
}
|
int _ret = DataFactory.SqlDataBase().UpdateByHashtable("PaTask", "UserCode", UserCode, ht);
|
|
if (_ret == 1) result = true;
|
|
return result;
|
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
public PaTask DataRowToModel(DataRow row)
|
{
|
throw new NotImplementedException();
|
}
|
|
public bool Delete(string UserCode)
|
{
|
bool result = false;
|
try
|
{
|
Hashtable ht = new Hashtable();
|
|
int _ret = DataFactory.SqlDataBase().DeleteData("PaTask", "UserCode", UserCode);
|
|
if (_ret == 1) result = true;
|
|
return result;
|
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
|
public bool BatchDelete(string[] TaskId)
|
{
|
bool result = false;
|
try
|
{
|
int dt = DataFactory.SqlDataBase().IsExist("Task", "TaskId", TaskId);
|
if (dt >= TaskId.Length)
|
{
|
int _ret = DataFactory.SqlDataBase().BatchDeleteData("Task", "TaskId", TaskId);
|
if (_ret >= TaskId.Length) result = true;
|
}
|
return result;
|
|
}
|
catch
|
{
|
return result;
|
}
|
}
|
|
public IList<Store> GetList()
|
{
|
throw new NotImplementedException();
|
}
|
|
/// <summary>
|
/// 获取库存信息
|
/// </summary>
|
/// <param name="Json"></param>
|
/// <param name="pageInfo"></param>
|
/// <returns></returns>
|
public IList<StorePalno> GetList(AjaxBasicList Json, ref PageInfo pageInfo)
|
{
|
try
|
{
|
IList<StorePalno> list = new List<StorePalno>();
|
StringBuilder strSql = new StringBuilder();
|
List<SqlParam> para = new List<SqlParam>();
|
strSql.Append("select distinct tb1.MatNo,tb3.MatName,tb1.Quant,tb3.PackFormat,tb4.UnitName as Unit,tb1.Demo1,tb1.Demo ");
|
strSql.Append("from Log_Stroe_Mat as tb1 ");
|
strSql.Append("left join log_Store as tb2 on tb1.MatNo = tb2.MatNo ");
|
strSql.Append("left join Material as tb3 on tb1.MatNo = tb3.MatNo ");
|
strSql.Append("left join Log_Unit as tb4 on tb3.UnitFrist = tb4.UnitNum where 1=1 ");
|
|
|
if (Json.MatNo != null && Json.MatNo != "")
|
{
|
strSql.Append(" and tb1.MatNo like '%" + Json.MatNo + "%' ");
|
para.Add(new SqlParam("@MatNo", Json.MatNo));
|
}
|
|
if (Json.MatName != null && Json.MatName != "")
|
{
|
strSql.Append(" and tb3.MatName like '%" + Json.MatName + "%' ");
|
para.Add(new SqlParam("@MatName", Json.MatName));
|
}
|
|
// 库位
|
if (Json.AddreNo != null && Json.AddreNo != "")
|
{
|
strSql.Append(" and tb2.addre like '%" + Json.AddreNo + "%' ");
|
para.Add(new SqlParam("@PurchaseNo", Json.AddreNo));
|
}
|
|
// 库区
|
if (Json.AreaNo != null && Json.AreaNo != "")
|
{
|
strSql.Append(" and tb2.addre like '%" + Json.AreaNo + "%' ");
|
para.Add(new SqlParam("@ItemNo", Json.AreaNo));
|
}
|
|
if (Json.PackFormat != null && Json.PackFormat != "")
|
{
|
strSql.Append(" and tb3.PackFormat like '%" + Json.PackFormat + "%' ");
|
para.Add(new SqlParam("@ItemName", Json.PackFormat));
|
}
|
|
SqlParam[] param = null;
|
if (para != null)
|
param = para.ToArray();
|
|
DataTable dt = DataFactory.SqlDataBase().GetPageList(strSql.ToString(), param, "MatNo", "DESC", ref pageInfo);
|
|
list = ModelConvertHelper<StorePalno>.DataTableToModel(dt);
|
|
return list;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
|
public DataSet GetStoreList(string MatNo, string MatName, string AreaNo, string AddreNo, string PackFormat, string userName)
|
{
|
try
|
{
|
DataSet ds = new DataSet("ds1");
|
DataTable dt = new DataTable();
|
StringBuilder sbstr = new StringBuilder();
|
sbstr.Append("select StorageName,'" + userName + "' as UserName,CONVERT(varchar(10),GETDATE(),120) as CreateTime");
|
sbstr.Append(" from StorageArea where StorageNo= '" + AreaNo + "';");
|
dt = DataFactory.SqlDataBase().GetDataTableBySQL(sbstr);
|
dt.TableName = "PortHead";
|
ds.Tables.Add(dt);
|
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("select distinct tb1.MatNo,tb3.MatName,tb1.Quant,tb3.PackFormat as Addre,tb4.UnitName,tb1.Demo1 as Palno,tb1.Demo ");
|
strSql.Append("from Log_Stroe_Mat as tb1 ");
|
strSql.Append("left join log_Store as tb2 on tb1.MatNo = tb2.MatNo ");
|
strSql.Append("left join Material as tb3 on tb1.MatNo = tb3.MatNo ");
|
strSql.Append("left join Log_Unit as tb4 on tb3.UnitFrist = tb4.UnitNum where 1=1 ");
|
|
MatNo = MatNo.Trim();
|
if (MatNo != null && MatNo != "")
|
{
|
strSql.Append(" and tb1.MatNo like '%" + MatNo + "%' ");
|
}
|
MatName = MatName.Trim();
|
if (MatName != null && MatName != "")
|
{
|
strSql.Append(" and tb3.MatName like '%" + MatName + "%' ");
|
}
|
AddreNo = AddreNo.Trim();
|
if (AddreNo != null && AddreNo != "")
|
{
|
strSql.Append(" and tb2.addre like '%" + AddreNo + "%' ");
|
}
|
AreaNo = AreaNo.Trim();
|
if (AreaNo != null && AreaNo != "")
|
{
|
strSql.Append(" and tb2.addre like '%" + AreaNo + "%' ");
|
}
|
PackFormat = PackFormat.Trim();
|
if (PackFormat != null && PackFormat != "")
|
{
|
strSql.Append(" and tb3.PackFormat like '%" + PackFormat + "%' ");
|
}
|
|
DataTable dt1 = DataFactory.SqlDataBase().GetDataTableBySQL(strSql);
|
dt1.TableName = "StoreDetail";
|
ds.Tables.Add(dt1);
|
|
return ds;
|
}
|
catch (Exception)
|
{
|
throw new NotImplementedException();
|
}
|
}
|
public IList<StorePalno> GetLogStore(AjaxBasicList Json, ref PageInfo pageInfo)
|
{
|
try
|
{
|
|
IList<StorePalno> list = new List<StorePalno>();
|
StringBuilder strSql = new StringBuilder();
|
List<SqlParam> para = new List<SqlParam>();
|
strSql.Append("Select * from View_Log_Store where Quant !='0' and matno is not null ");
|
|
|
if (Json.MatNo != null && Json.MatNo != "")
|
{
|
strSql.Append(" and ");
|
strSql.Append("MatNo like '%' + @MatNo + '%' ");
|
para.Add(new SqlParam("@MatNo", Json.MatNo));
|
}
|
|
if (Json.MatName != null && Json.MatName != "")
|
{
|
strSql.Append(" and ");
|
strSql.Append("MatName like '%' + @MatName + '%' ");
|
para.Add(new SqlParam("@MatName", Json.MatName));
|
}
|
|
if (Json.Ling_No != null && Json.Ling_No != "")
|
{
|
strSql.Append(" and ");
|
strSql.Append("Ling_No like '%' + @Ling_No + '%' ");
|
para.Add(new SqlParam("@Ling_No", Json.Ling_No));
|
}
|
|
if (Json.PurchaseNo != null && Json.PurchaseNo != "")
|
{
|
strSql.Append(" and ");
|
strSql.Append("PurchaseNo like '%' + @PurchaseNo + '%' ");
|
para.Add(new SqlParam("@PurchaseNo", Json.PurchaseNo));
|
}
|
|
if (Json.ItemNo != null && Json.ItemNo != "")
|
{
|
strSql.Append(" and ");
|
strSql.Append("ItemNo like '%' + @ItemNo + '%' ");
|
para.Add(new SqlParam("@ItemNo", Json.ItemNo));
|
}
|
|
if (Json.ItemName != null && Json.ItemName != "")
|
{
|
strSql.Append(" and ");
|
strSql.Append("ItemName like '%' + @ItemName + '%' ");
|
para.Add(new SqlParam("@ItemName", Json.ItemName));
|
}
|
|
if (Json.BeCreateTime != null && Json.BeCreateTime != DateTime.MinValue && Json.BeCreateTime != DateTime.MaxValue)
|
{
|
strSql.Append(" and ");
|
strSql.Append("CreateTime >= @CreateTime1");
|
para.Add(new SqlParam("@CreateTime1", Json.BeCreateTime));
|
}
|
if (Json.EnCreateTime != null && Json.EnCreateTime != DateTime.MinValue && Json.EnCreateTime != DateTime.MaxValue)
|
{
|
strSql.Append(" and ");
|
strSql.Append("CreateTime <= @CreateTime2");
|
para.Add(new SqlParam("@CreateTime2", Json.EnCreateTime));
|
}
|
|
|
SqlParam[] param = null;
|
if (para != null)
|
param = para.ToArray();
|
|
DataTable dt = DataFactory.SqlDataBase().GetPageList(strSql.ToString(), param, "CreateTime", "DESC", ref pageInfo);
|
|
list = ModelConvertHelper<StorePalno>.DataTableToModel(dt);
|
|
return list;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
/// <summary>
|
/// 托盘出库弹出框托盘信息
|
/// </summary>
|
/// <param name="Json"></param>
|
/// <param name="pageInfo"></param>
|
/// <returns></returns>
|
public IList<Storek> GetPalletStore(AjaxBasicList Json, ref PageInfo pageInfo)
|
{
|
try
|
{
|
|
IList<Storek> list = new List<Storek>();
|
StringBuilder strSql = new StringBuilder();
|
List<SqlParam> para = new List<SqlParam>();
|
//strSql.Append("Select * from View_Log_Store where Quant !='0' and MatNo is null ");
|
strSql.Append("Select * from View_Log_Store where TurnoverDemand='02' AND ISNULL(MatNo,'')='' ");
|
|
if (Json.PackFormat != null && Json.PackFormat != "")
|
{
|
strSql.Append(" and ");
|
strSql.Append("Palno like '%' + @Palno + '%' ");
|
para.Add(new SqlParam("@Palno", Json.PackFormat));
|
}
|
|
if (Json.Palno != null && Json.Palno != "")
|
{
|
strSql.Append(" and ");
|
strSql.Append("Palno like '%' + @Palno + '%' ");
|
para.Add(new SqlParam("@Palno", Json.Palno));
|
}
|
|
|
if (Json.BeCreateTime != null && Json.BeCreateTime != DateTime.MinValue && Json.BeCreateTime != DateTime.MaxValue)
|
{
|
strSql.Append(" and ");
|
strSql.Append("CreateTime >= @CreateTime1");
|
para.Add(new SqlParam("@CreateTime1", Json.BeCreateTime));
|
}
|
if (Json.EnCreateTime != null && Json.EnCreateTime != DateTime.MinValue && Json.EnCreateTime != DateTime.MaxValue)
|
{
|
strSql.Append(" and ");
|
strSql.Append("CreateTime <= @CreateTime2");
|
para.Add(new SqlParam("@CreateTime2", Json.EnCreateTime));
|
}
|
|
|
SqlParam[] param = null;
|
if (para != null)
|
param = para.ToArray();
|
|
DataTable dt = DataFactory.SqlDataBase().GetPageList(strSql.ToString(), param, "IsDel", "DESC", ref pageInfo);
|
|
list = ModelConvertHelper<Storek>.DataTableToModel(dt);
|
|
return list;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
|
public IList<Store> GetList(AjaxStoreList Json, ref PageInfo pageInfo)
|
{
|
try
|
{
|
|
IList<Store> list = new List<Store>();
|
StringBuilder strSql = new StringBuilder();
|
List<SqlParam> para = new List<SqlParam>();
|
strSql.Append("Select * from View_Log_Store_Palno ");
|
|
|
if (Json.Palno != null && Json.Palno != "")
|
{
|
if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
|
strSql.Append("Palno like '%' + @Palno + '%' ");
|
para.Add(new SqlParam("@Palno", Json.Palno));
|
}
|
|
if (Json.BeCreateTime != null && Json.BeCreateTime != DateTime.MinValue && Json.BeCreateTime != DateTime.MaxValue)
|
{
|
if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
|
strSql.Append("CreateTime >= @CreateTime1");
|
para.Add(new SqlParam("@CreateTime1", Json.BeCreateTime));
|
}
|
if (Json.EnCreateTime != null && Json.EnCreateTime != DateTime.MinValue && Json.EnCreateTime != DateTime.MaxValue)
|
{
|
if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
|
strSql.Append("CreateTime <= @CreateTime2");
|
para.Add(new SqlParam("@CreateTime2", Json.EnCreateTime));
|
}
|
|
|
SqlParam[] param = null;
|
if (para != null)
|
param = para.ToArray();
|
|
DataTable dt = DataFactory.SqlDataBase().GetPageList(strSql.ToString(), param, "CreateTime", "DESC", ref pageInfo);
|
|
list = ModelConvertHelper<Store>.DataTableToModel(dt);
|
|
return list;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
|
public IList<StoreNullPalno> GetList(AjaxNullPalnoList Json, ref PageInfo pageInfo)
|
{
|
try
|
{
|
|
IList<StoreNullPalno> list = new List<StoreNullPalno>();
|
StringBuilder strSql = new StringBuilder();
|
List<SqlParam> para = new List<SqlParam>();
|
strSql.Append("Select * from Log_Store_Palno ");
|
|
|
|
if (Json.Palno != null && Json.Palno != "")
|
{
|
if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
|
strSql.Append("Palno like '%' + @Palno + '%' ");
|
para.Add(new SqlParam("@Palno", Json.Palno));
|
}
|
|
if (Json.BeCreateTime != null && Json.BeCreateTime != DateTime.MinValue && Json.BeCreateTime != DateTime.MaxValue)
|
{
|
if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
|
strSql.Append("CreateTime >= @CreateTime1");
|
para.Add(new SqlParam("@CreateTime1", Json.BeCreateTime));
|
}
|
if (Json.EnCreateTime != null && Json.EnCreateTime != DateTime.MinValue && Json.EnCreateTime != DateTime.MaxValue)
|
{
|
if (para.Count < 1) strSql.Append("where "); else strSql.Append(" and ");
|
strSql.Append("CreateTime <= @CreateTime2");
|
para.Add(new SqlParam("@CreateTime2", Json.EnCreateTime));
|
}
|
|
|
SqlParam[] param = null;
|
if (para != null)
|
param = para.ToArray();
|
|
DataTable dt = DataFactory.SqlDataBase().GetPageList(strSql.ToString(), param, "CreateTime", "DESC", ref pageInfo);
|
|
list = ModelConvertHelper<StoreNullPalno>.DataTableToModel(dt);
|
|
return list;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
|
public Store GetModel(string usercode)
|
{
|
try
|
{
|
Store us = null;
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("Select UserCode,UserName,RealName,Phone,Mobile,Email,RoleName,DepartName,CreatUser,CreatTime,UpdateUser,UpdateTime,Demo,Ord,IsDel from View_UserInfo where ");
|
strSql.Append("UserCode = @UserCode ");
|
strSql.Append("and IsDel != 1");
|
SqlParam[] para = new SqlParam[]
|
{
|
new SqlParam("@UserCode", usercode),
|
};
|
|
IDataReader dt = DataFactory.SqlDataBase().GetDataReaderBySQL(strSql, para);
|
|
us = ModelConvertHelper<Store>.ReaderToModel(dt);
|
|
return us;
|
}
|
catch
|
{
|
throw new NotImplementedException();
|
}
|
}
|
|
|
public DataTable GetDataTable(string[] strWhere)
|
{
|
DataTable dt = null;
|
try
|
{
|
int index = 0;
|
string str = "@UserCode" + index;
|
SqlParam[] param = new SqlParam[strWhere.Length];
|
StringBuilder sql = new StringBuilder();
|
sql.Append("Select Username as 用户名,UserCode as 员工编号,RealName as 真名,Email as 邮箱,Mobile as 手机,Phone as 固话,CreatTime as 创建时间,DepartName as 部门,RoleName as 角色,Demo as 备注 FROM View_UserInfo where UserCode in (");
|
|
for (int i = 0; i < param.Length - 1; i++)
|
{
|
string obj2 = strWhere[i];
|
str = "@UserCode" + index;
|
sql.Append(str).Append(",");
|
param[index] = new SqlParam(str, obj2);
|
index++;
|
}
|
str = "@UserCode" + 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 DataTable GetDataTable(int PageSize, int PageIndex, string strWhere)
|
{
|
throw new NotImplementedException();
|
}
|
|
/// <summary>
|
/// 获取库存明细
|
/// </summary>
|
/// <param name="MatNo"></param>
|
/// <returns></returns>
|
public DataTable GetStoreDetail(string MatNo, string departGuid)
|
{
|
try
|
{
|
// 若物料编码为空则查询的为空托盘，空托盘无需考虑部门权限
|
if (MatNo == "")
|
{
|
departGuid = "";
|
}
|
|
StringBuilder sbStr = new StringBuilder();
|
sbStr.Append($@"
|
SELECT tb1.LingNo Ling_No,
|
tb1.LocationCode Addre,
|
tb1.Palno,
|
tb1.TuNo,
|
tb1.MatNo,
|
tb2.MatName,
|
tb2.PackFormat,
|
cast(tb1.Quant as varchar(50)) Quant,
|
tb3.UnitName,
|
dl.Property AS ALockId,
|
tb4.TypeName AS ALock
|
FROM log_Store AS tb1
|
LEFT JOIN Material AS tb2
|
ON tb1.MatGuid = tb2.guid AND tb2.IsDel=0
|
LEFT JOIN Unit AS tb3
|
ON tb2.Unit = tb3.Guid
|
JOIN dbo.DepotsLocation dl
|
ON tb1.LocationCode=dl.LocationCode
|
LEFT JOIN
|
(SELECT * FROM Dictionary WHERE TopCode = 'Property') AS tb4
|
ON dl.Property = tb4.Code
|
WHERE isnull(tb1.MatNo,'') ='{MatNo}' AND isnull(tb1.DepartGuid,'')='{departGuid}';");
|
DataTable dt = DataFactory.SqlDataBase().GetDataTableBySQL(sbStr);
|
return dt;
|
}
|
catch (Exception)
|
{
|
throw new NotImplementedException();
|
}
|
}
|
}
|
}
|
