public void PrepareDataForExport(DataTable dataTable, string dataObject, string sqlOperation = "INSERT")
{
try
{
// don't get po_no for "JobPOs" list - wait until list is distilled to items matching JobCstActs, etc
if (!dataObject.Equals("JobPOs"))
{
if (!dataObject.Equals("JobVPOs"))
{
dataTable.Columns.Add("po_type");
dataTable.Columns.Add("po_no");
}
foreach (DataRow row in dataTable.Rows)
{
PrepareDataRowForExport(row, dataObject, sqlOperation);
}
// write final output to csv file
string Filename = _serviceLog.WriteToFile(dataTable, dataObject);
_serviceLog.AppendLog(String.Format("{0} Sapphire {1} records found missing in Pervasive", dataTable.Rows.Count, dataObject));
_serviceLog.AppendLog(String.Format("{0} {1} records written to file {2}", dataTable.Rows.Count, dataObject, Filename));
}
}
catch (Exception ex)
{
_serviceLog.AppendLog(ex.ToString());
}
}
private void CreateMissingJobCstActs(DataTable recordsToInsert, string dbName, string sqlOperation = "INSERT", string activity = "")
{
try
{
recordsToInsert.Columns.Add("po_type");
bool isFirstRow = true;
foreach (DataRow row in recordsToInsert.Rows)
{
if (InsertJobCstActsRecord(row))
{
if (isFirstRow)
{
_serviceLog.AppendLogMessage(String.Format("\nThe following activities have been created in {0} for job {1}:", dbName, row["NewJobNumber"]));
_serviceLog.AppendLogMessage("NewJobNumber\t\tActivity");
isFirstRow = false;
}
_serviceLog.AppendLogMessage($"{row["NewJobNumber"]}\t\t{row["activity"]}");
}
}
}
catch (Exception ex)
{
_serviceLog.AppendLog(ex.ToString());
}
}
public void ImportPOSyncData()
{
// Always truncate for now..
bool truncate = true;
/*
* Console.Write("Truncate table? (Y/N) ");
* if (Console.ReadLine() != "Y") truncate = false;
*/
_serviceLog.AppendLog(String.Format("Importing PO Sync Data{0}", truncate ? " with truncate" : " with update"));
ImportPOSyncData(truncate);
}
// ***********************
// TODO:
// need to turn this INSERT into pSQL Stored Procedure in order to get new po_no at time of insert
// ***********************
public string CreateJobPOSQLInsert(DataRow row, string company = "")
{
try
{
int po_no = int.TryParse(GetPONumber(row["JobID"].ToString(), company), out int result) ? result : 0;
string sql = "INSERT INTO \"" + company + "\".PO_HEADER" +
"(po_no, NewJobNumber, job_no, activity, po_type, egm_amount, vpo_yes_no, vendor_id, " +
"release_date, ReleaseTime, cancelled_date, CancelTime, payment_date, " +
"payment_amount, subtotal, tax, total, taxable_amount, TaxRate, ApprovePaymentDate, " +
"community, product, building, unit, eSubmittalDate, " +
"UserID, LastModifiedDate, LastModifiedTime, " +
"SapphirePONumber, SapphireObjID, SapphireObjRID) " +
"VALUES (" + po_no + ", " +
"'" + row["NewJobNumber"].ToString() + "', " +
"'" + row["job_no"].ToString() + "', " +
"'" + row["activity"].ToString() + "', " +
"'" + row["po_type"].ToString() + "', " +
"0, " +
"0, " +
"'" + row["Vendor_ID"].ToString() + "', " +
(_dateService.IsNullSapphireDate(row["DateReleased"]) ? "NULL" : "'" + _dateService.FormatDateForPervasive(row["DateReleased"]) + "'") + ", " +
(_dateService.IsNullSapphireDate(row["DateReleased"]) ? "NULL" : "'" + _dateService.FormatTimeForPervasive(row["DateReleased"]) + "'") + ", " +
(_dateService.IsNullSapphireDate(row["DateCancelled"]) ? "NULL" : "'" + _dateService.FormatDateForPervasive(row["DateCancelled"]) + "'") + ", " +
(_dateService.IsNullSapphireDate(row["DateCancelled"]) ? "NULL" : "'" + _dateService.FormatTimeForPervasive(row["DateCancelled"]) + "'") + ", " +
(_dateService.IsNullSapphireDate(row["DatePaid"]) ? "NULL" : "'" + _dateService.FormatDateForPervasive(row["DatePaid"]) + "'") + ", " +
row["AmtPaid"].ToString() + ", " +
row["AmtSubtotal"].ToString() + ", " +
row["AmtTax"].ToString() + ", " +
row["AmtTotal"].ToString() + ", " +
row["AmtTaxable"].ToString() + ", " +
row["TaxPercentage"].ToString() + ", " +
(_dateService.IsNullSapphireDate(row["DateApproved"]) ? "NULL" : "'" + _dateService.FormatDateForPervasive(row["DateApproved"]) + "'") + ", " +
"'" + row["job_no"].ToString().Substring(0, 3) + "', " +
"'" + row["job_no"].ToString().Substring(3, 2) + "', " +
"'" + row["job_no"].ToString().Substring(5, 3) + "', " +
"'" + row["job_no"].ToString().Substring(8, 4) + "', " +
(_dateService.IsNullSapphireDate(row["DateComplByVnd"]) ? "NULL" : "'" + _dateService.FormatDateForPervasive(row["DateComplByVnd"]) + "'") + ", " +
"'" + "DI-Reconciliation" + "', " +
" CURDATE(), " +
" CURTIME(), " +
"'" + row["SapphirePONumber"].ToString() + "', " +
"'" + row["SapphireObjID"].ToString() + "', " +
"" + (int)row["SapphireObjRID"] + ");";
return(sql);
}
catch (Exception ex)
{
_serviceLog.AppendLog(ex.Message, "", ex);
return(null);
}
}
private void ReconcileJobVPORecords()
{
Console.WriteLine("1 - Reconcile JobVPO Approval Dates");
Console.WriteLine("2 - Reconcile JobVPO Approval/eSubmittal/Cancel Dates");
if (int.TryParse(Console.ReadLine(), out int result))
{
switch (result)
{
case 1:
// Check that pervasive data matches sapphire data based on the following logic:
// If JobVPOs.Total < 0 and JobVPOs.Status is "Approved" or "Authorized", set po_header.approval_date = JobVPOs.DateAuthorized
_serviceLog.AppendLog("Reconcile Job VPO Approval Dates");
this.dataObject = "JobVPOs";
_variancePurchaseOrderService.ReconcileJobVPOApprovalDates();
break;
case 2:
_serviceLog.AppendLog("Reconcile Job VPO Approval/eSubmittal/Cancel Dates");
this.dataObject = "JobVPOs";
_variancePurchaseOrderService.ReconcileJobVPOApprovedAndCompletedDates();
break;
default:
_consoleLogger.Info("Please select a valid option.");
ReconcileJobVPORecords();
break;
}
}
else
{
_consoleLogger.Info("Please select a valid option.");
ReconcileJobVPORecords();
}
}
public DataTable GetQueueRecords(string dataObject = "")
{
var queueQuery = GetQueueQueryString();
if (!string.IsNullOrEmpty(queueQuery))
{
var connString = ConfigurationManager.ConnectionStrings["QueueDbContext"].ConnectionString;
_serviceLog.AppendLog(String.Format(" Getting Queue {0} records", dataObject));
var queueRecords = _sqlClient.QueryADO(queueQuery, connString);
_serviceLog.AppendLog(String.Format(" {0} Queue {1} records found", queueRecords.Rows.Count, dataObject));
if (queueRecords != null && queueRecords.Rows.Count != 0)
{
return(queueRecords);
}
}
return(null);
}
public string CreateJobCostEGMAmountsUpdateSQL(DataRow row, string company = "")
{
try
{
return("UPDATE \"" + company + "\".PO_HEADER" +
" SET " +
" egm_amount = " + row["sap_egm_amt"] + ", " +
" UserID = 'DI-Reconciliation', " +
" LastModifiedDate = CURDATE(), " +
" LastModifiedTime = CURTIME() " +
" WHERE" +
" job_no = '" + row["JobID"].ToString().Substring(0, 12) + "'" +
" AND activity = " + row["ActID"] +
";");
}
catch (Exception ex)
{
_serviceLog.AppendLog("Error creating POProcessingDatesUpdate SQL.", "", ex);
return(null);
}
}
public bool DatesAreEqual(object sapphireDate, object pervasiveDate, bool compareTimes = true)
{
try
{
string sapphireDateStringToCompare = FormatDateForCompare(sapphireDate, compareTimes);
string pervasiveDateStringToCompare = FormatDateForCompare(pervasiveDate, compareTimes);
if (String.IsNullOrEmpty(sapphireDateStringToCompare) && String.IsNullOrEmpty(pervasiveDateStringToCompare))
{
return(true);
}
if (sapphireDateStringToCompare.Equals(pervasiveDateStringToCompare))
{
return(true);
}
return(false);
}
catch (Exception ex)
{
_serviceLog.AppendLog(ex.Message, "", ex);
return(false);
}
}
// JobVPO Approval Dates Business Logic
// If JobVPOs.Total < 0 and JobVPOs.Status is "Approved" or "Authorized", set po_header.approval_date = JobVPOs.DateAuthorized
// Otherwise, set po_header.approval_date = JobVPOs.DateApproved
public void ReconcileJobVPOApprovalDates(string dataObject = "JobVPOsBySapphirePONumber")
{
DataTable recordsToReconcile = new DataTable();
recordsToReconcile.Columns.Add("dbName");
recordsToReconcile.Columns.Add("JobVPORID");
recordsToReconcile.Columns.Add("JobVPOID");
recordsToReconcile.Columns.Add("SapphireAmtTotal");
recordsToReconcile.Columns.Add("JobVPOStatus");
recordsToReconcile.Columns.Add("DateAuthorized");
recordsToReconcile.Columns.Add("DateApproved");
recordsToReconcile.Columns.Add("approval_date");
recordsToReconcile.Columns.Add("ApprovePaymentDate");
recordsToReconcile.Columns.Add("DateCompleted");
recordsToReconcile.Columns.Add("eSubmittalDate");
recordsToReconcile.Columns.Add("SapphirePONumber");
recordsToReconcile.Columns.Add("SQLUpdate");
try
{
var sapphireRecords = _sapphireRepository.GetSapphireRecords("JobVPOs");
var pervasiveRecords = _purchaseOrderHeaderRepository.GetPervasiveRecords("JobVPOs");
if ((!(sapphireRecords is null) && sapphireRecords.AsEnumerable().Any()) &&
(!(pervasiveRecords is null) && pervasiveRecords.AsEnumerable().Any()))
{
// (left) join sapphire and pervasive data together on JobVPOs.JobVPOID = po_header.SapphirePONumber
var vpoCompareDataSet = from srow in sapphireRecords.AsEnumerable()
join prow in pervasiveRecords.AsEnumerable().DefaultIfEmpty() on
new { JobVPOID = srow.Field <string>("JobVPOID") }
equals new { JobVPOID = prow.Field <string>("SapphirePONumber") }
where srow.Field <decimal>("AmtTotal") < 0 &&
(srow.Field <string>("Status").Equals("Approved") ||
srow.Field <string>("Status").Equals("Authorized"))
select new
{
DbName = prow.Field <string>("datasource"),
JobVPORID = srow.Field <int>("JobVPORID"),
JobVPOID = srow.Field <string>("JobVPOID"),
SapphireAmtTotal = srow.Field <decimal>("AmtTotal"),
JobVPOStatus = srow.Field <string>("Status"),
DateAuthorized = srow.Field <DateTime?>("DateAuthorized"),
DateApproved = srow.Field <DateTime?>("DateApproved"),
Approval_Date = prow.Field <DateTime?>("approval_date"),
SapphirePONumber = prow.Field <string>("SapphirePONumber")
};
bool UnmatchedValue = false;
string updateSQL = string.Empty;
if (vpoCompareDataSet.Any())
{
// Find matching pervasive record, compare date fields
foreach (var vpo in vpoCompareDataSet)
{
// if JobVPOs.AmtTotal is negative (< 0) and status is either Approved or Authorized,
if (vpo.SapphireAmtTotal < 0 && (vpo.JobVPOStatus == "Approved" || vpo.JobVPOStatus == "Authorized"))
{
// set po_header.approval_date = JobVPOs.DateAuthorized
UnmatchedValue = !_dateService.DatesAreEqual(vpo.DateAuthorized, vpo.Approval_Date, false);
if (UnmatchedValue && !String.IsNullOrEmpty(vpo.DbName))
{
updateSQL = CreateSQLUpdateSetDateValuesBySapphirePONumber(vpo.JobVPOID, "approval_date", _dateService.FormatDateForPervasive((object)vpo.DateAuthorized), vpo.DbName);
}
}
if (UnmatchedValue)
{
DataRow recordToUpdate = recordsToReconcile.NewRow();
recordToUpdate["dbName"] = vpo.DbName;
recordToUpdate["JobVPORID"] = vpo.JobVPORID;
recordToUpdate["JobVPOID"] = vpo.JobVPOID;
recordToUpdate["SapphireAmtTotal"] = vpo.SapphireAmtTotal;
recordToUpdate["JobVPOStatus"] = vpo.JobVPOStatus;
recordToUpdate["DateAuthorized"] = _dateService.FormatDateForPervasive(vpo.DateAuthorized);
recordToUpdate["DateApproved"] = _dateService.FormatDateForPervasive(vpo.DateApproved);
recordToUpdate["approval_date"] = String.IsNullOrEmpty(vpo.Approval_Date.ToString()) ? null : _dateService.FormatDateForPervasive((DateTime)vpo.Approval_Date);
recordToUpdate["SapphirePONumber"] = vpo.SapphirePONumber;
recordToUpdate["SQLUpdate"] = updateSQL;
recordsToReconcile.Rows.Add(recordToUpdate);
UnmatchedValue = false;
}
}
}
}
if (recordsToReconcile.Rows.Count > 0)
{
_exportService.PrepareDataForExport(recordsToReconcile, "JobVPOsBySapphirePONumber", "UPDATE");
}
}
catch (Exception ex)
{
_serviceLog.AppendLog(ex.Message, "", ex);
// write export data to file anyway
if (recordsToReconcile.Rows.Count > 0)
{
_exportService.PrepareDataForExport(recordsToReconcile, "JobVPOsBySapphirePONumber", "UPDATE");
}
}
}
public void ReconcilePurchaseOrderDates(string dataObject = "JobPOs")
{
DataTable recordsToReconcile = new DataTable();
recordsToReconcile.Columns.Add("dbName");
recordsToReconcile.Columns.Add("JobPOID");
recordsToReconcile.Columns.Add("DateComplByVnd");
recordsToReconcile.Columns.Add("DateApproved");
recordsToReconcile.Columns.Add("eSubmittalDate");
recordsToReconcile.Columns.Add("ApprovePaymentDate");
bool UnmatchedValue = false;
var sapphireRecords = _sapphireRepository.GetSapphireRecords(dataObject);
var pervasiveRecords = _purchaseOrderHeaderRepository.GetPervasiveRecords(dataObject);
try
{
var poCompareDataSet = from srow in sapphireRecords.AsEnumerable()
join prow in pervasiveRecords.AsEnumerable().DefaultIfEmpty() on
new { JobPOID = srow.Field <string>("SapphirePONumber") }
equals new { JobPOID = prow.Field <string>("SapphirePONumber") }
select new
{
DbName = srow.Field <string>("dbName"),
JobPOID = srow.Field <string>("SapphirePONumber"),
DateComplByVnd = srow.Field <DateTime>("DateComplByVnd"),
DateApproved = srow.Field <DateTime>("DateApproved"),
eSubmittalDate = prow.Field <DateTime>("eSubmittalDate"),
ApprovePaymentDate = prow.Field <DateTime>("ApprovePaymentDate"),
SapphirePONumber = prow.Field <string>("SapphirePONumber")
};
if (poCompareDataSet.Any())
{
// Find matching pervasive record, compare date fields
foreach (var po in poCompareDataSet)
{
// sapphire fields
var jobPOID = po.GetType().GetProperty("SapphirePONumber").GetValue(po, null).ToString() ?? "";
// get matching pervasive record where SapphirePONumber = jobPOID
var pervasiveRows = pervasiveRecords.AsEnumerable()
.Where(p => p.Field <string>("SapphirePONumber").Equals(jobPOID));
if (pervasiveRows.Any())
{
DataTable dt = pervasiveRows.CopyToDataTable();
if (dt.Rows.Count > 1)
{
_serviceLog.AppendLog(String.Format("Multiple pervasive records found for SapphirePONumber {0}", jobPOID));
continue;
}
if (dt.Rows.Count == 1)
{
// eSubmittalDate (JobSchActs.DateComplByVnd)
// also, JobPOs.DateCompleted - verify which field appears to be currently used in production
object dateComplByVnd = po.GetType().GetProperty("DateComplByVnd").GetValue(po, null);
object eSubmittalDate = dt.Rows[0]["eSubmittalDate"];
if (!_dateService.DatesAreEqual(dateComplByVnd, eSubmittalDate))
{
UnmatchedValue = true;
}
// ApprovePaymentDate (DateApproved)
object dateApproved = po.GetType().GetProperty("DateApproved").GetValue(po, null);
object approvePaymentDate = dt.Rows[0]["ApprovePaymentDate"];
if (!_dateService.DatesAreEqual(dateApproved, approvePaymentDate))
{
UnmatchedValue = true;
}
if (UnmatchedValue)
{
DataRow recordToUpdate = recordsToReconcile.NewRow();
recordToUpdate["dbName"] = po.GetType().GetProperty("dbName").GetValue(po, null).ToString() ?? "";
recordToUpdate["JobPOID"] = jobPOID;
recordToUpdate["DateComplByVnd"] = _dateService.FormatDateForPervasive(dateComplByVnd);
recordToUpdate["DateApproved"] = _dateService.FormatDateForPervasive(dateApproved);
recordToUpdate["eSubmittalDate"] = String.IsNullOrEmpty(eSubmittalDate.ToString()) ? null : ((DateTime)eSubmittalDate).ToString("yyyy-MM-dd");
recordToUpdate["ApprovePaymentDate"] = String.IsNullOrEmpty(approvePaymentDate.ToString()) ? null : ((DateTime)approvePaymentDate).ToString("yyyy-MM-dd");
recordsToReconcile.Rows.Add(recordToUpdate);
UnmatchedValue = false;
}
}
}
}
}
if (recordsToReconcile.Rows.Count > 0)
{
_exportService.PrepareDataForExport(recordsToReconcile, "poProcessingDates", "UPDATE");
}
}
catch (Exception ex)
{
_serviceLog.AppendLog(ex.Message, "", ex);
// write export data to file anyway
if (recordsToReconcile.Rows.Count > 0)
{
_exportService.PrepareDataForExport(recordsToReconcile, "poProcessingDates", "UPDATE");
}
}
}