public function getPOLineRelatedQuery($params = [], $modelline = [], $groupBy = null)
{
$query = new Query();
if ($params['partner_id']) {
$partner_id = $params['partner_id'];
} else {
$partner_id = '0';
}
if ($modelline) {
if ($modelline['product_id']) {
$product = $modelline['product_id'];
} else {
$product = '0';
}
} else {
$product = $params['product_id'];
}
if ($params['state']) {
$state = $params['state'];
} else {
$state = '0';
}
if ($params['date_order']) {
$dattefrom = $params['date_order'];
$dateto = $params['duedate'];
} else {
$dattefrom = '0';
$dateto = '0';
}
// if($params['pricelist']){
// if(is_array($params['pricelist'])){
// $pricelist=implode(",", $params['pricelist']);
// }else{
// $pricelist=$params['pricelist'];
// }
// }else{
// $pricelist='0';
// }
if ($groupBy) {
if (is_array($params['pricelist'])) {
$pricelist = implode(",", $params['pricelist']);
} else {
$pricelist = $params['pricelist'];
}
}
if ($groupBy) {
if ($groupBy == 'partner') {
$query->select(['CONCAT("pol"."partner_id", \'/\',"pid"."id",\'/\',\'' . $product . '\',\'/\',\'' . $state . '\',\'/\',\'' . $dattefrom . '\',\'/\',\'' . $dateto . '\') as id,
rp.name as partner,
SUM(pol.price_unit*pol.product_qty) as total,
pid.name as pricelist']);
}
} else {
$query->select('
pol.id as id,
pol.partner_id as partner_id,
po.date_order as date_order,
po.name as no_po,
pol.name as pol_desc,
rp.name as partner,
pol.product_id as product_id,
pp.name_template as product,
pol.price_unit as price_unit,
pol.state as state,
pol.product_qty as product_qty,
pu.name as uom,
pid.name as pricelist,
(pol.product_qty*pol.price_unit) as total,
');
}
$query->from('purchase_order_line as pol')->join('LEFT JOIN', 'purchase_order as po', 'po.id=pol.order_id')->join('LEFT JOIN', 'product_pricelist as pid', 'pid.id=po.pricelist_id')->join('LEFT JOIN', 'product_product as pp', 'pp.id=pol.product_id')->join('LEFT JOIN', 'product_uom as pu', 'pu.id=pol.product_uom')->join('LEFT JOIN', 'res_partner as rp', 'rp.id=pol.partner_id');
if ($groupBy) {
if ($groupBy == 'partner') {
$query->groupBy(['pol.partner_id', 'rp.name', 'pid.id']);
$query->orderBy('rp.name ASC');
}
}
if (isset($params['partner_id']) && $params['partner_id']) {
if ($params['partner_id'] != '0') {
$query->andWhere(['pol.partner_id' => explode(',', $params['partner_id'])]);
}
}
if (isset($modelline['name']) && $modelline['name']) {
$query->andWhere(['ilike', 'pol.name', $modelline['name']]);
// die();
}
if (isset($modelline['product_id']) && $modelline['product_id']) {
if ($modelline['product_id'] != '0') {
$query->andWhere(['pol.product_id' => explode(',', $modelline['product_id'])]);
}
}
if (isset($params['state']) && $params['state']) {
if ($params['state'] == "purchased") {
$cekstate = 'confirmed, approved, done';
if ($params['state'] != '0') {
$query->andWhere(['pol.state' => explode(',', $cekstate)]);
}
} else {
if ($params['state'] != '0') {
$to_state = [];
$exps = explode(',', urldecode($params['state']));
foreach ($exps as $exp) {
if ($exp == 'purchased') {
$to_state[] = 'confirmed';
$to_state[] = 'approved';
$to_state[] = 'done';
} else {
$to_state[] = $exp;
}
}
$query->andWhere(['pol.state' => $to_state]);
}
}
} else {
$query->andWhere(['in', 'pol.state', ['confirmed', 'approved', 'done']]);
}
if (isset($params['date_order']) && $params['date_order']) {
if ($params['date_order'] != '0') {
$query->andWhere(['>=', 'po.date_order', $params['date_order']]);
$query->andWhere(['<=', 'po.date_order', $params['duedate']]);
}
}
if (isset($params['pricelist'])) {
$query->andWhere(['po.pricelist_id' => $params['pricelist']]);
}
if (!$groupBy) {
$query->addOrderBy(['po.date_order' => SORT_DESC]);
}
return $query;
}
public function testOrder()
{
$query = new Query();
$query->orderBy('team');
$this->assertEquals(['team' => SORT_ASC], $query->orderBy);
$query->addOrderBy('company');
$this->assertEquals(['team' => SORT_ASC, 'company' => SORT_ASC], $query->orderBy);
$query->addOrderBy('age');
$this->assertEquals(['team' => SORT_ASC, 'company' => SORT_ASC, 'age' => SORT_ASC], $query->orderBy);
$query->addOrderBy(['age' => SORT_DESC]);
$this->assertEquals(['team' => SORT_ASC, 'company' => SORT_ASC, 'age' => SORT_DESC], $query->orderBy);
$query->addOrderBy('age ASC, company DESC');
$this->assertEquals(['team' => SORT_ASC, 'company' => SORT_DESC, 'age' => SORT_ASC], $query->orderBy);
}
public function getSOLineRelatedQuery($params = [], $groupBy = null)
{
$query = new Query();
if ($params['productcategory']) {
if (is_array($params['productcategory'])) {
$category = implode(",", $params['productcategory']);
} else {
$category = $params['productcategory'];
}
} else {
$category = '0';
}
if ($params['partner']) {
$partner = $params['partner'];
} else {
$partner = '0';
}
if ($params['product']) {
$product = $params['product'];
} else {
$product = '0';
}
if ($params['state']) {
$state = $params['state'];
} else {
$state = '0';
}
if ($params['date_from']) {
$dattefrom = $params['date_from'];
$dateto = $params['date_to'];
} else {
$dattefrom = '0';
$dateto = '0';
}
if ($params['pricelist']) {
if (is_array($params['pricelist'])) {
$pricelist = implode(",", $params['pricelist']);
} else {
$pricelist = $params['pricelist'];
}
} else {
$pricelist = '0';
}
// jika group
if ($groupBy) {
$query->select(['CONCAT("pc"."id", \'/\', "pid"."id", \'/\', "pc"."name", \'/\', "pid"."name",\'/\',\'' . $category . '\',\'/\',\'' . $partner . '\',\'/\',\'' . $product . '\',\'/\',\'' . $pricelist . '\',\'/\',\'' . $state . '\',\'/\',\'' . $dattefrom . '\',\'/\',\'' . $dateto . '\') as id,
pc.name as category,
SUM(sol.price_unit*sol.product_uom_qty) as total,
pid.name as pricelist']);
} else {
$query->select('
sol.id as id,
so.partner_id as partner_id,
so.date_order as date_order,
so.name as no_po,
rp.name as partner,
sol.product_id as product_id,
pp.name as product,
sol.price_unit as price_unit,
sol.state as state,
pc.name as category,
pid.name as pricelist,
sol.product_uom_qty as qty,
sol.name as product_desc,
(sol.product_uom_qty*sol.price_unit) as total,
so.name as so_no
');
}
$query->from('sale_order_line as sol')->join('LEFT JOIN', 'sale_order as so', 'so.id=sol.order_id')->join('LEFT JOIN', 'product_template as pp', 'pp.id=sol.product_id')->join('LEFT JOIN', 'res_partner as rp', 'rp.id=so.partner_id')->join('LEFT JOIN', 'product_category as pc', 'pc.id=pp.categ_id')->join('LEFT JOIN', 'product_pricelist as pid', 'pid.id=so.pricelist_id');
if ($groupBy) {
$query->groupBy(['pc.id', 'pid.id']);
}
if (isset($params['partner']) && $params['partner']) {
if ($params['partner'] != '0') {
$query->andWhere(['so.partner_id' => explode(',', $params['partner'])]);
}
}
if (isset($params['productcategory']) && $params['productcategory']) {
if ($params['productcategory'] != '0') {
if (is_array($params['productcategory'])) {
$query->andWhere(['pp.categ_id' => explode(',', implode(",", $params['productcategory']))]);
} else {
$query->andWhere(['pp.categ_id' => explode(',', $params['productcategory'])]);
}
}
}
if (isset($params['product']) && $params['product']) {
if ($params['product'] != '0') {
$query->andWhere(['sol.product_id' => explode(',', $params['product'])]);
}
}
if (isset($params['pricelist']) && $params['pricelist']) {
if ($params['pricelist'] != '0') {
if (is_array($params['pricelist'])) {
$query->andWhere(['so.pricelist_id' => explode(',', implode(",", $params['pricelist']))]);
} else {
$query->andWhere(['so.pricelist_id' => explode(',', $params['pricelist'])]);
}
}
}
// if(isset($params['state']) && $params['state']){
// if($params['state']!='0')
// {
// $query->andWhere(['sol.state'=>explode(',',$params['state'])]);
// }
// }
if (isset($params['state']) && $params['state']) {
if ($params['state'] == "order") {
$cekstate = 'confirmed, approved, done';
if ($params['state'] != '0') {
$query->andWhere(['sol.state' => explode(',', $cekstate)]);
}
} else {
if ($params['state'] != '0') {
$query->andWhere(['sol.state' => explode(',', $params['state'])]);
}
}
} else {
$query->andWhere(['in', 'sol.state', ['confirmed', 'approved', 'done']]);
}
if (isset($params['date_from']) && $params['date_from']) {
if ($params['date_from'] != '0') {
$query->andWhere(['>=', 'so.date_order', $params['date_from']]);
$query->andWhere(['<=', 'so.date_order', $params['date_to']]);
}
}
$query->andWhere(['not', ['sol.product_id' => null]]);
if (!$groupBy) {
$query->addOrderBy(['so.date_order' => SORT_DESC]);
}
return $query;
}