Middleware_5.1/FirstStep/DbSetter.cpp

#include "StdAfx.h"
#include "DbSetter.h"
#include "resource.h"


CDbSetter::CDbSetter(void)
{
}


CDbSetter::~CDbSetter(void)
{
}

/*****************************************************************
**【函数名称】	__createConfigTable4SqlServer
**【函数功能】	为SqlServer数据库创建配置表
**【参数】		
**【返回值】	
****************************************************************/
bool CDbSetter::__createConfigTable4SqlServer( void )
{
	IOtlConnection* pOtlCon = IOtlConnection::getInstance();
	CString strSQL;

	// conf_system 配置表

	pOtlCon->ExecCommand(_T("truncate table conf_system")); 

 	//pOtlCon->DropTableCommand(_T("DROP TABLE conf_system"));
// 	strSQL = "CREATE TABLE [dbo].[conf_system](" \
// 		"[id] [int] IDENTITY(1,1) NOT NULL," \
// 		"[name] [varchar](50) NULL," \
// 		"[value] [varchar](300) NULL," \
// 		"CONSTRAINT [PK_conf_system] PRIMARY KEY CLUSTERED " \
// 		"(" \
// 		"[id] ASC" \
// 		")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]" \
// 		") ON [PRIMARY]";
// 
// 
// 	if(!pOtlCon->ExecCommand(strSQL))
// 		return false;

	// conf_ivr_flow IVR流程表
		pOtlCon->ExecCommand(_T("truncate table conf_ivr_flow")); 
// 	pOtlCon->DropTableCommand(_T("DROP TABLE conf_ivr_flow"));
// 	strSQL = "CREATE TABLE [dbo].[conf_ivr_flow](" \
// 		"[FlowID] [int] IDENTITY(0,1) NOT NULL," \
// 		"[FilePath] [varchar](512) NOT NULL," \
// 		"CONSTRAINT [PK_conf_ivr_flow] PRIMARY KEY CLUSTERED " \
// 		"(" \
// 		"[FlowID] ASC" \
// 		")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]" \
// 		") ON [PRIMARY]";
// 	if(!pOtlCon->ExecCommand(strSQL))
// 		return false;

	// conf_dev 设备配置表

    pOtlCon->ExecCommand(_T("truncate table conf_dev")); 
// 	pOtlCon->DropTableCommand(_T("DROP TABLE conf_dev"));
// 	strSQL = "CREATE TABLE [dbo].[conf_dev](" \
// 		"[id] [int] IDENTITY(1,1) NOT NULL," \
// 		"[name] [varchar](50) NULL," \
// 		"[value] [varchar](300) NULL," \
// 		"CONSTRAINT [PK_conf_dev] PRIMARY KEY CLUSTERED " \
// 		"(" \
// 		"[id] ASC" \
// 		")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]" \
// 		") ON [PRIMARY]";
// 	if(!pOtlCon->ExecCommand(strSQL))
// 		return false;

	// conf_line 线路配置表
	 pOtlCon->ExecCommand(_T("truncate table conf_line")); 
// 	pOtlCon->DropTableCommand(_T("DROP TABLE conf_line"));
// 	strSQL = "CREATE TABLE [dbo].[conf_line](" \
// 		"[id] [int] NOT NULL," \
// 		"[type] [int] NOT NULL," \
// 		"[value] [varchar](100) NULL," \
// 		"CONSTRAINT [PK_conf_line] PRIMARY KEY CLUSTERED " \
// 		"(" \
// 		"[id] ASC" \
// 		")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]" \
// 		") ON [PRIMARY]";
// 	if(!pOtlCon->ExecCommand(strSQL))
// 		return false;

	// conf_vs VS配置表
	  pOtlCon->ExecCommand(_T("truncate table conf_vs")); 
// 	pOtlCon->DropTableCommand(_T("DROP TABLE conf_vs"));
// 	strSQL = "CREATE TABLE [dbo].[conf_vs](" \
// 		"[id] [int] IDENTITY(1,1) NOT NULL," \
// 		"[name] [varchar](50) NULL," \
// 		"[value] [varchar](300) NULL," \
// 		"[value2] [varchar](300) NULL," \
// 		"CONSTRAINT [PK_conf_vs] PRIMARY KEY CLUSTERED " \
// 		"(" \
// 		"[id] ASC" \
// 		")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]" \
// 		") ON [PRIMARY]";
// 	if(!pOtlCon->ExecCommand(strSQL))
// 		return false;

	// SIP账号表
	pOtlCon->ExecCommand(_T("truncate table conf_sip_account")); 
// 	pOtlCon->DropTableCommand(_T("DROP TABLE conf_sip_account"));
// 	strSQL = "CREATE TABLE [dbo].[conf_sip_account](" \
// 		"[id] [int] NOT NULL," \
// 		"[account] [varchar](50) NOT NULL," \
// 		"[auth_account] [varchar](50) NOT NULL," \
// 		"[password] [varchar](30) NULL," \
// 		"[proxy_addr] [varchar](50) NOT NULL," \
// 		"[proxy_port] [int] NOT NULL," \
// 		"[reg] [varchar](10) NOT NULL," \
// 		"[reg_interval] [int] NOT NULL," \
// 		"CONSTRAINT [PK_conf_sip_account] PRIMARY KEY CLUSTERED " \
// 		"(" \
// 		"[id] ASC" \
// 		")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]" \
// 		") ON [PRIMARY]";
// 	if(!pOtlCon->ExecCommand(strSQL))
// 		return false;

	// 中继表
		pOtlCon->ExecCommand(_T("truncate table conf_trunk_item")); 
// 	pOtlCon->DropTableCommand(_T("DROP TABLE conf_trunk_item"));
// 	strSQL = "CREATE TABLE [dbo].[conf_trunk_item](" \
// 		"[id] [int] NOT NULL," \
// 		"[node] [int] NOT NULL," \
// 		"[board] [int] NOT NULL," \
// 		"[span] [int] NOT NULL," \
// 		"[sip_user] [int] NOT NULL," \
// 		"CONSTRAINT [PK_conf_trunk_item] PRIMARY KEY CLUSTERED " \
// 		"(" \
// 		"[id] ASC" \
// 		")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]" \
// 		") ON [PRIMARY]";
// 	if(!pOtlCon->ExecCommand(strSQL))
// 		return false;

	// 呼叫中继选择表
	pOtlCon->ExecCommand(_T("truncate table conf_trunk_match")); 
// 	pOtlCon->DropTableCommand(_T("DROP TABLE conf_trunk_match"));
// 	strSQL = "CREATE TABLE [dbo].[conf_trunk_match](" \
// 		"[id] [int] IDENTITY(1,1) NOT NULL," \
// 		"[prefix] [varchar](10) NOT NULL," \
// 		"[caller] [varchar](30) NOT NULL," \
// 		"[trunk_id] [int] NOT NULL," \
// 		"[priority] [int] NOT NULL," \
// 		"CONSTRAINT [PK_conf_trunk_match] PRIMARY KEY CLUSTERED " \
// 		"(" \
// 		"[id] ASC" \
// 		")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]" \
// 		") ON [PRIMARY]";
// 	if(!pOtlCon->ExecCommand(strSQL))
// 		return false;

	return true;
}

