/// <summary>
/// 更新同一规格下的采购数量
/// </summary>
/// <param name="purchasingGoodsId"></param>
/// <param name="planQuantity"></param>
/// <returns></returns>
public static bool UpdatePurchasingGoodsPlanQuantity(Guid purchasingGoodsId, int planQuantity)
{
const string SQL = QueryString.UPDATE_PURCHASINGDETAIL_PLAN_QUANTITY;
var param = PrepareParameter.Create();
param.Append("@PurchasingGoodsID", purchasingGoodsId);
param.Append("@PlanQuantity", planQuantity);
using (var db = DatabaseFactory.Create())
{
return(db.Execute(false, SQL, param.Result()));
}
}
public static List <PlanPurchasingGoods> GetAllSumPurchasingQuantity(Guid warehouseId, Guid hostingFilialeId)
{
const string SQL = QueryString.SUM_PURCHASING_ALL_QUANTITY;
var param = PrepareParameter.Create();
param.Append("@WarehouseID", warehouseId);
param.Append("@HostingFilialeId", hostingFilialeId);
using (var db = DatabaseFactory.Create())
{
var result = db.Select <PlanPurchasingGoods>(true, SQL, param.Result());
return(result != null?result.ToList() : new List <PlanPurchasingGoods>());
}
}
/// <summary>
/// 统计采购的商品数量
/// </summary>
/// <param name="goodsId"></param>
/// <param name="warehouseId"></param>
/// <param name="hostingFilialeId"></param>
/// <returns></returns>
public static double GetSumPurchasingQuantity(Guid goodsId, Guid warehouseId, Guid hostingFilialeId)
{
const string SQL = QueryString.SUM_PURCHASING_QUANTITY;
var param = PrepareParameter.Create();
param.Append("@GoodsId", goodsId);
param.Append("@WarehouseID", warehouseId);
param.Append("@HostingFilialeId", hostingFilialeId);
using (var db = DatabaseFactory.Create())
{
return(db.GetValue <double>(true, SQL, param.Result()));
}
}
/// <summary>
/// 记录采购的最后日期
/// </summary>
/// <param name="goodsId"></param>
/// <param name="lastDate"></param>
/// <returns></returns>
public static bool UpdateLastPurchasingDate(Guid warehouseId, Guid hostingFilialeId, Guid goodsId, DateTime lastDate)
{
const string SQL = QueryString.UPDATE_LASTPURCHASINGDATE;
var param = PrepareParameter.Create();
param.Append("@LastPurchasingDate", lastDate);
param.Append("@GoodsId", goodsId);
param.Append("@WarehouseId", warehouseId);
param.Append("@HostingFilialeId", hostingFilialeId);
using (var db = DatabaseFactory.Create())
{
return(db.Execute(false, SQL, param.Result()));
}
}
/// <summary>
/// 获取在同一个采购组、供应商、仓库的未提交的自动报备详细采购商品信息ID
/// </summary>
/// <param name="purchasingId"></param>
/// <param name="goodsId"></param>
/// <param name="specification"></param>
/// <param name="hostingFilialeId"></param>
/// <returns></returns>
public static Guid GetSamePurchasingGoodsId(Guid purchasingId, Guid goodsId, string specification, Guid hostingFilialeId)
{
const string SQL = QueryString.SELECT_SAME_PURCHASINGDETAIL_SPECIFICATION;
var param = PrepareParameter.Create();
param.Append("@PurchasingID", purchasingId);
param.Append("@GoodsId", goodsId);
param.Append("@Specification", specification);
param.Append("@FilialeId", hostingFilialeId);
using (var db = DatabaseFactory.Create())
{
return(db.GetValue <Guid>(true, SQL, param.Result()));
}
}
/// <summary>
/// 获取在同一个责任人、供应商、仓库的未提交的自动报备采购信息ID
/// </summary>
/// <param name="companyId"></param>
/// <param name="personResponsible"></param>
/// <param name="warehouseId"></param>
/// <param name="hostingFilialeId"></param>
/// <returns></returns>
public static Guid GetSamePurchasingId(Guid companyId, Guid personResponsible, Guid warehouseId, Guid hostingFilialeId)
{
const string SQL = QueryString.SELECT_SAME_PURCHASINGINFO;
var param = PrepareParameter.Create();
param.Append("@CompanyID", companyId);
param.Append("@PersonResponsible", personResponsible);
param.Append("@WarehouseID", warehouseId);
param.Append("@HostingFilialeId", hostingFilialeId);
using (var db = DatabaseFactory.Create())
{
return(db.GetValue <Guid>(true, SQL, param.Result()));
}
}
/// <summary>
/// 获取子商品的60、30、11天销量
/// Add by liucaijun at 2012-10-12
/// </summary>
/// <param name="realGoodsId"></param>
/// <param name="warehouseId"></param>
/// <param name="endTime"></param>
/// <param name="hostingFilialeId"></param>
/// <returns></returns>
public static ChildGoodsSalePurchasing GetChildGoodsSale(Guid realGoodsId, Guid warehouseId, Guid hostingFilialeId, DateTime endTime)
{
//获取子商品的销售信息
const string SQL = @"
SELECT
RealGoodsId AS GoodsId,
DeliverWarehouseId AS WarehouseId,
ISNULL([60],0) AS SixtyDaySales,
ISNULL([30],0) AS ThirtyDaySales,
(ISNULL([11],0)/11) AS ElevenDaySales
FROM
(
SELECT RealGoodsId,DeliverWarehouseId,goodsSales,60 AS t FROM lmshop_GoodsDaySalesStatistics AS t1
WHERE DayTime between dateadd(day,-60,convert(VARCHAR(10),@EndTime,120)) and dateadd(day,-30,convert(VARCHAR(10),@EndTime,120))
and DeliverWarehouseId=@WareHouseId and RealGoodsId=@RealGoodsId and HostingFilialeId=@HostingFilialeId
UNION all
SELECT RealGoodsId,DeliverWarehouseId,goodsSales, 30 as t from lmshop_GoodsDaySalesStatistics AS t2
WHERE DayTime between dateadd(day,-30,convert(varchar(10),@EndTime,120)) and @EndTime
and DeliverWarehouseId=@WareHouseId and RealGoodsId=@RealGoodsId and HostingFilialeId=@HostingFilialeId
UNION all
SELECT RealGoodsId,DeliverWarehouseId,goodsSales ,11 AS t FROM lmshop_GoodsDaySalesStatistics AS t3
WHERE DayTime between dateadd(day,-11,convert(VARCHAR(10),@EndTime,120)) and @EndTime
and DeliverWarehouseId=@WareHouseId and RealGoodsId=@RealGoodsId and HostingFilialeId=@HostingFilialeId
)kkk
PIVOT
(
sum (goodssales)
FOR t IN
( [11], [30], [60] )
) AS pvt
";
var param = PrepareParameter.Create();
param.Append("@RealGoodsId", realGoodsId);
param.Append("@WareHouseId", warehouseId);
param.Append("@EndTime", endTime);
param.Append("@HostingFilialeId", hostingFilialeId);
using (var db = DatabaseFactory.Create())
{
return(db.Single <ChildGoodsSalePurchasing>(true, SQL, param.Result()) ?? new ChildGoodsSalePurchasing());
}
}