Skip to content

Instantly share code, notes, and snippets.

View exemplum100's full-sized avatar
🦉

exemplum100

🦉
View GitHub Profile
@exemplum100
exemplum100 / adhoc2.sql
Created December 19, 2024 05:43
Пример использования курсора для поиска аномалий в системе для выявления причин инцидента
CREATE TABLE #rezults
(cobj BIGINT,
times DATETIME,
counts INT)
DECLARE @xg1 DATETIME2,
@xg2 INT,
@xg3 BIGINT,
@mess VARCHAR(300),
@logr DATETIME2
SET @xg2=0
@exemplum100
exemplum100 / geography_point.sql
Created December 19, 2024 05:43
Использование типа GEOGRAPHY для отчета
DECLARE geo_kursor CURSOR LOCAL FOR
SELECT k.pin,k.lat,k.long,k.geo,k.reciv FROM #koors3 AS k
ORDER BY k.reciv
OPEN geo_kursor
FETCH NEXT FROM geo_kursor
INTO @pinfirst,@latg,@longg,@geocurs,@receiv ---LOOK
WHILE @@FETCH_STATUS=0
BEGIN
SET @userloc = geography::Point(@latg, @longg, 4326).STBuffer(@km)
SET @checkgeo1 = (SELECT TOP (1) t.checkgeo FROM #t2 AS t WHERE t.checkpin=@pinfirst ORDER BY t.receivedm DESC )
@exemplum100
exemplum100 / ValFromTableList.sql
Last active December 19, 2024 05:44
Пример запроса sql для сверки значений из списка таблиц
--Cursor Sample, declare for tables list
--Result select val from list of tables, with equal column names--
IF OBJECT_ID('tempdb..#connector1') IS NOT NULL DROP TABLE #connector1
IF OBJECT_ID('tempdb..#resulttable') IS NOT NULL DROP TABLE #resulttable
CREATE TABLE #connector1 (val int)
CREATE TABLE #resulttable (id int, val int)
DECLARE
@exemplum100
exemplum100 / pyOutlookRemind.py
Created December 19, 2024 05:43
Пример использования py с Outlook для напоминания необработанных сообщений, не имеющих статуса обработки
#How it work#
#:Search info in incoming messages and remind about new value, into Outlook from third-system, without last(closed) selfstatus
#:Last selfstatus substitution in closed_list.txt manualy
#:Make txt file with some info, for reminder button
#:After, remind about new messages
#(foxes in comment just for ex)
import os
import time
from datetime import datetime
import win32com.client as win32
@exemplum100
exemplum100 / SendZipByOutlook.py
Created December 19, 2024 05:43
Пример использования py с Outlook (архивирование)
import os
import shutil
import pprint
from datetime import datetime
import win32com.client as win32
#upd stamp
with open("lastupd.txt", "a") as f:
x = datetime.today().strftime('%Y-%m-%d %H:%M:%S')
f.write(x + '\n')
@exemplum100
exemplum100 / adhoc1.sql
Created December 19, 2024 05:43
Примеры сложных запросов (формирование данных под шаблон)
----Фильтрация данных под шаблон
WITH cte as (
SELECT e.CREATED_ON,e.DATE_,e.AXIS_1,'D_1' as AXIS_2,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(AXIS_3,'"',''),'дата продажи',''),'.',''),' ',''),',',''),'АББ','АБВ') as AXIS_3,
e.AXIS_4, 'Имя компании' as AXIS_5, e.AXIS_6, REPLACE(SUBSTRING(AXIS_3,0,CHARINDEX(',',AXIS_3)),'АББ','АБВ') as AXIS_7
FROM etls as e WHERE
e.AXIS_6 != 'TEST' or e.AXIS_6 is NULL
UNION ALL
SELECT e.CREATED_ON,e.DATE_,e.AXIS_1,'D_2' as AXIS_2,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(AXIS_3,'"',''),'дата продажи',''),'.',''),' ',''),',',''),'АББ','АБВ') as AXIS_3,
@exemplum100
exemplum100 / postgist.py
Created December 19, 2024 05:43
Gists update
import requests
import json
GITHUB_API="https://api.github.com"
API_TOKEN = config.token
#
a = []
x=open(config.discr, 'r')
@exemplum100
exemplum100 / generatenames.sql
Created December 19, 2024 05:43
Случайная генерация имен для наполнения БД
USE [sqlnames]
GO
/****** Object: StoredProcedure [dbo].[sp_generatenames] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_generatenames]
@genvalue INT
AS
@exemplum100
exemplum100 / telebot_py.py
Created December 19, 2024 05:43
Пример телеграм бота на Python с разным функционалом
import config
import telebot
import re
import time
import datetime
from threading import Thread
import schedule
from time import sleep
import requests
import random
@exemplum100
exemplum100 / parsexcel.py
Created December 19, 2024 05:43
Пример использования python c excel для парсинга данных
import openpyxl as px
from tkinter import *
from tkinter import filedialog as fd
import os
import re
file_name='C:/Users/Default/Desktop/PyScript/PyScript/отчет.xlsx'
defrow=1
defcolumn=1
sheetnms=''
sheetl=[]