export_agenda.py

def export_job_agenda(request, job):

    # import ipdb; ipdb.set_trace()

    job_object = get_object_or_404(Job, pk=job)
    schedule = Schedule.objects.filter(job=job_object)
    list_schedule = []

    # para cada agendamento
    for sc in schedule:
        # liste os candidatos
        for user in sc.candidates.all():
            # liste o confirmados
            confirmed_candidates = list(sc.confirmed.all().values_list('id'))
            # traga o profile e upf de cada candidatos listado
            user_profile = UserProfile.objects.get(user=user)
            usercpf = UserCPF.objects.filter(user=user).first()
            # pegue o cpf se for brasileiro e estiver cadastrado
            if usercpf is None:
                cpf = ''
            elif usercpf.not_brazilian:
                cpf = force_text(_('Não é Brasileiro'))
            else:
                cpf = usercpf.cpf

            try:
                candidate_last_salary = user_profile.user.usersalary.last_salary
                candidate_mim_salary = user_profile.user.usersalary.min_salary
                candidate_max_salary = user_profile.user.usersalary.max_salary
            except Exception as e :
                candidate_last_salary = '----'
                candidate_mim_salary = '----'
                candidate_max_salary = '----'
            # monte o endereço
            address = u'{} ,Nº {} complemento:{}'.format(
                user_profile.address,  user_profile.adddressnumber, user_profile.complement)
            # liste os reposaveis se houver
            _interviews = ', '.join(
                list(sc.interviewers.all().values_list('email', flat=True)))
            # verifique se o cabra está confirmado
            _confirmed = ''
            if sc.candidates.filter(candidates__in=confirmed_candidates).first():
                _confirmed = force_text(_('SIM'))
            else:
                _confirmed = force_text(_(u'Não'))
            if sc.is_canceled:
                _is_cancelad = force_text(_('SIM'))
            else:
                _is_cancelad = force_text(_(u'Não'))

            # formating time
            _time = sc.start_time.strftime("%H:%M:%S")
            candidate = Candidate.objects.get(candidate=user, job=job_object)
            # dicionario com as informações
            data = dict(
                job=job_object.title,
                name=user.get_full_name(),
                email=user.email,
                cpf=cpf,
                phone=user_profile.phone1,
                phone_other=user_profile.phone2,
                birthday=user_profile.birthday,
                job_match=candidate.match * 0.01,
                status=force_text(candidate.get_status_display()),
                source=candidate.source,

                candidate_age=user_profile.get_age,
                candidate_born_sex=force_text(user_profile.get_born_sex_display()),
                candidate_social_status=force_text(user_profile.get_social_status_display()),

                candidate_last_salary=candidate_last_salary,
                candidate_mim_salary=candidate_mim_salary,
                candidate_max_salary=candidate_max_salary,

                candidate_education=user_profile.get_user_education().title,
                candidate_status_complete=int(
                    user_profile.percentage_complete) * 0.01,

                candidate_address=address,
                candidate_neighbourhood=user_profile.neighbourhood,
                candidate_state=user_profile.state,
                candidate_city=user_profile.city,
                candidate_country=user_profile.country,

                title=sc.title,
                creator=sc.creator.get_full_name(),
                creator_email=sc.creator.email,
                type_conference=force_text(sc.get_conference_type_display()),
                date=sc.start_date,
                time=_time,
                is_canceled=_is_cancelad,
                interviewers=_interviews,
                confirmed=_confirmed
            )
            list_schedule.append(data)

    out_format = ''
    df = pd.DataFrame(list_schedule)

    # codigo estava quebrando quando não havia candidatos em agendamento.
    if not df.empty:
        # ordenando colunas no dataframe
        df = df[['job', 'name', 'email', 'cpf', 'phone', 'phone_other', 'birthday', 'job_match',
                 'status', 'source', 'candidate_age', 'candidate_born_sex',
                 'candidate_social_status', 'candidate_last_salary', 'candidate_mim_salary',
                 'candidate_max_salary', 'candidate_education', 'candidate_status_complete',
                 'candidate_address', 'candidate_neighbourhood', 'candidate_state', 'candidate_city',
                 'candidate_country',  'title', 'creator', 'creator_email', 'type_conference',
                 'date', 'time', 'is_canceled', 'interviewers', 'confirmed']]

        df['date'] = pd.to_datetime(df.date).dt.strftime('%d/%m/%Y')
        df['birthday'] = pd.to_datetime(df.birthday).dt.strftime('%d/%m/%Y')

        output = StringIO.StringIO()
        writer = pd.ExcelWriter(output, engine='xlsxwriter')
        df.to_excel(writer, sheet_name=job_object.title, index=False)

        # melhorando a formatação.
        workbook = writer.book
        worksheet = writer.sheets[job_object.title]

        worksheet.set_zoom(90)
        momey_format = workbook.add_format({'align': 'center', 'num_format': 'R$ #,##0', 'bold': True})
        porcent_format = workbook.add_format({'align': 'center', 'num_format': '0%', 'bold': True})
        number_format = workbook.add_format({'align': 'center', 'bold': True})
        unicode_format = workbook.add_format({'align': 'center'})

        # unicode format
        for idx, col in enumerate(df):
            if df[col].dtype == 'object':
                header = df[[col]].astype(unicode).columns.values
                header_len = len(header.max()) + 2
                column_len = df[[col]].astype(unicode).apply(
                    lambda x: x.str.len()).max().max() + 2
                if header_len > column_len:
                    worksheet.set_column(idx, idx, header_len, unicode_format)

                else:
                    worksheet.set_column(idx, idx, column_len, unicode_format)

        # money format
        worksheet.set_column('K:K', 25, number_format)
        worksheet.set_column('N:N', 25, momey_format)
        worksheet.set_column('O:O', 25, momey_format)
        worksheet.set_column('P:P', 25, momey_format)

        # porcent format
        worksheet.set_column('H:H', 25, porcent_format)
        worksheet.set_column('R:R', 25, porcent_format)

        writer.save()
        output.seek(0)

        content_type_ = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        response = HttpResponse(output.read(), content_type=content_type_)
        out_format = 'attachment; filename=%s-%s.xlsx' % (
            slugify(job_object.title), date.today().isoformat())
        response['Content-Disposition'] = out_format
    else:
        messages.error(request, _('Não existe Agendamento para essa vaga'))
        response = HttpResponseRedirect(reverse('jobapplicants', kwargs={'pk': job_object.pk }))
    return response

