1237 lines
53 KiB
C#
1237 lines
53 KiB
C#
using NHibernate;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.ComponentModel;
|
|
using System.Globalization;
|
|
using System.Linq;
|
|
using Tanshu.Accounts.Entities;
|
|
|
|
namespace Tanshu.Accounts.Repository
|
|
{
|
|
public class SaleInfo
|
|
{
|
|
public int StartBill { get; set; }
|
|
public int FinishBill { get; set; }
|
|
public decimal Rate { get; set; }
|
|
public decimal Net { get; set; }
|
|
public decimal Vat { get; set; }
|
|
}
|
|
public class ExcelInfo
|
|
{
|
|
public DateTime Date { get; set; }
|
|
public string StartBill { get; set; }
|
|
public string FinishBill { get; set; }
|
|
public IDictionary<decimal, SaleInfo> SaleAndVat { get; set; }
|
|
public decimal ServiceTax { get; set; }
|
|
}
|
|
public class SaleJson
|
|
{
|
|
IFormatProvider culture = new CultureInfo("en-US", true);
|
|
private DateTime? dateStart;
|
|
private DateTime? dateFinish;
|
|
public string _startDate { get; set; }
|
|
public string _finishDate { get; set; }
|
|
public IList<SaleDetailJson> Sale { get; set; }
|
|
public DateTime StartDate
|
|
{
|
|
get
|
|
{
|
|
if (!dateStart.HasValue)
|
|
{
|
|
DateTime tDate;
|
|
if (!DateTime.TryParseExact(_startDate, "dd-MMM-yyyy", culture, DateTimeStyles.NoCurrentDateDefault, out tDate))
|
|
throw new ArgumentException();
|
|
dateStart = tDate;
|
|
}
|
|
return dateStart.Value;
|
|
}
|
|
}
|
|
public DateTime FinishDate
|
|
{
|
|
get
|
|
{
|
|
if (!dateFinish.HasValue)
|
|
{
|
|
DateTime tDate;
|
|
if (!DateTime.TryParseExact(_finishDate, "dd-MMM-yyyy", culture, DateTimeStyles.NoCurrentDateDefault, out tDate))
|
|
throw new ArgumentException();
|
|
dateFinish = tDate;
|
|
}
|
|
return dateFinish.Value;
|
|
}
|
|
}
|
|
}
|
|
public class SaleDetailJson
|
|
{
|
|
public decimal Rate { get; set; }
|
|
public bool IsLiq { get; set; }
|
|
public decimal Amount { get; set; }
|
|
}
|
|
public class BeerJson
|
|
{
|
|
IFormatProvider culture = new CultureInfo("en-US", true);
|
|
private DateTime? date;
|
|
public string _date { get; set; }
|
|
public IList<BeerDetailJson> Beers { get; set; }
|
|
public DateTime Date
|
|
{
|
|
get
|
|
{
|
|
if (!date.HasValue)
|
|
{
|
|
DateTime tDate;
|
|
if (!DateTime.TryParseExact(_date, "dd-MMM-yyyy", culture, DateTimeStyles.NoCurrentDateDefault, out tDate))
|
|
throw new ArgumentException();
|
|
date = tDate;
|
|
}
|
|
return date.Value;
|
|
}
|
|
}
|
|
public decimal Quantity
|
|
{
|
|
get
|
|
{
|
|
decimal amount = 0;
|
|
foreach (var item in Beers)
|
|
{
|
|
amount += item.Quantity;
|
|
}
|
|
return amount;
|
|
}
|
|
}
|
|
}
|
|
public class BeerDetailJson
|
|
{
|
|
public string Name { get; set; }
|
|
public decimal Quantity { get; set; }
|
|
}
|
|
public class CreditJson
|
|
{
|
|
IFormatProvider culture = new CultureInfo("en-US", true);
|
|
private DateTime? date;
|
|
public string _date { get; set; }
|
|
public decimal Amount { get; set; }
|
|
public DateTime Date
|
|
{
|
|
get
|
|
{
|
|
if (!date.HasValue)
|
|
{
|
|
DateTime tDate;
|
|
if (!DateTime.TryParseExact(_date, "dd-MMM-yyyy", culture, DateTimeStyles.NoCurrentDateDefault, out tDate))
|
|
throw new ArgumentException();
|
|
date = tDate;
|
|
}
|
|
return date.Value;
|
|
}
|
|
}
|
|
}
|
|
public class ManagementBI : IUnitOfWork
|
|
{
|
|
protected readonly IStatelessSession _session;
|
|
private ITransaction _transaction;
|
|
|
|
public ManagementBI()
|
|
{
|
|
_session = SessionManager.StatelessSession;
|
|
_transaction = _session.BeginTransaction();
|
|
}
|
|
#region Cleanup
|
|
public void DeleteVoid(DateTime startDate, DateTime finishDate)
|
|
{
|
|
var query = @"delete from VoucherSettlement vs where vs.Voucher in (
|
|
select v from Voucher v where v.Date >= :startDate and v.Date <= :finishDate and v.Void = :void
|
|
)";
|
|
_session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.SetParameter("void", true)
|
|
.ExecuteUpdate();
|
|
query = @"delete from Inventory i where i.Kot in (
|
|
select k from Kot k where k.Voucher in (
|
|
select v from Voucher v where v.Date >= :startDate and v.Date <= :finishDate and v.Void = :void
|
|
))";
|
|
_session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.SetParameter("void", true)
|
|
.ExecuteUpdate();
|
|
query = @"delete from Kot k where k.Voucher in (
|
|
select v from Voucher v where v.Date >= :startDate and v.Date <= :finishDate and v.Void = :void
|
|
)";
|
|
_session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.SetParameter("void", true)
|
|
.ExecuteUpdate();
|
|
query = @"delete from Voucher v where v.Date >= :startDate and v.Date <= :finishDate and v.Void = :void";
|
|
_session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.SetParameter("void", true)
|
|
.ExecuteUpdate();
|
|
}
|
|
public void DeleteTobacco(DateTime startDate, DateTime finishDate)
|
|
{
|
|
const string query = @"
|
|
delete from Inventory i where i.Product in (
|
|
select p from Product p where p.ProductGroup in (
|
|
select pg from ProductGroup pg where pg.GroupType = :tobacco
|
|
)
|
|
) and i.Kot in (
|
|
select k from Kot k where k.Voucher in (
|
|
select v from Voucher v where v.Date >= :startDate and v.Date <= :finishDate
|
|
)
|
|
)";
|
|
_session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.SetParameter("tobacco", "Cash Charges")
|
|
.ExecuteUpdate();
|
|
}
|
|
public void DeleteReprints(DateTime startDate, DateTime finishDate)
|
|
{
|
|
const string query = @"delete from Reprint r where r.Voucher in (
|
|
select v from Voucher v where v.Date >= :startDate and v.Date <= :finishDate
|
|
)";
|
|
_session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.ExecuteUpdate();
|
|
}
|
|
public void MoveStaffToNc(DateTime startDate, DateTime finishDate)
|
|
{
|
|
var query = @"update Voucher set VoucherType = :nc where VoucherType = :staff and Date >= :startDate and Date <= :finishDate";
|
|
_session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.SetParameter("nc", VoucherType.NoCharge)
|
|
.SetParameter("staff", VoucherType.Staff)
|
|
.ExecuteUpdate();
|
|
}
|
|
public void ClearModifiers(DateTime startDate, DateTime finishDate)
|
|
{
|
|
var query = @"delete from InventoryModifier im where im.Inventory in (
|
|
select i from Inventory i where i.Kot in (
|
|
select k from Kot k where k.Voucher in (
|
|
select v from Voucher v where v.Date >= :startDate and v.Date <= :finishDate
|
|
)))";
|
|
_session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.ExecuteUpdate();
|
|
}
|
|
public void CombineKots(DateTime startDate, DateTime finishDate)
|
|
{
|
|
var query = @"
|
|
select v.VoucherID, k.KotID, i.InventoryID, i.Product.ProductID, i.Quantity, i.IsHappyHour
|
|
from Voucher v
|
|
inner join v.Kots k
|
|
inner join k.Inventories i
|
|
where v.Date >= :startDate and v.Date <= :finishDate
|
|
order by v.Date, k.Date";
|
|
var list = _session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.List<object[]>();
|
|
Dictionary<Guid, Dictionary<Guid, List<object[]>>> vouchers = new Dictionary<Guid, Dictionary<Guid, List<object[]>>>();
|
|
foreach (var item in list)
|
|
{
|
|
var voucherID = (Guid)item[0];
|
|
var kotID = (Guid)item[1];
|
|
var inventoryID = (Guid)item[2];
|
|
var productID = (Guid)item[3];
|
|
var quantity = (decimal)item[4];
|
|
var isHappyHour = (bool)item[5];
|
|
if (!vouchers.ContainsKey(voucherID))
|
|
vouchers.Add(voucherID, new Dictionary<Guid, List<object[]>>());
|
|
if (!vouchers[voucherID].ContainsKey(kotID))
|
|
vouchers[voucherID].Add(kotID, new List<object[]>());
|
|
vouchers[voucherID][kotID].Add(new object[] { inventoryID, productID, quantity, isHappyHour });
|
|
}
|
|
foreach (var voucher in vouchers)
|
|
{
|
|
var kots = voucher.Value;
|
|
if (kots.Count <= 1)
|
|
continue;
|
|
Dictionary<Int32, Guid> kotInventories = new Dictionary<Int32, Guid>();
|
|
foreach (var item in kots.ElementAt(0).Value)
|
|
{
|
|
Guid inventoryID = (Guid)item[0];
|
|
Guid productID = (Guid)item[1];
|
|
bool isHappyHour = (bool)item[3];
|
|
kotInventories.Add(productID.GetHashCode() ^ isHappyHour.GetHashCode(), inventoryID);
|
|
}
|
|
for (var i = kots.Count; i > 1; i--)
|
|
{
|
|
foreach (var item in kots.ElementAt(i - 1).Value)
|
|
{
|
|
Guid inventoryID = (Guid)item[0];
|
|
Guid productID = (Guid)item[1];
|
|
decimal quantity = (decimal)item[2];
|
|
bool isHappyHour = (bool)item[3];
|
|
var key = productID.GetHashCode() ^ isHappyHour.GetHashCode();
|
|
if (kotInventories.ContainsKey(key))
|
|
{
|
|
query = @"update Inventory set Quantity = Quantity + :quantity where InventoryID = :inventoryID";
|
|
_session.CreateQuery(query).SetParameter("quantity", quantity).SetParameter("inventoryID", kotInventories[key]).ExecuteUpdate();
|
|
query = @"delete from Inventory where InventoryID = :inventoryID";
|
|
_session.CreateQuery(query).SetParameter("inventoryID", inventoryID).ExecuteUpdate();
|
|
}
|
|
else
|
|
{
|
|
kotInventories.Add(key, inventoryID);
|
|
query = @"update Inventory set Kot.KotID = :kotID where InventoryID = :inventoryID";
|
|
_session.CreateQuery(query).SetParameter("kotID", kots.ElementAt(0).Key).SetParameter("inventoryID", inventoryID).ExecuteUpdate();
|
|
}
|
|
}
|
|
query = @"delete from Kot where KotID = :kotID";
|
|
_session.CreateQuery(query).SetParameter("kotID", kots.ElementAt(i - 1).Key).ExecuteUpdate();
|
|
}
|
|
}
|
|
}
|
|
public void RemoveBlankKots(DateTime startDate, DateTime finishDate)
|
|
{
|
|
var query = @"delete from Kot k where
|
|
k.Voucher in (select v from Voucher v where v.Date >= :startDate and v.Date <= :finishDate)
|
|
and k not in (select distinct i.Kot from Inventory i)";
|
|
_session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.ExecuteUpdate();
|
|
query = @"delete from VoucherSettlement vs where vs.Voucher in (select v from Voucher v where v.Date >= :startDate and v.Date <= :finishDate and v not in (select distinct k.Voucher from Kot k))";
|
|
_session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.ExecuteUpdate();
|
|
query = @"delete from Voucher v where v.Date >= :startDate and v.Date <= :finishDate and v not in (select distinct k.Voucher from Kot k)";
|
|
_session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.ExecuteUpdate();
|
|
}
|
|
|
|
public void SetPayments(DateTime startDate, DateTime finishDate)
|
|
{
|
|
var query = @"select v.VoucherID, v.VoucherType, sum(i.Amount)
|
|
from Voucher v
|
|
inner join v.Kots k
|
|
inner join k.Inventories i
|
|
where v.Date >= :startDate and v.Date <= :finishDate
|
|
group by v.VoucherID, v.VoucherType";
|
|
var list = _session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.List<object[]>();
|
|
Dictionary<Guid, object[]> vouchers = new Dictionary<Guid, object[]>();
|
|
foreach (var item in list)
|
|
{
|
|
var voucherType = (int)item[1] == 1 || (int)item[1] == 3 ? VoucherType.Regular : (int)item[1] == 2 ? VoucherType.NoCharge : VoucherType.Staff;
|
|
vouchers.Add((Guid)item[0], new object[] { voucherType, Math.Round((decimal)item[2], 5) });
|
|
}
|
|
|
|
foreach (var item in vouchers)
|
|
{
|
|
SettleOption settlementType;
|
|
switch ((VoucherType)item.Value[0])
|
|
{
|
|
case VoucherType.NoCharge:
|
|
settlementType = SettleOption.NoCharge;
|
|
break;
|
|
case VoucherType.Staff:
|
|
settlementType = SettleOption.Staff;
|
|
break;
|
|
default:
|
|
settlementType = SettleOption.Cash;
|
|
break;
|
|
}
|
|
var amount = -1 * (decimal)item.Value[1];
|
|
var roundoff = Math.Round(amount) - amount;
|
|
query = @"select count(*) from VoucherSettlement vs
|
|
where vs.Voucher.VoucherID = :voucherID and (
|
|
(vs.Amount = :amount and vs.Settled = :soAmount) or
|
|
(vs.Amount = :roundOff and vs.Settled = :soRoundOff) or
|
|
(vs.Amount = :paid and vs.Settled = :so))";
|
|
var existing = _session.CreateQuery(query)
|
|
.SetParameter("voucherID", item.Key)
|
|
.SetParameter("amount", amount)
|
|
.SetParameter("roundOff", roundoff)
|
|
.SetParameter("paid", -1 * (amount + roundoff))
|
|
.SetParameter("soAmount", SettleOption.Amount)
|
|
.SetParameter("soRoundOff", SettleOption.RoundOff)
|
|
.SetParameter("so", settlementType)
|
|
.UniqueResult<long>();
|
|
if (existing == 3)
|
|
continue;
|
|
query = @"delete from VoucherSettlement vs where vs.Voucher.VoucherID = :voucherID";
|
|
_session
|
|
.CreateQuery(query)
|
|
.SetParameter("voucherID", item.Key)
|
|
.ExecuteUpdate();
|
|
_session.Insert(new VoucherSettlement() { Voucher = new Voucher() { VoucherID = item.Key }, Amount = amount, Settled = SettleOption.Amount });
|
|
_session.Insert(new VoucherSettlement() { Voucher = new Voucher() { VoucherID = item.Key }, Amount = roundoff, Settled = SettleOption.RoundOff });
|
|
_session.Insert(new VoucherSettlement() { Voucher = new Voucher() { VoucherID = item.Key }, Amount = -1 * (amount + roundoff), Settled = settlementType });
|
|
}
|
|
}
|
|
public void UpdateBillID(DateTime startDate, DateTime finishDate, BackgroundWorker bw)
|
|
{
|
|
var query = @"
|
|
select MAX(v.BillID)
|
|
from Voucher v
|
|
where v.Date < :startDate and v.Void = false and v.VoucherType in (:regular, :takeAway)
|
|
";
|
|
var lastBill = _session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("regular", VoucherType.Regular)
|
|
.SetParameter("takeAway", VoucherType.TakeAway)
|
|
.UniqueResult();
|
|
var newID = lastBill == null ? 1 : GetNewID((int)lastBill);
|
|
var list = _session.QueryOver<Voucher>()
|
|
.Where(x => x.Date >= startDate && x.Date <= finishDate && x.Void == false && (x.VoucherType == VoucherType.Regular || x.VoucherType == VoucherType.TakeAway))
|
|
.OrderBy(x => x.Date).Asc
|
|
.List();
|
|
var count = " of " + list.Count.ToString();
|
|
var i = 0;
|
|
foreach (var voucher in list)
|
|
{
|
|
i++;
|
|
if (i % 20 == 0)
|
|
bw.ReportProgress(0, "Loop " + i.ToString() + count);
|
|
if (voucher.BillID != newID)
|
|
{
|
|
var update = _session.CreateSQLQuery("exec UpdateBillID ?,?");
|
|
update.SetParameter<Guid>(0, voucher.VoucherID);
|
|
update.SetParameter<int>(1, newID);
|
|
update.ExecuteUpdate();
|
|
}
|
|
newID = GetNewID(newID);
|
|
}
|
|
}
|
|
|
|
public void UpdateOtherBillID(DateTime startDate, DateTime finishDate, VoucherType vt, BackgroundWorker bw)
|
|
{
|
|
var query = @"
|
|
select MAX(v.BillID)
|
|
from Voucher v
|
|
where v.Date < :startDate and v.Void = false and v.VoucherType = :vt";
|
|
var lastBill = _session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("vt", vt)
|
|
.UniqueResult();
|
|
var newID = lastBill == null ? 1 : (int)lastBill + 1;
|
|
var list = _session.QueryOver<Voucher>()
|
|
.Where(x => x.Date >= startDate && x.Date <= finishDate && x.Void == false && x.VoucherType == vt)
|
|
.OrderBy(x => x.Date).Asc
|
|
.List();
|
|
var count = " of " + list.Count.ToString();
|
|
var i = 0;
|
|
foreach (var voucher in list)
|
|
{
|
|
i++;
|
|
if (i % 20 == 0)
|
|
bw.ReportProgress(0, "Loop " + i.ToString() + count);
|
|
if (voucher.BillID != newID)
|
|
{
|
|
var update = _session.CreateSQLQuery("exec UpdateBillID ?,?");
|
|
update.SetParameter<Guid>(0, voucher.VoucherID);
|
|
update.SetParameter<int>(1, newID);
|
|
update.ExecuteUpdate();
|
|
}
|
|
newID += 1;
|
|
}
|
|
}
|
|
private static int GetNewID(int lastBill)
|
|
{
|
|
lastBill += 1;
|
|
if (lastBill % 10000 == 0)
|
|
lastBill += 1;
|
|
return lastBill;
|
|
}
|
|
|
|
#endregion
|
|
public void LiqNcSwap(decimal rate, decimal target, DateTime startDate, DateTime finishDate)
|
|
{
|
|
var existingSale = GetSaleAmount(rate, startDate, finishDate);
|
|
if (existingSale > target / .75M)
|
|
{
|
|
// Do not put all in NC this will allow for about 25% discount on the rest of non nc liqour
|
|
existingSale = MoveToNc(rate, target / .75M, startDate, finishDate);
|
|
}
|
|
else if (existingSale < target)
|
|
{
|
|
existingSale = MoveFromNc(rate, target, startDate, finishDate);
|
|
}
|
|
}
|
|
public decimal MoveToNc(decimal rate, decimal target, DateTime startDate, DateTime finishDate)
|
|
{
|
|
string query = @"
|
|
select v.VoucherID, sum(i.Quantity * i.Price * (1 - i.Discount) * (1 + case when i.IsScTaxable then i.ServiceCharge else 0 end))
|
|
from Voucher v
|
|
inner join v.Kots k
|
|
inner join k.Inventories i
|
|
where v.Date >= :startDate and v.Date <= :finishDate and i.Vat.TaxID = :vatLiquor and i.VatRate = :vatRate and v.Void = false and v.VoucherType not in (:nc, :staff)
|
|
group by v.VoucherID
|
|
order by sum(i.Quantity * i.Price * (1 - i.Discount) * (1 + case when i.IsScTaxable then i.ServiceCharge else 0 end)) desc
|
|
";
|
|
var list = _session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.SetParameter("nc", VoucherType.NoCharge)
|
|
.SetParameter("staff", VoucherType.Staff)
|
|
.SetParameter("vatLiquor", new Guid("2C8AD8EC-E09A-4194-B348-01243474CF26"))
|
|
.SetParameter("vatRate", rate)
|
|
.List();
|
|
var totalAmount = GetSaleAmount(rate, startDate, finishDate);
|
|
for (int i = 0; i < list.Count / 20; i += 2) // Skip every alternate bill
|
|
{
|
|
if (totalAmount <= target)
|
|
break;
|
|
var item = (object[])list[i];
|
|
var voucherID = (Guid)item[0];
|
|
var amount = (decimal)item[1];
|
|
|
|
query = @"update Voucher set VoucherType = :nc where VoucherID = :voucherID";
|
|
_session
|
|
.CreateQuery(query)
|
|
.SetParameter("nc", VoucherType.NoCharge)
|
|
.SetParameter("voucherID", voucherID)
|
|
.ExecuteUpdate();
|
|
totalAmount -= amount;
|
|
}
|
|
return totalAmount;
|
|
}
|
|
public decimal MoveFromNc(decimal rate, decimal target, DateTime startDate, DateTime finishDate)
|
|
{
|
|
string query = @"
|
|
select v.VoucherID, sum(i.Quantity * i.Price * (1 - i.Discount) * (1 + case when i.IsScTaxable then i.ServiceCharge else 0 end))
|
|
from Voucher v
|
|
inner join v.Kots k
|
|
inner join k.Inventories i
|
|
where v.Date >= :startDate and v.Date <= :finishDate and i.Vat.TaxID = :vatLiquor and i.VatRate = :vatRate and v.Void = false and v.VoucherType = :nc
|
|
group by v.VoucherID
|
|
order by sum(i.Quantity * i.Price * (1 - i.Discount) * (1 + case when i.IsScTaxable then i.ServiceCharge else 0 end)) desc
|
|
";
|
|
var list = _session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.SetParameter("nc", VoucherType.NoCharge)
|
|
.SetParameter("vatLiquor", new Guid("2C8AD8EC-E09A-4194-B348-01243474CF26"))
|
|
.SetParameter("vatRate", rate)
|
|
.List();
|
|
var existingSale = GetSaleAmount(rate, startDate, finishDate);
|
|
for (int i = 0; i < list.Count; i += 1)
|
|
{
|
|
if (existingSale >= target)
|
|
break;
|
|
var item = (object[])list[i];
|
|
var voucherID = (Guid)item[0];
|
|
var amount = (decimal)item[1];
|
|
|
|
query = @"update Voucher set VoucherType = :regular where VoucherID = :voucherID";
|
|
_session
|
|
.CreateQuery(query)
|
|
.SetParameter("regular", VoucherType.Regular)
|
|
.SetParameter("voucherID", voucherID)
|
|
.ExecuteUpdate();
|
|
existingSale += amount;
|
|
}
|
|
return existingSale;
|
|
}
|
|
public IList<SaleInfo> GetSaleAndVat(DateTime startDate, DateTime finishDate)
|
|
{
|
|
const string query = @"
|
|
select i.VatRate as Rate,
|
|
sum(i.Quantity * i.Price * (1 - i.Discount) * (1 + case when i.IsScTaxable then i.ServiceCharge else 0 end)) as Net,
|
|
sum(i.Quantity * i.Price * (1 - i.Discount) * (1 + case when i.IsScTaxable then i.ServiceCharge else 0 end) * i.VatRate) as Vat
|
|
from Voucher v
|
|
inner join v.Kots k
|
|
inner join k.Inventories i
|
|
where v.Date >= :startDate and v.Date <= :finishDate and v.Void = false and v.VoucherType in (:regular, :takeAway)
|
|
group by i.VatRate
|
|
order by i.VatRate
|
|
";
|
|
var list = _session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.SetParameter("regular", VoucherType.Regular)
|
|
.SetParameter("takeAway", VoucherType.TakeAway)
|
|
.List<object[]>();
|
|
var info = new List<SaleInfo>();
|
|
foreach (var item in list)
|
|
{
|
|
info.Add(new SaleInfo() { Rate = (decimal)item[0], Net = (decimal)item[1], Vat = (decimal)item[2] });
|
|
}
|
|
return info;
|
|
}
|
|
public decimal GetServiceTax(DateTime startDate, DateTime finishDate)
|
|
{
|
|
const string query = @"
|
|
select sum(i.Quantity * i.Price * (1 - i.Discount) * (1 + case when i.IsScTaxable then i.ServiceCharge else 0 end) * i.ServiceTaxRate) as Amount
|
|
from Voucher v
|
|
inner join v.Kots k
|
|
inner join k.Inventories i
|
|
where v.Date >= :startDate and v.Date <= :finishDate and v.Void = false and v.VoucherType not in (:nc, :staff)
|
|
";
|
|
var qty = _session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.SetParameter("nc", VoucherType.NoCharge)
|
|
.SetParameter("staff", VoucherType.Staff)
|
|
.UniqueResult();
|
|
return qty == null ? 0 : (decimal)qty;
|
|
}
|
|
public SaleInfo GetMinMaxBills(DateTime startDate, DateTime finishDate)
|
|
{
|
|
const string query = @"
|
|
select MIN(v.BillID) as StartBill, MAX(v.BillID) as FinishBill
|
|
from Voucher v
|
|
where v.Date >= :startDate and v.Date <= :finishDate and v.Void = false and v.VoucherType in (:regular, :takeAway)
|
|
";
|
|
var item = _session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.SetParameter("regular", VoucherType.Regular)
|
|
.SetParameter("takeAway", VoucherType.TakeAway)
|
|
.UniqueResult<object[]>();
|
|
if (item[0] == null || item[1] == null)
|
|
return null;
|
|
return new SaleInfo() { StartBill = (int)item[0], FinishBill = (int)item[1] };
|
|
}
|
|
|
|
public int? GetLastBill(DateTime date)
|
|
{
|
|
const string query = @"
|
|
select v.BillID
|
|
from Voucher v
|
|
where v.Date >= :startDate and v.Date <= :finishDate and v.Void = false and v.VoucherType in (:regular, :takeAway)
|
|
order by v.Date desc
|
|
";
|
|
var qty = _session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", date.AddHours(7))
|
|
.SetParameter("finishDate", date.AddDays(1).AddHours(7))
|
|
.SetParameter("regular", VoucherType.Regular)
|
|
.SetParameter("takeAway", VoucherType.TakeAway)
|
|
.SetMaxResults(1)
|
|
.UniqueResult();
|
|
if (qty == null)
|
|
return null;
|
|
return (int)qty;
|
|
}
|
|
|
|
private decimal GetSaleAmount(decimal vat, DateTime startDate, DateTime finishDate)
|
|
{
|
|
const string query = @"
|
|
select sum(i.Quantity * i.Price * (1 - i.Discount) * (1 + case when i.IsScTaxable then i.ServiceCharge else 0 end)) as Amount
|
|
from Voucher v
|
|
inner join v.Kots k
|
|
inner join k.Inventories i
|
|
where v.Date >= :startDate and v.Date <= :finishDate and i.VatRate = :vat and v.Void = false and v.VoucherType not in (:nc, :staff)
|
|
";
|
|
var qty = _session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.SetParameter("vat", vat)
|
|
.SetParameter("nc", VoucherType.NoCharge)
|
|
.SetParameter("staff", VoucherType.Staff)
|
|
.UniqueResult();
|
|
return qty == null ? 0 : (decimal)qty;
|
|
}
|
|
public class DateVatSale
|
|
{
|
|
public DateTime Date { get; set; }
|
|
public decimal VatRate { get; set; }
|
|
public decimal Net { get; set; }
|
|
public decimal Gross { get; set; }
|
|
}
|
|
public List<DateVatSale> GetSaleAmount(DateTime startDate, DateTime finishDate)
|
|
{
|
|
var list = new List<DateVatSale>();
|
|
const string query = @"
|
|
select i.VatRate, sum(i.Quantity * i.EffectivePrice * (1 - i.Discount) * (1 + case when i.IsScTaxable then i.ServiceCharge else 0 end)), sum(i.Amount)
|
|
from Voucher v
|
|
inner join v.Kots k
|
|
inner join k.Inventories i
|
|
where v.Date >= :startDate and v.Date <= :finishDate and v.Void = false and v.VoucherType not in (:nc, :staff)
|
|
group by i.VatRate
|
|
";
|
|
IList<object[]> q;
|
|
for (var date = startDate.Date; date <= finishDate.Date; date = date.AddDays(1))
|
|
{
|
|
q = _session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", date.AddHours(7))
|
|
.SetParameter("finishDate", date.AddDays(1).AddHours(7))
|
|
.SetParameter("nc", VoucherType.NoCharge)
|
|
.SetParameter("staff", VoucherType.Staff)
|
|
.List<object[]>();
|
|
foreach (var item in q)
|
|
{
|
|
list.Add(new DateVatSale() { Date = date, VatRate = (decimal)item[0], Net = (decimal)item[1], Gross = (decimal)item[2] });
|
|
}
|
|
}
|
|
return list;
|
|
}
|
|
private class InvDate
|
|
{
|
|
public DateTime Date { get; set; }
|
|
public Inventory Inv { get; set; }
|
|
}
|
|
public void SetQuantityAndDiscount(IList<SaleDetailJson> sale, IList<CreditJson> credit, DateTime startDate, DateTime finishDate)
|
|
{
|
|
var rand = new Random();
|
|
const string query = @"
|
|
select v.Date, i
|
|
from Voucher v
|
|
inner join v.Kots k
|
|
inner join k.Inventories i
|
|
where v.Date >= :startDate and v.Date <= :finishDate
|
|
and v.VoucherType = :regular";
|
|
var list = _session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate.AddHours(7))
|
|
.SetParameter("finishDate", finishDate.AddDays(1).AddHours(7))
|
|
.SetParameter("regular", VoucherType.Regular)
|
|
.List<object[]>();
|
|
List<InvDate> inventories = new List<InvDate>();
|
|
foreach (var item in Randomize(list))
|
|
{
|
|
inventories.Add(new InvDate()
|
|
{
|
|
Date = ((DateTime)item[0]).AddHours(-7).Date,
|
|
Inv = (Inventory)item[1]
|
|
});
|
|
}
|
|
var dailySaleGroupedByVat = GetSaleAmount(startDate, finishDate);
|
|
foreach (var s in sale)
|
|
{
|
|
// Now sale will contain the amounts to be adjusted
|
|
// +ve is the amount to reduce by, -ve is amount to increase sale by
|
|
var currentNetSale = dailySaleGroupedByVat.Where(x => x.VatRate == s.Rate).Sum(x => x.Net);
|
|
s.Amount = currentNetSale - s.Amount;
|
|
}
|
|
foreach (var c in credit)
|
|
{
|
|
// Now credit will contain the margin available for the day
|
|
c.Amount = dailySaleGroupedByVat.Where(x => x.Date == c.Date).Sum(x => x.Gross) - c.Amount;
|
|
}
|
|
foreach (var inv in inventories)
|
|
{
|
|
var s = sale.SingleOrDefault(x => x.Rate == inv.Inv.VatRate);
|
|
var c = credit.SingleOrDefault(x => x.Date == inv.Date);
|
|
if (s == null) // Temp ignore and move on
|
|
continue;
|
|
//throw new ArgumentException("Unknown type of vat rate encountered");
|
|
if (c == null) // Means no credit card for the day
|
|
c = new CreditJson() { _date = inv.Date.ToString("dd-MMM-yyyy"), Amount = s.Amount };
|
|
// throw new ArgumentException("Unknown date encountered");
|
|
if (Math.Abs(s.Amount) < 1)
|
|
continue; // Close enough for now
|
|
if (s.Amount > 0 && c.Amount <= 0)
|
|
continue; //Move on if we have to reduce and we do not have credit sale margin
|
|
if (!s.IsLiq) //Food
|
|
{
|
|
if (inv.Inv.IsHappyHour)
|
|
throw new ArgumentException("Non-liqour does not have happy hour");
|
|
if (s.Amount < 0)
|
|
{
|
|
IncreaseFood(s, inv, c);
|
|
}
|
|
else
|
|
{
|
|
DecreaseFood(s, inv, c);
|
|
}
|
|
}
|
|
else
|
|
{
|
|
if (s.Amount < 0)
|
|
{
|
|
//Increase Sales
|
|
if (inv.Inv.IsHappyHour)
|
|
IncreaseLiqourUsingHappyHour(s, inv, c);
|
|
else if (inv.Inv.Discount > 0)
|
|
IncreaseLiqourUsingDiscount(s, inv, c);
|
|
|
|
}
|
|
else
|
|
{
|
|
if (inv.Inv.Quantity != 0 && inv.Inv.EffectivePrice != 0)
|
|
DecreaseLiqour(rand, s, inv, c);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
public void IncreaseLiqIfLess(IList<SaleDetailJson> sale, DateTime startDate, DateTime finishDate)
|
|
{
|
|
var rand = new Random();
|
|
const string query = @"
|
|
select v.Date, i
|
|
from Voucher v
|
|
inner join v.Kots k
|
|
inner join k.Inventories i
|
|
where v.Date >= :startDate and v.Date <= :finishDate
|
|
and v.VoucherType = :regular";
|
|
var list = _session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate.AddHours(7))
|
|
.SetParameter("finishDate", finishDate.AddDays(1).AddHours(7))
|
|
.SetParameter("regular", VoucherType.Regular)
|
|
.List<object[]>();
|
|
List<InvDate> inventories = new List<InvDate>();
|
|
foreach (var item in Randomize(list))
|
|
{
|
|
inventories.Add(new InvDate()
|
|
{
|
|
Date = ((DateTime)item[0]).AddHours(-7).Date,
|
|
Inv = (Inventory)item[1]
|
|
});
|
|
}
|
|
foreach (var inv in inventories)
|
|
{
|
|
var s = sale.SingleOrDefault(x => x.Rate == inv.Inv.VatRate);
|
|
if (s == null) // Temp ignore and move on
|
|
continue;
|
|
//throw new ArgumentException("Unknown type of vat rate encountered");
|
|
if (Math.Abs(s.Amount) < 2)
|
|
continue; // Close enough for now
|
|
if (s.Amount >= 0)
|
|
continue; //Move on if we have to reduce and we do not have credit sale margin
|
|
if (s.IsLiq)
|
|
continue; //Move on if we have to reduce and we do not have credit sale margin
|
|
//Increase Sales
|
|
IncreaseLiqourUsingPrice(s, inv);
|
|
|
|
}
|
|
}
|
|
|
|
private void IncreaseFood(SaleDetailJson s, InvDate inv, CreditJson c)
|
|
{
|
|
//Increase Sales
|
|
if (inv.Inv.Discount != 0)
|
|
{
|
|
var invMax = inv.Inv.Quantity * inv.Inv.EffectivePrice;
|
|
decimal discount;
|
|
if (invMax * inv.Inv.Discount < -1 * s.Amount)
|
|
discount = 0;
|
|
else
|
|
discount = (s.Amount * -1) / invMax;
|
|
|
|
const string query = @"update Inventory set Discount = :discount where InventoryID = :inventoryID";
|
|
_session.CreateQuery(query).SetParameter("discount", discount).SetParameter("inventoryID", inv.Inv.InventoryID).ExecuteUpdate();
|
|
s.Amount += invMax * (inv.Inv.Discount - discount);
|
|
c.Amount += inv.Inv.Amount * (inv.Inv.Discount - discount);
|
|
}
|
|
|
|
}
|
|
private void DecreaseFood(SaleDetailJson s, InvDate inv, CreditJson c)
|
|
{
|
|
if (inv.Inv.Net > s.Amount || inv.Inv.Amount > c.Amount)
|
|
{
|
|
var netRatio = (inv.Inv.Net - s.Amount) / inv.Inv.Net;
|
|
var grossRatio = (inv.Inv.Amount - c.Amount) / inv.Inv.Amount;
|
|
decimal ratio;
|
|
if (netRatio < 0)
|
|
ratio = grossRatio;
|
|
else if (grossRatio < 0)
|
|
ratio = netRatio;
|
|
else
|
|
ratio = Math.Min(netRatio, grossRatio);
|
|
|
|
|
|
const string query = @"update Inventory set Quantity = Quantity * :ratio where InventoryID = :inventoryID";
|
|
_session.CreateQuery(query).SetParameter("ratio", ratio).SetParameter("inventoryID", inv.Inv.InventoryID).ExecuteUpdate();
|
|
// reduce from daily
|
|
s.Amount -= inv.Inv.Net * (1 - ratio);
|
|
c.Amount -= inv.Inv.Amount * (1 - ratio);
|
|
}
|
|
else
|
|
{
|
|
const string query = @"delete from Inventory where InventoryID = :inventoryID";
|
|
_session.CreateQuery(query).SetParameter("inventoryID", inv.Inv.InventoryID).ExecuteUpdate();
|
|
// reduce from daily
|
|
s.Amount -= inv.Inv.Net;
|
|
c.Amount -= inv.Inv.Amount;
|
|
}
|
|
}
|
|
private void IncreaseLiqourUsingHappyHour(SaleDetailJson s, InvDate inv, CreditJson c)
|
|
{
|
|
var query = @"select count(*) from Inventory i where i.Product.ProductID = :productID and i.IsHappyHour = :false and i.Kot.KotID = :kotID";
|
|
var old = _session.CreateQuery(query)
|
|
.SetParameter("productID", inv.Inv.Product.ProductID)
|
|
.SetParameter("false", false)
|
|
.SetParameter("kotID", inv.Inv.Kot.KotID)
|
|
.UniqueResult<long>();
|
|
|
|
if (old > 0)
|
|
{
|
|
query = @"update Inventory i set Quantity = Quantity + :quantity where i.Product.ProductID = :productID and i.IsHappyHour = :false and i.Kot.KotID = :kotID";
|
|
_session.CreateQuery(query)
|
|
.SetParameter("quantity", inv.Inv.Quantity)
|
|
.SetParameter("productID", inv.Inv.Product.ProductID)
|
|
.SetParameter("false", false)
|
|
.SetParameter("kotID", inv.Inv.Kot.KotID)
|
|
.ExecuteUpdate();
|
|
query = @"delete from Inventory where InventoryID = :inventoryID";
|
|
_session.CreateQuery(query).SetParameter("inventoryID", inv.Inv.InventoryID).ExecuteUpdate();
|
|
}
|
|
else
|
|
{
|
|
query = @"update Inventory set IsHappyHour = :false where InventoryID = :inventoryID";
|
|
_session.CreateQuery(query).SetParameter("false", false).SetParameter("inventoryID", inv.Inv.InventoryID).ExecuteUpdate();
|
|
}
|
|
inv.Inv.IsHappyHour = false;
|
|
s.Amount += inv.Inv.Net;
|
|
c.Amount += inv.Inv.Amount;
|
|
}
|
|
private void IncreaseLiqourUsingDiscount(SaleDetailJson s, InvDate inv, CreditJson c)
|
|
{
|
|
decimal discount;
|
|
if (s.Amount * -1 > inv.Inv.Quantity * inv.Inv.EffectivePrice * inv.Inv.Discount)
|
|
discount = 0;
|
|
else
|
|
discount = inv.Inv.Discount + Math.Round(s.Amount / (inv.Inv.Quantity * inv.Inv.EffectivePrice), 2);
|
|
|
|
const string query = @"update Inventory i set Discount = :discount where i.InventoryID = :inventoryID";
|
|
_session.CreateQuery(query)
|
|
.SetParameter("discount", discount)
|
|
.SetParameter("inventoryID", inv.Inv.InventoryID)
|
|
.ExecuteUpdate();
|
|
inv.Inv.Discount = 1 - (inv.Inv.Discount - discount);
|
|
s.Amount += inv.Inv.Net;
|
|
c.Amount += inv.Inv.Amount;
|
|
}
|
|
private void IncreaseLiqourUsingPrice(SaleDetailJson s, InvDate inv)
|
|
{
|
|
//decimal price;
|
|
//if (s.Amount * -1 > inv.Inv.Quantity * inv.Inv.EffectivePrice)
|
|
// discount = 0;
|
|
//else
|
|
// discount = inv.Inv.Discount + Math.Round(s.Amount / (inv.Inv.Quantity * inv.Inv.EffectivePrice), 2);
|
|
|
|
//const string query = @"update Inventory i set Discount = :discount where i.InventoryID = :inventoryID";
|
|
//_session.CreateQuery(query)
|
|
// .SetParameter("discount", discount)
|
|
// .SetParameter("inventoryID", inv.Inv.InventoryID)
|
|
// .ExecuteUpdate();
|
|
//inv.Inv.Discount = 1 - (inv.Inv.Discount - discount);
|
|
//s.Amount += inv.Inv.Net;
|
|
//c.Amount += inv.Inv.Amount;
|
|
}
|
|
private void DecreaseLiqour(Random r, SaleDetailJson s, InvDate inv, CreditJson c)
|
|
{
|
|
var min = Math.Max(Convert.ToInt32(inv.Inv.Discount * 100) + 1, 10);
|
|
var discount = Convert.ToDecimal(r.Next(min, 90)) / 100;
|
|
var netChange = inv.Inv.Quantity * inv.Inv.EffectivePrice * (discount - inv.Inv.Discount);
|
|
|
|
if (netChange > s.Amount)
|
|
{
|
|
discount = s.Amount / (inv.Inv.Quantity * inv.Inv.EffectivePrice);
|
|
discount = Math.Round(discount, 2);
|
|
}
|
|
netChange = inv.Inv.Quantity * inv.Inv.EffectivePrice * (discount - inv.Inv.Discount);
|
|
|
|
const string query = @"update Inventory set Discount = :discount where InventoryID = :inventoryID";
|
|
_session.CreateQuery(query).SetParameter("discount", discount).SetParameter("inventoryID", inv.Inv.InventoryID).ExecuteUpdate();
|
|
|
|
var grossChange = inv.Inv.Amount * (discount - inv.Inv.Discount);
|
|
s.Amount -= netChange;
|
|
c.Amount -= grossChange;
|
|
}
|
|
|
|
#region Beer
|
|
public decimal GetBeer(DateTime startDate, DateTime finishDate)
|
|
{
|
|
const string query = @"
|
|
select sum(i.Quantity * p.Quantity) as 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 p.Quantity != 0
|
|
";
|
|
var qty = _session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.UniqueResult();
|
|
return qty == null ? 0 : (decimal)qty;
|
|
}
|
|
public decimal SetBeer(DateTime startDate, DateTime finishDate, decimal quantity)
|
|
{
|
|
var query = @"
|
|
select i.InventoryID, i.Product.ProductID, 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 p.Quantity != 0";
|
|
var list = _session
|
|
.CreateQuery(query)
|
|
.SetParameter("startDate", startDate)
|
|
.SetParameter("finishDate", finishDate)
|
|
.List<object[]>();
|
|
list = Randomize(list);
|
|
Dictionary<Guid, object[]> inventories = new Dictionary<Guid, object[]>();
|
|
foreach (var item in list)
|
|
{
|
|
var inventoryID = (Guid)item[0];
|
|
var productID = (Guid)item[1];
|
|
var inventoryQuantity = (decimal)item[2];
|
|
var productQuantity = (decimal)item[3];
|
|
inventories.Add(inventoryID, new object[] { productID, inventoryQuantity, productQuantity });
|
|
}
|
|
var left = GetBeer(startDate, finishDate) - quantity;
|
|
if (left < 0)
|
|
Console.Write("Beer was already negative");
|
|
foreach (var item in inventories)
|
|
{
|
|
if (left <= 0)
|
|
break;
|
|
var productID = (Guid)item.Value[0];
|
|
var inventoryQuantity = (decimal)item.Value[1];
|
|
var productQuantity = (decimal)item.Value[2];
|
|
|
|
var q = inventoryQuantity * productQuantity;
|
|
|
|
if (q > left)
|
|
{
|
|
var newQuantity = inventoryQuantity * (q - left) / q;
|
|
query = @"update Inventory set Quantity = :quantity where InventoryID = :inventoryID";
|
|
_session.CreateQuery(query).SetParameter("quantity", newQuantity).SetParameter("inventoryID", item.Key).ExecuteUpdate();
|
|
left = 0;
|
|
}
|
|
else
|
|
{
|
|
query = @"delete from Inventory where InventoryID = :inventoryID";
|
|
_session.CreateQuery(query).SetParameter("inventoryID", item.Key).ExecuteUpdate();
|
|
left -= q;
|
|
}
|
|
}
|
|
return GetBeer(startDate, finishDate);
|
|
}
|
|
#endregion
|
|
#region Helper and Comments
|
|
private static IList<T> Randomize<T>(IEnumerable<T> list)
|
|
{
|
|
var tList = list.ToArray();
|
|
var rand = new Random();
|
|
for (var i = tList.Length - 1; i > 0; i--)
|
|
{
|
|
var swapIndex = rand.Next(i + 1);
|
|
var tmp = tList[i];
|
|
tList[i] = tList[swapIndex];
|
|
tList[swapIndex] = tmp;
|
|
}
|
|
return tList.ToList();
|
|
}
|
|
public string FullBillID(int billID, VoucherType voucherType)
|
|
{
|
|
switch (voucherType)
|
|
{
|
|
case VoucherType.NoCharge:
|
|
return "NC-" + billID.ToString();
|
|
case VoucherType.Staff:
|
|
return "ST-" + billID.ToString();
|
|
case VoucherType.TakeAway:
|
|
case VoucherType.Regular:
|
|
default:
|
|
return (billID / 10000).ToString() + "-" + (billID % 10000).ToString();
|
|
}
|
|
}
|
|
|
|
// public decimal GetBeer(int baseCode, DateTime startDate, DateTime finishDate)
|
|
// {
|
|
// const string query = @"
|
|
//select sum(i.Quantity * p.Quantity) as 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 p.BaseCode = :baseCode
|
|
// ";
|
|
// var qty = Session
|
|
// .CreateQuery(query)
|
|
// .SetParameter("startDate", startDate)
|
|
// .SetParameter("finishDate", finishDate)
|
|
// .SetParameter("baseCode", baseCode)
|
|
// .UniqueResult();
|
|
// return qty == null ? 0 : (decimal)qty;
|
|
// }
|
|
// public decimal SetQuantity(int baseCode, decimal quantity, DateTime startDate, DateTime finishDate)
|
|
// {
|
|
// var list = Randomize(new VoucherBI().List(x => x.Date >= startDate && x.Date <= finishDate && x.Void == false));
|
|
// var left = GetBeer(baseCode, startDate, finishDate) - quantity;
|
|
// foreach (var item in list)
|
|
// {
|
|
// if (left <= 0)
|
|
// break;
|
|
// foreach (var kot in item.Kots)
|
|
// {
|
|
// if (left <= 0)
|
|
// break;
|
|
// foreach (var inventory in kot.Inventories)
|
|
// {
|
|
// if (left <= 0)
|
|
// break;
|
|
// if (inventory.Product.BaseCode == baseCode)
|
|
// {
|
|
// using (var bi = new InventoryBI())
|
|
// {
|
|
// var inventoryQuantity = inventory.Quantity * inventory.Product.Quantity;
|
|
|
|
// if (inventoryQuantity > left)
|
|
// {
|
|
// var newQuantity = inventory.Quantity * (inventoryQuantity - left) / inventoryQuantity;
|
|
// var i = bi.Get(x => x.InventoryID == inventory.InventoryID);
|
|
// i.Quantity = newQuantity;
|
|
// bi.Update(i);
|
|
// left = 0;
|
|
// }
|
|
// else
|
|
// {
|
|
// left -= inventoryQuantity;
|
|
// bi.Delete(x => x.InventoryID == inventory.InventoryID);
|
|
// }
|
|
// }
|
|
// }
|
|
// }
|
|
// }
|
|
// }
|
|
// return GetBeer(baseCode, startDate, finishDate);
|
|
// }
|
|
//private static int GetNewID(int code, int toBaseCode)
|
|
//{
|
|
// // Name Mug, Pit, H H
|
|
// // Dark 301, 305, 384 // BaseCode = 1
|
|
// // Wheat 300, 304, 383 // BaseCode = 2
|
|
// // Premium 299, 303, 382 // BaseCode = 3
|
|
// // Light 297, 302, 363 // BaseCode = 4
|
|
// // Dragon 677, 679, 678 // BaseCode = 5
|
|
// // Festivals 762 764, 752 // BaseCode = 6
|
|
// // Festivals 751, 753, 763 // BaseCode = 6
|
|
// // Festivals 734, 736, 587 // BaseCode = 6
|
|
// // Festivals 408, 409, 735 // BaseCode = 6
|
|
// // Strong 697, 708, 707 // BaseCode = 7
|
|
// var list = new List<int> { 301, 300, 299, 297, 677, 762, 751, 734, 408, 697 };
|
|
// if (list.Contains(code))
|
|
// {
|
|
// if (toBaseCode == 1)
|
|
// return 301;
|
|
// if (toBaseCode == 2)
|
|
// return 300;
|
|
// if (toBaseCode == 3)
|
|
// return 299;
|
|
// if (toBaseCode == 4)
|
|
// return 297;
|
|
// if (toBaseCode == 5)
|
|
// return 677;
|
|
// if (toBaseCode == 6)
|
|
// return code;
|
|
// if (toBaseCode == 7)
|
|
// return 697;
|
|
// }
|
|
// list = new List<int> { 305, 304, 303, 302, 679, 764, 753, 736, 409, 708 };
|
|
// if (list.Contains(code))
|
|
// {
|
|
// if (toBaseCode == 1)
|
|
// return 305;
|
|
// if (toBaseCode == 2)
|
|
// return 304;
|
|
// if (toBaseCode == 3)
|
|
// return 303;
|
|
// if (toBaseCode == 4)
|
|
// return 302;
|
|
// if (toBaseCode == 5)
|
|
// return 679;
|
|
// if (toBaseCode == 6)
|
|
// return code;
|
|
// if (toBaseCode == 7)
|
|
// return 708;
|
|
// }
|
|
// list = new List<int> { 384, 383, 382, 363, 678, 752, 763, 587, 735, 707 };
|
|
// if (list.Contains(code))
|
|
// {
|
|
// if (toBaseCode == 1)
|
|
// return 384;
|
|
// if (toBaseCode == 2)
|
|
// return 383;
|
|
// if (toBaseCode == 3)
|
|
// return 382;
|
|
// if (toBaseCode == 4)
|
|
// return 363;
|
|
// if (toBaseCode == 5)
|
|
// return 678;
|
|
// if (toBaseCode == 6)
|
|
// return code;
|
|
// if (toBaseCode == 7)
|
|
// return 707;
|
|
// }
|
|
|
|
// return code;
|
|
//}
|
|
//public void SetMove(int fromBaseCode, int toBaseCode, DateTime startDate, DateTime finishDate)
|
|
//{
|
|
// var list = new VoucherBI().List(x => x.Date >= startDate && x.Date <= finishDate);
|
|
// using (var bi = new InventoryBI())
|
|
// {
|
|
// using (var pbi = new ProductBI())
|
|
// {
|
|
// foreach (var item in list)
|
|
// {
|
|
// foreach (var kot in item.Kots)
|
|
// {
|
|
// foreach (var inventory in kot.Inventories)
|
|
// {
|
|
// if (inventory.Product.BaseCode == fromBaseCode)
|
|
// {
|
|
// var i = bi.Get(x => x.InventoryID == inventory.InventoryID);
|
|
// i.Product = pbi.Get(x => x.ProductID == GetNewID(i.Product.ProductID, toBaseCode));
|
|
// GetNewID(i.InventoryID, toBaseCode);
|
|
// bi.Update(i);
|
|
// }
|
|
// }
|
|
// }
|
|
// }
|
|
// }
|
|
// }
|
|
//}
|
|
#endregion
|
|
public void Dispose()
|
|
{
|
|
if (_transaction != null)
|
|
_transaction.Rollback();
|
|
}
|
|
|
|
public void SaveChanges()
|
|
{
|
|
if (_transaction == null)
|
|
throw new InvalidOperationException("UnitOfWork have already been saved.");
|
|
|
|
_transaction.Commit();
|
|
_transaction = null;
|
|
}
|
|
}
|
|
} |