/// <summary> /// writes the individual line out to the file as a CSV /// </summary> /// <param name="customerData">List of string values associated with each customer from the database.</param> private static void writeCIFFile(Customer customerData) { string output; output = ""; for (int j = 0; j < CSVFields.Count; j++) { if (CSVFields[j] == "Account Number") { output = output + customerData.getAccountNumber() + customerData.getDelimiter() + customerData.getStatementNumber().ToString("000") + ","; } else if (CSVFields[j] == "Due Date") { output = output + customerData.getDueDate() + ","; } else if (CSVFields[j] == "Amount Due"){ output = output + customerData.getAmountDue() + ","; } //This is a test block for adding Paperless Billing // else if (CSVFields[j] == "Paperless") // { // output = output + customerData.getPaperless() + ","; // } else { output = output + ","; } } outputFile.WriteLine(output); //Console.WriteLine(output); }
/// <summary> /// Looks up all customers in the database who have a current bill due /// </summary> /// <param name="billRange">Optional string date of how old of bills we look for</param> private static void getCustomersDue(string billRange) { SqlConnection conn = new SqlConnection(connString); SqlCommand command = new SqlCommand(); try { string accountNumber = ""; int statementNumber = 1; //string statementNumberString = ""; string dueDate = ""; string amountDue = ""; string text = String.Format("select IBLKNB, IBHXCD, IBT8DT, IBW2TX from [COMMSOFT].[CCTS].[COMDB200].[BAHPHY01] where IBTIDT > '{0}'", billRange); int paperless = 0; SqlDataReader reader; command.CommandText = text; command.CommandType = CommandType.Text; command.Connection = conn; conn.Open(); reader = command.ExecuteReader(); if (reader.HasRows) { int count = 0; while (reader.Read()) { Console.WriteLine("Reading line {0}", count); count++; for (int i = 0; i < reader.FieldCount; i++) { if (reader.GetName(i) == "IBLKNB") { accountNumber = reader.GetSqlValue(i).ToString(); Console.WriteLine(accountNumber); } else if (reader.GetName(i) == "IBHXCD") { int.TryParse(reader.GetSqlValue(i).ToString(), out statementNumber); } //Console.WriteLine(reader.GetName(i) + " = " + reader.GetSqlValue(i)); else if (reader.GetName(i) == "IBT8DT") { dueDate = reader.GetSqlValue(i).ToString(); //need to convert due date to the correct format MMDDYYYY string month = dueDate.Substring(3, 2); string day = dueDate.Substring(5, 2); string year = "2" + (int.Parse(dueDate.Substring(0, 1)) - 1).ToString() + dueDate.Substring(1, 2); dueDate = month + day + year; } else if (reader.GetName(i) == "IBW2TX") { var paperlessCode = reader.GetSqlValue(i).ToString().ToUpper(); Console.WriteLine(paperlessCode); if (paperlessCode == "E" || paperlessCode == "D") { paperless = 1; } else { paperless = 0; } //accountNumber = reader.GetSqlValue(i).ToString(); } } Customer tempCustomer = new Customer(); tempCustomer.setAccountNumber(accountNumber); tempCustomer.setDelimiter('-'); tempCustomer.setStatementNumber(statementNumber); tempCustomer.setDueDate(dueDate); tempCustomer.setPaperless(paperless); // accountNumber = accountNumber + "-" + statementNumberString; //customerData.Add(accountNumber); //customerData.Add(dueDate); //customerData.Add(amountDue); customers.Add(tempCustomer); //writeCIFFile(customerData); } } conn.Close(); //Console.WriteLine("count = {0}", customers.Count); //System.DateTime now; //System.DateTime then; //System.DateTime diff; for (int i = 0; i < customers.Count; i++) { Console.WriteLine("Writing to file "+(i+1) +"/" + customers.Count); //need to fetch the amount due from another SQL call using the account number // now = DateTime.Now; amountDue = getCustomerAmountDue(customers[i].getAccountNumber(), customers[i].getStatementNumber()); //then = DateTime.Now; //Console.WriteLine("Time taken to get customer Amount due..."+((then.Second*1000 + then.Millisecond) - (now.Second*1000 + now.Millisecond)).ToString() +"ms"); customers[i].setAmountDue(amountDue); Console.WriteLine(String.Format("Amount due for {0}{1}{2} = {3}",customers[i].getAccountNumber(),customers[i].getDelimiter(),customers[i].getStatementNumber(),customers[i].getAmountDue())); writeCIFFile(customers[i]); } } catch (Exception e) { conn.Close(); body = body + "Threw an exception: " + e.Message.ToString() + (char)13 +(char)10; Console.WriteLine(string.Format("Exception with sql database {0}", e)); } finally { conn.Close(); } }