odoo2next.py

from frappeclient import FrappeClient
import psycopg2
import psycopg2.extras
import unicodedata
import traceback
import datetime

print "logging in..."
client = FrappeClient("https://XXXXXXXXXXXXXXXXXX", "XXXXXXXXXXX", "XXXXXXXXXXXXXXXXX")
conn = psycopg2.connect(dbname="odoo_dbname", user="odoo_dbuser", host="odoo_dbhost", port="odoo_dbport", password="odoo_dbpwd")
cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)


def products():
    cur.execute("SELECT * from product_product")
    count = 1
    failed = []

    for product in cur:
        doc = {
            "doctype": "Item",
            "name": product['name_template'],
            "item_code": product['default_code'],
            "item_name": product['name_template'],
            "description": product['name_template'],
            "item_group": "Products",
            "stock_uom": "Unit",
            "is_stock_item": 1,
            "openerp_id": product['id'],
            "default_warehouse": "Stores - CRL"

        }
        if doc['item_code'] is None:
            doc_name = unicodedata.normalize('NFKD', unicode(doc['name'], 'utf-8')).encode('ASCII', 'ignore')
            doc['item_code'] = doc_name[:3] +"/"+ str(doc['openerp_id'])
        else:
            doc['item_code'] = doc['item_code'] + "/" + str(doc["openerp_id"])
        try:
            client.insert(doc)
            print "Processing %i %s" % (count, product['name_template'])
        except:
            failed.append(product['name_template'])
            print "Processing failed %i %s %s" % (count, product['name_template'], product['default_code'])
            client.insert(doc)

        count+=1
    print failed


def users():
    cur.execute("SELECT * from res_users")
    count = 1
    failed = []

    for user in cur:
        doc = {
            "doctype": "User",
            "name": user['login'],
            "first_name": user['name'],
            "user_type": 'System User',
            "new_password": user['password'],
        }
        if user['email'] is None:
            doc['email'] = doc['name'].lower()+'@chillingtonrwanda.com'
        print "Processing %i %s" % (count, user['name'])
        try:
            client.insert(doc)
        except:
            print "%s Failed" % doc['name']
        count+=1
    print failed

def customer_supplier():
    cur.execute("SELECT * from res_partner")
    count = 1
    failed = []

    suppliers = []
    customers = []

    for partner in cur:
        try:
            if partner['customer'] == True:
                doc = {
                "doctype": "Customer",
                "name": partner['name'],
                "customer_name": partner['name'],
                "customer_type": 'Company',
                "customer_group": "Commercial",
                "territory": "Rwanda",
                }
                if doc['name'] in customers:
                    doc['name'] += str(partner['id'])
                    doc['customer_name'] += str(partner['id'])
                client.insert(doc)
                customers.append(doc['name'])
        except:
            print partner

        try:
            if partner['supplier'] == True:
                doc = {
                    "doctype": "Supplier",
                    "name": partner['name'],
                    "supplier_name": partner['name'],
                    "supplier_type": 'Local',
                }
                if doc['name'] in suppliers:
                    doc['name'] += str(partner['id'])
                client.insert(doc)
                suppliers = doc['name']
        except:
            print partner
        count+=1
        print "Processing %i" % count
    print failed


def contacts():
    cur.execute("select p.name as partner_name,p.customer as customer, p.supplier as supplier,a.* from res_partner_address a, res_partner p where p.id=a.partner_id and (a.email is not null or a.phone is not null or a.mobile is not null or a.name is not null)")
    count = 1
    failed = []

    for contact in cur:
        doc = {
            "doctype": "Contact",
            "name": contact['name'],
            "first_name": contact['name'],
            "email_id": contact['email'],
            "phone": contact['phone'],
            "mobile_no": contact['mobile'],
        }
        if doc['name'] is None:
            doc['name'] = contact['partner_name']
            doc['first_name'] = contact['partner_name']
        if contact['supplier']:
            doc['supplier'] = contact['partner_name']
        if contact['customer']:
            doc['customer'] = contact['partner_name']
        print "Processing %i %s" % (count, doc['name'])
        try:
            client.insert(doc)
        except:
            print "%s Failed" % doc['name']
        count+=1
    print failed

def sales_order():
    cur2 = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    cur.execute("select sale_order.id,date_order, res_partner.name as partner_name,amount_untaxed,amount_tax,amount_total,sale_order.name,state from sale_order, res_partner where res_partner.id=sale_order.partner_id LIMIT 1000")
    count = 1
    failed = []

    for sale_order in cur:
        doc = {
            "doctype": "Sales Order",
            "customer": sale_order['partner_name'],
            "title": sale_order['name'],
            "contact_display": sale_order['name'],
            "order_type": "Sales",
            "transaction_date": str(sale_order['date_order']),
            "delivery_date": str(sale_order['date_order']),
            "company": "Chillington",
            'currency': 'RWF',
            'conversion_rate': 1.0,
            'selling_price_list': 'Standard Selling',
            'price_list_currency': 'RWF',
            'plc_conversion_rate': 1.0,
            'naming_series': 'SO-',
            'order_type': 'Sales',
            'status': 'Draft',
            "items":[]
        }
        query = """
        select id,product_uos_qty,product_uos, product_uom_qty,product_uom,price_unit,name from sale_order_line where order_id=%i
        """ % int(sale_order['id'])
        cur2.execute(query)
        for sale_order in cur2:
            item = client.get_doc('Item',
                        filters=[["Item", "openerp_id", "=", int(sale_order['id'])]],
                        fields=["name", "item_name", "item_code"])
            doc['items'].append({
                'qty': float(sale_order['product_uos_qty']),
                'doctype':'Sales Order Item',
                'item_code':item[0]['item_code'],
                'item_name':item[0]['item_name'],
                'description':item[0]['item_name'],
                'rate': float(sale_order['price_unit'])
            })
        print doc
        try:
            client.insert(doc)
        except:
            traceback.print_exc()
        count += 1


