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