/*****************************************************************
**【函数名称】	__createConfigTable4MySql
**【函数功能】	为MySql数据库创建配置表
**【参数】		
**【返回值】	
****************************************************************/
bool CDbSetter::__createConfigTable4MySql( void )
{
	IOtlConnection* pOtlCon = IOtlConnection::getInstance();
	CString strSQL;

	// conf_system 配置表
	pOtlCon->DropTableCommand(_T("DROP TABLE conf_system"));
	strSQL = "CREATE TABLE conf_system (" \
		"id int(10) NOT NULL auto_increment," \
		"name varchar(50) default NULL," \
		"value varchar(300) default NULL," \
		"PRIMARY KEY  (id)" \
		") ENGINE=MyISAM DEFAULT CHARSET=utf8";
	if(!pOtlCon->ExecCommand(strSQL))
		return false;

	// conf_ivr_flow
	pOtlCon->DropTableCommand(_T("DROP TABLE conf_ivr_flow"));
	strSQL = "CREATE TABLE conf_ivr_flow (" \
		"FlowID int(10) NOT NULL auto_increment," \
		"FilePath varchar(512) NOT NULL," \
		"PRIMARY KEY  (FlowID)" \
		") ENGINE=MyISAM DEFAULT CHARSET=utf8";
	if(!pOtlCon->ExecCommand(strSQL))
		return false;

	// conf_dev 设备配置表
	pOtlCon->DropTableCommand(_T("DROP TABLE conf_dev"));
	strSQL = "CREATE TABLE conf_dev (" \
		"id int(10) NOT NULL auto_increment," \
		"name varchar(50) default NULL," \
		"value varchar(300) default NULL," \
		"PRIMARY KEY (id)" \
		") ENGINE=MyISAM DEFAULT CHARSET=utf8";
	if(!pOtlCon->ExecCommand(strSQL))
		return false;

	// conf_line 线路配置表
	pOtlCon->DropTableCommand(_T("DROP TABLE conf_line"));
	strSQL = "CREATE TABLE conf_line (" \
		"id int(10) NOT NULL," \
		"type int(10) NOT NULL," \
		"value varchar(100) default NULL," \
		"PRIMARY KEY (id)" \
		") ENGINE=MyISAM DEFAULT CHARSET=utf8";
	if(!pOtlCon->ExecCommand(strSQL))
		return false;

	// conf_vs VS配置表
	pOtlCon->DropTableCommand(_T("DROP TABLE conf_vs"));
	strSQL = "CREATE TABLE conf_vs (" \
		"id int(10) NOT NULL auto_increment," \
		"name varchar(50) default NULL," \
		"value varchar(300) default NULL," \
		"value2 varchar(300) default NULL," \
		"PRIMARY KEY (id)" \
		") ENGINE=MyISAM DEFAULT CHARSET=utf8";
	if(!pOtlCon->ExecCommand(strSQL))
		return false;

	// SIP账号表
	pOtlCon->DropTableCommand(_T("DROP TABLE conf_sip_account"));
	strSQL = "CREATE TABLE conf_sip_account (" \
		"id int(10) NOT NULL," \
		"account varchar(50) NOT NULL," \
		"auth_account varchar(50) NOT NULL," \
		"password varchar(30) default NULL," \
		"proxy_addr varchar(50) NOT NULL," \
		"proxy_port int(10) NOT NULL," \
		"reg varchar(10) NOT NULL," \
		"reg_interval int(10) NOT NULL," \
		"PRIMARY KEY (id)" \
		") ENGINE=MyISAM DEFAULT CHARSET=utf8";
	if(!pOtlCon->ExecCommand(strSQL))
		return false;

	// 中继表
	pOtlCon->DropTableCommand(_T("DROP TABLE conf_trunk_item"));
	strSQL = "CREATE TABLE conf_trunk_item(" \
		"id int(10) NOT NULL," \
		"node int(10) NOT NULL," \
		"board int(10) NOT NULL," \
		"span int(10) NOT NULL," \
		"sip_user int(10) NOT NULL," \
		"PRIMARY KEY (id)" \
		") ENGINE=MyISAM DEFAULT CHARSET=utf8";
	if(!pOtlCon->ExecCommand(strSQL))
		return false;

	// 呼叫中继选择表
	pOtlCon->DropTableCommand(_T("DROP TABLE conf_trunk_match"));
	strSQL = "CREATE TABLE conf_trunk_match(" \
		"id int(10) NOT NULL auto_increment," \
		"prefix varchar(10) NOT NULL," \
		"caller varchar(30) NOT NULL," \
		"trunk_id int(10) NOT NULL," \
		"priority int(10) NOT NULL," \
		"PRIMARY KEY (id)" \
		") ENGINE=MyISAM DEFAULT CHARSET=utf8";
	if(!pOtlCon->ExecCommand(strSQL))
		return false;	

	return true;
}

