private void setIlIlceList()
{
DataTable dtIl = dbEmlak.DataTableGetir("SELECT*FROM IL");
List <IL> Iller = new List <IL>();
Iller = (from DataRow row in dtIl.Rows
select new IL
{
ID = Convert.ToInt32(row["ID"].ToString()),
ADI = row["ADI"].ToString()
}).ToList();
DataTable dtIlce = dbEmlak.DataTableGetir("SELECT*FROM ILCE");
List <ILCE> Ilceler = new List <ILCE>();
Ilceler = (from DataRow row in dtIlce.Rows
select new ILCE
{
ID = Convert.ToInt32(row["ID"].ToString()),
ADI = row["ADI"].ToString()
}).ToList();
ViewBag.Iller = new SelectList(Iller, "ID", "ADI");
ViewBag.Ilceler = new SelectList(Ilceler, "ID", "ADI");
}
public DataTable getKitaplar(string _kitap)
{
return(db.DataTableGetir("SELECT KT.ADI AS KITAP_ADI,Y.ADI AS YAZAR,YE.ADI AS YAYIN_EVI,KT.TANITIM,COUNT(KO.KITAP_ID) AS OKUNMA_SAYISI, AVG(KP.PUAN) AS ORTALAMA_PUAN " +
"FROM KITAP AS KT, YAZAR AS Y, KITAP_YAZAR AS KY, YAYIN_EVI AS YE, KITAP_OKUNMA AS KO, KITAP_PUAN AS KP " +
"WHERE KT.ID = KY.KITAP_ID AND KY.YAZAR_ID = Y.ID AND KT.YAYIN_EVI_ID = Y.ID AND KT.ID = KO.KITAP_ID AND KT.ID = KP.KITAP_ID AND KT.ADI='" + _kitap + "' " +
"GROUP BY KT.ADI, Y.ADI, KT.TANITIM,YE.ADI"));
}
public DataTable getPopulerYazarlar()
{
return(dbPY.DataTableGetir("SELECT KY.YAZAR_ID,Y.ADI AS YAZAR_ADI,COUNT(KO.KITAP_ID) AS OKUNMA_SAYISI " +
"FROM KITAP_YAZAR AS KY, KITAP_OKUNMA AS KO, YAZAR AS Y " +
"WHERE KO.KITAP_ID = KY.KITAP_ID AND Y.ID = KY.YAZAR_ID " +
"GROUP BY KY.YAZAR_ID, Y.ADI ORDER BY COUNT(KO.KITAP_ID) DESC"));
}
public bool checkKullanici(string _kullanici_adi, string _sifre)
{
try
{
DataTable dt = dbLogin.DataTableGetir("SP_KULLANICI_GIRIS '" + _kullanici_adi + "','" + _sifre + "'");
if (dt.Rows.Count > 0)
{
KullaniciModel kullaniciModel = KullaniciModel.getKullanici();
kullaniciModel.KullaniciAdi = _kullanici_adi;
kullaniciModel.Resim = dt.Rows[0]["RESIM"].ToString();
kullaniciModel.Ad = dt.Rows[0]["ADI"].ToString();
kullaniciModel.Soyad = dt.Rows[0]["SOYADI"].ToString();
kullaniciModel.Cinsiyet = dt.Rows[0]["CINSIYET"].ToString();
kullaniciModel.Email = dt.Rows[0]["EMAIL"].ToString();
kullaniciModel.Telefon = dt.Rows[0]["TELEFON"].ToString();
kullaniciModel.Adres = dt.Rows[0]["ADRES"].ToString();
kullaniciModel.Sehir = dt.Rows[0]["SEHIR"].ToString();
kullaniciModel.YetkiID = Convert.ToInt32(dt.Rows[0]["YETKI_ID"].ToString());
return(true);
}
else
{
return(false);
}
}
catch (Exception)
{
return(false);
}
}
public ActionResult TakipKayit(IlanTakipModel _IlanTakipModel)
{
DataTable dt = db.DataTableGetir("TAKIP_KAYIT @UYE_ID=" + KULLANICI.GetKullanici().UYE_ID + ",@IL_ID=" + _IlanTakipModel.Adres.IL_ID + ",@ILCE_ID=" +
_IlanTakipModel.Adres.ILCE_ID + ",@DETAY ='" + _IlanTakipModel.Adres.DETAY + "',@KOORDINAT_X='" +
ILAN.LATITUDE + "',@KOORDINAT_Y='" + ILAN.LONGITUDE + "'");
return(RedirectToAction("Index", "Home"));
}
private void setMenu()
{
KullaniciModel kullaniciModel = KullaniciModel.getKullanici();
rptMenu.DataSource = db.DataTableGetir("SELECT*FROM MENU WHERE YETKI_ID=" + kullaniciModel.YetkiID);
rptMenu.DataBind();
}
public DataTable getMesajlar()
{
KullaniciModel kullaniciModel = new KullaniciModel();
return(dbPK.DataTableGetir("SELECT KI.ADI,M.BASLIK,M.MESAJ,M.TARIH FROM MESAJ AS M,KISI AS KI,KULLANICI AS KU " +
"WHERE M.GIDEN_ID = KI.ID AND KU.KISI_ID = KI.ID AND KU.ADI = '" + kullaniciModel.KullaniciAdi + "'"));
}
public ActionResult LoginAction(KULLANICI kullanici)
{
if (kullanici != null)
{
DataTable dt = dbEmlak.DataTableGetir("KULLANICI_KONTROL '" + kullanici.KULLANICI_ADI + "','" + kullanici.SIFRE + "'");
Model1 m = new Model1();
Boolean x = false;
if (dt.Rows.Count > 0)
{
string ka = dt.Rows[0]["KULLANICI_ADI"].ToString();
Session["yetkiId"] = dt.Rows[0]["YETKI_ID"];
KULLANICI Kullanici = KULLANICI.GetKullanici();
Kullanici.UYE_ID = Convert.ToInt32(dt.Rows[0]["UYE_ID"].ToString());
Kullanici.KULLANICI_ADI = dt.Rows[0]["KULLANICI_ADI"].ToString();
Kullanici.SIFRE = dt.Rows[0]["SIFRE"].ToString();
Kullanici.YETKI_ID = Convert.ToInt32(dt.Rows[0]["YETKI_ID"].ToString());
return(RedirectToAction("Index", "Home"));
}
else
{
ViewData["result"] = "Kullanıcı Bulunamadı";
}
}
return(View("Index"));
}
public DataTable getOkunanKitaplar(string _kullanici_adi)
{
return(db.DataTableGetir("SELECT DISTINCT KT.ADI AS KITAP_ADI,KP.PUAN,KI.INCELEME,KA.CUMLE AS ALINTI " +
"FROM KITAP_OKUNMA AS KO, KITAP AS KT, KULLANICI AS KU, KITAP_PUAN AS KP," +
"KITAP_INCELEME AS KI, KITAP_ALINTI AS KA,KISI " +
"WHERE KO.KITAP_ID = KT.ID AND KU.ID = KO.KULLANICI_ID AND KP.KITAP_ID = KO.KITAP_ID AND KT.ID = KI.KITAP_ID AND KT.ID = KA.KITAP_ID " +
"AND KISI.ID=KU.KISI_ID AND KISI.ADI+' '+KISI.SOYADI = '" + _kullanici_adi + "'"));
}
private void setAktifKullanici()
{
DataTable dt = db.DataTableGetir("SELECT K.UYE_ID,B.AD,B.SOYAD,Y.ADI AS YETKI,[KULLANICI_ADI] " +
"FROM[EMLAK].[dbo].[KULLANICI] AS K, UYE, BIREYSEL_UYE AS B, YETKI AS Y WHERE K.UYE_ID = UYE.ID AND UYE.UYE_ID = B.ID AND Y.ID = K.YETKI_ID AND UYE.UYE_DURUMU=1");
List <KullaniciListeModel> AktifKullanicilar = new List <KullaniciListeModel>();
AktifKullanicilar = (from DataRow row in dt.Rows
select new KullaniciListeModel
{
ID = Convert.ToInt32(row["UYE_ID"].ToString()),
AD = row["AD"].ToString(),
SOYAD = row["SOYAD"].ToString(),
YETKI = row["YETKI"].ToString(),
KULLANICI_ADI = row["KULLANICI_ADI"].ToString()
}).ToList();
ViewBag.AktifKullanicilar = AktifKullanicilar;
}
protected void btnMesaj_Click(object sender, EventArgs e)
{
DbBaglanti db = new DbBaglanti();
KullaniciModel kullanici = KullaniciModel.getKullanici();
db.DataTableGetir("MESAJ_GONDER '" + kullanici.KullaniciAdi + "','" + kullanici_adi + "','" + tbBaslik.Text + "','" + tbMesaj.Text + "'");
tbBaslik.Text = "";
tbMesaj.Text = "";
}
private void setIlanTalepleri()
{
DataTable dt = db.DataTableGetir("SELECT I.ID,I.BASLIK, IL.ADI AS IL, ILCE.ADI AS ILCE, A.DETAY,[YAYIN_SURESI],[FIYAT],[KOORDINAT_X],[KOORDINAT_Y] " +
"FROM[EMLAK].[dbo].[ILAN] AS I, ADRES AS A, IL, ILCE WHERE I.ADRES_ID = A.ID AND IL.ID = A.IL_ID AND ILCE.ID = A.ILCE_ID AND I.DURUM_ID=1");
List <IlanTalepModel> TalepEdilenIlanlar = new List <IlanTalepModel>();
TalepEdilenIlanlar = (from DataRow row in dt.Rows
select new IlanTalepModel
{
ID = Convert.ToInt32(row["ID"].ToString()),
BASLIK = row["BASLIK"].ToString(),
IL = row["IL"].ToString(),
ILCE = row["ILCE"].ToString(),
DETAY = row["DETAY"].ToString(),
YAYIN_SURESI = row["YAYIN_SURESI"].ToString(),
FIYAT = row["FIYAT"].ToString(),
KOORDINAT_X = row["KOORDINAT_X"].ToString(),
KOORDINAT_Y = row["KOORDINAT_Y"].ToString()
}).ToList();
ViewBag.TalepEdilenIlanlar = TalepEdilenIlanlar;
}
private void getEslesenTalepler()
{
DataTable dt = db.DataTableGetir("ESLESEN_TALEPLER '" + KULLANICI.GetKullanici().UYE_ID + "'");
List <EslesenTaleplerModel> EslesenTalepler = new List <EslesenTaleplerModel>();
EslesenTalepler = (from DataRow row in dt.Rows
select new EslesenTaleplerModel
{
ID = Convert.ToInt32(row["ID"].ToString()),
BASLIK = row["BASLIK"].ToString(),
KATEGORI = row["KATEGORI"].ToString(),
IL = row["IL"].ToString(),
ILCE = row["ILCE"].ToString(),
KOORDINAT_X = row["KOORDINAT_X"].ToString(),
KOORDINAT_Y = row["KOORDINAT_Y"].ToString()
}).ToList();
ViewBag.EslesenTalepler = EslesenTalepler;
}
private void getTakipListesi()
{
DataTable dt = db.DataTableGetir("SELECT T.[ID], IL.ADI AS IL, ILCE.ADI AS ILCE,[DETAY],[KOORDINAT_X],[KOORDINAT_Y] " +
"FROM[EMLAK].[dbo].[TAKIP] as T, IL, ILCE WHERE T.IL_ID = IL.ID AND T.ILCE_ID = ILCE.ID AND T.AKTIF=1");
List <TakipListesiModel> TakipListesi = new List <TakipListesiModel>();
TakipListesi = (from DataRow row in dt.Rows
select new TakipListesiModel
{
ID = Convert.ToInt32(row["ID"].ToString()),
IL = row["IL"].ToString(),
ILCE = row["ILCE"].ToString(),
DETAY = row["DETAY"].ToString(),
KOORDINAT_X = row["KOORDINAT_X"].ToString(),
KOORDINAT_Y = row["KOORDINAT_Y"].ToString()
}).ToList();
ViewBag.TakipListesi = TakipListesi;
}
public ActionResult UyeKayitAction(UyeKayitModel _UyeModel)
{
DbBaglanti dbBaglanti = new DbBaglanti();
DataTable dtResult = dbBaglanti.DataTableGetir("UYE_EKLE '" + _UyeModel.BireyselUye.AD + "','" + _UyeModel.BireyselUye.SOYAD + "','"
+ _UyeModel.BireyselUye.CINSIYET + "','" + _UyeModel.BireyselUye.TEL_NO + "'," + "'" + _UyeModel.BireyselUye.EMAIL + "','" +
_UyeModel.BireyselUye.ADRES + "','" + _UyeModel.BireyselUye.IL_ID + "','" + _UyeModel.BireyselUye.ILCE_ID + "','" +
_UyeModel.Kullanici.KULLANICI_ADI + "','" + _UyeModel.Kullanici.SIFRE + "'");
if (dtResult.Rows[0]["RESULT"].ToString() == "KAYIT BAŞARILI")
{
ViewData["result"] = dtResult.Rows[0]["RESULT"].ToString();
return(View("Index"));
}
else
{
ViewData["result"] = dtResult.Rows[0]["RESULT"].ToString();
return(View("UyeKayit"));
}
}
private void getBireyselUye()
{
DataTable dtUye = dbEmlak.DataTableGetir("SELECT B.ID,B.AD,B.SOYAD,B.CINSIYET,B.TEL_NO,B.EMAIL,B.ADRES FROM [EMLAK].[dbo].[UYE]," +
"BIREYSEL_UYE AS B WHERE UYE.UYE_ID=B.ID AND UYE.ID=" + KULLANICI.GetKullanici().UYE_ID);
List <BIREYSEL_UYE> BireyselUye = new List <BIREYSEL_UYE>();
BireyselUye = (from DataRow row in dtUye.Rows
select new BIREYSEL_UYE
{
ID = Convert.ToInt32(row["ID"].ToString()),
AD = row["AD"].ToString(),
SOYAD = row["SOYAD"].ToString(),
CINSIYET = row["CINSIYET"].ToString(),
TEL_NO = row["TEL_NO"].ToString(),
EMAIL = row["EMAIL"].ToString(),
ADRES = row["ADRES"].ToString()
}).ToList();
ViewBag.BireyselUye = BireyselUye[0];
}
private void getAktifIlanlar()
{
DataTable dtAktifIlanlar = db.DataTableGetir("SELECT I.[ID],B.AD,B.SOYAD,IL.ADI AS IL,ILCE.ADI AS ILCE,K.ADI AS KATEGORI,[BASLIK],[YAYIN_SURESI],[FIYAT],[ACIKLAMA] " +
"FROM[EMLAK].[dbo].[ILAN] AS I, ADRES AS A, KATEGORI AS K, UYE, BIREYSEL_UYE AS B, IL, ILCE " +
"WHERE I.ADRES_ID = A.ID AND I.KATEGORI_ID = K.ID AND I.UYE_ID = UYE.ID AND UYE.UYE_ID = B.ID AND IL.ID = A.IL_ID AND ILCE.ID = A.ILCE_ID AND I.DURUM_ID = 2");
List <IlanDashboard> Ilanlar = new List <IlanDashboard>();
Ilanlar = (from DataRow row in dtAktifIlanlar.Rows
select new IlanDashboard
{
ID = Convert.ToInt32(row["ID"].ToString()),
AD = row["AD"].ToString(),
SOYAD = row["SOYAD"].ToString(),
IL = row["IL"].ToString(),
ILCE = row["ILCE"].ToString(),
KATEGORI = row["KATEGORI"].ToString(),
BASLIK = row["BASLIK"].ToString(),
YAYIN_SURESI = row["YAYIN_SURESI"].ToString(),
FIYAT = row["FIYAT"].ToString(),
ACIKLAMA = row["ACIKLAMA"].ToString()
}).ToList();
ViewBag.Ilanlar = Ilanlar;
}
public DataTable getYuksekPuanliKitaplar()
{
return(db.DataTableGetir("SELECT KT.ADI AS KITAP_ADI,AVG(PUAN) AS ORTALAMA_PUAN " +
"FROM KITAP_PUAN AS KP,KITAP AS KT WHERE KT.ID=KP.KITAP_ID GROUP BY KT.ADI,PUAN ORDER BY PUAN DESC"));
}
public ActionResult KullaniciIslem(string id, string islem)
{
db.DataTableGetir("UPDATE [EMLAK].[dbo].[UYE] SET UYE_DURUMU='" + islem + "' WHERE ID='" + id + "'");
return(RedirectToAction("Index", "KullaniciListele"));
}
public DataTable getKitaplar(string _kitap)
{
return(db.DataTableGetir("SELECT ID,ADI FROM KITAP WHERE ADI LIKE '%" + _kitap + "%' "));
}
public DataTable getYazarlar(string _yazar)
{
return(db.DataTableGetir("SELECT ID,ADI+' '+SOYADI AS YAZAR_ADI FROM YAZAR WHERE ADI+' '+SOYADI LIKE '%" + _yazar + "%' "));
}
public DataTable getOrtakKitapOneri()
{
KullaniciModel kullanici = KullaniciModel.getKullanici();
return(db.DataTableGetir("SP_GET_ORTAK_KITAP_ONERI '" + kullanici.KullaniciAdi + "'"));
}
public DataTable getKullanicilar(string _kullanici)
{
return(db.DataTableGetir("SELECT ID,ADI +' '+SOYADI AS KULLANICI_ADI FROM KISI WHERE ADI +' '+SOYADI LIKE '%" + _kullanici + "%'"));
}
public DataTable getPopulerKitaplar()
{
return(dbPK.DataTableGetir("SELECT KT.ADI AS KITAP_ADI,COUNT(KITAP_ID) AS OKUNMA_SAYISI " +
"FROM KITAP_OKUNMA AS KO,KITAP AS KT WHERE KT.ID=KO.KITAP_ID GROUP BY KT.ADI ORDER BY OKUNMA_SAYISI DESC"));
}
public void insertKitap(string _yayin_evi, string _yayin_evi_adres, string _yazar_adi, string _yazar_soyadi, string _kitap_adi
, string _isbn, string _sayfa_sayisi, string _yayin_tarihi, string _kitap_tanitim)
{
dbKT.DataTableGetir("SP_INSERT_KITAP '" + _yayin_evi + "','" + _yayin_evi_adres + "','" + _yazar_adi
+ "','" + _yazar_soyadi + "','" + _kitap_adi + "','" + _isbn + "','" + _sayfa_sayisi + "','" + _yayin_tarihi + "','" + _kitap_tanitim + "'");
}
