-
Notifications
You must be signed in to change notification settings - Fork 0
/
report.py
57 lines (41 loc) · 2.46 KB
/
report.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
#-*-coding:utf-8-*-
import os
import datetime
import sys
import pymysql
import subprocess
import db
import sendmail
to_mail_list = "ouyangyt@mmbrk.com;lifu@mmbrk.com"
def select_1():
alias = ["经纪人","姓名","股票代码","委托时间","委托方向","委托币种","委托价格","已成交数量"]
sql = """
SELECT IFNULL(c.name,d.channel_name) 经纪人,b.name 姓名,stock_code 股票代码
,entrust_datetime 委托时间,CASE WHEN entrust_bs=0 THEN '买' WHEN entrust_bs=1 THEN '卖' WHEN entrust_bs=2 THEN 'short' END 委托方向
,CASE WHEN currency=0 THEN '港币' WHEN currency=1 THEN '美金' WHEN currency=2 THEN '人民币' END 委托币种
,CASE WHEN entrust_order_type=1 THEN latest_filled_price WHEN entrust_order_type!=1 THEN entrust_price END 委托价格
,filled_amount 已成交数量 FROM trade_entrust_order a LEFT JOIN customer_info b ON a.customer_no=b.customer_no
LEFT JOIN broker_info c ON b.broker_id = c.id
LEFT JOIN channel_info d ON b.channel_id=d.channel_id WHERE a.local_order_type = 0 AND a.is_preview_order = 1 AND a.filled_amount!=0
AND c.name LIKE '浙江财富%'
"""
db.query(sql,alias,'浙江财富.csv')
sendmail.send_mail(to_mail_list,u'浙江财富.csv')
def select_2():
alias = ["经纪人/渠道","姓名","股票代码","委托时间","委托方向","委托币种","委托价格","已成交数量"]
sql = """
SELECT IFNULL(c.name,d.channel_name) 经纪人,b.name 姓名,stock_code 股票代码
,entrust_datetime 委托时间,CASE WHEN entrust_bs=0 THEN '买' WHEN entrust_bs=1 THEN '卖' WHEN entrust_bs=2 THEN 'short' END 委托方向
,CASE WHEN currency=0 THEN '港币' WHEN currency=1 THEN '美金' WHEN currency=2 THEN '人民币' END 委托币种
,CASE WHEN entrust_order_type=1 THEN latest_filled_price WHEN entrust_order_type!=1 THEN entrust_price END 委托价格
,filled_amount 已成交数量 FROM trade_entrust_order a LEFT JOIN customer_info b ON a.customer_no=b.customer_no
LEFT JOIN broker_info c ON b.broker_id = c.id
LEFT JOIN channel_info d ON b.channel_id=d.channel_id WHERE a.local_order_type = 0 AND a.is_preview_order = 1 AND a.filled_amount!=0
"""
db.query(sql,alias,'全量.csv')
sendmail.send_mail(to_mail_list,u'全量.csv')
def select():
select_1()
select_2()
if __name__ == '__main__':
select()