Example #1
0
        public static GeoLocation GetGeoLocationFromDB(string strIP)
        {
            GeoLocation gl = new GeoLocation();
            try
            {
                using (SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString))
                {
                    string strQuery = String.Format("select ip, country, province, city, province_code from dbo.tb_geolocation where ip = '{0}'", strIP);
                    using (DataSet dt = SqlHelper.ExecuteDataset(sc, CommandType.Text, strQuery))
                    {
                        if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count == 1)
                        {
                            DataRow r = dt.Tables[0].Rows[0];
                            gl.ip = Convert.ToString(r[0]);
                            gl.country_name = Convert.ToString(r[1]);
                            gl.region_name = Convert.ToString(r[2]);
                            gl.city = Convert.ToString(r[3]);
                            gl.region_code = Convert.ToString(r[4]);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                AdssLogger.WriteLog("Exception in GetGeoLocationFromDB(): " + e.Message);
            }

            return gl;
        }
Example #2
0
        public string GetFullResult()
        {
            string strResult = "{}";
            AdsInfo adi = new AdsInfo();
            try
            {
                //SqlConnection sc = DBConn.GetConnection();
                using (TransactionScope ts = new TransactionScope())
                {
                    using (SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString))
                    {
                        if (sc != null)
                        {
                            // step1. basic
                            //string strSQL = String.Format("select id, source, type_ads, type_video_media, click_reference, time_to_play_video_ads, src_account, convert(VARCHAR(24), camp_start_date, 20), convert(VARCHAR(24), camp_stop_date, 20), camp_status, campaign_name from dbo.tb_ads_info where camp_status = 1 and id {0}", m_condition.GetCondition());
                            string strSQL = String.Format("select id, source, type_ads, type_video_media, click_reference, time_to_play_video_ads, src_account, convert(VARCHAR(24), camp_start_date, 20), convert(VARCHAR(24), camp_stop_date, 20), camp_status, campaign_name, multstream from dbo.tb_ads_info where id {0}", m_condition.GetCondition(null));
                            // step2. addional
                            DataSet dt = SqlHelper.ExecuteDataset(sc, CommandType.Text, strSQL);

                            if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count == 1)
                            {
                                DataRow r = dt.Tables[0].Rows[0];
                                adi.id = Convert.ToInt32(r[0]);
                                adi.source = Convert.ToString(r[1]);
                                adi.type_ads = Convert.ToByte(r[2]);
                                adi.type_video_media = Convert.ToString(r[3]);
                                adi.click_reference = Convert.ToString(r[4]);
                                adi.time_to_play_video_ads = Convert.ToInt32(r[5]);
                                adi.src_account = Convert.ToString(r[6]);
                                adi.camp_start_date = Convert.ToString(r[7]);
                                adi.camp_stop_date = Convert.ToString(r[8]);
                                adi.camp_status = Convert.ToByte(r[9]);
                                adi.campaign_name = Convert.ToString(r[10]);
                                adi.multstream = Convert.ToString(r[11]);
                            }
                            // step2. addional

                            // network
                            //strSQL = String.Format("select b.network from dbo.tb_tgt_network as a, dbo.tb_network as b where a.network_id = b.network_id and a.id {0}", m_condition.GetCondition());
                            strSQL = String.Format("select network from dbo.tb_tgt_network where id {0}", m_condition.GetCondition(null));
                            dt = SqlHelper.ExecuteDataset(sc, CommandType.Text, strSQL);
                            if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
                            {
                                NetworkInfo ni = new NetworkInfo();
                                foreach (DataRow r in dt.Tables[0].Rows)
                                {
                                    ni.network.Add(Convert.ToString(r[0]));
                                }
                                adi.tgt_network = ni;
                            }
                            // device
                            //strSQL = String.Format("select b.device from dbo.tb_tgt_device as a, dbo.tb_device as b where a.device_id = b.device_id and a.id {0}", m_condition.GetCondition());
                            strSQL = String.Format("select device from dbo.tb_tgt_device where id {0}", m_condition.GetCondition(null));
                            dt = SqlHelper.ExecuteDataset(sc, CommandType.Text, strSQL);
                            if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
                            {
                                DeviceInfo di = new DeviceInfo();
                                foreach (DataRow r in dt.Tables[0].Rows)
                                {
                                    di.device.Add(Convert.ToString(r[0]));
                                }
                                adi.tgt_device = di;
                            }
                            // language
                            //strSQL = String.Format("select b.language from dbo.tb_tgt_language as a, dbo.tb_language as b where a.language_id = b.language_id and a.id {0}", m_condition.GetCondition());
                            strSQL = String.Format("select language from dbo.tb_tgt_language where id {0}", m_condition.GetCondition(null));
                            dt = SqlHelper.ExecuteDataset(sc, CommandType.Text, strSQL);
                            if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
                            {
                                LanguageInfo li = new LanguageInfo();
                                foreach (DataRow r in dt.Tables[0].Rows)
                                {
                                    li.language.Add(Convert.ToString(r[0]));
                                }
                                adi.tgt_language = li;
                            }

                            ///////////////////////////////////////////////////////////////////////////////////////////////
                            //// location
                            ////strSQL = String.Format("select cou.country, pro.province, ci.city from dbo.tb_tgt_location as l, dbo.tb_country as cou, dbo.tb_province as pro, dbo.tb_city as ci where l.location_code = (select right('000' + CAST((select country_id from tb_country where country = cou.country) as varchar(3)), 3) + right('000' + cast((select province_id from tb_province where province = pro.province) as varchar(3)), 3) + right('000' + cast((select city_id from tb_city where city = ci.city) as varchar(3)), 3)) and l.id {0} order by cou.country, pro.province, ci.city;", m_condition.GetCondition());
                            //strSQL = String.Format("select country, province, city from dbo.tb_tgt_location where id {0} order by country, province, city;", m_condition.GetCondition());
                            //dt = SqlHelper.ExecuteDataset(sc, CommandType.Text, strSQL);
                            //if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
                            //{
                            //    LocationInfo lci = new LocationInfo();
                            //    int i1, i2;
                            //    foreach (DataRow r in dt.Tables[0].Rows)
                            //    {
                            //        string sCountry = Convert.ToString(r[0]);
                            //        string sProvince = Convert.ToString(r[1]);
                            //        string sCity = Convert.ToString(r[2]);

                            //        //i1 = lci.country.FindIndex(x => x.name == sCountry);
                            //        // all value from database should use ==
                            //        i1 = lci.country.FindIndex(x => x.name.Equals(sCountry, StringComparison.OrdinalIgnoreCase));
                            //        if (i1 < 0)
                            //        {
                            //            CountryInfo ci = new CountryInfo();
                            //            ci.name = sCountry;
                            //            lci.country.Add(ci);
                            //            i1 = lci.country.Count - 1;
                            //        }

                            //        //lci.country[i1]
                            //        i2 = lci.country[i1].province.FindIndex(x => x.name == sProvince);
                            //        //i2 = lci.country.FindIndex(x => x.name.Equals(sProvince, StringComparison.OrdinalIgnoreCase));
                            //        if (i2 < 0)
                            //        {
                            //            ProvinceInfo pi = new ProvinceInfo();
                            //            pi.name = sProvince;
                            //            lci.country[i1].province.Add(pi);
                            //            i2 = lci.country[i1].province.Count - 1;
                            //        }
                            //        lci.country[i1].province[i2].city.Add(sCity);
                            //    }
                            //    adi.tgt_location = lci;
                            //}
                            ///////////////////////////////////////////////////////////////////////////////////////////////

                            // location
                            //strSQL = String.Format("select cou.country, pro.province, ci.city from dbo.tb_tgt_location as l, dbo.tb_country as cou, dbo.tb_province as pro, dbo.tb_city as ci where l.location_code = (select right('000' + CAST((select country_id from tb_country where country = cou.country) as varchar(3)), 3) + right('000' + cast((select province_id from tb_province where province = pro.province) as varchar(3)), 3) + right('000' + cast((select city_id from tb_city where city = ci.city) as varchar(3)), 3)) and l.id {0} order by cou.country, pro.province, ci.city;", m_condition.GetCondition());
                            strSQL = String.Format("select country, province_code, city from dbo.tb_tgt_location where id {0} order by country, province_code, city;", m_condition.GetCondition(null));
                            dt = SqlHelper.ExecuteDataset(sc, CommandType.Text, strSQL);
                            if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count > 0)
                            {
                                LocationInfo lci = new LocationInfo();
                                foreach (DataRow r in dt.Tables[0].Rows)
                                {
                                    GeoLocation gl = new GeoLocation();
                                    gl.country_name = Convert.ToString(r[0]);
                                    gl.region_code = Convert.ToString(r[1]);
                                    gl.city = Convert.ToString(r[2]);
                                    lci.location.Add(gl);

                                }
                                adi.tgt_location = lci;
                            }

                            // bid info
                            strSQL = String.Format("select budget, price, describe from dbo.tb_bid_info where id {0}", m_condition.GetCondition(null));
                            dt = SqlHelper.ExecuteDataset(sc, CommandType.Text, strSQL);
                            if (dt != null && dt.Tables.Count > 0)// && dt.Tables[0].Rows.Count == 1)
                            {
                                //BidInfo bi = new BidInfo();
                                foreach (DataRow r in dt.Tables[0].Rows)
                                {
                                    BidInfo bi = new BidInfo();
                                    bi.budget = Convert.ToInt32(r[0]);
                                    bi.price = Convert.ToInt32(r[1]);
                                    bi.describe = Convert.ToString(r[2]);

                                    adi.ads_bid.Add(bi);
                                }
                            }

                            // keyword
                            strSQL = String.Format("select keyword from dbo.tb_keyword_info where id {0}", m_condition.GetCondition(null));
                            dt = SqlHelper.ExecuteDataset(sc, CommandType.Text, strSQL);
                            if (dt != null && dt.Tables.Count > 0)// && dt.Tables[0].Rows.Count == 1)
                            {
                                //BidInfo bi = new BidInfo();
                                foreach (DataRow r in dt.Tables[0].Rows)
                                {
                                    adi.keyword.Add(Convert.ToString(r[0]));
                                }
                            }
                            strResult = new JavaScriptSerializer().Serialize(adi);
                            //System.Diagnostics.Trace.WriteLine(strResult);
                        }
                        else
                        {
                            //Trace.WriteLine("can not get sql connection.");
                            AdssLogger.WriteLog("FinalFilter.GetFullResult() --- can not get sql connection.");
                        }
                    }
                }
            }
            catch (Exception e)
            {
                //Trace.WriteLine(e.Message);
                AdssLogger.WriteLog("FinalFilter.GetFullResult() --- Exception: " + e.Message);
            }

            return strResult;
        }
