function createdetailevents($parenteventid)
{
$sql = "select * from user_event where id=" . $parenteventid;
mysql_query($sql);
$handle = mysql_query($sql) or die(mysql_error());
/*$this->db->where('id', $parenteventid);
$q = $this->db->get('user_event');
if ($q->num_rows() > 0):
$row = $q->row();
endif;*/
$arr;
while ($row = mysql_fetch_object($handle)) {
$arr["_ID"] = $row->id;
$arr["_EventName"] = $row->event_title;
$arr["_Location"] = $row->event_loc;
$arr["_Description"] = $row->event_desc;
$arr["_StartDate"] = $row->start_date;
$arr["_EndDate"] = $row->to_date;
$arr["_Daily"] = $row->daily;
$arr["_Weekly"] = $row->weekly;
$arr["_Monthly"] = $row->monthly;
$arr["_Yearly"] = $row->yearly;
$arr["_Daily_interval"] = $row->daily_interval;
$arr["_Weekly_interval"] = $row->weekly_interval;
$arr["_Weekly_interval_day"] = $row->weekly_interval_day;
$arr["_Monthly_interval"] = $row->monthly_interval;
$arr["_Monthly_interval_date"] = $row->monthly_interval_date;
$arr["_Yearly_interval_date"] = $row->yearly_interval_date;
$arr["_Yearly_interval_month"] = $row->yearly_interval_month;
$arr["_Occurence"] = $row->event_occ;
$arr["repeat_start_date"] = $row->repeat_start_date;
if (trim($arr["_Daily"]) == "0" && trim($arr["_Monthly"]) == "0" && trim($arr["_Weekly"]) == "0" && trim($arr["_Yearly"]) == "0") {
$startdate = $arr['_StartDate'];
$start_exp = explode("/", $startdate);
$arr["_StartDate"] = $start_exp[2] . "-" . $start_exp[1] . "-" . $start_exp[0];
$enddate = $arr['_EndDate'];
$end_exp = explode("/", $enddate);
$arr["_EndDate"] = $end_exp[2] . "-" . $end_exp[1] . "-" . $end_exp[0];
$strFieldsName = "parenteventid,startDate,endDate";
$strInsertValue = "'" . $arr["_ID"] . "','" . $arr["_StartDate"] . "','" . $arr["_EndDate"] . "'";
$this->insertdetailevent($strFieldsName, $strInsertValue);
} else {
$startdate = $arr['_StartDate'];
$start_exp = explode("/", $startdate);
$startdate = $start_exp[2] . "-" . $start_exp[1] . "-" . $start_exp[0];
$enddate = $arr['_EndDate'];
$end_exp = explode("/", $enddate);
$enddate = $end_exp[2] . "-" . $end_exp[1] . "-" . $end_exp[0];
$repeat_start_date = $arr['repeat_start_date'];
$repeat_exp = explode("/", $repeat_start_date);
$repeat_start_date = $repeat_exp[2] . "-" . $repeat_exp[1] . "-" . $repeat_exp[0];
$date_difference = strtotime($repeat_start_date) - strtotime($startdate);
if (trim($arr["_Daily"]) == "1") {
// Start Code
$strFieldsName = "parenteventid,startDate,endDate";
$strInsertValue = "";
$arr["_Occurence"] = $date_difference / (60 * 60 * 24);
if (trim($arr["_Daily_interval"]) != "") {
if (intval($arr["_Daily_interval"]) <= 0) {
$arr["_Daily_interval"] = 1;
}
$icounter = -1;
$blnloop = 1;
while ($blnloop == 1) {
$icounter++;
$strInsertValue = "'" . $arr["_ID"] . "'";
$interval = intval(trim($arr["_Daily_interval"])) * $icounter;
$startresult = mysql_fetch_array(mysql_query("SELECT ADDDATE('" . $startdate . "', INTERVAL " . $interval . " DAY) as startdate"));
$endresult = mysql_fetch_array(mysql_query("SELECT ADDDATE('" . $enddate . "', INTERVAL " . $interval . " DAY) as enddate"));
$strNewDate = $startresult['startdate'];
$strEndDate = $endresult['enddate'];
//$strNewDate=adddays($arr["_StartDate"],(intval(trim($arr["_Daily_interval"]))*$icounter),"day");
//$strEndDate=adddays($arr["_EndDate"],(intval(trim($arr["_Daily_interval"]))*$icounter),"day");
$strInsertValue .= ",'" . $strNewDate . "','" . $strEndDate . "'";
$this->insertdetailevent($strFieldsName, $strInsertValue);
if ($icounter + 1 >= intval($arr["_Occurence"])) {
$blnloop = 0;
}
}
}
// End Code
}
// weekly
if (trim($arr["_Weekly"]) == "1") {
$arr["_Occurence"] = $date_difference / (60 * 60 * 24 * 7);
// Start Code
$strFieldsName = "parenteventid,startDate,endDate";
$strInsertValue = "";
if (trim($arr["_Weekly_interval"]) == "") {
$startdate = $arr['_StartDate'];
$start_exp = explode("/", $startdate);
$arr["_StartDate"] = $start_exp[2] . "-" . $start_exp[1] . "-" . $start_exp[0];
$arr["_Weekly_interval"] = getCurrentWeeklyDay($arr["_StartDate"]);
}
if (trim($arr["_Weekly_interval"]) != "") {
$icounter = -1;
$blnloop = 1;
$strNewDate = "";
$strNewEndDate = "";
$startdate = $arr['_StartDate'];
$start_exp = explode("/", $startdate);
$arr["_StartDate"] = $start_exp[2] . "-" . $start_exp[1] . "-" . $start_exp[0];
$enddate = $arr['_EndDate'];
$end_exp = explode("/", $enddate);
$arr["_EndDate"] = $end_exp[2] . "-" . $end_exp[1] . "-" . $end_exp[0];
while ($blnloop == 1) {
$strInsertValue = "'" . $arr["_ID"] . "'";
$icounter++;
if ($icounter == 0) {
$strNewDate = getWeeklyDay($arr["_StartDate"], trim($arr["_Weekly_interval"]));
} else {
$intWeekInterval = 1;
if (trim($arr["_Weekly_interval_day"]) != "") {
$intWeekInterval = intval(trim($arr["_Weekly_interval_day"]));
}
if ($intWeekInterval <= 0) {
$intWeekInterval = 1;
}
$strNewDate = adddays($strNewDate, $intWeekInterval, "week");
}
$arrTempCompare = getDateDifference($arr["_StartDate"], $strNewDate);
$strNewEndDate = adddays($arr["_EndDate"], intval($arrTempCompare["days"]), "day");
$exp_new = explode("-", $strNewDate);
if (strlen($exp_new[2]) == 1) {
$exp_new[2] = "0" . $exp_new[2];
}
$exp_newend = explode("-", $strNewEndDate);
if (strlen($exp_newend[2]) == 1) {
$exp_newend[2] = "0" . $exp_newend[2];
}
$strNewDate = $exp_new[0] . "-" . $exp_new[1] . "-" . $exp_new[2];
$strNewEndDate = $exp_newend[0] . "-" . $exp_newend[1] . "-" . $exp_newend[2];
$strInsertValue .= ",'" . $strNewDate . "','" . $strNewEndDate . "'";
$this->insertdetailevent($strFieldsName, $strInsertValue);
if ($icounter + 1 >= intval($arr["_Occurence"])) {
$blnloop = 0;
}
}
}
// End Code
}
// Monthly
if (trim($arr["_Monthly"]) == "1") {
// Start Code
$arr["_Occurence"] = $date_difference / (60 * 60 * 24 * 30);
$strFieldsName = "parenteventid,startDate,endDate";
$strInsertValue = "";
$startdate = $arr['_StartDate'];
$start_exp = explode("/", $startdate);
$arr["_StartDate"] = $start_exp[2] . "-" . $start_exp[1] . "-" . $start_exp[0];
$enddate = $arr['_EndDate'];
$end_exp = explode("/", $enddate);
$arr["_EndDate"] = $end_exp[2] . "-" . $end_exp[1] . "-" . $end_exp[0];
if (trim($arr["_Monthly_interval"]) == "") {
$arr["_Monthly_interval"] = getCurrentMonthDay($arr["_StartDate"]);
}
if (trim($arr["_Monthly_interval"]) != "") {
$icounter = -1;
$blnloop = 1;
$strNewDate = "";
while ($blnloop == 1) {
$strInsertValue = "'" . $arr["_ID"] . "'";
$icounter++;
$intMonthInterval = 1;
if (trim($arr["_Monthly_interval_date"]) != "") {
$intMonthInterval = intval(trim($arr["_Monthly_interval_date"]));
}
if ($intMonthInterval <= 0) {
$intMonthInterval = 1;
}
if ($icounter == 0) {
$strNewDate = getMonthDay($arr["_StartDate"], trim($arr["_Monthly_interval"]), $intMonthInterval, 1);
} else {
$strNewDate = getMonthDay($strNewDate, trim($arr["_Monthly_interval"]), $intMonthInterval);
}
if ($strNewDate != "") {
$arrTempCompare = getDateDifference($arr["_StartDate"], $strNewDate);
$strNewEndDate = adddays($arr["_EndDate"], intval($arrTempCompare["days"]), "day");
$exp_new = explode("-", $strNewDate);
if (strlen($exp_new[2]) == 1) {
$exp_new[2] = "0" . $exp_new[2];
}
$exp_newend = explode("-", $strNewEndDate);
if (strlen($exp_newend[2]) == 1) {
$exp_newend[2] = "0" . $exp_newend[2];
}
$strNewDate = $exp_new[0] . "-" . $exp_new[1] . "-" . $exp_new[2];
$strNewEndDate = $exp_newend[0] . "-" . $exp_newend[1] . "-" . $exp_newend[2];
$strInsertValue .= ",'" . $strNewDate . "','" . $strNewEndDate . "'";
$this->insertdetailevent($strFieldsName, $strInsertValue);
}
if ($icounter + 1 >= intval($arr["_Occurence"])) {
$blnloop = 0;
}
}
}
// End Code
}
// Yearly
if (trim($arr["_Yearly"]) == "1") {
// Start Code
$arr["_Occurence"] = $date_difference / (60 * 60 * 24 * 365);
$startdate = $arr['_StartDate'];
$start_exp = explode("/", $startdate);
$arr["_StartDate"] = $start_exp[2] . "-" . $start_exp[1] . "-" . $start_exp[0];
$enddate = $arr['_EndDate'];
$end_exp = explode("/", $enddate);
$arr["_EndDate"] = $end_exp[2] . "-" . $end_exp[1] . "-" . $end_exp[0];
// Start Code
$strFieldsName = "parenteventid,startDate,endDate";
$strInsertValue = "";
if (trim($arr["_Yearly_interval_date"]) == "" && intval(trim($arr["_Yearly_interval_date"])) <= 0) {
$arr["_Yearly_interval_date"] = getCurrentMonth($arr["_StartDate"]);
}
if (trim($arr["_Yearly_interval_month"]) == "" && intval(trim($arr["_Yearly_interval_month"])) <= 0) {
$arr["_Yearly_interval_month"] = getCurrentMonthDay($arr["_StartDate"]);
}
if (trim($arr["_Yearly_interval_month"]) != "") {
$icounter = -1;
$blnloop = 1;
$strNewDate = "";
while ($blnloop == 1) {
$strInsertValue = "'" . $arr["_ID"] . "'";
$icounter++;
if ($icounter == 0) {
$strNewDate = getYearDay($arr["_StartDate"], trim($arr["_Yearly_interval_date"]), trim($arr["_Yearly_interval_month"]), 1);
} else {
$strNewDate = getYearDay($strNewDate, trim($arr["_Yearly_interval_date"]), trim($arr["_Yearly_interval_month"]));
}
if ($strNewDate != "") {
$arrTempCompare = getDateDifference($arr["_StartDate"], $strNewDate);
$strNewEndDate = adddays($arr["_EndDate"], intval($arrTempCompare["days"]), "day");
$exp_new = explode("-", $strNewDate);
if (strlen($exp_new[2]) == 1) {
$exp_new[2] = "0" . $exp_new[2];
}
$exp_newend = explode("-", $strNewEndDate);
if (strlen($exp_newend[2]) == 1) {
$exp_newend[2] = "0" . $exp_newend[2];
}
$strNewDate = $exp_new[0] . "-" . $exp_new[1] . "-" . $exp_new[2];
$strNewEndDate = $exp_newend[0] . "-" . $exp_newend[1] . "-" . $exp_newend[2];
$strInsertValue .= ",'" . $strNewDate . "','" . $strNewEndDate . "'";
$this->insertdetailevent($strFieldsName, $strInsertValue);
}
if ($icounter + 1 >= intval($arr["_Occurence"])) {
$blnloop = 0;
}
}
}
// End Code
}
}
}
}
/**
* 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 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);
}
}
}
}
}
}
}
/**
* @intellisense
* @param {array} time - array(year, month, day, hour, minute, second)
* @param {int} firstdayofweek - the first day of the week. 0 - monday, 6 - sunday, -1 - use system settings
*/
function getweekstart($time, $firstdayofweek = -1)
{
global $locale_info;
if ($firstdayofweek < 0) {
$firstdayofweek = $locale_info["LOCALE_IFIRSTDAYOFWEEK"];
}
$wday = getdayofweek($time);
if ($wday >= $firstdayofweek + 1) {
$diff = $wday - $firstdayofweek - 1;
} else {
$diff = $wday + 7 - $firstdayofweek - 1;
}
return adddays($time, -$diff);
}
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();
}
function getweekstart($time)
{
global $locale_info;
$wday = getdayofweek($time);
if ($wday >= $locale_info["LOCALE_IFIRSTDAYOFWEEK"] + 1) {
$diff = $wday - $locale_info["LOCALE_IFIRSTDAYOFWEEK"] - 1;
} else {
$diff = $wday + 7 - $locale_info["LOCALE_IFIRSTDAYOFWEEK"] - 1;
}
return adddays($time, -$diff);
}
function StrWhereAdv($strField, $SearchFor, $strSearchOption, $SearchFor2, $etype, $isSuggest)
{
global $strTableName;
$pSet = new ProjectSettings($strTableName, PAGE_SEARCH);
$cipherer = new RunnerCipherer($strTableName);
$type = $pSet->getFieldType($strField);
$isOracle = false;
$ismssql = false;
$isdb2 = false;
$btexttype = IsTextType($type);
$isMysql = false;
if (IsBinaryType($type)) {
return "";
}
if ($strSearchOption == 'Empty') {
if (IsCharType($type) && (!$ismssql || !$btexttype) && !$isOracle) {
return "(" . GetFullFieldNameForInsert($pSet, $strField) . " is null or " . GetFullFieldNameForInsert($pSet, $strField) . "='')";
} elseif ($ismssql && $btexttype) {
return "(" . GetFullFieldNameForInsert($pSet, $strField) . " is null or " . GetFullFieldNameForInsert($pSet, $strField) . " LIKE '')";
} else {
return GetFullFieldNameForInsert($pSet, $strField) . " is null";
}
}
$like = "ilike";
if ($pSet->getEditFormat($strField) == EDIT_FORMAT_LOOKUP_WIZARD) {
if ($pSet->multiSelect($strField)) {
$SearchFor = splitvalues($SearchFor);
} else {
$SearchFor = array($SearchFor);
}
$ret = "";
foreach ($SearchFor as $value) {
if (!($value == "null" || $value == "Null" || $value == "")) {
if (strlen($ret)) {
$ret .= " or ";
}
if ($strSearchOption == "Equals") {
$value = make_db_value($strField, $value);
if (!($value == "null" || $value == "Null")) {
$ret .= GetFullFieldName($strField, "", false) . '=' . $value;
}
} elseif ($isSuggest) {
$ret .= " " . GetFullFieldName($strField, "", false) . " " . $like . " " . db_prepare_string('%' . $value . '%');
} else {
if (strpos($value, ",") !== false || strpos($value, '"') !== false) {
$value = '"' . str_replace('"', '""', $value) . '"';
}
if ($isMysql) {
$value = str_replace('\\\\', '\\\\\\\\', $value);
}
//for search by multiply Lookup wizard field
$ret .= GetFullFieldName($strField, "", false) . " = " . db_prepare_string($value);
$ret .= " or " . GetFullFieldName($strField, "", false) . " " . $like . " " . db_prepare_string("%," . $value . ",%");
$ret .= " or " . GetFullFieldName($strField, "", false) . " " . $like . " " . db_prepare_string("%," . $value);
$ret .= " or " . GetFullFieldName($strField, "", false) . " " . $like . " " . db_prepare_string($value . ",%");
}
}
}
if (strlen($ret)) {
$ret = "(" . $ret . ")";
}
return $ret;
}
if ($pSet->GetEditFormat($strField) == EDIT_FORMAT_CHECKBOX) {
if ($SearchFor == "none") {
return "";
}
if (NeedQuotes($type)) {
$isOracle = false;
if ($SearchFor == "on") {
$whereStr = "(" . GetFullFieldName($strField) . "<>'0' ";
if (!$isOracle) {
$whereStr .= " and " . GetFullFieldName($strField) . "<>'' ";
}
$whereStr .= " and " . GetFullFieldName($strField) . " is not null)";
return $whereStr;
} elseif ($SearchFor == "off") {
$whereStr = "(" . GetFullFieldName($strField) . "='0' ";
if (!$isOracle) {
$whereStr .= " or " . GetFullFieldName($strField) . "='' ";
}
$whereStr .= " or " . GetFullFieldName($strField) . " is null)";
}
} else {
if ($SearchFor == "on") {
return "(" . GetFullFieldName($strField) . "<>0 and " . GetFullFieldName($strField) . " is not null)";
} elseif ($SearchFor == "off") {
return "(" . GetFullFieldName($strField) . "=0 or " . GetFullFieldName($strField) . " is null)";
}
}
}
$value1 = $cipherer->MakeDBValue($strField, $SearchFor, $etype, "", true);
$value2 = false;
$cleanvalue2 = false;
if ($strSearchOption == "Between") {
$cleanvalue2 = prepare_for_db($strField, $SearchFor2, $etype);
$value2 = make_db_value($strField, $SearchFor2, $etype);
}
if ($strSearchOption != "Contains" && $strSearchOption != "Starts with" && ($value1 === "null" || $value2 === "null") && !$cipherer->isFieldPHPEncrypted($strField)) {
return "";
}
if (IsCharType($type) && !$btexttype) {
if (!$cipherer->isFieldPHPEncrypted($strField)) {
$value1 = $pSet->isEnableUpper($value1);
$value2 = $pSet->isEnableUpper($value2);
$gstrField = $pSet->isEnableUpper(GetFullFieldName($strField, "", false));
} else {
$gstrField = GetFullFieldName($strField, "", false);
}
} elseif ($strSearchOption == "Contains" || $strSearchOption == "Starts with") {
$gstrField = db_field2char(GetFullFieldName($strField, "", false), $type);
} elseif ($pSet->getViewFormat($strField) == FORMAT_TIME) {
$gstrField = db_field2time(GetFullFieldName($strField, "", false), $type);
} else {
$gstrField = GetFullFieldName($strField, "", false);
}
$ret = "";
if ($strSearchOption == "Contains") {
if ($isMysql) {
$SearchFor = str_replace('\\\\', '\\\\\\\\', $SearchFor);
}
if ($cipherer->isFieldPHPEncrypted($strField)) {
return $gstrField . "=" . $cipherer->MakeDBValue($strField, $SearchFor);
}
if (IsCharType($type) && !$btexttype) {
return $gstrField . " " . $like . " " . $pSet->isEnableUpper(db_prepare_string("%" . $SearchFor . "%"));
} else {
return $gstrField . " " . $like . " " . db_prepare_string("%" . $SearchFor . "%");
}
} else {
if ($strSearchOption == "Equals") {
return $gstrField . "=" . $value1;
} else {
if ($strSearchOption == "Starts with") {
if ($isMysql) {
$SearchFor = str_replace('\\\\', '\\\\\\\\', $SearchFor);
}
if (IsCharType($type) && !$btexttype) {
return $gstrField . " " . $like . " " . $pSet->isEnableUpper(db_prepare_string($SearchFor . "%"));
} else {
return $gstrField . " " . $like . " " . db_prepare_string($SearchFor . "%");
}
} else {
if ($strSearchOption == "More than") {
return $gstrField . ">" . $value1;
} else {
if ($strSearchOption == "Less than") {
return $gstrField . "<" . $value1;
} else {
if ($strSearchOption == "Equal or more than") {
return $gstrField . ">=" . $value1;
} else {
if ($strSearchOption == "Equal or less than") {
return $gstrField . "<=" . $value1;
} else {
if ($strSearchOption == "Between") {
$ret = $gstrField . ">=" . $value1 . " and ";
if (IsDateFieldType($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($strField, $value2, $strTableName);
$ret .= $gstrField . "<" . $value2;
} else {
$ret .= $gstrField . "<=" . $value2;
}
} else {
$ret .= $gstrField . "<=" . $value2;
}
return $ret;
}
}
}
}
}
}
}
}
return "";
}
function cached_formatweekstart($strtime)
{
global $cache_formatweekstart;
if (!isset($cache_formatweekstart[$strtime])) {
$start = cached_getweekstart($strtime);
$end = adddays($start, 6);
$res = format_shortdate($start) . " - " . format_shortdate($end);
$cache_formatweekstart[$strtime] = $res;
return $res;
} else {
return $cache_formatweekstart[$strtime];
}
}
/**
* 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;
}
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;
}
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);
}
