def check_avaliable(checkid):
try:
with postgresql.open(gp.dbconnect) as db:
check = db.query(
"SELECT i.importcode, ci.amount, ci.price "
"FROM root.checkitem ci "
"INNER JOIN root.item AS i ON i.globalid = ci.globalid "
"WHERE ci.checkid = " + str(checkid))
items = []
for (code, count, price) in check:
if not code:
code = "0"
items.append({
"code": str(code),
"count": float(count),
"price": float(price)
})
items_p = []
payments = db.query("SELECT paymentid, SUM(total) "
"FROM root.checkpayment "
"WHERE checkid = " + str(checkid) + " "
"GROUP BY paymentid")
for (payment, total) in payments:
items_p.append({
"payment": int(payment),
"total": float(total)
})
except:
return basic.resp(400, {"errors": "Haven't connect with base"})
answer = dict({"items": items, "payments": items_p})
return basic.resp(200, {"check": answer})
def add_barcode():
(json, errors) = basic.barcode_validate()
if errors:
return basic.resp(400, {"errors": errors})
barcode = json['barcode']
for item in barcode:
if (not 'code' in item) or \
(not 'barc' in item):
return basic.resp(400, {"errors": "Haven't value"})
else:
try:
with postgresql.open(basic.dbconnect) as db:
id_item = db.query("SELECT itemid FROM root.barcode "
"WHERE barcode = '" +
str(item['barc']) + "'")
if id_item == []:
db.query(
str("INSERT INTO root.barcode(itemid, barcode) "
"VALUES((SELECT itemid FROM root.item "
"WHERE importcode = '{0}'), '{1}')").format(
str(item['code']), str(item['barc'])))
else:
id_barc = db.query(
"SELECT barcodeid FROM root.barcode "
"WHERE barcode = '" + str(item['barc']) + "'")
db.query("UPDATE root.barcode SET itemid = " +
id_item[0][0] + " "
"WHERE barcodeid = " + str(id_barc[0][0]))
except:
return basic.resp(400, {"errors": "Haven't connect with base"})
return basic.resp(200, {})
def check_import(checkid):
try:
with postgresql.open(gp.dbconnect) as db:
db.execute("UPDATE root.check SET exported = 1 "
"WHERE checkid = " + str(checkid))
except:
return basic.resp(400, {"errors": "Haven't connect with base"})
return basic.resp(200, {})
def checks_list():
try:
with postgresql.open(gp.dbconnect) as db:
checks = db.query(
"SELECT checkid "
"FROM root.check "
"WHERE storn <> 1 AND exported <> 1 AND operationid = 1")
items = []
for checkid in checks:
items.append({"checkid": str(checkid[0])})
return basic.resp(200, {"checks": items})
except:
return basic.resp(400, {"errors": "Haven't connect with base"})
def items():
with postgresql.open(gp.dbconnect) as db:
tuples = db.query("SELECT i.itemid, i.name, i.descript "
"FROM root.item i "
"WHERE i.avaliable = 1")
items = []
for (itemid, name, descript) in tuples:
items.append({"id": itemid, "name": name, "comment": descript})
return basic.resp(200, {"items": items})
def add_items():
(json, errors) = basic.group_validate()
if errors:
return basic.resp(400, {"errors": errors})
groups = json['groups']
for item in groups:
if (not 'code' in item) or \
(not 'name' in item) or \
(not 'parentcode' in item):
return basic.resp(400, {"errors": "Haven't value"})
else:
try:
with postgresql.open(gp.dbconnect) as db:
id_item = db.query(
"SELECT itemgroupid FROM root.itemgroup "
"WHERE importcode = '" + str(item['code']) + "'")
if id_item == []:
db.query(
str("INSERT INTO root.itemgroup(name, avaliable, isbig, parentid, importcode) "
"VALUES('{0}', 1, 0, 0, '{1}') RETURNING itemgroupid"
).format(str(item['name']), str(item['code'])))
except:
return basic.resp(400, {"errors": "Haven't connect with base"})
for item in groups:
try:
with postgresql.open(gp.dbconnect) as db:
if str(item['parentcode']).replace(" ", "") != "":
id_itemparent = db.query(
"SELECT itemgroupid FROM root.itemgroup "
"WHERE importcode = '" + str(item['parentcode']) + "'")
db.query(
str("UPDATE root.itemgroup SET parentid = " +
str(id_itemparent[0][0]) + " "
"WHERE importcode = '" + str(item['code']) + "'"))
except:
return basic.resp(400, {"errors": "Haven't connect with base"})
return basic.resp(200, {})
def stores():
with postgresql.open(gp.dbconnect) as db:
tuples = db.query("SELECT s.itemid, s.value, s.stationid "
"FROM root.store s "
"INNER JOIN root.item i ON i.itemid = s.itemid "
"WHERE i.avaliable = 1 AND s.value > 0.0")
stores = []
for (itemid, store, stationid) in tuples:
stores.append({ "id": itemid, "store": float(store), "station": int(stationid) })
return basic.resp(200, {"stores": stores})
def prepay_status(prepay_id):
with postgresql.open(gp.dbconnect) as db:
tuples = db.query("SELECT ps.name, pp.prepaystateid "
"FROM root.prepay pp "
"INNER JOIN root.prepaystate ps "
"ON pp.prepaystateid = ps.prepaystateid "
"WHERE prepayid = " + str(prepay_id))
prepay_state = []
for (name, prepaystateid) in tuples:
prepay_state.append({ "name": name, "state_id": prepaystateid })
return basic.resp(200, {"prepay": prepay_state})
def prices():
with postgresql.open(gp.dbconnect) as db:
tuples = db.query("SELECT p.itemid, p.price, p.pricelistid "
"FROM root.price p "
"INNER JOIN root.item i ON i.itemid = p.itemid "
"WHERE i.avaliable = 1 AND p.price > 0.0")
prices = []
for (itemid, price, pricelistid) in tuples:
prices.append({
"id": itemid,
"price": float(price),
"pricelist": int(pricelistid)
})
return basic.resp(200, {"prices": prices})
def groups():
with postgresql.open(gp.dbconnect) as db:
tuples = db.query("SELECT g.itemgroupid, g.name, g.parentid, g.color "
"FROM root.itemgroup g "
"WHERE g.avaliable = 1 "
"ORDER BY g.parentid")
items = []
for (itemid, name, parentid, color) in tuples:
items.append({
"id": itemid,
"name": name,
"parent": parentid,
"color": color
})
return basic.resp(200, {"items": items})
def clients():
with postgresql.open(gp.dbconnect) as db:
tuples = db.query(
"SELECT clientid, name, phone, email, city, street, house, building, flat_office, card "
"FROM root.client "
"WHERE avaliable = 1")
clients = []
for (clientid, name, phone, email, city, street, house, building,
flat_office, card) in tuples:
clients.append({
"id": clientid,
"name": name,
"phone": phone,
"email": email,
"city": city,
"street": street,
"house": house,
"building": building,
"flat_office": flat_office,
"card": card
})
return basic.resp(200, {"clients": clients})
def ping():
print('hello')
return basic.resp(200, {"florapi": "Hello. I'm florapi."})
def page_not_found(e):
return basic.resp(405, {})
def prepay():
(json, errors) = basic.prepay_validate()
if errors: # list is not empty
return basic.resp(400, {"errors": errors})
client = json["client"]
items = json["items"]
delivery_address = json["delivery_to"]
if (not "pricelist" in json) or \
(not "station" in json) or \
(not "time" in json) or \
(not "date" in json):
return basic.resp(400, {"errors": "Haven't value"})
with postgresql.open(gp.dbconnect) as db:
client_count = 0
client_id = None
# Поиск клиента в базе
if "id" not in client:
client_count = 0
else:
client_count = int(
db.query(
"SELECT count(clientid) " +
"FROM root.client " +
"WHERE clientid = " + str(client["id"])
)[0][0]
)
if client_count == 0:
# Добавляем клиента если не находится
insert = db.prepare(
"INSERT INTO root.client (name, phone, email, city, street, " +
"house, building, flat_office) " +
"VALUES ($1, $2, $3, $4, $5, $6, $7, $8) " +
"RETURNING clientid;")
[(client_id,)] = insert(
str(client["name"]) if "name" in client else "",
str(client["phone"]) if "phone" in client else "",
str(client["email"]) if "email" in client else "",
str(client["city"]) if "city" in client else "",
str(client["street"]) if "street" in client else "",
str(client["house"]) if "house" in client else "",
str(client["building"]) if "building" in client else "",
str(client["flat_office"]) if "flat_office" in client else "")
elif client_count == 1:
client_id = str(client["id"])
elif client_count > 1:
return gp.resp(400, {"errors": "Multiple values"})
items_accepted = []
comment = str(json["comment"]) if "comment" in json else ""
for item in items:
if "id" not in item:
return gp.resp(400, {"errors": "Uncorrect value"})
result = db.query("SELECT i.itemid, i.globalid, s.value, p.price " +
"FROM root.item i " +
"INNER JOIN root.store s ON s.globalid = i.globalid " +
"INNER JOIN root.price p ON p.globalid = i.globalid " +
"WHERE i.itemid = " + str(item["id"]) + " AND " +
"p.pricelistid = " + str(json["pricelist"]) + " AND " +
"s.stationid = " + str(json["station"]))
if len(result) != 1:
comment += "NoName " + item["store"] + "шт " + item["price"] + " | "
continue
if float(item["store"]) > result[0]["value"]:
comment += "NoName " + item["store"] + "шт " + item["price"] + " | "
continue
items_accepted.append({
"itemid": result[0]["itemid"],
"globalid": result[0]["globalid"],
"store": item["store"],
"realprice": result[0]["price"],
"price": item["price"]
})
insert = db.prepare(
"INSERT INTO root.prepay (createtime, todate, totime, stationid, clientid, " +
"createby, storn, sourceid, prepayclassid, prepaystateid) " +
"VALUES ($1, $2, $3, $4, $5, 1, 1, 1, 1, 1) " +
"RETURNING prepayid")
prepay_id = None
[(prepay_id,)] = insert(
datetime.datetime.now(),
datetime.datetime.strptime(json["date"], '%Y-%m-%d').date(),
datetime.datetime.strptime(json["time"], '%H:%M').time(),
int(json["station"]),
int(client_id))
for item in items_accepted:
insert = db.prepare("SELECT public.calc_store_item($1, $2, $3, $4)")
insert(int(item["itemid"]), int(item["globalid"]), float("{0:.3f}".format(float(item["store"]))), int(json["station"]))
insert = db.prepare("INSERT INTO root.prepayitem(prepayid, itemid, amount, price, realprice, " +
"subtotal, total, stationid, globalid) " +
"VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9)")
insert(prepay_id, int(item["itemid"]), float(item["store"]), float(item["price"]), float(item["realprice"]),
float(item["price"]) * float(item["store"]), float(item["realprice"]) * float(item["store"]), int(json["station"]), int(item["globalid"]))
insert = db.prepare(
"INSERT INTO root.prepayinfo (customer, customerphone, name, phone, city, street, house, " +
"building, flat_office, prepayid) " +
"VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) ")
insert(
str(client["name"]) if "name" in client else "",
str(client["phone"]) if "phone" in client else "",
str(delivery_address["name"]) if "name" in client else "",
str(delivery_address["phone"]) if "phone" in client else "",
str(delivery_address["city"]) if "city" in client else "",
str(delivery_address["street"]) if "street" in client else "",
str(delivery_address["house"]) if "house" in client else "",
str(delivery_address["building"]) if "building" in client else "",
str(delivery_address["flat_office"]) if "flat_office" in client else "",
prepay_id)
insert = db.prepare("INSERT INTO root.prepaystatus(prepayid, paymentok, itemok, ready, send, received, cancell) " +
"VALUES($1, 0, 0, 0, 0, 0, 0)")
insert(prepay_id)
return basic.resp(200, {"id": str(prepay_id), "client": str(client_id)})
def add_store():
(json, errors) = basic.store_validate()
if errors:
return basic.resp(400, {"errors": errors})
stores = json['stores']
for item in stores:
if (not 'code' in item) or \
(not 'name' in item) or \
(not 'store' in item) or \
(not 'parentcode' in item):
return basic.resp(400, {"errors": "Haven't value"})
else:
try:
with postgresql.open(gp.dbconnect) as db:
id_item = db.query(
"SELECT itemid FROM root.item WHERE importcode = '" +
str(item['code']) + "'")
itemgroupid = db.query(
str("SELECT itemgroupid "
"FROM root.itemgroup "
"WHERE importcode = '" + str(item['parentcode']) +
"'"))
if id_item == []:
id_item = db.query(
str("INSERT INTO root.item(name, avaliable, position, importcode) "
"VALUES('{0}', 1, 999, '{1}') RETURNING itemid"
).format(str(item['name']), str(item['code'])))
else:
if len(itemgroupid[0]) == 1:
db.query(
str("UPDATE root.item SET itemgroupid = " +
str(itemgroupid[0][0]) + " "
"WHERE itemid = " + str(id_item[0][0])))
else:
db.query(
str("UPDATE root.item SET itemgroupid = 0 "
"WHERE itemid = " + str(id_item[0][0])))
id_global = db.query(
str("SELECT globalitemsid FROM root.globalitems "
"WHERE status = 1 AND itemid = " +
str(id_item[0][0])))
id_store = db.query(
str("SELECT storeid FROM root.store "
"WHERE stationid = 1 "
"AND itemid = " + str(id_item[0][0]) + " "
"AND globalid = " + str(id_global[0][0])))
if id_store == []:
db.query(
str("INSERT INTO root.store(itemid, globalid, value, stationid) "
"VALUES({0}, {1}, {2}, 1)").format(
str(id_item[0][0]), str(id_global[0][0]),
str(item['store'])))
else:
db.query(
str("UPDATE root.store SET value = " +
str(item['store']) + " "
"WHERE storeid = " + str(id_store[0][0])))
except:
return basic.resp(400, {"errors": "Haven't connect with base"})
return basic.resp(200, {})
