private void writeTableContentToInsertStatement(string tableName, StreamWriter sw, Data_Access DAccess, bool isHQConnection = false, bool skipFirstField = false, bool skipAddBranchID = false, string sqlParam = "", string customTableName = "")
{
string sqlCommand = "";
MySqlDataReader rdr;
string insertStatement = "";
string valueStatement = "";
int rdrFieldIndex = 0;
int startIndex = 0;
DateTime tempDateTime;
string dateTimeValue;
object DBValue = null;
int branchID = 0;
string namaCabang = "";
branchID = gUtil.loadbranchID(2, out namaCabang);
if (sqlParam.Length <= 0)
sqlCommand = "SELECT * FROM " + tableName;
else
sqlCommand = sqlParam;
if (skipFirstField)
startIndex = 1;
sw.WriteLine("");
using (rdr = DAccess.getData(sqlCommand, isHQConnection))
{
if (rdr.HasRows)
{
while (rdr.Read())
{
if (customTableName.Length > 0)
insertStatement = "INSERT INTO " + customTableName + "(";
else
insertStatement = "INSERT INTO " + tableName + "(";
valueStatement = "";
for (rdrFieldIndex = startIndex; rdrFieldIndex < rdr.FieldCount; rdrFieldIndex++)
{
DBValue = rdr.GetValue(rdrFieldIndex);
if (DBValue.ToString().Length > 0)
{
insertStatement = insertStatement + rdr.GetName(rdrFieldIndex) + ", ";
if (rdr.GetDataTypeName(rdrFieldIndex) == "DATE")
{
tempDateTime = rdr.GetDateTime(rdrFieldIndex);
dateTimeValue = String.Format(culture, "{0:dd-MM-yyyy}", tempDateTime);
valueStatement = valueStatement + "STR_TO_DATE('" + dateTimeValue + "', '%d-%m-%Y'), ";
}
else if (rdr.GetDataTypeName(rdrFieldIndex) == "DATETIME")
{
tempDateTime = rdr.GetDateTime(rdrFieldIndex);
dateTimeValue = gUtil.getCustomStringFormatDate(tempDateTime);
valueStatement = valueStatement + "STR_TO_DATE('" + dateTimeValue + "', '%d-%m-%Y %H:%i'), ";
}
else
valueStatement = valueStatement + "'" + Convert.ToString(rdr.GetValue(rdrFieldIndex)) + "', ";
}
}
if (!skipAddBranchID)
{
insertStatement = insertStatement + "BRANCH_ID) VALUES(";
valueStatement = valueStatement + branchID + ");";
}
else
{
insertStatement = insertStatement.Substring(0, insertStatement.Length - 2);
insertStatement = insertStatement + ") VALUES(";
valueStatement = valueStatement.Substring(0, valueStatement.Length - 2);
valueStatement = valueStatement + ");";
}
insertStatement = insertStatement + valueStatement;
sw.WriteLine(insertStatement);
}
}
rdr.Close();
}
sw.WriteLine("");
}
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 void loadProdukData()
{
MySqlDataReader rdr;
DataTable dt = new DataTable();
string sqlCommand = "";
string namaProductParam = "";
string kodeProductParam = "";
string showactive = "";
int locationID = 0;
DS.mySqlConnect();
//if (namaProdukTextBox.Text.Equals(""))
// return;
namaProductParam = MySqlHelper.EscapeString(namaProdukTextBox.Text);
//kodeProductParam = MySqlHelper.EscapeString(textBox1.Text);
locationID = gutil.loadlocationID(2);
if (produknonactiveoption.Checked == false)
{
showactive = "AND MP.PRODUCT_ACTIVE = 1 ";
}
if (originModuleID == globalConstants.RETUR_PENJUALAN)
{
if (locationID > 0)
{
sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', PL.PRODUCT_LOCATION_QTY AS 'QTY' " +
"FROM MASTER_PRODUCT MP, SALES_DETAIL SD, PRODUCT_LOCATION PL " +
"WHERE SD.SALES_INVOICE = '" + returJualSearchParam + "' AND SD.PRODUCT_ID = MP.PRODUCT_ID AND MP.PRODUCT_IS_SERVICE = 0 " + showactive +
"AND MP.PRODUCT_ID LIKE '%" + kodeProductParam + "%' AND MP.PRODUCT_NAME LIKE '%" + namaProductParam + "%' " +
"AND PL.PRODUCT_ID = MP.PRODUCT_ID AND PL.LOCATION_ID = " + locationID +
" GROUP BY MP.PRODUCT_ID";
}
else
{
sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', MP.PRODUCT_STOCK_QTY AS 'QTY' " +
"FROM MASTER_PRODUCT MP, SALES_DETAIL SD " +
"WHERE SD.SALES_INVOICE = '" + returJualSearchParam + "' AND SD.PRODUCT_ID = MP.PRODUCT_ID AND MP.PRODUCT_IS_SERVICE = 0 " + showactive +
"AND MP.PRODUCT_ID LIKE '%" + kodeProductParam + "%' AND MP.PRODUCT_NAME LIKE '%" + namaProductParam + "%'" +
" GROUP BY MP.PRODUCT_ID";
}
}
else if (originModuleID == globalConstants.RETUR_PENJUALAN_STOCK_ADJUSTMENT)
{
if (locationID > 0)
{
sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', PL.PRODUCT_LOCATION_QTY AS 'QTY' " +
"FROM MASTER_PRODUCT MP, SALES_DETAIL SD, SALES_HEADER SH, PRODUCT_LOCATION PL " +
"WHERE SH.SALES_INVOICE = SD.SALES_INVOICE AND SD.PRODUCT_ID = MP.PRODUCT_ID AND SH.CUSTOMER_ID = " + Convert.ToInt32(returJualSearchParam) + " AND MP.PRODUCT_IS_SERVICE = 0 " + showactive +
"AND MP.PRODUCT_ID LIKE '%" + kodeProductParam + "%' AND MP.PRODUCT_NAME LIKE '%" + namaProductParam + "%' " +
"AND PL.PRODUCT_ID = MP.PRODUCT_ID AND PL.LOCATION_ID = " + locationID +
" GROUP BY MP.PRODUCT_ID";
}
else
{
sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', MP.PRODUCT_STOCK_QTY AS 'QTY' " +
"FROM MASTER_PRODUCT MP, SALES_DETAIL SD, SALES_HEADER SH " +
"WHERE SH.SALES_INVOICE = SD.SALES_INVOICE AND SD.PRODUCT_ID = MP.PRODUCT_ID AND SH.CUSTOMER_ID = " + Convert.ToInt32(returJualSearchParam) + " AND MP.PRODUCT_IS_SERVICE = 0 " + showactive +
"AND MP.PRODUCT_ID LIKE '%" + kodeProductParam + "%' AND MP.PRODUCT_NAME LIKE '%" + namaProductParam + "%'" +
" GROUP BY MP.PRODUCT_ID";
}
}
else if (originModuleID == globalConstants.RETUR_PEMBELIAN)
{
if (locationID > 0)
{
sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', PL.PRODUCT_LOCATION_QTY AS 'QTY' FROM MASTER_PRODUCT MP, PRODUCT_LOCATION PL WHERE MP.PRODUCT_IS_SERVICE = 0 " + showactive + "AND (MP.PRODUCT_STOCK_QTY - MP.PRODUCT_LIMIT_STOCK > 0) AND PL.PRODUCT_ID = MP.PRODUCT_ID AND PL.LOCATION_ID = " + locationID + " ORDER BY MP.PRODUCT_NAME ASC";
}
else
{
sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', MP.PRODUCT_STOCK_QTY AS 'QTY' FROM MASTER_PRODUCT MP WHERE MP.PRODUCT_IS_SERVICE = 0 " + showactive + "AND (MP.PRODUCT_STOCK_QTY - MP.PRODUCT_LIMIT_STOCK > 0) ORDER BY MP.PRODUCT_NAME ASC";
}
}
else if (originModuleID == globalConstants.PENERIMAAN_BARANG || (originModuleID == globalConstants.NEW_PURCHASE_ORDER) || (originModuleID == globalConstants.NEW_REQUEST_ORDER) || (originModuleID == globalConstants.PRE_ORDER_SALES) || (originModuleID == globalConstants.CASHIER_MODULE))
{
if (locationID > 0)
{
sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', PL.PRODUCT_LOCATION_QTY AS 'QTY' FROM MASTER_PRODUCT MP, PRODUCT_LOCATION PL WHERE MP.PRODUCT_ID LIKE '%" + kodeProductParam + "%' AND MP.PRODUCT_NAME LIKE '%" + namaProductParam + "%'" + showactive + " AND PL.PRODUCT_ID = MP.PRODUCT_ID AND PL.LOCATION_ID = " + locationID;
}
else
{
sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', MP.PRODUCT_STOCK_QTY AS 'QTY' FROM MASTER_PRODUCT MP WHERE MP.PRODUCT_ID LIKE '%" + kodeProductParam + "%' AND MP.PRODUCT_NAME LIKE '%" + namaProductParam + "%'" + showactive;
}
}
//else if ((originModuleID == globalConstants.CASHIER_MODULE))
//{
// if (locationID > 0)
// sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', PL.PRODUCT_LOCATION_QTY AS 'QTY' FROM MASTER_PRODUCT MP, PRODUCT_LOCATION PL WHERE MP.PRODUCT_STOCK_QTY > 0 AND MP.PRODUCT_ID LIKE '%" + kodeProductParam + "%' AND MP.PRODUCT_NAME LIKE '%" + namaProductParam + "%'" + showactive + " AND PL.PRODUCT_ID = MP.PRODUCT_ID AND PL.LOCATION_ID = " + locationID;
// else
// sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', MP.PRODUCT_STOCK_QTY AS 'QTY' FROM MASTER_PRODUCT MP WHERE MP.PRODUCT_STOCK_QTY > 0 AND MP.PRODUCT_ID LIKE '%" + kodeProductParam + "%' AND MP.PRODUCT_NAME LIKE '%" + namaProductParam + "%'" + showactive;
//}
else
{
if (locationID > 0)
{
sqlCommand = "SELECT MP.ID, MP.PRODUCT_ID AS 'PRODUK ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', MP.PRODUCT_DESCRIPTION AS 'DESKRIPSI PRODUK', PL.PRODUCT_LOCATION_QTY AS 'QTY' FROM MASTER_PRODUCT MP, PRODUCT_LOCATION PL WHERE MP.PRODUCT_ACTIVE = 1 AND MP.PRODUCT_IS_SERVICE = 0 AND MP.PRODUCT_NAME LIKE '%" + namaProductParam + "%' AND PL.PRODUCT_ID = MP.PRODUCT_ID AND PL.LOCATION_ID = " + locationID;
}
else
{
sqlCommand = "SELECT ID, PRODUCT_ID AS 'PRODUK ID', PRODUCT_NAME AS 'NAMA PRODUK', PRODUCT_DESCRIPTION AS 'DESKRIPSI PRODUK' FROM MASTER_PRODUCT WHERE PRODUCT_ACTIVE = 1 AND PRODUCT_IS_SERVICE = 0 AND PRODUCT_NAME LIKE '%" + namaProductParam + "%'";
}
}
if (originModuleID == globalConstants.STOK_PECAH_BARANG)
{
sqlCommand = sqlCommand + " AND PRODUCT_IS_SERVICE = 0";
}
using (rdr = DS.getData(sqlCommand))
{
if (rdr.HasRows)
{
dt.Load(rdr);
dataProdukGridView.DataSource = dt;
dataProdukGridView.Columns["ID"].Visible = false;
//dataProdukGridView.Columns["PRODUK ID"].Width = 200;
dataProdukGridView.Columns["PRODUK ID"].Visible = false;
dataProdukGridView.Columns["NAMA PRODUK"].Width = 200;
//dataProdukGridView.Columns["DESKRIPSI PRODUK"].Width = 300;
}
}
}
private int calculatePaperLength()
{
int result = 0;
MySqlDataReader rdr;
//event printing
string dateFrom = String.Format(culture, "{0:yyyyMMdd}", DateTime.Now);
Font font = new Font("Courier New", 9);
int rowheight = (int)Math.Ceiling(font.GetHeight());
int add_offset = rowheight;
int Offset = 5;
int totalLength = Offset;
string sqlCommand = "";
Offset = Offset + add_offset;
Offset = Offset + add_offset;
Offset = Offset + add_offset;
Offset = Offset + add_offset;
DS.mySqlConnect();
sqlCommand = "SELECT MP.PRODUCT_NAME, IFNULL(PD.PRODUCT_SOLD_QTY, 0) AS PRODUCT_SOLD_QTY, IFNULL((PRODUCT_LAST_STOCK_QTY - PRODUCT_RIIL_QTY), 0) AS SELISIH " +
"FROM MASTER_PRODUCT MP, PRODUCT_DAILY_ADJUSTMENT_HEADER PH, PRODUCT_DAILY_ADJUSTMENT_DETAIL PD " +
"WHERE PD.PRODUCT_ID = MP.PRODUCT_ID AND DATE_FORMAT(PH.PRODUCT_ADJUSTMENT_DATE, '%Y%m%d') = '" + dateFrom + "' AND PD.PRODUCT_ADJUSTMENT_ID = PH.PRODUCT_ADJUSTMENT_ID";
using (rdr = DS.getData(sqlCommand))
{
if (rdr.HasRows)
{
int i = 0;
while (rdr.Read())
{
Offset = Offset + add_offset;
}
}
}
DS.mySqlClose();
Offset = Offset + add_offset;
Offset = Offset + add_offset;
Offset = Offset + add_offset;
Offset = Offset + add_offset;
// =========================================================================
Offset = Offset + add_offset;
Offset = Offset + add_offset;
// =========================================================================
Offset = Offset + add_offset;
Offset = Offset + add_offset;
// =========================================================================
Offset = Offset + add_offset;
Offset = Offset + add_offset;
// =========================================================================
Offset = Offset + add_offset;
Offset = Offset + add_offset;
// =========================================================================
Offset = Offset + add_offset;
Offset = Offset + add_offset;
// =========================================================================
Offset = Offset + add_offset;
Offset = Offset + add_offset;
Offset = Offset + add_offset;
Offset = Offset + add_offset;
Offset = Offset + add_offset;
Offset = Offset + add_offset;
Offset = Offset + add_offset;
Offset = Offset + add_offset;
Offset = Offset + add_offset;
//DS.mySqlConnect();
//sqlCommand = "SELECT DJ.JOURNAL_DESCRIPTION, IFNULL(DJ.JOURNAL_NOMINAL, 0) AS NOMINAL " +
// "FROM DAILY_JOURNAL DJ, MASTER_ACCOUNT MA " +
// "WHERE DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d') = '" + dateFrom + "' AND DJ.ACCOUNT_ID > 3 AND MA.ACCOUNT_TYPE_ID = 2 AND DJ.ACCOUNT_ID = MA.ACCOUNT_ID";
//using (rdr = DS.getData(sqlCommand))
//{
// if (rdr.HasRows)
// {
// int i = 0;
// while (rdr.Read())
// {
// Offset = Offset + add_offset;
// }
// }
//}
//DS.mySqlClose();
Offset = Offset + add_offset;
Offset = Offset + add_offset;
Offset = Offset + add_offset;
Offset = Offset + add_offset;
result = totalLength + Offset + add_offset + add_offset;
return result;
}
private string createUpdateQueryDataCabang(int branchID, string tableName, string PKField, List <string> fieldToBackup, string ipServerBranch = "")
{
Data_Access DS_BRANCH = new Data_Access();
//bool result = false;
string sqlCommand = "";
MySqlDataReader rdr;
string fieldName;
object DBValue = null;
int rdrFieldIndex = 0;
int startIndex = 0;
StreamWriter sw = null;
string fileName = "";
string commandStatement = "";
string valueStatement = "";
string fieldValueForPK = "";
fileName = "EXPORT_" + branchID + "_DATA_" + tableName + ".sql";
if (!File.Exists(fileName))
{
sw = File.CreateText(fileName);
}
else
{
File.Delete(fileName);
sw = File.CreateText(fileName);
}
// CONNECT TO BRANCH
if (DS_BRANCH.Branch_mySQLConnect(branchID, ipServerBranch))
{
sqlCommand = "SELECT * FROM " + tableName;
using (rdr = DS_BRANCH.getData(sqlCommand, false, true))
{
if (rdr.HasRows)
{
while (rdr.Read())
{
commandStatement = "UPDATE " + tableName + " SET ";
valueStatement = "";
for (rdrFieldIndex = startIndex; rdrFieldIndex < rdr.FieldCount; rdrFieldIndex++)
{
DBValue = rdr.GetValue(rdrFieldIndex);
if (DBValue.ToString().Length > 0)
{
fieldName = rdr.GetName(rdrFieldIndex);
if (!fieldToSkip.Contains(fieldName) && fieldName != PKField)
{
continue;
}
if (fieldName != PKField)
{
valueStatement = valueStatement + fieldName + " = " + "'" + Convert.ToString(rdr.GetValue(rdrFieldIndex)) + "', ";
}
else
{
fieldValueForPK = Convert.ToString(rdr.GetValue(rdrFieldIndex));
}
}
}
valueStatement = valueStatement.Substring(0, valueStatement.Length - 2);
commandStatement = commandStatement + valueStatement;
commandStatement = commandStatement + " WHERE " + PKField + " = '" + fieldValueForPK + "';";
sw.WriteLine(commandStatement);
}
}
}
rdr.Close();
// CLOSE BRANCH CONNECTION
DS_BRANCH.Branch_mySqlClose();
//result = true;
}
else
{
gUtil.saveSystemDebugLog(0, "[SYNC CABANG] FAILED TO CONNECT TO CABANG");
}
sw.Close();
return(fileName);
}
private void loadInvoiceData()
{
string sqlCommand;
MySqlDataReader rdr;
DataTable dt = new DataTable();
DataGridViewTextBoxColumn qtyColumn = new DataGridViewTextBoxColumn();
// LOAD DATA HEADER
sqlCommand = "SELECT SH.SALES_INVOICE, IFNULL(MC.CUSTOMER_FULL_NAME, 'P-UMUM') AS CUSTOMER_NAME, IFNULL(SALES_ORDER_DELIVERED_ADDRESS, '') AS REMARK FROM SALES_HEADER SH LEFT OUTER JOIN MASTER_CUSTOMER MC ON (SH.CUSTOMER_ID = MC.CUSTOMER_ID) WHERE SH.SALES_INVOICE = '" + selectedSalesInvoice + "' AND SH.BRANCH_ID = " + branchID;
using (rdr = DS.getData(sqlCommand))
{
if (rdr.HasRows)
{
while (rdr.Read())
{
noInvoiceTextBox.Text = rdr.GetString("SALES_INVOICE");
customerNameTextBox.Text = rdr.GetString("CUSTOMER_NAME");
instructionTextBox.Text = rdr.GetString("REMARK");
}
}
}
rdr.Close();
// LOAD DATA DETAIL
sqlCommand = "SELECT SD.ID, SD.PRODUCT_ID, SD.IS_COMPLETED, IF(SD.IS_COMPLETED = 1, 'COMPLETED', 'PENDING') AS STATUS, MP.PRODUCT_NAME AS 'NAMA PRODUK', SD.PRODUCT_QTY AS 'ORDER QTY', IFNULL(TAB1.QTY, 0) + IFNULL(TAB2.QTY, 0) AS 'DELIVERED QTY' " +
"FROM SALES_DETAIL SD LEFT OUTER JOIN " +
"(SELECT DH.SALES_INVOICE, DD.PRODUCT_ID, SUM(DD.PRODUCT_QTY) AS QTY FROM DELIVERY_ORDER_HEADER DH, DELIVERY_ORDER_DETAIL DD WHERE DD.DO_ID = DH.DO_ID AND DH.SALES_INVOICE = '" + selectedSalesInvoice + "' GROUP BY DD.PRODUCT_ID) TAB1 ON (TAB1.PRODUCT_ID = SD.PRODUCT_ID) " +
"LEFT OUTER JOIN (SELECT SH.BRANCH_ID, SH.REFERENCE_SO, SDF.PRODUCT_ID, SUM(SDF.PRODUCT_QTY) AS QTY FROM SALES_DETAIL_FULFILLMENT SDF, SALES_HEADER SH WHERE SDF.SALES_INVOICE = SH.SALES_INVOICE AND SH.REFERENCE_SO = '" + selectedSalesInvoice + "' GROUP BY SH.BRANCH_ID, SH.REFERENCE_SO, SDF.PRODUCT_ID) TAB2 ON (TAB2.PRODUCT_ID = SD.PRODUCT_ID) " +
", MASTER_PRODUCT MP " +
"WHERE SD.PRODUCT_ID = MP.PRODUCT_ID AND SD.SALES_INVOICE = '" + selectedSalesInvoice + "' AND SD.BRANCH_ID = " + branchID;
isLoading = true;
using (rdr = DS.getData(sqlCommand))
{
if (rdr.HasRows)
{
dt.Load(rdr);
detailGridView.DataSource = dt;
detailGridView.Columns["ID"].Visible = false;
detailGridView.Columns["PRODUCT_ID"].Visible = false;
detailGridView.Columns["IS_COMPLETED"].Visible = false;
detailGridView.Columns["STATUS"].ReadOnly = true;
detailGridView.Columns["NAMA PRODUK"].Width = 200;
detailGridView.Columns["NAMA PRODUK"].ReadOnly = true;
detailGridView.Columns["ORDER QTY"].Width = 200;
detailGridView.Columns["ORDER QTY"].ReadOnly = true;
detailGridView.Columns["DELIVERED QTY"].Width = 200;
detailGridView.Columns["DELIVERED QTY"].ReadOnly = true;
qtyColumn.Name = "qty";
qtyColumn.HeaderText = "QTY";
qtyColumn.Width = 200;
detailGridView.Columns.Add(qtyColumn);
for (int i = 0; i < detailGridView.Rows.Count; i++)
{
if (detailGridView.Rows[i].Cells["IS_COMPLETED"].Value.ToString().Equals("1"))
{
detailGridView.Rows[i].Cells["QTY"].ReadOnly = true;
}
else
{
detailGridView.Rows[i].Cells["QTY"].Style.BackColor = Color.LightBlue;
}
detailGridView.Rows[i].Cells["QTY"].Value = 0;
//deliveryQty.Add("0");
}
}
}
rdr.Close();
isLoading = false;
}
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;
dataRequestOrderGridView.Columns["TOTAL"].DefaultCellStyle.FormatProvider = culture;
dataRequestOrderGridView.Columns["TOTAL"].DefaultCellStyle.Format = "C2";
}
rdr.Close();
}
}
private void writeTableContentToInsertStatement(string tableName, StreamWriter sw, Data_Access DAccess,
bool skipAddBranchID = false, string sqlParam = "", string customTableName = "", string fieldForPK = "", string fieldValueForPK = "", bool forcedInsert = false)
{
string sqlCommand = "";
MySqlDataReader rdr;
string valueStatement = "";
int rdrFieldIndex = 0;
int startIndex = 0;
DateTime tempDateTime;
string dateTimeValue;
object DBValue = null;
int branchID = 0;
string namaCabang = "";
int editFlag = 1;
string commandStatement = "";
string fieldName = "";
branchID = gUtil.loadbranchID(2, out namaCabang);
if (sqlParam.Length <= 0) // EMPTY SQL PARAM MEANS, READ FROM EDIT FLAG
{
if (forcedInsert == false)
{
sqlCommand = "SELECT * FROM " + tableName + " WHERE EDITED <> 0";
}
else
{
sqlCommand = "SELECT * FROM " + tableName + " WHERE 1 = 1";
}
if (fieldValueForPK.Length > 0)
{
sqlCommand = sqlCommand + " AND " + fieldForPK + " = '" + fieldValueForPK + "'";
}
}
else
{
sqlCommand = sqlParam;
}
sw.WriteLine("");
using (rdr = DAccess.getData(sqlCommand))
{
if (rdr.HasRows)
{
while (rdr.Read())
{
if (forcedInsert == false)
{
if (sqlParam.Length <= 0)
{
editFlag = rdr.GetInt32("EDITED");
}
}
if (editFlag == 1)
{
commandStatement = "INSERT INTO " + tableName + "(";
}
else
{
commandStatement = "UPDATE " + tableName + " SET ";
}
valueStatement = "";
for (rdrFieldIndex = startIndex; rdrFieldIndex < rdr.FieldCount; rdrFieldIndex++)
{
DBValue = rdr.GetValue(rdrFieldIndex);
if (DBValue.ToString().Length > 0)
{
fieldName = rdr.GetName(rdrFieldIndex);
if (editFlag == 2 && fieldName == fieldForPK)
{
continue;
}
if (fieldToSkip.Contains(fieldName))
{
continue;
}
if (editFlag == 1) // INSERT STATEMENT
{
commandStatement = commandStatement + fieldName + ", ";
}
else // UPDATE STATEMENT
{
valueStatement = valueStatement + fieldName + " = ";
}
if (rdr.GetDataTypeName(rdrFieldIndex) == "DATE")
{
tempDateTime = rdr.GetDateTime(rdrFieldIndex);
dateTimeValue = String.Format(culture, "{0:dd-MM-yyyy}", tempDateTime);
valueStatement = valueStatement + "STR_TO_DATE('" + dateTimeValue + "', '%d-%m-%Y'), ";
}
else if (rdr.GetDataTypeName(rdrFieldIndex) == "DATETIME")
{
tempDateTime = rdr.GetDateTime(rdrFieldIndex);
dateTimeValue = gUtil.getCustomStringFormatDate(tempDateTime);
valueStatement = valueStatement + "STR_TO_DATE('" + dateTimeValue + "', '%d-%m-%Y %H:%i'), ";
}
else
{
valueStatement = valueStatement + "'" + Convert.ToString(rdr.GetValue(rdrFieldIndex)) + "', ";
}
}
}
if (!skipAddBranchID)
{
if (editFlag == 1)
{
commandStatement = commandStatement + "BRANCH_ID) VALUES(";
valueStatement = valueStatement + branchID + ");";
}
else
{
valueStatement = valueStatement + "BRANCH_ID = " + branchID;
}
}
else
{
if (editFlag == 1)
{
commandStatement = commandStatement.Substring(0, commandStatement.Length - 2);
commandStatement = commandStatement + ") VALUES(";
valueStatement = valueStatement.Substring(0, valueStatement.Length - 2);
valueStatement = valueStatement + ");";
}
else
{
valueStatement = valueStatement.Substring(0, valueStatement.Length - 2);
}
}
commandStatement = commandStatement + valueStatement;
if (editFlag == 2 && fieldForPK.Length > 0 && fieldValueForPK.Length > 0)
{
commandStatement = commandStatement + " WHERE " + fieldForPK + " = '" + fieldValueForPK + "';";
}
//else
// commandStatement = commandStatement + ";";
sw.WriteLine(commandStatement);
}
}
rdr.Close();
}
sw.WriteLine("");
}
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 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;
case globalConstants.MENU_SALES_QUOTATION:
messageContent = "QUOTATION [" + param1 + "] DIPESAN TGL " + param2;
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 loadDataStockTake()
{
string selectedDate = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(stockTakeDateTimePicker.Value));
string sqlCommand = "";
MySqlDataReader rdr;
//DataTable dt = new DataTable();
if (detailDataGrid.ColumnCount <= 0)
{
return;
}
// CHECK WHETHER DATA HAS BEEN SAVED BEFORE OR NOT
sqlCommand = "SELECT COUNT(1) FROM PRODUCT_DAILY_ADJUSTMENT_HEADER WHERE DATE_FORMAT(PRODUCT_ADJUSTMENT_DATE, '%Y%m%d') = '" + selectedDate + "'";
if (Convert.ToInt32(DS.getDataSingleValue(sqlCommand)) > 0)
{
int userAccessOption = DS.getUserAccessRight(globalConstants.REVISI_STOCK_TAKE_HARIAN, gUtil.getUserGroupID());
if (userAccessOption == 1)
{
sqlCommand = "SELECT PRODUCT_ADJUSTMENT_ID FROM PRODUCT_DAILY_ADJUSTMENT_HEADER WHERE DATE_FORMAT(PRODUCT_ADJUSTMENT_DATE, '%Y%m%d') = '" + selectedDate + "'";
globalProductAdjustmentID = DS.getDataSingleValue(sqlCommand).ToString();
moduleID = EDIT_DAILY_STOCK_TAKE;
sqlCommand = "SELECT STOCK_TAKE_CLOSED FROM PRODUCT_DAILY_ADJUSTMENT_HEADER WHERE DATE_FORMAT(PRODUCT_ADJUSTMENT_DATE, '%Y%m%d') = '" + selectedDate + "'";
stockTakeCloseStatus = Convert.ToInt32(DS.getDataSingleValue(sqlCommand));
if (stockTakeCloseStatus == 1)
{
allowToEdit = false;
detailDataGrid.ReadOnly = true;
label3.Text = "CLOSED";
label3.Visible = true;
}
else
{
allowToEdit = true;
}
}
else
{
moduleID = EDIT_DAILY_STOCK_TAKE;
allowToEdit = false;
detailDataGrid.ReadOnly = true;
}
}
switch (moduleID)
{
case NEW_DAILY_STOCK_TAKE:
sqlCommand = "SELECT MP.PRODUCT_ID, MP.PRODUCT_NAME AS ROTI, MP.PRODUCT_STOCK_AWAL AS AWAL, (IFNULL(TAB1.TOTAL_RECEIVED, 0)-IFNULL(TAB4.TOTAL_RECEIVED_RETURN, 0)) AS PRODUKSI, '' AS REMARK, '0' AS BS, MP.PRODUCT_STOCK_QTY AS AKHIR, (IFNULL(TAB2.TOTAL_SALES, 0) + IFNULL(TAB_DO.TOTAL_DELIVERED, 0) - IFNULL(TAB3.TOTAL_SALES_RETURN, 0)) AS LAKU, " +
"IF(MP.PRODUCT_STOCK_AWAL > 0, MP.PRODUCT_STOCK_QTY - (MP.PRODUCT_STOCK_AWAL+ (IFNULL(TAB1.TOTAL_RECEIVED, 0)-IFNULL(TAB4.TOTAL_RECEIVED_RETURN, 0)) - (IFNULL(TAB2.TOTAL_SALES, 0)-IFNULL(TAB3.TOTAL_SALES_RETURN, 0))), '0') AS PENYESUAIAN, " +
"'0' AS RIILQTY " +
"FROM MASTER_PRODUCT MP LEFT OUTER JOIN " +
"(SELECT PRODUCT_ID, SUM(PRODUCT_ACTUAL_QTY) AS TOTAL_RECEIVED FROM PRODUCTS_RECEIVED_HEADER PRH, PRODUCTS_RECEIVED_DETAIL PRD WHERE PRD.PR_INVOICE = PRH.PR_INVOICE AND DATE_FORMAT(PRH.PR_DATE , '%Y%m%d') = '" + selectedDate + "' GROUP BY PRODUCT_ID) TAB1 ON TAB1.PRODUCT_ID = MP.PRODUCT_ID ";
if (gUtil.isServerApp() == 0)
{
sqlCommand = sqlCommand + "LEFT OUTER JOIN (SELECT PRODUCT_ID, SUM(PRODUCT_QTY) AS TOTAL_SALES FROM SALES_HEADER SH, SALES_DETAIL SD WHERE SH.SALES_TOP = 1 AND SD.SALES_INVOICE = SH.SALES_INVOICE AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') = '" + selectedDate + "' GROUP BY PRODUCT_ID) TAB2 ON TAB2.PRODUCT_ID = MP.PRODUCT_ID " +
"LEFT OUTER JOIN (SELECT '0' AS PRODUCT_ID, 0 AS TOTAL_DELIVERED) TAB_DO ON TAB_DO.PRODUCT_ID = MP.PRODUCT_ID ";
}
else
{
sqlCommand = sqlCommand + "LEFT OUTER JOIN(SELECT PRODUCT_ID, SUM(PRODUCT_QTY) AS TOTAL_SALES FROM SALES_HEADER SH, SALES_DETAIL SD WHERE SH.SALES_TOP = 1 AND SD.SALES_INVOICE = SH.SALES_INVOICE AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') = '" + selectedDate + "' GROUP BY PRODUCT_ID) TAB2 ON TAB2.PRODUCT_ID = MP.PRODUCT_ID " +
"LEFT OUTER JOIN(SELECT DD.PRODUCT_ID, SUM(DD.PRODUCT_QTY) AS TOTAL_DELIVERED FROM DELIVERY_ORDER_HEADER DH, DELIVERY_ORDER_DETAIL DD WHERE DATE_FORMAT(DH.DO_DATE, '%Y%m%d') = '" + selectedDate + "' GROUP BY PRODUCT_ID) TAB_DO ON TAB_DO.PRODUCT_ID = MP.PRODUCT_ID ";
}
sqlCommand = sqlCommand + "LEFT OUTER JOIN(SELECT PRODUCT_ID, SUM(PRODUCT_RETURN_QTY) AS TOTAL_SALES_RETURN FROM RETURN_SALES_HEADER RSH, RETURN_SALES_DETAIL RSD WHERE RSD.RS_INVOICE = RSH.RS_INVOICE AND DATE_FORMAT(RSH.RS_DATETIME, '%Y%m%d') = '" + selectedDate + "' GROUP BY PRODUCT_ID) TAB3 ON TAB3.PRODUCT_ID = MP.PRODUCT_ID " +
"LEFT OUTER JOIN(SELECT PRODUCT_ID, SUM(PRODUCT_QTY) AS TOTAL_RECEIVED_RETURN FROM RETURN_PURCHASE_HEADER RPH, RETURN_PURCHASE_DETAIL RPD WHERE RPD.RP_ID = RPH.RP_ID AND DATE_FORMAT(RPH.RP_DATE, '%Y%m%d') = '" + selectedDate + "' GROUP BY PRODUCT_ID) TAB4 ON TAB4.PRODUCT_ID = MP.PRODUCT_ID " +
"WHERE MP.PRODUCT_ACTIVE = 1";
break;
case EDIT_DAILY_STOCK_TAKE:
sqlCommand = "SELECT PAD.PRODUCT_ID, MP.PRODUCT_NAME AS ROTI, PAD.PRODUCT_LAST_STOCK_QTY AS AWAL, PAD.PRODUCT_RECEIVED_QTY AS PRODUKSI, PAD.REMARKS AS REMARK, PAD.PRODUCT_BS_QTY AS BS, PAD.PRODUCT_LEFTOVER_QTY AS AKHIR, PAD.PRODUCT_SOLD_QTY AS LAKU, PAD.PRODUCT_ADJUSTMENT_QTY AS PENYESUAIAN, PAD.PRODUCT_RIIL_QTY AS RIILQTY " +
"FROM PRODUCT_DAILY_ADJUSTMENT_HEADER PAH, PRODUCT_DAILY_ADJUSTMENT_DETAIL AS PAD, MASTER_PRODUCT MP " +
"WHERE PAD.PRODUCT_ADJUSTMENT_ID = PAH.PRODUCT_ADJUSTMENT_ID AND PAD.PRODUCT_ID = MP.PRODUCT_ID AND DATE_FORMAT(PAH.PRODUCT_ADJUSTMENT_DATE, '%Y%m%d') = '" + selectedDate + "'";
break;
}
using (rdr = DS.getData(sqlCommand))
{
detailDataGrid.Rows.Clear();
if (rdr.HasRows)
{
while (rdr.Read())
{
detailDataGrid.Rows.Add(rdr.GetString("PRODUCT_ID"),
detailDataGrid.Rows.Count + 1,
rdr.GetString("ROTI"),
rdr.GetString("AWAL"),
rdr.GetString("PRODUKSI"),
rdr.GetString("LAKU"),
rdr.GetString("PENYESUAIAN"),
rdr.GetString("BS"),
rdr.GetString("AKHIR"),
rdr.GetString("RIILQTY"),
rdr.GetString("REMARK"),
rdr.GetString("AKHIR")
);
BSQty.Add(rdr.GetString("BS"));
//calculateAkhirValue(detailDataGrid.Rows.Count-1);
}
}
}
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");
//hargaBSTextBox.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)
{
}
}
}
}
}
}
