public VMWxUserPoints GetWxUserByOpenid(string Openid)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Read))
{
string query = string.Format(@"select t.*,isnull(t1.Points,0) as Points from Train_WxUser t
left join Train_Points t1 on t.Openid=t1.Openid
where t.Openid='{0}'", Openid);
return(conn.Query <VMWxUserPoints>(query).FirstOrDefault());
}
}
public ShopCarEntity GetMyShopByopenIdAndclassId(string openid, string classId)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Read))
{
string query = string.Format(@" select t.*
from Train_ShopCar t
where t.Openid='{0}' and t.ClassId='{1}' and t.status=1", openid, classId);
return(conn.Query <ShopCarEntity>(query).FirstOrDefault());
}
}
public List <VMClassCar> GetMyShopByopenId(string openid)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Read))
{
string query = string.Format(@" select t1.*,t.ShopCarId, 0 as IsChecked
from Train_ShopCar t
inner join Train_Class t1 on t.ClassId=t1.ClassId
where t.Openid='{0}' and t.status=1", openid);
return(conn.Query <VMClassCar>(query).ToList());
}
}
/// <summary>
/// 根据登录账号，获取菜单权限
/// </summary>
/// <param name="loginName"></param>
/// <returns></returns>
public IList <MenuEntity> GetMenusByLoginName(string loginName)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Read))
{
string query = string.Format(@"select Distinct t.*
from Train_Menu t
inner join Train_RoleMenu t1 on t.Menu_Id=t1.Menu_Id
inner join Train_User t3 on t1.Role_Id=t3.Role_Id
where t3.status=1 and t3.login_name=@loginName");
return(conn.Query <MenuEntity>(query, new { loginName = loginName }).ToList());
}
}
public int UpdateBargainNowPrice(string BargainId, decimal nowPrice)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Write))
{
string query = string.Format(@" UPDATE Train_Bargain
SET NowPrice= {0}
,UpdateTime = getdate()
WHERE BargainId='{1}'",
nowPrice, BargainId);
return(conn.Execute(query));
}
}
public int UpdateHelpNowCount(string HelpInfoId, int NowCount)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Write))
{
string query = string.Format(@" UPDATE Train_HelpInfo
SET NowCount= {0}
,UpdateTime = getdate()
WHERE HelpInfoId='{1}'",
NowCount, HelpInfoId);
return(conn.Execute(query));
}
}
public OrderEntity GetOrderByOrderNo(string orderNo)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Read))
{
string query = string.Format(@"select t.*
from Train_Order t
where t.orderNo='{0}'
", orderNo);
return(conn.Query <OrderEntity>(query).FirstOrDefault());
}
}
public UserEntity GetUserByKey(string userId)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Read))
{
string query = string.Format("SELECT * FROM Train_User WHERE status=1 and user_id=@userId");
var result = conn.Query <UserEntity>(query, new { userId = userId });
if (result == null)
{
return(new UserEntity());
}
return(result.FirstOrDefault());
}
}
public RoleEntity GetRoleByKey(string roleId)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Read))
{
string query = string.Format("SELECT * FROM Train_Role WHERE status=1 and role_id=@role_id");
var result = conn.Query <RoleEntity>(query, new { role_id = roleId });
if (result == null)
{
return(new RoleEntity());
}
return(result.FirstOrDefault());
}
}
public OrderEntity GetOrderByOpenIdandClassId(string OpenId, string ClassId)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Read))
{
string query = string.Format(@"select t.*
from Train_Order t
inner join Train_OrderGoods t1 on t.OrderId=t1.OrderId
where t.Openid='{0}' and t1.ClassId='{1}'
", OpenId, ClassId);
return(conn.Query <OrderEntity>(query).FirstOrDefault());
}
}
public VMBargainClass GetBargainClass(string BargainId)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Read))
{
string query = string.Format(@"select t.*,isnull(t2.PrePrice,0) as PrePrice,isnull(t2.NowPrice,0) as NowPrice,isnull(t1.FloorPrice,0) as FloorPrice,t1.StartTime,t1.EndTime,t2.OpenId as ownerOpenid
from Train_Class t
inner join Train_BargainConfig t1 on t.ClassId=t1.ClassId
inner join Train_Bargain t2 on t.ClassId=t2.ClassId
where t2.BargainId='{0}' and t2.status=1", BargainId);
return(conn.Query <VMBargainClass>(query).FirstOrDefault());
}
}
public VMGBClass GetGbClass(string classid)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Read))
{
string query = string.Format(@"select t.*,t1.GroupPrice,t1.NeedCount,t1.StartTime,t1.EndTime,isnull(t2.NowCount,0) as NowCount
from Train_Class t
inner join Train_GroupBuyConfig t1 on t.ClassId=t1.ClassId
left join Train_GroupBuy t2 on t.ClassId=t2.ClassId and t2.status=1
where t.ClassId='{0}' ", classid);
return(conn.Query <VMGBClass>(query).FirstOrDefault());
}
}
public List <VMClassLike> GetClassByCondition(string name, string classType, string startDate, string endDate, string classGroup, string order)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Read))
{
string query = string.Format(@"select * from Train_Class
where status!=0");
if (!string.IsNullOrEmpty(name))
{
string sql = string.Format(" and ClassName like '%{0}%'", name);
query = query + sql;
}
if (!string.IsNullOrEmpty(classType))
{
string sql = string.Format(" and ClassType='{0}'", classType);
query = query + sql;
}
if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
{
string sql = string.Format(" and CreateTime between '{0}' and '{1}'", startDate, endDate);
query = query + sql;
}
if (!string.IsNullOrEmpty(classGroup))
{
string sql = string.Format(" and ClassGroup='{0}'", classGroup);
query = query + sql;
}
if (!string.IsNullOrEmpty(order))
{
string sql = string.Empty;
switch (order)
{
case "TimeDesc":
sql = string.Format(" order by CreateTime desc", order);
query = query + sql;
break;
case "TimeAsc":
sql = string.Format(" order by CreateTime desc", order);
query = query + sql;
break;
}
}
else
{
query = query + " order by CreateTime desc";
}
return(conn.Query <VMClassLike>(query).ToList());
}
}
public int UpdateRole(RoleEntity role, string loginName)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Write))
{
string query = string.Format(@"update train_role set role_name='{0}' ,update_time=getdate(),update_user='******' where role_id='{2}'",
role.Role_Name, loginName, role.Role_Id);
return(conn.Execute(query));
}
//string strConn = ConfigSettings.ConnectionString;
//string strSql = @"update train_user set user_name='{0}' , login_name='{1}',Password='******',update_time=getdate(),update_user='******' where user_id='{4}'";
//strSql = string.Format(strSql,);
//return SqlHelper.ExecuteNonQuery(strConn, CommandType.Text, strSql);
}
public List <VMOrderClass> GetMyClassByopenId(string openid, string title)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Read))
{
string query = string.Format(@" select t2.*,t.status as OrderStatus,t.OrderNo,t1.Price as RealPrice
from Train_Order t
inner join Train_OrderGoods t1 on t.OrderId=t1.OrderId
inner join Train_Class t2 on t1.ClassId=t2.ClassId
where t.Openid='{0}'", openid);
if (!string.IsNullOrEmpty(title))
{
string sql = string.Format(" and t2.ClassName='{0}'", title);
query = query + sql;
}
return(conn.Query <VMOrderClass>(query).ToList());
}
}
public List <VMWxHistory> GetHistoryData(string openid)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Read))
{
string query = string.Format(@"select tt.* from (
select t1.ArticleTitle as Name,t1.ImageUrl as images,'' as Abstract,t1.ImageLink as link,t.ObjectType,t.ObjectId,t.OpenId
from Train_ClickCount t
inner join Train_Article t1 on t.ObjectId=t1.ArticleId
union
select t1.ClassName as Name,t1.ClassImages as images,t1.ClassAbstract as Abstract,'' as link,t.ObjectType,t.ObjectId,t.OpenId
from Train_ClickCount t
inner join Train_Class t1 on t.ObjectId=t1.ClassId) tt
where tt.OpenId='{0}'", openid);
return(conn.Query <VMWxHistory>(query).ToList());
}
}
public List <VMUserInfo> GetUserByCondition(string userName)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Read))
{
string query = string.Format(@"select t.user_id as UserId,t.user_name as UserName,t.login_name as LoginName,t.Password,t.Status,
t.create_time as CreateTime,t.create_user as CreateUser,t.update_time as UpdateTime,t.update_user as UpdateUser,
t2.role_id as RolelId,t2.role_name as RoleName
from train_user t
inner join train_role t2 on t.role_id=t2.role_id
where t.status=1");
if (!string.IsNullOrEmpty(userName))
{
string sql = string.Format(" and t.user_name='{0}'", userName);
query = query + sql;
return(conn.Query <VMUserInfo>(query).ToList());
}
else
{
return(conn.Query <VMUserInfo>(query).ToList());
}
}
}
public List <VMClassChapter> GetChaptersByCondition(string className, string chapterTitle)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Read))
{
string query = string.Format(@"select t.*,t1.ClassName
from Train_Chapter t
inner join Train_Class t1 on t.ClassId=t1.ClassId
where t1.status=1 and t.status=1");
if (!string.IsNullOrEmpty(className))
{
string sql = string.Format(" and t1.ClassName like '%{0}%'", className);
query = query + sql;
}
if (!string.IsNullOrEmpty(chapterTitle))
{
string sql = string.Format(" and t.ChapterTitle like '%{0}%'", chapterTitle);
query = query + sql;
}
return(conn.Query <VMClassChapter>(query).ToList());
}
}
public int SaveClass(ClassEntity Class, string loginName)
{
if (string.IsNullOrEmpty(Class.ClassId))
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Write))
{
string query = string.Format(@"INSERT INTO Train_Class
(ClassId
,ClassName
,ClassType
,ClassContent
,ClassImages
,ClassPrice
,ClassTeacher
,HotCount
,IsGroupBuy
,IsBargain
,IsHelp
,Remark
,Status
,CreateTime
,CreateUser
,UpdateTime
,UpdateUser
,ClassAbstract,ClassGroup,StartTime
,EndTime,VirPrice)
VALUES
('{0}'
,'{1}'
,'{2}'
,'{3}'
,'{4}'
,'{5}'
,'{6}'
,'{7}'
,'{8}'
,'{9}'
,'{10}'
,'{11}'
,'{12}'
,getdate()
,'{13}'
,getdate()
,'{14}'
,'{15}','{16}','{17}','{18}','{19}')",
Guid.NewGuid().ToString(), Class.ClassName, Class.ClassType, Class.ClassContent, Class.ClassImages, Class.ClassPrice,
Class.ClassTeacher, Class.HotCount, Class.IsGroupBuy, Class.IsBargain, Class.IsHelp, Class.Remark, 1, loginName, loginName, Class.ClassAbstract, Class.ClassGroup, Class.StartTime, Class.EndTime, Class.VirPrice);
return(conn.Execute(query));
}
}
else
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Write))
{
string query = string.Format(@" UPDATE Train_Class
SET ClassName= '{0}'
,ClassType= '{1}'
,ClassContent= '{2}'
,ClassImages= '{3}'
,ClassPrice= '{4}'
,ClassTeacher= '{5}'
,HotCount= '{6}'
,IsGroupBuy= '{7}'
,IsBargain= '{8}'
,IsHelp= '{9}'
,Remark = '{10}'
,UpdateTime = getdate()
,UpdateUser = '******'
,ClassAbstract = '{12}',ClassGroup='{13}',StartTime='{15}',EndTime='{16}',VirPrice='{17}'
WHERE ClassId='{14}'",
Class.ClassName, Class.ClassType, Class.ClassContent, Class.ClassImages, Class.ClassPrice, Class.ClassTeacher, Class.HotCount, Class.IsGroupBuy,
Class.IsBargain, Class.IsHelp, Class.Remark, loginName, Class.ClassAbstract, Class.ClassGroup, Class.ClassId, Class.StartTime, Class.EndTime, Class.VirPrice);
return(conn.Execute(query));
}
}
}
public int SaveWxUser(WxUserEntity wxUser, string loginName)
{
var hasInfo = GetWxUserByOpenid(wxUser.Openid);
if (hasInfo == null)
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Write))
{
string query = string.Format(@"INSERT INTO Train_WxUser
(WxUserId
,Subscribe
,Openid
,Nickname
,Sex
,City
,Country
,Province
,UserLanguage
,Headimgurl
,Status
,Phone
,CreateTime
,CreateUser
,UpdateTime
,UpdateUser
,Name)
VALUES
('{0}'
,'{1}'
,'{2}'
,'{3}'
,'{4}'
,'{5}'
,'{6}'
,'{7}'
,'{8}'
,'{9}'
,'{10}'
,'{11}'
,GETDATE()
,'{12}'
,GETDATE()
,'{13}'
,'{14}')",
Guid.NewGuid().ToString(), wxUser.Subscribe, wxUser.Openid, wxUser.Nickname, wxUser.Sex, wxUser.City, wxUser.Country,
wxUser.Province, wxUser.UserLanguage, wxUser.Headimgurl, 1, wxUser.Phone, loginName, loginName, wxUser.Name);
return(conn.Execute(query));
}
}
else
{
using (IDbConnection conn = DBContext.GetConnection(DataBaseName.AccountTrianDB, ReadOrWriteDB.Write))
{
string query = string.Format(@"UPDATE Train_WxUser
SET Subscribe = '{0}'
,Nickname = '{1}'
,Sex = '{2}'
,City ='{3}'
,Country = '{4}'
,Province = '{5}'
,UserLanguage = '{6}'
,Headimgurl = '{7}'
,Phone = '{8}'
,Name = '{9}'
,UpdateTime =getdate()
,UpdateUser = '******'
WHERE WxUserId='{11}'",
wxUser.Subscribe, wxUser.Nickname, wxUser.Sex, wxUser.City, wxUser.Country,
wxUser.Province, wxUser.UserLanguage, wxUser.Headimgurl, wxUser.Phone, wxUser.Name, loginName, wxUser.WxUserId);
return(conn.Execute(query));
}
}
}