CREATE TEMPORARY TABLE IF NOT EXISTS `_temp_all_voucher` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`voucher_id` int(11) DEFAULT NULL,
`voucher_no` varchar(80) DEFAULT NULL,
`voucher_value` int(11) DEFAULT NULL,
`type_id` int(11) DEFAULT NULL,
`type_desc` varchar(20) DEFAULT NULL,
`status_id` int(11) DEFAULT NULL,
`status_desc` varchar(29) DEFAULT NULL,
`expiry_date` date DEFAULT NULL,
`submission_id` int(11) DEFAULT NULL,
`submission_time` datetime DEFAULT NULL,
`submission_user_id` int(11) DEFAULT NULL,
`submission_by` varchar(100) DEFAULT NULL,
`reimburse_id` int(11) DEFAULT NULL,
`reimburse_time` datetime DEFAULT NULL,
`reimburse_user_id` int(11) DEFAULT NULL,
`reimburse_by` varchar(100) DEFAULT NULL,
`is_retained` enum('Y','N') DEFAULT 'N',
`retained_time` datetime DEFAULT NULL,
`retained_user_id` int(11) DEFAULT NULL,
`retained_by` int(11) DEFAULT NULL,
`retained_remarks` varchar(100) DEFAULT NULL,
`released_time` datetime DEFAULT NULL,
`release_user_id` int(11) DEFAULT NULL,
`released_by` varchar(100) DEFAULT NULL,
`campaign_id` int(11) DEFAULT NULL,
`campaign_code` varchar(200) DEFAULT NULL,
`issue_time` datetime DEFAULT NULL,
`issue_by` varchar(100) DEFAULT NULL,
`issue_user_id` int(11) DEFAULT NULL,
`storeId` int(11) DEFAULT NULL,
`organization` varchar(100) DEFAULT NULL,
`storeName` varchar(100) DEFAULT NULL,
`unit` varchar(100) DEFAULT NULL,
`void_user_id` int(11) DEFAULT NULL,
`void_time` datetime DEFAULT NULL,
`void_by` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `voucher_id` (`voucher_id`),
KEY `type_id` (`type_id`),
KEY `status_id` (`status_id`),
KEY `submission_id` (`submission_id`),
KEY `is_retained` (`is_retained`),
KEY `campaign_id` (`campaign_id`),
KEY `issue_user_id` (`issue_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
#insert all voucher in to temp table
INSERT INTO `_temp_all_voucher` (`id`, `voucher_id`, `voucher_no`, `voucher_value`, `type_id`, `type_desc`, `status_id`, `status_desc`, `expiry_date`, `submission_id`, `submission_time`, `reimburse_time`,`is_retained`, `campaign_id`, `campaign_code`)
select NULL, voucher_id, concat(prefix, voucher_text, suffix) as voucher_no, voucher_value, type_id, NULL, status_id, NULL, expiry_date, NULL, NULL, NULL, NULL, NULL, NULL
from voucher;
CREATE temporary TABLE `_temp_err_voucher` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`voucher_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `voucher_id` (`voucher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
#insert err record
INSERT INTO `_temp_err_voucher` (`voucher_id`)
select a.voucher_id from voucher_reimbursement_details a, voucher_reimbursement_details b
where a.voucher_id = b.voucher_id
and a.is_retained = 'N' and b.is_retained = 'Y'
and a.is_deleted = 'N' and b.is_deleted = 'N';
#update type desc
update _temp_all_voucher a, voucher_lkup_type b
set a.type_desc = b.type_desc
where a.type_id = b.type_id;
#update status desc
update _temp_all_voucher a, voucher_lkup_status b
set a.status_desc = b.status_desc
where a.status_id = b.status_id;
#except double submit get submission id & submit time
update _temp_all_voucher a,
(
select voucher_id, max(submission_id) as submission_id
from voucher_reimbursement_details
where is_deleted = 'N' and is_retained = 'N'
and voucher_id not in (select voucher_id from _temp_err_voucher) GROUP BY 1
) b, voucher_reimbursement c
set a.submission_id = b.submission_id, a.submission_time = c.submit_time, a.reimburse_time = c.reimburse_time, a.reimburse_user_id = c.reimburse_user, a.submission_user_id = c.submit_user, a.reimburse_id = c.reimburse_id
where a.voucher_id = b.voucher_id and b.submission_id = c.submission_id;
#double submit get submission id & submit time
update _temp_all_voucher a,
(
select voucher_id, submission_id from voucher_reimbursement_details
where is_deleted='N' and is_retained = 'N'
and voucher_id in (select voucher_id from _temp_err_voucher)
) b, voucher_reimbursement c
set a.submission_id = b.submission_id, a.submission_time = c.submit_time, a.reimburse_time = c.reimburse_time, a.reimburse_user_id = c.reimburse_user, a.submission_user_id = c.submit_user, a.reimburse_id = c.reimburse_id
where a.voucher_id = b.voucher_id and b.submission_id = c.submission_id;
#pure retaind get submission id & submit time
update _temp_all_voucher a,
(
select voucher_id, submission_id from voucher_reimbursement_details where is_retained = 'Y' and is_deleted='N'
and voucher_id not in (select voucher_id from _temp_err_voucher)
) b, voucher_reimbursement c
set a.submission_id = b.submission_id, a.submission_time = c.submit_time, a.reimburse_time = c.reimburse_time, a.reimburse_user_id = c.reimburse_user, a.submission_user_id = c.submit_user, a.reimburse_id = c.reimburse_id
where a.voucher_id = b.voucher_id and b.submission_id = c.submission_id;
#update is_retained
update _temp_all_voucher a, voucher_reimbursement_details b
set a.is_retained = b.is_retained
where a.voucher_id = b.voucher_id and a.submission_id = b.submission_id;
#update campaign id and campaign code
update _temp_all_voucher a, voucher_campaign_issue b, voucher_campaign c, voucher_action d
set a.campaign_id = c.campaign_id, a.campaign_code = c.campaign_code, a.issue_time = d.action_time, a.issue_user_id = d.user_id
where a.voucher_id = b.voucher_id and b.campaign_id = c.campaign_id and b.voucher_id = d.voucher_id and d.action_id = 30
and b.is_deleted = 'N' and b.id = d.related_id;
#update issue user
update _temp_all_voucher a, evo_central_config.customer_user b
set a.issue_by = b.user_name
where a.issue_user_id = b.user_id;
#update release action
update voucher_action a, voucher_reimbursement_details b, _temp_all_voucher c
set c.released_time = a.action_time, c.release_user_id = a.user_id
where a.action_id = 80 and a.voucher_id = b.voucher_id
and a.related_id = b.detail_id
and b.is_deleted = 'N'
and a.voucher_id = c.voucher_id;
#update release user
update _temp_all_voucher a, evo_central_config.customer_user b
set a.released_by = b.user_name
where a.release_user_id= b.user_id;
#update retain action
update voucher_action a, voucher_reimbursement_details b, _temp_all_voucher c
set c.retained_time = a.action_time, c.retained_user_id = a.user_id, c.retained_remarks = a.remarks
where a.action_id = 70 and a.voucher_id = b.voucher_id
and a.related_id = b.detail_id
and b.is_deleted = 'N'
and a.voucher_id = c.voucher_id;
#update retain user
update _temp_all_voucher a, evo_central_config.customer_user b
set a.retained_by = b.user_name
where a.retained_user_id = b.user_id;
#update submit user
update _temp_all_voucher a, evo_central_config.customer_user b
set a.submission_by = b.user_name
where a.submission_user_id = b.user_id;
#update reimburse user
update _temp_all_voucher a, evo_central_config.customer_user b
set a.reimburse_by = b.user_name
where a.reimburse_user_id = b.user_id;
#update store info
update _temp_all_voucher a, voucher_reimbursement b, store c
set a.storeId = c.storeId, a.organization = c.organization, a.storeName = c.storeName, a.unit = c.unit
where a.submission_id = b.submission_id and b.lease_out_id = c.storeId;
#update void action and user
update _temp_all_voucher a, voucher_action b
set a.void_time = b.action_time, a.void_user_id = b.user_id
where b.action_id = 140 and a.voucher_id = b.voucher_id and a.status_id = 50;
#update void user
update _temp_all_voucher a, evo_central_config.customer_user b
set a.void_by = b.user_name
where a.void_user_id = b.user_id;
#drop TEMPORARY TABLE `_temp_all_voucher`
#drop TEMPORARY TABLE `_temp_err_voucher`
select
voucher_no as 'Voucher no',
voucher_value as 'Voucher Value',
IFNULL(type_desc, '') as 'Voucher Type',
IFNULL(status_desc, '') as 'Voucher Status',
IFNULL(expiry_date, '') as 'Expiry Date',
IFNULL(submission_id, '') as 'Submission ID',
IFNULL(submission_time, '') as 'Submission Date',
IFNULL(is_retained, '') as 'Retained Y/N',
IFNULL(campaign_code, '') as 'Campaign Code or Name',
IFNULL(issue_time, '') as 'Issued Date',
IFNULL(issue_by, '') as 'Issue By',
IFNULL(reimburse_time, '') as 'Reimburse Date',
IFNULL(reimburse_by, '') as 'Reimburse By',
IFNULL(retained_time, '') as 'Retained Time',
IFNULL(retained_by, '') as 'Retained By',
IFNULL(released_time, '') as 'Release Time',
IFNULL(released_by, '') as 'Release By',
IFNULL(storeId, '') as 'Store ID',
IFNULL(organization, '') as 'Organization',
IFNULL(storeName, '') as 'Store Name',
IFNULL(unit, '') as 'Unit',
IFNULL(void_time, '') as 'Void Time',
IFNULL(void_by, '') as 'Void By'
from _temp_all_voucher
order by 1 asc;