//affirmmedicalweightlossEntities db = new affirmmedicalweightlossEntities();
public static String AddCustomer(String firstName, String lastName, String phone, String eMail, String address, String city, String state, String zip, char sex, String dateOfBirth)
{
string connStr = ConfigurationManager.ConnectionStrings["affirmWL"].ConnectionString;
MySqlConnection msqcon = new MySqlConnection(connStr);
MySqlCommand msqcom = new MySqlCommand(connStr);
String messageFromSDK = "";
String Salutation = "";
if (sex == 'M')
{
Salutation = "Mr.";
}
else
{
Salutation = "Ms.";
}
//create a new Customer object that we will add to the DB through Entity Framework
customer newCustomer = new customer();
//instantiate properties we already know about the Customer to add
newCustomer.Name = (firstName + " " + lastName);
newCustomer.FirstName = firstName;
newCustomer.LastName = lastName;
newCustomer.BillAddress_Addr1 = address;
newCustomer.BillAddress_City = city;
newCustomer.BillAddress_State = state;
newCustomer.BillAddress_PostalCode = zip;
newCustomer.Phone = phone;
newCustomer.Email = eMail;
newCustomer.Salutation = Salutation;
newCustomer.Birthday = dateOfBirth;
//add customer to QuickBooks file
try
{
//Create the session Manager object
QBSessionManager sessionManager = new QBSessionManager();
//Create the message set request object to hold our request
IMsgSetRequest requestMsgSet = sessionManager.CreateMsgSetRequest("US", 8, 0);
requestMsgSet.Attributes.OnError = ENRqOnError.roeContinue;
//Connect to QuickBooks and begin a session
sessionManager.OpenConnection(@"C:\Users\User\Documents\Affirm Medical Weightloss.QBW", "Affirm Medical Main Data Portal");
//sessionManager.OpenConnection(@"\\USER-PC\quickbooks_affirm14\Affirm Medical Weightloss", "Affirm Medical Main Data Portal");
//sessionManager.BeginSession("C:/Users/User/Documents/Affirm Medical Weightloss.QBW", ENOpenMode.omDontCare);
sessionManager.BeginSession("", ENOpenMode.omDontCare);
//sessionBegun = true;
//find customer
//add the customer
ICustomerAdd customerAddRq = requestMsgSet.AppendCustomerAddRq();
customerAddRq.Name.SetValue(newCustomer.Name);
customerAddRq.FirstName.SetValue(firstName);
customerAddRq.LastName.SetValue(lastName);
customerAddRq.Phone.SetValue(phone);
customerAddRq.Email.SetValue(eMail);
customerAddRq.BillAddress.Addr1.SetValue(address);
customerAddRq.BillAddress.City.SetValue(city);
customerAddRq.BillAddress.State.SetValue(state);
customerAddRq.BillAddress.PostalCode.SetValue(zip);
customerAddRq.Salutation.SetValue(Salutation);
//modify CustomField1 (Birthday field) for the new customer
IDataExtMod customerExtModRq = requestMsgSet.AppendDataExtModRq();
customerExtModRq.OwnerID.SetValue("0");
customerExtModRq.DataExtName.SetValue("Birthday");
customerExtModRq.DataExtValue.SetValue(dateOfBirth);
customerExtModRq.ORListTxn.ListDataExt.ListDataExtType.SetValue(ENListDataExtType.ldetCustomer);
customerExtModRq.ORListTxn.ListDataExt.ListObjRef.FullName.SetValue(newCustomer.Name);
//Send the request and get the response from QuickBooks
IMsgSetResponse responseMsgSet = sessionManager.DoRequests(requestMsgSet);
IResponse response = responseMsgSet.ResponseList.GetAt(0);
ICustomerRet customerRet = (ICustomerRet)response.Detail;
messageFromSDK = responseMsgSet.ResponseList + response.StatusMessage + customerRet;
newCustomer.ListID = customerRet.ListID.GetValue();
newCustomer.TimeCreated = System.Convert.ToString(customerRet.TimeCreated.GetValue());
newCustomer.TimeModified = System.Convert.ToString(customerRet.TimeModified.GetValue());
newCustomer.EditSequence = customerRet.EditSequence.GetValue();
sessionManager.EndSession();
sessionManager.CloseConnection();
//Customer.QuickBooksID = customerRet.ListID.GetValue();
}
catch (Exception ex)
{
return "There was an error attempting to connect to\nQuickBooks for customer additions:\n" + ex.Message;
}
//add customer to local DB
try
{
msqcon.Open();
String query = "SELECT COUNT(*) FROM customer WHERE firstName = '" + firstName + "' AND lastName = '" + lastName + "'";
MySqlCommand msqfetch = new MySqlCommand(query, msqcon);
MySqlDataReader msqfind = msqfetch.ExecuteReader();
int duplicates = 0;
while (msqfind.Read())
{
duplicates = System.Convert.ToInt32(msqfind["COUNT(*)"]);
}
if (duplicates > 0)
{
msqcon.Close();
return "Duplicates: " + duplicates;
}
else
{
msqcon.Close();
db.Entry(newCustomer).State = EntityState.Added;
db.SaveChanges();
//query = "INSERT INTO customers (FirstName, LastName, Phone, Email, BillAddress_Addr1, BillAddress_City, BillAddress_State, BillAddress_PostalCode, Salutation, DateOfBirth)" +
// "Values ('" + firstName + "', '" + lastName + "', '" + phone + "', '" + eMail + "', '" + address +
// "', '" + city + "', '" + state + "', '" + zip + "', '" + Salutation + "', '" + dateOfBirth + "')";
//msqcom = new MySqlCommand(query, msqcon);
//msqcom.ExecuteNonQuery();
//msqcon.Close();
}
}
catch (MySqlException e)
{
return "Error connecting to database! Check your connection\nand make sure your database is online and accessible: "
+ e.Message;
}
//return "Customer successfully added to database and QuickBooks.";
return "Operation successful, following message returned from QuickBooks: " + messageFromSDK;
}
public static String AddSimilar(String firstName, String lastName, String phone, String eMail, String address, String city, String state, int zip, char sex, String dateOfBirth, int dupInc)
{
string connStr = ConfigurationManager.ConnectionStrings["affirmWL"].ConnectionString;
MySqlConnection msqcon = new MySqlConnection(connStr);
MySqlCommand msqcom = new MySqlCommand(connStr);
try
{
msqcon.Open();
String query = "SELECT * FROM customers WHERE firstName = '" + firstName + "' AND lastName = '" + lastName + "'";
MySqlCommand msqfetch = new MySqlCommand(query, msqcon);
MySqlDataReader msqfind = msqfetch.ExecuteReader();
String addressMatch = "";
String cityMatch = "";
String stateMatch = "";
String dateOfBirthMatch = "";
int zipMatch = 0;
while (msqfind.Read())
{
addressMatch = System.Convert.ToString(msqfind["BillAddress_Addr1"]);
cityMatch = System.Convert.ToString(msqfind["BillAddress_City"]);
stateMatch = System.Convert.ToString(msqfind["BillAddress_State"]);
zipMatch = System.Convert.ToInt32(msqfind["BillAddress_PostalCode"]);
dateOfBirthMatch = System.Convert.ToString(msqfind["Birthday"]);
}
if (address.Equals(addressMatch) && city.Equals(cityMatch) && state.Equals(stateMatch) && (zip == zipMatch) && dateOfBirth.Equals(dateOfBirthMatch))
{
msqcon.Close();
return "No duplicate entries allowed (exact same address information).";
}
else
{
msqfind.Close();
msqcon.Close();
String Salutation = "";
if (sex == 'M')
{
Salutation = "Mr.";
}
else
{
Salutation = "Ms.";
}
customer newCustomer = new customer();
//instantiate properties we already know about the Customer to add
newCustomer.Name = (firstName + " " + lastName);
newCustomer.FirstName = firstName;
newCustomer.LastName = lastName;
newCustomer.BillAddress_Addr1 = address;
newCustomer.BillAddress_City = city;
newCustomer.BillAddress_State = state;
newCustomer.BillAddress_PostalCode = System.Convert.ToString(zip);
newCustomer.Phone = phone;
newCustomer.Email = eMail;
newCustomer.Salutation = Salutation;
newCustomer.Birthday = dateOfBirth;
//query = "INSERT INTO customers (firstName, lastName, primaryPhone, primaryEmail, address, city, state, zip, gender)" +
// "Values ('" + firstName + "', '" + lastName + "', '" + phone + "', '" + eMail + "', '" + address +
// "', '" + city + "', '" + state + "', '" + zip + "', '" + sex + "')";
//msqcom = new MySqlCommand(query, msqcon);
//msqcom.ExecuteNonQuery();
msqcon.Close();
}
}
catch (MySqlException e)
{
return "Error connecting to database! Check your connection\nand make sure your database is online and accessible.";
}
try
{
//Create the session Manager object
QBSessionManager sessionManager = new QBSessionManager();
//Create the message set request object to hold our request
IMsgSetRequest requestMsgSet = sessionManager.CreateMsgSetRequest("US", 8, 0);
requestMsgSet.Attributes.OnError = ENRqOnError.roeContinue;
//Connect to QuickBooks and begin a session
sessionManager.OpenConnection(@"C:\Users\User\Documents\Affirm Medical Weightloss.QBW", "Affirm Medical Main Data Portal");
//sessionManager.OpenConnection(@"\\USER-PC\quickbooks_affirm14\Affirm Medical Weightloss", "Affirm Medical Main Data Portal");
//sessionManager.BeginSession("C:/Users/User/Documents/Affirm Medical Weightloss.QBW", ENOpenMode.omDontCare);
sessionManager.BeginSession("C:\\Users\\User\\Documents\\Affirm Medical Weightloss.QBW", ENOpenMode.omDontCare);
//sessionBegun = true;
//add the customer
ICustomerAdd customerAddRq = requestMsgSet.AppendCustomerAddRq();
customerAddRq.Name.SetValue(firstName + " " + lastName + "(" + dupInc + ")");
customerAddRq.FirstName.SetValue(firstName);
customerAddRq.LastName.SetValue(lastName);
customerAddRq.Phone.SetValue(phone);
customerAddRq.Email.SetValue(eMail);
customerAddRq.BillAddress.Addr1.SetValue(address);
customerAddRq.BillAddress.City.SetValue(city);
customerAddRq.BillAddress.State.SetValue(state);
customerAddRq.BillAddress.PostalCode.SetValue(System.Convert.ToString(zip));
if (sex.Equals('M'))
{
customerAddRq.Salutation.SetValue("Mr.");
}
else
{
customerAddRq.Salutation.SetValue("Ms.");
}
//modify CustomField1 (Birthday field) for the new customer
IDataExtMod customerExtModRq = requestMsgSet.AppendDataExtModRq();
customerExtModRq.OwnerID.SetValue("0");
customerExtModRq.DataExtName.SetValue("Birthday");
customerExtModRq.DataExtValue.SetValue(dateOfBirth);
customerExtModRq.ORListTxn.ListDataExt.ListDataExtType.SetValue(ENListDataExtType.ldetCustomer);
customerExtModRq.ORListTxn.ListDataExt.ListObjRef.FullName.SetValue(firstName + " " + lastName + "(" + dupInc + ")");
//Send the request and get the response from QuickBooks
IMsgSetResponse responseMsgSet = sessionManager.DoRequests(requestMsgSet);
IResponse response = responseMsgSet.ResponseList.GetAt(0);
ICustomerRet customerRet = (ICustomerRet)response.Detail;
sessionManager.EndSession();
sessionManager.CloseConnection();
//Customer.QuickBooksID = customerRet.ListID.GetValue();
}
catch (Exception ex)
{
return "There was an error attempting to connect to\nQuickBooks for customer additions:\n" + ex.Message;
}
return "This similar customer was successfully\nadded to database and QuickBooks.";
}
