private bool syncToCentralHQ() { bool result = false; Data_Access DS_HQ = new Data_Access(); // CREATE CONNECTION TO CENTRAL HQ DATABASE SERVER gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "TRY TO CREATE CONNECTION TO CENTRAL HQ"); if (DS_HQ.HQ_mySQLConnect()) { gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "CONNECTION TO CENTRAL HQ CREATED"); // DUMP NECESSARY DATA TO LOCAL COPY exportData(syncFileName, DS_HQ, true); gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "CENTRAL HQ DATA EXPORTED"); // CLOSE CONNECTION TO CENTRAL HQ DATABASE SERVER DS_HQ.mySqlClose(); gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "CLOSE CONNECTION TO CENTRAL HQ"); // INSERT TO LOCAL DATA gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "SYNC LOCAL INFORMATION WITH DATA FROM CENTRAL HQ [" + syncFileName + "]"); syncInformation(syncFileName); gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "SYNC LOCAL INFORMATION FINISHED"); result = true; } else { MessageBox.Show("KONEKSI KE PUSAT GAGAL"); gutil.saveSystemDebugLog(globalConstants.MENU_SINKRONISASI_INFORMASI, "FAILED TO CONNECT TO CENTRAL HQ"); result = false; } return(result); }
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); }