~zaber/openobject-addons/zaber-custom

from osv import fields
from osv import osv
from tools import config
import re
import urllib2
import csv

class product_product(osv.osv):
    _inherit = 'product.product'

    kit_regex_string = "(K\d+|KT\w+|PMF3|MC06|MC04)$"

    def sanity_check(self, cr, uid, context=None):
        self.sanity_check_fast(cr, uid, context)
        self.sanity_check_slow(cr, uid, context)
        return 0

    def sanity_check_fast(self, cr, uid, context):
        ''' A set of fast sanity checks
        '''
        self.web_lookup = False
        self.product_bom(cr, uid, context)
        self.product_multiple_revisions(cr, uid, context)
        self.product_track_product_true(cr, uid, context)
        self.product_track_incoming_true(cr, uid, context)
        self.product_track_outgoing_true(cr, uid, context)
        self.product_costing_method(cr, uid, context)
        self.product_orderpoint(cr, uid, context)
        self.product_obsolete_sellable(cr, uid, context)
        self.product_unsellable_with_estimated_sales(cr, uid, context)
        self.product_stock_paradox(cr, uid, context)
        self.product_weights(cr, uid, context)
        self.product_bought_not_purchasable(cr, uid, context)

    def sanity_check_slow(self, cr, uid, context):
        ''' A set of slow sanity checks
        '''
        self.product_prices_sync(cr, uid, context)
        self.product_weight_sync(cr, uid, context)

    def product_bought_not_purchasable(self, cr, uid, context):
        """ Checking for products that have a supply method of "buy" but are
            not marked with "can be purchased"

            Criteria for 'insanity':
            Active = True
            Product Type != 'Service'
            Status = 'In Production'
            Supply Method = 'Buy'
            Can be Purchased = False

            refs #176
        """
        case_id = self.pool.get('crm.helpdesk').sanity_test_create_stub_case(cr,uid,context,
        "Found product with supply method 'Buy' but no 'Can be purchased' ",
        """ This is a stub to track incidents where products have a supply
            method of "buy" but are not set as "Can be purchased"
        """,
        'product_bought_not_purchasable')

        cr.execute('''
                      select
                        pp.id,
                        pt.name,
                        pp.default_code
                      from
                        product_product pp left join
                        product_template pt on pp.product_tmpl_id = pt.id left join
                        mrp_production mp on pp.id = mp.product_id
                      where
                        pp.active != 'f'
                        and pt.type != 'service'
                        and pt.supply_method = 'buy'
                        and pt.purchase_ok != 't'
                        and mp.state != 'end'
                        and mp.state != 'obsolete'
                        ;
        ''')
        prod_ids = [row[0] for row in cr.fetchall()]
        if not prod_ids:
            return

        out = "The following products have a supply method of 'Buy' but" \
              "are not set as 'Can be purchased':\n"

        for prod in self.browse(cr, uid, prod_ids):
            out += "%i:(%s) %s\n\n" % (prod.id,prod.default_code,prod.name)

        out += "\n\n"
        self.pool.get("crm.helpdesk").sanity_test_log(cr,
                                                      uid,
                                                      context,
                                                      case_id,
                                                      out)


    def product_stock_paradox(self,cr,uid,context):
        case_id = self.pool.get('crm.helpdesk').sanity_test_create_stub_case(cr,uid,context,
        "Found Products with negative Real Stock",
        """ This is a stub to track incidents where products are in the database with a negative quantity.
        """,
        'product_stock_paradox')

        offset = 0
        ids = [1]
        prod_ids = []
        while ids:
            ids = self.search(cr,uid,[],offset=offset,limit=100)
            for prod in self.browse(cr,uid,ids,context):
                if prod.qty_available < 0:
                    prod_ids.append(prod.id)
            offset += len(ids)

        if not prod_ids:
            return # horray, no weirdness

        problem_writeup = "The following products have a negative quantity:\n\n"
        for prod in self.browse(cr,uid,prod_ids):
            problem_writeup += "%i:(%s) %s Qty: %f\n\n" % (prod.id,prod.default_code,prod.name,prod.qty_available)
        problem_writeup += "\n\n"

        # now that we've finished documenting the problem let's notify the 
        # appropriate users
        self.pool.get('crm.helpdesk').sanity_test_log(
                  cr, uid, context,
                  case_id,
                  problem_writeup
              )

    def product_unsellable_with_estimated_sales(self,cr,uid,context):
        case_id = self.pool.get('crm.helpdesk').sanity_test_create_stub_case(cr,uid,context,
        "Found Products with Daily Sales Mode of Estimated but Can Be Sold marked False",
        """ This is a stub to track incidents where products that are not marked 'Can Be Sold' have a 'Daily Sales Mode'
            of 'estimated'. Products that we don't sell should never have estimated daily sales.
        """,
        'product_unsellable_with_estimated_sales')

        prod_ids = self.search(cr,uid,[
                                  ('daily_sales_mode','=','estimated'),
                                  ('sale_ok','=',False),
                                  ('daily_sales','<>',0.00),
                                  ])
        if not prod_ids:
            return # horray, no weirdness

        problem_writeup = "The following products have a Daily Sales Mode of Estimated but Can Be Sold marked False:\n\n"
        for prod in self.browse(cr,uid,prod_ids):
            problem_writeup += "%i:(%s) %s\n\n" % (prod.id,prod.default_code,prod.name)
        problem_writeup += "\n\n"

        # now that we've finished documenting the problem let's notify the 
        # appropriate users
        self.pool.get('crm.helpdesk').sanity_test_log(
                  cr, uid, context,
                  case_id,
                  problem_writeup
              )

    def product_obsolete_sellable(self,cr,uid,context):
        case_id = self.pool.get('crm.helpdesk').sanity_test_create_stub_case(cr,uid,context,
        "Found Products with Status of Obsolete but Can Be Sold marked True",
        """ This is a stub to track incidents where products marked 'Obsolete' still have their
            'Can Be Sold' designation attached
        """,
        'product_obsolete_sellable')

        prod_ids = self.search(cr,uid,[
                                  ('state','=','obsolete'),
                                  ('sale_ok','=',True),
                                  ])
        if not prod_ids:
            return # horray, no weirdness

        problem_writeup = "The following obsolete products can still be sold:\n\n"
        for prod in self.browse(cr,uid,prod_ids):
            problem_writeup += "%i:(%s) %s\n\n" % (prod.id,prod.default_code,prod.name)
        problem_writeup += "\n\n"

        # now that we've finished documenting the problem let's notify the 
        # appropriate users
        self.pool.get('crm.helpdesk').sanity_test_log(
                  cr, uid, context,
                  case_id,
                  problem_writeup
              )


    def _product_kits(self,cr,uid,context):
        """ Get a dict of all the accessory kits (eg. PMF3, KT04) on 
            the system
        """

        if hasattr(self,'kit_lookup'):
            return self.kit_lookup

        # Find the names of all the kit subcomponents
        cr.execute( """
                    SELECT id
                    FROM   product_product
                    WHERE  default_code ~* E'^%s$'
                    AND    active = 't'
                  """ % ( re.escape(self.kit_regex_string) ) )
        kit_ids = [row[0] for row in cr.fetchall()]
        self.kit_lookup = {}
        for product in self.browse(cr,uid,kit_ids,context):
            self.kit_lookup[product.default_code] = product

        return self.kit_lookup
        
    def _parse_product_name(self, cr, uid, context, code):
        """ Parse the code into longest product found in ZERP.
            If code is T-LSM025B-S-KT04, returned array is 
            T-LSM025B-S and KT04, not T-LSM025B
        """
        segs = code.split('-')
        result = []
        i = 0
        
        while i < len(segs):
            lookup = segs[i:]
            while lookup:
                search = '-'.join(lookup)
                found_ids = self.search(cr, uid, [
                            ('default_code','=',search),
                            ('state','=','sellable'),],
                            order="default_code, revision desc")
                if found_ids: 
                    result.append(found_ids[0])
                    i = segs.index( lookup[len(lookup)-1] ) + 1
                    break
                else:
                    lookup.pop()
                    continue
            if not lookup:
                if not result:
                    # code does not begin with a found product, 
                    # treat as missing product in ZERP
                    break
                i += 1
        return result
        
    def _zerp_web_products(self,cr,uid,context,web_products):
        """ Get a dict of all the products that match or are
            kits of the web_products. 
        """

        if hasattr(self,'product_lookup'):
            return self.product_lookup

        product_ids = self.search(cr,uid,[
                        ('default_code','in',web_products.keys()),
                        ('state','=','sellable'),],
                      order="default_code, revision desc")
        price_differences = []
        self.product_lookup = {} 
        for product in self.browse(cr,uid,product_ids,context):
            self.product_lookup[product.default_code] = product

        return self.product_lookup


    def _product_web_compare(self,cr,uid,context,web_products):

        product_lookup = self._zerp_web_products(cr,uid,context,web_products)

        missing_records = {}
        discontinued_but_sellable_records = {}
        available_but_not_sellable_records = {}
        matched_records = {}
        composite_records = {}
        
        for web_code,web_product in web_products.copy().iteritems():
            # Compare the records
            if web_code in product_lookup:
                if product_lookup[web_code].sale_ok is True:
                    if web_product['sellable'] == '1':
                        matched_records[web_code] = product_lookup[web_code]
                    else:
                        discontinued_but_sellable_records[web_code] = web_product
                else:
                    if web_product['sellable'] == '1':
                        available_but_not_sellable_records[web_code] = web_product
            else:
                # parse code into codes of devices and kits found in ZERP
                matches = self._parse_product_name(cr, uid, context, web_code)
                if matches:
                # Change web discrepancies sanity check to look for the base 
                # part and accessory kit separately in zerp if the whole part 
                # number is not found.

                # Ex if the web part number is ASR100B120B-E03T3-T-LSM050B-S-KT02-KT03
                # then _parse_product_name returns a list of ASR100B120B-E03T3, 
                # T-LSM050B-S, KT02, KT03. If either one is marked as not sellable, 
                # the whole product will be considered not sellable

                    sale_ok = True
                    parts = []
                    for match in matches:
                        part = self.browse(cr,uid,match)
                        parts.append(part)
                        if part.sale_ok is False:
                            sale_ok = False
                    
                    if sale_ok is True:
                        if web_product['sellable'] == '1':
                            composite_records[web_code] = {
                              'parts': parts,
                            }
                        else:
                            discontinued_but_sellable_records[web_code] = web_product
                    else:
                        if web_product['sellable'] == '1':
                            available_but_not_sellable_records[web_code] = web_product

                # Nothing found that matches
                else:
                    if web_product['sellable'] == '1': missing_records[web_code] = web_product
                    continue

        return {
          'missing_records': missing_records,
          'discontinued_but_sellable_records': discontinued_but_sellable_records,
          'available_but_not_sellable_records': available_but_not_sellable_records,
          'matched_records': matched_records,
          'composite_records': composite_records
        }

    def _web_product_lookup(self,cr,uid,context):
        if self.web_lookup:
            return self.web_lookup

        web_data = urllib2.urlopen(config['external_website_price_feed']).read()
        self.web_lookup = {}

        send_email = False
        for row in csv.reader(web_data.splitlines()):
            if row:
                self.web_lookup[row[1]] = dict(zip(
                  ('id','partnumber','price','weight','spec_weight', 'sellable'), row
                ))

        return self.web_lookup

    def product_weight_sync(self,cr,uid,context):
        case_id = self.pool.get('crm.helpdesk').sanity_test_create_stub_case(cr,uid,context,
        "Found Products that do not have the same weight online and in Zerp",
        """ This is a stub to track incidents of products that have a weight online that does not
            match the weight listed in Zerp
        """,
        'product_weight_sync')

        web_products = self._web_product_lookup(cr,uid,context)
        res = self._product_web_compare(cr,uid,context,web_products)

        spec_differences = []
        for code, rec in web_products.iteritems():
            if rec['sellable'] is False: continue
            web_weight = float(rec['weight'] or 0)
            spec_weight = float(rec['spec_weight'] or 0)
            delta = abs(spec_weight - web_weight)
            if delta > 0.001:
                spec_differences.append(
                  "   %s - Spec Weight: %.03f, Web Weight: %.03f" % ( code, spec_weight, web_weight )
                )

        mismatched_records = []
        for code, rec in res['matched_records'].iteritems():
            web_weight = float(web_products[code]['weight'] or 0)
            delta = abs(rec.weight - web_weight)
            if delta > 0.001:
                mismatched_records.append(
                  "   %s - Zerp Weight: %.03f, Web Weight: %.03f" % ( code, rec.weight, web_weight )
                )

        for code, rec in res['composite_records'].iteritems():
            web_weight = float(web_products[code]['weight'] or 0)
            zerp_weight = 0
            for part in rec['parts']:
                zerp_weight += part.weight
            delta = abs(zerp_weight - web_weight)
            if delta > 0.001:
                mismatched_records.append(
                  "   %s* - Zerp Weight: %.03f, Web Weight: %.03f" % ( code, zerp_weight, web_weight )
                )

        if not spec_differences and not mismatched_records:
            return

        problem_writeup = ""
        if spec_differences:
            problem_writeup += "Found products on the web that have weights that do not match their spec listing:\n\n"
            problem_writeup += "\n".join(sorted(spec_differences))
            problem_writeup += "\n\n"

        if mismatched_records:
            problem_writeup += "Found products on the web whose weights that do not match Zerp:\n"
            problem_writeup += "Any Zerp partnumbers marked with an asterisk '*' have weights built from the sum of the base part and kit\n\n"
            problem_writeup += "\n".join(sorted(mismatched_records))

        # now that we've finished documenting the problem let's notify the 
        # appropriate users
        send_email = True
        close_case = False
        self.pool.get('crm.helpdesk').sanity_test_log(
                  cr, uid, context,
                  case_id,
                  problem_writeup,
                  send_email,
                  close_case
              )


    def product_prices_sync(self,cr,uid,context):
        case_id = self.pool.get('crm.helpdesk').sanity_test_create_stub_case(cr,uid,context,
        "Found Products that do not have the same price online and in Zerp",
        """ This is a stub to track incidents of products that have a price online that does not
            match the price listed in Zerp
        """,
        'product_prices_sync')


        web_products = self._web_product_lookup(cr,uid,context)
        res = self._product_web_compare(cr,uid,context,web_products)
        send_email = False
        close_case = False

        mismatched_records = []
        for code, rec in res['matched_records'].iteritems():
            web_price = float(web_products[code]['price'] or 0)
            delta = abs(rec.list_price - web_price)
            if delta > 0.001:
                send_email = True
                mismatched_records.append(
                  "   %s - Zerp Price: %.03f, Web Price: %.03f" % ( code, rec.list_price, web_price )
                )

        for code, rec in res['composite_records'].iteritems():
            web_price = float(web_products[code]['price'] or 0)
            zerp_price = 0
            for part in rec['parts']:
                zerp_price += part.list_price
            delta = abs(zerp_price - web_price)
            if delta > 0.001:
                send_email = True
                mismatched_records.append(
                  "   %s* - Zerp Price: %.03f, Web Price: %.03f" % ( code, zerp_price, web_price )
                )

        missing_records = res['missing_records']
        discontinued_but_sellable_records = res['discontinued_but_sellable_records']
        available_but_not_sellable_records = res['available_but_not_sellable_records']
        
        if not mismatched_records and not missing_records and not discontinued_but_sellable_records and not available_but_not_sellable_records:
            return

        problem_writeup = ""
        if missing_records:
            problem_writeup += "The following products are available on the website but not in Zerp:\n\n"
            problem_writeup += "\n".join(sorted(missing_records.keys()))
            problem_writeup += "\n\n"
                
        if discontinued_but_sellable_records:
            problem_writeup += "The following products are discontinued on the website but marked sellable in Zerp:\n\n"
            problem_writeup += "\n".join(sorted(discontinued_but_sellable_records.keys()))
            problem_writeup += "\n\n"
        
        if available_but_not_sellable_records:
            problem_writeup += "The following products are available on the website but not marked sellable in Zerp:\n\n"
            problem_writeup += "\n".join(sorted(available_but_not_sellable_records.keys()))
            problem_writeup += "\n\n"

        if mismatched_records:
            problem_writeup += "The following products found on the website and in Zerp but with different prices:\n\n"
            problem_writeup += "\n".join(mismatched_records)
            problem_writeup += "\n\n"
            problem_writeup += "Any Zerp prices marked with an asterisk '*' is a price built from the sum of the base part and kit"
        
        # If send_email is false, it means that there are no
        # urgent issues related to prices. Therefore, we'll 
        # treat this as an ignorable warning
        if not send_email:
            close_case = True

        # now that we've finished documenting the problem let's notify the 
        # appropriate users
        self.pool.get('crm.helpdesk').sanity_test_log(
                  cr, uid, context,
                  case_id,
                  problem_writeup,
                  send_email,
                  close_case
              )


    def product_weights(self, cr, uid, context):
        case_id = self.pool.get('crm.helpdesk').sanity_test_create_stub_case(cr,uid,context,
        "Found Products with Mismatched Gross and Net Weights",
        """ This is a stub to track incidents of products with that have a Gross Weight
            and a Net Weight that are different.
        """,
        'product_weights')

        cr.execute('''
          SELECT pp.id
          FROM  product_product as pp,
                product_template as pt
          WHERE pt.weight <> pt.weight_net
            AND pp.product_tmpl_id = pt.id
        ''')
        prod_ids = [row[0] for row in cr.fetchall()]
        if not prod_ids:
            return # horray, no weirdness

        problem_writeup = "The following Products have Mismatched Gross and Net Weights:\n\n"
        for prod in self.browse(cr,uid,prod_ids):
            problem_writeup += "%i:(%s) %s\n\n" % (prod.id,prod.default_code,prod.name)
        problem_writeup += "\n\n"

        # now that we've finished documenting the problem let's notify the 
        # appropriate users
        self.pool.get('crm.helpdesk').sanity_test_log(
                  cr, uid, context,
                  case_id,
                  problem_writeup
              )

        return

    def product_track_product_true(self, cr, uid, context):
        case_id = self.pool.get('crm.helpdesk').sanity_test_create_stub_case(cr,uid,context,
        "Found Products with Track Production marked true",
        """ This is a stub to track incidents of products with "track_product" 
            or Track Manufacturing Lots set to a true value
        """,
        'product_track_product_true')

        prod_ids = self.search(cr,uid,[
                          ('track_production','=','t'),
                          ])
        if not prod_ids:
            return # horray, no weirdness

        problem_writeup = "The following Products have Track Production marked true:\n\n"
        for prod in self.browse(cr,uid,prod_ids):
            problem_writeup += "%i:(%s) %s\n\n" % (prod.id,prod.default_code,prod.name)
        problem_writeup += "\n\n"

        # now that we've finished documenting the problem let's notify the 
        # appropriate users
        self.pool.get('crm.helpdesk').sanity_test_log(
                  cr, uid, context,
                  case_id,
                  problem_writeup
              )

        return

    def product_costing_method(self, cr, uid, context):
        case_id = self.pool.get('crm.helpdesk').sanity_test_create_stub_case(cr,uid,context,
        'Found products with costing method other than Average Price',
        """ This is a stub to track incidents of products with costing method
            set to a value other than "Average Price"
        """,
        'product_costing_method')

        prod_ids = self.search(cr,uid,[
                          ('cost_method','<>','average'),
                          ])
        if not prod_ids:
            return # horray, no weirdness

        problem_writeup = "The following Products have their Costing Method different than Average Price:\n\n"
        for prod in self.browse(cr,uid,prod_ids):
            problem_writeup += "%i:(%s) %s: %s\n\n" % (prod.id,prod.default_code,prod.name,prod.cost_method)
        problem_writeup += "\n\n"

        # now that we've finished documenting the problem let's notify the 
        # appropriate users
        self.pool.get('crm.helpdesk').sanity_test_log(
                  cr, uid, context,
                  case_id,
                  problem_writeup
              )

        return


    def product_track_incoming_true(self, cr, uid, context):
        case_id = self.pool.get('crm.helpdesk').sanity_test_create_stub_case(cr,uid,context,
        'Found Products with Track Incoming marked true',
        """ This is a stub to track incidents of products with "track_incoming" 
            set to a true value
        """,
        'product_track_incoming_true')

        prod_ids = self.search(cr,uid,[
                          ('track_incoming','=','t'),
                          ])
        if not prod_ids:
            return # horray, no weirdness

        problem_writeup = "The following Products have Track Incoming marked true:\n\n"
        for prod in self.browse(cr,uid,prod_ids):
            problem_writeup += "%i:(%s) %s\n\n" % (prod.id,prod.default_code,prod.name)
        problem_writeup += "\n\n"

        # now that we've finished documenting the problem let's notify the 
        # appropriate users
        self.pool.get('crm.helpdesk').sanity_test_log(
                  cr, uid, context,
                  case_id,
                  problem_writeup
              )

        return

    def product_track_outgoing_true(self, cr, uid, context):

        case_id = self.pool.get('crm.helpdesk').sanity_test_create_stub_case(cr,uid,context,
        'Found Products with Track Outgoing marked true',
        """ This is a stub to track incidents of products with "track_incoming" 
            set to a true value
        """,
        'product_track_outgoing_true')


        prod_ids = self.search(cr,uid,[
                          ('track_outgoing','=','t'),
                          ])
        if not prod_ids:
            return # horray, no weirdness

        problem_writeup = "The following Products have Track Outgoing marked true:\n\n"
        for prod in self.browse(cr,uid,prod_ids):
            problem_writeup += "%i:(%s) %s\n\n" % (prod.id,prod.default_code,prod.name)
        problem_writeup += "\n\n"

        # now that we've finished documenting the problem let's notify the 
        # appropriate users
        self.pool.get('crm.helpdesk').sanity_test_log(
                  cr, uid, context,
                  case_id,
                  problem_writeup
              )

        return


    def product_orderpoint(self, cr, uid, context):
        case_id = self.pool.get('crm.helpdesk').sanity_test_create_stub_case(cr,uid,context,
        "Found Products without a Minimum Stock Rule",
        """ This is a stub to track incidents of products with that are stockable yet do
            not have any minimum order points defined.
        """,
        'product_orderpoint')

        cr.execute('''
                      select 
                        pp.id,
                        pt.name,
                        pp.default_code
                      from 
                        product_product pp left join
                        product_template pt on pp.product_tmpl_id = pt.id left join 
                        stock_warehouse_orderpoint swo on pp.id = swo.product_id 
                      where 
                        swo.id is null 
                        and pp.active
                        and pt.type = 'product';
        ''')
        prod_ids = [row[0] for row in cr.fetchall()]
        if not prod_ids:
            return # horray, no weirdness

        problem_writeup = "The following products do not have Minimum Stock Rule defined:\n\n"
        for prod in self.browse(cr,uid,prod_ids):
            problem_writeup += "%i:(%s) %s\n\n" % (prod.id,prod.default_code,prod.name)
        problem_writeup += "\n\n"

        # now that we've finished documenting the problem let's notify the 
        # appropriate users
        self.pool.get('crm.helpdesk').sanity_test_log(
                  cr, uid, context,
                  case_id,
                  problem_writeup
              )

        return

    def product_multiple_revisions(self,cr,uid,context):
        case_id = self.pool.get('crm.helpdesk').sanity_test_create_stub_case(
            cr,
            uid,
            context,
            "Found Products with multiple revisions in same state",
            """ This is a stub to track incidents.
            """,
            'product_multiple_revisions')

        cr.execute('''
            select  prod.default_code,
                    tmpl.state
            from    product_product prod
            join    product_template tmpl
            on      prod.product_tmpl_id = tmpl.id
            where   tmpl.state in ('sellable', 'end')
            and     prod.default_code is not null
            and     prod.active
            group
            by      prod.default_code, tmpl.state
            having  count(*) > 1
            order
            by      tmpl.state, prod.default_code
            ;''')
        active_codes = []
        end_codes = []
        for row in cr.fetchall():
            if row[1] == 'end':
                end_codes.append(row[0])
            else:
                active_codes.append(row[0])

        problem_writeup = ''
        
        if active_codes:
            problem_writeup += (
                "The following products have multiple active revisions:\n\n" +
                '\n'.join(active_codes) + 
                '\n\n')
        
        if end_codes:
            problem_writeup += (
                "The following products have multiple end-of-lifecycle " +
                "revisions:\n\n" +
                '\n'.join(end_codes) + 
                '\n\n')

        if problem_writeup:
            # now that we've finished documenting the problem let's notify the 
            # appropriate users
            self.pool.get('crm.helpdesk').sanity_test_log(
                cr, 
                uid, 
                context,
                case_id,
                problem_writeup)

    def product_bom(self, cr, uid, context):
        case_id = self.pool.get('crm.helpdesk').sanity_test_create_stub_case(
            cr,
            uid,
            context,
            "Found products without bills of materials",
            "This is a stub to track incidents of products that are " +
                "produced, yet have no bill of materials defined.",
            'product_bom')

        cr.execute('''\
SELECT
    prod.default_code,
    tmpl.name,
    prod.create_date,
    cusr.name create_name,
    prod.write_date,
    wusr.name write_name
FROM
    product_product prod
JOIN
    product_template tmpl
ON
    tmpl.id = prod.product_tmpl_id
JOIN
    res_users cusr
ON
    cusr.id = prod.create_uid
LEFT JOIN
    res_users wusr
ON
    wusr.id = prod.write_uid
WHERE
    tmpl.supply_method = 'produce'
AND tmpl.type = 'product'
AND prod.active
AND NOT EXISTS
    (
        SELECT
            1
        FROM
            mrp_bom bom
        WHERE
            bom.product_id = prod.id )
''')
        products = cr.fetchall()
        if not products:
            return # hurrah, no weirdness

        problem_writeup = ("The following products do not have bills of " +
                           "materials:\n\n")
        template = "[%s] %s created on %s by %s, modified on %s by %s.\n"
        for product in products:
            problem_writeup += template % product
        problem_writeup += "\n\n"

        # now that we've finished documenting the problem let's notify the 
        # appropriate users
        self.pool.get('crm.helpdesk').sanity_test_log(cr, 
                                                      uid, 
                                                      context,
                                                      case_id,
                                                      problem_writeup)

