public function autoExecutePayments()
{
//Status 2 orders are confirmed orders ready to collect payment.
//This status change automatically whenever the payment is approved by the customer
$db = new clsDBdbConnection();
$sql = "select id from orders where status_id = 2";
$db->query($sql);
$payments = new Payments();
while ($db->next_record()) {
$orderid = (int) $db->f("id");
$payments->executePayment($orderid);
}
$db->close();
return true;
}
public function getRouteCenterByGuid($guid)
{
$result = array("status" => false, "coordinates" => "", "message" => "", "zoom" => 11);
if (strlen($guid) > 0) {
$db = new clsDBdbConnection();
$guid = $db->esc($guid);
$sql = "select ST_AsGeoJSON( ST_Centroid(coordinates) ) as center, ST_IsValid(coordinates) as valid from tap_routes where guid = '{$guid}' ";
$db->query($sql);
$db->next_record();
$validCoordinates = $db->f("valid");
if ($validCoordinates) {
$center = $db->f("center");
$center = json_decode($center, JSON_NUMERIC_CHECK);
$lat = $center["coordinates"][1];
$lng = $center["coordinates"][0];
$center = "{$lat} , {$lng}";
$result["status"] = true;
$result["coordinates"] = $center;
$result["message"] = "Command Executed Successfully";
} else {
$result["status"] = false;
$result["message"] = "Not Valid Coordinates";
}
$db->close();
return $result;
} else {
$result["status"] = false;
$result["message"] = "Invalid Route GUID";
return $result;
}
}
function licensing_customerscontent_licensing_id_license_status_BeforeShow(&$sender)
{
$licensing_customerscontent_licensing_id_license_status_BeforeShow = true;
$Component =& $sender;
$Container =& CCGetParentContainer($sender);
global $licensing_customerscontent;
//Compatibility
//End licensing_customerscontent_licensing_id_license_status_BeforeShow
//Custom Code @207-2A29BDB7
// -------------------------
// Write your own code here.
$idstatus = (int) $sender->GetValue();
if ($idstatus > 0) {
$db = new clsDBdbConnection();
$sql = "select status_name,icon_name,css_color from alm_license_status where id = {$idstatus}";
$db->query($sql);
$db->next_record();
$licensing_customerscontent->licensing->lblicense_status_css->SetValue($db->f("css_color"));
$sender->SetValue($db->f("status_name"));
$db->close();
}
// -------------------------
//End Custom Code
//Close licensing_customerscontent_licensing_id_license_status_BeforeShow @205-C8AA0A17
return $licensing_customerscontent_licensing_id_license_status_BeforeShow;
}
private function saveCustomerImage($params = array())
{
$image_name = $params["image_name"];
$guid = $params["guid"];
if (strlen($image_name) > 0 && strlen($guid) > 0) {
//Update recently uploaded image
$db = new clsDBdbConnection();
$sql = "update alm_users set photo = '{$image_name}' where guid = '{$guid}' ";
$db->query($sql);
$db->close();
return true;
} else {
return false;
}
}
public function setExpireOrderTickets($orderid, $userid)
{
$orderid = (int) $orderid;
if ($orderid > 0) {
$db = new clsDBdbConnection();
$sql = "update ppconsole_tickets set status_id = 3, modified_iduser = {$userid} where assignedto_orderid = {$orderid}";
$db->query($sql);
$db->close();
return true;
} else {
return false;
}
}
public function deleteHolidaysByGuid($params = array())
{
$result = array("status" => false, "message" => "");
$guid = $params["guid"];
if (strlen($guid) > 0) {
//This operation will also delete contacts associated to the customer
$db = new clsDBdbConnection();
$guid = $db->esc($guid);
$sql = "delete from alm_customers_contacts_holidays where guid = '{$guid}'";
$db->query($sql);
$db->close();
$result["status"] = true;
$result["message"] = "Command executed successfully.";
return $result;
} else {
$result["status"] = false;
$result["message"] = "Invalid GUID";
return $result;
}
}
function CCLoginUser($login, $password)
{
CCLogoutUser();
$db = new clsDBdbConnection();
$SQL = "SELECT id, group_id, password FROM alm_users WHERE username="******" AND password="******"UserID", $db->f("id"));
CCSetSession("UserLogin", $login);
CCSetSession("GroupID", $db->f("group_id"));
}
return $Result;
}
private function setCheckinEmailOrder($orderid, $userid, $venue_id)
{
if ($orderid > 0 && $userid > 0 && $venue_id > 0) {
$db = new clsDBdbConnection();
$guid = Options::getUUIDv6();
$sql = "insert into ppconsole_ticketscheckin(guid,order_id,checkin_userid,checkin_venueid,created_iduser)\n values('{$guid}',{$orderid},{$userid},{$venue_id},{$userid}) ";
$db->query($sql);
$db->close();
return true;
} else {
return false;
}
}
function companies_list_alm_customers_ds_BeforeExecuteSelect(&$sender)
{
$companies_list_alm_customers_ds_BeforeExecuteSelect = true;
$Component =& $sender;
$Container =& CCGetParentContainer($sender);
global $companies_list;
//Compatibility
//End companies_list_alm_customers_ds_BeforeExecuteSelect
//Custom Code @26-2A29BDB7
// -------------------------
// Write your own code here.
//Special search for contacts since its stored on a separate table
$where = trim($companies_list->alm_customers->ds->Where);
$search = trim(CCGetFromGet("s_search", ""));
if (strlen($search) > 0) {
$sql = "select customer_id from alm_customers_contacts where contact like '%{$search}%' ";
$db = new clsDBdbConnection();
$db->query($sql);
$id = "0";
while ($db->next_record()) {
$id .= $db->f("customer_id") . ",";
}
$id = trim($id, ",");
$db->close();
if (strlen($where) > 0) {
//The parenthesis is needed to properly filter the search and users own customers
$where = "( {$where} ";
$where .= " or id in ({$id}) ) ";
} else {
$where .= " id in ({$id}) ";
}
$companies_list->alm_customers->ds->Where = $where;
}
//Filtering grid content for users group
if (CCGetGroupID() == 1) {
$userid = CCGetUserID();
if (strlen($where) > 0) {
$where .= " and assigned_to = {$userid} ";
} else {
$where .= " assigned_to = {$userid} ";
}
$companies_list->alm_customers->ds->Where = $where;
}
// -------------------------
//End Custom Code
//Close companies_list_alm_customers_ds_BeforeExecuteSelect @5-F8608223
return $companies_list_alm_customers_ds_BeforeExecuteSelect;
}
public function getProductSalesPoints($params = array())
{
$result = array("status" => false, "message" => "", "markers" => array(), "rows" => array(), "coordinateSet" => array());
$db = new clsDBdbConnection();
$collection = array("type" => "FeatureCollection", "features" => array());
$rows = array();
//Date range filter settings
$daterange = trim($params["daterange"]);
$daterange_array = explode(",", $daterange);
$fromDate = trim($daterange_array[0]);
$toDate = trim($daterange_array[1]);
$daterange_filter = "";
if (strlen($fromDate) > 0 && strlen($toDate) > 0) {
$daterange_filter = " and a.sale_date between '{$fromDate}' and '{$toDate}' ";
}
$post_products = $params["products"];
$post_products = implode("','", $post_products);
if (strlen($post_products) > 0) {
$post_products = "'" . $post_products . "'";
$post_products = " and a.product_sku_or_code in ({$post_products}) ";
}
$sql = "select a.product_sku_or_code,sum(a.sale_qty) as sale_qty,\n ST_AsGeoJSON(b.coordinates) as coordinates,c.products_name\n from tap_product_sales a, customers b, tap_products c\n where b.id = a.customer_id {$daterange_filter}\n and c.products_sku = a.product_sku_or_code\n {$post_products}\n group by a.product_sku_or_code,b.coordinates,c.products_name\n order by a.product_sku_or_code ";
$db->query($sql);
$lastProductsSku = "";
//Used to assign unique random colors to sellers markers
while ($db->next_record()) {
$curProduct = array();
$products_sku = $db->f("product_sku_or_code");
$sale_qty = $db->f("sale_qty");
$geometry = $db->f("coordinates");
//No need decoding, it is being decoded before creating the circle
$products_name = $db->f("products_name");
//Generating unique colors for markers
if ($lastProductsSku == "") {
$lastProductsSku = $products_sku;
$style = Options::getRandomColor();
//Adding only sellers for lisbox
$curProduct["coordinates"] = $geometry;
$curProduct["products_sku"] = $products_sku;
$curProduct["products_name"] = $products_name;
$curProduct["sale_qty"] = $sale_qty;
$curProduct["style"] = $style;
}
if ($products_sku != $lastProductsSku) {
$lastProductsSku = $products_sku;
$style = Options::getRandomColor();
//Adding only sellers for lisbox
$curProduct["coordinates"] = $geometry;
$curProduct["products_sku"] = $products_sku;
$curProduct["products_name"] = $products_name;
$curProduct["sale_qty"] = $sale_qty;
$curProduct["style"] = $style;
} else {
$lastProductsSku = $products_sku;
//$style = Options::getRandomColor();
//Adding only sellers for lisbox
$curProduct["coordinates"] = $geometry;
$curProduct["products_sku"] = $products_sku;
$curProduct["products_name"] = $products_name;
$curProduct["sale_qty"] = $sale_qty;
$curProduct["style"] = $style;
}
/*
$paramsFeature = array();
$paramsFeature["style"] = $style;
$paramsFeature["products_name"] = $products_name;
$paramsFeature["products_sku"] = $products_sku;
$paramsFeature["title"] = ""; //The descripcion has title included
$paramsFeature["icon_content"] = "";
$paramsFeature["description"] = $products_name;
$feature = $this->buildFeature($geometry,$paramsFeature);
array_push($collection["features"],$feature);
*/
$rows[] = $curProduct;
}
//$result["markers"] = json_encode($collection,JSON_NUMERIC_CHECK);
$result["rows"] = $rows;
$db->close();
return $result;
}
function contacts_maintcontent_alm_customers_contacts_BeforeShow(&$sender)
{
$contacts_maintcontent_alm_customers_contacts_BeforeShow = true;
$Component =& $sender;
$Container =& CCGetParentContainer($sender);
global $contacts_maintcontent;
//Compatibility
//End contacts_maintcontent_alm_customers_contacts_BeforeShow
//Custom Code @37-2A29BDB7
// -------------------------
// Write your own code here.
global $Tpl;
$guid = CCGetFromGet("guid", "");
$db = new clsDBdbConnection();
$db2 = new clsDBdbConnection();
$db3 = new clsDBdbConnection();
$contact_hobbies = explode(",", trim($contacts_maintcontent->alm_customers_contacts->hidhobbies->GetValue(), ","));
$sql = "select id,hobbies from alm_customers_contacts_hobbies";
$db->query($sql);
while ($db->next_record()) {
$hobbie_id = $db->f("id");
$Tpl->setvar("hobbie_value", $hobbie_id);
$Tpl->setvar("hobbie_title", $db->f("hobbies"));
if (in_array($db->f("id"), $contact_hobbies)) {
$Tpl->setvar("hobbie_checked", "checked");
} else {
$Tpl->setvar("hobbie_checked", "");
}
$parentPath = $Tpl->block_path;
$Tpl->block_path = $Tpl->block_path . "/hobbies_list";
$Tpl->SetBlockVar("subhobbies_list", "");
$sql2 = "select id,subhobbi from alm_customers_contacts_subhobbies where hobbie_id = {$hobbie_id}";
$db2->query($sql2);
$contact_guid = $contacts_maintcontent->alm_customers_contacts->hidguid->GetValue();
$contact_id = CCDLookup("id", "alm_customers_contacts", "guid = '{$contact_guid}'", $db3);
$subhobbie = CCDLookup("subhobbies", "alm_customers_contacts_subhobbies_details", "contact_id = {$contact_id} and hobbie_id = {$hobbie_id}", $db3);
$subhobbie = explode(",", $subhobbie);
while ($db2->next_record()) {
$subhubbie_id = $db2->f("id");
if (in_array($subhubbie_id, $subhobbie)) {
$Tpl->setvar("subhobbie_checked", "checked");
} else {
$Tpl->setvar("subhobbie_checked", "");
}
$Tpl->setvar("parent_hobbie", $hobbie_id);
$Tpl->setvar("subhobbie_value", $subhubbie_id);
$Tpl->setvar("subhobbie_title", $db2->f("subhobbi"));
$Tpl->Parse("subhobbies_list", true);
}
$table_detail = $Tpl->GetVar("subhobbies_list");
$Tpl->block_path = $parentPath;
$Tpl->SetBlockVar("subhobbies_list", $table_detail);
$Tpl->Parse("hobbies_list", true);
}
$db3->close();
$db2->close();
$db->close();
// -------------------------
//End Custom Code
//Close contacts_maintcontent_alm_customers_contacts_BeforeShow @2-DACA5085
return $contacts_maintcontent_alm_customers_contacts_BeforeShow;
}
public function getProvinceStats($province_id)
{
$result = array("status" => false, "message" => "", "points" => 0, "population" => 0, "gender_male" => 0, "gender_female" => 0);
$province_id = (int) $province_id;
if ($province_id > 0) {
$db = new clsDBdbConnection();
$sql = "select points,population,gender_male,gender_female from tap_dr_provinces_stats where id = {$province_id} ";
$db->query($sql);
$db->next_record();
$result["points"] = number_format($db->f("points"));
$result["population"] = number_format($db->f("population"));
$result["gender_male"] = number_format($db->f("gender_male"));
$result["gender_female"] = number_format($db->f("gender_female"));
$db->close();
$result["status"] = true;
$result["message"] = "Message Executed Successfully";
return $result;
} else {
$result["status"] = false;
$result["message"] = "Invalid Province ID";
return $result;
}
/*This is the query to create the update daemon for the table tap_dr_provinces_points_stats
*
*
* select b.id,b.name as province,count(a.coordinates) as points
from customers a,tap_dr_provinces b
where
a.coordinates is not null
and
b.coordinates && a.coordinates
and
ST_Contains ( b.coordinates , a.coordinates)
group by b.id,b.name
*
*
* MySQL Query for population and gender stats based on jce_padron and jce_provinci
* select b.cod_provin as id, b.descripcio as name,count(a.dni_numtrans) as population,
(select count(a.sexo) from jce_padron a where a.cod_provin = b.cod_provin and sexo = 'M') as gender_male,
(select count(a.sexo) from jce_padron a where a.cod_provin = b.cod_provin and sexo = 'F') as gender_female
from jce_padron a,jce_provinci b
where a.cod_provin = b.cod_provin
group by b.cod_provin,b.descripcio
*
* */
}
private function saveExecutedPayment($payment_result, $orderid, $userid)
{
if (strlen($payment_result->getId()) > 0 && $orderid > 0) {
$db = new clsDBdbConnection();
$guid = Options::getUUIDv6();
$orderid = (int) $orderid;
//Payment info
$payment_state = $payment_result->getState();
if ($payment_state == "approved") {
$payment_id = $payment_result->getId();
$payment_createdon = $payment_result->getCreateTime();
$payment_updatedon = $payment_result->getUpdateTime();
$payment_intent = $payment_result->getIntent();
//Payer info
$payer_id = $payment_result->getPayer()->getPayerInfo()->getPayerId();
$payer_email = $payment_result->getPayer()->getPayerInfo()->getEmail();
$payer_firstname = $payment_result->getPayer()->getPayerInfo()->getFirstName();
$payer_lastname = $payment_result->getPayer()->getPayerInfo()->getLastName();
$payer_ship_line1 = $payment_result->getPayer()->getPayerInfo()->getShippingAddress()->getLine1();
//Line2 of shipping address returns an undefined index error when there is not 2nd line
//$payer_ship_line2 = $payment_result->getPayer()->getPayerInfo()->getShippingAddress()->getLine2();
$payer_ship_line2 = null;
$payer_ship_city = $payment_result->getPayer()->getPayerInfo()->getShippingAddress()->getCity();
$payer_ship_state = $payment_result->getPayer()->getPayerInfo()->getShippingAddress()->getState();
$payer_ship_postalcode = $payment_result->getPayer()->getPayerInfo()->getShippingAddress()->getPostalCode();
$payer_ship_countrycode = $payment_result->getPayer()->getPayerInfo()->getShippingAddress()->getCountryCode();
//Its possible to have multiple sale transactions,
//To be implemented in the future for shopping cart
$transactions = $payment_result->getTransactions();
$relatedresources = $transactions[0]->getRelatedResources();
$sales = $relatedresources[0]->getSale();
//Sale Transaction (completed payment) info
$sale_id = $sales->getId();
$sale_createdon = $sales->getCreateTime();
$sale_updatedon = $sales->getUpdateTime();
$sale_state = $sales->getState();
$sale_parentpayment = $sales->getParentPayment();
$sale_total = $sales->getAmount()->getTotal();
$sale_currency = $sales->getAmount()->getCurrency();
$sql = "insert into paypal_payments (guid,order_id,payment_id,payment_createdon,payment_updatedon,\n payment_state,payment_intent,payer_id,payer_email,payer_firstname,payer_lastname,payer_ship_line1,\n payer_ship_line2,payer_ship_city,payer_ship_state,payer_ship_postalcode,payer_ship_countrycode,\n sale_id,sale_createdon,sale_updatedon,sale_state,sale_total,sale_currency,sale_parentpayment,created_iduser)\n values ('{$guid}','{$orderid}','{$payment_id}','{$payment_createdon}','{$payment_updatedon}',\n '{$payment_state}','{$payment_intent}','{$payer_id}','{$payer_email}','{$payer_firstname}',\n '{$payer_lastname}','{$payer_ship_line1}','{$payer_ship_line2}','{$payer_ship_city}','{$payer_ship_state}',\n '{$payer_ship_postalcode}','{$payer_ship_countrycode}','{$sale_id}','{$sale_createdon}','{$sale_updatedon}',\n '{$sale_state}','{$sale_total}','{$sale_currency}','{$sale_parentpayment}',{$userid}) ";
$db->query($sql);
$db->next_record();
//Update order status after payment confirmation
$sql = "update orders set status_id = 3,paypal_state = '{$sale_state}', modified_iduser = {$userid} where id = {$orderid}";
$db->query($sql);
$db->close();
return true;
} else {
return false;
}
} else {
return false;
}
}
public function bulkRenew($params = array())
{
$result = array("status" => false, "message" => "");
$grantNumber = $params["grant_number"];
$newGrantNumber = $params["newgrant_number"];
$expedDate = $params["expedition_date"];
$expirDate = $params["expiration_date"];
$userId = $params["user_id"];
if (strlen($grantNumber) > 0 && strlen($newGrantNumber) > 0 && strlen($expedDate) > 0 && strlen($expirDate) > 0) {
$db = new \clsDBdbConnection();
$db2 = new \clsDBdbConnection();
$fields_array = array("id", "guid", "id_suite", "id_product_type", "id_licensed_by", "id_license_type", "id_product_tag", "id_license_sector", "id_reseller", "id_customer", "id_product", "id_license_granttype", "msrp_price", "nodes", "id_license_status", "licensed_amount", "channel_sku", "grant_number", "registered_date", "serial_number", "currency");
$fields = implode(",", $fields_array);
$sql = "select {$fields} from alm_licensing where grant_number = '{$grantNumber}' ";
$db->query($sql);
$parentGuid = CCDLookUp("guid", "alm_licensing", "grant_number = '{$grantNumber}' and id_license_status = 2 limit 1", $db2);
while ($db->next_record()) {
$licenseType = $db->f("id_license_type");
$licenseStatus = $db->f("id_license_status");
if ($licenseStatus == 3) {
$guid = \uuid_create();
/**
* Only adds parent license guid to support licenses
*/
$newParentGuid = "";
if ($licenseType == 10 || $licenseType == 11 || $licenseType == 13) {
$newParentGuid = $parentGuid;
}
$nodes = $db->f('nodes');
$licensedAmount = $db->f('licensed_amount');
$channelSku = $db->f('channel_sku');
$msrpPrince = $db->f('msrp_price');
$registeredDate = $db->f('registered_date');
if (strlen($registeredDate) <= 0) {
$registeredDate = null;
}
$serialNumber = $db->f('serial_number');
$currency = $db->f('currency');
$expiredGuid = $db->f('guid');
$idSuite = (int) $db->f('id_suite');
$idProductType = (int) $db->f('id_product_type');
$idLicenseType = (int) $db->f('id_license_type');
$idProductTag = (int) $db->f('id_product_tag');
$idLicenseBy = (int) $db->f('id_licensed_by');
$idLicenseSector = (int) $db->f('id_license_sector');
$idReseller = (int) $db->f('id_reseller');
$idCustomer = (int) $db->f('id_customer');
$idLicenseStatus = 2;
$idProduct = (int) $db->f('id_product');
$idLicenseGrantType = (int) $db->f('id_license_granttype');
$sql2 = "insert into alm_licensing (id_suite,id_product_type,id_license_type,id_product_tag,id_licensed_by,id_license_sector,id_reseller,\n\t\t\t\t\tid_customer,id_license_status,id_product,id_license_granttype,guid,nodes,licensed_amount,channel_sku,msrp_price,grant_number,expedition_date,\n\t\t\t\t\texpiration_date,registered_date,serial_number,currency,created_iduser,expired_license_guid,parent_license_guid)\n\t\t\t\t\tvalues({$idSuite}, {$idProductType}, {$idLicenseType}, {$idProductTag}, {$idLicenseBy}, {$idLicenseSector}, {$idReseller}, {$idCustomer}, {$idLicenseStatus}, {$idProduct},\n\t\t\t\t\t{$idLicenseGrantType}, '{$guid}', '{$nodes}', '{$licensedAmount}', '{$channelSku}', '{$msrpPrince}', '{$newGrantNumber}','{$expedDate}', '{$expirDate}',\n\t\t\t\t '{$registeredDate}', '{$serialNumber}', '{$currency}', {$userId}, '{$expiredGuid}', '{$newParentGuid}')";
$db2->query($sql2);
$this->setLicenseArchivedByGuid(array("guid" => $expiredGuid));
} else {
//If not expired might be a perpetual, which we update its grant number to match the new grant
if ($licenseStatus == 2 && ($licenseType == 7 || $licenseType == 12)) {
$perpetualGuid = $db->f('guid');
$sql3 = "update alm_licensing set grant_number = '{$newGrantNumber}' where guid = '{$perpetualGuid}' ";
$db2->query($sql3);
}
}
}
$db2->close();
$db->close();
$result["status"] = true;
$result["message"] = "Bulk renewal executed successfully";
return $result;
} else {
$result["status"] = false;
$result["message"] = "Invalid fields";
return $result;
}
}
public function getFeaturedPackages()
{
$db = new clsDBdbConnection();
//status_id 2 = active, 3 = souldout
$sql = "select id,guid,title,title_summary,price,valid_to,status_id,details from packages\n where status_id in (2,3) and featured = 1 order by status_id";
$db->query($sql);
$packages = array();
while ($db->next_record()) {
$row = array();
$package_id = (int) $db->f("id");
$row["guid"] = $db->f("guid");
$row["title"] = $db->f("title");
$row["title_summary"] = $db->f("title_summary");
$row["price"] = $db->f("price");
$row["valid_to"] = $db->f("valid_to");
$row["status_id"] = (int) $db->f("status_id");
$row["details"] = nl2br($db->f("details"));
$row["package_images"] = $this->getImagesByPackageId($package_id);
$packages[] = $row;
}
$db->close();
return $packages;
}
public function getGroupDetail($group_name)
{
$db = new clsDBdbConnection();
//Getting group details, the _ is s group extendable character
$group_name = $group_name . "_";
$sql = "select guid,variable,value,style from options where variable like '{$group_name}%' ";
$db->query($sql);
$group_options = array();
while ($db->next_record()) {
$group_details = array();
$group_details["guid"] = $db->f("guid");
$group_details["variable"] = $db->f("variable");
$group_details["value"] = $db->f("value");
$group_details["style"] = $db->f("style");
$group_options[] = $group_details;
}
$db->close();
return $group_options;
}
