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



def get_today():
    today = datetime.now(timezone.utc).astimezone().isoformat()
    return today

# 获得连接
def GetConnect():
    conn = False
    try:
        conn = psycopg2.connect(database="mmmworthtest",
                                    user="bianxinhua",
                                    password="bianxinhua_123",
                                    host="114.55.200.59",
                                    port="31543")
    except Exception as err:
        print("连接数据库失败，%s" % err)
    return conn
#获取员工的姓名
def get_employees_name_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = ''' select employee_name from employees e where e.company_id = 366 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()

#获取员工的uid
def get_uid_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = ''' select uid from employees e where e.company_id = 366 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()
#获取超级管理员账号
def get_employee_account_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select  employee_account  from employees  where company_id = 366 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()

#获取抢单任务单的状态:待发布
def get_task_status_1_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select task_status from tasks  where company_id = 366 and task_type = 1 and task_status = 1 and id = {} '''.format(get_loot_task_id())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#获取抢单任务单的状态:待领取
def get_task_status_2_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select task_status from tasks  where company_id = 366 and task_type = 1 and task_status = 2 and id = {} '''.format(get_loot_task_id())
    print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
#获取抢单任务单的状态:进行中
def get_task_status_3_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select task_status from tasks  where company_id = 366 and task_type = 1 and task_status = 3 and id = {} '''.format(get_loot_task_id())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()
# #获取抢单任务单的状态:待确认
def get_task_status_7_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select task_status from tasks  where company_id = 366 and task_status = 7 and id = {}'''.format(get_loot_task_id())
    print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#获取任务单的状态:待验收
def get_task_status_4_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select task_status from tasks t where t.company_id = 366 and t.task_status = 4 and t.receiver_uid = '3248817613892608'  and id = {}'''.format(get_loot_task_id())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#获取任务单的状态:已完成
def get_task_status_5_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select task_status from tasks t where t.company_id = 366 and t.task_status = 5 and id = {}'''.format(get_loot_task_id())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()

#获取任务单的状态:已关闭
def get_task_status_6_from_database():
    db = GetConnect()
    cursor = db.cursor()
    # sql= "select id from " + '''"''' +  tbl + '''"'''+ ' ' + 'order by id desc '
    sql = '''select task_status from tasks t where t.company_id = 366 and t.task_status = 6 and id = {}'''.format(get_loot_task_id())
    # print(sql)
    try:
        cursor.execute(sql)
        result = cursor.fetchone()
        return result[0]
    except Exception as err:
        # 发生错误时回滚
        print(err)
        db.rollback()
    db.close()