分类： Icolumn
nex magration check
#status check ####################################### select count(*), sum(voucher_value), b.status_desc from nex_evox_live.voucher a, nex_evox_live.voucher_lkup_status b where a.is_evoucher = 'Y' and a.status_id = b.status_id group by a.status_id; #11979410Floating #664952245Reimbursed select count(*), sum(voucher_value), b.status_desc from nex_vms_live.voucher a, nex_vms_live.voucher_lkup_status b where a.is_evoucher = 'Y' and a.status_id = b.status_id group by a.status_id; #11979410Issued #573645765Reimbursed #913 6480Tenant Submitted #Stock check ####################################### select count(*), sum(voucher_value), b.stock_desc from nex_evox_live.voucher a, nex_evox_live.voucher_lkup_stock b where a.is_evoucher = 'Y' and a.stock_id = b.stock_id group by a.stock_id; #11979410Floating #664952245Reimbursed select count(*), sum(voucher_value), b.stock_desc from nex_vms_live.voucher a, nex_vms_live.voucher_lkup_stock b where a.is_evoucher = 'Y' and a.stock_id = b.stock_id group by a.stock_id; #11979410Issued #573645765Reimbursed #913 6480Tenant Submitted #action check ####################################### select count(*), sum(voucher_value), b.action_id, c.action_desc from nex_evox_live.voucher a, nex_evox_live.voucher_action b, nex_evox_live.voucher_lkup_action c where a.is_evoucher = 'Y' and a.voucher_id = b.voucher_id and b.action_id = c.action_id group by b.action_id; #7846616551 Order #78466165520 Stock In #78466165570 Release #78466165580 Allocation #784661655150Direct Issue #664952245180Reimburse select count(*), sum(voucher_value), b.action_id, c.action_desc from nex_vms_live.voucher a, nex_vms_live.voucher_action b, nex_vms_live.voucher_lkup_action c where a.is_evoucher = 'Y' and a.voucher_id = b.voucher_id and b.action_id = c.action_id group by b.action_id; #7846616551 Create #78466165530 Direct Issue #66495224560 Tenant Submit #57364576561 Reimburse #374731810130Extend #campaign check ####################################### select campaign_id, campaign_code, campaign_desc from nex_evox_live.voucher_campaign where is_evoucher_campaign = 'Y' #99Reward E-Voucher RedemptionReward E-Voucher Redemption #100PJ MASKS SAVE CHRISTMAS @ NEXRECEIVE $10 ENEXVOUCHER WHEN YOU CHARGE $250 TO YOUR CITI CREDIT CARD AT PARTICIPATING STORES #101PROSPEROUS ABUNDANCE @ NEX 2020RECEIVE $10 ENEXVOUCHER WHEN YOU CHARGE $250 TO YOUR CITI CREDIT CARD AT PARTICIPATING STORES select campaign_id, campaign_code, campaign_desc from nex_vms_live.voucher_campaign where is_evoucher_campaign = 'Y' #5 Reward E-Voucher RedemptionReward E-Voucher Redemption #32PJ MASKS SAVE CHRISTMAS @ NEXRECEIVE $10 ENEXVOUCHER WHEN YOU CHARGE $250 TO YOUR CITI CREDIT CARD AT PARTICIPATING STORES #34PROSPEROUS ABUNDANCE @ NEX 2020RECEIVE $10 ENEXVOUCHER WHEN YOU CHARGE $250 TO YOUR CITI CREDIT CARD AT PARTICIPATING STORES #issue record check ####################################### select count(*) as cnt, sum(c.voucher_value) as total_value ,b.campaign_code from nex_evox_live.voucher_campaign_issue a, nex_evox_live.voucher_campaign b, nex_evox_live.voucher c ,nex_evox_live.voucher_action d where b.is_evoucher_campaign = 'Y' and a.campaign_id = b.campaign_id and a.voucher_id = c.voucher_id and a.is_deleted = 'N' and a.id = d.related_id and d.action_id = 150 group by a.campaign_id #623345525Reward E-Voucher Redemption #8298290PJ MASKS SAVE CHRISTMAS @ NEX #7847840PROSPEROUS ABUNDANCE @ NEX 2020 select count(*) as cnt, sum(c.voucher_value) as total_value ,b.campaign_code from nex_vms_live.voucher_campaign_issue a, nex_vms_live.voucher_campaign b, nex_vms_live.voucher c, nex_vms_live.voucher_action d where b.is_evoucher_campaign = 'Y' and a.campaign_id = b.campaign_id and a.voucher_id = c.voucher_id and a.is_deleted = 'N' and a.id = d.related_id and d.action_id = 30 group by a.campaign_id #623345525Reward E-Voucher Redemption #8298290PJ MASKS SAVE CHRISTMAS @ NEX #7847840PROSPEROUS ABUNDANCE @ NEX 2020 #request table issue check ####################################### select sum(b.total_value) as total_value from nex_evox_live.voucher_request_approve a, nex_evox_live.voucher_batch_no_section b where a.batch_id = b.batch_id and a.request_action = 150 and from_stock=120 #6568890 select sum(b.total_value) as total_value from nex_vms_live.voucher_request_approve a, voucher_batch_no_section b where a.batch_id = b.batch_id and a.request_action = 30 #9175975 select count(*) from nex_evox_live.voucher_request_approve where request_action = 150 and from_stock=120 #7846 select count(*) from nex_vms_live.voucher_request_approve where request_action = 30 #7846 #use voucher issue table check issue ####################################### select sum(c.total_value) as total_value from nex_evox_live.voucher_campaign a, nex_evox_live.voucher_issue b,nex_evox_live.voucher_batch_no_section c where a.is_evoucher_campaign = 'Y' and a.campaign_id = b.campaign_id and b.batch_id = c.batch_id #61655 #use reimbursement & reimbursement details check reimbruse ####################################### select count(*), sum(c.voucher_value) from nex_evox_live.voucher_reimbursement a, nex_evox_live.voucher_reimbursement_details b, nex_evox_live.voucher c where a.is_evoucher='Y' and a.submission_id = b.submission_id and b.is_deleted = 'N ' and b.is_retained = 'N' and b.voucher_id = c.voucher_id #664952245 select count(*), sum(c.voucher_value) from nex_vms_live.voucher_reimbursement a, nex_vms_live.voucher_reimbursement_details b, nex_vms_live.voucher c where a.submission_id = b.submission_id and b.is_deleted = 'N ' and b.is_retained = 'N' and b.voucher_id = c.voucher_id #664952245
merctus all voucher report
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;
merctus voucher summary report
#select min(issue_time) from voucher_issued_details_for_report set @start_time = '2020-05-01 00:00:00'; set @end_time = '2020-05-31 23:59:59'; #get opening select SUM( IF(issue_time < @start_time, voucher_value, 0 )) - SUM( IF(reimburse_time < @start_time, voucher_value, 0 )) - SUM( IF(void_time < @start_time, voucher_value, 0 )) AS 'Opening Floating', SUM( IF(issue_time between @start_time and @end_time, voucher_value, 0 )) as issued, SUM( IF(reimburse_time between @start_time and @end_time, voucher_value, 0 )) as reimbursed, SUM( IF(void_time between @start_time and @end_time, voucher_value, 0 )) as void, SUM( IF(expire_time between @start_time and @end_time, voucher_value, 0 )) as expired from voucher_issued_details_for_report;
Merctus use snapshot table calculate voucher summary report
just a month Ex.
we can get pre month data and concat to a complate report.
#1.get every monthly data set @start_time = '2020-04-01 00:00:00'; set @end_time = '2020-04-30 23:59:59'; SELECT voucher_id, voucher_value #DATE_FORMAT(expire_time,'%Y%m') months, SUM(voucher_value) AS amount FROM voucher_issued_details_for_report WHERE expire_time >= @start_time AND expire_time <= @end_time and issue_time <= @end_time and (submit_time > @end_time or reimburse_time is null) and (void_time > @end_time or void_time is null) select * from voucher where voucher_id in (1036,1037,5261) select * from voucher_issued_details_for_report where voucher_id in (1036,1037,5261)
CRM API submit and resubmit by VMS Verification
#first CRM API submit and VMS resubmit or reimbruse select concat(a.prefix, a.voucher_text, a.suffix), b.* from voucher a, ( select a.submit_time, b.action_id, a.voucher_id from ( select min(a.action_time) as submit_time, a.voucher_id from voucher_action a, voucher b where a.action_id = 60 and a.user_id = 3324 and a.voucher_id = b.voucher_id group by a.voucher_id ) a left join voucher_action b on (a.voucher_id = b.voucher_id and b.action_id in (70) and b.user_id != 3324) where b.id is not null ) b where a.voucher_id = b.voucher_id
laravel 密码错误
shoptima的项目 安装好了但是登录不进去
https://learnku.com/articles/5963/toggle-laravel-login-default-bcrypt-encryption-validation
跟着这篇文章先了解了laravel login default bcrypt 加密验证的流程
然后在
shoptima_crm/vendor/laravel/framework/src/Illuminate/Auth/EloquentUserProvider.php
public function validateCredentials
在check之前添加如下代码
$pwd = 'secret';
// echo $hash = password_hash($pwd, PASSWORD_BCRYPT);exit;
$hash = '$2y$10$sCP.2BFmk74IIJ6ftVT4.uN4BjH092LpvgcFAG7FtSOvT4VmJ0guW';
if (password_verify($pwd,$hash)) {
echo "密码正确";
} else {
echo "密码错误";
}
exit;
然后把password_hash算出的密码 保存到数据库里就可以了
password_hash每次加密过的结果都是不同的
只有通过password_verify来验证
—————————–
可以使用artisan来做：
php artisan tinker
$u =App\User::find(1)
$u->password=bcrypt('newpassword')
$u->save()
—————————–
另外一种方式：
Set mail driver as "log" ( .env file )
click forgot password
get password reset link(mail content) in laravel.log
paste it in browser and reset password.
change some type campaign
(LL1000017085PJ - LL10000017094PJ and LL5000000881PJ-LL5000000PJ884) #70841,70842,70843,70844,70845,70846,70847,70848,70849,70850 select group_concat(voucher_id) from voucher where prefix='LL10' and voucher_no between 17085 and 17094 and suffix = 'PJ' #669749,669750,669751,669752 select group_concat(voucher_id) from voucher where prefix='LL50' and voucher_no between 881 and 884 and suffix = 'PJ' select * from voucher_campaign_issue where voucher_id = 669749 #23 -> 25 select * from voucher_campaign where campaign_id in (23,25) select * from voucher_campaign_issue where voucher_id in (70841,70842,70843,70844,70845,70846,70847,70848,70849,70850,669749,669750,669751,669752) select * from voucher_campaign_issue_request where request_id = 106050 select * from voucher_request_approve where request_id = 106050 select * from voucher_action where voucher_id in (70841,70842,70843,70844,70845,70846,70847,70848,70849,70850,669749,669750,669751,669752) and action_id = 30 update voucher_campaign_issue set campaign_id = 25 where voucher_id in (70841,70842,70843,70844,70845,70846,70847,70848,70849,70850,669749,669750,669751,669752); update voucher_campaign_issue_request set campaign_id = 25 where request_id = 106050 update voucher_request_approve set request_remarks = 'Citibank Internet Rewards $50 Type: Partner Redemption Expiry Date: 2020-11-22 Receipt Number: BR0009005 Ref No.: 00002036968916001,00002036968916002,00002036968916003,00002036968916006,00002036968916005,00002036968916004 Name: Jason Chua' where request_id = 106050 update voucher_action set remarks = 'Citibank Internet Rewards $50 Type: Partner Redemption Expiry Date: 2020-11-22 Receipt Number: BR0009005 Ref No.: 00002036968916001,00002036968916002,00002036968916003,00002036968916006,00002036968916005,00002036968916004 Name: Jason Chua' where voucher_id in (70841,70842,70843,70844,70845,70846,70847,70848,70849,70850,669749,669750,669751,669752) and action_id = 30;
Wisma lost request approve record
#1.search lose request approve record select distinct(a.campaign_id),d.campaign_code,c.type_desc,a.request_id as a_request_id,b.request_id, c.type_id, a.voucher_id from voucher_lkup_type c, voucher_campaign d, voucher_campaign_issue a left join voucher_request_approve b on (a.request_id = b.request_id) where d.campaign_id = a.campaign_id and c.type_id = d.type_id having b.request_id is null #2.Complement complete request approve record select tmp_a.a_request_id as request_id,NULL as batch_id, null as voucher_id, tmp_b.user_id as request_user, tmp_b.action_time as request_time, 30 as request_action, tmp_a.from_stock, tmp_b.remarks as request_remarks , NULL as approve_user,NULL as approve_time, 30 as approve_action, 50 as to_stock,'Y' as is_done,NULL as cancel_user,NULL as cancel_user from ( select a.voucher_id,a.campaign_id,d.campaign_code,c.type_desc,a.request_id as a_request_id,b.request_id, c.type_id, c.from_stock from voucher_lkup_type c, voucher_campaign d, voucher_campaign_issue a left join voucher_request_approve b on (a.request_id = b.request_id) where d.campaign_id = a.campaign_id and c.type_id = d.type_id group by a.campaign_id, a.request_id having b.request_id is null ) tmp_a left join ( #solve issue action repet issue action, use last time record select * from ( select a.user_id, a.action_time, a.voucher_id, a.remarks from voucher_action a, ( select a.voucher_id,b.request_id from voucher_lkup_type c, voucher_campaign d, voucher_campaign_issue a left join voucher_request_approve b on (a.request_id = b.request_id) where d.campaign_id = a.campaign_id and c.type_id = d.type_id group by a.campaign_id, a.request_id having b.request_id is null ) b where a.voucher_id = b.voucher_id and a.action_id = 30 order by action_time desc ) x group by voucher_id ) tmp_b on tmp_a.voucher_id = tmp_b.voucher_id #3.use code create batch_id & insert into request_approve record voucher_stock_batch->add_voucher_batch_by_voucher_ids
dialog
用了很久了 简单记录一下吧
弹出等待框
$.vms.popup({'title': 'Processing...', 'content':'Processing...', 'buttons':{}});
$.vms.popup_close();
弹出指定框
var dialog_opt = {};
dialog_opt.title = 'Success';
dialog_opt.content = 'The submission have been completed.';
dialog_opt.error = false;
dialog_opt.buttons = {
'OK': function() {
oPenddingReimburseList.fnReloadAjax();
$.vms.popup_close();
}
}
dialog_opt.width = 305;
$.vms.popup(dialog_opt);
弹出id内容框