public ActionResult Create(SiteViewModel model)
{
if (ModelState.IsValid)
{
SiteCoord sc = Maps.GetLocForAddress(model.Address);
string region = Maps.GetRegion(sc);
//Mapper.Map<Site>(model);
Site s = new Site()
{
Address = model.Address,
Height = model.Height
};
try
{
DesignWindCalculation calc = new DesignWindCalculation();
double designSpeed = calc.For(s);
SiteSurvey survey = new SiteSurvey();
survey = calc.Result(ref s);
} catch (Exception e)
{
}
db.Sites.Add(s);
db.SaveChanges();
return(RedirectToAction("Edit", new { id = s.Id }));
}
return(View(model));
}
public async Task <IActionResult> EditSiteSurvey(int id, [Bind("Id,SiteSurveyNumber,Description,Scheduled,Started,Ended,Created,ApplicationUserId,IncidentId,FloorOneDescription,ImageModelId,ImageModelId1,ImageModelId2,ImageModelId3,ImageModelId4,FloorTwoDescription,ImageModelId5,ImageModelId6,ImageModelId7,ImageModelId8,ImageModelId9,FloorThreeDescription,ImageModelId10,ImageModelId11,ImageModelId12,ImageModelId13,ImageModelId14,FloorFourDescription,ImageModelId15,ImageModelId16,ImageModelId17,ImageModelId18,ImageModelId19,FloorFiveDescription,ImageModelId20,ImageModelId21,ImageModelId22,ImageModelId23,ImageModelId24,SiteSurveyStatusId")] SiteSurvey siteSurvey)
{
if (id != siteSurvey.Id)
{
return(NotFound());
}
if (ModelState.IsValid)
{
try
{
_context.Update(siteSurvey);
await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!SiteSurveyExists(siteSurvey.Id))
{
return(NotFound());
}
else
{
throw;
}
}
return(RedirectToAction(nameof(IndexSearchSiteSurveys)));
}
ViewData["ApplicationUserId"] = new SelectList(_context.Users, "Id", "FullName", siteSurvey.ApplicationUserId);
ViewData["ImageModelId"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId);
ViewData["ImageModelId9"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId9);
ViewData["ImageModelId10"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId10);
ViewData["ImageModelId11"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId11);
ViewData["ImageModelId12"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId12);
ViewData["ImageModelId13"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId13);
ViewData["ImageModelId14"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId14);
ViewData["ImageModelId15"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId15);
ViewData["ImageModelId16"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId16);
ViewData["ImageModelId17"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId17);
ViewData["ImageModelId18"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId18);
ViewData["ImageModelId1"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId1);
ViewData["ImageModelId19"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId19);
ViewData["ImageModelId20"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId20);
ViewData["ImageModelId21"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId21);
ViewData["ImageModelId22"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId22);
ViewData["ImageModelId23"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId23);
ViewData["ImageModelId24"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId24);
ViewData["ImageModelId2"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId2);
ViewData["ImageModelId3"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId3);
ViewData["ImageModelId4"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId4);
ViewData["ImageModelId5"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId5);
ViewData["ImageModelId6"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId6);
ViewData["ImageModelId7"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId7);
ViewData["ImageModelId8"] = new SelectList(_context.Images, "ImageId", "Title", siteSurvey.ImageModelId8);
ViewData["IncidentId"] = new SelectList(_context.Incident, "Id", "IncidentNumber", siteSurvey.IncidentId);
ViewData["SiteSurveyStatusId"] = new SelectList(_context.Set <SiteSurveyStatus>(), "Id", "SiteSurveyStatusName", siteSurvey.SiteSurveyStatusId);
return(View(siteSurvey));
}
/// <summary>
/// 问卷调查明细
/// </summary>
/// <param name="siteGuid"></param>
/// <returns></returns>
public List <SiteSurvey> SiteSurveyDetails(string siteGuid, string filterDate)
{
try
{
// 必填项
string strSql = string.Format("SELECT DISTINCT A2.SORT,A2.ITEMNAME AS SURVEYITEM,A1.CREATETIME,A1.HEADGUID,A1.LINEGUID,A1.USERANSWER,A1.CREATETIME,A1.CREATEUSER AS CREATEUSERID,A2.DISPLAYZONE,A2.ITEMSTYLE "
+ "FROM TBLSURVEYRESPONSEDETAILS (NOLOCK) A1,TBLSURVEYLINESDEF (NOLOCK) A2,TBLSURVEYHEADDEF (NOLOCK) A3 "
+ "WHERE A2.DELETETIME IS NULL AND A1.LINEGUID = A2.LINEGUID AND A3.HEADGUID = A2.HEADGUID "
+ "AND A1.CREATEUSER<>'' AND A1.SITEGUID in ({0}) {1} "
+ "ORDER BY A1.CREATEUSER, A1.CREATETIME, A2.SORT ", siteGuid, filterDate);
// 微信用户账号列表
string userSql = string.Format("SELECT CONVERT(VARCHAR,A1.USERID) AS CREATEUSERID,A1.WECHATID,A2.CREATETIME LOGINTIME "
+ "FROM TBLUSER A1 LEFT JOIN(SELECT USERID,MAX(CREATETIME) CREATETIME FROM TBLUSERLOG GROUP BY USERID) A2 ON A1.USERID=A2.USERID ");
//问卷调研明细（必填项）
List <SiteSurvey> SiteSurveyDetails = SqlServerHelper.GetEntityList <SiteSurvey>(SqlServerHelper.salesorderConn(), strSql);
//微信用户信息表
List <SiteSurvey> UserDetails = SqlServerHelper.GetEntityList <SiteSurvey>(SqlServerHelper.sfeed(), userSql);
if ((SiteSurveyDetails == null || !SiteSurveyDetails.Any()) || (UserDetails == null || !UserDetails.Any()))
{
return(null);
}
//按用户对调研明细分组
SiteSurveyDetails = SiteSurveyDetails.GroupJoin(UserDetails,
a => new { createUserID = a.createUserID }, b => new { createUserID = b.createUserID },
(a, b) =>
{
a.wechatID = b.FirstOrDefault().wechatID;
a.loginTime = b.FirstOrDefault().loginTime < a.createTime ? a.createTime : b.FirstOrDefault().loginTime;
return(a);
}).GroupBy(dr =>
new
{
createUserID = dr.createUserID,
headGuid = dr.headGuid,
}).Select(q =>
{
var details = q.Where(s => s.createUserID == q.Key.createUserID && s.headGuid == q.Key.headGuid).ToList <object>();
SiteSurvey data = new SiteSurvey()
{
headGuid = q.Key.headGuid,
createUserID = q.Key.createUserID,
createTime = q.FirstOrDefault().createTime,
wechatID = q.FirstOrDefault().wechatID
};
data.surveyDetails = details;
return(data);
}).OrderByDescending(dr => dr.createTime).ToList();
return(SiteSurveyDetails);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 获取问卷调研反馈汇总
/// </summary>
/// <param name="param"></param>
/// <returns></returns>
public List <SiteSurvey> SiteSurveySummary(Dictionary <string, dynamic> param)
{
try
{
string filterDate = string.Empty;
if (param.ContainsKey("startDate") && param.ContainsKey("endDate"))
{
filterDate = string.Format("AND CONVERT(VARCHAR(10),A1.CREATETIME,23) BETWEEN '{0}' AND '{1}'",
string.IsNullOrWhiteSpace(param["startDate"])? "2000-01-01": param["startDate"],
string.IsNullOrWhiteSpace(param["endDate"])?"2099-12-31":param["endDate"]);
}
//问卷发起BU和Site
string buSql = string.Format("SELECT GUID AS SITEGUID,BUGUID AS COMPANYGUID FROM TBLSITE WHERE GUID='{0}' OR BUGUID='{0}'", (string)param["siteGuid"]);
//问卷汇总
string strSql = string.Format("SELECT DISTINCT A1.SITEGUID,A3.BUGUID AS COMPANYGUID, "
//+ "SUBSTRING(A1.USERANSWER,CHARINDEX('、',A1.USERANSWER)+1,LEN(A1.USERANSWER)-CHARINDEX('、',A1.USERANSWER)) AS USERANSWER, "
+ "A1.USERANSWER, "
+ "A1.USERANSWER,COUNT(A1.USERANSWER) AS AMOUNT ,A2.DISPLAYZONE "
+ "FROM TBLSURVEYRESPONSEDETAILS (NOLOCK) A1,TBLSURVEYLINESDEF (NOLOCK) A2,TBLSURVEYHEADDEF (NOLOCK) A3 "
+ "WHERE A2.DELETETIME IS NULL AND A1.LINEGUID=A2.LINEGUID AND A3.HEADGUID=A2.HEADGUID "
+ " {0}"
+ "AND A2.ALLOWNULL=0 AND A2.DISPLAYZONE='stdTable' "
+ "GROUP BY A1.SITEGUID,A3.BUGUID,A1.USERANSWER,A2.DISPLAYZONE ORDER BY COUNT(A1.USERANSWER) DESC", filterDate);
//问卷浏览量（按日期排序）
string brSql = string.Format("SELECT CONVERT(VARCHAR(10),A1.CREATETIME,23) AS SURVEYTIME,COUNT(A1.USERID) AS AMOUNT FROM TBLUSERLOG (NOLOCK) A1 "
+ "WHERE A1.PAGENAME LIKE '%{0}%' "
+ " {1}"
+ "GROUP BY CONVERT(VARCHAR(10), A1.CREATETIME, 23) ORDER BY CONVERT(VARCHAR(10), A1.CREATETIME, 23)", param["pageName"], filterDate);
//问卷发起BU和Site
List <CCMast> BUSiteList = SqlServerHelper.GetEntityList <CCMast>(SqlServerHelper.sfeed(), buSql);
//问卷汇总
List <SiteSurvey> SiteSurveySummary = SqlServerHelper.GetEntityList <SiteSurvey>(SqlServerHelper.salesorderConn(), strSql);
//问卷浏览量（按日期排序）
List <SiteSurvey> BrTimeList = SqlServerHelper.GetEntityList <SiteSurvey>(SqlServerHelper.sfeed(), brSql);
//问卷回收量（按日期排序）
List <SiteSurvey> FBTimeList = new List <SiteSurvey>();
List <SiteSurvey> list = new List <SiteSurvey>();
SiteSurvey obj = new SiteSurvey();
if (BUSiteList == null || !BUSiteList.Any() || SiteSurveySummary == null || !SiteSurveySummary.Any())
{
return(null);
}
var SurveySummary = SiteSurveySummary.Join(BUSiteList,
a => new { siteGuid = a.siteGuid },
b => new { siteGuid = b.siteGuid, },
(a, b) =>
{
a.companyGuid = b.companyGuid;
a.siteGuid = b.siteGuid;
return(a);
}).ToList();
if (SurveySummary == null || !SurveySummary.Any())
{
string siteGuid = string.Format("{0}{1}{0}", "'", (string)param["siteGuid"]);
list = SiteSurveyDetails(siteGuid, filterDate);
}
else
{
List <object> surveySummary = new List <object>();
foreach (var item in SurveySummary)
{
obj = new SiteSurvey();
obj.companyGuid = item.companyGuid;
obj.siteGuid = item.siteGuid;
obj.userAnswer = item.userAnswer;
obj.amount = item.amount;
surveySummary.Add(obj);
}
// 问卷调查明细
string siteGuid = string.Empty;
foreach (var line in SurveySummary)
{
siteGuid += ",'" + line.siteGuid + "'";
}
if (!string.IsNullOrWhiteSpace(siteGuid))
{
list = SiteSurveyDetails(siteGuid.Substring(1), filterDate);
}
SurveySummary = SurveySummary.GroupBy(dr => dr.userAnswer).Select(gr => new SiteSurvey()
{
companyGuid = gr.FirstOrDefault().companyGuid,
userAnswer = gr.Key,
amount = gr.Sum(d => d.amount),
siteGuid = siteGuid.Substring(1),
}).ToList();
}
List <object> surveyDetails = new List <object>();
List <object> Items = new List <object>();
List <SiteSurvey> surveyItems = new List <SiteSurvey>();
if (list != null && list.Any())
{
foreach (var item in list)
{
obj = new SiteSurvey();
obj.surveyDetails = item.surveyDetails;
obj.createTime = item.createTime;
obj.createUserID = item.createUserID;
obj.headGuid = item.headGuid;
obj.wechatID = item.wechatID;
surveyDetails.Add(obj);
Items.Add(obj.surveyDetails);
}
//调研问题汇总
if (Items != null && Items.Any())
{
for (int i = 0; i < Items.Count; i++)
{
dynamic Details = Items[i];
if (Details.Count == 0)
{
continue;
}
for (int j = 0; j < Details.Count; j++)
{
obj = new SiteSurvey();
obj.headGuid = Details[j].headGuid;
obj.lineGuid = Details[j].lineGuid;
obj.surveyItem = Details[j].surveyItem;
obj.createTime = Details[j].createTime;
obj.displayZone = Details[j].displayZone;
surveyItems.Add(obj);
FBTimeList.Add(obj);
}
}
}
}
if (surveyItems != null && surveyItems.Any())
{
surveyItems = surveyItems.Distinct(q => q.lineGuid).ToList(); //调研中提出的具体问题
}
//问卷回收量（按日期排序）
if (FBTimeList != null && FBTimeList.Any())
{
List <SiteSurvey> FBTimeList2 = FBTimeList.GroupBy(ds => new
{
surveyTime = ds.createTime.ToString("yyyy-MM-dd"),
headGuid = ds.headGuid.ToString()
}).Select(dr => new SiteSurvey()
{
surveyTime = dr.FirstOrDefault().createTime.ToString("yyyy-MM-dd"),
headGuid = dr.FirstOrDefault().headGuid,
}).OrderBy(g => g.surveyTime).ToList();
FBTimeList = FBTimeList2.GroupBy(dh => new
{
surveyTime = dh.surveyTime
}).Select(dw => new SiteSurvey()
{
surveyTime = dw.Key.surveyTime,
amount = dw.Count()
}).OrderBy(h => h.surveyTime).ToList();
}
List <SiteSurvey> SurveyList = new List <SiteSurvey>();
obj = new SiteSurvey();
if (SurveySummary != null && SurveySummary.Any())
{
obj.surveySummary = SurveySummary.ToList <object>(); //调研汇总
}
if (surveyDetails != null && surveyDetails.Any())
{
obj.surveyDetails = surveyDetails; //调研明细
}
if (surveyItems != null && surveyItems.Any())
{
obj.surveyItems = surveyItems.ToList <object>(); //调研问题
}
if (BrTimeList != null && BrTimeList.Any())
{
obj.BrTimeList = BrTimeList.ToList <object>(); //问卷浏览量（按日期）
}
if (FBTimeList != null && FBTimeList.Any())
{
obj.FBTimeList = FBTimeList.ToList <object>(); //问卷回收量（按日期）
}
SurveyList.Add(obj);
return(SurveyList);
}
catch (Exception ex)
{
throw ex;
}
}
