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