Luckycall/ReportWebCore/SuperAdmin/Source/DbConnector.cs

using Db_Core;
using log4net;
using SuperAdmin.Models;
using System;
using System.Collections.Generic;
using System.Data.Common;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using ReportWeb.Models;
using ReportWeb.Source;
using ReportWebCore.Models.Http;

namespace SuperAdmin.Source
{
    /// <summary>
    /// Summary description for DbConnector
    /// </summary>
    public class DbConnector
    {
        private readonly ILog log = LogManager.GetLogger(typeof(DbConnector));
        private ConnectionType connType = ConnectionType.ORACLE;

        public List<Services> LoadListServices()
        {
            try
            {
                String sql = @"select * from sv where status = 1";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                var rs = Services.Parse((OracleDataReader)stmt.ExecuteReader(connType));
                stmt.CloseConnection();
                return rs;
            }
            catch (Exception ex)
            {
                log.Error("Error LoadListServices", ex);
                return null;
            }
        }

        public List<Services> LoadListServicesReport()
        {
            try
            {
                String sql = @"select * from sv join sv_report sr on sv.sv_code = sr.sv_code where sv.status = 1";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                var rs = Services.Parse((OracleDataReader)stmt.ExecuteReader(connType));
                stmt.CloseConnection();
                return rs;
            }
            catch (Exception ex)
            {
                log.Error("Error LoadListServicesReport", ex);
                return null;
            }
        }

        public List<Services> LoadListServicesAdv(int status)
        {
            try
            {
                String sql = @"select sv.id, sv.sv_code, sv.sv_name,
                        a.msg_adv, a.adv_name, a.channel_adv, a.message_type, a.from_money, a.status, a.id adv_id , a.active_hour, a.max_number_msg
                        from sv
                        join sv_adv a 
                        on sv.sv_code = a.sv_code where sv.status = 1 
                        [ and a.status = ? ]";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                if (status >= 0)
                {
                    stmt.SetParameter(0, status);
                }
                var rs = Services.Parse((OracleDataReader)stmt.ExecuteReader(connType));
                stmt.CloseConnection();
                return rs;
            }
            catch (Exception ex)
            {
                log.Error("Error LoadListServicesAdv", ex);
                return null;
            }
        }

        public List<Users> GetUserByName(string username)
        {
            try
            {
                log.Info("Error GetUserByName: " + username);
                String sql = @"select * from user_report where status = 1 and username = ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                log.Info(stmt.connectionString);
                stmt.SetParameters(username);
                var rs = Users.Parse((OracleDataReader)stmt.ExecuteReader(connType));
                stmt.CloseConnection();
                return rs;
            }
            catch (Exception ex)
            {
                log.Error("Error GetUserByName", ex);
                return null;
            }
        }

        public Services GetServiceById(string serviceId)
        {
            try
            {
                String sql = @"select * from sv where status = 1 and id = ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(serviceId);
                var rs = Services.Parse((OracleDataReader)stmt.ExecuteReader(connType));
                stmt.CloseConnection();
                if (rs != null && rs.Count > 0)
                {
                    return rs[0];
                }
                return null;
            }
            catch (Exception ex)
            {
                log.Error("Error GetServiceById", ex);
                return null;
            }
        }

        public Services GetServiceRpBySvCode(string svCode)
        {
            try
            {
                String sql = @"select * from sv_report where status = 1 and sv_code = ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(svCode);
                var rs = Services.Parse((OracleDataReader)stmt.ExecuteReader(connType));
                stmt.CloseConnection();
                if (rs != null && rs.Count > 0)
                {
                    return rs[0];
                }
                return null;
            }
            catch (Exception ex)
            {
                log.Error("Error GetServiceRpBySvCode", ex);
                return null;
            }
        }

        public Services GetServiceAdvById(string advId)
        {
            try
            {
                String sql = @"select a.*, a.id adv_id from sv_adv a where id = ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(advId);
                var rs = Services.Parse((OracleDataReader)stmt.ExecuteReader(connType));
                stmt.CloseConnection();
                if (rs != null && rs.Count > 0)
                {
                    return rs[0];
                }
                return null;
            }
            catch (Exception ex)
            {
                log.Error("Error GetServiceAdvById", ex);
                return null;
            }
        }

        public List<DailyReport> GetCommonReport(DateTime fromDate, DateTime toDate)
        {
            DbConnection myConnection = null;
            SqlStatement stmt = new SqlStatement(SqlConnString.GAMEPORTAL);
            String sqlCommand = "SELECT a.sv_code, sv.sv_name service_name, sv.id service_id, to_char(a.report_date,'dd/mm/yyyy') report_date, a.revenue, a.count_reg, a.paid_money, a.count_active, a.count_deactive, a.revenue_buy " +
                " FROM daily_report a, sv " +
                "WHERE a.sv_code = sv.sv_code AND report_date >= :fromDate AND report_date <= :toDate ORDER BY a.sv_code, a.report_date";
            try
            {
                using (myConnection = (DbConnection)stmt.GetConnection(connType))
                {
                    DbCommand myCommand = myConnection.CreateCommand();
                    myCommand.CommandText = sqlCommand;
                    myCommand.Parameters.Clear();

                    // from date 
                    AddCmdParam(ref myCommand, "fromDate", fromDate);

                    // todate
                    AddCmdParam(ref myCommand, "toDate", toDate);

                    DbDataReader reader = myCommand.ExecuteReader();

                    return DailyReport.Parse((OracleDataReader)reader);
                }
            }
            catch (Exception ex)
            {
                log.Error("Error GetDailyReport", ex);
                return null;
            }
            finally
            {
                try
                {
                    if (myConnection != null)
                    {
                        myConnection.Close();
                    }
                }
                catch { }
            }
        }

        public List<DailyReport> GetTotalReport(DateTime fromDate, DateTime toDate)
        {
            DbConnection myConnection = null;
            SqlStatement stmt = new SqlStatement(SqlConnString.GAMEPORTAL);
            String sqlCommand = "SELECT to_char(a.report_date,'dd/mm/yyyy') report_date, sum(a.revenue) revenue," +
                " sum(a.count_reg) count_reg, sum(a.paid_money) paid_money, sum(a.count_active) count_active, sum(a.count_deactive) count_deactive FROM daily_report a  " +
                "WHERE report_date >= :fromDate AND report_date <= :toDate GROUP by a.report_date ORDER BY a.report_date";
            try
            {
                using (myConnection = (DbConnection)stmt.GetConnection(connType))
                {
                    DbCommand myCommand = myConnection.CreateCommand();
                    myCommand.CommandText = sqlCommand;
                    myCommand.Parameters.Clear();

                    // from date 
                    AddCmdParam(ref myCommand, "fromDate", fromDate);

                    // todate
                    AddCmdParam(ref myCommand, "toDate", toDate);

                    DbDataReader reader = myCommand.ExecuteReader();

                    return DailyReport.Parse((OracleDataReader)reader);
                }
            }
            catch (Exception ex)
            {
                log.Error("Error GetDailyReport", ex);
                return null;
            }
            finally
            {
                try
                {
                    if (myConnection != null)
                    {
                        myConnection.Close();
                    }
                }
                catch { }
            }
        }

        public List<DailyReport> GetDailyReport(string serviceCode, DateTime fromDate, DateTime toDate)
        {
            DbConnection myConnection = null;
            SqlStatement stmt = new SqlStatement(SqlConnString.GAMEPORTAL);
            String sqlCommand =
                "SELECT a.sv_code, to_char(a.report_date,'dd/mm/yyyy') report_date, a.revenue, a.revenue_buy, a.count_reg, a.paid_money, a.count_active, a.count_deactive " +
                "FROM daily_report a " +
                "WHERE sv_code = :service_code AND report_date >= :fromDate AND report_date <= :toDate ORDER BY a.report_date desc";
            try
            {
                using (myConnection = (DbConnection)stmt.GetConnection(connType))
                {
                    DbCommand myCommand = myConnection.CreateCommand();
                    myCommand.CommandText = sqlCommand;
                    myCommand.Parameters.Clear();

                    // service code
                    AddCmdParam(ref myCommand, "service_code", serviceCode);

                    // from date 
                    AddCmdParam(ref myCommand, "fromDate", fromDate);

                    // todate
                    AddCmdParam(ref myCommand, "toDate", toDate);

                    DbDataReader reader = myCommand.ExecuteReader();

                    return DailyReport.Parse((OracleDataReader)reader);
                }
            }
            catch (Exception ex)
            {
                log.Error("Error GetDailyReport", ex);
                return null;
            }
            finally
            {
                try
                {
                    if (myConnection != null)
                    {
                        myConnection.Close();
                    }
                }
                catch { }
            }
        }

