addons/auction/migrate/migrate.py

   1 #!/usr/bin/python
   2 # -*- encoding: utf-8 -*-
   3
   4 import psycopg
   5 import csv
   6
   7 db_old = "in"
   8 print 'Extracting data from db '+db_old
   9
  10 def import_sql(cr, fname, query, fields=None, trigger=None):
  11     cr.execute(query)
  12     if not fields:
  13         fields = map(lambda x: x[0], cr.description)
  14     fp = file(fname,'wb')
  15     result = cr.fetchall()
  16     if trigger:
  17         result = map(lambda x: tuple(trigger(cr, list(x))), result)
  18     writer = csv.writer(fp,lineterminator='\n')
  19     writer.writerow(fields)
  20     writer.writerows(result)
  21     fp.close()
  22
  23
  24 db = psycopg.connect("dbname="+db_old)
  25 cr = db.cursor()
  26
  27
  28
  29 cr.execute("update auction_lots set state='draft' where state is null or state = '' ")
  30 cr.execute("update auction_lots set state='sold' where state='invoiced'")
  31
  32
  33 cr.execute('select id,name from res_partner')
  34 res= cr.fetchall()
  35 names=[]
  36 for r in res:
  37     if r[1] in names:
  38         cr.execute("update res_partner set name= %s where id=%d",(r[1]+str(r[0]),r[0]))
  39     else:
  40         names.append(r[1])
  41
  42 # cr.execute('select id,name from res_country')
  43 # res= cr.fetchall()
  44 # names=[]
  45 # for r in res:
  46 #   if r[1] in names:
  47 #       cr.execute("update res_country set name= %s where id=%d",(str(r[1])+str(r[0]),r[0]))
  48 #   else:
  49 #       names.append(r[1])
  50
  51 # cr.execute('select id,code from res_country')
  52 # res= cr.fetchall()
  53 # names=[]
  54 # for r in res:
  55 #   if r[1] in names:
  56 #       cr.execute("update res_country set code= %s where id=%d",(str(r[1])+str(r[0]),r[0]))
  57 #   else:
  58 #       names.append(r[1])
  59
  60
  61
  62 def _account_trigger(cr, x):
  63     x = list(x)
  64     if x[5] not in ('receivable','payable','view','income','expense','tax','cash','asset','equity','closed'):
  65         x[5] = {'stock_inventory':'asset','stock_income':'income','stock_expense':'expense'}.get(x[5], 'asset')
  66     return tuple(x)
  67 # account.account
  68 import_sql(cr,
  69     'account.account.csv',
  70     "select 'account' || id as id, code, name, 'EUR' as currency_id, True as active, type from account_account",
  71     trigger = _account_trigger
  72 )
  73
  74 #account.tax
  75 import_sql(cr,
  76     'account.tax.csv',
  77     """select
  78             'tax' || id as id,
  79             domain,
  80             name,
  81             'account'||account_collected_id as "account_collected_id:id",
  82             'account'||account_paid_id as "account_paid_id:id",
  83             amount,
  84             child_depend,
  85             type
  86         from
  87             account_tax
  88     """
  89 )
  90
  91 #res.country
  92 # import_sql(cr,
  93 #   'res.country.csv',
  94 #   """
  95 #   select
  96 #   'country'||id as id,
  97 #   coalesce(name,id::char(10)) as "name",
  98 #   coalesce(code,id::char(10)) as "code"
  99 #   from
 100 #   res_country
 101 #   """
 102 # )
 103 #clients category
 104 import_sql(cr,
 105     'res.partner.category.csv',
 106     """
 107     select
 108     name,
 109     active
 110     from
 111     res_partner_category
 112     """
 113 )
 114 ##res.partner.category.rel
 115 #import_sql(cr,
 116 #   'res.partner.category.rel.csv',
 117 #   """
 118 #   select
 119 #   'partner'||rel.partner_id as "partner_id:id",
 120 #   'categ'||rel.category_id as "category_id:id"
 121 #   from
 122 #   res_partner_category_rel rel, res_partner r
 123 #   where rel.partner_id=r.id
 124 #   """
 125 #)
 126 #res.partner
 127 import_sql(cr,
 128     'res.partner.csv',
 129     """
 130     select
 131     'partner'||r.id as id,
 132     r.lang,
 133     r.website,
 134     r.name,
 135     r.comment,
 136
 137     r.active
 138     from
 139     res_partner r
 140     """
 141 )
 142
 143 #   (select cat.name||'\N' from res_partner r1, res_partner_category cat,res_partner_category_rel rel where r1.id=rel.partner_id and rel.category_id=cat.id) as "category_id",
 144 #   title,
 145
 146 #res.partner.address
 147 import_sql(cr,
 148     'res.partner.address.csv',
 149     """
 150     select
 151     'address'||id as id,
 152     coalesce('partner'||partner_id,'partner_unknown') as "partner_id:id",
 153     name,
 154     street,
 155     zip,
 156     city,
 157     email,
 158     phone,
 159     type,
 160     mobile,
 161     fax
 162     from
 163     res_partner_address
 164     """
 165 )
 166 #   'country'||country as "country_id:id",
 167
 168
 169 #auction.lot.category
 170
 171 import_sql(cr,
 172     'auction.lot.category.csv',
 173     """
 174     select
 175         'cat'||id as "id",
 176         name,
 177         active
 178     from
 179         auction_lot_category
 180     order by
 181         id
 182     """
 183 )
 184 #auction.dates.csv
 185
 186 import_sql(cr,
 187     'auction.dates.csv',
 188     """
 189     select
 190
 191         'date'||id as "id",
 192         'Auction'||id as "name",
 193         expo1,
 194         expo2,
 195         'auction_db.account'||acc_expense as "acc_expense:id",
 196         'auction_db.account'||acc_income as "acc_income:id",
 197         coalesce(state,'draft') as "state",
 198         auction1,
 199         auction2,
 200         'account.expenses_journal' as "journal_seller_id:id",
 201         'account.sales_journal' as "journal_id:id",
 202         'auction_db.aaa_un' as "account_analytic_id:id"
 203
 204     from
 205         auction_dates
 206     order by
 207         id
 208     """
 209 )
 210
 211 # auction.artist.csv
 212
 213 import_sql(cr,
 214     'auction.artists.csv',
 215     """
 216     select
 217         'artist'||id as "id",
 218         name,
 219         biography,
 220         birth_death_dates
 221
 222     from
 223         auction_artists
 224     order by
 225         id
 226     """
 227 )
 228
 229
 230 # auction.deposit.csv
 231
 232 import_sql(cr,
 233     'auction.deposit.csv',
 234     """
 235     select
 236         'deposit'||id as "id",
 237         name,
 238         date_dep,
 239         coalesce('auction_db.partner'||partner_id,'auction_db.partner_unknown') as "partner_id:id",
 240         method,
 241         'auction_db.tax'||tax_id as "tax_id:id",
 242         total_neg
 243
 244     from
 245         auction_deposit
 246     order by
 247         id
 248     """
 249 )
 250
 251
 252 #lot
 253 import_sql(cr,
 254     'auction.lots.csv',
 255     """
 256     select
 257         'lot'||l.id as id,
 258         'auction_db2.date'||l.auction_id as "auction_id:id",
 259         'auction_db2.deposit'||l.bord_vnd_id as "bord_vnd_id:id",
 260         l.name,
 261         l.name2,
 262         l.author_right,
 263         l.lot_est1,
 264         l.lot_est1,
 265         l.lot_local,
 266         l.artist_id,
 267         l.artist2_id,
 268         l.important,
 269         l.obj_desc,
 270         l.obj_num,
 271         l.obj_ret,
 272         l.obj_comm,
 273         l.obj_price,
 274         l.ach_avance,
 275         l.ach_login,
 276         'auction_db.partner'||l.ach_uid as "ach_uid:id",
 277         l.ach_emp,
 278         l.vnd_lim,
 279         l.vnd_lim_net,
 280         coalesce(l.state,'draft') as "state",
 281         'auction_db.product_product_unknown' as "product_id:id"
 282     from
 283         auction_lots l join auction_dates d on (l.auction_id=d.id) join auction_deposit o on (l.bord_vnd_id=o.id) where d.expo2 like '2007%'
 284     order by
 285         l.id
 286     """
 287 )
 288
 289
 290 #       'auction_db.invoice'||ach_inv_id as "ach_inv_id:id",
 291 #       'auction_db.invoice'||ach_inv_id as "sel_inv_id:id",
 292
 293 def _deposit(cr, rec):
 294     if not rec[3]:
 295         rec[3] = '6025'
 296     return rec
 297
 298
 299
 300
 301 # 'invoice'||invoice_id as "invoice_id:id",
 302 import_sql(cr,
 303     'account.invoice.csv',
 304     """
 305     select
 306         'invoice'||id as "id",
 307         comment,
 308         date_due,
 309         number,
 310         'base.main_company' as "company_id:id",
 311         'auction_db.address'||address_invoice_id as "address_invoice_id:id",
 312         'auction_db.partner'||partner_id as "partner_id:id",
 313         state,
 314         type,
 315         'auction_db.account'||account_id as "account_id:id",
 316         date_invoice,
 317         name,
 318         'auction_db.address'||address_contact_id as "address_contact_id:id"
 319     from
 320         account_invoice
 321     order by
 322         id
 323     """
 324 )
 325
 326 import_sql(cr,
 327     'account.invoice.line.csv',
 328     """
 329     select
 330         name,
 331         'invoice'||invoice_id as "invoice_id:id",
 332         price_unit,
 333         'auction_db.account'||account_id as "account_id:id",
 334         quantity
 335     from
 336         account_invoice_line
 337     order by
 338         id
 339     """
 340 )
 341
 342 #auction.bid.csv
 343
 344 import_sql(cr,
 345     'auction.bid.csv',
 346     """
 347     select
 348         'bid'||b.id as "id",
 349         'date'||b.auction_id as "auction_id:id",
 350         coalesce('auction_db.partner'||b.partner_id,'auction_db.partner_unknown') as "partner_id:id",
 351         b.name,
 352         b.contact_tel
 353     from
 354         auction_bid b join auction_dates d on (b.auction_id=d.id) where d.expo2 like '2007%'
 355     order by
 356         b.id
 357     """
 358 )
 359
 360
 361 #auction.bid_line.csv
 362 import_sql(cr,
 363     'auction.bid_line.csv',
 364     """
 365     select
 366         line.name,
 367         'auction_db2.bid'||line.bid_id as "bid_id:id",
 368         'auction_db3.lot'||line.lot_id as "lot_id:id",
 369         line.price,
 370         line.call
 371
 372     from
 373         auction_bid_line line join auction_bid b on (b.id=line.bid_id) join auction_lots lot on (lot.id=line.lot_id) join auction_deposit o on (lot.bord_vnd_id=o.id)  join auction_dates d on (lot.auction_id=d.id) where d.expo2 like '2007%' and o.date_dep like '2007%'
 374     order by
 375         line.id
 376     """
 377 )
 378
 379
 380
 381
 382
 383
 384 cr.close()
 385 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:
 386