internal static UserInfo GetUserById(int userId)
{
UserInfo data = null;
StringBuilder sqlCmd = new StringBuilder("select * from WebUser where UserId=");
sqlCmd.Append(userId.ToString());
sqlCmd.Append(" and IsDelete=0 and Enable=1");
using (var dr = MysqlHelper.ExcuteReader(sqlCmd.ToString()))
{
if (dr.Read())
{
data = new UserInfo()
{
ModifyTime = dr.GetDateTime("ModifyTime"),
CreateTime = dr.GetDateTime("CreateTime"),
Enable = dr.GetBoolean("Enable"),
ModifyBy = dr.GetInt32("ModifyBy"),
CreateBy = dr.GetInt32("CreateBy"),
Email = dr["Email"].ToString(),
LoginName = dr["LoginName"].ToString(),
Mobile = dr["Mobile"].ToString(),
QQ = dr["QQ"].ToString(),
UserId = dr.GetInt32("UserId"),
UserName = dr["UserName"].ToString(),
Pwd = dr["Pwd"].ToString()
};
}
}
return(data);
}
internal static Model.Info GetInfoById(int id)
{
StringBuilder sqlCmd = new StringBuilder("SELECT * FROM Info WHERE IsDelete=0 and Enable=1 and InfoId =");
sqlCmd.Append(id.ToString());
Info data = null;
using (var dr = MysqlHelper.ExcuteReader(sqlCmd.ToString()))
{
if (dr.Read())
{
data = new Info()
{
InfoContent = dr["InfoTitle"].ToString(),
ShowSort = dr.GetInt32("ShowSort"),
InfoId = dr.GetInt32("InfoId"),
InfoTitle = dr["InfoTitle"].ToString(),
CreateBy = dr.GetInt32("CreateBy"),
CreateTime = dr.GetDateTime("CreateTime"),
Enable = dr.GetBoolean("Enable"),
ModifyBy = dr.GetInt32("ModifyBy"),
ModifyTime = dr.GetDateTime("ModifyTime"),
TitleImg = dr["TitleImg"].ToString(),
};
}
}
return(data);
}
internal static UserInfo Login(string loginName, string pwd)
{
UserInfo data = null;
StringBuilder sqlCmd = new StringBuilder("select * from WebUser where LoginName='");
sqlCmd.Append(loginName);
sqlCmd.Append("' and Pwd='");
sqlCmd.Append(pwd);
sqlCmd.Append("' and IsDelete=0 and Enable=1");
using (var dr = MysqlHelper.ExcuteReader(sqlCmd.ToString()))
{
if (dr.Read())
{
data = new UserInfo()
{
Email = dr["Email"].ToString(),
LoginName = dr["LoginName"].ToString(),
Mobile = dr["Mobile"].ToString(),
QQ = dr["QQ"].ToString(),
UserId = dr.GetInt32("UserId"),
UserName = dr["UserName"].ToString()
};
}
}
return(data);
}
internal static List <Info> GetListByChannelId(int channelId)
{
List <Info> list = new List <Info>();
StringBuilder sqlCmd = new StringBuilder("SELECT a.ShowSort,a.InfoTitle,a.InfoContent,a.TitleImg,a.`Enable`,a.CreateTime,a.ModifyTime,a.CreateBy,a.ModifyBy,a.IsDelete,b.InfoId FROM Info AS a INNER JOIN Channel_Info_Relation AS b ON a.InfoId = b.InfoId WHERE a.IsDelete=0 and a.Enable=1 and b.ChannelId =");
sqlCmd.Append(channelId.ToString());
using (var dr = MysqlHelper.ExcuteReader(sqlCmd.ToString()))
{
while (dr.Read())
{
Info data = new Info()
{
InfoContent = dr["InfoTitle"].ToString(),
ShowSort = dr.GetInt32("ShowSort"),
InfoId = dr.GetInt32("InfoId"),
InfoTitle = dr["InfoTitle"].ToString(),
CreateBy = dr.GetInt32("CreateBy"),
CreateTime = dr.GetDateTime("CreateTime"),
Enable = dr.GetBoolean("Enable"),
ModifyBy = dr.GetInt32("ModifyBy"),
ModifyTime = dr.GetDateTime("ModifyTime"),
TitleImg = dr["TitleImg"].ToString(),
};
list.Add(data);
}
}
if (list.Count == 0)
{
list = null;
}
return(list);
}
internal static int Insert(int userId, int channelId, int ShowSort, string infoTitle, string infoContent, bool Enable, string TitleImg, string JumpUrl = "")
{
string sqlCmd = string.Format("insert Info(ShowSort,InfoTitle,InfoContent,TitleImg,Enable,CreateTime,CreateBy,ModifyTime,ModifyBy,JumpUrl,IsJump) values({0},'{1}','{2}','{3}',{4},{5},{6},{7},{8},'{9}',{10});select last_insert_id();", ShowSort.ToString(), infoTitle, infoContent, TitleImg, Enable ? "1" : "0", "now()", userId, "now()", userId, JumpUrl, string.IsNullOrEmpty(JumpUrl)?0:1);
int dataId = 0;
using (var dr = MysqlHelper.ExcuteReader(sqlCmd.ToString()))
{
if (dr.Read())
{
dataId = dr.GetInt32(0);
}
}
return(dataId);
}
internal static GridPage <Info> GetPager(int pageSize, int pageCurrentIndex, int channelId)
{
GridPage <Info> result = new GridPage <Info>()
{
CurrentPage = pageCurrentIndex,
PageSize = pageSize,
};
List <Info> list = new List <Info>();
StringBuilder sqlCmd = new StringBuilder("SELECT a.ShowSort,a.InfoTitle,a.InfoContent,a.TitleImg,a.`Enable`,a.CreateTime,a.ModifyTime,a.CreateBy,a.ModifyBy,a.IsDelete,b.InfoId FROM Info AS a INNER JOIN Channel_Info_Relation AS b ON a.InfoId = b.InfoId WHERE a.IsDelete=0 and a.Enable=1 and b.ChannelId =");
sqlCmd.Append(channelId.ToString());
sqlCmd.Append(" order by ShowSort desc");
sqlCmd.Append(" limit ");
sqlCmd.Append(((pageCurrentIndex - 1) * pageSize).ToString() + "," + pageSize.ToString());
using (var dr = MysqlHelper.ExcuteReader(sqlCmd.ToString()))
{
while (dr.Read())
{
Info data = new Info()
{
InfoContent = dr["InfoTitle"].ToString(),
InfoId = dr.GetInt32("InfoId"),
InfoTitle = dr["InfoTitle"].ToString(),
TitleImg = dr["TitleImg"].ToString(),
};
if (String.IsNullOrEmpty(data.TitleImg))
{
data.IsTitleImg = false;
}
else
{
data.IsTitleImg = true;
}
list.Add(data);
}
}
result.List = list;
return(result);
}
internal static List <Model.Channel> GetShowIndexChannels(int channelCount, int siteId)
{
List <Channel> list = new List <Model.Channel>();
StringBuilder sqlCmd = new StringBuilder("select * from Channel limit ");
sqlCmd.Append(channelCount.ToString());
sqlCmd.Append(" where IsDelete=0 and Enable=1 and IsShowIndex=1 and SiteId=");
sqlCmd.Append(siteId.ToString());
sqlCmd.Append(" order by IndexShowSort asc");
using (var dr = MysqlHelper.ExcuteReader(sqlCmd.ToString()))
{
while (dr.Read())
{
Channel data = new Channel()
{
ChannelId = dr.GetInt32("ChannelId"),
ChannelName = dr["ChannelName"].ToString(),
CreateBy = dr.GetInt32("CreateBy"),
CreateTime = dr.GetDateTime("CreateTime"),
Enable = dr.GetBoolean("Enable"),
IndexShowSort = dr.GetInt32("IndexShowSort"),
InfoTotal = dr.GetInt32("InfoTotal"),
IsShowIndex = dr.GetBoolean("IsShowIndex"),
ModifyBy = dr.GetInt32("ModifyBy"),
ModifyTime = dr.GetDateTime("ModifyTime"),
SiteId = dr.GetInt32("SiteId"),
};
list.Add(data);
}
}
if (list.Count == 0)
{
list = null;
}
return(list);
}
internal static bool Insert(int id, int channelId)
{
string sqlCmd = string.Format("insert Channel_Info_Relation(ChannelId,InfoId,CreateTime) values ({0},{1},{2})", channelId.ToString(), id.ToString(), "now()");
return(MysqlHelper.ExecuteNonQuery(sqlCmd) > 0);
}
internal static bool Delete(int id)
{
string sqlCmd = string.Format("update Info set IsDelete=1 where InfoId={0}", id.ToString());
return(MysqlHelper.ExecuteNonQuery(sqlCmd) > 0);
}
internal static bool Edit(int userId, int infoId, int channelId, int ShowSort, string infoTitle, string infoContent, bool Enable, string TitleImg)
{
string sqlCmd = string.Format("update Info set ShowSort={0},InfoTitle='{1}',InfoContent='{2}',TitleImg='{3}',Enable={4},ModifyTime=now(),ModifyBy={5} where InfoId={6}", ShowSort.ToString(), infoTitle, infoContent, TitleImg, Enable ? "1" : "0", userId.ToString(), infoId.ToString());
return(MysqlHelper.ExecuteNonQuery(sqlCmd) > 0);
}