public void ReconcileJobCstActRecordsAndEGMAmounts(string jobNumber, string company = "")
{
// check to see if there is an existing workflow for this record
int workflowRID = GetSapphireWorkflow(jobNumber);
// Get sapphire JobCstActs data
DataTable sapphireJobCstActs = _sapphireRepository.GetSapphireRecords("JobCstActs", jobNumber);
if (!(sapphireJobCstActs is null))
{
if (!string.IsNullOrEmpty(jobNumber) && !string.IsNullOrEmpty(company))
{
// Get po_header data from pervasive
DataTable pervasiveJobCstActs = _jobCostActivityRepository.GetPervasiveJobCstActs(jobNumber, company);
if (!(pervasiveJobCstActs is null) && pervasiveJobCstActs.AsEnumerable().Any())
{
// 1. Find missing Job Cost Activities (JobCstActs) & create in PO_Header
CreateMissingJobCstActs(sapphireJobCstActs, pervasiveJobCstActs, company, workflowRID);
// 2) Set EGM amounts (reset all PO_Header egm amounts to 0 for this job then sync BudgetAmt from sapphire to PO_Header)
ResetEgmAmounts(jobNumber, company, workflowRID);
SetEgmValues(jobNumber, sapphireJobCstActs, company, workflowRID);
// 3) Recalc EGMs (IT_RecalcEGMValues)
_jobCostActivityRepository.RecalculateEgmValues(jobNumber, company, workflowRID);
}
}
}
}
private void GetSapphireRecordsNotFoundInPervasive(string dataObject, string filterColumn, string jobNumber = "")
{
try
{
var sapphireRecords = _sapphireRepository.GetSapphireRecords(dataObject, jobNumber);
var pervasiveRecords = _purchaseOrderHeaderRepository.GetPervasiveRecords(dataObject, jobNumber);
// if no records returned from sapphire
if (sapphireRecords is null || !(sapphireRecords.AsEnumerable().Any()))
{
Console.WriteLine("No sapphire records found for this job. Please ensure you have entered the job number correctly and have configured the correct database connection.");
return;
}
if (!(pervasiveRecords is null) && pervasiveRecords.AsEnumerable().Any())
{
// Get list of ids from pervasive data based on filter column passed in by object this task is currently attempting to reconcile
// e.g. if SapphireObjID = JobPOs, then pervasive JobPOReconcilerID will be "JobPOID-JobID-ActID"
// if SapphireObjID = JobCstActs, then pervasive JobCstReconcilerID will be "JobID-ActID"
var idsFromPervasiveTable = new HashSet <string>(pervasiveRecords.AsEnumerable()
.OrderBy(o => o.Field <string>(filterColumn))
.Select(tb => tb.Field <string>(filterColumn)));
var recordsFromSapphireListNotInPervasiveList = sapphireRecords.AsEnumerable()
.Where(ta => !idsFromPervasiveTable.Contains(ta.Field <string>(filterColumn)));
// No missing records identified
if (!recordsFromSapphireListNotInPervasiveList.Any() && !String.IsNullOrEmpty(jobNumber))
{
Console.WriteLine(String.Format("\nNo missing activities were found for job {0}.", _jobService.FormatJobNumber(jobNumber)));
// Loop through process again, allow entry of new job number for another run
if (!String.IsNullOrEmpty(jobNumber))
{
ReconcileJobCostActivityRecordsByJobNumber();
}
}
// If any missing records are identified
if (recordsFromSapphireListNotInPervasiveList.Any())
{
DataTable dataTable = recordsFromSapphireListNotInPervasiveList.CopyToDataTable();
// Prepare data for export
// 1 - recordsFromSapphireListNotInPervasiveList (do not build SQL insert for JobPOs, do not export files for specific job number)
if (String.IsNullOrEmpty(jobNumber))
{
_exportService.PrepareDataForExport(dataTable, dataObject);
}
// If JobNumber has been set, continue JobCstActs insert procedure
if (!String.IsNullOrEmpty(jobNumber))
{
_jobCostActivityService.ConfirmJobCstActInsertByJobNumber(jobNumber, dataTable, dataObject);
// Loop through process again, allow entry of new job number for another run
if (!String.IsNullOrEmpty(jobNumber))
{
ReconcileJobCostActivityRecordsByJobNumber();
}
}
// Note: When reconciling JobPOs, after receiving list back of Sapphire JobPOs which do not match on "JobPOID-JobID-ActID" from pervasive,
// remaining list must be checked against "JobID-ActID" from pervasive to identify matching JobCstActs records.
// if matching record is found, new po_no should be generated and existing JobCstAct record in PO_Header should be updated.
// Any remaining items that do not match JobPOs or JobCstActs records in PO_Header will be INSERTs.
if (dataObject == "JobPOs" && dataTable.Rows.Count > 0)
{
// this list will become a list of PO_Header records to update with new po_no and JobPOs data from Sapphire
var idsFromPervasiveTableWherePoNoIs0 = new HashSet <string>(pervasiveRecords.AsEnumerable()
.OrderBy(o => o.Field <string>(filterColumn))
.Where(p => p.Field <UInt32>("po_no").Equals(0))
.Select(tb => tb.Field <string>(filterColumn)));
if (idsFromPervasiveTableWherePoNoIs0.Any())
{
var jobPORecordsMatchingPervasiveJobCstActRecords = recordsFromSapphireListNotInPervasiveList.AsEnumerable()
.Where(jp => idsFromPervasiveTableWherePoNoIs0.Contains(jp.Field <string>("JobCstReconcilerID")));
// 2 - jobPORecordsMatchingPervasiveJobCstActRecords (generate pervasive po_no and build SQL Update statement)
_exportService.PrepareDataForExport(jobPORecordsMatchingPervasiveJobCstActRecords.CopyToDataTable(), "jobPORecordsMatchingPervasiveJobCstActRecords", "UPDATE");
// this list will be a list of records to INSERT to PO_Header since no match was found on JobPORID-JobID-Activity or JobID-Activity combinations
var jobPORecordsNotFoundInPervasive = recordsFromSapphireListNotInPervasiveList.AsEnumerable()
.Where(jp => !idsFromPervasiveTable.Contains(jp.Field <string>("JobCstReconcilerID")));
if (jobPORecordsNotFoundInPervasive.Any())
{
// 3 - jobPORecordsNotFoundInPervasive (generate pervasive po_no and build SQL INSERT statement)
_exportService.PrepareDataForExport(jobPORecordsNotFoundInPervasive.CopyToDataTable(), "jobPORecordsNotFoundInPervasive");
}
}
}
}
}
}
catch (Exception ex)
{
_serviceLog.AppendLog(ex.ToString());
}
}
// 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 DataTable GetSapphireJobPORecords()
{
return(_sapphireRepository.GetSapphireRecords("JobPOs"));
}
