private void btnValidateDetail_Click(object sender, EventArgs e)
{
string queryDetail = tbQueryDetailFix.Text;
if (string.IsNullOrEmpty(queryDetail))
{
MessageBox.Show("Tidak ada query yang ingin di validate.", "Peringatan");
return;
}
string queryMessage = string.Empty;
if (!ClassHelper.IsQueryContainDate(queryDetail, out queryMessage))
{
MessageBox.Show(queryMessage);
return;
}
DataTable dt = new DataTable();
try
{
DataParser.CekValidQuery(queryDetail, ClassHelper.dbConnector, ClassHelper.dictDbConnector, out dt);
MessageBox.Show("Query berhasil", "Perhatian");
}
catch (Exception ex)
{
MessageBox.Show("Query tidak valid : " + ex.Message, "Peringatan");
return;
}
}
private void btnValidate_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(tbHasil.Text))
{
MessageBox.Show("Silahkan klik generate terlebih dahulu", "Peringatan");
return;
}
string query = tbHasil.Text.Replace("\r\n", string.Empty);
DataTable dt = new DataTable();
try
{
DataParser.CekValidQuery(query, ClassHelper.dbConnector, ClassHelper.dictDbConnector, out dt);
MessageBox.Show("Query berhasil", "Perhatian");
}
catch (Exception ex)
{
MessageBox.Show("Query tidak valid : " + ex.Message, "Peringatan");
return;
}
}
private void numWizard_ValueChanged(object sender, EventArgs e)
{
string queryPajak = string.Empty;
string queryDetail = string.Empty;
switch (Convert.ToInt32(numWizard.Value))
{
case 0:
lblTitle.Text = "Setting Koneksi Database";
lblInformation.Text = "Halaman ini digunakan untuk setting database, Silahkan ikuti langkah - langkah yang telah disediakan.";
pbInformation.Image = Properties.Resources.db;
btnBack.Enabled = false;
btnNext.Enabled = true;
btnFinish.Enabled = false;
lblConn.BackColor = System.Drawing.Color.White;
lblQuery.BackColor = System.Drawing.Color.Transparent;
lblDetail.BackColor = System.Drawing.Color.Transparent;
lblColumn.BackColor = System.Drawing.Color.Transparent;
lblFinish.BackColor = System.Drawing.Color.Transparent;
tabcWizard.SelectedIndex = 0;
break;
case 1:
lblTitle.Text = "Generate Query Pajak";
lblInformation.Text = "Halaman Generate ini menampilkan Query yang akan digunakan untuk mengambil data pajak.";
pbInformation.Image = Properties.Resources.query;
btnBack.Enabled = true;
btnNext.Enabled = true;
btnFinish.Enabled = false;
lblConn.BackColor = System.Drawing.Color.Transparent;
lblQuery.BackColor = System.Drawing.Color.White;
lblDetail.BackColor = System.Drawing.Color.Transparent;
lblColumn.BackColor = System.Drawing.Color.Transparent;
lblFinish.BackColor = System.Drawing.Color.Transparent;
tabcWizard.SelectedIndex = 1;
//set db connector
ClassHelper.dbConnector = _dbConnector;
ClassHelper.dictDbConnector = new System.Collections.Generic.Dictionary <string, string>();
switch (_dbConnector)
{
case "ORACLE":
ClassHelper.dictDbConnector.Add(tbOracleDataSource.Name, tbOracleDataSource.Text);
ClassHelper.dictDbConnector.Add(tbOracleUsername.Name, tbOracleUsername.Text);
ClassHelper.dictDbConnector.Add(tbOraclePassword.Name, tbOraclePassword.Text);
break;
case "SQL":
ClassHelper.dictDbConnector.Add(tbSqlServer.Name, tbSqlServer.Text);
ClassHelper.dictDbConnector.Add(tbSqlDatabase.Name, tbSqlDatabase.Text);
ClassHelper.dictDbConnector.Add(tbSqlUsername.Name, tbSqlUsername.Text);
ClassHelper.dictDbConnector.Add(tbSqlPassword.Name, tbSqlPassword.Text);
break;
case "MYSQL":
ClassHelper.dictDbConnector.Add(tbMySqlServer.Name, tbMySqlServer.Text);
ClassHelper.dictDbConnector.Add(tbMySqlDatabase.Name, tbMySqlDatabase.Text);
ClassHelper.dictDbConnector.Add(tbMySqlUsername.Name, tbMySqlUsername.Text);
ClassHelper.dictDbConnector.Add(tbMySqlPassword.Name, tbMySqlPassword.Text);
break;
case "ACCESS":
ClassHelper.dictDbConnector.Add(tbAccessPath.Name, tbAccessPath.Text);
ClassHelper.dictDbConnector.Add(tbAccessPassword.Name, tbAccessPassword.Text);
break;
default:
break;
}
break;
case 2:
lblTitle.Text = "Generate Query Detail";
lblInformation.Text = "Halaman Generate ini menampilkan Query yang akan digunakan untuk mengambil detail transaksi.";
pbInformation.Image = Properties.Resources.table_relationships;
_dtPajak = new DataTable();
queryPajak = _objForm.Controls["tbHasil"].Text;
_queryPajakFinish = queryPajak;
#region Old Code
//switch (_dbConnector)
//{
// case "ORACLE":
// try
// {
// POProject.CommandAdapter.OracleCmdBuilder cmd = new POProject.CommandAdapter.OracleCmdBuilder(tbOracleDataSource.Text, tbOracleUsername.Text, tbOraclePassword.Text);
// _queryPajakFinish = queryPajak + " WHERE rownum <= 10";
// cmd.Query = _queryPajakFinish;
// _dtPajak = cmd.GetTable();
// DataTable resultTable = new DataTable();
// ValidateNamaKolom(queryPajak, _dtPajak, out resultTable);
// _dtPajak = resultTable.Copy();
// }
// catch (Exception ex)
// {
// MessageBox.Show(ex.Message, "Peringatan");
// btnBack_Click(null, null);
// return;
// }
// break;
// case "SQL":
// try
// {
// POProject.CommandAdapter.SqlCmdBuilder cmd = new POProject.CommandAdapter.SqlCmdBuilder(tbSqlServer.Text, tbSqlDatabase.Text,
// tbSqlUsername.Text, tbSqlPassword.Text);
// _queryPajakFinish = "SELECT TOP(10) " + queryPajak.Replace("SELECT", string.Empty).Replace("select", string.Empty);
// cmd.Query = _queryPajakFinish;
// _dtPajak = cmd.GetTable();
// }
// catch (Exception ex)
// {
// MessageBox.Show(ex.Message, "Peringatan");
// btnBack_Click(null, null);
// return;
// }
// break;
// case "MYSQL":
// try
// {
// POProject.CommandAdapter.MySqlCmdBuilder cmd = new POProject.CommandAdapter.MySqlCmdBuilder(tbMySqlServer.Text, tbMySqlDatabase.Text,
// tbMySqlUsername.Text, tbMySqlPassword.Text);
// _queryPajakFinish = queryPajak + " LIMIT 10";
// cmd.Query = _queryPajakFinish;
// _dtPajak = cmd.GetTable();
// }
// catch (Exception ex)
// {
// MessageBox.Show(ex.Message, "Peringatan");
// btnBack_Click(null, null);
// return;
// }
// break;
// case "ACCESS":
// try
// {
// POProject.CommandAdapter.MsAccessCmdBuilder cmd = new POProject.CommandAdapter.MsAccessCmdBuilder(tbAccessPath.Text, tbAccessPassword.Text);
// _queryPajakFinish = "SELECT TOP 10" + queryPajak.Replace("SELECT", string.Empty).Replace("select", string.Empty);
// cmd.Query = _queryPajakFinish;
// _dtPajak = cmd.GetTable();
// }
// catch (Exception ex)
// {
// MessageBox.Show(ex.Message, "Peringatan");
// btnBack_Click(null, null);
// return;
// }
// break;
// default:
// break;
//}
#endregion
try
{
DataParser.CekValidQuery(_queryPajakFinish, _dbConnector, ClassHelper.dictDbConnector, out _dtDetail);
}
catch (Exception ex)
{
throw ex;
}
btnBack.Enabled = true;
btnNext.Enabled = true;
btnFinish.Enabled = false;
lblConn.BackColor = System.Drawing.Color.Transparent;
lblQuery.BackColor = System.Drawing.Color.Transparent;
lblDetail.BackColor = System.Drawing.Color.White;
lblColumn.BackColor = System.Drawing.Color.Transparent;
lblFinish.BackColor = System.Drawing.Color.Transparent;
tabcWizard.SelectedIndex = 2;
break;
case 3:
lblTitle.Text = "Pilihan Nama Kolom";
lblInformation.Text = "Halaman ini untuk memilih nama - nama kolom yang akan diambil, Nama kolom yang diambil akan menghasilkan query select kolom tersebut.";
pbInformation.Image = Properties.Resources.tablecolumn;
if (!_isFromBack)
{
_dtPajak = new DataTable();
_dtDetail = new DataTable();
queryPajak = _objForm.Controls["tbHasil"].Text;
_queryPajakFinish = queryPajak;
queryDetail = _objForm2.Controls["tbHasil"].Text;
_queryDetailFinish = queryDetail;
try
{
DataParser.CekValidQuery(_queryPajakFinish, _dbConnector, ClassHelper.dictDbConnector, out _dtPajak);
DataTable resultPajak = new DataTable();
ValidateNamaKolom(queryPajak, _dtPajak, out resultPajak);
_dtPajak = resultPajak.Copy();
DataParser.CekValidQuery(_queryDetailFinish, _dbConnector, ClassHelper.dictDbConnector, out _dtDetail);
DataTable resultDetail = new DataTable();
ValidateNamaKolom(queryDetail, _dtDetail, out resultDetail);
_dtDetail = resultDetail.Copy();
}
catch (Exception ex)
{
throw ex;
}
if (_dtPajak != null)
{
lvPajak.Items.Clear();
foreach (DataColumn dCol in _dtPajak.Columns)
{
lvPajak.Items.Add(dCol.ColumnName.ToUpper());
}
}
if (_dtDetail != null)
{
lvDetail.Items.Clear();
foreach (DataColumn dCol in _dtDetail.Columns)
{
lvDetail.Items.Add(dCol.ColumnName.ToUpper());
}
}
}
btnBack.Enabled = true;
btnNext.Enabled = true;
btnFinish.Enabled = false;
lblConn.BackColor = System.Drawing.Color.Transparent;
lblQuery.BackColor = System.Drawing.Color.Transparent;
lblDetail.BackColor = System.Drawing.Color.Transparent;
lblColumn.BackColor = System.Drawing.Color.White;
lblFinish.BackColor = System.Drawing.Color.Transparent;
tabcWizard.SelectedIndex = 3;
break;
case 4:
lblTitle.Text = "Selesai dan Simpan";
lblInformation.Text = "Selesai dan anda dapat menyimpan setting database yang telah dibuat.";
pbInformation.Image = Properties.Resources.savesettings;
string nmKolomPajak = string.Empty;
string nmKolomDetail = string.Empty;
//list nama kolom todo query
//if (cbxAllPajak.Checked)
//{
// int iStart = _queryPajakFinish.IndexOf("SELECT");
// int iEnd = _queryPajakFinish.IndexOf("FROM");
// string newQuery = "SELECT * " + _queryPajakFinish.Remove(iStart, iEnd).Replace("WHERE rownum <= 10", string.Empty).Replace("LIMIT 10", string.Empty);
// tbQueryPajakFix.Text = newQuery;
//}
//else
//{
foreach (var col in lvPajak.CheckedItems)
{
nmKolomPajak += ((System.Windows.Forms.ListViewItem)col).Text + ",";
}
nmKolomPajak = "SELECT " + nmKolomPajak.Remove(nmKolomPajak.Length - 1);
int iStartPajak = _queryPajakFinish.ToUpper().IndexOf("SELECT");
int iEndPajak = _queryPajakFinish.ToUpper().IndexOf("FROM");
//string newQueryPajak = nmKolomPajak + " " + _queryPajakFinish.Remove(iStartPajak, iEndPajak).Replace("WHERE rownum <= 10", string.Empty).Replace("LIMIT 10", string.Empty);
string newQueryPajak = nmKolomPajak + " " + _queryPajakFinish.Remove(iStartPajak, iEndPajak);
tbQueryPajakFix.Text = newQueryPajak;
//}
//if (cbxAllDetail.Checked)
//{
// int iStart = _queryDetailFinish.IndexOf("SELECT");
// int iEnd = _queryDetailFinish.IndexOf("FROM");
// string newQuery = "SELECT * " + _queryDetailFinish.Remove(iStart, iEnd).Replace("WHERE rownum <= 10", string.Empty).Replace("LIMIT 10", string.Empty);
// tbQueryDetailFix.Text = newQuery;
//}
//else
//{
foreach (var col in lvDetail.CheckedItems)
{
nmKolomDetail += ((System.Windows.Forms.ListViewItem)col).Text + ",";
}
nmKolomDetail = "SELECT " + nmKolomDetail.Remove(nmKolomDetail.Length - 1);
int iStartDetail = _queryDetailFinish.ToUpper().IndexOf("SELECT");
int iEndDetail = _queryDetailFinish.ToUpper().IndexOf("FROM");
//string newQueryDetail = nmKolomDetail + " " + _queryDetailFinish.Remove(iStartDetail, iEndDetail).Replace("WHERE rownum <= 10", string.Empty).Replace("LIMIT 10", string.Empty);
string newQueryDetail = nmKolomDetail + " " + _queryDetailFinish.Remove(iStartDetail, iEndDetail);
tbQueryDetailFix.Text = newQueryDetail;
//}
btnBack.Enabled = true;
btnNext.Enabled = false;
btnFinish.Enabled = true;
lblConn.BackColor = System.Drawing.Color.Transparent;
lblQuery.BackColor = System.Drawing.Color.Transparent;
lblDetail.BackColor = System.Drawing.Color.Transparent;
lblColumn.BackColor = System.Drawing.Color.Transparent;
lblFinish.BackColor = System.Drawing.Color.White;
tabcWizard.SelectedIndex = 4;
break;
default:
break;
}
}