private void btnSaveUpdate_Click(object sender, EventArgs e) { try { string query = "update car_expenses set Car_ID=" + comCarNumber.SelectedValue + " ,Cost=" + txtCost.Text + " , Note='" + txtNote.Text + "' ,Date='" + dateTimePicker1.Value.Date.ToString("yyyy-MM-dd") + "' , Expenses_Type='" + comType.Text + "', Expenses_Type_ID=" + comType.SelectedValue + " where ID=" + row[0].ToString(); dbconnection.Open(); MySqlCommand comand = new MySqlCommand(query, dbconnection); comand.ExecuteNonQuery(); double totalCost = 0; try { totalCost = Convert.ToDouble(txtCost.Text); } catch { MessageBox.Show("insert correct value"); } query = "select TotalSafay from Total_Revenue_Of_CarIncom where Car_ID=" + comCarNumber.SelectedValue; MySqlCommand com = new MySqlCommand(query, dbconnection); double totalSafay = Convert.ToDouble(com.ExecuteScalar()); query = "update Total_Revenue_Of_CarIncom set TotalSafay=" + (totalSafay - totalCost) + " where Car_ID=" + comCarNumber.SelectedValue; com = new MySqlCommand(query, dbconnection); com.ExecuteNonQuery(); MessageBox.Show("updated"); CarExpenses.displayData(); XtraTabPage xtraTabPage = getTabPage("تعديل مصروف"); xtraTabPage.ImageOptions.Image = null; } catch (Exception ex) { MessageBox.Show(ex.Message); } dbconnection.Close(); }
private void btnAddCarExpenses_Click(object sender, EventArgs e) { try { dbconnection.Open(); if (txtCost.Text != "" && comCarNumber.Text != "" && comType.Text != "") { string query = "insert into car_expenses (Car_ID,Cost,Note,Date,Expenses_Type,Expenses_Type_ID)values(@Car_ID,@Cost,@Note,@Date,@Expenses_Type,@Expenses_Type_ID)"; MySqlCommand com = new MySqlCommand(query, dbconnection); com.Parameters.Add("@Date", MySqlDbType.Date); com.Parameters["@Date"].Value = dateTimePicker1.Value.Date; com.Parameters.Add("@Car_ID", MySqlDbType.Int16); com.Parameters["@Car_ID"].Value = comCarNumber.SelectedValue; if (txtNote.Text != "") { com.Parameters.Add("@Note", MySqlDbType.VarChar); com.Parameters["@Note"].Value = txtNote.Text; } else { com.Parameters.Add("@Note", MySqlDbType.VarChar); com.Parameters["@Note"].Value = txtNote.Text; } com.Parameters.Add("@Expenses_Type", MySqlDbType.VarChar); com.Parameters["@Expenses_Type"].Value = comType.Text; com.Parameters.Add("@Expenses_Type_ID", MySqlDbType.Int16); com.Parameters["@Expenses_Type_ID"].Value = comType.SelectedValue; double cost; if (double.TryParse(txtCost.Text, out cost)) { com.Parameters.Add("@Cost", MySqlDbType.VarChar); com.Parameters["@Cost"].Value = txtCost.Text; } else { MessageBox.Show("insert correct value to cost field"); dbconnection.Close(); return; } com.ExecuteNonQuery(); DateTime date = dateTimePicker1.Value.Date; string d = date.ToString("yyyy-MM-dd"); query = "select sum(Cost) from car_expenses where Car_ID=" + comCarNumber.SelectedValue + " and Date ='" + d + "'"; com = new MySqlCommand(query, dbconnection); if (com.ExecuteScalar() != null) { double totalCost = Convert.ToDouble(txtCost.Text); query = "select TotalSafay from Total_Revenue_Of_CarIncom where Car_ID=" + comCarNumber.SelectedValue + ""; com = new MySqlCommand(query, dbconnection); double totalSafay = 0; if (com.ExecuteScalar() != null) { totalSafay = Convert.ToDouble(com.ExecuteScalar()); query = "update Total_Revenue_Of_CarIncom set TotalSafay=" + (totalSafay - totalCost) + " where Car_ID=" + comCarNumber.SelectedValue + ""; com = new MySqlCommand(query, dbconnection); com.ExecuteNonQuery(); } else { query = "insert into Total_Revenue_Of_CarIncom (TotalSafay,TotalGate, Car_ID) values(@TotalSafay,@TotalGate,@Car_ID)"; com = new MySqlCommand(query, dbconnection); com.Parameters.Add("@TotalSafay", MySqlDbType.Double); com.Parameters["@TotalSafay"].Value = -totalCost; com.Parameters.Add("@TotalGate", MySqlDbType.Double); com.Parameters["@TotalGate"].Value = 0; com.Parameters.Add("@Car_ID", MySqlDbType.Int16); com.Parameters["@Car_ID"].Value = comCarNumber.SelectedValue; com.ExecuteNonQuery(); } } else { MessageBox.Show("error"); dbconnection.Close(); return; } //update bank query = "select Bank_Stock from bank where Bank_ID=13"; com = new MySqlCommand(query, dbconnection); double bankMoney = Convert.ToDouble(com.ExecuteScalar()); bankMoney -= Convert.ToDouble(txtCost.Text); query = "update bank set Bank_Stock=" + bankMoney + " where Bank_ID=13"; com = new MySqlCommand(query, dbconnection); com.ExecuteNonQuery(); MessageBox.Show("Done"); carExpenses.displayData(); clear(); } else { MessageBox.Show("fill required fields"); } } catch (Exception ex) { MessageBox.Show(ex.Message); } dbconnection.Close(); }