# create department records based on department names imported from Active Directory

import pyodbc 
conn = pyodbc.connect(
    'Driver={SQL Server};'
    'Server=localhost\\sqlexpress;'
    'Database=nService4;'
    'Trusted_Connection=yes;'
)

cursor = conn.cursor()
users = cursor.execute('SELECT * FROM dbo.ns4_user ORDER BY usr_id').fetchall()

depts = cursor.execute('SELECT * FROM dbo.ns4_department ORDER BY dept_id DESC').fetchall()
newDeptId = 1000
if len(depts) > 0:
    newDeptId = depts[0].dept_id + 1

# put auto created department in its range so that we don't need to update ns4_unique_id
if newDeptId < 1000:
    newDeptId = 1000

for user in users:
    if user.usr_org_id and user.usr_dept:
        findDeptSql = (
            'SELECT * FROM dbo.ns4_department WHERE dept_name = \'' + user.usr_dept + '\' '
            'AND dept_org_id = ' + str(user.usr_org_id))
        
        depts = cursor.execute(findDeptSql).fetchall()
        usrDeptId = newDeptId
        if (len(depts) == 0):
            cursor.execute(
                'INSERT INTO dbo.ns4_department(dept_id, dept_org_id, dept_name) VALUES'
                '(' + str(newDeptId) + ', ' + str(user.usr_org_id) + ', \'' + user.usr_dept + '\') '
            )
            conn.commit()
            newDeptId += 1
        else:
            usrDeptId = depts[0].dept_id
            
        cursor.execute(
            'UPDATE dbo.ns4_user SET usr_dept_id = ' + str(usrDeptId) + ' WHERE '
            'usr_id = ' + str(user.usr_id)
        )
        conn.commit()

print('done')