serverTest

from _thread import *
import socket
import sys
import time
import sqlite3
from sqlite3 import *
import datetime
import json
database = 'database.db'


PORT=8080
n_client=2
c_client=0
connSet=set()

def addAcc(username: str, password: str, Type: int):
    try:
        connection = connect(database)
        cursor = connection.cursor()
        if Type==0:
            cursor.execute('''INSERT INTO account(user, pass) VALUES (?, ?)''',(username, password))
        else:
            cursor.execute('''INSERT INTO admin(user, pass) VALUES (?, ?)''',(username, password))
        connection.commit()
        cursor.close()
        connection.close()
        return (b'1')
    except:
        return (b'0')
def addDate(ctID,date,tmp1,tmp2,sID):
    connection = connect(database)
    cursor = connection.cursor()
    try:
        cursor.execute('INSERT INTO info VALUES (?, ?, ?, ?, ?)',(ctID,date,tmp1,tmp2,sID))
    except:
        cursor.execute('UPDATE info SET tempFrom=?,tempTo=?,statusID=? WHERE id=? AND date=?',(tmp1,tmp2,sID,ctID,date))
    finally:
        connection.commit()
        cursor.close()
        connection.close()

def addCity(ID,name):
    try:
        connection = connect(database)
        cursor = connection.cursor()
        cursor.execute('INSERT INTO city VALUES (?, ?)',(ID,name))
        connection.commit()
        cursor.close()
        connection.close()
        return b'1'
    except:
        return b'0'
def Login(username: str,password: str, Type:int):
    connection = connect(database)
    cursor = connection.cursor()
    if Type==0:
        cursor1=cursor.execute('SELECT * FROM account WHERE user = ? AND pass = ?',(username,password)).fetchall()
    else :
        cursor1=cursor.execute('SELECT * FROM admin WHERE user = ? AND pass = ?',(username,password)).fetchall()
    cursor.close()
    connection.close()
    if not cursor1:
        return b'0'
    else:
        return b'1'
def getAll(date):
    connection = connect(database)
    cursor = connection.cursor()
    cursor1=cursor.execute('''SELECT * FROM info JOIN city ON info.id=city.id WHERE date = ?  ''',(date,)).fetchall()
    cursor.close()
    connection.close()
    return cursor1
def getDate(ID,date):
    connection = connect(database)
    cursor = connection.cursor()
    cursor1=cursor.execute('''SELECT * FROM info,city WHERE info.id = ? AND date=? AND info.id=city.id ''',(ID,date)).fetchall()
    cursor.close()
    connection.close()
    return cursor1
def endAll():
    global c_client
    for conn in connSet:
        connSet.discard(conn)
        conn.close()
        c_client=c_client-1


def clientthread(conn,addr):
    while True:
        data=""
        try:
            data=conn.recv(1024)
        except:
            print (addr[0]+':'+str(addr[1])+' ngat ket noi')
            break
        if not data:
            print (addr[0]+':'+str(addr[1])+' ngat ket noi')
            break
        data=data.decode()
        print(data)

        x=data.split(" ")
        if x[0]=="acc":
            if x[3]=="0": #user
                conn.sendall(Login(x[1],x[2],0))
            else:         #admin
                conn.sendall(Login(x[1],x[2],1))
        if x[0]=="re":
            conn.sendall(addAcc(x[1],x[2],0))
        if x[0]=="get":
            if x[1]=="all":
                if x[2]=="now":
                    date=datetime.datetime.now()
                    x[2]=str(date.day)+"/"+str(date.month)+"/"+str(date.year)
                    x[2]="20/5/2021"
                jsend={"all" :[]}
                data=getAll(x[2])
                for line in data:
                    tmpInfo={}
                    tmpInfo["id"]=line[0]
                    tmpInfo["name"]=line[6]
                    tmpInfo["date"]=line[1]
                    tmpInfo["tmpF"]=line[2]
                    tmpInfo["tmpT"]=line[3]
                    tmpInfo["statusID"]=line[4]
                    jsend["all"].append(tmpInfo)
                jsended=json.dumps(jsend)
                conn.sendall(jsended.encode())
            else:
                jsend={"7days":[]}
                for i in range(7):
                    date=datetime.datetime.today()+datetime.timedelta(days=i)
                    toDay=str(date.day)+"/"+str(date.month)+"/"+str(date.year)
                    toDay='15/5/2021'
                    data=getDate('82',toDay)
                    tmpInfo={}
                    tmpInfo["id"]=data[0][0]
                    tmpInfo["name"]=data[0][6]
                    tmpInfo["date"]=data[0][1]
                    tmpInfo["tmpF"]=data[0][2]
                    tmpInfo["tmpT"]=data[0][3]
                    tmpInfo["statusID"]=data[0][4]
                    jsend["7days"].append(tmpInfo)
                jsended=json.dumps(jsend)
                conn.sendall(jsended.encode())
        if x[0]=="end": #client end
            conn.sendall(b'ok end')
            break
        if x[0]=="admin":
            if x[1]=="addCity":
                tmpx=data.split(" ",3)
                conn.sendall(addCity(x[2],tmpx[3]))
            if x[1]=="upDate":
                addDate(x[2],x[3],x[4],x[5],x[6])
                conn.sendall(b'1')
            if x[1]=="upCity":
                s7days=data.partition("*")[2]
                s7days=s7days.split("\n")
                s7days.pop()
                for line in s7days:
                    print (line)
                    line=line.split(" ")
                    addDate(x[2],line[0],line[1],line[2],line[3])
                conn.sendall(b'1')
    global c_client
    global connSet
    c_client=c_client-1
    connSet.discard(conn)
    conn.close()
def main():
    try:
        hostname=socket.gethostname()
        local_ip = socket.gethostbyname(hostname)
        HOST=str(local_ip)
        print(HOST)

        s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        s.setsockopt(socket.SOL_SOCKET,socket.SO_REUSEADDR,1)
        s.bind((HOST, PORT))
        print ("Listening on %s %d" %(HOST, (PORT)))
        s.listen(2)

        global connSet
        global c_client

        while True:
            if c_client==n_client:
                continue
            conn, addr = s.accept()
            print ('Connected by ' + addr[0] + ':' + str(addr[1]))
            c_client=c_client+1
            connSet.add(conn)
            start_new_thread(clientthread,(conn,addr))
        s.close()
    except KeyboardInterrupt as msg:
        sys.exit(0)

if __name__=="__main__":
    main()