public void SqlBuilderTestString()
{
SqlResultBuilder builder = SqlResultBuilder.NewInstance(5);
builder.SetColumnType(0, "nvarchar");
builder.SetColumnType(1, "nvarchar");
builder.SetColumnType(2, "nvarchar");
builder.SetColumnType(3, "nvarchar");
builder.SetColumnType(4, "nvarchar");
builder.SetColumnName(0, "Col-0");
builder.SetColumnName(1, "Col-1");
builder.SetColumnName(2, "Col-2");
builder.SetColumnName(3, "Col-3");
builder.SetColumnName(4, "Col-4");
builder.PushRow();
builder.AddValue("R0-C0");
builder.AddValue("R0-C1");
builder.AddValue("R0-C2");
builder.AddValue("R0-C3");
builder.AddValue("R0-C4");
builder.PopRow();
builder.PushRow();
builder.AddValue("R1-C0");
builder.AddValue("R1-C1");
builder.AddValue("R1-C2");
builder.AddValue("R1-C3");
builder.AddValue("R1-C4");
builder.PopRow();
builder.PushRow();
builder.AddValue("R2-C0");
builder.AddValue("R2-C1");
builder.AddValue("R2-C2");
builder.AddValue("R2-C3");
builder.AddValue("R2-C4");
builder.PopRow();
ISqlResultSet result = builder.Build();
Assert.IsTrue(result.GetColumnCount() == 5, "Column count is not equal to 5!");
Assert.IsTrue(result.GetRowCount() == 3, "Row count is not equal to 5!");
for (int i = 0; i < result.GetColumnCount(); i++)
{
string expected0 = "Col-" + i;
string actual0 = result.GetColumnName(i);
Assert.IsTrue(expected0.Equals(actual0), "Expected value{0}, current value {1}", expected0, actual0);
for (int j = 0; j < result.GetRowCount(); j++)
{
string actual1 = result.GetValue(i, j).AsString();
string expected1 = "R" + j + "-C" + i;
Assert.IsTrue(expected1.Equals(actual1), "Expected value{0}, current value {1}", expected1, actual1);
}
}
}
public void SqlBuilderTestBit()
{
SqlResultBuilder builder = SqlResultBuilder.NewInstance(2);
builder.SetColumnType(0, "bit");
builder.SetColumnType(1, "bit");
builder.SetColumnName(0, "Col-0");
builder.SetColumnName(1, "Col-1");
builder.PushRow();
builder.AddValue(0);
builder.AddValue(1);
builder.PopRow();
builder.PushRow();
builder.AddValue(false);
builder.AddValue(true);
builder.PopRow();
ISqlResultSet result = builder.Build();
Assert.IsTrue(result.GetColumnCount() == 2, "Column count is not equal to 5!");
Assert.IsTrue(result.GetRowCount() == 2, "Row count is not equal to 5!");
Assert.IsTrue(result.GetValue(0, 0).AsBool() == false);
Assert.IsTrue(result.GetValue(1, 0).AsBool() == true);
Assert.IsTrue(result.GetValue(0, 1).AsBool() == false);
Assert.IsTrue(result.GetValue(1, 1).AsBool() == true);
}
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);
}
public void SqlBuilderTestInt()
{
SqlResultBuilder builder = SqlResultBuilder.NewInstance(3);
builder.SetColumnType(0, "int");
builder.SetColumnType(1, "int");
builder.SetColumnType(2, "int");
builder.SetColumnName(0, "Col-0");
builder.SetColumnName(1, "Col-1");
builder.SetColumnName(2, "Col-2");
builder.PushRow();
builder.AddValue(0);
builder.AddValue(1);
builder.AddValue(2);
builder.PopRow();
builder.PushRow();
builder.AddValue(3);
builder.AddValue(4);
builder.AddValue(5);
builder.PopRow();
ISqlResultSet result = builder.Build();
Assert.IsTrue(result.GetColumnCount() == 3, "Column count is not equal to 5!");
Assert.IsTrue(result.GetRowCount() == 2, "Row count is not equal to 5!");
Assert.IsTrue(result.GetValue(0, 0).AsInt() == 0);
Assert.IsTrue(result.GetValue(1, 0).AsInt() == 1);
Assert.IsTrue(result.GetValue(2, 0).AsInt() == 2);
Assert.IsTrue(result.GetValue(0, 1).AsInt() == 3);
Assert.IsTrue(result.GetValue(1, 1).AsInt() == 4);
Assert.IsTrue(result.GetValue(2, 1).AsInt() == 5);
}
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);
}
}
}
private Grid CreateGridFromResultSet(ISqlResultSet dataset)
{
int columns = dataset.GetColumnCount();
int rows = dataset.GetRowCount();
Grid grid = new Grid();
for (int i = 0; i < columns; i++)
{
grid.ColumnDefinitions.Add(new ColumnDefinition()
{
Width = GridLength.Auto
});
}
for (int i = 0; i < rows + 1; i++)
{
grid.RowDefinitions.Add(new RowDefinition()
{
Height = GridLength.Auto
});
}
Thickness partialThickness = new Thickness(0, 0, 1, 1);
Thickness topThickness = new Thickness(0, 1, 1, 1);
Thickness leftThickness = new Thickness(1, 0, 1, 1);
Thickness fullThickness = new Thickness(1, 1, 1, 1);
for (int i = 0; i < columns; i++)
{
string colName = dataset.GetColumnName(i);
Border border = new Border()
{
BorderBrush = Brushes.Black, BorderThickness = i == 0 ? fullThickness : topThickness, Background = Brushes.Gray
};
border.SetValue(Grid.ColumnProperty, i);
border.SetValue(Grid.RowProperty, 0);
TextBlock textBlock = new TextBlock()
{
Text = colName, Padding = new Thickness(5)
};
border.Child = textBlock;
grid.Children.Add(border);
}
for (int i = 0; i < columns; i++)
{
for (int j = 0; j < rows; j++)
{
Border border = new Border()
{
BorderBrush = Brushes.Black, BorderThickness = i == 0 ? leftThickness : partialThickness
};
border.SetValue(Grid.ColumnProperty, i);
border.SetValue(Grid.RowProperty, j + 1);
string text = dataset.GetValue(i, j).AsString();
TextBlock textBlock = new TextBlock()
{
Text = text, Padding = new Thickness(5)
};
border.Child = textBlock;
grid.Children.Add(border);
}
}
return(grid);
}
public static SimpleHTMLTable FromSqlResult(ISqlResultSet result)
{
return(FromSqlResult(result, result.GetRowCount()));
}