public List <Reg> GetRegs(Reg selectParameters)
{
string where = "";
if (selectParameters.Lname != null)
{
where += $@" UPPER(r.lname) LIKE '{selectParameters.Lname.ToUpper()}%'";
}
if (selectParameters.Fname != null)
{
if (where.Length > 0)
{
where += " AND";
}
where += $@" UPPER(r.fname) LIKE '{selectParameters.Fname.ToUpper()}%'";
}
if (selectParameters.Pname != null)
{
if (where.Length > 0)
{
where += " AND";
}
where += $@" UPPER(r.pname) LIKE '{selectParameters.Pname.ToUpper()}%'";
}
if (selectParameters.Country != null)
{
if (where.Length > 0)
{
where += " AND";
}
where += $@" UPPER(r.country) LIKE '%{selectParameters.Country.ToUpper()}%'";
}
if (selectParameters.City != null)
{
if (where.Length > 0)
{
where += " AND";
}
where += $@" UPPER(r.city) LIKE '%{selectParameters.City.ToUpper()}%'";
}
if (selectParameters.Phone != null)
{
if (where.Length > 0)
{
where += " AND";
}
where += $@" REPLACE(REPLACE(REPLACE(r.phone,'-',''),'(',''),')','') LIKE '%{selectParameters.Phone.ToUpper()}%'";
}
if (selectParameters.StayWhere != null)
{
if (where.Length > 0)
{
where += " AND";
}
where += $@" sw.name LIKE '{selectParameters.StayWhere}'";
}
if (selectParameters.StayLength != null)
{
if (where.Length > 0)
{
where += " AND";
}
where += $@" sl.name LIKE '{selectParameters.StayLength}'";
}
if (where.Length > 0)
{
where = " WHERE " + where;
}
List <Reg> regs = new List <Reg>();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = connect;
cmd.CommandText = $@"SELECT r.id, r.numcard, r.lname, r.fname, r.pname, r.phone, r.birthday, sw.id, sw.name, sl.id, sl.name, r.country, r.city, n.id, n.name" +
" FROM reg r" +
" LEFT JOIN stay_where sw ON r.stay_where_id = sw.id" +
" LEFT JOIN stay_length sl ON r.stay_length_id = sl.id" +
" LEFT JOIN nutrition n ON r.nutrition_id = n.id" +
where +
" ORDER BY r.id";
using (DbDataReader reader = cmd.ExecuteReader()) {
if (reader.HasRows)
{
while (reader.Read())
{
Reg reg = new Reg()
{
Id = reader.GetInt32(0),
Numcard = reader.GetValue(1).ToString(),
Lname = reader.GetValue(2).ToString(),
Fname = reader.GetValue(3).ToString(),
Pname = reader.GetValue(4).ToString(),
Phone = reader.GetValue(5).ToString(),
Birthday = reader.GetValue(6) != DBNull.Value ? reader.GetDateTime(6) : (DateTime?)null,
Age = reader.GetValue(6) != DBNull.Value ? Reg.GetAge(reader.GetDateTime(6)) : null,
StayWhereId = reader.GetValue(7) != DBNull.Value ? reader.GetInt32(7) : 0,
StayWhere = reader.GetValue(8).ToString(),
StayLengthId = reader.GetValue(9) != DBNull.Value ? reader.GetInt32(7) : 0,
StayLength = reader.GetValue(10).ToString(),
Country = reader.GetValue(11).ToString(),
City = reader.GetValue(12).ToString(),
NutritionId = reader.GetValue(13) != DBNull.Value ? reader.GetInt32(7) : 0,
Nutrition = reader.GetValue(14).ToString(),
};
regs.Add(reg);
}
}
}
return(regs);
}
