private void bt_DeleteRevision_Click(object sender, EventArgs e)
{
for (int i = 0; i < dgv_Revisions.SelectedCells.Count; i++)
{
string timeStamp = dgv_Revisions.SelectedCells[i].Value.ToString();
timeStamp = timeStamp.Substring(timeStamp.IndexOf('_') + 1);
timeStamp = '%' + timeStamp + '%';
SQL_Executor.Exec_SPU_With_Multple_Parameters_NoReturn(connectionString, "SPU_DELETE_REVISION", "@TimeStamp", timeStamp);
}
}
private void bt_InnerRevision_Click(object sender, EventArgs e)
{
string terBank = dgv_Revisions.SelectedCells[0].Value.ToString();
string timeStamp1 = dgv_Revisions.SelectedCells[0].Value.ToString();
string timeStamp2 = dgv_Revisions.SelectedCells[1].Value.ToString();
terBank = terBank.Remove(terBank.IndexOf('_'));
timeStamp1 = timeStamp1.Substring(timeStamp1.IndexOf('_') + 1);
timeStamp2 = timeStamp2.Substring(timeStamp2.IndexOf('_') + 1);
DataSet ds_InnerRevision_ARM_AD_SCCM = SQL_Executor.Exec_SPU_With_Multple_Parameters_Return_DS(connString, "SPU_INNER_ARM_REVISION", "@TerBankName", terBank, "@TimeStamp1", timeStamp1, "@TimeStamp2", timeStamp2);
DataSet ds_InnerRevision_SERV_AD_SCCM = SQL_Executor.Exec_SPU_With_Multple_Parameters_Return_DS(connString, "SPU_INNER_SERV_REVISION", "@TerBankName", terBank, "@TimeStamp1", timeStamp1, "@TimeStamp2", timeStamp2);
DataSet ds_InnerRevision_AVPO = SQL_Executor.Exec_SPU_With_Multple_Parameters_Return_DS(connString, "SPU_INNER_AVPO_REVISION", "@TerBankName", terBank, "@TimeStamp1", timeStamp1, "@TimeStamp2", timeStamp2);
ds_InnerRevision_ARM_AD_SCCM.Tables[0].TableName = "Without_AD";
ds_InnerRevision_ARM_AD_SCCM.Tables[1].TableName = "Without_SCCM";
ds_InnerRevision_ARM_AD_SCCM.Tables[2].TableName = "Without_AVPO";
ds_InnerRevision_SERV_AD_SCCM.Tables[0].TableName = "Without_AD";
ds_InnerRevision_SERV_AD_SCCM.Tables[1].TableName = "Without_SCCM";
ds_InnerRevision_SERV_AD_SCCM.Tables[2].TableName = "Without_AVPO";
//workbook.Worksheets.Add(ds_ProblemArm_AllArm_Ad_Sccm_WithoutAVPO);
ds_InnerRevision_AVPO.Tables[0].TableName = "KES_Old_Client";
ds_InnerRevision_AVPO.Tables[1].TableName = "KES_Old_Base";
ds_InnerRevision_AVPO.Tables[2].TableName = "SEP_Old_Client";
ds_InnerRevision_AVPO.Tables[3].TableName = "SEP_Old_Base";
var workbook3 = new XLWorkbook();
workbook3.Worksheets.Add(ds_InnerRevision_ARM_AD_SCCM);
workbook3.SaveAs(terBank + "_InnerRevision_ARM_" + timeStamp1 + "_" + timeStamp2 + ".xlsx");
workbook3 = new XLWorkbook();
workbook3.Worksheets.Add(ds_InnerRevision_SERV_AD_SCCM);
workbook3.SaveAs(terBank + "_InnerRevision_SERV_" + timeStamp1 + "_" + timeStamp2 + ".xlsx");
workbook3 = new XLWorkbook();
workbook3.Worksheets.Add(ds_InnerRevision_AVPO);
workbook3.SaveAs(terBank + "_InnerRevision_AVPO_" + timeStamp1 + "_" + timeStamp2 + ".xlsx");
}
private void tabControl1_TabIndexChanged(object sender, EventArgs e)
{
dgv_Revisions.DataSource = null;
dgv_Revisions.DataSource = SQL_Executor.Exec_SPU_With_Multple_Parameters_Return_DS(connString, "SPU_SELECT_REVISION").Tables[0];
}
private void UpLoadData()
{
timeStamp = DateTime.Now.ToString("s").Replace('-', '_').Replace(':', '_');
string tblADName = tb_TerBank.Text + "_AD_" + timeStamp;
SQL_Executor.Exec_SPU_With_Multple_Parameters_NoReturn(connString, "SPU_CreateTable_AD", "@TableName", tblADName);
var dirInfo = new DirectoryInfo(tb_PathADReport.Text);
foreach (var file in dirInfo.GetFiles("*.csv", SearchOption.TopDirectoryOnly))
{
SQL_Executor.Exec_SPU_With_Multple_Parameters_NoReturn(connString, "SPU_Insert_AD", "@TableName", tblADName, "@FilePath", file.FullName);
}
dirInfo = new DirectoryInfo(tb_PathADOmegaReport.Text);
string tblADOmegaName = "OMEGA" + "_AD_" + timeStamp;
SQL_Executor.Exec_SPU_With_Multple_Parameters_NoReturn(connString, "SPU_CreateTable_AD", "@TableName", tblADOmegaName);
foreach (var file in dirInfo.GetFiles("*.csv", SearchOption.TopDirectoryOnly))
{
SQL_Executor.Exec_SPU_With_Multple_Parameters_NoReturn(connString, "SPU_Insert_AD", "@TableName", tblADOmegaName, "@FilePath", file.FullName);
}
string tblKESName = tb_TerBank.Text + "_KES_" + timeStamp;
SQL_Executor.Exec_SPU_With_Multple_Parameters_NoReturn(connString, "SPU_CreateTable_KES", "@TableName", tblKESName);
dirInfo = new DirectoryInfo(tb_PathKESReport.Text);
foreach (var file in dirInfo.GetFiles("*.csv", SearchOption.TopDirectoryOnly))
{
SQL_Executor.Exec_SPU_With_Multple_Parameters_NoReturn(connString, "SPU_Insert_KES", "@TableName", tblKESName, "@FilePath", file.FullName);
}
string tblMPName = tb_TerBank.Text + "_MP_" + timeStamp;
SQL_Executor.Exec_SPU_With_Multple_Parameters_NoReturn(connString, "SPU_CreateTable_MP", "@TableName", tblMPName);
dirInfo = new DirectoryInfo(tb_PathMPReport.Text);
foreach (var file in dirInfo.GetFiles("*.csv", SearchOption.TopDirectoryOnly))
{
SQL_Executor.Exec_SPU_With_Multple_Parameters_NoReturn(connString, "SPU_Insert_MP", "@TableName", tblMPName, "@FilePath", file.FullName);
}
string tblSCCMName = tb_TerBank.Text + "_SCCM_" + timeStamp;
SQL_Executor.Exec_SPU_With_Multple_Parameters_NoReturn(connString, "SPU_CreateTable_SCCM", "@TableName", tblSCCMName);
dirInfo = new DirectoryInfo(tb_PathSCCMReport.Text);
foreach (var file in dirInfo.GetFiles("*.csv", SearchOption.TopDirectoryOnly))
{
SQL_Executor.Exec_SPU_With_Multple_Parameters_NoReturn(connString, "SPU_Insert_SCCM", "@TableName", tblSCCMName, "@FilePath", file.FullName);
}
string tblSEPName = tb_TerBank.Text + "_SEP_" + timeStamp;
SQL_Executor.Exec_SPU_With_Multple_Parameters_NoReturn(connString, "SPU_CreateTable_SEP", "@TableName", tblSEPName);
dirInfo = new DirectoryInfo(tb_PathSEPReport.Text);
foreach (var file in dirInfo.GetFiles("*.csv", SearchOption.TopDirectoryOnly))
{
SQL_Executor.Exec_SPU_With_Multple_Parameters_NoReturn(connString, "SPU_Insert_SEP", "@TableName", tblSEPName, "@FilePath", file.FullName);
}
}
private void bt_LoadRevision_Click(object sender, EventArgs e)
{
dgv_Revisions.DataSource = null;
dgv_Revisions.DataSource = SQL_Executor.Exec_SPU_With_Multple_Parameters_Return_DS(connString, "SPU_SELECT_REVISION").Tables[0];
}
private void GetReport(string terBank, string timeStamp, bool isNewRevision = true)
{
if (isNewRevision)
{
SQL_Executor.Exec_SPU_With_Multple_Parameters_NoReturn(connString, "SPU_INSERT_PROBLEM_HOSTS", "@TerBankName", terBank, "@TimeStamp", timeStamp);
}
DataSet ds_ProblemArm_AllArm_Ad_Sccm_WithoutAVPO = SQL_Executor.Exec_SPU_With_Multple_Parameters_Return_DS(connString, "SPU_SELECT_PROBLEM_ARM_AD_SCCM", "@TerBankName", terBank, "@TimeStamp", timeStamp);
DataSet ds_ProblemArm_ProblemAVPO = SQL_Executor.Exec_SPU_With_Multple_Parameters_Return_DS(connString, "SPU_SELECT_PROBLEM_ARM_AVPO", "@TerBankName", terBank, "@TimeStamp", timeStamp);
DataSet ds_ProblemArm_SUMM;
if (chb_isKESOnARM.Checked)
{
ds_ProblemArm_SUMM = SQL_Executor.Exec_SPU_With_Multple_Parameters_Return_DS(connString, "SPU_SELECT_PROBLEM_ARM_SUMM_KES", "@TerBankName", terBank, "@TimeStamp", timeStamp);
}
else
{
ds_ProblemArm_SUMM = SQL_Executor.Exec_SPU_With_Multple_Parameters_Return_DS(connString, "SPU_SELECT_PROBLEM_ARM_SUMM_SEP", "@TerBankName", terBank, "@TimeStamp", timeStamp);
}
var workbook = new XLWorkbook("report_template_ARM.xlsx");
var ws1 = workbook.Worksheet(1);
ws1.Cell("A4").Value = terBank;
ws1.Cell("B4").Value = ds_ProblemArm_AllArm_Ad_Sccm_WithoutAVPO.Tables[0].Rows.Count + ds_ProblemArm_AllArm_Ad_Sccm_WithoutAVPO.Tables[1].Rows.Count;
ws1.Cell("D4").Value = ds_ProblemArm_AllArm_Ad_Sccm_WithoutAVPO.Tables[1].Rows.Count;
ws1.Cell("E4").Value = ds_ProblemArm_AllArm_Ad_Sccm_WithoutAVPO.Tables[2].Rows.Count;
ws1.Cell("F4").Value = ds_ProblemArm_AllArm_Ad_Sccm_WithoutAVPO.Tables[3].Rows.Count;
if (chb_isKESOnARM.Checked)
{
ws1.Cell("G4").Value = ds_ProblemArm_ProblemAVPO.Tables[0].Rows.Count;
ws1.Cell("H4").Value = ds_ProblemArm_ProblemAVPO.Tables[1].Rows.Count;
}
else
{
ws1.Cell("G4").Value = ds_ProblemArm_ProblemAVPO.Tables[2].Rows.Count;
ws1.Cell("H4").Value = ds_ProblemArm_ProblemAVPO.Tables[3].Rows.Count;
}
ws1.Cell("I4").Value = ds_ProblemArm_SUMM.Tables[0].Rows.Count;
ws1.Cell("K4").Value = timeStamp;
ds_ProblemArm_AllArm_Ad_Sccm_WithoutAVPO.Tables[0].TableName = "All_ARM";
ds_ProblemArm_AllArm_Ad_Sccm_WithoutAVPO.Tables[1].TableName = "Without_AD";
ds_ProblemArm_AllArm_Ad_Sccm_WithoutAVPO.Tables[2].TableName = "Without_SCCM";
ds_ProblemArm_AllArm_Ad_Sccm_WithoutAVPO.Tables[3].TableName = "Without_AVPO";
workbook.Worksheets.Add(ds_ProblemArm_AllArm_Ad_Sccm_WithoutAVPO);
ds_ProblemArm_ProblemAVPO.Tables[0].TableName = "KES_Old_Client";
ds_ProblemArm_ProblemAVPO.Tables[1].TableName = "KES_Old_Base";
ds_ProblemArm_ProblemAVPO.Tables[2].TableName = "SEP_Old_Client";
ds_ProblemArm_ProblemAVPO.Tables[3].TableName = "SEP_Old_Base";
if (chb_isKESOnARM.Checked)
{
workbook.Worksheets.Add(ds_ProblemArm_ProblemAVPO.Tables[0]);
workbook.Worksheets.Add(ds_ProblemArm_ProblemAVPO.Tables[1]);
}
else
{
workbook.Worksheets.Add(ds_ProblemArm_ProblemAVPO.Tables[2]);
workbook.Worksheets.Add(ds_ProblemArm_ProblemAVPO.Tables[3]);
}
workbook.SaveAs(tb_PathSumReport.Text + @"\" + terBank + "_ARM_Rep_" + timeStamp + ".xlsx");
DataSet ds_ProblemServ_AllSERV_Ad_Sccm_WithoutAVPO = SQL_Executor.Exec_SPU_With_Multple_Parameters_Return_DS(connString, "SPU_SELECT_PROBLEM_SERV_AD_SCCM", "@TerBankName", terBank, "@TimeStamp", timeStamp);
DataSet ds_ProblemServ_ProblemAVPO = SQL_Executor.Exec_SPU_With_Multple_Parameters_Return_DS(connString, "SPU_SELECT_PROBLEM_SERV_AVPO", "@TerBankName", terBank, "@TimeStamp", timeStamp);
DataSet ds_ProblemServ_SUMM;
if (chb_isKESOnARM.Checked)
{
ds_ProblemServ_SUMM = SQL_Executor.Exec_SPU_With_Multple_Parameters_Return_DS(connString, "SPU_SELECT_PROBLEM_SERV_SUMM_SEP", "@TerBankName", terBank, "@TimeStamp", timeStamp);
}
else
{
ds_ProblemServ_SUMM = SQL_Executor.Exec_SPU_With_Multple_Parameters_Return_DS(connString, "SPU_SELECT_PROBLEM_SERV_SUMM_KES", "@TerBankName", terBank, "@TimeStamp", timeStamp);
}
var workbook1 = new XLWorkbook("report_template_SERV.xlsx");
var ws2 = workbook1.Worksheet(1);
ws2.Cell("A4").Value = terBank;
ws2.Cell("B4").Value = ds_ProblemServ_AllSERV_Ad_Sccm_WithoutAVPO.Tables[0].Rows.Count;
ws2.Cell("D4").Value = ds_ProblemServ_AllSERV_Ad_Sccm_WithoutAVPO.Tables[1].Rows.Count;
ws2.Cell("E4").Value = ds_ProblemServ_AllSERV_Ad_Sccm_WithoutAVPO.Tables[2].Rows.Count;
ws2.Cell("F4").Value = ds_ProblemServ_AllSERV_Ad_Sccm_WithoutAVPO.Tables[3].Rows.Count;
if (chb_isKESOnARM.Checked)
{
ws2.Cell("G4").Value = ds_ProblemServ_ProblemAVPO.Tables[2].Rows.Count;
ws2.Cell("H4").Value = ds_ProblemServ_ProblemAVPO.Tables[3].Rows.Count;
}
else
{
ws2.Cell("G4").Value = ds_ProblemServ_ProblemAVPO.Tables[0].Rows.Count;
ws2.Cell("H4").Value = ds_ProblemServ_ProblemAVPO.Tables[1].Rows.Count;
}
//Trace.WriteLine($"ws2: {ws2}");
ws2.Cell("I4").Value = ds_ProblemServ_SUMM.Tables[0].Rows.Count;
ws2.Cell("K4").Value = timeStamp;
ds_ProblemServ_AllSERV_Ad_Sccm_WithoutAVPO.Tables[0].TableName = "All_SERV";
ds_ProblemServ_AllSERV_Ad_Sccm_WithoutAVPO.Tables[1].TableName = "Without_AD";
ds_ProblemServ_AllSERV_Ad_Sccm_WithoutAVPO.Tables[2].TableName = "Without_SCCM";
ds_ProblemServ_AllSERV_Ad_Sccm_WithoutAVPO.Tables[3].TableName = "Without_AVPO";
workbook1.Worksheets.Add(ds_ProblemServ_AllSERV_Ad_Sccm_WithoutAVPO);
ds_ProblemServ_ProblemAVPO.Tables[0].TableName = "KES_Old_Client";
ds_ProblemServ_ProblemAVPO.Tables[1].TableName = "KES_Old_Base";
ds_ProblemServ_ProblemAVPO.Tables[2].TableName = "SEP_Old_Client";
ds_ProblemServ_ProblemAVPO.Tables[3].TableName = "SEP_Old_Base";
if (chb_isKESOnARM.Checked)
{
workbook1.Worksheets.Add(ds_ProblemServ_ProblemAVPO.Tables[2]);
workbook1.Worksheets.Add(ds_ProblemServ_ProblemAVPO.Tables[3]);
}
else
{
workbook1.Worksheets.Add(ds_ProblemServ_ProblemAVPO.Tables[0]);
workbook1.Worksheets.Add(ds_ProblemServ_ProblemAVPO.Tables[1]);
}
workbook1.SaveAs(tb_PathSumReport.Text + @"\" + terBank + "_SERV_Rep_" + timeStamp + ".xlsx");
}
