public function batchPayment($client_id, $paid_amount, $paid_date, $note) { $sql = "SELECT sale_id,(amount-paid) amount_to_paid\n FROM (\n SELECT s.id sale_id,s.`sale_time`,CONCAT_WS(' ',first_name,last_name) client_id,IFNULL(s.`sub_total`,0) amount,IFNULL(sa.`paid`,0) paid,IFNULL(sa.`balance`,0) balance\n FROM sale s INNER JOIN sale_amount sa ON sa.`sale_id`=s.`id` AND balance>0\n INNER JOIN `client` c ON c.`id`=s.`client_id`\n AND c.id=:client_id\n -- WHERE s.status IS NULL \n ) AS t\n GROUP BY sale_id\n ORDER BY sale_time"; $result = Yii::app()->db->createCommand($sql)->queryAll(true, array(':client_id' => $client_id)); foreach ($result as $record) { $sale_amount = SaleAmount::model()->find('sale_id=:sale_id', array(':sale_id' => $record["sale_id"])); $transaction = Yii::app()->db->beginTransaction(); try { if ($paid_amount <= $record["amount_to_paid"]) { $payment_amount = $paid_amount; $sale_amount->paid = $sale_amount->paid + $paid_amount; $sale_amount->balance = $sale_amount->total - $sale_amount->paid; $sale_amount->save(); $sale_payment = new SalePayment(); $sale_payment->sale_id = $record["sale_id"]; $sale_payment->payment_type = 'Cash'; $sale_payment->payment_amount = $payment_amount; $sale_payment->date_paid = $paid_date; $sale_payment->note = $note; $sale_payment->save(); $transaction->commit(); break; } else { $sale_amount->paid = $sale_amount->paid + $record["amount_to_paid"]; $sale_amount->balance = $sale_amount->total - $sale_amount->paid; $sale_amount->save(); $paid_amount = $paid_amount - $record["amount_to_paid"]; $payment_amount = $record["amount_to_paid"]; $sale_payment = new SalePayment(); $sale_payment->sale_id = $record["sale_id"]; $sale_payment->payment_type = 'Cash'; $sale_payment->payment_amount = $payment_amount; $sale_payment->date_paid = $paid_date; $sale_payment->note = $note; $sale_payment->save(); $transaction->commit(); } } catch (Exception $e) { $transaction->rollback(); return $e->getMessage(); } } }
public function gridPaidAmount($data, $row) { $model = SaleAmount::model()->find('sale_id=:sale_id', array(':sale_id' => $data->id)); $amount = isset($model) ? $model->total : $data->sub_total; echo $amount; }