#!/usr/bin/python3
import os
import sys
import re
import pymysql
import time
import logging
import pandas as pd
import requests
from clickhouse_driver import Client
"""
统计佛山市市级卡口的港澳过车总数，识别率及格的总数
"""
if __name__ == '__main__':
logging.basicConfig(filename=os.path.dirname(os.path.abspath(__file__)) + "/pn_recognize_fail.log",level=logging.DEBUG)
try:
cursor = Client(host='68.109.211.36', port=9001, password='Yisa_fs_2021')
except:
logging.info("lighting连接失败！")
sys.exit(1)
ct = "date >= '2022-07-25' and date < '2022-07-27'"
#ct = "date = '2022-07-20'"
sql = "select license_plate2,xgbdp,ambdp,viid_object_id,location_id,capture_time,image_url1,location_id from yisa_oe.vehicle_all where (license_plate2 like '粤Z%澳' or license_plate2 LIKE '粤Z%港') and " + ct
try:
results = cursor.execute(sql)
sql = "select count(*) from yisa_oe.vehicle_all where license_plate2 LIKE '粤Z%港' and " + ct
xg_len = cursor.execute(sql)
sql = "select count(*) from yisa_oe.vehicle_all where license_plate2 LIKE '粤Z%澳' and " + ct
am_len = cursor.execute(sql)
except:
logging.error("lighting语句执行错误！")
sys.exit(1)
try:
mysql_db = pymysql.connect(host='68.109.211.67',user='yisa_oe',password='Yisa_fs_2021',database='yisa_oe')
except:
logging.info("mysql连接失败！")
sys.exit(1)
pn_list = [] # 元素是列表，0:卡口名称，1：卡口id，2：香港内地牌，3：香港本地牌，4：澳门内地牌，5：澳门本地牌，6：香港识别率，7：澳门识别率
am_recognize = 0
for row in results:
row_list = list(row)
tmp_list = ['','',0,0,0,0,0,0,0]
localtion_id = int(row_list[7])
cursor = mysql_db.cursor()
if row_list[0]: # 二次识别成功
if re.findall(r"澳+",row_list[0]): # 二次识别是澳牌
tmp_list[4] = 1
try:
sql = "select pointname,PROVIDER from location where id = {};".format(localtion_id)
#print(sql)
cursor.execute(sql)
result = cursor.fetchall()
if result:
pn = result[0][0]
tmp_list[0] = pn
tmp_list[1] = result[0][1]
else:
pn = '缺失点位'
continue
except:
logging.error("mysql语句执行错误！")
sys.exit(1)
if row_list[2]: #识别澳门本地牌成功
tmp_list[5] = 1
if re.findall(r"港+",row_list[0]):
tmp_list[2] = 1
try:
sql = "select pointname,PROVIDER from location where id = {};".format(localtion_id)
#print(sql)
cursor.execute(sql)
result = cursor.fetchall()
if result:
pn = result[0][0]
tmp_list[0] = pn
tmp_list[1] = result[0][1]
else:
pn = '缺失点位'
continue
except:
logging.error("mysql语句执行错误！")
sys.exit(1)
if row_list[1]: #识别香港本地牌成功
tmp_list[3] = 1
if pn_list:
flag = 0
for i in range(len(pn_list)):
if tmp_list[0] in pn_list[i]:
pn_list[i][2] = tmp_list[2] + pn_list[i][2]
pn_list[i][3] = tmp_list[3] + pn_list[i][3]
pn_list[i][4] = tmp_list[4] + pn_list[i][4]
pn_list[i][5] = tmp_list[5] + pn_list[i][5]
flag = 1
if flag == 0: #pn_list没有这个卡口
pn_list.append(tmp_list)
else:
pn_list.append(tmp_list)
for i in range(len(pn_list)):
#print(pn_list[i][2],pn_list[i][3],pn_list[i][4],pn_list[i][5])
flag1 = 0
flag2 = 0
pn_list[i][6] = pn_list[i][2] + pn_list[i][4]
if pn_list[i][2] == 0:
pn_list[i][7] = 0
flag1 = 1
if pn_list[i][4] == 0:
pn_list[i][8] = 0
flag2 = 1
if flag1 == 0:
a = (pn_list[i][3] / pn_list[i][2]) * 100
xg_recognize_rate = round(a ,2)
pn_list[i][7] = xg_recognize_rate
pn_list[i][7]
if flag2 == 0:
b = (pn_list[i][5] / pn_list[i][4]) * 100
am_recognize_rate = round(b ,2)
pn_list[i][8] = am_recognize_rate
sort_pn_list = sorted(pn_list,key=(lambda x:x[6]),reverse=True)
df = pd.DataFrame(sort_pn_list,columns=['卡口名称','卡口ID','香港内地牌','香港本地牌','澳门内地牌','澳门本地牌','港澳过车总数','香港识别率','澳门识别率'])
#df.to_csv('pn_recognize_fail.csv',index=False)
#df2 = pd.read_excel('/home/mypy/SJKK.xlsx')
df2 = pd.read_excel('/home/mypy/vehicle_barrier_province_export.xlsx')
df['卡口ID'] = df['卡口ID'].astype(int)
#print('df_type: {}'.format(df['卡口ID'].dtypes))
#print('df2_type: {}'.format(df2['proid'].dtypes))
df3 = df[df['卡口ID'].isin(df2['proid'].values)]
l = []
for i in df2['proid'].values:
tmp_list = [['无港澳过车数据',0,0,0,0,0,0,0,0]]
if i not in df3['卡口ID'].values:
#l.append(i) # 港澳过车总数是0的卡口proid
sql = "select pointname,PROVIDER from location where PROVIDER = '{}';".format(i)
cursor.execute(sql)
result = cursor.fetchall()
if not result:
print('不存在点位名称的proid: {}'.format(i))
pn = '未找到点位名称'
else:
pn = result[0][0]
tmp_list[0][0] = pn
tmp_list[0][1] = i
tmp_df = pd.DataFrame(tmp_list,columns=['卡口名称','卡口ID','香港内地牌','香港本地牌','澳门内地牌','澳门本地牌','港澳过车总数','香港识别率','澳门识别率'])
df3 = df3.append(tmp_df,ignore_index = True)
print(df3.dtypes)
#df3.to_csv('0701.csv',index=False)
count_fs_list = [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
# 0:卡口总数，1:粤Z澳过车总数,2:澳本地牌总数，3:澳本地牌识别率,4:有澳车过的卡口总数，5:澳本地牌识别率大于0%的卡口数量，6:占比,7:澳本地牌识别率大于92%的卡口数量，8:占比
# 9:粤Z港过车总数，10:港本地牌总数，11:港本地牌识别率，12:有港车过的卡口总数，13:港本地牌识别率大于0%的卡口数量，14:占比，15,港本地牌识别率大于92%的卡口数量，16:占比
count_fs_list[0] = df3.shape[0]
count_fs_list[1] = df3.sum()['澳门内地牌']
count_fs_list[2] = df3.sum()['澳门本地牌']
count_fs_list[3] = str(round((count_fs_list[2] / count_fs_list[1] * 100),2)) + '%'
count_fs_list[4] = df3[df3['澳门内地牌'] > 0].shape[0]
count_fs_list[5] = df3[df3['澳门识别率'] > 0].shape[0]
count_fs_list[6] = str(round((count_fs_list[5] / count_fs_list[4] * 100),2)) + '%'
count_fs_list[7] = df3[df3['澳门识别率'] > 92].shape[0]
count_fs_list[8] = str(round((count_fs_list[7] / count_fs_list[4] * 100),2)) + '%'
count_fs_list[9] = df3.sum()['香港内地牌']
count_fs_list[10] = df3.sum()['香港本地牌']
count_fs_list[11] = str(round((count_fs_list[10] / count_fs_list[9] * 100),2)) + '%'
count_fs_list[12] = df3[df3['香港内地牌'] > 0].shape[0]
count_fs_list[13] = df3[df3['香港识别率'] > 0].shape[0]
count_fs_list[14] = str(round((count_fs_list[13] / count_fs_list[12] * 100),2)) + '%'
count_fs_list[15] = df3[df3['香港识别率'] > 92].shape[0]
count_fs_list[16] = str(round((count_fs_list[15] / count_fs_list[12] * 100),2)) + '%'
count_fs_to_df = []
count_fs_to_df.append(count_fs_list)
count_fs_df = pd.DataFrame(count_fs_to_df,columns=['卡口总数','粤Z澳过车总数','澳本地牌总数','澳本地牌识别率','有澳车过的卡口总数','澳本地牌识别率大于0%的卡口数量','占比','澳本地牌识别率大于92%的卡口数量','占比','粤Z港过车总数','港本地牌总数','港本地牌识别率','有港车过的卡口总数','港本地牌识别率大于0%的卡口数量','占比','港本地牌识别率大于92%的卡口数量','占比'])
count_fs_df.to_csv('./csv/count_fs_20.csv',index=False)
print(count_fs_df.head())
#print(l)