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); }