public IEnumerable <DTO.VipProductDto> GetPageList(DTO.Pager page, DTO.SearchVipProduct condition)
{
dynamic param = new ExpandoObject();
string where = "";
if (!string.IsNullOrEmpty(condition.ProductCodeOrBarCode))
{
where += "and (p.`Code`=@ProductCodeOrBarCode or p.BarCode = @ProductCodeOrBarCode)";
param.ProductCodeOrBarCode = condition.ProductCodeOrBarCode;
}
if (!string.IsNullOrEmpty(condition.Name))
{
where += "and p.`Name` like @ProductName";
param.ProductName = string.Format("%{0}%", condition.Name);
}
string sql = @"select v.Id, p.Id as ProductId, p.`Name` as ProductName,p.`Code` as ProductCode,p.BarCode,p.Specification,p.Unit,p.SalePrice,v.SalePrice as VipSalePrice from VipProduct v left join product p on v.ProductId = p.Id
where 1=1 {0} ORDER BY v.Id desc LIMIT {1},{2}";
//rows = this._query.FindPage<ProductDto>(page.PageIndex, page.PageSize).Where<Product>(where, param);
sql = string.Format(sql, where, (page.PageIndex - 1) * page.PageSize, page.PageSize);
var rows = this._query.FindAll <VipProductDto>(sql, param);
string sqlCount = @"select count(*) from VipProduct v left join product p on v.ProductId = p.Id
where 1=1 {0} ";
sqlCount = string.Format(sqlCount, where);
page.Total = this._query.Context.ExecuteScalar <int>(sqlCount, param);
return(rows);
}
public IEnumerable <DTO.StocktakingPlanDto> GetPageList(DTO.Pager page, DTO.SearchStocktakingPlan condition)
{
dynamic param = new ExpandoObject();
string where = "";
if (!string.IsNullOrEmpty(condition.Code))
{
where += "and t0.Code=@Code ";
param.Code = condition.Code;
}
if (condition.StoreId > 0)
{
where += "and t0.StoreId=@StoreId ";
param.StoreId = condition.StoreId;
}
if (condition.Status > 0)
{
where += "and t0.Status=@Status ";
param.Status = condition.Status;
}
if (condition.StocktakingDate.HasValue)
{
where += "and t0.StocktakingDate>=@beginDate and t0.StocktakingDate<@endDate ";
param.beginDate = condition.StocktakingDate;
param.endDate = condition.StocktakingDate.Value.AddDays(1);
}
string sql = @"select t0.Id,t0.Code,t0.StocktakingDate,t0.method,t0.Status,t0.createdByName,t1.Name as StoreName
from stocktakingplan t0 inner join store t1 on t0.StoreId = t1.Id
where 1=1 {0} ORDER BY t0.Id desc LIMIT {1},{2}";
sql = string.Format(sql, where, (page.PageIndex - 1) * page.PageSize, page.PageSize);
var rows = this._query.FindAll <StocktakingPlanDto>(sql, param);
page.Total = this._query.Count <StocktakingPlan>(where, param);
return(rows);
}
public IEnumerable <DTO.StorePurchaseOrderQueryDto> GetPageList(DTO.Pager page, DTO.SearchStorePurchaseOrder condition)
{
dynamic param = new ExpandoObject();
string where = "";
if (!string.IsNullOrEmpty(condition.Code))
{
where += "and t0.Code=@Code ";
param.Code = condition.Code;
}
if (condition.SupplierId > 0)
{
where += "and t0.SupplierId=@SupplierId ";
param.SupplierId = condition.SupplierId;
}
if (!string.IsNullOrEmpty(condition.StoreId) && condition.StoreId != "0")
{
where += "and t0.StoreId in @StoreId ";
param.StoreId = condition.StoreId.Split(',').ToIntArray();;
}
if (!string.IsNullOrEmpty(condition.Status))
{
where += "and t0.Status in (" + condition.Status + ") ";
// param.Status = condition.Status;
}
string pwhere = "";
if (!string.IsNullOrEmpty(condition.ProductCodeOrBarCode))
{
//where += "and t3.ProductId in (select Id from Product where Code=@ProductCodeOrBarCode or BarCode=@ProductCodeOrBarCode) ";
//param.Code = condition.Code;
pwhere = string.Format("left join product p on p.Id = i.productid where p.Code='{0}' or p.BarCode='{0}' ", condition.ProductCodeOrBarCode);
}
if (condition.OrderType > 0)
{
where += " and t0.OrderType=@OrderType ";
param.OrderType = condition.OrderType;
}
if (condition.StartDate.HasValue)
{
where += "and t0.CreatedOn >=@StartDate ";
param.StartDate = condition.StartDate.Value;
}
if (condition.EndDate.HasValue)
{
where += "and t0.CreatedOn < @EndDate ";
param.EndDate = condition.EndDate.Value.AddDays(1);
}
if (condition.StoragedBegin.HasValue)
{
where += "and t0.StoragedOn >=@StoragedBegin ";
param.StoragedBegin = condition.StoragedBegin.Value;
}
if (condition.StoragedEnd.HasValue)
{
where += "and t0.StoragedOn < @StoragedEnd ";
param.StoragedEnd = condition.StoragedEnd.Value.AddDays(1);
}
if (!string.IsNullOrEmpty(condition.AuditName))
{
where += "and h.CreatedByName like @AuditName ";
param.AuditName = string.Format("%{0}%", condition.AuditName);
}
string formType = condition.OrderType == 1?"StorePurchaseOrder": "StorePurchaseRefundOrder";
string sql = @"select t0.Id,t0.Code,t0.SupplierId,t0.CreatedOn,t0.CreatedByName,t0.Status,t0.SupplierBill,t1.Code as SupplierCode,t1.Name as SupplierName,t2.Name as StoreName,t3.Quantity,t3.ActualQuantity,t3.Amount,t0.StoragedOn,h.CreatedByName as AuditName
from (select i.StorePurchaseOrderId,SUM(i.Quantity) as Quantity,SUM(i.ActualQuantity) as ActualQuantity,SUM(i.Price* i.ActualQuantity ) as Amount
from storepurchaseorderitem i {3} GROUP BY i.StorePurchaseOrderId) t3 left join
storepurchaseorder t0 on t0.Id = t3.StorePurchaseOrderId left join supplier t1 on t0.SupplierId = t1.Id left join store t2 on t0.StoreId = t2.Id
left join processhistory h on t0.Id = h.formId and FormType='{4}' and h.`Status` =5 where 1=1 {0} ORDER BY t0.Id desc LIMIT {1},{2}";
sql = string.Format(sql, where, (page.PageIndex - 1) * page.PageSize, page.PageSize, pwhere, formType);
var rows = this._query.FindAll <StorePurchaseOrderQueryDto>(sql, param);
// 统计列
string sqlSum = @"select count(*) as TotalCount, sum(t3.Quantity) as Quantity ,sum(t3.ActualQuantity) as ActualQuantity ,sum(t3.Amount) as Amount
from (select i.StorePurchaseOrderId,SUM(i.Quantity) as Quantity,SUM(i.ActualQuantity) as ActualQuantity,SUM(i.Price* i.ActualQuantity ) as Amount
from storepurchaseorderitem i {1} GROUP BY i.StorePurchaseOrderId) t3 left join
storepurchaseorder t0 on t0.Id = t3.StorePurchaseOrderId left join supplier t1 on t0.SupplierId = t1.Id left join store t2 on t0.StoreId = t2.Id
left join processhistory h on t0.Id = h.formId and FormType='{2}' and h.`Status` =5 where 1=1 {0} ";
sqlSum = string.Format(sqlSum, where, pwhere, formType);
var sumStoreInventory = this._query.Find <SumStorePurchaseOrder>(sqlSum, param) as SumStorePurchaseOrder;
page.Total = sumStoreInventory.TotalCount;
page.SumColumns.Add(new SumColumn("Quantity", sumStoreInventory.Quantity.ToString()));
page.SumColumns.Add(new SumColumn("ActualQuantity", sumStoreInventory.ActualQuantity.ToString()));
page.SumColumns.Add(new SumColumn("Amount", sumStoreInventory.Amount.ToString("F4")));
return(rows);
}
public IEnumerable <DTO.StoreInventoryQueryDto> GetPageList(DTO.Pager page, DTO.SearchStoreInventory condition)
{
dynamic param = new ExpandoObject();
string where = "";
if (!string.IsNullOrEmpty(condition.StoreId) && condition.StoreId != "0")
{
where += "and t0.StoreId in @StoreId ";
param.StoreId = condition.StoreId.Split(',').ToIntArray();;
}
if (!string.IsNullOrEmpty(condition.ProductCodeOrBarCode))
{
where += "and (t1.Code=@ProductCodeOrBarCode or t1.BarCode=@ProductCodeOrBarCode) ";
param.ProductCodeOrBarCode = condition.ProductCodeOrBarCode;
}
if (!string.IsNullOrEmpty(condition.ProductName))
{
where += "and t1.Name like @ProductName ";
param.ProductName = string.Format("%{0}%", condition.ProductName);
}
if (!string.IsNullOrEmpty(condition.CategoryId))
{
where += "and t3.Id like @CategoryId ";
param.CategoryId = string.Format("{0}%", condition.CategoryId);
}
if (!string.IsNullOrEmpty(condition.Operate))
{
where += string.Format("and t0.Quantity {0} @Quantity ", condition.Operate);
param.Quantity = condition.Quantity;
}
string sql = @"select t0.Quantity,t0.LastCostPrice,t1.`Code` as ProductCode ,t1.`Name` as ProductName,t1.BarCode,t1.Specification,
case when t0.StoreSalePrice>0 then t0.StoreSalePrice else t1.SalePrice END as SalePrice,t2.`name` as StoreName,t3.FullName as CategoryName
from storeinventory t0 left join product t1 on t0.productId = t1.Id
left join store t2 on t2.Id = t0.StoreId
left join category t3 on t1.CategoryId = t3.Id
where 1=1 {0} ORDER BY t2.Id,t1.Id ";
if (!page.toExcel)
{
sql += string.Format(" LIMIT {0},{1}", (page.PageIndex - 1) * page.PageSize, page.PageSize);
}
sql = string.Format(sql, where);
var rows = this._query.FindAll <StoreInventoryQueryDto>(sql, param);
// 查询统计列数据
string sqlSum = @"select count(*) as TotalCount, sum(t0.Quantity) as Quantity,sum(t0.LastCostPrice*t0.Quantity) as Amount,sum(t1.SalePrice*t0.Quantity) as SaleAmount
from storeinventory t0 left join product t1 on t0.productId = t1.Id
left join store t2 on t2.Id = t0.StoreId
left join category t3 on t1.CategoryId = t3.Id
where 1=1 {0}";
sqlSum = string.Format(sqlSum, where);
var sumStoreInventory = this._query.Find <SumStoreInventory>(sqlSum, param) as SumStoreInventory;
page.Total = sumStoreInventory.TotalCount;
page.SumColumns.Add(new SumColumn("Quantity", sumStoreInventory.Quantity.ToString()));
page.SumColumns.Add(new SumColumn("Amount", sumStoreInventory.Amount.ToString("F4")));
page.SumColumns.Add(new SumColumn("SaleAmount", sumStoreInventory.SaleAmount.ToString("F2")));
return(rows);
}
public IEnumerable <DTO.TransferOrderDto> GetPageList(DTO.Pager page, DTO.SearchTransferOrder condition)
{
dynamic param = new ExpandoObject();
string where = "";
if (!string.IsNullOrEmpty(condition.Code))
{
where += "and o.Code=@Code ";
param.Code = condition.Code;
}
if (condition.Status != 0)
{
where += "and o.Status=@Status ";
param.Status = condition.Status;
}
if (!string.IsNullOrEmpty(condition.StoreId) && condition.StoreId != "0")
{
if (condition.From.HasValue && condition.To.HasValue && condition.From.Value && condition.To.Value || !condition.From.HasValue && !condition.To.HasValue)
{
where += "and ( o.FromStoreId in @StoreId or o.ToStoreId in @StoreId ) ";
param.StoreId = condition.StoreId.Split(',').ToIntArray();
}
else
{
if (condition.From.HasValue && condition.From.Value)
{
where += "and o.FromStoreId in @StoreId ";
param.StoreId = condition.StoreId.Split(',').ToIntArray();
}
if (condition.To.HasValue && condition.To.Value)
{
where += "and o.ToStoreId in @StoreId ";
param.StoreId = condition.StoreId.Split(',').ToIntArray();
}
}
}
if (condition.StartDate.HasValue)
{
where += "and o.CreatedOn >=@StartDate ";
param.StartDate = condition.StartDate.Value;
}
if (condition.EndDate.HasValue)
{
where += "and o.CreatedOn < @EndDate ";
param.EndDate = condition.EndDate.Value.AddDays(1);
}
if (!string.IsNullOrEmpty(condition.ProductCodeOrBarCode))
{
where += @"and o.Id in (select d.transferOrderId from transferorderitem d left join product p on p.id = d.productid where p.code=@ProductCodeOrBarCode or p.barcode =@ProductCodeOrBarCode ) ";
param.ProductCodeOrBarCode = condition.ProductCodeOrBarCode;
}
string sql = @"select o.Id,o.Code,o.FromStoreName,o.ToStoreName,o.Status,o.CreatedByName,o.UpdatedByName,o.CreatedOn, t.TotalQuantity,t.TotalAmount
from transferorder o left join
(select i.TransferOrderId,sum(i.Quantity) as TotalQuantity ,sum(i.price* i.Quantity) as TotalAmount
from transferorderitem i GROUP BY i.transferorderId ) t on o.Id = t.TransferOrderId
where 1=1 {0} ORDER BY o.Id desc LIMIT {1},{2}";
//rows = this._query.FindPage<ProductDto>(page.PageIndex, page.PageSize).Where<Product>(where, param);
sql = string.Format(sql, where, (page.PageIndex - 1) * page.PageSize, page.PageSize);
var rows = this._query.FindAll <TransferOrderDto>(sql, param);
string sqlCount = @"select count(*) from transferorder o left join
(select i.TransferOrderId,sum(i.Quantity) as TotalQuantity ,sum(i.price* i.Quantity) as TotalAmount
from transferorderitem i GROUP BY i.transferorderId ) t on o.Id = t.TransferOrderId
where 1=1 {0} ORDER BY o.Id desc ";
sqlCount = string.Format(sqlCount, where);
page.Total = this._query.Context.ExecuteScalar <int>(sqlCount, param);
return(rows);
}