//Get Customer Invoices private string GetInvoices(Customer customer, int customerId, QuickBooksOnlineConnectionStringBuilder connString) { _logger.LogInfo("Start Add or update invoice operation for customer for customer id " + customerId + " for subscriber id " + subscriberId); // Need to clear the list invoiceList = new List <Invoice>(); // To insert error log in catch statement, made this variable public currentMethodName = this.ControllerContext.RouteData.Values["action"].ToString(); bool addInvoice = false; int CustId; string QBIId; int colIndex = 0; string IDNbr = ""; DateTime IDate; DateTime IDueDate; Decimal ITotalAmt; Decimal IBalance; string ITxns; try { using (QuickBooksOnlineConnection connInv = new QuickBooksOnlineConnection(connString.ToString())) { connInv.RuntimeLicense = runTimeLicense; using (QuickBooksOnlineCommand cmdInv = new QuickBooksOnlineCommand("Select * FROM Invoices WHERE CustomerRef = " + customer.QBCustomerId, connInv)) { using (QuickBooksOnlineDataReader reader = cmdInv.ExecuteReader()) { DateTime ILastPymtDate = DateTime.MaxValue; DateTime ILastReminder = DateTime.MaxValue; while (reader.Read()) { //if (Int32.TryParse((string)reader["Id"], out int IId)) //{ //} //else //{ // continue; //} QBIId = reader.GetString("Id"); CustId = customerId; colIndex = reader.GetOrdinal("DocNumber"); IDNbr = reader.GetString(colIndex); IDate = reader.GetDateTime("TxnDate"); IDueDate = reader.GetDateTime("DueDate"); ITotalAmt = reader.GetDecimal("TotalAmt"); IBalance = reader.GetDecimal("Balance"); colIndex = reader.GetOrdinal("LinkedTxnAggregate"); ITxns = Validate.SafeGetString(reader, colIndex); //Filter Invoices to keep addInvoice = false; if (IBalance > 0) { // we always want to add the invoice if the balance > 0 addInvoice = true; } // Get the last payment date XmlDocument xDoc = new XmlDocument(); // Convert string to stream byte[] byteArray = Encoding.ASCII.GetBytes(ITxns); MemoryStream stream = new MemoryStream(byteArray); if (stream.Length > 0) { xDoc.Load(stream); XmlNodeList xnList = xDoc.SelectNodes("/LinkedTxnAggregate/Row"); // If we have transaction information, process it if (xnList.Count > 0) { foreach (XmlNode xn in xnList) { string txnId = xn["TxnId"].InnerXml; string txnType = xn["TxnType"].InnerXml; if (txnType == "Payment") { DateTime txnDate = GetPymtDate(txnId, connString, IDNbr); DateTime now = DateTime.Now; //for test data //DateTime now = new DateTime(2014, 12, 31); int monthDiff = GetMonthDifference(now, txnDate); if (monthDiff < 6) { ILastPymtDate = txnDate; addInvoice = true; break; } else { if (addInvoice == true) { //Balance is greater than zero //Add the invoice ILastPymtDate = txnDate; } else { addInvoice = false; } break; } } } } } // Don't add the invoice if (addInvoice == false) { continue; } invoiceList.Add(new Invoice { InvoiceId = 0, QBInvoiceId = QBIId, CustomerId = CustId, InvDocNbr = IDNbr, InvDate = IDate, InvDueDate = IDueDate, InvTotalAmt = ITotalAmt, InvBalance = IBalance, InvTxns = ITxns, InvLastPymtDate = ILastPymtDate, InvLastReminder = ILastReminder, SendAutoReminder = true }); } } } } } catch (Exception ex) { _logger.LogError("error occurred at invoice level" + ex.Message); _errorLogRepo.InsertErrorLog(new ErrorLog { SubscriberId = subscriberId, ErrorMessage = ex.Message, InvDocNbr = IDNbr, ServiceName = serviceName, MethodName = currentMethodName, ErrorDateTime = DateTime.Now }); return("Error occurred " + ex.Message); } foreach (var inv in invoiceList) { Invoice invoice = _invoiceRepo.GetByID(inv.CustomerId, inv.QBInvoiceId); if (invoice == null) { // Need to add a invoice record var result = _invoiceRepo.AddInvoice(inv); if (result == false) { return("Not able to add Invoice."); } } else { // Need to update invoice inv.InvoiceId = invoice.InvoiceId; var result = _invoiceRepo.UpdateInvoice(inv); if (result == false) { return("Not able to update Invoice."); } } } _logger.LogInfo("End Add or update invoice operation for customer for customer id " + customerId + " for subscriber id " + subscriberId); return(SuccessMessage); }
// Get Customers private string GetQBOCustomers(QBOAccess qboAccess) { _logger.LogInfo("Begin Get QBO Customer Access for Subscriber " + subscriberId); var connString = new QuickBooksOnlineConnectionStringBuilder(); connString.Offline = false; connString.OAuthAccessToken = appOauthAccessToken; connString.OAuthClientId = qboAccess.ClientId; connString.OAuthClientSecret = qboAccess.ClientSecret; connString.CompanyId = qboAccess.Company; connString.OAuthVersion = "2.0"; connString.UseSandbox = useSandBox; // To insert error log in catch statement, made this variable public currentMethodName = this.ControllerContext.RouteData.Values["action"].ToString(); int colIndex = 0; string QBCId; string GName; string FName; string Suf; string DName = ""; string CName; string PPhone; string MPhone; string PEmail; // string Nte; try { using (QuickBooksOnlineConnection connQBO = new QuickBooksOnlineConnection(connString.ToString())) { connQBO.RuntimeLicense = runTimeLicense; using (QuickBooksOnlineCommand cmdQBO = new QuickBooksOnlineCommand("Select * FROM Customers WHERE Balance != 0", connQBO)) { _logger.LogInfo("Successfully called select query for Subscriber " + subscriberId); using (QuickBooksOnlineDataReader reader = cmdQBO.ExecuteReader()) { _logger.LogInfo("Start reading data from quickbook for subscriber id " + subscriberId); while (reader.Read()) { //if (Int32.TryParse((string)reader["Id"], out int CId)) //{ //} //else //{ // continue; //} QBCId = reader.GetString("Id"); colIndex = reader.GetOrdinal("GivenName"); GName = Validate.SafeGetString(reader, colIndex); colIndex = reader.GetOrdinal("FamilyName"); FName = Validate.SafeGetString(reader, colIndex); colIndex = reader.GetOrdinal("Suffix"); Suf = Validate.SafeGetString(reader, colIndex); colIndex = reader.GetOrdinal("DisplayName"); DName = Validate.SafeGetString(reader, colIndex); colIndex = reader.GetOrdinal("CompanyName"); CName = Validate.SafeGetString(reader, colIndex); colIndex = reader.GetOrdinal("PrimaryPhone_FreeFormNumber"); PPhone = Validate.SafeGetString(reader, colIndex); colIndex = reader.GetOrdinal("Mobile_FreeFormNumber"); MPhone = Validate.SafeGetString(reader, colIndex); colIndex = reader.GetOrdinal("PrimaryEmailAddr_Address"); PEmail = Validate.SafeGetString(reader, colIndex); //colIndex = reader.GetOrdinal("Notes"); //Nte = Validate.SafeGetString(reader, colIndex); customerList.Add(new Customer { CustomerId = 0, QBCustomerId = QBCId, GivenName = GName, FamilyName = FName, Suffix = Suf, DisplayName = DName, CompanyName = CName, Active = (bool)reader["Active"], PrimaryPhone = PPhone, MobilePhone = MPhone, PrimaryEmailAddress = PEmail, Balance = Convert.ToDecimal(reader["Balance"]), // Notes = Nte, SubscriberId = subscriberId, SendAutoReminder = true }); } } } } } catch (Exception ex) { _logger.LogError("Error occurred at cusomer level " + ex.Message); _errorLogRepo.InsertErrorLog(new ErrorLog { SubscriberId = qboAccess.Id, ErrorMessage = ex.Message, DisplayName = DName, ServiceName = serviceName, MethodName = currentMethodName, ErrorDateTime = DateTime.Now }); return("Error occurred " + ex.Message); } foreach (var cust in customerList) { _logger.LogInfo("Start operation for customer for customer id " + cust.QBCustomerId + " for subscriber id " + subscriberId); Customer customer = _customerRepo.GetByID(subscriberId, cust.QBCustomerId); if (customer == null) { // customer not found, add it var customerId = _customerRepo.AddCustomer(cust); if (customerId == 0) { return("Not able to add customer in customer table."); } // Get any invoices this customer may have _logger.LogInfo("Add or update invoice operation for customer for customer id " + cust.QBCustomerId + " for subscriber id " + subscriberId); var result = GetInvoices(cust, customerId, connString); if (result != SuccessMessage) { return(result); } } else { // we found a customer update it cust.CustomerId = customer.CustomerId; var result = _customerRepo.UpdateCustomer(cust); if (result == false) { return("Not able to update customer in customer table."); } // Get any invoices this customer may have var invoiceResult = GetInvoices(cust, customer.CustomerId, connString); if (invoiceResult != SuccessMessage) { return(invoiceResult); } } _logger.LogInfo("End operation for customer for customer id " + cust.QBCustomerId + " for subscriber id " + subscriberId); } _logger.LogInfo("End Get QBO Customer Access for Subscriber " + subscriberId); return(SuccessMessage); }