Description
This script will insert tasks from text files and create new tickets.
** Read the instructions (RTFM) before using this script **
I DO NOT RECOMMEND USE THIS WITH SW V 5.1
I’ve been trying and there have been many complications
Source Code
#cs ----------------------------------------------------------------------------
AutoIt Version: 3.3.6.1
Author: Henry Redfield
Aargau3 in Spiceworks Community
Script Function:
What we have here is a new way to communicate
What I have attempted to do with this script is to create 'Task' tickets in SpiceWorks
There is a process for Daily M-Fr, Weekly - Run on Mondays and Monthly which runs on the last weekday of the month (not easy)
All tasks will have a label at the begining to reflect Daily, ect.
Every task is set to a category to reflect Daily Task, ect. for reporting purposes
By inserting tasks this way, any and all ticket rules are bypassed
Please understand exactly what is happening before you use this live
Read the EULA - I am not responsible for any damage, explicite or implied, that may be caused by you using this and I will deny everything
**********************************************************************************************************************
* Do not run this script if you are running a network scan as that can corrupt your DB, possibly- Time accordingly *
**********************************************************************************************************************
I have attempted to document everything, so give me a break
#ce ----------------------------------------------------------------------------
; Includes Here
#include <Constants.au3>
#include <file.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include<date.au3>
; The single or double quotes are specific to where they are used later.
; See the SQL entry area below to figure out what I did
$file1 = "DailyTasks.txt"
$file2 = "WeeklyTasks.txt"
$file3 = "MonthlyTasks.txt"
$filed1 = "Daily - "
$filed2 = "Weekly - "
$filed3 = "Monthly - "
$filea1 = "'Daily Task'"
$filea2 = "'Weekly Task'"
$filea3 = "'Monthly Task'"
$filex = ""
$flbl = ""
$ftyp = ""
;---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#cs ----------------------------------------------------------------------------
Part 1: Daily Tasks
This will process the Daily task list from Monday - Friday
#ce ----------------------------------------------------------------------------
if @WDAY >=2 and @WDAY <=6 Then
call("Func1")
call("Func10")
EndIf
Func Func1()
$filex = $file1
$flbl = $filed1
$ftyp = $filea1
EndFunc
sleep(1500)
;---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#cs ----------------------------------------------------------------------------
Part 2: Weekly Tasks
I'm just going to run this on Mondays to populate for the week
#ce ----------------------------------------------------------------------------
; FYI --- @WDAY - 1=Sunday, 2=Monday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday, 7=Sunday
if @WDAY =2 Then
call("Func2")
call("Func10")
EndIf
Func Func2()
$filex = $file2
$flbl = $filed2
$ftyp = $filea2
EndFunc
sleep(1500)
;---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#cs ----------------------------------------------------------------------------
Part 3: Monthly Tasks
This was the hard one to get right.
Find the last WEEK DAY of the month and run the process
What this does: This checks the current weekday and adds 1 on M-Th and 3 on Fri and gets the calendar date yyyy/mm/dd
#ce ----------------------------------------------------------------------------
$day = _LastWorkDay()
; @WDAY - 1=Sunday - 7=Saturday
Func _LastWorkDay()
Local $Offset
Switch @WDAY
Case 2
$Offset = +0
Case 3
$Offset = +0
Case 4
$Offset = +0
Case 5
$Offset = +0
Case 6
$Offset = +3
; Case Else
; $Offset = +0
EndSwitch
Return _DateAdd("d", $Offset, _NowCalcDate())
EndFunc
#cs ----------------------------------------------------------------------------
Then, This part gets the last calendar day of the month that you are running the process
and formats the date to be compared to the date retrieved above
#ce ----------------------------------------------------------------------------
$i = @MON
$iDays = _DateDaysInMonth( @YEAR,$i )
$day1 = @YEAR&"/"&$i&"/"&String( $iDays )
#cs ----------------------------------------------------------------------------
Now for the fun part,
If today is either the last day of the month (M-TH) or the date calculated is going to be into the next month
then it is the last weekday of the month and you can process monthly tasks, otherwise you don't want to do anything.
This will run the process on either the last weekday of the month or the last calendar day of the month if it falls on a weekday.
#ce ----------------------------------------------------------------------------
if @WDAY >=2 and @WDAY <=6 Then
if $day >= $day1 Then
call("Func3")
call("Func10")
EndIf
EndIf
Func Func3()
$filex = $file3
$flbl = $filed3
$ftyp = $filea3
EndFunc
sleep(1500)
;---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
; Other Functions
Func Func10() ;This does the actual processing of the tickets
;---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
; Let's open the file in read only mode
$file = FileOpen($filex, 0)
; Check if file opened for reading OK
If $file = -1 Then
MsgBox(0, "Error", "Unable to open file.")
Exit
EndIf
; Read in line one to see what date is there
$line = FileReadLine($file,1)
FileClose($file)
;-------------------------------------------------------------------------------
; Let's open the file in edit mode this time
$file = FileOpen($filex, 1)
; Check if file opened for writing OK
If $file = -1 Then
MsgBox(0, "Error", "Unable to open file.")
Exit
EndIf
; Set the date to today in line one of the file
if $line <> @YEAR&"-"&@MON&"-"&@MDAY Then
FileSetPos($file,0,$FILE_BEGIN)
FileWriteLine($file,@YEAR&"-"&@MON&"-"&@MDAY)
Call ("Func11")
Else
FileClose($file)
EndIf
FileClose($file)
EndFunc
;-------------------------------------------------------------------------------
Func Func11()
Dim $aRecords
$file = $filex
If Not _FileReadToArray($file,$aRecords) Then
MsgBox(4096,"Error", " Error reading log to Array error:" & @error)
Exit
EndIf
; Start the SQL stuff here
; Probably the only thing that you will have to change is the database file location in $SqlDb
$SqlDb = "C:\Spiceworks\db\spiceworks_prod.db"
; These are the fields that are being populated
$SqlCmd = "INSERT INTO tickets (summary, status, description, due_date, created_at, created_by, category)"
if @WDAY >=2 and @WDAY <=6 then
; Go into a loop - WOO HOO!
For $x = 10 to $aRecords[0]
; Msgbox(0,'Record:' & $x, $aRecords[$x])
Local $hQuery, $aRow
_SQLite_Startup ()
ConsoleWrite("_SQLite_LibVersion=" &_SQLite_LibVersion() & @CRLF)
_SQLite_Open ($SqlDb)
; This is where the magic happens! These are the values being inserted
; The due date(the first strftime) is set to the day that the ticket was created
_SQLite_Exec (-1, $SqlCmd & "VALUES ('"& $flbl & $aRecords[$x] &"','open','" & $aRecords[$x] &"',strftime('%Y-%m-%d 08:00:00'),strftime('%Y-%m-%d 08:00:00'),452,"&$ftyp&")") ; INSERT Data
$aRecords[$x] = ""
sleep(1500)
Next
_SQLite_Close()
_SQLite_Shutdown()
EndIf
$filex = ""
$flbl = ""
$ftyp = ""
EndFunc
#cs-------------------------------------------------------------------------------------
; If anyone can improve on this, please do and share with the spiceworks community.
#ce-------------------------------------------------------------------------------------
