/*
* Function : CreateRow()
* Description : Creates a new rowInfo object to be added onto the weeklyRowDict/dailyRowDict
* for a product name passed.
* Params : (string) productName
* (bool) weeklyRowExist
* (bool) dailyRowExist
* (bool) wigigRowExist
* (ref Dictionary<string, List<rowInfo>>) weeklyRowDict
* (ref Dictionary<string, List<rowInfo>>) dailyRowDict
* (ref Dictionary<string, List<rowInfo>>) wigigRowDict
* (XmlNode) uut
* (FileInfo) zip
* (string) workWeek
* (string) workDay
* Return :
*/
public void CreateRow(string productName, bool weeklyRowExist, bool dailyRowExist, bool wigigRowExist, ref Dictionary <string, List <rowInfo> > weeklyRowDict, ref Dictionary <string, List <rowInfo> > dailyRowDict, ref Dictionary <string, List <rowInfo> > wigigRowDict, XmlNode uut, FileInfo zip, string workWeek, string workDay)
{
// Create the rowInfo object
rowInfo weeklyRow = new rowInfo();
rowInfo dailyRow = new rowInfo();
rowInfo wigigRow = new rowInfo();
// Set attributes then check/update attributes totalboards and failedboards
weeklyRow.WorkYear = dailyRow.WorkYear = wigigRow.WorkYear = uut["TestTime"].InnerText.Substring(6, 4);
weeklyRow.WorkWeek = dailyRow.WorkWeek = wigigRow.WorkWeek = workWeek;
weeklyRow.ODM = dailyRow.ODM = wigigRow.ODM = zip.Name.Substring(8, 2);
if (uut["Indicator_1A2A"].InnerText.Equals("1A"))
{
weeklyRow.TotalBoards += 1;
dailyRow.TotalBoards += 1;
wigigRow.TotalBoards += 1;
}
if (string.Equals(uut["ErrorCode"].InnerText, "WIFI_FT_ERR-034",
StringComparison.CurrentCultureIgnoreCase))
{
weeklyRow.FailedBoards += 1;
dailyRow.FailedBoards += 1;
}
else if (string.Equals(uut["ErrorCode"].InnerText, "WIGIG_FT-ERR-128",
StringComparison.CurrentCultureIgnoreCase))
{
wigigRow.FailedBoards += 1;
}
// Check a couple attributes for values of Null, Empty, WhiteSpace
// to avoid adding empty rows
if (!string.IsNullOrEmpty(weeklyRow.WorkYear) &&
!string.IsNullOrEmpty(weeklyRow.WorkWeek) &&
!string.IsNullOrWhiteSpace(weeklyRow.WorkYear) &&
!string.IsNullOrWhiteSpace(weeklyRow.WorkWeek))
{
if (!weeklyRowExist)
{
weeklyRowDict[productName].Add(weeklyRow);
}
if (!dailyRowExist)
{
dailyRow.WorkDay = workDay;
dailyRowDict[productName].Add(dailyRow);
}
if (!wigigRowExist)
{
wigigRow.WorkDay = workDay;
wigigRowDict[productName].Add(wigigRow);
}
}
}
/*
* Function : gatherSpreadsheet()
* Description : Gets data from excel spreadsheet and store it into the dictionaries accordingly.
* Params : (ref Dictionary<string, List<rowInfo>>) weeklyRowDict
* (ref Dictionary<string, List<rowInfo>>) dailyRowDict
* (ref Dictionary<string, List<rowInfo>>) wigigRowDict
* (string) dateString
* Return :
*/
public void GatherSpreadsheet(ref Dictionary <string, List <rowInfo> > weeklyRowDict, ref Dictionary <string, List <rowInfo> > dailyRowDict, ref Dictionary <string, List <rowInfo> > wigigRowDict, string dateString)
{
p.logger("[DataRetrieval.cs] Gathering data from spreadsheets");
string[] cx = Directory.GetFiles(ConfigurationManager.AppSettings["reportPath"], dateString + "_CX*");
string[] wz = Directory.GetFiles(ConfigurationManager.AppSettings["reportPath"], dateString + "_WZ*");
string[] cx_wigig = Directory.GetFiles(ConfigurationManager.AppSettings["reportPath"], dateString + "_WIGIG_CX*");
string[] fs = Directory.GetFiles(ConfigurationManager.AppSettings["reportPath"], dateString + "_FS*");
string[] odmList = { "CX", "WZ", "FS" };
foreach (string ODM in odmList)
{
var application = new Microsoft.Office.Interop.Excel.Application();
application.Visible = false;
p.logger("[DataRetrieval.cs] Gathering data from " + ODM);
// Open up the Excel sheet for the current ODM
Workbooks workbooks = application.Workbooks;
Workbook workbook = null;
if (ODM.Equals("CX") && cx.Length > 0)
{
workbook = workbooks.Open(cx[0], ReadOnly: false);
}
else if (ODM.Equals("WZ") && wz.Length > 0)
{
workbook = workbooks.Open(wz[0], ReadOnly: false);
}
else if (ODM.Equals("FS") && fs.Length > 0)
{
workbook = workbooks.Open(fs[0], ReadOnly: false);
}
Process pid = GetProcess(application);
if (workbook == null)
{
continue;
}
var worksheets = workbook.Worksheets;
// Iterate over the products' worksheets to insert data
for (int i = 1; i <= worksheets.Count; i++)
{
Worksheet worksheet = (Worksheet)workbook.Worksheets[i];
var rowCountTemp = worksheet.UsedRange;
var rowCountTemp2 = rowCountTemp.Rows;
int rowCount = rowCountTemp2.Count - 1;
bool insertDailyData = false;
int titleCount = 0;
if (rowCountTemp != null)
{
Marshal.ReleaseComObject(rowCountTemp);
}
if (rowCountTemp2 != null)
{
Marshal.ReleaseComObject(rowCountTemp2);
}
// Start at the 2nd position because the first position contains the column titles.
// Iterate over all of the used rows to get data and save data to dictionaries.
for (int j = 2; j < (rowCount + 2); j++)
{
int k = 0;
var rangeTemp = worksheet.UsedRange;
Range range1 = rangeTemp.Rows[j];
rowInfo row = new rowInfo();
if (rangeTemp != null)
{
Marshal.ReleaseComObject(rangeTemp);
}
foreach (Range r in range1.Cells)
{
string value = r.Text;
if ((string.IsNullOrEmpty(value) || string.IsNullOrWhiteSpace(value)) && (titleCount < 6))
{
insertDailyData = true;
if (value.Equals("Work Year") ||
value.Equals("Work Week") ||
value.Equals("Work Day") ||
value.Equals("Failed Boards") ||
value.Equals("Total Boards") ||
value.Equals("Failure Rate (%)"))
{
titleCount++;
}
}
if (!insertDailyData)
{
switch (k)
{
case 0:
row.WorkYear = value;
break;
case 1:
row.WorkWeek = value;
break;
case 2:
int temp;
Int32.TryParse(value, out temp);
row.FailedBoards = temp;
break;
case 3:
int temp2;
Int32.TryParse(value, out temp2);
row.TotalBoards = temp2;
break;
case 4:
row.FailureRate = value;
break;
case 5:
row.Goal = value;
break;
}
}
else
{
if (string.IsNullOrEmpty(value) ||
string.IsNullOrWhiteSpace(value) ||
value.Equals("Work Year") ||
value.Equals("Work Week") ||
value.Equals("Work Day") ||
value.Equals("Failed Boards") ||
value.Equals("Total Boards") ||
value.Equals("Failure Rate (%)"))
{
continue;
}
else
{
switch (k)
{
case 0:
row.WorkYear = value;
break;
case 1:
row.WorkWeek = value;
break;
case 2:
row.WorkDay = value;
break;
case 3:
int temp;
Int32.TryParse(value, out temp);
row.FailedBoards = temp;
break;
case 4:
int temp2;
Int32.TryParse(value, out temp2);
row.TotalBoards = temp2;
break;
case 5:
row.FailureRate = value;
break;
}
}
}
k++;
if (r != null)
{
Marshal.ReleaseComObject(r);
}
}
row.ODM = ODM;
if (string.IsNullOrEmpty(row.WorkYear) ||
string.IsNullOrEmpty(row.WorkWeek) ||
string.IsNullOrWhiteSpace(row.WorkYear) ||
string.IsNullOrWhiteSpace(row.WorkWeek))
{
continue;
}
if (!insertDailyData)
{
weeklyRowDict[worksheet.Name].Add(row);
}
else
{
dailyRowDict[worksheet.Name].Add(row);
}
if (range1 != null)
{
Marshal.ReleaseComObject(range1);
}
}
if (worksheet != null)
{
Marshal.ReleaseComObject(worksheet);
}
}
// Clean up the COM objects to remove "EXCEL.EXE" process
if (worksheets != null)
{
Marshal.ReleaseComObject(worksheets);
}
if (workbooks != null)
{
Marshal.ReleaseComObject(workbooks);
}
if (workbook != null)
{
workbook.Close(Type.Missing, Type.Missing, Type.Missing);
Marshal.ReleaseComObject(workbook);
}
if (application != null)
{
application.Quit();
Marshal.ReleaseComObject(application);
}
pid.Kill();
p.logger("[DataRetrieval.cs] Finished gathering data for " + ODM);
}
if (cx_wigig.Length <= 0)
{
return;
}
// Gather CX WIGIG data from spreadsheet
var app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
Workbooks wbs = app.Workbooks;
Workbook wb = wbs.Open(cx_wigig[0], ReadOnly: false);
Process pidWigig = GetProcess(app);
p.logger("[DataRetrieval.cs] Gathering data for WIGIG");
var ws = wb.Worksheets;
for (int i = 1; i <= ws.Count; i++)
{
Worksheet worksheet = (Worksheet)wb.Worksheets[i];
var rowCountTemp = worksheet.UsedRange;
var rowCountTemp2 = rowCountTemp.Rows;
int rowCount = rowCountTemp2.Count - 1;
if (rowCountTemp != null)
{
Marshal.ReleaseComObject(rowCountTemp);
}
if (rowCountTemp2 != null)
{
Marshal.ReleaseComObject(rowCountTemp2);
}
for (int j = 2; j < (rowCount + 2); j++)
{
int k = 0;
var rangeTemp = worksheet.UsedRange;
Range range1 = rangeTemp.Rows[j];
rowInfo row = new rowInfo();
if (rangeTemp != null)
{
Marshal.ReleaseComObject(rangeTemp);
}
foreach (Range r in range1.Cells)
{
string value = r.Text;
switch (k)
{
case 0:
row.WorkYear = value;
break;
case 1:
row.WorkWeek = value;
break;
case 2:
row.WorkDay = value;
break;
case 3:
int temp;
Int32.TryParse(value, out temp);
row.FailedBoards = temp;
break;
case 4:
int temp2;
Int32.TryParse(value, out temp2);
row.TotalBoards = temp2;
break;
case 5:
row.FailureRate = value;
break;
}
k++;
if (r != null)
{
Marshal.ReleaseComObject(r);
}
}
row.ODM = "CX";
if (string.IsNullOrEmpty(row.WorkYear) ||
string.IsNullOrEmpty(row.WorkWeek) ||
string.IsNullOrWhiteSpace(row.WorkYear) ||
string.IsNullOrWhiteSpace(row.WorkWeek))
{
continue;
}
else
{
wigigRowDict[worksheet.Name].Add(row);
}
if (range1 != null)
{
Marshal.ReleaseComObject(range1);
}
}
if (worksheet != null)
{
Marshal.ReleaseComObject(worksheet);
}
}
if (ws != null)
{
Marshal.ReleaseComObject(ws);
}
if (wbs != null)
{
Marshal.ReleaseComObject(wbs);
}
if (wb != null)
{
wb.Close(Type.Missing, Type.Missing, Type.Missing);
Marshal.ReleaseComObject(wb);
}
if (app != null)
{
app.Quit();
Marshal.ReleaseComObject(app);
}
pidWigig.Kill();
p.logger("[DataRetrieval.cs] Finished gathering data for WIGIG");
}