public static List<Object> CloneContactList(List<Object> list)
{
List<Object> listContact = new List<Object>();
if (list != null && list.Count() > 0)
{
foreach (Object contact in list)
{
if (contact is ContactDB)
{
ContactDB cloneContact = new ContactDB();
cloneContact.Clone((ContactDB)contact);
listContact.Add(cloneContact);
}
else if (contact is ContactPrinter)
{
ContactPrinter cloneContact = new ContactPrinter();
cloneContact.Clone((ContactPrinter)contact);
listContact.Add(cloneContact);
}
}
}
return listContact;
}
public ContactDB(ContactDB ContactDb)
{
var listEmailDB = new List<EmailDB>();
foreach (var email in ContactDb.listEmail)
{
listEmailDB.Add(email);
}
var listFax = new List<FaxDB>();
foreach (var fax in ContactDb.listFax)
{
listFax.Add(fax);
}
this.id = ContactDb.id;
this.accountId = ContactDb.accountId;
this.accountName = ContactDb.accountName;
this.accountTypeName = ContactDb.accountTypeName;
this.contactAction = ContactDb.contactAction;
this.firstName = ContactDb.firstName;
this.groupId = ContactDb.groupId;
this.groupName = ContactDb.groupName;
this.lastName = ContactDb.lastName;
this.listEmail = listEmailDB;
this.listFax = listFax;
this.local_contact_id = ContactDb.local_contact_id;
this.middleName = ContactDb.middleName;
this.name = ContactDb.name;
this.nameOrder = ContactDb.nameOrder;
}
public Window_AddEditContact(Object parent, Object contact, List<string> listGroup) // for printer
{
InitializeComponent();
currentMode = MODE_PRINTER;
this.parent = parent;
listGroupName = listGroup;
if (contact == null)
{
this.Title = CMLibrary.Properties.Resources.add_contact;
}
else
{
this.Title = CMLibrary.Properties.Resources.edit_contact;
if (contact is ContactDB)
{
contactDB = (ContactDB)contact;
}
else if (contact is ContactPrinter)
{
contactPrinter = (ContactPrinter)contact;
}
}
}
private void AddOrUpdateContactsToDatabase()
{
if (isUserCancel)
{
return;
}
List<ContactDB> list = new List<ContactDB>();
int count = listContact.Count;
for (int i = 0; i < count; i++)
{
if (!(listContact[i + 1] is ContactItem))
{
continue;
}
ContactItem oContact = (ContactItem)listContact[i + 1];
ContactDB contact = new ContactDB();
contact.AccountId = this.account.Id;
contact.AccountTypeName = AccountTypeDB.TYPE_OUTLOOK_STRING;
contact.Id = -1;
contact.FirstName = oContact.FirstName;
contact.MiddleName = oContact.MiddleName;
contact.LastName = oContact.LastName;
// add list email
List<EmailDB> listEmail = new List<EmailDB>();
// email 1
string email = oContact.Email1Address;
if (!String.IsNullOrEmpty(email))
{
EmailDB emailDB = new EmailDB();
emailDB.Value = email;
EmailTypeDB emailType = new EmailTypeDB();
emailType.Name = EmailTypeDB.OUTLOOK_EMAIL_TYPE_EMAIL_1_STRING;
emailType.AccountTypeName = AccountTypeDB.TYPE_OUTLOOK_STRING;
emailDB.AccountTypeName = AccountTypeDB.TYPE_OUTLOOK_STRING;
emailDB.EmailType = emailType;
listEmail.Add(emailDB);
}
// email 2
email = oContact.Email2Address;
if (!String.IsNullOrEmpty(email))
{
EmailDB emailDB = new EmailDB();
emailDB.Value = email;
EmailTypeDB emailType = new EmailTypeDB();
emailType.Name = EmailTypeDB.OUTLOOK_EMAIL_TYPE_EMAIL_2_STRING;
emailType.AccountTypeName = AccountTypeDB.TYPE_OUTLOOK_STRING;
emailDB.AccountTypeName = AccountTypeDB.TYPE_OUTLOOK_STRING;
emailDB.EmailType = emailType;
listEmail.Add(emailDB);
}
// email 3
email = oContact.Email3Address;
if (!String.IsNullOrEmpty(email))
{
EmailDB emailDB = new EmailDB();
emailDB.Value = email;
EmailTypeDB emailType = new EmailTypeDB();
emailType.Name = EmailTypeDB.OUTLOOK_EMAIL_TYPE_EMAIL_3_STRING;
emailType.AccountTypeName = AccountTypeDB.TYPE_OUTLOOK_STRING;
emailDB.AccountTypeName = AccountTypeDB.TYPE_OUTLOOK_STRING;
emailDB.EmailType = emailType;
listEmail.Add(emailDB);
}
contact.ListEmail = listEmail;
// add list fax
List<FaxDB> listFax = new List<FaxDB>();
// home fax
string fax = oContact.HomeFaxNumber;
if (!String.IsNullOrEmpty(fax))
{
FaxDB faxDB = new FaxDB();
faxDB.Value = fax;
FaxTypeDB faxType = new FaxTypeDB();
faxType.Name = FaxTypeDB.OUTLOOK_FAX_TYPE_HOME_STRING;
faxType.AccountTypeName = AccountTypeDB.TYPE_OUTLOOK_STRING;
faxDB.AccountTypeName = AccountTypeDB.TYPE_OUTLOOK_STRING;
faxDB.FaxType = faxType;
listFax.Add(faxDB);
}
// work fax
fax = oContact.BusinessFaxNumber;
if (!String.IsNullOrEmpty(fax))
{
FaxDB faxDB = new FaxDB();
faxDB.Value = fax;
FaxTypeDB faxType = new FaxTypeDB();
faxType.Name = FaxTypeDB.OUTLOOK_FAX_TYPE_WORK_STRING;
faxType.AccountTypeName = AccountTypeDB.TYPE_OUTLOOK_STRING;
faxDB.AccountTypeName = AccountTypeDB.TYPE_OUTLOOK_STRING;
faxDB.FaxType = faxType;
listFax.Add(faxDB);
}
// other fax
fax = oContact.OtherFaxNumber;
if (!String.IsNullOrEmpty(fax))
{
FaxDB faxDB = new FaxDB();
faxDB.Value = fax;
FaxTypeDB faxType = new FaxTypeDB();
faxType.Name = FaxTypeDB.OUTLOOK_FAX_TYPE_OTHER_STRING;
faxType.AccountTypeName = AccountTypeDB.TYPE_OUTLOOK_STRING;
faxDB.AccountTypeName = AccountTypeDB.TYPE_OUTLOOK_STRING;
faxDB.FaxType = faxType;
listFax.Add(faxDB);
}
contact.ListFax = listFax;
list.Add(contact);
}
CheckMaximumAndImport(list);
}
private void AddOrUpdateContactsToDatabase()
{
if(isUserCancel)
{
return;
}
List<ContactDB> list = new List<ContactDB>();
foreach (Item item in listContact)
{
Contact eContact = item as Contact;
if (eContact != null)
{
ContactDB contact = new ContactDB();
contact.AccountId = this.account.Id;
contact.AccountTypeName = AccountTypeDB.TYPE_EXCHANGE_SERVER_STRING;
contact.Id = -1;
contact.FirstName = eContact.GivenName;
contact.MiddleName = eContact.MiddleName;
contact.LastName = eContact.Surname;
// add list email
List<EmailDB> listEmail = new List<EmailDB>();
EmailAddressDictionary emailCollection = eContact.EmailAddresses;
// email 1
EmailAddress emailAddr;
emailCollection.TryGetValue(EmailAddressKey.EmailAddress1, out emailAddr);
string email = emailAddr != null ? emailAddr.Address : null;
if (!String.IsNullOrEmpty(email))
{
EmailDB emailDB = new EmailDB();
emailDB.Value = email;
EmailTypeDB emailType = new EmailTypeDB();
emailType.Name = EmailTypeDB.EXCHANGE_EMAIL_TYPE_EMAIL_1_STRING;
emailType.AccountTypeName = AccountTypeDB.TYPE_EXCHANGE_SERVER_STRING;
emailDB.AccountTypeName = AccountTypeDB.TYPE_EXCHANGE_SERVER_STRING;
emailDB.EmailType = emailType;
listEmail.Add(emailDB);
}
// email 2
emailCollection.TryGetValue(EmailAddressKey.EmailAddress2, out emailAddr);
email = emailAddr != null ? emailAddr.Address : null;
if (!String.IsNullOrEmpty(email))
{
EmailDB emailDB = new EmailDB();
emailDB.Value = email;
EmailTypeDB emailType = new EmailTypeDB();
emailType.Name = EmailTypeDB.EXCHANGE_EMAIL_TYPE_EMAIL_2_STRING;
emailType.AccountTypeName = AccountTypeDB.TYPE_EXCHANGE_SERVER_STRING;
emailDB.AccountTypeName = AccountTypeDB.TYPE_EXCHANGE_SERVER_STRING;
emailDB.EmailType = emailType;
listEmail.Add(emailDB);
}
// email 3
emailCollection.TryGetValue(EmailAddressKey.EmailAddress3, out emailAddr);
email = emailAddr != null ? emailAddr.Address : null;
if (!String.IsNullOrEmpty(email))
{
EmailDB emailDB = new EmailDB();
emailDB.Value = email;
EmailTypeDB emailType = new EmailTypeDB();
emailType.Name = EmailTypeDB.EXCHANGE_EMAIL_TYPE_EMAIL_3_STRING;
emailType.AccountTypeName = AccountTypeDB.TYPE_EXCHANGE_SERVER_STRING;
emailDB.AccountTypeName = AccountTypeDB.TYPE_EXCHANGE_SERVER_STRING;
emailDB.EmailType = emailType;
listEmail.Add(emailDB);
}
contact.ListEmail = listEmail;
// add list fax
List<FaxDB> listFax = new List<FaxDB>();
PhoneNumberDictionary phoneDictionary = eContact.PhoneNumbers;
// home fax
string fax;
phoneDictionary.TryGetValue(PhoneNumberKey.HomeFax, out fax);
if (!String.IsNullOrEmpty(fax))
{
FaxDB faxDB = new FaxDB();
faxDB.Value = fax;
FaxTypeDB faxType = new FaxTypeDB();
faxType.Name = FaxTypeDB.EXCHANGE_FAX_TYPE_HOME_STRING;
faxType.AccountTypeName = AccountTypeDB.TYPE_EXCHANGE_SERVER_STRING;
faxDB.AccountTypeName = AccountTypeDB.TYPE_EXCHANGE_SERVER_STRING;
faxDB.FaxType = faxType;
listFax.Add(faxDB);
}
// work fax
phoneDictionary.TryGetValue(PhoneNumberKey.BusinessFax, out fax);
if (!String.IsNullOrEmpty(fax))
{
FaxDB faxDB = new FaxDB();
faxDB.Value = fax;
FaxTypeDB faxType = new FaxTypeDB();
faxType.Name = FaxTypeDB.EXCHANGE_FAX_TYPE_WORK_STRING;
faxType.AccountTypeName = AccountTypeDB.TYPE_EXCHANGE_SERVER_STRING;
faxDB.AccountTypeName = AccountTypeDB.TYPE_EXCHANGE_SERVER_STRING;
faxDB.FaxType = faxType;
listFax.Add(faxDB);
}
// other fax
phoneDictionary.TryGetValue(PhoneNumberKey.OtherFax, out fax);
if (!String.IsNullOrEmpty(fax))
{
FaxDB faxDB = new FaxDB();
faxDB.Value = fax;
FaxTypeDB faxType = new FaxTypeDB();
faxType.Name = FaxTypeDB.EXCHANGE_FAX_TYPE_OTHER_STRING;
faxType.AccountTypeName = AccountTypeDB.TYPE_EXCHANGE_SERVER_STRING;
faxDB.AccountTypeName = AccountTypeDB.TYPE_EXCHANGE_SERVER_STRING;
faxDB.FaxType = faxType;
listFax.Add(faxDB);
}
contact.ListFax = listFax;
list.Add(contact);
}
}
CheckMaximumAndImport(list);
}
public List<int> GetContactInGroupOption(ContactDB contact, List<int> listCurrentContactInGroup)
{
List<int> listContactDuplicateInGroup = new List<int>();
if (this.OpenConnection() == true)
{
string tempStr = "";
foreach (var Id in listCurrentContactInGroup)
{
tempStr += Id + ",";
}
string contact_id = tempStr.TrimEnd(',');
try
{
string query =
"SELECT contact.id as id FROM " +
"contact " +
"left join (email join email_type on email_type_id = email_type.id and email_type.preferred = 1) on contact.id = email.contact_id " +
"left join (fax join fax_type on fax_type_id = fax_type.id and fax_type.preferred = 1) on contact.id = fax.contact_id " +
"where " +
"(" +
"email_type.preferred = 1 or email_type.preferred is null " +
"and fax_type.preferred = 1 or fax_type.preferred is null " +
") " +
"And " +
"contact.id in (" + contact_id + ") and " +
"IFNULL(contact.first_name, '') = @firstname and " +
"IFNULL(contact.middle_name, '') = @middlename and " +
"IFNULL(contact.last_name, '') = @lastname and " +
"IFNULL(email.value, '') = @email and " +
"IFNULL(fax.value, '') = @fax";
//Create Command
SQLiteCommand cmd = new SQLiteCommand(query, connection);
cmd.Parameters.AddWithValue("@firstname", contact.FirstName);
cmd.Parameters.AddWithValue("@middlename", contact.MiddleName);
cmd.Parameters.AddWithValue("@lastname", contact.LastName);
cmd.Parameters.AddWithValue("@email", contact.ListEmail.Count() == 0 ? "" : contact.ListEmail.First().Value);
cmd.Parameters.AddWithValue("@fax", contact.ListFax.Count() == 0 ? "" : contact.ListFax.First().Value);
//Create a data reader and Execute the command
SQLiteDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
listContactDuplicateInGroup.Add(Int32.Parse(dataReader["id"].ToString()));
}
//close Data Reader
dataReader.Close();
}
catch (Exception ex)
{
}
//close connection
this.CloseConnection();
}
return listContactDuplicateInGroup;
}
public int GetContactIdDuplicatedByContactObj(ContactDB contact) // tttt
{
List<int> listDuplicatedContactId = new List<int>();
if (this.OpenConnection() == true)
{
try
{
string firstName = contact.FirstName;
string middleName = contact.MiddleName;
string lastName = contact.LastName;
int accountId = contact.AccountId;
List<EmailDB> listEmail = contact.ListEmail;
List<FaxDB> listFax = contact.ListFax;
if (String.IsNullOrEmpty(firstName + middleName + lastName) &&
listEmail.Count() == 0 && listFax.Count() == 0)
{
return 0;
}
// duplicate by Name and main Email
string mainEmail = "";
string mainFax = "";
if (listEmail.Count() == 1)
{
mainEmail = listEmail.First().Value;
}
else
{
foreach (EmailDB email_db in listEmail)
{
if (email_db.EmailType.IsPreferred)
{
mainEmail = email_db.Value;
break;
}
}
}
if (listFax.Count() == 1)
{
mainFax = listFax.First().Value;
}
else
{
foreach (FaxDB fax_db in listFax)
{
if (fax_db.FaxType.IsPreferred)
{
mainFax = fax_db.Value;
break;
}
}
}
string query =
"SELECT contact.id as id FROM " +
"contact " +
"left join (email join email_type on email_type_id = email_type.id and email_type.preferred = 1) on contact.id = email.contact_id " +
"left join (fax join fax_type on fax_type_id = fax_type.id and fax_type.preferred = 1) on contact.id = fax.contact_id " +
"where " +
"(" +
"email_type.preferred = 1 or email_type.preferred is null " +
"and fax_type.preferred = 1 or fax_type.preferred is null " +
") " +
"And " +
"contact.id not in (@contact_id) and " +
"IFNULL(contact.first_name, '') = @firstname and " +
"IFNULL(contact.middle_name, '') = @middlename and " +
"IFNULL(contact.last_name, '') = @lastname and " +
"IFNULL(email.value, '') = @email and " +
"IFNULL(fax.value, '') = @fax";
//Create Command
SQLiteCommand cmd = new SQLiteCommand(query, connection);
cmd.Parameters.AddWithValue("@contact_id", contact.Id);
cmd.Parameters.AddWithValue("@firstname", firstName);
cmd.Parameters.AddWithValue("@middlename", middleName);
cmd.Parameters.AddWithValue("@lastname", lastName);
cmd.Parameters.AddWithValue("@email", mainEmail);
cmd.Parameters.AddWithValue("@fax", mainFax);
//Create a data reader and Execute the command
SQLiteDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
listDuplicatedContactId.Add(Int32.Parse(dataReader["id"].ToString()));
}
//close Data Reader
dataReader.Close();
}
catch (Exception ex)
{
}
this.CloseConnection();
}
if (listDuplicatedContactId.Any())
{
return contact.Id;
}
else
{
return 0;
}
}
public List<ContactDB> GetAllListContact()
{
string query = @"SELECT contact.id, contact.first_name, contact.last_name, contact.middle_name, contact.account_id, account_type.name as account_type_name FROM contact
inner join account on contact.account_id = account.id
inner join account_type on account.account_type_id = account_type.id";
//Create a list to store the result
List<ContactDB> list = new List<ContactDB>();
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
SQLiteCommand cmd = new SQLiteCommand(query, connection);
//Create a data reader and Execute the command
SQLiteDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
List<EmailDB> listEmail = new List<EmailDB>();
List<FaxDB> listFax = new List<FaxDB>();
while (dataReader.Read())
{
int id = Int32.Parse(dataReader["id"].ToString());
string first_name = dataReader["first_name"].ToString();
string middle_name = dataReader["middle_name"].ToString();
string last_name = dataReader["last_name"].ToString();
int account_id = Int32.Parse(dataReader["account_id"].ToString());
string account_type_name = dataReader["account_type_name"].ToString();
ContactDB contact = new ContactDB();
contact.Id = id;
contact.FirstName = first_name;
contact.MiddleName = middle_name;
contact.LastName = last_name;
contact.AccountId = account_id;
contact.AccountTypeName = account_type_name;
list.Add(contact);
}
//close Data Reader
dataReader.Close();
//close Connection
this.CloseConnection();
}
return list;
}
private void AddOrUpdateContactsToDatabase()
{
if (isUserCancel)
{
return;
}
List<ContactDB> list = new List<ContactDB>();
int count = listContact.Count();
LiveContact lContact;
for (int i = 0; i < count; i++)
{
lContact = listContact[i];
ContactDB contact = new ContactDB();
contact.AccountId = this.account.Id;
contact.AccountTypeName = AccountTypeDB.TYPE_LIVE_HOTMAIL_STRING;
contact.Id = -1;
contact.FirstName = lContact.First_name;
contact.LastName = lContact.Last_name;
// add list email
List<EmailDB> listEmail = new List<EmailDB>();
List<TypeAndValue> emails = lContact.Emails;
int size = emails.Count();
TypeAndValue typeAndValue;
for (int j = 0; j < size; j++)
{
typeAndValue = emails[j];
EmailDB emailDB = new EmailDB();
emailDB.Value = typeAndValue.Value;
EmailTypeDB emailType = new EmailTypeDB();
string type = typeAndValue.Type;
if (type.Equals("personal"))
{
emailType.Name = EmailTypeDB.LIVE_EMAIL_TYPE_PERSONAL_STRING;
}
else if (type.Equals("business"))
{
emailType.Name = EmailTypeDB.LIVE_EMAIL_TYPE_BUSINESS_STRING;
}
else
{
emailType.Name = EmailTypeDB.LIVE_EMAIL_TYPE_OTHER_STRING;
}
emailType.AccountTypeName = AccountTypeDB.TYPE_LIVE_HOTMAIL_STRING;
emailDB.AccountTypeName = AccountTypeDB.TYPE_LIVE_HOTMAIL_STRING;
emailDB.EmailType = emailType;
listEmail.Add(emailDB);
}
contact.ListEmail = listEmail;
// add list fax
List<FaxDB> listFax = new List<FaxDB>();
List<TypeAndValue> phones = lContact.Phones;
size = phones.Count();
for (int j = 0; j < size; j++)
{
typeAndValue = phones[j];
string type = typeAndValue.Type;
if (type.EndsWith("fax"))
{
FaxDB faxDB = new FaxDB();
faxDB.Value = typeAndValue.Value;
FaxTypeDB faxType = new FaxTypeDB();
if (type.Equals("home_fax"))
{
faxType.Name = FaxTypeDB.LIVE_FAX_TYPE_HOME_STRING;
}
else if (type.Equals("work_fax"))
{
faxType.Name = FaxTypeDB.LIVE_FAX_TYPE_WORK_STRING;
}
faxType.AccountTypeName = AccountTypeDB.TYPE_LIVE_HOTMAIL_STRING;
faxDB.AccountTypeName = AccountTypeDB.TYPE_LIVE_HOTMAIL_STRING;
faxDB.FaxType = faxType;
listFax.Add(faxDB);
}
}
contact.ListFax = listFax;
list.Add(contact);
}
CheckMaximumAndImport(list);
}
private List<ContactDB> ObjConvertToContactDB(List<Object> listContactObj)
{
List<ContactDB> list = new List<ContactDB>();
foreach (var contactObj in listContactObj)
{
int id = -1;
int local_contact_id = 0;
string firstName = "";
string lastName = "";
string middleName = "";
string groupName = "";
string location = "";
EmailDB email = new EmailDB();
FaxDB fax = new FaxDB();
if (contactObj is ContactDB)
{
ContactDB contactDB = (ContactDB)contactObj;
id = contactDB.Id;
local_contact_id = contactDB.Local_contact_id;
lastName = contactDB.LastName;
firstName = contactDB.FirstName;
middleName = contactDB.MiddleName;
groupName = contactDB.GroupName;
location = contactDB.AccountName;
email.Value = contactDB.ListEmail.Count() > 0 ? contactDB.ListEmail[0].Value : "";
fax.Value = contactDB.ListFax.Count() > 0 ? contactDB.ListFax[0].Value : "";
}
else if (contactObj is ContactPrinter)
{
ContactPrinter contactPrinter = ((ContactPrinter)contactObj);
//string[] names = ContactDB.GetFirstMidLastFromFullName(contactPrinter.Name, contactPrinter.NameOrder);
//id = contactPrinter.Id;
//firstName = names[0];
//middleName = names[1];
//lastName = names[2];
lastName = contactPrinter.LastName;
firstName = contactPrinter.FirstName;
middleName = contactPrinter.MiddleName;
groupName = contactPrinter.GroupName;
email.Value = contactPrinter.Email;
fax.Value = contactPrinter.Fax;
}
ContactDB contact = new ContactDB();
contact.Id = id;
contact.Local_contact_id = local_contact_id;
contact.LastName = lastName;
contact.FirstName = firstName;
contact.MiddleName = middleName;
contact.ListEmail.Add(email);
contact.ListFax.Add(fax);
list.Add(contact);
}
return list;
}
private void AddOrUpdateContactsToDatabase()
{
if(isUserCancel)
{
return;
}
List<ContactDB> list = new List<ContactDB>();
int count = listContact.Count;
ICloudContact iContact;
for (int i = 0; i < count; i++)
{
iContact = listContact[i];
ContactDB contact = new ContactDB();
contact.AccountId = this.account.Id;
contact.AccountTypeName = AccountTypeDB.TYPE_ICLOUD_STRING;
contact.Id = -1;
contact.FirstName = iContact.FirstName;
contact.MiddleName = iContact.MiddleName;
contact.LastName = iContact.LastName;
// add list email
List<EmailDB> listEmail = new List<EmailDB>();
List<TypeAndValue> emails = iContact.EmailAddresses;
int size = emails.Count();
TypeAndValue typeAndValue;
for (int j = 0; j < size; j++)
{
typeAndValue = emails[j];
EmailDB emailDB = new EmailDB();
emailDB.Value = typeAndValue.Value;
EmailTypeDB emailType = new EmailTypeDB();
string type = typeAndValue.Type;
if (type.Equals("HOME"))
{
emailType.Name = EmailTypeDB.ICLOUD_EMAIL_TYPE_HOME_STRING;
}
else if (type.Equals("WORK"))
{
emailType.Name = EmailTypeDB.ICLOUD_EMAIL_TYPE_WORK_STRING;
}
else
{
emailType.Name = EmailTypeDB.ICLOUD_EMAIL_TYPE_OTHER_STRING;
}
emailType.AccountTypeName = AccountTypeDB.TYPE_ICLOUD_STRING;
emailDB.AccountTypeName = AccountTypeDB.TYPE_ICLOUD_STRING;
emailDB.EmailType = emailType;
listEmail.Add(emailDB);
}
contact.ListEmail = listEmail;
// add list fax
List<FaxDB> listFax = new List<FaxDB>();
List<TypeAndValue> phones = iContact.Phones;
size = phones.Count();
for (int j = 0; j < size; j++)
{
typeAndValue = phones[j];
string type = typeAndValue.Type;
if (type.EndsWith("FAX"))
{
FaxDB faxDB = new FaxDB();
faxDB.Value = typeAndValue.Value;
FaxTypeDB faxType = new FaxTypeDB();
if (type.Equals("HOME FAX"))
{
faxType.Name = FaxTypeDB.ICLOUD_FAX_TYPE_HOME_STRING;
}
else if (type.Equals("WORK FAX"))
{
faxType.Name = FaxTypeDB.ICLOUD_FAX_TYPE_WORK_STRING;
}
else
{
faxType.Name = FaxTypeDB.ICLOUD_FAX_TYPE_OTHER_STRING;
}
faxType.AccountTypeName = AccountTypeDB.TYPE_ICLOUD_STRING;
faxDB.AccountTypeName = AccountTypeDB.TYPE_ICLOUD_STRING;
faxDB.FaxType = faxType;
listFax.Add(faxDB);
}
}
contact.ListFax = listFax;
list.Add(contact);
}
CheckMaximumAndImport(list);
}
private void AddOrUpdateContactsToDatabase()
{
if (isUserCancel)
{
return;
}
List<ContactDB> list = new List<ContactDB>();
foreach (Contact gContact in listContact.Entries)
{
ContactDB contact = new ContactDB();
contact.AccountId = this.account.Id;
contact.AccountTypeName = AccountTypeDB.TYPE_GMAIL_STRING;
string title = gContact.Title;
string[] names = ContactDB.GetFirstMidLastFromFullName(title, ContactDB.NAME_ORDER_FIRST_MID_LAST);
contact.Id = -1;
contact.FirstName = names[0];
contact.MiddleName = names[1];
contact.LastName = names[2];
// add list email
List<EmailDB> listEmail = new List<EmailDB>();
foreach (EMail email in gContact.Emails)
{
EmailDB emailDB = new EmailDB();
emailDB.Value = email.Address;
EmailTypeDB emailType = new EmailTypeDB();
if (email.Home)
{
emailType.Name = EmailTypeDB.GMAIL_EMAIL_TYPE_HOME_STRING;
}
else if (email.Work)
{
emailType.Name = EmailTypeDB.GMAIL_EMAIL_TYPE_WORK_STRING;
}
else
{
emailType.Name = EmailTypeDB.GMAIL_EMAIL_TYPE_OTHER_STRING;
}
emailType.AccountTypeName = AccountTypeDB.TYPE_GMAIL_STRING;
emailDB.AccountTypeName = AccountTypeDB.TYPE_GMAIL_STRING;
emailDB.EmailType = emailType;
listEmail.Add(emailDB);
}
contact.ListEmail = listEmail;
// add list fax
List<FaxDB> listFax = new List<FaxDB>();
foreach (PhoneNumber phone in gContact.Phonenumbers)
{
Object relObj = phone.Attributes["rel"];
string type = relObj != null ? relObj.ToString() : "";
if (type.EndsWith("fax"))
{
FaxDB faxDB = new FaxDB();
faxDB.Value = phone.Value;
FaxTypeDB faxType = new FaxTypeDB();
if (type.EndsWith("home_fax"))
{
faxType.Name = FaxTypeDB.GMAIL_FAX_TYPE_HOME_STRING;
}
else if (type.EndsWith("work_fax"))
{
faxType.Name = FaxTypeDB.GMAIL_FAX_TYPE_WORK_STRING;
}
faxType.AccountTypeName = AccountTypeDB.TYPE_GMAIL_STRING;
faxDB.AccountTypeName = AccountTypeDB.TYPE_GMAIL_STRING;
faxDB.FaxType = faxType;
listFax.Add(faxDB);
}
}
contact.ListFax = listFax;
list.Add(contact);
}
CheckMaximumAndImport(list);
}
// Contact
public int SelfAddContact(string firstName, string middleName, string lastName, string email, string fax)
{
DBManager db = new DBManager();
AccountDB selfAccount = db.GetSelfAccount();
ContactDB contact = new ContactDB();
contact.FirstName = firstName;
contact.MiddleName = middleName;
contact.LastName = lastName;
contact.AccountId = selfAccount.Id;
if (!String.IsNullOrEmpty(email))
{
List<EmailDB> listEmail = new List<EmailDB>();
EmailDB emailDB = new EmailDB();
emailDB.Value = email;
List<EmailTypeDB> listEmailType = db.GetAllEmailTypeByAccountTypeName(AccountTypeDB.TYPE_SELF_STRING);
if (listEmailType.Count() > 0)
{
emailDB.EmailType = listEmailType[0];
}
listEmail.Add(emailDB);
contact.ListEmail = listEmail;
}
if (!String.IsNullOrEmpty(fax))
{
List<FaxDB> listFax = new List<FaxDB>();
FaxDB faxDB = new FaxDB();
faxDB.Value = fax;
List<FaxTypeDB> listFaxType = db.GetAllFaxTypeByAccountTypeName(AccountTypeDB.TYPE_SELF_STRING);
if (listFaxType.Count() > 0)
{
faxDB.FaxType = listFaxType[0];
}
listFax.Add(faxDB);
contact.ListFax = listFax;
}
List<ContactDB> list = new List<ContactDB>();
list.Add(contact);
return db.AddOrUpdateContacts(list);
}
public List<int> CheckDuplicate(ContactDB contactDB, List<int> currentContact)
{
DBManager db = new DBManager();
return db.GetContactInGroupOption(contactDB, currentContact);
}
// Contact
public List<int> SelfAddContact(string firstName, string middleName, string lastName, string email, string fax)
{
DBManager db = new DBManager();
AccountDB selfAccount = db.GetSelfAccount();
ContactDB contact = new ContactDB();
contact.FirstName = firstName;
contact.MiddleName = middleName;
contact.LastName = lastName;
contact.AccountId = selfAccount.Id;
List<Dictionary<string, int>> positionEmailFax = new List<Dictionary<string, int>>();
if (!String.IsNullOrEmpty(email))
{
Dictionary<string, int> dictionaryEmail = new Dictionary<string, int>();
List<EmailDB> listEmail = new List<EmailDB>();
EmailDB emailDB = new EmailDB();
emailDB.Value = email;
List<EmailTypeDB> listEmailType = db.GetAllEmailTypeByAccountTypeName(AccountTypeDB.TYPE_SELF_STRING);
if (listEmailType.Any())
{
for (int i = 0; i < listEmailType.Count; i++)
{
if (listEmailType[i].IsPreferred)
{
emailDB.EmailType = listEmailType[i];
dictionaryEmail.Add("Email", i);
}
}
}
listEmail.Add(emailDB);
contact.ListEmail = listEmail;
positionEmailFax.Add(dictionaryEmail);
}
if (!String.IsNullOrEmpty(fax))
{
Dictionary<string, int> dictionaryFax = new Dictionary<string, int>();
List<FaxDB> listFax = new List<FaxDB>();
FaxDB faxDB = new FaxDB();
faxDB.Value = fax;
List<FaxTypeDB> listFaxType = db.GetAllFaxTypeByAccountTypeName(AccountTypeDB.TYPE_SELF_STRING);
if (listFaxType.Any())
{
for (int i = 0; i < listFaxType.Count; i++)
{
if (listFaxType[i].IsPreferred)
{
faxDB.FaxType = listFaxType[i];
dictionaryFax.Add("Fax", i);
}
}
}
listFax.Add(faxDB);
contact.ListFax = listFax;
positionEmailFax.Add(dictionaryFax);
}
List<ContactDB> list = new List<ContactDB>();
list.Add(contact);
return db.AddOrUpdateContacts(list);
}
public int AddContact(ContactDB contact)
{
string contactName = contact.Name;
int accountId = contact.AccountId;
List<EmailDB> listEmail = contact.ListEmail;
List<FaxDB> listFax = contact.ListFax;
// insert new contact
string firstName = contact.FirstName;
string middleName = contact.MiddleName;
string lastName = contact.LastName;
int contactId = -1;
string insSql = "INSERT INTO contact (first_name, middle_name, last_name, account_id) " +
"VALUES(?, ?, ?, ?)";
connection.Execute(insSql, firstName, middleName, lastName, accountId);
OneValueObject obj = connection.Query<OneValueObject>("SELECT last_insert_rowid() as IntValue").First();
contactId = obj != null ? obj.IntValue : -1;
// save list email
int count1 = listEmail.Count();
EmailDB emailDB;
for (int j = 0; j < count1; j++)
{
emailDB = listEmail[j];
insSql = "INSERT INTO email (contact_id, value, email_type_id)" +
" VALUES(?, ?, ?)";
connection.Execute(insSql, contactId, emailDB.Value, emailDB.EmailType.Id);
}
// save list fax
count1 = listFax.Count();
FaxDB faxDB;
for (int j = 0; j < count1; j++)
{
faxDB = listFax[j];
insSql = "INSERT INTO fax (contact_id, value, fax_type_id)" +
" VALUES(?, ?, ?)";
connection.Execute(insSql, contactId, faxDB.Value, faxDB.FaxType.Id);
}
return contactId;
}
// Contact
public ContactDB GetContactById(int id)
{
string query =
"SELECT contact.id as Id, contact.first_name as FirstName, contact.middle_name as MiddleName, contact.last_name as LastName, " +
"pref_email.value as PreferredEmail, pref_email.id as PreferredEmailId, pref_fax.value as PreferredFax, pref_fax.id as PreferredFaxId, account.username as AccountName, " +
"account.id as AccountId, account_type.name as AccountTypeName, " +
"pref_email.preferred as email_preferred, pref_fax.preferred as fax_preferred, " +
"GROUP_CONCAT('group'.name , ';') as GroupName, " +
"GROUP_CONCAT('group'.id , ';') as GroupId, " +
"contact_name_order.name_order as NameOrder " +
"FROM " +
"contact " +
"join account on contact.account_id = account.id " +
"join account_type on account_type.id = account.account_type_id " +
"join host_contact_name_order on host_contact_name_order.id = account_type.id " +
"join contact_name_order on contact_name_order.id = host_contact_name_order.contact_name_order_id " +
"left join ('group' join group_contact on 'group'.id = group_contact.group_id) on contact.id = group_contact.contact_id " +
"left join (select min(email.id) as id, contact_id, email.value, email_type_id, email_type.account_type_id, name, preferred from email, email_type where email_type_id = email_type.id and email_type.preferred = 1 group by contact_id) as pref_email on contact.id = pref_email.contact_id " +
"left join (select min(fax.id) as id, contact_id, fax.value, fax_type_id, fax_type.account_type_id, name, preferred from fax, fax_type where fax_type_id = fax_type.id and fax_type.preferred = 1 group by contact_id) as pref_fax on contact.id = pref_fax.contact_id " +
"where " +
"(( " +
"pref_email.preferred = 1 and pref_fax.preferred = 1 " +
") " +
"or " +
"( " +
"pref_email.preferred is null or pref_fax.preferred is null " +
"))" +
"and " +
"( " +
"contact.id = ? " +
") " +
"group by contact.id";
//Create a list to store the result
ContactDB contact = connection.Query<ContactDB>(query, id).First();
if (contact != null)
{
contact.UpdateListEmailAndFax();
}
else
{
contact = new ContactDB();
}
return contact;
}
public FileImportResult ImportFromCSVFile(string path)
{
FileImportResult result = new FileImportResult();
string last_name = String.Empty;
string first_name = String.Empty;
string middle_name = String.Empty;
string name = String.Empty;
string email = String.Empty;
string fax = String.Empty;
try
{
if (File.Exists(path))
{
string data = "";
try
{
data = File.ReadAllText(path);
}
catch (Exception e)
{
result.errorCode = CODE_FILE_IN_USE;
return result;
}
string[] contactSpits = data.Split(new string[] { "\r", "\r\n", "\n" }, StringSplitOptions.None);
int size = contactSpits.Count();
EmailTypeDB emailType = dbManager.GetPreferredEmailTypeByAccountTypeName(AccountTypeDB.TYPE_CSV_STRING);
FaxTypeDB faxType = dbManager.GetPreferredFaxTypeByAccountTypeName(AccountTypeDB.TYPE_CSV_STRING);
AccountDB account = dbManager.GetCSVAccount();
string accTypeName = AccountTypeDB.TYPE_CSV_STRING;
List<ContactDB> listContact = new List<ContactDB>();
bool isGetFullName = true;
ContactDB contact;
string[] headers = contactSpits[0].Split(',');
for (int i = size - 1; i >= 1; i--)
{
string bareData = contactSpits[i];
var valueSplits = splitCSVLine(bareData);
if (valueSplits.Count() <= 1)
{
continue;
}
for (int j = valueSplits.Count() - 1; j >= 0; j--)
{
var value = MyUtils.RemoveSpaces(valueSplits.ElementAt(j));
switch (MyUtils.RemoveSpaces(headers[j].ToLower()))
{
// name
case COLUMN_FIRST_1: first_name = value; isGetFullName = false; break;
case COLUMN_FIRST_2: first_name = value; isGetFullName = false; break;
case COLUMN_FIRST_3: first_name = value; isGetFullName = false; break;
case COLUMN_LAST_1: last_name = value; isGetFullName = false; break;
case COLUMN_LAST_2: last_name = value; isGetFullName = false; break;
case COLUMN_LAST_3: last_name = value; isGetFullName = false; break;
case COLUMN_MIDDLE_1: middle_name = value; isGetFullName = false; break;
case COLUMN_MIDDLE_2: middle_name = value; isGetFullName = false; break;
case COLUMN_MIDDLE_3: middle_name = value; isGetFullName = false; break;
case COLUMN_NAME_1: name = value; break;
case COLUMN_NAME_2: name = value; break;
case COLUMN_NAME_3: name = value; break;
case COLUMN_NAME_4: name = value; break;
// email
case COLUMN_EMAIL_1: email = value; break;
case COLUMN_EMAIL_2: email = value; break;
case COLUMN_EMAIL_3: email = value; break;
case COLUMN_EMAIL_4: email = value; break;
case COLUMN_EMAIL_5: email = value; break;
case COLUMN_EMAIL_6: email = value; break;
case COLUMN_EMAIL_7: email = value; break;
case COLUMN_EMAIL_8: email = value; break;
case COLUMN_EMAIL_9: email = value; break;
case COLUMN_EMAIL_10: email = value; break;
case COLUMN_EMAIL_11: email = value; break;
case COLUMN_EMAIL_12: email = value; break;
case COLUMN_EMAIL_13: email = value; break;
case COLUMN_EMAIL_14: email = value; break;
// fax
case COLUMN_FAX_1: fax = value; break;
case COLUMN_FAX_2: fax = value; break;
case COLUMN_FAX_3: fax = value; break;
case COLUMN_FAX_4: fax = value; break;
case COLUMN_FAX_5: fax = value; break;
case COLUMN_FAX_6: fax = value; break;
case COLUMN_FAX_7: fax = value; break;
case COLUMN_FAX_8: fax = value; break;
case COLUMN_FAX_9: fax = value; break;
case COLUMN_FAX_10: fax = value; break;
case COLUMN_FAX_11: fax = value; break;
default:
break;
}
}
if (isGetFullName)
{
if (String.IsNullOrEmpty(name))
{
continue;
}
}
else
{
if (String.IsNullOrEmpty(first_name) && String.IsNullOrEmpty(last_name) && String.IsNullOrEmpty(middle_name))
{
continue;
}
}
List<EmailDB> listEmail = new List<EmailDB>();
if (!String.IsNullOrEmpty(email))
{
EmailDB emailDB = new EmailDB();
emailType.AccountTypeName = AccountTypeDB.TYPE_CSV_STRING;
emailDB.AccountTypeName = AccountTypeDB.TYPE_CSV_STRING;
emailDB.Value = email;
emailDB.EmailType = emailType;
listEmail.Add(emailDB);
}
List<FaxDB> listFax = new List<FaxDB>();
if (!String.IsNullOrEmpty(fax))
{
FaxDB faxDB = new FaxDB();
faxType.AccountTypeName = AccountTypeDB.TYPE_CSV_STRING;
faxDB.AccountTypeName = AccountTypeDB.TYPE_CSV_STRING;
faxDB.Value = fax;
faxDB.FaxType = faxType;
listFax.Add(faxDB);
}
contact = new ContactDB();
contact.AccountId = account.Id;
contact.AccountTypeName = accTypeName;
contact.Id = -1;
if (isGetFullName)
{
string nameOrder = dbManager.GetPreferredHostNameOrder("CSV");
string[] names = ContactDB.GetFirstMidLastFromFullName(name, nameOrder);
first_name = names[0];
middle_name = names[1];
last_name = names[2];
}
else
{
contact.FirstName = first_name;
contact.LastName = last_name;
contact.MiddleName = middle_name;
}
contact.FirstName = first_name;
contact.LastName = last_name;
contact.MiddleName = middle_name;
contact.ListEmail = listEmail;
contact.ListFax = listFax;
listContact.Add(contact);
}
return AddOrUpdateContact(listContact);
}
}
catch (Exception)
{
result.errorCode = CODE_FILE_CAN_NOT_IMPORT;
return result;
}
result.errorCode = CODE_FILE_CAN_NOT_IMPORT;
return result;
}
public List<ContactDB> SearchContacts(string search, int from, int limitPerPage)
{
string query =
"SELECT * FROM ( " +
"SELECT contact.id as id, contact.first_name as first_name, contact.middle_name as middle_name, contact.last_name as last_name, " +
"pref_email.value as email, pref_email.id as email_id, pref_fax.value as fax, pref_fax.id as fax_id, account.username as accountname, " +
"account.id as accountid, account_type.name as accounttypename, " +
"pref_email.preferred as email_preferred, pref_fax.preferred as fax_preferred, " +
"GROUP_CONCAT('group'.name , ';') as groupname, " +
"GROUP_CONCAT('group'.id , ';') as groupid, " +
"contact_name_order.name_order as name_order " +
"FROM " +
"contact " +
"join account on contact.account_id = account.id " +
"join account_type on account_type.id = account.account_type_id " +
"join host_contact_name_order on host_contact_name_order.id = account_type.id " +
"join contact_name_order on contact_name_order.id = host_contact_name_order.contact_name_order_id " +
"left join ('group' join group_contact on 'group'.id = group_contact.group_id) on contact.id = group_contact.contact_id " +
"left join (select min(email.id) as id, contact_id, email.value, email_type_id, email_type.account_type_id, name, preferred from email, email_type where email_type_id = email_type.id and email_type.preferred = 1 group by contact_id) as pref_email on contact.id = pref_email.contact_id " +
"left join (select min(fax.id) as id, contact_id, fax.value, fax_type_id, fax_type.account_type_id, name, preferred from fax, fax_type where fax_type_id = fax_type.id and fax_type.preferred = 1 group by contact_id) as pref_fax on contact.id = pref_fax.contact_id " +
"where " +
"(( " +
"pref_email.preferred = 1 and pref_fax.preferred = 1 " +
") " +
"or " +
"( " +
"pref_email.preferred is null or pref_fax.preferred is null " +
")) " +
"group by contact.id " +
"order by contact.id desc " +
") as search_contact " +
"WHERE " +
"TRIM( REPLACE(first_name, '_', '#')||' '|| REPLACE(middle_name, '_', '#')||' '|| REPLACE(last_name, '_', '#')) like @search " +
"or " +
"TRIM( REPLACE(last_name, '_', '#')||' '|| REPLACE(first_name, '_', '#')||' '|| REPLACE(middle_name, '_', '#')) like @search " +
"or " +
"TRIM( REPLACE(first_name, '_', '#')||' '|| REPLACE(last_name, '_', '#')||' '|| REPLACE(middle_name, '_', '#')) like @search " +
"or " +
"TRIM( REPLACE(last_name, '_', '#')||' '|| REPLACE(first_name, '_', '#')||' '|| REPLACE(middle_name, '_', '#')) like @search " +
"or " +
"REPLACE(accountname, '_', '#') like @search " +
"or " +
"REPLACE(email, '_', '#') like @search " +
"or " +
"REPLACE(fax, '_', '#') like @search " +
"or " +
"REPLACE(groupname, '_', '#') like @search ";
if (limitPerPage > 0)
{
query += "limit @limit offset @offset ";
}
//Create a list to store the result
List<ContactDB> list = new List<ContactDB>();
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
SQLiteCommand cmd = new SQLiteCommand(query, connection);
//Create a data reader and Execute the command
cmd.Parameters.AddWithValue("@search", "%" + search + "%");
if (limitPerPage > 0)
{
cmd.Parameters.AddWithValue("@limit", limitPerPage);
cmd.Parameters.AddWithValue("@offset", from);
}
SQLiteDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
int id = Int32.Parse(dataReader["id"].ToString());
string groupid = dataReader["groupid"].ToString();
int accid = Int32.Parse(dataReader["accountid"].ToString());
string first_name = dataReader["first_name"].ToString();
string middle_name = dataReader["middle_name"].ToString();
string last_name = dataReader["last_name"].ToString();
string email = ("" + dataReader["email"]).ToString();
string fax = ("" + dataReader["fax"]).ToString();
string accountname = dataReader["accountname"].ToString();
string accounttypename = dataReader["accounttypename"].ToString();
string groupName = dataReader["groupname"].ToString();
string name_order = dataReader["name_order"].ToString();
ContactDB contact = new ContactDB();
contact.Id = id;
contact.FirstName = first_name;
contact.MiddleName = middle_name;
contact.LastName = last_name;
contact.AccountName = accountname;
contact.AccountId = accid;
contact.GroupId = groupid;
contact.GroupName = groupName;
contact.NameOrder = name_order;
// only preferred email
List<EmailDB> listEmail = new List<EmailDB>();
if (!String.IsNullOrEmpty(email))
{
int emailId = Int32.Parse(dataReader["email_id"].ToString());
EmailDB emailDB = new EmailDB();
emailDB.Id = emailId;
emailDB.Value = email;
listEmail.Add(emailDB);
}
contact.ListEmail = listEmail;
List<FaxDB> listFax = new List<FaxDB>();
if (!String.IsNullOrEmpty(fax))
{
int faxId = Int32.Parse(dataReader["fax_id"].ToString());
FaxDB faxDB = new FaxDB();
faxDB.Id = faxId;
faxDB.Value = fax;
listFax.Add(faxDB);
}
contact.ListFax = listFax;
contact.AccountTypeName = accounttypename;
list.Add(contact);
}
//close Data Reader
dataReader.Close();
//close Connection
this.CloseConnection();
}
return list;
}
public FileImportResult ImportFromVCFFile(string path)
{
FileImportResult result = new FileImportResult();
try
{
File.ReadAllText(path);
}
catch (Exception e)
{
result.errorCode = CODE_FILE_IN_USE;
return result;
}
vCard card = null;
try
{
card = new vCard(path);
}
catch (Exception ex)
{
result.errorCode = CODE_FILE_STRUCTURE_NOT_SUITABLE;
return result;
}
List<EmailDB> listEmail = new List<EmailDB>();
List<FaxDB> listFax = new List<FaxDB>();
EmailTypeDB preferEmailTypeFile = dbManager.GetPreferEmailFromCSVSetting();
// get email
vCardEmailAddressCollection listVEmail = card.EmailAddresses;
if (listVEmail != null && listVEmail.Count() > 0)
{
EmailDB preferEmailDB = new EmailDB();
EmailTypeDB preferEmailType = new EmailTypeDB();
//process Email1 from VCard
foreach (vCardEmailAddress vEmail in listVEmail)
{
if (vEmail.IsPreferred == true && preferEmailTypeFile.Name.Equals("Home"))
{
preferEmailType.Name = EmailTypeDB.VCF_EMAIL_TYPE_HOME_STRING;
preferEmailDB.Value = vEmail.Address;
preferEmailType.AccountTypeName = AccountTypeDB.TYPE_VCF_STRING;
preferEmailDB.AccountTypeName = AccountTypeDB.TYPE_VCF_STRING;
preferEmailDB.EmailType = preferEmailType;
listEmail.Add(preferEmailDB);
listVEmail.Remove(vEmail);
break;
}
else if (vEmail.IsPreferred == true && preferEmailTypeFile.Name.Equals("Work"))
{
preferEmailType.Name = EmailTypeDB.VCF_EMAIL_TYPE_WORK_STRING;
preferEmailDB.Value = vEmail.Address;
preferEmailType.AccountTypeName = AccountTypeDB.TYPE_VCF_STRING;
preferEmailDB.AccountTypeName = AccountTypeDB.TYPE_VCF_STRING;
preferEmailDB.EmailType = preferEmailType;
listEmail.Add(preferEmailDB);
listVEmail.Remove(vEmail);
break;
}
else if (vEmail.IsPreferred == true && preferEmailTypeFile.Name.Equals("Other"))
{
preferEmailType.Name = EmailTypeDB.VCF_EMAIL_TYPE_OTHER_STRING;
preferEmailDB.Value = vEmail.Address;
preferEmailType.AccountTypeName = AccountTypeDB.TYPE_VCF_STRING;
preferEmailDB.AccountTypeName = AccountTypeDB.TYPE_VCF_STRING;
preferEmailDB.EmailType = preferEmailType;
listEmail.Add(preferEmailDB);
listVEmail.Remove(vEmail);
break;
}
}
if (listVEmail.Count() == 1)
{
//process Email2 from VCard
EmailDB email2DB = new EmailDB();
email2DB.Value = listVEmail[0].Address;
email2DB.AccountTypeName = AccountTypeDB.TYPE_VCF_STRING;
email2DB.AccountTypeName = AccountTypeDB.TYPE_VCF_STRING;
EmailTypeDB email2Type = new EmailTypeDB();
switch (preferEmailTypeFile.Name)
{
case "Home":
email2Type.Name = EmailTypeDB.VCF_EMAIL_TYPE_WORK_STRING;
break;
case "Work":
email2Type.Name = EmailTypeDB.VCF_EMAIL_TYPE_HOME_STRING;
break;
case "Other":
email2Type.Name = EmailTypeDB.VCF_EMAIL_TYPE_HOME_STRING;
break;
}
email2DB.EmailType = email2Type;
listEmail.Add(email2DB);
}
else if (listVEmail.Count() == 2)
{
//process Email2 from VCard
EmailDB email2DB = new EmailDB();
email2DB.Value = listVEmail[0].Address;
email2DB.AccountTypeName = AccountTypeDB.TYPE_VCF_STRING;
email2DB.AccountTypeName = AccountTypeDB.TYPE_VCF_STRING;
//process Email3 from VCard
EmailDB email3DB = new EmailDB();
email3DB.Value = listVEmail[1].Address;
email3DB.AccountTypeName = AccountTypeDB.TYPE_VCF_STRING;
email3DB.AccountTypeName = AccountTypeDB.TYPE_VCF_STRING;
EmailTypeDB email2Type = new EmailTypeDB();
EmailTypeDB email3Type = new EmailTypeDB();
switch (preferEmailTypeFile.Name)
{
case "Home":
email2Type.Name = EmailTypeDB.VCF_EMAIL_TYPE_WORK_STRING;
email3Type.Name = EmailTypeDB.VCF_EMAIL_TYPE_OTHER_STRING;
break;
case "Work":
email2Type.Name = EmailTypeDB.VCF_EMAIL_TYPE_HOME_STRING;
email3Type.Name = EmailTypeDB.VCF_EMAIL_TYPE_OTHER_STRING;
break;
case "Other":
email2Type.Name = EmailTypeDB.VCF_EMAIL_TYPE_HOME_STRING;
email3Type.Name = EmailTypeDB.VCF_EMAIL_TYPE_WORK_STRING;
break;
}
email2DB.EmailType = email2Type;
email3DB.EmailType = email3Type;
listEmail.Add(email2DB);
listEmail.Add(email3DB);
}
}
// get fax
vCardPhoneCollection listVPhone = card.Phones;
if (listVPhone != null && listVPhone.Count() > 0)
{
foreach (vCardPhone vphone in listVPhone)
{
if (vphone.IsFax)
{
FaxDB faxDB = new FaxDB();
faxDB.Value = vphone.FullNumber;
FaxTypeDB faxType = new FaxTypeDB();
if (vphone.IsHome)
{
faxType.Name = FaxTypeDB.VCF_FAX_TYPE_HOME_STRING;
}
else if (vphone.IsWork)
{
faxType.Name = FaxTypeDB.VCF_FAX_TYPE_WORK_STRING;
}
else
{
faxType.Name = FaxTypeDB.VCF_FAX_TYPE_OTHER_STRING;
}
faxType.AccountTypeName = AccountTypeDB.TYPE_VCF_STRING;
faxDB.AccountTypeName = AccountTypeDB.TYPE_VCF_STRING;
faxDB.FaxType = faxType;
listFax.Add(faxDB);
}
}
}
AccountDB account = dbManager.GetVCFAccount();
string accTypeName = AccountTypeDB.TYPE_VCF_STRING;
List<ContactDB> listContact = new List<ContactDB>();
ContactDB contact = new ContactDB();
contact.AccountId = account.Id;
contact.AccountTypeName = accTypeName;
contact.Id = -1;
contact.FirstName = card.GivenName;
contact.MiddleName = card.AdditionalNames;
contact.LastName = card.FamilyName;
contact.ListEmail = listEmail;
contact.ListFax = listFax;
listContact.Add(contact);
return AddOrUpdateContact(listContact);
}
public List<int> GetContactsDuplicate(ContactDB contactAdded, List<ContactDB> listAllContactCurrent, List<EmailDB> listEmail, List<FaxDB> listFax, int accountTypeId) // ttt
{
List<int> listContactId = new List<int>();
if (listEmailTypeAll.Count > 0 && listFaxTypeAll.Count > 0)
{
DuplicateContactMode mode = DBParameter.DuplicateMode;
int emailIndex = -1;
int faxIndex = -1;
EmailDB emailDB = new EmailDB();
FaxDB faxDB = new FaxDB();
try
{
for (int e = 0; e < listEmail.Count(); e++)
{
if (listEmail[e].EmailType.IsPreferred)
{
emailDB.Value = listEmail[e].Value;
emailDB.AccountTypeId = listEmail[e].AccountTypeId;
emailIndex = e;
break;
}
}
for (int f = 0; f < listFax.Count(); f++)
{
if (listFax[f].FaxType.IsPreferred)
{
faxDB.Value = listFax[f].Value;
faxDB.AccountTypeId = listFax[f].AccountTypeId;
faxIndex = f;
break;
}
}
// Checked list contacts current with object contact added
foreach (var contactCurrent in listAllContactCurrent)
{
string email = String.Empty;
string fax = String.Empty;
// Get value email
var lEmail = contactCurrent.ListEmail.Where(e => e.AccountTypeId == accountTypeId).ToList();
if (contactCurrent.ListEmail.Count() == 1)
{
email = contactCurrent.ListEmail[0].Value;
}
else
{
foreach (var emailMapping in lEmail)
{
if (String.Compare(emailDB.Value, emailMapping.Value) == 0)
{
email = emailMapping.Value;
break;
}
}
if (email == String.Empty)
{
email = contactCurrent.ListEmail[emailIndex].Value;
}
}
// Get value fax
var lFax = contactCurrent.ListFax.Where(f => f.AccountTypeId == accountTypeId).ToList();
if (contactCurrent.ListFax.Count() == 1)
{
email = contactCurrent.ListEmail[0].Value;
}
else
{
foreach (var faxMapping in lFax)
{
if (String.Compare(faxDB.Value, faxMapping.Value) == 0)
{
fax = faxMapping.Value;
break;
}
}
if (fax == String.Empty)
{
fax = contactCurrent.ListFax[faxIndex].Value;
}
}
if (String.Compare(contactAdded.FirstName, contactCurrent.FirstName) == 0 &&
String.Compare(contactAdded.LastName, contactCurrent.LastName) == 0 &&
String.Compare(contactAdded.MiddleName, contactCurrent.MiddleName) == 0 &&
String.Compare(emailDB.Value, email) == 0 &&
String.Compare(faxDB.Value, fax) == 0)
{
listContactId.Add(contactCurrent.Id);
}
}
}
catch
{
}
}
return listContactId;
}
private void LoadContact()
{
if (currentMode == MODE_LOCAL)
{
contactDB = dbController.GetContactById(contactId);
tbox_firstname.Text = contactDB.FirstName;
tbox_middlename.Text = contactDB.MiddleName;
tbox_lastname.Text = contactDB.LastName;
string email = contactDB.ListEmail.Count > 0 ? contactDB.ListEmail[0].Value : "";
string fax = contactDB.ListFax.Count > 0 ? contactDB.ListFax[0].Value : "";
tbox_email.Text = email;
tbox_fax.Text = fax;
tbox_group.SetTextAndIgnoreTextChanged(contactDB.GroupName);
}
else if (currentMode == MODE_PRINTER)
{
string firstName = "", midName = "", lastName = "";
string email = "";
string fax = "";
string groupName = "";
string id = "";
if (contactDB != null)
{
firstName = contactDB.FirstName;
midName = contactDB.MiddleName;
lastName = contactDB.LastName;
groupName = contactDB.GroupName;
email = contactDB.ListEmail.Count > 0 ? contactDB.ListEmail[0].Value : "";
fax = contactDB.ListFax.Count > 0 ? contactDB.ListFax[0].Value : "";
}
else if (contactPrinter != null)
{
id = contactPrinter.Id.ToString();
//string[] names = ContactDB.GetFirstMidLastFromFullName(contactPrinter.Name, contactPrinter.NameOrder);
//firstName = names[0];
//lastName = names[1];
//midName = names[2];
firstName = contactPrinter.FirstName;
lastName = contactPrinter.LastName;
midName = contactPrinter.MiddleName;
groupName = contactPrinter.GroupName;
email = contactPrinter.Email;
fax = contactPrinter.Fax;
id = contactPrinter.Id.ToString();
}
tbox_firstname.Text = firstName;
tbox_middlename.Text = midName;
tbox_lastname.Text = lastName;
tbox_contact_id.Text = id;
EmailDB emailDB = new EmailDB();
emailDB.Id = 0;
emailDB.Value = email;
listEmail.Add(emailDB);
FaxDB faxDB = new FaxDB();
faxDB.Id = 0;
faxDB.Value = fax;
listFax.Add(faxDB);
tbox_email.Text = email;
tbox_fax.Text = fax;
tbox_group.SetTextAndIgnoreTextChanged(groupName);
}
LoadComboboxEmail();
LoadComboboxFax();
}
public int AddContact(ContactDB contact)
{
//open connection
if (this.OpenConnection() == true)
{
string contactName = contact.Name;
int accountId = contact.AccountId;
List<EmailDB> listEmail = contact.ListEmail;
List<FaxDB> listFax = contact.ListFax;
// insert new contact
string firstName = contact.FirstName;
string middleName = contact.MiddleName;
string lastName = contact.LastName;
SQLiteTransaction trans = connection.BeginTransaction();
int contactId = -1;
try
{
SQLiteCommand comm = connection.CreateCommand();
comm.Transaction = trans;
comm.CommandText = "INSERT INTO contact (first_name, middle_name, last_name, account_id) " +
"VALUES(@first_name, @middle_name, @last_name, @account_id)";
comm.Parameters.AddWithValue("@first_name", firstName);
comm.Parameters.AddWithValue("@middle_name", middleName);
comm.Parameters.AddWithValue("@last_name", lastName);
comm.Parameters.AddWithValue("@account_id", accountId);
comm.ExecuteNonQuery();
SQLiteCommand lastIdComm = connection.CreateCommand();
lastIdComm.CommandText = "SELECT last_insert_rowid()";
contactId = Int32.Parse(lastIdComm.ExecuteScalar().ToString());
// save list email
int count1 = listEmail.Count();
EmailDB emailDB;
for (int j = 0; j < count1; j++)
{
emailDB = listEmail[j];
SQLiteCommand insMailComm = connection.CreateCommand();
insMailComm.Transaction = trans;
insMailComm.CommandText = "INSERT INTO email (contact_id, value, email_type_id)" +
" VALUES(@contact_id, @value, @email_type_id)";
insMailComm.Parameters.AddWithValue("@contact_id", contactId);
insMailComm.Parameters.AddWithValue("@value", emailDB.Value);
insMailComm.Parameters.AddWithValue("@email_type_id", emailDB.EmailType.Id);
insMailComm.ExecuteNonQuery();
}
// save list fax
count1 = listFax.Count();
FaxDB faxDB;
for (int j = 0; j < count1; j++)
{
faxDB = listFax[j];
SQLiteCommand insFaxComm = connection.CreateCommand();
insFaxComm.Transaction = trans;
insFaxComm.CommandText = "INSERT INTO fax (contact_id, value, fax_type_id)" +
" VALUES(@contact_id, @value, @fax_type_id)";
insFaxComm.Parameters.AddWithValue("@contact_id", contactId);
insFaxComm.Parameters.AddWithValue("@value", faxDB.Value);
insFaxComm.Parameters.AddWithValue("@fax_type_id", faxDB.FaxType.Id);
insFaxComm.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
}
//close connection
this.CloseConnection();
return contactId;
}
return 0;
}
private void btn_save_Click(object sender, RoutedEventArgs e)
{
int currentLimitContact = ContactDB.MAX_CONTACT;
int currentLimitGroup = GroupDB.MAX_GROUP;
int newContactCount = UC_AddressBook.AllContactCount + 1;
int newGroupCount;
string group = MyUtils.RemoveSpaces(tbox_group.Text);
string firstName = MyUtils.RemoveSpaces(tbox_firstname.Text);
string middleName = MyUtils.RemoveSpaces(tbox_middlename.Text);
string lastName = MyUtils.RemoveSpaces(tbox_lastname.Text);
if (String.IsNullOrEmpty(firstName + middleName + lastName))
{
NotifyUser(CMLibrary.Properties.Resources.name_no_empty, NotifyType.ErrorMessage);
return;
}
string email = tbox_email.Text.Trim();
string fax = MyUtils.RemoveSpaces(tbox_fax.Text);
string contact_id = tbox_contact_id.Text.Trim();
if (String.IsNullOrEmpty(email))
{
if (String.IsNullOrEmpty(fax))
{
NotifyUser(CMLibrary.Properties.Resources.enter_value_for_email_or_fax, NotifyType.ErrorMessage);
return;
}
}
else if (!MyUtils.IsEmailValid(email))
{
NotifyUser(CMLibrary.Properties.Resources.email_invalid, NotifyType.ErrorMessage);
return;
}
if (CheckLimitGroupInLocal(group) > 0)
{
newGroupCount = UC_AddressBook.AllGroupCount + CheckLimitGroupInLocal(group);
}
else
{
newGroupCount = UC_AddressBook.AllGroupCount;
}
DuplicateContactMode mode;
if (radio_btn_allow_duplicate.IsChecked == true)
{
mode = DuplicateContactMode.ALLOW_ADD_NEW;
}
else if (radio_btn_replace_with_new.IsChecked == true)
{
mode = DuplicateContactMode.REPLACE_WITH_NEW_ITEM;
}
else
{
mode = DuplicateContactMode.DO_NOT_IMPORT;
}
dbController.UpdateDuplicateContactMode(mode);
if (currentMode == MODE_LOCAL)
{
int res = 0;
List<int> listBelongGroupId = new List<int>();
List<int> listContactIdDuplicated = new List<int>();
bool isValidateContactLimit;
if (contactId <= 0) // add new
{
isValidateContactLimit = validate_contact_limit(currentLimitContact, currentLimitGroup, newContactCount, newGroupCount);
if (isValidateContactLimit)
{
listContactId = dbController.SelfAddContact(firstName, middleName, lastName, email, fax);
}
else {
this.Close();
}
}
else
{
int updRes = 0;
// list email
List<EmailDB> lEmail = new List<EmailDB>();
EmailDB emailDB = new EmailDB();
emailDB.Value = email;
lEmail.Add(emailDB);
// list fax
List<FaxDB> lFax = new List<FaxDB>();
FaxDB faxDB = new FaxDB();
faxDB.Value = fax;
lFax.Add(faxDB);
List<ContactDB> listContactDB = new List<ContactDB>();
ContactDB contactDB = new ContactDB();
contactDB.Id = contactId;
contactDB.FirstName = firstName;
contactDB.LastName = lastName;
contactDB.MiddleName = middleName;
contactDB.ListEmail = lEmail;
contactDB.ListFax = lFax;
listContactDB.Add(contactDB);
listContactIdDuplicated = dbController.GetContactIdDuplicated(listContactDB);
if (mode == DuplicateContactMode.DO_NOT_IMPORT)
{
if (listContactIdDuplicated.Any())
{
this.Close();
return;
}
else
{
isValidateContactLimit = validate_contact_limit(ContactDB.MAX_LIMITATION, currentLimitGroup, 0, newGroupCount);
if (isValidateContactLimit)
{
updRes = dbController.UpdateContact(contactId, firstName, middleName, lastName, email, fax);
}
else
{
return;
}
}
}
else if (mode == DuplicateContactMode.REPLACE_WITH_NEW_ITEM)
{
isValidateContactLimit = validate_contact_limit(ContactDB.MAX_LIMITATION, currentLimitGroup, 0, newGroupCount);
if (isValidateContactLimit)
{
updRes = dbController.UpdateContact(contactId, firstName, middleName, lastName, email, fax);
dbController.UpdateContactDuplicate(contactId, listContactIdDuplicated);
}
else { return; }
}
else
{
isValidateContactLimit = validate_contact_limit(ContactDB.MAX_LIMITATION, currentLimitGroup, 0, newGroupCount);
if (isValidateContactLimit)
{
updRes = dbController.UpdateContact(contactId, firstName, middleName, lastName, email, fax);
}
else
{
return;
}
}
if (updRes <= 0)
{
NotifyUser(CMLibrary.Properties.Resources.database_failure, NotifyType.ErrorMessage);
return;
}
else
{
if (parent is Page_LocalContacts)
{
((Page_LocalContacts)parent).clearPreviousState();
//((Page_LocalContacts)parent).Reload();
}
}
}
//List<int> contactIds = new List<int>();
//contactIds.Add(contactId);
//List<int> currentBelongGroupIds = dbController.GetGroupIdListByCheckedContactIdList(contactIds);
if (isValidateContactLimit == true)
{
listBelongGroupId = GetListGroupId();
if (listBelongGroupId == null)
{
return;
}
}
if (listContactId.Count != 0)
{
foreach (var Id in listContactId)
{
res = dbController.UpdateContactBelongToGroupList(Id, listBelongGroupId);
}
}
else
{
res = dbController.UpdateContactBelongToGroupList(contactId, listBelongGroupId);
}
// update all contacts if option choose replace duplicate
if (listContactIdDuplicated.Any() && mode != DuplicateContactMode.ALLOW_ADD_NEW)
{
foreach (var Id in listContactIdDuplicated)
{
res = dbController.UpdateContactBelongToGroupList(Id, listBelongGroupId);
}
}
if (res > 0)
{
if (parent is Page_LocalContacts)
{
Page_LocalContacts page = (Page_LocalContacts)parent;
page.NotifyNewGroupAndContactAdded();
page.RefreshContactCount();
}
this.Close();
}
else
{
NotifyUser(CMLibrary.Properties.Resources.database_failure, NotifyType.ErrorMessage);
}
}
else if (currentMode == MODE_PRINTER)
{
Page_PrinterContacts uc_parent = null;
if (parent is Page_PrinterContacts)
{
uc_parent = (Page_PrinterContacts)parent;
}
string listGroupStr = tbox_group.Text.TrimStart().TrimEnd();
List<string> listGroupSplits = GetListGroupString(listGroupStr);
int len = listGroupSplits.Count;
if (len > GroupDB.MAX_GROUP)
{
NotifyUser(CMLibrary.Properties.Resources.exceed_group_limit, NotifyType.ErrorMessage);
return;
}
string groupName = "";
for (int i = 0; i < len; i++)
{
groupName += listGroupSplits[i];
if (i < len - 1)
{
groupName += GroupDB.SEPARATE_TOKEN;
}
}
var listContactPrinter = DataManager.Instance.ListSentToPrinter;
if (contactDB == null && contactPrinter == null) // Add new
{
int contactPrinter_Id = 1;
if (listContactPrinter.Any())
{
contactPrinter_Id = listContactPrinter.Cast<ContactPrinter>().ToList().OrderBy(c => c.Id).ToList().Last().Id + 1;
}
contactPrinter = new ContactPrinter();
contactPrinter.Id = contactPrinter_Id;
string name = ContactDB.GetFullNameFromFirstMidLast(firstName, middleName, lastName, contactPrinter.NameOrder);
contactPrinter.FirstName = firstName;
contactPrinter.LastName = lastName;
contactPrinter.MiddleName = middleName;
contactPrinter.Name = name;
contactPrinter.Email = email;
contactPrinter.Fax = fax;
contactPrinter.GroupName = groupName;
contactPrinter.ContactAction = ContactAB.PRINTER_ADD;
Printer printer = new Printer("", AccountDB.SELF_ACCOUNT_NAME);
contactPrinter.FromPrinter = printer;
List<ContactPrinter> list = new List<ContactPrinter>();
list.Add(contactPrinter);
if (uc_parent != null)
{
uc_parent.AddMoreContacts(list);
}
}
else // edit
{
if (contactDB != null)
{
contactDB.FirstName = firstName;
contactDB.MiddleName = middleName;
contactDB.LastName = lastName;
contactDB.SelfUpdateName();
List<EmailDB> listEmail = contactDB.ListEmail;
if (listEmail.Count > 0)
{
listEmail[0].Value = email;
}
else
{
EmailDB emailDB = new EmailDB();
emailDB.Value = email;
listEmail.Add(emailDB);
}
List<FaxDB> listFax = contactDB.ListFax;
if (listFax.Count > 0)
{
listFax[0].Value = fax;
}
else
{
FaxDB faxlDB = new FaxDB();
faxlDB.Value = fax;
listFax.Add(faxlDB);
}
var isDuplicate = uc_parent.FindDuplicateContact123(contactDB);
if (uc_parent.CheckLimitGroupWhenChange(groupName))
{
NotifyUser(CMLibrary.Properties.Resources.group_limitation_printer, NotifyType.ErrorMessage);
return;
}
switch (mode)
{
case DuplicateContactMode.REPLACE_WITH_NEW_ITEM:
contactDB.GroupName = groupName;
break;
case DuplicateContactMode.ALLOW_ADD_NEW:
contactDB.GroupName = groupName;
break;
case DuplicateContactMode.DO_NOT_IMPORT:
if (!isDuplicate.Any())
{
contactDB.GroupName = groupName;
}
break;
default:
break;
}
if (uc_parent != null)
{
uc_parent.UpdateContact(contactDB, false);
}
}
else if (contactPrinter != null)
{
var isDuplicate = uc_parent.FindDuplicateContact123(contactPrinter);
string name = ContactDB.GetFullNameFromFirstMidLast(firstName, middleName, lastName, ContactDB.NAME_ORDER_FIRST_LAST_MID);
contactPrinter.FirstName = firstName;
contactPrinter.LastName = lastName;
contactPrinter.MiddleName = middleName;
contactPrinter.Name = name;
contactPrinter.Email = email;
contactPrinter.Fax = fax;
if (uc_parent.CheckLimitGroupWhenChange(groupName))
{
NotifyUser(CMLibrary.Properties.Resources.group_limitation_printer, NotifyType.ErrorMessage);
return;
}
switch (mode)
{
case DuplicateContactMode.REPLACE_WITH_NEW_ITEM:
contactPrinter.GroupName = groupName;
break;
case DuplicateContactMode.ALLOW_ADD_NEW:
contactPrinter.GroupName = groupName;
break;
case DuplicateContactMode.DO_NOT_IMPORT:
if (!isDuplicate.Any())
{
contactPrinter.GroupName = groupName;
}
break;
default:
break;
}
if (contactPrinter.IsLocal(int.Parse(contact_id)))
{
contactPrinter.ContactAction = ContactAB.PRINTER_UPDATE;
}
if (uc_parent != null)
{
uc_parent.UpdateContact(contactPrinter, false);
}
}
}
this.Close();
}
}
// Contact
public ContactDB GetContactById(int id)
{
string query =
"SELECT contact.id as id, contact.first_name as first_name, contact.middle_name as middle_name, contact.last_name as last_name, " +
"pref_email.value as email, pref_email.id as email_id, pref_fax.value as fax, pref_fax.id as fax_id, account.username as accountname, " +
"account.id as accountid, account_type.name as accounttypename, " +
"pref_email.preferred as email_preferred, pref_fax.preferred as fax_preferred, " +
"GROUP_CONCAT('group'.name , ';') as groupname, " +
"GROUP_CONCAT('group'.id , ';') as groupid, " +
"contact_name_order.name_order as name_order " +
"FROM " +
"contact " +
"join account on contact.account_id = account.id " +
"join account_type on account_type.id = account.account_type_id " +
"join host_contact_name_order on host_contact_name_order.id = account_type.id " +
"join contact_name_order on contact_name_order.id = host_contact_name_order.contact_name_order_id " +
"left join ('group' join group_contact on 'group'.id = group_contact.group_id) on contact.id = group_contact.contact_id " +
"left join (select min(email.id) as id, contact_id, email.value, email_type_id, email_type.account_type_id, name, preferred from email, email_type where email_type_id = email_type.id and email_type.preferred = 1 group by contact_id) as pref_email on contact.id = pref_email.contact_id " +
"left join (select min(fax.id) as id, contact_id, fax.value, fax_type_id, fax_type.account_type_id, name, preferred from fax, fax_type where fax_type_id = fax_type.id and fax_type.preferred = 1 group by contact_id) as pref_fax on contact.id = pref_fax.contact_id " +
"where " +
"(( " +
"pref_email.preferred = 1 and pref_fax.preferred = 1 " +
") " +
"or " +
"( " +
"pref_email.preferred is null or pref_fax.preferred is null " +
"))" +
"and " +
"( " +
"contact.id = @id " +
") " +
"group by contact.id";
//Create a list to store the result
ContactDB contact = new ContactDB();
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
SQLiteCommand cmd = new SQLiteCommand(query, connection);
//Create a data reader and Execute the command
cmd.Parameters.AddWithValue("@id", id);
SQLiteDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
int accid = Int32.Parse(dataReader["accountid"].ToString());
string first_name = dataReader["first_name"].ToString();
string middle_name = dataReader["middle_name"].ToString();
string last_name = dataReader["last_name"].ToString();
string email = ("" + dataReader["email"]).ToString();
string fax = ("" + dataReader["fax"]).ToString();
string accountname = dataReader["accountname"].ToString();
string accounttypename = dataReader["accounttypename"].ToString();
string name_order = dataReader["name_order"].ToString();
string groupid = dataReader["groupid"].ToString();
string groupName = dataReader["groupname"].ToString();
contact.Id = id;
contact.FirstName = first_name;
contact.MiddleName = middle_name;
contact.LastName = last_name;
contact.AccountName = accountname;
contact.AccountId = accid;
contact.NameOrder = name_order;
contact.GroupId = groupid;
contact.GroupName = groupName;
// only preferred email
List<EmailDB> listEmail = new List<EmailDB>();
if (!String.IsNullOrEmpty(email))
{
int emailId = Int32.Parse(dataReader["email_id"].ToString());
EmailDB emailDB = new EmailDB();
emailDB.Id = emailId;
emailDB.Value = email;
listEmail.Add(emailDB);
}
contact.ListEmail = listEmail;
List<FaxDB> listFax = new List<FaxDB>();
if (!String.IsNullOrEmpty(fax))
{
int faxId = Int32.Parse(dataReader["fax_id"].ToString());
FaxDB faxDB = new FaxDB();
faxDB.Id = faxId;
faxDB.Value = fax;
listFax.Add(faxDB);
}
contact.ListFax = listFax;
contact.AccountTypeName = accounttypename;
break;
}
//close Data Reader
dataReader.Close();
//close Connection
this.CloseConnection();
}
return contact;
}
public FileImportResult ImportFromVCFFile(string path)
{
FileImportResult result = new FileImportResult();
try
{
File.ReadAllText(path);
}
catch (Exception e)
{
result.errorCode = CODE_FILE_IN_USE;
return result;
}
vCard card = null;
try
{
card = new vCard(path);
}
catch (Exception ex)
{
result.errorCode = CODE_FILE_STRUCTURE_NOT_SUITABLE;
return result;
}
List<EmailDB> listEmail = new List<EmailDB>();
List<FaxDB> listFax = new List<FaxDB>();
// get email
vCardEmailAddressCollection listVEmail = card.EmailAddresses;
if (listVEmail != null && listVEmail.Count() > 0)
{
foreach (vCardEmailAddress vEmail in listVEmail)
{
EmailDB emailDB = new EmailDB();
emailDB.Value = vEmail.Address;
EmailTypeDB emailType = new EmailTypeDB();
if (vEmail.ItemType.ToString().Equals("HOME"))
{
emailType.Name = EmailTypeDB.VCF_EMAIL_TYPE_HOME_STRING;
}
else if (vEmail.ItemType.ToString().Equals("WORK"))
{
emailType.Name = EmailTypeDB.VCF_EMAIL_TYPE_WORK_STRING;
}
else
{
emailType.Name = EmailTypeDB.VCF_EMAIL_TYPE_OTHER_STRING;
}
emailType.AccountTypeName = AccountTypeDB.TYPE_VCF_STRING;
emailDB.AccountTypeName = AccountTypeDB.TYPE_VCF_STRING;
emailDB.EmailType = emailType;
listEmail.Add(emailDB);
}
}
// get fax
vCardPhoneCollection listVPhone = card.Phones;
if (listVPhone != null && listVPhone.Count() > 0)
{
foreach (vCardPhone vphone in listVPhone)
{
if (vphone.IsFax)
{
FaxDB faxDB = new FaxDB();
faxDB.Value = vphone.FullNumber;
FaxTypeDB faxType = new FaxTypeDB();
if (vphone.IsHome)
{
faxType.Name = FaxTypeDB.VCF_FAX_TYPE_HOME_STRING;
}
else if (vphone.IsWork)
{
faxType.Name = FaxTypeDB.VCF_FAX_TYPE_WORK_STRING;
}
else
{
faxType.Name = FaxTypeDB.VCF_FAX_TYPE_OTHER_STRING;
}
faxType.AccountTypeName = AccountTypeDB.TYPE_VCF_STRING;
faxDB.AccountTypeName = AccountTypeDB.TYPE_VCF_STRING;
faxDB.FaxType = faxType;
listFax.Add(faxDB);
}
}
}
AccountDB account = dbManager.GetVCFAccount();
string accTypeName = AccountTypeDB.TYPE_VCF_STRING;
List<ContactDB> listContact = new List<ContactDB>();
ContactDB contact = new ContactDB();
contact.AccountId = account.Id;
contact.AccountTypeName = accTypeName;
contact.Id = -1;
contact.FirstName = card.GivenName;
contact.MiddleName = card.AdditionalNames;
contact.LastName = card.FamilyName;
contact.ListEmail = listEmail;
contact.ListFax = listFax;
listContact.Add(contact);
return AddOrUpdateContact(listContact);
}
public void Clone(ContactDB contactDB)
{
if (contactDB != null)
{
contactAction = contactDB.ContactAction;
id = contactDB.Id;
firstName = contactDB.FirstName;
middleName = contactDB.MiddleName;
lastName = contactDB.LastName;
name = contactDB.Name;
// clone email list
listEmail = new List<EmailDB>();
foreach (EmailDB emailDB in contactDB.ListEmail)
{
EmailDB cloneEmailDB = new EmailDB();
cloneEmailDB.Clone(emailDB);
listEmail.Add(cloneEmailDB);
}
// clone fax list
listFax = new List<FaxDB>();
foreach (FaxDB faxDB in contactDB.ListFax)
{
FaxDB cloneFaxDB = new FaxDB();
cloneFaxDB.Clone(faxDB);
listFax.Add(cloneFaxDB);
}
groupName = contactDB.GroupName;
nameOrder = contactDB.NameOrder;
}
}
public FileImportResult ImportFromCSVFile(string path)
{
FileImportResult result = new FileImportResult();
// the structure is name,email,fax
if (File.Exists(path))
{
string data = "";
try
{
data = File.ReadAllText(path);
}
catch (Exception e)
{
result.errorCode = CODE_FILE_IN_USE;
return result;
}
string[] contactSpits = data.Split(new string[] { "\r\n", "\n" }, StringSplitOptions.None);
int size = contactSpits.Count();
EmailTypeDB emailType = dbManager.GetPreferredEmailTypeByAccountTypeName(AccountTypeDB.TYPE_CSV_STRING);
FaxTypeDB faxType = dbManager.GetPreferredFaxTypeByAccountTypeName(AccountTypeDB.TYPE_CSV_STRING);
AccountDB account = dbManager.GetCSVAccount();
string accTypeName = AccountTypeDB.TYPE_CSV_STRING;
List<ContactDB> listContact = new List<ContactDB>();
ContactDB contact;
for (int i = 1; i < size; i++) // begin with 1 because first line is header
{
string bareData = contactSpits[i];
string[] valueSplits = bareData.Split(',');
if (valueSplits.Count() == 3)
{
string name = valueSplits[0];
name = name.Replace(COMMA_REPLACE, ",");
string email = valueSplits[1];
email = email.Replace(COMMA_REPLACE, ",");
string fax = valueSplits[2];
fax = fax.Replace(COMMA_REPLACE, ",");
if (String.IsNullOrEmpty(name) && String.IsNullOrEmpty(email) && String.IsNullOrEmpty(fax))
{
continue;
}
List<EmailDB> listEmail = new List<EmailDB>();
if (!String.IsNullOrEmpty(email))
{
EmailDB emailDB = new EmailDB();
emailType.AccountTypeName = AccountTypeDB.TYPE_CSV_STRING;
emailDB.AccountTypeName = AccountTypeDB.TYPE_CSV_STRING;
emailDB.Value = email;
emailDB.EmailType = emailType;
listEmail.Add(emailDB);
}
List<FaxDB> listFax = new List<FaxDB>();
if (!String.IsNullOrEmpty(fax))
{
FaxDB faxDB = new FaxDB();
faxType.AccountTypeName = AccountTypeDB.TYPE_CSV_STRING;
faxDB.AccountTypeName = AccountTypeDB.TYPE_CSV_STRING;
faxDB.Value = fax;
faxDB.FaxType = faxType;
listFax.Add(faxDB);
}
contact = new ContactDB();
contact.AccountId = account.Id;
contact.AccountTypeName = accTypeName;
contact.Id = -1;
contact.FirstName = name;
contact.ListEmail = listEmail;
contact.ListFax = listFax;
listContact.Add(contact);
}
}
return AddOrUpdateContact(listContact);
}
result.errorCode = CODE_FILE_CAN_NOT_IMPORT;
return result;
}
private void AddOrUpdateContactsToDatabase()
{
if(isUserCancel)
{
return;
}
List<ContactDB> list = new List<ContactDB>();
int count = listContact.Count();
YahooContact yContact;
for (int i = 0; i < count; i++)
{
yContact = listContact[i];
ContactDB contact = new ContactDB();
contact.AccountId = this.account.Id;
contact.AccountTypeName = AccountTypeDB.TYPE_YAHOO_STRING;
string fullName = yContact.GivenName + " " + yContact.FamilyName + " " + yContact.MiddleName;
contact.Id = -1;
contact.FirstName = yContact.GivenName;
contact.MiddleName = yContact.MiddleName;
contact.LastName = yContact.FamilyName;
// add list email
List<EmailDB> listEmail = new List<EmailDB>();
List<TypeAndValue> emails = yContact.Emails;
int size = emails.Count();
TypeAndValue typeAndValue;
for (int j = 0; j < size; j++)
{
typeAndValue = emails[j];
EmailDB emailDB = new EmailDB();
emailDB.Value = typeAndValue.Value;
EmailTypeDB emailType = new EmailTypeDB();
string type = typeAndValue.Type;
if (type.Equals("PERSONAL"))
{
emailType.Name = EmailTypeDB.YAHOO_EMAIL_TYPE_HOME_STRING;
}
else if (type.Equals("WORK"))
{
emailType.Name = EmailTypeDB.YAHOO_EMAIL_TYPE_WORK_STRING;
}
else
{
emailType.Name = EmailTypeDB.YAHOO_EMAIL_TYPE_EMAIL_STRING;
}
emailType.AccountTypeName = AccountTypeDB.TYPE_YAHOO_STRING;
emailDB.AccountTypeName = AccountTypeDB.TYPE_YAHOO_STRING;
emailDB.EmailType = emailType;
listEmail.Add(emailDB);
}
contact.ListEmail = listEmail;
// add list fax
List<FaxDB> listFax = new List<FaxDB>();
List<TypeAndValue> phones = yContact.Phones;
size = phones.Count();
for (int j = 0; j < size; j++)
{
typeAndValue = phones[j];
string type = typeAndValue.Type;
if (type.Equals("FAX"))
{
FaxDB faxDB = new FaxDB();
faxDB.Value = typeAndValue.Value;
FaxTypeDB faxType = new FaxTypeDB();
faxType.Name = FaxTypeDB.YAHOO_FAX_TYPE_FAX_STRING;
faxType.AccountTypeName = AccountTypeDB.TYPE_YAHOO_STRING;
faxDB.AccountTypeName = AccountTypeDB.TYPE_YAHOO_STRING;
faxDB.FaxType = faxType;
listFax.Add(faxDB);
}
}
contact.ListFax = listFax;
list.Add(contact);
}
CheckMaximumAndImport(list);
}
public List<int> AddOrUpdateContacts(List<ContactDB> list)
{
List<ContactDB> listAllContactCurrent = new List<ContactDB>();
var listAllContact = GetAllListContact();
foreach (var contact in listAllContact)
{
ContactDB contactDB = new ContactDB();
contactDB.Id = contact.Id;
contactDB.FirstName = contact.FirstName;
contactDB.LastName = contact.LastName;
contactDB.MiddleName = contact.MiddleName;
contactDB.AccountId = contact.AccountId;
contactDB.AccountTypeName = contact.AccountTypeName;
contactDB.ListEmail = GetListEmailByContactId(contact.Id);
contactDB.ListFax = GetListFaxByContactId(contact.Id);
listAllContactCurrent.Add(contactDB);
}
var listAccountType = GetAccountType();
if (list == null)
{
return null;
}
if (listEmailTypeAll == null || listEmailTypeAll.Count() == 0)
{
listEmailTypeAll = GetAllEmailType();
}
if (listFaxTypeAll == null || listFaxTypeAll.Count() == 0)
{
listFaxTypeAll = GetAllFaxType();
}
if (listEmailTypeAll.Count > 0 && listFaxTypeAll.Count > 0 && this.OpenConnection() == true)
{
int size = list.Count();
ContactDB contact;
DuplicateContactMode mode = DBParameter.DuplicateMode;
int res = 0;
// int contactId = -1; nvthuong db
List<int> listContactId = new List<int>();
SQLiteTransaction trans = connection.BeginTransaction();
try
{
for (int i = 0; i < size; i++)
{
contact = list[i];
string firstName = contact.FirstName;
string middleName = contact.MiddleName;
string lastName = contact.LastName;
int accountId = contact.AccountId;
List<EmailDB> listEmail = contact.ListEmail;
List<FaxDB> listFax = contact.ListFax;
if (String.IsNullOrEmpty(firstName + middleName + lastName) &&
listEmail.Count() == 0 && listFax.Count() == 0)
{
continue;
}
string accountTypeName = String.Empty;
if (contact.AccountTypeName == null)
{
accountTypeName = "Self";
}
else
{
accountTypeName = contact.AccountTypeName;
}
int accountTypeId = listAccountType.Where(type => type.Name == accountTypeName).Select(type => type.Id).First();
#region Get Email And Fax Type ByA ccount Type Name
if (String.Compare(accountTypeName, AccountTypeDB.TYPE_GMAIL_STRING) == 0)
{
// get list email and fax type
if (listEmailTypeGmail == null)
{
listEmailTypeGmail = GetEmailTypeListByAccountTypeName(listEmailTypeAll, accountTypeName);
}
if (listFaxTypeGmail == null)
{
listFaxTypeGmail = GetFaxTypeListByAccountTypeName(listFaxTypeAll, accountTypeName);
}
// update list email type and fax type
UpdateListEmailDBByListEmailTypeDB(listEmail, listEmailTypeGmail);
UpdateListFaxDBByListFaxTypeDB(listFax, listFaxTypeGmail);
}
else if (String.Compare(accountTypeName, AccountTypeDB.TYPE_YAHOO_STRING) == 0)
{
// get list email and fax type
if (listEmailTypeYahoo == null)
{
listEmailTypeYahoo = GetEmailTypeListByAccountTypeName(listEmailTypeAll, accountTypeName);
}
if (listFaxTypeYahoo == null)
{
listFaxTypeYahoo = GetFaxTypeListByAccountTypeName(listFaxTypeAll, accountTypeName);
}
// update list email type and fax type
UpdateListEmailDBByListEmailTypeDB(listEmail, listEmailTypeYahoo);
UpdateListFaxDBByListFaxTypeDB(listFax, listFaxTypeYahoo);
}
else if (String.Compare(accountTypeName, AccountTypeDB.TYPE_ICLOUD_STRING) == 0)
{
// get list email and fax type
if (listEmailTypeICloud == null)
{
listEmailTypeICloud = GetEmailTypeListByAccountTypeName(listEmailTypeAll, accountTypeName);
}
if (listFaxTypeICloud == null)
{
listFaxTypeICloud = GetFaxTypeListByAccountTypeName(listFaxTypeAll, accountTypeName);
}
// update list email type and fax type
UpdateListEmailDBByListEmailTypeDB(listEmail, listEmailTypeICloud);
UpdateListFaxDBByListFaxTypeDB(listFax, listFaxTypeICloud);
}
else if (String.Compare(accountTypeName, AccountTypeDB.TYPE_LIVE_HOTMAIL_STRING) == 0)
{
// get list email and fax type
if (listEmailTypeLive == null)
{
listEmailTypeLive = GetEmailTypeListByAccountTypeName(listEmailTypeAll, accountTypeName);
}
if (listFaxTypeLive == null)
{
listFaxTypeLive = GetFaxTypeListByAccountTypeName(listFaxTypeAll, accountTypeName);
}
// update list email type and fax type
UpdateListEmailDBByListEmailTypeDB(listEmail, listEmailTypeLive);
UpdateListFaxDBByListFaxTypeDB(listFax, listFaxTypeLive);
}
else if (String.Compare(accountTypeName, AccountTypeDB.TYPE_OUTLOOK_STRING) == 0)
{
// get list email and fax type
if (listEmailTypeOutlook == null)
{
listEmailTypeOutlook = GetEmailTypeListByAccountTypeName(listEmailTypeAll, accountTypeName);
}
if (listFaxTypeOutlook == null)
{
listFaxTypeOutlook = GetFaxTypeListByAccountTypeName(listFaxTypeAll, accountTypeName);
}
// update list email type and fax type
UpdateListEmailDBByListEmailTypeDB(listEmail, listEmailTypeOutlook);
UpdateListFaxDBByListFaxTypeDB(listFax, listFaxTypeOutlook);
}
else if (String.Compare(accountTypeName, AccountTypeDB.TYPE_EXCHANGE_SERVER_STRING) == 0)
{
// get list email and fax type
if (listEmailTypeExchangeServer == null)
{
listEmailTypeExchangeServer = GetEmailTypeListByAccountTypeName(listEmailTypeAll, accountTypeName);
}
if (listFaxTypeExchangeServer == null)
{
listFaxTypeExchangeServer = GetFaxTypeListByAccountTypeName(listFaxTypeAll, accountTypeName);
}
// update list email type and fax type
UpdateListEmailDBByListEmailTypeDB(listEmail, listEmailTypeExchangeServer);
UpdateListFaxDBByListFaxTypeDB(listFax, listFaxTypeExchangeServer);
}
else if (String.Compare(accountTypeName, AccountTypeDB.TYPE_SELF_STRING) == 0)
{
// get list email and fax type
if (listEmailTypeSelf == null)
{
listEmailTypeSelf = GetEmailTypeListByAccountTypeName(listEmailTypeAll, accountTypeName);
}
if (listFaxTypeSelf == null)
{
listFaxTypeSelf = GetFaxTypeListByAccountTypeName(listFaxTypeAll, accountTypeName);
}
// update list email type and fax type
UpdateListEmailDBByListEmailTypeDB(listEmail, listEmailTypeSelf);
UpdateListFaxDBByListFaxTypeDB(listFax, listFaxTypeSelf);
}
else if (String.Compare(accountTypeName, AccountTypeDB.TYPE_CSV_STRING) == 0)
{
// get list email and fax type
if (listEmailTypeCSV == null)
{
listEmailTypeCSV = GetEmailTypeListByAccountTypeName(listEmailTypeAll, accountTypeName);
}
if (listFaxTypeCSV == null)
{
listFaxTypeCSV = GetFaxTypeListByAccountTypeName(listFaxTypeAll, accountTypeName);
}
// update list email type and fax type
UpdateListEmailDBByListEmailTypeDB(listEmail, listEmailTypeCSV);
UpdateListFaxDBByListFaxTypeDB(listFax, listFaxTypeCSV);
}
else if (String.Compare(accountTypeName, AccountTypeDB.TYPE_VCF_STRING) == 0)
{
// get list email and fax type
if (listEmailTypeVCF == null)
{
listEmailTypeVCF = GetEmailTypeListByAccountTypeName(listEmailTypeAll, accountTypeName);
}
if (listFaxTypeVCF == null)
{
listFaxTypeVCF = GetFaxTypeListByAccountTypeName(listFaxTypeAll, accountTypeName);
}
// update list email type and fax type
UpdateListEmailDBByListEmailTypeDB(listEmail, listEmailTypeVCF);
UpdateListFaxDBByListFaxTypeDB(listFax, listFaxTypeVCF);
}
else if (String.Compare(accountTypeName, AccountTypeDB.TYPE_OFFICE365_STRING) == 0)
{
// get list email and fax type
if (listEmailTypeOffice365 == null)
{
listEmailTypeOffice365 = GetEmailTypeListByAccountTypeName(listEmailTypeAll, accountTypeName);
}
if (listFaxTypeOffice365 == null)
{
listFaxTypeOffice365 = GetFaxTypeListByAccountTypeName(listFaxTypeAll, accountTypeName);
}
// update list email type and fax type
UpdateListEmailDBByListEmailTypeDB(listEmail, listEmailTypeOffice365);
UpdateListFaxDBByListFaxTypeDB(listFax, listFaxTypeOffice365);
}
#endregion
//// need to detect duplicate contact here
// duplicate by Name and main Email
string mainEmail = "";
string mainFax = "";
//int duplicatedContactId = -1;
List<int> listDuplicatedContactId = new List<int>();
if (mode != DuplicateContactMode.ALLOW_ADD_NEW)
{
//listDuplicatedContactId = GetContactsDuplicate(contact, listAllContactCurrent, listEmail, listFax, accountTypeId);
foreach (EmailDB email_db in listEmail)
{
if (email_db.EmailType.IsPreferred)
{
mainEmail = email_db.Value;
break;
}
}
foreach (FaxDB fax_db in listFax)
{
if (fax_db.FaxType.IsPreferred)
{
mainFax = fax_db.Value;
break;
}
}
string query =
"SELECT contact.id as id FROM " +
"contact " +
"left join (email join email_type on email_type_id = email_type.id and email_type.preferred = 1) on contact.id = email.contact_id " +
"left join (fax join fax_type on fax_type_id = fax_type.id and fax_type.preferred = 1) on contact.id = fax.contact_id " +
"where " +
"(" +
"email_type.preferred = 1 or email_type.preferred is null " +
"and fax_type.preferred = 1 or fax_type.preferred is null " +
") " +
"And " +
"IFNULL(contact.first_name, '') = @firstname and " +
"IFNULL(contact.middle_name, '') = @middlename and " +
"IFNULL(contact.last_name, '') = @lastname and " +
"IFNULL(email.value, '') = @email and " +
"IFNULL(fax.value, '') = @fax";
//Create Command
SQLiteCommand cmd = new SQLiteCommand(query, connection);
cmd.Parameters.AddWithValue("@firstname", firstName);
cmd.Parameters.AddWithValue("@middlename", middleName);
cmd.Parameters.AddWithValue("@lastname", lastName);
cmd.Parameters.AddWithValue("@email", mainEmail);
cmd.Parameters.AddWithValue("@fax", mainFax);
//Create a data reader and Execute the command
SQLiteDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
listDuplicatedContactId.Add(Int32.Parse(dataReader["id"].ToString()));
}
//close Data Reader
dataReader.Close();
if (listDuplicatedContactId.Any() && mode == DuplicateContactMode.DO_NOT_IMPORT)
{
continue;
}
}
////
// insert new contact
int contactId = 0;
if (!listDuplicatedContactId.Any())
{
SQLiteCommand comm = connection.CreateCommand();
comm.Transaction = trans;
comm.CommandText = "INSERT INTO contact (first_name, middle_name, last_name, account_id) " +
"VALUES(@first_name, @middle_name, @last_name, @account_id)";
comm.Parameters.AddWithValue("@first_name", firstName);
comm.Parameters.AddWithValue("@middle_name", middleName);
comm.Parameters.AddWithValue("@last_name", lastName);
comm.Parameters.AddWithValue("@account_id", accountId);
comm.ExecuteNonQuery();
SQLiteCommand lastIdComm = connection.CreateCommand();
lastIdComm.CommandText = "SELECT last_insert_rowid()";
contactId = Int32.Parse(lastIdComm.ExecuteScalar().ToString());
listContactId.Add(Int32.Parse(lastIdComm.ExecuteScalar().ToString()));
// save list email
//// delete old list email first
SQLiteCommand delComm = connection.CreateCommand();
delComm.Transaction = trans;
delComm.CommandText = "DELETE FROM email WHERE contact_id=@id";
delComm.Parameters.AddWithValue("@id", contactId);
delComm.ExecuteNonQuery();
//// save
int count1 = listEmail.Count();
EmailDB emailDB;
//List<int> listEmailTypeId = new List<int>();
for (int j = 0; j < count1; j++)
{
emailDB = listEmail[j];
int typeId = emailDB.EmailType.Id;
SQLiteCommand insMailComm = connection.CreateCommand();
insMailComm.Transaction = trans;
insMailComm.CommandText = "INSERT INTO email (contact_id, value, email_type_id)" +
" VALUES(@contact_id, @value, @email_type_id)";
insMailComm.Parameters.AddWithValue("@contact_id", contactId);
insMailComm.Parameters.AddWithValue("@value", emailDB.Value);
insMailComm.Parameters.AddWithValue("@email_type_id", typeId);
insMailComm.ExecuteNonQuery();
}
// save list fax
//// delete old list fax first
SQLiteCommand delComm2 = connection.CreateCommand();
delComm2.Transaction = trans;
delComm2.CommandText = "DELETE FROM fax WHERE contact_id=@id";
delComm2.Parameters.AddWithValue("@id", contactId);
delComm2.ExecuteNonQuery();
//// save
count1 = listFax.Count();
FaxDB faxDB;
//List<int> listFaxTypeId = new List<int>();
for (int j = 0; j < count1; j++)
{
faxDB = listFax[j];
int typeId = faxDB.FaxType.Id;
SQLiteCommand insFaxComm = connection.CreateCommand();
insFaxComm.Transaction = trans;
insFaxComm.CommandText = "INSERT INTO fax (contact_id, value, fax_type_id)" +
" VALUES(@contact_id, @value, @fax_type_id)";
insFaxComm.Parameters.AddWithValue("@contact_id", contactId);
insFaxComm.Parameters.AddWithValue("@value", faxDB.Value);
insFaxComm.Parameters.AddWithValue("@fax_type_id", faxDB.FaxType.Id);
insFaxComm.ExecuteNonQuery();
}
res++;
}
else
{
// update account id
foreach (var duplicatedContactId in listDuplicatedContactId)
{
SQLiteCommand comm = connection.CreateCommand();
comm.Transaction = trans;
comm.CommandText = "UPDATE contact set account_id = @account_id where id = @id";
comm.Parameters.AddWithValue("@account_id", accountId);
comm.Parameters.AddWithValue("@id", duplicatedContactId);
comm.ExecuteNonQuery();
listContactId.Add(duplicatedContactId);
// save list email
//// delete old list email first
SQLiteCommand delComm = connection.CreateCommand();
delComm.Transaction = trans;
delComm.CommandText = "DELETE FROM email WHERE contact_id=@id";
delComm.Parameters.AddWithValue("@id", duplicatedContactId);
delComm.ExecuteNonQuery();
//// save
int count1 = listEmail.Count();
EmailDB emailDB;
//List<int> listEmailTypeId = new List<int>();
for (int j = 0; j < count1; j++)
{
emailDB = listEmail[j];
int typeId = emailDB.EmailType.Id;
SQLiteCommand insMailComm = connection.CreateCommand();
insMailComm.Transaction = trans;
insMailComm.CommandText = "INSERT INTO email (contact_id, value, email_type_id)" +
" VALUES(@contact_id, @value, @email_type_id)";
insMailComm.Parameters.AddWithValue("@contact_id", duplicatedContactId);
insMailComm.Parameters.AddWithValue("@value", emailDB.Value);
insMailComm.Parameters.AddWithValue("@email_type_id", typeId);
insMailComm.ExecuteNonQuery();
}
// save list fax
//// delete old list fax first
SQLiteCommand delComm2 = connection.CreateCommand();
delComm2.Transaction = trans;
delComm2.CommandText = "DELETE FROM fax WHERE contact_id=@id";
delComm2.Parameters.AddWithValue("@id", duplicatedContactId);
delComm2.ExecuteNonQuery();
//// save
count1 = listFax.Count();
FaxDB faxDB;
//List<int> listFaxTypeId = new List<int>();
for (int j = 0; j < count1; j++)
{
faxDB = listFax[j];
int typeId = faxDB.FaxType.Id;
SQLiteCommand insFaxComm = connection.CreateCommand();
insFaxComm.Transaction = trans;
insFaxComm.CommandText = "INSERT INTO fax (contact_id, value, fax_type_id)" +
" VALUES(@contact_id, @value, @fax_type_id)";
insFaxComm.Parameters.AddWithValue("@contact_id", duplicatedContactId);
insFaxComm.Parameters.AddWithValue("@value", faxDB.Value);
insFaxComm.Parameters.AddWithValue("@fax_type_id", faxDB.FaxType.Id);
insFaxComm.ExecuteNonQuery();
}
// delete group contact duplicate
if (contact.Id < 0)
{
SQLiteCommand delGroupComm = connection.CreateCommand();
delGroupComm.Transaction = trans;
delGroupComm.CommandText = "DELETE FROM group_contact WHERE contact_id=@id";
delGroupComm.Parameters.AddWithValue("@id", duplicatedContactId);
delGroupComm.ExecuteNonQuery();
}
res++;
}
}
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
res = -1;
}
this.CloseConnection();
return listContactId;
}
return null;
}
