#!/usr/bin/env python
# -*- coding: utf-8 -*-

from PyQt5.QtCore import Qt,QDateTime
from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSqlTableModel, QSqlRelationalTableModel, QSqlRelation, QSqlQueryModel

import os
from tables import *

def createConnection():
    db = QSqlDatabase.addDatabase('QSQLITE')
    init=False
    if(not os.path.exists('CarRecs.db')):
        init=True
    db.setDatabaseName('CarRecs.db')
    if not db.open():
        return False
    if(init and not createTables()):
        return False
    return True



def createTables():
    q=QSqlQuery()
    sqls = getTableCrtSqls()
    for sql in sqls:
        if(not q.exec(sql)):
            return False
    sqls.clear()
    sqls.append('insert into FuelClasses(code,name,grades) values(1,\'汽油\',\'3,93#|7,97#|2,92#|5,95#\')')
    sqls.append('insert into FuelClasses(code,name,grades) values(2,\'柴油\',\'1,0#|2,-10#\')')
    sqls.append('insert into FuelClasses(code,name,grades) values(3,\'天然气\',\'\')')
    sqls.append('insert into fuelUpdateHistorys(startDate,description) values(\'2000-01-01\',\'执行国3标准\')')
    sqls.append('insert into fuelUpdateHistorys(startDate,description) values(\'2014-01-01\',\'执行国4标准\')')
    sqls.append('insert into fuelUpdateHistorys(startDate,description) values(\'2016-01-01\',\'执行国5标准\')')
    sqls.append('insert into Fuels(fuelClass,fuelName,gradeLevel,gradeName,updateHistory,isTrace) values(1,\'93#汽油\',3,\'93#\',\'2\',0)')
    sqls.append('insert into Fuels(fuelClass,fuelName,gradeLevel,gradeName,updateHistory,isTrace) values(1,\'97#汽油\',7,\'97#\',\'2\',0)')
    sqls.append('insert into Fuels(fuelClass,fuelName,gradeLevel,gradeName,updateHistory,isTrace) values(1,\'92#汽油\',2,\'92#\',\'3\',1)')
    sqls.append('insert into Fuels(fuelClass,fuelName,gradeLevel,gradeName,updateHistory,isTrace) values(1,\'95#汽油\',5,\'95#\',\'3\',1)')
    sqls.append('insert into Fuels(fuelClass,fuelName,gradeLevel,gradeName,updateHistory,isTrace) values(2,\'0#柴油\',1,\'0#\',\'2,3\',1)')
    sqls.append('insert into Fuels(fuelClass,fuelName,gradeLevel,gradeName,updateHistory,isTrace) values(2,\'-10#柴油\',2,\'-10#\',\'2,3\',0)')
    sqls.append('insert into Fuels(fuelClass,fuelName,gradeLevel,gradeName,updateHistory,isTrace) values(3,\'天然气\',1,\'\',\'\',0)')
    sqls.append('insert into jobClasses(jcName) values(\'常规保养\')')
    sqls.append('insert into jobClasses(jcName) values(\'故障修复\')')
    sqls.append('insert into jobClasses(jcName) values(\'事故修复\')')
    sqls.append('insert into repairer(name) values(\'自己搞定\')')
    sqls.append('insert into repairer(name) values(\'4S店\')')


    for sql in sqls:
        if(not q.exec(sql)):
            return False
    return True

def getJobClsModel():
    model = QSqlTableModel()
    model.setEditStrategy(QSqlTableModel.OnManualSubmit)
    model.setTable(TN_JC)
    model.select()
    return model

def getRepairerModel():
    model = QSqlTableModel()
    model.setEditStrategy(QSqlTableModel.OnManualSubmit)
    model.setTable(TN_REPAIRER)
    model.select()
    return model

def getMaintenceModel():
    model = QSqlTableModel()
    model.setEditStrategy(QSqlTableModel.OnManualSubmit)
    model.setTable(TN_MR)
    model.setSort(1,Qt.DescendingOrder)
    model.select()
    return model

def getToFuelRecModel():
    #model = QSqlRelationalTableModel()
    model = MyRelationalModel(2)
    model.setTable(TN_TFR)
    model.setRelation(TC_TF_VEHICLE, QSqlRelation(TN_VINFO, "_id", FN_VI_LICENSE));
    model.setRelation(TC_TF_FUEL, QSqlRelation(TN_FUELS, "_id", FN_F_NAME));
    model.setRelation(TC_TF_STATION, QSqlRelation(TN_STATION, "_id", FN_S_NAME))
    model.setHeaderData(TC_TF_VEHICLE,Qt.Horizontal,"车牌号")
    model.setHeaderData(TC_TF_DATE,Qt.Horizontal,"日期")
    model.setHeaderData(TC_TF_FUEL,Qt.Horizontal,"燃料")
    model.setHeaderData(TC_TF_MILEAGE,Qt.Horizontal,"里程表")
    model.setHeaderData(TC_TF_FUELDIAL,Qt.Horizontal,"油表读数")
    model.setHeaderData(TC_TF_MONEY,Qt.Horizontal,"金额")
    model.setHeaderData(TC_TF_AMOUNT,Qt.Horizontal,"油量")
    model.setHeaderData(TC_TF_PRICE,Qt.Horizontal,"单价")
    model.setHeaderData(TC_TF_STATION,Qt.Horizontal,"加油站")
    model.setEditStrategy(QSqlTableModel.OnManualSubmit)
    return model

