public Form5(bool type, Kitap ktp)
{
InitializeComponent();
this.Type = type;
Current = ktp;
KitapTB.Text = Current.ToString();
Uyeler = UyeIslemleri.getMembers();
if (Type)
{
this.Text = "Kitap Kirala";
HasarCB.Hide();
label6.Hide();
UcretTB.Hide();
label5.Hide();
Finish.Text = "Kirala";
}
else
{
this.Text = "Kitap Teslim Al";
int[] Temp = KiralamaIslemleri.getLastRent(Current.ID);
KiralamaID = Temp[0];
foreach (Uye item in Uyeler)
{
if (item.ID == Temp[1])
{
UyeList.Items.Add(item);
UyeList.Enabled = false;
UyeNoTB.Enabled = false;
UyeAdiTB.Enabled = false;
break;
}
}
Gecikme = KiralamaIslemleri.gecikmeHesapla(KiralamaID);
HasarCB.Checked = Current.HasarDurumu;
ucretHesapla();
Finish.Text = "Teslim Al";
}
}
public Form2(String Title, Kitap Addition)
{
InitializeComponent();
YazarCB.DataSource = YazarIslemleri.getAuthors();
YazarCB.SelectedIndex = -1;
YazarList.Items.Clear();
YayineviCB.DataSource = YayineviIslemleri.getPublishers();
KategoriCB.DataSource = KategoriIslemleri.getCategories();
this.Text = Title;
Current = Addition;
KitapTB.Text = Current.Adi;
BasimYiliNUD.Maximum = DateTime.Now.Year;
BasimYiliNUD.Value = Current.BasimYili;
SayfaSayisiNUD.Maximum = Decimal.MaxValue;
SayfaSayisiNUD.Value = Current.SayfaSayisi;
FiyatTB.Text = Current.Fiyat.ToString();
Hasar.Checked = Current.HasarDurumu;
KapakTB.Text = Current.Kapak;
OzetRTB.Text = Current.Ozet;
if (Current.Publisher != null)
{
if (YayineviCB.Items.Contains(Current.Publisher))
{
YayineviCB.SelectedItem = Current.Publisher;
}
else
{
YayineviCB.Text = Current.Publisher.Adi;
YayineviCB.SelectedIndex = -1;
}
}
else
{
YayineviCB.SelectedIndex = -1;
}
if (Current.Category != null)
{
if (KategoriCB.Items.Contains(Current.Category))
{
KategoriCB.SelectedItem = Current.Category;
}
else
{
KategoriCB.Text = Current.Category.Adi;
KategoriCB.SelectedIndex = -1;
}
}
else
{
KategoriCB.SelectedIndex = -1;
}
if (Current.Author != null)
{
YazarList.Items.AddRange(Current.Author);
}
}
public static List<Kitap> mostRentedBook(DateTime First, DateTime Last)
{
List<Kitap> Value = new List<Kitap>();
SqlConnection con = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
String CommandString = "select KitapID,COUNT(KitapID) as KiralamaSayisi from dbo.Kiralama" +
" join dbo.KiralamaDetay on Kiralama.ID = KiralamaDetay.ID" +
" where KiralamaDetay.KiralamaTarihi between";
CommandString += " '" + convertToSqlDate(First.ToShortDateString()) + "' and";
CommandString += " '" + convertToSqlDate(Last.ToShortDateString()) + "' group by KitapID";
CommandString += " order by KiralamaSayisi desc";
SqlCommand cmd = new SqlCommand(CommandString, con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
int max = 0;
List<int> Kitaplar = new List<int>();
if (reader.Read())
{
max = reader.GetInt32(1);
Kitaplar.Add(reader.GetInt32(0));
}
while (reader.Read())
{
int currentCount = reader.GetInt32(1);
if (currentCount == max)
{
Kitaplar.Add(reader.GetInt32(0));
}
else if (currentCount < max)
{
break;
}
}
reader.Close();
con.Close();
if (Kitaplar.Count > 0)
{
CommandString = "select * from dbo.Kitap where ";
foreach (int item in Kitaplar)
{
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);
SqlConnection AuthorCon = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
SqlCommand AuthorCmd = new SqlCommand("select YazarID from dbo.KitapYazar where KitapID = " + id.ToString(), AuthorCon);
AuthorCon.Open();
SqlDataReader AuthorReader = AuthorCmd.ExecuteReader();
List<Yazar> YazarList = new List<Yazar>();
while (AuthorReader.Read())
{
YazarList.Add(YazarIslemleri.getAuthor(AuthorReader.GetInt32(0)));
}
AuthorReader.Close();
AuthorCon.Close();
Yazar[] Yazarlar = null;
if (YazarList.Count > 0)
{
Yazarlar = new Yazar[YazarList.Count];
YazarList.CopyTo(Yazarlar);
}
Yayinevi Publisher = YayineviIslemleri.getPublisher(reader.GetInt32(3));
Kategori Category = KategoriIslemleri.getCategory(reader.GetInt32(4));
Kitap Current = new Kitap(id, reader.GetString(1), int.Parse(reader.GetString(2)), Yazarlar, Publisher, Category,
reader.GetInt32(5), reader.GetBoolean(7), reader.GetBoolean(6), reader.GetDecimal(8));
try
{
Current.Ozet = reader.GetString(9);
}
catch (SqlNullValueException snve)
{
Current.Ozet = "";
}
try
{
Current.Kapak = reader.GetString(10);
}
catch (SqlNullValueException snve)
{
Current.Kapak = "";
}
if (!Value.Contains(Current))
{
Value.Add(Current);
}
}
reader.Close();
con.Close();
}
return Value;
}
public static List<Kitap> search(string kitapAdi, Yazar[] yazarlar, int[] yillar)
{
List<Kitap> Value = new List<Kitap>();
SqlConnection con = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
String CommandString = "select ID,Adi,BasimYili,YayineviID,KategoriID,SayfaSayisi,HasarDurumu,KiralamaDurumu,Ucret,Ozet,Kapak ";
CommandString += "from dbo.Kitap join dbo.KitapYazar on dbo.Kitap.ID = dbo.KitapYazar.KitapID where ";
CommandString += "(Adi like '%" + kitapAdi + "%') and (";
int counter = 0;
foreach (Yazar item in yazarlar)
{
if (item.ID != 0)
{
CommandString += "dbo.KitapYazar.YazarID=" + item.ID.ToString() + " or ";
counter++;
}
}
if (counter > 0)
{
CommandString = CommandString.Remove(CommandString.Length - 4);
CommandString += ") and (";
}
foreach (int item in yillar)
{
CommandString += "dbo.Kitap.BasimYili='" + item.ToString() + "' or ";
}
CommandString = CommandString.Remove(CommandString.Length - 4);
CommandString += ")";
SqlCommand cmd = new SqlCommand(CommandString, con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
SqlConnection AuthorCon = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
SqlCommand AuthorCmd = new SqlCommand("select YazarID from dbo.KitapYazar where KitapID = " + id.ToString(), AuthorCon);
AuthorCon.Open();
SqlDataReader AuthorReader = AuthorCmd.ExecuteReader();
List<Yazar> YazarList = new List<Yazar>();
while (AuthorReader.Read())
{
YazarList.Add(YazarIslemleri.getAuthor(AuthorReader.GetInt32(0)));
}
AuthorReader.Close();
AuthorCon.Close();
Yazar[] Yazarlar = null;
if (YazarList.Count > 0)
{
Yazarlar = new Yazar[YazarList.Count];
YazarList.CopyTo(Yazarlar);
}
Yayinevi Publisher = YayineviIslemleri.getPublisher(reader.GetInt32(3));
Kategori Category = KategoriIslemleri.getCategory(reader.GetInt32(4));
Kitap Current = new Kitap(id, reader.GetString(1), int.Parse(reader.GetString(2)), Yazarlar, Publisher, Category,
reader.GetInt32(5), reader.GetBoolean(7), reader.GetBoolean(6), reader.GetDecimal(8));
try
{
Current.Ozet = reader.GetString(9);
}
catch (SqlNullValueException snve)
{
Current.Ozet = "";
}
try
{
Current.Kapak = reader.GetString(10);
}
catch (SqlNullValueException snve)
{
Current.Kapak = "";
}
if (!Value.Contains(Current))
{
Value.Add(Current);
}
}
reader.Close();
con.Close();
return Value;
}
public static List<Kitap> search(string kitapAdi)
{
List<Kitap> Value = new List<Kitap>();
SqlConnection con = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
String CommandString = "select * from dbo.Kitap where Adi like '%" + kitapAdi + "%'";
SqlCommand cmd = new SqlCommand(CommandString, con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
SqlConnection AuthorCon = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
SqlCommand AuthorCmd = new SqlCommand("select YazarID from dbo.KitapYazar where KitapID = " + id.ToString(), AuthorCon);
AuthorCon.Open();
SqlDataReader AuthorReader = AuthorCmd.ExecuteReader();
List<Yazar> YazarList = new List<Yazar>();
while (AuthorReader.Read())
{
YazarList.Add(YazarIslemleri.getAuthor(AuthorReader.GetInt32(0)));
}
AuthorReader.Close();
AuthorCon.Close();
Yazar[] Yazarlar = null;
if (YazarList.Count > 0)
{
Yazarlar = new Yazar[YazarList.Count];
YazarList.CopyTo(Yazarlar);
}
Yayinevi Publisher = YayineviIslemleri.getPublisher(reader.GetInt32(3));
Kategori Category = KategoriIslemleri.getCategory(reader.GetInt32(4));
Kitap Current = new Kitap(id, reader.GetString(1), int.Parse(reader.GetString(2)), Yazarlar, Publisher, Category,
reader.GetInt32(5), reader.GetBoolean(7), reader.GetBoolean(6), reader.GetDecimal(8));
try
{
Current.Ozet = reader.GetString(9);
}
catch (SqlNullValueException snve)
{
Current.Ozet = "";
}
try
{
Current.Kapak = reader.GetString(10);
}
catch (SqlNullValueException snve)
{
Current.Kapak = "";
}
Value.Add(Current);
}
reader.Close();
con.Close();
return Value;
}
public static bool record(Kitap value)
{
bool check = true;
SqlConnection con = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
String CommandString = "";
#region insert
if (value.ID == 0)
{
CommandString += "insert into dbo.Kitap ";
int[] YazarID = null;
if (value.Author != null)
{
YazarID = new int[value.Author.Length];
for (int i = 0; i < value.Author.Length; i++)
{
if (value.Author[i].ID != 0)
{
YazarID[i] = value.Author[i].ID;
}
else
{
int id = YazarIslemleri.record(value.Author[i]);
if (id != 0)
{
YazarID[i] = id;
}
else
{
check = false;
break;
}//iç if
}//dış if
}//for
}//en dış if
int YayineviID = 0;
if (check)
{
if (value.Publisher.ID != 0)
{
YayineviID = value.Publisher.ID;
}
else
{
int id = YayineviIslemleri.record(value.Publisher);
if (id != 0)
{
YayineviID = id;
}
else
{
check = false;
}
}
}
int KategoriID = 0;
if (check)
{
if (value.Category.ID != 0)
{
KategoriID = value.Category.ID;
}
else
{
int id = KategoriIslemleri.record(value.Category);
if (id != 0)
{
KategoriID = id;
}
else
{
check = false;
}
}
}
if (check)
{
if (String.IsNullOrEmpty(value.Kapak))
{
if (String.IsNullOrEmpty(value.Ozet))
{
CommandString += "(Adi,BasimYili,YayineviID,KategoriID,SayfaSayisi,HasarDurumu,KiralamaDurumu,Ucret) values(";
CommandString += "'" + value.Adi + "'";
CommandString += ",'" + value.BasimYili.ToString() + "'";
CommandString += "," + YayineviID.ToString();
CommandString += "," + KategoriID.ToString();
CommandString += "," + value.SayfaSayisi.ToString();
CommandString += "," + (value.HasarDurumu ? "1" : "0");
CommandString += "," + (value.KiralamaDurumu ? "1" : "0");
CommandString += "," + value.Fiyat.ToString().Replace(',', '.') + ")";
}
else
{
CommandString += "(Adi,BasimYili,YayineviID,KategoriID,SayfaSayisi,HasarDurumu,KiralamaDurumu,Ucret,Ozet) values(";
CommandString += "'" + value.Adi + "'";
CommandString += ",'" + value.BasimYili.ToString() + "'";
CommandString += "," + YayineviID.ToString();
CommandString += "," + KategoriID.ToString();
CommandString += "," + value.SayfaSayisi.ToString();
CommandString += "," + (value.HasarDurumu ? "1" : "0");
CommandString += "," + (value.KiralamaDurumu ? "1" : "0");
CommandString += "," + value.Fiyat.ToString().Replace(',', '.');
CommandString += ",'" + value.Ozet + "')";
}
}
else
{
if (String.IsNullOrEmpty(value.Ozet))
{
CommandString += "(Adi,BasimYili,YayineviID,KategoriID,SayfaSayisi,HasarDurumu,KiralamaDurumu,Ucret,Kapak) values(";
CommandString += "'" + value.Adi + "'";
CommandString += ",'" + value.BasimYili.ToString() + "'";
CommandString += "," + YayineviID.ToString();
CommandString += "," + KategoriID.ToString();
CommandString += "," + value.SayfaSayisi.ToString();
CommandString += "," + (value.HasarDurumu ? "1" : "0");
CommandString += "," + (value.KiralamaDurumu ? "1" : "0");
CommandString += "," + value.Fiyat.ToString().Replace(',', '.');
CommandString += ",'" + value.Kapak + "')";
}
else
{
CommandString += " values(";
CommandString += "'" + value.Adi + "'";
CommandString += ",'" + value.BasimYili.ToString() + "'";
CommandString += "," + YayineviID.ToString();
CommandString += "," + KategoriID.ToString();
CommandString += "," + value.SayfaSayisi.ToString();
CommandString += "," + (value.HasarDurumu ? "1" : "0");
CommandString += "," + (value.KiralamaDurumu ? "1" : "0");
CommandString += "," + value.Fiyat.ToString().Replace(',', '.');
CommandString += ",'" + value.Ozet + "'";
CommandString += ",'" + value.Kapak + "')";
}
}
try
{
SqlCommand cmd = new SqlCommand(CommandString, con);
con.Open();
int rows = cmd.ExecuteNonQuery();
if (rows == 1)
{
con.Close();
cmd = new SqlCommand("select ID from dbo.Kitap order by ID desc", con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
int KitapID = 0;
if (reader.Read())
{
KitapID = reader.GetInt32(0);
}
else
{
check = false;
}
reader.Close();
con.Close();
if (check && YazarID != null)
{
foreach (int item in YazarID)
{
cmd = new SqlCommand("insert into dbo.KitapYazar values(" + KitapID.ToString() + "," + item.ToString() + ")", con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
else
{
con.Close();
check = false;
}
}
catch (Exception ex)
{
check = false;
}
}
}
#endregion
#region update
else
{
CommandString += "update dbo.Kitap set ";
int[] YazarID = null;
if (value.Author != null)
{
YazarID = new int[value.Author.Length];
for (int i = 0; i < value.Author.Length; i++)
{
if (value.Author[i].ID != 0)
{
YazarID[i] = value.Author[i].ID;
}
else
{
int id = YazarIslemleri.record(value.Author[i]);
if (id != 0)
{
YazarID[i] = id;
}
else
{
check = false;
break;
}//iç if
}//dış if
}//for
}//en dış if
int YayineviID = 0;
if (check)
{
if (value.Publisher.ID != 0)
{
YayineviID = value.Publisher.ID;
}
else
{
int id = YayineviIslemleri.record(value.Publisher);
if (id != 0)
{
YayineviID = id;
}
else
{
check = false;
}
}
}
int KategoriID = 0;
if (check)
{
if (value.Category.ID != 0)
{
KategoriID = value.Category.ID;
}
else
{
int id = KategoriIslemleri.record(value.Category);
if (id != 0)
{
KategoriID = id;
}
else
{
check = false;
}
}
}
if (check)
{
if (String.IsNullOrEmpty(value.Kapak))
{
if (String.IsNullOrEmpty(value.Ozet))
{
CommandString += "Adi='" + value.Adi + "'";
CommandString += ",BasimYili='" + value.BasimYili.ToString() + "'";
CommandString += ",YayineviID=" + YayineviID.ToString();
CommandString += ",KategoriID=" + KategoriID.ToString();
CommandString += ",SayfaSayisi=" + value.SayfaSayisi.ToString();
CommandString += ",HasarDurumu=" + (value.HasarDurumu ? "1" : "0");
CommandString += ",KiralamaDurumu=" + (value.KiralamaDurumu ? "1" : "0");
CommandString += ",Ucret=" + value.Fiyat.ToString().Replace(',', '.');
}
else
{
CommandString += "Adi='" + value.Adi + "'";
CommandString += ",BasimYili='" + value.BasimYili.ToString() + "'";
CommandString += ",YayineviID=" + YayineviID.ToString();
CommandString += ",KategoriID=" + KategoriID.ToString();
CommandString += ",SayfaSayisi=" + value.SayfaSayisi.ToString();
CommandString += ",HasarDurumu=" + (value.HasarDurumu ? "1" : "0");
CommandString += ",KiralamaDurumu=" + (value.KiralamaDurumu ? "1" : "0");
CommandString += ",Ucret=" + value.Fiyat.ToString().Replace(',', '.');
CommandString += ",Ozet='" + value.Ozet + "'";
}
}
else
{
if (String.IsNullOrEmpty(value.Ozet))
{
CommandString += "Adi='" + value.Adi + "'";
CommandString += ",BasimYili='" + value.BasimYili.ToString() + "'";
CommandString += ",YayineviID=" + YayineviID.ToString();
CommandString += ",KategoriID=" + KategoriID.ToString();
CommandString += ",SayfaSayisi=" + value.SayfaSayisi.ToString();
CommandString += ",HasarDurumu=" + (value.HasarDurumu ? "1" : "0");
CommandString += ",KiralamaDurumu=" + (value.KiralamaDurumu ? "1" : "0");
CommandString += ",Ucret=" + value.Fiyat.ToString().Replace(',', '.');
CommandString += ",Kapak='" + value.Kapak + "'";
}
else
{
CommandString += "Adi='" + value.Adi + "'";
CommandString += ",BasimYili='" + value.BasimYili.ToString() + "'";
CommandString += ",YayineviID=" + YayineviID.ToString();
CommandString += ",KategoriID=" + KategoriID.ToString();
CommandString += ",SayfaSayisi=" + value.SayfaSayisi.ToString();
CommandString += ",HasarDurumu=" + (value.HasarDurumu ? "1" : "0");
CommandString += ",KiralamaDurumu=" + (value.KiralamaDurumu ? "1" : "0");
CommandString += ",Ucret=" + value.Fiyat.ToString().Replace(',', '.');
CommandString += ",Ozet='" + value.Ozet + "'";
CommandString += ",Kapak='" + value.Kapak + "'";
}
}
CommandString += " where ID = " + value.ID;
try
{
SqlCommand cmd = new SqlCommand(CommandString, con);
con.Open();
int rows = cmd.ExecuteNonQuery();
if (rows == 1 && YazarID != null)
{
con.Close();
cmd = new SqlCommand("delete from dbo.KitapYazar where KitapID=" + value.ID.ToString(), con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
foreach (int item in YazarID)
{
cmd = new SqlCommand("insert into dbo.KitapYazar values(" + value.ID.ToString() + "," + item.ToString() + ")", con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
else
{
con.Close();
check = false;
}
}
catch (Exception ex)
{
check = false;
}
}
}
#endregion
return check;
}
private void KitapEkle_Click(object sender, EventArgs e)
{
Kitap Yeni = new Kitap(null, (int)BasimYiliNUD.Value, null, null, null, 0, false, false, 0);
if (!String.IsNullOrEmpty(KitapTB.Text))
{
Yeni.Adi = KitapTB.Text;
}
if (BasimYiliList.Items.Count == 1)
{
Yeni.BasimYili = (int)BasimYiliList.Items[0];
}
if (!String.IsNullOrEmpty(YazarCB.Text))
{
String[] AdSoyad = YazarIslemleri.seperateName(YazarCB.Text);
String Ad = AdSoyad[0];
String Soyad = AdSoyad[1];
bool check = false;
foreach (Yazar item in YazarCB.Items)
{
if (item.Adi.Equals(Ad) && item.Soyadi.Equals(Soyad))
{
check = true;
break;
}
}
Yazar[] Yazarlar;
if (check)
{
Yazarlar = new Yazar[YazarList.Items.Count];
YazarList.Items.CopyTo(Yazarlar, 0);
}
else
{
Yazarlar = new Yazar[YazarList.Items.Count + 1];
YazarList.Items.CopyTo(Yazarlar, 0);
Yazarlar[YazarList.Items.Count] = new Yazar(Ad, Soyad);
}
Yeni.Author = Yazarlar;
}
else if (YazarList.Items.Count > 0)
{
Yazar[] Yazarlar = new Yazar[YazarList.Items.Count];
YazarList.Items.CopyTo(Yazarlar, 0);
Yeni.Author = Yazarlar;
}
Form2 EkleForm = new Form2("Yeni Kitap Ekle", Yeni);
EkleForm.ShowDialog();
refreshAuthors();
setList();
}
