");
//   print_r($_SESSION['UserInfo']);
//   print_r($school_record);
//   echo("type: " . $_SESSION['UserInfo']['type'] . "\n\n");
//}
//
//if ($_SESSION['UserInfo']['type'] == 'SuperUser') {
//   echo("
");
//   print_r($_POST);
//   echo("\nschool_num: $school_num\n");
//   echo("
");
//}

$selected_schedule = intval($selected_schedule);
$school_num = $school_record['record_num'];


// echo("
selected_site: $selected_site
\n");

$PrimaryTable = "users";


//
// Define the default query to be used
//
$Parameters  = "";
$EmailField  = "email";
$Recipients = array("email","first_name","last_name","status","balance");
$Alignment  = array("left","left","left","center","right");

$uawhere = "ua.school_num = $school_num AND ";
$selected_school = $school_num;
if (isset($selected_site) && $selected_site != 'All') {
   $selected_school = $selected_site;
   $uawhere = "ua.school_num = $selected_school AND ";
}
if (isset($selected_site) && $selected_site == 'All') {
   $results = db_query("SELECT record_num FROM schools WHERE status = 'Linked' AND linked_site = $school_num");
   $lsites = "$school_num";
   if (db_num_rows($results) > 0) {
      while ($row = db_fetch_array($results)) {
         $lsites .= "," . $row['record_num'];
      }
   }
   $uawhere = "ua.school_num IN ($lsites) AND ";
}


// If only send based on orders in a selected schedule
if (isset($selected_schedule) && $selected_schedule > 0) {
   $schedule_school = db_getVal("SELECT school_num FROM hotlunch_schedules WHERE record_num = $selected_schedule");
   $uawhere = "ua.school_num = $schedule_school AND ";
}


// Define email category and description
$Category = getLanguageText('TEXT-PARENTS-AND-STAFF-BROADCAST');
$Description = getLanguageText('TEXT-BROADCAST-TO-PARENTS-AND-STAFF');

$Where = "";
$Having = "";

