Merge lp:~inddiana/sisb/sisb_nomina_resumen_reporte_gastos into lp:sisb

[[SISB] Aryrosa Fuentes]

[IMP] se creo el modulo sisb_nomina_resumen para generar un reporte de los gasto de nominas por mes.
[FIX] Ajsutes en el reporte con respecto a la forma de obtener la fecha de inicio de la relacion laboral. Se asume que en sisb_nomina ya existe la funcion que realiza el calculo.

=== added directory 'sisb_nomina_resumen'
=== added file 'sisb_nomina_resumen/__init__.py'
--- sisb_nomina_resumen/__init__.py	1970-01-01 00:00:00 +0000
+++ sisb_nomina_resumen/__init__.py	2018-02-07 15:37:15 +0000
@@ -0,0 +1,1 @@
+import wizard, report
 
=== added file 'sisb_nomina_resumen/__openerp__.py'
--- sisb_nomina_resumen/__openerp__.py	1970-01-01 00:00:00 +0000
+++ sisb_nomina_resumen/__openerp__.py	2018-02-07 15:37:15 +0000
@@ -0,0 +1,42 @@
+# -*- coding:utf-8 -*-
+##############################################################################
+#
+#    SISB, Sistema Integral Simon Bolivar
+#    Copyright (C) 2012-2013 Industrias Diana, C.A. All Rights Reserved
+#
+#    This program is free software: you can redistribute it and/or modify
+#    it under the terms of the GNU Affero General Public License as published by
+#    the Free Software Foundation, either version 3 of the License, or
+#    (at your option) any later version.
+#
+#    This program is distributed in the hope that it will be useful,
+#    but WITHOUT ANY WARRANTY; without even the implied warranty of
+#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+#    GNU Affero General Public License for more details.
+#
+#    You should have received a copy of the GNU Affero General Public License
+#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
+#
+##############################################################################
+{
+    'name': 'Resumen general de asignaciones y deducciones de nomina.',
+    'version': '1.0',
+    "author": "Industrias Diana C.A.",
+    "category": "Human Resources",
+    "website": "http://www.industriasdiana.gob.ve",
+    'description': """Modulo que genera un reporte general con todas las asignaciones y deducciones de todos 
+                    los trabajadores, clasificandolos por el tipo de nomina, en formato .ods y protegido
+                    contra escritura.""",
+    'depends': ['base', 'sisb_nomina'],
+    'external_dependencies': {
+        'python': ['xlwt']
+    },
+    'init_xml': [],
+    'update_xml': [
+        'security/groups.xml',
+        'security/ir.model.access.csv',
+        'wizard/sisb_nomina_resumen_wizard.xml'
+    ],
+    'active': True,
+    'installable': True
+}
 
=== added directory 'sisb_nomina_resumen/i18n'
=== added file 'sisb_nomina_resumen/i18n/es_VE.mo'
 Binary files sisb_nomina_resumen/i18n/es_VE.mo	1970-01-01 00:00:00 +0000 and sisb_nomina_resumen/i18n/es_VE.mo	2018-02-07 15:37:15 +0000 differ
