# coding=utf-8
import MySQLdb
import datetime
import time

# 安装mysqlDB,yum install MySQL-python

print("执行......日志")

# 读取昨天的日志并且把结果拼接成字符串存入到数据库中
db = MySQLdb.connect("47.96.251.37","aliyuntest","foxtry@aliyuntest","futures")
def getYesterday():
    today=datetime.date.today()
    oneday=datetime.timedelta(days=1)
    yesterday=today-oneday
    timeStruct = time.strptime(str(yesterday), "%Y-%m-%d")
    yesterdayTime = time.localtime(int(time.mktime(timeStruct)))
    return time.strftime("%Y%m%d", yesterdayTime)

dateStr=str(getYesterday())
filepath="lion_futuresManage.log_"+dateStr+".log"
file = open(filepath,"r")

# file 文件
orderfile=open("order"+dateStr+".sql", "w")
cancellfile=open("cancel"+dateStr+".sql","w")
zyzsfile=open("zyzs"+dateStr+".sql","w")

# 读取日志文件并拼接出sql存入数据库中
while True:
    line = file.readline()
    if not line:
        file.close()
        break
    if "cancel by user" in line:
        cancelDate=line[0:19]
        orderId=line.split("orderId: [")[1].split("].")[0]
        cacelsql="INSERT INTO futures.user_cancel_order_log (order_id, cancel_date) VALUES ( "+ "'" + orderId + "'" +", "+ "'" +cancelDate + "'"  +");\n"
        cancellfile.write(cacelsql)
    elif "user order" in line:
        ordersqlarr = line.split("user order 用户下单 . ")[1].split(", ")
        orderdate=line[0:19]
        ordersql="INSERT INTO futures.user_order_log (user_id, future_code, openclose, order_type, side, price, num, client_ip, " \
                 "order_date) VALUES ("+ordersqlarr[0].split(": [")[1].split("]")[0]+", "+"'" + ordersqlarr[1].split(": [")[1].split("]")[0]+"'" + ", "+ordersqlarr[2].split(": [")[1].split("]")[0]+\
                 ", "+ordersqlarr[3].split(": [")[1].split("]")[0]+", "+ordersqlarr[4].split(": [")[1].split("]")[0]+\
                 ", "+ordersqlarr[5].split(": [")[1].split("]")[0]+", "+ordersqlarr[6].split(": [")[1].split("]")[0]+\
                 ", "+"'" + ordersqlarr[7].split(": [")[1].split("]")[0]+"'" + ", "+ "'"+orderdate+"'" + ");\n"
        orderfile.write(ordersql)
    elif "setZyzs. 设置止盈止损" in line:
        zyzsArr = line.split("setZyzs. 设置止盈止损. ")[1].split("], ")
        setDate = line[0:19]
        zyzsSql="INSERT INTO futures.user_zyzs_log (user_id, holding_id, price_zy, stop_profit, price_zs, stop_loss, num, order_type, price_zy_rt, price_profit_rt, price_zs_rt, " \
                "stop_loss_rt, set_date) VALUES ("+zyzsArr[0].split("[")[1]+", "+zyzsArr[1].split("[")[1]+", '"+zyzsArr[2].split("[")[1]+"', '"+zyzsArr[3].split("[")[1]+"', '"+zyzsArr[4].split("[")[1]+"', '"+zyzsArr[5].split("[")[1]+"', "+zyzsArr[6].split("[")[1]+", "+zyzsArr[7].split("[")[1]+", '"+zyzsArr[8].split("[")[1]+"', '"+zyzsArr[9].split("[")[1]+"', '"+zyzsArr[10].split("[")[1]+"', '"+zyzsArr[11].split("[")[1].split("]")[0]+"', '"+setDate+"');\n"
        zyzsfile.write(zyzsSql)
    else:
        continue
orderfile.close()
cancellfile.close()
zyzsfile.close()


# 获取statement
cursor = db.cursor()
ordersql=open("order"+dateStr+".sql", "r")
cancelsql=open("cancel"+dateStr+".sql","r")
zyzssqls=open("zyzs"+dateStr+".sql","r")

while True:
    linesql = ordersql.readline()
    if not linesql:
        ordersql.close()
        break
    cursor.execute(linesql)

while True:
    csql = cancelsql.readline()
    if not csql:
        cancelsql.close()
        break
    cursor.execute(csql)
while True:
    zyzsExe = zyzssqls.readline()
    if not zyzsExe:
        zyzssqls.close()
        break
    cursor.execute(zyzsExe)
db.commit()
#关闭数据库连接
cursor.close()
db.close()

"""
sql 相关表
CREATE TABLE `user_cancel_order_log` (
  `id` bigint(16) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cancel_date` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2347 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `user_order_log` (
  `id` bigint(16) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(16) DEFAULT NULL,
  `future_code` varchar(255) DEFAULT NULL,
  `openclose` int(11) DEFAULT NULL,
  `order_type` int(11) DEFAULT NULL,
  `side` int(11) DEFAULT NULL,
  `price` decimal(18,2) DEFAULT NULL,
  `num` int(11) DEFAULT NULL,
  `client_ip` varchar(255) DEFAULT NULL,
  `order_date` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16563 DEFAULT CHARSET=utf8mb4 COMMENT='用户下单日志';

CREATE TABLE `user_zyzs_log` (
  `id` bigint(16) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(16) DEFAULT NULL,
  `holding_id` bigint(16) DEFAULT NULL,
  `price_zy` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `stop_profit` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `price_zs` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `stop_loss` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `num` int(11) DEFAULT NULL,
  `order_type` int(11) DEFAULT NULL,
  `price_zy_rt` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `price_profit_rt` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `price_zs_rt` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `stop_loss_rt` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `set_date` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2281 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

"""