public static Yayinevi getPublisher(int id)
{
Yayinevi Value = null;
SqlConnection con = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
SqlCommand cmd = new SqlCommand("select * from dbo.Yayinevi where ID=" + id.ToString(), con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
Value = new Yayinevi(reader.GetInt32(0),reader.GetString(1));
try
{
Value.Adres = reader.GetString(2);
}
catch (SqlNullValueException snve)
{
Value.Adres = "";
}
try
{
Value.KurulusYili = int.Parse(reader.GetString(3));
}
catch (SqlNullValueException snve)
{
Value.KurulusYili = 0;
}
}
reader.Close();
con.Close();
return Value;
}
public Kitap(int id, string adi, int basimYili, Yazar[] yazar, Yayinevi yayinevi, Kategori kategori, int sayfa, bool kiralama, bool hasar, Decimal fiyat)
: base(id, adi)
{
this.BasimYili = basimYili;
this.Author = yazar;
this.Publisher = yayinevi;
this.Category = kategori;
this.SayfaSayisi = sayfa;
this.KiralamaDurumu = kiralama;
this.HasarDurumu = hasar;
this.Fiyat = fiyat;
}
public static List<Yayinevi> getPublishers()
{
List<Yayinevi> Value = new List<Yayinevi>();
SqlConnection con = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
SqlCommand cmd = new SqlCommand("select * from dbo.Yayinevi", con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
string adi = reader.GetString(1);
Yayinevi Current = new Yayinevi(id, adi);
string adresi,kurulus;
int yil;
try
{
adresi = reader.GetString(2);
}
catch (SqlNullValueException snve)
{
adresi = "";
}
try
{
kurulus = reader.GetString(3);
yil = int.Parse(kurulus);
}
catch (SqlNullValueException snve)
{
yil = 0;
}
Current.Adres = adresi;
Current.KurulusYili = yil;
Value.Add(Current);
}
reader.Close();
con.Close();
return Value;
}
public static int record(Yayinevi Value)
{
int rvalue = 0;
SqlConnection con = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
String CommandString = "";
if (Value.ID == 0)
{
CommandString += "insert into dbo.Yayinevi";
if (String.IsNullOrEmpty(Value.Adres))
{
if (Value.KurulusYili == 0)
{
CommandString += " (Adi) values('" + Value.Adi + "')";
}
else
{
CommandString += " (Adi,KurulusYili) values('" + Value.Adi + "','" + Value.KurulusYili.ToString() + "')";
}
}
else
{
if (Value.KurulusYili == 0)
{
CommandString += " (Adi,Adresi) values('" + Value.Adi + "','" + Value.Adres + "')";
}
else
{
CommandString += " (Adi,Adresi,KurulusYili) values('" + Value.Adi + "','" + Value.Adres + "','" + Value.KurulusYili.ToString() + "')";
}
}
SqlCommand cmd = new SqlCommand(CommandString, con);
con.Open();
try
{
int rows = cmd.ExecuteNonQuery();
if (rows == 1)
{
con.Close();
cmd = new SqlCommand("select ID from dbo.Yayinevi order by ID desc",con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
rvalue = reader.GetInt32(0);
reader.Close();
}
else
{
rvalue = 0;
}
}
catch (Exception ex)
{
rvalue = 0;
}
con.Close();
}
else
{
CommandString += "update dbo.Yayinevi set";
if (String.IsNullOrEmpty(Value.Adres))
{
if (Value.KurulusYili == 0)
{
CommandString += " Adi='" + Value.Adi + "'";
}
else
{
CommandString += " Adi='" + Value.Adi + "',KurulusYili='" + Value.KurulusYili.ToString() + "'";
}
}
else
{
if (Value.KurulusYili == 0)
{
CommandString += " Adi='" + Value.Adi + "',Adresi='" + Value.Adres + "'";
}
else
{
CommandString += " Adi='" + Value.Adi + "',Adresi='" + Value.Adres + "',KurulusYili='" + Value.KurulusYili.ToString() + "'";
}
}
CommandString += " where ID=" + Value.ID.ToString();
SqlCommand cmd = new SqlCommand(CommandString, con);
con.Open();
try
{
int rows = cmd.ExecuteNonQuery();
if (rows == 1)
{
rvalue = Value.ID;
}
else
{
rvalue = 0;
}
}
catch (Exception ex)
{
rvalue = 0;
}
con.Close();
}
return rvalue;
}
public static List<Yayinevi> mostPopularPublisher(DateTime First, DateTime Last)
{
List<Yayinevi> Value = new List<Yayinevi>();
SqlConnection con = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
String CommandString = "select YayineviID,COUNT(YayineviID) as TercihSayisi from dbo.Kiralama ";
CommandString += "join dbo.KiralamaDetay on Kiralama.ID = KiralamaDetay.ID ";
CommandString += "join dbo.Kitap on Kitap.ID = Kiralama.KitapID ";
CommandString += "where KiralamaDetay.KiralamaTarihi between ";
CommandString += "'" + convertToSqlDate(First.ToShortDateString()) + "' and ";
CommandString += "'" + convertToSqlDate(Last.ToShortDateString()) + "'";
CommandString += " group by YayineviID order by TercihSayisi desc";
SqlCommand cmd = new SqlCommand(CommandString, con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
int max = 0;
List<int> Publishers = new List<int>();
if (reader.Read())
{
max = reader.GetInt32(1);
Publishers.Add(reader.GetInt32(0));
}
while (reader.Read())
{
int currentCount = reader.GetInt32(1);
if (currentCount == max)
{
Publishers.Add(reader.GetInt32(0));
}
else if (currentCount < max)
{
break;
}
}
reader.Close();
con.Close();
if (Publishers.Count > 0)
{
CommandString = "select * from dbo.Yayinevi where ";
foreach (int item in Publishers)
{
CommandString += "ID=" + item.ToString() + " or ";
}
CommandString = CommandString.Remove(CommandString.Length - 4);
cmd = new SqlCommand(CommandString, con);
con.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
string adi = reader.GetString(1);
Yayinevi Current = new Yayinevi(id, adi);
string adresi, kurulus;
int yil;
try
{
adresi = reader.GetString(2);
}
catch (SqlNullValueException snve)
{
adresi = "";
}
try
{
kurulus = reader.GetString(3);
yil = int.Parse(kurulus);
}
catch (SqlNullValueException snve)
{
yil = 0;
}
Current.Adres = adresi;
Current.KurulusYili = yil;
Value.Add(Current);
}
reader.Close();
con.Close();
}
return Value;
}
public Form3(String Title, Object Edition, int type)
{
InitializeComponent();
this.Text = Title;
KurulusYiliNUD.Maximum = DateTime.Now.Year;
this.Type = type;
switch (Type)
{
case 0:
label5.Text = "Yazar :";
comboBox1.DataSource = YazarIslemleri.getAuthors();
label2.Text = "Özgeçmiş :";
label4.Hide();
KurulusYiliNUD.Hide();
KurulusYiliCB.Hide();
CurrentYaz = (Yazar)Edition;
if (comboBox1.Items.Contains(CurrentYaz))
{
comboBox1.SelectedItem = CurrentYaz;
}
else
{
comboBox1.SelectedIndex = -1;
AdiTB.Text = CurrentYaz.Adi;
SoyadiTB.Text = CurrentYaz.Soyadi;
richTextBox1.Text = CurrentYaz.Ozgecmis;
}
break;
case 1:
label5.Text = "Yayınevi :";
comboBox1.DataSource = YayineviIslemleri.getPublishers();
label2.Text = "Adres :";
label3.Hide();
SoyadiTB.Hide();
KurulusYiliNUD.Enabled = false;
KurulusYiliCB.Checked = false;
CurrentYay = (Yayinevi)Edition;
if (comboBox1.Items.Contains(CurrentYay))
{
comboBox1.SelectedItem = CurrentYay;
}
else
{
comboBox1.SelectedIndex = -1;
AdiTB.Text = CurrentYay.Adi;
richTextBox1.Text = CurrentYay.Adres;
if (CurrentYay.KurulusYili != 0)
{
KurulusYiliNUD.Value = CurrentYay.KurulusYili;
KurulusYiliCB.Checked = true;
}
}
break;
case 2:
comboBox1.DataSource = KategoriIslemleri.getCategories();
label3.Hide();
SoyadiTB.Hide();
label4.Hide();
KurulusYiliNUD.Hide();
KurulusYiliCB.Hide();
CurrentKat = (Kategori)Edition;
if (comboBox1.Items.Contains(CurrentKat))
{
comboBox1.SelectedItem = CurrentKat;
}
else
{
comboBox1.SelectedIndex = -1;
AdiTB.Text = CurrentKat.Adi;
richTextBox1.Text = CurrentKat.Aciklama;
}
break;
}
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
if (comboBox1.SelectedIndex != -1)
{
switch (Type)
{
case 0:
CurrentYaz = (Yazar)comboBox1.SelectedItem;
AdiTB.Text = CurrentYaz.Adi;
SoyadiTB.Text = CurrentYaz.Soyadi;
richTextBox1.Text = CurrentYaz.Ozgecmis;
break;
case 1:
CurrentYay = (Yayinevi)comboBox1.SelectedItem;
AdiTB.Text = CurrentYay.Adi;
richTextBox1.Text = CurrentYay.Adres;
if (CurrentYay.KurulusYili != 0)
{
KurulusYiliNUD.Value = CurrentYay.KurulusYili;
KurulusYiliCB.Checked = true;
}
break;
case 2:
CurrentKat = (Kategori)comboBox1.SelectedItem;
AdiTB.Text = CurrentKat.Adi;
richTextBox1.Text = CurrentKat.Aciklama;
break;
}
}
}