import datetime,pymysql,time

import xlwt

mysql_host='172.16.203.1'

#mysql_host='10.0.2.17'

mysql_port=13306

#mysql_port=3306

mysql_db1='tzqweb'

mysql_db2='password'  #这里输入真实密码

mysql_user='system'

mysql_pass='password'  #这里输入真实密码

#上一个月的第一天日期2018-09-01这种格式

if datetime.date.today().month==1:

    LastMonthFirstDay = datetime.date(datetime.date.today().year-1,datetime.date.today().month+11,1)

else:

    LastMonthFirstDay = datetime.date(datetime.date.today().year,datetime.date.today().month-1,1)

#上一个月的最后日期2018-09-01这种格式

LastMonthLastDay = datetime.date(datetime.date.today().year,datetime.date.today().month,1)-datetime.timedelta(1)

#获取这个月第一天2018-09-01这种格式

ThisMonthFirst_day=datetime.date.today().replace(day=1)

#获取今天2018-09-01这种格式

ThisMonthToday=datetime.date.today()

##得到这个月的sql--1

print('sql----------------1,返回值为8列')

#获取的值为8列个数字，第一个是日期，第2个是新用户访问，第3个是点击人数，第4个是点击次数，5是包月人数，6是包月失败，7老包月，8点播人数

SQL1='''select tab1.time1,ifnull(s4.zcrs,0) as zcrs ,ifnull(s1.djrs,0) as djrs ,ifnull(s1.djcs,0) as djl ,ifnull(s2.byrs,0) as byrs ,ifnull(s3.sbrs,0) as dgsbrs,ifnull(tab2.lbyfwrs,0) as lbyfwrs,ifnull(s5.dbrs,0) as dbrs from

(SELECT CONCAT(DATE_FORMAT(curdate(),'%Y-%m-'),aa.stats_day) time1 FROM mydate aa WHERE CAST(aa.stats_day AS SIGNED)<=DAYOFMONTH(last_day(curdate()))) tab1

left join

(select  s1.time1 as time1,count(DISTINCT o.userid) as lbyfwrs  from

(SELECT CONCAT(DATE_FORMAT(curdate(),'%Y-%m-'),aa.stats_day) time1 FROM mydate aa WHERE CAST(aa.stats_day AS SIGNED)<=DAYOFMONTH(last_day(curdate()))) s1,

edu_userorder o,edu_userapp a

where o.appid=188 and o.ordertime<=CONCAT(DATE_ADD(s1.time1,interval -1 day),' 23:59:59')

and a.userid=o.userid and a.appid=188 and a.recordtime<=CONCAT(s1.time1,' 23:59:59')

and a.recordtime>=CONCAT(s1.time1,' 00:00:00')

group by s1.time1) tab2

on tab1.time1=tab2.time1

left join

(select DATE_FORMAT(t.recordtime,'%Y-%m-%d') as time1,count(*) as djcs,count(distinct t.userid) as djrs from edu_userapp t where t.recordtime>=CONCAT(DATE_ADD(curdate(),interval -day(curdate())+1 day),' 00:00:00')

and t.recordtime<=CONCAT(last_day(curdate()),' 23:59:59') and t.appid=188

group by DATE_FORMAT(t.recordtime,'%Y-%m-%d') )s1

on tab1.time1=s1.time1 left join

( select DATE_FORMAT(t.ordertime,'%Y-%m-%d') as time2, count(distinct t.userid) as byrs from edu_userorder t where t.ordertime>=CONCAT(DATE_ADD(curdate(),interval -day(curdate())+1 day),' 00:00:00')

and t.ordertime<=CONCAT(last_day(curdate()),' 23:59:59') and t.appid=188

group by DATE_FORMAT(t.ordertime,'%Y-%m-%d')) s2

on tab1.time1=s2.time2 left join

(select DATE_FORMAT(t.recordtime,'%Y-%m-%d') as time2, count(distinct t.userid) as sbrs from edu_userorder_fail t where t.recordtime>=CONCAT(DATE_ADD(curdate(),interval -day(curdate())+1 day),' 00:00:00')

and t.recordtime<=CONCAT(last_day(curdate()),' 23:59:59') and t.appid=188 and t.errorcode='9314'

group by DATE_FORMAT(t.recordtime,'%Y-%m-%d')) s3

on tab1.time1=s3.time2 left join

(select DATE_FORMAT(t.recordtime,'%Y-%m-%d') as time1,count(*) as zcrs from (

select a.userid,min(a.recordtime) as recordtime from edu_userapp a where a.appid=188 group by a.userid

 ) t where t.recordtime>=CONCAT(DATE_ADD(curdate(),interval -day(curdate())+1 day),' 00:00:00')

and t.recordtime<=CONCAT(last_day(curdate()),' 23:59:59')

group by DATE_FORMAT(t.recordtime,'%Y-%m-%d') )s4

on tab1.time1=s4.time1 left join

(

 select DATE_FORMAT(bl.starttime,'%Y-%m-%d') as time1,count(DISTINCT bl.userid) as dbrs from seewomq.edu_userlogs bl

where bl.starttime>=CONCAT(DATE_ADD(curdate(),interval -day(curdate())+1 day),' 00:00:00')

 and bl.starttime<=CONCAT(last_day(curdate()),' 23:59:59')

 and bl.platform='ttmq'

group by DATE_FORMAT(bl.starttime,'%Y-%m-%d')

)s5

on tab1.time1=s5.time1

order by tab1.time1;'''

