def get_delivery_order(conn, year=2013, month=1, max_lines=3):
i = get_next_pk(conn, 'test_delivery_orders', 'delivery_order_key')
for order_line in query(conn, '''
select sol.* 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
where year(c.contract_date) = ? and month(c.contract_date) = ?
order by sol.sales_order_line_key
''', year, month):
plan = unique(conn, 'select * from test_shipping_plans where sales_order_line_key = ?',
order_line['SALES_ORDER_LINE_KEY'])
for taken in model.divide(order_line['QTY'], max_lines):
yield (i, 'FHTZD-%s' % str(i).rjust(8, '0'), 13000007,
8, 11, 21, taken, 1, plan['SHIPPING_PLAN_KEY'])
i += 1
def get_sale_order_line(conn, year=2013, month=1, max_lines=3):
i = get_next_pk(conn, 'test_sales_order_lines', 'sales_order_line_key')
for contract in query(conn, '''
select c.contract_key, c.contract_date, 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
group by c.contract_key, c.contract_date
order by c.contract_key
''', year, month):
order = unique(conn, 'select * from test_sales_orders where contract_key = ?', contract['CONTRACT_KEY'])
for taken in model.divide(contract['TOTAL'], max_lines):
yield (i, order['SALES_ORDER_KEY'], 1, taken, 1)
i += 1