delete from ccmanager where pid in (1,101,102,103,104,301);

set names utf8;

/* FAMILY EXPENSES 2017 */
/* BUDGET */
insert into ccmanager (code_center,code_categ,rab_date,rab_qty,rab_number,mid,pid)
select a.code_center,a.code_categ,'2017-01-01',a.qty,a.budget,1,a.pid
from ademo a
where a.pid=1;

/* EXPENSES */
insert into ccmanager (code_center,code_categ,rab_date,rab_qty,rab_number,mid,pid)
select a.code_center,a.code_categ,c.calday,a.qty*0.055,a.budget*0.055,2,a.pid
from ademo a, calendar c
where date_format(c.calday,'%d') in ('25')
and date_format(c.calday,'%Y') = '2017'
and a.pid=1;



/* EQUIPMENTY CONTROL pid=103 */
delete from ccmanager where pid = 103;

/* BUDGET */
insert into ccmanager (code_center,code_categ,rab_date,rab_qty,rab_number,mid,pid)
select a.code_center,a.code_categ,'2019-01-01',a.qty,a.budget,1,a.pid
from ademo a
where a.pid=103;

/* 1000 FUEL EXPENSES INCLUDING UPDATE FOR UMC AS KMS, HRS OR MILES */
insert into ccmanager (code_center,code_categ,rab_date,rab_qty,rab_number,mid,pid)
select a.code_center,a.code_categ,c.calday,a.qty*0.012,a.budget*0.012,2,a.pid
from ademo a, calendar c
where date_format(c.calday,'%d') in ('01','04','12','20')
and date_format(c.calday,'%Y') = '2019'
and a.pid=103
and a.code_categ in (select code_categ from categs where pid = 103 and code_categ_group = '0100');

/* 2000 SERVICE EXPENSES INCLUDING UPDATE FOR UMC AS KMS, HRS OR MILES */
insert into ccmanager (code_center,code_categ,rab_date,rab_qty,rab_number,mid,pid)
select a.code_center,a.code_categ,c.calday,a.qty*0.65,a.budget*0.45,2,a.pid
from ademo a, calendar c
where date_format(c.calday,'%d-%m') in ('01-05','10-11')
and date_format(c.calday,'%Y') = '2019'
and a.pid=103
and a.code_categ in (select code_categ from categs where pid = 103 and code_categ_group = '0200');

/* 3000 TYRES EXPENSES INCLUDING UPDATE FOR UMC AS KMS, HRS OR MILES */
insert into ccmanager (code_center,code_categ,rab_date,rab_qty,rab_number,mid,pid)
select a.code_center,a.code_categ,c.calday,a.qty,a.budget,2,a.pid
from ademo a, calendar c
where date_format(c.calday,'%d-%m') in ('12-12')
and date_format(c.calday,'%Y') = '2019'
and a.pid=103
and a.code_categ in (select code_categ from categs where pid = 103 and code_categ_group = '0300');

/* 4000 LUBRICANTS EXPENSES INCLUDING UPDATE FOR UMC AS KMS, HRS OR MILES */
insert into ccmanager (code_center,code_categ,rab_date,rab_qty,rab_number,mid,pid)
select a.code_center,a.code_categ,c.calday,a.qty*0.49,a.budget*0.49,2,a.pid
from ademo a, calendar c
where date_format(c.calday,'%d-%m') in ('12-06','19-12')
and date_format(c.calday,'%Y') = '2019'
and a.pid=103
and a.code_categ in (select code_categ from categs where pid = 103 and code_categ_group = '0400');

SELECT @i:=0;
update ccmanager set rab_umc = @i:=@i+600 where pid = 103 and mid = 2 and code_center in (select code_center from centers where pid=103 and code_center_group = '1000') ORDER BY code_center,rab_date,cid;

SELECT @i:=0;
update ccmanager set rab_umc = @i:=@i+800 where pid = 103 and mid = 2 and code_center in (select code_center from centers where pid=103 and code_center_group = '2000') ORDER BY code_center,rab_date,cid;

SELECT @i:=0;
update ccmanager set rab_umc = @i:=@i+500 where pid = 103 and mid = 2 and code_center in (select code_center from centers where pid=103 and code_center_group = '3000') ORDER BY code_center,rab_date,cid;



/* TAXI COMPANY 2017 */
/* TAXI 2017 pid=301 */
/* BUDGET */
insert into ccmanager (code_center,code_categ,rab_date,rab_qty,rab_number,mid,pid)
select a.code_center,a.code_categ,'2017-01-01',a.qty,a.budget,1,a.pid
from ademo a
where a.pid=301;

/* EXPENSES NOT SALARIES */
insert into ccmanager (code_center,code_categ,rab_date,rab_qty,rab_number,mid,pid)
select a.code_center,a.code_categ,c.calday,a.qty/a.qdiv,a.budget/a.qdiv,2,a.pid
from ademo a, calendar c
where date_format(c.calday,'%d') in ('02','05','12','22','26')
and date_format(c.calday,'%Y') = '2017'
and a.code_center not in ('5001','5002')
and a.pid=301;

delete from ccmanager where date_format(rab_date,'%d%m') in ('2202','2604','2212','2212','1211','2211','2611') and pid = 301;

