public static string SafeGetString(this QuickBooksOnlineDataReader reader, int colIndex)
{
try
{
if (!reader.IsDBNull(colIndex))
{
return(reader.GetString(colIndex));
}
return(string.Empty);
}
catch (Exception ex)
{
return(string.Empty);
}
}
//Get Invoice Payments
private DateTime GetPymtDate(string txnId, QuickBooksOnlineConnectionStringBuilder connString, string IDNbr)
{
//For test data
//DateTime toDay = new DateTime(2014, 12, 31);
currentMethodName = this.ControllerContext.RouteData.Values["action"].ToString();
DateTime toDay = DateTime.Now;
try
{
using (QuickBooksOnlineConnection connPymt = new QuickBooksOnlineConnection(connString.ToString()))
{
connPymt.RuntimeLicense = runTimeLicense;
using (QuickBooksOnlineCommand cmdPymt = new QuickBooksOnlineCommand("Select TxnDate FROM Payments WHERE Id = " + txnId, connPymt))
{
using (QuickBooksOnlineDataReader reader = cmdPymt.ExecuteReader())
{
while (reader.Read())
{
DateTime txnDate = reader.GetDateTime("TxnDate");
return(txnDate);
}
}
}
}
return(toDay);
}
catch (Exception ex)
{
_errorLogRepo.InsertErrorLog(new ErrorLog
{
SubscriberId = subscriberId,
ErrorMessage = ex.Message,
InvDocNbr = IDNbr,
ServiceName = serviceName,
MethodName = currentMethodName,
ErrorDateTime = DateTime.Now
});
return(toDay);
}
}
//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);
}
//Refresh QBO
private string RefreshQBO(QBOAccess qboAccess)
{
_logger.LogInfo("Refresh QBO started for subscriber id" + subscriberId);
AESCryptography cryptography = new AESCryptography(_configuration);
var connString = new QuickBooksOnlineConnectionStringBuilder();
connString.Offline = false;
connString.OAuthClientId = qboAccess.ClientId;
connString.OAuthClientSecret = qboAccess.ClientSecret;
if (!IsAllDigits(qboAccess.Company))
{
qboAccess.Company = cryptography.Decrypt(qboAccess.Company);
qboAccess.RefreshToken = cryptography.Decrypt(qboAccess.RefreshToken);
}
connString.CompanyId = qboAccess.Company;
connString.OAuthRefreshToken = qboAccess.RefreshToken;
connString.OAuthVersion = "2.0";
connString.UseSandbox = useSandBox;
//connString.InitiateOAuth = "GETANDREFRESH";
connString.Logfile = "c:\\users\\public\\documents\\rssApiLog.txt";
connString.Verbosity = "5";
currentMethodName = this.ControllerContext.RouteData.Values["action"].ToString();
try
{
using (QuickBooksOnlineConnection connQBO = new QuickBooksOnlineConnection(connString.ToString()))
{
connQBO.RuntimeLicense = runTimeLicense;
using (QuickBooksOnlineCommand cmdQBO = new QuickBooksOnlineCommand("RefreshOAuthAccessToken", connQBO))
{
cmdQBO.Parameters.Add(new QuickBooksOnlineParameter("OAuthRefreshToken", qboAccess.RefreshToken));
cmdQBO.CommandType = CommandType.StoredProcedure;
using (QuickBooksOnlineDataReader reader = cmdQBO.ExecuteReader())
{
if (reader.Read())
{
appOauthAccessToken = (String)reader["OAuthAccessToken"];
appOauthRefreshToken = (String)reader["OAuthRefreshToken"];
}
else
{
_errorLogRepo.InsertErrorLog(new ErrorLog
{
SubscriberId = qboAccess.SubscriberId,
ErrorMessage = "You will need to re-authorize your QuickBooks account and try to sync again.",
ServiceName = serviceName,
MethodName = currentMethodName,
ErrorDateTime = DateTime.Now
});
return("You will need to re-authorize your QuickBooks account and try to sync again.");
}
}
}
}
}
catch (Exception ex)
{
_logger.LogError("error occurred in refresh qbo method " + ex.Message);
_errorLogRepo.InsertErrorLog(new ErrorLog
{
SubscriberId = qboAccess.SubscriberId,
ErrorMessage = ex.Message,
ServiceName = serviceName,
MethodName = currentMethodName,
ErrorDateTime = DateTime.Now
});
return(ex.Message);
}
_logger.LogInfo("Refresh QBO ended for subscriber id" + subscriberId);
return(SuccessMessage);
}
public ActionResult <bool> BeginAuthorize(int id)
{
_logger.LogInfo("Start BeginAuthorize for Subscriber " + id);
HttpContext.Session.SetInt32("subscriberId", id); // set the session subscriber id
var connString = new QuickBooksOnlineConnectionStringBuilder();
connString.Offline = false;
connString.OAuthClientId = appClientId;
connString.OAuthClientSecret = appClientSecret;
connString.UseSandbox = useSandBox;
connString.Logfile = "c:\\users\\public\\documents\\QBOLog.txt";
connString.Verbosity = "5";
String callbackURL = _configuration["CFXServiceConfiguration:AuthanticateServiceEndPoint"] + "api/master/finalauthorize";
currentMethodName = this.ControllerContext.RouteData.Values["action"].ToString();
try
{
using (QuickBooksOnlineConnection connQBO = new QuickBooksOnlineConnection(connString.ToString()))
{
connQBO.RuntimeLicense = runTimeLicense;
using (QuickBooksOnlineCommand cmdQBO = new QuickBooksOnlineCommand("GetOAuthAuthorizationURL", connQBO))
{
cmdQBO.Parameters.Add(new QuickBooksOnlineParameter("CallbackURL", callbackURL));
cmdQBO.CommandType = CommandType.StoredProcedure;
using (QuickBooksOnlineDataReader reader = cmdQBO.ExecuteReader())
{
if (reader.Read())
{
Response.Redirect(reader["URL"] as string, false);
}
else
{
_errorLogRepo.InsertErrorLog(new ErrorLog
{
SubscriberId = id,
ErrorMessage = "No Authorization URL available",
ServiceName = serviceName,
MethodName = currentMethodName,
ErrorDateTime = DateTime.Now
});
return(false);
}
}
}
}
}
catch (Exception ex)
{
_errorLogRepo.InsertErrorLog(new ErrorLog
{
SubscriberId = id,
ErrorMessage = ex.Message,
ServiceName = serviceName,
MethodName = currentMethodName,
ErrorDateTime = DateTime.Now
});
return(false);
}
_logger.LogInfo("End BeginAuthorize for Subscriber " + id);
return(true);
}
public ContentResult FinalAuthorize()
{
string verifierToken = "";
var webRoot = _env.ContentRootPath;
var successFileContent = System.IO.File.ReadAllText(webRoot + "/HTMLResponse/Successful.html");
var failureFileContent = System.IO.File.ReadAllText(webRoot + "/HTMLResponse/Unsuccessful.html");
int sid;
try
{
sid = (int)HttpContext.Session.GetInt32("subscriberId");
}
catch (Exception)
{
sid = _cache.Get <int>("subscriberId");
}
// will delete this
//var qboQueryString = Request.Query;
//foreach (var qbItem in qboQueryString.Keys)
//{
// System.Diagnostics.Debug.WriteLine("Key: " + qbItem + ", Value: " + Request.Query[qbItem]);
//}
_logger.LogInfo("Start FinalAuthorize for Subscriber " + sid);
string qboCode = Request.Query["code"];
if (qboCode == null)
{
return(new ContentResult
{
ContentType = "text/html",
Content = failureFileContent
});
}
verifierToken = qboCode;
companyId = Request.Query["realmid"];
var connString = new QuickBooksOnlineConnectionStringBuilder();
connString.OAuthClientId = appClientId;
connString.OAuthClientSecret = appClientSecret;
connString.UseSandbox = useSandBox;
connString.Logfile = "c:\\users\\public\\documents\\QBOLog.txt";
connString.Verbosity = "5";
string callbackURL = _configuration["CFXServiceConfiguration:AuthanticateServiceEndPoint"] + "api/master/finalauthorize";
currentMethodName = this.ControllerContext.RouteData.Values["action"].ToString();
try
{
using (QuickBooksOnlineConnection connQBO = new QuickBooksOnlineConnection(connString.ToString()))
{
connQBO.RuntimeLicense = runTimeLicense;
using (QuickBooksOnlineCommand cmdQBO = new QuickBooksOnlineCommand("GetOAuthAccessToken", connQBO))
{
cmdQBO.Parameters.Add(new QuickBooksOnlineParameter("Authmode", "WEB"));
cmdQBO.Parameters.Add(new QuickBooksOnlineParameter("Verifier", verifierToken));
cmdQBO.Parameters.Add(new QuickBooksOnlineParameter("CompanyId", companyId));
cmdQBO.Parameters.Add(new QuickBooksOnlineParameter("CallbackURL", callbackURL));
cmdQBO.CommandType = CommandType.StoredProcedure;
using (QuickBooksOnlineDataReader reader = cmdQBO.ExecuteReader())
{
if (reader.Read())
{
appOauthAccessToken = (String)reader["OAuthAccessToken"];
appOauthRefreshToken = (String)reader["OAuthRefreshToken"];
}
else
{
_errorLogRepo.InsertErrorLog(new ErrorLog
{
SubscriberId = sid,
ErrorMessage = "No OAuthRefreshToken available",
ServiceName = serviceName,
MethodName = currentMethodName,
ErrorDateTime = DateTime.Now
});
return(new ContentResult
{
ContentType = "text/html",
Content = failureFileContent
});
}
}
}
}
}
catch (Exception ex)
{
_errorLogRepo.InsertErrorLog(new ErrorLog
{
SubscriberId = sid,
ErrorMessage = ex.Message,
ServiceName = serviceName,
MethodName = currentMethodName,
ErrorDateTime = DateTime.Now
});
return(new ContentResult
{
ContentType = "text/html",
Content = failureFileContent
});
}
// Get/Update our QBOAccess record
bool bRtn;
DataServices.Models.QBOAccess qboAccess = _qboaccessRepo.GetById(sid);
AESCryptography cryptography = new AESCryptography(_configuration);
companyId = cryptography.Encrypt(companyId);
appOauthRefreshToken = cryptography.Encrypt(appOauthRefreshToken);
if (qboAccess == null)
{
bRtn = _qboaccessRepo.AddQBOAccess(appClientId, appClientSecret, companyId, appOauthAccessToken, appOauthRefreshToken, sid);
}
else
{
bRtn = _qboaccessRepo.UpdateQBOAccess(qboAccess.Id, companyId, appOauthAccessToken, appOauthRefreshToken, qboAccess);
}
if (bRtn == true)
{
_logger.LogInfo("End FinalAuthorize for Subscriber " + sid);
return(new ContentResult
{
ContentType = "text/html",
Content = successFileContent
});
}
return(new ContentResult
{
ContentType = "text/html",
Content = failureFileContent
});
}
