// 按签到序号清空指定用户的签到信息
public static int recoverSignupUserItem(int ID)
{
try
{
List <SignupUserItem> list;
Dictionary <String, String[]> conditions = new Dictionary <String, String[]>();
conditions.Add("ID", new String[] { new Operator(OperatorTypeEnum.Equal), ID.ToString() });
int ret = DbSelect.getSignupList(out list, conditions);
if (ret != OK)
{
throw new Exception(String.Format("不存在序号为\"{0}\"的记录。", ID));
}
using (SqlConnection conn = ConnectDb())
{
SqlCommand cmd = new SqlCommand(String.Format(sql_RecoverSignupUserItem_Update, ID), conn);
cmd.ExecuteNonQuery();
cmd = new SqlCommand(String.Format(sql_RecoverSignupUserItem_Delete, ID), conn);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(String.Format("清空指定用户的签到信息时出错：{0}", ex.Message));
}
return(OK);
}
// 导出签到名单到Excel文件
public static int exportSignupList(String excelFile, Dictionary <String, String[]> conditions)
{
try
{
List <QuestionItem> listQuestions;
List <SignupUserItem> list;
DbSelect.getQuestionList(out listQuestions, null);
// 读取签到名单
DbSelect.getSignupList(out list, conditions);
// 生成Excel文件
SpreadsheetClass factory = new SpreadsheetClass();
Worksheet sheet = factory.ActiveSheet;
List <string> columnHeaderNames = new List <string> {
"签到编号", "姓名", "身份证号码", "手机号码", "签到时间"
};
List <int> columnHeaderWidth = new List <int> {
10, 15, 12, 15
};
int columnHeaderQuestionWidth = 42;
listQuestions.ForEach((QuestionItem q) =>
{
columnHeaderNames.Add(q.Question);
columnHeaderWidth.Add(columnHeaderQuestionWidth);
});
sheet.Name = String.Format("签到名单({0}.{1})", DateTime.Today.Year, DateTime.Today.Month.ToString("00"));
// 字段名
for (int i = 0; i < columnHeaderNames.Count; i++)
{
Range col = sheet.get_Range((char)('A' + i) + "1");
sheet.Cells[1, i + 1] = columnHeaderNames[i];
col.set_ColumnWidth(columnHeaderWidth[i]);
col.set_HorizontalAlignment(XlHAlign.xlHAlignCenter);
col.Font.set_Bold(true);
}
// 记录
for (int i = 0, ROW_OFFSET = 2, COL_OFFSET = 1; i < list.Count; i++)
{
sheet.Cells[ROW_OFFSET + i, COL_OFFSET] = list[i].AssignID;
sheet.Cells[ROW_OFFSET + i, COL_OFFSET + 1] = list[i].Name;
sheet.Cells[ROW_OFFSET + i, COL_OFFSET + 2] = '\'' + list[i].CardID;
sheet.Cells[ROW_OFFSET + i, COL_OFFSET + 3] = '\'' + list[i].Mobile;
sheet.Cells[ROW_OFFSET + i, COL_OFFSET + 4] = list[i].SignupTime.ToString();
if (list[i].Answers.Count == listQuestions.Count)
{
for (int j = 0; j < listQuestions.Count; j++)
{
sheet.Cells[ROW_OFFSET + i, COL_OFFSET + j + 4] = list[i].Answers[j].Answer;
}
}
}
factory.Export(excelFile, SheetExportActionEnum.ssExportActionNone, SheetExportFormat.ssExportXMLSpreadsheet);
return(OK);
}
catch (Exception ex)
{
throw new Exception(String.Format("导出签到名单时出错：{0}", ex.Message));
}
}
// 查询签到用户调查问题回答信息
public static int getUserAnswerList(out List <AnswerItem> list, int user_id)
{
list = null;
try
{
if (listQuestions == null)
{
DbSelect.getQuestionList(out listQuestions, null);
}
using (SqlConnection conn = ConnectDb())
{
Dictionary <String, String[]> conditions = new Dictionary <String, String[]>();
conditions.Add("UserID", new String[] { new Operator(OperatorTypeEnum.Equal), user_id.ToString() });
SqlParameter[] pms;
String sql = String.Format(sql_GetUserAnswerList, getUserAnswerItemFilter(conditions, out pms));
SqlCommand cmd = new SqlCommand(sql, conn);
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
SqlDataReader dr = cmd.ExecuteReader();
list = new List <AnswerItem>();
while (dr.Read())
{
int q_id = (int)dr["QuestionID"];
int ans_id = (int)dr["AnswerID"];
QuestionItem ques = listQuestions.Find((QuestionItem q) =>
{
return(q.ID == q_id);
});
if (ques == null)
{
AnswerItem answer = new AnswerItem();
list.Add(answer);
}
else
{
AnswerItem answer = ques.Answers.Find((AnswerItem ans) =>
{
return(ans.ID == ans_id);
});
list.Add(answer);
}
}
dr.Close();
dr.Dispose();
}
}
catch (Exception ex)
{
throw new Exception(String.Format("查询签到用户调查问题回答信息时出错：{0}", ex.Message));
}
return(OK);
}