'Write email opt-in preferences to the output file.
This will generate a CSV with one row for each enrollment.
This means that the user\'s "opt in" preference will be specified
multiple times if the user has enrolled in multiple courses
within the org.  However, the values should always be the same:
if the user is listed as "opted out" for course A, she will
also be listed as "opted out" for courses B, C, and D.
Arguments:
file_handle (file): Handle to the output file.
org_aliases (list): List of aliases for the org.
courses (list): List of course keys in the org.
Returns:
None'

def _write_email_opt_in_prefs(self, file_handle, org_aliases, courses):

    writer = csv.DictWriter(file_handle, fieldnames=self.OUTPUT_FIELD_NAMES)
    writer.writeheader()
    cursor = self._db_cursor()
    query = u'\n                                                SELECT\n                                                                user.`id`    AS    `user_id`,\n                                                                user.`username`    AS    username,\n                                                                user.`email`    AS    `email`,\n                                                                profile.`name`    AS    `full_name`,\n                                                                enrollment.`course_id`    AS    `course_id`,\n                                                                (\n                                                                                SELECT    value\n                                                                                FROM    user_api_userorgtag\n                                                                                WHERE    org    IN    (    {org_list}    )\n                                                                                AND    `key`="email-optin"\n                                                                                AND    `user_id`=user.`id`\n                                                                                ORDER    BY    modified    DESC\n                                                                                LIMIT    1\n                                                                )    AS    `is_opted_in_for_email`,\n                                                                (\n                                                                                SELECT    modified\n                                                                                FROM    user_api_userorgtag\n                                                                                WHERE    org    IN    (    {org_list}    )\n                                                                                AND    `key`="email-optin"\n                                                                                AND    `user_id`=user.`id`\n                                                                                ORDER    BY    modified    DESC\n                                                                                LIMIT    1\n                                                                )    AS    `preference_set_datetime`\n                                                FROM\n                                                                student_courseenrollment    AS    enrollment\n                                                                LEFT    JOIN    auth_user    AS    user    ON    user.id=enrollment.user_id\n                                                                LEFT    JOIN    auth_userprofile    AS    profile    ON    profile.user_id=user.id\n                                                WHERE    enrollment.course_id    IN    (    {course_id_list}    )\n                                                '.format(course_id_list=self._sql_list(courses), org_list=self._sql_list(org_aliases))
    cursor.execute(query)
    row_count = 0
    for row in self._iterate_results(cursor):
        (user_id, username, email, full_name, course_id, is_opted_in, pref_set_datetime) = row
        writer.writerow({'user_id': user_id, 'username': username.encode('utf-8'), 'email': email.encode('utf-8'), 'full_name': (full_name.encode('utf-8') if full_name else ''), 'course_id': course_id.encode('utf-8'), 'is_opted_in_for_email': (is_opted_in if is_opted_in else 'True'), 'preference_set_datetime': (pref_set_datetime if pref_set_datetime else self.DEFAULT_DATETIME_STR)})
        row_count += 1
    LOGGER.info(u'Retrieved    {num_rows}    records.'.format(num_rows=row_count))
