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