Stipend related queries

-- Get number of data with invalid mobile number
SELECT COUNT(*) AS total FROM `stipend_info_2021_AR` t
WHERE NOT
(
  (SUBSTR(t.`mobile_number`,1,3) IN ('017','016','015','019','018','011') AND LENGTH(t.`mobile_number`) = 11)
  OR
  (SUBSTR(t.`mobile_number`,1,2) IN ('17','16','15','19','18','11') AND LENGTH(t.`mobile_number`) = 10)
)

-- Set mobile_flag for data with invalid mobile number
UPDATE `stipend_info_29June` t SET t.`mobile_flag` = 1;

UPDATE `stipend_info_29June` t SET t.`mobile_flag` = 0 WHERE
(
  (SUBSTR(t.`mobile_number`,1,3) IN ('017','016','015','019','018','011') AND LENGTH(t.`mobile_number`) = 11)
  OR
  (SUBSTR(t.`mobile_number`,1,2) IN ('17','16','15','19','18','11') AND LENGTH(t.`mobile_number`) = 10)
);

-- Get number of data having valid mobile number and invalid amount
SELECT COUNT(*) AS total FROM `stipend_info_29June` t
WHERE
(
  (SUBSTR(t.`mobile_number`,1,3) IN ('017','016','015','019','018','011') AND LENGTH(t.`mobile_number`) = 11)
  OR
  (SUBSTR(t.`mobile_number`,1,2) IN ('17','16','15','19','18','11') AND LENGTH(t.`mobile_number`) = 10)
)
AND
(t.`amount` > 3600 OR MOD(t.`amount`,25) != 0 OR t.amount is null or t.amount = 'Null');


-- Update data with wrong amount set amount_flag
UPDATE `stipend_info_29June` t SET t.`amount_flag` = 0

UPDATE `stipend_info_29June` t SET t.`amount_flag` = 1
WHERE
(
  (SUBSTR(t.`mobile_number`,1,3) IN ('017','016','015','019','018','011') AND LENGTH(t.`mobile_number`) = 11)
  OR
  (SUBSTR(t.`mobile_number`,1,2) IN ('17','16','15','19','18','11') AND LENGTH(t.`mobile_number`) = 10)
)
AND
(t.`amount` > 3600 OR MOD(t.`amount`,25) != 0 OR t.amount IS NULL OR t.amount = 'Null');

-- Export result
SELECT
  s.`token_no`,
  s.`parent_name_bijoy` AS beneficiary,
  s.`customer_wallet`,
  GROUP_CONCAT(DISTINCT t.student_name_original SEPARATOR ',') AS Students_name,
  SUM(s.`amount`) AS Amount,
  GROUP_CONCAT(t.`class_name_orginal` SEPARATOR ',') AS classes,
  COUNT(
    DISTINCT t.`student_name_original`
  ) AS total,
  s.`school_name_original`
FROM
  stipend_info_29June s
  INNER JOIN `student_29June` t
    ON s.`id` = t.`stipend_info_id`
WHERE s.mobile_flag=0
AND s.`amount_flag` = 0
AND s.amount IS NOT NULL
GROUP BY s.`mobile_number`,
  s.`parent_name_bijoy`
ORDER BY s.`school_name_original`,s.`token_no` ASC;

-- Total amount-wise distribution of parents in schools (if one wants to know which total amounts are mostly found)
SELECT ab.total_amount, COUNT(*) AS total, ab.school FROM
(SELECT
  SUM(s.`amount`) AS total_amount,
  s.`school_name_unicode` AS school
FROM
  stipend_info_29June s
  INNER JOIN `student_29June` t
    ON s.`id` = t.`stipend_info_id`
WHERE s.mobile_flag=0
AND s.`amount_flag` = 0
AND s.amount IS NOT NULL
GROUP BY s.`mobile_number`,
SUBSTR(s.`parent_name`, 1, 1)
) ab
GROUP BY ab.total_amount, ab.school
ORDER BY ab.school,total DESC


-- To see how many persons are against each mobile number
select ab.mobile_number, count(*) as COUNT, Group_Concat(ab.beneficiary separator ','), ab.school_name_unicode
from
(
SELECT
  s.`parent_name_unicode` AS beneficiary,
  s.mobile_number,
  SUM(s.`amount`) AS Amount,
  s.`school_name_unicode`,
   s.school_name_original
FROM
  `stipend_info_10JulDisb_1Jul-1` s
  INNER JOIN `student_10JulDisb_1Jul-1` t
    ON s.`id` = t.`stipend_info_id`
WHERE s.mobile_flag=0
AND s.amount IS NOT NULL

GROUP BY s.`mobile_number`,
  s.`parent_name_bijoy`, s.school_name_original
  HAVING Amount > 0
) ab
group by ab.mobile_number
order by  ab.school_name_unicode,COUNT desc;

-- an error check: which students from pre-primary gets 1200 taka
SELECT
    t.mobile_number,
    t.amount AS stipendAmount,
    s.amount AS studentAmount,
    s.class_name_orginal
FROM
    `stipend_info_03072016` t
        JOIN
    `student_03072016` s ON t.id = s.stipend_info_id
WHERE
    (s.class_name LIKE '%cÖvK%'
        OR s.class_name LIKE '%wkï%')
        AND t.amount = 1200
ORDER BY t.mobile_number;
------------------------------