public static void Upload(object sessionId)
{
string searchSql = "SELECT `session_id`,`create_date`,`name`,`id_no`,`id_card_photo`,`take_photo`,`face_data`,`id_address`,`ticket_type`,`ticket_no`,`area`,`row`,`seat`,`tel_no`,`tel_area`,`buy_name`,`buy_photo`,`buy_date`,`validate_type`,`sync_time`,`status`,`remark` FROM in_sessions WHERE session_id = " + sessionId;
DataTable dt = MySqlDBHelper.ExecuteDataTable(entranceCon, searchSql);
string[] SQLStringList = new string[dt.Rows.Count];
MySqlParameter[][] mySqlParameters = new MySqlParameter[dt.Rows.Count][];
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
string insertSql = @"INSERT INTO in_sessions(`session_id`,`create_date`,`name`,`id_no`,`id_card_photo`,`take_photo`,`face_data`,`id_address`,`ticket_type`,`ticket_no`,`area`,`row`,`seat`,`tel_no`,`tel_area`,`buy_name`,`buy_photo`,`buy_date`,`validate_type`,`sync_time`,`status`,`remark`) VALUES(@session_id,@create_date,@name,@id_no,@id_card_photo,@take_photo,@face_data,@id_address,@ticket_type,@ticket_no,@area,@row,@seat,@tel_no,@tel_area,@buy_name,@buy_photo,@buy_date,@validate_type,@sync_time,@status,@remark)";
mySqlParameters[i] = new MySqlParameter[22];
mySqlParameters[i][0] = new MySqlParameter("session_id", row["session_id"]);
mySqlParameters[i][1] = new MySqlParameter("create_date", row["create_date"]);
mySqlParameters[i][2] = new MySqlParameter("name", row["name"]);
mySqlParameters[i][3] = new MySqlParameter("id_no", row["id_no"]);
mySqlParameters[i][4] = new MySqlParameter("id_card_photo", row["id_card_photo"]);
mySqlParameters[i][5] = new MySqlParameter("take_photo", row["take_photo"]);
mySqlParameters[i][6] = new MySqlParameter("face_data", row["face_data"]);
mySqlParameters[i][7] = new MySqlParameter("id_address", row["id_address"]);
mySqlParameters[i][8] = new MySqlParameter("ticket_type", row["ticket_type"]);
mySqlParameters[i][9] = new MySqlParameter("ticket_no", row["ticket_no"]);
mySqlParameters[i][10] = new MySqlParameter("area", row["area"]);
mySqlParameters[i][11] = new MySqlParameter("row", row["row"]);
mySqlParameters[i][12] = new MySqlParameter("seat", row["seat"]);
mySqlParameters[i][13] = new MySqlParameter("tel_no", row["tel_no"]);
mySqlParameters[i][14] = new MySqlParameter("tel_area", row["tel_area"]);
mySqlParameters[i][15] = new MySqlParameter("buy_name", row["buy_name"]);
mySqlParameters[i][16] = new MySqlParameter("buy_photo", row["buy_photo"]);
mySqlParameters[i][17] = new MySqlParameter("buy_date", row["buy_date"]);
mySqlParameters[i][18] = new MySqlParameter("validate_type", row["validate_type"]);
mySqlParameters[i][19] = new MySqlParameter("sync_time", row["sync_time"]);
mySqlParameters[i][20] = new MySqlParameter("status", row["status"]);
mySqlParameters[i][21] = new MySqlParameter("remark", row["remark"]);
SQLStringList[i] = insertSql;
}
MySqlDBHelper.ExecuteTransaction(centerCon, CommandType.Text, SQLStringList, mySqlParameters);
LogManager.WriteLog("上传比赛数据成功：" + dt.Rows.Count + "条数据");
}
/// <summary>
/// 同步黑名单
/// </summary>
public static void BlackName(int session_id)
{
LogManager.WriteLog("开始同步黑名单数据");
try
{
string searchSql = @"SELECT `id`,`session_id`,`create_date`,`buy_name`,`id_no`,`id_card_photo`, `year_ticket_photo`,`address`,`status`,`remark` FROM black_names where `session_id` = " + session_id;
DataTable dt = MySqlDBHelper.ExecuteDataTable(centerCon, searchSql);
string[] SQLStringList = new string[dt.Rows.Count];
MySqlParameter[][] mySqlParameters = new MySqlParameter[dt.Rows.Count][];
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
string insertSql = @"INSERT INTO black_names( `id`,`session_id`,`create_date`,`buy_name`,`id_no`,`id_card_photo`, `year_ticket_photo`,`address`,`status`,`remark`) VALUES(@id,@session_id,@create_date,@buy_name,@id_no,@id_card_photo,@year_ticket_photo,@address,@status,@remark)";
mySqlParameters[i] = new MySqlParameter[10];
mySqlParameters[i][0] = new MySqlParameter("id", row["id"]);
mySqlParameters[i][1] = new MySqlParameter("session_id", row["session_id"]);
mySqlParameters[i][2] = new MySqlParameter("create_date", row["create_date"]);
mySqlParameters[i][3] = new MySqlParameter("buy_name", row["buy_name"]);
mySqlParameters[i][4] = new MySqlParameter("id_no", row["id_no"]);
mySqlParameters[i][5] = new MySqlParameter("id_card_photo", row["id_card_photo"]);
mySqlParameters[i][6] = new MySqlParameter("year_ticket_photo", row["year_ticket_photo"]);
mySqlParameters[i][7] = new MySqlParameter("address", row["address"]);
mySqlParameters[i][8] = new MySqlParameter("status", row["status"]);
mySqlParameters[i][9] = new MySqlParameter("remark", row["remark"]);
SQLStringList[i] = insertSql;
}
string deleteSql = "DELETE FROM black_names WHERE session_id = " + session_id;
MySqlDBHelper.ExecuteNonQuery(entranceCon, CommandType.Text, deleteSql);
MySqlDBHelper.ExecuteTransaction(entranceCon, CommandType.Text, SQLStringList, mySqlParameters);
LogManager.WriteLog("同步黑名单数据成功：" + dt.Rows.Count + "条数据");
}
catch (Exception ex)
{
LogManager.WriteLog("同步黑名单数据异常：" + ex.Message);
}
}
/// <summary>
/// 同步白名单
/// </summary>
public static void WhiteName(int session_id, string session_name)
{
LogManager.WriteLog("开始同步白名单数据");
try
{
string searchSql = @"SELECT oi.CertificatePic,ProductName, CertificateName, oi.CertificateNo,CellPhone,AreaName,RowNum,SeatNum,TicketId ,o.Address
FROM himall_orderitems oi
INNER JOIN himall_orders o on o.Id = oi.OrderId
LEFT JOIN himall_orderseats os ON os.OrderId = o.Id
WHERE oi.ProductName = '" + session_name + "' AND (o.OrderStatus = 3 OR o.OrderStatus = 5) ";
DataTable dt = MySqlDBHelper.ExecuteDataTable(ticketCon, searchSql);
List <string> SQLStringList = new List <string>();
List <MySqlParameter[]> mySqlParameters = new List <MySqlParameter[]>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
string certificateName = row["CertificateName"].ToString();
string certificateNo = row["CertificateNo"].ToString();
string certificatePic = row["CertificatePic"].ToString();
if (!string.IsNullOrEmpty(certificateName))
{
string[] certificateNames = certificateName.Split(',');
string[] certificateNos = certificateNo.Split(',');
for (int j = 0; j < certificateNames.Length; j++)
{
string insertSql = @"insert INTO white_names(`session_id`,`create_date`,`buy_name`,`id_no`,`address`,`ticket_type`,
`ticket_no`,`area`,`row`,`seat`,`status`,`rrfeaturet`,`pic_url` ) VALUE
(@session_id,@create_date,@buy_name,@id_no,@address,@ticket_type,
@ticket_no,@area,@row,@seat,@status,@rrfeaturet,@pic_url)";
MySqlParameter[] sqlParameters = new MySqlParameter[13];
sqlParameters[0] = new MySqlParameter("session_id", session_id);
sqlParameters[1] = new MySqlParameter("create_date", DateTime.Now);
sqlParameters[2] = new MySqlParameter("buy_name", certificateNames[j]);
sqlParameters[3] = new MySqlParameter("id_no", certificateNos[j]);
sqlParameters[4] = new MySqlParameter("address", string.IsNullOrEmpty(row["Address"].ToString()) ? "" : row["Address"].ToString());
sqlParameters[5] = new MySqlParameter("ticket_type", 0);
sqlParameters[6] = new MySqlParameter("ticket_no", string.IsNullOrEmpty(row["TicketId"].ToString()) ? "" : row["TicketId"].ToString());
sqlParameters[7] = new MySqlParameter("area", string.IsNullOrEmpty(row["AreaName"].ToString()) ? "" : row["AreaName"].ToString());
sqlParameters[8] = new MySqlParameter("row", string.IsNullOrEmpty(row["RowNum"].ToString()) ? "" : row["RowNum"].ToString());
sqlParameters[9] = new MySqlParameter("seat", string.IsNullOrEmpty(row["SeatNum"].ToString()) ? "" : row["SeatNum"].ToString());
sqlParameters[10] = new MySqlParameter("status", 1);
sqlParameters[11] = new MySqlParameter("rrfeaturet", "");
sqlParameters[12] = new MySqlParameter("pic_url", string.IsNullOrEmpty(certificatePic) ? "" : certificatePic.Split(',')[j]);
mySqlParameters.Add(sqlParameters);
SQLStringList.Add(insertSql);
}
}
}
string deleteSql = "DELETE FROM white_names WHERE session_id = " + session_id;
MySqlDBHelper.ExecuteNonQuery(entranceCon, CommandType.Text, deleteSql);
bool succes = MySqlDBHelper.ExecuteTransaction(entranceCon, CommandType.Text, SQLStringList.ToArray(), mySqlParameters.ToArray());
if (succes)
{
LogManager.WriteLog("同步白名单数据成功：" + SQLStringList.Count + "条数据");
}
else
{
LogManager.WriteLog("同步白名单数据失败");
}
}
catch (Exception ex)
{
LogManager.WriteLog("同步白名单数据异常：" + ex.Message);
}
}