license-reset-queries

UPDATE corporate_member_info 
SET r_comp = NULL, o_comp = NULL, 
f_name = NULL, l_name = NULL, Nname_f = NULL, Nname_l = NULL, 
password = "****", login_name = licence_ID,
course_access = NULL,  limited_acess = NULL,
email = NULL, employee_id = NULL, email = NULL, report_frequency = NULL, 
language_preference = "ar", 
report_send = NULL, report_frequency = NULL, report_recipients = NULL,
current_login = NULL, logins = NULL, total_time_spent = NULL, 
activate_date = NULL, 
subscription_start_date = NULL, subscription_end_date = NULL, total_time_spent = NULL,
standby = 1, 
completed = 0, test_pass = 0, in_progress = 0, 
active = 1
WHERE portal_id = 41 AND test_user IS NULL
LIMIT 1

UPDATE corporate_member_info 
SET r_comp = NULL, o_comp = NULL, 
f_name = NULL, l_name = NULL, Nname_f = NULL, Nname_l = NULL, 
password = "****", 
course_access = NULL,  limited_acess = NULL,
email = NULL, 
language_preference = "ar", 
report_send = NULL, report_frequency = NULL, report_recipients = NULL,
current_login = NULL, logins = NULL, total_time_spent = NULL, 
activate_date = NULL, 
subscription_start_date = NULL, subscription_end_date = NULL, total_time_spent = NULL,
standby = 1, 
completed = 0, test_pass = 0, in_progress = 0, 
active = 1, 
mng_group = "BAJ", group_id = 471
WHERE portal_id = 39 AND test_user IS NULL
LIMIT 10

DELETE FROM member_course_table
WHERE member_ID IN (SELECT member_ID FROM corporate_member_info WHERE portal_id = 38)

UPDATE portals_group_journal SET active_lic = 0, total_course_comp = 0, total_course_pass = 0, total_course_init = 0, total_lessons_complete = 0
WHERE portal_id = 38

//licence_ID,load_order,mng_group,group_id,login_name,password,language_preference,company,portal_id,duration,transactions,standby,limited_acess
//new user query
INSERT INTO corporate_member_info 
SET licence_ID = "zamil15047", load_order = 15947, mng_group = "Zamil Group", group_id = 442, login_name = "zamil15047", password = "****", language_preference = "en",company="zamil",portal_id=38,duration=180,standby=1,limited_acess=NULL ;

//finding  users with progres in courses that are not in their course_access field
SELECT member_course_table.*, corporate_member_info.* 
FROM member_course_table 
JOIN corporate_member_info ON member_course_table.member_ID = corporate_member_info.member_ID 
WHERE corporate_member_info.course_access 
NOT REGEXP member_course_table.course_ID 
AND corporate_member_info.member_ID 
  IN (SELECT member_ID 
      FROM corporate_member_info 
      WHERE licence_ID LIKE 'saib%') 
AND member_course_table.course_start_date < corporate_member_info.subscription_start_date