def xl_workdayremain(sheet, position):
mydate = MyDate()
last_saved = mydate.last_saved
workday, totalworkdays = mydate.workdays_of_month(last_saved)
remainworkday = totalworkdays - workday
sht = xw.Book.caller().sheets[sheet]
sht.range(position).value = remainworkday
def xl_workdays(sheet, workday_pos):
mydate = MyDate()
last_saved = mydate.last_saved
workday, totalworkdays = mydate.workdays_of_month(last_saved)
sht = xw.Book.caller().sheets[sheet]
sht.range(workday_pos).value = workday
def main():
report = DailyReportSummary()
yesterday = MyDate().yesterday
path = report.path
recharge_and_withdraw = report.cg_recharge_withdraw_vol_daily()
interest = report.product_and_jxq()
interest.interest = interest.interest / 10000
recharge_and_withdraw.iloc[:,
1:] = recharge_and_withdraw.iloc[:, 1:] / 10000
recharge_and_withdraw = to_timeseries('2017-08-10', yesterday,
recharge_and_withdraw, interest)
fixed_vol = report.cg_fixed_vol()
chuxin_vol = report.cg_chuxin_vol_monthly()
fixed_vol.index = fixed_vol.index.strftime('%Y-%m')
chuxin_vol.index = chuxin_vol.index.strftime('%Y-%m')
interest_payable = report.product_jxq_interest_payable()
interest_payable.interest_payable = interest_payable.interest_payable / 10000
interest_payable = interest_payable.set_index('date')
product_vol = pd.concat([interest_payable, fixed_vol, chuxin_vol], axis=1)
recharge_and_withdraw.to_csv(path + r'\recharge_and_withdraw.csv',
index=False)
product_vol.to_csv(path + r'\product_vol.csv', index=True)
def __init__(self):
mypath = MyPath()
mydate = MyDate()
global WeeklyReport
WeeklyReport = WeeklyReportSummary()
# import necessary datetime
self.yesterday = mydate.yesterday
self.path = mypath.path
self.last_saved = mydate.last_saved
self.curdate = mydate.curdate
def download_raw_data(start_date=None, end_date=None, save_path=None):
if start_date is None or end_date is None:
dateclass = MyDate()
start_date = dateclass.last_saved
end_date = dateclass.yesterday
confirm = input("Confirm:\t start date: %s, end date: %s\n[y]/n: " % (start_date, end_date))
while confirm not in ['y', 'n', '']:
confirm = input("[y]/n: ")
if confirm != 'y' or '':
start_date = input("Set start date: ")
end_date = input("Set end date: ")
if not save_path:
save_path = raw_path
req = NewManageCrawler()
req.salesDetailExcel(start_date, end_date, save_path)
def __init__(self,
old_start_date='2017-04-17',
cg_start_date='2017-08-10',
end_date=""):
self.old_start_date = old_start_date
self.cg_start_date = cg_start_date
self.to_the_beginning = '2000-01-01'
mydate = MyDate()
mytool = MyPath()
if end_date == "":
self.endate = mydate.yesterday
else:
self.endate = end_date
self.month = self.endate[:8] + '01'
old_day_before_start = datetime.datetime.strptime(
self.old_start_date, '%Y-%m-%d')
old_day_before_start = old_day_before_start - datetime.timedelta(
days=1)
self.old_day_before_start = old_day_before_start.strftime('%Y-%m-%d')
self.to_csv = mytool.to_csv
def xl_area_performance_output(template_path=None):
my_tool = MyDate()
yesterday = my_tool.yesterday
last_saved = my_tool.last_saved
dirpath = MyPath().scriptpath
if not template_path:
template_path = dirpath + r'\area_template.xls'
else:
dirpath = os.path.split(template_path)[0]
SH_data, NJ_data = dc.area_performance(dc.raw_path)
SH_data = SH_data.astype('str')
NJ_data = NJ_data.astype('str')
if yesterday == last_saved:
xl_save_as(template_path=template_path,
sheetname='销售人员统计明细报表',
data=SH_data,
save_as=r'%s\output\线上业绩-%s上海.xls' %
(dirpath, yesterday[5:7] + yesterday[8:]))
xl_save_as(template_path=template_path,
sheetname='销售人员统计明细报表',
data=NJ_data,
save_as=r'%s\output\线上业绩-%s江苏.xls' %
(dirpath, yesterday[5:7] + yesterday[8:]))
else:
xl_save_as(template_path=template_path,
sheetname='销售人员统计明细报表',
data=SH_data,
save_as=r'%s\output\线上业绩-%s-%s上海.xls' %
(dirpath, last_saved[5:7] + last_saved[8:],
yesterday[5:7] + yesterday[8:]))
xl_save_as(template_path=template_path,
sheetname='销售人员统计明细报表',
data=NJ_data,
save_as=r'%s\output\线上业绩-%s-%s江苏.xls' %
(dirpath, last_saved[5:7] + last_saved[8:],
yesterday[5:7] + yesterday[8:]))
def area_data_output(): # duplicated, do not use!
inpath = '' # input('Set input path(script path as default):\n')
outpath = '' # input('Set output path(script path as default):\n')
my_tool = MyDate()
dirpath = script_path
yesterday = my_tool.yesterday
last_saved = my_tool.last_saved
# output cleaned data:
import os
if os.path.isdir(outpath):
dirpath = outpath
if os.path.isfile(inpath):
raw_path = inpath
SH_data, NJ_data = area_performance(filename=raw_path)
else:
SH_data, NJ_data = area_performance()
if yesterday == last_saved:
output_excel(SH_data,
'%s\\线上业绩-%s上海.xlsx' % (dirpath, yesterday[5:7] + yesterday[8:]),
'销售人员统计明细报表')
output_excel(NJ_data,
'%s\\线上业绩-%s江苏.xlsx' % (dirpath, yesterday[5:7] + yesterday[8:]),
'销售人员统计明细报表')
else:
output_excel(SH_data,
'%s\\线上业绩-%s-%s上海.xlsx' % (dirpath,
last_saved[5:7] + last_saved[8:],
yesterday[5:7] + yesterday[8:]),
'销售人员统计明细报表')
output_excel(NJ_data,
'%s\\线上业绩-%s-%s江苏.xlsx' % (dirpath,
last_saved[5:7] + last_saved[8:],
yesterday[5:7] + yesterday[8:]),
'销售人员统计明细报表')
import pandas as pd
import datetime
from compile.my_class import sql_connection, MyDate
mytool = MyDate()
cur = sql_connection()
sql_yueyuejia_invest = \
"""
select date(tbm.create_date),u.mobile_no,tbm.basic_lock_month,tbm.amount
from t_bus_match_link_invest tbm
left join t_bus_user u on u.user_name=tbm.user_name
where (date(tbm.clearing_date)>%s
or tbm.clearing_date is null)
and status=159002
and u.role_id in ('ordinary','counselor')
;"""
last_month_floor = mytool.last_month_floor
last_month_ceil = mytool.last_month_ceil
this_month = last_month_ceil.strftime('%Y-%m-%d')
cur.execute(sql_yueyuejia_invest, this_month)
yueyuejia_invest = pd.DataFrame(
list(cur.fetchall()),
columns=['date', 'mobile_no', 'basic_lock_month', 'amount'])
cur.close()
mindate = yueyuejia_invest.date.min()
max_iter = int((last_month_floor - mindate).days / 30)
last_month_investor = pd.DataFrame()
month_range = datetime.timedelta(30)
def nation_main():
date = VBATool.xl_dateRange_in_filename()
nation_name = "全国理财报表-" + date + ".xlsx"
mindate, maxdate = VBATool.data_timeRange(drop_zero=False)
zmindate, zmaxdate = VBATool.data_timeRange(drop_zero=True)
mydate = MyDate()
workday, totalworkday = mydate.workdays_of_month(maxdate.replace('/', '-'))
time_proc = workday / totalworkday * 100
path = MyPath().scriptpath
att_path = path + '\\output\\' + nation_name
receiver, ccreceiver = ['*****@*****.**'], []
#receiver = ['*****@*****.**', '*****@*****.**', '*****@*****.**']
#ccreceiver = ['*****@*****.**', '*****@*****.**', '*****@*****.**']
parse1 = pd.read_excel(path + "\汇总表及划扣明细.xlsm",
sheetname='日报数据1',
index_col='营业部')
p1_new_invest = parse1.loc['全国总计', '绩效业绩']
p1_reinvest = parse1.loc['全国总计', 'Unnamed: 3']
p1_perf = parse1.loc['全国总计', 'Unnamed: 4']
p1_arrival = parse1.loc['全国总计', '到账业绩']
parse2 = pd.read_excel(path + "\汇总表及划扣明细.xlsm",
sheetname='日报数据2',
index_col='营业部')
p2_new_invest = float(parse2.loc['全国总计', '绩效业绩'])
p2_reinvest = float(parse2.loc['全国总计', 'Unnamed: 3'])
p2_perf = float(parse2.loc['全国总计', 'Unnamed: 4'])
p2_arrival = float(parse2.loc['全国总计', '到账业绩'])
achieve_rate = parse2.loc['全国总计', '达成率']
text1 = """<html> <p><span lang=3D"EN-US" style="font-size: 16px; font-family: 'Microsoft YaHei UI', Tahoma;">
各位领导好：<br/><br/>
截至<strong>%s</strong>时间进度为<strong>%.1f</strong>%%，绩效业绩进度为<strong>%s</strong>；<br/><br/>
<strong>%s</strong>：<strong>绩效业绩</strong>为<strong>%.1f万：</strong>新投资<strong>%.1f万</strong>，再投资<strong>%.1f万</strong>；<strong>到账业绩</strong>为<strong>%.1f万</strong>；<br/><br/>
<strong>%s月总计</strong>：<strong>绩效业绩</strong>为<strong>%.1f万：</strong>新投资<strong>%.1f万</strong>，再投资<strong>%.1f万</strong>；<strong>到账业绩</strong>为<strong>%.1f万</strong>；
</span></p>
<p>
<span lang=3D"EN-US" style="background:yellow; font-size: 18px;"><strong>%s：</strong></span>
</p></html>
""" % (zmaxdate.replace('/', '.'), time_proc, achieve_rate, date, p1_perf,
p1_new_invest, p1_reinvest, p1_arrival, int(maxdate[5:7]), p2_perf,
p2_new_invest, p2_reinvest, p2_arrival, date)
text2 = """<html><p>
<span lang=3D"EN-US" style="background:yellow; font-size: 17px;"><strong>%s月累计：</strong></span>
</p>
</html>""" % int(maxdate[5:7])
msg = MyMail(receiver=receiver,
cc=ccreceiver,
subject=os.path.splitext(nation_name)[0])
msg.set_html_format_table(path + r'\mail\日报数据1.htm')
msg.set_table_data(path + r'\汇总表及划扣明细.xlsm',
sheetname='日报数据1',
float_decimal=1)
tb1 = msg.add_html_table(inplace=False)
msg.set_html_format_table(path + r'\mail\日报数据2.htm')
msg.set_table_data(path + r'\汇总表及划扣明细.xlsm',
sheetname='日报数据2',
float_decimal=1)
tb2 = msg.add_html_table(inplace=False)
pic_path = path + r'\mail\signature.png'
f = open(pic_path, 'rb')
img64 = base64.encodebytes(f.read()).decode()
f.close()
suffix = pic_path.split('.')[-1]
img = '''<hr /><p>
<img src="data:image/%s;base64,%s" width="370" height="130">
</p>'''\
% (suffix, img64)
msg.add_plain_text(text1 + tb1 + text2 + tb2)
# msg.add_attach(att_path)
# msg.add_sig_to_text(path+r'\mail\signature.png')
msg.send()