java/talent-card.git

			@@ -18,10 +18,24 @@
			when coalesce(o.subsidy_amount, 0) > 0
			and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
			then o.subsidy_amount else 0 end), 0) as subsidyAmountCents,
			(select count(*) from verify_record v
			where v.status = 1
			and v.verify_time >= date_format(current_date, '%Y-%m-01')
			and v.verify_time < date_add(last_day(current_date), interval 1 day)) as monthlyVerificationCount,
			(select count(*) from benefit_order verified_order
			where verified_order.deleted = 0
			and verified_order.order_status = 1
			and verified_order.verify_status = 1
			and coalesce(
			(select max(v.verify_time) from verify_record v
			where v.order_id = verified_order.id and v.status = 1),
			(select max(pt.paid_at) from payment_transaction pt
			where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
			verified_order.updated_at
			) >= date_format(current_date, '%Y-%m-01')
			and coalesce(
			(select max(v.verify_time) from verify_record v
			where v.order_id = verified_order.id and v.status = 1),
			(select max(pt.paid_at) from payment_transaction pt
			where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
			verified_order.updated_at
			) < date_add(last_day(current_date), interval 1 day)) as monthlyVerificationCount,
			(select count(*) from talent_user u where u.deleted = 0) as talentCount,
			(select count(*) from benefit_order po where po.deleted = 0 and po.order_status = 0) as pendingOrderCount,
			(select count(*) from merchant_product pp where pp.deleted = 0 and pp.audit_status = 1) as pendingProductCount,
			@@ -37,14 +51,42 @@
			coalesce((select count(*) from talent_user u
			where u.deleted = 0
			and date(u.created_at) = date_sub(current_date, interval 1 day)), 0) as talentCompare,
			(coalesce((select count(*) from verify_record current_month_verify
			where current_month_verify.status = 1
			and current_month_verify.verify_time >= date_format(current_date, '%Y-%m-01')
			and current_month_verify.verify_time < date_add(last_day(current_date), interval 1 day)), 0)
			- coalesce((select count(*) from verify_record previous_month_verify
			where previous_month_verify.status = 1
			and previous_month_verify.verify_time >= date_sub(date_format(current_date, '%Y-%m-01'), interval 1 month)
			and previous_month_verify.verify_time < date_format(current_date, '%Y-%m-01')), 0)) as monthlyVerificationCompare,
			(coalesce((select count(*) from benefit_order current_month_order
			where current_month_order.deleted = 0
			and current_month_order.order_status = 1
			and current_month_order.verify_status = 1
			and coalesce(
			(select max(v.verify_time) from verify_record v
			where v.order_id = current_month_order.id and v.status = 1),
			(select max(pt.paid_at) from payment_transaction pt
			where pt.order_no = current_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
			current_month_order.updated_at
			) >= date_format(current_date, '%Y-%m-01')
			and coalesce(
			(select max(v.verify_time) from verify_record v
			where v.order_id = current_month_order.id and v.status = 1),
			(select max(pt.paid_at) from payment_transaction pt
			where pt.order_no = current_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
			current_month_order.updated_at
			) < date_add(last_day(current_date), interval 1 day)), 0)
			- coalesce((select count(*) from benefit_order previous_month_order
			where previous_month_order.deleted = 0
			and previous_month_order.order_status = 1
			and previous_month_order.verify_status = 1
			and coalesce(
			(select max(v.verify_time) from verify_record v
			where v.order_id = previous_month_order.id and v.status = 1),
			(select max(pt.paid_at) from payment_transaction pt
			where pt.order_no = previous_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
			previous_month_order.updated_at
			) >= date_sub(date_format(current_date, '%Y-%m-01'), interval 1 month)
			and coalesce(
			(select max(v.verify_time) from verify_record v
			where v.order_id = previous_month_order.id and v.status = 1),
			(select max(pt.paid_at) from payment_transaction pt
			where pt.order_no = previous_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
			previous_month_order.updated_at
			) < date_format(current_date, '%Y-%m-01')), 0)) as monthlyVerificationCompare,
			coalesce(sum(case
			when coalesce(o.subsidy_amount, 0) > 0
			and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
			@@ -71,10 +113,24 @@
			where u.deleted = 0
			and u.created_at < date_add(days.bucket_day, interval 1 day)) as talent,
			(select count(*)
			from verify_record v
			where v.status = 1
			and v.verify_time >= days.bucket_day
			and v.verify_time < date_add(days.bucket_day, interval 1 day)) as verification,
			from benefit_order verified_order
			where verified_order.deleted = 0
			and verified_order.order_status = 1
			and verified_order.verify_status = 1
			and coalesce(
			(select max(v.verify_time) from verify_record v
			where v.order_id = verified_order.id and v.status = 1),
			(select max(pt.paid_at) from payment_transaction pt
			where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
			verified_order.updated_at
			) >= days.bucket_day
			and coalesce(
			(select max(v.verify_time) from verify_record v
			where v.order_id = verified_order.id and v.status = 1),
			(select max(pt.paid_at) from payment_transaction pt
			where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
			verified_order.updated_at
			) < date_add(days.bucket_day, interval 1 day)) as verification,
			round(coalesce((select sum(case
			when coalesce(o.subsidy_amount, 0) > 0
			and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
			@@ -107,7 +163,7 @@
			@Select("""
			select buckets.bucket_time as time,
			coalesce(count(v.id), 0) as `order`,
			round(coalesce(sum(v.verify_amount), 0) / 100, 2) as amount
			round(coalesce(sum(v.pay_amount), 0) / 100, 2) as amount
			from (
			select 0 bucket_hour, '0:00' bucket_time union all select 2, '2:00'
			union all select 4, '4:00' union all select 6, '6:00'
			@@ -116,10 +172,19 @@
			union all select 16, '16:00' union all select 18, '18:00'
			union all select 20, '20:00' union all select 22, '22:00'
			) buckets
			left join verify_record v on v.status = 1
			and v.verify_time >= current_date
			and v.verify_time < date_add(current_date, interval 1 day)
			and floor(hour(v.verify_time) / 2) * 2 = buckets.bucket_hour
			left join (
			select o.id, o.pay_amount,
			coalesce(
			(select max(vr.verify_time) from verify_record vr where vr.order_id = o.id and vr.status = 1),
			(select max(pt.paid_at) from payment_transaction pt
			where pt.order_no = o.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
			o.updated_at
			) as effective_time
			from benefit_order o
			where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
			) v on v.effective_time >= current_date
			and v.effective_time < date_add(current_date, interval 1 day)
			and floor(hour(v.effective_time) / 2) * 2 = buckets.bucket_hour
			group by buckets.bucket_hour, buckets.bucket_time
			order by buckets.bucket_hour
			""")
			@@ -128,7 +193,7 @@
			@Select("""
			select date_format(days.bucket_day, '%c/%e') as time,
			coalesce(count(v.id), 0) as `order`,
			round(coalesce(sum(v.verify_amount), 0) / 100, 2) as amount
			round(coalesce(sum(v.pay_amount), 0) / 100, 2) as amount
			from (
			select date_sub(current_date, interval 6 day) as bucket_day union all
			select date_sub(current_date, interval 5 day) union all
			@@ -138,9 +203,18 @@
			select date_sub(current_date, interval 1 day) union all
			select current_date
			) days
			left join verify_record v on v.status = 1
			and v.verify_time >= days.bucket_day
			and v.verify_time < date_add(days.bucket_day, interval 1 day)
			left join (
			select o.id, o.pay_amount,
			coalesce(
			(select max(vr.verify_time) from verify_record vr where vr.order_id = o.id and vr.status = 1),
			(select max(pt.paid_at) from payment_transaction pt
			where pt.order_no = o.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
			o.updated_at
			) as effective_time
			from benefit_order o
			where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
			) v on v.effective_time >= days.bucket_day
			and v.effective_time < date_add(days.bucket_day, interval 1 day)
			group by days.bucket_day
			order by days.bucket_day
			""")
			@@ -149,7 +223,7 @@
			@Select("""
			select concat(weeks.week_start, '~', weeks.week_end) as time,
			coalesce(count(v.id), 0) as `order`,
			round(coalesce(sum(v.verify_amount), 0) / 100, 2) as amount
			round(coalesce(sum(v.pay_amount), 0) / 100, 2) as amount
			from (
			select 0 bucket_index,
			date_format(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 0 day), '%c/%e') as week_start,
			@@ -181,9 +255,18 @@
			date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 28 day),
			last_day(current_date)
			) weeks
			left join verify_record v on v.status = 1
			and v.verify_time >= weeks.start_date
			and v.verify_time < date_add(weeks.end_date, interval 1 day)
			left join (
			select o.id, o.pay_amount,
			coalesce(
			(select max(vr.verify_time) from verify_record vr where vr.order_id = o.id and vr.status = 1),
			(select max(pt.paid_at) from payment_transaction pt
			where pt.order_no = o.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
			o.updated_at
			) as effective_time
			from benefit_order o
			where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
			) v on v.effective_time >= weeks.start_date
			and v.effective_time < date_add(weeks.end_date, interval 1 day)
			where weeks.start_date <= last_day(current_date)
			group by weeks.bucket_index, weeks.week_start, weeks.week_end, weeks.start_date
			order by weeks.bucket_index
			@@ -193,15 +276,24 @@
			@Select("""
			select concat(months.bucket_month, '月') as time,
			coalesce(count(v.id), 0) as `order`,
			round(coalesce(sum(v.verify_amount), 0) / 100, 2) as amount
			round(coalesce(sum(v.pay_amount), 0) / 100, 2) as amount
			from (
			select 1 bucket_month union all select 2 union all select 3 union all select 4
			union all select 5 union all select 6 union all select 7 union all select 8
			union all select 9 union all select 10 union all select 11 union all select 12
			) months
			left join verify_record v on v.status = 1
			and year(v.verify_time) = year(current_date)
			and month(v.verify_time) = months.bucket_month
			left join (
			select o.id, o.pay_amount,
			coalesce(
			(select max(vr.verify_time) from verify_record vr where vr.order_id = o.id and vr.status = 1),
			(select max(pt.paid_at) from payment_transaction pt
			where pt.order_no = o.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
			o.updated_at
			) as effective_time
			from benefit_order o
			where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
			) v on year(v.effective_time) = year(current_date)
			and month(v.effective_time) = months.bucket_month
			group by months.bucket_month
			order by months.bucket_month
			""")