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