public function fetchRelatedFor(Doctrine_Record $record)
{
$id = $record->getIncremented();
if (empty($id) || !$this->definition['table']->getAttribute(Doctrine_Core::ATTR_LOAD_REFERENCES)) {
return Doctrine_Collection::create($this->getTable());
} else {
$q = new Doctrine_RawSql($this->getTable()->getConnection());
$assocTable = $this->getAssociationFactory()->getTableName();
$tableName = $record->getTable()->getTableName();
$identifierColumnNames = $record->getTable()->getIdentifierColumnNames();
$identifier = array_pop($identifierColumnNames);
$sub = 'SELECT ' . $this->getForeignRefColumnName() . ' FROM ' . $assocTable . ' WHERE ' . $this->getLocalRefColumnName() . ' = ?';
$condition[] = $tableName . '.' . $identifier . ' IN (' . $sub . ')';
$joinCondition[] = $tableName . '.' . $identifier . ' = ' . $assocTable . '.' . $this->getForeignRefColumnName();
if ($this->definition['equal']) {
$sub2 = 'SELECT ' . $this->getLocalRefColumnName() . ' FROM ' . $assocTable . ' WHERE ' . $this->getForeignRefColumnName() . ' = ?';
$condition[] = $tableName . '.' . $identifier . ' IN (' . $sub2 . ')';
$joinCondition[] = $tableName . '.' . $identifier . ' = ' . $assocTable . '.' . $this->getLocalRefColumnName();
}
$q->select('{' . $tableName . '.*}, {' . $assocTable . '.*}')->from($tableName . ' INNER JOIN ' . $assocTable . ' ON ' . implode(' OR ', $joinCondition))->where(implode(' OR ', $condition))->orderBy($tableName . '.' . $identifier . ' ASC');
if ($orderBy = $this->getOrderByStatement($tableName, true)) {
$q->addOrderBy($orderBy);
}
$q->addComponent($tableName, $this->getClass());
$path = $this->getClass() . '.' . $this->getAssociationFactory()->getComponentName();
if ($this->definition['refClassRelationAlias']) {
$path = $this->getClass() . '.' . $this->definition['refClassRelationAlias'];
}
$q->addComponent($assocTable, $path);
$params = $this->definition['equal'] ? array($id, $id) : array($id);
$res = $q->execute($params);
return $res;
}
}
public function fetchRelatedFor(Doctrine_Record $record)
{
$id = $record->getIncremented();
$q = new Doctrine_RawSql();
$assocTable = $this->getAssociationFactory()->getTableName();
$tableName = $record->getTable()->getTableName();
$identifier = $record->getTable()->getIdentifier();
$sub = 'SELECT ' . $this->getForeign() . ' FROM ' . $assocTable . ' WHERE ' . $this->getLocal() . ' = ?';
$sub2 = 'SELECT ' . $this->getLocal() . ' FROM ' . $assocTable . ' WHERE ' . $this->getForeign() . ' = ?';
$q->select('{' . $tableName . '.*}, {' . $assocTable . '.*}')->from($tableName . ' INNER JOIN ' . $assocTable . ' ON ' . $tableName . '.' . $identifier . ' = ' . $assocTable . '.' . $this->getLocal() . ' OR ' . $tableName . '.' . $identifier . ' = ' . $assocTable . '.' . $this->getForeign())->where($tableName . '.' . $identifier . ' IN (' . $sub . ') OR ' . $tableName . '.' . $identifier . ' IN (' . $sub2 . ')');
$q->addComponent($tableName, $record->getTable()->getComponentName());
$q->addComponent($assocTable, $record->getTable()->getComponentName() . '.' . $this->getAssociationFactory()->getComponentName());
return $q->execute(array($id, $id));
}
/**
* Obtiene la lista de miembros de celula dado el id del lider, la lista
* solo muestra los miembros que pueden habrir célula
* @param integer $id_lider el Identificacor del lider
* @return MiembroCelula Lista de miembros encontrados
*/
public function getMiembrosLideresPorLider($id_lider, $genero)
{
$q = new Doctrine_RawSql();
$q->select('d.*');
$q->from('miembro_celula mc inner join celula c on c.id = mc.celula_id inner join sf_guard_user d on mc.discipulo_id = d.id');
$q->where('c.discipulo_lider_id = ? OR d.tipo_discipulo = ?', array($id_lider, 4));
$q->andWhere('d.genero = ?', $genero);
$q->addComponent('d', 'Discipulo d');
// $q->orWhere('d.tipo_discipulo = ?', 4);
return $q->execute();
}
/**
* Get Doctrine Query which can be used fetch employee list with the given
* sorting and filtering options
*
* @param array $sortField
* @param $sortOrder
* @param $filters
* @return array
*/
public function getEmployeePerformanceReviewListQuery($sortField = null, $sortOrder = null, array $filters = null)
{
$searchByStatus = false;
/*
* Using RawSQL since it is difficult to use DQL to get an efficient query for the
* employee list search
*/
$query = new Doctrine_RawSql();
$query->select('{e.emp_number}, {e.employee_id}, {e.emp_firstname}, {e.emp_lastname}, ' . '{e.emp_middle_name}, {cs.title}, {j.jobtit_name}, {es.estat_name}, ' . '{s.emp_firstname}, {s.emp_lastname}, rt.erep_reporting_mode')->from("hs_hr_employee e LEFT JOIN hs_hr_compstructtree cs on cs.id = e.work_station " . " LEFT JOIN hs_hr_job_title j on j.jobtit_code = e.job_title_code " . " LEFT JOIN hs_hr_empstat es on e.emp_status = es.estat_code " . " LEFT JOIN hs_hr_emp_reportto rt on e.emp_number = rt.erep_sub_emp_number " . " LEFT JOIN hs_hr_employee s on s.emp_number = rt.erep_sup_emp_number ");
$query->addComponent('e', 'Employee e');
$query->addComponent('cs', 'e.subDivision cs');
$query->addComponent('j', 'e.jobTitle j');
$query->addComponent('es', 'e.employeeStatus es');
$query->addComponent('s', 'e.supervisors s');
/* search filters */
if (!empty($filters)) {
$filterCount = 0;
foreach ($filters as $searchField => $searchBy) {
if (!empty($searchField) && !empty($searchBy) && array_key_exists($searchField, self::$searchMapping)) {
$field = self::$searchMapping[$searchField];
$value = '%' . $searchBy . '%';
if ($searchField == 'subDivision') {
/*
* Not efficient if searching substations by more than one value, but
* we only have the facility to search by one value in the UI.
*/
$query->andwhere('e.work_station IN (SELECT n.id FROM hs_hr_compstructtree n ' . 'INNER JOIN hs_hr_compstructtree p WHERE n.lft >= p.lft ' . 'AND n.rgt <= p.rgt AND p.title LIKE ? )', $value);
} else {
if ($searchField == 'supervisorId') {
$query->andwhere('s.emp_number = ?', $searchBy);
} else {
if ($filterCount == 0) {
$query->where($field . ' LIKE ?', $value);
} else {
$query->andwhere($field . ' LIKE ?', $value);
}
}
}
$filterCount++;
if ($searchField == 'employeeStatus') {
$searchByStatus = true;
}
}
}
}
/* If not searching by employee status, hide terminated employees */
if (!$searchByStatus) {
$query->andwhere('( e.emp_status != ? OR e.emp_status IS NULL )', 'EST000');
}
/* sorting */
if (!empty($sortField) && !empty($sortOrder)) {
if (array_key_exists($sortField, self::$sortMapping)) {
$field = self::$sortMapping[$sortField];
if (is_array($field)) {
foreach ($field as $key => $name) {
$query->addOrderBy($name . ' ' . $sortOrder);
}
} else {
$query->orderBy($field . ' ' . $sortOrder);
}
}
}
/* Default sort by emp_number, makes resulting order predictable, useful for testing */
$query->addOrderBy('e.emp_number', 'asc');
/* Sort subordinates direct first, then indirect, then by supervisor name */
$query->addOrderBy('rt.erep_reporting_mode', 'asc');
if ($sortField != 'supervisor') {
$query->addOrderBy('s.emp_firstname', 'asc');
$query->addOrderBy('s.emp_lastname', 'asc');
}
return $query;
}
public function testSwitchingTheFieldOrder()
{
$query = new Doctrine_RawSql();
$query->parseQuery('SELECT {phonenumber.*}, {entity.name} FROM entity LEFT JOIN phonenumber ON phonenumber.entity_id = entity.id LIMIT 3');
$query->addComponent('entity', 'Entity');
$query->addComponent('phonenumber', 'Entity.Phonenumber');
$this->assertEqual($query->getSql(), 'SELECT entity.name AS entity__name, entity.id AS entity__id, phonenumber.id AS phonenumber__id, phonenumber.phonenumber AS phonenumber__phonenumber, phonenumber.entity_id AS phonenumber__entity_id FROM entity LEFT JOIN phonenumber ON phonenumber.entity_id = entity.id LIMIT 3');
$coll = $query->execute(array(), Doctrine::HYDRATE_ARRAY);
$this->assertEqual(count($coll), 3);
}
public static function getDistrictDisbursements($district, $vaccine, $from, $to, $offset, $items, $order_by, $order, $district_store = 0, $balance = 0) {
$owner = "D" . $district;
$balance_sql = "SET @stock_in = $balance";
Doctrine_Manager::getInstance() -> getCurrentConnection() -> standaloneQuery($balance_sql) -> execute();
Doctrine_Manager::getInstance() -> getCurrentConnection() -> standaloneQuery('SET @stock_out = 0;') -> execute();
Doctrine_Manager::getInstance() -> getCurrentConnection() -> standaloneQuery('SET @stock_balance = 0;') -> execute();
$query = new Doctrine_RawSql();
if ($district_store > 0) {
if ($order == "DESC") {
$query -> addComponent('d', 'Disbursements') -> select('{d.Date_Issued},{d.Issued_To_National},{d.Issued_To_Region},{d.Issued_To_District},{d.Quantity},{d.Vaccine_Id},{d.Batch_Number},{d.Voucher_Number},{d.Added_By},{d.Stock_At_Hand},{d.Total_Stock_In},{d.Total_Stock_Out},{d.Total_Stock_Balance}') -> from("(SELECT id,Date_Issued,Issued_To_National,Issued_To_Region,Issued_To_District,Quantity,Vaccine_Id,Batch_Number,Voucher_Number,Added_By,Stock_At_Hand, (case when Issued_By_District='$district' then (@stock_out := @stock_out + Quantity) else case when total_stock_balance > 0 then @stock_out :=0 end end) as total_stock_out, (case when Issued_To_District='$district' then (@stock_in := @stock_in + Quantity) else case when total_stock_balance > 0 then @stock_in :=total_stock_balance end end) as total_stock_in, (case when total_stock_balance > 0 then @stock_balance :=total_stock_balance else @stock_balance := @stock_in - @stock_out end) as total_stock_balance FROM Disbursements d where Vaccine_Id = '$vaccine' and Issued_To_District = '$district_store' and Date_Issued_Timestamp between '$from' and '$to' and Owner = '$owner' order by Unix_Timestamp(str_to_date(Date_Issued,'%m/%d/%Y')) asc,id asc) d") -> orderBy("Unix_Timestamp(str_to_date(Date_Issued,'%m/%d/%Y')) desc,id desc") -> offset($offset) -> limit($items);
} else {
$query -> addComponent('d', 'Disbursements') -> select('{d.Date_Issued},{d.Issued_To_National},{d.Issued_To_Region},{d.Issued_To_District},{d.Quantity},{d.Vaccine_Id},{d.Batch_Number},{d.Voucher_Number},{d.Added_By},{d.Stock_At_Hand},{d.Total_Stock_In},{d.Total_Stock_Out},{d.Total_Stock_Balance}') -> from("(SELECT id,Date_Issued,Issued_To_National,Issued_To_Region,Issued_To_District,Quantity,Vaccine_Id,Batch_Number,Voucher_Number,Added_By,Stock_At_Hand, (case when Issued_By_District='$district' then (@stock_out := @stock_out + Quantity) else case when total_stock_balance > 0 then @stock_out :=0 end end) as total_stock_out, (case when Issued_To_District='$district' then (@stock_in := @stock_in + Quantity) else case when total_stock_balance > 0 then @stock_in :=total_stock_balance end end) as total_stock_in, (case when total_stock_balance > 0 then @stock_balance :=total_stock_balance else @stock_balance := @stock_in - @stock_out end) as total_stock_balance FROM Disbursements d where Vaccine_Id = '$vaccine' and Issued_To_District = '$district_store' and Date_Issued_Timestamp between '$from' and '$to' and Owner = '$owner' order by Unix_Timestamp(str_to_date(Date_Issued,'%m/%d/%Y')) asc,id asc) d") -> orderBy("Unix_Timestamp(str_to_date(Date_Issued,'%m/%d/%Y')) asc,id asc") -> offset($offset) -> limit($items);
}
} else {
if ($order == "DESC") {
$query -> addComponent('d', 'Disbursements') -> select('{d.Date_Issued},{d.Issued_To_National},{d.Issued_To_Region},{d.Issued_To_District},{d.Quantity},{d.Vaccine_Id},{d.Batch_Number},{d.Voucher_Number},{d.Added_By},{d.Stock_At_Hand},{d.Total_Stock_In},{d.Total_Stock_Out},{d.Total_Stock_Balance}') -> from("(SELECT id,Date_Issued,Issued_To_National,Issued_To_Region,Issued_To_District,Quantity,Vaccine_Id,Batch_Number,Voucher_Number,Added_By,Stock_At_Hand, (case when Issued_By_District='$district' then (@stock_out := @stock_out + Quantity) else case when total_stock_balance > 0 then @stock_out :=0 end end) as total_stock_out, (case when Issued_To_District='$district' then (@stock_in := @stock_in + Quantity) else case when total_stock_balance > 0 then @stock_in :=total_stock_balance end end) as total_stock_in, (case when total_stock_balance > 0 then @stock_balance :=total_stock_balance else @stock_balance := @stock_in - @stock_out end) as total_stock_balance FROM Disbursements d where Vaccine_Id = '$vaccine' and Date_Issued_Timestamp between '$from' and '$to' and Owner = '$owner' order by Unix_Timestamp(str_to_date(Date_Issued,'%m/%d/%Y')) asc,id asc) d") -> orderBy("Unix_Timestamp(str_to_date(Date_Issued,'%m/%d/%Y')) desc,id desc") -> offset($offset) -> limit($items);
} else {
$query -> addComponent('d', 'Disbursements') -> select('{d.Date_Issued},{d.Issued_To_National},{d.Issued_To_Region},{d.Issued_To_District},{d.Quantity},{d.Vaccine_Id},{d.Batch_Number},{d.Voucher_Number},{d.Added_By},{d.Stock_At_Hand},{d.Total_Stock_In},{d.Total_Stock_Out},{d.Total_Stock_Balance}') -> from("(SELECT id,Date_Issued,Issued_To_National,Issued_To_Region,Issued_To_District,Quantity,Vaccine_Id,Batch_Number,Voucher_Number,Added_By,Stock_At_Hand, (case when Issued_By_District='$district' then (@stock_out := @stock_out + Quantity) else case when total_stock_balance > 0 then @stock_out :=0 end end) as total_stock_out, (case when Issued_To_District='$district' then (@stock_in := @stock_in + Quantity) else case when total_stock_balance > 0 then @stock_in :=total_stock_balance end end) as total_stock_in, (case when total_stock_balance > 0 then @stock_balance :=total_stock_balance else @stock_balance := @stock_in - @stock_out end) as total_stock_balance FROM Disbursements d where Vaccine_Id = '$vaccine' and Date_Issued_Timestamp between '$from' and '$to' and Owner = '$owner' order by Unix_Timestamp(str_to_date(Date_Issued,'%m/%d/%Y')) asc,id asc) d") -> orderBy("Unix_Timestamp(str_to_date(Date_Issued,'%m/%d/%Y')) asc,id asc") -> offset($offset) -> limit($items);
}
}
$disbursements = $query -> execute();
return $disbursements;
}
/**
* Generate a messages collection containing all messages exchanged between the message sender and the recipient
* @return
*/
function getMessageHistory()
{
// Use doctrine raw sql
$q = new Doctrine_RawSql();
$q->select('{m.*}, {mr.*}');
$q->from('message m INNER JOIN messagerecipient mr ON (m.id = mr.messageid AND ISNULL(m.commentid))');
$q->where("(m.senderid = '" . $this->getSenderID() . "' AND mr.recipientid = '" . $this->getRecipient()->getID() . "') OR \n\t\t\t\t\t(m.senderid = '" . $this->getRecipient()->getID() . "' AND mr.recipientid = '" . $this->getSenderID() . "') ORDER BY m.datecreated ASC ");
$q->addComponent('m', 'Message m');
$q->addComponent('mr', 'm.recipients mr');
$result = $q->execute();
// debugMessage($result->toArray());
return $result;
}
/**
public function fetchRelatedFor(Doctrine_Record $record)
{
$id = $record->getIncremented();
if (empty($id) || ! $this->definition['table']->getAttribute(Doctrine::ATTR_LOAD_REFERENCES)) {
return new Doctrine_Collection($this->getTable());
} else {
$q = new Doctrine_Query();
$c = $this->getTable()->getComponentName();
$a = substr($c, 0, 1);
$c2 = $this->getAssociationTable()->getComponentName();
$a2 = substr($c2, 0, 1);
$q->from($c)
->innerJoin($c . '.' . $c2)
$sub = 'SELECT ' . $this->getForeign()
. ' FROM ' . $c2
. ' WHERE ' . $this->getLocal()
. ' = ?';
}
}
*/
public function fetchRelatedFor(Doctrine_Record $record)
{
$id = $record->getIncremented();
if (empty($id) || !$this->definition['table']->getAttribute(Doctrine::ATTR_LOAD_REFERENCES)) {
return new Doctrine_Collection($this->getTable());
} else {
$q = new Doctrine_RawSql();
$assocTable = $this->getAssociationFactory()->getTableName();
$tableName = $record->getTable()->getTableName();
$identifier = $record->getTable()->getIdentifier();
$sub = 'SELECT ' . $this->getForeign() . ' FROM ' . $assocTable . ' WHERE ' . $this->getLocal() . ' = ?';
$condition[] = $tableName . '.' . $identifier . ' IN (' . $sub . ')';
$joinCondition[] = $tableName . '.' . $identifier . ' = ' . $assocTable . '.' . $this->getForeign();
if ($this->definition['equal']) {
$sub2 = 'SELECT ' . $this->getLocal() . ' FROM ' . $assocTable . ' WHERE ' . $this->getForeign() . ' = ?';
$condition[] = $tableName . '.' . $identifier . ' IN (' . $sub2 . ')';
$joinCondition[] = $tableName . '.' . $identifier . ' = ' . $assocTable . '.' . $this->getLocal();
}
$q->select('{' . $tableName . '.*}, {' . $assocTable . '.*}')->from($tableName . ' INNER JOIN ' . $assocTable . ' ON ' . implode(' OR ', $joinCondition))->where(implode(' OR ', $condition));
$q->addComponent($tableName, $record->getTable()->getComponentName());
$q->addComponent($assocTable, $record->getTable()->getComponentName() . '.' . $this->getAssociationFactory()->getComponentName());
$params = $this->definition['equal'] ? array($id, $id) : array($id);
return $q->execute($params);
}
}
