Svc_ComLogNoDB.ps1

<#
REVISÕES

Hilton Junior - Data: 26/06/2017 11:28
Ajustes finais

Hilton Junior - Data: 26/06/2017 17:23
Rotina de conexão com banco acertada para ambiente abaixo de 2.0, uso de credenciais nao funciona nos mesmos.


#>

$scriptPath = "C:\Svc"
$logFile = "$scriptPath\Svc.log"
$iniFile = "$scriptPath\Svc.ini"
$defaultTime = $null
$connectionString = $null
$global_msg = ""
$ver_pwr_shl = $PSVersionTable.PSVersion | Select-Object -ExpandProperty Major

#### CREATELOG ####
#Função checa se arquivo de log existe, caso não existir, cria arquivo, caso existe checa se o tamanho do mesmo é menor que 10MB
#caso seja maior, renomeia o atual e cria novo arquivo.
function createLog {
	try{
		if (Test-Path $logFile){
			$size = Get-ChildItem $logFile | Select -ExpandProperty Length
			$size = [math]::Round(($size/1MB),1)
			if ($size -gt 10){
				$logBkp = (".\Svc-{0:ddMMyyyy}.log" -f (get-date))
				Move-Item $logFile $logBkp
			}
		}
		if (!(Test-Path $logFile)){
			New-Item -Path $logFile -ItemType "File"
		}
	} catch {
		$posMsg = $_.InvocationInfo.PositionMessage
		throw "CREATLOG | $_ | $posMsg"
	}
}

#### ESCREVELOG ####
# Registra as mensagens gravadas na variável $global_msg no arquivo de log.
function escreveLog {
	try {
		$global_msg = "{0:dd/MM/yyyy HH:mm:ss} | $global_msg" -f (Get-Date)
		Add-Content -Path $logFile -Value "$global_msg"
	} catch {
		$posMsg = $_.InvocationInfo.PositionMessage
		throw "ESCREVELOG | $_ | $posMsg"
	}
}

#Utilizada para validar a data de schedule informada na agenda
function Test-DateTimePattern{
	 param(
        [string]$String,
        [string]$Pattern,
        [System.Globalization.CultureInfo]$Culture = (Get-Culture),
        [switch]$PassThru
    )
	
	try {
		$result = try{ [DateTime]::ParseExact($String,$Pattern,$Culture) } catch{}
 
	    if($PassThru -and $result)
	    {
	        $result
	    }
	    else
	    {
	        [bool]$result
	    } 
	} catch {
		$posMsg = $_.InvocationInfo.PositionMessage
		throw "TEST-DATETIMEPATTERN | $_ | $posMsg"
	}
}


#### GETFILEHASH ####
Function GetFileHash([String] $FileName,$HashName = "MD5") 
{
	try {
		$FileStream = New-Object System.IO.FileStream($FileName,[System.IO.FileMode]::Open) 
		$StringBuilder = New-Object System.Text.StringBuilder 
		[System.Security.Cryptography.HashAlgorithm]::Create($HashName).ComputeHash($FileStream)|%{[Void]$StringBuilder.Append($_.ToString("x2"))} 
		$FileStream.Close() 
		$hash = $StringBuilder.ToString()
		return $hash
	} catch {
		$posMsg = $_.InvocationInfo.PositionMessage
		throw "GETFILEHASH | $_ | $posMsg"
	}
	
}

function Get_PlainText()
{
	[CmdletBinding()]
	param
	(
		[parameter(Mandatory = $true)]
		[System.Security.SecureString]$SecureString
	)
	
 
	try
	{
		$bstr = [Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecureString);
		return [Runtime.InteropServices.Marshal]::PtrToStringBSTR($bstr);
	} catch {
		$posMsg = $_.InvocationInfo.PositionMessage
		throw "GET_PLAINTEXT | $_ | $posMsg"
	}
	finally
	{
		[Runtime.InteropServices.Marshal]::FreeBSTR($bstr);
	}	
}

function Decrypt_String($encryptedString) {
   	try{ 
		$pass = $encryptedString
		$passSize = $pass.Length
		$div = [math]::Round($passSize/3)
		$passStr2 = $pass.Substring(($passSize - $div),$div)
		$passStr13 = $pass.Replace("$passStr2","")
		$passStr1 = $passStr13.Substring(0,$div)
		$passStr3 = $passStr13.Replace("$passStr1","")
		$pass2 = $passStr1 + $passStr2 + $passStr3
		$pass = ConvertTo-SecureString $pass2
		
		return $pass
	} catch {
		$posMsg = $_.InvocationInfo.PositionMessage
		throw "DECRYPT_STRING | $_ | $posMsg"
	}
	
}

