aji/costapy
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity

Compare commits

base: aji:system/invlab
Branches Tags
aji:master
aji:system/authsquare
aji:template/sbmodernbiz
aji:template/kiddy
aji:template/prime
aji:system/invlab
aji:bottle
aji:cherrypy
..
compare: aji:master
Branches Tags
aji:system/authsquare
aji:template/sbmodernbiz
aji:template/kiddy
aji:template/prime
aji:system/invlab
aji:bottle
aji:master
aji:cherrypy

No commits in common. "system/invlab" and "master" have entirely different histories.
system/inv ... master

22 changed files with 1 additions and 885 deletions
Show Stats Expand all files Collapse all files

5
bruno/bruno.json
View File

@ -1,5 +0,0 @@
{
"version": "1",
"name": "invlab",
"type": "collection"
}

21
bruno/inventory/Inventory Add.bru
View File

@ -1,21 +0,0 @@
meta {
name: Inventory Add
type: http
seq: 3
}
post {
url: http://localhost:11000/api/invlab/inventory/add
body: json
auth: none
}
body:json {
{
"item":1,
"flow":"sell",
"when":"2025-01-02 14:56:45",
"qty":2,
"note":null
}
}

15
bruno/inventory/Inventory Detail.bru
View File

@ -1,15 +0,0 @@
meta {
name: Inventory Detail
type: http
seq: 2
}
post {
url: http://localhost:11000/api/invlab/inventory/detail
body: json
auth: none
}
body:json {
{"id":1}
}

22
bruno/inventory/Inventory Edit.bru
View File

@ -1,22 +0,0 @@
meta {
name: Inventory Edit
type: http
seq: 4
}
post {
url: http://localhost:11000/api/invlab/inventory/edit
body: json
auth: none
}
body:json {
{
"id":6,
"item":1,
"flow":"sell",
"when":"2025-01-02 14:56:45",
"qty":2,
"note":null
}
}

15
bruno/inventory/Inventory List.bru
View File

@ -1,15 +0,0 @@
meta {
name: Inventory List
type: http
seq: 1
}
post {
url: http://localhost:11000/api/invlab/inventory/list
body: json
auth: none
}
body:json {
{}
}

15
bruno/inventory/Inventory Remove.bru
View File

@ -1,15 +0,0 @@
meta {
name: Inventory Remove
type: http
seq: 5
}
post {
url: http://localhost:11000/api/invlab/inventory/remove
body: json
auth: none
}
body:json {
{"id":3}
}

18
bruno/item/Item Add.bru
View File

@ -1,18 +0,0 @@
meta {
name: Item Add
type: http
seq: 3
}
post {
url: http://localhost:11000/api/invlab/item/add
body: json
auth: none
}
body:json {
{
"sku":"TEST-001",
"name":"Card holder wallet"
}
}

15
bruno/item/Item Detail.bru
View File

@ -1,15 +0,0 @@
meta {
name: Item Detail
type: http
seq: 2
}
post {
url: http://localhost:11000/api/invlab/item/detail
body: json
auth: none
}
body:json {
{"sku":"TEST-001"}
}

18
bruno/item/Item Edit.bru
View File

@ -1,18 +0,0 @@
meta {
name: Item Edit
type: http
seq: 4
}
post {
url: http://localhost:11000/api/invlab/item/edit
body: json
auth: none
}
body:json {
{
"sku":"TEST-001",
"name":"Card holder"
}
}

15
bruno/item/Item List.bru
View File

@ -1,15 +0,0 @@
meta {
name: Item List
type: http
seq: 1
}
post {
url: http://localhost:11000/api/invlab/item/list
body: json
auth: none
}
body:json {
{}
}

15
bruno/item/Item Remove.bru
View File

@ -1,15 +0,0 @@
meta {
name: Item Remove
type: http
seq: 5
}
post {
url: http://localhost:11000/api/invlab/item/remove
body: json
auth: none
}
body:json {
{"sku":"TEST-001"}
}

21
bruno/price/Price Add.bru
View File

@ -1,21 +0,0 @@
meta {
name: Price Add
type: http
seq: 3
}
post {
url: http://localhost:11000/api/invlab/price/add
body: json
auth: none
}
body:json {
{
"sku":"TEST-001",
"type":"sell",
"currency":"IDR",
"value":36000,
"periods":"2025-01-01 18:32:35"
}
}

