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;
 }
Пример #2
0
 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);
 }
Пример #3
0
    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;
    }