441 lines
14 KiB
PHP
Executable File
441 lines
14 KiB
PHP
Executable File
<?php
|
|
// تنزيل البيانات من اي تيبل
|
|
require_once('fixed/config/go_con.php');
|
|
ini_set('memory_limit', '2048M');
|
|
set_time_limit(300);
|
|
|
|
// $query = "SELECT `all_books`.`tretment_id`, `all_books`.`number`,
|
|
|
|
|
|
// CASE
|
|
// WHEN `tretment`.`commercial_register_id` = 2 THEN 'الرجالي'
|
|
// WHEN `tretment`.`commercial_register_id` = 3 THEN 'النسائي'
|
|
// ELSE '..'
|
|
// END AS `commercial`
|
|
|
|
|
|
// FROM `all_books`
|
|
// INNER JOIN `tretment` ON `all_books`.`tretment_id` = `tretment`.`id`
|
|
// WHERE `all_books`.`tretment_id` IN (SELECT `tretment_id` FROM `all_books` WHERE `status_id` IN (6,8,12,13,14,15) GROUP BY `tretment_id` HAVING count(`book_details_id`) = 1)
|
|
// AND `all_books`.`day_date` < '2020-10-12'
|
|
// AND `all_books`.`status_id` IN (6,8,12,13,14,15)
|
|
// ORDER BY `all_books`.`tretment_id` DESC";
|
|
|
|
|
|
|
|
|
|
// $query = "SELECT
|
|
// `all_books`.`tretment_id`,
|
|
// `all_books`.`number`,
|
|
// `tretment`.`name`,
|
|
// `tt`.`dd`,
|
|
// tt.cnt,
|
|
// CASE
|
|
// WHEN `tretment`.`commercial_register_id` = 2 THEN 'الرجالي'
|
|
// WHEN `tretment`.`commercial_register_id` = 3 THEN 'النسائي'
|
|
// ELSE '..'
|
|
// END AS `commercial`
|
|
|
|
|
|
// FROM `all_books`
|
|
// INNER JOIN `tretment` ON `all_books`.`tretment_id` = `tretment`.`id`
|
|
// INNER JOIN (SELECT `tretment_id`, count(`book_details_id`) AS cnt, MAX(`day_date`) AS dd FROM `all_books` WHERE `status_id` IN (6,8,12,13,14,15) GROUP BY `tretment_id` ORDER BY cnt DESC) tt ON tt.tretment_id = `tretment`.`id`
|
|
// WHERE `tt`.`dd` < '2023-06-14'
|
|
// AND `all_books`.`status_id` IN (6,8,12,13,14,15)
|
|
// GROUP BY `all_books`.`tretment_id`
|
|
// ORDER BY `tt`.`cnt` DESC";
|
|
// $result = $db->query($query);
|
|
// if ($result->num_rows > 0) {
|
|
// $output_filename = "2023-06-14.csv";
|
|
// $output_file = fopen($output_filename, "w");
|
|
|
|
|
|
// while ($row = $result->fetch_assoc()) {
|
|
// fputcsv($output_file, $row);
|
|
// }
|
|
|
|
// fclose($output_file);
|
|
// } else {
|
|
// echo "No data found.";
|
|
// }
|
|
|
|
|
|
|
|
// $query = "SELECT `tretment`.`name`, `tretment`.`number` , `tretment`.`date_time` , COUNT(`tt`.`tretment_id`) AS `count`
|
|
|
|
// FROM `tretment`
|
|
// INNER JOIN (SELECT `book`.`tretment_id`, `bd`.`day_date`
|
|
|
|
// FROM `book_details` AS `bd`
|
|
// INNER JOIN `book` ON `bd`.`book_id` = `book`.`id`
|
|
// JOIN (SELECT `book_id`, MAX(`id`) AS `max_id` FROM `book_details` GROUP BY `book_id`) `t1_max` ON `bd`.`book_id` = `t1_max`.`book_id` AND `bd`.`id` = `t1_max`.`max_id`
|
|
|
|
// WHERE `book`.`tretment_id` IN (SELECT `t`.`id` FROM `tretment` AS `t` WHERE `commercial_register_id` = 3)
|
|
// AND `bd`.`status_id` IN (6,8,12,13,14,15)
|
|
|
|
// GROUP BY `book`.`tretment_id`, `bd`.`day_date`) AS `tt` ON `tretment`.`id` = `tt`.`tretment_id`
|
|
|
|
// GROUP BY `tretment`.`id`";
|
|
// $result = $db->query($query);
|
|
// if ($result->num_rows > 0) {
|
|
// $output_filename = "2024-01-16.csv";
|
|
// $output_file = fopen($output_filename, "w");
|
|
|
|
|
|
// while ($row = $result->fetch_assoc()) {
|
|
// fputcsv($output_file, $row);
|
|
// }
|
|
|
|
// fclose($output_file);
|
|
// } else {
|
|
// echo "No data found.";
|
|
// }
|
|
|
|
require '../../vendor/autoload.php';
|
|
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
|
|
|
// $query = "SELECT
|
|
|
|
// `tretment`.`name` AS `tr_name`,
|
|
// `tretment`.`number` AS `number`,
|
|
// COUNT(`book`.`id`)
|
|
|
|
// FROM `book_details` AS `bd`
|
|
|
|
// INNER JOIN `book` ON `bd`.`book_id` = `book`.`id`
|
|
// INNER JOIN `tretment` ON `book`.`tretment_id` = `tretment`.`id`
|
|
|
|
// INNER JOIN (
|
|
// SELECT MIN(`book_details`.`book_id`) AS `bbf`, `tretment`.`id` AS `dsad`
|
|
|
|
// FROM `book_details`
|
|
// INNER JOIN `book` ON `book_details`.`book_id` = `book`.`id`
|
|
// INNER JOIN `tretment` ON `tretment`.`id` = `book`.`tretment_id`
|
|
|
|
// GROUP BY `book`.`tretment_id`
|
|
// ) `t12_max`
|
|
// ON `tretment`.`id` = `t12_max`.`dsad` AND `bd`.`book_id` = `t12_max`.`bbf`
|
|
|
|
// WHERE `bd`.`branch_id` = 4
|
|
// AND `bd`.`status_id` IN (6,8,12,13,14,15)
|
|
|
|
// GROUP BY `tretment`.`id`"; // your SQL query here
|
|
// $result = $db->query($query);
|
|
|
|
// if ($result->num_rows > 0) {
|
|
// $spreadsheet = new Spreadsheet();
|
|
// $sheet = $spreadsheet->getActiveSheet();
|
|
|
|
// $rowNumber = 1;
|
|
// while ($row = $result->fetch_assoc()) {
|
|
// $col = 'A';
|
|
// foreach ($row as $cell) {
|
|
// $sheet->setCellValue($col++ . $rowNumber, $cell);
|
|
// }
|
|
// $rowNumber++;
|
|
// }
|
|
|
|
// $writer = new Xlsx($spreadsheet);
|
|
// $output_filename = "2024-01-18.xlsx";
|
|
// $writer->save($output_filename);
|
|
// } else {
|
|
// echo "No data found.";
|
|
// }
|
|
|
|
// $query = "SELECT
|
|
|
|
// `tretment`.`name` AS `tr_name`,
|
|
// `tretment`.`number` AS `number`,
|
|
// COUNT(`book`.`id`)
|
|
|
|
// FROM `book_details` AS `bd`
|
|
|
|
// INNER JOIN `book` ON `bd`.`book_id` = `book`.`id`
|
|
// INNER JOIN `tretment` ON `book`.`tretment_id` = `tretment`.`id`
|
|
|
|
|
|
|
|
// WHERE `bd`.`branch_id` = 12
|
|
// AND `bd`.`status_id` IN (6,8,12,13,14,15)
|
|
|
|
// GROUP BY `tretment`.`id`";
|
|
// $result = $db->query($query);
|
|
|
|
// if ($result->num_rows > 0) {
|
|
// $spreadsheet = new Spreadsheet();
|
|
// $sheet = $spreadsheet->getActiveSheet();
|
|
|
|
// $rowNumber = 1;
|
|
// while ($row = $result->fetch_assoc()) {
|
|
// $col = 'A';
|
|
// foreach ($row as $cell) {
|
|
// $sheet->setCellValue($col++ . $rowNumber, $cell);
|
|
// }
|
|
// $rowNumber++;
|
|
// }
|
|
|
|
// $writer = new Xlsx($spreadsheet);
|
|
// $output_filename = "2024-01-18.xlsx";
|
|
// $writer->save($output_filename);
|
|
// } else {
|
|
// echo "No data found.";
|
|
// }
|
|
|
|
|
|
// $query = "SELECT
|
|
|
|
// `tretment`.`name` AS `tr_name`,
|
|
// `tretment`.`number` AS `number`,
|
|
// COUNT(`book`.`id`) AS `count`
|
|
|
|
// FROM `book_details` AS `bd`
|
|
|
|
// INNER JOIN `book` ON `bd`.`book_id` = `book`.`id`
|
|
// INNER JOIN `tretment` ON `book`.`tretment_id` = `tretment`.`id`
|
|
|
|
|
|
|
|
// WHERE `bd`.`branch_id` = 2
|
|
// AND `bd`.`status_id` IN (6,8,12,13,14,15)
|
|
|
|
// GROUP BY `tretment`.`id`
|
|
// ORDER BY `count` DESC";
|
|
// $result = $db->query($query);
|
|
|
|
// if ($result->num_rows > 0) {
|
|
// $spreadsheet = new Spreadsheet();
|
|
// $sheet = $spreadsheet->getActiveSheet();
|
|
|
|
// $rowNumber = 1;
|
|
// while ($row = $result->fetch_assoc()) {
|
|
// $col = 'A';
|
|
// foreach ($row as $cell) {
|
|
// $sheet->setCellValue($col++ . $rowNumber, $cell);
|
|
// }
|
|
// $rowNumber++;
|
|
// }
|
|
|
|
// $writer = new Xlsx($spreadsheet);
|
|
// $output_filename = "2024-03-18.xlsx";
|
|
// $writer->save($output_filename);
|
|
// } else {
|
|
// echo "No data found.";
|
|
// }
|
|
|
|
|
|
// $query = "SELECT
|
|
// `invoices`.`price`,
|
|
// `bd`.`day_date`,
|
|
// `bd`.`day_time`,
|
|
// `services`.`name`,
|
|
// `services`.`price` AS `serv_p`,
|
|
// `services`.`point`,
|
|
// `tretment`.`name` AS `tr_name`,
|
|
// `tretment`.`number`,
|
|
// count(`tretment`.`name`),
|
|
// IFNULL(
|
|
// (SELECT
|
|
// SUM(`invoices_discount_details`.`discount`)
|
|
// FROM `invoices_discount_details`
|
|
// LEFT JOIN `invoices_discount` ON `invoices_discount_details`.`invoices_discount_id` = `invoices_discount`.`id`
|
|
// LEFT JOIN `invoices_details` ON `invoices_discount`.`invoices_details_id` = `invoices_details`.`id`
|
|
// WHERE `invoices_details`.`invoices_id` = `invoices`.`id`
|
|
// AND `invoices_discount_details`.`services_id` = `services`.`id`
|
|
// AND `invoices_discount_details`.`id` IN (SELECT
|
|
// `invoices_discount_details`.`id`
|
|
// FROM `invoices_discount_details`
|
|
// LEFT JOIN `invoices_discount` ON `invoices_discount_details`.`invoices_discount_id` = `invoices_discount`.`id`
|
|
// LEFT JOIN `invoices_details` ON `invoices_discount`.`invoices_details_id` = `invoices_details`.`id`
|
|
// WHERE `invoices_details`.`invoices_id` = `invoices`.`id`
|
|
// AND `invoices_discount_details`.`services_id` = `services`.`id`
|
|
// GROUP BY `invoices_discount_details`.`reason`
|
|
// )
|
|
// )
|
|
// ,0) AS `desc`
|
|
|
|
// FROM `book_details` AS `bd`
|
|
|
|
// INNER JOIN `book` ON `bd`.`book_id` = `book`.`id`
|
|
// INNER JOIN `invoices_has_book` ON `invoices_has_book`.`book_id` = `book`.`id`
|
|
// INNER JOIN `invoices` ON `invoices_has_book`.`invoices_id` = `invoices`.`id`
|
|
// INNER JOIN `tretment` ON `book`.`tretment_id` = `tretment`.`id`
|
|
// INNER JOIN `services` ON `bd`.`services_id` = `services`.`id`
|
|
// INNER JOIN `emplyee` ON `bd`.`emplyee_id` = `emplyee`.`id`
|
|
|
|
// WHERE `bd`.`emplyee_id`='338'
|
|
// AND `emplyee`.`occupation_id` = 2
|
|
// AND `bd`.`status_id` IN (6,8,12,13,14,15)
|
|
// AND `bd`.`id` IN (SELECT MAX(`book_details`.`id`) FROM `book_details` GROUP BY `book_details`.`book_id`)
|
|
// group by `tretment`.`id`
|
|
// ORDER BY `bd`.`day_date`, `bd`.`day_time`
|
|
// ";
|
|
|
|
// $query = <<<SQL
|
|
// SELECT `tretment`.`name`, `tretment`.`number` ,`tretment_has_service`.`date_time`, `services`.`name` FROM `tretment_has_service` INNER JOIN `tretment` ON `tretment`.`id` = `tretment_has_service`.`tretment_id` INNER JOIN `services` ON `services`.`id` = `tretment_has_service`.`services_id` WHERE (`tretment_has_service`.`services_id` BETWEEN 249 AND 265 ) OR (`tretment_has_service`.`services_id` BETWEEN 133 AND 178 ) AND `tretment_has_service`.`done_id` = 1
|
|
// SQL;
|
|
|
|
|
|
// $query = <<<SQL
|
|
// SELECT
|
|
// tretment_id AS `tretment_id`,
|
|
// number AS `number`,
|
|
// day_date AS `day_date`,
|
|
// SUM(selected_services_count) AS `selected_services_count`,
|
|
// SUM(total_services_count) AS `total_services_count`,
|
|
// SUM(selected_services_count) / SUM(total_services_count) AS `oer`
|
|
// FROM (SELECT
|
|
// ddt.tretment_id,
|
|
// ddt.number,
|
|
// MAX(ddt.day_date) AS day_date,
|
|
// COUNT(ddt.serv_id) AS service_count,
|
|
// ddt.serv_id,
|
|
// ddt.name,
|
|
// SUM(CASE WHEN ddt.serv_id IN (1, 30, 93, 183) THEN 1 ELSE 0 END) AS selected_services_count,
|
|
// COUNT(ddt.serv_id) AS total_services_count
|
|
// FROM
|
|
// (
|
|
// SELECT
|
|
// bd.day_date,
|
|
// bd.day_time,
|
|
// bd.branch_id,
|
|
// bd.emplyee_id,
|
|
// bd.status_id,
|
|
// bd.date_time,
|
|
// bd.book_id,
|
|
// services.id AS serv_id,
|
|
// services.name,
|
|
// book.tretment_id,
|
|
// book.notes,
|
|
// book.date_time AS user_date,
|
|
// user.name AS user_book,
|
|
// tretment.name AS tr_name,
|
|
// tretment.number
|
|
// FROM
|
|
// book_details AS bd
|
|
// LEFT JOIN
|
|
// book ON bd.book_id = book.id
|
|
// LEFT JOIN
|
|
// tretment ON book.tretment_id = tretment.id
|
|
// LEFT JOIN
|
|
// user ON book.user_id = user.id
|
|
// LEFT JOIN
|
|
// services ON bd.services_id = services.id
|
|
// LEFT JOIN
|
|
// emplyee ON bd.emplyee_id = emplyee.id
|
|
// JOIN
|
|
// (
|
|
// SELECT
|
|
// book_id, MAX(id) AS max_id
|
|
// FROM
|
|
// book_details
|
|
// GROUP BY
|
|
// book_id
|
|
// ) AS t1_max ON bd.book_id = t1_max.book_id AND bd.id = t1_max.max_id
|
|
// WHERE
|
|
// emplyee.activation_id = 2
|
|
// AND bd.status_id IN (6, 8, 12, 13, 14, 15)
|
|
// AND book.tretment_id IN
|
|
// (
|
|
// SELECT
|
|
// id AS ttr
|
|
// FROM
|
|
// (
|
|
// SELECT
|
|
// tretment.id,
|
|
// tretment.name,
|
|
// tretment.number,
|
|
// SUM(services.price + ((services.price * 15) / 100)) AS total_pays,
|
|
// COUNT(book_id) AS total_pay_books
|
|
// FROM
|
|
// tretment
|
|
// LEFT JOIN
|
|
// book ON book.tretment_id = tretment.id
|
|
// LEFT JOIN
|
|
// book_details AS dd ON book.id = dd.book_id
|
|
// LEFT JOIN
|
|
// services ON dd.services_id = services.id
|
|
// WHERE
|
|
// tretment.id IN (SELECT tretment_id FROM total_points WHERE point > 5000)
|
|
// AND dd.id IN (SELECT MAX(book_details.id) FROM book_details WHERE book_id = dd.book_id GROUP BY book_details.book_id)
|
|
// AND dd.status_id IN (6, 8, 12, 13, 14, 15)
|
|
// GROUP BY
|
|
// tretment.id
|
|
// HAVING
|
|
// total_pay_books > 5
|
|
// AND total_pays > 5000
|
|
// ) AS subquery
|
|
// )
|
|
// ORDER BY
|
|
// bd.day_time,
|
|
// bd.date_time
|
|
// ) AS ddt
|
|
// WHERE ddt.serv_id <> 0
|
|
// GROUP BY
|
|
// ddt.tretment_id,
|
|
// ddt.serv_id
|
|
// ) AS ddwrer
|
|
// GROUP BY tretment_id
|
|
// HAVING oer >= 0.50
|
|
// ORDER BY oer DESC
|
|
// SQL;
|
|
|
|
$query = <<<SQL
|
|
SELECT
|
|
tt.name,
|
|
tt.number,
|
|
book_details.day_date,
|
|
book_details.day_time
|
|
FROM
|
|
(SELECT
|
|
tretment.name,
|
|
tretment.number,
|
|
MAX(dd.id) AS ddid
|
|
FROM
|
|
tretment
|
|
LEFT JOIN
|
|
book ON book.tretment_id = tretment.id
|
|
LEFT JOIN
|
|
book_details AS dd ON book.id = dd.book_id
|
|
LEFT JOIN
|
|
services ON dd.services_id = services.id
|
|
WHERE
|
|
dd.id IN (SELECT MAX(book_details.id)
|
|
FROM book_details
|
|
WHERE book_id = dd.book_id
|
|
GROUP BY book_id)
|
|
AND dd.status_id IN (6, 8, 12, 13, 14, 15)
|
|
GROUP BY
|
|
tretment.id, tretment.name, tretment.number
|
|
) AS tt
|
|
INNER JOIN
|
|
book_details ON book_details.id = tt.ddid
|
|
WHERE
|
|
book_details.branch_id = 13
|
|
ORDER BY `book_details`.`day_date` ASC
|
|
SQL;
|
|
|
|
$result = $db->query($query);
|
|
|
|
if ($result->num_rows > 0) {
|
|
$spreadsheet = new Spreadsheet();
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
|
|
$rowNumber = 1;
|
|
while ($row = $result->fetch_assoc()) {
|
|
$col = 'A';
|
|
foreach ($row as $cell) {
|
|
$sheet->setCellValue($col++ . $rowNumber, $cell);
|
|
}
|
|
$rowNumber++;
|
|
}
|
|
|
|
$writer = new Xlsx($spreadsheet);
|
|
$output_filename = "asda.xlsx";
|
|
$writer->save($output_filename);
|
|
} else {
|
|
echo "No data found.";
|
|
}
|
|
|