Example #3
0
        public static GeoLocation GetGeoLocationFromHttp(string strIP)
        {
            GeoLocation gl = new GeoLocation();
            HttpWebRequest req = (HttpWebRequest)WebRequest.Create("https://freegeoip.net/json/" + strIP);
            req.Method = "GET";
            req.KeepAlive = false;
            WebResponse wres = req.GetResponse();
            HttpStatusCode iRet = ((HttpWebResponse)wres).StatusCode;
            if (iRet == HttpStatusCode.OK)
            {
                using (Stream stm = wres.GetResponseStream())
                {
                    StreamReader sr = new StreamReader(stm, Encoding.UTF8);
                    string strJson = sr.ReadToEnd();

                    gl = new JavaScriptSerializer().Deserialize<GeoLocation>(strJson);
                }
            }

            if (string.IsNullOrEmpty(gl.ip) || string.IsNullOrEmpty(gl.country_name)
                || string.IsNullOrEmpty(gl.region_name) || string.IsNullOrEmpty(gl.city))
            {
                // try ip api http://ip-api.com/json/114.187.79.6
                GeoLocation gl_ipapi = GetGeoLocationFromIPAPI(strIP);
                if (!string.IsNullOrEmpty(gl_ipapi.ip))
                    InsertGeoLocation(gl_ipapi);
                else
                    InsertGeoLocation(gl);
            }
            else
                InsertGeoLocation(gl);
            return gl;
        }
