public List <dynamic> GetAllPersonalPic(PicManage picManage, PageModel pm)
{
StringBuilder sbSql = new StringBuilder(@"SELECT * FROM dbo.Image WHERE UserCode=@UserCode");
var dyParamter = new DynamicParameters();
dyParamter.Add("UserCode", picManage.UserCode);
dyParamter.Add("PageIndex", pm.PageIndex);
dyParamter.Add("PageSize", pm.PageSize);
string querySql = string.Format("WITH query AS ({0}) ", sbSql);
string countSql = querySql + " SELECT COUNT(*) FROM query";
using (var conn = AdoConfig.GetDBConnection())
{
try
{
pm.TotalCount = conn.Query <int>(countSql, dyParamter).FirstOrDefault();
if (pm.TotalCount > 0)
{
string pageSql = querySql + " SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY IsDel ASC) AS RowNum, * FROM query ) t WHERE t.RowNum > (@PageIndex -1) * @PageSize AND t.RowNum <= @PageIndex * @PageSize";
return(conn.Query <dynamic>(pageSql, dyParamter).ToList());
}
}
catch (Exception ex)
{
new SendWarnningEMail().SendEMail(ex.ToString());
}
return(new List <dynamic>());
}
}
public int UpdateArticle(Article article, UserInfoForCookie user)
{
StringBuilder sbsql = new StringBuilder(@"UPDATE dbo.Article
SET ");
var dyParamter = new DynamicParameters();
dyParamter.Add("UserCode", user.UserCode);
dyParamter.Add("ArticleNo", article.ArticleNo);
if (article.Title != null)
{
sbsql.Append("Title=@Title");
dyParamter.Add("Title", article.Title);
}
if (article.Content != null)
{
sbsql.Append("Content=@Content");
dyParamter.Add("Content", article.Content);
}
if (article.ImageUrl != null)
{
sbsql.Append("ImageUrl=@ImageUrl");
dyParamter.Add("ImageUrl", article.ImageUrl);
}
sbsql.Append(@"
Modifier = @UserCode ,
ModDate = GETDATE()
WHERE ArticleNo = @ArticleNo");
using (var conn = AdoConfig.GetDBConnection())
{
return(conn.Execute(sbsql.ToString(), dyParamter));
}
}
/// <summary>
/// 判断该用户名是否被占用
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public bool GetUserByName(string name)
{
string sql = @"SELECT COUNT(*)
FROM dbo.[User]
WHERE UserName = @UserName
AND IsDel = 0;";
using (var conn = AdoConfig.GetDBConnection())
{
return(conn.Query(sql, new { UserName = name }).FirstOrDefault() > 0 ? true : false);
}
}
/// <summary>
/// 判断该邮箱是否已经注册
/// </summary>
/// <param name="e_mail"></param>
/// <returns></returns>
public bool GetUserByEmail(string e_mail)
{
string sql = @"SELECT COUNT(*)
FROM dbo.[User]
WHERE E_Mail = @E_Mail
AND IsDel = 0;";
using (var conn = AdoConfig.GetDBConnection())
{
return(conn.Query(sql, new { E_Mail = e_mail }).FirstOrDefault() > 0 ? true : false);
}
}
/// <summary>
/// 修改用户名
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public int ModifyUserName(U_User user)
{
string sql = @"UPDATE dbo.[User]
SET UserName = @UserName
WHERE UserCode = @UserCode
AND IsDel = 0";
using (var conn = AdoConfig.GetDBConnection())
{
return(conn.Execute(sql, user));
}
}
/// <summary>
/// 重置密码
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public int ResetUserPassword(U_User user)
{
string sql = @"UPDATE dbo.[User]
SET PassWord = @PassWord ,
ModDate = GETDATE()
WHERE E_Mail = @E_Mail
AND IsDel = 0";
using (var conn = AdoConfig.GetDBConnection())
{
return(conn.Execute(sql, user));
}
}
public int DeleteArticle(Article article, UserInfoForCookie user)
{
string sql = @"UPDATE dbo.Article
SET IsDel = 1 ,
Modifier = @UserCode ,
ModDate = GETDATE()
WHERE ArticleNo = @ArticleNo;";
using (var conn = AdoConfig.GetDBConnection())
{
return(conn.Execute(sql, new { article.ArticleNo, user.UserCode }));
}
}
public int SavePicToDB(UserImage userImage)
{
string sql = @"UPDATE dbo.Image
SET IsDel = 1,ModDate = GETDATE()
WHERE UserCode = @UserCode AND IsDel=0";
sql += @"UPDATE dbo.Image
SET IsDel = 0,ModDate = GETDATE()
WHERE UserCode = @UserCode AND ImageCode=@ImageCode
;";
using (var conn = AdoConfig.GetDBConnection())
{
return(conn.Execute(sql, new { ImageCode = userImage.ImageCode, ImageSrc = userImage.ImageSrc, UserCode = userImage.UserCode, }));
}
}
public int Register(U_User user)
{
string sql = @"INSERT dbo.[User]
( UserName ,
PassWord ,
UserCode ,
E_Mail
)
VALUES ( @UserName ,
@PassWord ,
@UserCode ,
@E_Mail
)";
using (var conn = AdoConfig.GetDBConnection())
{
return(conn.Execute(sql, user));
}
}
public U_User UserLogin(U_User user)
{
string sql = @"
SELECT U.* ,
I.ImageSrc AS ImageUrl
FROM dbo.[User] U
LEFT JOIN dbo.Image I ON I.UserCode = U.UserCode
WHERE ( UserName = @UserName
OR E_Mail = @E_Mail
)
AND PassWord = @PassWord
AND U.IsDel = 0
AND I.IsDel = 0;";
using (var conn = AdoConfig.GetDBConnection())
{
return(conn.Query <U_User>(sql, new { user.UserName, user.PassWord, user.E_Mail }).FirstOrDefault());
}
}
public List <dynamic> GetFollowMessage(PageModel pm)
{
StringBuilder sbSql = new StringBuilder(@"SELECT TOP 2000
pro.PropertyID ,
pro.PropertyCode ,
fol.FollowID ,
fol.FollowCode ,
fol.FollowDate ,
fol.FollowType ,
fol.DeptName + '.' + fol.EmpName AS EmpName ,
pro.PropertyNo ,
est.EstateName ,
pro.TradeInt ,
fol.Content ,
fol.AlertDate ,
fol.AlertInfo ,
fol.AlertType ,
fol.FlagAlerted
FROM Property AS pro ( NOLOCK )
LEFT JOIN Follow AS fol ( NOLOCK ) ON fol.PropertyCode = pro.PropertyCode
LEFT JOIN Estate AS est ( NOLOCK ) ON pro.EstateCode = est.EstateCode
WHERE fol.FlagDeleted = 0
AND fol.FlagTrashed = 0
AND pro.IsDel = 0
");
var dyParamter = new DynamicParameters();
dyParamter.Add("PageIndex", pm.PageIndex);
dyParamter.Add("PageSize", pm.PageSize);
string querySql = string.Format("WITH query AS ({0}) ", sbSql);
string countSql = querySql + " SELECT COUNT(*) FROM query";
using (var conn = AdoConfig.GetDBConnection())
{
pm.TotalCount = conn.Query <int>(countSql, dyParamter).FirstOrDefault();
if (pm.TotalCount > 0)
{
string pageSql = querySql + " SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY FollowID ASC) AS RowNum, * FROM query ) t WHERE t.RowNum > (@PageIndex -1) * @PageSize AND t.RowNum <= @PageIndex * @PageSize";
return(conn.Query <dynamic>(pageSql, dyParamter).ToList());
}
return(new List <dynamic>());
}
}
public void SavePicToDB(string UserCode, List <string> urlList)
{
StringBuilder InsertSql = new StringBuilder();
DynamicParameters dyParameters = new DynamicParameters();
dyParameters.Add("UserCode", UserCode);
for (int i = 0; i < urlList.Count; i++)
{
InsertSql.AppendFormat(@"INSERT dbo.Image
( ImageCode, ImageSrc, UserCode,IsDel )
VALUES ( @ImageCode{0}, @ImageSrc{0}, @UserCode,1 );", i);
dyParameters.Add("ImageSrc" + i, urlList[i]);
dyParameters.Add("ImageCode" + i, new CreateEmpCode().GetRandomEmpCode(2, 4));
}
using (var conn = AdoConfig.GetDBConnection())
{
conn.Execute(InsertSql.ToString(), dyParameters);
}
}
public int InsertArticle(Article article, UserInfoForCookie user)
{
string sql = @" INSERT dbo.Article
( Title ,
ArticleNo,
Creator ,
CreateDate ,
Content ,
ImageUrl
)
VALUES ( @Title ,
@ArticleNo,
@UserCode ,
GETDATE() ,
@Content ,
@ImageUrl
)";
using (var conn = AdoConfig.GetDBConnection())
{
return(conn.Execute(sql, new { article.Title, article.ArticleNo, article.Content, article.ImageUrl, user.UserCode }));
}
}
public List <dynamic> GetUserArticle(ArticleModel article, PageModel pm)
{
// string sql = @"SELECT * ,
// SrcString = STUFF(( SELECT ',' + img.ImageSrc
// FROM dbo.Image img
// WHERE img.ImageCode = art.ImageCode
// FOR
// XML PATH('')
// ), 1, 1, '')
//FROM dbo.Article art;";
// StringBuilder sbSql = new StringBuilder(@"SELECT A.* ,
// I.ImageSrc
//FROM dbo.Article A
// LEFT JOIN dbo.Image I
// ON I.ImageCode = A.ImageCode
//WHERE A.IsDel = 0");
StringBuilder sbSql = new StringBuilder(@"SELECT A.*
FROM dbo.Article A
");
var dyParamter = new DynamicParameters();
dyParamter.Add("PageIndex", pm.PageIndex);
dyParamter.Add("PageSize", pm.PageSize);
string querySql = string.Format("WITH query AS ({0}) ", sbSql);
string countSql = querySql + " SELECT COUNT(*) FROM query";
using (var conn = AdoConfig.GetDBConnection())
{
pm.TotalCount = conn.Query <int>(countSql, dyParamter).FirstOrDefault();
if (pm.TotalCount > 0)
{
string pageSql = querySql + " SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS RowNum, * FROM query ) t WHERE t.RowNum > (@PageIndex -1) * @PageSize AND t.RowNum <= @PageIndex * @PageSize";
return(conn.Query <dynamic>(pageSql, dyParamter).ToList());
}
return(new List <dynamic>());
}
}