public static List<Odeme> getPayments(DateTime First, DateTime Last, Bilesen Type)
{
List<Odeme> Value = new List<Odeme>();
SqlConnection con = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
String CommandString = "select Tarih,Uye.Adi,Uye.Soyadi,Aciklama,Tutar";
if(Type == null)
{
CommandString += ",OdemeTipi.Adi";
CommandString += " from dbo.Odeme";
CommandString += " join dbo.Uye on Uye.ID = Odeme.UyeID";
CommandString += " join dbo.OdemeTipi on OdemeTipi.ID = Odeme.OdemeTipi";
CommandString += " where (Tarih between ";
CommandString += "'" + convertToSqlDate(First.ToShortDateString()) + "' and ";
CommandString += "'" + convertToSqlDate(Last.ToShortDateString()) + "')";
SqlCommand cmd = new SqlCommand(CommandString, con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Odeme Current = new Odeme(reader.GetDateTime(0), reader.GetString(1), reader.GetString(2),
reader.GetString(3), reader.GetDecimal(4), reader.GetString(5));
Value.Add(Current);
}
reader.Close();
con.Close();
}
else
{
CommandString += " from dbo.Odeme";
CommandString += " join dbo.Uye on Uye.ID = Odeme.UyeID";
CommandString += " join dbo.OdemeTipi on OdemeTipi.ID = Odeme.OdemeTipi";
CommandString += " where (Tarih between ";
CommandString += "'" + convertToSqlDate(First.ToShortDateString()) + "' and ";
CommandString += "'" + convertToSqlDate(Last.ToShortDateString()) + "')";
CommandString += " and (Odeme.OdemeTipi=" + Type.ID.ToString() + ")";
SqlCommand cmd = new SqlCommand(CommandString, con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Odeme Current = new Odeme(reader.GetDateTime(0), reader.GetString(1), reader.GetString(2),
reader.GetString(3), reader.GetDecimal(4), "");
Value.Add(Current);
}
reader.Close();
con.Close();
}
return Value;
}
public static List<Bilesen> getPayTypes()
{
List<Bilesen> Value = new List<Bilesen>();
SqlConnection con = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
SqlCommand cmd = new SqlCommand("select * from dbo.OdemeTipi", con);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Bilesen Current = new Bilesen(reader.GetInt32(0), reader.GetString(1));
Value.Add(Current);
}
reader.Close();
con.Close();
return Value;
}
public static bool teslimAl(int kiralamaID, Decimal Gecikme, Decimal Hasar, Bilesen OdemeTipi)
{
bool check = true;
SqlConnection con = new SqlConnection("Server=.;Database=KutuphaneOtomasyon;Trusted_Connection=true");
int KitapID=0,UyeID = 0;
SqlCommand cmd = new SqlCommand("select KitapID,UyeID from dbo.Kiralama where ID=" + kiralamaID.ToString(), con);
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
KitapID = reader.GetInt32(0);
UyeID = reader.GetInt32(1);
}
else
{
check = false;
}
reader.Close();
con.Close();
String CommandString = "";
int KiraID = 0;
if (check)
{
CommandString = "insert into dbo.Odeme values(";
CommandString += "'" + RaporIslemleri.convertToSqlDate(DateTime.Now.ToShortDateString()) + "'";
CommandString += "," + UyeID.ToString();
CommandString += ",'Kira Bedeli'";
CommandString += ",1";
CommandString += "," + OdemeTipi.ID.ToString() + ")";
cmd = new SqlCommand(CommandString, con);
con.Open();
int rows = cmd.ExecuteNonQuery();
con.Close();
if (rows == 1)
{
cmd = new SqlCommand("select ID from dbo.Odeme order by ID desc", con);
con.Open();
reader = cmd.ExecuteReader();
if (reader.Read())
{
KiraID = reader.GetInt32(0);
}
else
{
check = false;
}
reader.Close();
con.Close();
}
else
{
check = false;
}
}
if (check)
{
if (Gecikme > 0)
{
int GecikmeID = 0;
CommandString = "insert into dbo.Odeme values(";
CommandString += "'" + RaporIslemleri.convertToSqlDate(DateTime.Now.ToShortDateString()) + "'";
CommandString += "," + UyeID.ToString();
CommandString += ",'Gecikme Bedeli'";
CommandString += "," + Gecikme.ToString().Replace(',', '.');
CommandString += "," + OdemeTipi.ID.ToString() + ")";
cmd = new SqlCommand(CommandString, con);
con.Open();
int rows = cmd.ExecuteNonQuery();
con.Close();
if (rows == 1)
{
cmd = new SqlCommand("select ID from dbo.Odeme order by ID desc", con);
con.Open();
reader = cmd.ExecuteReader();
if (reader.Read())
{
GecikmeID = reader.GetInt32(0);
}
else
{
check = false;
}
reader.Close();
con.Close();
}
else
{
check = false;
}
if (check)
{
if (Hasar > 0)
{
int HasarID = 0;
CommandString = "insert into dbo.Odeme values(";
CommandString += "'" + RaporIslemleri.convertToSqlDate(DateTime.Now.ToShortDateString()) + "'";
CommandString += "," + UyeID.ToString();
CommandString += ",'Hasar Bedeli'";
CommandString += "," + Hasar.ToString().Replace(',', '.');
CommandString += "," + OdemeTipi.ID.ToString() + ")";
cmd = new SqlCommand(CommandString, con);
con.Open();
rows = cmd.ExecuteNonQuery();
con.Close();
if (rows == 1)
{
cmd = new SqlCommand("select ID from dbo.Odeme order by ID desc", con);
con.Open();
reader = cmd.ExecuteReader();
if (reader.Read())
{
HasarID = reader.GetInt32(0);
}
else
{
check = false;
}
reader.Close();
con.Close();
}
else
{
check = false;
}
if (check)
{
CommandString = "update dbo.Kiralama set TeslimDurumu=1 where ID=" + kiralamaID.ToString();
CommandString += " update dbo.KiralamaDetay set TeslimTarihi='" + RaporIslemleri.convertToSqlDate(DateTime.Now.ToString()) + "'";
CommandString += ",Deformasyon=1";
CommandString += ",KiraOdemesi=" + KiraID.ToString();
CommandString += ",GecikmeOdemesi=" + GecikmeID.ToString();
CommandString += ",HasarOdemesi=" + HasarID.ToString();
CommandString += " where ID=" + kiralamaID.ToString();
CommandString += " update dbo.Kitap set HasarDurumu=1";
CommandString += ",KiralamaDurumu=0";
CommandString += " where ID=" + KitapID.ToString();
cmd = new SqlCommand(CommandString, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
else
{
CommandString = "update dbo.Kiralama set TeslimDurumu=1 where ID=" + kiralamaID.ToString();
CommandString += " update dbo.KiralamaDetay set TeslimTarihi='" + RaporIslemleri.convertToSqlDate(DateTime.Now.ToString()) + "'";
CommandString += ",Deformasyon=0";
CommandString += ",KiraOdemesi=" + KiraID.ToString();
CommandString += ",GecikmeOdemesi=" + GecikmeID.ToString();
CommandString += " where ID=" + kiralamaID.ToString();
CommandString += " update dbo.Kitap set KiralamaDurumu=0";
CommandString += " where ID=" + KitapID.ToString();
cmd = new SqlCommand(CommandString, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
else
{
if (Hasar > 0)
{
int HasarID = 0;
CommandString = "insert into dbo.Odeme values(";
CommandString += "'" + RaporIslemleri.convertToSqlDate(DateTime.Now.ToShortDateString()) + "'";
CommandString += "," + UyeID.ToString();
CommandString += ",'Hasar Bedeli'";
CommandString += "," + Hasar.ToString().Replace(',', '.');
CommandString += "," + OdemeTipi.ID.ToString() + ")";
cmd = new SqlCommand(CommandString, con);
con.Open();
int rows = cmd.ExecuteNonQuery();
con.Close();
if (rows == 1)
{
cmd = new SqlCommand("select ID from dbo.Odeme order by ID desc", con);
con.Open();
reader = cmd.ExecuteReader();
if (reader.Read())
{
HasarID = reader.GetInt32(0);
}
else
{
check = false;
}
reader.Close();
con.Close();
}
else
{
check = false;
}
if (check)
{
CommandString = "update dbo.Kiralama set TeslimDurumu=1 where ID=" + kiralamaID.ToString();
CommandString += " update dbo.KiralamaDetay set TeslimTarihi='" + RaporIslemleri.convertToSqlDate(DateTime.Now.ToString()) + "'";
CommandString += ",Deformasyon=1";
CommandString += ",KiraOdemesi=" + KiraID.ToString();
CommandString += ",HasarOdemesi=" + HasarID.ToString();
CommandString += " where ID=" + kiralamaID.ToString();
CommandString += " update dbo.Kitap set HasarDurumu=1";
CommandString += ",KiralamaDurumu=0";
CommandString += " where ID=" + KitapID.ToString();
cmd = new SqlCommand(CommandString, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
else
{
CommandString = "update dbo.Kiralama set TeslimDurumu=1 where ID=" + kiralamaID.ToString();
CommandString += " update dbo.KiralamaDetay set TeslimTarihi='" + RaporIslemleri.convertToSqlDate(DateTime.Now.ToString()) + "'";
CommandString += ",Deformasyon=0";
CommandString += ",KiraOdemesi=" + KiraID.ToString();
CommandString += " where ID=" + kiralamaID.ToString();
CommandString += " update dbo.Kitap set KiralamaDurumu=0";
CommandString += " where ID=" + KitapID.ToString();
cmd = new SqlCommand(CommandString, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}
catch (Exception ex)
{
check = false;
}
return check;
}