        public List<DailyReport> GetDailyReportAccu(string serviceCode, DateTime fromDate, DateTime toDate)
        {
            DbConnection myConnection = null;
            SqlStatement stmt = new SqlStatement(SqlConnString.GAMEPORTAL);
            String sqlCommand = "SELECT a.sv_code, to_char(a.report_date,'dd/mm/yyyy') report_date, a.revenue, a.revenue_buy, a.count_reg, a.paid_money, a.count_active, a.count_deactive " +
                "FROM daily_report a " +
                "WHERE sv_code = :service_code AND report_date >= :fromDate AND report_date <= :toDate ORDER BY a.report_date desc";
            try
            {
                using (myConnection = (DbConnection)stmt.GetConnection(connType))
                {
                    DbCommand myCommand = myConnection.CreateCommand();
                    myCommand.CommandText = sqlCommand;
                    myCommand.Parameters.Clear();

                    // service code
                    AddCmdParam(ref myCommand, "service_code", serviceCode);

                    // from date 
                    AddCmdParam(ref myCommand, "fromDate", fromDate);

                    // todate
                    AddCmdParam(ref myCommand, "toDate", toDate);

                    DbDataReader reader = myCommand.ExecuteReader();

                    return DailyReport.Parse((OracleDataReader)reader);
                }
            }
            catch (Exception ex)
            {
                log.Error("Error GetDailyReport", ex);
                return null;
            }
            finally
            {
                try
                {
                    if (myConnection != null)
                    {
                        myConnection.Close();
                    }
                }
                catch { }
            }
        }

        public List<DailyReport> GetHourlyReport(string serviceCode, DateTime fromDate, DateTime toDate)
        {
            DbConnection myConnection = null;
            SqlStatement stmt = new SqlStatement(SqlConnString.GAMEPORTAL);
            String sqlCommand =
                "SELECT a.sv_code, to_char(a.report_date,'dd/mm/yyyy hh24') report_date, a.revenue, a.revenue_buy, a.count_reg, a.paid_money, a.count_active, a.count_deactive " +
                "FROM  hourly_report a " +
                "WHERE sv_code = :service_code AND report_date >= :fromDate AND report_date <= :toDate + 1 ORDER BY a.report_date desc";
            try
            {
                using (myConnection = (DbConnection)stmt.GetConnection(connType))
                {
                    DbCommand myCommand = myConnection.CreateCommand();
                    myCommand.CommandText = sqlCommand;
                    myCommand.Parameters.Clear();

                    // service code
                    AddCmdParam(ref myCommand, "service_code", serviceCode);

                    // from date 
                    AddCmdParam(ref myCommand, "fromDate", fromDate);

                    // todate
                    AddCmdParam(ref myCommand, "toDate", toDate);

                    DbDataReader reader = myCommand.ExecuteReader();

                    return DailyReport.Parse((OracleDataReader)reader);
                }
            }
            catch (Exception ex)
            {
                log.Error("Error GetHourlyReport", ex);
                return null;
            }
            finally
            {
                try
                {
                    if (myConnection != null)
                    {
                        myConnection.Close();
                    }
                }
                catch { }
            }
        }

        public List<DailyReport> GetTotalHourlyReport(DateTime fromDate, DateTime toDate)
        {
            DbConnection myConnection = null;
            SqlStatement stmt = new SqlStatement(SqlConnString.GAMEPORTAL);
            String sqlCommand = "SELECT to_char(a.report_date,'dd/mm/yyyy hh24') report_date, sum(a.revenue) revenue," +
                " sum(a.count_reg) count_reg, sum(a.paid_money) paid_money, sum(a.count_active) count_active, sum(a.count_deactive) count_deactive FROM hourly_report a  " +
                "WHERE report_date >= trunc(:fromDate) AND report_date < trunc(:toDate) + 1 GROUP by a.report_date ORDER BY a.report_date";
            try
            {
                using (myConnection = (DbConnection)stmt.GetConnection(connType))
                {
                    DbCommand myCommand = myConnection.CreateCommand();
                    myCommand.CommandText = sqlCommand;
                    myCommand.Parameters.Clear();


                    // from date 
                    AddCmdParam(ref myCommand, "fromDate", fromDate);

                    // todate
                    AddCmdParam(ref myCommand, "toDate", toDate);

                    DbDataReader reader = myCommand.ExecuteReader();

                    return DailyReport.Parse((OracleDataReader)reader);
                }
            }
            catch (Exception ex)
            {
                log.Error("Error GetTotalHourlyRerpot", ex);
                return null;
            }
            finally
            {
                try
                {
                    if (myConnection != null)
                    {
                        myConnection.Close();
                    }
                }
                catch { }
            }
        }

