public JsonResult LoadData(Pager page, SearchOutInOrder condition) { if (string.IsNullOrEmpty(condition.StoreId) || condition.StoreId == "0") { condition.StoreId = _context.CurrentAccount.CanViewStores; } var rows = _outInOrderQuery.GetPageList(page, condition); return(Json(new { success = true, data = rows, total = page.Total, sum = page.SumColumns })); }
public IEnumerable <OutInOrderSummaryDto> GetSummaryList(Pager page, SearchOutInOrder condition) { dynamic param = new ExpandoObject(); string where = ""; if (!string.IsNullOrEmpty(condition.StoreId) && condition.StoreId != "0") { where += "and h.StoreId in @StoreId "; param.StoreId = condition.StoreId.Split(',').ToIntArray();; } if (condition.OutInOrderTypeId != 0) { where += " and t.Id=@OutInOrderTypeId "; param.OutInOrderTypeId = condition.OutInOrderTypeId; } if (condition.OutInInventory != 0) { where += " and t.OutInInventory=@OutInInventory "; param.OutInInventory = condition.OutInInventory; } if (condition.StartDate.HasValue) { where += "and h.CreatedOn >=@StartDate "; param.StartDate = condition.StartDate.Value; } if (condition.EndDate.HasValue) { where += "and h.CreatedOn < @EndDate "; param.EndDate = condition.EndDate.Value.AddDays(1); } string sql = @"select s.name as StoreName,b.typeName,a.Quantity,a.Amount from ( select h.StoreId,t.Id outinordertypeId,sum(h.ChangeQuantity) as Quantity,sum( h.ChangeQuantity*h.Price) as amount from storeinventoryhistory h inner join outinorder o on o.code = h.BillCode inner join outinordertype t on t.Id = o.OutInOrderTypeId where h.BillType in (61,62) {0} GROUP BY h.StoreId,t.Id ) a left join store s on a.storeid = s.id left join outinordertype b on b.id = a.outinordertypeId 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 <OutInOrderSummaryDto>(sql, param); string sqlSum = @" select count(*) as TotalCount,sum(Quantity) as Quantity,sum( amount) as amount from ( select h.StoreId,t.Id outinordertypeId,count(*) as TotalCount,sum(h.ChangeQuantity) as Quantity,sum( h.ChangeQuantity*h.Price) as amount from storeinventoryhistory h inner join outinorder o on o.code = h.BillCode inner join outinordertype t on t.Id = o.OutInOrderTypeId where h.BillType in (61,62) {0} GROUP BY h.StoreId,t.Id ) a "; sqlSum = string.Format(sqlSum, where); var sumStoreInventory = this._query.Find <SumOutInOrderSummary>(sqlSum, param) as SumOutInOrderSummary; page.Total = sumStoreInventory.TotalCount; page.SumColumns.Add(new SumColumn("Quantity", sumStoreInventory.Quantity.ToString())); page.SumColumns.Add(new SumColumn("Amount", sumStoreInventory.Amount.ToString("F4"))); return(rows); }
public IEnumerable <OutInOrderListDto> GetFinishList(Pager page, SearchOutInOrder 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) && condition.Status != "0") { where += "and t0.Status in (" + condition.Status + ") "; // param.Status = condition.Status; } if (!string.IsNullOrEmpty(condition.ProductCodeOrBarCode)) { where = string.Format("and (p.Code=@ProductCodeOrBarCode or p.BarCode=@ProductCodeOrBarCode) ", condition.ProductCodeOrBarCode); param.ProductCodeOrBarCode = condition.ProductCodeOrBarCode; } if (!string.IsNullOrEmpty(condition.ProductName)) { where = string.Format("and p.Name like @ProductName ", condition.ProductName); param.ProductName = string.Format("%{0}%", condition.ProductName); } if (condition.OutInOrderTypeId != 0) { where += " and t0.OutInOrderTypeId=@OutInOrderTypeId "; param.OutInOrderTypeId = condition.OutInOrderTypeId; } if (condition.OutInInventory != 0) { where += " and t.OutInInventory=@OutInInventory "; param.OutInInventory = condition.OutInInventory; } if (condition.StartDate.HasValue) { where += "and t0.UpdatedOn >=@StartDate "; param.StartDate = condition.StartDate.Value; } if (condition.EndDate.HasValue) { where += "and t0.UpdatedOn < @EndDate "; param.EndDate = condition.EndDate.Value.AddDays(1); } if (!string.IsNullOrEmpty(condition.AuditName)) { where += "and h.CreatedByName like @AuditName "; param.AuditName = string.Format("%{0}%", condition.AuditName); } string formType = condition.OutInInventory == 1 ? "OtherInOrder" : "OtherOutOrder"; string sql = @"select t0.Id,t0.Code,t0.SupplierId,t0.CreatedOn,t0.CreatedByName,t0.Status,t0.remark,t1.Code as SupplierCode,t1.Name as SupplierName,t2.Name as StoreName, t3.Quantity,t3.CostPrice,t3.LastCostPrice,t0.UpdatedOn,h.CreatedByName as AuditName,t.TypeName,p.`Name` as ProductName,p.`Code` as ProductCode,p.BarCode,p.Specification,p.Unit from OutInOrder t0 inner join OutInOrderitem t3 on t0.Id = t3.OutInOrderId left join product p on p.id = t3.ProductId left join OutInOrderType t on t.Id = t0.OutInOrderTypeId 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='{3}' and h.`Status` =4 where 1=1 {0} ORDER BY t0.Id desc LIMIT {1},{2}"; sql = string.Format(sql, where, (page.PageIndex - 1) * page.PageSize, page.PageSize, formType); var rows = this._query.FindAll <OutInOrderListDto>(sql, param); // 统计列 string sqlSum = @"select count(*) as TotalCount, sum(t3.Quantity) as Quantity ,sum(t3.Quantity*t3.CostPrice) as Amount from OutInOrder t0 inner join OutInOrderitem t3 on t0.Id = t3.OutInOrderId left join product p on p.id = t3.ProductId left join OutInOrderType t on t.Id = t0.OutInOrderTypeId 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='{1}' and h.`Status` =4 where 1=1 {0} "; sqlSum = string.Format(sqlSum, where, formType); var sumStoreInventory = this._query.Find <SumOutInOrderSummary>(sqlSum, param) as SumOutInOrderSummary; page.Total = sumStoreInventory.TotalCount; page.SumColumns.Add(new SumColumn("Quantity", sumStoreInventory.Quantity.ToString())); page.SumColumns.Add(new SumColumn("Amount", sumStoreInventory.Amount.ToString("F4"))); return(rows); }
public JsonResult LoadSummaryData(Pager page, SearchOutInOrder conditon) { var rows = _outInOrderQuery.GetSummaryList(page, conditon); return(Json(new { success = true, data = rows, total = page.Total, sum = page.SumColumns })); }