private string GetNextApexVendor(ApexDataDataContext dc)
{
const string STARTVENDORID = "10000";
string lastVendorID = String.Empty;
try
{
lastVendorID = dc.QBVendors
.Where(s => s.ApexCompany.StartsWith(_ApexTargetCompany))
.Select(s => s.ApexVendorID).Max();
}
catch
{
}
if (string.IsNullOrEmpty(lastVendorID))
{
lastVendorID = _ApexTargetCompany + STARTVENDORID;
}
int id;
bool parseOK = Int32.TryParse(lastVendorID.Substring(1, 5), out id);
if (parseOK)
{
return(_ApexTargetCompany + (id + 1).ToString());
}
else
{
return(_ApexTargetCompany + STARTVENDORID);
}
}
private void TransferTerms(XmlSerializer serializer, XmlSerializerNamespaces ns, QBXML qbxml, QBXMLMsgsRq qbMsgsRq)
{
MemoryStream ms;
StreamReader sr;
string xmlRequest;
var termsrq = new TermsQueryRqType();
termsrq.requestID = "1";
qbMsgsRq.Items[0] = termsrq;
qbxml.Items[0] = qbMsgsRq;
ms = new MemoryStream();
serializer.Serialize(ms, qbxml, ns);
ms.Seek(0, SeekOrigin.Begin);
sr = new StreamReader(ms);
xmlRequest = sr.ReadToEnd();
xmlRequest = xmlRequest.Replace("<?xml version=\"1.0\"?>", "<?xml version=\"1.0\"?><?qbxml version=\"4.0\"?>");
if (DEBUGMODE)
{
File.WriteAllText("c:\\QB\\TermsQBXML.xml", xmlRequest);
}
_Response = _Rp.ProcessRequest(_Ticket, xmlRequest);
if (DEBUGMODE)
{
File.WriteAllText("c:\\QB\\Terms.xml", _Response);
}
QBXML rsXML = GetQbxml(serializer);
if (rsXML?.Items?[0] is QBXMLMsgsRs)
{
QBXMLMsgsRs msgsrs = (QBXMLMsgsRs)rsXML.Items[0];
if (msgsrs?.Items?[0] is TermsQueryRsType)
{
TermsQueryRsType rs = (TermsQueryRsType)msgsrs.Items[0];
using (ApexDataDataContext dc = new ApexDataDataContext(_SqlConnBuilder.ConnectionString))
{
foreach (var term in rs.Items)
{
if (term is StandardTermsRet)
{
StandardTermsRet qbTerm = (StandardTermsRet)term;
if (!dc.VENDTERMs.Where(s => s.VendTerm1 == qbTerm.Name).Any())
{
VENDTERM newTerm = new VENDTERM();
newTerm.VendTerm1 = qbTerm.Name;
dc.VENDTERMs.InsertOnSubmit(newTerm);
}
}
dc.SubmitChanges();
}
}
}
}
}
private async static Task ProcessVendorAsync(XElement vendorInfo)
{
string apexVendorID = vendorInfo.Element("avm_num").Value.Trim().PadLeft(6);
if (String.IsNullOrEmpty(apexVendorID.Trim()))
{
return;
}
using (var dc = new ApexDataDataContext())
{
var apexVendor = dc.Vendors.Where(v => v.Vendor1 == apexVendorID).SingleOrDefault();
bool newVendor = (apexVendor == null);
if (!newVendor)
{
Console.WriteLine($" Vendor {apexVendorID} is not new, Apex data preserved.");
return;
}
apexVendor = new Vendor();
apexVendor.Vendor1 = apexVendorID;
apexVendor.Name = LoadValue(vendorInfo.Element("avm_name").Value, 25);
apexVendor.Add1 = LoadValue(vendorInfo.Element("avm_add__1").Value, 25);
apexVendor.Add2 = LoadValue(vendorInfo.Element("avm_add__2").Value, 25);
apexVendor.City = LoadValue(vendorInfo.Element("avm_add__3").Value, 15);
apexVendor.State = LoadValue(vendorInfo.Element("avm_add__4").Value, 4);
apexVendor.Zip = LoadValue(vendorInfo.Element("avm_pcode").Value, 15);
apexVendor.Phone = LoadValue(vendorInfo.Element("avm_phone").Value, 15);
apexVendor.Fax = LoadValue(vendorInfo.Element("avm_fax").Value, 15);
apexVendor.EMail = LoadValue(vendorInfo.Element("avm_email").Value, 40);
apexVendor.CompLevel = 0;
apexVendor.PermitLow = "T";
apexVendor.AcctID = apexVendorID;
dc.Vendors.InsertOnSubmit(apexVendor);
try
{
dc.SubmitChanges();
Console.WriteLine($" Vendor {apexVendorID} processed.");
}
catch (Exception ex)
{
Console.WriteLine($"Error on vendor {apexVendorID}, {ex}");
}
}
}
private static async Task ProcessJobAsync(XElement jobInfo)
{
string apexJobID = jobInfo.Element("job_num").Value.Trim().PadLeft(12);
if (String.IsNullOrEmpty(apexJobID.Trim()))
{
return;
}
using (var dc = new ApexDataDataContext())
{
var apexJob = dc.Jobs.Where(v => v.Job1 == apexJobID).SingleOrDefault();
bool newJob = (apexJob == null);
if (newJob)
{
apexJob = new Job();
}
apexJob.Job1 = apexJobID;
apexJob.Name = LoadValue(jobInfo.Element("job_name").Value, 25);
apexJob.Add1 = LoadValue(jobInfo.Element("job_shipaddr__1").Value, 25);
apexJob.Add2 = LoadValue(jobInfo.Element("job_shipaddr__2").Value, 25);
apexJob.City = LoadValue(jobInfo.Element("job_shipaddr__3").Value, 15);
apexJob.State = LoadValue(jobInfo.Element("job_shipaddr__4").Value, 4);
apexJob.Zip = LoadValue(jobInfo.Element("job_pcode").Value, 15);
apexJob.Phone = LoadValue(jobInfo.Element("job_tel").Value, 15);
if (newJob)
{
apexJob.Act = LoadValue(jobInfo.Element("job_active").Value, 1);
apexJob.TaxDefault = "N";
dc.Jobs.InsertOnSubmit(apexJob);
}
try
{
dc.SubmitChanges();
Console.WriteLine($" Job {apexJobID} processed.");
}
catch (Exception ex)
{
Console.WriteLine($"Error on Job {apexJobID}, {ex}");
}
}
}
private static async Task ProcessWOAsync(XElement woInfo)
{
string apexWOID = woInfo.Element("sso_order").Value.Trim();
if (String.IsNullOrEmpty(apexWOID.Trim()))
{
return;
}
using (var dc = new ApexDataDataContext())
{
var apexWO = dc.WOs.Where(v => v.WO1 == apexWOID).SingleOrDefault();
bool newWO = (apexWO == null);
if (newWO)
{
apexWO = new WO();
}
apexWO.WO1 = apexWOID;
apexWO.SiteName = LoadValue(woInfo.Element("slc_name").Value, 25);
apexWO.SiteAdd1 = LoadValue(woInfo.Element("slc_add__1").Value, 25);
apexWO.SiteAdd2 = LoadValue(woInfo.Element("slc_add__2").Value, 25);
apexWO.SiteCity = LoadValue(woInfo.Element("slc_add__3").Value, 15);
apexWO.SiteState = LoadValue(woInfo.Element("slc_add__4").Value, 4);
apexWO.SiteZip = LoadValue(woInfo.Element("slc_pcode").Value, 15);
apexWO.Customer = LoadValue(woInfo.Element("job_num").Value, 4);
apexWO.Act = "A";
if (newWO)
{
dc.WOs.InsertOnSubmit(apexWO);
}
try
{
dc.SubmitChanges();
Console.WriteLine($" Work Order {apexWOID} processed.");
}
catch (Exception ex)
{
Console.WriteLine($"Error on Work Order {apexWOID}, {ex}");
}
}
}
private static async Task ProcessWBSAsync(XElement wbsInfo)
{
string apexJobID = wbsInfo.Element("job_num").Value.Trim().PadLeft(12);
string wbsCode = wbsInfo.Element("jwb_code").Value;
if (String.IsNullOrEmpty(apexJobID.Trim()))
{
return;
}
using (var dc = new ApexDataDataContext())
{
var apexWBS = dc.COINSESB_WBs.Where(v => v.Job == apexJobID && v.WB_Code == wbsCode).SingleOrDefault();
bool newWBS = (apexWBS == null);
if (newWBS)
{
apexWBS = new COINSESB_WB();
}
apexWBS.Job = apexJobID;
apexWBS.WB_Code = LoadValue(wbsInfo.Element("jwb_code").Value, 50);
apexWBS.WB_Desc = LoadValue(wbsInfo.Element("jwb_desc").Value, 50);
apexWBS.Activity = LoadValue(wbsInfo.Element("jca_activity").Value, 50);
apexWBS.Section = LoadValue(wbsInfo.Element("jcs_section").Value, 50);
apexWBS.UsesActivity = !String.IsNullOrEmpty(apexWBS.Activity);
if (newWBS)
{
dc.COINSESB_WBs.InsertOnSubmit(apexWBS);
}
try
{
dc.SubmitChanges();
Console.WriteLine($" WBS {apexJobID},{wbsCode} processed.");
}
catch (Exception ex)
{
Console.WriteLine($"Error on WBS {apexJobID},{wbsCode}, {ex}");
}
}
}
public static List <GLAcct> BuildGLAcctList(ApexDataDataContext apexData)
{
//This has now been standardized between companies, and is in the Apex property bag
//Due to the number of changes that we have made to date, I am retaining the design where this can vary again by company
List <GLAcct> acctList = new List <GLAcct>();
string acctName = apexData.PropBags.Where(s => s.PropName == "ApexQB-M").Select(s => s.PropVal).FirstOrDefault();
if (acctName == null)
{
throw new Exception("ApexQB G/L M property is not present, process aborting");
}
acctList.Add(new GLAcct {
ApexCompany = "M", ApexGLRef = "M", QBGLAcctFullName = acctName
});
acctList.Add(new GLAcct {
ApexCompany = "T", ApexGLRef = "M", QBGLAcctFullName = acctName
});
acctList.Add(new GLAcct {
ApexCompany = "E", ApexGLRef = "M", QBGLAcctFullName = acctName
});
acctName = apexData.PropBags.Where(s => s.PropName == "ApexQB-R").Select(s => s.PropVal).FirstOrDefault();
if (acctName == null)
{
throw new Exception("ApexQB G/L R property is not present, process aborting");
}
acctList.Add(new GLAcct {
ApexCompany = "M", ApexGLRef = "R", QBGLAcctFullName = acctName
});
acctList.Add(new GLAcct {
ApexCompany = "T", ApexGLRef = "R", QBGLAcctFullName = acctName
});
acctList.Add(new GLAcct {
ApexCompany = "E", ApexGLRef = "R", QBGLAcctFullName = acctName
});
acctName = apexData.PropBags.Where(s => s.PropName == "ApexQB-T").Select(s => s.PropVal).FirstOrDefault();
if (acctName == null)
{
throw new Exception("ApexQB G/L T property is not present, process aborting");
}
acctList.Add(new GLAcct {
ApexCompany = "M", ApexGLRef = "T", QBGLAcctFullName = acctName
});
acctList.Add(new GLAcct {
ApexCompany = "T", ApexGLRef = "T", QBGLAcctFullName = acctName
});
acctList.Add(new GLAcct {
ApexCompany = "E", ApexGLRef = "T", QBGLAcctFullName = acctName
});
acctName = apexData.PropBags.Where(s => s.PropName == "ApexQB-S").Select(s => s.PropVal).FirstOrDefault();
if (acctName == null)
{
throw new Exception("ApexQB G/L S property is not present, process aborting");
}
acctList.Add(new GLAcct {
ApexCompany = "M", ApexGLRef = "S", QBGLAcctFullName = acctName
});
acctList.Add(new GLAcct {
ApexCompany = "T", ApexGLRef = "S", QBGLAcctFullName = acctName
});
acctList.Add(new GLAcct {
ApexCompany = "E", ApexGLRef = "S", QBGLAcctFullName = acctName
});
acctName = apexData.PropBags.Where(s => s.PropName == "ApexQB-E").Select(s => s.PropVal).FirstOrDefault();
if (acctName == null)
{
throw new Exception("ApexQB G/L E property is not present, process aborting");
}
acctList.Add(new GLAcct {
ApexCompany = "M", ApexGLRef = "E", QBGLAcctFullName = acctName
});
acctList.Add(new GLAcct {
ApexCompany = "T", ApexGLRef = "E", QBGLAcctFullName = acctName
});
acctList.Add(new GLAcct {
ApexCompany = "E", ApexGLRef = "E", QBGLAcctFullName = acctName
});
return(acctList);
// return new List<GLAcct>
// {
// //CEM, company "M"
// new GLAcct { ApexCompany = "M", ApexGLRef = "M", QBGLAcctFullName = "Cost of Goods Sold:MATERIALS:JOB MATERIALS - PO" },
// new GLAcct { ApexCompany = "M", ApexGLRef = "R", QBGLAcctFullName = "Cost of Goods Sold:RENTAL EQUIPMENT:Rental Equipment" },
// new GLAcct { ApexCompany = "M", ApexGLRef = "T", QBGLAcctFullName = "Cost of Goods Sold:RENTAL EQUIPMENT:Trailer Rental" },
// new GLAcct { ApexCompany = "M", ApexGLRef = "S", QBGLAcctFullName = "Cost of Goods Sold:SUBCONTRACTORS EXPENSE" },
// new GLAcct { ApexCompany = "M", ApexGLRef = "E", QBGLAcctFullName = "Cost of Goods Sold:SMALL Tools & Repairs PO" },
// //CET, company "T"
// new GLAcct { ApexCompany = "T", ApexGLRef = "M", QBGLAcctFullName = "Cost of Goods Sold (Job Cost):H MATERIALS:15 Materials PO" },
// new GLAcct { ApexCompany = "T", ApexGLRef = "R", QBGLAcctFullName = "Cost of Goods Sold (Job Cost):F RENTAL EQUIPMENT:27 Rental Equipment" },
// new GLAcct { ApexCompany = "T", ApexGLRef = "T", QBGLAcctFullName = "Cost of Goods Sold (Job Cost):F RENTAL EQUIPMENT:40 Trailer Rental" },
// new GLAcct { ApexCompany = "T", ApexGLRef = "S", QBGLAcctFullName = "Cost of Goods Sold (Job Cost):I SUBCONTRACTS" },
// new GLAcct { ApexCompany = "T", ApexGLRef = "E", QBGLAcctFullName = "Cost of Goods Sold (Job Cost):E TOOLS AND EQUIPMENT:35 Small Tools & Repairs PO" },
// //CEDB, company "E"
// new GLAcct { ApexCompany = "E", ApexGLRef = "M", QBGLAcctFullName = "Cost of Goods Sold (Job Cost):H MATERIALS:15 Materials PO" },
// new GLAcct { ApexCompany = "E", ApexGLRef = "R", QBGLAcctFullName = "Cost of Goods Sold (Job Cost):F RENTAL EQUIPMENT:27 Rental Equipment" },
// new GLAcct { ApexCompany = "E", ApexGLRef = "T", QBGLAcctFullName = "Cost of Goods Sold (Job Cost):F RENTAL EQUIPMENT:40 Trailer Rental" },
// new GLAcct { ApexCompany = "E", ApexGLRef = "S", QBGLAcctFullName = "Cost of Goods Sold (Job Cost):I SUBCONTRACTS" },
// new GLAcct { ApexCompany = "E", ApexGLRef = "E", QBGLAcctFullName = "Cost of Goods Sold (Job Cost):E TOOLS AND EQUIPMENT:35 Small Tools & Repairs PO" }
// };
}
private void TransferJobs(XmlSerializer serializer, XmlSerializerNamespaces ns, QBXML qbxml, QBXMLMsgsRq qbMsgsRq)
{
MemoryStream ms;
StreamReader sr;
string xmlRequest;
var custrq = new CustomerQueryRqType();
custrq.requestID = "1";
qbMsgsRq.Items = new object[1] {
custrq
};
qbxml.Items[0] = qbMsgsRq;
ms = new MemoryStream();
serializer.Serialize(ms, qbxml, ns);
ms.Seek(0, SeekOrigin.Begin);
sr = new StreamReader(ms);
xmlRequest = sr.ReadToEnd();
xmlRequest = xmlRequest.Replace("<?xml version=\"1.0\"?>", "<?xml version=\"1.0\"?><?qbxml version=\"4.0\"?>");
if (DEBUGMODE)
{
File.WriteAllText("c:\\QB\\CustQBXML.xml", xmlRequest);
}
_Response = _Rp.ProcessRequest(_Ticket, xmlRequest);
if (DEBUGMODE)
{
File.WriteAllText("c:\\QB\\Customers.xml", _Response);
}
QBXML rsXML = GetQbxml(serializer);
if (rsXML?.Items?[0] is QBXMLMsgsRs)
{
QBXMLMsgsRs msgsrs = (QBXMLMsgsRs)rsXML.Items[0];
if (msgsrs?.Items?[0] is CustomerQueryRsType)
{
CustomerQueryRsType rs = (CustomerQueryRsType)msgsrs.Items[0];
if (rs.statusCode != "0")
{
MessageBox.Show(rs.statusMessage);
}
else
{
for (int i = 0; i < rs.CustomerRet.Length; i++)
{
CustomerRet cr = rs.CustomerRet[i];
if (cr.Sublevel == "0")
{
continue; //don't process the top level customers; we only want the job records
}
Match m = _JobRegEx.Match(cr?.FullName);
if (!m.Success)
{
continue; //this isn't a job number we can use
}
string qbJobID = m.Groups[1].Value;
string qbJobName = m.Groups[2].Value;
using (ApexDataDataContext dc = new ApexDataDataContext(_SqlConnBuilder.ConnectionString))
{
Job job = null;
QBJob qbjob;
qbjob = dc.QBJobs.Where(s => s.ApexCompany == _ApexTargetCompany && s.ApexJobID == qbJobID && s.QBListID != cr.ListID)
.FirstOrDefault();
if (qbjob != null)
{
MessageBox.Show($"Job {qbJobID} from {qbJobName} has already been assigned to {qbjob.QBJobName} and cannot be transferred.", "Duplicate job");
continue;
}
qbjob = dc.QBJobs.Where(s => s.ApexCompany == _ApexTargetCompany && s.QBListID == cr.ListID).SingleOrDefault();
if (qbjob != null)
{
if (qbjob.ApexJobID != qbJobID.PadLeft(12)) //we need to delete and treat as new
{
dc.QBJobs.DeleteOnSubmit(qbjob);
QBJob qbjob2 = new QBJob();
qbjob2.QBListID = cr?.ListID;
qbjob2.QBJobName = cr?.FullName;
qbjob2.ApexJobID = qbJobID.PadLeft(12);
qbjob2.ApexCompany = _ApexTargetCompany;
dc.QBJobs.InsertOnSubmit(qbjob2);
}
else
{
qbjob.QBJobName = cr?.FullName;
}
}
else
{
qbjob = new QBJob();
qbjob.QBListID = cr?.ListID;
qbjob.QBJobName = cr?.FullName;
qbjob.ApexJobID = qbJobID.PadLeft(12);
qbjob.ApexCompany = _ApexTargetCompany;
dc.QBJobs.InsertOnSubmit(qbjob);
}
job = dc.Jobs.Where(s => s.Job1 == qbjob.ApexJobID.PadLeft(12)).SingleOrDefault();
if (job != null)
{
SetJobFields(cr, qbJobName, job);
}
else
{
job = new Job();
//Start the new Apex job
job.Job1 = qbJobID.PadLeft(12);
job.Act = "A";
job.Schedule = "STD";
job.TaxDefault = "Y";
job.TaxRate = 0;
job.POMsg = String.Empty;
SetJobFields(cr, qbJobName, job);
dc.Jobs.InsertOnSubmit(job);
}
dc.SubmitChanges();
}
}
}
}
}
}
private void TransferVendors(XmlSerializer serializer, XmlSerializerNamespaces ns, QBXML qbxml, QBXMLMsgsRq qbMsgsRq)
{
MemoryStream ms;
StreamReader sr;
string xmlRequest;
var vendrq = new VendorQueryRqType();
vendrq.requestID = "1";
qbMsgsRq.Items[0] = vendrq;
qbxml.Items[0] = qbMsgsRq;
ms = new MemoryStream();
serializer.Serialize(ms, qbxml, ns);
ms.Seek(0, SeekOrigin.Begin);
sr = new StreamReader(ms);
xmlRequest = sr.ReadToEnd();
xmlRequest = xmlRequest.Replace("<?xml version=\"1.0\"?>", "<?xml version=\"1.0\"?><?qbxml version=\"4.0\"?>");
if (DEBUGMODE)
{
File.WriteAllText("c:\\QB\\VendQBXML.xml", xmlRequest);
}
_Response = _Rp.ProcessRequest(_Ticket, xmlRequest);
if (DEBUGMODE)
{
File.WriteAllText("c:\\QB\\Vendors.xml", _Response);
}
QBXML rsXML = GetQbxml(serializer);
if (rsXML?.Items?[0] is QBXMLMsgsRs)
{
QBXMLMsgsRs msgsrs = (QBXMLMsgsRs)rsXML.Items[0];
if (msgsrs?.Items?[0] is VendorQueryRsType)
{
VendorQueryRsType rs = (VendorQueryRsType)msgsrs.Items[0];
if (rs.statusCode != "0")
{
MessageBox.Show(rs.statusMessage);
}
else
{
for (int i = 0; i < rs.VendorRet.Length; i++)
{
VendorRet vr = rs.VendorRet[i];
using (ApexDataDataContext dc = new ApexDataDataContext(_SqlConnBuilder.ConnectionString))
{
Vendor vendor = null;
QBVendor qbvendor = dc.QBVendors
.Where(s => s.ApexCompany == _ApexTargetCompany && s.QBListID == vr.ListID)
.SingleOrDefault();
bool newRecord;
if (qbvendor == null) //new vendor
{
newRecord = true;
qbvendor = new QBVendor();
vendor = new Vendor();
string newVendorID = GetNextApexVendor(dc);
//Set up the translation table
qbvendor.QBListID = vr?.ListID;
qbvendor.QBVendorName = vr?.Name;
qbvendor.ApexVendorID = newVendorID.PadLeft(6); //pad it just in case we change the way we're numbering
qbvendor.ApexCompany = _ApexTargetCompany;
qbvendor.Terms = vr?.TermsRef?.FullName;
//Start the new Apex vendor
vendor.Vendor1 = newVendorID;
vendor.AcctID = newVendorID;
vendor.Name = LoadField(vr?.Name.ToUpper(), 25);
}
else
{
newRecord = false;
qbvendor.Terms = vr?.TermsRef?.FullName;
vendor = dc.Vendors.Where(s => s.Vendor1 == qbvendor.ApexVendorID).Single();
}
vendor.Add1 = LoadField(vr?.VendorAddress?.Addr1, 25);
vendor.Add2 = LoadField(vr?.VendorAddress?.Addr2, 25);
vendor.City = LoadField(vr?.VendorAddress?.City, 15);
vendor.State = LoadField(vr?.VendorAddress?.State, 4);
vendor.Zip = LoadField(vr?.VendorAddress?.PostalCode, 15);
vendor.EMail = LoadField(vr?.Email, 40);
vendor.Attn = LoadField(vr?.Contact, 20);
vendor.Phone = LoadField(vr?.Phone, 15);
vendor.CompLevel = 0;
vendor.Terms = LoadField(vr?.TermsRef?.FullName, 15);
vendor.Company = _ApexTargetCompany;
if (newRecord)
{
dc.QBVendors.InsertOnSubmit(qbvendor);
dc.Vendors.InsertOnSubmit(vendor);
}
dc.SubmitChanges();
}
}
}
}
}
}
private void ProcessInvoice(VendIvc invoice, ApexDataDataContext apexData)
{
PO po = apexData.POs.Where(s => s.Po1 == invoice.PO).SingleOrDefault(); //get the corresponding P/O
if (po == null)
{
_StatusLines.Add(new StatusLine
{
Invoice = invoice.Invoice,
PO = invoice.PO.Trim(),
Message = "The invoice points to an invalid P/O!?"
});
return;
}
if (po.Vendor == null)
{
_StatusLines.Add(new StatusLine
{
Invoice = invoice.Invoice,
PO = invoice.PO.Trim(),
Message = "There is no vendor on this P/O"
});
return;
}
Job job = apexData.Jobs.Where(s => s.Job1 == po.Job).SingleOrDefault(); //get the job
if (job == null)
{
_StatusLines.Add(new StatusLine
{
Invoice = invoice.Invoice,
PO = invoice.PO.Trim(),
Message = "There is no job on this P/O"
});
return;
}
QBJob qbjob = apexData.QBJobs.Where(s => s.ApexCompany == _ApexTargetCompany &&
s.ApexJobID == po.Job).SingleOrDefault();
if (qbjob == null)
{
_StatusLines.Add(new StatusLine
{
Invoice = invoice.Invoice,
PO = invoice.PO.Trim(),
Message = "This P/O has an invalid QuickBooks job reference"
});
return;
}
QBVendor qbvendor = apexData.QBVendors.Where(s => s.ApexCompany == _ApexTargetCompany &&
s.ApexVendorID == po.Vendor).SingleOrDefault();
if (qbvendor == null)
{
_StatusLines.Add(new StatusLine
{
Invoice = invoice.Invoice,
PO = invoice.PO.Trim(),
Message = "This P/O has an invalid QuickBooks vendor reference"
});
return;
}
var qbxml = new QBXML();
qbxml.ItemsElementName = new ItemsChoiceType99[1] {
ItemsChoiceType99.QBXMLMsgsRq
};
var qbMsgsRq = new QBXMLMsgsRq();
qbMsgsRq.onError = QBXMLMsgsRqOnError.continueOnError;
var billaddrq = new BillAddRqType();
billaddrq.requestID = "1";
TermsRef termsref = new TermsRef
{
FullName = po.VendorTerms
};
string ApexGLRef = apexData.Costcodes
.Where(s => s.Schedule == "STD" && s.CostCode1 == po.POLines.Select(l => l.CostCode).FirstOrDefault())
.Select(s => s.GL).FirstOrDefault();
if (String.IsNullOrEmpty(ApexGLRef))
{
ApexGLRef = "M";
}
string QBGLAcctFullName = GLAcctUtility.GLAcctList
.Where(s => s.ApexCompany == _ApexTargetCompany && s.ApexGLRef == ApexGLRef)
.Select(s => s.QBGLAcctFullName).SingleOrDefault();
AccountRef accountref = new AccountRef
{
FullName = QBGLAcctFullName
};
AccountRef creditaccountref = new AccountRef
{
FullName = "Cash Discount on Payables"
};
//Classes in QuickBooks equate to Divisions in Apex for this client
ClassRef classref = new ClassRef
{
FullName = apexData.Divisions.Where(s => s.Company == po.Company && s.Division1 == po.Division).Select(s => s.Name).SingleOrDefault()
};
CustomerRef customerref = new CustomerRef
{
ListID = qbjob.QBListID
};
ExpenseLineAdd expenseline = new ExpenseLineAdd
{
AccountRef = accountref,
Amount = invoice.IvcAmt?.ToString("F2"),
CustomerRef = customerref,
Memo = job.Job1 + " " + qbjob.QBJobName.Substring(0, qbjob.QBJobName.IndexOf(':'))
};
if (classref.FullName != null)
{
expenseline.ClassRef = classref;
}
ExpenseLineAdd[] expenseLines;
if ((invoice.Discount ?? 0) != 0) //Add an expense line for the discount amount if the discount is not zero
{
ExpenseLineAdd creditexpenseline = new ExpenseLineAdd
{
AccountRef = creditaccountref,
Amount = (0 - invoice.Discount ?? 0).ToString("F2"),
ClassRef = classref,
Memo = job.Job1 + " " + qbjob.QBJobName.Substring(0, qbjob.QBJobName.IndexOf(':'))
};
expenseLines = new ExpenseLineAdd[2];
expenseLines[0] = expenseline;
expenseLines[1] = creditexpenseline;
}
else
{
expenseLines = new ExpenseLineAdd[1];
expenseLines[0] = expenseline;
}
VendorRef vendorref = new VendorRef
{
ListID = qbvendor.QBListID
};
var billadd = new BillAdd
{
DueDate = invoice.PayDate?.ToString("yyyy-MM-dd"),
Memo = "From Apex",
RefNumber = invoice.Invoice,
TermsRef = termsref,
TxnDate = invoice.IvcDate?.ToString("yyyy-MM-dd"),
ExpenseLineAdd = expenseLines,
VendorRef = vendorref
};
qbMsgsRq.Items = new object[1] {
billaddrq
};
qbxml.Items = new object[1] {
qbMsgsRq
};
billaddrq.BillAdd = billadd;
XmlSerializer serializer = new XmlSerializer(typeof(QBXML));
XmlSerializerNamespaces ns = new XmlSerializerNamespaces();
ns.Add("", ""); //Don't use a namespace in the XML for QuickBooks
MemoryStream ms = new MemoryStream();
serializer.Serialize(ms, qbxml, ns);
ms.Seek(0, SeekOrigin.Begin);
var sr = new StreamReader(ms);
string xmlRequest = sr.ReadToEnd();
xmlRequest = xmlRequest.Replace("<?xml version=\"1.0\"?>", "<?xml version=\"1.0\"?><?qbxml version=\"4.0\"?>");
if (DEBUGMODE)
{
File.WriteAllText("c:\\QB\\BillAddQBXML.xml", xmlRequest);
}
_Response = _Rp.ProcessRequest(_Ticket, xmlRequest);
if (DEBUGMODE)
{
File.WriteAllText("c:\\QB\\BillAddResponse.xml", _Response);
}
QBXML rsXML = GetQbxml(serializer);
string message = ((BillAddRsType)((QBXMLMsgsRs)rsXML?.Items?[0])?.Items?[0]).statusMessage;
string statuscode = ((BillAddRsType)((QBXMLMsgsRs)rsXML?.Items?[0])?.Items?[0]).statusCode;
_StatusLines.Add(new StatusLine
{
Invoice = invoice.Invoice,
PO = invoice.PO.Trim(),
Message = message,
StatusCode = statuscode
});
if (statuscode == "0") //Apex's part is done now that the invoice has been successfully sent to QuickBooks to be paid
{
QBInvoice qbIvc = new QBInvoice
{
Invoice = invoice.Invoice,
PO = invoice.PO,
SentDate = DateTime.Now
};
apexData.QBInvoices.InsertOnSubmit(qbIvc);
invoice.IvcStatus = "P";
apexData.SubmitChanges();
}
}
protected void SendApexInvoices()
{
using (ApexDataDataContext apexData = new ApexDataDataContext(_SqlConnBuilder.ConnectionString))
{
try
{
_Rp = new RequestProcessor2();
_Rp.OpenConnection("", "Apex Interface");
_Ticket = _Rp.BeginSession("", Interop.QBXMLRP2.QBFileMode.qbFileOpenDoNotCare);
_QBCompanyName = Path.GetFileName(_Rp.GetCurrentCompanyFileName(_Ticket));
_ApexTargetCompany = GetApexTargetCompany(_QBCompanyName);
if (_ApexTargetCompany == "*") //invalid company open
{
Application.Exit();
return;
}
lblApexCompany.Text = "Quickbooks Company: " + Path.GetFileName(_QBCompanyName);
lblApexCompany.Visible = true;
lblApexCompany.Refresh();
_StatusLines.Clear();
List <VendIvc> apexInvoiceList = (from ivc in apexData.VendIvcs
join po in apexData.POs on ivc.PO equals po.Po1
where ivc.IvcStatus == "A" &&
po.Company == _ApexTargetCompany
select ivc).ToList();
if (apexInvoiceList.Count == 0)
{
MessageBox.Show("There are no invoices to send.");
}
else
{
_InvoicesSent = true; //We have a valid invoice to send so present the interface status report when complete
try
{
GLAcctUtility.GLAcctList = GLAcctUtility.BuildGLAcctList(apexData);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "G/L Setup Error");
return;
}
foreach (VendIvc invoice in apexInvoiceList)
{
decimal ponet = apexData.POs.Where(s => s.Po1 == invoice.PO).Select(s => s.PoNet).SingleOrDefault() ?? 0;
decimal totalInvoiced = apexData.VendIvcs.Where(s => s.PO == invoice.PO).Sum(s => s.IvcAmt) ?? 0;
if (apexData.QBInvoices.Where(s => s.Invoice == invoice.Invoice && s.PO == invoice.PO).Any())
{
_StatusLines.Add(new StatusLine
{
Invoice = invoice.Invoice,
PO = invoice.PO.Trim(),
Message = "Duplicate invoice"
});
}
else
{
if (totalInvoiced != 0 && (totalInvoiced - ponet > _MaxDiffAmt))
{
_StatusLines.Add(new StatusLine
{
Invoice = invoice.Invoice,
PO = invoice.PO.Trim(),
Message = $"The total invoiced {totalInvoiced:c} exceeds the P/O Net amount {ponet:c} by over {_MaxDiffAmt:c}"
});
}
else
{
decimal ivcTotal = 0; decimal poTaxableAmt = 0;
foreach (VendIvcL ivcLine in invoice.VendIvcLs)
{
ivcTotal += ivcLine.AmtIvc ?? 0;
POLine poline = apexData.POLines.Where(s => s.Po == ivcLine.PO && s.PoLine1 == ivcLine.POLine).SingleOrDefault();
if (poline?.Taxable == "Y")
{
poTaxableAmt += ivcLine.AmtIvc ?? 0;
}
}
double poTaxRate = apexData.POs.Where(s => s.Po1 == invoice.PO).Select(s => s.TaxRate).SingleOrDefault() ?? 0;
ivcTotal += poTaxableAmt * ((decimal)(poTaxRate * 0.01));
decimal invoiceDiff = Math.Abs((invoice.IvcAmt ?? 0) - ivcTotal);
if (ivcTotal != 0 && ((double)((invoiceDiff / ivcTotal)) > (_MaxDiffPct * 0.01) ||
invoiceDiff > _MaxDiffAmt))
{
//This enforces a business rule set by the client regarding tolerances when reconciling a vendor invoice
_StatusLines.Add(new StatusLine
{
Invoice = invoice.Invoice,
PO = invoice.PO.Trim(),
Message = $"The P/O lines invoiced {ivcTotal:c} are more than {_MaxDiffAmt:c} or {_MaxDiffPct}% different from the invoice amount {invoice.IvcAmt:c}"
});
}
else
{
//Initial audits passed; process invoice
ProcessInvoice(invoice, apexData);
}
}
}
}
}
}
catch (System.Runtime.InteropServices.COMException ex)
{
MessageBox.Show("COM Error Description = " + ex.Message, "COM error");
return;
}
finally
{
if (_Ticket != null)
{
_Rp.EndSession(_Ticket);
}
if (_Rp != null)
{
_Rp.CloseConnection();
}
UpdateStatus("Export complete");
}
};
}
private static async Task ProcessCCAsync(XElement ccInfo)
{
string apexJobID = ccInfo.Element("job_num").Value.Trim().PadLeft(12);
if (String.IsNullOrEmpty(apexJobID.Trim()))
{
return;
}
string[] jcc_cc = ccInfo.Element("jcc_cc").Value.Split('-');
if (jcc_cc.Length != 2 && jcc_cc.Length != 3)
{
Console.WriteLine($"Unexpected cost code for job {apexJobID}");
return;
}
string apexCostCode;
string apexPhase = jcc_cc[0];
if (jcc_cc.Length == 2)
{
apexCostCode = jcc_cc[1].PadLeft(9);
}
else
{
apexCostCode = jcc_cc[2].PadLeft(9);
}
using (var dc = new ApexDataDataContext())
{
var apexCC = dc.JobPhCcds.Where(v => v.Job == apexJobID && v.Phase == apexPhase && v.CostCode == apexCostCode).SingleOrDefault();
bool newWBS = (apexCC == null);
if (newWBS)
{
apexCC = new JobPhCcd();
}
apexCC.Job = apexJobID;
apexCC.Phase = apexPhase;
apexCC.CostCode = apexCostCode;
apexCC.Description = LoadValue(ccInfo.Element("jcc_desc").Value, 35);
apexCC.Act = "A";
if (newWBS)
{
dc.JobPhCcds.InsertOnSubmit(apexCC);
if (!dc.JobPhases.Where(p => p.Job == apexJobID && p.Phase == apexPhase).Any())
{
JobPhase jp = new JobPhase
{
Job = apexJobID,
Phase = apexPhase,
Description = $"Phase {apexPhase}",
Act = "A"
};
dc.JobPhases.InsertOnSubmit(jp);
}
}
try
{
dc.SubmitChanges();
Console.WriteLine($" Cost Code {apexJobID}, {apexPhase}-{apexCostCode} processed.");
}
catch (Exception ex)
{
Console.WriteLine($"Error on Cost Code {apexJobID}, {apexPhase}-{apexCostCode}, {ex}");
}
}
}
