private void btnDelete_Click(object sender, RoutedEventArgs e)
{
actionIndex = 3;
btnSalvar.IsEnabled = true;
aux = new SaleOrder();
SearchSaleOrder window = new SearchSaleOrder(this);
window.Show();
}
private void btnEditar_Click(object sender, RoutedEventArgs e)
{
actionIndex = 2;
EnableFields();
aux = new SaleOrder();
SearchSaleOrder window = new SearchSaleOrder(this);
window.Show();
}
public JsonResult QuerySaleSummary(Pager page, SearchSaleOrder condition)
{
if (string.IsNullOrEmpty(condition.StoreId) || condition.StoreId == "0")
{
condition.StoreId = _context.CurrentAccount.CanViewStores;
}
var rows = _saleOrderQuery.QuerySaleSummary(page, condition);
return(Json(new { success = true, data = rows, total = page.Total, sum = page.SumColumns }));
}
public IEnumerable <SaleOrderListDto> QuerySaleOrderItems(Pager page, SearchSaleOrder condition)
{
dynamic param = new ExpandoObject();
string where = "";
if (!string.IsNullOrEmpty(condition.NickName))
{
where += " and a.NickName like @NickName ";
param.NickName = string.Format("%{0}%", condition.NickName);
}
if (!string.IsNullOrEmpty(condition.Code))
{
where += "and o.Code=@Code ";
param.Code = condition.Code;
}
if (condition.PosId.HasValue)
{
where += " and o.PosId=@PosId ";
param.PosId = condition.PosId.Value;
}
if (!string.IsNullOrEmpty(condition.StoreId) && condition.StoreId != "0")
{
where += "and s.Id in @StoreId ";
param.StoreId = condition.StoreId.Split(',').ToIntArray();;
}
if (condition.WrokFrom.HasValue)
{
where += " and w.StartDate >= @StartDate ";
param.StartDate = condition.WrokFrom.Value;
}
if (condition.WrokTo.HasValue)
{
where += " and w.StartDate < @EndDate ";
param.EndDate = condition.WrokTo.Value.AddDays(1);
}
if (condition.From.HasValue)
{
where += " and o.UpdatedOn>=@From ";
param.From = condition.From.Value;
}
if (condition.To.HasValue)
{
where += " and o.UpdatedOn<@To ";
param.To = condition.To.Value.AddDays(1);
}
if (condition.OrderLevel > 0)
{
where += " and o.OrderLevel=@OrderLevel ";
param.OrderLevel = condition.OrderLevel;
}
if (condition.PaymentWay > 0)
{
where += " and o.PaymentWay=@PaymentWay ";
param.PaymentWay = condition.PaymentWay;
}
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 += " and p.Name like @ProductName ";
param.ProductName = string.Format("%{0}%", condition.ProductName);
}
string sql = @"select o.Id, o.`Code`,o.PosId,o.OrderType,o.`Status`,o.OrderAmount,o.PayAmount,o.OnlinePayAmount,o.PaymentWay,o.PaidDate,o.UpdatedOn,o.OrderLevel,
a.NickName,s.Name as StoreName,i.ProductCode,i.ProductName,i.ProductId,i.Quantity,i.RealPrice,i.AvgCostPrice,i.RealPrice*i.Quantity as Amount,i.AvgCostPrice*i.Quantity as CostAmount,p.BarCode,p.Specification
from saleorder o
INNER JOIN saleorderitem i on o.Id = i.SaleOrderId
left join product p on p.Id = i.ProductId
left join store s on s.Id= o.StoreId
left join account a on a.Id = o.CreatedBy
where 1=1 {0} ORDER BY o.Id desc LIMIT {1},{2}";
if (string.IsNullOrEmpty(where))
{
page.Total = 0;
return(new List <SaleOrderListDto>());
}
sql = string.Format(sql, where, (page.PageIndex - 1) * page.PageSize, page.PageSize);
var rows = this._query.FindAll <SaleOrderListDto>(sql, param) as IEnumerable <SaleOrderListDto>;
var sqlSum = @"select count(*) TotalCount,sum(i.Quantity) Quantity,sum(i.RealPrice*i.Quantity) Amount,sum(i.AvgCostPrice*i.Quantity) as CostAmount
from saleorder o
INNER JOIN saleorderitem i on o.Id = i.SaleOrderId
left join product p on p.Id = i.ProductId
left join store s on s.Id= o.StoreId
left join account a on a.Id = o.CreatedBy
where o.Status=3 {0} ";
sqlSum = string.Format(sqlSum, where);
var sumStoreInventory = this._query.Find <SumSaleOrder>(sqlSum, param) as SumSaleOrder;
page.Total = sumStoreInventory.TotalCount;
page.SumColumns.Add(new SumColumn("Quantity", sumStoreInventory.Quantity.ToString()));
page.SumColumns.Add(new SumColumn("CostAmount", sumStoreInventory.CostAmount.ToString("F4")));
page.SumColumns.Add(new SumColumn("Amount", sumStoreInventory.Amount.ToString("F2")));
return(rows);
}
public IEnumerable <SaleCheckDto> QuerySaleCheck(Pager page, SearchSaleOrder condition)
{
dynamic param = new ExpandoObject();
string where = "";
string owhere = "";
if (!string.IsNullOrEmpty(condition.Code))
{
owhere += "and o.Code=@Code ";
param.Code = condition.Code;
}
if (condition.PosId.HasValue)
{
owhere += " and o.PosId=@PosId ";
param.PosId = condition.PosId.Value;
}
if (!string.IsNullOrEmpty(condition.StoreId) && condition.StoreId != "0")
{
owhere += "and o.StoreId in @StoreId ";
param.StoreId = condition.StoreId.Split(',').ToIntArray();;
}
if (condition.From.HasValue)
{
owhere += " and o.UpdatedOn>=@From";
param.From = condition.From.Value;
}
if (condition.To.HasValue)
{
owhere += " and o.UpdatedOn<@To";
param.To = condition.To.Value.AddDays(1);
}
if (!string.IsNullOrEmpty(condition.NickName))
{
where += " and w.CreatedByName like @NickName ";
param.NickName = string.Format("%{0}%", condition.NickName);
}
if (condition.WrokFrom.HasValue)
{
where += " and w.StartDate >= @StartDate ";
param.StartDate = condition.WrokFrom.Value;
}
if (condition.WrokTo.HasValue)
{
where += " and w.StartDate < @EndDate ";
param.EndDate = condition.WrokTo.Value.AddDays(1);
}
string sql = @"select w.Id, w.CreatedByName,s.Name as StoreName,w.PosId,w.StartDate,w.EndDate,t.TotalAmount,t.orderCount,t.Status,t.OrderType from WorkSchedule w inner join (
select o.WorkScheduleCode,sum(OrderAmount) as TotalAmount,count(*) as orderCount,o.Status,o.OrderType from saleorder o
where (o.Status = -1 or o.OrderType =2) {1} group by o.WorkScheduleCode,o.Status,o.OrderType
) t on t.WorkScheduleCode = w.Code
inner join Store s on s.Id = w.StoreId
where 1=1 {0}";
//rows = this._query.FindPage<ProductDto>(page.PageIndex, page.PageSize).Where<Product>(where, param);
if (string.IsNullOrEmpty(where) && string.IsNullOrEmpty(owhere))
{
page.Total = 0;
return(new List <SaleCheckDto>());
}
sql = string.Format(sql, where, owhere);
var rows = this._query.FindAll <SaleCheckDto>(sql, param) as IEnumerable <SaleCheckDto>;
page.Total = rows.Count();
return(rows);
}
public IEnumerable <SaleSummaryDto> QuerySaleSummary(Pager page, SearchSaleOrder condition)
{
dynamic param = new ExpandoObject();
string where = "";
string owhere = "";
if (!string.IsNullOrEmpty(condition.Code))
{
owhere += "and o.Code=@Code ";
param.Code = condition.Code;
}
if (condition.PosId.HasValue)
{
owhere += " and o.PosId=@PosId ";
param.PosId = condition.PosId.Value;
}
if (!string.IsNullOrEmpty(condition.StoreId) && condition.StoreId != "0")
{
owhere += "and o.StoreId in @StoreId ";
param.StoreId = condition.StoreId.Split(',').ToIntArray();;
}
if (condition.From.HasValue)
{
owhere += " and o.UpdatedOn>=@From ";
param.From = condition.From.Value;
}
if (condition.To.HasValue)
{
owhere += " and o.UpdatedOn<@To ";
param.To = condition.To.Value.AddDays(1);
}
if (condition.OrderLevel > 0)
{
owhere += " and o.OrderLevel=@OrderLevel ";
param.OrderLevel = condition.OrderLevel;
}
if (condition.PaymentWay > 0)
{
owhere += " and o.PaymentWay=@PaymentWay ";
param.PaymentWay = condition.PaymentWay;
}
if (!string.IsNullOrEmpty(condition.NickName))
{
where += " and w.CreatedByName like @NickName ";
param.NickName = string.Format("%{0}%", condition.NickName);
}
if (condition.WrokFrom.HasValue)
{
where += " and w.StartDate >= @StartDate ";
param.StartDate = condition.WrokFrom.Value;
}
if (condition.WrokTo.HasValue)
{
where += " and w.StartDate < @EndDate ";
param.EndDate = condition.WrokTo.Value.AddDays(1);
}
string sql = @"select w.Id, w.CreatedByName,s.Name as StoreName,w.PosId,w.CashAmount,w.StartDate,w.EndDate,t.TotalAmount,t.TotalOnlineAmount,t.paymentWay from WorkSchedule w inner join (
select o.WorkScheduleCode,sum(OrderAmount) as TotalAmount,sum(OnlinePayAmount) as TotalOnlineAmount,paymentWay from saleorder o
where o.Status = 3 {1} group by o.WorkScheduleCode,o.paymentWay
) t on t.WorkScheduleCode = w.Code
inner join Store s on s.Id = w.StoreId
where 1=1 {0} ORDER BY w.Id desc LIMIT {2},{3}";
//rows = this._query.FindPage<ProductDto>(page.PageIndex, page.PageSize).Where<Product>(where, param);
if (string.IsNullOrEmpty(where) && string.IsNullOrEmpty(owhere))
{
page.Total = 0;
return(new List <SaleSummaryDto>());
}
sql = string.Format(sql, where, owhere, (page.PageIndex - 1) * page.PageSize, page.PageSize);
var rows = this._query.FindAll <SaleSummaryDto>(sql, param) as IEnumerable <SaleSummaryDto>;
string sqlCount = @"select count(*) from WorkSchedule w inner join (
select o.WorkScheduleCode,sum(OrderAmount) as TotalAmount,sum(OnlinePayAmount) as TotalOnlineAmount,paymentWay from saleorder o
where o.Status = 3 {1} group by o.WorkScheduleCode,o.paymentWay
) t on t.WorkScheduleCode = w.Code
inner join Store s on s.Id = w.StoreId
where 1=1 {0}";
sqlCount = string.Format(sqlCount, where, owhere);
page.Total = this._query.Context.ExecuteScalar <int>(sqlCount, param);
//汇总
string sqlSum = @"select sum(w.CashAmount) as CashAmount,sum(t.TotalAmount) as TotalAmount,sum(t.TotalOnlineAmount) as TotalOnlineAmount from WorkSchedule w inner join (
select o.WorkScheduleCode,sum(OrderAmount) as TotalAmount,sum(OnlinePayAmount) as TotalOnlineAmount,paymentWay from saleorder o
where o.Status = 3 {1} group by o.WorkScheduleCode,o.paymentWay
) t on t.WorkScheduleCode = w.Code
inner join Store s on s.Id = w.StoreId
where 1=1 {0}";
sqlSum = string.Format(sqlSum, where, owhere);
var sum = this._query.Find <SumWorkSchedule>(sqlSum, param) as SumWorkSchedule;
page.SumColumns.Add(new SumColumn("CashAmount", sum.CashAmount.ToString("F2")));
page.SumColumns.Add(new SumColumn("TotalAmount", sum.TotalAmount.ToString("F2")));
page.SumColumns.Add(new SumColumn("TotalOnlineAmount", sum.TotalOnlineAmount.ToString("F2")));
return(rows);
}
