public bool addReport(FinancialReportEditViewModel financialReportEditViewModel)
{
string conn = ConfigurationManager.ConnectionStrings["MysqlConnection"].ConnectionString;
MySqlConnection mySqlConnection = new MySqlConnection(conn);
string sqlquery = @"
INSERT INTO financial_reports(ticker, reporting_period, statement_type, release_date, P_E, P_B,
EV_EBITDA, NetDepth_EBITDA, ROA, fk_Company)
VALUES(?ticker, ?reporting_period, ?statement_type, ?release_date, ?P_E, ?P_B,
?EV_EBITDA, ?NetDepth_EBITDA, ?ROA, ?fk_Company)";
MySqlCommand mySqlCommand = new MySqlCommand(sqlquery, mySqlConnection);
mySqlCommand.Parameters.Add("?ticker", MySqlDbType.VarChar).Value = financialReportEditViewModel.ticker;
mySqlCommand.Parameters.Add("?reporting_period", MySqlDbType.VarChar).Value = financialReportEditViewModel.reporting_period;
mySqlCommand.Parameters.Add("?statement_type", MySqlDbType.VarChar).Value = financialReportEditViewModel.statement_type;
mySqlCommand.Parameters.Add("?release_date", MySqlDbType.DateTime).Value = financialReportEditViewModel.release_date;
mySqlCommand.Parameters.Add("?P_E", MySqlDbType.Decimal).Value = financialReportEditViewModel.P_E;
mySqlCommand.Parameters.Add("?P_B", MySqlDbType.Decimal).Value = financialReportEditViewModel.P_B;
mySqlCommand.Parameters.Add("?EV_EBITDA", MySqlDbType.Decimal).Value = financialReportEditViewModel.EV_EBITDA;
mySqlCommand.Parameters.Add("?NetDepth_EBITDA", MySqlDbType.Decimal).Value = financialReportEditViewModel.NetDepth_EBITDA;
mySqlCommand.Parameters.Add("?ROA", MySqlDbType.Decimal).Value = financialReportEditViewModel.ROA;
mySqlCommand.Parameters.Add("?fk_Company", MySqlDbType.Int32).Value = financialReportEditViewModel.fk_Company;
mySqlConnection.Open();
mySqlCommand.ExecuteNonQuery();
mySqlConnection.Close();
return(true);
}
public FinancialReportEditViewModel getReport(string ticker)
{
FinancialReportEditViewModel financialReportEditViewModel = new FinancialReportEditViewModel();
string conn = ConfigurationManager.ConnectionStrings["MysqlConnection"].ConnectionString;
MySqlConnection mySqlConnection = new MySqlConnection(conn);
string sqlquery = @"select r.* from " + Globals.dbPrefix + @"financial_reports r WHERE r.ticker='" + ticker + "'";
MySqlCommand mySqlCommand = new MySqlCommand(sqlquery, mySqlConnection);
mySqlConnection.Open();
MySqlDataAdapter mda = new MySqlDataAdapter(mySqlCommand);
DataTable dt = new DataTable();
mda.Fill(dt);
mySqlConnection.Close();
foreach (DataRow item in dt.Rows)
{
financialReportEditViewModel.ticker = Convert.ToString(item["ticker"]);
financialReportEditViewModel.reporting_period = Convert.ToString(item["reporting_period"]);
financialReportEditViewModel.statement_type = Convert.ToString(item["statement_type"]);
financialReportEditViewModel.release_date = Convert.ToDateTime(item["release_date"]);
financialReportEditViewModel.P_E = Convert.ToDecimal(item["P_E"]);
financialReportEditViewModel.P_B = Convert.ToDecimal(item["P_B"]);
financialReportEditViewModel.EV_EBITDA = Convert.ToDecimal(item["EV_EBITDA"]);
financialReportEditViewModel.NetDepth_EBITDA = Convert.ToDecimal(item["NetDepth_EBITDA"]);
financialReportEditViewModel.ROA = Convert.ToDecimal(item["ROA"]);
financialReportEditViewModel.fk_Company = Convert.ToInt32(item["fk_Company"]);
}
return(financialReportEditViewModel);
}
public bool updateReport(FinancialReportEditViewModel financialReportEditViewModel)
{
string conn = ConfigurationManager.ConnectionStrings["MysqlConnection"].ConnectionString;
MySqlConnection mySqlConnection = new MySqlConnection(conn);
string sqlquery = @"UPDATE financial_reports r SET
r.reporting_period = ?reporting_period,
r.statement_type = ?statement_type,
r.release_date = ?release_date,
r.P_E = ?P_E,
r.P_B = ?P_B,
r.EV_EBITDA = ?EV_EBITDA,
r.NetDepth_EBITDA = ?NetDepth_EBITDA,
r.ROA = ?ROA,
r.fk_Company = ?fk_Company
WHERE r.ticker=?ticker";
MySqlCommand mySqlCommand = new MySqlCommand(sqlquery, mySqlConnection);
mySqlCommand.Parameters.Add("?ticker", MySqlDbType.VarChar).Value = financialReportEditViewModel.ticker;
mySqlCommand.Parameters.Add("?reporting_period", MySqlDbType.VarChar).Value = financialReportEditViewModel.reporting_period;
mySqlCommand.Parameters.Add("?statement_type", MySqlDbType.VarChar).Value = financialReportEditViewModel.statement_type;
mySqlCommand.Parameters.Add("?release_date", MySqlDbType.DateTime).Value = financialReportEditViewModel.release_date.Date; // .date tik cia
mySqlCommand.Parameters.Add("?P_E", MySqlDbType.Decimal).Value = financialReportEditViewModel.P_E;
mySqlCommand.Parameters.Add("?P_B", MySqlDbType.Decimal).Value = financialReportEditViewModel.P_B;
mySqlCommand.Parameters.Add("?EV_EBITDA", MySqlDbType.Decimal).Value = financialReportEditViewModel.EV_EBITDA;
mySqlCommand.Parameters.Add("?NetDepth_EBITDA", MySqlDbType.Decimal).Value = financialReportEditViewModel.NetDepth_EBITDA;
mySqlCommand.Parameters.Add("?ROA", MySqlDbType.Decimal).Value = financialReportEditViewModel.ROA;
mySqlCommand.Parameters.Add("?fk_Company", MySqlDbType.Int32).Value = financialReportEditViewModel.fk_Company;
mySqlConnection.Open();
mySqlCommand.ExecuteNonQuery();
mySqlConnection.Close();
return(true);
}
// GET: FinancialReport/Create
public ActionResult Create()
{
FinancialReportEditViewModel financialReportEditViewModel = new FinancialReportEditViewModel();
PopulateSelections(financialReportEditViewModel);
return(View(financialReportEditViewModel));
}
public void PopulateSelections(FinancialReportEditViewModel financialReportEditViewModel)
{
var companies = companiesRepository.getCompanies();
List <SelectListItem> selectListCompanies = new List <SelectListItem>();
foreach (var item in companies)
{
selectListCompanies.Add(new SelectListItem()
{
Value = Convert.ToString(item.code), Text = item.name
});
}
financialReportEditViewModel.CompaniesList = selectListCompanies;
}
public ActionResult Edit(string ticker, FinancialReportEditViewModel collection)
{
try
{
if (ModelState.IsValid)
{
financialReportsRepository.updateReport(collection);
}
return(RedirectToAction("Index"));
}
catch
{
PopulateSelections(collection);
return(View(collection));
}
}
