zayavka-samba-ad/zayavka_reader.py

#!/usr/bin/python3
# coding=utf-8
import csv
import sys

import MySQLdb

dbconn = {'host': '10.3.11.226',
          'user': 'readonlyuser',
          'pass': 'Readonly@2006',
          'base': 'techgrow'}

sql = 'select * from LDAP'

ad_groups_users = ['vpn-users', 'gogs']
ad_groups_bitrix = ['vpn-web-vpn', 'gogs']
ad_groups_1c = ['vpn-1c-vpn', 'Domain Users']
ad_group_xserv = 'ics'
ad_group_rdp = 'Пользователи удаленного рабочего стола'


def mysql_reader(dbconn, sql):
    data = tuple
    err = False
    try:
        conn = MySQLdb.connect(host=dbconn['host'], user=dbconn['user'],
                               passwd=dbconn['pass'], db=dbconn['base'], charset="utf8")
        cur = conn.cursor(MySQLdb.cursors.DictCursor)
        cur.execute(sql)
        data = cur.fetchall()
        cur.close()
    except MySQLdb.Error as exc:
        print("Connection error: {}".format(err))
        conn.close()
        err = exc
    else:
        conn.commit()
        conn.close()
    return data, err


def users_make_zayavka(users):
    res = {}
    for i in users:
        usrname = str(i['LoginEmail']).split('@')[0]
        if usrname in res:
            continue
        enabled = False
        pwchange = False
        toremove = False
        res[usrname] = {}
        # преобразуем флаги в удобную форму
        if i['Activ'] == 'Y':
            enabled = True
        if i['Changes'] == 'Y':
            pwchange = True
        if i['Delete'] == 'Y':
            toremove = True
        # размечаем группы, в которых должен быть пользователь
        memberof = []
        if str(i['Otdel']) == 'Users':
            memberof = ad_groups_users[:]
        elif str(i['Otdel']) == '1C':
            memberof = ad_groups_1c[:]
        elif str(i['Otdel']) == 'Bitrix':
            memberof = ad_groups_bitrix[:]
        if str(i['XServ']) == '1':
            memberof.append(ad_group_xserv)
        if str(i['RDP']) == '1':
            memberof.append(ad_group_rdp)
        # собственно добавляем в мапу
        res[usrname] = {'id': str(i['ID']),
                        'samAccountName': usrname,
                        'memberOf': memberof,
                        'usrPass': str(i['Pass']),
                        'groupSet': str(i['Otdel']),
                        'enabled': enabled,
                        'pwChange': pwchange,
                        'toRemove': toremove,
                        'email': i['LoginEmail']
                        }

    return res

def mailboxes(data):
    mboxes = []
    for user in data:
        domain = user['LoginEmail'].split('@')[1]
        name = user['LoginEmail'].split('@')[0]
        mbox = ["Администратор", domain, name, user['Pass'], user['Changes'], user['Delete']]
        mboxes.append(mbox)
    return mboxes

def main():
    data, err = mysql_reader(dbconn, sql)
    if len(sys.argv) == 1:
        if not err:
            users = users_make_zayavka(data)
            x = ''
            for user in users:
                print(user, users[user])
                input(x)
        else:
            print(err)
    else:
        csvFile = sys.argv[1];
        with open(csvFile, 'w') as f:
            write = csv.writer(f, quoting=csv.QUOTE_ALL)
            write.writerows(mailboxes(data))

if __name__ == '__main__':
    main()