=== added file 'sisb_nomina_resumen/i18n/es_VE.po'
--- sisb_nomina_resumen/i18n/es_VE.po	1970-01-01 00:00:00 +0000
+++ sisb_nomina_resumen/i18n/es_VE.po	2018-02-07 15:37:15 +0000
@@ -0,0 +1,109 @@
+# Translation of OpenERP Server.
+# This file contains the translation of the following modules:
+#	* sisb_nomina_resumen
+#
+msgid ""
+msgstr ""
+"Project-Id-Version: OpenERP Server 6.0.4\n"
+"Report-Msgid-Bugs-To: [e-mail]\n"
+"POT-Creation-Date: 2017-05-10 13:33+0000\n"
+"PO-Revision-Date: 2017-05-10 09:37-0400\n"
+"Last-Translator: carmelo <[e-mail]>\n"
+"Language-Team: \n"
+"MIME-Version: 1.0\n"
+"Content-Type: text/plain; charset=UTF-8\n"
+"Content-Transfer-Encoding: 8bit\n"
+"Plural-Forms: \n"
+
+#. module: sisb_nomina_resumen
+#: code:addons/sisb_nomina_resumen/wizard/sisb_nomina_resumen.py:190
+#, python-format
+msgid "Net Total"
+msgstr "Total Neto"
+
+#. module: sisb_nomina_resumen
+#: code:addons/sisb_nomina_resumen/wizard/sisb_nomina_resumen.py:157
+#, python-format
+msgid "Roster"
+msgstr "Nómina"
+
+#. module: sisb_nomina_resumen
+#: view:sisb.nomina.resumen:0
+msgid "Print"
+msgstr "Imprimir"
+
+#. module: sisb_nomina_resumen
+#: code:addons/sisb_nomina_resumen/wizard/sisb_nomina_resumen.py:220
+#, python-format
+msgid "roster_report_"
+msgstr "reporte_nomina_"
+
+#. module: sisb_nomina_resumen
+#: model:ir.actions.act_window,name:sisb_nomina_resumen.action_wizard_sisb_nomina_resumen
+#: model:ir.ui.menu,name:sisb_nomina_resumen.menu_wizard_sisb_nomina_resumen
+msgid "Resumen mensual de nomina"
+msgstr "Resumen mensual de nomina"
+
+#. module: sisb_nomina_resumen
+#: model:ir.actions.report.xml,name:sisb_nomina_resumen.report_nomina_resumen
+msgid "Reporte de nomina"
+msgstr "Reporte de nomina"
+
+#. module: sisb_nomina_resumen
+#: code:addons/sisb_nomina_resumen/wizard/sisb_nomina_resumen.py:177
+#, python-format
+msgid "Total bonuses"
+msgstr "Total Asignaciones"
+
+#. module: sisb_nomina_resumen
+#: field:sisb.nomina.resumen,month:0
+msgid "Month"
+msgstr "Mes"
+
+#. module: sisb_nomina_resumen
+#: code:addons/sisb_nomina_resumen/wizard/sisb_nomina_resumen.py:158
+#, python-format
+msgid "Employee"
+msgstr "Empleado"
+
+#. module: sisb_nomina_resumen
+#: code:addons/sisb_nomina_resumen/wizard/sisb_nomina_resumen.py:162
+#, python-format
+msgid "Job"
+msgstr "Cargo"
+
+#. module: sisb_nomina_resumen
+#: code:addons/sisb_nomina_resumen/wizard/sisb_nomina_resumen.py:159
+#, python-format
+msgid "Identification"
+msgstr "Cédula"
+
+#. module: sisb_nomina_resumen
+#: code:addons/sisb_nomina_resumen/wizard/sisb_nomina_resumen.py:160
+#, python-format
+msgid "Join date"
+msgstr "Fecha Ingreso"
+
+#. module: sisb_nomina_resumen
+#: code:addons/sisb_nomina_resumen/wizard/sisb_nomina_resumen.py:161
+#, python-format
+msgid "Department"
+msgstr "Departamento"
+
+#. module: sisb_nomina_resumen
+#: model:ir.model,name:sisb_nomina_resumen.model_sisb_nomina_resumen
+msgid "sisb.nomina.resumen"
+msgstr "sisb.nomina.resumen"
+
+#. module: sisb_nomina_resumen
+#: code:addons/sisb_nomina_resumen/wizard/sisb_nomina_resumen.py:223
+#, python-format
+msgid "No data found"
+msgstr "No se encontró información"
+
+#. module: sisb_nomina_resumen
+#: code:addons/sisb_nomina_resumen/wizard/sisb_nomina_resumen.py:186
+#, python-format
+msgid "Total debts"
+msgstr "Total Deducciones"
+
 
=== added directory 'sisb_nomina_resumen/report'
=== added file 'sisb_nomina_resumen/report/__init__.py'
--- sisb_nomina_resumen/report/__init__.py	1970-01-01 00:00:00 +0000
+++ sisb_nomina_resumen/report/__init__.py	2018-02-07 15:37:15 +0000
@@ -0,0 +1,1 @@
+import report_nomina_resumen
 
