public static int SelectPocet(int id, MyDatabase pDb = null)
{
MyDatabase db;
if (pDb == null)
{
db = new MyDatabase();
db.Connect();
}
else
{
db = (MyDatabase)pDb;
}
SqlCommand command = db.CreateCommand(SQL_POCET_POZICE_ZAMESTNANCE);
command.Parameters.AddWithValue("@ID_poziceZamestnance", id);
int pocet = 0;
SqlDataReader reader = db.Select(command);
reader.Read();
pocet = reader.GetInt32(0);
reader.Close();
if (pDb == null)
{
db.Close();
}
return(pocet);
}
//3.4
public static Collection <PoziceZamestnance> Select(MyDatabase pDb = null)
{
MyDatabase db;
if (pDb == null)
{
db = new MyDatabase();
db.Connect();
}
else
{
db = (MyDatabase)pDb;
}
SqlCommand command = db.CreateCommand(SQL_SELECT);
SqlDataReader reader = db.Select(command);
Collection <PoziceZamestnance> pzC = Read(reader);
reader.Close();
if (pDb == null)
{
db.Close();
}
return(pzC);
}
//3.3
public static int Delete(int kID, MyDatabase pDb = null)
{
MyDatabase db;
if (pDb == null)
{
db = new MyDatabase();
db.Connect();
}
else
{
db = (MyDatabase)pDb;
}
int ret = 0;
if (SelectPocet(kID, pDb) == 1)
{
SqlCommand command = db.CreateCommand(SQL_DELETE_ID);
command.Parameters.AddWithValue("@ID_poziceZamestnance", kID);
ret = db.ExecuteNonQuery(command);
}
if (pDb == null)
{
db.Close();
}
return(ret);
}
public static Collection <SmenaTymu> Select(MyDatabase pDb = null)
{
MyDatabase db;
if (pDb == null)
{
db = new MyDatabase();
db.Connect();
}
else
{
db = (MyDatabase)pDb;
}
SqlCommand command = db.CreateCommand(SQL_SELECT);
SqlDataReader reader = db.Select(command);
Collection <SmenaTymu> stC = Read(reader);
reader.Close();
if (pDb == null)
{
db.Close();
}
return(stC);
}
public static int Insert(SmenaTymu st, MyDatabase pDB = null)
{
MyDatabase db;
if (pDB == null)
{
db = new MyDatabase();
db.Connect();
}
else
{
db = (MyDatabase)pDB;
}
SqlCommand command = db.CreateCommand(SQL_INSERT);
PrepareCommand(command, st);
int ret = db.ExecuteNonQuery(command);
if (pDB == null)
{
db.Close();
}
return(ret);
}
public static Collection <SmenaTymu> SeznamSmenTymu(int id, MyDatabase pDb = null)
{
MyDatabase db;
if (pDb == null)
{
db = new MyDatabase();
db.Connect();
}
else
{
db = (MyDatabase)pDb;
}
SqlCommand command = db.CreateCommand(SQL_SELECT_ID_SMENY);
command.Parameters.AddWithValue("@ID_smeny", id);
SqlDataReader reader = db.Select(command);
Collection <SmenaTymu> stC = Read(reader);
reader.Close();
if (pDb == null)
{
db.Close();
}
return(stC);
}
public static int SelectPocet(int id, MyDatabase pDb = null)
{
MyDatabase db;
if (pDb == null)
{
db = new MyDatabase();
db.Connect();
}
else
{
db = (MyDatabase)pDb;
}
SqlCommand command = db.CreateCommand(SQL_POCET_SMENY_TYMU);
command.Parameters.AddWithValue("@ID_smenyTymu", id);
int pocet = 0;
SqlDataReader reader = db.Select(command);
reader.Read();
pocet = reader.GetInt32(0);
reader.Close();
if (pDb == null)
{
db.Close();
}
return(pocet);
}
public static Collection <Tym> VypisTymuZaUrciteObdobi(DateTime Od, DateTime Do, MyDatabase pDb = null)
{
String SQL_DOVOLENE_ZA_CAS = "SELECT * FROM Tym " +
"WHERE Datum BETWEEN '" + Convert.ToDateTime(Od).ToString("yyyy-MM-dd") + "' and '" + Convert.ToDateTime(Do).ToString("yyyy-MM-dd") + "'";
MyDatabase db;
if (pDb == null)
{
db = new MyDatabase();
db.Connect();
}
else
{
db = (MyDatabase)pDb;
}
SqlCommand command = db.CreateCommand(SQL_DOVOLENE_ZA_CAS);
SqlDataReader reader = db.Select(command);
Collection <Tym> tymy = Read(reader);
reader.Close();
if (pDb == null)
{
db.Close();
}
return(tymy);
}
public static int Update(PoziceZamestnance pz, MyDatabase pDb = null)
{
MyDatabase db;
if (pDb == null)
{
db = new MyDatabase();
db.Connect();
}
else
{
db = (MyDatabase)pDb;
}
SqlCommand command = db.CreateCommand(SQL_UPDATE);
PrepareCommand(command, pz);
int ret = db.ExecuteNonQuery(command);
if (pDb == null)
{
db.Close();
}
return(ret);
}
public static Collection <PoziceZamestnance> SeznamPozicZamestnance(int id, MyDatabase pDb = null)
{
MyDatabase db;
if (pDb == null)
{
db = new MyDatabase();
db.Connect();
}
else
{
db = (MyDatabase)pDb;
}
SqlCommand command = db.CreateCommand(SQL_SELECT_ID_ZAMESTNANCE);
command.Parameters.AddWithValue("@ID_zamestnance", id);
SqlDataReader reader = db.Select(command);
Collection <PoziceZamestnance> poziceZamestnance = Read(reader);
reader.Close();
if (pDb == null)
{
db.Close();
}
return(poziceZamestnance);
}
public static int VytvoreniKopieTymu(int ID_starehoTymu, DateTime Datum, string nazev, int ID_smeny, MyDatabase pDb = null)
{
MyDatabase db;
if (pDb == null)
{
db = new MyDatabase();
db.Connect();
}
else
{
db = (MyDatabase)pDb;
}
SqlCommand command = db.CreateCommand("VytvoreniKopieTymu");
command.CommandType = CommandType.StoredProcedure;
SqlParameter input = new SqlParameter();
input.ParameterName = "@ID_starehoTymu";
input.DbType = DbType.Int32;
input.Value = ID_starehoTymu;
input.Direction = ParameterDirection.Input;
command.Parameters.Add(input);
SqlParameter input2 = new SqlParameter();
input2.ParameterName = "@Datum";
input2.DbType = DbType.DateTime;
input2.Value = Datum;
input2.Direction = ParameterDirection.Input;
command.Parameters.Add(input2);
SqlParameter input3 = new SqlParameter();
input3.ParameterName = "@nazev";
input3.DbType = DbType.String;
input3.Value = nazev;
input3.Direction = ParameterDirection.Input;
command.Parameters.Add(input3);
SqlParameter input4 = new SqlParameter();
input4.ParameterName = "@ID_smeny";
input4.DbType = DbType.Int32;
input4.Value = ID_smeny;
input4.Direction = ParameterDirection.Input;
command.Parameters.Add(input4);
int ret = db.ExecuteNonQuery(command);
if (pDb == null)
{
db.Close();
}
return(ret);
}
public static int VlozeniNoveDovolene(int ID_zamestnance, DateTime Od, DateTime Do, int ID_duvod, MyDatabase pDb = null)
{
MyDatabase db;
if (pDb == null)
{
db = new MyDatabase();
db.Connect();
}
else
{
db = (MyDatabase)pDb;
}
SqlCommand command = db.CreateCommand("VlozeniNoveDovolene");
command.CommandType = CommandType.StoredProcedure;
SqlParameter input = new SqlParameter();
input.ParameterName = "@ID_zamestnance";
input.DbType = DbType.Int32;
input.Value = ID_zamestnance;
input.Direction = ParameterDirection.Input;
command.Parameters.Add(input);
SqlParameter input2 = new SqlParameter();
input2.ParameterName = "@Od";
input2.DbType = DbType.DateTime;
input2.Value = Od;
input2.Direction = ParameterDirection.Input;
command.Parameters.Add(input2);
SqlParameter input3 = new SqlParameter();
input3.ParameterName = "@Do";
input3.DbType = DbType.DateTime;
input3.Value = Do;
input3.Direction = ParameterDirection.Input;
command.Parameters.Add(input3);
SqlParameter input4 = new SqlParameter();
input4.ParameterName = "@ID_duvod";
input4.DbType = DbType.Int32;
input4.Value = ID_duvod;
input4.Direction = ParameterDirection.Input;
command.Parameters.Add(input4);
int ret = db.ExecuteNonQuery(command);
if (pDb == null)
{
db.Close();
}
return(ret);
}
public static Pracoviste Select(int id, MyDatabase pDb = null)
{
MyDatabase db;
if (pDb == null)
{
db = new MyDatabase();
db.Connect();
}
else
{
db = (MyDatabase)pDb;
}
SqlCommand command = db.CreateCommand(SQL_SELECT_ID);
command.Parameters.AddWithValue("@ID_pracoviste", id);
SqlDataReader reader = db.Select(command);
Collection <Pracoviste> pracovisteC = Read(reader);
Pracoviste pracoviste = null;
if (pracovisteC.Count == 1)
{
pracoviste = pracovisteC[0];
}
reader.Close();
if (pDb == null)
{
db.Close();
}
if (pracoviste == null)
{
Console.WriteLine("Pracoviste neexistuje");
}
return(pracoviste);
}
public static TymZamestnance Select(int id, MyDatabase pDb = null)
{
MyDatabase db;
if (pDb == null)
{
db = new MyDatabase();
db.Connect();
}
else
{
db = (MyDatabase)pDb;
}
SqlCommand command = db.CreateCommand(SQL_SELECT_ID);
command.Parameters.AddWithValue("@ID_tymZamestnance", id);
SqlDataReader reader = db.Select(command);
Collection <TymZamestnance> tzC = Read(reader);
TymZamestnance tz = null;
if (tzC.Count == 1)
{
tz = tzC[0];
}
reader.Close();
if (pDb == null)
{
db.Close();
}
if (tz == null)
{
Console.WriteLine("Tym zamestnance neexistuje");
}
return(tz);
}
public static Duvod Select(int id, MyDatabase pDb = null)
{
MyDatabase db;
if (pDb == null)
{
db = new MyDatabase();
db.Connect();
}
else
{
db = (MyDatabase)pDb;
}
SqlCommand command = db.CreateCommand(SQL_SELECT_ID);
command.Parameters.AddWithValue("@ID_duvodu", id);
SqlDataReader reader = db.Select(command);
Collection <Duvod> duvody = Read(reader);
Duvod duvod = null;
if (duvody.Count == 1)
{
duvod = duvody[0];
}
reader.Close();
if (pDb == null)
{
db.Close();
}
if (duvod == null)
{
Console.WriteLine("Duvod neexistuje");
}
return(duvod);
}
public static Zamestnanec Select(int id, MyDatabase pDb = null)
{
MyDatabase db;
if (pDb == null)
{
db = new MyDatabase();
db.Connect();
}
else
{
db = (MyDatabase)pDb;
}
SqlCommand command = db.CreateCommand(SQL_SELECT_ID);
command.Parameters.AddWithValue("@ID_zamestnance", id);
SqlDataReader reader = db.Select(command);
Collection <Zamestnanec> zamestnanci = Read(reader);
Zamestnanec zamestnanec = null;
if (zamestnanci.Count == 1)
{
zamestnanec = zamestnanci[0];
}
reader.Close();
if (pDb == null)
{
db.Close();
}
if (zamestnanec == null)
{
Console.WriteLine("Zamestnanec neexistuje");
}
return(zamestnanec);
}
public static Smena Select(int id, MyDatabase pDb = null)
{
MyDatabase db;
if (pDb == null)
{
db = new MyDatabase();
db.Connect();
}
else
{
db = (MyDatabase)pDb;
}
SqlCommand command = db.CreateCommand(SQL_SELECT_ID);
command.Parameters.AddWithValue("@ID_smeny", id);
SqlDataReader reader = db.Select(command);
Collection <Smena> smeny = Read(reader);
Smena smena = null;
if (smeny.Count == 1)
{
smena = smeny[0];
}
reader.Close();
if (pDb == null)
{
db.Close();
}
if (smena == null)
{
Console.WriteLine("Smena neexistuje");
}
return(smena);
}
public static int VlozeniNovehoZamestnance(String jmeno, String prijmeni, DateTime datum_narozeni, Boolean jeMistr, String heslo, String pozice, MyDatabase pDb = null)
{
MyDatabase db;
if (pDb == null)
{
db = new MyDatabase();
db.Connect();
}
else
{
db = (MyDatabase)pDb;
}
SqlCommand command = db.CreateCommand("VlozeniNovehoZamestnance");
command.CommandType = CommandType.StoredProcedure;
SqlParameter input = new SqlParameter();
input.ParameterName = "@jmeno";
input.DbType = DbType.String;
input.Value = jmeno;
input.Direction = ParameterDirection.Input;
command.Parameters.Add(input);
SqlParameter input2 = new SqlParameter();
input2.ParameterName = "@prijmeni";
input2.DbType = DbType.String;
input2.Value = prijmeni;
input2.Direction = ParameterDirection.Input;
command.Parameters.Add(input2);
SqlParameter input3 = new SqlParameter();
input3.ParameterName = "@datum_narozeni";
input3.DbType = DbType.DateTime;
input3.Value = datum_narozeni;
input3.Direction = ParameterDirection.Input;
command.Parameters.Add(input3);
SqlParameter input4 = new SqlParameter();
input4.ParameterName = "@jeMistr";
input4.DbType = DbType.Boolean;
input4.Value = jeMistr;
input4.Direction = ParameterDirection.Input;
command.Parameters.Add(input4);
SqlParameter input5 = new SqlParameter();
input5.ParameterName = "@heslo";
input5.DbType = DbType.String;
input5.Value = heslo;
input5.Direction = ParameterDirection.Input;
command.Parameters.Add(input5);
SqlParameter input6 = new SqlParameter();
input6.ParameterName = "@pozice";
input6.DbType = DbType.String;
input6.Value = pozice;
input6.Direction = ParameterDirection.Input;
command.Parameters.Add(input6);
int ret = db.ExecuteNonQuery(command);
if (pDb == null)
{
db.Close();
}
return(ret);
}
