#!/usr/bin/env ruby

querylog= '/home/libby/public_html/_tmp.html'

# libby.miller@bristol.ac.uk (thanks danbri :)
# 2002-07-25
#
# usage:
#
# rdfdump file:../rdf/whodoeswhat.rdf  -o ntriples -q > ../rdf/whodoeswhat.nt
# rdfdump file:../rdf/partners.rdf  -o ntriples -q > ../rdf/partners.nt
# rdfdump file:../rdf/_esw_projdata.rdf  -o ntriples -q > ../rdf/_esw_projdata.nt
# 
# 
# libby.miller@bristol.ac.uk
# the aim here is to pull out enough information to allow the
# calculation of start dates for deliverables. This is quite complex
# because all we have is months per partner per WP, and guesstimates of
# deliverable months (but not per partner). In addition, the total
# assigned months from the deliverables in the workpackage don't add  up
# to the total months in the workpackage (the latter is more).

# This is the plan here as a first pass:
# - find the total effort per partner per wp
# - for each deliverable, assign the assigned effort to the lead
# - use this assigned effort as the length of the deliverable
# - for each partner, divide the rest of the partner effort in the WP by
# the number of deliverables the partner is interested in but not leading,
# and use this average amount as their deliverable assignment.

# notes: rounding might be a problem

require 'squish'
require 'basicrdf'
require 'dbi'

# to start with we need 
# - effort per Wp per partner
# - whether lead/interest or neither per deliv/partner

# for each partner

wps = ['1','2','3','4','5','6','7','8','9','10','11','12.1','12.2','12.3','12.4']
#### should scrape this
wp_del_totals={'1'=>'13','2'=>'3','3'=>'17','4'=>'2','5'=>'3','6'=>'3','7'=>'4','8'=>'8','9'=>'3','10'=>'3','11'=>'2','12.1'=>'8','12.2'=>'2','12.3'=>'2','12.4'=>'1'}

partners = ['http://ilrt.org/','http://www.w3.org/','http://www.rl.ac.uk/','http://www-uk.hpl.hp.com/','http://www.stilo.com/']

#wps = ["4"]
#partners = ['http://ilrt.org/']

#deliv hash
delivs={}

#due hash
due={}

#wp-deliv
wp_del={}

#load data once
data = Loader.ntfile2graph '../rdf/whodoeswhat.nt'
data2 = Loader.ntfile2graph '../rdf/_esw_projdata.nt'
data3 = Loader.ntfile2graph '../rdf/partners.nt'

#merge graphs
data.tell_all data2.statements
data.tell_all data3.statements

# postgres access
DBI.connect ( 'DBI:Pg:esw-admin1', 'libby', '' ) do |dbh|


#### uncomment to load the data into postgres. You still need to turn it into nt by doing this:
#### rdfdump file:../rdf/whodoeswhat.rdf  -o ntriples -q > ../rdf/whodoeswhat.nt
#### rdfdump file:../rdf/partners.rdf  -o ntriples -q > ../rdf/partners.nt
#### rdfdump file:../rdf/_esw_projdata.rdf  -o ntriples -q > ../rdf/_esw_projdata.nt

#  sql_inserts = data.toSQLInserts ("uri=file://ignore")
#  puts "-"
#  begin 
#    dbh.do "delete from triples where assertid = 'uri=file://ignore'"
#  rescue 
#    puts "DBI: Error in sql delete, msg: #{$!}"
#  end
#  puts "+"
#  sql_inserts.each do |sql_insert|
#   begin 
#      print '.'
#      dbh.do sql_insert 
#    rescue 
#      # puts "DBI: Error in sql insert, sql: #{sql_insert} msg: #{$!}"
#      # most errors are duplicate keys. should filter those out. 
#    end
#  end


#### for each partner/wp combination collect 
#### list of delivs leading
#### list of delivs interested in
#### average amount of effort per deliverable interested in

  wps.each do |x|

   puts "\n\nWorkpackage #{x}";

	partners.each do |y|
	puts "\nPARTNER #{y}";

