private void SettupWorkflowImport()
{
// test DB connection
#region TestConn
string connectionString = ConfigurationManager.ConnectionStrings["CertusDB"].ToString();
string query;
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand command = conn.CreateCommand();
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
}
#endregion
// if there is a LoadingForm, report progress
if (WorkflowManager.CheckIfFormIsOpened("LoadingForm"))
{
if (Application.OpenForms[0].InvokeRequired)
{
Application.OpenForms[0].Invoke(new Action(() =>
{
(Application.OpenForms["LoadingForm"] as LoadingForm).ChangeLabel($"Executing query...");
(Application.OpenForms["LoadingForm"] as LoadingForm).HideCloseBtn();
(Application.OpenForms["LoadingForm"] as LoadingForm).Refresh();
}));
}
else
{
(Application.OpenForms["LoadingForm"] as LoadingForm).ChangeLabel($"Executing query...");
(Application.OpenForms["LoadingForm"] as LoadingForm).HideCloseBtn();
(Application.OpenForms["LoadingForm"] as LoadingForm).Refresh();
}
}
// generate items
WorkflowImportRouter(2);
}
private void GenerateWorkflowItemList()
{
string connectionString = ConfigurationManager.ConnectionStrings["CertusDB"].ToString();
string query;
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand command = conn.CreateCommand();
// get query
using (Stream strm = Assembly.GetExecutingAssembly().GetManifestResourceStream("CertusCompanion.ImportQueries.WIR4.0_DS.sql"))
{
using (StreamReader sr = new StreamReader(strm))
{
query = sr.ReadToEnd();
}
}
// manipulate query
if (workflowItemSelection == "Non-completed")
{
query = query.Replace("TOP", "--TOP");
query = query.Replace("0--<cl>", $"{clientID}--<cl>");
query = query.Replace("AND DocumentWorkflowStatus.DocumentWorkflowStatusID > 3--<c2>", "--AND DocumentWorkflowStatus.DocumentWorkflowStatusID > 3--<c2>");
}
else if (workflowItemSelection == "Most Recent...")
{
query = query.Replace("TOP 0", $"TOP {itemCount}");
query = query.Replace("0--<cl>", $"{clientID}--<cl>");
query = query.Replace("AND DocumentWorkflowStatus.DocumentWorkflowStatusID <= 3--<c1>", "--AND DocumentWorkflowStatus.DocumentWorkflowStatusID <= 3--<c1>");
query = query.Replace("AND DocumentWorkflowStatus.DocumentWorkflowStatusID > 3--<c2>", "--AND DocumentWorkflowStatus.DocumentWorkflowStatusID > 3--<c2>");
}
else if (workflowItemSelection == "Most Recent (Non-completed)...")
{
query = query.Replace("TOP 0", $"TOP {itemCount}");
query = query.Replace("0--<cl>", $"{clientID}--<cl>");
query = query.Replace("AND DocumentWorkflowStatus.DocumentWorkflowStatusID > 3--<c2>", "--AND DocumentWorkflowStatus.DocumentWorkflowStatusID > 3--<c2>");
}
else if (workflowItemSelection == "Most Recent (Completed)...")
{
query = query.Replace("TOP 0", $"TOP {itemCount}");
query = query.Replace("0--<cl>", $"{clientID}--<cl>");
query = query.Replace("AND DocumentWorkflowStatus.DocumentWorkflowStatusID <= 3--<c1>", "--AND DocumentWorkflowStatus.DocumentWorkflowStatusID <= 3--<c1>");
}
// execute query
command.CommandText = query;
command.CommandType = CommandType.Text;
command.CommandTimeout = 450;
SqlDataAdapter wiAdapter = new SqlDataAdapter(command);
DataTable wiTable = new DataTable();
wiAdapter.Fill(wiTable);
// if there is a LoadingForm, report progress
if (WorkflowManager.CheckIfFormIsOpened("LoadingForm"))
{
if (Application.OpenForms[0].InvokeRequired)
{
Application.OpenForms[0].Invoke(new Action(() =>
{
(Application.OpenForms["LoadingForm"] as LoadingForm).ChangeLabel($"Generating items...");
(Application.OpenForms["LoadingForm"] as LoadingForm).MoveBar(25);
(Application.OpenForms["LoadingForm"] as LoadingForm).Refresh();
}));
}
else
{
(Application.OpenForms["LoadingForm"] as LoadingForm).ChangeLabel($"Generating items...");
(Application.OpenForms["LoadingForm"] as LoadingForm).MoveBar(25);
(Application.OpenForms["LoadingForm"] as LoadingForm).Refresh();
}
}
// add to WI
foreach (DataRow row in wiTable.Rows)
{
string documentWorkflowItemID = row[0].ToString();
string CertificateID = row[1].ToString();
string vendorName = row[2].ToString();
string vendorID = row[3].ToString();
string clID = row[4].ToString();
string workflowAnalyst = row[5].ToString();
string workflowAnalystID = row[6].ToString();
string companyAnalyst = row[7].ToString();
string companyAnalystID = row[8].ToString();
DateTime parsedDateTimeValue;
DateTime?emailDate = null;
DateTime.TryParse(row[9].ToString(), out parsedDateTimeValue);
emailDate = parsedDateTimeValue;
string emailFromAddress = row[10].ToString();
string subjectLine = row[11].ToString();
string status = row[12].ToString();
string certusFileID = row[13].ToString();
string fileName = row[14].ToString();
string fileSize = row[15].ToString();
string fileMIME = row[16].ToString();
string fileURL = row[17].ToString();
WorkflowItem wi = new WorkflowItem
(
documentWorkflowItemID,
CertificateID,
vendorName,
vendorID,
clID,
null,
null,
null,
null,
workflowAnalyst,
workflowAnalystID,
companyAnalyst,
companyAnalystID,
emailDate,
emailFromAddress,
subjectLine,
null,
status,
certusFileID,
fileName,
fileURL,
fileSize,
fileMIME,
null
);
this.currentImportItems.Add(wi);
}
// if there is a LoadingForm, report progress
if (WorkflowManager.CheckIfFormIsOpened("LoadingForm"))
{
if (Application.OpenForms[0].InvokeRequired)
{
Application.OpenForms[0].Invoke(new Action(() =>
{
(Application.OpenForms["LoadingForm"] as LoadingForm).ChangeLabel($"Saving item data...");
(Application.OpenForms["LoadingForm"] as LoadingForm).MoveBar(25);
(Application.OpenForms["LoadingForm"] as LoadingForm).Refresh();
}));
}
else
{
(Application.OpenForms["LoadingForm"] as LoadingForm).ChangeLabel($"Saving item data...");
(Application.OpenForms["LoadingForm"] as LoadingForm).MoveBar(25);
(Application.OpenForms["LoadingForm"] as LoadingForm).Refresh();
}
}
WorkflowImportRouter(3);
}