#! /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_user",
                                    password="Bianxinhua_user#123",
                                    host="101.37.68.23",
                                    port="15432")
    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()

def get_id_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = 'select  id  from \"{}\" order by id DESC'.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_actual_id_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = 'select  id  from \"{}\"  where order_type = 1 order by id DESC'.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_purpose_id_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = 'select  id  from \"{}\"  where order_type = 2 order by id DESC'.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_account_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql= "select account from " + tbl  + ' ' + '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_partnername_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql= "select partner_name from " + tbl  + ' ' + '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_area_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql= "select region_info from " + tbl  + ' ' + 'order by id desc '
    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(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql= "select salesman from " + tbl  + ' ' + 'order by id desc '
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0][0]['name']
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

def get_phone_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql= "select salesman from " + tbl  + ' ' + 'order by id desc '
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0][0]['telephone']
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

def get_status_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql= "select status from " + tbl  + ' ' + '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_partnername_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql= "select partner_name from " + tbl  + ' ' + "where partner_category = 1" + ' ' + '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_order_status_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql = 'select  order_status  from \"{}\" where order_type = 1 order by id DESC'.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_purpose_order_status_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql = 'select  order_status  from \"{}\" where order_type = 2 order by id DESC'.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_actual_address_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql = 'select  buyer  from \"{}\" where order_type = 1 order by id DESC'.format(tbl)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]['shippingAddress']
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

def get_purpose_address_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql = 'select  buyer  from \"{}\" where order_type = 2 order by id DESC'.format(tbl)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]['shippingAddress']
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

def get_actual_buyer_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql = 'select  buyer  from \"{}\" where order_type = 1 order by id DESC'.format(tbl)
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]['buyerName']
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

def get_purpose_buyer_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql = 'select  buyer  from \"{}\" where order_type = 2 order by id DESC'.format(tbl)
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]['buyerName']
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

def get_actual_buyerPhone_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql = 'select  buyer  from \"{}\" where order_type = 1 order by id DESC'.format(tbl)
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]['contactInfo']
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

def get_purpose_buyerPhone_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql = 'select  buyer  from \"{}\" where order_type = 2 order by id DESC'.format(tbl)
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]['contactInfo']
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

def get_actual_orderDist_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql = 'select  order_region_info  from \"{}\" where order_type = 1 order by id DESC'.format(tbl)
    # 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_purpose_orderDist_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql = 'select  order_region_info  from \"{}\" where order_type = 2 order by id DESC'.format(tbl)
    # 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_actual_orderId_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql = 'select  order_code  from \"{}\" where order_type = 1  order by id DESC'.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_purpose_orderId_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql = 'select  order_code  from \"{}\" where order_type = 2  order by id DESC'.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_actual_orderName_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql = 'select  order_name  from \"{}\" where order_type = 1 order by id DESC'.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_purpose_orderName_from_database(tbl):
    db = GetConnect()
    cursor = db.cursor()
    sql = 'select  order_name  from \"{}\" where order_type = 2 order by id DESC'.format(tbl)
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

# print(get_purpose_order_status_from_database('order'))