﻿/*--create table cus_curr_bk as select * from cus_curr

    -- save transactions table
    */
    create table if not exists transactions_h as select * from transactions where 1=2;
    insert into transactions_h select * from transactions a where  strftime('%Y-%m-%d',substr(a.date_,7,4)||'-' ||substr(a.date_,4,2)||'-'||substr(a.date_,1,2) )<=date('now','localtime','start of year','-1 day');

    /*-- save bills table*/
    create table if not exists bills_h as select * from bills where 1=2;
    insert into bills_h select * from bills a where  date(a.date_)<=date('now','localtime','start of year','-1 day');

    /*-- save bills_transactions table*/
    create table if not exists bill_transactions_h as select * from bill_transactions where 1=2;
    insert into bill_transactions_h select b.* from bills a,bill_transactions b where  a.id=b.bill_id and  date(a.date_)<=date('now','localtime','start of year','-1 day');

    /*-- save items table*/
    create table if not exists items_h as select * from items where 1=2;
    /*--delete from items_h --NOT RESET...it will be append into table...will be considered in items_cost_calc_v  opening bal transactions*/
    insert into items_h select * from items a where  date(a.o_date)<=date('now','localtime','start of year','-1 day') and a.id not in (select id from items_h);

    /*--to be feth customer balance till 31-12*/
    drop table if exists cus_curr_temp;
    create table if not exists cus_curr_temp as select * from cus_curr where 1=2;
    /*--reset table*/
    delete from cus_curr_temp;
    insert into cus_curr_temp
    SELECT a.b_id as ID, a.b_name as name,a.gsm gsm ,a.c_id as g_id, a.c_name g_name ,a.f_id as f_id  ,a.f_name,
    sum(case when not(a.[in] = 1 and (a.t_cus_id!=a.b_id or a.t_cus_id is null)) then a.out else 0 end )*1.0 as cr ,
    sum  ( case when a.[in] = 1 and (a.t_cus_id!=a.b_id or a.t_cus_id is null) then a.out else 0 end)*1.0 as db ,
    ifnull( (sum(case when (a.t_cus_id=a.b_id) then -1*a.out else  (a.[in]*a.out) end)),0) AS balance,
    ifnull( a.d_name, (select name from currency where id=0))  curr
    ,a.curr_id as curr_id,a.gsm as phone
    ,max(strftime('%Y-%m-%d',substr(a.date_,7,4)||'-' ||substr(a.date_,4,2)||'-'||substr(a.date_,1,2) )) d
    ,( (select julianday(strftime('%Y-%m-%d','now'))
    -julianday(max(strftime('%Y-%m-%d',substr(c.date_,7,4)||'-'
    ||substr(c.date_,4,2)||'-'||substr(c.date_,1,2) )))
    from transactions as c where (c.cus_id=a.b_id or c.t_cus_id=a.b_id)) ) as days_late,
    ifnull(max(a.a_id),0) max_id,count(*) cnt
    FROM  cus_tr_curr_view a
    where strftime('%Y-%m-%d',substr(a.date_,7,4)||'-' ||substr(a.date_,4,2)||'-'||substr(a.date_,1,2) )<=date('now','localtime','start of year','-1 day')
    GROUP BY a.b_name, a.b_id,a.d_name,a.d_id,a.c_id,a.c_name,a.f_id,a.f_name,a.gsm,a.curr_id,a.d_name;

    /*-- to be fetch items balance on 31-12*/
     drop table if  exists items_cost_calc_temp;
    create table if not exists items_cost_calc_temp as select * from items_cost_calc where 1=2;
    /*--RESET TABLE*/
    delete from items_cost_calc_temp ;
    insert into items_cost_calc_temp
    select a.*
    from items_cost_calc a where date(a.date_)<=date('now','localtime','start of year','-1 day')
    and a.rowid=( select max(e.rowid) from items_cost_calc e where e.item_id=a.item_id and e.br_id=a.br_id  and e.date_<=date('now','localtime','start of year','-1 day'));

    /*--RESET Opening balace in items table*/
    update items set
    o_cost=0
    ,o_qty=0 ;
    /*-- Then it should be not updated as transactions/bills tables*/
    /*--update bills_transactions of start date of year to be in 2nd day of year*/
    	update bills
    	set date_=date(date_,'+1 day')
    	where date_=strftime('%Y-%m-%d',date('now','localtime','start of year')) ;
    	update transactions
                    	set date_=strftime('%d-%m-%Y',date(date(strftime('%Y-%m-%d',substr( date_,7,4)||'-' ||substr( date_,4,2)||'-'||substr( date_,1,2) )),'+1 day'))
                    	where date(strftime('%Y-%m-%d',substr( date_,7,4)||'-' ||substr( date_,4,2)||'-'||substr( date_,1,2) ))
                    	=strftime('%Y-%m-%d',date('now','localtime','start of year')) ;
    /*-- ADD opening balance as Adj inv (increase)
    --BIll HEADER
    */
      drop table if  exists items_cost_temp_h;
    	create table if not exists items_cost_temp_h (id integer primary key autoincrement,br_id integer,tot real);
    	delete from items_cost_temp_h;
    	insert into items_cost_temp_h(br_id,tot)
    	select br_id, sum(n_q*n_u)  tot from items_cost_calc_temp
    	group by br_id
    	having sum(n_q*n_u)!=0;
        WITH RECURSIVE
        fibo (id,br_id, tot)
        as
        (
        select id, br_id,tot from items_cost_temp_h
        )
        insert into bills (id,tr_type,date_,amount,br_id,adj_id,tran_status,bill_type
        ,bill_no2,remarks,curr_id,bill_no)
        select  (select ifnull(max(a.id),0)+fibo.id   as _id from bills a ),4,date('now','localtime','start of year'),tot,br_id,4,2,0,
        (select ifnull(max(a.bill_no2),0)+1   as _id from bills a
        where a.tr_type=4 and a.is_back=0 and a.br_id=fibo.br_id),
        'رصيد افتتاحي',
        0,0
        from fibo;
    /*--Bill Details*/
    WITH RECURSIVE
    fibo (item_id,item_type_id,qty,cost_price,br_id)
    as
    (
   select a.item_id,b.item_type_id,round(a.n_q,7) qty,a.n_u cost_price,a.br_id from items_cost_calc_temp a,items b where a.item_id=b.id
       and round(a.n_q,7)!=0
    )
    insert into bill_transactions(bill_id,item_id,item_type_id,qty,qty_pr,qty_t,cost_price,curr_id)
    select  (select ifnull(max(a.id),0)   as _id from bills a where a.tr_type=4 and a.br_id=fibo.br_id ),item_id,item_type_id,
    qty,qty,qty,cost_price,0
    from fibo;

    /*--Opening balance in transactions table without id(-13,-14)		*/
    insert into transactions(cus_id,[in],out,date_,remarks,bill_id,curr_id)
    select id , (case when sum(balance)<0 then -1 else 1 end )  in_
    ,abs(sum(balance)),strftime('%d-%m-%Y',date('now','localtime','start of year')) date_,'رصيد افتتاحي'
    as remarks,-1 bill_id,curr_id
    from cus_curr_temp
    where id not in (-14,-13)
    and (f_id like '1%' or f_id like '2%')
    group by id,name,curr_id,f_id
    having sum(balance)<>0;

