static void Main(string[] args)
{
// If start with no args will use the practice db. Must start with the argument -p to use the production db PID Chekcing not done in PracticeDB
Debug.Listeners.Add(new TextWriterTraceListener(Console.Out));
Debug.AutoFlush = true;
Debug.Indent();
Debug.WriteLine("Starting excel access");
WorkBookClass xcel = new WorkBookClass((args.Length > 0) ? args[0] : string.Empty); // opens spreadsheet brings spreadsheet data across into a List(rowData)
MySql dbAccess = new MySql((args.Length > 0) ? args[0] : string.Empty ); // initializes connection to database
//process rows xcel method have to pass datbase object so that can do database method calls from xcel
dbAccess.GetDataSet(xcel.rowList);
xcel.ProcessRows(dbAccess); //passing dbaccess so method can access license and inventory datatables
Debug.WriteLine("about to update sheet");
//add lines at bottom of spreadsheet with update summary and table inquiry SQL
xcel.ResultUserMess(dbAccess.UpdateSysLicTable().ToString(), dbAccess.UpdateInventoryTable().ToString());
dbAccess.CloseConn();
xcel.Dispose();
}
private void InvDbAnalysis(MySql dbAccess, RowData row)
{
var qry = from DataRow record in dbAccess.invLicCombined.Rows
where ((record["mr_serial_number"] != DBNull.Value) ? (string)record["mr_serial_number"] : "") == row.mSerNo && ((record["mr_manufacturer"] != DBNull.Value) ? (string)record["mr_manufacturer"] : "") == row.mMfr
select new { rec = record, indx = dbAccess.invLicCombined.Rows.IndexOf(record) };
switch (qry.Count())
{
case 0:
row.mRsult = "No Entry in Inventory or License Database";
UpdateWSheetRow(rowList.IndexOf(row));
return;
case 1:
break;
default:
row.mRsult = "Multiple Entries in Inventory Database - major DB error";
UpdateWSheetRow(rowList.IndexOf(row));
return;
}
//at this point have only 1 record which is correct and desirable
DataRow rowInvTable = qry.ElementAtOrDefault(0).rec; //row in license table to be processed
int invTableRow = qry.ElementAtOrDefault(0).indx; //index of that row - needed for later updating of the database after row processing
Func<bool> testIfActive000 = () => ((sbyte)rowInvTable["active"] & 1) == 0;
Func<bool> qualified4Nlk00 = () => ((sbyte)rowInvTable["active"] & 2) == 2;
Func<bool> localW7LicAvail = () => ((sbyte)rowInvTable["inv_flags"] & 4) == 4;
Func<bool> locW7withKtype0 = () => (string.IsNullOrWhiteSpace((string)rowInvTable["os_product_key_type"])) == false;
//Func<bool> locW7withlast5c = () => (string.IsNullOrWhiteSpace((string)rowInvTable["os_partial_product_key"])) == false;
Action errMessageNotActiv = () => { row.mRsult = "Flagged as Not Active System in Inventory database"; };
Action errMessNotW7Eligab = () => { row.mStatus = "System in Inventory DB, but is not eligible for NLK Win 7 installation"; ProcessNewKeyInv(row, "System Status updated in Inventory DB to allow key download and install", rowInvTable); };
Action statusPartKeyExist = () => { row.mStatus = "Windows 7, Key Type is " + rowInvTable["os_product_key_type"] + " with last 5 of " + rowInvTable["os_partial_product_key"]; ProcessNewKeyInv(row, "Inventory DB Product Key updated and ready for download", rowInvTable); };
Action statusWindowsXP000 = () => { row.mStatus = "Windows XP or No data for Windows 7"; ProcessNewKeyInv(row, "Inventory DB Product Key added and is ready for download", rowInvTable); };
//Action statusNlkEligible0 = () => { row.mStatus = "NLK Eligible"; ProcessNewKeyInv(row, "Product Key added and is ready for download", rowInvTable); };
LogicTable.Start()
.Condition(testIfActive000, "TFFF")
.Condition(qualified4Nlk00, "-F-T")
.Condition(localW7LicAvail, "-FT-")
.Condition(locW7withKtype0, "---F")
.Action(errMessageNotActiv, "X ")
.Action(errMessNotW7Eligab, " X ") // W7 Home premium will end on this action
.Action(statusPartKeyExist, " X ")
.Action(statusWindowsXP000, " X");
dbAccess.invLicCombined.Rows[invTableRow]["active"] = rowInvTable["active"];
dbAccess.invLicCombined.Rows[invTableRow]["inv_flags"] = rowInvTable["inv_flags"];
dbAccess.invLicCombined.Rows[invTableRow]["os_product_key"] = rowInvTable["os_product_key"];
UpdateWSheetRow(rowList.IndexOf(row));
if (sqlQryResultInventory.Length != 0)
sqlQryResultInventory.Append(" OR ");
sqlQryResultInventory.Append("(`mr_manufacturer` = " + "'" + row.mMfr + "' AND " + "`mr_serial_number` = " + "'" + row.mSerNo + "')");
}
internal void ProcessRows(MySql dbAccess)
{
userMessageCell.Value2 = "Updating local copy of license and inventory database entries";
foreach (var row in rowList)
{
if (dbAccess.systemlicEx == null)
{
row.mRsult = "No Entry in License Database";
UpdateWSheetRow(rowList.IndexOf(row));
InvDbAnalysis(dbAccess, row);
continue;
}
var qry = from DataRow record in dbAccess.systemlicEx.Rows
where ((record["mr_serial_number"] != DBNull.Value) ? (string)record["mr_serial_number"] : "") == row.mSerNo && ((record["mr_manufacturer"] != DBNull.Value) ? (string)record["mr_manufacturer"] : "") == row.mMfr
select new { rec = record, indx = dbAccess.systemlicEx.Rows.IndexOf(record) };
switch (qry.Count())
{
case 0:
row.mRsult = "No Entry in License Database";
UpdateWSheetRow(rowList.IndexOf(row));
InvDbAnalysis(dbAccess, row);
continue;
case 1:
break;
default:
row.mRsult = "Multiple Entries in License Database - major DB error";
UpdateWSheetRow(rowList.IndexOf(row));
continue;
}
//at this point have only 1 record which is correct and desirable
DataRow rowlicTable = qry.ElementAtOrDefault(0).rec; //row in license table to be processed
int licTableRow = qry.ElementAtOrDefault(0).indx; //index of that row - needed for later updating of the database after row processing
Func<bool> notValid0000000 = () => ((sbyte)rowlicTable["valid"] & 1) == 0;
Func<bool> forceKeyUpload0 = () => ((sbyte)rowlicTable["valid"] & 4) == 4;
Func<bool> forcKeyUplodOEM = () => ((string)rowlicTable["product_code"]) == "OEM:SLP";
Func<bool> keyfordownload0 = () => ((sbyte)rowlicTable["valid"] & 2) == 2;
Func<bool> nlkfordownload0 = () => ((string)rowlicTable["product_code"]) == "NLK";
Action statusNotValid0000 = () => row.mRsult = "Flagged \"Not Valid\" in License Database";
Action statusKeyUpload000 = () => ProcessNewKeyLic(row, "Upload Product Key - VLK(Techsoup??)", rowlicTable);
Action statusKeyUploadOEM = () => ProcessNewKeyLic(row, "Upload OEM COA Product Key", rowlicTable);
Action statKeyForDownload = () => { string l5 = GetExistKeyLast5(rowlicTable); ProcessNewKeyLic(row, "Product Key Ending " + l5 + " available for download", rowlicTable); };
Action statNLKForDownload = () => ProcessNewKeyLic(row, "NLK available for download", rowlicTable);
LogicTable.Start()
.Condition(notValid0000000, "TFFFF")
.Condition(forceKeyUpload0, "-TTFF")
.Condition(forcKeyUplodOEM, "-FTFF")
.Condition(keyfordownload0, "-FFTF")
.Condition(nlkfordownload0, "-FF-T")
.Action(statusNotValid0000, "X ")
.Action(statusKeyUploadOEM, " X ")
.Action(statusKeyUpload000, " X ")
.Action(statKeyForDownload, " X ")
.Action(statNLKForDownload, " X");
dbAccess.systemlicEx.Rows[licTableRow]["valid"] = rowlicTable["valid"];
dbAccess.systemlicEx.Rows[licTableRow]["product_code"] = rowlicTable["product_code"];
UpdateWSheetRow(rowList.IndexOf(row));
if (sqlQryResultLicense.Length > 0)
sqlQryResultLicense.Append(" OR ");
sqlQryResultLicense.Append("(`mr_manufacturer` = " + "'" + row.mMfr + "' AND " + "`mr_serial_number` = " + "'" + row.mSerNo + "')");
}
}
internal void ProcessRows(MySql dbAccess)
{
userMessageCell.Value2 = "Updating local copy of license and inventory database entries";
foreach (var row in rowList)
{
if (dbAccess.systemlicEx == null)
{
row.mRsult = "No Entry in License Database";
UpdateWSheetRow(rowList.IndexOf(row));
InvDbAnalysis(dbAccess, row);
continue;
}
var qry = from DataRow record in dbAccess.systemlicEx.Rows
where ((record["mr_serial_number"] != DBNull.Value) ? (string)record["mr_serial_number"] : "") == row.mSerNo && ((record["mr_manufacturer"] != DBNull.Value) ? (string)record["mr_manufacturer"] : "") == row.mMfr
select new { rec = record, indx = dbAccess.systemlicEx.Rows.IndexOf(record) };
switch (qry.Count())
{
case 0:
row.mRsult = "No Entry in License Database";
UpdateWSheetRow(rowList.IndexOf(row));
InvDbAnalysis(dbAccess, row);
continue;
case 1:
break;
default:
row.mRsult = "Multiple Entries in License Database - major DB error";
UpdateWSheetRow(rowList.IndexOf(row));
continue;
}
//at this point have only 1 record which is correct and desirable
DataRow rowlicTable = qry.ElementAtOrDefault(0).rec; //row in license table to be processed
int licTableRow = qry.ElementAtOrDefault(0).indx; //index of that row - needed for later updating of the database after row processing
Func <bool> notValid0000000 = () => ((sbyte)rowlicTable["valid"] & 1) == 0;
Func <bool> forceKeyUpload0 = () => ((sbyte)rowlicTable["valid"] & 4) == 4;
Func <bool> forcKeyUplodOEM = () => ((string)rowlicTable["product_code"]) == "OEM:SLP";
Func <bool> keyfordownload0 = () => ((sbyte)rowlicTable["valid"] & 2) == 2;
Func <bool> nlkfordownload0 = () => ((string)rowlicTable["product_code"]) == "NLK";
Action statusNotValid0000 = () => row.mRsult = "Flagged \"Not Valid\" in License Database";
Action statusKeyUpload000 = () => ProcessNewKeyLic(row, "Upload Product Key - VLK(Techsoup??)", rowlicTable);
Action statusKeyUploadOEM = () => ProcessNewKeyLic(row, "Upload OEM COA Product Key", rowlicTable);
Action statKeyForDownload = () => { string l5 = GetExistKeyLast5(rowlicTable); ProcessNewKeyLic(row, "Product Key Ending " + l5 + " available for download", rowlicTable); };
Action statNLKForDownload = () => ProcessNewKeyLic(row, "NLK available for download", rowlicTable);
LogicTable.Start()
.Condition(notValid0000000, "TFFFF")
.Condition(forceKeyUpload0, "-TTFF")
.Condition(forcKeyUplodOEM, "-FTFF")
.Condition(keyfordownload0, "-FFTF")
.Condition(nlkfordownload0, "-FF-T")
.Action(statusNotValid0000, "X ")
.Action(statusKeyUploadOEM, " X ")
.Action(statusKeyUpload000, " X ")
.Action(statKeyForDownload, " X ")
.Action(statNLKForDownload, " X");
dbAccess.systemlicEx.Rows[licTableRow]["valid"] = rowlicTable["valid"];
dbAccess.systemlicEx.Rows[licTableRow]["product_code"] = rowlicTable["product_code"];
UpdateWSheetRow(rowList.IndexOf(row));
if (sqlQryResultLicense.Length > 0)
{
sqlQryResultLicense.Append(" OR ");
}
sqlQryResultLicense.Append("(`mr_manufacturer` = " + "'" + row.mMfr + "' AND " + "`mr_serial_number` = " + "'" + row.mSerNo + "')");
}
}
