public static DB.GLX_Header CreateReversalEntry(DB.GLX_Header glx_header, DataContext dataContext)
{
DB.GLX_Header glx_header_reversal = GLX.GLX_Header.New;
glx_header_reversal.JournalTypeId = (byte)GLX.GLX_JournalType.Reversal;
glx_header_reversal.Reference = glx_header.Reference;
glx_header_reversal.Description = String.Format("Reverse {0}", glx_header.Description);
glx_header_reversal.TrackId = glx_header.TrackId;
glx_header_reversal.Date = DateTime.Now;
//TODO: Fix this
//glx_header_reversal.InternalReference = (glx_header.InternalReference ?? "") + ".REV";
// Check if entry period is still open
DB.SYS_Period period = SYS.SYS_Period.Load(glx_header.PeriodId, dataContext);
if (period.StatusId == (byte)SYS.SYS_Status.Open)
{
glx_header_reversal.Date = glx_header.Date;
glx_header_reversal.PeriodId = period.Id;
}
// Add reversal lines - they currently default to aging 00 though
foreach (DB.GLX_Line line in glx_header.GLX_Line)
{
DB.GLX_Line glx_line_reversal = GLX.GLX_Line.New;
glx_line_reversal.EntityId = line.EntityId;
glx_line_reversal.Amount = -line.Amount;
glx_line_reversal.CenterId = line.CenterId;
glx_line_reversal.CreatedBy = ApplicationDataContext.Instance.LoggedInUser.PersonId;
glx_line_reversal.CreatedOn = DateTime.Now;
glx_line_reversal.AgingId = line.AgingId;
glx_header_reversal.GLX_Line.Add(glx_line_reversal);
}
return(glx_header_reversal);
}
private void InstantFeedbackSourceSalesHistory_GetQueryable(object sender, DevExpress.Data.Linq.GetQueryableEventArgs e)
{
DB.SYS_Period currentPeriod = BL.SYS.SYS_Period.GetCurrentPeriod(DataContext);
DateTime dateAfter = DateTime.Today.AddYears(-2);
e.QueryableSource = DataContext.ReadonlyContext.VW_ItemHistory.Where(n => n.EntityId == itmInventory.EntityId && n.Date > dateAfter && n.Date <= currentPeriod.EndDate && n.SiteId == defaultSiteId);
}
protected override void OnStart()
{
try
{
base.OnStart();
DB.SYS_Period lastPeriod = DataContext.EntitySystemContext.SYS_Period.OrderByDescending(o => o.EndDate).FirstOrDefault();
if (lastPeriod != null)
{
txtFinancialYear.EditValue = lastPeriod.FinancialYear + 1;
ddlMonth.EditValue = lastPeriod.EndDate.ToString("MMMM");
txtFinancialYear.Enabled = false;
ddlMonth.Enabled = false;
}
else
{
txtFinancialYear.EditValue = DateTime.Now.Year;
ddlMonth.EditValue = "January";
}
buildPeriods();
}
catch (Exception ex)
{
if (CDS.Shared.Exception.UserInterfaceExceptionHandler.HandleException(ref ex))
{
throw ex;
}
}
}
private void InstantFeedbackSourceHistory_GetQueryable(object sender, DevExpress.Data.Linq.GetQueryableEventArgs e)
{
DB.SYS_Period currentPeriod = BL.SYS.SYS_Period.GetCurrentPeriod(DataContext);
DateTime dateAfter = DateTime.Today.AddYears(-2);
e.QueryableSource = DataContext.ReadonlyContext.VW_CompanyHistory.Where(n => n.CompanyId == orgCompany.Id && n.Date > dateAfter && n.Date <= currentPeriod.EndDate);
}
public static DB.ITM_History GetItemCurrentHistory(DB.ITM_Inventory entry, DataContext dataContext)
{
DB.SYS_Period currentPeriod = SYS.SYS_Period.GetCurrentPeriod(dataContext);
DB.ITM_History history = dataContext.EntityInventoryContext.ITM_History.FirstOrDefault(n => n.InventoryId ==
entry.EntityId && n.PeriodId == currentPeriod.Id && n.SiteId == ApplicationDataContext.Instance.LoggedInUser.DefaultSiteId);
return(history);
}
private void LoadPeriod(DB.SYS_Period glxPeriod)
{
try
{
if (glxPeriod != null)
{
BindingSource.DataSource = glxPeriod;
btnEndPeriod.Enabled = glxPeriod.StatusId != (byte)BL.SYS.SYS_Status.Closed;
btnPostPeriod.Enabled = glxPeriod.StatusId != (byte)BL.SYS.SYS_Status.Closed;
Int64 endperiodid = glxPeriod.Id;
DB.SYS_Period startperiod = DataContext.EntitySystemContext.SYS_Period.Where(o => o.EndDate < glxPeriod.EndDate).OrderByDescending(o => o.EndDate).FirstOrDefault();
Int64 startperiodid = -1;
if (startperiod != null)
{
startperiodid = startperiod.Id;
}
List <DB.VW_Balance> balances = DataContext.ReadonlyContext.VW_Balance.Where(n => n.AccountMasterControlId == null & (n.PeriodId == startperiodid || n.PeriodId == endperiodid)).ToList();
var accounts = balances.Select(n => new { n.AccountId, n.AccountCode, n.AccountName, n.TypeFlag1, n.TypeCode }).Distinct().ToList();
foreach (var account in accounts)
{
var end = balances.Where(n => n.AccountId == account.AccountId && n.PeriodId == endperiodid).Select(n => n.BalanceAmount.Value).FirstOrDefault();
var start = balances.Where(n => n.AccountId == account.AccountId && n.PeriodId == startperiodid).Select(n => n.BalanceAmount.Value).FirstOrDefault();
decimal endbalance = end != null ? end : 0;
decimal startbalance = start != null ? start : 0;
balances.Add(new DB.VW_Balance()
{
AccountId = account.AccountId,
AccountCode = account.AccountCode,
AccountName = account.AccountName,
TypeFlag1 = account.TypeFlag1,
TypeCode = account.TypeCode,
PeriodCode = "MOVEMENT",
BalanceAmount = (endbalance) - (startbalance)
});
}
BindingSourceBalance.DataSource = balances;
}
}
catch (Exception ex)
{
if (CDS.Shared.Exception.UserInterfaceExceptionHandler.HandleException(ref ex))
{
throw ex;
}
}
}
public static System.Data.DataTable GetPayable(DB.SYS_Period period, string filter, DataContext dataContext)
{
String query = "";
if (BL.ApplicationDataContext.Instance.AccessGranted(BL.SEC.AccessCodes.FIAARE))
{
query = "SELECT Type, AccountId, Title, Reference, Description, Date, Balance, Period, Aging, TrackNumber, LineId FROM CDS_GLX.VW_PayableItems WHERE ((Type='OI') OR (Type='BBF' AND PeriodId=" + period.Id + ")) AND " + filter + " ORDER BY Date";
}
else
{
query = "SELECT Type, AccountId, Title, Reference, Description, Date, Balance, Period, Aging, TrackNumber, LineId FROM CDS_GLX.VW_PayableItems WHERE ((Type='OI') OR (Type='BBF' AND PeriodId=" + period.Id + ")) AND " + filter + ") AND SiteId=" + BL.ApplicationDataContext.Instance.LoggedInUser.DefaultSiteId + " ORDER BY Date";
}
System.Data.SqlClient.SqlDataAdapter adapter = ApplicationDataContext.Instance.GetAdapter("SELECT Type, AccountId, Title, Reference, Description, Date, Balance, Period, Aging, TrackNumber, LineId FROM CDS_GLX.VW_PayableItems WHERE ((Type='OI') OR (Type='BBF' AND PeriodId=" + period.Id + ")) AND " + filter + " ORDER BY Date");
System.Data.DataTable tblResult = new System.Data.DataTable();
adapter.Fill(tblResult);
return(tblResult);
}
internal static String Save(DB.SYS_Period entry, DataContext dataContext)
{
try
{
if (dataContext.EntitySystemContext.GetEntityState(entry) == System.Data.Entity.EntityState.Detached)
{
Save(new List <DB.SYS_Period>()
{
entry
}, dataContext);
}
}
catch (System.Data.Entity.Validation.DbEntityValidationException ex)
{
return(dataContext.PackageValidationException());
}
return("Success");
}
private void buildPeriods()
{
try
{
if (ddlMonth.SelectedIndex == -1)
{
Essential.BaseAlert.ShowAlert("Invalid selection", "Month name incorrectly spelt", Essential.BaseAlert.Buttons.Ok, Essential.BaseAlert.Icons.Information);
return;
}
periods = new List <DB.SYS_Period>();
DateTime start = new DateTime(Convert.ToInt32(txtFinancialYear.EditValue) - 1, ddlMonth.SelectedIndex + 1, 1);
for (int i = 0; i < 12; i++)
{
start = start.AddMonths(1);
DB.SYS_Period item = BL.SYS.SYS_Period.New;
item.StartDate = start;
item.EndDate = start.AddMonths(1).AddSeconds(-1);
item.Code = item.EndDate.ToString("yyyy-MM (MMM)").ToUpper();
item.Name = item.EndDate.ToString("MMMM yyyy");
item.FinancialYear = Convert.ToInt16(txtFinancialYear.EditValue);
item.FinancialQuarter = Convert.ToInt16((i / 3) + 1);
item.StatusId = (byte)BL.SYS.SYS_Status.Open;
periods.Add(item);
}
BindingSourcePeriod.DataSource = periods;
}
catch (Exception ex)
{
if (CDS.Shared.Exception.UserInterfaceExceptionHandler.HandleException(ref ex))
{
throw ex;
}
}
}
private void RunSystemChecks()
{
try
{
List <SystemCheck> systemChecks = new List <SystemCheck>();
// TODO: Do checks and balances to see if everything is ok! This may have to be done asynchronously
// ADMINISTRATIVE CHECKS
// Check 1: Check that there are no unposted entries - they must all be posted or rejected
if (DataContext.EntityAccountingContext.GLX_Header.Where(n => n.PeriodId == period.Id && n.StatusId == 50).Count() == 0)
{
systemChecks.Add(new SystemCheck()
{
ImageId = 0, CheckStatus = "All Entries have been signed off."
});
}
else
{
systemChecks.Add(new SystemCheck()
{
ImageId = 1, CheckStatus = "All Entries have not been signed off."
});
}
// Check 2: Check that ALL preceding periods have been closed - mandatory : 51 == CLOSED
DB.SYS_Period previousperiod = BL.SYS.SYS_Period.GetPreviousItem(period, DataContext);
// If there is no previous period
if (previousperiod == null)
{
systemChecks.Add(new SystemCheck()
{
ImageId = 0, CheckStatus = "All Preceding Period has been closed."
});
}
else if (previousperiod.StatusId == (byte)BL.SYS.SYS_Status.Closed)
{
systemChecks.Add(new SystemCheck()
{
ImageId = 0, CheckStatus = "All Preceding Period has been closed."
});
}
else
{
systemChecks.Add(new SystemCheck()
{
ImageId = 1, CheckStatus = "All Preceding Period has not been closed."
});
}
// SANITY CHECKS
// Check 1: Check that documents and ledger entries match up
// Check that ledger entries and balances match up (starting balance + movement = ending balance)
DB.SYS_Period startperiod = BL.SYS.SYS_Period.GetPreviousItem(period, DataContext);
decimal?startbalance = startperiod == null ? 0 : DataContext.EntityAccountingContext.GLX_History.Where(n => n.PeriodId == startperiod.Id).Sum(l => l.Amount);
decimal?movement = DataContext.ReadonlyContext.VW_Line.Where(n => n.PeriodCode == period.Code).ToList <DB.VW_Line>().Sum(l => l.Amount);
decimal?endbalance = DataContext.EntityAccountingContext.GLX_History.Where(n => n.PeriodId == period.Id).Sum(l => l.Amount);
if (Math.Abs((startbalance ?? 0) + (movement ?? 0) - (endbalance ?? 0)) == 0)
{
systemChecks.Add(new SystemCheck()
{
ImageId = 0, CheckStatus = "Movements for this period is correct."
});
}
else
{
systemChecks.Add(new SystemCheck()
{
ImageId = 1, CheckStatus = "Movements for this period isn't correct."
});
}
// Check 3: Check that ledger entries per account (amounts = zero / debit = credit)
if ((movement ?? 0) == 0)
{
systemChecks.Add(new SystemCheck()
{
ImageId = 0, CheckStatus = "Movements Zeroes for this period."
});
}
else
{
systemChecks.Add(new SystemCheck()
{
ImageId = 1, CheckStatus = "Movements doesn't Zeroes for this period."
});
}
// Check 4: Check that ledger balances balance (amounts = zero)
BindingSourceChecks.DataSource = systemChecks;
grdChecks.RefreshDataSource();
//Disables button if there are errors
btnClosePeriod.Enabled = !(systemChecks.Where(n => n.ImageId > 0).Count() > 0);
}
catch (Exception ex)
{
if (CDS.Shared.Exception.UserInterfaceExceptionHandler.HandleException(ref ex))
{
throw ex;
}
}
}
public ClosePeriodDialogue(Int64 PeriodId)
{
InitializeComponent();
period = BL.SYS.SYS_Period.Load(PeriodId, DataContext);
}
public static DB.SYS_Period GetPreviousItem(DB.SYS_Period period, DataContext dataContext)
{
return(dataContext.EntitySystemContext.SYS_Period.OrderByDescending(o => o.EndDate).FirstOrDefault(n => n.EndDate < period.EndDate && n.EndDate != period.EndDate));
}
public static DB.SYS_Period GetNextItem(DB.SYS_Period period, DataContext dataContext)
{
return(dataContext.EntitySystemContext.SYS_Period.OrderBy(o => o.EndDate).FirstOrDefault(n => n.EndDate > period.EndDate && n.EndDate != period.EndDate));
}
