using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Xml;
using System.Collections;
using MySql.Data.MySqlClient; /// <summary>
/// Summary description for MySqlHelper
/// </summary>
public sealed class MySqlHelper
{
private static readonly string connectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionStringMySql"].ConnectionString;
public static readonly string connectionString1 = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionStringMySql1"].ConnectionString;
public static readonly string connectionString2 = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionStringMySql2"].ConnectionString;
public static readonly string connectionString3 = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionStringMySql3"].ConnectionString;
public static readonly string connectionString4 = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnectionStringMySql4"].ConnectionString;
public MySqlHelper()
{
} public static DataSet ExecuteDataset(CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
{
using (MySqlConnection cn = new MySqlConnection(connectionString))
{
cn.Open();
return ExecuteDataset(cn, commandType, commandText, commandParameters);
}
} public static DataSet ExecuteDatasetNew(CommandType commandType, string commandText, string newconntentString,params MySqlParameter[] commandParameters)
{
using (MySqlConnection cn = new MySqlConnection(newconntentString))
{
cn.Open();
return ExecuteDataset(cn, commandType, commandText, commandParameters);
}
} public static DataSet ExecuteDataset(MySqlConnection connection, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandTimeout = ;
PrepareCommand(cmd, connection, (MySqlTransaction)null, commandType, commandText, commandParameters);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
} private static void PrepareCommand(MySqlCommand command, MySqlConnection connection, MySqlTransaction transaction, CommandType commandType, string commandText, MySqlParameter[] commandParameters)
{
if (connection.State != ConnectionState.Open)
{
Console.WriteLine("open");
connection.Open();
}
command.Connection = connection;
command.CommandText = commandText;
if (transaction != null)
{
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
}
command.CommandType = commandType;
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
} private static void AttachParameters(MySqlCommand command, MySqlParameter[] commandParameters)
{
foreach (MySqlParameter p in commandParameters)
{
//check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
} command.Parameters.Add(p);
}
}
}
以上是help类。
接着是GetAdspaceReportForMD_HourlyNew，GetMediaBuyReportFromHourlyNew，GetAdFormatReportHourlyNew，GetAdFormatReportHourlyALLNew调用：
public static List<AdSpaceHourlyNew> GetAdspaceReportForMD_HourlyNew(DateTime start, DateTime end, String aid)
{
List<AdSpaceHourlyNew> result = new List<AdSpaceHourlyNew>();
List<AdSpaceHourlyNew> result1 = new List<AdSpaceHourlyNew>();
List<AdSpaceHourlyNew> result2 = new List<AdSpaceHourlyNew>();
AdSpaceIdForHourly adspaceinfo = new AdSpaceIdForHourly(); string[] constr = new[] { MySqlHelper.connectionString1, MySqlHelper.connectionString3, MySqlHelper.connectionString4 }; string sqladspace = @"SELECT AD.ID,AD.NAME,AD.ROAD_BLOCK_GROUP_ID FROM AD_SPACE AD WHERE id=:AID";
OracleParameter para2 = new OracleParameter(":AID", OracleType.Int32);
para2.Value = aid;
OracleParameter[] paras = { para2 };
DataTable dtadspace = OracleHelper.ExecuteDataset(CONNECTION_STRING, CommandType.Text, sqladspace, paras).Tables[]; if (dtadspace.Rows.Count > )
{
adspaceinfo = new AdSpaceIdForHourly()
{
RoadBlockGroupId = Convert.ToInt32(dtadspace.Rows[]["ROAD_BLOCK_GROUP_ID"]),
AdSpaceId = Convert.ToInt32(dtadspace.Rows[]["ID"]),
AdSpaceName = dtadspace.Rows[]["NAME"].ToString()
};
} String sqlhourly = @"SELECT date_format(SETTLED_TIME,'%Y-%m-%d %H') SETTLED_TIME, SUM(COUNT) AS PV
FROM REPORT_DATA_HOURLY_CASUAL_NCT R
WHERE
(ACTION_TYPE,PROCESS_STATUS) IN ((2,3),(7,1),(2,5),(2,0),(2,4),(8,1),(2,2),(7,2),(8,4))
AND R.AD_SPACE_ID=@AID
AND SETTLED_TIME>=@ST AND SETTLED_TIME<@ET
GROUP BY date_format(SETTLED_TIME,'%Y-%m-%d %H')";
MySqlParameter[] orp = new MySqlParameter[] {
new MySqlParameter("@AID", aid),
new MySqlParameter("@ST", start),
new MySqlParameter("@ET", end)
};
#region
for (int i = ; i < ; i++)
{
DataTable dt1 = MySqlHelper.ExecuteDatasetNew(CommandType.Text, sqlhourly, constr[i], orp).Tables[];
if (dt1.Rows.Count > )
{
for (int x = ; x < dt1.Rows.Count; x++)
{
var r = result1.Find(os => os.SettledTime == dt1.Rows[x]["SETTLED_TIME"].ToString());
if (r == null)
{
result1.Add(new AdSpaceHourlyNew()
{
SettledTime = dt1.Rows[x]["SETTLED_TIME"].ToString(),
AdSpaceId = adspaceinfo.AdSpaceId,
AdSpaceName = adspaceinfo.AdSpaceName,
Pv = Convert.ToInt64(dt1.Rows[x]["PV"])
});
}
else
{
r.Pv += Convert.ToInt64(dt1.Rows[x]["PV"]);
}
}
}
}
#endregion string sqlhourlypv = string.Empty;
if (IfRB(aid))
{
sqlhourlypv = @"SELECT date_format(SETTLED_TIME,'%Y-%m-%d %H') SETTLED_TIME,SUM(ADV_IMP_PAID) AS ADV,SUM(PUB_IMP_PAID) AS PUB,
SUM(CLICKS)AS CLICKS FROM REPORT_DATA_HOURLY_NOCITY R WHERE R.ROADBLOCK_ID=@ROAD_BLOCK_GROUP_ID AND ADV_FLAG=1 AND SETTLED_TIME>=@ST AND SETTLED_TIME<@ET
GROUP BY date_format(SETTLED_TIME,'%Y-%m-%d %H')";
MySqlParameter[] orp1 = new MySqlParameter[] {
new MySqlParameter("@ROAD_BLOCK_GROUP_ID", adspaceinfo.RoadBlockGroupId),
new MySqlParameter("@ST", start),
new MySqlParameter("@ET", end)
};
for (int i = ; i < ; i++)
{
DataTable dt1 = MySqlHelper.ExecuteDatasetNew(CommandType.Text, sqlhourlypv, constr[i], orp1).Tables[];
if (dt1.Rows.Count > )
{
for (int y = ; y < dt1.Rows.Count; y++)
{
var r = result1.Find(os => os.SettledTime == dt1.Rows[y]["SETTLED_TIME"].ToString());
if (r == null)
{
result2.Add(new AdSpaceHourlyNew()
{
SettledTime = dt1.Rows[y]["SETTLED_TIME"].ToString(),
AdSpaceId = adspaceinfo.AdSpaceId,
AdSpaceName = adspaceinfo.AdSpaceName,
Adv = Convert.ToInt64(dt1.Rows[y]["ADV"]),
Pub = Convert.ToInt64(dt1.Rows[y]["PUB"]),
Clicks = Convert.ToInt64(dt1.Rows[y]["CLICKS"])
});
}
else
{
r.Adv += Convert.ToInt64(dt1.Rows[y]["ADV"]);
r.Pub += Convert.ToInt64(dt1.Rows[y]["PUB"]);
r.Clicks += Convert.ToInt64(dt1.Rows[y]["CLICKS"]);
}
}
}
}
}
else
{
sqlhourlypv = @"SELECT date_format(SETTLED_TIME,'%Y-%m-%d %H') SETTLED_TIME,SUM(ADV_IMP_PAID) AS ADV,SUM(PUB_IMP_PAID) AS PUB,
SUM(CLICKS)AS CLICKS FROM REPORT_DATA_HOURLY_NOCITY R WHERE R.AD_SPACE_ID=@ROAD_BLOCK_GROUP_ID AND ADV_FLAG=1 AND SETTLED_TIME>=@ST AND SETTLED_TIME<@ET
GROUP BY date_format(SETTLED_TIME,'%Y-%m-%d %H')";
MySqlParameter[] orp1 = new MySqlParameter[] {
new MySqlParameter("@ROAD_BLOCK_GROUP_ID", adspaceinfo.AdSpaceId),
new MySqlParameter("@ST", start),
new MySqlParameter("@ET", end)
};
for (int i = ; i < ; i++)
{
DataTable dt1 = MySqlHelper.ExecuteDatasetNew(CommandType.Text, sqlhourlypv, constr[i], orp1).Tables[];
if (dt1.Rows.Count > )
{
for (int y = ; y < dt1.Rows.Count; y++)
{
var r = result2.Find(os => os.SettledTime == dt1.Rows[y]["SETTLED_TIME"].ToString());
if (r == null)
{
result2.Add(new AdSpaceHourlyNew()
{
SettledTime = dt1.Rows[y]["SETTLED_TIME"].ToString(),
AdSpaceId = adspaceinfo.AdSpaceId,
AdSpaceName = adspaceinfo.AdSpaceName,
Adv = Convert.ToInt64(dt1.Rows[y]["ADV"]),
Pub = Convert.ToInt64(dt1.Rows[y]["PUB"]),
Clicks = Convert.ToInt64(dt1.Rows[y]["CLICKS"])
});
}
else
{
r.Adv += Convert.ToInt64(dt1.Rows[y]["ADV"]);
r.Pub += Convert.ToInt64(dt1.Rows[y]["PUB"]);
r.Clicks += Convert.ToInt64(dt1.Rows[y]["CLICKS"]);
}
}
}
}
} foreach (var minfo in result1)
{
AdSpaceHourlyNew r = new AdSpaceHourlyNew();
var pubr1 = result1.Find(os => os.SettledTime == minfo.SettledTime);
var pubr2 = result2.Find(os => os.SettledTime == minfo.SettledTime);
if (pubr2!=null)
{
r.AdSpaceId = minfo.AdSpaceId;
r.AdSpaceName = minfo.AdSpaceName;
r.Pv = minfo.Pv;
r.Pub = pubr2 != null ? pubr2.Pub : ;
r.Clicks = pubr2 != null ? pubr2.Clicks : ;
r.Adv = pubr2 != null ? pubr2.Adv : ;
r.Ctr = r.Adv > ? (double)r.Clicks / r.Adv : ;
r.SettledTime = minfo.SettledTime;
result.Add(r);
}
else
{
r.AdSpaceId = minfo.AdSpaceId;
r.AdSpaceName = minfo.AdSpaceName;
r.Pv = minfo.Pv;
r.Pub = pubr1 != null ? pubr1.Pub : ;
r.Clicks = pubr1 != null ? pubr1.Clicks : ;
r.Adv = pubr1 != null ? pubr1.Adv : ;
r.Ctr = r.Adv > ? (double)r.Clicks / r.Adv : ;
r.SettledTime = minfo.SettledTime;
result.Add(r);
}
}
return result;
}
public static List<MediaBuyReportFromHourly> GetMediaBuyReportFromHourlyNew(int campaignID, DateTime start, DateTime end)
{
List<MediaBuyReportFromHourly> List = new List<MediaBuyReportFromHourly>();
List<MediaBuyReportFromHourly> List1 = new List<MediaBuyReportFromHourly>();
List<MediaBuyReportFromHourly> List2 = new List<MediaBuyReportFromHourly>();
MediaBuyReportFromHourly MediaBuyReportFromHourly = new MediaBuyReportFromHourly();
string[] constr = new[] { MySqlHelper.connectionString1, MySqlHelper.connectionString3, MySqlHelper.connectionString4 }; string sqlo = @"select campaign_id,id as media_buy_id,start_time,end_time,NAME,--rate.rate,
BUDGET,PACING_DISABLED,decode(PACING_DISABLED,1,0,NVL(minute_budget,0)) minute_budget,balance FROM media_buy WHERE campaign_id = :CAMPAIGN_ID AND status > 0";
OracleParameter pCampaignID = new OracleParameter(":CAMPAIGN_ID", OracleType.Int32);
pCampaignID.Value = campaignID; DataSet ords = OracleHelper.ExecuteDataset(CONNECTION_STRING, CommandType.Text, sqlo, pCampaignID);
string mediabuyid = "";
foreach (DataRow row in ords.Tables[].Rows)
{
mediabuyid += row[] + " ,";
}
mediabuyid = mediabuyid.TrimEnd(',');
if (ords.Tables[].Rows.Count > )
{
for (int i = ; i < ords.Tables[].Rows.Count; i++)
{
List1.Add(new MediaBuyReportFromHourly()
{
Campaign_ID = Convert.ToInt32(ords.Tables[].Rows[i]["Campaign_ID"].ToString()),
Media_Buy_ID = Convert.ToInt32(ords.Tables[].Rows[i]["Media_Buy_ID"].ToString()),
Start_Time = Convert.ToDateTime(ords.Tables[].Rows[i]["Start_Time"].ToString()),
End_Time = Convert.ToDateTime(ords.Tables[].Rows[i]["End_Time"].ToString()),
Name = ords.Tables[].Rows[i]["Name"].ToString(),
Budget = Convert.ToInt32(ords.Tables[].Rows[i]["Budget"]),
Pacing_Disabled = Convert.ToInt32(ords.Tables[].Rows[i]["Pacing_Disabled"]),
Minute_Budget = Convert.ToDouble(ords.Tables[].Rows[i]["Minute_Budget"]),
Balance = Convert.ToDouble(ords.Tables[].Rows[i]["Balance"])
});
} }
string sqlm = @"select media_buy_id, coalesce(sum(adv_imp_paid),0) as adv_imp_paid,coalesce(sum(pub_imp_paid),0) as pub_imp_paid,coalesce(sum(clicks),0) as clicks
from report_data_hourly_nocity a
where media_buy_id in ({0})
and settled_time >= @starttime and settled_time <@endtime and adv_flag=1
group by media_buy_id";
sqlm = string.Format(sqlm, mediabuyid);
MySqlParameter[] myp = new MySqlParameter[] {
new MySqlParameter("@starttime", start),
new MySqlParameter("@endtime", end)
}; for (int i = ; i < ; i++)
{
DataTable dt1 = MySqlHelper.ExecuteDatasetNew(CommandType.Text, sqlm, constr[i], myp).Tables[];
if (dt1.Rows.Count > )
{
for (int j = ; j < dt1.Rows.Count; j++)
{
var r = List2.Find(mbi => mbi.Media_Buy_ID == Convert.ToInt32(dt1.Rows[j]["Media_Buy_ID"].ToString()));
if (r == null)
{
List2.Add(new MediaBuyReportFromHourly()
{
Media_Buy_ID = Convert.ToInt32(dt1.Rows[j]["Media_Buy_ID"] != DBNull.Value ? dt1.Rows[j]["Media_Buy_ID"] : ),
Adv_Imp_Paid = Convert.ToInt64(dt1.Rows[j]["Adv_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Adv_Imp_Paid"] : ),
Pub_Imp_Paid = Convert.ToInt64(dt1.Rows[j]["Pub_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Pub_Imp_Paid"] : ),
Clicks = Convert.ToInt64(dt1.Rows[j]["Clicks"] != DBNull.Value ? dt1.Rows[j]["Clicks"] : ) });
}
else
{
r.Adv_Imp_Paid += Convert.ToInt64(dt1.Rows[j]["Adv_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Adv_Imp_Paid"] : );
r.Pub_Imp_Paid += Convert.ToInt64(dt1.Rows[j]["Pub_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Pub_Imp_Paid"] : );
r.Clicks += Convert.ToInt64(dt1.Rows[j]["Clicks"] != DBNull.Value ? dt1.Rows[j]["Clicks"] : );
}
} }
}
foreach (var mymbi in List2)
{
MediaBuyReportFromHourly r = new MediaBuyReportFromHourly();
var minfo = List1.Find(mbi => mbi.Media_Buy_ID == mymbi.Media_Buy_ID);
if (minfo != null)
{
r.Campaign_ID = minfo.Campaign_ID;
r.Media_Buy_ID = minfo.Media_Buy_ID;
r.Start_Time = minfo.Start_Time;
r.End_Time = minfo.End_Time;
r.Name = minfo.Name;
r.Budget = minfo.Budget;
r.Pacing_Disabled = minfo.Pacing_Disabled;
r.Minute_Budget = minfo.Minute_Budget;
r.Balance = minfo.Balance;
} r.Adv_Imp_Paid = mymbi.Adv_Imp_Paid;
r.Pub_Imp_Paid = mymbi.Pub_Imp_Paid;
r.Clicks = mymbi.Clicks;
r.Ctr = r.Adv_Imp_Paid > ? (double)r.Clicks / r.Adv_Imp_Paid : ;
List.Add(r);
} //foreach (var minfo in List1)
//{
// MediaBuyReportFromHourly r = new MediaBuyReportFromHourly();
// var mymbi = List2.Find(mbi => mbi.Media_Buy_ID == minfo.Media_Buy_ID);
// r.Campaign_ID = minfo.Campaign_ID;
// r.Media_Buy_ID = minfo.Media_Buy_ID;
// r.Start_Time = minfo.Start_Time;
// r.End_Time = minfo.End_Time;
// r.Name = minfo.Name;
// r.Budget = minfo.Budget;
// r.Pacing_Disabled = minfo.Pacing_Disabled;
// r.Minute_Budget = minfo.Minute_Budget;
// r.Balance = minfo.Balance;
// r.Adv_Imp_Paid = mymbi != null ? mymbi.Adv_Imp_Paid : 0;
// r.Pub_Imp_Paid = mymbi != null ? mymbi.Pub_Imp_Paid : 0;
// r.Clicks = mymbi != null ? mymbi.Clicks : 0;
// r.Ctr = r.Adv_Imp_Paid > 0 ? (double)r.Clicks / r.Adv_Imp_Paid : 0;
// List.Add(r);
//} return List.OrderBy(os => os.Media_Buy_ID).ToList();
}
public static List<AdFormatReportResultHourly> GetAdFormatReportHourlyNew(int campaignID, DateTime start, DateTime end)
{
List<AdFormatReportResultHourly> List = new List<AdFormatReportResultHourly>();
List<AdFormatReportHourly> ListA = new List<AdFormatReportHourly>();
List<AdFormatReportHourly> ListB = new List<AdFormatReportHourly>();
List<AdFormatReportHourly> List1 = new List<AdFormatReportHourly>();
List<AdFormatReportHourly> List2 = new List<AdFormatReportHourly>();
List<AdFormatReportHourly> List3 = new List<AdFormatReportHourly>();
List<AdFormatReportHourly> List4 = new List<AdFormatReportHourly>();
AdFormatReportHourly AdFormatReportHourly = new AdFormatReportHourly();
string[] constr = new[] { MySqlHelper.connectionString1, MySqlHelper.connectionString3, MySqlHelper.connectionString4 }; string sqlo = @"select campaign_id,id FROM media_buy WHERE campaign_id = :CAMPAIGN_ID AND status > 0";
OracleParameter pCampaignID = new OracleParameter(":CAMPAIGN_ID", OracleType.Int32);
pCampaignID.Value = campaignID; DataSet ords = OracleHelper.ExecuteDataset(CONNECTION_STRING, CommandType.Text, sqlo, pCampaignID);
string mediabuyid = "";
foreach (DataRow row in ords.Tables[].Rows)
{
mediabuyid += row[] + " ,";
}
mediabuyid = mediabuyid.TrimEnd(',');
if (mediabuyid.Length == )
return List;
string creativids = "";
string sqlm = @"select creative_id,roadblock_id, coalesce(sum(adv_imp_paid),0) as adv_imp_paid,coalesce(sum(pub_imp_paid),0) as pub_imp_paid,coalesce(sum(clicks),0) as clicks
from report_data_hourly_nocity a where media_buy_id in ({0}) and settled_time >= @starttime and settled_time <@endtime and adv_flag=1 group by creative_id,roadblock_id";
sqlm = string.Format(sqlm, mediabuyid);
MySqlParameter[] myp = new MySqlParameter[] {
new MySqlParameter("@starttime", start),
new MySqlParameter("@endtime", end)
};
for (int i = ; i < ; i++)
{
DataTable dt1 = MySqlHelper.ExecuteDatasetNew(CommandType.Text, sqlm, constr[i], myp).Tables[];
if (dt1.Rows.Count > )
{
for (int j = ; j < dt1.Rows.Count; j++)
{
int creativeid = Convert.ToInt32(dt1.Rows[j]["Creative_ID"].ToString());
creativids += creativeid + ",";
int roadblockid = Convert.ToInt32(dt1.Rows[j]["roadblock_id"].ToString());
var r = List1.Find(cid => cid.Creative_ID == creativeid && cid.RoadblockId == roadblockid);
if (r == null)
{
List1.Add(new AdFormatReportHourly()
{
Campaign_ID = campaignID,
Creative_ID = creativeid,
RoadblockId = roadblockid,
Adv_Imp_Paid = Convert.ToInt64(dt1.Rows[j]["Adv_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Adv_Imp_Paid"] : ),
Pub_Imp_Paid = Convert.ToInt64(dt1.Rows[j]["Pub_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Pub_Imp_Paid"] : ),
Clicks = Convert.ToInt64(dt1.Rows[j]["Clicks"] != DBNull.Value ? dt1.Rows[j]["Clicks"] : )
});
}
else
{
r.Adv_Imp_Paid += Convert.ToInt64(dt1.Rows[j]["Adv_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Adv_Imp_Paid"] : );
r.Pub_Imp_Paid += Convert.ToInt64(dt1.Rows[j]["Pub_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Pub_Imp_Paid"] : );
r.Clicks += Convert.ToInt64(dt1.Rows[j]["Clicks"] != DBNull.Value ? dt1.Rows[j]["Clicks"] : );
}
}
}
} #region
if (creativids.Length > )
{
string sqlNC = @"select id,ad_format,format_name from (select id,ad_format,ad_size_format_desc as format_name from creative c left join AD_SIZE_FORMAT f on c.ad_format=f.format) c
where c.id in ({0}) ";
sqlNC = string.Format(sqlNC, creativids.TrimEnd(',')); DataSet ordsN2 = OracleHelper.ExecuteDataset(CONNECTION_STRING, CommandType.Text, sqlNC);
if (ordsN2.Tables[].Rows.Count > )
{
for (int k = ; k < ordsN2.Tables[].Rows.Count; k++)
{
List2.Add(new AdFormatReportHourly()
{
Creative_ID = Convert.ToInt32(ordsN2.Tables[].Rows[k]["ID"].ToString()),
Format_Name = ordsN2.Tables[].Rows[k]["Format_Name"].ToString(),
AdFormat = Convert.ToInt32(ordsN2.Tables[].Rows[k]["ad_format"].ToString())
});
}
}
}
#endregion foreach (var minfo in List1)
{
AdFormatReportHourly r = new AdFormatReportHourly();
var mymbi = List2.Find(cid => cid.Creative_ID == minfo.Creative_ID);
r.Campaign_ID = campaignID;
r.RoadblockId = minfo.RoadblockId;
r.Format_Name = mymbi != null ? mymbi.Format_Name : "";
r.AdFormat = mymbi != null ? mymbi.AdFormat : -;
r.Adv_Imp_Paid = minfo.Adv_Imp_Paid;
r.Pub_Imp_Paid = minfo.Pub_Imp_Paid;
r.Clicks = minfo.Clicks;
ListA.Add(r);
ListB.Add(r);
} ListA = ListA.FindAll(os => (os.RoadblockId != || os.AdFormat != ));
var ListA = ListA.GroupBy(a1 => new { a1.Campaign_ID, a1.Format_Name })
.Select(a2 => new AdFormatReportHourly
{
Clicks=a2.Sum(a3=>a3.Clicks),
Adv_Imp_Paid=a2.Sum(a3=>a3.Adv_Imp_Paid),
Pub_Imp_Paid=a2.Sum(a3=>a3.Pub_Imp_Paid),
Campaign_ID=a2.Key.Campaign_ID,
Format_Name=a2.Key.Format_Name
});
ListB = ListB.FindAll(os => os.RoadblockId == && os.AdFormat == ).ToList();
var listB = ListB.GroupBy(t1 => new { t1.Campaign_ID, t1.Format_Name })
.Select(t2 => new AdFormatReportHourly
{
Clicks=t2.Sum(t3=>t3.Clicks),
Adv_Imp_Paid=t2.Sum(t3=>t3.Adv_Imp_Paid),
Pub_Imp_Paid=t2.Sum(t3=>t3.Pub_Imp_Paid),
Campaign_ID=t2.Key.Campaign_ID,
Format_Name=t2.Key.Format_Name
});
foreach (var lb in listB)
{
var temp = ListA.FindAll(os => os.Format_Name == lb.Format_Name);
if (temp == null || temp.Count == )
{
ListA.Add(lb);
}
}
ListA.ForEach(os =>
{
List.Add(new AdFormatReportResultHourly()
{
Adv_Imp_Paid = os.Adv_Imp_Paid,
Campaign_ID = os.Campaign_ID,
Format_Name = os.Format_Name,
Clicks = os.Clicks,
Pub_Imp_Paid = os.Pub_Imp_Paid,
Ctr = os.Adv_Imp_Paid > ? os.Clicks / os.Adv_Imp_Paid :
});
});
return List;
}
public static List<AdFormatReportResultNewHourly> GetAdFormatReportHourlyALLNew(string campaignID, DateTime start, DateTime end)
{
List<AdFormatReportResultNewHourly> List = new List<AdFormatReportResultNewHourly>();
List<AdFormatReportHourly> ListA = new List<AdFormatReportHourly>();
List<AdFormatReportHourly> ListB = new List<AdFormatReportHourly>();
List<AdFormatReportHourly> List1 = new List<AdFormatReportHourly>();
List<AdFormatReportHourly> List2 = new List<AdFormatReportHourly>();
List<AdFormatReportHourly> List3 = new List<AdFormatReportHourly>();
List<AdFormatReportHourly> List4 = new List<AdFormatReportHourly>();
AdFormatReportHourly AdFormatReportHourly = new AdFormatReportHourly();
string[] constr = new[] { MySqlHelper.connectionString1, MySqlHelper.connectionString3, MySqlHelper.connectionString4 }; string sqlo = @"select campaign_id,id FROM media_buy WHERE campaign_id in (" + campaignID + @") AND status > :status";
OracleParameter pCampaignID = new OracleParameter(":status", OracleType.Int32);
pCampaignID.Value = ; DataSet ords = OracleHelper.ExecuteDataset(CONNECTION_STRING, CommandType.Text, sqlo, pCampaignID);
string mediabuyid = "";
foreach (DataRow row in ords.Tables[].Rows)
{
mediabuyid += row[] + " ,"; }
mediabuyid = mediabuyid.TrimEnd(',');
if (mediabuyid.Length == )
return List;
string creativids = "";
string sqlm = @"select creative_id,roadblock_id, coalesce(sum(adv_imp_paid),0) as adv_imp_paid,coalesce(sum(pub_imp_paid),0) as pub_imp_paid,coalesce(sum(clicks),0) as clicks
from report_data_hourly_nocity a where media_buy_id in ({0}) and settled_time >= @starttime and settled_time <@endtime and adv_flag=1 group by creative_id,roadblock_id";
sqlm = string.Format(sqlm, mediabuyid);
MySqlParameter[] myp = new MySqlParameter[] {
new MySqlParameter("@starttime", start),
new MySqlParameter("@endtime", end)
};
for (int i = ; i < ; i++)
{
DataTable dt1 = MySqlHelper.ExecuteDatasetNew(CommandType.Text, sqlm, constr[i], myp).Tables[];
if (dt1.Rows.Count > )
{
for (int j = ; j < dt1.Rows.Count; j++)
{
int creativeid = Convert.ToInt32(dt1.Rows[j]["Creative_ID"].ToString()); creativids += creativeid + ",";
int roadblockid = Convert.ToInt32(dt1.Rows[j]["roadblock_id"].ToString());
var r = List1.Find(cid => cid.Creative_ID == creativeid && cid.RoadblockId == roadblockid);
if (r == null)
{
List1.Add(new AdFormatReportHourly()
{
Campaign_ID =,
Creative_ID = creativeid,
RoadblockId = roadblockid,
Adv_Imp_Paid = Convert.ToInt64(dt1.Rows[j]["Adv_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Adv_Imp_Paid"] : ),
Pub_Imp_Paid = Convert.ToInt64(dt1.Rows[j]["Pub_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Pub_Imp_Paid"] : ),
Clicks = Convert.ToInt64(dt1.Rows[j]["Clicks"] != DBNull.Value ? dt1.Rows[j]["Clicks"] : )
});
}
else
{
r.Adv_Imp_Paid += Convert.ToInt64(dt1.Rows[j]["Adv_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Adv_Imp_Paid"] : );
r.Pub_Imp_Paid += Convert.ToInt64(dt1.Rows[j]["Pub_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Pub_Imp_Paid"] : );
r.Clicks += Convert.ToInt64(dt1.Rows[j]["Clicks"] != DBNull.Value ? dt1.Rows[j]["Clicks"] : );
}
}
}
} #region
if (creativids.Length > )
{
string sqlNC = @"select id,ad_format,format_name from (select id,ad_format,ad_size_format_desc as format_name from creative c left join AD_SIZE_FORMAT f on c.ad_format=f.format) c
where c.id in ({0}) ";
sqlNC = string.Format(sqlNC, creativids.TrimEnd(',')); DataSet ordsN2 = OracleHelper.ExecuteDataset(CONNECTION_STRING, CommandType.Text, sqlNC);
if (ordsN2.Tables[].Rows.Count > )
{
for (int k = ; k < ordsN2.Tables[].Rows.Count; k++)
{
List2.Add(new AdFormatReportHourly()
{
Creative_ID = Convert.ToInt32(ordsN2.Tables[].Rows[k]["ID"].ToString()),
Format_Name = ordsN2.Tables[].Rows[k]["Format_Name"].ToString(),
AdFormat = Convert.ToInt32(ordsN2.Tables[].Rows[k]["ad_format"].ToString())
});
}
}
}
#endregion foreach (var minfo in List1)
{
AdFormatReportHourly r = new AdFormatReportHourly();
var mymbi = List2.Find(cid => cid.Creative_ID == minfo.Creative_ID);
r.Campaign_ID =;
r.RoadblockId = minfo.RoadblockId;
r.Format_Name = mymbi != null ? mymbi.Format_Name : "";
r.AdFormat = mymbi != null ? mymbi.AdFormat : -;
r.Adv_Imp_Paid = minfo.Adv_Imp_Paid;
r.Pub_Imp_Paid = minfo.Pub_Imp_Paid;
r.Clicks = minfo.Clicks;
ListA.Add(r);
ListB.Add(r);
} ListA = ListA.FindAll(os => (os.RoadblockId != || os.AdFormat != ));
var ListA = ListA.GroupBy(a1 => new { a1.Campaign_ID, a1.Format_Name })
.Select(a2 => new AdFormatReportHourly
{
Clicks=a2.Sum(a3=>a3.Clicks),
Adv_Imp_Paid=a2.Sum(a3=>a3.Adv_Imp_Paid),
Pub_Imp_Paid=a2.Sum(a3=>a3.Pub_Imp_Paid),
Campaign_ID=a2.Key.Campaign_ID,
Format_Name=a2.Key.Format_Name
});
ListB = ListB.FindAll(os => os.RoadblockId == && os.AdFormat == ).ToList();
var listB = ListB.GroupBy(t1 => new { t1.Campaign_ID, t1.Format_Name })
.Select(t2 => new AdFormatReportHourly
{
Clicks=t2.Sum(t3=>t3.Clicks),
Adv_Imp_Paid=t2.Sum(t3=>t3.Adv_Imp_Paid),
Pub_Imp_Paid=t2.Sum(t3=>t3.Pub_Imp_Paid),
Campaign_ID=t2.Key.Campaign_ID,
Format_Name=t2.Key.Format_Name
});
foreach (var lb in listB)
{
var temp = ListA.FindAll(os => os.Format_Name == lb.Format_Name);
if (temp == null || temp.Count == )
{
ListA.Add(lb);
}
}
ListA.ForEach(os =>
{
List.Add(new AdFormatReportResultNewHourly()
{
Adv_Imp_Paid = os.Adv_Imp_Paid,
Campaign_ID = "AllCampaign",
Format_Name = os.Format_Name,
Clicks = os.Clicks,
Pub_Imp_Paid = os.Pub_Imp_Paid,
Ctr = os.Adv_Imp_Paid > ? os.Clicks / os.Adv_Imp_Paid :
});
});
return List;
}