public PslgAtaskaitaViewModel getBedraSumaUzsakytuPaslaugu(DateTime?nuo, DateTime?iki)
{
PslgAtaskaitaViewModel viso = new PslgAtaskaitaViewModel();
string conn = ConfigurationManager.ConnectionStrings["MysqlConnection"].ConnectionString;
MySqlConnection mySqlConnection = new MySqlConnection(conn);
string sqlquery = @"select sum(b.kiekis) as kiekis, sum(b.kiekis*b.kaina) as suma from " + Globals.dbPrefix + "paslaugos a, " + Globals.dbPrefix + "uzsakytos_paslaugos b, " + Globals.dbPrefix + @"sutartys c
where a.id=b.fk_paslauga and b.fk_sutartis=c.nr
and c.sutarties_data>=IFNULL(?nuo, c.sutarties_data) and c.sutarties_data <= IFNULL(?iki, c.sutarties_data)";
MySqlCommand mySqlCommand = new MySqlCommand(sqlquery, mySqlConnection);
mySqlCommand.Parameters.Add("?nuo", MySqlDbType.DateTime).Value = nuo;
mySqlCommand.Parameters.Add("?iki", MySqlDbType.DateTime).Value = iki;
mySqlConnection.Open();
MySqlDataAdapter mda = new MySqlDataAdapter(mySqlCommand);
DataTable dt = new DataTable();
mda.Fill(dt);
mySqlConnection.Close();
foreach (DataRow item in dt.Rows)
{
viso.visoUzsakyta = Convert.ToInt32(item["kiekis"] == System.DBNull.Value ? 0 : item["kiekis"]);
viso.bendraSuma = Convert.ToDecimal(item["suma"] == System.DBNull.Value ? 0 : item["suma"]);
}
return(viso);
}
public PslgAtaskaitaViewModel getBedraSumaUzsakytuPaslaugu(DateTime?nuo, DateTime?iki)
{
PslgAtaskaitaViewModel viso = new PslgAtaskaitaViewModel();
string conn = ConfigurationManager.ConnectionStrings["MysqlConnection"].ConnectionString;
MySqlConnection mySqlConnection = new MySqlConnection(conn);
string sqlquery = @"select sum(b.dshonoraras) as suma from " + Globals.dbPrefix + "kinostudija a, " + Globals.dbPrefix + "darbosutartis b, " + Globals.dbPrefix + @"aktorystessutartis c
where a.kinostudijosid=b.fk_KINOSTUDIJAkinostudijosid
and b.dspasirasymodata>=IFNULL(?nuo, b.dspasirasymodata) and b.dspasirasymodata <= IFNULL(?iki, b.dspasirasymodata)";
MySqlCommand mySqlCommand = new MySqlCommand(sqlquery, mySqlConnection);
mySqlCommand.Parameters.Add("?nuo", MySqlDbType.DateTime).Value = nuo;
mySqlCommand.Parameters.Add("?iki", MySqlDbType.DateTime).Value = iki;
mySqlConnection.Open();
MySqlDataAdapter mda = new MySqlDataAdapter(mySqlCommand);
DataTable dt = new DataTable();
mda.Fill(dt);
mySqlConnection.Close();
foreach (DataRow item in dt.Rows)
{
viso.bendraSuma = Convert.ToDecimal(item["suma"] == System.DBNull.Value ? 0 : item["suma"]);
}
return(viso);
}
// GET: Ataskaita
// Gali būti nenurodytos datos dėl to prie kintamuju ?
public ActionResult Index(DateTime?nuo, DateTime?iki)
{
// išrenka paslaugas
PslgAtaskaitaViewModel pslgAtaskaita = ataskaituRepository.getBedraSumaUzsakytuPaslaugu(nuo, iki);
pslgAtaskaita.paslaugos = ataskaituRepository.getUzsakytosPaslaugos(nuo, iki);
//išsaugomos numatytos reiksmes datos intervalui
pslgAtaskaita.nuo = nuo == null? null : nuo;
pslgAtaskaita.iki = iki == null? null : iki;
return(View(pslgAtaskaita));
}