private void DBhandler()
{
_path = Path.Combine(RepairShoprUtils.folderPath, "RepairShopr.db3");
FileInfo fileInfo = new FileInfo(_path);
if (!fileInfo.Exists)
{
SQLiteConnection.CreateFile(_path);
try
{
using (SQLiteConnection con = new SQLiteConnection("data source=" + _path + ";PRAGMA journal_mode=WAL;"))
{
con.SetPassword("shyam");
con.Open();
SQLiteCommand cmdTableAccount = new SQLiteCommand("CREATE TABLE Account (Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,AccountId nvarchar(50),CustomerId nvarchar(50))", con);
cmdTableAccount.ExecuteNonQuery();
SQLiteCommand cmdTableTicket = new SQLiteCommand("CREATE TABLE Ticket (Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,TicketId nvarchar(50),RTicketId nvarchar(30))", con);
cmdTableTicket.ExecuteNonQuery();
SQLiteCommand cmdTableInvoice = new SQLiteCommand("CREATE TABLE Invoice (Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,InvoiceId nvarchar(50),RinvoiceId nvarchar(50))", con);
cmdTableInvoice.ExecuteNonQuery();
con.Close();
}
}
catch (Exception ex)
{
RepairShoprUtils.LogWriteLineinHTML("Failed to Create Datebase Setting", MessageSource.Initialization, ex.StackTrace, messageType.Error);
}
}
}
private void backgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
progressBar1.Value = 100;
buttonStop.Enabled = false;
buttonExport.Enabled = true;
label3.Text = "Exporting Process is Completed";
RepairShoprUtils.LogWriteLineinHTML("Exporting Process is Completed ", MessageSource.Complete, "", messageType.Information);
}
public MainForm()
{
InitializeComponent();
label1.Text = string.Empty;
this.Text = "CommitCRM Exporter";
buttonExport.Enabled = false;
progressBar1.Visible = false;
label3.Text = "";
RepairShoprUtils.LogWriteLineinHTML("Initializing System with Default value", MessageSource.Initialization, "", messageType.Information);
CommitCRMHandle();
DBhandler();
}
protected async Task <Ticket> ExportSingleTicket(CommitCRM.Ticket ticket, int?defaultLocationId, string customerId, SQLiteConnection connection, string remoteHost)
{
return(await Task.Factory.StartNew(() =>
{
var myNameValueCollection = new NameValueCollection();
string subject = string.Empty;
if (!string.IsNullOrEmpty(ticket.Description))
{
if (ticket.Description.Length > 255)
{
subject = ticket.Description.Substring(0, 255);
}
else
{
subject = ticket.Description;
}
}
if (defaultLocationId.HasValue)
{
myNameValueCollection.Add("location_id", defaultLocationId.Value.ToString());
}
myNameValueCollection.Add("subject", subject);
myNameValueCollection.Add("customer_id", customerId);
if (!string.IsNullOrEmpty(ticket.TicketType))
{
myNameValueCollection.Add("problem_type", ticket.TicketType);
}
myNameValueCollection.Add("status", "Resolved");
myNameValueCollection.Add("comment_body", GetComment(ticket));
if (!string.IsNullOrEmpty(ticket.Status_Text))
{
myNameValueCollection.Add("comment_subject", ticket.Status_Text);
}
myNameValueCollection.Add("comment_hidden", "1");
myNameValueCollection.Add("comment_do_not_email", "1");
string createDate = HttpUtility.UrlEncode(ticket.UpdateDate.ToString("yyyy-MM-dd H:mm:ss"));
myNameValueCollection.Add("created_at", createDate);
var newTicket = RepairShoprUtils.ExportTicket(myNameValueCollection, remoteHost);
if (newTicket != null)
{
using (SQLiteCommand cmdINewItem = new SQLiteCommand(string.Format("INSERT INTO Ticket (TicketId,RTicketId) VALUES('{0}','{1}')", ticket.TicketREC_ID, newTicket.Id), connection))
cmdINewItem.ExecuteNonQuery();
}
return newTicket;
}));
}
private void buttonLogin_Click(object sender, EventArgs e)
{
label1.ForeColor = Color.Green;
label1.Text = "Login in progress";
if (string.IsNullOrEmpty(textBoxUserName.Text))
{
errorProvider1.SetError(textBoxUserName, "User Name is Required field");
label1.ForeColor = Color.Red;
label1.Text = "User Name is Required field";
RepairShoprUtils.LogWriteLineinHTML("User Name is is Required ", MessageSource.Login, "", messageType.Error);
return;
}
else
{
errorProvider1.Clear();
label1.ForeColor = Color.Green;
}
if (string.IsNullOrEmpty(textBoxPassWord.Text))
{
label1.ForeColor = Color.Red;
errorProvider2.SetError(textBoxPassWord, "Password is Required field");
label1.Text = "Password is Required field";
RepairShoprUtils.LogWriteLineinHTML("Password is Required field ", MessageSource.Login, "", messageType.Error);
return;
}
else
{
errorProvider2.Clear();
label1.ForeColor = Color.Green;
}
RepairShoprUtils.LogWriteLineinHTML("Sending User Name and Password for Authentication", MessageSource.Login, "", messageType.Information);
LoginResponse result = RepairShoprUtils.GetLoginResquest(textBoxUserName.Text.Trim(), textBoxPassWord.Text.Trim(), _remoteHost);
if (result != null)
{
label1.Text = "Login Successful";
buttonExport.Enabled = true;
_defaultLocationId = result.DefaulLocationId;
}
else
{
label1.ForeColor = Color.Red;
label1.Text = "Failed to Authenticate";
_defaultLocationId = null;
}
}
private void resetConfigurationToolStripMenuItem_Click(object sender, EventArgs e)
{
int result = -1;
int rsult = -1;
RepairShoprUtils.LogWriteLineinHTML("Reseting Configuration including Database.. ", MessageSource.Initialization, "", messageType.Information);
using (SQLiteConnection conn = new SQLiteConnection("data source=" + _path + ";PRAGMA journal_mode=WAL;Password=shyam;"))
{
conn.Open();
using (SQLiteCommand cmdAccountDelete = new SQLiteCommand("DELETE From Account", conn))
{
result = cmdAccountDelete.ExecuteNonQuery();
}
using (SQLiteCommand cmdTicketDelete = new SQLiteCommand("DELETE From Ticket", conn))
{
rsult = cmdTicketDelete.ExecuteNonQuery();
}
var defaultTickets = new CommitCRM.ObjectQuery <CommitCRM.Ticket>(CommitCRM.LinkEnum.linkAND, 1);
defaultTickets.AddSortExpression(CommitCRM.Ticket.Fields.UpdateDate, CommitCRM.SortDirectionEnum.sortASC);
var defaultTicketResult = defaultTickets.FetchObjects();
string ticketNumber = string.Empty;
if (defaultTicketResult != null && defaultTicketResult.Count > 0)
{
ticketNumber = defaultTicketResult[0].TicketNumber;
}
Properties.Settings.Default.TicketNumber = ticketNumber;
var creationDate = GetCreationDate();
Properties.Settings.Default.CustomerExport = creationDate;
Properties.Settings.Default.TicketExport = creationDate;
Properties.Settings.Default.Save();
if (result != -1 && rsult != -1)
{
RepairShoprUtils.LogWriteLineinHTML("Reset Configuration including Database successfull.", MessageSource.Initialization, "", messageType.Information);
MessageBox.Show("Configuration is reset successfull", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
conn.Close();
}
}
private void buttonExport_Click(object sender, EventArgs e)
{
RepairShoprUtils.LogWriteLineinHTML("Export Button Clicked", MessageSource.Login, "", messageType.Information);
if (string.IsNullOrEmpty(Properties.Settings.Default.InstalledLocation))
{
CommitCRMHandle();
}
if (string.IsNullOrEmpty(Properties.Settings.Default.InstalledLocation))
{
MessageBox.Show("CommitCRM location is not browsed. The export operation is terminated", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (_bgw == null)
{
_bgw = new BackgroundWorker();
_bgw.DoWork += new DoWorkEventHandler(backgroundWorker_DoWork);
_bgw.ProgressChanged += new ProgressChangedEventHandler(backgroundWorker_ProgressChanged);
_bgw.RunWorkerCompleted += new RunWorkerCompletedEventHandler(backgroundWorker_RunWorkerCompleted);
_bgw.WorkerReportsProgress = true;
_bgw.WorkerSupportsCancellation = true;
}
_exportTicket = checkBoxExportTicket.Checked;
_exportCustomer = checkBoxExportCustomer.Checked;
if (radioButtonRepairShopr.Checked)
{
_remoteHost = "repairshopr.com";
}
else
{
_remoteHost = "syncromsp.com";
}
progressBar1.Value = 0;
progressBar1.Visible = true;
progressBar1.Enabled = true;
_bgw.RunWorkerAsync();
buttonStop.Enabled = true;
}
private void CommitCRMHandle()
{
try
{
bool folderExist = false;
if (!string.IsNullOrEmpty(Properties.Settings.Default.InstalledLocation))
{
folderExist = Directory.Exists(Properties.Settings.Default.InstalledLocation);
}
if (!folderExist)
{
string p_name = "CommitCRM";
string installedLocation = null;
RegistryKey key = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall");
RepairShoprUtils.LogWriteLineinHTML("Reading CommitCRM Install Location from RegistryKey", MessageSource.Initialization, "", messageType.Information);
foreach (var keyName in key.GetSubKeyNames())
{
RegistryKey subkey = key.OpenSubKey(keyName);
var displayName = subkey.GetValue("DisplayName") as string;
if (p_name.Equals(displayName, StringComparison.OrdinalIgnoreCase) == true)
{
installedLocation = subkey.GetValue("InstallLocation") as string;
RepairShoprUtils.LogWriteLineinHTML(string.Format("CommitCRM Install Directiory is '{0}'", installedLocation), MessageSource.Initialization, "", messageType.Information);
break;
}
}
if (string.IsNullOrEmpty(installedLocation))
{
if (MessageBox.Show("CommitCRM location is not found. Would you like to browse it now?", this.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
{
var dialog = new FolderBrowserDialog();
dialog.ShowNewFolderButton = false;
if (dialog.ShowDialog() == DialogResult.OK)
{
installedLocation = dialog.SelectedPath;
}
}
}
Properties.Settings.Default.InstalledLocation = installedLocation;
Properties.Settings.Default.Save();
if (string.IsNullOrEmpty(Properties.Settings.Default.InstalledLocation))
{
RepairShoprUtils.LogWriteLineinHTML("CommitCRM is not configured", MessageSource.Initialization, "", messageType.Information);
return;
}
}
RepairShoprUtils.LogWriteLineinHTML("Setting CommitCRM Parameter such as DLL folder and DB Folder", MessageSource.Initialization, "", messageType.Information);
var config = new CommitCRM.Config();
config.AppName = "RepairShopr";
config.CommitDllFolder = Path.Combine(Properties.Settings.Default.InstalledLocation, "ThirdParty", "UserDev");
config.CommitDbFolder = Path.Combine(Properties.Settings.Default.InstalledLocation, "db");
CommitCRM.Application.Initialize(config);
RepairShoprUtils.LogWriteLineinHTML("Successfully configure CommitCRM", MessageSource.Initialization, "", messageType.Information);
}
catch (CommitCRM.Exception exc)
{
RepairShoprUtils.LogWriteLineinHTML("Failed to Configure CommitCRM", MessageSource.Initialization, exc.Message, messageType.Error);
}
}
private void backgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
try
{
var connectionString = "data source=" + _path + ";PRAGMA journal_mode=WAL;Password=shyam;";
DateTime customerExport;
DateTime ticketExport;
ticketExport = customerExport = GetCreationDate();
#region Customer Export
if (_exportCustomer)
{
var exporter = new CustomerExporter(connectionString, () => ((BackgroundWorker)sender).CancellationPending);
exporter.ReportStatusEvent += (message) =>
{
_statusMessage = message;
};
exporter.ReportProgressEvent += (index, percentage, cancelled) =>
{
((BackgroundWorker)sender).ReportProgress((int)percentage, index);
if (cancelled)
{
RepairShoprUtils.LogWriteLineinHTML("Contact Exporting Process is Stoped or Cancelled by User", MessageSource.Customer, "", messageType.Warning);
}
};
exporter.ReportCustomerErrorEvent += (customer, message, exception) =>
{
RepairShoprUtils.LogWriteLineinHTML(message, MessageSource.Customer, exception?.ToString(), messageType.Error);
};
var task = exporter.Export(customerExport, _remoteHost);
task.Wait();
}
#endregion
#region Customer Export
if (_exportTicket)
{
if (Properties.Settings.Default.TicketExport != null && Properties.Settings.Default.TicketExport > ticketExport)
{
ticketExport = Properties.Settings.Default.TicketExport;
}
var exporter = new TicketExporter(_defaultLocationId, connectionString, () => ((BackgroundWorker)sender).CancellationPending);
exporter.ReportStatusEvent += (message) =>
{
_statusMessage = message;
};
exporter.ReportProgressEvent += (index, percentage, cancelled) =>
{
((BackgroundWorker)sender).ReportProgress((int)percentage, index);
if (cancelled)
{
RepairShoprUtils.LogWriteLineinHTML("Contact Exporting Process is Stoped or Cancelled by User", MessageSource.Customer, "", messageType.Warning);
}
};
exporter.ReportTicketErrorEvent += (customer, message, exception) =>
{
RepairShoprUtils.LogWriteLineinHTML(message, MessageSource.Customer, exception?.ToString(), messageType.Error);
};
var task = exporter.Export(ticketExport, _remoteHost);
task.Wait();
}
#endregion
}
catch (CommitCRM.Exception exc)
{
RepairShoprUtils.LogWriteLineinHTML("Failed to Export Data", MessageSource.Initialization, exc.Message, messageType.Error);
}
}
protected async Task <Customer> ExportSingleCustomer(CommitCRM.Account account, SQLiteConnection connection, string remoteHost)
{
return(await Task.Factory.StartNew(() =>
{
var myNameValueCollection = new NameValueCollection();
string fullname = account.GetFieldValue("FLDCRDCONTACT");
myNameValueCollection.Add("business_name", account.CompanyName);
if (!string.IsNullOrEmpty(fullname) && !string.IsNullOrEmpty(account.LastName))
{
myNameValueCollection.Add("firstname", fullname.Replace(account.LastName, string.Empty));
}
else
{
myNameValueCollection.Add("firstname", fullname);
}
myNameValueCollection.Add("lastname", account.LastName);
myNameValueCollection.Add("email", account.EmailAddress1);
myNameValueCollection.Add("phone", Regex.Replace(account.Phone1, @"[^.0-9\s]", ""));
myNameValueCollection.Add("mobile", Regex.Replace(account.Phone2, @"[^.0-9\s]", ""));
myNameValueCollection.Add("address", account.AddressLine1);
myNameValueCollection.Add("address_2", account.AddressLine2);
myNameValueCollection.Add("city", account.City);
myNameValueCollection.Add("state", account.State);
myNameValueCollection.Add("zip", account.Zip);
myNameValueCollection.Add("notes", account.Notes);
var newCustomer = RepairShoprUtils.ExportCustomer(myNameValueCollection, remoteHost);
if (newCustomer != null)
{
using (SQLiteCommand cmdINewItem = new SQLiteCommand(string.Format("INSERT INTO Account (AccountId,CustomerId) VALUES('{0}','{1}')", account.AccountREC_ID, newCustomer.Id), connection))
cmdINewItem.ExecuteNonQuery();
var contacts = new CommitCRM.ObjectQuery <CommitCRM.Contact>();
contacts.AddCriteria(CommitCRM.Contact.Fields.ParentAccountREC_ID, CommitCRM.OperatorEnum.opEqual, account.AccountREC_ID);
contacts.AddCriteria(CommitCRM.Contact.Fields.AccountType, CommitCRM.OperatorEnum.opEqual, 5);
var contactsResult = contacts.FetchObjects();
foreach (CommitCRM.Contact contact in contactsResult)
{
string contactname = contact.GetFieldValue("FLDCRDCONTACT");
NameValueCollection contactNameCollection = new NameValueCollection();
if (account.EmailAddress1.Contains("@"))
{
myNameValueCollection.Add("email", contact.EmailAddress1);
}
contactNameCollection.Add("phone", contact.Phone1);
contactNameCollection.Add("mobile", contact.Phone2);
contactNameCollection.Add("address", contact.AddressLine1);
contactNameCollection.Add("address_2", contact.AddressLine2);
contactNameCollection.Add("city", contact.City);
contactNameCollection.Add("state", contact.State);
contactNameCollection.Add("zip", contact.Zip);
contactNameCollection.Add("customer_id", newCustomer.Id);
contactNameCollection.Add("name", contactname);
RepairShoprUtils.ExportContact(contactNameCollection, remoteHost);
}
}
return newCustomer;
}));
}