/*--OPening balance of NET Revenue*/
    WITH RECURSIVE
    fibo ( tot)
    as
    (
    select (ifnull(abs( sum(case when (case when (a.t_cus_id=a.b_id) then -1*a.out_ else
    (a.[in]*a.out_) end)>= 0 then 0 else  (case when (a.t_cus_id=a.b_id) then -1*a.out_ else
    (a.[in]*a.out_) end)  end )),0)
    -ifnull(abs(sum(case when (case when (a.t_cus_id=a.b_id) then -1*a.out_ else
    (a.[in]*a.out_) end)>= 0 then  (case when (a.t_cus_id=a.b_id) then -1*a.out_ else
    (a.[in]*a.out_) end) else 0 end )),0))
    +ifnull((select sum(n_q*n_u )
    from items_cost_calc a
      where date(a.date_)<=date('now','localtime','start of year','-1 day')
    and a.rowid=( select max(e.rowid) from items_cost_calc e where e.item_id=a.item_id and e.br_id=a.br_id  and e.date_<=date('now','localtime','start of year','-1 day'))) ,0)
    +ifnull((select sum(n_q*n_u)*-1  from items_cost_calc a where tr_type=0 or(tr_type=4 and adj_id=4)
    and a.date_<=date('now','localtime','start of year','-1 day')),0)
    FROM  cus_tr_curr_view a
    where strftime('%Y-%m-%d',substr(a.date_,7,4)||'-' ||substr(a.date_,4,2)||'-'||substr(a.date_,1,2) )<=date('now','localtime','start of year','-1 day')
    and (a.f_id like '3%' or a.f_id like '4%')
    )
    insert into transactions(cus_id,t_cus_id,[in],out,date_,remarks,bill_id,curr_id)
    select(case when tot>0 then -13 else -17 end),(case when tot>0 then -17 else -13 end),1,tot,
    strftime('%d-%m-%Y',date('now','localtime','start of year')) date_,'رصيد افتتاحي'
    ,0,0	 from fibo
;


/*-- delete data from bill_transactions and bills*/
delete from bill_transactions where bill_id in (select id from bills a where  date(a.date_)<=date('now','localtime','start of year','-1 day') );
delete from bills where  date(date_)<=date('now','localtime','start of year','-1 day');
/*--delete from transactions table*/
delete from transactions   where strftime('%Y-%m-%d',substr(date_,7,4)||'-' ||substr(date_,4,2)||'-'||substr(date_,1,2) )<=date('now','localtime','start of year','-1 day');
/*--Update closing year date*/
    insert into closing_year(date_,cnt) values(date('now','localtime','start of year'),(select count(*) from bills_h)+(select count(*) from transactions_h));

