public void getMaxNota()
{
MySqlConnection conn = new MySqlConnection(db.conn());
MySqlCommand nota = new MySqlCommand("select LPAD(count(id)+1,3,0) as total from h_jual where tanggal=curdate()", conn);
conn.Open();
MySqlDataReader reader = nota.ExecuteReader();
while (reader.Read())
{
string date = DateTime.UtcNow.ToString("yyyymmdd");
txtNota.Text = date + reader.GetString("total");
}
conn.Close();
}
public Login()
{
InitializeComponent();
MySqlConnection conn = new MySqlConnection(db.conn());
var cmd = new MySqlCommand("select * from pegawai", conn);
/*MySqlDataReader reader;*/
try
{
// Open the database
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
/*MessageBox.Show("Database Terhubung");*/
Console.WriteLine(reader[4]);
}
}
else
{
Console.WriteLine("No rows found.");
}
// Finally close the connection
conn.Close();
}
catch (Exception ex)
{
// Show any error message.
MessageBox.Show(ex.Message);
}
}
public Supplier_Form(Supplier parent)
{
InitializeComponent();
_parent = parent;
conn = new MySqlConnection(db.conn());
ds = new DataSet();
}
private void button1_Click(object sender, EventArgs e)
{
int no = 0;
String nota = txtNota.Text;
String supplier_id = cbSupplier.SelectedValue.ToString();
String barang_id = cbBarang.SelectedValue.ToString();
String qty = upQty.Text;
//MessageBox.Show(qty);
db = new koneksi();
MySqlConnection conn = new MySqlConnection(db.conn());
MySqlCommand get_harga = new MySqlCommand("SELECT harga_barang FROM barang where id='" + barang_id + "'", conn);
conn.Open();
MySqlDataReader reader = get_harga.ExecuteReader();
int total = 0;
while (reader.Read())
{
total = Int32.Parse(qty) * reader.GetInt32("harga_barang");
}
//int index = dataGridViewAddBarang.Rows.Add();
DataGridViewRow newRow = new DataGridViewRow();
newRow.CreateCells(dataGridViewAddBarang);
newRow.Cells[0].Value = (no + 1).ToString();
newRow.Cells[1].Value = nota;
newRow.Cells[2].Value = supplier_id;
newRow.Cells[3].Value = barang_id;
newRow.Cells[4].Value = qty;
newRow.Cells[5].Value = total;
dataGridViewAddBarang.Rows.Add(newRow);
}
private void prove_pembelian_Load(object sender, EventArgs e)
{
db = new koneksi();
MySqlConnection conn = new MySqlConnection(db.conn());
String approve = "";
MySqlCommand id = new MySqlCommand("select id , status_approve from h_beli where nota_pembelian='" + nota + "' ", conn);
conn.Open();
MySqlDataReader reader = id.ExecuteReader();
while (reader.Read())
{
id_nota = reader.GetString("id");
approve = reader.GetString("status_approve");
}
conn.Close();
if (approve == "1")
{
btn_approve.Visible = false;
}
conn.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter("select db.id_barang , b.nama_barang , db.qty , db.sub_total from d_beli db join barang b on b.id=db.id_barang where id_h_beli='" + id_nota + "'", conn);
DataSet barang = new DataSet();
adapter.Fill(barang, "barang");
dataGridDetail.DataSource = barang.Tables["barang"];
conn.Close();
}
public Supplier()
{
InitializeComponent();
page_add = new Supplier_Form(this);
conn = new MySqlConnection(db.conn());
ds = new DataSet();
loadData();
}
public Kategori()
{
InitializeComponent();
koneksi cone = new koneksi();
conn = new MySqlConnection(cone.conn());
ds = new DataSet();
loadKategori();
}
public CariBarang(Penjualan pejnfrm)
{
InitializeComponent();
koneksi cone = new koneksi();
conn = new MySqlConnection(cone.conn());
ds = new DataSet();
penj = pejnfrm;
}
public Stok()
{
InitializeComponent();
page_add = new Stok_Form(this);
koneksi cone = new koneksi();
conn = new MySqlConnection(cone.conn());
ds = new DataSet();
loadstok();
}
public Stok_Form(Stok parent)
{
InitializeComponent();
_parent = parent;
koneksi cone = new koneksi();
conn = new MySqlConnection(cone.conn());
ds = new DataSet();
loadkategori();
}
public Admin()
{
InitializeComponent();
page_add = new Admin_Add(this);
koneksi cone = new koneksi();
conn = new MySqlConnection(cone.conn());
ds = new DataSet();
loadPegawai();
}
public void getSupplier()
{
db = new koneksi();
MySqlConnection conn = new MySqlConnection(db.conn());
MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT id , nama_supplier from supplier", conn);
DataSet ds1 = new DataSet();
adapter.Fill(ds1, "supplier");
cbSupplier.DataSource = ds1.Tables["supplier"];
cbSupplier.ValueMember = "id";
cbSupplier.DisplayMember = "nama_supplier";
conn.Close();
}
public void getBarang()
{
db = new koneksi();
MySqlConnection conn = new MySqlConnection(db.conn());
MySqlDataAdapter da = new MySqlDataAdapter("SELECT id,nama_barang,harga_barang FROM barang", conn);
DataSet ds3 = new DataSet();
da.Fill(ds3, "barang");
cbBarang.DataSource = ds3.Tables["barang"];
cbBarang.ValueMember = "id";
cbBarang.DisplayMember = "nama_barang";
conn.Close();
}
public void displayPembelian()
{
db = new koneksi();
MySqlConnection conn = new MySqlConnection(db.conn());
MySqlDataAdapter adapter = new MySqlDataAdapter("select hb.nota_pembelian , " +
"s.nama_supplier " +
"from h_beli hb " +
"join supplier s on hb.id_supplier=s.id ", conn);
DataSet ds2 = new DataSet();
adapter.Fill(ds2, "display");
dataGridViewDisplayPembelian.DataSource = ds2.Tables["display"];
conn.Close();
}
private void txtcari_TextChanged(object sender, EventArgs e)
{
DataSet ds2 = new DataSet();
db = new koneksi();
MySqlConnection conn = new MySqlConnection(db.conn());
MySqlDataAdapter adapter = new MySqlDataAdapter("select hb.nota_pembelian , " +
"s.nama_supplier " +
"from h_beli hb " +
"join supplier s on hb.id_supplier=s.id where hb.nota_pembelian LIKE '%" + txtcari.Text + "%'", conn);
adapter.Fill(ds2, "display");
dataGridViewDisplayPembelian.DataSource = ds2.Tables["display"];
conn.Close();
}
public void generateNP()
{
koneksi cone = new koneksi();
MySqlConnection conn = new MySqlConnection(cone.conn());
MySqlCommand nota = new MySqlCommand("select max(id)+1 as 'max_id' from pegawai", conn);
conn.Open();
MySqlDataReader reader = nota.ExecuteReader();
while (reader.Read())
{
string date = DateTime.UtcNow.ToString("yyyy");
txtnomor.ReadOnly = true;
txtnomor.Text = "ADM" + date + reader.GetString("max_id");
}
}
public void getMaxNota()
{
db = new koneksi();
MySqlConnection conn = new MySqlConnection(db.conn());
MySqlCommand nota = new MySqlCommand("select max(id) as 'max_id' from h_beli", conn);
conn.Open();
MySqlDataReader reader = nota.ExecuteReader();
while (reader.Read())
{
string date = DateTime.UtcNow.ToString("yyyy");
txtNota.ReadOnly = true;
txtNota.Text = "1922020" + reader.GetString("max_id") + date;
}
}
private void btn_save_Click(object sender, EventArgs e)
{
nomor_pegawai = txtnomor.Text;
nama_pegawai = txtnama.Text;
jabatan = (cbjabatan.SelectedIndex + 1).ToString();
email = txtemail.Text;
password = txtpassword.Text;
tgl_lahir = dateTimePicker1.Value.ToString("yyyy-MM-dd");
koneksi cone = new koneksi();
MySqlConnection MyConn2 = new MySqlConnection(cone.conn());
if (btn_save.Text == "Simpan")
{
string Query = "insert into pegawai(nomor_pegawai,nama_pegawai,jabatan,email,password,tgl_lahir) " +
"values('" + nomor_pegawai + "','" + nama_pegawai + "','" + jabatan + "','" + email + "','" + password + "','" + tgl_lahir + "');";
MySqlCommand MyCommand2 = new MySqlCommand(Query, MyConn2);
MySqlDataReader MyReader2;
MyConn2.Open();
MyReader2 = MyCommand2.ExecuteReader(); // Here our query will be executed and data saved into the database.
MessageBox.Show("Berhasil Tambah Data");
MyConn2.Close();
clear();
}
if (btn_save.Text == "Ubah")
{
string Query = "update pegawai set nama_pegawai='" + nama_pegawai + "'," +
"jabatan='" + jabatan + "'," +
"email='" + email + "'," +
"password='******'" +
"where nomor_pegawai = '" + nomor_pegawai + "';";
MySqlCommand MyCommand2 = new MySqlCommand(Query, MyConn2);
MySqlDataReader MyReader2;
MyConn2.Open();
MyReader2 = MyCommand2.ExecuteReader(); // Here our query will be executed and data saved into the database.
MessageBox.Show("Berhasil Ubah Data");
MyConn2.Close();
this.Close();
}
_parent.loadPegawai();
}
private void button1_Click_1(object sender, EventArgs e)
{
var email = txt_email.Text;
var pass = txt_pass.Text;
koneksi db;
db = new koneksi();
MySqlConnection conn = new MySqlConnection(db.conn());
var cmd = new MySqlCommand("select * from pegawai where email='" + email + "' and password='******'", conn);
conn.Open();
var pegawai = cmd.ExecuteReader();
pegawai.Read();
if (pegawai.HasRows)
{
var id = pegawai[0].ToString();
int id_jabatan = (int)pegawai[3];
var jabatan = "";
if (id_jabatan == 1)
{
jabatan = "Manager";
}
else
{
jabatan = "Admin";
}
txt_email.ResetText();
txt_pass.ResetText();
Home page_home = new Home(pegawai[2].ToString() + "(" + jabatan + ")", id, id_jabatan);
page_home.Show();
this.Hide();
}
else
{
MessageBox.Show("Login Gagal");
txt_email.ResetText();
txt_pass.ResetText();
txt_email.Focus();
}
}
private void btn_approve_Click(object sender, EventArgs e)
{
db = new koneksi();
MySqlConnection conn = new MySqlConnection(db.conn());
String query = "UPDATE h_beli SET STATUS_APPROVE=1 WHERE ID='" + id_nota + "'";
conn.Open();
MySqlCommand update = new MySqlCommand(query, conn);
update.ExecuteNonQuery();
conn.Close();
for (int i = 0; i < dataGridDetail.Rows.Count - 1; i++)
{
String id_barang = dataGridDetail.Rows[i].Cells["id_barang"].Value.ToString();
int stok_baru = Int32.Parse(dataGridDetail.Rows[i].Cells["qty"].Value.ToString());
int stok_jum_jum = 0;
MySqlCommand jum_stok = new MySqlCommand("select stok from barang where id='" + id_barang + "'", conn);
conn.Open();
MySqlDataReader read_stok = jum_stok.ExecuteReader();
while (read_stok.Read())
{
int stok_awal = Int32.Parse(read_stok.GetString("stok"));
stok_jum_jum = stok_awal + stok_baru;
}
conn.Close();
String up_stok = "UPDATE barang SET stok='" + stok_jum_jum.ToString() + "' WHERE ID='" + id_barang + "'";
conn.Open();
MySqlCommand update_stok = new MySqlCommand(up_stok, conn);
update_stok.ExecuteNonQuery();
conn.Close();
}
this.Close();
}
private void btn_save_Click_1(object sender, EventArgs e)
{
nama_barang = txtnama.Text;
harga_barang = txtharga.Text;
merk = txtmerk.Text;
kategori = (cbkategori.SelectedValue).ToString();
koneksi cone = new koneksi();
MySqlConnection MyConn2 = new MySqlConnection(cone.conn());
if (btn_save.Text == "Simpan")
{
string Query = "insert into barang(nama_barang,harga_barang,merk,id_kategori) " +
"values('" + nama_barang + "','" + harga_barang + "','" + merk + "','" + kategori + "');";
MySqlCommand MyCommand2 = new MySqlCommand(Query, MyConn2);
MySqlDataReader MyReader2;
MyConn2.Open();
MyReader2 = MyCommand2.ExecuteReader(); // Here our query will be executed and data saved into the database.
MessageBox.Show("Berhasil Tambah Data");
MyConn2.Close();
clear();
}
if (btn_save.Text == "Ubah")
{
string Query = "update barang set harga_barang ='" + harga_barang + "',nama_barang ='" + nama_barang + "',Merk ='" + merk + "',id_kategori ='" + kategori + "', updated_at=sysdate()" +
"where id = '" + id + "';";
MySqlCommand MyCommand2 = new MySqlCommand(Query, MyConn2);
MySqlDataReader MyReader2;
MyConn2.Open();
MyReader2 = MyCommand2.ExecuteReader(); // Here our query will be executed and data saved into the database.
MessageBox.Show("Berhasil Ubah Data");
MyConn2.Close();
this.Close();
}
_parent.loadstok();
}
private void btnSave_Click(object sender, EventArgs e)
{
String nota = dataGridViewAddBarang.Rows[0].Cells[1].Value.ToString();
String supplier = dataGridViewAddBarang.Rows[0].Cells[2].Value.ToString();
String date_now = DateTime.UtcNow.ToString("yyyy-MM-dd");
int subtotal = 0;
String mx_id_beli = "";
int index = dataGridViewAddBarang.Rows.Count;
for (int rows = 0; rows < dataGridViewAddBarang.Rows.Count - 1; rows++)
{
subtotal += Convert.ToInt32(dataGridViewAddBarang.Rows[rows].Cells["hargaTotal"].Value);
}
//MessageBox.Show(subtotal.ToString());
db = new koneksi();
MySqlConnection conn = new MySqlConnection(db.conn());
conn.Open();
String query = "INSERT INTO h_beli(ID_SUPPLIER , NOTA_PEMBELIAN , SUBTOTAL , STATUS_APPROVE , CREATED_AT , UPDATED_AT) " +
"VALUES(@ID_SUPPLIER , @NOTA_PEMBELIAN , @SUBTOTAL , @STATUS_APPROVE , @CREATED_AT , @UPDATED_AT)";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@ID_SUPPLIER", supplier);
cmd.Parameters.AddWithValue("@NOTA_PEMBELIAN", nota);
cmd.Parameters.AddWithValue("@SUBTOTAL", subtotal.ToString());
cmd.Parameters.AddWithValue("@CREATED_AT", date_now);
cmd.Parameters.AddWithValue("@UPDATED_AT", date_now);
cmd.Parameters.AddWithValue("@STATUS_APPROVE", "0");
cmd.ExecuteNonQuery();
MySqlCommand id_hbeli = new MySqlCommand("select id from h_beli where nota_pembelian='" + nota + "'", conn);
MySqlDataReader reader = id_hbeli.ExecuteReader();
while (reader.Read())
{
mx_id_beli = reader.GetString("id");
}
conn.Close();
conn.Open();
for (int rows = 0; rows < dataGridViewAddBarang.Rows.Count - 1; rows++)
{
String Note = dataGridViewAddBarang.Rows[rows].Cells["nota_pembelian"].Value.ToString();
String id_supplier = dataGridViewAddBarang.Rows[rows].Cells["id_supplier"].Value.ToString();
String barang_id = dataGridViewAddBarang.Rows[rows].Cells["barang_id"].Value.ToString();
String qty = dataGridViewAddBarang.Rows[rows].Cells["qty"].Value.ToString();
String harga = dataGridViewAddBarang.Rows[rows].Cells["hargaTotal"].Value.ToString();
String query_detail = "INSERT INTO d_beli(id_h_beli , id_barang , nota_pembelian , qty , sub_total , created_at , updated_at) " +
"VALUES(@id_h_beli , @id_barang , @nota_pembelian , @qty , @sub_total , @created_at , @updated_at)";
MySqlCommand cmdDetail = new MySqlCommand(query_detail, conn);
cmdDetail.Parameters.AddWithValue("@id_h_beli", mx_id_beli);
cmdDetail.Parameters.AddWithValue("@id_barang", barang_id);
cmdDetail.Parameters.AddWithValue("@nota_pembelian", Note);
cmdDetail.Parameters.AddWithValue("@qty", qty);
cmdDetail.Parameters.AddWithValue("@sub_total", harga);
cmdDetail.Parameters.AddWithValue("@created_at", date_now);
cmdDetail.Parameters.AddWithValue("@updated_at", date_now);
cmdDetail.ExecuteNonQuery();
}
conn.Close();
getMaxNota();
displayPembelian();
dataGridViewAddBarang.Rows.Clear();
}