/* EXPENSES SALARIES and OFFICE MAINTENANCE */
insert into ccmanager (code_center,code_categ,rab_date,rab_qty,rab_number,mid,pid)
select a.code_center,a.code_categ,c.calday,a.qty,a.budget/a.qdiv,2,a.pid
from ademo a, calendar c
where date_format(c.calday,'%d') in ('03')
and date_format(c.calday,'%Y') = '2017'
and a.code_center in ('5001','5002')
and a.pid=301;

update ccmanager set rab_number = rab_number + 10000 where pid = 301 and mid = 2 and code_center = '5001' and code_categ = '0504';
update ccmanager set rab_number = rab_number * 0.95  where pid = 301 and mid = 2 and code_center = '5002';

/* UPDATE FOR COSTS TO CHANGE VALUES PER MONTH */
update ccmanager set rab_number = rab_number * 1.05 where mid=2 and date_format(rab_date,'%m') IN ('01','03','06') and pid in (1,103);
update ccmanager set rab_number = rab_number * 0.95 where mid=2 and date_format(rab_date,'%m') IN ('02','05','08') and pid in (1,103);
update ccmanager set rab_number = rab_number * 0.75 where mid=2 and date_format(rab_date,'%m') IN ('04','07','10') and pid in (1,103);



/* !!!!! INSERT ALL FOR 301 TO 302 (GREEK) !!!! */
insert into ccmanager (code_center,code_categ,rab_date,rab_qty,rab_number,rab_umc,mid,pid)
select code_center,code_categ,rab_date,rab_qty,rab_number,rab_umc,mid,302
from ccmanager
where pid = 301;

/* !!!!! INSERT ALL FOR 103 TO 104 (GREEK) !!!! */
insert into ccmanager (code_center,code_categ,rab_date,rab_qty,rab_number,rab_umc,mid,pid)
select code_center,code_categ,rab_date,rab_qty,rab_number,rab_umc,mid,104
from ccmanager
where pid = 103;

/* INVOICE UPDATES FOR 103 */
update ccmanager a set a.invoice = concat('Invoice 3540')
where a.mid in (select x.mid from ccmanager_id x where x.expenses=1 and x.pid = a.pid)
and a.pid in (103)
and a.invoice is null
and a.code_categ like '012%';

update ccmanager a set a.invoice = concat('Invoice 3541')
where a.mid in (select x.mid from ccmanager_id x where x.expenses=1 and x.pid = a.pid)
and a.pid in (103)
and a.code_categ like '012%'
order by RAND()
limit 100;

update ccmanager a set a.invoice = concat('Invoice 3542')
where a.mid in (select x.mid from ccmanager_id x where x.expenses=1 and x.pid = a.pid)
and a.pid in (103)
and a.code_categ like '012%'
order by RAND()
limit 100;

update ccmanager a set a.invoice = concat('Inv.-0005321')
where a.mid in (select x.mid from ccmanager_id x where x.expenses=1 and x.pid = a.pid)
and a.pid in (103)
and a.invoice is null
and a.code_categ like '011%';

update ccmanager a set a.invoice = concat('Inv.-12345')
where a.mid in (select x.mid from ccmanager_id x where x.expenses=1 and x.pid = a.pid)
and a.pid in (103)
and a.invoice is null
and a.code_categ like '0401';

/* INVOICE UPDATES FOR 104 */
update ccmanager a set a.invoice = concat('Τιμολόγιο 3540')
where a.mid in (select x.mid from ccmanager_id x where x.expenses=1 and x.pid = a.pid)
and a.pid in (104)
and a.invoice is null
and a.code_categ like '012%';

update ccmanager a set a.invoice = concat('Τιμολόγιο 3541')
where a.mid in (select x.mid from ccmanager_id x where x.expenses=1 and x.pid = a.pid)
and a.pid in (104)
and a.code_categ like '012%'
order by RAND()
limit 100;

update ccmanager a set a.invoice = concat('Τιμολόγιο 3542')
where a.mid in (select x.mid from ccmanager_id x where x.expenses=1 and x.pid = a.pid)
and a.pid in (104)
and a.code_categ like '012%'
order by RAND()
limit 100;

update ccmanager a set a.invoice = concat('Τιμ.-00054321')
where a.mid in (select x.mid from ccmanager_id x where x.expenses=1 and x.pid = a.pid)
and a.pid in (104)
and a.invoice is null
and a.code_categ like '011%';

update ccmanager a set a.invoice = concat('Τιμ.-12345')
where a.mid in (select x.mid from ccmanager_id x where x.expenses=1 and x.pid = a.pid)
and a.pid in (104)
and a.invoice is null
and a.code_categ like '0401';


/* INVOICE UPDATES FOR OTHERS */
update ccmanager a set a.invoice = concat('Inv.-',a.cid)
where a.mid in (select x.mid from ccmanager_id x where x.expenses=1 and x.pid = a.pid)
and a.pid in (1,101,102,301);

update ccmanager a set a.invoice = null where pid = 301 and code_center in ('5001');

update ccmanager set comms_html = "<p>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse quis interdum sapien, vitae convallis ante.<br></p>" where pid in (1,101,102,103,104,301);

update ccmanager set xuid="st" where pid in (1,101,102,103,104,301);

/*
create or replace view ademov as (
select a.pid,p.desc_project,a.code_center,c.desc_center,c.comm_center,a.code_categ,k.desc_categ,a.budget,a.qty,(a.budget/a.qty) as unit_cost
from ademo a,centers c,categs k, ccprojects p
where a.pid = c.pid and a.code_center = c.code_center
and a.pid = k.pid and a.code_categ = k.code_categ
and a.pid = p.pid)

*/