//Get Person Details
public List <PersonDetails> getDetails(string hash, string id)
{
List <PersonDetails> person_details = new List <PersonDetails>();
try
{
if (Account_BL.ifLogin(hash))
{
int personID = Tools.convertTonumber(id);
dal.connection.Open();
string query = "SELECT * FROM persons WHERE id=" + personID;
SqlDataReader dr = dal.Command(query).ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
PersonDetails person = new PersonDetails();
person.id = (int)dr["id"];
person.first_name = dr["first_name"].ToString();
person.last_name = dr["last_name"].ToString();
person.phone = (int)dr["phone"];
person.email = dr["email"].ToString();
person.birthday = Convert.ToDateTime(dr["birthday"]).ToString("yyyy-MM-dd");
person.address = dr["address"].ToString();
person_details.Add(person);
}
dal.connection.Close();
}
}
catch (Exception)
{ }
return(person_details);
}
//Get Regions
public List <Region> getRegions(string hash, string city)
{
List <Region> regions = new List <Region>();
try
{
if (Account_BL.ifLogin(hash))
{
int city_id = Tools.convertTonumber(city);
string where = "";
if (city_id != 0)
{
where = " WHERE city_id=" + city_id;
}
dal.connection.Open();
string query = "SELECT * FROM regions" + where;
SqlDataReader dr = dal.Command(query).ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
Region region = new Region();
region.id = (int)dr["id"];
region.name = dr["name"].ToString();
region.city_id = (int)dr["city_id"];
regions.Add(region);
}
dal.connection.Close();
}
}
catch (Exception)
{ }
return(regions);
}
//Get Cities
public List <City> getCities(string hash, string country)
{
List <City> cities = new List <City>();
try
{
if (Account_BL.ifLogin(hash))
{
int country_id = Tools.convertTonumber(country);
string where = "";
if (country_id != 0)
{
where = " WHERE country_id=" + country_id;
}
dal.connection.Open();
string query = "SELECT * FROM cities" + where;
SqlDataReader dr = dal.Command(query).ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
City city = new City();
city.id = (int)dr["id"];
city.name = dr["name"].ToString();
city.country_id = (int)dr["country_id"];
cities.Add(city);
}
dal.connection.Close();
}
}
catch (Exception)
{ }
return(cities);
}
//Get Counteries
public List <Country> getCountries(string hash)
{
List <Country> countries = new List <Country>();
try
{
if (Account_BL.ifLogin(hash))
{
dal.connection.Open();
string query = "SELECT * FROM countries";
SqlDataReader dr = dal.Command(query).ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
Country country = new Country();
country.id = (int)dr["id"];
country.name = dr["name"].ToString();
countries.Add(country);
}
dal.connection.Close();
}
}
catch (Exception) { }
return(countries);
}
//Get persons
public List <Person> getPersons(string hash, string country, string city, string region, string order_by, string order_type)
{
List <Person> person_list = new List <Person>();
try
{
if (Account_BL.ifLogin(hash))
{
string where = "";
int country_id = Tools.convertTonumber(country);
int city_id = Tools.convertTonumber(city);
int region_id = Tools.convertTonumber(region);
int orderby = Tools.convertTonumber(order_by);
int ordertype = Tools.convertTonumber(order_type);
string sort_by = "";
string sort_type = "";
if (ordertype == 1)
{
sort_type = " DESC";
}
else
{
sort_type = " ASC";
}
switch (orderby)
{
case 2: sort_by = "first_name"; break;
case 3: sort_by = "last_name"; break;
default: sort_by = "id"; break;
}
if (country_id != 0)
{
where = " WHERE id in(SELECT person_id FROM persons_places WHERE country_id=" + country_id;
}
if (city_id != 0)
{
where += " AND city_id=" + city_id;
}
if (region_id != 0)
{
where += " AND region_id=" + region_id;
}
if (country_id != 0 || city_id != 0 || region_id != 0)
{
where += ")";
}
dal.connection.Open();
string query = "SELECT id,first_name,last_name FROM persons" + where + " ORDER BY " + sort_by + sort_type;
SqlDataReader dr = dal.Command(query).ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
Person person = new Person();
person.id = (int)dr["id"];
person.first_name = dr["first_name"].ToString();
person.last_name = dr["last_name"].ToString();
person_list.Add(person);
}
dal.connection.Close();
}
}
catch (Exception) { }
return(person_list);
}
