import fdb
#import xlwt
import xlsxwriter


def output(filename, list1, list2):
    # Create an new Excel file and add a worksheet.
    workbook = xlsxwriter.Workbook(filename + '.xlsx')
    worksheet = workbook.add_worksheet()

    # Write some numbers, with row/column notation.
    for i in range(len(list1)):
        decoded_str = list1[i].decode("windows-1252")
        encoded_str = decoded_str.encode('ascii', 'ignore')
        worksheet.write_string(i, 0, encoded_str)
        worksheet.write_string(i, 1, list2[i].decode('utf-8'))

    workbook.close()

def get_users(date_1,date_2):
    mon_con = fdb.connect(dsn="cp_monitoring.gdb",user='SYSDBA',password='masterke')
    cur = mon_con.cursor()
    cur.execute("select username from users_activity where logon_date between '{0}' and '{1}'".format(date_1, date_2))
    res = cur.fetchall()
   #for elem in res:
    #    print elem
    cur.close()
    mon_con.close()
    return res

def get_firms(user_name):
    curr_con = fdb.connect(dsn="curr.gdb",user='SYSDBA',password='masterke')
    cur = curr_con.cursor()
    cur.execute("select firmid from users where id='{0}'".format(user_name))
    res = cur.fetchall()
    #for elem in res:
     #   print elem
    cur.close()
    curr_con.close()
    if len(res):
        s = ''.join(res[0])
        return s
    else:
        return ''

#def get_status(user_idd):
 #   status = ' '
  #  mon_con = fdb.connect(dsn="curr.gdb",user='SYSDBA',password='masterke')
   # cur = mon_con.cursor()
    #cur.execute("select id from firms where user_id <> '{0}'".format(user_idd))
    #res = cur.fetchall()

    #cur.close()
    #mon_con.close()
def get_all_firms():
    curr_con = fdb.connect(dsn="curr.gdb",user='SYSDBA',password='masterke')
    cur = curr_con.cursor()
    cur.execute("select id from firms order by id")
    res = cur.fetchall()
    cur.close()
    curr_con.close()
    return res

def finish(selected_firmid, all_firmid):
    index = 0
    print len(selected_firmid)
    firms = list()
    activities = list()
    for elem in all_firmid:
        s = ''.join(elem)
        firms.append(s)
        if (s in selected_firmid):
            activities.append('active')
            #print ( s + ' active')
        else:
            activities.append('inactive')
            #print ( s + ' inactive')
    output('UserActivities', firms, activities)
        #if (index < len(selected_firmid)):
        #    s1 = selected_firmid[index]#''.join(selected_firmid[index])
        #    s2 = ''.join(elem)
        #    if
            #if(s1 == s2):
            #    index+=1
            #    print '----'
            #    print s1
            #    print s2
            #    print ( s1 + ' active')
            #else:
            #    print ( str(elem) + ' inactive')


if __name__ == '__main__':
    user_id = get_users('12.04.2018','12.04.2018')
    selected_firmid = list()
    for elem in user_id:
        firm_id = get_firms(elem[0])
        if firm_id != '':
            selected_firmid.append(firm_id)
    selected_firmid.sort()
    selected_firmid = list(set(selected_firmid))
    all_1=get_all_firms()
    all_1.sort()
    finish(selected_firmid, all_1)