public function getListofFilesMeetCriteria()
{
$storedCrit = $this->getCriteria();
$qry = CcFilesQuery::create();
$qry->useFkOwnerQuery("subj", "left join");
if (isset($storedCrit["crit"])) {
foreach ($storedCrit["crit"] as $crit) {
$i = 0;
foreach ($crit as $criteria) {
//$spCriteriaPhpName = self::$criteria2PeerMap[$criteria['criteria']];
$spCriteria = $criteria['criteria'];
$spCriteriaModifier = $criteria['modifier'];
$column = CcFilesPeer::getTableMap()->getColumnByPhpName(self::$criteria2PeerMap[$spCriteria]);
// if the column is timestamp, convert it into UTC
if ($column->getType() == PropelColumnTypes::TIMESTAMP) {
$spCriteriaValue = Application_Common_DateHelper::ConvertToUtcDateTimeString($criteria['value']);
/* Check if only a date was supplied and trim
* the time after it is converted to UTC time
*/
if (strlen($criteria['value']) <= 10) {
//extract date only from timestamp in db
$spCriteria = 'date(' . $spCriteria . ')';
$spCriteriaValue = substr($spCriteriaValue, 0, 10);
}
if (isset($criteria['extra'])) {
$spCriteriaExtra = Application_Common_DateHelper::ConvertToUtcDateTimeString($criteria['extra']);
if (strlen($criteria['extra']) <= 10) {
$spCriteriaExtra = substr($spCriteriaExtra, 0, 10);
}
}
} elseif ($spCriteria == "bit_rate" || $spCriteria == 'sample_rate') {
// multiply 1000 because we store only number value
// e.g 192kps is stored as 192000
$spCriteriaValue = $criteria['value'] * 1000;
if (isset($criteria['extra'])) {
$spCriteriaExtra = $criteria['extra'] * 1000;
}
/*
* If user is searching for an exact match of length we need to
* search as if it starts with the specified length because the
* user only sees the rounded version (i.e. 4:02.7 is 4:02.761625
* in the database)
*/
} elseif ($spCriteria == 'length' && $spCriteriaModifier == "is") {
$spCriteriaModifier = "starts with";
$spCriteria = $spCriteria . '::text';
$spCriteriaValue = $criteria['value'];
} else {
/* Propel does not escape special characters properly when using LIKE/ILIKE
* We have to add extra slashes in these cases
*/
$tempModifier = trim(self::$modifier2CriteriaMap[$spCriteriaModifier]);
if ($tempModifier == 'ILIKE') {
$spCriteriaValue = addslashes($criteria['value']);
// addslashes() does not esapce '%' so we have to do it manually
$spCriteriaValue = str_replace('%', '\\%', $spCriteriaValue);
} else {
$spCriteriaValue = $criteria['value'];
}
$spCriteriaExtra = $criteria['extra'];
}
if ($spCriteriaModifier == "starts with") {
$spCriteriaValue = "{$spCriteriaValue}%";
} elseif ($spCriteriaModifier == "ends with") {
$spCriteriaValue = "%{$spCriteriaValue}";
} elseif ($spCriteriaModifier == "contains" || $spCriteriaModifier == "does not contain") {
$spCriteriaValue = "%{$spCriteriaValue}%";
} elseif ($spCriteriaModifier == "is in the range") {
$spCriteriaValue = "{$spCriteria} >= '{$spCriteriaValue}' AND {$spCriteria} <= '{$spCriteriaExtra}'";
}
$spCriteriaModifier = self::$modifier2CriteriaMap[$spCriteriaModifier];
try {
if ($spCriteria == "owner_id") {
$spCriteria = "subj.login";
}
if ($i > 0) {
$qry->addOr($spCriteria, $spCriteriaValue, $spCriteriaModifier);
} else {
$qry->add($spCriteria, $spCriteriaValue, $spCriteriaModifier);
}
if ($spCriteriaModifier == Criteria::NOT_ILIKE || $spCriteriaModifier == Criteria::NOT_EQUAL) {
$qry->addOr($spCriteria, null, Criteria::ISNULL);
}
} catch (Exception $e) {
Logging::info($e);
}
$i++;
}
}
// check if file exists
$qry->add("file_exists", "true", Criteria::EQUAL);
$qry->addAscendingOrderByColumn('random()');
}
// construct limit restriction
$limits = array();
if (isset($storedCrit['limit'])) {
if ($storedCrit['limit']['modifier'] == "items") {
$limits['time'] = 1440 * 60;
$limits['items'] = $storedCrit['limit']['value'];
} else {
$limits['time'] = $storedCrit['limit']['modifier'] == "hours" ? intval(floatval($storedCrit['limit']['value']) * 60 * 60) : intval($storedCrit['limit']['value'] * 60);
$limits['items'] = null;
}
}
try {
$out = $qry->setFormatter(ModelCriteria::FORMAT_ON_DEMAND)->find();
return array("files" => $out, "limit" => $limits, "count" => $out->count());
} catch (Exception $e) {
Logging::info($e);
}
}
public function isValid($params)
{
$isValid = true;
$data = $this->preValidation($params);
$criteria2PeerMap = array(0 => "Select criteria", "album_title" => "DbAlbumTitle", "artist_name" => "DbArtistName", "bit_rate" => "DbBitRate", "bpm" => "DbBpm", "composer" => "DbComposer", "conductor" => "DbConductor", "copyright" => "DbCopyright", "cuein" => "DbCuein", "cueout" => "DbCueout", "encoded_by" => "DbEncodedBy", "utime" => "DbUtime", "mtime" => "DbMtime", "lptime" => "DbLPtime", "genre" => "DbGenre", "info_url" => "DbInfoUrl", "isrc_number" => "DbIsrcNumber", "label" => "DbLabel", "language" => "DbLanguage", "length" => "DbLength", "mime" => "DbMime", "mood" => "DbMood", "owner_id" => "DbOwnerId", "replay_gain" => "DbReplayGain", "sample_rate" => "DbSampleRate", "track_title" => "DbTrackTitle", "track_number" => "DbTrackNumber", "year" => "DbYear");
// things we need to check
// 1. limit value shouldn't be empty and has upperbound of 24 hrs
// 2. sp_criteria or sp_criteria_modifier shouldn't be 0
// 3. validate formate according to DB column type
$multiplier = 1;
$result = 0;
// validation start
if ($data['etc']['sp_limit_options'] == 'hours') {
$multiplier = 60;
}
if ($data['etc']['sp_limit_options'] == 'hours' || $data['etc']['sp_limit_options'] == 'mins') {
$element = $this->getElement("sp_limit_value");
if ($data['etc']['sp_limit_value'] == "" || floatval($data['etc']['sp_limit_value']) <= 0) {
$element->addError(_("Limit cannot be empty or smaller than 0"));
$isValid = false;
} else {
$mins = floatval($data['etc']['sp_limit_value']) * $multiplier;
if ($mins > 1440) {
$element->addError(_("Limit cannot be more than 24 hrs"));
$isValid = false;
}
}
} else {
$element = $this->getElement("sp_limit_value");
if ($data['etc']['sp_limit_value'] == "" || floatval($data['etc']['sp_limit_value']) <= 0) {
$element->addError(_("Limit cannot be empty or smaller than 0"));
$isValid = false;
} elseif (!ctype_digit($data['etc']['sp_limit_value'])) {
$element->addError(_("The value should be an integer"));
$isValid = false;
} elseif (intval($data['etc']['sp_limit_value']) > 500) {
$element->addError(_("500 is the max item limit value you can set"));
$isValid = false;
}
}
if (isset($data['criteria'])) {
foreach ($data['criteria'] as $rowKey => $row) {
foreach ($row as $key => $d) {
$element = $this->getElement("sp_criteria_field_" . $rowKey . "_" . $key);
// check for not selected select box
if ($d['sp_criteria_field'] == "0" || $d['sp_criteria_modifier'] == "0") {
$element->addError(_("You must select Criteria and Modifier"));
$isValid = false;
} else {
$column = CcFilesPeer::getTableMap()->getColumnByPhpName($criteria2PeerMap[$d['sp_criteria_field']]);
// validation on type of column
if (in_array($d['sp_criteria_field'], array('length', 'cuein', 'cueout'))) {
if (!preg_match("/^(\\d{2}):(\\d{2}):(\\d{2})/", $d['sp_criteria_value'])) {
$element->addError(_("'Length' should be in '00:00:00' format"));
$isValid = false;
}
} elseif ($column->getType() == PropelColumnTypes::TIMESTAMP) {
if (!preg_match("/(\\d{4})-(\\d{2})-(\\d{2})/", $d['sp_criteria_value'])) {
$element->addError(_("The value should be in timestamp format (e.g. 0000-00-00 or 0000-00-00 00:00:00)"));
$isValid = false;
} else {
$result = Application_Common_DateHelper::checkDateTimeRangeForSQL($d['sp_criteria_value']);
if (!$result["success"]) {
// check for if it is in valid range( 1753-01-01 ~ 12/31/9999 )
$element->addError($result["errMsg"]);
$isValid = false;
}
}
if (isset($d['sp_criteria_extra'])) {
if (!preg_match("/(\\d{4})-(\\d{2})-(\\d{2})/", $d['sp_criteria_extra'])) {
$element->addError(_("The value should be in timestamp format (e.g. 0000-00-00 or 0000-00-00 00:00:00)"));
$isValid = false;
} else {
$result = Application_Common_DateHelper::checkDateTimeRangeForSQL($d['sp_criteria_extra']);
if (!$result["success"]) {
// check for if it is in valid range( 1753-01-01 ~ 12/31/9999 )
$element->addError($result["errMsg"]);
$isValid = false;
}
}
}
} elseif ($column->getType() == PropelColumnTypes::INTEGER && $d['sp_criteria_field'] != 'owner_id') {
if (!is_numeric($d['sp_criteria_value'])) {
$element->addError(_("The value has to be numeric"));
$isValid = false;
}
// length check
if ($d['sp_criteria_value'] >= pow(2, 31)) {
$element->addError(_("The value should be less then 2147483648"));
$isValid = false;
}
} elseif ($column->getType() == PropelColumnTypes::VARCHAR) {
if (strlen($d['sp_criteria_value']) > $column->getSize()) {
$element->addError(sprintf(_("The value should be less than %s characters"), $column->getSize()));
$isValid = false;
}
}
}
if ($d['sp_criteria_value'] == "") {
$element->addError(_("Value cannot be empty"));
$isValid = false;
}
}
//end foreach
}
//for loop
}
//if
return $isValid;
}
