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
