public static void Ticket(int sessionId, string gameName) { LogManager.WriteLog("购买数据开始处理"); try { string sql = "SELECT * FROM white_names where session_id = '" + sessionId + "' and isupload = 0 "; DataTable dataTable = MySqlDBHelper.ExecuteDataTable(PoliceDataFactory.entranceCon, sql); bool flag = dataTable.Rows.Count > 0; if (flag) { LogManager.WriteLog("本次上次购买数据数量为" + dataTable.Rows.Count + "条"); Ticket ticket = new Ticket { verification = verification, dataVoList = new List <TicketInfo>() }; foreach (object obj in dataTable.Rows) { DataRow item = (DataRow)obj; ticket.dataVoList.Add(new TicketInfo { activityId = gameName, purchaserName = item["buy_name"].ToString(), purchaserIdCardNo = item["id_no"].ToString(), purchaserTel = item["purchaser_tel"].ToString(), purchaserSeatNo = string.Concat(new string[] { item["area"].ToString(), item["row"].ToString(), "排", item["seat"].ToString(), "号" }), ticketNo = item["ticket_no"].ToString() }); } string url = URL + "/api/ticket"; string ticketdata = JsonConvert.SerializeObject(ticket); LogManager.WriteLog("购买上传数据:" + ticketdata); string msg = PoliceDataFactory.HttpPost(url, ticketdata, null); LogManager.WriteLog("购买数据上传完成"); LogManager.WriteLog("购买数据上传返回结果:" + msg); LogManager.WriteLog("更新上传数据状态"); string updatesql = "update white_names set isupload=1 where session_id = '" + sessionId + "'"; MySqlDBHelper.ExecuteNonQuery(PoliceDataFactory.entranceCon, CommandType.Text, updatesql); } else { LogManager.WriteLog("无购买数据上传"); } } catch (Exception ex) { LogManager.WriteLog("购买数据上传异常" + ex.Message); } }
/// <summary> /// 检票数据上传 /// </summary> public static void Entrance(int sessionId, string gameName) { LogManager.WriteLog("检票数据开始上传"); try { string sql = "SELECT DISTINCT s.session_id,s.tel_no,s.tel_area,s.ticket_no,s.id_no, s.buy_name,s.create_date from in_sessions s where session_id = '" + sessionId + "' and isupload = 0"; DataTable dataTable = MySqlDBHelper.ExecuteDataTable(entranceCon, sql); Entrance entrance = new Entrance() { verification = verification, dataVoList = new List <EntranceInfo>() }; foreach (DataRow item in dataTable.Rows) { entrance.dataVoList.Add(new EntranceInfo() { activityId = serverGameName, entranceCode = item["tel_no"].ToString(), entranceName = item["tel_area"].ToString(), ticketNumber = 1, certificationNumber = 0, ticketNo = item["ticket_no"].ToString(), ticketHolderIdCardNo = item["id_no"].ToString(), ticketHolderName = item["buy_name"].ToString(), entranceFacePic = "", abnormalNumber = 0, entranceTime = Convert.ToDateTime(item["create_date"].ToString()) }); } string url = URL + "/api/entrance"; string ticketdata = Newtonsoft.Json.JsonConvert.SerializeObject(entrance); LogManager.WriteLog("检票上传数据:" + ticketdata); string msg = PoliceDataFactory.HttpPost(url, ticketdata, null); LogManager.WriteLog("检票数据上传完成"); LogManager.WriteLog("检票数据上传返回结果:" + msg); LogManager.WriteLog("更新上传数据状态"); string updatesql = "update in_sessions set isupload=1 where session_id = '" + sessionId + "'"; MySqlDBHelper.ExecuteNonQuery(PoliceDataFactory.entranceCon, CommandType.Text, updatesql); } catch (Exception ex) { LogManager.WriteLog("检票数据上传异常" + ex.Message); } }
/// <summary> /// 告警数据上传 /// </summary> public static void InspectTicket(int sessionId, string gameName) { LogManager.WriteLog("告警数据开始上传"); try { string sql = "SELECT id,session_id,buy_name,id_no,id_card_photo,year_ticket_photo,address,status,remark from black_names where session_id = '" + sessionId + "' and isupload = 0"; DataTable dataTable = MySqlDBHelper.ExecuteDataTable(entranceCon, sql); InspectTicket inspectTicket = new InspectTicket() { verification = verification, dataVoList = new List <InspectTicketInfo>() }; foreach (DataRow item in dataTable.Rows) { inspectTicket.dataVoList.Add(new InspectTicketInfo() { taskId = serverGameName, warningTime = DateTime.Now, warningPosition = IrAdvanced.ReadString("TelArea"), ticketWarningType = 1, ticketNo = item["id"].ToString(), ticketHolderIdCardNo = item["id_no"].ToString(), ticketHolderName = item["buy_name"].ToString(), entranceFacePic = item["remark"].ToString() }); } string url = URL + "/api/inspectTicket"; string data = JsonConvert.SerializeObject(inspectTicket); LogManager.WriteLog("告警上传数据:" + data); string msg = PoliceDataFactory.HttpPost(url, data, null); LogManager.WriteLog("告警数据上传完成"); LogManager.WriteLog("告警数据上传返回结果:" + msg); LogManager.WriteLog("更新上传数据状态"); string updatesql = "update black_names set isupload=1 where session_id = '" + sessionId + "'"; MySqlDBHelper.ExecuteNonQuery(PoliceDataFactory.entranceCon, CommandType.Text, updatesql); } catch (Exception ex) { LogManager.WriteLog("检票数据上传异常" + ex.Message); } }
/// <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 PoliceModel.SessionModel KeyTable() { LogManager.WriteLog("开始同步比赛数据"); int session_id = 0; PoliceModel.SessionModel session = new PoliceModel.SessionModel(); try { string searchSql = @"select * FROM sessions WHERE `status` = 0 "; DataTable sessiondt = MySqlDBHelper.ExecuteDataTable(centerCon, searchSql); if (sessiondt.Rows.Count > 0) { DataRow sessiondataRow = sessiondt.Rows[0]; session_id = Convert.ToInt32(sessiondataRow["session_id"]); session.Id = session_id; session.Name = sessiondataRow["session_name"].ToString(); LogManager.WriteLog("比赛场次:" + sessiondataRow["session_name"].ToString()); string deleteSql = "DELETE FROM key_table where session_id =" + session_id + "; DELETE FROM sessions where session_id =" + session_id + ";"; MySqlDBHelper.ExecuteNonQuery(entranceCon, CommandType.Text, deleteSql); string sessionInsertSql = @"INSERT INTO `sessions` (`session_id`,`create_date`,`session_name`,`session_date`,`date_start`,`date_end`,`check_rule`,`status`,`remark`) VALUES (@session_id,@create_date,@session_name,@session_date,@date_start,@date_end,@check_rule,@status,@remark)"; MySqlParameter[] mySqlParameters = new MySqlParameter[9]; mySqlParameters[0] = new MySqlParameter("session_id", sessiondataRow["session_id"]); mySqlParameters[1] = new MySqlParameter("create_date", sessiondataRow["create_date"]); mySqlParameters[2] = new MySqlParameter("session_name", sessiondataRow["session_name"]); mySqlParameters[3] = new MySqlParameter("session_date", sessiondataRow["session_date"]); mySqlParameters[4] = new MySqlParameter("date_start", sessiondataRow["date_start"]); mySqlParameters[5] = new MySqlParameter("date_end", sessiondataRow["date_end"]); mySqlParameters[6] = new MySqlParameter("check_rule", sessiondataRow["check_rule"]); mySqlParameters[7] = new MySqlParameter("status", sessiondataRow["status"]); mySqlParameters[8] = new MySqlParameter("remark", sessiondataRow["remark"]); MySqlDBHelper.ExecuteNonQuery(entranceCon, CommandType.Text, sessionInsertSql, mySqlParameters); string keysearchSql = @"select * FROM key_table WHERE `session_id` = " + session_id; DataTable keytabledt = MySqlDBHelper.ExecuteDataTable(centerCon, keysearchSql); if (keytabledt.Rows.Count > 0) { DataRow row = keytabledt.Rows[0]; string keytableInsertSql = @"INSERT INTO `key_table` (`key_id`,`create_date`,`session_id`,`key_content`,`key_type`,`remark`) VALUES (@key_id,@create_date,@session_id,@key_content,@key_type,@remark)"; MySqlParameter[] keySqlParameters = new MySqlParameter[6]; keySqlParameters[0] = new MySqlParameter("key_id", row["key_id"]); keySqlParameters[1] = new MySqlParameter("create_date", row["create_date"]); keySqlParameters[2] = new MySqlParameter("session_id", row["session_id"]); keySqlParameters[3] = new MySqlParameter("key_content", row["key_content"]); keySqlParameters[4] = new MySqlParameter("key_type", row["key_type"]); keySqlParameters[5] = new MySqlParameter("remark", row["remark"]); MySqlDBHelper.ExecuteNonQuery(entranceCon, CommandType.Text, keytableInsertSql, keySqlParameters); } } LogManager.WriteLog("同步比赛数据完成"); } catch (Exception ex) { LogManager.WriteLog("同步比赛数据异常:" + ex.Message); } return(session); }
/// <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); } }