def get_sales_for_payment(curr_user):
if not curr_user.is_cashier():
return ResponseMessage(False, message="Unauthorized user!").resp(), 401
try:
ids = request.args.get('ids').rstrip(']').lstrip('[')
ids = list(ids.split(','))
filt_sales_h = [('id', 'in', ids)]
filt_sales_row = [('sales_id', 'in', ids)]
sales_h_filter = BaseQuery.create_query_filter(
SalesHeader, filters={'and': filt_sales_h})
sales_r_filter = BaseQuery.create_query_filter(
SalesRow, filters={'and': filt_sales_row})
# sales header query
sales_h_query = db.session.query(func.sum(SalesHeader.delfee).label('delfee'),
func.sum(SalesHeader.disc_amount).label('disc_amount'),
func.sum(SalesHeader.gross).label('gross'),
func.sum(SalesHeader.doctotal).label('doctotal'),
func.sum(SalesHeader.tenderamt).label('tenderamt'),
func.sum(SalesHeader.change).label('change'),
func.sum(SalesHeader.amount_due).label('amount_due')) \
.filter(*sales_h_filter) \
.first()
# sales row query
sales_r_query = db.session.query(
SalesRow.item_code,
SalesRow.unit_price,
SalesRow.discprcnt,
SalesRow.free,
func.sum(SalesRow.quantity).label('quantity'),
func.sum(SalesRow.disc_amount).label('disc_amount'),
func.sum(SalesRow.gross).label('gross'),
func.sum(SalesRow.linetotal).label('linetotal')) \
.filter(*sales_r_filter) \
.group_by(SalesRow.item_code, SalesRow.unit_price, SalesRow.discprcnt, SalesRow.free) \
.all()
sales_h_schema = SalesHeaderSchema()
sales_r_schema = SalesRowSchema(many=True)
result_header = sales_h_schema.dump(sales_h_query)
result_row = sales_r_schema.dump(sales_r_query)
return ResponseMessage(True,
count=len(result_row),
data={
"header": result_header,
"row": result_row
}).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
def get_all_item_request(curr_user):
try:
data = request.args.to_dict()
header_filt = []
row_filt = []
duedate = ''
for k, v in data.items():
if k in ['from_whse', 'to_whse'] and v:
row_filt.append((k, "==", v))
elif k == 'sap_number':
if v:
header_filt.append((k, "==", v))
else:
header_filt.append((k, "==", None))
elif k == 'confirm':
if not v:
header_filt.append((k, "==", None))
else:
header_filt.append((k, "==", int(v)))
elif 'duedate' == '':
if v:
duedate = v
else:
if v:
header_filt.append((k, "==", v))
# print(header_filt, row_filt)
req_header_filter = BaseQuery.create_query_filter(
ItemRequest, filters={'and': header_filt})
req_row_filter = BaseQuery.create_query_filter(
ItemRequestRow, filters={'and': row_filt})
if duedate:
item_req = db.session.query(ItemRequest). \
filter(and_(cast(ItemRequest.transdate, DATE) == duedate,
*req_header_filter,
*req_row_filter)).all()
else:
item_req = db.session.query(ItemRequest). \
filter(and_(*req_header_filter,
*req_row_filter)).all()
request_schema = ItemRequestSchema(
many=True,
exclude=("date_created", "date_updated", "created_by",
"updated_by"))
result = request_schema.dump(item_req)
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
def ar_confirm(curr_user):
if not curr_user.is_cashier() and not curr_user.is_admin(
) and not curr_user.is_manager():
return ResponseMessage(False, message="Unauthorized user!").resp(), 401
try:
ids = request.args.get('ids').rstrip(']').lstrip('[')
ids = list(ids.split(','))
filt_sales_h = [('id', 'in', ids)]
sales_filt = BaseQuery.create_query_filter(
SalesHeader, filters={'and': filt_sales_h})
sales = SalesHeader.query.filter(*sales_filt).all()
success_ref = []
unsuccess_ref = []
for sale in sales:
if sale.confirm:
unsuccess_ref.append(sale.reference)
continue
sale.confirm = True
sale.confirm_by = curr_user.id
sale.date_confirm = datetime.now()
success_ref.append(sale.reference)
db.session.commit()
success_message = f"Confirm success transaction: {str(success_ref).rstrip(']').lstrip('[')}"
unsuccess_message = f"Confirm unsuccess transaction: {str(unsuccess_ref).rstrip(']').lstrip('[')}"
return ResponseMessage(True, message=f"{success_message}" if len(success_ref) > 0 else '' \
f" ,{unsuccess_message}" if len(unsuccess_ref) > 0 else '' + '.').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
def get_all_deposit(curr_user):
cust = request.args.get('customer')
status = request.args.get('status')
filt = []
if cust:
filt.append(('cust_code', 'like', f'%{cust}%'))
if status:
filt.append(('status', '==', status))
try:
# generate obj for query
dep_filter = BaseQuery.create_query_filter(Deposit,
filters={'and': filt})
dep = db.session.query(Deposit).filter(*dep_filter).all()
dep_schema = DepositSchema(many=True,
exclude=("date_created", "date_updated",
"created_by", "updated_by"))
result = dep_schema.dump(dep)
return ResponseMessage(True, count=len(result), 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
def get_all_users(curr_user):
# contains to filter
filt = []
data = request.args.to_dict()
for k, v in data.items():
if k == 'search':
filt.append(('username', 'like', f'%{v}%'))
elif k == 'branch':
if v:
filt.append((k, '==', v))
elif k == 'whse':
if v:
filt.append((k, '==', v))
else:
filt.append((k, '==', bool(int(v))))
try:
user_filter = BaseQuery.create_query_filter(User, filters={'and': filt})
user = db.session.query(User).filter(*user_filter). \
order_by(User.fullname.asc()).all()
user_schema = UserSchema(many=True, only=("id", "username", "fullname",))
result = user_schema.dump(user)
return ResponseMessage(True, count=len(result), 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
def get_all_sales_type(curr_user):
try:
data = request.args.to_dict()
filt = []
filt_in = []
for k, v in data.items():
filt.append((k, '==', v))
if curr_user.is_sales():
if curr_user.is_ar_sales():
filt_in.append('AR Sales')
if curr_user.is_cash_sales():
filt_in.append('CASH')
if curr_user.is_agent_sales():
filt_in.append('Agent AR Sales')
if not curr_user.is_admin() and not curr_user.is_manager():
filt.append(('code', 'in', filt_in))
sales_filter = BaseQuery.create_query_filter(SalesType,
filters={'and': filt})
stype = db.session.query(SalesType).filter(*sales_filter).all()
stype_schema = SalesTypeSchema(many=True)
result = stype_schema.dump(stype)
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
def get_all_whse(curr_user):
try:
data = request.args.to_dict()
filt = []
if 'transtype' in data:
transtype = data['transtype']
if transtype.upper() == 'TRFR':
filt.append(('whsecode', '!=', curr_user.whse))
if 'branch' in data:
filt.append(('branch', '==', data['branch']))
if 'whsecode' in data:
filt.append(('whsecode', '==', data['whsecode']))
branch_filter = BaseQuery.create_query_filter(Warehouses,
filters={'and_': filt})
warehouses = db.session.query(Warehouses).filter(*branch_filter).all()
whse_schema = WarehouseSchema(many=True)
result = whse_schema.dump(warehouses)
return ResponseMessage(True, data=result).resp()
except (pyodbc.IntegrityError, IntegrityError) as err:
return ResponseMessage(False, message=f"{err}").resp(), 500
except Exception as err:
return ResponseMessage(False, message=f"{err}").resp(), 500
def get_all_po(curr_user):
try:
data = request.args.to_dict()
transdate = ''
whse_filt = []
header_filt = []
for k, v in data.items():
if k == 'transdate' and v:
transdate = v
elif k == 'branch' and v:
whse_filt.append((k, "==", v))
elif k == 'whsecode' and v:
whse_filt.append((k, "==", v))
else:
if v:
header_filt.append((k, "==", v))
whse_filters = BaseQuery.create_query_filter(
Warehouses, filters={"and": whse_filt})
po_filters = BaseQuery.create_query_filter(
PullOutHeader, filters={"and": header_filt})
if transdate:
po_filters.append((cast(PullOutHeader.transdate,
DATE) == transdate))
pullout = db.session.query(PullOutHeader).\
select_from(PullOutHeader).\
join(PullOutRow, PullOutRow.pullout_id == PullOutHeader.id).\
join(Warehouses, Warehouses.whsecode == PullOutRow.whsecode).\
filter(*whse_filters, *po_filters)
po_schema = PullOutHeaderSchema(many=True, exclude=("row", ))
result = po_schema.dump(pullout)
return ResponseMessage(True, count=len(result), 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()
def ar_for_confirm(curr_user):
if not curr_user.is_manager() and not curr_user.is_cashier(
) and not curr_user.is_admin():
return ResponseMessage(False, message="Unauthorized user!").resp(), 401
try:
filt = []
transnum = request.args.get('transnum')
user_id = request.args.get('user_id')
transtype = request.args.get('transtype')
date_created = request.args.get('date_created')
if transnum:
filt.append(('transnumber', 'like', f'%{transnum}%'))
if user_id:
filt.append(('created_by', '==', user_id))
if transtype:
filt.append(('transtype', '==', transtype))
filt.append(('docstatus', '==', 'O'))
sales_filter = BaseQuery.create_query_filter(SalesHeader,
filters={'and': filt})
if date_created:
sales = db.session.query(SalesHeader).join(SalesRow). \
join(Warehouses, Warehouses.whsecode == SalesRow.whsecode).filter(and_(Warehouses.branch == curr_user.branch,
func.cast(SalesHeader.date_created, DATE) == date_created,
and_(SalesHeader.confirm != True, SalesHeader.transtype !='CASH'),
*sales_filter)).all()
else:
sales = db.session.query(SalesHeader).join(SalesRow). \
join(Warehouses, Warehouses.whsecode == SalesRow.whsecode)\
.filter(and_(Warehouses.branch == curr_user.branch,
and_(SalesHeader.confirm != True, SalesHeader.transtype !='CASH'),
*sales_filter)).all()
sales_schema = SalesHeaderSchema(
many=True,
only=("id", "docstatus", "seriescode", "transnumber", "reference",
"transdate", "cust_code", "cust_name", "objtype", "remarks",
"transtype", "delfee", "disctype", "discprcnt",
"disc_amount", "gross", "gc_amount", "doctotal",
"reference2", "tenderamt", "sap_number", "appliedamt",
"amount_due", "void", "created_user", "confirm"))
result = sales_schema.dump(sales)
return ResponseMessage(True, count=len(result), 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
def sales_update_sap(curr_user):
if not curr_user.is_can_add_sap() and not curr_user.is_manager() and not curr_user.is_admin():
return ResponseMessage(False, message="Unauthorized user!").resp(), 401
try:
filt = []
date = ''
branch = ''
data = request.args.to_dict()
for k, v in data.items():
if k == 'date':
date = v
continue
if k == 'branch':
branch = v
continue
if k == 'cust_code':
filt.append((k, 'like', f'%{v}%'))
elif v:
filt.append((k, '==', v))
sales_filt = BaseQuery.create_query_filter(SalesHeader, filters={'and': filt})
sales = db.session.query(SalesHeader).filter(and_(
func.cast(SalesHeader.date_created, DATE) == date,
SalesHeader.confirm == True,
SalesHeader.sap_number == None,
SalesHeader.docstatus != 'N',
Warehouses.branch == branch,
*sales_filt)
).outerjoin(
SalesRow, SalesHeader.id == SalesRow.sales_id
).outerjoin(
Warehouses, SalesRow.whsecode == Warehouses.whsecode
).all()
sales_schema = SalesHeaderSchema(many=True, only=("id", "docstatus", "seriescode",
"transnumber", "reference", "transdate", "cust_code",
"cust_name", "objtype", "remarks", "transtype", "delfee",
"disctype", "discprcnt", "disc_amount", "gross",
"gc_amount", "doctotal", "reference2", "tenderamt",
"sap_number", "appliedamt", "amount_due", "void"))
result = sales_schema.dump(sales)
return ResponseMessage(True, count=len(result), 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
def get_all_cashout(curr_user):
try:
transnum = request.args.get('transnum')
filt = []
if transnum:
filt.append(('trans_num', 'like', f'%{transnum}%'))
query_filter = BaseQuery.create_query_filter(CashTransaction,
filters={'and': filt})
cash_out = CashTransaction.query.filter(*query_filter).all()
cash_out_schema = CashTransactionSchema(many=True)
result = cash_out_schema.dump(cash_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
def sales_void(curr_user):
remarks = request.args.get('remarks')
if not curr_user.can_void() or not curr_user.is_admin():
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
try:
ids = request.args.get('ids').rstrip(']').lstrip('[')
ids = list(ids.split(','))
filt_sales_h = [('id', 'in', ids)]
sales_filt = BaseQuery.create_query_filter(
SalesHeader, filters={'and': filt_sales_h})
sales = SalesHeader.query.filter(*sales_filt).all()
success_ref = []
unsuccess_ref = []
for sale in sales:
if sale.docstatus == 'C':
unsuccess_ref.append(sale.id)
continue
if sale.docstatus == 'N':
unsuccess_ref.append(sale.reference)
continue
sale.void = True
sale.docstatus = 'N'
sale.updated_by = curr_user.id
sale.date_updated = datetime.now()
if remarks:
sale.remarks = remarks
success_ref.append(sale.reference)
db.session.commit()
success_message = f"Void success transaction: {str(success_ref).rstrip(']').lstrip('[')}"
unsuccess_message = f"Void unsuccess transaction: {str(unsuccess_ref).rstrip(']').lstrip('[')}"
return ResponseMessage(True, message=f"{success_message}" if len(success_ref) > 0 else '' \
f" ,{unsuccess_message}" if len(unsuccess_ref) > 0 else '' + '.').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
def update_sales_sap_num(curr_user):
try:
data = request.get_json()
sap_number = data['sap_number']
remarks = data['remarks']
ids = data['ids']
filt_sales_h = [('id', 'in', ids)]
sales_filt = BaseQuery.create_query_filter(SalesHeader, filters={'and': filt_sales_h})
sales = SalesHeader.query.filter(*sales_filt).all()
count = 0
for sale in sales:
sale.sap_number = sap_number
sale.remarks = sap_number
count += 1
db.session.commit()
return ResponseMessage(True, message=f"{count} sales transaction updated!").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
def get_all_price_list_row(curr_user):
if not curr_user.is_admin() and not curr_user.is_manager():
return ResponseMessage(False, message="Unauthorized user!").resp(), 401
data = request.args.to_dict()
filt = []
for k, v in data.items():
filt.append((k, "==", v))
try:
row_filters = BaseQuery.create_query_filter(PriceListRow,
filters={"and": filt})
price_row = PriceListRow.query.filter(*row_filters).all()
price_row_schema = PriceListRowSchema(many=True)
result = price_row_schema.dump(price_row)
return ResponseMessage(True, count=len(result), data=result).resp()
except (exc.IntegrityError, pyodbc.IntegrityError) as err:
return ResponseMessage(False, message=f"{err}").resp(), 500
except Exception as err:
return ResponseMessage(False, message=f"{err}").resp(), 500
def get_all_po(curr_user):
try:
data = request.args.to_dict()
filt = []
for k, v in data.items():
if v:
filt.append((k, '==', v))
filters = BaseQuery.create_query_filter(POHeader, filters={"and": filt})
sap_po = db.session.query(POHeader).\
filter(and_(*filters, PORow.whscode == curr_user.whse,
POHeader.docstatus == 'O'))
header_schema = POheaderSchema(many=True)
result = header_schema.dump(sap_po)
return ResponseMessage(True, count=len(result), data=result).resp()
except (pyodbc.IntegrityError, exc.IntegrityError) as err:
return ResponseMessage(True, message=f"{err}").resp(), 500
except Exception as err:
return ResponseMessage(True, message=f"{err}").resp(), 500
def get_all_discount_type(curr_user):
try:
q = request.args.get('search')
filt = []
if q:
filt.append(('code', 'like', f'%{q}%'))
query_filter = BaseQuery.create_query_filter(DiscountType,
filters={'and': filt})
query = DiscountType.query.filter(*query_filter).all()
disc_type_schema = DiscountTypeSchema(many=True,
only=("id", "code",
"description", "discount"))
result = disc_type_schema.dump(query)
return ResponseMessage(True,
message=f"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
def get_all_customer(curr_user):
try:
data = request.args.to_dict()
if 'transtype' in data:
if data['transtype'].upper() == 'SALES':
filt = []
filt_cust_type = []
# add to filter list if the user is allow to cash sales
if curr_user.is_sales() and curr_user.is_cash_sales(
) and not curr_user.is_ar_sales():
filt.append(('whse', '==', curr_user.whse))
filt_cust_type.append(3) # Customer Type Cash Sales
# add to filter list if the user is allow to agent sales
if curr_user.is_sales() and curr_user.is_agent_sales(
) and not curr_user.is_ar_sales():
filt.append(('whse', '==', curr_user.whse))
filt_cust_type.append(4) # Customer Type Agent AR Sales
# add to filter list if the user is allow to ar sales
if curr_user.is_sales() and curr_user.is_ar_sales():
filt_cust_type.append(1) # Customer Type Customers
if filt_cust_type:
filt.append(('cust_type', 'in', filt_cust_type))
cust_filter = BaseQuery.create_query_filter(
Customer, filters={'and': filt})
customers = db.session.query(Customer).filter(
*cust_filter).all()
else:
customers = db.session.query(Customer).all()
cust_schema = CustomerSchema(many=True)
result = cust_schema.dump(customers)
return ResponseMessage(True, count=len(result), 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
def get_all_pricelist(curr_user):
if not curr_user.is_admin() and not curr_user.is_manager():
return ResponseMessage(False, message="Unauthorized user!").resp(), 401
try:
filt = []
for k, v in request.args.to_dict().items():
filt.append((k, "like", f'%{v}%'))
list_filters = BaseQuery.create_query_filter(PriceListHeader,
filters={"and": filt})
price_list = PriceListHeader.query.filter(*list_filters).all()
price_schema = PriceListHeaderSchema(many=True,
only=(
"id",
"code",
"description",
))
result = price_schema.dump(price_list)
return ResponseMessage(True, count=len(result), 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
def get_all_transfer(curr_user):
try:
data = request.args.to_dict()
date = ''
user_filt = []
filt = []
whse_filt = []
row_filt = []
for k, v in data.items():
if k == 'transdate':
if v:
date = v
elif k == 'user':
if v:
user_filt.append(('id', 'like', v))
elif 'sap_number' == k:
if v:
filt.append((k, "==", v))
else:
filt.append((k, "==", None))
elif 'branch' == k and v:
whse_filt.append((k, "==", v))
elif 'from_whse' == k and v:
row_filt.append((k, "==", v))
elif 'to_whse' == k and v:
row_filt.append((k, "==", v))
else:
if v:
filt.append((k, "==", v))
user_filters = BaseQuery.create_query_filter(
User, filters={'and': user_filt})
row_filters = BaseQuery.create_query_filter(TransferRow,
filters={'and': row_filt})
trans_filters = BaseQuery.create_query_filter(TransferHeader,
filters={'and': filt})
whse_filters = BaseQuery.create_query_filter(
Warehouses, filters={"and": whse_filt})
if date:
trans_filters.append((func.cast(TransferHeader.transdate,
DATE) == date))
transfer = db.session.query(TransferHeader).\
select_from(TransferHeader).\
join(TransferRow, TransferRow.transfer_id == TransferHeader.id).\
join(Warehouses, Warehouses.whsecode == TransferRow.to_whse).\
outerjoin(User, TransferHeader.created_by == User.id).\
filter(and_(
*trans_filters,
*user_filters,
*row_filters,
*whse_filters)).all()
trans_schema = TransferHeaderSchema(
many=True,
only=("id", "transnumber", "sap_number", "transdate", "remarks",
"docstatus", "reference"))
result = trans_schema.dump(transfer)
return ResponseMessage(True, data=result).resp()
except (pyodbc.IntegrityError, exc.IntegrityError) as err:
return ResponseMessage(True, message=f'{err}').resp(), 500
except Exception as err:
return ResponseMessage(True, message=f'{err}').resp(), 500
finally:
db.session.close()
def get_all_recv(curr_user):
try:
data = request.args.to_dict()
date = ''
filt = []
row_filt = []
whse_filt = []
for k, v in data.items():
if k == 'transdate':
if v:
date = v
elif 'sap_number' == k:
if not v:
filt.append((k, '==', None))
elif v:
filt.append((k, '==', v))
elif 'branch' == k and v:
whse_filt.append((k, "==", v))
elif 'from_whse' == k and v:
row_filt.append((k, "==", v))
elif 'to_whse' == k and v:
row_filt.append((k, "==", v))
else:
if v:
filt.append((k, "==", v))
rec_filters = BaseQuery.create_query_filter(ReceiveHeader,
filters={'and': filt})
row_filters = BaseQuery.create_query_filter(ReceiveRow,
filters={'and': row_filt})
whse_filters = BaseQuery.create_query_filter(
Warehouses, filters={"and": whse_filt})
if date:
rec_filters.append((func.cast(ReceiveHeader.transdate,
DATE) == date))
receive = db.session.query(ReceiveHeader).\
select_from(ReceiveHeader).\
join(ReceiveRow, ReceiveRow.receive_id == ReceiveHeader.id).\
join(Warehouses, ReceiveRow.to_whse == Warehouses.whsecode).\
filter(and_(*rec_filters,
*row_filters,
*whse_filters)).\
all()
recv_schema = ReceiveHeaderSchema(
many=True,
only=("id", "series", "seriescode", "transnumber", "sap_number",
"docstatus", "transtype", "transdate", "reference",
"reference2", "remarks"))
result = recv_schema.dump(receive)
return ResponseMessage(True, count=len(result), data=result).resp()
except (pyodbc.IntegrityError, exc.IntegrityError) as err:
return ResponseMessage(True, message=f'{err}').resp(), 500
except Exception as err:
return ResponseMessage(True, message=f'{err}').resp(), 500
finally:
db.session.close()
def final_report(curr_user):
try:
data = request.args.to_dict()
transdate = ''
user_filt = [
('branch', '==', curr_user.branch),
]
cash_filt = []
sales_filt = []
for k, v in data.items():
if k == 'transdate':
if v:
transdate = v
elif k == 'whsecode':
if not curr_user.is_admin():
sales_filt.append(('whsecode', "==", curr_user.whse))
else:
sales_filt.append(('whsecode', "==", v))
else:
if v:
sales_filt.append((k, "==", v))
# check first there's a order need to confirm
pending_order = SalesHeader.query.filter(and_(cast(SalesHeader.transdate, DATE) == transdate,
SalesHeader.confirm == False,
SalesHeader.docstatus != 'N')).first()
if pending_order:
raise Exception(f"Confirm first the transaction {pending_order.reference}")
user_filters = BaseQuery.create_query_filter(User, filters={'and': user_filt})
sales_filters = BaseQuery.create_query_filter(SalesHeader, filters={'and': sales_filt})
# Payment Cases
cash_trans = CashTransaction
pay_trans = PayTransHeader
sales_trans = SalesHeader
cash_on_hand_case = case([(cash_trans.transtype.in_(['CASH', 'DEPS']), cash_trans.amount)])
cash_sales_case = case([(and_(cash_trans.transtype == 'CASH',
sales_trans.transtype == 'CASH'), cash_trans.amount)])
ar_cash_case = case([(and_(cash_trans.transtype == 'CASH',
sales_trans.transtype == 'AR Sales'), cash_trans.amount)])
ar_agent_cash_case = case([(and_(cash_trans.transtype == 'CASH',
sales_trans.transtype == 'Agent AR Sales'), cash_trans.amount)])
deposit_case = case([(cash_trans.transtype == 'DEPS', cash_trans.amount)])
used_dep_case = case([(cash_trans.transtype == 'FDEPS', cash_trans.amount)])
bank_dep_case = case([(cash_trans.transtype == 'BDEP', cash_trans.amount)])
epay_case = case([(cash_trans.transtype == 'EPAY', cash_trans.amount)])
gcert_case = case([(cash_trans.transtype == 'GCRT', cash_trans.amount)])
cash_header = db.session.query(
func.sum(cash_on_hand_case).label('total_cash_on_hand'),
func.sum(cash_sales_case).label('cash_sales'),
func.sum(ar_cash_case).label('ar_cash_sales'),
func.sum(ar_agent_cash_case).label('ar_agent_sales'),
func.sum(deposit_case).label('deposit'),
func.sum(used_dep_case).label('used_dep'),
func.sum(bank_dep_case).label('bank_dep'),
func.sum(epay_case).label('epay'),
func.sum(gcert_case).label('gcert')). \
select_from(cash_trans). \
join(User, cash_trans.created_by == User.id). \
outerjoin(pay_trans, pay_trans.id == cash_trans.trans_id). \
outerjoin(sales_trans, pay_trans.base_id == sales_trans.id).\
filter(and_(cast(cash_trans.transdate, DATE) == transdate,
*user_filters
))
# Sales Type Cash Case
gross_cash_case = case([(SalesHeader.transtype == 'CASH', SalesHeader.gross)])
disc_cash_case = case([(SalesHeader.transtype == 'CASH', SalesHeader.disc_amount)])
net_cash_case = case([(SalesHeader.transtype == 'CASH', SalesHeader.doctotal)])
# Sales Type AR Sales Case
gross_ar_case = case([(SalesHeader.transtype == 'AR Sales', SalesHeader.gross)])
disc_ar_case = case([(SalesHeader.transtype == 'AR Sales', SalesHeader.disc_amount)])
net_ar_case = case([(SalesHeader.transtype == 'AR Sales', SalesHeader.doctotal)])
# Sales Type Agent AR Sales Case
gross_ar_agent_case = case([(SalesHeader.transtype == 'Agent AR Sales', SalesHeader.gross)])
disc_ar_agent_case = case([(SalesHeader.transtype == 'Agent AR Sales', SalesHeader.disc_amount)])
net_ar_agent_case = case([(SalesHeader.transtype == 'Agent AR Sales', SalesHeader.doctotal)])
# Sales Total and Transaction
sales_header = db.session.query(
func.sum(SalesHeader.gross).label('gross'),
func.sum(SalesHeader.gross).label('net_sales'),
func.sum(SalesHeader.disc_amount).label('disc_amount'),
func.sum(gross_cash_case).label('gross_cash_sales'),
func.sum(disc_cash_case).label('disc_cash_sales'),
func.sum(net_cash_case).label('net_cash_sales'),
func.sum(gross_ar_case).label('gross_ar_sales'),
func.sum(disc_ar_case).label('disc_ar_sales'),
func.sum(net_ar_case).label('net_ar_sales'),
func.sum(gross_ar_agent_case).label('gross_agent_sales'),
func.sum(disc_ar_agent_case).label('disc_agent_sales'),
func.sum(net_ar_agent_case).label('net_agent_sales'))\
.outerjoin(User, SalesHeader.created_by == User.id)\
.filter(and_(
func.cast(SalesHeader.transdate, DATE) == transdate,
SalesHeader.confirm == True,
SalesHeader.docstatus != 'N',
*sales_filters,
*user_filters
))
# Item Variance Count
fc_header = FinalInvCount
fc_row = FinalInvCountRow
variance = (fc_row.ending_final_count + fc_row.po_final_count - fc_row.quantity)
final_count_query = db.session.query(
fc_row.item_code,
fc_row.ending_final_count.label('actual_good'),
fc_row.po_final_count.label('actual_pullout'),
fc_row.quantity.label('system_bal'),
variance.label('variance'),
PriceListRow.price,
(PriceListRow.price * variance).label('total_amount')
). \
select_from(fc_header). \
join(fc_row, fc_row.finalcount_id == fc_header.id). \
outerjoin(Warehouses, fc_row.whsecode == Warehouses.whsecode). \
outerjoin(PriceListRow, and_(PriceListRow.pricelist_id == Warehouses.pricelist,
PriceListRow.item_code == fc_row.item_code)). \
filter(cast(fc_header.transdate, DATE) == transdate)
# Payment Method Summary
cash_case = case([(sales_trans.transtype == 'CASH', cash_trans.amount)])
ar_case = case([(sales_trans.transtype == 'AR Sales', cash_trans.amount)])
agent_case = case([(sales_trans.transtype == 'Agent AR Sales', cash_trans.amount)])
pay_method_query = db.session.query(
PaymentType.description.label('transtype'),
func.sum(func.isnull(cash_case, 0)).label('cash_sales'),
func.sum(func.isnull(ar_case, 0)).label('ar_sales'),
func.sum(func.isnull(agent_case, 0)).label('agent_sales')
). \
select_from(cash_trans). \
join(User, cash_trans.created_by == User.id). \
outerjoin(pay_trans, pay_trans.id == cash_trans.trans_id). \
outerjoin(sales_trans, pay_trans.base_id == sales_trans.id).\
outerjoin(PaymentType, cash_trans.transtype == PaymentType.code). \
filter(and_(cast(cash_trans.transdate, DATE) == transdate,
*user_filters
)).\
group_by(PaymentType.description)
# sales_cash = db.session.query(
# func.cast(SalesHeader.transdate, DATE),
# func.sum(SalesHeader.gross).label('gross'),
# func.sum(SalesHeader.disc_amount).label('disc_amount'),
# func.sum(SalesHeader.doctotal).label('doctotal'))\
# .outerjoin(User, SalesHeader.created_by == User.id)\
# .filter(and_(
# func.cast(SalesHeader.transdate, DATE) >= transdate,
# SalesHeader.confirm,
# SalesHeader.docstatus != 'N',
# SalesHeader.transtype == 'CASH',
# *sales_filters,
# *user_filters))\
# .group_by(SalesHeader.transdate).all()
# sales_ar = db.session.query(
# SalesHeader.reference,
# SalesHeader.transdate,
# SalesHeader.cust_code,
# SalesHeader.gross,
# SalesHeader.disc_amount,
# SalesHeader.doctotal)\
# .outerjoin(User, SalesHeader.created_by == User.id)\
# .filter(and_(
# func.cast(SalesHeader.transdate, DATE) >= transdate,
# SalesHeader.confirm,
# SalesHeader.docstatus != 'N',
# SalesHeader.transtype == 'AR Sales',
# *sales_filters,
# *user_filters)).all()
# sales_ar_agent = db.session.query(
# func.cast(SalesHeader.transdate, DATE),
# func.sum(SalesHeader.gross).label('gross'),
# func.sum(SalesHeader.disc_amount).label('disc_amount'),
# func.sum(SalesHeader.doctotal).label('doctotal'))\
# .outerjoin(User, SalesHeader.created_by == User.id)\
# .filter(and_(
# func.cast(SalesHeader.transdate, DATE) >= transdate,
# SalesHeader.confirm,
# SalesHeader.docstatus != 'N',
# SalesHeader.transtype == 'Agent AR Sales',
# *sales_filters,
# *user_filters)\
# .group_by(SalesHeader.transdate)).all()
cash_schema = CashTransSchema(many=True)
cash_result = cash_schema.dump(cash_header)
sales_schema = SalesTransSchema(many=True)
sales_result = sales_schema.dump(sales_header)
final_inv_schema = FinalInvCountSchema(many=True)
final_inv_result = final_inv_schema.dump(final_count_query)
pay_method_schema = PaymentMethodSchema(many=True)
pay_method_result = pay_method_schema.dump(pay_method_query)
return ResponseMessage(True, data={'cash': cash_result,
'sales': sales_result,
'payment_method': pay_method_result,
'final_inv': final_inv_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()
def sales_report(curr_user):
try:
sale_filt = []
user_filt = [('branch', '==', curr_user.branch)]
date = ''
data = request.args.to_dict()
for k, v in data.items():
if k == 'date':
date = v
elif k == 'user_id':
if v:
user_filt.append(('id', '==', v))
elif k == 'whse':
if v:
user_filt.append((k, "==", v))
else:
if v:
sale_filt.append((k, '==', v))
sales_filters = BaseQuery.create_query_filter(SalesHeader, filters={'and': sale_filt})
user_filters = BaseQuery.create_query_filter(User, filters={'and': user_filt})
cash_case = case([(SalesHeader.transtype == 'CASH', SalesHeader.doctotal)])
ar_case = case([(SalesHeader.transtype == 'AR Sales', SalesHeader.doctotal)])
ar_agent_case = case([(SalesHeader.transtype == 'Agent AR Sales', SalesHeader.doctotal)])
sales_header = db.session.query(
func.sum(SalesHeader.gross).label('gross'),
func.sum(cash_case).label('cashsales'),
func.sum(ar_case).label('arsales'),
func.sum(ar_agent_case).label('agentsales'),
func.sum(SalesHeader.disc_amount).label('disc_amount')
).outerjoin(User, SalesHeader.created_by == User.id
).filter(
and_(func.cast(SalesHeader.transdate, DATE) == date,
SalesHeader.confirm,
SalesHeader.docstatus != 'N',
*sales_filters,
*user_filters
)
).first()
sales_row = db.session.query(
SalesHeader.id,
SalesHeader.transnumber,
SalesHeader.transdate,
SalesHeader.cust_code,
SalesHeader.reference,
SalesHeader.gross,
SalesHeader.doctotal,
SalesHeader.transtype,
User.username.label('user')
).outerjoin(User, SalesHeader.created_by == User.id
).filter(
and_(func.cast(SalesHeader.transdate, DATE) == date,
SalesHeader.confirm,
SalesHeader.docstatus != 'N',
*sales_filters,
*user_filters
)
).all()
header_schema = SalesHeaderSchema()
row_schema = SalesHeaderSchema(many=True, exclude=("salesrow",))
header_result = header_schema.dump(sales_header)
row_result = row_schema.dump(sales_row)
return ResponseMessage(True, data={"header": header_result, "row": row_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()
def payment_new(curr_user):
if not curr_user.is_cashier() and not curr_user.is_admin():
return ResponseMessage(False, message="Unauthorized user!").resp(), 401
if request.method == 'GET':
try:
filt = []
transnum = request.args.get('transnum')
user_id = request.args.get('user_id')
transtype = request.args.get('transtype')
date_created = request.args.get('date_created')
if transnum:
filt.append(('transnumber', 'like', f'%{transnum}%'))
if user_id:
filt.append(('created_by', '==', user_id))
if transtype:
filt.append(('transtype', '==', transtype))
filt.append(('docstatus', '==', 'O'))
sales_filter = BaseQuery.create_query_filter(SalesHeader,
filters={'and': filt})
if date_created:
sales = db.session.query(SalesHeader).join(SalesRow). \
join(Warehouses, Warehouses.whsecode == SalesRow.whsecode).filter(
and_(Warehouses.branch == curr_user.branch,
func.cast(SalesHeader.date_created, DATE) == date_created,
or_(and_(SalesHeader.confirm != True, SalesHeader.transtype == 'CASH'),
and_(SalesHeader.confirm == True, SalesHeader.transtype != 'CASH')),
*sales_filter)).all()
else:
sales = db.session.query(SalesHeader).join(SalesRow). \
join(Warehouses, Warehouses.whsecode == SalesRow.whsecode) \
.filter(and_(Warehouses.branch == curr_user.branch,
or_(and_(SalesHeader.confirm != True, SalesHeader.transtype == 'CASH'),
and_(SalesHeader.confirm == True, SalesHeader.transtype != 'CASH')),
*sales_filter)).all()
sales_schema = SalesHeaderSchema(
many=True,
only=("id", "docstatus", "seriescode", "transnumber",
"reference", "transdate", "cust_code", "cust_name",
"objtype", "remarks", "transtype", "delfee", "disctype",
"discprcnt", "disc_amount", "gross", "gc_amount",
"doctotal", "reference2", "tenderamt", "sap_number",
"appliedamt", "amount_due", "void", "created_user",
"confirm"))
result = sales_schema.dump(sales)
return ResponseMessage(True, count=len(result), 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':
datas = request.get_json()
try:
for data in datas:
details = data['rows']
obj = ObjectType.query.filter_by(code='PMNT').first()
if not obj:
return ResponseMessage(
False, message="No object type!").resp(), 401
# query the series
series = Series.query.filter_by(whsecode=curr_user.whse,
objtype=obj.objtype).first()
# check if has an series
if not series:
return ResponseMessage(
False, message="No series found!").resp(), 401
# add to the header
data['header']['series'] = series.id
data['header']['seriescode'] = series.code
data['header']['transnumber'] = series.next_num
data['header'][
'reference'] = f"{series.code}-{obj.code}-{series.next_num}"
data['header']['objtype'] = obj.objtype
# 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')
# unpack to the class PayTransHeader
payment = PayTransHeader(**data['header'])
# get the amount due of sales
sales = SalesHeader.query.filter_by(
id=data['header']['base_id']).first()
if sales.docstatus != 'O':
raise Exception("Sales already closed!")
payment.total_due = sales.amount_due
payment.created_by = curr_user.id
payment.updated_by = curr_user.id
# add 1 to series next num
series.next_num += 1
db.session.add_all([series, payment])
db.session.flush()
sales = SalesHeader.query.get(payment.base_id)
sales_amount_due = sales.amount_due
# payment details
for row in details:
row['payment_id'] = payment.id
# check if is deposit
if row['payment_type'] in ['FDEPS']:
# get deposit payment by deposit id
dep = Deposit.query.filter_by(
id=row['deposit_id']).first()
# check if has record
if not dep:
raise Exception('Deposit not found!')
# check if the Deposit is not open.
if dep.status != 'O':
raise Exception('Deposit already closed!')
pay_row = PayTransRow(**row)
pay_row.created_by = curr_user.id
pay_row.updated_by = curr_user.id
# update the Payment Header
payment.total_paid += pay_row.amount
db.session.add(pay_row)
if sales.transtype == 'CASH':
if sales_amount_due > payment.total_paid:
raise Exception(
f"Can't add transaction {payment.reference} because "
"amount payable is greater than total payment!")
if payment.total_paid < 0:
raise Exception(
f"Can't add transaction {payment.reference} because "
"total payment is less than or equal to 0.")
if payment.total_paid > sales_amount_due:
raise Exception(
f"Can't add transaction {payment.reference} because "
"paid amount is greater than amount due!")
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 get_all_sales(curr_user):
try:
data = request.args.to_dict()
transdate = ''
header_filt = []
row_filt = []
user_filt = []
for k, v in data.items():
if k == 'transdate' and v:
transdate = v
elif k == 'sap_number':
if not v:
header_filt.append((k, "==", None))
if v:
header_filt.append((k, "!=", None))
elif k == 'whsecode' and v:
row_filt.append((k, "==", v))
elif k == 'branch':
user_filt.append((k, "==", v))
else:
if v:
header_filt.append((k, "==", v))
header_filter = BaseQuery.create_query_filter(
SalesHeader, filters={"and": header_filt})
row_filter = BaseQuery.create_query_filter(SalesRow,
filters={"and": row_filt})
user_filter = BaseQuery.create_query_filter(User,
filters={"and": user_filt})
if transdate:
query = db.session.query(SalesHeader). \
select_from(SalesHeader). \
join(SalesRow, SalesRow.sales_id == SalesHeader.id). \
outerjoin(User, User.id == SalesHeader.created_by). \
filter(and_(
cast(SalesHeader.transdate, DATE) == transdate,
*header_filter,
*row_filter,
*user_filter
))
else:
query = db.session.query(SalesHeader). \
select_from(SalesHeader). \
join(SalesRow, SalesRow.sales_id == SalesHeader.id). \
outerjoin(User, User.id == SalesHeader.created_by). \
filter(and_(
*header_filter,
*row_filter,
*user_filter
))
sales_schema = SalesHeaderSchema(many=True,
exclude=("date_created",
"date_updated", "created_by",
"updated_by", "salesrow"))
result = sales_schema.dump(query)
return ResponseMessage(True, count=len(result), 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
def update_payment_sap_num(curr_user):
if not curr_user.is_can_add_sap() and not curr_user.is_manager() and not curr_user.is_admin():
return ResponseMessage(False, message="Unauthorized user!").resp(), 401
try:
data = request.args.to_dict()
transdate = ''
sales_filts = []
payment_filts = []
payment_row_filts = [("sap_number", "==", None)]
user_filts = []
for k, v in data.items():
if 'sales_type' == k and v:
sales_filts.append(('transtype', "==", v))
elif 'payment_type' == k and v:
payment_row_filts.append((k, "==", v))
elif 'transdate' == k and v:
transdate = v
elif 'branch' == k and v:
user_filts.append((k, "==", v))
elif 'whse' == k and v:
user_filts.append((k, "==", v))
elif 'ids' == k and v:
payment_row_filts.append(('id', 'in', json.loads(request.args.get('ids'))))
elif 'search' == k and v:
payment_filts.append(('cust_code', 'like', f'%{v}%'))
sales_filter = BaseQuery.create_query_filter(SalesHeader, filters={"and": sales_filts})
payment_filter = BaseQuery.create_query_filter(PayTransHeader, filters={"and": payment_filts})
user_filter = BaseQuery.create_query_filter(User, filters={"and": user_filts})
payment_row_filter = BaseQuery.create_query_filter(PayTransRow, filters={"and": payment_row_filts})
if transdate:
query = db.session.query(
PayTransHeader.reference,
PayTransHeader.cust_code,
PayTransRow.payment_type,
PayTransRow.amount,
PayTransRow.reference2
).select_from(PayTransRow). \
join(PayTransHeader, PayTransHeader.id == PayTransRow.payment_id). \
join(SalesHeader, SalesHeader.id == PayTransHeader.base_id). \
outerjoin(User, User.id == SalesHeader.created_by). \
filter(and_(
cast(PayTransHeader.transdate, DATE) == transdate,
*sales_filter,
*payment_filter,
*user_filter,
*payment_row_filter
))
else:
query = db.session.query(
PayTransHeader.reference,
PayTransHeader.cust_code,
PayTransRow.payment_type,
PayTransRow.amount,
PayTransRow.reference2).select_from(PayTransRow). \
join(PayTransHeader, PayTransHeader.id == PayTransRow.payment_id). \
join(SalesHeader, SalesHeader.id == PayTransHeader.base_id). \
outerjoin(User, User.id == SalesHeader.created_by). \
filter(and_(
*sales_filter,
*payment_filter,
*user_filter,
*payment_row_filter,
))
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
if request.method == 'GET':
try:
row_schema = ForSAPIPSchema(many=True)
result = row_schema.dump(query)
return ResponseMessage(True, count=len(result), 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 == 'PUT':
try:
data = request.get_json()
for row in query:
row.sap_number = data['sap_number']
row.updated_by = curr_user.id
db.session.commit()
return ResponseMessage(True, message="Successfully updated!").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()
