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