/// <summary>
/// Get all users
/// </summary>
/// <returns></returns>
public virtual List <UserModels> GetAll()
{
string sqlStr = "select * from m_user";
List <UserModels> users = new List <UserModels>();
DataTable dt = SqlSeverProvider.ExecuteQuery(sqlStr);
UserModels user = null;
foreach (DataRow row in dt.Rows)
{
user = new UserModels
{
mobilePhone = (string)row["u_mobilePhone"],
password = (string)row["u_password"],
nickName = (string)row["u_nickName"],
cityName = (string)(row["u_cityName"] == System.DBNull.Value ?string.Empty: row["u_cityName"]),
level = (int)(row["u_level"] == System.DBNull.Value ? "0" : row["u_level"]),
userPoint = (int)(row["u_userPoint"] == System.DBNull.Value ? "0" : row["u_userPoint"]),
email = (string)(row["u_email"] == System.DBNull.Value ? string.Empty : row["u_email"]),
avatar = (string)(row["u_avatar"] == System.DBNull.Value ?string.Empty : row["u_avatar"]),
position = (int)(row["u_position"] == System.DBNull.Value ? "-1" : row["u_position"]),
positionTime = (DateTime)(row["u_positionTime"] == System.DBNull.Value ? "2013-08-12" : row["u_positionTime"]),
registerTime = (DateTime)(row["u_registerTime"] == System.DBNull.Value ? "2013-08-12" : row["u_registerTime"])
};
users.Add(user);
}
return(users);
}
public virtual storeActivity GetByPosition(int tradeAreaId, int floorNum, float mapX, float mapY, float scaling)
{
//存储过程名称
string sqlStr = "dbo.dz_Area_GetStoreByPosition ";
storeActivity storeAct = null;
SqlDataReader dr = SqlSeverProvider.ExecuteReader(sqlStr, CommandType.StoredProcedure);
if (dr.Read())
{
storeAct = new storeActivity
{
storeID = dr.GetInt32(0),
storeCName = dr.GetString(1),
storeEName = dr.GetString(2)
};
}
// 根据 storeId 获取活动信息 添加到 storeAct 后 在返回
ActivityModels activity = _ActivityManager.GetByStoreId(storeAct.storeID);
storeAct.activityID = activity.activityID;
storeAct.activityTitle = activity.activityTitle;
storeAct.activityContent = activity.activityContent;
return(storeAct);
}
/// <summary>
/// 将数据集分成训练集 和 测试集 ----------------Success
/// </summary>
/// <param name="i"></param>
/// <param name="j"></param>
public void SplitDataSet(int i, int j)
{
Array.Clear(TestSet, 0, itemTotal * userTotal);
Array.Clear(TrainSet, 0, itemTotal * userTotal);
Thread.Sleep(1000);
string sqlStr = "select * from Ratings";
var dataReader = SqlSeverProvider.ExecuteReader(sqlStr);
while (dataReader.Read())
{
var userID = dataReader.GetInt32(0);
var itemID = dataReader.GetInt32(1);
var rate = dataReader.GetInt32(2);
Random rand = new Random(DateTime.Now.Millisecond);
try
{
if (rand.Next() % (i - 1) == j)
{
TestSet[itemID - 1, userID] = 1;
}
else
{
TrainSet[itemID - 1, userID - 1] = 1;
}
}
catch { }
}
}
/// <summary>
/// 计算所有用户的兴趣程度矩阵
/// </summary>
public void GetUserInterestMatrix()
{
Array.Clear(UserInterestMatrix, 0, itemTotal * userTotal);
for (int i = 0; i < userTotal; i++)
{
DataTable Tb_UserInterest = new DataTable();
Tb_UserInterest.Columns.Add("userID", System.Type.GetType("System.Int32"));
Tb_UserInterest.Columns.Add("itemID", System.Type.GetType("System.Int32"));
Tb_UserInterest.Columns.Add("interest", System.Type.GetType("System.Double"));
string tableName = "UserInterest";
for (int j = 0; j < itemTotal; j++)
{
if (TrainSet[j, i] == 0)
{
UserInterestMatrix[i, j] = CalculateUserInterest(i, j, 10);
DataRow row = Tb_UserInterest.NewRow();
row[0] = i + 1;
row[1] = j + 1;
row[2] = UserInterestMatrix[i, j];
Tb_UserInterest.Rows.Add(row);
}
}
SqlSeverProvider.ExecuteSqlBulkCopy(Tb_UserInterest, tableName);
}
}
public virtual List <StoreModels> GetStoresByName(string name)//店铺查询
{
string sqlStr = "select * from m_store where s_storeCName like '%" + name + "%' or s_storeEName like '%" + name + "%' ";
DataTable dt = SqlSeverProvider.ExecuteQuery(sqlStr);
List <StoreModels> listStores = new List <StoreModels>();
StoreModels store = null;
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
store = new StoreModels
{
storeID = (int)row["s_storeId"],
areaKeyID = (int)row["s_areaKeyId"],
storeCName = (string)(row["s_storeCName"] == DBNull.Value ? string.Empty : row["s_storeCName"]),
storeEName = (string)(row["s_storeEName"] == DBNull.Value ? string.Empty : row["s_storeEName"]),
brandsID = (int)row["s_brandsId"],
address = (string)(row["s_address"] == DBNull.Value ? string.Empty : row["s_address"]),
phoneNumber = (string)(row["s_phoneNumber"] == DBNull.Value ? string.Empty : row["s_phoneNumber"]),
arrivalNum = (int)row["s_arrivalNum"]
};
listStores.Add(store);
}
return(listStores);
}
return(null);
}
//根据商圈楼层获取电梯扶梯
public virtual List <AreaModels> GetLifts(int TradeAreaID, int FloorNum)
{
string sqlString = "dbo.dz_Area_GetLift";
SqlParameter[] parm = { new SqlParameter("@TradeAreaId", TradeAreaID),
new SqlParameter("@FloorNum", FloorNum) };
List <AreaModels> lifts = new List <AreaModels>();
SqlDataReader reader = SqlSeverProvider.ExecuteReader(sqlString, CommandType.StoredProcedure, parm);
while (reader.Read())
{
lifts.Add(new AreaModels
{
AreaKeyID = (int)reader["a_allAreaKeyId"],
TradeAreaID = (int)reader["a_tradeAreaId"],
TradeAreaFloor = (int)reader["a_tradeAreaFloor"],
Category = (int)reader["a_areaCategory"],
AradID = (int)reader["a_areaId"],
StartX = float.Parse(reader["a_startX"].ToString()),
StartY = float.Parse(reader["a_startY"].ToString()),
Width = float.Parse(reader["a_width"].ToString()),
Height = float.Parse(reader["a_height"].ToString()),
IsMark = (bool)reader["a_isMark"]
});
}
reader.Close();
return(lifts);
}
public virtual StoreModels GetById(int id)
{
string sqlStr = "select * from m_store where s_storeId='" + id + "'";
SqlDataReader dr = SqlSeverProvider.ExecuteReader(sqlStr);
StoreModels store = null;
if (dr.Read())
{
store = new StoreModels
{
storeID = dr.GetInt32(0),
areaKeyID = dr.GetInt32(1),
storeCName = dr.GetString(2),
storeEName = dr.GetString(3),
categoryID = dr.GetInt32(4),
logo = dr.GetString(5),
brandsID = dr.GetInt32(6),
address = dr.GetString(7),
phoneNumber = dr.GetString(8),
arrivalNum = dr.GetInt32(9)
};
dr.Close();
return(store);
}
return(null);
}
//Method of IProvider
public virtual List <AreaModels> GetAll()
{
string sqlString = "select * from m_area";
List <AreaModels> areas = new List <AreaModels>();
AreaModels area = null;
DataTable tab = SqlSeverProvider.ExecuteQuery(sqlString);
foreach (DataRow row in tab.Rows)
{
area = new AreaModels
{
AreaKeyID = (int)row["a_allAreaKeyId"],
TradeAreaID = (int)row["a_tradeAreaId"],
TradeAreaFloor = (int)row["a_tradeAreaFloor"],
AradID = (int)row["a_areaId"],
Category = (int)row["a_areaCategory"],
StartX = (float)row["a_startX"],
StartY = (float)row["a_startY"],
Width = (float)row["a_width"],
Height = (float)row["a_height"],
IsMark = (bool)row["a_isMark"]
};
areas.Add(area);
}
return(areas);
}
public virtual List <UserModels> GetUserNearby(int tradeAreaId)
{
List <dynamic> dym = new List <dynamic>();
string sqlString = "dbo.dz_User_GetUserNearby";
SqlParameter[] parm = { new SqlParameter("@TradeAreaId", tradeAreaId) };
List <UserModels> users = new List <UserModels>();
DataTable dtb = SqlSeverProvider.ExecuteQuery(sqlString, CommandType.StoredProcedure, parm);
if (dtb == null)
{
return(users);
}
foreach (DataRow row in dtb.Rows)
{
users.Add(new UserModels
{
mobilePhone = (string)row["u_mobilePhone"],
password = row["a_tradeAreaFloor"].ToString(),
nickName = (string)row["u_nickName"],
avatar = (string)(row["u_avatar"] == System.DBNull.Value ? string.Empty : row["u_avatar"]),
positionTime = (DateTime)(row["u_positionTime"] == System.DBNull.Value ? "2013-08-12" : row["u_positionTime"])
});
}
return(users);
}
/// <summary>
/// Get user by mobilePhone
/// </summary>
/// <param name="phone"></param>
/// <returns></returns>
public virtual UserModels GetByPhone(string phone)
{
string sqlStr = "select * from m_user where u_mobilePhone=@phone";
SqlParameter[] parm =
{
new SqlParameter("@phone", phone)
};
DataTable dt = SqlSeverProvider.ExecuteQuery(sqlStr);
UserModels user = null;
foreach (DataRow row in dt.Rows)
{
user = new UserModels
{
mobilePhone = (string)row["u_mobilePhone"],
password = (string)row["u_password"],
nickName = (string)row["u_nickName"],
cityName = (string)(row["u_cityName"] == System.DBNull.Value ? string.Empty : row["u_cityName"]),
level = (int)(row["u_level"] == System.DBNull.Value ? "0" : row["u_level"]),
userPoint = (int)(row["u_userPoint"] == System.DBNull.Value ? "0" : row["u_userPoint"]),
email = (string)(row["u_email"] == System.DBNull.Value ? string.Empty : row["u_email"]),
avatar = (string)(row["u_avatar"] == System.DBNull.Value ? string.Empty : row["u_avatar"]),
position = (int)(row["u_position"] == System.DBNull.Value ? "-1" : row["u_position"]),
positionTime = (DateTime)(row["u_positionTime"] == System.DBNull.Value ? "2013-08-12" : row["u_positionTime"]),
registerTime = (DateTime)(row["u_registerTime"] == System.DBNull.Value ? "2013-08-12" : row["u_registerTime"])
};
}
return(user);
}
/// <summary>
/// 分类查询商铺
/// </summary>
/// <param name="cateID"></param>
/// <param name="tradeAreaId"></param>
/// <returns></returns>
public virtual List <StoreModels> GetStoresByCategory(int cateID, int tradeAreaId)
{
string sqlStr = "dbo.dz_Store_GetSpecialStore";
SqlParameter[] parm =
{
new SqlParameter("@TradeAreaId", tradeAreaId),
new SqlParameter("@CategoryId", cateID)
};
DataTable dt = SqlSeverProvider.ExecuteQuery(sqlStr, CommandType.StoredProcedure, parm);
List <StoreModels> listStores = new List <StoreModels>();
StoreModels store = null;
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
store = new StoreModels
{
storeID = (int)row["s_storeId"],
areaKeyID = (int)row["s_areaKeyId"],
storeCName = (string)(row["s_storeCName"] == DBNull.Value ? string.Empty : row["s_storeCName"]),
storeEName = (string)(row["s_storeEName"] == DBNull.Value ? string.Empty : row["s_storeEName"]),
brandsID = (int)row["s_brandsId"],
address = (string)(row["s_address"] == DBNull.Value ? string.Empty : row["s_address"]),
phoneNumber = (string)(row["s_phoneNumber"] == DBNull.Value ? string.Empty : row["s_phoneNumber"]),
arrivalNum = (int)row["s_arrivalNum"]
};
listStores.Add(store);
}
return(listStores);
}
return(null);
}
public virtual void Add(StoreModels store)
{
string sqlStr = string.Format("insert into m_store values({0},'{1}','{2}',{3},'{4}',{5},'{6}','{7}',{8})",
store.areaKeyID, store.storeCName, store.storeEName, store.categoryID, store.logo, store.brandsID,
store.address, store.phoneNumber, store.arrivalNum);
SqlSeverProvider.ExecuteNonQuery(sqlStr);
}
public virtual bool UpdatePosition(string phone, int positionId)
{
string sqlStr = string.Format("update m_user set u_position ={0} where u_mobilePhone='{1}'", positionId, phone);
var result = SqlSeverProvider.ExecuteNonQuery(sqlStr);
if (result > 0)
{
return(true);
}
return(false);
}
/// <summary>
/// 根据用户的评分行为进行推荐
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static List <int> GetRecommend(int userId)
{
List <int> itemsList = new List <int>();
string sqlStr = string.Format("select itemID from Recommended where userID={0}", userId);
SqlDataReader dataReader = SqlSeverProvider.ExecuteReader(sqlStr);
while (dataReader.Read())
{
itemsList.Add(dataReader.GetInt32(0));
}
return(itemsList);
}
//------------------------------------------------------
//数据库字段 englisgName 改为englishName
//
//---------------------------------------------------------
#region Add
public virtual void Add(BrandModels brand)
{
string sqlStr = "insert into m_brands values (@chineseName,@englishName,@logo)";
SqlParameter[] parm =
{
new SqlParameter("@chineseName", brand.chineseName),
new SqlParameter("@englishName", brand.englishName),
new SqlParameter("@logo", brand.logo)
};
var result = SqlSeverProvider.ExecuteNonQuery(sqlStr);
}
/// <summary>
/// 根据购物车推荐
/// </summary>
/// <param name="userId"></param>
/// <param name="shoppingCart"></param>
/// <returns></returns>
public static List <int> GetRecommend(int userId, List <int> shoppingCart)
{
List <int> itemsList = new List <int>();
string sqlStr = string.Format("select top(10) itemID,COUNT(itemID) as total from Ratings group by itemID order by total desc");
SqlDataReader dataReader = SqlSeverProvider.ExecuteReader(sqlStr);
while (dataReader.Read())
{
itemsList.Add(dataReader.GetInt32(0));
}
return(itemsList);
}
public TradeAreaModels GetById(int id)
{
string sqlString = "select * from m_tradeArea where t_tradeAreaId = " + id;
System.Data.SqlClient.SqlDataReader reader = SqlSeverProvider.ExecuteReader(sqlString);
TradeAreaModels trade = null;
if (reader.Read())
{
trade = TradeAreaManager.GetTradeAreaByReader(reader);
}
return(trade);
}
/// <summary>
/// Add user
/// </summary>
/// <param name="user"></param>
public virtual void Add(UserModels user)
{
string sqlStr = "insert into m_user(u_mobilePhone,u_password,u_nickName) values(@mobilePhone,@password,@nickName)";
SqlParameter[] parm =
{
new SqlParameter("@mobilePhone", user.mobilePhone),
new SqlParameter("@password", user.password),
new SqlParameter("@nickName", user.nickName)
};
var result = SqlSeverProvider.ExecuteNonQuery(sqlStr);
// 是否返回 true or
}
//Methods of ITradeAreaProvider
public List <TradeAreaModels> GetTradeAreaByCity(string cityname)
{
string sqlString = "dbo.dz_TradeArea_GetByCityName";
SqlParameter[] parm = { new SqlParameter("@CityName", cityname) };
List <TradeAreaModels> trades = new List <TradeAreaModels>();
SqlDataReader reader = SqlSeverProvider.ExecuteReader(sqlString, CommandType.StoredProcedure, parm);
while (reader.Read())
{
trades.Add(GetTradeAreaByReader(reader));
}
return(trades);
}
public virtual void Add(AreaModels area)
{
int ismark = 0;
if (area.IsMark == true)
{
ismark = 1;
}
string sqlString = string.Format("insert into m_area values({0},{1},{2},{3},{4},{5},{6},{7},{8})",
area.TradeAreaID, area.TradeAreaFloor, area.AradID, area.Category, area.StartX, area.StartY,
area.Width, area.Height, ismark);
SqlSeverProvider.ExecuteNonQuery(sqlString);
}
public void Add(TradeAreaModels trade)
{
string sqlString = string.Format("insert into m_tradeArea values({0},{1},{2},{3},{4},{5},{6},{7}",
trade.TradeAreaProvince,
trade.TradeAreaCity,
"'" + trade.TradeAreaCityName + "'",
"'" + trade.TradeAreaName + "'",
"'" + trade.TradeAreaAddress + "'",
trade.FloorCount,
trade.FirstFloor,
"'" + trade.TradeAreaLogo + "')");
SqlSeverProvider.ExecuteNonQuery(sqlString);
}
public static bool Login(string userName)
{
int userId = int.Parse(userName);
string sqlStr = string.Format("select * from Users where userID={0}", userId);
var result = SqlSeverProvider.ExecuteScalar(sqlStr);
if (result != null)
{
return(true);
}
else
{
return(false);
}
}
public void Update(int id, TradeAreaModels trade)
{
string sqlString = string.Format("update m_tradeArea set t_tradeAreaProvince={0}, t_tradeAreaCity={1}, t_tradeAreaCityName={2}, " +
"t_tradeAreaName={3}, t_tradeAreaAddress={4}, t_floorCount={5}, t_firstFloor={6}, t_tradeAreaLogo={7} where t_tradeAreaId={8}",
trade.TradeAreaProvince,
trade.TradeAreaCity,
"'" + trade.TradeAreaCityName + "'",
"'" + trade.TradeAreaName + "'",
"'" + trade.TradeAreaAddress + "'",
trade.FloorCount,
trade.FirstFloor,
"'" + trade.TradeAreaLogo + "'",
id);
SqlSeverProvider.ExecuteNonQuery(sqlString);
}
//Methods of IProvider
public List <TradeAreaModels> GetAll()
{
string sqlString = "select * from m_tradeArea";
List <TradeAreaModels> trades = new List <TradeAreaModels>();
DataTable tbl = SqlSeverProvider.ExecuteQuery(sqlString);
if (tbl == null)
{
return(null);
}
foreach (DataRow row in tbl.Rows)
{
trades.Add(TradeAreaManager.GetTradeAreaByRow(row));
}
return(trades);
}
/// <summary>
/// Update user profile according to mobilePhone
/// </summary>
/// <param name="phone"></param>
/// <param name="newUser"></param>
public virtual void UpdateByPhone(string phone, UserModels newUser)
{
string sqlStr = "update m_user set u_mobilePhone=@mobilePhone,u_password=@password,"
+ "u_nickName=@nickName,u_cityName=@cityName,u_email=@email,u_avatar=@avatar where u_mobilePhone=" + phone;
SqlParameter[] parm =
{
new SqlParameter("@mobilePhone", newUser.mobilePhone),
new SqlParameter("@password", newUser.password),
new SqlParameter("@nickName", newUser.nickName),
new SqlParameter("@cityName", newUser.cityName),
new SqlParameter("@email", newUser.email),
new SqlParameter("@avatar", newUser.avatar)
};
var result = SqlSeverProvider.ExecuteNonQuery(sqlStr);
}
/// <summary>
/// 推荐物品
/// </summary>
public void GetRecommend()
{
int mostInterestNum; //当前最感兴趣物品
for (int i = 0; i < userTotal; i++)
{
int [] orderFlag = new int[itemTotal];
for (int j = 0; j < recommendNum; j++)
{
mostInterestNum = 0;
while (orderFlag[mostInterestNum] != 0)
{
mostInterestNum++;
}
for (int k = 0; k < itemTotal; k++)
{
if (UserInterestMatrix[i, mostInterestNum] < UserInterestMatrix[j, k] && orderFlag[k] == 0)
{
mostInterestNum = k;
}
}
orderFlag[mostInterestNum] = 1;
RecommendSet[i, j] = mostInterestNum;
}
}
Console.WriteLine("推荐完成...");
for (int i = 0; i < 6040; i++)
{
DataTable Tb_Recommended = new DataTable();
Tb_Recommended.Columns.Add("userID", System.Type.GetType("System.Int32"));
Tb_Recommended.Columns.Add("itemID", System.Type.GetType("System.Int32"));
string tableName = "Recommended";
for (int j = 0; j < 10; j++)
{
var item = (int)RecommendSet[i, j];
//string str = string.Format("insert into Recommended values({0},{1})",i+1,item);
//SqlSeverProvider.ExecuteNonQuery(str);
DataRow row = Tb_Recommended.NewRow();
row[0] = i + 1;
row[1] = item;
Tb_Recommended.Rows.Add(row);
}
SqlSeverProvider.ExecuteSqlBulkCopy(Tb_Recommended, tableName);
}
Console.WriteLine("推荐完成22222...");
}
public virtual BrandModels GetById(int id)
{
string sqlStr = string.Format("select * from m_brands where b_brandsId={0}", id);
BrandModels brand = null;
DataTable dt = SqlSeverProvider.ExecuteQuery(sqlStr);
foreach (DataRow row in dt.Rows)
{
brand = new BrandModels
{
brandsID = (int)row["b_brandsId"],
chineseName = (string)(row["b_chineseName"] == System.DBNull.Value ?string.Empty : row["b_chineseName"]),
englishName = (string)(row["b_englisgName"] == System.DBNull.Value ? string.Empty : row["b_englisgName"]),
logo = (string)(row["b_logo"] == System.DBNull.Value ? string.Empty : row["b_logo"])
};
}
return(brand);
}
public virtual List <BrandModels> GetAll()
{
string sqlStr = "select * from m_brands";
List <BrandModels> brands = new List <BrandModels>();
DataTable dt = SqlSeverProvider.ExecuteQuery(sqlStr);
BrandModels brand = null;
foreach (DataRow row in dt.Rows)
{
brand = new BrandModels
{
brandsID = (int)row["b_brandsId"],
chineseName = (string)(row["b_chineseName"] == System.DBNull.Value ? string.Empty : row["b_chineseName"]),
englishName = (string)(row["b_englishName"] == System.DBNull.Value ? string.Empty : row["b_englishName"]),
logo = (string)row["b_logo"]
};
brands.Add(brand);
}
return(brands);
}
/// <summary>
/// 计算所有物品之间的相似性，得到相似性矩阵
/// </summary>
public void GetSimilarityMatrix()
{
Array.Clear(SimilarityMatrix, 0, itemTotal * itemTotal);
for (int i = 0; i < itemTotal; i++)
{
DataTable Tb_SimilarityMatrix = new DataTable();
Tb_SimilarityMatrix.Columns.Add("itemOne", System.Type.GetType("System.Int32"));
Tb_SimilarityMatrix.Columns.Add("itemTwo", System.Type.GetType("System.Int32"));
Tb_SimilarityMatrix.Columns.Add("similarity", System.Type.GetType("System.Double"));
string tableName = "SimilarityMatrix";
for (int j = 0; j < itemTotal; j++)
{
SimilarityMatrix[i, j] = CalculateSimilarityForA_B(i, j);
DataRow row = Tb_SimilarityMatrix.NewRow();
row[0] = i + 1;
row[1] = j + 1;
row[2] = SimilarityMatrix[i, j];
Tb_SimilarityMatrix.Rows.Add(row);
}
SqlSeverProvider.ExecuteSqlBulkCopy(Tb_SimilarityMatrix, tableName);
}
}
private static void OutUsersInterest()
{
for (int i = 0; i < 5; i++)
{
Random rand = new Random(DateTime.Now.Millisecond);
var user = rand.Next() % 6040;
Console.WriteLine("User " + user + ":");
string sqlStr = string.Format("select top(100) itemID,interest from UserInterest where userID={0} and interest>0 order by itemID", user);
SqlDataReader reader = SqlSeverProvider.ExecuteReader(sqlStr);
int num = 1;
while (reader.Read())
{
Console.Write(reader.GetInt32(0) + ":");
Console.Write(reader.GetDouble(1).ToString("0.0000") + " ; ");
if (num > 0 && num % 5 == 0)
{
Console.Write("\n");
}
num++;
}
Console.Write("\n");
}
}
