close all
set dele on
set safe off
set excl off

If !file('dor_cuv.dbf')

	create table temp(county c(14),;
										conum c(3),;
										digyr n(4),;
										realkey n(10),;
										parcel_no c(20),;
										ccy c(4),;
										saleprice n(11),;
										saledate d,;
										timber_val n(10),;
										imp_val n(10),;
										quota_val n(10),;
										other_val n(10),;
										net_sp n(11),;
										land_resid n(11),;
										totalacres n(8,2),;
										ag1_acres n(8,2),;
										ag2_acres n(8,2),;
										ag3_acres n(8,2),;
										ag4_acres n(8,2),;
										ag5_acres n(8,2),;
										ag6_acres n(8,2),;
										ag7_acres n(8,2),;
										ag8_acres n(8,2),;
										ag9_acres n(8,2),;
										wd1_acres n(8,2),;
										wd2_acres n(8,2),;
										wd3_acres n(8,2),;
										wd4_acres n(8,2),;
										wd5_acres n(8,2),;
										wd6_acres n(8,2),;
										wd7_acres n(8,2),;
										wd8_acres n(8,2),;
										wd9_acres n(8,2),;
										createdate d)
										
	copy to dor_cuv fox2x

Else
		
	use dor_cuv excl
	zap
		
EndIf		

If !file('dor_cuv_subs.dbf')

	create table temp(county c(14),;
										conum c(3),;
										digyr n(4),;
										realkey n(10),;
										landkey n(10),;
										sub_type c(3),;
										descrip c(15),;
										ltype n(2),;
										lclass n(2),;
										acres n(8,2),;
										cuv_match l,;
										createdate d)
										
	copy to dor_cuv_subs
	
Else

	use dor_cuv_subs excl
	zap	
										
EndIf

close databases


sele a
use realprop
set order to parcel_no

sele b
use saleinfo
set order to date_desc

sele c
use landsubs
set order to realkey

sele d
use defaults

sele e
use rurland

sele f
use cuvland

sele g 
use conames
go top

cname = space(13)
cnum = 0

Do while !eof()

	If at(lower(alltrim(name)) , lower(defaults.coname)) > 0

		cname = proper(name)
		cnum = num
		
		exit
		
	EndIf
	
	skip
	
EndDo

If len(alltrim(cname)) = 0

	clear
	@10,20 say 'County Name could not be located'
	@12,20 say 'Process is aborted.'
	@14,20 say 'Contact DOR Tech Support'
	
	close databases
	return
	
EndIf	

sele h
use conmai

sele 10
use dor_cuv

sele 11
use dor_cuv_subs

sele 12
use commimp
set order to realkey

sele 13
use reprop
set order to realkey

sele 14
use acessory
set order to realkey

sele realprop
go top

nimp = 0

clear