/*****************************************************************
**【函数名称】	__createStatisticsTable4SqlServer
**【函数功能】	为SqlServer数据库创建统计表
**【参数】		
**【返回值】	
****************************************************************/
bool CDbSetter::__createStatisticsTable4SqlServer( void )
{
	IOtlConnection* pOtlCon = IOtlConnection::getInstance();
	CString strSQL;

	// stat_callid

	pOtlCon->ExecCommand(_T("truncate table stat_callid")); 
//	pOtlCon->DropTableCommand(_T("DROP TABLE stat_callid"));
// 	strSQL = "CREATE TABLE [dbo].[stat_callid](" \
// 		"[id] [int] NOT NULL," \
// 		"[name] [varchar](30) NULL," \
// 		"[value] [bigint] NULL," \
// 		"CONSTRAINT [PK_stat_callid] PRIMARY KEY CLUSTERED " \
// 		"(" \
// 		"[id] ASC" \
// 		")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON " \
// 		"[PRIMARY]" \
// 		") ON [PRIMARY]";
// 	if(!pOtlCon->ExecCommand(strSQL) || !pOtlCon->InsertConstant(_T("INSERT INTO stat_callid values (1, 'callid', 0)")))
// 		return false;

	if(!pOtlCon->InsertConstant(_T("INSERT INTO stat_callid values (1, 'callid', 0)")))
		return false;

	// rep_host
	pOtlCon->ExecCommand(_T("truncate table rep_host")); 
// 	pOtlCon->DropTableCommand(_T("DROP TABLE rep_host"));
// 	strSQL = "CREATE TABLE [dbo].[rep_host](" \
// 		"[CallID] [bigint] NOT NULL," \
// 		"[ActionID] [tinyint] NOT NULL," \
// 		"[CallTypeOpType] [tinyint] NULL," \
// 		"[HostLine] [int] NULL," \
// 		"[CallerNum] [varchar](64) NULL," \
// 		"[CalleeNum] [varchar](64) NULL," \
// 		"[TimeConnected] [datetime] NULL," \
// 		"[TimeHangUp] [datetime] NULL," \
// 		"CONSTRAINT [PK_rep_host] PRIMARY KEY CLUSTERED " \
// 		"(" \
// 		"[CallID] ASC," \
// 		"[ActionID] ASC" \
// 		")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON " \
// 		"[PRIMARY]" \
// 		") ON [PRIMARY]";
// 	if(!pOtlCon->ExecCommand(strSQL))
// 		return false;

	// rep_trunk_call_in
	pOtlCon->ExecCommand(_T("truncate table rep_trunk_call_in")); 
// 	pOtlCon->DropTableCommand(_T("DROP TABLE rep_trunk_call_in"));
// 	strSQL = "CREATE TABLE [dbo].[rep_trunk_call_in](" \
// 		"[CallID] [bigint] NOT NULL," \
// 		"[ActionID] [tinyint] NOT NULL," \
// 		"[CallerNum] [varchar](64) NULL," \
// 		"[CalleeNum] [varchar](64) NULL," \
// 		"[TimeCallIn] [datetime] NULL," \
// 		"[IsLeaveMsg] [tinyint] NULL," \
// 		"[MsgFileName] [varchar](512) NULL," \
// 		"[IsWantAgent] [tinyint] NULL," \
// 		"[TimeWantAgent] [datetime] NULL," \
// 		"[IsWantAgentSucceed] [tinyint] NULL," \
// 		"[PeriodWantAgent] [int] NULL," \
// 		"[TimeTurnAgent] [datetime] NULL," \
// 		"[IsTurnAgentSucceed] [tinyint] NULL," \
// 		"[AgentID] [int] NULL," \
// 		"[PeriodTurnAgent] [int] NULL," \
// 		"[TimeConnected] [datetime] NULL," \
// 		"[FinalAgentID] [int] NULL," \
// 		"[FinaleAgentNum] [varchar](64) NULL," \
// 		"[RecFileName] [varchar](512) NULL," \
// 		"[TimeHangUp] [datetime] NULL," \
// 		"[PeriodTalking] [int] NULL," \
// 		"[IsSuccess] [int] NULL,"\
// 		"[FailType] [int] NULL,"\
// 		"CONSTRAINT [PK_rep_trunk_call_in] PRIMARY KEY CLUSTERED " \
// 		"(" \
// 		"[CallID] ASC," \
// 		"[ActionID] ASC" \
// 		")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON " \
// 		"[PRIMARY]" \
// 		") ON [PRIMARY]";
// 	if(!pOtlCon->ExecCommand(strSQL))
// 		return false;

	// rep_ext_call_in
	pOtlCon->ExecCommand(_T("truncate table rep_ext_call_in")); 
// 	pOtlCon->DropTableCommand(_T("DROP TABLE rep_ext_call_in"));
// 	strSQL = "CREATE TABLE [dbo].[rep_ext_call_in](" \
// 		"[CallID] [bigint] NOT NULL," \
// 		"[ActionID] [tinyint] NOT NULL," \
// 		"[CallType] [tinyint] NULL," \
// 		"[PeerLineType] [tinyint] NULL," \
// 		"[CallerAgentID] [int] NULL," \
// 		"[CallerNum] [varchar](64) NULL," \
// 		"[CalleeAgentID] [int] NULL," \
// 		"[CalleeNum] [varchar](64) NULL," \
// 		"[TimeAlerting] [datetime] NULL," \
// 		"[PeriodAlerting] [int] NULL," \
// 		"[IsAnswer] [tinyint] NULL," \
// 		"[TimeAnswer] [datetime] NULL," \
// 		"[RecFileName] [varchar](512) NULL," \
// 		"[TimeHangUp] [datetime] NULL," \
// 		"[PeriodTalking] [int] NULL," \
// 		"CONSTRAINT [PK_rep_ext_call_in] PRIMARY KEY CLUSTERED " \
// 		"(" \
// 		"[CallID] ASC," \
// 		"[ActionID] ASC" \
// 		")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON " \
// 		"[PRIMARY]" \
// 		") ON [PRIMARY]";
// 	if(!pOtlCon->ExecCommand(strSQL))
// 		return false;

	// rep_ext_call_out
	pOtlCon->ExecCommand(_T("truncate table rep_ext_call_out")); 
// 	pOtlCon->DropTableCommand(_T("DROP TABLE rep_ext_call_out"));
// 	strSQL = "CREATE TABLE [dbo].[rep_ext_call_out](" \
// 		"[CallID] [bigint] NOT NULL," \
// 		"[ActionID] [tinyint] NOT NULL," \
// 		"[CallType] [tinyint] NULL," \
// 		"[PeerLineType] [tinyint] NULL," \
// 		"[CallerAgentID] [int] NULL," \
// 		"[CallerNum] [varchar](64) NULL," \
// 		"[CalleeAgentID] [int] NULL," \
// 		"[CalleeNum] [varchar](64) NULL," \
// 		"[TimeRingBack] [datetime] NULL," \
// 		"[IsCallOutSucceed] [tinyint] NULL," \
// 		"[TimeConnected] [datetime] NULL," \
// 		"[FinalAgentId] [int] NULL," \
// 		"[FinalAgentNum] [varchar](64) NULL," \
// 		"[RecFileName] [varchar](512) NULL," \
// 		"[TimeHangUp] [datetime] NULL," \
// 		"[PeriodTalking] [int] NULL," \
// 		"CONSTRAINT [PK_rep_ext_call_out] PRIMARY KEY CLUSTERED " \
// 		"(" \
// 		"[CallID] ASC," \
// 		"[ActionID] ASC" \
// 		")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON " \
// 		"[PRIMARY]" \
// 		") ON [PRIMARY]";
// 	if(!pOtlCon->ExecCommand(strSQL))
// 		return false;

	// rep_ivr_call_out
	pOtlCon->ExecCommand(_T("truncate table rep_ivr_call_out")); 
// 	pOtlCon->DropTableCommand(_T("DROP TABLE rep_ivr_call_out"));
// 	strSQL = "CREATE TABLE [dbo].[rep_ivr_call_out](" \
// 		"[CallID] [bigint] NOT NULL," \
// 		"[ActionID] [tinyint] NOT NULL," \
// 		"[HostLine] [int] NULL," \
// 		"[CallerNum] [varchar](64) NULL," \
// 		"[CalleeNum] [varchar](64) NULL," \
// 		"[TimeRingBack] [datetime] NULL," \
// 		"[IsCallOutSucceed] [tinyint] NULL," \
// 		"[TimeConnected] [datetime] NULL," \
// 		"[TimeHangUp] [datetime] NULL," \
// 		"[PeriodTalking] [int] NULL," \
// 		"CONSTRAINT [PK_rep_ivr_call_out] PRIMARY KEY CLUSTERED " \
// 		"(" \
// 		"[CallID] ASC," \
// 		"[ActionID] ASC" \
// 		")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON " \
// 		"[PRIMARY]" \
// 		") ON [PRIMARY]";
// 	if(!pOtlCon->ExecCommand(strSQL))
// 		return false;

	// rep_fax
	pOtlCon->ExecCommand(_T("truncate table rep_fax")); 
// 	pOtlCon->DropTableCommand(_T("DROP TABLE rep_fax"));
// 	strSQL = "CREATE TABLE [dbo].[rep_fax](" \
// 		"[CallID] [bigint] NOT NULL," \
// 		"[ActionID] [tinyint] NOT NULL," \
// 		"[IsAuto] [tinyint] NULL," \
// 		"[RcvOrSend] [tinyint] NULL," \
// 		"[TimeFaxBegin] [datetime] NULL," \
// 		"[CallerAgentID] [int] NULL," \
// 		"[CallerNum] [varchar](64) NULL," \
// 		"[CalleeAgentID] [int] NULL," \
// 		"[CalleeNum] [varchar](64) NULL," \
// 		"[IsSucceed] [tinyint] NULL," \
// 		"[PeriodFax] [int] NULL," \
// 		"[FaxFileName] [varchar](512) NULL," \
// 		"CONSTRAINT [PK_rep_fax] PRIMARY KEY CLUSTERED " \
// 		"(" \
// 		"[CallID] ASC," \
// 		"[ActionID] ASC" \
// 		")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON " \
// 		"[PRIMARY]" \
// 		") ON [PRIMARY]";
// 	if(!pOtlCon->ExecCommand(strSQL))
// 		return false;

	// rep_transfer
	pOtlCon->ExecCommand(_T("truncate table rep_transfer")); 
// 	pOtlCon->DropTableCommand(_T("DROP TABLE rep_transfer"));
// 	strSQL = "CREATE TABLE [dbo].[rep_transfer](" \
// 		"[CallId] [bigint] NOT NULL," \
// 		"[ActionID] [tinyint] NOT NULL," \
// 		"[PeerLineType] [tinyint] NULL," \
// 		"[CallerAgentID] [int] NULL," \
// 		"[CallerNum] [varchar](64) NULL," \
// 		"[CalleeAgentID] [int] NULL," \
// 		"[CalleeNum] [varchar](64) NULL," \
// 		"[TimeBeginTransfer] [datetime] NULL," \
// 		"[IsTransferSucceed] [tinyint] NULL," \
// 		"[TimeConnected] [datetime] NULL," \
// 		"[TimeTransferSucceed] [datetime] NULL," \
// 		"[TimeEndTransfer] [datetime] NULL," \
// 		"CONSTRAINT [PK_rep_transfer] PRIMARY KEY CLUSTERED " \
// 		"(" \
// 		"[CallId] ASC," \
// 		"[ActionID] ASC" \
// 		")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON " \
// 		"[PRIMARY]" \
// 		") ON [PRIMARY]";
// 	if(!pOtlCon->ExecCommand(strSQL))
// 		return false;

	// rep_conference
	pOtlCon->ExecCommand(_T("truncate table rep_conference")); 
// 	pOtlCon->DropTableCommand(_T("DROP TABLE rep_conference"));
// 	strSQL = "CREATE TABLE [dbo].[rep_conference](" \
// 		"[CallID] [bigint] NOT NULL," \
// 		"[ActionID] [tinyint] NOT NULL," \
// 		"[PeerLineType] [tinyint] NULL," \
// 		"[CallerAgentID] [int] NULL," \
// 		"[CallerNum] [varchar](64) NULL," \
// 		"[CalleeAgentID] [int] NULL," \
// 		"[CalleeNum] [varchar](64) NULL," \
// 		"[TimeConference] [datetime] NULL," \
// 		"[IsConferenceSucceed] [tinyint] NULL," \
// 		"[TimeConnected] [datetime] NULL," \
// 		"[TimeConferenceEnd] [datetime] NULL," \
// 		"CONSTRAINT [PK_rep_conference] PRIMARY KEY CLUSTERED " \
// 		"(" \
// 		"[CallID] ASC," \
// 		"[ActionID] ASC" \
// 		")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON " \
// 		"[PRIMARY]" \
// 		") ON [PRIMARY]";
// 	if(!pOtlCon->ExecCommand(strSQL))
// 		return false;

	// rep_agent_state
	pOtlCon->ExecCommand(_T("truncate table rep_agent_state")); 
// 	pOtlCon->DropTableCommand(_T("DROP TABLE rep_agent_state"));
// 	strSQL = "CREATE TABLE [dbo].[rep_agent_state](" \
// 		"[AgentId] [int] NOT NULL," \
// 		"[LoginId] [bigint] NOT NULL," \
// 		"[State] [int] NULL," \
// 		"[OccurTime] [datetime] NULL," \
// 		"[Duration] [bigint] NULL"\
// 		") ON [PRIMARY]";
// 	if(!pOtlCon->ExecCommand(strSQL))
// 		return false;

	// rep_agent_detail
	pOtlCon->ExecCommand(_T("truncate table rep_agent_detail")); 
// 	pOtlCon->DropTableCommand(_T("DROP TABLE rep_agent_detail"));
// 	strSQL = "CREATE TABLE [dbo].[rep_agent_detail](" \
// 		"[AgentId] [int] NOT NULL," \
// 		"[LoginId] [bigint] NOT NULL," \
// 		"[ExtId] [int] NULL," \
// 		"[AgentIP] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL," \
// 		"[GroupId] [varchar](64) NULL," \
// 		"[AgentType] [int] NULL," \
// 		"[TimeLogin] [datetime] NULL," \
// 		"[TimeLogout] [datetime] NULL," \
// 		"[LogoutHost] [int] NULL," \
// 		"[LogoutType] [int] NULL," \
// 		"[LoginTimes] [bigint] NULL," \
// 		"[FreeTimes] [bigint] NULL," \
// 		"[ReposeTimes] [bigint] NULL," \
// 		"[TalkTimes] [bigint] NULL," \
// 		"[ReposeNum] [bigint] NULL," \
// 		"[AnswerNum] [bigint] NULL," \
// 		"[AnswerNumExt] [bigint] NULL," \
// 		"[AnswerNumTrunk] [bigint] NULL," \
// 		"[CallInNumExt] [bigint] NULL," \
// 		"[CallInNumTrunk] [bigint] NULL," \
// 		"[CallInTimesTotalExt] [bigint] NULL," \
// 		"[CallInTimesTotalTrunk] [bigint] NULL," \
// 		"[CallOutNumExt] [bigint] NULL," \
// 		"[CallOutNumTrunk] [bigint] NULL," \
// 		"[CallOutTimesTotalExt] [bigint] NULL," \
// 		"[CallOutTimesTotalTrunk] [bigint] NULL," \
// 		"[CallOutOkNumExt] [bigint] NULL," \
// 		"[CallOutOkNumTrunk] [bigint] NULL," \
// 		"[OverWorkTimesTotal] [bigint] NULL," \
// 		"[TimeAlertingTotal] [bigint] NULL," \
// 		"[TransferNum] [bigint] NULL," \
// 		"[BeTransferNum] [bigint] NULL," \
// 		"[ConferenceNum] [bigint] NULL," \
// 		"[BeConferenceNum] [bigint] NULL," \
// 		"[OverAlertingNum] [bigint] NULL," \
// 		"CONSTRAINT [PK_rep_agent_detail] PRIMARY KEY CLUSTERED " \
// 		"(" \
// 		"[AgentId] ASC," \
// 		"[LoginId] ASC" \
// 		")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]"\
// 		") ON [PRIMARY]" ;
// 	if(!pOtlCon->ExecCommand(strSQL))
// 		return false;

	return true;
}

