// constructor
public AdsInfo()
{
tgt_location = new LocationInfo();
tgt_network = new NetworkInfo();
tgt_device = new DeviceInfo();
tgt_language = new LanguageInfo();
ads_bid = new List<BidInfo>();
keyword = new List<string>();
campaign_name = source = type_video_media = click_reference = src_account = camp_start_date = camp_stop_date = "";
}
public LocationCondition()
{
locationinfo = new LocationInfo();
}
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;
}
public GetListCondition()
{
source = new List<string>();
type_ads = new List<byte>();
type_video_media = new List<string>();
click_reference = new List<string>();
time_to_play_video_ads = new List<int>();
camp_start_date = new List<string>();
camp_stop_date = new List<string>();
camp_status = new List<byte>();
tgt_location = new LocationInfo();
tgt_network = new NetworkInfo();
tgt_device = new DeviceInfo();
tgt_language = new LanguageInfo();
token = new List<string>();
bid_ads = new List<BidInfo>();
campaign_name = new List<string>();
}
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;
}