15
bruno/price/Price Detail.bru
View File

@ -1,15 +0,0 @@
meta {
name: Price Detail
type: http
seq: 2
}
post {
url: http://localhost:11000/api/invlab/price/detail
body: json
auth: none
}
body:json {
{"id":1}
}

22
bruno/price/Price Edit.bru
View File

@ -1,22 +0,0 @@
meta {
name: Price Edit
type: http
seq: 4
}
post {
url: http://localhost:11000/api/invlab/price/edit
body: json
auth: none
}
body:json {
{
"id":1,
"sku":"TEST-001",
"type":"buy",
"currency":"IDR",
"value":20000,
"periods":"2025-01-01 18:32:35"
}
}

15
bruno/price/Price List.bru
View File

@ -1,15 +0,0 @@
meta {
name: Price List
type: http
seq: 1
}
post {
url: http://localhost:11000/api/invlab/price/list
body: json
auth: none
}
body:json {
{}
}

17
bruno/price/Price Remove.bru
View File

@ -1,17 +0,0 @@
meta {
name: Price Remove
type: http
seq: 5
}
post {
url: http://localhost:11000/api/invlab/price/remove
body: json
auth: none
}
body:json {
{
"id":1
}
}

79
handler.py
View File

@ -5,16 +5,11 @@
# This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. # This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
# You should have received a copy of the GNU General Public License along with this program. If not, see https://www.gnu.org/licenses/. # You should have received a copy of the GNU General Public License along with this program. If not, see https://www.gnu.org/licenses/.
from bottle import Bottle, route, request, response from bottle import Bottle, route
from config import directory from config import directory
import json
import templates.plain.main as template_public import templates.plain.main as template_public
import modules.public.home as public_home import modules.public.home as public_home
import modules.api.item as api_invlab_item
import modules.api.price as api_invlab_price
import modules.api.inventory as api_invlab_inventory
app = Bottle() app = Bottle()
@ -26,75 +21,3 @@ def index():
} }
} }
return public_home.main().html(params) return public_home.main().html(params)
@app.route('/api/invlab/item/<crud>', method=['OPTIONS', 'POST'])
def index(crud):
try:
if request.method == 'OPTIONS':
return None
else:
response.content_type = 'application/json'
params = request.json
if crud == 'list':
return json.dumps(api_invlab_item.item().list(params), indent = 2).encode()
elif crud == 'detail':
return json.dumps(api_invlab_item.item().detail(params), indent = 2).encode()
elif crud == 'add':
return json.dumps(api_invlab_item.item().add(params), indent = 2).encode()
elif crud == 'edit':
return json.dumps(api_invlab_item.item().edit(params), indent = 2).encode()
elif crud == 'remove':
return json.dumps(api_invlab_item.item().remove(params), indent = 2).encode()
else:
return json.dumps({}, indent = 2).encode()
except Exception as e:
print(str(e),flush=True)
return json.dumps({}, indent = 2).encode()
@app.route('/api/invlab/price/<crud>', method=['OPTIONS', 'POST'])
def index(crud):
try:
if request.method == 'OPTIONS':
return None
else:
response.content_type = 'application/json'
params = request.json
if crud == 'list':
return json.dumps(api_invlab_price.price().list(params), indent = 2).encode()
elif crud == 'detail':
return json.dumps(api_invlab_price.price().detail(params), indent = 2).encode()
elif crud == 'add':
return json.dumps(api_invlab_price.price().add(params), indent = 2).encode()
elif crud == 'edit':
return json.dumps(api_invlab_price.price().edit(params), indent = 2).encode()
elif crud == 'remove':
return json.dumps(api_invlab_price.price().remove(params), indent = 2).encode()
else:
return json.dumps({}, indent = 2).encode()
except Exception as e:
print(str(e),flush=True)
return json.dumps({}, indent = 2).encode()
@app.route('/api/invlab/inventory/<crud>', method=['OPTIONS', 'POST'])
def index(crud):
try:
if request.method == 'OPTIONS':
return None
else:
response.content_type = 'application/json'
params = request.json
if crud == 'list':
return json.dumps(api_invlab_inventory.inventory().list(params), indent = 2).encode()
elif crud == 'detail':
return json.dumps(api_invlab_inventory.inventory().detail(params), indent = 2).encode()
elif crud == 'add':
return json.dumps(api_invlab_inventory.inventory().add(params), indent = 2).encode()
elif crud == 'edit':
return json.dumps(api_invlab_inventory.inventory().edit(params), indent = 2).encode()
elif crud == 'remove':
return json.dumps(api_invlab_inventory.inventory().remove(params), indent = 2).encode()
else:
return json.dumps({}, indent = 2).encode()
except Exception as e:
print(str(e),flush=True)
return json.dumps({}, indent = 2).encode()

