Example #1
0
 def create_source_xls(self, cr, uid, ids, data, context=None):
     if not context:
         context = {}
     parser_instance = self.parser(cr, uid, self.name2, context)
     self.parser_instance = parser_instance
     self.context = context
     objs = self.getObjects(cr, uid, ids, context)
     parser_instance.set_context(objs, data, ids, 'xls')
     objs = parser_instance.localcontext['objects']
     n = cStringIO.StringIO()
     wb = xlwt.Workbook(encoding='utf-8')
     _p = AttrDict(parser_instance.localcontext)
     _xs = self.xls_styles
     self.xls_headers = {
         'standard': '',
     }
     report_date = datetime_field.context_timestamp(cr, uid, datetime.now(),
                                                    context)
     report_date = report_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)
     self.xls_footers = {
         'standard':
         ('&L&%(font_size)s&%(font_style)s' + report_date +
          '&R&%(font_size)s&%(font_style)s&P / &N') % self.hf_params,
     }
     self.generate_xls_report(_p, _xs, data, objs, wb)
     wb.save(n)
     n.seek(0)
     return (n.read(), 'xls')
    def create_xlsx_report(self, ids, data, report):
        _logger.error("in xlsx report with env: %s", str(self.env.cr))
        self.parser_instance = self.parser(self.env.cr, self.env.uid,
                                           self.name2, self.env.context)
        objs = self.getObjects(self.env.cr, self.env.uid, ids,
                               self.env.context)
        self.parser_instance.set_context(objs, data, ids, 'xlsx')
        _p = AttrDict(self.parser_instance.localcontext)
        objs = self.parser_instance.localcontext['objects']
        file_data = StringIO()
        workbook = Workbook(file_data)
        _xs = self.xls_styles
        self.xls_headers = {
            'standard': '',
        }
        report_date = datetime_field.context_timestamp(self.cr, self.uid,
                                                       datetime.now(),
                                                       self.context)
        report_date = report_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)
        self.xls_footers = {'standard': report_date}
        self.generate_xls_report(_p, _xs, data, objs, workbook)
        workbook.close()
        file_data.seek(0)

        return (file_data.read(), 'xlsx')
Example #3
0
        def formatLang(value, 
                        digits=None, 
                        date=False, 
                        date_time=False, 
                        grouping=True, 
                        monetary=False, 
                        dp=False, 
                        currency_obj=False):
            """
                Assuming 'Account' decimal.precision=3:
                    formatLang(value) -> digits=2 (default)
                    formatLang(value, digits=4) -> digits=4
                    formatLang(value, dp='Account') -> digits=3
                    formatLang(value, digits=5, dp='Account') -> digits=5
            """
            if digits is None:
                if dp:
                    digits = get_digits(dp=dp)
                else:
                    digits = get_digits(value)

            if isinstance(value, (str, unicode)) and not value:
                return ''

            if not self.lang_dict_called:
                _get_lang_dict()
                self.lang_dict_called = True

            if date or date_time:
                if not str(value):
                    return ''

                date_format = self.lang_dict['date_format']
                parse_format = DEFAULT_SERVER_DATE_FORMAT
                if date_time:
                    value = value.split('.')[0]
                    date_format = date_format + " " + self.lang_dict['time_format']
                    parse_format = DEFAULT_SERVER_DATETIME_FORMAT
                if isinstance(value, basestring):
                    # FIXME: the trimming is probably unreliable if format includes day/month names
                    #        and those would need to be translated anyway.
                    date = datetime.strptime(value[:get_date_length(parse_format)], parse_format)
                elif isinstance(value, time.struct_time):
                    date = datetime(*value[:6])
                else:
                    date = datetime(*value.timetuple()[:6])
                if date_time:
                    # Convert datetime values to the expected client/context timezone
                    date = datetime_field.context_timestamp(cr, uid,
                                                            timestamp=date,
                                                            context=self.localcontext)
                return date.strftime(date_format.encode('utf-8'))

            res = self.lang_dict['lang_obj'].format('%.' + str(digits) + 'f', value, grouping=grouping, monetary=monetary)
            if currency_obj:
                if currency_obj.position == 'after':
                    res='%s %s'%(res,currency_obj.symbol)
                elif currency_obj and currency_obj.position == 'before':
                    res='%s %s'%(currency_obj.symbol, res)
            return res
 def create_source_xls(self, cr, uid, ids, data, context=None):
     if not context:
         context = {}
     parser_instance = self.parser(cr, uid, self.name2, context)
     self.parser_instance = parser_instance
     self.context = context
     objs = self.getObjects(cr, uid, ids, context)
     parser_instance.set_context(objs, data, ids, 'xls')
     objs = parser_instance.localcontext['objects']
     n = cStringIO.StringIO()
     # prevent style make error
     # http://stackoverflow.com/questions/17130516/xlwt-set-style-making-error-more-than-4094-xfs-styles
     wb = xlwt.Workbook(encoding='utf-8', style_compression=2)
     _p = AttrDict(parser_instance.localcontext)
     _xs = self.xls_styles
     self.xls_headers = {
         'standard': '',
     }
     report_date = datetime_field.context_timestamp(
         cr, uid, datetime.now(), context)
     report_date = report_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)
     self.xls_footers = {
         'standard': (
             '&L&%(font_size)s&%(font_style)s' + report_date +
             '&R&%(font_size)s&%(font_style)s&P / &N'
             ) % self.hf_params,
     }
     self.generate_xls_report(_p, _xs, data, objs, wb)
     wb.save(n)
     n.seek(0)
     return (n.read(), 'xls')
Example #5
0
 def set_context(self, objects, data, ids, report_type=None):
     report_date = datetime_field.context_timestamp(
         self.cr, self.uid, datetime.now(),
         self.context).strftime(DEFAULT_SERVER_DATETIME_FORMAT)
     self.localcontext.update({'report_date': report_date})
     super(report_financial_parser,
           self).set_context(objects, data, ids, report_type)
Example #6
0
    def evalStr(self, value):
        if self.evalContext is None:
            t = self.dt and util.strToTime(self.dt) or datetime.now()
            t = datetime_field.context_timestamp(self.cr,
                                                 self.uid,
                                                 timestamp=t,
                                                 context={"tz": self.tz})
            sequences = {
                'year': '%Y',
                'month': '%m',
                'day': '%d',
                'y': '%y',
                'doy': '%j',
                'woy': '%W',
                'weekday': '%w',
                'h24': '%H',
                'h12': '%I',
                'min': '%M',
                'sec': '%S'
            }
            self.evalContext = {
                key: t.strftime(sequence)
                for key, sequence in sequences.iteritems()
            }

        if value:
            return value % self.evalContext

        return value
Example #7
0
    def set_context(self, objects, data, ids, report_type=None):
        cr = self.cr
        uid = self.uid
        context = self.context

        reports = []
        reports.append(self.get_wo(data)[0])
        reports.append(self.get_so(data)[0])

        if not reports:
            raise osv.except_osv(('Warning'),
                                 ('Data Report Tidak Ditemukan !'))

        report_date = datetime_field.context_timestamp(
            cr, uid, datetime.now(),
            context).strftime(DEFAULT_SERVER_DATETIME_FORMAT)

        report_info = _('')

        self.localcontext.update({
            'report_info': report_info,
            'report_date': report_date,
            'reports': reports,
        })
        objects = False
        super(dym_report_penjualan_sowo_print,
              self).set_context(objects, data, ids, report_type)
Example #8
0
 def create_source_xls(self, cr, uid, ids, data, context=None):
     if not context:
         context = {}
     parser_instance = self.parser(cr, uid, self.name2, context)
     self.parser_instance = parser_instance
     self.context = context
     objs = self.getObjects(cr, uid, ids, context)
     parser_instance.set_context(objs, data, ids, 'xls')
     objs = parser_instance.localcontext['objects']
     n = cStringIO.StringIO()
     # prevent style make error
     # http://stackoverflow.com/questions/17130516/xlwt-set-style-making-error-more-than-4094-xfs-styles
     wb = xlwt.Workbook(encoding='utf-8', style_compression=2)
     _p = AttrDict(parser_instance.localcontext)
     _xs = self.xls_styles
     self.xls_headers = {
         'standard': '',
     }
     report_date = datetime_field.context_timestamp(cr, uid, datetime.now(),
                                                    context)
     report_date = report_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)
     self.xls_footers = {
         'standard':
         ('&L&%(font_size)s&%(font_style)s' + report_date +
          '&R&%(font_size)s&%(font_style)s&P / &N') % self.hf_params,
     }
     self.generate_xls_report(_p, _xs, data, objs, wb)
     wb.save(n)
     n.seek(0)
     return (n.read(), 'xls')
 def _convert_datetime_to_client_timezone(self, date_time):
     date_time = dt.strptime(date_time, DTF)
     date_time_new = datetime.context_timestamp(
         self.env.cr, self.env.uid, date_time,
         context=self.env.context)
     date_time_new_str = date_time_new.strftime(DTF)
     return date_time_new_str
Example #10
0
 def create_source_xls(self, cr, uid, ids, data, context=None):
     if not context:
         context = {}
     parser_instance = self.parser(cr, uid, self.name2, context)
     self.parser_instance = parser_instance
     self.context = context
     objs = self.getObjects(cr, uid, ids, context)
     parser_instance.set_context(objs, data, ids, 'xls')
     objs = parser_instance.localcontext['objects']
     n = cStringIO.StringIO()
     wb = xlwt.Workbook(encoding='utf-8')
     _p = AttrDict(parser_instance.localcontext)
     _xs = self.xls_styles
     self.xls_headers = {
         'standard': '',
     }
     report_date = datetime_field.context_timestamp(
         cr, uid, datetime.now(), context)
     report_date = report_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)
     self.xls_footers = {
         'standard': (
             '&L&%(font_size)s&%(font_style)s' + report_date +
             '&R&%(font_size)s&%(font_style)s&P / &N'
             ) % self.hf_params,
     }
     self.generate_xls_report(_p, _xs, data, objs, wb)
     wb.save(n)
     n.seek(0)
     return (n.read(), 'xls')
 def _convert_datetime_to_client_timezone(self, date_time):
     date_time = dt.strptime(date_time, DTF)
     date_time_new = datetime.context_timestamp(self.env.cr,
                                                self.env.uid,
                                                date_time,
                                                context=self.env.context)
     date_time_new_str = date_time_new.strftime(DTF)
     return date_time_new_str
Example #12
0
def strToLocalTimeFormat(cr, uid, str_time, format, context):
    # Convert datetime values to the expected client/context timezone
    timestamp = util.strToTime(str_time)
    converted = datetime_field.context_timestamp(cr,
                                                 uid,
                                                 timestamp=timestamp,
                                                 context=context)
    return util.dateFormat(converted, format)
Example #13
0
def strToLocalDateStr(cr, uid, str_time, context):
    # Convert datetime values to the expected client/context timezone
    timestamp = util.strToTime(str_time)
    converted = datetime_field.context_timestamp(cr,
                                                 uid,
                                                 timestamp=timestamp,
                                                 context=context)
    return util.timeToDateStr(converted)
    def set_context(self, objects, data, ids, report_type=None):
        # _logger.warn('set_context, objects = %s, data = %s, ids = %s',
        #              objects, data, ids)
        data = objects[0]
        cr = data._cr
        uid = data._uid
        context = data._context
        date_balance = data['date_balance']
        journal_ids = [x.id for x in data['journal_ids']]
        if not journal_ids:
            raise Warning(_('No Financial Journals selected!'))
        cr.execute(
            "SELECT s.name AS s_name, s.date AS s_date, j.code AS j_code, "
            "s.balance_end_real AS s_balance, "
            "coalesce(jcu.id,ccu.id) as j_curr_id "
            "FROM account_bank_statement s "
            "INNER JOIN account_journal j ON s.journal_id = j.id "
            "INNER JOIN res_company co ON j.company_id = co.id "
            "LEFT OUTER JOIN res_currency jcu ON j.currency = jcu.id "
            "LEFT OUTER JOIN res_currency ccu ON co.currency_id = ccu.id "
            "INNER JOIN "
            "  (SELECT journal_id, max(date) AS max_date "
            "   FROM account_bank_statement "
            "   WHERE date <= %s GROUP BY journal_id) d "
            "   ON (s.journal_id = d.journal_id AND s.date = d.max_date) "
            "WHERE s.journal_id in %s "
            "ORDER BY j_curr_id, j.code", (date_balance, tuple(journal_ids)))
        lines = cr.dictfetchall()
        [
            x.update(
                {'currency': data.env['res.currency'].browse(x['j_curr_id'])})
            for x in lines
        ]
        currencies = list(set([x['currency'] for x in lines]))
        totals = []
        for currency in currencies:
            lines_currency = filter(lambda x: x['currency'] == currency, lines)
            total_amount = reduce(lambda x, y: x + y,
                                  [x['s_balance'] for x in lines_currency])
            totals.append({
                'currency': currency,
                'total_amount': total_amount,
            })
        if not lines:
            raise Warning(_('No records found for your selection!'))

        report_date = datetime_field.context_timestamp(
            cr, uid, datetime.now(),
            context).strftime(DEFAULT_SERVER_DATETIME_FORMAT)

        self.localcontext.update({
            'lines': lines,
            'totals': totals,
            'date_balance': date_balance,
            'report_date': report_date,
        })
        super(bank_statement_balance_report,
              self).set_context(objects, data, ids, report_type=report_type)
Example #15
0
    def formatLang(self, value, digits=None, date=False, date_time=False, grouping=True, monetary=False, dp=False,
                   currency_obj=False):
        """
            Assuming 'Account' decimal.precision=3:
                formatLang(value) -> digits=2 (default)
                formatLang(value, digits=4) -> digits=4
                formatLang(value, dp='Account') -> digits=3
                formatLang(value, digits=5, dp='Account') -> digits=5
        """
        if not value:
            return ''
        if digits is None:
            if dp:
                digits = self.get_digits(dp=dp)
            else:
                digits = self.get_digits(value)

        if isinstance(value, (str, unicode)) and not value:
            return ''

        if not self.lang_dict_called:
            self._get_lang_dict()
            self.lang_dict_called = True

        if date or date_time:
            if not str(value):
                return ''

            date_format = self.lang_dict['date_format']
            parse_format = DEFAULT_SERVER_DATE_FORMAT
            if date_time:
                value = value.split('.')[0]
                date_format = date_format + " " + self.lang_dict['time_format']
                parse_format = DEFAULT_SERVER_DATETIME_FORMAT
            if isinstance(value, basestring):
                # FIXME: the trimming is probably unreliable if format includes day/month names
                #        and those would need to be translated anyway.
                date = datetime.strptime(value[:get_date_length(parse_format)], parse_format)
            elif isinstance(value, time.struct_time):
                date = datetime(*value[:6])
            else:
                date = datetime(*value.timetuple()[:6])
            if date_time:
                # Convert datetime values to the expected client/context timezone
                date = datetime_field.context_timestamp(self.cr, self.uid,
                                                        timestamp=date,
                                                        context=self.localcontext)
            return date.strftime(date_format.encode('utf-8'))

        res = self.lang_dict['lang_obj'].format('%.' + str(digits) + 'f', value, grouping=grouping, monetary=monetary)
        if currency_obj:
            if currency_obj.position == 'after':
                res = '%s %s' % (res, currency_obj.symbol)
            elif currency_obj and currency_obj.position == 'before':
                res = '%s %s' % (currency_obj.symbol, res)
        return res
Example #16
0
    def formatLang(
        self,
        value,
        digits=None,
        date=False,
        date_time=False,
        grouping=True,
        monetary=False,
        dp=False,
        currency_obj=False,
    ):
        if digits is None:
            if dp:
                digits = self.get_digits(dp=dp)
            elif currency_obj:
                digits = currency_obj.decimal_places
            else:
                digits = self.get_digits(value)

        if isinstance(value, (str, unicode)) and not value:
            return ""

        if not self.lang_dict_called:
            self._get_lang_dict()
            self.lang_dict_called = True

        if date or date_time:
            if not value:
                return ""

            date_format = self.lang_dict["date_format"]
            parse_format = DEFAULT_SERVER_DATE_FORMAT
            if date_time:
                value = value.split(".")[0]
                date_format = date_format + " " + self.lang_dict["time_format"]
                parse_format = DEFAULT_SERVER_DATETIME_FORMAT
            if isinstance(value, basestring):
                # FIXME: the trimming is probably unreliable if format includes day/month names
                #        and those would need to be translated anyway.
                date = datetime.strptime(value[: get_date_length(parse_format)], parse_format)
            elif isinstance(value, time.struct_time):
                date = datetime(*value[:6])
            else:
                date = datetime(*value.timetuple()[:6])
            if date_time:
                # Convert datetime values to the expected client/context timezone
                date = datetime_field.context_timestamp(self.cr, self.uid, timestamp=date, context=self.localcontext)
            return date.strftime(date_format.encode("utf-8"))

        res = self.lang_dict["lang_obj"].format("%." + str(digits) + "f", value, grouping=grouping, monetary=monetary)
        if currency_obj:
            if currency_obj.position == "after":
                res = u"%s\N{NO-BREAK SPACE}%s" % (res, currency_obj.symbol)
            elif currency_obj and currency_obj.position == "before":
                res = u"%s\N{NO-BREAK SPACE}%s" % (currency_obj.symbol, res)
        return res
Example #17
0
def strDateToUTCTimeStr(cr, uid, str_date, context):
    timestamp = util.strToTime(util.dateToTimeStr(str_date))
    converted = datetime_field.context_timestamp(cr,
                                                 uid,
                                                 timestamp=timestamp,
                                                 context=context)
    converted = util.strToTime(util.timeToStr(converted))
    diff = (converted - timestamp)
    utcTimestamp = timestamp - diff
    return util.timeToStr(utcTimestamp)
 def set_context(self, objects, data, ids, report_type=None):
     report_date = datetime_field.context_timestamp(self.cr, self.uid,
         datetime.now(), self.context).strftime(DEFAULT_SERVER_DATETIME_FORMAT)
     self.localcontext.update({
         'period_code': data['form']['period_code'],
         'partner_wo_vat': data['form']['partner_wo_vat'],
         'amtsum': data['form']['amtsum'],
         'report_date': report_date,
         'clientlist': data['form']['clientlist'],
     })
     super(vat_intra_print, self).set_context(objects, data, ids)
Example #19
0
    def _convert_timezone(self, date, format_string='%Y-%m-%d %H:%M:%S'):
        import logging
        logging.info(date)
        date = datetime.strptime(date, format_string)

        new_date = datetime_field.context_timestamp(self._cr, self._uid, timestamp=date, context=self._context)
        new_date = datetime.strptime(new_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT), DEFAULT_SERVER_DATETIME_FORMAT)
        duration = new_date - date
        seconds = duration.total_seconds()
        hours = seconds // 3600

        date = date + relativedelta(hours=hours)
        return date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)
Example #20
0
    def _convert_timezone(self, cr, uid, date, context):
        date = datetime.strptime(date, DEFAULT_SERVER_DATETIME_FORMAT)

        new_date = datetime_field.context_timestamp(cr, uid,
                                                    timestamp=date,
                                                    context=context)
        new_date = datetime.strptime(new_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT), DEFAULT_SERVER_DATETIME_FORMAT)

        duration = new_date - date
        seconds = duration.total_seconds()
        hours = seconds // 3600

        date = date + relativedelta(hours=-hours)
        return date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)
 def set_context(self, objects, data, ids, report_type=None):
     report_date = datetime_field.context_timestamp(
         self.cr, self.uid, datetime.now(),
         self.context).strftime(DEFAULT_SERVER_DATETIME_FORMAT)
     self.localcontext.update({
         'report_date':
         report_date,
         'period_start':
         data['form']['period_start'],
         'period_end':
         data['form']['period_end'],
         'grid_data_list':
         data['form']['grid_data_list']
     })
     super(vat_declaration_print, self).set_context(objects, data, ids)
Example #22
0
    def remove_7_hours(self, cr, uid, date, context=None):
        from openerp.osv.fields import datetime as datetime_field
        date = datetime.strptime(date, DEFAULT_SERVER_DATETIME_FORMAT)

        new_date = datetime_field.context_timestamp(cr, uid,
                                                    timestamp=date,
                                                    context=context)
        new_date = datetime.strptime(new_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT), DEFAULT_SERVER_DATETIME_FORMAT)

        duration = new_date - date
        seconds = duration.total_seconds()
        hours = seconds // 3600

        date = date + relativedelta(hours=-hours)
        return date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)
 def set_context(self, objects, data, ids, report_type=None):
     client_datas = data['client_datas']
     report_date = datetime_field.context_timestamp(
         self.cr, self.uid, datetime.now(),
         self.context).strftime(DEFAULT_SERVER_DATETIME_FORMAT)
     self.localcontext.update({
         'year':
         data['year'],
         'sum_turnover':
         client_datas[-1]['sum_turnover'],
         'sum_tax':
         client_datas[-1]['sum_tax'],
         'client_list':
         client_datas,
         'report_date':
         report_date,
     })
     super(partner_vat_listing_print, self).set_context(objects, data, ids)
Example #24
0
    def _convert_timezone(self, cr, uid, date, context):
        from datetime import datetime
        from openerp.tools import DEFAULT_SERVER_DATETIME_FORMAT, DEFAULT_SERVER_DATE_FORMAT
        from openerp.osv.fields import datetime as datetime_field
        from dateutil.relativedelta import relativedelta

        date = datetime.strptime(date, DEFAULT_SERVER_DATETIME_FORMAT)
        new_date = datetime_field.context_timestamp(cr, uid,
                                                    timestamp=date,
                                                    context=context)
        new_date = datetime.strptime(new_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT), DEFAULT_SERVER_DATETIME_FORMAT)

        duration = new_date - date
        seconds = duration.total_seconds()
        hours = seconds // 3600

        date = date + relativedelta(hours=-hours)
        return date.date().strftime(DEFAULT_SERVER_DATE_FORMAT)
Example #25
0
 def generate_info_general_report(self, data, get_account_lines, workbook):
     self.sheet.write("A5", _(u"Target Moves:"), self.format_title_table)
     if data['target_move'] == 'all':
         self.sheet.write("A6", _(u"All Entries"), self.format_default_info)
     elif data['target_move'] == 'posted':
         self.sheet.write("A6", _(u"All Posted Entries"),
                          self.format_default_info)
     if data['date_from']:
         self.sheet.write("B5", _(u"Date from:"), self.format_title_table)
         self.sheet.write("B6", u"%s" % data['date_from'],
                          self.format_default_info)
     if data['date_to']:
         self.sheet.write("C5", _(u"Date to:"), self.format_title_table)
         self.sheet.write("C6", u"%s" % data['date_to'],
                          self.format_default_info)
     self.sheet.write("D5", _(u"Print Date:"), self.format_title_table)
     self.sheet.write(
         "D6", u"%s" % datetime_field.context_timestamp(
             self.env.cr, self.env.uid, datetime.now(),
             self.context).strftime("%d/%m/%Y %H:%M") or '',
         self.format_default_info)