#获取的值为2个数字，第一个是点击人数，第二个是点击次数

SQL2='''select count(DISTINCT a.userid) as zdjrs ,count(*) zdjl from  edu_userapp a

 where  a.appid=188 and a.recordtime<=CONCAT(last_day(curdate()),' 23:59:59') and a.recordtime>=CONCAT(DATE_ADD(curdate(),interval -day(curdate())+1 day),' 00:00:00') ;

'''

#获取的值为1个数字,值为点播人数

SQL3='''select count(DISTINCT bl.userid) as dbrs from seewomq.edu_userlogs bl

where bl.starttime>=CONCAT(DATE_ADD(curdate(),interval -day(curdate())+1 day),' 00:00:00')

 and bl.starttime<=CONCAT(last_day(curdate()),' 23:59:59')

 and bl.platform='ttmq';

'''

#获取的值为2列,值为各个书本点播人数

SQL4='''select v.videoName, count(DISTINCT bl.userid) as dbrs from seewomq.edu_video v left join  seewomq.edu_userlogs bl

on v.id=bl.videoID  and bl.starttime>=CONCAT(DATE_ADD(curdate(),interval -day(curdate())+1 day),' 00:00:00') and bl.starttime<=CONCAT(last_day(curdate()),' 23:59:59')

 and bl.platform='ttmq' group by v.videoName order by count(DISTINCT bl.userid) desc;

'''

#定义个方法执行查询sql操作

def get_data(db,sql):

    # 使用cursor()方法获取操作游标

    cur1 = db.cursor()

    try:

        cur1.execute(sql) 	#执行sql语句

        return cur1.fetchall()	#获取查询的所有记录

    except Exception as e:

        raise e

    finally:

        db.close()	#关闭连接

print('################################################################')

print('准备获取sql1------data------------------------------------')

#打开数据库连接

db1= pymysql.connect(host=mysql_host,user=mysql_user,

 	password=mysql_pass,db=mysql_db1,port=mysql_port)

data_1=get_data(db1,SQL1)

#遍历结果

#获取的值为8列个数字，第一个是日期，第2个是新用户访问，第3个是点击人数，第4个是点击次数，5是包月人数，6是包月失败，7老包月，8点播人数

sql_1_data_list=[]