/*****************************************************************
**【函数名称】	__createStatisticsTable4MySql
**【函数功能】	为MySql数据库创建统计表
**【参数】		
**【返回值】	
****************************************************************/
bool CDbSetter::__createStatisticsTable4MySql( void )
{
	IOtlConnection* pOtlCon = IOtlConnection::getInstance();
	CString strSQL;

	// stat_callid
	pOtlCon->DropTableCommand(_T("DROP TABLE stat_callid"));
	strSQL = "CREATE TABLE stat_callid (" \
		"id int(10) NOT NULL," \
		"name varchar(30) NULL," \
		"value bigint NULL," \
		"PRIMARY KEY (id)" \
		") ENGINE=MyISAM DEFAULT CHARSET=utf8";
	if(!pOtlCon->ExecCommand(strSQL) || !pOtlCon->InsertConstant(_T("INSERT INTO stat_callid values (1, 'callid', 0)")))
		return false;

	// rep_host
	pOtlCon->DropTableCommand(_T("DROP TABLE rep_host"));
	strSQL = "CREATE TABLE rep_host(" \
		"CallID bigint NOT NULL," \
		"ActionID tinyint NOT NULL," \
		"CallTypeOpType tinyint NULL," \
		"HostLine int(10) NULL," \
		"CallerNum varchar(64) NULL," \
		"CalleeNum varchar(64) NULL," \
		"TimeConnected datetime NULL," \
		"TimeHangUp datetime NULL," \
		"PRIMARY KEY (CallID, ActionID)" \
		") ENGINE=MyISAM DEFAULT CHARSET=utf8";
	if(!pOtlCon->ExecCommand(strSQL))
		return false;

	// rep_trunk_call_in
	pOtlCon->DropTableCommand(_T("DROP TABLE rep_trunk_call_in"));
	strSQL = "CREATE TABLE rep_trunk_call_in(" \
		"CallID bigint NOT NULL," \
		"ActionID tinyint NOT NULL," \
		"CallerNum varchar(64) NULL," \
		"CalleeNum varchar(64) NULL," \
		"TimeCallIn datetime NULL," \
		"IsLeaveMsg tinyint NULL," \
		"MsgFileName varchar(512) NULL," \
		"IsWantAgent tinyint NULL," \
		"TimeWantAgent datetime NULL," \
		"IsWantAgentSucceed tinyint NULL," \
		"PeriodWantAgent int(10) NULL," \
		"TimeTurnAgent datetime NULL," \
		"IsTurnAgentSucceed tinyint NULL," \
		"AgentID int(10) NULL," \
		"PeriodTurnAgent int(10) NULL," \
		"TimeConnected datetime NULL," \
		"FinalAgentID int(10) NULL," \
		"FinaleAgentNum varchar(64) NULL," \
		"RecFileName varchar(512) NULL," \
		"TimeHangUp datetime NULL," \
		"PeriodTalking int(10) NULL," \
		"IsSuccess int(10) DEFAULT NULL,"\
		"FailType int(10) DEFAULT NULL,"\
		"PRIMARY KEY (CallID, ActionID)" \
		") ENGINE=MyISAM DEFAULT CHARSET=utf8";
	if(!pOtlCon->ExecCommand(strSQL))
		return false;

	// rep_ext_call_in
	pOtlCon->DropTableCommand(_T("DROP TABLE rep_ext_call_in"));
	strSQL = "CREATE TABLE rep_ext_call_in(" \
		"CallID bigint NOT NULL," \
		"ActionID tinyint NOT NULL," \
		"CallType tinyint NULL," \
		"PeerLineType tinyint NULL," \
		"CallerAgentID int(10) NULL," \
		"CallerNum varchar(64) NULL," \
		"CalleeAgentID int(10) NULL," \
		"CalleeNum varchar(64) NULL," \
		"TimeAlerting datetime NULL," \
		"PeriodAlerting int(10) NULL," \
		"IsAnswer tinyint NULL," \
		"TimeAnswer datetime NULL," \
		"RecFileName varchar(512) NULL," \
		"TimeHangUp datetime NULL," \
		"PeriodTalking int(10) NULL," \
		"PRIMARY KEY (CallID, ActionID)" \
		") ENGINE=MyISAM DEFAULT CHARSET=utf8";
	if(!pOtlCon->ExecCommand(strSQL))
		return false;

	// rep_ext_call_out
	pOtlCon->DropTableCommand(_T("DROP TABLE rep_ext_call_out"));
	strSQL = "CREATE TABLE rep_ext_call_out(" \
		"CallID bigint NOT NULL," \
		"ActionID tinyint NOT NULL," \
		"CallType tinyint NULL," \
		"PeerLineType tinyint NULL," \
		"CallerAgentID int(10) NULL," \
		"CallerNum varchar(64) NULL," \
		"CalleeAgentID int(10) NULL," \
		"CalleeNum varchar(64) NULL," \
		"TimeRingBack datetime NULL," \
		"IsCallOutSucceed tinyint NULL," \
		"TimeConnected datetime NULL," \
		"FinalAgentId int(10) NULL," \
		"FinalAgentNum varchar(64) NULL," \
		"RecFileName varchar(512) NULL," \
		"TimeHangUp datetime NULL," \
		"PeriodTalking int(10) NULL," \
		"PRIMARY KEY (CallID, ActionID)" \
		") ENGINE=MyISAM DEFAULT CHARSET=utf8";
	if(!pOtlCon->ExecCommand(strSQL))
		return false;

	// rep_ivr_call_out
	pOtlCon->DropTableCommand(_T("DROP TABLE rep_ivr_call_out"));
	strSQL = "CREATE TABLE rep_ivr_call_out(" \
		"CallID bigint NOT NULL," \
		"ActionID tinyint NOT NULL," \
		"HostLine int(10) NULL," \
		"CallerNum varchar(64) NULL," \
		"CalleeNum varchar(64) NULL," \
		"TimeRingBack datetime NULL," \
		"IsCallOutSucceed tinyint NULL," \
		"TimeConnected datetime NULL," \
		"TimeHangUp datetime NULL," \
		"PeriodTalking int(10) NULL," \
		"PRIMARY KEY (CallID, ActionID)" \
		") ENGINE=MyISAM DEFAULT CHARSET=utf8";
	if(!pOtlCon->ExecCommand(strSQL))
		return false;

	// rep_fax
	pOtlCon->DropTableCommand(_T("DROP TABLE rep_fax"));
	strSQL = "CREATE TABLE rep_fax(" \
		"CallID bigint NOT NULL," \
		"ActionID tinyint NOT NULL," \
		"IsAuto tinyint NULL," \
		"RcvOrSend tinyint NULL," \
		"TimeFaxBegin datetime NULL," \
		"CallerAgentID int(10) NULL," \
		"CallerNum varchar(64) NULL," \
		"CalleeAgentID int(10) NULL," \
		"CalleeNum varchar(64) NULL," \
		"IsSucceed tinyint NULL," \
		"PeriodFax int(10) NULL," \
		"FaxFileName varchar(512) NULL," \
		"PRIMARY KEY (CallID, ActionID)" \
		") ENGINE=MyISAM DEFAULT CHARSET=utf8";
	if(!pOtlCon->ExecCommand(strSQL))
		return false;

	// rep_transfer
	pOtlCon->DropTableCommand(_T("DROP TABLE rep_transfer"));
	strSQL = "CREATE TABLE rep_transfer(" \
		"CallId bigint NOT NULL," \
		"ActionID tinyint NOT NULL," \
		"PeerLineType tinyint NULL," \
		"CallerAgentID int(10) NULL," \
		"CallerNum varchar(64) NULL," \
		"CalleeAgentID int(10) NULL," \
		"CalleeNum varchar(64) NULL," \
		"TimeBeginTransfer datetime NULL," \
		"IsTransferSucceed tinyint NULL," \
		"TimeConnected datetime NULL," \
		"TimeTransferSucceed datetime NULL," \
		"TimeEndTransfer datetime NULL," \
		"PRIMARY KEY (CallID, ActionID)" \
		") ENGINE=MyISAM DEFAULT CHARSET=utf8";
	if(!pOtlCon->ExecCommand(strSQL))
		return false;

	// rep_conference
	pOtlCon->DropTableCommand(_T("DROP TABLE rep_conference"));
	strSQL = "CREATE TABLE rep_conference(" \
		"CallID bigint NOT NULL," \
		"ActionID tinyint NOT NULL," \
		"PeerLineType tinyint NULL," \
		"CallerAgentID int(10) NULL," \
		"CallerNum varchar(64) NULL," \
		"CalleeAgentID int(10) NULL," \
		"CalleeNum varchar(64) NULL," \
		"TimeConference datetime NULL," \
		"IsConferenceSucceed tinyint NULL," \
		"TimeConnected datetime NULL," \
		"TimeConferenceEnd datetime NULL," \
		"PRIMARY KEY (CallID, ActionID)" \
		") ENGINE=MyISAM DEFAULT CHARSET=utf8";
	if(!pOtlCon->ExecCommand(strSQL))
		return false;

	// rep_agent_state
	pOtlCon->DropTableCommand(_T("DROP TABLE rep_agent_state"));
	strSQL = "CREATE TABLE rep_agent_state(" \
		"AgentId int(10) NOT NULL," \
		"LoginId bigint NOT NULL," \
		"State int(10) NULL," \
		"OccurTime datetime NULL," \
		"Duration bigint NULL," \

		"ReposeType int(10) DEFAULT NULL,"\
		"CallId bigint(20) DEFAULT NULL"\
		") ENGINE=MyISAM DEFAULT CHARSET=utf8";
	if(!pOtlCon->ExecCommand(strSQL))
		return false;

	// rep_agent_detail
	pOtlCon->DropTableCommand(_T("DROP TABLE rep_agent_detail"));
	strSQL = "CREATE TABLE rep_agent_detail ("\
		"AgentId int(10) NOT NULL,"\
		"LoginId bigint(20) NOT NULL,"\
		"ExtId int(10) DEFAULT NULL,"\
		"AgentIP varchar(20) DEFAULT NULL,"\
		"GroupId varchar(64) DEFAULT NULL,"\
		"AgentType int(10) DEFAULT NULL,"\
		"TimeLogin datetime DEFAULT NULL,"\
		"TimeLogout datetime DEFAULT NULL,"\
		"LogoutHost int(10) DEFAULT NULL,"\
		"LogoutType int(10) DEFAULT NULL,"\
		"LoginTimes int(10) DEFAULT NULL,"\
		"FreeTimes int(10) DEFAULT NULL,"\
		"ReposeTimes int(10) DEFAULT NULL,"\
		"TalkTimes int(10) DEFAULT NULL,"\
		"ReposeNum int(10) DEFAULT NULL,"\
		"AnswerNum int(10) DEFAULT NULL,"\
		"AnswerNumExt int(20) DEFAULT NULL,"\
		"AnswerNumTrunk int(20) DEFAULT NULL,"\
		"CallInNumExt int(20) DEFAULT NULL,"\
		"CallInNumTrunk int(20) DEFAULT NULL,"\
		"CallInTimesTotalExt int(20) DEFAULT NULL,"\
		"CallInTimesTotalTrunk int(20) DEFAULT NULL,"\
		"CallOutNumExt int(20) DEFAULT NULL,"\
		"CallOutNumTrunk int(20) DEFAULT NULL,"\
		"CallOutTimesTotalExt int(20) DEFAULT NULL,"\
		"CallOutTimesTotalTrunk int(20) DEFAULT NULL,"\
		"CallOutOkNumExt int(20) DEFAULT NULL,"\
		"CallOutOkNumTrunk int(20) DEFAULT NULL,"\
		"OverWorkTimesTotal int(20) DEFAULT NULL,"\
		"TimeAlertingTotal int(20) DEFAULT NULL,"\
		"TransferNum int(20) DEFAULT NULL,"\
		"BeTransferNum int(20) DEFAULT NULL,"\
		"ConferenceNum int(20) DEFAULT NULL,"\
		"BeConferenceNum int(20) DEFAULT NULL,"\
		"OverAlertingNum int(20) DEFAULT NULL,"\
		"PRIMARY KEY (AgentId,LoginId)"\
		") ENGINE=InnoDB DEFAULT CHARSET=utf8;";	
	if(!pOtlCon->ExecCommand(strSQL))
		return false;

	return true;
}