Example #26
0
    def set_context(self, objects, data, ids, report_type=None):
        cr = self.cr
        uid = self.uid
        context = self.context
        branch_ids = data['branch_ids']
        trx_start_date = data['trx_start_date']
        trx_end_date = data['trx_end_date']

        title_prefix = ''
        title_short_prefix = ''

        report_service_rate = {
            'product_codes': [],
            'product_supplied': [],
            'type': 'payable',
            'title': '',
            'title_short': 'Laporan Service Rate'
        }

        query_start = "SELECT CONCAT(cast(l.id as text),'-',cast(obj.id as text),'-','WO') as id_ai, " \
            "COALESCE(b.name,'') as branch_id, " \
            "'WO' as tipe_dok, " \
            "obj.date as date, " \
            "obj.name as number, " \
            "COALESCE(t.name,'') as product_code, " \
            "COALESCE(t.description,'') as description, " \
            "COALESCE(p.default_code,'') as default_code, " \
            "COALESCE(l.price_unit,0) as het, " \
            "COALESCE(l.product_qty,0)+COALESCE(l.lost_order_qty,0) as qty_demand, " \
            "COALESCE(l.product_qty,0) as qty_supply, " \
            "COALESCE(l.lost_order_qty,0) as qty_backorder, " \
            "(COALESCE(l.price_unit,0)-(COALESCE(l.discount,0)/COALESCE(l.product_qty,0))-(COALESCE(l.discount_program,0)/COALESCE(l.product_qty,0))-COALESCE(s_db.discount_bundle,0)) / 1.1 as net_jual, " \
            "((COALESCE(l.price_unit,0)-(COALESCE(l.discount,0)/COALESCE(l.product_qty,0))-(COALESCE(l.discount_program,0)/COALESCE(l.product_qty,0))-COALESCE(s_db.discount_bundle,0)) / 1.1)*(COALESCE(l.product_qty,0)+COALESCE(l.lost_order_qty,0)) as jumlah_demand, " \
            "((COALESCE(l.price_unit,0)-(COALESCE(l.discount,0)/COALESCE(l.product_qty,0))-(COALESCE(l.discount_program,0)/COALESCE(l.product_qty,0))-COALESCE(s_db.discount_bundle,0)) / 1.1)*(COALESCE(l.product_qty,0)) as jumlah_supply, " \
            "((COALESCE(l.price_unit,0)-(COALESCE(l.discount,0)/COALESCE(l.product_qty,0))-(COALESCE(l.discount_program,0)/COALESCE(l.product_qty,0))-COALESCE(s_db.discount_bundle,0)) / 1.1)*(COALESCE(l.lost_order_qty,0)) as jumlah_order " \
            "FROM " \
            "dym_work_order_line l " \
            "LEFT JOIN dym_work_order obj ON l.work_order_id = obj.id " \
            "LEFT JOIN dym_branch b ON b.id = obj.branch_id " \
            "LEFT JOIN product_product p ON p.id = l.product_id " \
            "LEFT JOIN product_template t ON t.id = p.product_tmpl_id " \
            "LEFT JOIN (select db.wo_line_id, sum(db.diskon) as discount_bundle from dym_work_order_bundle db group by db.wo_line_id) s_db ON s_db.wo_line_id = l.id " \
            "where 1=1 and obj.state in ('open','done') and l.categ_id = 'Sparepart' "
        query_start2 = "SELECT CONCAT(cast(l.id as text),'-',cast(obj.id as text),'-','SO') as id_ai, " \
            "COALESCE(b.name,'') as branch_id, " \
            "'SO' as tipe_dok, " \
            "obj.date_order as date, " \
            "obj.name as number, " \
            "COALESCE(t.name,'') as product_code, " \
            "COALESCE(t.description,'') as description, " \
            "COALESCE(p.default_code,'') as default_code, " \
            "COALESCE(l.price_unit,0) as het, " \
            "COALESCE(l.product_uom_qty,0)+COALESCE(l.lost_order_qty,0) as qty_demand, " \
            "COALESCE(l.product_uom_qty,0) as qty_supply, " \
            "COALESCE(l.lost_order_qty,0) as qty_backorder, " \
            "(COALESCE(l.price_unit,0)*(1-COALESCE(l.discount,0)/100)-(COALESCE(l.discount_program,0)/COALESCE(l.product_uom_qty,0))-(COALESCE(l.discount_cash,0)/COALESCE(l.product_uom_qty,0))-(COALESCE(l.discount_lain,0)/COALESCE(l.product_uom_qty,0))) / 1.1 as net_jual, " \
            "((COALESCE(l.price_unit,0)*(1-COALESCE(l.discount,0)/100)-(COALESCE(l.discount_program,0)/COALESCE(l.product_uom_qty,0))-(COALESCE(l.discount_cash,0)/COALESCE(l.product_uom_qty,0))-(COALESCE(l.discount_lain,0)/COALESCE(l.product_uom_qty,0))) / 1.1)*(COALESCE(l.product_uom_qty,0)+COALESCE(l.lost_order_qty,0)) as jumlah_demand, " \
            "((COALESCE(l.price_unit,0)*(1-COALESCE(l.discount,0)/100)-(COALESCE(l.discount_program,0)/COALESCE(l.product_uom_qty,0))-(COALESCE(l.discount_cash,0)/COALESCE(l.product_uom_qty,0))-(COALESCE(l.discount_lain,0)/COALESCE(l.product_uom_qty,0))) / 1.1)*(COALESCE(l.product_uom_qty,0)) as jumlah_supply, " \
            "((COALESCE(l.price_unit,0)*(1-COALESCE(l.discount,0)/100)-(COALESCE(l.discount_program,0)/COALESCE(l.product_uom_qty,0))-(COALESCE(l.discount_cash,0)/COALESCE(l.product_uom_qty,0))-(COALESCE(l.discount_lain,0)/COALESCE(l.product_uom_qty,0))) / 1.1)*(COALESCE(l.lost_order_qty,0)) as jumlah_order " \
            "FROM " \
            "sale_order_line l " \
            "LEFT JOIN sale_order obj ON l.order_id = obj.id " \
            "LEFT JOIN dym_branch b ON b.id = obj.branch_id " \
            "LEFT JOIN product_product p ON p.id = l.product_id " \
            "LEFT JOIN product_template t ON t.id = p.product_tmpl_id " \
            "where 1=1 and obj.state in ('progress','manual','shipping_except','invoice_except','done') "
        move_selection = ""
        report_info = _('')
        move_selection += ""

        query_end = ""
        query_end2 = ""
        if trx_start_date:
            query_end += " AND obj.date >= '%s'" % str(trx_start_date)
            query_end2 += " AND obj.date_order >= '%s'" % str(trx_start_date)
        if trx_end_date:
            query_end += " AND obj.date <= '%s 23:59:59'" % str(trx_end_date)
            query_end2 += " AND obj.date_order <= '%s 23:59:59'" % str(
                trx_end_date)
        if branch_ids:
            query_end += " AND obj.branch_id in %s" % str(
                tuple(branch_ids)).replace(',)', ')')
            query_end2 += " AND obj.branch_id in %s" % str(
                tuple(branch_ids)).replace(',)', ')')
        reports = [report_service_rate]

        # query_order = "order by cabang"
        query_order = ""
        for report in reports:
            cr.execute(query_start + query_end + query_order + " UNION ALL " +
                       query_start2 + query_end2 + query_order)
            all_lines = cr.dictfetchall()
            id_ai = []
            if all_lines:
                p_map = map(
                    lambda x: {
                        'no':
                        0,
                        'id_ai':
                        x['id_ai'] if x['id_ai'] != None else 0,
                        'branch_id':
                        str(x['branch_id'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['branch_id'] != None else '',
                        'tipe_dok':
                        str(x['tipe_dok'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['tipe_dok'] != None else '',
                        'date':
                        str(x['date']) if x['date'] != None else '',
                        'number':
                        str(x['number'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['number'] != None else '',
                        'product_code':
                        str(x['product_code'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['product_code'] != None else '',
                        'product_name':
                        str(x['description']) if 'description' in x and x[
                            'description'] != None else str(x['default_code'])
                        if 'default_code' in x and x['default_code'] != None
                        else '',
                        'het':
                        x['het'],
                        'qty_demand':
                        x['qty_demand'],
                        'qty_supply':
                        x['qty_supply'],
                        'qty_backorder':
                        x['qty_backorder'],
                        'net_jual':
                        x['net_jual'],
                        'jumlah_demand':
                        x['jumlah_demand'],
                        'jumlah_supply':
                        x['jumlah_supply'],
                        'jumlah_order':
                        x['jumlah_order'],
                    }, all_lines)
                for p in p_map:
                    if p['id_ai'] not in map(lambda x: x.get('id_ai', None),
                                             id_ai):
                        records = filter(lambda x: x['id_ai'] == p['id_ai'],
                                         all_lines)
                        p.update({'lines': records})
                        id_ai.append(p)
                        if records[0]['product_code'] not in report[
                                'product_codes']:
                            report['product_codes'].append(
                                records[0]['product_code'])
                        if records[0]['product_code'] not in report[
                                'product_supplied'] and records[0][
                                    'qty_supply'] > 0:
                            report['product_supplied'].append(
                                records[0]['product_code'])
                report.update({'id_ai': id_ai})
                # report.update({'id_ai': p_map})

        reports = filter(lambda x: x.get('id_ai'), reports)

        if not reports:
            raise osv.except_osv(
                _('Data Not Found!'),
                _('Tidak ditemukan data dari hasil filter report service rate.'
                  ))

        report_date = datetime_field.context_timestamp(
            cr, uid, datetime.now(),
            context).strftime(DEFAULT_SERVER_DATETIME_FORMAT)

        self.localcontext.update({
            'report_info': report_info,
            'report_date': report_date,
            'reports': reports,
        })
        super(dym_report_service_rate_print,
              self).set_context(objects, data, ids, report_type)
Example #27
0
    def set_context(self, objects, data, ids, report_type=None):
        cr = self.cr
        uid = self.uid
        context = self.context
        branch_ids = data['branch_ids']
        date_from = data['date_from']
        date_to = data['date_to']
        state = data['state']
        jenis_penjualan = data['jenis_penjualan']

        title_prefix = ''
        title_short_prefix = ''

        report_ar = {
            'type': 'receivable',
            'title': title_prefix + _(''),
            'title_short': title_short_prefix + ', ' + _('Dealer_sale_order')
        }



        query_start = "select a.id as p_id, " \
                      "a.name as p_name, " \
                      "a.state as p_state, " \
                      "a.date_order as p_date_order, " \
                      "c.name as p_konsumen, " \
                      "z.name as p_salesman, " \
                      "e.name as p_fincoy, " \
                      "a.customer_dp as p_customer_dp, " \
                      "ss.name as p_sales_source, " \
                      "b.cicilan as p_cicilan, " \
                      "f.name as p_location_id, " \
                      "g.name_template as p_kode_product,  " \
                      "j.code as p_warna  , " \
                      "k.name as p_mesin,  " \
                      "k.chassis_no as p_rangka,  " \
                      "b.finco_tenor as p_tenor,  " \
                      "b.is_bbn as p_is_bbn,  " \
                      "l.name as p_nama_stnk,  " \
                      "b.uang_muka as p_uang_muka,  " \
                      "b.discount_po as p_pot_pelanggan,  " \
                      "b.price_unit as p_harga,  " \
                      "b.discount_total as p_total_discount,  " \
                      "b.price_bbn as p_harga_bbn,  " \
                      "m.name as p_cabang,  " \
                      "c.default_code as p_default_code  " \
                      "from dealer_sale_order a " \
                      "left join dealer_sale_order_line b ON a.id = b.dealer_sale_order_line_id " \
                      "left join res_partner c ON c.id = a.partner_id " \
                      "left join hr_employee d ON d.id = a.employee_id " \
                      "left join res_partner z ON z.id = d.partner_id " \
                      "left join res_partner e ON e.id = a.finco_id " \
                      "left join stock_location f ON f.id = b.location_id " \
                      "left join sales_source ss ON ss.id = a.sales_source " \
                      "LEFT JOIN product_product g ON g.id = b.product_id " \
                      "LEFT JOIN product_template h ON h.id = g.product_tmpl_id " \
                      "LEFT JOIN product_attribute_value_product_product_rel i ON i.prod_id = b.product_id " \
                      "LEFT JOIN product_attribute_value j ON j.id = i.att_id " \
                      "LEFT JOIN stock_production_lot k ON k.id = b.lot_id " \
                      "left join res_partner l ON l.id = b.partner_stnk_id " \
                      "left join dym_branch m ON m.id = a.branch_id " \

        query_start += " where a.id is not null "

        move_selection = ""
        report_info = _('')
        move_selection += ""

        query_end = ""
        if branch_ids:
            query_end += " AND  a.branch_id in %s " % str(
                tuple(branch_ids)).replace(',)', ')')

        if state:
            query_end += " AND  a.state = '%s' " % state

        if date_from:
            query_end+= ' AND  a.date_order >= ' +"'"+ date_from + "'" + \
            'AND  a.date_order <= ' +"'"+ date_to + "'" + \
            ''
        if jenis_penjualan and jenis_penjualan == 'cash':
            query_end += " AND  a.finco_id is null "

        if jenis_penjualan and jenis_penjualan == 'kredit':
            query_end += " AND  a.finco_id is not null "

        query_order = "order by p_cabang,p_name "
        reports = [report_ar]

        for report in reports:
            cr.execute(query_start + query_end + query_order)
            all_lines = cr.dictfetchall()
            partners = []

            if all_lines:

                def lines_map(x):
                    x.update({'docname': x['p_name']})

                map(lines_map, all_lines)
                for cnt in range(len(all_lines) - 1):
                    if all_lines[cnt]['p_id'] != all_lines[cnt + 1]['p_id']:
                        all_lines[cnt]['draw_line'] = 1
                    else:
                        all_lines[cnt]['draw_line'] = 0
                all_lines[-1]['draw_line'] = 1

                p_map = map(
                    lambda x: {
                        'p_id': x['p_id'],
                        'p_name': str(x['p_name']),
                        'p_state': str(x['p_state']),
                        'p_date_order': str(x['p_date_order']),
                        'p_konsumen': str(x['p_konsumen']),
                        'p_salesman': str(x['p_salesman']),
                        'p_fincoy': str(x['p_fincoy']),
                        'p_sales_source': str(x['p_sales_source']),
                        'p_cicilan': str(x['p_cicilan']),
                        'p_location_id': str(x['p_location_id']),
                        'p_kode_product': str(x['p_kode_product']),
                        'p_warna': str(x['p_warna']),
                        'p_cabang': str(x['p_cabang']),
                        'p_mesin': str(x['p_mesin']),
                        'p_default_code': str(x['p_default_code']),
                        'p_rangka': str(x['p_rangka']),
                        'p_tenor': str(x['p_tenor']),
                        'p_is_bbn': str(x['p_is_bbn']),
                        'p_nama_stnk': str(x['p_nama_stnk']),
                        'p_uang_muka': str(x['p_uang_muka']),
                        'p_pot_pelanggan': str(x['p_pot_pelanggan']),
                        'p_harga': str(x['p_harga']),
                        'p_total_discount': str(x['p_total_discount']),
                        'p_harga_bbn': str(x['p_harga_bbn']),
                        'p_customer_dp': str(x['p_customer_dp'])
                    }, all_lines)
                for p in p_map:
                    if p['p_id'] not in map(lambda x: x.get('p_id', None),
                                            partners):
                        partners.append(p)
                        partner_lines = filter(
                            lambda x: x['p_id'] == p['p_id'], all_lines)
                        p.update({'lines': partner_lines})
                        p.update({'d': 1, 'c': 2, 'b': 3})
                report.update({'partners': partners})

        reports = filter(lambda x: x.get('partners'), reports)
        if not reports:
            raise orm.except_orm(_('No Data Available'),
                                 _('No records found for your selection!'))

        report_date = datetime_field.context_timestamp(
            cr, uid, datetime.now(),
            context).strftime(DEFAULT_SERVER_DATETIME_FORMAT)

        self.localcontext.update({
            'report_info': report_info,
            'report_date': report_date,
            'reports': reports,
        })
        objects = False
        super(dym_dealer_sale_order_report_print,
              self).set_context(objects, data, ids, report_type)
Example #28
0
    def set_context(self, objects, data, ids, report_type=None):
        cr = self.cr
        uid = self.uid
        context = self.context
        journal_id = data['journal_id']
        branch_id = data['branch_id']
        start_date = data['start_date']
        end_date = data['end_date']
        title_prefix = ''
        title_short_prefix = ''

        bank_balance = 0.0
        where_analytic = " 1=1 "
        if branch_id:
            branch_ids = self.pool.get('dym.branch').browse(
                cr, uid, branch_id[0]).ids
        else:
            branch_ids = self.pool.get('res.users').browse(cr, uid,
                                                           uid).branch_ids.ids
        where_account = " 1=1 "

        if journal_id:
            journals = self.pool.get('account.journal').browse(
                cr, uid, journal_id[0])
            account_ids = []
            sql_query = ''
            if branch_ids:
                analytic_branch_ids = self.pool.get(
                    'account.analytic.account').search(
                        cr, uid, [('segmen', '=', 3),
                                  ('branch_id', 'in', branch_ids),
                                  ('type', '=', 'normal'),
                                  ('state', 'not in', ('close', 'cancelled'))])
                analytic_cc_ids = self.pool.get(
                    'account.analytic.account').search(
                        cr, uid,
                        [('segmen', '=', 4), ('type', '=', 'normal'),
                         ('state', 'not in', ('close', 'cancelled')),
                         ('parent_id', 'child_of', analytic_branch_ids)])
                where_analytic = " l.analytic_account_id  in %s " % str(
                    tuple(analytic_cc_ids)).replace(',)', ')')
                sql_query = ' AND l.analytic_account_id in %s' % str(
                    tuple(analytic_cc_ids)).replace(',)', ')')
            for journal in journals:
                if journal.default_debit_account_id and journal.default_debit_account_id.id not in account_ids:
                    account_ids.append(journal.default_debit_account_id.id)
                if journal.default_credit_account_id and journal.default_credit_account_id.id not in account_ids:
                    account_ids.append(journal.default_credit_account_id.id)

            bal_cr_init = journal.default_credit_account_id.with_context(
                date_from=start_date,
                date_to=end_date,
                initial_bal=True,
                sql_query=sql_query).balance
            bal_db_init = journal.default_debit_account_id.with_context(
                date_from=start_date,
                date_to=end_date,
                initial_bal=True,
                sql_query=sql_query).balance
            bank_balance_init = bal_cr_init or bal_db_init

            where_account = " a.id  in %s " % str(tuple(account_ids)).replace(
                ',)', ')')

        title_short = 'LAPORAN Kas Besar'
        #if data['projection']:
        #     title_short = title_short + ' (PROYEKSI)'
        report_kas_besar = {
            'type': 'KasBesar',
            'title': '',
            'title_short': title_short_prefix + ', ' + _(title_short),
            #'saldo_awal': saldo_awal,
            #'saldo_awal_projection': saldo_awal_projection,
            'start_date': start_date,
            'end_date': end_date
        }

        where_start_date = " l.date >= '%s' " % start_date
        where_end_date = " l.date <= '%s' " % end_date
        where_start_datex = " '%s' " % start_date
        where_end_datex = " '%s' " % end_date

        days = {
            'Sun': 'Minggu',
            'Mon': 'Senin',
            'Tue': 'Selasa',
            'Wed': 'Rabu',
            'Thu': 'Kamis',
            'Fri': 'Jumat',
            'Sat': 'Sabtu'
        }

        query_kas_besar = """
            select x.day"date",sum(x.debit) debit,sum(x.credit) credit from (
                select day::date,0 debit,0 credit from generate_series(timestamp %s, %s, '1 day') day
                union all
                select date,sum(debit) debit,sum(credit) from account_move_line l
                left join account_account a on a.id = l.account_id 
                WHERE %s and %s and %s and %s 
                GROUP BY l.date) x GROUP BY x.day ORDER BY 1""" % (
            where_start_datex, where_end_datex, where_account,
            where_start_date, where_end_date, where_analytic)

        #print query_kas_besar

        move_selection = ""
        report_info = _('')
        move_selection += ""

        reports = [report_kas_besar]

        for report in reports:
            a = cr.execute(query_kas_besar)
            all_lines = cr.dictfetchall()

            move_lines = []
            if all_lines:
                p_map = map(
                    lambda x: {
                        'no': 0,
                        'hari': '',
                        'date': x['date'] if x['date'] != None else '',
                        'debit': x['debit'] if x['debit'] > 0 else 0.0,
                        'credit': x['credit'] if x['credit'] > 0 else 0.0,
                        'saldo_awal': 0.0,
                        'saldo_akhir': 0.0,
                        'lebih_setor': 0.0,
                        'kurang_setor': 0.0,
                    }, all_lines)

                for p in p_map:
                    if p['date'] not in map(lambda x: x.get('date', None),
                                            move_lines):
                        account_move_lines = filter(
                            lambda x: x['date'] == p['date'], all_lines)

                        bal_cr_init = journal.default_credit_account_id.with_context(
                            date_from=p['date'],
                            date_to=p['date'],
                            initial_bal=True,
                            sql_query=sql_query).balance
                        bal_db_init = journal.default_debit_account_id.with_context(
                            date_from=p['date'],
                            date_to=p['date'],
                            initial_bal=True,
                            sql_query=sql_query).balance
                        bank_balance_init = bal_cr_init or bal_db_init

                        bal_cr_init = journal.default_credit_account_id.with_context(
                            date_from=datetime.strptime(
                                p['date'], DSDF).replace(day=1).strftime(DSDF),
                            date_to=datetime.strptime(p['date'], '%Y-%m-%d') -
                            timedelta(days=1),
                            initial_bal=False,
                            sql_query=sql_query).balance
                        bal_db_init = journal.default_debit_account_id.with_context(
                            date_from=datetime.strptime(
                                p['date'], DSDF).replace(day=1).strftime(DSDF),
                            date_to=datetime.strptime(p['date'], '%Y-%m-%d') -
                            timedelta(days=1),
                            initial_bal=False,
                            sql_query=sql_query).balance
                        saldo_awal = bank_balance_init + (bal_cr_init -
                                                          bal_db_init)
                        saldo_akhir = saldo_awal + p['debit'] - p['credit']

                        if p['debit'] > saldo_akhir:
                            lebih_setor = p['debit'] - saldo_akhir
                        else:
                            lebih_setor = 0

                        if p['debit'] < saldo_akhir:
                            kurang_setor = saldo_akhir - p['debit']
                        else:
                            kurang_setor = 0

                        date_a = datetime.strptime(p['date'], '%Y-%m-%d')
                        hari = days[date_a.strftime('%a')]
                        p.update({'hari': hari})
                        p.update({
                            'date':
                            datetime.strptime(str(p['date']),
                                              '%Y-%m-%d').strftime('%m/%d/%Y')
                        })
                        p.update({'saldo_awal': saldo_awal})
                        p.update({'lebih_setor': lebih_setor})
                        p.update({'kurang_setor': kurang_setor})
                        p.update({'saldo_akhir': saldo_akhir})

                        move_lines.append(p)
                report.update({'move_lines': move_lines})

        reports = filter(lambda x: x.get('move_lines'), reports)

        if not reports:
            reports = [{
                'type':
                'KasBesar',
                'title':
                '',
                #'title_short': title_short_prefix + ', ' + _(' '.join(['LAPORAN Kas Besar','(PROYEKSI)' if data['projection'] else ''])),
                'saldo_awal':
                saldo_awal,
                'start_date':
                start_date,
                'end_date':
                end_date,
                'hari':
                hari,
                'move_lines': [{
                    'no': 0,
                    'debit': 0,
                    'credit': 0,
                    'lebih_setor': 0,
                    'kurang_setor': 0,
                }],
            }]
        report_date = datetime_field.context_timestamp(
            cr, uid, datetime.now(),
            context).strftime(DEFAULT_SERVER_DATETIME_FORMAT)

        self.localcontext.update({
            'report_info': report_info,
            'report_date': report_date,
            'reports': reports,
        })
        objects = False
        super(dym_kas_besar_report_print,
              self).set_context(objects, data, ids, report_type)
Example #29
0
    def set_context(self, objects, data, ids, report_type=None):
        cr = self.cr
        uid = self.uid
        context = self.context
        start_date = data['start_date']
        end_date = data['end_date']
        branch_ids = data['branch_ids']
        account_id = data['account_ids']
        journal_id = data['journal_ids']
        branch_status = False
        trx_start_date = data['trx_start_date']
        trx_end_date = data['trx_end_date']
        division = data['division']
        partner_ids = data['partner_ids']

        title_prefix = ''
        title_short_prefix = ''
        
        report_other_receivable = {
            'type': 'payable',
            'title': '',
            'title_short': title_short_prefix + ', ' + _('Laporan Other Receivable')}

        query_start = "SELECT av.id as id_ai, " \
            "COALESCE(b.name,'') as branch_id, " \
            "av.date as date, " \
            "av.amount as total, " \
            "av.name as memo, " \
            "av.reference as ref, " \
            "av.division as division, " \
            "av.name as name, " \
            "av.number as number, " \
            "av.state as state, " \
            "av.date_due as date_due, " \
            "rp.default_code as partner_code, " \
            "rp.name as partner_name, " \
            "a.code as account_code, " \
            "a.name as account_name, " \
            "j.name as journal_name " \
            "FROM " \
            "account_voucher av " \
            "LEFT JOIN dym_branch b ON av.branch_id = b.id " \
            "LEFT JOIN res_partner rp ON rp.id = av.partner_id " \
            "LEFT JOIN account_account a ON a.id = av.account_id " \
            "LEFT JOIN account_journal j ON j.id = av.journal_id " \
            "where 1=1 and av.state = 'posted' and j.type in ('sale','sale_refund') and av.type = 'sale' "
            
        move_selection = ""
        report_info = _('')
        move_selection += ""
        
        query_end=""
        if division :
            query_end +=" AND av.division = '%s'" % str(division)
        if trx_start_date :
            query_end +=" AND av.date >= '%s'" % str(trx_start_date)
        if trx_end_date :
            query_end +=" AND av.date <= '%s'" % str(trx_end_date)
        if start_date :
            query_end +=" AND av.date_due >= '%s'" % str(start_date)
        if end_date :
            query_end +=" AND av.date_due <= '%s'" % str(end_date)
        if partner_ids :
            query_end +=" AND av.partner_id in %s" % str(
                tuple(partner_ids)).replace(',)', ')')
        if branch_ids :
            query_end +=" AND av.branch_id in %s" % str(
                tuple(branch_ids)).replace(',)', ')')
        if account_id :
            query_end+=" AND av.account_id in %s" % str(
                tuple(account_id)).replace(',)', ')')
        if journal_id :
            query_end+=" AND av.journal_id in %s" % str(
                tuple(journal_id)).replace(',)', ')')
        reports = [report_other_receivable]
        
        query_order = ""
        for report in reports:
            cr.execute(query_start + query_end + query_order)
            all_lines = cr.dictfetchall()
            id_ai = []

            if all_lines:
                p_map = map(
                    lambda x: {
                        'no': x['id_ai'] if x['id_ai'] != None else '',      
                        'id_ai': x['id_ai'] if x['id_ai'] != None else '',      
                        'branch_id': str(x['branch_id'].encode('ascii','ignore').decode('ascii')) if x['branch_id'] != None else '',
                        'date': str(x['date']) if x['date'] != None else '',
                        'number': str(x['number'].encode('ascii','ignore').decode('ascii')) if x['number'] != None else '',
                        'partner_name': str(x['partner_name'].encode('ascii','ignore').decode('ascii')) if x['partner_name'] != None else '',
                        'date_due': str(x['date_due']) if x['date_due'] != None else '',
                        'division': str(x['division']) if x['division'] != None else '',
                        'partner_code': str(x['partner_code']) if x['partner_code'] != None else '',
                        'journal_name': str(x['journal_name']) if x['journal_name'] != None else '',
                        'account_code': str(x['account_code'].encode('ascii','ignore').decode('ascii')) if x['account_code'] != None else '',
                        'account_name': str(x['account_name'].encode('ascii','ignore').decode('ascii')) if x['account_name'] != None else '',
                        'memo': str(x['memo']) if x['memo'] != None else '',
                        'ref': str(x['ref']) if x['ref'] != None else '',
                        'name': str(x['name']) if x['name'] != None else '',
                        'total': x['total'],
                        'state': x['state'],
                        'dpp': 0,
                        'ppn': 0,
                        'pph': 0,
                        'piutang': 0
                        },
                       
                    all_lines)
                for p in p_map:
                   if p['id_ai'] not in map(
                           lambda x: x.get('id_ai', None), id_ai):
                       account_analytic_account = filter(
                           lambda x: x['id_ai'] == p['id_ai'], all_lines)
                       analytic_1 = ''
                       analytic_2 = ''
                       analytic_3 = ''
                       analytic_4 = ''
                       analytic_1_name = ''
                       analytic_2_name = ''
                       analytic_3_name = ''
                       analytic_4_name = ''
                       ai = self.pool.get('account.voucher').browse(cr, uid, account_analytic_account[0]['id_ai'])
                       analytic = ai.analytic_4 or ''
                       branch_name = ''
                       branch = False
                       branch_status_1 = ''
                       branch_name = ''
                       branch_id = ''
                       if analytic:
                           if analytic.type == 'normal':
                               if analytic.segmen == 1 and analytic_1 == '':
                                   analytic_1_name = analytic.name
                                   analytic_1 = analytic.code
                               if analytic.segmen == 2 and analytic_2 == '':
                                   analytic_2_name = analytic.name
                                   analytic_2 = analytic.code
                               if analytic.segmen == 3 and analytic_3 == '':
                                   analytic_3_name = analytic.name
                                   analytic_3 = analytic.code
                                   branch = analytic.branch_id
                                   branch_name = branch.name
                                   branch_status_1 = branch.branch_status
                                   branch_id = branch.id
                               if analytic.segmen == 4 and analytic_4 == '':
                                   analytic_4_name = analytic.name
                                   analytic_4 = analytic.code
                                   analytic_id = analytic
                           while (analytic.parent_id):
                               analytic = analytic.parent_id
                               if analytic.type == 'normal':
                                   if analytic.segmen == 1 and analytic_1 == '':
                                       analytic_1_name = analytic.name
                                       analytic_1 = analytic.code
                                   if analytic.segmen == 2 and analytic_2 == '':
                                       analytic_2_name = analytic.name
                                       analytic_2 = analytic.code
                                   if analytic.segmen == 3 and analytic_3 == '':
                                       analytic_3_name = analytic.name
                                       analytic_3 = analytic.code
                                       branch = analytic.branch_id
                                       branch_name = branch.name
                                       branch_status_1 = branch.branch_status
                                       branch_id = branch.id
                                   if analytic.segmen == 4 and analytic_4 == '':
                                       analytic_4_name = analytic.name
                                       analytic_4 = analytic.code
                                       analytic_id == analytic
                           if (branch and branch_ids and branch.id not in branch_ids) or (branch and branch_status and branch_status != branch.branch_status):
                               continue
                           analytic_2_branch = analytic_2
                           if analytic_2 in ['210','220','230']:
                               if branch_status_1 == 'H123':
                                   analytic_2_branch = analytic_2[:2] + '1'
                               elif branch_status_1 == 'H23':
                                   analytic_2_branch = analytic_2[:2] + '2'
                               else:
                                   analytic_2_branch = analytic_2
                           analytic_combination = analytic_1 + '/' + analytic_2_branch + '/' + analytic_3 + '/' + analytic_4
                           move_line = ai.move_ids.filtered(lambda r: r.account_id.id == ai.account_id.id and r.debit > 0)
                           residual = 0
                           if move_line:
                              residual = self.pool.get('account.move.line').get_residual_date_based(cr, uid, move_line[0].id, trx_end_date)
                           p.update({'residual': residual})
                           p.update({'lines': account_analytic_account})
                           p.update({'analytic_1': analytic_1_name})
                           p.update({'analytic_2': analytic_2_name})
                           p.update({'analytic_3': analytic_3_name})
                           p.update({'analytic_4': analytic_4_name})
                           p.update({'branch_status': branch_status_1})
                           p.update({'branch_id': branch_name})
                           p.update({'analytic_combination': analytic_combination})
                           id_ai.append(p)
                    
                   av = self.pool.get('account.voucher').browse(cr, uid, p['id_ai'])
                   sum_ppn = av.tax_amount
                   sum_dpp = sum([sales_info.amount for sales_info in av.line_cr_ids])
                   sum_pph = sum([pph_line.amount for pph_line in av.withholding_ids])
                   sum_piutang = sum_dpp + sum_ppn - sum_pph
                   p.update({'dpp': sum_dpp})
                   p.update({'ppn': sum_ppn})
                   p.update({'pph': sum_pph})
                   p.update({'piutang': sum_piutang})

                report.update({'id_ai': id_ai})

        reports = filter(lambda x: x.get('id_ai'), reports)
        
        if not reports :
            reports = [{'title_short': 'Laporan Other Receivable', 'type': ['out_invoice','in_invoice','in_refund','out_refund'], 'id_ai':
                            [{'total': 0,
                              'date': 'NO DATA FOUND',
                              'branch_id': 'NO DATA FOUND',
                              'number': 'NO DATA FOUND',
                              'memo': 'NO DATA FOUND',
                              'ref': 'NO DATA FOUND',
                              'division': 'NO DATA FOUND',
                              'id_ai': 'NO DATA FOUND',
                              'analytic_1': 'NO DATA FOUND',
                              'analytic_2': 'NO DATA FOUND',
                              'analytic_3': 'NO DATA FOUND',
                              'analytic_4': 'NO DATA FOUND',
                              'analytic_combination': 'NO DATA FOUND',
                              'branch_status': 'NO DATA FOUND',
                              'state': 'NO DATA FOUND',
                              'partner_code': 'NO DATA FOUND',
                              'partner_name': 'NO DATA FOUND',
                              'journal_name': 'NO DATA FOUND',
                              'date_due': 'NO DATA FOUND',
                              'account_code': 'NO DATA FOUND',
                              'account_name': 'NO DATA FOUND',
                              'residual': 0,
                              'no': 0,
                              'name': 'NO DATA FOUND',}], 'title': ''}]

        report_date = datetime_field.context_timestamp(
            cr, uid, datetime.now(), context
            ).strftime(DEFAULT_SERVER_DATETIME_FORMAT)

        self.localcontext.update({
            'report_info': report_info,
            'report_date': report_date,
            'reports': reports,
            })
        objects=False
        super(dym_report_other_receivable_print, self).set_context(
            objects, data, ids, report_type)
Example #30
0
    def set_context(self, objects, data, ids, report_type=None):
        cr = self.cr
        uid = self.uid
        context = self.context
        division = data['division']
        date_start_date = data['date_start_date']
        date_end_date = data['date_end_date']
        branch_id = data['branch_id']
        product_ids = data['product_ids']
        cabang = branch_id[1] if branch_id else ''
        title_short_prefix = ''
        
        report_stock_mutation = {
            'type': 'receivable',
            'title': '',
            'division':division,
            'cabang':cabang,
            'title_short': title_short_prefix + '' + _('Laporan Stock Mutation')}
        where_division = where_division2 = " 1=1 "
        if division:
            where_division = " spick.division = '%s'" % str(division)
            where_division2 = " (spick.division = '%s' or spack.division = '%s')" % (str(division),str(division))
        where_picking_type_code = " 1=1 "
        where_date_start_date = where_date_start_date2 = " 1=1 "
        where_state = where_state2 = " 1=1 "
        where_state = " spack.state = 'posted' "
        where_state2 = " spl.state = 'done' "
        where_date_end_date = where_date_end_date2 = " 1=1 "
        if date_end_date:
            where_date_end_date = " date(spick.date) <= '%s'" % str(date_end_date)
            where_date_end_date2 = " date(spl.date) <= '%s'" % str(date_end_date)
        where_min_date_start_date = where_min_date_start_date2 = " 1=1 "
        where_min_date_end_date = where_min_date_end_date2 = " 1=1 "
        where_date_done_start_date = where_date_done_start_date2 = " 1=1 "
        where_date_done_end_date = where_date_done_end_date2 = " 1=1 "
        where_branch_ids = where_branch_ids2 = " 1=1 "
        if branch_id:
            where_branch_ids = " spick.branch_id = %s" % branch_id[0]
            where_branch_ids2 = " (spl.branch_id = %s or spack.branch_id = %s or spick.branch_id = %s)" % (branch_id[0],branch_id[0],branch_id[0])
        where_product_ids = " 1=1 "
        if product_ids :
            where_product_ids = " product.id in %s" % str(
                tuple(product_ids)).replace(',)', ')')
        where_partner_ids = where_partner_ids2 = " 1=1 "
        where_categ_ids = " 1=1 "

        query_stock_mutation = "SELECT CONCAT(cast(spl.id as text),'-dym_stock_packing_line') as id_ai, spick.id as id_picking, 'pack' as object, spl.id as pack_line_id, prod_tmpl.categ_id as categ_id, " \
            "b.code as branch_code, b.name as branch_name, spick.division, spt.name as picking_type_name, spack.name as packing_name, spack.date as packing_date, " \
            "partner.default_code as partner_code, partner.name as partner_name, expedisi.default_code as ekspedisi_code, expedisi.name as ekspedisi_name, " \
            "product.name_template as prod_tmpl, pav.name as color, spl.engine_number as engine, spl.chassis_number as chassis, " \
            "spl.tahun_pembuatan as tahun, " \
            "CASE WHEN source_sloc.usage not in ('internal','kpb','nrfs','customer','inventory') and sloc.usage in ('internal','kpb','nrfs') THEN spl.quantity " \
            "    ELSE 0 " \
            "END as qty, " \
            "CASE WHEN source_sloc.usage = 'customer' and sloc.usage in ('internal','kpb','nrfs') THEN spl.quantity " \
            "    ELSE 0 " \
            "END as qty_retur_jual, " \
            "CASE WHEN source_sloc.usage = 'inventory' and sloc.usage in ('internal','kpb','nrfs') THEN spl.quantity " \
            "    ELSE 0 " \
            "END as qty_adjustment_in, " \
            "CASE WHEN source_sloc.usage in ('internal','kpb','nrfs') and sloc.usage not in ('internal','kpb','nrfs','supplier','inventory') THEN spl.quantity " \
            "    ELSE 0 " \
            "END as qty_out, " \
            "CASE WHEN source_sloc.usage in ('internal','kpb','nrfs') and sloc.usage = 'supplier' THEN spl.quantity " \
            "    ELSE 0 " \
            "END as qty_retur_beli, " \
            "CASE WHEN source_sloc.usage in ('internal','kpb','nrfs') and sloc.usage = 'inventory' THEN spl.quantity " \
            "    ELSE 0 " \
            "END as qty_adjustment_out, " \
            "COALESCE(pph.old_cost_price,0) as amount_awal, " \
            "COALESCE(pph.trans_price,0) as amount_trans, " \
            "COALESCE(move.price_unit,0) as price_unit, " \
            "COALESCE(pph.cost,0) as amount_akhir, " \
            "COALESCE(pph.id,0) as pph_id, " \
            "spack.state as packing_state, spick.origin as picking_origin, prod_categ.name as categ_name, product.default_code as internal_ref, " \
            "COALESCE(spick2.origin,'') as backorder, sloc.name as location, case when spl.ready_for_sale = true then 'RFS' else 'NRFS' end as status_rfs, COALESCE(bs.name, '') as branch_source, parent_sloc.name as parent_location, source_sloc.name as location_source, parent_source_sloc.name as parent_location_source, spl.write_date as write_date, product.id as product_id " \
            "FROM " \
            "dym_stock_packing spack " \
            "inner join dym_stock_packing_line spl ON spack.id = spl.packing_id " \
            "left join stock_picking spick ON spick.id = spack.picking_id " \
            "left join stock_move move ON spick.id = move.picking_id and move.product_id = spl.product_id " \
            "left join consolidate_invoice_line cl ON cl.move_id = move.id " \
            "left join consolidate_invoice c ON c.id = cl.consolidate_id " \
            "left join product_price_history pph ON ((pph.model_name = 'consolidate.invoice' and pph.trans_id = c.id) or (pph.model_name = 'stock.picking' and pph.trans_id = spick.id)) and pph.product_id = spl.product_id " \
            "left join stock_picking spick2 ON spick2.id = spick.backorder_id " \
            "left join dym_branch b on b.id = spick.branch_id " \
            "left join res_partner partner on partner.id = spick.partner_id " \
            "left join res_partner expedisi on expedisi.id = spack.expedition_id " \
            "left join product_product product on product.id = spl.product_id " \
            "left join product_attribute_value_product_product_rel pavpp ON product.id = pavpp.prod_id " \
            "left join product_attribute_value pav ON pavpp.att_id = pav.id " \
            "left join stock_picking_type spt ON spt.id = spick.picking_type_id " \
            "left join product_template prod_tmpl ON prod_tmpl.id = product.product_tmpl_id " \
            "left join product_category prod_categ ON prod_categ.id = prod_tmpl.categ_id " \
            "left join stock_location sloc ON sloc.id = spl.destination_location_id " \
            "left join stock_location parent_sloc ON parent_sloc.id = sloc.location_id " \
            "left join stock_location source_sloc ON source_sloc.id = spl.source_location_id " \
            "left join stock_location parent_source_sloc ON parent_source_sloc.id = source_sloc.location_id " \
            "left join dym_branch bs ON spack.branch_sender_id = bs.id " \
            "where ((spl.engine_number is not null and spick.division = 'Unit') or spick.division != 'Unit') and spl.quantity > 0 and " + where_state + " AND " + where_division + " AND " + where_picking_type_code + " AND " + where_date_start_date + " AND " + where_date_end_date + " AND " + where_min_date_start_date + " AND " + where_min_date_end_date + " AND " + where_date_done_start_date + " AND " + where_date_done_end_date + " AND " + where_branch_ids + " AND " + where_categ_ids + " AND " + where_product_ids + " AND " + where_partner_ids + " " \
            " UNION ALL " \
            "SELECT CONCAT(cast(spl.id as text),'-stock_move') as id_ai, spick.id as id_picking, 'move' as object, spl.id as pack_line_id, prod_tmpl.categ_id as categ_id, " \
            "b.code as branch_code, b.name as branch_name, " \
            "CASE WHEN spack.id is not null THEN spack.division " \
            "    WHEN spick.id is not null THEN spick.division " \
            "    ELSE '' " \
            "END as division, " \
            "CASE WHEN spack.id is not null and sloc.usage = 'inventory' THEN 'Delivery Orders' " \
            "    WHEN spack.id is not null and source_sloc.usage = 'inventory' THEN 'Receipts' " \
            "    ELSE spt.name " \
            "END as picking_type_name, " \
            "CASE WHEN spack.id is not null THEN spack.name " \
            "    WHEN spick.id is not null THEN spick.name " \
            "    ELSE '' " \
            "END as packing_name, " \
            "date(spl.date) as packing_date, " \
            "partner.default_code as partner_code, partner.name as partner_name, '' as ekspedisi_code, '' as ekspedisi_name, " \
            "product.name_template as prod_tmpl, pav.name as color, lot.name as engine, CONCAT(lot.chassis_code, lot.chassis_no) as chassis, " \
            "lot.tahun as tahun,  " \
            "CASE WHEN source_sloc.usage not in ('internal','kpb','nrfs','customer','inventory') and sloc.usage in ('internal','kpb','nrfs') THEN spl.product_uom_qty " \
            "    ELSE 0 " \
            "END as qty, " \
            "CASE WHEN source_sloc.usage = 'customer' and sloc.usage in ('internal','kpb','nrfs') THEN spl.product_uom_qty " \
            "    ELSE 0 " \
            "END as qty_retur_jual, " \
            "CASE WHEN source_sloc.usage = 'inventory' and sloc.usage in ('internal','kpb','nrfs') THEN spl.product_uom_qty " \
            "    ELSE 0 " \
            "END as qty_adjustment_in, " \
            "CASE WHEN source_sloc.usage in ('internal','kpb','nrfs') and sloc.usage not in ('internal','kpb','nrfs','supplier','inventory') THEN spl.product_uom_qty " \
            "    ELSE 0 " \
            "END as qty_out, " \
            "CASE WHEN source_sloc.usage in ('internal','kpb','nrfs') and sloc.usage = 'supplier' THEN spl.product_uom_qty " \
            "    ELSE 0 " \
            "END as qty_retur_beli, " \
            "CASE WHEN source_sloc.usage in ('internal','kpb','nrfs') and sloc.usage = 'inventory' THEN spl.product_uom_qty " \
            "    ELSE 0 " \
            "END as qty_adjustment_out, " \
            "COALESCE(pph.old_cost_price,0) as amount_awal, " \
            "COALESCE(pph.trans_price,0) as amount_trans, " \
            "COALESCE(spl.price_unit,0) as price_unit, " \
            "COALESCE(pph.cost,0) as amount_akhir, " \
            "COALESCE(pph.id,0) as pph_id, " \
            "CASE WHEN spack.id is not null THEN spack.state " \
            "    WHEN spick.id is not null THEN spick.state " \
            "    ELSE '' " \
            "END as packing_state, " \
            "spick.origin as picking_origin, prod_categ.name as categ_name, product.default_code as internal_ref, " \
            "COALESCE(spick2.origin,'') as backorder, sloc.name as location, case when sloc.usage = 'internal' then 'RFS' when sloc.usage in ('kpb','nrfs') then 'NRFS' else '' end as status_rfs, '' as branch_source, parent_sloc.name as parent_location, source_sloc.name as location_source, parent_source_sloc.name as parent_location_source, spl.write_date as write_date, product.id as product_id " \
            "FROM " \
            "stock_move spl " \
            "left join stock_inventory spack ON spack.id = spl.inventory_id " \
            "left join stock_production_lot lot ON lot.id = spl.restrict_lot_id " \
            "left join stock_picking spick ON spick.id = spl.picking_id " \
            "left join consolidate_invoice_line cl ON cl.move_id = spl.id " \
            "left join consolidate_invoice c ON c.id = cl.consolidate_id " \
            "left join product_price_history pph ON ((pph.model_name = 'consolidate.invoice' and pph.trans_id = c.id) or (pph.model_name = 'stock.picking' and pph.trans_id = spick.id) or (pph.model_name = 'stock.inventory' and pph.trans_id = spack.id)) and pph.product_id = spl.product_id " \
            "left join stock_picking spick2 ON spick2.id = spick.backorder_id " \
            "left join dym_branch b on b.id = spl.branch_id " \
            "left join res_partner partner on partner.id = spick.partner_id " \
            "left join dym_stock_packing packing on spick.id = packing.picking_id " \
            "left join product_product product on product.id = spl.product_id " \
            "left join product_attribute_value_product_product_rel pavpp ON product.id = pavpp.prod_id " \
            "left join product_attribute_value pav ON pavpp.att_id = pav.id " \
            "left join stock_picking_type spt ON spt.id = spl.picking_type_id " \
            "left join product_template prod_tmpl ON prod_tmpl.id = product.product_tmpl_id " \
            "left join product_category prod_categ ON prod_categ.id = prod_tmpl.categ_id " \
            "left join stock_location sloc ON sloc.id = spl.location_dest_id " \
            "left join stock_location parent_sloc ON parent_sloc.id = sloc.location_id " \
            "left join stock_location source_sloc ON source_sloc.id = spl.location_id " \
            "left join stock_location parent_source_sloc ON parent_source_sloc.id = source_sloc.location_id " \
            "where ((lot.id is not null and (spack.division = 'Unit' or spick.division = 'Unit')) or (spack.division != 'Unit' or spick.division != 'Unit')) and spl.product_uom_qty > 0 and " + where_state2 + " AND " + where_division2 + " AND " + where_picking_type_code + " AND " + where_date_start_date2 + " AND " + where_date_end_date2 + " AND " + where_min_date_start_date2 + " AND " + where_min_date_end_date2 + " AND " + where_date_done_start_date2 + " AND " + where_date_done_end_date2 + " AND " + where_branch_ids2 + " AND " + where_categ_ids + " AND " + where_product_ids + " AND " + where_partner_ids2 + " and packing.id is null " \
            " ORDER BY product_id, packing_date, write_date, pph_id  " \
        
        move_selection = ""
        report_info = _('')
        move_selection += ""
        
        reports = [report_stock_mutation]
        #print query_stock_mutation
        product_x = ''
        for report in reports:
            cr.execute(query_stock_mutation)
            all_lines = cr.dictfetchall()
            picking_ids = []

            if all_lines:
                def lines_map(x):
                        x.update({'docname': x['branch_code']})
                map(lines_map, all_lines)
                for cnt in range(len(all_lines)-1):
                    if all_lines[cnt]['id_picking'] != all_lines[cnt+1]['id_picking']:
                        all_lines[cnt]['draw_line'] = 1
                    else:
                        all_lines[cnt]['draw_line'] = 0
                all_lines[-1]['draw_line'] = 1

                p_map = map(
                    lambda x: {
                        'no': 0,
                        'id_picking': str(x['id_picking']),
                        'id_ai': str(x['id_ai']),
                        'object': str(x['object']),
                        'pack_line_id': x['pack_line_id'],
                        'branch_code': str(x['branch_code'].encode('ascii','ignore').decode('ascii')) if x['branch_code'] != None else '',
                        'branch_name': str(x['branch_name'].encode('ascii','ignore').decode('ascii')) if x['branch_name'] != None else '',
                        'branch_source': str(x['branch_source'].encode('ascii','ignore').decode('ascii')) if x['branch_source'] != None else '',
                        'division': str(x['division'].encode('ascii','ignore').decode('ascii')) if x['division'] != None else '',
                        'picking_type_name': str(x['picking_type_name'].encode('ascii','ignore').decode('ascii')) if x['picking_type_name'] != None else '',
                        'internal_ref': str(x['internal_ref'].encode('ascii','ignore').decode('ascii')) if x['internal_ref'] != None else '',
                        'categ_name': str(x['categ_name'].encode('ascii','ignore').decode('ascii')) if x['categ_name'] != None else '',
                        'packing_name': str(x['packing_name'].encode('ascii','ignore').decode('ascii')) if x['packing_name'] != None else '',
                        'packing_date': str(x['packing_date'].encode('ascii','ignore').decode('ascii')) if x['packing_date'] != None else '',
                        'partner_code': str(x['partner_code'].encode('ascii','ignore').decode('ascii')) if x['partner_code'] != None else '',
                        'partner_name': str(x['partner_name'].encode('ascii','ignore').decode('ascii')) if x['partner_name'] != None else '',
                        'ekspedisi_code': str(x['ekspedisi_code'].encode('ascii','ignore').decode('ascii')) if x['ekspedisi_code'] != None else '',
                        'ekspedisi_name': str(x['ekspedisi_name'].encode('ascii','ignore').decode('ascii')) if x['ekspedisi_name'] != None else '',
                        'prod_tmpl': str(x['prod_tmpl'].encode('ascii','ignore').decode('ascii')) if x['prod_tmpl'] != None else '',
                        'color': str(x['color'].encode('ascii','ignore').decode('ascii')) if x['color'] != None else '',
                        'engine': str(x['engine'].encode('ascii','ignore').decode('ascii')) if x['engine'] != None else '',
                        'chassis': str(x['chassis'].encode('ascii','ignore').decode('ascii')) if x['chassis'] != None else '',
                        'tahun': str(x['tahun'].encode('ascii','ignore').decode('ascii')) if x['tahun'] != None else '',
                        'qty': x['qty'],
                        'qty_out': x['qty_out'],
                        'qty_retur_beli': x['qty_retur_beli'],
                        'qty_retur_jual': x['qty_retur_jual'],
                        'qty_adjustment_in': x['qty_adjustment_in'],
                        'qty_adjustment_out': x['qty_adjustment_out'],
                        'amount': 0,
                        'amount_out': 0,
                        'amount_retur_beli': 0,
                        'amount_retur_jual': 0,
                        'amount_adjustment_in': 0,
                        'amount_adjustment_out': 0,
                        'amount_awal': x['amount_awal'],
                        'amount_trans': x['amount_trans'],
                        'price_unit': x['price_unit'],
                        'amount_akhir': x['amount_akhir'],
                        'packing_state': str(x['packing_state'].encode('ascii','ignore').decode('ascii')) if x['packing_state'] != None else '',
                        'picking_origin': str(x['picking_origin'].encode('ascii','ignore').decode('ascii')) if x['picking_origin'] != None else '',
                        'backorder': str(x['backorder'].encode('ascii','ignore').decode('ascii')) if x['backorder'] != None else '',
                        'location': str(x['location'].encode('ascii','ignore').decode('ascii')) if x['location'] != None else '',
                        'parent_location': str(x['parent_location'].encode('ascii','ignore').decode('ascii')) if x['parent_location'] != None else '',
                        'location_source': str(x['location_source'].encode('ascii','ignore').decode('ascii')) if x['location_source'] != None else '',
                        'parent_location_source': str(x['parent_location_source'].encode('ascii','ignore').decode('ascii')) if x['parent_location_source'] != None else '',
                        'status_rfs': str(x['status_rfs'].encode('ascii','ignore').decode('ascii')) if x['status_rfs'] != None else '',
                        'product_id': str(x['product_id']),
                    },
                    all_lines)
                qty_awal = 0
                amount_awal = 0
                qty_akhir = 0
                amount_akhir = 0
                product_id = False
                flag = True

                for p in p_map:
                    if p['id_ai'] not in map(
                            lambda x: x.get('id_ai', None), picking_ids):
                        packing_line = filter(
                            lambda x: x['id_ai'] == p['id_ai'], all_lines)

                        #print product_x,packing_line[0]['product_id'],packing_line[0]['packing_date'],packing_line[0]['internal_ref'],qty_awal,packing_line[0]['qty'],packing_line[0]['qty_retur_jual'],packing_line[0]['qty_adjustment_in'],packing_line[0]['qty_out'],packing_line[0]['qty_retur_beli'],packing_line[0]['qty_adjustment_out']
                        if packing_line[0]['packing_date'] < str(date_start_date) and date_start_date:
                            if  product_x ==  packing_line[0]['product_id']:
                                qty_awal += packing_line[0]['qty']
                                qty_awal += packing_line[0]['qty_retur_jual']
                                qty_awal += packing_line[0]['qty_adjustment_in']
                                qty_awal -= packing_line[0]['qty_out']
                                qty_awal -= packing_line[0]['qty_retur_beli']
                                qty_awal -= packing_line[0]['qty_adjustment_out']
                               # print 'x',qty_awal

                            else:
                                qty_awal = packing_line[0]['qty']
                                qty_awal += packing_line[0]['qty_retur_jual']
                                qty_awal += packing_line[0]['qty_adjustment_in']
                                qty_awal -= packing_line[0]['qty_out']
                                qty_awal -= packing_line[0]['qty_retur_beli']
                                qty_awal -= packing_line[0]['qty_adjustment_out']
                                #print 'y', qty_awal

                            product_x = packing_line[0]['product_id']
                            #print 'a------------'
                        else:

                            flag = True
                            qty_akhir = qty_awal
                            if packing_line[0]['qty'] > 0:
                                if product_x == packing_line[0]['product_id']:
                                    qty_akhir += packing_line[0]['qty']
                                else:
                                    qty_akhir = packing_line[0]['qty']
                                p.update({'amount': packing_line[0]['amount_trans'] or packing_line[0]['price_unit']})
                            if packing_line[0]['qty_retur_jual'] > 0:
                                if product_x == packing_line[0]['product_id']:
                                    qty_akhir += packing_line[0]['qty_retur_jual']
                                else:
                                    qty_akhir += packing_line[0]['qty_retur_jual']
                                p.update({'amount_retur_jual': packing_line[0]['amount_trans'] or packing_line[0]['price_unit']})
                            if packing_line[0]['qty_adjustment_in'] > 0:
                                if product_x == packing_line[0]['product_id']:
                                    qty_akhir += packing_line[0]['qty_adjustment_in']
                                else:
                                    qty_akhir += packing_line[0]['qty_adjustment_in']
                                p.update({'amount_adjustment_in': packing_line[0]['amount_trans'] or packing_line[0]['price_unit']})
                            if packing_line[0]['qty_out'] > 0:
                                if product_x == packing_line[0]['product_id']:
                                    qty_akhir -= packing_line[0]['qty_out']
                                else:
                                    qty_akhir -= packing_line[0]['qty_out']
                                p.update({'amount_out': packing_line[0]['amount_trans'] or packing_line[0]['price_unit']})
                            if packing_line[0]['qty_retur_beli'] > 0:
                                if product_x == packing_line[0]['product_id']:
                                    qty_akhir -= packing_line[0]['qty_retur_beli']
                                else:
                                    qty_akhir -= packing_line[0]['qty_retur_beli']

                                p.update({'amount_retur_beli': packing_line[0]['amount_trans'] or packing_line[0]['price_unit']})
                            if packing_line[0]['qty_adjustment_out'] > 0:
                                if product_x == packing_line[0]['product_id']:
                                    qty_akhir -= packing_line[0]['qty_adjustment_out']
                                else:
                                    qty_akhir =- packing_line[0]['qty_adjustment_out']
                                p.update({'amount_adjustment_out': packing_line[0]['amount_trans'] or packing_line[0]['price_unit']})
                            p.update({'qty_awal': qty_awal})
                            p.update({'qty_akhir': qty_akhir})
                            p.update({'lines': packing_line})
                            picking_ids.append(p)
                            qty_awal = qty_akhir
                            amount_awal = amount_akhir
                            product_x = packing_line[0]['product_id']
                            #print 'b------------'
                report.update({'cabang': cabang})
                report.update({'picking_ids': picking_ids})

        reports = filter(lambda x: x.get('picking_ids'), reports)

        if not reports :
            reports = [{'picking_ids': [{
                        'no': 0,
                        'branch_code': 'NO DATA FOUND',
                        'branch_name': 'NO DATA FOUND',
                        'branch_source': 'NO DATA FOUND',
                        'division': 'NO DATA FOUND',
                        'categ_name': 'NO DATA FOUND',
                        'internal_ref': 'NO DATA FOUND',
                        'picking_type_name': 'NO DATA FOUND',
                        'packing_name': 'NO DATA FOUND',
                        'packing_date': 'NO DATA FOUND',
                        'partner_code': 'NO DATA FOUND',
                        'partner_name': 'NO DATA FOUND',
                        'ekspedisi_code': 'NO DATA FOUND',
                        'ekspedisi_name': 'NO DATA FOUND',
                        'prod_tmpl': 'NO DATA FOUND',
                        'color': 'NO DATA FOUND',
                        'engine': 'NO DATA FOUND',
                        'chassis': 'NO DATA FOUND',
                        'tahun': 'NO DATA FOUND',
                        'location': 'NO DATA FOUND',
                        'parent_location': 'NO DATA FOUND',
                        'location_source': 'NO DATA FOUND',
                        'parent_location_source': 'NO DATA FOUND',
                        'status_rfs': 'NO DATA FOUND',
                        'qty': 0,
                        'qty_out': 0,
                        'qty_retur_beli': 0,
                        'qty_retur_jual': 0,
                        'qty_adjustment_in': 0,
                        'qty_adjustment_out': 0,
                        'amount': 0,
                        'amount_out': 0,
                        'amount_retur_beli': 0,
                        'amount_retur_jual': 0,
                        'amount_adjustment_in': 0,
                        'amount_adjustment_out': 0,
                        'qty_awal': 0,
                        'qty_akhir': 0,
                        'amount_awal': 0,
                        'amount_akhir': 0,
                        'packing_state': 'NO DATA FOUND',
                        'picking_origin': 'NO DATA FOUND',
                        'backorder': 'NO DATA FOUND',}], 'title_short': 'Laporan Stock Mutation', 'type': 'receivable', 'title': '', 'division':division, 'cabang': cabang}]
        report_date = datetime_field.context_timestamp(
            cr, uid, datetime.now(), context
            ).strftime(DEFAULT_SERVER_DATETIME_FORMAT)

        self.localcontext.update({
            'report_info': report_info,
            'report_date': report_date,
            'reports': reports,
            })
        objects = False
        super(dym_report_stock_mutation_print, self).set_context(
            objects, data, ids, report_type)
Example #31
0
    def set_context(self, objects, data, ids, report_type=None):
        cr = self.cr
        uid = self.uid
        context = self.context
        start_date = data['start_date']
        end_date = data['end_date']
        branch_ids = data['branch_ids']
        account_id = data['account_ids']
        # segmen = data['segmen']
        branch_status = False
        trx_start_date = data['trx_start_date']
        trx_end_date = data['trx_end_date']
        division = data['division']
        partner_ids = data['partner_ids']

        title_prefix = ''
        title_short_prefix = ''
        
        report_loan_piutang = {
            'type': 'payable',
            'title': '',
            'title_short': title_short_prefix + ', ' + _('Laporan Loan Piutang')}

        query_start = "SELECT loan.id as id_ai, " \
            "COALESCE(b.name,'') as branch_id, " \
            "loan.date as date, " \
            "loan.jumlah_loan as total, " \
            "loan.loan_type as loan_type, " \
            "loan.memo as memo, " \
            "loan.reference as ref, " \
            "loan.division as division, " \
            "loan.name as number, " \
            "loan.state as state, " \
            "loan.first_due_date as date_due, " \
            "loan.effective_date as effective_date, " \
            "rp.default_code as partner_code, " \
            "rp.name as partner_name, " \
            "a.code as account_code, " \
            "a.name as account_name, " \
            "av.number as rv_pv " \
            "FROM " \
            "dym_loan loan " \
            "LEFT JOIN account_voucher av ON loan.voucher_id = av.id " \
            "LEFT JOIN dym_branch b ON loan.branch_id = b.id " \
            "LEFT JOIN res_partner rp ON rp.id = loan.partner_id " \
            "LEFT JOIN account_account a ON a.id = loan.account_id " \
            "where 1=1 and loan.state in ('approved','done') and loan.loan_type = 'Piutang' "
            
        move_selection = ""
        report_info = _('')
        move_selection += ""
        
        query_end=""
        if division :
            query_end +=" AND loan.division = '%s'" % str(division)
        if trx_start_date :
            query_end +=" AND loan.date >= '%s'" % str(trx_start_date)
        if trx_end_date :
            query_end +=" AND loan.date <= '%s'" % str(trx_end_date)
        if start_date :
            query_end +=" AND loan.date_due >= '%s'" % str(start_date)
        if end_date :
            query_end +=" AND loan.date_due <= '%s'" % str(end_date)
        if partner_ids :
            query_end +=" AND loan.partner_id in %s" % str(
                tuple(partner_ids)).replace(',)', ')')
        if branch_ids :
            query_end +=" AND loan.branch_id in %s" % str(
                tuple(branch_ids)).replace(',)', ')')
        if account_id :
            query_end+=" AND loan.account_id in %s" % str(
                tuple(account_id)).replace(',)', ')')
        reports = [report_loan_piutang]
        
        # query_order = "order by cabang"
        query_order = ""
        for report in reports:
            cr.execute(query_start + query_end + query_order)
            all_lines = cr.dictfetchall()
            id_ai = []

            if all_lines:
                # def lines_map(x):
                #         x.update({'docname': x['cabang']})
                # map(lines_map, all_lines)
                # for cnt in range(len(all_lines)-1):
                #     if all_lines[cnt]['id_aml'] != all_lines[cnt+1]['id_aml']:
                #         all_lines[cnt]['draw_line'] = 1
                #     else:
                #         all_lines[cnt]['draw_line'] = 0
                # all_lines[-1]['draw_line'] = 1

                p_map = map(
                    lambda x: {
                        'no': x['id_ai'] if x['id_ai'] != None else '',      
                        'id_ai': x['id_ai'] if x['id_ai'] != None else '',      
                        'branch_id': str(x['branch_id'].encode('ascii','ignore').decode('ascii')) if x['branch_id'] != None else '',
                        'date': str(x['date']) if x['date'] != None else '',
                        'number': str(x['number'].encode('ascii','ignore').decode('ascii')) if x['number'] != None else '',
                        'partner_name': str(x['partner_name'].encode('ascii','ignore').decode('ascii')) if x['partner_name'] != None else '',
                        'date_due': str(x['date_due']) if x['date_due'] != None else '',
                        'division': str(x['division']) if x['division'] != None else '',
                        'partner_code': str(x['partner_code']) if x['partner_code'] != None else '',
                        'tipe_pinjaman': str(x['tipe_pinjaman']) if x['tipe_pinjaman'] != None else '',
                        'account_code': str(x['account_code'].encode('ascii','ignore').decode('ascii')) if x['account_code'] != None else '',
                        'account_name': str(x['account_name'].encode('ascii','ignore').decode('ascii')) if x['account_name'] != None else '',
                        'memo': str(x['memo']) if x['memo'] != None else '',
                        'ref': str(x['ref']) if x['ref'] != None else '',
                        'rv_pv': str(x['rv_pv']) if x['rv_pv'] != None else '',
                        'effective_date': str(x['effective_date']) if x['effective_date'] != None else '',
                        'loan_type': str(x['loan_type']) if x['loan_type'] != None else '',
                        'total': x['total'],
                        'state': x['state'],},
                       
                    all_lines)
                for p in p_map:
                   if p['id_ai'] not in map(
                           lambda x: x.get('id_ai', None), id_ai):
                       account_analytic_account = filter(
                           lambda x: x['id_ai'] == p['id_ai'], all_lines)
                       analytic_1 = ''
                       analytic_2 = ''
                       analytic_3 = ''
                       analytic_4 = ''
                       analytic_1_name = ''
                       analytic_2_name = ''
                       analytic_3_name = ''
                       analytic_4_name = ''
                       ai = self.pool.get('dym.loan').browse(cr, uid, account_analytic_account[0]['id_ai'])
                       analytic = ai.analytic_4 or ''
                       branch_name = ''
                       branch = False
                       branch_status_1 = ''
                       branch_name = ''
                       branch_id = ''
                       if analytic:
                           if analytic.type == 'normal':
                               if analytic.segmen == 1 and analytic_1 == '':
                                   analytic_1_name = analytic.name
                                   analytic_1 = analytic.code
                               if analytic.segmen == 2 and analytic_2 == '':
                                   analytic_2_name = analytic.name
                                   analytic_2 = analytic.code
                               if analytic.segmen == 3 and analytic_3 == '':
                                   analytic_3_name = analytic.name
                                   analytic_3 = analytic.code
                                   branch = analytic.branch_id
                                   branch_name = branch.name
                                   branch_status_1 = branch.branch_status
                                   branch_id = branch.id
                               if analytic.segmen == 4 and analytic_4 == '':
                                   analytic_4_name = analytic.name
                                   analytic_4 = analytic.code
                                   analytic_id = analytic
                           while (analytic.parent_id):
                               analytic = analytic.parent_id
                               if analytic.type == 'normal':
                                   if analytic.segmen == 1 and analytic_1 == '':
                                       analytic_1_name = analytic.name
                                       analytic_1 = analytic.code
                                   if analytic.segmen == 2 and analytic_2 == '':
                                       analytic_2_name = analytic.name
                                       analytic_2 = analytic.code
                                   if analytic.segmen == 3 and analytic_3 == '':
                                       analytic_3_name = analytic.name
                                       analytic_3 = analytic.code
                                       branch = analytic.branch_id
                                       branch_name = branch.name
                                       branch_status_1 = branch.branch_status
                                       branch_id = branch.id
                                   if analytic.segmen == 4 and analytic_4 == '':
                                       analytic_4_name = analytic.name
                                       analytic_4 = analytic.code
                                       analytic_id == analytic
                           if (branch and branch_ids and branch.id not in branch_ids) or (branch and branch_status and branch_status != branch.branch_status):
                               continue
                           analytic_2_branch = analytic_2
                           if analytic_2 in ['210','220','230']:
                               if branch_status_1 == 'H123':
                                   analytic_2_branch = analytic_2[:2] + '1'
                               elif branch_status_1 == 'H23':
                                   analytic_2_branch = analytic_2[:2] + '2'
                               else:
                                   analytic_2_branch = analytic_2
                           analytic_combination = analytic_1 + '/' + analytic_2_branch + '/' + analytic_3 + '/' + analytic_4
                           residual = 0
                           move_line_id = self.pool.get('account.move.line').search(cr, uid, [
                                                          ('dym_loan_id','=',ai.id),
                                                          ('debit','>',0)
                                                          ])
                           residual = 0
                           if move_line_id:
                              residual = self.pool.get('account.move.line').get_residual_date_based(cr, uid, move_line_id, trx_end_date)
                           p.update({'residual': residual})
                           p.update({'lines': account_analytic_account})
                           p.update({'analytic_1': analytic_1_name})
                           p.update({'analytic_2': analytic_2_name})
                           p.update({'analytic_3': analytic_3_name})
                           p.update({'analytic_4': analytic_4_name})
                           p.update({'branch_status': branch_status_1})
                           p.update({'branch_id': branch_name})
                           p.update({'analytic_combination': analytic_combination})
                           id_ai.append(p)
                report.update({'id_ai': id_ai})
#                 for p in p_map:                report.update({'id_ai': id_ai})

#                     if p['id_aml'] not in map(
#                             lambda x: x.get('id_aml', None), ids_aml):
#                         ids_aml.append(p)
#                         lines = filter(
#                             lambda x: x['id_aml'] == p['id_aml'], all_lines)
#                         p.update({'lines': lines})
#                         p.update(
#                             {'d': 1,
#                              'c': 2,
#                              'b': 3})
                # report.update({'id_ai': p_map})

        reports = filter(lambda x: x.get('id_ai'), reports)
        
        if not reports :
            reports = [{'title_short': 'Laporan Loan Piutang', 'type': ['out_invoice','in_invoice','in_refund','out_refund'], 'id_ai':
                            [{'total': 0,
                              'date': 'NO DATA FOUND',
                              'branch_id': 'NO DATA FOUND',
                              'number': 'NO DATA FOUND',
                              'memo': 'NO DATA FOUND',
                              'ref': 'NO DATA FOUND',
                              'division': 'NO DATA FOUND',
                              'id_ai': 'NO DATA FOUND',
                              'analytic_1': 'NO DATA FOUND',
                              'analytic_2': 'NO DATA FOUND',
                              'analytic_3': 'NO DATA FOUND',
                              'analytic_4': 'NO DATA FOUND',
                              'analytic_combination': 'NO DATA FOUND',
                              'branch_status': 'NO DATA FOUND',
                              'state': 'NO DATA FOUND',
                              'partner_code': 'NO DATA FOUND',
                              'partner_name': 'NO DATA FOUND',
                              'journal_name': 'NO DATA FOUND',
                              'date_due': 'NO DATA FOUND',
                              'account_code': 'NO DATA FOUND',
                              'account_name': 'NO DATA FOUND',
                              'residual': 0,
                              'no': 0,
                              'loan_type': 'NO DATA FOUND',
                              'rv_pv': 'NO DATA FOUND',
                              'effective_date': 'NO DATA FOUND',
                              'tipe_pinjaman': 'NO DATA FOUND',}], 'title': ''}]

        report_date = datetime_field.context_timestamp(
            cr, uid, datetime.now(), context
            ).strftime(DEFAULT_SERVER_DATETIME_FORMAT)

        self.localcontext.update({
            'report_info': report_info,
            'report_date': report_date,
            'reports': reports,
            })
        super(dym_report_loan_piutang_print, self).set_context(
            objects, data, ids, report_type)
    def set_context(self, objects, data, ids, report_type=None):
        cr = self.cr
        uid = self.uid
        context = self.context

        period_obj = self.pool['account.period']

        posted = (data['target_move'] == 'posted') and True or False
        result_selection = data['result_selection']
        company_id = data['company_id']
        period_id = data['period_id']
        period = period_obj.browse(cr, uid, period_id, context=context)
        period_code = period.code
        title_prefix = _('Period') + ' %s : ' % period_code
        title_short_prefix = period_code
        digits = self.pool['decimal.precision'].precision_get(
            cr, uid, 'Account')

        # perform query on selected period as well as preceding periods.
        period_date_start = period.date_start
        period_query_ids = period_obj.search(
            cr, uid,
            [('date_stop', '<=', period_date_start),
             ('company_id', '=', company_id)])
        period_query_ids += [period_id]
        # find periods to select move_lines
        # that are reconciled after period
        next_period_ids = period_obj.search(
            cr, uid,
            [('date_stop', '>', period.date_stop),
             ('company_id', '=', company_id)])

        report_ar = {
            'type': 'receivable',
            'title': title_prefix + _('Open Receivables'),
            'title_short': title_short_prefix + ', ' + _('AR')}
        report_ap = {
            'type': 'payable',
            'title': title_prefix + _('Open Payables'),
            'title_short': title_short_prefix + ', ' + _('AP')}

        # CASE statement on due date since standard Odoo accounting
        # allows to change the date_maturity in the accounting entries
        # on confirmed invoices (when using the account_cancel module).
        # The CASE statement gives accounting entries priority
        # over the invoice field.
        query_start = "SELECT l.move_id AS m_id, l.id AS l_id, " \
            "l.date AS l_date, " \
            "m.name AS move_name, m.date AS m_date, " \
            "a.id AS a_id, a.code AS a_code, a.type AS a_type, " \
            "j.id AS j_id, j.code AS j_code, j.type AS j_type, " \
            "p.id AS p_id, p.name AS p_name, p.ref AS p_ref, " \
            "l.name AS l_name, " \
            "l.debit, l.credit, " \
            "(CASE WHEN l.date_maturity IS NOT NULL THEN l.date_maturity " \
            "ELSE ai.date_due END) AS date_due," \
            "l.reconcile_id, r.name AS r_name, " \
            "l.reconcile_partial_id, rp.name AS rp_name, " \
            "ai.internal_number AS inv_number, b.name AS st_number, " \
            "v.number AS voucher_number " \
            "FROM account_move_line l " \
            "INNER JOIN account_journal j ON l.journal_id = j.id " \
            "INNER JOIN account_move m ON l.move_id = m.id " \
            "INNER JOIN account_account a ON l.account_id = a.id " \
            "INNER JOIN account_period ON l.period_id = account_period.id " \
            "LEFT OUTER JOIN account_invoice ai ON ai.move_id = m.id " \
            "LEFT OUTER JOIN account_voucher v ON v.move_id = m.id " \
            "LEFT OUTER JOIN account_bank_statement b " \
            "ON l.statement_id = b.id " \
            "LEFT OUTER JOIN res_partner p ON l.partner_id = p.id " \
            "LEFT OUTER JOIN account_move_reconcile r " \
            "ON l.reconcile_id = r.id " \
            "LEFT OUTER JOIN account_move_reconcile rp " \
            "ON l.reconcile_partial_id = rp.id "

        if posted:
            move_selection = "AND m.state = 'posted' "
            report_info = _('All Posted Entries')
        else:
            move_selection = ''
            report_info = _('All Entries')

        move_selection += "AND account_period.id in %s" % str(
            tuple(period_query_ids)).replace(',)', ')')

        # define subquery to select move_lines within FY/period
        # that are reconciled after FY/period
        if next_period_ids:
            subquery = "OR reconcile_id IN " \
                "(SELECT reconcile_id FROM account_move_line " \
                "WHERE period_id IN %s " \
                "AND reconcile_id IS NOT NULL)" % str(
                    tuple(next_period_ids)).replace(',)', ')')
        else:
            subquery = None

        query_end = 'WHERE m.company_id = %s ' \
            'AND a.type = %s ' + move_selection + \
            'AND (l.reconcile_id IS NULL ' + (subquery or '') + ') ' \
            'AND (l.debit+l.credit) != 0 ' \
            'ORDER BY a_code, p_name, p_id, l_date'

        if result_selection == 'customer':
            reports = [report_ar]
        elif result_selection == 'supplier':
            reports = [report_ap]
        else:
            reports = [report_ar, report_ap]

        for report in reports:

            cr.execute(query_start + query_end, (company_id, report['type']))
            all_lines = cr.dictfetchall()
            partners = []

            if all_lines:

                # add reference of corresponding legal document
                def lines_map(x):
                    if x['j_type'] in ['sale', 'sale_refund',
                                       'purchase', 'purchase_refund']:
                        x.update({
                            'docname': x['inv_number'] or x['voucher_number']
                        })
                    elif x['j_type'] in ['bank', 'cash']:
                        x.update({
                            'docname': x['st_number'] or x['voucher_number']
                        })
                    else:
                        x.update({'docname': x['move_name']})
                map(lines_map, all_lines)

                # insert a flag in every line to indicate the end of a partner
                # this flag can be used to draw a full line between partners
                for cnt in range(len(all_lines)-1):
                    if all_lines[cnt]['p_id'] != all_lines[cnt+1]['p_id']:
                        all_lines[cnt]['draw_line'] = 1
                    else:
                        all_lines[cnt]['draw_line'] = 0
                all_lines[-1]['draw_line'] = 1

                p_map = map(
                    lambda x: {
                        'p_id': x['p_id'],
                        'p_name': x['p_name'],
                        'p_ref': x['p_ref']},
                    all_lines)
                for p in p_map:
                    # remove duplicates while preserving list order
                    if p['p_id'] not in map(
                            lambda x: x.get('p_id', None), partners):
                        partners.append(p)
                        partner_lines = filter(
                            lambda x: x['p_id'] == p['p_id'], all_lines)
                        p.update({'lines': partner_lines})
                        debits = map(
                            lambda x: x['debit'] or 0.0, partner_lines)
                        sum_debit = reduce(lambda x, y: x + y, debits)
                        sum_debit = round(sum_debit, digits)
                        credits = map(
                            lambda x: x['credit'] or 0.0, partner_lines)
                        sum_credit = reduce(lambda x, y: x + y, credits)
                        sum_credit = round(sum_credit, digits)
                        balance = sum_debit - sum_credit
                        p.update(
                            {'d': sum_debit,
                             'c': sum_credit,
                             'b': balance})
                report.update({'partners': partners})

                sum_debit = 0.0
                sum_credit = 0.0
                acc_lines = filter(
                    lambda x: x['a_type'] == report['type'], all_lines)
                debits = map(lambda x: x['debit'] or 0.0, acc_lines)
                if debits:
                    sum_debit = reduce(lambda x, y: x + y, debits)
                    sum_debit = round(sum_debit, digits)
                credits = map(lambda x: x['credit'] or 0.0, acc_lines)
                if credits:
                    sum_credit = reduce(lambda x, y: x + y, credits)
                    sum_credit = round(sum_credit, digits)
                balance = sum_debit - sum_credit
                report.update({'d': sum_debit, 'c': sum_credit, 'b': balance})

        reports = filter(lambda x: x.get('partners'), reports)
        if not reports:
            raise orm.except_orm(
                _('No Data Available'),
                _('No records found for your selection!'))

        report_date = datetime_field.context_timestamp(
            cr, uid, datetime.now(), context
            ).strftime(DEFAULT_SERVER_DATETIME_FORMAT)

        self.localcontext.update({
            'report_info': report_info,
            'report_date': report_date,
            'reports': reports,
            })
        super(partner_open_arap_print, self).set_context(
            objects, data, ids, report_type=report_type)
Example #33
0
    def set_context(self, objects, data, ids, report_type=None):
        cr = self.cr
        uid = self.uid
        context = self.context
        division = data['division']
        hutang_piutang_ksu = data['hutang_piutang_ksu']
        picking_type_code = data['picking_type_code']
        date_start_date = data['date_start_date']
        date_end_date = data['date_end_date']
        min_date_start_date = data['min_date_start_date']
        min_date_end_date = data['min_date_end_date']
        date_done_start_date = data['date_done_start_date']
        date_done_end_date = data['date_done_end_date']
        branch_ids = data['branch_ids']
        categ_ids = data['categ_ids']
        product_ids = data['product_ids']
        partner_ids = data['partner_ids']

        user_brw = self.pool.get('res.users').browse(cr, uid, uid)
        user_branch_type = user_brw.branch_type

        title_short_prefix = ''
        
        report_stock_movement = {
            'type': 'receivable',
            'title': '',
            'division':division,
            'hutang_piutang_ksu':hutang_piutang_ksu,
            'title_short': title_short_prefix + '' + _('Laporan Stock Movement')}
        if hutang_piutang_ksu == 'hutang':
            report_stock_movement['title_short'] = title_short_prefix + '' + 'Laporan Hutang KSU'
        elif hutang_piutang_ksu == 'piutang':
            report_stock_movement['title_short'] = title_short_prefix + '' + 'Laporan Piutang KSU'
        where_division = where_division2 = " 1=1 "
        if division and not hutang_piutang_ksu :
            where_division = " spick.division = '%s'" % str(division)
            where_division2 = " (spick.division = '%s' or spack.division = '%s')" % (str(division),str(division))
        where_picking_type_code = " 1=1 "
        if picking_type_code and not hutang_piutang_ksu:
            if picking_type_code == 'all' :
                where_picking_type_code = " (spt.code in ('incoming','outgoing','internal','interbranch_in','interbranch_out') or spack.id is not null)"
            elif picking_type_code == 'in' :
                where_picking_type_code = " (spt.code in ('incoming','interbranch_in') or (spack.id is not null and source_sloc.usage = 'inventory'))"
            elif picking_type_code == 'out' :
                where_picking_type_code = " (spt.code in ('outgoing','interbranch_out') or (spack.id is not null and sloc.usage = 'inventory'))"
            elif picking_type_code == 'incoming' :
                where_picking_type_code = " (spt.code = 'incoming' or (spack.id is not null and source_sloc.usage = 'inventory'))"
            elif picking_type_code == 'outgoing' :
                where_picking_type_code = " (spt.code = 'outgoing' or (spack.id is not null and sloc.usage = 'inventory'))"
            else :
                where_picking_type_code = " spt.code = '%s'" % str(picking_type_code)
        elif hutang_piutang_ksu == 'hutang':
            where_picking_type_code = " (spt.code in ('outgoing','interbranch_out') or (spack.id is not null and sloc.usage = 'inventory'))"
        elif hutang_piutang_ksu == 'piutang':
            where_picking_type_code = " (spt.code in ('incoming','interbranch_in') or (spack.id is not null and source_sloc.usage = 'inventory'))"
        where_date_start_date = where_date_start_date2 = " 1=1 "
        if date_start_date and not hutang_piutang_ksu:
            where_date_start_date = " date(spick.date) >= '%s'" % str(date_start_date)
            where_date_start_date2 = " date(spl.date) >= '%s'" % str(date_start_date)
        elif hutang_piutang_ksu == 'hutang':
            where_date_start_date = " (date(spick.date_done) >= '%s' or spick.date_done is null) and date(spick.create_date) <= '%s'" % (str(date_start_date),str(date_start_date))
            where_date_start_date2 = " ((date(spl.date) >= '%s' and spl.state = 'done') or spl.state not in ('done','cancel')) and date(spl.create_date) <= '%s'" % (str(date_start_date),str(date_start_date))
        elif hutang_piutang_ksu == 'piutang':
            where_date_start_date = " (date(spick.date_done) >= '%s' or spick.date_done is null) and date(spick.create_date) <= '%s'" % (str(date_start_date),str(date_start_date))
            where_date_start_date2 = " ((date(spl.date) >= '%s' and spl.state = 'done') or spl.state not in ('done','cancel')) and date(spl.create_date) <= '%s'" % (str(date_start_date),str(date_start_date))
        where_state = where_state2 = " 1=1 "
        if not hutang_piutang_ksu:
            where_state = " spack.state = 'posted' "
            where_state2 = " spl.state = 'done' "
        where_date_end_date = where_date_end_date2 = " 1=1 "
        if date_end_date and not hutang_piutang_ksu:
            where_date_end_date = " date(spick.date) <= '%s'" % str(date_end_date)
            where_date_end_date2 = " date(spl.date) <= '%s'" % str(date_end_date)
        where_min_date_start_date = where_min_date_start_date2 = " 1=1 "
        if min_date_start_date and not hutang_piutang_ksu:
            where_min_date_start_date = " date(spick.min_date) >= '%s'" % str(min_date_start_date)
            where_min_date_start_date2 = " date(spl.date_expected) >= '%s'" % str(min_date_start_date)
        where_min_date_end_date = where_min_date_end_date2 = " 1=1 "
        if min_date_end_date and not hutang_piutang_ksu:
            where_min_date_end_date = " date(spick.min_date) <= '%s'" % str(min_date_end_date)
            where_min_date_end_date2 = " date(spl.date_expected) <= '%s'" % str(min_date_end_date)
        where_date_done_start_date = where_date_done_start_date2 = " 1=1 "
        if date_done_start_date and not hutang_piutang_ksu:
            where_date_done_start_date = " date(spick.date_done) >= '%s'" % str(date_done_start_date)
            where_date_done_start_date2 = " date(spl.date) >= '%s' and spl.state = 'done'" % str(date_done_start_date)
        where_date_done_end_date = where_date_done_end_date2 = " 1=1 "
        if date_done_end_date and not hutang_piutang_ksu:
            where_date_done_end_date = " date(spick.date_done) <= '%s'" % str(date_done_end_date)
            where_date_done_end_date2 = " date(spl.date) <= '%s' and spl.state = 'done'" % str(date_done_end_date)
        where_branch_ids = where_branch_ids2 = " 1=1 "
        if branch_ids :
            where_branch_ids = " spick.branch_id in %s" % str(
                tuple(branch_ids)).replace(',)', ')')
            where_branch_ids2 = " spl.branch_id in %s" % str(
                tuple(branch_ids)).replace(',)', ')')
        where_product_ids = " 1=1 "
        if product_ids :
            where_product_ids = " product.id in %s" % str(
                tuple(product_ids)).replace(',)', ')')
        where_partner_ids = " 1=1 "
        if partner_ids :
            where_partner_ids = " spick.partner_id in %s" % str(
                tuple(partner_ids)).replace(',)', ')')
        where_categ_ids = " 1=1 "
        if categ_ids and where_product_ids == " 1=1 " :
            where_categ_ids = " prod_categ.id in %s" % str(
                tuple(categ_ids)).replace(',)', ')')
        
        query_stock_movement = "SELECT CONCAT(cast(spl.id as text),'-dym_stock_packing_line') as id_ai, spick.id as id_picking, 'pack' as object, spl.id as pack_line_id, prod_tmpl.categ_id as categ_id, " \
            "b.code as branch_code, b.name as branch_name, spick.division, spt.name as picking_type_name, spack.name as packing_name, spack.date as packing_date, " \
            "partner.default_code as partner_code, partner.name as partner_name, expedisi.default_code as ekspedisi_code, expedisi.name as ekspedisi_name, " \
            "product.name_template as prod_tmpl, pav.code as color, spl.engine_number as engine, spl.chassis_number as chassis, " \
            "spl.tahun_pembuatan as tahun, spl.quantity as qty, spack.state as packing_state, spick.origin as picking_origin, prod_categ.name as categ_name, product.default_code as internal_ref, " \
            "COALESCE(spick2.origin,'') as backorder, sloc.name as location, case when spl.ready_for_sale = true then 'RFS' else 'NRFS' end as status_rfs, COALESCE(bs.name, '') as branch_source, parent_sloc.name as parent_location, source_sloc.name as location_source, parent_source_sloc.name as parent_location_source " \
            "FROM " \
            "dym_stock_packing spack " \
            "inner join dym_stock_packing_line spl ON spack.id = spl.packing_id " \
            "left join stock_picking spick ON spick.id = spack.picking_id " \
            "left join stock_picking spick2 ON spick2.id = spick.backorder_id " \
            "left join dym_branch b on b.id = spick.branch_id " \
            "left join res_partner partner on partner.id = spick.partner_id " \
            "left join res_partner expedisi on expedisi.id = spack.expedition_id " \
            "left join product_product product on product.id = spl.product_id " \
            "left join product_attribute_value_product_product_rel pavpp ON product.id = pavpp.prod_id " \
            "left join product_attribute_value pav ON pavpp.att_id = pav.id " \
            "left join stock_picking_type spt ON spt.id = spick.picking_type_id " \
            "left join product_template prod_tmpl ON prod_tmpl.id = product.product_tmpl_id " \
            "left join product_category prod_categ ON prod_categ.id = prod_tmpl.categ_id " \
            "left join stock_location sloc ON sloc.id = spl.destination_location_id " \
            "left join stock_location parent_sloc ON parent_sloc.id = sloc.location_id " \
            "left join stock_location source_sloc ON source_sloc.id = spl.source_location_id " \
            "left join stock_location parent_source_sloc ON parent_source_sloc.id = source_sloc.location_id " \
            "left join dym_branch bs ON spack.branch_sender_id = bs.id " \
            "where ((spl.engine_number is not null and spick.division = 'Unit') or spick.division != 'Unit') and spl.quantity > 0 and " + where_state + " AND " + where_division + " AND " + where_picking_type_code + " AND " + where_date_start_date + " AND " + where_date_end_date + " AND " + where_min_date_start_date + " AND " + where_min_date_end_date + " AND " + where_date_done_start_date + " AND " + where_date_done_end_date + " AND " + where_branch_ids + " AND " + where_categ_ids + " AND " + where_product_ids + " AND " + where_partner_ids + " " \
            " UNION ALL " \
            "SELECT CONCAT(cast(spl.id as text),'-stock_move') as id_ai, spick.id as id_picking, 'move' as object, spl.id as pack_line_id, prod_tmpl.categ_id as categ_id, " \
            "b.code as branch_code, b.name as branch_name, " \
            "CASE WHEN spack.id is not null THEN spack.division " \
            "    WHEN spick.id is not null THEN spick.division " \
            "    ELSE '' " \
            "END as division, " \
            "CASE WHEN spack.id is not null and sloc.usage = 'inventory' THEN 'Delivery Orders' " \
            "    WHEN spack.id is not null and source_sloc.usage = 'inventory' THEN 'Receipts' " \
            "    ELSE spt.name " \
            "END as picking_type_name, " \
            "CASE WHEN spack.id is not null THEN spack.name " \
            "    WHEN spick.id is not null THEN spick.name " \
            "    ELSE '' " \
            "END as packing_name, " \
            "spl.date as packing_date, " \
            "partner.default_code as partner_code, partner.name as partner_name, '' as ekspedisi_code, '' as ekspedisi_name, " \
            "product.name_template as prod_tmpl, pav.code as color, lot.name as engine, CONCAT(lot.chassis_code, lot.chassis_no) as chassis, " \
            "lot.tahun as tahun, spl.product_uom_qty as qty, " \
            "CASE WHEN spack.id is not null THEN spack.state " \
            "    WHEN spick.id is not null THEN spick.state " \
            "    ELSE '' " \
            "END as packing_state, " \
            "spick.origin as picking_origin, prod_categ.name as categ_name, product.default_code as internal_ref, " \
            "COALESCE(spick2.origin,'') as backorder, sloc.name as location, case when sloc.usage = 'internal' then 'RFS' when sloc.usage in ('kpb','nrfs') then 'NRFS' else '' end as status_rfs, '' as branch_source, parent_sloc.name as parent_location, source_sloc.name as location_source, parent_source_sloc.name as parent_location_source " \
            "FROM " \
            "stock_move spl " \
            "left join stock_inventory spack ON spack.id = spl.inventory_id " \
            "left join stock_production_lot lot ON lot.id = spl.restrict_lot_id " \
            "left join stock_picking spick ON spick.id = spl.picking_id " \
            "left join stock_picking spick2 ON spick2.id = spick.backorder_id " \
            "left join dym_branch b on b.id = spl.branch_id " \
            "left join res_partner partner on partner.id = spick.partner_id " \
            "left join dym_stock_packing packing on spick.id = packing.picking_id " \
            "left join product_product product on product.id = spl.product_id " \
            "left join product_attribute_value_product_product_rel pavpp ON product.id = pavpp.prod_id " \
            "left join product_attribute_value pav ON pavpp.att_id = pav.id " \
            "left join stock_picking_type spt ON spt.id = spl.picking_type_id " \
            "left join product_template prod_tmpl ON prod_tmpl.id = product.product_tmpl_id " \
            "left join product_category prod_categ ON prod_categ.id = prod_tmpl.categ_id " \
            "left join stock_location sloc ON sloc.id = spl.location_dest_id " \
            "left join stock_location parent_sloc ON parent_sloc.id = sloc.location_id " \
            "left join stock_location source_sloc ON source_sloc.id = spl.location_id " \
            "left join stock_location parent_source_sloc ON parent_source_sloc.id = source_sloc.location_id " \
            "where ((lot.id is not null and (spack.division = 'Unit' or spick.division = 'Unit')) or (spack.division != 'Unit' or spick.division != 'Unit')) and spl.product_uom_qty > 0 and " + where_state2 + " AND " + where_division2 + " AND " + where_picking_type_code + " AND " + where_date_start_date2 + " AND " + where_date_end_date2 + " AND " + where_min_date_start_date2 + " AND " + where_min_date_end_date2 + " AND " + where_date_done_start_date2 + " AND " + where_date_done_end_date2 + " AND " + where_branch_ids2 + " AND " + where_categ_ids + " AND " + where_product_ids + " AND " + where_partner_ids + " and packing.id is null " \
            " ORDER BY branch_code " \
            
        move_selection = ""
        report_info = _('')
        move_selection += ""
        
        reports = [report_stock_movement]
        
        for report in reports:
            cr.execute(query_stock_movement)
            all_lines = cr.dictfetchall()
            picking_ids = []

            if all_lines:
                def lines_map(x):
                        x.update({'docname': x['branch_code']})
                map(lines_map, all_lines)
                for cnt in range(len(all_lines)-1):
                    if all_lines[cnt]['id_picking'] != all_lines[cnt+1]['id_picking']:
                        all_lines[cnt]['draw_line'] = 1
                    else:
                        all_lines[cnt]['draw_line'] = 0
                all_lines[-1]['draw_line'] = 1

                p_map = map(
                    lambda x: {
                        'no': 0,
                        'id_picking': str(x['id_picking']),
                        'id_ai': str(x['id_ai']),
                        'object': str(x['object']),
                        'pack_line_id': x['pack_line_id'],
                        'branch_code': str(x['branch_code'].encode('ascii','ignore').decode('ascii')) if x['branch_code'] != None else '',
                        'branch_name': str(x['branch_name'].encode('ascii','ignore').decode('ascii')) if x['branch_name'] != None else '',
                        'branch_source': str(x['branch_source'].encode('ascii','ignore').decode('ascii')) if x['branch_source'] != None else '',
                        'division': str(x['division'].encode('ascii','ignore').decode('ascii')) if x['division'] != None else '',
                        'picking_type_name': str(x['picking_type_name'].encode('ascii','ignore').decode('ascii')) if x['picking_type_name'] != None else '',
                        'internal_ref': str(x['internal_ref'].encode('ascii','ignore').decode('ascii')) if x['internal_ref'] != None else '',
                        'categ_name': str(x['categ_name'].encode('ascii','ignore').decode('ascii')) if x['categ_name'] != None else '',
                        'packing_name': str(x['packing_name'].encode('ascii','ignore').decode('ascii')) if x['packing_name'] != None else '',
                        'packing_date': str(x['packing_date'].encode('ascii','ignore').decode('ascii')) if x['packing_date'] != None else '',
                        'partner_code': str(x['partner_code'].encode('ascii','ignore').decode('ascii')) if x['partner_code'] != None else '',
                        'partner_name': str(x['partner_name'].encode('ascii','ignore').decode('ascii')) if x['partner_name'] != None else '',
                        'ekspedisi_code': str(x['ekspedisi_code'].encode('ascii','ignore').decode('ascii')) if x['ekspedisi_code'] != None else '',
                        'ekspedisi_name': str(x['ekspedisi_name'].encode('ascii','ignore').decode('ascii')) if x['ekspedisi_name'] != None else '',
                        'prod_tmpl': str(x['prod_tmpl'].encode('ascii','ignore').decode('ascii')) if x['prod_tmpl'] != None else '',
                        'color': str(x['color'].encode('ascii','ignore').decode('ascii')) if x['color'] != None else '',
                        'engine': str(x['engine'].encode('ascii','ignore').decode('ascii')) if x['engine'] != None else '',
                        'chassis': str(x['chassis'].encode('ascii','ignore').decode('ascii')) if x['chassis'] != None else '',
                        'tahun': str(x['tahun'].encode('ascii','ignore').decode('ascii')) if x['tahun'] != None else '',
                        'qty': x['qty'],
                        'packing_state': str(x['packing_state'].encode('ascii','ignore').decode('ascii')) if x['packing_state'] != None else '',
                        'picking_origin': str(x['picking_origin'].encode('ascii','ignore').decode('ascii')) if x['picking_origin'] != None else '',
                        'backorder': str(x['backorder'].encode('ascii','ignore').decode('ascii')) if x['backorder'] != None else '',
                        'location': str(x['location'].encode('ascii','ignore').decode('ascii')) if x['location'] != None else '',
                        'parent_location': str(x['parent_location'].encode('ascii','ignore').decode('ascii')) if x['parent_location'] != None else '',
                        'location_source': str(x['location_source'].encode('ascii','ignore').decode('ascii')) if x['location_source'] != None else '',
                        'parent_location_source': str(x['parent_location_source'].encode('ascii','ignore').decode('ascii')) if x['parent_location_source'] != None else '',
                        'status_rfs': str(x['status_rfs'].encode('ascii','ignore').decode('ascii')) if x['status_rfs'] != None else '',
                    },
                    
                    all_lines)
                for p in p_map:
                    if p['id_ai'] not in map(
                            lambda x: x.get('id_ai', None), picking_ids):
                        picking_ids.append(p)
                        packing_line = filter(
                            lambda x: x['id_ai'] == p['id_ai'], all_lines)
                        p.update({'lines': packing_line})
                        if packing_line[0]['object'] == 'pack':
                            line = self.pool.get('dym.stock.packing.line').browse(cr, uid, packing_line[0]['pack_line_id'])
                        elif packing_line[0]['object'] == 'move':
                            if user_branch_type == 'HO':
                                line = self.pool.get('stock.move').browse(cr, SUPERUSER_ID, packing_line[0]['pack_line_id'])
                            else:
                                line = self.pool.get('stock.move').browse(cr, uid, packing_line[0]['pack_line_id'])
                        if line.product_id.categ_id:
                            category = line.product_id.categ_id
                            if division == 'Unit':
                                while (category.parent_id and category.parent_id.bisnis_unit == False):
                                    category = category.parent_id
                            else:
                                flag = False
                                while (category.parent_id and flag == False):
                                    category = category.parent_id
                                    if category.bisnis_unit == True:
                                        flag = True
                            p.update({'categ_name': category.name})
                report.update({'picking_ids': picking_ids})

        reports = filter(lambda x: x.get('picking_ids'), reports)
        
        if not reports :
            reports = [{'picking_ids': [{
                        'no': 0,
                        'branch_code': 'NO DATA FOUND',
                        'branch_name': 'NO DATA FOUND',
                        'branch_source': 'NO DATA FOUND',
                        'division': 'NO DATA FOUND',
                        'categ_name': 'NO DATA FOUND',
                        'internal_ref': 'NO DATA FOUND',
                        'picking_type_name': 'NO DATA FOUND',
                        'packing_name': 'NO DATA FOUND',
                        'packing_date': 'NO DATA FOUND',
                        'partner_code': 'NO DATA FOUND',
                        'partner_name': 'NO DATA FOUND',
                        'ekspedisi_code': 'NO DATA FOUND',
                        'ekspedisi_name': 'NO DATA FOUND',
                        'prod_tmpl': 'NO DATA FOUND',
                        'color': 'NO DATA FOUND',
                        'engine': 'NO DATA FOUND',
                        'chassis': 'NO DATA FOUND',
                        'tahun': 'NO DATA FOUND',
                        'location': 'NO DATA FOUND',
                        'parent_location': 'NO DATA FOUND',
                        'location_source': 'NO DATA FOUND',
                        'parent_location_source': 'NO DATA FOUND',
                        'status_rfs': 'NO DATA FOUND',
                        'qty': 0,
                        'packing_state': 'NO DATA FOUND',
                        'picking_origin': 'NO DATA FOUND',
                        'backorder': 'NO DATA FOUND',}], 'title_short': 'Laporan Stock Movement', 'type': 'receivable', 'title': '', 'division':division, 'hutang_piutang_ksu':hutang_piutang_ksu}]
        
        report_date = datetime_field.context_timestamp(
            cr, uid, datetime.now(), context
            ).strftime(DEFAULT_SERVER_DATETIME_FORMAT)

        self.localcontext.update({
            'report_info': report_info,
            'report_date': report_date,
            'reports': reports,
            })
        objects = False
        super(dym_report_stock_movement_print, self).set_context(
            objects, data, ids, report_type)
Example #34
0
    def set_context(self, objects, data, ids, report_type=None):
        cr = self.cr
        uid = self.uid
        context = self.context
        division = data['division']
        start_date = data['start_date']
        end_date = data['end_date']
        trx_start_date = data['trx_start_date']
        trx_end_date = data['trx_end_date']
        status = data['status']
        branch_ids = data['branch_ids']
        partner_ids = data['partner_ids']
        account_ids = data['account_ids']
        journal_ids = data['journal_ids']
        segmen = data['segmen']
        branch_status = data['branch_status']
        detail_pembayaran = data['detail_pembayaran']
        where_end_datex = "av.date  <=  '%s' " % trx_end_date
        title_prefix = ''
        title_short_prefix = ''

        report_hutang = {
            'detail_pembayaran': detail_pembayaran,
            'type': 'payable',
            'title': '',
            'title_short': title_short_prefix + ', ' + _('Laporan Hutang')
        }
        #print "-----------", '"""+ str(start_date) + """'
        query_start = """SELECT distinct aml.id as id_aml, 
           aml.division as division, 
           rp.default_code as partner_code, 
           rp.name as partner_name, 
           a.code as account_code, 
           '' as account_sap, 
           aml.date as date_aml, 
           aml.date_maturity as due_date, 
           CURRENT_DATE - aml.date_maturity as overdue, 
           aml.reconcile_id as status, 
           aml.reconcile_partial_id as partial, 
           aml.debit as debit, 
           aml.credit as credit, 
           aml.name as name, 
           aml.ref as reference, 
           j.name as journal_name, 
           m.name as invoice_name, 
           '' as branch_code, 
           ai.supplier_invoice_number as supplier_invoice_number, 
           ai.document_date as supplier_invoice_date, 
               CASE WHEN aml.reconcile_id IS NOT NULL and  aml4.account_id in (2533) and aml2.amount - aml4.amount  < 0  then  aml2.amount   
                WHEN aml.reconcile_id IS NOT NULL and  aml4.account_id in (2533) and aml2.amount - aml4.amount  = 0  then  aml2.amount - aml4.amount 
		        WHEN aml.reconcile_id IS NOT NULL then  aml2.amount
                WHEN aml.reconcile_id IS NOT NULL and av.state is null THEN 0.0 
                WHEN aml.reconcile_partial_id IS NULL THEN aml.credit - aml.debit 
                WHEN count_credit <> 0 THEN  (aml3.credit - aml3.debit) / count_credit 
            ELSE 0 
            END as residual, 
            pt.name as payment_term,
            loa.branch as branch_x  
            FROM 
            account_move_line aml 
            LEFT JOIN (SELECT aml2.reconcile_partial_id, (select count(aml4.credit) from account_move_line aml4 where aml2.reconcile_partial_id = aml4.reconcile_partial_id and aml4.credit > 0
            and  aml4.date <='""" + str(
            trx_end_date
        ) + """') as count_credit, SUM(aml2.debit) as debit, SUM(aml2.credit) as credit FROM account_move_line aml2 
            WHERE aml2.reconcile_partial_id is not Null and  aml2.date <='""" + str(
            trx_end_date
        ) + """' GROUP BY aml2.reconcile_partial_id) aml3 
            on aml.reconcile_partial_id = aml3.reconcile_partial_id 
            LEFT JOIN account_move m ON m.id = aml.move_id 
            LEFT JOIN res_partner rp ON rp.id = aml.partner_id 
            LEFT JOIN account_account a ON a.id = aml.account_id 
            LEFT JOIN account_journal j ON j.id = aml.journal_id 
            LEFT JOIN account_invoice ai ON ai.move_id = aml.move_id 
            LEFT JOIN account_payment_term pt ON ai.payment_term = pt.id 
            LEFT JOIN account_voucher_line avl on aml.id = avl.move_line_id 
            LEFT JOIN account_voucher av ON avl.voucher_id = av.id 
            LEFT JOIN (select sum(credit- debit) amount,reconcile_id from account_move_line where date <= '""" + str(
            trx_end_date
        ) + """' and state <> 'cancel' group by reconcile_id) aml2 on aml.reconcile_id = aml2.reconcile_id
            LEFT JOIN (select sum(debit- credit) amount,move_id,account_id from account_move_line where date <= '""" + str(
            trx_end_date
        ) + """' and state <> 'cancel' and  account_id in (2533)  group by move_id,account_id) aml4 on aml.move_id = aml4.move_id
            LEFT JOIN (select dl.name,aa.name branch from dym_loan dl left join account_analytic_account aa on aa.id =  dl.analytic_3) loa on m.name = loa.name		
            where   right(m.name,10) <> '(Reversed)' AND m.state = 'posted'"""

        move_selection = ""
        report_info = _('')
        move_selection += ""

        query_end = ""
        if division:
            query_end += " AND aml.division = '%s'" % str(division)
        if start_date:
            query_end += " AND aml.date_maturity >= '%s'" % str(start_date)
        if end_date:
            query_end += " AND aml.date_maturity <= '%s'" % str(end_date)
        if trx_start_date:
            query_end += " AND aml.date >= '%s'" % str(trx_start_date)
        if trx_end_date:
            query_end += " AND aml.date <= '%s'" % str(trx_end_date)
        if status == 'reconciled':
            query_end += " AND aml.reconcile_id is not Null"
        elif status == 'outstanding':
            query_end += " AND aml.reconcile_id is Null"
        if partner_ids:
            query_end += " AND aml.partner_id in %s" % str(
                tuple(partner_ids)).replace(',)', ')')
        if account_ids:
            query_end += " AND aml.account_id in %s" % str(
                tuple(account_ids)).replace(',)', ')')
        if journal_ids:
            query_end += " AND aml.journal_id in %s" % str(
                tuple(journal_ids)).replace(',)', ')')
        reports = [report_hutang]
        query_order = ""
        add_line_x = ""

        print "=================", query_start + query_end + query_order
        for report in reports:
            cr.execute(query_start + query_end + query_order)

            all_lines = cr.dictfetchall()
            ids_aml = []

            if all_lines:
                p_map = map(
                    lambda x: {
                        'no':
                        0,
                        'id_aml':
                        x['id_aml'] if x['id_aml'] != None else '',
                        'payment_term':
                        x['payment_term'] if x['payment_term'] != None else '',
                        'branch_code':
                        x['branch_code'] if x['branch_code'] != None else '',
                        'division':
                        str(x['division'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['division'] != None else '',
                        'partner_code':
                        str(x['partner_code'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['partner_code'] != None else '',
                        'partner_name':
                        str(x['partner_name'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['partner_name'] != None else '',
                        'account_code':
                        str(x['account_code'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['account_code'] != None else '',
                        'invoice_name':
                        str(x['invoice_name'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['invoice_name'] != None else '',
                        'name':
                        str(x['name'].encode('ascii', 'ignore').decode('ascii')
                            ) if x['name'] != None else '',
                        'supplier_invoice_date':
                        str(x['supplier_invoice_date'].encode(
                            'ascii', 'ignore').decode('ascii'))
                        if x['supplier_invoice_date'] != None else '',
                        'supplier_invoice_number':
                        str(x['supplier_invoice_number'].encode(
                            'ascii', 'ignore').decode('ascii'))
                        if x['supplier_invoice_number'] != None else '',
                        'date_aml':
                        str(x['date_aml']) if x['date_aml'] != None else '',
                        'due_date':
                        str(x['due_date']) if x['due_date'] != None else '',
                        'overdue':
                        str(x['overdue']) if x['overdue'] != None and x[
                            'residual'] != None and x['residual'] > 0 else '',
                        'status':
                        'Outstanding'
                        if str(x['status']) == 'None' else 'Reconciled',
                        'tot_invoice':
                        x['credit'] - x['debit'],
                        #'saldo_awal': 0,
                        'amount_residual':
                        x['residual'] if x['residual'] != None else False,
                        'belum_jatuh_tempo':
                        (x['residual'] if x['residual'] != None else False)
                        if x['overdue'] <= 0 or x['overdue'
                                                  ] == None else False,
                        'overdue_1_30': (x['residual']
                                         if x['residual'] != None else False)
                        if x['overdue'] > 0 and x['overdue'] < 31 else False,
                        'overdue_31_60': (x['residual']
                                          if x['residual'] != None else False)
                        if x['overdue'] > 30 and x['overdue'] < 61 else False,
                        'overdue_61_90': (x['residual']
                                          if x['residual'] != None else False)
                        if x['overdue'] > 60 and x['overdue'] < 91 else False,
                        'overdue_91_n': (x['residual']
                                         if x['residual'] != None else False)
                        if x['overdue'] > 91 else False,
                        'reference':
                        str(x['reference'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['reference'] != None else '',
                        'branch_x':
                        str(x['branch_x'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['branch_x'] != None else '',
                        'journal_name':
                        str(x['journal_name'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['journal_name'] != None else '',
                    },
                    all_lines)
                for p in p_map:
                    if p['id_aml'] not in map(lambda x: x.get('id_aml', None),
                                              ids_aml):
                        account_move_lines = filter(
                            lambda x: x['id_aml'] == p['id_aml'], all_lines)
                        analytic_1 = ''
                        analytic_2 = ''
                        analytic_3 = ''
                        analytic_4 = ''
                        analytic_1_name = ''
                        analytic_2_name = ''
                        analytic_3_name = ''
                        analytic_4_name = ''
                        saldo_awal = 0
                        am = self.pool.get('account.move.line').browse(
                            cr, uid, account_move_lines[0]['id_aml'])
                        if am.debit > 0:
                            continue
                        analytic = am.analytic_account_id or ''
                        saldo_awal = 0
                        branch_code = ''
                        branch_name = ''
                        branch = False
                        branch_status_1 = ''
                        branch_name = ''
                        branch_code = ''
                        if analytic:
                            if analytic.type == 'normal':
                                if analytic.segmen == 1 and analytic_1 == '':
                                    analytic_1_name = analytic.name
                                    analytic_1 = analytic.code
                                if analytic.segmen == 2 and analytic_2 == '':
                                    analytic_2_name = analytic.name
                                    analytic_2 = analytic.code
                                if analytic.segmen == 3 and analytic_3 == '':
                                    analytic_3_name = analytic.name
                                    analytic_3 = analytic.code
                                    branch = analytic.sudo().branch_id
                                    branch_name = branch.name
                                    branch_code = branch.code
                                    branch_status_1 = branch.branch_status
                                    branch_id = branch.id
                                if analytic.segmen == 4 and analytic_4 == '':
                                    analytic_4_name = analytic.name
                                    analytic_4 = analytic.code
                                    analytic_id = analytic
                            while (analytic.parent_id):
                                analytic = analytic.parent_id
                                if analytic.type == 'normal':
                                    if analytic.segmen == 1 and analytic_1 == '':
                                        analytic_1_name = analytic.name
                                        analytic_1 = analytic.code
                                    if analytic.segmen == 2 and analytic_2 == '':
                                        analytic_2_name = analytic.name
                                        analytic_2 = analytic.code
                                    if analytic.segmen == 3 and analytic_3 == '':
                                        analytic_3_name = analytic.name
                                        analytic_3 = analytic.code
                                        branch = analytic.sudo().branch_id
                                        branch_name = branch.name
                                        branch_code = branch.code
                                        branch_status_1 = branch.branch_status
                                        branch_id = branch.id
                                    if analytic.segmen == 4 and analytic_4 == '':
                                        analytic_4_name = analytic.name
                                        analytic_4 = analytic.code
                                        analytic_id == analytic
                            if (branch and branch_ids
                                    and branch.id not in branch_ids) or (
                                        branch and branch_status and
                                        branch_status != branch.branch_status):
                                continue
                            analytic_2_branch = analytic_2
                            if analytic_2 in ['210', '220', '230']:
                                if branch_status_1 == 'H123':
                                    analytic_2_branch = analytic_2[:2] + '1'
                                elif branch_status_1 == 'H23':
                                    analytic_2_branch = analytic_2[:2] + '2'
                                else:
                                    analytic_2_branch = analytic_2
                            analytic_combination = analytic_1 + '/' + analytic_2_branch + '/' + analytic_3 + '/' + analytic_4
                            p.update({'lines': account_move_lines})
                            p.update({'analytic_1': analytic_1_name})
                            p.update({'analytic_2': analytic_2_name})
                            p.update({'analytic_3': analytic_3_name})
                            p.update({'analytic_4': analytic_4_name})
                            p.update({'branch_status': branch_status_1})
                            p.update({'branch_name': branch_name})
                            p.update({'branch_code': branch_code})
                            p.update(
                                {'analytic_combination': analytic_combination})
                            acc_number = ''
                            bank = ''
                            an_rek = ''
                            if am.partner_id.bank_ids:
                                acc_number = am.partner_id.bank_ids[
                                    0].acc_number
                                bank = am.partner_id.bank_ids[0].bank.name
                                an_rek = am.partner_id.bank_ids[0].owner_name
                            p.update({'acc_number': acc_number})
                            p.update({'bank': bank})
                            p.update({'an_rek': an_rek})
                            tgl_retur = ''
                            no_retur = ''
                            total_retur = 0
                            p.update({'tgl_retur': tgl_retur})
                            p.update({'no_retur': no_retur})
                            #p.update({'total_retur': total_retur})
                            if am.sudo().invoice:
                                rb_ids = self.pool.get(
                                    'dym.retur.beli').search(
                                        cr, uid,
                                        [('consolidate_id.invoice_id', '=',
                                          am.sudo().invoice.id),
                                         ('state', 'in', [
                                             'approved', 'except_picking',
                                             'except_invoice', 'done'
                                         ])])
                                if rb_ids:
                                    rb = self.pool.get(
                                        'dym.retur.beli').browse(
                                            cr, uid, rb_ids)
                                    tgl_retur = ', '.join(rb.mapped('date'))
                                    no_retur = ', '.join(rb.mapped('name'))
                                    total_retur = sum(x.amount_total
                                                      for x in rb)
                            if total_retur > 0:
                                if p['tot_invoice'] > total_retur:
                                    p.update({'tot_invoice': p['tot_invoice']})
                                    #p.update({'tot_invoice': p['tot_invoice'] - total_retur})
                                if p['overdue_1_30'] > total_retur:
                                    p.update({
                                        'overdue_1_30':
                                        p['overdue_1_30'] - total_retur
                                    })
                                if p['overdue_31_60'] > total_retur:
                                    p.update({
                                        'overdue_31_60':
                                        p['overdue_31_60'] - total_retur
                                    })
                                if p['overdue_61_90'] > total_retur:
                                    p.update({
                                        'overdue_61_90':
                                        p['overdue_61_90'] - total_retur
                                    })
                                if p['overdue_91_n'] > total_retur:
                                    p.update({
                                        'overdue_91_n':
                                        p['overdue_91_n'] - total_retur
                                    })

                            p.update({'total_retur': total_retur})
                            p.update({'pay_no': ''})
                            p.update({'pay_date': ''})
                            p.update({'pay_amount': 0})
                            p.update({'pay_pindahan': 0})
                            p.update({'pay_retur': 0})
                            p.update({'saldo_awal': 0})
                            #9999p.update({'branch_x': ''})
                            if "OPBAL" in p[
                                    'journal_name'] and p['tot_invoice'] > 0:
                                p.update({'saldo_awal': p['tot_invoice']})
                                p.update({'tot_invoice': 0})
                            else:
                                p.update({'saldo_awal': 0})
                            ids_aml.append(p)
                            index = len(ids_aml) - 1
                            partial_lines = lines = self.pool.get(
                                'account.move.line').browse(cr, uid, [])
                            if am.sudo().reconcile_id:
                                lines |= am.sudo().reconcile_id.line_id
                            elif am.sudo().reconcile_partial_id:
                                lines |= am.sudo(
                                ).reconcile_partial_id.line_partial_ids
                            partial_lines += am
                            payments = (lines -
                                        partial_lines).sorted().filtered(
                                            lambda r: r.debit > 0)
                            if payments and detail_pembayaran == True:
                                add_line = []
                                move_ids = []
                                for pay in payments:
                                    if pay.move_id.id in move_ids:
                                        continue
                                    move_ids.append(pay.move_id.id)
                                    voucher_id = self.pool.get(
                                        'account.voucher').search(
                                            cr, uid,
                                            [('move_id', '=', pay.move_id.id)])
                                    if voucher_id:
                                        bayar = 0
                                        pindahan = 0
                                        retur = 0
                                        voucher = self.pool.get(
                                            'account.voucher').browse(
                                                cr, uid, voucher_id)
                                        if voucher.amount != 0:
                                            if voucher.type == 'receipt':
                                                bayar += voucher.amount * -1
                                            else:
                                                bayar += voucher.amount

                                            if voucher.amount == pay.debit:
                                                pay_amount_res = self.get_pay_array(
                                                    cr, uid, voucher.number,
                                                    voucher.date,
                                                    1 * voucher.amount, 0, 0,
                                                    account_move_lines)
                                            else:
                                                pay_amount_res = self.get_pay_array(
                                                    cr, uid, voucher.number,
                                                    voucher.date,
                                                    1 * pay.debit, 0, 0,
                                                    account_move_lines)
                                            add_line.append(pay_amount_res)
                                        for voucher_line in voucher.line_ids.filtered(
                                                lambda r: r.type == 'cr' and r.
                                                amount > 0):
                                            rb_name = voucher_line.move_line_id.sudo(
                                            ).invoice.origin or ''
                                            retur_beli_id = self.pool.get(
                                                'dym.retur.beli').search(
                                                    cr, uid,
                                                    [('name', 'in',
                                                      rb_name.split(' '))])
                                            if not retur_beli_id:
                                                pindahan += voucher_line.amount
                                                pay_amount_res = self.get_pay_array(
                                                    cr, uid, voucher_line.
                                                    move_line_id.move_id.name,
                                                    voucher.date,
                                                    voucher_line.amount, 0, 0,
                                                    account_move_lines)
                                                add_line.append(pay_amount_res)
                                    else:
                                        if pay.move_id.model == 'dym.loan':
                                            pay_amount_res = self.get_pay_array(
                                                cr, uid, pay.move_id.name,
                                                pay.date, 0, pay.debit, 0,
                                                account_move_lines)
                                            add_line.append(pay_amount_res)
                                        else:
                                            pay_amount_res = self.get_pay_array(
                                                cr, uid, pay.move_id.name,
                                                pay.date, pay.debit, 0, 0,
                                                account_move_lines)
                                            add_line.append(pay_amount_res)

                                        #print 'vvvvvvvvvvvv',p['reference']
                                        if p['reference'][:3] == 'LOA':
                                            ref = p['reference'] + ' (Reversed)'
                                            acc = str(
                                                tuple(account_ids)).replace(
                                                    ',)', ')')
                                            move_a = self.pool.get(
                                                'account.move.line').search(
                                                    cr,
                                                    uid, [('ref', '=', ref),
                                                          ('debit', '>', 0)],
                                                    limit=1)
                                            move_x = self.pool.get(
                                                'account.move.line').browse(
                                                    cr, uid, move_a)
                                            #print 'ddddddddddddd', move_x.id,ref,acc
                                            if move_x.id:
                                                pay_amount_res = self.get_pay_array(
                                                    cr, uid, move_x.ref,
                                                    move_x.date,
                                                    -1 * move_x.debit, 0, 0,
                                                    account_move_lines)
                                                add_line.append(pay_amount_res)

                                        move_a = self.pool.get(
                                            'account.move').search(
                                                cr, uid,
                                                [('reverse_from_id', '=',
                                                  pay.move_id.id)])
                                        move_x = self.pool.get(
                                            'account.move').browse(
                                                cr, uid, move_a)
                                        #print pay.move_id.id, move_x.ids
                                        if move_x.id:
                                            move_rec = self.pool.get(
                                                'account.move.line').search(
                                                    cr, uid, [('move_id', '=',
                                                               move_x.id)])
                                            move_y = self.pool.get(
                                                'account.move.line').browse(
                                                    cr, uid, move_rec)
                                            for moves in move_y:
                                                #print moves.name,'eeeeeeeeeeeee'
                                                if moves.credit > 0:
                                                    #print moves.analytic_account_id.id, 'zzzz', pay.analytic_account_id.id
                                                    if moves.reconcile_id.id != pay.reconcile_id.id and moves.analytic_account_id.id == pay.analytic_account_id.id:
                                                        pay_amount_res = self.get_pay_array(
                                                            cr, uid,
                                                            move_x.name,
                                                            move_x.date,
                                                            -1 * pay.debit, 0,
                                                            0,
                                                            account_move_lines)
                                                        add_line.append(
                                                            pay_amount_res)
                                                        move_rec2 = self.pool.get(
                                                            'account.move.line'
                                                        ).search(
                                                            cr,
                                                            uid,
                                                            [('reconcile_id',
                                                              '=', moves.
                                                              reconcile_id.id),
                                                             ('credit', '>', 0)
                                                             ],
                                                            limit=1,
                                                            order='id desc')
                                                        move_y2 = self.pool.get(
                                                            'account.move.line'
                                                        ).browse(
                                                            cr, uid, move_rec2)
                                                        pay_amount_res = self.get_pay_array(
                                                            cr, uid,
                                                            move_y2.ref,
                                                            move_y2.date,
                                                            pay.debit, 0, 0,
                                                            account_move_lines)
                                                        add_line.append(
                                                            pay_amount_res)

                                if add_line:
                                    if str(trx_end_date) >= pay.date:
                                        ids_aml += add_line

                report.update({'ids_aml': ids_aml})
        reports = filter(lambda x: x.get('ids_aml'), reports)

        if not reports:
            reports = [{
                'title_short':
                'Laporan Hutang',
                'type':
                'payable',
                'ids_aml': [{
                    'reference': 'NO DATA FOUND',
                    'acc_number': 'NO DATA FOUND',
                    'tgl_retur': 'NO DATA FOUND',
                    'no_retur': 'NO DATA FOUND',
                    'payment_term': 'NO DATA FOUND',
                    'total_retur': 0,
                    'bank': 'NO DATA FOUND',
                    'an_rek': 'NO DATA FOUND',
                    'supplier_invoice_number': 'NO DATA FOUND',
                    'supplier_invoice_date': 'NO DATA FOUND',
                    'tot_invoice': 0,
                    'saldo_awal': 0,
                    'date_aml': 'NO DATA FOUND',
                    'partner_code': 'NO DATA FOUND',
                    'no': 0,
                    'branch_code': 'NO DATA FOUND',
                    'branch_name': 'NO DATA FOUND',
                    'amount_residual': 0,
                    'journal_name': 'NO DATA FOUND',
                    'status': 'NO DATA FOUND',
                    'division': 'NO DATA FOUND',
                    'belum_jatuh_tempo': 0,
                    'id_aml': 'NO DATA FOUND',
                    'due_date': 'NO DATA FOUND',
                    'overdue_31_60': 0,
                    'partner_name': 'NO DATA FOUND',
                    'overdue_1_30': 0,
                    'overdue_61_90': 0,
                    'overdue_91_n': 0,
                    'invoice_name': 0,
                    'pay_no': 'NO DATA FOUND',
                    'pay_date': 'NO DATA FOUND',
                    'pay_amount': 0,
                    'pay_pindahan': 0,
                    'pay_retur': 0,
                    'name': 'NO DATA FOUND',
                    'account_code': 0,
                    'analytic_1': 'NO DATA FOUND',
                    'analytic_2': 'NO DATA FOUND',
                    'analytic_3': 'NO DATA FOUND',
                    'analytic_4': 'NO DATA FOUND',
                    'analytic_combination': 'NO DATA FOUND',
                    'branch_status': 'NO DATA FOUND',
                    'overdue': 'NO DATA FOUND',
                    'branch_x': 'NO DATA FOUND'
                }],
                'title':
                '',
                'detail_pembayaran':
                detail_pembayaran
            }]

        report_date = datetime_field.context_timestamp(
            cr, uid, datetime.now(),
            context).strftime(DEFAULT_SERVER_DATETIME_FORMAT)

        self.localcontext.update({
            'report_info': report_info,
            'report_date': report_date,
            'reports': reports,
        })
        objects = False
        super(dym_report_hutang_print,
              self).set_context(objects, data, ids, report_type)
Example #35
0
    def set_context(self, objects, data, ids, report_type=None):
        cr = self.cr
        uid = self.uid
        context = self.context
        branch_ids = data['branch_ids']
        journal_ids = data['journal_ids']
        status = data['status']
        start_date = data['start_date']
        end_date = data['end_date']
        option = data['option']

        title_prefix = ''
        title_short_prefix = ''

        report_cash_non_status = {
            'type': 'Cash_non_status',
            'title': '',
            'title_short': _('laporan'),
            'start_date': start_date,
            'end_date': end_date,
            'option': option
        }

        where_option = " 1=1 "
        where_type = " 1=1 "
        if option == 'All Non Petty Cash':
            where_option = " j.type in ('bank','cash','edc','situation') "
            where_type = " a.type = 'liquidity' "
        elif option == 'Cash':
            where_option = " j.type in ('cash','situation') "
            where_type = " a.type = 'liquidity' "
        elif option == 'EDC':
            where_option = " j.type in ('edc','situation') "
            where_type = " a.type = 'receivable' "
        elif option == 'Bank':
            where_option = " j.type in ('bank','situation') "
            where_type = " a.type = 'liquidity' "
        elif option == 'Petty Cash':
            where_option = " j.type in ('pettycash','situation') "
            where_type = " a.type = 'liquidity' "

        where_branch = " 1=1 "
        if branch_ids:
            where_branch = " b.id in %s " % str(tuple(branch_ids)).replace(
                ',)', ')')
        else:
            area_user = self.pool.get('res.users').browse(cr, uid,
                                                          uid).branch_ids
            branch_ids_user = [b.id for b in area_user]
            where_branch = " b.id in %s " % str(tuple(branch_ids_user))

        where_journal = " 1=1 "
        if journal_ids:
            where_journal = " j.id  in %s " % str(tuple(journal_ids)).replace(
                ',)', ')')

        where_status = " 1=1 "
        if status == 'outstanding':
            where_status = " aml.reconcile_id is Null "
        elif status == 'reconcile':
            where_status = " aml.reconcile_id is not Null "

        where_start_date = " 1=1 "
        where_end_date = " 1=1 "
        if start_date:
            where_start_date = " aml.date >= '%s' " % start_date
        if end_date:
            where_end_date = " aml.date <= '%s' " % end_date
        user = self.pool.get('res.users').browse(cr, uid, uid)
        timezone = user.tz or 'Asia/Jakarta'
        if timezone == 'Asia/Jayapura':
            tz = '9'
        elif timezone == 'Asia/Pontianak':
            tz = '8'
        else:
            tz = '7'
        query_cash_non_status = "SELECT aml.date as tanggal, b.code as branch_code, to_char(am.create_date + interval '"+tz+" hours', 'HH12:MI AM') as Jam, "\
        "k.name as kwitansi_name, a.code as account_code, aml.name as keterangan, aml.debit - aml.credit as balance, "\
        "j.type as journal_type, am.name as scr "\
        "FROM account_move_line aml "\
        "LEFT JOIN account_move am ON am.id = aml.move_id "\
        "LEFT JOIN account_journal j ON j.id = aml.journal_id "\
        "LEFT JOIN account_account a ON a.id = aml.account_id "\
        "LEFT JOIN dym_register_kwitansi_line k ON k.id = aml.kwitansi_id "\
        "LEFT JOIN dym_branch b ON b.id = aml.branch_id "\
        "WHERE "+where_branch+" AND "+where_type+" AND "+where_journal+" AND "+where_status+" AND "+where_start_date+" AND "+where_end_date+" AND "+where_option+" "\
        "ORDER BY b.code, aml.date "

        move_selection = ""
        report_info = _('')
        move_selection += ""
        reports = [report_cash_non_status]
        for report in reports:
            cr.execute(query_cash_non_status)
            all_lines = cr.dictfetchall()
            move_lines = []
            if all_lines:

                p_map = map(
                    lambda x: {
                        'no':
                        0,
                        'branch_code':
                        x['branch_code'],
                        'tanggal':
                        x['tanggal'],
                        'jam':
                        x['jam'],
                        'kwitansi_name':
                        x['kwitansi_name'],
                        'account_code':
                        x['account_code'],
                        'keterangan':
                        x['keterangan'].encode('ascii', 'ignore').decode(
                            'ascii') if x['keterangan'] != None else '',
                        'tunai':
                        x['balance'] if x['journal_type'] == 'cash' else 0.0,
                        'bank_check':
                        x['balance'] if x['journal_type'] == 'bank' else 0.0,
                        'edc':
                        x['balance'] if x['journal_type'] == 'edc' else 0.0,
                        'total':
                        x['balance'],
                        'move_name':
                        x['scr'],
                    }, all_lines)

                report.update({'move_lines': p_map})

        reports = filter(lambda x: x.get('move_lines'), reports)
        if not reports:
            reports = [{
                'title_short':
                'laporan',
                'type':
                'Cash_non_status',
                'start_date':
                start_date,
                'end_date':
                end_date,
                'option':
                option,
                'move_lines': [{
                    'no': 0,
                    'branch_code': 'NO DATA FOUND',
                    'tanggal': 'NO DATA FOUND',
                    'jam': 'NO DATA FOUND',
                    'kwitansi_name': 'NO DATA FOUND',
                    'account_code': 0,
                    'keterangan': 'NO DATA FOUND',
                    'tunai': 0.0,
                    'bank_check': 0.0,
                    'edc': 0.0,
                    'total': 0.0,
                    'move_name': 'NO DATA FOUND',
                }],
                'title':
                ''
            }]
        report_date = datetime_field.context_timestamp(
            cr, uid, datetime.now(),
            context).strftime(DEFAULT_SERVER_DATETIME_FORMAT)

        self.localcontext.update({
            'report_info': report_info,
            'report_date': report_date,
            'reports': reports,
        })
        objects = False
        super(dym_cash_non_status_report_print,
              self).set_context(objects, data, ids, report_type)
Example #36
0
    def set_context(self, objects, data, ids, report_type=None):
        cr = self.cr
        uid = self.uid
        context = self.context
        section_id = data['section_id']
        user_id = data['user_id']
        product_ids = data['product_ids']
        start_date = data['start_date']
        end_date = data['end_date']
        partner_komisi_id = data['partner_komisi_id']
        proposal_id = data['proposal_id']
        hutang_komisi_id = data['hutang_komisi_id']
        state = data['state']
        branch_ids = data['branch_ids']
        finco_ids = data['finco_ids']
        segmen = data['segmen']
        branch_status = data['branch_status']

        title_short_prefix = ''

        report_penjualantax = {
            'type': '',
            'title': '',
            'title_short':
            title_short_prefix + ', ' + _('Laporan Penjualan Tax')
        }

        where_section_id = " 1=1 "
        if section_id:
            where_section_id = " dso.section_id = '%s'" % str(section_id)
        where_user_id = " 1=1 "
        if user_id:
            where_user_id = " dso.employee_id = '%s'" % str(user_id)
        where_partner_komisi_id = " 1=1 "
        if partner_komisi_id:
            where_partner_komisi_id = " dso.partner_komisi_id = '%s'" % str(
                partner_komisi_id)
        where_hutang_komisi_id = " 1=1 "
        if hutang_komisi_id:
            where_hutang_komisi_id = " dso.hutang_komisi_id = '%s'" % str(
                hutang_komisi_id)
        where_proposal_id = " 1=1 "
        if proposal_id:
            where_proposal_id_id = " dso.proposal_id = '%s'" % str(proposal_id)
        where_product_ids = " 1=1 "
        if product_ids:
            where_product_ids = " dsol.product_id in %s" % str(
                tuple(product_ids)).replace(',)', ')')
        where_start_date = " 1=1 "
        if start_date:
            where_start_date = " dso.date_order >= '%s'" % str(start_date)
        where_end_date = " 1=1 "
        if end_date:
            where_end_date = " dso.date_order <= '%s'" % str(end_date)
        where_state = " 1=1 "
        if state in ['progress', 'done']:
            where_state = " dso.state = '%s'" % str(state)
        else:
            where_state = " dso.state in ('progress','done')"
        where_branch_ids = " 1=1 "
        if branch_ids:
            where_branch_ids = " dso.branch_id in %s" % str(
                tuple(branch_ids)).replace(',)', ')')
        where_finco_ids = " 1=1 "
        if finco_ids:
            where_finco_ids = " dso.finco_id in %s" % str(
                tuple(finco_ids)).replace(',)', ')')

        query_penjualantax = "select dso.id as id_dso, " \
            "COALESCE(drsl.name, '') as no_registrasi, " \
            "round(COALESCE(dsol.price_subtotal,0) + round(dsol.price_subtotal*0.1,2) + COALESCE(dsol.price_bbn,0)) as piutang_total, " \
            "round(COALESCE(dsol.price_subtotal,0) + round(dsol.price_subtotal*0.1,2)) as total, " \
            "COALESCE(b.branch_status,'') as branch_status, " \
            "COALESCE(b.code,'') as branch_code, " \
            "COALESCE(spk.name,'') as spk_name, " \
            "COALESCE(dcp.name,'') as cabang_partner, " \
            "COALESCE(b.name,'') as branch_name, " \
            "COALESCE(md.default_code,'') as md_code, " \
            "COALESCE(dso.name,'') as name, " \
            "CASE WHEN dso.state = 'progress' THEN 'Sales Memo' " \
            "    WHEN dso.state = 'done' THEN 'Done' " \
            "    WHEN dso.state IS NULL THEN '' " \
            "    ELSE dso.state " \
            "END as state, " \
            "dso.date_order as date_order, " \
            "COALESCE(finco.name,'Cash') as finco_code, " \
            "CASE WHEN dso.is_cod = TRUE THEN 'COD' " \
            "    ELSE 'Reguler' " \
            "END as is_cod, " \
            "sm.id as stock_move_non_unit_id, " \
            "COALESCE(sales_koor.name,'') as sales_koor_name, " \
            "COALESCE(sales.name,'') as sales_name, " \
            "COALESCE(job.name,'') as job_name, " \
            "COALESCE(cust.default_code,'') as cust_code, " \
            "COALESCE(cust.name,'') as cust_name, " \
            "COALESCE(cust.npwp,'Non PKP') as pkp, " \
            "COALESCE(product.name_template,'') as product_name, COALESCE(pav.code,'') as pav_code, COALESCE(dsol.product_qty,0) as product_qty, " \
            "COALESCE(lot.name,'') as lot_name, COALESCE(lot.chassis_no,'') as lot_chassis, " \
            "COALESCE(dsol.price_unit,0) as price_unit, " \
            "COALESCE(dsol.discount_po,0) as discount_po, COALESCE(dsol_disc.ps_dealer,0) as ps_dealer, COALESCE(dsol_disc.ps_ahm,0) as ps_ahm, COALESCE(dsol_disc.ps_md,0) as ps_md, COALESCE(dsol_disc.ps_finco,0) as ps_finco, " \
            "COALESCE(dsol_disc.ps_dealer,0)+COALESCE(dsol_disc.ps_ahm,0)+COALESCE(dsol_disc.ps_md,0)+COALESCE(dsol_disc.ps_finco,0) as ps_total, " \
            "COALESCE(dsol.price_unit/1.1,0) as sales, " \
            "COALESCE(dsol.discount_po/1.1,0) as disc_reg, COALESCE(dsol_disc.discount_pelanggan/1.1,0) as disc_quo, COALESCE(dsol_disc.discount_pelanggan) as disc_quo_incl_tax, " \
            "COALESCE(dsol.discount_po/1.1,0)+COALESCE(dsol_disc.discount_pelanggan/1.1,0) as disc_total, " \
            "COALESCE(dsol.price_subtotal,0) as price_subtotal, round(dsol.price_subtotal*0.1,2) as PPN, COALESCE(dsol.force_cogs,0) as force_cogs, " \
            "COALESCE(dso.customer_dp,0) as piutang_dp, " \
            "CASE WHEN finco.name = 'PT. ADIRA DINAMIKA MULTI FINANCE TBK.' THEN round((COALESCE(dsol.price_unit,0) + COALESCE(dsol.price_bbn,0) - COALESCE(dsol.discount_po,0))) - COALESCE(dso.customer_dp,0) " \
            "     ELSE round(COALESCE(dsol.price_unit,0) + COALESCE(dsol.price_bbn,0) - COALESCE(dsol.discount_po,0) - COALESCE(dsol_disc.discount_pelanggan,0)) - COALESCE(dso.customer_dp,0) " \
            "END as piutang, " \
            "COALESCE(dsol.price_subtotal,0)-COALESCE(dsol.force_cogs,0) as gp_dpp_minus_hpp, " \
            "COALESCE(dsol.price_subtotal,0)-COALESCE(dsol.force_cogs,0)+COALESCE(dsol_disc.ps_ahm,0)+COALESCE(dsol_disc.ps_md,0)+COALESCE(dsol_disc.ps_finco,0) as gp_unit, " \
            "COALESCE(dsol.price_bbn,0) as price_bbn, COALESCE(dsol.price_bbn_beli,0) as price_bbn_beli, COALESCE(dsol.price_bbn,0)-COALESCE(dsol.price_bbn_beli,0) as gp_bbn, " \
            "(COALESCE(dsol.price_subtotal,0)-COALESCE(dsol.force_cogs,0)+COALESCE(dsol_disc.ps_ahm,0)+COALESCE(dsol_disc.ps_md,0)+COALESCE(dsol_disc.ps_finco,0))+(COALESCE(dsol.price_bbn,0)-COALESCE(dsol.price_bbn_beli,0)) as gp_total, " \
            "0 as pph_komisi, " \
            "COALESCE(dsol.amount_hutang_komisi,0) as amount_hutang_komisi, " \
            "COALESCE(dsol.insentif_finco/1.1,0) as insentif_finco, insentif_finco as dpp_insentif_finco, " \
            "COALESCE(dsol.discount_po/1.1,0)+COALESCE(dsol_disc.ps_dealer/1.1,0)+COALESCE(amount_hutang_komisi,0) as beban_cabang, " \
            "COALESCE(prod_category.name,'') as categ_name, " \
            "COALESCE(prod_category2.name,'') as categ2_name, " \
            "COALESCE(prod_template.series,'') as prod_series, " \
            "COALESCE(fp.name,'') as faktur_pajak, " \
            "COALESCE(medi.name,'') as partner_komisi_id, " \
            "COALESCE(hk.name,'') as hutang_komisi_id, " \
            "CONCAT(pro.number, ' ', pro.name) as proposal_id, " \
            "CASE WHEN sp.state = 'draft' THEN 'Draft' " \
            "     WHEN sp.state = 'cancel' THEN 'Cancelled' " \
            "     WHEN sp.state = 'waiting' THEN 'Waiting Another Operation' " \
            "     WHEN sp.state = 'confirmed' THEN 'Waiting Availability' " \
            "     WHEN sp.state = 'partially_available' THEN 'Partially Available' " \
            "     WHEN sp.state = 'assigned' THEN 'Ready to Transfer' " \
            "     WHEN sp.state = 'done' THEN 'Transferred' " \
            "     ELSE sp.state " \
            "END as state_picking, " \
            "sp.name as oos_number " \
            "from dealer_sale_order dso " \
            "inner join dealer_spk spk on spk.dealer_sale_order_id = dso.id " \
            "inner join dealer_sale_order_line dsol on dsol.dealer_sale_order_line_id = dso.id " \
            "left join stock_picking sp on sp.origin = dso.name " \
            "left join stock_move sm ON sm.dealer_sale_order_line_id = dsol.id and sm.state not in ('done','cancel','draft') and sm.product_id != dsol.product_id " \
            "left join dym_hutang_komisi hk ON dsol.hutang_komisi_id = hk.id " \
            "left join res_partner medi ON dso.partner_komisi_id = medi.id " \
            "left join dym_proposal_event pro ON dso.proposal_id = pro.id " \
            "left join dym_branch b ON dso.branch_id = b.id " \
            "left join res_partner md ON b.default_supplier_id = md.id " \
            "left join res_partner finco ON dso.finco_id = finco.id " \
            "left join hr_employee employee ON dso.employee_id = employee.id " \
            "left join resource_resource sales ON employee.resource_id = sales.id " \
            "left join hr_job job ON employee.job_id = job.id " \
            "left join crm_case_section sales_team ON dso.section_id = sales_team.id " \
            "left join hr_employee sales_leader ON sales_leader.id = sales_team.user_id " \
            "left join resource_resource sales_koor ON sales_leader.resource_id = sales_koor.id " \
            "left join res_partner cust ON dso.partner_id = cust.id " \
            "left join product_product product ON dsol.product_id = product.id " \
            "left join product_attribute_value_product_product_rel pavpp ON product.id = pavpp.prod_id " \
            "left join product_attribute_value pav ON pavpp.att_id = pav.id " \
            "left join product_template prod_template ON product.product_tmpl_id = prod_template.id " \
            "left join product_category prod_category ON prod_template.categ_id = prod_category.id " \
            "left join product_category prod_category2 ON prod_category.parent_id = prod_category2.id " \
            "left join stock_production_lot lot ON dsol.lot_id = lot.id " \
            "left join dym_faktur_pajak_out fp ON dso.faktur_pajak_id = fp.id " \
            "left join dym_cabang_partner dcp ON dcp.id = spk.partner_cabang " \
            "left join ( " \
            "select dealer_sale_order_line_discount_line_id, sum(ps_finco) as ps_finco, sum(ps_ahm) as ps_ahm, sum(ps_md) as ps_md, sum(ps_dealer) as ps_dealer, sum(ps_others) as ps_others, " \
            "sum(discount) as discount, sum(discount_pelanggan) as discount_pelanggan " \
            "from dealer_sale_order_line_discount_line " \
            "group by dealer_sale_order_line_discount_line_id " \
            ") dsol_disc ON dsol_disc.dealer_sale_order_line_discount_line_id = dsol.id " \
            "left join dealer_register_spk_line drsl ON drsl.id = dso.register_spk_id " \
            "WHERE " + where_section_id + " AND " + where_user_id + " AND " + where_product_ids + " AND " + where_start_date + " AND " + where_partner_komisi_id + " AND " + where_hutang_komisi_id + " AND " +where_proposal_id + " AND " + where_end_date + " AND " + where_state + " AND " + where_branch_ids + " AND " + where_finco_ids + " " \
            "order by b.code, dso.date_order"

        move_selection = ""
        report_info = _('')
        move_selection += ""

        reports = [report_penjualantax]
        for report in reports:
            cr.execute(query_penjualantax)
            all_lines = cr.dictfetchall()
            dso_ids = []

            if all_lines:

                def lines_map(x):
                    x.update({'docname': x['branch_code']})

                map(lines_map, all_lines)
                for cnt in range(len(all_lines) - 1):
                    if all_lines[cnt]['id_dso'] != all_lines[cnt +
                                                             1]['id_dso']:
                        all_lines[cnt]['draw_line'] = 1
                    else:
                        all_lines[cnt]['draw_line'] = 0
                all_lines[-1]['draw_line'] = 1

                p_map = map(
                    lambda x: {
                        'no':
                        0,
                        'id_dso':
                        x['id_dso'],
                        'state_ksu':
                        'Undelivered'
                        if x['state_picking'] == None else 'Delivered',
                        'state_picking':
                        x['state_picking'],
                        'oos_number':
                        x['oos_number'],
                        'no_registrasi':
                        str(x['no_registrasi'].encode('ascii', 'ignore').
                            decode('ascii'))
                        if x['no_registrasi'] != None else '',
                        'branch_status':
                        str(x['branch_status'].encode('ascii', 'ignore').
                            decode('ascii'))
                        if x['branch_status'] != None else '',
                        'branch_code':
                        str(x['branch_code'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['branch_code'] != None else '',
                        'branch_name':
                        str(x['branch_name'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['branch_name'] != None else '',
                        'md_code':
                        str(x['md_code'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['md_code'] != None else '',
                        'spk_name':
                        str(x['spk_name'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['spk_name'] != None else '',
                        'name':
                        str(x['name'].encode('ascii', 'ignore').decode('ascii')
                            ) if x['name'] != None else '',
                        'state':
                        str(x['state'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['state'] != None else '',
                        'date_order':
                        str(x['date_order'])
                        if x['date_order'] != None else False,
                        'finco_code':
                        str(x['finco_code'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['finco_code'] != None else '',
                        'is_cod':
                        str(x['is_cod'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['is_cod'] != None else '',
                        'sales_koor_name':
                        str(x['sales_koor_name'].encode('ascii', 'ignore').
                            decode('ascii'))
                        if x['sales_koor_name'] != None else '',
                        'sales_name':
                        str(x['sales_name'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['sales_name'] != None else '',
                        'job_name':
                        str(x['job_name'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['job_name'] != None else '',
                        'cust_code':
                        str(x['cust_code'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['cust_code'] != None else '',
                        'cust_name':
                        str(x['cust_name'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['cust_name'] != None else '',
                        'pkp':
                        str(x['pkp'].encode('ascii', 'ignore').decode('ascii'))
                        if x['pkp'] != None else 'Non PKP',
                        'product_name':
                        str(x['product_name'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['product_name'] != None else '',
                        'pav_code':
                        str(x['pav_code'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['pav_code'] != None else '',
                        'product_qty':
                        x['product_qty'],
                        'lot_name':
                        str(x['lot_name'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['lot_name'] != None else '',
                        'lot_chassis':
                        str(x['lot_chassis'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['lot_chassis'] != None else '',
                        'price_unit':
                        x['price_unit'],
                        'discount_po':
                        x['discount_po'],
                        'ps_dealer':
                        x['ps_dealer'],
                        'ps_ahm':
                        x['ps_ahm'],
                        'ps_md':
                        x['ps_md'],
                        'cabang_partner':
                        x['cabang_partner'],
                        'ps_finco':
                        x['ps_finco'],
                        'ps_total':
                        x['ps_total'],
                        'sales':
                        x['sales'] if x['sales'] != 0 else 0,
                        'disc_reg':
                        x['disc_reg'] if x['disc_reg'] != 0 else 0,
                        'disc_quo':
                        x['disc_quo'] if x['disc_quo'] != 0 else 0,
                        'disc_quo_incl_tax':
                        x['disc_quo_incl_tax']
                        if x['disc_quo_incl_tax'] != 0 else 0,
                        'disc_total':
                        x['disc_total'] if x['disc_total'] != 0 else 0,
                        'price_subtotal':
                        x['price_subtotal'],
                        'piutang_dp':
                        x['piutang_dp'],
                        'piutang':
                        x['piutang'],
                        'piutang_total':
                        x['piutang_total'],
                        'PPN':
                        x['ppn'],
                        'total':
                        x['total'],
                        'force_cogs':
                        x['force_cogs'],
                        'gp_dpp_minus_hpp':
                        x['gp_dpp_minus_hpp'],
                        'gp_unit':
                        x['gp_unit'],
                        'amount_hutang_komisi':
                        x['amount_hutang_komisi'],
                        'dpp_insentif_finco':
                        x['dpp_insentif_finco']
                        if x['dpp_insentif_finco'] != 0 else 0,
                        'price_bbn':
                        x['price_bbn'],
                        'price_bbn_beli':
                        x['price_bbn_beli'],
                        'pph_komisi':
                        x['amount_hutang_komisi'] * 3 / 100,
                        'gp_bbn':
                        x['gp_bbn'],
                        'gp_total':
                        x['gp_total'],
                        'beban_cabang':
                        x['beban_cabang'],
                        'categ_name':
                        str(x['categ_name'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['categ_name'] != None else '',
                        'categ2_name':
                        str(x['categ2_name'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['categ2_name'] != None else '',
                        'prod_series':
                        str(x['prod_series'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['prod_series'] != None else '',
                        'faktur_pajak':
                        str(x['faktur_pajak'].encode('ascii', 'ignore').decode(
                            'ascii')) if x['faktur_pajak'] != None else '',
                        'partner_komisi_id':
                        x['partner_komisi_id'],
                        'hutang_komisi_id':
                        x['hutang_komisi_id'],
                        'proposal_id':
                        x['proposal_id']
                    }, all_lines)
                for p in p_map:
                    if p['id_dso'] not in map(lambda x: x.get('id_dso', None),
                                              dso_ids):
                        records = filter(lambda x: x['id_dso'] == p['id_dso'],
                                         all_lines)
                        dso = self.pool.get('dealer.sale.order').browse(
                            cr, uid, records[0]['id_dso'])
                        analytic_1_id, analytic_2_id, analytic_3_id, analytic_4_id = self.pool.get(
                            'account.analytic.account').get_analytical(
                                cr, uid, dso.branch_id, 'Unit', False, 4,
                                'Sales')
                        analytic_1_name = self.pool.get(
                            'account.analytic.account').browse(
                                cr, uid, analytic_1_id).name or ''
                        analytic_1 = self.pool.get(
                            'account.analytic.account').browse(
                                cr, uid, analytic_1_id).code or ''

                        analytic_2_name = self.pool.get(
                            'account.analytic.account').browse(
                                cr, uid, analytic_2_id).name or ''
                        analytic_2 = self.pool.get(
                            'account.analytic.account').browse(
                                cr, uid, analytic_2_id).code or ''

                        analytic_3_name = self.pool.get(
                            'account.analytic.account').browse(
                                cr, uid, analytic_3_id).name or ''
                        analytic_3 = self.pool.get(
                            'account.analytic.account').browse(
                                cr, uid, analytic_3_id).code or ''
                        branch = dso.branch_id
                        branch_name = dso.branch_id.name
                        branch_status_1 = dso.branch_id.branch_status
                        branch_id = dso.branch_id.id

                        analytic_4_name = self.pool.get(
                            'account.analytic.account').browse(
                                cr, uid, analytic_4_id).name or ''
                        analytic_4 = self.pool.get(
                            'account.analytic.account').browse(
                                cr, uid, analytic_4_id).code or ''

                        if (branch and branch_ids
                                and branch.id not in branch_ids) or (
                                    branch and branch_status
                                    and branch_status != branch.branch_status):
                            continue
                        analytic_2_branch = analytic_2
                        if analytic_2 in ['210', '220', '230']:
                            if branch_status_1 == 'H123':
                                analytic_2_branch = analytic_2[:2] + '1'
                            elif branch_status_1 == 'H23':
                                analytic_2_branch = analytic_2[:2] + '2'
                            else:
                                analytic_2_branch = analytic_2
                        analytic_combination = analytic_1 + '/' + analytic_2_branch + '/' + analytic_3 + '/' + analytic_4
                        p.update({'lines': records})
                        p.update({'analytic_1': analytic_1_name})
                        p.update({'analytic_2': analytic_2_name})
                        p.update({'analytic_3': analytic_3_name})
                        p.update({'analytic_4': analytic_4_name})
                        p.update({'branch_id': branch_id})
                        p.update({'branch_status': branch_status_1})
                        p.update({'branch_name': branch_name})
                        p.update(
                            {'analytic_combination': analytic_combination})
                        p.update({'or_name': ''})
                        #p.update({'or_amount': '0'})
                        p.update({'invoice_number': ''})
                        p.update({'invoice_date': ''})
                        p.update({'invoice_status': ''})
                        p.update({'ar_days': '0'})
                        p.update({'tgl_lunas': ''})
                        voucher_ids = self.pool.get('account.voucher').search(
                            cr, uid, [
                                '|', ('name', 'ilike', dso.name),
                                ('reference', 'ilike', dso.name)
                            ])
                        if voucher_ids:
                            vouchers = self.pool.get('account.voucher').browse(
                                cr, uid, voucher_ids)
                            p.update({
                                'or_name':
                                ', '.join(vouchers.mapped('number'))
                            })
                            #p.update({'or_amount': ', '.join([str(i) for i in vouchers.mapped('amount')])})
                        invoice_ids = self.pool.get('account.invoice').search(
                            cr,
                            uid, [('origin', 'ilike', dso.name),
                                  ('type', '=', 'out_invoice')],
                            limit=1)
                        if invoice_ids:
                            invoices = self.pool.get('account.invoice').browse(
                                cr, uid, invoice_ids)
                            p.update({
                                'invoice_number':
                                ', '.join(invoices.mapped('internal_number'))
                            })
                            p.update({
                                'invoice_date':
                                invoices.date_invoice
                                and ', '.join(invoices.mapped('date_invoice'))
                                or None
                            })
                            p.update({'invoice_status': invoices.state})
                            if invoices.state == 'cancel':
                                p.update({
                                    'product_qty': 0,
                                    'price_unit': 0,
                                    'discount_po': 0,
                                    'ps_dealer': 0,
                                    'ps_ahm': 0,
                                    'ps_md': 0,
                                    'ps_finco': 0,
                                    'ps_total': 0,
                                    'sales': 0,
                                    'disc_reg': 0,
                                    'disc_quo': 0,
                                    'disc_quo_incl_tax': 0,
                                    'disc_total': 0,
                                    'piutang_dp': 0,
                                    'piutang': 0,
                                    'piutang_total': 0,
                                    'total': 0,
                                    'price_subtotal': 0,
                                    'PPN': 0,
                                    'force_cogs': 0,
                                    'gp_dpp_minus_hpp': 0,
                                    'gp_unit': 0,
                                    'amount_hutang_komisi': 0,
                                    'dpp_insentif_finco': 0,
                                    'price_bbn': 0,
                                    'price_bbn_beli': 0,
                                    'gp_bbn': 0,
                                    'gp_total': 0,
                                    'beban_cabang': 0,
                                    'pph_komisi': 0,
                                })
                            inv_cust = invoices
                            if len(invoices) > 1:
                                inv_cust = invoices.filtered(
                                    lambda r: r.tipe == 'customer')
                            if inv_cust and inv_cust[
                                    0].state == 'paid' and inv_cust[
                                        0].payment_ids:
                                paid_date = inv_cust[0].payment_ids.sorted(
                                    key=lambda r: r.date)[
                                        len(inv_cust[0].payment_ids) - 1].date
                                paid_date = datetime.strptime(
                                    paid_date, '%Y-%m-%d')
                                paid_date = datetime.date(paid_date)
                                str_paid_date = paid_date.strftime('%Y-%m-%d')
                            else:
                                paid_date = date.today()
                                str_paid_date = ''
                            inv_date = inv_cust[0].date_invoice
                            if inv_date:
                                inv_date = datetime.strptime(
                                    inv_date, '%Y-%m-%d')
                                inv_date = datetime.date(inv_date)
                                date_diff = int((paid_date - inv_date).days)
                                p.update({'ar_days': str(date_diff)})
                                p.update({'tgl_lunas': str_paid_date})
                            else:
                                p.update({'ar_days': None})
                                p.update({'tgl_lunas': None})
                        dso_ids.append(p)
                dso_ids = sorted(dso_ids, key=lambda k: k['invoice_number'])
                report.update({'dso_ids': dso_ids})

        reports = filter(lambda x: x.get('dso_ids'), reports)

        #print reports[0]['dso_ids'][0]

        tampung = []
        for x in reports:
            for y in x['dso_ids']:
                if len(y['lines']) == 1:
                    tampung.append(y)
                else:
                    temp_str = ''
                    temp = y
                    for z in y['lines']:
                        if z['lot_name'] != temp_str:
                            temp_str = z['lot_name']
                            test_tampung = {
                                'discount_po':
                                z['discount_po'],
                                'no_registrasi':
                                temp['no_registrasi'],
                                'pkp':
                                temp['pkp'],
                                'price_unit':
                                z['price_unit'],
                                'ps_md':
                                temp['ps_md'],
                                'id_dso':
                                temp['id_dso'],
                                'cabang_partner':
                                z['cabang_partner'],
                                'product_qty':
                                temp['product_qty'],
                                'piutang_total':
                                z['piutang_total'],
                                'state_picking':
                                temp['state_picking'],
                                'oos_number':
                                temp['oos_number'],
                                'total':
                                z['total'],
                                'tgl_lunas':
                                temp['tgl_lunas'],
                                'branch_id':
                                temp['branch_id'],
                                'analytic_4':
                                temp['analytic_4'],
                                'analytic_2':
                                temp['analytic_2'],
                                'analytic_3':
                                temp['analytic_3'],
                                'analytic_1':
                                temp['analytic_1'],
                                'piutang_dp':
                                temp['piutang_dp'],
                                'job_name':
                                temp['job_name'],
                                'disc_quo':
                                z['disc_quo'],
                                'cust_code':
                                temp['cust_code'],
                                'hutang_komisi_id':
                                temp['hutang_komisi_id'],
                                'ps_total':
                                z['ps_total'],
                                'sales':
                                z['sales'],
                                'branch_name':
                                temp['branch_name'],
                                'gp_bbn':
                                z['gp_bbn'],
                                'invoice_number':
                                temp['invoice_number'],
                                'name':
                                temp['name'],
                                'pph_komisi':
                                z['pph_komisi'],
                                'disc_total':
                                z['disc_total'],
                                'faktur_pajak':
                                temp['faktur_pajak'],
                                'cust_name':
                                temp['cust_name'],
                                'piutang':
                                z['piutang'],
                                'prod_series':
                                temp['prod_series'],
                                'disc_quo_incl_tax':
                                temp['disc_quo_incl_tax'],
                                'sales_name':
                                temp['sales_name'],
                                'pav_code':
                                z['pav_code'],
                                'sales_koor_name':
                                temp['sales_koor_name'],
                                'branch_status':
                                temp['branch_status'],
                                'md_code':
                                temp['md_code'],
                                'is_cod':
                                temp['is_cod'],
                                'ps_finco':
                                z['ps_finco'],
                                'state_ksu':
                                temp['state_ksu'],
                                'lines':
                                temp['lines'],
                                'price_subtotal':
                                z['price_subtotal'],
                                'finco_code':
                                temp['finco_code'],
                                'beban_cabang':
                                z['beban_cabang'],
                                'ar_days':
                                temp['ar_days'],
                                'date_order':
                                temp['date_order'],
                                'disc_reg':
                                z['disc_reg'],
                                'price_bbn':
                                z['price_bbn'],
                                'no':
                                temp['no'],
                                'PPN':
                                temp['PPN'],
                                'branch_code':
                                temp['branch_code'],
                                'state':
                                temp['state'],
                                'ps_dealer':
                                temp['ps_dealer'],
                                'amount_hutang_komisi':
                                temp['amount_hutang_komisi'],
                                'proposal_id':
                                temp['proposal_id'],
                                'product_name':
                                z['product_name'],
                                'analytic_combination':
                                temp['analytic_combination'],
                                'gp_total':
                                z['gp_total'],
                                'price_bbn_beli':
                                z['price_bbn_beli'],
                                'ps_ahm':
                                z['ps_ahm'],
                                'invoice_date':
                                temp['invoice_date'],
                                'force_cogs':
                                z['force_cogs'],
                                'categ2_name':
                                z['categ2_name'],
                                'lot_chassis':
                                z['lot_chassis'],
                                'invoice_status':
                                temp['invoice_status'],
                                'partner_komisi_id':
                                temp['partner_komisi_id'],
                                'lot_name':
                                z['lot_name'],
                                'dpp_insentif_finco':
                                z['dpp_insentif_finco'],
                                'categ_name':
                                z['categ_name'],
                                'gp_unit':
                                z['gp_unit'],
                                'or_name':
                                temp['or_name'],
                                # 'or_amount' : str(temp['or_amount']),
                                'spk_name':
                                z['spk_name'],
                                'gp_dpp_minus_hpp':
                                z['gp_dpp_minus_hpp'],
                            }
                            tampung.append(test_tampung)
        del reports[0]['dso_ids']
        report.update({'dso_ids': tampung})

        if not reports:
            reports = [{
                'dso_ids': [{
                    'no': 'NO DATA FOUND',
                    'state_ksu': 'NO DATA FOUND',
                    'invoice_number': 'NO DATA FOUND',
                    'invoice_status': 'NO DATA FOUND',
                    'invoice_date': 'NO DATA FOUND',
                    'state_ksu': 'NO DATA FOUND',
                    'state_picking': 'NO DATA FOUND',
                    'oos_number': 'NO DATA FOUND',
                    'branch_status': 'NO DATA FOUND',
                    'no_registrasi': 'NO DATA FOUND',
                    'branch_code': 'NO DATA FOUND',
                    'branch_name': 'NO DATA FOUND',
                    'analytic_1': 'NO DATA FOUND',
                    'analytic_2': 'NO DATA FOUND',
                    'analytic_3': 'NO DATA FOUND',
                    'analytic_4': 'NO DATA FOUND',
                    'analytic_combination': 'NO DATA FOUND',
                    'md_code': 'NO DATA FOUND',
                    'spk_name': 'NO DATA FOUND',
                    'name': 'NO DATA FOUND',
                    'state': 'NO DATA FOUND',
                    'date_order': 'NO DATA FOUND',
                    'finco_code': 'NO DATA FOUND',
                    'is_cod': 'NO DATA FOUND',
                    'sales_koor_name': 'NO DATA FOUND',
                    'sales_name': 'NO DATA FOUND',
                    'job_name': 'NO DATA FOUND',
                    'cust_code': 'NO DATA FOUND',
                    'cust_name': 'NO DATA FOUND',
                    'proposal_id': 'NO DATA FOUND',
                    'hutang_komisi_id': 'NO DATA FOUND',
                    'partner_komisi_id': 'NO DATA FOUND',
                    'or_name': 'NO DATA FOUND',
                    # 'or_amount': 0,
                    'product_name': 'NO DATA FOUND',
                    'pav_code': 'NO DATA FOUND',
                    'product_qty': 0,
                    'cabang_partner': 'NO DATA FOUND',
                    'lot_name': 'NO DATA FOUND',
                    'lot_chassis': 'NO DATA FOUND',
                    'tgl_lunas': 'NO DATA FOUND',
                    'ar_days': '0',
                    'price_unit': 0,
                    'discount_po': 0,
                    'ps_dealer': 0,
                    'ps_ahm': 0,
                    'ps_md': 0,
                    'ps_finco': 0,
                    'ps_total': 0,
                    'sales': 0,
                    'piutang_dp': 0,
                    'piutang': 0,
                    'piutang_total': 0,
                    'total': 0,
                    'disc_reg': 0,
                    'disc_quo': 0,
                    'disc_quo_incl_tax': 0,
                    'disc_total': 0,
                    'price_subtotal': 0,
                    'PPN': 0,
                    'force_cogs': 0,
                    'gp_dpp_minus_hpp': 0,
                    'gp_unit': 0,
                    'amount_hutang_komisi': 0,
                    'dpp_insentif_finco': 0,
                    'price_bbn': 0,
                    'price_bbn_beli': 0,
                    'gp_bbn': 0,
                    'gp_total': 0,
                    'beban_cabang': 0,
                    'pph_komisi': 0,
                    'categ_name': 'NO DATA FOUND',
                    'pkp': 'NO DATA FOUND',
                    'categ2_name': 'NO DATA FOUND',
                    'prod_series': 'NO DATA FOUND',
                    'faktur_pajak': 'NO DATA FOUND'
                }],
                'title_short':
                'Laporan Penjualan Tax',
                'type':
                '',
                'title':
                ''
            }]

        report_date = datetime_field.context_timestamp(
            cr, uid, datetime.now(),
            context).strftime(DEFAULT_SERVER_DATETIME_FORMAT)

        self.localcontext.update({
            'report_info': report_info,
            'report_date': report_date,
            'reports': reports,
        })
        super(dym_report_penjualantax_print,
              self).set_context(objects, data, ids, report_type)
Example #37
0
    def set_context(self, objects, data, ids, report_type=None):
        cr = self.cr
        uid = self.uid
        context = self.context
        journal_id = data['journal_id']
        start_date = data['start_date']
        end_date = data['end_date']
        start_value_date = data['start_value_date']
        end_value_date = data['end_value_date']
        title_prefix = ''
        title_short_prefix = ''

        bank_balance = 0
        where_account = " 1=1 "
        if journal_id:
            journals = self.pool.get('account.journal').browse(
                cr, uid, journal_id[0])
            account_ids = []
            for journal in journals:
                if journal.default_debit_account_id and journal.default_debit_account_id.id not in account_ids:
                    account_ids.append(journal.default_debit_account_id.id)
                if journal.default_credit_account_id and journal.default_credit_account_id.id not in account_ids:
                    account_ids.append(journal.default_credit_account_id.id)
                bank_balance = journal.default_credit_account_id.with_context(
                    date_from=start_date, date_to=start_date, initial_bal=True
                ).balance or journal.default_debit_account_id.with_context(
                    date_from=start_date, date_to=start_date,
                    initial_bal=True).balance
            where_account = " a.id  in %s " % str(tuple(account_ids)).replace(
                ',)', ')')

        saldo_awal = bank_balance

        report_bank_book = {
            'type': 'BankBook',
            'title': '',
            'title_short': title_short_prefix + ', ' + _('LAPORAN BANK BOOK'),
            'saldo_awal': saldo_awal,
            'start_date': start_date,
            'end_date': end_date
        }

        area_user = self.pool.get('res.users').browse(cr, uid, uid).branch_ids
        branch_ids_user = [b.id for b in area_user]
        branch_ids = branch_ids_user
        where_start_date = " l.date >= '%s' " % start_date
        where_end_date = " l.date <= '%s' " % end_date
        where_value_start = " 1=1 "
        if start_value_date:
            where_value_start = " (at.value_date >= '%s' OR " % start_value_date
            where_value_start += " cg.value_date >= '%s' OR " % start_value_date
            where_value_start += " av.value_date >= '%s' OR " % start_value_date
            where_value_start += " bt.value_date >= '%s') " % start_value_date
        where_value_end = " 1=1 "
        if end_value_date:
            where_value_end = " (at.value_date <= '%s' OR " % end_value_date
            where_value_end += " cg.value_date <= '%s' OR " % end_value_date
            where_value_end += " av.value_date <= '%s' OR " % end_value_date
            where_value_end += " bt.value_date <= '%s') " % end_value_date
        query_bank_book = "SELECT l.id, l.analytic_account_id, l.name, l.ref, p.name as partner_name, a.name as account_name, a.code as account_code, l.account_id, l.date, l.debit, l.credit, fc.name as finance_company, at.value_date as at_value_date, cg.value_date as cg_value_date, av.value_date as av_value_date, bt.value_date as bt_value_date, avp.date as avp_value_date FROM account_move_line l LEFT JOIN account_move m on m.id = l.move_id LEFT JOIN account_account a on a.id = l.account_id LEFT JOIN res_partner p on p.id = l.partner_id LEFT JOIN res_partner fc on fc.id = l.finco_id LEFT JOIN dym_alokasi_titipan at on at.name = m.name LEFT JOIN dym_clearing_giro cg on cg.name = m.name LEFT JOIN account_voucher av on av.number = m.name LEFT JOIN dym_bank_transfer bt on bt.name = m.name LEFT JOIN dym_advance_payment avp on avp.name = m.name WHERE %s and %s and %s and %s and %s ORDER BY l.id asc" % (
            where_account, where_start_date, where_end_date, where_value_start,
            where_value_end)

        move_selection = ""
        report_info = _('')
        move_selection += ""

        reports = [report_bank_book]

        for report in reports:
            a = cr.execute(query_bank_book)
            all_lines = cr.dictfetchall()

            move_lines = []
            if all_lines:
                p_map = map(
                    lambda x: {
                        'no':
                        0,
                        'id':
                        x['id'],
                        'date':
                        x['date'] if x['date'] != None else '',
                        'value_date':
                        x['at_value_date'] if x['at_value_date'] and x[
                            'at_value_date'] != None else x['cg_value_date']
                        if x['cg_value_date'] and x['cg_value_date'] != None
                        else x['av_value_date'] if x['av_value_date'] and x[
                            'av_value_date'] != None else x['bt_value_date']
                        if x['bt_value_date'] and x[
                            'bt_value_date'] != None else x['avp_value_date']
                        if x['avp_value_date'] and x['avp_value_date'
                                                     ] != None else '',
                        'debit':
                        x['debit'] if x['debit'] > 0 else 0.0,
                        'credit':
                        x['credit'] if x['credit'] > 0 else 0.0,
                        'account_code':
                        x['account_code'].encode('ascii', 'ignore').decode(
                            'ascii') if x['account_code'] != None else '',
                        'account_name':
                        x['account_name'].encode('ascii', 'ignore').decode(
                            'ascii') if x['account_name'] != None else '',
                        'partner_name':
                        x['partner_name'].encode('ascii', 'ignore').decode(
                            'ascii') if x['partner_name'] != None else '',
                        'finance_company':
                        x['finance_company'].encode('ascii', 'ignore').decode(
                            'ascii') if x['finance_company'] != None else '',
                        'name':
                        x['name'].encode('ascii', 'ignore').decode('ascii')
                        if x['name'] != None else '',
                        'ref':
                        x['ref'].encode('ascii', 'ignore').decode('ascii')
                        if x['ref'] != None else '',
                        'analytic_4':
                        x['analytic_account_id']
                        if x['analytic_account_id'] != None else '',
                    }, all_lines)

                for p in p_map:
                    if p['id'] not in map(lambda x: x.get('id', None),
                                          move_lines):
                        account_move_lines = filter(
                            lambda x: x['id'] == p['id'], all_lines)
                        analytic_1 = ''
                        analytic_2 = ''
                        analytic_3 = ''
                        analytic_4 = ''
                        analytic_1_name = ''
                        analytic_2_name = ''
                        analytic_3_name = ''
                        analytic_4_name = ''
                        analytic = self.pool.get(
                            'account.analytic.account').browse(
                                cr, uid, account_move_lines[0]
                                ['analytic_account_id']) or ''
                        branch_name = ''
                        branch = False
                        branch_status_1 = ''
                        branch_name = ''
                        branch_id = ''
                        if analytic:
                            if analytic.type == 'normal':
                                if analytic.segmen == 1 and analytic_1 == '':
                                    analytic_1_name = analytic.name
                                    analytic_1 = analytic.code
                                if analytic.segmen == 2 and analytic_2 == '':
                                    analytic_2_name = analytic.name
                                    analytic_2 = analytic.code
                                if analytic.segmen == 3 and analytic_3 == '':
                                    analytic_3_name = analytic.name
                                    analytic_3 = analytic.code
                                    branch = analytic.sudo().branch_id
                                    branch_name = branch.name
                                    branch_status_1 = branch.branch_status
                                    branch_id = branch.id
                                if analytic.segmen == 4 and analytic_4 == '':
                                    analytic_4_name = analytic.name
                                    analytic_4 = analytic.code
                                    analytic_id = analytic
                            while (analytic.parent_id):
                                analytic = analytic.parent_id
                                if analytic.type == 'normal':
                                    if analytic.segmen == 1 and analytic_1 == '':
                                        analytic_1_name = analytic.name
                                        analytic_1 = analytic.code
                                    if analytic.segmen == 2 and analytic_2 == '':
                                        analytic_2_name = analytic.name
                                        analytic_2 = analytic.code
                                    if analytic.segmen == 3 and analytic_3 == '':
                                        analytic_3_name = analytic.name
                                        analytic_3 = analytic.code
                                        branch = analytic.sudo().branch_id
                                        branch_name = branch.name
                                        branch_status_1 = branch.branch_status
                                        branch_id = branch.id
                                    if analytic.segmen == 4 and analytic_4 == '':
                                        analytic_4_name = analytic.name
                                        analytic_4 = analytic.code
                                        analytic_id == analytic
                            if (branch and branch_ids
                                    and branch.id not in branch_ids):
                                continue

                            analytic_2_branch = analytic_2
                            if analytic_2 in ['210', '220', '230']:
                                if branch_status_1 == 'H123':
                                    analytic_2_branch = analytic_2[:2] + '1'
                                elif branch_status_1 == 'H23':
                                    analytic_2_branch = analytic_2[:2] + '2'
                                else:
                                    analytic_2_branch = analytic_2

                            analytic_1_code = analytic_1
                            analytic_2_code = analytic_2_branch
                            analytic_3_code = analytic_3
                            analytic_4_code = analytic_4

                            analytic_combination = analytic_1 + '/' + analytic_2_branch + '/' + analytic_3 + '/' + analytic_4

                            p.update({'lines': account_move_lines})
                            p.update({'analytic_1': analytic_1_code})
                            p.update({'analytic_2': analytic_2_code})
                            p.update({'analytic_3': analytic_3_code})
                            p.update({'analytic_4': analytic_4_code})
                            p.update({'branch_id': branch_id})
                            p.update({'branch_status': branch_status_1})
                            p.update({'branch_name': branch_name})
                            p.update(
                                {'analytic_combination': analytic_combination})
                            move_lines.append(p)
                report.update({'move_lines': move_lines})

        reports = filter(lambda x: x.get('move_lines'), reports)

        if not reports:
            reports = [{
                'type':
                'BankBook',
                'title':
                '',
                'title_short':
                title_short_prefix + ', ' + _('LAPORAN BANK BOOK'),
                'saldo_awal':
                saldo_awal,
                'start_date':
                start_date,
                'end_date':
                end_date,
                'move_lines': [{
                    'no': 0,
                    'branch_status': 'NO DATA FOUND',
                    'branch_name': 'NO DATA FOUND',
                    'account_code': 'NO DATA FOUND',
                    'account_name': 'NO DATA FOUND',
                    'partner_name': 'NO DATA FOUND',
                    'finance_company': 'NO DATA FOUND',
                    'name': 0,
                    'ref': 0,
                    'date': 'NO DATA FOUND',
                    'value_date': 'NO DATA FOUND',
                    'debit': 0.0,
                    'credit': 0.0,
                    'analytic_1': 'NO DATA FOUND',
                    'analytic_2': 'NO DATA FOUND',
                    'analytic_3': 'NO DATA FOUND',
                    'analytic_4': 'NO DATA FOUND',
                    'analytic_combination': 'NO DATA FOUND',
                }],
            }]
        report_date = datetime_field.context_timestamp(
            cr, uid, datetime.now(),
            context).strftime(DEFAULT_SERVER_DATETIME_FORMAT)

        self.localcontext.update({
            'report_info': report_info,
            'report_date': report_date,
            'reports': reports,
        })
        objects = False
        super(dym_bank_book_report_print,
              self).set_context(objects, data, ids, report_type)
Example #38
0
    def set_context(self, objects, data, ids, report_type=None):
        cr = self.cr
        uid = self.uid
        context = self.context
        branch_ids = data['branch_ids']
        product_ids = data['product_ids']
        trx_start_date = data['trx_start_date']
        trx_end_date = data['trx_end_date']
        division = data['division']

        title_prefix = ''
        title_short_prefix = ''
        
        report_program_subsidi = {
            'type': 'payable',
            'title': '',
            'title_short': title_short_prefix + ', ' + _('Laporan Program Subsidi')}

        query_start = """
                SELECT d.id as id_ai, 
            COALESCE(b.name,'') as branch_id, 
            dso.division as division, 
            dso.name as number, 
            dso.date_order as date, 
            COALESCE(inv.number,'') as invoice_number, 
            inv.date_invoice as invoice_date, 
            COALESCE(t.name,'') as type, 
            COALESCE(pav.code,'') as warna, 
            COALESCE(lot.name,'') as engine_number, 
            COALESCE(p.name,'') as program, 
            COALESCE(d.ps_md,0) as ps_md, 
            COALESCE(d.ps_ahm,0) as ps_ahm, 
            case when COALESCE(d.ps_finco,0) = COALESCE(d.discount_pelanggan,0) then COALESCE(d.ps_finco,0) else 
            case when COALESCE(d.ps_finco,0) = 0 then COALESCE(d.ps_finco,0) else COALESCE(d.discount_pelanggan,0) end end as ps_finco, 
            COALESCE(d.ps_dealer,0) as ps_dealer, 
            COALESCE(d.ps_others,0) as ps_others,
            case when COALESCE(d.ps_finco,0) = COALESCE(d.discount_pelanggan,0) then COALESCE(d.ps_finco,0) else COALESCE(d.discount_pelanggan,0) end as discount, 
            --COALESCE(d.discount_pelanggan,0) as disc_pelanggan 
            COALESCE(l.discount_po,0) as disc_pelanggan, 
            round(COALESCE(d.ps_md,0)/1.1) as ps_md_real, 
            round(COALESCE(d.ps_ahm,0)/1.1) as ps_ahm_real, 
            case when COALESCE(d.ps_finco,0) = COALESCE(d.discount_pelanggan,0) then round(COALESCE(d.ps_finco,0)/1.1) else 
            case when COALESCE(d.ps_finco,0) = 0 then round(COALESCE(d.ps_finco,0)/1.1) else round(COALESCE(d.discount_pelanggan,0)/1.1) end end as ps_finco_real, 
            round(COALESCE(d.ps_dealer,0)/1.1) as ps_dealer_real, 
            round(COALESCE(d.ps_others,0)/1.1) as ps_others_real,
            case when COALESCE(d.ps_finco,0) = COALESCE(d.discount_pelanggan,0) then round(COALESCE(d.ps_finco,0)/1.1) else round(COALESCE(d.discount_pelanggan,0)/1.1) end as discount_real,
            --COALESCE(d.discount_pelanggan,0) as disc_pelanggan 
            round(COALESCE(l.discount_po,0)/1.1) as disc_pelanggan_real 
            FROM 
            dealer_sale_order dso
            LEFT JOIN dealer_sale_order_line l ON l.dealer_sale_order_line_id = dso.id
            left join dealer_sale_order_line_discount_line d on l.id = d.dealer_sale_order_line_discount_line_id
            LEFT JOIN dym_program_subsidi p ON p.id = d.program_subsidi
            LEFT JOIN account_invoice inv ON inv.origin = dso.name and inv.type = 'out_invoice'
            /*dealer_sale_order_line_discount_line d 
            LEFT JOIN dym_program_subsidi p ON p.id = d.program_subsidi 
            LEFT JOIN dealer_sale_order_line l ON l.id = d.dealer_sale_order_line_discount_line_id 
            LEFT JOIN dealer_sale_order dso ON l.dealer_sale_order_line_id = dso.id 
            LEFT JOIN account_invoice inv ON inv.origin = dso.name and inv.type = 'out_invoice' */
            LEFT JOIN dym_branch b ON b.id = dso.branch_id 
            LEFT JOIN product_product pr ON l.product_id = pr.id 
            LEFT JOIN product_template t ON t.id = pr.product_tmpl_id 
            LEFT JOIN stock_production_lot lot ON lot.id = l.lot_id 
            LEFT JOIN product_attribute_value_product_product_rel pavpp ON pr.id = pavpp.prod_id 
            LEFT JOIN product_attribute_value pav ON pavpp.att_id = pav.id
            where dso.state in ('progress','done')
        """
            
        move_selection = ""
        report_info = _('')
        move_selection += ""
        
        query_end=""
        if division :
            query_end +=" AND dso.division = '%s'" % str(division)
        if trx_start_date :
            query_end +=" AND dso.date_order >= '%s'" % str(trx_start_date)
        if trx_end_date :
            query_end +=" AND dso.date_order <= '%s'" % str(trx_end_date)
        if product_ids :
            query_end +=" AND l.product_id in %s" % str(
                tuple(product_ids)).replace(',)', ')')
        if branch_ids :
            query_end +=" AND dso.branch_id in %s" % str(
                tuple(branch_ids)).replace(',)', ')')
        reports = [report_program_subsidi]
        
        query_order = "order by 2,4"
        #query_order = ""
        for report in reports:
            cr.execute(query_start + query_end + query_order)
            # --print query_start + query_end + query_order
            all_lines = cr.dictfetchall()
            id_ai = []

            if all_lines:
                # def lines_map(x):
                #         x.update({'docname': x['cabang']})
                # map(lines_map, all_lines)
                # for cnt in range(len(all_lines)-1):
                #     if all_lines[cnt]['id_aml'] != all_lines[cnt+1]['id_aml']:
                #         all_lines[cnt]['draw_line'] = 1
                #     else:
                #         all_lines[cnt]['draw_line'] = 0
                # all_lines[-1]['draw_line'] = 1

                p_map = map(
                    lambda x: {
                        'no': 0,      
                        'id_ai': x['id_ai'] if x['id_ai'] != None else 0,      
                        'branch_id': str(x['branch_id'].encode('ascii','ignore').decode('ascii')) if x['branch_id'] != None else '',
                        'division': str(x['division']) if x['division'] != None else '',
                        'invoice_number': str(x['invoice_number'].encode('ascii','ignore').decode('ascii')) if x['invoice_number'] != None else '',
                        'invoice_date': str(x['invoice_date'].encode('ascii','ignore').decode('ascii')) if x['invoice_date'] != None else '',
                        'number': str(x['number'].encode('ascii','ignore').decode('ascii')) if x['number'] != None else '',
                        'date': str(x['date']) if x['date'] != None else '',
                        'type': str(x['type'].encode('ascii','ignore').decode('ascii')) if x['type'] != None else '',
                        'warna': str(x['warna'].encode('ascii','ignore').decode('ascii')) if x['warna'] != None else '',
                        'engine_number': str(x['engine_number'].encode('ascii','ignore').decode('ascii')) if x['engine_number'] != None else '',
                        'program': str(x['program'].encode('ascii','ignore').decode('ascii')) if x['program'] != None else '',
                        'ps_md': x['ps_md'],
                        'ps_ahm': x['ps_ahm'],
                        'ps_finco': x['ps_finco'],
                        'ps_dealer': x['ps_dealer'],
                        'ps_others': x['ps_others'],
                        'discount': x['discount'],
                        'disc_pelanggan': x['disc_pelanggan'],
                        'ps_md_real': x['ps_md_real'],
                        'ps_ahm_real': x['ps_ahm_real'],
                        'ps_finco_real': x['ps_finco_real'],
                        'ps_dealer_real': x['ps_dealer_real'],
                        'ps_others_real': x['ps_others_real'],
                        'discount_real': x['discount_real'],
                        'disc_pelanggan_real': x['disc_pelanggan_real'],},
                       
                    all_lines)

                ps = []
                for p in p_map:
                    if p['number'] in ps:
                        p['disc_pelanggan'] = 0
                        p['disc_pelanggan_real'] = 0
                    else:
                        ps.append(p['number'])

                report.update({'id_ai': p_map})

        reports = filter(lambda x: x.get('id_ai'), reports)
        
        if not reports :
            reports = [{'title_short': 'Laporan Program Subsidi', 'type': ['out_invoice','in_invoice','in_refund','out_refund'], 'id_ai':
                            [{'no': 0,
                              'branch_id': 'NO DATA FOUND',
                              'division': 'NO DATA FOUND',
                              'number': 'NO DATA FOUND',
                              'date': 'NO DATA FOUND',
                              'invoice_number': 'NO DATA FOUND',
                              'invoice_date': 'NO DATA FOUND',
                              'type': 'NO DATA FOUND',
                              'warna': 'NO DATA FOUND',
                              'engine_number': 'NO DATA FOUND',
                              'program': 'NO DATA FOUND',
                              'ps_md': 0,
                              'ps_ahm': 0,
                              'ps_finco': 0,
                              'ps_dealer': 0,
                              'ps_others': 0,
                              'discount': 0,
                              'disc_pelanggan': 0,
                              'ps_md_real': 0,
                              'ps_ahm_real': 0,
                              'ps_finco_real': 0,
                              'ps_dealer_real': 0,
                              'ps_others_real': 0,
                              'discount_real': 0,
                              'disc_pelanggan_real': 0,
                              }], 'title': ''}]

        report_date = datetime_field.context_timestamp(
            cr, uid, datetime.now(), context
            ).strftime(DEFAULT_SERVER_DATETIME_FORMAT)

        self.localcontext.update({
            'report_info': report_info,
            'report_date': report_date,
            'reports': reports,
            })
        super(dym_report_program_subsidi_print, self).set_context(
            objects, data, ids, report_type)
Example #39
0
    def set_context(self, objects, data, ids, report_type=None):
        locale.setlocale(locale.LC_ALL, 'en_US.UTF-8')
        cr = self.cr
        uid = self.uid
        context = self.context
        branch_ids = data['branch_ids']
        product_ids = data['product_ids']
        location_ids = data['location_ids']
        date = data['date']

        title_prefix = ''
        title_short_prefix = ''

        report_stock_unit = {
            'date':
            date,
            'type':
            'receivable',
            'title':
            '',
            'title_short':
            title_short_prefix + ', ' + _('Report Stock Unit Per Tanggal')
        }

        query_start = "select a.id as p_id, " \
            "c.name as p_name , " \
            "e.name_template as p_kode_product,  " \
            "x.description as p_default_code,  " \
            "g.code as p_warna,  " \
            "d.name as p_mesin,  " \
            "d.chassis_no as p_rangka,  " \
            "a.lot_id as lot_id,  " \
            "'"+str(date)+"' - d.receive_date as p_umur, "\
            "date_part('days', '"+str(date)+" 23:59:59' - MIN(a.in_date) ) as p_umur_quant, "\
            "d.tahun as p_tahun,  " \
            "d.state as p_state,  " \
            "b.name as p_nama_lokasi  " \
            "From  " \
            "stock_quant a " \
            "LEFT JOIN stock_location b ON b.id = a.location_id " \
            "LEFT JOIN dym_branch c ON c.id = b.branch_id " \
            "LEFT JOIN stock_production_lot d ON d.id = a.lot_id " \
            "LEFT JOIN product_product e ON e.id = a.product_id " \
            "LEFT JOIN product_template x ON x.id = e.product_tmpl_id " \
            "LEFT JOIN product_attribute_value_product_product_rel f ON f.prod_id = a.product_id " \
            "LEFT JOIN product_attribute_value g ON g.id = f.att_id " \
            "LEFT JOIN product_category h ON h.id = x.categ_id " \
            "LEFT JOIN stock_warehouse w ON b.warehouse_id = w.id " \
            "LEFT JOIN stock_picking r ON r.id = d.picking_id " \
            "LEFT JOIN dym_stock_packing p ON r.id = p.picking_id and p.branch_sender_id is not null " \
            "LEFT JOIN dym_branch bs ON p.branch_sender_id = bs.id " \
            "where a.lot_id is not null and a.in_date <= '"+str(date)+" 23:59:59' "

        categ_ids = self.pool.get('product.category').get_child_ids(
            cr, uid, ids, 'Unit')
        query_start += "and h.id in %s" % str(tuple(categ_ids)).replace(
            ',)', ')')

        move_selection = ""
        report_info = _('')
        move_selection += ""

        query_end = ""
        if branch_ids:
            query_end += " AND  b.branch_id in %s" % str(
                tuple(branch_ids)).replace(',)', ')')
        if product_ids:
            query_end += "AND  a.product_id  in %s" % str(
                tuple(product_ids)).replace(',)', ')')
        if location_ids:
            query_end += "AND  a.location_id  in %s" % str(
                tuple(location_ids)).replace(',)', ')')
        reports = [report_stock_unit]

        query_group = " group by a.id,p_name,p_kode_product,p_default_code,p_warna,p_mesin,p_rangka,lot_id,p_umur,p_tahun,p_state,p_nama_lokasi "
        query_order = "order by p_kode_product "

        for report in reports:
            cr.execute(query_start + query_end + query_group + query_order)
            all_lines = cr.dictfetchall()
            partners = []
            if all_lines:

                def lines_map(x):
                    x.update({'docname': x['p_name']})

                map(lines_map, all_lines)
                for cnt in range(len(all_lines) - 1):
                    if all_lines[cnt]['p_id'] != all_lines[cnt + 1]['p_id']:
                        all_lines[cnt]['draw_line'] = 1
                    else:
                        all_lines[cnt]['draw_line'] = 0
                all_lines[-1]['draw_line'] = 1
                p_map = map(
                    lambda x: {
                        'p_id':
                        x['p_id'],
                        'lot_id':
                        x['lot_id'],
                        'p_name':
                        x['p_name'],
                        'p_parent_category':
                        'Unit',
                        'p_branch_name':
                        x['p_name'],
                        'p_kode_product':
                        x['p_kode_product'],
                        'p_warna':
                        x['p_warna'],
                        'p_umur':
                        str(x['p_umur']) if x['p_umur'] != None and x['p_umur']
                        >= 0 else str(x['p_umur_quant']).split('.')[0]
                        if x['p_umur_quant'] != None else '',
                        'p_nama_lokasi':
                        x['p_nama_lokasi'],
                        'p_mesin':
                        x['p_mesin'],
                        'p_tahun':
                        x['p_tahun'],
                        'p_rangka':
                        x['p_rangka'],
                        'p_default_code':
                        x['p_default_code'],
                    }, all_lines)

                date_time = str(date) + ' 23:59:59'
                lot_ids = []
                product_code = ''
                total_intransit = 0
                total_reserved = 0
                total_undelivered = 0
                total_rfs = 0
                total_nrfs = 0
                grand_total_intransit = 0
                grand_total_reserved = 0
                grand_total_undelivered = 0
                grand_total_rfs = 0
                grand_total_nrfs = 0
                for p in p_map:
                    if p['p_id'] not in map(lambda x: x.get('p_id', None),
                                            partners):
                        partner_lines = filter(
                            lambda x: x['p_id'] == p['p_id'], all_lines)
                        quant = self.pool.get('stock.quant').browse(
                            cr, SUPERUSER_ID, partner_lines[0]['p_id'])
                        filtered_history_quant_before = quant.history_ids.filtered(
                            lambda r: r.date <= date_time).sorted(
                                key=lambda r: r.date, reverse=True)
                        if filtered_history_quant_before and filtered_history_quant_before[
                                0].location_dest_id.usage not in ('internal',
                                                                  'nrfs'):
                            continue
                        elif partner_lines[0]['lot_id'] in lot_ids:
                            continue
                        if product_code not in (
                                '', partner_lines[0]['p_kode_product']
                        ) and (total_nrfs > 0 or total_rfs > 0
                               or total_intransit > 0 or total_reserved > 0
                               or total_undelivered > 0):
                            partners.append({
                                'p_id': '',
                                'lot_id': '',
                                'p_name': '',
                                'p_parent_category': '',
                                'p_branch_name': '',
                                'p_kode_product': '',
                                'p_warna': '',
                                'p_umur': '',
                                'p_nama_lokasi': '',
                                'p_mesin': '',
                                'p_tahun': 'Sub Total',
                                'p_rangka': '',
                                'p_default_code': '',
                                'lines': partner_lines,
                                'intransit': total_intransit,
                                'rfs': total_rfs,
                                'nrfs': total_nrfs,
                                'reserved': total_reserved,
                                'undelivered': total_undelivered
                            })
                            total_intransit = 0
                            total_reserved = 0
                            total_undelivered = 0
                            total_rfs = 0
                            total_nrfs = 0
                            product_code = partner_lines[0]['p_kode_product']
                        if product_code == '':
                            product_code = partner_lines[0]['p_kode_product']
                        p.update({'lines': partner_lines})
                        intransit = 0
                        rfs = 0
                        nrfs = 0
                        reserved = 0
                        undelivered = 0
                        location = partner_lines[0]['p_nama_lokasi']
                        if quant.in_date <= date_time and (
                            (quant.consolidated_date
                             and quant.consolidated_date > date_time)
                                or not quant.consolidated_date):
                            intransit = 1
                            location = quant.history_ids.filtered(
                                lambda r: r.date <= date_time).sorted(
                                    key=lambda r: r.date
                                )[0].location_dest_id.name
                        elif filtered_history_quant_before and quant.lot_id.dealer_sale_order_id and quant.lot_id.dealer_sale_order_id.confirm_date and quant.lot_id.dealer_sale_order_id.confirm_date <= date_time and filtered_history_quant_before[
                                0].location_dest_id.usage != 'customer':
                            undelivered = 1
                            location = quant.lot_id.dealer_sale_order_id.dealer_sale_order_line.filtered(
                                lambda r: r.lot_id == quant.lot_id
                            ).location_id.name
                        elif quant.lot_id.dealer_sale_order_id and quant.lot_id.dealer_sale_order_id.create_date <= date_time and (
                            (quant.lot_id.dealer_sale_order_id.confirm_date
                             and quant.lot_id.dealer_sale_order_id.confirm_date
                             > date_time) or not quant.lot_id.
                                dealer_sale_order_id.confirm_date):
                            reserved = 1
                            location = quant.lot_id.dealer_sale_order_id.dealer_sale_order_line.filtered(
                                lambda r: r.lot_id == quant.lot_id
                            ).location_id.name
                        elif filtered_history_quant_before and filtered_history_quant_before[
                                0].location_dest_id.usage == 'internal':
                            rfs = 1
                            location = filtered_history_quant_before[
                                0].location_dest_id.name
                        elif filtered_history_quant_before and filtered_history_quant_before[
                                0].location_dest_id.usage == 'nrfs':
                            nrfs = 1
                            location = filtered_history_quant_before[
                                0].location_dest_id.name
                        p.update({
                            'p_nama_lokasi': location,
                            'intransit': intransit,
                            'rfs': rfs,
                            'nrfs': nrfs,
                            'reserved': reserved,
                            'undelivered': undelivered
                        })
                        total_intransit += intransit
                        total_rfs += rfs
                        total_nrfs += nrfs
                        total_reserved += reserved
                        total_undelivered += undelivered
                        if nrfs > 0 or rfs > 0 or intransit > 0 or reserved > 0 or undelivered > 0:
                            lot_ids.append(partner_lines[0]['lot_id'])
                            partners.append(p)
                if partners and (total_nrfs > 0 or total_rfs > 0
                                 or total_intransit > 0 or total_reserved > 0
                                 or total_undelivered > 0):
                    partners.append({
                        'p_id': '',
                        'lot_id': '',
                        'p_name': '',
                        'p_parent_category': '',
                        'p_branch_name': '',
                        'p_kode_product': '',
                        'p_warna': '',
                        'p_umur': '',
                        'p_nama_lokasi': '',
                        'p_mesin': '',
                        'p_tahun': 'Sub Total',
                        'p_rangka': '',
                        'p_default_code': '',
                        'lines': partner_lines,
                        'intransit': total_intransit,
                        'rfs': total_rfs,
                        'nrfs': total_nrfs,
                        'reserved': total_reserved,
                        'undelivered': total_undelivered
                    })
                report.update({'partners': partners})

        reports = filter(lambda x: x.get('partners'), reports)
        if not reports:
            raise orm.except_orm(_('No Data Available'),
                                 _('No records found for your selection!'))

        report_date = datetime_field.context_timestamp(
            cr, uid, datetime.now(),
            context).strftime(DEFAULT_SERVER_DATETIME_FORMAT)

        self.localcontext.update({
            'report_info': report_info,
            'report_date': report_date,
            'reports': reports,
        })
        objects = False
        super(dym_stock_unit_report_pertgl_print,
              self).set_context(objects, data, ids, report_type)