def rows(self): rows = [] if self.config['location_id']: supply_points = get_supply_points(self.config['location_id'], self.config['domain']).values_list( 'supply_point_id', flat=True ) last_period_st, last_period_end = calculate_last_period(self.config['enddate']) reported = StockTransaction.objects.filter(case_id__in=supply_points, report__date__range=[last_period_st, last_period_end] ).values_list('case_id', flat=True) not_reported = SQLLocation.objects.filter(location_type__in=self.location_types, parent__location_id=self.config['location_id'])\ .exclude(supply_point_id__in=reported) for loc in not_reported: url = make_url( StockLevelsReport, self.config['domain'], '?location_id=%s&startdate=%s&enddate=%s', (loc.location_id, self.config['startdate'], self.config['enddate'])) st = StockTransaction.objects.filter(case_id=loc.supply_point_id).order_by('-report__date') if st: date = st[0].report.date else: date = _('---') rows.append([link_format(loc.name, url), date]) return rows
def data(self): locations = self.report_location.get_descendants() locations_ids = locations.values_list('supply_point_id', flat=True) if not locations_ids: return {} unique_products = self.unique_products(locations) transactions = self.get_stock_transactions_for_supply_points_and_products( locations_ids, unique_products ).values_list('case_id', 'product_id', 'report__date', 'stock_on_hand') current_mos_locations = get_supply_points(self.report_config['domain'], self.report_config['location_id']) current_mos_locations_ids = set( current_mos_locations.values_list('supply_point_id', flat=True) ) stock_states = StockState.objects.filter( sql_product__domain=self.domain, case_id__in=current_mos_locations_ids ) product_case_with_stock = defaultdict(set) product_case_without_stock = defaultdict(set) months_of_stock = defaultdict(lambda: defaultdict(dict)) stock_state_map = { (stock_state.case_id, stock_state.product_id): stock_state.get_monthly_consumption() if stock_state.daily_consumption else None for stock_state in stock_states } stockouts = defaultdict(set) for (case_id, product_id, date, stock_on_hand) in transactions: if stock_on_hand > 0: product_case_with_stock[product_id].add(case_id) if case_id in current_mos_locations_ids: stock_state_dict = stock_state_map.get((case_id, product_id)) if stock_state_dict: months_of_stock[case_id][product_id] = stock_on_hand / stock_state_dict else: months_of_stock[case_id][product_id] = None else: product_case_without_stock[product_id].add(case_id) if case_id in current_mos_locations_ids: stockouts[case_id].add(product_id) months_of_stock[case_id][product_id] = 0 return { 'without_stock': { product_id: len(case_list) for product_id, case_list in product_case_without_stock.iteritems() }, 'with_stock': { product_id: len(case_list) for product_id, case_list in product_case_with_stock.iteritems() }, 'all': locations.count(), 'months_of_stock': months_of_stock, 'stockouts': stockouts, 'unique_products': unique_products, 'stockout_table_supply_points': current_mos_locations }
def rows(self): rows = [] unique_products = self.unique_products( get_supply_points(self.config['domain'], self.config['location_id']), all=(not self.config['export']) ) if self.config['location_id']: for case_id, products in self.config['months_of_stock'].iteritems(): sp = SQLLocation.objects.get(supply_point_id=case_id) url = make_url( StockStatus, self.config['domain'], '?location_id=%s&filter_by_program=%s&startdate=%s&enddate=%s&report_type=%s', (sp.location_id, self.config['program'] or ALL_OPTION, self.config['startdate'].date(), self.config['enddate'].date(), self.config['report_type']) ) row = [ link_format(sp.name, url) if not self.config.get('is_rendered_as_email', False) else sp.name ] for p in unique_products: product_data = products.get(p.product_id) if product_data: value = '%.1f' % product_data else: value = '-' row.append(value) rows.append(row) return rows
def rows(self): rows = {} if self.config['location_id']: supply_points = get_supply_points(self.config['location_id'], self.config['domain']) products = self.unique_products(supply_points, all=True) code_name_map = {} for product in products: rows[product.code] = [] code_name_map[product.code] = product.name enddate = self.config['enddate'] startdate = self.config['startdate'] if 'custom_date' in self.config else enddate - timedelta(days=90) for d in get_second_week(startdate, enddate): txs = list(StockTransaction.objects.filter( case_id__in=supply_points.values_list('supply_point_id', flat=True), sql_product__in=products, report__date__range=[d['start_date'], d['end_date']], type='stockonhand', stock_on_hand=0 ).values('sql_product__code').annotate(count=Count('case_id'))) for product in products: if not any([product.code == tx['sql_product__code'] for tx in txs]): rows[product.code].append({'x': d['start_date'], 'y': 0}) for tx in txs: rows[tx['sql_product__code']].append( { 'x': d['start_date'], 'y': tx['count'], 'name': code_name_map[tx['sql_product__code']] } ) return rows
def rows(self): rows = {} if self.config['location_id']: last_period_st, last_period_end = calculate_last_period(self.config['enddate']) supply_points = get_supply_points(self.config['location_id'], self.config['domain']).values_list( 'supply_point_id', flat=True ) complete = 0 incomplete = 0 for sp in supply_points: products_count = len(SQLLocation.objects.get(supply_point_id=sp).products) st = StockTransaction.objects.filter(case_id=sp, report__date__range=[last_period_st, last_period_end] ).distinct('product_id').count() if products_count == st: complete += 1 else: incomplete += 1 rows = dict( total=complete + incomplete, complete=complete, incomplete=incomplete ) return rows
def rows(self): locations = get_supply_points(self.config['location_id'], self.config['domain']) products = self.unique_products(locations) result = [['<input value=\"{0}\" type=\"checkbox\">{1} ({0})</input>'.format(p.code, p.name)] for p in products] result.append(['<button id=\"selection_pane_apply\" class=\"filters btn\">Apply</button>']) return result
def headers(self): headers = DataTablesHeader(DataTablesColumn('Location')) for product in self.unique_products( get_supply_points(self.config['domain'], self.config['location_id']), all=(not self.config['export']) ): if not self.config['export']: headers.add_column(DataTablesColumn(product.code)) else: headers.add_column(DataTablesColumn(u'{} ({})'.format(product.name, product.code))) return headers
def rows(self): rows = {} if self.config['location_id']: supply_points = get_supply_points(self.config['location_id'], self.config['domain']).values_list( 'supply_point_id', flat=True ) last_period_st, last_period_end = calculate_last_period(self.config['enddate']) reports = StockTransaction.objects.filter(case_id__in=supply_points, report__date__range=[last_period_st, last_period_end] ).distinct('case_id').count() rows = dict( total=len(supply_points), reported=reports, non_reported=len(supply_points) - reports ) return rows
def rows(self): rows = {} if self.config["location_id"]: supply_points = get_supply_points(self.config["location_id"], self.config["domain"]) products = self.unique_products(supply_points) for product in products: rows[product.code] = [] for d in get_second_week(self.config["startdate"], self.config["enddate"]): for product in products: st = StockTransaction.objects.filter( case_id__in=supply_points.values_list("supply_point_id", flat=True), sql_product=product, report__date__range=[d["start_date"], d["end_date"]], type="stockonhand", stock_on_hand=0, ).count() rows[product.code].append({"x": d["start_date"], "y": st}) return rows
def stockouts_data(self): supply_points = get_supply_points(self.report_config['domain'], self.report_config['location_id']) if not supply_points: return {} unique_products = self.unique_products(supply_points) transactions = self.get_stockouts_for_supply_points_and_products( supply_points, unique_products ).values_list('case_id', 'product_id') stockouts = defaultdict(set) for (case_id, product_id) in transactions: stockouts[case_id].add(product_id) return { 'stockouts': stockouts, 'unique_products': unique_products, 'stockout_table_supply_points': supply_points }
def rows(self): rows = {} if self.config['location_id']: supply_points = get_supply_points(self.config['location_id'], self.config['domain']) products = self.unique_products(supply_points, all=True) for product in products: rows[product.code] = [] enddate = self.config['enddate'] startdate = self.config['startdate'] if 'custom_date' in self.config else enddate - timedelta(days=90) for d in get_second_week(startdate, enddate): for product in products: st = StockTransaction.objects.filter( case_id__in=supply_points.values_list('supply_point_id', flat=True), sql_product=product, report__date__range=[d['start_date'], d['end_date']], type='stockonhand', stock_on_hand=0).count() rows[product.code].append({'x': d['start_date'], 'y': st}) return rows
def rows(self): rows = [] if self.config['location_id']: locations = get_supply_points(self.config['location_id'], self.config['domain']) for p in self.unique_products(locations, all=True): supply_points = locations.values_list('supply_point_id', flat=True) if supply_points: stocks = StockTransaction.objects.filter( type='stockonhand', product_id=p.product_id, case_id__in=supply_points, report__date__lte=self.config['enddate'], report__date__gte=self.config['startdate'] ).order_by('case_id', '-report__date').distinct('case_id') total = supply_points.count() with_stock = stocks.filter(stock_on_hand__gt=0).count() without_stock = stocks.filter(stock_on_hand=0).count() without_data = total - with_stock - without_stock rows.append({"product_code": p.code, "product_name": p.name, "total": total, "with_stock": with_stock, "without_stock": without_stock, "without_data": without_data}) return rows
def rows(self): rows = [] if self.config["location_id"]: locations = get_supply_points(self.config["location_id"], self.config["domain"]) for p in self.unique_products(locations): supply_points = locations.values_list("supply_point_id", flat=True) if supply_points: stocks = StockState.objects.filter(sql_product=p, case_id__in=supply_points) total = supply_points.count() with_stock = stocks.filter(stock_on_hand__gt=0).count() without_stock = stocks.filter(stock_on_hand=0).count() without_data = total - with_stock - without_stock rows.append( { "product_code": p.code, "total": total, "with_stock": with_stock, "without_stock": without_stock, "without_data": without_data, } ) return rows
def rows(self): rows = [] if self.config['location_id']: last_period_st, last_period_end = calculate_last_period(self.config['enddate']) for loc in self.get_locations: supply_points = get_supply_points(loc.location_id, loc.domain).values_list('supply_point_id', flat=True) sites = len(supply_points) reported = StockTransaction.objects.filter(case_id__in=supply_points, report__date__range=[last_period_st, last_period_end] ).distinct('case_id').count() reporting_rates = '%.2f%%' % (reported * 100 / (float(sites) or 1.0)) url = make_url( ReportingRatesReport, self.config['domain'], '?location_id=%s&startdate=%s&enddate=%s', (loc.location_id, self.config['startdate'], self.config['enddate'])) rows.append([link_format(loc.name, url), sites, reported, reporting_rates]) return rows
def rows(self): rows = [] if self.config['location_id']: locations = get_supply_points(self.config['location_id'], self.config['domain']) supply_points = locations.values_list('supply_point_id', flat=True) if not supply_points: return rows total = supply_points.count() unique_products = self.unique_products(locations, all=True).order_by('code') result_dict = {} for product in unique_products: result_dict[product.product_id] = [0, 0] last_stocks_in_period = StockTransaction.objects.filter( type='stockonhand', case_id__in=supply_points, report__date__lte=self.config['enddate'], report__date__gte=self.config['startdate'], sql_product__in=unique_products, ).distinct('case_id', 'product_id').order_by('case_id', 'product_id', '-report__date') for last_stock in last_stocks_in_period: index = 0 if last_stock.stock_on_hand > 0 else 1 result_dict[last_stock.product_id][index] += 1 for product in unique_products: with_stock = result_dict[product.product_id][0] without_stock = result_dict[product.product_id][1] without_data = total - with_stock - without_stock rows.append({"product_code": product.code, "product_name": product.name, "total": total, "with_stock": with_stock, "without_stock": without_stock, "without_data": without_data}) return rows
def rendered_content(self): locations = get_supply_points(self.config['location_id'], self.config['domain']) products = self.unique_products(locations, all=True) programs = {program.get_id: program.name for program in Program.by_domain(self.domain)} headers = [] if 'report_type' in self.config: from custom.ewsghana.reports.specific_reports.stock_status_report import MonthOfStockProduct headers = [h.html for h in MonthOfStockProduct(self.config).headers] result = {} for idx, product in enumerate(products, start=1): program = programs[product.program_id] product_dict = { 'name': product.name, 'code': product.code, 'idx': idx if not headers else headers.index(product.code) if product.code in headers else -1, 'checked': self.config['program'] is None or self.config['program'] == product.program_id } if program in result: result[program]['product_list'].append(product_dict) if result[program]['all'] and not product_dict['checked']: result[program]['all'] = False else: result[program] = { 'product_list': [product_dict], 'all': product_dict['checked'] } for _, product_dict in result.iteritems(): product_dict['product_list'].sort(key=lambda prd: prd['name']) return render_to_string('ewsghana/partials/product_selection_pane.html', { 'products_by_program': result, 'is_rendered_as_email': self.config.get('is_rendered_as_email', False), 'hide_columns': self.hide_columns })
def data(self): locations = self.location.get_descendants() locations_ids = locations.values_list('supply_point_id', flat=True) if not locations_ids: return { 'without_stock': {}, 'with_stock': {}, 'all': 0, 'months_of_stock': {}, 'stockouts': {}, 'unique_products': [], 'stockout_table_supply_points': [] } unique_products = self.unique_products(locations) transactions = self.get_stock_transactions_for_supply_points_and_products( locations_ids, unique_products ).values_list('case_id', 'product_id', 'report__date', 'stock_on_hand') current_mos_locations = get_supply_points(self.report_config['domain'], self.report_config['location_id']) current_mos_locations_ids = set( current_mos_locations.values_list('supply_point_id', flat=True) ) stock_states = StockState.objects.filter( sql_product__domain=self.domain, case_id__in=current_mos_locations_ids ) product_case_with_stock = defaultdict(set) product_case_without_stock = defaultdict(set) months_of_stock = defaultdict(lambda: defaultdict(dict)) stock_state_map = { (stock_state.case_id, stock_state.product_id): stock_state.get_monthly_consumption() for stock_state in stock_states } stockouts = defaultdict(set) for (case_id, product_id, date, stock_on_hand) in transactions: if stock_on_hand > 0: product_case_with_stock[product_id].add(case_id) if case_id in current_mos_locations_ids: stock_state_dict = stock_state_map.get((case_id, product_id)) if stock_state_dict: months_of_stock[case_id][product_id] = stock_on_hand / stock_state_dict else: months_of_stock[case_id][product_id] = None else: product_case_without_stock[product_id].add(case_id) if case_id in current_mos_locations_ids: stockouts[case_id].add(product_id) months_of_stock[case_id][product_id] = 0 return { 'without_stock': { product_id: len(case_list) for product_id, case_list in product_case_without_stock.iteritems() }, 'with_stock': { product_id: len(case_list) for product_id, case_list in product_case_with_stock.iteritems() }, 'all': locations.count(), 'months_of_stock': months_of_stock, 'stockouts': stockouts, 'unique_products': unique_products, 'stockout_table_supply_points': current_mos_locations }
def get_locations(self, loc_id, domain): return [loc.supply_point_id for loc in get_supply_points(loc_id, domain)]
def get_locations(self, loc_id, domain): return get_supply_points(loc_id, domain)