public System.Data.DataTable Export(PaperAnswer pa) { StringBuilder sql = new StringBuilder(); //sql.AppendLine(@"SELECT pa.answerID,pa.paperID,pa.userid,p.paperName,pa.userMail,pa.order_id,"); //sql.AppendLine(@" pa.classID,pc.className,pa.answerContent,pa.classType,pa.answerDate "); //sql.AppendLine(@" from paper_answer pa LEFT JOIN paper_class pc ON pa.classID=pc.classID"); //sql.AppendLine(@" LEFT JOIN paper p ON pa.paperID=p.paperID WHERE 1=1 "); sql.AppendLine(@"SELECT pa.answerID,pa.paperID,p.paperName,pa.userid,pa.userMail,pa.order_id,"); sql.AppendLine(@" pa.classID,pc.className,pa.classType,pa.answerContent,pa.answerDate,pcc.classContent,pa.order_id "); sql.AppendLine(@" from paper_answer pa "); sql.AppendLine(@" LEFT JOIN (SELECT DISTINCT(classID),className from paper_class) pc ON pa.classID=pc.classID "); sql.AppendLine(@" LEFT JOIN paper_class pcc ON pa.answerContent=pcc.id "); sql.AppendLine(@" LEFT JOIN paper p ON pa.paperID=p.paperID WHERE 1=1 "); if (pa.paperID != 0) { sql.AppendFormat(@" AND pa.paperID='{0}' ", pa.paperID); } if (pa.userid != 0) { sql.AppendFormat(@" AND pa.userid='{0}' ", pa.userid); } sql.AppendFormat(@" ORDER BY pa.answerDate DESC,pa.userid ASC,pa.paperID DESC,pa.classID ASC "); try { return _access.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("PaperAnswerDao-->Export" + ex.Message + sql.ToString(), ex); } }
public List<PaperAnswer> GetPaperAnswerList(PaperAnswer pa, out int totalCount) { StringBuilder sqlfield = new StringBuilder(); StringBuilder sqlfrom = new StringBuilder(); StringBuilder sqlwhere = new StringBuilder(); StringBuilder sql = new StringBuilder(); StringBuilder sq1count = new StringBuilder(); sqlfield.AppendLine(@"SELECT pa.answerID,pa.paperID,pa.userid,pa.userMail,pa.classID,"); sqlfield.AppendLine(@"pa.answerContent,pcc.classContent,pa.classType,pa.answerDate,pa.order_id "); sql.Append(sqlfield); sql.AppendFormat(@" ,pc.className,p.paperName "); sqlfrom.AppendLine(@" from paper_answer pa "); sqlfrom.AppendFormat(@" LEFT JOIN (SELECT DISTINCT(classID),className from paper_class) pc ON pa.classID=pc.classID "); sqlfrom.AppendFormat(@" LEFT JOIN paper_class pcc ON pa.answerContent=pcc.id "); sqlfrom.AppendFormat(@" LEFT JOIN paper p ON pa.paperID=p.paperID WHERE 1=1"); sq1count.Append(" select count(answerID) as totalCount "); sql.Append(sqlfrom); if (pa.paperID != 0) { sqlwhere.AppendFormat(@" AND pa.paperID='{0}' ", pa.paperID); } if (pa.classID != 0) { sqlwhere.AppendFormat(@" AND pa.classID='{0}' ", pa.classID); } if (pa.userid != 0) { sqlwhere.AppendFormat(@" AND pa.userid='{0}' ", pa.userid); } if (pa.answerID != 0) { sqlwhere.AppendFormat(@" AND pa.answerID='{0}' ", pa.answerID); } sql.Append(sqlwhere); sql.AppendFormat(" ORDER BY pa.answerDate DESC,pa.userid ASC,pa.paperID DESC,pa.classID ASC "); totalCount = 0; try { if (pa.IsPage) { sq1count.Append(sqlfrom.ToString() + sqlwhere.ToString()); sql.AppendFormat(@" limit {0},{1} ", pa.Start, pa.Limit); DataTable dt = _access.getDataTable(sq1count.ToString()); if (dt != null && dt.Rows.Count > 0) { totalCount =Convert.ToInt32(dt.Rows[0]["totalCount"]); } } return _access.getDataTableForObj<PaperAnswer>(sql.ToString()); } catch (Exception ex) { throw new Exception("PaperAnswerDao-->GetPaperAnswerList" + ex.Message + sql.ToString() + sq1count.ToString(), ex); } }
public DataTable ExportSinglePaperAnswer(PaperAnswer pa) { try { return _paperAnswerDao.Export(pa); } catch (Exception ex) { throw new Exception("PaperAnswerMgr-->ExportSinglePaperAnswer" + ex.Message, ex); } }
public DataTable GetPaperAnswerUser(PaperAnswer pa) { try { return _paperAnswerDao.GetPaperAnswerUser(pa); } catch (Exception ex) { throw new Exception("PaperAnswerMgr-->GetPaperAnswerUser" + ex.Message, ex); } }
public List<PaperAnswer> GetPaperAnswerList(PaperAnswer pa, out int totalCount) { try { return _paperAnswerDao.GetPaperAnswerList(pa, out totalCount); } catch (Exception ex) { throw new Exception("PaperAnswerMgr-->GetPaperAnswerList" + ex.Message, ex); } }
public void OutSinglePaperExcel() { try { PaperAnswer pa = new PaperAnswer(); PaperClass pc = new PaperClass(); string paper_name = Request.Params["paper_name"]; _paperAnswerMgr = new PaperAnswerMgr(mySqlConnectionString); if (!string.IsNullOrEmpty(Request.Params["paper_id"])) { pa.paperID = int.Parse(Request.Params["paper_id"]); pc.paperID = int.Parse(Request.Params["paper_id"]); } DataTable dtclass = _paperAnswerMgr.GetPaperClassID(pc); DataTable dtuser = _paperAnswerMgr.GetPaperAnswerUser(pa); DataTable dtanswer = _paperAnswerMgr.ExportSinglePaperAnswer(pa); DataTable dt = new DataTable(); dt.Columns.Add("用戶編號", typeof(string)); //dt.Columns.Add("用戶郵箱", typeof(string)); dt.Columns.Add("訂單編號", typeof(string)); dt.Columns.Add("作答時間", typeof(string)); for (int i = 0; i < dtclass.Rows.Count; i++) { dt.Columns.Add("題目" + (i + 1), typeof(string)); } for (int i = 0; i < dtuser.Rows.Count; i++) { DataRow dr = dt.NewRow(); dr["用戶編號"] = dtuser.Rows[i]["userid"]; // dr["用戶郵箱"] = dtuser.Rows[i]["userMail"]; dr["訂單編號"] = dtuser.Rows[i]["order_id"]; dr["作答時間"] = DateTime.Parse(dtuser.Rows[i]["answerDate"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"); for (int j = 0; j < dtclass.Rows.Count; j++) { DataRow[] drs = dtanswer.Select("userid=" + dtuser.Rows[i]["userid"] + " and answerDate='" + DateTime.Parse(dtuser.Rows[i]["answerDate"].ToString()).ToString("yyyy-MM-dd HH:mm:ss") + "' and classID=" + dtclass.Rows[j]["classID"]); string cname = "題目" + (j + 1); if (drs.Count() == 0) { dr[cname] = ""; } else if (drs.Count() == 1) { if (dtclass.Rows[j]["classType"].ToString() == "MC" || dtclass.Rows[j]["classType"].ToString() == "SC") { dr[cname] = drs[0]["classContent"]; } else if (dtclass.Rows[j]["classType"].ToString() == "ML" || dtclass.Rows[j]["classType"].ToString() == "SL") { dr[cname] = drs[0]["answerContent"]; } } //多選題目,答案組合 else if (drs.Count() > 1) { for (int k = 0; k < drs.Count(); k++) { dr[cname] += "|" + drs[k]["classContent"]; } dr[cname] = dr[cname].ToString().TrimStart('|'); } } dt.Rows.Add(dr); } if (dt.Rows.Count > 0) { string fileName = paper_name + "_答案_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; MemoryStream ms = ExcelHelperXhf.ExportDT(dt, paper_name); Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName); Response.BinaryWrite(ms.ToArray()); } else { Response.Write("會出數據不存在"); } } catch (Exception ex) { Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage(); logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message); logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; log.Error(logMessage); } }
public void OutExcel() { try { PaperAnswer pa = new PaperAnswer(); _paperAnswerMgr = new PaperAnswerMgr(mySqlConnectionString); if (!string.IsNullOrEmpty(Request.Params["paper_id"])) { pa.paperID = int.Parse(Request.Params["paper_id"]); } if (!string.IsNullOrEmpty(Request.Params["user_id"])) { pa.userid = int.Parse(Request.Params["user_id"]); } DataTable dt = _paperAnswerMgr.Export(pa); //DataTable ndt = dt.DefaultView.ToTable(false, new string[] { "paperName", "userid", "userMail", "order_id", "className", "classType", "answerContent", "classContent" }); DataTable ndt = dt.DefaultView.ToTable(false, new string[] { "paperName", "userid", "order_id", "className", "classType", "answerContent", "classContent" }); DataTable content = new DataTable(); content = ndt.Clone(); content.Columns.Add("answerDate", typeof(string)); content.Columns.Remove("classContent"); //content.Columns["paperName"].ColumnName = "問卷名稱"; //content.Columns["userid"].ColumnName = "用戶id"; //content.Columns["userMail"].ColumnName = "用戶郵箱"; //content.Columns["className"].ColumnName = "題目名稱"; //content.Columns["classType"].ColumnName = "題目類型"; //content.Columns["answerContent"].ColumnName = "答案"; DataRow dr; for (int i = 0; i < dt.Rows.Count; i++) { dr = content.NewRow(); if (i > 0) { if (dt.Rows[i]["paperID"].ToString() == dt.Rows[i - 1]["paperID"].ToString()) { //dr["userid"] = ndt.Rows[i]["userid"]; //dr["userMail"] = ndt.Rows[i]["userMail"]; //content.Rows.Add(dr); if (dt.Rows[i]["userid"].ToString() == dt.Rows[i - 1]["userid"].ToString() && dt.Rows[i]["answerDate"].ToString() == dt.Rows[i - 1]["answerDate"].ToString()) { dr["className"] = dt.Rows[i]["className"]; //dr["classType"] = dt.Rows[i]["classType"]; switch (dt.Rows[i]["classType"].ToString()) { case "SC": dr["classType"] = "單選"; dr["answerContent"] = dt.Rows[i]["classContent"]; break; case "MC": dr["classType"] = "多選"; dr["answerContent"] = dt.Rows[i]["classContent"]; break; case "SL": dr["classType"] = "單行"; dr["answerContent"] = dt.Rows[i]["answerContent"]; break; case "ML": dr["classType"] = "多行"; dr["answerContent"] = dt.Rows[i]["answerContent"]; break; } if (!string.IsNullOrEmpty(dt.Rows[i]["answerDate"].ToString())) { dr["answerDate"] = (DateTime.Parse(dt.Rows[i]["answerDate"].ToString())).ToString("yyyy-MM-dd HH:mm:ss"); } content.Rows.Add(dr); } else { dr["userid"] = dt.Rows[i]["userid"]; //dr["userMail"] = dt.Rows[i]["userMail"]; dr["order_id"] = dt.Rows[i]["order_id"]; content.Rows.Add(dr); dr = content.NewRow(); dr["className"] = dt.Rows[i]["className"]; //dr["classType"] = dt.Rows[i]["classType"]; switch (dt.Rows[i]["classType"].ToString()) { case "SC": dr["classType"] = "單選"; dr["answerContent"] = dt.Rows[i]["classContent"]; break; case "MC": dr["classType"] = "多選"; dr["answerContent"] = dt.Rows[i]["classContent"]; break; case "SL": dr["classType"] = "單行"; dr["answerContent"] = dt.Rows[i]["answerContent"]; break; case "ML": dr["classType"] = "多行"; dr["answerContent"] = dt.Rows[i]["answerContent"]; break; } if (!string.IsNullOrEmpty(dt.Rows[i]["answerDate"].ToString())) { dr["answerDate"] = DateTime.Parse(dt.Rows[i]["answerDate"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"); } content.Rows.Add(dr); } } else { dr["paperName"] = dt.Rows[i]["paperName"]; content.Rows.Add(dr); dr = content.NewRow(); dr["userid"] = dt.Rows[i]["userid"]; //dr["userMail"] = dt.Rows[i]["userMail"]; dr["order_id"] = dt.Rows[i]["order_id"]; content.Rows.Add(dr); dr = content.NewRow(); dr["className"] = dt.Rows[i]["className"]; //dr["classType"] = dt.Rows[i]["classType"]; switch (dt.Rows[i]["classType"].ToString()) { case "SC": dr["classType"] = "單選"; dr["answerContent"] = dt.Rows[i]["classContent"]; break; case "MC": dr["classType"] = "多選"; dr["answerContent"] = dt.Rows[i]["classContent"]; break; case "SL": dr["classType"] = "單行"; dr["answerContent"] = dt.Rows[i]["answerContent"]; break; case "ML": dr["classType"] = "多行"; dr["answerContent"] = dt.Rows[i]["answerContent"]; break; } if (!string.IsNullOrEmpty(dt.Rows[i]["answerDate"].ToString())) { dr["answerDate"] = DateTime.Parse(dt.Rows[i]["answerDate"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"); } content.Rows.Add(dr); } } else { dr["paperName"] = dt.Rows[i]["paperName"]; content.Rows.Add(dr); dr = content.NewRow(); dr["userid"] = dt.Rows[i]["userid"]; //dr["userMail"] = dt.Rows[i]["userMail"]; dr["order_id"] = dt.Rows[i]["order_id"]; content.Rows.Add(dr); dr = content.NewRow(); dr["className"] = dt.Rows[i]["className"]; //dr["classType"] = dt.Rows[i]["classType"]; switch (dt.Rows[i]["classType"].ToString()) { case "SC": dr["classType"] = "單選"; dr["answerContent"] = dt.Rows[i]["classContent"]; break; case "MC": dr["classType"] = "多選"; dr["answerContent"] = dt.Rows[i]["classContent"]; break; case "SL": dr["classType"] = "單行"; dr["answerContent"] = dt.Rows[i]["answerContent"]; break; case "ML": dr["classType"] = "多行"; dr["answerContent"] = dt.Rows[i]["answerContent"]; break; } if (!string.IsNullOrEmpty(dt.Rows[i]["answerDate"].ToString())) { dr["answerDate"] = DateTime.Parse(dt.Rows[i]["answerDate"].ToString()).ToString("yyyy-MM-dd HH:mm:ss"); } content.Rows.Add(dr); } } content.Columns["paperName"].ColumnName = "問卷名稱"; content.Columns["userid"].ColumnName = "用戶編號"; //content.Columns["userMail"].ColumnName = "用戶郵箱"; content.Columns["order_id"].ColumnName = "訂單編號"; content.Columns["className"].ColumnName = "題目名稱"; content.Columns["classType"].ColumnName = "題目類型"; content.Columns["answerContent"].ColumnName = "答案"; content.Columns["answerDate"].ColumnName = "作答時間"; string fileName = "問卷答案_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; MemoryStream ms = ExcelHelperXhf.ExportDT(content, ""); Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName); Response.BinaryWrite(ms.ToArray()); } catch (Exception ex) { Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage(); logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message); logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; log.Error(logMessage); } }
public HttpResponseBase GetPaperAnswerList() { string json = string.Empty; List<PaperAnswer> store = new List<PaperAnswer>(); PaperAnswer pa = new PaperAnswer(); try { pa.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 pa.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量 _paperAnswerMgr = new PaperAnswerMgr(mySqlConnectionString); int totalCount = 0; if (!string.IsNullOrEmpty(Request.Params["paper_id"])) { pa.paperID = int.Parse(Request.Params["paper_id"]); } if (!string.IsNullOrEmpty(Request.Params["user_id"])) { pa.userid = int.Parse(Request.Params["user_id"]); } store = _paperAnswerMgr.GetPaperAnswerList(pa, out totalCount); foreach (var item in store) { item.userMail = item.userMail.Split('@')[0] + "@***"; } IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss "; json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, Formatting.Indented, timeConverter) + "}";//返回json數據 } catch (Exception ex) { Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage(); logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message); logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; log.Error(logMessage); json = "{success:false}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public DataTable ExportSinglePaperAnswer(PaperAnswer pa) { StringBuilder sql = new StringBuilder(); sql.AppendLine(@""); sql.AppendLine(@" SELECT pa.userid,pa.order_id,pa.answerDate,pa.classID,pa.classType,pa.answerContent,pc.classContent "); sql.AppendLine(@" FROM paper_answer pa"); sql.AppendLine(@" LEFT JOIN paper_class pc ON pa.answerContent=pc.id"); sql.AppendLine(@" WHERE 1=1 "); if(pa.paperID!=0) { sql.AppendFormat(@" AND pa.paperID='{0}' ",pa.paperID); } sql.AppendLine(@" ORDER BY answerDate DESC,pa.userid ASC, pa.classID ASC;"); try { return _access.getDataTable(sql.ToString()); } catch(Exception ex) { throw new Exception("PaperAnswerDao-->ExportSinglePaperAnswer" + ex.Message + sql.ToString(), ex); } }
/// <summary> /// 獲得填寫問卷的所有用戶 /// </summary> /// <param name="pc"></param> /// <returns></returns> public DataTable GetPaperAnswerUser(PaperAnswer pa) { StringBuilder sql = new StringBuilder(); sql.AppendFormat(@"SELECT DISTINCT(answerDate),userid,userMail,order_id FROM paper_answer WHERE paperID='{0}'", pa.paperID); sql.AppendFormat(@" ORDER BY answerDate DESC,userid ASC"); try { return _access.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("PaperAnswerDao-->GetPaperAnswerUser" + ex.Message + sql.ToString(), ex); } }