import pymysql
import datetime


class Accounting:
    def __init__(self, id, type, account, money, time2, des):
        self.__id = id
        # ID
        self.__type = type
        # 类型
        self.__account = account
        # 账户
        self.__money = money
        # 金额
        self.__time2 = time2
        # 时间
        self.__des = des
        # 说明

    def setType(self, type):
        self.__type = type

    def getType(self):
        return self.__type

    def setAccount(self, account):
        self.__account = account

    def getAccount(self):
        return self.__account

    def getMoney(self):
        return self.__money

    def setMoney(self, money):
        self.__money = money

    def setDes(self, des):
        self.__des = des

    def getDes(self):
        return self.__des

    def __str__(self):
        return '{:<12}{:<12}{:<12}{:<12}{:<12}{:<12}'.format(self.__id, self.__type, self.__account, self.__money,
                                                             self.__time2, self.__des)


'''
create table Accounting
(
	id int primary key auto_increment,
	type varchar(10) not null,
	account varchar(10) not null,
	money int not null,
	time2 timestamp default current_timestamp,
	des varchar(100)
)engine=innodb default charset=utf8; 

'''


class Mysql:

    def __init__(self):
        self.__connect = pymysql.connect(host='localhost', user='root', passwd='sxx123', database='test4', port=3306)

        self.__cursor = self.__connect.cursor()

        self.__sql1 = 'select * from Accounting'

    def findAll(self):

        self.__cursor.execute(self.__sql1)

        arr = self.__cursor.fetchall()


        self.print2(arr)


    def print2(self,arr):
        if len(arr) > 0:
            print('{:<12}{:<12}{:<12}{:<12}{:<12}{:<12}'.format('ID', '类别', '账户', '金额', '时间', '说明'))
            for e in arr:
                obj = Accounting(e[0], e[1], e[2], e[3], e[4].strftime("%Y-%m-%d"), e[5])
                print(obj)
        else:
            print('没有记账数据！')


    def findBy(self):
        print('输入要查询的属性编号(多个用英文逗号隔开)!')
        print('1. ID')
        print('2. 类别')
        print('3. 账户')
        print('4. 金额')
        print('5. 时间')
        print('6. 说明')

        _str = input('输入选项:')
        _arr = _str.split(',')
        _sqlArr = []
        # print(_arr)
        if '1' in _arr:
            ID = input('请输入ID：')
            _sqlArr.append(f'id = {ID}')

        if '2' in _arr:
            type = input('请输入类别：')
            _sqlArr.append(f'type = "{type}"')

        if '3' in _arr:
            account = input('请输入账户：')
            _sqlArr.append(f'account = "{account}"')

        if '4' in _arr:
            a = input('请输入选项：1. 指定金额 2. 金额范围')
            if a.strip() == '1':
                money = input('请输入金额：')
                _sqlArr.append(f'money = {money}')
            elif a.strip() == '2':
                moneyStart = input('请输入起始金额(包含):')
                moneyEnd = input('请输入终止金额(包含):')
                _sqlArr.append(f'{moneyStart} <= money and money <= {moneyEnd}')

        if '5' in _arr:
            a = input('请输入选项：1. 指定日期 2. 日期范围')
            if a.strip() == '1':
                time2 = input('请输入日期(如：2023-10-01)：')
                _sqlArr.append(f"date_format(time2,'%Y-%m-%d') = '{time2}'")
            elif a.strip() == '2':
                timeStart = input('请输入起始日期(包含):')
                timeEnd = input('请输入终止日期(包含):')
                _sqlArr.append(f'''
                    date_format(time2,'%Y-%m-%d') >= '{timeStart}'
                    and date_format(time2,'%Y-%m-%d') <= '{timeEnd}'
                ''')

        if '6' in _arr:
            des = input('输入：')
            _sqlArr.append(f'des = {des}')

        if len(_sqlArr) > 1:
            _sqlStr = ' and '.join(_sqlArr)
            sql = self.__sql1 + f'''
            where {_sqlStr}
            '''
        elif len(_sqlArr) == 1:
            sql = self.__sql1 + f' where {_sqlArr[0]}'

        else:
            sql = self.__sql1

        print(sql)
        self.__cursor.execute(sql)

        result = self.__cursor.fetchall()

        self.print2(result)

    def addAccount(self):
        type = input('输入类别:')
        account = input('输入支付方式:')
        money = input('输入金额：')
        des = input('说明：')
        sql = f'insert into Accounting values(null,"{type}","{account}",{money},null,"{des}")'
        print(sql)
        row = self.__cursor.execute(sql)

        print(row)
        self.__connect.commit()

    def editAccount(self):
        Id = input('输入ID：')
        type = input('输入类别(不需要修改直接enter):')
        account = input('输入支付方式(不需要修改直接enter):')
        money = input('输入金额(不需要修改直接enter)：')
        des = input('说明(不需要修改直接enter)：')
        # update Accounting set des = '游戏收入' where id = 4
        _sqlStr2 = ''
        if type.strip() != '':
            _sqlStr2 += f" type = '{type.strip()}'"

        if account.strip() != '':
            if _sqlStr2 != '':
                _sqlStr2 += f" , account = '{account.strip()}'"
            else:
                _sqlStr2 += f" account = '{account.strip()}'"

        if money.strip() != '':
            if _sqlStr2 != '':
                _sqlStr2 += f" , money = {money.strip()}"
            else:
                _sqlStr2 += f" money = {money.strip()}"


        if des.strip() != '':
            if _sqlStr2 != '':
                _sqlStr2 += f" , des = '{des.strip()}' "
            else:
                _sqlStr2 += f" des = '{des.strip()}' "

        if _sqlStr2.strip() == '':
            print('没有输入任何修改参数')
        else:
            sql = f'update Accounting set {_sqlStr2} where id = {Id}'
            print(sql)
            row = self.__cursor.execute(sql)

            if row >= 1:
                print('修改成功！')
            self.__connect.commit()

    def deleteAccount(self):
        id = input('输入Id：')

        sql = f'delete from Accounting where ID = {id}'

        row = self.__cursor.execute(sql)

        if row >= 1:
            print('删除成功！')

        self.__cursor.fetchall()

        self.__connect.commit()




if __name__ == '__main__':
    row = 3
    flag = False
    a,b = None,None
    m = Mysql()
    while row > 0:
        a = input('输入账户:')
        b = input('输入密码:')
        if (a == 'admin' and b == '123456') or (a == 'lisi' and b == '123456'):
            flag = True
            break
        else:
            print('输入账户或者密码错误！')
        row -= 1

    if flag:
        if a == 'lisi':
            while True:
                print('1. 查询所有')
                print('2. 按条件查询')
                print('3. 退出')
                c = input('输入：')
                if c.strip() == '1':
                    m.findAll()
                elif c.strip() == '2':
                    m.findBy()
                elif c.strip() == '3':
                    break

        elif a == 'admin':
            while True:
                print('1. 添加账务')
                print('2. 编辑账户')
                print('3. 删除账务')
                print('4. 查询账务')
                print('5. 退出系统')
                c = input('输入:')
                if c.strip() == '1':
                    m.addAccount()
                elif c.strip() == '2':
                    m.editAccount()
                elif c.strip() == '3':
                    m.deleteAccount()
                elif c.strip() == '4':
                    m.findBy()
                elif c.strip() == '5':
                    break