private List <UserTagReport> ReadTotalReportedUserTagsFromDatabase()
{
var ruts = new List <UserTagReport>();
query = "SELECT [usergroup] ,[uid] ,[user_section] ,[real_section] ,[user_tag] ,[real_tag] ,[user_trust] ,[day] ,[weekday] ,[hour] ,[randcol] ,[randusercol] FROM [DissertationSimDB].[dbo].[" + Constants.UserUpdatesTable + "] where usergroup = " + experimentGroup + " order by usergroup,day,hour,user_section";
dbResult = db.ExecuteQuery(query);
for (int i = 0; i < dbResult["usergroup"].Count; i++)
{
var rut = new UserTagReport();
rut.UserGroup = experimentGroup;
rut.Day = int.Parse(dbResult["day"][i]);
rut.Weekday = int.Parse(dbResult["weekday"][i]);
rut.Hour = int.Parse(dbResult["hour"][i]);
rut.Section = int.Parse(dbResult["user_section"][i]);
rut.RealSection = int.Parse(dbResult["real_section"][i]);
rut.Tag = int.Parse(dbResult["user_tag"][i]);
rut.RealTag = int.Parse(dbResult["real_tag"][i]);
rut.UserID = int.Parse(dbResult["uid"][i]);
rut.UserTrust = int.Parse(dbResult["user_trust"][i]);
rut.RandomColumnValue = double.Parse(dbResult["randcol"][i]);
rut.RandomUserValue = double.Parse(dbResult["randusercol"][i]);
ruts.Add(rut);
}
return(ruts);
}
private void init()
{
// you must first configure SQL Server, create a database called 'DissertationSimDB' and run the DissertationSimDB.sql
// on it to make the tables and insert all the data
int[] experimentIDsBase = new int[] { 10, 11, 20, 21, 23, 31, 32 };
int n = experimentIDsBase.Length;
SqlServerConnector db = new SqlServerConnector();
string query;
query = "select count(distinct usergroup) as NumberOfGroups from " + Constants.UserUpdatesTable;
var result = db.ExecuteQuery(query);
int nGroups = int.Parse(result["NumberOfGroups"][0]);
//nGroups = 1;
int[] experimentIDs = new int[nGroups * n];
for (int i = 0; i < n; i++)
{
for (int g = 0; g < nGroups; g++)
{
experimentIDs[i + n * g] = 100 * g + experimentIDsBase[i];
}
}
//experimentIDs = new int[] { 10, 11, 20, 21, 23, 31, 32 };
_experimentExecutor = new ExperimentExecutor(experimentIDs);
}
public void TransferDataFromKparkServerToLocalMsSql()
{
var serverDb = new MySqlConnector();
var localDb = new Dissertation.SqlServerConnector();
string query;
localDb.ExecuteNonQuery("truncate table realUserUpdates");
//query = "SELECT * FROM entries WHERE uid not in (1,44)";
query = "SELECT entries.uid,entries.sid,entries.datetime,sectionnumber,occupancy FROM entries JOIN sections2 on entries.sid=sections2.sid JOIN parkings on parkings.pid=sections2.pid WHERE uid not in (1,44) and datetime>'2014-9-17'"; // and parkings.pid = 21 and floor=1
var RealDataEntries = serverDb.ExecuteSelect(query);
// get section real tags
query = "SELECT dbo.cardata.id, dbo.cardata.occupancy, dbo.cardata.day, dbo.cardata.weekday, dbo.cardata.hour, dbo.sectionProbabilities.section_id, dbo.sectionProbabilities.hour_id, dbo.sectionProbabilities.prob FROM dbo.cardata INNER JOIN dbo.sectionProbabilities ON dbo.cardata.id = dbo.sectionProbabilities.hour_id";
var result2 = localDb.ExecuteQuery(query);
Dictionary <string, int> HourSectionTagHash = HashHourSections(result2);
Random random = new Random();
string uid, sectionNumber, tag, day, hour, weekday, realtag;
DateTime timeOfTag;
TimeSpan span;
localDb.ExecuteNonQuery("truncate table realUserUpdates");
localDb.ExecuteNonQuery("truncate table realExperiments");
for (int i = 0; i < RealDataEntries["uid"].Count; i++)
{
timeOfTag = DateTime.Parse(RealDataEntries["datetime"][i]);
span = timeOfTag - DateTime.Parse("9/15/2014"); // first Monday before the experiment started (day 0 is monday)
span = span.Add(new TimeSpan(2, 0, 0)); // colorado time difference
day = span.Days.ToString();
weekday = determinWeekDay(timeOfTag.DayOfWeek.ToString()).ToString();
hour = span.Hours.ToString();
sectionNumber = RealDataEntries["sectionnumber"][i];
realtag = HourSectionTagHash[day + "_" + weekday + "_" + hour + "_" + sectionNumber].ToString();
query = string.Format("INSERT INTO [dbo].[realUserUpdates] ([usergroup] ,[uid] ,[user_section] ,[real_section] ,[user_tag] ,[real_tag] ,[user_trust] ,[day] ,[weekday] ,[hour] ,[randcol] ,[randusercol], [date_received]) VALUES ({0} ,{1} ,{2} ,{3} ,{4} ,{5} ,{6} ,{7} ,{8} ,{9} ,{10} ,{11}, '{12}')",
0, RealDataEntries["uid"][i], sectionNumber, RealDataEntries["sectionnumber"][i], RealDataEntries["occupancy"][i], realtag, 50, day, weekday, hour, random.NextDouble(), random.NextDouble(), RealDataEntries["datetime"][i]);
localDb.ExecuteNonQuery(query);
}
}
