private bool saveDataTransaction() { bool result = false; string sqlCommand = ""; MySqlException internalEX = null; double unitConversion = getConvertValue(); DS.beginTransaction(); try { DS.mySqlConnect(); switch (currentMode) { case NEW_CONVERSION: sqlCommand = "INSERT INTO UNIT_CONVERT (CONVERT_UNIT_ID_1, CONVERT_UNIT_ID_2, CONVERT_MULTIPLIER) VALUES (" + selectedUnit1_ID + ", " + selectedUnit2_ID + ", " + unitConversion + ")"; gUtil.saveSystemDebugLog(globalConstants.MENU_SATUAN, "ADD NEW UNIT CONVERT [" + selectedUnit1_ID + "/" + selectedUnit2_ID + "/" + unitConversion + "]"); break; case EDIT_CONVERSION: sqlCommand = "UPDATE UNIT_CONVERT SET CONVERT_MULTIPLIER = " + unitConversion + " WHERE CONVERT_UNIT_ID_1 = " + selectedUnit1_ID + " AND CONVERT_UNIT_ID_2 = " + selectedUnit2_ID; gUtil.saveSystemDebugLog(globalConstants.MENU_SATUAN, "UPDATE UNIT CONVERT [" + selectedUnit1_ID + "/" + selectedUnit2_ID + "/" + unitConversion + "]"); break; } if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } DS.commit(); result = true; } catch (Exception e) { gUtil.saveSystemDebugLog(globalConstants.MENU_SATUAN, "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 loadImportedDataRO(string filePath) { bool result = false; string sqlCommand = ""; string roInvoice = ""; DS.beginTransaction(); try { DS.mySqlConnect(); System.IO.StreamReader file = new System.IO.StreamReader(filePath); roInvoice = file.ReadLine(); if (!noROExist(roInvoice)) { while ((sqlCommand = file.ReadLine()) != null) { DS.executeNonQueryCommand(sqlCommand); } file.Close(); DS.commit(); } else { MessageBox.Show("NOMOR REQUEST ORDER SUDAH ADA"); } } catch (Exception e) { try { //myTrans.Rollback(); } catch (MySqlException ex) { if (DS.getMyTransConnection() != null) { MessageBox.Show("An exception of type " + ex.GetType() + " was encountered while attempting to roll back the transaction."); } } MessageBox.Show("An exception of type " + e.GetType() + " was encountered while inserting the data."); MessageBox.Show("Neither record was written to database."); } finally { DS.mySqlClose(); result = true; } return(result); }
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 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); }
public void saveUserChangeLog(int moduleID, int changeID, string changeDescription) { string sqlCommand = ""; string dateTimeNow = String.Format(culture, "{0:dd-M-yyyy HH:mm}", DateTime.Now); DS.beginTransaction(); try { sqlCommand = "INSERT INTO USER_CHANGE_LOG (USER_ID, MODULE_ID, CHANGE_ID, CHANGE_DATETIME, CHANGE_DESCRIPTION) VALUES (" + getUserID() + ", " +moduleID + ", " + changeID + ", STR_TO_DATE('" + dateTimeNow + "', '%d-%m-%Y %H:%i'), '" + changeDescription + "')"; DS.executeNonQueryCommand(sqlCommand); DS.commit(); } catch(Exception ex) {} }
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 bool saveData() { bool result = false; string sqlCommand = ""; MySqlException internalEX = null; int i = 0; int moduleIdValue = 0; int tempModuleID = 0; int userAccessValue = 0; DS.beginTransaction(); try { DS.mySqlConnect(); i = 0; while (i < groupAccessDataGridView.Rows.Count) { tempModuleID = Convert.ToInt32(groupAccessDataGridView.Rows[i].Cells["moduleID"].Value); if (moduleIdValue != tempModuleID) { if (moduleIdValue != 0) { if (Convert.ToInt32(DS.getDataSingleValue("SELECT COUNT(1) FROM USER_ACCESS_MANAGEMENT WHERE MODULE_ID = " + moduleIdValue + " AND GROUP_ID = " + selectedGroupID)) == 0) { // INSERT MODE sqlCommand = "INSERT INTO USER_ACCESS_MANAGEMENT (GROUP_ID, MODULE_ID, USER_ACCESS_OPTION) VALUES (" + selectedGroupID + ", " + moduleIdValue + ", " + userAccessValue + ")"; if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } } else { // EDIT MODE sqlCommand = "UPDATE USER_ACCESS_MANAGEMENT SET USER_ACCESS_OPTION = " + userAccessValue + " WHERE GROUP_ID = " + selectedGroupID + " AND MODULE_ID = " + moduleIdValue; if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } } } moduleIdValue = tempModuleID; userAccessValue = 0; } if (Convert.ToBoolean(groupAccessDataGridView.Rows[i].Cells["hakAkses"].Value)) { userAccessValue = userAccessValue + Convert.ToInt32(groupAccessDataGridView.Rows[i].Cells["featureID"].Value); } i++; } // INSERT / UPDATE if (Convert.ToInt32(DS.getDataSingleValue("SELECT COUNT(1) FROM USER_ACCESS_MANAGEMENT WHERE MODULE_ID = " + moduleIdValue + " AND GROUP_ID = " + selectedGroupID)) == 0) { // INSERT MODE sqlCommand = "INSERT INTO USER_ACCESS_MANAGEMENT (GROUP_ID, MODULE_ID, USER_ACCESS_OPTION) VALUES (" + selectedGroupID + ", " + moduleIdValue + ", " + userAccessValue + ")"; if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } } else { // EDIT MODE sqlCommand = "UPDATE USER_ACCESS_MANAGEMENT SET USER_ACCESS_OPTION = " + userAccessValue + " WHERE GROUP_ID = " + selectedGroupID + " AND MODULE_ID = " + moduleIdValue; if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } } //DS.executeNonQueryCommand(sqlCommand); 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 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 bool saveDataTransaction(int mode) { bool result = false; string sqlCommand = ""; MySqlException internalEX = null; string HQIP = HQIP1.Text.Trim() + "." + HQIP2.Text.Trim() + "." + HQIP3.Text.Trim() + "." + HQIP4.Text.Trim();; String branchID = BranchIDTextbox.Text; //String no_faktur = ""; String nama_toko = MySqlHelper.EscapeString(NamaTokoTextbox.Text); String alamat_toko = MySqlHelper.EscapeString(AlamatTextbox.Text); String telepon_toko = MySqlHelper.EscapeString(TeleponTextbox.Text); String email_toko = MySqlHelper.EscapeString(EmailTextbox.Text); int id = 2; DS.beginTransaction(); try { DS.mySqlConnect(); switch (mode) { case 1: sqlCommand = "INSERT INTO SYS_CONFIG (ID, NO_FAKTUR, BRANCH_ID, HQ_IP4, STORE_NAME, STORE_ADDRESS, STORE_PHONE, STORE_EMAIL) " + "VALUES (2, '', '" + branchID + "', '" + HQIP + "', '" + nama_toko + "', '" + alamat_toko + "', '" + telepon_toko + "', '" + email_toko + "')"; options = gutil.INS; gutil.saveSystemDebugLog(0, "INSERT DATA ID 2 TO SYS_CONFIG"); break; case 2: sqlCommand = "UPDATE SYS_CONFIG SET " + "BRANCH_ID = " + branchID + ", " + "HQ_IP4 = '" + HQIP + "', " + "STORE_NAME = '" + nama_toko + "', " + "STORE_ADDRESS = '" + alamat_toko + "', " + "STORE_PHONE = '" + telepon_toko + "', " + "STORE_EMAIL = '" + email_toko + "' " + "WHERE ID = " + id; options = gutil.UPD; gutil.saveSystemDebugLog(0, "UPDATE DATA ID 2"); break; } if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } DS.commit(); result = true; } catch (Exception e) { gutil.saveSystemDebugLog(0, "UPDATE / INSERT FAILED"); 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 saveDataTransaction() { bool result = false; string sqlCommand = ""; MySqlException internalEX = null; string selectedDate = dateJoinedDateTimePicked.Value.ToShortDateString(); string custJoinedDate = String.Format(culture, "{0:dd-MM-yyyy}", Convert.ToDateTime(selectedDate)); string custName = MySqlHelper.EscapeString(custNameTextBox.Text.Trim()); string custAddress1 = custAddress1TextBox.Text.Trim(); if (custAddress1.Equals("")) { custAddress1 = " "; } else { custAddress1 = MySqlHelper.EscapeString(custAddress1); } string custAddress2 = custAddress2TextBox.Text.Trim(); if (custAddress2.Equals("")) { custAddress2 = " "; } else { custAddress2 = MySqlHelper.EscapeString(custAddress2); } string custAddressCity = custAddressCityTextBox.Text.Trim(); if (custAddressCity.Equals("")) { custAddressCity = " "; } else { custAddressCity = MySqlHelper.EscapeString(custAddressCity); } string custPhone = custTelTextBox.Text.Trim(); if (custPhone.Equals("")) { custPhone = " "; } else { custPhone = MySqlHelper.EscapeString(custPhone); } string custFax = custFaxTextBox.Text.Trim(); if (custFax.Equals("")) { custFax = " "; } else { custFax = MySqlHelper.EscapeString(custFax); } string custEmail = custEmailTextBox.Text.Trim(); if (custEmail.Equals("")) { custEmail = " "; } else { custEmail = MySqlHelper.EscapeString(custEmail); } string custTotalSales = custTotalSalesTextBox.Text.Trim(); if (custTotalSales.Equals("")) { custTotalSales = "0"; } int custGroup = groupPelangganCombo.SelectedIndex + 1; byte custStatus = 0; if (nonAktifCheckbox.Checked) { custStatus = 0; } else { custStatus = 1; } DS.beginTransaction(); try { DS.mySqlConnect(); switch (originModuleID) { case globalConstants.NEW_CUSTOMER: sqlCommand = "INSERT INTO MASTER_CUSTOMER " + "(CUSTOMER_FULL_NAME, CUSTOMER_ADDRESS1, CUSTOMER_ADDRESS2, CUSTOMER_ADDRESS_CITY, CUSTOMER_PHONE, CUSTOMER_FAX, CUSTOMER_EMAIL, CUSTOMER_ACTIVE, CUSTOMER_JOINED_DATE, CUSTOMER_TOTAL_SALES_COUNT, CUSTOMER_GROUP) " + "VALUES ('" + custName + "', '" + custAddress1 + "', '" + custAddress2 + "', '" + custAddressCity + "', '" + custPhone + "', '" + custFax + "', '" + custEmail + "', " + custStatus + ", STR_TO_DATE('" + custJoinedDate + "', '%d-%m-%Y'), " + custTotalSales + ", " + custGroup + ")"; gUtil.saveSystemDebugLog(globalConstants.MENU_PELANGGAN, "INSERT NEW CUSTOMER DATA [" + custName + "]"); break; case globalConstants.EDIT_CUSTOMER: sqlCommand = "UPDATE MASTER_CUSTOMER SET " + "CUSTOMER_FULL_NAME = '" + custName + "', " + "CUSTOMER_ADDRESS1 = '" + custAddress1 + "', " + "CUSTOMER_ADDRESS2 = '" + custAddress2 + "', " + "CUSTOMER_ADDRESS_CITY = '" + custAddressCity + "', " + "CUSTOMER_PHONE = '" + custPhone + "', " + "CUSTOMER_FAX = '" + custFax + "', " + "CUSTOMER_EMAIL = '" + custEmail + "', " + "CUSTOMER_ACTIVE = " + custStatus + ", " + "CUSTOMER_JOINED_DATE = STR_TO_DATE('" + custJoinedDate + "', '%d-%m-%Y'), " + "CUSTOMER_TOTAL_SALES_COUNT = " + custTotalSales + ", " + "CUSTOMER_GROUP = " + custGroup + " " + "WHERE CUSTOMER_ID = " + selectedCustomerID; gUtil.saveSystemDebugLog(globalConstants.MENU_PELANGGAN, "EDIT CUSTOMER DATA [" + selectedCustomerID + "]"); break; } if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } DS.commit(); result = true; } catch (Exception e) { gUtil.saveSystemDebugLog(globalConstants.MENU_PELANGGAN, "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 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); }
private bool saveDataTransaction() { bool result = false; string sqlCommand = ""; MySqlException internalEX = null; double calculatedResult = 0; double actualResult = 0; double productLoss = 0; string selectedDate = DateTime.Now.ToString(); string pl_Date = String.Format(culture, "{0:dd-MM-yyyy}", Convert.ToDateTime(selectedDate)); DS.beginTransaction(); calculatedResult = Convert.ToDouble(resultTextBox.Text); actualResult = Convert.ToDouble(actualQtyTextBox.Text); productLoss = calculatedResult - actualResult; try { DS.mySqlConnect(); //REDUCE CURRENT STOCK QTY sqlCommand = "UPDATE MASTER_PRODUCT SET PRODUCT_STOCK_QTY = PRODUCT_STOCK_QTY - " + gUtil.validateDecimalNumericInput(Convert.ToDouble(numberOfProductTextBox.Text)) + " WHERE ID = " + selectedInternalProductID; gUtil.saveSystemDebugLog(globalConstants.MENU_PECAH_SATUAN_PRODUK, "REDUCE QTY [" + productIDTextBox.Text + "] AMT [" + gUtil.validateDecimalNumericInput(Convert.ToDouble(numberOfProductTextBox.Text)) + "]"); if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } //INCREASE NEW STOCK QTY sqlCommand = "UPDATE MASTER_PRODUCT SET PRODUCT_STOCK_QTY = PRODUCT_STOCK_QTY + " + actualResult + " WHERE ID = " + newSelectedInternalProductID; gUtil.saveSystemDebugLog(globalConstants.MENU_PECAH_SATUAN_PRODUK, "ADD QTY [" + newProductIDTextBox.Text + "] AMT [" + actualResult + "]"); if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } if (actualResult < calculatedResult) { // INSERT INTO PRODUCT LOSS TABLE sqlCommand = "INSERT INTO PRODUCT_LOSS (PL_DATETIME, PRODUCT_ID, PRODUCT_QTY, NEW_PRODUCT_ID, NEW_PRODUCT_QTY, TOTAL_LOSS) " + "VALUES (STR_TO_DATE('" + pl_Date + "', '%d-%m-%Y'), " + selectedInternalProductID + ", " + Convert.ToDouble(numberOfProductTextBox.Text) + ", " + newSelectedInternalProductID + ", " + gUtil.validateDecimalNumericInput(Convert.ToDouble(resultTextBox.Text)) + ", " + gUtil.validateDecimalNumericInput(productLoss) + ")"; gUtil.saveSystemDebugLog(globalConstants.MENU_PECAH_SATUAN_PRODUK, "ADD PRODUCT LOSS QTY [" + productIDTextBox.Text + "] AMT [" + gUtil.validateDecimalNumericInput(productLoss) + "]"); if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } } DS.commit(); result = true; } catch (Exception e) { gUtil.saveSystemDebugLog(globalConstants.MENU_PECAH_SATUAN_PRODUK, "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 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 bool saveDataTransaction() { bool result = false; string sqlCommand = ""; MySqlException internalEX = null; string groupName = MySqlHelper.EscapeString(namaGroupTextBox.Text.Trim()); string groupDesc = MySqlHelper.EscapeString(deskripsiTextBox.Text.Trim()); byte groupStatus = 0; byte groupCashier = 0; if (nonAktifCheckbox.Checked) { groupStatus = 0; } else { groupStatus = 1; } if (cashierCheckBox.Checked) { groupCashier = 1; } else { groupCashier = 0; } DS.beginTransaction(); try { DS.mySqlConnect(); switch (originModuleID) { case globalConstants.NEW_GROUP_USER: case globalConstants.PENGATURAN_GRUP_AKSES: sqlCommand = "INSERT INTO MASTER_GROUP (GROUP_USER_NAME, GROUP_USER_DESCRIPTION, GROUP_USER_ACTIVE, GROUP_IS_CASHIER) VALUES ('" + groupName + "', '" + groupDesc + "', " + groupStatus + ", " + groupCashier + ")"; break; case globalConstants.EDIT_GROUP_USER: sqlCommand = "UPDATE MASTER_GROUP SET GROUP_USER_NAME = '" + groupName + "', GROUP_USER_DESCRIPTION = '" + groupDesc + "', GROUP_USER_ACTIVE = " + groupStatus + ", GROUP_IS_CASHIER = " + groupCashier + " WHERE GROUP_ID = " + selectedGroupID; break; } 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 { if (originModuleID == globalConstants.PENGATURAN_GRUP_AKSES && result == true) { selectedGroupID = Convert.ToInt32(DS.getDataSingleValue("SELECT MAX(GROUP_ID) FROM MASTER_GROUP WHERE GROUP_USER_NAME = '" + groupName + "' AND GROUP_USER_DESCRIPTION = '" + groupDesc + "' ")); parentForm.setSelectedGroupID(selectedGroupID); } DS.mySqlClose(); } return(result); }
private bool saveDataTransaction() { bool result = false; string sqlCommand = ""; MySqlException internalEX = null; string kodeakun = kodeTextbox.Text.Trim(); string deskripsiakun = MySqlHelper.EscapeString(DeskripsiTextbox.Text.Trim()); int tipeakun = Int32.Parse(TipeComboBox.SelectedValue.ToString()); int nonactive = 1; if (NonactiveCheckbox.Checked == true) { nonactive = 0; } DS.beginTransaction(); try { DS.mySqlConnect(); switch (originModuleID) { case globalConstants.NEW_AKUN: sqlCommand = "INSERT INTO MASTER_ACCOUNT (ACCOUNT_ID, ACCOUNT_NAME, ACCOUNT_TYPE_ID, ACCOUNT_ACTIVE) " + "VALUES ('" + kodeakun + "', '" + deskripsiakun + "', '" + tipeakun + "', " + nonactive + ")"; gUtil.saveSystemDebugLog(globalConstants.MENU_PENGATURAN_NO_AKUN, "INSERT DATA TO MASTER ACCOUNT [" + kodeakun + "]"); break; case globalConstants.EDIT_AKUN: sqlCommand = "UPDATE MASTER_ACCOUNT SET " + "ACCOUNT_ID = '" + kodeakun + "', " + "ACCOUNT_NAME = '" + deskripsiakun + "', " + "ACCOUNT_TYPE_ID = '" + tipeakun + "', " + "ACCOUNT_ACTIVE = '" + nonactive + "' " + "WHERE ID = '" + selectedAccountID + "'"; gUtil.saveSystemDebugLog(globalConstants.MENU_PENGATURAN_NO_AKUN, "UPDATE DATA ON MASTER ACCOUNT [" + selectedAccountID + "]"); break; } if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } DS.commit(); result = true; } catch (Exception e) { gUtil.saveSystemDebugLog(globalConstants.MENU_PENGATURAN_NO_AKUN, "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 saveDataTransaction() { bool result = false; string sqlCommand = ""; MySqlException internalEX = null; string branchName = MySqlHelper.EscapeString(branchNameTextBox.Text.Trim()); string branchIPv4 = ip1Textbox.Text.Trim() + "." + ip2Textbox.Text.Trim() + "." + ip3Textbox.Text.Trim() + "." + ip4Textbox.Text.Trim(); string branchAddress1 = MySqlHelper.EscapeString(branchAddress1TextBox.Text.Trim()); string branchAddress2 = MySqlHelper.EscapeString(branchAddress2TextBox.Text.Trim()); string branchAddressCity = MySqlHelper.EscapeString(branchAddressCityTextBox.Text.Trim()); string branchPhone = MySqlHelper.EscapeString(branchTelephoneTextBox.Text.Trim()); byte branchStatus = 0; if (nonAktifCheckbox.Checked) { branchStatus = 0; } else { branchStatus = 1; } DS.beginTransaction(); try { DS.mySqlConnect(); switch (originModuleID) { case globalConstants.NEW_BRANCH: sqlCommand = "INSERT INTO MASTER_BRANCH (BRANCH_NAME, BRANCH_ADDRESS_1, BRANCH_ADDRESS_2, BRANCH_ADDRESS_CITY, BRANCH_TELEPHONE, BRANCH_IP4, BRANCH_ACTIVE) " + "VALUES ('" + branchName + "', '" + branchAddress1 + "', '" + branchAddress2 + "', '" + branchAddressCity + "', '" + branchPhone + "', '" + branchIPv4 + "', " + branchStatus + ")"; break; case globalConstants.EDIT_BRANCH: sqlCommand = "UPDATE MASTER_BRANCH SET " + "BRANCH_NAME = '" + branchName + "', " + "BRANCH_ADDRESS_1 = '" + branchAddress1 + "', " + "BRANCH_ADDRESS_2 = '" + branchAddress2 + "', " + "BRANCH_ADDRESS_CITY = '" + branchAddressCity + "', " + "BRANCH_TELEPHONE = '" + branchPhone + "', " + "BRANCH_IP4 = '" + branchIPv4 + "', " + "BRANCH_ACTIVE = '" + branchStatus + "' " + "WHERE BRANCH_ID = '" + selectedBranchID + "'"; break; } 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 CariButton_Click(object sender, EventArgs e) { string dateFrom, dateTo; dateFrom = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(datefromPicker.Value)); dateTo = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(datetoPicker.Value)); DS.mySqlConnect(); string sqlCommandx = ""; switch (originModuleID) { case globalConstants.REPORT_FINANCE_IN: sqlCommandx = "SELECT DJ.JOURNAL_DATETIME AS 'TGL', MA.ACCOUNT_NAME AS 'AKUN', DJ.JOURNAL_NOMINAL AS 'JML', DJ.JOURNAL_DESCRIPTION AS 'DESKRIPSI' " + "FROM DAILY_JOURNAL DJ, MASTER_ACCOUNT MA " + "WHERE DJ.ACCOUNT_ID = MA.ACCOUNT_ID AND MA.ACCOUNT_TYPE_ID = 1 AND DJ.BRANCH_ID = 0 " + "AND DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d') <= '" + dateTo + "'"; DS.writeXML(sqlCommandx, globalConstants.FinanceInXML); ReportCashierLogForm displayedForm1 = new ReportCashierLogForm(); displayedForm1.ShowDialog(this); break; case globalConstants.REPORT_FINANCE_OUT: sqlCommandx = "SELECT DJ.JOURNAL_DATETIME AS 'TGL', MA.ACCOUNT_NAME AS 'AKUN', DJ.JOURNAL_NOMINAL AS 'JML', DJ.JOURNAL_DESCRIPTION AS 'DESKRIPSI' " + "FROM DAILY_JOURNAL DJ, MASTER_ACCOUNT MA " + "WHERE DJ.ACCOUNT_ID = MA.ACCOUNT_ID AND MA.ACCOUNT_TYPE_ID = 2 AND DJ.BRANCH_ID = 0 " + "AND DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d') <= '" + dateTo + "'"; DS.writeXML(sqlCommandx, globalConstants.FinanceOutXML); ReportFinanceOutForm displayedForm2 = new ReportFinanceOutForm(); displayedForm2.ShowDialog(this); break; case globalConstants.REPORT_MARGIN: sqlCommandx = "SELECT DATE(SH.SALES_DATE) AS 'TGL', SUM((SD.SALES_SUBTOTAL-(SD.PRODUCT_QTY*SD.PRODUCT_PRICE))) AS 'MARGIN' " + "FROM SALES_HEADER SH, SALES_DETAIL SD " + "WHERE SH.SALES_PAID = 1 AND SH.SALES_INVOICE = SD.SALES_INVOICE " + "AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + "' " + "GROUP BY DATE(SH.SALES_DATE)"; DS.writeXML(sqlCommandx, globalConstants.MarginXML); ReportMarginForm displayedForm3 = new ReportMarginForm(); displayedForm3.ShowDialog(this); break; case globalConstants.REPORT_MONTHLY_BALANCE: int days = DateTime.DaysInMonth(Int32.Parse(MonthPicker.Value.ToString("yyyy")), Int32.Parse(MonthPicker.Value.ToString("MM"))); string monthname = MonthPicker.Value.ToString("MMMM"); DS.beginTransaction(); try { DS.mySqlConnect(); sqlCommandx = "DROP TABLE `daysmonth`"; DS.executeNonQueryCommand(sqlCommandx); sqlCommandx = "CREATE TABLE `daysmonth` (" + "`TGL` tinyint(4) unsigned NOT NULL," + "PRIMARY KEY (`TGL`)" + ") ENGINE = InnoDB DEFAULT CHARSET = utf8"; DS.executeNonQueryCommand(sqlCommandx); for (int i = 1; i <= days; i++) { sqlCommandx = "INSERT INTO `daysmonth` (`TGL`) VALUES (" + i + ")"; DS.executeNonQueryCommand(sqlCommandx); } DS.commit(); } catch (Exception excp) { try { //myTrans.Rollback(); } catch (MySqlException ex) { if (DS.getMyTransConnection() != null) { MessageBox.Show("An exception of type " + ex.GetType() + " was encountered while attempting to roll back the transaction."); } } MessageBox.Show("An exception of type " + e.GetType() + " was encountered while inserting the data."); MessageBox.Show("Neither record was written to database."); } finally { DS.mySqlClose(); } sqlCommandx = "SELECT tab1.TGL,tab1.DEBET,IF(tab2.KREDIT IS NULL,0,tab2.KREDIT) AS 'KREDIT' from " + "(SELECT tab1.TGL, IF(tab1.DEBET IS NULL, 0, tab1.DEBET) + IF(tab2.debet IS NULL, 0, tab2.DEBET) as 'DEBET' FROM " + "(SELECT TAB1.TGL, TAB2.DEBET from(SELECT TGL from daysmonth) tab1 left outer join(SELECT DATE_FORMAT(DJ.JOURNAL_DATETIME, '%d') AS 'TGL', " + "DJ.JOURNAL_NOMINAL AS 'DEBET' FROM DAILY_JOURNAL DJ, MASTER_ACCOUNT MA " + "WHERE DJ.ACCOUNT_ID = MA.ACCOUNT_ID AND MA.ACCOUNT_TYPE_ID = 1 AND DJ.BRANCH_ID = 0 " + "AND DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d') <= '" + dateTo + "') tab2 " + "on tab1.TGL = tab2.TGL) tab1 " + "left outer join " + "(SELECT DATE_FORMAT(SH.SALES_DATE, '%d') AS 'TGL', IF(SUM((SD.SALES_SUBTOTAL - (SD.PRODUCT_QTY * SD.PRODUCT_PRICE))) IS NULL, 0, SUM((SD.SALES_SUBTOTAL - (SD.PRODUCT_QTY * SD.PRODUCT_PRICE)))) AS 'DEBET' " + "FROM SALES_HEADER SH, SALES_DETAIL SD " + "WHERE SH.SALES_PAID = 1 AND SH.SALES_INVOICE = SD.SALES_INVOICE " + "AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + "' " + "GROUP BY DATE(SH.SALES_DATE)) tab2 " + "on tab1.TGL = tab2.TGL) tab1 " + "left outer join " + "(SELECT DATE_FORMAT(DJ.JOURNAL_DATETIME, '%d') AS 'TGL', IF(SUM(DJ.JOURNAL_NOMINAL) IS NULL, 0, -SUM(DJ.JOURNAL_NOMINAL)) AS 'KREDIT' " + "FROM DAILY_JOURNAL DJ, MASTER_ACCOUNT MA " + "WHERE DJ.ACCOUNT_ID = MA.ACCOUNT_ID AND MA.ACCOUNT_TYPE_ID = 2 AND DJ.BRANCH_ID = 0 " + "AND DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d') <= '" + dateTo + "' " + "GROUP BY DATE(DJ.JOURNAL_DATETIME)) tab2 " + "on tab1.TGL = tab2.TGL"; DS.writeXML(sqlCommandx, globalConstants.MonthlyBalanceXML); ReportMonthlyBalanceForm displayedForm4 = new ReportMonthlyBalanceForm(monthname); displayedForm4.ShowDialog(this); break; } }
private bool saveDataTransaction() { bool result = false; string sqlCommand = ""; string noRakBaris = ""; string noRakKolom = ""; MySqlException internalEX = null; productID = MySqlHelper.EscapeString(kodeProdukTextBox.Text); string produkBarcode = barcodeTextBox.Text; if (produkBarcode.Equals("")) { produkBarcode = "0"; } string produkName = MySqlHelper.EscapeString(namaProdukTextBox.Text.Trim()); string produkDesc = produkDescTextBox.Text.Trim(); if (produkDesc.Equals("")) { produkDesc = " "; } else { produkDesc = MySqlHelper.EscapeString(produkDesc); } string produkHargaPokok = hppTextBox.Text; string produkHargaEcer = hargaEcerTextBox.Text; string produkHargaPartai = hargaPartaiTextBox.Text; string produkHargaGrosir = hargaGrosirTextBox.Text; string produkBrand = merkTextBox.Text.Trim(); if (produkBrand.Equals("")) { produkBrand = " "; } else { produkBrand = MySqlHelper.EscapeString(produkBrand); } string produkQty = stokAwalTextBox.Text; if (produkQty.Equals("")) { produkQty = "0"; } string limitStock = limitStokTextBox.Text; if (limitStock.Equals("")) { limitStock = "0"; } noRakBaris = MySqlHelper.EscapeString(noRakBarisTextBox.Text); noRakKolom = noRakKolomTextBox.Text; while (noRakBaris.Length < 2) { noRakBaris = "-" + noRakBaris; } while (noRakKolom.Length < 2) { noRakKolom = "0" + noRakKolom; } string produkShelves = noRakBaris + noRakKolom; byte produkSvc = 0; if (produkJasaCheckbox.Checked) { produkSvc = 1; } else { produkSvc = 0; } byte produkStatus = 0; if (nonAktifCheckbox.Checked) { produkStatus = 0; } else { produkStatus = 1; } string produkPhoto = " "; if (!selectedPhoto.Equals("")) { produkPhoto = productID + ".jpg"; } DS.beginTransaction(); try { DS.mySqlConnect(); switch (originModuleID) { case globalConstants.EDIT_PRODUK: // UPDATE MASTER_PRODUK TABLE sqlCommand = "UPDATE MASTER_PRODUCT SET " + "PRODUCT_BARCODE = '" + produkBarcode + "', " + "PRODUCT_NAME = '" + produkName + "', " + "PRODUCT_DESCRIPTION = '" + produkDesc + "', " + "PRODUCT_BASE_PRICE = " + produkHargaPokok + ", " + "PRODUCT_RETAIL_PRICE = " + produkHargaEcer + ", " + "PRODUCT_BULK_PRICE = " + produkHargaPartai + ", " + "PRODUCT_WHOLESALE_PRICE = " + produkHargaGrosir + ", " + "PRODUCT_PHOTO_1 = '" + produkPhoto + "', " + "UNIT_ID = " + selectedUnitID + ", " + "PRODUCT_STOCK_QTY = " + produkQty + ", " + "PRODUCT_LIMIT_STOCK = " + limitStock + ", " + "PRODUCT_SHELVES = '" + produkShelves + "', " + "PRODUCT_ACTIVE = " + produkStatus + ", " + "PRODUCT_BRAND = '" + produkBrand + "', " + "PRODUCT_IS_SERVICE = " + produkSvc + " " + "WHERE PRODUCT_ID = '" + productID + "'"; gUtil.saveSystemDebugLog(globalConstants.MENU_TAMBAH_PRODUK, "UPDATE CURRENT PRODUCT DATA [" + productID + "]"); if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } // UPDATE PRODUCT_CATEGORY TABLE gUtil.saveSystemDebugLog(globalConstants.MENU_TAMBAH_PRODUK, "UPDATE PRODUCT CATEGORY FOR [" + productID + "]"); // delete the content first, and insert the new data based on the currentSelectedKategoryID LIST sqlCommand = "DELETE FROM PRODUCT_CATEGORY WHERE PRODUCT_ID = '" + productID + "'"; gUtil.saveSystemDebugLog(globalConstants.MENU_TAMBAH_PRODUK, "DELETE CURRENT PRODUCT CATEGORY FOR [" + productID + "]"); if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } // SAVE TO PRODUCT_CATEGORY TABLE gUtil.saveSystemDebugLog(globalConstants.MENU_TAMBAH_PRODUK, "INSERT PRODUCT CATEGORY FOR [" + productID + "]"); for (int i = 0; i < currentSelectedKategoriID.Count(); i++) { sqlCommand = "INSERT INTO PRODUCT_CATEGORY (PRODUCT_ID, CATEGORY_ID) VALUES ('" + productID + "', " + currentSelectedKategoriID[i] + ")"; if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } } break; default: // NEW PRODUK // SAVE TO MASTER_PRODUK TABLE 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, PRODUCT_PHOTO_1, UNIT_ID, PRODUCT_STOCK_QTY, PRODUCT_LIMIT_STOCK, PRODUCT_SHELVES, PRODUCT_ACTIVE, PRODUCT_BRAND, PRODUCT_IS_SERVICE) " + "VALUES " + "('" + productID + "', '" + produkBarcode + "', '" + produkName + "', '" + produkDesc + "', " + produkHargaPokok + ", " + produkHargaEcer + ", " + produkHargaPartai + ", " + produkHargaGrosir + ", '" + produkPhoto + "', " + selectedUnitID + ", " + produkQty + ", " + limitStock + ", '" + produkShelves + "', " + produkStatus + ", '" + produkBrand + "', " + produkSvc + ")"; gUtil.saveSystemDebugLog(globalConstants.MENU_TAMBAH_PRODUK, "INSERT NEW PRODUCT [" + productID + "]"); if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } // SAVE TO PRODUCT_CATEGORY TABLE gUtil.saveSystemDebugLog(globalConstants.MENU_TAMBAH_PRODUK, "INSERT PRODUCT CATEGORY FOR [" + productID + "]"); for (int i = 0; i < currentSelectedKategoriID.Count(); i++) { sqlCommand = "INSERT INTO PRODUCT_CATEGORY (PRODUCT_ID, CATEGORY_ID) VALUES ('" + productID + "', " + currentSelectedKategoriID[i] + ")"; if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } } break; } if (!selectedPhoto.Equals("PRODUCT_PHOTO/" + produkPhoto) && !selectedPhoto.Equals(""))// && result == true) { gUtil.saveSystemDebugLog(globalConstants.MENU_TAMBAH_PRODUK, "SET PRODUCT IMAGE [" + selectedPhoto + "]"); panelImage.BackgroundImage = null; System.IO.File.Copy(selectedPhoto, "PRODUCT_PHOTO/" + produkPhoto + "_temp"); gUtil.saveSystemDebugLog(globalConstants.MENU_TAMBAH_PRODUK, "COPY SELECTED IMAGE TO PRODUCT_PHOTO FOLDER"); if (System.IO.File.Exists("PRODUCT_PHOTO/" + produkPhoto)) { gUtil.saveSystemDebugLog(globalConstants.MENU_TAMBAH_PRODUK, "DELETE CURRENT PRODUCT IMAGE [" + productID + "]"); System.GC.Collect(); System.GC.WaitForPendingFinalizers(); System.IO.File.Delete("PRODUCT_PHOTO/" + produkPhoto); } System.IO.File.Move("PRODUCT_PHOTO/" + produkPhoto + "_temp", "PRODUCT_PHOTO/" + produkPhoto); panelImage.BackgroundImage = Image.FromFile("PRODUCT_PHOTO/" + produkPhoto); gUtil.saveSystemDebugLog(globalConstants.MENU_TAMBAH_PRODUK, "RENAME AND SET NEW PRODUCT IMAGE"); } DS.commit(); result = true; } catch (Exception e) { gUtil.saveSystemDebugLog(globalConstants.MENU_TAMBAH_PRODUK, "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 kodeRakValue; string nomorRakValue; MySqlException internalEX = null; DS.beginTransaction(); try { DS.mySqlConnect(); switch (originModuleID) { case globalConstants.PENGATURAN_HARGA_JUAL: for (int i = 0; i < dataProdukDataGridView.Rows.Count; i++) { if (Convert.ToBoolean(dataProdukDataGridView.Rows[i].Cells["CHANGED"].Value)) { sqlCommand = "UPDATE MASTER_PRODUCT SET " + "PRODUCT_RETAIL_PRICE = " + Convert.ToInt32(dataProdukDataGridView.Rows[i].Cells["HARGA_ECER"].Value) + ", " + "PRODUCT_BULK_PRICE = " + Convert.ToInt32(dataProdukDataGridView.Rows[i].Cells["HARGA_PARTAI"].Value) + ", " + "PRODUCT_WHOLESALE_PRICE = " + Convert.ToInt32(dataProdukDataGridView.Rows[i].Cells["HARGA_GROSIR"].Value) + " " + "WHERE ID = " + Convert.ToInt32(dataProdukDataGridView.Rows[i].Cells["ID"].Value); gutil.saveSystemDebugLog(globalConstants.MENU_PENGATURAN_HARGA, "UPDATE HARGA FOR [" + Convert.ToString(dataProdukDataGridView.Rows[i].Cells["kodeProduk"].Value) + "]"); if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } } } break; case globalConstants.PENGATURAN_LIMIT_STOK: for (int i = 0; i < dataProdukDataGridView.Rows.Count; i++) { if (Convert.ToBoolean(dataProdukDataGridView.Rows[i].Cells["CHANGED"].Value)) { sqlCommand = "UPDATE MASTER_PRODUCT SET " + "PRODUCT_LIMIT_STOCK = " + Convert.ToInt32(dataProdukDataGridView.Rows[i].Cells["LIMIT_STOK"].Value) + " " + "WHERE ID = " + Convert.ToInt32(dataProdukDataGridView.Rows[i].Cells["ID"].Value); gutil.saveSystemDebugLog(globalConstants.MENU_PENGATURAN_LIMIT_STOK, "UPDATE LIMIT STOK FOR [" + Convert.ToString(dataProdukDataGridView.Rows[i].Cells["kodeProduk"].Value) + "]"); if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } } } break; case globalConstants.PENGATURAN_NOMOR_RAK: for (int i = 0; i < dataProdukDataGridView.Rows.Count; i++) { if (Convert.ToBoolean(dataProdukDataGridView.Rows[i].Cells["CHANGED"].Value)) { kodeRakValue = dataProdukDataGridView.Rows[i].Cells["KODE_RAK"].Value.ToString(); while (kodeRakValue.Length < 2) { kodeRakValue = "-" + kodeRakValue; } nomorRakValue = dataProdukDataGridView.Rows[i].Cells["NOMOR_RAK"].Value.ToString(); while (nomorRakValue.Length < 2) { nomorRakValue = "0" + nomorRakValue; } sqlCommand = "UPDATE MASTER_PRODUCT SET " + "PRODUCT_SHELVES = '" + kodeRakValue + nomorRakValue + "' " + "WHERE ID = " + Convert.ToInt32(dataProdukDataGridView.Rows[i].Cells["ID"].Value); gutil.saveSystemDebugLog(globalConstants.MENU_PENGATURAN_NOMOR_RAK, "UPDATE NOMOR RAK FOR [" + Convert.ToString(dataProdukDataGridView.Rows[i].Cells["kodeProduk"].Value) + "]"); if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } } } break; } DS.commit(); result = true; } catch (Exception e) { gutil.saveSystemDebugLog(globalConstants.MENU_PENGATURAN_HARGA, "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 saveDataTransaction() { bool result = false; string sqlCommand = ""; MySqlException internalEX = null; string suppName = MySqlHelper.EscapeString(supplierNameTextBox.Text.Trim()); string suppAddress1 = MySqlHelper.EscapeString(supplierAddress1TextBox.Text.Trim()); if (suppAddress1.Equals("")) { suppAddress1 = " "; } string suppAddress2 = MySqlHelper.EscapeString(supplierAddress2TextBox.Text.Trim()); if (suppAddress2.Equals("")) { suppAddress2 = " "; } string suppAddressCity = MySqlHelper.EscapeString(supplierAddressCityTextBox.Text.Trim()); if (suppAddressCity.Equals("")) { suppAddressCity = " "; } string suppPhone = supplierPhoneTextBox.Text.Trim(); if (suppPhone.Equals("")) { suppPhone = " "; } string suppFax = supplierFaxTextBox.Text.Trim(); if (suppFax.Equals("")) { suppFax = " "; } string suppEmail = MySqlHelper.EscapeString(supplierEmailTextBox.Text.Trim()); if (suppEmail.Equals("")) { suppEmail = " "; } byte suppStatus = 0; if (nonAktifCheckbox.Checked) { suppStatus = 0; } else { suppStatus = 1; } DS.beginTransaction(); try { DS.mySqlConnect(); switch (originModuleID) { case globalConstants.NEW_SUPPLIER: sqlCommand = "INSERT INTO MASTER_SUPPLIER " + "(SUPPLIER_FULL_NAME, SUPPLIER_ADDRESS1, SUPPLIER_ADDRESS2, SUPPLIER_ADDRESS_CITY, SUPPLIER_PHONE, SUPPLIER_FAX, SUPPLIER_EMAIL, SUPPLIER_ACTIVE) " + "VALUES ('" + suppName + "', '" + suppAddress1 + "', '" + suppAddress2 + "', '" + suppAddressCity + "', '" + suppPhone + "', '" + suppFax + "', '" + suppEmail + "', " + suppStatus + ")"; gUtil.saveSystemDebugLog(globalConstants.MENU_SUPPLIER, "INSERT NEW SUPPLIER [" + suppName + "]"); break; case globalConstants.EDIT_SUPPLIER: sqlCommand = "UPDATE MASTER_SUPPLIER SET " + "SUPPLIER_FULL_NAME = '" + suppName + "', " + "SUPPLIER_ADDRESS1 = '" + suppAddress1 + "', " + "SUPPLIER_ADDRESS2 = '" + suppAddress2 + "', " + "SUPPLIER_ADDRESS_CITY = '" + suppAddressCity + "', " + "SUPPLIER_PHONE = '" + suppPhone + "', " + "SUPPLIER_FAX = '" + suppFax + "', " + "SUPPLIER_EMAIL = '" + suppEmail + "', " + "SUPPLIER_ACTIVE = " + suppStatus + " " + "WHERE SUPPLIER_ID = " + selectedSupplierID; gUtil.saveSystemDebugLog(globalConstants.MENU_SUPPLIER, "UPDATE SUPPLIER [" + selectedSupplierID + "]"); break; } if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } DS.commit(); result = true; } catch (Exception e) { gUtil.saveSystemDebugLog(globalConstants.MENU_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 loadImportedDataPM(string fileName) { bool result = false; string sqlCommand = ""; string pmInvoice = ""; //bool checkForRO = true; //int lastPos = 0; //int prevPos = 0; //int firstPos = 0; string roInvoice = ""; string tempString = ""; int importedBranchID = 0; int storedBranchID = 0; MySqlException internalEX = null; DS.beginTransaction(); try { DS.mySqlConnect(); System.IO.StreamReader file = new System.IO.StreamReader(fileName); pmInvoice = file.ReadLine(); roInvoice = file.ReadLine(); tempString = file.ReadLine();; if (int.TryParse(tempString, out importedBranchID)) { storedBranchID = getBranchID(); if (storedBranchID != importedBranchID) { file.Close(); throw new Exception("INFORMASI BRANCH ID TIDAK SESUAI"); } } else { //MessageBox.Show("INFORMASI BRANCH ID TIDAK DITEMUKAN"); file.Close(); throw new Exception("INFORMASI BRANCH ID TIDAK DITEMUKAN"); } if (!noPMExist(pmInvoice)) { while ((sqlCommand = file.ReadLine()) != null) { //if (checkForRO) //{ // tempString = sqlCommand; // // GET RO_INVOICE // if (sqlCommand.LastIndexOf("RO_INVOICE") > 0) // { // lastPos = sqlCommand.LastIndexOf("',"); // prevPos = sqlCommand.LastIndexOf(", '", lastPos - 1); // roInvoice = sqlCommand.Substring(prevPos+3, lastPos - prevPos-3); // } // // GET BRANCH_ID // if (sqlCommand.IndexOf("BRANCH_ID_TO") > 0) // { // firstPos = tempString.IndexOf("('"); // tempString = tempString.Substring(firstPos); // firstPos = tempString.IndexOf(","); // tempString = tempString.Substring(firstPos+1); // firstPos = tempString.IndexOf(","); // tempString = tempString.Substring(firstPos + 1); // firstPos = tempString.IndexOf(","); // tempString = tempString.Substring(1, firstPos-1); // if (int.TryParse(tempString, out importedBranchID)) // { // storedBranchID = getBranchID(); // if (storedBranchID != importedBranchID) // { // //MessageBox.Show("INFORMASI BRANCH ID TIDAK SESUAI"); // file.Close(); // throw new Exception("INFORMASI BRANCH ID TIDAK SESUAI"); // } // } // else // { // //MessageBox.Show("INFORMASI BRANCH ID TIDAK DITEMUKAN"); // file.Close(); // throw new Exception("INFORMASI BRANCH ID TIDAK DITEMUKAN"); // } // } //} //checkForRO = false; if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } } file.Close(); if (roInvoice.Length > 0) { sqlCommand = "UPDATE REQUEST_ORDER_HEADER SET RO_ACTIVE = 0 WHERE RO_INVOICE = '" + roInvoice + "'"; if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } } DS.commit(); result = true; } else { file.Close(); throw new Exception("NOMOR MUTASI SUDAH ADA"); } } catch (Exception e) { result = false; try { //myTrans.Rollback(); } catch (MySqlException ex) { if (DS.getMyTransConnection() != null) { MessageBox.Show("An exception of type " + ex.GetType() + " was encountered while attempting to roll back the transaction."); } } MessageBox.Show(e.Message); } finally { DS.mySqlClose(); } return(result); }
private bool saveDataTransaction() { bool result = false; string sqlCommand = ""; MySqlException internalEX = null; string userName = userNameTextBox.Text.Trim(); string userFullName = MySqlHelper.EscapeString(userFullNameTextBox.Text.Trim()); string password = passwordTextBox.Text.Trim(); string userPhone = MySqlHelper.EscapeString(userPhoneTextBox.Text.Trim()); byte userStatus = 0; if (nonAktifCheckbox.Checked) { userStatus = 0; } else { userStatus = 1; } DS.beginTransaction(); try { DS.mySqlConnect(); switch (originModuleID) { case globalConstants.NEW_USER: sqlCommand = "INSERT INTO MASTER_USER (USER_NAME, USER_FULL_NAME, USER_PASSWORD, USER_PHONE, USER_ACTIVE, GROUP_ID) " + "VALUES ('" + userName + "', '" + userFullName + "', '" + password + "', '" + userPhone + "', " + userStatus + ", " + selectedGroupID + ")"; break; case globalConstants.EDIT_USER: sqlCommand = "UPDATE MASTER_USER " + "SET USER_NAME = '" + userName + "', " + "USER_FULL_NAME = '" + userFullName + "', " + "USER_PASSWORD = '******', " + "USER_PHONE = '" + userPhone + "', " + "USER_ACTIVE = " + userStatus + ", " + "GROUP_ID = " + selectedGroupID + " " + "WHERE ID = " + selectedUserID; break; } 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 bool saveDataTransaction() { bool result = false; string sqlCommand = ""; MySqlException internalEX = null; string unitName = MySqlHelper.EscapeString(unitNameTextBox.Text.Trim()); string unitDesc = MySqlHelper.EscapeString(unitDescriptionTextBox.Text.Trim()); byte unitStatus = 0; if (nonAktifCheckbox.Checked) { unitStatus = 0; } else { unitStatus = 1; } DS.beginTransaction(); try { DS.mySqlConnect(); switch (originModuleID) { case globalConstants.NEW_UNIT: sqlCommand = "INSERT INTO MASTER_UNIT (UNIT_NAME, UNIT_DESCRIPTION, UNIT_ACTIVE) VALUES ('" + unitName + "', '" + unitDesc + "', " + unitStatus + ")"; gutil.saveSystemDebugLog(globalConstants.MENU_SATUAN, "ADD NEW UNIT [" + unitName + "]"); break; case globalConstants.EDIT_UNIT: sqlCommand = "UPDATE MASTER_UNIT SET UNIT_NAME = '" + unitName + "', UNIT_DESCRIPTION = '" + unitDesc + "', UNIT_ACTIVE = " + unitStatus + " WHERE UNIT_ID = " + selectedUnitID; gutil.saveSystemDebugLog(globalConstants.MENU_SATUAN, "UPDATE UNIT [" + selectedUnitID + "] [" + unitName + ", " + unitDesc + ", " + unitStatus + "]"); break; } if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } DS.commit(); result = true; } catch (Exception e) { gutil.saveSystemDebugLog(globalConstants.MENU_SATUAN, "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 saveDataTransaction() { //commit to database bool result = false; string sqlCommand = ""; MySqlException internalEX = null; int Account_ID = 0; String TglTrans = ""; String timeTrans = ""; Double NominalAkun = 0; int branch_id = getBranchID(); String deskripsi = ""; int user_id = 0; int pm_id = 0; String deskripsiakun = ""; //branch_id = getBranchID(); for (int rows = 0; rows < TransaksiAccountGridView.Rows.Count; rows++) { TglTrans = String.Format(culture, "{0:dd-MM-yyyy}", TransaksiAccountGridView.Rows[rows].Cells[1].Value.ToString()); timeTrans = gutil.getCustomStringFormatTime(DateTime.Now); TglTrans = TglTrans + " " + timeTrans; Account_ID = Int32.Parse(TransaksiAccountGridView.Rows[rows].Cells[2].Value.ToString()); pm_id = Int32.Parse(TransaksiAccountGridView.Rows[rows].Cells[6].Value.ToString()); Double debet, credit; if (Double.TryParse(TransaksiAccountGridView.Rows[rows].Cells[8].Value.ToString(), out debet)) { } if (Double.TryParse(TransaksiAccountGridView.Rows[rows].Cells[9].Value.ToString(), out credit)) { } if (debet == 0) { //credit NominalAkun = credit; } else { NominalAkun = debet; } deskripsiakun = TransaksiAccountGridView.Rows[rows].Cells[10].Value.ToString(); user_id = selectedUserID; selectedDJID = 0; selectedDJID = Int32.Parse(TransaksiAccountGridView.Rows[rows].Cells[0].Value.ToString()); originModuleID = globalConstants.NEW_DJ; if (selectedDJID > 0) { originModuleID = globalConstants.EDIT_DJ; } //for (int col = 0; col < TransaksiAccountGridView.Rows[rows].Cells.Count; col++) //{ // string value = TransaksiAccountGridView.Rows[rows].Cells[col].Value.ToString(); //} DS.beginTransaction(); try { DS.mySqlConnect(); switch (originModuleID) { case globalConstants.NEW_DJ: sqlCommand = "INSERT INTO DAILY_JOURNAL (ACCOUNT_ID, JOURNAL_DATETIME, JOURNAL_NOMINAL, BRANCH_ID, JOURNAL_DESCRIPTION, USER_ID, PM_ID) " + "VALUES (" + Account_ID + ", STR_TO_DATE('" + TglTrans + "', '%d-%m-%Y %H:%i')" + ", '" + NominalAkun + "', '" + branch_id + "', '" + deskripsiakun + "', '" + user_id + "', " + pm_id + ")"; options = gutil.INS; gutil.saveSystemDebugLog(globalConstants.MENU_TRANSAKSI_HARIAN, "INSERT NEW DATA TO DAILY JOURNAL TABLE [" + Account_ID + ", " + NominalAkun + "]"); break; case globalConstants.EDIT_DJ: sqlCommand = "UPDATE DAILY_JOURNAL SET " + "ACCOUNT_ID = " + Account_ID + ", " + "JOURNAL_DATETIME = " + "STR_TO_DATE('" + TglTrans + "', '%d-%m-%Y %H:%i')" + ", " + "JOURNAL_NOMINAL = '" + NominalAkun + "', " + "BRANCH_ID = '" + branch_id + "', " + "JOURNAL_DESCRIPTION = '" + deskripsiakun + "', " + "USER_ID = '" + user_id + "', " + "PM_ID = '" + pm_id + "' " + "WHERE JOURNAL_ID = '" + selectedDJID + "'"; options = gutil.UPD; gutil.saveSystemDebugLog(globalConstants.MENU_TRANSAKSI_HARIAN, "UPDATE DATA ON DAILY JOURNAL TABLE [" + selectedDJID + "]"); break; } if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } DS.commit(); result = true; } catch (Exception e) { gutil.saveSystemDebugLog(globalConstants.MENU_TRANSAKSI_HARIAN, "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 saveDataTransaction() { bool result = false; string sqlCommand = ""; MySqlException internalEX = null; DS.beginTransaction(); try { DS.mySqlConnect(); for (int i = 0; i < pengaturanKategoriDataGridView.Rows.Count; i++) { if (categoryProductValue[i].hasCategoryID != Convert.ToBoolean(pengaturanKategoriDataGridView.Rows[i].Cells["hakAkses"].Value)) { if ((categoryProductValue[i].hasCategoryID)) { sqlCommand = "DELETE FROM PRODUCT_CATEGORY WHERE PRODUCT_ID = '" + pengaturanKategoriDataGridView.Rows[i].Cells["PRODUCT_ID"].Value.ToString() + "' AND CATEGORY_ID = " + selectedCategoryID; gutil.saveSystemDebugLog(globalConstants.MENU_PENGATURAN_KATEGORI_PRODUK, "REMOVE CATEGORY [" + selectedCategoryID + "] FOR PRODUCT [" + pengaturanKategoriDataGridView.Rows[i].Cells["PRODUCT_ID"].Value.ToString() + "]"); } else { sqlCommand = "INSERT INTO PRODUCT_CATEGORY (PRODUCT_ID, CATEGORY_ID) VALUES ('" + pengaturanKategoriDataGridView.Rows[i].Cells["PRODUCT_ID"].Value.ToString() + "', " + selectedCategoryID + ")"; gutil.saveSystemDebugLog(globalConstants.MENU_PENGATURAN_KATEGORI_PRODUK, "ADD CATEGORY [" + selectedCategoryID + "] FOR PRODUCT [" + pengaturanKategoriDataGridView.Rows[i].Cells["PRODUCT_ID"].Value.ToString() + "]"); } if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX)) { throw internalEX; } } } DS.commit(); result = true; } catch (Exception e) { gutil.saveSystemDebugLog(globalConstants.MENU_PENGATURAN_KATEGORI_PRODUK, "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); }