def full_delete(enterprise_id): from pvscore.model.crm.customer import Customer company_ids = db.get_list("select company_id from crm_company where enterprise_id = '%s'" % enterprise_id) campaign_ids = db.get_list("""select campaign_id from crm_campaign where company_id in (select company_id from crm_company where enterprise_id = '%s')""" % enterprise_id) customer_ids = db.get_list("""select customer_id from crm_customer where campaign_id in (select campaign_id from crm_campaign where company_id in (select company_id from crm_company where enterprise_id = '%s'))""" % enterprise_id) product_ids = db.get_list("""select product_id from crm_product where company_id in (select company_id from crm_company where enterprise_id = '%s')""" % enterprise_id) # KB: [2013-01-15]: pragma no cover on this because it is not possible to create customers or products on one campaign from another. for cid in customer_ids: #pragma: no cover Customer.full_delete(cid[0]) for pid in product_ids: #pragma: no cover product_id = pid[0] Session.execute("delete from crm_product_return where product_id = '%s'" % product_id) Session.execute("delete from crm_product_category_join where product_id = '%s'" % product_id) Session.execute("delete from crm_product_child where parent_id = '%s'" % product_id) Session.execute("delete from crm_product_child where child_id = '%s'" % product_id) Session.execute("delete from crm_product_pricing where product_id = '%s'" % product_id) Session.execute("delete from crm_product_inventory_journal where product_id = '%s'" % product_id) Session.execute("delete from crm_purchase_order_item where product_id = '%s'" % product_id) Session.execute("delete from crm_order_item where product_id = '%s'" % product_id) Session.execute("delete from crm_product where product_id = '%s'" % product_id) for cid in campaign_ids: campaign_id = cid[0] Session.execute("delete from crm_product_pricing where campaign_id = '%s'" % campaign_id) for cid in company_ids: company_id = cid[0] Session.execute("delete from crm_product_category where company_id = '%s'" % company_id) Session.execute("delete from crm_report where company_id = '%s'" % company_id) Session.execute("""delete from cms_content where site_id in (select site_id from cms_site where company_id = '%s')""" % company_id) Session.execute("delete from cms_page where site_id in (select site_id from cms_site where company_id = '%s')" % company_id) Session.execute("delete from cms_site where company_id = '%s'" % company_id) Session.execute("update crm_company set default_campaign_id = null where company_id = '%s'" % company_id) Session.execute("delete from crm_campaign where company_id = '%s'" % company_id) Session.execute("delete from crm_purchase_order where company_id = '%s'" % company_id) Session.execute("delete from core_asset where enterprise_id = '%s'" % enterprise_id) Session.execute("delete from crm_communication where enterprise_id = '%s'" % enterprise_id) Session.execute("delete from core_status where event_id in (select event_id from core_status_event where enterprise_id = '%s')" % enterprise_id) Session.execute("delete from core_status_event_reason where event_id in (select event_id from core_status_event where enterprise_id = '%s')" % enterprise_id) Session.execute("delete from core_status_event where enterprise_id = '%s'" % enterprise_id) Session.execute("delete from cms_template where enterprise_id = '%s'" % enterprise_id) Session.execute("delete from crm_company where enterprise_id = '%s'" % enterprise_id) #Session.execute('update core_user set enterprise_id = null where enterprise_id = '%s'" % enterprise_id) Session.execute("delete from core_status where username in (select user_id from core_user where enterprise_id = '%s')" % enterprise_id) Session.execute("delete from crm_customer where user_created in (select user_id from core_user where enterprise_id = '%s')" % enterprise_id) Session.execute("delete from crm_customer where user_assigned in (select user_id from core_user where enterprise_id = '%s')" % enterprise_id) Session.execute("delete from core_user where enterprise_id = '%s'" % enterprise_id) Session.execute("delete from crm_discount where enterprise_id = '%s'" % enterprise_id) Session.execute("delete from crm_vendor where enterprise_id = '%s'" % enterprise_id) Session.execute("delete from crm_enterprise where enterprise_id = '%s'" % enterprise_id)
def results(self): report_id = self.request.matchdict.get('report_id') rep = Report.load(report_id) page = self.request.GET.get('page', 1) limit = self.request.GET.get('rows', 100) # get how many rows we want to have into the grid sidx = self.request.GET.get('sidx', None) # get index row - i.e. user click to sort sord = self.request.GET.get('sord', 'asc') # get the direction rpt_start_dt = self.request.GET.get('rpt_start_dt') if self.request.GET.get('rpt_start_dt') else util.str_today() rpt_end_dt = self.request.GET.get('rpt_end_dt') if self.request.GET.get('rpt_end_dt') else util.str_today() rpt_campaign_id = self.request.GET.get('rpt_campaign_id') if 'rpt_campaign_id' in self.request.GET else '' rpt_company_id = self.request.GET.get('rpt_company_id') if 'rpt_company_id' in self.request.GET else '' rpt_user_id = self.request.GET.get('rpt_user_id') if 'rpt_user_id' in self.request.GET else '' rpt_product_id = self.request.GET.get('rpt_product_id') if 'rpt_product_id' in self.request.GET else '' rpt_vendor_id = self.request.GET.get('rpt_vendor_id') if 'rpt_vendor_id' in self.request.GET else '' rpt_p0 = self.request.GET.get('rpt_p0') if 'rpt_p0' in self.request.GET else '' rpt_p1 = self.request.GET.get('rpt_p1') if 'rpt_p1' in self.request.GET else '' rpt_p2 = self.request.GET.get('rpt_p2') if 'rpt_p2' in self.request.GET else '' sql = rep.sql.format(enterprise_id=self.enterprise_id, vendor_id=self.request.ctx.user.vendor_id, rpt_start_dt=rpt_start_dt, rpt_end_dt=rpt_end_dt, rpt_campaign_id=rpt_campaign_id, rpt_company_id=rpt_company_id, rpt_user_id=rpt_user_id, rpt_product_id=rpt_product_id, rpt_vendor_id=rpt_vendor_id, rpt_p0=rpt_p0, rpt_p1=rpt_p1, rpt_p2=rpt_p2) count = db.get_value('select count(0) from (%s) x' % sql) total_pages = 0 if count > 0: total_pages = math.ceil(int(count)/int(limit)) total_pages = total_pages if total_pages > 1 else 1 page = min(page, total_pages) start = max(int(limit)*int(page)-int(limit), 0) # // do not put $limit*($page - 1) limit = util.nvl(limit, 'all') results = db.get_list(sql + (' ORDER BY %s %s ' % (sidx, sord) if sidx else '') + ' LIMIT {limit} offset {start}'.format(limit=limit, start=start)) response = { 'page': page, 'total': int(total_pages), 'records': int(count)} rows = [] for res_row in results: rows.append({'id': str(res_row[0]), 'cell': list([unicode(util.nvl(i)) for i in res_row])}) response['rows'] = rows return json.dumps(response)
def results_export(self): report_id = self.request.matchdict.get('report_id') rep = Report.load(report_id) enterprise_id = self.enterprise_id sidx = self.request.GET.get('sidx') # get index row - i.e. user click to sort sord = self.request.GET.get('sord') # get the direction rpt_start_dt = self.request.GET.get('rpt_start_dt') if self.request.GET.get('rpt_start_dt') else util.str_today() rpt_end_dt = self.request.GET.get('rpt_end_dt') if self.request.GET.get('rpt_end_dt') else util.str_today() rpt_campaign_id = self.request.GET.get('rpt_campaign_id') if 'rpt_campaign_id' in self.request.GET else '' rpt_company_id = self.request.GET.get('rpt_company_id') if 'rpt_company_id' in self.request.GET else '' rpt_user_id = self.request.GET.get('rpt_user_id') if 'rpt_user_id' in self.request.GET else '' rpt_product_id = self.request.GET.get('rpt_product_id') if 'rpt_product_id' in self.request.GET else '' rpt_vendor_id = self.request.GET.get('rpt_vendor_id') if 'rpt_vendor_id' in self.request.GET else '' rpt_p0 = self.request.GET.get('rpt_p0') if 'rpt_p0' in self.request.GET else '' rpt_p1 = self.request.GET.get('rpt_p1') if 'rpt_p1' in self.request.GET else '' rpt_p2 = self.request.GET.get('rpt_p2') if 'rpt_p2' in self.request.GET else '' sql = rep.sql.format(enterprise_id=enterprise_id, vendor_id=self.request.ctx.user.vendor_id, rpt_start_dt=rpt_start_dt, rpt_end_dt=rpt_end_dt, rpt_campaign_id=rpt_campaign_id, rpt_company_id=rpt_company_id, rpt_user_id=rpt_user_id, rpt_product_id=rpt_product_id, rpt_vendor_id=rpt_vendor_id, rpt_p0=rpt_p0, rpt_p1=rpt_p1, rpt_p2=rpt_p2) results = db.get_list(sql + (' ORDER BY %s %s ' % (sidx, sord) if sidx else '')) self.request.response.content_type = 'application/vnd.ms-excel' self.request.response.headers['Content-Disposition'] = 'attachment; filename="report.xls"' columns = [] if rep.column_names: jstr = '[{"columns" : %s}]' % rep.column_names.replace("'", '"') columns = json.loads(jstr) columns = columns[0]['columns'] return { 'rows' : results, 'columns' : columns }
def find_last_n_assets(last_n): return db.get_list("""select a.id from core_asset a where a.fk_type = 'Listing' and a.fk_id is not null and create_dt > (current_date - %s) order by create_dt desc """ % last_n)