public static function read($params, $currentUser, $con) { // check role's permission $permission = RolePermissionQuery::create()->select('read_debit')->findOneById($currentUser->role_id, $con); if (!$permission || $permission != 1) { throw new \Exception('Akses ditolak. Anda tidak mempunyai izin untuk melakukan operasi ini.'); } $page = isset($params->page) ? $params->page : 0; $limit = isset($params->limit) ? $params->limit : 100; $debits = DebitQuery::create()->filterByStatus('Active')->usePurchaseQuery()->leftJoin('SecondParty')->withColumn('SecondParty.Id', 'second_party_id')->withColumn('SecondParty.Name', 'second_party_name')->withColumn('Purchase.Date', 'date')->endUse()->withColumn('CONVERT(Debit.Total, SIGNED) - CONVERT(Debit.Paid, SIGNED)', 'balance'); if (isset($params->id)) { $debits->filterById($params->id); } if (isset($params->purchase_id)) { $debits->filterByPurchaseId($params->purchase_id); } if (isset($params->second_party_id)) { $debits->usePurchaseQuery()->filterBySecondPartyId($params->second_party_id)->endUse(); } if (isset($params->second_party_name)) { $debits->usePurchaseQuery()->useSecondPartyQuery()->filterByName("%{$params->second_party_name}%")->endUse()->endUse(); } if (isset($params->debit_status)) { switch ($params->debit_status) { case 'Lunas': $debits->where('CONVERT(Debit.Total, SIGNED) - CONVERT(Debit.Paid, SIGNED) <= 0'); break; case 'Belum Lunas': $debits->where('CONVERT(Debit.Total, SIGNED) - CONVERT(Debit.Paid, SIGNED) > 0'); break; } } $debits = $debits->select(array('id', 'purchase_id', 'total', 'paid', 'second_party_id', 'second_party_name', 'date', 'balance')); foreach ($params->sort as $sorter) { $debits->orderBy($sorter->property, $sorter->direction); } $debits->orderBy('id', 'DESC'); $debits = $debits->paginate($page, $limit); $total = $debits->getNbResults(); $data = []; foreach ($debits as $debit) { $debit = (object) $debit; $debit->cash_back = $debit->balance < 0 ? abs($debit->balance) : 0; $data[] = $debit; } $results['success'] = true; $results['data'] = $data; $results['total'] = $total; return $results; }
private static function getStats($params, $currentUser, $con) { $data = []; // sales this month $start = new \DateTime(Date('Y-m-01')); $until = new \DateTime(Date('Y-m-t')); $sales = SalesQuery::create()->filterByStatus('Active')->filterBySecondPartyId($params->customer_id)->filterByDate(array('min' => $start, 'max' => $until))->withColumn('COUNT(Sales.Id)', 'sales_count')->withColumn('SUM(Sales.TotalPrice)', 'sales_total')->select(['sales_count', 'sales_total'])->find($con); $data['sales_count_this_month'] = isset($sales[0]['sales_count']) ? $sales[0]['sales_count'] : 0; $data['sales_total_this_month'] = isset($sales[0]['sales_total']) ? $sales[0]['sales_total'] : 0; // sales this year $start = new \DateTime(Date('Y-01-01')); $until = new \DateTime(Date('Y-12-31')); $sales = SalesQuery::create()->filterByStatus('Active')->filterBySecondPartyId($params->customer_id)->filterByDate(array('min' => $start, 'max' => $until))->withColumn('COUNT(Sales.Id)', 'sales_count')->withColumn('SUM(Sales.TotalPrice)', 'sales_total')->select(['sales_count', 'sales_total'])->find($con); $data['sales_count_this_year'] = isset($sales[0]['sales_count']) ? $sales[0]['sales_count'] : 0; $data['sales_total_this_year'] = isset($sales[0]['sales_total']) ? $sales[0]['sales_total'] : 0; // get current credit balance $credits = CreditQuery::create()->filterByStatus('Active')->useSalesQuery()->filterBySecondPartyId($params->customer_id)->endUse()->withColumn('CONVERT(Credit.Total, SIGNED) - CONVERT(Credit.Paid, SIGNED)', 'balance')->select(array('balance'))->find($con); $credit_total = 0; foreach ($credits as $credit) { if ($credit > 0) { $credit_total += $credit; } } $data['credit'] = $credit_total; // get current debit balance $debits = DebitQuery::create()->filterByStatus('Active')->usePurchaseQuery()->filterBySecondPartyId($params->customer_id)->endUse()->withColumn('CONVERT(Debit.Total, SIGNED) - CONVERT(Debit.Paid, SIGNED)', 'balance')->select(array('balance'))->find($con); $debit_total = 0; foreach ($debits as $debit) { if ($debit > 0) { $debit_total += $debit; } } $data['debit'] = $debit_total; $data['customer_id'] = $params->customer_id; $results['data'] = $data; return $results; }
/** * Returns a new ChildDebitQuery object. * * @param string $modelAlias The alias of a model in the query * @param Criteria $criteria Optional Criteria to build the query from * * @return ChildDebitQuery */ public static function create($modelAlias = null, Criteria $criteria = null) { if ($criteria instanceof ChildDebitQuery) { return $criteria; } $query = new ChildDebitQuery(); if (null !== $modelAlias) { $query->setModelAlias($modelAlias); } if ($criteria instanceof Criteria) { $query->mergeWith($criteria); } return $query; }
/** * Removes this object from datastore and sets delete attribute. * * @param ConnectionInterface $con * @return void * @throws PropelException * @see Debit::setDeleted() * @see Debit::isDeleted() */ public function delete(ConnectionInterface $con = null) { if ($this->isDeleted()) { throw new PropelException("This object has already been deleted."); } if ($con === null) { $con = Propel::getServiceContainer()->getWriteConnection(DebitTableMap::DATABASE_NAME); } $con->transaction(function () use($con) { $deleteQuery = ChildDebitQuery::create()->filterByPrimaryKey($this->getPrimaryKey()); $ret = $this->preDelete($con); if ($ret) { $deleteQuery->delete($con); $this->postDelete($con); $this->setDeleted(true); } }); }
/** * Get the associated ChildDebit object * * @param ConnectionInterface $con Optional Connection object. * @return ChildDebit The associated ChildDebit object. * @throws PropelException */ public function getDebit(ConnectionInterface $con = null) { if ($this->aDebit === null && ($this->debit_id !== "" && $this->debit_id !== null)) { $this->aDebit = ChildDebitQuery::create()->findPk($this->debit_id, $con); /* The following can be used additionally to guarantee the related object contains a reference to this object. This level of coupling may, however, be undesirable since it could result in an only partially populated collection in the referenced object. $this->aDebit->addPayments($this); */ } return $this->aDebit; }
private static function getStats($date, $con) { $sales = SalesQuery::create()->filterByStatus('Active')->filterByDate(array('min' => $date->start, 'max' => $date->until))->withColumn('COUNT(Sales.Id)', 'sales_count')->withColumn('SUM(Sales.TotalPrice)', 'sales_total')->withColumn('SUM(CONVERT(Sales.TotalPrice, SIGNED) - CONVERT(Sales.BuyPrice, SIGNED))', 'sales_netto')->select(['sales_total', 'sales_count'])->find($con); $data['sales_count'] = isset($sales[0]['sales_count']) ? $sales[0]['sales_count'] : 0; $data['sales_total'] = isset($sales[0]['sales_total']) ? $sales[0]['sales_total'] : 0; $data['sales_netto'] = isset($sales[0]['sales_netto']) ? $sales[0]['sales_netto'] : 0; $purchase = PurchaseQuery::create()->filterByStatus('Active')->filterByDate(array('min' => $date->start, 'max' => $date->until))->withColumn('COUNT(Purchase.Id)', 'purchase_count')->withColumn('SUM(Purchase.TotalPrice)', 'purchase_total')->select(['purchase_count', 'purchase_total'])->find($con); $data['purchase_count'] = isset($purchase[0]['purchase_count']) ? $purchase[0]['purchase_count'] : 0; $data['purchase_total'] = isset($purchase[0]['purchase_total']) ? $purchase[0]['purchase_total'] : 0; // get current credit balance $credits = CreditQuery::create()->filterByStatus('Active')->useSalesQuery()->filterByDate(array('max' => $date->until))->endUse()->withColumn('CONVERT(Credit.Total, SIGNED) - CONVERT(Credit.Paid, SIGNED)', 'balance')->select(array('balance'))->find($con); $credit_total = 0; foreach ($credits as $credit) { if ($credit > 0) { $credit_total += $credit; } } $data['credit'] = $credit_total; // get current debit balance $debits = DebitQuery::create()->filterByStatus('Active')->usePurchaseQuery()->filterByDate(array('max' => $date->until))->endUse()->withColumn('CONVERT(Debit.Total, SIGNED) - CONVERT(Debit.Paid, SIGNED)', 'balance')->select(array('balance'))->find($con); $debit_total = 0; foreach ($debits as $debit) { if ($debit > 0) { $debit_total += $debit; } } $data['debit'] = $debit_total; $results['success'] = true; $results['data'] = $data; return $results; }
/** * Performs an INSERT on the database, given a Debit or Criteria object. * * @param mixed $criteria Criteria or Debit object containing data that is used to create the INSERT statement. * @param ConnectionInterface $con the ConnectionInterface connection to use * @return mixed The new primary key. * @throws PropelException Any exceptions caught during processing will be * rethrown wrapped into a PropelException. */ public static function doInsert($criteria, ConnectionInterface $con = null) { if (null === $con) { $con = Propel::getServiceContainer()->getWriteConnection(DebitTableMap::DATABASE_NAME); } if ($criteria instanceof Criteria) { $criteria = clone $criteria; // rename for clarity } else { $criteria = $criteria->buildCriteria(); // build Criteria from Debit object } if ($criteria->containsKey(DebitTableMap::COL_ID) && $criteria->keyContainsValue(DebitTableMap::COL_ID)) { throw new PropelException('Cannot insert a value for auto-increment primary key (' . DebitTableMap::COL_ID . ')'); } // Set the correct dbName $query = DebitQuery::create()->mergeWith($criteria); // use transaction because $criteria could contain info // for more than one table (I guess, conceivably) return $con->transaction(function () use($con, $query) { return $query->doInsert($con); }); }
/** * Returns the number of related Debit objects. * * @param Criteria $criteria * @param boolean $distinct * @param ConnectionInterface $con * @return int Count of related Debit objects. * @throws PropelException */ public function countDebits(Criteria $criteria = null, $distinct = false, ConnectionInterface $con = null) { $partial = $this->collDebitsPartial && !$this->isNew(); if (null === $this->collDebits || null !== $criteria || $partial) { if ($this->isNew() && null === $this->collDebits) { return 0; } if ($partial && !$criteria) { return count($this->getDebits()); } $query = ChildDebitQuery::create(null, $criteria); if ($distinct) { $query->distinct(); } return $query->filterByPurchase($this)->count($con); } return count($this->collDebits); }
public static function update($params, $currentUser, $con) { // check role's permission $permission = RolePermissionQuery::create()->select('update_purchase')->findOneById($currentUser->role_id, $con); if (!$permission || $permission != 1) { throw new \Exception('Akses ditolak. Anda tidak mempunyai izin untuk melakukan operasi ini.'); } $purchase = PurchaseQuery::create()->findOneById($params->id, $con); if (!$purchase) { throw new \Exception('Data tidak ditemukan'); } $purchase->setDate($params->date)->setSecondPartyId($params->second_party_id)->setTotalPrice($params->total_price)->setPaid($params->paid)->setNote($params->note)->setStatus('Active')->save($con); // check wether this transaction is debit or not, then if yes, create new debit record $balance = $params->paid - $params->total_price; if ($balance < 0) { $debit = DebitQuery::create()->filterByPurchaseId($purchase->getId())->findOne($con); if (!$debit) { $debit = new Debit(); } $debit->setPurchaseId($purchase->getId())->setTotal(abs($balance))->setStatus('Active')->save($con); } else { $debit = DebitQuery::create()->filterByPurchaseId($purchase->getId())->findOne($con); if ($debit) { $debit->setPurchaseId($purchase->getId())->setTotal(0)->setStatus('Canceled')->save($con); } } $products = json_decode($params->products); foreach ($products as $product) { // check whether current detail iteration is brand new or just updating the old one $newDetail = PurchaseDetailQuery::create()->findOneById($product->id); if (!$newDetail) { $isNew = true; $newDetail = new PurchaseDetail(); } else { $isNew = false; $oldDetail = $newDetail->copy(); } $newDetail->setPurchaseId($purchase->getId())->setStockId($product->stock_id)->setAmount($product->amount)->setTotalPrice($product->total_price)->setStatus('Active')->save($con); // make stock dance ^_^ if ($isNew) { $stock = StockQuery::create()->findOneById($newDetail->getStockId(), $con); if ($stock->getUnlimited() == false) { $stock->setAmount($stock->getAmount() + $product->amount)->save($con); } } else { // check whether updated detail stock is the same old one or not if ($newDetail->getStockId() == $oldDetail->getStockId()) { // and if actually the same, then set stock amount like this // amount = currentAmount - oldTransAmount + newTransAmount $stock = StockQuery::create()->findOneById($newDetail->getStockId(), $con); if ($stock->getUnlimited() == false) { $stock->setAmount($stock->getAmount() - $oldDetail->getAmount() + $newDetail->getAmount())->save($con); } } else { // but if two stocks is not the same, // then take back oldTransAmount from old-stock, and give newTransAmount to new-stock $stock = StockQuery::create()->findOneById($oldDetail->getStockId(), $con); if ($stock->getUnlimited() == false) { $stock->setAmount($stock->getAmount() - $oldDetail->getAmount())->save($con); } $stock = StockQuery::create()->findOneById($newDetail->getStockId(), $con); if ($stock->getUnlimited() == false) { $stock->setAmount($stock->getAmount() + $newDetail->getAmount())->save($con); } } } $notificationId = Purchases::newPriceNotification($stock, $newDetail, $con); if ($isNew || !($notificationId == 0)) { $newDetail->setNotificationId($notificationId)->save($con); } } // if there are any sales detail removed then make sure the stocks give back something they own... 'give back amount' $removeds = PurchaseDetailQuery::create()->filterById($params->removed_id)->find($con); foreach ($removeds as $removed) { $stock = StockQuery::create()->findOneById($removed->getStockId(), $con); if ($stock->getUnlimited() == false) { $stock->setAmount($stock->getAmount() - $removed->getAmount())->save($con); } $removed->setStatus('Deleted')->save($con); $notification = $removed->getNotification(); if ($notification) { $notification->delete($con); } } $logData['params'] = $params; // log history $purchaseHistory = new PurchaseHistory(); $purchaseHistory->setUserId($currentUser->id)->setPurchaseId($params->id)->setTime(time())->setOperation('update')->setData(json_encode($logData))->save($con); $results['success'] = true; $results['id'] = $params->id; return $results; }