示例#1
0
        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"));
        }
示例#2
0
        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"));
        }
示例#3
0
        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());
        }
示例#4
0
        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"));
        }
示例#5
0
        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));
        }
示例#6
0
        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));
        }
示例#7
0
        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));
            }
        }
示例#8
0
        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"));
        }