public static void UpdateLedgerAccountBalance(DB.GLX_Header glx_header, DataContext dataContext)
{
foreach (DB.GLX_Line line in glx_header.GLX_Line)
{
dataContext.EntityAccountingContext.ExecuteSqlCommand(String.Format("EXEC CDS_SYS.spUpdateAccountBalance {0},{1},{2},{3},{4},{5}", line.EntityId, glx_header.ReferencePeriodId, glx_header.PeriodId, line.AgingId, line.Amount, ApplicationDataContext.Instance.LoggedInUser.DefaultSiteId));
}
}
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);
}
internal static String Save(DB.GLX_Header entry, DataContext dataContext)
{
try
{
if (dataContext.EntityAccountingContext.GetEntityState(entry) == System.Data.Entity.EntityState.Detached)
{
dataContext.EntityAccountingContext.GLX_Header.Add(entry);
}
}
catch (System.Data.Entity.Validation.DbEntityValidationException ex)
{
return(dataContext.PackageValidationException());
}
return("Success");
}
public static void InsertProfitDistributionEntries(DB.GLX_Header glx_header, DataContext dataContext)
{
decimal total = 0;
decimal currentProfitTotal = 0;
decimal currentDistributionTotal = 0;
foreach (DB.GLX_Line line in glx_header.GLX_Line)
{
if (ApplicationDataContext.Instance.ProfitDistributionEntriesRequired.Contains(line.EntityId))
{
total += line.Amount;
}
//Total of current headers Profit Amount
if (line.EntityId == ApplicationDataContext.Instance.SiteAccounts.Profit.EntityId)
{
currentProfitTotal += line.Amount;
}
//Total of current headers Distribution Amount
if (line.EntityId == ApplicationDataContext.Instance.SiteAccounts.DistributedReserves.EntityId)
{
currentDistributionTotal += line.Amount;
}
}
if ((total - currentDistributionTotal) != 0 || (total + currentProfitTotal) != 0)
{
// DISTRIBUTION ENTRY
DB.GLX_Line glx_line_distribution = GLX.GLX_Line.New;
glx_line_distribution.EntityId = ApplicationDataContext.Instance.SiteAccounts.DistributedReserves.EntityId;
//CURRENT
glx_line_distribution.AgingId = 1;
glx_line_distribution.Amount = total - currentDistributionTotal;
glx_line_distribution.CenterId = GLX_Account.LoadByEntityId(glx_line_distribution.EntityId, dataContext).CenterId;
glx_header.GLX_Line.Add(glx_line_distribution);
// PROFIT ENTRY
DB.GLX_Line glx_line_profit = GLX.GLX_Line.New;
glx_line_profit.EntityId = ApplicationDataContext.Instance.SiteAccounts.Profit.EntityId;
//CURRENT
glx_line_profit.AgingId = 1;
glx_line_profit.Amount = -(total + currentProfitTotal);
glx_line_profit.CenterId = GLX_Account.LoadByEntityId(glx_line_profit.EntityId, dataContext).CenterId;
glx_header.GLX_Line.Add(glx_line_profit);
}
}
/// <summary>
/// Bind a blank new record to the form.
/// </summary>
/// <remarks>Created: Theo Crous 17/11/2011</remarks>
protected override void OnNewRecord()
{
try
{
base.OnNewRecord();
glxHeader = BL.GLX.GLX_Header.New;
glxLines = new List <DB.GLX_Line>();
glxHeader.GLX_Line = glxLines;
ServerModeSourcePeriod.QueryableSource = DataContext.EntitySystemContext.SYS_Period.Where(n => n.StatusId != 51).OrderByDescending(n => n.StartDate);
glxHeader.TrackId = -1;
}
catch (Exception ex)
{
if (CDS.Shared.Exception.UserInterfaceExceptionHandler.HandleException(ref ex))
{
throw ex;
}
}
}
/// <summary>
/// Open an Header record from the database.
/// </summary>
/// <param name="Id">The id (primary key) of the Header to open.</param>
/// <remarks>Created: Theo Crous 01/12/2011</remarks>
public override void OpenRecord(Int64 Id)
{
try
{
base.OpenRecord(Id);
AllowArchive = false;
Int64 headerId = DataContext.ReadonlyContext.VW_Line.Where(n => n.Id == (int)Id).Select(n => n.HeaderId).FirstOrDefault();
glxHeader = BL.GLX.GLX_Header.Load(headerId, DataContext, new List <String>()
{
"GLX_Line"
});
ServerModeSourcePeriod.QueryableSource = DataContext.EntitySystemContext.SYS_Period.Where(n => n.StatusId != 51 || n.Id == glxHeader.PeriodId).OrderByDescending(n => n.StartDate);
AllowSave = false;
}
catch (Exception ex)
{
if (CDS.Shared.Exception.UserInterfaceExceptionHandler.HandleException(ref ex))
{
throw ex;
}
}
}
protected override bool SaveSuccessful()
{
try
{
using (new CDS.Client.Desktop.Essential.UTL.WaitCursor())
{
this.OnSaveRecord();
if (!IsValid)
{
CDS.Client.Desktop.Essential.BaseAlert.ShowAlert("Error", "Please ensure that all Periods, Contra Accounts and References have been filled in.", Essential.BaseAlert.Buttons.Ok, Essential.BaseAlert.Icons.Error);
return(false);
}
if (CDS.Client.Desktop.Essential.BaseAlert.ShowAlert("Process Bulk Entries", "You are about to create multiple entries.\nAre you certain you wish to continue?", CDS.Client.Desktop.Essential.BaseAlert.Buttons.OkCancel, CDS.Client.Desktop.Essential.BaseAlert.Icons.Warning) == System.Windows.Forms.DialogResult.OK)
{
try
{
using (TransactionScope transaction = DataContext.GetTransactionScope())
{
// Create Records for all entries...
foreach (BulkEntry entry in bulkentries)
{
DB.GLX_Header glx_header = BL.GLX.GLX_Header.New;
glx_header.Date = entry.Date.Value;
glx_header.Description = entry.Description;
glx_header.Reference = entry.Reference;
glx_header.PeriodId = DataContext.ReadonlyContext.VW_Period.Where(n => glx_header.Date >= n.StartDate && glx_header.Date < n.EndDate).Select(n => n.Id).FirstOrDefault(); //entry.PeriodId.Value;
glx_header.ReferencePeriodId = glx_header.PeriodId;
glx_header.PostedDate = BL.ApplicationDataContext.Instance.ServerDateTime.Date;
glx_header.TrackId = ((DataAccessLayer.DB.GLX_Header)BindingSourceHeader.DataSource).TrackId;
glx_header.StatusId = chkAutoPost.Checked ? (byte)BL.SYS.SYS_Status.Posted : (byte)BL.SYS.SYS_Status.Unposted;
glx_header.JournalTypeId = (byte)BL.GLX.GLX_JournalType.BulkJournal;
// TOTAL
DB.GLX_Line glx_line_total = BL.GLX.GLX_Line.New;
glx_line_total.EntityId = (Int64)ddlAccount.EditValue;// DataAccessLayer.ApplicationContext.Instance.CompanySite.glx_Debtors;
//CURRENT
glx_line_total.AgingId = Convert.ToByte(ddlAging.EditValue);
glx_line_total.Amount = -entry.Inclusive.Value;
glx_line_total.CenterId = DataContext.ReadonlyContext.VW_Account.Where(n => n.Id == glx_line_total.EntityId).Select(n => n.CenterId).FirstOrDefault();
// EXCL
DB.GLX_Line glx_line_excl = BL.GLX.GLX_Line.New;
glx_line_excl.EntityId = entry.AccountId.Value;// DataAccessLayer.ApplicationContext.Instance.CompanySite.glx_Debtors;
//CURRENT
glx_line_excl.AgingId = entry.AgingId.Value;
glx_line_excl.Amount = entry.Exclusive.Value;
glx_line_excl.CenterId = DataContext.ReadonlyContext.VW_Account.Where(n => n.Id == glx_line_excl.EntityId).Select(n => n.CenterId).FirstOrDefault();
glx_header.GLX_Line.Add(glx_line_total);
glx_header.GLX_Line.Add(glx_line_excl);
if (entry.Inclusive != entry.Exclusive)
{
// TAX
DB.GLX_Line glx_line_tax = BL.GLX.GLX_Line.New;
glx_line_tax.EntityId = DataContext.EntityAccountingContext.GLX_Tax.Where(n => n.Id == entry.TaxId.Value).Select(n => n.EntityId.Value).FirstOrDefault(); //DataAccessLayer.ApplicationContext.Instance.CompanySite.glx_VatAccount;
//CURRENT
glx_line_tax.AgingId = entry.AgingId.Value;
glx_line_tax.Amount = (entry.Inclusive.Value - entry.Exclusive.Value);
glx_line_tax.CenterId = DataContext.ReadonlyContext.VW_Account.Where(n => n.Id == glx_line_tax.EntityId).Select(n => n.CenterId).FirstOrDefault();
glx_header.GLX_Line.Add(glx_line_tax);
}
//TODO: Need to check that this works
//DataContext.EntityAccountingContext.BeginTransaction();
glx_header.JournalTypeId = (byte)BL.GLX.GLX_JournalType.Journal;
if (!glx_header.IsYearendHeader)
{
BL.GLX.GLX_Header.InsertProfitDistributionEntries(glx_header, DataContext);
}
DB.SYS_Tracking tracking = BL.SYS.SYS_Tracking.New;
BL.EntityController.SaveSYS_Tracking(tracking, DataContext);
DataContext.SaveChangesEntitySystemContext();
glx_header.TrackId = tracking.Id;
BL.EntityController.SaveGLX_Header(glx_header, DataContext);
DataContext.SaveChangesEntityAccountingContext();
if (chkAutoPost.Checked)
{
BL.GLX.GLX_Header.UpdateLedgerAccountBalance(glx_header, DataContext);
}
}
DataContext.CompleteTransaction(transaction);
}
DataContext.EntitySystemContext.AcceptAllChanges();
DataContext.EntityAccountingContext.AcceptAllChanges();
return(true);
}
catch (Exception ex)
{
DataContext.EntitySystemContext.RejectChanges();
DataContext.EntityAccountingContext.RejectChanges();
HasErrors = true;
if (CDS.Shared.Exception.UserInterfaceExceptionHandler.HandleException(ref ex))
{
throw ex;
}
return(false);
}
}
else
{
return(false);
}
}
}
catch (Exception ex)
{
HasErrors = true;
if (CDS.Shared.Exception.UserInterfaceExceptionHandler.HandleException(ref ex))
{
throw ex;
}
return(false);
}
}
private void btnPostPeriod_Click(object sender, EventArgs e)
{
try
{
//TODO: Fix this
if (!ValidateBeforeSave())
{
return;
}
if (CDS.Client.Desktop.Essential.BaseAlert.ShowAlert("Create Entries", "You are about to Post Entries are you sure you want to continue", Essential.BaseAlert.Buttons.OkCancel, Essential.BaseAlert.Icons.Information) != System.Windows.Forms.DialogResult.OK)
{
return;
}
DB.GLX_Header glxHeader = BL.GLX.GLX_Header.New;
//glxHeader.Date = BL.ApplicationContext.Instance.ServerDateTime;
//Changed so that it is the "1st" transacrtion of the following month
glxHeader.Date = period.EndDate.AddMinutes(1);
glxHeader.Description = string.Format("Post Period Entries for {0}.", period.Title);
glxHeader.Reference = String.Format("Year-end {0}", period.Code);
//TODO: Chekc that this works
glxHeader.PeriodId = BL.SYS.SYS_Period.GetNextItem(period, DataContext).Id;
glxHeader.ReferencePeriodId = BL.SYS.SYS_Period.GetNextItem(period, DataContext).Id;
glxHeader.PostedDate = BL.ApplicationDataContext.Instance.ServerDateTime;
glxHeader.JournalTypeId = (byte)BL.GLX.GLX_JournalType.Posting;
glxHeader.TrackId = -1;
glxHeader.IsYearendHeader = true;
//POASTED
glxHeader.StatusId = (byte)BL.SYS.SYS_Status.Posted;
decimal profitTotal = 0;
foreach (DB.VW_PostPeriodFigures line in BindingSource)
{
DB.GLX_Line glx_line = BL.GLX.GLX_Line.New;
glx_line.EntityId = line.EntityId;// BL.ApplicationContext.Instance.CompanySite.glx_Debtors;
//CURRENT
glx_line.AgingId = 1;
glx_line.Amount = line.Amount.Value;
glx_line.CenterId = BL.GLX.GLX_Account.LoadByEntityId(glx_line.EntityId, DataContext).CenterId;
//Total for profit entry
profitTotal += line.Amount.Value;
glxHeader.GLX_Line.Add(glx_line);
}
//PROFIT ENTRY
DB.GLX_Line glx_line_profit = BL.GLX.GLX_Line.New;
glx_line_profit.EntityId = (Int64)ddlAccount.EditValue;
//CURRENT
glx_line_profit.AgingId = 1;
glx_line_profit.Amount = -profitTotal;
glx_line_profit.CenterId = BL.GLX.GLX_Account.LoadByEntityId(glx_line_profit.EntityId, DataContext).CenterId;
glxHeader.GLX_Line.Add(glx_line_profit);
try
{
using (TransactionScope transaction = DataContext.GetTransactionScope())
{
DB.SYS_Tracking sysTracking = BL.SYS.SYS_Tracking.New;
BL.EntityController.SaveSYS_Tracking(sysTracking, DataContext);
DataContext.SaveChangesEntitySystemContext();
glxHeader.TrackId = sysTracking.Id;
BL.EntityController.SaveGLX_Header(glxHeader, DataContext);
DataContext.SaveChangesEntityAccountingContext();
BL.GLX.GLX_Header.UpdateLedgerAccountBalance(glxHeader, DataContext);
DataContext.CompleteTransaction(transaction);
}
DataContext.EntitySystemContext.AcceptAllChanges();
DataContext.EntityAccountingContext.AcceptAllChanges();
}
catch (Exception ex)
{
DataContext.EntitySystemContext.RejectChanges();
DataContext.EntityAccountingContext.RejectChanges();
if (CDS.Shared.Exception.UserInterfaceExceptionHandler.HandleException(ref ex))
{
throw ex;
}
}
this.DialogResult = System.Windows.Forms.DialogResult.OK;
this.Close();
}
catch (Exception ex)
{
if (CDS.Shared.Exception.UserInterfaceExceptionHandler.HandleException(ref ex))
{
throw ex;
}
}
}
public static List <DB.GLX_Header> CreateReceipts(DataRow[] rows, Dictionary <Int64, String> accountsColumns, Dictionary <string, GLX.PaymentAccount> paymentAccounts, DataContext dataContext)
{
List <DB.GLX_Header> headers = new List <DB.GLX_Header>();
foreach (DataRow row in rows)
{
// Make a new header entry
DB.GLX_Header header = GLX.GLX_Header.New;
//GET PERIOD FROM DATE
header.PeriodId = SYS.SYS_Period.Load(Convert.ToDateTime(row["colEntryDate"]), dataContext).Id;
header.Date = Convert.ToDateTime(row["colEntryDate"]);
header.PostedDate = Convert.ToDateTime(row["colEntryDate"]).Date;
header.Reference = Convert.ToString(row["colEntryReference"]);
header.StatusId = (byte)SYS.SYS_Status.Posted;
if (Convert.ToDecimal(row["colTotal"]) >= 0)
{
header.Description = "DEBTOR PAYMENT RECEIVED";
header.JournalTypeId = (byte)GLX.GLX_JournalType.Receipts;
}
else
{
header.Description = "DEBTOR PAYMENT REVERSAL";
header.JournalTypeId = (byte)GLX.GLX_JournalType.Reversal;
}
if (row["TrackNumber"] != DBNull.Value && Convert.ToInt64(row["TrackNumber"]) != -1)
{
header.TrackId = Convert.ToInt64(row["TrackNumber"]);
}
else
{
header.TrackId = -1;
}
// Add the lines
DB.GLX_Line linedebtor = GLX.GLX_Line.New;
linedebtor.EntityId = Convert.ToInt64(row["AccountId"]);
header.ReferencePeriodId = header.PeriodId;
linedebtor.AgingId = Convert.ToByte(row["colEntryAging"]);
linedebtor.Amount = 0;
header.GLX_Line.Add(linedebtor);
Dictionary <Int64, Decimal> taxAccounts = new Dictionary <Int64, Decimal>();
foreach (var pair in accountsColumns)
{
GLX.PaymentAccount pa = paymentAccounts[pair.Value];
Decimal val = Convert.ToDecimal(row[pair.Value]);
if (val != 0)
{
DB.GLX_Line line = GLX.GLX_Line.New;
header.GLX_Line.Add(line);
line.EntityId = pair.Key;
line.AgingId = !dataContext.EntityAccountingContext.GLX_Account.Where(n => n.Id.Equals(pair.Key)).Select(n => n.AgingAccount).FirstOrDefault() ? (byte)1 : linedebtor.AgingId;
DB.GLX_Tax taxType = dataContext.EntityAccountingContext.GLX_Tax.FirstOrDefault(n => n.Id == pa.TaxId);
if (taxType != null && taxType.Percentage != 0)
{
line.Amount = Math.Round(val / (1 + taxType.Percentage), 2, MidpointRounding.AwayFromZero);
taxAccounts.Add(taxType.EntityId.Value, Math.Round(val - line.Amount, 2, MidpointRounding.AwayFromZero));
}
else
{
line.Amount = val;
}
linedebtor.Amount -= val; // Credit the debtor with the indicated amount
}
}
foreach (var account in taxAccounts.GroupBy(account => account.Key).Select(amount => new { id = amount.Key, amount = amount.Sum(account => account.Value) }))
{
DB.GLX_Line line = GLX.GLX_Line.New;
line.EntityId = account.id;
line.AgingId = linedebtor.AgingId;
line.Amount = account.amount;
header.GLX_Line.Add(line);
}
headers.Add(header);
}
return(headers);
}
public static void Approve(DB.GLX_Header glx_header, DataContext dataContext)
{
GLX.GLX_Header.UpdateLedgerAccountBalance(glx_header, dataContext);
}
protected override bool SaveSuccessful()
{
try
{
using (new CDS.Client.Desktop.Essential.UTL.WaitCursor())
{
this.OnSaveRecord();
if (!IsValid)
{
return(false);
}
foreach (BulkPaymentEntry mainLine in BulkPaymentEntries)
{
Int64 trackingNumber = -1;
// Make a new header entry
DB.GLX_Header header = BL.GLX.GLX_Header.New;
header.TrackId = -1;
header.PeriodId = mainLine.PeriodId.Value;
header.ReferencePeriodId = mainLine.PeriodId.Value;
header.Date = mainLine.Date.Value;
header.PostedDate = BL.ApplicationDataContext.Instance.ServerDateTime.Date;
header.Reference = mainLine.Reference;
header.Description = mainLine.Description;
header.StatusId = 52;
if (mainLine.Amount >= 0)
{
// header.Description = "CREDITOR PAYMENT RECEIVED";
header.JournalTypeId = (byte)BL.GLX.GLX_JournalType.Payment;
}
else
{
// header.Description = "CREDITOR PAYMENT REVERSAL";
header.JournalTypeId = (byte)BL.GLX.GLX_JournalType.Reversal;
}
//Double check if this has a use
if (!trackingNumber.Equals(-1))
{
header.TrackId = trackingNumber;
}
//Loop through INV or C/N or BBF (Agings)
foreach (var agingAmount in mainLine.BulkPaymentItems.Where(n => n.Checked.Equals(true)).GroupBy(g => g.AgingId).Select(l => new { AgingId = l.Key, Balance = l.Sum(i => i.Balance) }))
{
//DEBTOR LINE
DB.GLX_Line linecreditor = BL.GLX.GLX_Line.New;
//TODO: ACCOUNT NOT LOADING ON subLine
linecreditor.EntityId = mainLine.AccountId.Value;
linecreditor.AgingId = agingAmount.AgingId;
linecreditor.Amount = -agingAmount.Balance;
header.GLX_Line.Add(linecreditor);
}
//BANK LINE
DB.GLX_Line linebank = BL.GLX.GLX_Line.New;
linebank.EntityId = Convert.ToInt64(ddlAccount.EditValue);
linebank.AgingId = 1;
linebank.Amount = mainLine.Amount;
header.GLX_Line.Add(linebank);
//SETTLEMENT DISCOUNT TAX LINE
DB.GLX_Line linesettlementtax = BL.GLX.GLX_Line.New;
DB.GLX_Tax taxType = DataContext.EntityAccountingContext
.GLX_Tax.FirstOrDefault(n => n.Id == PaymentAccounts
.Where(nn => nn.AccountId.Equals(((DB.GLX_Account)ddlSettlementAccount.GetSelectedDataRow()).Id))
.Select(nn => nn.TaxId).FirstOrDefault());
if (taxType != null && taxType.Percentage != 0)
{
linesettlementtax.Amount = mainLine.Settlement * (1 - taxType.Percentage);
}
else
{
linesettlementtax.Amount = mainLine.Settlement;
}
header.GLX_Line.Add(linesettlementtax);
//SETTLEMENT DISCOUNT LINE
DB.GLX_Line linesettlement = BL.GLX.GLX_Line.New;
linesettlement.EntityId = Convert.ToInt64(ddlSettlementAccount.EditValue);
linesettlement.AgingId = 1;
linesettlement.Amount = mainLine.Settlement - linesettlementtax.Amount;
header.GLX_Line.Add(linesettlement);
//Change the subLines's tracking number to the new tracking number
foreach (var subLine in mainLine.BulkPaymentItems.Where(n => n.Checked.Equals(true)))
{
BL.GLX.GLX_Header.UpdateTrackNumber(subLine.HeaderId, trackingNumber, DataContext);
}
}
return(true);
}
}
catch (Exception ex)
{
HasErrors = true;
if (CDS.Shared.Exception.UserInterfaceExceptionHandler.HandleException(ref ex))
{
throw ex;
}
return(false);
}
}
