public function showDBValue(&$data, $keylink)
{
$result = "";
if (IsDateFieldType($this->fieldType)) {
$result = str_format_time(db2time($data[$this->field]));
} else {
$numbers = parsenumbers($data[$this->field]);
if (!count($numbers)) {
return "";
}
while (count($numbers) < 3) {
$numbers[] = 0;
}
$result = str_format_time(array(0, 0, 0, $numbers[0], $numbers[1], $numbers[2]));
}
return $this->checkForEncoding($result, $keylink);
}
/**
* @param &Array data
* @return String
*/
public function getTextValue(&$data)
{
$result = "";
if (IsDateFieldType($this->fieldType)) {
return str_format_time(db2time($data[$this->field]));
}
$numbers = parsenumbers($data[$this->field]);
if (!count($numbers)) {
return "";
}
while (count($numbers) < 3) {
$numbers[] = 0;
}
if (count($numbers) == 6) {
return str_format_time(array(0, 0, 0, $numbers[3], $numbers[4], $numbers[5]));
}
// sometimes data is datetime
return str_format_time(array(0, 0, 0, $numbers[0], $numbers[1], $numbers[2]));
}
function UnlockAdmin($strtable, $keys, $startEdit)
{
$skeys = "";
foreach ($keys as $ind => $val) {
if (strlen($skeys)) {
$skeys .= "&";
}
$skeys .= rawurlencode($val);
}
$sdate = now();
if ($startEdit) {
// add a record - lock
$this->TableObj->startdatetime = $sdate;
$this->TableObj->confirmdatetime = $sdate;
$this->TableObj->sessionid = session_id();
$this->TableObj->table = $strtable;
$this->TableObj->keys = $skeys;
$this->TableObj->userid = $this->UserID;
$this->TableObj->action = 1;
$this->TableObj->Add();
}
// delete all other locking records
$rstmp = CustomQuery("delete from " . AddTableWrappers($this->lockTableName) . " where " . AddFieldWrappers("table") . "=" . db_prepare_string($strtable) . " and " . AddFieldWrappers("keys") . "=" . db_prepare_string($skeys) . " and " . AddFieldWrappers("action") . "=1 and " . AddFieldWrappers("sessionid") . "<>'" . session_id() . "' ");
// inform other users that their locking were removed by locking
$rstmp = CustomQuery("delete from " . AddTableWrappers($this->lockTableName) . " where " . AddFieldWrappers("startdatetime") . "<'" . format_datetime_custom(adddays(db2time(now()), -2), "yyyy-MM-dd HH:mm:ss") . "' and " . AddFieldWrappers("action") . "=2");
$this->TableObj->startdatetime = $sdate;
$this->TableObj->confirmdatetime = $sdate;
$this->TableObj->sessionid = session_id();
$this->TableObj->table = $strtable;
$this->TableObj->keys = $skeys;
$this->TableObj->userid = $this->UserID;
$this->TableObj->action = 2;
$this->TableObj->Add();
}
/**
* Get value for field edit as time and get dpTime settings
* @param integer $convention - 24 or 12 hours format for timePicker
* @param string $type - type of field
* @param string $value - value of field
* @param boolean $useTimePicker - use timePicker or not
* @return array
*/
function getValForTimePicker($type, $value, $locale)
{
$val = "";
$dbtime = array();
if (IsDateFieldType($type)) {
$dbtime = db2time($value);
if (count($dbtime)) {
$val = format_datetime_custom($dbtime, $locale);
}
} else {
$arr = parsenumbers($value);
if (count($arr)) {
while (count($arr) < 3) {
$arr[] = 0;
}
$dbtime = array(0, 0, 0, $arr[0], $arr[1], $arr[2]);
$val = format_datetime_custom($dbtime, $locale);
}
}
return array('val' => $val, 'dbTime' => $dbtime);
}
/**
* @intellisense
*/
function toPHPTime($datevalue)
{
$arr = db2time($datevalue);
return mktime($arr[3], $arr[4], $arr[5], $arr[1], $arr[2], $arr[0]);
}
function getKey($data)
{
if (!$this->_recordBasedRequest) {
if ($this->_interval == 0) {
return $data[$this->alias()];
} else {
$key = array();
for ($nCnt = $this->_start; $nCnt < $this->_interval + $this->_start; $nCnt++) {
$key[] = $data[$this->_alias . $nCnt];
}
return join('-', $key);
}
} else {
$strdate = $data[$this->_name];
if ($strdate == null) {
return 'NULL';
}
if ($this->_interval == 0) {
return $strdate;
} else {
switch ($this->_interval) {
case 1:
$date = cached_db2time($strdate);
return $date[0];
case 2:
$date = cached_db2time($strdate);
return $date[0] . '-' . intval($date[1] / 3);
case 3:
$date = cached_db2time($strdate);
return $date[0] . '-' . $date[1];
case 4:
$start = cached_getweekstart($strdate);
return $start[0] . '-' . $start[1] . '-' . $start[2];
case 5:
$date = cached_db2time($strdate);
return $date[0] . '-' . $date[1] . '-' . $date[2];
case 6:
$date = db2time($strdate);
return $date[0] . '-' . $date[1] . '-' . $date[2] . '-' . $date[3];
case 7:
$date = db2time($strdate);
return $date[0] . '-' . $date[1] . '-' . $date[2] . '-' . $date[3] . '-' . $date[4];
}
}
}
}
function UnlockAdmin($strtable, $keys, $startEdit)
{
$skeys = "";
foreach ($keys as $ind => $val) {
if (strlen($skeys)) {
$skeys .= "&";
}
$skeys .= rawurlencode($val);
}
$sdate = now();
if ($startEdit) {
// add a record - lock
$this->insert($strtable, $sdate, $sdate, $skeys, session_id(), $this->UserID, 1);
}
// delete all other locking records
$where = $this->connection->addFieldWrappers("table") . "=" . $this->connection->prepareString($strtable) . " AND " . $this->connection->addFieldWrappers("keys") . "=" . $this->connection->prepareString($skeys) . " AND " . $this->connection->addFieldWrappers("action") . "=1 AND " . $this->connection->addFieldWrappers("sessionid") . "<>" . $this->connection->prepareString(session_id());
$this->delete($where);
// inform other users that their locking were removed by locking
$where = $this->connection->addFieldWrappers("startdatetime") . "<" . $this->connection->addDateQuotes(format_datetime_custom(adddays(db2time(now()), -2), "yyyy-MM-dd HH:mm:ss")) . " AND " . $this->connection->addFieldWrappers("action") . "=2";
$this->delete($where);
$this->insert($strtable, $sdate, $sdate, $skeys, session_id(), $this->UserID, 2);
}
public function showDBValue(&$data, $keylink)
{
return format_shortdate(db2time($data[$this->field]));
}
function buildControl($value, $mode, $fieldNum, $validate, $additionalCtrlParams, $data)
{
global $locale_info;
parent::buildControl($value, $mode, $fieldNum, $validate, $additionalCtrlParams, $data);
if ($fieldNum) {
$this->cfield = "value" . $fieldNum . "_" . GoodFieldName($this->field) . '_' . $this->id;
}
echo '<input id="' . $this->ctype . '" type="hidden" name="' . $this->ctype . '" value="date' . $this->pageObject->pSetEdit->getDateEditType($this->field) . '">';
if ($this->pageObject->pageType == PAGE_LIST) {
$pSet = new ProjectSettings($this->pageObject->tName, PAGE_SEARCH);
} else {
$pSet = $this->pageObject->pSetEdit;
}
$tvalue = $value;
$time = db2time($tvalue);
if (!count($time)) {
$time = array(0, 0, 0, 0, 0, 0);
}
$dp = 0;
$hasImgCal = true;
$showTime = $pSet->dateEditShowTime($this->field);
switch ($pSet->getDateEditType($this->field)) {
case EDIT_DATE_SIMPLE_INLINE:
$hasImgCal = false;
case EDIT_DATE_SIMPLE_DP:
$ovalue = $value;
if ($locale_info["LOCALE_IDATE"] == 1) {
$fmt = "dd" . $locale_info["LOCALE_SDATE"] . "MM" . $locale_info["LOCALE_SDATE"] . "yyyy";
} else {
if ($locale_info["LOCALE_IDATE"] == 0) {
$fmt = "MM" . $locale_info["LOCALE_SDATE"] . "dd" . $locale_info["LOCALE_SDATE"] . "yyyy";
} else {
$fmt = "yyyy" . $locale_info["LOCALE_SDATE"] . "MM" . $locale_info["LOCALE_SDATE"] . "dd";
}
}
if ($showTime || $time[3] || $time[4] || $time[5]) {
$fmt .= " HH:mm:ss";
}
if ($time[0]) {
$ovalue = format_datetime_custom($time, $fmt);
}
$ovalue1 = $time[2] . "-" . $time[1] . "-" . $time[0];
if ($showTime || $time[3] || $time[4] || $time[5]) {
$ovalue1 .= " " . $time[3] . ":" . $time[4] . ":" . $time[5];
}
// need to create date control object to use it with datePicker
$ret = '<input id="' . $this->cfield . '" ' . $this->inputStyle . ' type="Text" name="' . $this->cfield . '" value="' . $ovalue . '">';
$ret .= '<input id="ts' . $this->cfield . '" type="Hidden" name="ts' . $this->cfield . '" value="' . $ovalue1 . '">';
if ($hasImgCal) {
$ret .= ' <a href="#" id="imgCal_' . $this->cfield . '" data-icon="calendar" title="Click Here to Pick up the date" ></a>';
}
echo $ret;
break;
case EDIT_DATE_DD_INLINE:
case EDIT_DATE_DD_DP:
$dp = 1;
case EDIT_DATE_DD:
$controlWidth = $pSet->getControlWidth($this->field);
if ($controlWidth > 0) {
$controlWidth -= 10;
$yearWidth = floor($controlWidth * 0.3);
$yearStyle = 'style="min-width: ' . $yearWidth . 'px; ';
$dayWidth = floor($controlWidth * 0.2);
$dayStyle = 'style="min-width: ' . $dayWidth . 'px; margin-right:5px;" ';
$mothWidth = $controlWidth - $yearWidth - $dayWidth;
$monthStyle = 'style="min-width: ' . $mothWidth . 'px; margin-right:5px;" ';
} else {
$dayStyle = '';
$monthStyle = '';
$yearStyle = '';
}
$alt = ($mode == MODE_INLINE_EDIT || $mode == MODE_INLINE_ADD) && $this->is508 ? 'alt="' . $this->strLabel . '" ' : '';
$retday = '<select id="day' . $this->cfield . '" ' . $dayStyle . $alt . 'name="day' . $this->cfield . '" ></select>';
$retmonth = '<select id="month' . $this->cfield . '" ' . $monthStyle . $alt . 'name="month' . $this->cfield . '" ></select>';
$retyear = '<select id="year' . $this->cfield . '" ' . $yearStyle . $alt . 'name="year' . $this->cfield . '" ></select>';
$space = $controlWidth > 0 ? '' : " ";
if ($locale_info["LOCALE_ILONGDATE"] == 1) {
$ret = $retday . $space . $retmonth . $space . $retyear;
} else {
if ($locale_info["LOCALE_ILONGDATE"] == 0) {
$ret = $retmonth . $space . $retday . $space . $retyear;
} else {
$ret = $retyear . $space . $retmonth . $space . $retday;
}
}
if ($time[0] && $time[1] && $time[2]) {
$ret .= "<input id=\"" . $this->cfield . "\" type=hidden name=\"" . $this->cfield . "\" value=\"" . $time[0] . "-" . $time[1] . "-" . $time[2] . "\">";
} else {
$ret .= "<input id=\"" . $this->cfield . "\" type=hidden name=\"" . $this->cfield . "\" value=\"\">";
}
// calendar handling for three DD
if ($dp) {
$ret .= ' <a href="#" id="imgCal_' . $this->cfield . '" data-icon="calendar" title="Click Here to Pick up the date"></a>' . '<input id="ts' . $this->cfield . '" type=hidden name="ts' . $this->cfield . '" value="' . $time[2] . '-' . $time[1] . '-' . $time[0] . '">';
}
echo $ret;
break;
default:
// case EDIT_DATE_SIMPLE:
$ovalue = $value;
if ($time[0]) {
if ($showTime || $time[3] || $time[4] || $time[5]) {
$ovalue = str_format_datetime($time);
} else {
$ovalue = format_shortdate($time);
}
}
echo '<input id="' . $this->cfield . '" type=text name="' . $this->cfield . '" ' . $this->inputStyle . ' value="' . runner_htmlspecialchars($ovalue) . '">';
}
$this->buildControlEnd($validate);
}
/**
* Get the date slider's where
* @return string
*/
static function getDateSliderWhere($fName, $pSet, $cipherer, $table, $SearchFor, $SearchFor2, $strSearchOption, $fullFieldName)
{
$firstDelimPos = strpos($SearchFor, "-");
$lastDelimPos = strrpos($SearchFor, "-");
if ($firstDelimPos === FALSE || $firstDelimPos == $lastDelimPos) {
return "";
}
$stepType = $pSet->getFilterStepType($fName);
$timeValueEnvolved = false;
if ($stepType == FSST_SECONDS || $stepType == FSST_MINUTES || $stepType == FSST_HOURS) {
$timeValueEnvolved = true;
}
$value1 = $cipherer->MakeDBValue($fName, $SearchFor, "", true);
switch ($strSearchOption) {
case "slider":
$firstDelimPos = strpos($SearchFor2, "-");
$lastDelimPos = strrpos($SearchFor2, "-");
if ($firstDelimPos === FALSE || $firstDelimPos == $lastDelimPos) {
return "";
}
$cleanvalue2 = prepare_for_db($fName, $SearchFor2, "");
$timeArr = db2time($cleanvalue2);
if (!$timeValueEnvolved) {
// for dates without time, add one day
$timeArr = adddays($timeArr, 1);
$value2 = $timeArr[0] . "-" . $timeArr[1] . "-" . $timeArr[2];
} else {
if ($stepType == FSST_SECONDS) {
$timeArr = addSeconds($timeArr, 1);
} else {
$timeArr = addMinutes($timeArr, 1);
}
$dateString = $timeArr[0] . "-" . $timeArr[1] . "-" . $timeArr[2];
$hours = $timeArr[3] < 10 ? '0' . $timeArr[3] : $timeArr[3];
$minutes = $timeArr[4] < 10 ? '0' . $timeArr[4] : $timeArr[4];
$seconds = $timeArr[5] < 10 ? '0' . $timeArr[5] : $timeArr[5];
$timeString = $hours . ":" . $minutes . ":" . $seconds;
$value2 = $dateString . " " . $timeString;
}
$value2 = add_db_quotes($fName, $value2, $table);
return $fullFieldName . ">=" . $value1 . " and " . $fullFieldName . "<" . $value2;
case 'moreequal':
return $fullFieldName . ">=" . $value1;
case 'lessequal':
return $fullFieldName . "<=" . $value1;
default:
return "";
}
}
/**
* Get the WHERE clause conditions string for the search or suggest SQL query
* @param String SearchFor
* @param String strSearchOption
* @param String SearchFor2
* @param String etype
* @param Boolean isSuggest
* @return String
*/
function SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest)
{
if ($this->lookupType == LT_LISTOFVALUES) {
return parent::SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest);
}
$baseResult = $this->baseSQLWhere($strSearchOption);
if ($baseResult === false) {
return "";
}
if ($baseResult !== "") {
return $baseResult;
}
if ($this->connection->dbType != nDATABASE_MySQL) {
$this->btexttype = IsTextType($this->type);
}
if ($this->multiselect && $strSearchOption != "Equals") {
$SearchFor = splitvalues($SearchFor);
} else {
$SearchFor = array($SearchFor);
}
$gstrField = $this->getFieldSQLDecrypt();
$gstrField = $this->getFieldSQLDecrypt();
if (($strSearchOption == "Starts with" || $strSearchOption == "Contains") && (!IsCharType($this->type) || $this->btexttype)) {
$gstrField = $this->connection->field2char($gstrField, $this->type);
}
$ret = "";
foreach ($SearchFor as $searchItem) {
$value = $searchItem;
if ($value == "null" || $value == "Null" || $value == "") {
continue;
}
if (strlen(trim($ret))) {
$ret .= " or ";
}
if (($strSearchOption == "Starts with" || $strSearchOption == "Contains") && !$this->multiselect) {
$value = $this->connection->escapeLIKEpattern($value);
if ($strSearchOption == "Starts with") {
$value .= '%';
}
if ($strSearchOption == "Contains") {
$value = '%' . $value . '%';
}
}
if ($strSearchOption != "Starts with" && $strSearchOption != "Contains") {
$value = make_db_value($this->field, $value);
}
$searchIsCaseInsensitive = $this->pageObject->pSetEdit->getNCSearch();
if ($strSearchOption == "Equals" && !($value == "null" || $value == "Null")) {
$condition = $gstrField . '=' . $value;
} else {
if (($strSearchOption == "Starts with" || $strSearchOption == "Contains") && !$this->multiselect) {
$condition = $gstrField . " " . $this->like . " " . $this->connection->prepareString($value);
} else {
if ($strSearchOption == "More than") {
$condition = $gstrField . " > " . $value;
} else {
if ($strSearchOption == "Less than") {
$condition = $gstrField . "<" . $value;
} else {
if ($strSearchOption == "Equal or more than") {
$condition = $gstrField . ">=" . $value1;
} else {
if ($strSearchOption == "Equal or less than") {
$condition = $gstrField . "<=" . $value1;
} else {
if ($strSearchOption == "Between") {
$value2 = $this->connection->prepareString($SearchFor2);
if ($this->lookupType == LT_QUERY && IsCharType($this->type) && !$this->btexttype && $searchIsCaseInsensitive) {
$value2 = $this->connection->upper($value2);
}
$condition = $gstrField . ">=" . $value . " and ";
if (IsDateFieldType($this->type)) {
$timeArr = db2time($SearchFor2);
// for dates without time, add one day
if ($timeArr[3] == 0 && $timeArr[4] == 0 && $timeArr[5] == 0) {
$timeArr = adddays($timeArr, 1);
$SearchFor2 = $timeArr[0] . "-" . $timeArr[1] . "-" . $timeArr[2];
$SearchFor2 = add_db_quotes($this->field, $SearchFor2, $this->tName);
$condition .= $gstrField . "<" . $SearchFor2;
} else {
$condition .= $gstrField . "<=" . $value2;
}
} else {
$condition .= $gstrField . "<=" . $value2;
}
} else {
if ($this->multiselect) {
if (strpos($value, ",") !== false || strpos($value, '"') !== false) {
$value = '"' . str_replace('"', '""', $value) . '"';
}
$fullFieldName = $this->getFieldSQLDecrypt();
$value = $this->connection->escapeLIKEpattern($value);
//for search by multiply Lookup wizard field
$ret .= $fullFieldName . " = " . $this->connection->prepareString($value);
$ret .= " or " . $fullFieldName . " " . $this->like . " " . $this->connection->prepareString("%," . $value . ",%");
$ret .= " or " . $fullFieldName . " " . $this->like . " " . $this->connection->prepareString("%," . $value);
$ret .= " or " . $fullFieldName . " " . $this->like . " " . $this->connection->prepareString($value . ",%");
}
}
}
}
}
}
}
}
if ($condition != "" && ($isSuggest || $strSearchOption == "Contains" || $strSearchOption == "Equals" || $strSearchOption == "Starts with" || $strSearchOption == "More than" || $strSearchOption == "Less than" || $strSearchOption == "Equal or more than" || $strSearchOption == "Equal or less than" || $strSearchOption == "Between")) {
if ($this->linkAndDisplaySame || $strSearchOption != "Contains" && $strSearchOption != "Starts with") {
$ret .= " " . $condition;
} else {
return "";
}
}
}
$ret = trim($ret);
if (strlen($ret)) {
$ret = "(" . $ret . ")";
}
return $ret;
}
/**
* @param &Array data
* @return String
*/
public function getTextValue(&$data)
{
return str_format_datetime(db2time($data[$this->field]));
}
function SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest)
{
if ($this->lookupType == LT_LISTOFVALUES) {
return parent::SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest);
}
$baseResult = $this->baseSQLWhere($strSearchOption);
if ($baseResult === false) {
return "";
}
if ($baseResult != "") {
return $baseResult;
}
$displayFieldType = $this->type;
if ($this->lookupType == LT_QUERY) {
$displayFieldType = $this->lookupPSet->getFieldType($this->field);
$this->btexttype = IsTextType($displayFieldType);
}
if ($this->multiselect) {
$SearchFor = splitvalues($SearchFor);
} else {
$SearchFor = array($SearchFor);
}
$ret = "";
if ($this->linkAndDisplaySame) {
$gstrField = GetFullFieldName($this->field, "", false);
} else {
$gstrField = GetFullFieldName($this->displayFieldName, $this->lookupTable, false);
}
if ($this->customDisplay) {
$gstrField = $this->lwDisplayFieldWrapped;
} else {
if (!$this->linkAndDisplaySame && $this->lookupType == LT_QUERY && IsCharType($displayFieldType) && !$this->btexttype && !$this->ciphererDisplay->isFieldPHPEncrypted($this->displayFieldName)) {
$gstrField = $this->lookupPSet->isEnableUpper(GetFullFieldName($this->displayFieldName, $this->lookupTable, false));
}
}
foreach ($SearchFor as $value) {
if (!($value == "null" || $value == "Null" || $value == "")) {
if (strlen(trim($ret))) {
$ret .= " or ";
}
if (!$this->multiselect) {
if ($strSearchOption == "Starts with") {
$value .= '%';
}
if ($isSuggest || $strSearchOption == "Contains") {
$value = '%' . $value . '%';
}
if ($isSuggest || $strSearchOption == "Contains" || $strSearchOption == "Starts with" || $strSearchOption == "More than" || $strSearchOption == "Less than" || $strSearchOption == "Equal or more than" || $strSearchOption == "Equal or less than" || $strSearchOption == "Between" || $strSearchOption == "Equals" && $this->LCType == LCT_AJAX && !$this->linkAndDisplaySame) {
$value = $this->escapeSearchValForMySQL($value);
if ($this->lookupType == LT_QUERY && IsCharType($displayFieldType) && !$this->btexttype) {
$value = $this->lookupPSet->isEnableUpper(db_prepare_string($value));
} else {
$value = db_prepare_string($value);
}
} else {
if ($strSearchOption == "Equals") {
$value = make_db_value($this->field, $value);
}
}
}
if ($strSearchOption == "Equals") {
if (!($value == "null" || $value == "Null")) {
if ($this->LCType == LCT_AJAX && !$this->linkAndDisplaySame) {
$condition = $gstrField . '=' . $value;
} else {
$condition = GetFullFieldName($this->field, "", false) . '=' . $value;
}
}
} else {
if ($strSearchOption == "Starts with" || $strSearchOption == "Contains" && !$this->multiselect) {
$condition = $gstrField . " " . $this->like . " " . $value;
} else {
if ($strSearchOption == "More than") {
$condition = $gstrField . " > " . $value;
} else {
if ($strSearchOption == "Less than") {
$condition = $gstrField . "<" . $value;
} else {
if ($strSearchOption == "Equal or more than") {
$condition = $gstrField . ">=" . $value1;
} else {
if ($strSearchOption == "Equal or less than") {
$condition = $gstrField . "<=" . $value1;
} else {
if ($strSearchOption == "Between") {
if ($this->lookupType == LT_QUERY && IsCharType($displayFieldType) && !$this->btexttype) {
$value2 = $this->lookupPSet->isEnableUpper(db_prepare_string($SearchFor2));
} else {
$value2 = db_prepare_string($SearchFor2);
}
$condition = $gstrField . ">=" . $value . " and ";
if (IsDateFieldType($this->type)) {
$timeArr = db2time($SearchFor2);
// for dates without time, add one day
if ($timeArr[3] == 0 && $timeArr[4] == 0 && $timeArr[5] == 0) {
$timeArr = adddays($timeArr, 1);
$SearchFor2 = $timeArr[0] . "-" . $timeArr[1] . "-" . $timeArr[2];
$SearchFor2 = add_db_quotes($this->field, $SearchFor2, $this->pageObject->tName);
$condition .= $gstrField . "<" . $SearchFor2;
} else {
$condition .= $gstrField . "<=" . $value2;
}
} else {
$condition .= $gstrField . "<=" . $value2;
}
} else {
if (strpos($value, ",") !== false || strpos($value, '"') !== false) {
$value = '"' . str_replace('"', '""', $value) . '"';
}
$value = $this->escapeSearchValForMySQL($value);
//for search by multiply Lookup wizard field
$ret .= GetFullFieldName($this->field, "", false) . " = " . db_prepare_string($value);
$ret .= " or " . GetFullFieldName($this->field, "", false) . " " . $this->like . " " . db_prepare_string("%," . $value . ",%");
$ret .= " or " . GetFullFieldName($this->field, "", false) . " " . $this->like . " " . db_prepare_string("%," . $value);
$ret .= " or " . GetFullFieldName($this->field, "", false) . " " . $this->like . " " . db_prepare_string($value . ",%");
}
}
}
}
}
}
}
if ($condition != "" && ($isSuggest || $strSearchOption == "Contains" || $strSearchOption == "Equals" || $strSearchOption == "Starts with" || $strSearchOption == "More than" || $strSearchOption == "Less than" || $strSearchOption == "Equal or more than" || $strSearchOption == "Equal or less than" || $strSearchOption == "Between")) {
if ($this->linkAndDisplaySame || $strSearchOption == "Equals" && $this->LCType != LCT_AJAX) {
$ret .= " " . $condition;
} else {
if ($this->lookupType == LT_QUERY) {
$lookupQueryObj = $this->lookupPSet->getSQLQuery();
$ret .= " EXISTS (" . $lookupQueryObj->toSql($condition . " and " . GetFullFieldName($this->linkFieldName, $this->lookupTable, false) . " = " . AddTableWrappers($this->pageObject->pSetEdit->getStrOriginalTableName()) . "." . AddFieldWrappers($this->field), '', null, false) . ")";
} else {
$ret .= " EXISTS (SELECT 1 as fld from " . AddTableWrappers($this->lookupTable) . " where " . $condition . " and " . $this->lwLinkField . " = " . AddTableWrappers($this->pageObject->pSetEdit->getStrOriginalTableName()) . "." . AddFieldWrappers($this->field) . ")";
}
}
}
}
}
if (strlen(trim($ret))) {
$ret = "(" . $ret . ")";
} else {
$ret = trim($ret);
}
return $ret;
}
/**
* @param Mixed $val
* @return String
*/
public function addDateQuotes($val)
{
$arrDate = db2time($val);
return "'" . $arrDate[0] . "-" . $arrDate[1] . "-" . $arrDate[2] . " " . $arrDate[3] . ":" . $arrDate[4] . ":" . $arrDate[5] . "'";
}
/**
* @param &Array data
* @return String
*/
public function getTextValue(&$data)
{
return format_shortdate(db2time($data[$this->field]));
}
function LogAddEvent($message, $description = "", $str_table = "")
{
global $globalEvents;
$retval = true;
$table = $str_table;
$arr = array();
if ($globalEvents->exists("OnAuditLog")) {
$retval = $globalEvents->OnAuditLog($message, $this->params, $table, $arr, $arr, $arr);
}
if ($retval) {
$fp = $this->CreateLogFile();
$str = format_datetime_custom(db2time(now()), "MMM dd,yyyy") . chr(9) . format_datetime_custom(db2time(now()), "HH:mm:ss") . chr(9) . $params[0] . chr(9) . $params[1] . chr(9) . $table . chr(9) . $message . chr(9) . $description . "\r\n";
if ($fp) {
fputs($fp, $str);
fclose($fp);
}
}
return $retval;
}
/**
* Get the WHERE clause conditions string for the search or suggest SQL query
* @param String SearchFor
* @param String strSearchOption
* @param String SearchFor2
* @param String etype
* @param Boolean isSuggest
*/
function SQLWhere($SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest)
{
$baseResult = $this->baseSQLWhere($strSearchOption);
if ($baseResult === false) {
return "";
}
if ($baseResult != "") {
return $baseResult;
}
if (!strlen($SearchFor)) {
return "";
}
$value1 = $this->pageObject->cipherer->MakeDBValue($this->field, $SearchFor, $etype, true);
$value2 = false;
$cleanvalue2 = false;
if ($strSearchOption == "Between") {
$cleanvalue2 = prepare_for_db($this->field, $SearchFor2, $etype);
$value2 = make_db_value($this->field, $SearchFor2, $etype);
}
if ($strSearchOption != "Contains" && $strSearchOption != "Starts with" && ($value1 === "null" || $value2 === "null") && !$this->pageObject->cipherer->isFieldPHPEncrypted($this->field)) {
return "";
}
if (($strSearchOption == "Contains" || $strSearchOption == "Starts with") && !$this->isStringValidForLike($SearchFor)) {
return "";
}
$searchIsCaseInsensitive = $this->pageObject->pSetEdit->getNCSearch();
if (IsCharType($this->type) && !$this->btexttype) {
$gstrField = $this->getFieldSQLDecrypt();
if (!$this->pageObject->cipherer->isFieldPHPEncrypted($this->field) && $searchIsCaseInsensitive) {
$value1 = $this->connection->upper($value1);
$value2 = $this->connection->upper($value2);
$gstrField = $this->connection->upper($gstrField);
}
} elseif ($strSearchOption == "Contains" || $strSearchOption == "Starts with") {
$gstrField = $this->connection->field2char($this->getFieldSQLDecrypt(), $this->type);
} elseif ($this->pageObject->pSetEdit->getViewFormat($this->field) == FORMAT_TIME) {
$gstrField = $this->connection->field2time($this->getFieldSQLDecrypt(), $this->type);
} else {
$gstrField = $this->getFieldSQLDecrypt();
}
if ($strSearchOption == "Contains") {
if ($this->pageObject->cipherer->isFieldPHPEncrypted($this->field)) {
return $gstrField . "=" . $this->pageObject->cipherer->MakeDBValue($this->field, $SearchFor);
}
$SearchFor = $this->connection->escapeLIKEpattern($SearchFor);
if (IsCharType($this->type) && !$this->btexttype && $searchIsCaseInsensitive) {
return $gstrField . " " . $this->like . " " . $this->connection->upper($this->connection->prepareString("%" . $SearchFor . "%"));
}
return $gstrField . " " . $this->like . " " . $this->connection->prepareString("%" . $SearchFor . "%");
}
if ($strSearchOption == "Equals") {
return $gstrField . "=" . $value1;
}
if ($strSearchOption == "Starts with") {
$SearchFor = $this->connection->escapeLIKEpattern($SearchFor);
if (IsCharType($this->type) && !$this->btexttype && $searchIsCaseInsensitive) {
return $gstrField . " " . $this->like . " " . $this->connection->upper($this->connection->prepareString($SearchFor . "%"));
}
return $gstrField . " " . $this->like . " " . $this->connection->prepareString($SearchFor . "%");
}
if ($strSearchOption == "More than") {
return $gstrField . ">" . $value1;
}
if ($strSearchOption == "Less than") {
return $gstrField . "<" . $value1;
}
if ($strSearchOption == "Equal or more than") {
return $gstrField . ">=" . $value1;
}
if ($strSearchOption == "Equal or less than") {
return $gstrField . "<=" . $value1;
}
if ($strSearchOption == "Between") {
$ret = $gstrField . ">=" . $value1 . " and ";
if (IsDateFieldType($this->type)) {
$timeArr = db2time($cleanvalue2);
// for dates without time, add one day
if ($timeArr[3] == 0 && $timeArr[4] == 0 && $timeArr[5] == 0) {
$timeArr = adddays($timeArr, 1);
$value2 = $timeArr[0] . "-" . $timeArr[1] . "-" . $timeArr[2];
$value2 = add_db_quotes($this->field, $value2, $this->pageObject->tName);
$ret .= $gstrField . "<" . $value2;
} else {
$ret .= $gstrField . "<=" . $value2;
}
} else {
$ret .= $gstrField . "<=" . $value2;
}
return $ret;
}
return "";
}
function getDisplayValue($index, $value)
{
global $locale_info;
$field = $this->xml_array["group_fields"][$index]["name"];
if ($value == "" || is_null($value)) {
return "";
}
$ftype = $this->getFieldType($field);
$arr = array();
$arr = $this->xml_array["group_fields"];
for ($i = 0; $i < count($arr) - 1; $i++) {
if ($field == $arr[$i]["name"] && $index == $i) {
$int_type = $arr[$i]["int_type"];
break;
}
}
if ($int_type == 0) {
$prefix = "";
if ($this->table_type != "db") {
if (!$this->fromWizard) {
$prefix = $this->xml_array["tables"][0] . "_";
}
} else {
$field = $this->CrossGoodFieldName($field);
}
if ($this->xml_array['totals'][$prefix . $field]['curr'] == true) {
return str_format_currency($value);
} else {
return $value;
}
} elseif (IsNumberType($ftype)) {
$start = $value - $value % $int_type;
$end = $start + $int_type;
$prefix = "";
if ($this->table_type != "db") {
if (!$this->fromWizard) {
$prefix = $this->xml_array["tables"][0] . "_";
}
} else {
$field = $this->CrossGoodFieldName($field);
}
if ($this->xml_array['totals'][$prefix . $field]['curr'] == true) {
return str_format_currency($start) . " - " . str_format_currency($end);
} else {
return $start . " - " . $end;
}
} elseif (IsCharType($ftype)) {
return substr($value, 0, $int_type);
} elseif (IsDateFieldType($ftype)) {
$dvalue = substr($value, 0, 4) . '-' . substr($value, 4, 2) . '-' . substr($value, 6, 2);
if (strlen($value) == 10) {
$dvalue .= " " . substr($value, 8, 2) . "00:00";
} elseif (strlen($value) == 12) {
$dvalue .= " " . substr($value, 8, 2) . ":" . substr($value, 10, 2) . ":00";
}
$tm = db2time($dvalue);
if (!count($tm)) {
return "";
}
if ($int_type == 1) {
// DATE_INTERVAL_YEAR
return $tm[0];
} elseif ($int_type == 2) {
// DATE_INTERVAL_QUARTER
return $tm[0] . "/Q" . $tm[1];
} elseif ($int_type == 3) {
// DATE_INTERVAL_MONTH
return @$locale_info["LOCALE_SABBREVMONTHNAME" . $tm[1]] . " " . $tm[0];
} elseif ($int_type == 4) {
// DATE_INTERVAL_WEEK
$dates = $this->getDatesByWeek($tm[1] + 1, $tm[0]);
return format_shortdate(db2time($dates[0])) . ' - ' . format_shortdate(db2time($dates[1]));
} elseif ($int_type == 5) {
// DATE_INTERVAL_DAY
return format_shortdate($tm);
} elseif ($int_type == 6) {
// DATE_INTERVAL_HOUR
$tm[4] = 0;
$tm[5] = 0;
return str_format_datetime($tm);
} elseif ($int_type == 7) {
// DATE_INTERVAL_MINUTE
$tm[5] = 0;
return str_format_datetime($tm);
} else {
return str_format_datetime($tm);
}
}
}
function GetGroupDisplay($field, $value) {
global $locale_info, $rpt_array, $tbl, $fields_type;
for ($i = 0; $i < count($rpt_array['group_fields']) - 1; $i++){
$arr = $rpt_array['group_fields'][$i];
foreach ($rpt_array['totals'] as $fld){
if ($arr['name'] == fldname($fld)) {
if (!is_wr_custom())
$ftype = WRGetFieldType($fld['table'] . "." . $fld['name']);
else
$ftype = $fields_type[$fld['name']];
if ($field == $arr['name']) {
if ($arr['int_type'] == 0) {
$prefix = "";
if (!is_wr_db())
$prefix = $rpt_array["tables"][0] . "_";
else
$field = GoodFieldName($field);
if ($rpt_array['totals'][$prefix . $field]['curr'] == "true")
return str_format_currency($value);
else
return $value;
} elseif (IsNumberType($ftype)) {
$start = $value - ($value % $arr['int_type']);
$end = $start + $arr['int_type'];
$prefix = "";
if (!is_wr_db())
$prefix = $rpt_array["tables"][0] . "_";
else
$field = GoodFieldName($field);
if ($rpt_array['totals'][$prefix . $field]['curr'] == "true")
return str_format_currency($start) . " - " . str_format_currency($end);
else
return $start . " - " . $end;
} elseif (IsCharType($ftype)) {
return substr($value, 0, $arr['int_type']);
} elseif (IsDateFieldType($ftype)) {
$tm = db2time($value);
if (!count($tm))
return "";
if ($arr['int_type'] == 1) { // DATE_INTERVAL_YEAR
return $tm[0];
} elseif ($arr['int_type'] == 2) { // DATE_INTERVAL_QUARTER
return $tm[0] . "/Q" . floor(($tm[1] - 1) / 4 + 1);
} elseif ($arr['int_type'] == 3) { // DATE_INTERVAL_MONTH
return @$locale_info["LOCALE_SABBREVMONTHNAME" . $tm[1]] . " " . $tm[0];
} elseif ($arr['int_type'] == 4) { // DATE_INTERVAL_WEEK
$start = getweekstart($tm);
$end = adddays($start, 6);
return format_shortdate($start) . " - " . format_shortdate($end);
} elseif ($arr['int_type'] == 5) { // DATE_INTERVAL_DAY
return format_shortdate($tm);
} elseif ($arr['int_type'] == 6) { // DATE_INTERVAL_HOUR
$tm[4] = 0;
$tm[5] = 0;
return str_format_datetime($tm);
} elseif ($arr['int_type'] == 7) { // DATE_INTERVAL_MINUTE
$tm[5] = 0;
return str_format_datetime($tm);
} else {
return str_format_datetime($tm);
}
}
}
}
}
}
}
/**
* Get axes displyed values
* @param Number index
* @param String value
* @return String
*/
protected function getAxisDisplayValue($index, $value)
{
global $locale_info;
if ($value == "" || is_null($value)) {
return "";
}
$groupFieldsData = $this->xml_array["group_fields"];
$field = $groupFieldsData[$index]["name"];
$int_type = $groupFieldsData[$index]["int_type"];
if ($this->fromWizard) {
$control = $this->viewControls->getControl($field);
}
if ($int_type == 0) {
// The 'Normal' interval is set
if ($this->fromWizard) {
$data = array($field => $value);
return $control->showDBValue($data, "");
}
if ($this->table_type != "db") {
$fieldIdentifier = $this->xml_array["tables"][0] . "_" . $field;
} else {
$fieldIdentifier = $this->CrossGoodFieldName($field);
}
if ($this->xml_array['totals'][$fieldIdentifier]['curr'] == true) {
return str_format_currency($value);
}
return xmlencode($value);
}
$ftype = $this->getFieldType($field);
if (IsNumberType($ftype)) {
$start = $value - $value % $int_type;
$end = $start + $int_type;
if ($this->fromWizard) {
$dataStart = array($field => $start);
$dataEnd = array($field => $end);
return $control->showDBValue($dataStart, "") . " - " . $control->showDBValue($dataEnd, "");
}
if ($this->table_type != "db") {
$fieldIdentifier = $this->xml_array["tables"][0] . "_" . $field;
} else {
$fieldIdentifier = $this->CrossGoodFieldName($field);
}
if ($this->xml_array['totals'][$fieldIdentifier]['curr'] == true) {
return str_format_currency($start) . " - " . str_format_currency($end);
}
return $start . " - " . $end;
}
if (IsCharType($ftype)) {
return xmlencode(substr($value, 0, $int_type));
}
if (IsDateFieldType($ftype)) {
$dvalue = substr($value, 0, 4) . '-' . substr($value, 4, 2) . '-' . substr($value, 6, 2);
if (strlen($value) == 10) {
$dvalue .= " " . substr($value, 8, 2) . "00:00";
} elseif (strlen($value) == 12) {
$dvalue .= " " . substr($value, 8, 2) . ":" . substr($value, 10, 2) . ":00";
}
$tm = db2time($dvalue);
if (!count($tm)) {
return "";
}
switch ($int_type) {
case 1:
// DATE_INTERVAL_YEAR
return $tm[0];
case 2:
// DATE_INTERVAL_QUARTER
return $tm[0] . "/Q" . $tm[1];
case 3:
// DATE_INTERVAL_MONTH
return @$locale_info["LOCALE_SABBREVMONTHNAME" . $tm[1]] . " " . $tm[0];
case 4:
// DATE_INTERVAL_WEEK
$dates = $this->getDatesByWeek($tm[1] + 1, $tm[0]);
return format_shortdate(db2time($dates[0])) . ' - ' . format_shortdate(db2time($dates[1]));
case 5:
// DATE_INTERVAL_DAY
return format_shortdate($tm);
case 6:
// DATE_INTERVAL_HOUR
$tm[4] = 0;
$tm[5] = 0;
return str_format_datetime($tm);
case 7:
// DATE_INTERVAL_MINUTE
$tm[5] = 0;
return str_format_datetime($tm);
default:
return str_format_datetime($tm);
}
}
return "";
}
function secondsPassedFrom($datetime)
{
$arrDateTime = db2time($datetime);
return time() - mktime($arrDateTime[3], $arrDateTime[4], $arrDateTime[5], $arrDateTime[1], $arrDateTime[2], $arrDateTime[0]);
}
/**
* Get report text
* @param Number totalRecords
* @param Number addedRecords
* @param Number updatedRecords
* @param Boolean isNotLogFile
* @rturn String
*/
protected function getBasicReportText($totalRecords, $addedRecords, $updatedRecords, $isNotLogFile = true, $lineBreak = "<br>", $errorMessages = array(), $unprocessedData = array())
{
$importedReords = $addedRecords + $updatedRecords;
$notImportedRecords = $totalRecords - $importedReords;
$boldBegin = "";
$boldEnd = "";
$reportText = "";
if ($isNotLogFile) {
$boldBegin = "<b>";
$boldEnd = "</b>";
} else {
$reportText .= "Import into" . " " . $this->strOriginalTableName . $lineBreak . str_format_datetime(db2time(now())) . $lineBreak . $lineBreak;
}
$reportText .= mysprintf("%s out of %s records processed successfully.", array($boldBegin . $importedReords . $boldEnd, $boldBegin . $totalRecords . $boldEnd)) . $lineBreak . mysprintf("%s records added.", array($boldBegin . $addedRecords . $boldEnd)) . $lineBreak . mysprintf("%s records updated.", array($boldBegin . $updatedRecords . $boldEnd)) . $lineBreak;
if ($notImportedRecords) {
$reportText .= mysprintf("%s records processed with errors", array($boldBegin . $notImportedRecords . $boldEnd));
}
if ($notImportedRecords && count($errorMessages)) {
$reportText .= ":";
for ($i = 0; $i < count($errorMessages); $i++) {
if ($isNotLogFile) {
$reportText .= $lineBreak . $errorMessages[$i];
} else {
$reportText .= $lineBreak . $lineBreak . $errorMessages[$i] . $lineBreak . $unprocessedData[$i + 1];
}
}
}
return $reportText;
}
