2018-05-17 10:22:27 +00:00
using NHibernate ;
using System ;
2011-12-05 09:41:02 +00:00
using System.Collections.Generic ;
2018-05-17 10:22:27 +00:00
using System.ComponentModel ;
2014-12-03 07:13:35 +00:00
using System.Globalization ;
2011-12-05 09:41:02 +00:00
using System.Linq ;
using Tanshu.Accounts.Entities ;
2011-01-30 07:14:05 +00:00
2011-12-05 09:41:02 +00:00
namespace Tanshu.Accounts.Repository
{
2014-12-03 07:13:35 +00:00
public class SaleInfo
2011-12-05 09:41:02 +00:00
{
2014-12-03 07:13:35 +00:00
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 ;
2016-01-04 05:22:01 +00:00
public string _startDate { get ; set ; }
public string _finishDate { get ; set ; }
2014-12-03 07:13:35 +00:00
public IList < SaleDetailJson > Sale { get ; set ; }
2016-01-04 05:22:01 +00:00
public DateTime StartDate
2014-10-12 09:41:45 +00:00
{
2014-12-03 07:13:35 +00:00
get
{
2016-01-04 05:22:01 +00:00
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 ;
2014-12-03 07:13:35 +00:00
}
2014-10-12 09:41:45 +00:00
}
2016-01-04 05:22:01 +00:00
public DateTime FinishDate
2013-02-12 13:25:10 +00:00
{
2014-12-03 07:13:35 +00:00
get
2013-02-12 13:25:10 +00:00
{
2016-01-04 05:22:01 +00:00
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 ;
2013-02-12 13:25:10 +00:00
}
}
2014-12-03 07:13:35 +00:00
}
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 ;
2016-01-04 05:22:01 +00:00
public string _date { get ; set ; }
2014-12-03 07:13:35 +00:00
public IList < BeerDetailJson > Beers { get ; set ; }
2016-01-04 05:22:01 +00:00
public DateTime Date
2013-02-12 13:25:10 +00:00
{
2014-12-03 07:13:35 +00:00
get
2013-02-12 13:25:10 +00:00
{
2016-01-04 05:22:01 +00:00
if ( ! date . HasValue )
{
DateTime tDate ;
if ( ! DateTime . TryParseExact ( _date , "dd-MMM-yyyy" , culture , DateTimeStyles . NoCurrentDateDefault , out tDate ) )
throw new ArgumentException ( ) ;
2014-12-03 07:13:35 +00:00
date = tDate ;
2016-01-04 05:22:01 +00:00
}
return date . Value ;
2013-02-12 13:25:10 +00:00
}
}
2014-12-03 07:13:35 +00:00
public decimal Quantity
2013-02-12 13:25:10 +00:00
{
2014-12-03 07:13:35 +00:00
get
2013-02-12 13:25:10 +00:00
{
2014-12-03 07:13:35 +00:00
decimal amount = 0 ;
foreach ( var item in Beers )
2013-02-12 13:25:10 +00:00
{
2014-12-03 07:13:35 +00:00
amount + = item . Quantity ;
2013-02-12 13:25:10 +00:00
}
2014-12-03 07:13:35 +00:00
return amount ;
2013-02-12 13:25:10 +00:00
}
}
2014-12-03 07:13:35 +00:00
}
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 ;
2016-01-04 05:22:01 +00:00
public string _date { get ; set ; }
2014-12-03 07:13:35 +00:00
public decimal Amount { get ; set ; }
2016-01-04 05:22:01 +00:00
public DateTime Date
2013-02-12 13:25:10 +00:00
{
2014-12-03 07:13:35 +00:00
get
2013-02-12 13:25:10 +00:00
{
2016-01-04 05:22:01 +00:00
if ( ! date . HasValue )
{
DateTime tDate ;
if ( ! DateTime . TryParseExact ( _date , "dd-MMM-yyyy" , culture , DateTimeStyles . NoCurrentDateDefault , out tDate ) )
throw new ArgumentException ( ) ;
2014-12-03 07:13:35 +00:00
date = tDate ;
2016-01-04 05:22:01 +00:00
}
return date . Value ;
2013-02-12 13:25:10 +00:00
}
}
2014-12-03 07:13:35 +00:00
}
public class ManagementBI : IUnitOfWork
{
2016-04-21 11:55:14 +00:00
protected readonly IStatelessSession _session ;
2014-12-03 07:13:35 +00:00
private ITransaction _transaction ;
public ManagementBI ( )
{
2016-04-21 11:55:14 +00:00
_session = SessionManager . StatelessSession ;
2014-12-03 07:13:35 +00:00
_transaction = _session . BeginTransaction ( ) ;
}
#region Cleanup
public void DeleteVoid ( DateTime startDate , DateTime finishDate )
{
2016-04-21 11:55:14 +00:00
var query = @ "delete from VoucherSettlement vs where vs.Voucher in (
2014-12-03 07:13:35 +00:00
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 ( ) ;
2016-04-21 11:55:14 +00:00
query = @ "delete from Inventory i where i.Kot in (
select k from Kot k where k . Voucher in (
2014-12-03 07:13:35 +00:00
select v from Voucher v where v . Date > = : startDate and v . Date < = : finishDate and v . Void = : void
2016-04-21 11:55:14 +00:00
) ) ";
2014-12-03 07:13:35 +00:00
_session
. CreateQuery ( query )
. SetParameter ( "startDate" , startDate )
. SetParameter ( "finishDate" , finishDate )
. SetParameter ( "void" , true )
. ExecuteUpdate ( ) ;
2016-04-21 11:55:14 +00:00
query = @ "delete from Kot k where k.Voucher in (
2014-12-03 07:13:35 +00:00
select v from Voucher v where v . Date > = : startDate and v . Date < = : finishDate and v . Void = : void
2016-04-21 11:55:14 +00:00
) ";
2014-12-03 07:13:35 +00:00
_session
. CreateQuery ( query )
. SetParameter ( "startDate" , startDate )
. SetParameter ( "finishDate" , finishDate )
. SetParameter ( "void" , true )
. ExecuteUpdate ( ) ;
2016-04-21 11:55:14 +00:00
query = @"delete from Voucher v where v.Date >= :startDate and v.Date <= :finishDate and v.Void = :void" ;
2014-12-03 07:13:35 +00:00
_session
. CreateQuery ( query )
. SetParameter ( "startDate" , startDate )
. SetParameter ( "finishDate" , finishDate )
. SetParameter ( "void" , true )
. ExecuteUpdate ( ) ;
2016-04-21 11:55:14 +00:00
}
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
)
2014-12-03 07:13:35 +00:00
) ";
_session
. CreateQuery ( query )
. SetParameter ( "startDate" , startDate )
. SetParameter ( "finishDate" , finishDate )
2016-04-21 11:55:14 +00:00
. SetParameter ( "tobacco" , "Cash Charges" )
2014-12-03 07:13:35 +00:00
. ExecuteUpdate ( ) ;
2016-04-21 11:55:14 +00:00
}
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
) ";
2014-12-03 07:13:35 +00:00
_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 ( ) ;
}
2013-02-12 13:25:10 +00:00
public void CombineKots ( DateTime startDate , DateTime finishDate )
{
2014-12-03 07:13:35 +00:00
var query = @ "
2016-04-21 11:55:14 +00:00
select v . VoucherID , k . KotID , i . InventoryID , i . Product . ProductID , i . Quantity , i . IsHappyHour
2014-12-03 07:13:35 +00:00
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 ] ;
2016-04-21 11:55:14 +00:00
var isHappyHour = ( bool ) item [ 5 ] ;
2014-12-03 07:13:35 +00:00
if ( ! vouchers . ContainsKey ( voucherID ) )
vouchers . Add ( voucherID , new Dictionary < Guid , List < object [ ] > > ( ) ) ;
if ( ! vouchers [ voucherID ] . ContainsKey ( kotID ) )
vouchers [ voucherID ] . Add ( kotID , new List < object [ ] > ( ) ) ;
2016-04-21 11:55:14 +00:00
vouchers [ voucherID ] [ kotID ] . Add ( new object [ ] { inventoryID , productID , quantity , isHappyHour } ) ;
2014-12-03 07:13:35 +00:00
}
foreach ( var voucher in vouchers )
2013-02-12 13:25:10 +00:00
{
2014-12-03 07:13:35 +00:00
var kots = voucher . Value ;
if ( kots . Count < = 1 )
2014-10-12 09:41:45 +00:00
continue ;
2016-04-21 11:55:14 +00:00
Dictionary < Int32 , Guid > kotInventories = new Dictionary < Int32 , Guid > ( ) ;
2014-12-03 07:13:35 +00:00
foreach ( var item in kots . ElementAt ( 0 ) . Value )
2013-02-12 13:25:10 +00:00
{
2014-12-03 07:13:35 +00:00
Guid inventoryID = ( Guid ) item [ 0 ] ;
Guid productID = ( Guid ) item [ 1 ] ;
2016-04-21 11:55:14 +00:00
bool isHappyHour = ( bool ) item [ 3 ] ;
kotInventories . Add ( productID . GetHashCode ( ) ^ isHappyHour . GetHashCode ( ) , inventoryID ) ;
2014-12-03 07:13:35 +00:00
}
for ( var i = kots . Count ; i > 1 ; i - - )
{
foreach ( var item in kots . ElementAt ( i - 1 ) . Value )
2013-02-12 13:25:10 +00:00
{
2014-12-03 07:13:35 +00:00
Guid inventoryID = ( Guid ) item [ 0 ] ;
Guid productID = ( Guid ) item [ 1 ] ;
decimal quantity = ( decimal ) item [ 2 ] ;
2016-04-21 11:55:14 +00:00
bool isHappyHour = ( bool ) item [ 3 ] ;
var key = productID . GetHashCode ( ) ^ isHappyHour . GetHashCode ( ) ;
if ( kotInventories . ContainsKey ( key ) )
2013-02-12 13:25:10 +00:00
{
2014-12-03 07:13:35 +00:00
query = @"update Inventory set Quantity = Quantity + :quantity where InventoryID = :inventoryID" ;
2016-04-21 11:55:14 +00:00
_session . CreateQuery ( query ) . SetParameter ( "quantity" , quantity ) . SetParameter ( "inventoryID" , kotInventories [ key ] ) . ExecuteUpdate ( ) ;
2014-12-03 07:13:35 +00:00
query = @"delete from Inventory where InventoryID = :inventoryID" ;
_session . CreateQuery ( query ) . SetParameter ( "inventoryID" , inventoryID ) . ExecuteUpdate ( ) ;
2014-10-12 09:41:45 +00:00
}
else
{
2016-04-21 11:55:14 +00:00
kotInventories . Add ( key , inventoryID ) ;
2014-12-03 07:13:35 +00:00
query = @"update Inventory set Kot.KotID = :kotID where InventoryID = :inventoryID" ;
_session . CreateQuery ( query ) . SetParameter ( "kotID" , kots . ElementAt ( 0 ) . Key ) . SetParameter ( "inventoryID" , inventoryID ) . ExecuteUpdate ( ) ;
2013-02-12 13:25:10 +00:00
}
}
2014-12-03 07:13:35 +00:00
query = @"delete from Kot where KotID = :kotID" ;
_session . CreateQuery ( query ) . SetParameter ( "kotID" , kots . ElementAt ( i - 1 ) . Key ) . ExecuteUpdate ( ) ;
2013-02-12 13:25:10 +00:00
}
}
}
public void RemoveBlankKots ( DateTime startDate , DateTime finishDate )
{
2014-12-03 07:13:35 +00:00
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 ( ) ;
}
2016-04-21 11:55:14 +00:00
2014-12-03 07:13:35 +00:00
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 )
2013-02-12 13:25:10 +00:00
{
2014-12-03 07:13:35 +00:00
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 ] )
2013-02-12 13:25:10 +00:00
{
2014-12-03 07:13:35 +00:00
case VoucherType . NoCharge :
settlementType = SettleOption . NoCharge ;
break ;
case VoucherType . Staff :
settlementType = SettleOption . Staff ;
break ;
default :
settlementType = SettleOption . Cash ;
break ;
2013-02-12 13:25:10 +00:00
}
2014-12-03 07:13:35 +00:00
var amount = - 1 * ( decimal ) item . Value [ 1 ] ;
var roundoff = Math . Round ( amount ) - amount ;
2016-01-04 05:22:01 +00:00
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 ;
2014-12-03 07:13:35 +00:00
query = @"delete from VoucherSettlement vs where vs.Voucher.VoucherID = :voucherID" ;
_session
. CreateQuery ( query )
. SetParameter ( "voucherID" , item . Key )
. ExecuteUpdate ( ) ;
2016-04-21 11:55:14 +00:00
_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 } ) ;
2013-02-12 13:25:10 +00:00
}
}
2016-12-03 06:38:47 +00:00
public void UpdateBillID ( DateTime startDate , DateTime finishDate , BackgroundWorker bw )
2013-02-12 13:25:10 +00:00
{
var query = @ "
2014-12-03 07:13:35 +00:00
select MAX ( v . BillID )
2013-02-12 13:25:10 +00:00
from Voucher v
2014-12-03 07:13:35 +00:00
where v . Date < : startDate and v . Void = false and v . VoucherType in ( : regular , : takeAway )
2013-02-12 13:25:10 +00:00
";
2014-10-12 09:41:45 +00:00
var lastBill = _session
2013-02-12 13:25:10 +00:00
. CreateQuery ( query )
. SetParameter ( "startDate" , startDate )
2014-12-03 07:13:35 +00:00
. SetParameter ( "regular" , VoucherType . Regular )
. SetParameter ( "takeAway" , VoucherType . TakeAway )
2013-02-12 13:25:10 +00:00
. UniqueResult ( ) ;
2014-12-03 07:13:35 +00:00
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 ( ) ;
2016-12-03 06:38:47 +00:00
var count = " of " + list . Count . ToString ( ) ;
2014-12-03 07:13:35 +00:00
var i = 0 ;
2013-02-12 13:25:10 +00:00
foreach ( var voucher in list )
{
2014-12-03 07:13:35 +00:00
i + + ;
if ( i % 20 = = 0 )
2016-12-03 06:38:47 +00:00
bw . ReportProgress ( 0 , "Loop " + i . ToString ( ) + count ) ;
2013-02-12 13:25:10 +00:00
if ( voucher . BillID ! = newID )
{
2014-12-03 07:13:35 +00:00
var update = _session . CreateSQLQuery ( "exec UpdateBillID ?,?" ) ;
update . SetParameter < Guid > ( 0 , voucher . VoucherID ) ;
update . SetParameter < int > ( 1 , newID ) ;
update . ExecuteUpdate ( ) ;
2013-02-12 13:25:10 +00:00
}
newID = GetNewID ( newID ) ;
}
2016-12-03 06:38:47 +00:00
}
2013-02-12 13:25:10 +00:00
2016-12-03 06:38:47 +00:00
public void UpdateOtherBillID ( DateTime startDate , DateTime finishDate , VoucherType vt , BackgroundWorker bw )
{
var query = @ "
select MAX ( v . BillID )
2013-02-12 13:25:10 +00:00
from Voucher v
2016-12-03 06:38:47 +00:00
where v . Date < : startDate and v . Void = false and v . VoucherType = : vt ";
var lastBill = _session
2013-02-12 13:25:10 +00:00
. CreateQuery ( query )
. SetParameter ( "startDate" , startDate )
2016-12-03 06:38:47 +00:00
. SetParameter ( "vt" , vt )
2013-02-12 13:25:10 +00:00
. UniqueResult ( ) ;
2016-12-03 06:38:47 +00:00
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 )
2014-12-03 07:13:35 +00:00
. OrderBy ( x = > x . Date ) . Asc
. List ( ) ;
2016-12-03 06:38:47 +00:00
var count = " of " + list . Count . ToString ( ) ;
var i = 0 ;
2013-02-12 13:25:10 +00:00
foreach ( var voucher in list )
{
2016-12-03 06:38:47 +00:00
i + + ;
if ( i % 20 = = 0 )
bw . ReportProgress ( 0 , "Loop " + i . ToString ( ) + count ) ;
2013-02-12 13:25:10 +00:00
if ( voucher . BillID ! = newID )
{
2014-12-03 07:13:35 +00:00
var update = _session . CreateSQLQuery ( "exec UpdateBillID ?,?" ) ;
update . SetParameter < Guid > ( 0 , voucher . VoucherID ) ;
update . SetParameter < int > ( 1 , newID ) ;
update . ExecuteUpdate ( ) ;
2013-02-12 13:25:10 +00:00
}
2014-11-02 08:03:31 +00:00
newID + = 1 ;
2013-02-12 13:25:10 +00:00
}
}
2014-11-02 08:03:31 +00:00
private static int GetNewID ( int lastBill )
2013-02-12 13:25:10 +00:00
{
2014-11-02 08:03:31 +00:00
lastBill + = 1 ;
if ( lastBill % 10000 = = 0 )
lastBill + = 1 ;
return lastBill ;
2013-02-12 13:25:10 +00:00
}
2011-01-30 07:14:05 +00:00
2013-02-12 13:25:10 +00:00
#endregion
2016-04-21 11:55:14 +00:00
public void LiqNcSwap ( decimal rate , decimal target , DateTime startDate , DateTime finishDate )
{
var existingSale = GetSaleAmount ( rate , startDate , finishDate ) ;
if ( existingSale > target / . 75 M )
{
// Do not put all in NC this will allow for about 25% discount on the rest of non nc liqour
existingSale = MoveToNc ( rate , target / . 75 M , startDate , finishDate ) ;
}
else if ( existingSale < target )
{
existingSale = MoveFromNc ( rate , target , startDate , finishDate ) ;
}
}
public decimal MoveToNc ( decimal rate , decimal target , DateTime startDate , DateTime finishDate )
2013-02-12 13:25:10 +00:00
{
2014-12-03 07:13:35 +00:00
string query = @ "
2016-04-21 11:55:14 +00:00
select v . VoucherID , sum ( i . Quantity * i . Price * ( 1 - i . Discount ) * ( 1 + case when i . IsScTaxable then i . ServiceCharge else 0 end ) )
2013-02-12 13:25:10 +00:00
from Voucher v
inner join v . Kots k
inner join k . Inventories i
2016-04-21 11:55:14 +00:00
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 )
2013-02-12 13:25:10 +00:00
group by v . VoucherID
2016-04-21 11:55:14 +00:00
order by sum ( i . Quantity * i . Price * ( 1 - i . Discount ) * ( 1 + case when i . IsScTaxable then i . ServiceCharge else 0 end ) ) desc
2013-02-12 13:25:10 +00:00
";
2014-10-12 09:41:45 +00:00
var list = _session
2013-02-12 13:25:10 +00:00
. CreateQuery ( query )
2016-01-04 05:22:01 +00:00
. SetParameter ( "startDate" , startDate )
. SetParameter ( "finishDate" , finishDate )
2013-02-12 13:25:10 +00:00
. SetParameter ( "nc" , VoucherType . NoCharge )
. SetParameter ( "staff" , VoucherType . Staff )
2014-12-03 07:13:35 +00:00
. SetParameter ( "vatLiquor" , new Guid ( "2C8AD8EC-E09A-4194-B348-01243474CF26" ) )
2016-04-21 11:55:14 +00:00
. SetParameter ( "vatRate" , rate )
2013-02-12 13:25:10 +00:00
. List ( ) ;
2016-04-21 11:55:14 +00:00
var totalAmount = GetSaleAmount ( rate , startDate , finishDate ) ;
2014-12-03 07:13:35 +00:00
for ( int i = 0 ; i < list . Count / 20 ; i + = 2 ) // Skip every alternate bill
2013-02-12 13:25:10 +00:00
{
2014-12-03 07:13:35 +00:00
if ( totalAmount < = target )
2013-02-12 13:25:10 +00:00
break ;
var item = ( object [ ] ) list [ i ] ;
2014-10-12 09:41:45 +00:00
var voucherID = ( Guid ) item [ 0 ] ;
2013-02-12 13:25:10 +00:00
var amount = ( decimal ) item [ 1 ] ;
2014-12-03 07:13:35 +00:00
query = @"update Voucher set VoucherType = :nc where VoucherID = :voucherID" ;
_session
2013-02-12 13:25:10 +00:00
. CreateQuery ( query )
. SetParameter ( "nc" , VoucherType . NoCharge )
2014-12-03 07:13:35 +00:00
. SetParameter ( "voucherID" , voucherID )
. ExecuteUpdate ( ) ;
totalAmount - = amount ;
2013-02-12 13:25:10 +00:00
}
2016-04-21 11:55:14 +00:00
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 ;
2013-02-12 13:25:10 +00:00
}
2014-12-03 07:13:35 +00:00
public IList < SaleInfo > GetSaleAndVat ( DateTime startDate , DateTime finishDate )
2013-02-12 13:25:10 +00:00
{
const string query = @ "
2014-12-03 07:13:35 +00:00
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
2013-02-12 13:25:10 +00:00
from Voucher v
inner join v . Kots k
inner join k . Inventories i
2014-12-03 07:13:35 +00:00
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
2013-02-12 13:25:10 +00:00
";
2014-12-03 07:13:35 +00:00
var list = _session
2013-02-12 13:25:10 +00:00
. CreateQuery ( query )
. SetParameter ( "startDate" , startDate )
. SetParameter ( "finishDate" , finishDate )
2014-12-03 07:13:35 +00:00
. 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 ;
2013-02-12 13:25:10 +00:00
}
public decimal GetServiceTax ( DateTime startDate , DateTime finishDate )
{
const string query = @ "
2014-12-03 07:13:35 +00:00
select sum ( i . Quantity * i . Price * ( 1 - i . Discount ) * ( 1 + case when i . IsScTaxable then i . ServiceCharge else 0 end ) * i . ServiceTaxRate ) as Amount
2013-02-12 13:25:10 +00:00
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 )
";
2014-10-12 09:41:45 +00:00
var qty = _session
2013-02-12 13:25:10 +00:00
. CreateQuery ( query )
. SetParameter ( "startDate" , startDate )
. SetParameter ( "finishDate" , finishDate )
. SetParameter ( "nc" , VoucherType . NoCharge )
. SetParameter ( "staff" , VoucherType . Staff )
. UniqueResult ( ) ;
return qty = = null ? 0 : ( decimal ) qty ;
}
2014-12-03 07:13:35 +00:00
public SaleInfo GetMinMaxBills ( DateTime startDate , DateTime finishDate )
2013-02-12 13:25:10 +00:00
{
const string query = @ "
2014-12-03 07:13:35 +00:00
select MIN ( v . BillID ) as StartBill , MAX ( v . BillID ) as FinishBill
2013-02-12 13:25:10 +00:00
from Voucher v
2014-12-03 07:13:35 +00:00
where v . Date > = : startDate and v . Date < = : finishDate and v . Void = false and v . VoucherType in ( : regular , : takeAway )
2013-02-12 13:25:10 +00:00
";
2014-12-03 07:13:35 +00:00
var item = _session
2013-02-12 13:25:10 +00:00
. CreateQuery ( query )
2014-12-03 07:13:35 +00:00
. 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 ] } ;
2013-02-12 13:25:10 +00:00
}
2014-12-03 07:13:35 +00:00
public int? GetLastBill ( DateTime date )
2013-02-12 13:25:10 +00:00
{
const string query = @ "
select v . BillID
from Voucher v
2014-12-03 07:13:35 +00:00
where v . Date > = : startDate and v . Date < = : finishDate and v . Void = false and v . VoucherType in ( : regular , : takeAway )
2013-02-12 13:25:10 +00:00
order by v . Date desc
";
2014-10-12 09:41:45 +00:00
var qty = _session
2013-02-12 13:25:10 +00:00
. CreateQuery ( query )
. SetParameter ( "startDate" , date . AddHours ( 7 ) )
. SetParameter ( "finishDate" , date . AddDays ( 1 ) . AddHours ( 7 ) )
2014-12-03 07:13:35 +00:00
. SetParameter ( "regular" , VoucherType . Regular )
. SetParameter ( "takeAway" , VoucherType . TakeAway )
2013-02-12 13:25:10 +00:00
. SetMaxResults ( 1 )
. UniqueResult ( ) ;
2014-12-03 07:13:35 +00:00
if ( qty = = null )
return null ;
return ( int ) qty ;
2013-02-12 13:25:10 +00:00
}
2016-01-04 05:22:01 +00:00
private decimal GetSaleAmount ( decimal vat , DateTime startDate , DateTime finishDate )
2013-02-12 13:25:10 +00:00
{
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
2014-12-03 07:13:35 +00:00
where v . Date > = : startDate and v . Date < = : finishDate and i . VatRate = : vat and v . Void = false and v . VoucherType not in ( : nc , : staff )
2012-12-01 09:48:02 +00:00
";
2014-10-12 09:41:45 +00:00
var qty = _session
2012-12-01 09:48:02 +00:00
. CreateQuery ( query )
. SetParameter ( "startDate" , startDate )
. SetParameter ( "finishDate" , finishDate )
2013-02-12 13:25:10 +00:00
. SetParameter ( "vat" , vat )
2012-12-01 09:48:02 +00:00
. SetParameter ( "nc" , VoucherType . NoCharge )
. SetParameter ( "staff" , VoucherType . Staff )
. UniqueResult ( ) ;
return qty = = null ? 0 : ( decimal ) qty ;
}
2016-01-04 05:22:01 +00:00
public class DateVatSale
2012-12-01 09:48:02 +00:00
{
2016-01-04 05:22:01 +00:00
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 = @ "
2016-04-21 11:55:14 +00:00
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 )
2016-01-04 05:22:01 +00:00
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 ) )
2012-12-01 09:48:02 +00:00
{
2016-01-04 05:22:01 +00:00
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 )
2012-12-01 09:48:02 +00:00
{
2016-01-04 05:22:01 +00:00
list . Add ( new DateVatSale ( ) { Date = date , VatRate = ( decimal ) item [ 0 ] , Net = ( decimal ) item [ 1 ] , Gross = ( decimal ) item [ 2 ] } ) ;
2014-12-03 07:13:35 +00:00
}
2016-01-04 05:22:01 +00:00
}
return list ;
}
2016-04-21 11:55:14 +00:00
private class InvDate
2016-01-04 05:22:01 +00:00
{
public DateTime Date { get ; set ; }
2016-04-21 11:55:14 +00:00
public Inventory Inv { get ; set ; }
2013-02-12 13:25:10 +00:00
}
2016-01-04 05:22:01 +00:00
public void SetQuantityAndDiscount ( IList < SaleDetailJson > sale , IList < CreditJson > credit , DateTime startDate , DateTime finishDate )
2013-02-12 13:25:10 +00:00
{
var rand = new Random ( ) ;
2016-04-21 11:55:14 +00:00
const string query = @ "
select v . Date , i
2014-12-03 07:13:35 +00:00
from Voucher v
inner join v . Kots k
inner join k . Inventories i
where v . Date > = : startDate and v . Date < = : finishDate
2016-04-21 11:55:14 +00:00
and v . VoucherType = : regular ";
2014-12-03 07:13:35 +00:00
var list = _session
. CreateQuery ( query )
2016-01-04 05:22:01 +00:00
. SetParameter ( "startDate" , startDate . AddHours ( 7 ) )
. SetParameter ( "finishDate" , finishDate . AddDays ( 1 ) . AddHours ( 7 ) )
2014-12-03 07:13:35 +00:00
. SetParameter ( "regular" , VoucherType . Regular )
. List < object [ ] > ( ) ;
2016-01-04 05:22:01 +00:00
List < InvDate > inventories = new List < InvDate > ( ) ;
foreach ( var item in Randomize ( list ) )
2014-12-03 07:13:35 +00:00
{
2016-01-04 05:22:01 +00:00
inventories . Add ( new InvDate ( )
{
Date = ( ( DateTime ) item [ 0 ] ) . AddHours ( - 7 ) . Date ,
2016-04-21 11:55:14 +00:00
Inv = ( Inventory ) item [ 1 ]
2016-01-04 05:22:01 +00:00
} ) ;
2014-12-03 07:13:35 +00:00
}
2016-04-21 11:55:14 +00:00
var dailySaleGroupedByVat = GetSaleAmount ( startDate , finishDate ) ;
foreach ( var s in sale )
2013-02-12 13:25:10 +00:00
{
2016-04-21 11:55:14 +00:00
// 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 ) ;
2016-05-14 13:58:18 +00:00
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
2016-12-03 06:38:47 +00:00
c = new CreditJson ( ) { _date = inv . Date . ToString ( "dd-MMM-yyyy" ) , Amount = s . Amount } ;
2016-05-14 13:58:18 +00:00
// throw new ArgumentException("Unknown date encountered");
2016-12-03 06:38:47 +00:00
if ( Math . Abs ( s . Amount ) < 1 )
2016-04-21 11:55:14 +00:00
continue ; // Close enough for now
2016-12-03 06:38:47 +00:00
if ( s . Amount > 0 & & c . Amount < = 0 )
2016-04-21 11:55:14 +00:00
continue ; //Move on if we have to reduce and we do not have credit sale margin
if ( ! s . IsLiq ) //Food
2014-12-03 07:13:35 +00:00
{
2016-04-21 11:55:14 +00:00
if ( inv . Inv . IsHappyHour )
throw new ArgumentException ( "Non-liqour does not have happy hour" ) ;
if ( s . Amount < 0 )
2016-01-04 05:22:01 +00:00
{
2016-04-21 11:55:14 +00:00
IncreaseFood ( s , inv , c ) ;
}
else
{
DecreaseFood ( s , inv , c ) ;
}
}
else
{
if ( s . Amount < 0 )
{
//Increase Sales
if ( inv . Inv . IsHappyHour )
IncreaseLiqourUsingHappyHour ( s , inv , c ) ;
2016-07-04 06:21:39 +00:00
else if ( inv . Inv . Discount > 0 )
2016-04-21 11:55:14 +00:00
IncreaseLiqourUsingDiscount ( s , inv , c ) ;
2016-01-04 05:22:01 +00:00
}
else
{
2016-12-03 06:38:47 +00:00
if ( inv . Inv . Quantity ! = 0 & & inv . Inv . EffectivePrice ! = 0 )
DecreaseLiqour ( rand , s , inv , c ) ;
2016-01-04 05:22:01 +00:00
}
2013-02-12 13:25:10 +00:00
}
}
2012-12-01 09:48:02 +00:00
}
2016-07-04 06:21:39 +00:00
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");
2016-12-03 06:38:47 +00:00
if ( Math . Abs ( s . Amount ) < 2 )
2016-07-04 06:21:39 +00:00
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 ) ;
}
}
2016-04-21 11:55:14 +00:00
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 ;
}
2016-07-04 06:21:39 +00:00
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;
}
2016-04-21 11:55:14 +00:00
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 ;
}
2012-12-01 09:48:02 +00:00
2014-12-03 07:13:35 +00:00
#region Beer
public decimal GetBeer ( DateTime startDate , DateTime finishDate )
2012-12-01 09:48:02 +00:00
{
const string query = @ "
2014-12-03 07:13:35 +00:00
select sum ( i . Quantity * p . Quantity ) as Quantity
2012-12-01 09:48:02 +00:00
from Voucher v
inner join v . Kots k
inner join k . Inventories i
inner join i . Product p
2014-12-03 07:13:35 +00:00
where v . Date > = : startDate and v . Date < = : finishDate and v . Void = false and p . Quantity ! = 0
2012-12-01 09:48:02 +00:00
";
2014-12-03 07:13:35 +00:00
var qty = _session
2012-12-01 09:48:02 +00:00
. CreateQuery ( query )
. SetParameter ( "startDate" , startDate )
. SetParameter ( "finishDate" , finishDate )
2014-12-03 07:13:35 +00:00
. UniqueResult ( ) ;
return qty = = null ? 0 : ( decimal ) qty ;
2012-12-01 09:48:02 +00:00
}
2014-12-03 07:13:35 +00:00
public decimal SetBeer ( DateTime startDate , DateTime finishDate , decimal quantity )
2012-12-01 09:48:02 +00:00
{
2014-12-03 07:13:35 +00:00
var query = @ "
select i . InventoryID , i . Product . ProductID , i . Quantity , p . Quantity
2012-12-01 09:48:02 +00:00
from Voucher v
inner join v . Kots k
inner join k . Inventories i
inner join i . Product p
2014-12-03 07:13:35 +00:00
where v . Date > = : startDate and v . Date < = : finishDate
and p . Quantity ! = 0 ";
2014-10-12 09:41:45 +00:00
var list = _session
2012-12-01 09:48:02 +00:00
. CreateQuery ( query )
. SetParameter ( "startDate" , startDate )
. SetParameter ( "finishDate" , finishDate )
2014-12-03 07:13:35 +00:00
. List < object [ ] > ( ) ;
2014-10-12 09:41:45 +00:00
list = Randomize ( list ) ;
2014-12-03 07:13:35 +00:00
Dictionary < Guid , object [ ] > inventories = new Dictionary < Guid , object [ ] > ( ) ;
2011-12-05 09:41:02 +00:00
foreach ( var item in list )
2014-12-03 07:13:35 +00:00
{
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 ;
2016-12-03 06:38:47 +00:00
if ( left < 0 )
Console . Write ( "Beer was already negative" ) ;
2014-12-03 07:13:35 +00:00
foreach ( var item in inventories )
2011-12-05 09:41:02 +00:00
{
2012-12-01 09:48:02 +00:00
if ( left < = 0 )
break ;
2014-12-03 07:13:35 +00:00
var productID = ( Guid ) item . Value [ 0 ] ;
var inventoryQuantity = ( decimal ) item . Value [ 1 ] ;
var productQuantity = ( decimal ) item . Value [ 2 ] ;
2012-12-01 09:48:02 +00:00
2014-12-03 07:13:35 +00:00
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 ;
2011-12-05 09:41:02 +00:00
}
}
2014-12-03 07:13:35 +00:00
return GetBeer ( startDate , finishDate ) ;
2012-12-01 09:48:02 +00:00
}
2013-02-12 13:25:10 +00:00
#endregion
#region Helper and Comments
2011-12-05 09:41:02 +00:00
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 ( ) ;
}
2014-12-03 07:13:35 +00:00
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)
2013-02-12 13:25:10 +00:00
// {
// 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));
2014-12-03 07:13:35 +00:00
// var left = GetBeer(baseCode, startDate, finishDate) - quantity;
2013-02-12 13:25:10 +00:00
// 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);
// }
// }
// }
// }
// }
// }
2014-12-03 07:13:35 +00:00
// return GetBeer(baseCode, startDate, finishDate);
2013-02-12 13:25:10 +00:00
// }
//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
2014-10-12 09:41:45 +00:00
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 ;
}
2011-12-05 09:41:02 +00:00
}
}