PHP Am_Query::leftJoin方法代码示例

 public function createInvoice()
 {
     $query = new Am_Query(new InvoiceLogTable());
     $query->addField("m.login", "login");
     $query->addField("m.user_id", "user_id");
     $query->addField("i.public_id");
     $query->leftJoin("?_user", "m", "t.user_id=m.user_id");
     $query->leftJoin("?_invoice", "i", "t.invoice_id=i.invoice_id");
     $query->setOrder('log_id', 'desc');
     $g = new Am_Grid_Editable('_invoice', ___('Invoice Log'), $query, $this->getRequest(), $this->view);
     $g->setPermissionId(Am_Auth_Admin::PERM_LOGS_INVOICE);
     $userUrl = new Am_View_Helper_UserUrl();
     $g->addField(new Am_Grid_Field_Date('tm', ___('Date/Time'), true));
     $g->addField(new Am_Grid_Field('invoice_id', ___('Invoice'), true, '', array($this, 'renderInvoice'), '5%'));
     $g->addField(new Am_Grid_Field('login', ___('User'), true))->addDecorator(new Am_Grid_Field_Decorator_Link($userUrl->userUrl('{user_id}'), '_top'));
     $g->addField(new Am_Grid_Field('remote_addr', ___('IP'), true, '', null, '5%'));
     $g->addField(new Am_Grid_Field('paysys_id', ___('Paysystem'), true, '', null, '10%'));
     $g->addField(new Am_Grid_Field('title', ___('Title'), true, '', null, '25%'));
     $g->addField(new Am_Grid_Field_Expandable('details', ___('Details'), false, '', null, '25%'))->setGetFunction(array($this, 'renderInvoiceDetails'));
     $g->actionsClear();
     $g->actionAdd(new Am_Grid_Action_InvoiceRetry('retry'));
     $g->setFilter(new Am_Grid_Filter_InvoiceLog());
     $g->actionAdd(new Am_Grid_Action_Group_Callback('retrygroup', ___("Repeat Action Handling"), array('Am_Grid_Action_InvoiceRetry', 'groupCallback')));
     return $g;
 }

 function viewAction()
 {
     // display payouts list date | method | total | paid |
     $id = $this->getInt('payout_id');
     if (!$id) {
         throw new Am_Exception_InputError("Not payout_id passed");
     }
     $ds = new Am_Query($this->getDi()->affPayoutDetailTable);
     $ds->leftJoin('?_aff_payout', 'p', 'p.payout_id=t.payout_id');
     $ds->leftJoin('?_user', 'u', 't.aff_id=u.user_id');
     $ds->addField('u.*');
     $ds->addField('p.type', 'type');
     $ds->addWhere('t.payout_id=?d', $id);
     $grid = new Am_Grid_Editable('_d', ___("Payout %d Details", $id), $ds, $this->_request, $this->view);
     $grid->setPermissionId('affiliates');
     $grid->addCallback(Am_Grid_Editable::CB_RENDER_TABLE, array($this, 'addBackLink'));
     $grid->addField('email', ___('E-Mail'));
     $grid->addField('name_f', ___('First Name'));
     $grid->addField('name_l', ___('Last Name'));
     $grid->addField('type', ___('Payout Method'));
     $grid->addField('amount', ___('Amount'));
     //        $grid->addField('receipt_id', ___('Receipt Id'));
     $grid->addField('is_paid', ___('Is Paid?'));
     $grid->addField(new Am_Grid_Field_Expandable('_details', ___('Payout Details')))->setGetFunction(array($this, 'getPayoutDetails'));
     $grid->actionsClear();
     //$grid->actionAdd(new Am_Grid_Action_LiveEdit('receipt_id'));
     $grid->actionAdd(new Am_Grid_Action_PayoutMarkPaid('mark_paid', ___("Mark Paid")));
     $grid->actionAdd(new Am_Grid_Action_PayoutMarkNotPaid('mark_notpaid', ___("Mark NOT Paid")));
     $grid->runWithLayout();
     // detail payout records date | method | paid | receipt_id | aff. payout fields
 }

 function renderInvoiceCommissions(Invoice $invoice, Am_View $view)
 {
     $query = new Am_Query($this->getDi()->affCommissionTable);
     $query->leftJoin('?_invoice', 'i', 'i.invoice_id=t.invoice_id');
     $query->leftJoin('?_user', 'a', 't.aff_id=a.user_id');
     $query->leftJoin('?_product', 'p', 't.product_id=p.product_id');
     $query->addField('CONCAT(a.login, \' (\', a.name_f, \' \', a.name_l,\') [#\', a.user_id, \']\')', 'aff_name')->addField('p.title', 'product_title')->addField('IF(payout_detail_id IS NULL, \'no\', \'yes\')', 'is_paid');
     $query->setOrder('commission_id', 'desc');
     $query->addWhere('t.invoice_id=?', $invoice->pk());
     $items = $query->selectAllRecords();
     $view->comm_items = $items;
     $view->invoice = $invoice;
     return $view->render('blocks/admin-user-invoice-details.phtml');
 }

 protected function createAdapter()
 {
     $q = new Am_Query(new CcRebillTable());
     $q->clearFields();
     $q->groupBy('rebill_date');
     $q->addField('rebill_date');
     $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->groupBy('rebill_date');
     $u->clearFields()->addField('i.rebill_date');
     for ($i = 0; $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');
     return $q;
 }

 protected function createAdapter()
 {
     $q = new Am_Query(Am_Di::getInstance()->newsletterListTable);
     $q->leftJoin('?_newsletter_user_subscription', 's', 's.list_id = t.list_id AND s.is_active > 0');
     $q->addField('COUNT(s.list_id)', 'subscribed_users');
     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;
 }

 /** @return Am_Query */
 public function getQuery()
 {
     $q = new Am_Query($this->getDi()->invoicePaymentTable, 'p');
     $q->clearFields();
     $q->leftJoin('?_user', 'u', 'p.user_id=u.user_id');
     foreach ($this->getCountries() as $country) {
         $q->addField("ROUND(SUM(IF(country='{$country}', tax/base_currency_multi, 0)), 2)", 'tax_' . $country);
     }
     return $q;
 }

 function createGrid()
 {
     $query = new Am_Query($this->getDi()->fileDownloadTable);
     $query->leftJoin('?_file', 'f', 'f.file_id=t.file_id')->addField('f.title', 'title')->addWhere('user_id=?', $this->getParam('user_id'));
     $grid = new Am_Grid_Editable('_file_download', ___("File Downloads"), $query, $this->_request, $this->view);
     $grid->setPermissionId(Am_Auth_Admin::PERM_LOGS_DOWNLOAD);
     $grid->actionsClear();
     $grid->actionAdd(new Am_Grid_Action_Delete());
     $grid->actionAdd(new Am_Grid_Action_Group_Delete());
     $grid->addField(new Am_Grid_Field_Date('dattm', ___('Date/Time')));
     $grid->addField('remote_addr', ___('IP'));
     $grid->addField('title', ___('File'));
     return $grid;
 }

 public function createGrid()
 {
     $ds = new Am_Query($this->getDi()->helpdeskCategoryTable);
     $ds->leftJoin('?_admin', 'a', 't.owner_id=a.admin_id')->addField("CONCAT(a.login, ' (',a.name_f, ' ', a.name_l, ')')", 'owner');
     $ds->setOrder('sort_order');
     $grid = new Am_Grid_Editable('_helpdesk_category', ___("Ticket Categories"), $ds, $this->_request, $this->view);
     $grid->addField(new Am_Grid_Field('title', ___('Title'), true, '', null, '50%'));
     $grid->addField(new Am_Grid_Field('owner_id', ___('Owner'), true, '', array($this, 'renderOwner')));
     $grid->addField(new Am_Grid_Field_IsDisabled());
     $grid->setForm('Am_Form_Admin_HelpdeskCategory');
     $grid->addCallback(Am_Grid_Editable::CB_BEFORE_SAVE, array($this, 'beforeSave'));
     $grid->setPermissionId(Bootstrap_Helpdesk::ADMIN_PERM_CATEGORY);
     $grid->actionAdd(new Am_Grid_Action_Sort_HelpdeskCategory());
     return $grid;
 }

 public function createInvoice()
 {
     $query = new Am_Query(new InvoiceLogTable());
     $query->addField("m.login", "login");
     $query->leftJoin("?_user", "m", "t.user_id=m.user_id");
     $query->setOrder('tm', 'desc');
     $g = new Am_Grid_Editable('_invoice', ___('Invoice Log'), $query, $this->getRequest(), $this->view);
     $g->addField(new Am_Grid_Field('tm', ___('Time'), true, '', null, '10%'));
     $g->addField(new Am_Grid_Field('invoice_id', ___('Invoice'), true, '', null, '5%'));
     $g->addField(new Am_Grid_Field('login', ___('User'), true, '', null, '5%'));
     $g->addField(new Am_Grid_Field('remote_addr', ___('IP'), true, '', null, '5%'));
     $g->addField(new Am_Grid_Field('paysys_id', ___('Paysystem'), true, '', null, '10%'));
     $g->addField(new Am_Grid_Field('title', ___('Title'), true, '', null, '25%'));
     $g->addField(new Am_Grid_Field_Expandable('details', ___('Details'), false, '', null, '25%'))->setGetFunction(array($this, 'renderInvoiceDetails'));
     $g->actionsClear();
     $g->actionAdd(new Am_Grid_Action_InvoiceRetry());
     $g->setFilter(new Am_Grid_Filter_InvoiceLog());
     return $g;
 }

 public function createGrid()
 {
     $ds = new Am_Query($this->getDi()->couponBatchTable);
     $ds->addField('COUNT(c.coupon_id) AS coupons_count');
     $ds->leftJoin('?_coupon', 'c', 't.batch_id = c.batch_id');
     $ds->setOrder('batch_id', 'desc');
     $grid = new Am_Grid_Editable('_coupon', ___("Coupons Batches"), $ds, $this->_request, $this->view);
     $grid->addField('batch_id', ___('Batch ID'), true, '', null, '5%');
     $grid->addField(new Am_Grid_Field_Date('begin_date', ___('Begin Date')))->setFormatDate();
     $grid->addField(new Am_Grid_Field_Date('expire_date', ___('Expire Date')))->setFormatDate();
     $grid->addField('id_disabled', ___('Disabled?'), false, '', array($this, 'renderDisabled'), '10%');
     $grid->addField('is_recurring', ___('Recurring'), true, '', null, '5%');
     $grid->addField('discount', ___('Discount'), true, '', array($this, 'renderDiscount'), '5%');
     $grid->addField('product_ids', ___('Products'), false, '', array($this, 'renderProducts'), '25%');
     $grid->addField('comment', ___('Comment'), true, '', null, '15%');
     $grid->addField('coupons_count', ___('Coupons Count'), true, '', null, '5%');
     $grid->setForm(array($this, 'createForm'));
     $grid->actionGet('edit')->setTarget('_top');
     $grid->actionAdd(new Am_Grid_Action_Url("view", ___("View Coupons"), "javascript:amOpenCoupons(__ID__)"))->setAttribute("class", "coupons-link");
     $grid->actionAdd(new Am_Grid_Action_LiveEdit('comment'));
     $grid->setFormValueCallback('product_ids', array('RECORD', 'unserializeIds'), array('RECORD', 'serializeIds'));
     $grid->addCallback(Am_Grid_Editable::CB_BEFORE_SAVE, array($this, 'beforeSave'));
     $grid->addCallback(Am_Grid_Editable::CB_AFTER_INSERT, array($this, 'afterInsert'));
     $grid->setFilter(new Am_Grid_Filter_Coupon());
     return $grid;
 }

 public function paymentAction()
 {
     $totalFields = array();
     $query = new Am_Query($this->getDi()->invoicePaymentTable);
     $query->leftJoin('?_user', 'm', 'm.user_id=t.user_id')->addField("(SELECT GROUP_CONCAT(item_title SEPARATOR ', ') FROM ?_invoice_item WHERE invoice_id=t.invoice_id)", 'items')->addField('m.login', 'login')->addField('m.email', 'email')->addField('m.street', 'street')->addField('m.city', 'city')->addField('m.state', 'state')->addField('m.country', 'country')->addField('m.phone', 'phone')->addField('m.zip', 'zip')->addField("concat(m.name_f,' ',m.name_l)", 'name')->addField('t.invoice_public_id', 'public_id')->addWhere('t.user_id=?', $this->user_id);
     $query->setOrder("invoice_payment_id", "desc");
     $grid = new Am_Grid_Editable('_payment', ___('Payments'), $query, $this->_request, $this->view);
     $grid->actionsClear();
     $grid->addField(new Am_Grid_Field_Date('dattm', ___('Date/Time')));
     $grid->addField('invoice_id', ___('Invoice'))->setGetFunction(array($this, '_getInvoiceNum'))->addDecorator(new Am_Grid_Field_Decorator_Link('admin-user-payments/index/user_id/{user_id}#invoice-{invoice_id}', '_top'));
     $grid->addField('receipt_id', ___('Receipt'));
     $grid->addField('paysys_id', ___('Payment System'));
     array_push($totalFields, $grid->addField('amount', ___('Amount'))->setGetFunction(array($this, '_getAmount')));
     if ($this->getDi()->plugins_tax->getEnabled()) {
         array_push($totalFields, $grid->addField('tax', ___('Tax'))->setGetFunction(array($this, '_getTax')));
     }
     $grid->addField(new Am_Grid_Field_Date('refund_dattm', ___('Refunded')))->setFormatDatetime();
     $grid->addField('items', ___('Items'));
     $grid->setFilter(new Am_Grid_Filter_Payments());
     $action = new Am_Grid_Action_Export();
     $action->addField(new Am_Grid_Field('dattm', ___('Date Time')))->addField(new Am_Grid_Field('receipt_id', ___('Receipt')))->addField(new Am_Grid_Field('paysys_id', ___('Payment System')))->addField(new Am_Grid_Field('amount', ___('Amount')))->addField(new Am_Grid_Field('tax', ___('Tax')))->addField(new Am_Grid_Field_Date('refund_dattm', ___('Refunded')))->addField(new Am_Grid_Field('login', ___('Username')))->addField(new Am_Grid_Field('name', ___('Name')))->addField(new Am_Grid_Field('email', ___('Email')))->addField(new Am_Grid_Field('street', ___('Street')))->addField(new Am_Grid_Field('city', ___('City')))->addField(new Am_Grid_Field('state', ___('State')))->addField(new Am_Grid_Field('country', ___('Country')))->addField(new Am_Grid_Field('phone', ___('Phone')))->addField(new Am_Grid_Field('zip', ___('Zip Code')))->addField(new Am_Grid_Field('items', ___('Items')))->addField(new Am_Grid_Field('invoice_id', ___('Invoice')))->addField(new Am_Grid_Field('public_id', ___('Invoice (Public Id)')));
     $grid->actionAdd($action);
     if ($this->getDi()->config->get('send_pdf_invoice')) {
         $grid->actionAdd(new Am_Grid_Action_ExportPdf());
     }
     $action = $grid->actionAdd(new Am_Grid_Action_Total());
     foreach ($totalFields as $f) {
         $action->addField($f, 'ROUND(%s / base_currency_multi, 2)');
     }
     $grid->runWithLayout('admin/user-layout.phtml');
 }

 /**
  * @return Am_Query
  */
 protected function _getBaseQuery($joinConditions = "")
 {
     if ($joinConditions) {
         $joinConditions = "(" . $joinConditions . ") AND ";
     }
     $q = new Am_Query($this, 'r');
     $q->clearFields();
     $q->addField('DISTINCT r.resource_id', 'resource_id');
     $q->addField('resource_type', 'resource_type');
     $q->leftJoin("?_access_cache", "c", "\n                    {$joinConditions} \n                    (((c.fn = r.fn) AND (c.id = r.id)) OR (r.fn='product_category_id' AND r.id=-1)) \n                    AND (\n                            (c.status='active' AND r.start_days IS NULL AND r.stop_days IS NULL) \n                            OR\n                            (c.status='active' AND c.days BETWEEN IFNULL(r.start_days,0) AND IFNULL(r.stop_days, 90000)) \n                            OR\n                            (c.days >= IFNULL(r.start_days,0) AND r.stop_days = -1) \n                       )");
     // is available if free, or if user has equal subscription record in access_cache
     $q->addWhere("(r.fn='free' OR c.user_id IS NOT NULL)");
     return $q;
 }

 function historyRowsAction()
 {
     $q = new Am_Query($this->getDi()->emailSentTable);
     $q->leftJoin('?_admin', 'a', 't.admin_id=a.admin_id');
     $q->addField('a.login', 'admin_login');
     $q->setOrder('email_sent_id', 'DESC');
     // dirty hack
     $withWrap = (bool) $this->_request->get('_h_wrap');
     unset($_GET['_h_wrap']);
     $grid = new Am_Grid_Editable('_h', ___('E-Mails History'), $q, $this->_request, $this->view);
     $grid->setPermissionId(Am_Auth_Admin::PERM_EMAIL);
     if ($withWrap) {
         $grid->isAjax(false);
     }
     $grid->setCountPerPage(5);
     $grid->addField(new Am_Grid_Field_Date('tm_added', ___('Started')));
     $grid->addField('subject', ___('Subject'));
     $grid->addField('admin_login', ___('Sender'));
     $grid->addField('count_users', ___('Total'));
     $grid->addField('sent_users', ___('Sent'));
     $grid->addField('desc_users', ___('To'))->setAttrs(array('class' => 'break'));
     $grid->actionsClear();
     $grid->actionAdd(new Am_Grid_Action_Url('resend', ___('Resend'), REL_ROOT_URL . "/admin-email?resend_id=__ID__"))->setTarget('_top');
     $grid->actionAdd(new Am_Grid_Action_Url('continue', ___('Continue'), REL_ROOT_URL . "/admin-email/send?queue_id=__ID__"))->setTarget('_top')->setIsAvailableCallback(array($this, 'needContinueLink'));
     if ($this->getDi()->authAdmin->getUser()->isSuper()) {
         $grid->actionAdd(new Am_Grid_Action_Delete());
     }
     $grid->run($this->getResponse());
 }

 function viewAction()
 {
     Am_Aff_PayoutMethod::static_addFields();
     // display payouts list date | method | total | paid |
     $id = $this->getInt('payout_id');
     if (!$id) {
         throw new Am_Exception_InputError("Not payout_id passed");
     }
     $ds = new Am_Query($this->getDi()->affPayoutDetailTable);
     $ds->leftJoin('?_aff_payout', 'p', 'p.payout_id=t.payout_id');
     $ds->leftJoin('?_user', 'u', 't.aff_id=u.user_id');
     $ds->addField('u.*');
     $ds->addField('p.type', 'type');
     $ds->addWhere('t.payout_id=?d', $id);
     $grid = new Am_Grid_Editable('_d', ___("Payout %d Details", $id), $ds, $this->_request, $this->view);
     $grid->setEventId('gridAffPayoutDetail');
     $grid->setPermissionId(Bootstrap_Aff::ADMIN_PERM_ID);
     $grid->addCallback(Am_Grid_Editable::CB_RENDER_TABLE, array($this, 'addBackLink'));
     $userUrl = new Am_View_Helper_UserUrl();
     $grid->addField('email', ___('E-Mail'))->addDecorator(new Am_Grid_Field_Decorator_Link($userUrl->userUrl('{user_id}'), '_top'));
     $grid->addField('name_f', ___('First Name'));
     $grid->addField('name_l', ___('Last Name'));
     $grid->addField(new Am_Grid_Field_Enum('type', ___('Payout Method')))->setTranslations(Am_Aff_PayoutMethod::getAvailableOptions());
     $grid->addField('amount', ___('Amount'))->setGetFunction(array($this, 'getAmount'));
     //        $grid->addField('receipt_id', ___('Receipt Id'));
     $grid->addField(new Am_Grid_Field_Enum('is_paid', ___('Is Paid?')))->setTranslations(array(0 => ___('No'), 1 => ___('Yes')));
     $grid->addField(new Am_Grid_Field_Expandable('_details', ___('Payout Details')))->setGetFunction(array($this, 'getPayoutDetails'));
     $grid->actionsClear();
     //$grid->actionAdd(new Am_Grid_Action_LiveEdit('receipt_id'));
     $grid->actionAdd(new Am_Grid_Action_PayoutMarkPaid('mark_paid', ___('Mark Paid')));
     $grid->actionAdd(new Am_Grid_Action_PayoutMarkNotPaid('mark_notpaid', ___('Mark NOT Paid')));
     $grid->addCallback(Am_Grid_ReadOnly::CB_TR_ATTRIBS, array($this, 'detailCbGetTrAttribs'));
     $grid->runWithLayout();
 }