public void Clone(EmailDB emailDB)
{
if (emailDB != null)
{
id = emailDB.Id;
accountTypeId = emailDB.AccountTypeId;
accTypeName = emailDB.AccountTypeName;
emailType = new EmailTypeDB();
emailType.Clone(emailDB.EmailType);
value = emailDB.Value;
}
}
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;
}
}
private void AddOrUpdateContactsToDatabase()
{
if(isUserCancel)
{
return;
}
List<ContactDB> list = new List<ContactDB>();
int count = listContact.Count();
if (IsMaximumContactExeed(count))
{
return;
}
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);
}
// save/update database
DBManager db = new DBManager();
int res = db.AddOrUpdateContacts(list);
// notify UI
if (res > 0)
{
NotifyDownloadStatus(DownloadStatus.DONE, String.Format(AddressBook.Controller.Properties.Resources.import_num_contacts, res));
}
else
{
NotifyDownloadStatus(DownloadStatus.INFORM, AddressBook.Controller.Properties.Resources.no_contact_added);
}
}
private void btn_save_Click(object sender, EventArgs e)
{
string firstName = tbox_firstname.Text.Trim();
string middleName = tbox_middlename.Text.Trim();
string lastName = tbox_lastname.Text.Trim();
if (String.IsNullOrEmpty(firstName + middleName + lastName))
{
MessageBox.Show(ContactManagentLibrary.Properties.Resources.name_no_empty);
return;
}
string email = tbox_email.Text.Trim();
string fax = tbox_fax.Text.Trim();
if (String.IsNullOrEmpty(email))
{
if (String.IsNullOrEmpty(fax))
{
MessageBox.Show(ContactManagentLibrary.Properties.Resources.enter_value_for_email_or_fax);
return;
}
}
else if (!MyUtils.IsEmailValid(email))
{
MessageBox.Show(ContactManagentLibrary.Properties.Resources.wrong_email_format);
return;
}
DuplicateContactMode mode;
if (radio_btn_allow_duplicate.Checked)
{
mode = DuplicateContactMode.ALLOW_ADD_NEW;
}
else if (radio_btn_replace_with_new.Checked)
{
mode = DuplicateContactMode.REPLACE_WITH_NEW_ITEM;
}
else
{
mode = DuplicateContactMode.DO_NOT_IMPORT;
}
dbController.UpdateDuplicateContactMode(mode);
if (currentMode == MODE_LOCAL)
{
bool addNew = false;
if (contactId <= 0) // add new
{
addNew = true;
contactId = dbController.SelfAddContact(firstName, middleName, lastName, email, fax);
}
else
{
int updRes = dbController.UpdateContact(contactId, firstName, middleName, lastName, email, fax);
if (updRes <= 0)
{
MessageBox.Show(ContactManagentLibrary.Properties.Resources.database_failure);
return;
}
}
List<int> listBelongGroupId = GetListGroupId();
if (listBelongGroupId == null)
{
return;
}
int res = dbController.UpdateContactBelongToGroupList(contactId, listBelongGroupId);
if (res > 0)
{
if (parent is UC_LocalContacts)
{
UC_LocalContacts uc_cm = (UC_LocalContacts)parent;
if (addNew)
{
uc_cm.NotifyNewContactAdded();
}
uc_cm.NotifyNewGroupAdded();
uc_cm.Reload();
}
this.Close();
}
else
{
MessageBox.Show(ContactManagentLibrary.Properties.Resources.database_failure);
}
}
else if (currentMode == MODE_PRINTER)
{
UC_PrinterContacts uc_parent = null;
if (parent is UC_PrinterContacts)
{
uc_parent = (UC_PrinterContacts)parent;
}
string listGroupStr = search_tbox_group.Text.TrimStart().TrimEnd();
List<string> listGroupSplits = GetListGroupString(listGroupStr);
int len = listGroupSplits.Count();
if (len > GroupDB.MAX_GROUP)
{
MessageBox.Show(ContactManagentLibrary.Properties.Resources.exceed_group_limit);
return;
}
string groupName = "";
for (int i = 0; i < len; i++)
{
groupName += listGroupSplits[i];
if (i < len - 1)
{
groupName += GroupDB.SEPARATE_TOKEN;
}
}
if (contactDB == null && contactPrinter == null) // Add new
{
contactPrinter = new ContactPrinter();
string name = ContactDB.GetFullNameFromFirstMidLast(firstName, middleName, lastName, contactPrinter.NameOrder);
contactPrinter.Name = name;
contactPrinter.Email = email;
contactPrinter.Fax = fax;
contactPrinter.GroupName = groupName;
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);
}
contactDB.GroupName = groupName;
if (uc_parent != null)
{
uc_parent.UpdateContact(contactDB);
}
}
else if (contactPrinter != null)
{
string name = ContactDB.GetFullNameFromFirstMidLast(firstName, middleName, lastName, ContactDB.NAME_ORDER_FIRST_LAST_MID);
contactPrinter.Name = name;
contactPrinter.Email = email;
contactPrinter.Fax = fax;
contactPrinter.GroupName = groupName;
if (uc_parent != null)
{
uc_parent.UpdateContact(contactPrinter);
}
}
}
this.Close();
}
}
private void LoadContact()
{
if (contactId > 0)
{
if (currentMode == MODE_LOCAL)
{
contactDB = dbController.GetContactById(contactId);
}
tbox_firstname.Text = contactDB.FirstName;
tbox_middlename.Text = contactDB.MiddleName;
tbox_lastname.Text = contactDB.LastName;
listEmail = dbController.GetAllEmailByContactId(contactId);
listFax = dbController.GetAllFaxByContactId(contactId);
if (currentMode == MODE_PRINTER)
{
int mainEmailId = contactDB.ListEmail.Count() > 0 ? contactDB.ListEmail[0].Id : -1;
foreach (EmailDB emailDB in listEmail)
{
if (emailDB.Id == mainEmailId)
{
emailDB.Value = contactDB.ListEmail[0].Value;
break;
}
}
int mainFaxId = contactDB.ListFax.Count() > 0 ? contactDB.ListFax[0].Id : -1;
foreach (FaxDB faxDB in listFax)
{
if (faxDB.Id == mainFaxId)
{
faxDB.Value = contactDB.ListFax[0].Value;
break;
}
}
}
LoadComboboxEmail();
LoadComboboxFax();
search_tbox_group.SetTextAndIgnoreTextChanged(contactDB.GroupName);
}
else if (currentMode == MODE_PRINTER && contactPrinter != null)
{
string[] names = ContactDB.GetFirstMidLastFromFullName(contactPrinter.Name, contactPrinter.NameOrder);
tbox_firstname.Text = names[0];
tbox_middlename.Text = names[1];
tbox_lastname.Text = names[2];
string email = contactPrinter.Email;
string fax = contactPrinter.Fax;
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);
LoadComboboxEmail();
LoadComboboxFax();
search_tbox_group.SetTextAndIgnoreTextChanged(contactPrinter.GroupName);
}
}
private void AddOrUpdateContactsToDatabase()
{
if(isUserCancel)
{
return;
}
List<ContactDB> list = new List<ContactDB>();
int size = listContact.Count();
if (IsMaximumContactExeed(size))
{
return;
}
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);
}
}
// save/update database
DBManager db = new DBManager();
int res = db.AddOrUpdateContacts(list);
// notify UI
if (res > 0)
{
NotifyDownloadStatus(DownloadStatus.DONE, String.Format(AddressBook.Controller.Properties.Resources.import_num_contacts, res));
}
else
{
NotifyDownloadStatus(DownloadStatus.INFORM, AddressBook.Controller.Properties.Resources.no_contact_added);
}
}
private void AddOrUpdateContactsToDatabase()
{
if(isUserCancel)
{
return;
}
List<ContactDB> list = new List<ContactDB>();
int count = listContact.Count;
if (IsMaximumContactExeed(count))
{
return;
}
int realCount = 0;
for (int i = 0; i < count; i++)
{
if (!(listContact[i + 1] is ContactItem))
{
continue;
}
realCount++;
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);
}
// save/update database
DBManager db = new DBManager();
int res = db.AddOrUpdateContacts(list);
// notify UI
if (res > 0)
{
NotifyDownloadStatus(DownloadStatus.DONE, String.Format(AddressBook.Controller.Properties.Resources.import_num_contacts, res));
}
else
{
NotifyDownloadStatus(DownloadStatus.INFORM, AddressBook.Controller.Properties.Resources.no_contact_added);
}
}
// 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 List<ContactDB> SearchContactsNotInRange(string search, int from, int limitPerPage, List<int> notInList) // for group management
{
int count = notInList == null ? 0 : notInList.Count();
string notInArray = count == 0 ? "" : "and id not in (";
for (int i = 0; i < count; i++)
{
notInArray += notInList[i].ToString();
if (i < count - 1)
{
notInArray += ",";
}
}
notInArray += count == 0 ? "" : ") ";
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_email.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 asc " +
") as search_contact " +
"WHERE (" +
"TRIM( first_name||' '|| middle_name||' '|| last_name) like @search " +
"or " +
"TRIM( last_name||' '|| first_name||' '|| middle_name) like @search " +
"or " +
"TRIM( first_name||' '|| last_name||' '|| middle_name) like @search " +
"or " +
"TRIM( last_name||' '|| first_name||' '|| middle_name) like @search " +
"or " +
"email like @search " +
"or " +
"fax like @search " +
"or " +
"accountname like @search ) " +
notInArray;
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;
}
// email
public List<EmailDB> GetAllEmailByContactId(int contactId)
{
string query = "SELECT email.id as id, email.value as value, email_type_id, email_type.name as name, email_type.preferred as preferred "
+ "FROM email, email_type where email.email_type_id = email_type.id and "
+ "contact_id = @contactId";
//Create a list to store the result
List<EmailDB> list = new List<EmailDB>();
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
SQLiteCommand cmd = new SQLiteCommand(query, connection);
cmd.Parameters.AddWithValue("@contactId", contactId);
//Create a data reader and Execute the command
SQLiteDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
int id = Int32.Parse(dataReader["id"].ToString());
int email_type_id = Int32.Parse(dataReader["email_type_id"].ToString());
string value = dataReader["value"].ToString();
string email_type_name = dataReader["name"].ToString();
bool preferred = Int32.Parse(dataReader["preferred"].ToString()) == 1;
EmailDB email = new EmailDB();
email.Id = id;
email.Value = value;
EmailTypeDB type = new EmailTypeDB();
type.Id = email_type_id;
type.Name = email_type_name;
type.IsPreferred = preferred;
email.EmailType = type;
list.Add(email);
}
//close Data Reader
dataReader.Close();
//close Connection
this.CloseConnection();
}
return 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 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;
}
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);
}
private void AddOrUpdateContactsToDatabase()
{
if(isUserCancel)
{
return;
}
List<ContactDB> list = new List<ContactDB>();
if (IsMaximumContactExeed(listContact.Entries.Count()))
{
return;
}
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);
}
// save/update database
DBManager db = new DBManager();
int res = db.AddOrUpdateContacts(list);
// notify UI
if (res > 0)
{
NotifyDownloadStatus(DownloadStatus.DONE, String.Format(AddressBook.Controller.Properties.Resources.import_num_contacts, res));
}
else
{
NotifyDownloadStatus(DownloadStatus.INFORM, AddressBook.Controller.Properties.Resources.no_contact_added);
}
}