protected function createAdapter() { $q = new Am_Query(new CcRebillTable()); $q->clearFields(); $q->groupBy('rebill_date'); $q->addField('rebill_date'); $q->addField('(1)', 'is_log'); $q->addField('COUNT(t.rebill_date)', 'total'); $q->addField('SUM(IF(t.status=0, 1, 0))', 'status_0'); $q->addField('SUM(IF(t.status=1, 1, 0))', 'status_1'); $q->addField('SUM(IF(t.status=2, 1, 0))', 'status_2'); $q->addField('SUM(IF(t.status=3, 1, 0))', 'status_3'); $q->addField('SUM(IF(t.status=4, 1, 0))', 'status_4'); $u = new Am_Query(new InvoiceTable(), 'i'); $u->addWhere('i.paysys_id IN (?a)', $this->getPlugins()); $u->groupBy('rebill_date'); $u->clearFields()->addField('i.rebill_date'); $u->addField('(0)', 'is_log'); $u->addField('COUNT(i.invoice_id)', 'total'); for ($i = 1; $i < 6; $i++) { $u->addField('(NULL)'); } $u->leftJoin('?_cc_rebill', 't', 't.rebill_date=i.rebill_date'); $u->addWhere('i.rebill_date IS NOT NULL'); $u->addWhere('t.rebill_date IS NULL'); $q->addUnion($u); $q->addOrder('rebill_date', true); return $q; }
/** * @return Am_Query $query; */ function getMainDataSource() { $ds = new Am_Query($this->getDi()->invoicePaymentTable); $ds->leftJoin('?_user', 'u', 't.user_id = u.user_id')->leftJoin('?_data', 'user_country', 't.invoice_id = user_country.id and `table` = "invoice" and `key`="tax_user_country"')->leftJoin('?_invoice_refund', 'refund', 't.invoice_payment_id = refund.invoice_payment_id')->leftJoin('?_invoice', 'invoice', 't.invoice_id = invoice.invoice_id')->addField('if(user_country.value is null, if(u.country is null, "", u.country), user_country.value)', 'country')->addField('invoice.tax_rate')->addField('round(sum(if(refund.amount, t.amount-refund.amount, t.amount)/t.base_currency_multi),2)', 'sales_amount')->addField('round(sum(if(refund.amount, (t.amount-refund.amount)*if(t.tax, t.tax, 0)/t.amount, if(t.tax, t.tax, 0))/t.base_currency_multi),2)', 'tax_amount')->addField('round(sum(if(refund.amount, (t.amount-refund.amount) - (t.amount-refund.amount)*if(t.tax, t.tax, 0)/t.amount, t.amount-if(t.tax, t.tax, 0))/t.base_currency_multi),2)', 'sales_without_tax_amount')->addField('concat(if(country is null, "", country), "-", if(invoice.tax_rate is null, "", invoice.tax_rate))', 'country_rate'); $ds->groupBy('country_rate', ''); $ds->addOrder('country'); return $ds; }
public function createGrid() { $ds = new Am_Query($this->getDi()->productTable); $ds->addWhere('t.is_archived = ?', 0); $ds->addOrder('sort_order')->addOrder('title'); $grid = new Am_Grid_Editable('_product', ___("Products"), $ds, $this->_request, $this->view); $grid->setRecordTitle(___('Product')); $grid->actionAdd(new Am_Grid_Action_Group_ProductEnable(false)); $grid->actionAdd(new Am_Grid_Action_Group_ProductEnable(true)); $grid->actionAdd(new Am_Grid_Action_Group_ProductAssignCategory(false)); $grid->actionAdd(new Am_Grid_Action_Group_ProductAssignCategory(true)); $grid->actionAdd(new Am_Grid_Action_Group_ChangeOrder())->setTitle(___('Change Order')); $grid->actionAdd(new Am_Grid_Action_Group_Archive(true)); $grid->addField(new Am_Grid_Field('product_id', '#', true, '', null, '1%')); $grid->addField(new Am_Grid_Field('title', ___('Title'), true, '', null, '50%'))->setGetFunction(function ($r, $g, $f) { return strip_tags($r->{$f}); }); if ($this->getDi()->db->selectCell("SELECT COUNT(*) FROM ?_product_product_category")) { $grid->addField(new Am_Grid_Field('pgroup', ___('Product Categories'), false))->setRenderFunction(array($this, 'renderPGroup')); } $grid->addField(new Am_Grid_Field('terms', ___('Billing Terms'), false))->setRenderFunction(array($this, 'renderTerms')); if ($this->getDi()->plugins_tax->getEnabled()) { $grid->addField(new Am_Grid_Field('tax_group', ___('Tax'))); $grid->actionAdd(new Am_Grid_Action_LiveCheckbox('tax_group'))->setValue(IProduct::ALL_TAX)->setEmptyValue(IProduct::NO_TAX); } $grid->actionGet('edit')->setTarget('_top'); $grid->actionDelete('delete'); $grid->actionAdd(new Am_Grid_Action_Archive('delete', 1)); $grid->actionAdd(new Am_Grid_Action_LiveEdit('title')); $grid->actionAdd(new Am_Grid_Action_Sort_Product()); $grid->setFormValueCallback('start_date', array('RECORD', 'getStartDate'), array('RECORD', 'setStartDate')); $grid->setFormValueCallback('require_other', array('RECORD', 'unserializeList'), array('RECORD', 'serializeList')); $grid->setFormValueCallback('prevent_if_other', array('RECORD', 'unserializeList'), array('RECORD', 'serializeList')); $grid->addCallback(Am_Grid_Editable::CB_AFTER_SAVE, array($this, 'afterSave')); $grid->addCallback(Am_Grid_Editable::CB_VALUES_TO_FORM, array($this, 'valuesToForm')); $grid->addCallback(Am_Grid_ReadOnly::CB_TR_ATTRIBS, array($this, 'getTrAttribs')); $grid->setForm(array($this, 'createForm')); $grid->setFilter(new Am_Grid_Filter_Product()); $grid->setEventId('gridProduct'); $grid->actionAdd(new Am_Grid_Action_Url('categories', ___('Edit Categories'), REL_ROOT_URL . '/admin-product-categories'))->setType(Am_Grid_Action_Abstract::NORECORD)->setTarget('_top')->setCssClass('link')->setPrivilegeId('edit'); $grid->actionAdd(new Am_Grid_Action_Url('upgrades', ___('Manage Product Upgrade Paths'), REL_ROOT_URL . '/admin-products/upgrades'))->setType(Am_Grid_Action_Abstract::NORECORD)->setTarget('_top')->setCssClass('link')->setPrivilegeId('edit'); $grid->actionAdd(new Am_Grid_Action_CopyProduct())->setTarget('_top'); $ar_count = $this->getArchivedCount(1); if ($ar_count) { $grid->actionAdd(new Am_Grid_Action_Url('archived', ___("Archived products") . " ({$ar_count})", REL_ROOT_URL . '/admin-products/archived'))->setType(Am_Grid_Action_Abstract::NORECORD)->setTarget('_top')->setCssClass('link')->setPrivilegeId('browse'); } return $grid; }
protected function getQuery() { $q = new Am_Query($this->getDi()->uploadTable); $q->addOrder('name'); if ($this->prefix) { $q->addWhere('prefix=?', $this->prefix); } return $q; }
/** * return query object with category filter applied if specified * if parameters === 0, it selects products not assigned to any categories * if parameter === null, it selects products regardless of categories * @param int $product_category_id * @param bool|array $include_hidden Include products from hidden categories, * in case of array include hidden categories only with given in array codes. * @return Am_Query */ function createQuery($product_category_id = null, $include_hidden = true, $scope = false) { $q = new Am_Query($this, 'p'); $q->addOrder('sort_order')->addOrder('title'); $q->addWhere('p.is_disabled=0'); if ($scope) { $q->addWhere('ppc.product_category_id IN (?a)', $scope); } if ($product_category_id > 0) { $q->innerJoin('?_product_product_category', 'ppc', 'ppc.product_id = p.product_id AND ppc.product_category_id=' . intval($product_category_id)); } elseif ((string) $product_category_id === '0') { $q->leftJoin('?_product_product_category', 'ppc', 'ppc.product_id = p.product_id')->addHaving('count(ppc.product_category_id)=0'); } elseif (is_array($include_hidden)) { $q->leftJoin('?_product_product_category', 'ppc', 'ppc.product_id = p.product_id')->leftJoin('?_product_category', 'pc', 'pc.product_category_id = ppc.product_category_id')->addHaving('sum(if(pc.code>"" AND pc.code NOT IN (?a), 1, 0)) =0', $include_hidden); } elseif (!$include_hidden) { $q->leftJoin('?_product_product_category', 'ppc', 'ppc.product_id = p.product_id')->leftJoin('?_product_category', 'pc', 'pc.product_category_id = ppc.product_category_id')->addHaving('sum(if(pc.code>"", 1, 0)) =0'); } return $q; }