#include "connDialog.h"
#include "struct.h"
#include "treewidget.h"


extern QString g_connectName;


ConnDialog::ConnDialog(QWidget *parent /* = 0 */):QDialog(parent)
{
	splashScreen=0;
	QGridLayout *layout = new QGridLayout;
	
	QLabel		*label;
	label = new QLabel(tr("數(shù)據(jù)庫類型"));
	layout->addWidget(label,0,0);
	comboType = new QComboBox();
	comboType->addItem(tr("SQL lite"),QVariant(tr("QSQLITE")));
	comboType->addItem(tr("Oracle"),QVariant(tr("QOCI")));
	comboType->addItem(tr("MySql"),QVariant(tr("QMYSQL")));
	comboType->addItem(tr("ODBC"),QVariant(tr("QODBC")));
	comboType->addItem(tr("Sybase"),QVariant(tr("QTDS")));
	comboType->addItem(tr("IBM DB2"),QVariant(tr("QDB2")));	
	layout->addWidget(comboType,0,1);

	label = new QLabel(tr("服務(wù)器"));
	layout->addWidget(label,1,0);
	editServer = new QLineEdit(this);
	layout->addWidget(editServer,1,1);

	label = new QLabel(tr("數(shù)據(jù)庫"));
	layout->addWidget(label,2,0);
	editDatabase = new QLineEdit(this);
	QFileInfo info(QApplication::applicationDirPath()+"/dm.db");	
	if(info.exists() && info.size()>0)
	{
		editDatabase->setText(QApplication::applicationDirPath()+"/dm.db");
	}
	else
	{
		QSettings settings(qApp->applicationName(), "dbmanager");
		comboType->setCurrentIndex(comboType->findData(settings.value("type", "QSQLITE")));
	}
	layout->addWidget(editDatabase,2,1);

  label  =	new	QLabel(tr("用戶名"));
  layout->addWidget(label,3,0);
	editUsername = new QLineEdit(this);
	layout->addWidget(editUsername,3,1);
	label = new	QLabel(tr("密碼"));
	layout->addWidget(label,4,0);	
	editPassword  = new QLineEdit(this);
	editPassword->setEchoMode(QLineEdit::Password);
	layout->addWidget(editPassword,4,1);

  QDialogButtonBox *box=new QDialogButtonBox(this);
  box->addButton(QDialogButtonBox::Ok);
  box->addButton(QDialogButtonBox::Cancel);
  connect(box,SIGNAL(accepted()), this, SLOT(myAccept()));
  connect(box,SIGNAL(rejected()), this,SLOT(reject()));

	QVBoxLayout *layoutMain=new QVBoxLayout(this);
	QGroupBox *groupBox=new QGroupBox(tr(""),this);
	groupBox->setLayout(layout);
	layoutMain->addWidget(groupBox);
	layoutMain->addStretch();
	layoutMain->addWidget(box,Qt::AlignHCenter);
	setLayout(layoutMain);	

	setFixedWidth(300);

	setWindowTitle("連接數(shù)據(jù)庫");
}


void ConnDialog::myAccept()
{
	QString type;
	QVariant vtype = comboType->itemData(comboType->currentIndex());
	if (vtype.isValid())
		type=vtype.toString();
	
	db=QSqlDatabase::addDatabase(type,g_connectName);
	db.setHostName(editServer->text());
	db.setDatabaseName(editDatabase->text());
	if(db.open(editUsername->text(),editPassword->text()))
	{
		QSettings settings(qApp->applicationName(), "dbmanager");
		settings.setValue("type",QVariant(type));
		createTables();
		accept();
	}
	else
		QMessageBox::warning(this,"","連接錯誤,請檢查輸入項!");	
}

