Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Quick and dirty PowerShell interface for Cronos timesheets (imports data from csv)
#tSF for Cronos
#Lennert De Swaef
#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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment