public static SoapDevice FetchSoapDevice(int device_id)
{
SoapDevice device = new SoapDevice();
MySqlConnection con = getNewConnection();
string sql = "SELECT * FROM mobile_devices WHERE id = " + device_id;
con.Open();
MySqlCommand cmd = new MySqlCommand(sql, con);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
device.Id = device_id;
device.Name = (string)reader["model_name"];
if (device.Name == null)
{
return(null);
}
device.Company = FetchCompanyName(device_id);
device.SpecShape = (string)reader["spec_shape"];
device.SpecOs = (string)reader["spec_os"];
device.SpecConnectivity = (string)reader["spec_connectivity"];
device.SpecDisplay = (string)reader["spec_display"];
device.SpecInterface = (string)reader["spec_interface"];
device.SpecCamera = (string)reader["spec_camera"];
device.SpecMemory = (string)reader["spec_memory"];
device.SpecBattery = (string)reader["spec_battery"];
device.Available = (int)reader["available"];
device.Price = (double)reader["price"];
device.Picture = Global.BaseUrl + "Pictures/" + FetchImage(device_id);
}
reader.Close();
con.Close();
return(device);
}
public SoapDevice GetDevice(int id)
{
SoapDevice device = DB.FetchSoapDevice(id);
return(device);
}
public List<SoapDevice> Search(string company, string model, string os, string price, string minmax, int startItem, int numOfItems)
{
List<SoapDevice> devices = new List<SoapDevice>();
MySqlConnection con = DB.getNewConnection();
con.Open();
string sql = "SELECT * FROM mobile_devices m";
int searchParams = 0;
// search model
if (!string.IsNullOrEmpty(model))
{
if (0 == searchParams++) sql += " WHERE";
else sql += " AND";
sql += " model_name LIKE '%" + model + "%'";
}
// search os
if (!string.IsNullOrEmpty(os))
{
if (0 == searchParams++) sql += " WHERE";
else sql += " AND";
sql += " spec_os LIKE '%" + os + "%'";
}
// search price
if (!string.IsNullOrEmpty(price))
{
if (0 == searchParams++) sql += " WHERE";
else sql += " AND";
if (minmax.Equals("min")) sql += " price >= " + price;
else sql += " price <= " + price;
}
// search company
if (!string.IsNullOrEmpty(company))
{
if (0 == searchParams++) sql += " WHERE";
else sql += " AND";
sql += " company_id IN (SELECT id FROM companies WHERE name LIKE '%" + company + "%')";
}
MySqlCommand cmd = new MySqlCommand(sql, con);
MySqlDataReader reader = cmd.ExecuteReader();
int currentItem = 0;
while (reader.Read())
{
if (currentItem < startItem) { currentItem++; continue; }
if (currentItem >= startItem + numOfItems) { currentItem++; break; }
currentItem++;
SoapDevice device = new SoapDevice();
device.Id = (int)reader["id"];
device.Name = (string)reader["model_name"];
device.Company = DB.FetchCompanyName((int)reader["company_id"]);
device.SpecOs = (string)reader["spec_os"];
device.Price = (double)reader["price"];
devices.Add(device);
}
reader.Close();
con.Close();
return devices;
}
public List <SoapDevice> Search(string company, string model, string os, string price, string minmax, int startItem, int numOfItems)
{
List <SoapDevice> devices = new List <SoapDevice>();
MySqlConnection con = DB.getNewConnection();
con.Open();
string sql = "SELECT * FROM mobile_devices m";
int searchParams = 0;
// search model
if (!string.IsNullOrEmpty(model))
{
if (0 == searchParams++)
{
sql += " WHERE";
}
else
{
sql += " AND";
}
sql += " model_name LIKE '%" + model + "%'";
}
// search os
if (!string.IsNullOrEmpty(os))
{
if (0 == searchParams++)
{
sql += " WHERE";
}
else
{
sql += " AND";
}
sql += " spec_os LIKE '%" + os + "%'";
}
// search price
if (!string.IsNullOrEmpty(price))
{
if (0 == searchParams++)
{
sql += " WHERE";
}
else
{
sql += " AND";
}
if (minmax.Equals("min"))
{
sql += " price >= " + price;
}
else
{
sql += " price <= " + price;
}
}
// search company
if (!string.IsNullOrEmpty(company))
{
if (0 == searchParams++)
{
sql += " WHERE";
}
else
{
sql += " AND";
}
sql += " company_id IN (SELECT id FROM companies WHERE name LIKE '%" + company + "%')";
}
MySqlCommand cmd = new MySqlCommand(sql, con);
MySqlDataReader reader = cmd.ExecuteReader();
int currentItem = 0;
while (reader.Read())
{
if (currentItem < startItem)
{
currentItem++; continue;
}
if (currentItem >= startItem + numOfItems)
{
currentItem++; break;
}
currentItem++;
SoapDevice device = new SoapDevice();
device.Id = (int)reader["id"];
device.Name = (string)reader["model_name"];
device.Company = DB.FetchCompanyName((int)reader["company_id"]);
device.SpecOs = (string)reader["spec_os"];
device.Price = (double)reader["price"];
devices.Add(device);
}
reader.Close();
con.Close();
return(devices);
}