set nocount on
	declare @seedDate datetime

	set @seedDate=dateadd(year,-3,getdate())  --dateadd(month,-6,getdate())

	create table #reports (reportid int,callid int,meterreadinggroupid int)
	insert into #reports
	select reportid,callid,MeterReadingGroupID 
	from screports r  with (nolock)
	where r.createdate>=@seedDate and r.voidperiod is null and r.voidid is null


select 
identity(int) as scid,
rl.DetailID as LaborRecord,
replace(replace(replace(isnull(cul.address,cu.address),',',''),char(13),' '),char(10),' ') as [Address],
replace(replace(replace(bc.billcode+':'+bc.description,',',''),char(13),' '),char(10),' ') as BillCode,
replace(replace(replace(ct.calltype,',',''),char(13),' '),char(10),' ') as CallType,
replace(replace(replace(ct.category,',',''),char(13),' '),char(10),' ') as CallTypeCategory,
replace(replace(replace(ct.description,',',''),char(13),' '),char(10),' ') as CallTypeDescription,
replace(replace(replace(isnull(co.PrefFullName,c.Caller),',',''),char(13),' '),char(10),' ') as Contact,
replace(replace(replace(cc.CancelCode,',',''),char(13),' '),char(10),' ') as CancelCode,
replace(replace(replace(c.CancelDescription,',',''),char(13),' '),char(10),' ') as CancelDescription,
replace(replace(replace(isnull(cul.city, cu.city),',',''),char(13),' '),char(10),' ') as City,
c.CloseDate as CloseDate,
replace(replace(replace(isnull(sc.contractnumber,''),',',''),char(13),' '),char(10),' ') as ContractNumber,
c.date as CreateDate,
replace(replace(replace(cu.customername,',',''),char(13),' '),char(10),' ') as Customer,
replace(replace(replace(cu.address,',',''),char(13),' '),char(10),' ') as CustomerAddress,
replace(replace(replace(cu.city,',',''),char(13),' '),char(10),' ') as CustomerCity,
replace(replace(replace(cu.CustomerNumber,',',''),char(13),' '),char(10),' ') as CustomerNumber,
replace(replace(replace(cu.State,',',''),char(13),' '),char(10),' ') as CustomerState,
replace(replace(replace(cu.zip,',',''),char(13),' '),char(10),' ') as CustomerZip, 
(SELECT MIN(DispatchDate) from SCReports scr INNER JOIN SCReportLabors scl on scr.ReportID = scl.ReportID where scr.CallID = c.CallID and scr.voidperiod is null and scr.voidid is null) AS DispatchDate,
c.ReqDate as DueDate,
replace(replace(replace(cu.Email,',',''),char(13),' '),char(10),' ') as Email, 
(SELECT MAX(DepartureDate) from SCReports scr INNER JOIN SCReportLabors scl on scr.ReportID = scl.ReportID where scr.CallID = c.CallID and scr.voidperiod is null and scr.voidid is null) AS EndDate,	
replace(replace(replace(isnull(co.PrefFullName,eco.PrefFullName),',',''),char(13),' '),char(10),' ') as EquipmentContact,
replace(replace(replace(isnull(co.phone1,eco.phone1),',',''),char(13),' '),char(10),' ') as EquipmentContactPhone,
e.EquipmentNumber as EquipmentNumber,
replace(replace(replace(hc.onholdcode,',',''),char(13),' '),char(10),' ') as HoldCode,
replace(replace(replace(hc.Description,',',''),char(13),' '),char(10),' ') as HoldDescription,
cast(' ' as varchar(3000)) as IncompleteCode,
cast(' ' as varchar(3000)) as IncompleteCodeDescription,
e.InstallDate as InstallDate,
replace(replace(replace(isnull(cul.customername,cu.customername),',',''),char(13),' '),char(10),' ') as InstalledLocationName,
replace(replace(replace(isnull(cul.customernumber,cu.customernumber),',',''),char(13),' '),char(10),' ') as InstalledLocationNumber,
replace(replace(replace(c.description,',',''),char(13),' '),char(10),' ') as Issue,
rl.DispatchDate as LaborRecordDispatchDate,
rl.ArrivalDate as LaborRecordArrivalDate,
rl.DepartureDate as LaborRecordDepartureDate,
replace(replace(replace(ma.Make,',',''),char(13),' '),char(10),' ') as Make,
cast(' ' as varchar(5000)) as MeterReadings,
replace(replace(replace(m.Model,',',''),char(13),' '),char(10),' ') as Model,
replace(replace(replace(cu.Phone1,',',''),char(13),' '),char(10),' ') as Phone, 
replace(replace(replace(cp.priority+':'+cp.description+':'+cast(cp.rank as varchar(10)),',',''),char(13),' '),char(10),' ') as [Priority],
cast(' ' as varchar(3000)) as ProblemCodes,
cast(' ' as varchar(3000)) as ResolutionCodes,
e.serialnumber as SerialNumber,
cast(' ' as varchar(3000)) as ServiceCoverage,
c.EstStartDate as StartDate,
replace(replace(replace(isnull(cul.state,cu.State),',',''),char(13),' '),char(10),' ') as [State],
cs.description as [Status],
replace(replace(replace(a.preffullname,',',''),char(13),' '),char(10),' ') as Technician,
replace(replace(replace(cast(c.notes as varchar(8000)),',',''),char(13),' '),char(10),' ')  as TechnicianClosingNotes,
replace(replace(replace(a.AgentNumber,',',''),char(13),' '),char(10),' ') as TechnicianNumber,
replace(replace(replace(et.territory,',',''),char(13),' '),char(10),' ') as TerritoryCode,
replace(replace(replace(c.callnumber,',',''),char(13),' '),char(10),' ') as TicketNumber,
replace(replace(replace(wo.workordernumber,',',''),char(13),' '),char(10),' ') as WorkOrderNumber,
replace(replace(replace(isnull(cul.Zip,cu.Zip),',',''),char(13),' '),char(10),' ') as [Zip],
r.reportid,
r.MeterReadingGroupID
into #schistory
from #reports r 
 inner join screportlabors rl  with (nolock) on r.reportid=rl.reportid
 inner join sccalls c with (nolock)  on r.callid=c.callid
 inner join scworkorders wo with (nolock)  on c.workorderid=wo.workorderid
 inner join sccallstatustypes cs with (nolock)  on c.status=cs.type
 left join sccalltypes ct with (nolock)  on c.calltypeid=ct.calltypeid
 left join SCPriorities cp with (nolock)  on cp.Priorityid=c.PriorityID
 inner join shagents a with (nolock)  on c.TechnicianID=a.AgentID
 inner join scequipments e with (nolock)  on e.equipmentid=c.equipmentid
 left join icmodels m with (nolock)  on e.modelid=m.modelid
 left join scterritories et with (nolock)  on et.territoryid=e.territoryid
 left join sccontracts sc with (nolock)  on c.contractid=sc.contractid
 left join icmakes ma with (nolock)  on ma.makeid=m.makeid
 left join scbillcodes bc with (nolock)  on c.billcodeid=bc.billcodeid
 inner join arcustomers cu with (nolock)  on c.customerid=cu.customerid
 left join arcustomers cul with (nolock)  on e.locationid=cul.customerid
 left join cmcontacts co with (nolock)  on c.CallerContactID=co.contactid
 left join cmcontacts eco with (nolock)  on e.equipmentcontactid=eco.contactid
 left join sccancelcodes cc with (nolock) on c.cancelcodeid = cc.cancelcodeid
 left join ShOnHoldCodes hc with (nolock) on c.onholdcodeid = hc.onholdcodeid
 order by c.callnumber,rl.detailid

 update #schistory set problemCodes=rplist 
 from #schistory inner join (select rp.reportid,STUFF((select '|'+replace(pc.problemcode,',','')+':'+replace(pc.description,',','') 
 from screportproblemcodes p with (nolock)  
 inner join scproblemcodes pc with (nolock)  on p.problemcodeid=pc.problemcodeid where p.reportid=rp.reportid for XML PATH('')),1,1,'') rplist
  from screportproblemcodes rp with (nolock)  
 inner join #schistory h on rp.ReportID=h.reportid
 group by rp.reportid) h2 on h2.reportid=#schistory.reportid
 
 update #schistory set resolutionCodes=rplist 
 from #schistory inner join (select rp.reportid,STUFF((select '|'+replace(pc.RepairCode,',','')+':'+replace(pc.description,',','') 
 from screportrepaircodes p  with (nolock) 
 inner join screpaircodes pc with (nolock)  on p.repaircodeid=pc.repaircodeid 
 where p.reportid=rp.reportid for XML PATH('')),1,1,'') rplist
  from screportrepaircodes rp  with (nolock) 
 inner join #schistory h on rp.ReportID=h.reportid
 group by rp.reportid) h2 on h2.reportid=#schistory.reportid 
 
 DECLARE @SERVICE_hOURS VARCHAR(3000)
 SET @SERVICE_HOURS=
 Stuff((select '|'+Description+':'+cast(datepart(hour,openingtime) as varchar(2))+':'+right('0'+cast(datepart(MI,openingtime) as varchar(2)),2)+'-'+
 cast(datepart(hour,closingtime) as varchar(2))+':'+right('0'+cast(datepart(MI,closingtime) as varchar(2)),2)
 from shservicehours with (nolock)  for XML PATH('')),1,1,'')
 
 UPDATE #SCHISTory set servicecoverage=@service_hours
 
 update #schistory set meterReadings=mclist
 from #schistory inner join (select mr2.MeterReadingGroupID,STUFF((select '|'+replace(mt.metertype,',','')+':'+replace(mt.description,',','')+':'+case mt.issystemtype when 1 then 'Yes' else 'No' end+':'+replace(mtc.metertypecategory,',','')+':'+cast(cast(display as numeric(15,0)) as varchar(15))
 from mtmeterreadings mr with (nolock) 
 inner join mtmeters m with (nolock)  on mr.meterid=m.meterid
 inner join mtmetertypes mt with (nolock)  on m.metertypeid=mt.metertypeid
 inner join mtmetertypecategories mtc with (nolock)  on mtc.MeterTypeCategoryID=mt.metertypecategoryid 
 where mr.MeterReadingGroupID=mr2.meterreadinggroupid for XML PATH('')),1,1,'') mclist
 from mtmeterreadings mr2 with (nolock)  inner join #schistory h on mr2.meterreadinggroupid=h.meterreadinggroupid group by mr2.meterreadinggroupid) h2 on h2.meterreadinggroupid=#schistory.MeterReadingGroupID

 update #schistory set IncompleteCode = ic.IncompleteCode, IncompleteCodeDescription = ic.Description
 from #schistory 
 inner join screports sr on #schistory.reportid=sr.reportid
 inner join SCIncompleteCodes ic on ic.IncompleteCodeID = sr.IncompleteCodeID

  create table #schistoryoutput (scid int,LaborRecord varchar(8000), Address varchar(8000),BillCode varchar(8000),CallType varchar(8000),CallTypeCategory varchar(8000),CallTypeDescription varchar(8000),
 Contact varchar(8000),CancelCode varchar(8000),CancelDescription varchar(8000),City varchar(8000),CloseDate varchar(8000),ContractNumber varchar(8000),CreateDate varchar(8000),
 Customer varchar(8000),CustomerAddress varchar(8000),CustomerCity varchar(8000),CustomerNumber varchar(8000),CustomerState varchar(8000),CustomerZip varchar(8000),DispatchDate varchar(8000),
 DueDate varchar(8000),Email varchar(8000),EndDate varchar(8000),EquipmentContact varchar(8000),EquipmentContactPhone varchar(8000),EquipmentNumber varchar(8000),HoldCode varchar(8000),
 HoldDescription varchar(8000),IncompleteCode varchar(8000),IncompleteCodeDescription varchar(8000),InstallDate varchar(8000),InstalledLocationName varchar(8000),InstalledLocationNumber varchar(8000),
 Issue varchar(8000),LaborRecordDispatchDate varchar(8000),LaborRecordArrivalDate varchar(8000),LaborRecordDepartureDate varchar(8000),Make varchar(8000),MeterReadings varchar(8000),Model varchar(8000),
 Phone varchar(8000),Priority varchar(8000),ProblemCodes varchar(8000),ResolutionCodes varchar(8000),SerialNumber varchar(8000),ServiceCoverage varchar(8000),StartDate varchar(8000),State varchar(8000),
 Status varchar(8000),Technician varchar(8000),TechnicianClosingNotes varchar(8000),TechnicianNumber varchar(8000),TerritoryCode varchar(8000),TicketNumber varchar(8000),WorkOrderNumber varchar(8000),
 Zip varchar(8000),ReportID int,MeterReadingGroupID int)
 
 
 insert into #schistoryoutput (scid,LaborRecord,Address,BillCode,CallType,CallTypeCategory,CallTypeDescription,
 Contact,CancelCode,CancelDescription,City,CloseDate,ContractNumber,CreateDate,
 Customer,CustomerAddress,CustomerCity,CustomerNumber,CustomerState,CustomerZip,DispatchDate,
 DueDate,Email,EndDate,EquipmentContact,EquipmentContactPhone,EquipmentNumber,HoldCode,
 HoldDescription,IncompleteCode,IncompleteCodeDescription,InstallDate,InstalledLocationName,InstalledLocationNumber,
 Issue,LaborRecordDispatchDate,LaborRecordArrivalDate,LaborRecordDepartureDate,Make,MeterReadings,Model,
 Phone,Priority,ProblemCodes,ResolutionCodes,SerialNumber,ServiceCoverage,StartDate,State,
 Status,Technician,TechnicianClosingNotes,TechnicianNumber,TerritoryCode,TicketNumber,WorkOrderNumber,Zip)
 
 select 0,'LaborRecord','Address','BillCode','CallType','CallTypeCategory','CallTypeDescription',
 'Contact','CancelCode','CancelDescription','City','CloseDate','ContractNumber','CreateDate',
 'Customer','CustomerAddress','CustomerCity','CustomerNumber','CustomerState','CustomerZip','DispatchDate',
 'DueDate','Email','EndDate','EquipmentContact','EquipmentContactPhone','EquipmentNumber','HoldCode',
 'HoldDescription','IncompleteCode','IncompleteCodeDescription','InstallDate','InstalledLocationName','InstalledLocationNumber',
 'Issue','LaborRecordDispatchDate','LaborRecordArrivalDate','LaborRecordDepartureDate','Make','MeterReadings','Model',
 'Phone','Priority','ProblemCodes','ResolutionCodes','SerialNumber','ServiceCoverage','StartDate','State',
 'Status','Technician','TechnicianClosingNotes','TechnicianNumber','TerritoryCode','TicketNumber','WorkOrderNumber','Zip'
  
 insert into #schistoryoutput select scid,LaborRecord,Address,BillCode,CallType,CallTypeCategory,CallTypeDescription,
 Contact,CancelCode,CancelDescription,City,CloseDate,ContractNumber,CreateDate,
 Customer,CustomerAddress,CustomerCity,CustomerNumber,CustomerState,CustomerZip,DispatchDate,
 DueDate,Email,EndDate,EquipmentContact,EquipmentContactPhone,EquipmentNumber,HoldCode,
 HoldDescription,IncompleteCode,IncompleteCodeDescription,InstallDate,InstalledLocationName,InstalledLocationNumber,
 Issue,LaborRecordDispatchDate,LaborRecordArrivalDate,LaborRecordDepartureDate,Make,MeterReadings,Model,
 Phone,Priority,ProblemCodes,ResolutionCodes,SerialNumber,ServiceCoverage,StartDate,State,
 Status,Technician,TechnicianClosingNotes,TechnicianNumber,TerritoryCode,TicketNumber,WorkOrderNumber,Zip,reportid,meterreadinggroupid from #schistory

 select Address,BillCode,CallType,CallTypeCategory,CallTypeDescription,
 Contact,CancelCode,CancelDescription,City,CloseDate,ContractNumber,CreateDate,
 Customer,CustomerAddress,CustomerCity,CustomerNumber,CustomerState,CustomerZip,DispatchDate,
 DueDate,Email,EndDate,EquipmentContact,EquipmentContactPhone,EquipmentNumber,HoldCode,
 HoldDescription,IncompleteCode,IncompleteCodeDescription,InstallDate,InstalledLocationName,InstalledLocationNumber,
 Issue,LaborRecord,LaborRecordDispatchDate,LaborRecordArrivalDate,LaborRecordDepartureDate,Make,MeterReadings,Model,
 Phone,Priority,ProblemCodes,ResolutionCodes,SerialNumber,ServiceCoverage,StartDate,State,
 Status,Technician,TechnicianClosingNotes,TechnicianNumber,TerritoryCode,TicketNumber,WorkOrderNumber,Zip
 From #schistoryoutput 
 order by scid

 create table #scparts (LineID int identity(1,1),SerialNumber varchar(30),TicketNumber varchar(30),DealerItemNumber varchar(100),ItemDescription varchar(255),Quantity numeric(11,6),LineCost money)
 insert into #scparts (serialnumber,TicketNumber,dealeritemnumber,ItemDescription,quantity,linecost)

 select  replace(e.SerialNumber,',','') as SerialNumber,
  replace(CallNumber,',','') as TicketNumber,
  replace(Item,',','') as DealerItemNumber,
  replace(cm.description,',','') as ItemDescription,
 Quantity,
 isnull(i.avgcost,0) as LineCost 
 from #reports r 
 inner join sccalls c with (nolock)  on r.callid=c.callid
 inner join scequipments e with (nolock)  on e.equipmentid=c.equipmentid
 inner join shagents a with (nolock)  on c.technicianid=a.agentid
 left join icmodels m with (nolock)  on e.modelid=m.modelid
 left join icmakes ma with (nolock)  on ma.makeid=m.makeid
 inner join screportmaterials cm with (nolock)  on cm.reportid=r.reportid
 inner join icitems i with (nolock)  on cm.itemid=i.itemid
 order by callnumber
  

 create table #scpartsout (scid int,LineID varchar(8000),SerialNumber varchar(8000),TicketNumber varchar(8000),DealerItemNumber varchar(8000),ItemDescription varchar(8000), Quantity varchar(8000),LineCost varchar(8000))

 insert into #scpartsout (scid,LineID,serialnumber,TicketNumber,dealeritemnumber,itemdescription,quantity,linecost)
 select 0,'LineID','SerialNumber','TicketNumber','DealerItemNumber','ItemDescription','Quantity','LineCost'
 
 insert into #scpartsout select LineID,* from #scparts

 select LineID,SerialNumber,TicketNumber,DealerItemNumber,ItemDescription,Quantity,LineCost From #scpartsout 
 order by scid
   
 drop table #schistory
 drop table #scparts
 drop table #schistoryoutput
 drop table #scpartsout
 drop table #reports