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;
}
}