import datetime,requests,schedule
import re,os,sys,time,yaml,shutil
import calendar
import pandas as pd
from jinja2 import Template
from chinese_calendar import is_workday
from sqlalchemy import create_engine
root_path = os.path.dirname(os.path.dirname(os.path.realpath(__file__)))
sys.path.append(root_path)
print(os.path.realpath(__file__))
print(os.path.dirname(os.path.realpath(__file__)))
print(os.path.dirname(os.path.dirname(os.path.realpath(__file__))))
from view.a_env_path import pathConf
host = '43.139.173.35'
port = 3306
user = 'bonade'
passwd = 'root'
db = 'bonade'
web_Host = "10.2.29.44:80"
# wx_key = "bb411aa7-de56-41ff-9b23-75de594ba0db" # 3
# wx_key = "fac5fe55-5895-401d-9d14-368fdbc18d9b" # 群机器人调试
# wx_key = "eab11a24-3f64-46cd-92de-7c2a8ac2deb2" # 自动化报告群
wx_key = "55c2ba04-a047-43d1-9e8d-3b1d3e3518e4" # 测试管理中心消息推送
# wx_key = "8f22bacb-7cff-4426-9697-8390321baf1f"
Z_end_time_rict = 0 # 是否剔除 Z_end_time 为空的数据，0 不踢除， 1 剔除
class YamlRead(object):
def __init__(self,yamlPath):
self.yamlPath = yamlPath
with open(yamlPath, mode='r', encoding='utf8') as file_config:
config_dict = yaml.load(file_config.read(), Loader=yaml.FullLoader)
# print(config_dict)
self.yamldata = config_dict
def getdata(self):
""" 返回一个字典 """
# print(self.yamldata)
return self.yamldata
def write_data(self,data):
""" 写入yaml """
with open(self.yamlPath, "a+", encoding="utf-8") as f:
yaml.dump(data, f)
f.close()
pd.set_option('display.width', None)
# 用sqlalchemy构建数据库链接engine
connect_info = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'
engine_info1 = 'mysql+pymysql://%s:%s@%s:%s/%s?charset=utf8' % (user,passwd,host,str(port),db)
engine = create_engine(engine_info1)
# sql 命令
sql_cmd = "SELECT * FROM airui"
df = pd.read_sql(sql=sql_cmd, con=engine)
df = df[(df['report7z'].notna()) & (df['Z_end_time'].notna())] # 剔除没有报告的
# print(df)
# # for k,v in df.groupby(['author']):
# # print("key:",k)
# # print(v)
#
# # 取分组后的元素：
# # 方法一： 先转为list，然后找到第几个元素
# df_list = list(df.groupby(['author'])) # 返回二维列表
# # print(df_list[0][0])
# # print(type(df_list[0]))
# # 方法二： 转为dict后取出对应的key的值
# df_dict = dict(df_list)
# # print(df_dict['yu下']) # 如果分组是2个字段去值： df_dict[('王静', 'rpa')]
# # print(type(df_dict['yu下'])) # <class 'pandas.core.frame.DataFrame'>
# # print(df_dict['星程2'].totalnum.sum()) # 对 星程2 这个组的 totalnum 字段求和
#
# # 方法三： 利用 get_group(key)的方法直接取出对应的key的值
# # grouped = df.groupby(['author']).get_group('星程2') # 取出星程2 这个组
# # print(grouped)
#
# # ------------ 分组后聚合：
# # df.groupby(['author']).sum()
# # print(dict(df.groupby(['author']).sum()['totalnum']))
#
# # j3 = df.groupby(['author']).agg(['sum'])
# # j4 = df.groupby(['author']).agg(['sum'])['totalnum'] # 取 totalnum 列
# j4 = df.groupby(['author']).agg(['sum'])['totalnum'] # 取 totalnum 列
# # print(j4)
# # print(j4.loc['王静']['sum']) # agg聚合以后，通过行标签选取行
#
# # j5 = df.groupby(['author']).agg([("mysum",'sum')]) # 分组后的列，起一个别名
# # print(j5)
#
#
# # -------------- 对不同列使用不同的聚合函数
# j6 = df.groupby(['author']).agg({"totalnum":sum,"success":max}) # 对 totalnum 列求和，对 success 求max
# # print(j6)
#
# # -------------- df.groupby().apply()
# # df.groupby(['author']).apply("函数名","函数的参数")
#
#
# # j7 = df.drop_duplicates(subset=['author','PRJ_NAME'],inplace=False)
# # print(j7)
#
# # 确定要删除的字符串
# delete_string = '王'
# # 删除包含特定字符串的行
# # df = df[~df['author'].str.contains(delete_string)] # 删除 author 列 包含字符串 王 的行
# # print(df)
#
# # print(df[ df['author'].str.contains( '王' )==False ])
#
# # df = df[df['author'].str.contains('王|羽')==False]
# # print(df)
#
#
# df = df[ ~df.author.str.contains('|'.join(['王','羽'])) ]
# print(df)
now = datetime.date.today()
today_last = datetime.datetime(now.year,now.month,now.day,23,59,59,999999)
print(now,"now_last:",today_last)
nowtime= datetime.datetime.now()
# print("当前时间：",nowtime)
yesterday = now - datetime.timedelta(days=1)
yesterday_last = datetime.datetime(yesterday.year,yesterday.month,yesterday.day,23,59,59,999999)
# print("yesterday_last:",yesterday_last)
# print("yesterday:",yesterday)
# (datetime.datetime.now()+datetime.timedelta(hours=1)).strftime('%Y-%m-%d %H')
# print("yesterday：",yesterday)
tomorrow = datetime.date.today() + datetime.timedelta(days=1)
# print("明天日期：",tomorrow)
# date: "2022-08-09"
this_week_start = datetime.date.today() - datetime.timedelta(days=now.weekday())
this_week_end = now + datetime.timedelta(days=6-now.weekday())
this_week_last = datetime.datetime(this_week_end.year,this_week_end.month,this_week_end.day,23,59,59,999999)
# print("this_week_start:",this_week_start)
# print("this_week_end:",this_week_end)
# print("this_week_last:",this_week_last)
# 获取上周第一天和最后一天：
last_week_start = now - datetime.timedelta(days=now.weekday()+7)
last_week_end = now - datetime.timedelta(days=now.weekday()+1)
# print("last_week_start:",last_week_start)
# print("last_week_end:",last_week_end)
# 获取本月第一天和最后一天：
this_month_start = datetime.datetime(now.year, now.month, 1)
this_month_end = datetime.datetime(now.year, now.month, calendar.monthrange(now.year, now.month)[1])
this_month_last = datetime.datetime(this_month_end.year,this_month_end.month,this_month_end.day,23,59,59,999999)
# print("this_month_start:",this_month_start)
# print("this_month_end:",this_month_end)
# print("this_month_last:",this_month_last)
current_month = int(str(today_last)[5:7]) # 当前月份
current_month_monthRange = calendar.monthrange(now.year,current_month) # 输出的是一个元组，第一个元素是月份（0-11），第二个元素是这个月的天数。
current_month_day_sum = current_month_monthRange[1] # 当前月天数
# 获取上月第一天和最后一天 ：
last_month_end = this_month_start - datetime.timedelta(days=1)
last_month_start = datetime.datetime(last_month_end.year, last_month_end.month, 1)
last_month = int(str(last_month_start)[5:7]) # 上月份
last_month_monthRange = calendar.monthrange(now.year,last_month) # 输出的是一个元组，第一个元素是月份（0-11），第二个元素是这个月的天数。
last__month_day_sum = last_month_monthRange[1] # 上月天数
#获取本季第一天和最后一天：
m = now.month
this_quarter_month_start = int((m-1)/3)*3+1
this_quarter_month_end = this_quarter_month_start + 2
this_quarter_start = datetime.datetime(now.year, this_quarter_month_start, 1)
this_quarter_end = datetime.datetime(now.year, this_quarter_month_end, calendar.monthrange(now.year, this_quarter_month_end)[1])
print("this_quarter_start:",this_quarter_start)
print("this_quarter_end:",this_quarter_end)
# 获取上季第一天和最后一天：
last_quarter_end = this_quarter_start - datetime.timedelta(days=1)
last_quarter_start = datetime.datetime(last_quarter_end.year, last_quarter_end.month - 2, 1)
# 获取本年第一天和最后一天：
this_year_start = datetime.datetime(now.year, 1, 1)
this_year_end = datetime.datetime(now.year + 1, 1, 1) - datetime.timedelt
