//============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")); }