Do While !eof()

	@10,20 say recno()
	
	
	If (digclass <> 'A' and digclass <> 'V' and digclass <> 'P' and digclass <> 'W') or;
			totalacres < 25
			
			skip
			
			If !eof()
				loop
			Else
				exit
			EndIf
			
	EndIf

		
	sele saleinfo
	seek str(realprop.realkey,10,0)


	lsale = .f.
	
	Do While !eof() and str(realkey,10,0) = str(realprop.realkey,10,0)
	

		If (year(date()) - year(saledate) > 0 and year(date()) - year(saledate) < 3) and ;
			(upper(reason) = 'FM' or upper(reason) = 'LM')
		
			lsale = .t.
			exit
		 
		EndIf
		
		skip
			
	EndDo
	
	If !lsale
		
		sele realprop
		
		skip
		
		If !eof()
			loop
		Else
			exit
		EndIf		

	EndIf

	nimp = 0
	
	sele commimp
	seek realprop.realkey
	
	Do While !eof() and realkey = realprop.realkey
	
		If 	val(yr_bilt) < defaults.digyr
		
			nimp = nimp + iif(comimpovr > 0 , comimpovr , bldg_val)
			
		EndIf
		
		skip
		
	EndDo	
	
	sele reprop
	seek realprop.realkey
	
	Do While !eof() and realkey = realprop.realkey
	
		If 	yr_built < defaults.digyr
		
			nimp = nimp + iif(ovr_val > 0 , ovr_val , timp_val)
			
		EndIf
		
		skip
		
	EndDo	

	sele acessory
	seek realprop.realkey
	
	Do While !eof() and realkey = realprop.realkey
	
		If 	val(year_built) < defaults.digyr and acc_type = 'A'
		
			nimp = nimp + iif(ovr_val > 0 , ovr_val , imp_val)
			
		EndIf
		
		skip
		
	EndDo	


	sele dor_cuv
	appen blank
	
	replace county with cname,;
					conum with cnum,;
					ccy with realprop.ccy,;
					digyr with defaults.digyr,;
					realkey with realprop.realkey,;
					parcel_no with realprop.parcel_no,;
					saleprice with saleinfo.saleprice,;
					saledate with saleinfo.saledate,;
					imp_val with nimp,;
					timber_val with saleinfo.salesadj,;
					net_sp with saleinfo.net_sp,;
					totalacres with realprop.totalacres,;
					createdate with date(),;
					land_resid with net_sp - nimp
	
	sele conmai
	set order to realkey
	seek realprop.realkey
	
	lcuv = .f.
	
	If found()
	
		lcuv = .t.
		
	EndIf	
	
	sele landsubs
		
	If lcuv
	
		set order to conmaikey
		seek conmai.conmaikey
		
		Do While !eof() and conmaikey = conmai.conmaikey
		
			sele cuvland
			
			go top
			
			Do While !eof()
				
				If landtype = landsubs.ltype
				
					exit
					
				EndIf
				
				skip
					
			EndDo  && searching cuvland for landsubs.ltype
			
			sele dor_cuv_subs
			appen blank
						
			replace county with cname,;
							conum with cnum,;
							digyr with defaults.digyr,;
							realkey with landsubs.realkey,;
							landkey with landsubs.landkey,;
							sub_type with landsubs.sub_type,;
							descrip with cuvland.desc,;
							ltype with landsubs.ltype,;
							lclass with landsubs.lclass,;
							acres with landsubs.acres,;
							createdate with date(),;
							cuv_match with .t.
							
			sele dor_cuv
			
			If landsubs.ltype = 4
				cfname = "ag" + alltrim(str(landsubs.lclass,2,0)) + "_acres"
			Else
				cfname = "wd" + alltrim(str(landsubs.lclass,2,0)) + "_acres"
			EndIf	 	
			
			replace &cfname with &cfname + landsubs.acres
			
			sele landsubs
			skip		
			
		EndDo  && looping thru cuv records in landsubs
		
	EndIf	&& if there is a CU cov
	
	If !lcuv
	
		set order to realkey
		seek realprop.realkey
		
		Do While !eof() and realkey = realprop.realkey
		
			sele rurland
			
			go top

			Do While !eof()
				
				If landtype = landsubs.ltype
				
					exit
					
				EndIf
				
				skip
					
			EndDo  && searching rurland for landsubs.ltype
				
			sele dor_cuv_subs
			appen blank
			
			replace county with cname,;
							conum with cnum,;
							digyr with defaults.digyr,;
							realkey with landsubs.realkey,;
							landkey with landsubs.landkey,;
							sub_type with landsubs.sub_type,;
							descrip with rurland.desc,;
							ltype with landsubs.ltype,;
							lclass with landsubs.lclass,;
							acres with landsubs.acres,;
							createdate with date()
							
			If at('wood',lower(rurland.desc)) + at('timb',lower(rurland.desc)) + ;
				at('open',lower(rurland.desc)) + at('ag',lower(rurland.desc)) + ;
				at('orch',lower(rurland.desc)) + at('vine',lower(rurland.desc)) + ;
				at('pond',lower(rurland.desc)) + at('wet',lower(rurland.desc)) + ;
				at('marsh',lower(rurland.desc)) + at('waste',lower(rurland.desc)) > 0
												
				replac cuv_match with .t.
				
			Else
			
				replace cuv_match with .f.	
				
			EndIf	

			sele dor_cuv
			
			Do Case
			
				Case at('wood',lower(rurland.desc)) + at('timb',lower(rurland.desc)) > 0
					cfname = "wd" + alltrim(str(landsubs.lclass,2,0)) + "_acres"
				Case at('open',lower(rurland.desc)) + at('ag',lower(rurland.desc)) > 0
					cfname = "ag" + alltrim(str(landsubs.lclass,2,0)) + "_acres"
				Case at('orch',lower(rurland.desc)) + at('vine',lower(rurland.desc)) > 0
					cfname = "ag" + alltrim(str(landsubs.lclass,2,0)) + "_acres"
				Case at('pond',lower(rurland.desc)) + at('wet',lower(rurland.desc)) > 0
					cfname = "wd9_acres"
				Case at('marsh',lower(rurland.desc)) + at('waste',lower(rurland.desc)) > 0
					cfname = "wd9_acres"
				Otherwise	
					cfname = "wd5_acres"			

			EndCase
			
			replace &cfname with &cfname + landsubs.acres				
			
			sele landsubs
			skip		
			
		EndDo && looping thru fm land subrecs
	
	EndIf	&& no cu cov on parcel
					
	sele realprop
	skip
	
EndDo && working thru realprop

close all	

If !directory('temp')

	md temp
	cd
	
EndIf

If !directory('temp\dor_cuv')

	md temp\dor_cuv
	cd
	
EndIf

use dor_cuv
copy to temp\dor_cuv\dor_cuv fox2x
copy to temp\dor_cuv\dor_cuv sdf
copy to temp\dor_cuv\dor_cuv xls

use dor_cuv_subs
copy to temp\dor_cuv\dor_cuv_subs fox2x
copy to temp\dor_cuv\dor_cuv_subs sdf
copy to temp\dor_cuv\dor_cuv_subs xls

close databases

If !file('c:\progra~1\winzip\wzzip.exe')

	clear
	@10,20 say 'WinZip 9.0 and WinZip Command Line must be installed to complete the process.'
	@12,20 say 'Contact Tech Support for assistance.'
	
	return
	
Else

	run c:\progra~1\winzip\wzzip temp\dor_cuv\dor_cuv temp\dor_cuv\*.* 
	
	If file('temp\dor_cuv\dor_cuv.zip')
	
		clear
		@ 6,20 say 'DOR CUV files have been zipped.'
		@ 8,20 say 'dor_cuv.zip is located in the temp\dor_cuv folder.'
		@10,20 say '--------------------------------------------------------------'		
		@12,20 say 'Using the File >> Upload to FTP menu option in WinGAP,'
		@14,20 say 'Upload dor_cuv.zip to ftp site.'
		@16,20 say '--------------------------------------------------------------'
		@18,20 say 'Please email Gregg Reese at rgreese@msn.com'
		@20,20 say 'with a copy to the other DOR Support agents regarding the upload.'
		@22,20 say 'Thank you for your assistance...'
		
	Else
	
		clear
		@10,20 say 'DOR CUV files were not zipped.'
		@12,20 say 'Please contact DOR Tech Support for assistance'
			
	EndIf	
	
	return
	
EndIf