public static List <EmailAddress> GetEmails(string crmID)
{
try
{
string type = DiscoverType(crmID);
List <EmailAddress> emails = new List <EmailAddress>();
DataTable xlReader = XLSQL.ReturnTable("Select * from Emails where CRMID='" + crmID + "'and module='" + type + "' and deleted=0");
if (xlReader.Rows.Count != 0)
{
foreach (DataRow row in xlReader.Rows)
{
string crmid = row["crmid"].ToString();
string email = row["email"].ToString();
bool isPrimary = Convert.ToBoolean(row["isprimary"]);
bool doNotMail = Convert.ToBoolean(row["donotmail"]);
emails.Add(new EmailAddress(crmid, email, email, isPrimary, doNotMail));
}
}
return(emails);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-GetEmails", ex.ToString());
return(null);
}
}
public static List <Contact> Contacts(string id)
{
try
{
string type = DiscoverType(id);
List <Contact> contacts = new List <Contact>();
DataTable xlReader = XLSQL.ReturnTable("SELECT * from Connections('" + id + "', '" + type + "') where contacttype='Contact' and ISNULL(contactid,'')!=''");
if (xlReader.Rows.Count != 0)
{
foreach (DataRow row in xlReader.Rows)
{
Contact cont = Contact.FetchContact(row["contact_id"].ToString());
cont.relationship = row["reldesc"].ToString();
contacts.Add(cont);
}
}
contacts.Sort();
return(contacts);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-Contacts", ex.ToString());
return(null);
}
}
public static Staff FetchStaff(string iD)
{
try
{
Staff staff = new Staff();
DataTable xlReader = XLSQL.ReturnTable("Select * from Staff where crmID='" + iD + "'");
if (xlReader.Rows.Count == 1)
{
staff.crmID = xlReader.Rows[0]["crmid"].ToString();
staff.relationship = ""; //Only used in collections
staff.username = xlReader.Rows[0]["username"].ToString();
staff.name = xlReader.Rows[0]["fullname"].ToString();
staff.initials = xlReader.Rows[0]["initials"].ToString();
staff.department = xlReader.Rows[0]["department"].ToString();
staff.office = xlReader.Rows[0]["office"].ToString();
staff.grade = xlReader.Rows[0]["grade"].ToString();
staff.emails = EmailAddress.GetEmails(staff.crmID);
staff.addresses = Address.GetAddresses(staff.crmID);
staff.salutations = Salutation.GetSalutations(staff.crmID);
staff.numbers = Number.GetNumbers(staff.crmID);
}
return(staff);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-FetchStaff", ex.ToString());
return(null);
}
}
public static FPIClient GetFPIClientInvoice(Client client)
{
DataTable table = XLSQL.ReturnTable("select * from FPIInvoice('" + client.clientcode + "')");
FPIClient fpiClient = new FPIClient(table.Rows[0]);
return(fpiClient);
}
public static Organisation FetchOrganisation(string iD)
{
try
{
//Get contact data from Organisation
Organisation org = new Organisation();
DataTable xlReader = XLSQL.ReturnTable("Select * from Organisations where CRMID='" + iD + "'");
if (xlReader.Rows.Count == 1)
{
org.crmID = iD;
org.name = xlReader.Rows[0]["name"].ToString();
//Addresses
org.addresses = Address.GetAddresses(org.crmID);
//Numbers
org.numbers = Number.GetNumbers(org.crmID);
}
return(org);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-FetchOrganisation", ex.ToString());
return(null);
}
}
public static List <IndexList> SectionLists(string office, string department, string sectionValue)
{
try
{
string fileStore = FileStore(office, department);
DataTable xlReader = XLSQL.ReturnTable("SELECT Label, ListId, indexno FROM [XLant].[dbo].[VCSectionValuesView] where CabinetName='" + fileStore + "' and SectionValue='" + sectionValue + "' and label is not null order by Listid");
List <IndexList> lists = new List <IndexList>();
if (xlReader.Rows.Count != 0)
{
foreach (DataRow row in xlReader.Rows)
{
IndexList iList = new IndexList();
iList.name = row["Label"].ToString();
iList.index = row["IndexNo"].ToString();
DataTable listReader = XLSQL.ReturnTable("SELECT Value FROM [XLant].[dbo].[VCListsView] where ListId='" + row["ListId"].ToString() + "' order by Value");
List <string> list = new List <string>();
if (listReader.Rows.Count != 0)
{
foreach (DataRow r in listReader.Rows)
{
list.Add(r["Value"].ToString());
}
}
iList.items = list;
lists.Add(iList);
}
}
return(lists);
}
catch (Exception ex)
{
XLtools.LogException("XLVC-SectionLists", ex.ToString());
return(null);
}
}
public static List <Address> GetAddresses(string crmID)
{
try
{
string type = DiscoverType(crmID);
List <Address> addresses = new List <Address>();
DataTable xlReader = XLSQL.ReturnTable("Select * from [XLant].[dbo].[Addresses] ('" + crmID + "','" + type + "')");
if (xlReader.Rows.Count != 0)
{
foreach (DataRow row in xlReader.Rows)
{
addresses.Add(new Address(Convert.ToBoolean(row["IsPrimary"].ToString()), row["address1"].ToString(), row["address2"].ToString(), row["address3"].ToString(), row["address4"].ToString(), row["address5"].ToString(), row["postcode"].ToString()));
}
return(addresses);
}
else
{
return(null);
}
}
catch (Exception ex)
{
XLtools.LogException("XLMain-GetAddresses", ex.ToString());
return(null);
}
}
public static List <EntityCouplet> AllClients(bool activeOnly = true)
{
try
{
List <EntityCouplet> group = new List <EntityCouplet>();
string query = "SELECT clientcode, name from client";
if (activeOnly)
{
query = query + " where status in ('Active', 'New')";
}
query = query + " order by clientcode";
DataTable xlReader = XLSQL.ReturnTable(query);
if (xlReader.Rows.Count != 0)
{
foreach (DataRow row in xlReader.Rows)
{
group.Add(new EntityCouplet(row["clientcode"].ToString(), row["clientcode"].ToString() + " - " + row["name"].ToString()));
}
}
return(group);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-AllClients", ex.ToString());
return(null);
}
}
public static List <Client> Clients(string id)
{
try
{
string type = DiscoverType(id);
List <Client> group = new List <Client>();
Client tempClient = null;
DataTable xlReader = XLSQL.ReturnTable("SELECT * from Connections('" + id + "', '" + type + "') where contacttype='Client' and ISNULL(contactid,'')!=''");
if (xlReader.Rows.Count != 0)
{
foreach (DataRow row in xlReader.Rows)
{
tempClient = Client.FetchClient(row["crmid"].ToString());
tempClient.relationship = row["reldesc"].ToString();
group.Add(tempClient);
}
}
return(group);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-Clients", ex.ToString());
return(null);
}
}
public static Contact FetchContact(string iD)
{
try
{
Contact cont = new Contact();
//Get contact data from MainContact
DataTable xlReader = XLSQL.ReturnTable("Select * from Contact where CRMID='" + iD + "'");
if (xlReader.Rows.Count == 1)
{
cont.crmID = iD;
cont.firstname = xlReader.Rows[0]["first_name"].ToString();
cont.lastname = xlReader.Rows[0]["last_name"].ToString();
cont.relationship = ""; //only used in collections
cont.type = xlReader.Rows[0]["type"].ToString();
//see whether the title exists in the enum
if (Enum.IsDefined(typeof(Title), xlReader.Rows[0]["title"].ToString()))
{
cont.title = (Title)Enum.Parse(typeof(Title), xlReader.Rows[0]["title"].ToString(), true);
}
else
{
cont.title = (Title)Enum.Parse(typeof(Title), "Mr", true);
}
cont.position = xlReader.Rows[0]["position"].ToString();
}
//Organisation
cont.organisation = Organisation.GetOrganisation(cont.crmID);
//Addresses
cont.addresses = Address.GetAddresses(cont.crmID);
//Update the address block with the organisation name
if (cont.organisation != null)
{
foreach (Address add in cont.addresses)
{
add.addressBlock = cont.organisation.name + Environment.NewLine + add.addressBlock;
}
}
//Numbers
cont.numbers = Number.GetNumbers(cont.crmID);
//email
cont.emails = EmailAddress.GetEmails(cont.crmID);
//salutations
cont.salutations = Salutation.GetSalutations(cont.crmID);
//parameters
cont.parameters = null;
return(cont);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-FetchContact", ex.ToString());
return(null);
}
}
public static Contact FetchContact(string ID, string clientcrmid)
{
try
{
//Get client data from Client
Contact contact = new Contact();
DataTable xlReader = XLSQL.ReturnTable("Select * from IPSContact('" + clientcrmid + "') where ID='" + ID + "'");
if (xlReader.Rows.Count == 1)
{
contact.crmID = ID;
contact.name = xlReader.Rows[0]["name"].ToString();
contact.type = xlReader.Rows[0]["ctype"].ToString();
contact.address1 = xlReader.Rows[0]["address1"].ToString();
contact.address2 = xlReader.Rows[0]["address2"].ToString();
contact.address3 = xlReader.Rows[0]["address3"].ToString();
contact.address4 = xlReader.Rows[0]["address4"].ToString();
contact.address5 = xlReader.Rows[0]["address5"].ToString();
contact.postcode = xlReader.Rows[0]["postcode"].ToString();
if (contact.address1 != "")
{
contact.addressBlock = contact.address1 + Environment.NewLine;
}
if (contact.address2 != "")
{
contact.addressBlock += contact.address2 + Environment.NewLine;
}
if (contact.address3 != "")
{
contact.addressBlock += contact.address3 + Environment.NewLine;
}
if (contact.address4 != "")
{
contact.addressBlock += contact.address4 + Environment.NewLine;
}
if (contact.address5 != "")
{
contact.addressBlock += contact.address5 + Environment.NewLine;
}
if (contact.postcode != "")
{
contact.addressBlock += contact.postcode;
}
contact.fax = xlReader.Rows[0]["fax"].ToString();;
}
return(contact);
}
catch (Exception ex)
{
XLtools.LogException("Insol-FetchContact", ex.ToString());
return(null);
}
}
public static Client FetchClientFromCode(string clientCode)
{
try
{
Client client = new Client();
DataTable xlReader = XLSQL.ReturnTable("Select crmid from Client where clientcode='" + clientCode + "'");
client = FetchClient(xlReader.Rows[0]["crmid"].ToString());
return(client);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-FetchClientfromcode", ex.ToString());
return(null);
}
}
public static string GetSubject(string crmiID)
{
try
{
Contact contact = new Contact();
string subject = "";
DataTable xlReader = XLSQL.ReturnTable("SELECT [XLant].[dbo].[Insol_Subject] ('" + crmiID + "') as subject");
if (xlReader.Rows.Count == 1)
{
subject = xlReader.Rows[0]["subject"].ToString();;
}
return(subject);
}
catch (Exception ex)
{
XLtools.LogException("Insol-GetSubject", ex.ToString());
return(null);
}
}
public static Staff GetStaff(string grade, string iD)
{
try
{
Staff staff = new Staff();
string type = DiscoverType(iD);
DataTable xlReader = XLSQL.ReturnTable("SELECT TOP 1 * from Connections('" + iD + "', '" + type + "') where contacttype='Staff' and reldesc = '" + grade + "' and ISNULL(contactid,'')!=''");
if (xlReader.Rows.Count == 1)
{
staff = FetchStaff(xlReader.Rows[0]["contactid"].ToString());
}
return(staff);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-GetStaff", ex.ToString());
return(null);
}
}
public bool SavetoDb()
{
try
{
string query = "set dateformat dmy; INSERT INTO [XLant].[dbo].[Errors]([date],[user],[machine],[command],[error]) Values (@date, @user, @machine, @command, @errorMessage)";
List <SqlParameter> parameterCollection = new List <SqlParameter>();
parameterCollection.Add(new SqlParameter("date", Date.ToShortDateString()));
parameterCollection.Add(new SqlParameter("user", User));
parameterCollection.Add(new SqlParameter("machine", Machine));
parameterCollection.Add(new SqlParameter("command", Command));
parameterCollection.Add(new SqlParameter("errorMessage", ErrorMessage));
bool result = XLSQL.RunCommand(query, parameterCollection);
return(result);
}
catch
{
return(false);
//no error handling we would just create a cycle
}
}
//public DateTime appdate { get; set; }
public static KeyData FetchKeyData(string ID)
{
try
{
KeyData data = new KeyData();
DataTable xlReader = XLSQL.ReturnTable("Select * from insol where crmID='" + ID + "'");
if (xlReader.Rows.Count == 1)
{
//data.appdate = Convert.ToDateTime(xlReader["appdate"].ToString()); //DateTime.Parse(xlReader.NiceString("appdate"));
data.caseType = xlReader.Rows[0]["type"].ToString();
data.sign = xlReader.Rows[0]["sign"].ToString();
data.title = xlReader.Rows[0]["app"].ToString();
}
return(data);
}
catch (Exception ex)
{
XLtools.LogException("Insol-FetchKeyData", ex.ToString());
return(null);
}
}
public static Number GetNumber(string crmID, string ndesc)
{
try
{
string type = DiscoverType(crmID);
DataTable xlReader = XLSQL.ReturnTable("SELECT Top 1 * FROM [XLant].[dbo].[Numbers] ('" + crmID + "','" + type + "') where ndesc='" + ndesc + "' and ISNULL(number,'')!='' order by isPrimary desc");
if (xlReader.Rows.Count == 1)
{
Number number = new Number(xlReader.Rows[0]["ndesc"].ToString(), xlReader.Rows[0]["number"].ToString(), Convert.ToBoolean(xlReader.Rows[0]["isPrimary"].ToString()));
return(number);
}
else
{
return(null);
}
}
catch (Exception ex)
{
XLtools.LogException("XLMain-GetNumber", ex.ToString());
return(null);
}
}
public static List <Number> GetNumbers(string crmID)
{
try
{
string type = DiscoverType(crmID);
List <Number> numbers = new List <Number>();
DataTable xlReader = XLSQL.ReturnTable("SELECT * FROM [XLant].[dbo].[Numbers] ('" + crmID + "','" + type + "') where ISNULL(number,'')!=''");
if (xlReader.Rows.Count != 0)
{
foreach (DataRow row in xlReader.Rows)
{
numbers.Add(new Number(row["ndesc"].ToString(), row["number"].ToString(), Convert.ToBoolean(row["isPrimary"].ToString())));
}
}
return(numbers);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-GetNumbers", ex.ToString());
return(null);
}
}
public static List <string> SectionValues(string office, string department)
{
try
{
List <string> sections = new List <string>();
string fileStore = FileStore(office, department);
DataTable xlReader = XLSQL.ReturnTable("SELECT Distinct SectionValue FROM [XLant].[dbo].[VCSectionValuesView] where CabinetName='" + fileStore + "' order by SectionValue");
if (xlReader.Rows.Count != 0)
{
foreach (DataRow row in xlReader.Rows)
{
sections.Add(row["SectionValue"].ToString());
}
}
return(sections);
}
catch (Exception ex)
{
XLtools.LogException("XLVC-SectionValues", ex.ToString());
return(null);
}
}
/// <summary>
/// Returns the FPI clients for a manager
/// </summary>
/// <param name="managerCRMId">THe Id of the manager</param>
/// <param name="additionalQuery">Allows you to filter the list at the sql end format "where x = y"</param>
/// <returns>The populated list of clients</returns>
public static List <FPIClient> GetFPIClients(string managerCRMId, string additionalQuery = null, bool domestic = true)
{
List <FPIClient> list = new List <FPIClient>();
Staff manager = Staff.FetchStaff(managerCRMId);
string query = "SELECT * FROM [dbo].[FPIManager] ('" + manager.name + "')";
if (additionalQuery != null)
{
query += " " + additionalQuery;
}
if (query.Contains("where"))
{
query += " and";
}
else
{
query += " where";
}
if (domestic)
{
query += " (isnull(add4,'') = '' or add4 in ('UK', 'United Kingdom', 'England', 'Wales', 'Scotland', 'Northern Ireland', 'GB', 'NULL', ' '))";
}
else
{
query += " (isnull(add4,'') != '' and add4 not in ('UK', 'United Kingdom', 'England', 'Wales', 'Scotland', 'Northern Ireland', 'GB', 'NULL', ' '))";
}
DataTable xlReader = XLSQL.ReturnTable(query);
if (xlReader != null)
{
for (int i = 0; i < xlReader.Rows.Count; i++)
{
FPIClient client = new FPIClient(xlReader.Rows[i]);
list.Add(client);
}
}
return(list);
}
public static Organisation GetOrganisation(string contactID)
{
try
{
Organisation org = new Organisation();
//Get contact data from Organisation
DataTable xlReader = XLSQL.ReturnTable("SELECT * from Connections('" + contactID + "', 'Contact') where contacttype='Organisation' and ISNULL(contactid,'')!=''");
if (xlReader.Rows.Count == 1)
{
org = Organisation.FetchOrganisation(xlReader.Rows[0]["contactID"].ToString());
}
else
{
org = null;
}
return(org);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-FetchOrganisation", ex.ToString());
return(null);
}
}
public static List <Salutation> GetSalutations(string crmID)
{
try
{
string type = DiscoverType(crmID);
List <Salutation> salutations = new List <Salutation>();
DataTable xlReader = XLSQL.ReturnTable("Select * from [XLant].[dbo].[Salutations] ('" + crmID + "','" + type + "') where ISNULL(salutation,'')!=''");
if (xlReader.Rows.Count != 0)
{
foreach (DataRow row in xlReader.Rows)
{
salutations.Add(new Salutation("", row["desc"].ToString(), row["addressee"].ToString(), row["Salutation"].ToString(), Convert.ToBoolean(row["isprimary"].ToString())));
}
}
return(salutations);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-GetSalutations", ex.ToString());
return(null);
}
}
public static Staff StaffFromUser(string username)
{
try
{
Staff staff = new Staff();
if (@username.Contains("\\"))
{
//Contains domain so take everything after the \ doubled to handle escaping
username = username.Substring(@username.LastIndexOf("\\"));
}
DataTable xlReader = XLSQL.ReturnTable("Select Top 1 * from Staff where username='******'");
if (xlReader.Rows.Count == 1)
{
staff = FetchStaff(xlReader.Rows[0]["crmid"].ToString());
}
return(staff);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-FetchStaffFromUser", ex.ToString());
return(null);
}
}
public static List <EntityCouplet> AllStaff()
{
try
{
List <EntityCouplet> staff = new List <EntityCouplet>();
DataTable xlReader = XLSQL.ReturnTable("SELECT fullname, crmid from VCStaffView order by fullname");
if (xlReader.Rows.Count != 0)
{
foreach (DataRow row in xlReader.Rows)
{
EntityCouplet tempStaff = new EntityCouplet();
tempStaff.crmID = row["crmid"].ToString();
tempStaff.name = row["fullname"].ToString();
staff.Add(tempStaff);
}
}
return(staff);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-Allstaff", ex.ToString());
return(null);
}
}
public static List <Staff> connectedStaff(string id)
{
try
{
string type = DiscoverType(id);
List <Staff> staff = new List <Staff>();
DataTable xlReader = XLSQL.ReturnTable("SELECT * from Connections('" + id + "', '" + type + "') where contacttype='Staff' and ISNULL(contactid,'')!=''");
if (xlReader.Rows.Count != 0)
{
foreach (DataRow row in xlReader.Rows)
{
Staff emp = FetchStaff(row["contactid"].ToString());
emp.relationship = row["reldesc"].ToString();
staff.Add(emp);
}
}
return(staff);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-ConnectedStaff", ex.ToString());
return(null);
}
}
public static FileInfo FileIndex(string fileID, bool current = true)
{
try
{
FileInfo file = new FileInfo();
if (fileID != null)
{
DataTable xlReader = new DataTable();
if (current)
{
xlReader = XLSQL.ReturnTable("Select TOP(1) * from [XLant].[dbo].[VCFileIndexView] where fileID = '" + fileID + "'");
}
else
{
//if not current, returns the last set of audit data
xlReader = XLSQL.ReturnTable("select TOP(1)* from VCAuditLog where fileid = '" + fileID + "' and index01 is null order by auditId desc");
}
string statusIndex = "";
string toBeIndex = "";
XDocument settingsDoc = XLtools.settingsDoc;
IEnumerable <XElement> xIndexes = settingsDoc.Descendants("Indexes");
foreach (XElement xIndex in xIndexes.Descendants("Index"))
{
if (xIndex.AttributeValueNull("Type") == "Status")
{
statusIndex = xIndex.ElementValueNull();
}
else if (xIndex.AttributeValueNull("Type") == "ToBe")
{
toBeIndex = xIndex.ElementValueNull();
}
}
file.FileID = fileID;
file.Cabinet = xlReader.Rows[0]["FolderName"].ToString();
file.ToBeActionedBy = xlReader.Rows[0]["INDEX" + toBeIndex].ToString();
file.Status = xlReader.Rows[0]["INDEX" + statusIndex].ToString();
file.Extension = xlReader.Rows[0]["Extension"].ToString();
string indexNumber = "";
List <IndexPair> list = new List <IndexPair>();
for (int i = 1; i < 51; i++)
{
//add the 0 where required. Index09 is the section name and is handled differently
try
{
if (i < 9)
{
indexNumber = "INDEX0" + i.ToString();
}
else if (i == 9)
{
IndexPair index = new IndexPair();
index.index = "INDEX09";
index.value = xlReader.Rows[0]["SectionName"].ToString();
list.Add(index);
}
else
{
indexNumber = "INDEX" + i.ToString();
}
string value = xlReader.Rows[0][indexNumber].ToString();
if (!String.IsNullOrEmpty(value))
{
IndexPair index = new IndexPair();
index.index = indexNumber;
index.value = value;
list.Add(index);
}
}
catch
{
continue;
}
}
file.Indexes = list;
foreach (IndexPair i in file.Indexes)
{
//grab the client string and code
if (i.index == "INDEX02")
{
file.ClientString = i.value;
file.ClientCode = i.value.Substring(0, i.value.IndexOf("-") - 1);
}
//and the description
if (i.index == "INDEX03")
{
file.Description = i.value;
}
}
}
return(file);
}
catch (Exception ex)
{
XLtools.LogException("XLVC-FileIndex", ex.ToString());
return(null);
}
}
public static List <FileInfo> GetToDos(string userName)
{
try
{
//Run query against database
List <FileInfo> newToDos = new List <FileInfo>();
string description = "";
string status = "";
string toBeIndex = "";
//discover the to be actioned by index
XDocument settingsDoc = XLtools.settingsDoc;
//query the setting files and try to find a match
XElement setting = (from index in settingsDoc.Descendants("Indexes")
select index).FirstOrDefault();
foreach (XElement xIndex in setting.Descendants("Index"))
{
if (xIndex.AttributeValueNull("Type") == "ToBe")
{
toBeIndex = xIndex.Value;
}
if (xIndex.AttributeValueNull("Type") == "Description")
{
description = xIndex.Value;
}
if (xIndex.AttributeValueNull("Type") == "Status")
{
status = xIndex.Value;
}
}
string str = "select * from VCFileIndexView where index" + toBeIndex + " ='" + userName + "'";
DataTable xlReader = XLSQL.ReturnTable(str);
if (xlReader.Rows.Count != 0)
{
foreach (DataRow row in xlReader.Rows)
{
FileInfo file = new FileInfo();
file = FileIndex(row["FileId"].ToString());
//file.FileID = row["FileId"].ToString();
//file.Cabinet = row["FolderName"].ToString();
//file.EntryDate = DateTime.Parse(row["EntryDate"].ToString());
//string indexNumber = "";
//List<IndexPair> list = new List<IndexPair>();
//for (int i = 1; i < 51; i++)
//{
// //add the 0 where required. Index09 is the section name and is handled differently
// try
// {
// if (i == int.Parse(description))
// {
// file.Description = xlReader.Rows[0][indexNumber].ToString();
// }
// if (i == int.Parse(status))
// {
// file.Status = xlReader.Rows[0][indexNumber].ToString();
// }
// if (i == int.Parse(toBeIndex))
// {
// file.ToBeActionedBy = xlReader.Rows[0][indexNumber].ToString();
// }
// if (i == 2)
// {
// file.ClientString = xlReader.Rows[0][indexNumber].ToString();
// }
// if (i < 9)
// {
// indexNumber = "INDEX0" + i.ToString();
// }
// else if (i == 9)
// {
// IndexPair index = new IndexPair();
// index.index = "INDEX09";
// index.value = xlReader.Rows[0]["SectionName"].ToString();
// list.Add(index);
// }
// else
// {
// indexNumber = "Index" + i.ToString();
// }
// string value = xlReader.Rows[0][indexNumber].ToString();
// if (!String.IsNullOrEmpty(value))
// {
// IndexPair index = new IndexPair();
// index.index = indexNumber;
// index.value = value;
// list.Add(index);
// }
// }
// catch
// {
// continue;
// }
//}
//file.Indexes = list;
//string clientStr = file.ClientString;
//clientStr = clientStr.Substring(0, clientStr.IndexOf("-"));
//file.ClientCode = clientStr.TrimEnd();
newToDos.Add(file);
}
}
return(newToDos);
}
catch (Exception e)
{
XLtools.LogException("XLant Monitor", e.ToString());
return(null);
}
}
public static Client FetchClient(string ID)
{
try
{
if (String.IsNullOrEmpty(ID))
{
return(null);
}
//Get client data from Client
Client client = new Client();
//SqlDataReader xlReader = XLSQL.ReaderQuery("Select * from Client where CRMID='" + ID + "'");
DataTable xlReader = XLSQL.ReturnTable("Select Top 1 * from Client where CRMID='" + ID + "'");
//if we get more than one result we have a problem
if (xlReader.Rows.Count == 1)
{
client.crmID = ID;
client.clientcode = xlReader.Rows[0]["clientcode"].ToString();
client.name = xlReader.Rows[0]["name"].ToString();
client.relationship = "";//only used in collections
client.type = xlReader.Rows[0]["type"].ToString();
client.status = xlReader.Rows[0]["status"].ToString();
if (client.status.ToUpper() == "ACTIVE" || client.status.ToUpper() == "NEW")
{
client.isLive = true;
}
else
{
client.isLive = false;
}
client.department = xlReader.Rows[0]["department"].ToString();
client.office = xlReader.Rows[0]["office"].ToString();
client.partner = Staff.GetStaff("Partner", client.crmID);
client.manager = Staff.GetStaff("Manager", client.crmID);
if (client.type == "Ltd" || client.type == "Plc" || client.type == "LLP" || client.type == "PartnerS" || client.type == "Charity")
{
client.IsIndividual = false;
}
else
{
client.IsIndividual = true;
}
}
client.wip = 0;
client.debtor = 0;
//}
//Addresses
client.addresses = Address.GetAddresses(client.crmID);
//Numbers
client.numbers = Number.GetNumbers(client.crmID);
//email
client.emails = EmailAddress.GetEmails(client.crmID);
//salutations
client.salutations = Salutation.GetSalutations(client.crmID);
//parameters
client.parameters = null;
//connections
return(client);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-FetchClient", ex.ToString());
return(null);
}
}
public static string DiscoverType(string crmID)
{
try
{
string type = null;
//first find out whether it is a client
DataTable xlReader = XLSQL.ReturnTable("Select count(crmID) as number from Client where crmID='" + crmID + "'");
if (xlReader.Rows.Count != 0)
{
if (Convert.ToInt16(xlReader.Rows[0]["number"].ToString()) != 0)
{
type = "Client";
}
}
if (type == null)
{
//contact
xlReader = XLSQL.ReturnTable("Select count(crmID) as number from Contact where crmID='" + crmID + "'");
if (xlReader.Rows.Count != 0)
{
if (Convert.ToInt16(xlReader.Rows[0]["number"].ToString()) != 0)
{
type = "Contact";
}
}
}
if (type == null)
{
//staff
xlReader = XLSQL.ReturnTable("Select count(crmID) as number from Staff where crmID='" + crmID + "'");
if (xlReader.Rows.Count != 0)
{
if (Convert.ToInt16(xlReader.Rows[0]["number"].ToString()) != 0)
{
type = "Staff";
}
}
}
if (type == null)
{
//organisation
xlReader = XLSQL.ReturnTable("Select count(crmID) as number from Organisations where crmID='" + crmID + "'");
if (xlReader.Rows.Count != 0)
{
if (Convert.ToInt16(xlReader.Rows[0]["number"].ToString()) != 0)
{
type = "Organisation";
}
}
}
if (type == null)
{
type = "Unknown";
}
return(type);
}
catch (Exception ex)
{
XLtools.LogException("XLMain-DiscoverType", ex.ToString());
return(null);
}
}