Example #4
0
 public static void InsertGeoLocation(GeoLocation gl)
 {
     try
     {
         using (SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString))
         {
             if (sc != null)
             {
                 // xi'an for apostrophe problem
                 gl.city = gl.city.Replace("'", "''");
                 string strSQL = String.Format("insert into tb_geolocation (ip, country, province, city, province_code) values ('{0}', '{1}', '{2}', '{3}', '{4}')", gl.ip, String.IsNullOrEmpty(gl.country_name) ? "all" : gl.country_name, String.IsNullOrEmpty(gl.region_name) ? "all" : gl.region_name, String.IsNullOrEmpty(gl.city) ? "all" : gl.city, gl.region_code);
                 SqlHelper.ExecuteNonQuery(sc, CommandType.Text, strSQL);
             }
         }
     }
     catch (Exception e)
     {
         AdssLogger.WriteLog("Exception in InsertGeoLocation(): " + e.Message);
     }
 }
Example #5
0
        public static GeoLocation GetGeoLocationFromIPAPI(string strIP)
        {
            GeoLocation_IPAPI gl_ipapi = new GeoLocation_IPAPI();
            HttpWebRequest req = (HttpWebRequest)WebRequest.Create("http://ip-api.com/json/" + strIP);
            req.Method = "GET";
            req.KeepAlive = false;
            WebResponse wres = req.GetResponse();
            HttpStatusCode iRet = ((HttpWebResponse)wres).StatusCode;
            if (iRet == HttpStatusCode.OK)
            {
                using (Stream stm = wres.GetResponseStream())
                {
                    StreamReader sr = new StreamReader(stm, Encoding.UTF8);
                    string strJson = sr.ReadToEnd();

                    gl_ipapi = new JavaScriptSerializer().Deserialize<GeoLocation_IPAPI>(strJson);
                }
            }
            GeoLocation gl = new GeoLocation();

            if (String.Equals(gl_ipapi.status, "success", StringComparison.OrdinalIgnoreCase))
            {
                gl.ip = gl_ipapi.query;
                gl.country_name = gl_ipapi.country;
                gl.region_name = gl_ipapi.regionName;
                gl.city = gl_ipapi.city;
                gl.region_code = gl_ipapi.region;
            }
            return gl;
        }
