protected void btnImport_Click(object sender, ImageClickEventArgs e)
{
StockAccountModel model = new StockAccountModel();
model.CompanyCD = ((UserInfoUtil)SessionUtil.Session["UserInfo"]).CompanyCD;
model.ProductNo = txtProductNo.Value;
model.ProductName = txtProductName.Value;
string AlarmType = sltAlarmType.Value;
string BarCode = HiddenBarCode.Value.Trim();
string orderBy = txtorderBy.Value;
if (!string.IsNullOrEmpty(orderBy))
{
if (orderBy.Split('_')[1] == "a")
{
orderBy = orderBy.Split('_')[0] + " asc";
}
else
{
orderBy = orderBy.Split('_')[0] + " desc";
}
}
DataTable dt = StorageProductAlarmBus.GetStorageProductAlarm(AlarmType, model, orderBy, BarCode);
OutputToExecl.ExportToTableFormat(this, dt,
new string[] { "物品编号", "物品名称", "物品分类", "规格", "单位", "库存下限", "库存上限", "安全库存", "现有存量", "报警类型" },
new string[] { "ProductNo", "ProductName", "TypeID", "Specification", "UnitID", "MinStockNum", "MaxStockNum", "SafeStockNum", "ProductCount", "AlarmType" },
"库存报警列表");
}
public static DataTable GetStockStructAnalysis(StockAccountModel model, string ordercolumn, string ordertype)
{
try
{
return(StockAccountDBHelper.GetStockStructAnalysis(model, ordercolumn, ordertype));
}
catch (Exception)
{
throw;
}
}
public static DataTable GetProductStockTotalPrice(StockAccountModel model, int pageIndex, int pageCount, string ord, ref int TotalCount, bool iflist)
{
try
{
return(StockAccountDBHelper.GetProductStockTotalPrice(model, pageIndex, pageCount, ord, ref TotalCount, iflist));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 产品入库信息（采购入库）
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DataTable GetInfoByInPurchase(StockAccountModel model, int pageIndex, int pageCount, string ord, ref int TotalCount, bool iflist)
{
try
{
return(StockAccountDBHelper.GetInfoByInPurchase(model, pageIndex, pageCount, ord, ref TotalCount, iflist));
}
catch (Exception)
{
throw;
}
}
public static DataTable GetProductInOutInfo(StockAccountModel model)
{
try
{
return(StockAccountDBHelper.GetProductInOutInfo(model));
}
catch (Exception)
{
throw;
}
}
//获取物品的销售和购进数量、总额、金额、税额
public static DataTable GetProdcutSellandPurchaseInfo(StockAccountModel model)
{
try
{
return(StockAccountDBHelper.GetProdcutSellandPurchaseInfo(model));
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 库存限量报警
/// </summary>
/// <param name="AlarmType">0-全部，1-上限报警，2-下限报警</param>
/// <param name="model"></param>
/// <returns>DataTable</returns>
public static DataTable GetStorageProductAlarm(string AlarmType, StockAccountModel model, int pageIndex, int pageCount, string ord, ref int TotalCount, bool iflist)
{
try
{
return(StockSearchDBHelper.GetStorageProductAlarm(AlarmType, model, pageIndex, pageCount, ord, ref TotalCount, iflist));
}
catch (Exception)
{
throw;
}
}
//出库查询
public static DataTable OutStockSearch(string Confirmor, StockAccountModel model, int pageIndex, int pageCount, string ord, ref int TotalCount, bool iflist)
{
try
{
return(StockSearchDBHelper.OutStockSearch(Confirmor, model, pageIndex, pageCount, ord, ref TotalCount, iflist));
}
catch (Exception)
{
throw;
}
}
public static DataTable GetStorageProductAlarm(string AlarmType, StockAccountModel model, string orderby, string BarCode)
{
try
{
return(StorageProductAlarmDBHelper.GetStorageProductAlarm(AlarmType, model, orderby, BarCode));
}
catch (Exception)
{
throw;
}
}
//库存流水账查询
public static DataTable GetLineInfo(string Confirmor, string type, StockAccountModel model, int pageIndex, int pageCount, string ord, ref int TotalCount, bool iflist)
{
StringBuilder sql = new StringBuilder();
SqlCommand comm = new SqlCommand();
sql.AppendLine("SELECT a.*,convert(char(20),Convert(decimal(18," + ((UserInfoUtil)SessionUtil.Session["UserInfo"]).SelPoint + "),ProductCount))+' ' ProductCount1 FROM officedba.V_StockLineInfo a");
sql.AppendLine(" where a.CompanyCD='" + model.CompanyCD + "' and a.ProdNo='" + model.ProductNo + "'");
if (!string.IsNullOrEmpty(Confirmor))
{
sql.AppendLine(" and a.ConfirmorID=@ConfirmorID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ConfirmorID", Confirmor));
}
if (!string.IsNullOrEmpty(model.StorageID))
{
sql.AppendLine("and a.StorageID=@StorageID ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID", model.StorageID));
}
if (!string.IsNullOrEmpty(type))
{
sql.AppendLine(" and a.Type=@type ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@type", type));
}
sql.AppendLine(" and a.Date>='" + model.StartDate + "' ");
sql.AppendLine(" and a.Date<'" + Convert.ToDateTime(model.EndDate).AddDays(1).ToString("yyyy-MM-dd") + "' ");
if (!iflist)
{
if (ord != "asc" && ord != "desc")
{
sql.AppendLine(" order by " + ord);
}
else
{
sql.AppendLine(" order by Date");
}
comm.CommandText = sql.ToString();
return(SqlHelper.ExecuteSearch(comm));
}
else
{
comm.CommandText = sql.ToString();
return(SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount));
}
}
public static DataTable GetStorageProductAlarm(string AlarmType, StockAccountModel model, string orderby, string BarCode)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("select ISNULL(a.ProdNo,'') as ProductNo,ISNULL(a.ProductName,'') as ProductName ");
sql.AppendLine(",ISNULL(d.CodeName,'') as TypeID ");
sql.AppendLine(",ISNULL(a.Specification,'') as Specification ");
sql.AppendLine(",ISNULL(c.CodeName,'') as UnitID ");
sql.AppendLine(",a.MinStockNum,a.MaxStockNum,ISNULL(a.SafeStockNum,0) as SafeStockNum ");
sql.AppendLine(",ISNULL(b.ProductCount,0) as ProductCount ");
sql.AppendLine(",case ");
sql.AppendLine("when (b.ProductCount > a.MaxStockNum) then '上限报警' ");
sql.AppendLine("when (b.ProductCount < a.MinStockNum) then '下限报警' ");
sql.AppendLine("else '' ");
sql.AppendLine("end AS AlarmType ");
sql.AppendLine("from officedba.ProductInfo a ");
sql.AppendLine("right join (select a.ProductID,sum(ISNULL(a.ProductCount,0)) as ProductCount ");
sql.AppendLine(" from officedba.StorageProduct a where a.CompanyCD=@CompanyCD ");
sql.AppendLine(" group by a.ProductID) b on a.ID=b.ProductID --从分仓存量表中查询出group by ProductID的数据 ");
sql.AppendLine("left join officedba.CodeUnitType c on c.ID=a.UnitID ");
sql.AppendLine("left join officedba.CodeProductType d on d.ID=a.TypeID ");
sql.AppendLine(" where a.CompanyCD=@CompanyCD ");
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
switch (AlarmType)
{
case "0":
sql.AppendLine("and (b.ProductCount>a.MaxStockNum ");
sql.AppendLine("or b.ProductCount<a.MinStockNum) ");
break;
case "1":
sql.AppendLine("and b.ProductCount>a.MaxStockNum ");
break;
case "2":
sql.AppendLine("and b.ProductCount<a.MinStockNum ");
break;
default:
break;
}
if (!string.IsNullOrEmpty(model.ProductNo))
{
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProdNo", model.ProductNo));
}
if (!string.IsNullOrEmpty(model.ProductName))
{
sql.AppendLine(" and a.ProductName like '%' + @ProductName + '%'");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductName", model.ProductName));
}
if (!string.IsNullOrEmpty(BarCode))
{
sql.AppendLine(" and a.BarCode like '%' + @BarCode + '%'");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BarCode", BarCode));
}
if (!string.IsNullOrEmpty(orderby))
{
sql.AppendLine(" order by " + orderby);
}
comm.CommandText = sql.ToString();
return(SqlHelper.ExecuteSearch(comm));
}
/// <summary>
/// 库存限量报警
/// </summary>
/// <param name="AlarmType">0-全部，1-上限报警，2-下限报警</param>
/// <param name="model"></param>
/// <returns>DataTable</returns>
public static DataTable GetStorageProductAlarm(string AlarmType, StockAccountModel model, string BarCode, int pageIndex, int pageCount, string ord, ref int TotalCount)
{
UserInfoUtil userInfo = (UserInfoUtil)SessionUtil.Session["UserInfo"];
StringBuilder sql = new StringBuilder();
sql.AppendLine("select ISNULL(a.ProdNo,'') as ProductNo,ISNULL(a.ProductName,'') as ProductName ");
sql.AppendLine(",ISNULL(d.CodeName,'') as TypeID ");
sql.AppendLine(",ISNULL(a.Specification,'') as Specification ");
sql.AppendLine(",ISNULL(c.CodeName,'') as UnitID ");
sql.AppendLine(",Convert(numeric(22," + userInfo.SelPoint + "),a.MinStockNum) as MinStockNum,Convert(numeric(22," + userInfo.SelPoint + "),a.MaxStockNum) as MaxStockNum,Convert(numeric(22," + userInfo.SelPoint + "),ISNULL(a.SafeStockNum,0)) as SafeStockNum ");
sql.AppendLine(",Convert(numeric(22," + userInfo.SelPoint + "),ISNULL(b.ProductCount,0)) as ProductCount ");
sql.AppendLine(",case ");
sql.AppendLine("when (b.ProductCount > a.MaxStockNum) then '上限报警' ");
sql.AppendLine("when (b.ProductCount < a.MinStockNum) then '下限报警' ");
sql.AppendLine("else '' ");
sql.AppendLine("end AS AlarmType ");
sql.AppendLine("from officedba.ProductInfo a ");
sql.AppendLine("right join (select a.ProductID,sum(ISNULL(a.ProductCount,0)) as ProductCount ");
sql.AppendLine(" from officedba.StorageProduct a where a.CompanyCD=@CompanyCD ");
sql.AppendLine(" group by a.ProductID) b on a.ID=b.ProductID --从分仓存量表中查询出group by ProductID的数据 ");
sql.AppendLine("left join officedba.CodeUnitType c on c.ID=a.UnitID ");
sql.AppendLine("left join officedba.CodeProductType d on d.ID=a.TypeID ");
sql.AppendLine(" where a.CompanyCD=@CompanyCD ");
SqlCommand comm = new SqlCommand();
//添加公司代码参数
comm.Parameters.Add(SqlHelper.GetParameterFromString("@CompanyCD", model.CompanyCD));
switch (AlarmType)
{
case "0":
sql.AppendLine("and (b.ProductCount>a.MaxStockNum ");
sql.AppendLine("or b.ProductCount<a.MinStockNum) ");
break;
case "1":
sql.AppendLine("and b.ProductCount>a.MaxStockNum ");
break;
case "2":
sql.AppendLine("and b.ProductCount<a.MinStockNum ");
break;
default:
break;
}
if (!string.IsNullOrEmpty(model.ProductNo))
{
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProdNo", model.ProductNo));
}
if (!string.IsNullOrEmpty(model.ProductName))
{
sql.AppendLine(" and a.ProductName like '%' + @ProductName + '%'");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@ProductName", model.ProductName));
}
if (!string.IsNullOrEmpty(BarCode))
{
sql.AppendLine(" and a.BarCode like '%' + @BarCode + '%'");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@BarCode", BarCode));
}
comm.CommandText = sql.ToString();
return(SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount));
}
//入库查询
public static DataTable InStockSearch(string Confirmor, StockAccountModel model, int pageIndex, int pageCount, string ord, ref int TotalCount, bool iflist)
{
StringBuilder sql = new StringBuilder();
SqlCommand comm = new SqlCommand();
sql.AppendLine("select ISNULL(convert(varchar(10),b.ConfirmDate,21),'') as Date,a.StorageID ");
sql.AppendLine(",ISNULL(c.StorageName,'') as StorageName ");
sql.AppendLine(",'采购入库' as [Type] ");
sql.AppendLine(",b.InNo as BillNo ");
sql.AppendLine(",ISNULL(d.EmployeeName,'') as Confirmor ");
sql.AppendLine(",a.ProductID ");
sql.AppendLine(",e.ProdNo ");
sql.AppendLine(",ISNULL(e.ProductName,'') as ProductName ");
sql.AppendLine(",ISNULL(f.CodeName,'') as UnitID ");
sql.AppendLine(",ISNULL(e.Specification,'') as Specification ");
sql.AppendLine(",a.ProductCount ");
sql.AppendLine(",ISNULL(b.Summary,'') as Summary ");
sql.AppendLine("from officedba.StorageInPurchaseDetail a ");
sql.AppendLine("inner join officedba.StorageInPurchase b on b.InNo=a.InNo and b.CompanyCD='" + model.CompanyCD + "' and a.CompanyCD=b.CompanyCD ");
sql.AppendLine("left join officedba.StorageInfo c on c.ID=a.StorageID ");
sql.AppendLine("left join officedba.EmployeeInfo d on d.ID=b.Confirmor ");
sql.AppendLine("left join officedba.ProductInfo e on e.ID=a.ProductID ");
sql.AppendLine("left join officedba.CodeUnitType f on f.ID=e.UnitID ");
sql.AppendLine("where b.CompanyCD='" + model.CompanyCD + "' and e.ProdNo='" + model.ProductNo + "' ");
if (!string.IsNullOrEmpty(Confirmor))
{
sql.AppendLine("and b.Confirmor=@Confirmor1 ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Confirmor1", Confirmor));
}
if (!string.IsNullOrEmpty(model.StorageID))
{
sql.AppendLine("and a.StorageID=@StorageID1 ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID2", model.StorageID));
}
sql.AppendLine("and b.ConfirmDate>='" + model.StartDate + "' ");
sql.AppendLine("and b.ConfirmDate<'" + Convert.ToDateTime(model.EndDate).AddDays(1).ToString("yyyy-MM-dd") + "' ");
sql.AppendLine(" ");
sql.AppendLine("union all ");
sql.AppendLine(" ");
sql.AppendLine("select ISNULL(convert(varchar(10),b.ConfirmDate,21),'') as Date,a.StorageID ");
sql.AppendLine(",ISNULL(c.StorageName,'') as StorageName ");
sql.AppendLine(",'生产完工入库' as [Type] ");
sql.AppendLine(",b.InNo as BillNo ");
sql.AppendLine(",ISNULL(d.EmployeeName,'') as Confirmor ");
sql.AppendLine(",a.ProductID ");
sql.AppendLine(",e.ProdNo ");
sql.AppendLine(",ISNULL(e.ProductName,'') as ProductName ");
sql.AppendLine(",ISNULL(f.CodeName,'') as UnitID ");
sql.AppendLine(",ISNULL(e.Specification,'') as Specification ");
sql.AppendLine(",a.ProductCount ");
sql.AppendLine(",ISNULL(b.Summary,'') as Summary ");
sql.AppendLine("from officedba.StorageInProcessDetail a ");
sql.AppendLine("inner join officedba.StorageInProcess b on b.InNo=a.InNo and b.CompanyCD='" + model.CompanyCD + "' and a.CompanyCD=b.CompanyCD ");
sql.AppendLine("left join officedba.StorageInfo c on c.ID=a.StorageID ");
sql.AppendLine("left join officedba.EmployeeInfo d on d.ID=b.Confirmor ");
sql.AppendLine("left join officedba.ProductInfo e on e.ID=a.ProductID ");
sql.AppendLine("left join officedba.CodeUnitType f on f.ID=e.UnitID ");
sql.AppendLine("where b.CompanyCD='" + model.CompanyCD + "' and e.ProdNo='" + model.ProductNo + "' ");
if (!string.IsNullOrEmpty(Confirmor))
{
sql.AppendLine("and b.Confirmor=@Confirmor2 ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Confirmor2", Confirmor));
}
if (!string.IsNullOrEmpty(model.StorageID))
{
sql.AppendLine("and a.StorageID=@StorageID2 ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID2", model.StorageID));
}
sql.AppendLine("and b.ConfirmDate>='" + model.StartDate + "' ");
sql.AppendLine("and b.ConfirmDate<'" + Convert.ToDateTime(model.EndDate).AddDays(1).ToString("yyyy-MM-dd") + "' ");
sql.AppendLine(" ");
sql.AppendLine("union all ");
sql.AppendLine(" ");
sql.AppendLine("select ISNULL(convert(varchar(10),b.ConfirmDate,21),'') as Date,a.StorageID ");
sql.AppendLine(",ISNULL(c.StorageName,'') as StorageName ");
sql.AppendLine(",'其他入库' as [Type] ");
sql.AppendLine(",b.InNo as BillNo ");
sql.AppendLine(",ISNULL(d.EmployeeName,'') as Confirmor ");
sql.AppendLine(",a.ProductID ");
sql.AppendLine(",e.ProdNo ");
sql.AppendLine(",ISNULL(e.ProductName,'') as ProductName ");
sql.AppendLine(",ISNULL(f.CodeName,'') as UnitID ");
sql.AppendLine(",ISNULL(e.Specification,'') as Specification ");
sql.AppendLine(",a.ProductCount ");
sql.AppendLine(",ISNULL(b.Summary,'') as Summary ");
sql.AppendLine("from officedba.StorageInOtherDetail a ");
sql.AppendLine("inner join officedba.StorageInOther b on b.InNo=a.InNo and b.CompanyCD='" + model.CompanyCD + "' and a.CompanyCD=b.CompanyCD ");
sql.AppendLine("left join officedba.StorageInfo c on c.ID=a.StorageID ");
sql.AppendLine("left join officedba.EmployeeInfo d on d.ID=b.Confirmor ");
sql.AppendLine("left join officedba.ProductInfo e on e.ID=a.ProductID ");
sql.AppendLine("left join officedba.CodeUnitType f on f.ID=e.UnitID ");
sql.AppendLine("where b.CompanyCD='" + model.CompanyCD + "' and e.ProdNo='" + model.ProductNo + "' ");
if (!string.IsNullOrEmpty(Confirmor))
{
sql.AppendLine("and b.Confirmor=@Confirmor3 ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Confirmor3", Confirmor));
}
if (!string.IsNullOrEmpty(model.StorageID))
{
sql.AppendLine("and a.StorageID=@StorageID3 ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID3", model.StorageID));
}
sql.AppendLine("and b.ConfirmDate>='" + model.StartDate + "' ");
sql.AppendLine("and b.ConfirmDate<'" + Convert.ToDateTime(model.EndDate).AddDays(1).ToString("yyyy-MM-dd") + "' ");
sql.AppendLine(" ");
sql.AppendLine("union all ");
sql.AppendLine(" ");
sql.AppendLine("select ISNULL(convert(varchar(10),b.ConfirmDate,21),'') as Date,a.StorageID ");
sql.AppendLine(",ISNULL(c.StorageName,'') as StorageName ");
sql.AppendLine(",'红冲入库' as [Type] ");
sql.AppendLine(",b.InNo as BillNo ");
sql.AppendLine(",ISNULL(d.EmployeeName,'') as Confirmor ");
sql.AppendLine(",a.ProductID ");
sql.AppendLine(",e.ProdNo ");
sql.AppendLine(",ISNULL(e.ProductName,'') as ProductName ");
sql.AppendLine(",ISNULL(f.CodeName,'') as UnitID ");
sql.AppendLine(",ISNULL(e.Specification,'') as Specification ");
sql.AppendLine(",a.ProductCount ");
sql.AppendLine(",ISNULL(b.Summary,'') as Summary ");
sql.AppendLine("from officedba.StorageInRedDetail a ");
sql.AppendLine("inner join officedba.StorageInRed b on b.InNo=a.InNo and b.CompanyCD='" + model.CompanyCD + "' and a.CompanyCD=b.CompanyCD ");
sql.AppendLine("left join officedba.StorageInfo c on c.ID=a.StorageID ");
sql.AppendLine("left join officedba.EmployeeInfo d on d.ID=b.Confirmor ");
sql.AppendLine("left join officedba.ProductInfo e on e.ID=a.ProductID ");
sql.AppendLine("left join officedba.CodeUnitType f on f.ID=e.UnitID ");
sql.AppendLine("where b.CompanyCD='" + model.CompanyCD + "' and e.ProdNo='" + model.ProductNo + "' ");
if (!string.IsNullOrEmpty(Confirmor))
{
sql.AppendLine("and b.Confirmor=@Confirmor4 ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@Confirmor4", Confirmor));
}
if (!string.IsNullOrEmpty(model.StorageID))
{
sql.AppendLine("and a.StorageID=@StorageID4 ");
comm.Parameters.Add(SqlHelper.GetParameterFromString("@StorageID4", model.StorageID));
}
sql.AppendLine("and b.ConfirmDate>='" + model.StartDate + "' ");
sql.AppendLine("and b.ConfirmDate<'" + Convert.ToDateTime(model.EndDate).AddDays(1).ToString("yyyy-MM-dd") + "' ");
comm.CommandText = sql.ToString();
if (!iflist)
{
if (!string.IsNullOrEmpty(ord))
{
sql.AppendLine(" order by " + ord);
}
return(SqlHelper.ExecuteSearch(comm));
}
else
{
return(SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, ord, ref TotalCount));
}
}
