Quick and dirty PowerShell interface for Cronos timesheets (imports data from csv)

csvto-tia

#tSF for Cronos
#10 december 2014
#11/12/2014:added tSF production api key
#api overview in doc on https://timesheetservicetest.cronos.be/
#overview @ https://timesheettest.cronos.be
############################################################################

 [CmdletBinding()]
 Param(
  # [Parameter(Mandatory=$True)]
	[string]$filename
 )



$guid = "get your own guid"
#$tiaWebserviceUri="https://timesheetservicetest.cronos.be/api" #test
$tiaWebserviceUri="https://timesheetservice.cronos.be/api" #production!




############################################################################
$textvar = @"
you don't need iconos in the credentials
csv titles should be datum - TSCode - Sum of uren
datum field should be populate by Excel's short date format
datum field in csv needs to be exactly d/MM/yyyy
don't be confused when you see MM/dd/yyyy passing by because tia only supports this format
"@

write-host $textvar -backgroundcolor yellow -foregroundcolor black
#$conf=read-host "you sure?"

function PromptFile
{
    $loadNamespace = [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
    [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null
    $myDialog = New-Object System.Windows.Forms.OpenFileDialog
    $myDialog.Title = "Please select a file"
    $myDialog.InitialDirectory = $env:userprofile
    $myDialog.Filter = "Csv (*.csv*)|*.csv*"
    $result = $myDialog.ShowDialog()
    if($result -eq "OK") 
	{
		$inputFile = $myDialog.FileName
		return $inputFile
    }
    else 
	{
		Write-Host "Cancelled by user"
    }
}

#$var=@()
Function get-tscodeId([string]$name)
{
	#$url="https://timesheetservicetest.cronos.be/api/TsCode/GetAllPaged"
	$url="$tiaWebserviceUri/TsCode/GetAllPaged"
	$var = New-Object -TypeName PSObject -Property @{
										username=$username;
										password=$userpass;
										token="";
										ApiKey="$guid";
										Skip="0";
										Take="999"
										}
	$json = $var | convertto-json
	$answer = Invoke-RestMethod -Uri $url -Method Post -ContentType "application/json" -Body $json 
	$id = $answer.value | ?{$_.name -like ("$name")} 
	$name=$id.name
	$id=$id.id
	
	#write-host "returning $id for tscode: $name"
	return $id
}

Function get-alltscodeId
{
	#$url="https://timesheetservicetest.cronos.be/api/TsCode/GetAllPaged"
	$url="$tiaWebserviceUri/TsCode/GetAllPaged"
	$var = New-Object -TypeName PSObject -Property @{
										username="$username";
										password="$userpass";
										token="";
										ApiKey="$guid";
										Skip="0";
										Take="999"
										}
	$json = $var | convertto-json
	$answer = Invoke-RestMethod -Uri $url -Method Post -ContentType "application/json" -Body $json 
	return $answer.value
}			

#date in month/day/year
Function save-tsentry([string]$tscodeId, [string]$actualHours, [string]$date, [string]$comments="")
{
	#$url="https://timesheetservicetest.cronos.be/api/Work/Save"
	$url="$tiaWebserviceUri/Work/Save"									
	$var = New-Object -TypeName PSObject -Property @{
										username="$username";
										password="$userpass"
										token="";
										ApiKey="$guid";
										workItemId="0";
										tscodeId="$tscodeId";
										actualHours="$actualHours";
										extraHours="";
										interventionHours="";
										standbyHours="";
										date="$date";
										comments="$comments"
										}
	$json = $var | convertto-json
	$answer = Invoke-RestMethod -Uri $url -Method Post -ContentType "application/json" -Body $json 
	$answer=$null
	#$answer.value | fl *
}

function get-bydate([string]$date)
{
	#$url="https://timesheetservicetest.cronos.be/api/Work/GetByDate"
	$url="$tiaWebserviceUri/Work/GetByDate"			
	$var = New-Object -TypeName PSObject -Property @{
										username="$username";
										password="$userpass";
										token="";
										ApiKey="$guid";
										date="$date"
										}
	$json = $var | convertto-json
	$answer = Invoke-RestMethod -Uri $url -Method Post -ContentType "application/json" -Body $json 
	return $answer.value
}

function get-betweendates([string]$FromDate, [string]$ToDate)
{
	$url="$tiaWebserviceUri/Work/GetBetweenDates"			
	$var = New-Object -TypeName PSObject -Property @{
										username="$username";
										password="$userpass";
										token="";
										ApiKey="$guid";
										FromDate="$FromDate";
										ToDate="$ToDate"
										}
	$json = $var | convertto-json
	$answer = Invoke-RestMethod -Uri $url -Method Post -ContentType "application/json" -Body $json 
	return $answer
}

########################
####### MAIN ###########
########################
#import the csv
if(($filename -eq "") -or ($filename -eq $null))
{
	$filename = PromptFile
}
try
{
	$csv=import-csv $filename -delimiter ";"
}
catch
{
	write-error "Cannot import csv - breaking;" 
	break;
}
$creds=get-credential
$username=$creds.username
[String]$userpass = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($creds.password)); 

write-host "all dates are in MM/dd/yyy" -backgroundcolor red -foregroundcolor black


#get all applicable timesheet codes from user
$tscodeHolder = get-alltscodeId
#some placeholders
$fromdate="12/31/2040"
$todate="01/01/1900"
$confirmed=@()
#iterate over csv
foreach($entry in $csv)
{
	#placeholders
	$tscode=""
	$uren=""
	$date=""
	#get date and convert it to tia datetime format
	$datebelgium=[datetime]::ParseExact($entry.datum, "d/MM/yyyy", $null)
	$date=($datebelgium).tostring("MM/dd/yyyy") #month/day/year
	#get first and last day of date range
	if($date -lt $fromdate)
	{
		$fromdate=$date
	}
	if($date -gt $todate)
	{
		$todate=$date
	}
	
	#$datebelgium = ([datetime]$entry.date).tostring("dd/MM/yyyy") #just for write-host feedback to user
	$uren=$entry."Sum of uren" #amount of billable hours
	#$tscode = get-tscodeid $entry.tscode #find timesheetcode slow way
	$tempvarts=$entry.tscode
	$tscode = $tscodeHolder | ?{$_.name.trim(" ") -eq ("$tempvarts")}  #find the actual timesheetcode fast
	$tscodeid=$tscode.id
	$tscodename=$tscode.name
	if($tscodename -eq $null)
	{
		$tscodename = $tempvarts
	}
	
	if( ($tscode -ne "") -and ($tscode -ne $null) -and ($tscodeid -ne "") -and ($tscodeid -ne $null) ) #check if all arguments have a value
	{
		if ( ($uren -ne "") -and ($date -ne "") )
		{
			try{
				#write-host "adding $uren hours of work on $datebelgium to $tscode"
				#save-tsentry $tscode $uren $date #actually saving to cronos tia
				
				$confirmed += New-Object -TypeName PSObject -Property @{
									date="$date"
									uren="$uren"
									tscodeid="$tscodeid"
									tscodename="$tscodename"
									state="ok"
									}
				
				
			}
			catch #exception handler
			{
				#write-host "something went wrong" -backgroundcolor yellow -foregroundcolor black
				#$errormsg = $_
				#write-host $errormsg  -backgroundcolor yellow -foregroundcolor black
				$confirmed += New-Object -TypeName PSObject -Property @{
					date="$date"
					uren="$uren"
					tscodeid="$tscodeid"
					tscodename="$tscodename"
					state="not ok"
					}
			}
		}
		else
		{
			#write-host "uren or date not in correct format" -backgroundcolor red -foregroundcolor black
			$confirmed += New-Object -TypeName PSObject -Property @{
					date="$date"
					uren="$uren"
					tscodeid="$tscodeid"
					tscodename="$tscodename"
					state="not ok - uren or date not in right format"
					}
		}
	}
	else
	{
		#write-host "tscode not found for $entry.tscode" -backgroundcolor red -foregroundcolor black
		$confirmed += New-Object -TypeName PSObject -Property @{
					date="$date"
					uren="$uren"
					tscodeid="$tscodeid"
					tscodename="$tscodename"
					state="not ok - tscode not found"
					}
	}
}

$confirmed | ft state,tscodename,date,uren,tscodeid -auto

$cont=read-host "import into tia? (y/n)"



if(($cont -eq "y") -or ($cont -eq "Y"))
{
	foreach($value in $confirmed)
	{
		if($value.state -eq "ok")
		{
			try{
					#$datebelgium = ([datetime]$value.date).tostring("dd/MM/yyy") #just for write-host feedback to user
					$id=$value.tscodeid
					$uren=$value.uren
					$date=$value.date
					$tscode=$value.tscodename
					write-host "adding $uren hours of work on $date to $tscode"
					save-tsentry $id $uren $date  #actually saving to cronos tia
					#read-host
					
				}
			catch #exception handler
				{
					write-host "something went wrong" -backgroundcolor yellow -foregroundcolor black
					$errormsg = $_
					write-host $errormsg  -backgroundcolor yellow -foregroundcolor black
				}
			}
			else
			{
				write-host "skipping this one" $value.tscodename
			}
	}
}
else
{
	write-host "user aborted"
}

#show what's actually imported into tia
$result = get-betweendates $fromdate $todate
$result = $result.value 
#$result | out-gridview

#explorer "https://timesheets.cronos.be/"
#explorer "https://timesheettest.cronos.be"