function InsertRecord($arr, $recInd)
{
global $goodlines, $conn, $error_message, $keys_present, $keys, $strOriginalTableName, $strTableName, $eventObj, $locale_info, $auditObj, $cipherer, $gSettings, $pageObject;
$ret = 1;
$rawvalues = array();
foreach ($arr as $key => $val) {
$rawvalues[$key] = $val;
$type = $gSettings->getFieldType($key);
if (!NeedQuotes($type)) {
$value = (string) $val;
$value = str_replace(",", ".", $value);
if (strlen($value) > 0) {
$value = str_replace($locale_info["LOCALE_SCURRENCY"], "", $value);
$arr[$key] = 0 + $value;
} else {
$arr[$key] = NULL;
}
}
}
$retval = true;
if ($eventObj->exists('BeforeInsert')) {
$retval = $eventObj->BeforeInsert($rawvalues, $arr, $pageObject);
}
if ($retval) {
$fields = array_keys($arr);
foreach ($fields as $key => $val) {
$fields_list[$key] = GetFullFieldNameForInsert($gSettings, $val);
}
$values_list = "";
foreach ($arr as $key => $val) {
if (!is_null($arr[$key])) {
$values_list .= $cipherer->AddDBQuotes($key, $val) . ", ";
} else {
$values_list .= "NULL, ";
}
}
if (strlen($values_list) > 0) {
$values_list = substr($values_list, 0, strlen($values_list) - 2);
}
$sql = "insert into " . AddTableWrappers($strOriginalTableName) . " (" . implode(",", $fields_list) . ") values (" . $values_list . ")";
if (db_exec_import($sql, $conn)) {
$goodlines++;
if ($auditObj) {
$aKeys = GetKeysArray($arr, true);
$auditObj->LogAdd($strTableName, $arr, $aKeys);
}
} else {
$temp_error_message = "<b>Error:</b> in the line: " . implode(",", $arr) . ' <a linkType="debugOpener" recId="' . $recInd . '" href="" onclick="importMore(' . $recInd . ');">More info</a><br>';
$temp_error_message .= '<div id="importDebugInfoTable' . $recInd . '" cellpadding="3" cellspacing="1" align="center" style="display: none;"><p class="error">SQL query: ' . $sql . '; </p><p class="error">DB error: ' . db_error($conn) . ';</p></div>';
$temp_error_message .= "<br><br>";
// we'll try to update the record
if ($keys_present) {
$sql = "update " . AddTableWrappers($strOriginalTableName) . " set ";
$sqlset = "";
$where = " where ";
foreach ($fields as $k => $val) {
if (!in_array(AddFieldWrappers($fields[$k]), $keys)) {
if (!is_null($arr[$val])) {
$sqlset .= $fields_list[$k] . "=" . $cipherer->AddDBQuotes($val, $arr[$val]) . ", ";
} else {
$sqlset .= $fields_list[$k] . "=NULL, ";
}
} else {
$where .= $fields_list[$k] . "=" . $cipherer->AddDBQuotes($val, $arr[$val]) . " and ";
}
}
if (strlen($sqlset) > 0) {
$sql .= substr($sqlset, 0, strlen($sqlset) - 2);
}
$where = substr($where, 0, strlen($where) - 5);
$sql .= " " . $where;
$rstmp = db_query("select * from " . AddTableWrappers($strOriginalTableName) . " " . $where, $conn);
$data = db_fetch_array($rstmp);
if ($data) {
if ($auditObj) {
foreach ($data as $key => $val) {
$auditOldValues[$key] = $val;
}
}
if (db_exec_import($sql, $conn)) {
// update successfull
$goodlines++;
if ($auditObj) {
$aKeys = GetKeysArray($arr);
$auditObj->LogEdit($strTableName, $arr, $auditOldValues, $aKeys);
}
} else {
// update not successfull
$error_message .= $temp_error_message;
$ret = 0;
}
} else {
$error_message .= $temp_error_message;
$ret = 0;
}
} else {
$error_message .= $temp_error_message;
}
}
return $ret;
}
}
/**
* Adds sub query for counting details recs number
*
*/
function addMasterDetailSubQuery()
{
// add count of child records to SQL
if ($this->subQueriesSupp && $this->subQueriesSupAccess && !$this->theSameFieldsType) {
for ($i = 0; $i < count($this->allDetailsTablesArr); $i++) {
if ($this->allDetailsTablesArr[$i]['dispChildCount'] || $this->allDetailsTablesArr[$i]['hideChild']) {
$origTName = $this->allDetailsTablesArr[$i]['dOriginalTable'];
$dataSourceTName = $this->allDetailsTablesArr[$i]['dDataSourceTable'];
$shortTName = $this->allDetailsTablesArr[$i]['dShortTable'];
$detailsSettings = $this->pSet->getTable($dataSourceTName);
$detailsQuery = $detailsSettings->getSQLQuery();
$detailsSqlWhere = $detailsQuery->WhereToSql();
$masterWhere = "";
foreach ($this->masterKeysByD[$i] as $idx => $val) {
if ($masterWhere) {
$masterWhere .= " and ";
}
$masterWhere .= $this->cipherer->GetFieldName(AddTableWrappers("subQuery_cnt") . "." . AddFieldWrappers($this->detailKeysByD[$i][$idx]), $this->masterKeysByD[$i][$idx]) . "=" . $this->cipherer->GetFieldName(AddTableWrappers($this->origTName) . "." . AddFieldWrappers($this->masterKeysByD[$i][$idx]), $this->masterKeysByD[$i][$idx]);
}
// add a key field to the select list
$subQ = "";
foreach ($this->detailKeysByD[$i] as $k) {
if (strlen($subQ)) {
$subQ .= ",";
}
$subQ .= GetFullFieldNameForInsert($this->pSet, $k);
}
$subQ = "SELECT " . $subQ . " " . $detailsQuery->FromToSql();
// add security where clause for sub query
$securityClause = SecuritySQL("Search", $dataSourceTName);
if (strlen($securityClause)) {
$subQ .= " WHERE " . whereAdd($detailsSqlWhere, $securityClause);
} elseif (strlen($detailsSqlWhere)) {
$subQ .= " WHERE " . whereAdd("", $detailsSqlWhere);
}
// add detail table query tail
$subQ .= " " . $detailsQuery->TailToSql();
$countsql = "SELECT count(*) FROM (" . $subQ . ") " . AddTableWrappers("subQuery_cnt") . " WHERE " . $masterWhere;
$this->gsqlHead .= ",(" . $countsql . ") as " . AddFieldWrappers($dataSourceTName . "_cnt") . " ";
}
}
}
}
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 getStringSql($forGroupedField = false)
{
$fname = $this->_oldAlgorithm && !$forGroupedField ? GetFullFieldNameForInsert($this->pSet, $this->_name) : cached_ffn($this->_name, $forGroupedField);
if ($this->_interval > 0) {
return 'substr(' . $fname . ', 1, ' . $this->_interval . ')';
} else {
return $fname;
}
}
/**
* Use for count details recs number, if subQueryes not supported, or keys have different types
*
* @param integer $i
* @param array $detailid
* @intellisense
*/
function countDetailsRecsNoSubQ($dInd, &$detailid)
{
global $tables_data;
global $masterTablesData;
global $detailsTablesData;
global $allDetailsTablesArr;
$dDataSourceTable = $this->allDetailsTablesArr[$dInd]['dDataSourceTable'];
$masterPSet = $this->pSet->getTable($dDataSourceTable);
$detailsQuery = $masterPSet->getSQLQuery();
$dSqlWhere = $detailsQuery->WhereToSql();
$detailKeys = $masterPSet->getDetailKeysByMasterTable($this->tName);
$securityClause = SecuritySQL("Search", $dDataSourceTable);
// add where
if (strlen($securityClause)) {
$dSqlWhere = whereAdd($dSqlWhere, $securityClause);
}
$masterwhere = "";
foreach ($this->masterKeysByD[$dInd] as $idx => $val) {
if ($masterwhere) {
$masterwhere .= " and ";
}
$mastervalue = $this->cipherer->MakeDBValue($detailKeys[$idx], $detailid[$idx], "", $dDataSourceTable, true);
if ($mastervalue == "null") {
$masterwhere .= GetFullFieldNameForInsert($masterPSet, $detailKeys[$idx]) . " is NULL ";
} else {
$masterwhere .= GetFullFieldName($detailKeys[$idx], $dDataSourceTable, false) . "=" . $mastervalue;
}
}
return SQLQuery::gSQLRowCount_int($detailsQuery->HeadToSql(), $detailsQuery->FromToSql(), $dSqlWhere, $detailsQuery->GroupByToSql(), $detailsQuery->Having()->toSql($detailsQuery), $masterwhere, "");
}
function baseSQLWhere($strSearchOption)
{
$this->btexttype = IsTextType($this->type);
if (IsBinaryType($this->type)) {
return false;
}
if ($strSearchOption == 'Empty') {
if (IsCharType($this->type) && (!$this->ismssql || !$this->btexttype) && !$this->isOracle) {
return "(" . GetFullFieldNameForInsert($this->pageObject->pSetEdit, $this->field) . " is null or " . GetFullFieldNameForInsert($this->pageObject->pSetEdit, $this->field) . "='')";
} elseif ($this->ismssql && $this->btexttype) {
return "(" . GetFullFieldNameForInsert($this->pageObject->pSetEdit, $this->field) . " is null or " . GetFullFieldNameForInsert($this->pageObject->pSetEdit, $this->field) . " LIKE '')";
} else {
return GetFullFieldNameForInsert($this->pageObject->pSetEdit, $this->field) . " is null";
}
}
return "";
}