def __channel():
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
cnn_sales = mysql_conn.get_conn(db_config.config_sales)
if cnn_sales == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_sales = cnn_sales.cursor(dictionary=True)
sql_select_employee = '''
select id,name,user_no,dept_no,position_no from employee where dept_no = 10 and is_test = 0
'''
cursor_employee.execute(sql_select_employee)
employees = cursor_employee.fetchall()
for employee in employees:
sql_select_sales = '''
select id,emp_no,sum(money) money,purchase_date,product_no from sales
WHERE product_type not in (%s) and emp_no = %s
and PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format(purchase_date, '%Y%m' ) ) = 1
group by emp_no
'''
cursor_sales.execute(sql_select_sales,(3,employee['user_no']))
sales = cursor_sales.fetchall()
for sale in sales:
if sale['money'] >= 1500000:
commission_scale = 0.0006
if sale['money'] < 1500000:
commission_scale = 0.0004
p_type = '其他产品'
a = str(sale["purchase_date"]) #获得时间
timeArray = time.strptime(a, "%Y-%m-%d")
year = time.strftime("%Y", timeArray)
month = time.strftime("%m",timeArray)
establish_money = __getEstablishMoney(sale['emp_no'])#本月成立金额
if establish_money == None:
establish_money = 0
commission_money = int(establish_money) * commission_scale
sql_insert_commission_wealthCenter = '''
insert into commission_wealthCenter(emp_no,emp_name,dept_no,position_no,sales_money,
commission_scale,establish_money,commission_money,is_examine,year,month,product_no,type)
values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);
'''
cursor_employee.execute(sql_insert_commission_wealthCenter,(sale['emp_no'],employee['name'],employee['dept_no'],\
employee['position_no'],sale['money'],commission_scale,establish_money,commission_money,1,year,month,sale['product_no'],p_type))
except Exception, e:
print(e)
return False
def __channelSpecial(): #渠道特殊产品
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
cnn_sales = mysql_conn.get_conn(db_config.config_sales)
if cnn_sales == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_sales = cnn_sales.cursor(dictionary=True)
sql_select_employee = '''
select id,name,user_no,dept_no,position_no from employee where dept_no = 10 and is_test = 0
'''
cursor_employee.execute(sql_select_employee)
employees = cursor_employee.fetchall()
for employee in employees:
sql_select_sales = '''
select id,emp_no,sum(money) money,purchase_date,product_no from sales
WHERE product_type in (%s) and emp_no = %s
and PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format(purchase_date, '%Y%m' ) ) = 1
group by emp_no
'''
cursor_sales.execute(sql_select_sales, (3, employee['user_no']))
sales = cursor_sales.fetchall()
for sale in sales:
if sale['money'] >= 1500000:
commission_scale = 0.006
if sale['money'] < 1500000:
commission_scale = 0.004
p_type = '契约型基金'
a = str(sale["purchase_date"]) #获得时间
timeArray = time.strptime(a, "%Y-%m-%d")
year = time.strftime("%Y", timeArray)
month = time.strftime("%m", timeArray)
establish_money = __getEstablishMoneySpecial(
sale['emp_no']) #本月成立金额
commission_money = int(establish_money) * commission_scale
sql_insert_commission_wealthCenter = '''
insert into commission_wealthCenter(emp_no,emp_name,dept_no,position_no,sales_money,
commission_scale,establish_money,commission_money,is_examine,year,month,product_no,type)
values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);
'''
cursor_employee.execute(sql_insert_commission_wealthCenter,(sale['emp_no'],employee['name'],employee['dept_no'],\
employee['position_no'],sale['money'],commission_scale,establish_money,commission_money,1,year,month,sale['product_no'],p_type))
except Exception, e:
print(e)
return False
def __main():
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cnn_base_info=mysql_conn.get_conn(db_config.config_base_info)
if not cnn_base_info:
__write_warn("can't get connection of base_info")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
cursor_base_info=cnn_base_info.cursor(dictionary=True)
sql_select_emp = '''
SELECT e.id,e.name,e.status,d.start_time
FROM employee e LEFT OUTER JOIN employee_detail d
ON e.id = d.emp_no WHERE e.`status` = 5 and
TO_DAYS(NOW()) - TO_DAYS(d.start_time) >= 60 and
TO_DAYS(NOW()) - TO_DAYS(d.start_time) <= 90
'''
cursor_employee.execute(sql_select_emp);
emps = cursor_employee.fetchall();
print emps
for emp in emps:
a = str(emp["start_time"]) #获得时间
timeArray = time.strptime(a, "%Y-%m-%d")
year = time.strftime("%Y", timeArray)
month = time.strftime("%m",timeArray)
day = time.strftime("%d",timeArray)
to = "*****@*****.**"
subject = "员工试用到期提醒"
endmonth = int(month)+3
starttime = str(year)+"年"+str(month)+"月"+str(day)+"日"
endtime = str(year)+"年"+str(endmonth)+"月"+str(day)+"日"
content = str(emp["name"])+starttime+"开始试用，试用期三个月，"+endtime+"试用结束,请及时沟通，确认是否到期转正"
print content
sql_insert_email = '''
insert into email
(`to`,subject,host,`user`,password,suffix,sender_name,body,status,in_user_no)
values(%s,%s,'mail.bestinvestor.com.cn','administrator','huazhen@123',
'bestinvestor.com.cn','系统管理员',%s,0,176)
'''
cursor_base_info.execute(sql_insert_email,(to,subject,content))
except Exception, e:
print (e)
return False
def __main():
try:
cnn_base_info = mysql_conn.get_conn(db_config.config_base_info)
if cnn_base_info == None:
__write_warn("can't get connection of notify", "__process_mails")
return
cursor_base_info = cnn_base_info.cursor(dictionary=True)
sql_select_sms = '''
select sysno,cellnumber,smscontent,retrycount from sms where status = 1;
'''
cursor_base_info.execute(sql_select_sms)
smsList = cursor_base_info.fetchall()
for sms in smsList:
sql_update_statu = '''
update sms set status = 2 where sysno = %s
''' % sms["sysno"]
cursor_base_info.execute(sql_update_statu)
if (send_sms(sms["cellnumber"], sms["smscontent"])):
sql_update_status = '''
update sms set status = 3 where sysno = %s
''' % sms["sysno"]
cursor_base_info.execute(sql_update_status)
else:
cursor_base_info.execute(sql_update_retrycount,
(sms["retrycount"] + 1, sms["sysno"]))
except Exception, e:
print(e)
return False
def __dept(deptNo):
try:
cnn_report = mysql_conn.get_conn(db_config.config_report)
if cnn_report == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_report = cnn_report.cursor(dictionary=True)
sql_one = ''' SELECT
product_name,
SUM(money_total) as money,
dept_name
FROM dept_sales_daily
WHERE TO_DAYS( NOW( ) ) - TO_DAYS( clac_time) = 1 %s
GROUP BY dept_name,product_name;
'''%deptNo
print sql_one
cursor_report.execute(sql_one)
mails = cursor_report.fetchall()
if len(mails)==0:
content = "null"
else:
content = ""
for mail in mails:
content = content + "<tbody><tr><td>" + str(mail["dept_name"])+"</td><td>"+str(mail["product_name"])+"</td><td>"+str(mail["money"])+"万元</td></tr>"
except Exception,e:
print(e);
return False
def __select_transaction():
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_transaction = '''
select id,transaction_type,name,before_dept,before_position,sex,after_dept,after_position,start_time,
try_wage,meal_subsidy,telephone_subsidy,tratfic_subsidy,transaction_time,before_wage,after_wage,end_time,
id_card,card_number,account_line,departture_debie,comment from employee_transaction
'''
cursor_employee.execute(sql_select_transaction)
transactions = cursor_employee.fetchall()
wbk = xlwt.Workbook()
sheet2 = wbk.add_sheet(u'人事异动表')
sheet1 = wbk.add_sheet(u'人事统计报表')
createXLSTitle(sheet1)
createXLSTitle1(sheet2)
__data(sheet1)
__None(sheet1)
for i, transaction in enumerate(transactions):
createXLS(sheet2,transaction,i)
year = str(time.strftime("%Y"))
month = str(int(time.strftime("%m")) - 1)
print year + "mmmmmmmmmmmm"
print month
wbk.save("/usr/local/apache-tomcat-8.0.11/webapps/upload/files/"+year+"/"+month+"/"+ unicode("人事报表")+".xls")
except Exception, e:
print (e)
return False
def __main():
try:
cnn_base_info=mysql_conn.get_conn(db_config.config_base_info)
if cnn_base_info == None:
__write_warn("can't get connection of notify","__process_mails")
return
cursor_base_info=cnn_base_info.cursor(dictionary=True)
sql_select_sms = '''
select sysno,cellnumber,smscontent,retrycount from sms where status = 1;
'''
cursor_base_info.execute(sql_select_sms)
smsList = cursor_base_info.fetchall()
for sms in smsList:
sql_update_statu = '''
update sms set status = 2 where sysno = %s
'''%sms["sysno"]
cursor_base_info.execute(sql_update_statu)
if(send_sms(sms["cellnumber"],sms["smscontent"])):
sql_update_status = '''
update sms set status = 3 where sysno = %s
'''%sms["sysno"]
cursor_base_info.execute(sql_update_status)
else:
cursor_base_info.execute(sql_update_retrycount,(sms["retrycount"]+1,sms["sysno"]))
except Exception, e:
print (e)
return False
def __select_transaction():
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_transaction = '''
select id,transaction_type,name,before_dept,before_position,sex,after_dept,after_position,start_time,
try_wage,meal_subsidy,telephone_subsidy,tratfic_subsidy,transaction_time,before_wage,after_wage,end_time,
id_card,card_number,account_line,departture_debie,comment from employee_transaction
'''
cursor_employee.execute(sql_select_transaction)
transactions = cursor_employee.fetchall()
wbk = xlwt.Workbook()
sheet2 = wbk.add_sheet(u'人事异动表')
sheet1 = wbk.add_sheet(u'人事统计报表')
createXLSTitle(sheet1)
createXLSTitle1(sheet2)
__data(sheet1)
__None(sheet1)
for i, transaction in enumerate(transactions):
createXLS(sheet2, transaction, i)
year = str(time.strftime("%Y"))
month = str(int(time.strftime("%m")) - 1)
print year + "mmmmmmmmmmmm"
print month
wbk.save("/usr/local/apache-tomcat-8.0.11/webapps/upload/files/" +
year + "/" + month + "/" + unicode("人事报表") + ".xls")
except Exception, e:
print(e)
return False
def __emp(deptNo):
try:
cnn_report = mysql_conn.get_conn(db_config.config_report)
if cnn_report == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_report = cnn_report.cursor(dictionary=True)
sql_emp = '''SELECT
product_name,emp_name,SUM(money_total) as money
from emp_sales_daily
where emp_no in(
select user_no
from employee.employee
where TO_DAYS( NOW( ) ) - TO_DAYS( clac_time) = 1 %s
)GROUP BY product_name,emp_name;
'''%deptNo
print(sql_emp)
cursor_report.execute(sql_emp)
mails = cursor_report.fetchall()
if len(mails)==0:
content = "null"
else:
content = ""
for mail in mails:
content = content + "<tbody><tr><td>" + str(mail["product_name"])+"</td><td>"+str(mail["emp_name"])+"</td><td>"+str(mail["money"])+"万元</td></tr>"
except Exception,e:
print(e)
return False
def __emp(deptNo):
try:
cnn_report = mysql_conn.get_conn(db_config.config_report)
if cnn_report == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_report = cnn_report.cursor(dictionary=True)
sql_emp = '''SELECT
product_name,emp_name,SUM(money_total) as money
from emp_sales_daily
where emp_no in(
select user_no
from employee.employee
where TO_DAYS( NOW( ) ) - TO_DAYS( clac_time) = 1 %s
)GROUP BY product_name,emp_name;
''' % deptNo
print(sql_emp)
cursor_report.execute(sql_emp)
mails = cursor_report.fetchall()
if len(mails) == 0:
content = "null"
else:
content = ""
for mail in mails:
content = content + "<tbody><tr><td>" + str(
mail["product_name"]) + "</td><td>" + str(
mail["emp_name"]) + "</td><td>" + str(
mail["money"]) + "万元</td></tr>"
except Exception, e:
print(e)
return False
def __dept(deptNo):
try:
cnn_report = mysql_conn.get_conn(db_config.config_report)
if cnn_report == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_report = cnn_report.cursor(dictionary=True)
sql_one = ''' SELECT
product_name,
SUM(money_total) as money,
dept_name
FROM dept_sales_daily
WHERE TO_DAYS( NOW( ) ) - TO_DAYS( clac_time) = 1 %s
GROUP BY dept_name,product_name;
''' % deptNo
print sql_one
cursor_report.execute(sql_one)
mails = cursor_report.fetchall()
if len(mails) == 0:
content = "null"
else:
content = ""
for mail in mails:
content = content + "<tbody><tr><td>" + str(
mail["dept_name"]) + "</td><td>" + str(
mail["product_name"]) + "</td><td>" + str(
mail["money"]) + "万元</td></tr>"
except Exception, e:
print(e)
return False
def __main():
try:
cnn_sales = mysql_conn.get_conn(db_config.config_sales)
if cnn_sales == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_sales = cnn_sales.cursor(dictionary=True)
cnn_base_info = mysql_conn.get_conn(db_config.config_base_info)
if cnn_base_info == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_base_info = cnn_base_info.cursor(dictionary=True)
sql_select_sales = '''
select id,customer_no,money,product_no from sales where `status` = 3 and is_send_sms = 0
'''
cursor_sales.execute(sql_select_sales)
sales = cursor_sales.fetchall()
for sale in sales:
sql_insert_sms = '''
insert into sms(cellnumber,smscontent,status) value(%s,%s,%s);
'''
customers = __getCustoemrByCustoemrNo(sale['customer_no'])
for customer in customers:
customerName = customer['name']
templetes = __getSmsTempleteByKey(3)
for templete in templetes:
content = templete['content']
products = __getProductByNo(sale['product_no'])
for product in products:
productName = product['name']
content = content.replace('${USER_NAME}', customerName)
content = content.replace('${AMOUNT}', str(sale['money']))
content = content.replace('${PRODUCT_NAME}',
"'" + productName + "'")
cursor_base_info.execute(sql_insert_sms,
(customer['cellphone1'], content, 1))
sql_update_sales = '''
update sales set is_send_sms = 1 where id = %s
''' % sale['id']
cursor_sales.execute(sql_update_sales)
except Exception, e:
print(e)
return False
def __main():
try:
cnn_report = mysql_conn.get_conn(db_config.config_report)
if cnn_report == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_report = cnn_report.cursor(dictionary=True)
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_employee = '''
select id,name,user_no,dept_no,company_no,status
from employee where dept_no in (10,11,5,13,14,15,16,17,18,19)
'''
cursor_employee.execute(sql_select_employee);
employees = cursor_employee.fetchall();
for employee in employees:
dept_no = employee['dept_no']
company_no = employee['company_no']
emp_name = employee['name']
emp_status = employee['status']
year = time.strftime('%Y')
month = time.strftime('%m')
day = time.strftime('%d')
week = int(time.strftime('%W'))-1
add_a = __getAddCountByLevelAndAgentNo(4,employee['user_no'])
add_b = __getAddCountByLevelAndAgentNo(3,employee['user_no'])
add_c = __getAddCountByLevelAndAgentNo(2,employee['user_no'])
add_d = __getAddCountByLevelAndAgentNo(1,employee['user_no'])
customer_count = __getCustomerCountByAgentNo(employee['user_no'])
add_employee_count = add_a + add_b + add_c + add_d
report_type = 2
sql_insert_add_employee_report = '''
insert into add_customer_report(emp_no,dept_no,company_no,add_a,
add_b,add_c,add_d,add_employee_count,employee_count,type,emp_name,emp_status,year,month,day,week)
values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);
'''
cursor_report.execute(sql_insert_add_employee_report,(employee['user_no'],dept_no,company_no,\
add_a,add_b,add_c,add_d,add_employee_count,customer_count,report_type,emp_name,emp_status,year,month,day,week));
except Exception, e:
print (e)
return False
def __main():
try:
cnn_report = mysql_conn.get_conn(db_config.config_report)
if cnn_report == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_report = cnn_report.cursor(dictionary=True)
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_employee = '''
select id,name,user_no,dept_no,company_no,status
from employee where dept_no in (10,11,5,13,14,15,16,17,18,19)
'''
cursor_employee.execute(sql_select_employee)
employees = cursor_employee.fetchall()
for employee in employees:
dept_no = employee['dept_no']
company_no = employee['company_no']
emp_name = employee['name']
emp_status = employee['status']
year = time.strftime('%Y')
month = time.strftime('%m')
day = time.strftime('%d')
week = int(time.strftime('%W')) - 1
add_a = __getAddCountByLevelAndAgentNo(4, employee['user_no'])
add_b = __getAddCountByLevelAndAgentNo(3, employee['user_no'])
add_c = __getAddCountByLevelAndAgentNo(2, employee['user_no'])
add_d = __getAddCountByLevelAndAgentNo(1, employee['user_no'])
customer_count = __getCustomerCountByAgentNo(employee['user_no'])
add_employee_count = add_a + add_b + add_c + add_d
report_type = 2
sql_insert_add_employee_report = '''
insert into add_customer_report(emp_no,dept_no,company_no,add_a,
add_b,add_c,add_d,add_employee_count,employee_count,type,emp_name,emp_status,year,month,day,week)
values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);
'''
cursor_report.execute(sql_insert_add_employee_report,(employee['user_no'],dept_no,company_no,\
add_a,add_b,add_c,add_d,add_employee_count,customer_count,report_type,emp_name,emp_status,year,month,day,week))
except Exception, e:
print(e)
return False
def __main():
try:
cnn_sales = mysql_conn.get_conn(db_config.config_sales)
if cnn_sales == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_sales = cnn_sales.cursor(dictionary=True)
cnn_base_info = mysql_conn.get_conn(db_config.config_base_info)
if cnn_base_info == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_base_info = cnn_base_info.cursor(dictionary=True)
sql_select_sales = '''
select id,customer_no,money,product_no from sales where `status` = 3 and is_send_sms = 0
'''
cursor_sales.execute(sql_select_sales);
sales = cursor_sales.fetchall();
for sale in sales:
sql_insert_sms = '''
insert into sms(cellnumber,smscontent,status) value(%s,%s,%s);
'''
customers = __getCustoemrByCustoemrNo(sale['customer_no'])
for customer in customers:
customerName = customer['name'];
templetes = __getSmsTempleteByKey(3);
for templete in templetes:
content = templete['content']
products = __getProductByNo(sale['product_no'])
for product in products:
productName = product['name']
content = content.replace('${USER_NAME}',customerName)
content = content.replace('${AMOUNT}',str(sale['money']))
content = content.replace('${PRODUCT_NAME}',"'"+productName+"'")
cursor_base_info.execute(sql_insert_sms,(customer['cellphone1'],content,1))
sql_update_sales = '''
update sales set is_send_sms = 1 where id = %s
'''%sale['id']
cursor_sales.execute(sql_update_sales);
except Exception, e:
print (e)
return False
def __process_mails():
try:
cnn_base_info=mysql_conn.get_conn(db_config.config_base_info)
if cnn_base_info == None:
__write_warn("can't get connection of notify","__process_mails")
return
cursor_base_info=cnn_base_info.cursor(dictionary=True)
sql_top_10_mail="""SELECT
`email`.`id`,
`email`.`to`,
`email`.`cc`,
`email`.`subject`,
`email`.`host`,
`email`.`user`,
`email`.`password`,
`email`.`suffix`,
`email`.`sender_name`,
`email`.`body`,
`email`.`from_name`
FROM
`email`
WHERE
`email`.`status` = 0
ORDER BY id ASC
LIMIT 5;"""
cursor_base_info.execute(sql_top_10_mail)
dict_mail_list = cursor_base_info.fetchall()
for mail in dict_mail_list:
print mail["id"]
sql_select_email_files = '''
select email_no,file_url,file_name from email_files where email_no = %s
'''%mail["id"]
cursor_base_info.execute(sql_select_email_files)
email_files = cursor_base_info.fetchall()
namelist = []
for email_file in email_files:
url = "http://192.168.1.236/upload/files"+str(email_file["file_url"])
name = download(sys.argv[1:],url)
namelist.append(name)
if mail_sender.send_mail([str(mail["to"])],str(mail["subject"]),str(mail["body"]),namelist,str(mail["sender_name"]),str(mail["host"]),str(mail["user"]),str(mail["password"]),str(mail["suffix"]),str(mail["from_name"])):
sql_update_mail_status ="""
UPDATE `email` SET `status`=1 WHERE id = %s
"""%mail["id"]
cursor_base_info.execute(sql_update_mail_status)
__write_info("send success","__process_mails")
else:
__write_info("send failed","__process_mails")
except mysql.connector.Error as e:
__write_err('raise exception when sending mail!{}'.format(e))
finally:
cursor_base_info.close()
cnn_base_info.close()
def __getProductByNo(productNo):
try:
cnn_product = mysql_conn.get_conn(db_config.config_product)
if cnn_product == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_product = cnn_product.cursor(dictionary=True)
sql_select_product = '''
select id,name from product where id = %s
'''%productNo
cursor_product.execute(sql_select_product);
products = cursor_product.fetchall()
return products
except Exception, e:
raise
def __getProductByNo(productNo):
try:
cnn_product = mysql_conn.get_conn(db_config.config_product)
if cnn_product == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_product = cnn_product.cursor(dictionary=True)
sql_select_product = '''
select id,name from product where id = %s
''' % productNo
cursor_product.execute(sql_select_product)
products = cursor_product.fetchall()
return products
except Exception, e:
raise
def __getCertificateByEmpNo(emp_no):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_certificate = '''
select credential,credential_date from employee_credential where emp_no = %s
''' % emp_no
cursor_employee.execute(sql_select_certificate)
certificates = cursor_employee.fetchall()
return certificates
except Exception, e:
print(e)
return False
def __getDegreeByEmpNo(emp_no):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_degree = '''
select education,graduation_school,major from employee_education where emp_no = %s
''' % emp_no
cursor_employee.execute(sql_select_degree)
degrees = cursor_employee.fetchall()
return degrees
except Exception, e:
print(e)
return False
def __getEmpNumberByDeptAndPosition(dept_no, position_no):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_employee = '''
select count(*) count from employee where dept_no = %s and position_no = %s
'''
cursor_employee.execute(sql_select_employee, (dept_no, position_no))
empNumbers = cursor_employee.fetchall()
return empNumbers
except Exception, e:
print(e)
return False
def __updateIsExamineById(id):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_update_examine = '''
update subsidy_total set is_examine = 3 where id = %s
'''%id
cursor_employee.execute(sql_update_examine)
except Exception, e:
print (e)
return False
def __updateIsExamineById(id):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_update_examine = '''
update subsidy_total set is_examine = 3 where id = %s
''' % id
cursor_employee.execute(sql_update_examine)
except Exception, e:
print(e)
return False
def __getCustoemrByCustoemrNo(customerNo):
try:
cnn_customer = mysql_conn.get_conn(db_config.config_customer)
if cnn_customer == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_customer= cnn_customer.cursor(dictionary=True)
sql_select_customer = '''
select id,name,cellphone1 from customer_personal where id = %s
'''%customerNo
cursor_customer.execute(sql_select_customer);
customers = cursor_customer.fetchall();
return customers
except Exception, e:
print (e)
return False
def __getCertificateByEmpNo(emp_no):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_certificate = '''
select credential,credential_date from employee_credential where emp_no = %s
'''%emp_no
cursor_employee.execute(sql_select_certificate)
certificates= cursor_employee.fetchall()
return certificates
except Exception, e:
print (e)
return False
def __getDegreeByEmpNo(emp_no):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_degree = '''
select education,graduation_school,major from employee_education where emp_no = %s
'''%emp_no
cursor_employee.execute(sql_select_degree);
degrees = cursor_employee.fetchall();
return degrees
except Exception, e:
print (e)
return False
def __getSmsTempleteByKey(key):
try:
cnn_base_info = mysql_conn.get_conn(db_config.config_base_info)
if cnn_base_info == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_base_info = cnn_base_info.cursor(dictionary=True)
sql_select_templete = '''
select content,subject from smstemplete where sysno = %s
'''%key
cursor_base_info.execute(sql_select_templete);
templetes = cursor_base_info.fetchall();
return templetes
except Exception, e:
print (e)
return False
def __getSmsTempleteByKey(key):
try:
cnn_base_info = mysql_conn.get_conn(db_config.config_base_info)
if cnn_base_info == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_base_info = cnn_base_info.cursor(dictionary=True)
sql_select_templete = '''
select content,subject from smstemplete where sysno = %s
''' % key
cursor_base_info.execute(sql_select_templete)
templetes = cursor_base_info.fetchall()
return templetes
except Exception, e:
print(e)
return False
def __getCustoemrByCustoemrNo(customerNo):
try:
cnn_customer = mysql_conn.get_conn(db_config.config_customer)
if cnn_customer == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_customer = cnn_customer.cursor(dictionary=True)
sql_select_customer = '''
select id,name,cellphone1 from customer_personal where id = %s
''' % customerNo
cursor_customer.execute(sql_select_customer)
customers = cursor_customer.fetchall()
return customers
except Exception, e:
print(e)
return False
def __getEmpNumberByDeptAndPosition(dept_no,position_no):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_employee = '''
select count(*) count from employee where dept_no = %s and position_no = %s
'''
cursor_employee.execute(sql_select_employee,(dept_no,position_no));
empNumbers = cursor_employee.fetchall();
return empNumbers
except Exception, e:
print(e)
return False
def __getStoreManagerByDeptNo(dept_no):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_store_manager = '''
select name,user_no from employee
where dept_no = %s and position_no = 14;
'''%dept_no
cursor_employee.execute(sql_store_manager);
results = cursor_employee.fetchall();
return results
except Exception, e:
print (e)
return False
def __getStoreManagerByDeptNo(dept_no):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_store_manager = '''
select name,user_no from employee
where dept_no = %s and position_no = 14;
''' % dept_no
cursor_employee.execute(sql_store_manager)
results = cursor_employee.fetchall()
return results
except Exception, e:
print(e)
return False
def __getStatusByEmpNo(empNo):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_employee = '''
select name,status from employee
where user_no = %s
'''%empNo
cursor_employee.execute(sql_select_employee);
results = cursor_employee.fetchall();
return results
except Exception, e:
print (e)
return False
def __getStatusByEmpNo(empNo):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_employee = '''
select name,status from employee
where user_no = %s
''' % empNo
cursor_employee.execute(sql_select_employee)
results = cursor_employee.fetchall()
return results
except Exception, e:
print(e)
return False
def __getPositionByPositionNo(position_no):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_dept = '''
select name from position where id = %s
'''%position_no
cursor_employee.execute(sql_select_dept)
positions = cursor_employee.fetchall()
for position in positions:
return position['name']
except Exception, e:
print (e)
return False
def __getDeptNameByDeptNo(dept_no):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_dept = '''
select name from department where id = %s
'''%dept_no
cursor_employee.execute(sql_select_dept)
depts = cursor_employee.fetchall()
for dept in depts:
return dept['name']
except Exception, e:
print (e)
return False
def __getDeptNameByDeptNo(dept_no):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_dept = '''
select name from department where id = %s
''' % dept_no
cursor_employee.execute(sql_select_dept)
depts = cursor_employee.fetchall()
for dept in depts:
return dept['name']
except Exception, e:
print(e)
return False
def __getPositionByPositionNo(position_no):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_dept = '''
select name from position where id = %s
''' % position_no
cursor_employee.execute(sql_select_dept)
positions = cursor_employee.fetchall()
for position in positions:
return position['name']
except Exception, e:
print(e)
return False
def __main():
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_subsidy_total = '''
select id,emp_no,emp_name,dept_no,position_no,
subsidy_scale,sales_money,subsidy,achieve_time,is_examine
from subsidy_total
'''
cursor_employee.execute(sql_select_subsidy_total)
subsidy_totals = cursor_employee.fetchall()
for subsidy_total in subsidy_totals:
if subsidy_total['is_examine'] != 2: #状态为审核的插入到subsidy
continue
else:
money = subsidy_total['subsidy'] / 6.0
for i in range(1, 7):
sql_send_time = '''select DATE_ADD(%s,INTERVAL %s MONTH) send_time'''
cursor_employee.execute(sql_send_time,
(subsidy_total['achieve_time'], i))
send_times = cursor_employee.fetchall()
for send_time in send_times:
timeArray = time.strptime(send_time['send_time'],
"%Y-%m-%d")
send_time = datetime.datetime.strptime(
time.strftime("%Y-%m", timeArray), "%Y-%m").date()
sql_insert_subsidy = '''
insert into subsidy(emp_no,emp_name,dept_no,
position_no,money,source,send_time,in_time)
values(%s,%s,%s,%s,round(%s,2),%s,%s,now())'''
cursor_employee.execute(sql_insert_subsidy,(subsidy_total['emp_no'],subsidy_total['emp_name'],\
subsidy_total['dept_no'],14,money,subsidy_total['id'],send_time))
__updateIsExamineById(subsidy_total['id'])
#插入成功后状态更改为3(完成)
except Exception, e:
print(e)
return False
def __getNowNumberPuTong():
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_guanli = '''
select count(*) count from employee where `status` in(1,4,5)
and id not in(select parent_no from employee)
'''
cursor_employee.execute(sql_select_guanli)
nums = cursor_employee.fetchall()
for num in nums:
number = num['count']
return number
except Exception, e:
print (e)
return False
def __getNowNumberByDeptNo(dept_no):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_zaizhi = '''
select count(*) count from employee where status in (1,4,5) and dept_no = %s
''' % dept_no
cursor_employee.execute(sql_select_zaizhi)
nums = cursor_employee.fetchall()
for num in nums:
number = num['count']
return number
except Exception, e:
print(e)
return False
def __getAvgAge():
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_age = '''
select avg(birthday_year) age from employee_information
'''
cursor_employee.execute(sql_select_age)
nums = cursor_employee.fetchall()
for num in nums:
number = num['age']
return number
except Exception, e:
print(e)
return False
def __getLiZhiNumberByDepts(dept_nos):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_dept = '''
select count(*) count from resign_application where dept_no in (%s)
''' % dept_nos
cursor_employee.execute(sql_select_dept)
nums = cursor_employee.fetchall()
for num in nums:
number = num['count']
return number
except Exception, e:
print(e)
return False
def __getResultByActivitiNo(activiti_no):
try:
cnn_work_flow = mysql_conn.get_conn(db_config.config_work_flow)
if cnn_work_flow == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_work_flow = cnn_work_flow.cursor(dictionary=True)
sql_select_result = '''
SELECT count(*) count FROM `ACT_HI_PROCINST` WHERE END_ACT_ID_ IS NOT NULL AND PROC_INST_ID_= %s ;
''' % activiti_no
cursor_work_flow.execute(sql_select_result)
results = cursor_work_flow.fetchall()
for result in results:
res = result['count']
return res
except Exception, e:
print(e)
return False
def __getLizhiNumberGuanLi():
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_putong = '''
select count(*) count from resign_application
where emp_no in (select parent_no from employee)
'''
cursor_employee.execute(sql_select_putong)
nums = cursor_employee.fetchall()
for num in nums:
number = num['count']
return number
except Exception, e:
print (e)
return False
def __getLizhiNumberGuanLi():
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_putong = '''
select count(*) count from resign_application
where emp_no in (select parent_no from employee)
'''
cursor_employee.execute(sql_select_putong)
nums = cursor_employee.fetchall()
for num in nums:
number = num['count']
return number
except Exception, e:
print(e)
return False
def __getResultByActivitiNo(activiti_no):
try:
cnn_work_flow = mysql_conn.get_conn(db_config.config_work_flow)
if cnn_work_flow == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_work_flow = cnn_work_flow.cursor(dictionary=True)
sql_select_result = '''
SELECT count(*) count FROM `ACT_HI_PROCINST` WHERE END_ACT_ID_ IS NOT NULL AND PROC_INST_ID_= %s ;
'''%activiti_no
cursor_work_flow.execute(sql_select_result);
results = cursor_work_flow.fetchall()
for result in results:
res = result['count']
return res
except Exception, e:
print (e)
return False
def __getNowNumberByAge(age):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
year = time.strftime("%Y");
sql_select = '''
select count(*) count from employee_information where %s
'''%age
cursor_employee.execute(sql_select)
nums = cursor_employee.fetchall()
for num in nums:
number = num['count']
return number
except Exception, e:
print (e)
return False
def __getLiZhiNumberByDepts(dept_nos):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_dept = '''
select count(*) count from resign_application where dept_no in (%s)
'''%dept_nos
cursor_employee.execute(sql_select_dept)
nums = cursor_employee.fetchall()
for num in nums:
number = num['count']
return number
except Exception, e:
print (e)
return False
def __getCommissionScale(finish_level):#根据完成比例和职级查询提成比例
try:
cnn_report = mysql_conn.get_conn(db_config.config_report)
if cnn_report == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_report = cnn_report.cursor(dictionary=True)
sql_jiben = '''
select id,commission_scale cs
from commission_rule
where finish_level = %s;
'''%finish_level
cursor_report.execute(sql_jiben);
result = cursor_report.fetchall();
return result;
except Exception, e:
print (e)
return False
def __getAvgAge():
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_age = '''
select avg(birthday_year) age from employee_information
'''
cursor_employee.execute(sql_select_age)
nums = cursor_employee.fetchall()
for num in nums:
number = num['age']
return number
except Exception, e:
print (e)
return False
def __getCommissionScale(finish_level): #根据完成比例和职级查询提成比例
try:
cnn_report = mysql_conn.get_conn(db_config.config_report)
if cnn_report == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_report = cnn_report.cursor(dictionary=True)
sql_jiben = '''
select id,commission_scale cs
from commission_rule
where finish_level = %s;
''' % finish_level
cursor_report.execute(sql_jiben)
result = cursor_report.fetchall()
return result
except Exception, e:
print(e)
return False
def __getNowNumberPuTong():
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_guanli = '''
select count(*) count from employee where `status` in(1,4,5)
and id not in(select parent_no from employee)
'''
cursor_employee.execute(sql_select_guanli)
nums = cursor_employee.fetchall()
for num in nums:
number = num['count']
return number
except Exception, e:
print(e)
return False
def __getAddCountByLevelAndAgentNo(level,agent_no):
try:
cnn_customer = mysql_conn.get_conn(db_config.config_customer)
if cnn_customer == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_customer = cnn_customer.cursor(dictionary=True)
sql_select_add_count = '''
select count(*) count from customer_personal where relation_level = %s and agent_no = %s and
TO_DAYS( NOW( ) ) - TO_DAYS( in_time) <= 1
'''
cursor_customer.execute(sql_select_add_count,(level,agent_no));
add_counts = cursor_customer.fetchall();
for add_count in add_counts:
count = add_count['count']
return count
except Exception, e:
print (e)
return False
def __getNowNumberByAge(age):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
year = time.strftime("%Y")
sql_select = '''
select count(*) count from employee_information where %s
''' % age
cursor_employee.execute(sql_select)
nums = cursor_employee.fetchall()
for num in nums:
number = num['count']
return number
except Exception, e:
print(e)
return False
def __getEmpNameByDeptAndPosition(dept_no, position_no):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_employee = '''
select name from employee where dept_no = %s and position_no = %s
'''
cursor_employee.execute(sql_select_employee, (dept_no, position_no))
emps = cursor_employee.fetchall()
all_emp_name = ""
for emp in emps:
all_emp_name = all_emp_name + emp["name"] + ";"
return all_emp_name
except Exception, e:
print(e)
return False
def __getNowNumberByDeptNo(dept_no):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_zaizhi = '''
select count(*) count from employee where status in (1,4,5) and dept_no = %s
'''%dept_no
cursor_employee.execute(sql_select_zaizhi)
nums = cursor_employee.fetchall()
for num in nums:
number = num['count']
return number
except Exception, e:
print (e)
return False
def __getEmpNameByDeptAndPosition(dept_no,position_no):
try:
cnn_employee = mysql_conn.get_conn(db_config.config_employee)
if cnn_employee == None:
__write_warn("Can't get connection of notify","__process_mails")
return
cursor_employee = cnn_employee.cursor(dictionary=True)
sql_select_employee = '''
select name from employee where dept_no = %s and position_no = %s
'''
cursor_employee.execute(sql_select_employee,(dept_no,position_no));
emps = cursor_employee.fetchall();
all_emp_name = ""
for emp in emps:
all_emp_name = all_emp_name + emp["name"] + ";"
return all_emp_name
except Exception, e:
print (e)
return False
def __getAddCountByLevelAndAgentNo(level, agent_no):
try:
cnn_customer = mysql_conn.get_conn(db_config.config_customer)
if cnn_customer == None:
__write_warn("Can't get connection of notify", "__process_mails")
return
cursor_customer = cnn_customer.cursor(dictionary=True)
sql_select_add_count = '''
select count(*) count from customer_personal where relation_level = %s and agent_no = %s and
TO_DAYS( NOW( ) ) - TO_DAYS( in_time) <= 1
'''
cursor_customer.execute(sql_select_add_count, (level, agent_no))
add_counts = cursor_customer.fetchall()
for add_count in add_counts:
count = add_count['count']
return count
except Exception, e:
print(e)
return False