export_data_report.py

def _format(val):
    if isinstance(val, datetime):
        if val.year <= 1900:
            val = '{0.day:02d}/{0.month:02d}/{0.year:4d}'.format(val)
        else:
            val = val.strftime('%d/%m/%Y %H:%M')
    elif isinstance(val, date):
        if val.year <= 1900:
            val = '{0.day:02d}/{0.month:02d}/{0.year:4d}'.format(val)
        else:
            val = val.strftime('%d/%m/%Y')
    return val


def _export_data(request, columns, rows, file_title):
        response = HttpResponse(content_type='application/ms-excel')
        filename_ = '%s%s.xls' % (slugify(file_title), date.today().isoformat())
        filename = 'attachment; filename="%s"' % filename_
        response['Content-Disposition'] = filename


        wb = xlwt.Workbook(encoding='utf-8')
        ws = wb.add_sheet('sheet')

        # Sheet header, first row
        row_num = 0

        font_style = xlwt.XFStyle()
        default_style = xlwt.XFStyle()
        font_style.font.bold = True

        for col_num in range(len(columns)):
            ws.write(row_num, col_num, force_text(columns[col_num]), font_style)

        for row, rowdata in enumerate(sorted(rows)):
            row_num += 1
            for col, val in enumerate(rowdata):
                val = _format(val)
                ws.write(row_num, col, val, default_style)
        wb.save(response)
        return response

export_job_open.py

