def update_warehouse_info(original_wh_id, wh_id, wh_name, wh_street, wh_state, wh_zip, wh_country): if original_wh_id != wh_id: a = dcur.execute( """ select warehouse_id from warehouse.warehouses where warehouse_id = %s; """, [wh_id]) a = dcur.fetchall() if a: return True a = dcur.execute( """ begin; update warehouse.warehouses set warehouse_id = trim(%s), warehouse_name = trim(%s), warehouse_street_address = trim(%s), warehouse_state = trim(%s), warehouse_zip = trim(%s), warehouse_country = trim(%s) where warehouse_id = %s; commit; """, [ wh_id, wh_name, wh_street, wh_state, wh_zip, wh_country, original_wh_id ])
def get_base_amazon_data(schema_name): if schema_name == "amazon_coins": a = dcur.execute( """ select item_sku, 'coin', quantity from {0}.template; """.format(schema_name)) elif schema_name == "amazon_entertainment_collectibles": a = dcur.execute( """ select item_sku, 'coin', limited_edition_quantity from {0}.template; """.format(schema_name)) elif schema_name == "amazon_food_service_and_jan_san": a = dcur.execute( """ select sku, "product-name", "number-of-items" from {0}.template; """.format(schema_name)) else: a = dcur.execute( """ select item_sku, item_name, quantity from {0}.template; """.format(schema_name)) a = dcur.fetchall() return a
def select_warehouse_list(): a = dcur.execute(""" select warehouse_id, warehouse_name, warehouse_type from warehouse.warehouses; """) a = dcur.fetchall() return a
def select_valid_filetypes(): a = dcur.execute(""" select file_type from orders.valid_file_type; """) a = dcur.fetchall() return a
def select_company_product_candidates(order_id): a = dcur.execute( """ select sku, marketplace_sku, product_name from marketplace.msku_marketplace mmm join marketplace.msku_sku using (marketplace_sku) left join product.descriptions using (sku) where exists (select * from orders.market_orders where marketplace = mmm.marketplace and internal_order_id = %s::int) and not exists (select * from orders.market_order_skus where marketplace_sku = mmm.marketplace_sku and internal_order_id = %s::int) and not exists (select * from orders.shipto_marketplace_skus where marketplace_sku = mmm.marketplace_sku); """, [order_id, order_id]) a = dcur.fetchall() return a
def select_valid_mskus(): a = dcur.execute(""" select marketplace_sku from marketplace.msku_sku; """) a = dcur.fetchall() return a
def update_pickingloc_info(wh, plid, old_plname, new_plname, upc): if old_plname != new_plname: a = dcur.execute( """ select * from warehouse.picking_locations wpp where exists (select * from warehouse.warehouse_picking_loc where picking_location_id = wpp.picking_location_id and warehouse_id = %s) and picking_location_name = %s; """, [wh, new_plname]) a = dcur.fetchall() if a: return True a = dcur.execute( """ begin; update warehouse.picking_locations set picking_location_name = %(plname)s, upc = %(upc)s::bigint where picking_location_id = %(plid)s::int; """, {"plname": new_plname, "upc": upc, "plid": plid})
def insert_picking_location(d): a = dcur.execute( """ select * from warehouse.picking_locations wpp where exists (select * from warehouse.warehouse_picking_loc where picking_location_id = wpp.picking_location_id and warehouse_id = %(wh)s) and picking_location_name = %(plname)s; """, d) a = dcur.fetchall() if a: return True if d["upc"] == "": d["upc"] = None a = dcur.execute( """ with new_plid (picking_location_id) as (insert into warehouse.picking_locations (picking_location_name, upc) values(%(plname)s, %(upc)s::bigint) returning picking_location_id) insert into warehouse.warehouse_picking_loc (warehouse_id, picking_location_id) select %(wh)s, picking_location_id from new_plid; """, d)
def update_user(original_username, username, real_name, utype, urole): if original_username != username: a = dcur.execute( """ select user_name from users.users where user_name = %(username)s; """, {"username": username}) a = dcur.fetchall() if a: return True if utype == "": utype = None a = dcur.execute( """ begin; select users.update_user(%(original_username)s, %(username)s, %(real_name)s, %(user_type)s, %(user_role)s); commit; """, { "original_username": original_username, "username": username, "real_name": real_name, "user_type": utype, "user_role": urole })
def select_users(): a = dcur.execute(""" select user_name, person_name, user_type, user_role from users.users; """) a = dcur.fetchall() return a
def insert_new_user(username, password, real_name, utype, urole): a = dcur.execute( """ select user_name from users.users where user_name = %(username)s; """, {"username": username}) a = dcur.fetchall() if a: return True a = dcur.execute( """ begin; insert into users.users (user_name, password, person_name, user_type, user_role) values (trim(%(user_name)s), %(password)s, trim(%(person_name)s), trim(%(user_type)s), trim(%(user_role)s)); commit """, { "user_name": username, "password": password, "person_name": real_name, "user_type": utype, "user_role": urole })
def select_valid_marketplaces(): a = dcur.execute(""" select marketplace from marketplace.valid_markeplace; """) a = dcur.fetchall() return a
def sku_upcs(): a = dcur.execute(""" select image from product.image_gallery """) a = dcur.fetchall() return a
def insert_picking_location(d): a = dcur.execute( """ select warehouse_id, warehouse_name, picking_location_name from warehouse.warehouses join warehouse.warehouse_picking_loc using(warehouse_id) join warehouse.picking_locations using(picking_location_id) where warehouse_id = %(wh)s and picking_location_name = %(picking-location)s; """, d) a = dcur.fetchall() if a: ploc = d["picking-location"] whname = a[0]["warehouse_name"] return "{0} already exists in {1} warehouse".format(ploc, whname) else: a = dcur.execute( """ with new_picking_location (new_location_id) as (insert into warehouse.picking_locations (picking_location_name, sku, qty) values (%(picking-location)s, %(sku)s, %(upc)s::int) returning picking_location_id ) insert into warehouse.warehouse_picking_loc (warehouse_id, picking_location_id) select %(wh)s, new_location_id from new_picking_location; """, d) return None
def insert_picking_location(d): a = dcur.execute( """ select * from warehouse.picking_locations wpp where exists (select * from warehouse.warehouse_picking_loc where picking_location_id = wpp.picking_location_id and warehouse_id = %(wh)s) and picking_location_name = %(plname)s; """, d) a = dcur.fetchall() if a: return True if d["upc"] == "": d["upc"] = None a = dcur.execute( """ with new_plid (picking_location_id) as (insert into warehouse.picking_locations (picking_location_name, upc) values(%(plname)s, %(upc)s::bigint) returning picking_location_id) insert into warehouse.warehouse_picking_loc (warehouse_id, picking_location_id) select %(wh)s, picking_location_id from new_plid; """, d)
def insert_new_user(username, password, real_name, utype, urole): a = dcur.execute( """ select user_name from users.users where user_name = %(username)s; """, {"username": username}) a = dcur.fetchall() if a: return True a = dcur.execute( """ begin; insert into users.users (user_name, password, person_name, user_type, user_role) values (trim(%(user_name)s), %(password)s, trim(%(person_name)s), trim(%(user_type)s), trim(%(user_role)s)); commit """, {"user_name": username, "password" :password, "person_name": real_name, "user_type": utype, "user_role": urole})
def select_pallet_locations(wh): a = dcur.execute( """ select pallet_location_id, pallet_location_name, pallet_id, string_agg(sku || '/' || upc || ' cases(' || case_qty || ')', ';;'), string_agg(sku || '(' || (case_qty * piece_qty * box_qty)::varchar || ')', ';;') from warehouse.warehouses join warehouse.warehouse_pallet_loc using (warehouse_id) join warehouse.pallet_locations using (pallet_location_id) left join warehouse.pallet_palletloc using (pallet_location_id) left join warehouse.pallet_case using (pallet_id) left join warehouse.case_box using (case_id) left join warehouse.boxes using (box_id) left join product.sku_upc using (upc) where warehouse_id = %s group by pallet_location_id, pallet_location_name, pallet_id order by pallet_location_id; """, [wh]) a = dcur.fetchall() return a
def select_all_images(): a = dcur.execute( """ select array_agg(sku) sku_list, image from product.image_gallery ig left join product.images im on (ig.image = im.main_image or ig.image = im.image_one or ig.image = im.image_two or ig.image = im.image_three or ig.image = im.image_four or ig.image = im.image_five or ig.image = im.image_six or ig.image = im.image_seven or ig.image = im.image_eight or ig.image = im.image_nine or ig.image = im.image_ten or ig.image = im.image_eleven or ig.image = im.image_twelve or ig.image = im.swatch_image) group by image order by image; """ ) a = dcur.fetchall() return a
def select_warehouse_types(): a = dcur.execute(""" select warehouse_type from warehouse.valid_warehouse_types; """) a = dcur.fetchall() return a
def update_user(original_username, username, real_name, utype, urole): if original_username != username: a = dcur.execute( """ select user_name from users.users where user_name = %(username)s; """, {"username": username}) a = dcur.fetchall() if a: return True if utype == "": utype = None a = dcur.execute( """ begin; select users.update_user(%(original_username)s, %(username)s, %(real_name)s, %(user_type)s, %(user_role)s); commit; """, {"original_username": original_username, "username": username, "real_name": real_name, "user_type": utype, "user_role": urole})
def sku_types(): a = dcur.execute(""" select sku_type from product.sku_types; """) a = dcur.fetchall() return a
def add_contact(d): a = dcur.execute( """ begin; with new_contact (contact_id) as (insert into company.contacts (contact_name, contact_position, contact_phone, contact_phone2, contact_email) values (%(contact-name)s, %(position)s, %(phone-one)s, %(phone-two)s, %(email)s) returning (company_contact_id)) insert into company.company_contact (company_id, company_contact_id) select %(cid)s, contact_id from new_contact returning company_contact_id; """, d) a = dcur.fetchall() a = dcur.execute( """ commit; """) return a
def select_all_running_inventory(): a = dcur.execute( """ select sku, upc, coalesce(total1, 0) + coalesce(total2, 0) from (select sku, upc, qty total1 from warehouse.warehouse_picking_loc join warehouse.picking_locations using (picking_location_id) join product.sku_upc using (upc)) t1 full join (select sku, upc, sum(box_qty * piece_qty * case_qty) total2 from warehouse.warehouses join warehouse.warehouse_pallet_loc using (warehouse_id) join warehouse.pallet_locations using (pallet_location_id) join warehouse.pallet_palletloc using (pallet_location_id) join warehouse.pallet_case using (pallet_id) join warehouse.case_box using (case_id) join warehouse.boxes using (box_id) join product.sku_upc using (upc) group by sku, upc) t2 using (sku, upc); """) a = dcur.fetchall() return a
def insert_pallet_location(wh, pl_name): a = dcur.execute( """ select * from warehouse.pallet_locations join warehouse.warehouse_pallet_loc using (pallet_location_id) where pallet_location_name = trim(%(plname)s) and warehouse_id = %(wh)s; """, {"plname": pl_name, "wh": wh}) a = dcur.fetchall() if a: return True a = dcur.execute( """ begin; with new_loc (pallet_location_id) as (insert into warehouse.pallet_locations (pallet_location_name) values (%(plname)s) returning pallet_location_id ) insert into warehouse.warehouse_pallet_loc (warehouse_id, pallet_location_id) select %(wh)s, pallet_location_id from new_loc; commit; """, {"plname": pl_name, "wh": wh})
def running_inventory(wh): a = dcur.execute( """ select sku, upc, sum(coalesce(box_qty * piece_qty * case_qty, 0) + coalesce(qty, 0)) from warehouse.warehouses join warehouse.warehouse_pallet_loc using (warehouse_id) join warehouse.pallet_locations using (pallet_location_id) join warehouse.pallet_palletloc using (pallet_location_id) join warehouse.pallet_case using (pallet_id) join warehouse.case_box using (case_id) join warehouse.boxes using (box_id) join product.sku_upc using (upc) right join warehouse.picking_locations using (upc) where warehouse_id = %s group by sku, upc; """, [wh]) a = dcur.fetchall() return a
def select_all_running_inventory(): a = dcur.execute(""" select sku, upc, coalesce(total1, 0) + coalesce(total2, 0) from (select sku, upc, qty total1 from warehouse.warehouse_picking_loc join warehouse.picking_locations using (picking_location_id) join product.sku_upc using (upc)) t1 full join (select sku, upc, sum(box_qty * piece_qty * case_qty) total2 from warehouse.warehouses join warehouse.warehouse_pallet_loc using (warehouse_id) join warehouse.pallet_locations using (pallet_location_id) join warehouse.pallet_palletloc using (pallet_location_id) join warehouse.pallet_case using (pallet_id) join warehouse.case_box using (case_id) join warehouse.boxes using (box_id) join product.sku_upc using (upc) group by sku, upc) t2 using (sku, upc); """) a = dcur.fetchall() return a
def generate_pallet_id(wh): a = dcur.execute( """ begin; with new_pl (pallet_location_id) as (insert into warehouse.pallet_locations (pallet_location_name) values ('staged') returning pallet_location_id) , new_pallet (pallet_id) as (insert into warehouse.pallets (pallet_id) values (default) returning pallet_id) , new_wh_palletloc as (insert into warehouse.warehouse_pallet_loc (warehouse_id, pallet_location_id) select %s, pallet_location_id from new_pl) insert into warehouse.pallet_palletloc (pallet_location_id, pallet_id) select pallet_location_id, pallet_id from new_pl, new_pallet returning pallet_id; """, [wh]) a = dcur.fetchall() a = dcur.execute( """ commit; """) return a
def select_pallet_locations(wh): a = dcur.execute( """ select pallet_location_id, pallet_location_name, pallet_id, string_agg(sku || '/' || upc || ' cases(' || case_qty || ')', ';;'), string_agg(sku || '(' || (case_qty * piece_qty * box_qty)::varchar || ')', ';;') from warehouse.warehouses join warehouse.warehouse_pallet_loc using (warehouse_id) join warehouse.pallet_locations using (pallet_location_id) left join warehouse.pallet_palletloc using (pallet_location_id) left join warehouse.pallet_case using (pallet_id) left join warehouse.case_box using (case_id) left join warehouse.boxes using (box_id) left join product.sku_upc using (upc) where warehouse_id = %s group by pallet_location_id, pallet_location_name, pallet_id order by pallet_location_id; """, [wh]) a = dcur.fetchall() return a
def insert_picking_location(d): a = dcur.execute( """ select warehouse_id, warehouse_name, picking_location_name from warehouse.warehouses join warehouse.warehouse_picking_loc using(warehouse_id) join warehouse.picking_locations using(picking_location_id) where warehouse_id = %(wh)s and picking_location_name = %(picking-location)s; """, d) a = dcur.fetchall() if a: ploc = d["picking-location"] whname = a[0]["warehouse_name"] return "{0} already exists in {1} warehouse".format(ploc, whname) else: a = dcur.execute( """ with new_picking_location (new_location_id) as (insert into warehouse.picking_locations (picking_location_name, sku, qty) values (%(picking-location)s, %(sku)s, %(upc)s::int) returning picking_location_id ) insert into warehouse.warehouse_picking_loc (warehouse_id, picking_location_id) select %(wh)s, new_location_id from new_picking_location; """, d) return None
def update_palletloc_name(plid, pl_name, wh): a = dcur.execute( """ select * from warehouse.pallet_locations join warehouse.warehouse_pallet_loc using (pallet_location_id) where pallet_location_name = trim(%(plname)s) and warehouse_id = %(wh)s; """, { "plname": pl_name, "wh": wh }) a = dcur.fetchall() if a: return True a = dcur.execute( """ begin; update warehouse.pallet_locations set pallet_location_name = %(plname)s where pallet_location_id = %s(plid)::int; commit; """, { "plname": pl_name, "plid": plid })
def select_valid_usertypes(): a = dcur.execute(""" select user_type from users.valid_user_types; """) a = dcur.fetchall() return a
def running_inventory(wh): a = dcur.execute( """ select sku, upc, sum(coalesce(box_qty * piece_qty * case_qty, 0) + coalesce(qty, 0)) from warehouse.warehouses join warehouse.warehouse_pallet_loc using (warehouse_id) join warehouse.pallet_locations using (pallet_location_id) join warehouse.pallet_palletloc using (pallet_location_id) join warehouse.pallet_case using (pallet_id) join warehouse.case_box using (case_id) join warehouse.boxes using (box_id) join product.sku_upc using (upc) right join warehouse.picking_locations using (upc) where warehouse_id = %s group by sku, upc; """, [wh]) a = dcur.fetchall() return a
def update_pickingloc_info(wh, plid, old_plname, new_plname, upc): if old_plname != new_plname: a = dcur.execute( """ select * from warehouse.picking_locations wpp where exists (select * from warehouse.warehouse_picking_loc where picking_location_id = wpp.picking_location_id and warehouse_id = %s) and picking_location_name = %s; """, [wh, new_plname]) a = dcur.fetchall() if a: return True a = dcur.execute( """ begin; update warehouse.picking_locations set picking_location_name = %(plname)s, upc = %(upc)s::bigint where picking_location_id = %(plid)s::int; """, { "plname": new_plname, "upc": upc, "plid": plid })
def generate_pallet_id(wh): a = dcur.execute( """ begin; with new_pl (pallet_location_id) as (insert into warehouse.pallet_locations (pallet_location_name) values ('staged') returning pallet_location_id) , new_pallet (pallet_id) as (insert into warehouse.pallets (pallet_id) values (default) returning pallet_id) , new_wh_palletloc as (insert into warehouse.warehouse_pallet_loc (warehouse_id, pallet_location_id) select %s, pallet_location_id from new_pl) insert into warehouse.pallet_palletloc (pallet_location_id, pallet_id) select pallet_location_id, pallet_id from new_pl, new_pallet returning pallet_id; """, [wh]) a = dcur.fetchall() a = dcur.execute(""" commit; """) return a
def insert_pallet_location(wh, pl_name): a = dcur.execute( """ select * from warehouse.pallet_locations join warehouse.warehouse_pallet_loc using (pallet_location_id) where pallet_location_name = trim(%(plname)s) and warehouse_id = %(wh)s; """, { "plname": pl_name, "wh": wh }) a = dcur.fetchall() if a: return True a = dcur.execute( """ begin; with new_loc (pallet_location_id) as (insert into warehouse.pallet_locations (pallet_location_name) values (%(plname)s) returning pallet_location_id ) insert into warehouse.warehouse_pallet_loc (warehouse_id, pallet_location_id) select %(wh)s, pallet_location_id from new_loc; commit; """, { "plname": pl_name, "wh": wh })
def sku_upcs(): a = dcur.execute( """ select image from product.image_gallery """) a = dcur.fetchall() return a
def select_image_gallery(): a = dcur.execute(""" select image from product.image_gallery order by image; """) a = dcur.fetchall() return a
def select_all_incoming_orders(): a = dcur.execute(""" select incoming_order_id, invoice, vendor_id, order_date, eta, completed from incoming.orders """) a = dcur.fetchall() return a
def sku_types(): a = dcur.execute( """ select sku_type from product.sku_types; """) a = dcur.fetchall() return a
def select_valid_usertypes(): a = dcur.execute( """ select user_type from users.valid_user_types; """) a = dcur.fetchall() return a
def select_salesteam_list(): a = dcur.execute(""" select user_name, person_name from users.users where user_type = 'sales'; """) a = dcur.fetchall() return a
def select_users(): a = dcur.execute( """ select user_name, person_name, user_type, user_role from users.users; """) a = dcur.fetchall() return a
def get_amazon_valid_arrays(schema_name, table_name): a = dcur.execute( """ select * from {0}.{1}; """.format(schema_name, table_name)) a = dcur.fetchall() return a
def select_valid_roles(): a = dcur.execute(""" select user_role from users.valid_user_roles where user_role <> 'original admin'; """) a = dcur.fetchall() return a
def valid_warehouses(): a = dcur.execute(""" select warehouse_id, warehouse_name, warehouse_type from warehouse.warehouses order by warehouse_name; """) a = dcur.fetchall() return a
def select_sku_kits(sku): a = dcur.execute( """ select child_sku, child_sku_qty from product.kits where master_sku = %s; """, [sku]) a = dcur.fetchall() return a
def select_image_gallery(): a = dcur.execute( """ select image from product.image_gallery order by image; """) a = dcur.fetchall() return a
def select_valid_roles(): a = dcur.execute( """ select user_role from users.valid_user_roles where user_role <> 'original admin'; """) a = dcur.fetchall() return a
def select_user_info(uid): a = dcur.execute( """ select user_name, person_name, user_type, user_role from users.users where user_name = %(user_id)s; """, {"user_id" : uid}) a = dcur.fetchall() return a
def select_palletloc_name(plid): a = dcur.execute( """ select pallet_location_name from warehouse.pallet_locations where pallet_location_id = %(plid)s::int; """, {"plid": plid}) a = dcur.fetchall() return a
def select_user_password_role(username): a = dcur.execute( """ select password, user_role from users.users where user_name = %(username)s; """, {"username": username}) a = dcur.fetchall() return a
def select_picking_location_info(pid): a = dcur.execute( """ select picking_location_name, upc, qty from warehouse.picking_locations where picking_location_id = %s::int; """, [pid]) a = dcur.fetchall() return a
def valid_warehouses(): a = dcur.execute( """ select warehouse_id, warehouse_name, warehouse_type from warehouse.warehouses order by warehouse_name; """) a = dcur.fetchall() return a
def select_vendor_contact_info(contact_id): a = dcur.execute( """ select contact_id, name, title, phone, alt_phone, email from vendor.contacts where contact_id = %s; """, [contact_id]) a = dcur.fetchall() return a
def get_upcs(): a = dcur.execute( """ select upc from product.sku_upc where upc is not null; """) a = dcur.fetchall() res = [i[0] for i in a] return res
def select_contact(cnid): a = dcur.execute( """ select company_contact_id, contact_name, contact_position, contact_phone, contact_phone2, contact_email from company.contacts where company_contact_id = %(cnid)s::int; """, {"cnid": cnid}) a = dcur.fetchall() return a
def select_palletlocs_list(wh): a = dcur.execute( """ select pallet_location_id, pallet_location_name from warehouse.warehouse_pallet_loc join warehouse.pallet_locations using (pallet_location_id) where warehouse_id = %(wh_id)s; """, {"wh_id": wh}) a = dcur.fetchall() return a