public List<Recommendation_Meta_Item> GetRecommendC6_ForSubCategory(string SubCate, int k) { string sql; Dictionary<string, object> parameters = new Dictionary<string, object>(); parameters.Add("@k", k.ToString()); parameters.Add("@SubCate", SubCate); // Recommendation_Meta_Item list = new Recommendation_Meta_Item(); List<Recommendation_Meta_Item> list = new List<Recommendation_Meta_Item>(); Recommendation_Meta_Item item; //----------------------------------------------- sql = "SELECT TOP "+k+" [RS].SCORE_GLOBAL_C6.MetaItemID, SCORE FROM [RS].SCORE_GLOBAL_C6 WHERE [RS].SCORE_GLOBAL_C6.MetaItemID " + "IN(SELECT DISTINCT MetaItemID FROM [RS].ALL_TRANSACTION WHERE UserID IN(SELECT UserID From RS.USER_TBL WHERE U_SubCategoryID = @SubCate)) ORDER BY SCORE DESC"; SqlDataReader dr = executeReader(sql, parameters); bool Check = dr.HasRows; while (dr.Read()) { item = new Recommendation_Meta_Item(); item.UserID = SubCate;//Luu tam thoi item.RecommendType = ConstantValues.RC_TYPE_LRS01;// "Best Selling"; item.MetaItemID = dr[0].ToString(); item.Quantity = 0; item.Score = Convert.ToDouble(dr[1]); list.Add(item); } dr.Close(); if (!Check) { sql = "SELECT TOP "+k+" [RS].[CLUSTER_SCORE_C6].MetaItemID, Q " + "FROM [RS].[CLUSTER_SCORE_C6] " + "WHERE [RS].[CLUSTER_SCORE_C6].MetaItemID IN(SELECT DISTINCT MetaItemID FROM [RS].ALL_TRANSACTION WHERE UserID IN( " + "SELECT UserID From RS.USER_TBL WHERE U_SubCategoryID = @SubCate)) ORDER BY Q DESC"; SqlDataReader dr2 = executeReader(sql, parameters); while (dr2.Read()) { item = new Recommendation_Meta_Item(); item.UserID = SubCate;//Luu tam thoi item.RecommendType = ConstantValues.RC_TYPE_LRS01;//"Best Selling"; item.MetaItemID = dr2[0].ToString(); // item.MetaItemName = dr2[1].ToString(); item.Quantity = 0; item.Score = Convert.ToDouble(dr2[1]); list.Add(item); } dr2.Close(); } return list; }
//SONNT has modified //public void addRecommendationItem(Recommendation_Item RCItem) //SONNT has modified //{ // Dictionary<string, object> parameters = new Dictionary<string, object>(); // parameters.Add("@ScheduleID", RCItem.ScheduleID); // parameters.Add("@MetaItemID", RCItem.MetaItemID); // parameters.Add("@UserID", RCItem.UserID); // parameters.Add("@Quantity", RCItem.Quantity); // parameters.Add("@Score", RCItem.Score); // parameters.Add("@RecommendType", RCItem.RecommendType); // parameters.Add("@Price", RCItem.Price); // //executeNonQuery("INSERT INTO [RS].[RECOMMENDATION_TBL] ([ScheduleID] ,[MetaItemID] ,[UserID] ,[Quantity] ,[Score] ,[RecommendType],[Price],[MetaItemName],[UserName]) VALUES (@ScheduleID ,@MetaItemID ,@UserID ,@Quantity ,@Score ,@RecommendType,@Price, (SELECT i.[MetaItemName] from [RS].[META_ITEM_TBL] i WHERE i.[MetaItemID] = @MetaItemID),(SELECT u.[UserName] from [RS].[USER_TBL] u WHERE u.[UserID] = @UserID))", parameters); // executeNonQuery("INSERT INTO [RS].[RECOMMENDATION_TBL] ([ScheduleID] ,[MetaItemID] ,[UserID] ,[Quantity] ,[Score] ,[RecommendType],[Price]) VALUES (@ScheduleID ,@MetaItemID ,@UserID ,@Quantity ,@Score ,@RecommendType,@Price)", parameters); //} public void addRecommendationItem(Recommendation_Meta_Item RCItem) { string[] MetaID = RCItem.MetaItemID.Split('_'); RCItem.MetaItemID = MetaID[0]; RCItem.ItemFamillyCode = MetaID[1]; Dictionary<string, object> parameters = new Dictionary<string, object>(); parameters.Add("@ScheduleID", RCItem.ScheduleID); parameters.Add("@MetaItemID", RCItem.MetaItemID); parameters.Add("@UserID", RCItem.UserID); parameters.Add("@Quantity", RCItem.Quantity); parameters.Add("@Score", RCItem.Score); parameters.Add("@RecommendType", RCItem.RecommendType); parameters.Add("@ItemFamillyCode", RCItem.ItemFamillyCode); executeNonQuery("INSERT INTO [RS].[RECOMMENDATION_TBL] ([ScheduleID] ,[MetaItemID] ,[UserID] ,[Quantity] ,[Score] ,[RecommendType],[ItemFamillyCode]) VALUES (@ScheduleID ,@MetaItemID ,@UserID ,@Quantity ,@Score ,@RecommendType,@ItemFamillyCode)", parameters); }
public List<Recommendation_Meta_Item> GetRecommendations(string sql) { List<Recommendation_Meta_Item> list = new List<Recommendation_Meta_Item>(); SqlDataReader dr = executeReader(sql); while (dr.Read()) { Recommendation_Meta_Item item = new Recommendation_Meta_Item(); item.UserName = dr.GetString(dr.GetOrdinal("UserName")); item.UserID = dr.GetString(dr.GetOrdinal("UserID")); item.MetaItemID = dr.GetString(dr.GetOrdinal("ItemID")); item.MetaItemName = dr.GetString(dr.GetOrdinal("MetaItemName")); item.Quantity = dr.GetDouble(dr.GetOrdinal("Quantity")); list.Add(item); } dr.Close(); return list; }
public Int32 GetQualtityTraditionalUser(Recommendation_Meta_Item RC) { int QTY = 0; Dictionary<string, object> parameters = new Dictionary<string, object>(); parameters.Add("@UserID", RC.UserID); parameters.Add("@MetaItemID", RC.MetaItemID); string sql = "SELECT AVG([QAV]*[WEIGHT]) AS QTY " + "FROM [RS].[ALL_TRANSACTION], [RS].[WEIGHT_MATRIX] " + "WHERE MetaItemDestination =@MetaItemID AND [MetaItemSource] = [MetaItemID] AND [WEIGHT_MATRIX].ClusterID = [ALL_TRANSACTION].ClusterID AND [UserID] = @UserID"; SqlDataReader dr = executeReader(sql, parameters); while (dr.Read()) { if (!dr.IsDBNull(0)) { double WEI = Convert.ToDouble(dr[0], CultureInfo.CreateSpecificCulture("en-GB")); QTY = (int) WEI; } } dr.Close(); //---------------------------------- if (QTY == 0) { sql = "SELECT AVG([QAV]) AS QTY " + "FROM [RS].[ALL_TRANSACTION] " + "WHERE [MetaItemID] = @MetaItemID AND [UserID] = @UserID"; SqlDataReader dr1 = executeReader(sql, parameters); while (dr1.Read()) { if (!dr1.IsDBNull(0)) { double WEI = Convert.ToDouble(dr1[0], CultureInfo.CreateSpecificCulture("en-GB")); QTY = (int) WEI; } } dr1.Close(); } //-------------------------------- return QTY; }
public double GetQualtityNewUser(Recommendation_Meta_Item RC) { double QAV = 2; Dictionary<string, object> parameters = new Dictionary<string, object>(); parameters.Add("@UserID", RC.UserID); parameters.Add("@MetaItemID", RC.MetaItemID); string sql = "SELECT QAV FROM [RS].[QTY_PAR_SUBCATEGORY] WHERE [MetaItemID] = @MetaItemID AND [U_SUBCATEGORYID] = (SELECT U_SUBCATEGORYID FROM [RS].USER_TBL WHERE USERID = @UserID)"; SqlDataReader dr = executeReader(sql, parameters); while (dr.Read()) { QAV = Convert.ToDouble(dr[0]); if (QAV < 0) QAV = 1; } dr.Close(); return QAV; }
public Recommendation_Meta_Item getCandidateForUserSubCategory_R4(string u_subcategory) { Recommendation_Meta_Item item = new Recommendation_Meta_Item(); Dictionary<string, object> parameters = new Dictionary<string, object>(); parameters.Add("@u_subcategory", u_subcategory); string sql = "SELECT TOP 1 U_SUBCATEGORYID, ITEMFAMILLYCODE, RAV as SCORE FROM [RS].SCORE_GLOBAL_C9 where U_SUBCATEGORYID = @u_subcategory " + " AND ITEMFAMILLYCODE IN(SELECT DISTINCT ITEMFAMILLYCODE FROM [RS].NEW_META_ITEM) OrDER BY SCORE DESC"; SqlDataReader dr = executeReader(sql, parameters); while (dr.Read()) { item.UserID = dr[0].ToString();//luu U_SubcategoryID item.MetaItemID = dr[1].ToString();//luu famillycode item.Score = Convert.ToDouble(dr[2]); } dr.Close(); return item; }
//have formular here public List<Recommendation_Meta_Item> GetRecommendationR3(Predict_DAO_MCol a) { List<Recommendation_Meta_Item> list1 = new List<Recommendation_Meta_Item>(); Recommendation_Meta_Item recommendationMetaItem1 = new Recommendation_Meta_Item(); List<CONF> list2 = new List<CONF>(); List<DIST> list3 = new List<DIST>(); List<string> list4 = new List<string>(); List<string> list5 = new List<string>(); List<string> list6 = new List<string>(); List<U_I_Q> list7 = new List<U_I_Q>(); foreach (string cluster in a.GetListClusterID()) { List<CONF> confOfCluster = a.GetCONF_OfCluster(cluster); List<DIST> distOfCluster = a.GetDIST_OfCluster(cluster); List<string> listUserOfCluster = a.GetListUser_OfCluster(cluster); List<U_I_Q> traditionalUsersMore = a.GetQuantityTraditionalUsers_More(cluster); foreach (string user in listUserOfCluster) { List<string> notPurchasedOfUser = a.GetListMetaItemNotPurchased_OfUser(user, cluster); List<string> listMetaItemOfUser = a.GetListMetaItem_OfUser(user); string str = ""; double num1 = 0.0; for (int index1 = 0; index1 < notPurchasedOfUser.Count; ++index1) { double num2 = 0.0; double num3 = 0.0; double num4 = 1.0; for (int index2 = 0; index2 < listMetaItemOfUser.Count; ++index2) { for (int index3 = 0; index3 < confOfCluster.Count; ++index3) { if (confOfCluster[index3].MetaItemSource.Equals(listMetaItemOfUser[index2]) && confOfCluster[index3].MetaItemDestination.Equals(notPurchasedOfUser[index1])) { num3 = confOfCluster[index3].Confident; break; } } for (int index3 = 0; index3 < distOfCluster.Count; ++index3) { if (distOfCluster[index3].MetaItemDestination.Equals(listMetaItemOfUser[index2]) && distOfCluster[index3].MetaItemDestination.Equals(notPurchasedOfUser[index1])) { num4 = distOfCluster[index3].Distance; break; } } if (num4 != 0.0) num2 += num3 / num4; } if (num1 < num2 / (double)listMetaItemOfUser.Count) { num1 = num2; str = notPurchasedOfUser[index1]; } } if (num1 != 0.0) { Recommendation_Meta_Item recommendationMetaItem2 = new Recommendation_Meta_Item(); recommendationMetaItem2.UserID = user; recommendationMetaItem2.MetaItemID = str; recommendationMetaItem2.RecommendType = ConstantValues.RC_TYPE_LRS03; recommendationMetaItem2.Quantity = 0.0; for (int index = 0; index < traditionalUsersMore.Count; ++index) { if (traditionalUsersMore[index].MetaItemID.Equals(str)) { recommendationMetaItem2.Quantity = traditionalUsersMore[index].QTY; break; } } recommendationMetaItem2.Score = num1; list1.Add(recommendationMetaItem2); } } } return list1; }