public ISqlResultSet TransformSqlResultSet(ISqlResultSet resultSet)
{
List <int> colIndices = new List <int>();
List <string> colIds = new List <string>();
List <string> colNames = new List <string>();
int i = 0;
var e = _data.Columns.GetEnumerator();
while (e.MoveNext())
{
string colId = e.Current.Key;
int colIndex = resultSet.ColumnIndexOf(colId);
if (colIndex != -1)
{
colIndices.Add(colIndex);
colIds.Add(colId);
colNames.Add(e.Current.Value);
i++;
}
}
SqlResult transformedResults = new SqlResult(colIndices.Count, resultSet.GetRowCount());
transformedResults.SetColumnNames(colNames.ToArray());
for (int j = 0; j < resultSet.GetRowCount(); j++)
{
ISqlValue[] row = new ISqlValue[colIndices.Count];
i = 0;
foreach (int index in colIndices)
{
row[i] = resultSet.GetValue(index, j);
i++;
}
transformedResults.AddRow(j, row);
}
return(transformedResults);
}
private void SendEmails()
{
App app = Application.Current as App;
if (app.SqlManager.IsConnected)
{
// Getting the entries where prealert is 0 / false
ISqlResultSet result = app.SqlManager.ExecuteQuerryFromFile("Sql/GetPreAlertFalse.sql");
if (result.GetRowCount() == 0)
{
Console.WriteLine("No entries with PreAlert = false found!");
return;
}
string json = File.ReadAllText(App.TRANSFORM_DIR + "PurchaseOrder.json");
TableTransformer tableTransformer = TableTransformer.FromJson(json);
ISqlResultSet transformedResult = tableTransformer.TransformSqlResultSet(result);
json = File.ReadAllText(App.TRANSFORM_DIR + "Items.json");
tableTransformer = TableTransformer.FromJson(json);
HashSet <int> ids = new HashSet <int>();
for (int i = 0; i < result.GetRowCount(); i++)
{
int?nid = result.GetValue(0, i).AsInt();
if (nid.HasValue)
{
ids.Add(nid.Value);
}
}
List <EmailWrapper> emails = new List <EmailWrapper>();
for (int i = 0; i < result.GetRowCount(); i++)
{
int idcol = result.ColumnIndexOf("ID");
int colid = result.GetValue(idcol, i).AsInt().Value;
int inboundcol = result.ColumnIndexOf("INBOUNDID");
int inboundid = result.GetValue(inboundcol, i).AsInt().Value;
ISqlResultSet itemResult = app.SqlManager.ExectuteParameterizedQuerryFromFile("Sql/ItemLevel.sql", new string[] { inboundid.ToString() });
ISqlResultSet transformedItemResult = tableTransformer.TransformSqlResultSet(itemResult);
EmailDataContext context = new EmailDataContext
{
ShipmentTable = SimpleHTMLTable.FromSqlResult(transformedResult, i, 1),
ItemTable = SimpleHTMLTable.FromSqlResult(transformedItemResult)
};
string htmlSource = File.ReadAllText("Html/EmailTemplate.html");
HTMLParser parser = new HTMLParser(htmlSource)
{
DataContext = context
};
string parsedSource = parser.Parse();
EmailWrapper emailData = new EmailWrapper
{
ID = colid,
ShouldSend = true,
Receiver = app.Settings.EmailAdress,
Subject = ("PREALERT - OrderID=" + inboundid),
HTMLBody = parsedSource
};
emails.Add(emailData);
}
if (app.Settings.ShowEmailsBeforeSending)
{
EmailPreviewWindow emailPreviewWindow = new EmailPreviewWindow(emails);
emailPreviewWindow.ShowDialog();
}
HashSet <int> sendIds = new HashSet <int>();
OutlookApplication application = OutlookApplication.CreateApplication();
foreach (EmailWrapper data in emails)
{
if (data.ShouldSend)
{
//Console.WriteLine("EmailData: ID:{0}, TO:{1}, SUBJECT:{2}, SHOULD_SEND:{3}", data.ID, data.Receiver, data.Subject, data.ShouldSend);
bool sent = SendEmail(application, data);
if (sent)
{
sendIds.Add(data.ID);
}
}
}
if (sendIds.Count > 0)
{
// updating the prealert to 1 / true
StringBuilder conditionBuilder = new StringBuilder();
int j = 0;
foreach (int id in sendIds)
{
conditionBuilder.Append("ID = " + id);
j++;
if (j < sendIds.Count)
{
conditionBuilder.Append(" OR ");
}
}
string updateQuerry = "UPDATE TRITPurchaseOrder SET PREALERT = 1 WHERE " + conditionBuilder.ToString();
int rowsAffected = app.SqlManager.ExecuteWithoutResult(updateQuerry);
Console.WriteLine("Rows affected: {0}", rowsAffected);
}
}
}