addons/account/report/third_party_ledger.py

   1 # -*- encoding: utf-8 -*-
   2 ##############################################################################
   3 #
   4 #    OpenERP, Open Source Management Solution
   5 #    Copyright (C) 2004-2009 Tiny SPRL (<http://tiny.be>). All Rights Reserved
   6 #    $Id$
   7 #
   8 #    This program is free software: you can redistribute it and/or modify
   9 #    it under the terms of the GNU General Public License as published by
  10 #    the Free Software Foundation, either version 3 of the License, or
  11 #    (at your option) any later version.
  12 #
  13 #    This program is distributed in the hope that it will be useful,
  14 #    but WITHOUT ANY WARRANTY; without even the implied warranty of
  15 #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  16 #    GNU General Public License for more details.
  17 #
  18 #    You should have received a copy of the GNU General Public License
  19 #    along with this program.  If not, see <http://www.gnu.org/licenses/>.
  20 #
  21 ##############################################################################
  22
  23 import pooler
  24 import time
  25 import re
  26 import rml_parse
  27 import datetime
  28 from report import report_sxw
  29
  30 class third_party_ledger(rml_parse.rml_parse):
  31         def __init__(self, cr, uid, name, context):
  32                 self.date_lst = []
  33                 self.date_lst_string = ''
  34                 super(third_party_ledger, self).__init__(cr, uid, name, context)
  35                 self.localcontext.update( {
  36                         'time': time,
  37                         'lines': self.lines,
  38                         'sum_debit_partner': self._sum_debit_partner,
  39                         'sum_credit_partner': self._sum_credit_partner,
  40                         'sum_debit': self._sum_debit,
  41                         'sum_credit': self._sum_credit,
  42                         'get_company': self._get_company,
  43                         'get_currency': self._get_currency,
  44                         'comma_me' : self.comma_me,
  45                 })
  46         def date_range(self,start,end):
  47                 if not start or not end:
  48                         return []
  49                 start = datetime.date.fromtimestamp(time.mktime(time.strptime(start,"%Y-%m-%d")))
  50                 end = datetime.date.fromtimestamp(time.mktime(time.strptime(end,"%Y-%m-%d")))
  51                 full_str_date = []
  52         #
  53                 r = (end+datetime.timedelta(days=1)-start).days
  54         #
  55                 date_array = [start+datetime.timedelta(days=i) for i in range(r)]
  56                 for date in date_array:
  57                         full_str_date.append(str(date))
  58                 return full_str_date
  59
  60         #
  61         def transform_period_into_date_array(self,data):
  62                 ## Get All Period Date
  63                 if not data['form']['periods'][0][2] :
  64                         periods_id =  self.pool.get('account.period').search(self.cr, self.uid, [('fiscalyear_id','=',data['form']['fiscalyear'])])
  65                 else:
  66                         periods_id = data['form']['periods'][0][2]
  67                 date_array = []
  68                 for period_id in periods_id:
  69                         period_obj = self.pool.get('account.period').browse(self.cr, self.uid, period_id)
  70                         date_array = date_array + self.date_range(period_obj.date_start,period_obj.date_stop)
  71                 self.date_lst = date_array
  72                 self.date_lst.sort()
  73
  74         def transform_date_into_date_array(self,data):
  75                 return_array = self.date_range(data['form']['date1'],data['form']['date2'])
  76                 self.date_lst = return_array
  77                 self.date_lst.sort()
  78
  79         def transform_both_into_date_array(self,data):
  80
  81                 if not data['form']['periods'][0][2] :
  82                         periods_id =  self.pool.get('account.period').search(self.cr, self.uid, [('fiscalyear_id','=',data['form']['fiscalyear'])])
  83                 else:
  84                         periods_id = data['form']['periods'][0][2]
  85                 date_array = []
  86                 for period_id in periods_id:
  87                         period_obj = self.pool.get('account.period').browse(self.cr, self.uid, period_id)
  88                         date_array = date_array + self.date_range(period_obj.date_start,period_obj.date_stop)
  89
  90                 period_start_date = date_array[0]
  91                 date_start_date = data['form']['date1']
  92                 period_stop_date = date_array[-1]
  93                 date_stop_date = data['form']['date2']
  94
  95                 if period_start_date<date_start_date:
  96                         start_date = period_start_date
  97                 else :
  98                         start_date = date_start_date
  99
 100                 if date_stop_date<period_stop_date:
 101                         stop_date = period_stop_date
 102                 else :
 103                         stop_date = date_stop_date
 104                 final_date_array = []
 105                 final_date_array = final_date_array + self.date_range(start_date, stop_date)
 106                 self.date_lst = final_date_array
 107                 self.date_lst.sort()
 108
 109         def transform_none_into_date_array(self,data):
 110                 sql = "SELECT min(date) as start_date from account_move_line"
 111                 self.cr.execute(sql)
 112                 start_date = self.cr.fetchone()[0]
 113                 sql = "SELECT max(date) as start_date from account_move_line"
 114                 self.cr.execute(sql)
 115                 stop_date = self.cr.fetchone()[0]
 116                 array= []
 117                 array = array + self.date_range(start_date, stop_date)
 118                 self.date_lst = array
 119                 self.date_lst.sort()
 120
 121
 122         def comma_me(self,amount):
 123                 if  type(amount) is float :
 124                         amount = str('%.2f'%amount)
 125                 else :
 126                         amount = str(amount)
 127                 if (amount == '0'):
 128                      return ' '
 129                 orig = amount
 130                 new = re.sub("^(-?\d+)(\d{3})", "\g<1>'\g<2>", amount)
 131                 if orig == new:
 132                         return new
 133                 else:
 134                         return self.comma_me(new)
 135         def special_map(self):
 136                 string_map = ''
 137                 for date_string in self.date_lst:
 138                         string_map = date_string + ','
 139                 return string_map
 140
 141         def set_context(self, objects, data, ids, report_type = None):
 142                 PARTNER_REQUEST = ''
 143                 if (data['model'] == 'res.partner'):
 144                         ## Si on imprime depuis les partenaires
 145                         if ids:
 146                                 PARTNER_REQUEST =  "AND line.partner_id IN (" + ','.join(map(str, ids)) + ")"
 147                 # Transformation des date
 148                 #
 149                 #
 150 #               if data['form']['fiscalyear']:
 151 #                       self.transform_period_into_date_array(data)
 152 #               else:
 153 #                       self.transform_date_into_date_array(data)
 154                 ##
 155                 if data['form']['state'] == 'none':
 156                         self.transform_none_into_date_array(data)
 157                 elif data['form']['state'] == 'bydate':
 158                         self.transform_date_into_date_array(data)
 159                 elif data['form']['state'] == 'byperiod':
 160                         self.transform_period_into_date_array(data)
 161                 elif data['form']['state'] == 'all':
 162                         self.transform_both_into_date_array(data)
 163
 164                 self.date_lst_string = ''
 165                 if self.date_lst:
 166                         self.date_lst_string = '\'' + '\',\''.join(map(str,self.date_lst)) + '\''
 167                 #
 168                 #new_ids = [id for (id,) in self.cr.fetchall()]
 169                 if data['form']['result_selection'] == 'supplier':
 170                         self.ACCOUNT_TYPE = "('receivable')"
 171                 elif data['form']['result_selection'] == 'customer':
 172                         self.ACCOUNT_TYPE = "('payable')"
 173                 elif data['form']['result_selection'] == 'all':
 174                         self.ACCOUNT_TYPE = "('payable','receivable')"
 175
 176                 self.cr.execute(
 177                         "SELECT a.id " \
 178                         "FROM account_account a " \
 179                         "LEFT JOIN account_account_type t " \
 180                                 "ON (a.type=t.code) " \
 181                         "WHERE a.company_id = %s " \
 182                                 'AND a.type IN ' + self.ACCOUNT_TYPE + " " \
 183                                 "AND a.active", (data['form']['company_id'],))
 184                 self.account_ids = ','.join([str(a) for (a,) in self.cr.fetchall()])
 185
 186                 account_move_line_obj = pooler.get_pool(self.cr.dbname).get('account.move.line')
 187                 partner_to_use = []
 188
 189                 if self.date_lst and data['form']['soldeinit'] :
 190
 191                         self.cr.execute(
 192                                 "SELECT DISTINCT line.partner_id " \
 193                                 "FROM account_move_line AS line, account_account AS account " \
 194                                 "WHERE line.partner_id IS NOT NULL " \
 195                                         "AND line.account_id = account.id " \
 196                                         "AND line.date >= %s " \
 197                                         "AND line.date <= %s " \
 198                                         "AND line.reconcile_id IS NULL " \
 199                                         "AND line.account_id IN (" + self.account_ids + ") " \
 200                                         " " + PARTNER_REQUEST + " " \
 201                                         "AND account.company_id = %s " \
 202                                         "AND account.active " ,
 203                                 (self.date_lst[0],self.date_lst[len(self.date_lst)-1],data['form']['company_id']))
 204 #               else:
 205 #
 206 #                       self.cr.execute(
 207 #                               "SELECT DISTINCT line.partner_id " \
 208 #                               "FROM account_move_line AS line, account_account AS account " \
 209 #                               "WHERE line.partner_id IS NOT NULL " \
 210 #                                       "AND line.account_id = account.id " \
 211 #                                       "AND line.date IN (" + self.date_lst_string + ") " \
 212 #                                       "AND line.account_id IN (" + self.account_ids + ") " \
 213 #                                       " " + PARTNER_REQUEST + " " \
 214 #                                       "AND account.company_id = %s " \
 215 #                                       "AND account.active " ,
 216 #                               (data['form']['company_id']))
 217
 218                 res = self.cr.dictfetchall()
 219
 220                 for res_line in res:
 221                             partner_to_use.append(res_line['partner_id'])
 222                 new_ids = partner_to_use
 223
 224                 self.partner_ids = ','.join(map(str, new_ids))
 225                 objects = self.pool.get('res.partner').browse(self.cr, self.uid, new_ids)
 226                 super(third_party_ledger, self).set_context(objects, data, new_ids, report_type)
 227
 228         def lines(self, partner,data):
 229                 account_move_line_obj = pooler.get_pool(self.cr.dbname).get('account.move.line')
 230                 full_account = []
 231                 if data['form']['reconcil'] :
 232                         RECONCILE_TAG = " "
 233                 else:
 234                         RECONCILE_TAG = "AND l.reconcile_id IS NULL"
 235
 236 #               if data['form']['soldeinit'] :
 237 #
 238 #                       self.cr.execute(
 239 #                                       "SELECT l.id,l.date,j.code, l.ref, l.name, l.debit, l.credit " \
 240 #                                       "FROM account_move_line l " \
 241 #                                       "LEFT JOIN account_journal j " \
 242 #                                               "ON (l.journal_id = j.id) " \
 243 #                                       "WHERE l.partner_id = %s " \
 244 #                                               "AND l.account_id IN (" + self.account_ids + ") " \
 245 #                                               "AND l.date <= %s " \
 246 #                                               "AND l.reconcile_id IS NULL "
 247 #                                       "ORDER BY l.id",
 248 #                                       (partner.id, self.date_lst[0]))
 249 #                       res = self.cr.dictfetchall()
 250 #                       print"----res----",res
 251 #                       sum = 0.0
 252 #                       for r in res:
 253 #                               sum = r['debit'] - r['credit']
 254 #                               r['progress'] = sum
 255 #                               full_account.append(r)
 256                 if self.date_lst_string:
 257                         self.cr.execute(
 258                                 "SELECT l.id,l.date,j.code, l.ref, l.name, l.debit, l.credit " \
 259                                 "FROM account_move_line l " \
 260                                 "LEFT JOIN account_journal j " \
 261                                         "ON (l.journal_id = j.id) " \
 262                                 "WHERE l.partner_id = %s " \
 263                                         "AND l.account_id IN (" + self.account_ids + ") " \
 264                                         "AND l.date IN (" + self.date_lst_string + ") " \
 265                                         " " + RECONCILE_TAG + " "\
 266                                         "ORDER BY l.id",
 267                                         (partner.id,))
 268                 res = self.cr.dictfetchall()
 269                 sum = 0.0
 270                 for r in res:
 271                                 sum = r['debit'] - r['credit']
 272                                 r['progress'] = sum
 273                                 full_account.append(r)
 274
 275                 return full_account
 276
 277         def _sum_debit_partner(self, partner,data):
 278
 279                 account_move_line_obj = pooler.get_pool(self.cr.dbname).get('account.move.line')
 280                 result_tmp = 0.0
 281                 if data['form']['reconcil'] :
 282                         RECONCILE_TAG = " "
 283                 else:
 284                         RECONCILE_TAG = "AND reconcile_id IS NULL"
 285                 if self.date_lst and data['form']['soldeinit'] :
 286                         self.cr.execute(
 287                                 "SELECT sum(debit) " \
 288                                 "FROM account_move_line " \
 289                                 "WHERE partner_id = %s " \
 290                                         "AND account_id IN (" + self.account_ids + ") " \
 291                                         "AND reconcile_id IS NULL " \
 292                                         "AND date < %s " ,
 293                                 (partner.id, self.date_lst[0],))
 294                         contemp = self.cr.fetchone()
 295                         if contemp != None:
 296                                 result_tmp = contemp[0] or 0.0
 297                         else:
 298                                 result_tmp = result_tmp + 0.0
 299
 300                 if self.date_lst_string:
 301                         self.cr.execute(
 302                                         "SELECT sum(debit) " \
 303                                         "FROM account_move_line " \
 304                                         "WHERE partner_id = %s " \
 305                                                 "AND account_id IN (" + self.account_ids + ") " \
 306                                                 " " + RECONCILE_TAG + " " \
 307                                                 "AND date IN (" + self.date_lst_string + ") " ,
 308                                         (partner.id,))
 309
 310                         contemp = self.cr.fetchone()
 311                         if contemp != None:
 312                                 result_tmp = contemp[0] or 0.0
 313                         else:
 314                                 result_tmp = result_tmp + 0.0
 315                 return result_tmp
 316
 317         def _sum_credit_partner(self, partner,data):
 318                 account_move_line_obj = pooler.get_pool(self.cr.dbname).get('account.move.line')
 319                 result_tmp = 0.0
 320                 if data['form']['reconcil'] :
 321                         RECONCILE_TAG = " "
 322                 else:
 323                         RECONCILE_TAG = "AND reconcile_id IS NULL"
 324                 if self.date_lst and data['form']['soldeinit'] :
 325                         self.cr.execute(
 326                                         "SELECT sum(credit) " \
 327                                         "FROM account_move_line " \
 328                                         "WHERE partner_id=%s " \
 329                                                 "AND account_id IN (" + self.account_ids + ") " \
 330                                                 "AND reconcile_id IS NULL " \
 331                                                 "AND date < %s " ,
 332                                         (partner.id,self.date_lst[0],))
 333                         contemp = self.cr.fetchone()
 334                         if contemp != None:
 335                                 result_tmp = contemp[0] or 0.0
 336                         else:
 337                                 result_tmp = result_tmp + 0.0
 338
 339                 if self.date_lst_string:
 340                         self.cr.execute(
 341                                         "SELECT sum(credit) " \
 342                                         "FROM account_move_line " \
 343                                         "WHERE partner_id=%s " \
 344                                                 "AND account_id IN (" + self.account_ids + ") " \
 345                                                 " " + RECONCILE_TAG + " " \
 346                                                 "AND date IN (" + self.date_lst_string + ") " ,
 347                                         (partner.id,))
 348
 349                         contemp = self.cr.fetchone()
 350                         if contemp != None:
 351                                 result_tmp = contemp[0] or 0.0
 352                         else:
 353                                 result_tmp = result_tmp + 0.0
 354                 return result_tmp
 355
 356         def _sum_debit(self,data):
 357                 if not self.ids:
 358                         return 0.0
 359                 account_move_line_obj = pooler.get_pool(self.cr.dbname).get('account.move.line')
 360                 result_tmp = 0.0
 361                 if data['form']['reconcil'] :
 362                         RECONCILE_TAG = " "
 363                 else:
 364                         RECONCILE_TAG = "AND reconcile_id IS NULL"
 365                 if self.date_lst and data['form']['soldeinit'] :
 366                         self.cr.execute(
 367                                         "SELECT sum(debit) " \
 368                                         "FROM account_move_line " \
 369                                         "WHERE partner_id IN (" + self.partner_ids + ") " \
 370                                                 "AND account_id IN (" + self.account_ids + ") " \
 371                                                 "AND reconcile_id IS NULL " \
 372                                                 "AND date < %s " ,
 373                                         (self.date_lst[0],))
 374                         contemp = self.cr.fetchone()
 375                         if contemp != None:
 376                                 result_tmp = contemp[0] or 0.0
 377                         else:
 378                                 result_tmp = result_tmp + 0.0
 379
 380                 if self.date_lst_string:
 381                         self.cr.execute(
 382                                         "SELECT sum(debit) " \
 383                                         "FROM account_move_line " \
 384                                         "WHERE partner_id IN (" + self.partner_ids + ") " \
 385                                                 "AND account_id IN (" + self.account_ids + ") " \
 386                                                 " " + RECONCILE_TAG + " " \
 387                                                 "AND date IN (" + self.date_lst_string + ") "
 388                                         )
 389
 390                         contemp = self.cr.fetchone()
 391                         if contemp != None:
 392                                 result_tmp = contemp[0] or 0.0
 393                         else:
 394                                 result_tmp = result_tmp + 0.0
 395
 396                 return result_tmp
 397
 398
 399         def _sum_credit(self,data):
 400                 if not self.ids:
 401                         return 0.0
 402                 account_move_line_obj = pooler.get_pool(self.cr.dbname).get('account.move.line')
 403                 result_tmp = 0.0
 404                 if data['form']['reconcil'] :
 405                         RECONCILE_TAG = " "
 406                 else:
 407                         RECONCILE_TAG = "AND reconcile_id IS NULL"
 408                 if self.date_lst and data['form']['soldeinit'] :
 409                         self.cr.execute(
 410                                         "SELECT sum(credit) " \
 411                                         "FROM account_move_line " \
 412                                         "WHERE partner_id IN (" + self.partner_ids + ") " \
 413                                                 "AND account_id IN (" + self.account_ids + ") " \
 414                                                 "AND reconcile_id IS NULL " \
 415                                                 "AND date < %s " ,
 416                                         (self.date_lst[0],))
 417                         contemp = self.cr.fetchone()
 418                         if contemp != None:
 419                                 result_tmp = contemp[0] or 0.0
 420                         else:
 421                                 result_tmp = result_tmp + 0.0
 422
 423                 if self.date_lst_string:
 424                         self.cr.execute(
 425                                         "SELECT sum(credit) " \
 426                                         "FROM account_move_line " \
 427                                         "WHERE partner_id IN (" + self.partner_ids + ") " \
 428                                                 "AND account_id IN (" + self.account_ids + ") " \
 429                                                 " " + RECONCILE_TAG + " " \
 430                                                 "AND date IN (" + self.date_lst_string + ") "
 431                                         )
 432                         contemp = self.cr.fetchone()
 433                         if contemp != None:
 434                                 result_tmp = contemp[0] or 0.0
 435                         else:
 436                                 result_tmp = result_tmp + 0.0
 437
 438                 return result_tmp
 439
 440         def _get_company(self, form):
 441                 return pooler.get_pool(self.cr.dbname).get('res.company').browse(self.cr, self.uid, form['company_id']).name
 442
 443         def _get_currency(self, form):
 444                 return pooler.get_pool(self.cr.dbname).get('res.company').browse(self.cr, self.uid, form['company_id']).currency_id.name
 445
 446 report_sxw.report_sxw('report.account.third_party_ledger', 'res.partner',
 447                 'addons/account/report/third_party_ledger.rml',parser=third_party_ledger,
 448                 header=False)
 449
 450 report_sxw.report_sxw('report.account.third_party_ledger_other', 'res.partner',
 451                 'addons/account/report/third_party_ledger_other.rml',parser=third_party_ledger,
 452                 header=False)
 453
 454 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: