def new_branch(curr_user):
if not curr_user.is_admin():
return ResponseMessage(False, message="Unauthorized user!").resp(), 401
data = request.get_json()
data['created_by'] = curr_user.id
data['updated_by'] = curr_user.id
if not data['code'] or not data['name']:
return ResponseMessage(
False, message="Missing required argument!").resp(), 401
check = Check(**data)
# check if Branch is exists
if check.branch_exist(data['code']):
return ResponseMessage(
False, message="Branch code already exists!").resp(), 401
try:
branch = Branch(**data)
db.session.add(branch)
db.session.commit()
branch_schema = BranchSchema()
result = branch_schema.dump(branch)
return ResponseMessage(True,
message="Successfully added!",
data=result).resp()
except (pyodbc.IntegrityError, IntegrityError) as err:
db.session.rollback()
return ResponseMessage(False, message=f"{err}").resp(), 500
except Exception as err:
db.session.rollback()
return ResponseMessage(False, message=f"{err}").resp(), 500
finally:
db.session.close()
def update_item(curr_user, id):
user = curr_user
if not user.is_admin():
return ResponseMessage(False, message="Unauthorized user!").resp(), 401
data = request.get_json()
try:
item = Items.query.get(id)
except:
return ResponseMessage(False, message="Invalid item id!").resp(), 401
check = Check(**data)
if data['item_code']:
if check.itemcode_exist():
return ResponseMessage(
False, message="Item code already exists!").resp(), 401
item.item_code = data['item_code']
if data['item_name']:
item.item_name = data['item_name']
if data['uom']:
if not check.uom_exist():
return ResponseMessage(False,
message="Uom doesn't exists!").resp(), 401
item.uom = data['uom']
if data['group_code']:
if not check.itemgroup_exist():
return ResponseMessage(
False, message="Item group doesn't exists!").resp(), 401
item.item_group = data['group_code']
if data['min_stock']:
item.min_stock = data['min_stock']
if data['max_stock']:
item.max_stock = data['max_stock']
if data['price']:
item.price = data['price']
item.updated_by = user.id
item.date_updated = datetime.now()
try:
db.session.commit()
item_schema = ItemsSchema()
result = item_schema.dump(item)
return ResponseMessage(True,
message="Successfully updated",
data=result).resp()
except (pyodbc.IntegrityError, exc.IntegrityError) as err:
db.session.rollback()
return ResponseMessage(False, message=f"{err}").resp(), 401
except:
db.session.rollback()
return ResponseMessage(
False, message="Unable to update!. Unknown error!").resp(), 401
finally:
db.session.close()
def new_warehouse(curr_user):
user = curr_user
if not user.is_admin():
return ResponseMessage(False, message="Unathorized user!").resp(), 401
# list of dictionary
data = request.get_json()
success = []
unsuccess = []
try:
for row in data:
if not row['whsecode'] or not row['whsename'] or not row['branch']:
raise Exception("Missing required field!")
check = Check(**row)
# initialize dictionary to append in success or unsuccess list
d = {}
id = row['whsecode']
d[id] = []
if check.whsecode_exist():
raise Exception(
f"Warehouse code '{row['whsecode']}' is already exists!")
if not check.branch_exist(row['branch']):
raise Exception(f"Branch code '{row['branch']}' doesnt exist!")
whse = Warehouses(**row)
whse.created_by = user.id
whse.updated_by = user.id
success.append(d)
db.session.add(whse)
db.session.commit()
return ResponseMessage(True,
data={
"Successfully": success,
"Unsuccessful ": unsuccess
}).resp()
except (pyodbc.IntegrityError, IntegrityError) as err:
db.session.rollback()
return ResponseMessage(False, message=f"{err}").resp(), 500
except Exception as err:
db.session.rollback()
return ResponseMessage(False, message=f"{err}").resp(), 500
finally:
db.session.close()
def create_item(curr_user):
user = curr_user
if not user.is_admin():
return ResponseMessage(False, message="Unauthorized user!").resp(), 401
data = request.get_json()
try:
success = []
unsuccess = []
for row in data:
row['created_by'] = curr_user.id
row['updated_by'] = curr_user.id
if not row['item_code'] or not row['item_name'] or not row[
'item_group'] or not row['price']:
return ResponseMessage(
False, message="Missing required fields!").resp(), 401
check = Check(**row)
# initialize dictionary to append in success or unsuccess list
d = {}
id = row['item_code']
d[id] = []
if not check.uom_exist():
d[id].append(f"Uom '{row['uom']}' not exists!")
unsuccess.append(d)
continue
if not check.itemgroup_exist():
unsuccess.append(d)
d[id].append(f"Item group '{row['item_group']}' not exists!")
continue
if check.itemcode_exist():
unsuccess.append(d)
d[id].append(f"Item code '{row['item_code']}' already exists!")
continue
item = Items(**row)
item.barcode = hash(row['item_code'])
success.append(d)
db.session.add(item)
db.session.commit()
return ResponseMessage(True,
data={
"Successfully": success,
"Unsuccessful ": unsuccess
}).resp()
except Exception as err:
db.session.rollback()
return ResponseMessage(False, message=f'{err}').resp(), 401
except (pyodbc.IntegrityError, exc.IntegrityError) as err:
db.session.rollback()
return ResponseMessage(False, message=f'{err}').resp(), 401
finally:
db.session.close()
def create_receive(curr_user):
if not curr_user.can_receive():
return ResponseMessage(False, message="Unauthorized user!").resp(), 401
# query the whse
whse = Warehouses.query.filter_by(whsecode=curr_user.whse).first()
if whse.is_cutoff():
return ResponseMessage(
False, message="Your warehouse cutoff is enable!").resp(), 401
data = request.get_json()
details = data['details']
data['header']['created_by'] = curr_user.id
data['header']['updated_by'] = curr_user.id
try:
if data['header']['transtype'] in ['SAPIT', 'SAPPO']:
if not data['header']['sap_number']:
raise Exception("Missing SAP number!")
elif type(int(data['header']['sap_number'])) != int:
raise Exception("Invalid SAP number, must be integer!")
except Exception as err:
return ResponseMessage(False, message=f"{err}").resp(), 401
if data['header']['transtype'] != 'TRFR':
if not details:
return ResponseMessage(
False, message="No data in details argument!").resp(), 401
try:
obj = ObjectType.query.filter_by(code='RCVE').first()
series = Series.query.filter_by(whsecode=curr_user.whse,
objtype=obj.objtype).first()
if series.next_num + 1 > series.end_num:
raise Exception("Series number already in max!")
if not series:
raise Exception("Invalid Series")
reference = f"{series.code}-{obj.code}-{series.next_num}"
# add to header
data['header']['series'] = series.id
data['header']['objtype'] = obj.objtype
data['header']['seriescode'] = series.code
data['header']['transnumber'] = series.next_num
data['header']['reference'] = reference
r_h = ReceiveHeader(**data['header'])
# add 1 to series next num
series.next_num += 1
it = ITHeader.query.filter(
and_(ITHeader.docnum == r_h.sap_number,
ITHeader.docstatus.in_(['C', 'N']))).first()
if it:
if it.docstatus == 'C':
raise Exception("Document is already closed!")
raise Exception("Document is already canceled!")
db.session.add_all([r_h, series])
db.session.flush()
# if SAP IT
if data['header']['transtype'] == 'SAPIT':
if data['header']['sap_number']:
if type(int(data['header']['sap_number'])) != int:
r_h.docstatus = 'O'
else:
r_h.docstatus = 'C'
for row in details:
check = Check(**row)
# check if valid
if not check.itemcode_exist():
raise Exception("Invalid itemcode!")
elif not check.uom_exist():
raise Exception("Invalid uom!")
elif not check.towhse_exist():
raise Exception("Invalid to whse code!")
if row['to_whse'] != curr_user.whse:
raise Exception(
"Invalid to_whse must be current user whse!")
if row['actualrec'] < 1:
raise Exception(
"Cannot add if actual receive is less than 1.")
r_r = ReceiveRow(receive_id=r_h.id,
transnumber=r_h.transnumber,
created_by=curr_user.id,
updated_by=curr_user.id,
sap_number=r_h.sap_number,
objtype=r_h.objtype,
**row)
db.session.add(r_r)
# if From SAP PO
elif data['header']['transtype'] == 'SAPPO':
if data['header']['sap_number']:
if type(int(data['header']['sap_number'])) != int:
r_h.docstatus = 'O'
else:
r_h.docstatus = 'C'
for row in details:
check = Check(**row)
data['from_whse'] = data['header']['supplier']
data['to_whse'] = curr_user.whse
# check if valid
if not check.itemcode_exist():
raise Exception("Invalid itemcode!")
elif not check.uom_exist():
raise Exception("Invalid uom!")
elif not check.towhse_exist():
raise Exception("Invalid to whse code!")
if row['to_whse'] != curr_user.whse:
raise Exception(
"Invalid to_whse must be current user whse!")
r_r = ReceiveRow(receive_id=r_h.id,
transnumber=r_h.transnumber,
created_by=curr_user.id,
updated_by=curr_user.id,
sap_number=r_h.sap_number,
objtype=r_h.objtype,
**row)
db.session.add(r_r)
# if from pos system transfer
elif data['header']['transtype'] == 'TRFR':
# transfer id
base_id = data['header']['base_id']
if base_id:
transfer = TransferHeader.query.get(base_id)
if not transfer:
raise Exception("Invalid base_id")
transfer.docstatus = 'C'
trans_row = TransferRow.query.filter_by(
transfer_id=base_id).all()
if not trans_row:
raise Exception("No transfer rows!")
for row in details:
check = Check(**row)
# check if valid
if not check.itemcode_exist():
raise Exception("Invalid item code!")
elif not check.uom_exist():
raise Exception("Invalid uom!")
elif not check.towhse_exist():
raise Exception("Invalid to whse code!")
if row['to_whse'] != curr_user.whse:
raise Exception(
"Invalid to_whse must be current user whse!")
if row['actualrec'] < 1:
raise Exception(
"Cannot add if actual receive is less than 1.")
r_r = ReceiveRow(receive_id=r_h.id,
transnumber=r_h.transnumber,
created_by=curr_user.id,
updated_by=curr_user.id,
sap_number=r_h.sap_number,
objtype=r_h.objtype,
**row)
db.session.add(r_r)
# Manual
elif data['header']['transtype'] not in ['TRFR', 'SAPIT', 'SAPPO']:
for row in details:
row['to_whse'] = curr_user.whse
check = Check(**row)
# check if valid
if r_h.type2.upper() == 'SAPIT':
if not check.fromwhse_exist():
raise Exception("Invalid from whse code!")
if not check.itemcode_exist():
raise Exception("Invalid item code!")
elif not check.uom_exist():
raise Exception("Invalid uom!")
elif not check.towhse_exist():
raise Exception("Invalid from whse code!")
if row['to_whse'] != curr_user.whse:
raise Exception("Invalid to_whse!")
r_r = ReceiveRow(receive_id=r_h.id,
transnumber=r_h.transnumber,
created_by=curr_user.id,
updated_by=curr_user.id,
sap_number=r_h.sap_number,
objtype=r_h.objtype,
**row)
db.session.add(r_r)
# Check and Update docstatus of SAP IT Table
if r_h.transtype == 'SAPIT':
it_header = ITHeader.query.filter(
and_(ITHeader.docnum == r_h.sap_number,
ITRow.actual_rec != None,
ITHeader.docstatus == 'O')).first()
it_header.docstatus = 'C'
# Check and Update docstatus of SAP PO table
if r_h.transtype == 'SAPPO':
po_header = POHeader.query.filter(
and_(POHeader.docnum == r_h.sap_number,
PORow.actual_rec != None,
POHeader.docstatus == 'O')).first()
po_header.docstatus = 'C'
db.session.commit()
recv_schema = ReceiveHeaderSchema(
only=("id", "series", "seriescode", "transnumber", "sap_number",
"docstatus", "transtype", "transdate", "reference",
"reference2", "remarks", "recrow"))
result = recv_schema.dump(r_h)
return ResponseMessage(True,
message="Successfully added!",
data=result).resp()
except (pyodbc.IntegrityError, exc.IntegrityError) as err:
db.session.rollback()
return ResponseMessage(False, message=f"{err}").resp(), 500
except Exception as err:
db.session.rollback()
return ResponseMessage(False, message=f"{err}").resp(), 500
finally:
db.session.rollback()
db.session.close()
def create_transfer(curr_user):
if not curr_user.can_transfer():
return ResponseMessage(False,
message="Unauthorized to transfer!").resp(), 401
# query the whse
whse = Warehouses.query.filter_by(whsecode=curr_user.whse).first()
if whse.is_cutoff():
return ResponseMessage(
False, message="Your warehouse cutoff is enable!").resp(), 401
data = request.get_json()
details = data['details']
if data['header']['transdate']:
data['header']['transdate'] = datetime.strptime(
data['header']['transdate'], '%Y/%m/%d %H:%M')
if not details:
return ResponseMessage(False,
message="No data in details argument!").resp()
try:
obj = ObjectType.query.filter_by(code='TRFR').first()
series = Series.query.filter_by(whsecode=curr_user.whse,
objtype=obj.objtype).first()
if series.next_num + 1 > series.end_num:
raise Exception("Series number already in max!")
if not series:
raise Exception("Invalid Series")
reference = f"{series.code}-{obj.code}-{series.next_num}"
t_h = TransferHeader(series=series.id,
seriescode=series.code,
transnumber=series.next_num,
reference=reference,
created_by=curr_user.id,
updated_by=curr_user.id,
**data['header'])
t_h.objtype = obj.objtype
# add 1 to series next num
series.next_num += 1
db.session.add_all([t_h, series])
db.session.flush()
for row in details:
# add to user whse to data dictionary as from whse
row['from_whse'] = curr_user.whse
check = Check(**row)
# check if valid
if not check.itemcode_exist():
raise Exception("Invalid itemcode!")
elif not check.uom_exist():
raise Exception("Invalid uom!")
elif not check.towhse_exist():
raise Exception("Invalid to whse code!")
if row['from_whse'] != curr_user.whse:
raise Exception("Invalid from_whse!")
if not row['quantity']:
raise Exception("Quantity is less than 1.")
# query first the quantity of inventory
whseinv = WhseInv.query.filter_by(
warehouse=row['from_whse'],
item_code=row['item_code']).first()
# if below quantity raise an error!
if row['quantity'] > whseinv.quantity:
raise Exception("Below quantity stock!")
# table row
t_r = TransferRow(transfer_id=t_h.id,
transnumber=t_h.transnumber,
created_by=curr_user.id,
updated_by=curr_user.id,
sap_number=t_h.sap_number,
objtype=t_h.objtype,
**row)
db.session.add(t_r)
db.session.flush()
db.session.commit()
trans_schema = TransferHeaderSchema()
result = trans_schema.dump(t_h)
return ResponseMessage(True,
message="Successfully added!",
data=result).resp()
except Exception as err:
db.session.rollback()
return ResponseMessage(False, message=f"{err}").resp(), 500
except (exc.IntegrityError, pyodbc.IntegrityError) as err:
db.session.rollback()
return ResponseMessage(False, message=f"{err}").resp(), 500
finally:
db.session.close()
def create_po_req(curr_user):
if not curr_user.is_admin() and not curr_user.is_allow_pullout():
return ResponseMessage(False, message="Unauthorized user!").resp(), 401
whse = Warehouses.query.filter_by(whsecode=curr_user.whse).first()
# if whse.is_cutoff():
# return ResponseMessage(False, message="Cutoff is enable, please disable it!").resp(), 401
if not whse.is_cutoff():
return ResponseMessage(False, message="Cutoff is disable").resp(), 401
date = request.args.get('date')
if request.method == 'GET':
try:
if not curr_user.is_manager():
whse_inv_case = case([(WhseInv.quantity != 0, 1)], else_=0)
whse_inv = db.session.query(
WhseInv.item_code, WhseInv.item_code, WhseInv.quantity,
Items.uom).filter(
WhseInv.warehouse == curr_user.whse).outerjoin(
Items,
Items.item_code == WhseInv.item_code).order_by(
whse_inv_case.desc(), WhseInv.item_code).all()
whseinv_schema = WhseInvSchema(many=True)
result = whseinv_schema.dump(whse_inv)
return ResponseMessage(True, data=result).resp()
elif curr_user.is_manager():
po_req_header = PullOutHeaderRequest
po_req_row = PullOutRowRequest
sales_case = case([(po_req_header.user_type == 'sales',
po_req_row.quantity)])
auditor_case = case([(po_req_header.user_type == 'auditor',
po_req_row.quantity)])
pull_out = db.session.query(
po_req_row.item_code,
func.sum(func.isnull(sales_case, 0)).label('sales_count'),
func.sum(func.isnull(auditor_case,
0)).label('auditor_count'),
func.sum(
func.isnull(sales_case, 0) -
func.isnull(auditor_case, 0)).label('variance'),
po_req_row.uom).filter(
and_(
cast(po_req_header.transdate, DATE) == date,
po_req_row.whsecode == curr_user.whse,
po_req_header.id == po_req_row.pulloutreq_id,
False == po_req_header.confirm)
).group_by(po_req_row.item_code, po_req_row.uom).having(
func.sum(
func.isnull(sales_case, 0) -
func.isnull(auditor_case, 0)) != 0).all()
po_req_schema = PullOutHeaderRequestSchema(many=True)
result = po_req_schema.dump(pull_out)
return ResponseMessage(True, data=result).resp()
except (pyodbc.IntegrityError, exc.IntegrityError) as err:
return ResponseMessage(False, message=f"{err}").resp(), 500
except Exception as err:
return ResponseMessage(False, message=f"{err}").resp(), 500
finally:
db.session.close()
elif request.method == 'POST':
try:
# query the whse and check if the cutoff is true
data = request.get_json()
header = data['header']
rows = data['rows']
# add to headers
header['created_by'] = curr_user.id
header['updated_by'] = curr_user.id
if curr_user.is_manager():
header['user_type'] = 'manager'
elif curr_user.is_auditor():
header['user_type'] = 'auditor'
elif curr_user.is_sales() and not curr_user.is_manager():
header['user_type'] = 'sales'
pending_req_po = PullOutHeaderRequest.query.filter(
and_(
PullOutHeaderRequest.user_type == header['user_type'],
func.cast(PullOutHeaderRequest.transdate,
DATE) == header['transdate'],
PullOutHeaderRequest.docstatus == 'O',
PullOutHeaderRequest.confirm == False)).first()
if pending_req_po:
return ResponseMessage(
False, message=f"You have an entry that still pending!"
).resp(), 401
# query the object type
obj = ObjectType.query.filter_by(code='PORQ').first()
# Check if has objtype
if not obj:
return ResponseMessage(
False, message="Object type not found!").resp(), 401
# query the series
series = Series.query.filter_by(whsecode=curr_user.whse,
objtype=obj.objtype).first()
# check if has series
if not series:
return ResponseMessage(False,
message="Series not found!").resp(), 401
# check if next num is not greater done end num
if series.next_num + 1 > series.end_num:
return ResponseMessage(
False, message="Series number is greater than next num!"
).resp(), 401
# construct reference
reference = f"{series.code}-{obj.code}-{series.next_num}"
# add to header
header['series'] = series.id
header['objtype'] = obj.objtype
header['seriescode'] = series.code
header['transnumber'] = series.next_num
header['reference'] = reference
# add 1 to next series
series.next_num += 1
po_req_header = PullOutHeaderRequest(**header)
db.session.add_all([series, po_req_header])
db.session.flush()
for row in rows:
# query the stock inventory
whse_inv = WhseInv.query.filter_by(
warehouse=curr_user.whse,
item_code=row['item_code']).first()
# check if the whse inv is less than the quantity to pullout
# if true then raise an error.
if whse_inv.quantity < row['quantity']:
raise Exception(
f"{row['item_code'].title()} below stock level!")
# add to row
row['whsecode'] = curr_user.whse
row['objtype'] = po_req_header.objtype
row['created_by'] = po_req_header.created_by
row['updated_by'] = po_req_header.updated_by
check = Check(**row)
# check if valid
if not check.itemcode_exist():
raise Exception("Invalid item code!")
if not check.uom_exist():
raise Exception("Invalid uom!")
po_req_row = PullOutRowRequest(pulloutreq_id=po_req_header.id,
**row)
db.session.add(po_req_row)
db.session.commit()
return ResponseMessage(True, message="Successfully added!").resp()
except (pyodbc.IntegrityError, exc.IntegrityError) as err:
db.session.rollback()
return ResponseMessage(False, message=f"{err}").resp(), 500
except Exception as err:
db.session.rollback()
return ResponseMessage(False, message=f"{err}").resp(), 500
finally:
db.session.close()
def new_sales(curr_user):
# check if user sales is true
if not curr_user.is_sales():
return ResponseMessage(False, message="Unauthorized user!").resp(), 401
# query the whse and check if cutoff is enable
whse = Warehouses.query.filter_by(whsecode=curr_user.whse).first()
if whse.is_cutoff():
return ResponseMessage(
False, message="Your warehouse cutoff is enable!").resp(), 401
# get the json data from request body
data = request.get_json()
# get the all details from data key rows
details = data['rows']
# check if has transdate and convert to datetime object
if data['header']['transdate']:
data['header']['transdate'] = datetime.strptime(
data['header']['transdate'], '%Y/%m/%d %H:%M')
# add to header dictionary
data['header']['created_by'] = curr_user.id
data['header']['updated_by'] = curr_user.id
try:
obj = ObjectType.query.filter_by(code='SLES').first()
if not obj:
raise Exception("Invalid object type!")
series = Series.query.filter_by(whsecode=curr_user.whse,
objtype=obj.objtype).first()
if not series:
raise Exception("Invalid series!")
if data['header']['transtype'].upper() == 'CASH':
cust = db.session.query(Customer). \
filter(and_(Customer.whse == curr_user.whse, Customer.code.contains('Cash'))).first()
data['header']['cust_code'] = cust.code
data['header']['cust_name'] = cust.name
if data['header']['transtype'].upper() == 'AGENT AR SALES':
cust = db.session.query(Customer). \
filter(and_(Customer.whse == curr_user.whse, Customer.code.contains('AR Sales'))).first()
data['header']['cust_code'] = cust.code
data['header']['cust_name'] = cust.name
# check if the header has discount and user is allowed to add discount
if data['header']['discprcnt'] and not curr_user.can_discount():
raise Exception("You're not allowed to add discount!")
if not Customer.query.filter_by(
code=data['header']['cust_code']).first():
raise Exception("Invalid Customer Code")
# add 1 to series
series.next_num += 1
sales = SalesHeader(**data['header'])
sales.seriescode = series.code
sales.transnumber = series.next_num
sales.reference = f"{series.code}-{obj.code}-{series.next_num}"
sales.objtype = obj.objtype
db.session.add_all([series, sales])
db.session.flush()
for row in details:
row['whsecode'] = curr_user.whse
row['sales_id'] = sales.id
check = Check(**row)
if not check.itemcode_exist():
raise Exception("Invalid item code!")
elif not check.uom_exist():
raise Exception("Invalid uom!")
elif not check.whsecode_exist():
raise Exception("Invalid whsecode!")
# check if the row has discount and if user is allowed to add discount
if row['discprcnt'] and not curr_user.can_discount():
raise Exception(
"You're not allowed to add sales with discount!")
# query the inventory of warehouse
whseinv = WhseInv.query.filter_by(
item_code=row['item_code'], warehouse=row['whsecode']).first()
# check the quantity
if row['quantity'] > whseinv.quantity:
raise Exception(f"{row['item_code'].title()} below qty!")
if row['free']:
row['unit_price'] = 0
# add to row the computation
row['gross'] = row['unit_price'] * row['quantity']
row['disc_amount'] = row['gross'] * (row['discprcnt'] / 100 if
row['discprcnt'] else 0 / 100)
row['linetotal'] = row['gross'] - row['disc_amount']
s_r = SalesRow(**row)
sales.gross += float(s_r.gross)
sales.row_discount += s_r.disc_amount
db.session.add(s_r)
sales.disc_amount = sales.gross * (sales.discprcnt /
100) + sales.row_discount
sales.doctotal = sales.gross + sales.delfee - sales.disc_amount - sales.gc_amount
sales.amount_due = sales.doctotal
if sales.tenderamt > sales.amount_due:
sales.change = sales.tenderamt - sales.amount_due
db.session.commit()
sales_schema = SalesHeaderSchema()
result = sales_schema.dump(sales)
return ResponseMessage(True,
message="Successfully added!",
data=result).resp()
except (pyodbc.IntegrityError, exc.IntegrityError) as err:
db.session.rollback()
return ResponseMessage(False, message=f"{err}").resp(), 500
except Exception as err:
db.session.rollback()
return ResponseMessage(False, message=f"{err}").resp(), 500
finally:
db.session.close()
def create_inv_count(curr_user):
if not curr_user.is_admin() and not curr_user.is_allow_ending():
return ResponseMessage(False, message="Unauthorized user!").resp(), 401
whse = Warehouses.query.filter_by(whsecode=curr_user.whse).first()
if not whse.is_cutoff():
return ResponseMessage(False, message="Cutoff is disable").resp(), 401
date = request.args.get('date')
if request.method == 'GET':
try:
if not curr_user.is_manager():
whse_inv_case = case([(WhseInv.quantity != 0, 1)], else_=0)
inv = db.session.query(
WhseInv.item_code, WhseInv.item_code, WhseInv.quantity,
Items.uom).filter(
WhseInv.warehouse == curr_user.whse).outerjoin(
Items,
Items.item_code == WhseInv.item_code).order_by(
whse_inv_case.desc(), WhseInv.item_code).all()
inv_schema = WhseInvSchema(many=True)
result = inv_schema.dump(inv)
return ResponseMessage(True, data=result).resp()
elif curr_user.is_manager():
count_header = CountingInventoryHeader
count_row = CountingInventoryRow
sales_case = case([(count_header.user_type == 'sales',
count_row.actual_count)])
auditor_case = case([(count_header.user_type == 'auditor',
count_row.actual_count)])
inv = db.session.query(
count_row.item_code, WhseInv.quantity.label('quantity'),
func.sum(func.isnull(sales_case, 0)).label('sales_count'),
func.sum(func.isnull(auditor_case,
0)).label('auditor_count'),
func.sum(
func.isnull(sales_case, 0) -
func.isnull(auditor_case, 0)).label('variance'),
count_row.uom).outerjoin(
WhseInv,
and_(count_row.whsecode == WhseInv.warehouse,
WhseInv.item_code == count_row.item_code)).filter(
and_(
cast(count_header.transdate,
DATE) == date,
count_row.whsecode == curr_user.whse,
count_header.id == count_row.counting_id,
False == count_header.confirm)).group_by(
count_row.item_code, WhseInv.quantity,
count_row.uom).having(
func.sum(
func.isnull(sales_case, 0) -
func.isnull(auditor_case, 0))
!= 0).all()
inv_schema = CountingInventoryRowSchema(many=True)
result = inv_schema.dump(inv)
return ResponseMessage(True, data=result).resp()
except (pyodbc.IntegrityError, exc.IntegrityError) as err:
return ResponseMessage(False, message=f"{err}").resp(), 500
except Exception as err:
return ResponseMessage(False, message=f"{err}").resp(), 500
elif request.method == 'POST':
try:
# query the whse and check if the cutoff is true
data = request.get_json()
header = data['header']
rows = data['rows']
# add to headers
header['created_by'] = curr_user.id
header['updated_by'] = curr_user.id
if curr_user.is_manager():
header['user_type'] = 'manager'
elif curr_user.is_auditor():
header['user_type'] = 'auditor'
elif curr_user.is_sales() and not curr_user.is_manager():
header['user_type'] = 'sales'
if CountingInventoryHeader.query.filter(
and_(
CountingInventoryHeader.user_type ==
header['user_type'],
func.cast(CountingInventoryHeader.transdate,
DATE) == header['transdate'],
CountingInventoryHeader.docstatus == 'C',
False == CountingInventoryHeader.confirm)).first():
return ResponseMessage(
False,
message=f"You're already added ending inventory this day"
).resp(), 401
obj = ObjectType.query.filter_by(code='ICNT').first()
# Check if has objtype
if not obj:
return ResponseMessage(
False, message="Object type not found!").resp(), 401
# query the series
series = Series.query.filter_by(whsecode=curr_user.whse,
objtype=obj.objtype).first()
# check if has series
if not series:
return ResponseMessage(False,
message="Series not found!").resp(), 401
# check if next num is not greater done end num
if series.next_num + 1 > series.end_num:
return ResponseMessage(
False, message="Series number is greater than next num!"
).resp(), 401
# construct reference
reference = f"{series.code}-{obj.code}-{series.next_num}"
# add to header
header['series'] = series.id
header['objtype'] = obj.objtype
header['seriescode'] = series.code
header['transnumber'] = series.next_num
header['reference'] = reference
# add 1 to next series
series.next_num += 1
inv_count_header = CountingInventoryHeader(**header)
db.session.add_all([series, inv_count_header])
db.session.flush()
for row in rows:
row['whsecode'] = curr_user.whse
check = Check(**row)
# check if valid
if not check.itemcode_exist():
raise Exception("Invalid item code!")
inv_count_row = CountingInventoryRow(
counting_id=inv_count_header.id, **row)
inv_count_row.objtype = inv_count_header.objtype
inv_count_row.created_by = inv_count_header.created_by
inv_count_row.updated_by = inv_count_header.updated_by
db.session.add(inv_count_row)
db.session.commit()
return ResponseMessage(True, message="Successfully added!").resp()
except (pyodbc.IntegrityError, exc.IntegrityError) as err:
db.session.rollback()
return ResponseMessage(False, message=f"{err}").resp(), 500
except Exception as err:
db.session.rollback()
return ResponseMessage(False, message=f"{err}").resp(), 500
finally:
db.session.close()
def create_item_request(curr_user):
try:
data = request.get_json()
header = data['header']
details = data['rows']
obj = ObjectType.query.filter_by(code='REQT').first()
series = Series.query.filter_by(whsecode=curr_user.whse,
objtype=obj.objtype).first()
if series.next_num + 1 > series.end_num:
raise Exception("Series number already in max!")
if not series:
raise Exception("Invalid Series")
reference = f"{series.code}-{obj.code}-{series.next_num}"
req_header = ItemRequest(series=series.id,
seriescode=series.code,
transnumber=series.next_num,
reference=reference,
objtype=obj.objtype,
**header)
req_header.created_by = curr_user.id
req_header.updated_by = curr_user.id
# add 1 to next num series
series.next_num += 1
db.session.add_all([req_header, series])
db.session.flush()
for row in details:
# add user whse to row
row['to_whse'] = curr_user.whse
check = Check(**row)
# check if valid
if not check.itemcode_exist():
raise Exception("Invalid item code!")
elif not check.uom_exist():
raise Exception("Invalid uom!")
elif not check.fromwhse_exist():
raise Exception("Invalid from whse code!")
elif not check.towhse_exist():
raise Exception("Invalid to whse code!")
req_row = ItemRequestRow(request_id=req_header.id,
objtype=req_header.objtype,
created_by=req_header.created_by,
updated_by=req_header.updated_by,
**row)
db.session.add(req_row)
db.session.commit()
request_schema = ItemRequestSchema()
result = request_schema.dump(req_header)
return ResponseMessage(True,
message="Successfully added!",
data=result).resp()
except (pyodbc.IntegrityError, exc.IntegrityError) as err:
return ResponseMessage(False, message=f"{err}").resp(), 500
except Exception as err:
return ResponseMessage(False, message=f"{err}").resp(), 500