use sgp_lite_api;


select auca.id from sgp_lite_accounts_applicationuser au
join sgp_lite_accounts_applicationusercustomeraccount auca on au.id = auca.application_user_id
  join sgp_lite_tests_test test on auca.id = test.owner_id
where au.email='ensinomedio@colegionaturezaararaquara.com.br';

show COLUMNS  from sgp_lite_tests_test;
# -----------------------------------------------------------------

SELECT aa.username, aa.first_name, aa.last_name, cac.member_date
FROM sgp_lite_accounts_applicationuser aa
    INNER JOIN sgp_lite_accounts_applicationusercustomeraccount aacu ON aa.id = aacu.application_user_id
        AND aacu.type_id = 2
    INNER JOIN sgp_lite_customer_account_customeraccount cac ON aacu.customer_account_id = cac.id
WHERE
    cac.member_date BETWEEN '2017-07-07' AND '2017-07-14';


SELECT aa.username, aa.first_name, aa.last_name, cac.member_date, COUNT(DISTINCT ttr.id) as digitalizacoes
FROM sgp_lite_accounts_applicationuser aa
    INNER JOIN sgp_lite_accounts_applicationusercustomeraccount aacu ON aa.id = aacu.application_user_id
    INNER JOIN sgp_lite_customer_account_customeraccount cac ON aacu.customer_account_id = cac.id
    INNER JOIN sgp_lite_tests_testresult ttr ON aacu.id = ttr.owner_id
WHERE
    aacu.id NOT IN (
        SELECT DISTINCT aacu.id FROM sgp_lite_accounts_applicationusercustomeraccount aacu
            INNER JOIN sgp_lite_tests_testresult ttr2 ON aacu.id = ttr2.owner_id
        WHERE
            aacu.type_id = 2 AND ttr2.creation_date < '2017-07-07'
    ) AND ttr.creation_date BETWEEN '2017-07-07' AND '2017-07-14'
GROUP BY aa.username
HAVING
    digitalizacoes >= 10;



        SELECT aa.id, aa.username, aa.first_name, aa.last_name, cac.member_date
        FROM sgp_lite_accounts_applicationuser aa
            INNER JOIN sgp_lite_accounts_applicationusercustomeraccount aacu ON aa.id = aacu.application_user_id
                AND aacu.type_id = 2
            INNER JOIN sgp_lite_customer_account_customeraccount cac ON aacu.customer_account_id = cac.id
        WHERE
            cac.member_date BETWEEN '2017-07-07' AND '2017-07-14';


SELECT aa.id, aa.username, aa.first_name, aa.last_name, cac.member_date, COUNT(DISTINCT ttr.id) as digitalizacoes
FROM sgp_lite_accounts_applicationuser aa
    INNER JOIN sgp_lite_accounts_applicationusercustomeraccount aacu ON aa.id = aacu.application_user_id
    INNER JOIN sgp_lite_customer_account_customeraccount cac ON aacu.customer_account_id = cac.id
    INNER JOIN sgp_lite_tests_testresult ttr ON aacu.id = ttr.owner_id
WHERE
    aacu.id NOT IN (
        SELECT DISTINCT aacu.id FROM sgp_lite_accounts_applicationusercustomeraccount aacu
            INNER JOIN sgp_lite_tests_testresult ttr2 ON aacu.id = ttr2.owner_id
        WHERE
            aacu.type_id = 2 AND ttr2.creation_date < '2017-07-07'
    ) AND ttr.creation_date BETWEEN '2017-07-07' AND '2017-07-14'
GROUP BY aa.username
HAVING
    digitalizacoes >= 10;




# --------------------------------------------------------------------
SELECT aa.id, aa.username, aa.first_name, aa.last_name, aacu.id as owner_id, cac.id, cacp.name, caca.subscription_id,
    caca.status_id, caca.*, cacac.*
FROM sgp_lite_accounts_applicationuser aa
    INNER JOIN sgp_lite_accounts_applicationusercustomeraccount aacu ON aa.id = aacu.application_user_id AND aacu.type_id = 2
    INNER JOIN sgp_lite_customer_account_customeraccount cac ON aacu.customer_account_id = cac.id
    INNER JOIN sgp_lite_customer_account_customeragreement caca ON cac.id = caca.customer_account_id
    INNER JOIN sgp_lite_customer_account_accountplan cacp ON caca.account_plan_id = cacp.id
    LEFT JOIN sgp_lite_customer_account_customeragreementcredit cacac ON caca.id = cacac.customer_agreement_id
WHERE
    aa.username LIKE '%compras@maededeus.edu.br%'
ORDER BY caca.id;