#### CONNECTDB ####
function connectDb {
	$connStr = ""
	$UID = ""
	$pass_enc = ""
	try{
			$countParam = $connectionString.Split(";").GetUpperBound(0)

			for ($iiii = 0; $iiii -le $countParam; $iiii++){
				$param = $connectionString.Split(";")[$iiii]
				
				if ($param -match "pwd"){
					$pass = ($param.Split("=")[1]).Trim()
					$pass_enc = (Decrypt_String -encryptedString $pass)
					if ($ver_pwr_shl -gt 2){						
	 					$pass_enc.MakeReadOnly()
					} else {
						$pass_dec = Get_PlainText -SecureString $pass_enc
						$connStr = $connStr + "pwd = $pass_dec" + ";"	
					}				
				}	
				
				if (($param -match "server") -or ($param -match "database")){
					$connStr = $connStr + $param + ";"
				}	
				
				if ($param -match "uid"){
					if ($ver_pwr_shl -gt 2){
						$UID = ($param.Split("=")[1]).Trim()
					} else {
						$connStr = $connStr + $param + ";"	
					}				
				}	
			}
			
			$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
			$SqlConnection.ConnectionString = $connStr
			
			if ($ver_pwr_shl -gt 2){
				$creds = New-Object System.Data.SqlClient.SqlCredential($UID,$pass_enc)
				$SqlConnection.Credential = $creds
			} 
			
		return $SqlConnection
	} catch {
		$posMsg = $_.InvocationInfo.PositionMessage
		throw "CONNECTDB | $_ | $posMsg"
	}
}

#### SELECTDB ####
#Efetua comunicação com a base de dados retornandos os dados referentes as rotinas que estão em processo de execução
function selectDb {

	param(
	        [string]$query
	)
	
	try{
		$SqlConnection = connectDb
		$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
		$SqlCmd.Connection = $SqlConnection
		$SqlCmd.CommandText = $query
		$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
		$SqlAdapter.SelectCommand = $SqlCmd
		$DataSet = New-Object System.Data.DataSet
		$SqlAdapter.Fill($DataSet) | Out-Null
				
		return ($DataSet)
	}catch{
		$posMsg = $_.InvocationInfo.PositionMessage
		throw "SELECTDB | $_ | $posMsg"
	} 
}

#### INSERTDB ####
function insertDb {
	param(
	        [string]$alias
	)
	
	try {
		
		$SqlConnection = connectDb
		$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
		$SqlCmd.CommandText = "INSERT INTO SVCHIST (Alias, TimeRun, Maquina) VALUES(@alias,@date,@machine)"
		$SqlCmd.Connection = $SqlConnection
		$data = "{0:yyyy-MM-dd HH:mm:ss.fff}" -f (Get-Date)
		$SqlCmd.Parameters.AddWithValue("@alias", $alias) | Out-Null
		$SqlCmd.Parameters.AddWithValue("@date", $data) | Out-Null
		$SqlCmd.Parameters.AddWithValue("@machine", $env:computername) | Out-Null 
		$SqlCmd.Connection.Open()
		$SqlCmd.ExecuteNonQuery() | Out-Null
		
	} catch {
		$posMsg = $_.InvocationInfo.PositionMessage
		throw "INSERTDB | $_ | $posMsg"
	} 
}

