/// <summary>
/// 取当指定时间指定仓库/产品,库存
/// </summary>
/// <param name="StorageID"></param>
/// <param name="ProductsID"></param>
/// <param name="dT"></param>
/// <returns></returns>
public ProductsStorageInfo GetProductsStorageModel(int StorageID, int ProductsID, DateTime dT)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 ProductsStorageID,StorageID,ProductsID,pStorage,pStorageIn,pStorageOut,pUpdateTime,(select top 1 isBad from tbStockProductInfo where sAppendTime<=@dT and ProductsID=@ProductsID and StorageID=@StorageID order by sAppendTime desc) as StorageBad from tbProductsStorageInfo ");
strSql.Append(" where StorageID=@StorageID and ProductsID=@ProductsID and datediff(day,pUpdateTime,@dT)=0");
SqlParameter[] parameters =
{
new SqlParameter("@StorageID", SqlDbType.Int, 4),
new SqlParameter("@ProductsID", SqlDbType.Int, 4),
new SqlParameter("@dT", SqlDbType.DateTime)
};
parameters[0].Value = StorageID;
parameters[1].Value = ProductsID;
parameters[2].Value = dT;
ProductsStorageInfo model = new ProductsStorageInfo();
DataSet ds = DbHelper.ExecuteDataset(CommandType.Text, strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["ProductsStorageID"].ToString() != "")
{
model.ProductsStorageID = int.Parse(ds.Tables[0].Rows[0]["ProductsStorageID"].ToString());
}
if (ds.Tables[0].Rows[0]["StorageID"].ToString() != "")
{
model.StorageID = int.Parse(ds.Tables[0].Rows[0]["StorageID"].ToString());
}
if (ds.Tables[0].Rows[0]["ProductsID"].ToString() != "")
{
model.ProductsID = int.Parse(ds.Tables[0].Rows[0]["ProductsID"].ToString());
}
if (ds.Tables[0].Rows[0]["pStorage"].ToString() != "")
{
model.pStorage = decimal.Parse(ds.Tables[0].Rows[0]["pStorage"].ToString());
}
if (ds.Tables[0].Rows[0]["pStorageIn"].ToString() != "")
{
model.pStorageIn = decimal.Parse(ds.Tables[0].Rows[0]["pStorageIn"].ToString());
}
if (ds.Tables[0].Rows[0]["pStorageOut"].ToString() != "")
{
model.pStorageOut = decimal.Parse(ds.Tables[0].Rows[0]["pStorageOut"].ToString());
}
if (ds.Tables[0].Rows[0]["StorageBad"].ToString() != "")
{
model.pStorageBad = decimal.Parse(ds.Tables[0].Rows[0]["StorageBad"].ToString());
}
if (ds.Tables[0].Rows[0]["pUpdateTime"].ToString() != "")
{
model.pUpdateTime = DateTime.Parse(ds.Tables[0].Rows[0]["pUpdateTime"].ToString());
}
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public void UpdateProductsStorage(ProductsStorageInfo model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update tbProductsStorageInfo set ");
strSql.Append("StorageID=@StorageID,");
strSql.Append("ProductsID=@ProductsID,");
strSql.Append("pStorage=@pStorage,");
strSql.Append("pStorageIn=@pStorageIn,");
strSql.Append("pStorageOut=@pStorageOut,");
strSql.Append("pUpdateTime=@pUpdateTime");
strSql.Append(" where ProductsStorageID=@ProductsStorageID ");
SqlParameter[] parameters =
{
new SqlParameter("@ProductsStorageID", SqlDbType.Int, 4),
new SqlParameter("@StorageID", SqlDbType.Int, 4),
new SqlParameter("@ProductsID", SqlDbType.Int, 4),
new SqlParameter("@pStorage", SqlDbType.Decimal, 9),
new SqlParameter("@pStorageIn", SqlDbType.Decimal, 9),
new SqlParameter("@pStorageOut", SqlDbType.Decimal, 9),
new SqlParameter("@pUpdateTime", SqlDbType.DateTime)
};
parameters[0].Value = model.ProductsStorageID;
parameters[1].Value = model.StorageID;
parameters[2].Value = model.ProductsID;
parameters[3].Value = model.pStorage;
parameters[4].Value = model.pStorageIn;
parameters[5].Value = model.pStorageOut;
parameters[6].Value = model.pUpdateTime;
DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public ProductsStorageInfo GetProductsStorageModel(int ProductsStorageID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 ProductsStorageID,StorageID,ProductsID,pStorage,pStorageIn,pStorageOut,pUpdateTime from tbProductsStorageInfo ");
strSql.Append(" where ProductsStorageID=@ProductsStorageID ");
SqlParameter[] parameters =
{
new SqlParameter("@ProductsStorageID", SqlDbType.Int, 4)
};
parameters[0].Value = ProductsStorageID;
ProductsStorageInfo model = new ProductsStorageInfo();
DataSet ds = DbHelper.ExecuteDataset(CommandType.Text, strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["ProductsStorageID"].ToString() != "")
{
model.ProductsStorageID = int.Parse(ds.Tables[0].Rows[0]["ProductsStorageID"].ToString());
}
if (ds.Tables[0].Rows[0]["StorageID"].ToString() != "")
{
model.StorageID = int.Parse(ds.Tables[0].Rows[0]["StorageID"].ToString());
}
if (ds.Tables[0].Rows[0]["ProductsID"].ToString() != "")
{
model.ProductsID = int.Parse(ds.Tables[0].Rows[0]["ProductsID"].ToString());
}
if (ds.Tables[0].Rows[0]["pStorage"].ToString() != "")
{
model.pStorage = decimal.Parse(ds.Tables[0].Rows[0]["pStorage"].ToString());
}
if (ds.Tables[0].Rows[0]["pStorageIn"].ToString() != "")
{
model.pStorageIn = decimal.Parse(ds.Tables[0].Rows[0]["pStorageIn"].ToString());
}
if (ds.Tables[0].Rows[0]["pStorageOut"].ToString() != "")
{
model.pStorageOut = decimal.Parse(ds.Tables[0].Rows[0]["pStorageOut"].ToString());
}
if (ds.Tables[0].Rows[0]["pUpdateTime"].ToString() != "")
{
model.pUpdateTime = DateTime.Parse(ds.Tables[0].Rows[0]["pUpdateTime"].ToString());
}
return(model);
}
else
{
return(null);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int AddProductsStorage(ProductsStorageInfo model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into tbProductsStorageInfo(");
strSql.Append("StorageID,ProductsID,pStorage,pStorageIn,pStorageOut,pUpdateTime)");
strSql.Append(" values (");
strSql.Append("@StorageID,@ProductsID,@pStorage,@pStorageIn,@pStorageOut,@pUpdateTime)");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@StorageID", SqlDbType.Int, 4),
new SqlParameter("@ProductsID", SqlDbType.Int, 4),
new SqlParameter("@pStorage", SqlDbType.Decimal, 9),
new SqlParameter("@pStorageIn", SqlDbType.Decimal, 9),
new SqlParameter("@pStorageOut", SqlDbType.Decimal, 9),
new SqlParameter("@pUpdateTime", SqlDbType.DateTime)
};
parameters[0].Value = model.StorageID;
parameters[1].Value = model.ProductsID;
parameters[2].Value = model.pStorage;
parameters[3].Value = model.pStorageIn;
parameters[4].Value = model.pStorageOut;
parameters[5].Value = model.pUpdateTime;
object obj = DbHelper.ExecuteScalar(CommandType.Text, strSql.ToString(), parameters);
if (obj == null)
{
return(-1);
}
else
{
return(Convert.ToInt32(obj));
}
}
public static void UpdateProductsStorage(ProductsStorageInfo model)
{
DatabaseProvider.GetInstance().UpdateProductsStorage(model);
}
public static int AddProductsStorage(ProductsStorageInfo model)
{
return(DatabaseProvider.GetInstance().AddProductsStorage(model));
}
/// <summary>
/// 添加指定时间指定仓库/商品库存
/// </summary>
/// <param name="StorageID"></param>
/// <param name="ProductsID"></param>
/// <param name="dT"></param>
/// <returns></returns>
public int AddProductsStorage(int StorageID, int ProductsID, DateTime dT)
{
int ProductsStorageID = 0;
decimal pStorage = 0; //计算实际库存
decimal pStorageIn = 0; //入库未结存
decimal pStorageOut = 0; //出库未结存
StringBuilder strSql = new StringBuilder();
//调拨单处理，正数为增加，负数为减少
strSql.Append("declare @QuantityIn Decimal;" +
"declare @QuantityOut Decimal;" +
"select @QuantityIn = isnull(sum(oQuantity),0) from tbOrderListInfo where oWorkType=1 " +
" and OrderID in(select OrderID from tbOrderInfo where oState=0 and oType =9 and oSteps>=4)" +
" and StorageID=@StorageID and ProductsID=@ProductsID and oAppendTime<=@dT and oQuantity>0;" +
"select @QuantityOut = isnull(sum(oQuantity),0)*-1 from tbOrderListInfo where oWorkType=1 " +
" and OrderID in(select OrderID from tbOrderInfo where oState=0 and oType =9) " +
" and StorageID=@StorageID and ProductsID=@ProductsID and oAppendTime<=@dT and oQuantity<0; ");
strSql.Append("select vStorage.Quantity,vStorageIn.Quantity,vStorageOut.Quantity from ");
//实际库存
strSql.Append("(select isnull(sum(pQuantity),0) as Quantity from tbProductsStorageLogInfo ");
strSql.Append(" where StorageID=@StorageID and ProductsID=@ProductsID and pAppendTime<=@dT and pType<>-1) as vStorage,");
//开单后已审核未核销的单据
//入库,已收货后途中库存增加
strSql.Append("(select (isnull(sum(oQuantity),0)+@QuantityIn) as Quantity from tbOrderListInfo where oWorkType=1 "); //审核后核销前
strSql.Append("and OrderID in(select OrderID from tbOrderInfo where oState=0 and oType in(1,4,8) and oSteps>=4) ");
strSql.Append("and StorageID=@StorageID and ProductsID=@ProductsID and oAppendTime<=@dT ) as vStorageIn,"); //oAppendTime<=@dT
//特殊情况**********换货单(oType=11):审核后库存减少,核销后库存增加**********
//所以未核销前属于 出库性质
//出库
strSql.Append("(select (isnull(sum(oQuantity),0)+@QuantityOut) as Quantity from tbOrderListInfo where oWorkType=1 "); //审核后核销前
strSql.Append("and OrderID in(select OrderID from tbOrderInfo where oState=0 and oType in(2,3,5,6,7,10,11)) ");
strSql.Append("and StorageID=@StorageID and ProductsID=@ProductsID and oAppendTime<=@dT ) as vStorageOut "); // oAppendTime<=@dT
strSql.Append("");
SqlParameter[] parameters =
{
new SqlParameter("@StorageID", SqlDbType.Int, 4),
new SqlParameter("@ProductsID", SqlDbType.Int, 4),
new SqlParameter("@dT", SqlDbType.DateTime)
};
parameters[0].Value = StorageID;
parameters[1].Value = ProductsID;
parameters[2].Value = dT;
DataSet ds = DbHelper.ExecuteDataset(CommandType.Text, strSql.ToString(), parameters);
if (ds != null)
{
pStorage = Convert.ToDecimal(ds.Tables[0].Rows[0][0]);
pStorageIn = Convert.ToDecimal(ds.Tables[0].Rows[0][1]);
pStorageOut = Convert.ToDecimal(ds.Tables[0].Rows[0][2]);
ProductsStorageID = ExistsProductsStorage(StorageID, ProductsID, dT);//是否已经存在记录
if (ProductsStorageID > 0)
{
ProductsStorageInfo psi = GetProductsStorageModel(ProductsStorageID);
if (psi != null)
{
psi.pStorage = pStorage;
psi.pStorageIn = pStorageIn;
psi.pStorageOut = pStorageOut;
psi.pUpdateTime = DateTime.Now;
UpdateProductsStorage(psi);//已存在的更新
}
}
else
{
ProductsStorageInfo psi = new ProductsStorageInfo();
psi.StorageID = StorageID;
psi.ProductsID = ProductsID;
psi.pStorage = pStorage;
psi.pStorageIn = pStorageIn;
psi.pStorageOut = pStorageOut;
psi.pUpdateTime = DateTime.Now;
ProductsStorageID = AddProductsStorage(psi);//不存在的创建
}
}
return(ProductsStorageID);
}
