<?php
namespace App\Repository;
use App\Entity\Sales;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\DBAL\Exception;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\Persistence\ManagerRegistry;
/**
* @extends ServiceEntityRepository<Sales>
*
* @method Sales|null find($id, $lockMode = null, $lockVersion = null)
* @method Sales|null findOneBy(array $criteria, array $orderBy = null)
* @method Sales[] findAll()
* @method Sales[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class SalesRepository extends ServiceEntityRepository
{
private $em;
public function __construct(ManagerRegistry $registry, EntityManagerInterface $em)
{
parent::__construct($registry, Sales::class);
$this->em = $em;
}
public function add(Sales $entity, bool $flush = false): void
{
$this->getEntityManager()->persist($entity);
if ($flush) {
$this->getEntityManager()->flush();
}
}
public function remove(Sales $entity, bool $flush = false): void
{
$this->getEntityManager()->remove($entity);
if ($flush) {
$this->getEntityManager()->flush();
}
}
public function getSalesList(array $dataReceive, string $search = null, int $start = 0, int $length = -1, $order = array())
{
$columns = array(
0 => 'DATE',
1 => 'TIME',
2 => 'AMOUNT',
3 => 'USERNAME',
4 => 'ID',
5 => 'SHOP'
);
$sql = "select
s.id as ID,
s.date as 'DATE',
s.time as 'TIME',
s.amount as AMOUNT,
u.username as USERNAME,
p.label as SHOP,
s.bon as BON
from sales s
left join user u on (u.id = s.user_id)
left join shop p on (p.id = s.shop_id)
WHERE s.deleted != 1
";
$sqlCount = "select count(distinct s.id)
from sales s
left join user u on (u.id = s.user_id)
left join shop p on (p.id = s.shop_id)
WHERE s.deleted != 1";
//apply search if requested
if ($search != null) {
$conditions = ' AND (DATE_FORMAT(s.date, "%d/%m/%Y") like "%' . $search . '%" OR u.username like "%' . $search . '%" OR s.bon like "%' . $search . '%") ';
$sql = $sql . $conditions;
$sqlCount = $sqlCount . $conditions;
}
//apply order on date
$sql = $sql . ' ORDER BY s.date desc' ;
if ($length != -1) {
$sql = $sql . ' LIMIT ' . $length . ' OFFSET ' . $start;
}
$countFilter = 0;
$queryBuilderCount = $this->em->getConnection()->prepare($sqlCount);
$countFilter = $queryBuilderCount->executeQuery()->fetchOne();
$stmt = $this->em->getConnection()->prepare($sql);
$result = $stmt->executeQuery()->fetchAllAssociative();
return array(
'data' => $result,
'filtre' => $countFilter
);
}
/**
* @throws Exception
*/
public function getJournalList(array $dataReceive, string $search = null, int $start = 0, int $length = -1, array $order = array())
{
$columns = array(
0 => 'date',
1 => 'nbr_ventes',
2 => 'montant'
);
$userFilter = $dataReceive[2] != "all" ? " and s.user_id = ". intval($dataReceive[2]) : "";
$sql = "select
date_format(s.date, '%d %b %Y') as date,
count(distinct s.id) as nbr_ventes,
COALESCE(SUM(si.amount), 0) + COALESCE(SUM(sp.amount), 0) AS montant
from sales s
left join sale_items si on s.id = si.sale_id
left join sale_packs sp on s.id = sp.sale_id
join shop s2 on (s2.id = s.shop_id)
where s.deleted != 1 and s.error != 1 and s2.id = " . $dataReceive[0] . "
and date_format(s.date, '%m%Y') = " . $dataReceive[1] . $userFilter ;
$sqlCount = "select count(distinct date_format(s.date, '%d %b %Y'))
from sales s
left join sale_items si on s.id = si.sale_id
left join sale_packs sp on s.id = sp.sale_id
join shop s2 on (s2.id = s.shop_id)
where s.deleted != 1 and s.error != 1 and s2.id = " . $dataReceive[0] . "
and date_format(s.date, '%m%Y') = " . $dataReceive[1] . $userFilter ;
//apply search if requested
if ($search != null) {
$conditions = ' AND (s2.label like "%' . $search . '%")';
$sql = $sql . $conditions;
$sqlCount = $sqlCount . $conditions;
}
$sql .= ' group by date_format(s.date, \'%d %b %Y\')';
//apply order if requested
if ($length == -1) {
$sql = $sql . ' ORDER BY ' . $columns[$order[0]['column']] . ' ' . $order[0]['dir'];
} else {
$sql = $sql . ' ORDER BY ' . $columns[$order[0]['column']] . ' ' . $order[0]['dir'] . ' LIMIT ' . $length . ' OFFSET ' . $start;
}
$countFilter = 0;
$queryBuilderCount = $this->em->getConnection()->prepare($sqlCount);
$countFilter = $queryBuilderCount->executeQuery()->fetchOne();
$stmt = $this->em->getConnection()->prepare($sql);
$result = $stmt->executeQuery()->fetchAllAssociative();
return array(
'data' => $result,
'filtre' => $countFilter
);
}
/**
* @throws Exception
*/
public function getSalesDetailsList($dataReceive=[], int $start = 0, int $length = -1, array $order = array())
{
$columns = array(
0 => 'mois',
1 => 'nb_vente',
2 => 'ca',
3 => 'couts',
4 => 'benef'
);
$shopFilter = '';
$today = new \DateTime();
$year = $today->format('Y');
if($dataReceive != []){
$year = $dataReceive[0];
$shopFilter .= isset($dataReceive[1]) ? ' and s.shop_id ='. $dataReceive[1] : '';
}
$sql = "
select
date_format(s.`date` ,'%m') as mois
, count(distinct s.id) as nb_vente
, COALESCE(SUM(si.amount), 0) + COALESCE(SUM(sp.amount), 0) AS ca
, COALESCE(SUM(si.costs), 0) + COALESCE(SUM(sp.costs), 0) AS couts
, COALESCE(SUM(si.benef), 0) + COALESCE(SUM(sp.benef), 0) AS benef
from sales s
left join sale_items si on s.id = si.sale_id
left join sale_packs sp on s.id = sp.sale_id
where s.deleted != 1
-- and si.costs is not null
-- and si.count < 5000
and date_format(s.`date` , '%Y') = " . $year . $shopFilter . "
group by mois
";
//apply search if requested
// if ($search != null) {
// $conditions = ' AND (s2.label like "%'.$search.'%")';
// $sql = $sql . $conditions;
// }
//apply order if requested
if ($length == -1) {
$sql = $sql . ' ORDER BY ' . $columns[$order[0]['column']] . ' ' . $order[0]['dir'];
} else {
$sql = $sql . ' ORDER BY ' . $columns[$order[0]['column']] . ' ' . $order[0]['dir'] . ' LIMIT ' . $length . ' OFFSET ' . $start;
}
$stmt = $this->em->getConnection()->prepare($sql);
$result = $stmt->executeQuery()->fetchAllAssociative();
return array(
'data' => $result,
'filtre' => 0
);
}
/**
* @throws Exception
*/
public function getTotalYear($selectedShop, $year)
{
$shopFilter = '';
if($selectedShop != null){
$shopFilter .= ' and s.shop_id ='. $selectedShop;
}
$sql = "select count(distinct s.ID)
from sales s
where s.deleted != 1 and date_format(s.`date` , '%Y') = " . $year . $shopFilter;
$stmt = $this->em->getConnection()->prepare($sql);
return $stmt->executeQuery()->fetchOne();
}
/**
* @throws Exception
*/
public function getTotalWeek($dateFilter = null, $selectedShop = null)
{
$shopFilter = '';
if($selectedShop != null){
$shopFilter .= ' and s.shop_id ='. $selectedShop;
}
$datetime = new \DateTime();
if ($dateFilter != null) {
$datetime = $datetime->createFromFormat('Y-m-d', $dateFilter);
}
$date = $datetime->format('Y-m-d');
$week = strftime("%U", strtotime($date));
$year = $datetime->format('Y');
$sql = "select count(distinct s.ID)
from sales s
where date_format(s.`date` , '%Y') = ". $year ." and date_format(s.`date` , '%U') = " . $week . $shopFilter;
$stmt = $this->em->getConnection()->prepare($sql);
return $stmt->executeQuery()->fetchOne();
}
/**
* @throws Exception
*/
public function getNumbersByMonth($selectedShop, $year): array
{
$shopFilter = '';
if($selectedShop != null){
$shopFilter .= ' and s.shop_id ='. $selectedShop;
}
$sql = "select
date_format(s.`date` ,'%m') as mois
,COALESCE(SUM(si.amount), 0) + COALESCE(SUM(sp.amount), 0) AS ca
, COALESCE(SUM(si.benef), 0) + COALESCE(SUM(sp.benef), 0) AS benef
from sales s
left join sale_items si on s.id = si.sale_id
left join sale_packs sp on s.id = sp.sale_id
where s.deleted != 1
and date_format(s.`date` , '%Y') = " . $year . $shopFilter . "
group by mois
ORDER BY mois asc;
";
$stmt = $this->em->getConnection()->prepare($sql);
return $stmt->executeQuery()->fetchAllAssociative();
}
/**
* @throws Exception
*/
public function getNumbersByDay($dateFilter = null, $selectedShop = null): array
{
$shopFilter = '';
if($selectedShop != null){
$shopFilter .= ' and s.shop_id ='. $selectedShop;
}
$datetime = new \DateTime();
if ($dateFilter != null) {
$datetime = $datetime->createFromFormat('Y-m-d', $dateFilter);
}
$date = $datetime->format('Y-m-d');
$week = strftime("%U", strtotime($date));
$year = $datetime->format('Y');
$sql = "
select
date_format(s.`date` ,'%w') as day
, COALESCE(SUM(si.amount), 0) + COALESCE(SUM(sp.amount), 0) AS ca
, COALESCE(SUM(si.benef), 0) + COALESCE(SUM(sp.benef), 0) AS benef
from sales s
left join sale_items si on s.id = si.sale_id
left join sale_packs sp on s.id = sp.sale_id
where s.deleted != 1
and date_format(s.`date` , '%Y') = " . $year . " and date_format(s.`date` , '%U') = " . $week . $shopFilter . "
group by day
";
$stmt = $this->em->getConnection()->prepare($sql);
return $stmt->executeQuery()->fetchAllAssociative();
}
/**
* @throws Exception
*/
public function getNumbersByCat($selectedShop, $year): array
{
$shopFilter = '';
if($selectedShop != null){
$shopFilter .= ' and s.shop_id ='. $selectedShop;
}
$sql = "
select
ifnull( c.label , 'Inconnue') as cat
,COALESCE(SUM(si.amount), 0) + COALESCE(SUM(sp.amount), 0) AS nb_vente
from sales s
left join sale_items si on s.id = si.sale_id
left join sale_packs sp on s.id = sp.sale_id
left join product p on p.id = si.product_id
left join category c on c.id = p.category_id
where s.deleted != 1
-- and si.costs is not null
-- and si.count < 5000
and date_format(s.`date` , '%Y') = " . $year . $shopFilter . "
group by c.id
";
$stmt = $this->em->getConnection()->prepare($sql);
return $stmt->executeQuery()->fetchAllAssociative();
}
/**
* @throws Exception
*/
public function getTrafficByDay($selectedShop = null): array
{
$today = new \DateTime();
$month = $today->format('m');
$shopFilter = '';
if($selectedShop != null){
$shopFilter .= ' and s.shop_id ='. $selectedShop;
}
$sql = "select
req.day,
sum(req.cat1) as 8_12,
sum(req.cat2) as 12_16,
sum(req.cat3) as 16_20,
sum(req.cat4) as 20_00
from(
select s.id as vente, DAYOFWEEK(s.date) as day, s.shop_id as shop,
if (date_format(s.`date`, '%H') >= 8 and date_format(s.`date`, '%H') < 12 , 1 , 0 ) as cat1,
if (date_format(s.`date`, '%H') >= 12 and date_format(s.`date`, '%H') < 16 , 1 , 0 ) as cat2,
if (date_format(s.`date`, '%H') >= 16 and date_format(s.`date`, '%H') < 20 , 1 , 0 ) as cat3,
if (date_format(s.`date`, '%H') >= 20 and date_format(s.`date`, '%H') <= 23 , 1 , 0 ) as cat4
from sales s
where s.deleted != 1 and date_format(s.`date` , '%m') = " . $month . $shopFilter . "
)req
group by day;
";
$stmt = $this->em->getConnection()->prepare($sql);
return $stmt->executeQuery()->fetchAllAssociative();
}
// /**
// * @return Sales[] Returns an array of Sales objects
// */
// public function findByExampleField($value): array
// {
// return $this->createQueryBuilder('s')
// ->andWhere('s.exampleField = :val')
// ->setParameter('val', $value)
// ->orderBy('s.id', 'ASC')
// ->setMaxResults(10)
// ->getQuery()
// ->getResult()
// ;
// }
// public function findOneBySomeField($value): ?Sales
// {
// return $this->createQueryBuilder('s')
// ->andWhere('s.exampleField = :val')
// ->setParameter('val', $value)
// ->getQuery()
// ->getOneOrNullResult()
// ;
// }
}