788 lines
26 KiB
PHP
788 lines
26 KiB
PHP
<?php
|
|
// preDb_0dcc87940d3655fa574b253df04ca1c3
|
|
// %g7!@fG
|
|
function createPzFromInvoice($record, $stockId)
|
|
{
|
|
global $app_list_strings;
|
|
$db = $GLOBALS['db'];
|
|
|
|
if ($stockId == null) {
|
|
die('Nie wskazano magazynu');
|
|
} else {
|
|
$stock = $db->fetchByAssoc($db->query("SELECT id, name FROM ecmstocks WHERE id = '" . $stockId . "'"));
|
|
if ($stock == null) {
|
|
die("Wskazano błedny magazyn");
|
|
}
|
|
}
|
|
|
|
$curl = curl_init();
|
|
curl_setopt($curl, CURLOPT_CUSTOMREQUEST, 'GET');
|
|
curl_setopt($curl, CURLOPT_VERBOSE, 1);
|
|
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
|
|
curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, 0);
|
|
curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, 0);
|
|
curl_setopt($curl, CURLOPT_URL, "https://crm.e5.pl/REST/index.php?key=7e50a8a5-f01f-4fbc-8c1b-59f3fc474bb5&action=getInvoice&record=" . $record);
|
|
$res = curl_exec($curl);
|
|
$inv = json_decode($res);
|
|
$inv->position_list = json_decode($inv->position_list);
|
|
echo 'Tworzę dokument PZ w Twinpol<br>';
|
|
echo 'Numer faktury: ' . $inv->document_no . '<br>';
|
|
echo 'Na magazyn: ' . $stock['name'] . ' (ID: ' . $stock['id'] . ')<br><br>';
|
|
|
|
//check if PZ not exists
|
|
$exists = $db->fetchByAssoc($db->query("SELECT id, document_no FROM ecmstockdocins WHERE name='" . $inv->document_no . "'"));
|
|
if ($exists) {
|
|
echo "Istnieje już dokument PZ przypisany do tej faktury: <a target='new' href='https://crm.twinpol.com/index.php?module=EcmStockDocIns&action=DetailView&record=" . $exists['id'] . "'>" . $exists['document_no'] . "</a>";
|
|
die();
|
|
}
|
|
|
|
// Create PZ
|
|
$pz = new EcmStockDocIn();
|
|
$pz->parent_id = 'f084e64a-4e63-a3d1-6417-58cbf730df3f';
|
|
$pz->parent_name = 'E5 Polska Sp. z o.o.';
|
|
$pz->parent_address_street = 'ul. Wąwozowa 11';
|
|
$pz->parent_address_postalcode = '02-796';
|
|
$pz->parent_address_city = 'Warszawa';
|
|
$pz->parent_address_country = 'Polska';
|
|
$pz->parent_nip = '525-21-73-990';
|
|
$pz->name = $inv->document_no;
|
|
$pz->date_entered = date('Y-m-d H:i:s');
|
|
$pz->date_modified = date('Y-m-d H:i:s');
|
|
$pz->modified_user_id = '86e2871a-9d00-f756-6bba-59cce0c5db59'; // magazyn@e5.pl
|
|
$pz->assigned_user_id = '86e2871a-9d00-f756-6bba-59cce0c5db59';
|
|
$pz->created_by = '86e2871a-9d00-f756-6bba-59cce0c5db59';
|
|
$pz->deleted = '0';
|
|
$pz->register_date = date('Y-m-d');
|
|
$pz->stock_id = $stock['id'];
|
|
$pz->total = format_number($inv->total_netto);
|
|
$pz->date_fk = $inv->register_date;
|
|
$pz->status = 'accepted';
|
|
$pz->kind = 'other';
|
|
$pz->total_netto = format_number($inv->total_netto);
|
|
$pz->total_brutto = format_number($inv->total_brutto);
|
|
$pz->canceled = '0';
|
|
$pz->country_transport_val = 0;
|
|
$pz->foreign_transport_val = 0;
|
|
$pz->foreign_transport_cur_id = 'PLN';
|
|
$pz->country_transport_cur_id = 'PLN';
|
|
$pz->addition_cost_val = 0;
|
|
$pz->addition_cost_cur_id = 'PLN';
|
|
$pz->currency_id = 'PLN';
|
|
$pz->currency_value = 0;
|
|
$pz->currency_sad_value = 0;
|
|
$pz->vats_summary = $inv->vats_summary;
|
|
$pz->position_list = array();
|
|
$pz->fromREST = true;
|
|
|
|
$gotAllProducts = true; // hope :)
|
|
foreach ($inv->position_list as $product) {
|
|
echo 'Produkt: ' . $product->product_code . '<br>';
|
|
$p = getProduct($product->product_code);
|
|
if (!$p) {
|
|
echo ' Produkt nie istnieje w bazie TWINPOL<br>';
|
|
$gotAllProducts = false;
|
|
continue;
|
|
}
|
|
// add to pz
|
|
$prod = array();
|
|
$prod['date_entered'] = date('Y-m-d H:i:s');
|
|
$prod['date_modified'] = date('Y-m-d H:i:s');
|
|
$prod['modified_user_id'] = '86e2871a-9d00-f756-6bba-59cce0c5db59';
|
|
$prod['assigned_user_id'] = '86e2871a-9d00-f756-6bba-59cce0c5db59';
|
|
$prod['created_by'] = '86e2871a-9d00-f756-6bba-59cce0c5db59';
|
|
$prod['deleted'] = '0';
|
|
$prod['ecmstockdocin_id'] = $pz->id;
|
|
$prod['product_id'] = $p->id;
|
|
$prod['position'] = $product->position;
|
|
$prod['product_code'] = $p->code;
|
|
$prod['name'] = $p->name;
|
|
$prod['quantity'] = $product->quantity;
|
|
$prod['price'] = $product->price_netto;
|
|
$prod['price_fk'] = $product->price_netto;
|
|
$prod['total'] = $product->total_netto;
|
|
$prod['unit_id'] = $product->unit_id;
|
|
$prod['unit_name'] = $product->unit_name;
|
|
$prod['ecmproductcategory_id'] = $p->product_category_id;
|
|
$prod['ecmvat_id'] = $p->vat_id;
|
|
$prod['ecmvat_value'] = $p->vat_value;
|
|
$prod['ecmvat_name'] = $p->vat_name;
|
|
$prod['palet'] = 0;
|
|
$prod['clo'] = 0;
|
|
$prod['waga'] = 0;
|
|
array_push($pz->position_list, $prod);
|
|
}
|
|
if (!$gotAllProducts) {
|
|
echo 'Brak produktów w bazie Twinpol. Dokument PZ nie został utworzony';
|
|
die();
|
|
} else {
|
|
$new = $pz->save(true);
|
|
echo '<a target="new" href="https://crm.twinpol.com/index.php?module=EcmStockDocIns&action=DetailView&record=' . $new . '">Utworzono PZ.</a>';
|
|
die();
|
|
}
|
|
}
|
|
|
|
function sendStocks()
|
|
{
|
|
$db = $GLOBALS['db'];
|
|
$stocks = array();
|
|
$stocksRes = $db->query("SELECT id, name FROM ecmstocks WHERE deleted='0'");
|
|
while ($row = $db->fetchByAssoc($stocksRes)) {
|
|
$tmp = array();
|
|
$tmp['id'] = $row['id'];
|
|
$tmp['name'] = $row['name'];
|
|
$stocks[] = $tmp;
|
|
}
|
|
echo json_encode($stocks);
|
|
}
|
|
function sendSale($record)
|
|
{
|
|
require_once('modules/EcmSales/EcmSale.php');
|
|
$sale = new EcmSale();
|
|
$sale->retrieve($record);
|
|
$pl = $sale->getPositionList();
|
|
$response = array(
|
|
'document_no' => $sale->document_no,
|
|
'name' => $sale->name,
|
|
'parent_document_no' => $sale->parent_document_no,
|
|
'register_date' => $sale->register_date,
|
|
'send_date' => $sale->send_date,
|
|
'delivery_date' => $sale->delivery_date,
|
|
'currency_id' => $sale->currency_id,
|
|
'total_netto' => $sale->total_netto,
|
|
'total_brutto' => $sale->total_brutto,
|
|
'discount' => $sale->discount,
|
|
'parent_name' => $sale->parent_name,
|
|
'parent_nip' => $sale->parent_nip,
|
|
'parent_iln' => $sale->parent_iln,
|
|
'parent_address_street' => $sale->parent_address_street,
|
|
'parent_address_city' => $sale->parent_address_city,
|
|
'parent_address_postalcode' => $sale->parent_address_postalcode,
|
|
'parent_address_country' => $sale->parent_address_country,
|
|
'shipping_address_name' => $sale->shipping_address_name,
|
|
'shipping_address_street' => $sale->shipping_address_street,
|
|
'shipping_address_postalcode' => $sale->shipping_address_postalcode,
|
|
'shipping_address_city' => $sale->shipping_address_city,
|
|
'shipping_address_country' => $sale->shipping_address_country,
|
|
'vats_summary' => $sale->vats_summary,
|
|
'position_list' => $pl,
|
|
'id' => $sale->id
|
|
);
|
|
echo json_encode($response);
|
|
}
|
|
function updateSaleE5Number($record, $e5_record, $e5_document_no)
|
|
{
|
|
$db = $GLOBALS['db'];
|
|
$db->query("UPDATE ecmsales SET edi_zs_id='$e5_record', edi_zs_document_no='$e5_document_no' WHERE id='$record'");
|
|
die();
|
|
}
|
|
function createCostDocumentFromInvoice($record)
|
|
{
|
|
global $app_list_strings;
|
|
$db = $GLOBALS['db'];
|
|
|
|
|
|
$curl = curl_init();
|
|
curl_setopt($curl, CURLOPT_CUSTOMREQUEST, 'GET');
|
|
curl_setopt($curl, CURLOPT_VERBOSE, 1);
|
|
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
|
|
curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, 0);
|
|
curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, 0);
|
|
curl_setopt($curl, CURLOPT_URL, "https://crm.e5.pl/REST/index.php?key=7e50a8a5-f01f-4fbc-8c1b-59f3fc474bb5&action=getInvoice&record=" . $record);
|
|
$res = curl_exec($curl);
|
|
$inv = json_decode($res);
|
|
$inv->position_list = json_decode($inv->position_list);
|
|
echo 'Tworzę dokument kosztowy w Twinpol<br>';
|
|
echo 'Numer faktury: ' . $inv->document_no . '<br>';
|
|
|
|
removeDocumentIfExists($inv->document_no);
|
|
|
|
$doc = new Document();
|
|
$doc->document_name = $inv->document_no;
|
|
$doc->document_number = $inv->document_no;
|
|
$doc->doc_symbol = $inv->document_no;
|
|
$doc->revision = 1;
|
|
|
|
$doc->category_id = 'invoice';
|
|
$doc->status = '2';
|
|
$doc->transaction_type = '1';
|
|
$doc->document_type = 'k';
|
|
|
|
$doc->active_date = $inv->register_date;
|
|
$doc->payment_date = $inv->payment_date;
|
|
$doc->document_date = $inv->register_date;
|
|
$doc->date_entered = date('Y-m-d H:i:s');
|
|
$doc->date_modified = date('Y-m-d H:i:s');
|
|
|
|
$doc->value = $inv->total_brutto;
|
|
$doc->left_paid = $inv->total_brutto;
|
|
$doc->currency_id = $inv->currency_id;
|
|
$doc->currency_value = 1;
|
|
|
|
$id = $doc->save();
|
|
|
|
$parents = array();
|
|
$parent = array();
|
|
$parent['id'] = create_guid();
|
|
$parent['name'] = 'E5 Polska Sp. z o.o.';
|
|
$parent['parent_id'] = 'f084e64a-4e63-a3d1-6417-58cbf730df3f';
|
|
$parent['parent_type'] = 'Accounts';
|
|
$parents[] = $parent;
|
|
$doc->saveParentList($parents);
|
|
|
|
$vats = array();
|
|
$vatList = explode(',', $inv->vats_summary);
|
|
foreach ($vatList as $vatItem) {
|
|
if (empty($vatItem)) continue;
|
|
$parts = explode(':', $vatItem);
|
|
if (count($parts) != 5) continue;
|
|
$vat = array();
|
|
$vat['netto'] = $parts[1];
|
|
$vat['vat'] = $parts[2];
|
|
$vat['vat_id'] = str_replace('%', '', $parts[0]);
|
|
$vat['vat_value'] = $parts[0];
|
|
$vats[] = $vat;
|
|
}
|
|
$doc->saveVatList($vats);
|
|
|
|
echo 'Utworzono dokument kosztowy: <a target="new" href="https://crm.twinpol.com/index.php?module=Documents&action=DetailView&record=' . $id . '">' . $doc->document_name . '</a><br>';
|
|
}
|
|
// local helpers
|
|
function getProduct($code)
|
|
{
|
|
$db = $GLOBALS['db'];
|
|
$res = $db->fetchByAssoc($db->query("SELECT * FROM ecmproducts WHERE code='$code' AND deleted=0"));
|
|
if (!$res) {
|
|
return null;
|
|
} else {
|
|
require_once('modules/EcmProducts/EcmProduct.php');
|
|
$prod = new EcmProduct();
|
|
$prod->retrieve($res['id']);
|
|
return $prod;
|
|
}
|
|
}
|
|
/*
|
|
function createProduct($record) {
|
|
$curl = curl_init();
|
|
curl_setopt($curl, CURLOPT_CUSTOMREQUEST, 'GET' );
|
|
curl_setopt($curl, CURLOPT_VERBOSE, 1);
|
|
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
|
|
curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, 0);
|
|
curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, 0);
|
|
curl_setopt($curl, CURLOPT_URL, "https://crm.e5.pl/REST/index.php?key=7e50a8a5-f01f-4fbc-8c1b-59f3fc474bb5&action=getProduct&record=".$record);
|
|
$res = curl_exec($curl);
|
|
$prod = json_decode($res);
|
|
require_once('modules/EcmProducts/EcmProduct.php');
|
|
$new = new EcmProduct();
|
|
$new->name = $prod->name;
|
|
$new->code = $prod->code;
|
|
$new->ean = $prod->ean;
|
|
$new->unit_id = $prod->unit_id;
|
|
$new->product_category_id = $prod->product_category_id;
|
|
$new->product_category_name = $prod->product_category_name;
|
|
$new->position_list3 = [];
|
|
$db = $GLOBALS['db'];
|
|
$vat = $db->fetchByAssoc($db->query("SELECT * FROM ecmvats WHERE value=$prod->vat"));
|
|
$new->vat_id = $vat['id'];
|
|
$new->vat_name = $vat['name'];
|
|
$new->vat_value = $vat['value'];
|
|
$new->assigned_user_id = '1';
|
|
$new->modified_user_id = '1';
|
|
$new->created_by = '1';
|
|
$new->date_entered = date('Y-m-d H:i:s');
|
|
$new->date_modified = date('Y-m-d H:i:s');
|
|
$new->assigned_user_name = 'Admin System';
|
|
$new->modified_user_name = 'Admin System';
|
|
//$new->save();
|
|
return $new;
|
|
}
|
|
*/
|
|
function removeDocumentIfExists($document_number)
|
|
{
|
|
$db = $GLOBALS['db'];
|
|
$res = $db->fetchByAssoc($db->query("SELECT id FROM documents WHERE document_number='$document_number'"));
|
|
if ($res) {
|
|
echo 'Istnieje już dokument kosztowy o numerze ' . $document_number . '. Usuwam go...<br>';
|
|
$db->query("DELETE FROM documents WHERE id='" . $res['id'] . "'");
|
|
$db->query("DELETE FROM documents_accounts WHERE document_id='" . $res['id'] . "'");
|
|
$db->query("DELETE FROM documents_vat WHERE document_id='" . $res['id'] . "'");
|
|
}
|
|
}
|
|
function brecho($msg)
|
|
{
|
|
echo '<br><br>';
|
|
var_dump($msg);
|
|
echo '<br><br>';
|
|
}
|
|
// AI analysis
|
|
function createCSVReports()
|
|
{
|
|
{
|
|
$db = $GLOBALS['db'];
|
|
$exportDir = __DIR__ . "/export";
|
|
|
|
$jobs = [
|
|
[
|
|
'sql' => "
|
|
SELECT
|
|
i.document_no,
|
|
i.register_date,
|
|
i.parent_name,
|
|
p.code,
|
|
p.name,
|
|
CASE p.group_ks
|
|
WHEN '1' THEN 'Towar handlowy'
|
|
WHEN '2' THEN 'Wyrób gotowy'
|
|
WHEN '3' THEN 'Usługi'
|
|
WHEN '530547ef-2dea-7622-843b-59d745b14c64' THEN 'Materiały'
|
|
WHEN '8451dded-710f-51c2-7ed1-60a377eaa7b7' THEN 'Surowce'
|
|
ELSE 'Nieznane'
|
|
END AS group_ks_name,
|
|
GROUP_CONCAT(DISTINCT c.name ORDER BY cb.position SEPARATOR ' | ') AS category,
|
|
ii.quantity,
|
|
ii.price_netto,
|
|
COALESCE(cur.name, 'PLN') AS currency_name
|
|
FROM ecminvoiceouts AS i
|
|
INNER JOIN ecminvoiceoutitems AS ii
|
|
ON i.id = ii.ecminvoiceout_id
|
|
AND ii.deleted = 0
|
|
INNER JOIN ecmproducts AS p
|
|
ON ii.ecmproduct_id = p.id
|
|
AND p.deleted = 0
|
|
LEFT JOIN currencies AS cur
|
|
ON cur.id = i.currency_id
|
|
AND cur.deleted = 0
|
|
LEFT JOIN ecmproductcategories_bean AS cb
|
|
ON cb.bean_id = p.id
|
|
AND cb.bean_name = 'EcmProducts'
|
|
AND cb.deleted = 0
|
|
LEFT JOIN ecmproductcategories AS c
|
|
ON c.id = cb.ecmproductcategory_id
|
|
AND c.deleted = 0
|
|
WHERE i.type = 'normal'
|
|
AND i.register_date BETWEEN '2024-01-01' AND '2024-12-31'
|
|
AND i.deleted = 0
|
|
GROUP BY
|
|
i.id,
|
|
ii.id
|
|
ORDER BY
|
|
i.register_date DESC;
|
|
",
|
|
'filename' => 'invoices_2024.csv',
|
|
], // invoices 2024
|
|
[
|
|
'sql' => "
|
|
SELECT
|
|
i.document_no,
|
|
i.register_date,
|
|
i.parent_name,
|
|
p.code,
|
|
p.name,
|
|
CASE p.group_ks
|
|
WHEN '1' THEN 'Towar handlowy'
|
|
WHEN '2' THEN 'Wyrób gotowy'
|
|
WHEN '3' THEN 'Usługi'
|
|
WHEN '530547ef-2dea-7622-843b-59d745b14c64' THEN 'Materiały'
|
|
WHEN '8451dded-710f-51c2-7ed1-60a377eaa7b7' THEN 'Surowce'
|
|
ELSE 'Nieznane'
|
|
END AS group_ks_name,
|
|
GROUP_CONCAT(DISTINCT c.name ORDER BY cb.position SEPARATOR ' | ') AS category,
|
|
ii.quantity,
|
|
ii.price_netto,
|
|
COALESCE(cur.name, 'PLN') AS currency_name
|
|
FROM ecminvoiceouts AS i
|
|
INNER JOIN ecminvoiceoutitems AS ii
|
|
ON i.id = ii.ecminvoiceout_id
|
|
AND ii.deleted = 0
|
|
INNER JOIN ecmproducts AS p
|
|
ON ii.ecmproduct_id = p.id
|
|
AND p.deleted = 0
|
|
LEFT JOIN currencies AS cur
|
|
ON cur.id = i.currency_id
|
|
AND cur.deleted = 0
|
|
LEFT JOIN ecmproductcategories_bean AS cb
|
|
ON cb.bean_id = p.id
|
|
AND cb.bean_name = 'EcmProducts'
|
|
AND cb.deleted = 0
|
|
LEFT JOIN ecmproductcategories AS c
|
|
ON c.id = cb.ecmproductcategory_id
|
|
AND c.deleted = 0
|
|
WHERE i.type = 'normal'
|
|
AND i.register_date BETWEEN '2025-01-01' AND '2025-12-31'
|
|
AND i.deleted = 0
|
|
GROUP BY
|
|
i.id,
|
|
ii.id
|
|
ORDER BY
|
|
i.register_date DESC;
|
|
",
|
|
'filename' => 'invoices_2025.csv',
|
|
], // invoices 2025
|
|
[
|
|
'sql' => "
|
|
SELECT
|
|
i.document_no,
|
|
i.register_date,
|
|
oi.document_no AS FV,
|
|
oi.register_date AS FV_date,
|
|
i.parent_name,
|
|
p.code,
|
|
p.name,
|
|
CASE p.group_ks
|
|
WHEN '1' THEN 'Towar handlowy'
|
|
WHEN '2' THEN 'Wyrób gotowy'
|
|
WHEN '3' THEN 'Usługi'
|
|
ELSE 'Nieznane'
|
|
END AS group_ks_name,
|
|
GROUP_CONCAT(DISTINCT c.name ORDER BY cb.position SEPARATOR ' | ') AS category,
|
|
ii.quantity_corrected AS quantity_corrected,
|
|
ii.total_netto_corrected AS total_netto_corrected,
|
|
COALESCE(cur.name, 'PLN') AS currency_name
|
|
FROM ecminvoiceouts AS i
|
|
INNER JOIN ecminvoiceoutitems AS ii
|
|
ON i.id = ii.ecminvoiceout_id AND ii.deleted = 0
|
|
INNER JOIN ecmproducts AS p
|
|
ON ii.ecmproduct_id = p.id AND p.deleted = 0
|
|
LEFT JOIN ecminvoiceouts AS oi
|
|
ON oi.id = i.ecminvoiceout_id AND oi.deleted = 0
|
|
LEFT JOIN currencies AS cur
|
|
ON cur.id = i.currency_id AND cur.deleted = 0
|
|
LEFT JOIN ecmproductcategories_bean AS cb
|
|
ON cb.bean_id = p.id
|
|
AND cb.bean_name = 'EcmProducts'
|
|
AND cb.deleted = 0
|
|
LEFT JOIN ecmproductcategories AS c
|
|
ON c.id = cb.ecmproductcategory_id AND c.deleted = 0
|
|
WHERE i.type = 'correct'
|
|
AND i.register_date BETWEEN '2024-01-01' AND '2024-12-31'
|
|
AND i.deleted = 0
|
|
GROUP BY
|
|
i.id,
|
|
ii.id,
|
|
oi.id
|
|
ORDER BY i.register_date DESC;
|
|
",
|
|
'filename' => 'correct_invoices_2024.csv',
|
|
], // correct invoices 2024
|
|
[
|
|
'sql' => "
|
|
SELECT
|
|
i.document_no,
|
|
i.register_date,
|
|
oi.document_no AS FV,
|
|
oi.register_date AS FV_date,
|
|
i.parent_name,
|
|
p.code,
|
|
p.name,
|
|
CASE p.group_ks
|
|
WHEN '1' THEN 'Towar handlowy'
|
|
WHEN '2' THEN 'Wyrób gotowy'
|
|
WHEN '3' THEN 'Usługi'
|
|
ELSE 'Nieznane'
|
|
END AS group_ks_name,
|
|
GROUP_CONCAT(DISTINCT c.name ORDER BY cb.position SEPARATOR ' | ') AS category,
|
|
ii.quantity_corrected AS quantity_corrected,
|
|
ii.total_netto_corrected AS total_netto_corrected,
|
|
COALESCE(cur.name, 'PLN') AS currency_name
|
|
FROM ecminvoiceouts AS i
|
|
INNER JOIN ecminvoiceoutitems AS ii
|
|
ON i.id = ii.ecminvoiceout_id AND ii.deleted = 0
|
|
INNER JOIN ecmproducts AS p
|
|
ON ii.ecmproduct_id = p.id AND p.deleted = 0
|
|
LEFT JOIN ecminvoiceouts AS oi
|
|
ON oi.id = i.ecminvoiceout_id AND oi.deleted = 0
|
|
LEFT JOIN currencies AS cur
|
|
ON cur.id = i.currency_id AND cur.deleted = 0
|
|
LEFT JOIN ecmproductcategories_bean AS cb
|
|
ON cb.bean_id = p.id
|
|
AND cb.bean_name = 'EcmProducts'
|
|
AND cb.deleted = 0
|
|
LEFT JOIN ecmproductcategories AS c
|
|
ON c.id = cb.ecmproductcategory_id AND c.deleted = 0
|
|
WHERE i.type = 'correct'
|
|
AND i.register_date BETWEEN '2025-01-01' AND '2025-12-31'
|
|
AND i.deleted = 0
|
|
GROUP BY
|
|
i.id,
|
|
ii.id,
|
|
oi.id
|
|
ORDER BY i.register_date DESC;
|
|
",
|
|
'filename' => 'correct_invoices_2025.csv',
|
|
], // correct invoices 2025
|
|
[
|
|
'sql' => "
|
|
SELECT
|
|
i.document_no,
|
|
i.register_date,
|
|
i.parent_name,
|
|
p.code,
|
|
p.name,
|
|
CASE p.group_ks
|
|
WHEN '1' THEN 'Towar handlowy'
|
|
WHEN '2' THEN 'Wyrób gotowy'
|
|
WHEN '3' THEN 'Usługi'
|
|
WHEN '530547ef-2dea-7622-843b-59d745b14c64' THEN 'Materiały'
|
|
WHEN '8451dded-710f-51c2-7ed1-60a377eaa7b7' THEN 'Surowce'
|
|
ELSE 'Nieznane'
|
|
END AS group_ks_name,
|
|
pc.category,
|
|
ii.quantity,
|
|
ii.price_netto
|
|
FROM ecommerce_invoices AS i
|
|
INNER JOIN ecommerce_invoices_products AS ii
|
|
ON i.id = ii.invoice_id
|
|
INNER JOIN ecmproducts AS p
|
|
ON ii.ecmproduct_id = p.id
|
|
LEFT JOIN (
|
|
SELECT
|
|
cb.bean_id AS product_id,
|
|
GROUP_CONCAT(DISTINCT c.name ORDER BY cb.position SEPARATOR ' | ') AS category
|
|
FROM ecmproductcategories_bean AS cb
|
|
INNER JOIN ecmproductcategories AS c
|
|
ON c.id = cb.ecmproductcategory_id
|
|
AND c.deleted = 0
|
|
WHERE cb.bean_name = 'EcmProducts'
|
|
AND cb.deleted = 0
|
|
GROUP BY cb.bean_id
|
|
) AS pc
|
|
ON pc.product_id = p.id
|
|
WHERE i.type = 'normal'
|
|
AND i.register_date >= '2024-01-01'
|
|
AND i.register_date < '2025-01-01'
|
|
ORDER BY i.register_date DESC;
|
|
",
|
|
'filename' => 'ecommerce_invoices_2024.csv',
|
|
], // ecommerce invoices 2024
|
|
[
|
|
'sql' => "
|
|
SELECT
|
|
i.document_no,
|
|
i.register_date,
|
|
i.parent_name,
|
|
p.code,
|
|
p.name,
|
|
CASE p.group_ks
|
|
WHEN '1' THEN 'Towar handlowy'
|
|
WHEN '2' THEN 'Wyrób gotowy'
|
|
WHEN '3' THEN 'Usługi'
|
|
WHEN '530547ef-2dea-7622-843b-59d745b14c64' THEN 'Materiały'
|
|
WHEN '8451dded-710f-51c2-7ed1-60a377eaa7b7' THEN 'Surowce'
|
|
ELSE 'Nieznane'
|
|
END AS group_ks_name,
|
|
pc.category,
|
|
ii.quantity,
|
|
ii.price_netto
|
|
FROM ecommerce_invoices AS i
|
|
INNER JOIN ecommerce_invoices_products AS ii
|
|
ON i.id = ii.invoice_id
|
|
INNER JOIN ecmproducts AS p
|
|
ON ii.ecmproduct_id = p.id
|
|
LEFT JOIN (
|
|
SELECT
|
|
cb.bean_id AS product_id,
|
|
GROUP_CONCAT(DISTINCT c.name ORDER BY cb.position SEPARATOR ' | ') AS category
|
|
FROM ecmproductcategories_bean AS cb
|
|
INNER JOIN ecmproductcategories AS c
|
|
ON c.id = cb.ecmproductcategory_id
|
|
AND c.deleted = 0
|
|
WHERE cb.bean_name = 'EcmProducts'
|
|
AND cb.deleted = 0
|
|
GROUP BY cb.bean_id
|
|
) AS pc
|
|
ON pc.product_id = p.id
|
|
WHERE i.type = 'normal'
|
|
AND i.register_date >= '2025-01-01'
|
|
AND i.register_date < '2026-01-01'
|
|
ORDER BY i.register_date DESC;
|
|
",
|
|
'filename' => 'ecommerce_invoices_2025.csv',
|
|
], // ecommerce invoices 2025
|
|
[
|
|
'sql' => "
|
|
SELECT
|
|
ss.product_code,
|
|
ss.product_name,
|
|
COALESCE(NULLIF(ss.quantity, ''), 0) AS quantity,
|
|
s.name
|
|
FROM ecmstockstates AS ss
|
|
JOIN ecmstocks AS s ON ss.stock_id = s.id
|
|
ORDER BY quantity + 0 DESC;",
|
|
'filename' => 'stocks.csv',
|
|
], // stocks
|
|
[
|
|
'sql' => "
|
|
SELECT
|
|
i.document_no,
|
|
i.register_date,
|
|
p.code,
|
|
p.name,
|
|
CASE p.group_ks
|
|
WHEN '1' THEN 'Towar handlowy'
|
|
WHEN '2' THEN 'Wyrób gotowy'
|
|
WHEN '3' THEN 'Surowiec'
|
|
WHEN '4' THEN 'Usługa'
|
|
ELSE 'Nieznane'
|
|
END AS group_ks_name,
|
|
pc.category,
|
|
s.name AS stock,
|
|
ii.quantity
|
|
FROM ecmstockdocinsideouts AS i
|
|
INNER JOIN ecmstockdocinsideoutitems AS ii
|
|
ON i.id = ii.ecmstockdocinsideout_id
|
|
AND ii.deleted = 0
|
|
INNER JOIN ecmproducts AS p
|
|
ON ii.ecmproduct_id = p.id
|
|
AND p.deleted = 0
|
|
INNER JOIN ecmstocks AS s
|
|
ON i.stock_id = s.id
|
|
AND s.deleted = 0
|
|
LEFT JOIN (
|
|
SELECT
|
|
cb.bean_id AS product_id,
|
|
GROUP_CONCAT(DISTINCT c.name ORDER BY cb.position SEPARATOR ' | ') AS category
|
|
FROM ecmproductcategories_bean AS cb
|
|
INNER JOIN ecmproductcategories AS c
|
|
ON c.id = cb.ecmproductcategory_id
|
|
AND c.deleted = 0
|
|
WHERE cb.bean_name = 'EcmProducts'
|
|
AND cb.deleted = 0
|
|
GROUP BY cb.bean_id
|
|
) AS pc
|
|
ON pc.product_id = p.id
|
|
WHERE i.deleted = 0
|
|
AND i.register_date BETWEEN '2025-01-01' AND '2025-12-31'
|
|
ORDER BY i.register_date DESC;
|
|
",
|
|
'filename' => 'rw_2025.csv',
|
|
], // rw 2025
|
|
[
|
|
'sql' => "
|
|
SELECT
|
|
i.document_no,
|
|
i.register_date,
|
|
p.code,
|
|
p.name,
|
|
CASE p.group_ks
|
|
WHEN '1' THEN 'Towar handlowy'
|
|
WHEN '2' THEN 'Wyrób gotowy'
|
|
WHEN '3' THEN 'Surowiec'
|
|
WHEN '4' THEN 'Usługa'
|
|
ELSE 'Nieznane'
|
|
END AS group_ks_name,
|
|
pc.category,
|
|
s.name AS stock,
|
|
ii.quantity
|
|
FROM ecmstockdocinsideouts AS i
|
|
INNER JOIN ecmstockdocinsideoutitems AS ii
|
|
ON i.id = ii.ecmstockdocinsideout_id
|
|
AND ii.deleted = 0
|
|
INNER JOIN ecmproducts AS p
|
|
ON ii.ecmproduct_id = p.id
|
|
AND p.deleted = 0
|
|
INNER JOIN ecmstocks AS s
|
|
ON i.stock_id = s.id
|
|
AND s.deleted = 0
|
|
LEFT JOIN (
|
|
SELECT
|
|
cb.bean_id AS product_id,
|
|
GROUP_CONCAT(DISTINCT c.name ORDER BY cb.position SEPARATOR ' | ') AS category
|
|
FROM ecmproductcategories_bean AS cb
|
|
INNER JOIN ecmproductcategories AS c
|
|
ON c.id = cb.ecmproductcategory_id
|
|
AND c.deleted = 0
|
|
WHERE cb.bean_name = 'EcmProducts'
|
|
AND cb.deleted = 0
|
|
GROUP BY cb.bean_id
|
|
) AS pc
|
|
ON pc.product_id = p.id
|
|
WHERE i.deleted = 0
|
|
AND i.register_date BETWEEN '2024-01-01' AND '2024-12-31'
|
|
ORDER BY i.register_date DESC;
|
|
",
|
|
'filename' => 'rw_2024.csv',
|
|
], // rw 2024
|
|
];
|
|
|
|
$report = [];
|
|
foreach ($jobs as $job) {
|
|
$sql = $job['sql'];
|
|
$filename = $job['filename'];
|
|
$headers = isset($job['headers']) ? $job['headers'] : null;
|
|
|
|
$res = $db->query($sql);
|
|
$fullpath = rtrim($exportDir, "/") . "/" . $filename;
|
|
|
|
$result = exportToCSVFile($res, $fullpath, $headers, ';', true);
|
|
|
|
if ($result['ok']) {
|
|
$report[] = "OK → {$result['path']} (wiersze: {$result['rows']})".PHP_EOL;
|
|
} else {
|
|
$report[] = "ERR → {$result['path']} ({$result['error']})".PHP_EOL;;
|
|
}
|
|
}
|
|
|
|
echo implode("\n", $report);
|
|
exit;
|
|
}
|
|
}
|
|
function exportToCSVFile($res, $fullpath, array $headers = null, $delimiter = ';', $withBom = true)
|
|
{
|
|
$db = $GLOBALS['db'];
|
|
|
|
$dir = dirname($fullpath);
|
|
if (!is_dir($dir)) {
|
|
if (!@mkdir($dir, 0775, true)) {
|
|
return ['ok'=>false, 'path'=>$fullpath, 'rows'=>0, 'error'=>"Nie mogę utworzyć katalogu: $dir"];
|
|
}
|
|
}
|
|
if (!is_writable($dir)) {
|
|
return ['ok'=>false, 'path'=>$fullpath, 'rows'=>0, 'error'=>"Katalog nie jest zapisywalny: $dir"];
|
|
}
|
|
|
|
$fp = @fopen($fullpath, 'w');
|
|
if ($fp === false) {
|
|
return ['ok'=>false, 'path'=>$fullpath, 'rows'=>0, 'error'=>"Nie mogę otworzyć pliku do zapisu: $fullpath"];
|
|
}
|
|
|
|
// BOM dla Excel PL
|
|
if ($withBom) {
|
|
fwrite($fp, "\xEF\xBB\xBF");
|
|
}
|
|
|
|
// pobierz pierwszy wiersz, by ewentualnie zbudować nagłówki
|
|
$first = $db->fetchByAssoc($res);
|
|
|
|
// brak danych → pusty plik z ewentualnym nagłówkiem (jeśli podany ręcznie)
|
|
if (!$first) {
|
|
if ($headers !== null) {
|
|
fputcsv($fp, $headers, $delimiter);
|
|
}
|
|
fclose($fp);
|
|
return ['ok'=>true, 'path'=>$fullpath, 'rows'=>0, 'error'=>null];
|
|
}
|
|
|
|
// dynamiczne nagłówki, jeśli nie podano
|
|
if ($headers === null) {
|
|
$headers = array_keys($first);
|
|
}
|
|
|
|
// wypisz nagłówki
|
|
fputcsv($fp, $headers, $delimiter);
|
|
|
|
// zapisz pierwszy wiersz w kolejności nagłówków
|
|
$line = [];
|
|
foreach ($headers as $h) { $line[] = isset($first[$h]) ? $first[$h] : ''; }
|
|
fputcsv($fp, $line, $delimiter);
|
|
$count = 1;
|
|
|
|
// pozostałe wiersze
|
|
while ($row = $db->fetchByAssoc($res)) {
|
|
$line = [];
|
|
foreach ($headers as $h) { $line[] = isset($row[$h]) ? $row[$h] : ''; }
|
|
fputcsv($fp, $line, $delimiter);
|
|
$count++;
|
|
}
|
|
|
|
fclose($fp);
|
|
return ['ok'=>true, 'path'=>$fullpath, 'rows'=>$count, 'error'=>null];
|
|
} |