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
