public static IEnumerable AccountsNotPaidForWeeks(int weeks)
{
var db = new LA_Entities();
var sql = @"
declare @cutoffDate as datetime
set @cutoffDate = DATEADD(week, {0}*-1, GETDATE());
print @cutoffDate
select np.Account_Id
from (
select lp.*,
case a.PayMonthly
when 0 then DATEADD(day, (a.PaymentPeriod*7), lp.LastPayment)
else DATEADD(month, a.PaymentPeriod, lp.LastPayment)
end as NextPaymentWasDue
from (
select p.Account_Id,
max(p.[Timestamp]) as LastPayment
from Payments p
where p.Amount > 0
group by p.Account_Id) lp
inner join Accounts a on a.Id = lp.Account_Id) np
where np.NextPaymentWasDue < @cutoffDate
order by np.NextPaymentWasDue asc
";
sql = string.Format(sql, weeks);
var customerIds = db.Database.SqlQuery <int>(sql).ToList();
return(customerIds);
}
private void ClearFields()
{
var db = new LA_Entities();
if (_currentCustomerID > 0)
{
Customer c = db.Customers.Find(_currentCustomerID);
c.LockedByUser = "";
db.SaveChanges();
}
_currentCustomerID = 0;
txtAddress.Text = "";
txtForename.Text = "";
txtMaxLoan.Text = "";
txtNotes.Text = "";
txtPhoneNumber.Text = "";
txtPostCode.Text = "";
txtStartDate.Text = "";
txtSurname.Text = "";
cmbCollectionDay.SelectedIndex = -1;
cmbCollector.SelectedIndex = -1;
dgAccounts.DataSource = null;
btnSearch.Enabled = true;
ClearErrors();
btnDelete.Enabled = false;
btnNewAccount.Enabled = false;
BtnSave.Enabled = false;
btnSearch.Enabled = true;
txtSurname.Focus();
}
public static IEnumerable <int> GetCustomerIdsByDebt(int debt)
{
var db = new LA_Entities();
var sql = @"
select a.Customer_Id
from Accounts a
inner
join (
select a.Id,
sum(p.Amount) as Paid
from AccountStatusChanges astc
inner
join (
select Account_Id,
max(Timestamp) as ts
from AccountStatusChanges
group by Account_Id) x on x.Account_Id = astc.Account_Id and x.ts = astc.Timestamp
inner join AccountStatus ast on ast.Id = astc.AccountStatus_Id
inner join Accounts a on a.Id = astc.Account_Id
inner join Payments p on p.Account_Id = a.Id
where ast.Status = 'Created'
group by a.Id) amtPaid on amtPaid.Id = a.Id
group by a.Customer_Id
having sum(a.GrossValue - amtPaid.Paid) >= {0}
order by sum(a.GrossValue - amtPaid.Paid) desc
";
sql = string.Format(sql, debt);
var customerIds = db.Database.SqlQuery <int>(sql).ToList();
return(customerIds);
}
private void cntAccount_Load(object sender, EventArgs e)
{
_db = new LA_Entities();
dtNextPayment.CustomFormat = @"ddd: dd-MMM-yyyy";
ClearAccount();
txtInvoiceCode.Focus();
}
private void cntCustomer_Load(object sender, EventArgs e)
{
var db = new LA_Entities();
cmbCollector.DataSource = (from c in db.Collectors select c).ToList();
cmbCollector.DisplayMember = "CollectorName";
cmbCollector.ValueMember = "Id";
ClearFields();
}
public void ShowCustomer(int customerID)
{
var db = new LA_Entities();
var customer = db.Customers.Find(customerID);
//Is this customer locked by someone else?
if (!String.IsNullOrEmpty(customer.LockedByUser) && customer.LockedByUser != Properties.Settings.Default.User)
{
//Is it to be unlocked?
if (MessageBox.Show("This customer is currently locked by " + customer.LockedByUser + ". Do you want to unlock it?", "Customer Locked", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
{
customer.LockedByUser = "";
db.SaveChanges();
}
else
{
if (EvBackToMain != null)
{
EvBackToMain();
}
return;
}
}
_currentCustomerID = customer.Id;
txtSurname.Text = customer.Surname;
txtStartDate.Text = customer.StartDate.ToString("dd MMMM yyyy");
txtPostCode.Text = customer.PostCode;
txtPhoneNumber.Text = customer.PhoneNumber;
txtNotes.Text = customer.Notes;
txtMaxLoan.Text = customer.Maxloan.ToString(CultureInfo.InvariantCulture);
txtForename.Text = customer.Forename;
txtAddress.Text = customer.Address;
cmbCollectionDay.SelectedIndex = customer.PreferredDay;
cmbCollector.SelectedValue = customer.Collector.Id;
customer.LockedByUser = Properties.Settings.Default.User;
db.SaveChanges();
dgAccounts.AutoGenerateColumns = false;
var accountsToShow = customer.Accounts.ToList().Where(account => !account.CurrentStatus.IsDeleted).ToList();
dgAccounts.DataSource = accountsToShow;
btnDelete.Enabled = true;
btnNewAccount.Enabled = true;
BtnSave.Enabled = true;
btnSearch.Enabled = false;
}
private void btnDelete_Click(object sender, EventArgs e)
{
var db = new LA_Entities();
var name = txtForename.Text.Trim() + " " + txtSurname.Text.Trim();
if (MessageBox.Show("Delete " + name, "Confirm Deletion", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
{
return;
}
//Get the customer
Customer cust = db.Customers.Find(_currentCustomerID);
//Check Accounts
bool canDelete = true;
foreach (Account a in cust.Accounts)
{
if (a.CurrentStatus.IsCreated && a.Outstanding > 0)
{
canDelete = false;
}
}
if (!canDelete)
{
MessageBox.Show("This customer has outstanding accounts and, therefore, cannot be deleted.", "No Deletion", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
//Delete
cust.IsDeleted = true;
db.SaveChanges();
if (EvShowStatusText != null)
{
EvShowStatusText(cust.FullName + " deleted.");
}
ClearFields();
}
private void SaveCustomer()
{
var db = new LA_Entities();
Customer customer;
if (_currentCustomerID == 0)
{
customer = new Customer();
db.Customers.Add(customer);
}
else
{
customer = db.Customers.Find(_currentCustomerID);
}
if (txtMaxLoan.Text.Trim().Length == 0)
{
txtMaxLoan.Text = "0";
}
customer.Address = txtAddress.Text.Trim();
customer.Collector_Id = ((Collector)cmbCollector.SelectedItem).Id;
customer.Forename = txtForename.Text.Trim();
customer.Maxloan = int.Parse(txtMaxLoan.Text);
customer.Notes = txtNotes.Text.Trim();
customer.PhoneNumber = txtPhoneNumber.Text.Trim();
customer.PostCode = txtPostCode.Text.Trim();
customer.PreferredDay = cmbCollectionDay.SelectedIndex;
customer.Surname = txtSurname.Text.Trim();
db.SaveChanges();
_currentCustomerID = customer.Id;
if (EvShowStatusText != null)
{
EvShowStatusText("Customer: '" + customer.Surname + "' Saved");
}
}
private void btnImportDatabase_Click(object sender, EventArgs e)
{
try
{
if (txtEncryptedDatabaseFile.Text.Trim().Length == 0)
{
return;
}
if (txtKey.Text.Trim().Length == 0)
{
return;
}
//*****************************************************************************
//Does DB Exist?
var canConnectToDb = true;
var db = new LA_Entities();
var serverName = db.Database.Connection.DataSource;
var databaseName = db.Database.Connection.Database;
var connectionString = string.Format("Server={0};Database=master;Trusted_Connection=True;", serverName);
var sqlConnection = new SqlConnection(connectionString);
try
{
//var collectors = (from c in db.Collectors select c).ToList();
var sqlFindDb = string.Format("select count(*) from master.dbo.sysdatabases where name = '{0}'", databaseName);
var da = new SqlDataAdapter();
var sqlCommand = new SqlCommand(sqlFindDb, sqlConnection);
da.SelectCommand = sqlCommand;
var ds = new DataSet();
sqlConnection.Open();
da.Fill(ds);
sqlConnection.Close();
var sCount = ds.Tables[0].Rows[0][0].ToString();
int count;
if (!int.TryParse(sCount, out count))
{
canConnectToDb = false;
}
if (count == 0)
{
canConnectToDb = false;
}
}
catch (Exception)
{
canConnectToDb = false;
}
//Remove existing Db
if (canConnectToDb)
{
var sqlDropDatabase = string.Format("ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE{1}drop database [{0}]", databaseName, Environment.NewLine);
var sqlCommand = new SqlCommand(sqlDropDatabase, sqlConnection);
sqlConnection.Open();
var executeNonQuery = sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
//Create blank DB
var sqlCreateDatabase = string.Format("create database [{0}]", databaseName);
var command = new SqlCommand(sqlCreateDatabase, sqlConnection);
sqlConnection.Open();
command.ExecuteNonQuery();
//Decrypt
var encryptedFilepath = txtEncryptedDatabaseFile.Text.Trim();
var key = txtKey.Text.Trim();
var decryptFilePath = Symmetric.DecryptFile(encryptedFilepath, key, Path.GetTempPath());
//Run script to rebuild db
//var script = File.ReadAllText(scriptFileName);
//SqlCommand command;
sqlConnection.Open();
var scriptBatch = new StringBuilder();
using (var reader = new StreamReader(decryptFilePath))
{
while (true)
{
var line = reader.ReadLine();
if (line == null)
{
break;
}
//if (line.ToUpper().Contains(" ANSI_NULLS ")) continue;
if (line.TrimStart().StartsWith("--"))
{
continue;
}
if (line.Equals("go", StringComparison.InvariantCultureIgnoreCase))
{
var s = scriptBatch.ToString();
command = new SqlCommand(s, sqlConnection);
command.ExecuteNonQuery();
scriptBatch.Clear();
}
else
{
if (line.Trim().Length > 0)
{
scriptBatch.AppendLine(line);
}
}
}
}
if (scriptBatch.ToString().Trim().Length > 0)
{
command = new SqlCommand(scriptBatch.ToString(), sqlConnection);
command.ExecuteNonQuery();
}
sqlConnection.Close();
MessageBox.Show(@"New Database Loaded");
Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, @"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/*
* public string Agreement(int accountID)
* {
* string pdfPath = "";
*
* var document = new Document();
* try
* {
* var nl = Environment.NewLine;
* pdfPath = ReportHelper.CreateDoc(ref document, false);
* document.Open();
*
* //Define Fonts
* var fontNormal = FontFactory.GetFont(FontFactory.HELVETICA, 8, Font.NORMAL);
* var fontNormalBold = FontFactory.GetFont(FontFactory.HELVETICA, 8, Font.BOLD);
* var fontSmall = FontFactory.GetFont(FontFactory.HELVETICA, 6, Font.NORMAL);
*
* var account = _db.Accounts.Find(accountID);
* if (account == null) throw new Exception("Can't find Account");
*
* //Pages
* for (var page = 1; page <= 2; page++)
* {
* ReportHelper.AddLine(document, fontNormal, "Pre Contract Fixed Sum Loan Agreement regulated by the Consumer Credit Act 1974");
*
* //Define Table
* var tblTop = new PdfPTable(4);
* //tblTop.BorderWidth = 0;
* //tblTop.Border = Rectangle.NO_BORDER;
* //tblTop.AutoFillEmptyCells = true;
* //tblTop.Cellpadding = 1;
*
* float[] headerwidths = { 5, 10, 5, 10 };
* tblTop.SetWidths(headerwidths);
*
* const string c1 = "The Lenders";
* var c2 = "R & M Donaldson" + Environment.NewLine + "P.O. Box 11, Heaton, NE7 7YW" + nl + "Telephone 0191 2811112";
* var c3 = "The Borrower" + nl + "Address";
* var c4 = account.Customer.FullName + nl + account.Customer.Address;
*
* ReportHelper.AddCell(tblTop, c1, 1, Element.ALIGN_RIGHT, fontNormalBold, Rectangle.NO_BORDER, 8);
* ReportHelper.AddCell(tblTop, c2, 1, Element.ALIGN_LEFT, fontNormal, Rectangle.NO_BORDER, 8);
* ReportHelper.AddCell(tblTop, c3, 1, Element.ALIGN_RIGHT, fontNormalBold, Rectangle.NO_BORDER, 8);
* ReportHelper.AddCell(tblTop, c4, 1, Element.ALIGN_LEFT, fontNormal, Rectangle.NO_BORDER, 8);
*
* ReportHelper.AddCell(tblTop, "", 1, Element.ALIGN_RIGHT, fontNormal, Rectangle.NO_BORDER, 8);
* ReportHelper.AddCell(tblTop, "", 1, Element.ALIGN_LEFT, fontNormal, Rectangle.NO_BORDER, 8);
* ReportHelper.AddCell(tblTop, "Telephone", 1, Element.ALIGN_RIGHT, fontNormalBold, Rectangle.NO_BORDER, 8);
* ReportHelper.AddCell(tblTop, account.Customer.PhoneNumber, 1, Element.ALIGN_LEFT, fontNormal, Rectangle.NO_BORDER, 8);
*
* //Define Table
* var tblBody = new PdfPTable(2);
* //tblBody.BorderWidth = 0;
* //tblBody.Border = Rectangle.NO_BORDER;
* //tblBody.AutoFillEmptyCells = false;
* //tblBody.Cellpadding = 1;
*
* ReportHelper.AddCell(tblBody, "Key Financial Information", 1, Element.ALIGN_LEFT, fontNormalBold, Rectangle.RECTANGLE, 6);
* ReportHelper.AddCell(tblBody, "Terms and Conditions", 1, Element.ALIGN_LEFT, fontNormalBold, Rectangle.RECTANGLE, 6);
*
* //Define Table
* var tblLeft = new PdfPTable(2);
* //tblLeft.BorderWidth = 0;
* //tblLeft.Border = Rectangle.NO_BORDER;
* //tblLeft.AutoFillEmptyCells = true;
* //tblLeft.Cellpadding = 0;
* //tblLeft.Cellpadding = 0;
*
* var period = "";
* period = account.PayMonthly ? "month" : "week";
* var numOfPayments = (account.PlannedNumberOfPayments * account.PaymentPeriod);
*
* //Loans paid off
* var lpoText = "Amount required to settle existing loan(s)";
* double paidOffAmount = 0;
* var paidOffLoans = account.GetPayOffPayments();
* if (paidOffLoans.Count > 0)
* {
* lpoText += nl + "(";
* foreach (var p in paidOffLoans)
* {
* lpoText += p.Account.InvoiceCode + ", ";
* paidOffAmount += p.Amount;
* }
* lpoText = lpoText.Substring(0, lpoText.Length - 2);
* lpoText += ")";
* }
*
* ReportHelper.AddCell(tblLeft, "Amount of Loan", 1, Element.ALIGN_LEFT, fontSmall, Rectangle.NO_BORDER, 6);
* ReportHelper.AddCell(tblLeft, ReportHelper.ShowCurrency(account.NetValue), 1, Element.ALIGN_RIGHT, fontSmall, Rectangle.NO_BORDER, 6);
*
* ReportHelper.AddCell(tblLeft, "The agreement will have a minimum duration of " + numOfPayments + " " + period + "s", 1, Element.ALIGN_LEFT, fontSmall, Rectangle.NO_BORDER, 2, 6);
*
* ReportHelper.AddCell(tblLeft, "Total amount now payable (loan + interest)", 1, Element.ALIGN_LEFT, fontSmall, Rectangle.NO_BORDER, 6);
* ReportHelper.AddCell(tblLeft, ReportHelper.ShowCurrency(account.GrossValue), 1, Element.ALIGN_RIGHT, fontSmall, Rectangle.NO_BORDER, 6);
*
* ReportHelper.AddCell(tblLeft, "Repayments are to commence", 1, Element.ALIGN_LEFT, fontSmall, Rectangle.NO_BORDER, 6);
* ReportHelper.AddCell(tblLeft, account.StartDate.ToString("dd/MMM/yyyy"), 1, Element.ALIGN_RIGHT, fontSmall, Rectangle.NO_BORDER, 6);
*
* var paragraph = "The loan will be repayable in " + numOfPayments + " installments and each subsequent " + period + "ly payment will be due on the same day each succeeding " + period;
* ReportHelper.AddCell(tblLeft, paragraph, 1, Element.ALIGN_LEFT, fontSmall, Rectangle.NO_BORDER, 2, 6);
*
* ReportHelper.AddCell(tblLeft, "Your " + period + "ly repayment will be", 1, Element.ALIGN_LEFT, fontSmall, Rectangle.NO_BORDER, 6);
* ReportHelper.AddCell(tblLeft, ReportHelper.ShowCurrency(account.Payment), 1, Element.ALIGN_RIGHT, fontSmall, Rectangle.NO_BORDER, 6);
*
* ReportHelper.AddCell(tblLeft, "Rate of interest per annum is", 1, Element.ALIGN_LEFT, fontSmall, Rectangle.NO_BORDER, 6);
* ReportHelper.AddCell(tblLeft, "XXX%", 1, Element.ALIGN_RIGHT, fontSmall, Rectangle.NO_BORDER, 6);
*
* ReportHelper.AddCell(tblLeft, "The APR applicable to this agreement is", 1, Element.ALIGN_LEFT, fontSmall, Rectangle.NO_BORDER, 6);
* ReportHelper.AddCell(tblLeft, account.TypicalApr.ToString("0.0") + "%", 1, Element.ALIGN_RIGHT, fontSmall, Rectangle.NO_BORDER, 6);
*
* ReportHelper.AddCell(tblLeft, "Other Financial Infomation", 1, Element.ALIGN_LEFT, fontSmall, Rectangle.NO_BORDER, 2, 6);
*
* ReportHelper.AddCell(tblLeft, "Total amount interest charged on this loan", 1, Element.ALIGN_LEFT, fontSmall, Rectangle.NO_BORDER, 6);
* ReportHelper.AddCell(tblLeft, ReportHelper.ShowCurrency(account.Interest), 1, Element.ALIGN_RIGHT, fontSmall, Rectangle.NO_BORDER, 6);
*
* ReportHelper.AddCell(tblLeft, lpoText, 1, Element.ALIGN_LEFT, fontSmall, Rectangle.NO_BORDER, 6);
* ReportHelper.AddCell(tblLeft, ReportHelper.ShowCurrency(paidOffAmount), 1, Element.ALIGN_RIGHT, fontSmall, Rectangle.NO_BORDER, 6);
*
* ReportHelper.AddCell(tblLeft, "Cash now required", 1, Element.ALIGN_LEFT, fontSmall, Rectangle.NO_BORDER, 6);
* ReportHelper.AddCell(tblLeft, ReportHelper.ShowCurrency(account.NetValue - paidOffAmount), 1, Element.ALIGN_RIGHT, fontSmall, Rectangle.NO_BORDER, 6);
*
* paragraph = "The total interest due under this agreement is calculated and added to the sum due at the commencement of this agreement";
* ReportHelper.AddCell(tblLeft, paragraph, 1, Element.ALIGN_LEFT, fontSmall, Rectangle.NO_BORDER, 2, 6);
*
* string text1 = "We have the right to charge interest at the annual percentage rate shown above, on the overdue amounts " +
* "(including any balance which may become payable under clause 3) and which remains unpaid at the date for the last " +
* "payment under this agreement. The interest will be charged on a daily basis from the date the amount falls due " +
* "until it is received and will run both before and after any Judgement. We may also charge you for any legal costs " +
* "and other expenses incurred by us in obtaining payment by you of any sum overdue. We may also demand immediate " +
* "repayment of the entire sum due to us in accordance with clause 3." + nl +
* "You have the right to pay off the balance of this agreement before the date of which the final payment falls due. " +
* "Based on a £100 loan over one year at the same rate of interest as this account" + nl +
* "If paid in full after three months an interest rate of £18.81 will be due." + nl +
* "If paid in full after six months an interest rate of £8.19 will be due." + nl +
* "If paid in full after nine months an interest rate of £1.62 will be due." + nl +
* "These figures are for illustration only as they take no account of any variation that may be made to this agreement." + nl + nl +
* "MISSING PAYMENTS" + nl +
* "Missing payments could have severe consequences and make obtaining credit more difficult." + nl + nl +
* "IMPORTANT-READ THIS CAREFULLY TO FIND OUT ABOUT YOUR RIGHTS" + nl +
* "The Consumer Act 1974 lays down certain requirements for your protection, which should have been complied with when " +
* "this agreement was made. If they are not, the creditor cannot enforce this agreement without getting a court order." + nl +
* "The Act also gives you a number of rights. You can settle this agreement at anytime by giving notice in writing " +
* "and paying off the amount you owe under te agreement which may be reduced by a rebate. Examples indicating the " +
* "amount you have to pay appear in this agreement." + nl + nl +
* "If you would like to know more about your rights under the Act, contact either your local Trading Standards " +
* "Department or your nearest Citizens Advice Bureau." + nl + nl +
* "YOUR RIGHT TO CANCEL" + nl +
* "Once you've signed this agreement, you will have a short time in which you can cancel it. The Creditor will send " +
* "you exact details of how and when you can do this.";
* ReportHelper.AddCell(tblLeft, "Key Information", 1, Element.ALIGN_LEFT, fontNormalBold, Rectangle.RECTANGLE, 2, 6);
* ReportHelper.AddCell(tblLeft, text1, 1, Element.ALIGN_JUSTIFIED, fontSmall, Rectangle.NO_BORDER, 2, 6);
*
* //Define Table
* var tblRight = new PdfPTable(1);
* //tblLeft.BorderWidth = 0;
* //tblLeft.Border = Rectangle.NO_BORDER;
* //tblLeft.AutoFillEmptyCells = true;
* //tblLeft.Cellpadding = 0;
*
* var text2 = "1. PAYMENT" + nl +
* "By signing this agreement you agree to repay the loan plus the interest by the payments set out, by their " +
* "specified dates. Payments must be made to us at the address shown, or to any person or address notified by us in " +
* "writing. Punctual payment is essential. If you pay by post you do so at your own risk. If you already have a loan " +
* "account with us, the appropriate part of the new loan will be applied in paying off the existing loan, and only the " +
* "remaining balance will be paid to you." + nl + nl +
* "2. FAILURE TO PAY ON TIME" + nl +
* "We have the right to charge interest at the annual percentage rate shown (less the part attributed to any " +
* "documentation fee) on all overdue amounts including any balance which may become payable under clause 3 and which remains " +
* "unpaid on the date for the last payment under this agreement. This interest will be calculated on a daily basis from " +
* "the date the amount falls due until it is received and will run both before and after any Judgement. We may also " +
* "charge you for any legal costs and other expenses incurred by us in obtaining payment by you of any sum overdue." + nl + nl +
* "3. RIGHT TO DEMAND EARLIER REPAYMENT" + nl +
* "IF:" + nl +
* "(a) Any amount payable by you is overdue for more than fourteen days, or " + nl +
* "(b) Any statement made by you in the course of obtaining this loan is found to be untrue, or " + nl +
* "(c) You have an interim or bankruptcy order made against you or you petition for your own bankruptcy, or you are served " +
* "with a creditors demand under the Insolvency Act 1986 or the Banckruptcy (Scotland) Act 1985. Or make a formal " +
* "composition or scheme with your creditors or call a meeting with them. We have the right to demand earlier payment " +
* "of the balance of the loan and the interest by sending you a default notice under the COnsumer Credit Act. If you " +
* "fail to take the action required by this notice within the time specified, the whole of the balance remaining unpaid " +
* "on the agreement shall become payable immediately. You will become entitled to a rebate of part of the interest if you " +
* "pay that balance before it would have become due had we not demanded earlier payment." + nl + nl +
* "4. EARLIER REPAYMENT BY YOU" + nl +
* "If you pay off the balance of this agreement before the date of which the final payment falls due you will usually " +
* "be entitled to a rebate of part of the interest." + nl + nl +
* "5. GENERAL PROVISIONS" + nl +
* "(d) No XXXXXXX or indulgence, which we may extend to you, shall affect our strict rights under this agreement. " +
* "(e) We may transfer our rights under this agreement. (f) You must notify us in writing within seven days of any " +
* "change of address. (g) Where two or more of you are named as the borrower you jointly and XXXXXX accept the " +
* "obligations under this Agreement. That means that each of you could be held fully responsible under this agreement." + nl + nl +
* "6. WHEN THIS AGREEMENT TAKES EFFECT" + nl +
* "This Agreement will only take effect if and when it is signed by us or our authorised representative." + nl + nl +
* "This is a credit agreement regulated by the Customer Credit Act 1974. Sign only if you want to be bound legally by " +
* "its terms." + nl + nl + nl + nl +
* "Signature(s) of Borrower(s)" + nl + nl + nl + nl +
* "Name(s) of Borrower(s). Please Print" + nl + nl + nl + nl +
* "Date of Signature(s)" + nl + nl;
* if (page == 1)
* text2 +=
* "YOUR RIGHT TO CANCEL" + nl +
* "Once you have signed you will have, for a short time, a right to cancel this Agreement. You can do this by sending " +
* "or taking a WRITTEN notice of cancellation to Kay Donaldson at R & M Donaldson P.O. Box 11 Heaton Newcastle Upon " +
* "Tyne NE7 7YW." + nl +
* "If you cancel this agreement you must repay all monies lent to you by the lender under this Agreement." + nl + nl +
* "IMPORTANT" + nl +
* "Terms and conditions governing this Agreement are printed above. By signing this Agreement you confirm " +
* "that you have read, understood and agree to be bound by those terms and conditions.";
* else
* text2 +=
* nl + nl + nl + nl + nl +
* "Signature(s) of Lenders" + nl + nl + nl +
* "Date of Signature(s)" + nl + nl + nl;
* text2 +=
* "IMPORTANT" + nl +
* "Terms and conditions governing this Agreement are printed above. By signing this Agreement you confirm " +
* "that you have read, understood and agree to be bound by those terms and conditions.";
*
* ReportHelper.AddCell(tblRight, text2, 15, Element.ALIGN_JUSTIFIED, fontSmall, Rectangle.NO_BORDER, 6);
*
* //tblBody.InsertTable(tblLeft);
* //tblBody.InsertTable(tblRight);
* tblBody.AddCell(tblLeft);
* tblBody.AddCell(tblRight);
*
* document.Add(tblTop);
* document.Add(tblBody);
*
* if (page < 2) document.NewPage();
* }
* }
* catch (Exception ex)
* {
* pdfPath = "";
* }
* finally
* {
* document.Close();
* }
*
*
* return pdfPath;
* }
*/
public static string Sundries(DateTime dateTime)
{
var document = new Document();
var db = new LA_Entities();
dateTime = dateTime.Date;
var pdfPath = ReportHelper.CreateDoc(ref document);
document.Open();
//Define Fonts
var fontReportHeader = FontFactory.GetFont(FontFactory.HELVETICA, 18, Font.BOLD);
var fontColumnHeader = FontFactory.GetFont(FontFactory.HELVETICA, 10, Font.BOLD);
var fontNormal = FontFactory.GetFont(FontFactory.COURIER, 8, Font.NORMAL);
//Adds content to the document:
ReportHelper.AddLine(document, fontReportHeader, "Sundries Since " + dateTime.ToString("dd MMM yyyy"));
//Define Table
var tbl = new PdfPTable(4)
{
TotalWidth = PageSize.A4.Width
};
//tbl.BorderWidth = 0;
//tbl.Border = Rectangle.NO_BORDER;
//tbl.AutoFillEmptyCells = true;
//tbl.Cellpadding = 1;
//Table Headers
ReportHelper.AddCell(tbl, "Customer", 1, Element.ALIGN_CENTER, fontColumnHeader, Rectangle.BOTTOM_BORDER, 5);
ReportHelper.AddCell(tbl, "Account", 1, Element.ALIGN_CENTER, fontColumnHeader, Rectangle.BOTTOM_BORDER, 5);
ReportHelper.AddCell(tbl, "Date", 1, Element.ALIGN_CENTER, fontColumnHeader, Rectangle.BOTTOM_BORDER, 5);
ReportHelper.AddCell(tbl, "Payment", 1, Element.ALIGN_CENTER, fontColumnHeader, Rectangle.BOTTOM_BORDER, 5);
//Get Payments
const int leading = 0;
var sundries = Payment.GetSundryPaymentsFromDate(dateTime);
foreach (var p in sundries)
{
var account = db.Accounts.Find(p.Account_Id);
if (account == null)
{
throw new Exception("Cannot find account");
}
var phrase = new Phrase {
Leading = leading
};
var chunk = new Chunk(account.Customer.FullName, fontNormal);
phrase.Add(chunk);
var cell = new PdfPCell();
cell.AddElement(chunk);
cell.HorizontalAlignment = Element.ALIGN_LEFT;
//cell.Border = Rectangle.NO_BORDER;
tbl.AddCell(cell);
phrase = new Phrase {
Leading = leading
};
chunk = new Chunk(account.InvoiceCode, fontNormal);
phrase.Add(chunk);
cell = new PdfPCell();
cell.AddElement(chunk);
cell.HorizontalAlignment = Element.ALIGN_RIGHT;
//cell.Border = Rectangle.NO_BORDER;
tbl.AddCell(cell);
phrase = new Phrase {
Leading = leading
};
chunk = new Chunk(p.Timestamp.ToString("dd MMM yyyy"), fontNormal);
phrase.Add(chunk);
cell = new PdfPCell();
cell.AddElement(chunk);
cell.HorizontalAlignment = Element.ALIGN_RIGHT;
//cell.Border = Rectangle.NO_BORDER;
tbl.AddCell(cell);
phrase = new Phrase {
Leading = leading
};
chunk = new Chunk(p.Amount.ToString("£0.00"), fontNormal);
phrase.Add(chunk);
cell = new PdfPCell();
cell.AddElement(chunk);
cell.HorizontalAlignment = Element.ALIGN_RIGHT;
//cell.Border = Rectangle.NO_BORDER;
tbl.AddCell(cell);
}
document.Add(tbl);
document.Close();
return(pdfPath);
}
//private static void showAccountEvent(Account a, int maxPayments, Font fontColumnHeader,
// Table tbl, List<Account> accountEvents, Payment p, string textLabel)
//{
// foreach (Account acc in accountEvents)
// {
// if (acc.AccountID == p.AccountID)
// {
// ReportHelper.addCell(tbl, textLabel + ": " + a.InvoiceCode, 1, Element.ALIGN_CENTER, fontColumnHeader, Rectangle.NO_BORDER, 10);
// for (int i = 0; i < (maxPayments + 3); i++)
// ReportHelper.addCell(tbl, "", 1, Element.ALIGN_CENTER, fontColumnHeader, Rectangle.NO_BORDER, 10);
// acc.AccountID = 0;
// }
// }
//}
//private static void checkAccountList(int maxPayments, Font fontColumnHeader, Font fontNormal,
// Table tbl, List<Account> accountList, string textLabel)
//{
// foreach (Account acc in accountList)
// {
// if (acc.AccountID > 0)
// {
// ReportHelper.addCell(tbl, textLabel + ": " + acc.InvoiceCode, 1, Element.ALIGN_CENTER, fontColumnHeader, Rectangle.NO_BORDER, 10);
// for (int i = 0; i < (maxPayments + 3); i++)
// ReportHelper.addCell(tbl, "", 1, Element.ALIGN_CENTER, fontColumnHeader, Rectangle.NO_BORDER, 10);
// acc.AccountID = 0;
// ReportHelper.addCell(tbl, acc.Customer.FullName, 1, Element.ALIGN_CENTER, fontNormal, Rectangle.NO_BORDER, 10);
// ReportHelper.addCell(tbl, acc.Customer.AddressFirstLine, 1, Element.ALIGN_CENTER, fontNormal, Rectangle.NO_BORDER, 10);
// ReportHelper.addCell(tbl, acc.InvoiceCode, 1, Element.ALIGN_CENTER, fontNormal, Rectangle.NO_BORDER, 10);
// ReportHelper.addCell(tbl, acc.NetValue.ToString("C2"), 1, Element.ALIGN_CENTER, fontNormal, Rectangle.NO_BORDER, 10);
// for (int i = 0; i < maxPayments; i++)
// ReportHelper.addCell(tbl, "-", 1, Element.ALIGN_CENTER, fontNormal, Rectangle.NO_BORDER, 10);
// }
// }
//}
public static string ByDebt(int debt)
{
var document = new Document();
var pdfPath = ReportHelper.CreateDoc(ref document);
document.Open();
//Define Fonts
var fontReportHeader = FontFactory.GetFont(FontFactory.HELVETICA, 18, Font.BOLD);
var fontColumnHeader = FontFactory.GetFont(FontFactory.HELVETICA, 10, Font.BOLD);
var fontNormal = FontFactory.GetFont(FontFactory.HELVETICA, 6, Font.NORMAL);
var fontTotal = FontFactory.GetFont(FontFactory.HELVETICA, 8, Font.BOLD);
//Adds content to the document:
ReportHelper.AddLine(document, fontReportHeader, "By Debt - " + debt.ToString("£0.00"));
//Define Table
var tbl = new PdfPTable(5);
//tbl.BorderWidth = 0;
//tbl.Cellpadding = 2;
//tbl.Border = Rectangle.NO_BORDER;
//tbl.AutoFillEmptyCells = true;
//Table Headers
ReportHelper.AddCell(tbl, "Customer", 1, Element.ALIGN_CENTER, fontColumnHeader, Rectangle.BOTTOM_BORDER, 10);
ReportHelper.AddCell(tbl, "Account", 1, Element.ALIGN_CENTER, fontColumnHeader, Rectangle.BOTTOM_BORDER, 10);
ReportHelper.AddCell(tbl, "Net Value", 1, Element.ALIGN_CENTER, fontColumnHeader, Rectangle.BOTTOM_BORDER, 10);
ReportHelper.AddCell(tbl, "Overdue", 1, Element.ALIGN_CENTER, fontColumnHeader, Rectangle.BOTTOM_BORDER, 10);
ReportHelper.AddCell(tbl, "Outstanding", 1, Element.ALIGN_CENTER, fontColumnHeader, Rectangle.BOTTOM_BORDER, 10);
//Get Data
var db = new LA_Entities();
var customerIds = Functions.GetCustomerIdsByDebt(debt);
foreach (var customerId in customerIds)
{
var customer = db.Customers.Find(customerId);
if (customer == null)
{
continue;
}
ReportHelper.AddCell(tbl, customer.FullName, 1, Element.ALIGN_LEFT, fontNormal, Rectangle.BOTTOM_BORDER, 2, 10);
ReportHelper.AddCell(tbl, customer.AddressFirstLine, 1, Element.ALIGN_LEFT, fontNormal, Rectangle.BOTTOM_BORDER, 3, 10);
//Account Info
double totalOutstanding = 0;
foreach (var account in customer.Accounts)
{
if (!account.CurrentStatus.IsCreated)
{
continue;
}
ReportHelper.AddCell(tbl, "", 1, Element.ALIGN_CENTER, fontNormal, Rectangle.RIGHT_BORDER, 10);
ReportHelper.AddCell(tbl, account.InvoiceCode, 1, Element.ALIGN_LEFT, fontNormal, Rectangle.RIGHT_BORDER, 10);
ReportHelper.AddCell(tbl, account.NetValue.ToString("£0.00"), 1, Element.ALIGN_RIGHT, fontNormal, Rectangle.RIGHT_BORDER, 10);
ReportHelper.AddCell(tbl, account.Overdue.ToString("£0.00"), 1, Element.ALIGN_RIGHT, fontNormal, Rectangle.RIGHT_BORDER, 10);
ReportHelper.AddCell(tbl, account.Outstanding.ToString("£0.00"), 1, Element.ALIGN_RIGHT, fontNormal, Rectangle.NO_BORDER, 10);
totalOutstanding += account.Outstanding;
}
ReportHelper.AddCell(tbl, "Total:", 1, Element.ALIGN_RIGHT, fontTotal, Rectangle.NO_BORDER, 4, 10);
ReportHelper.AddCell(tbl, totalOutstanding.ToString("£0.00"), 1, Element.ALIGN_RIGHT, fontTotal, Rectangle.BOTTOM_BORDER, 10);
}
document.Add(tbl);
document.Close();
return(pdfPath);
}
