public Customer Select(int id)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_SELECT_BY_ID);
command.Parameters.Add(new SqlParameter("@p_id", SqlDbType.Int));
command.Parameters["@p_id"].Value = id;
SqlDataReader reader = db.Select(command);
try
{
reader.Read();
Customer cust = new Customer();
cust.id = reader.GetInt32(0);
cust.email = reader.GetString(1);
cust.fname = reader.GetString(2);
cust.lname = reader.GetString(3);
cust.phone = reader.GetString(4);
cust.adress = reader.GetString(5);
cust.city = reader.GetString(6);
cust.postaCode = reader.GetInt32(7);
reader.Close();
db.Close();
return cust;
}
catch
{
return null;
}
}
public int Insert(StoredWheels st)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_INSERT);
PrepareCommand(command, st);
int ret = db.ExecuteNonQuery(command);
db.Close();
return ret;
}
public int Insert(OkPneuTire tire)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_INSERT);
PrepareCommand(command, tire);
int ret = db.ExecuteNonQuery(command);
db.Close();
return ret;
}
//public int Delete(int id)
//{
// Database db = new Database(connectionString);
// db.Connect();
// SqlCommand command = db.CreateCommand(SQL_DELETE);
// command.Parameters.Add(new SqlParameter("@p_id", SqlDbType.Int));
// command.Parameters["@p_id"].Value = id;
// int ret = db.ExecuteNonQuery(command);
// db.Close();
// return ret;
//}
public int Insert(Customer cust)
{
if (!FindCustomerByEmail(cust.email))
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_INSERT);
PrepareCommand(command, cust);
int ret = db.ExecuteNonQuery(command);
db.Close();
return ret;
}
else return -2;
}
protected void Button1_Click(object sender, EventArgs e)
{
if (DoUpdate)
{
strBuilCommand.Append(" where @p_id = id");
SchoolDatabase db = new SchoolDatabase();
db.Connect();
string tmp = strBuilCommand.ToString();
SqlCommand command = db.CreateCommand(tmp);
PrepareCommand(command, emp);
int ret = db.ExecuteNonQuery(command);
db.Close();
}
Response.Redirect("~/Employees/ListEmployee.aspx");
}
public bool IsGarageEmpty(int gar)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_IS_GARAGE_EMPTY);
command.Parameters.Add(new SqlParameter("@p_gar", SqlDbType.Int));
command.Parameters["@p_gar"].Value = gar;
SqlDataReader reader = db.Select(command);
reader.Read();
int tmp = reader.GetInt32(0);
reader.Close();
db.Close();
if (tmp == 0) return true;
else return false;
}
public List<ListItem> FindManufacturers()
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_FIND_MANUFACTURER);
SqlDataReader reader = db.Select(command);
List<ListItem> tires = new List<ListItem>();
tires.Add(new ListItem("Všichni výrobci", "x", true));
while (reader.Read())
{
ListItem l1 = new ListItem(reader.GetString(0), "x", true);
tires.Add(l1);
}
db.Close();
return tires;
}
public bool UnstoreCheck(int id)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_UNSTORE_CHECK);
command.Parameters.Add(new SqlParameter("@p_id", SqlDbType.Int));
command.Parameters["@p_id"].Value = id;
SqlDataReader reader = db.Select(command);
reader.Read();
int ret = reader.GetInt32(0);
db.Close();
if (ret == 1) return true;
else return false;
}
public List<StoredWheels> Select(int id)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_SELECT);
command.Parameters.Add(new SqlParameter("@p_Customers_id", SqlDbType.VarChar, 20));
command.Parameters["@p_Customers_id"].Value = id;
SqlDataReader reader = db.Select(command);
List<StoredWheels> cust = Read(reader);
reader.Close();
db.Close();
return cust;
}
public int Exit(Contract con)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_EXIT);
command.Parameters.Add(new SqlParameter("@p_GarageNumber", SqlDbType.SmallInt));
command.Parameters["@p_GarageNumber"].Value = con.GarageNumber;
command.Parameters.Add(new SqlParameter("@p_Payment", SqlDbType.SmallInt));
command.Parameters["@p_Payment"].Value = con.Payment;
command.Parameters.Add(new SqlParameter("@p_VAT", SqlDbType.Bit));
command.Parameters["@p_VAT"].Value = con.VAT;
int ret = db.ExecuteNonQuery(command);
db.Close();
return ret;
}
public int Arrival(Contract con)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_ARRIVAL);
command.Parameters.Add(new SqlParameter("@p_CustomerID", SqlDbType.Int));
command.Parameters["@p_CustomerID"].Value = con.CustomerId;
command.Parameters.Add(new SqlParameter("@p_EmployeesID", SqlDbType.Int));
command.Parameters["@p_EmployeesID"].Value = con.EmployeesId;
command.Parameters.Add(new SqlParameter("@p_GarageNumber", SqlDbType.SmallInt));
command.Parameters["@p_GarageNumber"].Value = con.GarageNumber;
int ret = db.ExecuteNonQuery(command);
db.Close();
return ret;
}
public bool UnstoreTires(int id, int EmployeeID)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_UNSTORE);
command.Parameters.Add(new SqlParameter("@p_id", SqlDbType.Int));
command.Parameters["@p_id"].Value = id;
command.Parameters.Add(new SqlParameter("@p_Unstore_id", SqlDbType.Int));
command.Parameters["@p_Unstore_id"].Value = EmployeeID;
int ret = db.ExecuteNonQuery(command);
db.Close();
if (ret == 1) return true;
else return false;
}
public OkPneuTire Select(string EAN)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_FIND_BY_EAN);
command.Parameters.Add(new SqlParameter("@p_EAN", SqlDbType.VarChar, 14));
command.Parameters["@p_EAN"].Value = EAN;
SqlDataReader reader = db.Select(command);
reader.Read();
OkPneuTire tire = new OkPneuTire();
tire.Name = reader.GetString(0);
tire.ProdejniCena = reader.GetDecimal(1);
reader.Close();
db.Close();
return tire;
}
public List<OkPneuTire> Select(string strsize, char season, bool OnExternalStore, bool OnLokalStore, string manu)
{
if (strsize == null) return null;
string pattern = "[1-3][0-9][0,5][3-9][0,5][1,2][0-9]";
Regex currencyRegex = new Regex(pattern);
if (currencyRegex.IsMatch(strsize))
{
int size = Int32.Parse(strsize);
int min;
if (OnExternalStore) min = 4;
else min = 0;
int minL;
if (OnLokalStore) minL = 4;
else minL = 0;
SchoolDatabase db = new SchoolDatabase();
db.Connect();
if (manu != "Všichni výrobci")
{
SqlCommand command = db.CreateCommand(SQL_SELECT_BY_DETAIL_SIZE);
command.Parameters.Add(new SqlParameter("@p_Size", SqlDbType.Int));
command.Parameters["@p_Size"].Value = size;
command.Parameters.Add(new SqlParameter("@p_Season", SqlDbType.Char, 1));
command.Parameters["@p_Season"].Value = season;
command.Parameters.Add(new SqlParameter("@p_OnExternalStore", SqlDbType.Int));
command.Parameters["@p_OnExternalStore"].Value = min;
command.Parameters.Add(new SqlParameter("@p_OnLocalStore", SqlDbType.Int));
command.Parameters["@p_OnLocalStore"].Value = minL;
command.Parameters.Add(new SqlParameter("@p_manu", SqlDbType.VarChar, 10));
command.Parameters["@p_manu"].Value = manu;
SqlDataReader reader = db.Select(command);
List<OkPneuTire> tires = Read(reader);
reader.Close();
db.Close();
return tires;
}
else
{
SqlCommand command = db.CreateCommand(SQL_SELECT_BY_DETAIL_SIZE_WITHOUT_MANU);
command.Parameters.Add(new SqlParameter("@p_Size", SqlDbType.Int));
command.Parameters["@p_Size"].Value = size;
command.Parameters.Add(new SqlParameter("@p_Season", SqlDbType.Char, 1));
command.Parameters["@p_Season"].Value = season;
command.Parameters.Add(new SqlParameter("@p_OnExternalStore", SqlDbType.Int));
command.Parameters["@p_OnExternalStore"].Value = min;
command.Parameters.Add(new SqlParameter("@p_OnLocalStore", SqlDbType.Int));
command.Parameters["@p_OnLocalStore"].Value = minL;
SqlDataReader reader = db.Select(command);
List<OkPneuTire> tires = Read(reader);
reader.Close();
db.Close();
return tires;
}
}
else return null;
}
public List<Contract> Select()
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_SELECT);
SqlDataReader reader = db.Select(command);
List<Contract> emp = Read(reader);
reader.Close();
db.Close();
return emp;
}
private bool FindCustomerByEmail(string email)
{
int tmp;
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_CUSTOMER_EXISTS);
command.Parameters.Add(new SqlParameter("@p_email", SqlDbType.VarChar, 50));
command.Parameters["@p_email"].Value = email;
SqlDataReader reader = db.Select(command);
reader.Read();
tmp = reader.GetInt32(0);
reader.Close();
db.Close();
if (tmp > 0) return true;
else return false;
}
public List<Customer> Select(string lname)
{
string tmp = lname + '%';
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_SELECT);
command.Parameters.Add(new SqlParameter("@p_lname", SqlDbType.VarChar, 20));
command.Parameters["@p_lname"].Value = tmp;
SqlDataReader reader = db.Select(command);
List<Customer> cust = Read(reader);
reader.Close();
db.Close();
return cust;
}
public List<OkPneuTire> Select(int size)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_SELECT_BY_DETAIL);
command.Parameters.Add(new SqlParameter("@p_Size", SqlDbType.Int));
command.Parameters["@p_Size"].Value = size;
SqlDataReader reader = db.Select(command);
List<OkPneuTire> tires = Read(reader);
reader.Close();
db.Close();
return tires;
}
public int Update(Customer cust)
{
SchoolDatabase db = new SchoolDatabase();
db.Connect();
SqlCommand command = db.CreateCommand(SQL_UPDATE);
PrepareCommand(command, cust);
int ret = db.ExecuteNonQuery(command);
db.Close();
return ret;
}