public static int addSignupUserItem(ref SignupUserItem item)
{
item.Name = item.Name.Replace(" ", "").Trim();
item.CardID = item.CardID.Replace(" ", "").Trim();
item.Mobile = item.Mobile.Replace(" ", "").Trim();
if (item.Name.Length == 0 || item.CardID.Length == 0 || item.Mobile.Length == 0)
{
throw new Exception("请填写您的姓名、身份证号码和手机号码！");
}
else if (item.CardID.Length > 0 && !Regex.Match(item.CardID, @"^\d{17}(\d|X)$", RegexOptions.IgnoreCase).Success)
{
throw new Exception("身份证号码格式不正确，请检查是否有误！");
}
else if (item.Mobile.Length > 0 && !Regex.Match(item.Mobile, @"^\d{11}$").Success)
{
throw new Exception("手机号码格式不正确，请检查是否有误！");
}
List <AnswerItem> answers = item.Answers;
List <QuestionItem> ques;
DbSelect.getQuestionList(out ques, null);
if (!ques.TrueForAll((QuestionItem q) => answers.Exists((AnswerItem ans) => ans.Question.ID == q.ID)))
{
throw new Exception("请确认是否已回答所有问题！");
}
List <SignupUserItem> list;
Dictionary <String, String[]> conditions = new Dictionary <String, String[]>();
conditions.Add("Name", new String[] { new Operator(OperatorTypeEnum.Equal), item.Name });
conditions.Add("CardID", new String[] { new Operator(OperatorTypeEnum.Equal), item.CardID });
conditions.Add("Mobile", new String[] { new Operator(OperatorTypeEnum.Equal), item.Mobile });
int ret = DbSelect.getSignupList(out list, conditions);
if (ret != OK)
{
return(FAILED);
}
if (list.Count == 0) // 无匹配用户
{
throw new Exception("考生信息不符，无法查询！");
}
else
{
item = list[0];
item.Answers = answers;
if (list[0].SignupSequence.Length != 0) // 已签到
{
return(CANCELLED);
}
}
return(DbEdit.updateSignupUserItem(ref item));
}
// 查询签到用户信息
public static int getSignupList(out List <SignupUserItem> list, Dictionary <String, String[]> conditions)
{
list = null;
try
{
using (SqlConnection conn = ConnectDb())
{
SqlParameter[] pms;
String sql = String.Format(sql_GetSignupList, getSignupUserItemFilter(conditions, out pms));
SqlCommand cmd = new SqlCommand(sql, conn);
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
SqlDataReader dr = cmd.ExecuteReader();
list = new List <SignupUserItem>();
while (dr.Read())
{
SignupUserItem item = new SignupUserItem();
item.ID = (int)dr["ID"];
item.SignupSequence = dr["SignupSequence"].ToString();
item.AssignID = dr["AssignID"].ToString();
item.Name = dr["Name"].ToString();
item.CardID = dr["CardID"].ToString();
item.Mobile = dr["Mobile"].ToString();
item.ImportTime = dr["ImportTime"].ToString();
item.SignupTime = dr["SignupTime"].ToString();
item.SignupPage = dr["SignupPage"].ToString();
List <AnswerItem> listAnswers;
getUserAnswerList(out listAnswers, item.ID);
item.Answers = listAnswers;
list.Add(item);
}
dr.Close();
dr.Dispose();
}
}
catch (Exception ex)
{
throw new Exception(String.Format("查询签到用户信息时出错：{0}", ex.Message));
}
return(OK);
}
// 导入待签到用户信息到数据库
public static int importSignupUserList(String excelFile, out int importedCount)
{
List <SignupUserItem> list = new List <SignupUserItem>();
importedCount = 0;
try
{
using (OleDbConnection conn = ConnectDb(excelFile))
{
// 获取可访问的表名
DataTableReader dtr = new DataTableReader(conn.GetSchema("Tables", new string[] { null, null, null, "TABLE" }));
String table_used;
if (dtr.Read())
{
table_used = dtr["TABLE_NAME"].ToString();
}
else
{
throw new Exception("该Excel文件不包含可用的表格。");
}
// 查询分组编号记录
OleDbCommand cmd = new OleDbCommand(String.Format(sql_ImportInterviewIDList_QueryAll, table_used), conn);
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SignupUserItem item = new SignupUserItem();
item.Name = dr[0].ToString();
item.CardID = dr[1].ToString();
item.Mobile = dr[2].ToString();
item.AssignID = dr[3].ToString();
list.Add(item);
}
dr.Close();
dr.Dispose();
}
}
catch (Exception ex)
{
throw new Exception(String.Format("读取Excel文件时出错：{0}", ex.Message));
}
importedCount = list.Count;
return(DbEdit.batchAddSignupUserItems(list));
}
// 写入用户签到信息并返回签到信息
public static int updateSignupUserItem(ref SignupUserItem item)
{
SqlConnection conn;
try
{
conn = ConnectDb();
// 更新签到记录
SqlParameter[] pms = new SqlParameter[] {
new SqlParameter("@Name", item.Name),
new SqlParameter("@Mobile", item.Mobile)
};
SqlCommand cmd = new SqlCommand(sql_UpdateSignupUserItem_Exec, conn);
cmd.Parameters.AddRange(pms);
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
sdr.Read();
item.SignupSequence = sdr.GetInt32(0).ToString();
item.SignupPage = sdr.GetString(1);
sdr.Close();
int user_id = item.ID;
item.Answers.ForEach((AnswerItem ans) =>
{
pms = new SqlParameter[] {
new SqlParameter("@UserID", user_id),
new SqlParameter("@QuestionID", ans.Question.ID),
new SqlParameter("@AnswerID", ans.ID)
};
cmd = new SqlCommand(sql_UpdateSignupUserItem_Exec2, conn);
cmd.Parameters.AddRange(pms);
cmd.ExecuteNonQuery();
});
}
catch (Exception ex)
{
throw new Exception(String.Format("写入签到信息时出错：{0}", ex.Message));
}
return(OK);
}
protected void Page_Load(object sender, EventArgs e)
{
SiteSettings.init(Server);
try
{
String person_name = Request.Form["person_name"];
String person_card_id = Request.Form["person_card_id"];
String person_mobile = Request.Form["person_mobile"];
String url_Signup = null;
SignupUserItem item = new SignupUserItem();
Application["lastRequestTime"] = DateTime.Now.Ticks;
Application.Lock();
item.Name = person_name;
item.CardID = person_card_id;
item.Mobile = person_mobile;
item.Answers = new List <AnswerItem>();
foreach (string key in Request.Form.Keys)
{
if (!key.StartsWith("ques"))
{
continue;
}
int q_id = Int32.Parse(key.Substring(4));
item.Answers.Add(new AnswerItem()
{
ID = Int32.Parse(Request.Form[key]),
Question = new QuestionItem()
{
ID = q_id
}
});
}
int ret = Core.addSignupUserItem(ref item);
Application.UnLock();
if (ret == Core.OK)
{
NameValueCollection vars = new NameValueCollection();
vars.Add("$person_name", item.Name);
vars.Add("$person_card_id", item.CardID);
vars.Add("$person_mobile", item.Mobile);
vars.Add("$assign_id", item.AssignID);
vars.Add("$signup_seq", item.SignupSequence);
ret = Misc.generateNoticePage(item.SignupPage, vars);
}
else if (ret == Core.CANCELLED)
{
url_Signup = item.SignupPage;
if (url_Signup.Length == 0)
{
throw new Exception("签到页面信息丢失！");
}
}
// 跳转到通知页
url_Signup = Misc.url(SiteSettings.SITE_ROOT, SiteSettings.DIR_GENERATED, item.SignupPage);
Response.Redirect(url_Signup, false);
}
catch (Exception ex)
{
Misc.doPageJump(this, "javascript:history.go(-1);", ex.Message);
}
}
