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