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