private Dictionary <string, int> getProcessors()
{
Dictionary <string, int> processorList = new Dictionary <string, int>();
//query list for mySql
List <MySqlCommand> queries = new List <MySqlCommand>();
//String timestamp =
db_connect connection = new db_connect();
connection.connect();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection.getConnection();
cmd.CommandText = string.Format("SELECT id, name FROM e_processors");
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
processorList.Add(reader.GetString(1), reader.GetInt32(0));
}
reader.Close();
return(processorList);
}
private void startApplication()
{
log.Debug("Program running...");
//setup folder query
StringFieldCriterion folderCri = selectLoanFolder();
//execute encompass query
LoanReportCursor results = encompassQueryBuilder(folderCri);
int total = results.Count;
int count = 0;
log.Info("Total Files: " + total);
//query list for mySql
//List<MySqlCommand> queries = new List<MySqlCommand>();
log.Debug("connecting to MySQL...");
db_connect connection = new db_connect();
connection.connect();
log.Debug("connected to MySQL");
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connection.getConnection();
foreach (LoanReportData data in results)
{
Dictionary <String, String> map = dbQueryBuilder(data);
/*cmd.CommandText = string.Format(
* "INSERT INTO loans(guid, investor, investorNum, createdAt, b1_lname, b1_fname, loanAmt, loanNum, fundedDate, processor, loan_officer, purchasedDate) " +
* "values(@v1, @v2, @v3, @createdAt, @v4, @v5, @v6, @v7, @v8, @v9, @v10, " +
* "@v11, @currMilestone) " +
* "ON DUPLICATE KEY UPDATE guid=@v1, investor=@v2, investorNum=@v3, updatedAt=@updatedAt, b1_lname=@v4, b1_fname=@v5, "+
* "loanAmt=@v6, loanNum=@v7, fundedDate=@v8, processor=@v9, loan_officer=@v10, " +
* "purchasedDate=@v11"
* );*/
var now = timestamp.ToString("yyyy - MM - dd HH: mm:ss");
StringBuilder insert = new StringBuilder();
insert.Append("INSERT INTO loans(guid, createdAt, loanFolder ");
//cols
foreach (String key in map.Keys)
{
insert.Append(", " + key);
}
//values
insert.Append(") ");
StringBuilder values = new StringBuilder();
values.Append("VALUES('" + data.Guid + "', '" + now + "', '" + folderCri.Value + "'");
foreach (String key in map.Keys)
{
if (map[key] != null)
{
values.Append(", '" + MySqlHelper.EscapeString(" " + map[key]) + "'");
}
else
{
values.Append(", NULL");
}
}
values.Append(") ");
StringBuilder update2 = new StringBuilder();
update2.Append("ON DUPLICATE KEY UPDATE updatedAt='" + now + "', loanFolder='" + folderCri.Value + "'");
foreach (String key in map.Keys)
{
if (map[key] != null)
{
update2.Append(", " + key + "='" + MySqlHelper.EscapeString(" " + map[key]) + "'");
}
else
{
update2.Append(", " + key + "=NULL");
}
}
//mysql command
cmd.CommandText = insert.ToString() + values.ToString() + update2.ToString();
/* cmd.Parameters.Clear();
* cmd.Parameters.AddWithValue("@v1", data.Guid);
* cmd.Parameters.AddWithValue("@v2", map["investor"]);
* cmd.Parameters.AddWithValue("@v3", map["investorNum"]);
* cmd.Parameters.AddWithValue("@createdAt", timestamp.ToString("yyyy-MM-dd HH:mm:ss"));
* cmd.Parameters.AddWithValue("@updatedAt", timestamp.ToString("yyyy-MM-dd HH:mm:ss"));
* cmd.Parameters.AddWithValue("@v4", map["b1_lname"]);
* cmd.Parameters.AddWithValue("@v5", map["b1_fname"]);
* cmd.Parameters.AddWithValue("@v6", map["loanAmt"]);
* cmd.Parameters.AddWithValue("@v7", map["loanNum"]);
* cmd.Parameters.AddWithValue("@v8", map["fundedDate"]);
* cmd.Parameters.AddWithValue("@v9", map["processor"]);
* cmd.Parameters.AddWithValue("@v10", map["loanOfficer"]);
* cmd.Parameters.AddWithValue("@v11", map["purchasedDate"]);
* cmd.Prepare();*/
cmd.ExecuteNonQuery();
//status output
count++;
if (count % 10 == 0)
{
Console.Write("\r{0} ", "writing: " + count + "/" + total);
}
}
Console.Out.WriteLine("");
results.Close();
TimeSpan time = DateTime.Now.Subtract(timestamp);
/*connection.addLog("db_update", "updated: " + folderCri.Value + total + " rows in " +
* String.Format("{0} min {1}.{2} sec"
* , time.Minutes
* , time.Seconds
* , time.Milliseconds) );*/
connection.close();
log.Info("Finished updating db in " +
String.Format("{0} min {1}.{2} sec"
, time.Minutes
, time.Seconds
, time.Milliseconds));
}