#### CHECK_SCHEDULE ####
function check_schedule {
	param(
	    [string]$schedule,
		[string]$alias
	)
	try{
		$sch = 0
		$tmpFile = "$scriptPath\$alias.tmp"
		if ($schedule -ne ""){
			$days = ""
			$countSchedules = $schedule.Split("|").GetUpperBound(0)

			for ($ii = 0; $ii -le $countSchedules; $ii++){
				$datasche = $schedule.Split("|")[$ii]
				$checkMask = $datasche.Split(",").GetUpperBound(0)
				if ($checkMask -eq 0){
					throw "Formato de schedule invalido! Padrao: <dia da semana>,<hora de execucao>"
				}
				$dayWeek = $datasche.Split(",")[0]
				$hour = $datasche.Split(",")[1]
				
				#Teste do dia da semana do schedule
				if ($dayWeek -ne "*"){
					$countDayWeek = $dayWeek.Split("-").GetUpperBound(0)
					if ($countDayWeek -gt 0){
						$diaIni = [Int] [DayOfWeek] $dayWeek.Split("-")[0]
						$diaFim = [Int] [DayOfWeek] $dayWeek.Split("-")[1]
						
						if ($diaIni -eq $diaFim){
							throw "Dia inicial igual ao final, favor utilizar * para todos os dias, ou inserir um intervalo valido"
						}
						
						if ($diaIni -gt $diaFim){
							for ($iii = $diaIni; $iii -le 6; $iii++){
								$days += " " + [String] [DayOfWeek] $iii + " "
							}
							for ($iii = 0; $iii -le $diaFim; $iii++){
								$days += " " + [String] [DayOfWeek] $iii + " "
							}
							
							$days
						} else {
							for ($iii = $diaIni; $iii -le $diaFim; $iii++){
								$days += " " + [String] [DayOfWeek] $iii + " "
							}
						}						
					} else {
						$days = [DayOfWeek] $dayWeek
					}
				} else {
					if ($dayWeek -eq "*"){
						$days = " Sunday Monday Tuesday Wednesday Thursday Friday Saturday "
					}
				}
				$diaAtual = [String] (Get-Date).DayOfWeek
				if ($days -match $diaAtual){
					#Teste da hora do shedule
					$countHours = $hour.Split(":").GetUpperBound(0)
					
					if ($countHours -eq 0){
						#Caso execução seja em intervalos regulares
						if ($hour -gt 15){
							if (Test-Path $tmpFile){
								$horaLastExec = Get-Content $tmpFile
								if (Test-DateTimePattern -String $horaLastExec -Pattern "dd/MM/yyyy HH:mm"){
									$horaAtual = "{0:dd/MM/yyyy HH:mm}" -f (Get-Date)
									if (($horaAtual -ge ((get-date $horaLastExec).AddMinutes($hour).ToString("dd/MM/yyyy HH:mm")))){
										$sch = 1
										$data = "{0:dd/MM/yyyy HH:mm}" -f (Get-Date)
										Set-Content $tmpFile -Value $data
										break
									}
								} else {
									throw "Formato de data/hora incorreto em: $tmpFile"
								}
							} else {
								$sch = 1
								New-Item -Path $tmpFile -ItemType "File"
								$data = "{0:dd/MM/yyyy HH:mm}" -f (Get-Date)
								Set-Content $tmpFile -Value $data
								break
							}
						} else {
							throw "Tempo para execucao minimo deve ser maior que 15 minutos!"
						}		
					} else {
						#Caso execução seja em horário fixo
						if (Test-DateTimePattern -String $hour -Pattern t){
							$horaAtual = Get-Date -Format HH:mm
							if (($horaAtual -ge $hour) -and ($horaAtual -lt ((get-date $hour).AddSeconds($defaultTime).ToString("HH:mm")))){
								$sch = 1
								break
							}
						} else {
							throw "Formato de hora invalido: $hour"
						}
					}					
				}				
			}	
		} else {
			throw "Schedule em branco, favor verificar"
		}
			
		return $sch

	} catch {
		$posMsg = $_.InvocationInfo.PositionMessage
		throw "CHECK_SCHEDULE | $_ | $posMsg"
	}	
}

#### EXEC_APP ####
function exec_app {
	param(
	    $selecteds
	)
	try {
		
		foreach ($Row in $selecteds.Tables[0].Rows) { 
			if (Test-Path $Row.Path){
				$hashFisico = GetFileHash -Filename $Row.Path
				if ($hashFisico -eq $Row.Hash){
					$app = $Row.Path
					$param = $Row.Parameters
					& "$app" $param		
					insertDb -alias $Row.Alias
					$global_msg = "EXEC_APP | APP INICIADO | " + $Row.Alias
					escreveLog
				} else {
					$global_msg = "EXEC_APP | Hash fisico diferente do original! Alias: " + $Row.Alias
					escreveLog
				}
			} else {
				$global_msg = "EXEC_APP | Executável não encontrado! Alias: " + $Row.Alias
				escreveLog
			}				
		}
		
		
	} catch {
		$posMsg = $_.InvocationInfo.PositionMessage
		throw "EXEC_APP | $_ | $posMsg"
	}
	
}

