Jacek Pyziak
f3be8e1025
- Updated client-edit template to change 'Firma' to 'Nazwa' and added 'Nazwa firmy' input field. - Modified main-view template to reflect the new naming conventions for clients. - Enhanced finances main-view to display a list of clients with revenue details. - Added client selection dropdown in operation-edit template. - Improved work-time template by adding keyboard shortcut for task confirmation. - Introduced CrmController for handling client-related actions. - Created FinancesController to manage finance operations and categories. - Implemented ClientRepository for client data management. - Developed FinanceRepository for finance operations and data handling.
421 lines
16 KiB
PHP
421 lines
16 KiB
PHP
<?php
|
|
namespace Domain\Finances;
|
|
|
|
class FinanceRepository
|
|
{
|
|
private $mdb;
|
|
|
|
public function __construct( $mdb = null )
|
|
{
|
|
if ( $mdb )
|
|
$this -> mdb = $mdb;
|
|
else
|
|
{
|
|
global $mdb;
|
|
$this -> mdb = $mdb;
|
|
}
|
|
}
|
|
|
|
public function firstOperationDate()
|
|
{
|
|
return $this -> mdb -> get( 'finance_operations', 'date', [ 'ORDER' => [ 'date' => 'ASC' ] ] );
|
|
}
|
|
|
|
public function getOperationTags( int $operation_id )
|
|
{
|
|
$return = '';
|
|
$tags_id = $this -> mdb -> select( 'finance_operation_tags', 'tag_id', [ 'operation_id' => $operation_id ] );
|
|
foreach ( $tags_id as $tag_id )
|
|
{
|
|
if ( $return )
|
|
$return .= ', ';
|
|
$return .= $this -> mdb -> get( 'finance_tags', 'tag', [ 'id' => $tag_id ] );
|
|
}
|
|
return $return;
|
|
}
|
|
|
|
public function clientName( $client_id )
|
|
{
|
|
return $this -> mdb -> get( 'crm_client', 'firm', [ 'id' => $client_id ] );
|
|
}
|
|
|
|
public function clientsListByDates( $date_from, $date_to )
|
|
{
|
|
return $this -> mdb -> query(
|
|
'SELECT cc.id, cc.firm FROM crm_client AS cc '
|
|
. 'INNER JOIN finance_operations AS fo ON fo.client_id = cc.id '
|
|
. 'WHERE date >= :date_from AND date <= :date_to '
|
|
. 'GROUP BY cc.id ORDER BY firm ASC',
|
|
[ ':date_from' => $date_from, ':date_to' => $date_to ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
}
|
|
|
|
public function clientsList()
|
|
{
|
|
return $this -> mdb -> select( 'crm_client', [ 'id', 'firm' ], [ 'ORDER' => [ 'firm' => 'ASC' ] ] );
|
|
}
|
|
|
|
public function clientsWithRevenue( $date_from, $date_to, $group_id )
|
|
{
|
|
return $this -> mdb -> query(
|
|
'SELECT cc.id, cc.firm, '
|
|
. 'SUM( CASE WHEN fo.amount > 0 THEN fo.amount ELSE 0 END ) AS income, '
|
|
. 'SUM( CASE WHEN fo.amount < 0 THEN fo.amount ELSE 0 END ) AS costs, '
|
|
. 'SUM( fo.amount ) AS total, '
|
|
. 'COUNT( fo.id ) AS operations_count '
|
|
. 'FROM crm_client AS cc '
|
|
. 'INNER JOIN finance_operations AS fo ON fo.client_id = cc.id '
|
|
. 'INNER JOIN finance_categories AS fc ON fc.id = fo.category_id '
|
|
. 'WHERE fo.date >= :date_from AND fo.date <= :date_to AND fc.group_id = :group_id '
|
|
. 'GROUP BY cc.id '
|
|
. 'ORDER BY total DESC',
|
|
[ ':date_from' => $date_from, ':date_to' => $date_to, ':group_id' => (int)$group_id ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
}
|
|
|
|
public function deleteCategory( $category_id )
|
|
{
|
|
return $this -> mdb -> delete( 'finance_categories', [ 'id' => (int)$category_id ] );
|
|
}
|
|
|
|
public function defaultGroup()
|
|
{
|
|
return $this -> mdb -> get( 'finance_group', 'id', [ 'default_group' => 1 ] );
|
|
}
|
|
|
|
public function groupsList()
|
|
{
|
|
return $this -> mdb -> select( 'finance_group', '*', [ 'ORDER' => [ 'name' => 'ASC' ] ] );
|
|
}
|
|
|
|
public function deleteOperation( $operation_id )
|
|
{
|
|
return $this -> mdb -> delete( 'finance_operations', [ 'id' => $operation_id ] );
|
|
}
|
|
|
|
public function tagsJson( $group_id )
|
|
{
|
|
return $this -> mdb -> select( 'finance_tags', 'tag', [ 'group_id' => $group_id, 'ORDER' => [ 'tag' => 'ASC' ] ] );
|
|
}
|
|
|
|
public function tagsList( $group_id )
|
|
{
|
|
$tags = [];
|
|
$results = $this -> mdb -> select( 'finance_tags', '*', [ 'group_id' => $group_id ] );
|
|
if ( is_array( $results ) and !empty( $results ) ) foreach ( $results as $row )
|
|
{
|
|
$tag = $row;
|
|
$tag['count'] = $this -> mdb -> count( 'finance_operation_tags', [ 'tag_id' => $row['id'] ] );
|
|
$tags[] = $tag;
|
|
}
|
|
|
|
if ( count( $tags ) )
|
|
array_multisort( array_column( $tags, 'count' ), SORT_DESC, $tags );
|
|
|
|
return $tags;
|
|
}
|
|
|
|
public function operationsList( $date_from, $date_to, $group_id, $client_id = null )
|
|
{
|
|
$sql = 'SELECT fo.*, fc.name '
|
|
. 'FROM finance_operations AS fo '
|
|
. 'INNER JOIN finance_categories AS fc ON fc.id = fo.category_id '
|
|
. 'WHERE date >= :date_from AND date <= :date_to AND group_id = :group_id ';
|
|
|
|
$params = [ ':date_from' => $date_from, ':date_to' => $date_to, ':group_id' => (int)$group_id ];
|
|
|
|
if ( $client_id )
|
|
{
|
|
$sql .= 'AND fo.client_id = :client_id ';
|
|
$params[':client_id'] = (int)$client_id;
|
|
}
|
|
|
|
$sql .= 'ORDER BY fo.date DESC, fo.id DESC';
|
|
|
|
return $this -> mdb -> query( $sql, $params ) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
}
|
|
|
|
public function operationDetails( $operation_id )
|
|
{
|
|
$operation = $this -> mdb -> get( 'finance_operations', '*', [ 'id' => $operation_id ] );
|
|
$operation['tags'] = $this -> mdb -> query(
|
|
'SELECT tag, tag_id FROM finance_operation_tags AS fot '
|
|
. 'INNER JOIN finance_tags AS ft ON fot.tag_id = ft.id '
|
|
. 'WHERE operation_id = ' . (int)$operation_id
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
return $operation;
|
|
}
|
|
|
|
public function saveOperation( $operation_id, $category_id, $date, $amount, $description, $tags, $group_id, $client_id = null )
|
|
{
|
|
$data = [
|
|
'date' => $date ? $date : date( 'Y-m-d' ),
|
|
'category_id' => $category_id,
|
|
'amount' => $amount,
|
|
'description' => $description,
|
|
'client_id' => $client_id ? (int)$client_id : null
|
|
];
|
|
|
|
if ( !$operation_id )
|
|
{
|
|
$this -> mdb -> insert( 'finance_operations', $data );
|
|
$id = $this -> mdb -> id();
|
|
}
|
|
else
|
|
{
|
|
$this -> mdb -> update( 'finance_operations', $data, [ 'id' => $operation_id ] );
|
|
$this -> mdb -> delete( 'finance_operation_tags', [ 'operation_id' => $operation_id ] );
|
|
$id = $operation_id;
|
|
}
|
|
|
|
$this -> syncOperationTags( $id, $tags, $group_id );
|
|
return $id;
|
|
}
|
|
|
|
private function syncOperationTags( $operation_id, $tags_string, $group_id )
|
|
{
|
|
$tags = explode( ',', $tags_string );
|
|
if ( !is_array( $tags ) )
|
|
return;
|
|
|
|
foreach ( $tags as $tag )
|
|
{
|
|
$tag = trim( $tag );
|
|
if ( !$tag )
|
|
continue;
|
|
|
|
$tag_id = $this -> mdb -> get( 'finance_tags', 'id', [ 'AND' => [ 'group_id' => $group_id, 'tag' => $tag ] ] );
|
|
if ( !$tag_id )
|
|
{
|
|
$this -> mdb -> insert( 'finance_tags', [ 'tag' => $tag, 'group_id' => $group_id ] );
|
|
$tag_id = $this -> mdb -> id();
|
|
}
|
|
$this -> mdb -> insert( 'finance_operation_tags', [ 'operation_id' => $operation_id, 'tag_id' => $tag_id ] );
|
|
}
|
|
}
|
|
|
|
public function categoryDetails( $category_id )
|
|
{
|
|
return $this -> mdb -> get( 'finance_categories', '*', [ 'id' => $category_id ] );
|
|
}
|
|
|
|
public function saveCategory( $category_id, $name, $parent_id, $group_id )
|
|
{
|
|
if ( !$category_id )
|
|
{
|
|
$this -> mdb -> insert( 'finance_categories', [
|
|
'name' => $name,
|
|
'parent_id' => $parent_id ? $parent_id : null,
|
|
'group_id' => $group_id
|
|
] );
|
|
return $this -> mdb -> id();
|
|
}
|
|
else
|
|
{
|
|
$this -> mdb -> update( 'finance_categories', [
|
|
'name' => $name,
|
|
'group_id' => $group_id
|
|
], [ 'id' => $category_id ] );
|
|
return $category_id;
|
|
}
|
|
}
|
|
|
|
public function walletSummary( $group_id )
|
|
{
|
|
$results = $this -> mdb -> query(
|
|
'SELECT SUM(amount) AS val FROM finance_operations '
|
|
. 'WHERE category_id IN ( SELECT id FROM finance_categories WHERE group_id = :group_id )',
|
|
[ ':group_id' => (int)$group_id ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
return $results[0]['val'];
|
|
}
|
|
|
|
public function walletSummaryThisMonth( $group_id )
|
|
{
|
|
$date_from = date( 'Y-m-01' );
|
|
$date_to = date( 'Y-m-t' );
|
|
|
|
$results = $this -> mdb -> query(
|
|
'SELECT SUM(amount) AS val FROM finance_operations '
|
|
. 'WHERE category_id IN ( SELECT id FROM finance_categories WHERE group_id = :group_id ) '
|
|
. 'AND date >= :date_from AND date <= :date_to',
|
|
[ ':group_id' => (int)$group_id, ':date_from' => $date_from, ':date_to' => $date_to ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
return $results[0]['val'];
|
|
}
|
|
|
|
public function walletIncomeThisMonth( $group_id )
|
|
{
|
|
$date_from = date( 'Y-m-01' );
|
|
$date_to = date( 'Y-m-t' );
|
|
|
|
$results = $this -> mdb -> query(
|
|
'SELECT SUM(amount) AS val FROM finance_operations '
|
|
. 'WHERE category_id IN ( SELECT id FROM finance_categories WHERE group_id = :group_id ) '
|
|
. 'AND date >= :date_from AND date <= :date_to AND amount > 0',
|
|
[ ':group_id' => (int)$group_id, ':date_from' => $date_from, ':date_to' => $date_to ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
return $results[0]['val'];
|
|
}
|
|
|
|
public function walletExpensesThisMonth( $group_id )
|
|
{
|
|
$date_from = date( 'Y-m-01' );
|
|
$date_to = date( 'Y-m-t' );
|
|
|
|
$results = $this -> mdb -> query(
|
|
'SELECT SUM(amount) AS val FROM finance_operations '
|
|
. 'WHERE category_id IN ( SELECT id FROM finance_categories WHERE group_id = :group_id ) '
|
|
. 'AND date >= :date_from AND date <= :date_to AND amount < 0',
|
|
[ ':group_id' => (int)$group_id, ':date_from' => $date_from, ':date_to' => $date_to ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
return $results[0]['val'];
|
|
}
|
|
|
|
public function operations( $category_id, $date_from, $date_to, $tag_id = null )
|
|
{
|
|
$operations = [];
|
|
|
|
if ( $tag_id )
|
|
$results = $this -> mdb -> query(
|
|
'SELECT fo.* FROM finance_operations AS fo '
|
|
. 'INNER JOIN finance_operation_tags AS fot ON fot.operation_id = fo.id '
|
|
. 'WHERE category_id = :category_id AND date >= :date_from AND date <= :date_to AND tag_id = :tag_id '
|
|
. 'ORDER BY date DESC, id DESC',
|
|
[ ':category_id' => (int)$category_id, ':date_from' => $date_from, ':date_to' => $date_to, ':tag_id' => (int)$tag_id ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
else
|
|
$results = $this -> mdb -> query(
|
|
'SELECT fo.* FROM finance_operations AS fo '
|
|
. 'WHERE category_id = :category_id AND date >= :date_from AND date <= :date_to '
|
|
. 'ORDER BY date DESC, fo.id DESC',
|
|
[ ':category_id' => (int)$category_id, ':date_from' => $date_from, ':date_to' => $date_to ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
if ( is_array( $results ) ) foreach ( $results as $row )
|
|
{
|
|
$row['tags'] = $this -> mdb -> query(
|
|
'SELECT tag FROM finance_operation_tags AS fot '
|
|
. 'INNER JOIN finance_tags AS ft ON fot.tag_id = ft.id '
|
|
. 'WHERE operation_id = ' . (int)$row['id']
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
$operations[] = $row;
|
|
}
|
|
|
|
return $operations;
|
|
}
|
|
|
|
public function categories( $date_from, $date_to, $tag_id = '', $parent_id = null, $group_id, $client_id = null )
|
|
{
|
|
$categories = [];
|
|
$results = $this -> mdb -> select( 'finance_categories', [ 'id', 'name' ], [
|
|
'AND' => [ 'group_id' => $group_id, 'parent_id' => $parent_id ],
|
|
'ORDER' => [ 'name' => 'ASC' ]
|
|
] );
|
|
|
|
if ( !is_array( $results ) or empty( $results ) )
|
|
return $categories;
|
|
|
|
foreach ( $results as $row )
|
|
{
|
|
if ( $tag_id )
|
|
{
|
|
$row['costs'] = (double)$this -> categorySumByTag( $row['id'], $tag_id, $group_id, $date_from, $date_to, '< 0' );
|
|
$row['costs_count'] = (int)$this -> categoryCountByTag( $row['id'], $tag_id, $group_id, $date_from, $date_to, '< 0' );
|
|
$row['income'] = (double)$this -> categorySumByTag( $row['id'], $tag_id, $group_id, $date_from, $date_to, '> 0' );
|
|
$row['income_count'] = (int)$this -> categoryCountByTag( $row['id'], $tag_id, $group_id, $date_from, $date_to, '> 0' );
|
|
}
|
|
elseif ( $client_id )
|
|
{
|
|
$row['costs'] = (double)$this -> categorySumByClient( $row['id'], $client_id, $date_from, $date_to, '< 0' );
|
|
$row['costs_count'] = (int)$this -> categoryCountByClient( $row['id'], $client_id, $date_from, $date_to, '< 0' );
|
|
$row['income'] = (double)$this -> categorySumByClient( $row['id'], $client_id, $date_from, $date_to, '> 0' );
|
|
$row['income_count'] = (int)$this -> categoryCountByClient( $row['id'], $client_id, $date_from, $date_to, '> 0' );
|
|
}
|
|
else
|
|
{
|
|
$row['costs'] = (double)$this -> categorySum( $row['id'], $date_from, $date_to, '< 0' );
|
|
$row['costs_count'] = (int)$this -> categoryCount( $row['id'], $date_from, $date_to, '< 0' );
|
|
$row['income'] = (double)$this -> categorySum( $row['id'], $date_from, $date_to, '> 1' );
|
|
$row['income_count'] = (int)$this -> categoryCount( $row['id'], $date_from, $date_to, '> 1' );
|
|
}
|
|
|
|
$row['subcategories'] = $this -> categories( $date_from, $date_to, $tag_id, $row['id'], $group_id, $client_id );
|
|
$categories[] = $row;
|
|
}
|
|
|
|
return $categories;
|
|
}
|
|
|
|
private function categorySum( $category_id, $date_from, $date_to, $amount_condition )
|
|
{
|
|
$results = $this -> mdb -> query(
|
|
'SELECT SUM(amount) FROM finance_operations '
|
|
. 'WHERE category_id = :category_id AND date >= :date_from AND date <= :date_to AND amount ' . $amount_condition,
|
|
[ ':category_id' => (int)$category_id, ':date_from' => $date_from, ':date_to' => $date_to ]
|
|
) -> fetchAll();
|
|
return $results[0][0];
|
|
}
|
|
|
|
private function categoryCount( $category_id, $date_from, $date_to, $amount_condition )
|
|
{
|
|
$results = $this -> mdb -> query(
|
|
'SELECT COUNT(0) FROM finance_operations '
|
|
. 'WHERE category_id = :category_id AND date >= :date_from AND date <= :date_to AND amount ' . $amount_condition,
|
|
[ ':category_id' => (int)$category_id, ':date_from' => $date_from, ':date_to' => $date_to ]
|
|
) -> fetchAll();
|
|
return $results[0][0];
|
|
}
|
|
|
|
private function categorySumByTag( $category_id, $tag_id, $group_id, $date_from, $date_to, $amount_condition )
|
|
{
|
|
$results = $this -> mdb -> query(
|
|
'SELECT SUM(amount) FROM finance_operations AS fo '
|
|
. 'INNER JOIN finance_categories AS fc ON fc.id = fo.category_id '
|
|
. 'INNER JOIN finance_operation_tags AS fot ON fot.operation_id = fo.id '
|
|
. 'INNER JOIN finance_tags AS ft ON ft.id = fot.tag_id '
|
|
. 'WHERE category_id = :category_id AND tag_id = :tag_id AND ft.group_id = :group_id '
|
|
. 'AND date >= :date_from AND date <= :date_to AND amount ' . $amount_condition,
|
|
[ ':category_id' => (int)$category_id, ':tag_id' => (int)$tag_id, ':group_id' => (int)$group_id, ':date_from' => $date_from, ':date_to' => $date_to ]
|
|
) -> fetchAll();
|
|
return $results[0][0];
|
|
}
|
|
|
|
private function categoryCountByTag( $category_id, $tag_id, $group_id, $date_from, $date_to, $amount_condition )
|
|
{
|
|
$results = $this -> mdb -> query(
|
|
'SELECT COUNT(fo.id) FROM finance_operations AS fo '
|
|
. 'INNER JOIN finance_categories AS fc ON fc.id = fo.category_id '
|
|
. 'INNER JOIN finance_operation_tags AS fot ON fot.operation_id = fo.id '
|
|
. 'INNER JOIN finance_tags AS ft ON ft.id = fot.tag_id '
|
|
. 'WHERE category_id = :category_id AND tag_id = :tag_id AND ft.group_id = :group_id '
|
|
. 'AND date >= :date_from AND date <= :date_to AND amount ' . $amount_condition,
|
|
[ ':category_id' => (int)$category_id, ':tag_id' => (int)$tag_id, ':group_id' => (int)$group_id, ':date_from' => $date_from, ':date_to' => $date_to ]
|
|
) -> fetchAll();
|
|
return $results[0][0];
|
|
}
|
|
|
|
private function categorySumByClient( $category_id, $client_id, $date_from, $date_to, $amount_condition )
|
|
{
|
|
$results = $this -> mdb -> query(
|
|
'SELECT SUM(amount) FROM finance_operations '
|
|
. 'WHERE category_id = :category_id AND client_id = :client_id '
|
|
. 'AND date >= :date_from AND date <= :date_to AND amount ' . $amount_condition,
|
|
[ ':category_id' => (int)$category_id, ':client_id' => (int)$client_id, ':date_from' => $date_from, ':date_to' => $date_to ]
|
|
) -> fetchAll();
|
|
return $results[0][0];
|
|
}
|
|
|
|
private function categoryCountByClient( $category_id, $client_id, $date_from, $date_to, $amount_condition )
|
|
{
|
|
$results = $this -> mdb -> query(
|
|
'SELECT COUNT(0) FROM finance_operations '
|
|
. 'WHERE category_id = :category_id AND client_id = :client_id '
|
|
. 'AND date >= :date_from AND date <= :date_to AND amount ' . $amount_condition,
|
|
[ ':category_id' => (int)$category_id, ':client_id' => (int)$client_id, ':date_from' => $date_from, ':date_to' => $date_to ]
|
|
) -> fetchAll();
|
|
return $results[0][0];
|
|
}
|
|
}
|