import os
import psycopg2
import time

def copyDB(countCashBox):
    db_host = 'localhost'
    db_name = 'down'
    db_user = 'postgres'
    db_password = '1'
    dsn = "host={} dbname={} user={} password={}".format(db_host, db_name, db_user, db_password)
    conn = psycopg2.connect(dsn)
    cur = conn.cursor()
    delete_product = "delete from product_pos_{}"
    delete_barcode = "delete from barcode_pos_{}"
    delete_price = "delete from price_pos_{}"
    delete_inventorycount = "delete from inventorycount_pos_{}"
    update_product = "copy product_pos_{} from '/home/tester/fm/query/1' using delimiters ','"
    update_barcode = "update barcode_pos_{}  set modified  = 1"
    update_inventorycount = "update inventorycount_pos_{}  set modified  = 1"
    update_price = "update price_pos_{}  set modified  = 1"
    cur.execute("Begin;")
    start_time = time.time()
    for i in range(1, countCashBox + 1):
        cur.execute(delete_product.format(i))
        cur.execute(delete_barcode.format(i))
        cur.execute(delete_price.format(i))
        cur.execute(delete_inventorycount.format(i))
        cur.execute(update_product.format(i))
        cur.execute(update_barcode.format(i))
        cur.execute(update_price.format(i))
        cur.execute(update_inventorycount.format(i))
    cur.execute("COMMIT;")
    print 'Time to update DB '
    print time.time() - start_time
    cur.close()
    conn.close()

def startServer():
    os.system("pkill FmService")
    os.system("/home/tester/fm/FmService 2>/dev/null &")
    print 'FmServer Started'
def stopServer():
    os.system("pkill FmService")

def startFakepos(countCashBox):
    os.system("sshpass -p '1' ssh tester@192.168.63.122 '/home/tester/tools/fakepossd/start_ssd %s'" %countCashBox)

def stopFakepos():
    os.system("sshpass -p '1' ssh tester@192.168.63.122 'pkill fakepossd'")

def countSelles(countCashBox):
    db_host = 'localhost'
    db_name = 'down'
    db_user = 'postgres'
    db_password = '1'
    file = 'result'
    my_file = open(file, 'a')
    dsn = "host={} dbname={} user={} password={}".format(db_host, db_name, db_user, db_password)
    conn = psycopg2.connect(dsn)
    cur = conn.cursor()
    select_product = "select count(*) from product_pos_{} where  modified  = 0"
    select_barcode = "select count(*) from  barcode_pos_{} where  modified  = 0"
    select_inventorycount = "select count(*)from  inventorycount_pos_{} where  modified  = 0"
    select_price = "select count(*) from price_pos_{}  where  modified  = 0"
    cur.execute("Begin;")
    start_time = time.time()
    count_row_product = 0
    count_row_barcode = 0
    count_row_price = 0
    count_row_inventorycount = 0
    for i in range(1, countCashBox + 1):
        cur.execute(select_product.format(i))
        count_row_product +=  int(cur.fetchone()[0])
        cur.execute(select_barcode.format(i))
        count_row_barcode +=  int(cur.fetchone()[0])
        cur.execute(select_price.format(i))
        count_row_price +=  int(cur.fetchone()[0])
        cur.execute(select_inventorycount.format(i))
        count_row_inventorycount +=  int(cur.fetchone()[0])
    cur.execute("COMMIT;")
    print 'Time to count row'
    print time.time() - start_time
    cur.close()
    conn.close()
    my_file.write('loop N {}\n'.format(str(countCashBox)))
    my_file.write('{:.5} Mb \n'.format(str(count_row_product * 1.82 / 1024 + count_row_barcode * 0.24 / 1024 + count_row_price * 0.24 / 1024 + count_row_inventorycount * 0.19 / 1024)))
    my_file.write('{0} products \n{1} barcode \n{2} price \n{3} inventorycount \n'.format(str(count_row_product), str(count_row_barcode), str(count_row_price), str(count_row_inventorycount)))
    my_file.close


cashboxlist = [200, 500, 1000]

stopServer()
stopFakepos()
for element in cashboxlist:
    print ('Cashbox N {}'.format(str(element)))
    copyDB(element)
    startServer()
    startFakepos(element)
    time.sleep(3600)
    stopFakepos()
    stopServer()
    countSelles(element)