public bool QueryStockSumByWHcode(string WHcode, out List <Stock_Model> list, out string strErrMsg)
{
try
{
string sql = @"select materialno, materialdesc,materialstd,SUM(qty) as sumqty from T_STOCK where warehouseno = '" + WHcode + "' group by materialno,materialdesc,materialstd,warehouseno";
list = new List <Stock_Model>();
using (SqlDataReader dr = OperationSql.ExecuteReader(System.Data.CommandType.Text, sql))
{
while (dr.Read())
{
Stock_Model model = new Stock_Model();
model.MaterialNo = dr["materialno"].ToDBString();
model.MaterialENDesc = dr["materialdesc"].ToDBString();
model.MaterialStd = dr["materialstd"].ToDBString();
model.Qty = dr["sumqty"].ToDecimal();
list.Add(model);
}
}
strErrMsg = "";
return(true);
}
catch (Exception ex)
{
list = null;
strErrMsg = ex.Message;
return(false);
}
}
public bool GetStockDetailListByPage(ref List <Stock_Model> modelList, Stock_Model model, ref DividPage page, UserInfo user, ref string strError)
{
if (page == null)
{
page = new DividPage();
}
List <Stock_Model> lstModel = new List <Stock_Model>();
try
{
using (SqlDataReader dr = Common_DB.QueryByDividPage(ref page, "V_StockDetail", GetFilterSql(model, user), "*", "Order By CreateTime desc,WarehouseNo,HouseNo,AreaNo,MaterialNo,Qty"))
{
while (dr.Read())
{
lstModel.Add(GetModelFromDataReader(dr));
}
}
modelList = lstModel;
return(true);
}
catch (Exception ex)
{
strError = ex.Message;
return(false);
}
finally
{
}
}
public List <Stock_Model> GetStockByAreaNo(string strAreaNo)
{
try
{
string strSql = "select (case when materialno is null then tmaterialno else materialno end) as materialno,(case when materialdesc is null then tmaterialdesc else materialdesc end) as materialdesc,sum(QTY) as qty from t_stock where areano ='{0}' " +
"group by materialno,materialdesc,tmaterialno,tmaterialdesc";
strSql = string.Format(strSql, strAreaNo);
List <Stock_Model> lstStock = new List <Stock_Model>();
using (SqlDataReader dr = OperationSql.ExecuteReader(System.Data.CommandType.Text, strSql))
{
while (dr.Read())
{
Stock_Model SM = new Stock_Model();
SM.MaterialNo = dr["materialno"].ToDBString();
SM.MaterialENDesc = dr["materialdesc"].ToDBString();
SM.Qty = dr["qty"].ToDecimal();
lstStock.Add(SM);
}
}
return(lstStock);
}
catch (Exception ex)
{
throw ex;
}
}
public List <Stock_Model> GetStockByMaterialNo(string strMaterialNo)
{
try
{
List <Stock_Model> lstStock = new List <Stock_Model>();
string strSql = string.Format("select * from v_getstockbymaterialno where materialno = '{0}'", strMaterialNo);
using (SqlDataReader dr = OperationSql.ExecuteReader(System.Data.CommandType.Text, strSql))
{
while (dr.Read())
{
Stock_Model stockModel = new Stock_Model();
stockModel.MaterialNo = dr["materialno"].ToDBString();
stockModel.AreaNo = dr["areano"].ToDBString();
stockModel.MaterialENDesc = dr["materialdesc"].ToDBString();
stockModel.Qty = dr["qty"].ToDecimal();
lstStock.Add(stockModel);
}
}
return(lstStock);
}
catch (Exception ex)
{
throw ex;
}
}
public bool QueryStockSumByArraySerialNo(out List <StockHead_Model> list, string strArraySerialNo, out string strErrMsg)
{
string strSerialNo = "";
string[] ArrayMaterialNo = strArraySerialNo.Split(',');
foreach (var item in ArrayMaterialNo)
{
if (string.IsNullOrEmpty(strSerialNo))
{
strSerialNo = "N'" + item + "'";
}
else
{
strSerialNo += (",N'" + item + "'");
}
}
try
{
string sql = @"select materialno,batchno,Descr,CHDesc,dMDate,dVDate,shelflife,qty ,num,serialno from T_BARCODE where 1=1 AND serialno IN (" + strSerialNo + ")";
list = new List <StockHead_Model>();
using (SqlDataReader dr = OperationSql.ExecuteReader(System.Data.CommandType.Text, sql))
{
while (dr.Read())
{
Stock_Model model = new Stock_Model();
model.MaterialNo = dr["materialno"].ToDBString();
model.MaterialENDesc = dr["materialdesc"].ToDBString();
model.MaterialCHDesc = dr["MaterialCHDesc"].ToCHString();
model.Qty = dr["qty"].ToDecimal();
model.Num = dr["num"].ToInt32();
model.BatchNo = dr["batchno"].ToDBString();
model.SerialNo = dr["serialno"].ToDBString();
StockHead_Model head = list.Find(s => s.MaterialNo.Equals(model.MaterialNo) && s.BatchNo.Equals(model.BatchNo));
if (head == null)
{
head = new StockHead_Model(model);
list.Add(head);
}
else
{
head.iQuantity += model.Qty;
head.iNum += model.Num;
head.lstStockInfo.Add(model);
}
}
}
strErrMsg = "";
return(true);
}
catch (Exception ex)
{
list = null;
strErrMsg = ex.Message;
return(false);
}
}
public List <Stock_Model> GetStockByArrayMaterialNo(string VoucherNo, string strArrayMaterialNo)
{
string strMaterialNo = "";
string[] ArrayMaterialNo = strArrayMaterialNo.Split(',');
foreach (var item in ArrayMaterialNo)
{
if (string.IsNullOrEmpty(strMaterialNo))
{
strMaterialNo = "N'" + item + "'";
}
else
{
strMaterialNo += (",N'" + item + "'");
}
}
try
{
List <Stock_Model> lstStock = new List <Stock_Model>();
string strSql = "";
if (string.IsNullOrEmpty(VoucherNo))
{
strSql = @"select * from v_getstockbymaterialno where 1=1 and materialno in (" + strMaterialNo + ")";
}
else
{
strSql = @"select a.materialno,a.materialdesc,b.materialstd,warehouseno,areano,b.voucherno,ISNULL(sum(a.qty),0) qty from T_STOCK a join T_OUTBARCODE b on a.serialno=b.serialno
where a.materialno in (" + strMaterialNo + ") and b.voucherno='" + VoucherNo + "' group by a.materialno,a.materialdesc,b.materialstd,warehouseno,areano,b.voucherno";
}
using (SqlDataReader dr = OperationSql.ExecuteReader(System.Data.CommandType.Text, strSql))
{
while (dr.Read())
{
Stock_Model stockModel = new Stock_Model();
stockModel.MaterialNo = dr["materialno"].ToDBString();
stockModel.AreaNo = dr["areano"].ToDBString();
stockModel.MaterialENDesc = dr["materialdesc"].ToDBString();
stockModel.MaterialStd = dr["materialstd"].ToDBString();
stockModel.Qty = dr["qty"].ToDecimal();
if (!string.IsNullOrEmpty(VoucherNo))
{
stockModel.VoucherNo = dr["VoucherNo"].ToDBString();
}
lstStock.Add(stockModel);
}
}
return(lstStock);
}
catch (Exception ex)
{
throw ex;
}
}
public StockHead_Model(Stock_Model child)
{
this.MaterialNo = child.MaterialNo;
this.BatchNo = child.BatchNo;
this.MaterialCHDesc = child.MaterialCHDesc;
this.MaterialENDesc = child.MaterialENDesc;
this.ShelfLife = child.ShelfLife;
this.iQuantity = child.Qty;
this.iNum = child.Num;
this.lstStockInfo = new List <Stock_Model> {
child
};
}
public List <Stock_Model> GetStockBySerialNo(string strSerialNo)
{
try
{
List <Stock_Model> lstStock = new List <Stock_Model>();
string strSql = string.Format(@"SELECT b.qty,a.materialno,a.materialdesc,a.materialstd,b.areano,b.warehouseno,b.houseno,
c.warehousename,d.housename,e.areaname
FROM[Barcode].[dbo].[T_OUTBARCODE] a
join[Barcode].[dbo].[T_STOCK] b on a.serialno = b.serialno
left join [Barcode].[dbo].[T_AREA] e on e.areano=b.areano
left join [Barcode].[dbo].[T_house] d on d.id=e.houseid
left join [Barcode].[dbo].[T_warehouse] c on c.id=d.warehouseid
where a.serialno = '{0}'", strSerialNo);
using (SqlDataReader dr = OperationSql.ExecuteReader(System.Data.CommandType.Text, strSql))
{
while (dr.Read())
{
Stock_Model stockModel = new Stock_Model();
stockModel.MaterialNo = dr["materialno"].ToDBString();
stockModel.AreaNo = dr["areano"].ToDBString();
stockModel.MaterialENDesc = dr["materialdesc"].ToDBString();
stockModel.MaterialStd = dr["MaterialStd"].ToDBString();
stockModel.AreaName = dr["AreaName"].ToDBString();
stockModel.HouseName = dr["HouseName"].ToDBString();
stockModel.WarehouseName = dr["WarehouseName"].ToDBString();
stockModel.HouseNo = dr["HouseNo"].ToDBString();
stockModel.WarehouseNo = dr["WarehouseNo"].ToDBString();
stockModel.Qty = dr["qty"].ToDecimal();
stockModel.SerialNo = strSerialNo;
lstStock.Add(stockModel);
}
}
return(lstStock);
}
catch (Exception ex)
{
throw ex;
}
}
private Stock_Model GetModelFromDataReader(SqlDataReader dr)
{
Stock_Model model = new Stock_Model();
if (Common_Func.readerExists(dr, "StockType"))
{
model.StockType = dr["StockType"].ToInt32();
}
if (Common_Func.readerExists(dr, "Barcode"))
{
model.Barcode = dr["Barcode"].ToDBString();
}
if (Common_Func.readerExists(dr, "SerialNo"))
{
model.SerialNo = dr["SerialNo"].ToDBString();
}
if (Common_Func.readerExists(dr, "MaterialNo"))
{
model.MaterialNo = dr["MaterialNo"].ToDBString();
}
if (Common_Func.readerExists(dr, "MaterialDesc"))
{
model.MaterialENDesc = dr["MaterialDesc"].ToDBString();
}
if (Common_Func.readerExists(dr, "WarehouseNo"))
{
model.WarehouseNo = dr["WarehouseNo"].ToDBString();
}
if (Common_Func.readerExists(dr, "HouseNo"))
{
model.HouseNo = dr["HouseNo"].ToDBString();
}
if (Common_Func.readerExists(dr, "AreaNo"))
{
model.AreaNo = dr["AreaNo"].ToDBString();
}
if (Common_Func.readerExists(dr, "Qty"))
{
model.Qty = dr["Qty"].ToDecimal();
}
if (Common_Func.readerExists(dr, "TempMaterialNo"))
{
model.TempMaterialNo = dr["TempMaterialNo"].ToDBString();
}
if (Common_Func.readerExists(dr, "TempMaterialDesc"))
{
model.TempMaterialDesc = dr["TempMaterialDesc"].ToDBString();
}
if (Common_Func.readerExists(dr, "PickAreaNo"))
{
model.PickAreaNo = dr["PickAreaNo"].ToDBString();
}
if (Common_Func.readerExists(dr, "CelAreaNo"))
{
model.CelAreaNo = dr["CelAreaNo"].ToDBString();
}
if (Common_Func.readerExists(dr, "BatchNo"))
{
model.BatchNo = dr["BatchNo"].ToDBString();
}
if (Common_Func.readerExists(dr, "StrStockType"))
{
model.StrStockType = dr["StrStockType"].ToDBString();
}
if (Common_Func.readerExists(dr, "WarehouseName"))
{
model.WarehouseName = dr["WarehouseName"].ToDBString();
}
if (Common_Func.readerExists(dr, "HouseName"))
{
model.HouseName = dr["HouseName"].ToDBString();
}
if (Common_Func.readerExists(dr, "AreaName"))
{
model.AreaName = dr["AreaName"].ToDBString();
}
if (Common_Func.readerExists(dr, "SN"))
{
model.SN = dr["SN"].ToDBString();
}
if (Common_Func.readerExists(dr, "Creater"))
{
model.Creater = dr["Creater"].ToDBString();
}
if (Common_Func.readerExists(dr, "CreateTime"))
{
model.CreateTime = dr["CreateTime"].ToDateTime();
}
return(model);
}
private string GetFilterSql(Stock_Model model, UserInfo user)
{
try
{
string strSql = " Where AreaNo is not null ";
bool hadWhere = true;
if (!string.IsNullOrEmpty(model.AreaNo))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " (AreaNo LIKE '%" + model.AreaNo + "%' OR AreaName LIKE '%" + model.AreaNo + "%') ";
hadWhere = true;
}
if (!string.IsNullOrEmpty(model.HouseNo))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " (HouseNo LIKE '%" + model.HouseNo + "%' OR HouseName LIKE '%" + model.HouseNo + "%') ";
hadWhere = true;
}
if (!string.IsNullOrEmpty(model.WarehouseNo))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " (WarehouseNo LIKE '%" + model.WarehouseNo + "%' OR WarehouseName LIKE '%" + model.WarehouseNo + "%') ";
hadWhere = true;
}
if (!string.IsNullOrEmpty(model.MaterialNo))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " (MaterialNo LIKE '%" + model.MaterialNo + "%' OR MaterialDesc LIKE '%" + model.MaterialNo + "%') ";
hadWhere = true;
}
if (!string.IsNullOrEmpty(model.Barcode))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " Barcode Like '%" + model.Barcode + "%' ";
hadWhere = true;
}
if (!string.IsNullOrEmpty(model.SerialNo))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " SerialNo LIKE '%" + model.SerialNo + "%' ";
hadWhere = true;
}
//if (model.BatchNo.Equals("NULL"))
//{
// strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
// strSql += " BatchNo is null ";
// hadWhere = true;
//}
if (!string.IsNullOrEmpty(model.BatchNo))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " BatchNo LIKE '%" + model.BatchNo + "%' ";
hadWhere = true;
}
if (!string.IsNullOrEmpty(model.SN))
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " SN LIKE '%" + model.SN + "%' ";
hadWhere = true;
}
if (model.StartTime != null)
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " CreateTime >= " + model.StartTime.ToDateTime().Date.ToSqlTimeString() + " ";
hadWhere = true;
}
if (model.EndTime != null)
{
strSql = Common_Func.AddWhereAnd(strSql, hadWhere);
strSql += " CreateTime <= " + model.EndTime.ToDateTime().AddDays(1).Date.ToSqlTimeString() + " ";
hadWhere = true;
}
return(strSql);
}
catch
{
return(string.Empty);
}
}
public List <Stock_Model> GetCapacity(QueryConditions list)
{
if (list == null)
{
return(null);
}
try
{
List <Stock_Model> lstStock = new List <Stock_Model>();
string strSql = @"select T.materialno,T.materialdesc,T.materialstd,T.ProductLineNo
, SUM(T.ErpQty) ErpQty, SUM(T.SaveQty) SaveQty, SUM(T.trayQty) trayQty, SUM(T.ErpQty + T.SaveQty + T.trayQty) TotalQty
from (
select b.materialno, b.materialdesc, b.materialstd, a.ProductLineNo
, SUM(b.qty) trayQty, 0 SaveQty, 0 ErpQty
from T_TRAY a
join T_OUTBARCODE b on a.ID = b.TRAYID
where 1 = 1 and ISNULL(b.iFlag, 0) = 0
--0and a.ProductLineNo = @ProductLineNo
--1and b.MaterialNo=@MaterialNo
--2and a.lastModifyDt>=@BeginDate
--3and a.lastModifyDt<=@EndDate
--4and b.materialdesc like @MaterialDesc
--5and b.materialstd like @MaterialStd
--6and b.VoucherNo=@VoucherNo
group by b.materialno, b.materialdesc, b.materialstd, a.ProductLineNo
union all
select a.materialno, a.materialdesc, a.materialstd, b.ProductLineNo
, 0 trayQty, SUM(a.qty) SaveQty, 0 ErpQty
from T_ProductInDetails a
join T_ProductInRecord b on a.ID = b.ID
where 1 = 1 and b.cstate = -1
--0and b.ProductLineNo = @ProductLineNo
--1and a.MaterialNo=@MaterialNo
--2and b.lastupdatetime>=@BeginDate
--3and b.lastupdatetime<=@EndDate
--4and a.materialdesc like @MaterialDesc
--5and a.materialstd like @MaterialStd
--6and b.VoucherNo=@VoucherNo
group by a.materialno, a.materialdesc, a.materialstd, b.ProductLineNo
union all
select a.materialno, a.materialdesc, a.materialstd, b.ProductLineNo
, 0 trayQty, 0 SaveQty, SUM(a.qty) ErpQty
from T_ProductInDetails a
join T_ProductInRecord b on a.ID = b.ID
where 1 = 1 and b.cstate = 0
--0and b.ProductLineNo = @ProductLineNo
--1and a.MaterialNo=@MaterialNo
--2and b.lastupdatetime>=@BeginDate
--3and b.lastupdatetime<=@EndDate
--4and a.materialdesc like @MaterialDesc
--5and a.materialstd like @MaterialStd
--6and b.VoucherNo=@VoucherNo
group by a.materialno, a.materialdesc, a.materialstd, b.ProductLineNo
) T
GROUP BY T.materialno, T.materialdesc, T.materialstd,T.ProductLineNo";
if (!string.IsNullOrEmpty(list.ProductLineNo))
{
strSql = strSql.Replace("--0", "").Replace("@ProductLineNo", list.ProductLineNo.ToSelSqlString());
}
if (!string.IsNullOrEmpty(list.MaterialNo))
{
strSql = strSql.Replace("--1", "").Replace("@MaterialNo", list.MaterialNo.ToSelSqlString());
}
if (list.StartTime != null)
{
string BeginDate = list.StartTime.ToDateTime().ToString("yyyy-MM-dd");
strSql = strSql.Replace("--2", "").Replace("@BeginDate", BeginDate.ToSelSqlString());
}
if (list.EndTime != null)
{
string EndDate = list.EndTime.ToDateTime().AddDays(1).ToString("yyyy-MM-dd");
strSql = strSql.Replace("--3", "").Replace("@EndDate", EndDate.ToSelSqlString());
}
if (!string.IsNullOrEmpty(list.MaterialNo))
{
strSql = strSql.Replace("--4", "").Replace("@MaterialDesc", list.MaterialDesc.ToLikeSqlString());
}
if (!string.IsNullOrEmpty(list.MaterialStd))
{
strSql = strSql.Replace("--5", "").Replace("@MaterialStd", list.MaterialStd.ToLikeSqlString());
}
if (!string.IsNullOrEmpty(list.VoucherNo))
{
strSql = strSql.Replace("--6", "").Replace("@VoucherNo", list.VoucherNo.ToSelSqlString());
}
using (SqlDataReader dr = OperationSql.ExecuteReader(System.Data.CommandType.Text, strSql))
{
while (dr.Read())
{
Stock_Model stockModel = new Stock_Model();
stockModel.ProductLineNo = dr["ProductLineNo"].ToDBString();
stockModel.MaterialNo = dr["materialno"].ToDBString();
stockModel.MaterialENDesc = dr["materialdesc"].ToDBString();
stockModel.MaterialStd = dr["materialstd"].ToDBString();
stockModel.TotalQty = dr["TotalQty"].ToDouble();
stockModel.TrayQty = dr["TrayQty"].ToDouble();
stockModel.SaveQty = dr["SaveQty"].ToDouble();
stockModel.ErpQty = dr["ErpQty"].ToDouble();
lstStock.Add(stockModel);
}
}
return(lstStock);
}
catch (Exception ex)
{
throw ex;
}
}