src/Repository/DeliveryParcelRepository.php line 564

Open in your IDE?
  1. <?php
  2. /**
  3. * Created by PhpStorm.
  4. * User: parcel
  5. * Date: 1/23/19
  6. * Time: 7:35 AM
  7. */
  9. namespace App\Repository;
  12. use App\Entity\Delivery;
  13. use Doctrine\DBAL\Exception;
  14. use Doctrine\ORM\EntityRepository;
  15. use Doctrine\ORM\NonUniqueResultException;
  16. use Doctrine\ORM\NoResultException;
  18. class DeliveryParcelRepository extends EntityRepository{
  21. public function deliveryParcels(Delivery $delivery) {
  22. $qb = $this->createQueryBuilder('dp');
  23. $qb->join("dp.parcel", "p")
  24. ->join("dp.delivery", "d")
  25. ->join("dp.createdBy", "cb")
  28. ->select("p.id as parcel")
  29. ->addSelect("dp.id as id")
  30. ->addSelect("d.id as delivery")
  31. ->addSelect("dp.isCancelled as is_cancelled")
  32. ->addSelect("dp.cancelledBecause as cancelled_because")
  33. ->addSelect("cb.id as created_by")
  35. ->where("dp.delivery=:delivery")
  36. ->setParameter("delivery", $delivery);
  39. return $qb
  40. ->getQuery()
  41. ->execute();
  42. }
  44. public function outGoingDeliveryParcels($station, $lastDelivery) {
  45. $qb = $this->createQueryBuilder('dp');
  46. $qb->join("dp.parcel", "p")
  47. ->join('p.waybill', 'w')
  48. ->join("dp.delivery", "d")
  49. ->join("dp.createdBy", "cb")
  52. ->select("p.id as parcel")
  53. ->addSelect("dp.id as online_id")
  54. ->addSelect("d.id as delivery")
  55. ->addSelect("dp.isCancelled as is_cancelled")
  56. ->addSelect("dp.cancelledBecause as cancelled_because")
  57. ->addSelect('d.isReceivedFully as is_received_fully')
  58. ->addSelect('dp.isReceived as is_received')
  59. ->addSelect("cb.id as created_by")
  61. ->where("d.originStation=:station")
  62. ->andWhere('d.isVerified = false')
  63. ->andWhere("d.id >:last_delivery")
  65. ->setParameter("last_delivery", $lastDelivery)
  66. ->setParameter("station", $station);
  69. return $qb
  70. ->getQuery()
  71. ->execute();
  72. }
  75. public function getDeliveryParcels($delivery) {
  76. $qb = $this->createQueryBuilder('dp');
  77. $qb->join("dp.parcel", "p")
  78. ->join('p.waybill', 'w')
  79. ->join('w.transaction', 'transaction')
  80. ->join('w.toStation', 'toStation')
  81. ->join("dp.delivery", "d")
  82. ->join("dp.createdBy", "cb")
  83. ->join("cb.person", "person")
  87. ->select("dp.id as id")
  88. ->addSelect("w.id as waybill")
  89. ->addSelect("toStation.stationName as to_station_name")
  90. ->addSelect("transaction.amount as amount")
  91. ->addSelect("d.id as delivery")
  92. ->addSelect("p.id as parcel_id")
  93. ->addSelect("dp.isCancelled as is_cancelled")
  94. ->addSelect("dp.cancelledBecause as cancelled_because")
  95. ->addSelect("person.firstName as created_by")
  96. ->addSelect("p.description as description")
  98. ->where("d.id =:delivery")
  100. ->setParameter("delivery", $delivery);
  103. return $qb
  104. ->getQuery()
  105. ->execute();
  106. }
  109. public function deliveryIncomingParcels($station, $lastDelivery) {
  110. $qb = $this->createQueryBuilder('dp');
  111. $qb->join("dp.parcel", "p")
  112. ->join('p.waybill', 'w')
  113. ->join("dp.delivery", "d")
  114. ->join("dp.createdBy", "cb")
  117. ->select("p.id as parcel")
  118. ->addSelect("dp.id as id")
  119. ->addSelect("d.id as delivery")
  120. ->addSelect("dp.isCancelled as is_cancelled")
  121. ->addSelect("dp.cancelledBecause as cancelled_because")
  122. ->addSelect('d.isReceivedFully as is_received_fully')
  123. ->addSelect('dp.isReceived as is_received')
  124. ->addSelect("cb.id as created_by")
  126. ->where("d.destination=:station")
  127. ->andWhere('d.isCancelled = false')
  128. ->andWhere('d.isVerified = true')
  129. ->andWhere('d.id >:last_delivery')
  130. // ->andWhere('d.isReceived = false')
  131. ->andWhere('w.isCollected = false')
  132. // ->andWhere('dp.id >:last_id')
  134. ->setParameter('last_delivery', $lastDelivery)
  135. ->setParameter("station", $station);
  138. return $qb
  139. ->getQuery()
  140. ->execute();
  141. }
  145. public function commonDeliveryIncomingParcels($delivery_id) {
  146. $qb = $this->createQueryBuilder('dp');
  147. $qb->join("dp.parcel", "p")
  148. ->join("dp.delivery", "d")
  149. ->join("dp.createdBy", "cb")
  151. ->join('p.waybill', 'w')
  152. ->join("w.fromStation", "ft")
  153. ->join("w.toStation", "tt")
  154. ->join("w.transaction", "t")
  155. ->join("w.organization", "o")
  159. ->select("dp.id as id")
  160. ->addSelect("d.id as delivery")
  161. ->addSelect('dp.isReceived as is_received')
  162. ->addSelect("cb.id as created_by")
  165. // Beginning incoming parcel
  167. ->addSelect("p.id as parcel")
  168. ->addSelect("dp.isCancelled as is_cancelled")
  169. ->addSelect("dp.cancelledBecause as cancelled_because")
  170. ->addSelect("p.description as description")
  171. ->addSelect('d.isReceivedFully as is_received_fully')
  172. ->addSelect('p.number as number')
  174. // Beginning waybill
  176. ->addSelect("w.id as waybill")
  177. ->addSelect("w.senderName as sender_name")
  178. ->addSelect("w.senderPhoneNumber as sender_phone_number")
  179. ->addSelect("w.receiverName as receiver_name")
  180. ->addSelect("w.receiverPhoneNumber as receiver_phone_number")
  181. ->addSelect("ft.id as from_station")
  182. ->addSelect("tt.id as to_station")
  183. ->addSelect("tt.stationName as to_station_name")
  184. ->addSelect("DATE_FORMAT(w.createdAt, '%Y-%m-%d %H:%i:%s') as created_at")
  185. ->addSelect("t.id as transaction_id")
  186. ->addSelect("t.amount as amount")
  187. ->addSelect("w.isCollected as is_collected")
  188. ->addSelect("o.id as organization")
  189. ->addSelect("w.percelCount as parcel_count")
  193. ->where('d.isCancelled = false')
  194. ->andWhere('dp.isCancelled = false')
  195. ->andWhere('d.isVerified = true')
  196. ->andWhere('d.id = :delivery')
  198. ->setParameter('delivery', $delivery_id);
  201. return $qb
  202. ->getQuery()
  203. ->execute();
  204. }
  208. public function oneDeliveryIncomingParcels($delivery_number) {
  209. $qb = $this->createQueryBuilder('dp');
  210. $qb->join("dp.parcel", "p")
  211. ->join('p.waybill', 'w')
  212. ->join("dp.delivery", "d")
  213. ->join("dp.createdBy", "cb")
  216. ->select("p.id as parcel")
  217. ->addSelect("dp.id as id")
  218. ->addSelect("d.id as delivery")
  219. ->addSelect("dp.isCancelled as is_cancelled")
  220. ->addSelect("dp.cancelledBecause as cancelled_because")
  221. ->addSelect('d.isReceivedFully as is_received_fully')
  222. ->addSelect('dp.isReceived as is_received')
  223. ->addSelect("cb.id as created_by")
  225. ->where("d.id=:delivery_number")
  227. ->setParameter('delivery_number', $delivery_number);
  230. return $qb
  231. ->getQuery()
  232. ->execute();
  233. }
  236. public function getOutGoingParcels($station, $lastWaybill) {
  237. $qb = $this->createQueryBuilder("dp")
  238. ->join("dp.parcel", "p")
  239. ->join("p.waybill", "w")
  241. ->select("p.id")
  242. ->addSelect("w.id as waybill")
  243. ->addSelect("p.number as number")
  244. ->addSelect("p.isInDelivery as is_in_delivery")
  245. ->addSelect("p.description")
  246. ->addSelect("p.createdAt as created_at")
  248. ->where("w.id >:lastWaybill")
  249. ->andWhere("w.fromStation =:fromStation")
  250. ->andWhere("w.createdAt >:today")
  252. ->setParameter("today", new \DateTime("-5 days"))
  253. ->setParameter("fromStation", $station)
  254. ->setParameter("lastWaybill", $lastWaybill);
  256. return $qb
  257. ->getQuery()
  258. ->execute();
  259. }
  262. public function getIncomingParcels($station, $lastDelivery) {
  263. $qb = $this->createQueryBuilder("dp")
  264. ->join("dp.parcel", "p")
  265. ->join("p.waybill", "w")
  266. ->join("dp.delivery", "d")
  268. ->select("distinct(p.id) as id")
  269. ->addSelect("w.id as waybill")
  270. ->addSelect("p.number as number")
  271. ->addSelect("p.isInDelivery as is_in_delivery")
  272. ->addSelect("p.description")
  273. ->addSelect("p.createdAt as created_at")
  275. ->where("d.id >:last_delivery")
  276. ->andWhere('dp.isCancelled = false')
  277. ->andWhere("d.destination =:destination")
  278. ->andWhere("w.isCollected = false")
  279. ->andWhere("d.isVerified = true")
  280. // ->andWhere("w.createdAt >:today")
  282. // ->setParameter("today", new \DateTime("-5 days"))
  283. ->setParameter("destination", $station)
  284. ->setParameter("last_delivery", $lastDelivery);
  286. return $qb
  287. ->getQuery()
  288. ->execute();
  289. }
  293. public function getOneDeliveryIncomingParcels($delivery_number) {
  294. $qb = $this->createQueryBuilder("dp")
  295. ->join("dp.parcel", "p")
  296. ->join("p.waybill", "w")
  297. ->join("dp.delivery", "d")
  299. ->select("distinct(p.id) as id")
  300. ->addSelect("w.id as waybill")
  301. ->addSelect("p.number as number")
  302. ->addSelect("p.isInDelivery as is_in_delivery")
  303. ->addSelect("p.description")
  304. ->addSelect("p.createdAt as created_at")
  306. ->where("d.id = :delivery_number")
  307. ->setParameter("delivery_number", $delivery_number);
  309. return $qb
  310. ->getQuery()
  311. ->execute();
  312. }
  315. public function incomingParcels($station, $lastDelivery) {
  316. $qb = $this->createQueryBuilder("dp")
  317. ->join("dp.delivery", "d")
  318. ->join("dp.parcel", "p")
  319. ->join("p.waybill", "w")
  321. ->select("p.id")
  322. ->addSelect("w.id as waybill")
  323. ->addSelect("p.number as number")
  324. ->addSelect("p.isInDelivery as is_in_delivery")
  325. ->addSelect("p.description")
  326. ->addSelect("p.createdAt as created_at")
  328. ->where("d.id >:last_delivery")
  329. ->andWhere("d.destination =:destination")
  330. ->andWhere("d.isReceivedFully = false")
  331. ->andWhere("d.isVerified = true")
  333. ->setParameter("last_delivery", $lastDelivery)
  334. ->setParameter("destination", $station);
  336. return $qb
  337. ->getQuery()
  338. ->execute();
  339. }
  341. public function findAllWaybills($last_id, $station){
  342. $qb = $this->createQueryBuilder("dp")
  343. ->join("dp.parcel", "p")
  344. ->join("p.waybill", "w")
  345. ->join("w.fromStation", "ft")
  346. ->join("w.toStation", "tt")
  347. ->join("w.createdBy", "cb")
  348. ->join("w.transaction", "t")
  349. ->join("w.organization", "o")
  351. ->select("DISTINCT(w.id) as id")
  352. ->addSelect("w.senderName as sender_name")
  353. ->addSelect("w.senderPhoneNumber as sender_phone_number")
  354. ->addSelect("w.receiverName as receiver_name")
  355. ->addSelect("w.receiverPhoneNumber as receiver_phone_number")
  356. ->addSelect("ft.id as from_station")
  357. ->addSelect("tt.id as to_station")
  358. ->addSelect("w.createdAt as created_at")
  359. ->addSelect("cb.id as created_by")
  360. ->addSelect("t.id as transaction_id")
  361. ->addSelect("t.amount as amount")
  362. ->addSelect("w.isCollected as is_collected")
  363. ->addSelect("o.id as organization")
  364. ->addSelect("w.percelCount as parcel_count")
  365. ->where("w.id > :lastID")
  366. ->andWhere("w.toStation =:toStation")
  367. // ->andWhere("ft.id =:fromStation OR tt.id =:toStation")
  369. ->setParameter("lastID", $last_id)
  370. ->setParameter("toStation", $station);
  373. return $qb
  374. ->getQuery()
  375. ->execute();
  376. }
  378. public function findWaybill($station, $waybill){
  379. $qb = $this->createQueryBuilder("dp")
  380. ->join("dp.parcel", "p")
  381. ->join("dp.delivery", "d")
  382. ->join("p.waybill", "w")
  383. ->join("w.fromStation", "ft")
  384. ->join("w.toStation", "tt")
  385. ->join("w.createdBy", "cb")
  386. ->join("w.transaction", "t")
  387. ->join("w.organization", "o")
  389. ->select("DISTINCT(w.id) as id")
  390. ->addSelect("w.senderName as sender_name")
  391. ->addSelect("w.senderPhoneNumber as sender_phone_number")
  392. ->addSelect("w.receiverName as receiver_name")
  393. ->addSelect("w.receiverPhoneNumber as receiver_phone_number")
  394. ->addSelect("ft.id as from_station")
  395. ->addSelect("tt.id as to_station")
  396. ->addSelect("w.createdAt as created_at")
  397. ->addSelect("cb.id as created_by")
  398. ->addSelect("t.id as transaction_id")
  399. ->addSelect("t.amount as amount")
  400. ->addSelect("w.isCollected as is_collected")
  401. ->addSelect("o.id as organization")
  402. ->addSelect("w.percelCount as parcel_count")
  403. ->addSelect("d.id as delivery_id")
  404. ->where("w.id =:waybill")
  405. ->andWhere("w.toStation =:toStation")
  406. // ->andWhere("ft.id =:fromStation OR tt.id =:toStation")
  408. ->setParameter("waybill", $waybill)
  409. ->setParameter("toStation", $station);
  412. try {
  413. return $qb
  414. ->getQuery()
  415. ->getSingleResult();
  416. } catch (NoResultException $e) {
  417. } catch (NonUniqueResultException $e) {
  418. }
  419. }
  421. public function findAllDeliveryWaybills($station,$lastDelivery){
  422. $qb = $this->createQueryBuilder("dp")
  423. ->join("dp.parcel","p")
  424. ->join("p.waybill", "w")
  425. ->join("w.fromStation", "ft")
  426. ->join("w.toStation", "tt")
  427. ->join("w.createdBy", "cb")
  428. ->join("w.transaction", "t")
  429. ->join("w.organization", "o")
  430. ->join("dp.delivery", "d")
  432. ->select("DISTINCT(w.id) as id")
  433. ->addSelect("w.senderName as sender_name")
  434. ->addSelect("w.senderPhoneNumber as sender_phone_number")
  435. ->addSelect("w.receiverName as receiver_name")
  436. ->addSelect("w.receiverPhoneNumber as receiver_phone_number")
  437. ->addSelect("ft.id as from_station")
  438. ->addSelect("tt.id as to_station")
  439. ->addSelect("w.createdAt as created_at")
  440. ->addSelect("cb.id as created_by")
  441. ->addSelect("t.id as transaction_id")
  442. ->addSelect("t.amount as amount")
  443. ->addSelect("w.isCollected as is_collected")
  444. ->addSelect("o.id as organization")
  445. ->addSelect("w.percelCount as parcel_count")
  448. ->where("d.id >:last_delivery")
  449. ->andWhere("d.destination =:destination")
  450. ->andWhere("d.isVerified = true")
  451. ->andWhere("w.isCollected = false")
  452. ->setParameter("last_delivery", $lastDelivery)
  453. ->setParameter("destination", $station);
  456. return $qb
  457. ->getQuery()
  458. ->execute();
  459. }
  462. public function findAllOneDeliveryWaybills($delivery_number){
  463. $qb = $this->createQueryBuilder("dp")
  464. ->join("dp.parcel","p")
  465. ->join("p.waybill", "w")
  466. ->join("w.fromStation", "ft")
  467. ->join("w.toStation", "tt")
  468. ->join("w.createdBy", "cb")
  469. ->join("w.transaction", "t")
  470. ->join("w.organization", "o")
  471. ->join("dp.delivery", "d")
  473. ->select("DISTINCT(w.id) as id")
  474. ->addSelect("w.senderName as sender_name")
  475. ->addSelect("w.senderPhoneNumber as sender_phone_number")
  476. ->addSelect("w.receiverName as receiver_name")
  477. ->addSelect("w.receiverPhoneNumber as receiver_phone_number")
  478. ->addSelect("ft.id as from_station")
  479. ->addSelect("tt.id as to_station")
  480. ->addSelect("w.createdAt as created_at")
  481. ->addSelect("cb.id as created_by")
  482. ->addSelect("t.id as transaction_id")
  483. ->addSelect("t.amount as amount")
  484. ->addSelect("w.isCollected as is_collected")
  485. ->addSelect("o.id as organization")
  486. ->addSelect("w.percelCount as parcel_count")
  489. ->where("d.id =:delivery_number")
  490. ->setParameter("delivery_number", $delivery_number);
  493. return $qb
  494. ->getQuery()
  495. ->execute();
  496. }
  499. public function unCollectedParcel($waybill) {
  500. $qb = $this->createQueryBuilder('dp');
  501. $qb->join('dp.parcel', 'p')
  502. ->join('p.waybill', 'w')
  503. ->join('dp.delivery', 'd');
  505. $qb->andWhere('d.isVerified = true')
  506. ->andWhere('p.waybill = :waybill')
  507. ->andWhere('dp.isReceived = true')
  508. ->andWhere('w.isCollected = false')
  509. ->setParameter('waybill', $waybill);
  511. return $qb
  512. ->getQuery()
  513. ->execute();
  515. }
  517. public function findUnReceivedParcels($stationID) {
  518. $qb = $this->createQueryBuilder('dp');
  519. $qb->join('dp.parcel', 'p')
  520. ->join('dp.delivery', 'd');
  522. $qb->andWhere('d.isReceivedFully = false')
  523. ->andWhere('d.isVerified = true')
  524. ->andWhere('dp.isReceived = false')
  525. ->andWhere('d.destination =:toStation')
  526. ->setParameter('toStation', $stationID);
  528. return $qb
  529. ->getQuery()
  530. ->execute();
  531. }
  532. public function findEnRouteDeliveries($filterRules, $fromStation, $offset, $rows): ?array
  533. {
  535. $conn = $this->getEntityManager()->getConnection();
  537. $additionalFilter = '';
  538. if (!empty($filterRules)){
  539. $filterRules = json_decode($filterRules);
  540. foreach($filterRules as $rule){
  541. $rule = get_object_vars($rule);
  542. $field = $rule['field'];
  543. // $fieldPlace = str_replace(".", "", $field);
  544. $op = $rule['op'];
  545. $value = $rule['value'];
  546. if ($value == 0 || !empty($value)){
  547. if ($op == 'contains'){
  548. $additionalFilter .= " AND ".$field." like'%".$value."%'";
  549. } else if($op == 'equal'){
  550. if($field == 'a.created_at')
  551. $additionalFilter .= " AND cast(".$field." as date) = cast('${value}' as date)";
  552. else
  553. $additionalFilter .= " AND ".$field."= ${value}";
  555. }else if($op == 'less'){
  556. if($field == 'a.created_at')
  557. $additionalFilter .= " AND cast(".$field." as date) > cast('${value}' as date)";
  558. else
  559. $additionalFilter .= " AND ".$field." < ${value}";
  560. }else if($op == 'greater'){
  561. if($field == 'a.created_at')
  562. $additionalFilter .= " AND cast(".$field." as date) > cast('${value}' as date)";
  563. else
  564. $additionalFilter .= " AND ".$field." > ${value}";
  566. }else if($op == 'greater_equal'){
  567. if($field == 'a.created_at')
  568. $additionalFilter .= " AND cast(".$field." as date) >= cast('${value}' as date)";
  569. else
  570. $additionalFilter .= " AND ".$field." >= ${value}";
  571. }
  572. }
  573. }
  574. }
  576. $sql = "SELECT b.id as id, e.station_name,b.parcel_count,b.created_at,
  577. b.is_verified,b.loaded_at,g.reg_number
  578. FROM delivery b
  579. JOIN delivery_vehicle d ON d.delivery_id = b.id
  580. JOIN station e ON e.id = b.destination
  581. JOIN station f ON f.id = b.origin_station
  582. JOIN vehicle g ON g.id = d.vehicle_id
  583. WHERE b.origin_station = ${fromStation} ${additionalFilter} ORDER BY b.id DESC LIMIT ${offset},${rows}";
  586. try {
  587. $stmt = $conn->prepare($sql);
  589. return $stmt->executeQuery()->fetchAllAssociative();
  590. } catch (Exception | \Doctrine\DBAL\Exception $e) {
  592. return null;
  593. }
  596. }
  598. public function findTotalEnRouteDeliveries($filterRules, $fromStation) {
  600. $qb = $this->createQueryBuilder('dp');
  601. $qb->join('dp.delivery', 'd')
  602. ->join('dp.parcel', 'p')
  603. ->join('d.deliveryVehicle', 'dv')
  604. ->join('d.destination', 'e')
  605. ->join('d.originStation', 'os')
  606. ->join('dv.vehicle', 'v')
  607. ->join('d.createdBy', 'cb')
  608. ->join('cb.person', 'up');
  611. $qb->select("count(d.id) as total");
  614. $qb->andWhere('d.isReceived = false')
  615. ->andWhere('d.originStation =:fromStation')
  616. ->setParameter('fromStation', $fromStation);
  618. if (!empty($filterRules) && !is_null($filterRules)){
  619. $filterRules = json_decode($filterRules);
  620. foreach($filterRules as $rule){
  621. $rule = get_object_vars($rule);
  622. $field = $rule['field'];
  623. $fieldPlace = str_replace(".", "", $field);
  624. $op = $rule['op'];
  625. $value = $rule['value'];
  626. if (!empty($value)){
  627. if ($op == 'contains'){
  628. $qb->andWhere($field." LIKE :".$fieldPlace);
  629. $qb->setParameter($fieldPlace, '%'.$value.'%');
  631. } else if($op == 'equal'){
  632. if($value > 0) {
  633. $qb->andWhere($field."=".$value);
  634. // $qb->setParameter($field, $value);
  635. }
  637. }else if($op == 'less'){
  638. if($value > 0) {
  639. $qb->andWhere($field."<".$value);
  640. // $qb->setParameter($field, $value);
  641. }
  643. }else if($op == 'greater'){
  644. if($value > 0) {
  645. $qb->andWhere($field.">".$value);
  646. // $qb->setParameter($field, $value);
  647. }
  649. }
  650. }
  651. }
  652. }
  653. return $qb->getQuery()
  654. ->getSingleScalarResult();
  655. }
  657. public function findDeliveryParcels($delivery_id) {
  659. $qb = $this->createQueryBuilder('dp');
  660. $qb->join('dp.parcel', 'p')
  661. ->join('dp.delivery', 'd')
  662. ->join('p.waybill', 'w')
  663. ->join('w.toStation', 'ts')
  664. ->join('w.transaction', 't');
  667. $qb->select("w.id as id")
  668. ->addSelect('ts.stationName as station_name')
  669. ->addSelect('w.senderName as sender_name')
  670. ->addSelect('w.senderPhoneNumber as sender_phone_number')
  671. ->addSelect('w.receiverName as receiver_name')
  672. ->addSelect('w.receiverPhoneNumber as receiver_phone_number')
  673. ->addSelect('p.description');
  676. $qb->where('d.id =:delivery_id')
  677. ->setParameter('delivery_id', $delivery_id);
  681. return
  682. $qb->getQuery()
  683. ->execute();
  684. }
  686. public function findEnRouteParcels($filterRules, $fromStation, $offset, $rows) {
  688. $qb = $this->createQueryBuilder('dp');
  689. $qb->join('dp.parcel', 'p')
  690. ->join('dp.delivery', 'd')
  691. ->join('d.deliveryVehicle', 'dv')
  692. ->join('dv.vehicle', 'v')
  693. ->join('p.waybill', 'w')
  694. ->join('w.toStation', 'ts')
  695. ->join('w.transaction', 't');
  698. $qb->select("w.id as id")
  699. ->addSelect('ts.stationName as station_name')
  700. ->addSelect('w.senderName as sender_name')
  701. ->addSelect('w.senderPhoneNumber as sender_phone_number')
  702. ->addSelect('w.receiverName as receiver_name')
  703. ->addSelect('w.receiverPhoneNumber as receiver_phone_number')
  704. ->addSelect('p.description')
  705. ->addSelect('d.id as delivery_id')
  706. ->addSelect('v.regNumber as reg_number');
  709. $qb->andWhere('d.isReceived = false')
  710. ->andWhere('d.isVerified = true')
  711. ->andWhere('d.originStation =:fromStation')
  712. ->setParameter('fromStation', $fromStation);
  714. if (!empty($filterRules) && !is_null($filterRules)){
  715. $filterRules = json_decode($filterRules);
  716. foreach($filterRules as $rule){
  717. $rule = get_object_vars($rule);
  718. $field = $rule['field'];
  719. $fieldPlace = str_replace(".", "", $field);
  720. $op = $rule['op'];
  721. $value = $rule['value'];
  722. if (!empty($value)){
  723. if ($op == 'contains'){
  724. $qb->andWhere($field." LIKE :".$fieldPlace);
  725. $qb->setParameter($fieldPlace, '%'.$value.'%');
  727. } else if($op == 'equal'){
  728. if($value > 0) {
  729. $qb->andWhere($field."=".$value);
  730. // $qb->setParameter($field, $value);
  731. }
  733. }else if($op == 'less'){
  734. if($value > 0) {
  735. $qb->andWhere($field."<".$value);
  736. // $qb->setParameter($field, $value);
  737. }
  739. }else if($op == 'greater'){
  740. if($value > 0) {
  741. $qb->andWhere($field.">".$value);
  742. // $qb->setParameter($field, $value);
  743. }
  745. }
  746. }
  747. }
  748. }
  750. return $qb
  751. ->setFirstResult($offset)
  752. ->setMaxResults($rows)
  753. ->getQuery()
  754. ->execute();
  755. }
  761. public function findTotalEnRouteParcels($filterRules, $fromStation) {
  763. $qb = $this->createQueryBuilder('dp')
  764. ->select('count(t.id)');
  766. $qb->join('dp.parcel', 'p')
  767. ->join('dp.delivery', 'd')
  768. ->join('d.deliveryVehicle', 'dv')
  769. ->join('p.waybill', 'w')
  770. ->join('w.transaction', 't');
  772. $qb->andWhere('d.isReceived = false')
  773. ->andWhere('d.isVerified = true')
  774. ->andWhere('d.originStation =:fromStation')
  775. ->setParameter('fromStation', $fromStation);
  777. if (!empty($filterRules) && !is_null($filterRules)){
  778. $filterRules = json_decode($filterRules);
  779. foreach($filterRules as $rule){
  780. $rule = get_object_vars($rule);
  781. $field = $rule['field'];
  782. $fieldPlace = str_replace(".", "", $field);
  783. $op = $rule['op'];
  784. $value = $rule['value'];
  785. if (!empty($value)){
  786. if ($op == 'contains'){
  787. $qb->andWhere($field." LIKE :".$fieldPlace);
  788. $qb->setParameter($fieldPlace, '%'.$value.'%');
  790. } else if($op == 'equal'){
  791. if($value > 0) {
  792. $qb->andWhere($field."=".$value);
  793. // $qb->setParameter($field, $value);
  794. }
  796. }else if($op == 'less'){
  797. if($value > 0) {
  798. $qb->andWhere($field."<".$value);
  799. // $qb->setParameter($field, $value);
  800. }
  802. }else if($op == 'greater'){
  803. if($value > 0) {
  804. $qb->andWhere($field.">".$value);
  805. // $qb->setParameter($field, $value);
  806. }
  808. }
  809. }
  810. }
  811. }
  813. try {
  814. return $qb->getQuery()
  815. ->getSingleScalarResult();
  817. } catch (NoResultException $e) {
  819. } catch (NonUniqueResultException $e) {
  820. }
  821. }
  823. public function findIncomingParcels($filterRules, $toStation, $offset, $rows) {
  825. $conn = $this->getEntityManager()->getConnection();
  827. $additionalFilter = '';
  828. if (!empty($filterRules)){
  829. $filterRules = json_decode($filterRules);
  830. foreach($filterRules as $rule){
  831. $rule = get_object_vars($rule);
  832. $field = $rule['field'];
  833. // $fieldPlace = str_replace(".", "", $field);
  834. $op = $rule['op'];
  835. $value = $rule['value'];
  836. if ($value == 0 || !empty($value)){
  837. if ($op == 'contains'){
  838. $additionalFilter .= " AND ".$field." like'%".$value."%'";
  839. } else if($op == 'equal'){
  840. if($field == 'a.created_at')
  841. $additionalFilter .= " AND cast(".$field." as date) = cast('${value}' as date)";
  842. else
  843. $additionalFilter .= " AND ".$field."= ${value}";
  845. }else if($op == 'less'){
  846. if($field == 'a.created_at')
  847. $additionalFilter .= " AND cast(".$field." as date) > cast('${value}' as date)";
  848. else
  849. $additionalFilter .= " AND ".$field." < ${value}";
  850. }else if($op == 'greater'){
  851. if($field == 'a.created_at')
  852. $additionalFilter .= " AND cast(".$field." as date) > cast('${value}' as date)";
  853. else
  854. $additionalFilter .= " AND ".$field." > ${value}";
  856. }else if($op == 'greater_equal'){
  857. if($field == 'a.created_at')
  858. $additionalFilter .= " AND cast(".$field." as date) >= cast('${value}' as date)";
  859. else
  860. $additionalFilter .= " AND ".$field." >= ${value}";
  861. }
  862. }
  863. }
  864. }
  866. $sql = "SELECT distinct(b.id) as id, f.station_name,
  867. i.first_name,i.second_name,i.sir_name,b.parcel_count,b.created_at,
  868. b.is_verified,b.loaded_at,g.reg_number
  869. FROM delivery b
  870. JOIN delivery_parcel a ON b.id = a.delivery_id
  871. JOIN parcel c ON c.id = a.parcel_id
  872. JOIN delivery_vehicle d ON d.id = b.id
  873. JOIN station e ON e.id = b.destination
  874. JOIN station f ON f.id = b.origin_station
  875. JOIN vehicle g ON g.id = d.vehicle_id
  876. JOIN user h ON h.id = a.created_by
  877. JOIN person i ON i.id = h.person_id
  878. WHERE b.destination = ${toStation} ${additionalFilter} ORDER BY b.id DESC LIMIT ${offset},${rows}";
  881. try {
  882. $stmt = $conn->prepare($sql);
  884. return $stmt->executeQuery()->fetchAllAssociative();
  885. } catch (Exception | \Doctrine\DBAL\Exception $e) {
  887. return null;
  888. }
  889. }
  895. public function findTotalIncomingParcels($filterRules, $toStation) {
  897. $qb = $this->createQueryBuilder('dp')
  898. ->select('count(t.id)');
  900. $qb->join('dp.parcel', 'p')
  901. ->join('dp.delivery', 'd')
  902. ->join('p.waybill', 'w')
  903. ->join('w.transaction', 't');
  905. $qb->andWhere('d.isReceivedFully = false')
  906. ->andWhere('w.isCollected = false')
  907. ->andWhere('d.isVerified = true')
  908. ->andWhere('d.destination =:toStation')
  909. ->setParameter('toStation', $toStation);
  911. if (!empty($filterRules) && !is_null($filterRules)){
  912. $filterRules = json_decode($filterRules);
  913. foreach($filterRules as $rule){
  914. $rule = get_object_vars($rule);
  915. $field = $rule['field'];
  916. $fieldPlace = str_replace(".", "", $field);
  917. $op = $rule['op'];
  918. $value = $rule['value'];
  919. if (!empty($value)){
  920. if ($op == 'contains'){
  921. $qb->andWhere($field." LIKE :".$fieldPlace);
  922. $qb->setParameter($fieldPlace, '%'.$value.'%');
  924. } else if($op == 'equal'){
  925. if($value > 0) {
  926. $qb->andWhere($field."=".$value);
  927. // $qb->setParameter($field, $value);
  928. }
  930. }else if($op == 'less'){
  931. if($value > 0) {
  932. $qb->andWhere($field."<".$value);
  933. // $qb->setParameter($field, $value);
  934. }
  936. }else if($op == 'greater'){
  937. if($value > 0) {
  938. $qb->andWhere($field.">".$value);
  939. // $qb->setParameter($field, $value);
  940. }
  942. }
  943. }
  944. }
  945. }
  947. try {
  948. return $qb->getQuery()
  949. ->getSingleScalarResult();
  951. } catch (NoResultException $e) {
  953. } catch (NonUniqueResultException $e) {
  954. }
  955. }
  958. public function findReceivedParcels($filterRules, $stationTo, $offset, $rows) {
  960. $qb = $this->createQueryBuilder('dp');
  961. $qb->join('dp.parcel', 'p')
  962. ->join('dp.delivery', 'd')
  963. ->join('d.deliveryVehicle', 'dv')
  964. ->join('p.waybill', 'w')
  965. ->join('w.transaction', 't');
  967. $qb->andWhere('d.isReceived = true')
  968. ->andWhere('w.isCollected = false')
  969. ->andWhere('d.isVerified = true')
  970. ->andWhere('d.destination =:toStation')
  971. ->setParameter('toStation', $stationTo);
  976. if (!empty($filterRules) && !is_null($filterRules)){
  977. $filterRules = json_decode($filterRules);
  978. foreach($filterRules as $rule){
  979. $rule = get_object_vars($rule);
  980. $field = $rule['field'];
  981. $fieldPlace = str_replace(".", "", $field);
  982. $op = $rule['op'];
  983. $value = $rule['value'];
  984. if (!empty($value)){
  985. if ($op == 'contains'){
  986. $qb->andWhere($field." LIKE :".$fieldPlace);
  987. $qb->setParameter($fieldPlace, '%'.$value.'%');
  989. } else if($op == 'equal'){
  990. if($value > 0) {
  991. $qb->andWhere($field."=".$value);
  992. // $qb->setParameter($field, $value);
  993. }
  995. }else if($op == 'less'){
  996. if($value > 0) {
  997. $qb->andWhere($field."<".$value);
  998. // $qb->setParameter($field, $value);
  999. }
  1001. }else if($op == 'greater'){
  1002. if($value > 0) {
  1003. $qb->andWhere($field.">".$value);
  1004. // $qb->setParameter($field, $value);
  1005. }
  1007. }
  1008. }
  1009. }
  1010. }
  1012. return
  1013. $qb
  1014. ->setFirstResult($offset)
  1015. ->setMaxResults($rows)
  1016. ->getQuery()
  1017. ->execute();
  1018. }
  1020. public function findTotalReceivedParcels($filterRules, $toStation) {
  1022. $qb = $this->createQueryBuilder('dp')
  1023. ->select('count(t.id)');
  1025. $qb->join('dp.parcel', 'p')
  1026. ->join('dp.delivery', 'd')
  1027. ->join('p.waybill', 'w')
  1028. ->join('w.transaction', 't');
  1030. $qb->andWhere('d.isReceived = true')
  1031. ->andWhere('w.isCollected = false')
  1032. ->andWhere('d.isVerified = true')
  1033. ->andWhere('d.destination =:toStation')
  1034. ->setParameter('toStation', $toStation);
  1037. if (!empty($filterRules) && !is_null($filterRules)){
  1038. $filterRules = json_decode($filterRules);
  1039. foreach($filterRules as $rule){
  1040. $rule = get_object_vars($rule);
  1041. $field = $rule['field'];
  1042. $fieldPlace = str_replace(".", "", $field);
  1043. $op = $rule['op'];
  1044. $value = $rule['value'];
  1045. if (!empty($value)){
  1046. if ($op == 'contains'){
  1047. $qb->andWhere($field." LIKE :".$fieldPlace);
  1048. $qb->setParameter($fieldPlace, '%'.$value.'%');
  1050. } else if($op == 'equal'){
  1051. if($value > 0) {
  1052. $qb->andWhere($field."=".$value);
  1053. // $qb->setParameter($field, $value);
  1054. }
  1056. }else if($op == 'less'){
  1057. if($value > 0) {
  1058. $qb->andWhere($field."<".$value);
  1059. // $qb->setParameter($field, $value);
  1060. }
  1062. }else if($op == 'greater'){
  1063. if($value > 0) {
  1064. $qb->andWhere($field.">".$value);
  1065. // $qb->setParameter($field, $value);
  1066. }
  1068. }
  1069. }
  1070. }
  1071. }
  1073. try {
  1074. return $qb->getQuery()
  1075. ->getSingleScalarResult();
  1077. } catch (NoResultException $e) {
  1079. } catch (NonUniqueResultException $e) {
  1080. }
  1081. }
  1086. public function findCollectedParcels($filterRules, $stationTo, $offset, $rows) {
  1089. $conn = $this->getEntityManager()->getConnection();
  1091. $additionalFilter = '';
  1092. if (!empty($filterRules)){
  1093. $filterRules = json_decode($filterRules);
  1094. foreach($filterRules as $rule){
  1095. $rule = get_object_vars($rule);
  1096. $field = $rule['field'];
  1097. // $fieldPlace = str_replace(".", "", $field);
  1098. $op = $rule['op'];
  1099. $value = $rule['value'];
  1100. if ($value == 0 || !empty($value)){
  1101. if ($op == 'contains'){
  1102. $additionalFilter .= " AND ".$field." like'%".$value."%'";
  1103. } else if($op == 'equal'){
  1104. if($field == 'a.created_at')
  1105. $additionalFilter .= " AND cast(".$field." as date) = cast('${value}' as date)";
  1106. else
  1107. $additionalFilter .= " AND ".$field."= ${value}";
  1109. }else if($op == 'less'){
  1110. if($field == 'a.created_at')
  1111. $additionalFilter .= " AND cast(".$field." as date) > cast('${value}' as date)";
  1112. else
  1113. $additionalFilter .= " AND ".$field." < ${value}";
  1114. }else if($op == 'greater'){
  1115. if($field == 'a.created_at')
  1116. $additionalFilter .= " AND cast(".$field." as date) > cast('${value}' as date)";
  1117. else
  1118. $additionalFilter .= " AND ".$field." > ${value}";
  1120. }else if($op == 'greater_equal'){
  1121. if($field == 'a.created_at')
  1122. $additionalFilter .= " AND cast(".$field." as date) >= cast('${value}' as date)";
  1123. else
  1124. $additionalFilter .= " AND ".$field." >= ${value}";
  1125. }
  1126. }
  1127. }
  1128. }
  1132. $sql = "SELECT b.way_bill_id as waybill, a.delivery_id, e.sender_name,e.sender_phone_number,e.receiver_name,e.receiver_phone_number,
  1133. h.station_name,b.description, c.parcel_count
  1134. FROM delivery_parcel a
  1135. JOIN parcel b on a.parcel_id = b.id
  1136. JOIN delivery c ON a.delivery_id = c.id
  1137. JOIN way_bill e ON e.id = b.way_bill_id
  1138. JOIN transaction f ON f.way_bill_id = e.id
  1139. JOIN station h ON h.id = e.from_station_id
  1140. WHERE c.is_received = 1 AND c.is_cancelled = 0 AND e.is_collected = 1 AND c.is_verified = 1 AND c.destination = :station ${additionalFilter} ORDER BY e.id DESC LIMIT ${offset},${rows}";
  1142. $sql_count = "EXPLAIN SELECT count(a.id) as total_count
  1143. FROM delivery_parcel a
  1144. JOIN parcel b on a.parcel_id = b.id
  1145. JOIN delivery c ON a.delivery_id = c.id
  1146. JOIN delivery_vehicle d ON a.delivery_id = d.delivery_id
  1147. JOIN way_bill e ON e.id = b.way_bill_id
  1148. JOIN transaction f ON f.way_bill_id = e.id
  1149. JOIN collected_parcel g ON g.waybill_id = e.id
  1150. JOIN station h ON h.id = e.from_station_id
  1151. WHERE c.is_received = true AND c.is_cancelled = false AND e.is_collected = true AND c.is_verified = true AND c.destination = :station ${additionalFilter} ORDER BY e.id DESC LIMIT ${offset},${rows}";
  1154. try {
  1155. $stmt = $conn->prepare($sql);
  1156. $results = $stmt->executeQuery(['station' => $stationTo])->fetchAllAssociative();
  1158. $stmt = $conn->prepare($sql_count);
  1159. $resultCount = $stmt->executeQuery(['station' => $stationTo])->fetchAssociative();
  1161. // dump($resultCount); die;
  1163. return [
  1164. 'results' => $results,
  1165. 'total_count' => $resultCount['rows']
  1166. ];
  1167. } catch (Exception | \Doctrine\DBAL\Exception $e) {
  1169. return null;
  1170. }
  1172. }
  1173. }