void ConnDialog::createTables()
{
	if(splashScreen)
	{
		hide();
		splashScreen->show();
		splashScreen->showMessage("正在初始化緩存,請稍候...",Qt::AlignCenter|Qt::AlignBottom);
	}
QString sql1,sql2,sql3,sql4;
sql1="CREATE TABLE dm_application\n" \
"(\n" \
"  id INTEGER not null            ,             \n" \
"  application_name varchar(100),               \n" \
"  constraint pk_dm_application primary key(id) \n" \
")\n" ;
sql2="CREATE TABLE dm_connection \n" \
"(\n" \
"  id       INTEGER not null,   \n" \
"  application_id varchar(100),  \n" \
"  type     varchar(20) not null, \n" \
"  server   varchar(100) ,        \n" \
"  dbname       varchar(100),      \n" \
"  username varchar(100),      \n" \
"  password varchar(100),      \n" \
"  memo     varchar(255),       \n" \
"  constraint pk_dm_connection_type primary key(id) \n" \
")\n" ;
sql3="CREATE TABLE dm_table\n" \
"(\n" \
"   connection_id INTEGER,       \n" \
"   table_name varchar(100),     \n" \
"   memo       varchar(100),     \n" \
"   constraint pk_dm_table primary key(connection_id,table_name)  \n" \
")\n" ;
sql4="CREATE TABLE dm_field\n" \
"(\n" \
"   connection_id INTEGER,        \n" \
"   table_name varchar(100),   \n" \
"   field_name varchar(100),    \n" \
"   field_xh   INTEGER,          \n" \
"   can_filter varchar(3),       \n" \
"   asso_table_name varchar(100), \n" \
"   asso_field_name varchar(100),  \n" \
"   asso_field_display varchar(100), \n" \
"   memo  varchar(100),           \n" \
"   is_primarykey varchar(3),      \n" \
"   primarykey_name varchar(100),   \n" \
"   constraint pk_dm_field primary key(connection_id,table_name,field_name) \n" \
")" ;
QString sqlType="create table dm_type(id varchar(10),name varchar(10))";
QString sqlInitApplication="insert into dm_application(id,application_name) values(1,'數(shù)據(jù)庫')";
QString sqlExampleConnection="insert into dm_connection(id,application_id,type,dbname,memo) values(1,1,'QSQLITE',':memory:','Example')";
QStringList list=db.tables(QSql::Tables);
foreach(QString tname,list)
{
	if(tname.toLower()=="dm_application")
		sql1.clear();
	if(tname.toLower()=="dm_connection")
		sql2.clear();
	if(tname.toLower()=="dm_table")
		sql3.clear();
	if(tname.toLower()=="dm_field")
		sql4.clear();
	if(tname.toLower()=="dm_type")
		sqlType.clear();
}

if(sql1.length()) 
{
		db.exec(sql1);
		if(db.lastError().isValid()) QMessageBox::warning(0,"",db.lastError().text());
		db.exec(sqlInitApplication);
		if(db.lastError().isValid()) QMessageBox::warning(0,"",db.lastError().text());
}
if(sql2.length()) 
{
	db.exec(sql2);
	if(db.lastError().isValid()) QMessageBox::warning(0,"",db.lastError().text());
	db.exec(sqlExampleConnection);
	if(db.lastError().isValid()) QMessageBox::warning(0,"",db.lastError().text());
} 
if(sql3.length()) 
{
	db.exec(sql3); 
	if(db.lastError().isValid()) QMessageBox::warning(0,"",db.lastError().text());
}
if(sql4.length()) 
{
	db.exec(sql4); 
	if(db.lastError().isValid()) QMessageBox::warning(0,"",db.lastError().text());
}
	

ConnectionSetup connection;
connection.id=0;
connection.type     = comboType->itemData(comboType->currentIndex()).toString();
connection.server   = editServer->text();
connection.database = editDatabase->text();
connection.username = editUsername->text();
connection.password = editPassword->text();
connection.memo     = "system";
TreeWidget *tree = new TreeWidget(this);	   	 
tree->setConnectionSetup(&db,connection);	   	 
tree->insertTable(&db);
delete tree;

QString sql;

if(sqlType.length())
{
	db.exec(sqlType);
	if(db.lastError().isValid()) 
		QMessageBox::warning(0,"",db.lastError().text());
sql="insert into dm_type(id,name) values('QSQLITE','SQL lite')";	 db.exec(sql);
sql="insert into dm_type(id,name) values('QOCI','Oracle')";		 db.exec(sql);
sql="insert into dm_type(id,name) values('QODBC','ODBC')";		 db.exec(sql);
sql="insert into dm_type(id,name) values('QMYSQL','MySql')";		 db.exec(sql);
sql="insert into dm_type(id,name) values('QTDS','Sybase')";		 db.exec(sql);
sql="insert into dm_type(id,name) values('QDB2','IBM DB2')";		 db.exec(sql);


	sql="update dm_field set asso_table_name='DM_TYPE',asso_field_name='ID',asso_field_display='NAME' where connection_id=0 and upper(table_name)='DM_CONNECTION' and upper(field_name)='TYPE'";
  db.exec(sql);
  if(db.lastError().isValid()) QMessageBox::warning(0,"",db.lastError().text());
	sql="update dm_field set asso_table_name='DM_APPLICATION',asso_field_name='ID',asso_field_display='APPLICATION_NAME' where connection_id=0 and upper(table_name)='DM_CONNECTION' and upper(field_name)='APPLICATION_ID'";
  db.exec(sql);
  if(db.lastError().isValid()) QMessageBox::warning(0,"",db.lastError().text());
  	
  
}
	if(db.driver() && db.driver()->hasFeature(QSqlDriver::Transactions))
	{
		db.rollback();
		db.transaction();
	}

}