public ActionResult BestSellingProductsByCustomer(int customer, DateRange dates)
{
var start = dates.StartDate.Date;
var end = dates.EndDate.Date.AddDays (1).AddSeconds (-1);
var qry = from x in SalesOrder.Queryable
from y in x.Details
where x.Customer.Id == customer &&
x.IsCompleted &&
x.IsPaid &&
!x.IsCancelled &&
x.Date >= start &&
x.Date <= end
select new {
Id = y.ProductCode,
Name = y.ProductName,
Units = y.Quantity,
Total = y.Quantity * y.Price,
Subtotal = y.Quantity * y.Price / (y.TaxRate + 1m)
};
var qry2 = from x in qry.ToList ()
group x by new { x.Id, x.Name } into g
select new SummaryItem {
Id = g.Key.Id,
Name = g.Key.Name,
Units = g.Sum (x => x.Units),
Total = g.Sum (x => x.Total),
Subtotal = g.Sum (x => x.Subtotal),
};
var items = qry2.OrderByDescending (x => x.Total).ToList ();
AnalyzeABC (items);
return PartialView ("_SummaryReport", items);
}
public ActionResult SalesOrderSummary(int store, DateRange dates)
{
var start = dates.StartDate.Date;
var end = dates.EndDate.Date.AddDays (1).AddSeconds (-1);
string sql = @"SELECT date Date, CONCAT(first_name, ' ', last_name) SalesPerson, sales_order SalesOrder, m.due_date DueDate, c.name Customer,
GROUP_CONCAT(DISTINCT (SELECT GROUP_CONCAT(DISTINCT f.batch, f.serial SEPARATOR ' ')
FROM fiscal_document_detail fd LEFT JOIN fiscal_document f ON fd.document = f.fiscal_document_id
WHERE fd.order_detail = d.sales_order_detail_id) SEPARATOR ' ') Invoices,
SUM(ROUND(d.quantity * d.price * d.exchange_rate * (1 - d.discount) * IF(d.tax_included = 0, 1 + d.tax_rate, 1), 2)) TotalEx,
SUM(ROUND(d.quantity * d.price * (1 - d.discount) * IF(d.tax_included = 0, 1 + d.tax_rate, 1), 2)) Total,
m.currency Currency
FROM sales_order m
INNER JOIN sales_order_detail d ON m.sales_order_id = d.sales_order
INNER JOIN employee e ON m.salesperson = e.employee_id
INNER JOIN customer c ON m.customer = c.customer_id
WHERE m.store = :store AND m.completed = 1 AND m.cancelled = 0 AND
m.date >= :start AND m.date <= :end
GROUP BY sales_order";
var items = (IList<dynamic>) ActiveRecordMediator<Product>.Execute (delegate (ISession session, object instance) {
var query = session.CreateSQLQuery (sql);
query.AddScalar ("Date", NHibernateUtil.DateTime);
query.AddScalar ("SalesPerson", NHibernateUtil.String);
query.AddScalar ("SalesOrder", NHibernateUtil.Int32);
query.AddScalar ("Invoices", NHibernateUtil.String);
query.AddScalar ("DueDate", NHibernateUtil.DateTime);
query.AddScalar ("Customer", NHibernateUtil.String);
query.AddScalar ("TotalEx", NHibernateUtil.Decimal);
query.AddScalar ("Total", NHibernateUtil.Decimal);
query.AddScalar ("Currency", NHibernateUtil.Int32);
query.SetDateTime ("start", start);
query.SetDateTime ("end", end);
query.SetInt32 ("store", store);
return query.DynamicList ();
}, null);
return PartialView ("_SalesOrderSummary", items);
}
public ActionResult SalesBySalesPerson(int store, DateRange dates)
{
var start = dates.StartDate.Date;
var end = dates.EndDate.Date.AddDays (1).AddSeconds (-1);
string sql = @"SELECT salesperson SalesPersonId, first_name FirstName, last_name LastName,
SUM(quantity) Units,
SUM(ROUND(quantity * price * d.exchange_rate * (1 - discount) / IF(tax_included = 0, 1, 1 + tax_rate), 2)) Subtotal,
SUM(ROUND(quantity * price * d.exchange_rate * (1 - discount) * IF(tax_included = 0, 1 + tax_rate, 1), 2)) Total
FROM sales_order m
INNER JOIN sales_order_detail d ON m.sales_order_id = d.sales_order
INNER JOIN employee e ON m.salesperson = e.employee_id
WHERE m.store = :store AND m.completed = 1 AND m.cancelled = 0 AND
m.date >= :start AND m.date <= :end
GROUP BY salesperson, first_name, last_name";
var items = (IList<dynamic>) ActiveRecordMediator<Product>.Execute (delegate (ISession session, object instance) {
return session.CreateSQLQuery (sql)
.SetParameter ("start", start)
.SetParameter ("end", end)
.SetParameter ("store", store)
.DynamicList ();
}, null);
return PartialView ("_SalesBySalesPerson", items);
}
public ActionResult ReceivedPayments(int store, DateRange dates)
{
var start = dates.StartDate.Date;
var end = dates.EndDate.Date.AddDays (1).AddSeconds (-1);
var qry = from x in CustomerPayment.Queryable
where x.Store.Id == store &&
x.Date >= start && x.Date <= end &&
x.Amount > 0
select new ReceivedPayment {
Date = x.Date,
/*SalesOrder = x.SalesOrder.Id,
Serial = x.SalesOrder.Serial,*/
Customer = x.Customer,
Method = x.Method,
Amount = x.Amount
};
return PartialView ("_ReceivedPayments", qry.ToList ());
}
public ActionResult ProductSalesBySalesPersonAndModel(int employee, string productModel, DateRange dates)
{
var start = dates.StartDate.Date;
var end = dates.EndDate.Date.AddDays (1).AddSeconds (-1);
string sql = @"SELECT p.brand Brand, p.model Model, p.code Code, p.name Name,
SUM(quantity) Units,
SUM(ROUND(d.quantity * d.price * d.exchange_rate * (1 - d.discount) / IF(d.tax_included = 0, 1, 1 + d.tax_rate), 2)) Subtotal,
SUM(ROUND(d.quantity * d.price * d.exchange_rate * (1 - d.discount) * IF(d.tax_included = 0, 1 + d.tax_rate, 1), 2)) Total
FROM sales_order m
INNER JOIN sales_order_detail d ON m.sales_order_id = d.sales_order
INNER JOIN product p ON d.product = p.product_id
WHERE m.salesperson = :employee AND m.completed = 1 AND m.cancelled = 0 AND
m.date >= :start AND m.date <= :end WHERE_MODEL
GROUP BY d.product";
if (string.IsNullOrWhiteSpace (productModel)) {
sql = sql.Replace ("WHERE_MODEL", string.Empty);
} else {
sql = sql.Replace ("WHERE_MODEL", "AND p.model = :model");
}
var items = (IList<dynamic>) ActiveRecordMediator<Product>.Execute (delegate (ISession session, object instance) {
var query = session.CreateSQLQuery (sql);
query.AddScalar ("Brand", NHibernateUtil.String);
query.AddScalar ("Model", NHibernateUtil.String);
query.AddScalar ("Code", NHibernateUtil.String);
query.AddScalar ("Name", NHibernateUtil.String);
query.AddScalar ("Units", NHibernateUtil.Decimal);
query.AddScalar ("Subtotal", NHibernateUtil.Decimal);
query.AddScalar ("Total", NHibernateUtil.Decimal);
query.SetDateTime ("start", start);
query.SetDateTime ("end", end);
query.SetInt32 ("employee", employee);
if (!string.IsNullOrWhiteSpace (productModel)) {
query.SetString ("model", productModel);
}
return query.DynamicList ();
}, null);
return PartialView ("_ProductSalesBySalesPerson", items);
}
public ActionResult ProductSalesByModel(string productModel, DateRange dates)
{
var start = dates.StartDate.Date;
var end = dates.EndDate.Date.AddDays (1).AddSeconds (-1);
var query = from x in SalesOrder.Queryable
from y in x.Details
where x.IsCompleted && !x.IsCancelled &&
x.Date >= start && x.Date <= end &&
y.Product.Model.Contains (productModel)
orderby y.ProductName
select new {
Model = y.Product.Model,
Brand = y.Product.Brand,
Code = y.Product.Code,
Name = y.Product.Name,
Quantity = y.Quantity,
Price = y.Price,
ExchangeRate = y.ExchangeRate,
Discount = y.Discount,
TaxRate = y.TaxRate,
IsTaxIncluded = y.IsTaxIncluded
};
var items = from x in query.ToList ()
group x by new { x.Model, x.Brand, x.Code, x.Name } into g
select new SummaryItem {
Id = g.Key.Brand,
Category = g.Key.Model,
Code = g.Key.Code,
Name = g.Key.Name,
Units = g.Sum (y => y.Quantity),
Total = g.Sum (y => Model.ModelHelpers.Total (y.Quantity, y.Price, y.ExchangeRate, y.Discount, y.TaxRate, y.IsTaxIncluded)),
Subtotal = g.Sum (y => Model.ModelHelpers.Subtotal (y.Quantity, y.Price, y.ExchangeRate, y.Discount, y.TaxRate, y.IsTaxIncluded))
};
return PartialView ("_ProductSalesByCategory", items);
}
public ActionResult Kardex(int warehouse, int product, DateRange dates)
{
var start = dates.StartDate.Date;
var end = dates.EndDate.Date.AddDays (1).AddSeconds (-1);
var balance = from x in LotSerialTracking.Queryable
where x.Warehouse.Id == warehouse && x.Product.Id == product && x.Date < start
select x.Quantity;
ViewBag.OpeningBalance = balance.Count () > 0 ? balance.Sum () : 0m;
string sql = @"SELECT DATE(l.date) Date, l.source Source, l.reference Reference, l.lot_number LotNumber, l.expiration_date ExpirationDate, SUM(quantity) Quantity
FROM lot_serial_tracking l
WHERE warehouse = :warehouse AND product = :product AND date >= :start AND date <= :end
GROUP BY DATE(l.date), l.source, l.reference, l.lot_number, l.expiration_date
ORDER BY l.date";
var items = (IList<dynamic>) ActiveRecordMediator<Product>.Execute (delegate (ISession session, object instance) {
var query = session.CreateSQLQuery (sql);
query.AddScalar ("Date", NHibernateUtil.Date);
query.AddScalar ("Source", NHibernateUtil.Int32);
query.AddScalar ("Reference", NHibernateUtil.Int32);
query.AddScalar ("LotNumber", NHibernateUtil.String);
query.AddScalar ("ExpirationDate", NHibernateUtil.Date);
query.AddScalar ("Quantity", NHibernateUtil.Decimal);
query.SetInt32 ("warehouse", warehouse);
query.SetInt32 ("product", product);
query.SetDateTime ("start", start);
query.SetDateTime ("end", end);
return query.DynamicList ();
}, null);
return PartialView ("_Kardex", items);
}
public ActionResult ProductSalesByCustomer(int customer, DateRange dates)
{
var start = dates.StartDate.Date;
var end = dates.EndDate.Date.AddDays (1).AddSeconds (-1);
var query = from x in SalesOrder.Queryable
from y in x.Details
where x.Customer.Id == customer &&
x.IsCompleted && !x.IsCancelled &&
x.Date >= start && x.Date <= end
select new {
SalesOrder = x.Id,
Code = y.ProductCode,
Name = y.ProductName,
Quantity = y.Quantity,
Price = y.Price,
ExchangeRate = y.ExchangeRate,
Discount = y.Discount,
TaxRate = y.TaxRate,
IsTaxIncluded = y.IsTaxIncluded
};
var items = from x in query.ToList ()
select new SummaryItem {
Category = x.SalesOrder.ToString (),
Id = x.Code,
Name = x.Name,
Units = x.Quantity,
Total = Model.ModelHelpers.Total (x.Quantity, x.Price, x.ExchangeRate, x.Discount, x.TaxRate, x.IsTaxIncluded),
Subtotal = Model.ModelHelpers.Subtotal (x.Quantity, x.Price, x.ExchangeRate, x.Discount, x.TaxRate, x.IsTaxIncluded)
};
return PartialView ("_ProductSalesByCustomer", items);
}
public ActionResult FiscalDocuments(string taxpayer, DateRange dates)
{
var start = dates.StartDate.Date;
var end = dates.EndDate.Date.AddDays (1).AddSeconds (-1);
var query = from x in FiscalDocument.Queryable
where x.Issuer.Id == taxpayer && x.IsCompleted &&
((x.Issued >= start && x.Issued <= end) || (x.CancellationDate >= start && x.CancellationDate <= end))
orderby x.Issued
select x;
return PartialView ("_FiscalDocuments", query.ToList ());
}
public ActionResult GrossProfitsBySalesPerson(int store, DateRange dates)
{
var start = dates.StartDate.Date;
var end = dates.EndDate.Date.AddDays (1).AddSeconds (-1);
var qry = from x in SalesOrder.Queryable
from y in x.Details
where x.Store.Id == store &&
x.IsCompleted &&
x.IsPaid &&
!x.IsCancelled &&
x.Date >= start &&
x.Date <= end
select new {
Id = x.SalesPerson.Id,
Name = x.SalesPerson.FirstName + " " + x.SalesPerson.LastName,
Units = y.Quantity,
Total = y.Quantity * (y.Price - y.Cost),
Subtotal = y.Quantity * (y.Price - y.Cost) / (y.TaxRate + 1m)
};
var qry2 = from x in qry.ToList ()
group x by new { x.Id, x.Name } into g
select new SummaryItem {
Id = g.Key.Id.ToString (),
Name = g.Key.Name,
Units = g.Sum (x => x.Units),
Total = g.Sum (x => x.Total),
Subtotal = g.Sum (x => x.Subtotal)
};
var items = qry2.OrderByDescending (x => x.Total).ToList ();
AnalyzeABC (items);
return PartialView ("_SummaryReport", items);
}
public ActionResult CustomerSalesOrders(int customer, DateRange dates)
{
var start = dates.StartDate.Date;
var end = dates.EndDate.Date.AddDays (1).AddSeconds (-1);
var query = from x in SalesOrder.Queryable
where x.Customer.Id == customer &&
x.IsCompleted && !x.IsCancelled &&
x.Date >= start && x.Date <= end
orderby x.Date
select x;
return PartialView ("_CustomerSalesOrders", query.ToList ());
}
public ActionResult SalesPersonOrdersAndRefunds(int employee, DateRange dates)
{
var start = dates.StartDate.Date;
var end = dates.EndDate.Date.AddDays (1).AddSeconds (-1);
string sql = @"SELECT sales_order SalesOrder, 0 Refund, date Date, name Customer,
GROUP_CONCAT(DISTINCT (SELECT GROUP_CONCAT(DISTINCT f.batch, f.serial SEPARATOR ' ')
FROM fiscal_document_detail fd LEFT JOIN fiscal_document f ON fd.document = f.fiscal_document_id
WHERE fd.order_detail = d.sales_order_detail_id) SEPARATOR ' ') Invoices,
SUM(ROUND(d.quantity * d.price * d.exchange_rate * (1 - d.discount) / IF(d.tax_included = 0, 1, 1 + d.tax_rate), 2)) Subtotal,
SUM(ROUND(d.quantity * d.price * d.exchange_rate * (1 - d.discount) * IF(d.tax_included = 0, 1 + d.tax_rate, 1), 2)) Total
FROM sales_order m
INNER JOIN sales_order_detail d ON m.sales_order_id = d.sales_order
INNER JOIN customer c ON m.customer = c.customer_id
WHERE m.salesperson = :employee AND m.completed = 1 AND m.cancelled = 0 AND
m.date >= :start AND m.date <= :end
GROUP BY sales_order";
var orders = (IList<dynamic>) ActiveRecordMediator<Product>.Execute (delegate (ISession session, object instance) {
var query = session.CreateSQLQuery (sql);
query.AddScalar ("SalesOrder", NHibernateUtil.Int32);
query.AddScalar ("Refund", NHibernateUtil.Int32);
query.AddScalar ("Date", NHibernateUtil.DateTime);
query.AddScalar ("Customer", NHibernateUtil.String);
query.AddScalar ("Invoices", NHibernateUtil.String);
query.AddScalar ("Subtotal", NHibernateUtil.Decimal);
query.AddScalar ("Total", NHibernateUtil.Decimal);
query.SetDateTime ("start", start);
query.SetDateTime ("end", end);
query.SetInt32 ("employee", employee);
return query.DynamicList ();
}, null);
sql = @"SELECT sales_order SalesOrder, customer_refund Refund, s.date Date, name Customer,
GROUP_CONCAT(DISTINCT (SELECT GROUP_CONCAT(DISTINCT f.batch, f.serial SEPARATOR ' ')
FROM fiscal_document_detail fd LEFT JOIN fiscal_document f ON fd.document = f.fiscal_document_id
WHERE fd.order_detail = d.sales_order_detail) SEPARATOR ' ') Invoices,
-SUM(ROUND(d.quantity * d.price * d.exchange_rate * (1 - d.discount) / IF(d.tax_included = 0, 1, 1 + d.tax_rate), 2)) Subtotal,
-SUM(ROUND(d.quantity * d.price * d.exchange_rate * (1 - d.discount) * IF(d.tax_included = 0, 1 + d.tax_rate, 1), 2)) Total
FROM customer_refund m
INNER JOIN sales_order s ON m.sales_order = s.sales_order_id
INNER JOIN customer_refund_detail d ON m.customer_refund_id = d.customer_refund
INNER JOIN customer c ON m.customer = c.customer_id
WHERE m.sales_person = :employee AND m.completed = 1 AND m.cancelled = 0 AND
s.date >= :start AND s.date <= :end
GROUP BY sales_order";
var refunds = (IList<dynamic>) ActiveRecordMediator<Product>.Execute (delegate (ISession session, object instance) {
var query = session.CreateSQLQuery (sql);
query.AddScalar ("SalesOrder", NHibernateUtil.Int32);
query.AddScalar ("Refund", NHibernateUtil.Int32);
query.AddScalar ("Date", NHibernateUtil.DateTime);
query.AddScalar ("Customer", NHibernateUtil.String);
query.AddScalar ("Invoices", NHibernateUtil.String);
query.AddScalar ("Subtotal", NHibernateUtil.Decimal);
query.AddScalar ("Total", NHibernateUtil.Decimal);
query.SetDateTime ("start", start);
query.SetDateTime ("end", end);
query.SetInt32 ("employee", employee);
return query.DynamicList ();
}, null);
var items = orders.ToList ();
items.AddRange (refunds);
return PartialView ("_SalesPersonOrdersAndRefunds", items);
}
Search<SupplierReturn> GetSupplierReturns(DateRange dates, Search<SupplierReturn> search)
{
var qry = from x in SupplierReturn.Queryable
where (x.IsCompleted || x.IsCancelled) &&
(x.ModificationTime >= dates.StartDate.Date && x.ModificationTime <= dates.EndDate.Date.Add (new TimeSpan (23, 59, 59)))
orderby x.Id descending
select x;
search.Total = qry.Count ();
search.Results = qry.Skip (search.Offset).Take (search.Limit).ToList ();
return search;
}
public ActionResult Historic(DateRange item, Search<SupplierReturn> search)
{
ViewBag.SupplierReturnsDates = item;
search.Limit = WebConfig.PageSize;
search = GetSupplierReturns (item, search);
return PartialView ("_Historic", search);
}
public ViewResult Historic()
{
DateRange item = new DateRange ();
item.StartDate = DateTime.Now;
item.EndDate = DateTime.Now;
return View ("Historic", item);
}
