01 TABLE SMSM
=====================================================================================================================

drop table SMSCONFIG cascade constraints;
CREATE TABLE SMSCONFIG (
SENDER_NUMBER VARCHAR2(20) NOT NULL
,SENDER_CODE VARCHAR2(30) NOT NULL
, PHONENETWORK VARCHAR2(30) NOT NULL
, ENABLE_FLAG NUMBER(1,0) DEFAULT 1 NOT NULL
, USER_NAME VARCHAR2(20) NOT NULL
, USER_PWD VARCHAR2(20) NOT NULL
, URL VARCHAR2(150) NULL
, URL_PARAMS VARCHAR2(255) NULL) ;
ALTER TABLE SMSCONFIG ADD ( CONSTRAINT smsconfig_pk PRIMARY KEY ( SENDER_NUMBER,SENDER_CODE, PHONENETWORK )) ;


drop table SMSPATTERNCONFIG cascade constraints;
CREATE TABLE SMSPATTERNCONFIG (
SMS_TRANS_CODE VARCHAR2(10) NOT NULL
, SMS_PATTERN VARCHAR2(500) NOT NULL
, FROM_SYSTEM VARCHAR2(500) NOT NULL
, ENABLE_FLAG NUMBER(1,0) DEFAULT 1 NOT NULL
, SMS_TRANS_SQL VARCHAR2(500) NOT NULL
) ;
ALTER TABLE SMSPATTERNCONFIG ADD ( CONSTRAINT smspatternconfig_pk PRIMARY KEY ( SMS_TRANS_CODE, FROM_SYSTEM )) ;

drop table SMSTRANSACTION cascade constraints;
CREATE TABLE SMSTRANSACTION (
REF_NO VARCHAR2(100) NOT NULL
, SEQ_NO NUMBER(6,0) NOT NULL
, MEMBER_NO VARCHAR2(10) NOT NULL
, TELEPHONE_NUMBER VARCHAR2(20) NOT NULL
, MESSAGE_TEXT VARCHAR2(255) NOT NULL
, CREATE_DATE DATE NOT NULL
, SEND_DATE DATE NOT NULL
, MESSAGE_STATUS NUMBER(1,0) DEFAULT 1 NOT NULL
, FROM_SYSTEM VARCHAR2(10)
, SMS_TRANS_CODE VARCHAR2(10)
, POST_FLAG NUMBER(1,0) DEFAULT 1 NOT NULL) ;
ALTER TABLE SMSTRANSACTION ADD ( CONSTRAINT SMSTRANSACTION_PK PRIMARY KEY ( REF_NO, SEQ_NO )) ;

http://corpsms.dtac.co.th/servlet/com.iess.socket.SmsCorplink

public String setPostdataThaiUnicode(String user, String password, String Msn, int i, String Sno){
String MsgDemo = “0e2a0e270e310e2a0e140e35”;
String postData = &RefNo= + Integer.toString(i);
postData += &Sender= + URLEncoder.encode(“I am your sender”);
postData += &Msn=+Msn;
postData += &Sno=+Sno;
postData += &MsgType=H”;
postData += &Msg=+ URLEncoder.encode(MsgDemo);
postData += &Encoding=25”;
postData += &User=+ URLEncoder.encode(user);
postData += &Password=+ URLEncoder.encode(password);
return postData;
}

=====================================================================================================================
1.จ่ายเงินกู้

create or replace view SMS_PATTERN_L01 as 
select q.* from (
select l.loancontract_no as loancontract_no
,sl.slip_date as send_date
,sl.slip_date as slip_date
,to_char(sl.slip_date,'dd/MM/')||(to_number(to_char(sl.slip_date,'yyyy'))+543) as slip_date_str
,l.member_no as member_no
,replace(NVL(m.addr_mobilephone,m.addr_phone),'-') as phone_number
,l.loanapprove_amt as loanapprove_amt,replace(to_char(l.loanapprove_amt,'999,999,990.00'),' ','') as loanapprove_amt_str 
,sl.payoutnet_amt as payoutnet_amt,replace(to_char(sl.payoutnet_amt,'999,999,990.00'),' ','') as payoutnet_amt_str  
from slslippayout sl ,lncontmaster l ,mbmembmaster m
where
 l.loancontract_no = sl.loancontract_no and 
 m.member_no = l.member_no and
 sl.payoutnet_amt > 0 and 
 l.contract_status = 1 
) q where q.phone_number is not null and length(q.phone_number) = 10
order by member_no asc 
;

