public List <Datas.Program> SelectAllPrograms()
{
con.Open();
List <Datas.Program> programs = new List <Datas.Program>();
string sorgu = "SELECT program.id, program.programAdi, program.programBolumSayisi,program.programTipi,program.programUzunlugu,program.toplamPuan,tur.turAdi,tur.id FROM program, programTur, tur WHERE programTur.programId = program.id AND programTur.turId = tur.id; ";
cmd = new SQLiteCommand(sorgu, con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
Datas.Program program = new Datas.Program
{
Id = Int32.Parse(dr["id"].ToString()),
ProgramAdi = dr["programAdi"].ToString(),
ProgramBolumSayisi = Int32.Parse(dr["programBolumSayisi"].ToString()),
ProgramTipi = Int32.Parse(dr["programTipi"].ToString()),
ProgramUzunlugu = Int32.Parse(dr["programUzunlugu"].ToString()),
ToplamPuan = Int32.Parse(dr["toplamPuan"].ToString()),
Turler = dr["turAdi"].ToString()
};
bool varMi = false;
foreach (var item in programs)
{
if (program.Id == item.Id)
{
item.Turler += "," + program.Turler;
varMi = true;
}
}
if (varMi == false)
{
programs.Add(program);
}
}
con.Close();
return(programs);
}
public List <Datas.Program> SelectPrograms(int turID, string programAdi)
{
con.Open();
/*
* SELECT program.id, program.programAdi, program.programBolumSayisi,program.programTipi,program.programUzunlugu,program.toplamPuan,tur.turAdi,tur.id FROM program, programTur, tur WHERE programTur.programId = program.id AND programTur.turId = tur.id AND programAdi LIKE "*Recep İvedik*";
*/
List <Datas.Program> programs = new List <Datas.Program>();
string sorgu;
if (programAdi == "_____" && turID != -1)
{
sorgu = "SELECT " +
"program.id AS pid, program.programAdi, program.programBolumSayisi," +
"program.programTipi,program.programUzunlugu,program.toplamPuan," +
"tur.turAdi, tur.id " +
"FROM " +
"programTur " +
"INNER JOIN tur ON " +
"tur.id = programTur.turID " +
"INNER JOIN program ON " +
"program.id = programTur.programID " +
"WHERE tur.id = " + turID + "";
}
else if (turID == -1 && programAdi != "_____")
{
sorgu = "SELECT program.id AS pid, program.programAdi, " +
"program.programBolumSayisi,program.programTipi," +
"program.programUzunlugu,program.toplamPuan, tur.turAdi, tur.id " +
"FROM " +
"programTur INNER JOIN " +
"tur ON " +
"tur.id = programTur.turID " +
"INNER JOIN program ON " +
"program.id = programTur.programID " +
"WHERE program.programAdi LIKE '%" + programAdi + "%'; ";
}
else if (turID != -1 && programAdi != "_____")
{
sorgu = "SELECT program.id AS pid, program.programAdi, " +
"program.programBolumSayisi,program.programTipi," +
"program.programUzunlugu,program.toplamPuan, tur.turAdi, tur.id " +
"FROM " +
"programTur INNER JOIN " +
"tur ON " +
"tur.id = programTur.turID " +
"INNER JOIN program ON " +
"program.id = programTur.programID " +
"WHERE tur.id = " + turID + " AND program.programAdi LIKE '%" + programAdi + "%'; ";
}
else
{
sorgu = "SELECT program.id AS pid, program.programAdi, " +
"program.programBolumSayisi,program.programTipi," +
"program.programUzunlugu,program.toplamPuan, tur.turAdi, tur.id " +
"FROM " +
"programTur INNER JOIN " +
"tur ON " +
"tur.id = programTur.turID " +
"INNER JOIN program ON " +
"program.id = programTur.programID";
}
cmd = new SQLiteCommand(sorgu, con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
Datas.Program program = new Datas.Program
{
Id = Int32.Parse(dr["pid"].ToString()),
ProgramAdi = dr["programAdi"].ToString(),
ProgramBolumSayisi = Int32.Parse(dr["programBolumSayisi"].ToString()),
ProgramTipi = Int32.Parse(dr["programTipi"].ToString()),
ProgramUzunlugu = Int32.Parse(dr["programUzunlugu"].ToString()),
ToplamPuan = Int32.Parse(dr["toplamPuan"].ToString()),
Turler = dr["turAdi"].ToString()
};
bool varMi = false;
foreach (var item in programs)
{
if (program.Id == item.Id)
{
item.Turler += "," + program.Turler;
varMi = true;
}
}
if (varMi == false)
{
programs.Add(program);
}
}
con.Close();
return(programs);
}
public List <Datas.Program> SelectProgram(int id)
{
con.Open();
List <Datas.Program> programs = new List <Datas.Program>();
string sorgu = "SELECT program.id, program.programAdi, program.programBolumSayisi,program.programTipi,program.programUzunlugu,program.toplamPuan,tur.turAdi,tur.id FROM program, programTur, tur WHERE program.id = " + id + " AND programTur.programId = program.id AND programTur.turId = tur.id; ";
cmd = new SQLiteCommand(sorgu, con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
Datas.Program program = new Datas.Program
{
Id = Int32.Parse(dr["id"].ToString()),
ProgramAdi = dr["programAdi"].ToString(),
ProgramBolumSayisi = Int32.Parse(dr["programBolumSayisi"].ToString()),
ProgramTipi = Int32.Parse(dr["programTipi"].ToString()),
ProgramUzunlugu = Int32.Parse(dr["programUzunlugu"].ToString()),
ToplamPuan = Int32.Parse(dr["toplamPuan"].ToString()),
Turler = dr["turAdi"].ToString()
};
bool varMi = false;
foreach (var item in programs)
{
if (program.Id == item.Id)
{
item.Turler += "," + program.Turler;
varMi = true;
}
}
if (varMi == false)
{
programs.Add(program);
}
}
con.Close();
con.Open();
int userID = Program.user.Id;
int programID = id;
string puanVarMiSorgu = "SELECT count(*) FROM program, kullaniciProgram, kullanici WHERE kullaniciProgram.programID = program.id AND kullaniciProgram.kullaniciID = kullanici.id AND kullanici.id = " + userID + " AND program.id= " + programID + ";";
cmd = new SQLiteCommand(puanVarMiSorgu, con);
//MessageBox.Show(cmd.ExecuteScalar().ToString());
int puanVarMi = Int32.Parse(cmd.ExecuteScalar().ToString());
con.Close();
if (puanVarMi > 0)
{
con.Open();
string puanSorgu = "SELECT * FROM program, kullaniciProgram, kullanici WHERE kullaniciProgram.programID = program.id AND kullaniciProgram.kullaniciID = kullanici.id AND kullanici.id = " + userID + " AND program.id= " + programID + " ORDER BY program.programUzunlugu DESC; ";
cmd = new SQLiteCommand(puanSorgu, con);
dr = cmd.ExecuteReader();
while (dr.Read())
{
//Console.WriteLine(dr["verilenPuan"].ToString());
// Console.WriteLine(dr["izlemeSuresi"].ToString());
//Console.WriteLine(dr["izlemeTarihi"].ToString());
// Console.WriteLine(dr["izlemeTarihi"].GetType());
programs[0].KullaniciPuani = (dr["verilenPuan"].ToString() != "") ? Int32.Parse(dr["verilenPuan"].ToString()) : 0;
programs[0].IzlemeSure = (dr["izlemeSuresi"].ToString() != "") ? Int32.Parse(dr["izlemeSuresi"].ToString()) : 0;
programs[0].IzlemeTarihi = (dr["izlemeTarihi"].ToString() != "") ? DateTime.Parse(dr["izlemeTarihi"].ToString()) : DateTime.MinValue;
programs[0].HangiBolumdeKaldi = (dr["HangiBolumdeKaldi"].ToString() != "") ? Int32.Parse(dr["HangiBolumdeKaldi"].ToString()) : 1;
}
con.Close();
}
else
{
programs[0].KullaniciPuani = 0;
programs[0].IzlemeSure = 0;
programs[0].HangiBolumdeKaldi = 1;
}
return(programs);
}