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;
}
}
public function uploadUserPhoto($file, $params = array())
{
if (!empty($file) && strlen($params["guid"]) > 0) {
$db = new clsDBdbConnection();
$options = Options::getConsoleOptions();
$uploadTo = $options["console_users_url"];
$tmpFile = $file["file"]["tmp_name"];
$fileName = $file["file"]["name"];
$targetPath = dirname(__FILE__) . "/.." . $uploadTo;
//because dirname will be positioned in include folder
$fileExt = "." . pathinfo($fileName, PATHINFO_EXTENSION);
$targetFilename = Options::getUUIDv6() . $fileExt;
$targetFile = $targetPath . $targetFilename;
//Updating an existing image, which will replace the existing one for the new
$params["guid"] = $db->esc($params["guid"]);
$existing_photo = CCDLookUp("photo", "alm_users", "guid = '{$params["guid"]}'", $db);
$existing_photo = trim($existing_photo);
if (strlen($existing_photo) > 0) {
//Get the existing image name to re-use it and replace image on upload
$targetFilename = $existing_photo;
$targetFile = $targetPath . $targetFilename;
}
if (move_uploaded_file($tmpFile, $targetFile)) {
//File successfully uploaded
$params["image_name"] = $targetFilename;
//Saving db file reference
$this->saveCustomerImage($params);
$db->close();
return true;
} else {
$db->close();
return false;
}
/*
$log = new Logger('almlogs');
$log->pushHandler(new StreamHandler(MAIN_LOG, Logger::WARNING));
$log->addWarning($params["guid"].LOG_LINESEPARATOR);
$log->addWarning($params["title"].LOG_LINESEPARATOR);
*/
} else {
return false;
}
}
public function isOrderByIdEmailValid($orderid, $email)
{
if ($orderid > 0 && strlen($email) > 0) {
$db = new clsDBdbConnection();
$orderid = (int) $orderid;
$email = $db->esc($email);
if ($this->isOrderIdValid($orderid)) {
$customer_id = (int) CCDLookUp("customer_id", "orders", "id = {$orderid}", $db);
if ($customer_id > 0) {
$customer_email = trim(CCDLookUp("email", "customers", "id = {$customer_id}", $db));
if ($email == $customer_email) {
return true;
} else {
return false;
}
} else {
return false;
}
} else {
return false;
}
$db->close();
} 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;
}
}
public function addTicketsBulkByRange($sponsor_id, $price, $valid_date, $tickets_from, $tickets_to, $userid)
{
$result = array("status" => false, "message" => "", "duplicate_tickets" => "", "totaladded_tickets", "0");
$tickets_from = (int) $tickets_from;
$tickets_to = (int) $tickets_to;
if ($tickets_from > 0 && $tickets_to > 0 && $tickets_from < $tickets_to) {
$db = new clsDBdbConnection();
$sponsor_id = (int) $sponsor_id;
$price = (double) $price;
$ticketlist = array();
for ($ticket = $tickets_from; $ticket <= $tickets_to; $ticket++) {
$ticketlist[] = $ticket;
}
$duplicate_tickets = array();
$totaladded_tickets = 0;
foreach ($ticketlist as $theTicket) {
if (!$this->isTicketValidBySponsor($sponsor_id, $theTicket)) {
if ($sponsor_id > 0) {
$guid = Options::getUUIDv6();
$valid_date = $db->esc($valid_date);
$sql = "insert into ppconsole_tickets(guid,ticket_number,sponsor_id,price,valid_date,created_iduser)\n values ('{$guid}','{$theTicket}',{$sponsor_id},{$price},'{$valid_date}',{$userid}) ";
$db->query($sql);
//Counts how many tickets were added
$totaladded_tickets++;
}
} else {
$duplicate_tickets[] = $theTicket;
}
}
$db->close();
$result["status"] = true;
$result["message"] = "Tickets processed successfully";
$result["duplicate_tickets"] = $duplicate_tickets;
$result["totaladded_tickets"] = $totaladded_tickets;
return $result;
} else {
$result["status"] = false;
$result["message"] = "There are errors in ticket range.";
return $result;
}
}
public function exportZones($zone_guid, $customer_type_id, $route_id, $userid)
{
$result = array("status" => false, "message" => "", "excel" => "");
$userid = (int) $userid;
$route_id = (int) $route_id;
if (strlen($zone_guid) > 0 || strlen($customer_type_id) > 0 || $route_id > 0) {
$db = new clsDBdbConnection();
$zone_guid = $db->esc($zone_guid);
$zone_id = (int) CCDLookUp("id", "tap_zones", "guid = '{$zone_guid}'", $db);
if ($zone_id > 0 || strlen($customer_type_id) > 0 || $route_id > 0) {
$zone_name = CCDLookUp("description", "tap_zones", "id = {$zone_id}", $db);
$username = CCDLookUp("username", "tap_users", "id = {$userid}", $db);
$partner_id = (int) CCDLookUp("partner_id", "tap_users", "id = {$userid}", $db);
$partner_name = CCDLookUp("partner", "tap_partners", "id = {$partner_id}", $db);
$route_name = CCDLookUp("name", "tap_routes", "id = {$route_id}", $db);
$customer_type = "";
//Only look for customer type if chosen as search criteria
if (strlen($customer_type_id) > 0) {
//Get all customer_types
$sql_types = "select customer_type from customer_types where id in ({$customer_type_id}) ";
$db->query($sql_types);
while ($db->next_record()) {
$customer_type .= $db->f("customer_type") . ",";
}
}
$customer_type = rtrim($customer_type, ",");
$customer_type = ltrim($customer_type, ",");
//Build Excel Sheet
$sheet = new PHPExcel();
$sheet->getProperties()->setCreator("User : {$username}")->setTitle("ZONE: {$zone_name}");
$cont = 12;
$fieldrow = 12;
$sheet->setActiveSheetIndex(0)->setCellValue("A7", "CUSTOMERS LIST");
$sheet->setActiveSheetIndex(0)->setCellValue("A8", "ZONE: {$zone_name}");
$sheet->setActiveSheetIndex(0)->setCellValue("A9", "ROUTE: {$route_name}");
$sheet->setActiveSheetIndex(0)->setCellValue("A10", "CUSTOMER TYPES: {$customer_type}");
//Get customer map sql parameters
$customers = new Customers();
$sql = $customers->buildSearchSQLZoneCatRoute($zone_id, $customer_type_id, $route_id);
$db->query($sql);
while ($db->next_record()) {
//$sql = select a.id,a.guid,a.name,a.address,a.phone,a.lat,a.lng from customers a, tap_zones b
$customer_id = $db->f("id");
$customer_name = $db->f("name");
$customer_address = $db->f("address");
$customer_phone = $db->f("phone");
$customer_lat = $db->f("lat");
$customer_lng = $db->f("lng");
$customer_partner_id = $db->f("partner_id");
$customer_type = $db->f("customer_type");
$direct_customer = "YES";
if ($partner_id != $customer_partner_id) {
$direct_customer = "NO";
}
$sheet->setActiveSheetIndex(0)->setCellValue("A{$fieldrow}", "CUSTOMER ID");
$sheet->setActiveSheetIndex(0)->setCellValue("B{$fieldrow}", "CUSTOMER NAME");
$sheet->setActiveSheetIndex(0)->setCellValue("C{$fieldrow}", "CUSTOMER ADDRESS");
$sheet->setActiveSheetIndex(0)->setCellValue("D{$fieldrow}", "CUSTOMER PHONE");
$sheet->setActiveSheetIndex(0)->setCellValue("E{$fieldrow}", "CUSTOMER LAT");
$sheet->setActiveSheetIndex(0)->setCellValue("F{$fieldrow}", "CUSTOMER LNG");
$sheet->setActiveSheetIndex(0)->setCellValue("G{$fieldrow}", "DIRECT CUSTOMER");
$sheet->setActiveSheetIndex(0)->setCellValue("H{$fieldrow}", "CUSTOMER CATEGORY");
//Detail
$sheet->setActiveSheetIndex(0)->getCell("A{$cont}")->setValueExplicit($customer_id, PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->setActiveSheetIndex(0)->setCellValue("B{$cont}", $customer_name);
$sheet->setActiveSheetIndex(0)->setCellValue("C{$cont}", $customer_address);
$sheet->setActiveSheetIndex(0)->setCellValue("D{$cont}", $customer_phone);
$sheet->setActiveSheetIndex(0)->setCellValue("E{$cont}", $customer_lat);
$sheet->setActiveSheetIndex(0)->setCellValue("F{$cont}", $customer_lng);
$sheet->setActiveSheetIndex(0)->setCellValue("G{$cont}", $direct_customer);
$sheet->setActiveSheetIndex(0)->setCellValue("H{$cont}", $customer_type);
$cont++;
}
//Apply autosize to all the columns
for ($cont = "A"; $cont <= "K"; $cont++) {
$sheet->getActiveSheet()->getColumnDimension($cont)->setAutoSize(true);
}
$sheet->getActiveSheet()->setTitle("ZONE CONTENT LIST - {$partner_name}");
//$sheet->getActiveSheet()->getHeaderFooter()->setOddHeader("&L&G&C&HZONE CONTENT LIST - $partner_name");
//Setup the logo for the excel sheet
/*
$sheetImage = new PHPExcel_Worksheet_HeaderFooterDrawing();
$sheetImage->setName("logo");
$sheetImage->setDescription("logo");
$sheetImage->setPath("Styles/theme/mainlogo.png");
$sheetImage->setCoordinates('A1');
$sheetImage->setOffsetX(110);
$sheetImage->setHeight(70);
$sheetImage->setWorksheet($sheet->getActiveSheet());
*/
$sheet->setActiveSheetIndex(0);
$sheetWriter = PHPExcel_IOFactory::createWriter($sheet, 'Excel5');
//header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//header('Content-Disposition: attachment;filename="rifasinst_ganadores.xls"');
//header("Cache-Control: max-age=0");
//$sheetWriter->save("php://output");
$result["status"] = true;
$result["message"] = "Excel generated successfully";
$result["excel"] = $sheetWriter;
} else {
$result["status"] = false;
$result["message"] = "Invalid Zone ID";
}
$db->close();
return $result;
} else {
$result["status"] = false;
$result["message"] = "Invalid Zone GUID";
}
}
public static function setProducts($params = array())
{
$result = array("status" => false, "message" => "");
$o = $params["o"];
$userid = (int) $params["userid"];
if ($o == "execute") {
$db = new \clsDBdbConnection();
$db2 = new \clsDBdbConnection();
$sql = "select trim(layer2) as suite_name, trim(layer3_stub) as suite_code, trim(layer3_stub_description) as suite_description, trim(layer4) as offering,\n\t\t\ttrim(layer5) as description, TRIM(band) as band, TRIM( min) as min, TRIM( max) as max, TRIM( description) as short_description, TRIM( mcafee_partno) as mcafee_partno,\n\t\t\tTRIM( channel_sku) as channel_sku, TRIM( msrp_list_price ) as msrp_list_price, TRIM( assurance) as assurance, TRIM( packaged_weight) as packaged_weight,\n\t\t\tTRIM( packaged_size) as packaged_size, TRIM( tiered_pric) as tiered_pric, TRIM( pricing_tier) as pricing_tier, TRIM( reseller_authorization) as reseller_authorization,\n\t\t\tTRIM( tier1_authorization) as tier1_authorization, TRIM( reorder) as reorder, TRIM( export_restriction) as export_restriction, TRIM( fcs_date ) as fcs_date,\n\t\t\tTRIM( eos_date ) as eos_date, TRIM( commission_group_description) as commission_group_description,\n\t\t\tTRIM( ean_upc) as ean_upc, TRIM( effective_date) as effective_date, TRIM( currency) as currency, TRIM( price_list_description) as price_list_description\n\t\t\tFROM mcafee_pricelist";
$db->query($sql);
while ($db->next_record()) {
$suite_name = trim($db->f("suite_name"));
$suite_code = trim($db->f("suite_code"));
$suite_description = trim($db->f("suite_description"));
$offering_name = trim($db->f("offering"));
$id_suite = (int) CCDLookUp("id", "alm_product_suites", "suite_name = '{$suite_name}' and suite_code = '{$suite_code}' and suite_description = '{$suite_description}' ", $db2);
$id_offering = (int) CCDLookUp("id", "alm_product_offerings", "offer_name = '{$offering_name}' ", $db2);
$description = trim($db->f("description"));
$band = trim($db->f("band"));
$min = (int) $db->f("min");
$max = (int) $db->f("max");
$short_description = trim($db->f("short_description"));
$mcafee_partno = trim($db->f("mcafee_partno"));
$channel_sku = trim($db->f("channel_sku"));
$msrp_list_price = (double) $db->f("msrp_list_price");
$assurance = trim($db->f("assurance"));
$assurance = $assurance == "Yes" ? 1 : 0;
$packaged_weight = trim($db->f("packaged_weight"));
$packaged_size = $db->esc(trim($db->f("packaged_size")));
$tiered_pric = trim($db->f("tiered_pric"));
$tiered_pric = $tiered_pric == "Yes" ? 1 : 0;
$pricing_tier = trim($db->f("pricing_tier"));
$id_pricing_tier = (int) CCDLookUp("id", "alm_product_pricing_tier", "pricingtier_name = '{$pricing_tier}' ", $db2);
$reseller_authorization = trim($db->f("reseller_authorization"));
$reseller_authorization = $reseller_authorization == "Yes" ? 1 : 0;
$tier1_authorization = trim($db->f("tier1_authorization"));
$tier1_authorization = $tier1_authorization == "Yes" ? 1 : 0;
$reorder = trim($db->f("reorder"));
$reorder = $reorder == "Yes" ? 1 : 0;
$export_restriction = trim($db->f("export_restriction"));
$export_restriction = $export_restriction == "Yes" ? 1 : 0;
$fcs_date = trim($db->f("fcs_date"));
$eos_date = trim($db->f("eos_date"));
$commission_group_description = trim($db->f("commission_group_description"));
$ean_upc = trim($db->f("ean_upc"));
$effective_date = trim($db->f("effective_date"));
$currency = trim($db->f("currency"));
$price_list_description = trim($db->f("price_list_description"));
$guid = uuid_create();
$sql = "insert into alm_products (guid,id_suite,id_offering,id_pricing_tier,description,short_description,band,range_min,range_max,\n\t\t\t\tmanufacturer_partno,channel_sku,msrp_price,assurance,packaged_weight,packaged_size,tiered_pric,reseller_authorization,tier1_authorization,\n\t\t\t\treorder,export_restriction,fcs_date,eos_date,commission_group_description,barcode_ean_upc,effective_date,currency,price_list_description,created_iduser)\n\t\t\t\t\t\tvalues('{$guid}',{$id_suite},{$id_offering},{$id_pricing_tier},'{$description}','{$short_description}','{$band}',{$min},{$max},\n\t\t\t\t\t\t'{$mcafee_partno}','{$channel_sku}',{$msrp_list_price},{$assurance},'{$packaged_weight}','{$packaged_size}',{$tiered_pric},{$reseller_authorization},\n\t\t\t\t\t\t{$tier1_authorization},{$reorder},{$export_restriction},'{$fcs_date}','{$eos_date}','{$commission_group_description}','{$ean_upc}','{$effective_date}',\n\t\t\t\t\t\t'{$currency}','{$price_list_description}',{$userid})";
$db2->query($sql);
//Duplication may happen for Media Kit products on McAffee Price List
//print_r($db2->Errors);
}
$db2->close();
$db->close();
$result["status"] = true;
$result["message"] = "Operation Executed Successfully.";
return $result;
} else {
$result["status"] = false;
$result["message"] = "Invalid Operation ID";
return $result;
}
}
public function getPackageToOrderByGuid($guid)
{
$db = new clsDBdbConnection();
$guid = $db->esc($guid);
//status_id 2 = active
$sql = "select id,price from packages where status_id = 2 and guid = '{$guid}' ";
$db->query($sql);
$db->next_record();
$package = array();
$package["id"] = (int) $db->f("id");
$package["price"] = $db->f("price");
$db->close();
return $package;
}