=== added file 'sisb_nomina_resumen/report/report_nomina_resumen.py'
--- sisb_nomina_resumen/report/report_nomina_resumen.py	1970-01-01 00:00:00 +0000
+++ sisb_nomina_resumen/report/report_nomina_resumen.py	2018-02-07 15:37:15 +0000
@@ -0,0 +1,35 @@
+#!/usr/bin/env python
+# -*- coding: utf-8 -*-
+from report import report_sxw
+import os
+
+
+class report_nomina_resumen(report_sxw.rml_parse):
+    """ Herencia a clase base para reportes de OpenERP
+    """
+    _name = 'report.nomina.resumen'
+
+    def __init__(self, cr, uid, name, context):
+        """ Se sobrecarga el init para agregarle el context
+        """
+        super(report_nomina_resumen, self).__init__(cr, uid, name, context)
+        self.context = context
+
+
+report_sxw = report_sxw.report_sxw
+
+
+class TxtParser(report_sxw):
+    def __init__(self, name, table, rml=False, parser=False, header=True, store=False):
+        self.parser_instance = False
+        self.localcontext = {}
+        report_sxw.__init__(self, name, table, rml, parser, header, store)
+
+    def create_single_pdf(self, cursor, uid, ids, data, report_xml, context):
+        file = open(data['file'], 'r')
+        output = file.read()
+        os.remove(data['file'])
+        return (output, 'ods')
+
+
+TxtParser('report.nomina.resumen', 'nomina.resumen', None, parser=report_nomina_resumen, header=False)
 
=== added directory 'sisb_nomina_resumen/security'
=== added file 'sisb_nomina_resumen/security/groups.xml'
--- sisb_nomina_resumen/security/groups.xml	1970-01-01 00:00:00 +0000
+++ sisb_nomina_resumen/security/groups.xml	2018-02-07 15:37:15 +0000
@@ -0,0 +1,20 @@
+<?xml version="1.0" encoding="utf-8"?>
+<openerp>
+  <data noupdate="0">
+    <!-- Groups -->
+    <record id="group_sisb_nomina_resumen" model="res.groups">
+      <field name="name">Nomina / Reporte Mensual</field>
+    </record>
+    <!-- Access to menues -->
+    <record model="ir.ui.menu" id="hr.menu_hr_root">
+      <field eval="[(4, ref('group_sisb_nomina_resumen'))]" name="groups_id"/>
+    </record>
+
+    <record model="ir.ui.menu" id="hr.menu_hr_reporting">
+      <field eval="[(4, ref('group_sisb_nomina_resumen'))]" name="groups_id"/>
+    </record>
+    <record model="ir.ui.menu" id="sisb_nomina.menu_hr_payroll_reporting">
+      <field eval="[(4, ref('group_sisb_nomina_resumen'))]" name="groups_id"/>
+    </record>
+  </data>
+</openerp>
 
=== added file 'sisb_nomina_resumen/security/ir.model.access.csv'
--- sisb_nomina_resumen/security/ir.model.access.csv	1970-01-01 00:00:00 +0000
+++ sisb_nomina_resumen/security/ir.model.access.csv	2018-02-07 15:37:15 +0000
@@ -0,0 +1,2 @@
+id,name,model_id:id,group_id:id,perm_read,perm_write,perm_create,perm_unlink
+access_sisb_nomina_resumen_hr_month,Resumen Nomina: hr.month,sisb_nomina.model_hr_month,group_sisb_nomina_resumen,1,0,0,0
 
=== added directory 'sisb_nomina_resumen/wizard'
=== added file 'sisb_nomina_resumen/wizard/__init__.py'
--- sisb_nomina_resumen/wizard/__init__.py	1970-01-01 00:00:00 +0000
+++ sisb_nomina_resumen/wizard/__init__.py	2018-02-07 15:37:15 +0000
@@ -0,0 +1,1 @@
+import sisb_nomina_resumen
 
