DROP VIEW IF EXISTS active;
CREATE VIEW active  AS  select contracts.carid AS carid, cars.kind AS kind,cars.numbers AS numbers,cars.model AS model,
contracts.contract_id AS contract_id,contracts.date_out AS date_out,contracts.date_in AS date_in,contracts.branch AS branch,
contracts.tamm_expire AS tamm_expire,contracts.day_cost AS day_cost,contracts.cont_status AS cont_status,customers.cust_id AS cust_id,
customers.cust_name AS cust_name,customers.phone AS phone,customers.idSA AS idSA
from ((contracts join customers) join cars)
 where ((contracts.carid = cars.carid)
 and ((contracts.cust_id = customers.cust_id) or (contracts.cust_id2 = customers.cust_id)));

DROP VIEW IF EXISTS car_contracts;
 CREATE VIEW car_contracts  AS  select contracts.carid AS carid,cars.kind AS kind,cars.numbers AS numbers,cars.model AS model,contracts.contract_id AS contract_id,contracts.date_out AS date_out,contracts.date_in AS date_in,contracts.days_qty AS days_qty,contracts.wanted AS wanted,contracts.day_cost AS day_cost,contracts.cont_status AS cont_status,contracts.cust_id2 AS cust_id2,contracts.cust_id AS cust_id,customers.cust_name AS cust_name,customers.phone AS phone from ((contracts join customers) join cars) where (((contracts.carid = cars.carid) and (contracts.cust_id = customers.cust_id)) or (contracts.cust_id2 = customers.cust_id));

DROP VIEW IF EXISTS supp_mat_v;
 CREATE VIEW supp_mat_v  AS  select material_supp.mat_name AS mat_name,material_supp.spcial AS spcial,material_supp.comm AS comm,
        material_in.mat_supp_id AS mat_supp_id,material_in.mat_in_id AS mat_in_id,material_in.store_id AS store_id,material_in.net_cost AS net_cost,material_in.invoice_no AS invoice_no,material_in.mat_invoice_date AS mat_invoice_date,
        store.store_name AS store_name,
        items_line.qty AS qty,items_line.outt AS outt,items_line.item_id AS item_id,items_line.line_id AS line_id , items_line.unit_price AS unit_price,
        items.item_name AS item_name , items.kind AS kind
         from ((((material_supp join material_in) join store) join items_line) join items) where ((material_supp.mat_supp_id = material_in.mat_supp_id) and (material_in.store_id = store.store_id) and (material_in.mat_in_id = items_line.mat_in_id) and (items_line.item_id = items.item_id));


DROP VIEW IF EXISTS car_ment_v;
 CREATE VIEW car_ment_v  AS  SELECT item_line_out.branch AS branch ,item_line_out.line_out_id AS line_out_id,item_line_out.req_id AS req_id,item_line_out.item_id AS item_id,item_line_out.qty AS qty,item_line_out.line_in AS line_in,item_line_out.tot_price AS tot_price, item_line_out.date_out AS date_out, item_line_out.technician AS technician,
 cars.kind AS kind,cars.numbers AS numbers,cars.model AS model,cars.carid AS carid,
 supp_mat_v.mat_name AS mat_name,supp_mat_v.store_name AS store_name,supp_mat_v.item_name AS item_name,supp_mat_v.invoice_no AS invoice_no,ment_request.done AS done,ment_request.date_done AS date_done,ment_request.ask_date AS ask_date from (((item_line_out join cars) join supp_mat_v) join ment_request) where ((item_line_out.req_id = ment_request.req_id) and (ment_request.car_id = cars.carid) and (item_line_out.line_in = supp_mat_v.line_id));


DROP VIEW IF EXISTS newcontprint;
CREATE VIEW newcontprint  AS  select cars.carid ,cars.brand ,cars.kind , concat(cars.letters,' ',cars.numbers) AS plate ,
 cars.model ,cars.color ,customers.cust_id ,customers.cust_name ,customers.nation ,customers.phone ,customers.phone2,
 customers.working_woner ,customers.w_phone ,customers.address ,customers.working_lic ,customers.w_phone2 ,
 customers.working_address ,customers.idSA ,customers.id_Expire ,customers.id_From ,customers.license ,customers.lic_expire ,
 customers.lic_from ,
 contracts.contract_id ,contracts.used_area ,contracts.rent_days ,contracts.date_out ,contracts.branch ,contracts.father ,
 contracts.km_out ,contracts.time_out ,contracts.day_cost ,contracts.kilo_cost ,contracts.hour_cost ,contracts.free_kilo,
 contracts.cust_id2  from cars , customers , contracts
 where contracts.carid = cars.carid and contracts.cust_id = customers.cust_id;

DROP VIEW IF EXISTS  sanad_in;
CREATE VIEW sanad_in  AS  select invoice.in_id AS in_id,  invoice.father AS father,invoice.payMethod AS payMethod,invoice.printed_no AS printed_no,invoice.sanad_date AS sanad_date,invoice.money_in AS money_in,invoice.comm AS comm,users.realName AS realName,branches.b_name AS b_name,branches.branchID AS branchID,contracts.cust_id AS cust_id,contracts.date_out AS date_out,contracts.cont_status AS cont_status,contracts.contract_id AS contract_id,cars.carid AS carid,cars.kind AS kind,cars.numbers AS numbers,cars.letters AS letters,cars.model AS model,customers.cust_name AS cust_name from (((((invoice join cars) join contracts) join customers) join branches) join users) where ((invoice.contract_id = contracts.contract_id) and (contracts.carid = cars.carid) and (contracts.cust_id = customers.cust_id) and (invoice.father = users.user_Id) and (invoice.branch = branches.branchID));

