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, {})