private void createEntryForProductID(string productID)
{
MySqlDataReader rdr;
MySqlException internalEX = null;
string sqlCommand;
DS.beginTransaction();
try
{
sqlCommand = "SELECT ID FROM MASTER_LOCATION";
using (rdr = DS.getData(sqlCommand))
{
if (rdr.HasRows)
{
while (rdr.Read())
{
sqlCommand = "INSERT INTO PRODUCT_LOCATION (LOCATION_ID, PRODUCT_ID, PRODUCT_LOCATION_QTY) VALUES (" + rdr.GetInt32("ID") + ", '" + productID + "', 0)";
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw (internalEX);
}
}
}
}
rdr.Close();
DS.commit();
}
catch (Exception ex)
{ }
}
private bool syncLocalDataToServer(Data_Access localDS, string fileName)
{
System.IO.StreamReader file = new System.IO.StreamReader(fileName);
string sqlCommand = "";
MySqlException internalEX = null;
bool result = false;
localDS.beginTransaction(Data_Access.HQ_SERVER);
try
{
while ((sqlCommand = file.ReadLine()) != null)
{
if (sqlCommand.Length > 0)
if (!localDS.executeNonQueryCommand(sqlCommand, ref internalEX))
throw internalEX;
}
file.Close();
localDS.commit();
result = true;
}
catch (Exception ex)
{
gUtil.saveSystemDebugLog(0, "[TUTUP TOKO] FAILED TO SYNC LOCAL DATA TO SERVER [" + ex.Message + "]");
}
return result;
}
public void updateSyncFlag(string tableName, string PKField = "", string PKFieldValue = "")
{
string sqlCommand = "";
MySqlException internalEX = null;
DS.beginTransaction();
try
{
sqlCommand = "UPDATE " + tableName + " SET SYNCHRONIZED = 1, EDITED = 0";
if (PKField.Length > 0)
{
sqlCommand = sqlCommand + " WHERE " + PKField + " = '" + PKFieldValue + "'";
}
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
DS.commit();
}
catch (Exception ex)
{
gUtil.saveSystemDebugLog(0, "FAILED TO SET SYNC FIELD [" + ex.Message + "]");
}
}
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);
}
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;
string sqlCommand = "";
MySqlException internalEX = null;
DS.beginTransaction();
try
{
DS.mySqlConnect();
sqlCommand = "UPDATE PRODUCT_LOCATION SET PRODUCT_LOCATION_QTY = PRODUCT_LOCATION_QTY + " + jumlahTextBox.Text + " WHERE LOCATION_ID = " + selectedTujuanLocation + " AND PRODUCT_ID = " + selectedProductID;
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
sqlCommand = "UPDATE PRODUCT_LOCATION SET PRODUCT_LOCATION_QTY = PRODUCT_LOCATION_QTY - " + jumlahTextBox.Text + " WHERE LOCATION_ID = " + selectedAsalLocation + " AND PRODUCT_ID = " + selectedProductID;
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 = "";
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);
}
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 syncLocalDataToServerMultipleFiles(Data_Access localDS, int serverToConnect = 1)
{
// SEND DATA TO SERVER
System.IO.StreamReader file;// = new System.IO.StreamReader(fileName);
string sqlCommand = "";
MySqlException internalEX = null;
bool result = false;
localDS.beginTransaction(serverToConnect);
try
{
for (int i = 0; i < fileToExecute.Count; i++)
{
file = new System.IO.StreamReader(fileToExecute[i]);
while ((sqlCommand = file.ReadLine()) != null)
{
if (sqlCommand.Length > 0)
{
if (!localDS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
}
}
file.Close();
}
localDS.commit();
result = true;
}
catch (Exception ex)
{
gUtil.saveSystemDebugLog(0, "[SYNC] FAILED TO SYNC LOCAL DATA TO SERVER [" + ex.Message + "]");
}
return(result);
}
private bool saveDataTransaction()
{
bool result = false;
string sqlCommand = "";
string salesInvoice = "";
MySqlException internalEX = null;
DS.beginTransaction();
try
{
for (int i = 0; i < detailGridView.Rows.Count; i++)
{
salesInvoice = detailGridView.Rows[i].Cells["SALES INVOICE"].Value.ToString();
if (Convert.ToBoolean(detailGridView.Rows[i].Cells["status"].Value) == true)
{
sqlCommand = "UPDATE SALES_HEADER SET INCLUDE_IN_COMMISSION = 1 WHERE SALES_INVOICE = '" + salesInvoice + "' AND SALES_VOID = 0";
}
else
{
sqlCommand = "UPDATE SALES_HEADER SET INCLUDE_IN_COMMISSION = 0 WHERE SALES_INVOICE = '" + salesInvoice + "' AND SALES_VOID = 0";
}
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
}
DS.commit();
result = true;
}
catch (Exception ex)
{
gUtil.saveSystemDebugLog(0, "[COMMISSION] FAILED TO UPDATE FLAG AT SALES_HEADER [" + ex.Message + "]");
}
return(result);
}
private bool clearDataCabang(string tableName, int branchID, string ipServerBranch = "")
{
Data_Access DS_BRANCH = new Data_Access();
bool result = false;
string sqlCommand = "";
MySqlException internalEX = null;
// CONNECT TO BRANCH
if (DS_BRANCH.Branch_mySQLConnect(branchID, ipServerBranch))
{
DS_BRANCH.beginTransaction(Data_Access.BRANCH_SERVER);
try
{
sqlCommand = "DELETE FROM " + tableName;
if (!DS_BRANCH.executeNonQueryCommand(sqlCommand))
{
throw internalEX;
}
DS_BRANCH.commit();
result = true;
}
catch (Exception ex)
{
gUtil.saveSystemDebugLog(0, "[SYNC CABANG] FAILED TO CLEAR DATA CABANG [" + ex.Message + "]");
}
// CLOSE BRANCH CONNECTION
DS_BRANCH.Branch_mySqlClose();
}
else
{
gUtil.saveSystemDebugLog(0, "[SYNC CABANG] FAILED TO CONNECT TO CABANG");
}
return(result);
}
private bool pullDetailMessageAndSaveToTable(int moduleID, string sqlCommand)
{
MySqlDataReader rdr;
string param1;
string param2;
string productID;
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 " + param2;
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" + param2;
break;
case globalConstants.MENU_REQUEST_ORDER:
messageContent = "REQUEST ORDER [" + param1 + "] EXPIRED PADA TGL " + param2;
break;
case globalConstants.MENU_PRODUK:
messageContent = "[" + param1 + "] SUDAH MENDEKATI LIMIT, STOK [" + param2 + "]";
productID = rdr.GetString("PRODUCT_ID");
param1 = productID;
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()
{
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()
{
bool result = false;
string sqlCommand = "";
MySqlException internalEX = null;
string userName = userNameTextBox.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_SALESPERSON:
sqlCommand = "INSERT INTO MASTER_SALESPERSON(SALES_PERSON_NAME, SALES_PERSON_PHONE, SALES_PERSON_ACTIVE) " +
"VALUES ('" + userName + "', '" + userPhone + "', " + userStatus + ")";
break;
case globalConstants.EDIT_SALESPERSON:
sqlCommand = "UPDATE MASTER_SALESPERSON " +
"SET SALES_PERSON_NAME = '" + userName + "', " +
"SALES_PERSON_PHONE = '" + userPhone + "', " +
"SALES_PERSON_ACTIVE = " + userStatus + " " +
"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 productID = "";
double orderQty = 0;
double deliveredQty = 0;
double qty = 0;
int fullfilledItem = 0;
int status = 0;
int lineItemID = 0;
int locationID = gUtil.loadlocationID(2);
string selectedDate = DODtPicker.Value.ToShortDateString();
string DODateTime = String.Format(culture, "{0:dd-MM-yyyy}", Convert.ToDateTime(selectedDate));
// RE-GENERATE NO RETUR IN CASE CHANGED
doInvoiceTextBox.Text = gUtil.getAutoGenerateID("DELIVERY_ORDER_HEADER", "DO", "-", "DO_ID");
DS.beginTransaction();
try
{
DS.mySqlConnect();
// INSERT DATA HEADER
sqlCommand = "INSERT INTO DELIVERY_ORDER_HEADER (DO_ID, SALES_INVOICE, REV_NO, DO_DATE, REF_NO_NOTA) VALUES ('" + doInvoiceTextBox.Text + "', '" + selectedSalesInvoice + "', " + salesRevNo + ", STR_TO_DATE('" + DODateTime + "', '%d-%m-%Y'), '" + refNoNotaTextBox.Text + "')";
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
// INSERT DATA DETAIL
for (int i = 0; i < detailGridView.Rows.Count; i++)
{
productID = detailGridView.Rows[i].Cells["PRODUCT_ID"].Value.ToString();
orderQty = Convert.ToDouble(detailGridView.Rows[i].Cells["ORDER QTY"].Value);
deliveredQty = Convert.ToDouble(detailGridView.Rows[i].Cells["DELIVERED QTY"].Value);
qty = Convert.ToDouble(detailGridView.Rows[i].Cells["QTY"].Value);
status = Convert.ToInt32(detailGridView.Rows[i].Cells["IS_COMPLETED"].Value);
lineItemID = Convert.ToInt32(detailGridView.Rows[i].Cells["ID"].Value);
if (status == 0 && qty > 0)
{
// INSERT INTO DETAIL
sqlCommand = "INSERT INTO DELIVERY_ORDER_DETAIL (DO_ID, PRODUCT_ID, PRODUCT_QTY) VALUES ('" + doInvoiceTextBox.Text + "', '" + productID + "', " + qty + ")";
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
// REDUCE STOCK
if (!gUtil.productIsService(productID))
{
// REDUCE STOCK AT MASTER STOCK
sqlCommand = "UPDATE MASTER_PRODUCT SET PRODUCT_STOCK_QTY = PRODUCT_STOCK_QTY - " + qty + " WHERE PRODUCT_ID = '" + productID + "'";
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
// REDUCE STOCK AT PRODUCT LOCATION
sqlCommand = "UPDATE PRODUCT_LOCATION SET PRODUCT_LOCATION_QTY = PRODUCT_LOCATION_QTY - " + qty + " WHERE PRODUCT_ID = '" + productID + "' AND LOCATION_ID = " + locationID;
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
}
if (orderQty <= deliveredQty + qty)
{
// ORDER FULFILLED
fullfilledItem++;
sqlCommand = "UPDATE SALES_DETAIL SET IS_COMPLETED = 1 WHERE SALES_INVOICE = '" + selectedSalesInvoice + "' AND REV_NO = " + salesRevNo + " AND PRODUCT_QTY = " + orderQty + " AND ID = " + lineItemID;
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
}
}
else if (status == 1)
{
fullfilledItem++;
}
}
if (fullfilledItem == detailGridView.Rows.Count)
{
// WHOLE ORDER COMPLETED
// UPDATE SALES HEADER SET SALES ACTIVE TO 0
sqlCommand = "UPDATE SALES_HEADER SET SALES_ACTIVE = 0 WHERE SALES_INVOICE = '" + selectedSalesInvoice + "' AND REV_NO = '" + salesRevNo + "'";
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
}
DS.commit();
result = true;
}
catch (Exception ex)
{
}
return(result);
}
private bool saveDataTransaction()
{
bool result = false;
string sqlCommand = "";
MySqlException internalEX = null;
string categoryID = "";
string categoryName = MySqlHelper.EscapeString(categoryNameTextBox.Text.Trim());
string categoryDesc = MySqlHelper.EscapeString(categoryDescriptionTextBox.Text.Trim());
byte categoryStatus = 0;
if (nonAktifCheckbox.Checked)
{
categoryStatus = 0;
}
else
{
categoryStatus = 1;
}
DS.beginTransaction();
try
{
DS.mySqlConnect();
switch (originModuleID)
{
case globalConstants.NEW_CATEGORY:
//if (gutil.isSS_ServerApp() == 1)
//{
// categoryID = gutil.getAutoGenerateID("MASTER_CATEGORY", "", "", "CATEGORY_ID");
// sqlCommand = "INSERT INTO MASTER_CATEGORY (CATEGORY_ID, CATEGORY_NAME, CATEGORY_DESCRIPTION, CATEGORY_ACTIVE) " +
// "VALUES ('" + categoryID + "', '" + categoryName + "', '" + categoryDesc + "', " + categoryStatus + ")";
//}
//else
sqlCommand = "INSERT INTO MASTER_CATEGORY (CATEGORY_NAME, CATEGORY_DESCRIPTION, CATEGORY_ACTIVE) " +
"VALUES ('" + categoryName + "', '" + categoryDesc + "', " + categoryStatus + ")";
gutil.saveSystemDebugLog(globalConstants.MENU_KATEGORI, "ADD NEW CATEGORY [" + categoryName + "]");
break;
case globalConstants.EDIT_CATEGORY:
sqlCommand = "UPDATE MASTER_CATEGORY SET " +
"CATEGORY_NAME = '" + categoryName + "', " +
"CATEGORY_DESCRIPTION = '" + categoryDesc + "', " +
"CATEGORY_ACTIVE = " + categoryStatus + " " +
"WHERE CATEGORY_ID = " + selectedCategoryID;
gutil.saveSystemDebugLog(globalConstants.MENU_KATEGORI, "UPDATE CATEGORY [" + selectedCategoryID + "]");
break;
}
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
DS.commit();
result = true;
}
catch (Exception e)
{
gutil.saveSystemDebugLog(globalConstants.MENU_KATEGORI, "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 = "";
string dateLogin;
string dateLogOut;
string dateTimeFrom;
string dateTimeTo;
double totalCashTransaction = 0;
double totalNonCashTransaction = 0;
double totalOtherTransaction = 0;
MySqlException internalEX = null;
double startAmount = 0;
string startAmountInput = "";
double endAmount = 0;
string endAmountInput = "";
DS.beginTransaction();
try
{
DS.mySqlConnect();
if (loginState == 0)
{
//startAmount = Convert.ToDouble(gUtil.allTrim(startAmountBox.Text));
startAmountInput = gUtil.allTrim(textBox1.Text);
startAmountInput = startAmountInput.Replace(",", "");
startAmountInput = startAmountInput.Replace(".", "");
startAmount = Convert.ToDouble(startAmountInput);
dateLogin = gUtil.getCustomStringFormatDate(DateTime.Now);//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'), " + startAmount + ")";
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
gUtil.saveSystemDebugLog(0, "INSERT DATA FOR A NEW CASHIER SESSION, SA=" + startAmount);
}
else if (loginState == 1)
{
endAmountInput = gUtil.allTrim(textBox2.Text);
endAmountInput = endAmountInput.Replace(",", "");
endAmountInput = endAmountInput.Replace(".", "");
endAmount = Convert.ToDouble(endAmountInput);
dateLogOut = gUtil.getCustomStringFormatDate(DateTime.Now); //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 = " + endAmount + ", 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=" + endAmount + ", TC = " + totalCashTransaction + ", TN=" + totalNonCashTransaction + ", TO=" + totalOtherTransaction);
}
DS.commit();
result = true;
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
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;
}
// LARAS : ALL TRANSACTIONS ARE IN CASH
paymentMethod = 0;
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 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 void clearDailyTransaction()
{
string sqlCommand = "";
MySqlDataReader rdr;
MySqlException internalEX = null;
int creditID = 0;
int debtID = 0;
string salesInvoice = "";
DS.beginTransaction();
try
{
// GET A LIST OF CREDIT_ID
sqlCommand = "SELECT SH.SALES_INVOICE, C.CREDIT_ID FROM CREDIT C, SALES_HEADER SH WHERE C.SALES_INVOICE = SH.SALES_INVOICE AND SH.SALES_PAID = 1 AND C.CREDIT_PAID = 1";
using (rdr = DS.getData(sqlCommand))
{
while (rdr.Read())
{
salesInvoice = rdr.GetString("SALES_INVOICE");
creditID = rdr.GetInt32("CREDIT_ID");
// CLEAR SALES DETAIL
sqlCommand = "DELETE FROM SALES_DETAIL WHERE SALES_INVOICE = '" + salesInvoice + "'";
DS.executeNonQueryCommand(sqlCommand, ref internalEX);
// CLEAR SALES HEADER
sqlCommand = "DELETE FROM SALES_HEADER WHERE SALES_INVOICE = '" + salesInvoice + "'";
DS.executeNonQueryCommand(sqlCommand, ref internalEX);
// CLEAR PAYMENT_CREDIT
sqlCommand = "DELETE FROM PAYMENT_CREDIT WHERE CREDIT_ID = " + creditID;
DS.executeNonQueryCommand(sqlCommand, ref internalEX);
// CLEAR CREDIT
sqlCommand = "DELETE FROM CREDIT WHERE CREDIT_ID = " + creditID;
DS.executeNonQueryCommand(sqlCommand, ref internalEX);
}
}
rdr.Close();
// GET A LIST OF DEBT_ID
sqlCommand = "SELECT PH.PURCHASE_INVOICE, D.DEBT_ID FROM DEBT D, PURCHASE_HEADER PH WHERE D.PURCHASE_INVOICE = PH.PURCHASE_INVOICE AND PH.PURCHASE_PAID = 1 AND D.DEBT_PAID = 1";
using (rdr = DS.getData(sqlCommand))
{
while (rdr.Read())
{
debtID = rdr.GetInt32("DEBT_ID");
// CLEAR PAYMENT_CREDIT
sqlCommand = "DELETE FROM PAYMENT_DEBT WHERE DEBT_ID = " + debtID;
DS.executeNonQueryCommand(sqlCommand, ref internalEX);
// CLEAR CREDIT
sqlCommand = "DELETE FROM DEBT WHERE DEBT_ID = " + debtID;
DS.executeNonQueryCommand(sqlCommand, ref internalEX);
}
}
rdr.Close();
DS.commit();
}
catch (Exception ex)
{
gUtil.saveSystemDebugLog(0, "[TUTUP TOKO] FAIL TO CLEAR DATA SALES [" + ex.Message + "]");
}
}
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 regionName = MySqlHelper.EscapeString(namaGroupTextBox.Text.Trim());
string regionDesc = MySqlHelper.EscapeString(deskripsiTextBox.Text.Trim());
byte regionStatus = 0;
if (nonAktifCheckbox.Checked)
{
regionStatus = 0;
}
else
{
regionStatus = 1;
}
DS.beginTransaction();
try
{
DS.mySqlConnect();
switch (originModuleID)
{
case globalConstants.NEW_LOCATION:
sqlCommand = "INSERT INTO MASTER_LOCATION (LOCATION_NAME, LOCATION_DESCRIPTION, LOCATION_ACTIVE) VALUES ('" + regionName + "', '" + regionDesc + "', " + regionStatus + ")";
break;
case globalConstants.EDIT_LOCATION:
sqlCommand = "UPDATE MASTER_LOCATION SET LOCATION_NAME = '" + regionName + "', LOCATION_DESCRIPTION= '" + regionDesc + "', LOCATION_ACTIVE = " + regionStatus + " WHERE ID = " + selectedLocationID;
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(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 = branchComboHidden.Text; //BranchIDTextbox.Text;
String locationID = locationComboHidden.Text; //locationIDTextBox.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, LOCATION_ID, HQ_IP4, STORE_NAME, STORE_ADDRESS, STORE_PHONE, STORE_EMAIL, POS_RECEIPT_PRINTER, KUARTO_PRINTER) " +
"VALUES (2, '', '" + branchID + "', '" + locationID + "', '" + HQIP + "', '" + nama_toko + "', '" + alamat_toko + "', '" + telepon_toko + "', '" + email_toko + "', '" + posReceiptPrinter.Text + "', '" + kuartoPrinter.Text + "')";
options = gutil.INS;
gutil.saveSystemDebugLog(0, "INSERT DATA ID 2 TO SYS_CONFIG");
break;
case 2:
sqlCommand = "UPDATE SYS_CONFIG SET " +
"BRANCH_ID = " + branchID + ", " +
"LOCATION_ID = " + locationID + ", " +
"HQ_IP4 = '" + HQIP + "', " +
"STORE_NAME = '" + nama_toko + "', " +
"STORE_ADDRESS = '" + alamat_toko + "', " +
"STORE_PHONE = '" + telepon_toko + "', " +
"STORE_EMAIL = '" + email_toko + "', " +
"POS_RECEIPT_PRINTER = '" + posReceiptPrinter.Text + "', " +
"KUARTO_PRINTER = '" + kuartoPrinter.Text + "' " +
"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 = "";
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 = "";
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);
}
private bool saveDataTransaction()
{
bool result = false;
string sqlCommand = "";
string returID = "0";
int supplierID = 0;
string ReturDateTime = "";
double returTotal = 0;
double hppValue;
double qtyValue;
string descriptionValue;
DateTime selectedReturDate;
MySqlException internalEX = null;
// RE-GENERATE NO RETUR IN CASE ID CHANGED
noReturTextBox.Text = GUTIL.getAutoGenerateID("RETURN_PURCHASE_HEADER", "RP", "-", "RP_ID");
returID = noReturTextBox.Text;
supplierID = selectedSupplierID;
selectedReturDate = ReturDtPicker_1.Value;
ReturDateTime = String.Format(culture, "{0:dd-MM-yyyy}", selectedReturDate);
returTotal = globalTotalValue;
DS.beginTransaction();
try
{
DS.mySqlConnect();
// SAVE HEADER TABLE
sqlCommand = "INSERT INTO RETURN_PURCHASE_HEADER (RP_ID, SUPPLIER_ID, RP_DATE, RP_TOTAL, RP_PROCESSED) VALUES " +
"('" + returID + "', " + supplierID + ", STR_TO_DATE('" + ReturDateTime + "', '%d-%m-%Y'), " + returTotal + ", 1)";
GUTIL.saveSystemDebugLog(globalConstants.MENU_RETUR_PEMBELIAN, "INSERT TO RETURN PURCHASE HEADER");
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
// SAVE DETAIL TABLE
for (int i = 0; i < detailReturDataGridView.Rows.Count; i++)
{
if (null != detailReturDataGridView.Rows[i].Cells["productID"].Value && GUTIL.isProductIDExist(detailReturDataGridView.Rows[i].Cells["productID"].Value.ToString()))
{
hppValue = Convert.ToDouble(detailReturDataGridView.Rows[i].Cells["hpp"].Value); //Convert.ToDouble(productPriceList[i]);
qtyValue = Convert.ToDouble(detailReturDataGridView.Rows[i].Cells["qty"].Value); //Convert.ToDouble(detailQty[i]);
try
{
descriptionValue = detailReturDataGridView.Rows[i].Cells["description"].Value.ToString();
}
catch (Exception ex)
{
descriptionValue = " ";
}
sqlCommand = "INSERT INTO RETURN_PURCHASE_DETAIL (RP_ID, PRODUCT_ID, PRODUCT_BASEPRICE, PRODUCT_QTY, RP_DESCRIPTION, RP_SUBTOTAL) VALUES " +
"('" + returID + "', '" + detailReturDataGridView.Rows[i].Cells["productID"].Value.ToString() + "', " + hppValue + ", " + qtyValue + ", '" + MySqlHelper.EscapeString(descriptionValue) + "', " + Convert.ToDouble(detailReturDataGridView.Rows[i].Cells["subTotal"].Value) + ")";
GUTIL.saveSystemDebugLog(globalConstants.MENU_RETUR_PEMBELIAN, "INSERT TO RETURN PURCHASE DETAIL");
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
// UPDATE PRODUCT LOCATION DATA
sqlCommand = "UPDATE PRODUCT_LOCATION SET PRODUCT_LOCATION_QTY = PRODUCT_LOCATION_QTY - " + qtyValue + " WHERE PRODUCT_ID = '" + detailReturDataGridView.Rows[i].Cells["productID"].Value.ToString() + "' AND LOCATION_ID = " + locationID;
GUTIL.saveSystemDebugLog(globalConstants.MENU_RETUR_PEMBELIAN, "UPDATE PRODUCT LOCATION DATA");
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
// UPDATE MASTER PRODUCT
sqlCommand = "UPDATE MASTER_PRODUCT SET PRODUCT_STOCK_QTY = PRODUCT_STOCK_QTY - " + qtyValue + " WHERE PRODUCT_ID = '" + detailReturDataGridView.Rows[i].Cells["productID"].Value.ToString() + "'";
GUTIL.saveSystemDebugLog(globalConstants.MENU_RETUR_PEMBELIAN, "UPDATE MASTER PRODUCT");
if (!DS.executeNonQueryCommand(sqlCommand, ref internalEX))
{
throw internalEX;
}
}
}
DS.commit();
result = true;
}
catch (Exception e)
{
GUTIL.saveSystemDebugLog(globalConstants.MENU_RETUR_PEMBELIAN, "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;
string monthSelected = "";
dateFrom = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(datefromPicker.Value));
dateTo = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(datetoPicker.Value));
monthSelected = String.Format(culture, "{0:yyyyMM}", Convert.ToDateTime(MonthPicker.Value));
DS.mySqlConnect();
string sqlCommandx = "";
switch (originModuleID)
{
case globalConstants.REPORT_FINANCE_IN:
sqlCommandx = "SELECT '' AS BRANCH_NAME, 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);
ReportFinanceInForm displayedForm1 = new ReportFinanceInForm();
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 '' AS BRANCH_NAME, 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 INNER JOIN (SELECT SALES_INVOICE, MAX(REV_NO) AS MAX FROM SALES_DETAIL GROUP BY SALES_INVOICE) MAX_SD ON SD.SALES_INVOICE = MAX_SD.SALES_INVOICE AND SD.REV_NO = MAX_SD.MAX " +
"WHERE SH.REV_NO = SD.REV_NO AND 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 BRANCH_NAME, 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();
}
string queryDate = "";
string queryDebetDJ = "";
string queryDebetSO = "";
string queryCreditDJ = "";
queryDate = "SELECT TGL FROM DAYSMONTH";
queryDebetDJ = "SELECT DATE_FORMAT(DJ.JOURNAL_DATETIME, '%d') AS 'TGL', SUM(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 DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%M') = '" + monthSelected + "' " +
"GROUP BY DATE_FORMAT(DJ.JOURNAL_DATETIME, '%d')";
queryDebetSO = "SELECT DATE_FORMAT(SH.SALES_DATE, '%d') AS 'TGL', " +
"IFNULL(SUM((SD.SALES_SUBTOTAL-(SD.PRODUCT_QTY * SD.PRODUCT_PRICE))), 0) AS 'DEBET' " +
"FROM SALES_HEADER SH, SALES_DETAIL SD, (SELECT SALES_INVOICE, MAX(REV_NO) REV_NO FROM SALES_HEADER GROUP BY SALES_INVOICE) SH2 " +
"WHERE SH.SALES_PAID = 1 AND SH.SALES_INVOICE = SD.SALES_INVOICE " +
"AND SH.SALES_INVOICE = SH2.SALES_INVOICE AND SH.REV_NO = SH2.REV_NO AND SD.SALES_INVOICE = SH2.SALES_INVOICE AND SD.REV_NO = SH2.REV_NO " +
"AND DATE_FORMAT(SH.SALES_DATE, '%Y%m') = '" + monthSelected + "' " +
"GROUP BY DATE_FORMAT(SH.SALES_DATE, '%d')";
queryCreditDJ = "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 DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m') = '" + monthSelected + "' " +
"GROUP BY DATE_FORMAT(DJ.JOURNAL_DATETIME, '%d')";
sqlCommandx = "SELECT TABDAYS.TGL, (IFNULL(TABDJ.DEBET, 0) + IFNULL(TABSO.DEBET, 0)) AS DEBET, IFNULL(TABCRED.KREDIT, 0) AS KREDIT " +
"FROM (" + queryDate + ") TABDAYS LEFT OUTER JOIN (" + queryDebetDJ + ") TABDJ " +
"ON (TABDAYS.TGL = TABDJ.TGL) LEFT OUTER JOIN (" + queryDebetSO + ") TABSO " +
"ON (TABDAYS.TGL = TABSO.TGL) LEFT OUTER JOIN (" + queryCreditDJ + ") TABCRED " +
"ON (TABDAYS.TGL = TABCRED.TGL)";
//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 DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m') = '" + monthSelected + "') 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, (SELECT SALES_INVOICE, MAX(REV_NO) REV_NO FROM SALES_HEADER GROUP BY SALES_INVOICE) SH2 " +
// "WHERE SH.SALES_PAID = 1 AND SH.SALES_INVOICE = SD.SALES_INVOICE AND SH.SALES_INVOICE = SH2.SALES_INVOICE AND SH.REV_NO = SH2.REV_NO AND SD.SALES_INVOICE = SH2.SALES_INVOICE AND SD.REV_NO = SH2.REV_NO " +
// "AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') = '" + monthSelected + "' " +
// "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 DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d') = '" + monthSelected + "' " +
// "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;
}
}