=== added file 'sisb_nomina_resumen/wizard/sisb_nomina_resumen.py'
--- sisb_nomina_resumen/wizard/sisb_nomina_resumen.py	1970-01-01 00:00:00 +0000
+++ sisb_nomina_resumen/wizard/sisb_nomina_resumen.py	2018-02-07 15:37:15 +0000
@@ -0,0 +1,253 @@
+#!/usr/bin/env python
+# -*- coding: utf-8 -*-
+"""Modelos del modulo :Resumen mensual de nomina:"""
+from osv import fields, osv
+from tools.translate import _
+from datetime import datetime
+import xlwt
+
+
+class FitCol(object):
+    """Ajusta el ancho de las columnas automaticamente"""
+
+    def __init__(self, sheet):
+        self.sheet = sheet
+        self.widths = dict()
+
+    def write(self, r, c, label='', *args, **kwargs):
+        if isinstance(label, str) and len(args) == 0:
+            data = label.decode('utf-8')
+        elif isinstance(label, str) and len(args) > 0:
+            data = datetime.strptime(label, '%Y-%m-%d')
+        else:
+            data = label
+        self.sheet.write(r, c, data, *args, **kwargs)
+        if isinstance(label, (int, float)):
+            label = str(label).decode('utf-8')
+        width = (len(label) + 1) * 256
+        if width > self.widths.get(c, 0):
+            self.widths[c] = width
+            self.sheet.col(c).width = width
+
+    def __getattr__(self, attr):
+        return getattr(self.sheet, attr)
+
+
+class sisb_nomina_resumen(osv.osv_memory):
+    """Asistente para el reporte del resumen de nomina"""
+    _name = 'sisb.nomina.resumen'
+    _columns = {
+        'month': fields.many2one('hr.month', 'Month', required=True)
+    }
+
+    def get_employees_data(self, cr, month):
+        """ Query a Postgres para obtener informacion de los empleados en un mes.
+
+            :param month: Nombre del mes a consultar.
+        """
+        query = '''
+            SELECT
+                hde.payroll as nom,
+                he.name as emp,
+                he.identification_id as ide,
+                date_start_rel_laboral(he.id)  as dst,
+                hde.department_id as dpt,
+                hde.job as job,
+                case when hpl.type_calc = 'deduction' then 'debt' else 'bonus' end as typ,
+                hc.name as con,
+                sum(hpl.total)
+            FROM
+                hr_payslip_line as hpl
+                join hr_payslip as hrp on hrp.id = hpl.slip_id
+                join hr_employee as he on he.id = hpl.employee_id
+                join hr_department_employee as hde on hde.employee_id = he.id
+                    and hde.date_start < hrp.date_from and (hde.date_end > hrp.date_from or hde.date_end is null)
+                join hr_concept as hc on hc.id = hpl.concept_id and hc.type <> 'aporte_patronal'
+                join hr_process as hp on hp.id = hpl.process_id and hp.type in ('nomina')
+                join hr_period_process_rel as hppr on hppr.process_id = hp.id
+                join hr_period as hpe on hpe.id = hppr.period_id
+                join hr_month as hm on hm.id = hpe.hr_month_id and hm.name = '%s'
+            WHERE appears_on_payslip = True
+            group by emp, con, typ, ide, nom, dst, dpt, job
+            order by emp, typ, con
+        ''' % (month)
+
+        cr.execute(query)
+        employees = {}
+        for roster, name, identification, join_date, department, job, asign, concept, total in cr.fetchall():
+            if identification not in employees:
+                employees[identification] = {}
+            employees[identification]['roster'] = roster
+            employees[identification]['name'] = name
+            employees[identification]['identification'] = identification
+            employees[identification]['join_date'] = join_date
+            employees[identification]['department'] = department
+            employees[identification]['job'] = job
+            if 'bonuses' not in employees[identification]:
+                employees[identification]['bonuses'] = {}
+            if 'debts' not in employees[identification]:
+                employees[identification]['debts'] = {}
+            if asign == 'bonus':
+                employees[identification]['bonuses'][concept] = total
+            else:
+                employees[identification]['debts'][concept] = total
+
+        return employees
+
+    def get_concepts_header(self, cr, month):
+        """ Query que se trae todos los conceptos de asignaciones y deducciones de los trabajadores en el mes indicado.
+
+            :param month: Nombre del mes a consultar.
+        """
+        query = '''
+        SELECT
+            case when hpl.type_calc = 'deduction' then 'debt' else 'bonus' end as asigned,
+            hc.name as concept,
+            hc.code as code
+        FROM
+            hr_payslip_line as hpl
+            join hr_employee as he on he.id = hpl.employee_id
+            join hr_payroll as hno on hno.id = he.hr_payroll_id
+            join hr_concept as hc on hc.id = hpl.concept_id and hc.type <> 'aporte_patronal'
+            join hr_process as hp on hp.id = hpl.process_id and hp.type in ('nomina')
+            join hr_period_process_rel as hppr on hppr.process_id = hp.id
+            join hr_period as hpe on hpe.id = hppr.period_id
+            join hr_month as hm on hm.id = hpe.hr_month_id and hm.name = '%s'
+        WHERE appears_on_payslip = True
+        group by concept, asigned, hc.code
+        order by asigned, concept
+        ''' % (month)
+
+        cr.execute(query)
+        bonuses = ['']
+        debts = []
+        for row in cr.fetchall():
+            con_type, concept, code = row
+            if con_type == 'bonus':
+                if code == '0100':
+                    bonuses[0] = concept
+                else:
+                    bonuses.append(concept)
+            else:
+                debts.append(concept)
+
+        return {'bonuses': bonuses, 'debts': debts}
+
+    def get_payrolls_pages(self, cr, month):
+        """ Query que se trae los nombres de las nominas de los trabajadores del mes indicado.
+
+            :param month: Nombre del mes a consultar.
+        """
+        query = '''
+        SELECT
+            hde.payroll as roster
+        FROM
+            hr_payslip_line as hpl
+            join hr_payslip as hrp on hrp.id = hpl.slip_id
+            join hr_employee as he on he.id = hpl.employee_id
+            join hr_payroll as hno on hno.id = he.hr_payroll_id
+            join hr_department_employee as hde on hde.employee_id = he.id and hde.date_start < hrp.date_from
+                and (hde.date_end > hrp.date_from or hde.date_end is null)
+            join hr_process as hp on hp.id = hpl.process_id and hp.type in ('nomina')
+            join hr_period_process_rel as hppr on hppr.process_id = hp.id
+            join hr_period as hpe on hpe.id = hppr.period_id
+            join hr_month as hm on hm.id = hpe.hr_month_id and hm.name = '%s'
+        WHERE appears_on_payslip = True
+        group by roster
+        ''' % (month)
+
+        cr.execute(query)
+        return [row[0] for row in cr.fetchall()]
+
+    def print_results(self, cr, uid, ids, context=None):
+        """ Funcion llamada por el wizard para imprimir los resultados.
+        """
+        month = self.browse(cr, uid, ids)[0].month.name
+        rosters = self.get_payrolls_pages(cr, month)
+        doc = xlwt.Workbook()
+        pages = {}
+        j = {}  # Seran los indices de las filas de cada pagina
+        for roster in rosters:
+            roster = roster[:31]  # Por limitaciones de los archivos xls, los nombres de paginas no pueden exceder los 31 caracteres
+            # Se crean las paginas y se guardan. Se le van creando las cabeceras
+            pages[roster] = doc.add_sheet(roster, True)
+            pages[roster].protect = True
+            pages[roster] = FitCol(pages[roster])
+            pages[roster].write(0, 0, _('N°'))
+            pages[roster].write(0, 1, _('Employee'))
+            pages[roster].write(0, 2, _('Identification'))
+            pages[roster].write(0, 3, _('Join date'))
+            pages[roster].write(0, 4, _('Department'))
+            pages[roster].write(0, 5, _('Job'))
+            j[roster] = 1
+
+        # Se procede a crear las cabeceras de conceptos en cada una de las paginas
+        i = 6  # Columns index
+        columns = {}
+        concepts = self.get_concepts_header(cr, month)
+        bonuses = concepts['bonuses']
+        debts = concepts['debts']
+        for bonus in bonuses:
+            for page in pages.values():
+                page.write(0, i, bonus)
+            columns[bonus] = i
+            i += 1
+        for page in pages.values():
+            page.write(0, i, _('Total bonuses'))
+        columns['bonuses'] = i
+        i += 1
+        for debt in debts:
+            for page in pages.values():
+                page.write(0, i, debt)
+            columns[debt] = i
+            i += 1
+        for page in pages.values():
+            page.write(0, i, _('Total debts'))
+        columns['debts'] = i
+        i += 1
+        for page in pages.values():
+            page.write(0, i, _('Net Total'))
+        columns['net_total'] = i
+
+        # -------- EMPEZAMOS A LLENAR LA DATA DE LOS TRABAJADORES ------------
+        datas = self.get_employees_data(cr, month)
+        date_format = xlwt.XFStyle()
+        date_format.num_format_str = 'dd/mm/yyyy'
+        currency_format = xlwt.XFStyle()
+        currency_format.num_format_str = "#,##0.00"
+        for data in datas:
+            n = datas[data]['roster'][:31]
+            pages[n].write(j[n], 0, j[n])
+            pages[n].write(j[n], 1, datas[data]['name'])
+            pages[n].write(j[n], 2, int(datas[data]['identification']))
+            pages[n].write(j[n], 3, datas[data]['join_date'], date_format)
+            pages[n].write(j[n], 4, datas[data]['department'])
+            pages[n].write(j[n], 5, datas[data]['job'])
+            total_bonuses = 0
+            for bonus in datas[data]['bonuses']:
+                pages[n].write(j[n], columns[bonus], datas[data]['bonuses'][bonus], currency_format)
+                total_bonuses += datas[data]['bonuses'][bonus]
+            pages[n].write(j[n], columns['bonuses'], total_bonuses, currency_format)
+            total_debts = 0
+            for debt in datas[data]['debts']:
+                pages[n].write(j[n], columns[debt], datas[data]['debts'][debt], currency_format)
+                total_debts += datas[data]['debts'][debt]
+            pages[n].write(j[n], columns['debts'], total_debts, currency_format)
+            pages[n].write(j[n], columns['net_total'], total_bonuses - total_debts, currency_format)
+            j[n] += 1
+        try:
+            file_name = '/tmp/' + _('roster_report_') + datetime.now().strftime('%H%M%S%d%m%Y')
+            doc.save(file_name)
+        except:
+            raise osv.except_osv('ERROR', _('No data found'))
+
+        data = {}
+        data['file'] = file_name
+        return {
+            'type': 'ir.actions.report.xml',
+            'report_name': 'nomina.resumen',
+            'datas': data,
+        }
+
+
+sisb_nomina_resumen()
 
