def alter_data():
# session().connection().execute("""
# update horse.delivery_slip_parts
# set sell_price = horse.delivery_slip_parts.quantity_out * horse.order_parts.sell_price
# from horse.order_parts
# where horse.order_parts.order_part_id = horse.delivery_slip_parts.order_part_id
# """
session().commit()
def alter_data():
mainlog.info("Setting employee is_active flag")
session().connection().execute("""
update horse.employees
set is_active = (roles is not null) or (select count(*) from horse.timetracks where employee_id = horse.employees.employee_id and start_time > date '2013-11-01') > 0;"""
)
session().commit()
def alter_data():
q = session().query(Employee).filter(Employee.roles != None).all()
mainlog.info("Updating {} records".format(len(q)))
for employee in q:
if len(employee.roles) > 0:
mainlog.info(employee)
# The roles attributes is not easy to work with :-(
r = employee.roles
r.add(RoleType.view_prices)
employee.roles = r
session().commit()
def alter_data():
# Allow the migration script to be run several times
session().query(FilterQuery).delete()
owner_id = dao.employee_dao.find_by_login("dd").employee_id
fq = FilterQuery()
fq.name = "Finies ce mois"
fq.query = "CompletionDate in CurrentMonth"
fq.shared = True
fq.owner_id = owner_id
dao.filters_dao.save(fq)
fq = FilterQuery()
fq.name = u"Finies mois passe"
fq.query = "CompletionDate in MonthBefore"
fq.shared = True
fq.owner_id = owner_id
dao.filters_dao.save(fq)
fq = FilterQuery()
fq.name = "En production"
fq.query = "Status = ready_for_production"
fq.shared = True
fq.owner_id = owner_id
dao.filters_dao.save(fq)
fq = FilterQuery()
fq.name = "Devis"
fq.query = "Status = preorder"
fq.shared = True
fq.owner_id = owner_id
dao.filters_dao.save(fq)
fq = FilterQuery()
fq.name = "Dormantes"
fq.query = "Status = production_paused"
fq.shared = True
fq.owner_id = owner_id
dao.filters_dao.save(fq)
def alter_data():
q = session().query(OrderPart).filter(
and_(OrderPart.state == OrderPartStateType.completed,
OrderPart.completed_date == None)).all()
mainlog.info("Updating {} completed records".format(len(q)))
for part in q:
part.completed_date = part.order.completed_date
q = session().query(OrderPart).filter(
and_(OrderPart.state == OrderPartStateType.aborted,
OrderPart.completed_date == None)).all()
mainlog.info("Updating {} aborted records".format(len(q)))
for part in q:
part.completed_date = part.order.completed_date
session().commit()
def alter_structure():
session().connection().execute(
"""CREATE SEQUENCE horse.audit_trail_id_generator INCREMENT 1 MINVALUE 0 START 0;"""
)
# session().connection().execute("""CREATE TABLE horse.audit_trail
# (
# audit_trail_id integer NOT NULL,
# what character varying NOT NULL,
# detailed_what character varying,
# target_id integer NOT NULL,
# "when" timestamp without time zone NOT NULL,
# who_id integer,
# who_else character varying,
# CONSTRAINT audit_trail_pkey PRIMARY KEY (audit_trail_id),
# CONSTRAINT audit_trail_who_id_fkey FOREIGN KEY (who_id)
# REFERENCES employees (employee_id) MATCH SIMPLE
# ON UPDATE NO ACTION ON DELETE NO ACTION,
# CONSTRAINT who_is_human_or_computer CHECK (who_id IS NULL AND who_else IS NOT NULL OR who_id IS NOT NULL AND who_else IS NULL)
# )
# WITH (
# OIDS=FALSE
# );
# """)
# session().connection().execute("""CREATE INDEX ix_horse_audit_trail_when
# ON horse.audit_trail
# USING btree
# ("when");""")
session().connection().execute(
"GRANT ALL ON horse.audit_trail TO horse_clt ;")
session().connection().execute(
"GRANT ALL ON SEQUENCE horse.audit_trail_id_generator TO horse_clt ;")
# session().connection().execute("ALTER TABLE horse.delivery_slip ADD COLUMN active boolean DEFAULT true;")
# session().connection().execute("ALTER TABLE horse.delivery_slip ALTER COLUMN active SET NOT NULL;")
session().commit()
def alter_structure():
session().connection().execute("""CREATE TABLE horse.filter_queries (
filter_query_id SERIAL NOT NULL,
name VARCHAR NOT NULL,
query VARCHAR NOT NULL,
shared BOOLEAN NOT NULL,
owner_id INTEGER NOT NULL,
PRIMARY KEY (filter_query_id),
CONSTRAINT fq_by_name UNIQUE (name, owner_id),
FOREIGN KEY(owner_id) REFERENCES horse.employees (employee_id))""")
session().connection().execute("GRANT ALL ON horse.filter_queries TO horse_clt ;")
session().commit()
def alter_data():
mainlog.info("Terminating the old indirect order")
old_indirects = dao.order_dao.find_by_accounting_label(1)
old_indirects.state = OrderStatusType.order_completed
old_indirects.completed_date = date(2010, 1, 1)
session().commit()
mainlog.info("Reset everything to allow for multiple runs")
session().query(OrderPart).update({
OrderPart.state: OrderPartStateType.preorder,
OrderPart.completed_date: None
})
parts = session().query(OrderPart,Order.state,Order.creation_date).join(Order).\
filter(and_( Order.completed_date == None,
Order.state.in_([OrderStatusType.order_completed,OrderStatusType.order_aborted]))).\
all()
mainlog.info(
"Setting completed/aborted orders without date. Updating {} records".
format(len(parts)))
for part, order_state, creation_date in parts:
part.state = OrderPartStateType.state_from_order_state(order_state)
part.completed_date = creation_date
parts = session().query(OrderPart,Order.state,Order.completed_date).join(Order).\
filter(and_( Order.completed_date != None,
Order.state.in_([OrderStatusType.order_completed,OrderStatusType.order_aborted]))).\
all()
mainlog.info(
"Setting completed/aborted orders. Updating {} records".format(
len(parts)))
for part, order_state, completed_date in parts:
part.state = OrderPartStateType.state_from_order_state(order_state)
part.completed_date = completed_date
mainlog.info(
"Setting orders which are neither completed or aborted; clearing completion dates"
)
parts = session().query(OrderPart,Order.state).join(Order).\
filter(~Order.state.in_([OrderStatusType.order_aborted,OrderStatusType.order_completed])).\
all()
mainlog.info("Updating {} records".format(len(parts)))
for part, order_state in parts:
part.state = OrderPartStateType.state_from_order_state(order_state)
part.completed_date = None
mainlog.info("Setting completed_date")
parts = session().query(OrderPart.order_part_id,
func.max(DeliverySlip.creation).label("last_delivery")).\
filter(
and_( OrderPart.completed_date == None,
~OrderPart.state.in_([OrderPartStateType.completed,OrderPartStateType.aborted]),
or_( and_(OrderPart.estimated_time_per_unit > 0, OrderPart.tex2 == OrderPart.qty),
and_(OrderPart.estimated_time_per_unit == 0, OrderPart.qty == 0)))).\
outerjoin(DeliverySlipPart).\
outerjoin(DeliverySlip).\
group_by(OrderPart.order_part_id).\
all()
mainlog.info("Updating {} records".format(len(parts)))
for part in parts:
part.state = OrderPartStateType.completed
part.completed_date = date.today()
mainlog.info("Commit")
session().commit()
def alter_structure():
session().connection().execute(
"CREATE TYPE horse.ck_order_part_state_type AS ENUM ('preorder','aborted','completed','definition','production_paused','ready_for_production');"
)
session().connection().execute(
"ALTER TABLE horse.order_parts ADD COLUMN state horse.ck_order_part_state_type;"
)
# -- The idea is to set parts' states to match the order's state
session().connection().execute("""UPDATE horse.order_parts
SET state = (CASE
WHEN orders.state = 'preorder_definition' THEN CAST('preorder' AS horse.ck_order_part_state_type)
WHEN orders.state = 'order_completed' THEN CAST('completed' AS horse.ck_order_part_state_type)
WHEN orders.state = 'order_aborted' THEN CAST('aborted' AS horse.ck_order_part_state_type)
WHEN orders.state = 'order_definition' THEN CAST('ready_for_production' AS horse.ck_order_part_state_type)
WHEN orders.state = 'order_production_paused' THEN CAST('production_paused' AS horse.ck_order_part_state_type)
WHEN orders.state = 'order_ready_for_production' THEN CAST('ready_for_production' AS horse.ck_order_part_state_type)
ELSE CAST('ready_for_production' AS horse.ck_order_part_state_type)
END)
FROM horse.orders WHERE orders.order_id = order_parts.order_id;
""")
session().connection().execute(
"ALTER TABLE horse.order_parts ALTER COLUMN state SET NOT NULL;")
session().connection().execute(
"ALTER TABLE horse.order_parts ADD COLUMN completed_date DATE;")
session().connection().execute("""UPDATE horse.order_parts
SET completed_date = orders.completed_date
FROM horse.orders WHERE orders.order_id = order_parts.order_id;""")
session().connection().execute(
"CREATE INDEX parts_state_idx ON horse.order_parts (state);")
def alter_structure():
session().connection().execute(
"ALTER TABLE horse.employees ADD COLUMN is_active BOOLEAN not null default true;"
)