Example #1
0
 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);
     }
 }
Example #2
0
 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);
     }
 }
Example #3
0
 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);
     }
 }
Example #4
0
            public static FPIClient GetFPIClientInvoice(Client client)
            {
                DataTable table     = XLSQL.ReturnTable("select * from FPIInvoice('" + client.clientcode + "')");
                FPIClient fpiClient = new FPIClient(table.Rows[0]);

                return(fpiClient);
            }
Example #5
0
            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);
     }
 }
Example #7
0
 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);
     }
 }
Example #8
0
 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);
     }
 }
Example #9
0
 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);
     }
 }
Example #10
0
 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);
     }
 }
Example #11
0
 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);
     }
 }
Example #12
0
 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);
     }
 }
Example #13
0
 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);
     }
 }
Example #14
0
 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);
     }
 }
Example #15
0
 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
     }
 }
Example #16
0
            //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);
                }
            }
Example #17
0
 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);
     }
 }
Example #18
0
 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);
     }
 }
Example #20
0
            /// <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);
            }
Example #21
0
 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);
     }
 }
Example #22
0
            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);
                }
            }
Example #23
0
 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);
     }
 }
Example #24
0
 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);
     }
 }
Example #25
0
 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);
            }
        }
Example #28
0
 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);
     }
 }
Example #29
0
        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);
            }
        }