def export_purchase_order():
    cur2 = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    cur.execute("""
                SELECT
                    purchase_order.id,
                    date_order,
                    res_partner.name as partner_name,
                    amount_untaxed,amount_tax,
                    amount_total,
                    purchase_order.name,
                    state
                    FROM purchase_order, res_partner
                    WHERE res_partner.id=purchase_order.partner_id AND date_order>='2016-01-01'
                     LIMIT 10""")
    for purchase_order in cur:
        #check this PO has an invoice
        cur_check_invoice = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
        cur_check_invoice.execute("""
            select origin,reference from account_invoice WHERE origin='%s'
        """ % purchase_order['name'])
        doc = {
            "doctype": "Purchase Order",
            "supplier": purchase_order['partner_name'],
            "title": purchase_order['name'],
            "contact_display": purchase_order['name'],
            #"order_type": "Sales",
            "transaction_date": str(purchase_order['date_order']),
            #"delivery_date": str(purchase_order['date_order']),
            "company": "Chillington",
            'currency': 'RWF',
            'conversion_rate': 1.0,
            'buying_price_list': 'Standard Buying',
            'price_list_currency': 'RWF',
            'plc_conversion_rate': 1.0,
            'naming_series': 'PO-',
            #'order_type': 'Sales',
            "docstatus": 1 if cur_check_invoice.rowcount > 0 else 0,
            "items":[]
        }
        query = """
        select id,product_qty,product_uom,price_unit,name,product_id from purchase_order_line where order_id=%i
        """ % int(purchase_order['id'])
        cur2.execute(query)
        for purchase_order_line in cur2:
            item = client.get_doc('Item',
                        filters=[["Item", "openerp_id", "=", int(purchase_order_line['product_id'])]],
                        fields=["name", "item_name", "item_code"])
            print item[0]['item_name']

            doc['items'].append({
                'qty': float(purchase_order_line['product_qty']),
                'doctype':'Purchase Order Item',
                'item_code':item[0]['item_code'],
                'item_name':item[0]['item_name'],
                'description':item[0]['item_name'],
                'rate': float(purchase_order_line['price_unit']),
                'schedule_date': str(purchase_order['date_order'])
            })
        print doc
        try:
            client.insert(doc)
        except:
            traceback.print_exc()


def supplier_invoice():
    cur2 = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    cur.execute("select account_invoice.id,account_invoice.type,date_invoice, res_partner.name as partner_name,amount_untaxed,amount_tax,amount_total,account_invoice.name,state from account_invoice, res_partner where res_partner.id=account_invoice.partner_id LIMIT 1000")
    count = 1
    failed = []


    for invoice in cur:
        if invoice['state']=="paid":
            print "grgrgrg"
        elif invoice['state']=="paid":
            if invoice['type']=="in_invoice":
                doc = {
                    "doctype": "Purchase Invoice",
                    "supplier": invoice['partner_name'],
                    "title": invoice['name'],
                    "contact_display": invoice['name'],
                    "posting_date": str(invoice['date_invoice']),
                    "company": "Chillington",
                    'currency': 'RWF',
                    'credit_to': 'Creditors - C',
                    'conversion_rate': 1.0,
                    'price_list_currency': 'RWF',
                    'plc_conversion_rate': 1.0,
                    'naming_series': 'PINV-',
                    'status': 'Draft',
                    "items":[]
                }

                query = """
                select * from account_invoice_line where invoice_id=%i
                """ % int(invoice['id'])
                cur2.execute(query)
                for invoice_item in cur2:
                    #print invoice_item
                    item = client.get_doc('Item',
                            filters=[["Item", "openerp_id", "=", int(invoice_item['product_id'])]],
                            fields=["name", "item_name", "item_code"])
                    print item[0]['item_name']
                    #print item_code
                    this_item = {
                        'qty': float(invoice_item['quantity']),
                        'doctype':'Purchase Invoice Item',
                        'item_code':item[0]['item_code'] if item[0]['item_code'] is not None else item['openerp_id'],
                        'item_name':item[0]['item_name'],
                        'description':item[0]['item_name'],
                        'rate': float(invoice_item['price_unit']),
                        'amount': 5000,
                        'base_amount': 'RWF',
                        'base_rate': 'RWF'
                        }
                    print this_item
                    doc['items'].append({
                        'qty': float(invoice_item['quantity']),
                        'doctype':'Purchase Invoice Item',
                        'item_code':item[0]['item_code'] if item[0]['item_code'] is not None else item['openerp_id'],
                        'item_name':item[0]['item_name'],
                        'description':item[0]['item_name'],
                        'rate': float(invoice_item['price_unit']),
                        'amount': 5000,
                        'base_amount': 'RWF',
                        'base_rate': 'RWF'
                        })
                    #print doc
                try:
                    client.insert(doc)
                except:
                   traceback.print_exc()
                count += 1
            else:
                pass


if __name__ == "__main__":
    #products()
    #users()
    #customer_supplier()
    #contacts()
    #sales_order()
    export_purchase_order()