#! /usr/bin/env python
# -*- coding: utf-8 -*-
# Date: 2020/7/12 16:27
# @Author:bxh
# @file: pg_db_func.py
import psycopg2
from datetime import date, timedelta
import datetime,time


def get_today():
    now_today = datetime.datetime.now()
    str_time = now_today.strftime("%Y-%m-%d")
    return str_time

def get_tomorrow():
    tomorrow = (date.today() + timedelta(days=1)).strftime("%Y-%m-%d")
    return tomorrow
def getMonth():
    year = str(datetime.date.today().year)
    d = datetime.date.today()
    month = '%02d' % d.month
    if month in ['01', '03', '05', '07', '08', '10', '12']:
        s = str("-01 00:00:00")
        e = str("-31 23:59:59")
        startTime = year + "-" + str(month) + s
        endTime = year + "-" + str(month) + e
        list = [startTime, endTime]
        return list
    elif month == '02':
        s = str("-01 00:00:00")
        e = str("-28 23:59:59")
        startTime = year + "-" + str(month) + s
        endTime = year + "-" + str(month) + e
        list = [startTime, endTime]
        return list
    else:
        s = str("-01 00:00:00")
        e = str("-30 23:59:59")
        startTime = year + "-" + str(month) + s
        endTime = year + "-" + str(month) + e
        list = [startTime, endTime]
        return list
list_time = getMonth()
def get_starTimestamp():
    return list_time[0]
def get_endTimestamp():
    return list_time[1]
# 获得连接
def GetConnect():
    conn = False
    try:
        conn = psycopg2.connect(database="partner_test",
                                    user="bianxinhua_user",
                                    password="Bianxinhua_user#123",
                                    host="101.37.68.23",
                                    port="15432")
    except Exception as err:
        print("连接数据库失败，%s" % err)
    return conn
#获取超级管理员id
def get_partner_id_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select  id  from \"{}\" where account = '13459147023' '''.format(tbl)
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#获取超级管理员账号
def get_partner_account_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select  account  from \"{}\" where account = '13459147023' '''.format(tbl)
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#获取超级管理员密码
def get_partner_password_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select "password" from \"{}\" where account = '13459147023' '''.format(tbl)
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#累计实发订单
def get_cumulativeQuantity_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select  COUNT(id) from \"{}\" where partner_id = {} and order_status in (2,3)  '''.format(tbl,get_partner_id_from_database('partner_info'))
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#累计实发订单金额
def get_cumulativeMoney_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select  sum(order_actual_amount) from \"{}\" where partner_id = {} and order_status in (2,3)  '''.format(tbl,get_partner_id_from_database('partner_info'))
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return int(result[0])
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#今日新增实发订单金额
def get_todayRealMoney_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select  COALESCE(sum(order_actual_amount),0) from \"{}\" where create_at between \'{}\' and \'{}\'  and order_status in (2,3)  '''.format(tbl,get_today(),get_tomorrow())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return int(result[0])
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#今日新增实发订单
def get_todayRealQuantity_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select COUNT(id) from \"{}\" where create_at between \'{}\' and \'{}\'  and order_status in (2,3)  '''.format(tbl,get_today(),get_tomorrow())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return int(result[0])
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#今日新增意向订单
def get_todayIntentionQuantity_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select COUNT(id) from \"{}\" where   create_at between \'{}\' and \'{}\'  and order_status in (1)  '''.format(tbl,get_today(),get_tomorrow())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return int(result[0])
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#今日新增意向订单金额
def get_todayIntentionMoney_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select  COALESCE(sum(order_actual_amount),0) from \"{}\" where create_at between \'{}\' and \'{}\'  and order_status in (1)  '''.format(tbl,get_today(),get_tomorrow())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return int(result[0])
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#订单数量
def get_orderQuantity_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select COUNT(id) from \"{}\" where partner_id = {} and create_at between \'{}\' and \'{}\'  and order_type = 1 and  order_status in (2,3)  '''.format(tbl,get_partner_id_from_database('partner_info'),get_starTimestamp(),get_endTimestamp())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return int(result[0])
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#累计实发订单
def get_cumulativeQuantity_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select COUNT(id) from \"{}\" where partner_id = {} and create_at between \'{}\' and \'{}\'  and  order_type = 1 and order_status in (2,3)  '''.format(tbl,get_partner_id_from_database('partner_info'),get_starTimestamp(),get_endTimestamp())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return int(result[0])
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#部分发货的订单数量
def get_partShipmentQuantity_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select COUNT(id) from \"{}\" where partner_id = {} and create_at between \'{}\' and \'{}\'  and  order_type = 1 and order_status in (2)  '''.format(tbl,get_partner_id_from_database('partner_info'),get_starTimestamp(),get_endTimestamp())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return int(result[0])
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#全部发货的订单数量
def get_allShipmentQuantity_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select COUNT(id) from \"{}\" where partner_id = {} and create_at between \'{}\' and \'{}\'  and  order_type = 1 and order_status in (3)  '''.format(tbl,get_partner_id_from_database('partner_info'),get_starTimestamp(),get_endTimestamp())
    print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return int(result[0])
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
# print(get_allShipmentQuantity_from_database('order'))
#累计意向订单
def get_intentionQuantity_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select COUNT(id) from \"{}\" where partner_id = {} and create_at between \'{}\' and \'{}\'  and  order_type = 2  '''.format(tbl,get_partner_id_from_database('partner_info'),get_starTimestamp(),get_endTimestamp())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return int(result[0])
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#累计所有年份未收分红
def get_outstanding_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select COALESCE(sum(((order_actual_amount - order_payment_amount ) * partner_bonus_percent)/100),0) as outstanding from \"{}\" where partner_id = {} and create_at between \'{}\' and \'{}\'   '''.format(tbl,get_partner_id_from_database('partner_info'),get_starTimestamp(),get_endTimestamp())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return round(result[0],2)
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#累计所有年份已收分红
def get_received_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select COALESCE(sum((order_payment_amount * partner_bonus_percent)/100),0) as received from \"{}\" where partner_id = {} and create_at between \'{}\' and \'{}\'   '''.format(tbl,get_partner_id_from_database('partner_info'),get_starTimestamp(),get_endTimestamp())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return round(result[0],2)
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
print(get_received_from_database('order'))