def export_data_jobsopen(request):
    '''
    Exporta as vagas abertas, incluindo campos customizados.
    '''
    response = HttpResponse(content_type='application/ms-excel')
    filename = 'attachment; filename="open_jobs%s.xls"' % date.today().isoformat()
    response['Content-Disposition'] = filename
    wb = xlwt.Workbook(encoding='utf-8')
    ws = wb.add_sheet('sheet')

    # Sheet header, first row
    row_num = 0

    font_style = xlwt.XFStyle()
    font_style.font.bold = True

    firm = request.user.employee.firm
    emp = Employee.objects.filter(firm=firm).values('user')
    jobs = Job.objects.filter(creator__in=emp)

    jobfieldanswer = JobFieldAnswer.objects.filter(job__in=jobs)
    title_fields = [field.jobcustomfield.title for field in jobfieldanswer]
    # Precisa do set pra não repetir os campos customizados.
    title_fields = set(title_fields)

    columns = [
        _('Vaga'),
        _('Criado por'),
        _('Recrutador'),
        _('Código de Referência'),
        _('Data Publicação'),
        _('Status'),
        _('Anotações'),
        _('Privacidade'),
        _('Tipo de Contratação'),
        _('Experiência'),
        _('Descrição da Vaga e Responsabilidades'),
        _('Requisitos obrigatórios'),
        _('Benefícios'),
        _('Departamento'),
        _('Prazo de candidatura'),
        _('Local da Vaga'),
        _('Número de posições'),
        _('Meta SLA em dias'),
        _('Data Meta SLA'),
        _('SLA Atingido'),
        _('SLA Parou?'),
        _('Data SLA Parou'),
        _('Status'),
        _('Centro de Custo'),
        _('PCD'),
        _('Motivo de Cancelamento'),
        _('Divulgação'),
        _('Gestor'),
        _('Faixa Salarial'),
        _('Escolaridade mínima'),
        _('Experiência imprescindível'),
        _('Palavras-chave'),
        _('Template de Etapas'),
    ]
    columns.extend(title_fields)

    for col_num in range(len(columns)):
        ws.write(row_num, col_num, force_text(columns[col_num]), font_style)

    # Sheet body, remaining rows
    default_style = xlwt.XFStyle()

    date_initial = request.GET.get('date_initial', '')
    date_end = request.GET.get('date_end', '')
    d_ini, d_end = '', ''
    if date_initial:
        d_ini = datetime.strptime(date_initial, "%Y-%m-%d")
    if date_end:
        d_end = datetime.strptime(date_end, "%Y-%m-%d") + timedelta(days=1)

    jobs = jobs.filter(pub_date__range=[date_initial, date_end])


    rows = []
    for job in jobs:
        try:
            jobnotes = '|'.join(map(lambda x: re.sub(r'<.*?>', ' ', x),
                                    job.jobnotes_set.values_list('note', flat=True)))
        except:
            jobnotes = ''
        item = [
            job.title_ref_number(),
            job.creator.email,
            job.recruiter.email if job.recruiter else '---',
            job.ref_number,
            job.pub_date,
            force_text(job.status_display()),
            jobnotes,
            force_text(job.privacy_display()),
            force_text(job.employment_type_display()),
            force_text(job.required_experience_display()),
            job.description,
            job.requirements,
            job.benefits,
            job.department.department if job.department else '---',
            job.deadline,
            job.city,
            job.number_of_openings,
            job.sla,
            job.sla_date,
            job.sla_current,
            force_text(job.sla_stopped_display()),
            job.date_stopped,
            job.custom_status.title if job.custom_status else '---',
            job.cost_center,
            force_text(job.disability_display()),
            job.reason_cancel.title if job.reason_cancel else '---',
            force_text(job.get_where_to_post_display()),
            job.hiring_manager.get_full_name() if job.hiring_manager else '---',
            '%s - %s' % (job.min_salary, job.max_salary),
            force_text(job.get_min_required_edu_level_display()),
            force_text(job.get_min_required_experience_display()),
            job.keywords,
            job.stage_template.title if job.stage_template else '',
        ]
        # Inserindo os campos customizados
        for title in title_fields:
            if title in job.jobfieldanswers.values_list(
                    'jobcustomfield__title', flat=True):
                item.append(job.jobfieldanswers.filter(
                    jobcustomfield__title=title).values_list(
                    'answer', flat=True).first())
            else:
                item.append('---')
        rows.append(item)

    for row, rowdata in enumerate(sorted(rows)):
        row_num += 1
        for col, val in enumerate(rowdata):
            if isinstance(val, datetime):
                val = val.strftime('%d/%m/%Y %H:%M')
            elif isinstance(val, date):
                val = val.strftime('%d/%m/%Y')
            ws.write(row_num, col, val, default_style)

    wb.save(response)
    return response


