#! /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 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",
                                    password="bianxinhua_123",
                                    host="114.55.200.59",
                                    port="31543")
    except Exception as err:
        print("连接数据库失败，%s" % err)
    return conn
#获取合伙人id
def get_partner_id_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select  id  from partner_info  where account = '13459147023' '''
    # 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_name_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select  partner_name  from partner_info  where account = '13459147023' '''
    # 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():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select  account  from partner_info where account = '13459147023' '''
    # 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():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select  "password"  from partner_info where account = \'{}\''''.format(get_partner_account_from_database())
    # 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():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select  COUNT(id) from order_base where partner_id = {} and order_type in (1)  '''.format(get_partner_id_from_database())
    # 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():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select  sum(plan_order_amount) from order_base where partner_id = {} and order_type in (1)  '''.format(get_partner_id_from_database())
    print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        if round(result[0], 2) == 0:
            return 0
        else:
            return round(result[0], 2)
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#今日新增实发订单金额
def get_todayRealMoney_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select  COALESCE(sum(plan_order_amount),0) from order_base where create_time between \'{}\' and \'{}\'  and order_type in (1)  '''.format(get_today(),get_tomorrow())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        if round(result[0], 2) == 0:
            return 0
        else:
            return round(result[0], 2)
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#今日新增实发订单
def get_todayRealQuantity_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select COUNT(id) from order_base where create_time between \'{}\' and \'{}\'  and order_type in (1)  '''.format(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_receivable_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select sum(COALESCE(partner_bonus_has,0)+COALESCE(partner_bonus_not,0)-COALESCE(partner_bonus_expense,0)) from order_base where create_time between \'{}\' and \'{}\' and partner_id = {} and order_type in (1)  '''.format('2020-04-1','2021-03-31 23:59:59',get_partner_id_from_database())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        if round(result[0], 2) == 0:
            return 0
        else:
            return round(result[0], 2)
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#实发订单总数
def get_total_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select count(id) from order_base where create_time between \'{}\' and \'{}\' and partner_id = {} and order_type in (1)  '''.format('2020-04-1','2021-03-31 23:59:59',get_partner_id_from_database())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#累计所有年份未收分红
def get_outstanding_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select sum(partner_bonus_not) from order_base where partner_id = {} and order_type=1 and create_time between \'{}\' and \'{}\'   '''.format(get_partner_id_from_database(),'2020-04-01 00:00:00','2021-03-31 23:59:59')
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        if round(result[0], 2) == 0:
            return 0
        else:
            return round(result[0],2)
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
# print(get_outstanding_from_database())
 #累计所有年份已收分红
def get_received_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select  sum(partner_bonus_has) from order_base where partner_id = {}  and order_type=1 and create_time between \'{}\' and \'{}\'   '''.format(get_partner_id_from_database(),'2020-04-01 00:00:00','2021-03-31 23:59:59')
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        if round(result[0],2) == 0:
            return 0
        else:
            return round(result[0],2)
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#已收明细列表--发货单号
def get_detailAction_0_order_code_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select delivery_code  from order_base where partner_id = {}  and order_type=1 and partner_bonus_has > 0 order by id desc '''.format(get_partner_id_from_database())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#未收明细列表--发货单号
def get_detailAction_1_order_code_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select delivery_code from order_base where partner_id = {}  and order_type=1 and partner_bonus_not > 0 order by id desc '''.format(get_partner_id_from_database())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#已收/未收明细列表--订单金额
def get_detailAction_0_plan_order_amount_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select plan_order_amount  from order_base where partner_id = {}  and order_type=1 and partner_bonus_has > 0 order by id desc '''.format(get_partner_id_from_database())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#已收/未收明细列表--应收分红
def get_detailAction_0_use_partner_bonus_amount_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select use_partner_bonus  from order_base where partner_id = {}  and order_type=1 and partner_bonus_has > 0 order by id desc '''.format(get_partner_id_from_database())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        if result[0] == -1.0:
            sql = '''select partner_bonus_has  from order_base where partner_id = {}  and order_type=1 and partner_bonus_has > 0 order by id desc '''.format(
                get_partner_id_from_database())
            cursor.execute(sql)
            result = cursor.fetchone()
            return result[0]
        else:
            return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#获取订单id
def get_order_id_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select id from order_base where partner_id = {}  and order_type=1  order by id desc '''.format(get_partner_id_from_database())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()