/** * This function gets the list of statistics_plot from statistics identifier * * int statistics_id: statistics identifier * * return: list Statistics_Plot * **/ public List<Statistics_Plot> getStatisticsPlotsFromStatistics(int statistics_id) { IDbConnection dbConnection; IDbCommand dbCommand; IDataReader reader; int user_id = GlobalVariables.user_id; connectionString = "URI=file:"+Application.dataPath + "/"+GlobalVariables.user_database; dbConnection = new SqliteConnection (connectionString); dbConnection.Open (); List<Statistics_Plot> statistics_plots = new List<Statistics_Plot> (); Statistics_Plot statistics_plot = new Statistics_Plot(); // Select string sql = "SELECT * FROM statistics_plot AS sp "; sql += "WHERE sp.statistics_id ="+statistics_id; //Debug.Log (sql); dbCommand = dbConnection.CreateCommand (); dbCommand.CommandText = sql; reader = dbCommand.ExecuteReader (); while(reader.Read()) { int _id = Int32.Parse(reader.GetString(0)); statistics_plot = getStatisticsPlot(_id); statistics_plots.Add(statistics_plot); //Debug.Log ("activity_id:"+statistics.id); } if (dbCommand != null) { dbCommand.Dispose (); } dbCommand = null; if (reader != null) { reader.Dispose (); } reader = null; if (dbConnection != null) { dbConnection.Close (); } dbConnection = null; return statistics_plots; }
/** * This function creates a new statistics_plot or updates an existing one * * Statistics_Plot statistics_plot: statistics_plot to create or to update * * return: void * **/ public Statistics_Plot saveStatisticsPlot(string database, Statistics_Plot statistics_plot) { IDbConnection dbConnection; IDbCommand dbCommand; IDataReader reader; connectionString = "URI=file:"+Application.dataPath + "/"+database; dbConnection = new SqliteConnection (connectionString); dbConnection.Open (); int id = statistics_plot.id; string sql=""; dbCommand = dbConnection.CreateCommand (); if (id == 0) { // Insert new Statistics_Plot sql= "INSERT INTO statistics_plot (statistics_id,plot_path,active,sent,is_new) VALUES ("+statistics_plot.statistics_id.ToString()+",'"+statistics_plot.plot_path+"',"+statistics_plot.active+",1,1)"; Debug.Log (sql); dbCommand.CommandText = sql; reader = dbCommand.ExecuteReader (); // Select the last temporal index inserted to know sql= "SELECT last_insert_rowid()"; Debug.Log (sql); dbCommand.CommandText = sql; reader = dbCommand.ExecuteReader (); while(reader.Read()) { int _id = Int32.Parse(reader.GetString(0)); statistics_plot.id = _id; } } else { // Update animation sql= "UPDATE animation_player SET statistics_id="+statistics_plot.statistics_id+",plot_path='"+statistics_plot.plot_path+"',active="+statistics_plot.active.ToString()+",sent=1,is_new=0"+ " WHERE id="+id.ToString(); Debug.Log (sql); dbCommand.CommandText = sql; reader = dbCommand.ExecuteReader (); } if (dbCommand != null) { dbCommand.Dispose (); } dbCommand = null; if (reader != null) { reader.Dispose (); } reader = null; if (dbConnection != null) { dbConnection.Close (); } dbConnection = null; return statistics_plot; }
/** * This function gets the statistics_plot from its identifier * * int statistics_plot_id: statistics_plot identifier * * return: Statistics_Plot * **/ public Statistics_Plot getStatisticsPlot(int statistics_plot_id) { IDbConnection dbConnection; IDbCommand dbCommand; IDataReader reader; int user_id = GlobalVariables.user_id; connectionString = "URI=file:"+Application.dataPath + "/"+GlobalVariables.user_database; dbConnection = new SqliteConnection (connectionString); dbConnection.Open (); Statistics_Plot statistics_plot = new Statistics_Plot(); // Select string sql = "SELECT * FROM statistics_plot AS sp "; sql += "WHERE sp.id ="+statistics_plot_id; //Debug.Log (sql); dbCommand = dbConnection.CreateCommand (); dbCommand.CommandText = sql; reader = dbCommand.ExecuteReader (); while(reader.Read()) { int _id = Int32.Parse(reader.GetString(0)); int _statistics_id = Int32.Parse(reader.GetString(1)); string _plot_path = reader.GetString(2); int _active = Int32.Parse(reader.GetString(3)); int _sent = Int32.Parse(reader.GetString(4)); int _is_new = Int32.Parse(reader.GetString(5)); statistics_plot = new Statistics_Plot(_id,_statistics_id,_plot_path,_active,_sent,_is_new); //Debug.Log ("activity_id:"+statistics.id); } if (dbCommand != null) { dbCommand.Dispose (); } dbCommand = null; if (reader != null) { reader.Dispose (); } reader = null; if (dbConnection != null) { dbConnection.Close (); } dbConnection = null; return statistics_plot; }