//================================================================================== //mengambil id bank berdasarkan nama bank yang dipilih=== public void get_id_bank(string _id_bank1, string _id_bank2) { CRUD sql = new CRUD(); //ckon.con.Close(); //String sql = "SELECT BANK_NAME FROM bank WHERE BANK_ID LIKE '%" + id_bank1 + "%'"; //ckon.cmd = new MySqlCommand(sql, ckon.con); //ckon.con.Open(); //ckon.myReader = ckon.cmd.ExecuteReader(); //while (ckon.myReader.Read()) //{ // bank_name1 = ckon.myReader.GetString("BANK_NAME"); //} //ckon.con.Close(); //String sql2 = "SELECT BANK_NAME FROM bank WHERE BANK_ID LIKE '%" + id_bank2 + "%'"; //ckon.cmd = new MySqlCommand(sql2, ckon.con); //ckon.con.Open(); //ckon.myReader = ckon.cmd.ExecuteReader(); //while (ckon.myReader.Read()) //{ // bank_name2 = ckon.myReader.GetString("BANK_NAME"); //} //ckon.con.Close(); if (_id_bank1 != string.Empty) { try { ckon.sqlCon().Open(); String cmd_bank1 = "SELECT BANK_NAME FROM bank WHERE BANK_ID LIKE '%" + _id_bank1 + "%'"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd_bank1, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { bank_name1 = ckon.sqlDataRd["BANK_NAME"].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(); } } } if (_id_bank2 != string.Empty) { try { ckon.sqlCon().Open(); String cmd_bank2 = "SELECT BANK_NAME FROM bank WHERE BANK_ID LIKE '%" + _id_bank2 + "%'"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd_bank2, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { bank_name2 = ckon.sqlDataRd["BANK_NAME"].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(); } } } }
//=================GET NUMBER RUNNING========================= /* * public void get_running_number(String number, String bulan, int no_trans, String tipe) * { * l_transaksi.Text = number; * bulan2 = bulan; * no_trans2 = no_trans; * tipe2 = tipe; * } */ //=================================GENERATOR NUMBER================================= public void new_invoice() { CRUD sql = new CRUD(); dgv_petty.Rows.Clear(); combo_expense.SelectedIndex = -1; l_total.Text = "0"; //===================================================== //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_code = ckon.sqlDataRd["CODE"].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(sql2, ckon.con); //try //{ // ckon.con.Open(); // ckon.myReader = ckon.cmd.ExecuteReader(); // if (ckon.myReader.HasRows) // { // while (ckon.myReader.Read()) // { // store_code = ckon.myReader.GetString("CODE"); // } // } // ckon.con.Close(); //} //catch //{ MessageBox.Show("Failed when get data from store data"); } //========================================================================================== //string sql = "SELECT SUBSTRING(PETTY_CASH_ID, 8) AS inv FROM pettycash ORDER BY PETTY_CASH_ID DESC LIMIT 1"; //ckon.cmd = new MySqlCommand(sql, ckon.con); //try //{ // ckon.con.Open(); // ckon.myReader = ckon.cmd.ExecuteReader(); // if (ckon.myReader.HasRows) // { // while (ckon.myReader.Read()) // { // noo_inv_new = ckon.myReader.GetInt32("inv"); // noo_inv_new = noo_inv_new + 1; // //l_transaksi.Text = "PC-" + noo_inv_new.ToString(); // } // if(noo_inv_new < 10) // { l_transaksi.Text = store_code + "/PC-000" + noo_inv_new.ToString(); } // else if(noo_inv_new < 100) // { l_transaksi.Text = store_code + "/PC-00" + noo_inv_new.ToString(); } // else if(noo_inv_new < 1000 ) // { l_transaksi.Text = store_code + "/PC-0" + noo_inv_new.ToString();} // else if(noo_inv_new < 10000) // { l_transaksi.Text = store_code + "/PC-" + noo_inv_new.ToString(); } // else // { } // } // else // { l_transaksi.Text = store_code +"/PC-0001"; } // ckon.con.Close(); //} //catch //{ } }
//=========METHOD GET DATA FROM AUTO_NUMBER TABLE FOR SALES TRANSACTION public void get_running_number() { CRUD sql = new CRUD(); try { ckon.sqlCon().Open(); String cmd = "SELECT * FROM auto_number WHERE Store_Code = '" + store_code + "' AND Type_Trans = '6'"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { bulan_trans = ckon.sqlDataRd["Month"].ToString(); number_trans = Convert.ToInt32(ckon.sqlDataRd["Number"].ToString()); } if (bulan_now == bulan_trans) { number_trans = number_trans + 1; if (number_trans < 10) { number_trans_string = "0000" + number_trans.ToString(); } else if (number_trans < 100) { number_trans_string = "000" + number_trans.ToString(); } else if (number_trans < 1000) { number_trans_string = "00" + number_trans.ToString(); } else if (number_trans < 10000) { number_trans_string = "0" + number_trans.ToString(); } else { number_trans_string = number_trans.ToString(); } //==MEMBUAT STRING FINAL RUNNING NUMBER final_running_number = "PC/" + store_code + "-" + tahun_now + "" + bulan_trans + "-" + number_trans_string; l_transaksi.Text = final_running_number; } else { number_trans = 1; bulan_trans = bulan_now; //MENJADIKAN BULAN TRANSAKSI = BULAN SEKARANG //==MEMBUAT STRING FINAL RUNNING NUMBER final_running_number = "PC/" + store_code + "-" + tahun_now + "" + bulan_trans + "-00001"; l_transaksi.Text = final_running_number; } } else { number_trans = 1; bulan_trans = bulan_now;//BULAN TRANSAKSI = BULAN SEKARANG final_running_number = "PC/" + store_code + "-" + tahun_now + "" + bulan_trans + "-00001"; l_transaksi.Text = final_running_number; String cmd_insert = "INSERT INTO auto_number (Store_Code,Month,Number,Type_Trans) VALUES ('" + store_code + "','" + bulan_trans + "','0','6')"; sql.ExecuteNonQuery(cmd_insert); //MessageBox.Show(final_running_number); } } 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.con.Open(); //ckon.cmd = new MySqlCommand(sql, ckon.con); //ckon.myReader = ckon.cmd.ExecuteReader(); //if (ckon.myReader.HasRows) //{ // while (ckon.myReader.Read()) // { // //tahun_trans = ckon.myReader.GetString("Year"); // bulan_trans = ckon.myReader.GetString("Month"); // number_trans = ckon.myReader.GetInt32("Number"); // } // if (bulan_now == bulan_trans) // { // number_trans = number_trans + 1; // if (number_trans < 10) // { number_trans_string = "0000" + number_trans.ToString(); } // else if (number_trans < 100) // { number_trans_string = "000" + number_trans.ToString(); } // else if (number_trans < 1000) // { number_trans_string = "00" + number_trans.ToString(); } // else if (number_trans < 10000) // { number_trans_string = "0" + number_trans.ToString(); } // else // { number_trans_string = number_trans.ToString(); } // //==MEMBUAT STRING FINAL RUNNING NUMBER // final_running_number = "PC/" + store_code + "-" + tahun_now + "" + bulan_trans + "-" + number_trans_string; // l_transaksi.Text = final_running_number; // } // else // { // number_trans = 1; // bulan_trans = bulan_now;//MENJADIKAN BULAN TRANSAKSI = BULAN SEKARANG // //==MEMBUAT STRING FINAL RUNNING NUMBER // final_running_number = "PC/" + store_code + "-" + tahun_now + "" + bulan_trans + "-00001"; // l_transaksi.Text = final_running_number; // } //} //else //{ // number_trans = 1; // bulan_trans = bulan_now;//BULAN TRANSAKSI = BULAN SEKARANG // final_running_number = "PC/" + store_code + "-" + tahun_now + "" + bulan_trans + "-00001"; // l_transaksi.Text = final_running_number; // String query = "INSERT INTO auto_number (Store_Code,Month,Number,Type_Trans) VALUES ('" + store_code + "','" + bulan_trans + "','0','6')"; // CRUD ubah = new CRUD(); // ubah.ExecuteNonQuery(query); // //MessageBox.Show(final_running_number); //} //ckon.con.Close(); }
public void promotionByBrand() { CRUD sql = new CRUD(); //ckon.con.Close(); dgv_purchase.Rows.Clear(); try { ckon.sqlCon().Open(); String cmd = "SELECT b.Code, c.Description, a.DiscountCode, a.DiscountName, a.DiscountType, a.DiscountPercent as HeaderDiscountPercent, a. DiscountCash as HeaderDiscountCash, " + "a.QtyMin as HeaderQtyMin, a.QtyMax as HeaderQtyMax, a.AmountMin as HeaderAmountMin, a.AmountMax as HeaderAmountMax, b.DiscountPrecentage, b.DiscountCash, " + "b.QtyMin, b.QtyMax, b.AmountMin, b.AmountMax FROM DiscountSetup a " + "INNER JOIN DiscountSetupLines b ON b.DiscountSetupId = a.Id " + "INNER JOIN itemdimensionbrand c ON c.Code = b.Code " + "WHERE a.DiscountCode = '" + id_diskon + "'"; ckon.dt = sql.ExecuteDataTable(cmd, ckon.sqlCon()); foreach (DataRow row in ckon.dt.Rows) { int dgRows = dgv_purchase.Rows.Add(); dgv_purchase.Rows[dgRows].Cells[0].Value = row["Code"].ToString(); dgv_purchase.Rows[dgRows].Cells[1].Value = row["Description"].ToString(); dgv_purchase.Rows[dgRows].Cells[2].Value = row["DiscountPercent"].ToString(); dgv_purchase.Rows[dgRows].Cells[3].Value = row["DiscountCash"].ToString(); dgv_purchase.Rows[dgRows].Cells[4].Value = row["QtyMin"].ToString(); dgv_purchase.Rows[dgRows].Cells[5].Value = row["QtyMax"].ToString(); dgv_purchase.Rows[dgRows].Cells[6].Value = row["AmountMin"].ToString(); dgv_purchase.Rows[dgRows].Cells[7].Value = row["AmountMax"].ToString(); dgv_purchase.Rows[dgRows].Cells[8].Value = row["HeaderDiscountPercent"].ToString(); dgv_purchase.Rows[dgRows].Cells[9].Value = row["HeaderDiscountCash"].ToString(); dgv_purchase.Rows[dgRows].Cells[10].Value = row["HeaderQtyMin"].ToString(); dgv_purchase.Rows[dgRows].Cells[11].Value = row["HeaderQtyMax"].ToString(); dgv_purchase.Rows[dgRows].Cells[12].Value = row["HeaderAmountMin"].ToString(); dgv_purchase.Rows[dgRows].Cells[13].Value = row["HeaderAmountMax"].ToString(); } //==========================FUNCTION FOR HIDE FIELD WHEN FIELD EMPTY=============================================== foreach (DataGridViewColumn clm in dgv_purchase.Columns) { dgv_purchase.Columns[clm.Index].Visible = false; //bool notAvailable = true; bool notAvailable = false; foreach (DataGridViewRow row in dgv_purchase.Rows) { if (row.Cells[clm.Index].Value != null) { // If string of value is empty if (row.Cells[clm.Index].Value.ToString() != field_none) { if (row.Cells[clm.Index].Value.ToString() != field_none2) { if (row.Cells[clm.Index].Value.ToString() != "0,0000" || row.Cells[clm.Index].Value.ToString() != "0") { if (row.Cells[clm.Index].Value.ToString() != "") { notAvailable = true; break; } } } } } } if (notAvailable) { //dgv_purchase.Columns[clm.Index].Visible = false; dgv_purchase.Columns[clm.Index].Visible = true; } } } catch (Exception e) { MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { ckon.dt.Rows.Clear(); if (ckon.sqlCon().State == ConnectionState.Open) { ckon.sqlCon().Close(); } } }
//================================================================================== //=======================TOTAL EXPENSE============================================== public void get_expense() { CRUD sql = new CRUD(); //ckon.con.Close(); try { ckon.sqlCon().Open(); String cmd = "SELECT SUM(pettycash.TOTAL_EXPENSE) as total FROM pettycash WHERE STATUS='1'"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { if (ckon.sqlDataRd["total"].ToString() != "") { tot_exp = Convert.ToInt32(ckon.sqlDataRd["total"].ToString()); } if (tot_exp <= 0) { l_expense.Text = "0,00"; } else { l_expense.Text = string.Format("{0:#,###}" + ",00", tot_exp); } } } else { l_expense.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(); //while (ckon.myReader.Read()) //{ // try // { // tot_exp = ckon.myReader.GetInt32("total"); // if (tot_exp <= 0) // { l_expense.Text = "0,00"; } // else // { // //bg_ToStore = bg_ToStore - tot_exp; // //l_budget.Text = string.Format("{0:#,###}" + ",00", bg_ToStore); // l_expense.Text = string.Format("{0:#,###}" + ",00", tot_exp); // } // //l_total_amount.Text = amount.ToString("C2", CultureInfo.GetCultureInfo("id-ID")); // } // catch // { // l_expense.Text = "0,00"; // } //} //ckon.con.Close(); }
//=============================BUTTON USE======================================== private void b_ok_Click(object sender, EventArgs e) { CRUD sql = new CRUD(); //if(status=="0") //{ // MessageBox.Show("Discount Not Match"); //} //else //{ //===CEK APAKAH DISKON SUDAH PERNAH DIGUNAKAN DI TRANSAKSI INI cek_discount_line(); //jika sudah ada maka tampilkan pesan agar tidak bisa digunakan lagi if (count_disc_Tline >= 1) { MessageBox.Show("Discounts Have Been Used"); } else { //jika tipe diskon adalah 3, atau buy and get //if (disc_type == "3") //{ // //========HITUNG ADA BRAPA BANYAK DISCOUNT ITEM DARI DISCOUNT CODE // count_artcile(); // //JIKA ADA 1, LANGSUNG APPLY, JIKA ADA BANYAK, OPEN DIALOG // if (count == 1) // { // W_disc_GetArticle disc = new W_disc_GetArticle(); // //disc.message(); // disc.get_disc_code(diskon_kode, disc_type, id_transaksi, spg_id); // disc.disc_1item(); // disc.search_data_article(); // disc.insert(); // DiscountAfterUsePromNew afteruser = new DiscountAfterUsePromNew(); // afteruser.retreive(id_transaksi, kodetoko2, custid2); // uc_coba.Instance.retreive(); // uc_coba.Instance.itung_total(); // this.Close(); // } // //jumlah diskon_item lebih dari 1, open dialog // else // { // W_disc_GetArticle disc = new W_disc_GetArticle(); // disc.get_disc_code(diskon_kode, disc_type, id_transaksi, spg_id); // disc.getget(id_transaksi, kodetoko2, custid2); // disc.retreive(); // disc.ShowDialog(); // this.Close(); // } //} ////============akhir tipe diskon 3================ ////jenis diskon 2 //else if (disc_type == "2") //{ // try // { // ckon.sqlCon().Open(); // String cmd_discType = "Select * from disctype2 where DiscountRetailId='" + id_diskon + "' and TransId = '" + id_transaksi + "'"; // ckon.sqlDataRd = sql.ExecuteDataReader(cmd_discType, ckon.sqlCon()); // if (ckon.sqlDataRd.HasRows) // { // while (ckon.sqlDataRd.Read()) // { // String discount = ckon.sqlDataRd["Discount"].ToString(); // String totharga = ckon.sqlDataRd["TotHarga"].ToString(); // String artid = ckon.sqlDataRd["articleid"].ToString(); // String persen = ckon.sqlDataRd["DiscPersent"].ToString(); // String update = "UPDATE transaction_line set DISCOUNT = '" + discount + "', SUBTOTAL = '" + totharga + "', DISCOUNT_CODE = '" + id_diskon + "', DISCOUNT_TYPE='" + disc_type + "', DISCOUNT_DESC = '" + persen + "' WHERE TRANSACTION_ID = '" + id_transaksi + "' and ARTICLE_ID = '" + artid + "'"; // sql.ExecuteNonQuery(update); // DiscountAfterUsePromNew afteruser = new DiscountAfterUsePromNew(); // afteruser.retreive(id_transaksi, kodetoko2, custid2); // uc_coba.Instance.retreive(); // uc_coba.Instance.itung_total(); // this.Close(); // } // } // } // catch (Exception er) // { // 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 discount = ckon.myReader.GetString("Discount"); // String totharga = ckon.myReader.GetString("TotHarga"); // String artid = ckon.myReader.GetString("articleid"); // String persen = ckon.myReader.GetString("DiscPersent"); // String update = "UPDATE transaction_line set DISCOUNT = '" + discount + "', SUBTOTAL = '" + totharga + "', DISCOUNT_CODE = '"+ id_diskon +"', DISCOUNT_TYPE='"+ disc_type +"', DISCOUNT_DESC = '"+ persen +"' WHERE TRANSACTION_ID = '" + id_transaksi + "' and ARTICLE_ID = '"+ artid +"'"; // CRUD changee = new CRUD(); // changee.ExecuteNonQuery(update); // DiscountAfterUseProm afteruser = new DiscountAfterUseProm(); // afteruser.retreive(id_transaksi, kodetoko2, custid2); // uc_coba.Instance.retreive(); // uc_coba.Instance.itung_total(); // this.Close(); // //diskon_kode = ckon.myReader.GetString("DISCOUNT_CODE"); // //diskon_name = ckon.myReader.GetString("DISCOUNT_NAME"); // //diskon_ktg = ckon.myReader.GetString("DISCOUNT_CATEGORY"); // //diskon_desc = ckon.myReader.GetString("DESCRIPTION"); // //status = ckon.myReader.GetString("STATUS"); // //art_id_diskon = ckon.myReader.GetString("ARTICLE_ID"); // //disc_type = ckon.myReader.GetString("DISCOUNT_TYPE"); // //disc_desc = ckon.myReader.GetString("DISCOUNT_DESC"); // ////l_diskon_name.Text = diskon_name; // //t_disc_name.Text = diskon_name; // //l_d_ctg.Text = diskon_ktg; // //l_d_code.Text = diskon_kode; // //l_d_desc.Text = diskon_desc; // } // ckon.con.Close(); // } // catch (Exception ex) // { MessageBox.Show(ex.ToString()); } //} //} //jenis diskon 4, atau selain tipe 3 //else //{ // //=====JENIS DISKON========== // get_type_diskon(); // //=====HARGA SETELAH DISKON=== // get_total_price(); // //MessageBox.Show(total_kotor.ToString()); // //=====MASUKAN HARGA SETELAH DISKON KE TRANS_LINE YG SESUAI DENGAN // update(); //} //====akhir diskon tipe selain 3 discPromoCalcSP(id_transaksi, id_diskon); uc_coba.Instance.retreive(); uc_coba.Instance.itung_total(); this.Close(); } //====akhir jika diskon belom pernah digunakan //} }
//=============================================================================================== public void get_data() { CRUD sql = new CRUD(); //ckon.con.Close(); try { ckon.sqlCon().Open(); String cmd = "SELECT * FROM promotion_line WHERE DISCOUNT_CODE='" + id_diskon + "'"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { art_bonus = ckon.sqlDataRd["ARTICLE_ID_DISCOUNT"].ToString(); net_diskon = Convert.ToInt32(ckon.sqlDataRd["DISCOUNT_PERCENT"].ToString()); net_price = Convert.ToInt32(ckon.sqlDataRd["DISCOUNT_PRICE"].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()) // { // //try // //{ net_diskon = ckon.myReader.GetInt32("DISCOUNT_PERCENT"); } // //catch // //{ net_diskon = 0; } // ////=============================== // //try // //{ net_price = ckon.myReader.GetInt32("DISCOUNT_PRICE"); } // //catch // //{ net_price = 0; } // art_bonus = ckon.myReader.GetString("ARTICLE_ID_DISCOUNT"); // net_diskon = ckon.myReader.GetInt32("DISCOUNT_PERCENT"); // net_price = ckon.myReader.GetInt32("DISCOUNT_PRICE"); // } // ckon.con.Close(); //} //catch //{ } }
//===================================================================================== //===============TAMPILKAN DATA PENJUALAN=================================================== public void retreive() { String art_id, art_name, spg_id, discAmount, qty, disc, sub_total2; int price, sub_total; string command; CRUD sql = new CRUD(); dgv_purchase.Rows.Clear(); try { ckon.sqlCon().Open(); command = "SELECT transaction_line.ARTICLE_ID ,transaction_line.QUANTITY, transaction_line.SUBTOTAL, transaction_line.SPG_ID,transaction_line.DISCOUNT, transaction_line.DISCOUNT,article.ARTICLE_NAME, " + "transaction_line.DISCOUNTAMOUNT, article.PRICE FROM transaction_line, article WHERE article.ARTICLE_ID = transaction_line. ARTICLE_ID " + "AND transaction_line.TRANSACTION_ID='" + l_transaksi.Text + "' ORDER BY transaction_line._id ASC"; ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { art_id = ckon.sqlDataRd["ARTICLE_ID"].ToString(); art_name = ckon.sqlDataRd["ARTICLE_NAME"].ToString(); spg_id = ckon.sqlDataRd["SPG_ID"].ToString(); discAmount = ckon.sqlDataRd["DISCOUNTAMOUNT"].ToString(); price = Convert.ToInt32(ckon.sqlDataRd["PRICE"].ToString()); qty = ckon.sqlDataRd["QUANTITY"].ToString(); disc = ckon.sqlDataRd["DISCOUNT"].ToString(); sub_total = Convert.ToInt32(ckon.sqlDataRd["SUBTOTAL"].ToString()); int dgRows = dgv_purchase.Rows.Add(); dgv_purchase.Rows[dgRows].Cells[0].Value = art_id; dgv_purchase.Rows[dgRows].Cells[1].Value = art_name; dgv_purchase.Rows[dgRows].Cells[2].Value = spg_id; dgv_purchase.Rows[dgRows].Cells[3].Value = qty; dgv_purchase.Rows[dgRows].Cells[4].Value = price; dgv_purchase.Rows[dgRows].Cells[5].Value = disc; dgv_purchase.Rows[dgRows].Cells[6].Value = discAmount; dgv_purchase.Rows[dgRows].Cells[7].Value = sub_total == 0 ? "0,00" : sub_total.ToString(); } } dgv_purchase.Columns[3].DefaultCellStyle.Format = "#,###"; dgv_purchase.Columns[4].DefaultCellStyle.Format = "#,###"; dgv_purchase.Columns[6].DefaultCellStyle.Format = "#,###"; dgv_purchase.Columns[7].DefaultCellStyle.Format = "#,###"; } 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(); } } try { ckon.sqlCon().Open(); command = "SELECT [transaction].*,bank.BANK_NAME AS payment, c.BANK_NAME AS payment2 FROM [TRANSACTION] LEFT JOIN bank ON [transaction].BANK_NAME=bank.BANK_ID " + "LEFT JOIN bank c ON [transaction].BANK_NAME2=c.BANK_ID WHERE TRANSACTION_ID ='" + l_transaksi.Text + "'"; ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { if (Convert.ToInt32(ckon.sqlDataRd["EDC"].ToString()) > 0) { payment1_txt.Text = "Payment Method : EDC"; payment1_value.Text = ckon.sqlDataRd["payment"].ToString() + " : " + string.Format("{0:#,###}" + ",00", Convert.ToInt32(ckon.sqlDataRd["EDC"].ToString())) + " - No Ref : " + ckon.sqlDataRd["NO_REF"].ToString(); if (Convert.ToInt32(ckon.sqlDataRd["EDC2"].ToString()) > 0) { payment2_value.Text = ckon.sqlDataRd["payment2"].ToString() + " : " + string.Format("{0:#,###}" + ",00", Convert.ToInt32(ckon.sqlDataRd["EDC2"].ToString())) + " - No Ref : " + ckon.sqlDataRd["NO_REF2"].ToString(); } } else { payment1_txt.Text = "Payment Method : Cash"; payment1_value.Text = ""; payment2_value.Text = ""; } } } } 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(); } } }
//================================================================================== //===========================ITUNG TOTAL BELANJA===================================================== public void itung_total() { string command; CRUD sql = new CRUD(); try { ckon.sqlCon().Open(); command = "SELECT * FROM [TRANSACTION] WHERE TRANSACTION_ID ='" + l_transaksi.Text + "'"; ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon()); if (ckon.sqlDataRd.Read()) { while (ckon.sqlDataRd.Read()) { get_diskon = Convert.ToInt32(ckon.sqlDataRd["DISCOUNT"].ToString()); get_voucher = Convert.ToInt32(ckon.sqlDataRd["VOUCHER"].ToString()); } get_dis_vou = get_diskon + get_voucher; if (get_diskon == 0) { l_diskon.Text = "0,00"; } else { l_diskon.Text = string.Format("{0:#,###}" + ",00", get_diskon); } if (get_voucher == 0) { l_vou.Text = "0,00"; } else { l_vou.Text = string.Format("{0:#,###}" + ",00", get_voucher); } } } 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(); } } //========================================================================================== try { ckon.sqlCon().Open(); command = "SELECT SUM(transaction_line.SUBTOTAL) as total FROM transaction_line WHERE TRANSACTION_ID='" + l_transaksi.Text + "'"; ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { totall = Convert.ToInt32(ckon.sqlDataRd["total"].ToString()); totall = totall - get_dis_vou; l_total.Text = string.Format("{0:#,###}" + ",00", totall); } } } 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(); } } }
//============FUNGSI CEK TABEL ARTICLE BERDASARKAN ARTICLE ID YANG TELAH DI SCAN== public void cek_article() { int total_amount_new = 0; int good_qty_int = 0; CRUD sql = new CRUD(); //ckon.con.Close(); try { ckon.sqlCon().Open(); String sql_articleHO = "SELECT distinct TOP 1 * FROM article_ho WHERE ARTICLE_ID = '" + t_barcode.Text + "'"; ckon.sqlDataRd = sql.ExecuteDataReader(sql_articleHO, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { id_inv = ckon.sqlDataRd["_id"].ToString(); art_id = ckon.sqlDataRd["ARTICLE_ID"].ToString(); art_name = ckon.sqlDataRd["ARTICLE_NAME"].ToString(); color = ckon.sqlDataRd["COLOR"].ToString(); new_price = Convert.ToInt32(ckon.sqlDataRd["PRICE"].ToString()); unit = ckon.sqlDataRd["UNIT"].ToString(); article_dept = ckon.sqlDataRd["DEPARTMENT"].ToString(); good_qty = "1"; search_txt.Text = color; } } else { String sql_hoLine = "SELECT distinct * FROM ho_line WHERE MUTASI_ORDER_ID = '" + l_transaksi.Text + "' AND ARTICLE_ID = '" + art_id + "'"; ckon.sqlDataRd = sql.ExecuteDataReader(sql_hoLine, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { next_qty = ckon.sqlDataRd["QUANTITY"].ToString(); } int qty = Convert.ToInt32(next_qty) + 1; total_amount_new = new_price * qty; String upd = "UPDATE ho_line SET QUANTITY='" + qty + "', SUBTOTAL='" + total_amount_new + "' WHERE MUTASI_ORDER_ID = '" + l_transaksi.Text + "' AND ARTICLE_ID = '" + art_id + "'"; sql.ExecuteNonQuery(upd); } else { String input = "INSERT INTO ho_line (MUTASI_ORDER_ID,ARTICLE_ID,QUANTITY,UNIT,SUBTOTAL,DEPARTMENT,PRICE,ARTICLE_NAME,COLOR) VALUES ('" + l_transaksi.Text + "','" + art_id + "', '" + good_qty + "', '" + unit + "','" + total_amount_new + "','" + article_dept + "','" + new_price + "','" + art_name + "','" + color + "')"; sql.ExecuteNonQuery(input); } } } 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(); //int count = 0; //if (ckon.myReader.HasRows) //{ // count = count + 1; // while (ckon.myReader.Read()) // { // id_inv = ckon.myReader.GetString("_id"); // art_id = ckon.myReader.GetString("ARTICLE_ID"); // art_name = ckon.myReader.GetString("ARTICLE_NAME"); // //size = ckon.myReader.GetString("SIZE"); // color = ckon.myReader.GetString("COLOR"); // new_price = ckon.myReader.GetInt32("PRICE"); // unit = ckon.myReader.GetString("UNIT"); // article_dept = ckon.myReader.GetString("DEPARTMENT"); // good_qty = "1"; // search_txt.Text = color; // } //} //else //{ // MessageBox.Show("Article Not Found"); // ckon.con.Close(); //} //ckon.con.Close(); //===mengecek apakah article id tersebut dengan mutasi order yang ada sudah ada di mutasi order list atau belum //if (count == 1) //{ // String sql3 = "SELECT distinct * FROM ho_line WHERE MUTASI_ORDER_ID = '" + l_transaksi.Text + "' AND ARTICLE_ID = '" + art_id + "'"; // ckon.con.Open(); // ckon.cmd = new MySqlCommand(sql3, ckon.con); // ckon.myReader = ckon.cmd.ExecuteReader(); // total_amount_new = new_price * Convert.ToInt32(good_qty); // if (ckon.myReader.HasRows) // { // while (ckon.myReader.Read()) // { // next_qty = ckon.myReader.GetString("QUANTITY"); // } // int qt1 = Convert.ToInt32(next_qty) + 1; // total_amount_new = new_price * qt1; // String upd = "UPDATE ho_line SET QUANTITY='" + qt1 + "', SUBTOTAL='" + total_amount_new + "' WHERE MUTASI_ORDER_ID = '" + l_transaksi.Text + "' AND ARTICLE_ID = '" + art_id + "'"; // CRUD masuk = new CRUD(); // masuk.ExecuteNonQuery(upd); // } // else // { // String input = "INSERT INTO ho_line (MUTASI_ORDER_ID,ARTICLE_ID,QUANTITY,UNIT,SUBTOTAL,DEPARTMENT,PRICE,ARTICLE_NAME,COLOR) VALUES ('" + l_transaksi.Text + "','" + art_id + "', '" + good_qty + "', '" + unit + "','" + total_amount_new + "','" + article_dept + "','" + new_price + "','" + art_name + "','" + color + "')"; // CRUD masuk = new CRUD(); // masuk.ExecuteNonQuery(input); // } // ckon.con.Close(); //} t_barcode.Text = ""; setFocus(); }
//===================== GET LINE ========================================== public void retreive(string sort = "", string filter = "", string search = "") { //ckon.con.Close(); CRUD sql = new CRUD(); dgv_request.Rows.Clear(); try { ckon.sqlCon().Open(); String cmd = "SELECT DISTINCT ho_line.ARTICLE_ID, ho_line.QUANTITY, ho_line.UNIT, ho_line.SUBTOTAL, ho_line.ARTICLE_NAME, article_ho.SIZE, ho_line.COLOR, ho_line.PRICE, ho_line.DEPARTMENT, ho_line._id FROM ho_line LEFT JOIN article_ho ON ho_line.ARTICLE_ID=article_ho.ARTICLE_ID WHERE ho_line.MUTASI_ORDER_ID='" + l_transaksi.Text + "' "; if (filter != "") { filter = filter.Replace("ARTICLE_ID", "ho_line.ARTICLE_ID"); cmd += " AND " + filter; } if (sort != "") { cmd += " ORDER BY " + sort; } else { cmd += " ORDER BY ho_line._id DESC"; } ckon.dt = sql.ExecuteDataTable(cmd, ckon.sqlCon()); foreach (DataRow row in ckon.dt.Rows) { int dgRows = dgv_request.Rows.Add(); dgv_request.Rows[dgRows].Cells[0].Value = row["ARTICLE_ID"]; dgv_request.Rows[dgRows].Cells[1].Value = row["ARTICLE_ID"].ToString(); dgv_request.Rows[dgRows].Cells[2].Value = row["ARTICLE_NAME"].ToString(); dgv_request.Rows[dgRows].Cells[3].Value = row["COLOR"].ToString(); dgv_request.Rows[dgRows].Cells[4].Value = row["SIZE"]; dgv_request.Rows[dgRows].Cells[6].Value = row["QUANTITY"].ToString(); dgv_request.Rows[dgRows].Cells[7].Value = row["PRICE"]; dgv_request.Rows[dgRows].Cells[8].Value = row["SUBTOTAL"]; dgv_request.Rows[dgRows].Cells[9].Value = row["DEPARTMENT"].ToString(); } dgv_request.Columns[7].DefaultCellStyle.Format = "#,###"; dgv_request.Columns[8].DefaultCellStyle.Format = "#,###"; dgv_request.Columns[6].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dgv_request.Columns[7].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; dgv_request.Columns[8].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; } catch (Exception e) { MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { ckon.dt.Rows.Clear(); if (ckon.sqlCon().State == ConnectionState.Open) { ckon.sqlCon().Close(); } } //Console.Write(sql); //ckon.cmd = new MySqlCommand(sql, ckon.con); //try //{ // ckon.con.Open(); // ckon.adapter = new MySqlDataAdapter(ckon.cmd); // ckon.adapter.Fill(ckon.dt); // foreach (DataRow row in ckon.dt.Rows) // { // int n = dgv_request.Rows.Add(); // dgv_request.Rows[n].Cells[0].Value = row["ARTICLE_ID"]; // dgv_request.Rows[n].Cells[1].Value = row["ARTICLE_ID"].ToString(); // dgv_request.Rows[n].Cells[2].Value = row["ARTICLE_NAME"].ToString(); // dgv_request.Rows[n].Cells[3].Value = row["COLOR"].ToString(); // dgv_request.Rows[n].Cells[4].Value = row["SIZE"]; // dgv_request.Rows[n].Cells[6].Value = row["QUANTITY"].ToString(); // dgv_request.Rows[n].Cells[7].Value = row["PRICE"]; // dgv_request.Rows[n].Cells[8].Value = row["SUBTOTAL"]; // dgv_request.Rows[n].Cells[9].Value = row["DEPARTMENT"].ToString(); // } // dgv_request.Columns[7].DefaultCellStyle.Format = "#,###"; // dgv_request.Columns[8].DefaultCellStyle.Format = "#,###"; // dgv_request.Columns[6].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; // dgv_request.Columns[7].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; // dgv_request.Columns[8].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; // ckon.dt.Rows.Clear(); // ckon.con.Close(); //} //catch //{ } }
//====================AMBIL TOTAL QTY, AMOUNT FROM MUTASI ORDER======================================== public void qty() { total_amount = 0; int total_qty = 0; //ckon.con.Close(); try { String command = "SELECT SUM(CONVERT(INT,QUANTITY)) as total, SUM(SUBTOTAL) as total_amount FROM ho_line where MUTASI_ORDER_ID = '" + l_transaksi.Text + "'"; CRUD sql = new CRUD(); ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { qty2 = ckon.sqlDataRd["total"].ToString(); if (qty2 != "") { l_qty.Text = qty2.ToString(); total_qty = Int32.Parse(qty2); } else { l_qty.Text = "0"; } if (ckon.sqlDataRd["total_amount"].ToString() != "") { total_amount = Convert.ToInt32(ckon.sqlDataRd["total_amount"].ToString()); l_amount.Text = string.Format("{0:#,###}" + ",00", total_amount); } else { l_amount.Text = "0,00"; } } } else { l_qty.Text = "0"; l_amount.Text = "0,00"; } String cmd_Update = "UPDATE ho_header SET TOTAL_QTY='" + total_qty + "', TOTAL_AMOUNT='" + total_amount + "' WHERE MUTASI_ORDER_ID = '" + l_transaksi.Text + "'"; CRUD update = new CRUD(); update.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(); } } //ckon.cmd = new MySqlCommand(sql, ckon.con); //ckon.con.Open(); //ckon.myReader = ckon.cmd.ExecuteReader(); //if (ckon.myReader.HasRows) //{ // while (ckon.myReader.Read()) // { // try // { // qty2 = ckon.myReader.GetString("total"); // l_qty.Text = qty2.ToString(); // total_qty = Int32.Parse(qty2); // total_amount = ckon.myReader.GetInt32("total_amount"); // l_amount.Text = string.Format("{0:#,###}" + ",00", total_amount); // } // catch // { // l_qty.Text = "0"; // l_amount.Text = "0,00"; // } // } //} //String upd = "UPDATE ho_header SET TOTAL_QTY='" + total_qty + "', TOTAL_AMOUNT='" + total_amount + "' WHERE MUTASI_ORDER_ID = '" + l_transaksi.Text + "'"; //CRUD masuk = new CRUD(); //masuk.ExecuteNonQuery(upd); //ckon.con.Close(); }
//========================================================================== private void getCodeFromTo(String kode) { setFocus(); //ckon.con.Close(); try { String command = "SELECT * FROM ho_transaction_type WHERE CODE='" + kode + "'"; CRUD sql = new CRUD(); ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { kode_tipe_from = ckon.sqlDataRd["WAREHOUSE_FROM"].ToString(); kode_tipe_to = ckon.sqlDataRd["WAREHOUSE_TO"].ToString(); kode_movement = ckon.sqlDataRd["CODE"].ToString(); transtypeid = Convert.ToInt32(ckon.sqlDataRd["ID"].ToString()); } if (kode_tipe_from == "Store") { kode_to = kode_tipe_to; combo_mutasiTo.Text = ""; combo_mutasiTo.Items.Clear(); String query = "SELECT CODE, NAME FROM store UNION SELECT CODE, NAME FROM store_relasi"; isi_combo_mutasi(query); } else if (kode_tipe_to == "Store") { kode_from = kode_tipe_from; combo_mutasiTo.Text = ""; combo_mutasiTo.Items.Clear(); String query = "SELECT CODE, NAME FROM store UNION SELECT CODE, NAME FROM store_relasi"; isi_combo_mutasi(query); } else { kode_from = kode_tipe_from; kode_to = kode_tipe_to; combo_mutasiTo.Text = ""; combo_mutasiTo.Items.Clear(); combo_mutasiTo.Items.Add(kode_to); combo_mutasiTo.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()) // { // kode_tipe_from = ckon.myReader.GetString("WAREHOUSE_FROM"); // kode_tipe_to = ckon.myReader.GetString("WAREHOUSE_TO"); // kode_movement = ckon.myReader.GetString("CODE"); // transtypeid = ckon.myReader.GetInt32("ID"); // } // ckon.con.Close(); // if (kode_tipe_from == "Store") // { // kode_to = kode_tipe_to; // combo_mutasiTo.Text = ""; // combo_mutasiTo.Items.Clear(); // String query = "SELECT CODE, NAME FROM store UNION SELECT CODE, NAME FROM store_relasi"; // isi_combo_mutasi(query); // } // else if (kode_tipe_to == "Store") // { // kode_from = kode_tipe_from; // combo_mutasiTo.Text = ""; // combo_mutasiTo.Items.Clear(); // String query = "SELECT CODE, NAME FROM store UNION SELECT CODE, NAME FROM store_relasi"; // isi_combo_mutasi(query); // } // else // { // kode_from = kode_tipe_from; // kode_to = kode_tipe_to; // // // combo_mutasiTo.Text = ""; // combo_mutasiTo.Items.Clear(); // combo_mutasiTo.Items.Add(kode_to); // combo_mutasiTo.SelectedIndex = 0; // } //} //catch //{ } }
public void set_running_number() { DateTime mydate = DateTime.Now; CRUD sql = new CRUD(); bulan_now = mydate.ToString("MM"); tahun_now = mydate.ToString("yy"); number_trans = 0; //ckon.con.Close(); try { ckon.sqlCon().Open(); String cmd = "SELECT * FROM auto_number WHERE Store_Code = 'HO' AND Type_Trans = '45' AND Month='" + bulan_now + "' AND Year='" + tahun_now + "'"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { number_trans = Convert.ToInt32(ckon.sqlDataRd["Number"].ToString()); } number_trans = number_trans + 1; number_trans_string = number_trans.ToString().PadLeft(5, '0');//wahyu final_running_number = "IO/HO-" + tahun_now + "" + bulan_now + "-" + number_trans_string; l_transaksi.Text = final_running_number; String cmd_update = "UPDATE auto_number SET Number = '" + number_trans + "' WHERE Type_Trans='45' AND Year='" + tahun_now + "' AND Month='" + bulan_now + "'"; sql.ExecuteNonQuery(cmd_update); } else { number_trans = number_trans + 1; number_trans_string = number_trans.ToString().PadLeft(5, '0');//wahyu final_running_number = "IO/HO-" + tahun_now + "" + bulan_now + "-" + number_trans_string; l_transaksi.Text = final_running_number; String cmd_insert = "INSERT INTO auto_number (Store_Code,Year,Month,Number,Type_Trans) VALUES ('HO','" + tahun_now + "','" + bulan_now + "','" + number_trans + "','45')"; sql.ExecuteNonQuery(cmd_insert); } } 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.con.Open(); //ckon.cmd = new MySqlCommand(sql, ckon.con); //ckon.myReader = ckon.cmd.ExecuteReader(); //if (ckon.myReader.HasRows) //{ // while (ckon.myReader.Read()) // { // number_trans = ckon.myReader.GetInt32("Number"); // } // number_trans = number_trans + 1; // number_trans_string = number_trans.ToString().PadLeft(5, '0');//wahyu // final_running_number = "IO/HO-" + tahun_now + "" + bulan_now + "-" + number_trans_string; // l_transaksi.Text = final_running_number; // String query = "UPDATE auto_number SET Number = '" + number_trans + "' WHERE Type_Trans='45' AND Year='" + tahun_now + "' AND Month='" + bulan_now + "'"; // CRUD ubah = new CRUD(); // ubah.ExecuteNonQuery(query); //} //else //{ // number_trans = number_trans + 1; // number_trans_string = number_trans.ToString().PadLeft(5, '0');//wahyu // final_running_number = "IO/HO-" + tahun_now + "" + bulan_now + "-" + number_trans_string; // l_transaksi.Text = final_running_number; // String query = "INSERT INTO auto_number (Store_Code,Year,Month,Number,Type_Trans) VALUES ('HO','" + tahun_now + "','" + bulan_now + "','" + number_trans + "','45')"; // CRUD ubah = new CRUD(); // ubah.ExecuteNonQuery(query); //} //ckon.con.Close(); }
//=========METHOD GET DATA FROM AUTO_NUMBER TABLE FOR SALES TRANSACTION public void get_running_number() { //DevCode code = new DevCode(); String device_code = ""; //device_code = code.aDevCode; String command = ""; try { ckon.sqlCon().Open(); if (Properties.Settings.Default.DevCode != "null" && type_trans == "7") { command = "SELECT * FROM auto_number WHERE Store_Code = '" + store_code + "' AND Type_Trans = '7' AND Dev_Code='" + device_code + "'"; } else { command = "SELECT * FROM auto_number WHERE Store_Code = '" + store_code + "' AND Type_Trans = '" + type_trans + "'"; } CRUD sql = new CRUD(); ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { bulan_trans = ckon.sqlDataRd["Month"].ToString(); number_trans = Convert.ToInt32(ckon.sqlDataRd["Number"].ToString()); } if (bulan_now == bulan_trans) { number_trans = number_trans + 1; if (number_trans < 10) { number_trans_string = "0000" + number_trans.ToString(); } else if (number_trans < 100) { number_trans_string = "000" + number_trans.ToString(); } else if (number_trans < 1000) { number_trans_string = "00" + number_trans.ToString(); } else if (number_trans < 10000) { number_trans_string = "0" + number_trans.ToString(); } else { number_trans_string = number_trans.ToString(); } //==MEMBUAT STRING FINAL RUNNING NUMBER if (Properties.Settings.Default.DevCode != "null" && type_trans == "7") { final_running_number = awal_number + "/" + store_code + "-" + tahun_now + "" + bulan_trans + "-" + number_trans_string + "-" + Properties.Settings.Default.DevCode; } else { final_running_number = awal_number + "/" + store_code + "-" + tahun_now + "" + bulan_trans + "-" + number_trans_string; } //final_running_number = awal_number + "/" + store_code + "-" + tahun_now + "" + bulan_trans + "-" + number_trans_string; //============UPDATE KE TABEL AUTO_NUMBER================ } else { number_trans = 1; bulan_trans = bulan_now; //MENJADIKAN BULAN TRANSAKSI = BULAN SEKARANG //==MEMBUAT STRING FINAL RUNNING NUMBER //final_running_number = awal_number + "/" + store_code + "-" + tahun_now + "" + bulan_trans + "-00001"; if (Properties.Settings.Default.DevCode != "null" && type_trans == "7") { final_running_number = awal_number + "/" + store_code + "-" + tahun_now + "" + bulan_trans + "-00001-" + device_code; } else { final_running_number = awal_number + "/" + store_code + "-" + tahun_now + "" + bulan_trans + "-00001"; } } } else { number_trans = 1; bulan_trans = bulan_now;//BULAN TRANSAKSI = BULAN SEKARANG if (Properties.Settings.Default.DevCode != "null" && type_trans == "7") { final_running_number = awal_number + "/" + store_code + "-" + tahun_now + "" + bulan_trans + "-00001-" + device_code; command = "INSERT INTO auto_number (Store_Code,Month,Number,Type_Trans,Dev_Code) VALUES ('" + store_code + "','" + bulan_trans + "','0','" + type_trans + "','" + Properties.Settings.Default.DevCode + "')"; } else { final_running_number = awal_number + "/" + store_code + "-" + tahun_now + "" + bulan_trans + "-00001"; command = "INSERT INTO auto_number (Store_Code,Month,Number,Type_Trans) VALUES ('" + store_code + "','" + bulan_trans + "','0','" + type_trans + "')"; } CRUD ubah = new CRUD(); ubah.ExecuteNonQuery(command); } } 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 total_trans() { string command; int qty = 0; int total_qty = 0; int cash2 = 0; int edc_total = 0; int petty = 0; CRUD sql = new CRUD(); try { ckon.sqlCon().Open(); command = "SELECT SUM(transaction_line.QUANTITY) as total FROM [transaction] INNER JOIN transaction_line " + "ON transaction_line.TRANSACTION_ID = [transaction].TRANSACTION_ID " + "WHERE [transaction].ID_C_STORE = '" + id_Cstore + "' AND([transaction].STATUS = '1' or [transaction].STATUS = '2')"; ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { qty = ckon.sqlDataRd["total"].ToString() != "" ? Convert.ToInt32(ckon.sqlDataRd["total"].ToString()) : 0; total_qty = total_qty + qty; } } else { total_qty = 0; } l_qty.Text = total_qty.ToString(); //======================================================================================================== date = mydate.ToString("yyyy-MM-dd"); command = "SELECT SUM([transaction].TOTAL) as total FROM [transaction] WHERE ID_C_STORE = '" + id_Cstore + "' AND(STATUS='1' or STATUS='2')"; ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { amount = ckon.sqlDataRd["total"].ToString() != "" ? Convert.ToInt32(ckon.sqlDataRd["total"].ToString()) : 0; if (amount <= 0) { l_total_amount.Text = "0,00"; } else { l_total_amount.Text = string.Format("{0:#,###}" + ",00", amount); } } } else { l_total_amount.Text = "0,00"; } //=================================================================================================== command = "SELECT SUM([transaction].CASH) as total FROM [transaction] WHERE ID_C_STORE = '" + id_Cstore + "' AND (STATUS='1' or STATUS='2')"; ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { cash = ckon.sqlDataRd["total"].ToString() != "" ? Convert.ToInt32(ckon.sqlDataRd["total"].ToString()) : 0; } } else { cash = 0; } command = "SELECT SUM([transaction].CHANGEE) as total FROM [transaction] WHERE ID_C_STORE = '" + id_Cstore + "' AND (STATUS='1' or STATUS='2')"; ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { change = ckon.sqlDataRd["total"].ToString() != "" ? Convert.ToInt32(ckon.sqlDataRd["total"].ToString()) : 0; } } else { change = 0; } cash2 = cash - change; if (cash2 <= 0) { l_cash.Text = "0,00"; } else { l_cash.Text = string.Format("{0:#,###}" + ",00", cash2); } //=================================================================================================== command = "SELECT SUM([transaction].DISCOUNT) as total FROM [transaction] WHERE ID_C_STORE = '" + id_Cstore + "' AND (STATUS='1' or STATUS='2')"; ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { diskon = ckon.sqlDataRd["total"].ToString() != "" ? Convert.ToInt32(ckon.sqlDataRd["total"].ToString()) : 0; if (diskon <= 0) { l_discount.Text = "0,00"; } else { l_discount.Text = string.Format("{0:#,###}" + ",00", diskon); } } } else { l_discount.Text = "0,00"; } //=================================================================================================== command = "SELECT SUM([transaction].EDC) as total FROM [transaction] WHERE ID_C_STORE = '" + id_Cstore + "' AND (STATUS='1' or STATUS='2')"; ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { edc = ckon.sqlDataRd["total"].ToString() != "" ? Convert.ToInt32(ckon.sqlDataRd["total"].ToString()) : 0; } } else { edc = 0; } command = "SELECT SUM([transaction].EDC2) as total FROM [transaction] WHERE ID_C_STORE = '" + id_Cstore + "' AND (STATUS='1' or STATUS='2')"; ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { edc2 = ckon.sqlDataRd["total"].ToString() != "" ? Convert.ToInt32(ckon.sqlDataRd["total"].ToString()) : 0; } } else { edc2 = 0; } edc_total = edc + edc2; if (edc_total <= 0) { l_edc.Text = "0,00"; } else { l_edc.Text = string.Format("{0:#,###}" + ",00", edc_total); } command = "SELECT SUM([transaction].VOUCHER) as total FROM [transaction] WHERE ID_C_STORE = '" + id_Cstore + "' AND (STATUS='1' or STATUS='2')"; ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { voucher = ckon.sqlDataRd["total"].ToString() != "" ? Convert.ToInt32(ckon.sqlDataRd["total"].ToString()) : 0; if (voucher <= 0) { l_voucher.Text = "0,00"; } else { l_voucher.Text = string.Format("{0:#,###}" + ",00", voucher); } } } else { l_voucher.Text = "0,00"; } //============MENDAPATKAN PETTY CASH YG TERSISA DARI TABEL STORE=========== command = "SELECT STORE.BUDGET_TO_STORE FROM STORE"; ckon.sqlDataRd = sql.ExecuteDataReader(command, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { petty = ckon.sqlDataRd["BUDGET_TO_STORE"].ToString() != "" ? Convert.ToInt32(ckon.sqlDataRd["BUDGET_TO_STORE"].ToString()) : 0; if (petty == 0) { l_petty.Text = "0,00"; } else { l_petty.Text = string.Format("{0:#,###}" + ",00", petty); } } } else { l_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(); } } }
private void sls_price_KeyPress(object sender, KeyPressEventArgs e) { CRUD sql = new CRUD(); int sales_price = System.Convert.ToInt32(sls_price.Text.ToString()); try { if (e.KeyChar == (char)Keys.Enter) { ckon.sqlCon().Open(); string cmd = "SELECT * FROM transaction_line WHERE TRANSACTION_ID ='" + idTransLine + "' AND ARTICLE_ID='" + idArticle + "'"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { qty = Convert.ToInt32(ckon.sqlDataRd["QUANTITY"].ToString()); subtotal = Convert.ToInt32(ckon.sqlDataRd["SUBTOTAL"].ToString()); totDisc = Convert.ToInt32(ckon.sqlDataRd["DISCOUNT"].ToString()); oldPrice = Convert.ToInt32(ckon.sqlDataRd["PRICE"].ToString()); } } if (oldPrice != sales_price) { int total = sales_price * qty; String cmd_update = "UPDATE transaction_line SET PRICE='" + sales_price + "',DISCOUNT=0 ,SUBTOTAL='" + total + "' WHERE TRANSACTION_ID='" + idTransLine + "' AND ARTICLE_ID='" + idArticle + "'"; sql.ExecuteNonQuery(cmd_update); } //ckon.cmd = new MySqlCommand(sql, ckon.con); //ckon.con.Open(); //ckon.myReader = ckon.cmd.ExecuteReader(); //int count = 0; //while (ckon.myReader.Read()) //{ // count = count + 1; // qty = ckon.myReader.GetInt32("QUANTITY"); // subtotal = ckon.myReader.GetInt32("SUBTOTAL"); // totDisc = ckon.myReader.GetInt32("DISCOUNT"); // oldPrice = ckon.myReader.GetInt32("PRICE"); //} //ckon.con.Close(); //if (oldPrice != sales_price) //{ // int total = sales_price * qty; // String sql3 = "UPDATE transaction_line SET PRICE='" + sales_price + "',DISCOUNT=0 ,SUBTOTAL='" + total + "' WHERE TRANSACTION_ID='" + idTransLine + "' AND ARTICLE_ID='" + idArticle + "'"; // CRUD input = new CRUD(); // input.ExecuteNonQuery(sql3); //} //uc_coba.Instance.itung_total(); //uc_coba.Instance.retreive(); this.Close(); } } catch { } }
//=================================MENGHITUNG TOTAL CASH================================================================ public void itung_cash() { CRUD sql = new CRUD(); DateTime mydate = DateTime.Now; String date = mydate.ToString("yyyy-MM-dd"); try { ckon.sqlCon().Open(); String cmd_transCash = "SELECT SUM([transaction].CASH) as total FROM [transaction] WHERE ID_C_STORE = '" + id_cStrore2 + "' AND (STATUS='1' or STATUS='2')"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd_transCash, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { cash = Convert.ToInt32(ckon.sqlDataRd["total"].ToString()); } } else { cash = 0; } String cmd_transChange = "SELECT SUM([transaction].CHANGEE) as total FROM [transaction] WHERE ID_C_STORE = '" + id_cStrore2 + "' AND (STATUS='1' or STATUS='2')"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd_transChange, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { change = Convert.ToInt32(ckon.sqlDataRd["total"].ToString()); } } else { change = 0; } cash2 = cash - change; if (cash2 <= 0) { l_cash.Text = "0,00"; t_cash.Text = "0,00"; } else { l_cash.Text = string.Format("{0:#,###}" + ",00", cash2); t_cash.Text = string.Format("{0:#,###}", cash2); valueCash = cash2; } } 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 FOR GET DATA FROM DISCOUNT CODE FROM DISCOUNT HEADER============================ public void data_from_id() { CRUD sql = new CRUD(); try { ckon.sqlCon().Open(); //String cmd = "Select * from promotion where DISCOUNT_CODE='" + id_diskon + "'"; String cmd = "SELECT a.DiscountCode, a.DiscountName, b.Code, a.DiscountType FROM DiscountSetup a " + "INNER JOIN DiscountSetupLines b ON b.DiscountSetupId = a.Id " + "INNER JOIN article c ON c.ARTICLE_ID = b.Code " + "WHERE(a.DiscountType = 4 OR a.DiscountType = 5) AND a.DiscountCode = '" + id_diskon + "'"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { diskon_kode = ckon.sqlDataRd["DiscountCode"].ToString(); diskon_name = ckon.sqlDataRd["DiscountName"].ToString(); //diskon_ktg = ckon.sqlDataRd["DISCOUNT_CATEGORY"].ToString(); //diskon_desc = ckon.sqlDataRd["DESCRIPTION"].ToString(); //status = ckon.sqlDataRd["STATUS"].ToString(); art_id_diskon = ckon.sqlDataRd["Code"].ToString(); disc_type = ckon.sqlDataRd["DiscountType"].ToString(); //disc_desc = ckon.sqlDataRd["DISCOUNT_DESC"].ToString(); //l_diskon_name.Text = diskon_name; t_disc_name.Text = diskon_name; l_d_code.Text = diskon_kode; //l_d_name.Text = diskon_kode; l_d_type.Text = disc_type; } } } 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()) // { // diskon_kode = ckon.myReader.GetString("DISCOUNT_CODE"); // diskon_name = ckon.myReader.GetString("DISCOUNT_NAME"); // diskon_ktg = ckon.myReader.GetString("DISCOUNT_CATEGORY"); // diskon_desc = ckon.myReader.GetString("DESCRIPTION"); // status = ckon.myReader.GetString("STATUS"); // art_id_diskon = ckon.myReader.GetString("ARTICLE_ID"); // disc_type = ckon.myReader.GetString("DISCOUNT_TYPE"); // disc_desc = ckon.myReader.GetString("DISCOUNT_DESC"); // //l_diskon_name.Text = diskon_name; // t_disc_name.Text = diskon_name; // l_d_ctg.Text = diskon_ktg; // l_d_code.Text = diskon_kode; // l_d_desc.Text = diskon_desc; // } // } // catch // { } //} }
//=========METHOD GET DATA FROM AUTO_NUMBER TABLE FOR SALES TRANSACTION public void get_running_number() { CRUD sql = new CRUD(); try { ckon.sqlCon().Open(); String cmd = "SELECT TOP 1 Number FROM auto_number WHERE Store_Code = '" + store_code + "' AND Type_Trans = '3' AND Month='" + bulan_now + "' AND Year='" + tahun_now + "'"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { number_trans = Convert.ToInt32(ckon.sqlDataRd["Number"].ToString()); } number_trans = number_trans + 1; number_trans_string = number_trans.ToString().PadLeft(5, '0');// if (Properties.Settings.Default.DevCode == "null") { final_running_number = "MT/" + store_code + "-" + tahun_now + "" + bulan_now + "-" + number_trans_string; } else { final_running_number = "MT/" + store_code + "-" + tahun_now + "" + bulan_now + "-" + number_trans_string + "-" + Properties.Settings.Default.DevCode; } l_transaksi.Text = final_running_number; String cmd_update = "UPDATE auto_number SET Number = '" + number_trans + "' WHERE Type_Trans='3' AND Year='" + tahun_now + "' AND Month='" + bulan_now + "'"; CRUD update = new CRUD(); update.ExecuteNonQuery(cmd_update); } else { number_trans = number_trans + 1; number_trans_string = number_trans.ToString().PadLeft(5, '0');//wahyu String cmd_insert = ""; if (Properties.Settings.Default.DevCode == "null") { final_running_number = "MT/" + store_code + "-" + tahun_now + "" + bulan_now + "-" + number_trans_string; cmd_insert = "INSERT INTO auto_number (Store_Code,Year,Month,Number,Type_Trans) VALUES ('" + store_code + "','" + tahun_now + "','" + bulan_now + "','" + number_trans + "','3')"; } else { final_running_number = "MT/" + store_code + "-" + tahun_now + "" + bulan_now + "-" + number_trans_string + "-" + Properties.Settings.Default.DevCode; cmd_insert = "INSERT INTO auto_number (Store_Code,Year,Month,Number,Type_Trans,Dev_Code) VALUES ('" + store_code + "','" + tahun_now + "','" + bulan_now + "','" + number_trans + "','3','" + Properties.Settings.Default.DevCode + "')"; } l_transaksi.Text = final_running_number; CRUD insert = new CRUD(); insert.ExecuteNonQuery(cmd_insert); } } 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 FOR GET DATA FROM DISCOUNT LINE================================ public void retreive() { CRUD sql = new CRUD(); //ckon.con.Close(); dgv_purchase.Rows.Clear(); try { ckon.sqlCon().Open(); //String cmd = "SELECT * FROM promotion_line WHERE DISCOUNT_CODE='" + id_diskon + "'"; String cmd = "select c.ARTICLE_ID, c.ARTICLE_NAME, a.DiscountCode, a.DiscountName, a.DiscountType, a.DiscountPercent as HeaderDiscountPercent, a.DiscountCash as HeaderDiscountCash, " + "a.QtyMin as HeaderQtyMin, a.QtyMax as HeaderQtyMax, a.AmountMin as HeaderAmountMin, a.AmountMax as HeaderAmountMax, b.DiscountPrecentage, b.DiscountCash, " + "b.QtyMin, b.QtyMax, b.AmountMin, b.AmountMax from DiscountSetup a join DiscountSetupLines b " + "on a.Id = b.DiscountSetupId join article c " + "on b.Code = c.ARTICLE_ID where a.DiscountCode = '" + id_diskon + "'"; ckon.dt = sql.ExecuteDataTable(cmd, ckon.sqlCon()); foreach (DataRow row in ckon.dt.Rows) { int dgRows = dgv_purchase.Rows.Add(); dgv_purchase.Rows[dgRows].Cells[0].Value = row["ARTICLE_ID"].ToString(); dgv_purchase.Rows[dgRows].Cells[1].Value = row["ARTICLE_NAME"].ToString(); dgv_purchase.Rows[dgRows].Cells[2].Value = row["DiscountPrecentage"].ToString(); dgv_purchase.Rows[dgRows].Cells[3].Value = row["DiscountCash"].ToString(); dgv_purchase.Rows[dgRows].Cells[4].Value = row["QtyMin"].ToString(); dgv_purchase.Rows[dgRows].Cells[5].Value = row["QtyMax"].ToString(); dgv_purchase.Rows[dgRows].Cells[6].Value = row["AmountMin"].ToString(); dgv_purchase.Rows[dgRows].Cells[7].Value = row["AmountMax"].ToString(); dgv_purchase.Rows[dgRows].Cells[8].Value = row["HeaderDiscountPercent"].ToString(); dgv_purchase.Rows[dgRows].Cells[9].Value = row["HeaderDiscountCash"].ToString(); dgv_purchase.Rows[dgRows].Cells[10].Value = row["HeaderQtyMin"].ToString(); dgv_purchase.Rows[dgRows].Cells[11].Value = row["HeaderQtyMax"].ToString(); dgv_purchase.Rows[dgRows].Cells[12].Value = row["HeaderAmountMin"].ToString(); dgv_purchase.Rows[dgRows].Cells[13].Value = row["HeaderAmountMax"].ToString(); dgv_purchase.Columns[2].DefaultCellStyle.Format = "#,###"; dgv_purchase.Columns[3].DefaultCellStyle.Format = "#,###"; dgv_purchase.Columns[4].DefaultCellStyle.Format = "#,###"; dgv_purchase.Columns[5].DefaultCellStyle.Format = "#,###"; dgv_purchase.Columns[6].DefaultCellStyle.Format = "#,###"; dgv_purchase.Columns[7].DefaultCellStyle.Format = "#,###"; dgv_purchase.Columns[8].DefaultCellStyle.Format = "#,###"; dgv_purchase.Columns[9].DefaultCellStyle.Format = "#,###"; dgv_purchase.Columns[10].DefaultCellStyle.Format = "#,###"; dgv_purchase.Columns[11].DefaultCellStyle.Format = "#,###"; dgv_purchase.Columns[12].DefaultCellStyle.Format = "#,###"; dgv_purchase.Columns[13].DefaultCellStyle.Format = "#,###"; } //==========================FUNCTION FOR HIDE FIELD WHEN FIELD EMPTY=============================================== foreach (DataGridViewColumn clm in dgv_purchase.Columns) { dgv_purchase.Columns[clm.Index].Visible = false; //bool notAvailable = true; bool notAvailable = false; foreach (DataGridViewRow row in dgv_purchase.Rows) { if (row.Cells[clm.Index].Value != null) { // If string of value is empty if (row.Cells[clm.Index].Value.ToString() != field_none) { if (row.Cells[clm.Index].Value.ToString() != field_none2) { if (row.Cells[clm.Index].Value.ToString() != "0,0000") { if (row.Cells[clm.Index].Value.ToString() != "0") { if (row.Cells[clm.Index].Value.ToString() != "") { notAvailable = true; break; } } } } } } } if (notAvailable) { //dgv_purchase.Columns[clm.Index].Visible = false; dgv_purchase.Columns[clm.Index].Visible = true; } } } catch (Exception e) { MessageBox.Show(e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { ckon.dt.Rows.Clear(); if (ckon.sqlCon().State == ConnectionState.Open) { ckon.sqlCon().Close(); } } }
//================================================================================== //====================AMBIL TOTAL QTY FROM MUTASI ORDER======================================== public void qty() { CRUD sql = new CRUD(); try { ckon.sqlCon().Open(); String cmd_moQty = "SELECT SUM(mutasiorder_line.QUANTITY) as total FROM mutasiorder_line where MUTASI_ORDER_ID = '" + l_transaksi.Text + "'"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd_moQty, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { if (ckon.sqlDataRd["total"].ToString() != "") { qty2 = ckon.sqlDataRd["total"].ToString(); l_qty.Text = qty2.ToString(); } else { l_qty.Text = "0"; } } } else { l_qty.Text = "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(); } } //=======================MENGHITUNG TOTAL AMOUNT DARI TRANSASKI LINE=================== try { ckon.sqlCon().Open(); String cmd_moSubTotal = "SELECT SUM(mutasiorder_line.SUBTOTAL) as total_amount FROM mutasiorder_line where MUTASI_ORDER_ID = '" + l_transaksi.Text + "'"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd_moSubTotal, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { if (ckon.sqlDataRd["total_amount"].ToString() != "") { total_amount = Convert.ToInt32(ckon.sqlDataRd["total_amount"].ToString()); l_amount.Text = string.Format("{0:#,###}" + ",00", total_amount); } else { l_amount.Text = "0,00"; } } } else { l_amount.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(); } } }
//================================================================================== //=====================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()); } if (bg_ToStore == 0) { l_budget.Text = "0,00"; } else { l_budget.Text = String.Format("{0:#,###}" + ",00", bg_ToStore); } } } 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"); // } // if(bg_ToStore==0) // { l_budget.Text = "0,00"; } // else // { l_budget.Text = String.Format("{0:#,###}" + ",00", bg_ToStore); } // //if(bg_ToCasir==0) // //{ l_b_ToCashier.Text = "0,00"; } // //else // //{ l_b_ToCashier.Text = String.Format("{0:#,###}" + ",00", bg_ToCasir); } //} //ckon.con.Close(); }
//=========================================================================================== //==============================ACTION MINUS, PLUS OR DELETE DATA============================ private void dgv_request_CellClick(object sender, DataGridViewCellEventArgs e) { CRUD sql = new CRUD(); this.ActiveControl = t_barcode; t_barcode.Focus(); if (dgv_request.Columns[e.ColumnIndex].Name == "Delete") { String DEL = dgv_request.SelectedRows[0].Cells[1].Value.ToString(); String cmd_delete = "DELETE FROM mutasiorder_line WHERE MUTASI_ORDER_ID='" + l_transaksi.Text + "' AND ARTICLE_ID='" + DEL + "'"; CRUD delete = new CRUD(); delete.ExecuteNonQuery(cmd_delete); retreive(); qty(); } if (dgv_request.Columns[e.ColumnIndex].Name == "plus") { //ckon.con.Close(); String _id2 = dgv_request.SelectedRows[0].Cells[0].Value.ToString(); String ID = dgv_request.SelectedRows[0].Cells[1].Value.ToString(); String quantity = dgv_request.SelectedRows[0].Cells[7].Value.ToString(); String subtotal = dgv_request.SelectedRows[0].Cells[10].Value.ToString(); String price = dgv_request.SelectedRows[0].Cells[5].Value.ToString(); int new_price = Int32.Parse(price); int new_subtotal = Int32.Parse(subtotal); //mencari good qty dari tabel inventory try { ckon.sqlCon().Open(); String cmd = "SELECT * FROM inventory WHERE ARTICLE_ID = '" + _id2 + "'"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { GOOD_QTY_PLUS = Convert.ToInt32(ckon.sqlDataRd["GOOD_QTY"].ToString()); } } int new_qty = Int32.Parse(quantity); new_qty = new_qty + 1; new_subtotal = new_subtotal + new_price; //BANDINGKAN, JIKA LEBIH BESAR DARI GOOD_QTY JGN DI EKSEKUSI if (new_qty > GOOD_QTY_PLUS) { MessageBox.Show("Quantity Exceeded"); } else { String cmd_update = "UPDATE mutasiorder_line SET QUANTITY='" + new_qty + "',SUBTOTAL='" + new_subtotal + "' WHERE MUTASI_ORDER_ID='" + l_transaksi.Text + "' AND ARTICLE_ID='" + ID + "'"; CRUD update = new CRUD(); update.ExecuteNonQuery(cmd_update); retreive(); qty(); } } 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(); } } } if (dgv_request.Columns[e.ColumnIndex].Name == "minus") { String ID = dgv_request.SelectedRows[0].Cells[1].Value.ToString(); String quantity = dgv_request.SelectedRows[0].Cells[7].Value.ToString(); String subtotal = dgv_request.SelectedRows[0].Cells[10].Value.ToString(); String price = dgv_request.SelectedRows[0].Cells[5].Value.ToString(); int new_price = Int32.Parse(price); int new_subtotal = Int32.Parse(subtotal); int new_qty = Int32.Parse(quantity); new_qty = new_qty - 1; new_subtotal = new_subtotal - new_price; if (new_qty <= 0) { MessageBox.Show("Minimum QTY 1"); } else { String cmd_update = "UPDATE mutasiorder_line SET QUANTITY='" + new_qty + "', SUBTOTAL='" + new_subtotal + "' WHERE MUTASI_ORDER_ID='" + l_transaksi.Text + "' AND ARTICLE_ID='" + ID + "'"; CRUD update = new CRUD(); update.ExecuteNonQuery(cmd_update); retreive(); qty(); } } if (dgv_request.Columns[e.ColumnIndex].Name == "quantity") { String ID = dgv_request.SelectedRows[0].Cells[1].Value.ToString(); String name = dgv_request.SelectedRows[0].Cells[2].Value.ToString(); String size = dgv_request.SelectedRows[0].Cells[3].Value.ToString(); String color = dgv_request.SelectedRows[0].Cells[4].Value.ToString(); String price = dgv_request.SelectedRows[0].Cells[5].Value.ToString(); String quantity = dgv_request.SelectedRows[0].Cells[7].Value.ToString(); String from = "Mut_order"; w_editQty edit_qty = new w_editQty(); edit_qty.detail(l_transaksi.Text, ID, name, size, color, price, quantity); edit_qty.menu_asal(from); edit_qty.cek_qty(); edit_qty.ShowDialog(); } }
//======================LIST HOLD TRANSACTION============================================ public void holding() { CRUD sql = new CRUD(); dgv_hold.Rows.Clear(); //ckon.con.Close(); //string date = tanggal; try { ckon.sqlCon().Open(); String cmd = "SELECT * FROM pettycash WHERE STATUS='0' ORDER BY EXPENSE_DATE ASC"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { id_trans = ckon.sqlDataRd["PETTY_CASH_ID"].ToString(); exp_ctg = ckon.sqlDataRd["EXPENSE_CATEGORY"].ToString(); exp_date = ckon.sqlDataRd["EXPENSE_DATE"].ToString(); exp_total = ckon.sqlDataRd["TOTAL_EXPENSE"].ToString(); int exp_total2 = Int32.Parse(exp_total); int n = dgv_hold.Rows.Add(); dgv_hold.Rows[n].Cells[0].Value = id_trans; dgv_hold.Rows[n].Cells[1].Value = exp_ctg; dgv_hold.Rows[n].Cells[2].Value = exp_total2; } dgv_hold.Columns[2].DefaultCellStyle.Format = "#,###"; } } 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()) // { // id_trans = ckon.myReader.GetString("PETTY_CASH_ID"); // exp_ctg = ckon.myReader.GetString("EXPENSE_CATEGORY"); // exp_date = ckon.myReader.GetString("EXPENSE_DATE"); // exp_total = ckon.myReader.GetString("TOTAL_EXPENSE"); // int exp_total2 = Int32.Parse(exp_total); // int n = dgv_hold.Rows.Add(); // dgv_hold.Rows[n].Cells[0].Value = id_trans; // dgv_hold.Rows[n].Cells[1].Value = exp_ctg; // dgv_hold.Rows[n].Cells[2].Value = exp_total2; // } // dgv_hold.Columns[2].DefaultCellStyle.Format = "#,###"; //} //ckon.con.Close(); }
//============FUNGSI CEK TABEL ARTICLE BERDASARKAN ARTICLE ID YANG TELAH DI SCAN== public void cek_article() { CRUD sql = new CRUD(); int total_amount_new; int good_qty_int = 0; int count = 0; //ckon.con.Close(); try { ckon.sqlCon().Open(); String cmd = "SELECT article._id, article.ARTICLE_ID, article.ARTICLE_NAME, article.SIZE_ID, article.COLOR_ID, article.PRICE, article.UNIT, inventory.GOOD_QTY FROM article INNER JOIN inventory " + "ON inventory.ARTICLE_ID = article._id " + "WHERE article.ARTICLE_ID = '" + t_barcode.Text + "'"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { count = count + 1; while (ckon.sqlDataRd.Read()) { id_inv = ckon.sqlDataRd["_id"].ToString(); art_id = ckon.sqlDataRd["ARTICLE_ID"].ToString(); art_name = ckon.sqlDataRd["ARTICLE_NAME"].ToString(); size = ckon.sqlDataRd["SIZE_ID"].ToString(); color = ckon.sqlDataRd["COLOR_ID"].ToString(); new_price = Convert.ToInt32(ckon.sqlDataRd["PRICE"].ToString()); unit = ckon.sqlDataRd["UNIT"].ToString(); good_qty = ckon.sqlDataRd["GOOD_QTY"].ToString(); good_qty_int = Convert.ToInt32(ckon.sqlDataRd["GOOD_QTY"]); } } //===mengecek apakah article id tersebut dengan mutasi order yang ada sudah ada di mutasi order list atau belum if (count == 1 && good_qty_int >= 1) { total_amount_new = new_price * Convert.ToInt32(good_qty); String cmd_moLine = "SELECT * FROM mutasiorder_line WHERE MUTASI_ORDER_ID = '" + l_transaksi.Text + "' AND ARTICLE_ID = '" + art_id + "'"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd_moLine, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { MessageBox.Show("This Article Has Been Entered"); } else { String cmd_insert = "INSERT INTO mutasiorder_line (MUTASI_ORDER_ID,ARTICLE_ID,QUANTITY,UNIT, SUBTOTAL) VALUES ('" + l_transaksi.Text + "','" + art_id + "', '" + good_qty + "', '" + unit + "','" + total_amount_new + "')"; CRUD insert = new CRUD(); insert.ExecuteNonQuery(cmd_insert); } } } 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(); } } }
//========================================================================================== //======================SIMPAN PETTY CASH HEADER============================================ public void save_petty_header() { CRUD sql = new CRUD(); DateTime mydate = DateTime.Now; DateTime myhour = DateTime.Now; //ckon.con.Close(); try { ckon.sqlCon().Open(); String cmd = "SELECT * FROM pettycash WHERE PETTY_CASH_ID ='" + l_transaksi.Text + "'"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon()); if (!ckon.sqlDataRd.HasRows) { String cmd_insert = "INSERT INTO pettycash (PETTY_CASH_ID,STORE_CODE,EXPENSE_CATEGORY_ID,EXPENSE_CATEGORY,EXPENSE_DATE,STATUS, DATE,TIME,CUST_ID_STORE) VALUES ('" + l_transaksi.Text + "','" + store_code + "','" + exp_code + "', '" + combo_expense.Text + "' , '" + d_tgl_expense.Text + "','0','" + mydate.ToString("yyyy-MM-dd") + "', '" + myhour.ToLocalTime().ToString("H:mm:ss") + "','" + cust_id_store2 + "') "; sql.ExecuteNonQuery(cmd_insert); String cmd_update = "UPDATE auto_number SET Month = '" + bulan_now + "', Number = '" + number_trans + "' WHERE Type_Trans='6'"; sql.ExecuteNonQuery(cmd_update); } } 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(sql0, ckon.con); //ckon.con.Open(); //ckon.myReader = ckon.cmd.ExecuteReader(); //int count0 = 0; //while (ckon.myReader.Read()) //{ // count0 = count0 + 1; //} //ckon.con.Close(); //if (count0 == 0) //{ // String sql = "INSERT INTO pettycash (PETTY_CASH_ID,STORE_CODE,EXPENSE_CATEGORY_ID,EXPENSE_CATEGORY,EXPENSE_DATE,STATUS, DATE,TIME,CUST_ID_STORE) VALUES ('" + l_transaksi.Text + "','"+ store_code +"','"+exp_code +"', '" + combo_expense.Text + "' , '" + d_tgl_expense.Text + "','0','" + mydate.ToString("yyyy-MM-dd") + "', '" + myhour.ToLocalTime().ToString("H:mm:ss") + "','"+ cust_id_store2 +"') "; // ckon.con.Open(); // ckon.cmd = new MySqlCommand(sql, ckon.con); // ckon.cmd.ExecuteNonQuery(); // ckon.con.Close(); // String query = "UPDATE auto_number SET Month = '" + bulan_now + "', Number = '" + number_trans + "' WHERE Type_Trans='6'"; // CRUD ubah = new CRUD(); // ubah.ExecuteNonQuery(query); //} //else //{ //} }
//==================================================================================== //============================BUTTON CONFIRM====================================== private void b_confirm_Click(object sender, EventArgs e) { API_MutasiOrder mutasiOrder = new API_MutasiOrder(); bool api_response; try { this.ActiveControl = t_barcode; t_barcode.Focus(); if (l_qty.Text == "0" || combo_InOut.Text == "" || combo_coverage.Text == "" || combo_mutasiTo.Text == "") { MessageBox.Show("No Item On List And Please Select All Info"); } else { get_data_combo_store(); if (combo_InOut.Text == "IN") { String cmd_update = "UPDATE mutasiorder SET MUTASI_FROM_WAREHOUSE='" + combo_store2 + "' ,MUTASI_TO_WAREHOUSE = '" + store_code + "',REQUEST_DELIVERY_DATE = '" + tanggal_req.Text + "' ,TOTAL_QTY='" + l_qty.Text + "', STATUS='1', CUST_ID_STORE='" + cust_id_store + "', EMPLOYEE_ID='" + epy_id + "', EMPLOYEE_NAME='" + epy_name + "',TOTAL_AMOUNT='" + total_amount + "', NO_SJ = '" + no_sj.Text + "' WHERE MUTASI_ORDER_ID = '" + l_transaksi.Text + "'"; CRUD update = new CRUD(); update.ExecuteNonQuery(cmd_update); api_response = mutasiOrder.mutasiOrder().Result; if (api_response) { MessageBox.Show("data successfully added"); reset(); holding(); new_invoice(); set_running_number(); save_trans_header(); } else { MessageBox.Show("Make Sure You are Connected To Internet"); } } else { if (count_eror != 0) { MessageBox.Show("There Is A Total Quantity That Exceeds Inventory. Please Check Again !"); } else { String cmd_update = "UPDATE mutasiorder SET MUTASI_FROM_WAREHOUSE='" + store_code + "' ,MUTASI_TO_WAREHOUSE = '" + combo_store2 + "',REQUEST_DELIVERY_DATE = '" + tanggal_req.Text + "' ,TOTAL_QTY='" + l_qty.Text + "', STATUS='1', CUST_ID_STORE='" + cust_id_store + "', EMPLOYEE_ID='" + epy_id + "', EMPLOYEE_NAME='" + epy_name + "',TOTAL_AMOUNT='" + total_amount + "', NO_SJ = '" + no_sj.Text + "' WHERE MUTASI_ORDER_ID = '" + l_transaksi.Text + "'"; CRUD update = new CRUD(); update.ExecuteNonQuery(cmd_update); api_response = mutasiOrder.mutasiOrder().Result; if (api_response) { //===POTONG INVENTORY SAAT MUTASI OUT Inv_Line inv = new Inv_Line(); String type_trans = "3"; inv.cek_type_trans(type_trans); inv.mutasi_out(l_transaksi.Text); MessageBox.Show("data successfully added"); reset(); holding(); new_invoice(); set_running_number(); save_trans_header(); } else { MessageBox.Show("Make Sure You are Connected To Internet"); } } } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
private void combo_value_SelectedIndexChanged(object sender, EventArgs e) { string query = "", id = ""; try { if (t_find_article.text == "") { if (combo_ktg2.Text == "BRAND_ID") { query = "SELECT * FROM itemdimensionbrand WHERE Description = '" + combo_value.Text + "'"; } else if (combo_ktg2.Text == "DEPARTMENT_ID") { query = "SELECT * FROM itemdimensiondepartment WHERE Description = '" + combo_value.Text + "'"; } else if (combo_ktg2.Text == "DEPARTMENT_TYPE_ID") { query = "SELECT * FROM itemdimensiondepartmenttype WHERE Description = '" + combo_value.Text + "'"; } else if (combo_ktg2.Text == "SIZE_ID") { query = "SELECT * FROM itemdimensionsize WHERE Description = '" + combo_value.Text + "'"; } else if (combo_ktg2.Text == "COLOR_ID") { query = "SELECT * FROM itemdimensioncolor WHERE Description = '" + combo_value.Text + "'"; } else if (combo_ktg2.Text == "GENDER_ID") { query = "SELECT * FROM itemdimensiongender WHERE Description = '" + combo_value.Text + "'"; } ckon.sqlCon().Open(); CRUD sql = new CRUD(); ckon.sqlDataRd = sql.ExecuteDataReader(query, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { id = ckon.sqlDataRd["Id"].ToString(); } } } } catch (Exception er) { 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 cmd = "select TOP 100 article._id,article.ARTICLE_ID, article.ARTICLE_NAME, size.Description as SIZE_ID, color.Description as COLOR_ID, article.PRICE, inventory.GOOD_QTY FROM article INNER JOIN inventory ON article._id = inventory.ARTICLE_ID INNER JOIN itemdimensioncolor color ON color.Id = article.COLOR_ID INNER JOIN itemdimensionsize size ON size.Id = article.SIZE_ID WHERE article." + combo_ktg2.Text + " = '" + id + "' AND inventory.GOOD_QTY >=1"; get_load_data(cmd); }
//=================== GET DATA ID=================================================== public void get_data_id() { CRUD sql = new CRUD(); //ckon.con.Close(); //String sql = "SELECT * FROM transaction WHERE TRANSACTION_ID='" + id_list + "'"; //ckon.cmd = new MySqlCommand(sql, ckon.con); //ckon.con.Open(); //ckon.myReader = ckon.cmd.ExecuteReader(); //while (ckon.myReader.Read()) //{ // t_custId.Text = ckon.myReader.GetString("CUSTOMER_ID"); // t_spgId.Text = ckon.myReader.GetString("SPG_ID"); // diskon = ckon.myReader.GetInt32("DISCOUNT"); // total = ckon.myReader.GetInt32("TOTAL"); // cash = ckon.myReader.GetInt32("CASH"); // edc = ckon.myReader.GetInt32("EDC"); // edc2 = ckon.myReader.GetInt32("EDC2"); // change = ckon.myReader.GetInt32("CHANGEE"); // noref = ckon.myReader.GetString("NO_REF"); // noref2 = ckon.myReader.GetString("NO_REF2"); // pay_type = ckon.myReader.GetString("PAYMENT_TYPE"); // tgl_trans = ckon.myReader.GetString("DATE"); // id_bank1 = ckon.myReader.GetString("BANK_NAME"); // id_bank2 = ckon.myReader.GetString("BANK_NAME2"); //} try { ckon.sqlCon().Open(); String cmd = "SELECT * FROM [transaction] WHERE TRANSACTION_ID='" + id_list + "'"; ckon.sqlDataRd = sql.ExecuteDataReader(cmd, ckon.sqlCon()); if (ckon.sqlDataRd.HasRows) { while (ckon.sqlDataRd.Read()) { t_custId.Text = ckon.sqlDataRd["CUSTOMER_ID"].ToString(); t_spgId.Text = ckon.sqlDataRd["SPG_ID"].ToString(); diskon = Convert.ToInt32(ckon.sqlDataRd["DISCOUNT"].ToString()); total = Convert.ToInt32(ckon.sqlDataRd["TOTAL"].ToString()); cash = Convert.ToInt32(ckon.sqlDataRd["CASH"].ToString()); edc = Convert.ToInt32(ckon.sqlDataRd["EDC"].ToString()); edc2 = Convert.ToInt32(ckon.sqlDataRd["EDC2"].ToString()); change = Convert.ToInt32(ckon.sqlDataRd["CHANGEE"].ToString()); noref = ckon.sqlDataRd["NO_REF"].ToString(); noref2 = ckon.sqlDataRd["NO_REF2"].ToString(); pay_type = ckon.sqlDataRd["PAYMENT_TYPE"].ToString(); tgl_trans = ckon.sqlDataRd["DATE"].ToString(); id_bank1 = ckon.sqlDataRd["BANK_NAME"].ToString(); id_bank2 = ckon.sqlDataRd["BANK_NAME2"].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(); } } if (pay_type == "0") { string_tipe = "CASH"; rev = "-"; } if (pay_type == "1") { string_tipe = "EDC"; rev = noref; //mencari bank name sesuai bank id get_id_bank(id_bank1, id_bank2); } if (pay_type == "2") { string_tipe = "SPLIT"; rev = noref; //mencari bank name sesuai bank id get_id_bank(id_bank1, id_bank2); } if (pay_type == "3") { string_tipe = "SPLIT EDC"; rev = noref; //mencari bank name sesuai bank id get_id_bank(id_bank1, id_bank2); } //MENCETAK METHOD PEMBAYARAN desc_method_payment(); tot_dis = total + diskon; tot_pay = cash + edc; }