product_product()

class product_supplierinfo(osv.osv):
    _inherit = 'product.supplierinfo'

    def sanity_check(self, cr, uid, context=None):
        self.sanity_check_fast(cr, uid, context)
        return 0

    def sanity_check_fast(self, cr, uid, context):
        ''' A set of fast sanity checks
        '''
        self.product_double_delay(cr, uid, context)

    def product_double_delay(self,cr,uid,context):
        case_id = self.pool.get('crm.helpdesk').sanity_test_create_stub_case(cr,uid,context,
        "Found Products with mfg delay and supplier delay",
        """ This is a stub to track incidents where products have both a
        manufacturing delay and a supplier delay. They should have one or the
        other, but not both.
        """,
        'product_double_delay')

        
        supplierinfo_ids = self.search(cr, uid, [('delay', '>', 0.001)])
        product_codes = []
        for supplierinfo in self.browse(cr, uid, supplierinfo_ids):
            if supplierinfo.product_id.produce_delay > 0.001:
                # The product template has the problem, but we want to report
                # the product code from the product_product table.
                for product_product in supplierinfo.product_id.variant_ids:
                    product_codes.append(product_product.default_code)
        if not product_codes:
            return # hurrah, no weirdness

        problem_writeup = (
            "The following products have both a manufacturing delay and " +
            "a supplier delay.:\n\n" + 
            ', '.join(product_codes) +
            "\n\n")

        # now that we've finished documenting the problem let's notify the 
        # appropriate users
        self.pool.get('crm.helpdesk').sanity_test_log(
            cr, 
            uid, 
            context,
            case_id,
            problem_writeup)

product_supplierinfo()