253 lines
11 KiB
C#
253 lines
11 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using Tanshu.Accounts.Contracts;
|
|
using Tanshu.Accounts.Entities;
|
|
using Tanshu.Common;
|
|
using Tanshu.Common.Helpers;
|
|
using System.Linq;
|
|
using NHibernate.Transform;
|
|
using NHibernate.Criterion;
|
|
|
|
namespace Tanshu.Accounts.Repository
|
|
{
|
|
public class ReportsBI
|
|
{
|
|
public IList<BeerConsumptionDetail> BeerConsumption(DateTime startDate, DateTime finishDate)
|
|
{
|
|
startDate = startDate.Date.AddHours(6);
|
|
finishDate = finishDate.Date.AddDays(1).AddHours(5);
|
|
if (finishDate <= startDate)
|
|
return new List<BeerConsumptionDetail>();
|
|
|
|
using (var session = SessionManager.Session)
|
|
{
|
|
const string query = @"
|
|
select v.Date, p.ProductID, p.Name, Sum(i.Quantity * p.Quantity)
|
|
from Voucher v
|
|
inner join v.Kots k
|
|
inner join k.Inventories i
|
|
inner join i.Product p
|
|
where v.Date >= :startDate and v.Date <= :finishDate and v.Void = false
|
|
and exists (select Voucher from VoucherSettlement vs where vs.Voucher = v
|
|
and vs.Settled in (:cash, :creditCard, :billToCompany, :staff, :noCharge))
|
|
group by v.Date, p.ProductID, p.Name
|
|
order by v.Date, p.ProductID, p.Name
|
|
";
|
|
var list = session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.SetParameter("cash", SettleOption.Cash)
|
|
.SetParameter("creditCard", SettleOption.CreditCard)
|
|
.SetParameter("billToCompany", SettleOption.BillToCompany)
|
|
.SetParameter("staff", SettleOption.Staff)
|
|
.SetParameter("noCharge", SettleOption.NoCharge)
|
|
.List<object[]>();
|
|
var outList = new List<BeerConsumptionDetail>();
|
|
foreach (var item in list)
|
|
if ((decimal)item[3] != 0)
|
|
outList.Add(new BeerConsumptionDetail() { Date = (DateTime)item[0], ProductID = (Guid)item[1], Name = (string)item[2], Quantity = (decimal)item[3] });
|
|
return outList;
|
|
}
|
|
}
|
|
public IList<SalesAnalysisDetail> SaleQuantity(DateTime startDate, DateTime finishDate)
|
|
{
|
|
startDate = startDate.Date.AddHours(6);
|
|
finishDate = finishDate.Date.AddDays(1).AddHours(5);
|
|
if (finishDate <= startDate)
|
|
return new List<SalesAnalysisDetail>();
|
|
|
|
using (var session = SessionManager.Session)
|
|
{
|
|
const string query = @"
|
|
select p.ProductID, p.FullName, i.IsHappyHour, Sum(i.Quantity)
|
|
from Voucher v
|
|
inner join v.Kots k
|
|
inner join k.Inventories i
|
|
inner join i.Product p
|
|
inner join p.ProductGroup pg
|
|
where v.Date >= :startDate and v.Date <= :finishDate and v.Void = false
|
|
and exists (select Voucher from VoucherSettlement vs where vs.Voucher = v
|
|
and vs.Settled in (:cash, :creditCard, :billToCompany, :staff))
|
|
group by p.ProductID, p.FullName, i.IsHappyHour, p.ProductGroup, pg.GroupType
|
|
order by pg.GroupType, p.ProductGroup, p.FullName, i.IsHappyHour
|
|
";
|
|
var list = session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.SetParameter("cash", SettleOption.Cash)
|
|
.SetParameter("creditCard", SettleOption.CreditCard)
|
|
.SetParameter("billToCompany", SettleOption.BillToCompany)
|
|
.SetParameter("staff", SettleOption.Staff)
|
|
.List<object[]>();
|
|
var outList = new List<SalesAnalysisDetail>();
|
|
foreach (var item in list)
|
|
outList.Add(new SalesAnalysisDetail() { ProductID = (Guid)item[0], Name = (string)item[1], IsHappyHour = (bool)item[2], Sale = (decimal)item[3] });
|
|
return outList;
|
|
}
|
|
}
|
|
public IList<SalesAnalysisDetail> NcQuantity(DateTime startDate, DateTime finishDate)
|
|
{
|
|
startDate = startDate.Date.AddHours(6);
|
|
finishDate = finishDate.Date.AddDays(1).AddHours(5);
|
|
if (finishDate <= startDate)
|
|
return new List<SalesAnalysisDetail>();
|
|
|
|
using (var session = SessionManager.Session)
|
|
{
|
|
const string query = @"
|
|
select p.ProductID, p.FullName, i.IsHappyHour, Sum(i.Quantity)
|
|
from Voucher v
|
|
inner join v.Kots k
|
|
inner join k.Inventories i
|
|
inner join i.Product p
|
|
where v.Date >= :startDate and v.Date <= :finishDate and v.Void = false
|
|
and exists (select Voucher from VoucherSettlement vs where vs.Voucher = v and vs.Settled = :noCharge)
|
|
group by p.ProductID, p.FullName, i.IsHappyHour
|
|
";
|
|
var list = session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.SetParameter("noCharge", SettleOption.NoCharge)
|
|
.List<object[]>(); var outList = new List<SalesAnalysisDetail>();
|
|
foreach (var item in list)
|
|
outList.Add(new SalesAnalysisDetail() { ProductID = (Guid)item[0], Name = (string)item[1], IsHappyHour = (bool)item[2], NC = (decimal)item[3] });
|
|
return outList;
|
|
}
|
|
}
|
|
|
|
public IList<BillDetail> GetBillDetails(DateTime startDate, DateTime finishDate)
|
|
{
|
|
startDate = startDate.Date.AddHours(6);
|
|
finishDate = finishDate.Date.AddDays(1).AddHours(5);
|
|
if (finishDate <= startDate)
|
|
return new List<BillDetail>();
|
|
using (var session = SessionManager.Session)
|
|
{
|
|
Voucher vAlias = null;
|
|
VoucherSettlement vsAlias = null;
|
|
|
|
var list = session.QueryOver<Voucher>(() => vAlias)
|
|
.Left.JoinAlias(x => x.Settlements, () => vsAlias)
|
|
.Where(x => x.Date >= startDate && x.Date <= finishDate)
|
|
.OrderBy(x => x.VoucherType).Asc
|
|
.ThenBy(x => x.BillID).Asc
|
|
.TransformUsing(Transformers.DistinctRootEntity)
|
|
.List();
|
|
|
|
var outList = new List<BillDetail>();
|
|
foreach (var item in list)
|
|
{
|
|
if (item.Void)
|
|
outList.Add(new BillDetail()
|
|
{
|
|
Date = item.Date,
|
|
BillID = item.FullBillID,
|
|
Settlement = string.Format("Void: {0}", item.VoidReason),
|
|
Amount = Math.Round(item.Settlements.Single(x => x.Settled == SettleOption.Amount).Amount * -1, 2)
|
|
});
|
|
|
|
else
|
|
{
|
|
foreach (var so in item.Settlements.Where(x => x.Settled.Visible()))
|
|
{
|
|
outList.Add(new BillDetail()
|
|
{
|
|
Date = item.Date,
|
|
BillID = item.FullBillID,
|
|
Settlement = so.Settled.Display(),
|
|
Amount = Math.Round(so.Amount, 2)
|
|
});
|
|
}
|
|
}
|
|
}
|
|
return outList;
|
|
}
|
|
}
|
|
public IList<SalesAnalysis> DiscountReport(DateTime startDate, DateTime finishDate)
|
|
{
|
|
startDate = startDate.Date.AddHours(6);
|
|
finishDate = finishDate.Date.AddDays(1).AddHours(5);
|
|
if (finishDate <= startDate)
|
|
return new List<SalesAnalysis>();
|
|
using (var session = SessionManager.Session)
|
|
{
|
|
const string query = @"
|
|
select pg.GroupType, sum(i.Quantity * i.EffectivePrice * i.Discount)
|
|
from Inventory i
|
|
inner join i.Kot k
|
|
inner join k.Voucher v
|
|
inner join i.Product p
|
|
inner join p.ProductGroup pg
|
|
where v.Date >= :startDate and v.Date <= :finishDate and i.Discount != 0
|
|
and v not in (select Voucher from VoucherSettlement vs where vs.Voucher = v and (vs.Settled = :noCharge or vs.Settled = :staff))
|
|
group by pg.GroupType
|
|
order by pg.GroupType
|
|
";
|
|
var list = session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.SetParameter("noCharge", SettleOption.NoCharge)
|
|
.SetParameter("staff", SettleOption.Staff)
|
|
.List<object[]>();
|
|
return list.Select(item => new SalesAnalysis()
|
|
{
|
|
GroupType = (string)item[0],
|
|
Amount = (decimal)item[1]
|
|
}).ToList();
|
|
}
|
|
}
|
|
public IList<BillDetail> VoidOrReprintedBillsList(DateTime startDate, DateTime finishDate)
|
|
{
|
|
startDate = startDate.Date.AddHours(6);
|
|
finishDate = finishDate.Date.AddDays(1).AddHours(5);
|
|
if (finishDate <= startDate)
|
|
return new List<BillDetail>();
|
|
using (var session = SessionManager.Session)
|
|
{
|
|
Voucher vAlias = null;
|
|
VoucherSettlement vsAlias = null;
|
|
ICriterion amount = Restrictions.Where<VoucherSettlement>(x => x.Settled == SettleOption.Amount);
|
|
|
|
var listVoids = session.QueryOver<Voucher>(() => vAlias)
|
|
.Left.JoinAlias(x => x.Settlements, () => vsAlias, amount)
|
|
.Where(x => x.Date >= startDate && x.Date <= finishDate && x.Void == true)
|
|
.OrderBy(x => x.VoucherType).Asc
|
|
.ThenBy(x => x.BillID).Asc
|
|
.TransformUsing(Transformers.DistinctRootEntity)
|
|
.List();
|
|
|
|
var outList = new List<BillDetail>();
|
|
foreach (var item in listVoids)
|
|
{
|
|
outList.Add(new BillDetail()
|
|
{
|
|
Date = item.Date,
|
|
BillID = item.FullBillID,
|
|
Settlement = string.Format("Void: {0}", item.VoidReason),
|
|
Amount = Math.Round(item.Settlements.SingleOrDefault().Amount * -1, 2)
|
|
});
|
|
}
|
|
var listReprint = session.QueryOver<Reprint>()
|
|
.Where(x => x.Date >= startDate && x.Date <= finishDate)
|
|
.List();
|
|
foreach (var item in listReprint)
|
|
{
|
|
outList.Add(new BillDetail()
|
|
{
|
|
Date = item.Date,
|
|
BillID = item.Voucher.FullBillID,
|
|
Settlement = string.Format("Reprinted by {0}", item.User.Name),
|
|
Amount = item.Voucher.Settlements.Single(x => x.Settled == SettleOption.Amount).Amount * -1
|
|
});
|
|
}
|
|
|
|
return outList;
|
|
}
|
|
}
|
|
}
|
|
}
|