do set_menu

= get_pdt('Monthly Service Activity Chart (Summary)')
do wait_msg
wait wind '!!! WAIT !!! Gathering Data using SQL..' nowa
del = tmp_file('DBF')
sele year(iif(jobcard_no = 'DS',job_mast.bill_dt,job_mast.deli_dt)) as year,;
	mont(iif(jobcard_no = 'DS',job_mast.bill_dt,job_mast.deli_dt)) as month, ;
	count(DISTINCT iif(jobcard_no = 'DS',job_mast.bill_dt,job_mast.deli_dt)) as days, ;
	sum(iif(servicetyp = 'F',1,0)) as tot_free, ;
	sum(iif(servicetyp = 'P',1,0)) as tot_pmc, ;
	sum(iif(servicetyp = 'W',1,0)) as only_warra , ;
	sum(iif(servicetyp = 'R',1,0)) as tot_regula, ;
	sum(iif(servicetyp = 'A',1,0)) as only_accid , ;
	sum(iif(servicetyp = 'M',1,0)) as tot_minor , ;
	sum(iif(jobcard_no = 'DS',1,0)) as tot_ds,;
	sum(iif(CATEGORY = 'W',1,0)) as tot_warran, ;
	sum(iif(CATEGORY = 'E',1,0)) as tot_engine, ;
	sum(iif(CATEGORY = 'A',1,0)) as tot_accide, ;
	sum(iif(CATEGORY = 'F',1,0)) as tot_camp, ;
	sum(iif(jobcard_no = 'DS',tot_spares - sdisc_amt,0)) as ds_amt,;
	sum(tot_spares - sdisc_amt) as spares ,;
	sum(iif(! inli(servicetyp,'M','D'),tot_spares - sdisc_amt,0000000.00)) as spare_amt,;
	sum(iif(servicetyp <> 'M',tot_labour - ldisc_amt,0000000.00)) as labour_amt,;
	sum(iif(servicetyp = 'M',tot_spares - sdisc_amt,0000000.00)) as minor_spar,;
	sum(iif(servicetyp = 'M',tot_labour - ldisc_amt,0000000.00)) as minor_labo ,;
	sum(tot_oil) as oil_amt,;
	sum(free_rs) as free_note, ;
	sum(company_wt) as totcompwty, ;
	sum(dealer_wty) as totdlrwty, ;
	00000.00 as pmc_oil, ;
	000000.00 as pmc_labour, ;
	0000 as new_pmc ;
	where betw(iif(jobcard_no = 'DS',job_mast.bill_dt,job_mast.deli_dt),date1,date2) ;
	group by 1,2 ;
	orde by year,month ;
	from job_mast ;
	into dbf (del)
use
use (del) in 0 alia del
sele del
inde on month tag month
*sele year(issue_dt) as year,mont(issue_dt) as month, ;
	sum(iif(servicetyp <> 'M' and category <> 'W',spare_rt*qty,0.00)) as spare_amt, ;
	sum(iif(servicetyp = 'M',spare_rt*qty,0.00)) as minor_spar, ;
	sum(iif(warranty = 'C',spare_rt*qty,0.00)) as totcompwty,;
	sum(iif(warranty = 'D',spare_rt*qty,0.00)) as totdlrwty;
	from issuebod,issue,job_mast ;
	where job_mast.jobcard_no = issue.jobcard_no ;
		and issue.issue_no = issuebod.issue_no ;
		and ! inli(servicetyp,'D') ;
		and betw(job_mast.deli_dt,date1,date2) ;
	group by 1,2 ;
	orde by year,month ;
	into cursor query
sele year(issue_dt) as year,mont(issue_dt) as month, ;
	sum(iif(warranty = 'C',spare_rt*qty,0.00)) as totcompwty,;
	sum(iif(warranty = 'D',spare_rt*qty,0.00)) as totdlrwty;
	from issuebod,issue,job_mast ;
	where job_mast.jobcard_no = issue.jobcard_no ;
		and issue.issue_no = issuebod.issue_no ;
		and ! inli(servicetyp,'D') ;
		and betw(job_mast.deli_dt,date1,date2) ;
	group by 1,2 ;
	orde by year,month ;
	into cursor query
scan
	if seek(month,'DEL')
*		repl del.spare_amt with spare_amt ;
			del.minor_spar with minor_spar ;
			del.totcompwty with totcompwty ;
			del.totdlrwty with totdlrwty 
		repl del.totcompwty with totcompwty ;
			del.totdlrwty with totdlrwty 
	else
		do alert with 'Monthly Summary Info. was not updated for month : ' +  ltrim(str(month))
	endif
ends
use in query
sele year(deli_dt) as year,mont(job_dt) as month,;
	sum(pmc_oil) as pmc_oil, ;
	sum(pmc_labour) as pmc_labour ;
	from job_mast ;
	where betw(job_mast.deli_dt,date1,date2) ;
		and job_mast.servicetyp = 'P' ;
	group by 1,2 ;	
	orde by year,month ;
	into cursor query	
scan
	if seek(month,'DEL')
		repl del.pmc_oil with pmc_oil ;
			del.pmc_labour with pmc_labour
	else
		do alert with 'Monthly Summary Info. was not updated for PMC for month : ' +  ltrim(str(month))
	endif
ends
sele year(PMC_DATE) as year,mont(PMC_DATE) as month,;
	count(*) as new_pmc ;
	from client ;
	where betw(pmc_date,date1,date2) ;
	group by 1,2 ;
	orde by year,month ;
	into cursor query
scan
	if seek(month,'DEL')
		repl del.new_pmc with new_pmc 
	else
		do alert with 'Monthly Summary Info. was not updated for NEW PMC for month : ' +  ltrim(str(month))
	endif
ends


use in query
sele del
sele * ;
	from del ;
	orde by year,month ;
	into cursor query
use in del
cdx = del + '.cdx'
del = del + '.dbf'
dele file (del)
dele file (cdx)
wait clea	
if _tally = 0
    deac wind wait
	do err_beep
	do alert with '!!! ATTENTION !!!;No data found ...'
else
	wait wind '!!! WAIT !!! Generating Report ....' nowa
    do prinrepo with 'WMONTHIN','MONTHINF','','',''
endif	
use in query
do cls_menu	