function getOnlineSql($searchContent, $searchField, $searchType = array(), $table, $fields = '', $leftjoins = array()) { global $db, $config; $updateTimeInterval = $config['system']['update_online_interval'] * 60; $joinstr = astercrm::createSqlWithStype($searchField, $searchContent, $searchType, $table); $fieldstr = ''; if (is_array($fields)) { foreach ($fields as $field => $alias) { if (!is_numeric($field)) { $fieldstr .= " " . $field . " AS " . $alias . ","; } else { $fieldstr .= " " . $alias . ","; } } } $leftStr = ''; if (!empty($leftjoins)) { foreach ($leftjoins as $model => $param) { // the keys of array $leftjoins are the table which need to left join $leftStr .= 'LEFT JOIN ' . $model . ' ON ' . $param[0] . '=' . $param[1] . ' '; } } if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); if ($fieldstr != '') { $fieldstr = rtrim($fieldstr, ','); $query = "SELECT " . $fieldstr . " FROM " . $table . " " . $leftStr . " WHERE (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(last_update_time)) < {$updateTimeInterval} AND " . $joinstr; } else { $query = "SELECT * FROM " . $table . " '" . $leftStr . " WHERE (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(last_update_time)) < {$updateTimeInterval} AND " . $joinstr; } } else { if ($fieldstr != '') { $fieldstr = rtrim($fieldstr, ','); $query = "SELECT " . $fieldstr . " FROM " . $table . " " . $leftStr . "WHERE (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(last_update_time)) < {$updateTimeInterval} "; } else { $query = "SELECT * FROM " . $table . " WHERE (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(last_update_time)) < {$updateTimeInterval} "; } } return $query; }
function searchFormSubmit($searchFormValue, $numRows = null, $limit = null, $id = null, $type = null) { global $locate, $db, $config; //根据后台设置导出的customer字段,来导出diallist关联的customer数据 $customerField = ''; if ($config['system']['export_customer_fields_in_dialedlist'] != '') { $relateCustomerFieldArr = explode(',', $config['system']['export_customer_fields_in_dialedlist']); foreach ($relateCustomerFieldArr as $tmp) { $customerField .= 'customer.' . $tmp . ','; } } $objResponse = new xajaxResponse(); $searchField = array(); $searchContent = array(); $optionFlag = $searchFormValue['optionFlag']; $exportFlag = $searchFormValue['exportFlag']; $searchContent = $searchFormValue['searchContent']; //搜索内容 数组 $searchField = $searchFormValue['searchField']; //搜索条件 数组 $searchType = $searchFormValue['searchType']; $ordering = $searchFormValue['ordering']; $order = $searchFormValue['order']; $divName = "grid"; if ($optionFlag == "export" || $optionFlag == "exportcsv") { $joinstr = astercrm::createSqlWithStype($searchField, $searchContent, $searchType, 'campaigndialedlist'); $joinstr = ltrim($joinstr, 'AND'); if ($customerField != '') { $sql = "SELECT campaigndialedlist.dialednumber,customer.customer,campaigndialedlist.customername,campaigndialedlist.dialtime,campaigndialedlist.answertime,campaigndialedlist.duration,campaigndialedlist.billsec,campaigndialedlist.billsec_leg_a as total_billsec,campaigndialedlist.campaignresult,campaigndialedlist.response,campaigndialedlist.detect,campaigndialedlist.transfertime,campaigndialedlist.transfertarget,campaigndialedlist.resultby,campaigndialedlist.dialedby, groupname, campaignname,campaigndialedlist.dialedtime," . rtrim($customerField, ',') . " FROM campaigndialedlist LEFT JOIN astercrm_accountgroup ON astercrm_accountgroup.groupid = campaigndialedlist.groupid LEFT JOIN campaign ON campaign.id = campaigndialedlist.campaignid LEFT JOIN customer ON customer.id = campaigndialedlist.customerid "; } else { $sql = "SELECT campaigndialedlist.dialednumber,customer.customer,campaigndialedlist.customername,campaigndialedlist.dialtime,campaigndialedlist.answertime,campaigndialedlist.duration,campaigndialedlist.billsec,campaigndialedlist.billsec_leg_a as total_billsec,campaigndialedlist.campaignresult,campaigndialedlist.response,campaigndialedlist.detect,campaigndialedlist.transfertime,campaigndialedlist.transfertarget,campaigndialedlist.resultby,campaigndialedlist.dialedby, groupname, campaignname,campaigndialedlist.dialedtime FROM campaigndialedlist LEFT JOIN astercrm_accountgroup ON astercrm_accountgroup.groupid = campaigndialedlist.groupid LEFT JOIN campaign ON campaign.id = campaigndialedlist.campaignid LEFT JOIN customer ON customer.id = campaigndialedlist.customerid "; } if ($joinstr != '') { $sql .= " WHERE " . $joinstr; } $_SESSION['export_sql'] = $sql; $objResponse->addAssign("hidSql", "value", $sql); //赋值隐含域 $objResponse->addAssign("maintable", "value", 'campaigndialedlist'); //赋值隐含域 $objResponse->addAssign("exporttype", "value", $optionFlag); $objResponse->addScript("document.getElementById('exportForm').submit();"); } elseif ($optionFlag == "delete") { astercrm::deletefromsearch($searchContent, $searchField, $searchType, 'campaigndialedlist'); $html = createGrid($searchFormValue['numRows'], $searchFormValue['limit'], '', '', '', $divName, "", ''); $objResponse->addClear("msgZone", "innerHTML"); $objResponse->addAssign($divName, "innerHTML", $html); } elseif ($optionFlag == "recycle") { $num = Customer::recyclefromsearch($searchContent, $searchField, $searchType, 'campaigndialedlist'); $html = createGrid($searchFormValue['numRows'], $searchFormValue['limit'], '', '', $order, $divName, $ordering, ''); $objResponse->addClear("msgZone", "innerHTML"); $objResponse->addALert($num . " " . $locate->Translate("number have been recycled")); $objResponse->addAssign($divName, "innerHTML", $html); $noanswer = Customer::getNoanswerCallsNumber(); $objResponse->addAssign("spanRecycleUp", "innerHTML", "No answer calls and never recycle: {$noanswer}"); $objResponse->addAssign("spanRecycleDown", "innerHTML", "No answer calls and never recycle: {$noanswer}"); } else { if ($type == "delete") { $res = Customer::deleteRecord($id, 'campaigndialedlist'); if ($res) { $html = createGrid($searchFormValue['numRows'], $searchFormValue['limit'], $searchField, $searchContent, $order, $divName, $ordering, $searchType); $objResponse = new xajaxResponse(); $objResponse->addAssign("msgZone", "innerHTML", $locate->Translate("delete_rec")); } else { $objResponse->addAssign("msgZone", "innerHTML", $locate->Translate("rec_cannot_delete")); } } else { $html = createGrid($numRows, $limit, $searchField, $searchContent, $order, $divName, $ordering, $searchType); } $objResponse->addClear("msgZone", "innerHTML"); $objResponse->addAssign($divName, "innerHTML", $html); } return $objResponse->getXML(); }
function &getNumRowsMorewithstype($filter, $content, $stype, $table) { global $customers_db, $config; $joinstr = astercrm::createSqlWithStype($filter, $content, $stype); $sql = "SELECT COUNT(*) FROM " . $config['customers']['customertable'] . " WHERE "; //if ($_SESSION['curuser']['usertype'] == 'admin'){ $sql .= " 1 "; //} if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); //去掉最左边的AND $sql .= " AND " . $joinstr; } Customer::events($sql); $res =& $customers_db->getOne($sql); return $res; }
function &getNumRowsMorewithstype($filter, $content, $stype, $table) { global $db; $joinstr = astercrm::createSqlWithStype($filter, $content, $stype); $sql = "SELECT COUNT(*) AS numRows FROM myrate LEFT JOIN accountgroup ON accountgroup.id = myrate.groupid LEFT JOIN resellergroup ON resellergroup.id = myrate.resellerid WHERE"; if ($_SESSION['curuser']['usertype'] == 'admin') { $sql .= " 1 "; } elseif ($_SESSION['curuser']['usertype'] == 'reseller') { $sql .= " (myrate.resellerid = " . $_SESSION['curuser']['resellerid'] . " OR myrate.resellerid = 0) "; } else { $sql .= " ( (myrate.groupid = " . $_SESSION['curuser']['groupid'] . " OR myrate.groupid = 0) AND (myrate.resellerid = " . $_SESSION['curuser']['resellerid'] . " OR myrate.resellerid = 0) )"; } if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); //去掉最左边的AND $sql .= " AND " . $joinstr . " "; } else { $sql .= " 1 "; } Customer::events($sql); $res =& $db->getOne($sql); return $res; }
function &getRecordsFilteredMorewithstype($start, $limit, $filter, $content, $stype, $order, $table) { global $db; $joinstr = astercrm::createSqlWithStype($filter, $content, $stype); //<---- change by your function $sql = "SELECT * FROM sms_sents WHERE 1 "; if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); //去掉最左边的AND $sql .= " AND " . $joinstr . " " . " ORDER BY " . $order . " " . $_SESSION['ordering'] . " LIMIT {$start}, {$limit} {$ordering}"; } Customer::events($sql); $res =& $db->query($sql); return $res; }
function deleteDuplicates($f) { global $db, $locate; $joinstr = astercrm::createSqlWithStype($f['searchField'], $f['searchContent'], $f['searchType'], "diallist"); $ajoinstr = str_replace('diallist.', 'a.', $joinstr); if ($_SESSION['curuser']['usertype'] != 'admin') { $ajoinstr .= " AND a.groupid = '" . $_SESSION['curuser']['groupid'] . "'"; $joinstr .= " AND diallist.groupid = '" . $_SESSION['curuser']['groupid'] . "'"; } $query = "DELETE diallist as a FROM diallist as a ,( SELECT * FROM diallist WHERE 1 " . $joinstr . " GROUP BY dialnumber HAVING COUNT(dialnumber) > 1 ) as b WHERE a.dialnumber = b.dialnumber AND a.id <> b.id " . $ajoinstr . " "; $res = $db->query($query); return $res; }
function exportDuplicates($f) { $objResponse = new xajaxResponse(); $joinstr = astercrm::createSqlWithStype($f['searchField'], $f['searchContent'], $f['searchType'], "diallist"); $ajoinstr = str_replace('diallist.', 'a.', $joinstr); if ($_SESSION['curuser']['usertype'] != 'admin') { $ajoinstr .= " AND a.groupid = '" . $_SESSION['curuser']['groupid'] . "'"; $joinstr .= " AND diallist.groupid = '" . $_SESSION['curuser']['groupid'] . "'"; } $query = "SELECT a.*,campaign.campaignname FROM diallist as a LEFT JOIN campaign ON campaign.id=a.campaignid,( SELECT * FROM diallist WHERE 1 " . $joinstr . " GROUP BY dialnumber HAVING COUNT(dialnumber) > 1 ) as b WHERE a.dialnumber = b.dialnumber AND a.id <> b.id " . $ajoinstr . " "; $_SESSION['export_sql'] = $query; $objResponse->addAssign("hidSql", "value", $query); //赋值隐含域 $objResponse->addAssign("maintable", "value", 'diallist_dup'); //赋值隐含域 $objResponse->addAssign("exporttype", "value", 'exportcsv'); $objResponse->addScript("document.getElementById('exportForm').submit();"); return $objResponse; }
function searchFormSubmit($searchFormValue, $numRows = null, $limit = null, $id = null, $type = null) { global $locate, $db; $objResponse = new xajaxResponse(); $searchField = array(); $searchContent = array(); $optionFlag = $searchFormValue['optionFlag']; $exportFlag = $searchFormValue['exportFlag']; $searchContent = $searchFormValue['searchContent']; //搜索内容 数组 $searchField = $searchFormValue['searchField']; //搜索条件 数组 $divName = "grid"; $searchType = $searchFormValue['searchType']; $ordering = $searchFormValue['ordering']; $order = $searchFormValue['order']; if ($exportFlag == "1" || $optionFlag == "export") { // 需要特殊处理 // $sql = astercrm::getSql($searchContent,$searchField,$searchType,'surveyresult'); //得到要导出的sql语句 $joinstr = astercrm::createSqlWithStype($searchField, $searchContent, $searchType, 'surveyresult'); $objResponse->addAssign("hidSql", "value", $joinstr); //赋值隐含域 $objResponse->addScript("document.getElementById('exportForm').submit();"); } elseif ($optionFlag == "delete") { astercrm::deletefromsearch($searchContent, $searchField, $searchType, 'surveyresult'); $html = createGrid($searchFormValue['numRows'], $searchFormValue['limit'], '', '', $order, $divName, $ordering, 1, 1, ''); $objResponse->addClear("msgZone", "innerHTML"); $objResponse->addAssign($divName, "innerHTML", $html); } else { if ($type == "delete") { $res = Customer::deleteRecord($id, 'surveyresult'); if ($res) { $html = createGrid($searchFormValue['numRows'], $searchFormValue['limit'], $searchField, $searchContent, $order, $divName, $ordering, "", $searchType); $objResponse = new xajaxResponse(); $objResponse->addAssign("msgZone", "innerHTML", $locate->Translate("delete_rec")); } else { $objResponse->addAssign("msgZone", "innerHTML", $locate->Translate("rec_cannot_delete")); } } else { $html = createGrid($numRows, $limit, $searchField, $searchContent, $order, $divName, $ordering, "", $searchType); } $objResponse = new xajaxResponse(); $objResponse->addClear("msgZone", "innerHTML"); $objResponse->addAssign($divName, "innerHTML", $html); } return $objResponse->getXML(); }
function &getRecRecordsFilteredMorewithstype($customerid, $start, $limit, $filter, $content, $stype, $order) { global $db; $joinstr = astercrm::createSqlWithStype($filter, $content, $stype, 'monitorrecord'); if ($customerid != '') { $sql = astercrm::getCustomerphoneSqlByid($customerid, 'dst', 'OR', 'src', 'mycdr'); } if ($_SESSION['curuser']['usertype'] == 'admin' && $customerid == '') { $sql = "SELECT mycdr.calldate,mycdr.src,mycdr.dst,mycdr.didnumber,mycdr.dstchannel,mycdr.duration,mycdr.billsec,monitorrecord.id,monitorrecord.filename,monitorrecord.fileformat,monitorrecord.processed,monitorrecord.creby,astercrm_accountgroup.groupname,astercrm_account.username FROM mycdr LEFT JOIN monitorrecord ON mycdr.monitored = monitorrecord.id LEFT JOIN astercrm_accountgroup ON astercrm_accountgroup.id = mycdr.astercrm_groupid LEFT JOIN astercrm_account ON astercrm_account.id = mycdr.accountid WHERE mycdr.processed >= 0 "; } elseif ($_SESSION['curuser']['usertype'] == 'groupadmin' && $customerid == '') { $sql = "SELECT mycdr.calldate,mycdr.src,mycdr.dst,mycdr.didnumber,mycdr.dstchannel,mycdr.duration,mycdr.billsec,monitorrecord.id,monitorrecord.filename,monitorrecord.fileformat,monitorrecord.processed,monitorrecord.creby,astercrm_accountgroup.groupname,astercrm_account.username FROM mycdr LEFT JOIN monitorrecord ON mycdr.monitored = monitorrecord.id LEFT JOIN astercrm_accountgroup ON astercrm_accountgroup.id = mycdr.astercrm_groupid LEFT JOIN astercrm_account ON astercrm_account.id = mycdr.accountid WHERE mycdr.astercrm_groupid = '" . $_SESSION['curuser']['groupid'] . "' AND mycdr.processed >= 0 "; } else { if ($sql != '') { if ($_SESSION['curuser']['usertype'] == 'admin') { $sql = "SELECT mycdr.calldate,mycdr.src,mycdr.dst,mycdr.didnumber,mycdr.dstchannel,mycdr.duration,mycdr.billsec,monitorrecord.id,monitorrecord.filename,monitorrecord.fileformat,monitorrecord.processed,monitorrecord.creby,astercrm_accountgroup.groupname,astercrm_account.username FROM mycdr LEFT JOIN monitorrecord ON mycdr.monitored = monitorrecord.id LEFT JOIN astercrm_accountgroup ON astercrm_accountgroup.id = mycdr.astercrm_groupid LEFT JOIN astercrm_account ON astercrm_account.id = mycdr.accountid WHERE (" . $sql . ") AND mycdr.processed >= 0 "; } elseif ($_SESSION['curuser']['usertype'] == 'groupadmin') { $sql = "SELECT mycdr.calldate,mycdr.src,mycdr.dst,mycdr.didnumber,mycdr.dstchannel,mycdr.duration,mycdr.billsec,monitorrecord.id,monitorrecord.filename,monitorrecord.fileformat,monitorrecord.processed,monitorrecord.creby,astercrm_accountgroup.groupname,astercrm_account.username FROM mycdr LEFT JOIN monitorrecord ON mycdr.monitored = monitorrecord.id LEFT JOIN astercrm_accountgroup ON astercrm_accountgroup.id = mycdr.astercrm_groupid LEFT JOIN astercrm_account ON astercrm_account.id = mycdr.accountid WHERE (" . $sql . ") AND mycdr.astercrm_groupid = '" . $_SESSION['curuser']['groupid'] . "' AND mycdr.processed >= 0 "; } else { $sql = "SELECT mycdr.calldate,mycdr.src,mycdr.dst,mycdr.didnumber,mycdr.dstchannel,mycdr.duration,mycdr.billsec,monitorrecord.id,monitorrecord.filename,monitorrecord.fileformat,monitorrecord.processed,monitorrecord.creby,astercrm_accountgroup.groupname,astercrm_account.username FROM mycdr LEFT JOIN monitorrecord ON mycdr.monitored = monitorrecord.id LEFT JOIN astercrm_accountgroup ON astercrm_accountgroup.id = mycdr.astercrm_groupid LEFT JOIN astercrm_account ON astercrm_account.id = mycdr.accountid WHERE (" . $sql . ") AND mycdr.accountid = '" . $_SESSION['curuser']['accountid'] . "' AND mycdr.processed >= 0 "; } } else { $sql = "SELECT mycdr.* FROM monitorrecord LEFT JOIN mycdr ON mycdr.monitored = monitorrecord.id LEFT JOIN astercrm_accountgroup ON astercrm_accountgroup.id = mycdr.astercrm_groupid LEFT JOIN astercrm_account ON astercrm_account.id = mycdr.accountid WHERE mycdr.id = 0"; } } if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); //去掉最左边的AND $sql .= " AND " . $joinstr . " "; } $sql .= " ORDER BY " . $order . " DESC LIMIT {$start}, {$limit} {$ordering}"; astercrm::events($sql); $res =& $db->query($sql); return $res; }
function &getRecordsFilteredMorewithstype($start, $limit, $filter, $content, $stype, $order, $table) { global $db; $joinstr = astercrm::createSqlWithStype($filter, $content, $stype); //<---- change by your function $sql = "SELECT sms_templates.*,campaign.campaignname,trunkinfo.trunkname FROM sms_templates LEFT JOIN campaign ON campaign.id = sms_templates.campaign_id LEFT JOIN trunkinfo ON trunkinfo.id = sms_templates.trunkinfo_id WHERE 1 "; if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); //去掉最左边的AND $sql .= " AND " . $joinstr . " " . " ORDER BY " . $order . " " . $_SESSION['ordering'] . " LIMIT {$start}, {$limit} {$ordering}"; } Customer::events($sql); $res =& $db->query($sql); return $res; }
function &getRecordsFilteredMorewithstype($start, $limit, $filter, $content, $stype = null, $order, $table, $ordering = "") { global $db, $config; $joinstr = astercrm::createSqlWithStype($filter, $content, $stype); if ($config['system']['portal_display_type'] == "note") { if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); //去掉最左边的AND $sql = "SELECT \r\n\t\t\t\t\t\t\t\t\t\t\tnote.id AS id, \r\n\t\t\t\t\t\t\t\t\t\t\tnote, \r\n\t\t\t\t\t\t\t\t\t\t\tpriority,\r\n\t\t\t\t\t\t\t\t\t\t\tprivate,\r\n\t\t\t\t\t\t\t\t\t\t\tcustomer.customer AS customer,\r\n\t\t\t\t\t\t\t\t\t\t\tcontact.contact AS contact,\r\n\t\t\t\t\t\t\t\t\t\t\tcustomer.category AS category,\r\n\t\t\t\t\t\t\t\t\t\t\tnote.cretime AS cretime,\r\n\t\t\t\t\t\t\t\t\t\t\tnote.creby AS creby,\r\n\t\t\t\t\t\t\t\t\t\t\tnote.customerid AS customerid,\r\n\t\t\t\t\t\t\t\t\t\t\tnote.contactid AS contactid\r\n\t\t\t\t\t\t\t\t\t\t\tFROM note \r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN customer ON customer.id = note.customerid \r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN contact ON contact.id = note.contactid\r\n\t\t\t\t\t\t\t\t\t\t\tWHERE {$joinstr} \r\n\t\t\t\t\t\t\t\t\t\t\tAND priority>0\r\n\t\t\t\t\t\t\t\t\t\t\tAND note.creby = '" . $_SESSION['curuser']['username'] . "' " . " ORDER BY {$order} " . $_SESSION['ordering'] . " LIMIT {$start}, {$limit} "; } else { $sql = "SELECT \r\n\t\t\t\t\t\t\t\t\t\t\tnote.id AS id, \r\n\t\t\t\t\t\t\t\t\t\t\tnote, \r\n\t\t\t\t\t\t\t\t\t\t\tpriority,\r\n\t\t\t\t\t\t\t\t\t\t\tprivate,\r\n\t\t\t\t\t\t\t\t\t\t\tcustomer.customer AS customer,\r\n\t\t\t\t\t\t\t\t\t\t\tcontact.contact AS contact,\r\n\t\t\t\t\t\t\t\t\t\t\tcustomer.category AS category,\r\n\t\t\t\t\t\t\t\t\t\t\tnote.cretime AS cretime,\r\n\t\t\t\t\t\t\t\t\t\t\tnote.creby AS creby ,\r\n\t\t\t\t\t\t\t\t\t\t\tnote.customerid AS customerid,\r\n\t\t\t\t\t\t\t\t\t\t\tnote.contactid AS contactid\r\n\t\t\t\t\t\t\t\t\t\t\tFROM note \r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN customer ON customer.id = note.customerid \r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN contact ON contact.id = note.contactid" . " AND note.creby = '" . $_SESSION['curuser']['username'] . "' " . " ORDER BY {$order} " . $_SESSION['ordering'] . " LIMIT {$start}, {$limit} "; } } else { if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); //去掉最左边的AND $sql = "SELECT customer.id AS id,\r\n\t\t\t\t\t\t\t\t\t\t\tcustomer.customer AS customer,\r\n\t\t\t\t\t\t\t\t\t\t\tcustomer.category AS category,\r\n\t\t\t\t\t\t\t\t\t\t\tcustomer.contact AS contact,\r\n\t\t\t\t\t\t\t\t\t\t\tcustomer.cretime as cretime,\r\n\t\t\t\t\t\t\t\t\t\t\tcustomer.phone as phone,\r\n\t\t\t\t\t\t\t\t\t\t\tcustomer.mobile as mobile,\r\n\t\t\t\t\t\t\t\t\t\t\tnote.note AS note,\r\n\t\t\t\t\t\t\t\t\t\t\tnote.priority AS priority,\r\n\t\t\t\t\t\t\t\t\t\t\tnote.attitude AS attitude,\r\n\t\t\t\t\t\t\t\t\t\t\tnote.private AS private,\r\n\t\t\t\t\t\t\t\t\t\t\tnote.creby AS creby\r\n\t\t\t\t\t\t\t\t\t\t\tFROM customer\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN note ON customer.id = note.customerid" . " WHERE " . $joinstr; if ($config['system']['detail_level'] != 'all') { $sql .= " AND customer.groupid = '" . $_SESSION['curuser']['groupid'] . "' "; } $sql .= " ORDER BY {$order} " . $_SESSION['ordering'] . " LIMIT {$start}, {$limit} "; } else { $sql = "SELECT customer.id AS id,\r\n\t\t\t\t\t\t\t\t\t\t\tcustomer.customer AS customer,\r\n\t\t\t\t\t\t\t\t\t\t\tcustomer.category AS category,\r\n\t\t\t\t\t\t\t\t\t\t\tcustomer.contact AS contact,\r\n\t\t\t\t\t\t\t\t\t\t\tcustomer.cretime as cretime,\r\n\t\t\t\t\t\t\t\t\t\t\tcustomer.phone as phone,\r\n\t\t\t\t\t\t\t\t\t\t\tcustomer.mobile as mobile,\r\n\t\t\t\t\t\t\t\t\t\t\tnote.note AS note,\r\n\t\t\t\t\t\t\t\t\t\t\tnote.priority AS priority,\r\n\t\t\t\t\t\t\t\t\t\t\tnote.attitude AS attitude,\r\n\t\t\t\t\t\t\t\t\t\t\tnote.private AS private,\r\n\t\t\t\t\t\t\t\t\t\t\tnote.creby AS creby\r\n\t\t\t\t\t\t\t\t\t\t\tFROM customer\r\n\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN note ON customer.id = note.customerid "; if ($config['system']['detail_level'] != 'all') { $sql .= " WHERE customer.groupid = '" . $_SESSION['curuser']['groupid'] . "' "; } $sql .= " ORDER BY {$order} " . $_SESSION['ordering'] . " LIMIT {$start}, {$limit} "; } } astercrm::events($sql); $res =& $db->query($sql); return $res; }
function searchFormSubmit($searchFormValue, $numRows = null, $limit = null, $id = null, $type = null) { global $locate, $db; $objResponse = new xajaxResponse(); $searchField = array(); $searchContent = array(); $optionFlag = $searchFormValue['optionFlag']; $exportFlag = $searchFormValue['exportFlag']; $searchContent = $searchFormValue['searchContent']; //搜索内容 数组 $searchField = $searchFormValue['searchField']; //搜索条件 数组 $searchType = $searchFormValue['searchType']; $ordering = $searchFormValue['ordering']; $order = $searchFormValue['order']; $divName = "grid"; if ($optionFlag == "export" || $optionFlag == "exportcsv") { $sql = astercrm::getSql($searchContent, $searchField, $searchType, 'dnc_list'); //得到要导出的sql语句 $joinstr = astercrm::createSqlWithStype($searchField, $searchContent, $searchType, 'dnc_list'); $joinstr = ltrim($joinstr, 'AND'); $sql = "SELECT dnc_list.number,dnc_list.status,groupname,campaignname,dnc_list.cretime,dnc_list.creby FROM dnc_list LEFT JOIN astercrm_accountgroup ON astercrm_accountgroup.groupid = dnc_list.groupid LEFT JOIN campaign ON campaign.id = dnc_list.campaignid "; if ($joinstr != '') { $sql .= " WHERE " . $joinstr; } $_SESSION['export_sql'] = $sql; $objResponse->addAssign("hidSql", "value", $sql); //赋值隐含域 $objResponse->addAssign("maintable", "value", 'dnc_list'); //赋值隐含域 $objResponse->addAssign("exporttype", "value", $optionFlag); $objResponse->addScript("document.getElementById('exportForm').submit();"); } elseif ($optionFlag == "delete") { astercrm::deletefromsearch($searchContent, $searchField, $searchType, 'dnc_list'); $html = createGrid($searchFormValue['numRows'], $searchFormValue['limit'], '', '', $order, $divName, $ordering, ''); $objResponse->addClear("msgZone", "innerHTML"); $objResponse->addAssign($divName, "innerHTML", $html); } else { if ($type == "delete") { $res = Customer::deleteRecord($id, 'dnc_list'); if ($res) { $html = createGrid($searchFormValue['numRows'], $searchFormValue['limit'], $searchField, $searchContent, $order, $divName, $ordering, $searchType); $objResponse = new xajaxResponse(); $objResponse->addAssign("msgZone", "innerHTML", $locate->Translate("delete_rec")); } else { $objResponse->addAssign("msgZone", "innerHTML", $locate->Translate("rec_cannot_delete")); } } else { $html = createGrid($numRows, $limit, $searchField, $searchContent, $order, $divName, $ordering, $searchType); } $objResponse->addClear("msgZone", "innerHTML"); $objResponse->addAssign($divName, "innerHTML", $html); } return $objResponse->getXML(); }
function multiEditUpdate($searchContent = array(), $searchField = array(), $searchType = array(), $table, $f) { global $db, $locate; $objResponse = new xajaxResponse(); $searchContent = split(',', $searchContent); $searchField = split(',', $searchField); $searchType = split(',', $searchType); $i = 0; foreach ($searchField as $field) { if (trim($searchType[$i]) != '' && trim($searchContent[$i]) != '') { if ($field == 'resellername' && trim($searchType[$i]) != '' && trim($searchContent[$i]) != '') { if (trim($searchType[$i]) == "like") { $whereResellername .= "AND resellername LIKE '%" . $searchContent[$i] . "%' "; } elseif (trim($searchType[$i]) == "equal") { $whereResellername .= "AND resellername = '" . $searchContent[$i] . "' "; } elseif (trim($searchType[$i]) == "more") { $whereResellername .= "AND resellername > '" . $searchContent[$i] . "' "; } elseif (trim($searchType[$i]) == "less") { $whereResellername .= "AND resellername < '" . $searchContent[$i] . "' "; } $searchField[$i] = ''; } elseif ($field == 'groupname') { if (trim($searchType[$i]) == "like") { $whereGroupname .= "AND groupname like '%" . $searchContent[$i] . "%' "; } elseif (trim($searchType[$i]) == "equal") { $whereGroupname .= "AND groupname = '" . $searchContent[$i] . "' "; } elseif (trim($searchType[$i]) == "more") { $whereGroupname .= "AND groupname > '" . $searchContent[$i] . "' "; } elseif (trim($searchType[$i]) == "less") { $whereGroupname .= "AND groupname < '" . $searchContent[$i] . "' "; } $searchField[$i] = ''; } $i++; } } if ($whereResellername != '') { $sql = "SELECT id FROM resellergroup WHERE 1 " . $whereResellername; $resellerRes =& $db->query($sql); while ($resellerRes->fetchInto($row)) { $resellerJoinStr .= "AND resellerid = " . $row['id'] . " "; } } if ($whereGroupname != '') { $sql = "SELECT id FROM accountgroup WHERE 1 " . $whereGroupname; $groupRes =& $db->query($sql); while ($groupRes->fetchInto($row)) { $groupJoinStr .= "AND groupid = " . $row['id'] . " "; } } $joinstr = astercrm::createSqlWithStype($searchField, $searchContent, $searchType); list($field, $fieldType) = split(',', $f['multieditField']); $sucessNum = 0; if ($f['multioption'] == 'modify') { $query = "SELECT id," . trim($field) . " FROM " . $table . " WHERE 1 " . $joinstr . $groupJoinStr . $resellerJoinStr; astercrm::events($query); $res = $db->query($query); while ($res->fetchInto($row)) { if ($f['multieditcontent'] != '') { if ($f['multieditType'] == 'to') { if ($fieldType == 'int' || $fieldType == 'real') { if (!is_numeric($f['multieditcontent'])) { $objResponse->addAlert($locate->Translate("Must fill number in blank for field") . ":" . $field); return $objResponse; } } $newValue = $f['multieditcontent']; } else { if (!is_numeric($f['multieditcontent'])) { $objResponse->addAlert($locate->Translate("Must fill number in blank for field") . ":" . $field); return $objResponse; } if ($f['multieditType'] == 'plus') { $newValue = $row[$field] + $f['multieditcontent']; } elseif ($f['multieditType'] == 'minus') { $newValue = $row[$field] - $f['multieditcontent']; } elseif ($f['multieditType'] == 'multiply') { $newValue = $row[$field] * $f['multieditcontent']; } } $updateSql = "UPDATE " . $table . " SET " . trim($field) . " = '" . $newValue . "'"; if ($f['resellerid'] != '') { $updateSql .= ", resellerid =" . $f['resellerid'] . ", groupid =" . $f['groupid']; } $updateSql .= ",addtime = now() WHERE id = " . $row['id']; } else { if ($f['resellerid'] != '') { $updateSql = "UPDATE " . $table . " SET resellerid =" . $f['resellerid'] . ", groupid =" . $f['groupid'] . ",addtime = now() WHERE id = " . $row['id']; } else { $objResponse->addAlert($locate->Translate("No Option")); return $objResponse; } } astercrm::events($updateSql); $updateRes = $db->query($updateSql); if ($updateRes === 1) { $sucessNum++; } } $objResponse->addAlert($sucessNum . $locate->Translate("records have been changed")); } elseif ($f['multioption'] == 'duplicate') { $query = "SELECT * FROM " . $table . " WHERE 1 " . $joinstr . $groupJoinStr . $resellerJoinStr; astercrm::events($query); $res =& $db->query($query); while ($res->fetchInto($row)) { $insertField = ''; $insertValue = ''; if ($f['multieditcontent'] != '') { if ($f['multieditType'] == 'to') { if ($fieldType == 'int' || $fieldType == 'real') { if (!is_numeric($f['multieditcontent'])) { $objResponse->addAlert($locate->Translate("Must fill number in blank for field") . ":" . $field); return $objResponse; } } $newValue = $f['multieditcontent']; } else { if (!is_numeric($f['multieditcontent'])) { $objResponse->addAlert($locate->Translate("Must fill number in blank for field") . ":" . $field); return $objResponse; } if ($f['multieditType'] == 'plus') { $newValue = $row[$field] + $f['multieditcontent']; } elseif ($f['multieditType'] == 'minus') { $newValue = $row[$field] - $f['multieditcontent']; } elseif ($f['multieditType'] == 'multiply') { $newValue = $row[$field] * $f['multieditcontent']; } } foreach ($row as $key => $value) { if (!preg_match("/id\$/", $key) && $key != 'addtime') { $insertField .= $key . ","; if ($key != $field) { $insertValue .= "'" . $value . "',"; } else { $insertValue .= "'" . $newValue . "',"; } } } $insertField .= "resellerid,groupid"; if ($f['resellerid'] != '') { $insertValue .= $f['resellerid'] . "," . $f['groupid']; } else { $insertValue .= $row['resellerid'] . "," . $row['groupid']; } } else { foreach ($row as $key => $value) { if (!preg_match("/id\$/", $key) && $key != 'addtime') { $insertField .= $key . ","; $insertValue .= "'" . $value . "',"; } } if ($f['resellerid'] != '') { $insertField .= " resellerid,groupid"; $insertValue .= $f['resellerid'] . "," . $f['groupid']; } else { $objResponse->addAlert($locate->Translate("No Option")); return $objResponse; } } $insertField = "(" . $insertField . ",addtime)"; $insertValue = "(" . $insertValue . ",now())"; $insertSql = "INSERT INTO " . $table . " " . $insertField . " VALUES " . $insertValue; //echo $insertSql;exit; astercrm::events($insertSql); $insertRes = $db->query($insertSql); if ($insertRes === 1) { $sucessNum++; } } $objResponse->addAlert($sucessNum . $locate->Translate("records have been added")); } $html = createGrid(0, 25, $searchField, $searchContent, $searchField, 'grid', "", 1, 1, $searchType, $f['displaymode']); $objResponse->addClear("msgZone", "innerHTML"); $objResponse->addAssign('grid', "innerHTML", $html); return $objResponse; }
function &getRecordsFilteredMorewithstype($start, $limit, $filter, $content, $stype = null, $order, $table, $ordering = "", $allOrAnswer = null) { global $db; $joinstr = astercrm::createSqlWithStype($filter, $content, $stype); if ($_SESSION['curuser']['usertype'] == 'admin') { $sql = "SELECT * FROM " . $table . " WHERE (groupid > '0' OR resellerid > '0')"; } elseif ($_SESSION['curuser']['usertype'] == 'groupadmin') { $sql = "SELECT * FROM " . $table . " WHERE groupid = '" . $_SESSION['curuser']['groupid'] . "'"; } elseif ($_SESSION['curuser']['usertype'] == 'reseller') { $sql = "SELECT * FROM " . $table . " WHERE resellerid = '" . $_SESSION['curuser']['resellerid'] . "'"; } elseif ($_SESSION['curuser']['usertype'] == 'clid') { $sql = "SELECT * FROM " . $table . " WHERE src = '" . $_SESSION['curuser']['username'] . "'"; } if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); //去掉最左边的AND $sql .= " AND " . $joinstr . " "; } if (!empty($allOrAnswer) && $allOrAnswer == 'answered') { $sql .= " AND billsec > 0 "; } $sql .= " ORDER BY " . $order . " " . $_SESSION['ordering'] . " LIMIT {$start}, {$limit} {$ordering}"; Customer::events($sql); $res =& $db->query($sql); return $res; }
function &getNumRowsMorewithstype($filter, $content, $stype, $table) { global $db; $joinstr = astercrm::createSqlWithStype($filter, $content, $stype); if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); //去掉最左边的AND $sql = 'SELECT COUNT(*) AS numRows FROM resellergroup WHERE ' . $joinstr; } else { $sql = "SELECT COUNT(*) AS numRows FROM resellergroup"; } Customer::events($sql); $res =& $db->getOne($sql); return $res; }
function getExportSql($searchContent, $searchField, $searchType, $table) { global $db; $i = 0; $joinstr = astercrm::createSqlWithStype($searchField, $searchContent, $searchType); if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); $sql = "SELECT note_leads.id AS noteid,note_leads.note,note_leads.callerid AS customerphone,note_leads.priority,note_leads.attitude,note_leads.cretime AS notecretime,note_leads.creby AS notecreby,note_leads.private,customer_leads.* FROM note_leads LEFT JOIN customer_leads ON customer_leads.id = note_leads.customerid " . " WHERE " . $joinstr . " "; } else { $sql = "SELECT note_leads.*,customer_leads.* FROM note_leads LEFT JOIN customer_leads ON customer_leads.id = note_leads.customerid "; } return $sql; }
function &getNumRowsMorewithstype($filter, $content, $stype, $table) { global $db; $joinstr = astercrm::createSqlWithStype($filter, $content, $stype); $sql = "SELECT COUNT(*) FROM account_log WHERE "; if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); //去掉最左边的AND $sql .= " " . $joinstr; } else { $sql .= " 1"; } Customer::events($sql); $res =& $db->getOne($sql); return $res; }
function deleteFromSearch($searchContent, $searchField, $searchType = "", $table) { global $db; $joinstr = astercrm::createSqlWithStype($searchField, $searchContent, $searchType, $table); if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); $sql = 'DELETE FROM ' . $table . ' LEFT JOIN clid ON clid.id = curcdr.userid LEFT JOIN resellergroup ON resellergroup.id = curcdr.resellerid LEFT JOIN accountgroup ON accountgroup.id = curcdr.groupid WHERE ' . $joinstr; } else { if ($_SESSION['curuser']['usertype'] == 'admin') { $sql = 'TRUNCATE ' . $table; } else { $sql = "DELETE FROM " . $table . " WHERE " . $table . ".groupid = '" . $_SESSION['curuser']['groupid'] . "'"; } } //echo $sql;exit; Customer::events($sql); $res =& $db->query($sql); return $res; }
function &getRecordsFilteredMorewithstype($start, $limit, $filter, $content, $stype, $order, $table) { global $db; $joinstr = astercrm::createSqlWithStype($filter, $content, $stype, 'astercrm_account'); $sql = "SELECT astercrm_account.*, groupname FROM astercrm_account LEFT JOIN astercrm_accountgroup ON astercrm_accountgroup.id = astercrm_account.groupid WHERE "; if ($_SESSION['curuser']['usertype'] == 'admin') { $sql .= " 1 "; } else { $sql .= " astercrm_account.groupid = " . $_SESSION['curuser']['groupid'] . " "; } if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); //去掉最左边的AND $sql .= " AND " . $joinstr . " " . " ORDER BY " . $order . " " . $_SESSION['ordering'] . " LIMIT {$start}, {$limit} {$ordering}"; } Customer::events($sql); $res =& $db->query($sql); return $res; }
function &getNumRowsMorewithstype($filter = null, $content = null, $stype, $table) { global $db; $joinstr = astercrm::createSqlWithStype($filter, $content, $stype); $sql = "SELECT COUNT(*) AS numRows FROM surveyresult LEFT JOIN customer ON customer.id = surveyresult.customerid LEFT JOIN contact ON contact.id = surveyresult.contactid LEFT JOIN survey ON survey.id = surveyresult.surveyid LEFT JOIN campaign ON campaign.id = surveyresult.campaignid WHERE "; if ($_SESSION['curuser']['usertype'] == 'admin') { $sql .= " "; } else { $sql .= " surveyresult.groupid = " . $_SESSION['curuser']['groupid'] . " AND "; } if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); //去掉最左边的AND $sql .= " " . $joinstr; } else { $sql .= " 1"; } Customer::events($sql); $res =& $db->getOne($sql); // print $sql; // print "\n"; // print $res; // exit; return $res; }
function deleteToHistoryFromSearch($searchContent, $searchField, $searchType = "", $table) { global $db, $config; if (empty($_SESSION['curuser']['usertype'])) { return; } $joinstr = astercrm::createSqlWithStype($searchField, $searchContent, $searchType, $table); if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); $history_sql = "INSERT INTO " . $table . "_history SELECT * FROM " . $table . " WHERE " . $joinstr; $sql = 'DELETE FROM ' . $table . ' WHERE ' . $joinstr; } else { if ($_SESSION['curuser']['usertype'] == 'admin') { $sql = 'TRUNCATE ' . $table; $history_sql = "INSERT INTO " . $table . "_history SELECT * FROM " . $table . " "; } else { $sql = "DELETE FROM " . $table . " WHERE " . $table . ".groupid = '" . $_SESSION['curuser']['groupid'] . "'"; $history_sql = "INSERT INTO " . $table . "_history SELECT * FROM " . $table . " WHERE " . $table . ".groupid = '" . $_SESSION['curuser']['groupid'] . "' "; } } Customer::events($history_sql); $result =& $db->query($history_sql); if ($result) { Customer::events($sql); $res =& $db->query($sql); return $res; } else { return false; } }
function &getNumRowsMorewithstype($filter, $content, $stype, $table) { global $db; $joinstr = astercrm::createSqlWithStype($filter, $content, $stype, "campaign"); $sql = "SELECT COUNT(*) FROM campaignresult LEFT JOIN astercrm_accountgroup ON astercrm_accountgroup.groupid = campaignresult.groupid LEFT JOIN campaign ON campaign.id = campaignresult.campaignid LEFT JOIN campaignresult AS presult ON presult.id = campaignresult.parentid WHERE "; if ($_SESSION['curuser']['usertype'] == 'admin') { $sql .= " "; } else { $sql .= " campaignresult.groupid = " . $_SESSION['curuser']['groupid'] . " AND "; } if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); //去掉最左边的AND $sql .= " " . $joinstr; } else { $sql .= " 1"; } Customer::events($sql); $res =& $db->getOne($sql); return $res; }
function &getNumRowsMorewithstype($filter, $content, $stype, $table) { global $db; $joinstr = astercrm::createSqlWithStype($filter, $content, $stype, "worktimepackages"); $sql = "SELECT COUNT(*) FROM worktimepackages LEFT JOIN astercrm_accountgroup ON astercrm_accountgroup.id = worktimepackages.groupid WHERE "; if ($_SESSION['curuser']['usertype'] == 'admin') { $sql .= " "; } else { $sql .= " worktimepackages.groupid = " . $_SESSION['curuser']['groupid'] . " AND "; } if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); //去掉最左边的AND $sql .= " " . $joinstr; } else { $sql .= " 1"; } Customer::events($sql); $res =& $db->getOne($sql); return $res; }
function specialGetSql($searchContent, $searchField, $searchType = array(), $table, $fields = '', $leftjoins = array()) { global $db; $joinstr = astercrm::createSqlWithStype($searchField, $searchContent, $searchType, $table); $fieldstr = ''; if (is_array($fields)) { foreach ($fields as $field => $alias) { if (!is_numeric($field)) { $fieldstr .= " " . $field . " AS " . $alias . ","; } else { $fieldstr .= " " . $alias . ","; } } } $leftStr = ''; if (!empty($leftjoins)) { foreach ($leftjoins as $model => $param) { // the keys of array $leftjoins are the table which need to left join $leftStr .= 'LEFT JOIN ' . $model . ' ON ' . $param[0] . '=' . $param[1] . ' '; } } if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); if ($fieldstr != '') { $fieldstr = rtrim($fieldstr, ','); $query = 'SELECT ' . $fieldstr . ' FROM ' . $table . ' ' . $leftStr . ' WHERE ' . $joinstr; } else { $query = 'SELECT * FROM ' . $table . ' ' . $leftStr . ' WHERE ' . $joinstr; } } else { if ($fieldstr != '') { $fieldstr = rtrim($fieldstr, ','); $query = 'SELECT ' . $fieldstr . ' FROM ' . $table . ' ' . $leftStr . ' '; } else { $query = 'SELECT * FROM ' . $table . ''; } } return $query; }
function &getNumRowsMorewithstype($filter, $content, $stype, $table) { global $db; $joinstr = astercrm::createSqlWithStype($filter, $content, $stype); $sql = "SELECT COUNT(*) FROM contact LEFT JOIN customer ON customer.id = contact.customerid LEFT JOIN note on note.contactid = contact.id WHERE "; if ($_SESSION['curuser']['usertype'] == 'admin') { $sql .= " "; } else { $sql .= " contact.groupid = " . $_SESSION['curuser']['groupid'] . " AND "; } if ($joinstr != '') { $joinstr = ltrim($joinstr, 'AND'); //去掉最左边的AND $sql .= " " . $joinstr; } else { $sql .= " 1"; } Customer::events($sql); $res =& $db->getOne($sql); return $res; }
function &getRecordsFilteredMorewithstype($start, $limit, $filter, $content, $stype = null, $order, $table, $ordering = "") { global $db; $joinstr = astercrm::createSqlWithStype($filter, $content, $stype); if ($_SESSION['curuser']['usertype'] == 'admin') { $sql = "SELECT * FROM credithistory WHERE "; } elseif ($_SESSION['curuser']['usertype'] == 'groupadmin') { $sql = "SELECT * FROM credithistory WHERE groupid = '" . $_SESSION['curuser']['groupid'] . "'"; } elseif ($_SESSION['curuser']['usertype'] == 'reseller') { $sql = "SELECT * FROM credithistory WHERE resellerid = '" . $_SESSION['curuser']['resellerid'] . "'"; } elseif ($_SESSION['curuser']['usertype'] == 'clid') { $sql = "SELECT * FROM credithistory WHERE clidid = '" . $_SESSION['curuser']['clidid'] . "'"; } if ($joinstr != '') { if ($_SESSION['curuser']['usertype'] == 'admin') { $joinstr = ltrim($joinstr, 'AND'); //去掉最左边的AND $sql .= $joinstr . " "; } else { $sql .= $joinstr . " "; } } $sql .= " ORDER BY " . $order . " " . $_SESSION['ordering'] . " LIMIT {$start}, {$limit} {$ordering}"; Customer::events($sql); $res =& $db->query($sql); return $res; }