private void LoadSupplier()
{
SupplierNameCombobox.DataSource = null;
MySqlDataReader rdr;
DataTable dt = new DataTable();
DS.mySqlConnect();
string SQLcommand = "";
if (nonactivecheckbox1.Checked)
{
SQLcommand = "SELECT SUPPLIER_ID AS 'ID', SUPPLIER_FULL_NAME AS 'NAME' FROM MASTER_SUPPLIER";
}
else
{
SQLcommand = "SELECT SUPPLIER_ID AS 'ID', SUPPLIER_FULL_NAME AS 'NAME' FROM MASTER_SUPPLIER WHERE SUPPLIER_ACTIVE = 1";
}
using (rdr = DS.getData(SQLcommand))
{
if (rdr.HasRows)
{
SupplierNameCombobox.Visible = true;
nonactivecheckbox1.Visible = true;
ErrorLabel1.Visible = false;
dt.Load(rdr);
SupplierNameCombobox.DataSource = dt;
SupplierNameCombobox.ValueMember = "ID";
SupplierNameCombobox.DisplayMember = "NAME";
SupplierNameCombobox.SelectedIndex = 0;
}
else
{
SupplierNameCombobox.Visible = false;
nonactivecheckbox1.Visible = false;
ErrorLabel1.Visible = true;
}
}
}
private bool saveDataTransaction()
{
bool result = false;
string sqlCommand = "";
string newPassword = newPasswordTextBox.Text;
//newPassword = MySqlHelper.EscapeString(newPassword);
MySqlException internalEX = null;
DS.beginTransaction();
try
{
DS.mySqlConnect();
sqlCommand = "UPDATE MASTER_USER SET USER_PASSWORD = '******' WHERE ID = " + selectedUserID;
gutil.saveSystemDebugLog(0, "UPDATE NEW PASSWORD [" + newPassword + "]");
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
DS.commit();
result = true;
}
catch (Exception e)
{
try
{
DS.rollBack();
}
catch (MySqlException ex)
{
if (DS.getMyTransConnection() != null)
{
gutil.showDBOPError(ex, "ROLLBACK");
}
}
gutil.showDBOPError(e, "INSERT");
result = false;
}
finally
{
DS.mySqlClose();
}
return(result);
}
private void loadCustomerData()
{
MySqlDataReader rdr;
DataTable dt = new DataTable();
DS.mySqlConnect();
using (rdr = DS.getData("SELECT * FROM MASTER_CUSTOMER WHERE CUSTOMER_ID = " + selectedCustomerID))
{
if (rdr.HasRows)
{
while (rdr.Read())
{
dateJoinedDateTimePicked.Value = rdr.GetDateTime("CUSTOMER_JOINED_DATE");
custNameTextBox.Text = rdr.GetString("CUSTOMER_FULL_NAME");
custAddress1TextBox.Text = rdr.GetString("CUSTOMER_ADDRESS1");
custAddress2TextBox.Text = rdr.GetString("CUSTOMER_ADDRESS2");
custAddressCityTextBox.Text = rdr.GetString("CUSTOMER_ADDRESS_CITY");
custTelTextBox.Text = rdr.GetString("CUSTOMER_PHONE");
custFaxTextBox.Text = rdr.GetString("CUSTOMER_FAX");
custEmailTextBox.Text = rdr.GetString("CUSTOMER_EMAIL");
custTotalSalesTextBox.Text = rdr.GetString("CUSTOMER_TOTAL_SALES_COUNT");
groupPelangganCombo.SelectedIndex = rdr.GetInt32("CUSTOMER_GROUP") - 1;
if (rdr.GetString("CUSTOMER_ACTIVE").Equals("1"))
{
nonAktifCheckbox.Checked = false;
}
else
{
nonAktifCheckbox.Checked = true;
}
}
}
}
}
private bool saveDataTransaction()
{
bool result = false;
string sqlCommand = "";
MySqlException internalEX = null;
DS.beginTransaction();
noFakturValue = noFakturTextBox.Text;
try
{
DS.mySqlConnect();
sqlCommand = "UPDATE SYS_CONFIG SET NO_FAKTUR = '" + noFakturValue + "' WHERE ID = 1";
gutil.saveSystemDebugLog(globalConstants.MENU_SET_NO_FAKTUR, "UPDATE SYS CONFIG VALUE [" + noFakturValue + "]");
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
DS.commit();
result = true;
}
catch (Exception e)
{
gutil.saveSystemDebugLog(globalConstants.MENU_SET_NO_FAKTUR, "EXCEPTION THROWN [" + e.Message + "]");
try
{
DS.rollBack();
}
catch (MySqlException ex)
{
if (DS.getMyTransConnection() != null)
{
gutil.showDBOPError(ex, "ROLLBACK");
}
}
gutil.showDBOPError(e, "INSERT");
result = false;
}
finally
{
DS.mySqlClose();
}
return(result);
}
private void loadNamaUser(int user_id, out string nama)
{
nama = "";
MySqlDataReader rdr;
DataTable dt = new DataTable();
DS.mySqlConnect();
//1 load default 2 setting user
using (rdr = DS.getData("SELECT USER_NAME AS 'NAME' FROM MASTER_USER WHERE ID=" + user_id))
{
if (rdr.HasRows)
{
rdr.Read();
nama = rdr.GetString("NAME");
}
}
}
private void loadCustomerData(int options = 0)
{
MySqlDataReader rdr;
DataTable dt = new DataTable();
string sqlCommand;
string namaPelangganParam = "";
DS.mySqlConnect();
namaPelangganParam = MySqlHelper.EscapeString(namaPelangganTextbox.Text);
if (options == 1)
{
sqlCommand = "SELECT CUSTOMER_ID, CUSTOMER_FULL_NAME AS 'NAMA PELANGGAN', DATE_FORMAT(CUSTOMER_JOINED_DATE,'%d-%M-%Y') AS 'TANGGAL BERGABUNG', IF(CUSTOMER_GROUP = 1,'ECER', IF(CUSTOMER_GROUP = 2,'PARTAI', 'GROSIR')) AS 'GROUP CUSTOMER' FROM MASTER_CUSTOMER";
}
else
{
if (namaPelangganTextbox.Text.Equals(""))
{
return;
}
if (pelanggangnonactiveoption.Checked == true)
{
sqlCommand = "SELECT CUSTOMER_ID, CUSTOMER_FULL_NAME AS 'NAMA PELANGGAN', DATE_FORMAT(CUSTOMER_JOINED_DATE,'%d-%M-%Y') AS 'TANGGAL BERGABUNG', IF(CUSTOMER_GROUP = 1,'ECER', IF(CUSTOMER_GROUP = 2,'PARTAI', 'GROSIR')) AS 'GROUP CUSTOMER' FROM MASTER_CUSTOMER WHERE CUSTOMER_FULL_NAME LIKE '%" + namaPelangganParam + "%'";
}
else
{
sqlCommand = "SELECT CUSTOMER_ID, CUSTOMER_FULL_NAME AS 'NAMA PELANGGAN', DATE_FORMAT(CUSTOMER_JOINED_DATE,'%d-%M-%Y') AS 'TANGGAL BERGABUNG', IF(CUSTOMER_GROUP = 1,'ECER', IF(CUSTOMER_GROUP = 2,'PARTAI', 'GROSIR')) AS 'GROUP CUSTOMER' FROM MASTER_CUSTOMER WHERE CUSTOMER_ACTIVE = 1 AND CUSTOMER_FULL_NAME LIKE '%" + namaPelangganParam + "%'";
}
}
using (rdr = DS.getData(sqlCommand))
{
if (rdr.HasRows)
{
dt.Load(rdr);
dataPelangganDataGridView.DataSource = dt;
dataPelangganDataGridView.Columns["CUSTOMER_ID"].Visible = false;
dataPelangganDataGridView.Columns["NAMA PELANGGAN"].Width = 300;
dataPelangganDataGridView.Columns["TANGGAL BERGABUNG"].Width = 200;
dataPelangganDataGridView.Columns["GROUP CUSTOMER"].Width = 200;
}
}
}
private bool saveDataTransaction()
{
bool result = false;
int dataCount = 0;
string sqlCommand = "";
DS.beginTransaction();
try
{
DS.mySqlConnect();
dataCount = Convert.ToInt32(DS.getDataSingleValue("SELECT COUNT(1) FROM SYS_CONFIG_TAX"));
if (dataCount > 0)
{
// UPDATE
sqlCommand = "UPDATE SYS_CONFIG_TAX SET PERSENTASE_PENJUALAN = " + gutil.allTrim(persentasePenjualan.Text) + ", PERSENTASE_PEMBELIAN = " + gutil.allTrim(persentasePembelian.Text) + ", " +
"AVERAGE_PENJUALAN_HARIAN = " + omsetPenjualan.Text + ", AVERAGE_PEMBELIAN_HARIAN = " + omsetPembelian.Text + ", RASIO_TOLERANSI = " + rasioToleransi.Text;
gutil.saveSystemDebugLog(globalConstants.MENU_PENGATURAN_LIMIT_PAJAK, "UPDATE SYS CONFIG WITH NEW DATA");
}
else
{
// INSERT
sqlCommand = "INSERT INTO SYS_CONFIG_TAX (PERSENTASE_PENJUALAN, PERSENTASE_PEMBELIAN, AVERAGE_PENJUALAN_HARIAN, AVERAGE_PEMBELIAN_HARIAN, RASIO_TOLERANSI) " +
"VALUES (" + gutil.allTrim(persentasePenjualan.Text) + ", " + gutil.allTrim(persentasePembelian.Text) + ", " + omsetPenjualan.Text + ", " + omsetPembelian.Text + ", " + rasioToleransi.Text + ")";
gutil.saveSystemDebugLog(globalConstants.MENU_PENGATURAN_LIMIT_PAJAK, "INSERT SYS CONFIG WITH NEW DATA");
}
DS.executeNonQueryCommand(sqlCommand);
DS.commit();
result = true;
}
catch (Exception e)
{
gutil.saveSystemDebugLog(globalConstants.MENU_PENGATURAN_LIMIT_PAJAK, "EXCEPTION THROWN [" + e.Message + "]");
result = false;
MessageBox.Show(e.Message);
}
finally
{
DS.mySqlClose();
}
return(result);
}
private void loadProductName()
{
string productName = "";
if ((newSelectedInternalProductID == selectedInternalProductID) || newSelectedInternalProductID == 0)
{
return;
}
DS.mySqlConnect();
productName = DS.getDataSingleValue("SELECT PRODUCT_NAME FROM MASTER_PRODUCT WHERE ID = " + newSelectedInternalProductID).ToString();
newProductIDTextBox.Text = productName;
}
private void loadKategoriData(int options = 0)
{
MySqlDataReader rdr;
DataTable dt = new DataTable();
string sqlCommand;
string categoryNameParam;
if (options != 1 && categoryNameTextBox.Text.Equals(""))
{
return;
}
DS.mySqlConnect();
if (options == 1)
{
sqlCommand = "SELECT CATEGORY_ID, CATEGORY_NAME AS 'NAMA KATEGORI', CATEGORY_DESCRIPTION AS 'DESKRIPSI KATEGORI' FROM MASTER_CATEGORY";
}
else
{
categoryNameParam = MySqlHelper.EscapeString(categoryNameTextBox.Text);
if (tagnonactiveoption.Checked == true)
{
sqlCommand = "SELECT CATEGORY_ID, CATEGORY_NAME AS 'NAMA KATEGORI', CATEGORY_DESCRIPTION AS 'DESKRIPSI KATEGORI' FROM MASTER_CATEGORY WHERE CATEGORY_NAME LIKE '%" + categoryNameParam + "%'";
}
else
{
sqlCommand = "SELECT CATEGORY_ID, CATEGORY_NAME AS 'NAMA KATEGORI', CATEGORY_DESCRIPTION AS 'DESKRIPSI KATEGORI' FROM MASTER_CATEGORY WHERE CATEGORY_ACTIVE = 1 AND CATEGORY_NAME LIKE '%" + categoryNameParam + "%'";
}
}
using (rdr = DS.getData(sqlCommand))
{
if (rdr.HasRows)
{
dt.Load(rdr);
kategoriProdukDataGridView.DataSource = dt;
kategoriProdukDataGridView.Columns["CATEGORY_ID"].Visible = false;
kategoriProdukDataGridView.Columns["NAMA KATEGORI"].Width = 200;
kategoriProdukDataGridView.Columns["DESKRIPSI KATEGORI"].Width = 300;
}
}
}
private void loadUnitData(int options = 0)
{
MySqlDataReader rdr;
DataTable dt = new DataTable();
string sqlCommand;
string unitNameParam = "";
unitNameParam = MySqlHelper.EscapeString(unitNameTextBox.Text);
DS.mySqlConnect();
if (options == 1)
{
sqlCommand = "SELECT UNIT_ID, UNIT_NAME AS 'NAMA UNIT', UNIT_DESCRIPTION AS 'DESKRIPSI UNIT' FROM MASTER_UNIT";
}
else
{
if (unitNameTextBox.Text.Equals(""))
{
return;
}
if (satuannonactiveoption.Checked == true)
{
sqlCommand = "SELECT UNIT_ID, UNIT_NAME AS 'NAMA UNIT', UNIT_DESCRIPTION AS 'DESKRIPSI UNIT' FROM MASTER_UNIT WHERE UNIT_NAME LIKE '%" + unitNameParam + "%'";
}
else
{
sqlCommand = "SELECT UNIT_ID, UNIT_NAME AS 'NAMA UNIT', UNIT_DESCRIPTION AS 'DESKRIPSI UNIT' FROM MASTER_UNIT WHERE UNIT_ACTIVE = 1 AND UNIT_NAME LIKE '%" + unitNameParam + "%'";
}
}
using (rdr = DS.getData(sqlCommand))
{
if (rdr.HasRows)
{
dt.Load(rdr);
dataUnitGridView.DataSource = dt;
dataUnitGridView.Columns["UNIT_ID"].Visible = false;
dataUnitGridView.Columns["NAMA UNIT"].Width = 200;
dataUnitGridView.Columns["DESKRIPSI UNIT"].Width = 300;
}
}
}
private void loadSupplierData(int options = 0)
{
MySqlDataReader rdr;
DataTable dt = new DataTable();
string sqlCommand;
string namaSupplierParam = "";
DS.mySqlConnect();
//if (namaSupplierTextbox.Text.Equals(""))
// return;
namaSupplierParam = MySqlHelper.EscapeString(namaSupplierTextbox.Text);
if (options == 1)
{
sqlCommand = "SELECT SUPPLIER_ID, SUPPLIER_FULL_NAME AS 'NAMA SUPPLIER' FROM MASTER_SUPPLIER";
}
else
{
if (suppliernonactiveoption.Checked == true)
{
sqlCommand = "SELECT SUPPLIER_ID, SUPPLIER_FULL_NAME AS 'NAMA SUPPLIER' FROM MASTER_SUPPLIER WHERE SUPPLIER_FULL_NAME LIKE '%" + namaSupplierParam + "%'";
}
else
{
sqlCommand = "SELECT SUPPLIER_ID, SUPPLIER_FULL_NAME AS 'NAMA SUPPLIER' FROM MASTER_SUPPLIER WHERE SUPPLIER_ACTIVE = 1 AND SUPPLIER_FULL_NAME LIKE '%" + namaSupplierParam + "%'";
}
}
using (rdr = DS.getData(sqlCommand))
{
dataSupplierDataGridView.DataSource = null;
if (rdr.HasRows)
{
dt.Load(rdr);
dataSupplierDataGridView.DataSource = dt;
dataSupplierDataGridView.Columns["SUPPLIER_ID"].Visible = false;
dataSupplierDataGridView.Columns["NAMA SUPPLIER"].Width = 300;
}
}
}
private void button1_Click(object sender, EventArgs e)
{
DS.mySqlConnect();
if (textBox1.Text.Equals(""))
{
MessageBox.Show("Error no SQL Command");
}
else
{
String sqlcmd = textBox1.Text;
if (textBox2.Text.Equals(""))
{
DS.writeXML(sqlcmd); //done writing XML report
}
else
{
DS.writeXML(sqlcmd, textBox2.Text);
}
}
}
private void loadDeskripsi(int accountID)
{
DeskripsiAkunTextbox.Text = "";
MySqlDataReader rdr;
DataTable dt = new DataTable();
DS_2.mySqlConnect();
using (rdr = DS_2.getData("SELECT ACCOUNT_ID as 'ID', ACCOUNT_TYPE_ID AS 'TIPE', ACCOUNT_NAME AS 'NAME' FROM MASTER_ACCOUNT WHERE ACCOUNT_ID = '" + selectedAccountID + "'"))
{
if (rdr.HasRows)
{
while (rdr.Read())
{
NamaAkunTextbox.Text = rdr.GetString("NAME");
selectedTipeAkun = rdr.GetInt32("TIPE");
}
}
}
}
private void loadAccountData(string accountnameParam, int options = 0)
{
MySqlDataReader rdr;
DataTable dt = new DataTable();
string sqlCommand;
string accountName = MySqlHelper.EscapeString(accountnameParam);
DS.mySqlConnect();
if (options == 1)
{
sqlCommand = "SELECT MASTER_ACCOUNT.ID AS 'ID', MASTER_ACCOUNT.ACCOUNT_ID AS 'KODE AKUN', MASTER_ACCOUNT.ACCOUNT_NAME AS 'DESKRIPSI', MASTER_ACCOUNT_TYPE.ACCOUNT_TYPE_NAME AS 'TIPE' FROM MASTER_ACCOUNT, MASTER_ACCOUNT_TYPE WHERE MASTER_ACCOUNT.ACCOUNT_TYPE_ID = MASTER_ACCOUNT_TYPE.ACCOUNT_TYPE_ID";
}
else
{
if (accountnonactiveoption.Checked)
{
sqlCommand = "SELECT MASTER_ACCOUNT.ID AS 'ID', MASTER_ACCOUNT.ACCOUNT_ID AS 'KODE AKUN', MASTER_ACCOUNT.ACCOUNT_NAME AS 'DESKRIPSI', MASTER_ACCOUNT_TYPE.ACCOUNT_TYPE_NAME AS 'TIPE' FROM MASTER_ACCOUNT, MASTER_ACCOUNT_TYPE WHERE MASTER_ACCOUNT.ACCOUNT_TYPE_ID = MASTER_ACCOUNT_TYPE.ACCOUNT_TYPE_ID AND MASTER_ACCOUNT.ACCOUNT_NAME LIKE '%" + accountName + "%'";
}
else
{
sqlCommand = "SELECT MASTER_ACCOUNT.ID AS 'ID', MASTER_ACCOUNT.ACCOUNT_ID AS 'KODE AKUN', MASTER_ACCOUNT.ACCOUNT_NAME AS 'DESKRIPSI', MASTER_ACCOUNT_TYPE.ACCOUNT_TYPE_NAME AS 'TIPE' FROM MASTER_ACCOUNT, MASTER_ACCOUNT_TYPE WHERE MASTER_ACCOUNT.ACCOUNT_ACTIVE = 1 AND MASTER_ACCOUNT.ACCOUNT_TYPE_ID = MASTER_ACCOUNT_TYPE.ACCOUNT_TYPE_ID AND MASTER_ACCOUNT.ACCOUNT_NAME LIKE '%" + accountName + "%'";
}
}
using (rdr = DS.getData(sqlCommand))
{
if (rdr.HasRows)
{
dt.Load(rdr);
dataAccountGridView.DataSource = dt;
dataAccountGridView.Columns["ID"].Visible = false;
dataAccountGridView.Columns["KODE AKUN"].Width = 150;
dataAccountGridView.Columns["DESKRIPSI"].Width = 250;
dataAccountGridView.Columns["TIPE"].Width = 100;
}
}
}
private void loadBranchData(string branchNameParam)
{
MySqlDataReader rdr;
DataTable dt = new DataTable();
string sqlCommand;
string branchName = MySqlHelper.EscapeString(branchNameParam);
DS.mySqlConnect();
if (branchNameParam.Equals("All"))
{
sqlCommand = "SELECT BRANCH_ID, BRANCH_NAME AS 'NAMA CABANG', branch_ip4 AS 'ALAMAT IP CABANG' FROM MASTER_BRANCH";
}
else
{
if (cabangnonactiveoption.Checked)
{
sqlCommand = "SELECT BRANCH_ID, BRANCH_NAME AS 'NAMA CABANG', branch_ip4 AS 'ALAMAT IP CABANG' FROM MASTER_BRANCH WHERE BRANCH_NAME LIKE '%" + branchName + "%'";
}
else
{
sqlCommand = "SELECT BRANCH_ID, BRANCH_NAME AS 'NAMA CABANG', branch_ip4 AS 'ALAMAT IP CABANG' FROM MASTER_BRANCH WHERE BRANCH_ACTIVE = 1 AND BRANCH_NAME LIKE '%" + branchName + "%'";
}
}
using (rdr = DS.getData(sqlCommand))
{
if (rdr.HasRows)
{
dt.Load(rdr);
dataCabangGridView.DataSource = dt;
dataCabangGridView.Columns["BRANCH_ID"].Visible = true;
dataCabangGridView.Columns["NAMA CABANG"].Width = 200;
dataCabangGridView.Columns["ALAMAT IP CABANG"].Width = 200;
}
}
}
private void loadUserGroupData(int options = 0)
{
MySqlDataReader rdr;
DataTable dt = new DataTable();
string sqlCommand;
string namaGroupParam = MySqlHelper.EscapeString(namaGroupTextbox.Text);
DS.mySqlConnect();
if (options == 1)
{
sqlCommand = "SELECT GROUP_ID, GROUP_USER_NAME AS 'NAMA GROUP', GROUP_USER_DESCRIPTION AS 'DESKRIPSI GROUP' FROM MASTER_GROUP";
}
else
{
if (groupnonactiveoption.Checked)
{
sqlCommand = "SELECT GROUP_ID, GROUP_USER_NAME AS 'NAMA GROUP', GROUP_USER_DESCRIPTION AS 'DESKRIPSI GROUP' FROM MASTER_GROUP WHERE GROUP_USER_NAME LIKE '%" + namaGroupParam + "%'";
}
else
{
sqlCommand = "SELECT GROUP_ID, GROUP_USER_NAME AS 'NAMA GROUP', GROUP_USER_DESCRIPTION AS 'DESKRIPSI GROUP' FROM MASTER_GROUP WHERE GROUP_USER_ACTIVE = 1 AND GROUP_USER_NAME LIKE '%" + namaGroupParam + "%'";
}
}
using (rdr = DS.getData(sqlCommand))
{
if (rdr.HasRows)
{
dt.Load(rdr);
dataUserGroupGridView.DataSource = dt;
dataUserGroupGridView.Columns["GROUP_ID"].Visible = false;
dataUserGroupGridView.Columns["NAMA GROUP"].Width = 200;
dataUserGroupGridView.Columns["DESKRIPSI GROUP"].Width = 300;
}
}
}
private void loadROdata()
{
MySqlDataReader rdr;
DataTable dt = new DataTable();
string sqlCommand;
string dateFrom;
string dateTo;
string noMutasiParam = "";
DS.mySqlConnect();
//sqlCommand = "SELECT ID, PM_INVOICE AS 'NO MUTASI', DATE_FORMAT(PM_DATETIME,'%d-%M-%Y') AS 'TGL MUTASI', M1.BRANCH_NAME AS 'ASAL MUTASI', M2.BRANCH_NAME AS 'TUJUAN MUTASI', PM_TOTAL AS 'TOTAL', RO_INVOICE AS 'NO PERMINTAAN' " +
// "FROM PRODUCTS_MUTATION_HEADER LEFT OUTER JOIN MASTER_BRANCH M1 ON (BRANCH_ID_FROM = M1.BRANCH_ID) " +
// "LEFT OUTER JOIN MASTER_BRANCH M2 ON (BRANCH_ID_TO = M2.BRANCH_ID) " +
// "WHERE 1 = 1 AND PM_RECEIVED = 0";
sqlCommand = "SELECT ID, PM_INVOICE AS 'NO MUTASI', DATE_FORMAT(PM_DATETIME,'%d-%M-%Y') AS 'TGL MUTASI', M2.BRANCH_NAME AS 'TUJUAN MUTASI', PM_TOTAL AS 'TOTAL', RO_INVOICE AS 'NO PERMINTAAN' " +
"FROM PRODUCTS_MUTATION_HEADER PH, MASTER_BRANCH M2 " + //LEFT OUTER JOIN MASTER_BRANCH M2 ON (BRANCH_ID_TO = M2.BRANCH_ID) " +
"WHERE 1 = 1 AND PH.BRANCH_ID_TO = M2.BRANCH_ID AND PM_RECEIVED = 0";
if (!showAllCheckBox.Checked)
{
if (noMutasiTextBox.Text.Length > 0)
{
noMutasiParam = MySqlHelper.EscapeString(noMutasiTextBox.Text);
sqlCommand = sqlCommand + " AND PM_INVOICE LIKE '%" + noMutasiParam + "%'";
}
dateFrom = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(PMDtPicker_1.Value));
dateTo = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(PMDtPicker_2.Value));
sqlCommand = sqlCommand + " AND DATE_FORMAT(PM_DATETIME, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(PM_DATETIME, '%Y%m%d') <= '" + dateTo + "'";
//if (branchFromCombo.Text.Length > 0)
//{
// sqlCommand = sqlCommand + " AND BRANCH_ID_FROM = " + selectedBranchFromID;
//}
if (branchToCombo.Text.Length > 0)
{
sqlCommand = sqlCommand + " AND BRANCH_ID_TO = " + selectedBranchToID;
}
}
sqlCommand = sqlCommand + " ORDER BY PM_DATETIME ASC";
using (rdr = DS.getData(sqlCommand))
{
dataRequestOrderGridView.DataSource = null;
if (rdr.HasRows)
{
dt.Load(rdr);
dataRequestOrderGridView.DataSource = dt;
dataRequestOrderGridView.Columns["ID"].Visible = false;
dataRequestOrderGridView.Columns["NO MUTASI"].Width = 200;
dataRequestOrderGridView.Columns["TGL MUTASI"].Width = 200;
//dataRequestOrderGridView.Columns["ASAL MUTASI"].Width = 200;
dataRequestOrderGridView.Columns["TUJUAN MUTASI"].Width = 200;
dataRequestOrderGridView.Columns["TOTAL"].Width = 200;
dataRequestOrderGridView.Columns["NO PERMINTAAN"].Width = 200;
}
rdr.Close();
}
}
private bool saveDataTransaction()
{
bool result = false;
string sqlCommand = "";
double newStockQty = 0;
string adjustmentDate;
string descriptionParam;
MySqlException internalEX = null;
newStockQty = Convert.ToDouble(jumlahBaruMaskedTextBox.Text);
adjustmentDate = String.Format(culture, "{0:dd-MM-yyyy}", DateTime.Now);
if (descriptionTextBox.Text.Length <= 0)
{
descriptionTextBox.Text = " ";
}
descriptionParam = MySqlHelper.EscapeString(descriptionTextBox.Text);
DS.beginTransaction();
try
{
DS.mySqlConnect();
// UPDATE MASTER PRODUCT WITH THE NEW QTY
sqlCommand = "UPDATE MASTER_PRODUCT SET PRODUCT_STOCK_QTY = " + newStockQty + " WHERE ID = " + selectedProductID;
gutil.saveSystemDebugLog(globalConstants.MENU_PENYESUAIAN_STOK, "UPDATE STOCK QTY [" + selectedProductID + "]");
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
// INSERT INTO PRODUCT ADJUSTMENT TABLE
sqlCommand = "INSERT INTO PRODUCT_ADJUSTMENT (PRODUCT_ID, PRODUCT_ADJUSTMENT_DATE, PRODUCT_OLD_STOCK_QTY, PRODUCT_NEW_STOCK_QTY, PRODUCT_ADJUSTMENT_DESCRIPTION) VALUES " +
"('" + kodeProductTextBox.Text + "', STR_TO_DATE('" + adjustmentDate + "', '%d-%m-%Y'), " + jumlahAwalMaskedTextBox.Text + ", " + jumlahBaruMaskedTextBox.Text + ", '" + descriptionParam + "')";
gutil.saveSystemDebugLog(globalConstants.MENU_PENYESUAIAN_STOK, "INSERT INTO PRODUCT ADJUSTMENT TABLE [" + kodeProductTextBox.Text + ", " + jumlahAwalMaskedTextBox.Text + ", " + jumlahBaruMaskedTextBox.Text + "]");
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
DS.commit();
result = true;
}
catch (Exception e)
{
gutil.saveSystemDebugLog(globalConstants.MENU_PENYESUAIAN_STOK, "EXCEPTION THROWN [" + e.Message + "]");
try
{
DS.rollBack();
}
catch (MySqlException ex)
{
if (DS.getMyTransConnection() != null)
{
gutil.showDBOPError(ex, "ROLLBACK");
}
}
gutil.showDBOPError(e, "INSERT");
result = false;
}
finally
{
DS.mySqlClose();
}
return(result);
}
public bool saveDataTransaction()
{
bool result = false;
string sqlCommand = "";
string paymentDateTime = "";
DateTime selectedPaymentDate;
string paymentDueDateTime = "";
DateTime selectedPaymentDueDate;
double paymentNominal = 0;
int paymentMethod = 0;
string paymentDescription = "";
int paymentConfirmed = 0;
MySqlException internalEX = null;
selectedPaymentDate = paymentDateTimePicker.Value;
paymentDateTime = String.Format(culture, "{0:dd-MM-yyyy}", selectedPaymentDate);
paymentNominal = Convert.ToDouble(totalPaymentMaskedTextBox.Text);
paymentDescription = MySqlHelper.EscapeString(descriptionTextBox.Text);
paymentMethod = paymentCombo.SelectedIndex;
if (paymentNominal > globalTotalValue)
{
paymentNominal = globalTotalValue;
}
if (paymentMethod < 3) //0, 1, 2
{
// TUNAI, KARTU DEBIT, KARTU KREDIT
paymentConfirmed = 1;
paymentDueDateTime = paymentDateTime;
gutil.saveSystemDebugLog(globalConstants.MENU_PEMBAYARAN_HUTANG_SUPPLIER, "PAYMENT DEBT BY CASH");
}
else if (paymentMethod == 3) //3
{
// TRANSFER
paymentDueDateTime = paymentDateTime;
gutil.saveSystemDebugLog(globalConstants.MENU_PEMBAYARAN_HUTANG_SUPPLIER, "PAYMENT DEBT BY TRANSFER");
}
else if (paymentMethod > 3) //4, 5
{
// CEK, BG
selectedPaymentDueDate = cairDTPicker.Value;
paymentDueDateTime = String.Format(culture, "{0:dd-MM-yyyy}", selectedPaymentDueDate);
gutil.saveSystemDebugLog(globalConstants.MENU_PEMBAYARAN_HUTANG_SUPPLIER, "PAYMENT DEBT BY CHEQUE OR BG");
}
DS.beginTransaction();
try
{
DS.mySqlConnect();
// SAVE HEADER TABLE
sqlCommand = "INSERT INTO PAYMENT_DEBT (DEBT_ID, PAYMENT_DATE, PM_ID, PAYMENT_NOMINAL, PAYMENT_DESCRIPTION, PAYMENT_CONFIRMED, PAYMENT_DUE_DATE) VALUES " +
"(" + selectedDebtID + ", STR_TO_DATE('" + paymentDateTime + "', '%d-%m-%Y'), 1, " + gutil.validateDecimalNumericInput(paymentNominal) + ", '" + paymentDescription + "', " + paymentConfirmed + ", STR_TO_DATE('" + paymentDueDateTime + "', '%d-%m-%Y'))";
gutil.saveSystemDebugLog(globalConstants.MENU_PEMBAYARAN_HUTANG_SUPPLIER, "INSERT INTO PAYMENT DEBT [" + selectedDebtID + ", " + gutil.validateDecimalNumericInput(paymentNominal) + "]");
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
if (paymentNominal == globalTotalValue && paymentConfirmed == 1)
{
// UPDATE CREDIT TABLE
sqlCommand = "UPDATE DEBT SET DEBT_PAID = 1 WHERE DEBT_ID = " + selectedDebtID;
gutil.saveSystemDebugLog(globalConstants.MENU_PEMBAYARAN_HUTANG_SUPPLIER, "UPDATE DEBT, SET TO FULLY PAID [" + selectedDebtID + "]");
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
// UPDATE SALES HEADER TABLE
sqlCommand = "UPDATE PURCHASE_HEADER SET PURCHASE_PAID = 1 WHERE PURCHASE_INVOICE = '" + selectedPOInvoice + "'";
gutil.saveSystemDebugLog(globalConstants.MENU_PEMBAYARAN_HUTANG_SUPPLIER, "UPDATE PURCHASE HEADER SET TO FULLY PAID [" + selectedPOInvoice + "]");
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
}
DS.commit();
result = true;
}
catch (Exception e)
{
gutil.saveSystemDebugLog(globalConstants.MENU_PEMBAYARAN_HUTANG_SUPPLIER, "EXCEPTION THROWN [" + e.Message + "]");
try
{
DS.rollBack();
}
catch (MySqlException ex)
{
if (DS.getMyTransConnection() != null)
{
gutil.showDBOPError(ex, "ROLLBACK");
}
}
gutil.showDBOPError(e, "INSERT");
result = false;
}
finally
{
DS.mySqlClose();
}
return(result);
}
private bool updateLocalData()
{
bool result = false;
string sqlCommand = "";
string productID;
string productBarcode;
string productName;
string productDescription;
string productBasePrice;
string productRetailPrice;
string productBulkPrice;
string productWholesalePrice;
string productService;
string productUnitID;
string categoryID;
MySqlException internalEX = null;
MySqlDataReader rdr;
DataTable dt = new DataTable();
DataTable dt2 = new DataTable();
DataTable dt3 = new DataTable();
int i = 0;
DS.beginTransaction();
gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "UPDATE LOCAL DATA");
sqlCommand = "SELECT PRODUCT_ID FROM MASTER_PRODUCT WHERE PRODUCT_ACTIVE = 1";
try
{
DS.mySqlConnect();
using (rdr = DS.getData(sqlCommand))
{
if (rdr.HasRows)
{
dt.Load(rdr);
rdr.Close();
dataGridView1.DataSource = dt;
i = 0;
// UPDATE CURRENT DATA IN LOCAL DATABASE
gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "UPDATE CURRENT DATA IN LOCAL DATABASE");
while (i < dataGridView1.Rows.Count)
{
productID = dataGridView1.Rows[i].Cells["PRODUCT_ID"].Value.ToString();
productBasePrice = DS.getDataSingleValue("SELECT PRODUCT_BASE_PRICE FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID = '" + productID + "'").ToString();
productRetailPrice = DS.getDataSingleValue("SELECT PRODUCT_RETAIL_PRICE FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID = '" + productID + "'").ToString();
productBulkPrice = DS.getDataSingleValue("SELECT PRODUCT_BULK_PRICE FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID = '" + productID + "'").ToString();
productWholesalePrice = DS.getDataSingleValue("SELECT PRODUCT_WHOLESALE_PRICE FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID = '" + productID + "'").ToString();
productUnitID = DS.getDataSingleValue("SELECT UNIT_ID FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID = '" + productID + "'").ToString();
sqlCommand = "UPDATE MASTER_PRODUCT SET " +
"PRODUCT_BASE_PRICE = " + productBasePrice + ", PRODUCT_RETAIL_PRICE = " + productRetailPrice + ", PRODUCT_BULK_PRICE = " + productBulkPrice + ", PRODUCT_WHOLESALE_PRICE = " + productWholesalePrice + ", UNIT_ID = " + productUnitID +
" WHERE PRODUCT_ID = '" + productID + "'";
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
//sqlCommand = "DELETE FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID = '" + productID + "'";
//if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
// throw internalEX;
i++;
}
gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "FINISHED UPDATE CURRENT DATA IN LOCAL DATABASE");
dataGridView1.DataSource = null;
// INSERT NEW PRODUCT CATEGORY
sqlCommand = "SELECT * FROM TEMP_PRODUCT_CATEGORY WHERE CONCAT(PRODUCT_ID, '-', CATEGORY_ID) NOT IN (SELECT CONCAT(PRODUCT_ID, '-', CATEGORY_ID) FROM PRODUCT_CATEGORY)";
using (rdr = DS.getData(sqlCommand))
{
gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "INSERT NEW PRODUCT CATEGORY [" + Convert.ToInt32(rdr.HasRows) + "]");
if (rdr.HasRows)
{
dt2.Load(rdr);
rdr.Close();
dataGridView1.DataSource = dt2;
i = 0;
while (i < dataGridView1.Rows.Count)
{
productID = dataGridView1.Rows[i].Cells["PRODUCT_ID"].Value.ToString();
categoryID = dataGridView1.Rows[i].Cells["CATEGORY_ID"].Value.ToString();
sqlCommand = "INSERT INTO PRODUCT_CATEGORY (PRODUCT_ID, CATEGORY_ID) VALUES (" +
"'" + productID + "', " + categoryID + ")";
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
i++;
}
}
}
dataGridView1.DataSource = null;
// INSERT NEW DATA
sqlCommand = "SELECT * FROM TEMP_MASTER_PRODUCT WHERE PRODUCT_ID NOT IN (SELECT PRODUCT_ID FROM MASTER_PRODUCT)";
using (rdr = DS.getData(sqlCommand))
{
gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "INSERT NEW PRODUCT DATA [" + Convert.ToInt32(rdr.HasRows) + "]");
if (rdr.HasRows)
{
dt3.Load(rdr);
rdr.Close();
dataGridView1.DataSource = dt3;
i = 0;
while (i < dataGridView1.Rows.Count)
{
productID = MySqlHelper.EscapeString(dataGridView1.Rows[i].Cells["PRODUCT_ID"].Value.ToString());
productBarcode = MySqlHelper.EscapeString(dataGridView1.Rows[i].Cells["PRODUCT_BARCODE"].Value.ToString());
productName = MySqlHelper.EscapeString(dataGridView1.Rows[i].Cells["PRODUCT_NAME"].Value.ToString());
productDescription = MySqlHelper.EscapeString(dataGridView1.Rows[i].Cells["PRODUCT_DESCRIPTION"].Value.ToString());
productBasePrice = dataGridView1.Rows[i].Cells["PRODUCT_BASE_PRICE"].Value.ToString();
productRetailPrice = dataGridView1.Rows[i].Cells["PRODUCT_RETAIL_PRICE"].Value.ToString();
productBulkPrice = dataGridView1.Rows[i].Cells["PRODUCT_BULK_PRICE"].Value.ToString();
productWholesalePrice = dataGridView1.Rows[i].Cells["PRODUCT_WHOLESALE_PRICE"].Value.ToString();
productService = dataGridView1.Rows[i].Cells["PRODUCT_IS_SERVICE"].Value.ToString();
productUnitID = dataGridView1.Rows[i].Cells["UNIT_ID"].Value.ToString();
sqlCommand = "INSERT INTO MASTER_PRODUCT (PRODUCT_ID, PRODUCT_BARCODE, PRODUCT_NAME, PRODUCT_DESCRIPTION, PRODUCT_BASE_PRICE, PRODUCT_RETAIL_PRICE, PRODUCT_BULK_PRICE, PRODUCT_WHOLESALE_PRICE, UNIT_ID, PRODUCT_STOCK_QTY, PRODUCT_LIMIT_STOCK, PRODUCT_SHELVES, PRODUCT_ACTIVE, PRODUCT_IS_SERVICE) VALUES (" +
"'" + productID + "', '" + productBarcode + "', '" + productName + "', '" + productDescription + "', " + productBasePrice + ", " + productRetailPrice + ", " + productBulkPrice + ", " + productWholesalePrice + ", " + productUnitID + ", 0, 0, '--00', 1, " + productService + ")";
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
i++;
}
}
}
DS.commit();
}
}
result = true;
}
catch (Exception e)
{
gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "EXCEPTION THROWN [" + e.Message + "]");
try
{
DS.rollBack();
}
catch (MySqlException ex)
{
if (DS.getMyTransConnection() != null)
{
gutil.showDBOPError(ex, "ROLLBACK");
}
}
gutil.showDBOPError(e, "ROLLBACK");
result = false;
}
finally
{
DS.mySqlClose();
}
return(result);
}
private bool saveDataTransaction()
{
bool result = false;
string sqlCommand = "";
string productQty;
string productOldQty;
string productID;
string adjusmentDate = "";
string productDescription = "";
int i = 0;
MySqlException internalEX = null;
DS.beginTransaction();
try
{
adjusmentDate = String.Format(culture, "{0:dd-MM-yyyy}", DateTime.Now);
DS.mySqlConnect();
i = 0;
while (i<detailImportDataGrid.Rows.Count)
{
productQty = detailImportDataGrid.Rows[i].Cells["productRealQty"].Value.ToString();
productID = MySqlHelper.EscapeString(detailImportDataGrid.Rows[i].Cells["productID"].Value.ToString());
productOldQty = detailImportDataGrid.Rows[i].Cells["productQty"].Value.ToString();
productDescription =MySqlHelper.EscapeString(detailImportDataGrid.Rows[i].Cells["description"].Value.ToString());
if (!productOldQty.Equals(productQty))
{
sqlCommand = "UPDATE MASTER_PRODUCT SET " +
"PRODUCT_STOCK_QTY = " + productQty + " " +
"WHERE PRODUCT_ID = '" + productID + "'";
gutil.saveSystemDebugLog(globalConstants.MENU_PENYESUAIAN_STOK, "UPDATE STOCK QTY [" + productID + "]");
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
throw internalEX;
sqlCommand = "INSERT INTO PRODUCT_ADJUSTMENT (PRODUCT_ID, PRODUCT_ADJUSTMENT_DATE, PRODUCT_OLD_STOCK_QTY, PRODUCT_NEW_STOCK_QTY, PRODUCT_ADJUSTMENT_DESCRIPTION) " +
"VALUES " +
"('" + productID + "', STR_TO_DATE('" + adjusmentDate + "', '%d-%m-%Y'), " + productOldQty + ", " + productQty + ", '" + productDescription + "')";
gutil.saveSystemDebugLog(globalConstants.MENU_PENYESUAIAN_STOK, "INSERT INTO PRODUCT ADJUSTMENT [" + productID + ", " + productOldQty + ", " + productQty + "]");
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
throw internalEX;
}
i += 1;
}
DS.commit();
result = true;
}
catch (Exception e)
{
gutil.saveSystemDebugLog(globalConstants.MENU_PENYESUAIAN_STOK, "EXCEPTION THROWN [" + e.Message + "]");
try
{
DS.rollBack();
}
catch (MySqlException ex)
{
if (DS.getMyTransConnection() != null)
{
gutil.showDBOPError(ex, "ROLLBACK");
}
}
gutil.showDBOPError(e, "ROLLBACK");
result = false;
}
finally
{
DS.mySqlClose();
}
return result;
}
private bool saveDataTransaction()
{
bool result = false;
string sqlCommand = "";
string dateLogin;
string dateLogOut;
string dateTimeFrom;
string dateTimeTo;
double totalCashTransaction = 0;
double totalNonCashTransaction = 0;
double totalOtherTransaction = 0;
MySqlException internalEX = null;
DS.beginTransaction();
try
{
DS.mySqlConnect();
if (loginState == 0)
{
dateLogin = String.Format(culture, "{0:dd-M-yyyy HH:mm}", DateTime.Now);
// INSERT TO CASHIER LOG
sqlCommand = "INSERT INTO CASHIER_LOG (USER_ID, DATE_LOGIN, AMOUNT_START) VALUES (" + gUtil.getUserID() + ", STR_TO_DATE('" + dateLogin + "', '%d-%m-%Y %H:%i'), " + startAmountBox.Text + ")";
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
gUtil.saveSystemDebugLog(0, "INSERT DATA FOR A NEW CASHIER SESSION, SA=" + startAmountBox.Text);
}
else if (loginState == 1)
{
dateLogOut = String.Format(culture, "{0:dd-M-yyyy HH:mm}", DateTime.Now);
dateTimeFrom = String.Format(culture, "{0:yyyyMMddHHmm}", dateTimeLogin);
dateTimeTo = String.Format(culture, "{0:yyyyMMddHHmm}", DateTime.Now);
//GET TOTAL CASH TRANSACTION
sqlCommand = "SELECT IFNULL(SUM(SALES_TOTAL), 0) FROM SALES_HEADER " +
"WHERE SALES_TOP = 1 AND SALES_PAYMENT_METHOD = 0 " +
"AND DATE_FORMAT(SALES_DATE, '%Y%m%d%H%i') >= '" + dateTimeFrom + "' " +
"AND DATE_FORMAT(SALES_DATE, '%Y%m%d%H%i') <= '" + dateTimeTo + "'";
totalCashTransaction = Convert.ToDouble(DS.getDataSingleValue(sqlCommand));
//GET TOTAL NON CASH TRANSACTION
sqlCommand = "SELECT IFNULL(SUM(SALES_TOTAL), 0) FROM SALES_HEADER " +
"WHERE SALES_TOP = 1 AND SALES_PAYMENT_METHOD > 0 " +
"AND DATE_FORMAT(SALES_DATE, '%Y%m%d%H%i') >= '" + dateTimeFrom + "' " +
"AND DATE_FORMAT(SALES_DATE, '%Y%m%d%H%i') <= '" + dateTimeTo + "'";
totalNonCashTransaction = Convert.ToDouble(DS.getDataSingleValue(sqlCommand));
//GET TOTAL OTHER TRANSACTION
sqlCommand = "SELECT IFNULL(SUM(JOURNAL_NOMINAL), 0) FROM DAILY_JOURNAL DJ, MASTER_ACCOUNT MA " +
"WHERE DJ.PM_ID = 1 AND DJ.ACCOUNT_ID = MA.ACCOUNT_ID AND MA.ACCOUNT_TYPE_ID = 2 " +
"AND DATE_FORMAT(JOURNAL_DATETIME, '%Y%m%d%H%i') >= '" + dateTimeFrom + "' " +
"AND DATE_FORMAT(JOURNAL_DATETIME, '%Y%m%d%H%i') <= '" + dateTimeTo + "'";
totalOtherTransaction = Convert.ToDouble(DS.getDataSingleValue(sqlCommand));
sqlCommand = "UPDATE CASHIER_LOG SET DATE_LOGOUT = STR_TO_DATE('" + dateLogOut + "', '%d-%m-%Y %H:%i'), AMOUNT_END = " + endAmountBox.Text + ", COMMENT = '" + remarkTextBox.Text + "', TOTAL_CASH_TRANSACTION = " + totalCashTransaction + ", TOTAL_NON_CASH_TRANSACTION = " + totalNonCashTransaction + ", TOTAL_OTHER_TRANSACTION = " + totalOtherTransaction + " WHERE ID = " + logEntryID;
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
gUtil.saveSystemDebugLog(0, "UPDATE DATA FOR CASHIER END SESSION, EA=" + endAmountBox.Text + ", TC = " + totalCashTransaction + ", TN=" + totalNonCashTransaction + ", TO=" + totalOtherTransaction);
}
DS.commit();
result = true;
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
return(result);
}
private void loadProdukData()
{
MySqlDataReader rdr;
DataTable dt = new DataTable();
string productShelves = "";
string fileName = "";
DS.mySqlConnect();
// LOAD PRODUCT DATA
using (rdr = DS.getData("SELECT * FROM MASTER_PRODUCT WHERE ID = " + selectedInternalProductID))
{
if (rdr.HasRows)
{
while (rdr.Read())
{
kodeProdukTextBox.Text = rdr.GetString("PRODUCT_ID");
barcodeTextBox.Text = rdr.GetString("PRODUCT_BARCODE");
namaProdukTextBox.Text = rdr.GetString("PRODUCT_NAME");
produkDescTextBox.Text = rdr.GetString("PRODUCT_DESCRIPTION");
hppTextBox.Text = rdr.GetString("PRODUCT_BASE_PRICE");
hargaEcerTextBox.Text = rdr.GetString("PRODUCT_RETAIL_PRICE");
hargaPartaiTextBox.Text = rdr.GetString("PRODUCT_BULK_PRICE");
hargaGrosirTextBox.Text = rdr.GetString("PRODUCT_WHOLESALE_PRICE");;
merkTextBox.Text = rdr.GetString("PRODUCT_BRAND");
stokAwalTextBox.Text = rdr.GetString("PRODUCT_STOCK_QTY");
limitStokTextBox.Text = rdr.GetString("PRODUCT_LIMIT_STOCK");
productShelves = rdr.GetString("PRODUCT_SHELVES");
noRakBarisTextBox.Text = productShelves.Substring(0, 2);
noRakKolomTextBox.Text = productShelves.Substring(2);
selectedUnitID = rdr.GetInt32("UNIT_ID");
if (rdr.GetString("PRODUCT_ACTIVE").Equals("1"))
{
nonAktifCheckbox.Checked = false;
}
else
{
nonAktifCheckbox.Checked = true;
}
if (rdr.GetString("PRODUCT_IS_SERVICE").Equals("1"))
{
produkJasaCheckbox.Checked = true;
stokAwalTextBox.Enabled = false;
limitStokTextBox.Enabled = false;
}
else
{
produkJasaCheckbox.Checked = false;
stokAwalTextBox.Enabled = true;
limitStokTextBox.Enabled = true;
}
fileName = rdr.GetString("PRODUCT_PHOTO_1").Trim();
if (!fileName.Equals(""))
{
try
{
panelImage.BackgroundImageLayout = ImageLayout.Stretch;
panelImage.BackgroundImage = Image.FromFile("PRODUCT_PHOTO/" + fileName);
selectedPhoto = "PRODUCT_PHOTO/" + fileName;
photoFileName = fileName;
}
catch (Exception ex)
{
}
}
}
}
}
}
private void loadPRData()
{
MySqlDataReader rdr;
DataTable dt = new DataTable();
string sqlCommand = "";
string dateFrom, dateTo;
string noPOInvoiceParam = "";
string whereClause1 = "";
string selectClause1;
string selectClause2;
DS.mySqlConnect();
selectClause1 = "SELECT ID, PR_INVOICE AS 'NO PENERIMAAN', DATE_FORMAT(PR_DATE, '%d-%M-%Y') AS 'TANGGAL PENERIMAAN', " +
"M.SUPPLIER_FULL_NAME AS 'ASAL', P.PR_TOTAL AS 'TOTAL', PURCHASE_INVOICE, PM_INVOICE " +
"FROM PRODUCTS_RECEIVED_HEADER P, MASTER_SUPPLIER M " +
"WHERE P.PR_FROM = M.SUPPLIER_ID ";
selectClause2 = "SELECT ID, PR_INVOICE AS 'NO PENERIMAAN', DATE_FORMAT(PR_DATE, '%d-%M-%Y') AS 'TANGGAL PENERIMAAN', " +
"'GUDANG PUSAT' AS 'ASAL', P.PR_TOTAL AS 'TOTAL', PURCHASE_INVOICE, PM_INVOICE " +
"FROM PRODUCTS_RECEIVED_HEADER P ";
if (!showAllCheckBox.Checked)
{
if (supplierID > 0)
{
sqlCommand = selectClause1;
whereClause1 = whereClause1 + " AND P.PR_FROM = " + supplierID;
}
else
{
sqlCommand = selectClause2;
whereClause1 = "WHERE 1=1";
}
if (noPOInvoiceTextBox.Text.Length > 0)
{
noPOInvoiceParam = MySqlHelper.EscapeString(noPOInvoiceTextBox.Text);
whereClause1 = whereClause1 + " AND PR_INVOICE LIKE '%" + noPOInvoiceParam + "%'";
}
dateFrom = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(PODtPicker_1.Value));
dateTo = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(PODtPicker_2.Value));
whereClause1 = whereClause1 + " AND DATE_FORMAT(PR_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(PR_DATE, '%Y%m%d') <= '" + dateTo + "'";
sqlCommand = sqlCommand + whereClause1;
}
else
{
sqlCommand = selectClause1 + " UNION " + selectClause2 + " WHERE P.PR_FROM = 0";
}
using (rdr = DS.getData(sqlCommand))
{
dataPenerimaanBarang.DataSource = null;
if (rdr.HasRows)
{
dt.Load(rdr);
dataPenerimaanBarang.DataSource = dt;
dataPenerimaanBarang.Columns["ID"].Visible = false;
dataPenerimaanBarang.Columns["NO PENERIMAAN"].Width = 200;
dataPenerimaanBarang.Columns["TANGGAL PENERIMAAN"].Width = 200;
dataPenerimaanBarang.Columns["ASAL"].Width = 200;
dataPenerimaanBarang.Columns["TOTAL"].Width = 200;
}
rdr.Close();
}
}
private void loadInvoiceData()
{
MySqlDataReader rdr;
DataTable dt = new DataTable();
string sqlCommand = "";
string dateFrom, dateTo;
string noInvoiceParam = "";
string whereClause1 = "";
string sqlClause1 = "";
string sqlClause2 = "";
DS.mySqlConnect();
sqlClause1 = "SELECT ID, SALES_INVOICE AS 'NO INVOICE', CUSTOMER_FULL_NAME AS 'CUSTOMER', DATE_FORMAT(SALES_DATE, '%d-%M-%Y') AS 'TGL INVOICE', (SALES_TOTAL - SALES_DISCOUNT_FINAL) AS 'TOTAL' " +
"FROM SALES_HEADER SH, MASTER_CUSTOMER MC " +
"WHERE SH.CUSTOMER_ID = MC.CUSTOMER_ID";
sqlClause2 = "SELECT ID, SALES_INVOICE AS 'NO INVOICE', '' AS 'CUSTOMER', DATE_FORMAT(SALES_DATE, '%d-%M-%Y') AS 'TGL INVOICE', (SALES_TOTAL - SALES_DISCOUNT_FINAL) AS 'TOTAL' " +
"FROM SALES_HEADER SH " +
"WHERE SH.CUSTOMER_ID = 0";
if (!showAllCheckBox.Checked)
{
if (noInvoiceTextBox.Text.Length > 0)
{
whereClause1 = whereClause1 + " AND SH.SALES_INVOICE LIKE '%" + noInvoiceParam + "%'";
}
dateFrom = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(PODtPicker_1.Value));
dateTo = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(PODtPicker_2.Value));
whereClause1 = whereClause1 + " AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + "'";
if (customerID > 0)
{
sqlCommand = sqlClause1 + whereClause1 + " AND AND SH.CUSTOMER_ID = " + customerID;
}
else
{
sqlCommand = sqlClause1 + whereClause1 + " UNION " + sqlClause2 + whereClause1;
}
}
else
{
sqlCommand = sqlClause1 + " UNION " + sqlClause2;
}
using (rdr = DS.getData(sqlCommand))
{
dataPenerimaanBarang.DataSource = null;
if (rdr.HasRows)
{
dt.Load(rdr);
dataPenerimaanBarang.DataSource = dt;
dataPenerimaanBarang.Columns["ID"].Visible = false;
dataPenerimaanBarang.Columns["NO INVOICE"].Width = 200;
dataPenerimaanBarang.Columns["TGL INVOICE"].Width = 200;
dataPenerimaanBarang.Columns["CUSTOMER"].Width = 200;
dataPenerimaanBarang.Columns["TOTAL"].Width = 200;
}
rdr.Close();
}
}
private bool pullDetailMessageAndSaveToTable(int moduleID, string sqlCommand)
{
MySqlDataReader rdr;
string param1;
string param2;
bool newData = false;
double jumlahPembayaran;
string deskripsiPembayaran;
string messageContent = "";
string insertSQLCommand = "";
string todayDate = String.Format(culture, "{0:dd-MM-yyyy}", DateTime.Now);
DS.beginTransaction();
try
{
DS.mySqlConnect();
using (rdr = DS.getData(sqlCommand))
{
if (rdr.HasRows)
{
newData = true;
while (rdr.Read())
{
param1 = rdr.GetString("PARAM_1");
param2 = rdr.GetString("PARAM_2");
switch (moduleID)
{
case globalConstants.MENU_TRANSAKSI_PENJUALAN:
messageContent = "SALES INVOICE [" + param1 + "] JATUH TEMPO TGL " + param2;
break;
case globalConstants.MENU_PURCHASE_ORDER:
messageContent = "PURCHASE ORDER [" + param1 + "] JATUH TEMPO TGL " + param2;
break;
case globalConstants.MENU_PEMBAYARAN_PIUTANG:
jumlahPembayaran = rdr.GetDouble("JUMLAH");
deskripsiPembayaran = rdr.GetString("DESCRIPTION");
messageContent = "PEMBAYARAN SALES INVOICE [" + param1 + "] [" + deskripsiPembayaran + "] SEBESAR " + jumlahPembayaran.ToString("C2", culture) + " JATUH TEMPO";
break;
case globalConstants.MENU_PEMBAYARAN_HUTANG_SUPPLIER:
jumlahPembayaran = rdr.GetDouble("JUMLAH");
deskripsiPembayaran = rdr.GetString("DESCRIPTION");
messageContent = "PEMBAYARAN PURCASE ORDER [" + param1 + "] [" + deskripsiPembayaran + "] SEBESAR " + jumlahPembayaran.ToString("C2", culture) + " JATUH TEMPO";
break;
case globalConstants.MENU_REQUEST_ORDER:
messageContent = "REQUEST ORDER [" + param1 + "] EXPIRED PADA TGL " + param2;
break;
case globalConstants.MENU_PRODUK:
messageContent = "PRODUCT_ID [" + param1 + "] SUDAH MENDEKATI LIMIT";
break;
}
insertSQLCommand = "INSERT INTO MASTER_MESSAGE (STATUS, MODULE_ID, IDENTIFIER_NO, MSG_DATETIME_CREATED, MSG_CONTENT) " +
"VALUES " +
"(0, " + moduleID + ", '" + param1 + "', STR_TO_DATE('" + todayDate + "', '%d-%m-%Y'), '" + messageContent + "')";
DS.executeNonQueryCommand(insertSQLCommand);
}
DS.commit();
}
}
}
catch (Exception e)
{
try
{
DS.rollBack();
}
catch (MySqlException ex)
{
if (DS.getMyTransConnection() != null)
{
gutil.showDBOPError(ex, "ROLLBACK");
}
}
gutil.showDBOPError(e, "INSERT");
}
finally
{
DS.mySqlClose();
}
return(newData);
}
private void loadPOData()
{
MySqlDataReader rdr;
DataTable dt = new DataTable();
string sqlCommand = "";
string dateFrom, dateTo;
string noPOInvoiceParam = "";
DS.mySqlConnect();
sqlCommand = "SELECT ID, PURCHASE_INVOICE AS 'NO PURCHASE', DATE_FORMAT(PURCHASE_DATETIME, '%d-%M-%Y') AS 'TANGGAL PURCHASE', " +
"IF(PURCHASE_TERM_OF_PAYMENT = 0, 'TUNAI', 'KREDIT') AS 'MODE PEMBAYARAN', " +
"DATE_FORMAT(PURCHASE_DATE_RECEIVED, '%d-%M-%Y') AS 'TANGGAL DITERIMA', " +
"DATE_FORMAT(PURCHASE_TERM_OF_PAYMENT_DATE, '%d-%M-%Y') AS 'TANGGAL JATUH TEMPO', " +
"M.SUPPLIER_FULL_NAME AS 'NAMA SUPPLIER', P.PURCHASE_TOTAL AS 'TOTAL', PURCHASE_SENT " +
"FROM PURCHASE_HEADER P, MASTER_SUPPLIER M " +
"WHERE P.SUPPLIER_ID = M.SUPPLIER_ID";
if (originModuleID == globalConstants.PENERIMAAN_BARANG_DARI_PO)
{
sqlCommand = sqlCommand + " AND PURCHASE_SENT = 1 AND PURCHASE_RECEIVED = 0";
}
else if (originModuleID == globalConstants.PEMBAYARAN_HUTANG)
{
sqlCommand = sqlCommand + " AND P.PURCHASE_PAID = 0 AND PURCHASE_SENT = 1 AND PURCHASE_RECEIVED = 1";
}
else if (originModuleID == globalConstants.REPRINT_PURCHASE_ORDER)
{
}
else
{
sqlCommand = sqlCommand + " AND PURCHASE_SENT = 0";
}
if (!showAllCheckBox.Checked)
{
if (noPOInvoiceTextBox.Text.Length > 0)
{
noPOInvoiceParam = MySqlHelper.EscapeString(noPOInvoiceTextBox.Text);
sqlCommand = sqlCommand + " AND PURCHASE_INVOICE LIKE '%" + noPOInvoiceParam + "%'";
}
dateFrom = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(PODtPicker_1.Value));
dateTo = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(PODtPicker_2.Value));
if (originModuleID == globalConstants.PEMBAYARAN_HUTANG)
{
// FILTER BY TANGGAL JATUH TEMPO
sqlCommand = sqlCommand + " AND DATE_FORMAT(PURCHASE_TERM_OF_PAYMENT_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(PURCHASE_TERM_OF_PAYMENT_DATE, '%Y%m%d') <= '" + dateTo + "'";
}
else
{
sqlCommand = sqlCommand + " AND DATE_FORMAT(PURCHASE_DATETIME, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(PURCHASE_DATETIME, '%Y%m%d') <= '" + dateTo + "'";
}
if (supplierCombo.Text.Length > 0)
{
sqlCommand = sqlCommand + " AND P.SUPPLIER_ID = " + supplierID;
}
}
using (rdr = DS.getData(sqlCommand))
{
dataPurchaseOrder.DataSource = null;
if (rdr.HasRows)
{
dt.Load(rdr);
dataPurchaseOrder.DataSource = dt;
dataPurchaseOrder.Columns["ID"].Visible = false;
dataPurchaseOrder.Columns["PURCHASE_SENT"].Visible = false;
if (originModuleID == globalConstants.PEMBAYARAN_HUTANG)
{
dataPurchaseOrder.Columns["TANGGAL DITERIMA"].Visible = false;
}
dataPurchaseOrder.Columns["NO PURCHASE"].Width = 200;
dataPurchaseOrder.Columns["TANGGAL PURCHASE"].Width = 200;
dataPurchaseOrder.Columns["TANGGAL DITERIMA"].Width = 200;
dataPurchaseOrder.Columns["TANGGAL JATUH TEMPO"].Width = 200;
dataPurchaseOrder.Columns["MODE PEMBAYARAN"].Width = 200;
dataPurchaseOrder.Columns["NAMA SUPPLIER"].Width = 200;
dataPurchaseOrder.Columns["TOTAL"].Width = 200;
}
rdr.Close();
}
}