def export_data_jobsclosed(request):
    '''
    Exporta as vagas fechadas, incluindo campos customizados.
    '''
    response = HttpResponse(content_type='application/ms-excel')
    filename = 'attachment; filename="closed_jobs%s.xls"' % date.today().isoformat()
    response['Content-Disposition'] = filename
    wb = xlwt.Workbook(encoding='utf-8')
    ws = wb.add_sheet('sheet')

    # Sheet header, first row
    row_num = 0

    font_style = xlwt.XFStyle()
    font_style.font.bold = True

    firm = request.user.employee.firm
    emp = Employee.objects.filter(firm=firm).values('user')
    jobs = Job.objects.filter(creator__in=emp, sla_stopped=True)

    jobfieldanswer = JobFieldAnswer.objects.filter(job__in=jobs)
    title_fields = [field.jobcustomfield.title for field in jobfieldanswer]
    # Precisa do set pra não repetir os campos customizados.
    title_fields = set(title_fields)

    columns = [
        _('Vaga'),
        _('Criado por'),
        _('Recrutador'),
        _('Código de Referência'),
        _('Data Publicação'),
        _('Status'),
        _('Privacidade'),
        _('Tipo de Contratação'),
        _('Experiência'),
        _('Descrição da Vaga e Responsabilidades'),
        _('Requisitos obrigatórios'),
        _('Benefícios'),
        _('Departamento'),
        _('Prazo de candidatura'),
        _('Local da Vaga'),
        _('Número de posições'),
        _('Meta SLA em dias'),
        _('Data Meta SLA'),
        _('SLA Atingido'),
        _('SLA Parou?'),
        _('Data SLA Parou'),
        _('Status'),
        _('Centro de Custo'),
        _('PCD'),
        _('Motivo de Cancelamento'),
        _('Divulgação'),
        _('Gestor'),
        _('Faixa Salarial'),
        _('Escolaridade mínima'),
        _('Experiência imprescindível'),
        _('Palavras-chave'),
        _('Template de Etapas'),
    ]
    columns.extend(title_fields)

    for col_num in range(len(columns)):
        ws.write(row_num, col_num, force_text(columns[col_num]), font_style)

    # Sheet body, remaining rows
    default_style = xlwt.XFStyle()

    date_initial = request.GET.get('date_initial', '')
    date_end = request.GET.get('date_end', '')
    d_ini, d_end = '', ''
    if date_initial:
        d_ini = datetime.strptime(date_initial, "%Y-%m-%d")
    if date_end:
        d_end = datetime.strptime(date_end, "%Y-%m-%d") + timedelta(days=1)

    jobs = jobs.filter(date_stopped__range=[date_initial, date_end])

    rows = []
    for job in jobs:
        item = [
            job.title_ref_number(),
            job.creator.email,
            job.recruiter.email if job.recruiter else '---',
            job.ref_number,
            job.pub_date,
            force_text(job.status_display()),
            force_text(job.privacy_display()),
            force_text(job.employment_type_display()),
            force_text(job.required_experience_display()),
            job.description,
            job.requirements,
            job.benefits,
            job.department.department if job.department else '---',
            job.deadline,
            job.city,
            job.number_of_openings,
            job.sla,
            job.sla_date,
            job.sla_current,
            force_text(job.sla_stopped_display()),
            job.date_stopped,
            job.custom_status.title if job.custom_status else '---',
            job.cost_center,
            force_text(job.disability_display()),
            job.reason_cancel.title if job.reason_cancel else '---',
            force_text(job.get_where_to_post_display()),
            job.hiring_manager.get_full_name() if job.hiring_manager else '---',
            '%s - %s' % (job.min_salary, job.max_salary),
            force_text(job.get_min_required_edu_level_display()),
            force_text(job.get_min_required_experience_display()),
            job.keywords,
            job.stage_template.title if job.stage_template else '',
        ]
        # Inserindo os campos customizados
        for title in title_fields:
            if title in job.jobfieldanswers.values_list(
                    'jobcustomfield__title', flat=True):
                item.append(job.jobfieldanswers.filter(
                    jobcustomfield__title=title).values_list(
                    'answer', flat=True).first())
            else:
                item.append('---')
        rows.append(item)

    for row, rowdata in enumerate(sorted(rows)):
        row_num += 1
        for col, val in enumerate(rowdata):
            if isinstance(val, datetime):
                val = val.strftime('%d/%m/%Y %H:%M')
            elif isinstance(val, date):
                val = val.strftime('%d/%m/%Y')
            ws.write(row_num, col, val, default_style)

    wb.save(response)
    return response