'อนุมัติจ่ายเงินกู้  '||loanapprove_amt_str||' บ.รับสุทธิ  '||payoutnet_amt_str||' บ. วันที่  '||slip_date_str

select * from SMS_PATTERN_L01 where to_char(send_date,'yyyyMMdd')='20160322' ;

=====================================================================================================================
2.อนุมัติเงินกู้

create or replace view SMS_PATTERN_L02 as 
select q.* from (
select l.loancontract_no as loancontract_no
,l.loanapprove_date as send_date
,l.loanapprove_date as loanapprove_date
,to_char(l.loanapprove_date,'dd/MM/')||(to_number(to_char(l.loanapprove_date,'yyyy'))+543) as loanapprove_date_str
,l.member_no as member_no
,replace(NVL(m.addr_mobilephone,m.addr_phone),'-') as phone_number
,l.loanapprove_amt as loanapprove_amt,replace(to_char(l.loanapprove_amt,'999,999,990.00'),' ','') as loanapprove_amt_str 
from lncontmaster l ,mbmembmaster m
where
 m.member_no = l.member_no and
 l.contract_status = 1 
) q where q.phone_number is not null and length(q.phone_number) = 10
order by member_no asc 
;

'สัญญาเงินกู้  '||loancontract_no||' ได้รับการอนุมัติแล้ว  '||loanapprove_amt_str||' บ. วันที่  '||loanapprove_date_str

select * from SMS_PATTERN_L02 where to_char(send_date,'yyyyMMdd')='20160223';

3.รับฝากเงินฝาก 

create or replace view SMS_PATTERN_D01 as 
select q.* from (
select d.deptaccount_no
,d.deptslip_date as send_date
,d.deptslip_date as deptslip_date
,to_char(d.deptslip_date,'dd/MM/')||to_char(to_number(to_char(d.deptslip_date,'yyyy'))+543) as deptslip_date_str
,dm.member_no as member_no
,replace(NVL(m.addr_mobilephone,m.addr_phone),'-') as phone_number
,d.deptslip_amt ,replace(to_char(d.deptslip_amt,'999,999,990.00'),' ','') as deptslip_amt_str 
,dc.recppaytype_desc 
from dpdeptslip d , dpucfrecppaytype dc , dpdeptmaster dm ,mbmembmaster m
where
 d.deptaccount_no = dm.deptaccount_no and 
 dc.recppaytype_code =d.recppaytype_code and 
 m.member_no = dm.member_no and
 dc.recppaytype_flag >0 
) q where q.phone_number is not null and length(q.phone_number) = 10
order by member_no asc 
;

'รับฝากเงิน '||deptslip_amt_str||' บ.เข้าบัญชีเลขที่  '||deptaccount_no||' วันที่  '||deptslip_date_str

select * from SMS_PATTERN_D01 where to_char(send_date,'yyyyMMdd')='20160128';

4.ถอนเงินฝาก 

create or replace view SMS_PATTERN_D02 as 
select q.* from (
select d.deptaccount_no
,d.deptslip_date as send_date
,d.deptslip_date as deptslip_date
,to_char(d.deptslip_date,'dd/MM/')||to_char(to_number(to_char(d.deptslip_date,'yyyy'))+543) as deptslip_date_str
,dm.member_no as member_no
,replace(NVL(m.addr_mobilephone,m.addr_phone),'-') as phone_number
,d.deptslip_amt ,replace(to_char(d.deptslip_amt,'999,999,990.00'),' ','') as deptslip_amt_str 
,dc.recppaytype_desc 
from dpdeptslip d , dpucfrecppaytype dc , dpdeptmaster dm ,mbmembmaster m
where
 d.deptaccount_no = dm.deptaccount_no and 
 dc.recppaytype_code =d.recppaytype_code and 
 m.member_no = dm.member_no and
 dc.recppaytype_flag <0 
) q where q.phone_number is not null and length(q.phone_number) = 10
order by member_no asc 
;

'ถอนเงินฝาก '||deptslip_amt_str||' บ.บัญชีเลขที่ '||deptaccount_no||' วันที่ '||deptslip_date_str

select * from SMS_PATTERN_D02 where to_char(send_date,'yyyyMMdd')='20160128';