nikolasd’s gists

nikolasd / documents_w_transactions_wo_accounting.sql

Last active October 19, 2024 15:53

Παραστατικά που έχουν κινηθεί και δεν έχουν γέφυρα λογιστικής

	SELECT
	ser.heCode AS DOCSERIES,
	ser.heShortcut AS DOCSERIESSHORTCUT,
	ser.heName AS DOCSERIESNAME,
	typ.heCode AS DOCTYPE,
	typ.heName AS DOCTYPENAME,
	acc.heCode AS ACCTMP,
	acc.heName AS ACCTPMNAME,
	cmp.heName AS CMP,
	J.PLETHOS AS PLETHOS

nikolasd / transcations_without_accounting.sql

Last active October 19, 2024 15:53

Ποιά παραστατικά δεν έχουν ενημερώσει τη λογιστική, είτε έχουν ή δεν έχουν γέφυρα;

	SELECT
	heDocEntries.heDocCode Doc,
	heDocEntries.heofficialdate DocDate,
	CASE heDocEntries.heAccUpd
	WHEN ';1;' THEN 'Yes'
	ELSE 'No'
	END Accounting,
	heCompanies.heName Company,
	heDocSeries.heCode Code,
	hedocseries.hename Description,

nikolasd / find_accounting_template_by_docseries.sql

Last active March 8, 2019 12:17

Pylon SQL - Εύρεση όλων των εισπρακτικών παραστατικών που χρησιμοποιούν συγκεκριμένη γέφυρα

	SELECT
	ser.heCode AS DOCSERIES,
	ser.heShortcut AS DOCSERIESSHORTCUT,
	ser.heName AS DOCSERIESNAME,
	typ.heCode AS DOCTYPE,
	typ.heName AS DOCTYPENAME,
	acc.heCode AS ACCTMP,
	acc.heName AS ACCTPMNAME,
	cmp.heName AS CMP
	FROM heDocSeries AS ser

nikolasd / create_user_update_user_of_document.sql

Last active October 19, 2024 15:54

Ποιός είναι ο χρήστης δημιουργίας και αλλαγής ενός παραστατικού.

	SELECT
	heDocEntries.HEDOCCODE AS Document,
	crUser.POUSERNAME AS CreateUser,
	updUsers.POUSERNAME AS UpdateUser
	FROM heDocEntries
	LEFT JOIN heUsers crHUsers
	ON heDocEntries.HECREATIONUSRSID = crHUsers.HEUSERID
	LEFT JOIN poUsers crUser
	ON crHUsers.HEUSERID = crUser.POID
	LEFT JOIN heUsers updHUsers

nikolasd / MaintenanceSolution.sql

Last active October 19, 2024 15:54

SQL Server Backup, Integrity Check, and Index and Statistics Maintenance The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005,

	/*

	SQL Server Maintenance Solution - SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, and SQL Server 2017

	Backup: https://ola.hallengren.com/sql-server-backup.html
	Integrity Check: https://ola.hallengren.com/sql-server-integrity-check.html
	Index and Statistics Maintenance: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

	License: https://ola.hallengren.com/license.html

nikolasd / rename_pylon_series.sql

Last active October 19, 2024 15:55

Some SQL scripts used to rename Pylon Series in order to copy them. Because Pylon, does not allows a shortcut bigger that 10 chars, because... Pylon!

	/*
	1. Backup HeDocSeries
	*/
	INSERT INTO hedocseries_bak
	SELECT
	*
	FROM hedocseries

	/*
	*

nikolasd / update-reference-number

Last active October 23, 2018 12:57

Όταν μας βγάζει σφάλμα ότι δεν μπορεί να αποθηκευθεί μία εγγραφή, γιατί η τιμή υπάρχει είδη και αναφέρετε στο Unique Index του ReferenceNumber.

	-- Update sequence current value with max reference number
	-- example is for HEITEMS sequence
	DECLARE @refno int = (SELECT
	CAST(MAX(HEITEMS.herefnumber) AS int)
	FROM HEITEMS)
	DECLARE @inc int = (SELECT
	CAST(current_value AS int)
	FROM sys.sequences
	WHERE name LIKE 'HEITEMS%')

nikolasd / search_string.sql

Last active October 19, 2024 15:55

Searches a specific string inside all tables of a DB

	DECLARE @SearchStr nvarchar(100)
	SET @SearchStr = '48a95b8a-5636-e811-95a5-30e37a693c68'

	CREATE TABLE #Results (
	ColumnName nvarchar(370),
	ColumnValue nvarchar(3630)
	)

	SET NOCOUNT ON

nikolasd / multiple_dbs_backup

Last active October 23, 2018 12:19

Backup multiple DBs at once. Exclude specific DBs

	-- Check DBs
	/*
	DECLARE @pylonVer varchar(20) -- current pylon ver

	-- specify current pylon version
	SET @pylonVer = '170432'

	SELECT
	name
	FROM master.dbo.sysdatabases

nikolasd / export_to_json_and_upload_to_ftp.ps1

Last active October 19, 2024 15:56

Pylon PowerShell - Export SQL entries to CSV, convert to JSON and upload though FTP

	#Requires -Version 4
	$root = "C:\infomind"
	$sql = invoke-sqlcmd -Username sa -Password db_password -Database pylonchp -ServerInstance "SERVER\INSTANCE" -InputFile "$($root)\scripts\m-hospitality.sql" \| Export-csv -Path "$($root)\file.csv" -Force -Encoding "UTF8" -NoTypeInformation
	#$json = convertto-json -inputobject $sql
	#$null = new-item -type file -force "$($root)\m-hospitality.json" -value $json
	#$null = convertto-json -inputobject $sql \| Out-File "$($root)\file.json"
	#$csv = Export-csv -InputObject $sql -Path "$($root)\file.csv" -Force -Encoding "UTF8"
	# create the FtpWebRequest and configure it
	$ftp = [System.Net.FtpWebRequest]::Create("ftp://ftp.infomind.gr/chp.csv";;)
	$ftp = [System.Net.FtpWebRequest]$ftp

Nikolas Demiridis nikolasd