export_matrix_data.py

def export_matrix_job(request, pk):
    context = {}

    list_ = []
    job = get_object_or_404(Job, pk=pk)
    context['candidates'] = Candidate.objects.filter(job=job)

    cands, titles, titles2, grades = [], [], [], []

    quizzes = JobQuiz.objects.filter(job=job).prefetch_related('quiz', 'job')
    cand = dict(Candidate.objects.filter(
        job=job).values_list('candidate', 'pk'))

    for q in quizzes:
        candidates = list(q.job.candidates.values_list('id', flat=True))
        sitting_ = q.quiz.sitting_set.filter(user_id__in=candidates)
        if sitting_.exists():
            for i in sitting_:
                _sitting = Sitting.objects.filter(user_id=i.user_id)
                user_profile = UserProfile.objects.get(user=i.user_id)

                aproved_text = ''
                for s in _sitting:
                    if not s.check_if_passed:
                        aproved_text = force_text(_('Reprovado'))
                    elif aproved_text == '' and s.check_if_passed:
                        aproved_text = force_text(_('Aprovado'))

                usercpf = UserCPF.objects.filter(user=i.user_id)
                if usercpf.exists():
                    document = i.user.usercpf.cpf
                else:
                    document = None
                data = dict(
                    user=i.user.get_full_name(),
                    email=i.user.email,
                    document=document,
                    phone=user_profile.phone1,
                    aproved=aproved_text,
                    porcent=i.get_percent_correct,
                    question=i.quiz.title,
                )
                list_.append(data)

    _index = ['user', 'email', 'document', 'phone', 'aproved']

    if not list_:
        kw = {'pk': pk}
        messages.error(request, _('Nenhum teste respondido ainda'))
        response = HttpResponseRedirect(reverse('jobapplicants', kwargs=kw))
        return response

    df = pd.DataFrame(list_)
    df.groupby("question")
    pv = pd.pivot_table(df,
                        index=_index,
                        columns='question',
                        values='porcent',
                        aggfunc=lambda x: x
                        )

    output = StringIO.StringIO()
    writer = pd.ExcelWriter(output, engine='xlsxwriter')
    _title =  job.title[:30] if len(job.title) > 30 else job.title
    pv.to_excel(writer, sheet_name=_title)
    workbook = writer.book
    worksheet = writer.sheets[_title]

    unicode_format = workbook.add_format({'align': 'center'})

    # unicode format
    for idx, col in enumerate(df):
        if df[col].dtype == 'object':

            header = df[[col]].astype(unicode).columns.values
            header_len = len(header.max()) + 2

            column_len = df[[col]].astype(unicode).apply(
                lambda x: x.str.len()).max().max() + 2

            if header_len > column_len:
                worksheet.set_column(idx, idx, header_len, unicode_format)

            else:
                worksheet.set_column(idx, idx, column_len, unicode_format)

    worksheet.set_column('A:A', 25, unicode_format)
    worksheet.set_column('B:B', 35, unicode_format)
    worksheet.set_column('C:C', 25, unicode_format)
    worksheet.set_column('D:D', 25, unicode_format)
    worksheet.set_column('E:E', 25, unicode_format)

    writer.save()
    output.seek(0)

    content_type_ = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    response = HttpResponse(output.read(), content_type=content_type_)
    out_format = 'attachment; filename=%s-%s.xlsx' % (
        slugify(job.title), date.today().isoformat())
    response['Content-Disposition'] = out_format

    return response