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

# 获得连接
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
#获取超级管理员账号
def get_admin_account_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select  account  from \"{}\" where  is_default ='t' '''.format(tbl)
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#获取管理员的id
def get_admin_id_from_database():
    db = GetConnect()
    cursor = db.cursor()
    sql = '''select  id  from admin_user where is_usable = 't' order by id desc '''
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#获取管理员的account
def get_account_from_database():
    db = GetConnect()
    cursor = db.cursor()
    sql= '''select account from admin_user where is_usable = 't' order by id desc '''
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#实际订单的发货单号
def get_actual_delivery_code_from_database():
    db = GetConnect()
    cursor = db.cursor()
    sql = '''select  delivery_code  from order_base where order_type = 1  order by id DESC'''
    #print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#实际订单的id
def get_actual_id_from_database():
    db = GetConnect()
    cursor = db.cursor()
    sql = '''select  id  from order_base where order_type = 1  order by id DESC'''
    #print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#意向订单id
def get_purpose_id_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select  id  from order_base  where order_type = 2 order by id DESC'''
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#意向订单的产品ID
def get_ID_from_database(id):
    db = GetConnect()
    cursor = db.cursor()
    sql= '''select  id  from order_good where order_id = {} order by id ASC '''.format(get_purpose_id_from_database())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
        return result[id][0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#分红管理的产品ID
def get_dividends_ID_from_database(id):
    db = GetConnect()
    cursor = db.cursor()
    sql= '''select  id  from order_good where order_id = {} order by id ASC '''.format(get_actual_id_from_database())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
        return result[id][0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
print(get_dividends_ID_from_database(0))
#意向订单的产品名称
def get_productName_from_database(id):
    db = GetConnect()
    cursor = db.cursor()
    sql= '''select  good_name  from order_good where order_id = {} order by id ASC '''.format(get_purpose_id_from_database())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
        return result[id][0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#意向订单的产品数量
def get_orderNum_from_database(id):
    db = GetConnect()
    cursor = db.cursor()
    sql= '''select plan_good_number from order_good where order_id = {} order by id ASC '''.format(get_purpose_id_from_database())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
        return result[id][0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#意向订单的产品单价
def get_univalence_from_database(id):
    db = GetConnect()
    cursor = db.cursor()
    sql= '''select price from order_good where order_id = {} order by id ASC '''.format(get_purpose_id_from_database())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
        return result[id][0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#意向订单的合伙人分红比例
def get_partnerRatio_from_database(id):
    db = GetConnect()
    cursor = db.cursor()
    sql= '''select partner_bonus_percent from order_good where order_id = {} order by id ASC '''.format(get_purpose_id_from_database())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
        return result[id][0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#获取合伙人所属区域
def get_area_from_database():
    db = GetConnect()
    cursor = db.cursor()
    sql = '''select region_info from partner_info  where account = '13459147023' '''
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]['regionName']
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#获取合伙人的业务员姓名
def get_salesman_from_database():
    db = GetConnect()
    cursor = db.cursor()
    sql = '''select salesman from partner_info  where account = '13459147023' '''
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0][0]['name']
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#获取合伙人的状态
def get_status_from_database():
    db = GetConnect()
    cursor = db.cursor()
    sql = '''select status 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()
# print(get_status_from_database())