Example #6
0
        private LocationInfo GetLocationInfoFromDB(string sToken)
        {
            LocationInfo li = new LocationInfo();
            string strIp = FingerPrint.GetVisitorIPAddress();
            if (string.IsNullOrEmpty(strIp) || string.IsNullOrEmpty(sToken))
                return li;
            try
            {
                //SqlConnection sc = DBConn.GetConnection();
                ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                //using (SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString))
                //{
                //    if (sc != null)
                //    {
                //        string strSQL = String.Format("select country, province, city from dbo.tb_user_info where token = '{0}' and ip = '{1}'", sToken, strIp);
                //        DataSet dt = SqlHelper.ExecuteDataset(sc, CommandType.Text, strSQL);
                //        DataTable dtb = dt.Tables[0];

                //        if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count == 1)
                //        {
                //            DataRow r = dt.Tables[0].Rows[0];
                //            ProvinceInfo pi = new ProvinceInfo();
                //            pi.name = Convert.ToString(r[1]);
                //            pi.city.Add(Convert.ToString(r[2]));
                //            CountryInfo ci = new CountryInfo();
                //            ci.name = Convert.ToString(r[0]);
                //            ci.province.Add(pi);
                //            li.country.Add(ci);
                //        }
                //    }
                //    else
                //    {
                //        //Trace.WriteLine("can not get sql connection.");
                //        AdssLogger.WriteLog("GetList.GetLocationInfoFromDB() --- can not get sql connection.");
                //    }
                //}
                ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
                using (SqlConnection sc = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString))
                {
                    if (sc != null)
                    {
                        string strSQL = String.Format("select country, province, city, province_code from dbo.tb_user_info where token = '{0}' and ip = '{1}'", sToken, strIp);
                        DataSet dt = SqlHelper.ExecuteDataset(sc, CommandType.Text, strSQL);
                        DataTable dtb = dt.Tables[0];

                        if (dt != null && dt.Tables.Count > 0 && dt.Tables[0].Rows.Count == 1)
                        {
                            DataRow r = dt.Tables[0].Rows[0];
                            GeoLocation gl = new GeoLocation();

                            gl.country_name = Convert.ToString(r[0]);
                            gl.region_name = Convert.ToString(r[1]);
                            gl.city = Convert.ToString(r[2]);
                            gl.region_code = Convert.ToString(r[3]);
                            li.location.Add(gl);
                        }
                    }
                    else
                    {
                        //Trace.WriteLine("can not get sql connection.");
                        AdssLogger.WriteLog("GetList.GetLocationInfoFromDB() --- can not get sql connection.");
                    }
                }
            }
            catch (Exception e)
            {
                //Trace.WriteLine(e.Message);
                AdssLogger.WriteLog("Getlist.GetLocationInfoFromDB() --- Exception: " + e.Message);
            }
            return li;
        }