Untitled

mail@pastecode.io avatar
unknown
sql
7 months ago
1.7 kB
1
Indexable
Never
SELECT DISTINCT  
v.id, 
CASE 
	WHEN v.approval_status = -1 AND CURDATE() < CAST(v.start_time AS DATE) THEN 'Chờ phê duyệt'
	WHEN v.approval_status = -1 AND CURDATE() >= CAST(v.start_time AS DATE) THEN 'Hết hạn phê duyệt' 
	WHEN v.approval_status = 0 THEN 'Từ chối phê duyệt'
	WHEN v.approval_status = 1 AND CURDATE() < CAST(v.start_time AS DATE) THEN 'Chờ triển khai'
	WHEN v.approval_status = 1 AND CURDATE() > CAST(v.end_time AS DATE) THEN 'Hết hiệu lực'
	ELSE 'Đang triển khai'
END AS approvalStatus,
v.code, 
v.name, 
v.quantity_issued AS quantityIssued, 
CASE 
	WHEN v.voucher_categorization = 1 THEN 'Voucher điện tử'
	ELSE 'Voucher giấy'
END AS voucherCategory,
CASE 
	WHEN v.discount_method = 1 THEN 'Giảm tiền trên tổng hoá đơn'
	WHEN v.discount_method = 2 THEN 'Giảm % trên tổng hoá đơn'
	WHEN v.discount_method = 3 THEN 'Giảm tiền theo từng dịch vụ'
	ELSE 'Giảm % theo từng dịch vụ'
END AS discountMethod,
b.name AS appliedBranch, 
v.start_time AS startDate, 
v.end_time AS endDate, 
fu.file_name AS fileName, 
v.approval_by AS approvedBy, 
v.approval_date AS approvalDate, 
v.status, 
v.last_modified_by AS lastModifiedBy, 
v.last_modified_date AS lastModifiedDate 
FROM
voucher v 
INNER JOIN discount_program_branches dpb ON dpb.`type` = 2 AND dpb.id_parent = v.id 
INNER JOIN branches b ON b.id = dpb.id_branch 
INNER JOIN discount_program_file_mapping dpfm ON dpfm.`type` = 2 AND dpfm.id_parent = v.id 
INNER JOIN file_upload fu ON fu.id = dpfm.file_upload_id AND fu.`type` = 2 
INNER JOIN `user` u ON u.id = v.approval_by  
WHERE v.status = 1 AND b.status = 1 AND fu.status = 1 AND dpb.status = 1 AND u.status = 1