public bool UpdateQBOAccess(int id, string accessToken, string refreshToken, QBOAccess ourQBOAccess)
{
try
{
if (id != ourQBOAccess.Id)
{
return(false);
}
//string protectRefreshToken = _protector.Protect(refreshToken);
using (IDbConnection conn = Connection)
{
string sQuery = @"UPDATE [dbo].[QBOAccess] SET [RefreshToken] = @refreshToken, [AccessToken] = @accessToken " +
"WHERE Id = @id";
conn.Open();
var results = conn.Execute(@sQuery, new { id, refreshToken, accessToken });
if (results > 0)
{
return(true);
}
return(false);
}
}
catch (Exception ex)
{
var exResult = ex.Message;
return(false);
}
}
public QBOAccess GetById(int sId)
{
string sQuery = "";
// Get the QBO credentials
using (IDbConnection conn = Connection)
{
var @params = new { SubscriberId = sId };
sQuery = "SELECT * FROM QBOAccess WHERE SubscriberId = @SubscriberId";
conn.Open();
var result = conn.Query <QBOAccess>(sQuery, @params);
if (result.FirstOrDefault() != null)
{
QBOAccess accessResult = result.FirstOrDefault();
//string sRefresh = _protector.Unprotect(accessResult.RefreshToken);
//accessResult.RefreshToken = sRefresh;
return(accessResult);
}
{
return(result.FirstOrDefault());
}
}
}
public ActionResult <QBSyncResponse> Client(int id)
{
//If the id is 0, return all subscribers otherwise return the requested subscriber
string bRtn;
bool updateAccessTableResult;
subscriberId = id;
IEnumerable <Subscriber> subscriber;
currentMethodName = this.ControllerContext.RouteData.Values["action"].ToString();
if (subscriberId == 0)
{
subscriber = _subscriberRepo.GetAllSubscribers();
}
else
{
subscriber = _subscriberRepo.GetById(subscriberId);
if (subscriber == null)
{
_logger.LogError("subscriber not found");
return(new QBSyncResponse()
{
ResponseStatus = false,
ResponseMessage = "Subscriber not found."
});
}
}
foreach (Subscriber subs in subscriber)
{
subscriberId = subs.Id;
_logger.LogInfo("Begin Subscriber " + subscriberId + " Authorization");
QBOAccess qboAccess = _qboaccessRepo.GetById(subscriberId);
if (qboAccess == null)
{
_logger.LogError("You must authorize with QuickBooks before syncing your data.");
return(new QBSyncResponse()
{
ResponseStatus = false,
ResponseMessage = "You must authorize with QuickBooks before syncing your data."
});
}
// save Access Id
int qboAccessId = qboAccess.Id;
// Refresh QBO connection
bRtn = RefreshQBO(qboAccess);
if (bRtn != SuccessMessage)
{
_qboaccessRepo.DeleteQBOAccess(subscriberId);
return(new QBSyncResponse()
{
ResponseStatus = false,
ResponseMessage = bRtn
});
}
// Update Access table with new refresh token
try
{
AESCryptography cryptography = new AESCryptography(_configuration);
appOauthRefreshToken = cryptography.Encrypt(appOauthRefreshToken);
if (IsAllDigits(qboAccess.Company))
{
companyId = cryptography.Encrypt(qboAccess.Company);
}
else
{
companyId = qboAccess.Company;
}
updateAccessTableResult = _qboaccessRepo.UpdateQBOAccess(qboAccessId, companyId, appOauthAccessToken, appOauthRefreshToken, qboAccess);
if (updateAccessTableResult == false)
{
_logger.LogError("You will need to re-authorize your QuickBooks account and try to sync again.");
return(new QBSyncResponse()
{
ResponseStatus = false,
ResponseMessage = "You will need to re-authorize your QuickBooks account and try to sync again."
});
}
}
catch (Exception ex)
{
_logger.LogError("error occurred" + ex.Message);
_errorLogRepo.InsertErrorLog(new ErrorLog
{
SubscriberId = subscriberId,
ErrorMessage = ex.Message,
ServiceName = serviceName,
MethodName = currentMethodName,
ErrorDateTime = DateTime.Now
});
return(new QBSyncResponse()
{
ResponseStatus = false,
ResponseMessage = "Error occurred " + ex.Message
});
}
_logger.LogInfo("End Subscriber " + subscriberId + " Authorization");
//Time to get some data from QBO
_logger.LogInfo("Begin QBO Data Access for Subscriber " + subscriberId);
// Get and Update Customers & Invoices
bRtn = GetQBOCustomers(qboAccess);
if (bRtn != SuccessMessage)
{
_logger.LogError(bRtn);
return(new QBSyncResponse()
{
ResponseStatus = false,
ResponseMessage = bRtn
});
}
_logger.LogInfo("End QBO Data Access for Subscriber " + subscriberId);
//Update the last sync date in the subscriber table
var updateSyncDateResult = _subscriberRepo.UpdateSubscriber(subscriberId, DateTime.Now, subs);
if (updateSyncDateResult == false)
{
_logger.LogError("Not able to update last sync date for subscriber");
return(new QBSyncResponse()
{
ResponseStatus = false,
ResponseMessage = "Not able to update last sync date for subscriber"
});
}
}
return(new QBSyncResponse()
{
ResponseStatus = true,
ResponseMessage = 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);
}
