from DepositAPI.model.BaseModel import BaseModel from DepositAPI.model.QuotePackage import QuotePackage from DepositAPI.libs.db.RDbSql import DbMySql from DepositAPI.common import Utility from DepositAPI.common.const.hpodconst import ROLE_TYPE class Quote(BaseModel): def __init__(self): self.table = "tbl_quote" self.primary_key = "ID" self.fields = [] def get_base_query(self, where): sql_query = f"""SELECT * FROM (SELECT p.ID, pd.PartnerName, pd.Status as ProdStatus, pd.LicensePlate, p.UID, p.Username, u.SaleID, s.FullName as SaleName, p.HsCode, p.ImageUrl, p.ImageUrl2, p.ImageUrl3, p.ProductInfo, p.QuoteAmount, p.PackType, p.CustomsUnit, p.CustomsPriceNote, p.CustomsPriceCNY, p.CustomsAmountCNY, p.CustomsPriceVND, p.CustomsAmountVND, p.CustomsContent, p.Status, p.ExchangeRate, p.WeightPrice, p.VolumePrice, p.BrandName, p.ProductName, p.ProductNameVN, p.ProductMaterial, p.ProductUses, p.ProductSize, p.ProductNumberInPack, p.ProductNumber, p.ProductCapacity, p.Manufacturer, p.ManufacturerAddress, p.NumberOfPack, p.WeightOfPack, p.Length, p.Width, p.Height, p.Weight, p.Volume, p.TotalAmount, p.PaidAmount, p.VnImportDate, p.VnExportDate, p.TruckID, p.TotalCost, p.ImportTax, p.VatTax, p.ServiceFee, p.OtherFeeAmount, p.OtherFeeNote, p.TotalFeeAmount, p.EstimateTransitPrice, p.EstimateTransitAmount, p.InvoicePrice, p.Currency, p.IsQuoted, p.IsLinked, p.LabelCode, p.LabelDate, p.CreatedDate, p.CreatedBy, p.ModifiedDate, p.ModifiedBy, p.Description FROM tbl_quote as p LEFT JOIN tbl_user as u ON p.UID = u.ID LEFT JOIN tbl_user as s ON u.SaleID = s.ID LEFT JOIN (SELECT pq.QuoteID, prod.PartnerName, prod.Status, GROUP_CONCAT(DISTINCT t.LicensePlate) as LicensePlate FROM tbl_product_quote as pq INNER JOIN tbl_product as prod ON pq.ProductID = prod.ID LEFT JOIN tbl_truck_product as tp ON prod.ID = tp.ProductID LEFT JOIN tbl_truck as t ON tp.TruckID = t.ID GROUP BY pq.QuoteID) as pd ON p.ID = pd.QuoteID ) as t {where}""" return sql_query def get_list(self, filter, quickFilter, sortBy, descending, rowsPerPage, offset, user_id, role_id): where = self.build_query_by_filter(filter, quickFilter) sortBy = self.primary_key if len(self.fields) > 0 and sortBy not in self.fields else sortBy if role_id == ROLE_TYPE.Sale: where = "%s AND SaleID = %s" % (where, user_id) if where != '' else "WHERE SaleID = %s" % (user_id) sql_query = self.get_base_query(where) items, totalRow = self.get_list_objects(sql_query, sortBy, descending, rowsPerPage, offset, bySql=True) return items, totalRow def get_detail_by_code(self, code): db = DbMySql() query_sql = f""" SELECT * FROM tbl_quote WHERE LOWER(HsCode) = 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 get_product_suggestions(self, product_info): """Lấy danh sách suggest cho tên hàng từ các đơn cũ""" db = DbMySql() query_sql = f""" SELECT DISTINCT q.ProductInfo, q.HsCode, q.CustomsPriceCNY, q.ImportTax, q.VatTax, q.ServiceFee FROM tbl_quote as q WHERE q.ProductInfo LIKE :product_info ORDER BY q.ID DESC LIMIT 10;""" query_param = {'product_info': f'%{product_info}%'} rows = db.query(query=query_sql, params=query_param) return self.process_in_pandas(rows) def get_quotes_by_customer(self, customer_id): """Lấy danh sách khai báo theo khách hàng""" db = DbMySql() query_sql = f""" SELECT q.ID, q.ImageUrl, q.HsCode, q.ProductInfo, q.CustomsPriceCNY, q.CustomsAmountCNY, q.WeightPrice, q.VolumePrice, q.TotalAmount, q.CreatedDate, q.IsLinked, q.ProductNumber, q.InvoicePrice, q.ServiceFee FROM tbl_quote as q WHERE q.UID = :customer_id ORDER BY q.CreatedDate DESC;""" query_param = {'customer_id': customer_id} rows = db.query(query=query_sql, params=query_param) return self.process_in_pandas(rows) def update(self, request, pk): Utility.del_prop(request, 'CreatedDate') Utility.del_prop(request, 'ModifiedDate') Utility.del_prop(request, 'isTrusted') Utility.del_prop(request, 'Packages') # Tính toán các giá trị tự động self.calculate_quote_amounts(request) request.data['ModifiedBy'] = request.user.username return self.update_object_detail(request, pk) def insert(self, request): Utility.del_prop(request, 'CreatedDate') Utility.del_prop(request, 'ModifiedDate') Utility.del_prop(request, 'isTrusted') packages = Utility.get_prop(request, 'Packages', True, []) # Tính toán các giá trị tự động self.calculate_quote_amounts(request) request.data['CreatedBy'] = request.user.username item = self.insert_object_detail(request) qp_model = QuotePackage() for package in packages: package['ID'] = None package['QuoteID'] = item['ID'] qp_model.insert(package) return item def calculate_quote_amounts(self, request): """Tính toán các giá trị khai báo tự động""" try: # Lấy các giá trị từ request customs_price_vnd = int(request.data.get('CustomsPriceVND', 0)) product_number = int(request.data.get('ProductNumber', 0)) # weight_price = float(request.data.get('WeightPrice', 0)) # volume_price = float(request.data.get('VolumePrice', 0)) import_tax = float(request.data.get('ImportTax', 0)) vat_tax = float(request.data.get('VatTax', 0)) service_fee = float(request.data.get('ServiceFee', 0)) # estimate_transit_price = float(request.data.get('EstimateTransitPrice', 0)) estimate_transit_amount = int(request.data.get('EstimateTransitAmount', 0)) other_fee_amount = int(request.data.get('OtherFeeAmount', 0)) # Tính tổng cân nặng và thể tích từ các sản phẩm trong khai báo # quote_id = request.data.get('ID') if hasattr(request.data, 'get') else getattr(request.data, 'ID', None) # total_weight, total_volume = self.get_total_weight_volume_from_products(quote_id) # Nếu có dữ liệu từ sản phẩm thì sử dụng, không thì dùng giá trị nhập tay # weight = total_weight if total_weight > 0 else float(request.data.get('Weight', 0)) # volume = total_volume if total_volume > 0 else float(request.data.get('Volume', 0)) # Tính tiền vận chuyển # weight_amount = weight * weight_price # volume_amount = volume * volume_price # transport_amount = max(weight_amount, volume_amount) customs_amount_vnd = customs_price_vnd * product_number # Tính phí dịch vụ total_fee_amount = customs_amount_vnd * (import_tax + (1 + import_tax/100) * vat_tax + service_fee) / 100 + other_fee_amount # Tính tổng tiền vận chuyển dự kiến # estimate_transit_amount = weight * estimate_transit_price # Tính tổng tiền hàng total_amount = total_fee_amount + estimate_transit_amount # Cập nhật vào request # request.data['Weight'] = request.data.get('WeightOfPack', 0) * request.data.get('NumberOfPack', 0) # request.data['Volume'] = request.data.get('NumberOfPack', 0) * request.data.get('Length', 0) * request.data.get('Width', 0) * request.data.get('Height', 0) / 1000000 request.data['TotalAmount'] = total_amount request.data['TotalFeeAmount'] = total_fee_amount # request.data['EstimateTransitAmount'] = estimate_transit_amount except (ValueError, TypeError): # Nếu có lỗi trong quá trình tính toán, bỏ qua pass def get_total_weight_volume_from_products(self, quote_id): """Lấy tổng cân nặng và thể tích từ các sản phẩm trong khai báo""" if not quote_id: return 0, 0 try: db = DbMySql() query_sql = """ SELECT SUM(COALESCE(p.Weight, 0)) as TotalWeight, SUM(COALESCE(p.Volume, 0)) as TotalVolume FROM tbl_product p INNER JOIN tbl_product_quote pq ON p.ID = pq.ProductID WHERE pq.QuoteID = :quote_id """ query_param = {'quote_id': quote_id} rows = db.query(query=query_sql, params=query_param) if rows and len(rows) > 0: total_weight = float(rows[0].get('TotalWeight', 0) or 0) total_volume = float(rows[0].get('TotalVolume', 0) or 0) return total_weight, total_volume except Exception as e: # Log error nếu cần pass return 0, 0