/// <summary>
/// 评价
/// </summary>
/// <returns></returns>
public bool SumLike(E_Person model)
{
StringBuilder strSql = new StringBuilder();
StringBuilder setSql = new StringBuilder();
if (model.perfect != null && model.perfect > 0)
{
setSql.Append("perfect=perfect+1,");
}
if (model.good != null && model.good > 0)
{
setSql.Append("good=good+1,");
}
if (model.bad != null && model.bad > 0)
{
setSql.Append("bad=bad+1,");
}
strSql.Append("update dp_person set " + setSql.ToString().TrimEnd(',') + " where personid=@personid ");
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
int count = conn.Execute(strSql.ToString(), model);
if (count > 0)//如果更新失败
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 更新
/// </summary>
public bool Update(E_User model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"UPDATE [comment].[dbo].[dp_user]
SET [realname] = @realname
,[username] = @username
,[password] = @password
,[areaid] = @areaid
,[classinfoid] = @classinfoid
,[updatetime] = @updatetime
WHERE userid=@userid");
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
int count = conn.Execute(strSql.ToString(), model);
if (count > 0)//如果更新失败
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 人员统计
/// </summary>
/// <param name="eStatisticsParameter"></param>
/// <returns></returns>
public List <E_PersonReport> GetPersonReport(E_StatisticsParameter eStatisticsParameter)
{
List <E_PersonReport> list = new List <E_PersonReport>();
StringBuilder strwhere = new StringBuilder();
if (eStatisticsParameter.areaid > 0)
{
strwhere.AddWhere($"A.areaid={eStatisticsParameter.areaid}");
}
if (eStatisticsParameter.classinfoid > 0)
{
strwhere.AddWhere($"A.classinfoid={eStatisticsParameter.classinfoid}");
}
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"select
*,
case when total>0 then cast(cast(perfect as float)/cast(total as float) as decimal(18,2)) else 0 end as perfectrate, --好评率
case when total>0 then cast(cast(bad as float)/cast(total as float) as decimal(18,2)) else 0 end as badrate --差评率
from
(
select A.*,(perfect+good+bad) as total,B.CName as classname from dp_person as A
left join ClassInfo as B on A.classinfoid=B.id {0}
) as T", strwhere.ToString());
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
list = conn.Query <E_PersonReport>(strSql.ToString())?.ToList();
}
return(list);
}
public bool UpdateHits(E_Food model)
{
StringBuilder strSql = new StringBuilder();
StringBuilder setSql = new StringBuilder();
if (model.praise > 0)
{
setSql.Append("praise=praise+1");
}
if (model.bad > 0)
{
setSql.Append("bad=bad+1");
}
strSql.Append("update dp_food set " + setSql + " where foodid=@foodid ");
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
int count = conn.Execute(strSql.ToString(), model);
if (count > 0)//如果更新失败
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
public List <E_Opinion> GetList(E_Opinion model, ref int total)
{
if (model.PageIndex <= 0)
{
model.PageIndex = 1;
}
List <E_Opinion> list;
StringBuilder strSql = new StringBuilder();
StringBuilder whereSql = new StringBuilder(" where isdelete=0 ");
strSql.Append(" select ROW_NUMBER() OVER ( ORDER BY opinionid desc) AS RID, * from dp_opinion ");
if (model.opiniontypeid > 0)
{
whereSql.Append(" and opiniontypeid=@opiniontypeid");
}
strSql.Append(whereSql);
string CountSql = "SELECT COUNT(1) as RowsCount FROM (" + strSql.ToString() + ") AS CountList";
string pageSqlStr = "select * from ( " + strSql.ToString() + " ) as Temp_PageData where Temp_PageData.RID BETWEEN {0} AND {1}";
pageSqlStr = string.Format(pageSqlStr, (model.PageSize * (model.PageIndex - 1) + 1).ToString(), (model.PageSize * model.PageIndex).ToString());
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
list = conn.Query <E_Opinion>(pageSqlStr, model)?.ToList();
total = conn.ExecuteScalar <int>(CountSql, model);
}
return(list);
}
/// <summary>
/// 点评项综合平均统计
/// </summary>
/// <returns></returns>
public List <E_ItemTotalAvg> GetItemTotalAvg(E_StatisticsParameter eStatisticsParameter)
{
List <E_ItemTotalAvg> list = new List <E_ItemTotalAvg>();
StringBuilder strwhere = new StringBuilder();
if (eStatisticsParameter.sex > 0)
{
strwhere.AddWhere($"D.sex={eStatisticsParameter.sex}");
}
if (eStatisticsParameter.ageround > 0)
{
strwhere.AddWhere($"D.ageround={eStatisticsParameter.ageround}");
}
if (eStatisticsParameter.domain > 0)
{
strwhere.AddWhere($"D.domain={eStatisticsParameter.domain}");
}
if (eStatisticsParameter.job > 0)
{
strwhere.AddWhere($"D.job={eStatisticsParameter.job}");
}
if (eStatisticsParameter.areaid > 0)
{
strwhere.AddWhere($"D.areaid={eStatisticsParameter.areaid}");
}
if (eStatisticsParameter.classinfoid > 0)
{
strwhere.AddWhere($"D.classinfoid={eStatisticsParameter.classinfoid}");
}
if (eStatisticsParameter.starttime != null)
{
strwhere.AddWhere($"D.addtime>cast('{Convert.ToDateTime(eStatisticsParameter.starttime).ToString("yyyy-MM-dd")}' as datetime)");
}
if (eStatisticsParameter.endtime != null)
{
strwhere.AddWhere($"D.addtime<cast('{Convert.ToDateTime(eStatisticsParameter.endtime).ToString("yyyy-MM-dd")}' as datetime)");
}
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat(@"select
C.typename, --点评维度
cast(avg(cast(B.expect as float)) as decimal(18,2)) as avgexpect, --期望均值(E)
cast(avg(cast(B.real as float)) as decimal(18,2)) as avgreal, --感知均值(P)
cast(avg(cast(B.real as float))-avg(cast(B.expect as float))as decimal(18,2)) as avgdifferent, --差值（P-E）均值
cast((avg(cast(B.real as float))-avg(cast(B.expect as float)))/avg(cast(B.expect as float))as decimal(18,2)) as expectdeviation, --期望偏离度（（P-E）/E*100%）
cast((avg(cast(B.real as float))-avg(cast(B.expect as float)))/avg(cast(B.expect as float))+1 as decimal(18,2)) as satisfaction --顾客满意度（期望偏离度+100%）
from dp_commentitem as A
left join dp_commentcommentitem as B on A.commentitemid=B.commentitemid
left join dp_commenttype as C on A.commenttypeid=C.commenttypeid
left join dp_comment as D on D.commentid=B.commentid {0}
group by C.commenttypeid,C.typename", strwhere.ToString());
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
list = conn.Query <E_ItemTotalAvg>(strSql.ToString())?.ToList();
}
return(list);
}
/// <summary>
/// 添加
/// </summary>
public bool Add(E_CommentCommentItem model)
{
string sql = @"INSERT INTO [comment].[dbo].[dp_commentcommentitem]([commentid],[commentitemid],[expect],[real])
VALUES (@commentid,@commentitemid,@expect,@real);";
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
return(conn.Execute(sql, model) > 0);
}
}
/// <summary>
/// 添加
/// </summary>
public bool Add(E_CommentOpinion model)
{
string sql = @"INSERT INTO [comment].[dbo].[dp_commentopinion]([commentid],[opinionid])
VALUES(@commentid,@opinionid);";
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
return(conn.Execute(sql, model) > 0);
}
}
/// <summary>
/// 添加
/// </summary>
public int Add(E_Comment model)
{
string sql = @"INSERT INTO [comment].[dbo].[dp_comment]([userid],[areaid],[classinfoid],[sex],[ageround],[domain],[job],[contents],[addtime])
VALUES(@userid,@areaid,@classinfoid,@sex,@ageround,@domain,@job,@contents,@addtime);SELECT @@IDENTITY;";
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
return(Convert.ToInt32(conn.ExecuteScalar(sql, model)));
}
}
/// <summary>
/// 数量
/// </summary>
public int GetCount()
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from dp_user with (nolock)");
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
return((int)conn.ExecuteScalar(strSql.ToString()));
}
return(0);
}
/// <summary>
/// 查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
public E_Person GetInfoById(E_Person model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from dp_person where personid=@personid");
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
model = conn.Query <E_Person>(strSql.ToString(), model)?.FirstOrDefault();
}
return(model);
}
/// <summary>
/// 查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
public E_CommentItem GetInfoById(E_CommentItem model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from dp_commentitem where commentitemid=@commentitemid");
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
model = conn.Query <E_CommentItem>(strSql.ToString(), model)?.FirstOrDefault();
}
return(model);
}
/// <summary>
/// 查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
public List <dp_themeopinion> GetList(dp_themeopinion model)
{
List <dp_themeopinion> list;
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from dp_themeopinion where themeid=@themeid");
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
list = conn.Query <dp_themeopinion>(strSql.ToString(), model)?.ToList();
}
return(list);
}
/// <summary>
/// 查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
public List <E_CommentType> GetList()
{
List <E_CommentType> list;
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from dp_commenttype where isdelete=0");
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
list = conn.Query <E_CommentType>(strSql.ToString())?.ToList();
}
return(list);
}
/// <summary>
/// 添加
/// </summary>
public int Add(E_Impdish model)
{
string sql = @"INSERT INTO tb_impdish
([dishname],[caix],[weix],[diz],[prjf],[pic],[zhul],[ful],[tiaol],[pengrjf],[jishuyd],[newpic])
VALUES
(@dishname,@caix,@weix,@diz,@prjf,@pic,@zhul,@ful,@tiaol,@pengrjf,@jishuyd,@newpic);select @@IDENTITY;";
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
return(Convert.ToInt32(conn.ExecuteScalar(sql, model)));
}
}
/// <summary>
/// 查询
/// </summary>
public List <E_Area> GetList()
{
List <E_Area> list;
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from area");
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
list = conn.Query <E_Area>(strSql.ToString())?.ToList();
}
return(list);
}
/// <summary>
/// 查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <returns></returns>
public List <E_Food> GetList(E_Food model, ref int total)
{
if (model.PageIndex <= 0)
{
model.PageIndex = 1;
}
List <E_Food> list;
StringBuilder strSql = new StringBuilder();
StringBuilder whereSql = new StringBuilder(" where 1=1 ");
strSql.Append(" select ROW_NUMBER() OVER ( ORDER BY UpdateTime desc) AS RID, * from dp_food ");
if (model.areaid > 0)
{
whereSql.Append(" and areaid=@areaid");
}
if (model.classinfoid > 0)
{
whereSql.Append(" and classinfoid=@classinfoid");
}
if (!String.IsNullOrEmpty(model.foodname))
{
whereSql.Append(" and foodname like '%'+@foodname +'%'");
}
if (!String.IsNullOrEmpty(model.pname))
{
whereSql.Append(" and pname like '%'+@pname +'%'");
}
if (model.addtime != null)
{
whereSql.Append(" and addtime=@addtime");
}
if (model.startaddtime != null)
{
whereSql.Append(" and addtime>=@startaddtime");
}
strSql.Append(whereSql);
string CountSql = "SELECT COUNT(1) as RowsCount FROM (" + strSql.ToString() + ") AS CountList";
string pageSqlStr = "select * from ( " + strSql.ToString() + " ) as Temp_PageData where Temp_PageData.RID BETWEEN {0} AND {1}";
pageSqlStr = string.Format(pageSqlStr, (model.PageSize * (model.PageIndex - 1) + 1).ToString(), (model.PageSize * model.PageIndex).ToString());
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
list = conn.Query <E_Food>(pageSqlStr, model)?.ToList();
total = conn.ExecuteScalar <int>(CountSql, model);
}
return(list);
}
/// <summary>
/// 查询
/// </summary>
public E_User GetInfoByName(E_User model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select *,Name as areaname,CName as classinfoname from (
select * from dp_user where username =@username and password=@password) a
left join dbo.Area b on a.areaid = b.id
left join dbo.ClassInfo c on a.classinfoid = c.id");
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
model = conn.Query <E_User>(strSql.ToString(), model)?.FirstOrDefault();
}
return(model);
}
public E_Food GetInfoByDishId(E_Food model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from dp_food where dishid=@dishid ");
if (model.startaddtime != null)
{
strSql.Append(" and addtime>=@startaddtime");
}
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
model = conn.Query <E_Food>(strSql.ToString(), model)?.FirstOrDefault();
}
return(model);
}
/// <summary>
/// 依据主题ID 查询对应的点评项
/// </summary>
/// <param name="themeid">主题ID</param>
/// <returns></returns>
public List <E_CommentItem> GetCommentItemByThemeID(int themeid)
{
List <E_CommentItem> list;
StringBuilder strSql = new StringBuilder();
strSql.Append($@"select B.* from (
select * from dp_themecommentitem where themeid={themeid}
) as A inner join dp_commentitem as B on A.commentitemid=B.commentitemid
where B.isdelete=0");
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
list = conn.Query <E_CommentItem>(strSql.ToString())?.ToList();
}
return(list);
}
/// <summary>
/// 查询
/// </summary>
public List <E_ClassInfo> GetList(E_ClassInfo model)
{
List <E_ClassInfo> list;
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from classinfo where 1=1");
if (model.areaid > 0)
{
strSql.Append(" and areaid=@areaid ");
}
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
list = conn.Query <E_ClassInfo>(strSql.ToString(), model)?.ToList();
}
return(list);
}
/// <summary>
/// 添加
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Add(E_Food model)
{
string sql = "INSERT INTO dp_food(areaid, classinfoid,dishid, foodname,pid, pname, pic,updatetime,addtime) VALUES (@areaid, @classinfoid,@dishid, @foodname,@pid, @pname, @pic,getdate(),getdate())";
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
int count = conn.Execute(sql, model);
if (count > 0)//如果更新失败
{
return(true);
}
else
{
return(false);
}
}
}
public bool Add(dp_themeopinion model)
{
string sql = "INSERT INTO dp_themeopinion(themeid,opinionid) VALUES (@themeid,@opinionid)";
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
int count = conn.Execute(sql, model);
if (count > 0)//如果更新失败
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 添加
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Add(E_CommentItem model)
{
string sql = "INSERT INTO dp_commentitem(commenttypeid,[title]) VALUES (@commenttypeid,@title)";
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
int count = conn.Execute(sql, model);
if (count > 0)//如果更新失败
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 添加
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Add(E_Person model)
{
string sql = "INSERT INTO dp_person(areaid, classinfoid, personname, jobtypeid) VALUES (@areaid, @classinfoid, @personname, @jobtypeid)";
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
int count = conn.Execute(sql, model);
if (count > 0)//如果更新失败
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 查询
/// </summary>
public List <E_User> GetList(E_User model)
{
List <E_User> list;
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select * from
(
select row_number()over(order by userid asc) as rowid, dp_user.*, Area.Name as areaname, ClassInfo.CName as classinfoname from dp_user with (nolock)
left join dbo.Area on dp_user.areaid = dbo.Area.id
left join dbo.ClassInfo on dp_user.classinfoid = dbo.ClassInfo.id
) as T where t.rowid between @pageindex*@pagesize and (@pageindex+1)*@pagesize");
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
list = conn.Query <E_User>(strSql.ToString(), model)?.ToList();
}
return(list);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool DeleteById(dp_themeopinion model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete dp_themeopinion where themeid=@themeid ");
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
int count = conn.Execute(strSql.ToString(), model);
if (count > 0)//如果更新失败
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 删除
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool DeleteById(E_CommentItem model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update dp_commentitem set isdelete=1 where commentitemid=@commentitemid ");
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
int count = conn.Execute(strSql.ToString(), model);
if (count > 0)//如果更新失败
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 更新
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Update(E_Person model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update dp_person set areaid=@areaid, classinfoid=@classinfoid, personname=@personname, jobtypeid=@jobtypeid where personid=@personid ");
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
int count = conn.Execute(strSql.ToString(), model);
if (count > 0)//如果更新失败
{
return(true);
}
else
{
return(false);
}
}
}
/// <summary>
/// 隐藏
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool DisplayById(E_Food model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update dp_food set isdisplay=@isdisplay where foodid=@foodid ");
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
int count = conn.Execute(strSql.ToString(), model);
if (count > 0)//如果更新失败
{
return(true);
}
else
{
return(false);
}
}
}