193
modules/api/inventory.py
View File

@ -1,193 +0,0 @@
import mysql.connector as mariadb
from config import database
from scripts import loggorilla
class inventory:
def __init__(self):
self.db_main = mariadb.connect(**database.db_main)
self.cursor = self.db_main.cursor(dictionary=True)
def list(self, params):
APIADDR = "/api/invlab/inventory/list"
response = {}
self.cursor.execute("BEGIN;")
try:
ls = []
self.cursor.execute("SELECT item.id AS item_id, item.name AS item_name, SUM(CASE WHEN inv.flow = 'sell' THEN price.value * inv.qty WHEN inv.flow = 'buy' THEN -price.value * inv.qty ELSE 0 END) AS profit, SUM(CASE WHEN inv.flow = 'buy' THEN inv.qty WHEN inv.flow = 'sell' THEN -inv.qty ELSE 0 END) AS current_qty FROM inventory inv LEFT JOIN item ON inv.item = item.id LEFT JOIN item_price price ON price.id = (SELECT sub.id FROM item_price sub WHERE sub.item = item.id AND sub.type = inv.flow AND sub.periods <= inv.when ORDER BY sub.periods DESC LIMIT 1) GROUP BY item.id, item.name ORDER BY item.name ASC;")
l1 = self.cursor.fetchall()
c1 = 0
for d1 in l1:
ls.append({
"item":{
"id" : d1["item_id" ],
"name" : d1["item_name" ]
},
"qty" : int(d1["current_qty" ]),
"profit" : int(d1["profit" ]),
"history" : []
})
self.cursor.execute("SELECT inv.id, inv.flow, DATE_FORMAT(inv.when, '%Y-%m-%d %H:%i:%S') AS `when`, DATE_FORMAT(price.periods, '%Y-%m-%d %H:%i:%S') AS price_periods, price.value AS price_value, inv.qty, (price.value * inv.qty) AS `total` FROM inventory inv LEFT JOIN item ON inv.item = item.id LEFT JOIN item_price price ON price.id = (SELECT sub.id FROM item_price sub WHERE sub.item = item.id AND sub.type = inv.flow AND sub.periods <= inv.when ORDER BY sub.periods DESC LIMIT 1 ) WHERE item.id = %s ORDER BY inv.when DESC; ", ( d1["item_id"], ) )
l2 = self.cursor.fetchall()
c2 = 0
for d2 in l2:
ls[c1]["history"].append({
"id" : d2["id" ],
"flow" : d2["flow" ],
"when" : d2["when" ],
"price" : {
"periods" : d2["price_periods" ],
"value" : d2["price_value" ]
},
"qty" : d2["qty" ],
"total" : d2["total" ]
})
c2+=2
c1+=1
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "success"
response["desc" ] = "data collected"
response["data" ] = ls
except Exception as e:
self.cursor.execute("ROLLBACK;")
loggorilla.error(APIADDR, str(e) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "failed"
response["desc" ] = "Internal Server Error. Please contact us if you still have an error."
finally:
self.cursor.execute("COMMIT;")
self.cursor.close()
self.db_main.close()
return response
def detail(self, params):
APIADDR = "/api/invlab/inventory/detail"
response = {}
loggorilla.prcss(APIADDR, "Define parameters")
id = params["id"]
self.cursor.execute("BEGIN;")
try:
self.cursor.execute("SELECT item.id AS item_id, item.name AS item_name, SUM(CASE WHEN inv.flow = 'sell' THEN price.value * inv.qty WHEN inv.flow = 'buy' THEN -price.value * inv.qty ELSE 0 END) AS profit, SUM(CASE WHEN inv.flow = 'buy' THEN inv.qty WHEN inv.flow = 'sell' THEN -inv.qty ELSE 0 END) AS current_qty FROM inventory inv LEFT JOIN item ON inv.item = item.id LEFT JOIN item_price price ON price.id = (SELECT sub.id FROM item_price sub WHERE sub.item = item.id AND sub.type = inv.flow AND sub.periods <= inv.when ORDER BY sub.periods DESC LIMIT 1) WHERE item.id = %s GROUP BY item.id, item.name ORDER BY item.name ASC;", ( id, ) )
d1 = self.cursor.fetchone()
data = {
"item":{
"id" : d1["item_id" ],
"name" : d1["item_name" ]
},
"qty" : int(d1["current_qty" ]),
"profit" : int(d1["profit" ]),
"history" : []
}
self.cursor.execute("SELECT inv.id, inv.flow, DATE_FORMAT(inv.when, '%Y-%m-%d %H:%i:%S') AS `when`, DATE_FORMAT(price.periods, '%Y-%m-%d %H:%i:%S') AS price_periods, price.value AS price_value, inv.qty, (price.value * inv.qty) AS `total` FROM inventory inv LEFT JOIN item ON inv.item = item.id LEFT JOIN item_price price ON price.id = (SELECT sub.id FROM item_price sub WHERE sub.item = item.id AND sub.type = inv.flow AND sub.periods <= inv.when ORDER BY sub.periods DESC LIMIT 1 ) WHERE item.id = %s ORDER BY inv.when DESC; ", ( d1["item_id"], ) )
l2 = self.cursor.fetchall()
c2 = 0
for d2 in l2:
data["history"].append({
"id" : d2["id" ],
"flow" : d2["flow" ],
"when" : d2["when" ],
"price" : {
"periods" : d2["price_periods" ],
"value" : d2["price_value" ]
},
"qty" : d2["qty" ],
"total" : d2["total" ]
})
c2+=2
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "success"
response["desc" ] = "data collected"
response["data" ] = data
except Exception as e:
self.cursor.execute("ROLLBACK;")
loggorilla.error(APIADDR, str(e) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "failed"
response["desc" ] = "Internal Server Error. Please contact us if you still have an error."
finally:
self.cursor.execute("COMMIT;")
self.cursor.close()
self.db_main.close()
return response
def add(self, params):
APIADDR = "/api/invlab/inventory/add"
response = {}
loggorilla.prcss(APIADDR, "Define parameters")
item = params["item" ]
flow = params["flow" ]
when = params["when" ]
qty = params["qty" ]
note = params["note" ]
self.cursor.execute("BEGIN;")
try:
loggorilla.prcss(APIADDR, "Inserting")
self.cursor.execute("INSERT INTO `inventory` VALUES (DEFAULT, %s, %s, %s, %s, %s) ;", (item, flow, when, qty, note) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "success"
response["desc" ] = "data added"
except Exception as e:
self.cursor.execute("ROLLBACK;")
loggorilla.error(APIADDR, str(e) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "failed"
response["desc" ] = "Internal Server Error. Please contact us if you still have an error."
finally:
self.cursor.execute("COMMIT;")
self.cursor.close()
self.db_main.close()
return response
def edit(self, params):
APIADDR = "/api/invlab/inventory/edit"
response = {}
loggorilla.prcss(APIADDR, "Define parameters")
key = params["id" ]
item = params["item" ]
flow = params["flow" ]
when = params["when" ]
qty = params["qty" ]
note = params["note" ]
self.cursor.execute("BEGIN;")
try:
loggorilla.prcss(APIADDR, "Updating")
self.cursor.execute("UPDATE `inventory` SET `item` = %s, `flow` = %s, `when` = %s, `qty` = %s, `note` = %s WHERE `id` = %s ;", (item, flow, when, qty, note, key) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "success"
response["desc" ] = "data change"
except Exception as e:
self.cursor.execute("ROLLBACK;")
loggorilla.error(APIADDR, str(e) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "failed"
response["desc" ] = "Internal Server Error. Please contact us if you still have an error."
finally:
self.cursor.execute("COMMIT;")
self.cursor.close()
self.db_main.close()
return response
def remove(self, params):
APIADDR = "/api/invlab/inventory/remove"
response = {}
loggorilla.prcss(APIADDR, "Define parameters")
key = params["id" ]
self.cursor.execute("BEGIN;")
try:
loggorilla.prcss(APIADDR, "Deleting")
self.cursor.execute("DELETE FROM `inventory` WHERE `id` = %s ;", (key,) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "success"
response["desc" ] = "data removed"
except Exception as e:
self.cursor.execute("ROLLBACK;")
loggorilla.error(APIADDR, str(e) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "failed"
response["desc" ] = "Internal Server Error. Please contact us if you still have an error."
finally:
self.cursor.execute("COMMIT;")
self.cursor.close()
self.db_main.close()
return response

160
modules/api/item.py
View File

@ -1,160 +0,0 @@
import mysql.connector as mariadb
from config import database
from scripts import loggorilla
class item:
def __init__(self):
self.db_main = mariadb.connect(**database.db_main)
self.cursor = self.db_main.cursor(dictionary=True)
def list(self, params):
APIADDR = "/api/invlab/item/list"
response = {}
self.cursor.execute("BEGIN;")
try:
ls = []
self.cursor.execute("SELECT * FROM `item`;")
l1 = self.cursor.fetchall()
c1 = 0
for d1 in l1:
ls.append({
"sku" :d1["sku" ],
"name" :d1["name" ],
"price" :{
"buy":[],
"sell":[]
}
})
self.cursor.execute("SELECT `id`, `currency`, `value`, DATE_FORMAT(`periods`, '%Y-%m-%d %H:%i:%S') AS `periods` from item_price WHERE `sku` = %s AND `type`='buy' ORDER BY periods DESC, id DESC; ", ( d1["sku"], ) )
ls[c1]["price"]["buy" ] = self.cursor.fetchall()
self.cursor.execute("SELECT `id`, `currency`, `value`, DATE_FORMAT(`periods`, '%Y-%m-%d %H:%i:%S') AS `periods` from item_price WHERE `sku` = %s AND `type`='sell' ORDER BY periods DESC, id DESC; ", ( d1["sku"], ) )
ls[c1]["price"]["sell"] = self.cursor.fetchall()
c1+=1
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "success"
response["desc" ] = "data collected"
response["data" ] = ls
except Exception as e:
self.cursor.execute("ROLLBACK;")
loggorilla.error(APIADDR, str(e) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "failed"
response["desc" ] = "Internal Server Error. Please contact us if you still have an error."
finally:
self.cursor.execute("COMMIT;")
self.cursor.close()
self.db_main.close()
return response
def detail(self, params):
APIADDR = "/api/invlab/item/detail"
response = {}
loggorilla.prcss(APIADDR, "Define parameters")
sku = params["sku"]
self.cursor.execute("BEGIN;")
try:
self.cursor.execute("SELECT * FROM `item` WHERE `sku` = %s ;", ( sku, ) )
r = self.cursor.fetchone()
data = {
"sku" :r["sku" ],
"name" :r["name" ],
"price" :{
"buy":[],
"sell":[]
}
}
self.cursor.execute("SELECT `id`, `currency`, `value`, DATE_FORMAT(`periods`, '%Y-%m-%d %H:%i:%S') AS `periods` from item_price WHERE `sku` = %s AND `type`='buy' ORDER BY periods DESC, id DESC; ", ( sku, ) )
data["price"]["buy" ] = self.cursor.fetchall()
self.cursor.execute("SELECT `id`, `currency`, `value`, DATE_FORMAT(`periods`, '%Y-%m-%d %H:%i:%S') AS `periods` from item_price WHERE `sku` = %s AND `type`='sell' ORDER BY periods DESC, id DESC; ", ( sku, ) )
data["price"]["sell"] = self.cursor.fetchall()
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "success"
response["desc" ] = "data collected"
response["data" ] = data
except Exception as e:
self.cursor.execute("ROLLBACK;")
loggorilla.error(APIADDR, str(e) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "failed"
response["desc" ] = "Internal Server Error. Please contact us if you still have an error."
finally:
self.cursor.execute("COMMIT;")
self.cursor.close()
self.db_main.close()
return response
def add(self, params):
APIADDR = "/api/invlab/item/add"
response = {}
loggorilla.prcss(APIADDR, "Define parameters")
sku = params["sku" ]
name = params["name" ]
self.cursor.execute("BEGIN;")
try:
loggorilla.prcss(APIADDR, "Inserting")
self.cursor.execute("INSERT INTO `item` VALUES (%s, %s) ;", (sku, name) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "success"
response["desc" ] = "data added"
except Exception as e:
self.cursor.execute("ROLLBACK;")
loggorilla.error(APIADDR, str(e) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "failed"
response["desc" ] = "Internal Server Error. Please contact us if you still have an error."
finally:
self.cursor.execute("COMMIT;")
self.cursor.close()
self.db_main.close()
return response
def edit(self, params):
APIADDR = "/api/invlab/item/edit"
response = {}
loggorilla.prcss(APIADDR, "Define parameters")
key = params["sku" ]
name = params["name" ]
self.cursor.execute("BEGIN;")
try:
loggorilla.prcss(APIADDR, "Updating")
self.cursor.execute("UPDATE `item` SET `name` = %s WHERE `sku` = %s ;", (name, key) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "success"
response["desc" ] = "data change"
except Exception as e:
self.cursor.execute("ROLLBACK;")
loggorilla.error(APIADDR, str(e) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "failed"
response["desc" ] = "Internal Server Error. Please contact us if you still have an error."
finally:
self.cursor.execute("COMMIT;")
self.cursor.close()
self.db_main.close()
return response
def remove(self, params):
APIADDR = "/api/invlab/item/remove"
response = {}
loggorilla.prcss(APIADDR, "Define parameters")
key = params["sku" ]
self.cursor.execute("BEGIN;")
try:
loggorilla.prcss(APIADDR, "Deleting")
self.cursor.execute("DELETE FROM `item` WHERE `sku` = %s ;", (key,) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "success"
response["desc" ] = "data removed"
except Exception as e:
self.cursor.execute("ROLLBACK;")
loggorilla.error(APIADDR, str(e) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "failed"
response["desc" ] = "Internal Server Error. Please contact us if you still have an error."
finally:
self.cursor.execute("COMMIT;")
self.cursor.close()
self.db_main.close()
return response

139
modules/api/price.py
View File

@ -1,139 +0,0 @@
import mysql.connector as mariadb
from config import database
from scripts import loggorilla
class price:
def __init__(self):
self.db_main = mariadb.connect(**database.db_main)
self.cursor = self.db_main.cursor(dictionary=True)
def list(self, params):
APIADDR = "/api/invlab/price/list"
response = {}
self.cursor.execute("BEGIN;")
try:
self.cursor.execute("SELECT `id`, `sku`, `type`, `currency`, `value`, DATE_FORMAT(`periods`, '%Y-%m-%d %H:%i:%S') AS `periods` FROM `item_price` ORDER BY periods DESC, id DESC;")
ls = self.cursor.fetchall()
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "success"
response["desc" ] = "data collected"
response["data" ] = ls
except Exception as e:
self.cursor.execute("ROLLBACK;")
loggorilla.error(APIADDR, str(e) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "failed"
response["desc" ] = "Internal Server Error. Please contact us if you still have an error."
finally:
self.cursor.execute("COMMIT;")
self.cursor.close()
self.db_main.close()
return response
def detail(self, params):
APIADDR = "/api/invlab/price/detail"
response = {}
loggorilla.prcss(APIADDR, "Define parameters")
id = params["id"]
self.cursor.execute("BEGIN;")
try:
self.cursor.execute("SELECT `id`, `sku`, `type`, `currency`, `value`, DATE_FORMAT(`periods`, '%Y-%m-%d %H:%i:%S') AS `periods` FROM `item_price` WHERE id= %s ORDER BY periods DESC, id DESC;", (id,))
data = self.cursor.fetchone()
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "success"
response["desc" ] = "data collected"
response["data" ] = data
except Exception as e:
self.cursor.execute("ROLLBACK;")
loggorilla.error(APIADDR, str(e) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "failed"
response["desc" ] = "Internal Server Error. Please contact us if you still have an error."
finally:
self.cursor.execute("COMMIT;")
self.cursor.close()
self.db_main.close()
return response
def add(self, params):
APIADDR = "/api/invlab/price/add"
response = {}
loggorilla.prcss(APIADDR, "Define parameters")
sku = params["sku" ]
type = params["type" ]
currency = params["currency" ]
value = params["value" ]
periods = params["periods" ]
self.cursor.execute("BEGIN;")
try:
loggorilla.prcss(APIADDR, "Inserting")
self.cursor.execute("INSERT INTO `item_price` VALUES (DEFAULT, %s, %s, %s, %s, %s) ;", (sku, type, currency, value, periods) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "success"
response["desc" ] = "data added"
except Exception as e:
self.cursor.execute("ROLLBACK;")
loggorilla.error(APIADDR, str(e) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "failed"
response["desc" ] = "Internal Server Error. Please contact us if you still have an error."
finally:
self.cursor.execute("COMMIT;")
self.cursor.close()
self.db_main.close()
return response
def edit(self, params):
APIADDR = "/api/invlab/price/edit"
response = {}
loggorilla.prcss(APIADDR, "Define parameters")
key = params["id" ]
sku = params["sku" ]
type = params["type" ]
currency = params["currency" ]
value = params["value" ]
periods = params["periods" ]
self.cursor.execute("BEGIN;")
try:
loggorilla.prcss(APIADDR, "Updating")
self.cursor.execute("UPDATE `item_price` SET `sku` = %s,`type` = %s,`currency` = %s,`value` = %s,`periods` = %s WHERE `id` = %s ;", (sku, type, currency, value, periods, key) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "success"
response["desc" ] = "data change"
except Exception as e:
self.cursor.execute("ROLLBACK;")
loggorilla.error(APIADDR, str(e) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "failed"
response["desc" ] = "Internal Server Error. Please contact us if you still have an error."
finally:
self.cursor.execute("COMMIT;")
self.cursor.close()
self.db_main.close()
return response
def remove(self, params):
APIADDR = "/api/invlab/price/remove"
response = {}
loggorilla.prcss(APIADDR, "Define parameters")
key = params["id" ]
self.cursor.execute("BEGIN;")
try:
loggorilla.prcss(APIADDR, "Deleting")
self.cursor.execute("DELETE FROM `item_price` WHERE `id` = %s ;", (key,) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "success"
response["desc" ] = "data removed"
except Exception as e:
self.cursor.execute("ROLLBACK;")
loggorilla.error(APIADDR, str(e) )
loggorilla.prcss(APIADDR, "Giving response")
response["status" ] = "failed"
response["desc" ] = "Internal Server Error. Please contact us if you still have an error."
finally:
self.cursor.execute("COMMIT;")
self.cursor.close()
self.db_main.close()
return response

13
scripts/loggorilla.py
View File

@ -1,13 +0,0 @@
import datetime
def prcss(loc, msg):
print(f"[loggorilla][{datetime.datetime.now()}][\033[32mprcss\033[39m][\033[95m{loc}\033[39m] {msg}", flush=True)
def accss(loc, msg):
print(f"[loggorilla][{datetime.datetime.now()}][\033[36maccss\033[39m][\033[95m{loc}\033[39m] {msg}", flush=True)
def fyinf(loc, msg):
print(f"[loggorilla][{datetime.datetime.now()}][\033[93mfyinf\033[39m][\033[95m{loc}\033[39m] {msg}", flush=True)
def error(loc, msg):
print(f"[loggorilla][{datetime.datetime.now()}][\033[31merror\033[39m][\033[95m{loc}\033[39m] {msg}", flush=True)

38
sql/inv.sql
View File

@ -1,38 +0,0 @@
CREATE TABLE `item` (
`sku` VARCHAR(60) NOT NULL,
`name` LONGTEXT DEFAULT NULL,
PRIMARY KEY (`sku`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Reselling item price, not including production item price, service item price, and subscription item price
CREATE TABLE `item_price` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`sku` VARCHAR(60) NOT NULL,
`type` VARCHAR(4) NOT NULL, -- buy, sell
`currency` VARCHAR(3) NOT NULL,
`value` INT(11) NOT NULL,
`periods` DATETIME NOT NULL,
PRIMARY KEY (`id`),
KEY `sku` (`sku`),
CONSTRAINT `item_price_fk_sku`
FOREIGN KEY (`sku`)
REFERENCES `item`(`sku`)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Not including assets inventory, get from buying/production, and shipping/expedition
CREATE TABLE `inventory` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`sku` VARCHAR(60) NOT NULL,
`flow` VARCHAR(20) NOT NULL, -- in, out, buy, sell, buy return, sell return, use, produce
`when` DATETIME NOT NULL,
`qty` INT(11) NOT NULL,
`note` LONGTEXT DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `sku` (`sku`),
CONSTRAINT `inventory_fk_sku`
FOREIGN KEY (`sku`)
REFERENCES `item`(`sku`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- get from: buying/production