private void UpdateWenJuanData(HttpRequestBase Request) { Guid submitID = Guid.NewGuid(); WenJuanContext wc = new WenJuanContext(); WenJuan wj = new WenJuan(); wj.SubmitID = submitID; wj.BanShiChu = Request["BanShiChu"]; wj.YingXiang = Request["YingXiang"]; wj.YiShiGuanNian = Request["YiShiGuanNian"]; wj.YingXiang_Other = Request["YingXiang_Other"]; wj.YiShiGuanNian = Request["YiShiGuanNian"]; wj.YiShiGuanNian_Other = Request["YiShiGuanNian_Other"]; wj.ZhongDian = Request["ZhongDian"]; wj.LiuCheng = Request["LiuCheng"]; wj.XinXi = Request["XinXi"]; wj.BaoHe = Request["BaoHe"]; wj.PeiYang = Request["PeiYang"]; wj.TiSheng = Request["TiSheng"]; wj.TiSheng_Other = Request["TiSheng_Other"]; wj.GangWeiWenTi = Request["GangWeiWenTi"]; wj.FenPeiFnagAn = Request["FenPeiFnagAn"]; wj.JieYue = Request["JieYue"]; wj.JieYue_Other = Request["JieYue_Other"]; if (!string.IsNullOrEmpty(Request["XuQiu"]) && Request["XuQiu"].IndexOf(',') != -1) { string[] cList1 = Request["XuQiu"].Split(','); wj.XuQiu = cList1[0]; for (int i = 1; i < cList1.Length; i++) { WenJuan wjTemp = new WenJuan(); wjTemp.SubmitID = submitID; wjTemp.BanShiChu = Request["BanShiChu"]; wjTemp.XuQiu = cList1[i]; wc.WenJuans.Add(wjTemp); wc.SaveChanges(); } } else { wj.XuQiu = Request["XuQiu"]; } if (!string.IsNullOrEmpty(Request["BuTongChang"]) && Request["BuTongChang"].IndexOf(',') != -1) { string[] cList1 = Request["BuTongChang"].Split(','); wj.BuTongChang = cList1[0]; for (int i = 1; i < cList1.Length; i++) { WenJuan wjTemp = new WenJuan(); wjTemp.SubmitID = submitID; wjTemp.BanShiChu = Request["BanShiChu"]; wjTemp.BuTongChang = cList1[i]; wc.WenJuans.Add(wjTemp); wc.SaveChanges(); } } else { wj.BuTongChang = Request["BuTongChang"]; } if (!string.IsNullOrEmpty(Request["GangWeiHeLi"]) && Request["GangWeiHeLi"].IndexOf(',') != -1) { string[] cList1 = Request["GangWeiHeLi"].Split(','); wj.GangWeiHeLi = cList1[0]; for (int i = 1; i < cList1.Length; i++) { WenJuan wjTemp = new WenJuan(); wjTemp.SubmitID = submitID; wjTemp.BanShiChu = Request["BanShiChu"]; wjTemp.GangWeiHeLi = cList1[i]; wc.WenJuans.Add(wjTemp); wc.SaveChanges(); } } else { wj.GangWeiHeLi = Request["GangWeiHeLi"]; } if (!string.IsNullOrEmpty(Request["LiuChengJianYi"]) && Request["LiuChengJianYi"].IndexOf(',') != -1) { string[] cList1 = Request["LiuChengJianYi"].Split(','); wj.LiuChengJianYi = cList1[0]; for (int i = 1; i < cList1.Length; i++) { WenJuan wjTemp = new WenJuan(); wjTemp.SubmitID = submitID; wjTemp.BanShiChu = Request["BanShiChu"]; wjTemp.LiuChengJianYi = cList1[i]; wc.WenJuans.Add(wjTemp); wc.SaveChanges(); } } else { wj.LiuChengJianYi = Request["LiuChengJianYi"]; } if (!string.IsNullOrEmpty(Request["KeFuQueDian"]) && Request["KeFuQueDian"].IndexOf(',') != -1) { string[] cList1 = Request["KeFuQueDian"].Split(','); wj.KeFuQueDian = cList1[0]; for (int i = 1; i < cList1.Length; i++) { WenJuan wjTemp = new WenJuan(); wjTemp.SubmitID = submitID; wjTemp.BanShiChu = Request["BanShiChu"]; wjTemp.KeFuQueDian = cList1[i]; wc.WenJuans.Add(wjTemp); wc.SaveChanges(); } } else { wj.KeFuQueDian = Request["KeFuQueDian"]; } if (!string.IsNullOrEmpty(Request["JiangBen"]) && Request["JiangBen"].IndexOf(',') != -1) { string[] cList1 = Request["JiangBen"].Split(','); wj.JiangBen = cList1[0]; for (int i = 1; i < cList1.Length; i++) { WenJuan wjTemp = new WenJuan(); wjTemp.SubmitID = submitID; wjTemp.BanShiChu = Request["BanShiChu"]; wjTemp.JiangBen = cList1[i]; wc.WenJuans.Add(wjTemp); wc.SaveChanges(); } } else { wj.JiangBen = Request["JiangBen"]; } if (!string.IsNullOrEmpty(Request["JiaQiang"]) && Request["JiaQiang"].IndexOf(',') != -1) { string[] cList1 = Request["JiaQiang"].Split(','); wj.JiaQiang = cList1[0]; for (int i = 1; i < cList1.Length; i++) { WenJuan wjTemp = new WenJuan(); wjTemp.SubmitID = submitID; wjTemp.BanShiChu = Request["BanShiChu"]; wjTemp.JiaQiang = cList1[i]; wc.WenJuans.Add(wjTemp); wc.SaveChanges(); } } else { wj.JiaQiang = Request["JiaQiang"]; } if (!string.IsNullOrEmpty(Request["KaoHe"]) && Request["KaoHe"].IndexOf(',') != -1) { string[] cList1 = Request["KaoHe"].Split(','); wj.KaoHe = cList1[0]; for (int i = 1; i < cList1.Length; i++) { WenJuan wjTemp = new WenJuan(); wjTemp.SubmitID = submitID; wjTemp.BanShiChu = Request["BanShiChu"]; wjTemp.KaoHe = cList1[i]; wc.WenJuans.Add(wjTemp); wc.SaveChanges(); } } else { wj.KaoHe = Request["KaoHe"]; } if (!string.IsNullOrEmpty(Request["PeiYangBuHeLi"]) && Request["PeiYangBuHeLi"].IndexOf(',') != -1) { string[] cList1 = Request["PeiYangBuHeLi"].Split(','); wj.PeiYangBuHeLi = cList1[0]; for (int i = 1; i < cList1.Length; i++) { WenJuan wjTemp = new WenJuan(); wjTemp.SubmitID = submitID; wjTemp.BanShiChu = Request["BanShiChu"]; wjTemp.PeiYangBuHeLi = cList1[i]; wc.WenJuans.Add(wjTemp); wc.SaveChanges(); } } else { wj.PeiYangBuHeLi = Request["PeiYangBuHeLi"]; } if (!string.IsNullOrEmpty(Request["KaoHeGaiJin"]) && Request["KaoHeGaiJin"].IndexOf(',') != -1) { string[] cList1 = Request["KaoHeGaiJin"].Split(','); wj.KaoHeGaiJin = cList1[0]; for (int i = 1; i < cList1.Length; i++) { WenJuan wjTemp = new WenJuan(); wjTemp.SubmitID = submitID; wjTemp.BanShiChu = Request["BanShiChu"]; wjTemp.KaoHeGaiJin = cList1[i]; wc.WenJuans.Add(wjTemp); wc.SaveChanges(); } } else { wj.KaoHeGaiJin = Request["KaoHeGaiJin"]; } if (!string.IsNullOrEmpty(Request["GouTong"]) && Request["GouTong"].IndexOf(',') != -1) { string[] cList1 = Request["GouTong"].Split(','); wj.GouTong = cList1[0]; for (int i = 1; i < cList1.Length; i++) { WenJuan wjTemp = new WenJuan(); wjTemp.SubmitID = submitID; wjTemp.BanShiChu = Request["BanShiChu"]; wjTemp.GouTong = cList1[i]; wc.WenJuans.Add(wjTemp); wc.SaveChanges(); } } else { wj.GouTong = Request["GouTong"]; } if (!string.IsNullOrEmpty(Request["RenLiGaiJin"]) && Request["RenLiGaiJin"].IndexOf(',') != -1) { string[] cList1 = Request["RenLiGaiJin"].Split(','); wj.RenLiGaiJin = cList1[0]; for (int i = 1; i < cList1.Length; i++) { WenJuan wjTemp = new WenJuan(); wjTemp.SubmitID = submitID; wjTemp.BanShiChu = Request["BanShiChu"]; wjTemp.RenLiGaiJin = cList1[i]; wc.WenJuans.Add(wjTemp); wc.SaveChanges(); } } else { wj.RenLiGaiJin = Request["RenLiGaiJin"]; } if (!string.IsNullOrEmpty(Request["PeiXunWenTi"]) && Request["PeiXunWenTi"].IndexOf(',') != -1) { string[] cList1 = Request["PeiXunWenTi"].Split(','); wj.PeiXunWenTi = cList1[0]; for (int i = 1; i < cList1.Length; i++) { WenJuan wjTemp = new WenJuan(); wjTemp.SubmitID = submitID; wjTemp.BanShiChu = Request["BanShiChu"]; wjTemp.PeiXunWenTi = cList1[i]; wc.WenJuans.Add(wjTemp); wc.SaveChanges(); } } else { wj.PeiXunWenTi = Request["PeiXunWenTi"]; } wj.JianYiA = Request["JianYiA"]; wj.JianYiB = Request["JianYiB"]; wj.JianYiC = Request["JianYiC"]; wj.JianYiD = Request["JianYiD"]; wc.WenJuans.Add(wj); wc.SaveChanges(); }
public ActionResult WenJuanResult() { string keyWords = string.Empty; ViewBag.ErrorMsg = string.Empty; Dictionary <string, string> tongJiList = new Dictionary <string, string>(); tongJiList.Add("YingXiang", string.Empty); tongJiList.Add("YiShiGuanNian", string.Empty); tongJiList.Add("ZhongDian", string.Empty); tongJiList.Add("LiuCheng", string.Empty); tongJiList.Add("XinXi", string.Empty); tongJiList.Add("BaoHe", string.Empty); tongJiList.Add("PeiYang", string.Empty); tongJiList.Add("TiSheng", string.Empty); tongJiList.Add("GangWeiWenTi", string.Empty); tongJiList.Add("FenPeiFnagAn", string.Empty); tongJiList.Add("JieYue", string.Empty); tongJiList.Add("XuQiu", string.Empty); tongJiList.Add("BuTongChang", string.Empty); tongJiList.Add("GangWeiHeLi", string.Empty); tongJiList.Add("LiuChengJianYi", string.Empty); tongJiList.Add("KeFuQueDian", string.Empty); tongJiList.Add("JiangBen", string.Empty); tongJiList.Add("JiaQiang", string.Empty); tongJiList.Add("KaoHe", string.Empty); tongJiList.Add("PeiYangBuHeLi", string.Empty); tongJiList.Add("KaoHeGaiJin", string.Empty); tongJiList.Add("GouTong", string.Empty); tongJiList.Add("RenLiGaiJin", string.Empty); tongJiList.Add("PeiXunWenTi", string.Empty); tongJiList.Add("YingXiang_Other", string.Empty); tongJiList.Add("YiShiGuanNian_Other", string.Empty); tongJiList.Add("TiSheng_Other", string.Empty); tongJiList.Add("JieYue_Other", string.Empty); tongJiList.Add("JianYiA", string.Empty); tongJiList.Add("JianYiB", string.Empty); tongJiList.Add("JianYiC", string.Empty); tongJiList.Add("JianYiD", string.Empty); ViewBag.TongJiData = tongJiList; ViewBag.TongJiList = "YingXiang$YiShiGuanNian$ZhongDian$LiuCheng$XinXi$BaoHe$PeiYang$TiSheng$GangWeiWenTi$FenPeiFnagAn$JieYue$XuQiu$BuTongChang$GangWeiHeLi" + "$LiuChengJianYi$KeFuQueDian$JiangBen$JiaQiang$KaoHe$PeiYangBuHeLi$KaoHeGaiJin$GouTong$RenLiGaiJin$PeiXunWenTi"; if (!string.IsNullOrEmpty(Request["BanShiChu"])) { keyWords = Request["BanShiChu"]; } else if (!string.IsNullOrEmpty(Request["Zone"])) { keyWords = Request["Zone"]; } else { ViewBag.ErrorMsg = "请选择统计的区域或者办事处、分公司。"; return(View()); } WenJuanContext wc = new WenJuanContext(); int totalUser = wc.WenJuans.Where(u => keyWords.IndexOf("'" + u.BanShiChu + "'") != -1).Select(u => u.SubmitID).Distinct().Count(); if (totalUser != 0) { GetTongJiData(totalUser, wc, tongJiList, keyWords); } else { ViewBag.ErrorMsg = "暂无符合的数据。"; return(View()); } return(View()); }
private void GetTongJiData(int totalUser, WenJuanContext wc, Dictionary <string, string> tongJiList, string keyWords) { string tjTotal = "YingXiang$YiShiGuanNian$ZhongDian$LiuCheng$XinXi$BaoHe$PeiYang$TiSheng$GangWeiWenTi$FenPeiFnagAn$JieYue$XuQiu$BuTongChang$GangWeiHeLi" + "$LiuChengJianYi$KeFuQueDian$JiangBen$JiaQiang$KaoHe$PeiYangBuHeLi$KaoHeGaiJin$GouTong$RenLiGaiJin$PeiXunWenTi"; string jyTotal = "YingXiang_Other$YiShiGuanNian_Other$TiSheng_Other$JieYue_Other$JianYiA$JianYiB$JianYiC$JianYiD"; string[] jyList = jyTotal.Split('$'); string[] tjList = tjTotal.Split('$'); MySqlConnection dbcon = (MySqlConnection)wc.Database.Connection; MySqlCommand cm = new MySqlCommand(); cm.Connection = dbcon; cm.Connection.Open(); foreach (string tj in tjList) { cm.CommandText = "select count(distinct SubmitID) from wenjuans where " + tj + " is not null and BanShiChu in (" + keyWords + ");"; int userCheckCount = Convert.ToInt32(cm.ExecuteScalar()); cm.CommandText = "select " + tj + ",count(BanShiChu) as checkCount from wenjuans where " + tj + " is not null and BanShiChu in (" + keyWords + ") group by " + tj + ";"; MySqlDataReader rd = cm.ExecuteReader(); string tempStr = string.Empty; while (rd.Read()) { string name = rd[tj].ToString().Substring(0, 1); string val = rd["checkCount"].ToString(); double bl = Math.Round(Convert.ToDouble(val) / userCheckCount * 100, 2); name += " " + bl.ToString() + "%"; if (tempStr == string.Empty) { tempStr = "{ 'name': '" + name + "', 'val': " + val + " }"; } else { tempStr = tempStr + ",{ 'name': '" + name + "', 'val': " + val + " }"; } } if (tempStr != string.Empty) { tempStr = "[" + tempStr + "]"; tongJiList[tj] = tempStr + "$" + "选择总数/问卷总数:" + userCheckCount.ToString() + "/" + totalUser.ToString() + " 选择率:" + Math.Round(Convert.ToDouble(userCheckCount) / totalUser * 100, 2).ToString() + "%"; } rd.Close(); } foreach (string jy in jyList) { cm.CommandText = "select count(BanShiChu) from wenjuans where " + jy + " is not null and " + jy + " <>'' and BanShiChu in (" + keyWords + ");"; int userCheckCount = Convert.ToInt32(cm.ExecuteScalar()); cm.CommandText = "select distinct " + jy + " from wenjuans where " + jy + " is not null and " + jy + " <>'' and BanShiChu in (" + keyWords + ");"; MySqlDataReader rd = cm.ExecuteReader(); string tempStr = string.Empty; while (rd.Read()) { if (tempStr == string.Empty) { tempStr = rd[jy].ToString(); } else { tempStr = tempStr + "$" + rd[jy].ToString(); } } if (tempStr != string.Empty) { tongJiList[jy] = tempStr + "&" + "填写总数/问卷总数:" + userCheckCount.ToString() + "/" + totalUser.ToString() + " 填写率:" + Math.Round(Convert.ToDouble(userCheckCount) / totalUser * 100, 2).ToString() + "%"; } rd.Close(); } cm.Connection.Close(); //tongJiList["YingXiang"] = "[{ 'name': '中文', 'val': 20 }, { 'name': '英文', 'val': 20 }, { 'name': '泰文', 'val': 20 }, { 'name': '添文', 'val': 20 }, { 'name': '草书', 'val': 20 }]"; }