//取消费列表根据ItemID用于更新消费//
public static ItemEntity GetItemListById(int itemId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetItemListById_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@ItemID";
param.Value = itemId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
DataTable dt = GenericDataAccess.ExecuteCommand(comm);
ItemEntity item = new ItemEntity();
if (dt.Rows.Count > 0)
{
item.ItemID = Int32.Parse(dt.Rows[0]["ItemID"].ToString());
item.ItemType = dt.Rows[0]["ItemType"].ToString();
item.ItemName = dt.Rows[0]["ItemName"].ToString();
item.CategoryTypeID = Int32.Parse(dt.Rows[0]["CategoryTypeID"].ToString());
item.ItemPrice = Double.Parse(dt.Rows[0]["ItemPrice"].ToString());
item.ItemBuyDate = DateTime.Parse(dt.Rows[0]["ItemBuyDate"].ToString());
item.UserID = Int32.Parse(dt.Rows[0]["UserID"].ToString());
item.Recommend = Int32.Parse(dt.Rows[0]["Recommend"].ToString());
item.Synchronize = Int32.Parse(dt.Rows[0]["Synchronize"].ToString());
item.ItemAppID = Int32.Parse(dt.Rows[0]["ItemAppID"].ToString());
item.RegionID = Int32.Parse(dt.Rows[0]["RegionID"].ToString());
item.RegionType = dt.Rows[0]["RegionType"].ToString();
item.ZhuanTiID = Int32.Parse(dt.Rows[0]["ZhuanTiID"].ToString());
}
return(item);
}
//取消费商品数量排行//
public static DataTable GetItemNumTopList(string date, int userId, out int countMax)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetItemNumTopList_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@Date";
param.Value = date;
param.DbType = DbType.String;
param.Size = 10;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@CountMax";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
DataTable dt = GenericDataAccess.ExecuteCommand(comm);
countMax = Int32.Parse(comm.Parameters["@CountMax"].Value.ToString());
return(dt);
}
public static CardEntity GetCardByCardId(int cardId, int userId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCardByCardId_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CardID";
param.Value = cardId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
CardEntity card = new CardEntity();
DataTable dt = GenericDataAccess.ExecuteCommand(comm);
if (dt.Rows.Count > 0)
{
card.CardID = Int32.Parse(dt.Rows[0]["CardID"].ToString());
card.CardName = dt.Rows[0]["CardName"].ToString();
card.CardMoney = Double.Parse(dt.Rows[0]["CardMoney"].ToString());
card.CardLive = Int32.Parse(dt.Rows[0]["CardLive"].ToString());
card.Synchronize = Int32.Parse(dt.Rows[0]["Synchronize"].ToString());
}
return(card);
}
//取支出借还消费分析//
public static DataTable GetJieHuanFenXiList(int userId, string date, string type)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetJieHuanFenXiList_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@Date";
param.Value = date;
param.DbType = DbType.String;
param.Size = 10;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@Type";
param.Value = type;
param.DbType = DbType.String;
param.Size = 10;
comm.Parameters.Add(param);
return(GenericDataAccess.ExecuteCommand(comm));
}
//取专题列表根据专题ID//
public static ZhuanTiEntity GetZhuanTiListById(int zhuanTiId, int userId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetZhuanTiListById_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@ZhuanTiID";
param.Value = zhuanTiId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
ZhuanTiEntity zhuanTi = new ZhuanTiEntity();
DataTable dt = GenericDataAccess.ExecuteCommand(comm);
if (dt.Rows.Count > 0)
{
zhuanTi.ZhuanTiID = Int32.Parse(dt.Rows[0]["ZTID"].ToString());
zhuanTi.ZhuanTiName = dt.Rows[0]["ZhuanTiName"].ToString();
zhuanTi.ZhuanTiImage = dt.Rows[0]["ZhuanTiImage"].ToString();
zhuanTi.ZhuanTiLive = Int32.Parse(dt.Rows[0]["ZhuanTiLive"].ToString());
zhuanTi.Synchronize = Int32.Parse(dt.Rows[0]["Synchronize"].ToString());
zhuanTi.ZhuanTiDate = dt.Rows[0]["ZhuanTiDate"].ToString();
zhuanTi.ZhuanTiShouRu = Double.Parse(dt.Rows[0]["ZhuanTiShouRu"].ToString());
zhuanTi.ZhuanTiZhiChu = Double.Parse(dt.Rows[0]["ZhuanTiZhiChu"].ToString());
}
return(zhuanTi);
}
//取消费类别//ItemList.aspx//UserCategoryAdmin.aspx
public static DataTable GetItemListType()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetItemListType_v4";
return(GenericDataAccess.ExecuteCommand(comm));
}
//取消费价格最高//
public static DataTable GetTongJiByItemPriceMax()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetTongJiByItemPriceMax_v4";
return(GenericDataAccess.ExecuteCommand(comm));
}
//取最后注册的用户//
public static DataTable GetTongJiByUserAddLast()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetTongJiByUserAddLast_v4";
return(GenericDataAccess.ExecuteCommand(comm));
}
//取管理所有用户//
public static DataTable GetAdminUserList()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetAdminUserList_v4";
return(GenericDataAccess.ExecuteCommand(comm));
}
//取总统计数据//
public static DataTable GetAdminTongJiQuanBu()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetAdminTongJiQuanBu_v4";
return(GenericDataAccess.ExecuteCommand(comm));
}
//取所有删除消费列表//
public static DataTable BackupDeleteTable()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "BackupDeleteTable_v4";
return(GenericDataAccess.ExecuteCommand(comm));
}
//取消费次数排行明细//
public static DataTable GetItemNumDetailList(string itemName, string itemType, int userId, int catTypeId, string date, string orderBy, out double priceMax)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetItemNumDetailList_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@ItemName";
param.Value = itemName;
param.DbType = DbType.String;
param.Size = 20;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@ItemType";
param.Value = itemType;
param.DbType = DbType.String;
param.Size = 10;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@CatTypeID";
param.Value = catTypeId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@Date";
param.Value = date;
param.DbType = DbType.String;
param.Size = 10;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@OrderBy";
param.Value = orderBy;
param.DbType = DbType.String;
param.Size = 10;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@PriceMax";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
DataTable dt = GenericDataAccess.ExecuteCommand(comm);
priceMax = Double.Parse(comm.Parameters["@PriceMax"].Value.ToString());
return(dt);
}
//取消费列表价格分析明细//
public static DataTable GetJiaGeFenXiMingXiList(string itemName, string itemType, int userId, int pageNumber, out int howManyItems, out double priceMax)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetJiaGeFenXiMingXiList_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@ItemName";
param.Value = itemName;
param.DbType = DbType.String;
param.Size = 20;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@ItemType";
param.Value = itemType;
param.DbType = DbType.String;
param.Size = 10;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@PageNumber";
param.Value = pageNumber;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@PagePerNumber";
param.Value = WebConfiguration.PagePerNumber;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@HowManyItems";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@PriceMax";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
DataTable dt = GenericDataAccess.ExecuteCommand(comm);
howManyItems = Int32.Parse(comm.Parameters["@HowManyItems"].Value.ToString());
priceMax = Double.Parse(comm.Parameters["@PriceMax"].Value.ToString());
return(dt);
}
//取管理用户资料根据用户ID//
public static DataTable GetAdminUserListById(int userId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetAdminUserListById_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
return(GenericDataAccess.ExecuteCommand(comm));
}
//根据UserGroupID取消费列表，返回DataTable//
public static DataTable SyncGetItemListWebFirst(int userId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "SyncGetItemListWebFirst_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
return(GenericDataAccess.ExecuteCommand(comm));
}
//取管理用户列表根据关键字//
public static DataTable GetAdminUserListByKeywords(string keywords)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetAdminUserListByKeywords_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@Keywords";
param.Value = keywords;
param.DbType = DbType.String;
param.Size = 20;
comm.Parameters.Add(param);
return(GenericDataAccess.ExecuteCommand(comm));
}
//取授权登录列表通过OpenID//
public static DataTable GetOAuthByOpenId(string openId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetOAuthByOpenId_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@OpenID";
param.Value = openId;
param.DbType = DbType.String;
param.Size = 100;
comm.Parameters.Add(param);
return(GenericDataAccess.ExecuteCommand(comm));
}
//取管理用户列表根据日期//
public static DataTable GetAdminUserListByDate(string date)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetAdminUserListByDate_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@Date";
param.Value = date;
param.DbType = DbType.String;
param.Size = 10;
comm.Parameters.Add(param);
return(GenericDataAccess.ExecuteCommand(comm));
}
//取分类总计比较明细//
public static DataTable GetBiJiaoMingXiList(int catTypeId, string date, int userId, out double priceMax, out int countMax)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetBiJiaoMingXiList_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CategoryTypeID";
param.Value = catTypeId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@Date";
param.Value = date;
param.DbType = DbType.String;
param.Size = 10;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@PriceMaxOut";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Double;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@CountMaxOut";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
priceMax = 0;
countMax = 0;
DataTable dt = GenericDataAccess.ExecuteCommand(comm);
priceMax = Double.Parse(comm.Parameters["@PriceMaxOut"].Value.ToString());
countMax = Int32.Parse(comm.Parameters["@CountMaxOut"].Value.ToString());
return(dt);
}
//取消费间隔分析，从商品购买日期与前一个购买日期相隔的天数//
public static DataTable GetJianGeFenXiList(int userId, int pageNumber, out int howManyItems, out int notBuyMax)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetJianGeFenXiList_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@PageNumber";
param.Value = pageNumber;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@PagePerNumber";
param.Value = WebConfiguration.PagePerNumber;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@HowManyItems";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@NotBuyMax";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
DataTable dt = GenericDataAccess.ExecuteCommand(comm);
howManyItems = Int32.Parse(comm.Parameters["@HowManyItems"].Value.ToString());
notBuyMax = Int32.Parse(comm.Parameters["@NotBuyMax"].Value.ToString());
return(dt);
}
//取专题列表明细//
public static DataTable GetZhuanTiListDetail(int zhuanTiId, int userId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetZhuanTiListDetail_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@ZhuanTiID";
param.Value = zhuanTiId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
return(GenericDataAccess.ExecuteCommand(comm));
}
//取消费列表通过当前用户和日期//ItemList.aspx
public static DataTable GetItemList(string date, int userId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetItemList_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@ItemBuyDate";
param.Value = DateTime.Parse(date);
param.DbType = DbType.DateTime;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
return(GenericDataAccess.ExecuteCommand(comm));
}
//根据ItemAppID取消费列表，返回DataTable//
public static DataTable SyncCheckItemListByItemAppId(int itemAppId, int userId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "SyncCheckItemListByItemAppId_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@ItemAppID";
param.Value = itemAppId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
return(GenericDataAccess.ExecuteCommand(comm));
}
//取消费列表商品价格根据商品名称//ItemAddSmart.aspx
public static DataTable GetItemPriceListByName(int userId, string itemName)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetItemPriceListByName_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@ItemName";
param.Value = itemName;
param.DbType = DbType.String;
param.Size = 20;
comm.Parameters.Add(param);
return(GenericDataAccess.ExecuteCommand(comm));
}
//搜索消费列表通过关键字//SearchItem.aspx
public static DataTable GetItemListByKeywords(string words, int userId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetItemListByKeywords_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@Keywords";
param.Value = words;
param.DbType = DbType.String;
param.Size = 20;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
return(GenericDataAccess.ExecuteCommand(comm));
}
//取用户资料根据UserID用于更新//
public static UserEntity GetUserById(int userId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetUserById_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
DataTable dt = GenericDataAccess.ExecuteCommand(comm);
UserEntity user = new UserEntity();
if (dt.Rows.Count > 0)
{
user.UserID = Int32.Parse(dt.Rows[0]["UserID"].ToString());
user.UserName = dt.Rows[0]["UserName"].ToString();
user.UserPassword = dt.Rows[0]["UserPassword"].ToString();
user.UserNickName = dt.Rows[0]["UserNickName"].ToString();
user.UserImage = dt.Rows[0]["UserImage"].ToString();
user.UserEmail = dt.Rows[0]["UserEmail"].ToString();
user.UserPhone = dt.Rows[0]["UserPhone"].ToString();
user.UserTheme = dt.Rows[0]["UserTheme"].ToString();
user.UserLevel = Int32.Parse(dt.Rows[0]["UserLevel"].ToString());
user.UserFrom = dt.Rows[0]["UserFrom"].ToString();
user.UserCity = dt.Rows[0]["UserCity"].ToString();
user.UserMoney = Double.Parse(dt.Rows[0]["UserMoney"].ToString());
user.UserWorkDay = dt.Rows[0]["UserWorkDay"].ToString();
user.UserFunction = dt.Rows[0]["UserFunction"].ToString();
user.ModifyDate = DateTime.Parse(dt.Rows[0]["ModifyDate"].ToString());
user.CreateDate = DateTime.Parse(dt.Rows[0]["CreateDate"].ToString());
user.CategoryRate = Double.Parse(dt.Rows[0]["CategoryRate"].ToString());
}
return(user);
}
//备份用户类别表//
public static DataTable BackupUserCategoryTable(string bakDate, string bakDate2)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "BackupUserCategoryTable_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@BakDate";
param.Value = bakDate;
param.DbType = DbType.String;
param.Size = 10;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@BakDate2";
param.Value = bakDate2;
param.DbType = DbType.String;
param.Size = 10;
comm.Parameters.Add(param);
return(GenericDataAccess.ExecuteCommand(comm));
}
//检查是否绑定根据UserID//
public static bool CheckOAuthBound(int userId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "CheckOAuthBound_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
int result = 1;
DataTable dt = GenericDataAccess.ExecuteCommand(comm);
if (dt.Rows.Count > 0)
{
result = Int32.Parse(dt.Rows[0]["OAuthBound"].ToString());
}
return(result != 0);
}
//检查是否绑定根据OpenID//
public static int CheckOAuthBoundByOpenId(string openId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "CheckOAuthBoundByOpenId_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@OpenID";
param.Value = openId;
param.DbType = DbType.String;
param.Size = 100;
comm.Parameters.Add(param);
int result = 2;
DataTable dt = GenericDataAccess.ExecuteCommand(comm);
if (dt.Rows.Count > 0)
{
result = Int32.Parse(dt.Rows[0]["OAuthBound"].ToString());
}
return(result);
}
//取商品推荐分析//
public static DataTable GetTuiJianFenXiList(int userId, out int monthMax)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetTuiJianFenXiList_v4";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@UserID";
param.Value = userId;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
param = comm.CreateParameter();
param.ParameterName = "@MonthMax";
param.Direction = ParameterDirection.Output;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
DataTable dt = GenericDataAccess.ExecuteCommand(comm);
monthMax = Int32.Parse(comm.Parameters["@MonthMax"].Value.ToString());
return(dt);
}
