/// <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); } } }