Exemplo n.º 1
0
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) . '&nbsp;&nbsp;<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;
    }
}
Exemplo n.º 2
0
 /**
  * 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") . " ";
             }
         }
     }
 }
Exemplo n.º 3
0
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 "";
}
Exemplo n.º 4
0
 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;
     }
 }
Exemplo n.º 5
0
 /**
  * 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, "");
 }
Exemplo n.º 6
0
 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 "";
 }