for row in data_1 :

    tmp_list=[]

    row_date = row[0]

    row_new_user = row[1]

    row_click_user = row[2]

    row_click_count=row[3]

    row_order_users=row[4]

    row_order_fails=row[5]

    row_order_old=row[6]

    row_dianbo=row[7]

    tmp_list.append(row_date)

    tmp_list.append(row_new_user)

    tmp_list.append(row_click_user)

    tmp_list.append(row_click_count)

    tmp_list.append(row_order_users)

    tmp_list.append(row_order_fails)

    tmp_list.append(row_order_old)

    tmp_list.append(row_dianbo)

    sql_1_data_list.append(tmp_list)

print('sql----1------data---****************************')

print('准备获取sql2------data------------------------------------')

db1= pymysql.connect(host=mysql_host,user=mysql_user,

 	password=mysql_pass,db=mysql_db1,port=mysql_port)

data_2=get_data(db1,SQL2)

sql_2_data_list=[]

for row in data_2:

    sql_2_data_list.append(row[0])

    sql_2_data_list.append(row[1])

print(sql_2_data_list)

print('sql----2------data---****************************')

print('准备获取sql3------data------------------------------------')

db1= pymysql.connect(host=mysql_host,user=mysql_user,

 	password=mysql_pass,db=mysql_db1,port=mysql_port)

data_3=get_data(db1,SQL3)

sql_3_data_list=[]

for row in data_3:

    sql_3_data_list.append(row[0])

print(sql_3_data_list)

print('sql----3------data---****************************')

print('准备获取sql4------data-------------------------------------------')

db1= pymysql.connect(host=mysql_host,user=mysql_user,

 	password=mysql_pass,db=mysql_db1,port=mysql_port)

data_4=get_data(db1,SQL4)

sql_4_data_list=[]

for row in data_4:

    tmp_list=[]

    tmp_list.append(row[0])

    tmp_list.append(row[1])

    sql_4_data_list.append(tmp_list)

#

print(sql_4_data_list)

print('sql----4------data---****************************')

print('#########################准备写excel表###########################################')

# 创建 xls 文件对象

wb = xlwt.Workbook()

# 新增一个表单

sh = wb.add_sheet('Last_month')

#先写标题列

sh.write(0,0,'日期')

sh.write(0,1,'新用户访问')

sh.write(0,2,'点击人数')

sh.write(0,3,'点击次数')

sh.write(0,4,'包月人数')

sh.write(0,5,'失败人数')

sh.write(0,6,'老包月访问')

sh.write(0,7,'点播人数')

sh.write(0,8,'活跃率')

sh.write(0,9,'书本')

sh.write(0,10,'点击人数')

# 按位置添加数据

#先写excel添加---sql—1-data

data_1_lenth=len(sql_1_data_list)

print(sql_1_data_list)

start_row_num=1

for data_list in sql_1_data_list:

    start_col_num=0

    for item in data_list:

        sh.write(start_row_num,start_col_num,item)

        start_col_num+=1

    start_row_num+=1

#第一列写合计

sh.write(data_1_lenth+1,0,'合计')

#写excel添加---sql—2-data

# sql_2_row_start=31

sql_2_row_start=data_1_lenth+1

sql_2_col_start=2

print(sql_2_data_list)

for sql_2_data in sql_2_data_list:

    sh.write(sql_2_row_start,sql_2_col_start,sql_2_data)

    sql_2_col_start+=1

#写excel添加---sql—3-data

sql_3_row_start=data_1_lenth+1

sql_3_col=7

sh.write(sql_3_row_start,sql_3_col,sql_3_data_list[0])

#写excel添加---sql—4-data

sql_4_row_start=1

sql_4_item_1_col=9

sql_4_item_2_col=10

for sql_4_data in sql_4_data_list:

    sh.write(sql_4_row_start,sql_4_item_1_col,sql_4_data[0])

    sh.write(sql_4_row_start,sql_4_item_2_col,sql_4_data[1])

    sql_4_row_start+=1

filename=str(ThisMonthToday)

# 保存文件

wb.save(filename+'月报'+'.xls')