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); }