def getVehicleModel():
    model = QSqlTableModel()
    model.setTable(TN_VINFO)
    model.setEditStrategy(QSqlTableModel.OnManualSubmit)
    model.select()
    return model

def getYearScope(vId):
    q = QSqlQuery()
    sql = "select max(%s),min(%s) from %s where %s=%d" %(FN_TF_DATE,FN_TF_DATE,TN_TFR,FN_TF_VEHICLE,vId)
    sy=ey=0
    if((not q.exec(sql)) or not q.next()):
        return (sy,ey)
    if(q.value(1)!=''):
        sy=int(q.value(1))
    if(q.value(0)!=''):
        ey = int(q.value(0))
    return (sy,ey)

def getFuelModel():
    m = QSqlQueryModel()
    sql = "select _id,%s from %s order by %s,%s" %(FN_F_NAME,TN_FUELS,FN_F_FC,FN_F_GL)
    m.setQuery(sql)
    return m

def getFuelClassModel():
    m = QSqlTableModel()
    m.setTable(TN_FC)
    m.setEditStrategy(QSqlTableModel.OnManualSubmit)
    m.select()
    return m

def getAdjustPriceDates():
    '''返回调价日期列表'''
    dates = list()
    q = QSqlQuery()
    if(q.exec("select DISTINCT %s from %s order by %s DESC" %(FN_FP_DATE,TN_FP,FN_FP_DATE))):
        while(q.next()):
            dates.append(q.value(0))
    return dates

def getPriceForDate(d,closeTo=False):
    '''
        返回指定日期各种燃料的单价的字典（键为燃料id，值为单价）
        参数：
            d：整数表示的日期
            closeTo：是否查询与该日期最接近的价格
    '''
    q = QSqlQuery()
    result = dict()
    date = d
    if(closeTo):
        if(not q.exec("select max(%s) from %s" %(FN_FP_DATE,TN_FP))):
            return result
        date = 0
        if(q.first()):
            date = q.value(0)
        if(d != 0 and d < date): #如果数据库中保存的价格晚于请求的日期，则按顺序查找最接近的日期
            if(not q.exec("select DISTINCT %s from %s order by %s DESC" %(FN_FP_DATE,TN_FP,FN_FP_DATE))):
                return result
            while(q.next()):
                pd = q.value(0)
                if(d >= pd):
                    date = pd
                    break

    sql = "select %s,%s from %s where %s=%d" %(FN_FP_FUEL,FN_FP_PRICE,TN_FP,FN_FP_DATE,date)
    if(not q.exec(sql)):
        return result
    while(q.next()):
        fc = q.value(0)
        price = q.value(1)
        result[fc] = price


    return result

def getFuelForVehicle(vId):
    q = QSqlQuery()
    if(not q.exec("select %s from %s where _id=%d" %(FN_VI_FUEL,TN_VINFO,vId)) or not q.first()):
        return False
    return q.value(0)

def getFuelId(fCode,grade,gname):
    '''返回指定燃料的ID，如果未找到则返回0
        参数 fcode：燃料种类代码
        参数 grade：燃料级别
        参数 gname：级别名
    '''
    q = QSqlQuery()
    if(grade==0 and not gname):
        sql = 'select _id from %s where %s=%d ' %(TN_FUELS,FN_F_FC,fCode)
    else:
        sql = 'select _id from %s where %s=%d and %s=%d and %s=\'%s\' ' %(TN_FUELS,FN_F_FC,fCode,FN_F_GL,grade,FN_F_GNAME,gname)
    if(not q.exec(sql) or not q.next()):
        return 0
    return q.value(0)

def isTracePriceForFuel(fuelId):
    '''返回指定燃料是否需要跟踪价格'''
    q = QSqlQuery()
    if(not q.exec("select %s from %s where _id=%d" %(FN_F_ISTRACE,TN_FUELS,fuelId)) or not q.first()):
        return False
    t=q.value(0)
    if(t == 1):
        return True
    else:
        return False

