# -*- coding: utf-8 -*-
from callspeewee import *
from queryldap import *
# from optparse import OptionParser
import xlsxwriter, pprint, sys, datetime
selector = calls_sip.call_id_field << calls_sip.select(calls_sip.call_id_field).distinct().where(calls_sip.sender_field % ‘10.4%’).where(calls_sip.event_field == ‘BYE’).where(calls_sip.direction_field=='SIPTR: Received')
if len(sys.argv) > 1:
dates = sys.argv
selector = calls_sip.call_id_field << calls_sip.select(calls_sip.call_id_field).distinct().where(calls_sip.sender_field % ‘10.4%’).where(calls_sip.event_field == ‘BYE’).where(calls_sip.direction_field=='SIPTR: Received').where(calls_sip.event_time > dates).where(calls_sip.event_time < dates)
# Запрашиваем звонки, 
request = (calls_sip.select(
fn.min(calls_sip.time_field).alias('callStart'), 
fn.max(calls_sip.time_field).alias('callEnd'), 
(
fn.timediff(fn.max(calls_sip.time_field), fn.min(calls_sip.time_field))
).alias('callDuration'),
fn.max(calls_sip.x_genesys_calluuid_field).alias('x_genesys_calluuid_field'),
calls_sip.call_id_field
).where(
selector
)
.group_by(calls_sip.call_id_field)
)
calls = {}
for call in request:
calls = call
# Выдергиваем звонки, 
dataRequest = (calls_genesys_data.select(
calls_genesys_data.call_result_ext,
calls_genesys_data.attributethisdn,
calls_genesys_data.attributecalluuid,
calls_genesys_data.attributecallid,
calls_genesys_data.rtargetagentselected,
calls_genesys_data.gsw_phone
))
#.where(calls_genesys_data.skilltype << ))
if dataRequest:
namepart = (datetime.datetime.now()).strftime('%Y-%m-%d')
#	Создаем файл с отчетом
wb = xlsxwriter.Workbook('С:\dev\py\dropReport-' + str(namepart) + ‘.xlsx’)
ws = wb.add_worksheet('Сброшенные звонки'.decode('utf8'))
#	Добавляем форматы ячеек
time_format = wb.add_format({'num_format': ‘hh:mm:ss’})
date_format = wb.add_format({'num_format': ‘yyyy-mm-dd hh:mm:ss’})
#	Задаем заголовки
ws.write(0, 0, ‘Начало звонка’.decode('utf8'))
ws.write(0, 1, ‘Конец звонка’.decode('utf8'))
ws.write(0, 2, ‘Продолжительность’.decode('utf8'))
ws.write(0, 3, ‘Добавочный’.decode('utf8'))
ws.write(0, 4, ‘Логин оператора’.decode('utf8'))
ws.write(0, 5, ‘Номер клиента’.decode('utf8'))
ws.write(0, 6, ‘SV’.decode('utf'))
ws.write(0, 7, ‘Подразделение’.decode('utf8'))
#	Настраиваем ширину колонок, ширина - 3й аргумент
ws.set_column(0, 2, 20)
ws.set_column(3, 3, 15)
ws.set_column(4, 4, 25)
ws.set_column(5, 5, 20)
ws.set_column(6, 6, 30)
ws.set_column(7, 7, 30)
row = 1
col = 0
#	Заносим данные из запросов
for data in dataRequest:
if data.attributecalluuid in calls:
#	print data.rtargetagentselected.split('_')
#	print data.rtargetagentselected +“\n”
thecall = calls
ws.write_datetime(row, 0, thecall.callStart, date_format)
ws.write_datetime(row, 1, thecall.callEnd, date_format)
ws.write_datetime(row, 2, thecall.callDuration, time_format)
ws.write_number(row, 3, data.attributethisdn)
ws.write(row, 4, str(data.rtargetagentselected))
ws.write_number(row, 5, data.gsw_phone)
if ‘_’ in data.rtargetagentselected:
dep, man, sam, title = findUserLDAP(data.rtargetagentselected.split('_'), ldap_connection)
ws.write(row, 6, man)
ws.write(row, 7, dep)
row += 1
#	Вешаем фильтры на заголовки колонок
ws.autofilter('A1:H'+str(row))
wb.close()

# -*- coding: utf-8 -*-
import ldap, sys
ldapServer = "ldap://1309.example.com"
bindDN = "norights@example.com"
bindPass = "123"
#	userFilter = "(&(objectClass=person)(primaryGroupID=7235))"
userBase = "ou=all users,dc=example,dc=com".decode('utf8')
try:
	ldap_connection = ldap.initialize(ldapServer)
	ldap_connection.simple_bind_s(bindDN, bindPass)
except ldap.LDAPError, e:
	print 'Error connecting to server ' + str(e) + "\n"
def findUserLDAP(login, ldapConnection):
	userFilter = "(sAMAccountName=" + login + ")"
	try:
		acc = ldapConnection.search_s(
			userBase,
			ldap.SCOPE_SUBTREE,
			userFilter,
			attrlist = ['sAMAccountName', 'Manager', 'department', 'title']
		)
		try:
#			print "Returning..."
			return ''.join(map(str, acc[0][1]['department'])).decode('utf8'), ''.join(map(str, acc[0][1]['manager'])).decode('utf8').split('=')[1].split(',')[0], ''.join(map(str, acc[0][1]['sAMAccountName'])), ''.join(map(str, acc[0][1]['title'])).decode('utf8') 
		except:
			return
	except ldap.LDAPError, e:
		print "Error searching user " + str(e)
		sys.stderr.write("Error searching user " + str(e))
		return
#u = 'VF_tsk'
#print u.split('_')[0]
#findUserLDAP('VF', ldap_connection)
#dep, man, sam, title = findUserLDAP('VF')
#print dep.decode('utf8')
"""
pagedResultsControl = SimplePageResultsControl(
	ldap.LDAP_CONTROL_PAGE_OID, True, (pageSize, ''))
accounts = []
pages = 0
while True:
	serverctrls = [pagedResultsControl]
	try:
		msgid = ldap_connection.search_ext(
			userBase,
			ldap.SCOPE_ONELEVEL,
			userFilter,
			attrlist = ['sAMAccountName', 'Manager'],
			serverctrls = serverctrls
		)
	except ldap.LDAPError, e:
		print 'Error performin user search: ' + str(e) + "\n"
		sys.exit(1)
	try:
		unused_code, results, unused_msgid, serverctrls = ldap_connection.result3(msgid)
	except ldap.LDAPError, e:
		print 'Error getting search results: ' + str(e) + "\n"
		sys.exit(1)
	for result in results:
		pages += 1
		accounts.append(result)
	cookie = None
	for serverctrl in serverctrls:
		if serverctrl.controlType == ldap.LDAP_CONTROL_PAGE_OID:
			unused_est, cookie = serverctrl.controlValue
			if cookie:
				pagedResultsControl.controlValue = (pageSize, cookie)
			break
	if not cookie:
		break
ldap_connection.unbind_s()
for entry in accounts:
	print entry
#	print str(entry[0]) + " === " + 
#	if entry[1].has_key('sAMAccountName') and entry[1].has_key('Manager'):
#		print str()
"""