if (array_key_exists('sel',$_POST)) {
   if (is_array($_POST['sel'])) {
      $_POST['sel'] = join(",", $_POST['sel']);
   }
   $Where = " and u.record_num IN ({$_POST['sel']})";
   $Description .= " (custom recipient list)";
}
else {
   $customized = 'N';

   // A specific user type has been selected
   if (isset($selected_types) && is_array($selected_types) && count($selected_types) > 0) {
      $types = implode("','",$selected_types);
      $wtypes = "$types','System Administrator','Application Administrator','Application Volunteer','Schedule Volunteer','Administration Team','Schedule Team'," .
                "'Financial','Financial Team','Volunteer Administrator','Communications Administrator";
      $Where .= " and type in ('$wtypes')";

      if (strpos($Description, " where") === false) $Description .= " where";
      else $Description .= " and";
      $Description .= " type in ('$types')";
   }
   else {
      echo("
\n");
      $error_message = getLanguageText('TEXT-YOU-MUST-SELECT-AT-LEAST-ONE-USER-TYPE');
      include "form.messages.php";
      return;
   }

   // A user flag value has been set
   if ($user_flag) {
      $Where .= " and email_flag = '$user_flag'";

      if (strpos($Description, " where") === false) $Description .= " where";
      else $Description .= " and";
      $Description .= " user email flag = '$user_flag'";
   }

   // A user status value has been set
   if ($user_status) {
      $Where .= " and u.status = '$user_status'";

      if (strpos($Description, " where") === false) $Description .= " where";
      else $Description .= " and";
      $Description .= " user status = '$user_status'";
   }

   // A selected language is specified
   if ($selected_language != 'Any') {
      $Where .= " and u.language = '$selected_language'";

      if (strpos($Description, " where") === false) $Description .= " where";
      else $Description .= " and";
      $Description .= " language = '$selected_language'";
   }

   // A volunteer flag value has been set
   if ($volunteer_flag) {
      $Where .= " and available_to_volunteer = '$volunteer_flag'";

      if (strpos($Description, " where") === false) $Description .= " where";
      else $Description .= " and";
      $Description .= " volunteer flag = '$volunteer_flag'";
   }

   // If a set of volunteers for a lunch date is selected
   if (isset($volunteer_menu_num) && intval($volunteer_menu_num) > 0) {
      // Get set of volunteers on the selected date
      if (intval($volunteer_menu_num) > 0) $query = "SELECT * FROM hotlunch_volunteers WHERE school_num = $school_num AND menu_num = $volunteer_menu_num AND deleted != 'Y'";
      else $query = "SELECT * FROM hotlunch_volunteers WHERE school_num = $school_num AND deleted != 'Y'";

      $volunteers = db_query($query);
      if (db_num_rows($volunteers) > 0) {
         if (isset($volunteer_menu_num) && intval($volunteer_menu_num) > 0) {
            $mdate = db_getVal("SELECT menu_date FROM hotlunch_menu_dates WHERE school_num = $school_num and record_num = $volunteer_menu_num");
            $menu_date = date("D M j, Y",strtotime($mdate) + 60 * 60 * 10);
            $Description .= " registered volunteer on '$menu_date'";
         }
         else $Description .= " registered volunteer";

         $volunteer_users = "";
         while ($row = db_fetch_array($volunteers)) {
            if (strlen($volunteer_users) > 0) $volunteer_users .= ",";
            $volunteer_users .= $row['user_num'];
         }

         $Where .= " and u.record_num in ($volunteer_users)";

         if (strpos($Description, " where") === false) $Description .= " where";
         else $Description .= " and";
      }
   }

   // If a set of volunteers for a schedule is selected
   if (isset($volunteer_schedule_num) && intval($volunteer_schedule_num) > 0) {
      // Get set of volunteers in the selected schedule
      $volunteers = db_query("SELECT * FROM hotlunch_volunteers WHERE school_num = $school_num AND schedule_num = $volunteer_schedule_num AND deleted != 'Y'");
      if (db_num_rows($volunteers) > 0) {
         $school_num = $school_record['record_num'];
         $stitle = db_getVal("SELECT schedule_name FrOm hotlunch_schedules WHERE school_num = $school_num AND record_num = $volunteer_schedule_num");
         $volunteer_users = "";
         while ($row = db_fetch_array($volunteers)) {
            if (strlen($volunteer_users) > 0) $volunteer_users .= ",";
            $volunteer_users .= $row['user_num'];
         }

         $Where .= " and u.record_num in ($volunteer_users)";

         if (strpos($Description, " where") === false) $Description .= " where";
         else $Description .= " and";
         $Description .= " registered volunteer in '$stitle'";
      }
   }

   // Check for class parents only
   if (isset($class_parents_only) && $class_parents_only == 'Y') {
      $Where .= " and u.class_parent = 'Y'";

      if (strpos($Description, " where") === false) $Description .= " where";
      else $Description .= " and";
      $Description .= " class_parent = 'Y'";
   }

   // A date added has been defined
   if ($date_added) {
      $Where .= " and u.date_added = '$date_added'";

      if (strpos($Description, " where") === false) $Description .= " where";
      else $Description .= " and";
      $Description .= " date_added = '$date_added'";
   }

   // A single user id has been defined
   if ($selected_userid) {
      $Where .= " and u.record_num = '$selected_userid'";

      $query = "select userid from users where record_num = '$selected_userid'";
      $userid = db_getVal($query);

      if (strpos($Description, " where") === false) $Description .= " where";
      else $Description .= " and";
      $Description .= " userid = '$userid'";
      $customized = 'Y';
   }

   // A single student has been selected
   if ($selected_student) {
      $query = "select concat_ws(', ',last_name,first_name) as name,user_num " .
               "from people " .
               "where school_num = $base_school_num " .
               "and record_num = '$selected_student'";
      $results = db_query($query);
      $row = db_fetch_array($results);
      $name = $row['name'];
      $selected_user_num = $row['user_num'];

      $Where .= " and u.record_num = '$selected_user_num'";

      if (strpos($Description, " where") === false) $Description .= " where";
      else $Description .= " and";
      $Description .= " student = '$name'";
      $customized = 'Y';
   }

   // Student user defined data
   $uWhere = "";
   for ($f = 0; $f <= 10; $f++) {
      $ext = "";
      if ($f > 0) $ext = $f;
      $ufield = "user_defined_value$ext";

      if (isset($$ufield)) {
         if (strlen($uWhere)) $uWhere .= " AND ";

         $uWhere .= " user_defined_value$ext = '" . $$ufield . "'";

         if (strpos($Description, " where") === false) $Description .= " where";
         else $Description .= " and";
         $text = $school_record["user_defined_title$ext"];
         $Description .= " $text = '" . $$ufield . "'";
      }
   }

   if (strlen($uWhere)) $Where .= " u.record_num IN (SELECT user_num FROM people WHERE school_num = $base_school_num AND $uWhere)";


   // Users by current order source
   if ($selected_order_source) {
      $Where .= " and source = '$selected_order_source'";

      if (strpos($Description, " where") === false) $Description .= " where";
      else $Description .= " and";
      $Description .= " order source = '$selected_order_source'";
      $customized = 'Y';
   }

   // A set of users with a current balance of...
   if ($selected_balance) {
      if (strpos($Description, " where") === false) $Description .= " where";
      else $Description .= " and";

      switch($selected_balance) {
         case 'Greater Than Zero':
            $Having = " having sum(ua.transaction_amount) > 0";
            $Description .= " account_balance greater than zero";
            break;

         case 'Greater Than Specified Amount':
            $specified_amount = round($specified_amount,2);
            $Having = " having sum(ua.transaction_amount) > $specified_amount";
            $Description .= " account_balance greater than $currencySymbol" . number_format($specified_amount,2);
            break;

         case 'Non-Zero':
            $Having = " having sum(ua.transaction_amount) <> 0";
            $Description .= " account_balance is not zero";
            break;

         case 'Zero':
            $Having = " having sum(ua.transaction_amount) = 0";
            $Description .= " account_balance is zero";
            break;

         case 'Less Than Zero':
            $Having = " having sum(ua.transaction_amount) < 0";
            $Description .= " account_balance less than zero";
            break;
      }

      $customized = 'Y';
   }

   // Handle selected schedule
   if ((isset($selected_schedule) && $selected_schedule > 0) || (isset($selected_order_status) && count($selected_order_status) > 0)) {
      if (strpos($Description, " where") === false) $Description .= " where";
      else $Description .= " and";

      $schedule_where = "";
      if (isset($selected_schedule) && $selected_schedule > 0) {
         $school_num = $school_record['record_num'];
         $schedule_school = db_getVal("SELECT school_num FROM hotlunch_schedules WHERE record_num = $selected_schedule");
         $schedule_name = db_getVal("SELECT schedule_name fRoM hotlunch_schedules WHERE school_num = $schedule_school AND record_num = $selected_schedule");
         $schedule_where = " AND hloh.school_num = $schedule_school AND hloh.schedule_num = $selected_schedule";
         $Description .= " order placed in '$schedule_name'";
      }
      else {
         $schedule_school = $school_num;
      }

      $selected_status = " AND hloh.status NOT IN ('Inactive','Deleted')";
      if (isset($selected_order_status) && is_array($selected_order_status) && count($selected_order_status)) {
         $status = implode("','",$selected_order_status);
         $selected_status = " AND hloh.status IN ('$status')";

         if (isset($selected_schedule) && $selected_schedule > 0) $Description .= " and";
         $Description .= " '$selected_status'";
      }

      $NOT = "";
      if (isset($inschedule) && $inschedule == 'N') $NOT = "NOT";

      $Where .= " AND u.record_num $NOT IN (SELECT user_num FROM people AS p, hotlunch_order_header AS hloh " .
                                           "WHERE p.school_num = $base_school_num " .
                                           "AND hloh.school_num = $schedule_school " .
                                           "AND p.record_num = hloh.ordered_by_num " .
                                           "$schedule_where $selected_status)";
   }


   // Handle selected menu items
   if (isset($selected_menu_items) && count($selected_menu_items) > 0) {

      if (strpos($Description, " where") === false) $Description .= " where";
      else $Description .= " and";

      $items = implode(",",$selected_menu_items);
      $results = db_query("SELECT distinct item_title FROM hotlunch_menu_items WHERE school_num = $school_num AND record_num IN ($items)");
      $iDescription = "";
      while ($row = db_fetch_array($results)) {
         $item_title = $row['item_title'];
         if (strlen($iDescription)) $iDescription .= " or ";
         $iDescription .= $item_title;
      }

      $Where .= " AND u.record_num IN (SELECT user_num FROM people AS p, hotlunch_order_items AS hloi " .
                                      "WHERE p.school_num = $base_school_num " .
                                      "AND p.record_num = hloi.ordered_by_num " .
                                      "AND hloi.school_num IN (" . $school_record['linked_sites'] . ") " .
                                      "AND hloi.menu_item_num IN ($items))";
      $Description .= " ordered " . $iDescription;
   }


   // Handle selected categories
   if (isset($student_categories) && count($student_categories)) {
      // Reset the start of each query
      $Set = implode(",",$student_categories);
      $Where .= " and u.record_num IN (SELECT user_num FROM people AS p, people_categories AS pc WHERE p.school_num = $base_school_num AND pc.school_num = $base_school_num AND p.record_num = pc.people_num AND category_num IN ($Set))";

      if (strpos($Description, " where") === false) $Description .= " where";
      else $Description .= " and";
      $Description .= " students in selected classes";
   }


   // Handle selected classes
   if (isset($selected_classes) && count($selected_classes)) {
      // Reset the start of each query
      $Set = implode(",",$selected_classes);
      if ($school_record['implement_meal_locations'] == 'Y') {
         $Where .= " and u.record_num IN (SELECT user_num FROM people WHERE school_num = $base_school_num AND (meal_location in ($Set) OR class_num in ($Set)))";
         $query = "UPDATE people SET meal_location = class_num WHERE school_num = $school_num AND meal_location IS NULL";
         db_query($query);
      }
      else $Where .= " and u.record_num IN (SELECT user_num FROM people WHERE school_num = $base_school_num AND class_num in ($Set))";

      if (strpos($Description, " where") === false) $Description .= " where";
      else $Description .= " and";
      $Description .= " students in selected classes";
   }
}


// create attachment list
$attachments = '';
$attachmentList = array();
if (array_key_exists('attachments',$_POST)) {
   if (is_array($_POST['attachments'])) {
      $attachmentList = $_POST['attachments'];
      if (is_array($attachmentList)) {
         $attachments = implode(",", $attachmentList);
      }
      else {
         $attachments = $attachmentList;
      }
   }
}
writeLog("attachments: $attachments");

// Set the start of each query
$Query = "SELECT DISTINCT u.userid,password,email,email2,email3,include_email1_on_list,include_email2_on_list,include_email3_on_list,u.type,u.status,u.date_added,u.last_login,u.can_order,u.num_orders," .
            "u.source,ordered,paid,trx_status,u.posted_date,available_to_volunteer,language,u.first_name,u.last_name,home_phone,address,city,province,postal,user_ip,u.record_num," .
            "sum(ua.transaction_amount) AS balance " .
         "FROM users AS u LEFT JOIN user_account AS ua ON $uawhere ua.user_num = u.record_num AND (deleted != 'Y' OR deleted IS NULL) " .
         "WHERE u.email is not null " .
            "AND u.email != '' " .
            "AND u.school_num = $base_school_num " .
            "AND receive_emails != 'N' " .
            "AND u.status not in ('Deleted','Inactive') ";
$GroupBy = "GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30 ";

$Query .= "$Where $GroupBy $Having ORDER BY email,u.record_num";


//
// Determine how many emails will be sent
//
$results = db_query($Query);
$num_rows = db_num_rows($results);

?>


 

  

   

    
Confirm Email Send
   
  
  

   

$Subject
\n");
   echo("$Category:
\n");
   echo("$Description:
\n");
?>
   
  
  

   

     0) {
?>
     

     

      
 record(s) found
     

      
:
      

       

       
      
     
     

      

       

       
      
     
    
   
  
 
 
 
 
 
 
 
 
 
 
 
 
 \n");
     }
   }
  ?>
 
 


 0) {
      echo("
\n");
      echo("
".getLanguageText('TEXT-ATTACHMENTS')."
\n");
      echo("
\n");

      for ($i = 0; $i < count($attachmentList); $i++) {
         $fname = $attachmentList[$i];
         $fname = preg_replace("#files/.*/documents/#","",$fname);
         echo("
".$fname."
\n");
      }

      echo("
\n");
      echo("
\n");
   }
?>



 
 $b) {
          echo(" \n");        
        }
      } else {
        echo(" \n");
      }
   }
?>
 


 
Recipient List
 

  
  
  
  
  
  
  
  $b) {
          echo(" \n");        
        }
      } else {
        echo(" \n");
      }
   }
  ?>
  
 
 $field\n");
      }
?>
    \n");

         for ($i = 0; $i < count($Recipients); $i++) {
            $field = $Recipients[$i];
            $align = $Alignment[$i];
            echo("  \n");
         }
         echo("");
         echo("  \n");
      }
?>
 

  


  
$row[$field]

 

 
 

\n\n");
?>