#### total effort for this wp per partner

	total_effort=0;
	total_lead_effort=0;

	squish="select   ?effort, ?wp,  ?org, 
	WHERE 
	(pm::workpackageUrl ?org http://www.w3.org/2001/sw/Europe/plan/workpackages/live/esw-wp-#{x}.html) 
	(pm::partnerHomepage ?org #{y})
	(pm::effort ?org ?effort) 
	USING 
	 pm for http://www.w3.org/2002/02/esw/pm# 
	foaf for http://xmlns.com/foaf/0.1/"

	query = SquishQuery.new.parseFromText squish

	#puts query.toSQLQuery

	#postgres  query 
	   dbh.select_all( query.toSQLQuery) do |row|
	     del=ResultRow.new row
	     # puts "Row: "+del.inspect

	     puts "Effort: "+del.effort+" PARTNER #{y} WP "+del.wp
	     total_effort=del.effort;

	   end  

	puts "\nTotal effort for WP #{x} for partner #{y} is #{total_effort}"

	wp_hashy=delivs["#{x}"]

	if (wp_hashy==nil)
	   wp_hashy={}
	   delivs["#{x}"]=wp_hashy
	end

	wp_hashy["#{y}effort"]=total_effort

#### now get deliverables from this WP which {y} is leading
#### assume the assighned affort (i.e. the estimated effort for the deliv)==the duration of deliv
#### second query

#### keep track of delivs leading, for checking against interest

	lead_interest_check=[]

	squish2="select  ?email, ?del, ?assignedeffort, ?due, 
	WHERE 
	(foaf::workplaceHomepage ?email #{y})  
	(pm::deliv_lead_email ?del ?email)
	(pm::deliverable http://www.w3.org/2001/sw/Europe/plan/workpackages/live/esw-wp-#{x}.html ?del) 
	(pm::duration ?del ?assignedeffort) 
	(pm::relMonthDue ?del ?due) 
	USING 
	pm for http://www.w3.org/2002/02/esw/pm# 
	foaf for http://xmlns.com/foaf/0.1/" 

	query2 = SquishQuery.new.parseFromText squish2

	puts "\nLEAD \n"

	#postgres  query stuff
	   dbh.select_all( query2.toSQLQuery) do |row|

	     del=ResultRow.new row
	     lead_interest_check[lead_interest_check.size+1]="#{del.del}"

	     total_lead_effort=total_lead_effort+(del.assignedeffort.to_i)
	     fte1_start=(del.due.to_i)-(del.assignedeffort.to_i)
	     puts "Effort for deliverable : "+del.del+" for #{y} is "+del.assignedeffort+" DUE "+del.due+" 1FTE start #{fte1_start}"


#### assuming 1FTE, the lead partner start for the deliv is the same as assigned effort
#### so can be dealt with later

	     hashy=delivs["#{del.del}"]
		if (hashy==nil)
		   hashy={}
		   delivs["#{del.del}"]=hashy
		end

	     hashy["#{y}"] = del.assignedeffort
	     hashy["#{y}start"] = fte1_start
	     hashy["lead"] = "#{y}"

	     hashy["#{y}leademail"] = hashy["#{y}leademail"].to_s+"<#{del.email}>"

	     due["#{del.del}"]=del.due

	     puts "LEAD is #{y}"

	     wp_del_arr=wp_del["#{x}"]

#### keeping a list of the deliverables per WP

		if (wp_del_arr==nil)
		   wp_del_arr=[]
		   wp_del["#{x}"]=wp_del_arr
		end

		wp_del_arr[wp_del_arr.size+1]="#{del.del}"

	   end  

#### keeping an eye on unassigned effort for this parter/WP.
#### if there is unassigned, try to distribute it evenly amoung delivs partner has an interest in.

	unassigned=(total_effort.to_i)-(total_lead_effort.to_i)
	puts "Total assigned effort is #{total_lead_effort}\n#{unassigned} is unassigned for partner #{y} for WP #{x}\ntrying interest...."


#### dels where have an interest
#### third query

	squish3="select  ?email, ?del, ?due, 
	WHERE 
	(foaf::workplaceHomepage ?email #{y})  
	(pm::deliv_interest_email ?del ?email)
	(pm::deliverable http://www.w3.org/2001/sw/Europe/plan/workpackages/live/esw-wp-#{x}.html ?del) 
        (pm::relMonthDue ?del ?due)
	USING 
	 pm for http://www.w3.org/2002/02/esw/pm# 
	foaf for http://xmlns.com/foaf/0.1/" 

	query3 = SquishQuery.new.parseFromText squish3

	puts "\nINTEREST"
	count_interest=0;

	#postgres  query stuff
	   dbh.select_all( query3.toSQLQuery) do |row|
	     del=ResultRow.new row

	     puts "DELIVERABLE: "+del.del + " email "+del.email+" due "+del.due  # purtier than Java

	     due["#{del.del}"]=del.due

		if(!lead_interest_check.include?("#{del.del}"))
		     count_interest=count_interest+1;
		     puts "del is not already lead so counts as an interest"
		else
		     puts "partner is already lead on del - not counted as interest"
		end

	   end  

	av_effort=0
	
#### divide spare effort between the deliverables interested in in this WP.
#### assumes 1FTE

	if(unassigned!=0 && count_interest!=0) 
	  av_effort=unassigned/count_interest
	end

	puts "\ninterested in #{count_interest} deliverables; this makes average FTE1 effort for each one of #{av_effort}"

	if(count_interest!=0)
		unassigned=0;
	end


	wp_hashy=delivs["#{x}"]

	if (wp_hashy==nil)
	   wp_hashy={}
	   delivs["#{x}"]=wp_hashy
	end

#### keeping track of unassigned effort in the WP for this partner

	wp_hashy["#{y}unassigned"]=unassigned


#### given the efforts in interest delivs, works out 1FTE start months for each partner

	   dbh.select_all( query3.toSQLQuery) do |row|

	     del=ResultRow.new row

	     hashy2=delivs["#{del.del}"]

			if (hashy2==nil)
			   hashy2={}
			   delivs["#{del.del}"]=hashy2
			end

	     hashy2["#{y}interestemail"] = hashy2["#{y}interestemail"].to_s+"<#{del.email}>"

#### watch out for already assigned effort

		if(!lead_interest_check.include?("#{del.del}"))
		     starts= (del.due.to_i)-(av_effort.to_i)
		     puts "\n#{del.del} is due "+del.due+" so partner #{y} starts #{starts} assuming FTE1"
		     count_interest=count_interest+1;

#		     hashy2=delivs["#{del.del}"]

#			if (hashy2==nil)
#			   hashy2={}
#			   delivs["#{del.del}"]=hashy2
#			end

#### storing effort and starts for this deliv/partner

		     hashy2["#{y}"] = av_effort
		     hashy2["#{y}start"] = starts
		     hashy2["#{y}interest"] = "#{y}"

		end

	        wp_del_arr=wp_del["#{x}"]

		if (wp_del_arr==nil)
		   wp_del_arr=[]
		   wp_del["#{x}"]=wp_del_arr
		end

	     wp_del_arr[wp_del_arr.size+1]="#{del.del}"

	   end  

     end #partners

  end #wps

end #dbi

#### print out everything for checking

delivs.each_key do  |k| 
puts "key   "+k.to_s

hashy=delivs[k]

  hashy.each_key do  |kk| 
  puts "\tkey   "+kk.to_s
  puts "\tvalue "+hashy[kk].to_s

  end

end

#### print out CSV version

puts "********CSV********"

csvFile=File.new("../rdf/_deliv_detail.csv","w")

csvFile.write("SWAD-Europe deliverable effort detail

 libby.miller@bristol.ac.uk (thanks danbri :) 2002-07-25

 This spreadsheet is generated by the Ruby program 
http://www.w3.org/2001/sw/Europe/plan/workpackages/live/bin/deliv-start-end.rb

which uses the RDF files
http://www.w3.org/2001/sw/Europe/plan/workpackages/live/rdf/partners.rdf
http://www.w3.org/2001/sw/Europe/plan/workpackages/live/rdf/whodoeswhat.rdf
http://www.w3.org/2001/sw/Europe/plan/workpackages/live/rdf/_esw_projdata.rdf

 The aim here is to pull out enough information to allow the
 calculation of start dates for deliverables. This is quite complex
 because all we have is months per partner per WP and guesstimates of
 deliverable months (but not per partner). In addition the total
 assigned months from the deliverables in the workpackage don't add  up
 to the total months in the workpackage (the latter is more).

 This is the plan here:
 - find the total effort per partner per wp
 - for each deliverable assign the assigned effort to the lead
 - use this assigned effort as the length of the deliverable
 - for each partner divide the rest of the partner effort in the WP by
 the number of deliverables the partner is interested in but not leading
 and use this average amount as their deliverable assignment.

NOTES

 WPs start at the beginning of a month and end at the end of it.        
 Therefore total months on a deliverable may appear not to lead to the  
 right end date - but they in fact do.


 FTE1 is assumed throughout which leads to some deliverables starting
 before the beginning of the project. You may need to fiddle with this 
 for certain partners to get a more accurate view of their effort for 
 any given month.

 The sum of deliverables for any partner on any WP plus unassigned effort 
 should equal total effort on that Wp for that partner.

 2.1 may seems strange, but 0 effort due month 4 means start month 4!

")

#### for the gantt chart

gantt=""
smallgantt=""

#### loop through the WPs in order

wps.each do |z| 

#### IDs for the partners (URLs)

csvFile.write("\n\n\nWP #{z}")
csvFile.write("\nPartner IDs,,,")

  partners.each do |d| 

  csvFile.write("#{d},")

  end

csvFile.write("\n,,,")

#### calculated effort per deliverable for each partner in this WP

csvFile.write("\nCalculated deliverable effort per partner,,,")


  wp_del_arr=wp_del["#{z}"]-[nil]
  wp_del_arr.sort!
#puts "\ndels wp "+wp_del_arr.to_s

csvFile.write("\n")

     wp_del_arr.each do |a|

##### get the hash from delivs

	hashy=delivs[a]

#### short name for deliverable

	short_del=a.gsub(/http.*del_/, "")
	csvFile.write("\n#{short_del},,,")

#### for each partner get value - this is effort on deliv

	   partners.each do |b| 

	   effort=hashy[b]

#puts "\neffort "+effort.to_s

 	    if(effort==nil)
	     effort=0
	    end	

	   csvFile.write(effort.to_s+",")

           end

     end

  total=wp_del_totals["#{z}"]

  csvFile.write("\n\ntotal number of deliverables should be "+total.to_s+" actual is "+wp_del_arr.size.to_s)

    if(total.to_i!=wp_del_arr.size.to_i)
	csvFile.write(" - deliverable count mismatch!")
    else
	csvFile.write(" - ok")
    end



#### total effort per partner for this WP
csvFile.write("\n,,,")
csvFile.write("\nPartner total workpackage effort,,,")
csvFile.write("\n,,,")

  partners.each do |c| 

   hhh=delivs["#{z}"]

   eff=hhh["#{c}effort"]

   if(eff!=nil)
    csvFile.write("#{eff},")
   else
    csvFile.write("0,")
   end

  end

#### unassigned effort per partner for this WP

csvFile.write("\nPartner total workpackage unassigned effort,,,")
csvFile.write("\n,,,")

  partners.each do |r| 

   hhh=delivs["#{z}"]

   eff=hhh["#{r}unassigned"]

   if(eff!=nil)
    csvFile.write("#{eff},")
   else
    csvFile.write("0,")
   end

  end


#### start months for each deliv

  csvFile.write("\n\nCalculated deliverable start per partner,,,")
  csvFile.write("\n")

  wp_start=30

     wp_del_arr.each do |q|


#### get the hash froom delivs

	hashy=delivs[q]
	start_partner=hashy["lead"]

#### short name for deliv

	short_del=q.gsub(/http.*del_/, "")
	csvFile.write("\n#{short_del},,,")


#### for each partner get value - this is effort on deliv

	   partners.each do |b| 

	   #gantt =gantt+ "#{short_del},"
	   #gantt =gantt+ "#{b},"

#	   gantt=gantt+"<td>"+short_del+"</td><td>"+b+"</td>"

	   start=hashy["#{b}start"]

	   if(start==nil)
	     start='-'
           else
                if(start.to_i<wp_start.to_i)
                wp_start=start
                end
	   end	

#### print out a table


#	   gantt =gantt+ "#{start.to_s},"

#		if(start!="-")
#		   gantt =gantt+ due[q].to_s+",\n\n"
#	 	else
#		   gantt =gantt+"-\n\n"
#		end

	   realstart=start.to_i+1

	   endy=due["#{q}"]

	   if(start.to_i==endy.to_i)
		csvFile.write(start.to_s+",")
	   else
		csvFile.write(realstart.to_s+",")
	   end

	   if(start!="-")

	   bgcol="blue"

	     if(b==start_partner)
		   gantt=gantt+"<td>"+short_del+"</td><td><b>"+b+"</b></td>"
		   bgcol="red"
	     else
		   gantt=gantt+"<td>"+short_del+"</td><td>"+b+"</td>"
	           bgcol="blue"
		     end

#########

	     if(b==start_partner)
		smallgantt=smallgantt+"<tr>"
		smallgantt=smallgantt+"<td>"+q+"</td><td><b>"+b+"</b></td>"


		   for qq in (1..30)
		     if (start.to_i==endy.to_i && start.to_i==qq)
			smallgantt=smallgantt+"<td bgcolor=\"#ddddff\">&nbsp;</td>\n"
		     elsif (qq < realstart)
			smallgantt=smallgantt+"<td>&nbsp;</td>\n"
		     elsif ((qq >=realstart.to_i) && (qq <= endy.to_i))

			  if(qq==realstart.to_i ||(qq==1 && realstart<1))
				smallgantt=smallgantt+"<td bgcolor=\""+bgcol+"\">"+realstart.to_s+"</td>\n"
			  elsif (qq==endy.to_i)
				smallgantt=smallgantt+"<td bgcolor=\""+bgcol+"\">"+endy.to_s+"</td>\n"
			  else
				smallgantt=smallgantt+"<td bgcolor=\""+bgcol+"\">&nbsp;</td>\n"
			  end
		     elsif (qq>endy.to_i)
			smallgantt=smallgantt+"<td>&nbsp;</td>\n"
		     end
		   end
		smallgantt=smallgantt+"<tr>"

	     end

#########

		   for zz in (1..30)
		     if (start.to_i==endy.to_i && start.to_i==zz)
			gantt=gantt+"<td bgcolor=\"#ddddff\">&nbsp;</td>\n"
		     elsif (zz < realstart)
			gantt=gantt+"<td>&nbsp;</td>\n"
		     elsif ((zz >=realstart.to_i) && (zz <= endy.to_i))

			  if(zz==realstart.to_i ||(zz==1 && realstart<1))
				gantt=gantt+"<td bgcolor=\""+bgcol+"\">"+realstart.to_s+"</td>\n"
			  elsif (zz==endy.to_i)
				gantt=gantt+"<td bgcolor=\""+bgcol+"\">"+endy.to_s+"</td>\n"
			  else
				gantt=gantt+"<td bgcolor=\""+bgcol+"\">&nbsp;</td>\n"
			  end
		     elsif (zz>endy.to_i)
			gantt=gantt+"<td>&nbsp;</td>\n"
		     end
		   end

	   end

	     gantt=gantt+"</tr><tr>"

           end

#   gantt=gantt+"</tr><tr>"


#### keeping track of the lead partner and overall deliverable start

#	csvFile.write(",start,"+hashy["#{start_partner}start"].to_s)
	csvFile.write(",realstart,"+hashy["#{start_partner}start"].to_s)
	csvFile.write(",end,"+due["#{q}"].to_s)
        csvFile.write(",lead,"+start_partner)

     end

  csvFile.write("\n\nWP start is "+wp_start.to_s)

#### emails of leads and interested

  csvFile.write("\n\nEmail contacts for leads and interested people per partner,,,")
  csvFile.write("\n")

     wp_del_arr.each do |q|

#### get the hash from delivs

	hashy=delivs[q]

#### short name for deliv

	short_del=q.gsub(/http.*del_/, "")
	csvFile.write("\n#{short_del},,,")

#### for each partner get value - this is effort on deliv

	   partners.each do |b| 

	   leademail=hashy["#{b}leademail"]
	   inemail=hashy["#{b}interestemail"]

	   if(leademail==nil)
	     leademail=''
	   else
	      csvFile.write("lead: "+leademail.to_s+" ")
	   end	
	   if(inemail==nil)
	     inemail=''
	   else
	    csvFile.write("interest: "+inemail.to_s+" ")
	   end	

	   csvFile.write(",")
           end

     end

end


csvFile.close

puts "\n********CSV********"


puts "\n********GANTT********"


aFile=File.new("../_gantt.html","w")
bFile=File.new("../_smallgantt.html","w")
bFile.write("<html><head><title>SWAD-Europe Deliverables Small Gantt Chart</title>")
bFile.write("\n<body bgcolor=\"#ffffff\"><h3>SWAD-Europe Deliverables Small Gantt Chart</h3>")
bFile.write("\n<table><tr><td>lead effort</td><td bgcolor=\"red\">&nbsp;</td></tr>")
bFile.write("\n<table border=\"1\"><tr><td>Deliv no</td><td>Partner</td>\n")

	for y in (1..30)
	  if(y<10)
		bFile.write("<td>&nbsp;"+y.to_s+"</td>")
	  else
		bFile.write("<td>"+y.to_s+"</td>")
	  end
	end

	bFile.write("</tr>")
	bFile.write(smallgantt)
	bFile.write("</tr></table></body></html>\n")
	bFile.close




aFile.write("<html><head><title>SWAD-Europe Deliverables Gantt Chart</title>")
#aFile.write("<link rel="StyleSheet" type="text/css" href="http://www.w3.org/2001/sw/Europe/style/swad-europe-grey.css" />

aFile.write("\n<body bgcolor=\"#ffffff\"><h3>SWAD-Europe Deliverables Gantt Chart</h3>")
aFile.write("\n<p>Key:</p>")
aFile.write("\n<table><tr><td>lead effort</td><td bgcolor=\"red\">&nbsp;</td></tr>")
aFile.write("\n<tr><td>interest effort</td><td bgcolor=\"blue\">&nbsp;</td></tr>")
aFile.write("\n<tr><td>interest with no effort</td><td bgcolor=\"#ddddff\">&nbsp;</td></tr></table>")
aFile.write("\n<p>Note: effort starts at the beginning of a month and ends at the end of it. Month 1 is May 2002.</p>")

aFile.write("\n<table border=\"1\"><tr><td>Deliv no</td><td>Partner</td>\n")


	for y in (1..30)
	  if(y<10)
		aFile.write("<td>&nbsp;"+y.to_s+"</td>")
	  else
		aFile.write("<td>"+y.to_s+"</td>")
	  end
	end

	aFile.write("</tr>")
	aFile.write(gantt)
	aFile.write("</tr></table></body></html>\n")
	aFile.close

puts "\n********GANTT********"




	#puts "debug..."
	#puts resultset.debug_report

#	log = File::new querylog, File::CREAT|File::RDWR, 0644
#	log.puts resultset.debug_report
#	log.close