[Summary view] [Print] [Text view]

   1  <?php
   2  /*********************************************************************************
   3  ** The contents of this file are subject to the vtiger CRM Public License Version 1.0
   4  * ("License"); You may not use this file except in compliance with the License
   5  * The Original Code is:  vtiger CRM Open Source
   6  * The Initial Developer of the Original Code is vtiger.
   7  * Portions created by vtiger are Copyright (C) vtiger.
   8  * All Rights Reserved.
   9  ********************************************************************************/
  10  ini_set("include_path", "../");
  11  
  12  require ('send_mail.php');
  13  require_once ('config.php');
  14  require_once ('include/utils/utils.php');
  15  
  16  // Email Setup
  17  global $adb;
  18  $emailresult = $adb->pquery("SELECT email1 from vtiger_users", array());
  19  $emailid = $adb->fetch_array($emailresult);
  20  $emailaddress = $emailid[0];
  21  $mailserveresult = $adb->pquery("SELECT server,server_username,server_password,smtp_auth FROM vtiger_systems where server_type = ?", array('email'));
  22  $mailrow = $adb->fetch_array($mailserveresult);
  23  $mailserver = $mailrow[0];
  24  $mailuname = $mailrow[1];
  25  $mailpwd = $mailrow[2];
  26  $smtp_auth = $mailrow[3];
  27  // End Email Setup
  28  
  29  
  30  //query the vtiger_notificationscheduler vtiger_table and get data for those notifications which are active
  31  $sql = "select active from vtiger_notificationscheduler where schedulednotificationid=1";
  32  $result = $adb->pquery($sql, array());
  33  
  34  $activevalue = $adb->fetch_array($result);
  35  
  36  if($activevalue[0] == 1)
  37  {
  38      //Delayed Tasks Notification
  39  
  40      //get all those activities where the status is not completed even after the passing of 24 hours
  41      $today = date("Ymd"); 
  42      $result = $adb->pquery("select vtiger_activity.status,vtiger_activity.activityid,subject,(vtiger_activity.date_start +1),vtiger_crmentity.smownerid from vtiger_activity inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_activity.activityid where vtiger_crmentity.deleted=0 and vtiger_activity.status <> 'Completed' and activitytype='Task' and ".$today." > (vtiger_activity.date_start+1)", array());
  43  
  44      while ($myrow = $adb->fetch_array($result))
  45      {
  46          $status=$myrow[0];
  47          $subject = (strlen($myrow[2]) > 30)?substr($myrow[2],0,27).'...':$myrow[2];
  48          $user_id = $myrow[4];
  49          if($user_id != '')
  50          {
  51              $user_res = $adb->pquery('select user_name from vtiger_users where id=?',array($user_id));
  52              $assigned_user = $adb->query_result($user_res,0,'user_name');
  53          }
  54          $mail_body = getTranslatedString('Dear_Admin_tasks_not_been_completed')." ".getTranslatedString('LBL_SUBJECT').": ".$subject."<br> ".getTranslatedString('LBL_ASSIGNED_TO').": ".$assigned_user."<br><br>".getTranslatedString('Task_sign');
  55           sendmail($emailaddress,$emailaddress,getTranslatedString('Task_Not_completed').': '.$subject,$mail_body,$mailserver,$mailuname,$mailpwd,"",$smtp_auth);
  56      }
  57  }
  58  
  59  //Big Deal Alert
  60  $sql = "select active from vtiger_notificationscheduler where schedulednotificationid=2";
  61  $result = $adb->pquery($sql, array());
  62  
  63  $activevalue = $adb->fetch_array($result);
  64  if($activevalue[0] == 1)
  65  {
  66      $result = $adb->pquery("SELECT sales_stage,amount,potentialid,potentialname FROM vtiger_potential inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_potential.potentialid where vtiger_crmentity.deleted=0 and sales_stage='Closed Won' and amount > 10000",array());
  67      while ($myrow = $adb->fetch_array($result))
  68      {
  69          $pot_id = $myrow['potentialid'];
  70          $pot_name = $myrow['potentialname'];
  71          $body_content = getTranslatedString('Dear_Team').getTranslatedString('Dear_Team_Time_to_Party')."<br><br>".getTranslatedString('Potential_Id')." ".$pot_id;
  72          $body_content .= getTranslatedString('Potential_Name')." ".$pot_name."<br><br>";
  73          sendmail($emailaddress,$emailaddress,getTranslatedString('Big_Deal_Closed_Successfully'),$body_content,$mailserver,$mailuname,$mailpwd,"",$smtp_auth);
  74      }
  75  }
  76  //Pending tickets
  77  $sql = "select active from vtiger_notificationscheduler where schedulednotificationid=3";
  78  $result = $adb->pquery($sql, array());
  79  
  80  $activevalue = $adb->fetch_array($result);
  81  if($activevalue[0] == 1)
  82  {
  83      $result = $adb->pquery("SELECT vtiger_troubletickets.status,ticketid FROM vtiger_troubletickets INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=vtiger_troubletickets.ticketid WHERE vtiger_crmentity.deleted='0' AND vtiger_troubletickets.status <> 'Completed' AND vtiger_troubletickets.status <> 'Closed'", array());
  84  
  85      while ($myrow = $adb->fetch_array($result))
  86      {
  87          $ticketid = $myrow['ticketid'];
  88          sendmail($emailaddress,$emailaddress,getTranslatedString('Pending_Ticket_notification'),getTranslatedString('Kind_Attention').$ticketid .getTranslatedString('Thank_You_HelpDesk'),$mailserver,$mailuname,$mailpwd,"",$smtp_auth);
  89      }
  90  }
  91  
  92  //Too many tickets related to a particular vtiger_account/company causing concern
  93  $sql = "select active from vtiger_notificationscheduler where schedulednotificationid=4";
  94  $result = $adb->pquery($sql, array());
  95  
  96  $activevalue = $adb->fetch_array($result);
  97  if($activevalue[0] == 1)
  98  {
  99      $result = $adb->pquery("SELECT count(*) as count FROM vtiger_troubletickets INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid=vtiger_troubletickets.ticketid WHERE vtiger_crmentity.deleted='0' AND vtiger_troubletickets.status <> 'Completed' AND vtiger_troubletickets.status <> 'Closed'", array());
 100  $count = $adb->query_result($result,0,'count');
 101  //changes made to get too many tickets notification only when tickets count is greater than or equal to 5
 102      if($count >= 5)
 103      {
 104          sendmail($emailaddress,$emailaddress,getTranslatedString('Too_many_pending_tickets'),getTranslatedString('Dear_Admin_too_ many_tickets_pending'),$mailserver,$mailuname,$mailpwd,"",$smtp_auth);
 105      }
 106  }
 107  
 108  //Support Starting
 109  $sql = "select active from vtiger_notificationscheduler where schedulednotificationid=5";
 110  $result = $adb->pquery($sql, array());
 111  
 112  $activevalue = $adb->fetch_array($result);
 113  if($activevalue[0] == 1)
 114  {
 115      $result = $adb->pquery("SELECT vtiger_products.productname FROM vtiger_products inner join vtiger_crmentity on vtiger_products.productid = vtiger_crmentity.crmid where vtiger_crmentity.deleted=0 and start_date like ?", array(date('Y-m-d'). "%"));
 116      while ($myrow = $adb->fetch_array($result))
 117      {
 118          $productname=$myrow[0];
 119          sendmail($emailaddress,$emailaddress,getTranslatedString('Support_starting'),getTranslatedString('Hello_Support').$productname ."\n ".getTranslatedString('Congratulations'),$mailserver,$mailuname,$mailpwd,"",$smtp_auth);
 120      }
 121  }
 122  
 123  //Support ending
 124  $sql = "select active from vtiger_notificationscheduler where schedulednotificationid=6";
 125  $result = $adb->pquery($sql, array());
 126  
 127  $activevalue = $adb->fetch_array($result);
 128  if($activevalue[0] == 1)
 129  {
 130      $result = $adb->pquery("SELECT vtiger_products.productname from vtiger_products inner join vtiger_crmentity on vtiger_products.productid = vtiger_crmentity.crmid where vtiger_crmentity.deleted=0 and expiry_date like ?", array(date('Y-m-d') ."%"));
 131      while ($myrow = $adb->fetch_array($result))
 132      {
 133          $productname=$myrow[0];
 134          sendmail($emailaddress,$emailaddress,getTranslatedString('Support_Ending_Subject'),getTranslatedString('Support_Ending_Content').$productname.getTranslatedString('kindly_renew'),$mailserver,$mailuname,$mailpwd,"",$smtp_auth);
 135      }
 136  }
 137  
 138  ?>