        public DataTable GetRevenueHourly(string serviceCode, DateTime fromDate, DateTime toDate)
        {
            DbConnection myConnection = null;
            SqlStatement stmt = new SqlStatement(SqlConnString.GAMEPORTAL);
            String sqlCommand =
                @"select * from  (SELECT sv_code, report_date,
                       SUM(revenue) as TOTAL,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 0 AND to_number(to_char(report_hour, 'hh24')) < 1  THEN revenue ELSE 0 END) as h00,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 1 AND to_number(to_char(report_hour, 'hh24')) < 2  THEN revenue ELSE 0 END) as h01,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 2 AND to_number(to_char(report_hour, 'hh24')) < 3  THEN revenue ELSE 0 END) as h02,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 3 AND to_number(to_char(report_hour, 'hh24')) < 4  THEN revenue ELSE 0 END) as h03,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 4 AND to_number(to_char(report_hour, 'hh24')) < 5  THEN revenue ELSE 0 END) as h04,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 5 AND to_number(to_char(report_hour, 'hh24')) < 6  THEN revenue ELSE 0 END) as h05,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 6 AND to_number(to_char(report_hour, 'hh24')) < 7  THEN revenue ELSE 0 END) as h06,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 7 AND to_number(to_char(report_hour, 'hh24')) < 8  THEN revenue ELSE 0 END) as h07,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 8 AND to_number(to_char(report_hour, 'hh24')) < 9  THEN revenue ELSE 0 END) as h08,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 9 AND to_number(to_char(report_hour, 'hh24')) < 10  THEN revenue ELSE 0 END) as h09,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 10 AND to_number(to_char(report_hour, 'hh24')) < 11  THEN revenue ELSE 0 END) as h10,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 11 AND to_number(to_char(report_hour, 'hh24')) < 12  THEN revenue ELSE 0 END) as h11,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 12 AND to_number(to_char(report_hour, 'hh24')) < 13  THEN revenue ELSE 0 END) as h12,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 13 AND to_number(to_char(report_hour, 'hh24')) < 14  THEN revenue ELSE 0 END) as h13,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 14 AND to_number(to_char(report_hour, 'hh24')) < 15  THEN revenue ELSE 0 END) as h14,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 15 AND to_number(to_char(report_hour, 'hh24')) < 16  THEN revenue ELSE 0 END) as h15,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 16 AND to_number(to_char(report_hour, 'hh24')) < 17  THEN revenue ELSE 0 END) as h16,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 17 AND to_number(to_char(report_hour, 'hh24')) < 18  THEN revenue ELSE 0 END) as h17,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 18 AND to_number(to_char(report_hour, 'hh24')) < 19  THEN revenue ELSE 0 END) as h18,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 19 AND to_number(to_char(report_hour, 'hh24')) < 20  THEN revenue ELSE 0 END) as h19,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 20 AND to_number(to_char(report_hour, 'hh24')) < 21  THEN revenue ELSE 0 END) as h20,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 21 AND to_number(to_char(report_hour, 'hh24')) < 22  THEN revenue ELSE 0 END) as h21,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 22 AND to_number(to_char(report_hour, 'hh24')) < 23  THEN revenue ELSE 0 END) as h22,
                       SUM(CASE WHEN to_number(to_char(report_hour, 'hh24')) >= 23 AND to_number(to_char(report_hour, 'hh24')) < 24  THEN revenue ELSE 0 END) as h23
                       FROM(
                            SELECT trunc(report_date) report_date,
                                   sv_code,
                                   revenue,
                                   report_date report_hour
                            FROM hourly_report
                       )
            
            GROUP BY sv_code, report_date 
            ) 
            WHERE sv_code = :service_code AND report_date >= :fromDate AND report_date <= :toDate ORDER BY report_date desc";
            try
            {
                using (myConnection = (DbConnection)stmt.GetConnection(connType))
                {
                    DbCommand myCommand = myConnection.CreateCommand();
                    myCommand.CommandText = sqlCommand;
                    myCommand.Parameters.Clear();

                    // service code
                    AddCmdParam(ref myCommand, "service_code", serviceCode);

                    // from date 
                    AddCmdParam(ref myCommand, "fromDate", fromDate);

                    // todate
                    AddCmdParam(ref myCommand, "toDate", toDate);

                    DbDataReader reader = myCommand.ExecuteReader();
                    var tb = new DataTable();
                    tb.Load(reader);
                    return tb;
                }
            }
            catch (Exception ex)
            {
                log.Error("Error GetRevenueHourly", ex);
                return null;
            }
            finally
            {
                try
                {
                    if (myConnection != null)
                    {
                        myConnection.Close();
                    }
                }
                catch { }
            }
        }

        public List<ChargeLog> GetChargeLog(Services sv, String msisdn, DateTime fromDate, DateTime toDate)
        {
            try
            {
                String sqlCommand = "SELECT * FROM " + sv.db_name + ".charge_log WHERE msisdn = ? AND insert_time >= ? AND insert_time - 1 <= ? ORDER BY insert_time desc";
                SqlStatement stmt = new SqlStatement(sqlCommand, BuildConnectionString(sv));
                stmt.SetParameters(msisdn, fromDate, toDate);
                return ChargeLog.Parse(stmt.ExecuteReader());
            }
            catch (Exception ex)
            {
                log.Error("Error GetChargeLog " + msisdn, ex);
                return null;
            }
        }

        public List<RegInfo> GetRegisterInfo(Services sv, String msisdn, DateTime fromDate, DateTime toDate)
        {
            try
            {
                String sqlCommand = "SELECT * FROM " + sv.db_name + ".reg_info WHERE msisdn = ?  AND expire_time >= ? AND register_time - 1 <= ? ORDER BY register_time desc";
                SqlStatement stmt = new SqlStatement(sqlCommand, BuildConnectionString(sv));
                stmt.SetParameters(msisdn, fromDate, toDate);
                return RegInfo.Parse(stmt.ExecuteReader());
            }
            catch (Exception ex)
            {
                log.Error("Error Get RegInfo " + msisdn, ex);
                return null;
            }
        }

        // lucky spin
        public List<SpinLog> GetLuckySpin(String msisdn, DateTime fromDate, DateTime toDate)
        {
            try
            {
                String sqlCommand = "SELECT * FROM  lucky_spin WHERE msisdn = ?  AND expire_time >= ? AND insert_time - 1 <= ? ORDER BY expire_time desc";
                SqlStatement stmt = new SqlStatement(sqlCommand, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(msisdn, fromDate, toDate);
                return SpinLog.Parse(stmt.ExecuteReader());
            }
            catch (Exception ex)
            {
                log.Error("Error Get Spin log " + msisdn, ex);
                return null;
            }
        }

        public int CountLuckySpin(String msisdn)
        {
            SqlStatement stmt = null;
            try
            {
                String sqlCommand = "select nvl(sum(added - used),0) remain_spin, nvl(sum(added),0) total_spin,  nvl(sum(used),0) used_spin  from lucky_spin "
                + " where msisdn = ? and expire_time > sysdate";
                stmt = new SqlStatement(sqlCommand, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(msisdn);
                using (OracleDataReader reader = (OracleDataReader)stmt.ExecuteReader(connType))
                {
                    while (reader.Read())
                    {
                        return int.Parse(reader.GetValue("remain_spin").ToString());
                    }
                    return 0;
                }
            }
            catch (Exception ex)
            {
                log.Error("Error Get Spin log " + msisdn, ex);
                return 0;
            }
            finally
            {
                try
                {
                    stmt.CloseConnection();
                }
                catch { }
            }

        }
        public List<LuckyCode> GetListLuckyCode(String msisdn, String code, String codeType, String period, String redeemStatus, DateTime? fromDate, DateTime? toDate)
        {
            try
            {
                String sqlCommand = @"
                        SELECT  *
                          FROM   (SELECT   *
                                    FROM   (SELECT    a.id, a.msisdn, a.code, a.insert_time, a.expire_time, a.period,
                                        a.status, a.confirm_time, a.confirm_channel, a.fee, a.code_type FROM lucky_code a
                                            WHERE  1=1
                                            [ AND insert_time >= trunc(?) ]
                                           [ AND insert_time < trunc(?) + 1 ]
                                           [ AND msisdn like '%' || ? || '%' ]  
                                           [ AND code = ? ]   
                                           [ AND period = ? ]
                                           [ AND status = ? ]
                                           [ AND code_type = ? ]
                                                )
                                           lc
                                  UNION ALL
                                  (SELECT   a.id, a.msisdn, a.code, a.insert_time, a.expire_time, a.period,
                                        a.status, a.confirm_time, a.confirm_channel, a.fee, a.code_type FROM lucky_code_his a
                                        WHERE  1=1
                                           [ AND insert_time >= trunc(?) ]
                                           [ AND insert_time < trunc(?) + 1 ]
                                           [ AND msisdn like '%' || ? || '%' ]  
                                           [ AND code = ? ]   
                                           [ AND period = ? ]
                                           [ AND status = ? ]
                                           [ AND code_type = ? ]
                                    )
                                ) ";

                SqlStatement stmt = new SqlStatement(sqlCommand, SqlConnString.GAMEPORTAL);
                int numPa = 7;
                if (fromDate != null)
                {
                    stmt.SetParameter(0, fromDate);
                    stmt.SetParameter(0 + numPa, fromDate);
                }
                if (toDate != null)
                {
                    stmt.SetParameter(1, toDate);
                    stmt.SetParameter(1 + numPa, toDate);
                }
                if (msisdn != null && msisdn.Length > 0)
                {
                    stmt.SetParameter(2, msisdn);
                    stmt.SetParameter(2 + numPa, msisdn);
                }
                if (code != null && code.Length > 0)
                {
                    stmt.SetParameter(3, code);
                    stmt.SetParameter(3 + numPa, code);
                }
                if (period != null && period.Length > 0)
                {
                    stmt.SetParameter(4, period);
                    stmt.SetParameter(4 + numPa, period);
                }
                if (redeemStatus != null && redeemStatus != "-1" && redeemStatus.Length > 0)
                {
                    stmt.SetParameter(5, redeemStatus);
                    stmt.SetParameter(5 + numPa, redeemStatus);
                }

                if (codeType != null && codeType.Length > 0 && codeType != "-1")
                {
                    stmt.SetParameter(6, codeType);
                    stmt.SetParameter(6 + numPa, codeType);
                }
                return LuckyCode.Parse(stmt.ExecuteReader());
            }
            catch (Exception ex)
            {
                log.Error("Error GetListLuckyCode " + msisdn, ex);
                return null;
            }
        }

        // RANKING
        public List<UserCoin> GetRankingCoin(String msisdn, String month, int maxRow)
        {
            try
            {
                String sqlCommand =
                    @"SELECT a.*  FROM 
                        (SELECT *
                        FROM ( SELECT  rownum rank, a.*
                               FROM (SELECT  c.*, r.product_name, p.prize_id, p.process_time, p.execute_id
                                    FROM user_coin c 
                                    left join (select * from reg_info where status = 1) r  on c.msisdn = r.msisdn
                                    left join (select * from prize_winner where period = 3 and code_time >= to_date(?, 'mm/yyyy') and code_time <= ADD_MONTHS(to_date(?, 'mm/yyyy'), 1) ) p on c.msisdn =  p.msisdn 
                                    where c.start_time <= to_date(?, 'mm/yyyy') and c.end_time  >= to_date(?, 'mm/yyyy')
                                         
                                    ORDER BY c.total_coin DESC, c.last_update) a )
                        WHERE 1=1 
                          [ and msisdn = ? ]
                          [ and rank <= ? ]
                        ) a";
                //String sqlCommand = "SELECT * FROM (SELECT * FROM  user_coin WHERE start_time <= to_date(?, 'mm/yyyy') and end_time  >= to_date(?, 'mm/yyyy') " +
                //    " [ and msisdn = ? ]" +
                //    " ORDER BY total_coin desc, last_update) " +
                //    " where 1=1 " +
                //    " [and rownum <= ?]";
                SqlStatement stmt = new SqlStatement(sqlCommand, SqlConnString.GAMEPORTAL);
                stmt.SetParameter(0, month);
                stmt.SetParameter(1, month);
                stmt.SetParameter(2, month);
                stmt.SetParameter(3, month);
                if (msisdn != null & msisdn.Length > 0)
                {
                    stmt.SetParameter(4, msisdn);
                }
                else
                {
                    if (maxRow > 0)
                    {
                        stmt.SetParameter(5, maxRow);
                    }
                }
                return UserCoin.Parse(stmt.ExecuteReader());
            }
            catch (Exception ex)
            {
                log.Error("Error Get ranking coin " + msisdn, ex);
                return null;
            }
        }

        public List<PrizeObj> GetListPrizeWinner(String msisdn, String period, DateTime? fromDate, DateTime? toDate)
        {
            try
            {
                String sqlCommand = @"
                        SELECT   *
                          FROM   (  SELECT   pw.*,
                                             lch.status status,
                                             g.is_auto,
                                             g.times_prize,
                                             lp.description,
                                             lp.prize_name,
                                             lp.is_top
                                      FROM           prize_winner pw
                                                 LEFT JOIN
                                                     list_prize lp
                                                 ON pw.prize_id = lp.id
                                             LEFT JOIN
                                                 group_prize g
                                                    ON lp.sub_group_prize = g.sub_group_id
                                            left join 
                                                execute_prize_his lch on lch.prize_winner_id =  pw.id 
                                     WHERE   1 = 1
                                  ORDER BY   code_time DESC)
                         WHERE  1=1
                               [ AND code_time >= trunc(?) ]
                               [ AND code_time < trunc(?) + 1 ]
                               [ AND msisdn like '%' || ? || '%' ]   
                               [ AND period = ? ]";

                SqlStatement stmt = new SqlStatement(sqlCommand, SqlConnString.GAMEPORTAL);
                if (fromDate != null)
                {
                    stmt.SetParameter(0, fromDate);
                }
                if (toDate != null)
                {
                    stmt.SetParameter(1, toDate);
                }
                if (msisdn != null && msisdn.Length > 0)
                {
                    stmt.SetParameter(2, msisdn);
                }
                if (period != null && period.Length > 0 && period != "-1")
                {
                    stmt.SetParameter(3, period);
                }
                return PrizeObj.Parse(stmt.ExecuteReader());
            }
            catch (Exception ex)
            {
                log.Error("Error GetListPrizeWinner " + msisdn, ex);
                return null;
            }
        }

        public PrizeObj GetWinnerById(int id)
        {
            try
            {
                String sqlCommand = @"
                       SELECT * FROM prize_winner where id = ?";

                SqlStatement stmt = new SqlStatement(sqlCommand, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(id);
                List<PrizeObj> list = PrizeObj.Parse(stmt.ExecuteReader());
                return list[0];
            }
            catch (Exception ex)
            {
                log.Error("Error GetWinnerById " + id, ex);
                return null;
            }
        }

        public bool InsertPrizeWinner(String msisdn, int prizeId, String code, DateTime codeTime, int codeType)
        {
            try
            {
                String sql = @"INSERT INTO prize_winner (ID,MSISDN,PRIZE_ID,CODE,CODE_TIME,PROCESS_TIME,CODE_TYPE)
                                VALUES(prize_winner_seq.nextval, ?, ?, ?, ?, NULL, ?)";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(msisdn, prizeId, code, codeTime, codeType);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error InsertService", ex);
                return false;
            }
        }


        public bool DeleteWinner(int id)
        {
            try
            {
                String sql = "DELETE FROM prize_winner WHERE id= ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(id);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error DeleteWinner", ex);
                return false;
            }
        }


        public bool UpdateSendWinner(int id, String user)
        {
            try
            {
                String sql = "UPDATE   prize_winner set process_time = sysdate, processed_by = ?  WHERE id= ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(user, id);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error UpdateSendWinner", ex);
                return false;
            }
        }

        public bool SendMessage(String messageCode, String msisdn, String channel)
        {
            try
            {
                String sql = @"insert into mt values(
                                mt_seq.nextval, 0, ?, (select distinct(param_value) from config where param_name = ?), sysdate, 0, ?
                                )";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(msisdn, messageCode, channel);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error InsertService", ex);
                return false;
            }
        }

        public List<PrizeObj> LoadListPrize(int period, int is_auto)
        {
            try
            {
                String sqlCommand = @"
                        SELECT   *
                          FROM   (  SELECT   lp.id, g.period,
                                             g.channel,
                                             g.is_auto,
                                             lp.description,
                                             lp.prize_name,
                                             lp.is_top
                                      FROM   list_prize lp
                                             LEFT JOIN
                                                 group_prize g
                                             ON lp.sub_group_prize = g.sub_group_id
                                     WHERE   1 = 1 )
                         WHERE  1=1 and period = ? and is_auto = ? ";

                SqlStatement stmt = new SqlStatement(sqlCommand, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(period, is_auto);
                return PrizeObj.Parse(stmt.ExecuteReader());
            }
            catch (Exception ex)
            {
                log.Error("Error LoadListPrize ", ex);
                return null;
            }
        }

        private String BuildConnectionString(Services sv)
        {
            String desc = sv.connection_string.Split('@')[1];
            return "Data Source=" + desc + "; " +
               " User ID=" + sv.db_username + ";Password=" + sv.db_password + ";Max Pool Size=3;";
        }

        public bool InsertService(string svCode, string svName)
        {
            try
            {
                String sql = @"INSERT INTO sv (ID,SV_CODE,SV_NAME,STATUS) 
                VALUES(sv_seq.nextval, ?, ?, 1)";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(svCode, svName);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error InsertService", ex);
                return false;
            }
        }
        public bool UpdateServiceInfo(string serviceId, string serviceCode, string serviceName)
        {
            try
            {
                String sql = @"Update sv set sv_code = ?, sv_name = ? where id = ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(serviceCode, serviceName, serviceId);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error UpdateServiceInfo", ex);
                return false;
            }
        }

        public bool UpdateServiceStatus(string svId, string status)
        {
            try
            {
                String sql = @"Update sv set  status = ? where id = ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(status, svId);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error UpdateServiceStatus", ex);
                return false;
            }
        }

        public bool InsertSvReport(string svCode, string dbName, string connectionString, string dbUsername, string dbPassword, string sqlReportDaily, string sqlReportHourly)
        {
            try
            {
                String sql = @"INSERT INTO sv_report (SV_CODE,DB_NAME,CONNECTION_STRING,DB_USERNAME,DB_PASSWORD,SQL_REPORT_DAILY,SQL_REPORT_HOURLY,STATUS) 
                    VALUES(?, ?, ?, ?, ?, ?, ?, 1)";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(svCode, dbName, connectionString, dbUsername, dbPassword, sqlReportDaily, sqlReportHourly);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error InsertSvReport", ex);
                return false;
            }
        }

        public bool UpdateServiceRpInfo(string svCode, string dbName, string connectionString, string dbUsername, string dbPassword, string sqlReportDaily, string sqlReportHourly)
        {
            try
            {
                String sql = @"Update sv_report set DB_NAME = ?,CONNECTION_STRING = ?,DB_USERNAME = ?,DB_PASSWORD = ?,SQL_REPORT_DAILY = ?,SQL_REPORT_HOURLY = ?
                    where sv_code = ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(dbName, connectionString, dbUsername, dbPassword, sqlReportDaily, sqlReportHourly, svCode);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error UpdateServiceRpInfo", ex);
                return false;
            }
        }

        public bool UpdateServiceRpStatus(string svCode, string status)
        {
            try
            {
                String sql = @"Update sv_report set  status = ? where sv_code = ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(status, svCode);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error UpdateServiceRpStatus", ex);
                return false;
            }
        }


        public bool ClearPortDown(string portName)
        {
            try
            {
                String sql = @"UPDATE port_down SET end_time = sysdate WHERE port_name = ? AND end_time is null";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(portName);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error ClearPortDown", ex);
                return false;
            }
        }


        private void AddCmdParam(ref DbCommand myCommand, string paramName, object paramValue)
        {
            var parameter1 = myCommand.CreateParameter();
            parameter1.ParameterName = ":" + paramName;
            parameter1.Value = paramValue;
            myCommand.Parameters.Add(parameter1);
        }



        public List<Broadcast> GetBroadcastById(int broadcastId)
        {
            try
            {
                String sql =
                    @"SELECT br.*,  a.adv_name, a.id adv_id"
                        + " FROM broadcast br JOIN sv_adv a on br.sv_adv_id = a.id  "
                        + " WHERE br.id = ? "
                        + " ORDER BY schedule_time desc";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(broadcastId);
                var rs = Broadcast.Parse((OracleDataReader)stmt.ExecuteReader(connType));
                stmt.CloseConnection();
                return rs;
            }
            catch (Exception ex)
            {
                log.Error("Error GetBroadcastById", ex);
                return null;
            }
        }

        public List<Broadcast> LoadListBroadcast(DateTime? fromdate, DateTime? todate)
        {
            try
            {
                String sql =
                    @"SELECT br.*,  a.adv_name, a.id adv_id"
                        + " FROM broadcast br JOIN sv_adv a on br.sv_adv_id = a.id  "
                        + " WHERE 1=1 " +
                        " [ AND br.schedule_time >= ? ] " +
                        " [ and br.schedule_time <= ? + 1 ]"
                        + " ORDER BY schedule_time desc";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                if (fromdate != null)
                {
                    stmt.SetParameter(0, fromdate);
                }
                if (todate != null)
                {
                    stmt.SetParameter(1, todate);
                }
                var rs = Broadcast.Parse((OracleDataReader)stmt.ExecuteReader(connType));
                stmt.CloseConnection();
                return rs;
            }
            catch (Exception ex)
            {
                log.Error("Error LoadListBroadcast", ex);
                return null;
            }
        }


        public bool InsertBroadcast(Broadcast broadcast)
        {
            try
            {
                String sql = @"INSERT INTO broadcast (ID,BROADCAST_NAME,IMPORT_FILE,COUNT_SUB,STATUS,SCHEDULE_TIME,FINISH_TIME,SV_ADV_ID,SV_CODE,MSG_ADV,
                                CHANNEL_ADV,MESSAGE_TYPE,COUNT_SUB_REAL,PROGRESS_SUB) 
                    VALUES(?, ?, ?, ?, 0, ?, NULL, ?, ?, ?, ?, ?, 0, 0)";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(
                    broadcast.id,
                    broadcast.broadcast_name,
                    broadcast.import_file,
                    broadcast.count_sub,
                    DateTime.ParseExact(broadcast.schedule_time, "dd/MM/yyyy HH:mm:ss", null),
                    broadcast.sv_adv_id,
                    broadcast.sv_code,
                    broadcast.msg_adv,
                    broadcast.channel_adv,
                    broadcast.message_type);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error InsertSvReport", ex);
                return false;
            }
        }

        public bool UpdateBroadcast(Broadcast broadcast)
        {
            try
            {
                String sql = "UPDATE BROADCAST SET BROADCAST_NAME = ?, IMPORT_FILE = ?, COUNT_SUB = ?, STATUS = ?, SCHEDULE_TIME = ?,  SV_ADV_ID = ?,"
                + "SV_CODE = ?, MSG_ADV = ?, CHANNEL_ADV = ?, MESSAGE_TYPE = ? "
                + " WHERE id = ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(
                    broadcast.broadcast_name,
                    broadcast.import_file,
                    broadcast.count_sub,
                    broadcast.status,
                    DateTime.ParseExact(broadcast.schedule_time, "dd/MM/yyyy HH:mm:ss", null),
                    broadcast.sv_adv_id,
                    broadcast.sv_code,
                    broadcast.msg_adv,
                    broadcast.channel_adv,
                    broadcast.message_type,
                    broadcast.id);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error UpdateBroadcast", ex);
                return false;
            }
        }

        public bool UpdateBroadcastStatus(int id, string status)
        {
            try
            {
                String sql = "UPDATE BROADCAST SET STATUS = ?   WHERE id = ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(status, id);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error UpdateBroadcastStatus", ex);
                return false;
            }
        }

        public int ImportSubBroadcast(Broadcast broadcast, List<String> listMsisdn)
        {
            DbConnection myConnection = null;
            SqlStatement stmt = new SqlStatement(SqlConnString.GAMEPORTAL);
            String sqlCommand = "INSERT INTO MT_EXTEND_TEMP (ID,MSISDN,MESSAGE,RECEIVE_TIME,SEND_TIME,RETRY_NUM,CHANNEL,MESSAGE_TYPE,BROADCAST_ID)"
                + " VALUES(MT_EXTEND_seq.nextval, :msisdn, :message, sysdate, :send_time, 0,  :channel, :message_type, :broadcast_id)";
            int i = 0;
            try
            {
                using (myConnection = (DbConnection)stmt.GetConnection(connType))
                {
                    DbTransaction transaction = myConnection.BeginTransaction();
                    DbCommand myCommand = myConnection.CreateCommand();
                    myCommand.Transaction = transaction;
                    myCommand.CommandText = sqlCommand;
                    for (i = 0; i < listMsisdn.Count; i++)
                    {
                        myCommand.Parameters.Clear();
                        AddCmdParam(ref myCommand, "msisdn", listMsisdn[i]);
                        AddCmdParam(ref myCommand, "message", broadcast.msg_adv);
                        AddCmdParam(ref myCommand, "send_time", DateTime.ParseExact(broadcast.schedule_time, "dd/MM/yyyy HH:mm:ss", null));
                        AddCmdParam(ref myCommand, "channel", broadcast.channel_adv);
                        AddCmdParam(ref myCommand, "message_type", broadcast.message_type);
                        AddCmdParam(ref myCommand, "broadcast_id", broadcast.id);
                        myCommand.ExecuteNonQuery();
                        if (i % 1000 == 999)
                        {
                            transaction.Commit();
                            transaction = myConnection.BeginTransaction();
                            myCommand.Transaction = transaction;
                        }
                    }
                    transaction.Commit();
                    return i;
                }
            }
            catch (Exception ex)
            {
                log.Error("Error ImportSubBroadcast", ex);
                return i;
            }
            finally
            {
                try
                {
                    if (myConnection != null)
                    {
                        myConnection.Close();
                    }
                }
                catch { }
            }
        }

        public bool InsertMtExtendFromTemp(int broadcastId)
        {
            DbConnection myConnection = null;
            SqlStatement stmt = new SqlStatement(SqlConnString.GAMEPORTAL);
            String sqlCommand = "broadcast_from_temp";
            try
            {
                using (myConnection = (DbConnection)stmt.GetConnection(connType))
                {
                    DbCommand myCommand = myConnection.CreateCommand();
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.CommandText = sqlCommand;
                    myCommand.Parameters.Clear();
                    AddCmdParam(ref myCommand, "brid", broadcastId);
                    myCommand.ExecuteNonQuery();
                    return true;
                }
            }
            catch (Exception ex)
            {
                log.Error("Error InsertMtExtendFromTemp", ex);
                return false;
            }
            finally
            {
                try
                {
                    if (myConnection != null)
                    {
                        myConnection.Close();
                    }
                }
                catch { }
            }
        }


        public bool DeleteMtExtendTemp(int broadcastId)
        {
            try
            {
                String sql = "DELETE FROM mt_extend_temp WHERE broadcast_id= ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(broadcastId);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error DeleteMtExtendTemp", ex);
                return false;
            }
        }


        public bool DeleteMtExtend(int broadcastId)
        {
            try
            {
                String sql = "DELETE FROM mt_extend WHERE broadcast_id= ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(broadcastId);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error DeleteMtExtend", ex);
                return false;
            }
        }

        public int GetNextBroadcastId()
        {
            try
            {
                String sql = @"select broadcast_seq.nextval seq from dual";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                OracleDataReader reader = (OracleDataReader)stmt.ExecuteReader(connType);
                int seq = 0;
                while (reader.Read())
                {
                    seq = int.Parse(reader.GetValue(0).ToString());
                }
                stmt.CloseConnection();
                return seq;
            }
            catch (Exception ex)
            {
                log.Error("Error GetNextBroadcastId", ex);
                return 0;
            }
        }


        public List<ReportDetail> GetReportDetail(DateTime fromDate, DateTime toDate)
        {
            DbConnection myConnection = null;
            SqlStatement stmt = new SqlStatement(SqlConnString.GAMEPORTAL);
            String sqlCommand = @"SELECT a.sv_code, to_char(a.report_date,'dd/mm/yyyy') report_date, a.revenue_others, a.revenue_mocha,
           a.revenue_web, a.revenue_buy_others, a.revenue_buy_mocha,
           a.revenue_buy_web, a.count_reg_others, a.count_reg_mocha,
           a.count_reg_web FROM report_detail a WHERE REPORT_DATE >= :fromDate and REPORT_DATE <= :toDate ORDER BY report_date desc";
            //                @"select
            //'LUCKY_DRAW' SV_CODE, 
            //  to_char(REPORT_DATE,'dd-mm-yyyy') REPORT_DATE,
            //nvl(revenue_others,0) REVENUE_OTHERS,
            //nvl(revenue_mocha,0) REVENUE_MOCHA,
            //nvl(revenue_web,0) revenue_web,
            //nvl(revenue_buy_others,0) REVENUE_BUY_OTHERS,
            //nvl(revenue_buy_mocha,0) REVENUE_BUY_MOCHA,
            //nvl(revenue_buy_web,0) revenue_buy_web,
            //nvl(count_reg_others,0) count_reg_others  ,
            //nvl(count_reg_mocha,0) count_reg_mocha   ,
            //nvl(count_reg_web,0) count_reg_web  
            //from 
            //(
            //select a.report_date, a.revenue_others,  revenue_mocha, revenue_web, revenue_buy_others, revenue_buy_mocha, revenue_buy_web, count_reg_others, count_reg_mocha, count_reg_web  from  
            //( select nvl(sum(fee),0) revenue_others, trunc(charge_time) REPORT_DATE from charge_log 
            //    where  charge_time >= :from_date and charge_time < :to_date + 1 and fee > 0 and product_name = 'Lucky_Draw' and channel not in ('1','4')
            //    group by trunc(charge_time) ) a
            // left join
            //(select nvl(sum(fee),0) revenue_mocha, trunc(charge_time) REPORT_DATE from charge_log
            //     where charge_time >= :from_date and charge_time < :to_date + 1 and fee > 0 and product_name = 'Lucky_Draw'  and channel = '1'
            //      group by trunc(charge_time) ) a2 on a.report_date = a2.report_date
            //left join
            //(select nvl(sum(fee),0) revenue_web, trunc(charge_time) REPORT_DATE from charge_log
            //     where charge_time >= :from_date and charge_time < :to_date + 1 and fee > 0 and product_name = 'Lucky_Draw'  and channel = '4'
            //      group by trunc(charge_time) ) a3 on a.report_date = a3.report_date
            //left join
            //(select nvl(sum(fee),0) revenue_buy_others, trunc(charge_time) REPORT_DATE from charge_log
            //     where charge_time >= :from_date and charge_time < :to_date + 1 and fee > 0 and product_name = 'Lucky_Draw'  and description in ('Redeem')and channel not in ('1','4')
            //      group by trunc(charge_time) ) b on a.report_date = b.report_date
            //left join
            //(select nvl(sum(fee),0) revenue_buy_mocha, trunc(charge_time) REPORT_DATE from charge_log
            //     where charge_time >= :from_date and charge_time < :to_date+ 1 and fee > 0 and product_name = 'Lucky_Draw'  and description in ('Redeem')and channel = '1'
            //      group by trunc(charge_time) ) b2 on a.report_date = b2.report_date
            //left join
            //(select nvl(sum(fee),0) revenue_buy_web, trunc(charge_time) REPORT_DATE from charge_log
            //     where charge_time >= :from_date and charge_time < :to_date+ 1 and fee > 0 and product_name = 'Lucky_Draw'  and description in ('Redeem')and channel = '4'
            //      group by trunc(charge_time) ) b3 on a.report_date = b3.report_date
            //left join
            //(select count(*) count_reg_others, trunc(register_time) REPORT_DATE from reg_info 
            //    where register_time >= :from_date and register_time < :to_date+ 1 and channel not in ('1','4')
            //    group by trunc(register_time) ) c on c.report_date = a.report_date
            //left join
            //(select count(*) count_reg_mocha, trunc(register_time) REPORT_DATE from reg_info 
            //    where register_time >= :from_date and register_time < :to_date+ 1 and channel = '1'
            //    group by trunc(register_time) ) c2 on c2.report_date = a.report_date
            //left join
            //(select count(*) count_reg_web, trunc(register_time) REPORT_DATE from reg_info 
            //    where register_time >= :from_date and register_time < :to_date+ 1 and channel = '4'
            //    group by trunc(register_time) ) c3 on c3.report_date = a.report_date
            //   ) a order by a.report_date desc ";
            try
            {
                using (myConnection = (DbConnection)stmt.GetConnection(connType))
                {
                    DbCommand myCommand = myConnection.CreateCommand();
                    myCommand.CommandText = sqlCommand;
                    myCommand.Parameters.Clear();

                    // from date 
                    AddCmdParam(ref myCommand, "fromDate", fromDate);

                    // todate
                    AddCmdParam(ref myCommand, "toDate", toDate);

                    DbDataReader reader = myCommand.ExecuteReader();

                    return ReportDetail.Parse((OracleDataReader)reader);
                }
            }
            catch (Exception ex)
            {
                log.Error("Error GetReportDetail", ex);
                return null;
            }
            finally
            {
                try
                {
                    if (myConnection != null)
                    {
                        myConnection.Close();
                    }
                }
                catch { }
            }
        }

        public List<WebserviceObj> GetWebserviceById(int wsId)
        {
            try
            {
                String sql =
                    @"SELECT * from webservice where ws_id = ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(wsId);
                var rs = WebserviceObj.Parse((OracleDataReader)stmt.ExecuteReader(connType));
                stmt.CloseConnection();
                return rs;
            }
            catch (Exception ex)
            {
                log.Error("Error GetWebserviceById", ex);
                return null;
            }
        }


        public List<WebserviceObj> LoadListWebserivce(String wsName)
        {
            try
            {
                String sql =
                    @"SELECT * from webservice WHERE ws_name = ?"
                        + " ORDER BY ws_code";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(wsName);
                var rs = WebserviceObj.Parse((OracleDataReader)stmt.ExecuteReader(connType));
                stmt.CloseConnection();
                return rs;
            }
            catch (Exception ex)
            {
                log.Error("Error LoadListWebserivce", ex);
                return null;
            }
        }

        public bool InsertWebservice(WebserviceObj wsObj)
        {
            try
            {
                String sql = @"INSERT INTO webservice (WS_ID,WS_NAME,WS_CODE,WSDL,MSG_TEMPLATE,STATUS) 
                        VALUES(webservice_seq.nextval, ?, ?, ?, ?, 1)";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(
                    wsObj.ws_name,
                    wsObj.ws_code,
                    wsObj.wsdl,
                    wsObj.msg_template);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error InsertWebservice", ex);
                return false;
            }
        }

        public bool UpdateWebservice(WebserviceObj wsObj)
        {
            try
            {
                String sql = "UPDATE webservice SET WS_NAME = ?, WS_CODE = ?, WSDL = ?, MSG_TEMPLATE = ? "
                + " WHERE WS_ID = ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(
                    wsObj.ws_name,
                    wsObj.ws_code,
                    wsObj.wsdl,
                    wsObj.msg_template,
                    wsObj.ws_id);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error UpdateWebservice", ex);
                return false;
            }
        }

        public bool UpdateWebserviceStatus(int id, string status)
        {
            try
            {
                String sql = "UPDATE webservice SET STATUS = ?   WHERE ws_id = ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(status, id);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error UpdateWebserviceStatus", ex);
                return false;
            }
        }

        // SV ADV
        public bool InsertSvAdv(string svCode, string msgAdv, string advName, string channelAdv, string messageType, string activeHour, string maxNumberMsg, string fromMoney)
        {
            try
            {
                String sql = @"INSERT INTO sv_adv (ID, SV_CODE,ADV_NAME,MSG_ADV,MSG_ADV_PUSH,CHANNEL_ADV,MESSAGE_TYPE,ACTIVE_HOUR,max_number_msg,FROM_MONEY,STATUS ) 
                    VALUES(sv_adv_seq.nextval, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(svCode, advName, msgAdv, msgAdv, channelAdv, messageType, activeHour, maxNumberMsg, fromMoney);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error InsertSvReport", ex);
                return false;
            }
        }

        //public bool InsertSvAdvHistory(string svCode, string messageType)
        //{
        //    try
        //    {
        //        String sql = @"INSERT INTO sv_adv_history (ID,SV_CODE,START_TIME,END_TIME,COUNT_MSG,MESSAGE_TYPE) 
        //                VALUES(sv_adv_history_seq.nextval, ?, sysdate, NULL, 0, ?)";
        //        SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
        //        stmt.SetParameters(svCode, messageType);
        //        stmt.ExecuteNonQuery(connType);
        //        stmt.CloseConnection();
        //        return true;
        //    }
        //    catch (Exception ex)
        //    {
        //        log.Error("Error InsertSvAdvHistory", ex);
        //        return false;
        //    }
        //}


        public bool UpdateServiceAdvInfo(string svCode, string msgAdv, string advName, string channelAdv, string messageType, string activeHour, string maxNumberMsg, string fromMoney, string advId)
        {
            try
            {
                String sql = @"Update sv_adv set sv_code = ?, msg_adv = ?,adv_name = ?,channel_adv = ?,message_type = ?,active_hour = ? ,max_number_msg=?,from_money = ? 
                    where id = ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(svCode, msgAdv, advName, channelAdv, messageType, activeHour, maxNumberMsg, fromMoney, advId);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error UpdateServiceAdvInfo", ex);
                return false;
            }
        }

        public bool UpdateServiceAdvStatus(string advId, string status)
        {
            try
            {
                String sql = @"Update sv_adv set  status = ? where id = ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(status, advId);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error UpdateServiceAdvStatus", ex);
                return false;
            }
        }

        //public bool UpdateServiceAdvEndTime(string svCode)
        //{
        //    try
        //    {
        //        String sql = @"Update sv_adv_history set end_time = sysdate WHERE sv_code = ? and end_time is null";
        //        SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
        //        stmt.SetParameters(svCode);
        //        stmt.ExecuteNonQuery(connType);
        //        stmt.CloseConnection();
        //        return true;
        //    }
        //    catch (Exception ex)
        //    {
        //        log.Error("Error UpdateServiceAdvStatus", ex);
        //        return false;
        //    }
        //}



        // Service Adv schedule

        public SvAdvSchedule GetSvAdvScheduleById(string scheduleId, int codeType)
        {
            try
            {
                String sql;
                if (codeType == LuckyCardUtils.CodeType.LUCKY_CARD)
                {
                    sql = @"select s.*, a.adv_name, d.sv_adv_id, d.sv_code, d.msg_adv, a.msg_adv_push, d.channel_adv, d.message_type, d.from_money,
                             d.active_hour, d.max_number_msg, d.id schedule_detail_id, nvl(d.percent,0) percent 
                        from sv_adv_schedule s  
                        left join sv_adv a on a.status = 1
                        left join sv_adv_schedule_detail d on s.id = d.schedule_id and d.sv_adv_id = a.id and d.status = 1
                        where s.id = ?";
                }
                else
                {
                    sql = @"select s.*, a.adv_name, d.sv_adv_id, d.sv_code, d.msg_adv, d.msg_adv_push, d.channel_adv, d.message_type, d.from_money,
                             d.active_hour, d.max_number_msg, d.id schedule_detail_id, nvl(d.percent,0) percent 
                        from sv_adv_schedule_call s  
                        left join sv_adv a on a.status = 1
                        left join sv_adv_schedule_detail_call d on s.id = d.schedule_id and d.sv_adv_id = a.id and d.status = 1
                        where s.id = ?";
                }

                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(scheduleId);
                var rs = SvAdvSchedule.Parse((OracleDataReader)stmt.ExecuteReader(connType));
                stmt.CloseConnection();
                if (rs != null && rs.Count > 0)
                {
                    return rs[0];
                }
                return null;
            }
            catch (Exception ex)
            {
                log.Error("Error GetSvAdvScheduleById", ex);
                return null;
            }
        }

        public bool UpdateSvAdvScheduleInfo(SvAdvSchedule svAdvSchedule, int codeType)
        {
            DbConnection myConnection = null;
            SqlStatement stmt = new SqlStatement(SqlConnString.GAMEPORTAL);
            String sqlUpdateSchedule = codeType == LuckyCardUtils.CodeType.LUCKY_CARD
                ? "UPDATE sv_adv_schedule SET from_date = :from_date, to_date = :to_date, schedule_name = :schedule_name, max_total_msg = :max_total_msg WHERE id = :id"
                : "UPDATE sv_adv_schedule_call SET from_date = :from_date, to_date = :to_date, schedule_name = :schedule_name, max_total_msg = :max_total_msg WHERE id = :id";
            String sqlDeleteAdv = codeType == LuckyCardUtils.CodeType.LUCKY_CARD
                ? "UPDATE  sv_adv_schedule_detail SET status = 0 WHERE schedule_id = :schedule_id"
                : "UPDATE  sv_adv_schedule_detail_call SET status = 0 WHERE schedule_id = :schedule_id";
            String sqlInsertAdv = codeType == LuckyCardUtils.CodeType.LUCKY_CARD
                ? @"INSERT INTO sv_adv_schedule_detail
                    (ID,SCHEDULE_ID,SV_ADV_ID,PERCENT,SV_CODE,MSG_ADV,CHANNEL_ADV,MESSAGE_TYPE,FROM_MONEY,ACTIVE_HOUR,MAX_NUMBER_MSG)
                    SELECT sv_adv_schedule_detail_seq.nextval, 
                    :schedule_id, id, :percent, sv_code,  msg_adv, channel_adv, message_type, from_money, active_hour,MAX_NUMBER_MSG FROM sv_adv WHERE id = :sv_adv_id"
                : @"INSERT INTO sv_adv_schedule_detail_call
                    (ID,SCHEDULE_ID,SV_ADV_ID,PERCENT,SV_CODE,MSG_ADV,CHANNEL_ADV,MESSAGE_TYPE,FROM_MONEY,ACTIVE_HOUR,MAX_NUMBER_MSG)
                    SELECT sv_adv_schedule_detail_seq.nextval, 
                    :schedule_id, id, :percent, sv_code,  msg_adv, channel_adv, message_type, from_money, active_hour,MAX_NUMBER_MSG FROM sv_adv WHERE id = :sv_adv_id";

            try
            {
                using (myConnection = (DbConnection)stmt.GetConnection(connType))
                {
                    DbTransaction transaction = myConnection.BeginTransaction();
                    DbCommand myCommand = myConnection.CreateCommand();
                    myCommand.Transaction = transaction;

                    // update schedule
                    myCommand.CommandText = sqlUpdateSchedule;
                    myCommand.Parameters.Clear();
                    AddCmdParam(ref myCommand, "from_date", svAdvSchedule.from_date);
                    AddCmdParam(ref myCommand, "to_date", svAdvSchedule.to_date);
                    AddCmdParam(ref myCommand, "schedule_name", svAdvSchedule.schedule_name);
                    AddCmdParam(ref myCommand, "max_total_msg", svAdvSchedule.max_total_msg);
                    AddCmdParam(ref myCommand, "id", svAdvSchedule.id);
                    myCommand.ExecuteNonQuery();

                    // delete old adv
                    myCommand.CommandText = sqlDeleteAdv;
                    myCommand.Parameters.Clear();
                    AddCmdParam(ref myCommand, "schedule_id", svAdvSchedule.id);
                    myCommand.ExecuteNonQuery();

                    // insert new adv
                    myCommand.CommandText = sqlInsertAdv;
                    for (int i = 0; i < svAdvSchedule.list_adv.Count; i++)
                    {
                        SvAdv adv = svAdvSchedule.list_adv[i];
                        myCommand.Parameters.Clear();
                        AddCmdParam(ref myCommand, "schedule_id", svAdvSchedule.id);
                        AddCmdParam(ref myCommand, "percent", adv.percent);
                        AddCmdParam(ref myCommand, "sv_adv_id", adv.id);
                        //AddCmdParam(ref myCommand, "sv_code", adv.sv_code);
                        //AddCmdParam(ref myCommand, "msg_adv", adv.msg_adv);
                        //AddCmdParam(ref myCommand, "channel_adv", adv.channel_adv);
                        //AddCmdParam(ref myCommand, "message_type", adv.message_type);
                        //AddCmdParam(ref myCommand, "from_money", adv.from_money);
                        myCommand.ExecuteNonQuery();
                    }
                    transaction.Commit();
                    return true;
                }
            }
            catch (Exception ex)
            {
                log.Error("Error UpdateSvAdvScheduleInfo", ex);
                return false;
            }
            finally
            {
                try
                {
                    if (myConnection != null)
                    {
                        myConnection.Close();
                    }
                }
                catch { }
            }
        }

        public bool UpdateSvAdvScheduleStatus(string scheduleId, string status, int codeType)
        {
            try
            {
                String sql = codeType == LuckyCardUtils.CodeType.LUCKY_CARD
                    ? @"Update sv_adv_schedule set  status = ? where id = ?"
                    : @"Update sv_adv_schedule_call set  status = ? where id = ?";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                stmt.SetParameters(status, scheduleId);
                stmt.ExecuteNonQuery(connType);
                stmt.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                log.Error("Error UpdateSvAdvScheduleStatus", ex);
                return false;
            }
        }


        public List<SvAdv> LoadListServicesAdv()
        {
            try
            {
                String sql = @"select   sv.sv_code, sv.sv_name, a.msg_adv, a.adv_name, a.channel_adv, a.message_type, 
                        a.from_money, a.active_hour, a.max_number_msg, a.status, a.id   
                        from sv join sv_adv a on sv.sv_code = a.sv_code 
                            where sv.status = 1 
                            and a.status = 1 ORDER BY sv.sv_code ";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);

                var rs = SvAdv.Parse((OracleDataReader)stmt.ExecuteReader(connType));
                stmt.CloseConnection();
                return rs;
            }
            catch (Exception ex)
            {
                log.Error("Error LoadListServicesAdv", ex);
                return null;
            }
        }


        public List<SvAdvSchedule> LoadListSvAdvSchedule(int month, int year, int codeType)
        {
            try
            {
                String sql = codeType == LuckyCardUtils.CodeType.LUCKY_CARD
                    ? @"select s.*, d.sv_adv_id, d.sv_code, d.msg_adv, d.channel_adv, d.message_type, d.from_money,
                             d.id schedule_detail_id, nvl(d.percent,0) percent , sa.adv_name
                        from sv_adv_schedule s  
                        left join sv_adv_schedule_detail d on s.id = d.schedule_id and d.status = 1
                        left join sv_adv sa on sa.id = d.sv_adv_id 
                        where 1=1
                         and s.from_date < ? +1
                         and s.to_date >= ? 
                        ORDER BY s.from_date desc"
                    : @"select s.*, d.sv_adv_id, d.sv_code, d.msg_adv, d.channel_adv, d.message_type, d.from_money,
                             d.id schedule_detail_id, nvl(d.percent,0) percent , sa.adv_name
                        from sv_adv_schedule_call s  
                        left join sv_adv_schedule_detail_call d on s.id = d.schedule_id and d.status = 1
                        left join sv_adv_call sa on sa.id = d.sv_adv_id 
                        where 1=1
                         and s.from_date < ? +1
                         and s.to_date >= ? 
                        ORDER BY s.from_date desc";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                DateTime max = new DateTime(year, month, DateTime.DaysInMonth(year, month));
                DateTime min = new DateTime(year, month, 1);
                stmt.SetParameters(max, min);
                var rs = SvAdvSchedule.Parse((OracleDataReader)stmt.ExecuteReader(connType));
                stmt.CloseConnection();
                return rs;
            }
            catch (Exception ex)
            {
                log.Error("Error LoadListSvAdvSchedule", ex);
                return null;
            }
        }


        public List<SvAdv> LoadListSvAdvCounter(int month, int year, int codeType)
        {
            try
            {
                String sql = codeType == LuckyCardUtils.CodeType.LUCKY_CARD
                    ? @"select nvl(c.schedule_detail_id,-1) schedule_detail_id,
                          nvl(c.counter,0) counter, nvl(c.create_time, s.from_date) start_time, nvl(c.report_date, trunc(s.from_date)) report_date,
                          nvl(s.to_date, trunc(c.report_date) + 1 - 1/86400) end_time, d.*, a.adv_name
                    from  sv_adv_schedule_detail d
                    join sv_adv a on a.id = d.sv_adv_id
                    full outer join schedule_counter c on d.id = c.schedule_detail_id 
                    left join sv_adv_schedule s  on s.id = d.schedule_id 
                    where 1=1 
                     and nvl(c.create_time, s.from_date) < ? + 1
                     and nvl(c.create_time, s.to_date) >= ?
                    ORDER BY nvl(c.report_date, trunc(s.from_date)) desc, d.sv_code"
                    : @"select nvl(c.schedule_detail_id,-1) schedule_detail_id,
                          nvl(c.counter,0) counter, nvl(c.create_time, s.from_date) start_time, nvl(c.report_date, trunc(s.from_date)) report_date,
                          nvl(s.to_date, trunc(c.report_date) + 1 - 1/86400) end_time, d.*, a.adv_name
                    from  sv_adv_schedule_detail_call d
                    join sv_adv a on a.id = d.sv_adv_id
                    full outer join schedule_counter_call c on d.id = c.schedule_detail_id 
                    left join sv_adv_schedule_call s  on s.id = d.schedule_id 
                    where 1=1 
                     and nvl(c.create_time, s.from_date) < ? + 1
                     and nvl(c.create_time, s.to_date) >= ?
                    ORDER BY  nvl(c.create_time, s.from_date) desc, d.sv_code";
                SqlStatement stmt = SqlStatement.FromString(sql, SqlConnString.GAMEPORTAL);
                DateTime max = new DateTime(year, month, DateTime.DaysInMonth(year, month));
                DateTime min = new DateTime(year, month, 1);
                stmt.SetParameters(max, min);
                var rs = SvAdv.Parse((OracleDataReader)stmt.ExecuteReader(connType));
                stmt.CloseConnection();
                return rs;
            }
            catch (Exception ex)
            {
                log.Error("Error LoadListSvAdvCounter", ex);
                return null;
            }
        }


        public bool InsertSvAdvSchedule(SvAdvSchedule svAdvSchedule, int codeType)
        {
            DbConnection myConnection = null;
            SqlStatement stmt = new SqlStatement(SqlConnString.GAMEPORTAL);
            String sqlGetScheduleId = codeType == LuckyCardUtils.CodeType.LUCKY_CARD
                ? "SELECT sv_adv_schedule_seq.nextval seq FROM DUAL"
                : "SELECT sv_adv_schedule_call_seq.nextval seq FROM DUAL";
            String sqlUpdateSchedule = codeType == LuckyCardUtils.CodeType.LUCKY_CARD
                ? @"INSERT INTO sv_adv_schedule (ID,FROM_DATE,TO_DATE,SCHEDULE_NAME,max_total_msg,STATUS) 
                    VALUES(:schedule_id, :from_date, :to_date, :schedule_name, :max_total_msg, 1)"
                : @"INSERT INTO sv_adv_schedule_call (ID,FROM_DATE,TO_DATE,SCHEDULE_NAME,max_total_msg,STATUS) 
                    VALUES(:schedule_id, :from_date, :to_date, :schedule_name, :max_total_msg, 1)";
            String sqlInsertAdv = codeType == LuckyCardUtils.CodeType.LUCKY_CARD
               ? @"INSERT INTO sv_adv_schedule_detail
                    (ID,SCHEDULE_ID,SV_ADV_ID,PERCENT,SV_CODE,MSG_ADV,CHANNEL_ADV,MESSAGE_TYPE,FROM_MONEY,ACTIVE_HOUR,MAX_NUMBER_MSG)
                    SELECT sv_adv_schedule_detail_seq.nextval, 
                    :schedule_id, id, :percent, sv_code,  msg_adv, channel_adv, message_type, from_money, active_hour,MAX_NUMBER_MSG FROM sv_adv WHERE id = :sv_adv_id"
               : @"INSERT INTO sv_adv_schedule_detail_call
                    (ID,SCHEDULE_ID,SV_ADV_ID,PERCENT,SV_CODE,MSG_ADV,CHANNEL_ADV,MESSAGE_TYPE,FROM_MONEY,ACTIVE_HOUR,MAX_NUMBER_MSG)
                    SELECT sv_adv_schedule_detail_seq.nextval, 
                    :schedule_id, id, :percent, sv_code,  msg_adv, channel_adv, message_type, from_money, active_hour,MAX_NUMBER_MSG FROM sv_adv WHERE id = :sv_adv_id";
            // VALUES(sv_adv_schedule_detail_seq.nextval, :schedule_id, :sv_adv_id, :percent, :sv_code, :msg_adv, :channel_adv, :message_type, :from_money)";

            try
            {
                using (myConnection = (DbConnection)stmt.GetConnection(connType))
                {
                    DbTransaction transaction = myConnection.BeginTransaction();
                    DbCommand myCommand = myConnection.CreateCommand();
                    myCommand.Transaction = transaction;

                    // get sequence
                    myCommand.CommandText = sqlGetScheduleId;
                    myCommand.Parameters.Clear();
                    DbDataReader reader = myCommand.ExecuteReader();
                    if (reader.Read())
                    {
                        svAdvSchedule.id = reader.GetInt32(0);
                    }

                    // update schedule
                    myCommand.CommandText = sqlUpdateSchedule;
                    myCommand.Parameters.Clear();
                    AddCmdParam(ref myCommand, "schedule_id", svAdvSchedule.id);
                    AddCmdParam(ref myCommand, "from_date", svAdvSchedule.from_date);
                    AddCmdParam(ref myCommand, "to_date", svAdvSchedule.to_date);
                    AddCmdParam(ref myCommand, "schedule_name", svAdvSchedule.schedule_name);
                    AddCmdParam(ref myCommand, "max_total_msg", svAdvSchedule.max_total_msg);
                    myCommand.ExecuteNonQuery();

                    // insert new adv
                    myCommand.CommandText = sqlInsertAdv;
                    for (int i = 0; i < svAdvSchedule.list_adv.Count; i++)
                    {
                        SvAdv adv = svAdvSchedule.list_adv[i];
                        myCommand.Parameters.Clear();
                        AddCmdParam(ref myCommand, "schedule_id", svAdvSchedule.id);
                        AddCmdParam(ref myCommand, "percent", adv.percent);
                        AddCmdParam(ref myCommand, "sv_adv_id", adv.id);
                        //AddCmdParam(ref myCommand, "sv_code", adv.sv_code);
                        //AddCmdParam(ref myCommand, "msg_adv", adv.msg_adv);
                        //AddCmdParam(ref myCommand, "channel_adv", adv.channel_adv);
                        //AddCmdParam(ref myCommand, "message_type", adv.message_type);
                        //AddCmdParam(ref myCommand, "from_money", adv.from_money);
                        myCommand.ExecuteNonQuery();
                    }
                    transaction.Commit();
                    return true;
                }
            }
            catch (Exception ex)
            {
                log.Error("Error InsertSvAdvSchedule", ex);
                return false;
            }
            finally
            {
                try
                {
                    if (myConnection != null)
                    {
                        myConnection.Close();
                    }
                }
                catch { }
            }
        }


        public ErrResponse ProcessPrizeTop(long id)
        {
            try
            {
                using (OracleConnection objConn = new OracleConnection(SqlCommon.GetConnectionString(SqlConnString.GAMEPORTAL)))
                {
                    OracleCommand objCmd = new OracleCommand();
                    objCmd.Connection = objConn;
                    objCmd.CommandText = "process_prize_top";
                    objCmd.CommandType = CommandType.StoredProcedure;
                    objCmd.Parameters.Add("id_", OracleDbType.Int32).Value = id;
                    objCmd.Parameters.Add("cur_employees", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
                    try
                    {
                        objConn.Open();
                        OracleDataReader objReader = objCmd.ExecuteReader();
                        ErrResponse res = new ErrResponse();
                        while (objReader.Read())
                        {
                            for (int i = 0; i < objReader.FieldCount; i++)
                            {
                                if (objReader.GetName(i).ToUpper() == "ERROR")
                                {
                                    res.error = objReader[i].ToString();
                                }
                                else if (objReader.GetName(i).ToUpper() == "MESSAGE")
                                {
                                    res.message = objReader[i].ToString();
                                }
                            }
                            return res;
                        }
                        res.error = "-1";
                        res.message = "Not found";
                        return res;
                    }
                    catch (Exception ex)
                    {
                        log.Error("Exception ProcessPrizeTop: ", ex);
                        return new ErrResponse("-1", "Error database");
                    }
                    finally
                    {
                        try
                        {
                            objConn.Close();
                        }
                        catch { }
                    }
                }
            }
            catch (Exception ex)
            {
                log.Error("Error ProcessPrizeTop", ex);
                return new ErrResponse("-1", "Error database");
            }
        }

    }
}