def salesOfDailyReport(user_list, receiver):
title = '销售日报'
'''
receiver = ['[email protected];']
user_list = [('161006031101','123456','16'),('18V08747','123456','18'),
('1558098','123456','15'),('131006031102','123456','13'),
('121006031103','123456','12'),('881006031105','123456','88'),
('321006031107','123456','32'),('311006031110','123456','31')] #经过分析url中有一个参数 buid= 用户名的前两位
'''
print(begintime + '-' + endtime + '数据导出中...')
filewarn = dict()
filepath_list = []
for username, password, buid in user_list:
v = VssSpider(username, password)
txtcheck = v.getCheckCode()
v.longonVender(txtcheck)
url = 'http://vss.crv.com.cn/scm/DaemonSCMDownloadReport?reportname=cmexcel&class=SaleDaily4Scm&cmid=9000002000&buid=%s&sdate_min=%s&sdate_max=%s&' % (
buid, begintime, endtime)
#file_name = v.exportExcel(url, username, title)
#v.sendEmail(file_name, title, receiver)
#time.sleep(random.randint(1, 5))
is_exist, file_name, file_path = v.exportExcel(url, username, title)
if is_exist:
filepath_list.append(file_path)
filewarn[username] = "导出成功！"
else:
filewarn[username] = "导出失败！"
warntitle = str(filewarn)
logger.info(warntitle)
file_absolute_path = handleExcel.handel_main(filepath_list, title)
handleExcel.send_email_total(title, file_absolute_path, receiver,
warntitle)
print('销售日报完成')
def paymentOrder(user_list, receiver):
title = '付款单'
'''
receiver = ['[email protected];']
user_list = [('161006031101','123456','16','1006031101'), ('18V08747','123456','18','V08747'),
('1558098','123456','15','58098'), ('131006031102','123456','13','1006031102'),
('121006031103','123456','12','1006031103'), ('881006031105','123456','88','1006031105'),
('321006031107','123456','32','1006031107'), ('311006031110','123456','31','1006031110')]
'''
filewarn = dict()
filepath_list = []
for username, password, buid, venderid in user_list:
#url = r'http://vss.crv.com.cn/scm/DaemonMainDownload?operation=cmexcel&cmid=3020302000&service=Paymentvoucher&buid=%s&venderid=%s&' % (buid, venderid)
# url = r'http://vss.crv.com.cn/scm/DaemonMainDownload?operation=cmexcel&cmid=3020302000&service=Paymentvoucher&buid=%s&venderid=%s&date_min=%s&date_max=%s' % (buid, venderid, min_date, max_date)
url = r'http://vss.crv.com.cn/scm/DaemonMainDownload?operation=cmexcel&cmid=3020302000&service=Paymentvoucher&buid=%s&venderid=%s&' % (
buid, venderid)
vsObject = VssSpider(username, password)
txtcheck = vsObject.getCheckCode()
vsObject.longonVender(txtcheck)
is_exist, file_name, file_path = vsObject.exportExcel(
url, username, title)
# work_sheet = vssspiderPO.readExcelGetWorkSheet(file_path)
# if work_sheet.nrows > 1:
# vsObject.sendEmail(file_name, title, receiver)
# generator_sheetid = vssspiderPO.getSheetId(work_sheet)
# generator_valuelist_sheetid = vssspiderPO.requestSheetUrlGetSheet(vsObject, generator_sheetid)
# generator_excelName = vssspiderPO.writeExcel(username, generator_valuelist_sheetid, title)
# vssspiderPO.sendEmail2(generator_excelName, title, receiver)
# else:
# print("无付款单！")
# logger.info(u"无付款单！")
if is_exist:
filepath_list.append(file_path)
filewarn[username] = "导出成功！"
else:
filewarn[username] = "导出失败！"
'''
generator_sheetid = vssspiderPO.readExcelGetSheetId(file_name)
generator_valuelist_sheetid = vssspiderPO.requestSheetUrlGetSheet(vsObject, generator_sheetid)
generator_excelName = vssspiderPO.writeExcel(username, generator_valuelist_sheetid)
vssspiderPO.sendEmail2(generator_excelName, title, receiver)
'''
warntitle = str(filewarn)
logger.info(warntitle)
file_absolute_path = handleExcel.handel_main(filepath_list, title)
handleExcel.send_email_total(title, file_absolute_path, receiver,
warntitle)
print('付款单完成')
logger.info(u'付款单完成')
#paymentOrder()
def writeExcel(self, gene_rowvaluetuple, user_name, title):
localtime = getDate.getLocalTime()
filename = user_name + title + localtime + '.xlsx'
relativepath = r'.\tablefile' #相对路径
filepath = os.path.join(relativepath, title, filename) #组合相对路径
workbook = xlsxwriter.Workbook(filepath)
worksheet = workbook.add_worksheet(title)
i = 1
for rowvaluelist in gene_rowvaluetuple:
#print(rowvaluelist)
worksheet.write_row('A{}'.format(i), rowvaluelist)
i += 1
workbook.close()
print(filename + " 导出完成！")
logger.info(filename + " 导出完成！")
return filename, filepath
def sendEmail(self, filename, title, receiver):
outlook = win32.Dispatch('outlook.application')
mail = outlook.CreateItem(0)
receivers = receiver
mail.To = receivers[0]
mail.Subject = title + '\n'
mail.Body = title
try:
mail.Attachments.Add(
os.path.dirname(os.path.realpath(__file__)) + '\\tablefile' +
'\\' + title + '\\' + filename
) #获取本文件路径 os.path.dirname(os.path.realpath(__file__)) + '\\' + filename
except Exception as e:
mail.Body = filename + '发送失败！' + '\n' + str(e) # e.value
logger.error(e)
mail.Send()
print(filename + '邮件发送成功！')
logger.info(filename + u'邮件发送成功！')
def expensesDocuments(user_list, receiver):
title = '费用单据'
'''
receiver = ['[email protected];']
user_list = [('161006031101','123456','16','1006031101'), ('18V08747','123456','18','V08747'),
('1558098','123456','15','58098'), ('131006031102','123456','13','1006031102'),
('121006031103','123456','12','1006031103'), ('881006031105','123456','88','1006031105'),
('321006031107','123456','32','1006031107'), ('311006031110','123456','31','1006031110')]
'''
filewarn = dict()
filepath_list = []
for username, password, buid, venderid in user_list:
url = r'http://vss.crv.com.cn/scm/DaemonMainDownload?operation=cmexcel&cmid=3020304000&service=ChargeSum&buid=%s&venderid=%s&docdate_min=%s&docdate_max=%s&' % (
buid, venderid, min_date, max_date)
v = VssSpider(username, password)
txtcheck = v.getCheckCode()
v.longonVender(txtcheck)
is_exist, file_name, file_path = v.exportExcel(url, username, title)
'''
work_sheet = vssspiderPO.readExcelGetWorkSheet(file_name)
if work_sheet.nrows > 1:
v.sendEmail(file_name, title, receiver)
else:
print(username + min_date + '-' + max_date + "无费用单据！")
logger.warn(username + min_date + '-' + max_date + u'无费用单据！')
'''
if is_exist:
filepath_list.append(file_path)
filewarn[username] = "导出成功！"
else:
filewarn[username] = "导出失败！"
warntitle = str(filewarn)
logger.info(warntitle)
file_absolute_path = handleExcel.handel_main(filepath_list, title)
handleExcel.send_email_total(title, file_absolute_path, receiver,
warntitle)
print('费用单据完成')
logger.info(u'费用单据完成')
#expensesDocuments()
def promotionDetails(user_list, receiver):
title = '促销明细'
'''
receiver = ['[email protected];']
user_list = [('161006031101','123456','16','1006031101'), ('18V08747','123456','18','V08747'),
('1558098','123456','15','58098'), ('131006031102','123456','13','1006031102'),
('121006031103','123456','12','1006031103'), ('881006031105','123456','88','1006031105'),
('321006031107','123456','32','1006031107'), ('311006031110','123456','31','1006031110')]
'''
filewarn = dict()
filepath_list = []
for username, password, buid, venderid in user_list:
url = r'http://vss.crv.com.cn/scm/DaemonSCMDownloadReport?reportname=prom&buid=%s&venderid=%s&sdate_min=%s&sdate_max=%s' % (
buid, venderid, min_date, max_date)
#v = VssSpider(username, password)
#txtcheck = v.getCheckCode()
#v.longonVender(txtcheck)
#file_name, file_path = v.exportExcel(url, username, title)
#v.sendEmail(file_name, title, receiver)
#filepath_list.append(file_path)
vpd = VssSpiderPD(username, password)
txtcheck = vpd.getCheckCode()
vpd.longonVender(txtcheck)
rowxmllist = vpd.getUrlRowXmlList(url)
gene_rowvaluelist = vpd.getRowValueList(rowxmllist)
file_name, file_path = vpd.writeExcel(gene_rowvaluelist, username,
title)
filepath_list.append(file_path)
filewarn[username] = "导出成功！"
warntitle = str(filewarn)
logger.info(warntitle)
file_absolute_path = handleExcel.handel_main(filepath_list, title)
handleExcel.send_email_total(title, file_absolute_path, receiver,
warntitle)
print('促销明细完成')
logger.info(u'促销明细完成')
#promotionDetails()
def sendEmail2(generator_excelName, title, receiver): #generator_writeExcel =
outlook = win32.Dispatch('outlook.application')
mail = outlook.CreateItem(0)
receivers = receiver
mail.To = receivers[0]
mail.Subject = title + '\n'
mail.Body = title
for filename in generator_excelName:
logger.debug(filename)
try:
mail.Attachments.Add(
os.path.dirname(os.path.realpath(__file__)) + '\\tablefile' +
'\\' + title + '\\' + filename)
except Exception as e:
mail.Body = filename + '发送失败！' + '\n' + str(e) #e.value
logger.error(e)
continue
mail.Send()
print('付款单邮件发送成功！')
logger.info(u'付款单邮件发送成功！')
'''
def send_email_total(title, filepath, receiver, warntitle):
outlook = win32.Dispatch('outlook.application')
mail = outlook.CreateItem(0)
receivers = receiver
mail.To = receivers[0]
mail.Subject = title + '\n'
mail.Body = title + '\n' + warntitle
if get_file_size(filepath) < 20:
try:
mail.Attachments.Add(
filepath
) # 获取本文件路径 os.path.dirname(os.path.realpath(__file__)) + '\\' + filename
except Exception as e:
mail.Body = title + u'发送失败！' + '\n' + str(e) # e.value
logger.error(e)
mail.Send()
print(title + u'邮件发送成功！')
logger.info(title + u'邮件发送成功！')
else:
mail.Body = title + u'文件大小超过20M,发送失败！'
mail.Send()
logger.info(title + u'文件大小超过20M,发送失败！')
def theLastInventory(user_list, receiver):
title = '最新库存'
'''
receiver = ['[email protected];']
user_list = [('161006031101','123456','16','1006031101'), ('18V08747','123456','18','V08747'),
('1558098','123456','15','58098'), ('131006031102','123456','13','1006031102'),
('121006031103','123456','12','1006031103'), ('881006031105','123456','88','1006031105'),
('321006031107','123456','32','1006031107'), ('311006031110','123456','31','1006031110')]
'''
filewarn = dict()
filepath_list = []
for username, password, buid, venderid in user_list:
url = r'http://vss.crv.com.cn/scm/DaemonSCMDownloadReport?reportname=cmexcel&class=CurrtentInventory4Scm&cmid=9000003000&buid=%s&venderid=%s&' % (
buid, venderid)
v = VssSpider(username, password)
txtcheck = v.getCheckCode()
v.longonVender(txtcheck)
is_exist, file_name, file_path = v.exportExcel(url, username, title)
if is_exist:
filepath_list.append(file_path)
filewarn[username] = "导出成功！"
else:
filewarn[username] = "导出失败！"
#v.sendEmail(file_name, title, receiver) #每个账号表都发送
warntitle = str(filewarn)
logger.info(warntitle)
file_absolute_path = handleExcel.handel_main(filepath_list, title)
handleExcel.send_email_total(title, file_absolute_path, receiver,
warntitle)
print('最新库存完成')
logger.info(u'最新库存完成')
#theLastInventory()
def businessDocument(user_list, receiver):
title = '业务单据'
'''
receiver = ['[email protected];']
user_list = [('161006031101','123456','16','1006031101'), ('18V08747','123456','18','V08747'),
('1558098','123456','15','58098'), ('131006031102','123456','13','1006031102'),
('121006031103','123456','12','1006031103'), ('881006031105','123456','88','1006031105'),
('321006031107','123456','32','1006031107'), ('311006031110','123456','31','1006031110')]
'''
filewarn = dict()
filepath_list = []
for username, password, buid, venderid in user_list:
url = r'http://vss.crv.com.cn/scm/DaemonMainDownload?operation=cmexcel&cmid=3020303000&service=UnpaidSheet&buid=%s&venderid=%s&docdate_min=%s&docdate_max=%s&' % (
buid, venderid, min_date, max_date)
v = VssSpider(username, password)
txtcheck = v.getCheckCode()
v.longonVender(txtcheck)
is_exist, file_name, file_path = v.exportExcel(url, username, title)
#v.sendEmail(file_name, title, receiver)
if is_exist:
filepath_list.append(file_path)
filewarn[username] = "导出成功！"
else:
filewarn[username] = "导出失败！"
warntitle = str(filewarn)
logger.info(warntitle)
file_absolute_path = handleExcel.handel_main(filepath_list, title)
handleExcel.send_email_total(title, file_absolute_path, receiver,
warntitle)
print('业务单据完成')
logger.info(u'业务单据完成')
#businessDocument()
def exportExcel(self, url, user_name, title):
localtime = getDate.getLocalTime()
filename = user_name + title + localtime + '.xls'
relativepath = r'.\tablefile' #相对路径
filepath = os.path.join(relativepath, title, filename) #组合相对路径
isexist = False
for i in range(0, 20):
try:
with closing(
self.s.get(url,
headers=self.headers,
stream=True,
allow_redirects=False,
timeout=60)) as response: #请求超时,登录超时情况处理
logger.debug(response.status_code)
if re.search(r"<code>-1</code>", response.text) is None:
logger.info(user_name + title + "访问成功！")
with open(filepath, 'wb') as file: #filename
for chunk in response.iter_content(
chunk_size=1024): #分块下载
if chunk:
file.write(chunk)
isexist = True
print(filename + '导出完成！')
logger.info(filename + u'导出完成！')
break
else:
continue
except Exception as e:
logger.warn(user_name + title + "访问失败！" + str(e))
continue
logger.debug(user_name + title + "访问次数：" + str(i))
return isexist, filename, filepath