def getData(self, session, facts, dimensions, filters=[]): """General purpose summary data generator. """ for n, f in enumerate(filters): dc = odict(DIMENSIONS) if isinstance(f, (str, unicode)): filters[n] = eval(f) fact_aliases = facts[:] facts = [] for f in fact_aliases: if f not in FACTS: raise ValueError('Unknown fact: %s' % f) facts.append(FACTS[f]) dim_aliases = dimensions[:] dimensions = [] for d in dim_aliases: if d not in DIMENSIONS: raise ValueError('Unknown dimension: %s' % d) dimensions.append(DIMENSIONS[d]) cols = dimensions + facts conds = [] self._gatherConds(cols + filters, conds) q = session.query(*cols).filter(and_(*(conds + filters))) if facts: q = q.group_by(dimensions) #print q#;return [] return q.all()
def getData(self, session, order_item_id=None, batch_id=None, product_item_id=None): entity_name, entity_id = self._getEntityId(order_item_id, batch_id, product_item_id) if entity_name == 'order_item_id': history = self.histories.getHistory(order_item_id) #from reworks import Reworks #rework_data = Reworks().getReworks(order_item_id) elif entity_name == 'batch_id': history = self.histories.getBatchHistory(batch_id) else: # product_item history = self.histories.getProductHistory(product_item_id) history = [odict(todict(h)) for h in history] for s in history: if not isinstance(s.history_date, datetime): s.history_date = datetime(*s.history_date.tuple()[:6]) for attr in ('code', 'name', 'description'): func = getattr(self.activities, 'get%s' % attr.title()) try: setattr(s, 'activity_' + attr, func(s.activity_id)) except: setattr(s, 'activity_' + attr, '') if s.entity_type == 'order_item': s.entity_name = 'Order' elif s.entity_type == 'batch': s.entity_name = 'Batch' else: s.entity_name = 'Product' s.entity_type += '_id' """ # show rework reason in comment if s.activity_id == 'REWORK': for r in rework_data: if not isinstance(r['date'], datetime): r['date'] = datetime(*r['date'].tuple()[:6]) if s.history_date == r['date']: if r['rework_reason']: s.comments += ' %s' % r['rework_reason'] """ s.comments = (s.comments or '').replace('*', '').strip() if s.entity_type == 'batch_id' and s.activity_code == 'entry': s.comments += ''' <a href="batchdetails.py?batch_id=%s">Batch %s Details</a> ''' % (s.batch_id, s.batch_id) s.username = s.user.username or '' # change to string, add bolding s.history_date = '<strong>%s</strong> %s' % (s.history_date.date(), s.history_date.time()) # make 'order entry' and 'start over' activities in bold: s.bold = (s.activity_code == 'entry' and s.entity_type == 'order_item_id' or s.activity_code in ('resend', 'rework', 'aging')) return history
def getData(self, session, params): '''Get the order summary data based on the given parameters.''' from web.htmlutil import Encryption encrypt = Encryption().simple_encrypt cube = DataCube() data_tables = [] filters = params.filters facts = params.get('facts', ['orders'] * len(params.dims)) # extras are additional facts that will display in columns to # the right of the group by columns extra = params.get('extra_cols', []) # ratio data <A>_per_<B> computed from A and B, such that # division of B occurs only after all A's are summed up extra_facts = [e for e in extra if '_per_' not in e] for i, dims in enumerate(params.dims): fact = facts[i] percent_dims = [d.endswith('_percent') for d in dims[1:]] percent_cols = [] dims = [d.replace('_percent', '') for d in dims] # main dimension used to title the table row_dim = dims[0] dim_name = ' '.join(s.title() for s in row_dim.split('_')) data = odict({'title': fact.title(), 'row_name': dim_name, 'cols': [], 'rows': [], 'body': [], 'dims': dims}) if any(percent_dims): data.title = '%s Percentages' % data.title.rstrip('s') filter_str = '' if filters: filter_str = ';'.join(filters) + ';' for d in sorted(cube.getData(session, [fact] + extra_facts, dims, filters[:])): #print d, '<br/>' row_val = getattr(d, row_dim) if row_val not in data.rows: if not row_val: continue data.rows.append(row_val) data.body.append({row_dim: row_val, 'Total': 0, 'detail_params': {}}) row_filter_str = filter_str + "dc.%s == '%s'" % \ (dims[0], row_val) for g, col_dim in enumerate(dims[1:]): key = getattr(d, col_dim) # grouping is to ensure proper column ordering if len(data.cols) < g + 1: data.cols.append([]) percent_cols.append([]) if key not in data.cols[g]: data.cols[g].append(key) percent_cols[g].append(percent_dims[g]) if key not in data.body[-1]: data.body[-1][key] = 0 val = getattr(d, fact) data.body[-1][key] += val data.body[-1]['Total'] += val p = row_filter_str + ";dc.%s == '%s'" % (dims[1], key) data.body[-1]['detail_params'][key] = encrypt(p) for e in extra: if e not in data.body[-1]: data.body[-1][e] = 0 if '_per_' in e: # just numerator part of ratio data num = e.split('_per_')[0] data.body[-1][e] += getattr(d, num) else: data.body[-1][e] += getattr(d, e) data.body[-1]['detail_params']['Total'] = \ encrypt(row_filter_str) data.cols = [c for g in data.cols for c in g] # flatten cols percent_cols = [c for g in percent_cols for c in g] # flatten cols # only show total column if there are more than 1 col to sum if len(data.cols) > 1: data.cols.append('Total') percent_cols.append(percent_cols[-1]) data.cols += extra percent_cols += [False] * len(extra) data.total_row = {'detail_params': {}} # add 0 for missing values, populate total row and detail filters for col in data.cols: for row in data.body: if col not in row: row[col] = 0 data.total_row[col] = sum(row[col] for row in data.body) if col not in extra: p = filter_str + "dc.%s == '%s'" % (dims[1], col) data.total_row['detail_params'][col] = encrypt(p) # apply special formatting, # divide denominator part of ratio data and percents data.col_names = [] for i, col in enumerate(data.cols): for row in data.body + [data.total_row]: if '_per_' in col: div = col.split('_per_')[1] + 's' if div == fact: row[col] /= Decimal(row.get('Total', 1) or 1) else: row[col] /= Decimal(row[div] or 1) elif percent_cols[i]: if col == 'Total': div = Decimal(data.total_row['Total']) else: div = Decimal(row['Total']) row[col] *= 100 / (div or 1) if col.split('_per_')[0] in MONEY_FIELDS: row[col] = formatDollars(row[col]) elif percent_cols[i] and row[col]: row[col] = '%0.1f%%' % row[col] elif isinstance(row[col], (float, Decimal)) and \ row[col] <> int(row[col]): row[col] = '%0.4f' % row[col] data.col_names.append(' '.join(s.replace('usd', '$').title() for s in col.split('_'))) data_tables.append(data) #for b in data_tables[0].body: # print [(k, v) for k,v in b.items() if k <> 'detail_params'], '<br/>' return data_tables
def getData(self, session, rpt, filters): '''Get the order details data based on the given parameters.''' e = Encryption() filters = e.simple_decrypt(filters).split(';') fields = self.getFields(rpt) cube_fields = [c for c in fields if c not in SPECIAL_FIELDS] data = [] for dc in DataCube().getData(session, [], cube_fields, filters): d = odict(zip(cube_fields, dc)) if self.predefined[rpt].get('barcoded'): d.barcoded = True rework = 'rework_date' in fields if 'print_date' in fields or 'batch_id' in fields: hist_batch_id = session.query(BatchItem.batch_id)\ .filter((BatchItem.order_item_id == d.foid) & (BatchItem.active == 1))\ .order_by(desc(BatchItem.created))\ .scalar() if 'print_date' in fields: if hist_batch_id: dt = session.query(BatchHistory.history_date)\ .filter(BatchHistory.batch_id == hist_batch_id)\ .order_by(BatchHistory.history_date)\ .scalar() else: dt = session.query(OrderItemHistory.history_date)\ .filter(OrderItemHistory.order_item_id == d.foid)\ .order_by(OrderItemHistory.history_date)\ .scalar() d.print_date = dt if 'batch_id' in fields: d.batch_id = hist_batch_id if ('press' in fields or 'operator' in fields) and rework: d.press = '' d.operator = 'Unknown' bh = session.query(BatchHistory)\ .join((BatchItem, (BatchItem.batch_id == BatchHistory.batch_id)))\ .filter((BatchItem.order_item_id == d.foid) & ((BatchItem.removed_date == None) | (BatchItem.removed_date > d.rework_date)) & (BatchHistory.history_date < d.rework_date) & (BatchHistory.activity_id.in_([16, 17, 20, 24])))\ .order_by(desc(BatchHistory.history_date))\ .first() if bh: d.press = bh.comments.split(' ')[-1] d.operator = bh.user.username else: oh = session.query(OrderItemHistory)\ .filter((OrderItemHistory.order_item_id == d.foid) & (OrderItemHistory.history_date < d.rework_date) & (OrderItemHistory.activity_id.in_( [16, 17, 20, 24])))\ .order_by(desc(OrderItemHistory.history_date))\ .first() if oh: d.press = oh.comments.split(' ')[-1] d.operator = oh.user.username if 'pdf' in fields: import os.path d.pdfname = os.path.basename(d.pdf) d.dime = d.pdf.replace('hires/', 'share/dime_backup/').replace('.pdf', '.dime') d.log = "http://wcom.mypublisher.com/merc/cutomertransform" \ "details.asp?filename=%s" % os.path.basename(d.dime) data.append(d) return data