private bool insertDataToHQ(Data_Access DAccess)
{
bool result = false;
string sqlCommand = "";
MySqlException internalEX = null;
string roInvoice = "";
int branchIDFrom = 0;
int branchIDTo = 0;
string roDateTime = "";
double roTotal = 0;
string roDateExpired = "";
DateTime selectedRODate;
DateTime expiredRODate;
string messageContent = "";
string selectedDate = RODateTimePicker.Value.ToShortDateString();
selectedRODate = RODateTimePicker.Value;
expiredRODate = selectedRODate.AddDays(Convert.ToDouble(durationTextBox.Text));
roInvoice = ROinvoiceTextBox.Text;
branchIDFrom = selectedBranchFromID;
branchIDTo = selectedBranchToID;
roDateTime = String.Format(culture, "{0:dd-MM-yyyy}", Convert.ToDateTime(selectedDate));
roDateExpired = String.Format(culture, "{0:dd-MM-yyyy}", expiredRODate);
roTotal = globalTotalValue;
DAccess.beginTransaction(Data_Access.HQ_SERVER);
try
{
//DAccess.mySqlConnect();
// SAVE HEADER TABLE
sqlCommand = "INSERT INTO REQUEST_ORDER_HEADER (RO_INVOICE, RO_BRANCH_ID_FROM, RO_BRANCH_ID_TO, RO_DATETIME, RO_TOTAL, RO_EXPIRED, RO_ACTIVE) VALUES " +
"('" + roInvoice + "', " + branchIDFrom + ", " + branchIDTo + ", STR_TO_DATE('" + roDateTime + "', '%d-%m-%Y'), " + gUtil.validateDecimalNumericInput(roTotal) + ", STR_TO_DATE('" + roDateExpired + "', '%d-%m-%Y'), 1)";
gUtil.saveSystemDebugLog(globalConstants.MENU_REQUEST_ORDER, "INSERT REQUEST ORDER ["+roInvoice+"] TO HQ");
if (!DAccess.executeNonQueryCommand(sqlCommand, ref internalEX))
throw internalEX;
// SAVE DETAIL TABLE
for (int i = 0; i < detailRequestOrderDataGridView.Rows.Count; i++)
{
if (null != detailRequestOrderDataGridView.Rows[i].Cells["productID"].Value)
{
sqlCommand = "INSERT INTO REQUEST_ORDER_DETAIL (RO_INVOICE, PRODUCT_ID, PRODUCT_BASE_PRICE, RO_QTY, RO_SUBTOTAL) VALUES " +
"('" + roInvoice + "', '" + detailRequestOrderDataGridView.Rows[i].Cells["productID"].Value.ToString() + "', " + Convert.ToDouble(detailRequestOrderDataGridView.Rows[i].Cells["hpp"].Value) + ", " + Convert.ToDouble(detailRequestOrderDataGridView.Rows[i].Cells["qty"].Value) + ", " + gUtil.validateDecimalNumericInput(Convert.ToDouble(detailRequestOrderDataGridView.Rows[i].Cells["subTotal"].Value)) + ")";
gUtil.saveSystemDebugLog(globalConstants.MENU_REQUEST_ORDER, "INSERT DETAIL REQUEST ORDER [" + detailRequestOrderDataGridView.Rows[i].Cells["productID"].Value.ToString() + ", " + Convert.ToDouble(detailRequestOrderDataGridView.Rows[i].Cells["hpp"].Value) + ", " + Convert.ToDouble(detailRequestOrderDataGridView.Rows[i].Cells["qty"].Value) + "] TO HQ");
if (!DAccess.executeNonQueryCommand(sqlCommand, ref internalEX))
throw internalEX;
}
}
// INSERT INTO HQ MESSAGING TABLE
messageContent = "REQUEST ORDER [" + roInvoice + "] EXPIRED PADA TGL " + roDateExpired;
sqlCommand = "INSERT INTO MASTER_MESSAGE (STATUS, MODULE_ID, IDENTIFIER_NO, MSG_DATETIME_CREATED, MSG_CONTENT) " +
"VALUES " +
"(0, " + globalConstants.MENU_REQUEST_ORDER + ", '" + roInvoice + "', STR_TO_DATE('" + roDateTime + "', '%d-%m-%Y'), '" + messageContent + "')";
gUtil.saveSystemDebugLog(globalConstants.MENU_REQUEST_ORDER, "INSERT TO HQ MESSAGING TABLE");
if (!DAccess.executeNonQueryCommand(sqlCommand, ref internalEX))
throw internalEX;
DAccess.commit();
result = true;
}
catch (Exception e)
{
gUtil.saveSystemDebugLog(globalConstants.MENU_REQUEST_ORDER, "EXCEPTION THROWN ["+e.Message+"]");
try
{
DAccess.rollBack();
}
catch (MySqlException ex)
{
if (DAccess.getMyTransConnection() != null)
{
gUtil.showDBOPError(ex, "ROLLBACK");
}
}
gUtil.showDBOPError(e, "INSERT");
result = false;
}
finally
{
DAccess.mySqlClose();
}
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);
}
private bool updateDataToHQ(Data_Access DAccess)
{
bool result = false;
string sqlCommand = "";
MySqlException internalEX = null;
string pmInvoice = "";
pmInvoice = noMutasiTextBox.Text;
DAccess.beginTransaction(Data_Access.HQ_SERVER);
try
{
// UPDATE PM DATA AT HQ
sqlCommand = "UPDATE PRODUCTS_MUTATION_HEADER SET PM_RECEIVED = 1 WHERE PM_INVOICE = '" + pmInvoice + "'";
gUtil.saveSystemDebugLog(globalConstants.MENU_PENERIMAAN_BARANG, "ATTEMPT TO UPDATE PRODUCTS MUTATION HEADER TO INDICATE RECEIVED [" + pmInvoice + "]");
if (!DAccess.executeNonQueryCommand(sqlCommand, ref internalEX))
throw internalEX;
DAccess.commit();
result = true;
}
catch (Exception e)
{
gUtil.saveSystemDebugLog(globalConstants.MENU_PENERIMAAN_BARANG, "EXCEPTION THROWN ["+e.Message+"]");
try
{
DAccess.rollBack();
}
catch (MySqlException ex)
{
if (DAccess.getMyTransConnection() != null)
{
gUtil.showDBOPError(ex, "ROLLBACK");
}
}
gUtil.showDBOPError(e, "INSERT");
result = false;
}
finally
{
DAccess.mySqlClose();
}
return result;
}
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;
}