public List <string> GetPoolCountries(string parameter, List <Classes.Country> listCountries = null)
{
List <string> countries = new List <string>();
List <string> scountries = new List <string>();
List <string> fcountries = new List <string>();
string query = $"SELECT DISTINCT countries FROM sales.pools WHERE countries IS NOT NULL AND countries != '' {parameter}";
using (var conn = DBconnections.CRM("CRM.Utilities.PoolUtilities", "GetPoolCountByQuery"))
{
DataTable dt = DBqueries.Select(conn, query);
countries = dt.AsEnumerable().Select(x => x.Field <string>("countries")).ToList();
if (listCountries != null)
{
foreach (var cy in countries)
{
listCountries.ForEach((lc) => {
if (cy.Contains(lc.Name) && fcountries.Find(sc => sc == lc.Name) == null)
{
fcountries.Add(lc.Name);
}
});
}
}
else
{
foreach (var cy in countries)
{
if (cy.Contains(','))
{
scountries = cy.Split(',').ToList();
fcountries.AddRange(scountries);
}
else
{
fcountries.Add(cy);
}
}
}
}
return(fcountries);
}
public static Pool GetPoolById(int id)
{
Pool pool = null;
List <Classes.Country> listCountries = Classes.countries.ReturnListOfCountries();
using (NpgsqlConnection conn = DBconnections.CRM("CRM.Utilities.PoolUtilities", ""))
{
string q = "SELECT * FROM sales.pools WHERE id =" + id;
DataTable mtgres = Classes.BrandsMaster.DBqueries.Select(conn, q);
if (mtgres.Rows.Count > 0)
{
var r = mtgres.Rows[0];
var ia = r.ItemArray;
return(new Pool()
{
id = (int)r.ItemArray[0],
name = (string)r.ItemArray[1],
referrals = !string.IsNullOrEmpty(r.ItemArray[2].ToString()) ? (string)r.ItemArray[2].ToString().Replace("#", "") : "",
Countries = !string.IsNullOrEmpty(r.ItemArray[3].ToString()) ? Classes.BrandsMaster.CRM.Utilities.GetCountriesfromString((string)r.ItemArray[3], listCountries) : null,
mt_groups = !string.IsNullOrEmpty(r.ItemArray[4].ToString()) ? Classes.BrandsMaster.CRM.Utilities.GetMTGroupFromString((string)r.ItemArray[4]) : null,
AutoConvertToRetention = (bool)r.ItemArray[6],
AutoContractsClose = (bool)r.ItemArray[7],
MT_Login_Start = (int)r.ItemArray[8],
MT_Login_End = (int)r.ItemArray[9],
max_withdrawal = !string.IsNullOrEmpty(r.ItemArray[10].ToString()) ? (int?)r.ItemArray[10] : null,
is_enable = (bool)r.ItemArray[11],
agent = !string.IsNullOrEmpty(r.ItemArray[12].ToString()) ? (string)r.ItemArray[12] : null,
status = !string.IsNullOrEmpty(r.ItemArray[13].ToString()) ? (string)r.ItemArray[13] : null,
});
}
}
return(null);
}