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