select aca.id from sgp_lite_accounts_applicationuser au
join sgp_lite_accounts_applicationusercustomeraccount aca on au.id = aca.application_user_id
where email='ccantai@hotmail.com';

select * from sgp_lite_settings_apiauditlog where owner_id=1795 and type_audit='error' ORDER BY id desc limit 100;


select ca.name as 'CA', ca.id as 'CA ID', auca.id as 'AUCA', au.email as 'AU EMAIL' from sgp_lite_accounts_applicationusercustomeraccount auca
join sgp_lite_accounts_applicationuser au on auca.application_user_id = au.id
join sgp_lite_customer_account_customeraccount ca on auca.customer_account_id = ca.id
where au.email="dallazsnandreia@gmail.com";

select auca.id, au.email, auca.type_id from sgp_lite_customer_account_customeraccount ca
    join sgp_lite_accounts_applicationusercustomeraccount auca on ca.id = auca.customer_account_id
    join sgp_lite_accounts_applicationuser au on auca.application_user_id = au.id
# where au.id in (24421, 24917, 24503, 24004, 24567, 24370) limit 20;
where ca.id=21150 limit 20;

select * from sgp_lite_accounts_applicationuser where email='ccantai@hotmail.com';
select * from sgp_lite_customer_account_customeraccount where id=21150;

SELECT aa.id, COUNT(DISTINCT csuc.id) as total_content FROM sgp_lite_accounts_applicationuser aa
INNER JOIN sgp_lite_content_structure_applicationusercontent aac ON aa.id = aac.user_id
INNER JOIN sgp_lite_content_structure_usercontent csuc ON aac.content_id = csuc.id
INNER JOIN sgp_lite_content_structure_usercontentconfig casuc ON csuc.content_config_id = casuc.id
WHERE
casuc.`order` = 1
GROUP BY aa.id
HAVING total_content > 1
ORDER BY aa.id;

SELECT aa.id, csuc.parent_id, csuc.id, csuc.name FROM sgp_lite_accounts_applicationuser aa
INNER JOIN sgp_lite_content_structure_applicationusercontent aac ON aa.id = aac.user_id
INNER JOIN sgp_lite_content_structure_usercontent csuc ON aac.content_id = csuc.id
INNER JOIN sgp_lite_content_structure_usercontentconfig casuc ON csuc.content_config_id = casuc.id
WHERE
aa.id in (
    7,
    12,
    330,
    389,
    402,
    1257,
    2307,
    4180,
    7051,
    8455
)
ORDER BY aa.id;

use sgp_lite_api;


-- Achar o owner_id
select au.username, aca.id, aca.customer_account_id, au.id, aca.type_id from sgp_lite_accounts_applicationuser au
join sgp_lite_accounts_applicationusercustomeraccount aca on au.id = aca.application_user_id
where au.username LIKE '%pedrovsc@unipar.br%' limit 10;
# where aca.id=5822;

select t.name from sgp_lite_tests_testresult tr
    join sgp_lite_academic_student st on tr.student_id = st.id
    join sgp_lite_tests_test t on tr.test_id = t.id
where tr.owner_id=25451 and st.register_id=123456789;
# and t.name like "%JF-MTC NOT%";

select * from sgp_lite_tests_testresult
    where owner_id=25451 and student_read_id=123456789;

show COLUMNS from sgp_lite_settings_apiauditlog;

select * from sgp_lite_settings_apiauditlog where
#     owner_id=25451 and message like "%studentsync%" and message like "%170200072%" ORDER BY id desc limit 10;
    type_audit != 'info' and owner_id=5822 ORDER BY id desc limit 10;



select * from sgp_lite_tests_testresult where sync_id="35a9f68a-e87e-45d2-98d3-e5205a14556e";

select * from authtoken_token where user_id=5918;

select * from sgp_lite_accounts_applicationuser where email like "%unipar.br%";

select cag.* from sgp_lite_customer_account_customeragreement cag
join sgp_lite_customer_account_customeraccount ca on cag.customer_account_id = ca.id
join sgp_lite_accounts_applicationusercustomeraccount apuca on ca.id = apuca.customer_account_id
where apuca.id=10098;

select * from sgp_lite_customer_account_accountplan;


Select * from sgp_lite_tests_test tr
join sgp_lite_accounts_applicationusercustomeraccount apuca on tr.owner_id = apuca.id
# SET s.record_status = 2, s.last_modification = NOW()
where tr.record_status=1 and apuca.customer_account_id=10048;

UPDATE sgp_lite_tests_test tr
join sgp_lite_accounts_applicationusercustomeraccount apuca on tr.owner_id=apuca.id
SET tr.record_status = 2, tr.last_modification = NOW()
where tr.record_status=1 and apuca.customer_account_id=10048;