/**
* create a 'where string' with 'like,<,>,=' assign by stype
*
* @param $stype (array) assign search type
* @param $filter (array) filter in sql
* @param $content (array) content in sql
* @return $joinstr (string) sql where string
*/
function createSqlWithStype($filter, $content, $stype, $table)
{
$i = 0;
$joinstr = '';
foreach ($stype as $type) {
//echo $filter[$i];exit;
$content[$i] = preg_replace("/'/", "\\'", $content[$i]);
if ($filter[$i] != '' && trim($content[$i]) != '') {
if ($filter[$i] == 'groupname' and $table != "accountgroup" and $table != "") {
$group_res = astercrm::getFieldsByField('id', 'groupname', $content[$i], 'accountgroup', $type);
while ($group_res->fetchInto($group_row)) {
$group_str .= "OR {$table}.groupid = '" . $group_row['id'] . "' ";
}
if ($group_str == '') {
$group_str .= " {$table}.groupid = '-1' ";
}
} elseif ($filter[$i] == 'resellername' and $table != "resellergroup" and $table != "") {
$reseller_res = astercrm::getFieldsByField('id', 'resellername', $content[$i], 'resellergroup', $type);
while ($reseller_res->fetchInto($reseller_row)) {
$reseller_str .= "OR {$table}.resellerid = '" . $reseller_row['id'] . "' ";
}
if ($reseller_str == '') {
$reseller_str .= " {$table}.resellerid = '-1' ";
}
} else {
if ($type == "equal") {
$joinstr .= "AND {$filter[$i]} = '" . trim($content[$i]) . "' ";
} elseif ($type == "more") {
$joinstr .= "AND {$filter[$i]} > '" . trim($content[$i]) . "' ";
} elseif ($type == "less") {
$joinstr .= "AND {$filter[$i]} < '" . trim($content[$i]) . "' ";
} else {
$joinstr .= "AND {$filter[$i]} like '%" . trim($content[$i]) . "%' ";
}
}
}
$i++;
}
if ($group_str != '') {
$group_str = ltrim($group_str, 'OR');
$joinstr .= "AND (" . $group_str . ")";
}
if ($reseller_str != '') {
$reseller_str = ltrim($reseller_str, 'OR');
$joinstr .= "AND (" . $reseller_str . ")";
}
//echo $joinstr;exit;
return $joinstr;
}
/**
* create a 'where string' with 'like,<,>,=' assign by stype
*
* @param $stype (array) assign search type
* @param $filter (array) filter in sql
* @param $content (array) content in sql
* @return $joinstr (string) sql where string
*/
function createSqlWithStype($filter, $content, $stype = array(), $table = '', $option = 'search')
{
//print_r($filter);echo $table;exit;
$i = 0;
$joinstr = '';
foreach ($stype as $type) {
$content[$i] = preg_replace("/'/", "\\'", $content[$i]);
if ($filter[$i] != '' && trim($content[$i]) != '') {
if ($filter[$i] == 'groupname' and $table != "astercrm_accountgroup" and $table != "" and $table != "mycdr") {
$group_res = astercrm::getFieldsByField('id', 'groupname', $content[$i], 'astercrm_accountgroup', $type);
while ($group_res->fetchInto($group_row)) {
$group_str .= "OR {$table}.groupid = '" . $group_row['id'] . "' ";
}
if ($group_str == '') {
$group_str .= " {$table}.groupid = '-1' ";
}
} elseif (($filter[$i] == 'campaignname' or $filter[$i] == 'campaign.campaignname' and $option = 'delete') and $table != "campaign" and $table != "") {
$campaign_res = astercrm::getFieldsByField('id', 'campaignname', $content[$i], 'campaign', $type);
while ($campaign_res->fetchInto($campaign_row)) {
$campaign_str .= "OR {$table}.campaignid = '" . $campaign_row['id'] . "' ";
}
if ($campaign_str == '') {
$campaign_str .= " {$table}.campaignid = '0' ";
}
} else {
if ($table == 'monitorrecord' && $filter[$i] == 'dstchannel') {
$content[$i] = 'agent/' . $content[$i];
}
//echo $filter[$i] == 'username' && $table != "" && $table == "mycdr";exit;
if ($filter[$i] == 'username' && $table != "" && $table == "mycdr") {
$filter[$i] = 'astercrm_account.username';
} else {
if ($filter[$i] == 'groupname' && $table != "" && $table == "mycdr") {
$filter[$i] = 'astercrm_accountgroup.groupname';
}
}
if ($type == "equal") {
$joinstr .= "AND {$filter[$i]} = '" . trim($content[$i]) . "' ";
} elseif ($type == "more") {
$joinstr .= "AND {$filter[$i]} > '" . trim($content[$i]) . "' ";
} elseif ($type == "less") {
$joinstr .= "AND {$filter[$i]} < '" . trim($content[$i]) . "' ";
} else {
$joinstr .= "AND {$filter[$i]} like '%" . trim($content[$i]) . "%' ";
}
}
}
$i++;
}
if ($group_str != '') {
$group_str = ltrim($group_str, 'OR');
$joinstr .= "AND (" . $group_str . ")";
}
if ($campaign_str != '') {
$campaign_str = ltrim($campaign_str, 'OR');
$joinstr .= "AND (" . $campaign_str . ")";
}
//echo $joinstr;exit;
return $joinstr;
}