public static function getPilotAvailablePager($max, $airport = null, $page = 1, $sort = 0, $from_date = null, $to_date = null, $weekdays = array(1, 1, 1, 1, 1, 1, 1), $wing_id = null, $ident = null, $city = null, $state = null, $zip = null, $origin = null, $dest = null, $pilot = null, $co_pilot = null, $ifr = null, $mission_types = array(), $filled = null, $open = null, $max_passenger = null, $max_weight = null, $max_distance = null, $min_efficiency = null, $is_camp = false, $ignore_availability = null)
{
if (!empty($from_date)) {
$from_date = date('Y-m-d', strtotime($from_date));
}
if (!empty($to_date)) {
$to_date = date('Y-m-d', strtotime($to_date));
}
for ($i = 0; $i < 7; $i++) {
$weekdays[$i] = $weekdays[$i] ? 1 : 0;
}
$origin = $origin ? 1 : 0;
$dest = $dest ? 1 : 0;
$pilot = $pilot ? 1 : 0;
$co_pilot = $co_pilot ? 1 : 0;
$ifr = $ifr ? 1 : 0;
$filled = $filled ? 1 : 0;
$open = $open ? 1 : 0;
$max_passenger = (int) $max_passenger;
$max_weight = (int) $max_weight;
$max_distance = (int) $max_distance;
if (!$min_efficiency) {
$min_efficiency = 85;
} else {
if (intval($min_efficiency) <= 0) {
$min_efficiency = 0;
}
if ($min_efficiency > 100) {
$min_efficiency = 0;
}
}
$c = new Criteria();
// default
$c->addJoin(self::ID, MissionLegPeer::MISSION_ID, Criteria::INNER_JOIN);
$c->addJoin(MissionLegPeer::ID, PilotRequestPeer::LEG_ID, Criteria::LEFT_JOIN);
$c->addJoin(MissionPeer::ITINERARY_ID, ItineraryPeer::ID, Criteria::INNER_JOIN);
$c->add(MissionLegPeer::MISSION_REPORT_ID, null, Criteria::ISNULL);
// $c1 = $c->getNewCriterion(PilotRequestPeer::PILOT_TYPE, 'Command Pilot', Criteria::NOT_LIKE);
// $c2 = $c->getNewCriterion(PilotRequestPeer::ACCEPTED, 1, Criteria::NOT_EQUAL);
// $c3 = $c->getNewCriterion(PilotRequestPeer::PROCESSED, 1, Criteria::NOT_EQUAL);
// $c4 = $c->getNewCriterion(PilotRequestPeer::ID, null, Criteria::ISNULL);
// $c->add($c1->addAnd($c2)->addAnd($c3)->addOr($c4));
$c->addAlias('from_air', AirportPeer::TABLE_NAME);
$c->addAlias('to_air', AirportPeer::TABLE_NAME);
//$c->addJoin(self::ID, MissionLegPeer::MISSION_ID, Criteria::LEFT_JOIN);
$c->addJoin(MissionLegPeer::FROM_AIRPORT_ID, AirportPeer::alias('from_air', AirportPeer::ID), Criteria::INNER_JOIN);
$c->addJoin(MissionLegPeer::TO_AIRPORT_ID, AirportPeer::alias('to_air', AirportPeer::ID), Criteria::INNER_JOIN);
// weekdays
/*
$c0 = null;
foreach ($weekdays as $day => $v) {
if ($v != 1) continue;
if ($c0) {
$c0->addOr($c->getNewCriterion(self::MISSION_DATE, 'WEEKDAY('.self::MISSION_DATE.')='.$day, Criteria::CUSTOM));
}else{
$c0 = $c->getNewCriterion(self::MISSION_DATE, 'WEEKDAY('.self::MISSION_DATE.')='.$day, Criteria::CUSTOM);
}
}
if ($c0) $c->add($c0);
*/
$arr = '(';
foreach ($weekdays as $day => $v) {
if ($v != 1) {
continue;
}
$arr .= $day . ', ';
}
$arr .= '-1)';
$c->add(self::ID, 'WEEKDAY(' . self::MISSION_DATE . ') in ' . $arr, Criteria::CUSTOM);
if ($max_distance) {
//$c->addAnd(MissionPeer::ID, 'Round((ACos(
$c->addAnd(MissionPeer::ID, 'Round((ACos(
Sin(Radians(from_air.latitude))
*
Sin(Radians(to_air.latitude))
+
Cos(Radians(from_air.latitude))
*
Cos(Radians(to_air.latitude))
*
Cos(Radians(from_air.longitude)-Radians(to_air.longitude)))
*' . '180/pi())*60) <= ' . $max_distance, Criteria::CUSTOM);
}
if ($min_efficiency && $airport) {
$c->addAnd(MissionPeer::ID, "\n CEILING(ROUND(ACOS(SIN(RADIANS(from_air.latitude))\n *SIN(RADIANS(to_air.latitude))+COS(RADIANS(from_air.latitude))\n *COS(RADIANS(to_air.latitude))*COS(RADIANS(from_air.longitude)-RADIANS(to_air.longitude)))\n *((180*60)/PI()))/(ROUND(ACOS(SIN(RADIANS(from_air.latitude))\n *SIN(RADIANS(to_air.latitude))+COS(RADIANS(from_air.latitude))\n *COS(RADIANS(to_air.latitude))\n *\n COS(RADIANS(from_air.longitude)-RADIANS(to_air.longitude))\n ) * ((180*60)/PI())\n )\n +\n ROUND(\n ACOS(\n SIN(RADIANS({$airport->getLatitude()}))\n *\n SIN(RADIANS(to_air.latitude))\n +\n COS(RADIANS({$airport->getLatitude()}))\n *\n COS(RADIANS(to_air.latitude))\n *\n COS(RADIANS({$airport->getLongitude()})-RADIANS(to_air.longitude))\n ) * ((180*60)/PI())\n )\n +\n ROUND(\n ACOS(\n SIN(RADIANS({$airport->getLatitude()}))\n *\n SIN(RADIANS(from_air.latitude))\n +\n COS(RADIANS({$airport->getLatitude()}))\n *\n COS(RADIANS(from_air.latitude))\n *\n COS(RADIANS({$airport->getLongitude()})-RADIANS(from_air.longitude))\n ) * ((180*60)/PI()))) * 200) >= " . $min_efficiency, Criteria::CUSTOM);
}
if ($max_passenger) {
//xuuchin
// $c->addAlias('cp', '(SELECT camp_id, COUNT(passenger_id) as pass_count FROM camp_passenger GROUP BY camp_id)');
// $c->addJoin(MissionPeer::CAMP_ID, CampPassengerPeer::alias('cp', CampPassengerPeer::CAMP_ID), Criteria::LEFT_JOIN);
// $c1 = $c->getNewCriterion(MissionPeer::CAMP_ID, null, Criteria::ISNULL);
// $c2 = $c->getNewCriterion(CampPassengerPeer::CAMP_ID, 'cp.pass_count<='.$max_passenger, Criteria::CUSTOM); // tricking the count
// $c3 = $c->getNewCriterion(MissionPeer::CAMP_ID, null, Criteria::ISNOTNULL);
// $c->add($c1->addOr($c2->addAnd($c3)));
//shine
$c->addAlias('pass1', PassengerPeer::TABLE_NAME);
$c->addAlias('pass2', PassengerPeer::TABLE_NAME);
$c->addJoin(MissionPeer::PASSENGER_ID, PassengerPeer::alias('pass1', PassengerPeer::ID), Criteria::INNER_JOIN);
$c->addJoin(ItineraryPeer::PASSENGER_ID, PassengerPeer::alias('pass2', PassengerPeer::ID), Criteria::INNER_JOIN);
$c1 = $c->getNewCriterion(PassengerPeer::alias('pass1', PassengerPeer::ID), '((SELECT COUNT(COALESCE(c.id, 0)) FROM companion c WHERE c.passenger_id = pass1.id) + 1) <= ' . $max_passenger, Criteria::CUSTOM);
$c2 = $c->getNewCriterion(PassengerPeer::alias('pass2', PassengerPeer::ID), '((SELECT COUNT(COALESCE(c.id, 0)) FROM companion c WHERE c.passenger_id = pass2.id)+ 1) <= ' . $max_passenger, Criteria::CUSTOM);
$c->add($c1->addAnd($c2));
}
if ($max_weight) {
$c->addAlias('pass1', PassengerPeer::TABLE_NAME);
$c->addAlias('pass2', PassengerPeer::TABLE_NAME);
$c->addJoin(MissionPeer::PASSENGER_ID, PassengerPeer::alias('pass1', PassengerPeer::ID), Criteria::INNER_JOIN);
$c->addJoin(ItineraryPeer::PASSENGER_ID, PassengerPeer::alias('pass2', PassengerPeer::ID), Criteria::INNER_JOIN);
//MissionPeer::addSelectColumns($c);
//$c->addAsColumn('wsum1', '((SELECT SUM(c.weight) FROM companion c WHERE c.passenger_id = pass1.id) + pass1.weight)');
//$c->addAsColumn('wsum2', '((SELECT SUM(c.weight) FROM companion c WHERE c.passenger_id = pass2.id)+ pass2.weight)');
//$c1 = $c->getNewCriterion(PassengerPeer::alias('pass1', PassengerPeer::WEIGHT), $max_weight, Criteria::LESS_EQUAL);
//$c2 = $c->getNewCriterion(PassengerPeer::alias('pass2', PassengerPeer::WEIGHT), $max_weight, Criteria::LESS_EQUAL);
$c1 = $c->getNewCriterion(PassengerPeer::alias('pass1', PassengerPeer::WEIGHT), '((SELECT SUM(COALESCE(c.weight, 0)) FROM companion c WHERE c.passenger_id = pass1.id) + COALESCE(pass1.weight,0)) <= ' . $max_weight, Criteria::CUSTOM);
$c2 = $c->getNewCriterion(PassengerPeer::alias('pass2', PassengerPeer::WEIGHT), '((SELECT SUM(COALESCE(c.weight, 0)) FROM companion c WHERE c.passenger_id = pass2.id)+ COALESCE(pass2.weight,0)) <= ' . $max_weight, Criteria::CUSTOM);
$c->add($c1->addAnd($c2));
}
if ($filled && $open) {
$c->add(MissionLegPeer::PILOT_ID, null, Criteria::ISNOTNULL);
$c->addOr(MissionLegPeer::PILOT_ID, null, Criteria::ISNULL);
} else {
if ($filled) {
$c->add(MissionLegPeer::PILOT_ID, null, Criteria::ISNOTNULL);
}
if ($open) {
$c->add(MissionLegPeer::PILOT_ID, null, Criteria::ISNULL);
}
}
// mission types
if ($mission_types) {
$c->add(MissionPeer::MISSION_TYPE_ID, $mission_types, Criteria::IN);
}
// needing pilots
if ($pilot) {
$c->add(MissionLegPeer::PILOT_ID, null, Criteria::ISNULL);
}
// needing mission assistants
if ($co_pilot) {
$c->add(PilotRequestPeer::MISSION_ASSISTANT_WANTED, 1);
}
// needing mission assistants
if ($ifr) {
$c->add(PilotRequestPeer::IFR_BACKUP_WANTED, 1);
}
// date range
if (!empty($from_date)) {
if (empty($to_date)) {
$c->add(self::MISSION_DATE, $from_date, Criteria::GREATER_THAN);
} else {
$c1 = $c->getNewCriterion(self::MISSION_DATE, $from_date, Criteria::GREATER_THAN);
$c2 = $c->getNewCriterion(self::MISSION_DATE, $to_date, Criteria::LESS_EQUAL);
$c->add($c1->addAnd($c2));
}
} elseif (!empty($to_date)) {
$c->add(self::MISSION_DATE, $to_date, Criteria::LESS_EQUAL);
} else {
//$c->add(MissionPeer::MISSION_DATE, 'NOW()', Criteria::LESS_EQUAL);
$c->add(self::MISSION_DATE, date("Y/m/d", strtotime("now")), Criteria::GREATER_THAN);
$c->addAnd(self::MISSION_DATE, date("Y/m/d", strtotime("+1 year")), Criteria::LESS_EQUAL);
}
// wing id
if ($wing_id) {
if ($origin && $dest) {
$c1 = $c->getNewCriterion(AirportPeer::alias('from_air', AirportPeer::WING_ID), $wing_id);
$c2 = $c->getNewCriterion(AirportPeer::alias('to_air', AirportPeer::WING_ID), $wing_id);
$c->add($c1->addOr($c2));
} else {
if ($origin) {
$c->add(AirportPeer::alias('from_air', AirportPeer::WING_ID), $wing_id);
}
if ($dest) {
$c->add(AirportPeer::alias('to_air', AirportPeer::WING_ID), $wing_id);
}
}
}
// ident
if ($ident) {
if ($origin && $dest) {
$c1 = $c->getNewCriterion(AirportPeer::alias('from_air', AirportPeer::IDENT), $ident, Criteria::LIKE);
$c2 = $c->getNewCriterion(AirportPeer::alias('to_air', AirportPeer::IDENT), $ident, Criteria::LIKE);
$c->add($c1->addOr($c2));
} else {
if ($origin) {
$c->add(AirportPeer::alias('from_air', AirportPeer::IDENT), $ident, Criteria::LIKE);
}
if ($dest) {
$c->add(AirportPeer::alias('to_air', AirportPeer::IDENT), $ident, Criteria::LIKE);
}
}
}
// city
if ($city) {
if ($origin && $dest) {
$c1 = $c->getNewCriterion(AirportPeer::alias('from_air', AirportPeer::CITY), $city, Criteria::LIKE);
$c2 = $c->getNewCriterion(AirportPeer::alias('to_air', AirportPeer::CITY), $city, Criteria::LIKE);
$c->add($c1->addOr($c2));
} else {
if ($origin) {
$c->add(AirportPeer::alias('from_air', AirportPeer::CITY), $city);
}
if ($dest) {
$c->add(AirportPeer::alias('to_air', AirportPeer::CITY), $city);
}
}
}
// state
if ($state) {
if ($origin && $dest) {
$c1 = $c->getNewCriterion(AirportPeer::alias('from_air', AirportPeer::STATE), $state, Criteria::LIKE);
$c2 = $c->getNewCriterion(AirportPeer::alias('to_air', AirportPeer::STATE), $state, Criteria::LIKE);
$c->add($c1->addOr($c2));
} else {
if ($origin) {
$c->add(AirportPeer::alias('from_air', AirportPeer::STATE), $state);
}
if ($dest) {
$c->add(AirportPeer::alias('to_air', AirportPeer::STATE), $state);
}
}
}
// zipcode
if ($zip) {
if ($origin && $dest) {
$c1 = $c->getNewCriterion(AirportPeer::alias('from_air', AirportPeer::ZIPCODE), $zip, Criteria::LIKE);
$c2 = $c->getNewCriterion(AirportPeer::alias('to_air', AirportPeer::ZIPCODE), $zip, Criteria::LIKE);
$c->add($c1->addOr($c2));
} else {
if ($origin) {
$c->add(AirportPeer::alias('from_air', AirportPeer::ZIPCODE), $zip);
}
if ($dest) {
$c->add(AirportPeer::alias('to_air', AirportPeer::ZIPCODE), $zip);
}
}
}
//if available camp mission is selected
if ($is_camp) {
$c->add(self::CAMP_ID, null, Criteria::ISNOTNULL);
}
if ($sort == 0) {
$c->addAscendingOrderByColumn(self::MISSION_DATE);
} elseif ($sort == 1) {
$c->addDescendingOrderByColumn(self::MISSION_DATE);
}
//$c->addGroupByColumn(MissionPeer::ID);
//$c->addHaving($c->getNewCriterion(MissionPeer::ID, 'COUNT('.MissionPeer::ID.') = MAX('.MissionLegPeer::LEG_NUMBER.') AND mile <= 200', criteria::CUSTOM));
//$c->addAnd($c->getNewCriterion('', 200, Criteria::LESS_EQUAL));
//$c->addAsColumn('mile', "(Round((ACos( Sin(Radians(from_air.latitude)) * Sin(Radians(to_air.latitude)) + Cos(Radians(from_air.latitude)) * Cos(Radians(to_air.latitude)) * Cos(Radians(from_air.longitude)-Radians(to_air.longitude))) * 180/pi())*60*1.1515))");
//$c->add('mile', 200, Criteria::LESS_EQUAL);
// echo $c->toString();exit;
//$c->add(MissionPeer::CANCEL_MISSION, 1 /* ACTIVE MISSIONS */, Criteria::EQUAL); // 1 means mission is active and 0 means mission is cancelled
$c->add(MissionLegPeer::CANCELLED, null, Criteria::ISNULL);
$c->setDistinct(self::ID);
$pager = new sfPropelPager('Mission', $max);
//$pager = new afids_custom_propel_pager('Mission', $max);
$pager->setCriteria($c);
// $pager->setPeerMethod('doSelectJoinCustom');
$pager->setPage($page);
$pager->init();
return $pager;
}
