public function searchReportQuotation($params, $pageSize = 20)
{
$query = SaleOrder::find();
$query->joinWith(['partner', 'group', 'user', 'pricelist']);
$dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => $pageSize]]);
if (!($this->load($params) && $this->validate())) {
return $dataProvider;
}
if ($this->tanggal_awal && $this->tanggal_akhir != null) {
$query->where(['between', 'sale_order.create_date', $this->tanggal_awal, $this->tanggal_akhir]);
}
if ($this->pricelist_id != null) {
$query->andFilterWhere(['ilike', 'product_pricelist.name', $this->pricelist_id]);
}
if ($this->tag_group != null) {
$query->andFilterWhere(['or ilike', 'group_sales.name', $this->tag_group]);
}
if ($this->tag_user != null) {
$query->andFilterWhere(['or ilike', 'res_users.login', $this->tag_user]);
}
if ($this->tag_partner != null) {
$query->andFilterWhere(['or ilike', 'res_partner.display_name', $this->tag_partner]);
}
return $dataProvider;
}
public function getSaleOrder()
{
return $this->hasOne(SaleOrder::className(), ['id' => 'sale_id'])->via('prepare');
}
/**
* [prepareCsvData description]
* @param [array] $invoices array result of model->all()
* @return [type] [description]
*/
private function prepareCsvInvoiceData(array $invoices)
{
$res = [];
// var_dump($invoices);
foreach ($invoices as $inv) {
// echo $inv['id'].'/';
// var_dump($inv['faktur_pajak_no']);
if ($inv['state'] == 'draft' || $inv['state'] == 'cancel' || $inv['state'] == 'submited') {
// continue to next pointer
// we dont export draft invoice
continue;
}
if ($inv['type'] == 'out_invoice') {
$indexArr = explode('/', $inv['kwitansi'])[0];
$rate = $inv['currency_id'] == 13 ? 1 : $inv['pajak'];
$all_tax_code = substr($inv['faktur_pajak_no'], 0, 3);
$tax_code_type = substr($inv['faktur_pajak_no'], 0, 2);
$tax_replace_code = 0;
$tax_mod = $all_tax_code % 10;
if ($tax_mod == 1) {
// faktur pajak pengganti
$tax_replace_code = 1;
}
$pure_faktur_code = substr(preg_replace('/[\\s\\W]+/', '', $inv['faktur_pajak_no']), 3, strlen(preg_replace('/[\\s\\W]+/', '', $inv['faktur_pajak_no'])));
$exp_tax_date = explode('-', $inv['date_invoice']);
$tax_date = \DateTime::createFromFormat('Y-m-d', $inv['date_invoice']);
// "FK","09","0","0011578000001","6","2015","18/06/2015","010621191092000","INDOCEMENT TUNGGAL PRAKARSA TBK PT","Wisma Indocement Lt. 13 Blok - No.- RT:- RW:- Kel.- Kec.- Kota/Kab.- - 12910","56750000","5675000","0","","0","0","0","0","78049887/SBM/V/2015"
// JIKA FAKTUR BERBEDA DENGAN ALAMAT INVOICE
// USER HARUS ISI ALAMAT FAKTUR DI KOLOM FIELD FAKTUR ADDRESS
if ($inv['faktur_address']) {
$partner = isset($inv['fakturAddress']['parent']) ? $inv['fakturAddress']['parent'] : $inv['fakturAddress'];
$fakturAddress = $inv['fakturAddress'];
// $partner = $inv['fakturAddress'];
$iAddr = $fakturAddress['street'] . ', ' . $fakturAddress['street2'] . ' ' . $fakturAddress['city'] . ', ' . (isset($fakturAddress['state']['name']) ? $fakturAddress['state']['name'] : '') . ($fakturAddress['zip'] ? ' - ' . $fakturAddress['zip'] : "");
} else {
$partner = isset($inv['partner']['parent']) ? $inv['partner']['parent'] : $inv['partner'];
$iAddr = $inv['partner']['street'] . ', ' . $inv['partner']['street2'] . ' ' . $inv['partner']['city'] . ', ' . (isset($inv['partner']['state']['name']) ? $inv['partner']['state']['name'] : '') . ($inv['partner']['zip'] ? ' - ' . $inv['partner']['zip'] : "");
}
$this->validateCustomerData($partner);
$amount_untaxed = floor($this->convertIdr($inv['amount_untaxed'], $rate));
$amount_tax = floor(10 / 100 * $amount_untaxed);
$expPartnerName = explode(',', $partner['name']);
if (is_array($expPartnerName) && isset($expPartnerName[1])) {
$count = count($expPartnerName);
if ($count > 2) {
$partnerName = trim($expPartnerName[$count - 1]);
unset($expPartnerName[$count - 1]);
$sPartnerName = implode(",", $expPartnerName);
$partnerName .= ". " . trim($sPartnerName);
} else {
$partnerName = trim($expPartnerName[1]) . '.' . trim($expPartnerName[0]);
}
} else {
$partnerName = $inv['partner']['name'];
}
// echo $partnerName;
$res['OUT'][$indexArr]['fk'] = ['FK', $tax_code_type, $tax_replace_code, $pure_faktur_code, isset($inv['tax_period']) && $inv['tax_period'] ? $inv['tax_period'] : $tax_date->format('n'), $tax_date->format('Y'), $tax_date->format('d/m/Y'), preg_replace('/[\\s\\W]+/', '', $partner['npwp']), $partnerName, $iAddr, $this->convertIdr($inv['amount_untaxed'], $rate), $this->convertIdr($inv['amount_tax'], $rate), '0', $tax_code_type == 7 ? 1 : '', $inv['payment_for'] == 'dp' ? '1' : ($inv['payment_for'] == 'completion' ? '2' : '0'), $inv['payment_for'] == 'dp' ? $amount_untaxed : ($inv['payment_for'] == 'completion' ? $amount_untaxed : '0'), $inv['payment_for'] == 'dp' ? $amount_tax : ($inv['payment_for'] == 'completion' ? $amount_tax : '0'), '0', 'Invoice No : ' . $inv['kwitansi'] . "\nOrder No : " . $inv['origin'] . "\nOrder Ref : " . $inv['name'] . ($inv['pajak'] > 1 ? "\nTax Rate : " . Yii::$app->numericLib->indoStyle($inv['pajak']) . "\nKMK : " . $inv['kmk'] : '')];
// "FAPR","PT SINCHAN","JL PAHLAWAN BERTOPENG BLOK MATAHARI NO.11, KIOTO RT: 1 RW: 14 JAKARTA",,,,
$res['OUT'][$indexArr]['fapr'] = ['FAPR', 'PT. SUPRABAKTI MANDIRI', 'Jl. Danau Sunter Utara Blok. A No. 9 Tanjung Priok - Jakarta Utara 14350', '', '', '', ''];
// $invLines = $this->checkLineDiscount($inv['accountInvoiceLines']);
$invLines = $inv['accountInvoiceLines'];
// EACH LINE ITEM
if ($inv['payment_for']) {
// IF PAYMENT FOR DP OR COMPLETION THEN ALL SALE ORDER ITEM WILL BE RENDERED
// check from sale order invoice
$soRel = \app\models\SaleOrderInvoiceRel::find()->where(['invoice_id' => $inv['id']])->with(['order', 'order.saleOrderLines', 'order.saleOrderLines.product', 'order.pricelist', 'order.pricelist.currency'])->asArray()->one();
if ($soRel) {
$soInv = $soRel['order'];
} else {
// check if invoice made from picking
if (count($inv['stockPickings']) > 0) {
// if do
$sid = [];
$soid = null;
foreach ($inv['stockPickings'] as $no => $pick) {
$sid[$pick['sale_id']] = $no++;
$soid = $pick['sale_id'];
}
if (count($sid) == 1) {
$soInv = \app\models\SaleOrder::find()->where(['id' => $soid])->with(['saleOrderLines', 'saleOrderLines.product', 'pricelist', 'pricelist.currency'])->asArray()->one();
} else {
// LOOP FROM INVOICE LINES
foreach ($invLines as $item) {
// add item line
$render = $this->prepareFromInvLine($item, $rate);
$discountTotal += $render[6];
$dppTotal += $render[7];
$ppnTotal += $render[8];
$res['OUT'][$indexArr]['of'][] = $render;
}
$soInv = false;
}
}
}
// IF ITEM TAKE FROM SALE ORDER / SO
if ($soInv) {
// if founded
// override res['OUT'] fk
// var_dump($soInv);
$currencyOnSO = $soInv['pricelist']['currency'];
// IF CURRENCY ON SO IS NOT IDR BUT IN INVOICE CURENCY IS IDR
if ($currencyOnSO['id'] != 13 and $inv['currency_id'] == 13) {
if ($inv['pajak']) {
$rate = $inv['pajak'];
} else {
throw new NotFoundHttpException('Mata Uang di Sale Order = ' . $currencyOnSO['name'] . ' Tetapi di Invoice Mata Uang yang digunakan adalah ' . $inv['currency']['name'] . ', namun Tax Rate tidak didefinisikan pada sistem. Tolong Update Tax Rate !');
}
}
$discountTotal = 0;
$dppTotal = 0;
$subtotalTotal = 0;
$totalPPN = 0;
// LOOP EACH SO RELS IN ORDER LINE RELATION
foreach ($soInv['saleOrderLines'] as $soLine) {
if (floatval($soLine['price_unit']) == 0.0) {
continue;
}
$pUnit = $this->convertIdr(round($soLine['price_unit'], 2), $rate, 2);
$discountLine = 0;
$subtotal = round($pUnit * $soLine['product_uom_qty'], 2);
if (floatval($soLine['discount']) > 0) {
// IF NOMINAL COUNTED BY DISCOUNT NOMINAL
if ($soLine['discount_nominal']) {
if ($soLine['discount']) {
$discountLine = round($soLine['discount'] / 100 * $subtotal, 2);
} else {
$discountLine = $this->convertIdr($soLine['discount_nominal'], $rate);
}
} else {
// IF NOMINAL NOT COUNTED BY PERCENTAGE
$discountLine = $soLine['discount'] / 100 * round($pUnit * $soLine['product_uom_qty'], 2);
}
// $discountLine = ($soLine['discount_nominal'])
$discountTotal += $discountLine;
} elseif (floatval($soLine['discount_nominal'])) {
$discountLine = $this->convertIdr($soLine['discount_nominal'], $rate);
$discountTotal += $discountLine;
}
$dpp = round($subtotal - $discountLine, 2);
$dppTotal += $dpp;
$ppn = 10 / 100 * $dpp;
$totalPPN += $ppn;
$res['OUT'][$indexArr]['of'][] = ['OF', (string) $soLine['product']['default_code'], (string) $soLine['product']['name_template'], $pUnit, (double) $soLine['product_uom_qty'], $subtotal, $discountLine, $dpp, $ppn, 0, '0.0'];
}
// HERE
$ppnTotal = floor($totalPPN);
$res['OUT'][$indexArr]['fk'][10] = floor($dppTotal);
$res['OUT'][$indexArr]['fk'][11] = $ppnTotal;
/*var_dump($ppnTotal);
die();*/
// if invoice currency idr then trust user input total
if ($inv['currency_id'] != 13) {
$res['OUT'][$indexArr]['fk'][15] = floor($dppTotal * ($inv['dp_percentage'] / 100));
#uang muka dpp = dppTotal * dp percentage
$res['OUT'][$indexArr]['fk'][16] = floor($res['OUT'][$indexArr]['fk'][15] * (10 / 100));
#uang muka ppn = 10% ppnTotal
}
/*var_dump($ppnTotal);
die();*/
}
} else {
$discountTotal = 0;
$dppTotal = 0;
$ppnTotal = 0;
// LOOP FROM INVOICE LINES
//
// check line from total discount
foreach ($invLines as $item) {
$render = $this->prepareFromInvLine($item, $rate);
$discountTotal += $render[6];
$dppTotal += $render[7];
$ppnTotal += $render[8];
$res['OUT'][$indexArr]['of'][] = $render;
// echo $item['price_subtotal'].'\\';
}
// var_dump($dppTotal);
$dppTotal = floor($dppTotal);
$ppnTOtal = floor($ppnTotal);
// var_dump($dppTotal);
// OUT LINE NORMAL
// LINE EFAKTUR TANPA PAYMENT FOR REF
// $outFK10 = (is_numeric( $dppTotal ) && floor( $dppTotal ) != $dppTotal ? floor(floatval($dppTotal)) : floatval($dppTotal));
$outFK10 = is_numeric($dppTotal) && floor($dppTotal) == $dppTotal ? $dppTotal : floor($dppTotal);
/*echo '$dppTotal = '.$dppTotal;
echo '<br/>'.$outFK10;*/
$res['OUT'][$indexArr]['fk'][10] = $outFK10;
/*echo $res['OUT'][$indexArr]['fk'][10];
die();*/
$res['OUT'][$indexArr]['fk'][11] = floor($ppnTotal);
// die();
}
} elseif ($inv['type'] == 'in_invoice') {
// OUT INVOICE
// SUPPLIER INVOICE
$rate = $inv['currency_id'] == 13 ? 1 : $inv['pajak'];
$tax_date = \DateTime::createFromFormat('Y-m-d', $inv['date_invoice']);
$nilai1 = 0;
$nilai2 = 0;
foreach ($inv['accountInvoiceLines'] as $LineID) {
$invoicestax = \app\models\AccountInvoiceLineTax::find()->where(['invoice_line_id' => $LineID['id']])->asArray()->all();
foreach ($invoicestax as $valtax) {
$taxacc = \app\models\AccountTax::find()->where(['id' => $valtax['tax_id']])->asArray()->one();
if (preg_match('/PPH/i', $taxacc['name'])) {
// CEK Taxes PPH atau PPN, Jika PPH maka No Excute
} else {
// CEK Taxes PPH atau PPN, Jika PPN maka Excute
$nilai1 = $nilai1 + $LineID['price_unit'] * $LineID['quantity'];
// Cek berdasarkan Account Tax yang dipilih Many2many
$nilai2 = $nilai2 + $LineID['price_unit'] * $LineID['quantity'] * $taxacc['amount'];
}
}
}
$datainv[] = ["FM", substr($inv['faktur_pajak_no'], 0, 2), substr($inv['faktur_pajak_no'], 2, 1), substr(str_replace('-', '', str_replace('.', '', $inv['faktur_pajak_no'])), 3), $tax_date->format('n'), $tax_date->format('Y'), $tax_date->format('d/m/Y'), preg_replace('/[\\s\\W]+/', '', $inv['partner']['npwp']), $inv['partner']['name'], trim($inv['partner']['street']), $this->convertIdr(round($nilai1), $rate), $this->convertIdr(round($nilai2), $rate), "0", "1"];
/*var_dump($datainv);
die();*/
$res['IN'] = $datainv;
// $res = $this->prepareIn();
}
}
// \yii\helpers\VarDumper::dump($res);
// die();
return $res;
}
/**
* @return \yii\db\ActiveQuery
*/
public function getSaleOrders()
{
return $this->hasMany(SaleOrder::className(), ['pricelist_id' => 'id']);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getOrder()
{
return $this->hasOne(SaleOrder::className(), ['id' => 'order_id']);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getTerm()
{
return $this->hasOne(SaleOrder::className(), ['id' => 'term_id']);
}
public function getOrders()
{
return $this->hasMany(SaleOrder::className(), ['id' => 'order_id'])->via('saleOrderInvoiceRels');
}
/**
* @return \yii\db\ActiveQuery
*/
public function getSaleOrders()
{
return $this->hasMany(SaleOrder::className(), ['payment_term' => 'id']);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getSaleOrders()
{
return $this->hasMany(SaleOrder::className(), ['attention' => 'id']);
}
public function actionToDone($id)
{
\Yii::$app->response->format = \yii\web\Response::FORMAT_JSON;
$res = [];
if (Yii::$app->request->isAjax) {
$model = SaleOrder::findOne($id);
$model->state = 'done';
$res = ['status' => $model->update()];
}
return $res;
}
public function actionRfq($id)
{
$this->layout = False;
$model = SaleOrder::findOne($id);
if ($model === null) {
throw new NotFoundHttpException();
}
$dataContent = [];
$dataContentModel = $model->saleOrderLines;
$no = 1;
$TermCondition_murni = preg_replace('#\\R+#', '<br/>', $model->internal_notes);
$TermCondition_enter = explode("<br/>", $TermCondition_murni);
$TermCondition = [];
foreach ($TermCondition_enter as $key => $value) {
$split_term = str_split($value, 90);
foreach ($split_term as $key_split_term => $value_split_term) {
array_push($TermCondition, $value_split_term);
}
}
$NoteMurni = preg_replace('#\\R+#', '<br/>', $model->note);
$NoteEnter = explode("<br/>", $NoteMurni);
$Note = [];
foreach ($NoteEnter as $key => $value) {
$split_note = str_split($value, 90);
foreach ($split_note as $key_split_note => $value_split_note) {
array_push($Note, $value_split_note);
}
}
// var_dump($Note);
foreach ($dataContentModel as $key => $value) {
$price_sub = number_format($value->product_uom_qty * $value->price_unit, 2);
$unit = number_format($value->price_unit, 2);
if ($value->name === null) {
$deskription_orderline = "";
} else {
$deskription_orderline = $value->name;
}
$dataContent[$key]['no'] = $no;
$dataContent[$key]['product_uom_qty'] = $value->product_uom_qty;
$dataContent[$key]['unit'] = $value->productUom->name;
$dataContent[$key]['default_code'] = $value->product->default_code;
$dataContent[$key]['name_product'] = $value->product->name_template;
$dataContent[$key]['unit_price'] = $unit;
$dataContent[$key]['price_sub'] = $price_sub;
$dataContent[$key]['deskription_orderline'] = $deskription_orderline;
$dataContent[$key]['Note'] = $Note;
$dataContent[$key]['TermCondition'] = $TermCondition;
$dataContent[$key]['material_line'] = [];
foreach ($value->materialLines as $keyM => $vM) {
if ($vM->desc === null) {
$descriptionMaterial = "";
} else {
$descriptionMaterial = $vM->desc;
}
$dataContent[$key]['material_line'][] = ['product_id' => $vM->product->name_template, 'partNumber' => $vM->product->default_code, 'descriptionMaterial' => $descriptionMaterial, 'qty' => $vM->qty, 'uom' => $vM->uom0->name];
}
$no++;
}
if ($model->partner->state === null) {
$state = "";
} else {
$state = $model->partner->state->name;
}
if ($model->partner->country === null) {
$country = "";
} else {
$country = $model->partner->country->name;
}
if ($model->attention0 !== null) {
$AttentionName = $model->attention0->name;
$AttentionPhone = $model->attention0->phone;
if ($model->attention0->fax !== null) {
$fax = $model->attention0->fax;
} else {
if ($model->partner->fax !== null) {
$fax = $model->partner->fax;
} else {
$fax = "";
}
}
if ($model->attention0->email !== null) {
$email = $model->attention0->email;
} else {
if ($model->partner->fax !== null) {
$email = $model->partner->email;
} else {
$email = "";
}
}
} else {
$fax = "";
$email = "";
$AttentionName = "";
$AttentionPhone = "";
}
$TermCondition_murni = preg_replace('#\\R+#', '<br/>', $model->internal_notes);
$TermCondition_enter = explode("<br/>", $TermCondition_murni);
$TermCondition = [];
foreach ($TermCondition_enter as $key => $value) {
// echo $value.'<br/>';
array_push($TermCondition, str_split($value, 50));
}
// var_dump($TermCondition);
$NoteMurni = preg_replace('#\\R+#', '<br/>', $model->internal_notes);
$NoteEnter = explode("<br/>", $NoteMurni);
$Note = [];
foreach ($NoteEnter as $key => $value) {
// echo $value.'<br/>';
array_push($Note, str_split($value, 50));
}
return $this->render('rfq', ['Note' => $Note, 'TermCondition' => $TermCondition, 'model' => $model, 'state' => $state, 'country' => $country, 'fax' => $fax, 'email' => $email, 'AttentionName' => $AttentionName, 'AttentionPhone' => $AttentionPhone, 'dataContent' => $dataContent]);
}