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();
}
