/// <summary>
/// 购票数据上传
/// </summary>
/// <param name="datetime"></param>
/// <param name="gameName"></param>
public static void Ticket(string datetime, string gameName)
{
LogManager.WriteLog("购买数据开始处理");
try
{
string sql = @"SELECT ProductName, CertificateName, oi.CertificateNo,CellPhone,AreaName,RowNum,SeatNum,TicketId
FROM himall_orderseats os
INNER JOIN himall_orders o ON os.OrderId = o.Id
INNER JOIN himall_orderitems oi ON o.Id = oi.OrderId
WHERE ProductName = '" + gameName + "' and OrderDate > '" + datetime + @"'";
//string sql = @"select DISTINCT oi.Id,oi.CertificateName,oi.CertificateNo,oi.CertificatePic,oi.ProductName ,
// os.TicketId,os.GameId,os.AreaName,os.RowNum,os.SeatNum,o.CellPhone
// from himall_orders o
// INNER JOIN himall_orderitems oi on o.id = oi.OrderId
// INNER JOIN himall_orderseats os on oi.CertificateNo = os.CertificateNo
// WHERE OrderDate > '" + DateTime.Now.ToString("yyyy-MM-dd") + @"' AND `OrderStatus` = 2 LIMIT 10";
DataTable dataTable = MySqlDBHelper.ExecuteDataTable(ticketCon, sql);
if (dataTable.Rows.Count > 0)
{
LogManager.WriteLog("本次上次购买数据数量为" + dataTable.Rows.Count + "条");
Ticket ticket = new Ticket()
{
verification = verification, dataVoList = new List <TicketInfo>()
};
foreach (DataRow item in dataTable.Rows)
{
ticket.dataVoList.Add(new TicketInfo()
{
activityId = serverGameName,
purchaserName = item["CertificateName"].ToString(),
purchaserIdCardNo = item["CertificateNo"].ToString(),
purchaserTel = item["CellPhone"].ToString(),
purchaserSeatNo = item["AreaName"].ToString() + item["RowNum"].ToString() + "排" + item["SeatNum"].ToString() + "号",
ticketNo = item["TicketId"].ToString(),
});
}
string url = URL + "/api/ticket";
string ticketdata = Newtonsoft.Json.JsonConvert.SerializeObject(ticket);
string msg = HttpPost(url, ticketdata);
LogManager.WriteLog("购买数据上传完成");
LogManager.WriteLog("购买数据上传返回结果：" + msg);
}
else
{
LogManager.WriteLog("无购买数据上传");
}
}
catch (Exception ex)
{
LogManager.WriteLog("购买数据上传异常" + ex.Message);
}
}
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);
}
}
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 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);
}
}
