//============FUNGSI CEK TABEL ARTICLE BERDASARKAN ARTICLE ID YANG TELAH DI SCAN==
public void cek_article()
{
CRUD sql = new CRUD();
int total_amount_new;
//ckon.con.Close();
//String sql = "SELECT * FROM article WHERE ARTICLE_ID = '" + t_barcode.Text + "'";
try
{
ckon.sqlCon().Open();
String cmd = "Select article._id, article.ARTICLE_ID, article.UNIT, article.PRICE, inventory.GOOD_QTY FROM article INNER JOIN inventory ON article._id = inventory.ARTICLE_ID WHERE inventory.GOOD_QTY >=1 AND article.ARTICLE_ID = '" + t_barcode.Text + "'";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
id_inv = ckon.sqlDataRd["_id"].ToString();
art_id = ckon.sqlDataRd["ARTICLE_ID"].ToString();
unit = ckon.sqlDataRd["UNIT"].ToString();
price = Convert.ToInt32(ckon.sqlDataRd["PRICE"].ToString());
good_qty = ckon.sqlDataRd["GOOD_QTY"].ToString();
}
total_amount_new = price * Convert.ToInt32(good_qty);
String cmd_roLine = "SELECT * FROM returnorder_line WHERE RETURN_ORDER_ID = '" + l_transaksi.Text + "' AND ARTICLE_ID = '" + art_id + "'";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd_roLine, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
MessageBox.Show("This Article Has Been Entered");
}
else
{
String cmd_insert = "INSERT INTO returnorder_line (RETURN_ORDER_ID,ARTICLE_ID,QUANTITY,UNIT, SUBTOTAL) VALUES ('" + l_transaksi.Text + "','" + art_id + "', '" + good_qty + "', '" + unit + "','" + total_amount_new + "')";
sql.ExecuteNonQuery(cmd_insert);
}
}
else
{
MessageBox.Show("Article Not Found Or Quantity Empty");
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//MENDAPATKAN CURRENCY DARI TABEL CURRENCY
public void get_currency()
{
string command;
//ckon.con.Close();
//String sql = "SELECT * FROM currency";
//ckon.con.Open();
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//ckon.myReader = ckon.cmd.ExecuteReader();
//while (ckon.myReader.Read())
//{
// sign = ckon.myReader.GetString("NAME");
//}
//ckon.con.Close();
try
{
ckon.sqlCon().Open();
command = "SELECT * FROM currency";
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
sign = ckon.sqlDataRd["SIGN"].ToString();
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
if (sign == "IDR")
{
mata_uang = "Rp";
}
if (sign == "SGD")
{
mata_uang = "$";
}
if (sign == "MYR")
{
mata_uang = "RM";
}
}
//================DATAGRIDVIEW TO DATABASE =========================================
public void cek_article2()
{
new_price = int.Parse(S_price);
string j = "1";
//i = 0;
try
{
ckon.sqlCon().Open();
string command = "SELECT * FROM transaction_line WHERE TRANSACTION_ID ='" + t_id + "' AND ARTICLE_ID='" + S_ID + "'";
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
new_jumlah = Convert.ToInt32(ckon.sqlDataRd["QUANTITY"].ToString());
new_harga = Convert.ToInt32(ckon.sqlDataRd["SUBTOTAL"].ToString());
new_disc = Convert.ToInt32(ckon.sqlDataRd["DISCOUNT"].ToString());
}
new_price = new_price - disc;
new_jumlah = new_jumlah + 1;
new_harga = new_harga + new_price;
new_disc = new_disc + disc;
String sql_transLine = "UPDATE transaction_line SET QUANTITY='" + new_jumlah + "',DISCOUNT='" + new_disc + "' ,SUBTOTAL='" + new_harga + "' WHERE TRANSACTION_ID='" + t_id + "' AND ARTICLE_ID='" + S_ID + "'";
CRUD update = new CRUD();
update.ExecuteNonQuery(sql_transLine);
}
else
{
int convert_harga;//convert harga menjadi integer//Jika diskon tidak ada, maka subtotal dikurangi diskon
convert_harga = Int32.Parse(S_price);
new_harga = convert_harga - disc;
String sql_transLine = "INSERT INTO transaction_line (TRANSACTION_ID,ARTICLE_ID,QUANTITY,PRICE,DISCOUNT,SUBTOTAL, SPG_ID, DISCOUNT_CODE,DISCOUNT_TYPE,DISCOUNT_DESC) VALUES ('" + t_id + "','" + S_ID + "', '" + j + "', '" + S_price + "', '" + disc + "' ,'" + new_harga + "', '" + id_spg + "','" + disc_code + "','" + disc_type + "','" + disc_desc + "')";
CRUD input = new CRUD();
input.ExecuteNonQuery(sql_transLine);
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//=================BERGUNA UNTUK MENGAMBIL CODE STORE===============
public void get_id_store()
{
try
{
ckon.sqlCon().Open();
String command = "SELECT * FROM store";
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
store_code = ckon.sqlDataRd["CODE"].ToString();
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
public void runRetrive()
{
//ckon.con.Close();
String command = "SELECT TOP 100 * FROM ho_header where STATUS='0' AND EMPLOYEE_ID='" + epy_id + "' ORDER BY _id ASC";
try
{
ckon.sqlCon().Open();
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
l_transaksi.Text = ckon.sqlDataRd["MUTASI_ORDER_ID"].ToString();
}
}
else
{
set_running_number();
save_trans_header();
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//ckon.con.Open();
//ckon.myReader = ckon.cmd.ExecuteReader();
//if (ckon.myReader.HasRows)
//{
// while (ckon.myReader.Read())
// {
// l_transaksi.Text = ckon.myReader.GetString("MUTASI_ORDER_ID");
// }
//}
//else
//{
// set_running_number();
// save_trans_header();
//}
retreive();
qty();
setFocus();
//ckon.con.Close();
}
//=============================================================================================
//====================GET DATA FROM ID =======================================================
public void get_data()
{
CRUD sql = new CRUD();
int amount;
try
{
ckon.sqlCon().Open();
String cmd = "SELECT * FROM returnorder WHERE RETURN_ORDER_ID ='" + l_transaksi.Text + "'";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
remark = ckon.sqlDataRd["REMARK"].ToString();
qty2 = ckon.sqlDataRd["TOTAL_QTY"].ToString();
amount = Convert.ToInt32(ckon.sqlDataRd["TOTAL_AMOUNT"].ToString());
l_amount.Text = string.Format("{0:#,###}" + ",00", amount);
l_no_sj.Text = ckon.sqlDataRd["NO_SJ"].ToString();
}
}
}
catch (Exception e)
{
MessageBox.Show("No connection to database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//try
//{
// ckon.con.Open();
// ckon.myReader = ckon.cmd.ExecuteReader();
// while (ckon.myReader.Read())
// {
// remark = ckon.myReader.GetString("REMARK");
// qty2 = ckon.myReader.GetString("TOTAL_QTY");
// amount = ckon.myReader.GetInt32("TOTAL_AMOUNT");
// l_amount.Text = string.Format("{0:#,###}" + ",00", amount);
// l_no_sj.Text = ckon.myReader.GetString("NO_SJ");
// }
// ckon.con.Close();
//}
//catch
//{ }
l_remark.Text = remark;
l_quantity.Text = qty2;
}
//==============================================================================================
//===============================================================================================
//====================GET DATA FROM ID =======================================================
public void get_data()
{
CRUD sql = new CRUD();
try
{
ckon.sqlCon().Open();
String cmd = "SELECT * FROM returnorder WHERE RETURN_ORDER_ID ='" + l_transaksi.Text + "'";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
no_sj.Text = ckon.sqlDataRd["NO_SJ"].ToString();
}
}
}
catch (Exception e)
{
MessageBox.Show("No connection to database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
public void updateInventArticle()
{
CRUD sql = new CRUD();
int inventArtId;
try
{
ckon.sqlCon().Open();
String cmd = "SELECT ARTICLE_ID FROM inventory GROUP BY ARTICLE_ID HAVING COUNT(*) > 1";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
inventArtId = Convert.ToInt32(ckon.sqlDataRd["ARTICLE_ID"].ToString());
string cmd_update = "UPDATE inventory SET GOOD_QTY = (SELECT SUM(GOOD_QTY) FROM inventory WHERE ARTICLE_ID = '" + inventArtId + "') WHERE ARTICLE_ID = '" + inventArtId + "'";
sql.ExecuteNonQuery(cmd_update);
string cmd_delete = "WITH Temp " +
"AS(SELECT Article_ID, ROW_NUMBER() OVER(PARTITION by Article_ID ORDER BY Article_ID) AS duplicateRecCount FROM inventory where ARTICLE_ID = '" + inventArtId + "') " +
"DELETE FROM Temp WHERE duplicateRecCount > 1";
sql.ExecuteNonQuery(cmd_delete);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//========================ISI VALUE COMBO ==============================================
public void isi_combo_value(String query)
{
combo_value.Items.Clear();
try
{
ckon.sqlCon().Open();
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(query, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
String name = ckon.sqlDataRd["DESCRIPTION"].ToString();
combo_value.Items.Add(name);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//=====METHOD MENGHITUNG TOTAL BARIS RET_ORDER_LINE
public void count_ret_order()
{
CRUD sql = new CRUD();
try
{
ckon.sqlCon().Open();
String cmd = "SELECT COUNT(*) as total FROM returnorder_line WHERE RETURN_ORDER_ID = '" + return_id2 + "'";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
count_ro_line = Convert.ToInt32(ckon.sqlDataRd["total"].ToString());
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//=================================GENERATOR NUMBER=================================
public void new_invoice()
{
//===================AMBIL ID DARI TABEL CLOSING SHIFT===================
CRUD sql = new CRUD();
//ckon.con.Close();
try
{
ckon.sqlCon().Open();
String cmd_clShift = "SELECT TOP 1 ID_SHIFT FROM closing_shift ORDER BY _id DESC";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd_clShift, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
id_shift = ckon.sqlDataRd["ID_SHIFT"].ToString();
}
}
String cmd_clStore = "SELECT TOP 1 ID_C_STORE FROM closing_store ORDER BY _id DESC";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd_clStore, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
id_CStore = ckon.sqlDataRd["ID_C_STORE"].ToString();
}
}
String cmd_store = "SELECT CODE FROM store";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd_store, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
store_code = ckon.sqlDataRd["CODE"].ToString();
}
}
}
catch (Exception e)
{
throw;
}
}
private void w_edc_Load(object sender, EventArgs e)
{
CRUD sql = new CRUD();
isi_combo();
this.ActiveControl = combo_bank;
combo_bank.Focus();
type = "EDC";
//ckon.con.Close();
try
{
ckon.sqlCon().Open();
String cmd = "SELECT * FROM store";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
store_name = ckon.sqlDataRd["NAME"].ToString();
store_add = ckon.sqlDataRd["ADDRESS"].ToString();
}
}
}
catch (Exception er)
{
MessageBox.Show(er.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//ckon.cmd = new MySqlCommand(sql2, ckon.con);
//try
//{
// ckon.con.Open();
// ckon.myReader = ckon.cmd.ExecuteReader();
// if (ckon.myReader.HasRows)
// {
// while (ckon.myReader.Read())
// {
// store_name = ckon.myReader.GetString("NAME");
// store_add = ckon.myReader.GetString("ADDRESS");
// }
// }
// ckon.con.Close();
//}
//catch
//{ MessageBox.Show("Failed when get data from store data"); }
}
//==========================================================================================
//=====================ITUNG TOTAL BUDGET===========================================
public void get_budget()
{
CRUD sql = new CRUD();
//ckon.con.Close();
try
{
ckon.sqlCon().Open();
String cmd = "SELECT * FROM store ";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
bg_ToStore = Convert.ToInt32(ckon.sqlDataRd["BUDGET_TO_STORE"].ToString());
}
}
else
{
l_budget.Text = "0,00";
}
}
catch (Exception e)
{
MessageBox.Show("No connection to database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//ckon.con.Open();
//ckon.myReader = ckon.cmd.ExecuteReader();
//if (ckon.myReader.HasRows)
//{
// while (ckon.myReader.Read())
// {
// bg_ToStore = ckon.myReader.GetInt32("BUDGET_TO_STORE");
// //bg_ToCasir = ckon.myReader.GetInt32("BUDGET_TO_CASHIER");
// }
// l_budget.Text = String.Format("{0:#,###}" + ",00", bg_ToStore);
// //l_b_ToCashier.Text = String.Format("{0:#,###}" + ",00", bg_ToCasir);
//}
//else
//{
// l_budget.Text = "0,00";
//}
//ckon.con.Close();
}
//=============GET NAME STORE==========================================================
public void get_name()
{
string command;
//ckon.con.Close();
//String sql = "SELECT * FROM store";
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//try
//{
// ckon.con.Open();
// ckon.myReader = ckon.cmd.ExecuteReader();
// if (ckon.myReader.HasRows)
// {
// while (ckon.myReader.Read())
// {
// cust_id_store = ckon.myReader.GetString("CUST_ID_STORE");
// }
// }
// ckon.con.Close();
//}
//catch
//{ }
try
{
ckon.sqlCon().Open();
command = "SELECT * FROM store";
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
cust_id_store = ckon.sqlDataRd["CUST_ID_STORE"].ToString();
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//===========================METHOD ISI COMBO=============================================
public void isi_combo_spg()
{
CRUD sql = new CRUD();
combo_spg.Items.Clear();
//String sql = "SELECT employee.EMPLOYEE_ID, employee.NAME FROM employee INNER JOIN position ON employee.POSITION_ID = position._id WHERE position._id = '4' OR position._id = '3' OR position._id = '2'";
//String sql = "SELECT * FROM employee WHERE POSITION_ID = '2' OR POSITION_ID = '3' OR POSITION_ID = '4'";
try
{
ckon.sqlCon().Open();
String cmd = "SELECT * FROM employee where STORE_CODE = '" + store + "'";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
id_spg = ckon.sqlDataRd["EMPLOYEE_ID"].ToString();
nama_spg = ckon.sqlDataRd["NAME"].ToString();
combo_spg.Items.Add(id_spg + "--" + nama_spg);
}
}
}
catch (Exception e)
{
MessageBox.Show("No connection to database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//try
//{
// ckon.con.Open();
// ckon.myReader = ckon.cmd.ExecuteReader();
// while (ckon.myReader.Read())
// {
// //String name = ckon.myReader.GetString("ID_SPG");
// //combo_spg.Items.Add(name);
// id_spg = ckon.myReader.GetString("EMPLOYEE_ID");
// nama_spg = ckon.myReader.GetString("NAME");
// combo_spg.Items.Add(id_spg + "--" + nama_spg);
// }
// ckon.con.Close();
//}
//catch
//{ MessageBox.Show("Data gagal ditambilkan untuk combobox"); }
}
//=================CEK APAKAH USERNAME TERSEBUT DAN PASSWORD SAMA======
public void cek_username()
{
CRUD sql = new CRUD();
//ckon.con.Close();
try
{
ckon.sqlCon().Open();
String cmd = "SELECT * FROM employee WHERE EMPLOYEE_ID='" + t_user.Text + "' and Pass = '******'";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
cek_pass_baru();
}
else
{
MessageBox.Show("Username or Password Not Valid");
}
}
catch (Exception e)
{
MessageBox.Show("No connection to database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//ckon.con.Open();
//ckon.myReader = ckon.cmd.ExecuteReader();
//int count0 = 0;
//if (ckon.myReader.HasRows)
//{
// while (ckon.myReader.Read())
// {
// count0 = count0 + 1;
// }
// ckon.con.Close();
// if (count0 != 0)
// {
// cek_pass_baru();
// }
//}
//else
//{
// MessageBox.Show("Username or Password Not Valid");
//}
}
//==========================================================================================
//===========================ITUNG TOTAL BELANJA=====================================================
public void itung_total()
{
CRUD sql = new CRUD();
//ckon.con.Close();
try
{
ckon.sqlCon().Open();
String cmd = "SELECT SUM(pettycash_line.TOTAL) as total FROM pettycash_line WHERE PETTY_CASH_ID='" + l_transaksi.Text + "'";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
totall = Convert.ToInt32(ckon.sqlDataRd["total"].ToString());
}
l_total.Text = string.Format("{0:#,###}" + ",00", totall);
}
else
{
l_total.Text = "0";
}
}
catch (Exception e)
{
MessageBox.Show("No connection to database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//try
//{
// ckon.con.Open();
// ckon.myReader = ckon.cmd.ExecuteReader();
// while (ckon.myReader.Read())
// {
// totall = ckon.myReader.GetInt32("total");
// }
// ckon.con.Close();
// l_total.Text = string.Format("{0:#,###}" + ",00", totall);
// //l_total.Text = totall.ToString("C2", CultureInfo.GetCultureInfo("id-ID"));
//}
//catch
//{ l_total.Text = "0"; }
}
//==========================LOGIN================================================
public void loginSQL()
{
string command;
int count0 = 0;
try
{
ckon.sqlCon().Open();
command = "SELECT TOP 1 * FROM employee WHERE EMPLOYEE_ID = '" + t_username.Text + "' and CONVERT(nvarchar, Pass) = '" + t_pass.Text + "'";
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
count0 = count0 + 1;
nm_employe = ckon.sqlDataRd["NAME"].ToString();
id_employee = ckon.sqlDataRd["EMPLOYEE_ID"].ToString();
store_code = ckon.sqlDataRd["STORE_CODE"].ToString();
}
if (count0 != 0)
{
this.Hide();
Form1 fm1 = new Form1();
fm1.nama_employee = nm_employe;
fm1.id_employee = id_employee;
fm1.store_code = store_code;
fm1.setHo();
fm1.ShowDialog();
this.Close();
}
}
else
{
MessageBox.Show("Username or Password Not Valid");
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//=================isi combo box dengan data bank==========================================
public void isi_combo()
{
CRUD sql = new CRUD();
try
{
ckon.sqlCon().Open();
String cmd = "SELECT * FROM bank";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
String name = ckon.sqlDataRd["BANK_NAME"].ToString();
combo_bank1.Items.Add(name);
combo_bank2.Items.Add(name);
}
}
combo_bank1.SelectedIndex = 0;
combo_bank2.SelectedIndex = 0;
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//try
//{
// ckon.con.Open();
// ckon.myReader = ckon.cmd.ExecuteReader();
// while (ckon.myReader.Read())
// {
// String name = ckon.myReader.GetString("BANK_NAME");
// combo_bank1.Items.Add(name);
// combo_bank2.Items.Add(name);
// }
// ckon.con.Close();
//}
//catch
//{ MessageBox.Show("Data gagal ditambilkan untuk combobox"); }
//combo_bank1.SelectedIndex = 0;
//combo_bank2.SelectedIndex = 0;
}
//CEK DI TRANS LINE APAKAH DISCOUNT CODE INI SUDAH ADA APA BLOM, JIKA ADA=TOLAK, JIKA TIDAK ADA=LANJUTKAN
public void cek_discount_line()
{
CRUD sql = new CRUD();
try
{
ckon.sqlCon().Open();
//String cmd = "SELECT * FROM transaction_line WHERE TRANSACTION_ID='" + id_transaksi + "' AND DISCOUNT_CODE = '" + id_diskon + "'";
String cmd = "SELECT * FROM [tmp]." + kodetoko2 + " WHERE TRANSACTION_ID='" + id_transaksi + "' AND DISCOUNT_CODE = '" + id_diskon + "'";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
count_disc_Tline = count_disc_Tline + 1;
}
}
else
{
count_disc_Tline = 0;
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//ckon.con.Open();
//ckon.myReader = ckon.cmd.ExecuteReader();
//if (ckon.myReader.HasRows)
//{
// while (ckon.myReader.Read())
// {
// count_disc_Tline = count_disc_Tline + 1;
// }
// ckon.con.Close();
//}
//else
//{
// count_disc_Tline = 0;
//}
//ckon.con.Close();
}
//============================================================================================
//wtu
private string GetStore(string code)
{
CRUD sql = new CRUD();
String store = "-";
if (code == "" || code == "-")
{
return(store);
}
try
{
ckon.sqlCon().Open();
String cmd = "SELECT NAME FROM store_relasi WHERE CODE='" + code + "' UNION SELECT NAME FROM store WHERE CODE='" + code + "'";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
store = ckon.sqlDataRd["NAME"].ToString();
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//try
//{
// ckon.con.Open();
// ckon.myReader = ckon.cmd.ExecuteReader();
// while (ckon.myReader.Read())
// {
// store = ckon.myReader.GetString("NAME");
// }
// ckon.con.Close();
//}
//catch
//{ }
return(store);
}
//=====================select do line======================
public void get_do_line(String id_do)
{
string command;
koneksi ckon = new koneksi();
do_id = id_do;
//String sql = "SELECT * FROM deliveryorder_line WHERE DELIVERY_ORDER_ID = '" + do_id + "'";
//ckon3.cmd3 = new MySqlCommand(sql, ckon3.con3);
//ckon3.con3.Open();
//ckon3.myReader3 = ckon3.cmd3.ExecuteReader();
//while (ckon3.myReader3.Read())
//{
// art_id_do = ckon3.myReader3.GetString("ARTICLE_ID");
// qty_receive = ckon3.myReader3.GetInt32("QTY_RECEIVE");
// cek_qty_inv(art_id_do);
// cek_inv_line(do_id, qty_receive);
//}
//ckon3.con3.Close();
try
{
ckon.sqlCon().Open();
command = "SELECT a.QTY_RECEIVE, b._id FROM deliveryorder_line a JOIN article b "
+ "ON a.ARTICLE_ID = b.ARTICLE_ID WHERE DELIVERY_ORDER_ID = '" + do_id + "' AND QTY_RECEIVE > 0";
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
art_id_do = ckon.sqlDataRd["_id"].ToString();
qty_receive = Convert.ToInt32(ckon.sqlDataRd["QTY_RECEIVE"].ToString());
cek_qty_inv(art_id_do);
cek_inv_line(do_id, qty_receive);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//melihat max qty dari article yang dipilih
public void cek_qty()
{
CRUD sql = new CRUD();
try
{
ckon.sqlCon().Open();
String cmd = "SELECT inventory.GOOD_QTY FROM inventory INNER JOIN article "
+ "ON article._id = inventory.ARTICLE_ID "
+ "WHERE article.ARTICLE_ID = '" + id2 + "'";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
inv_good_qty = Convert.ToInt32(ckon.sqlDataRd["GOOD_QTY"].ToString());
}
}
}
catch (Exception e)
{
MessageBox.Show("No connection to database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//ckon.con.Open();
//ckon.myReader = ckon.cmd.ExecuteReader();
//while(ckon.myReader.Read())
//{
// String _id = ckon.myReader.GetString("_id");
// //ambil total qty dari tabel inventory
// String sql2 = "SELECT * FROM inventory WHERE ARTICLE_ID = '" + _id + "'";
// ckon2.cmd2 = new MySqlCommand(sql2, ckon2.con2);
// ckon2.con2.Open();
// ckon2.myReader2 = ckon2.cmd2.ExecuteReader();
// while(ckon2.myReader2.Read())
// {
// inv_good_qty = ckon2.myReader2.GetInt32("GOOD_QTY");
// }
// ckon2.con2.Close();
//}
//ckon.con.Close();
}
public void downloadFile()
{
CRUD sql = new CRUD();
String downloadPath = "";
String fileName = "";
String id = "";
String jobId = "";
String storeId = "";
String rowFatch = "";
int syncType = 0;
try
{
ckon.sqlConMsg().Open();
String cmd = "SELECT JobID, StoreID, DownloadPath, SynchDetail, RowFatch, Synctype FROM JobTabletoSynchDetailDownload " +
"WHERE SynchDetail NOT IN(SELECT SynchDetail FROM JobSynchDetailDownloadStatus) OR " +
"SynchDetail IN(SELECT SynchDetail FROM JobSynchDetailDownloadStatus a WHERE a.RowApplied = 0)";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlConMsg());
if (ckon.sqlDataRd.HasRows)
{
Guid downloadSession = Guid.NewGuid();
while (ckon.sqlDataRd.Read())
{
downloadPath = Convert.ToString(ckon.sqlDataRd["DownloadPath"]);
id = Convert.ToString(ckon.sqlDataRd["SynchDetail"]);
jobId = Convert.ToString(ckon.sqlDataRd["JobID"]);
storeId = Convert.ToString(ckon.sqlDataRd["StoreID"]);
rowFatch = Convert.ToString(ckon.sqlDataRd["RowFatch"]);
syncType = Convert.ToInt32(ckon.sqlDataRd["Synctype"]);
fileName = getFilename(downloadPath, syncType);
downloadFromFTP(downloadPath, fileName, id, jobId.Trim(), storeId.Trim(), rowFatch, downloadSession, syncType);
createFilePath(downloadPath);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//==============================ISI COMBO================================
public void combo_transtype()
{
combo_mutasiTo.Items.Clear();
trans_type.Items.Clear();
try
{
ckon.sqlCon().Open();
String command = "SELECT * FROM ho_transaction_type";
CRUD sql = new CRUD();
ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
String code = ckon.sqlDataRd["CODE"].ToString();
String name = ckon.sqlDataRd["DESCRIPTION"].ToString();
trans_type.Items.Add(code + " -- " + name);
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//String sql = query;
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//try
//{
// ckon.con.Open();
// ckon.myReader = ckon.cmd.ExecuteReader();
// while (ckon.myReader.Read())
// {
// String code = ckon.myReader.GetString("CODE");
// String name = ckon.myReader.GetString("DESCRIPTION");
// trans_type.Items.Add(code + " -- " + name);
// }
// ckon.con.Close();
//}
//catch
//{ }
}
//==================================GET BUDGET STORE==================================================
public void get_budget()
{
CRUD sql = new CRUD();
//ckon.con.Close();
try
{
ckon.sqlCon().Open();
String cmd = "SELECT * FROM store ";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
bg_ToCasir = Convert.ToInt32(ckon.sqlDataRd["BUDGET_TO_STORE"].ToString());
store_code = Convert.ToString(ckon.sqlDataRd["CODE"]);
value_budget = bg_ToCasir;
if (bg_ToCasir == 0)
{
l_petty.Text = "0,00";
t_petty.Text = "0";
}
else
{
l_petty.Text = String.Format("{0:#,###}" + ",00", bg_ToCasir);
t_petty.Text = String.Format("{0:#,###}", bg_ToCasir);
}
}
}
else
{
bg_ToCasir = 0;
l_petty.Text = "0,00";
t_petty.Text = "0,00";
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
public void deleteFile()
{
String downloadPath = "";
String jobId = "";
String storeId = "";
String path = "";
String fileName = "";
int syncType = 0;
CRUD sql = new CRUD();
SqlConnection con = ckon.sqlConMsg();
try
{
String cmd = "SELECT * FROM JobTabletoSynchDetailDownload a " +
"INNER JOIN JobSynchDetailDownloadStatus b ON b.SynchDetail = a.SynchDetail " +
"WHERE b.RowApplied != 0 AND b.status = 1 ORDER BY b.SynchDetail ASC";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, con);
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
downloadPath = Convert.ToString(ckon.sqlDataRd["DownloadPath"]);
jobId = Convert.ToString(ckon.sqlDataRd["JobID"]);
storeId = Convert.ToString(ckon.sqlDataRd["StoreID"]);
syncType = Convert.ToInt32(ckon.sqlDataRd["Synctype"]);
fileName = getFilename(downloadPath, syncType);
path = getExtactFilePath(downloadPath);
if (File.Exists(path + @"\" + fileName + ".bcp"))
{
File.Delete(path + @"\" + fileName + ".bcp");
}
}
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "No Connection", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
public void UploaddFile()
{
CRUD sql = new CRUD();
String uploadPath = "";
String fileName = "";
String id = "";
String jobId = "";
String storeId = "";
String rowFatch = "";
try
{
ckon.sqlConMsg().Open();
String cmd = "SELECT JobID, StoreID, UploadPath, SynchDetail, RowFatch FROM JobTabletoSynchDetailUpload " +
"WHERE SynchDetail NOT IN(SELECT SynchDetail FROM JobSynchDetailUploadStatus)";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlConMsg());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
uploadPath = Convert.ToString(ckon.sqlDataRd["UploadPath"]);
id = Convert.ToString(ckon.sqlDataRd["SynchDetail"]);
jobId = Convert.ToString(ckon.sqlDataRd["JobID"]);
storeId = Convert.ToString(ckon.sqlDataRd["StoreID"]);
rowFatch = Convert.ToString(ckon.sqlDataRd["RowFatch"]);
fileName = getFilename(uploadPath);
uploadToFTP(fileName, storeId, id, rowFatch);
}
}
}
catch (Exception e)
{
using (EventLog eventLog = new EventLog("Application"))
{
eventLog.Source = "Application";
eventLog.WriteEntry("Upload background " + e.Message.ToString(), EventLogEntryType.Information, 101, 1);
}
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//============================================================================
//========================METHOD SAVE INTO TABLE CLOSING_SHIFT WHEN OPENED SHIFT====================
public void save_closing_shift()
{
CRUD sql = new CRUD();
//ckon.con.Close();
try
{
ckon.sqlCon().Open();
String cmd = "SELECT TOP 1* FROM closing_shift ORDER BY _id DESC";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
open_trans = Convert.ToInt32(ckon.sqlDataRd["REAL_TRANS_BALANCE"].ToString());
open_petty = Convert.ToInt32(ckon.sqlDataRd["REAL_PETTY_CASH"].ToString());
open_deposit = Convert.ToInt32(ckon.sqlDataRd["REAL_DEPOSIT"].ToString());
}
}
else
{
open_trans = 0;
open_petty = 0;
open_deposit = 0;
}
string name = System.Environment.MachineName;
DateTime mydate = DateTime.Now;
String time_now = mydate.ToString("yyyy-MM-dd H:mm:ss");
String cmd_insert = "INSERT INTO closing_shift (ID_SHIFT,STORE_ID,SHIFT,OPENING_TIME,OPENING_TRANS_BALANCE, OPENING_PETTY_CASH,OPENING_DEPOSIT,DEVICE_NAME,EMPLOYEE_ID,EMPLOYEE_NAME) VALUES ('" + string_id_shift + "','" + store_id + "', '" + shift_name + "', '" + time_now + "','" + open_trans + "','" + open_petty + "', '" + open_deposit + "','" + name + "','" + id_epy + "','" + nm_epy + "')";
sql.ExecuteNonQuery(cmd_insert);
String cmd_update = "UPDATE auto_number SET Month = '" + bulan_shift + "', Number = '" + no_trans_shift + "' WHERE Type_Trans='" + tipe_closing_shift + "'";
sql.ExecuteNonQuery(cmd_update);
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
}
//===================================================================================
//===========================GET DATA BY ID ================================================
public void get_data_id()
{
CRUD sql = new CRUD();
//ckon.con.Close();
try
{
ckon.sqlCon().Open();
String cmd = "SELECT * FROM pettycash WHERE PETTY_CASH_ID='" + id_list + "'";
ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon());
if (ckon.sqlDataRd.HasRows)
{
while (ckon.sqlDataRd.Read())
{
d_tgl_expense.Text = ckon.sqlDataRd["EXPENSE_DATE"].ToString();
}
}
}
catch (Exception e)
{
MessageBox.Show("No connection to database", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (ckon.sqlDataRd != null)
{
ckon.sqlDataRd.Close();
}
if (ckon.sqlCon().State == ConnectionState.Open)
{
ckon.sqlCon().Close();
}
}
//ckon.cmd = new MySqlCommand(sql, ckon.con);
//ckon.con.Open();
//ckon.myReader = ckon.cmd.ExecuteReader();
//while (ckon.myReader.Read())
//{
// combo_expense.Text = ckon.myReader.GetString("EXPENSE_CATEGORY");
// d_tgl_expense.Text = ckon.myReader.GetString("EXPENSE_DATE");
// amount = ckon.myReader.GetInt32("TOTAL_EXPENSE");
// t_desc.Text = ckon.myReader.GetString("DESCRIPTION");
//}
//l_total.Text = string.Format("{0:#,###}" + ",00", amount);
//ckon.con.Close();
//l_total.Text = amount.ToString("C2", CultureInfo.GetCultureInfo("id-ID"));
}
