public JsonResult LoadFinishData(Pager page, SearchTransferOrder condition) { if (string.IsNullOrEmpty(condition.StoreId) || condition.StoreId == "0") { condition.StoreId = _context.CurrentAccount.CanViewStores; } var rows = _transaferQuery.GetFinishList(page, condition); return(Json(new { success = true, data = rows, total = page.Total, sum = page.SumColumns })); }
public IEnumerable <TransferOrderSummaryDto> GetSummaryList(Pager page, SearchTransferOrder condition) { dynamic param = new ExpandoObject(); string where = ""; if (!string.IsNullOrEmpty(condition.StoreId) && condition.StoreId != "0") { where += "and o.StoreId 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 ( p.code=@ProductCodeOrBarCode or p.barcode =@ProductCodeOrBarCode ) "; // param.ProductCodeOrBarCode = condition.ProductCodeOrBarCode; //} //if (!string.IsNullOrEmpty(condition.ProductName)) //{ // where += "and p.Name like @ProductName "; // param.ProductName = string.Format("%{0}%", condition.ProductName); //} string sql = @"select s.`Name` as StoreName,t.InQuantity,t.InAmount,t.OutQuantity,t.OutAmount,t.InQuantity+t.OutQuantity as DifferenceQuantity,t.InAmount+t.OutAmount as DifferenceAmount from ( select StoreId, sum(case when ChangeQuantity<0 then ChangeQuantity end) OutQuantity,sum(case when ChangeQuantity<0 then price* ChangeQuantity end) OutAmount, sum(case when ChangeQuantity>=0 then ChangeQuantity end) InQuantity,sum(case when ChangeQuantity>=0 then price* ChangeQuantity end) InAmount from storeinventoryhistory o where BillType=60 {0} GROUP BY storeid ORDER BY o.Id desc ) t left join store s on s.id = t.storeid 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 <TransferOrderSummaryDto>(sql, param); string sqlSum = @" select count(*) as TotalCount,sum(t.InQuantity) as InQuantity,sum(InAmount) as InAmount, sum(t.OutQuantity) as OutQuantity,sum(OutAmount) as OutAmount,sum(t.InQuantity+t.OutQuantity) as DifferenceQuantity,sum(t.InAmount+t.OutAmount) as DifferenceAmount from ( select sum(case when ChangeQuantity<0 then ChangeQuantity end) OutQuantity,sum(case when ChangeQuantity<0 then price* ChangeQuantity end) OutAmount, sum(case when ChangeQuantity>=0 then ChangeQuantity end) InQuantity,sum(case when ChangeQuantity>=0 then price* ChangeQuantity end) InAmount from storeinventoryhistory o where BillType=60 {0} GROUP BY storeid ORDER BY o.Id desc ) t "; sqlSum = string.Format(sqlSum, where); var sumStoreInventory = this._query.Find <SumTransferOrderSummary>(sqlSum, param) as SumTransferOrderSummary; page.Total = sumStoreInventory.TotalCount; page.SumColumns.Add(new SumColumn("InQuantity", sumStoreInventory.InQuantity.ToString())); page.SumColumns.Add(new SumColumn("InAmount", sumStoreInventory.InAmount.ToString("F4"))); page.SumColumns.Add(new SumColumn("OutQuantity", sumStoreInventory.OutQuantity.ToString())); page.SumColumns.Add(new SumColumn("OutAmount", sumStoreInventory.OutAmount.ToString("F4"))); page.SumColumns.Add(new SumColumn("DifferenceQuantity", sumStoreInventory.DifferenceQuantity.ToString())); page.SumColumns.Add(new SumColumn("DifferenceAmount", sumStoreInventory.DifferenceAmount.ToString("F4"))); return(rows); }
public IEnumerable <TransferOrderListDto> GetFinishList(Pager page, 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.UpdatedOn >=@StartDate "; param.StartDate = condition.StartDate.Value; } if (condition.EndDate.HasValue) { where += "and o.UpdatedOn < @EndDate "; param.EndDate = condition.EndDate.Value.AddDays(1); } if (!string.IsNullOrEmpty(condition.ProductCodeOrBarCode)) { where += @"and ( p.code=@ProductCodeOrBarCode or p.barcode =@ProductCodeOrBarCode ) "; param.ProductCodeOrBarCode = condition.ProductCodeOrBarCode; } if (!string.IsNullOrEmpty(condition.ProductName)) { where += "and p.Name like @ProductName "; param.ProductName = string.Format("%{0}%", condition.ProductName); } string sql = @"select o.Id,o.Code,o.FromStoreName,o.ToStoreName,o.Status,o.CreatedByName,o.CreatedOn, o.UpdatedOn,p.code as ProductCode,p.BarCode,p.`Name` as ProductName,p.Specification, i.Price,i.Quantity,i.Price* i.Quantity as Amount from transferorder o inner join transferorderitem i on o.Id = i.TransferOrderId left join product p on p.Id = i.ProductId 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 <TransferOrderListDto>(sql, param); string sqlSum = @" select count(*) as TotalCount,sum(i.Quantity) as Quantity,sum(i.Price* i.Quantity) as Amount from transferorder o inner join transferorderitem i on o.Id = i.TransferOrderId left join product p on p.Id = i.ProductId where 1=1 {0} "; sqlSum = string.Format(sqlSum, where); var sumStoreInventory = this._query.Find <SumTransferOrder>(sqlSum, param) as SumTransferOrder; 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, SearchTransferOrder conditon) { var rows = _transaferQuery.GetSummaryList(page, conditon); return(Json(new { success = true, data = rows, total = page.Total, sum = page.SumColumns })); }