~ Tiez Notes ~

-- Data Position utk materna
select cu.customer_id, cu.customer_name, cu.customer_phone, pl.product_id, ch.child_expected_birthday, ca.calling_strike_id
from customer cu, calling ca, product_log pl, children ch
where cu.customer_id = ca.customer_id
          and ca.validation_id = '1'
          and cu.customer_id = pl.customer_id
          and cu.customer_id = ch.customer_id
          and pl.product_id = 'materna'
          and pl.consumed_by = cu.customer_id
          and cu.followup_status = '1'
          and cu.loyalty1_by <> " "
          -- and ca.calling_strike_id <> '12'       
          and ca.calling_result_id = '1'
          and ca.calling_for_id = '3'
          -- and ca.calling_strike_id in ('4','5','10','11','13','14','15')
          and (select ca2.calling_strike_id from calling ca2 where ca2.customer_id = cu.customer_id and ca2.phone_date = (select max(ca1.phone_date) from calling ca1 where ca1.customer_id = cu.customer_id) limit 1) not in ('12', '17')
          and ch.child_expected_birthday <> '0000-00-00'
          and cu.submit_date <= curdate()
          -- and cu.customer_id = '01080800030023'
group by cu.customer_id
order by cu.customer_id
-- limit 100

-- Data Position Anak dgn Produk
-- select aa.umur_anak, aa.product_id, count(aa.umur_anak) as jumlah
-- from
-- (
select ch.child_id, cu.customer_id, ch.child_name, ch.child_birthday,
         -- PERIOD_DIFF(DATE_FORMAT('2010-11-30','%Y%m'),DATE_FORMAT(ch.child_birthday,'%Y%m')) as umur_anak,
         ((datediff(curdate(), ch.child_birthday)) / 30)  as umur_anak,
         -- (case when PERIOD_DIFF(DATE_FORMAT(curdate(),'%Y%m'),DATE_FORMAT(ch.child_birthday,'%Y%m')) between 0 and 6 then 'IF1'
         --         when PERIOD_DIFF(DATE_FORMAT(curdate(),'%Y%m'),DATE_FORMAT(ch.child_birthday,'%Y%m')) between 7 and 12 then 'IF2'
         --         when PERIOD_DIFF(DATE_FORMAT(curdate(),'%Y%m'),DATE_FORMAT(ch.child_birthday,'%Y%m')) between 13 and 36 then 'ESS3'
         -- end) as product_id,
         -- (case when ((datediff(curdate(), ch.child_birthday)) / 30)  >= 0 and ((datediff(curdate(), ch.child_birthday)) / 30)  <= 6  then 'IF1'
         --          when ((datediff(curdate(), ch.child_birthday)) / 30)  > 6   and ((datediff(curdate(), ch.child_birthday)) / 30)  <= 12 then 'IF2'
         --          when ((datediff(curdate(), ch.child_birthday)) / 30)  > 12 and ((datediff(curdate(), ch.child_birthday)) / 30)  <= 36 then 'ESS3'
         --          when ((datediff(curdate(), ch.child_birthday)) / 30)  > 36 then 'ESS4'
         -- end) as product_id,
         pl.product_id,
         cl.calling_result_id,
         cu.customer_address, cu.customer_phone, cu.customer_phone_2
from children ch
inner join  customer cu  on ch.customer_id = cu.customer_id
inner join  calling cl on cl.customer_id = cu.customer_id
inner join product_log pl on pl.customer_id = cu.customer_id
where ((ch.child_birthday >= (date_sub(curdate(), interval 37 month)))
and (ch.child_birthday <= (date_sub(curdate(), interval 0 month))))
and (ch.child_birthday<> '0000-00-00' or ch.child_birthday like '0001-00-00')
and cl.validation_id='1'
and cu.followup_status = '1'
and cl.calling_result_id = '1'
and pl.consumed_by = ch.child_id
and (select ca2.calling_strike_id from calling ca2 where ca2.customer_id = cu.customer_id and ca2.phone_date = (select max(ca1.phone_date) from calling ca1 where ca1.customer_id = cu.customer_id) limit 1) not in ('6', '12', '16')
and pl.product_id in ('if1', 'if2', 'ess3', 'ess4')
group by ch.child_id
order by ch.child_id, ch.child_birthday
-- limit 60000,60000
-- ) aa
-- group by aa.umur_anak, aa.product_id
-- order by aa.umur_anak

select cu.customer_id as ID,cu.customer_name as NAMA,cu.customer_phone as PHONE1,cu.customer_phone_2 as PHONE2,cu.customer_address as ALAMAT, pl.product_id as PRODUK,cs.calling_strike_name,(select max(calling.phone_date)
from calling where calling.customer_id=cu.customer_id and calling.calling_target_name=pl.consumed_by) as telepon  from customer cu
inner join product_log pl on pl.customer_id=cu.customer_id
inner join calling ca on ca.customer_id=cu.customer_id
inner join calling_strike cs on cs.calling_strike_id=ca.calling_strike_id
where ca.calling_strike_id='10'
and pl.product_id='lacta'
and cu.submit_date<='2010-12-09'
and pl.consumed_by=cu.customer_id
and
(select ca2.calling_strike_id from calling ca2
where ca2.customer_id = cu.customer_id
and ca2.phone_date =
(select max(ca1.phone_date) from calling ca1 where ca1.customer_id = cu.customer_id) limit 1)
not in ('12', '17')
group by cu.customer_id
order by cu.customer_name

