private void enter_TextChanged(object sender, EventArgs e)
{
connection CN = new connection();
CN.thisConnection.Open();
if (sBy.Text == "Product_Name")
{
OracleDataAdapter thisAdapter = new OracleDataAdapter("Select product_name, product_id, production_date,unit_price,quantity from product where product_name like '" + enter.Text + "%'", CN.thisConnection);
DataTable data = new DataTable();
thisAdapter.Fill(data);
dataGridView1.DataSource = data;
}
else if (sBy.Text == "Product_ID")
{
OracleDataAdapter thisAdapter = new OracleDataAdapter("Select product_name, product_id, production_date,unit_price,quantity from product where product_id like '" + enter.Text + "%'", CN.thisConnection);
DataTable data = new DataTable();
thisAdapter.Fill(data);
dataGridView1.DataSource = data;
}
CN.thisConnection.Close();
}
private void viewAllPro_Load(object sender, EventArgs e)
{
connection CN = new connection();
CN.thisConnection.Open();
OracleCommand thisCommand = CN.thisConnection.CreateCommand();
thisCommand.CommandText = "SELECT * FROM product order by product_name";
OracleDataReader thisReader = thisCommand.ExecuteReader();
while (thisReader.Read())
{
ListViewItem lsvItem = new ListViewItem();
lsvItem.Text = thisReader["product_name"].ToString();
lsvItem.SubItems.Add(thisReader["Product_id"].ToString());
lsvItem.SubItems.Add(thisReader["production_date"].ToString());
lsvItem.SubItems.Add(thisReader["category"].ToString());
lsvItem.SubItems.Add(thisReader["unit_price"].ToString());
lsvItem.SubItems.Add(thisReader["QUANTITY"].ToString());
lsvItem.SubItems.Add(thisReader["sale_product"].ToString());
lsvItem.SubItems.Add(thisReader["vat"].ToString());
lsvItem.SubItems.Add(thisReader["description"].ToString());
listView1.Items.Add(lsvItem);
}
CN.thisConnection.Close();
}
private void update_Click(object sender, EventArgs e)
{
connection sv = new connection();
sv.thisConnection.Open();
int newItems = int.Parse(newQtyBox.Text);
if (newQtyBox.Text == "")
{
newItems = 0;
}
OracleCommand thisCommand = sv.thisConnection.CreateCommand();
thisCommand.CommandText = "update product set quantity=quantity+" + newItems + ", unit_price='" + newPriceBox.Text + "'where product_id= '" + idBox.Text + "'";
thisCommand.Connection = sv.thisConnection;
thisCommand.CommandType = CommandType.Text;
try
{
thisCommand.ExecuteNonQuery();
MessageBox.Show("Stock Updated Successfully");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
sv.thisConnection.Close();
}
public void fill_listbox()
{
listBox1.Items.Clear();
connection CN = new connection();
CN.thisConnection.Open();
OracleCommand thisCommand = CN.thisConnection.CreateCommand();
thisCommand.CommandType = CommandType.Text;
thisCommand.CommandText = "select distinct customer.customer_name, mail_id, mobile_no, address from customer, invoice where customer.customer_id=invoice.customer_id and invoice_no= '" + invCombo.Text + "'";
thisCommand.ExecuteReader();
DataTable dt = new DataTable();
OracleDataAdapter da = new OracleDataAdapter(thisCommand);
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
listBox1.Items.Add(dr["customer_name"]).ToString();
listBox1.Items.Add(dr["mail_id"]).ToString();
listBox1.Items.Add(dr["mobile_no"]).ToString();
listBox1.Items.Add(dr["address"]).ToString();
}
CN.thisConnection.Close();
}
private void view_Click(object sender, EventArgs e)
{
connection CN = new connection();
CN.thisConnection.Open();
OracleCommand thisCommand = CN.thisConnection.CreateCommand();
thisCommand.CommandText =
"SELECT * FROM customer order by customer_name ";
OracleDataReader thisReader = thisCommand.ExecuteReader();
while (thisReader.Read())
{
ListViewItem lsvItem = new ListViewItem();
lsvItem.Text = thisReader["customer_name"].ToString();
lsvItem.SubItems.Add(thisReader["customer_id"].ToString());
lsvItem.SubItems.Add(thisReader["mail_id"].ToString());
lsvItem.SubItems.Add(thisReader["mobile_no"].ToString());
lsvItem.SubItems.Add(thisReader["address"].ToString());
listView1.Items.Add(lsvItem);
}
CN.thisConnection.Close();
}
private void submit2_Click(object sender, EventArgs e)
{
if (productName.Text == "" || qty.Text == "")
{
MessageBox.Show("Required fill up the fields for entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
connection CN = new connection();
CN.thisConnection.Open();
OracleDataAdapter thisAdapter = new OracleDataAdapter("SELECT * FROM product", CN.thisConnection);
OracleCommandBuilder thisBuilder = new OracleCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "product");
DataRow thisRow = thisDataSet.Tables["product"].NewRow();
int a;
try
{
thisRow["product_name"] = productName.Text;
thisRow["product_id"] = generateID();
thisRow["production_date"] = dateTimePicker1.Value.Date.ToString();
thisRow["category"] = catCombBox.Text;
thisRow["unit_price"] = unitPrice.Text;
thisRow["quantity"] = qty.Text;
thisRow["vat"] = vat.Text;
thisRow["description"] = descrp.Text;
thisDataSet.Tables["product"].Rows.Add(thisRow);
a = thisAdapter.Update(thisDataSet, "product");
if (a == 1)
{
MessageBox.Show("New product added", "INSERTED", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
productName.Text = "";
unitPrice.Clear();
qty.Clear();
vat.Clear();
descrp.Clear();
productName.Focus();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
CN.thisConnection.Close();
}
private void submit_Click(object sender, EventArgs e)
{
if (custName.Text == "" || mNo.Text == "")
{
MessageBox.Show("Required fill up all the fields for entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
connection sv = new connection();
sv.thisConnection.Open();
OracleDataAdapter thisAdapter = new OracleDataAdapter("SELECT * FROM customer", sv.thisConnection);
OracleCommandBuilder thisBuilder = new OracleCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "customer");
DataRow thisRow = thisDataSet.Tables["customer"].NewRow();
try
{
string cusid, dn;
string ex = "C";
dn = DateTime.Now.ToString("HHmmss-MM-dd-yy");
cusid = String.Concat(dn, ex);
thisRow["CUSTOMER_NAME"] = custName.Text;
thisRow["CUSTOMER_ID"] = cusid;
thisRow["MAIL_ID"] = mailID.Text;
thisRow["MOBILE_NO"] = mNo.Text;
thisRow["ADDRESS"] = address.Text;
thisDataSet.Tables["customer"].Rows.Add(thisRow);
thisAdapter.Update(thisDataSet, "customer");
MessageBox.Show("New customer added", "Submitted", MessageBoxButtons.OK, MessageBoxIcon.Information);
custName.Text = "";
mailID.Clear();
mNo.Clear();
address.Clear();
custName.Focus();
}
catch
{
MessageBox.Show("Insert error in database");
}
sv.thisConnection.Close();
}
private void custNmCombo_SelectedIndexChanged(object sender, EventArgs e)
{
connection CN = new connection();
CN.thisConnection.Open();
OracleCommand thisCommand = CN.thisConnection.CreateCommand();
thisCommand.CommandText = "SELECT * FROM customer where customer_name= '" + custNmCombo.Text + "'";
OracleDataReader thisReader = thisCommand.ExecuteReader();
while (thisReader.Read())
{
custIDCombo.Text = thisReader["Customer_id"].ToString();
}
CN.thisConnection.Close();
}
private void invCombo_SelectedIndexChanged(object sender, EventArgs e)
{
connection CN = new connection();
CN.thisConnection.Open();
OracleCommand thisCommand = CN.thisConnection.CreateCommand();
thisCommand.CommandText = "SELECT distinct * FROM invoice where invoice_no= '" + invCombo.Text + "'";
OracleDataReader thisReader = thisCommand.ExecuteReader();
while (thisReader.Read())
{
invCombo.Text = thisReader["invoice_no"].ToString();
}
CN.thisConnection.Close();
}
private void invoice_Load(object sender, EventArgs e)
{
connection CN = new connection();
CN.thisConnection.Open();
OracleCommand thisCommand = new OracleCommand("select distinct invoice_no from invoice");
thisCommand.Connection = CN.thisConnection;
thisCommand.CommandType = CommandType.Text;
OracleDataReader thisReader = thisCommand.ExecuteReader();
while (thisReader.Read())
{
invCombo.Items.Add(thisReader["invoice_no"].ToString());
}
}
private void button1_Click(object sender, EventArgs e)
{
connection con = new connection();
con.thisConnection.Open();
OracleDataAdapter thisAdapter = new OracleDataAdapter("delete from product where product_id ='" + textBox2.Text + "'", con.thisConnection);
thisAdapter.SelectCommand.ExecuteNonQuery();
con.thisConnection.Close();
MessageBox.Show("Product deleted successfully", "DELETE", MessageBoxButtons.OK, MessageBoxIcon.Information);
textBox2.Clear();
dataGridView1.ClearSelection();
OracleDataAdapter mehrab = new OracleDataAdapter("Select product_name, product_id, production_date,unit_price,quantity from product", con.thisConnection);
DataTable data = new DataTable();
mehrab.Fill(data);
dataGridView1.DataSource = data;
}
private void button1_Click(object sender, EventArgs e)
{
connection con = new connection();
con.thisConnection.Open();
OracleDataAdapter thisAdapter = new OracleDataAdapter("delete from customer where customer_id ='" + textBox2.Text + "'", con.thisConnection);
thisAdapter.SelectCommand.ExecuteNonQuery();
con.thisConnection.Close();
MessageBox.Show("Customer deleted successfully", "DELETE", MessageBoxButtons.OK, MessageBoxIcon.Information);
textBox2.Clear();
dataGridView1.ClearSelection();
connection con1 = new connection();
OracleDataAdapter mehrab = new OracleDataAdapter("Select * from customer", con1.thisConnection);
DataTable data = new DataTable();
mehrab.Fill(data);
dataGridView1.DataSource = data;
}
private void pNmCombo_SelectedIndexChanged(object sender, EventArgs e)
{
connection CN = new connection();
CN.thisConnection.Open();
OracleCommand thisCommand = CN.thisConnection.CreateCommand();
thisCommand.CommandText = "SELECT * FROM product where product_name= '" + pNmCombo.Text + "'";
OracleDataReader thisReader2 = thisCommand.ExecuteReader();
while (thisReader2.Read())
{
pIDComb.Text = thisReader2["product_id"].ToString();
unitP.Text = thisReader2["unit_price"].ToString();
vat.Text = thisReader2["vat"].ToString();
avlQty.Text = thisReader2["quantity"].ToString();
}
CN.thisConnection.Close();
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
connection CN = new connection();
CN.thisConnection.Open();
if (sBy.Text == "CUSTOMER_ID")
{
OracleDataAdapter thisAdapter = new OracleDataAdapter("Select customer_id, customer_name,mail_id, mobile_no,address from customer where CUSTOMER_ID like '" + textBox1.Text + "%'", CN.thisConnection);
DataTable data = new DataTable();
thisAdapter.Fill(data);
dataGridView1.DataSource = data;
}
else if (sBy.Text == "CUSTOMER_NAME")
{
OracleDataAdapter thisAdapter = new OracleDataAdapter("Select customer_id, customer_name, mail_id, mobile_no,address from customer where CUSTOMER_NAME like '" + textBox1.Text + "%'", CN.thisConnection);
DataTable data = new DataTable();
thisAdapter.Fill(data);
dataGridView1.DataSource = data;
}
else if (sBy.Text == "MOBILE_NO")
{
OracleDataAdapter thisAdapter = new OracleDataAdapter("Select customer_id, customer_name, mail_id, mobile_no,address from customer where mobile_no like '" + textBox1.Text + "%'", CN.thisConnection);
DataTable data = new DataTable();
thisAdapter.Fill(data);
dataGridView1.DataSource = data;
}
CN.thisConnection.Close();
}
private void Sell_Load(object sender, EventArgs e)
{
connection CN = new connection();
CN.thisConnection.Open();
OracleCommand thisCommand = new OracleCommand("select customer_id from customer");
thisCommand.Connection = CN.thisConnection;
thisCommand.CommandType = CommandType.Text;
OracleDataReader thisReader = thisCommand.ExecuteReader();
while (thisReader.Read())
{
custIDCombo.Items.Add(thisReader["customer_id"].ToString());
}
OracleCommand thisCommand1 = new OracleCommand("select customer_name from customer");
thisCommand1.Connection = CN.thisConnection;
thisCommand1.CommandType = CommandType.Text;
OracleDataReader thisReader1 = thisCommand1.ExecuteReader();
while (thisReader1.Read())
{
custNmCombo.Items.Add(thisReader1["customer_name"].ToString());
}
OracleCommand thisCommand2 = new OracleCommand("select product_id from product");
thisCommand2.Connection = CN.thisConnection;
thisCommand2.CommandType = CommandType.Text;
OracleDataReader thisReader2 = thisCommand2.ExecuteReader();
while (thisReader2.Read())
{
pIDComb.Items.Add(thisReader2["product_id"].ToString());
}
OracleCommand thisCommand3 = new OracleCommand("select product_name from product");
thisCommand3.Connection = CN.thisConnection;
thisCommand3.CommandType = CommandType.Text;
OracleDataReader thisReader3 = thisCommand3.ExecuteReader();
while (thisReader3.Read())
{
pNmCombo.Items.Add(thisReader3["product_name"].ToString());
}
CN.thisConnection.Close();
}
private void view_Click(object sender, EventArgs e)
{
connection CN = new connection();
CN.thisConnection.Open();
OracleCommand thisCommand = CN.thisConnection.CreateCommand();
thisCommand.CommandText = "SELECT * FROM invoice where invoice_no= '" + invCombo.Text + "'";
OracleDataReader thisReader = thisCommand.ExecuteReader();
if (!thisReader.Read())
{
MessageBox.Show("Invoive number not found..!", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
CN.thisConnection.Close();
return;
}
try
{
connection con = new connection();
con.thisConnection.Open();
OracleCommand thisCommand1 = con.thisConnection.CreateCommand();
OracleCommand thisCommand2 = con.thisConnection.CreateCommand();
thisCommand2.CommandText = "SELECT distinct sales_date from invoice where invoice_no= '" + invCombo.Text + "'";
thisCommand1.CommandText = "SELECT distinct customer_id from invoice where invoice_no= '" + invCombo.Text + "'";
OracleDataReader thisReader1 = thisCommand1.ExecuteReader();
OracleDataReader thisReader2 = thisCommand2.ExecuteReader();
if (thisReader1.Read())
{
custID.Text = thisReader1["customer_id"].ToString();
}
if (thisReader2.Read())
{
invDate.Text = thisReader2["sales_date"].ToString();
}
con.thisConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
fill_listbox();
connection DB = new connection();
DB.thisConnection.Open();
OracleCommand thisCommand3 = DB.thisConnection.CreateCommand();
thisCommand3.CommandText = "SELECT bill_amount,discount,sub_total from balance where invoice_no= '" + invCombo.Text + "'";
OracleDataReader thisReader3 = thisCommand3.ExecuteReader();
if (thisReader3.Read())
{
billAmnt.Text = thisReader3["bill_amount"].ToString();
discount.Text = thisReader3["discount"].ToString();
totalAmnt.Text = thisReader3["sub_total"].ToString();
}
DB.thisConnection.Close();
////////////////////////datagridview/////////////////////////////
connection DB2 = new connection();
DB2.thisConnection.Open();
OracleDataAdapter thisAdapter5 = new OracleDataAdapter("select product_name, product_id, unit_price, quantity, vat, amount from invoice where invoice_no ='" + invCombo.Text + "'", DB2.thisConnection);
DataTable data = new DataTable();
thisAdapter5.Fill(data);
dataGridView1.DataSource = data;
DB2.thisConnection.Close();
}
private void button4_Click(object sender, EventArgs e)
{
///////////////////////////////////////////Product stock UpDATE after selling//////////////////////////////////////////////////////////////
if (totalAm.Text != "")
{
int saleItem = 0;
string pid;
connection sv = new connection();
sv.thisConnection.Open();
OracleCommand thisCommand = sv.thisConnection.CreateCommand();
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
saleItem = Convert.ToInt32(dataGridView1.Rows[i].Cells[2].Value);
pid = Convert.ToString(dataGridView1.Rows[i].Cells[0].Value);
thisCommand.Connection = sv.thisConnection;
thisCommand.CommandType = CommandType.Text;
thisCommand.CommandText = "update product set quantity=quantity-" + saleItem + "where product_id= '" + pid + "'";
try
{
thisCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
sv.thisConnection.Close();
}
//////////////////////////end/////////////////////////////////////////////////////////
string inv, rif, meh;
rif = DateTime.Now.ToString("yy-MM-dd-HHmmss");
meh = "IN";
inv = String.Concat(rif, meh);
invNO.Text = inv;
///////////////////////sales entry//////////////////////
connection db = new connection();
db.thisConnection.Open();
OracleDataAdapter thisAdapter = new OracleDataAdapter("SELECT * FROM sell", db.thisConnection);
OracleCommandBuilder thisBuilder = new OracleCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet, "sell");
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
DataRow thisRow = thisDataSet.Tables["sell"].NewRow();
try
{;
thisRow["invoice_no"] = inv;
thisRow["CUSTOMER_ID"] = custIDCombo.Text;
thisRow["amount"] = Convert.ToDouble(dataGridView1.Rows[i].Cells[5].Value);
thisRow["product_id"] = Convert.ToString(dataGridView1.Rows[i].Cells[0].Value);
thisRow["product_name"] = Convert.ToString(dataGridView1.Rows[i].Cells[1].Value);
thisRow["quantity"] = Convert.ToDecimal(dataGridView1.Rows[i].Cells[2].Value);
thisRow["sales_date"] = invDTPicker.Value.Date.ToString();
thisDataSet.Tables["sell"].Rows.Add(thisRow); }
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
thisAdapter.Update(thisDataSet, "sell");
db.thisConnection.Close();
////////////////////////////////invoice_entry/////////////////////////////
connection db1 = new connection();
db1.thisConnection.Open();
OracleDataAdapter thisAdapter1 = new OracleDataAdapter("SELECT * FROM invoice", db1.thisConnection);
OracleCommandBuilder thisBuilder1 = new OracleCommandBuilder(thisAdapter1);
DataSet thisDataSet1 = new DataSet();
thisAdapter1.Fill(thisDataSet1, "invoice");
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
DataRow thisRow1 = thisDataSet1.Tables["invoice"].NewRow();
try
{
thisRow1["invoice_no"] = inv;
thisRow1["CUSTOMER_ID"] = custIDCombo.Text;
thisRow1["Customer_name"] = custNmCombo.Text;
thisRow1["amount"] = Convert.ToDouble(dataGridView1.Rows[i].Cells[5].Value);
thisRow1["product_id"] = Convert.ToString(dataGridView1.Rows[i].Cells[0].Value);
thisRow1["product_name"] = Convert.ToString(dataGridView1.Rows[i].Cells[1].Value);
thisRow1["quantity"] = Convert.ToDecimal(dataGridView1.Rows[i].Cells[2].Value);
thisRow1["unit_price"] = Convert.ToDouble(dataGridView1.Rows[i].Cells[3].Value);
thisRow1["vat"] = Convert.ToDecimal(dataGridView1.Rows[i].Cells[4].Value);
thisRow1["sales_date"] = invDTPicker.Value.Date.ToString();
thisDataSet1.Tables["invoice"].Rows.Add(thisRow1);
thisAdapter1.Update(thisDataSet1, "invoice");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
db1.thisConnection.Close();
/////////////////////balance table entry////////////////////////
connection db2 = new connection();
db2.thisConnection.Open();
if (discount.Text == "")
{
int val = 0;
discount.Text = val.ToString();
}
OracleDataAdapter thisAdapter2 = new OracleDataAdapter("SELECT * FROM balance", db2.thisConnection);
OracleCommandBuilder thisBuilder2 = new OracleCommandBuilder(thisAdapter2);
DataSet thisDataSet2 = new DataSet();
thisAdapter2.Fill(thisDataSet2, "balance");
DataRow thisRow2 = thisDataSet2.Tables["balance"].NewRow();
try
{
double bill = Convert.ToDouble(billAmnt.Text);
// MessageBox.Show(bill.ToString());
thisRow2["INVOICE_NO"] = inv;
thisRow2["BILL_AMOUNT"] = bill;
thisRow2["discount"] = Convert.ToDouble(discount.Text);
thisRow2["sub_total"] = Convert.ToDouble(totalAm.Text);
thisDataSet2.Tables["balance"].Rows.Add(thisRow2);
thisAdapter2.Update(thisDataSet2, "balance");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
db2.thisConnection.Close();
MessageBox.Show("Items are Sold\nThank You", "$SOLD$", MessageBoxButtons.OK, MessageBoxIcon.Information);
}