hackimov’s gists

hackimov / INJ.SQL

Created December 4, 2021 15:41

INJ.SQL

hackimov / transactions.sql

Last active June 28, 2021 04:11

migration to new view transactions

	BEGIN TRANSACTION;
	alter table transactions
	add tr_document_password_owner text,
	add tr_doctype_id guid,
	add tr_document_date date,
	add tr_document_number text,
	add tr_document_description text,
	add tr_document_owner_name text,
	add tr_document_owner_user_id guid,
	add tr_document_owner_structure_id guid,

hackimov / all_doc_type_counters.sql

Created May 17, 2021 01:34

FUNCTION RETURNS COUNT OF ALL REQUIRING REACTION DOCUMENTS BY DOCTYPE ID

	create function all_doc_type_counters(mailbox_id_in text, profile_id_in text, structure_id_in text) returns json
	language plpgsql
	as
	$$
	DECLARE owner_reaction_doctype_id text[];
	DECLARE partner_reaction_doctype_id text[];
	DECLARE departments text;
	DECLARE result_json json;
	DECLARE visibility_zone integer;

hackimov / doc_counters.sql

Created May 7, 2021 08:58

doc counters function

	create or replace function doc_counters(
	mailbox_id_in text,
	profile_id_in text,
	structure_id_in text
	)
	RETURNS json
	AS
	$$
	DECLARE owner_reaction integer;
	DECLARE partner_reaction integer;

hackimov / UPDATE_DOCUMENT_TOTAL_SUM.SQL

Created November 20, 2020 12:28

DOCUMENT TOTAL SUM UPDATE SQL

	UPDATE documents set document_total_sum = tmp_sel.summa FROM
	(SELECT documents.document_id, cast(sum(vat_total_sum) as numeric(20,2)) as summa from documents LEFT JOIN value_params vp on documents.document_id = vp.document_id
	LEFT JOIN goods g on vp.value_param_id = g.value_param_id
	WHERE param_id = '78624016-1195-4301-8576-cfabead6c9c9' group by documents.document_id) as tmp_sel
	WHERE documents.document_id = tmp_sel.document_id;

hackimov / Move_signatures.sql

Last active July 26, 2020 13:43

SQL TRANSACTION TO MOVE SIGNATURES TO SIGNATURE TABLE

	BEGIN TRANSACTION;
	INSERT INTO signatures (document_id, signature_pkcs7, signature_data, signature_role)
	(
	SELECT document_id, document_owner_pkcs7 as sign, document_sign_from_json as json, 'owner' as role FROM documents where document_owner_pkcs7 IS NOT NULL
	UNION ALL
	SELECT document_id, document_contragent_pkcs7 as sign, document_sign_from_json as json, 'contragent' as role FROM documents where document_contragent_pkcs7 IS NOT NULL
	UNION ALL
	SELECT document_id, document_confidant_pkcs7 as sign, document_confidant_sign_json as json, 'confidant' as role FROM documents where document_confidant_pkcs7 IS NOT NULL
	);

hackimov / select.sql

Created July 8, 2020 06:02

select from database

	SELECT CAST(document_data::json->>'EmpowermentId' AS char(24)) as internal_id, document_id from documents where
	doctype_id = '945dde7f-b55e-4666-990c-749751a3c726'
	and document_id <> CAST(document_data::json->>'EmpowermentId' AS char(24))
	and CAST(document_data::json->>'EmpowermentId' AS char(24)) <> ' '
	and CAST(document_data::json->>'EmpowermentId' AS char(24)) IS NOT NULL;

hackimov / upd.sql

Created July 8, 2020 05:59

update sql

	START TRANSACTION;
	-- дропаем рестикты
	ALTER TABLE transactions DROP CONSTRAINT fk_transact_reference_document;
	ALTER TABLE value_params DROP CONSTRAINT fk_value_pa_reference_document;

	-- создаём каскадный update для value params
	ALTER TABLE value_params ADD CONSTRAINT fk_value_pa_reference_document_upd
	FOREIGN KEY (document_id) REFERENCES documents(document_id) ON UPDATE CASCADE;

	-- создаём каскадный update для transactions

hackimov / CHILDS_SEARCH.SQL

Last active May 22, 2020 13:46

SQL RECURSIVE SEARCH CHILDS BY PARENT ID POSTGRESQL

	WITH RECURSIVE catalogs_tree AS
	(
	SELECT
	catalog_id,
	1 AS hierarchy_level
	FROM
	catalog
	WHERE
	catalog_id = '".$catalog_id."'
	AND mailbox_id = '".$session['mailbox_id']."'

hackimov / AutoDeployController.php

Created March 11, 2020 05:43

AutoDeploy и приём информации серверами , путем вызова вебхуков сервером GitHub

	<?php /** @noinspection PhpUndefinedMethodInspection PhpUnused */

	/**
	* НЕ ИЗМЕНЯТЬ!!!!! (жене мужу и т.д., а контроллер можно но в исключительных случаях)
	* Автор класса Александр Хакимов https://github.com/hackimov
	* Контроллер который занимается автодеплоем на стэйджинг и продакшин.
	* Это функционал ядра который восприимчив к изменению!
	* Если затронуть код то автодеплой сломается, исключением является если он уже сломался по причине если сервера были перенесены или недоступны из вне!
	*/

Александр hackimov