private void exportData(string fileName, Data_Access DAccess, bool isHQConnection = false) { //string localDate = ""; //string strCmdText = ""; //string ipServer; //System.Diagnostics.Process proc = new System.Diagnostics.Process(); MySqlDataReader rdr; string sqlCommand = ""; string insertStatement = ""; StreamWriter sw = null; // EXPORT MASTER PRODUCT DATA string strCmdText = "USE `sys_pos`; " + "\n" + "DROP TABLE IF EXISTS `temp_master_product`;" + "\n" + "\n" + "CREATE TABLE `temp_master_product` (" + "\n" + "`ID` int(10) unsigned NOT NULL AUTO_INCREMENT," + "\n" + "`PRODUCT_ID` varchar(50) DEFAULT NULL," + "\n" + "`PRODUCT_BARCODE` varchar(15) DEFAULT NULL," + "\n" + "`PRODUCT_NAME` varchar(50) DEFAULT NULL," + "\n" + "`PRODUCT_DESCRIPTION` varchar(100) DEFAULT NULL," + "\n" + "`PRODUCT_BASE_PRICE` double DEFAULT NULL," + "\n" + "`PRODUCT_RETAIL_PRICE` double DEFAULT NULL," + "\n" + "`PRODUCT_BULK_PRICE` double DEFAULT NULL," + "\n" + "`PRODUCT_WHOLESALE_PRICE` double DEFAULT NULL," + "\n" + "`UNIT_ID` smallint(5) unsigned DEFAULT '0'," + "\n" + "`PRODUCT_IS_SERVICE` tinyint(3) unsigned DEFAULT NULL," + "\n" + "PRIMARY KEY(`ID`)," + "\n" + "UNIQUE KEY `PRODUCT_ID_UNIQUE` (`PRODUCT_ID`)" + "\n" + ") ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;" + "\n" + "\n" + "DROP TABLE IF EXISTS `temp_product_category`;" + "\n" + "\n" + "CREATE TABLE `temp_product_category` (" + "\n" + "`PRODUCT_ID` varchar(50) NOT NULL," + "\n" + "`CATEGORY_ID` tinyint(3) unsigned NOT NULL," + "\n" + "PRIMARY KEY (`PRODUCT_ID`,`CATEGORY_ID`)" + "\n" + ") ENGINE = InnoDB DEFAULT CHARSET = utf8;" + "\n"; //localDate = String.Format(culture, "{0:ddMMyyyy}", DateTime.Now); //fileName = "SYNCINFO_PRODUCT_" + localDate + ".sql"; sqlCommand = "SELECT PRODUCT_ID, IFNULL(PRODUCT_BARCODE, '') AS PRODUCT_BARCODE, IFNULL(PRODUCT_NAME, '') AS PRODUCT_NAME, IFNULL(PRODUCT_DESCRIPTION, '') AS PRODUCT_DESCRIPTION, PRODUCT_BASE_PRICE, PRODUCT_RETAIL_PRICE, PRODUCT_BULK_PRICE, PRODUCT_WHOLESALE_PRICE, UNIT_ID, PRODUCT_IS_SERVICE FROM MASTER_PRODUCT WHERE PRODUCT_ACTIVE = 1"; using (rdr = DAccess.getData(sqlCommand, isHQConnection)) { if (rdr.HasRows) { if (!File.Exists(fileName)) { sw = File.CreateText(fileName); } else { File.Delete(fileName); sw = File.CreateText(fileName); } sw.WriteLine(strCmdText); while (rdr.Read()) { insertStatement = "INSERT INTO TEMP_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_IS_SERVICE) VALUES (" + "'" + MySqlHelper.EscapeString(rdr.GetString("PRODUCT_ID")) + "', '" + MySqlHelper.EscapeString(rdr.GetString("PRODUCT_BARCODE")) + "', '" + MySqlHelper.EscapeString(rdr.GetString("PRODUCT_NAME")) + "', '" + MySqlHelper.EscapeString(rdr.GetString("PRODUCT_DESCRIPTION")) + "', " + rdr.GetString("PRODUCT_BASE_PRICE") + ", " + rdr.GetString("PRODUCT_RETAIL_PRICE") + ", " + rdr.GetString("PRODUCT_BULK_PRICE") + ", " + rdr.GetString("PRODUCT_WHOLESALE_PRICE") + ", " + rdr.GetString("UNIT_ID") + ", " + rdr.GetString("PRODUCT_IS_SERVICE") + ");"; sw.WriteLine(insertStatement); } } rdr.Close(); } sw.WriteLine(""); // EXPORT MASTER KATEGORI DATA sw.WriteLine(""); sw.WriteLine("DELETE FROM MASTER_CATEGORY;"); sqlCommand = "SELECT CATEGORY_ID, CATEGORY_NAME, IFNULL(CATEGORY_DESCRIPTION, '') AS CATEGORY_DESCRIPTION FROM MASTER_CATEGORY WHERE CATEGORY_ACTIVE = 1"; using (rdr = DAccess.getData(sqlCommand, isHQConnection)) { if (rdr.HasRows) { while (rdr.Read()) { insertStatement = "INSERT INTO MASTER_CATEGORY (CATEGORY_ID, CATEGORY_NAME, CATEGORY_DESCRIPTION, CATEGORY_ACTIVE) VALUES (" + rdr.GetString("CATEGORY_ID") + ", '" + MySqlHelper.EscapeString(rdr.GetString("CATEGORY_NAME")) + "', '" + MySqlHelper.EscapeString(rdr.GetString("CATEGORY_DESCRIPTION")) + "', 1);"; sw.WriteLine(insertStatement); } } rdr.Close(); } sw.WriteLine(""); // EXPORT MASTER UNIT DATA sw.WriteLine(""); sw.WriteLine("DELETE FROM MASTER_UNIT;"); sqlCommand = "SELECT UNIT_ID, UNIT_NAME, IFNULL(UNIT_DESCRIPTION, '') AS UNIT_DESCRIPTION FROM MASTER_UNIT WHERE UNIT_ACTIVE = 1"; using (rdr = DAccess.getData(sqlCommand, isHQConnection)) { if (rdr.HasRows) { while (rdr.Read()) { insertStatement = "INSERT INTO MASTER_UNIT (UNIT_ID, UNIT_NAME, UNIT_DESCRIPTION, UNIT_ACTIVE) VALUES (" + rdr.GetString("UNIT_ID") + ", '" + MySqlHelper.EscapeString(rdr.GetString("UNIT_NAME")) + "', '" + MySqlHelper.EscapeString(rdr.GetString("UNIT_DESCRIPTION")) + "', 1);"; sw.WriteLine(insertStatement); } } rdr.Close(); } sw.WriteLine(""); // EXPORT MASTER UNIT KONVERSI DATA sw.WriteLine(""); sw.WriteLine("DELETE FROM UNIT_CONVERT;"); sqlCommand = "SELECT CONVERT_UNIT_ID_1, CONVERT_UNIT_ID_2, CONVERT_MULTIPLIER FROM UNIT_CONVERT"; using (rdr = DAccess.getData(sqlCommand, isHQConnection)) { if (rdr.HasRows) { while (rdr.Read()) { insertStatement = "INSERT INTO UNIT_CONVERT (CONVERT_UNIT_ID_1, CONVERT_UNIT_ID_2, CONVERT_MULTIPLIER) VALUES (" + rdr.GetString("CONVERT_UNIT_ID_1") + ", " + rdr.GetString("CONVERT_UNIT_ID_2") + ", " + rdr.GetString("CONVERT_MULTIPLIER") + ");"; sw.WriteLine(insertStatement); } } rdr.Close(); } sw.WriteLine(""); // EXPORT PRODUCT CATEGORY DATA sw.WriteLine(""); sqlCommand = "SELECT PRODUCT_ID, CATEGORY_ID FROM PRODUCT_CATEGORY"; using (rdr = DAccess.getData(sqlCommand, isHQConnection)) { if (rdr.HasRows) { while (rdr.Read()) { insertStatement = "INSERT INTO TEMP_PRODUCT_CATEGORY (PRODUCT_ID, CATEGORY_ID) VALUES (" + "'" + rdr.GetString("PRODUCT_ID") + "', " + rdr.GetString("CATEGORY_ID") + ");"; sw.WriteLine(insertStatement); } } rdr.Close(); } sw.WriteLine(""); sw.Close(); //ipServer = DS.getIPServer(); ////strCmdText = "/C mysqldump -h " + ipServer + " -u SYS_POS_ADMIN -ppass123 sys_pos MASTER_PRODUCT > \"" + fileName + "\""; //proc.StartInfo.FileName = "CMD.exe"; //proc.StartInfo.Arguments = "/C " + "mysqldump -h " + ipServer + " -u SYS_POS_ADMIN -ppass123 sys_pos > \"" + fileName + "\""; //proc.Exited += new EventHandler(ProcessExited); //proc.EnableRaisingEvents = true; //proc.Start(); //System.Diagnostics.Process.Start("CMD.exe", strCmdText); }
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 saveToCSV(string fileName) { //string fileName = ""; //string localDate; string sqlCommand; string line = ""; MySqlDataReader rdr; StreamWriter sw = null; bool firstColumn = true; StringBuilder builder = new StringBuilder(); string value = ""; if (fileName.Length <= 0) { return(false); } //localDate = String.Format(culture, "{0:ddMMyyyy}", DateTime.Now); //fileName = "EXPORT_" + localDate + ".csv"; //localDate = String.Format(culture, "{0:dd-MMM-yyyy}", DateTime.Now); sqlCommand = "SELECT PRODUCT_ID, PRODUCT_BARCODE, PRODUCT_NAME, PRODUCT_STOCK_QTY, 0 AS PRODUCT_ACTUAL_QTY, '' AS DESCRIPTION FROM MASTER_PRODUCT WHERE PRODUCT_ACTIVE = 1 ORDER BY ID"; using (rdr = DS.getData(sqlCommand)) { if (rdr.HasRows) { if (!File.Exists(fileName)) { sw = File.CreateText(fileName); } else { File.Delete(fileName); sw = File.CreateText(fileName); } //sw.WriteLine(localDate); builder.Append("KODE PRODUK"); builder.Append(","); builder.Append("BARCODE PRODUK"); builder.Append(","); builder.Append("NAMA PRODUK"); builder.Append(","); builder.Append("QTY PRODUK"); builder.Append(","); builder.Append("QTY RIIL"); builder.Append(","); builder.Append("DESCRIPTION"); line = builder.ToString(); sw.WriteLine(line); while (rdr.Read()) { builder.Clear(); firstColumn = true; for (int index = 0; index < rdr.FieldCount; index++) { value = rdr.GetString(index); // Add separator if this isn't the first value if (!firstColumn) { builder.Append(','); } // Implement special handling for values that contain comma or quote // Enclose in quotes and double up any double quotes if (value.IndexOfAny(new char[] { '"', ',' }) != -1) { builder.AppendFormat("\"{0}\"", value.Replace("\"", "\"\"")); } else { builder.Append(value); } //else // builder.AppendFormat("\'{0}\'", value); firstColumn = false; } line = builder.ToString(); //line = rdr.GetString("PRODUCT_ID") + ";" + rdr.GetString("PRODUCT_BARCODE") + ";" + rdr.GetString("PRODUCT_NAME") + ";" + rdr.GetString("PRODUCT_STOCK_QTY") + ";0;"; sw.WriteLine(line); } sw.Close(); } } return(true); }
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 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 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) { } } } } } }