=== added file 'sisb_nomina_resumen/wizard/sisb_nomina_resumen_wizard.xml'
--- sisb_nomina_resumen/wizard/sisb_nomina_resumen_wizard.xml	1970-01-01 00:00:00 +0000
+++ sisb_nomina_resumen/wizard/sisb_nomina_resumen_wizard.xml	2018-02-07 15:37:15 +0000
@@ -0,0 +1,38 @@
+<?xml version="1.0" encoding="utf-8"?>
+<openerp>
+  <data>
+    <!-- Wizard view -->
+    <record id="view_sisb_nomina_resumen" model="ir.ui.view">
+      <field name="name">sisb.nomina.resumen.form</field>
+      <field name="model">sisb.nomina.resumen</field>
+      <field name="type">form</field>
+      <field name="arch" type="xml">
+        <form>
+          <field name="month" />
+          <button name="print_results" string="Print" type="object" icon="gtk-print" />
+        </form>
+      </field>
+    </record>
+    <!-- Action -->
+    <record id="action_wizard_sisb_nomina_resumen" model="ir.actions.act_window">
+      <field name="name">Resumen mensual de nomina</field>
+      <field name="type">ir.actions.act_window</field>
+      <field name="res_model">sisb.nomina.resumen</field>
+      <field name="view_type">form</field>
+      <field name="view_mode">form</field>
+      <field name="target">new</field>
+    </record>
+    <!-- Report -->
+    <report
+            auto="False"
+            id="report_nomina_resumen"
+            model="sisb.nomina.resumen"
+            name="nomina.resumen"
+            rml=""
+            string="Reporte de nomina"
+    />
+    <!-- Menu -->
+    <menuitem id="menu_wizard_sisb_nomina_resumen" action="action_wizard_sisb_nomina_resumen" 
+      parent="sisb_nomina.menu_hr_payroll_reporting" name="Resumen mensual de nomina" groups="group_sisb_nomina_resumen" />
+  </data>
+</openerp>