def updateFuels(fc,fuelStr):
    '''
        更新燃料表
        参数
            fc  燃料种类id
            fuelStr是一个 ‘|’分隔的列表，每个列表又通过逗号分隔，元素依次是燃料ID，燃料类别名,燃料级别,燃料级别名，是否跟踪价格
    '''
    q = QSqlQuery()
    if(not fuelStr):
        pass #应该删除该类的所有燃料

    #首先要获取目前在表中保存的所有属于该类别的燃料
    if(not q.exec("select _id from %s where %s=%d" %(TN_FUELS,FN_F_FC,fc))):
        return False
    ids = list()
    while(q.next()):
        ids.append(q.value(0))
    fuels = fuelStr.split('|')
    for fuel in fuels:
        fl=fuel.split(',')
        isTrace=0
        if(fl[4]=='True'):
            isTrace=1
        if(fuel[0]=='0'):
            sql = 'insert into %s(%s ,%s ,%s ,%s,%s) values(%d,\'%s\',%s,\'%s\',%d)' \
                  %(TN_FUELS,FN_F_FC,FN_F_NAME,FN_F_GL,FN_F_GNAME,FN_F_ISTRACE,fc,fl[3]+fl[1],fl[2],fl[3],isTrace)
            if(not q.exec(sql)):
                return False
        else:
            sql = 'update %s set %s=\'%s\',%s=%s,%s=\'%s\',%s=%d where _id=%s' \
                  %(TN_FUELS,FN_F_NAME,fl[3]+fl[1],FN_F_GL,fl[2],FN_F_GNAME,fl[3],FN_F_ISTRACE,isTrace,fl[0])
            if(not q.exec(sql)):
                return False
            ids.remove(int(fl[0]))
    if(ids):
        for id in ids:
            sql = "delete from %s where _id=%d" %(TN_FUELS,id)
            if(not q.exec(sql)):
                return False
    return True


def updatePrice(date,prices,delPrices=list()):
    '''
        保存价格信息
        参数 date：整形表示的日期
            prices：单价字典（键为燃料id，值为单价）
    '''
    q = QSqlQuery()
    for (id,price) in prices.items():
        sql = "update %s set %s=%f where %s=%d and %s=%d" %(TN_FP,FN_FP_PRICE,price,FN_FP_FUEL,id,FN_FP_DATE,date)
        if(not q.exec(sql)):
            return False
        if(q.numRowsAffected() == 0):
            sql = "insert into %s(%s,%s,%s) values(%d,%d,%f)" %(TN_FP,FN_FP_FUEL,FN_FP_DATE,FN_FP_PRICE,id,date,price)
            if(not q.exec(sql)):
                return False
    if(delPrices):
        for id in delPrices:
            sql = "delete from %s where %s=%d and %s=%d" %(TN_FP,FN_FP_DATE,FN_FP_FUEL,date,id)
            if(not q.exec(sql)):
                return False
    return True

def getCurFuels(date):
    '''返回在指定日期，市场上通常使用的一组燃料'''
    fuels=dict()
    q=QSqlQuery()
    id=0
    if(q.exec("select _id,startDate from fuelUpdateHistorys order by startDate")):
        while(q.next()):
            preId = q.value(0)
            d = q.value(1)
            if(d>date):
                break
            id=preId
    if(q.exec("select _id,fuelName,updateHistory from Fuels")):
        while q.next():
            fid = q.value(0)
            ups = q.value(2)
            if(not ups):
                continue
            upLst = ups.split(',')
            if(str(id) in upLst):
                fuels[fid]=q.value(1)
    return fuels

def getCurTarcePriceFuels():
    fuels=dict()
    q = QSqlQuery()
    if(not q.exec('select _id,%s from %s where %s=1' %(FN_F_NAME,TN_FUELS,FN_F_ISTRACE))):
        return fuels
    while(q.next()):
        fid = q.value(0)
        fuels[fid] = q.value(1)
    return fuels

def getPreferredStation():
    '''返回用户首选的加油站ID'''
    q=QSqlQuery()
    if(not q.exec('select _id,max(%s) from %s' %(FN_S_WEIGHT,TN_STATION)) or not q.first()):
        return 0
    return q.value(0)


def canDelVehicle(vId):
    '''测试指定车辆ID是否在加油记录或维护记录中有引用，如有，则不能删除'''
    q=QSqlQuery()
    if(not q.exec("select _id from %s where %s=%d" %(TN_TFR,FN_TF_VEHICLE,vId)) or q.first()):
        return False
    return True

def removeVehicle(vId):
    '''移除指定车辆信息及其与之相关的加油记录和维护记录'''
    q=QSqlQuery()
    sql = "delete from %s where %s=%d" %(TN_TFR,FN_TF_VEHICLE,vId)
    if(not q.exec(sql)):
        return False
    sql = "delete from %s where _id=%d" %(TN_VINFO,vId)
    return q.exec(sql)

class MyRelationalModel(QSqlRelationalTableModel):
    def __init__(self,column):
        super(MyRelationalModel, self).__init__()
        self.column=column

    def data(self,index,role=Qt.DisplayRole):
        if(index.column()==self.column and role == Qt.DisplayRole):
            ti = QSqlRelationalTableModel.data(self,index,Qt.EditRole)
            ds = QDateTime.fromMSecsSinceEpoch(ti).date().toString(Qt.ISODate)
            return ds
        return QSqlRelationalTableModel.data(self,index,role)

class MySqlQueryModel(QSqlQueryModel):
    '''将指定列的由整数表示的日期转换为人可读的日期格式'''
    def __init__(self,column):
        super(MySqlQueryModel, self).__init__()
        self.column=column