public function getCurrentAvgEurRate($targetcurrency, Gpf_DateTime_Range $range)
{
$sql = new Gpf_SqlBuilder_SelectBuilder();
$sql->select->add('avg(' . Gpf_Db_Table_CurrencyRates::RATE . ')', Gpf_Db_Table_CurrencyRates::RATE);
$sql->from->add(Gpf_Db_Table_CurrencyRates::getName());
$sql->where->add(Gpf_Db_Table_CurrencyRates::SOURCE_CURRENCY, '=', 'EUR');
$sql->where->add(Gpf_Db_Table_CurrencyRates::TARGET_CURRENCY, '=', $targetcurrency);
$sql->where->add(Gpf_Db_Table_CurrencyRates::TYPE, '=', Gpf_Db_CurrencyRate::TYPE_DAILY);
$dateCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
$coumpoundCondWithin = new Gpf_SqlBuilder_CompoundWhereCondition();
$coumpoundCondWithin->add(Gpf_Db_Table_CurrencyRates::VALID_FROM, '>=', $range->getFrom()->toDateTime());
$coumpoundCondWithin->add(Gpf_Db_Table_CurrencyRates::VALID_TO, '<=', $range->getTo()->toDateTime());
$dateCondition->addCondition($coumpoundCondWithin, 'OR');
$coumpoundCondBefore = new Gpf_SqlBuilder_CompoundWhereCondition();
$coumpoundCondBefore->add(Gpf_Db_Table_CurrencyRates::VALID_FROM, '<=', $range->getFrom()->toDateTime());
$coumpoundCondBefore->add(Gpf_Db_Table_CurrencyRates::VALID_TO, '>=', $range->getFrom()->toDateTime());
$dateCondition->addCondition($coumpoundCondBefore, 'OR');
$coumpoundCondAfter = new Gpf_SqlBuilder_CompoundWhereCondition();
$coumpoundCondAfter->add(Gpf_Db_Table_CurrencyRates::VALID_FROM, '<=', $range->getTo()->toDateTime());
$coumpoundCondAfter->add(Gpf_Db_Table_CurrencyRates::VALID_TO, '>=', $range->getTo()->toDateTime());
$dateCondition->addCondition($coumpoundCondAfter, 'OR');
$sql->where->addCondition($dateCondition);
Gpf_Log::debug($sql->toString());
Gpf_Log::debug('Avg rate: ' . $sql->getOneRow()->get(Gpf_Db_Table_CurrencyRates::RATE));
return $sql->getOneRow()->get(Gpf_Db_Table_CurrencyRates::RATE);
}
protected function buildFrom() {
$affiliateWhere = new Gpf_SqlBuilder_CompoundWhereCondition();
$affiliateWhere->add(Pap_Db_Table_AffiliateTrackingCodes::AFFILIATEID, '=', Gpf_Session::getAuthUser()->getPapUserId(), 'OR');
$affiliateWhere->add(Pap_Db_Table_AffiliateTrackingCodes::AFFILIATEID, 'IS', 'NULL', 'OR', false);
$this->_selectBuilder->from->add(Pap_Db_Table_CommissionTypes::getName(), 'ct');
$onCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
$onCondition->add('ct.'.Pap_Db_Table_CommissionTypes::ID, '=', 'c.'.Pap_Db_Table_AffiliateTrackingCodes::COMMTYPEID, 'AND', false);
$onCondition->addCondition($affiliateWhere);
$this->_selectBuilder->from->addLeftJoin(Pap_Db_Table_AffiliateTrackingCodes::getName(), 'c',
$onCondition->toString());
}
protected function addSearch(Gpf_SqlBuilder_Filter $filter) {
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$this->initSearchAffiliateCondition($filter);
if (!is_null($this->_affiliateCondition)) {
$condition->addCondition($this->_affiliateCondition, 'OR');
}
$condition->add('t.'.Pap_Db_Table_Transactions::TRANSACTION_ID, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('t.'.Pap_Db_Table_Transactions::ORDER_ID, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('t.'.Pap_Db_Table_Transactions::PRODUCT_ID, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('t.'.Pap_Db_Table_Transactions::TOTAL_COST, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('t.'.Pap_Db_Table_Transactions::IP, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('t.'.Pap_Db_Table_Transactions::REFERER_URL, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('t.'.Pap_Db_Table_Transactions::SYSTEMNOTE, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('t.'.Pap_Db_Table_Transactions::MERCHANTNOTE, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('t.'.Pap_Db_Table_Transactions::DATA1, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('t.'.Pap_Db_Table_Transactions::DATA2, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('t.'.Pap_Db_Table_Transactions::DATA3, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('t.'.Pap_Db_Table_Transactions::DATA4, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$condition->add('t.'.Pap_Db_Table_Transactions::DATA5, 'LIKE', '%'.$filter->getValue().'%', 'OR');
$this->_selectBuilder->where->addCondition($condition);
}
/**
*
* @param $campaignId
* @param $commissionType
* @param $affiliateId
* @return Gpf_Data_RecordSet
*/
public function getAllUserCommissionTypes($campaignId = null, $commissionType = null, $affiliateId = null) {
$selectBuilder = $this->getAllCommissionTypesSelect($campaignId, $commissionType);
$selectBuilder->select->add(Pap_Db_Table_Campaigns::NAME, 'campaignname', 'c');
$selectBuilder->from->addInnerJoin(Pap_Db_Table_Campaigns::getName(), 'c', 'ct.'.self::CAMPAIGNID.'=c.'.Pap_Db_Table_Campaigns::ID);
if (Gpf_Session::getAuthUser()->getAccountId() != Gpf_Db_Account::DEFAULT_ACCOUNT_ID) {
$selectBuilder->where->add(Pap_Db_Table_Campaigns::ACCOUNTID, '=', Gpf_Session::getAuthUser()->getAccountId());
}
if ($affiliateId !== null && $affiliateId !== '') {
$selectBuilder->from->addLeftJoin(Pap_Db_Table_CommissionGroups::getName(), 'cg',
'ct.'.self::CAMPAIGNID.'=cg.'.Pap_Db_Table_CommissionGroups::CAMPAIGN_ID);
$selectBuilder->from->addLeftJoin(Pap_Db_Table_UserInCommissionGroup::getName(), 'uicg',
'cg.'.Pap_Db_Table_CommissionGroups::ID.'=uicg.'.Pap_Db_Table_UserInCommissionGroup::COMMISSION_GROUP_ID);
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$subCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
$subCondition->add('uicg.'.Pap_Db_Table_UserInCommissionGroup::USER_ID, '=', $affiliateId);
$subCondition->add('uicg.'.Pap_Db_Table_UserInCommissionGroup::STATUS, '=', 'A');
$condition->addCondition($subCondition, 'OR');
$condition->add('c.'.Pap_Db_Table_Campaigns::TYPE, '=', 'P', 'OR');
$selectBuilder->where->addCondition($condition);
$selectBuilder->groupBy->add('ct.'.self::ID);
}
return $selectBuilder->getAllRows();
}
/**
* @param string $formid
* @param string/array $status
* @return Gpf_Data_RecordSet
*/
public function getFieldsNoRpc($formid, $status = null, $mainFields = null)
{
$select = new Gpf_SqlBuilder_SelectBuilder();
$select->select->add("f." . self::ID, "id");
$select->select->add("f." . self::CODE, "code");
$select->select->add("f." . self::NAME, "name");
$select->select->add("f." . self::TYPE, "type");
$select->select->add("f." . self::STATUS, "status");
$select->select->add("f." . self::AVAILABLEVALUES, "availablevalues");
$select->from->add($this->getName(), "f");
$select->where->add(Gpf_Db_Table_Accounts::ID, '=', Gpf_Application::getInstance()->getAccountId());
$select->where->add(self::FORMID, '=', $formid);
if ($status != null) {
if (is_array($status)) {
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
foreach ($status as $statusCode) {
$condition->add(self::STATUS, '=', $statusCode, 'OR');
}
$select->where->addCondition($condition);
} else {
$select->where->add(self::STATUS, '=', $status);
}
}
if ($mainFields != null && $mainFields == Gpf::YES) {
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$condition->add(Gpf_Db_Table_Accounts::ID, '=', Gpf_Application::getInstance()->getAccountId());
$condition->add(self::FORMID, '=', $formid);
$conditionInner = new Gpf_SqlBuilder_CompoundWhereCondition();
$conditionInner->add(self::CODE, '=', 'parentuserid', 'OR');
$conditionInner->add(self::CODE, '=', 'refid', 'OR');
$conditionInner->add(self::CODE, '=', 'notificationemail', 'OR');
$condition->addCondition($conditionInner);
$select->where->addCondition($condition, 'OR');
}
//$select->orderBy->add("section");
$select->orderBy->add(self::ORDER);
$result = $select->getAllRows();
$result->addColumn("help", "");
return $result;
}
/**
* @param Gpf_SqlBuilder_SelectBuilder $select
*/
private function insertRuleToWhereCondition(Gpf_SqlBuilder_SelectBuilder $select) {
$value1 = Gpf_Settings::get(Pap_Features_CompressedCommissionPlacementModel_Definition::RULE_EQUATION_VALUE1);
$value2 = Gpf_Settings::get(Pap_Features_CompressedCommissionPlacementModel_Definition::RULE_EQUATION_VALUE2);
$what = $this->getRuleNameByCondition(Gpf_Settings::get(Pap_Features_CompressedCommissionPlacementModel_Definition::RULE_WHAT));
switch (Gpf_Settings::get(Pap_Features_CompressedCommissionPlacementModel_Definition::RULE_EQUATION)) {
case 'L':
$select->where->addCondition($this->createWhereCondition($what, ($this->getReachedConditionFilterValue()==Gpf::YES)?'<':'>=', $value1));
break;
case 'H':
$select->where->addCondition($this->createWhereCondition($what, ($this->getReachedConditionFilterValue()==Gpf::YES)?'>':'<=', $value1));
break;
case 'B':
if ($this->getReachedConditionFilterValue()==Gpf::YES) {
$compoundCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
$compoundCondition->add($what, 'BETWEEN', $value1 . ' AND ' . $value2, 'AND', false);
if ($value1 <= 0 && $value2 >= 0) {
$compoundCondition->add($what, '=', null, 'OR');
}
$select->where->addCondition($compoundCondition);
} else {
$condition = new Gpf_SqlBuilder_CompoundWhereCondition();
$condition->addCondition($this->createWhereCondition($what, '<', $value1));
$condition->addCondition($this->createWhereCondition($what, '>', $value2), 'OR');
$select->where->addCondition($condition);
}
break;
case 'E':
$select->where->addCondition($this->createWhereCondition($what, ($this->getReachedConditionFilterValue()==Gpf::YES)?'=':'!=', $value1));
break;
}
}
/**
*
* @return Gpf_Db_Task
*/
protected function getPendingTask($lastRunTaskId, $inclusion_type, $inclusion_tasks)
{
$sql = new Gpf_SqlBuilder_SelectBuilder();
$sql->select->addAll(Gpf_Db_Table_Tasks::getInstance());
$sql->from->add(Gpf_Db_Table_Tasks::getName());
$sql->where->add(Gpf_Db_Table_Tasks::DATEFINISHED, '=', null);
$sql->where->add(Gpf_Db_Table_Tasks::TYPE, '=', Gpf_Db_Task::TYPE_CRON);
if ($inclusion_type == self::INCLUDE_TASKS) {
$sql->where->add(Gpf_Db_Table_Tasks::CLASSNAME, 'IN', $inclusion_tasks);
} else {
if ($inclusion_type == self::EXCLUDE_TASKS) {
$sql->where->add(Gpf_Db_Table_Tasks::CLASSNAME, 'NOT IN', $inclusion_tasks);
}
}
$andCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
$andCondition->add(Gpf_Db_Table_Tasks::IS_EXECUTING, '<>', Gpf::YES, 'OR');
$orCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
$orCondition->add(Gpf_Db_Table_Tasks::IS_EXECUTING, '=', Gpf::YES);
$orCondition->add(Gpf_Db_Table_Tasks::DATECHANGED, '<', Gpf_Common_DateUtils::getDateTime(time() - self::MAX_TASK_RUN_TIME_WITHOUT_UPDATE));
$andCondition->addCondition($orCondition, 'OR');
$sql->where->addCondition($andCondition);
$sleepCondition = new Gpf_SqlBuilder_CompoundWhereCondition();
$sleepCondition->add(Gpf_Db_Table_Tasks::SLEEP_UNTIL, '=', null, 'OR');
$sleepCondition->add(Gpf_Db_Table_Tasks::SLEEP_UNTIL, '<=', Gpf_Common_DateUtils::now(), 'OR');
$sql->where->addCondition($sleepCondition);
$sql->orderBy->add(Gpf_Db_Table_Tasks::WORKING_AREA_TO . '-' . Gpf_Db_Table_Tasks::WORKING_AREA_FROM, false);
$sql->orderBy->add(Gpf_Db_Table_Tasks::DATECHANGED);
$recordset = $sql->getAllRows();
if ($recordset->getSize() == 0) {
return false;
}
if ($lastRunTaskId == null) {
return $this->getTask($recordset->get(0));
}
$recordId = $this->findLastRunTaskRecordsetPosition($recordset, $lastRunTaskId);
if ($recordId === null) {
return $this->getTask($recordset->get(0));
}
if ($recordId == $recordset->getSize() - 1) {
return false;
}
if ($recordset->get($recordId + 1) == null) {
return false;
}
return $this->getTask($recordset->get($recordId + 1));
}
function createUpdateIncrementBuild(Pap_Common_Banner $childBanner, $column){
$updateBuild = new Gpf_SqlBuilder_UpdateBuilder();
$updateBuild->from->add(Pap_Db_Table_BannersInRotators::getName());
$updateBuild->set->add($column, "$column+1", false);
$updateBuild->where->add(Pap_Db_Table_BannersInRotators::PARENT_BANNER_ID,'=',$this->getId());
$updateBuild->where->add(Pap_Db_Table_BannersInRotators::ROTATED_BANNER_ID,'=',$childBanner->getId());
$c1 = new Gpf_SqlBuilder_CompoundWhereCondition();
$c1->add(Pap_Db_Table_BannersInRotators::VALID_FROM,'=',null);
$c1->add(Pap_Db_Table_BannersInRotators::VALID_UNTIL,'=',null);
$c2 = new Gpf_SqlBuilder_CompoundWhereCondition();
$c2->add(Pap_Db_Table_BannersInRotators::VALID_FROM,'<',date("Y-m-d H:i:s"));
$c2->add(Pap_Db_Table_BannersInRotators::VALID_UNTIL,'>',date("Y-m-d H:i:s"));
$c3 = new Gpf_SqlBuilder_CompoundWhereCondition();
$c3->add(Pap_Db_Table_BannersInRotators::VALID_FROM,'=',null);
$c3->add(Pap_Db_Table_BannersInRotators::VALID_UNTIL,'>',date("Y-m-d H:i:s"));
$c4 = new Gpf_SqlBuilder_CompoundWhereCondition();
$c4->add(Pap_Db_Table_BannersInRotators::VALID_FROM,'<',date("Y-m-d H:i:s"));
$c4->add(Pap_Db_Table_BannersInRotators::VALID_UNTIL,'=',null);
$c = new Gpf_SqlBuilder_CompoundWhereCondition();
$c->addCondition($c1,'OR');
$c->addCondition($c2,'OR');
$c->addCondition($c3,'OR');
$c->addCondition($c4,'OR');
$updateBuild->where->addCondition($c,'AND');
return $updateBuild;
}