select aa.umur_anak, count(aa.umur_anak) as jumlah
from
(
select ch.child_id, cu.customer_id, ch.child_name, ch.child_birthday,
            PERIOD_DIFF(DATE_FORMAT('2010-11-30','%Y%m'),DATE_FORMAT(ch.child_birthday,'%Y%m')) as umur_anak, cl.calling_result_id,
            cu.customer_address, cu.customer_phone, cu.customer_phone_2
from children ch
inner join  customer cu  on ch.customer_id = cu.customer_id
inner join  calling cl on cl.customer_id = cu.customer_id
where ((ch.child_birthday >= (date_sub(curdate(), interval 37 month)))
and (ch.child_birthday <= (date_sub(curdate(), interval 0 month))))
and (ch.child_birthday<> '0000-00-00' or ch.child_birthday like '0001-00-00')
and cl.validation_id='1'
and cu.followup_status = '1'
and cl.calling_result_id = '1'
and (select ca2.calling_strike_id from calling ca2 where ca2.customer_id = cu.customer_id and ca2.phone_date = (select max(ca1.phone_date) from calling ca1 where ca1.customer_id = cu.customer_id) limit 1) not in ('12', '17')
group by ch.child_id
order by ch.child_id, ch.child_birthday
-- limit 60000,60000
) aa
group by aa.umur_anak
order by aa.umur_anak

-- Data Position utk materna
select cu.customer_id, cu.customer_name, cu.customer_phone, pl.product_id, ch.child_expected_birthday, ca.calling_strike_id
from customer cu, calling ca, product_log pl, children ch
where cu.customer_id = ca.customer_id
          and ca.validation_id = '1'
          and cu.customer_id = pl.customer_id
          and cu.customer_id = ch.customer_id
          and pl.product_id = 'materna'
          and pl.consumed_by = cu.customer_id
          and cu.followup_status = '1'
          and cu.loyalty1_by <> " "
          -- and ca.calling_strike_id <> '12'      
          and ca.calling_result_id = '1'
          and ca.calling_for_id = '3'
          -- and ca.calling_strike_id in ('4','5','10','11','13','14','15')
          and (select ca2.calling_strike_id from calling ca2 where ca2.customer_id = cu.customer_id and ca2.phone_date = (select max(ca1.phone_date) from calling ca1 where ca1.customer_id = cu.customer_id) limit 1) not in ('12', '17')
          and ch.child_expected_birthday <> '0000-00-00'
          and cu.submit_date <= curdate()
          -- and cu.customer_id = '01080800030023'
group by cu.customer_id
order by cu.customer_id

CREATE VIEW vUsiaAnakAll
as
SELECT ch.child_id, ch.child_name,ch.child_birthday,  ch.customer_id,PERIOD_DIFF(DATE_FORMAT(CURDATE(),'%Y%m'),DATE_FORMAT(ch.child_birthday,'%Y%m')) as umur
FROM children ch inner join customer cu using (customer_id)
WHERE cu.followup_status='1' and ((ch.child_birthday >= (date_sub(curdate(), interval 36 month)))
and (ch.child_birthday <= (date_sub(curdate(), interval 0 month))))
order by ch.child_birthday

select aa.umur_anak, count(aa.umur_anak) as jumlah
from
(
select distinct ch.child_id, cu.customer_id, ch.child_birthday,
            PERIOD_DIFF(DATE_FORMAT('2010-11-04','%Y%m'),DATE_FORMAT(ch.child_birthday,'%Y%m')) as umur_anak
from children ch
inner join  customer cu  on ch.customer_id = cu.customer_id
inner join  calling cl on cl.customer_id = cu.customer_id
where ((ch.child_birthday >= (date_sub(curdate(), interval 36 month)))
and (ch.child_birthday <= (date_sub(curdate(), interval 0 month))))
and (ch.child_birthday<> '0000-00-00' or ch.child_birthday like '0001-00-00')
and cl.validation_id='1'
and cu.followup_status = '1'
and cl.calling_strike_id in ('4','5','10','11','13','14','15')
order by ch.child_birthday
) aa
group by aa.umur_anak
order by aa.umur_anak