DROP VIEW IF EXISTS sanad_out;
CREATE VIEW sanad_out  AS  select outvoice.out_id AS out_id,outvoice.printed_no AS printed_no,outvoice.sanad_out AS sanad_out
      ,outvoice.money_out AS money_out,outvoice.payOutMethod AS payOutMethod,outvoice.iban AS iban,outvoice.trans AS trans
      ,outvoice.comm AS comm,outvoice.father AS father,outvoice.branch AS branch
      ,contracts.cust_id AS cust_id,contracts.date_out AS date_out,contracts.date_in AS date_in,contracts.cont_status AS cont_status,contracts.contract_id AS contract_id,cars.kind AS kind,cars.numbers AS numbers,cars.letters AS letters,cars.model AS model,
users.realName AS realName,branches.b_name AS b_name,customers.cust_name AS cust_name 
from (((((outvoice join cars) join contracts) join branches) join users) join customers)
 where ((outvoice.contract_id = contracts.contract_id) and (outvoice.father = users.user_Id) 
 and (outvoice.branch = branches.branchID) and (contracts.carid = cars.carid) 
 and (contracts.cust_id = customers.cust_id));

DROP VIEW IF EXISTS store_balance;
CREATE VIEW store_balance  AS  select items_line.line_id AS line_id,items_line.item_id AS item_id,items_line.mat_in_id AS mat_in_id,items_line.tot_price AS tot_price,items_line.qty AS qty,items_line.unit AS unit,items_line.unit_price AS unit_price,items_line.location AS location,items_line.comm AS comm,items_line.outt AS outt,material_in.store_id AS store_id,material_in.mat_supp_id AS mat_supp_id,material_in.invoice_no AS invoice_no,material_in.mat_invoice_date AS mat_invoice_date from (items_line join material_in) where ((items_line.mat_in_id = material_in.mat_in_id) and ((items_line.qty > items_line.outt)OR(items_line.outt IS NULL)));

DROP VIEW IF EXISTS car_out;
CREATE VIEW car_out AS select cars.carid , contracts.contract_id , contracts.branch ,outvoice.money_out ,outvoice.sanad_out , outvoice.father
from cars , contracts , outvoice
where contracts.carid = cars.carid
AND outvoice.contract_id = contracts.contract_id;

DROP VIEW IF EXISTS car_in;
CREATE VIEW car_in AS select cars.carid , contracts.contract_id ,contracts.branch , invoice.money_in ,invoice.sanad_date , invoice.current_km , invoice.father
from cars , contracts , invoice
where contracts.carid = cars.carid
AND invoice.contract_id = contracts.contract_id;

DROP VIEW IF EXISTS next_km;
CREATE VIEW next_km as SELECT items.item_name , ment_request.car_id , ment_request.kmm,item_line_out.date_out,item_line_out.next_km , item_line_out.item_id , item_line_out.line_out_id FROM ment_request , items , item_line_out WHERE item_line_out.req_id = ment_request.req_id AND item_line_out.item_id = items.item_id AND item_line_out.next_km IS NOT NULL ;


DROP VIEW IF EXISTS sanad_cut;
CREATE VIEW sanad_cut  AS  SELECT deduction.de_id , deduction.printed_no ,deduction.sanad_date ,deduction.money_de ,deduction.description ,deduction.father ,deduction.branch ,contracts.cust_id ,contracts.date_out ,contracts.date_in ,contracts.cont_status ,contracts.contract_id ,cars.kind ,cars.numbers ,cars.letters ,cars.model ,users.realName ,branches.b_name ,customers.cust_name from deduction ,cars , users ,branches,customers ,contracts where deduction.contract_id = contracts.contract_id and deduction.father = users.user_Id and deduction.branch = branches.branchID and contracts.carid = cars.carid and contracts.cust_id = customers.cust_id ;

DROP VIEW IF EXISTS car_ment_items;
 CREATE VIEW car_ment_items AS SELECT item_line_out.branch ,item_line_out.line_out_id ,item_line_out.req_id ,item_line_out.item_id , item_line_out.qty ,item_line_out.line_in ,item_line_out.tot_price , item_line_out.date_out , item_line_out.technician ,items.item_name , cars.kind ,concat(cars.letters,' ',cars.numbers) as PL ,cars.model ,cars.carid from item_line_out , ment_request , cars , items where item_line_out.req_id = ment_request.req_id AND ment_request.car_id = cars.carid AND item_line_out.item_id = items.item_id ;

DROP VIEW IF EXISTS aksatcontprint;
 CREATE VIEW aksatcontprint  AS  select cars.carid ,cars.brand ,cars.kind , concat(cars.letters,' ',cars.numbers) AS plate ,
  cars.model ,cars.color ,customers.cust_id ,customers.cust_name ,customers.nation ,customers.phone ,customers.phone2,
  customers.working_woner ,customers.w_phone ,customers.address ,customers.working_lic ,customers.w_phone2 ,
  customers.working_address ,customers.idSA ,customers.id_Expire ,customers.id_From ,customers.license ,customers.lic_expire ,
  customers.lic_from ,
  aksat_contract.contract_id ,aksat_contract.used_area ,aksat_contract.rent_days ,aksat_contract.branch ,aksat_contract.father ,
  aksat_contract.km_out ,aksat_contract.day_cost ,aksat_contract.kilo_cost  ,aksat_contract.free_kilo,
  aksat_contract.cust_id2  from cars , customers , aksat_contract
  where aksat_contract.carid = cars.carid and aksat_contract.cust_id = customers.cust_id;

//accounting

 CREATE VIEW entry_acc  AS  SELECT a1_id as id, a1_name as name from aid1 WHERE has_child = 2 union(SELECT a2_id , a2_name from aid2 WHERE has_child = 2)
  union(SELECT a3_id , a3_name from aid3) ;
