CASUAL_NCT

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 = 30000;
        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[0];

        if (dtadspace.Rows.Count > 0)
        {
            adspaceinfo = new AdSpaceIdForHourly()
            {
                RoadBlockGroupId = Convert.ToInt32(dtadspace.Rows[0]["ROAD_BLOCK_GROUP_ID"]),
                AdSpaceId = Convert.ToInt32(dtadspace.Rows[0]["ID"]),
                AdSpaceName = dtadspace.Rows[0]["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 = 0; i < 3; i++)
        {
            DataTable dt1 = MySqlHelper.ExecuteDatasetNew(CommandType.Text, sqlhourly, constr[i], orp).Tables[0];
            if (dt1.Rows.Count > 0)
            {
                for (int x = 0; 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 = 0; i < 3; i++)
            {
                DataTable dt1 = MySqlHelper.ExecuteDatasetNew(CommandType.Text, sqlhourlypv, constr[i], orp1).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    for (int y = 0; 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 = 0; i < 3; i++)
            {
                DataTable dt1 = MySqlHelper.ExecuteDatasetNew(CommandType.Text, sqlhourlypv, constr[i], orp1).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    for (int y = 0; 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 : 0;
                r.Clicks = pubr2 != null ? pubr2.Clicks : 0;
                r.Adv = pubr2 != null ? pubr2.Adv : 0;
                r.Ctr = r.Adv > 0 ? (double)r.Clicks / r.Adv : 0;
                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 : 0;
                r.Clicks = pubr1 != null ? pubr1.Clicks : 0;
                r.Adv = pubr1 != null ? pubr1.Adv : 0;
                r.Ctr = r.Adv > 0 ? (double)r.Clicks / r.Adv : 0;
                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[0].Rows)
        {
            mediabuyid += row[1] + " ,";
        }
        mediabuyid = mediabuyid.TrimEnd(',');
        if (ords.Tables[0].Rows.Count > 0)
        {
            for (int i = 0; i < ords.Tables[0].Rows.Count; i++)
            {
                List1.Add(new MediaBuyReportFromHourly()
                 {
                     Campaign_ID = Convert.ToInt32(ords.Tables[0].Rows[i]["Campaign_ID"].ToString()),
                     Media_Buy_ID = Convert.ToInt32(ords.Tables[0].Rows[i]["Media_Buy_ID"].ToString()),
                     Start_Time = Convert.ToDateTime(ords.Tables[0].Rows[i]["Start_Time"].ToString()),
                     End_Time = Convert.ToDateTime(ords.Tables[0].Rows[i]["End_Time"].ToString()),
                     Name = ords.Tables[0].Rows[i]["Name"].ToString(),
                     Budget = Convert.ToInt32(ords.Tables[0].Rows[i]["Budget"]),
                     Pacing_Disabled = Convert.ToInt32(ords.Tables[0].Rows[i]["Pacing_Disabled"]),
                     Minute_Budget = Convert.ToDouble(ords.Tables[0].Rows[i]["Minute_Budget"]),
                     Balance = Convert.ToDouble(ords.Tables[0].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 = 0; i < 3; i++)
        {
            DataTable dt1 = MySqlHelper.ExecuteDatasetNew(CommandType.Text, sqlm, constr[i], myp).Tables[0];
            if (dt1.Rows.Count > 0)
            {
                for (int j = 0; 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"] : 0),
                            Adv_Imp_Paid = Convert.ToInt64(dt1.Rows[j]["Adv_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Adv_Imp_Paid"] : 0),
                            Pub_Imp_Paid = Convert.ToInt64(dt1.Rows[j]["Pub_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Pub_Imp_Paid"] : 0),
                            Clicks = Convert.ToInt64(dt1.Rows[j]["Clicks"] != DBNull.Value ? dt1.Rows[j]["Clicks"] : 0)

                        });
                    }
                    else
                    {
                        r.Adv_Imp_Paid += Convert.ToInt64(dt1.Rows[j]["Adv_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Adv_Imp_Paid"] : 0);
                        r.Pub_Imp_Paid += Convert.ToInt64(dt1.Rows[j]["Pub_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Pub_Imp_Paid"] : 0);
                        r.Clicks += Convert.ToInt64(dt1.Rows[j]["Clicks"] != DBNull.Value ? dt1.Rows[j]["Clicks"] : 0);
                    }
                }

            }
        }
        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 > 0 ? (double)r.Clicks / r.Adv_Imp_Paid : 0;
            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[0].Rows)
        {
            mediabuyid += row[1] + " ,";
        }
        mediabuyid = mediabuyid.TrimEnd(',');
        if (mediabuyid.Length == 0)
            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 = 0; i < 3; i++)
        {
            DataTable dt1 = MySqlHelper.ExecuteDatasetNew(CommandType.Text, sqlm, constr[i], myp).Tables[0];
            if (dt1.Rows.Count > 0)
            {
                for (int j = 0; 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"] : 0),
                            Pub_Imp_Paid = Convert.ToInt64(dt1.Rows[j]["Pub_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Pub_Imp_Paid"] : 0),
                            Clicks = Convert.ToInt64(dt1.Rows[j]["Clicks"] != DBNull.Value ? dt1.Rows[j]["Clicks"] : 0)
                        });
                    }
                    else
                    {
                        r.Adv_Imp_Paid += Convert.ToInt64(dt1.Rows[j]["Adv_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Adv_Imp_Paid"] : 0);
                        r.Pub_Imp_Paid += Convert.ToInt64(dt1.Rows[j]["Pub_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Pub_Imp_Paid"] : 0);
                        r.Clicks += Convert.ToInt64(dt1.Rows[j]["Clicks"] != DBNull.Value ? dt1.Rows[j]["Clicks"] : 0);
                    }
                }
            }
        }

        #region
        if (creativids.Length > 0)
        {
            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[0].Rows.Count > 0)
            {
                for (int k = 0; k < ordsN2.Tables[0].Rows.Count; k++)
                {
                    List2.Add(new AdFormatReportHourly()
                    {
                        Creative_ID = Convert.ToInt32(ordsN2.Tables[0].Rows[k]["ID"].ToString()),
                        Format_Name = ordsN2.Tables[0].Rows[k]["Format_Name"].ToString(),
                        AdFormat = Convert.ToInt32(ordsN2.Tables[0].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 : -1;
            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 != 0 || os.AdFormat != 6));

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 == 0 && os.AdFormat == 6).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 == 0)
            {
                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 > 0 ? os.Clicks / os.Adv_Imp_Paid : 0
            });
        });
        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 = 0;
       
        DataSet ords = OracleHelper.ExecuteDataset(CONNECTION_STRING, CommandType.Text, sqlo, pCampaignID);
        string mediabuyid = "";
        foreach (DataRow row in ords.Tables[0].Rows)
        {
            mediabuyid += row[1] + " ,";
         
        }
        mediabuyid = mediabuyid.TrimEnd(',');
        if (mediabuyid.Length == 0)
            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 = 0; i < 3; i++)
        {
            DataTable dt1 = MySqlHelper.ExecuteDatasetNew(CommandType.Text, sqlm, constr[i], myp).Tables[0];
            if (dt1.Rows.Count > 0)
            {
                for (int j = 0; 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 =0,
                            Creative_ID = creativeid,
                            RoadblockId = roadblockid,
                            Adv_Imp_Paid = Convert.ToInt64(dt1.Rows[j]["Adv_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Adv_Imp_Paid"] : 0),
                            Pub_Imp_Paid = Convert.ToInt64(dt1.Rows[j]["Pub_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Pub_Imp_Paid"] : 0),
                            Clicks = Convert.ToInt64(dt1.Rows[j]["Clicks"] != DBNull.Value ? dt1.Rows[j]["Clicks"] : 0)
                        });
                    }
                    else
                    {
                        r.Adv_Imp_Paid += Convert.ToInt64(dt1.Rows[j]["Adv_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Adv_Imp_Paid"] : 0);
                        r.Pub_Imp_Paid += Convert.ToInt64(dt1.Rows[j]["Pub_Imp_Paid"] != DBNull.Value ? dt1.Rows[j]["Pub_Imp_Paid"] : 0);
                        r.Clicks += Convert.ToInt64(dt1.Rows[j]["Clicks"] != DBNull.Value ? dt1.Rows[j]["Clicks"] : 0);
                    }
                }
            }
        }

        #region
        if (creativids.Length > 0)
        {
            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[0].Rows.Count > 0)
            {
                for (int k = 0; k < ordsN2.Tables[0].Rows.Count; k++)
                {
                    List2.Add(new AdFormatReportHourly()
                    {
                        Creative_ID = Convert.ToInt32(ordsN2.Tables[0].Rows[k]["ID"].ToString()),
                        Format_Name = ordsN2.Tables[0].Rows[k]["Format_Name"].ToString(),
                        AdFormat = Convert.ToInt32(ordsN2.Tables[0].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 =0;
            r.RoadblockId = minfo.RoadblockId;
            r.Format_Name = mymbi != null ? mymbi.Format_Name : "";
            r.AdFormat = mymbi != null ? mymbi.AdFormat : -1;
            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 != 0 || os.AdFormat != 6));

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 == 0 && os.AdFormat == 6).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 == 0)
            {
                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 > 0 ? os.Clicks / os.Adv_Imp_Paid : 0
            });
        });
        return List;
    }