public static void EditExpense(ExpenseInfo expenseToEdit)
{
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "UPDATE excredit SET date=@date,type=@type,description=@description,amount=@amount WHERE id=@id";
msqlCommand.Parameters.AddWithValue("@date", expenseToEdit.date);
msqlCommand.Parameters.AddWithValue("@type", expenseToEdit.type);
msqlCommand.Parameters.AddWithValue("@description", expenseToEdit.description);
msqlCommand.Parameters.AddWithValue("@amount", expenseToEdit.amount);
msqlCommand.Parameters.AddWithValue("@id", expenseToEdit.id);
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
private void submitBtn_Click(object sender, RoutedEventArgs e)
{
ExpenseManagerData.ExpenseInfo newExcredit = new ExpenseManagerData.ExpenseInfo();
newExcredit.id = GenerateId();
newExcredit.type = (ExpenseType)Enum.Parse(typeof(ExpenseType), typeCB.Text, false); ;
newExcredit.date = dateDP.SelectedDate.Value;
newExcredit.description = descritptionTB.Text;
newExcredit.amount = Convert.ToDouble(amountTB.Text);
//ExpenseManagerGUI.MainWindow objMainWindow = new ExpenseManagerGUI.MainWindow();
//objMainWindow.showIncomeExpenseDetails();
if (isEdit == false)
{
newExcredit.id = GenerateId();
ExpenseManagerDb.DbInteraction.DoRegisterNewExcredit(newExcredit);
}
else
{
newExcredit.id = expenseId;
ExpenseManagerDb.DbInteraction.EditExpense(newExcredit);
}
this.Close();
}
private void getDatewiseReport()
{
ExpenseInfo expenseInfoObj = new ExpenseInfo();
expenseInfoObj.date = startingdateDP.SelectedDate.Value;
expenseInfoObj.endDate = enddatepickerDP.SelectedDate.Value;
List<ExpenseInfo> excredits = ExpenseManagerDb.DbInteraction.GetDatewiseAllExcreditList(expenseInfoObj);
incomeExpenseCollection.Clear();
foreach (ExpenseInfo excredit in excredits)
{
incomeExpenseCollection.Add(excredit);
}
}
public static int DoRegisterNewExcredit(ExpenseInfo excreditDetails)
{
return DoRegisterNewExcreditInDb(excreditDetails);
}
private static List<ExpenseInfo> QueryAllExcreditList()
{
List<ExpenseInfo> ExcreditList = new List<ExpenseInfo>();
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "Select * From excredit;";
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
while (msqlReader.Read())
{
ExpenseInfo Excredit = new ExpenseInfo();
Excredit.id = msqlReader.GetString("id");
Excredit.date = msqlReader.GetDateTime("date");
Excredit.type = (ExpenseType)Enum.Parse(typeof(ExpenseType), msqlReader.GetString("type"), false);
Excredit.description = msqlReader.GetString("description");
Excredit.amount = msqlReader.GetDouble("amount");
ExcreditList.Add(Excredit);
}
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
return ExcreditList;
}
private static int DoRegisterNewExcreditInDb(ExpenseInfo excreditDetails)
{
int returnVal = 0;
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{
//define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
//define the connection used by the command object
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "INSERT INTO excredit(id,date,type,description,amount) "
+ "VALUES(@id,@date,@type,@description,@amount)";
msqlCommand.Parameters.AddWithValue("@id", excreditDetails.id);
msqlCommand.Parameters.AddWithValue("@date", excreditDetails.date);
msqlCommand.Parameters.AddWithValue("@type", excreditDetails.type);
msqlCommand.Parameters.AddWithValue("@description", excreditDetails.description);
msqlCommand.Parameters.AddWithValue("@amount", excreditDetails.amount);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
public static List<ExpenseInfo> GetEachDayExcreditList(ExpenseInfo expenseInfoObj)
{
List<ExpenseInfo> ExcreditList = new List<ExpenseInfo>();
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlConnection.Open();
//DateTime endDatePicker = expenseInfoObj.endDate;
//DateTime startDatePicker = expenseInfoObj.date;
////TimeSpan diff = (TimeSpan)(endDatePicker - startDatePicker);
//msqlCommand.CommandText = "Select * From excredit where date = @input;";
////"SELECT purchaselist.datePurchase, purchaselist.invoiceNo,purchaselist.vendorId,purchasebilling.quantity,purchasebilling.rate, purchasebilling.amount, purchasebilling.vat,vendors.vendor_name,vendors.vat_no FROM purchaselist,purchasebilling,vendors WHERE purchaselist.invoiceNo=purchasebilling.invoiceNo AND date(purchaselist.datePurchase) >= DATE_SUB( @enddate, INTERVAL @diff DAY) AND purchaselist.vendorId = vendors.vendor_id ;";
//msqlCommand.Parameters.AddWithValue("@input", endDatePicker);
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
while (msqlReader.Read())
{
ExpenseInfo Excredit = new ExpenseInfo();
Excredit.id = msqlReader.GetString("id");
Excredit.date = msqlReader.GetDateTime("date");
Excredit.type = (ExpenseType)Enum.Parse(typeof(ExpenseType), msqlReader.GetString("type"), false);
Excredit.description = msqlReader.GetString("description");
Excredit.amount = msqlReader.GetDouble("amount");
ExcreditList.Add(Excredit);
}
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
return ExcreditList;
}
