public int UpdateClassID(PaperClass pc)
{
try
{
return _paperClassDao.UpdateClassID(pc);
}
catch (Exception ex)
{
throw new Exception("PaperClassMgr-->UpdateClassID" + ex.Message, ex);
}
}
public int Delete(PaperClass pc)
{
try
{
return _paperClassDao.Delete(pc);
}
catch (Exception ex)
{
throw new Exception("PaperClassMgr-->Delete" + ex.Message, ex);
}
}
public List<PaperClass> GetPaperClassList(PaperClass pc, out int totalCount)
{
try
{
return _paperClassDao.GetPaperClassList(pc, out totalCount);
}
catch (Exception ex)
{
throw new Exception("PaperClassMgr-->GetPaperClassList" + ex.Message, ex);
}
}
public int Add(PaperClass pc)
{
try
{
return _paperClassDao.Add(pc);
}
catch (Exception ex)
{
throw new Exception("PaperClassMgr-->Add" + ex.Message, ex);
}
}
public DataTable GetPaperClassID(PaperClass pc)
{
try
{
return _paperAnswerDao.GetPaperClassID(pc);
}
catch (Exception ex)
{
throw new Exception("PaperAnswerMgr-->GetPaperClassID" + ex.Message, ex);
}
}
public int Add(PaperClass pc)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(@"INSERT INTO paper_class (paperID,classID,className,classType,projectNum,");
sql.AppendLine(@"classContent,orderNum,isMust,`status` ) VALUES(");
sql.AppendFormat(@"'{0}','{1}','{2}','{3}','{4}',", pc.paperID, pc.classID, pc.className, pc.classType, pc.projectNum);
sql.AppendFormat(@"'{0}','{1}','{2}','{3}')", pc.classContent, pc.orderNum, pc.isMust, pc.status);
try
{
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("PaperClassDao-->Add" + ex.Message + sql.ToString(), ex);
}
}
public List<PaperClass> GetPaperClassList(PaperClass pc, out int totalCount)
{
StringBuilder sqlfield = new StringBuilder();
StringBuilder sqlfrom = new StringBuilder();
StringBuilder sqlwhere = new StringBuilder();
StringBuilder sql = new StringBuilder();
StringBuilder sqlcount = new StringBuilder();
sqlfield.AppendLine(@"SELECT pc.id,pc.paperID,pc.classID,pc.className,pc.classType,pc.projectNum,");
sqlfield.AppendLine(@"pc.classContent,pc.orderNum,pc.isMust,pc.`status`");
sql.Append(sqlfield);
sql.AppendFormat(@" ,p.paperName ");
sqlfrom.AppendFormat(@" FROM paper_class pc ");
sqlfrom.AppendFormat(@" LEFT JOIN paper p ON p.paperID=pc.paperID WHERE 1=1 ");
sqlcount.Append(" select count(pc.paperID) as totalCount ");
sql.Append(sqlfrom);
if (pc.paperID != 0)
{
sqlwhere.AppendFormat(@" AND pc.paperID='{0}' ", pc.paperID);
}
if (pc.classID != 0)
{
sqlwhere.AppendFormat(@" AND pc.classID='{0}' ", pc.classID);
}
sql.Append(sqlwhere);
sql.AppendFormat(" order by pc.paperID DESC,pc.classID ASC,pc.orderNum ASC ");
totalCount = 0;
if (pc.IsPage)
{
sqlcount.Append(sqlfrom.ToString() + sqlwhere.ToString());
sql.AppendFormat(@" limit {0},{1} ", pc.Start, pc.Limit);
DataTable dt = _access.getDataTable( sqlcount.ToString() );
if (dt != null && dt.Rows.Count > 0)
{
totalCount =Convert.ToInt32(dt.Rows[0]["totalCount"]);
}
}
try
{
return _access.getDataTableForObj<PaperClass>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("PaperClassDao-->GetPaperClassList" + ex.Message + sql.ToString() + sqlcount.ToString(), ex);
}
}
public int Update(PaperClass pc)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@"UPDATE paper_class SET ");
sql.AppendFormat(@" paperID='{0}',classID='{1}',className='{2}',", pc.paperID, pc.classID, pc.className);
sql.AppendFormat(@" classType='{0}',projectNum='{1}',isMust='{2}', ", pc.classType, pc.projectNum, pc.isMust);
sql.AppendFormat(@" classContent='{0}',orderNum='{1}' ", pc.classContent, pc.orderNum);
sql.AppendFormat(@" WHERE id='{0}' ;", pc.id);//根據id來更新題目選項
try
{
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("PaperClassDao-->Update" + ex.Message + sql.ToString(), 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 HttpResponseBase PaperClassDelete()
{
string json = string.Empty;
PaperClass pc = new PaperClass();
_paperClassMgr = new PaperClassMgr(mySqlConnectionString);
try
{
if (!string.IsNullOrEmpty(Request.Params["id"]))
{
pc.id = int.Parse(Request.Params["id"]);
}
if (_paperClassMgr.Delete(pc) > 0)
{
json = "{success:true}";
}
}
catch (Exception ex)
{
json = "{success:false,msg:\"" + "異常" + "\"}";
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);
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase PaperClassEdit()
{
string json = string.Empty;
PaperClass pc = new PaperClass();
_paperClassMgr = new PaperClassMgr(mySqlConnectionString);
List<PaperClass> store = new List<PaperClass>();
List<PaperClass> oldstore = new List<PaperClass>();
bool success = false;
try
{
PaperClass pcc = new PaperClass();
if (!string.IsNullOrEmpty(Request.Params["paper_id"]))
{
pc.paperID = int.Parse(Request.Params["paper_id"]);//新的問卷編號
pcc.paperID = int.Parse(Request.Params["paper_id"]);
}
int totalCount = 0;
pcc.IsPage = false;
store = _paperClassMgr.GetPaperClassList(pcc, out totalCount);//新的問卷編號查出來的數據
pcc = new PaperClass();
pcc.IsPage = false;
if (!string.IsNullOrEmpty(Request.Params["old_paper_id"]))
{
pcc.paperID = int.Parse(Request.Params["old_paper_id"]);//舊的問卷編號
}
string type = Request.Params["type"];
//設定題目編號 規則 paperID+四位自增數字（0001）
if (String.IsNullOrEmpty(Request.Params["class_id"]))
{
if (store.Count > 0)
{
int classid = store.Max(e => e.classID);
pc.classID = classid + 1;
}
else
{
pc.classID = int.Parse(pc.paperID.ToString() + "0001");
}
}
else
{
//修改問卷的話，題目編號也要相應變動
if (pc.paperID != pcc.paperID)
{
if (store.Count > 0)
{
int classid = store.Max(e => e.classID);
pc.classID = classid + 1;
}
else
{
pc.classID = int.Parse(pc.paperID.ToString() + "0001");
}
}
else
{
pc.classID = int.Parse(Request.Params["class_id"]);
}
}
pc.className = Request.Params["class_name"];
pc.classType = Request.Params["class_type"];
//pc.bannerUrl = Request.Params["banner_url"];
if (!string.IsNullOrEmpty(Request.Params["project_num"]))
{
pc.projectNum = int.Parse(Request.Params["project_num"]);
}
if (!string.IsNullOrEmpty(Request.Params["ismust"]))
{
pc.isMust = int.Parse(Request.Params["ismust"]);
}
if (!string.IsNullOrEmpty(Request.Params["pcontent"]))
{
string content = Request.Params["pcontent"].ToString();
string[] contents = content.Split(';');
string[] c;
for (int i = 0; i < contents.Length - 1; i++)
{
c = contents[i].Split(',');
pc.classContent = c[0].ToString();
pc.orderNum = int.Parse(c[1].ToString());
pc.id = int.Parse(c[2].ToString());
#region 新增
if (String.IsNullOrEmpty(Request.Params["class_id"]))
{
if (_paperClassMgr.Add(pc) > 0)
{
success = true;
}
else
{
success = false;
}
}
#endregion
#region 編輯
else
{
if (pc.id != 0)
{
if (_paperClassMgr.Update(pc) > 0)
{
success = true;
}
else
{
success = false;
}
}
//編輯時新增
else
{
if (_paperClassMgr.Add(pc) > 0)
{
success = true;
}
else
{
success = false;
}
}
}
#endregion
}
}
//題目類型選擇的不是單選多選時
else
{
#region 新增
if (String.IsNullOrEmpty(Request.Params["class_id"]))
{
if (_paperClassMgr.Add(pc) > 0)
{
success = true;
}
else
{
success = false;
}
}
#endregion
#region 編輯
else
{
if (!string.IsNullOrEmpty(Request.Params["id"]))
{
pc.id = int.Parse(Request.Params["id"]);
}
if (_paperClassMgr.Update(pc) > 0)
{
success = true;
}
else
{
success = false;
}
}
#endregion
}
//如果選擇新的問卷,就重組舊問卷的題目編號
if (Request.Params["paper_id"] != Request.Params["old_paper_id"])
{
if (!string.IsNullOrEmpty(Request.Params["class_id"]))
{
oldstore = _paperClassMgr.GetPaperClassList(pcc, out totalCount);//舊的問卷編號查出來的數據
int class_id = int.Parse(Request.Params["class_id"]);
foreach (var item in oldstore)
{
//一個問卷中，如果舊的題目編號小於當前的題目編號
if (item.classID > class_id)
{
item.classID = item.classID - 1;
_paperClassMgr.UpdateClassID(item);
}
}
}
}
if (success)
{
json = "{success:true}";
}
else
{
json = "{success:false}";
}
}
catch (Exception ex)
{
json = "{success:false,msg:\"" + "異常" + "\"}";
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);
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetPaperClassList()
{
string json = string.Empty;
List<PaperClass> store = new List<PaperClass>();
PaperClass pc = new PaperClass();
try
{
pc.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
pc.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
_paperClassMgr = new PaperClassMgr(mySqlConnectionString);
int totalCount = 0;
if (!string.IsNullOrEmpty(Request.Params["paper_id"]))
{
pc.paperID = int.Parse(Request.Params["paper_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["class_id"]))
{
pc.classID = int.Parse(Request.Params["class_id"]);
}
store = _paperClassMgr.GetPaperClassList(pc, out totalCount);
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;
}
/// <summary>
/// 根據id來刪除一條數據
/// </summary>
/// <param name="pc"></param>
/// <returns></returns>
public int Delete(PaperClass pc)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@"DELETE FROM paper_class WHERE id='{0}';", pc.id);
try
{
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("PaperClassDao-->Delete" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 更改問卷后，再重組原來的問卷的題目編號
/// </summary>
/// <param name="pc"></param>
/// <returns></returns>
public int UpdateClassID(PaperClass pc)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@"UPDATE paper_class SET ");
sql.AppendFormat(@" classID='{0}' ", pc.classID);
sql.AppendFormat(@" WHERE id='{0}' ;", pc.id);//根據id來更新題目選項
try
{
return _access.execCommand(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("PaperClassDao-->UpdateClassID" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
/// 獲得問卷的所有題目編號
/// </summary>
/// <param name="pc"></param>
/// <returns></returns>
public DataTable GetPaperClassID(PaperClass pc)
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat(@"SELECT DISTINCT(classID),classType FROM paper_class WHERE paperID='{0}' ORDER BY classID ASC;", pc.paperID);
try
{
return _access.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("PaperAnswerDao-->GetPaperClassID" + ex.Message + sql.ToString(), ex);
}
}