<code>
Option Public
Option Explicit
Use "sds.notes.common.script"
Use "aze.common.routines"
Uselsx "*LSXODBC"
Sub Initialize
If Hour(Now)>8 Then
Print "Agent läuft nur zwischen 0 und 8 Uhr in der Früh"
Exit Sub
End If
Print "Agent: Import aus WinTime gestartet"
Dim Session As New NotesSession
Dim db As NotesDatabase
Dim teldb As New NotesDatabase("ATSDSN02/SDS-WIEN/AT", "sds\sdstel.nsf")
Dim telview As NotesView
Dim view As NotesView
Dim coll As NotesDocumentCollection
Dim doc As NotesDocument
Dim Key(0) As String
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Set qry.Connection = con
Set result.Query = qry
Set db = Session.CurrentDatabase
Dim Jahr As String
Dim kw As Variant
Dim UnderKW As Variant
Dim tempKW As Variant
Dim status As Integer
status = con.ConnectTo("wintime", "wtime", "*********")
Set view = db.GetView("(DbLookup\nach Woche)")
Set telview = teldb.getView("(DbLookup\Gesamt Aktive)")
Jahr = CurrentYear
UnderKW = Format(Now, "ww") - 5
' Nur für 2005
If underkw < 1 Then underkw = 1
For Kw = Cint(Format(Now, "ww")) To Cint(UnderKW) Step -1
Key(0) = Right("0" & Cstr(Kw), 2)
Set coll = view.GetAllDocumentsByKey(Key, True)
Dim i As Integer
For i = 1 To coll.count
Set doc = coll.GetNthDocument(i)
If Kw = Cint(Format(Now, "ww")) - 1 Then
updateStammdaten doc, telview
End If
updateWeek con, qry, result, doc, Jahr, kw
Next
Print "(AZE) Woche " & KW & " erledigt!"
Next
con.Disconnect
Print "Successfully ended Import aus Wintime"
If Day(Now) = 1 Then
Set view = db.GetView("Verwaltung-Buchungen")
Set doc = view.getFirstDocument
While Not doc Is Nothing
doc.JahrMonat = Clng(Cstr(Year(Now)) & Right("0" & Cstr(Month(Now)), 2))
doc.Monat = Right("0" & Cstr(Month(Now)), 2)
Call doc.save(True, True)
Set doc = view.GetNextDocument(doc)
Wend
Print "Successfully moved Booking Documents."
End If
End Sub
Sub updateWeek(con As ODBCConnection, qry As ODBCQuery, result As ODBCResultSet, doc As NotesDocument, Jahr As String, kw As Variant)
Dim FirstName As String
Dim FirstName2 As String
Dim LastName As String
Dim FullWeek As String
Dim Datum As Variant
Dim AbwArt As String
Dim Komm As Variant
Dim Komm_Zeit As Variant
Dim Geht As Variant
Dim Geht_Zeit As Variant
Dim Ist_Zeit As Variant
Dim SollZeit As Variant
Dim temp_kw As Variant
Dim Summe As Double
Dim x As Integer
If kw = "39" Then
Stop
End If
FirstName = Ucase(Left(doc.Benutzername(0), Instr(doc.Benutzername(0), " ") - 1))
LastName = Ucase(Right(doc.Benutzername(0), Len(doc.Benutzername(0)) - Instr(doc.Benutzername(0), " ")))
FirstName2 = nReplaceSubstring(FirstName, "-", " ")
'TTagZei - qry.SQL = | SELECT TPerTab.Per_Name, TPerTab.Per_Vorname, TTagZei.* FROM TTagZei, TPerTab WHERE TTagZei.TZe_PersNr = TPerTab.Per_PersNr and TTagZei.TZe_JahrKW = | & Jahr & Right("0" & kw, 2) & | and ((TPerTab.Per_Name = '| & LastName & |' and TPerTab.Per_Vorname = '| & FirstName & |') or (TPerTab.Per_Name = '| & LastName & |' and TPerTab.Per_Vorname = '| & FirstName2 & |')) and (TTagZei.TZe_AbwArt Is NULL or TTagZei.TZe_AbwArt = 'DR' or TTagZei.TZe_AbwArt = 'DG' or TTagZei.TZe_AbwArt = 'AUS' or TTagZei.TZe_AbwArt = 'HEIM') and TTagZei.TZe_ZEitArt is not NULL |
'TTagLes - qry.SQL = | SELECT TPerTab.Per_Name, TPerTab.Per_VorName, TTagLes.*, TTagMoS.TMS_Datum, TTagMoS.TMS_JahrKw FROM TPerTab, TTagLes, TTagMoS WHERE ((TPerTab.Per_Name = '| & LastName & |' and TPerTab.Per_Vorname = '| & FirstName2 & |') or (TPerTab.Per_Name = '| & LastName & |' and TPerTab.Per_Vorname = '| & FirstName2 & |')) and TTagLes.TLe_PersNr = TPerTab.Per_PersNr and TTagLes.TLe_PersNr = TTagMos.TMS_PersNr and TTagLes.TLe_Datum = TTagMoS.TMS_Datum and TTagMos.TMS_JahrKW = | & Jahr & Right("0" & kw, 2) & | and TTagLes.TLe_VonZeit is not Null and TTagLes.TLe_BisZeit is not Null and (TTagLes.TLe_AbwArt = 'DR' or TTagLes.TLe_AbwArt = 'DG' or TTagLes.TLe_AbwArt = 'AUS' or TTagLes.TLe_AbwArt = 'HEIM' or TTagLes.TLe_AbwArt is Null) |
'qry.SQL = | SELECT TPerTab.Per_Name, TPerTab.Per_VorName, TTagLes.*, TTagMoS.TMS_JahrKw FROM TPerTab, TTagLes, TTagMoS WHERE ((TPerTab.Per_Name = '| & LastName & |' and TPerTab.Per_Vorname = '| & FirstName2 & |') or (TPerTab.Per_Name = '| & LastName & |' and TPerTab.Per_Vorname = '| & FirstName2 & |')) and TTagLes.TLe_PersNr = TPerTab.Per_PersNr and TTagLes.TLe_PersNr = TTagMos.TMS_PersNr and TTagLes.TLe_Datum = TTagMoS.TMS_Datum and TTagMos.TMS_JahrKW = | & Jahr & Right("0" & kw, 2) & | and Not(TTagLes.TLe_VonZeit Is Not Null and TTagLes.TLe_BisZeit Is Null) and (TTagLes.TLe_AbwArt = 'DR' or TTagLes.TLe_AbwArt = 'DG' or TTagLes.TLe_AbwArt = 'AUS' or TTagLes.TLe_AbwArt = 'HEIM' or TTagLes.TLe_AbwArt is Null) |
'qry.SQL = | SELECT TPerTab.Per_Name, TPerTab.Per_VorName, TTagLes.* FROM TPerTab, TTagLes WHERE ((TPerTab.Per_Name = '| & LastName & |' and TPerTab.Per_Vorname = '| & FirstName & |') or (TPerTab.Per_Name = '| & LastName & |' and TPerTab.Per_Vorname = '| & FirstName2 & |')) and TTagLes.TLe_PersNr = TPerTab.Per_PersNr and DATEPART(year, TTagLes.TLe_Datum) = | & Cint(Jahr) & | and TTagLes.TLe_KW = | & Right("0" & kw, 2) & | and Not(TTagLes.TLe_VonZeit Is Not Null and TTagLes.TLe_BisZeit Is Null) and (TTagLes.TLe_AbwArt = 'DR' or TTagLes.TLe_AbwArt = 'DG' or TTagLes.TLe_AbwArt = 'AUS' or TTagLes.TLe_AbwArt = 'HEIM' or TTagLes.TLe_AbwArt is Null) |
' If kw = "53" Then
' qry.SQL = | SELECT zeiten_notes.* FROM zeiten_notes WHERE ((Name = '| & LastName & |' and Vorname = '| & FirstName & |') or (Name = '| & LastName & |' and Vorname = '| & FirstName2 & |')) and DATEPART(year, TLe_Datum) = | & Cint(Jahr) & | and DATEPART(month, TLe_Datum) = 1 and TLe_KW = 52|
' Elseif kw = "52" Then
' qry.SQL = | SELECT zeiten_notes.* FROM zeiten_notes WHERE ((Name = '| & LastName & |' and Vorname = '| & FirstName & |') or (Name = '| & LastName & |' and Vorname = '| & FirstName2 & |')) and DATEPART(year, TLe_Datum) = | & Cint(Jahr) & | and DATEPART(month, TLe_Datum) = 12 and TLe_KW = 52|
' Else
qry.SQL = | SELECT zeiten_notes.* FROM zeiten_notes WHERE ((Name = '| & LastName & |' and Vorname = '| & FirstName & |') or (Name = '| & LastName & |' and Vorname = '| & FirstName2 & |')) and DATEPART(year, TLe_Datum) = | & Cint(Jahr) & | and TLe_KW = | & Right("0" & kw, 2)
' End If
result.execute
If result.GetError <> DBstsSUCCESS Then
Print con.GetExtendedErrorMessage,, con.GetError & " " & con.GetErrorMessage
Exit Sub
End If
If result.IsResultSetAvailable Then
Call doc.RemoveItem("Komm_wt")
Call doc.RemoveItem("Geht_wt")
Call doc.RemoveItem("Art_wt")
Call doc.RemoveItem("Diff_wt")
Do
result.NextRow
FullWeek = Right("0" + Cstr(result.GetValue("TLe_Kw", FullWeek)),2)
Datum = result.GetValue("TLe_Datum", Datum)
AbwArt = result.GetValue("AbwArt", AbwArt)
SollZeit = result.GetValue("Per_TagSollZeit", SollZeit)
If AbwArt = "" Or Isnull(AbwArt) Then AbwArt = "-"
Komm_Zeit = result.GetValue("TLe_VonZeit", Komm)
If Komm_Zeit <> "" Then
Komm_Zeit = Cdat(Komm_Zeit)
Komm_Zeit = Cdat(Datum) + (Komm_Zeit - Dateserial(Year(Komm_Zeit), Month(Komm_Zeit), Day(Komm_Zeit)))
Komm = Cstr(Year(Komm_Zeit)) & "-" & Right("0" & Cstr(Month(Komm_Zeit)), 2) & "-" & Right("0" & Cstr(Day(Komm_Zeit)), 2) & " " & Right("0" & Cstr(Hour(Komm_Zeit)), 2) & ":" & Right("0" & Cstr(Minute(Komm_Zeit)), 2)
Else
If AbwArt = "DG" Or AbwArt = "DR" Or AbwArt = "AUS" Or AbwArt = "RZA" Then
Komm_Zeit = Datum
Komm = Cstr(Year(Komm_Zeit)) & "-" & Right("0" & Cstr(Month(Komm_Zeit)), 2) & "-" & Right("0" & Cstr(Day(Komm_Zeit)), 2) & " " & "08:00"
End If
End If
Geht_Zeit = result.GetValue("TLe_BisZeit", Geht)
If Geht_Zeit <> "" Then ' And Geht_Zeit <> Dateserial(1899,12,30) Then
Geht_Zeit = Cdat(Geht_Zeit)
Geht_Zeit = Cdat(Datum) + (Geht_Zeit - Dateserial(Year(Geht_Zeit), Month(Geht_Zeit), Day(Geht_Zeit)))
If Geht_Zeit < Komm_Zeit Then
Geht_Zeit = Geht_Zeit + 1
End If
Geht = Cstr(Year(Geht_Zeit)) & "-" & Right("0" & Cstr(Month(Geht_Zeit)), 2) & "-" & Right("0" & Cstr(Day(Geht_Zeit)), 2) & " " & Right("0" & Cstr(Hour(Geht_Zeit)), 2) & ":" & Right("0" & Cstr(Minute(Geht_Zeit)), 2)
Else
If AbwArt = "DG" Or AbwArt = "DR" Or AbwArt = "AUS" Or AbwArt = "RZA" Then
Geht_Zeit = Datum
If Geht_Zeit < Komm_Zeit Then
Geht_Zeit = Geht_Zeit + 1
End If
If SollZeit = 0 Then
Stop
End If
Geht_Zeit = getGehenZeit(Geht_Zeit, SollZeit)
Geht = Cstr(Year(Geht_Zeit)) & "-" & Right("0" & Cstr(Month(Geht_Zeit)), 2) & "-" & Right("0" & Cstr(Day(Geht_Zeit)), 2) & " " & Right("0" & Cstr(Hour(Geht_Zeit)), 2) & ":" & Right("0" & Cstr(Minute(Geht_Zeit)), 2)
End If
End If
If Not(doc Is Nothing) And Komm <> "" And Geht <> "" And Geht <> Dateserial(1899, 12, 30) Then
Ist_Zeit = calcISTZeit(AbwArt, GetISTZeit(Komm, Geht))
If HasItemValue(doc, "Komm_wt", Komm) = False And HasItemValue(doc, "Geht_wt", Geht) = False And Ist_Zeit > 0 Then
AppendValueToItem doc, "Komm_wt", Komm
AppendValueToItem doc, "Geht_wt", Geht
AppendValueToItem doc, "Art_wt", AbwArt
AppendValueToItem doc, "Diff_wt", Ist_Zeit
End If
End If
Loop Until result.IsEndOfData
Summe = 0
For x = 0 To Ubound(doc.Diff_wt)
If doc.Diff_wt(x) <> "" Then
Summe = Summe + Cdbl(doc.Diff_wt(x))
End If
Next
doc.Gebuchte_Summe_wt = Summe
updateBookedFields doc
' Call doc.ComputeWithForm(True, False)
Call doc.save(True, True)
End If
result.Close(DB_CLOSE)
End Sub
Sub updateStammdaten(doc As NotesDocument, telview As NotesView)
Dim teldoc As NotesDocument
Dim key(0) As String
Key(0) = Right(doc.Benutzername(0), Len(doc.Benutzername(0)) - Instr(doc.Benutzername(0), " ")) & " " & Left(doc.Benutzername(0), Instr(doc.Benutzername(0), " ") -1)
Set teldoc = telview.getDocumentByKey(key, True)
If Not (teldoc Is Nothing) Then
Call doc.ReplaceItemValue("Kostenstelle", teldoc.Kst(0))
Call doc.ReplaceItemValue("Arbeitsteam", teldoc.Arbeitsteam(0))
Call doc.ReplaceItemValue("Bereich", teldoc.Bereich(0))
Call doc.ReplaceItemValue("Personal_Nr", teldoc.Personal_Nr(0))
' Call doc.ReplaceItemValue("Zimmer", teldoc.Zimmernr(0))
Else
Print "Keinen Telefonbucheintrag für " & Key(0) & " gefunden."
End If
End Sub
</code>