/*****************************************************************
**【函数名称】	__createStoredProcedure4SqlServer
**【函数功能】	为SqlServer数据库创建存储过程
**【参数】		
**【返回值】	
****************************************************************/
bool CDbSetter::__createStoredProcedure4SqlServer( void )
{
	// 从RESOURCE中读取创建存储过程的语句
	HINSTANCE hInst = AfxGetResourceHandle();
	HRSRC hRes = FindResource(hInst, MAKEINTRESOURCE(IDR_TXT_DA_4_SQLSERVER), RES_TYPE_PROCEDURE);
	if (hRes == NULL) 
	{
		AfxMessageBox("查找SQL Server版本的统计表存储过程资源文件失败！");
		return false;
	}

	DWORD len = SizeofResource(hInst, hRes);
	HGLOBAL hTxt = LoadResource(hInst, hRes);
	CString strSQL((char*)hTxt, len);
	FreeResource(hTxt);

	IOtlConnection* pOtlCon = IOtlConnection::getInstance();

	//pOtlCon->ExecCommand("DROP PROCEDURE IF EXISTS proc_DataAnalysis");

	if (!pOtlCon->ExecCommand(strSQL))
	{
		CString strError = _T("创建SQL Server统计表存储过程出错：\r\n");
		strError += pOtlCon->GetLastError();
		AfxMessageBox(strError);
		return false;
	}

	return true;
}

