public ActionResult AgeroundReport(E_StatisticsParameter eStatisticsParameter)
{
List <E_ColligateReport> itemtotallist = new List <E_ColligateReport>();
List <E_ItemTotalAvg> ageround_1_avglist = new List <E_ItemTotalAvg>();
List <E_ItemTotalAvg> ageround_2_avglist = new List <E_ItemTotalAvg>();
List <E_ItemTotalAvg> ageround_3_avglist = new List <E_ItemTotalAvg>();
if (eStatisticsParameter.issearch > 0)
{
D_Statistics dStatistics = new D_Statistics();
itemtotallist = dStatistics.GetColligateReport(eStatisticsParameter);
eStatisticsParameter.ageround = 1;
ageround_1_avglist = dStatistics.GetItemTotalAvg(eStatisticsParameter);
eStatisticsParameter.ageround = 2;
ageround_2_avglist = dStatistics.GetItemTotalAvg(eStatisticsParameter);
eStatisticsParameter.ageround = 3;
ageround_3_avglist = dStatistics.GetItemTotalAvg(eStatisticsParameter);
}
ViewBag.itemtotallist = itemtotallist;
ViewBag.ageround_1_avglist = ageround_1_avglist;
ViewBag.ageround_2_avglist = ageround_2_avglist;
ViewBag.ageround_3_avglist = ageround_3_avglist;
ViewBag.eStatisticsParameter = eStatisticsParameter;
ViewBag.arealist = dalarea.GetList(); //作业区列表
return(View("/views/statistics/ageround.cshtml"));
}
/// <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);
}
/// <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);
}
public ActionResult PersonReport(E_StatisticsParameter eStatisticsParameter)
{
List <E_PersonReport> itemtotallist = new List <E_PersonReport>();
if (eStatisticsParameter.issearch > 0)
{
D_Statistics dStatistics = new D_Statistics();
itemtotallist = dStatistics.GetPersonReport(eStatisticsParameter);
}
ViewBag.itemtotallist = itemtotallist;
ViewBag.eStatisticsParameter = eStatisticsParameter;
ViewBag.arealist = dalarea.GetList(); //作业区列表
return(View("/views/statistics/person.cshtml"));
}
public ActionResult TotalAvg(E_StatisticsParameter eStatisticsParameter)
{
List <E_ItemTotal> itemtotallist = new List <E_ItemTotal>();
List <E_ItemTotalAvg> itemtotalavglist = new List <E_ItemTotalAvg>();
if (eStatisticsParameter.issearch > 0)
{
D_Statistics dStatistics = new D_Statistics();
itemtotallist = dStatistics.GetItemTotal(eStatisticsParameter);
itemtotalavglist = dStatistics.GetItemTotalAvg(eStatisticsParameter);
}
ViewBag.itemtotallist = itemtotallist;
ViewBag.itemtotalavglist = itemtotalavglist;
ViewBag.eStatisticsParameter = eStatisticsParameter;
ViewBag.arealist = dalarea.GetList(); //作业区列表
return(View("/views/statistics/TotalAvg.cshtml"));
}
/// <summary>
/// 点评项综合统计
/// </summary>
/// <returns></returns>
public List <E_ItemTotal> GetItemTotal(E_StatisticsParameter eStatisticsParameter)
{
List <E_ItemTotal> list = new List <E_ItemTotal>();
StringBuilder strwhere = new StringBuilder();
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
typename,
title, --点评维度、点评项标题
sum(B.expect) as sumexpect, --期望值总和
sum(B.real) as sumreal, --感知值总和
(sum(B.real)-sum(B.expect)) as sumdifferent --差值
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 A.commentitemid,A.title,C.typename", strwhere.ToString());
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
list = conn.Query <E_ItemTotal>(strSql.ToString())?.ToList();
}
return(list);
}
public ActionResult SexReport(E_StatisticsParameter eStatisticsParameter)
{
List <E_ColligateReport> itemtotallist = new List <E_ColligateReport>();
List <E_ItemTotalAvg> sex_1_avglist = new List <E_ItemTotalAvg>();
List <E_ItemTotalAvg> sex_2_avglist = new List <E_ItemTotalAvg>();
if (eStatisticsParameter.issearch > 0)
{
D_Statistics dStatistics = new D_Statistics();
itemtotallist = dStatistics.GetColligateReport(eStatisticsParameter);
eStatisticsParameter.sex = 1;
sex_1_avglist = dStatistics.GetItemTotalAvg(eStatisticsParameter);
eStatisticsParameter.sex = 2;
sex_2_avglist = dStatistics.GetItemTotalAvg(eStatisticsParameter);
}
ViewBag.itemtotallist = itemtotallist;
ViewBag.sex_1_avglist = sex_1_avglist;
ViewBag.sex_2_avglist = sex_2_avglist;
ViewBag.eStatisticsParameter = eStatisticsParameter;
ViewBag.arealist = dalarea.GetList(); //作业区列表
return(View("/views/statistics/sex.cshtml"));
}
/// <summary>
/// 综合统计
/// </summary>
/// <param name="eStatisticsParameter"></param>
/// <returns></returns>
public List <E_ColligateReport> GetColligateReport(E_StatisticsParameter eStatisticsParameter)
{
List <E_ColligateReport> list = new List <E_ColligateReport>();
StringBuilder strwhere = new StringBuilder();
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
*,
(sex_1_real-sex_1_expect) as sex_1_different, --男差值(P-E)
(sex_2_real-sex_2_expect) as sex_2_different, --女差值（P-E）
(ageround_1_real-ageround_1_expect) as ageround_1_different, --18-30岁 差值(P-E)
(ageround_2_real-ageround_2_expect) as ageround_2_different, --30-45岁 差值（P-E）
(ageround_3_real-ageround_3_expect) as ageround_3_different, --45岁以上 差值（P-E）
(domain_1_real-domain_1_expect) as domain_1_different, --南方 差值（P-E）
(domain_2_real-domain_2_expect) as domain_2_different, --北方 差值（P-E）
(job_1_real-job_1_expect) as job_1_different, --定员 差值（P-E）
(job_2_real-job_2_expect) as job_2_different --访客 差值（P-E）
from
(
select
typename,
title,
sum(case when D.sex=1 then B.expect else 0 end) as sex_1_expect, --男，期望值（E）
sum(case when D.sex=2 then B.expect else 0 end) as sex_2_expect, --女，期望值（E）
sum(case when D.sex=1 then B.real else 0 end) as sex_1_real, --男，感知值（P）
sum(case when D.sex=2 then B.real else 0 end) as sex_2_real, --女，感知值（P）
sum(case when D.ageround=1 then B.expect else 0 end) as ageround_1_expect, --18-30岁，期望值（E）
sum(case when D.ageround=2 then B.expect else 0 end) as ageround_2_expect, --30-45岁，期望值（E）
sum(case when D.ageround=3 then B.expect else 0 end) as ageround_3_expect, --45岁以上，期望值（E）
sum(case when D.ageround=1 then B.real else 0 end) as ageround_1_real, --18-30岁，感知值（P）
sum(case when D.ageround=2 then B.real else 0 end) as ageround_2_real, --30-45岁，感知值（P）
sum(case when D.ageround=3 then B.real else 0 end) as ageround_3_real, --45岁以上，感知值（P）
sum(case when D.domain=1 then B.expect else 0 end) as domain_1_expect, --南方，期望值（E）
sum(case when D.domain=2 then B.expect else 0 end) as domain_2_expect, --北方，期望值（E）
sum(case when D.domain=1 then B.real else 0 end) as domain_1_real, --南方，感知值（P）
sum(case when D.domain=2 then B.real else 0 end) as domain_2_real, --北方，感知值（P）
sum(case when D.job=1 then B.expect else 0 end) as job_1_expect, --定员，期望值（E）
sum(case when D.job=2 then B.expect else 0 end) as job_2_expect, --访客，期望值（E）
sum(case when D.job=1 then B.real else 0 end) as job_1_real, --定员，感知值（P）
sum(case when D.job=2 then B.real else 0 end) as job_2_real --访客，感知值（P）
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 A.commentitemid,A.title,C.typename
) as T", strwhere.ToString());
using (IDbConnection conn = new SqlConnection(DapperHelper.GetConStr()))
{
list = conn.Query <E_ColligateReport>(strSql.ToString())?.ToList();
}
return(list);
}
