from DepositAPI.model.BaseModel import BaseModel
from DepositAPI.libs.db.RDbSql import DbMySql
from DepositAPI.common import Utility

class Truck(BaseModel):
    def __init__(self):
        self.table = "tbl_truck"
        self.primary_key = "ID"
        self.fields = []
    def get_detail_by_code(self, code):
        db = DbMySql()
        query_sql = f""" SELECT * FROM tbl_truck WHERE LOWER(LicensePlate) = LOWER(:code);"""
        query_param = {'code': str(code).strip()}
        rows = db.query(query=query_sql, params=query_param)
        data = self.process_in_pandas(rows)
        return data[0] if len(data) > 0 else None
    def update(self, request, pk):
        Utility.del_prop(request, 'CreatedDate')
        Utility.del_prop(request, 'ModifiedDate')
        Utility.del_prop(request, 'isTrusted')
        details = Utility.get_prop(request, 'Products', True, [])
        action_type = Utility.get_prop(request, 'ActionType', True, '')
        db = DbMySql()
        if action_type == 'update_child':
            sql_str = """ UPDATE tbl_product as p LEFT JOIN tbl_truck_product as op ON op.ProductID = p.ID
                            SET p.`Status` = :status WHERE op.TruckID=:order_id """
            db.query(query=sql_str, params={'order_id': int(pk), 'status': request.data['Status']})
            sql_str = """ UPDATE tbl_truck SET Status = :receive_status WHERE ID=:order_id """
            db.query(query=sql_str, params={'order_id': int(pk), 'receive_status': request.data['ReceiveStatus']})
            return {}
        if len(details) > 0:
            sql_str = """ UPDATE tbl_product SET TruckID=:order_id, Status = 2 WHERE ID IN (%s) AND Status < 2""" % (",".join(str(n) for n in details))
            db.query(query=sql_str, params={'order_id': pk})
            sql_str = f""" INSERT INTO tbl_truck_product(TruckID, ProductID, NumberOfPack) SELECT {pk}, ID, NumberOfPack FROM tbl_product WHERE ID IN ({",".join(str(n) for n in details)}) """
            db.query(query=sql_str)
        request.data['ModifiedBy'] = request.user.username
        db.call_proc(query='sp_calc_inventory', params=[0, int(pk)])
        item = self.update_object_detail(request, pk)
        if 'Status' in request.data:
            db.query(query=f"""
                    UPDATE tbl_product as p 
                    LEFT JOIN tbl_truck_product as tp ON tp.ProductID = p.ID
                    SET p.`Status` = {request.data['Status']} + 2 
                    WHERE tp.TruckID = {pk} AND p.`Status` <= 7
                    """)
        db.call_proc(query='sp_calc_truck', params=[int(pk)])
        return item
    def insert(self, request):
        Utility.del_prop(request, 'CreatedDate')
        Utility.del_prop(request, 'ModifiedDate')
        Utility.del_prop(request, 'isTrusted')
        request.data['CreatedBy'] = request.user.username
        details = Utility.get_prop(request, 'Products', True, [])
        item = self.insert_object_detail(request)
        db = DbMySql()
        if len(details) > 0:
            sql_str = """ UPDATE tbl_product SET TruckID=:order_id, Status = 2 WHERE ID IN (%s) AND Status < 2""" % (",".join(str(n) for n in details))
            db.query(query=sql_str, params={'order_id': item['ID']})
            sql_str = f""" INSERT INTO tbl_truck_product(TruckID, ProductID, NumberOfPack) SELECT {item['ID']}, ID, NumberOfPack FROM tbl_product WHERE ID IN ({",".join(str(n) for n in details)}) """
            db.query(query=sql_str, params={'order_id': item['ID']})
        db.call_proc(query='sp_calc_inventory', params=[0, item['ID']])
        self.update_object_detail({'ModifiedBy': request.user.username}, item['ID'])
        return item