public JsonResult LoadDataHistory(Pager page, SearchStoreInventoryHistory condition) { if (string.IsNullOrEmpty(condition.StoreId) || condition.StoreId == "0") { condition.StoreId = _context.CurrentAccount.CanViewStores; } var rows = _storeInventoryQuery.GetPageList(page, condition); return(Json(new { success = true, data = rows, total = page.Total, sum = page.SumColumns })); }
public IEnumerable <StoreInventoryHistoryQueryDto> GetPageList(Pager page, SearchStoreInventoryHistory 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.BillCode)) { where += "and t0.BillCode=@BillCode "; param.BillCode = condition.BillCode; } if (!string.IsNullOrEmpty(condition.BatchNo)) { where += "and t0.BatchNo=@BatchNo "; param.BatchNo = condition.BatchNo; } if (!string.IsNullOrEmpty(condition.BillType)) { where += "and t0.BillType in @BillType "; param.BillType = condition.BillType.Split(',').ToIntArray(); } 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 (!string.IsNullOrEmpty(condition.ProductCodeOrBarCode)) { where += "and (t1.Code=@ProductCodeOrBarCode or t1.BarCode=@ProductCodeOrBarCode) "; param.ProductCodeOrBarCode = condition.ProductCodeOrBarCode; } string sql = @"select t0.CreatedOn,t2.`name` as StoreName,t1.`Code` as ProductCode ,t1.`Name` as ProductName,t1.BarCode,t1.Specification,t0.BillType,t0.BillCode,t0.BatchNo, t0.Quantity,t0.Price, IFNULL(case when t0.ChangeQuantity<0 then t0.ChangeQuantity end ,0) as OutQuantity, IFNULL(case when t0.ChangeQuantity<0 then t0.ChangeQuantity*t0.Price end ,0) as OutAmount, IFNULL(case when t0.ChangeQuantity>=0 then t0.ChangeQuantity end,0) as InQuantity, IFNULL(case when t0.ChangeQuantity>=0 then t0.ChangeQuantity*t0.Price end ,0) as InAmount from storeinventoryhistory t0 left join product t1 on t0.productId = t1.Id inner join store t2 on t2.Id = t0.StoreId 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 <StoreInventoryHistoryQueryDto>(sql, param); string sqlCount = @"select count(*) as rowCount , sum(IFNULL(case when t0.ChangeQuantity<0 then t0.ChangeQuantity end ,0)) as OutQuantity, sum(IFNULL(case when t0.ChangeQuantity<0 then t0.ChangeQuantity*t0.Price end ,0)) as OutAmount, sum(IFNULL(case when t0.ChangeQuantity>=0 then t0.ChangeQuantity end,0)) as InQuantity, sum(IFNULL(case when t0.ChangeQuantity>=0 then t0.ChangeQuantity*t0.Price end ,0)) as InAmount from storeinventoryhistory t0 left join product t1 on t0.productId = t1.Id inner join store t2 on t2.Id = t0.StoreId where 1=1 {0} "; sqlCount = string.Format(sqlCount, where); var sumModel = this._query.Find <SumStoreInventoryHistory>(sqlCount, param) as SumStoreInventoryHistory; page.Total = sumModel.RowCount; page.SumColumns.Add(new SumColumn("InQuantity", sumModel.InQuantity.ToString())); page.SumColumns.Add(new SumColumn("OutAmount", sumModel.OutAmount.ToString("F4"))); page.SumColumns.Add(new SumColumn("OutQuantity", sumModel.OutQuantity.ToString())); page.SumColumns.Add(new SumColumn("InAmount", sumModel.InAmount.ToString("F4"))); //page.SumColumns.Add(new SumColumn("CurrentQuantity", sumModel.CurrentQuantity.ToString())); return(rows); }