/**
* Generate a set of Insert statements based on the bean given
*
* @deprecated
*
* @param SugarBean $bean the bean from which table we will generate insert stmts
* @param string $select_query the query which will give us the set of objects we want to place into our insert statement
* @param int $start the first row to query
* @param int $count the number of rows to query
* @param string $table the table to query from
* @param bool $is_related_query
* @return string SQL insert statement
*/
public function generateInsertSQL(SugarBean $bean, $select_query, $start, $count = -1, $table, $is_related_query = false)
{
$this->log->info('call to DBManager::generateInsertSQL() is deprecated');
global $sugar_config;
$rows_found = 0;
$count_query = $bean->create_list_count_query($select_query);
if (!empty($count_query)) {
// We have a count query. Run it and get the results.
$result = $this->query($count_query, true, "Error running count query for {$this->object_name} List: ");
$assoc = $this->fetchByAssoc($result);
if (!empty($assoc['c'])) {
$rows_found = $assoc['c'];
}
}
if ($count == -1) {
$count = $sugar_config['list_max_entries_per_page'];
}
$next_offset = $start + $count;
$result = $this->limitQuery($select_query, $start, $count);
// get basic insert
$sql = "INSERT INTO " . $table;
$custom_sql = "INSERT INTO " . $table . "_cstm";
// get field definitions
$fields = $bean->getFieldDefinitions();
$custom_fields = array();
if ($bean->hasCustomFields()) {
foreach ($fields as $fieldDef) {
if ($fieldDef['source'] == 'custom_fields') {
$custom_fields[$fieldDef['name']] = $fieldDef['name'];
}
}
if (!empty($custom_fields)) {
$custom_fields['id_c'] = 'id_c';
$id_field = array('name' => 'id_c', 'custom_type' => 'id');
$fields[] = $id_field;
}
}
// get column names and values
$row_array = array();
$columns = array();
$cstm_row_array = array();
$cstm_columns = array();
$built_columns = false;
while (($row = $this->fetchByAssoc($result)) != null) {
$values = array();
$cstm_values = array();
if (!$is_related_query) {
foreach ($fields as $fieldDef) {
if (isset($fieldDef['source']) && $fieldDef['source'] != 'db' && $fieldDef['source'] != 'custom_fields') {
continue;
}
$val = $row[$fieldDef['name']];
//handle auto increment values here only need to do this on insert not create
if ($fieldDef['name'] == 'deleted') {
$values['deleted'] = $val;
if (!$built_columns) {
$columns[] = 'deleted';
}
} else {
$type = $fieldDef['type'];
if (!empty($fieldDef['custom_type'])) {
$type = $fieldDef['custom_type'];
}
// need to do some thing about types of values
if ($this->dbType == 'mysql' && $val == '' && ($type == 'datetime' || $type == 'date' || $type == 'int' || $type == 'currency' || $type == 'decimal')) {
if (!empty($custom_fields[$fieldDef['name']])) {
$cstm_values[$fieldDef['name']] = 'null';
} else {
$values[$fieldDef['name']] = 'null';
}
} else {
if (isset($type) && $type == 'int') {
if (!empty($custom_fields[$fieldDef['name']])) {
$cstm_values[$fieldDef['name']] = $GLOBALS['db']->quote(from_html($val));
} else {
$values[$fieldDef['name']] = $GLOBALS['db']->quote(from_html($val));
}
} else {
if (!empty($custom_fields[$fieldDef['name']])) {
$cstm_values[$fieldDef['name']] = "'" . $GLOBALS['db']->quote(from_html($val)) . "'";
} else {
$values[$fieldDef['name']] = "'" . $GLOBALS['db']->quote(from_html($val)) . "'";
}
}
}
if (!$built_columns) {
if (!empty($custom_fields[$fieldDef['name']])) {
$cstm_columns[] = $fieldDef['name'];
} else {
$columns[] = $fieldDef['name'];
}
}
}
}
} else {
foreach ($row as $key => $val) {
if ($key != 'orc_row') {
$values[$key] = "'{$val}'";
if (!$built_columns) {
$columns[] = $key;
}
}
}
}
$built_columns = true;
if (!empty($values)) {
$row_array[] = $values;
}
if (!empty($cstm_values) && !empty($cstm_values['id_c']) && strlen($cstm_values['id_c']) > 7) {
$cstm_row_array[] = $cstm_values;
}
}
//if (sizeof ($values) == 0) return ""; // no columns set
// get the entire sql
$sql .= "(" . implode(",", $columns) . ") ";
$sql .= "VALUES";
for ($i = 0; $i < count($row_array); $i++) {
$sql .= " (" . implode(",", $row_array[$i]) . ")";
if ($i < count($row_array) - 1) {
$sql .= ", ";
}
}
//custom
// get the entire sql
$custom_sql .= "(" . implode(",", $cstm_columns) . ") ";
$custom_sql .= "VALUES";
for ($i = 0; $i < count($cstm_row_array); $i++) {
$custom_sql .= " (" . implode(",", $cstm_row_array[$i]) . ")";
if ($i < count($cstm_row_array) - 1) {
$custom_sql .= ", ";
}
}
return array('data' => $sql, 'cstm_sql' => $custom_sql, 'total_count' => $rows_found, 'next_offset' => $next_offset);
}
function getTotalCount($main_query)
{
if (!empty($this->count_query)) {
$count_query = $this->count_query;
} else {
$count_query = SugarBean::create_list_count_query($main_query);
}
$result = $this->db->query($count_query);
if ($row = $this->db->fetchByAssoc($result)) {
return $row['c'];
}
return 0;
}
/**
* create_list_count_query
* Overrode this method from SugarBean to handle the distinct parameter used to filter out
* duplicate entries for some of the subpanel listivews. Without the distinct filter, the
* list count would be inaccurate because one-to-many email_marketing entries may be associated
* with a campaign.
*
* @param string $query Select query string
* @param array $param array of arguments
* @return string count query
*
*/
function create_list_count_query($query, $params = array())
{
//include the distinct filter if a marketing id is defined, which means we need to filter out duplicates by the passed in group by.
//if no marketing id is specified, it is understood there might be duplicate target entries so no need to filter out
if (strpos($query, 'marketing_id') !== false && isset($params['distinct'])) {
$pattern = '/SELECT(.*?)(\\s){1}FROM(\\s){1}/is';
// ignores the case
$replacement = 'SELECT COUNT(DISTINCT ' . $params['distinct'] . ') c FROM ';
$query = preg_replace($pattern, $replacement, $query, 1);
return $query;
}
//If distinct parameter not found, default to SugarBean's function
return parent::create_list_count_query($query);
}
/**
* Returns the number of rows that the given SQL query should produce
*
* Internal function, do not override.
* @param string $query valid select query
* @param boolean $is_count_query Optional, Default false, set to true if passed query is a count query.
* @return int count of rows found
*/
function _get_num_rows_in_query($query, $is_count_query = false)
{
$num_rows_in_query = 0;
if (!$is_count_query) {
$count_query = SugarBean::create_list_count_query($query);
} else {
$count_query = $query;
}
$result = $this->db->query($count_query, true, "Error running count query for {$this->object_name} List: ");
$row_num = 0;
$row = $this->db->fetchByAssoc($result, $row_num);
while ($row) {
$num_rows_in_query += current($row);
$row_num++;
$row = $this->db->fetchByAssoc($result, $row_num);
}
return $num_rows_in_query;
}
function getTotalCount($main_query)
{
$count_query = SugarBean::create_list_count_query($main_query);
$result = $GLOBALS['db']->query($count_query);
if ($row = $GLOBALS['db']->fetchByAssoc($result)) {
return $row['c'];
}
return 0;
}