try {
	createLog
	
	#### INIT ####
	$global_msg = "Servico iniciado"
	escreveLog
	
	if (Test-Path $iniFile){
		foreach ($line in get-content $iniFile){
			$line = $line.Trim()
			if (!($line.StartsWith("#")) -and ($line.trim() -ne "")){
				if ($line.StartsWith("[Time]")){
					try{
						$defaultTime = [int] ($line.Split("]")[1]).trim()
					} catch {
						$erro = $_.FullyQualifiedErrorId
						if ($erro -eq "InvalidCastFromStringToInteger"){
							throw "INIT | Valor invalido! Parametro [Time] do ini não corresponde a um inteiro valido"
						} else {
							throw "$_"
						}					
					}						
				}	
				if ($line.StartsWith("[ConnectDB]")){
					$connectionString = ($line.Split("]")[1]).trim()					
				}								
			}			
		}
	} else {
		throw "INIT | Arquivo de configuração $iniFile inexistente!"	
	}
		
	if (($defaultTime -eq "") -or ($defaultTime -eq $null)){
		throw "INIT | Valor invalido! Parametro [Time] do ini esta vazio ou nulo"
	}
	if ($defaultTime -lt 60){
		throw "INIT | Valor invalido! Parametro [Time] do ini deve ser maior que 60 segundos"
	}
	if (($connectionString -eq "") -or ($connectionString -eq $null)){
		throw "INIT | Valor invalido! Parametro [ConnectDB] do ini esta vazio ou nulo"
	}
	
	$global_msg = "INIT | Buscando rotinas de inicializacao no BD"
	escreveLog
	
	try {
		#Buscar na base rotinas que estão marcadas para execução no start
		$querySelect = "SELECT Alias,Hash,Path,Parameters FROM VICSVC WHERE InitRun = 1"
		$listSelecteds = selectDb -query $querySelect
	
		exec_app -selecteds $listSelecteds
	} catch {
		$posMsg = $_.InvocationInfo.PositionMessage
		throw "INIT | $_ | $posMsg"
	}
	
	
	##### MAIN #####

	$run = 1
	while ($run -eq 1){
		try{

			createLog
			
			$global_msg = "MAIN | Ciclo iniciado"
			escreveLog
			
			#Checar no arquivo se existe alguma rotina para exeucao no horário corrente
			if (Test-Path $iniFile){
			
				#Array com os Alias contidos no arquivo VicSvc.ini
				$AliasList = New-Object System.Collections.ArrayList
				#Array com os Schedules contidos no arquivo VicSvc.ini
				$SchList = New-Object System.Collections.ArrayList
				
				#Efetuando a carga dos Arrays com os dados obtidos no arquivo.
				$global_msg = "MAIN | Lendo arquivo com lista de apps"
				escreveLog
				
				foreach ($line in get-content $iniFile){
					$line = $line.Trim()
					if (!($line.StartsWith("#")) -and ($line.trim() -ne "") -and !($line.StartsWith("["))){
						$count_ = $line.Split(";").GetUpperBound(0)
						if ($count_ -eq 1){
							$AliasList.Add($line.Split(";")[0]) > $null
							$SchList.Add($line.Split(";")[1]) > $null
						} else {
							$global_msg = "MAIN | Formato da linha inválido em $iniFile, linha: $line"
							escreveLog
						}
					}
				}
				#Validando Schedule e populando alias para uso no SELECT	
				$global_msg = "MAIN | Validando schedules configurados por app"
				escreveLog		
				
				$alias = ""
				$teste_sch = 0
				$count = 0
				for ($i=0; $i -lt $SchList.Count; $i++) {
					$teste_sch = check_schedule -schedule $SchList.Item($i) -alias $AliasList.Item($i)
					if ($teste_sch -eq 1){
						if ($count -eq 0){
							$alias += "'" + $AliasList.Item($i) + "'"
						} else {
							$alias += ",'" + $AliasList.Item($i) + "'"
						}
						
						$count = $count + 1
					}
				}
				
				if ($alias -ne ""){
					#Obtendo HASH das rotinas com schedule válido
					$global_msg = "MAIN | Selecionando apps com schedule valido no BD"
					escreveLog		
				
					$querySelect = "SELECT Alias,Hash,Path,Parameters FROM SVC WHERE AlIAS in ({0})" -f $alias
					$listSelecteds = selectDb -query $querySelect
					
					try {
						exec_app -selecteds $listSelecteds
					} catch {
						$global_msg = "MAIN | $_"
						escreveLog	
					}
					
				}			
			} else {
				throw "MAIN | Arquivo de configuração $iniFile inexistente!"
			}
			
			
			#Fim (pausa ate proximo ciclo)
			$global_msg = "MAIN | Ciclo finalizado"
			escreveLog
			
			Start-Sleep -s $defaultTime
		} catch {
			$posMsg = $_.InvocationInfo.PositionMessage
			$global_msg = "MAIN | $_ | $posMsg"
			escreveLog
			exit 1
			break
		} 
	}
	exit 0
} catch {
	$posMsg = $_.InvocationInfo.PositionMessage
	$global_msg = "$_ | $posMsg"
	escreveLog
	exit 1
	break
} Finally {
	$global_msg = "Servico finalizado"
	escreveLog
}