통계

>>통계테이블<<

CREATE TABLE `t_msg_statistics` (

`SEND_DT` date NOT NULL COMMENT '발송 일자',

`UNIT_ID` int(11) NOT NULL COMMENT 'UNIT의 ID (기본 : 0)',

`KAKAO_CNT` bigint(20) unsigned DEFAULT '0' COMMENT 'KAKAO_CNT발송건수, msg_type:7',

`SMS_CNT` bigint(20) unsigned DEFAULT '0' COMMENT 'SMS발송건수, msg_type:4',

`LMS_MMS_CNT` bigint(20) unsigned DEFAULT '0' COMMENT 'LMS, MMS 발송건수, msg_type:6',

PRIMARY KEY (`SEND_DT`,`UNIT_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

;

 

>>통계테이블에 값 넣기<<

insert into T_MSG_STATISTICS (SEND_DT, UNIT_ID, SMS_CNT, LMS_MMS_CNT, KAKAO_CNT)

select T.send_date

, T.unit_id

, COALESCE(sum(case when T.msg_type = 4 then cnt end),0) as sms_cnt

, COALESCE(sum(case when T.msg_type = 6 then cnt end),0) as lms_mms_cnt

, COALESCE(sum(case when T.msg_type = 7 then cnt end),0) as kakao_cnt

from (

select unit_id, DATE_FORMAT(SEND_TIME, '%Y-%m-%d') as send_date, msg_type, count(*) cnt

from MSG_LOG_202009 ML

group by unit_id, DATE_FORMAT(SEND_TIME, '%Y-%m-%d'), msg_type

-- HAVING unit_id = 66000

) T

group by T.unit_id, T.send_date

;

 

insert into T_MSG_STATISTICS (SEND_DT, UNIT_ID, SMS_CNT, LMS_MMS_CNT, KAKAO_CNT)

select T.send_date

, T.unit_id

, COALESCE(sum(case when T.msg_type = 4 then cnt end),0) as sms_cnt

, COALESCE(sum(case when T.msg_type = 6 then cnt end),0) as lms_mms_cnt

, COALESCE(sum(case when T.msg_type = 7 then cnt end),0) as kakao_cnt

from (

select unit_id, DATE_FORMAT(SEND_TIME, '%Y-%m-%d') as send_date, msg_type, count(*) cnt

from MSG_LOG_202008 ML

group by unit_id, DATE_FORMAT(SEND_TIME, '%Y-%m-%d'), msg_type

) T

group by T.send_date, T.unit_id

;

 

insert into T_MSG_STATISTICS (SEND_DT, UNIT_ID, SMS_CNT, LMS_MMS_CNT, KAKAO_CNT)

select T.send_date

, T.unit_id

, COALESCE(sum(case when T.msg_type = 4 then cnt end),0) as sms_cnt

, COALESCE(sum(case when T.msg_type = 6 then cnt end),0) as lms_mms_cnt

, COALESCE(sum(case when T.msg_type = 7 then cnt end),0) as kakao_cnt

from (

select unit_id, DATE_FORMAT(SEND_TIME, '%Y-%m-%d') as send_date, msg_type, count(*) cnt

from MSG_LOG_202007 ML

group by unit_id, DATE_FORMAT(SEND_TIME, '%Y-%m-%d'), msg_type

) T

group by T.send_date, T.unit_id

;

 

select SEND_DT, UNIT_ID, SMS_CNT, LMS_MMS_CNT, KAKAO_CNT from t_msg_statistics

;

 

 

 

 

select count(*) from T_MSG_STATISTICS MS

where 1=1 --

and UNIT_ID = 66000

and MS.SEND_DT BETWEEN '2020-09-01' and '2020-09-30'

;

 

delete from T_MSG_STATISTICS

where UNIT_ID = 66000

and SEND_DT BETWEEN '2020-09-01' and last_day('2020-09-01')

;

 

select count(*) from MSG_LOG_202009 MS

where UNIT_ID = 66000

and MS.SEND_TIME BETWEEN '2020-08-01' and last_day('2020-08-01')

;

insert into T_MSG_STATISTICS (SEND_DT, UNIT_ID, SMS_CNT, LMS_MMS_CNT, KAKAO_CNT)

select T.send_date

, T.unit_id

, COALESCE(sum(case when T.msg_type = 4 then cnt end),0) as sms_cnt

, COALESCE(sum(case when T.msg_type = 6 then cnt end),0) as lms_mms_cnt

, COALESCE(sum(case when T.msg_type = 7 then cnt end),0) as kakao_cnt

from (

select ML.unit_id, DATE_FORMAT(ML.SEND_TIME, '%Y-%m-%d') as send_date, ML.msg_type, count(*) cnt

from MSG_LOG_202009 ML

group by ML.unit_id, DATE_FORMAT(ML.SEND_TIME, '%Y-%m-%d'), ML.msg_type

having ML.unit_id = 66000

) T

group by T.send_date, T.unit_id

;

 

select last_day('2020-08-01') /* 2020-08-31 */

;

 

 

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

 

>>알림톡관리<<

 

/STUDY_CAFE_WEB/src/main/webapp/WEB-INF/jsp/web/sms/smsTemplateList.jsp

sms = {}

ajax호출 후 smsType change 이벤트 callback함수 호출할 때 파라메터 조합은 콜백함수 내에서 한다.

 

 

https://topic.alibabacloud.com/ko/article/javascript-gets-the-cursor-position-code-of-the-textarea-element_3_14_2003294.html