using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace DHH_Bill_TBLockLog
{
    class Program
    {
        
        static void Main(string[] args)
        {
            #region 测试数据库  
            Console.WriteLine("获取testid031用户在2013-4-11 15:53:40到2013-04-12 00:00:00的消费清单");
            Console.WriteLine();
            string conString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.20)(PORT=1521))" +
              "(CONNECT_DATA=(SID=44410g242)));User Id=44bikll;Password=487gf,.q;"; //这个也可以放到Web.Config中。  
            
            //实例化OracleConnection对象 
            try
            {
                using (OracleConnection conn = new OracleConnection(conString))
                {
                    conn.Open();
                    string sql = "select TBACCOUNT.username,TBLOCKLOG.accountid,TBLOCKLOG.amount,TBLOCKLOG.locktime  FROM TBLOCKLOG,TBACCOUNT WHERE TBLOCKLOG.accountid=TBACCOUNT.accountid  ";
                    sql += " and TBLOCKLOG.serviceid=1 and TBACCOUNT.username=‘testid031‘ and TBLOCKLOG.status=1 and TBLOCKLOG.locktime >= to_date(‘2013-4-11 13:53:40‘,‘yyyy-mm-dd hh24:mi:ss‘) and TBLOCKLOG.locktime <= to_date(‘2013-04-12 00:00:00‘,‘yyyy-mm-dd hh24:mi:ss‘) order by TBLOCKLOG.locktime";
                    
                    using (OracleCommand comm = new OracleCommand(sql, conn))
                    {
                        
                        using (OracleDataReader rdr = comm.ExecuteReader())
                        {
                           
                            while (rdr.Read())
                            {
                                Console.WriteLine("UserName：" + rdr.GetString(0) + ",UserId：" + rdr.GetInt32(1) + ",点数：" + rdr.GetInt32(2) + "," + rdr.GetDateTime(3));

                            }
                           
                        }

                    }

                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            #endregion 

            Console.WriteLine(); Console.WriteLine();
            Console.WriteLine("计算2013-4-11 15:53:40到2013-04-12 00:00:00时间的testid031用户消费的amount的总和");            
            Console.WriteLine(GetBillByDateAndGameId(conString, "testid031", 1, Convert.ToDateTime("2013-4-11 13:53:40"), Convert.ToDateTime("2013-04-12 00:00:00")));
           
        }



        public static int GetBillByDateAndGameId(string conString,string accountId, int gameId, DateTime beginTime, DateTime endTime)
        {
            try
            {
                using (OracleConnection conn = new OracleConnection(conString))
                {
                    accountId = accountId.ToLower();//全部转换为小写
                    conn.Open(); string sql = "select sum(TBLOCKLOG.amount) sumamount FROM TBLOCKLOG,TBACCOUNT WHERE TBLOCKLOG.accountid=TBACCOUNT.accountid  ";
                    sql += " and TBLOCKLOG.serviceid=" + gameId + " and TBACCOUNT.username=‘" + accountId + "‘ and TBLOCKLOG.status=1 and TBLOCKLOG.locktime >= to_date(‘" + beginTime + "‘,‘yyyy-mm-dd hh24:mi:ss‘) and TBLOCKLOG.locktime <= to_date(‘" + endTime + "‘,‘yyyy-mm-dd hh24:mi:ss‘) order by TBLOCKLOG.locktime";
                 
                    using (OracleCommand comm = new OracleCommand(sql, conn))
                    {

                        using (OracleDataReader rdr = comm.ExecuteReader())//创建一个OracleDateReader对象
                        {
                            int sum = 0;
                            while (rdr.Read())//读取数据，如果odr.Read()返回为false的话，就说明到记录集的尾部了
                            {
                                if (rdr.GetOracleValue(0).ToString() == "null")//防止为空 
                                {
                                    sum = 0;
                                }
                                else
                                {
                                    sum = int.Parse(rdr.GetOracleValue(0).ToString());
                                    //sum = int.Parse(rdr[0].ToString());
                                }
                               
                            }
                            return sum;
                        }

                    }

                }
            }
            catch (Exception ex)
            {
                return -1;
            }
        }
    }
   
}