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")); }