public List <DomainModel.RecommenderAction> GetWeeklyActivities() { List <DomainModel.RecommenderAction> actions = new List <DomainModel.RecommenderAction>(); NpgsqlCommand cmd = new NpgsqlCommand(); cmd.Connection = conn; cmd.CommandText = "select userid, productid from viewedproducts where time>@t union select userid, productid from viewedproducts where " + "time>=@tp and time<=@tk"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new NpgsqlParameter("@t", DateTime.Now.AddDays(-21))); if (DateTime.Now.Month >= 3 && DateTime.Now.Month <= 5) { cmd.Parameters.Add(new NpgsqlParameter("@tp", new DateTime(DateTime.Now.Year - 1, 3, 1))); cmd.Parameters.Add(new NpgsqlParameter("@tk", new DateTime(DateTime.Now.Year - 1, 5, 31))); } else if (DateTime.Now.Month >= 6 && DateTime.Now.Month <= 8) { cmd.Parameters.Add(new NpgsqlParameter("@tp", new DateTime(DateTime.Now.Year - 1, 6, 1))); cmd.Parameters.Add(new NpgsqlParameter("@tk", new DateTime(DateTime.Now.Year - 1, 8, 31))); } else if (DateTime.Now.Month >= 9 && DateTime.Now.Month <= 11) { cmd.Parameters.Add(new NpgsqlParameter("@tp", new DateTime(DateTime.Now.Year - 1, 9, 1))); cmd.Parameters.Add(new NpgsqlParameter("@tk", new DateTime(DateTime.Now.Year - 1, 11, 30))); } else { cmd.Parameters.Add(new NpgsqlParameter("@tp", new DateTime(DateTime.Now.Year - 2, 12, 1))); cmd.Parameters.Add(new NpgsqlParameter("@tk", new DateTime(DateTime.Now.Year - 1, 2, 28))); } da = new NpgsqlDataAdapter(cmd); dt = new DataTable(); da.Fill(dt); foreach (DataRow dr in dt.Rows) { DomainModel.RecommenderAction action = new DomainModel.RecommenderAction(); action.Action = "View"; action.UserId = new ObjectId(dr["userid"].ToString()); action.ProductId = new ObjectId(dr["productid"].ToString()); actions.Add(action); } cmd.CommandText = "select userid, productid, grade from reviewedproducts where time>@t union select userid, productid, grade from reviewedproducts where " + "time>=@tp and time<=@tk"; dt.Clear(); da.Fill(dt); foreach (DataRow dr in dt.Rows) { DomainModel.RecommenderAction action = new DomainModel.RecommenderAction(); action.Action = "Review"; action.UserId = new ObjectId(dr["userid"].ToString()); action.ProductId = new ObjectId(dr["productid"].ToString()); action.Grade = int.Parse(dr["grade"].ToString()); actions.Add(action); } cmd.CommandText = "select userid, productid from boughtproducts where time>@t union select userid, productid from boughtproducts where " + "time>=@tp and time<=@tk"; dt.Clear(); da.Fill(dt); foreach (DataRow dr in dt.Rows) { DomainModel.RecommenderAction action = new DomainModel.RecommenderAction(); action.Action = "Buy"; action.UserId = new ObjectId(dr["userid"].ToString()); action.ProductId = new ObjectId(dr["productid"].ToString()); actions.Add(action); } cmd.CommandText = "select userid, productid from seenreviews where time>@t union select userid, productid from seenreviews where " + "time>=@tp and time<=@tk"; dt.Clear(); da.Fill(dt); MongodbFunctions mongo = new MongodbFunctions(); foreach (DataRow dr in dt.Rows) { DomainModel.RecommenderAction action = new DomainModel.RecommenderAction(); action.Action = "SeeReviews"; action.UserId = new ObjectId(dr["userid"].ToString()); action.ProductId = new ObjectId(dr["productid"].ToString()); action.Grade = mongo.AverageGrade(action.ProductId)[0]; actions.Add(action); } cmd.Dispose(); return(actions); }
public List <DomainModel.RecommenderAction> GetMonthlyActivities(int months) { List <DomainModel.RecommenderAction> actions = new List <DomainModel.RecommenderAction>(); NpgsqlCommand cmd = new NpgsqlCommand(); cmd.Connection = conn; cmd.CommandText = "select userid, productid from viewedproducts where time>@t"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new NpgsqlParameter("@t", DateTime.Now.AddMonths(-1 * months))); da = new NpgsqlDataAdapter(cmd); dt = new DataTable(); da.Fill(dt); foreach (DataRow dr in dt.Rows) { DomainModel.RecommenderAction action = new DomainModel.RecommenderAction(); action.Action = "View"; action.UserId = new ObjectId(dr["userid"].ToString()); action.ProductId = new ObjectId(dr["productid"].ToString()); actions.Add(action); } cmd.CommandText = "select userid, productid, grade from reviewedproducts where time>@t"; dt.Clear(); da.Fill(dt); foreach (DataRow dr in dt.Rows) { DomainModel.RecommenderAction action = new DomainModel.RecommenderAction(); action.Action = "Review"; action.UserId = new ObjectId(dr["userid"].ToString()); action.ProductId = new ObjectId(dr["productid"].ToString()); action.Grade = int.Parse(dr["grade"].ToString()); actions.Add(action); } cmd.CommandText = "select userid, productid from boughtproducts where time>@t"; dt.Clear(); da.Fill(dt); foreach (DataRow dr in dt.Rows) { DomainModel.RecommenderAction action = new DomainModel.RecommenderAction(); action.Action = "Buy"; action.UserId = new ObjectId(dr["userid"].ToString()); action.ProductId = new ObjectId(dr["productid"].ToString()); actions.Add(action); } cmd.CommandText = "select userid, productid from seenreviews where time>@t"; dt.Clear(); da.Fill(dt); MongodbFunctions mongo = new MongodbFunctions(); foreach (DataRow dr in dt.Rows) { DomainModel.RecommenderAction action = new DomainModel.RecommenderAction(); action.Action = "SeeReviews"; action.UserId = new ObjectId(dr["userid"].ToString()); action.ProductId = new ObjectId(dr["productid"].ToString()); action.Grade = mongo.AverageGrade(action.ProductId)[0]; actions.Add(action); } cmd.Dispose(); return(actions); }