select aa.periode, aa.product_id, count(*) as JumlahLacta, sum(aa.0sd3bln) as 0sd3bln, sum(aa.3sd6bln) as 3sd6bln, sum(aa.6sd9bln) as 6sd9bln,
         sum(aa.9sd12bln) as 9sd12bln, sum(aa.TotOthers) as TotOthers, sum(aa.Success) as Success, sum(aa.NotSuccess) as NotSuccess,
         sum(aa.NotCalling) as NotCalling, sum(aa.NewData) as NewData,
         sum(aa.OldData) as OldData, sum(aa.Users) as Users, sum(aa.Prospects) as Prospects,
         sum(aa.DO) as TotalDO, sum(aa.DO0sd3bln) as DO0sd3bln, sum(aa.DO3sd6bln) as DO3sd6bln, sum(aa.DO6sd9bln) as DO6sd9bln, sum(aa.DO9sd12bln) as DO9sd12bln,
         sum(aa.DOlbh12) as OhtersDOLbh12,
         sum(aa.TotalFO) as TotalFO,
         sum(aa.FO0sd3bln) as FO0sd3bln, sum(aa.FO3sd6bln) as FO3sd6bln, sum(aa.FO6sd9bln) as FO6sd9bln, sum(aa.FO9sd12bln) as FO9sd12bln,
         sum(aa.FOlbh12) as OhtersFOLbh12,
         (sum(aa.Users) +  sum(aa.Prospects) + sum(aa.DO) + sum(aa.TotalFO)) as Total
from
(
select date_format(cu.submit_date,'%Y%m') as Periode, pl.product_id, pl.customer_id, cu.customer_name, cu.customer_address, cu.customer_phone, cu.customer_city,
         ca.phone_date, ca.calling_for_id, ca.calling_strike_id, ca.calling_target_id, (case when ca.calling_strike_id = '12' then 1 else 0 end) as DO,
         ca.calling_result_id, (case when ca.calling_result_id = '1' then 1 else 0 end) as Success, (case when ca.calling_result_id = '2' then 1 else 0 end) as NotSuccess,
         (case when ca.calling_result_id is null or '' then 1 else 0 end) as NotCalling,
         (case when date_format(cu.recruit_date,'%Y%m') = '201001' then 1 else 0 end) as NewData,
         (case when date_format(cu.recruit_date,'%Y%m') < '201001' then 1 else 0 end) as OldData,
         (case when ca.calling_strike_id = '10' then 1 else 0 end) as Users,
         (case when ca.calling_strike_id = '13' then 1 else 0 end) as Prospects,
         ch.child_name, ch.child_birthday, period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) as selisih_bulan,
         (case when period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 0 and 3 then 1 else 0 end) as 0sd3bln,
         (case when period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 4 and 6 then 1 else 0 end) as 3sd6bln,
         (case when period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 7 and 9 then 1 else 0 end) as 6sd9bln,
         (case when period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 10 and 12 then 1 else 0 end) as 9sd12bln,
         (case when period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) > 12 then 1 end) as TotOthers,
         (case when ca.calling_strike_id = '12' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 0 and 3 then 1 else 0 end) as DO0sd3bln,
         (case when ca.calling_strike_id = '12' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 4 and 6 then 1 else 0 end) as DO3sd6bln,
         (case when ca.calling_strike_id = '12' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 7 and 9 then 1 else 0 end) as DO6sd9bln,
         (case when ca.calling_strike_id = '12' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 10 and 12 then 1 else 0 end) as DO9sd12bln,
         (case when ca.calling_strike_id = '12' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) > 12 then 1 else 0 end) as DOlbh12,
         (case when ca.calling_strike_id = '14' then 1 else 0 end) as TotalFO,
         (case when ca.calling_strike_id = '14' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 0 and 3 then 1 else 0 end) as FO0sd3bln,
         (case when ca.calling_strike_id = '14' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 4 and 6 then 1 else 0 end) as FO3sd6bln,
         (case when ca.calling_strike_id = '14' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 7 and 9 then 1 else 0 end) as FO6sd9bln,
         (case when ca.calling_strike_id = '14' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) between 10 and 12 then 1 else 0 end) as FO9sd12bln,
         (case when ca.calling_strike_id = '14' and period_diff(date_format(curdate(),'%Y%m'), date_format(ch.child_birthday, '%Y%m')) > 12 then 1 else 0 end) as FOlbh12
from product_log pl, customer cu, calling ca, children ch
where pl.product_id = 'lacta' and date_format(cu.submit_date,'%Y%m') = '201001' and cu.followup_status = '1' and
          pl.customer_id = cu.customer_id and pl.customer_id = ca.customer_id and
          ca.calling_target_id = '1' and pl.customer_id = ch.customer_id and ch.child_birthday <> ' ' and
          ch.child_birthday > '0000-00-00' and ca.calling_strike_id > 0
order by pl.customer_id
) as aa
group by aa.periode, aa.product_id
order by aa.periode

select a.product_id, a.customer_id, b.customer_name, b.customer_city, c.child_name, c.child_number, round(((datediff(current_date(), c.child_birthday)) / 30)) as UmurBln,
         (case when d.calling_result_id = '1' then 'Success'
                  when d.calling_result_id = '2' then 'Not Success' end) CallingResult,
         d.calling_strike_id
from product_log a, customer b, children c, calling d
where a.customer_id = b.customer_id and
          a.customer_id = c.customer_id and
          a.customer_id = d.customer_id and
          a.product_id = 'IF1' and
         round(((datediff(current_date(), c.child_birthday)) / 30)) between '0' and '2' and
         d.calling_result_id = '1' and d.calling_strike_id in ( '1', '10') and
         a.input_date <= '2010-09-17'
order by a.customer_id;