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(); } }