Gists
On gists
Protecting
Heidi pwd decoding
heidi.js #
function heidiDecode(hex) {
var str = '';
var shift = parseInt(hex.substr(-1));
hex = hex.substr(0, hex.length - 1);
for (var i = 0; i < hex.length; i += 2)
str += String.fromCharCode(parseInt(hex.substr(i, 2), 16) - shift);
return str;
}
heidiDecode('pwd ...')
On gists
Decodes a password from HeidiSQL. HeidiSQL passwords can be found in the registry. Use File -> Export Settings to dump all settings. Great f
•
DOC
HeidiDecode.js #
function heidiDecode(hex) {
var str = '';
var shift = parseInt(hex.substr(-1));
hex = hex.substr(0, hex.length - 1);
for (var i = 0; i < hex.length; i += 2)
str += String.fromCharCode(parseInt(hex.substr(i, 2), 16) - shift);
return str;
}
document.write(heidiDecode('755A5A585C3D8141786B3C385E3A393'));
On gists
MySql
MySql - advanced
LEFT JOIN -> JOIN ON, WHERE
join.sql #
/*
https://www.codeproject.com/Articles/231132/Difference-between-And-clause-along-with-on-and-Wh
*/
+--------+---------------+
| DeptId | DeptName |
+--------+---------------+
| 1 | HR |
| 2 | Payroll |
| 3 | Admin |
| 4 | Marketing |
| 5 | HR & Accounts |
+--------+---------------+
+-------+----------+--------+-----------+
| EmpId | EmpName | DeptId | EmpSalary |
+-------+----------+--------+-----------+
| 1 | John | 1 | 5000.00 |
| 2 | Albert | 1 | 4500.00 |
| 3 | Crain | 2 | 6000.00 |
| 4 | Micheal | 2 | 5000.00 |
| 5 | David | NULL | 34.00 |
| 6 | Kelly | NULL | 457.00 |
| 7 | Rudy | 1 | 879.00 |
| 8 | Smith | 2 | 7878.00 |
| 9 | Karsen | 5 | 878.00 |
| 10 | Stringer | 5 | 345.00 |
| 11 | Cheryl | NULL | 0.00 |
+-------+----------+--------+-----------+
-- 1
select * from employees e
left join departments d on e.deptid = d.deptid;
+-------+----------+--------+-----------+--------+---------------+
| EmpId | EmpName | DeptId | EmpSalary | DeptId | DeptName |
+-------+----------+--------+-----------+--------+---------------+
| 1 | John | 1 | 5000.00 | 1 | HR |
| 2 | Albert | 1 | 4500.00 | 1 | HR |
| 3 | Crain | 2 | 6000.00 | 2 | Payroll |
| 4 | Micheal | 2 | 5000.00 | 2 | Payroll |
| 5 | David | NULL | 34.00 | NULL | NULL |
| 6 | Kelly | NULL | 457.00 | NULL | NULL |
| 7 | Rudy | 1 | 879.00 | 1 | HR |
| 8 | Smith | 2 | 7878.00 | 2 | Payroll |
| 9 | Karsen | 5 | 878.00 | 5 | HR & Accounts |
| 10 | Stringer | 5 | 345.00 | 5 | HR & Accounts |
| 11 | Cheryl | NULL | 0.00 | NULL | NULL |
+-------+----------+--------+-----------+--------+---------------+
-- 2
select * from employees e
left join departments d on e.deptid = d.deptid
and ( d.deptname = 'HR' or d.deptname = 'HR & Accounts');
+-------+----------+--------+-----------+--------+---------------+
| EmpId | EmpName | DeptId | EmpSalary | DeptId | DeptName |
+-------+----------+--------+-----------+--------+---------------+
| 1 | John | 1 | 5000.00 | 1 | HR |
| 2 | Albert | 1 | 4500.00 | 1 | HR |
| 3 | Crain | 2 | 6000.00 | NULL | NULL |
| 4 | Micheal | 2 | 5000.00 | NULL | NULL |
| 5 | David | NULL | 34.00 | NULL | NULL |
| 6 | Kelly | NULL | 457.00 | NULL | NULL |
| 7 | Rudy | 1 | 879.00 | 1 | HR |
| 8 | Smith | 2 | 7878.00 | NULL | NULL |
| 9 | Karsen | 5 | 878.00 | 5 | HR & Accounts |
| 10 | Stringer | 5 | 345.00 | 5 | HR & Accounts |
| 11 | Cheryl | NULL | 0.00 | NULL | NULL |
+-------+----------+--------+-----------+--------+---------------+
-- 3
select * from employees e
left join departments d on e.deptid = d.deptid
where ( d.deptname = 'HR' or d.deptname = 'HR & Accounts');
+-------+----------+--------+-----------+--------+---------------+
| EmpId | EmpName | DeptId | EmpSalary | DeptId | DeptName |
+-------+----------+--------+-----------+--------+---------------+
| 1 | John | 1 | 5000.00 | 1 | HR |
| 2 | Albert | 1 | 4500.00 | 1 | HR |
| 7 | Rudy | 1 | 879.00 | 1 | HR |
| 9 | Karsen | 5 | 878.00 | 5 | HR & Accounts |
| 10 | Stringer | 5 | 345.00 | 5 | HR & Accounts |
+-------+----------+--------+-----------+--------+---------------+
-- to same ale z druheho pohledu, slo by resit pres RIGHT JOIN
-- 1
select * from departments d
left join employees e on e.deptId = d.deptId;
+--------+---------------+-------+----------+--------+-----------+
| DeptId | DeptName | EmpId | EmpName | DeptId | EmpSalary |
+--------+---------------+-------+----------+--------+-----------+
| 1 | HR | 1 | John | 1 | 5000.00 |
| 1 | HR | 2 | Albert | 1 | 4500.00 |
| 1 | HR | 7 | Rudy | 1 | 879.00 |
| 2 | Payroll | 3 | Crain | 2 | 6000.00 |
| 2 | Payroll | 4 | Micheal | 2 | 5000.00 |
| 2 | Payroll | 8 | Smith | 2 | 7878.00 |
| 3 | Admin | NULL | NULL | NULL | NULL |
| 4 | Marketing | NULL | NULL | NULL | NULL |
| 5 | HR & Accounts | 9 | Karsen | 5 | 878.00 |
| 5 | HR & Accounts | 10 | Stringer | 5 | 345.00 |
+--------+---------------+-------+----------+--------+-----------+
-- 2
select * from departments d
left join employees e on e.deptId = d.deptId and
( d.deptname = 'HR' or d.deptname = 'HR & Accounts');
+--------+---------------+-------+----------+--------+-----------+
| DeptId | DeptName | EmpId | EmpName | DeptId | EmpSalary |
+--------+---------------+-------+----------+--------+-----------+
| 1 | HR | 1 | John | 1 | 5000.00 |
| 1 | HR | 2 | Albert | 1 | 4500.00 |
| 1 | HR | 7 | Rudy | 1 | 879.00 |
| 2 | Payroll | NULL | NULL | NULL | NULL |
| 3 | Admin | NULL | NULL | NULL | NULL |
| 4 | Marketing | NULL | NULL | NULL | NULL |
| 5 | HR & Accounts | 9 | Karsen | 5 | 878.00 |
| 5 | HR & Accounts | 10 | Stringer | 5 | 345.00 |
+--------+---------------+-------+----------+--------+-----------+
-- 3
SELECT * from departments d
left join employees e on e.deptId = d.deptId
where ( d.deptname = 'HR' or d.deptname = 'HR & Accounts');
+--------+---------------+-------+----------+--------+-----------+
| DeptId | DeptName | EmpId | EmpName | DeptId | EmpSalary |
+--------+---------------+-------+----------+--------+-----------+
| 1 | HR | 1 | John | 1 | 5000.00 |
| 1 | HR | 2 | Albert | 1 | 4500.00 |
| 1 | HR | 7 | Rudy | 1 | 879.00 |
| 5 | HR & Accounts | 9 | Karsen | 5 | 878.00 |
| 5 | HR & Accounts | 10 | Stringer | 5 | 345.00 |
+--------+---------------+-------+----------+--------+-----------+
On gists
Pivot table
•
MySql - advanced
pivot.sql #
-- static
SELECT
product_id,
SUM(CASE WHEN month = 'January' THEN sales_amount ELSE 0 END) AS January,
SUM(CASE WHEN month = 'February' THEN sales_amount ELSE 0 END) AS February,
SUM(CASE WHEN month = 'March' THEN sales_amount ELSE 0 END) AS March
FROM sales
GROUP BY product_id;
-- dynamic
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(CASE WHEN month = ''',
month,
''' THEN sales_amount ELSE 0 END) AS ',
CONCAT('`', month, '`')
)
) INTO @sql
FROM sales;
SET @sql = CONCAT('SELECT product_id, ', @sql, ' FROM sales GROUP BY product_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- another table
SELECT
`Sales Date`,
MAX(CASE WHEN `Product Name` = 'Product AthanEN `Sales Amount` END) AS `Product A`,
MAX(CASE WHEN `Product Name` = 'Product B' then `Sales Amount` END) AS `Product B`,
MAX(CASE WHEN `Product Name` = 'Product C' than `Sales Amount` END) AS `Product C`
FROM
`sales_data`
GROUP BY
`Sales Date`;
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('MAX(CASE WHEN `Product Name` = ''', `Product Name`, ''' THEN `Sales Amount` END) AS `', `Product Name`, '`')
) INTO @sql
FROM
`sales_data`;
SET @sql = CONCAT('SELECT `Sales Date`, ', @sql, ' FROM `sales_data` GROUP BY `Sales Date`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
/* month-year invoices */
select rok,
sum(case when mesic=1 then castka else 0 end) as 'leden',
sum(case when mesic=2 then castka else 0 end) as 'únor',
sum(case when mesic=3 then castka else 0 end) as 'březen',
sum(case when mesic=4 then castka else 0 end) as 'duben',
sum(case when mesic=5 then castka else 0 end) as 'květen',
sum(case when mesic=6 then castka else 0 end) as 'červen',
sum(case when mesic=7 then castka else 0 end) as 'červenec',
sum(case when mesic=8 then castka else 0 end) as 'srpen',
sum(case when mesic=9 then castka else 0 end) as 'září',
sum(case when mesic=10 then castka else 0 end) as 'říjen',
sum(case when mesic=11 then castka else 0 end) as 'listopad',
sum(case when mesic=12 then castka else 0 end) as 'prosinec'
from
(
select year(datum) as rok, month(datum) as mesic, sum(castka) as castka
from faktury group by year(datum), month(datum)
) as soucty
group by rok
On gists
MySql - advanced
window fn - standings rank with previous rank
rank.sql #
CREATE TABLE `standing` (
`team` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
`pts` INT(11) NULL DEFAULT NULL,
`season` INT(11) NULL DEFAULT NULL
)
COLLATE='utf8mb4_general_ci'
ENGINE=MyISAM
;
SELECT
team,
season,
pts,
RANK() OVER (PARTITION BY season ORDER BY pts DESC) AS rnk,
LAG(rank_prev_season) OVER (PARTITION BY team ORDER BY season) AS prev_rnk
FROM
(
SELECT
team,
season,
pts,
RANK() OVER (PARTITION BY season ORDER BY pts DESC) AS rank_prev_season
FROM
standing
) AS subquery
ORDER BY
season, rnk;
+--------+--------+------+-----+----------+
| team | season | pts | rnk | prev_rnk |
+--------+--------+------+-----+----------+
| Plzen | 2021 | 60 | 1 | NULL |
| Sparta | 2021 | 55 | 2 | NULL |
| Slavia | 2021 | 40 | 3 | NULL |
| Sparta | 2022 | 54 | 1 | 2 |
| Slavia | 2022 | 50 | 2 | 3 |
| Plzen | 2022 | 48 | 3 | 1 |
| Slavia | 2023 | 55 | 1 | 2 |
| Plzen | 2023 | 49 | 2 | 3 |
| Sparta | 2023 | 38 | 3 | 1 |
+--------+--------+------+-----+----------+
On gists
MySql
MySql tricks
MySql - advanced
sql table from select
howto.sql #
-- 1
SELECT 'Mon'
UNION
SELECT 'Tue'
UNION
SELECT 'Web'
UNION
SELECT 'Thu'
UNION
SELECT 'Fri'
UNION
SELECT 'Sat'
UNION
SELECT 'Sun';
-- 2
WITH RECURSIVE days_cte AS (
SELECT 'Mon' AS day_name
UNION ALL
SELECT
CASE day_name
WHEN 'Mon' THEN 'Tue'
WHEN 'Tue' THEN 'Wed'
WHEN 'Wed' THEN 'Thu'
WHEN 'Thu' THEN 'Fri'
WHEN 'Fri' THEN 'Sat'
WHEN 'Sat' THEN 'Sun'
ELSE 'Mon' -- Pro zajištění cyklického opakování
END
FROM days_cte
WHERE day_name != 'Sun'
)
SELECT day_name FROM days_cte;
On gists
Tailwind CSS
Dynamic component
index.html #
<!--
- https://www.youtube.com/watch?v=sxxUK0l8jKE
- https://play.tailwindcss.com/4cuLPqCv05
-->
<div class="p-20 flex justify-center items-center gap-12">
<img class="avatar-sm" src="https://res.cloudinary.com/thirus/image/upload/v1705061543/images/avatar.png" alt="" />
<img class="avatar" src="https://res.cloudinary.com/thirus/image/upload/v1705061543/images/avatar.png" alt="" />
<img class="avatar-lg" src="https://res.cloudinary.com/thirus/image/upload/v1705061543/images/avatar.png" alt="" />
<img class="avatar-xl" src="https://res.cloudinary.com/thirus/image/upload/v1705061543/images/avatar.png" alt="" />
</div>
On gists
Nette
IconList (scan)
IconListPresenter.php #
class IconListPresenter extends \App\Presenters\BasePresenter
{
/**
* @var Context
* @inject
*/
public $connection;
public function actionDefault()
{
$icons = scandir(INDEX_DIR . '/../webapp/frontapp/svg');
$list = [];
foreach ($icons as $icon) {
if (!strpos($icon, 'svg')) {
continue;
}
$list[] = $icon;
}
$this->template->occurrences = $this->findOccurrences($list);
$this->template->list = $list;
$scan = array_diff(scandir(INDEX_DIR.'/../app/ServiceModule/templates/IconList/icons-list/'), ['.', '..']);
$urls = [];
foreach ($scan as $iconName) {
if (is_file($file = INDEX_DIR.'/../app/ServiceModule/templates/IconList/icons-list/' . $iconName)) {
$urls[str_replace('.txt', '', $iconName)] = file_get_contents($file);
}
}
$this->template->urls = $urls;
}
public function actionUpdateIcons()
{
$icons = scandir(INDEX_DIR . '/../webapp/frontapp/svg');
$list = [];
foreach ($icons as $icon) {
if (!strpos($icon, 'svg')) {
continue;
}
$list[] = $icon;
}
$scan = array_diff(scandir(INDEX_DIR.'/../app/ServiceModule/templates/IconList/icons-list/'), ['.', '..']);
$urls = [];
foreach ($scan as $iconName) {
if (is_file($file = INDEX_DIR.'/../app/ServiceModule/templates/IconList/icons-list/' . $iconName)) {
$urls[str_replace('.txt', '', $iconName)] = file_get_contents($file);
}
}
foreach ($list as $icon) {
if (isset($urls[$icon])) {
$url = $urls[$icon];
$url = str_replace('48px.svg', '20px.svg', $url);
$fetchIcon = file_get_contents($url);
$fetchIcon = str_replace('><path', ' viewBox="0 0 20 20"><path fill="currentColor" ', $fetchIcon);
file_put_contents(INDEX_DIR.'/../webapp/frontapp/svg/' . $icon, $fetchIcon);
// save what we saved
//file_put_contents(INDEX_DIR.'/../app/ServiceModule/templates/IconList/icons-list/' . $icon . '.txt', $url);
}
}
$this->terminate();
}
private function createListFiles()
{
$allFiles = [];
foreach (Finder::findFiles('*.latte')->from(INDEX_DIR . '/../app/BrandCloudModule/') as $key => $splFile) {
$fileResouce = file_get_contents($key);
$allFiles[$key] = $fileResouce;
}
foreach (Finder::findFiles('*.vue')->from(INDEX_DIR . '/../webapp/frontapp/components/') as $key => $splFile) {
$fileResouce = file_get_contents($key);
$allFiles[$key] = $fileResouce;
}
return $allFiles;
}
private function findOccurrences($icons)
{
$allFiles = $this->createListFiles();
$all = [];
foreach ($allFiles as $filePath => $fileResource) {
foreach ($icons as $icon) {
$icon = str_replace('.svg', '', $icon);
//$nicePath = explode('..', $filePath)[1];
$nicePath = $filePath;
preg_match_all('~<aw-icon.+icon=("|\')' . preg_quote($icon) . '("|\')~', $fileResource, $m1);
preg_match_all('~("|\')' . preg_quote($icon) . '("|\')~', $fileResource, $m2);
if (!isset($all[$icon][$nicePath])) {
$all[$icon][$nicePath] = 0;
}
if (count($m1[0])) {
$all[$icon][$nicePath] += count($m1[0]);
}
if (count($m2[0])) {
$all[$icon][$nicePath] += count($m2[0]);
}
}
}
return $all;
}
public function actionSaveUrlIcon()
{
// RAW POST DATA
$data = json_decode(file_get_contents("php://input"));
// icon fetch & save
$url = str_replace('48px.svg', '20px.svg', $data->url);
$fetchIcon = file_get_contents($url);
$fetchIcon = str_replace('><path', ' viewBox="0 0 20 20"><path fill="currentColor" ', $fetchIcon);
file_put_contents(INDEX_DIR.'/../webapp/frontapp/svg/' . $data->icon, $fetchIcon);
// save what we saved
file_put_contents(INDEX_DIR.'/../app/ServiceModule/templates/IconList/icons-list/'.$data->icon . '.txt', $data->url);
// data
echo json_encode($data);
$this->terminate();
}
}
On gists
PHP Libs
PHP
Google Spread Sheet via Google_Client
index.php #
<?php
/*
https://www.nidup.io/blog/manipulate-google-sheets-in-php-with-api
*/
require './vendor/autoload.php';
// configure the Google Client
$client = new \Google_Client();
$client->setApplicationName('Google Sheets API');
$client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$client->setAccessType('offline');
// credentials.json is the key file we downloaded while setting up our Google Sheets API
$path = '***'; //google console admin need to generate this file
$client->setAuthConfig($path);
// configure the Sheets Service
$service = new \Google_Service_Sheets($client);
$spreadsheetId = '***'; // get with share with and see to url
// the spreadsheet id can be found in the url https://docs.google.com/spreadsheets/d/143xVs9lPopFSF4eJQWloDYAndMor/edit
$spreadsheet = $service->spreadsheets->get($spreadsheetId);
var_dump($spreadsheet);
// all values
$range = 'List 1'; // here we use the name of the Sheet to get all the rows
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
var_dump($values);
// rows by range
$range = 'List 1!A1:F10';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
var_dump($values);
// cells of column
$range = 'List1!B1:B21'; // the column containing the movie title
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
var_dump($values);
// rows into json objects
$range = 'Sheet1';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$rows = $response->getValues();
// Remove the first one that contains headers
$headers = array_shift($rows);
// Combine the headers with each following row
$array = [];
foreach ($rows as $row) {
$array[] = array_combine($headers, $row);
}
var_dump($array);
/*
$jsonString = json_encode($array, JSON_PRETTY_PRINT);
print($jsonString);
*/
// append new row
$newRow = [
'456740',
'Hellboy',
'https://image.tmdb.org/t/p/w500/bk8LyaMqUtaQ9hUShuvFznQYQKR.jpg',
"Hellboy comes to England, where he must defeat Nimue, Merlin's consort and the Blood Queen. But their battle will bring about the end of the world, a fate he desperately tries to turn away.",
'1554944400',
'Fantasy, Action'
];
$rows = [$newRow]; // you can append several rows at once
$valueRange = new \Google_Service_Sheets_ValueRange();
$valueRange->setValues($rows);
$range = 'List 1'; // the service will detect the last row of this sheet
$options = ['valueInputOption' => 'USER_ENTERED'];
$service->spreadsheets_values->append($spreadsheetId, $range, $valueRange, $options);
// update existing
$updateRow = [
'456740',
'Hellboy Updated Row',
'https://image.tmdb.org/t/p/w500/bk8LyaMqUtaQ9hUShuvFznQYQKR.jpg',
"Hellboy comes to England, where he must defeat Nimue, Merlin's consort and the Blood Queen. But their battle will bring about the end of the world, a fate he desperately tries to turn away.",
'1554944400',
'Fantasy, Action'
];
$rows = [$updateRow];
$valueRange = new \Google_Service_Sheets_ValueRange();
$valueRange->setValues($rows);
$range = 'Sheet1!A2'; // where the replacement will start, here, first column and second line
$options = ['valueInputOption' => 'USER_ENTERED'];
$service->spreadsheets_values->update($spreadsheetId, $range, $valueRange, $options);
// delete some row
$range = 'Sheet1!A23:F24'; // the range to clear, the 23th and 24th lines
$clear = new \Google_Service_Sheets_ClearValuesRequest();
$service->spreadsheets_values->clear($spreadsheetId, $range, $clear);
/** with text formatting */
$range = 'List 1!B4';
$params = [
'ranges' => $range,
'fields' => 'sheets.data.rowData.values',
'includeGridData' => true,
];
$response = $service->spreadsheets->get($spreadsheetId, $params);
$data = $response->getSheets()[0]['data'][0]['rowData'][0]['values'][0];
$formattedValue = $data['formattedValue'];
$hyperlink = $data['hyperlink'];
$userEnteredFormat = $data['userEnteredFormat'];
var_dump($formattedValue);
var_dump($hyperlink);
var_dump($userEnteredFormat);