def get_shipping_order(conn, year=2013, month=1): i = get_next_pk(conn, 'test_shipping_orders', 'shipping_order_key') # Should consider the last month of year if month == 12: possible_months = [(year, month), (year + 1, 1)] else: possible_months = [(year, month), (year, month + 1)] month_weight = [0.8, 0.2] days = {} for y, m in possible_months: days[m] = count_days(y, m) for delivery_order in query(conn, ''' select tdo.* from test_contracts c left join test_sales_orders so on c.contract_key = so.contract_key left join test_sales_order_lines sol on so.sales_order_key = sol.sales_order_key left join test_shipping_plans sp on sp.sales_order_line_key = sol.sales_order_line_key left join test_delivery_orders tdo on tdo.shipping_plan_key = sp.shipping_plan_key where year(c.contract_date) = 2013 and month(c.contract_date) = 1 order by tdo.delivery_order_key ''', year, month): rand_idx = np.random.choice(len(possible_months), 1, p=month_weight)[0] y_val, m_val = possible_months[rand_idx] day_range = days[m_val] yield (i, delivery_order['DELIVERY_ORDER_KEY'], '%s-%s-%s 00:00:00' % (str(y_val), str(m_val).rjust(2, '0'), str(random.randint(1, day_range) ).rjust(2, '0') ), delivery_order['QTY'], 'N') i += 1
def get_contract(conn, year=2013, month=1): i = get_next_pk(conn, 'test_contracts', 'contract_key') days = count_days(year, month) for customer in query(conn, 'select * from test_customers'): yield (i, customer['CUSTOMER_KEY'], ('XSHT-%s' % str(i).rjust(6, '0')), ('%s-%s-%s' % (str(year), str(month).rjust(2, '0'), str(random.randint(1, days)))), 'N') i += 1
def get_sale_order(conn, year=2013, month=1): i = get_next_pk(conn, 'test_sales_orders', 'sales_order_key') days = count_days(year=year, month=month) for contract in query(conn, ''' select * from test_contracts where year(contract_date) = ? and month(contract_date) = ? ''', year, month): yield (i, 'XSDD-%s' % str(i).rjust(8, '0'), 11000000, contract['CONTRACT_CODE'], contract['CONTRACT_KEY'], ('%s-%s-%s' % (str(year), str(month).rjust(2, '0'), str(random.randint(1, days)))), None, None, 'N', contract['CUSTOMER_KEY']) i += 1
def get_daily_sale(conn, year=2013, month=1, total_bias=30000, daily_bias=1000): row = unique(conn, ''' select sum(cl.qty) as total from (select * from test_contracts where year(contract_date) = ? and month(contract_date) = ?) c left join test_contract_lines cl on c.contract_key = cl.contract_key ''', year, month) total = row['TOTAL'] actual = random.randint(total - total_bias, total + total_bias) expected_daily_qty = actual / count_days(year, month) i = get_next_pk(conn, 'test_daily_sale', 'daily_sale_key') for day in days_in_month(year, month): yield (i, 0, 0, 0, 12, random.randint(expected_daily_qty - daily_bias, expected_daily_qty + daily_bias), "this is a reminder", str(day)) i += 1