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 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 __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()