/// <summary>
/// 插入采购记录
/// </summary>
/// <param name="purchasingId"></param>
/// <param name="purchasingNo"></param>
/// <param name="companyId"></param>
/// <param name="companyName"></param>
/// <param name="filialeId"></param>
/// <param name="warehouseId"></param>
/// <param name="purchasingFilialeId"></param>
/// <param name="purchasingState"></param>
/// <param name="purchasingType"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="description"></param>
/// <param name="pmId"></param>
/// <param name="purchasingToDate"></param>
/// <param name="nextPurchasingDate"></param>
/// <param name="isException"> </param>
/// <param name="personResponsible"> </param>
/// <returns></returns>
public static bool InsertPurchasing(Guid purchasingId, string purchasingNo, Guid companyId, string companyName, Guid filialeId, Guid warehouseId,
Guid purchasingFilialeId, int purchasingState, int purchasingType, DateTime startDate, DateTime endDate, string description, Guid pmId,
DateTime purchasingToDate, DateTime nextPurchasingDate, bool isException, Guid personResponsible)
{
const string SQL = QueryString.INSERT_PURCHASING;
var parms = PrepareParameter.GetInsertPurchasingParms(purchasingId, purchasingNo, companyId, companyName, filialeId, warehouseId, purchasingFilialeId, purchasingState, purchasingType, startDate, endDate, description, pmId, purchasingToDate, nextPurchasingDate, isException, personResponsible);
using (var db = DatabaseFactory.Create())
{
return(db.Execute(false, SQL, parms));
}
}
/// <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>
/// 插入采购商品的详细记录
/// </summary>
/// <param name="purchasingGoodsId"> </param>
/// <param name="purchasingId"></param>
/// <param name="goodsId"></param>
/// <param name="goodsName"></param>
/// <param name="unitName"></param>
/// <param name="goodsCode"></param>
/// <param name="specification"></param>
/// <param name="companyId"></param>
/// <param name="price"></param>
/// <param name="planQuantity"></param>
/// <param name="realityQuantity"></param>
/// <param name="state"></param>
/// <param name="description"></param>
/// <param name="dayAvgStocking"></param>
/// <param name="planStocking"></param>
/// <param name="sixtyDays"></param>
/// <param name="isException"> </param>
/// <param name="sixtyDaySales"> </param>
/// <param name="thirtyDaySales"> </param>
/// <param name="elevenDaySales"> </param>
/// <param name="purchasingGoodsType"> </param>
/// <returns></returns>
public static bool InsertPurchasingDetail(Guid purchasingGoodsId, Guid purchasingId, Guid goodsId, string goodsName, string unitName, string goodsCode, string specification, Guid companyId, decimal price, double planQuantity, double realityQuantity, int state, string description, double dayAvgStocking, double planStocking, int sixtyDays, bool isException, int sixtyDaySales, int thirtyDaySales, float elevenDaySales, int purchasingGoodsType)
{
const string SQL = QueryString.INSERT_PURCHASING_DETAILS;
var parms = PrepareParameter.GetInsertPurchasingDetailParms(purchasingGoodsId, purchasingId, goodsId, goodsName, unitName,
goodsCode, specification, companyId, price,
planQuantity, realityQuantity, state,
description, dayAvgStocking, planStocking,
sixtyDays, isException, sixtyDaySales, thirtyDaySales, elevenDaySales, purchasingGoodsType);
using (var db = DatabaseFactory.Create())
{
return(db.Execute(false, SQL, parms));
}
}
/// <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());
}
}