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;
}
private void UpdateBasicInfo(SqlConnection sc, AdsInfo adi)
{
if (sc != null)
{
// first insert
// sql
try
{
// insert into tb_ads_info (source, type_ads, type_video_media, click_reference, time_to_play_video_ads, src_account, camp_start_date, camp_stop_date, camp_status, campaign_name) value ()
string strSQL = String.Format("Update tb_ads_info set type_video_media = '{0}', click_reference = '{1}', time_to_play_video_ads = {2}, src_account = '{3}', camp_start_date = '{4}', camp_stop_date = '{5}', camp_status = {6}, campaign_name = '{7}', multstream = '{8}' where id = {9};",
adi.type_video_media, adi.click_reference, adi.time_to_play_video_ads, adi.src_account, adi.camp_start_date, adi.camp_stop_date, adi.camp_status, adi.campaign_name, adi.multstream, adi.id);
SqlHelper.ExecuteNonQuery(sc, CommandType.Text, strSQL);
}
catch (Exception e)
{
//System.Diagnostics.Trace.WriteLine(e.Message);
throw e;
}
}
}
// insert into other tables
//private void InsertAddionalInfo(SqlConnection sc, AdsInfo adi, int recordID)
private void InsertAddionalInfo(SqlConnection sc, AdsInfo adi)
{
if (sc != null)
{
try
{
string strSQL;
// network
if (adi.tgt_network != null && adi.tgt_network.network != null)
{
foreach (string strNetwork in adi.tgt_network.network)
{
strSQL = String.Format("insert into dbo.tb_tgt_network (id, network) values ({0}, '{1}')", adi.id, strNetwork);
//strSQL = String.Format("insert into tb_tgt_network (id, network_id) values ({0}, (select network_id from tb_network where network = '{1}'))", adi.id, strNetwork);
SqlHelper.ExecuteNonQuery(sc, CommandType.Text, strSQL);
}
}
// device
if (adi.tgt_device != null && adi.tgt_device.device != null)
{
foreach (string strDevice in adi.tgt_device.device)
{
strSQL = String.Format("insert into dbo.tb_tgt_device (id, device) values ({0}, '{1}')", adi.id, strDevice);
//strSQL = String.Format("insert into tb_tgt_device (id, device_id) values ({0}, (select device_id from tb_device where device = '{1}'))", adi.id, strDevice);
SqlHelper.ExecuteNonQuery(sc, CommandType.Text, strSQL);
}
}
// language
if (adi.tgt_language != null && adi.tgt_language.language != null)
{
foreach (string strLanguage in adi.tgt_language.language)
{
strSQL = String.Format("insert into dbo.tb_tgt_language (id, language) values ({0}, '{1}')", adi.id, strLanguage);
//strSQL = String.Format("insert into tb_tgt_language (id, language_id) values ({0}, (select language_id from tb_language where language = '{1}'))", adi.id, strLanguage);
SqlHelper.ExecuteNonQuery(sc, CommandType.Text, strSQL);
}
}
// location
if (adi.tgt_location != null && adi.tgt_location.location != null)
{
foreach (GeoLocation gl in adi.tgt_location.location)
{
// insert into dbo.tgt_location values ()
//string strCode = String.Format("(select right('000' + CAST((select country_id from tb_country where country = '{0}') as varchar(3)), 3) + right('000' + cast((select province_id from tb_province where province = '{1}') as varchar(3)), 3) + right('000' + cast((select city_id from tb_city where city = '{2}') as varchar(3)), 3))", ci.name, pi.name, cName);
strSQL = String.Format("insert into dbo.tb_tgt_location (id, country, province_code, city) values ({0}, '{1}', '{2}', '{3}')", adi.id, gl.country_name, gl.region_code, gl.city);
SqlHelper.ExecuteNonQuery(sc, CommandType.Text, strSQL);
}
}
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//if (adi.tgt_location != null && adi.tgt_location.country != null)
//{
// foreach (CountryInfo ci in adi.tgt_location.country)
// {
// if (ci.province != null && ci.province.Count > 0)
// {
// foreach (ProvinceInfo pi in ci.province)
// {
// if (pi.city != null && pi.city.Count > 0)
// {
// foreach (string cName in pi.city)
// {
// // insert into dbo.tgt_location values ()
// //string strCode = String.Format("(select right('000' + CAST((select country_id from tb_country where country = '{0}') as varchar(3)), 3) + right('000' + cast((select province_id from tb_province where province = '{1}') as varchar(3)), 3) + right('000' + cast((select city_id from tb_city where city = '{2}') as varchar(3)), 3))", ci.name, pi.name, cName);
// strSQL = String.Format("insert into dbo.tb_tgt_location (id, country, province, city) values ({0}, '{1}', '{2}', '{3}')", adi.id, ci.name, pi.name, cName);
// SqlHelper.ExecuteNonQuery(sc, CommandType.Text, strSQL);
// }
// }
// else
// {
// strSQL = String.Format("insert into dbo.tb_tgt_location (id, country, province, city) values ({0}, '{1}', '{2}', '{3}')", adi.id, ci.name, pi.name, "all");
// SqlHelper.ExecuteNonQuery(sc, CommandType.Text, strSQL);
// }
// }
// }
// else
// {
// strSQL = String.Format("insert into dbo.tb_tgt_location (id, country, province, city) values ({0}, '{1}', '{2}', '{3}')", adi.id, ci.name, "all", "all");
// SqlHelper.ExecuteNonQuery(sc, CommandType.Text, strSQL);
// }
// }
//}
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// bid info
if (adi.ads_bid != null)//adi.ads_bid.price > 0 && !string.IsNullOrEmpty(adi.ads_bid.describe) && adi.ads_bid.budget > 0)
{
foreach (BidInfo bi in adi.ads_bid)
{
if (bi.price > 0 && !string.IsNullOrEmpty(bi.describe) && bi.budget > 0)
// id, price, bude
{
strSQL = String.Format("insert into dbo.tb_bid_info (id, budget, price, describe) values ({0}, {1}, {2}, '{3}')", adi.id, bi.budget, bi.price, bi.describe);
SqlHelper.ExecuteNonQuery(sc, CommandType.Text, strSQL);
}
}
}
// keyword
if (adi.keyword != null && adi.keyword.Count > 0)
{
foreach (string tag in adi.keyword)
{
if (!String.IsNullOrEmpty(tag))
{
strSQL = String.Format("insert into dbo.tb_keyword_info (id, keyword) values ({0}, '{1}')", adi.id, tag);
SqlHelper.ExecuteNonQuery(sc, CommandType.Text, strSQL);
}
}
}
}
catch (Exception e)
{
throw e;
}
}
}
// insert into tb_ads_info
private int InsertBasicInfo(SqlConnection sc, AdsInfo adi)
{
int recordID = 0;
if (sc != null)
{
if (string.IsNullOrEmpty(adi.source))
{
AdssLogger.WriteLog("Invalid source: source can NOT be null or \"\"");
return recordID;
}
// first insert
// sql
// insert into tb_ads_info (source, type_ads, type_video_media, click_reference, time_to_play_video_ads, src_account, camp_start_date, camp_stop_date, camp_status,) value ()
string strInsert = String.Format("insert into tb_ads_info (source, type_ads, type_video_media, click_reference, time_to_play_video_ads, src_account, camp_start_date, camp_stop_date, camp_status, campaign_name) values ('{0}', {1}, '{2}', '{3}', {4}, '{5}', '{6}', '{7}', {8}, '{9}')",
adi.source, adi.type_ads, adi.type_video_media, adi.click_reference, adi.time_to_play_video_ads, adi.src_account, adi.camp_start_date, adi.camp_stop_date, adi.camp_status, adi.campaign_name);
try
{
SqlHelper.ExecuteNonQuery(sc, CommandType.Text, strInsert);
// then select id for later use
string strSelect = String.Format("select id from tb_ads_info where source = '{0}' and type_ads = {1}", adi.source, adi.type_ads);
using (DataSet dt = SqlHelper.ExecuteDataset(sc, CommandType.Text, strSelect))
{
if (dt != null && dt.Tables.Count == 1 && dt.Tables[0].Rows.Count == 1)
recordID = Convert.ToInt32(dt.Tables[0].Rows[0][0]);
}
}
catch (Exception e)
{
//System.Diagnostics.Trace.WriteLine(e.Message);
throw e;
}
}
return recordID;
}
private void DeleteRecord(SqlConnection sc, AdsInfo adi)
{
if (sc != null)
{
try
{
string strSQL = String.Format("delete from tb_ads_info where id = {0};delete from tb_tgt_network where id = {0};delete from tb_tgt_device where id = {0};delete from tb_tgt_language where id = {0};delete from tb_tgt_location where id = {0};delete from tb_bid_info where id = {0};delete from tb_keyword_info where id = {0};", adi.id);
SqlHelper.ExecuteNonQuery(sc, CommandType.Text, strSQL);
}
catch (Exception e)
{
throw e;
}
}
}
