public ActionResult getAracPlakaLsistesi()
{
string connectionString = "", sql;
string ilkodu = "71";
//sql = "SELECT DISTINCT (ILCE_ADI) FROM GIS_ILCE WHERE BOLGE_NO ='" + bolgeKodu + "' and IL_KODU ='" + ilkodu + "' ";
sql = "SELECT DISTINCT (PLAKA) FROM KIRGAZ.ABO_PER_MUA_IS_EMRI WHERE PLAKA IS NOT NULL ";
if (ilkodu == "71")
{
connectionString = ConfigurationManager.ConnectionStrings["kirikkale"].ToString();
}
else
{
connectionString = ConfigurationManager.ConnectionStrings["kirsehir"].ToString();
}
DbBaglanti test = new DbBaglanti();
OracleConnection bgln = test.OracleConnect(connectionString);
//OracleConnection bgln = test.OracleConnect(kirikkaleDb);
DataTable dt = test.GetDataTable(sql, bgln);
string jsonString = JsonConvert.SerializeObject(dt);
System.Diagnostics.Debug.Print("###################################### " + jsonString);
dt.Dispose();
return(this.Content(jsonString, "application/json"));
}
public ActionResult getIlceListesi(string bolgeAdi, string ilAdi)
{
string connectionString = "", sql;
string ilkodu = "", bolgeKodu = "";
System.Diagnostics.Debug.Print("###################################### " + " " + bolgeAdi + " " + ilAdi);
ilkodu = "71";
if (!string.IsNullOrWhiteSpace(ilAdi))
{
if (ilAdi.Equals("Kırıkkale"))
{
ilkodu = "71";
}
}
if (!string.IsNullOrWhiteSpace(bolgeAdi))
{
bolgeKodu = bolgeAdi.Split('-')[0];
}
//sql = "SELECT DISTINCT (ILCE_ADI) FROM GIS_ILCE WHERE BOLGE_NO ='" + bolgeKodu + "' and IL_KODU ='" + ilkodu + "' ";
sql = "SELECT DISTINCT (ILCE_ADI) FROM GIS_ILCE WHERE ILCE_KODU in (select DISTINCT (ILCE) from ABO_BOLGE where BOLGE_KODU ='" + bolgeKodu + "') and IL_KODU ='" + ilkodu + "' ";
if (ilkodu == "71")
{
connectionString = ConfigurationManager.ConnectionStrings["kirikkale"].ToString();
}
else
{
connectionString = ConfigurationManager.ConnectionStrings["kirsehir"].ToString();
}
DbBaglanti test = new DbBaglanti();
OracleConnection bgln = test.OracleConnect(connectionString);
//OracleConnection bgln = test.OracleConnect(kirikkaleDb);
DataTable dt = test.GetDataTable(sql, bgln);
string jsonString = JsonConvert.SerializeObject(dt);
System.Diagnostics.Debug.Print("###################################### " + jsonString);
dt.Dispose();
return(this.Content(jsonString, "application/json"));
}
public ActionResult getFotografBilgisi(string aboneNo)
{
string connectionString = "", sql;
string ilkodu = "";
System.Diagnostics.Debug.Print("###################################### " + " " + aboneNo);
ilkodu = "71";
sql = "SELECT FOTOGRAF FROM ABO_PER_MUA_IS_EMRI x WHERE ABONE_ID ='" + aboneNo + "'";
List <ImageModel> images = new List <ImageModel>();
if (ilkodu == "71")
{
connectionString = ConfigurationManager.ConnectionStrings["kirikkale"].ToString();
}
else
{
connectionString = ConfigurationManager.ConnectionStrings["kirsehir"].ToString();
}
DbBaglanti test = new DbBaglanti();
OracleConnection bgln = test.OracleConnect(connectionString);
//OracleConnection bgln = test.OracleConnect(kirikkaleDb);
DataTable dataTable = test.GetDataTable(sql, bgln);
foreach (DataRow dataRow in dataTable.Rows)
{
byte[] blob = (byte[])dataRow[0];
string imageBase64Data = Convert.ToBase64String(blob);
string imageDataURL = string.Format("data:image/png;base64,{0}", imageBase64Data);
ViewBag.ImageData = imageDataURL;
}
return(View());
}
public ActionResult getBolgeList()
{
string connectionString = "", sql;
string ilkodu = "71";
// sql = "SELECT DISTINCT (x.BOLGE_KODU), x.BOLGE_ADI FROM ABO_BOLGE x ";
sql = "select DISTINCT (b.BOLGE_KODU), b.BOLGE_ADI from ABO_BOLGE b, gis_ilce g where g.ilce_kodu = b.ilce";
if (ilkodu == "71")
{
connectionString = ConfigurationManager.ConnectionStrings["kirikkale"].ToString();
}
else
{
connectionString = ConfigurationManager.ConnectionStrings["kirsehir"].ToString();
}
DbBaglanti test = new DbBaglanti();
OracleConnection bgln = test.OracleConnect(connectionString);
//OracleConnection bgln = test.OracleConnect(kirikkaleDb);
DataTable dt = test.GetDataTable(sql, bgln);
string jsonString = JsonConvert.SerializeObject(dt);
System.Diagnostics.Debug.Print("################################ " + jsonString);
dt.Dispose();
return(this.Content(jsonString, "application/json"));
}
public ActionResult getGonderilenSMSListesi()
{
AESCrypt aes = new AESCrypt();
string connectionString = "", sql = "";
string ilkodu = "71";
/*
* <th>ABONE_ID</th>
* <th>ABONE_ADI</th>
* <th>ABONE_SOYADI</th>
* <th>CEP_TEL</th>
* <th>Fatura SMS</th>
* <th>Fatura SMS tarihi</th>
* <th>Fatura SMS ID</th>
* <th>İhbar SMS</th>
* <th>İhbar SMS tarihi</th>
* <th>İhbar SMS ID</th>
*/
if (Request.Form != null && Request.Form.Count > 0)
{
string tarihAraligi = Request.Form["zamanAraligi"];
string smsTuru = Request.Form["SMSTuruSelector"];
System.Diagnostics.Debug.Print("%%%%%%%%%%%%%%%%%%%%%% " + smsTuru);
string baslamaTarih = tarihAraligi.Split('-')[0].Replace('/', '.');
string bitirmeTarih = tarihAraligi.Split('-')[1].Replace('/', '.');
baslamaTarih = new string(baslamaTarih.Where(c => !char.IsWhiteSpace(c)).ToArray());
StringBuilder s = new StringBuilder();
s.Append(baslamaTarih.Split('.')[2]).Append(".").Append(baslamaTarih.Split('.')[0]).Append(".").Append(baslamaTarih.Split('.')[1]);
baslamaTarih = s.ToString();
System.Diagnostics.Debug.Print("%%%%%%%%%%%%%%%%%%%%%% " + baslamaTarih);
bitirmeTarih = new string(bitirmeTarih.Where(c => !char.IsWhiteSpace(c)).ToArray());
s = new StringBuilder();
s.Append(bitirmeTarih.Split('.')[2]).Append(".").Append(bitirmeTarih.Split('.')[0]).Append(".").Append(bitirmeTarih.Split('.')[1]);
bitirmeTarih = s.ToString();
System.Diagnostics.Debug.Print("%%%%%%%%%%%%%%%%%%%%%% " + bitirmeTarih);
if (smsTuru.Equals(""))
{
sql = "SELECT x.ABONE_ID, a.ABONE_ADI , a.ABONE_SOYADI, a.CEP_TEL, x.SMS_BILGI, x.FATURA_SMS_ID, x.FATURA_SMS_GONDERME_ZAMAN, x.SMS_IHBAR, x.IHBAR_SMS_ID, x.IHBAR_SMS_GONDERME_ZAMAN FROM ABO_ENDEKS x, ABO_ABONE_SAHIS a, ABO_ABONE b WHERE x.ABONE_ID = b.ABONE_ID AND a.ABONE_SAHIS_NO = b.ABONE_SAHIS_NO AND x.SMS_BILGI IS NOT NULL AND x.DONEM_AY = (SELECT MAX(DONEM_AY) FROM ABO_DONEM WHERE DONEM_YIL = (SELECT MAX(DONEM_YIL) FROM ABO_DONEM )) AND x.DONEM_YIL = (SELECT max(DONEM_YIL) FROM ABO_DONEM) and FATURA_SMS_GONDERME_ZAMAN BETWEEN to_date('" + baslamaTarih + "%','yyyy-MM-dd HH24:mi:ss') AND to_date('" + bitirmeTarih + "%','yyyy-MM-dd HH24:mi:ss')";
}
else
{
string sorgulanacakSMSTuru = faturaSMSTuruMapping(smsTuru);
switch (sorgulanacakSMSTuru)
{
case "0":
sql = "SELECT x.ABONE_ID, a.ABONE_ADI , a.ABONE_SOYADI, a.CEP_TEL, x.SMS_BILGI, x.FATURA_SMS_ID, x.FATURA_SMS_GONDERME_ZAMAN, x.SMS_IHBAR, x.IHBAR_SMS_ID, x.IHBAR_SMS_GONDERME_ZAMAN FROM ABO_ENDEKS x, ABO_ABONE_SAHIS a, ABO_ABONE b WHERE x.ABONE_ID = b.ABONE_ID AND a.ABONE_SAHIS_NO = b.ABONE_SAHIS_NO AND x.SMS_BILGI IS NOT NULL AND x.DONEM_AY = (SELECT MAX(DONEM_AY) FROM ABO_DONEM WHERE DONEM_YIL = (SELECT MAX(DONEM_YIL) FROM ABO_DONEM )) AND x.DONEM_YIL = (SELECT max(DONEM_YIL) FROM ABO_DONEM) and x.SON_OKUMA_TARIHI BETWEEN to_date('" + baslamaTarih + " 00:00:00','yyyy-MM-dd HH24:mi:ss') AND to_date('" + bitirmeTarih + " 23:59:00','yyyy-MM-dd HH24:mi:ss') and x.SMS_BILGI ='0'";
break;
case "1":
sql = "SELECT x.ABONE_ID, a.ABONE_ADI , a.ABONE_SOYADI, a.CEP_TEL, x.SMS_BILGI, x.FATURA_SMS_ID, x.FATURA_SMS_GONDERME_ZAMAN, x.SMS_IHBAR, x.IHBAR_SMS_ID, x.IHBAR_SMS_GONDERME_ZAMAN FROM ABO_ENDEKS x, ABO_ABONE_SAHIS a, ABO_ABONE b WHERE x.ABONE_ID = b.ABONE_ID AND a.ABONE_SAHIS_NO = b.ABONE_SAHIS_NO AND x.SMS_BILGI IS NOT NULL AND x.DONEM_AY = (SELECT MAX(DONEM_AY) FROM ABO_DONEM WHERE DONEM_YIL = (SELECT MAX(DONEM_YIL) FROM ABO_DONEM )) AND x.DONEM_YIL = (SELECT max(DONEM_YIL) FROM ABO_DONEM) and FATURA_SMS_GONDERME_ZAMAN BETWEEN to_date('" + baslamaTarih + " 00:00:00','yyyy-MM-dd HH24:mi:ss') AND to_date('" + bitirmeTarih + " 23:59:00','yyyy-MM-dd HH24:mi:ss') and x.SMS_BILGI ='1'";
break;
case "2":
sql = "SELECT x.ABONE_ID, a.ABONE_ADI , a.ABONE_SOYADI, a.CEP_TEL, x.SMS_BILGI, x.FATURA_SMS_ID, x.FATURA_SMS_GONDERME_ZAMAN, x.SMS_IHBAR, x.IHBAR_SMS_ID, x.IHBAR_SMS_GONDERME_ZAMAN FROM ABO_ENDEKS x, ABO_ABONE_SAHIS a, ABO_ABONE b WHERE x.ABONE_ID = b.ABONE_ID AND a.ABONE_SAHIS_NO = b.ABONE_SAHIS_NO AND x.SMS_BILGI IS NOT NULL AND x.DONEM_AY = (SELECT MAX(DONEM_AY) FROM ABO_DONEM WHERE DONEM_YIL = (SELECT MAX(DONEM_YIL) FROM ABO_DONEM )) AND x.DONEM_YIL = (SELECT max(DONEM_YIL) FROM ABO_DONEM) and FATURA_SMS_GONDERME_ZAMAN BETWEEN to_date('" + baslamaTarih + " 00:00:00','yyyy-MM-dd HH24:mi:ss') AND to_date('" + bitirmeTarih + " 23:59:00','yyyy-MM-dd HH24:mi:ss') and x.SMS_BILGI ='2'";
break;
case "3":
sql = "SELECT x.ABONE_ID, a.ABONE_ADI , a.ABONE_SOYADI, a.CEP_TEL, x.SMS_BILGI, x.FATURA_SMS_ID, x.FATURA_SMS_GONDERME_ZAMAN, x.SMS_IHBAR, x.IHBAR_SMS_ID, x.IHBAR_SMS_GONDERME_ZAMAN FROM ABO_ENDEKS x, ABO_ABONE_SAHIS a, ABO_ABONE b WHERE x.ABONE_ID = b.ABONE_ID AND a.ABONE_SAHIS_NO = b.ABONE_SAHIS_NO AND x.SMS_BILGI IS NOT NULL AND x.DONEM_AY = (SELECT MAX(DONEM_AY) FROM ABO_DONEM WHERE DONEM_YIL = (SELECT MAX(DONEM_YIL) FROM ABO_DONEM )) AND x.DONEM_YIL = (SELECT max(DONEM_YIL) FROM ABO_DONEM) and FATURA_SMS_GONDERME_ZAMAN BETWEEN to_date('" + baslamaTarih + " 00:00:00','yyyy-MM-dd HH24:mi:ss') AND to_date('" + bitirmeTarih + " 23:59:00','yyyy-MM-dd HH24:mi:ss') and x.SMS_BILGI ='3'";
break;
case "4":
sql = "SELECT x.ABONE_ID, a.ABONE_ADI , a.ABONE_SOYADI, a.CEP_TEL, x.SMS_BILGI, x.FATURA_SMS_ID, x.FATURA_SMS_GONDERME_ZAMAN, x.SMS_IHBAR, x.IHBAR_SMS_ID, x.IHBAR_SMS_GONDERME_ZAMAN FROM ABO_ENDEKS x, ABO_ABONE_SAHIS a, ABO_ABONE b WHERE x.ABONE_ID = b.ABONE_ID AND a.ABONE_SAHIS_NO = b.ABONE_SAHIS_NO AND x.SMS_BILGI IS NOT NULL AND x.DONEM_AY = (SELECT MAX(DONEM_AY) FROM ABO_DONEM WHERE DONEM_YIL = (SELECT MAX(DONEM_YIL) FROM ABO_DONEM )) AND x.DONEM_YIL = (SELECT max(DONEM_YIL) FROM ABO_DONEM) and FATURA_SMS_GONDERME_ZAMAN BETWEEN to_date('" + baslamaTarih + " 00:00:00','yyyy-MM-dd HH24:mi:ss') AND to_date('" + bitirmeTarih + " 23:59:00','yyyy-MM-dd HH24:mi:ss') and x.SMS_BILGI ='4'";
break;
case "5":
sql = "SELECT x.ABONE_ID, a.ABONE_ADI , a.ABONE_SOYADI, a.CEP_TEL, x.SMS_BILGI, x.FATURA_SMS_ID, x.FATURA_SMS_GONDERME_ZAMAN, x.SMS_IHBAR, x.IHBAR_SMS_ID, x.IHBAR_SMS_GONDERME_ZAMAN FROM ABO_ENDEKS x, ABO_ABONE_SAHIS a, ABO_ABONE b WHERE x.ABONE_ID = b.ABONE_ID AND a.ABONE_SAHIS_NO = b.ABONE_SAHIS_NO AND x.SMS_BILGI IS NOT NULL AND x.DONEM_AY = (SELECT MAX(DONEM_AY) FROM ABO_DONEM WHERE DONEM_YIL = (SELECT MAX(DONEM_YIL) FROM ABO_DONEM )) AND x.DONEM_YIL = (SELECT max(DONEM_YIL) FROM ABO_DONEM) and FATURA_SMS_GONDERME_ZAMAN BETWEEN to_date('" + baslamaTarih + " 00:00:00','yyyy-MM-dd HH24:mi:ss') AND to_date('" + bitirmeTarih + " 23:59:00','yyyy-MM-dd HH24:mi:ss') and x.SMS_BILGI ='5'";
break;
case "6":
//ihbar sms gonderildigi ise
sql = "SELECT x.ABONE_ID, a.ABONE_ADI , a.ABONE_SOYADI, a.CEP_TEL, x.SMS_BILGI, x.FATURA_SMS_ID, x.FATURA_SMS_GONDERME_ZAMAN, x.SMS_IHBAR, x.IHBAR_SMS_ID, x.IHBAR_SMS_GONDERME_ZAMAN FROM ABO_ENDEKS x, ABO_ABONE_SAHIS a, ABO_ABONE b WHERE x.ABONE_ID = b.ABONE_ID AND a.ABONE_SAHIS_NO = b.ABONE_SAHIS_NO AND x.SMS_BILGI IS NOT NULL AND x.DONEM_AY = (SELECT MAX(DONEM_AY) FROM ABO_DONEM WHERE DONEM_YIL = (SELECT MAX(DONEM_YIL) FROM ABO_DONEM )) AND x.DONEM_YIL = (SELECT max(DONEM_YIL) FROM ABO_DONEM) and FATURA_SMS_GONDERME_ZAMAN BETWEEN to_date('" + baslamaTarih + " 00:00:00','yyyy-MM-dd HH24:mi:ss') AND to_date('" + bitirmeTarih + " 23:59:00','yyyy-MM-dd HH24:mi:ss') and x.SMS_IHBAR ='1'";
break;
case "7":
//İlgili Borç Ödeğindeiğinden İhbar SMS Gönderilmemiştir
sql = "SELECT x.ABONE_ID, a.ABONE_ADI , a.ABONE_SOYADI, a.CEP_TEL, x.SMS_BILGI, x.FATURA_SMS_ID, x.FATURA_SMS_GONDERME_ZAMAN, x.SMS_IHBAR, x.IHBAR_SMS_ID, x.IHBAR_SMS_GONDERME_ZAMAN FROM ABO_ENDEKS x, ABO_ABONE_SAHIS a, ABO_ABONE b WHERE x.ABONE_ID = b.ABONE_ID AND a.ABONE_SAHIS_NO = b.ABONE_SAHIS_NO AND x.SMS_BILGI IS NOT NULL AND x.DONEM_AY = (SELECT MAX(DONEM_AY) FROM ABO_DONEM WHERE DONEM_YIL = (SELECT MAX(DONEM_YIL) FROM ABO_DONEM )) AND x.DONEM_YIL = (SELECT max(DONEM_YIL) FROM ABO_DONEM) and FATURA_SMS_GONDERME_ZAMAN BETWEEN to_date('" + baslamaTarih + " 00:00:00','yyyy-MM-dd HH24:mi:ss') AND to_date('" + bitirmeTarih + " 23:59:00','yyyy-MM-dd HH24:mi:ss') and x.SMS_IHBAR ='6'";
break;
}
}
}
else
{
string FATURA_SMS_GONDERME_ZAMAN = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
sql = "SELECT x.ABONE_ID, a.ABONE_ADI , a.ABONE_SOYADI, a.CEP_TEL, x.SMS_BILGI, x.FATURA_SMS_ID, x.FATURA_SMS_GONDERME_ZAMAN, x.SMS_IHBAR, x.IHBAR_SMS_ID, x.IHBAR_SMS_GONDERME_ZAMAN FROM ABO_ENDEKS x, ABO_ABONE_SAHIS a, ABO_ABONE b WHERE x.ABONE_ID = b.ABONE_ID AND a.ABONE_SAHIS_NO = b.ABONE_SAHIS_NO AND x.SMS_BILGI IS NOT NULL AND x.DONEM_AY = (SELECT MAX(DONEM_AY) FROM ABO_DONEM WHERE DONEM_YIL = (SELECT MAX(DONEM_YIL) FROM ABO_DONEM )) AND x.DONEM_YIL = (SELECT max(DONEM_YIL) FROM ABO_DONEM) and FATURA_SMS_GONDERME_ZAMAN BETWEEN to_date('" + FATURA_SMS_GONDERME_ZAMAN.Split(' ')[0] + " 00:00:00','yyyy-MM-dd HH24:mi:ss') AND to_date('" + FATURA_SMS_GONDERME_ZAMAN.Split(' ')[0] + " 23:59:00','yyyy-MM-dd HH24:mi:ss')";
}
System.Diagnostics.Debug.Print("%%%%%%%%%%%%%%%%%%%%%% " + sql);
if (ilkodu == "71")
{
//connectionString = ConfigurationManager.ConnectionStrings["kirikkale"].ToString();
// connectionString = ConfigurationManager.ConnectionStrings["kirikkaleTest"].ToString();
connectionString = ConfigurationManager.ConnectionStrings["kirsehir"].ToString();
//connectionString = ConfigurationManager.ConnectionStrings["KIRSEHIR_TEST"].ToString();
}
else
{
connectionString = ConfigurationManager.ConnectionStrings["kirsehir"].ToString();
}
DbBaglanti test = new DbBaglanti();
OracleConnection bgln = test.OracleConnect(connectionString);
//OracleConnection bgln = test.OracleConnect(kirikkaleDb);
DataTable dt = test.GetDataTable(sql, bgln);
/*foreach (DataRow row in dt.Rows)
* {
* foreach (DataColumn column in dt.Columns)
* {
* row.SetField(column, new value);
* }
* }
*/
for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
{
dt.Rows[rowIndex][1] = aes.decrypt(dt.Rows[rowIndex][1].ToString());
dt.Rows[rowIndex][2] = aes.decrypt(dt.Rows[rowIndex][2].ToString());
dt.Rows[rowIndex][3] = aes.decrypt(dt.Rows[rowIndex][3].ToString());
}
string jsonString = JsonConvert.SerializeObject(dt);
//System.Diagnostics.Debug.Print("###################################### " + jsonString);
dt.Dispose();
return(View(dt));
}
public ActionResult getCari()
{
string connectionString = "";
string ilkodu = "71";
if (ilkodu == "71" || ilkodu == "710")
{
//connectionString = "DATA SOURCE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.102.140)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DOCUART)));USER ID=TEST_KIRGAZ;PASSWORD=TEST_KIRGAZ";
connectionString = ConfigurationManager.ConnectionStrings["kirikkale"].ToString();
}
else
{
connectionString = ConfigurationManager.ConnectionStrings["kirsehir"].ToString();
}
DbBaglanti test = new DbBaglanti();
string sql = "select *from CARI_TOPLAMLAR_VIEW";
if (Request.Form != null && Request.Form.Count > 0)
{
string firmaKodu = Request.Form["firmaKodu"];
string cariMiktari = Request.Form["cariMiktari"];
if (firmaKodu != "" && cariMiktari == "")
{
System.Diagnostics.Debug.WriteLine("######################################## 1 " + firmaKodu);
if (firmaKodu.Contains("%"))
{
sql = "select *from CARI_TOPLAMLAR_VIEW where FIRMAKODU like '" + firmaKodu + "'";
}
else
{
sql = "select *from CARI_TOPLAMLAR_VIEW where FIRMAKODU= '" + firmaKodu + "'";
}
}
else if (cariMiktari != "" && firmaKodu == "")
{
sql = "select *from CARI_TOPLAMLAR_VIEW where CARI_KALAN> '" + Int32.Parse(cariMiktari) + "'";
}
else if (cariMiktari != "" && firmaKodu != "")
{
if (firmaKodu.Contains("%"))
{
System.Diagnostics.Debug.WriteLine("######################################## 2 " + firmaKodu);
sql = "select *from CARI_TOPLAMLAR_VIEW where CARI_KALAN> '" + Int32.Parse(cariMiktari) + "' and FIRMAKODU like '" + firmaKodu + "'";
}
else
{
sql = "select *from CARI_TOPLAMLAR_VIEW where CARI_KALAN> '" + Int32.Parse(cariMiktari) + "' and FIRMAKODU= '" + firmaKodu + "'";
}
}
}
else
{
sql = "select *from CARI_TOPLAMLAR_VIEW";
}
//string sql = "select ID,TABLO,DURUMU,IS_EMRI_SAYISI from cbs_is_emri_listesi_toplam2 order by ID,TABLO_ID ";
OracleConnection bgln = test.OracleConnect(connectionString);
DataTable dt = test.GetDataTable(sql, bgln);
return(View(dt));
}
public ActionResult UsulsuzGazList()
{
string connectionString = "";
string ilkodu = "71";
if (ilkodu == "71" || ilkodu == "710")
{
ViewBag.IL_Harita = "KIRIKKALE";
connectionString = ConfigurationManager.ConnectionStrings["kirikkale"].ToString();
}
else
{
connectionString = ConfigurationManager.ConnectionStrings["kirsehir"].ToString();
}
string txtIsEmriTipi = "";
if (Request.Form != null && Request.Form.Count > 0)
{
txtIsEmriTipi = Request.Form["cbIsEmriUGKTTList"];
}
DbBaglanti test = new DbBaglanti();
string sql = "select ABONE_ID,ABONE_ADI|| ' ' || ABONE_SOYADI as ABONE_AD_SOYAD,SAYAC_NO,TO_CHAR(TO_DATE(KAPATMA_TARIHI, 'DD.MM.YYYY')) as KAPATMA_TARIHI,CREATED_DATE, ILCE_ADI, MAHALLE_ADI, BINA_ID, DURUMU,USULSUZ_KUL_ID from CBSW_USULSUZ_KULLANIM_TBL order by created_date desc";
if (txtIsEmriTipi == null)
{
sql = "select ABONE_ID,ABONE_ADI|| ' ' || ABONE_SOYADI as ABONE_AD_SOYAD,SAYAC_NO,TO_CHAR(TO_DATE(KAPATMA_TARIHI, 'DD.MM.YYYY')) as KAPATMA_TARIHI,CREATED_DATE, ILCE_ADI, MAHALLE_ADI, BINA_ID, DURUMU,USULSUZ_KUL_ID from CBSW_USULSUZ_KULLANIM_TBL order by created_date desc";
}
else if (txtIsEmriTipi == "1")
{
sql = "select ABONE_ID,ABONE_ADI|| ' ' || ABONE_SOYADI as ABONE_AD_SOYAD,SAYAC_NO,TO_CHAR(TO_DATE(KAPATMA_TARIHI, 'DD.MM.YYYY')) as KAPATMA_TARIHI,CREATED_DATE, ILCE_ADI, MAHALLE_ADI, BINA_ID, DURUMU,USULSUZ_KUL_ID from CBSW_USULSUZ_KULLANIM_TBL WHERE DURUMU='1' order by created_date desc";
}
else if (txtIsEmriTipi == "2")
{
sql = "select ABONE_ID,ABONE_ADI|| ' ' || ABONE_SOYADI as ABONE_AD_SOYAD,SAYAC_NO,TO_CHAR(TO_DATE(KAPATMA_TARIHI, 'DD.MM.YYYY')) as KAPATMA_TARIHI,CREATED_DATE, ILCE_ADI, MAHALLE_ADI, BINA_ID, DURUMU,USULSUZ_KUL_ID from CBSW_USULSUZ_KULLANIM_TBL WHERE DURUMU='2' order by created_date desc";
}
else if (txtIsEmriTipi == "3")
{
sql = "select ABONE_ID,ABONE_ADI|| ' ' || ABONE_SOYADI as ABONE_AD_SOYAD,SAYAC_NO,TO_CHAR(TO_DATE(KAPATMA_TARIHI, 'DD.MM.YYYY')) as KAPATMA_TARIHI,CREATED_DATE, ILCE_ADI, MAHALLE_ADI, BINA_ID, DURUMU,USULSUZ_KUL_ID from CBSW_USULSUZ_KULLANIM_TBL WHERE DURUMU='3' order by created_date desc";
}
else if (txtIsEmriTipi == "4")
{
sql = "select ABONE_ID,ABONE_ADI|| ' ' || ABONE_SOYADI as ABONE_AD_SOYAD,SAYAC_NO,TO_CHAR(TO_DATE(KAPATMA_TARIHI, 'DD.MM.YYYY')) as KAPATMA_TARIHI,CREATED_DATE, ILCE_ADI, MAHALLE_ADI, BINA_ID, DURUMU,USULSUZ_KUL_ID from CBSW_USULSUZ_KULLANIM_TBL WHERE DURUMU='4' order by created_date desc";
}
else if (txtIsEmriTipi == "5")
{
sql = "select ABONE_ID,ABONE_ADI|| ' ' || ABONE_SOYADI as ABONE_AD_SOYAD,SAYAC_NO,TO_CHAR(TO_DATE(KAPATMA_TARIHI, 'DD.MM.YYYY')) as KAPATMA_TARIHI,CREATED_DATE, ILCE_ADI, MAHALLE_ADI, BINA_ID, DURUMU,USULSUZ_KUL_ID from CBSW_USULSUZ_KULLANIM_TBL WHERE DURUMU='5' order by created_date desc";
}
else if (txtIsEmriTipi == "6")
{
sql = "select ABONE_ID,ABONE_ADI|| ' ' || ABONE_SOYADI as ABONE_AD_SOYAD,SAYAC_NO,TO_CHAR(TO_DATE(KAPATMA_TARIHI, 'DD.MM.YYYY')) as KAPATMA_TARIHI,CREATED_DATE, ILCE_ADI, MAHALLE_ADI, BINA_ID, DURUMU,USULSUZ_KUL_ID from CBSW_USULSUZ_KULLANIM_TBL WHERE DURUMU='6' order by created_date desc";
}
else if (txtIsEmriTipi == "7")
{
sql = "select ABONE_ID,ABONE_ADI|| ' ' || ABONE_SOYADI as ABONE_AD_SOYAD,SAYAC_NO,TO_CHAR(TO_DATE(KAPATMA_TARIHI, 'DD.MM.YYYY')) as KAPATMA_TARIHI,CREATED_DATE, ILCE_ADI, MAHALLE_ADI, BINA_ID, DURUMU,USULSUZ_KUL_ID from CBSW_USULSUZ_KULLANIM_TBL WHERE DURUMU='7' order by created_date desc";
}
else if (txtIsEmriTipi == "8")
{
sql = "select ABONE_ID,ABONE_ADI|| ' ' || ABONE_SOYADI as ABONE_AD_SOYAD,SAYAC_NO,TO_CHAR(TO_DATE(KAPATMA_TARIHI, 'DD.MM.YYYY')) as KAPATMA_TARIHI,CREATED_DATE, ILCE_ADI, MAHALLE_ADI, BINA_ID, DURUMU,USULSUZ_KUL_ID from CBSW_USULSUZ_KULLANIM_TBL WHERE DURUMU='8' order by created_date desc";
}
else
{
sql = "select ABONE_ID,ABONE_ADI|| ' ' || ABONE_SOYADI as ABONE_AD_SOYAD,SAYAC_NO,TO_CHAR(TO_DATE(KAPATMA_TARIHI, 'DD.MM.YYYY')) as KAPATMA_TARIHI,CREATED_DATE, ILCE_ADI, MAHALLE_ADI, BINA_ID, DURUMU,USULSUZ_KUL_ID from CBSW_USULSUZ_KULLANIM_TBL order by created_date desc";
}
OracleConnection bgln = test.OracleConnect(connectionString);
DataTable dt = test.GetDataTable(sql, bgln);
int deger = dt.Rows.Count;
if (deger > 0)
{
//Session["UGKTTKayitSayi"] = deger;
return(View(dt));
}
else
{
//Session["UGKTTKayitSayi"] = "0";
DataTable dt2 = new DataTable();
dt2.Columns.Add("ABONE_ID");
dt2.Columns.Add("ABONE_AD_SOYAD");
dt2.Columns.Add("SAYAC_NO");
dt2.Columns.Add("KAPATMA_TARIHI");
dt2.Columns.Add("CREATED_DATE");
dt2.Columns.Add("ILCE_ADI");
dt2.Columns.Add("MAHALLE_ADI");
dt2.Columns.Add("BINA_ID");
dt2.Columns.Add("DURUMU"); // 8
dt2.Columns.Add("USULSUZ_KUL_ID");
DataRow dr = dt2.NewRow();
dr[0] = "----";
dr[1] = "----";
dr[2] = "----";
dr[3] = "----";
dr[4] = "----";
dr[5] = "----";
dr[6] = "----";
dr[7] = "----";
dr[8] = 99;
dr[9] = 0;
dt2.Rows.Add(dr);
return(View(dt2));
}
}
public ActionResult getPeriyodikIsEmirListesi(string bolgeAdi, string ilAdi, string ilceAdi, string mahalleAdi, string zamanAraligi, string sokakAdiSelector, string binaID, string IsEmriDurumSelector, string AracPlakaSelector, string IsEmriYıl)
{
AESCrypt aes = new AESCrypt();
string connectionString = "", sql;
string ilkodu = "", bolgeKodu = "";
System.Diagnostics.Debug.Print("###################################### " + " " + bolgeAdi + " " + ilceAdi + " " + ilAdi + " " + mahalleAdi);
ilkodu = "71";
if (!string.IsNullOrWhiteSpace(ilAdi))
{
if (ilAdi.Equals("Kırıkkale"))
{
ilkodu = "71";
}
}
if (!string.IsNullOrWhiteSpace(bolgeAdi))
{
bolgeKodu = bolgeAdi.Split('-')[0];
}
if (!string.IsNullOrWhiteSpace(bolgeAdi) && !string.IsNullOrWhiteSpace(ilAdi) && !string.IsNullOrWhiteSpace(ilceAdi) &&
!string.IsNullOrWhiteSpace(mahalleAdi))
{
sql = "SELECT ABONE_ID, ABONE_ADI, ABONE_SOYADI, SAYAC_NO, SAYAC_TURU, TAKILAN_SAYAC_NO, ISE_BASLAMA_TARIHI, ISI_BITIRME_TARIHI, EKIP_KODU, EKIP_PERSONEL_1, PLAKA, BILDIRIM_NO, ILCE, MAHALLE, BINA_ID, DURUMU FROM ABO_PER_MUA_IS_EMRI x WHERE BOLGE ='" + bolgeKodu + "' AND MAHALLE = (select DISTINCT(b.MAHALLE_KODU) from GIS_MAHALLE b where MAHALLE_ADI ='" + mahalleAdi + "' and ILCE_KODU = (select DISTINCT(b.ILCE_KODU) from GIS_ILCE b where b.ILCE_ADI ='" + ilceAdi + "')) AND ILCE = (select DISTINCT(b.ILCE_KODU) from GIS_ILCE b where b.ILCE_ADI ='" + ilceAdi + "')";
}
else
{
sql = "SELECT ABONE_ID, ABONE_ADI, ABONE_SOYADI, SAYAC_NO, SAYAC_TURU, TAKILAN_SAYAC_NO, ISE_BASLAMA_TARIHI, ISI_BITIRME_TARIHI, EKIP_KODU, EKIP_PERSONEL_1, PLAKA, BILDIRIM_NO, ILCE, MAHALLE, BINA_ID, DURUMU FROM ABO_PER_MUA_IS_EMRI";
}
System.Diagnostics.Debug.Print("###################################### " + " " + sql);
//sql = "SELECT * FROM ABO_PER_MUA_IS_EMRI WHERE BOLGE_NO ='" + bolgeKodu + "' and IL_KODU ='" + ilkodu + "' and ILCE_ADI ='" + ilceAdi + "' AND MAHALLE_ADI ='" + mahalleAdi + "'";
if (ilkodu == "71")
{
connectionString = ConfigurationManager.ConnectionStrings["kirikkale"].ToString();
}
else
{
connectionString = ConfigurationManager.ConnectionStrings["kirsehir"].ToString();
}
DbBaglanti test = new DbBaglanti();
OracleConnection bgln = test.OracleConnect(connectionString);
//OracleConnection bgln = test.OracleConnect(kirikkaleDb);
DataTable dt = test.GetDataTable(sql, bgln);
/*foreach (DataRow row in dt.Rows)
* {
* foreach (DataColumn column in dt.Columns)
* {
* row.SetField(column, new value);
* }
* }
*/
for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
{
dt.Rows[rowIndex][1] = aes.decrypt(dt.Rows[rowIndex][1].ToString());
dt.Rows[rowIndex][2] = aes.decrypt(dt.Rows[rowIndex][2].ToString());
}
string jsonString = JsonConvert.SerializeObject(dt);
//System.Diagnostics.Debug.Print("###################################### " + jsonString);
dt.Dispose();
return(this.Content(jsonString, "application/json"));
}