/*****************************************************************
**【函数名称】	__createStoredProcedure4MySql
**【函数功能】	为MySql数据库创建存储过程
**【参数】		
**【返回值】	
****************************************************************/
bool CDbSetter::__createStoredProcedure4MySql( void )
{
	// 从RESOURCE中读取创建存储过程的语句
	HINSTANCE hInst = AfxGetResourceHandle();
	HRSRC hRes = FindResource(hInst, MAKEINTRESOURCE(IDR_TXT_DA_4_MYSQL), RES_TYPE_PROCEDURE);
	if (hRes == NULL) 
	{
		AfxMessageBox("查找MySQL版本的统计表存储过程资源文件失败！");
		return false;
	}

	DWORD len = SizeofResource(hInst, hRes);
	HGLOBAL hTxt = LoadResource(hInst, hRes);
	CString strSQL((char*)hTxt, len);
	FreeResource(hTxt);

	IOtlConnection* pOtlCon = IOtlConnection::getInstance();

	if (!pOtlCon->ExecCommand(strSQL))
	{
		CString strError = _T("创建MySQL统计表存储过程出错：\r\n");
		strError += pOtlCon->GetLastError();
		AfxMessageBox(strError);
		return false;
	}

	return true;
}

/*****************************************************************
**【函数名称】	createTableConfig
**【函数功能】	创建配置表
**【参数】		
**【返回值】	
****************************************************************/
bool CDbSetter::createTableConfig( DB_TYPE DbTp )
{
	if(DbTp == DB_SQLServer)
		return __createConfigTable4SqlServer();
	else if(DbTp == DB_MySQL)
		return __createConfigTable4MySql();
	else
		return false;
}

/*****************************************************************
**【函数名称】	createTableStatistics
**【函数功能】	创建统计表
**【参数】		
**【返回值】	
****************************************************************/
bool CDbSetter::createTableStatistics( DB_TYPE DbTp )
{
	if(DbTp == DB_SQLServer)
		return __createStatisticsTable4SqlServer();
	else if(DbTp == DB_MySQL)
		return __createStatisticsTable4MySql();
	else
		return false;
}

/*****************************************************************
**【函数名称】	addStoredProcedure
**【函数功能】	添加统计存储过程
**【参数】		
**【返回值】	
****************************************************************/
bool CDbSetter::addStoredProcedure( DB_TYPE DbTp )
{
	if(DbTp == DB_SQLServer)
		return __createStoredProcedure4SqlServer();
	else if(DbTp == DB_MySQL)
		return __createStoredProcedure4MySql();
	else
		return false;
}