src/Diplix/KMGBundle/Repository/OrderRepository.php line 107

Open in your IDE?
  1. <?php
  2. namespace Diplix\KMGBundle\Repository;
  5. use Diplix\KMGBundle\Controller\Rating\RatingController;
  6. use Diplix\KMGBundle\Entity\Accounting\CoopMember;
  7. use Diplix\KMGBundle\Entity\Order;
  8. use Diplix\KMGBundle\Entity\OrderStatus;
  9. use Diplix\KMGBundle\Entity\Rating;
  10. use Diplix\KMGBundle\Entity\User;
  11. use Doctrine\ORM\AbstractQuery;
  12. use Doctrine\ORM\QueryBuilder;
  13. use PDO;
  15. class OrderRepository extends BasicRepository
  16. {
  17.     public function findHistoryFor(Order $o)
  18.     {
  19.         $st = $this->dbQuery("SELECT A.*,B.username, B.first_name,B.last_name
  20.                                   FROM syslog A
  21.                                   LEFT JOIN users B ON B.id = A.user_id
  22.                                   WHERE ref_entity = :class
  23.                                     AND ref_id = :id
  24.                                   ORDER BY log_time DESC
  25.                                   LIMIT 0,20",
  26.                     array("class"=>get_class($o),"id"=>$o->getId()));
  27.         $res = $st->fetchAllAssociative();
  28.         if (count($res)>0)
  29.         for ($i=0; $i < count($res); $i++)
  30.         {
  31.             $res[$i]["log_time"] = new \DateTime($res[$i]["log_time"]);
  32.             if ($res[$i]["changeset"]!="[]") $res[$i]["message"] .= $res[$i]["changeset"];
  33.         }
  34.         return $res;
  35.     }
  37.     public function countByDay($from,$to,$userList = [], $customerId=0)
  38.     {
  39.         $sql = "SELECT DATE_FORMAT(order_time,'%Y-%m-%d') AS day, COUNT(*) AS cnt
  40.                                   FROM orders
  41.                                   WHERE order_time >= :from AND order_time <= :to
  42.                                   ";
  43.         $params = array("from"=>$from->format("Y-m-d"),"to"=>$to->format("Y-m-d"));
  44.         if ($customerId > 0)
  45.         {
  46.             $sql .= " AND customer_id = :cid";
  47.             $params["cid"] = $customerId;
  48.         }
  49.         if ($userList != null )
  50.         {
  51.             if (count($userList)<1) throw new \Exception("No userlist provided.");
  52.             $sql .= "AND be_owner IN (:userlist)";
  53.             $params["userlist"] = implode(",",array_map(function($u){return (int)$u->getId();},$userList));
  54.         }
  55.         $sql .= "                                                                   
  56.                                   GROUP BY day
  57.                                   ORDER BY day ASC";
  58.         $st = $this->dbQuery($sql,$params);
  59.         $list = $st->fetchAllAssociative();
  60.         $ret = [];
  61.         foreach ($list as $l)
  62.         {
  63.             $ret[$l["day"]] = $l["cnt"];
  64.         }
  65.         return $ret;
  66.     }
  68.     public function findForTamiStatusUpdate()
  69.     {
  70.         $qb = $this->createQueryBuilder("A")
  71.             ->select("A,B,C,S")
  72.             ->leftJoin("A.customer","B")
  73.             ->leftJoin("A.beOwner","C")
  74.             ->leftJoin("A.orderStatus","S")
  75.             ->where("A.remoteStatus = :rs")
  76.             ->setParameter("rs",Order::REMOTE_SUCCESS)
  77.             ->andWhere("A.orderStatus < 3"); // 1==offen,2==vermittelt,3==erledigt, > 3 = fehler/storno/...
  78.         return $qb->getQuery()->getResult(AbstractQuery::HYDRATE_OBJECT);
  79.     }
  81.     /** @return QueryBuilder */
  82.     public function getQb(): QueryBuilder
  83.     {
  84.         return $this->createQueryBuilder("A")
  85.             ->select("A,B,C,S,D,R,P,M,X,J")
  86.             ->leftJoin("A.customer","B")
  87.             ->leftJoin("A.beOwner","C")
  88.             ->leftJoin("A.orderStatus","S")
  89.             ->leftJoin('A.paymentType','P')
  90.             ->leftJoin("A.addressList","D")
  91.             ->leftJoin("A.referencedParentOrder","R")
  92.             ->leftJoin('A.assignedTo','M')
  93.             ->leftJoin("A.xchgTo","X")
  94.             ->leftJoin("A.job","J");
  95.     }
  97.     /**
  98.      * @param array $userList
  99.      * @param int $customerId
  100.      * @param $filterStatus
  101.      * @param int $from
  102.      * @param int $to
  103.      * @param int $assignedTo
  104.      * @return array
  105.      * @throws \Exception
  106.      */
  107.     public function findAllForOverview($userList = array(), $customerId=0, $filterStatus,$from=0, $to=0,$assignedTo=0)
  108.     {
  109.         $qb = $this->getQb();
  111.         if ($userList != null )
  112.         {
  113.             if (count($userList)<1) throw new \Exception("No userlist provided.");
  114.             $or = $qb->expr()->orX();
  115.             foreach ($userList as $u)
  116.             {
  117.                 $or->add(sprintf("A.beOwner = '%d'",$u->getId()));
  118.             }
  119.             $qb->where($or);
  120.         }
  121.         if ($customerId > 0)
  122.         {
  123.             $qb->andWhere("B.id = :cid")
  124.                 ->setParameter("cid",$customerId);
  125.         }
  126.         if ($assignedTo > 0)
  127.         {
  128.             $qb->andWhere("A.assignedTo = :mm")
  129.                 ->setParameter('mm',$assignedTo);
  130.         }
  132.         if (is_array($filterStatus))
  133.         {
  134.             $qb->andWhere( $qb->expr()->in('A.orderStatus',':hiddenStati'))
  135.                 ->setParameter('hiddenStati',$filterStatus);
  136.         }
  137.         else
  138.         if ($filterStatus>0)
  139.         {
  140.             $qb->andWhere("A.orderStatus = :os")
  141.                 ->setParameter("os",$filterStatus);
  142.         }
  144.         if (is_object($from))
  145.         {
  146.             $qb->andWhere("A.orderTime >= :start")
  147.                 ->setParameter("start",$from);
  148.         }
  149.         if (is_object($to))
  150.         {
  151.             $qb->andWhere("A.orderTime <= :to")
  152.                 ->setParameter("to",$to);
  153.         }
  154.         $qb->orderBy("A.orderTime");
  155.         return $qb->getQuery()->getResult(AbstractQuery::HYDRATE_ARRAY);
  156.     }
  158.     public function getSingleAsArray($id)
  159.     {
  160.         $qb = $this->getQb();
  161.         $qb->andWhere('A.id = :id')->setParameter('id',$id);
  162.         return $qb->getQuery()->getSingleResult(AbstractQuery::HYDRATE_ARRAY);
  163.     }
  165.     public function getPendingInstantOrdersForDispo($hydrationMode = AbstractQuery::HYDRATE_ARRAY)
  166.     {
  167.         $limit = new \DateTime();
  168.         $limit->modify('-7 days');
  169.         $qb = $this->getQb();
  170.         $qb->where(
  171.                 $qb->expr()->orX(
  172.                     $qb->expr()->eq('A.instantOrderStatus',Order::INSTANT_ORDER_REQUEST_PENDING),
  173.                     $qb->expr()->andX(
  174.                         $qb->expr()->gte('A.instantOrderStatus',Order::INSTANT_ORDER_REQUEST_PENDING),
  175.                         $qb->expr()->gte('A.instantOrderInitiated',':timelimit')
  176.                     )
  177.                 )
  178.             )
  179.             ->setParameter('timelimit',$limit->format('Y-m-d H:i'))
  180.             ->orderBy('A.instantOrderInitiated','desc');
  181.         return $qb->getQuery()->getResult($hydrationMode);
  182.     }
  184.     public function getPendingInstantOrder(User $forUser, $hydrationMode = AbstractQuery::HYDRATE_ARRAY)
  185.     {
  186.         $limit = new \DateTime();
  187.         $limit->modify('-7 days');
  188.         $qb = $this->getQb();
  189.         $qb->andWhere('A.beOwner = :owner')
  190.             ->andWhere(
  191.                 $qb->expr()->orX(
  192.                     $qb->expr()->eq('S.id',OrderStatus::STATUS_INSTANT_ORDER_PENDING),
  193.                     $qb->expr()->andX(
  194.                         $qb->expr()->gte('A.instantOrderStatus',Order::INSTANT_ORDER_REQUEST_PENDING),
  195.                         $qb->expr()->gte('A.instantOrderInitiated',':timelimit')
  196.                    )
  197.                 )
  198.             )
  199.             ->setParameter('owner',$forUser->getId())
  200.             ->setParameter('timelimit',$limit->format('Y-m-d H:i'))
  201.             ->orderBy('A.instantOrderInitiated','desc');
  202.         return $qb->getQuery()->getResult($hydrationMode);
  203.     }
  206.     /**
  207.      * Find all Orders for customers with showRating=1 which are not yet rated
  208.      * @param $customerId int customer.id or -1 for all customers
  209.      * @param null $userList
  210.      * @return array
  211.      * @throws \Exception
  212.      */
  213.     public function findUnrated($customerId,$userList = null,$maxDaysIntoPast=null)
  214.     {
  215.         if ($maxDaysIntoPast===null) $maxDaysIntoPast = RatingController::MAX_AGE_FOR_RATING_DAYS;
  216.         $maxPast = (new \DateTime())->sub(new \DateInterval(sprintf("P%dD", $maxDaysIntoPast)));
  217.         $now = (new \DateTime())->sub(new \DateInterval(sprintf("PT%dH", 2)));
  218.         $qb = $this->createQueryBuilder("A")
  219.             ->select("A,B")
  220.             ->leftJoin("A.customer","B")
  221.             ->where("A.orderStatus = :os")
  222.                 ->setParameter("os",OrderStatus::STATUS_FINISHED)
  223.             ->andWhere("A.orderTime > :limit")
  224.                 ->setParameter("limit",$maxPast)
  225.             ->andWhere("A.orderTime < :now")
  226.                 ->setParameter("now",$now)
  227.             ->andWhere("B.showRating = '1'");
  228.         if ($customerId>=0)
  229.         {
  230.             $qb->andWhere("B.id = :cid")
  231.                     ->setParameter("cid",$customerId);
  232.         }
  233.         if ($userList != null )
  234.         {
  235.             if (count($userList)<1) throw new \Exception("No userlist provided.");
  236.             $or = $qb->expr()->orX();
  237.             foreach ($userList as $u)
  238.             {
  239.                 $or->add(sprintf("A.beOwner = '%d'",$u->getId()));
  240.             }
  241.             $qb->andWhere($or);
  242.         }
  243.         $qb->orderBy("A.orderTime");
  244.         $all = $qb->getQuery()->getResult(AbstractQuery::HYDRATE_OBJECT);
  245.         $ids = [];
  246.         /** @var Order $order */
  247.         foreach ($all as $order)
  248.         {
  249.             $ids[]= $order->getId();
  250.         }
  251.         $ratingRepo = $this->_em->getRepository(Rating::class);
  252.         $exist = $ratingRepo->findExistingRatings($ids);
  253.         $newResult = [];
  254.         foreach ($all as $order)
  255.         {
  256.             if (!in_array($order->getId(),$exist))
  257.                 $newResult[]=$order;
  258.         }
  259.         return $newResult;
  260.     }
  263.     public function findByTamiOrderNumbers(array $list)
  264.     {
  265.         $qb = $this->createQueryBuilder("A")
  266.             ->select("A,B,C,S,D,R")
  267.             ->leftJoin("A.customer","B")
  268.             ->leftJoin("A.beOwner","C")
  269.             ->leftJoin("A.orderStatus","S")
  270.             ->leftJoin("A.addressList","D")
  271.             ->leftJoin("A.referencedParentOrder","R");
  272.         $qb->where($qb->expr()->in('A.remoteOrderId',$list));
  273.         $all = $qb->getQuery()->getResult();
  274.         $ret = [];
  275.         /** @var Order $one */
  276.         foreach ($all as $one)
  277.         {
  278.             $ret[$one->getRemoteOrderId()] = $one;
  279.         }
  280.         return $ret;
  281.     }
  283.     public function findOpenNotInSyncWithTami()
  284.     {
  285.         $last = (new \DateTime())->sub(new \DateInterval("P14D"));
  286.         $qb = $this->getQb();
  287.         $qb ->where($qb->expr()->in("A.orderStatus",":status"))->setParameter("status",[OrderStatus::STATUS_OPEN,OrderStatus::STATUS_CANCELED])
  288.             ->andWhere("A.remoteStatus != :remoteStatus")->setParameter("remoteStatus", Order::REMOTE_SUCCESS)
  289.             ->andWhere("A.orderTime > :last")->setParameter("last",$last)
  290.             ->orderBy("A.orderTime",'desc');
  291.         return $qb->getQuery()->getResult();
  292.     }
  294.     public function getNewOrderId($date = null)
  295.     {
  296.         if ($date === null) $date = new \DateTime();
  297.         // Format: YYMMDDXXX
  298.         $prefix = $date->format("ymd"); // length of prefix = 6
  299.         // MySQL: For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.
  300.         $st = $this->dbQuery("
  301.                                   SELECT MAX(CAST(SUBSTRING(order_id,7) AS UNSIGNED)) FROM orders
  302.                                   WHERE order_id LIKE :pattern
  303.                                   AND be_deleted = '0'
  304.                                   ", array("pattern"=>$prefix. "%"));
  305.         $max = $st->fetchOne();
  306.         if ($max === false) $max = 0;
  307.         if ($max >= 999)
  308.         {
  309.             throw new \Exception("Reached max. order number limit on this day !");
  310.         }
  311.         return sprintf("%s%03d",$prefix,$max+1);
  312.     }
  314.     public function findAssignedToAnyMemberForDay($day, $sortByMemberId = true, $specificMember = null)
  315.     {
  316.         $from = clone $day;
  317.         $until = clone $day;
  318.         $from->modify('today'); // start of day
  319.         $until->modify('tomorrow')->modify('1 second ago'); // end of day
  320.         $list = $this->findAssignedToAnyMemberForDateRange($from,$until,$specificMember);
  321.         if ($sortByMemberId)
  322.         {
  323.             $sorted = [];
  324.             /** @var Order $item */
  325.             foreach ($list as $item)
  326.             {
  327.                 $mid = $item->getAssignedTo()->getId();
  328.                 if (!array_key_exists($mid,$sorted)) $sorted[$mid] = [];
  329.                 $sorted[$mid] []= $item;
  330.             }
  331.             return $sorted;
  332.         }
  333.         return $list;
  334.     }
  336.     public function findNotAssignedToAnyMemberForDateRange($dateFrom, $dateUntil)
  337.     {
  338.         return $this->_findAssignedToAnyMemberOrNotForDateRange($dateFrom,$dateUntil,null,true);
  339.     }
  341.     public function findAssignedToAnyMemberForDateRange($dateFrom, $dateUntil, $specificMember=null)
  342.     {
  343.         return $this->_findAssignedToAnyMemberOrNotForDateRange($dateFrom,$dateUntil,$specificMember,false);
  344.     }
  346.     protected function _findAssignedToAnyMemberOrNotForDateRange($dateFrom, $dateUntil, $specificMember, $notAssignedToAnyMember)
  347.     {
  348.         $qb = $this->getQb();
  349.         $ex = $qb->expr();
  350.         $qb->andWhere($ex->gte("A.orderTime", ':from' ),$ex->lte("A.orderTime", ':until' ))
  351.             ->setParameter('from',$dateFrom)
  352.             ->setParameter('until',$dateUntil);
  353.         $qb->andWhere('A.orderStatus < :s')->setParameter('s',OrderStatus::STATUS_CANCELED);
  354.         if ($specificMember != null)
  355.         {
  356.             $qb->andWhere('A.assignedTo = :m')->setParameter('m',$specificMember);
  357.         }
  358.         else
  359.         {
  360.             if ($notAssignedToAnyMember)
  361.                 $qb->andWhere('A.assignedTo is null');
  362.             else
  363.                 $qb->andWhere('A.assignedTo is not null');
  364.         }
  365.         $qb->addOrderBy('A.orderTime','asc');
  367.         return $qb->getQuery()->getResult();
  368.     }
  370.     public function findAssignedButNotConfirmedFor(CoopMember $specificMember)
  371.     {
  372.         $qb = $this->getQb();
  373.         $maxPast = (new \DateTime())->modify('-1 days');
  374.         $qb ->andWhere($qb->expr()->gte("A.orderTime", ':from' ))->setParameter('from',$maxPast)
  375.             ->andWhere('A.assignedTo = :m')->setParameter('m',$specificMember)
  376.             ->andWhere('A.assignmentConfirmed = 0')
  377.             ->addOrderBy('A.orderTime','asc');
  378.         return $qb->getQuery()->getResult();
  379.     }
  382. }