-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `db_a6d307_dugsi`
--

DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `add_bank` (IN `_id` INT, IN `_BankName` VARCHAR(50), IN `_Location` VARCHAR(50), IN `_accountno` VARCHAR(50))  NO SQL BEGIN
if not EXISTS(select id from bank WHERE id=_id) THEN
BEGIN
INSERT INTO `bank`( `BankName`, `Location`, `AccountNo`) VALUES (_BankName,_Location,_accountno);
SELECT 'Inserted Success' as msg;
end;
ELSE
BEGIN

UPDATE bank set BankName=_BankName,Location=_Location,AccountNo=_accountno WHERE id=_id;
SELECT 'Updated Success' as msg;
end;
END if;
end$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `all_WeeksEvents` (IN `p_accy` INT, IN `p_section` INT, IN `p_subject` INT, IN `p_employee` INT)   BEGIN

SELECT tc.id into @assign from assign_teacher_class tc 

JOIN employee e ON tc.teacher=e.id

where tc.accy=p_accy and tc.section=p_section and tc.subject=p_subject AND e.id=p_employee limit 1;
SELECT l.id into @lesson_id from lessoninfo l where l.assign=@assign;
    SELECT 
        w.WeekNumber,
        w.StartDate AS WeekStart,
        w.EndDate AS WeekEnd,
        IF(COUNT(e.EventID) > 0, 'Yes', 'No') AS HasEvent,
       
           CASE 
        WHEN COUNT(e.EventID) > 0 THEN GROUP_CONCAT(e.EventName ORDER BY e.EventDate SEPARATOR ', ')
        WHEN ld.week IS NOT NULL THEN 
        GROUP_CONCAT(ld.Lesson ORDER BY ld.id SEPARATOR '<br>')
      
    END AS EventNames,
     GROUP_CONCAT(td.Topic ORDER BY td.id SEPARATOR '<br>') as Topic, 
     GROUP_CONCAT(td.Reason ORDER BY td.id SEPARATOR '<br>')as Reason
    FROM Weeks w
    LEFT JOIN Events e
        ON (e.EventDate BETWEEN w.StartDate AND w.EndDate 
            OR (e.IsRecurring = 1 AND e.EventDate <= w.EndDate))
            left join lessondetail ld on ld.info_id=@lesson_id and ld.week= w.WeekID
            left join topicdetail td on td.lesson_id=ld.id
            where w.accy=p_accy
    GROUP BY w.WeekNumber, w.StartDate, w.EndDate
    ORDER BY w.WeekNumber;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `Auth_Salary` (IN `p_user_id` INT(255), IN `p_salaryid` INT, IN `input_string` TEXT, IN `p_method` INT, IN `p_amount` DECIMAL(18,2), IN `p_date` DATE, IN `p_remark` TEXT)   BEGIN
    DECLARE str_length INT DEFAULT 0;
    DECLARE current_pos INT DEFAULT 1;
    DECLARE comma_pos INT DEFAULT 0;
    DECLARE hyphen_pos INT DEFAULT 0;
    DECLARE part VARCHAR(255);
     
    DECLARE id_part VARCHAR(255);
    DECLARE amount_part VARCHAR(255);
    DECLARE value_id INT;
    DECLARE value_amount DECIMAL(10, 2);

     
    SET str_length = CHAR_LENGTH(input_string);

    
    SET hyphen_pos = INSTR(input_string, '-');
    
    
    SET id_part = SUBSTRING(input_string, 1, hyphen_pos - 1);
    SET amount_part = SUBSTRING(input_string, hyphen_pos + 1);

    
    DROP TEMPORARY TABLE IF EXISTS salcombine;

    
    CREATE TEMPORARY TABLE salcombine (
        id INT,
        amount DECIMAL(10, 2)
    );

    
    SET current_pos = 1;
    WHILE current_pos <= CHAR_LENGTH(id_part) DO
        SET comma_pos = INSTR(SUBSTRING(id_part, current_pos), ',');
        
        IF comma_pos = 0 THEN
            SET part = SUBSTRING(id_part, current_pos);
            SET current_pos = CHAR_LENGTH(id_part) + 1;
        ELSE
            SET part = SUBSTRING(id_part, current_pos, comma_pos - 1);
            SET current_pos = current_pos + comma_pos;
        END IF;

        
        SET value_id = CAST(part AS UNSIGNED);
        
        
        SET comma_pos = INSTR(SUBSTRING(amount_part, 1), ',');
        
        IF comma_pos = 0 THEN
            SET part = amount_part;
            SET amount_part = '';
        ELSE
            SET part = SUBSTRING(amount_part, 1, comma_pos - 1);
            SET amount_part = SUBSTRING(amount_part, comma_pos + 1);
        END IF;

        
        SET value_amount = CAST(part AS DECIMAL(10, 2));
        
        
        INSERT INTO salcombine (id, amount) VALUES (value_id, value_amount);
    END WHILE;

    UPDATE salary_detail
    JOIN salcombine ON salary_detail.id = salcombine.id
    SET salary_detail.net_salary = salcombine.amount,
        salary_detail.base_salary = salcombine.amount,
        salary_detail.status = 2
    WHERE salary_detail.salid = p_salaryid;

UPDATE salary_detail set status=1 where salary_detail.salid=p_salaryid and 
id not in (select id from salcombine);

select si.school,si.salary_month,si.Expense_id,si.Expense_invoice into @school,@name,@Expenseid,@Expense_invoice from salaryinfo si 
where  id =p_salaryid;
select e.id into @e_type from expensetype e where e.sch_id=@school and e.id in (23,24,25);
if EXISTS(SELECT * from expensebill e where e.id=@Expenseid)THEN
update Expensebill set amount=p_amount,paid=p_amount,  remark=p_remark, date=p_date where id=@Expenseid;
update expensepayment set  receiptmethod=p_method,amount=p_amount, remark=p_remark, date=p_date where invoice=@Expense_invoice;
update salaryinfo  set Totalamount=p_amount
where id=p_salaryid;
ELSE
INSERT INTO `Expensebill`(`school`,`type`, `name`, `amount`,paid,  `remark`, `date`, `user`) VALUES
(@school, @e_type, @name, p_amount, p_amount, p_remark, p_date, p_user_id);
SET @Invoice = LAST_INSERT_ID();
INSERT INTO `expensepayment`( `invoice`, `receiptmethod`, `amount`, `remark`, `date`, `user_id`) VALUES (@Invoice,p_method,p_amount,p_remark,p_date,p_user_id);
SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+03:00')into @date;
update salaryinfo  set Totalamount=p_amount,status=1,auth_by=p_user_id,auth_date=@date,Expense_invoice=concat('V-',@Invoice),Expense_id=@Invoice
where id=p_salaryid;
END if;




SELECT 'success| Si sax ah ayaa loo charge gareeyay'  ;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `A_TIJAABO` ()   BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;

DECLARE n1 INT DEFAULT 0;
DECLARE i1 INT DEFAULT 0;

CREATE TEMPORARY TABLE tmp
(exam int,id int, result int,percentage decimal(18,2));
insert into tmp
SELECT
  DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX( Exams , ',', n.digit+1), ',', -1) exam,id,result_id,percentage 
FROM
  exam_result_calculation
  INNER JOIN
  (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) n
  ON LENGTH(REPLACE(Exams, ',' , '')) <= LENGTH(Exams)-n.digit;
 
create TEMPORARY TABLE subject_class(
    class int,
    subject int
    );
    insert into subject_class
    SELECT cs.id 'class',s.id 'subject' FROM subject_group_class_sections clss INNER join classes cs on cs.id=clss.class_section_id INNER join subject_group_subjects sgs on sgs.subject_group_id=clss.subject_group_id INNER join subjects s on s.id=sgs.subject_id where cs.id=1;
CREATE TEMPORARY TABLE subject_exam
(subject int,class int,exam int );
SELECT COUNT(*) FROM subject_class INTO n1;
SELECT COUNT(*) FROM tmp INTO n;

SET i=0;
WHILE i<n DO 
SET i1=0;
WHILE i1<n1 DO 
INSERT INTO subject_exam
SELECT subject,class,(SELECT exam FROM tmp LIMIT i,1) 'exam' FROM subject_class LIMIT i1,1;

 
 
  SET i1 = i1 + 1;
END WHILE;
  SET i = i + 1;
END WHILE;






CREATE TEMPORARY TABLE IF NOT EXISTS student_marks_step1 AS (
select s.id 'subject_id',ss.student_id,css.id 'class_id',et.id 'exam_id'
    ,ifnull((select ifnull(emk.marks,0) from exam_marks emk where emk.student_id=ss.student_id and emk.exam_id=ec.exam and emk.subject_id=ec.subject),0)'Marks' 
    from subject_exam ec inner join exam_type et on et.id=ec.exam INNER join classes c on c.id=ec.class INNER join class_sections css on css.class_id=c.id  inner join subjects s on s.id=ec.subject  INNER join students ss on ss.class_id=css.id where c.id=1 and ss.student_id='MOA-401');
    CREATE TEMPORARY TABLE IF NOT EXISTS student_marks_step2 AS (
    select sts.subject_id,s.subject_name,sts.student_id,t.id,sum(sts.Marks) 'marks',t.percentage from student_marks_step1 sts inner join subjects s on s.id=sts.subject_id INNER join tmp t on t.exam=sts.exam_id GROUP by student_id,subject_id,t.id
        );












 CREATE TEMPORARY TABLE IF NOT EXISTS student_marks_step3 AS (
 select sts.subject_name,sts.student_id,sum(if(sts.Marks!=0,(sts.percentage/100)*sts.Marks,sts.Marks)) 'Marks' from student_marks_step2 sts group by student_id,subject_id 
     );
     SELECT sms3.subject_name,sms3.student_id,sms3.Marks,(SELECT sum(if(sm.Marks!=0,(sm.percentage/100)*sm.Marks,sm.Marks)) FROM student_marks_step2 sm where sm.student_id=sms3.student_id  group by sm.student_id)'Total' FROM student_marks_step3 sms3;
     
 
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `BusSignleStudentsDetail` (IN `p_student_id` VARCHAR(100), IN `p_type` VARCHAR(50))  NO SQL BEGIN
if(p_type='student')THEN
SELECT s.student_id,s.student_name,s.gender,s.reg_date as 'Admission_date',s.dob as "Date of birth",s.pob as "Place of birth",s.mother_name,s.phone as "Mother Phone",s.address,sp.sponsor_name,sp.mobile,sp.address as "Guardian address",s.relation,concat(c.class_name,' ',se.section) as class,s.photo as Image,s.status,s.class_id,st.status_name,sl.id as school_levels,concat(sc.school_name,' - ',l.name ,' ',c.class_name,' ',se.section) as school_name,'Student'as type,sc.id as school FROM students s JOIN class_sections cs on s.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id  JOIN levels l on sl.level_id = l.id
INNER JOIN status st on st.id=s.status

inner join sponsors sp on s.sponsor_id = sp.id  where 
s.student_id = p_student_id AND s.status=2 ORDER by s.id ASC;
ELSE

SELECT pl.id as student_id,pl.StudentName as student_name,pl.mothername as mother_name,pl.ClassName as school_name,ps.sponsor_name as sponsor_name ,ps.mobile ,dd.Districts,'(Pupil)' as type,pl.school from pupil pl join pupil_sponsors ps on ps.id=pl.sponsor_id join districts dd on dd.id=pl.district where pl.id=p_student_id;

END if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `BusStatement` (IN `p_bus` VARCHAR(100), IN `p_accy` INT, IN `p_type` VARCHAR(100), IN `p_user_id` INT)   BEGIN
 
CREATE TEMPORARY table busfee as 
SELECT sb.id,sb.student_id,ss.student_name,concat(l.name, '-' ,c.class_name,'/',se.section) as ClassName,ifnull((SELECT sum(cr.amount)  from cash_receipt cr where cr.fee_type=8 AND cr.status=1 and cr.student_id=sb.student_id and cr.academic_year=61),0) as paid,bu.bus_name as busname,d.driver_name as driver ,d.phone, ac.Name accy,mn.months,bu.number as taariko FROM studentbus sb JOIN students ss on ss.student_id=sb.student_id  
JOIN class_sections cs on ss.class_id = cs.id join classes c on cs.class_id = c.id 
join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id 
JOIN schools sc on sl.school_id = sc.id 
join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user_id
JOIN levels l on sl.level_id = l.id
JOIN sponsors sp on ss.sponsor_id=sp.id
JOIN buslocationinfo bi on bi.id=sb.Bus 
JOIN buslocation bl on bl.bus_info=bi.id and bl.id=sb.location 
join buses bu on bu.id=bi.bus join sub_district sd on sd.id=BL.location  
join location ll on ll.sub_district=sd.id 
JOIN districts dd on dd.id=sd.District 
join academicyear ac on ac.id=sb.accy
JOIN driver d ON d.id = bu.driver_id 
join months mn on mn.id=sb.from_month
where sb.Bus like p_bus and sb.accy like p_accy ;
CREATE TEMPORARY TABLE busfee1 aS
SELECT id,student_id,student_name,paid,busname,driver,phone,taariko from busfee;

if(p_type="paid")THEN
SELECT * from busfee1 where paid>0 ORDER by busname,student_name asc;
ELSE
SELECT * from busfee1 where paid<=0 ORDER by busname,student_name asc;
End if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `change_password` (IN `p_user_id` INT, IN `p_current` VARCHAR(1000), IN `p_passord` VARCHAR(1000))  NO SQL BEGIN
if EXISTS(SELECT * from user where id=p_user_id and password=p_current)THEN
UPDATE user u set u.password=p_passord where id=p_user_id;
SELECT 'success|Password has changed successfully';
ELSE
SELECT 'warning|Current password is incorrent';
END if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `checkbono` (IN `p_user_id` INT, IN `p_boono` INT)   BEGIN
SELECT  1 as no FROM cash_receipt cr
JOIN class_sections cs on cr.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id 
 INNER join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user_id where cr.boono=p_boono and cr.status=1;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `checkIfSubjectHasMarks` (IN `p_subject` INT, IN `p_class` INT, IN `p_accy` INT, IN `p_exam` INT)   BEGIN
if EXISTS (SELECT * from exam_marks em where em.class_id=p_class and em.exam_id=p_exam and em.subject_id=p_subject and em.academicyear=p_accy)THEN
select '1' as 'No';
ELSE
SELECT '0' as 'No';
END IF;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `CheckSubject` (IN `p_subject` TEXT, IN `p_class` TEXT, IN `p_accy` INT)   BEGIN
if EXISTS(SELECT * from exam_class_info e inner join exam_class ec on ec.exam_info=e.id 
  where e.acedemic_year=p_accy and e.class_id in (p_class) and ec.subject_id =p_subject)THEN
  SELECT 1 as 'No';
  ELSEIF (SELECT * from lesson ll where ll.subject_group_subject_id=p_subject and ll.subject_group_class_sections_id in(p_class) and ll.academic_year_id=p_accy)THEN
   SELECT 1 as 'No';
  ELSE
    SELECT 0 as 'No';
  END if;
  END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `check_student_acedemic_class` (IN `p_student_id` VARCHAR(100), IN `p_acedemic_year` INT)  NO SQL BEGIN
CREATE TEMPORARY TABLE  view1 as
SELECT sp.to_class as class,sp.from_academic from student_promotion sp where sp.student_id=p_student_id AND sp.from_academic=p_acedemic_year;



if ((SELECT count(*)  from  view1)<>0) THEN
SELECT sl.id as school_levels,v1.class,concat(sc.school_name,'-',l.name)as school_name,concat(c.class_name,'-',se.section) as class_name from view1 v1  JOIN class_sections cs on v1.class = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id 
JOIN levels l on l.id=sl.level_id
JOIN schools sc on sl.school_id = sc.id ;
ELSE
SELECT '0' as school_levels,'0'as class,'N/A' as school_name,'N/A'as class_name;
END if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `delete_employee` (IN `p_id` VARCHAR(30))   DELETE  from employee where id=p_id$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `DisplayWeeksWithAll` (IN `p_accy` INT, IN `p_section` INT, IN `p_subject` INT)   BEGIN
SELECT tc.id into @assign from assign_teacher_class tc where tc.accy=p_accy and tc.section=p_section 
and tc.subject=p_subject limit 1;
SELECT l.id into @lesson_id from lessoninfo l where l.assign=@assign;
    SELECT 
        w.WeekNumber,
        w.StartDate AS WeekStart,
        w.EndDate AS WeekEnd,
        IF(COUNT(e.EventID) > 0, 'Yes', 'No') AS HasEvent,
       
           CASE 
        WHEN COUNT(e.EventID) > 0 THEN GROUP_CONCAT(e.EventName ORDER BY e.EventDate SEPARATOR ', ')
        WHEN ld.week IS NOT NULL THEN 
        GROUP_CONCAT(ld.Lesson ORDER BY ld.id SEPARATOR '<br>')
      
    END AS EventNames,
     GROUP_CONCAT(td.Topic ORDER BY td.id SEPARATOR '<br>') as Topic, 
     GROUP_CONCAT(td.Reason ORDER BY td.id SEPARATOR '<br>')as Reason
    FROM Weeks w
    LEFT JOIN Events e
        ON (e.EventDate BETWEEN w.StartDate AND w.EndDate 
            OR (e.IsRecurring = 1 AND e.EventDate <= w.EndDate))
            left join lessondetail ld on ld.info_id=@lesson_id and ld.week= w.WeekID
            left join topicdetail td on td.lesson_id=ld.id
            where w.accy=p_accy
    GROUP BY w.WeekNumber, w.StartDate, w.EndDate
    ORDER BY w.WeekNumber;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `DisplayWeeksWithEvents` (`accy` INT)   BEGIN
    SELECT 
        w.WeekNumber,
        w.StartDate AS WeekStart,
        w.EndDate AS WeekEnd,
        IF(COUNT(e.EventID) > 0, 'Yes', 'No') AS HasEvent,
        GROUP_CONCAT(e.EventName ORDER BY e.EventDate SEPARATOR ', ') AS EventNames
    FROM Weeks w
    LEFT JOIN Events e
        ON (e.EventDate BETWEEN w.StartDate AND w.EndDate 
            OR (e.IsRecurring = 1 AND e.EventDate <= w.EndDate))
            where w.accy=accy
    GROUP BY w.WeekNumber, w.StartDate, w.EndDate
    ORDER BY w.WeekNumber;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `DisplayWeeksWithLesson` (IN `p_accy` INT, IN `p_section` INT, IN `p_subject` INT)   BEGIN
SELECT tc.id into @assign from assign_teacher_class tc where tc.accy=p_accy and tc.section=p_section 
and tc.subject=p_subject limit 1;
SELECT l.id into @lesson_id from lessoninfo l where l.assign=@assign;
    SELECT 
        w.WeekNumber,
        w.StartDate AS WeekStart,
        w.EndDate AS WeekEnd,
        IF(COUNT(e.EventID) > 0, 'Yes', 'No') AS HasEvent,
       
           CASE 
        WHEN COUNT(e.EventID) > 0 THEN GROUP_CONCAT(e.EventName ORDER BY e.EventDate SEPARATOR ', ')
        WHEN ld.week IS NOT NULL THEN 
        GROUP_CONCAT(ld.Lesson ORDER BY ld.id SEPARATOR ',')
      
    END AS EventNames
    FROM Weeks w
    LEFT JOIN Events e
        ON (e.EventDate BETWEEN w.StartDate AND w.EndDate 
            OR (e.IsRecurring = 1 AND e.EventDate <= w.EndDate))
            left join lessondetail ld on ld.info_id=@lesson_id and ld.week= w.WeekID
            where w.accy=p_accy
    GROUP BY w.WeekNumber, w.StartDate, w.EndDate
    ORDER BY w.WeekNumber;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `DPBus` (IN `p_user` INT)   BEGIN
 SELECT GROUP_CONCAT( DISTINCT sc.id SEPARATOR ',')  into @school from school_levels sl 
		join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user
		JOIN schools sc on sl.school_id = sc.id;
        
SELECT bs.id,bs.bus_name ,bs.number FROM buses bs JOIN driver dd on dd.id=bs.driver_id where  FIND_IN_SET(dd.school, @school) ;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `DPChargeNo` (IN `p_class` INT, IN `p_classs_section` VARCHAR(100), IN `p_accy` INT, IN `p_feetype` INT)   BEGIN
SELECT DISTINCT sc.Chargeno,sc.Chargeno FROM student_charge sc 
JOIN class_sections cs on cs.id=sc.class
where cs.class_id=p_class and cs.id like p_classs_section and sc.accyr=p_accy and sc.fee_type=p_feetype;
end$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `DPDriver` (IN `p_user` INT)   BEGIN
 SELECT GROUP_CONCAT( DISTINCT sc.id SEPARATOR ',')  into @school from school_levels sl 
		join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user
		JOIN schools sc on sl.school_id = sc.id;
SELECT dr.id, dr.driver_name  FROM driver dr JOIN schools sc on sc.id=dr.school where FIND_IN_SET(dr.school, @school);

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `DPFetchVillage` (IN `p_id` INT)   BEGIN

SELECT bl.id,concat(dd.Districts,'(',sd.village,')')as village from  buslocation bl 
JOIN sub_district sd ON sd.id=bl.location
join location ll on ll.sub_district=sd.id 

JOIN districts dd on dd.id=sd.District 
where bl.bus_info=p_id;
end$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `DPGuardian` (IN `p_user` INT)   BEGIN
SELECT  id, concat(id,' ',sp.sponsor_name,' (',sp.mobile,')') as name FROM sponsors sp where sp.id  in (SELECT s.sponsor_id   FROM students s JOIN class_sections cs on s.class_id = cs.id join classes c on cs.class_id = c.id  JOIN school_levels sl on c.school_level_id = sl.id   INNER join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user

);
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `edit_Salarydate` (IN `p_school` INT, IN `p_date` DATE)   BEGIN
UPDATE salary sl
SET sl.date =p_date
WHERE sl.school =p_school;
SELECT 'Success | date updated successfully';
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `expenseReport` (IN `p_type` VARCHAR(100), IN `p_school` VARCHAR(100), IN `p_from` DATE, IN `p_to` DATE)   BEGIN

SELECT 
    eb.id,
    LPAD(eb.id, 4, '0') AS INV,
    eb.type,
    eb.name,
    eb.amount AS amount,
    eb.remark,
    em.name AS employee,
    eb.paid,
    eb.amount - eb.paid AS Balance,
    eb.date
FROM 
    Expensebill eb 
LEFT JOIN 
    expensetype et ON et.id = eb.type 
LEFT JOIN 
    user u ON u.id = eb.user 
LEFT JOIN 
    employee em ON em.id = u.employee_id 
JOIN 
    schools sc ON eb.school = sc.school_name
WHERE 
    (eb.type = p_type or p_type='%')
    AND sc.school_name = p_school
    AND eb.date BETWEEN p_from AND p_to;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `fetchclasspromotion` (IN `p_studentid` VARCHAR(100))   BEGIN
SELECT ss.current_academic,ss.class_id into @accy,@class from students ss WHERE ss.student_id=p_studentid;
SELECT sp.id as spid,sl.school_id,sl.id as levels,cs.id as class ,sp.from_month,sp.to_month from student_promotion sp JOIN class_sections cs on cs.id=sp.to_class JOIN classes cl on cl.id=cs.class_id JOIN school_levels sl on sl.id=cl.school_level_id  where sp.student_id=p_studentid and sp.from_academic=@accy and sp.to_class=@class;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `Fetchstudentbusinfo` (IN `p_student` VARCHAR(100), IN `p_type` VARCHAR(100))   BEGIN
SELECT sb.student_id,sb.type,bs.bus_name,concat(ds.Districts,' (',sd.village,')') as village,ll.amount,sb.location,sb.Bus,sb.id FROM studentbus sb 
JOIN buslocationinfo bli on bli.id=sb.Bus 
join buslocation bl on bl.bus_info=bli.id and bl.id=sb.location
JOIN buses bs on bs.id=bli.bus 
join sub_district sd on sd.id= bl.location
JOIN location ll on ll.sub_district =sd.id
JOIN districts ds on ds.id=ll.District 

WHERE sb.status=0 and sb.student_id=p_student and sb.type=p_type and sb.accy=(SELECT max(ac.id) from academicyear  ac) ;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `FetchVillage` (IN `p_district` INT)   BEGIN
SELECT * from sub_district  where District=p_district;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `fetch_classSection` (IN `p_class` INT)   BEGIN
SELECT cs.id,s.section FROM class_sections cs JOIN classes 
c on cs.class_id = c.id JOIN sections s on cs.section_id = s.id JOIN 
school_levels sl on c.school_level_id = sl.id  JOIN levels l on sl.level_id = l.id 
JOIN schools sc on sl.school_id = sc.id 
WHERE cs.class_id=p_class GROUP BY cs.id ORDER BY l.id,c.class_name,s.section;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `fetch_examPermission` (IN `p_accy` INT, IN `p_class` INT, IN `p_user` INT)   BEGIN
SELECT distinct et.id,et.type FROM  exam_class_info ec 
		
		inner join exam_type et on et.id=ec.exam_type_id  
		inner join exam_permission ep on ep.exam=et.id and ep.accy=p_accy
		and ep.user_id=p_user
		WHERE ec.class_id = p_class
		 and ec.acedemic_year=p_accy and ec.status=0;	
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `fetch_exam_permisson` (IN `p_user` INT, IN `p_per_user` INT, IN `p_accy` INT)   BEGIN
SELECT u.role_id into @role from user u WHERE u.id=p_user;
SELECT u.id into @user from user u WHERE u.employee_id=p_per_user;

if (@role=7 or @role=1)THEN
CREATE TEMPORARY table e_permisson as
SELECT et.id,et.type from exam_type et;
ELSE
CREATE TEMPORARY table e_permisson as
SELECT et.id,et.type from exam_type et   
INNER JOIN exam_permission ep on et.id=ep.exam 
and ep.user_id=p_user and ep.accy=p_accy;

END if;
SELECT ep.id as ep,et.id,et.type,@user as user_id from e_permisson et   
left JOIN exam_permission ep on et.id=ep.exam
and ep.user_id=@user and ep.accy=p_accy;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `fetch_export_student_marks` (IN `p_class` INT, IN `p_academic_year` INT, IN `p_subject` INT, IN `p_exam` INT)  NO SQL BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS (
SELECT * FROM( SELECT s.student_id,s.student_name from students s where s.class_id=p_class and s.current_academic =p_academic_year AND s.status=2 UNION all select s.student_id,s.student_name from student_promotion sp inner join students s on s.student_id=sp.student_id where sp.from_class=p_class and sp.from_academic=p_academic_year ) as `Data` 
    );
    
SELECT s.student_id,s.student_name,ifnull(e.marks,0) 'marks' from tmp_students s
    LEFT join exam_marks e 
    on e.student_id=s.student_id and 		
    e.academicyear=p_academic_year and 
    e.subject_id = p_subject and 
    e.exam_id =p_exam and e.status=0 ORDER by student_name asc ;
    END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `fetch_parent_dashboard` (IN `p_sponserId` INT)   BEGIN
SELECT ac.id into @current from academicyear ac where ac.is_active='yes';

SELECT 
COUNT(ss.student_id) as children,
SUM(sf.amount) as invoice

from student_promotion sp INNER JOIN students ss on ss.student_id=sp.student_id  JOIN class_sections cs on sp.to_class = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id  JOIN levels l on sl.level_id = l.id JOIN sponsors spn ON ss.sponsor_id=spn.id join school_fee sf on sf.school_id=sl.id 
JOIN academicyear acc ON sp.from_academic=acc.id
and sf.fee_type_id=7 and sf.academidyear_id=sp.from_academic where 
ss.sponsor_id= p_sponserId
AND sp.from_academic=61
and ss.status in (1,2)  ;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `fetch_PersonalMeeting` (IN `p_id` INT)   BEGIN
SELECT s.student_name "Student",sp.sponsor_name "Guardian",CASE WHEN m.noted=1 THEN 'Yes' ELSE 'No' END AS "Noted" ,chs.name "Status",m.issue "Reason",m.date_reported "Date" FROM meeting m JOIN students s on m.student_id = s.student_id JOIN sponsors sp on m.sponsor_id = sp.id JOIN challenge_status chs on m.status_id = chs.id WHERE m.id=p_id;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `fetch_student_exam_behavior` (IN `p_class` INT, IN `p_academic_year` INT, IN `p_subject` INT, IN `p_exam` INT)  NO SQL BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS (
SELECT * FROM( SELECT s.student_id,s.student_name from students s where s.class_id=p_class and s.current_academic =p_academic_year UNION all select s.student_id,s.student_name from student_promotion sp inner join students s on s.student_id=sp.student_id where sp.from_class=p_class and sp.from_academic=p_academic_year ) as `Data` 
    );
    
SELECT s.student_id,ifnull(e.marks,'-')marks   from tmp_students s
    LEFT join exam_marks e 
    on e.student_id=s.student_id and 		
    e.academicyear=p_academic_year and 
    e.subject_id = p_subject and 
    e.exam_id =p_exam and e.status=0 HAVING marks = '-';
    END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `fetch_student_exam_behavior1` (IN `p_class` INT, IN `p_class_section` VARCHAR(100), IN `p_academic_year` INT, IN `p_subject` INT, IN `p_exam` INT)  NO SQL BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS (
SELECT * FROM( SELECT s.student_id,s.student_name from students s 
INNER JOIN class_sections cs on cs.id=s.class_id 
JOIN classes cc on cc.id=cs.class_id 
 where cc.id=p_class and s.class_id like p_class_section and s.current_academic =p_academic_year and s.status=2 
    UNION all 
  select s.student_id,s.student_name from student_promotion sp 
  inner join students s on s.student_id=sp.student_id 
  INNER JOIN class_sections cs on cs.id=sp.from_class 
  INNER JOIN classes cc on cc.id=cs.class_id 
 where sp.from_class like p_class_section and cc.id=p_class and sp.from_academic=p_academic_year ) as `Data` 
    );
    
SELECT s.student_id,ifnull(e.marks,'-')marks   from tmp_students s
    LEFT join exam_marks e 
    on e.student_id=s.student_id and 		
    e.academicyear=p_academic_year and 
    e.subject_id = p_subject and 
    e.exam_id =p_exam and e.status=0 HAVING marks = '-';
    END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `fetch_student_exam_behavior11` (IN `p_class` INT, IN `p_class_section` VARCHAR(100), IN `p_academic_year` INT, IN `p_subject` INT, IN `p_exam` INT)  NO SQL BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS (
SELECT * FROM( SELECT s.student_id,s.student_name from students s 
INNER JOIN class_sections cs on cs.id=s.class_id 
JOIN classes cc on cc.id=cs.class_id 
 where cc.id=p_class and s.class_id like p_class_section and s.current_academic =p_academic_year and s.status=2 
    UNION all 
  select s.student_id,s.student_name from student_promotion sp 
  inner join students s on s.student_id=sp.student_id 
  INNER JOIN class_sections cs on cs.id=sp.from_class 
  INNER JOIN classes cc on cc.id=cs.class_id 
 where sp.from_class like p_class_section and cc.id=p_class and sp.from_academic=p_academic_year ) as `Data` 
    );
    
SELECT s.student_id,ifnull(e.marks,'-')marks   from tmp_students s
    LEFT join exam_marks e 
    on e.student_id=s.student_id and 		
    e.academicyear=p_academic_year and 
    e.subject_id = p_subject and 
    e.exam_id =p_exam and e.status=0 HAVING marks = '-';
    END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `fetch_student_marks` (IN `p_class` INT, IN `p_academic_year` INT, IN `p_subject` INT, IN `p_exam` INT)  NO SQL BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS 
 
select s.student_id,s.student_name from student_promotion sp inner join students s on s.student_id=sp.student_id where sp.to_class=p_class  AND sp.from_academic=p_academic_year  and s.status=2 ;
    
SELECT ifnull(e.id,0) as id,s.student_id,s.student_name,ifnull(e.marks,'') 'marks' from tmp_students s
    LEFT join exam_marks e 
    on e.student_id=s.student_id and 		
    e.academicyear=p_academic_year and 
    e.subject_id = p_subject and 
    e.exam_id =p_exam and e.status=0  ORDER by s.student_name ASC;
    END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `fetch_student_marks1` (IN `p_class` VARCHAR(100), IN `p_class_section` VARCHAR(100), IN `p_academic_year` INT, IN `p_subject` INT, IN `p_exam` INT)  NO SQL BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS 

select s.student_id,s.student_name,sp.from_class as class_id from student_promotion sp 
inner join students s on s.student_id=sp.student_id 
INNER JOIN  class_sections cs on cs.id=sp.to_class INNER join classes c on c.id=cs.class_id
where c.id = p_class AND sp.to_class like p_class_section AND sp.from_academic=p_academic_year 
    ;
    
SELECT ifnull(e.id,0) as id,s.student_id,s.student_name,ifnull(e.marks,0) 'marks',s.class_id from tmp_students s
    LEFT join exam_marks e 
    on e.student_id=s.student_id and 		
    e.academicyear=p_academic_year and 
    e.subject_id = p_subject and 
    e.exam_id =p_exam and e.status=0 ;
    END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `fetch_student_marks11` (IN `p_class` VARCHAR(100), IN `p_class_section` VARCHAR(100), IN `p_academic_year` INT, IN `p_subject` INT, IN `p_exam` INT)  NO SQL BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS (
SELECT * FROM( SELECT s.student_id,s.student_name,s.class_id from students s 
INNER JOIN  class_sections cs on cs.id=s.class_id INNER join classes c on c.id=cs.class_id
where c.id = p_class AND  s.class_id like p_class_section and s.current_academic =p_academic_year  and s.status=2
UNION all select s.student_id,s.student_name,sp.from_class as class_id from student_promotion sp 
inner join students s on s.student_id=sp.student_id 
INNER JOIN  class_sections cs on cs.id=sp.from_class INNER join classes c on c.id=cs.class_id
where c.id = p_class AND sp.from_class like p_class_section and sp.from_class!=0 AND sp.from_academic=p_academic_year ) as `Data` 
    );
    
SELECT ifnull(e.id,0) as id,s.student_id,s.student_name,ifnull(e.marks,0) 'marks',s.class_id from tmp_students s
    LEFT join exam_marks e 
    on e.student_id=s.student_id and 		
    e.academicyear=p_academic_year and 
    e.subject_id = p_subject and 
    e.exam_id =p_exam and e.status=0 ;
    END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `fetch_student_marks_bystudentId	` (IN `p_class` INT, IN `p_academic_year` INT, IN `p_subject` INT, IN `p_exam` INT, IN `p_student_id` VARCHAR(100))   BEGIN


CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS 
 
select s.student_id from student_promotion sp inner join students s on s.student_id=sp.student_id where sp.to_class=p_class  AND sp.from_academic=p_academic_year  and s.status=2 and s.student_id=p_student_id ;
    
SELECT ifnull(e.id,0) as id,s.student_id,ifnull(e.marks,'') 'marks' from tmp_students s
    LEFT join exam_marks e 
    on e.student_id=s.student_id and 		
    e.academicyear=p_academic_year and 
    e.subject_id = p_subject and 
    e.exam_id =p_exam and e.status=0  ;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `fetch_student_marks_bystudentId` (IN `p_class` INT, IN `p_academic_year` INT, IN `p_subject` INT, IN `p_exam` INT, IN `p_student_id` VARCHAR(100))   BEGIN


CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS 
 
select s.student_id from student_promotion sp inner join students s on s.student_id=sp.student_id where sp.to_class=p_class  AND sp.from_academic=p_academic_year  and s.status=2 and s.student_id=p_student_id ;
    
SELECT ifnull(e.id,0) as id,s.student_id,ifnull(e.marks,'') 'marks' from tmp_students s
    LEFT join exam_marks e 
    on e.student_id=s.student_id and 		
    e.academicyear=p_academic_year and 
    e.subject_id = p_subject and 
    e.exam_id =p_exam and e.status=0  ;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `fetch_Student_sponsor` (IN `p_student_id` VARCHAR(100))   BEGIN

SELECT DISTINCT sp.id ,sp.sponsor_name FROM student_behavior sb 
JOIN students s on sb.student_id = s.student_id 
JOIN sponsors sp ON s.sponsor_id=sp.id
WHERE sb.student_id=p_student_id;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `FindLastYearClass` (IN `p_student_id` VARCHAR(100), IN `p_result` INT)   BEGIN

SELECT ss.class_id into @class from students ss where ss.student_id= p_student_id;
SELECT er.Final into @result from exam_result_class er where er.id=p_result;
if EXISTS(SELECT sp.from_class,sp.from_academic from student_promotion sp where sp.student_id=p_student_id and sp.to_class=@class)THEN
SELECT sp.from_class,sp.from_academic into @last_class,@accy from student_promotion sp where sp.student_id=p_student_id and sp.to_class=@class;

SELECT ac.Name into @AccyName from academicyear ac where ac.id=@accy;
SELECT cl.class_name into @ClassName from class_sections cs INNER join classes cl on cl.id=cs.class_id WHERE CS.id=@accy;
SELECT @ClassName  as last_class, @AccyName as last_year  , @result as final;
ELSE
SELECT 'N/A'  as last_class, 'N/A' as last_year , @result as final;
END if;



END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `FindYearClass` (IN `p_student_id` VARCHAR(100), IN `p_result` INT, IN `p_acemadic_year` INT)   BEGIN

SELECT er.Final into @result from exam_result_class er where er.id=p_result;
if EXISTS(SELECT sp.to_class,sp.from_academic from student_promotion sp where sp.student_id=p_student_id and sp.from_academic=p_acemadic_year)THEN

SELECT sp.to_class,sp.from_academic into @last_class,@accy from student_promotion sp where 
sp.student_id=p_student_id and sp.from_academic=p_acemadic_year;
SELECT cl.class_name,ifnull((SELECT cl.class_name from classes cl where cl.id=cl.NextClass),'-') into @ClassName,@nextClass from class_sections cs INNER join classes cl on cl.id=cs.class_id  WHERE CS.id=@last_class;
SELECT ac.Name into @AccyName from academicyear ac where ac.id=@accy;
SELECT concat(SUBSTRING_INDEX(@AccyName, "-", -1),'-',SUBSTRING_INDEX(@AccyName, "-", -1)+1) into @nextaccy;
SELECT @ClassName  as currentclass,@nextClass as nextclass, @AccyName as currentaccy, @nextaccy nextaccy , @result as final;
ELSEIF EXISTS(SELECT * from students ss where ss.student_id=p_student_id and ss.current_academic=p_acemadic_year)THEN

SELECT ss.class_id,ss.current_academic into @last_class,@accy from students ss where ss.student_id=p_student_id and ss.current_academic=p_acemadic_year;

SELECT ac.Name into @AccyName from academicyear ac where ac.id=@accy;

SELECT cl.class_name,ifnull((SELECT cls.class_name from classes cls where cls.id=cl.NextClass),'-') into @ClassName,@nextClass from class_sections cs INNER join classes cl on cl.id=cs.class_id  WHERE CS.id=@last_class;

SELECT concat(SUBSTRING_INDEX(@AccyName, "-", -1),'-',SUBSTRING_INDEX(@AccyName, "-", -1)+1) into @nextaccy;

SELECT @ClassName  as currentclass,@nextClass as nextclass, @AccyName as currentaccy, @nextaccy nextaccy , @result as final;
ELSE
SELECT 'N/A'  as last_class, 'N/A' as last_year , @result as final;
END if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `FTStudentBus` (IN `p_id` INT)   BEGIN
SELECT sb.student_id,ss.student_name,sb.id,concat(bu.bus_name,' - ',bi.shift)as bus,sb.type,dd.Districts,sd.village,sb.StartDate,concat(dd.Districts,' ',sd.village) as divl,bi.id as bus_id,sb.from_month,sb.to_month,sb.location  FROM studentbus sb JOIN students ss on ss.student_id=sb.student_id and sb.type='Student' and sb.status=0 JOIN buslocationinfo bi on bi.id=sb.Bus JOIN buslocation bl on bl.bus_info=bi.id and bl.id=sb.location join buses bu on bu.id=bi.bus join sub_district sd on sd.id=bl.location  join location ll on ll.sub_district=sd.id 
JOIN districts dd on dd.id=sd.District where sb.id=p_id
UNION
SELECT pp.id,pp.StudentName,sb.id,concat(bu.bus_name,' - ',bi.shift)as bus,sb.type,dd.Districts,sd.village,sb.StartDate,concat(dd.Districts,' ',sd.village) as divl,bi.id as bus_id,sb.from_month,sb.to_month,sb.location FROM studentbus sb JOIN pupil pp on pp.id=sb.student_id and sb.type='pupil' and sb.status=0 JOIN buslocationinfo bi on bi.id=sb.Bus JOIN buslocation bl on bl.bus_info=bi.id and bl.id=sb.location join buses bu on bu.id=bi.bus join sub_district sd on sd.id=bl.location  join location ll on ll.sub_district=sd.id  
JOIN districts dd on dd.id=sd.District   where sb.id=p_id;
end$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `GenerateSalary` (IN `p_enter_by` INT, IN `p_school` INT, IN `p_month` VARCHAR(100))   BEGIN
if not EXISTS(select * from salaryinfo o where o.salary_month=p_month AND o.school=p_school)THEN
SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+03:00')into @date;
SELECT sum(e.lacagta) into @amount from employee e where e.school=p_school;
insert into salaryinfo (salary_month,school,Totalamount, enter_by, reg_date) VALUES
(p_month,p_school,@amount,p_enter_by,@date);
SET @salid = LAST_INSERT_ID();
INSERT INTO `salary_detail`(salid,employee_id, base_salary,  net_salary) 
SELECT @salid,e.id,e.lacagta,e.lacagta from employee e where e.school=p_school;
select count(*) into @total from salary_detail where salid= @salid;
SELECT concat('success|', @total,'Si sax ah ayaa loo bedelay')  ;

ELSE
select 'warning|hore ayuu u jiray';
end if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `GenerateWeeks` (IN `start_date` DATE, IN `end_date` DATE, `accy` INT)   BEGIN
    DECLARE current_start DATE;
    DECLARE current_end DATE;
    DECLARE week_num INT DEFAULT 1;

    
    SET current_start = start_date + INTERVAL (7 - DAYOFWEEK(start_date)) % 7 DAY;

    
    IF DAYOFWEEK(start_date) = 7 THEN
        SET current_start = start_date;
    END IF;

    
    DELETE FROM Weeks WHERE StartDate >= start_date AND EndDate <= end_date;

    
    WHILE current_start <= end_date DO
        SET current_end = LEAST(current_start + INTERVAL 6 DAY, end_date);

        INSERT INTO Weeks (WeekNumber, StartDate, EndDate,accy)
        VALUES (week_num, current_start, current_end,accy);

        SET current_start = current_end + INTERVAL 1 DAY;
        SET week_num = week_num + 1;
    END WHILE;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `Gfetchinvoice` (IN `p_fee` INT)   BEGIN

if(p_fee=2)THEN
SELECT  CONCAT('E-',max(CAST(SUBSTRING_INDEX(boono, '-', -1) AS SIGNED))+1) as student_id FROM cash_receipt  WHERE status=1 and fee_type=2 and boono not in ('E-82209','E-81334','E-81332','E-81333','E-81377','E-82702','E-84565','E-84990');
ELSEIF(p_fee=8)THEN
SELECT CONCAT('B-', IFNULL(LPAD(max(CAST(SUBSTRING_INDEX(boono, '-', -1) AS SIGNED))+1, 4, '0'), '0001')) AS student_id
FROM cash_receipt
WHERE status = 1 AND fee_type = 8;


ELSE

SELECT CONCAT('RV-',max(CAST(SUBSTRING_INDEX(boono, '-', -1) AS SIGNED))+1) as student_id FROM cash_receipt  WHERE status=1 and fee_type!=2;
END if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `ListBus` (IN `p_user` INT)   BEGIN
 SELECT GROUP_CONCAT( DISTINCT sc.id SEPARATOR ',')  into @school from school_levels sl 
		join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user
		JOIN schools sc on sl.school_id = sc.id;
        
SELECT bs.bus_name ,bs.number,bs.NoStudent,dd.driver_name,sc.school_name ,CONCAT('<a href="" data-id="',bs.id,'" data-bus_name="',bs.bus_name,'" data-number="',bs.number,'"  data-NoStudent="',bs.NoStudent,'" data-driver_id="',dd.id,'"class="btn btn-xs update_link"><i class="fas fa-edit" style="color:black;"></i></a>') `Edit` FROM buses bs JOIN driver dd on dd.id=bs.driver_id JOIN schools sc on sc.id=dd.school where  FIND_IN_SET(dd.school, @school) ;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `ListBusLocation` (IN `p_user` INT)   BEGIN
SELECT GROUP_CONCAT( DISTINCT sc.id SEPARATOR ',')  into @school from school_levels sl 
		join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user
		JOIN schools sc on sl.school_id = sc.id;
        
SELECT b.bus_name,shift,CONCAT('<a href="" data-id="',bl.id,'" data-shift="',bl.shift,'"  data-bus="',bl.bus,'" data-village="',GROUP_CONCAT(ll.location SEPARATOR ','),'"  class="btn btn-xs update_link"><i class="fas fa-edit" style="color:blue;"></i></a>') `Edit`
from buslocationinfo bl JOIN buslocation ll on ll.bus_info=bl.id JOIN buses b on b.id=bl.bus  JOIN driver dd on dd.id=b.driver_id where ll.status=0 and  FIND_IN_SET(dd.school, @school)  GROUP by bl.id;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `ListDriver` (IN `p_user` INT)   BEGIN
 SELECT GROUP_CONCAT( DISTINCT sc.id SEPARATOR ',')  into @school from school_levels sl 
		join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user
		JOIN schools sc on sl.school_id = sc.id;
SELECT dr.driver_name "Magaca Darawalka",dr.phone "Lambarka",sc.school_name as Iskuul,CONCAT('<a href="" data-id="',dr.id,'" data-school="',dr.school,'" data-driver_name="',dr.driver_name,'" data-phone="',dr.phone,'"class="btn btn-xs update_link"><i class="fas fa-edit" style="color:blue;"></i></a>') `Edit`, CONCAT('<a href="" data-id="',dr.id,'" class="btn   btn-Light btn-sm delete_link"><i class="fas fa-trash"></a>')
     as
     `Delete` FROM driver dr JOIN schools sc on sc.id=dr.school where FIND_IN_SET(dr.school, @school);

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `ListExpenseReciept` (IN `p_from` DATE, IN `p_to` DATE, IN `p_type` VARCHAR(100), IN `p_school` VARCHAR(100))   BEGIN

SELECT ir.id, ir.date, ir.type, ir.name, ir.amount, ir.paid, ir.amount - ir.paid AS Balance, ir.remark, em.name AS users,
CONCAT('<a href="" data-id="', ir.id, '" data-href="../Form/ExpenseBill.php" data-table="ExpenseBill" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>
<a href="#" value="', ir.id, '" class="btn btn-xs ml-02 delete_link"><i class="fa fa-times" aria-hidden="true"></i></a>
<a href="#" style="visibility:', IF(ir.paid = ir.amount, 'hidden', 'visible'), '" value="', ir.id, '" class="btn btn-xs ml-02 paid_link"><i class="fas fa-money-check" aria-hidden="true"></i></a>
<a href="#" value="', ir.id, '" class="btn btn-xs ml-02 print_link"><i class="fa fa-print" aria-hidden="true"></i></a>') AS `Edit`
FROM Expensebill ir
JOIN user u ON u.id = ir.user
JOIN employee em ON em.id = u.employee_id
WHERE ir.status = 1
AND ir.date BETWEEN p_from AND p_to
AND (ir.type = p_type OR p_type = '%')
AND ir.school = p_school;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `ListFeeType` ()   SELECT ft.fee_name "Nooca Lacagta",ft.Description "Faahfaahin",ft.pay_method "Habka bixinta",CONCAT('<a href="" data-id="',ft.id,'" data-fee_name="',ft.fee_name,'" data-Description="',ft.Description,'"  data-pay_method="',ft.pay_method,'"class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM fee_type ft$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `ListIncomeReciept` (IN `p_from` DATE, IN `p_to` DATE, IN `p_type` VARCHAR(100), IN `p_school` INT)   BEGIN
SELECT ir.id,ir.date,ip.Name as Type ,ir.name,ir.amount,rm.method,ir.remark,em.name as users,CONCAT('<a href="" data-id="',ir.id,'" data-href="../Form/IncomeReciept.php" data-table="IncomeRecipet" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a><a href="#" value="',ir.id,'" class="btn btn-xs ml-02 delete_link"><i class="fa fa-times" aria-hidden="true"></i></a><a href="#" value="',ir.id,'" class="btn btn-xs ml-02 print_link"><i class="fa fa-print" aria-hidden="true"></i></a>') `Edit` from incomereciept ir join receipt_method rm on  ir.recieptby=rm.id JOIN incometype ip on ip.id=ir.type 
JOIN user u on u.id=ir.user JOIN employee em on em.id=u.employee_id
where ir.status=1 and ir.date BETWEEN p_from and p_to and ip.id like p_type and ir.school LIKE p_school;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `ListLocation` (IN `p_accy` INT)   SELECT dd.Districts "Degmada",COUNT(*) as No,CONCAT('<a href="" data-accy="',lc.accy,'" data-District="',dd.id,'"   class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>','<a href="" data-accy="',lc.accy,'" data-District="',dd.id,'"   class="btn btn-xs view"><i class="fas fa-eye"></i></a>') `Edit` FROM location lc JOIN districts dd ON dd.id=lc.District where lc.accy=p_accy GROUP by dd.id$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `ListReceiptMethod` ()   SELECT rm.method "Nooca",rm.description "Faahfaahin",CONCAT('<a href="" data-id="',rm.id,'" data-method="',rm.method,'" data-description="',rm.description,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM receipt_method rm$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `ListSalaryDeatailAuth` (IN `p_salid` INT)   BEGIN
SELECT sd.id,em.id as Empid,em.name,sd.net_salary,sc.school_name,case when sd.status =0 then 'Pending'
when sd.status =1 then 'Deleted' 
when sd.status =2 then 'Success' else '' end as status FROM salary_detail sd
join employee em on em.id=sd.employee_id
join schools sc on sc.id=em.school
WHERE  sd.salid=p_salid;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `ListScholarship` (IN `p_user` INT)   BEGIN
 SELECT GROUP_CONCAT( DISTINCT sc.id SEPARATOR ',')  into @school from school_levels sl 
		join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user
		JOIN schools sc on sl.school_id = sc.id;
        
SELECT sc.id as rowid ,sb.student_id,sb.type,ss.student_name,bu.bus_name,(sc.amount*ll.amount) as amount,concat(dd.Districts,' (',sd.village,')') as district,ssc.school_name FROM scholarship sc join studentbus sb on sb.student_id=sc.student_id and sb.type=sc.type JOIN buslocationinfo bi on bi.id=sb.Bus JOIN buslocation bl on bl.bus_info=bi.id and bl.id=sb.location join buses bu on bu.id=bi.bus join sub_district sd on sd.id=bl.location   join location ll on ll.sub_district=sd.id
JOIN districts dd on dd.id=sd.District JOIN students ss on ss.student_id=sb.student_id and sb.type='student' 
JOIN driver dr on dr.id=bu.driver_id JOIN schools ssc on ssc.id=dr.school where FIND_IN_SET(dr.school, @school)
and  ss.status=2 and  sb.status=0 and sc.status=1

UNION ALL

SELECT sc.id as rowid,p.id,sb.type,p.StudentName,bu.bus_name,(sc.amount*ll.amount) as amount,concat(dd.Districts,' (',sd.village,')') as district,ssc.school_name FROM scholarship sc join studentbus sb on sb.student_id=sc.student_id and sb.type=sc.type JOIN buslocationinfo bi on bi.id=sb.Bus JOIN buslocation bl on bl.bus_info=bi.id and bl.id=sb.location join buses bu on bu.id=bi.bus join sub_district sd on sd.id=bl.location   join location ll on ll.sub_district=sd.id
JOIN districts dd on dd.id=sd.District JOIN pupil p on p.id=sb.student_id and sb.type='pupil' JOIN driver dr on dr.id=bu.driver_id JOIN schools ssc on ssc.id=dr.school where sb.status=0 and sc.status=1 and  FIND_IN_SET(dr.school, @school) ;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `ListSchoolFees` ()   SELECT concat(s.school_name ,' (',l.name,') ') as "Magaca Iskuulka",ft.fee_name "Nooca Lacagta",sf.amount "Lacagta",a.Name "Sanad Waxbarasheedka",
CONCAT('<a href="" data-id="',sf.id,'" data-level="',sf.school_id,'" data-fee="',sf.fee_type_id,'"  data-amount="',sf.amount,'" data-accy="',sf.academidyear_id,'"  class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') as  'Edit',CONCAT('<a href="" data-id="',sf.id,'" class="btn btn-xs delete_link"><i class="fas fa-trash-alt"></i></a>')
     as
     `Delete`
FROM school_fee sf 
join school_levels sl on sl.id =sf.school_id
join schools s on sl.school_id = s.id join levels l on l.id=sl.level_id join fee_type ft on sf.fee_type_id= ft.id join academicyear a on sf.academidyear_id= a.id$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `ListStudentBus` (IN `p_bus` INT, IN `p_location` VARCHAR(100), IN `p_user_id` INT)   BEGIN
SELECT sb.id,sb.student_id,ss.student_name,sb.type,dd.Districts,sd.village,sb.StartDate,sp.sponsor_name,sp.mobile,concat(l.name, '-' ,c.class_name,'/',se.section) as ClassName FROM studentbus sb JOIN students ss on ss.student_id=sb.student_id and sb.type='Student' and sb.status=0
JOIN class_sections cs on ss.class_id = cs.id join classes c on cs.class_id = c.id 
join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id 
JOIN schools sc on sl.school_id = sc.id 
join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user_id
JOIN levels l on sl.level_id = l.id
JOIN sponsors sp on ss.sponsor_id=sp.id
JOIN buslocationinfo bi on bi.id=sb.Bus 
JOIN buslocation bl on bl.bus_info=bi.id and bl.id=sb.location 
join buses bu on bu.id=bi.bus join sub_district sd on sd.id=BL.location  join location ll on ll.sub_district=sd.id 
JOIN districts dd on dd.id=sd.District where sb.Bus=p_bus and sb.location like p_location and sb.status=0
UNION
SELECT sb.id as sbid,pp.id,pp.StudentName,sb.type,dd.Districts,sd.village,sb.StartDate,ps.sponsor_name,ps.mobile,pp.ClassName FROM studentbus sb JOIN pupil pp on pp.id=sb.student_id and sb.type='pupil' and sb.status=0 JOIN pupil_sponsors ps on pp.sponsor_id=ps.id JOIN buslocationinfo bi on bi.id=sb.Bus JOIN buslocation bl on bl.bus_info=bi.id and bl.id=sb.location join buses bu on bu.id=bi.bus join sub_district sd on sd.id=BL.location  join location ll on ll.sub_district=sd.id 
JOIN districts dd on dd.id=sd.District  where sb.Bus=p_bus and sb.location like p_location and sb.status=0 ;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `ListStudentCharge` (IN `p_bus` INT, IN `p_month` INT, IN `p_user` INT)   BEGIN
 SELECT GROUP_CONCAT( DISTINCT sc.id SEPARATOR ',')  into @school from school_levels sl 
		join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user
		JOIN schools sc on sl.school_id = sc.id;
        
SELECT sc.id as rowid ,sb.student_id,sb.type,ss.student_name,bu.bus_name,sc.amount as amount,concat(dd.Districts,' (',sd.village,')') as district,ssc.school_name FROM charges sc join studentbus sb on sb.student_id=sc.student_id and sb.type=sc.student_type JOIN buslocationinfo bi on bi.id=sb.Bus  JOIN buslocation bl on bl.bus_info=bi.id and bl.id=sb.location join buses bu on bu.id=bi.bus join sub_district sd on sd.id=bl.location  join location ll on ll.sub_district= sd.id
JOIN districts dd on dd.id=sd.District JOIN students ss on ss.student_id=sb.student_id and sb.type='student' 
JOIN driver dr on dr.id=bu.driver_id JOIN schools ssc on ssc.id=dr.school where FIND_IN_SET(dr.school, @school)
and  ss.status=2 and  sb.status=0 and sc.month=p_month and bi.id=p_bus

UNION ALL

SELECT sc.id as rowid,p.id,sb.type,p.StudentName,bu.bus_name,sc.amount as amount,concat(dd.Districts,' (',sd.village,')') as district,ssc.school_name FROM charges sc join studentbus sb on sb.student_id=sc.student_id and sb.type=sc.student_type JOIN buslocationinfo bi on bi.id=sb.Bus JOIN buslocation bl on bl.bus_info=bi.id and bl.id=sb.location join buses bu on bu.id=bi.bus join sub_district sd on sd.id=bl.location  join location ll on ll.sub_district= sd.id 
JOIN districts dd on dd.id=sd.District JOIN pupil p on p.id=sb.student_id and sb.type='pupil' JOIN driver dr on dr.id=bu.driver_id JOIN schools ssc on ssc.id=dr.school where sb.status=0  and  FIND_IN_SET(dr.school, @school)  and sc.month=p_month and bi.id=p_bus;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `ListStudentFood` (IN `p_level` INT, IN `p_shift` VARCHAR(100), IN `p_class_id` VARCHAR(100), IN `p_user` INT)   BEGIN
SELECT max(id) into @accy from academicyear;
SELECT s.student_id,s.student_name,sf.from_month,sf.id,mn.months ,sf.StartDate,sf.to_month FROM studentfood sf 
join student_promotion ssp on ssp.student_id=sf.student_id
join students s on s.student_id=ssp.student_id and ssp.from_academic=@accy
JOIN class_sections cs on ssp.to_class = cs.id 
join classes c on cs.class_id = c.id 
join sections se on cs.section_id = se.id 
JOIN school_levels sl on c.school_level_id = sl.id 
JOIN schools sc on sl.school_id = sc.id 
join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user
JOIN levels l on sl.level_id = l.id
JOIN months mn on mn.id=sf.from_month
inner join sponsors sp on sp.id=s.sponsor_id 
where  sl.id like p_level and cs.id like p_class_id and  c.shift like p_shift and s.status in (1,2) and sf.status=0 and sf.accy=@accy;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `ListVillage` ()   SELECT dd.Districts "Degmada",COUNT(*) as No,CONCAT('<a href="" data-District="',dd.id,'"   class="btn btn-xs update_link"><i class="fas fa-edit" style="color:black;"></i></a>','<a href="" data-District="',dd.id,'"   class="btn btn-xs view"><i class="fas fa-eye" style="color:black;"></i></a>') `Edit` FROM sub_district lc JOIN districts dd ON dd.id=lc.District GROUP by dd.id$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `List_employee_salary` (IN `p_school` INT)   BEGIN


SELECT 
   sc.school_name as school,
    sl.type,
    e.name as name ,
    sl.amount,
     sl.date,
     sl.user
FROM 
    salary sl 
    JOIN schools sc ON sl.school=sc.id 
    JOIN employee e ON sl.name=e.id
    WHERE sl.school=p_school;


END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `months` (IN `p_months` VARCHAR(100), IN `p_school_id` VARCHAR(50), IN `p_class_id` VARCHAR(50), IN `p_academic_year_id` VARCHAR(50), IN `p_company_id` INT, IN `p_fee_type` VARCHAR(50))  NO SQL BEGIN
SELECT concat(REPLACE(concat("'",p_months),"-","'>0 and '"),"'>0") into @zero;
SELECT @zero;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `Payment_Summary` (IN `p_student_id` VARCHAR(100), IN `p_accy` INT)   BEGIN
SELECT ft.id,cr.tran_date as DATE ,(CASE WHEN CR.status=1 THEN 'Payment' WHEN cr.status=2 THEN 'Cancel' WHEN cr.status=3 then 'Refund' END)AS 'TYPE',cr.boono as Invoice,ft.fee_name as ACCOUNT,(CASE when cr.fee_type=7 || cr.fee_type=8 || cr.fee_type=3  THEN GROUP_CONCAT( months.months ORDER BY months.id)  ELSE '-' END) AS Month, (CASE WHEN CR.status=1 THEN SUM(CR.amount) else 0.00 END)Amount  from cash_receipt cr INNER join fee_type ft on ft.id=cr.fee_type LEFT JOIN months ON  months.months=cr.month 
where cr.student_id=p_student_id and cr.academic_year=p_accy and cr.status!=2
group by cr.boono;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `Pivot` (IN `tbl_name` VARCHAR(99), IN `base_cols` VARCHAR(99), IN `pivot_col` VARCHAR(64), IN `tally_col` VARCHAR(64), IN `where_clause` VARCHAR(99), IN `order_by` VARCHAR(99))  SQL SECURITY INVOKER BEGIN
    
    
    SET @subq = CONCAT('SELECT DISTINCT ', pivot_col, ' AS val ',
                    ' FROM ', tbl_name, ' ', where_clause, ' ORDER BY 1');
    

    SET @cc1 = "CONCAT('SUM(IF(&p = ', &v, ', &t, 0)) AS ', &v)";
    SET @cc2 = REPLACE(@cc1, '&p', pivot_col);
    SET @cc3 = REPLACE(@cc2, '&t', tally_col);
    
    SET @qval = CONCAT("'\"', val, '\"'");
    
    SET @cc4 = REPLACE(@cc3, '&v', @qval);
    

    SET SESSION group_concat_max_len = 10000;   
    SET @stmt = CONCAT(
            'SELECT  GROUP_CONCAT(', @cc4, ' SEPARATOR ",\n")  INTO @sums',
            ' FROM ( ', @subq, ' ) AS top');
     select @stmt;
    PREPARE _sql FROM @stmt;
    EXECUTE _sql;                      
    DEALLOCATE PREPARE _sql;
    
    SET @stmt2 = CONCAT(
            'SELECT ',
                base_cols, ',\n',
                @sums,
                ',\n SUM(', tally_col, ') AS Total'
            '\n FROM ', tbl_name, ' ',
            where_clause,
            ' GROUP BY ', base_cols,
            '\n WITH ROLLUP',
            '\n', order_by
        );
    select @stmt2;                    
    PREPARE _sql FROM @stmt2;
    EXECUTE _sql;                     
    DEALLOCATE PREPARE _sql;
    
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `P_Papulate_students` (IN `P_id` INT)  NO SQL SELECT
        `id`,
        `StudentName`,
        `Gender`,
        `DOB`,
        `POB`,
        age,
        `mothername`,
        `SponsoredBy`,
        `Address`,
        `Phone`,
        `Fee`,
        `Fee_Status`,
        `ContactName`,
        `ContactAddress`,
        `ContactPhone`,
        `TransferedAcademicYear`,
        `RegAcademicYear`,
        `SchoolName`,
        `SchoolAddress`,
        `Ambarella`,
        `CompletedClass`,
        `TypeOfDeclaration`,
        `DateOfIssue`,
        `class_id`,
        `ClassType`,
        `RegType`,
        `EntryDate`,
        `Photo`
    FROM
        `students`
         WHERE
        id = P_id$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `Qsp_view_student_result1` (IN `p_result` INT, IN `p_classname` VARCHAR(255), IN `p_exam` VARCHAR(100), IN `p_accy` INT)   BEGIN
   DECLARE done INT DEFAULT 0;
    DECLARE v_id INT;
    DECLARE v_class VARCHAR(255);
    DECLARE v_academic_year VARCHAR(255);
    DECLARE v_field_value VARCHAR(255);  
    DECLARE field_name VARCHAR(255);     
    
    
    DECLARE result_cursor CURSOR FOR 
        SELECT CC.id, CC.class, CC.academic_year 
        FROM exam_result_class CC 
        JOIN classes cl ON cl.id = CC.class
        JOIN school_levels sl ON sl.id = cl.school_level_id
        WHERE CC.academic_year = p_accy 
        AND cl.class_name = p_classname 
        AND CC.exam_result_info = p_result 
        AND sl.school_id <> 13;
    
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_results (
        id INT,
        field_name VARCHAR(255),
        value VARCHAR(255)
    );
ENd$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `Qsp_view_student_result2` (IN `p_result` INT, IN `p_classname` VARCHAR(255), IN `p_exam` VARCHAR(100), IN `p_accy` INT)   BEGIN

    DECLARE done INT DEFAULT 0;
    DECLARE v_id INT;
    DECLARE v_class VARCHAR(255);
    DECLARE v_academic_year VARCHAR(255);
 
    
    DECLARE result_cursor CURSOR FOR 
        SELECT CC.id, CC.class, CC.academic_year 
        FROM exam_result_class CC 
        JOIN classes cl ON cl.id = CC.class
        JOIN school_levels sl ON sl.id = cl.school_level_id
        WHERE CC.academic_year = p_accy 
        AND cl.class_name = p_classname 
        AND CC.exam_result_info = p_result 
        AND (
    (cl.class_name = 'class 8') 
    OR (sl.school_id <> 13)
  );
    
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
   DROP TABLE IF EXISTS finalresult;
    
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_results (
        
        
        result_id INT,
        result_data VARCHAR(255)
    );
    
    
    OPEN result_cursor;
    
    
    read_loop: LOOP
        FETCH result_cursor INTO v_id, v_class, v_academic_year;
        IF done THEN
            LEAVE read_loop;
        END IF;

        
        CALL sp_view_student_result_Q(v_id, '%', '0', p_exam);
      
     

    END LOOP;

    
    CLOSE result_cursor;

  
CREATE TEMPORARY TABLE displatre AS
SELECT 
    finalresult.*, 
    @rank := IF(@prev_total = total, @rank, @rank + 1) AS `rank`,
    @prev_total := total as prev_total
FROM 
    finalresult,
    (SELECT @rank := 0, @prev_total := NULL) AS vars
ORDER BY 
    total DESC;
    ALTER TABLE displatre  DROP prev_total; 
select * from displatre;
   


END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `Qsp_view_student_result3` (IN `p_result` INT, IN `p_classname` VARCHAR(255), IN `p_exam` VARCHAR(100), IN `p_accy` INT)   BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE done_dynamic INT DEFAULT 0;  
    DECLARE v_id INT;
    DECLARE v_class VARCHAR(255);
    DECLARE v_academic_year VARCHAR(255);
    DECLARE v_field_value VARCHAR(255);  
    DECLARE field_name VARCHAR(255);     

    
    DECLARE result_cursor CURSOR FOR 
        SELECT CC.id, CC.class, CC.academic_year 
        FROM exam_result_class CC 
        JOIN classes cl ON cl.id = CC.class
        JOIN school_levels sl ON sl.id = cl.school_level_id
        WHERE CC.academic_year = p_accy 
        AND cl.class_name = p_classname 
        AND CC.exam_result_info = p_result 
        AND sl.school_id <> 13;
    
    
    DECLARE result_cursor_dynamic CURSOR FOR
        SELECT field_name, field_value  
        FROM finalresult;           

    
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND 
        SET done = 1, done_dynamic = 1;

    
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_results (
        id INT,
        field_name VARCHAR(255),
        value VARCHAR(255)
    );

    
    OPEN result_cursor;

    
    read_loop: LOOP
        FETCH result_cursor INTO v_id, v_class, v_academic_year;
        IF done THEN
            LEAVE read_loop;
        END IF;

        
        CALL sp_view_student_result_Q(v_id, '%', '0', p_exam);

        
        OPEN result_cursor_dynamic;

        
        dynamic_loop: LOOP
            FETCH result_cursor_dynamic INTO field_name, v_field_value;
            IF done_dynamic THEN
                LEAVE dynamic_loop;
            END IF;

            
            INSERT INTO temp_results (id, field_name, value)
            VALUES (v_id, field_name, v_field_value);
        END LOOP;

        
        CLOSE result_cursor_dynamic;

        
        SET done_dynamic = 0;

    END LOOP;

    
    CLOSE result_cursor;

    
    
    
    

    
    DROP TEMPORARY TABLE IF EXISTS temp_results;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `ReadWeeksWithEvents` (`accy` INT)   BEGIN
    SELECT 
        w.WeekNumber,
        w.StartDate AS WeekStart,
        w.EndDate AS WeekEnd,
        e.EventName,
        CASE 
            WHEN e.IsRecurring = 1 THEN 'Recurring'
            ELSE 'One-time'
        END AS EventType,
        e.EventDate
    FROM Weeks w
    LEFT JOIN Events e
        ON (e.IsRecurring = 1 OR e.EventDate BETWEEN w.StartDate AND w.EndDate)
        where e.accy=accy
    ORDER BY w.WeekNumber, e.EventDate;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `RPExpenseType` (IN `user_id` INT)   BEGIN
SELECT i.Name 'Type',i.remark "Faahfaahin",CONCAT('<a href=""  data-id="',i.id,'" data-Name="',i.Name,'" data-Description="',i.remark,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM expensetype i
JOIN schools sc on i.sch_id = sc.id
join school_levels sl on sc.id=sl.school_id
JOIN levels l on sl.level_id = l.id join level_permission lp on lp.level_id=sl.id and lp.user_id=user_id
GROUP BY i.Name;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `RPIncomeType` ()   BEGIN
SELECT i.Name 'Type',i.remark "Faahfaahin",CONCAT('<a href=""  data-id="',i.id,'" data-Name="',i.Name,'" data-Description="',i.remark,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM incometype i;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `RPTExamSheet` (IN `p_accy` INT, IN `p_class` INT, IN `p_section` VARCHAR(100))   BEGIN
SELECT ss.student_id,ss.student_name from student_promotion sp join students ss on ss.student_id=sp.student_id JOIN class_sections cl on cl.id=sp.to_class where cl.class_id= p_class and sp.to_class like p_section and sp.from_academic=p_accy ORDER by ss.student_name ASC;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_class_timetable` (IN `p_class_id` INT, IN `p_days` VARCHAR(100))  NO SQL BEGIN
select REPLACE(p_days,"-",",") into @days;
SELECT concat(REPLACE(concat("`",p_days),"-","`>0 and `"),"`>0") into @iszero;
create table d( day text );
insert into d values(@days);

drop temporary table if exists temp_days;
create temporary table temp_days( day char(255) );
set @sql1 = concat("insert into temp_days(day) values ('", replace(( select group_concat(distinct day) as data from d), ",", "'),('"),"');");
prepare stmt1 from @sql1;
execute stmt1;


drop temporary table if exists tmp_timetable;
CREATE TEMPORARY TABLE tmp_timetable
SELECT tt.id,concat(c.class_name,'(',se.section,')') as class_name,s.subject_name,tt.day_name,tt.start_time,tt.end_time FROM timetables tt JOIN teacher_subjects ts on tt.teacher_subject_id = ts.id JOIN subjects s on ts.subject_id = s.id JOIN class_sections cs on ts.class_section_id = cs.id JOIN classes c on cs.class_id = c.id JOIN sections se on cs.section_id = se.id WHERE cs.id =p_class_id;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'ifnull(case when day_name = ''',
       day_name,
      ''' then concat(subject_name,'','',start_time,'','',end_time) end,''not scheduled'') AS `',
      day_name, '`' 
    ) 
  ) INTO @sql 
  FROM tmp_timetable WHERE day_name IN((SELECT day FROM temp_days)) ;
  SELECT @sql;
 SET @sql = CONCAT('SELECT ', @sql, ' \r\n FROM tmp_timetable ');
 
 SET @sql=CONCAT("CREATE TEMPORARY  Table IF NOT EXISTS TempTble2 ",@sql,";");
set @sql2 =concat(" select * from TempTble2 where Saturday != 'not scheduled'"); 

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PREPARE stmt2 FROM @sql2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_class_timetable2` (IN `p_class_id` INT, IN `p_day` VARCHAR(50))  NO SQL BEGIN

SELECT tt.id,concat(c.class_name,'(',se.section,')') as class_name,tt.day_name,s.subject_name,tt.start_time,tt.end_time FROM timetables tt JOIN teacher_subjects ts on tt.teacher_subject_id = ts.id JOIN subjects s on ts.subject_id = s.id JOIN class_sections cs on ts.class_section_id = cs.id JOIN classes c on cs.class_id = c.id JOIN sections se on cs.section_id = se.id WHERE cs.id =p_class_id and day_name = p_day;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_daily_rv` (IN `p_fee_type` INT, IN `p_academidyear_id` VARCHAR(50), IN `p_from` DATE, IN `p_to` DATE, IN `p_user_id` INT)  NO SQL BEGIN
CREATE TEMPORARY TABLE dailyfee as
SELECT ac.Name,count(cash_receipt.student_id)  as total_students,(Case when cash_receipt.fee_type=7 THEN month ELSE fee_type.fee_name end) month,sum(cash_receipt.amount) as TotalAmount FROM cash_receipt JOIN fee_type on fee_type.id=cash_receipt.fee_type    JOIN academicyear ac on ac.id=academic_year
JOIN class_sections cs on cash_receipt.class_id = cs.id 
join classes c on cs.class_id = c.id 
JOIN school_levels sl on c.school_level_id = sl.id 
 INNER join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user_id
wHERE cash_receipt.tran_date between p_from and p_to and cash_receipt.status =1 and cash_receipt.fee_type = p_fee_type and cash_receipt.academic_year like p_academidyear_id  
group by cash_receipt.month,AC.id ORDER BY cash_receipt.month;

SELECT Name,GROUP_CONCAT(total_students SEPARATOR '<br>') as total_students,GROUP_CONCAT(month SEPARATOR '<br>') as month,GROUP_CONCAT(TotalAmount SEPARATOR '<br>')as TotalAmount,sum(TotalAmount)as total FROM dailyfee GROUP by Name;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_exam_result` ()  NO SQL BEGIN

CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (select s.subject_name,em.student_id,ifnull((select ifnull(marks,0) from exam_marks where subject_id=ec.subject_id and student_id=ss.student_id),0) 'Marks',ifnull((select ifnull(sum(marks),0) from exam_marks where  student_id=ss.student_id),0) 'Total' from exam_class ec inner join exam_type et on et.id=ec.exam_type_id inner join classes c on c.id=ec.class_id inner join subjects s on s.id=ec.subject_id inner join exam_marks em on em.class_id=ec.class_id INNER join students ss on ss.student_id=em.student_id);
CREATE TEMPORARY TABLE IF NOT EXISTS table3 AS (select et.type,c.class_name,s.subject_name,em.student_id ,ss.student_name ,em.marks from exam_class ec inner join exam_type et on et.id=ec.exam_type_id inner join classes c on c.id=ec.class_id inner join subjects s on s.id=ec.subject_id inner join exam_marks em on em.class_id=ec.class_id INNER join students ss on ss.student_id=em.student_id);

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(pa.subject_name = ''',
      subject_name,
      ''', pa.marks, NULL)) AS ',
     subject_name
    )
  ) INTO @sql
FROM table2;

SET @sql = CONCAT('SELECT p.student_id ''Student ID''
                   , p.student_name ''Student Name''
                    , ', @sql, ' ,pa.total as Total
                   FROM table3 p
                  LEFT JOIN table2 AS pa 
                   ON p.student_id = pa.student_id
                   GROUP BY p.student_id');
SET @createSQL = CONCAT('CREATE TEMPORARY TABLE tmp AS ', @sql);
PREPARE stmt FROM @createSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT * from tmp;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_exam_result_copy` ()  NO SQL BEGIN

CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (select s.subject_name,em.student_id,ifnull((select ifnull(marks,0) from exam_marks where subject_id=ec.subject_id and student_id=ss.student_id),0) 'Marks',ifnull((select ifnull(sum(marks),0) from exam_marks where  student_id=ss.student_id),0) 'Total' from exam_class ec inner join exam_type et on et.id=ec.exam_type_id inner join classes c on c.id=ec.class_id inner join subjects s on s.id=ec.subject_id inner join exam_marks em on em.class_id=ec.class_id INNER join students ss on ss.student_id=em.student_id);
CREATE TEMPORARY TABLE IF NOT EXISTS table3 AS (select et.type,c.class_name,s.subject_name,em.student_id ,ss.student_name ,em.marks from exam_class ec inner join exam_type et on et.id=ec.exam_type_id inner join classes c on c.id=ec.class_id inner join subjects s on s.id=ec.subject_id inner join exam_marks em on em.class_id=ec.class_id INNER join students ss on ss.student_id=em.student_id);

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(pa.subject_name = ''',
      subject_name,
      ''', pa.marks, NULL)) AS ',
     subject_name
    )
  ) INTO @sql
FROM table2;

SET @sql = CONCAT('SELECT p.student_id ''Student ID''
                   , p.student_name ''Student Name''
                    , ', @sql, ' ,pa.total as Total
                   FROM table3 p
                  LEFT JOIN table2 AS pa 
                   ON p.student_id = pa.student_id
                   GROUP BY p.student_id');
SET @createSQL = CONCAT('CREATE TEMPORARY TABLE tmp AS ', @sql);
PREPARE stmt FROM @createSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT * from tmp;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_free_students` (IN `_class_id` INT)  NO SQL select s.id 'Student_id',s.StudentName,concat(sc.school_name,' ',l.name) Dugsi,c.class_name Fasal from students s JOIN classes c on s.class_id = c.id join schools sc on c.school_id = sc.id join levels l on l.id = sc.level_id WHERE s.Fee_Status = 'free' and c.id =_class_id$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_graduate_students` (IN `_class_id` INT, IN `_academidyear_id` INT)  NO SQL BEGIN
select s.id 'Student_id',s.StudentName,concat(sc.school_name,' ',l.name) Dugsi,c.class_name Fasal,a.Name 'Graduation_year' from students s JOIN classes c on s.class_id = c.id join schools sc on c.school_id = sc.id join levels l on l.id = sc.level_id join academicyear a on a.id = s.g_academicyear WHERE s.is_graduation=1 and s.g_academicyear =_academidyear_id;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_Guardian_Invoice` (IN `p_sponsor` VARCHAR(100), IN `p_accy` VARCHAR(50))   BEGIN
SELECT 
concat(spn.id,'-',spn.sponsor_name,' ',spn.mobile) as Masuulka,
concat(spn.sponsor_name,' ',spn.mobile) as Magaca_Masuulka,
acc.Name as accy,
CASE 
        WHEN SUM(sf.amount) IS NULL THEN '0.00'
        ELSE SUM(sf.amount) 
    END AS Lacagta
from student_promotion sp INNER JOIN students ss on ss.student_id=sp.student_id  JOIN class_sections cs on sp.to_class = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id  JOIN levels l on sl.level_id = l.id JOIN sponsors spn ON ss.sponsor_id=spn.id join school_fee sf on sf.school_id=sl.id 
JOIN academicyear acc ON sp.from_academic=acc.id
and sf.fee_type_id=7 and sf.academidyear_id=sp.from_academic where 
ss.sponsor_id=p_sponsor 
AND sp.from_academic=p_accy 
and ss.status in (1,2);
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_header_info` (IN `school` INT, IN `class` INT, IN `year` INT)  NO SQL BEGIN
select name  into @academicyear from academicyear where id=year;
SELECT concat(sc.school_name,' ',l.name) as school_name ,c.class_name,@academicyear as academic_year FROM classes c join schools sc on c.school_id=sc.id join levels l on l.id = sc.level_id WHERE sc.id=school and c.id = class ;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_incomeSM` (IN `p_school` VARCHAR(50), IN `p_user` INT, IN `p_from` DATE, IN `p_to` DATE)   BEGIN
SELECT 'Previews' as Type,sum(income)-sum(expense) as Amount from (
SELECT ifnull(sum(cr.amount),0) as income,0 expense from cash_receipt cr
     JOIN class_sections cs on cr.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id 
 INNER join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user
 JOIN levels l on sl.level_id = l.id  
    
where cr.tran_date < p_from  and cr.status=1 and sl.school_id LIKE p_school
UNION ALL
SELECT 0 as Sincome,ifnull(sum(ex.amount),0) expense from expensebill ex where ex.status=1 and ex.date < p_from and ex.school=p_school ) as p

UNION all 
SELECT 'Income',ifnull(sum(cr.amount),0) as income from cash_receipt cr
 JOIN class_sections cs on cr.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id 
 INNER join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user
 JOIN levels l on sl.level_id = l.id
where cr.tran_date BETWEEN p_from and p_to and cr.status=1 and sl.school_id LIKE p_school
UNION ALL
SELECT 'Expense',ifnull(-1*sum(ex.amount),0) expense from expensebill ex where ex.status=1 and ex.date BETWEEN p_from and p_to and ex.school LIKE p_school;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_list_studens_bus` (IN `p_bus_id` VARCHAR(50))  NO SQL BEGIN

select sb.bus_id ,b.bus_name ,sb.student_id,s.StudentName,sb.Address,
sp.sponsor_name,sc.school_name,c.class_name,sb.amount from students s JOIN sponsors sp on sp.id = s.sponsor_id JOIN classes c on c.id = s.class_id join schools sc on sc.id = c.school_id join  school_bus sb on sb.student_id = s.id
inner join buses b on b.id =sb.bus_id
where b.id  like p_bus_id 
group by sb.id ,b.bus_name ,sb.student_id,s.StudentName,sb.Address;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_num_students_byschool` (IN `p_school_id` VARCHAR(50), IN `p_company_id` INT)  NO SQL BEGIN
SELECT sc.id,sc.school_name,count(s.id) as num_students FROM students s join classes c on s.class_id=c.id JOIN schools sc on c.school_id = sc.id where sc.id like p_school_id and sc.company_id = p_company_id GROUP by sc.id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_Paid_Months_Report` (IN `p_months` VARCHAR(100), IN `p_school_id` VARCHAR(50), IN `p_class_id` VARCHAR(50), IN `p_academic_year_id` VARCHAR(50), IN `p_company_id` INT, IN `p_fee_type` VARCHAR(50))  NO SQL BEGIN
select REPLACE(p_months,"-",",") into @months;
SELECT concat(REPLACE(concat("`",p_months),"-","`>0 and `"),"`>0") into @iszero;
create table t( txt text );
insert into t values(@months);

drop temporary table if exists temp;
create temporary table temp( val char(255) );
set @sql1 = concat("insert into temp (val) values ('", replace(( select group_concat(distinct txt) as data from t), ",", "'),('"),"');");
prepare stmt1 from @sql1;
execute stmt1;


CREATE TEMPORARY TABLE Tmpcash
SELECT s.id,s.student_id 'student_id',s.student_name,s.is_graduation,c.class_name,sc.school_name,sp.sponsor_name,sp.mobile,cr.month,cr.amount,cr.status FROM students s join classes c on s.class_id = c.id join schools sc on c.school_id = sc.id join sponsors sp on s.sponsor_id = sp.id join cash_receipt cr on cr.student_id = s.id join academicyear a on cr.academic_year=a.id WHERE sc.id like p_school_id and c.id like p_class_id and a.id like p_academic_year_id and fee_type like p_fee_type and cr.company_id=p_company_id and s.is_graduation=0 and cr.status !=2 ;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'ifnull(SUM(case when month = ''',
      month,
      ''' then amount end),0) AS `',
      month, '`'
    )
  ) INTO @sql 
  FROM Tmpcash WHERE month IN((SELECT val from temp));
  
  
 SET @sql = CONCAT('SELECT id,student_id,StudentName,is_graduation,class_name,school_name,sponsor_name,mobile, ', @sql, ' \r\n                  FROM Tmpcash group by id \r\n ');

     
 SET @sql=CONCAT("CREATE TEMPORARY  Table IF NOT EXISTS TempTble2 ",@sql,";");
set @sql2 =concat(" select * from TempTble2 where ",@iszero); 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PREPARE stmt2 FROM @sql2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_receipt_student` (IN `p_boono` VARCHAR(100))   BEGIN
SELECT cr.student_id 'Student_id',s.student_name as student_name,ls.name as school_name ,concat(c.class_name,'/', sec.section) as class_name,GROUP_CONCAT(cr.month  ORDER by cr.id SEPARATOR ',')as month ,sum(cr.amount) as amount,cr.boono,cr.tran_date,a.Name as academicyear,rm.method as receipt_method,cr.usd_no  FROM cash_receipt cr join students s on s.student_id = cr.student_id JOIN class_sections cs on cs.id=cr.class_id join classes c on c.id = cs.class_id JOIN sections sec on sec.id=cs.section_id join school_levels sl on sl.id=c.school_level_id join levels ls on ls.id=sl.level_id  join fee_type ft on cr.fee_type = ft.id join academicyear a on a.id = cr.academic_year join receipt_method rm on cr.receipt_by = rm.id 
   

where cr.boono = p_boono   and cr.status=1 GROUP BY cr.student_id,cr.class_id
UNION 
SELECT cr.student_id 'Student_id',s.student_name as student_name,ls.name as school_name ,concat(c.class_name,'/', sec.section) as class_name, concat( GROUP_CONCAT(cr.month  ORDER by cr.id SEPARATOR ','),'  ','<p style="color:red;"><b>(Refund)</b></p>') as month ,sum(cr.amount)*(-1) as amount,cr.boono,cr.tran_date,a.Name as academicyear,rm.method as receipt_method,cr.usd_no FROM cash_receipt cr join students s on s.student_id = cr.student_id JOIN class_sections cs on cs.id=cr.class_id join classes c on c.id = cs.class_id JOIN sections sec on sec.id=cs.section_id join school_levels sl on sl.id=c.school_level_id join levels ls on ls.id=sl.level_id  join fee_type ft on cr.fee_type = ft.id join academicyear a on a.id = cr.academic_year join receipt_method rm on cr.receipt_by = rm.id 
  
where cr.boono = p_boono   and cr.status=3 GROUP BY cr.student_id,cr.class_id

;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_receipt_student1` (IN `p_boono` INT)  NO SQL BEGIN
SELECT cr.student_id 'Student_id',s.student_name as student_name,ls.name as school_name ,concat(c.class_name,'/', sec.section) as class_name, GROUP_CONCAT(cr.month  ORDER by cr.id SEPARATOR ',')as month ,sum(cr.amount) as amount,cr.boono,cr.tran_date,a.Name as academicyear,rm.method as receipt_method,cr.usd_no  FROM cash_receipt cr join students s on s.student_id = cr.student_id JOIN class_sections cs on cs.id=cr.class_id join classes c on c.id = cs.class_id JOIN sections sec on sec.id=cs.section_id join school_levels sl on sl.id=c.school_level_id join levels ls on ls.id=sl.level_id  join fee_type ft on cr.fee_type = ft.id join academicyear a on a.id = cr.academic_year join receipt_method rm on cr.receipt_by = rm.id 


where cr.boono = p_boono   and cr.status=1 GROUP BY cr.student_id,cr.class_id
UNION 
SELECT cr.student_id 'Student_id',s.student_name as student_name,ls.name as school_name ,concat(c.class_name,'/', sec.section) as class_name, concat( GROUP_CONCAT(cr.month  ORDER by cr.id SEPARATOR ','),'  ','<p style="color:red;"><b>(Refund)</b></p>') as month ,sum(cr.amount)*(-1) as amount,cr.boono,cr.tran_date,a.Name as academicyear,rm.method as receipt_method,cr.usd_no FROM cash_receipt cr join students s on s.student_id = cr.student_id JOIN class_sections cs on cs.id=cr.class_id join classes c on c.id = cs.class_id JOIN sections sec on sec.id=cs.section_id join school_levels sl on sl.id=c.school_level_id join levels ls on ls.id=sl.level_id  join fee_type ft on cr.fee_type = ft.id join academicyear a on a.id = cr.academic_year join receipt_method rm on cr.receipt_by = rm.id 

where cr.boono = p_boono   and cr.status=3 GROUP BY cr.student_id,cr.class_id

;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_receipt_student2` (IN `p_boono` VARCHAR(100))  NO SQL SELECT 
    cr.student_id 'Student_id',
    s.student_name AS student_name,
    concat(sc.school_name,' ',ls.name) AS school_name,
    concat(c.class_name,'/', sec.section) AS class_name,
    b.bus_name as 'Bus', 
    GROUP_CONCAT(cr.month ORDER by cr.id SEPARATOR ',') AS month,
    sum(cr.amount) AS amount,
    cr.boono,
    cr.tran_date,
    a.Name AS academicyear,
    rm.method AS receipt_method,
    cr.usd_no,
    cr.remark AS note  
FROM 
    cash_receipt cr 
    JOIN students s ON s.student_id = cr.student_id 
    JOIN class_sections cs ON cs.id = cr.class_id 
    JOIN classes c ON c.id = cs.class_id 
    JOIN sections sec ON sec.id = cs.section_id 
    JOIN school_levels sl ON sl.id = c.school_level_id 
    JOIN schools sc ON sc.id = sl.school_id 
    JOIN levels ls ON ls.id = sl.level_id 
    JOIN fee_type ft ON cr.fee_type = ft.id 
    JOIN academicyear a ON a.id = cr.academic_year 
    JOIN receipt_method rm ON cr.receipt_by = rm.id 
    JOIN studentbus sb ON cr.student_id = sb.student_id
    JOIN buses b ON sb.Bus = b.id 
WHERE 
    cr.boono = p_boono   
    AND cr.status = 1 
GROUP BY 
    cr.student_id, cr.class_id

UNION 

SELECT 
    cr.student_id 'Student_id',
    s.student_name AS student_name,
    ls.name AS school_name,
    concat(c.class_name,'/', sec.section) AS class_name,
    b.bus_name as 'Bus', 
    concat(GROUP_CONCAT(cr.month ORDER by cr.id SEPARATOR ','), '  ', '<p style="color:red;"><b>(Refund)</b></p>') AS month,
    sum(cr.amount) * (-1) AS amount,
    cr.boono,
    cr.tran_date,
    a.Name AS academicyear,
    rm.method AS receipt_method,
    cr.usd_no,
    cr.cancel_reason AS note 
FROM 
    cash_receipt cr 
    JOIN students s ON s.student_id = cr.student_id 
    JOIN class_sections cs ON cs.id = cr.class_id 
    JOIN classes c ON c.id = cs.class_id 
    JOIN sections sec ON sec.id = cs.section_id 
    JOIN school_levels sl ON sl.id = c.school_level_id 
    JOIN levels ls ON ls.id = sl.level_id 
    JOIN fee_type ft ON cr.fee_type = ft.id 
    JOIN academicyear a ON a.id = cr.academic_year 
    JOIN receipt_method rm ON cr.receipt_by = rm.id 
   	JOIN studentbus sb ON cr.student_id = sb.student_id
    JOIN buses b ON sb.Bus = b.id 
WHERE 
    cr.boono = p_boono   
    AND cr.status = 3 
GROUP BY 
    cr.student_id, cr.class_id$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `Rpt_Salary` (IN `p_accy` VARCHAR(100), IN `p_school` INT)   BEGIN
SELECT sa.id,sa.salary_month,sc.school_name,sa.Expense_invoice,sa.Totalamount,sa.enter_by,
case when sa.status=0 then 'authorization'
when sa.status=1 then 'Success'
when sa.status=2 then 'Deleted' else '' end as status,Concat('<a href="" data-id="',sa.id,'"class="btn btn-light btn-sm delete_link"><i class="fas fa-trash-alt"></i></a>') as 'Cancel'
from salaryinfo sa 
join schools sc on sc.id=sa.school where sc.id=p_school and 
SUBSTRING(sa.salary_month, LOCATE(')', sa.salary_month) + 1) =p_accy;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `Rpt_SalaryDeatil` (IN `p_salid` INT)   BEGIN
SELECT concat('EMP-',em.id) id,em.name,em.tell_1,sd.net_salary,
case when sd.status =0 then 'Pending'
when sd.status =1 then 'Deleted' 
when sd.status =2 then 'Success' else '' end as status
FROM salary_detail sd
join employee em on em.id=sd.employee_id
WHERE sd.salid=p_salid;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_school_rv` (IN `p_fee_type` INT, IN `p_academidyear_id` VARCHAR(50), IN `p_from` DATE, IN `p_to` DATE, IN `p_user_id` INT)  NO SQL BEGIN
SELECT concat(sc.school_name,'-',l.name) as school_name,sum(cash_receipt.amount) as TotalAmount FROM cash_receipt RIGHT JOIN class_sections cs on cash_receipt.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id 
 INNER join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user_id
 JOIN levels l on sl.level_id = l.id WHERE tran_date between p_from and p_to and cash_receipt.status =1 and fee_type = p_fee_type and academic_year like p_academidyear_id  group by sl.id ORDER BY sc.school_name;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `Rpt_SingleStudentStatement` (IN `p_student` VARCHAR(50), IN `p_accy` VARCHAR(50))   BEGIN

SELECT ft.id,cr.tran_date as DATE ,(CASE WHEN cr.status=1 THEN 'Payment' WHEN cr.status=2 THEN 'Cancel' WHEN cr.status=3 then 'Refund' END)AS 'TYPE',cr.boono as Invoice,ft.fee_name as ACCOUNT,(CASE when cr.fee_type=7 THEN GROUP_CONCAT( months.months ORDER BY months.id) ELSE '-' END) AS Month,(CASE WHEN cr.status=1 THEN cr.remark ELSE cr.cancel_reason END) as MEMO, (CASE WHEN cr.status=3 THEN SUM(cr.amount)else 0.00 END) Increase, (CASE WHEN cr.status=1 THEN SUM(cr.amount) else 0.00 END)Descrease  from cash_receipt cr INNER join fee_type ft on ft.id=cr.fee_type LEFT JOIN months ON  months.id=cr.month 
JOIN academicyear acy ON cr.academic_year=acy.id
where cr.student_id=p_student  and acy.Name=p_accy and cr.status!=2
group by cr.boono;


END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_singlestudent_behavior` (IN `p_student_id` VARCHAR(100), IN `p_category` VARCHAR(50), IN `p_from` DATE, IN `p_to` DATE, IN `p_type` VARCHAR(100))   SELECT sb.date,bt.type as "behavior_category",b.name as "behavior_type",sb.subject,sb.reason,concat(c.class_name,' ',se.section)as class FROM behavior_type bt JOIN behavior b on b.type = bt.id JOIN student_behavior sb on sb.type = b.id JOIN students s on sb.student_id = s.student_id JOIN class_sections cs on sb.class_id = cs.id 
join classes c on cs.class_id = c.id 
join sections se on cs.section_id = se.id  WHERE s.student_id = p_student_id and bt.id like p_category and sb.date BETWEEN p_from and p_to and bt.IsBad like p_type$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_single_employee_attendence` (IN `p_emp_id` INT(100), IN `p_from` DATE, IN `p_to` DATE)   BEGIN
SELECT ea.laga_rabay,ea.soo_galay,subtime(ea.soo_galay,ea.laga_rabay) 'Dahid',ea.faafaahin,ea.date from employee_attendece ea where ea.Emp_id=p_emp_id and date BETWEEN p_from and p_to
;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_single_employee_behavior` (IN `p_emp_id` INT(100), IN `p_from` DATE, IN `p_to` DATE, IN `p_anshaxa` VARCHAR(100))   BEGIN
SELECT es.name 'Behavior',
EB.remark 'Reason',eb.date 'Date' from employee_behavior eb 
INNER join employee e on e.id=eb.Emp_id
INNER join employee_status es on es.id=eb.anshaxa where e.id=p_emp_id 
and es.id like p_anshaxa 
and date(eb.date) BETWEEN p_from and p_to
;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_sponsor_by_school` (IN `p_school` INT)   BEGIN


    SELECT 
        sp.id,
        
        sp.sponsor_name,
        sp.mobile
    FROM sponsors sp
    LEFT JOIN students s ON s.sponsor_id = sp.id
    LEFT JOIN class_sections cs ON cs.id = s.class_id
    LEFT JOIN classes c ON c.id = cs.class_id
    LEFT JOIN school_levels sl ON sl.id = c.school_level_id
    LEFT JOIN schools sch ON sch.id = sl.school_id
    JOIN sections se ON cs.section_id = se.id 
    
    JOIN levels l ON sl.level_id = l.id
    LEFT JOIN districts ds ON ds.id = s.district
    INNER JOIN status ss ON ss.id = s.status 
    WHERE sch.id = p_school
    GROUP BY sp.id, sch.school_name, sp.sponsor_name, sp.mobile;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_StudentBalance` (IN `p_BatchCode` VARCHAR(50), IN `p_BegDate` DATETIME, IN `p_StudentStatus` VARCHAR(50))  NO SQL BEGIN
DECLARE begin_date DATE;


SET begin_date=DATE_FORMAT(p_BegDate, "%Y%m%d");

CREATE TEMPORARY Table IF NOT EXISTS TempOutput
(
StudentID varchar(35) ,
JU_ID varchar(35),
StudentName varchar(85),
BatchCode varchar(35),
FeeType varchar(35),
Amount float  
);
INSERT INTO TempOutput SELECT L.id,L.StudentID, L.StudentName, 
	L.BatchCode, '' as FeeType, sum(R.Amount) as Amount 
	FROM Students L left outer join CashReceipt R on L.id=R.StudentID
	WHERE L.BatchCode like p_BatchCode and R.TranDate<=begin_date and R.Status='OK' and L.Status like p_StudentStatus
	and (RecType = 'Receipt' or RecType = 'Discount')
	group by L.id, L.StudentName, L.BatchCode;
    
    INSERT INTO TempOutput SELECT L.id,L.StudentID, L.StudentName, 
	L.BatchCode, '' as FeeType, -sum(R.Amount) as Amount 
	FROM Students L left outer join CashReceipt R on L.StudentID=R.StudentID
	WHERE L.BatchCode like p_BatchCode and R.TranDate<=begin_date and R.Status='OK' and L.Status like p_StudentStatus
	and RecType = 'Refund'
	group by L.id, L.StudentName, L.BatchCode;
    
    INSERT INTO TempOutput SELECT L.id,L.StudentID ,L.StudentName, 
	L.BatchCode, '' as FeeType, -sum(R.Amount) as Amount 
	FROM Students L left outer join StudentCharges R on L.id=R.StudentID
	WHERE L.BatchCode like p_BatchCode and R.TranDate<=begin_date and L.Status like p_StudentStatus
	group by L.id, L.StudentName, L.BatchCode;
  
    SELECT  StudentID, JU_ID,StudentName, BatchCode, FeeType, 
	(select FacName + ' Batch ' + right(BatchCode,2) from Faculties where FacID=left(BatchCode,4)) as BatchInfo,
	CASE WHEN sum(Amount)<0  then abs(sum(Amount)) else 0 end AS Dr,
	CASE WHEN sum(Amount)>=0 then abs(sum(Amount)) else 0 end AS Cr,begin_date as StartDate 
FROM TempOutput
GROUP BY StudentID, StudentName, BatchCode, FeeType
ORDER BY BatchCode, StudentName;


DROP TEMPORARY Table TempOutput;

end$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_StudentStatement` (IN `P_STUDENTID` INT, IN `p_fromdate` TEXT, IN `p_todate` TEXT)  NO SQL BEGIN

DECLARE facultyname,departname,batchname varchar(50);

DECLARE BDate DATE;
DECLARE EDate DATE;

SET BDate=DATE_FORMAT(p_fromdate, "%Y%m%d");
SET EDate=DATE_FORMAT(p_todate,  "%Y%m%d");

SELECT FacultyID,Dept_ID,BatchCode into  @fac_id,@depid,@batchname from students WHERE id=P_STUDENTID;

SELECT FacName into @facultyname FROM `faculties` WHERE id=@fac_id;
SELECT DeptName into @departname  FROM `depts` WHERE id=@depid;








CREATE TEMPORARY Table IF NOT EXISTS FinalOutput
(
RefNo varchar(35),
TranDate datetime,
StudentID varchar(35),
JU_ID varchar(35),
StudentName varchar(85),
BatchCode varchar(35),
FeeType varchar(35),
Description varchar(150),
BankRefNo varchar(150),
Dr float,
Cr float
);

CREATE TEMPORARY  Table IF NOT EXISTS TempOutput
(
RefNo varchar(35),
TranDate datetime,
StudentID varchar(35),
JU_ID varchar(35),
StudentName varchar(85),
BatchCode varchar(35),
FeeType varchar(35),
Description varchar(150),
Amount float
);

INSERT INTO TempOutput SELECT '' as RefNo, Null as TranDate, StudentID,JU_ID, StudentName, 
	'' as BatchCode, '' as FeeType, 'Prev. Balance' as Description, sum(Amount) as Amount FROM CashReceipt
	WHERE StudentID like P_STUDENTID and TranDate<BDate and Status='OK' and (RecType = 'Receipt' or RecType = 'Discount')
	group by StudentID, StudentName, BatchCode;
    
    INSERT INTO TempOutput SELECT '' as RefNo, Null as TranDate, StudentID,JU_ID, StudentName, 
	'' as BatchCode, '' as FeeType, 'Prev. Balance' as Description, -sum(Amount) as Amount FROM CashReceipt
	WHERE StudentID like P_STUDENTID and TranDate<BDate and Status='OK' and RecType = 'Refund'
	group by StudentID, StudentName, BatchCode;
    
    INSERT INTO TempOutput SELECT '' as RefNo, Null as TranDate, StudentID,JU_ID, StudentName, 
	'' as BatchCode, '' as FeeType, 'Prev. Balance' as Description, -sum(Amount) as Amount FROM StudentCharges
	WHERE StudentID like P_STUDENTID and TranDate<BDate
	group by StudentID, StudentName;
    
    INSERT INTO FinalOutput SELECT RefNo,TranDate, StudentID,JU_ID, StudentName, BatchCode, FeeType, Description, '' as BankRefNo,
	case when sum(Amount)<0 then abs(SUM(Amount)) else 0 end as Dr, case when sum(Amount)>=0 then abs(SUM(Amount)) else 0 end as Cr
	FROM TempOutput
	group by RefNo,TranDate, StudentID, StudentName, BatchCode, FeeType, Description;
	
INSERT INTO FinalOutput SELECT ID as RefNo, TranDate, StudentID,JU_ID, StudentName, 
	'' as BatchCode, ChargeType as FeeType, 'Charge' as Description,'' as BankRefNo, Amount as Dr, 0 as Cr FROM StudentCharges
	WHERE StudentID like P_STUDENTID and TranDate between BDate and EDate;
INSERT INTO FinalOutput SELECT ReceiptNo as RefNo, TranDate, StudentID,JU_ID, StudentName, 
	BatchCode, FeeType, RecType AS Description, BankRefNo, 0 as Dr, Amount as Cr FROM CashReceipt
	WHERE StudentID like P_STUDENTID and TranDate between BDate and EDate and Status='OK' and (RecType = 'Receipt' or RecType = 'Discount');
    INSERT INTO FinalOutput SELECT ReceiptNo as RefNo, TranDate, StudentID,JU_ID, StudentName, 
	BatchCode, FeeType, RecType AS Description, BankRefNo, Amount as Dr, 0 as Cr FROM CashReceipt
	WHERE StudentID like P_STUDENTID and TranDate between BDate and EDate and Status='OK' and RecType = 'Refund';
    if not EXISTS(SELECT  * ,BDate,EDate FROM FinalOutput ORDER BY TranDate) THEN
    BEGIN
     SELECT  '0' RefNo,'0' TranDate,'0' StudentID,'0' StudentName,'0' FeeType,'0' Description,'0' BankRefNo,'0' Dr,'0' Cr ,BDate,EDate ;
    end;
    ELSE
    BEGIN
    SELECT  * ,BDate,EDate,@facultyname as facultyname ,@departname as departname,@batchname as batchname FROM FinalOutput ORDER BY TranDate;
    end;
    end if;
    


DROP TEMPORARY Table FinalOutput;
DROP TEMPORARY Table TempOutput;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_student_attendence_date` (IN `p_level` VARCHAR(100), IN `p_class_id` VARCHAR(100), IN `p_xiisada` INT, IN `p_user_id` INT, IN `p_from_date` DATE, IN `p_to_date` DATE)  NO SQL BEGIN
SELECT s.student_id, s.student_name, sp.mobile, aat.type, aat.key_value, sa.remark, sa.date, CONCAT( c.class_name, ' ', sec.section, ' ', c.shift ) AS class FROM student_attendence sa JOIN attendence_type aat ON sa.attendence_type_id = aat.id RIGHT JOIN students s ON sa.student_id = s.student_id INNER JOIN sponsors sp ON sp.id = s.sponsor_id INNER JOIN class_sections cs ON cs.id = sa.class_id INNER JOIN classes c ON cs.class_id = c.id JOIN sections sec ON cs.section_id = sec.id JOIN school_levels sl on c.school_level_id = sl.id join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user_id JOIN levels l on sl.level_id = l.id JOIN schools sc on sl.school_id = sc.id where sa.date BETWEEN p_from_date and p_to_date and sa.xiisada=p_xiisada and sa.class_id  LIKE p_class_id and sl.id LIKE p_level ;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_student_behavior_bydate` (IN `p_category` VARCHAR(50), IN `p_from` DATE, IN `p_to` DATE, IN `p_level` VARCHAR(10), IN `p_class` VARCHAR(10), IN `p_user_id` INT, IN `p_type` VARCHAR(10))   BEGIN
CREATE TEMPORARY TABLE table2 as(
SELECT   s.student_id,s.student_name,concat(bt.type ,' :', COUNT(bt.type)) 'behavior_category',concat(b.name ,' :', COUNT(b.name)) 'behavior_type',sb.reason,concat(c.class_name,' ',se.section) as 'class',COUNT(bt.type) as total FROM behavior_type bt JOIN behavior b on b.type = bt.id JOIN student_behavior sb on sb.type = b.id JOIN students s on sb.student_id = s.student_id
    JOIN class_sections cs on sb.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id JOIN levels l on sl.level_id = l.id
    join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user_id
 WHERE  sl.id like p_level and cs.id like p_class and  bt.id like p_category and sb.date BETWEEN p_from and p_to and bt.IsBad like p_type
    GROUP by sb.student_id,BT.id,b.id
);
SELECT student_id,student_name, ifnull(GROUP_CONCAT( DISTINCT behavior_type   SEPARATOR ' <br> '),'') 'behavior_type',reason ,class,sum(total) as total from table2 GROUP by student_id ORDER by total DESC;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_student_status_date` (IN `p_student_id` VARCHAR(100), IN `p_from` DATE, IN `p_to` DATE)  NO SQL BEGIN
SELECT ss.status_name,st.date,st.detail FROM student_status_change st inner join students s on s.student_id=st.student_id INNER join status ss on ss.id=st.to_status where s.student_id=p_student_id and  st.date between p_from and p_to;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_student_summary` (IN `p_student_id` VARCHAR(100), IN `p_accy` VARCHAR(100), IN `p_type` INT)   BEGIN

SELECT ac.Name into @accy from academicyear ac where ac.Name=p_accy;
if(p_type=1)THEN

SELECT s.student_id,s.student_name,ss.status_name,st.detail,concat( (case when  month(st.date) BETWEEN 8 and 12 then Year(st.date) ELSE Year(st.date)-1 END),'-',(case when  month(st.date) BETWEEN 8 and 12 then Year(st.date)+1 ELSE Year(st.date) END) )as accy, st.date FROM student_status_change st inner join students s on s.student_id=st.student_id INNER join status ss on ss.id=st.to_status where s.student_id=p_student_id HAVING accy=@accy;
ELSEIF (p_type=2)  THEN

SELECT sa.`date`,ifnull(at.type,'Unattended') as `status`,x.xiisada,sa.remark,concat( (case when  month(sa.date) BETWEEN 8 and 12 then Year(sa.date) ELSE Year(sa.date)-1 END),'-',(case when  month(sa.date) BETWEEN 8 and 12 then Year(sa.date)+1 ELSE Year(sa.date) END) )as accy FROM student_attendence sa RIGHT JOIN students s on sa.student_id = s.student_id JOIN attendence_type at on sa.attendence_type_id = at.id  JOIN class_sections cs on sa.class_id = cs.id JOIN classes c on cs.class_id = c.id JOIN sections se on cs.section_id = se.id inner join xiisadaha x on x.id=sa.xiisada WHERE s.student_id = p_student_id HAVING accy=@accy  ORDER BY sa.date ASC;
ELSEIF (p_type=3)THEN

SELECT sb.date,bt.type as 'behavior_category',b.name as 'behavior_type',sb.subject,sb.reason,concat( (case when  month(sb.date) BETWEEN 8 and 12 then Year(sb.date) ELSE Year(sb.date)-1 END),'-',(case when  month(sb.date) BETWEEN 8 and 12 then Year(sb.date)+1 ELSE Year(sb.date) END) )as accy FROM behavior_type bt JOIN behavior b on b.type = bt.id JOIN student_behavior sb on sb.type = b.id JOIN students s on sb.student_id = s.student_id WHERE s.student_id = p_student_id HAVING accy=@accy ;



END if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_teachers_timetable` (IN `p_teacher_id` INT, IN `p_day` VARCHAR(50))  NO SQL BEGIN

SELECT tt.id,concat(c.class_name,'(',se.section,')') as class_name,tt.day_name,s.subject_name,tt.start_time,tt.end_time FROM timetables tt JOIN teacher_subjects ts on tt.teacher_subject_id = ts.id JOIN subjects s on ts.subject_id = s.id JOIN class_sections cs on ts.class_section_id = cs.id JOIN classes c on cs.class_id = c.id JOIN sections se on cs.section_id = se.id WHERE ts.teacher_id =p_teacher_id and day_name = p_day;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_teacher_lesson` (IN `p_school` INT, IN `p_level` INT, IN `p_class` INT, IN `p_date` DATE)   BEGIN
   SELECT 
    ls.id,
    sc.school_name as 'school',
    CONCAT(c.class_name,' ', se.section) as 'Fasal',
      l.name as 'Heerka',
    ls.xiisad as 'Xiisada',
    ls.lesson as 'Casharka',
    ls.date as 'Taariikhda'
FROM lessons ls
JOIN class_sections cs on ls.class = cs.id 
LEFT JOIN classes c on cs.class_id = c.id 
LEFT join sections se on cs.section_id = se.id 
JOIN school_levels sl on c.school_level_id = sl.id 
JOIN schools sc on sl.school_id = sc.id 
JOIN levels l on sl.level_id = l.id
WHERE 
ls.school = p_school
AND ls.level =p_level
AND ls.class =p_class
AND ls.date = p_date;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_topic_view` (IN `p_subject` INT, IN `p_class` INT, IN `p_accy` INT)  NO SQL BEGIN
CREATE TEMPORARY TABLE view1 as
SELECT
 
    IF(
        DAY(t.from_date) < 27,
        DATE_FORMAT(t.from_date, '%b %x'),
        DATE_FORMAT(t.to_date, '%b %x')
    ) AS MONTH,
   
        CONCAT(
            '(',
            DATE_FORMAT(t.from_date, '%d'),
            ' - ',
            IF(
                DAY(t.from_date) > 27,
                DATE_FORMAT(t.to_date, '%d %b'),
                DATE_FORMAT(t.to_date, '%d')
            ),
            ')'
        )  'Date',
    l.name 'Chapter_unit',
   t.name ,
   t.Objectives ,
    t.Reference ,
     t.Teaching_Method ,
    t.Assignment as Assignment,
    t.Practical ,
     t.Practical_Detail as 'Practical_Detail', l.id,t.from_date,t.status,t.complete_date as enddate
FROM
    topic t
JOIN lesson l ON
    t.lesson_id = l.id
     WHERE l.subject_group_subject_id=p_subject
    and l.subject_group_class_sections_id=p_class
    and l.academic_year_id=p_accy

ORDER BY l.id ASC;
SELECT
   MONTH,
    GROUP_CONCAT(
        Date SEPARATOR '<br>'
    ) 'Date',
    Chapter_unit ,
    GROUP_CONCAT(concat('-',name) SEPARATOR '<br>') 'Topic_subtopic',
    GROUP_CONCAT(concat('-',Objectives) SEPARATOR '<br>') as Objectives,
   GROUP_CONCAT( concat('-',Reference) SEPARATOR '<br>') as Reference,
    GROUP_CONCAT( concat('-',Teaching_Method )SEPARATOR '<br>')AS 'Teaching_Method',
   GROUP_CONCAT( concat('-', Assignment) SEPARATOR '<br>') as Assignment,
   GROUP_CONCAT( concat('-',Practical) SEPARATOR '<br>') as Practical,
   GROUP_CONCAT(  concat('-',Practical_Detail )SEPARATOR '<br>')as 'Practical_Detail',
    GROUP_CONCAT( concat('-',status) SEPARATOR '<br>') as status,
      GROUP_CONCAT( concat('-',enddate) SEPARATOR '<br>') as enddate
FROM
view1
  
GROUP BY
    id,MONTH
ORDER BY
    from_date
    ASC;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_topic_view_head` (IN `p_subject` INT, IN `p_class_id` INT, IN `p_accy` INT)  NO SQL BEGIN
SELECT s.subject_name,GROUP_CONCAT(em.name SEPARATOR ' & ') as name, a.Name as accy,c.class_name FROM lesson_teacher lt 
JOIN subjects s on lt.subject_id = s.id 
join classes c on c.id=lt.class_id
JOIN academicyear a on a.id=lt.accy
JOIN employee em on em.id=lt.teacher_id
where lt.subject_id=p_subject and lt.class_id=p_class_id and lt.accy=p_accy
GROUP by lt.subject_id,lt.class_id,lt.accy ;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_top_employee_attendence` (IN `p_from` DATE, IN `p_to` DATE, IN `p_user_id` INT)   BEGIN
SELECT concat('EMP-',ea.Emp_id) as id,e.name,SEC_TO_TIME( SUM(time_to_sec(subtime(ea.soo_galay,ea.laga_rabay))))  'Dahid',ifnull(GROUP_CONCAT( DISTINCT EA.faafaahin   SEPARATOR ' <br> '),'') 'faafaahin' from employee_attendece ea  INNER join employee e on e.id=ea.Emp_id 
INNER JOIN employee_permission ep on ep.qaybta=e.qeybta and ep.user_id=p_user_id
where  ea.date BETWEEN p_from and p_to
GROUP by ea.Emp_id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rpt_unpaid_Months_Report` (IN `p_months` VARCHAR(100), IN `p_school_id` VARCHAR(50), IN `p_class_id` VARCHAR(50), IN `p_academic_year_id` VARCHAR(50), IN `p_company_id` INT, IN `p_fee_type` VARCHAR(50))  NO SQL BEGIN
select REPLACE(p_months,"-",",") into @months;
SELECT concat(REPLACE(concat("`",p_months),"-","` is null and `"),"` is null") into @iszero;
create table t( txt text );
insert into t values(@months);

drop temporary table if exists temp;
create temporary table temp( val char(255) );
set @sql1 = concat("insert into temp (val) values ('", replace(( select group_concat(distinct txt) as data from t), ",", "'),('"),"');");
prepare stmt1 from @sql1;
execute stmt1;


CREATE TEMPORARY TABLE Tmpcash
SELECT s.id ,s.StudentID 'student_id',s.StudentName,s.is_graduation,c.id 'class_id', c.class_name,sc.school_name,sp.sponsor_name,sp.mobile,cr.month,cr.amount,cr.status FROM students s LEFT join classes c on s.class_id = c.id LEFT join schools sc on c.school_id = sc.id LEFT join sponsors sp on s.sponsor_id = sp.id left join cash_receipt cr on cr.student_id = s.id join academicyear a on cr.academic_year=a.id WHERE sc.id like p_school_id and c.id like p_class_id and a.id like p_academic_year_id and fee_type like p_fee_type and cr.company_id=p_company_id and s.is_graduation=0 and cr.status !=2;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(case when month = ''',
      month,
      ''' then amount end) AS `',
      month, '`'
    )
  ) INTO @sql 
  FROM Tmpcash WHERE month IN((SELECT val from temp));
  
  
 SET @sql = CONCAT('SELECT id,student_id,StudentName,is_graduation,class_id,class_name,school_name,sponsor_name,mobile, ', @sql, ' \r\n                  FROM Tmpcash group by id \r\n ');

     
 SET @sql=CONCAT("CREATE TEMPORARY  Table IF NOT EXISTS TempTble2 ",@sql,";");
set @sql2 =concat(" select s.id,s.StudentID as student_id,s.StudentName,sc.school_name,c.class_name,sp.sponsor_name,sp.mobile from students s join classes c on s.class_id = c.id join schools sc on c.school_id = sc.id join sponsors sp on s.sponsor_id = sp.id where s.id not in(select id from TempTble2 ) and class_id = ",p_class_id); 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PREPARE stmt2 FROM @sql2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;


END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_admision_report_detail` (IN `p_student_id` VARCHAR(100))   BEGIN

SELECT s.student_id,s.student_name,s.gender,s.reg_date as 'Admission_date',s.dob as "Date of birth",s.pob as "Place of birth",s.mother_name,s.phone as "Mother Phone",s.address,concat(sp.id,' -',sp.sponsor_name) as sponsor_name,sp.mobile,sp.address as "Guardian address",s.relation,ifnull(bg.name,"N/A") as "Blood Group",concat(c.class_name,'/',se.section) as class,s.photo as Image,s.status,s.class_id,st.status_name,sl.id as school_levels,concat(sc.school_name,' - ',l.name) as school_name FROM students s JOIN class_sections cs on s.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id  JOIN levels l on sl.level_id = l.id
LEFT JOIN status st on st.id=s.status
inner join sponsors sp on s.sponsor_id = sp.id LEFT JOIN blood_groups bg on s.blood_group = bg.id where  s.student_id = p_student_id ORDER by s.id ASC;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_all_parent_users` ()   BEGIN

SELECT sp.sponsor_name 'parent_name',sp.mobile 'parent_mobile',u.user_name 'parent_user' FROM user u 
JOIN sponsors sp ON u.employee_id=sp.id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_attendence_percentage` (IN `p_class_id` INT)   BEGIN
SELECT
    s.student_id,
    s.student_name,
    rpf_percent_attendence(s.id, 1) AS present,
    rpf_percent_attendence(s.id, 3) AS late,
    rpf_percent_attendence(s.id, 4) AS absent,
    rpf_percent_attendence(s.id, 6) AS halfday,CONCAT('<a href="Report/rpt_student_attendencedetail.php?id=',s.student_id,'" class="btn btn-sm view_student"><i class="fas fa-eye"></i></a>') as 'View Report'
FROM
    student_attendence sa
RIGHT JOIN students s ON
    sa.student_id = s.id
WHERE
    s.class_id = p_class_id
GROUP BY
    s.id;END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_behavior` (IN `user_id` INT)   BEGIN

SELECT  b.name 'Behavior Name', bt.type 'Behavior Type', b.marks 'Marks' ,CONCAT('<a href="" data-name="',b.name,'" data-type="',b.id,'" data-id="',b.id,'" data-mark="',b.marks,'" class="btn btn-xs update_link mr-4"><i class="fas fa-edit"></i></a>','<a href="" data-id="',b.id,'" class="btn   btn-light btn-sm delete_link"><i class="fas fa-trash-alt"></i></a>') `Actions` 
FROM behavior b 
JOIN behavior_type bt ON bt.id=b.type
JOIN schools sch on b.school = sch.id
join school_levels sl on sch.id=sl.school_id
JOIN levels l on sl.level_id = l.id 
join level_permission lp on lp.level_id=sl.id and lp.user_id=user_id
 GROUP BY b.id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_behavior1` ()   BEGIN
SELECT b.name 'Behavior Name',bt.type as 'Behavior Type',CONCAT('<a href="" data-name="',b.name,'" data-type="',bt.id,'" data-id="',b.id,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Action` FROM behavior b JOIN behavior_type bt on b.type = bt.id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_behavior_type` (IN `user_id` INT)   BEGIN
SELECT b.type as Type,if(b.IsBad=0,'Bad','Good') as IsBad,CONCAT('<a href="" data-type="',b.type,'" data-id="',b.id,'" data-IsBad="',b.IsBad,'" class="btn btn-xs update_link mr-4"><i class="fas fa-edit"></i></a>','<a href="" data-id="',b.id,'" class="btn   btn-light btn-sm delete_link"><i class="fas fa-trash-alt"></i></a>') as `Actions`
FROM behavior_type b 
JOIN schools sc on b.school = sc.id
join school_levels sl on sc.id=sl.school_id
JOIN levels l on sl.level_id = l.id join level_permission lp on lp.level_id=sl.id and lp.user_id=user_id
GROUP BY b.type;


END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_buses` (IN `p_company_id` INT)   SELECT b.bus_name "Magaca Baska",b.driver_name "Darawalka",b.mobile "Mobile-ka",CONCAT('<a href="actions/edit.php?table=buses&form=Diiwaangalin/frm_addbus.php&sp=sp_add_bus&id=',b.id,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM buses b where b.company_id = p_company_id$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_classes` ()   BEGIN
SELECT s.school_name "Magaca Dugsiga",l.name as "Heerka",c.class_name "Magaca Fasalka",ifnull(GROUP_CONCAT( DISTINCT se.section ORDER BY se.id  SEPARATOR' , '),'')
as "Sections",c.shift 'Galinka' ,CONCAT('<a href="" data-href="../Form/frm_editclasses.php" data-id="',c.id,'" data-tables="classes" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM classes c LEFT JOIN class_sections cs on cs.class_id = c.id LEFT JOIN sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id  JOIN levels l on sl.level_id = l.id JOIN schools s on  sl.school_id = s.id   GROUP BY c.id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_employee` (IN `p_user_id` INT)  NO SQL BEGIN

SELECT concat('EMP-',e.id) 'ID',e.name 'Name', concat(e.tell_1,' - ',e.tell_2) 'Tell',concat(s.Name,'<br>',q.Name,'<br>',e.galinka) 'Shaqada',
 CONCAT('<a href="" data-href="../Form/frm_edit_employee.php" data-id="',e.id,'"  data-tables"employee" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>')
  'Edit', 
  CONCAT('<a href="" data-href="../Form/employee_profile.php" data-id="',e.id,'"  class="btn btn-xs view_link"><i class="fas fa-eye"></i></a>') as 'View',

 CONCAT('<a href="#" value="',e.id,'" class="btn btn-xs delete_link"><i class="fas fa-trash-alt"></i></a>')
     as
     `Delete` from employee e LEFT join shaqada s on e.shaqada=s.id LEFT join  qaybta q on q.id= e.qeybta LEFT JOIN employee_permission ep on ep.qaybta=e.qeybta and ep.user_id=p_user_id where e.id!=1;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_employee_behavior` (IN `p_user_id` VARCHAR(100))   BEGIN
SELECT concat('EMP-',e.id) 'ID',e.name 'Name',es.name 'Behavior',
EB.remark 'Reason',eb.date 'Date',

 CONCAT('<a href="" data-table="employee_behavior" data-href="../Form/frm_add_employee_behavior.php" data-id="',eb.id,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>')
`Edit` from employee_behavior eb 
INNER join employee e on e.id=eb.Emp_id
INNER JOIN employee_permission ep on ep.qaybta=e.qeybta and ep.user_id=p_user_id
INNER join employee_status es on es.id=eb.anshaxa;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_employee_header_info` (IN `p_emp_id` VARCHAR(100))   BEGIN
SELECT concat('	EMP-',e.id) 'ID',e.name 'Name', concat(e.tell_1,' - ',e.tell_2) 'Tell',s.Name 'Shaqada',q.Name 'Qaybta',e.galinka 'Galinka'  from employee e INNER join shaqada s on e.shaqada=s.id INNER join  qaybta q on q.id= e.qeybta  WHERE e.id = p_emp_id ;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_employee_sponser` ()   BEGIN
SELECT e.name 'Magaca', concat(e.tell1,' - ', e.tell2) 'Tell',e.address 'Ciwaanka',e.job 'Shaqada',e.darajada 'Darajada',e.Aqoonsiga 'Aqoonsiga',CONCAT('<a href="" data-table="employee_sponser" data-href="../Form/frm_add_employee_mas''ul.php" data-id="',e.id,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM employee_sponser e ;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_exam_class` (IN `p_class_id` INT, IN `p_accy` INT)   BEGIN
SELECT e.type "Exam",ifnull(GROUP_CONCAT( DISTINCT c.class_name ORDER BY ec.id  SEPARATOR' , '),'')
as "Class",ifnull(GROUP_CONCAT( DISTINCT s.subject_name ORDER BY s.subject_name  SEPARATOR' , '),'')
as "Subject",sl.school_id 'levels',sg.id 'subject_group',ec.marks 'Mark',A.Name 'Acedemic year',CONCAT('<a href="" data-id="', ec.id,'" data-mark="', ec.marks,'" data-exam="', ec.exam_type_id,'"  data-accy="',ec.acedemic_year,'" data-class_id="',ec.class_id,'" data-level="',sl.id,'" data-subject_group="',sg.id,'"  data-subject="',ifnull(GROUP_CONCAT( DISTINCT s.id ORDER BY s.id  SEPARATOR','),''),'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit`  FROM exam_class_info ec 
inner join exam_class ecc on ecc.exam_info =ec.id
inner join exam_type e on e.id=ec.exam_type_id 
INNER JOIN subjects s on s.id=ecc.subject_id 
INNER join classes c on c.id=ec.class_id
INNER join school_levels sl on sl.id=c.school_level_id
INNER join levels l on l.id=sl.level_id
INNER JOIN subject_group_class_sections sgcs on sgcs.class_section_id=c.id
INNER JOIN subject_group_subjects sgs on sgs.subject_group_id=sgcs.subject_group_id and sgs.subject_id=s.id
INNER join subject_groups sg on sg.id=sgs.subject_group_id
INNER JOIN academicyear A ON A.id=EC.acedemic_year

where c.id=p_class_id and a.id=p_accy and ec.status=0 GROUP BY ec.class_id,ec.exam_type_id,ec.acedemic_year,ec.marks;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_exam_grades` ()   SELECT eg.Grade_name "Grade",eg.Percent_from 'From',eg.Percent_to 'To',CONCAT('<a href="" data-id="',eg.id,'" data-name="',eg.Grade_name,'" data-From="',eg.Percent_from,'" data-To="',eg.Percent_to,'"  data-Remark="',eg.Remark,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM exam_grades eg$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_exam_pass_mark` (IN `p_company_id` INT)   BEGIN
SELECT ifnull(GROUP_CONCAT( DISTINCT concat( c.class_name,'-',c.type,'-',language)  ORDER BY ec.id  SEPARATOR' , '),'')
as "Class",ec.marks 'Average',A.Name 'Academic year',CONCAT('<a href="Diiwaangalin/frm_update_pass_mark.php?id=', ec.id,'"class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM exam_pass_mark_info ec 
inner join exam_pass_mark ecc on ecc.pass_mark_info =ec.id
INNER join classes c on c.id=ecc.classes
INNER JOIN academicyear A ON A.id=ec.academic_year
 GROUP BY ec.id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_exam_result` ()   SELECT e.Name 'Name',CONCAT('<a href="" data-href="../Form/EditExamResult.php" data-id="', e.id,'" data-tables="exam_result"  class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>')  `Edit` FROM exam_create_result e$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_exam_result_view` (IN `p_level` INT, IN `p_class` INT, IN `p_accy` INT)   BEGIN
SELECT ifnull(GROUP_CONCAT( DISTINCT c.class_name ORDER BY ec.id  SEPARATOR' , '),'')
as "Class",ecr.Name as 'Exam',ifnull(GROUP_CONCAT( DISTINCT s.subject_name ORDER BY s.id  SEPARATOR' , '),'')
as "Subject",ec.pass_mark 'Mark',A.Name 'Acedemic year',CONCAT('<a href="" data-href="../Form/EditResultView.php" data-id="',ec.id,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') as 'Edit',CONCAT('<a href="" data-id="',ec.id,'" class="btn btn-xs delete_link"><i class="fas fa-trash-alt"></i></a>') `Delete` FROM exam_result_class ec 
inner join exam_result_class_subject ecc on ecc.exam_result_class_info =ec.id
INNER JOIN subjects s on s.id=ecc.subjects 
INNER join classes c on c.id=ec.class
INNER JOIN exam_create_result ecr on ecr.id=ec.exam_result_info
INNER JOIN academicyear A ON A.id=EC.academic_year
where c.id=p_class and c.school_level_id=p_level and ec.academic_year=p_accy
 GROUP BY ec.id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_exam_types` ()   BEGIN
SELECT e.type 'Type',CONCAT('<a href="" data-type="',e.type,'" data-id="',e.id,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` from  exam_type e ;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_fee_type` (IN `p_company_id` INT)   SELECT ft.fee_name "Nooca Lacagta",ft.Description "Faahfaahin",ft.pay_method "Habka bixinta",CONCAT('<a href="actions/edit.php?table=fee_type&form=Diiwaangalin/frm_addfeetype.php&sp=sp_add_fee_type&id=',ft.id,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM fee_type ft where ft.company_id = p_company_id$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_headerInfo_dynamicAccy` (IN `p_student_id` VARCHAR(100), IN `p_accy` VARCHAR(50))   BEGIN

SELECT 
    s.id,
    s.student_id,
    s.student_name,
    CONCAT(c.class_name, '(', se.section, ')') AS class_name,
    s.address,
    d.districts AS Districts, 
    s.mother_name,
    ss.sponsor_name,
    CONCAT(sc.school_name, ' - ', l.name) AS school,
    ss.mobile,
    s.reason,
    s.pob,
    s.dob,
    s.gender,
    s.photo
FROM 
    students s
 JOIN student_promotion sp ON sp.student_id=s.student_id
    JOIN class_sections cs ON sp.to_class = cs.id
    JOIN classes c ON cs.class_id = c.id
    
    JOIN sections se ON cs.section_id = se.id
    JOIN school_levels sl ON c.school_level_id = sl.id
    JOIN schools sc ON sl.school_id = sc.id
    JOIN levels l ON sl.level_id = l.id
    INNER JOIN sponsors ss ON ss.id = s.sponsor_id
    LEFT JOIN districts d ON d.id = s.district
  JOIN academicyear acc ON sp.from_academic=acc.id
WHERE 
    s.student_id =p_student_id AND  acc.id=p_accy
ORDER BY 
    s.id ASC;



END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_header_info` (IN `p_student_id` VARCHAR(100))   BEGIN
SELECT 
    s.id,
    s.student_id,
    s.student_name,
    CONCAT(c.class_name, '(', se.section, ')') AS class_name,
    s.address,
    d.districts AS Districts, 
    s.mother_name,
    ss.sponsor_name,
    CONCAT(sc.school_name, ' - ', l.name) AS school,
    ss.mobile,
    s.reason,
    s.pob,
    s.dob,
    s.gender,
    s.photo
FROM 
    students s
    JOIN class_sections cs ON s.class_id = cs.id
    JOIN classes c ON cs.class_id = c.id
    JOIN sections se ON cs.section_id = se.id
    JOIN school_levels sl ON c.school_level_id = sl.id
    JOIN schools sc ON sl.school_id = sc.id
    JOIN levels l ON sl.level_id = l.id
    INNER JOIN sponsors ss ON ss.id = s.sponsor_id
    LEFT JOIN districts d ON d.id = s.district
WHERE 
    s.student_id = p_student_id
ORDER BY 
    s.id ASC;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_header_info1` (IN `p_student_id` INT)   BEGIN
SELECT s.student_id,s.student_name,s.gender,s.reg_date as 'Admission_date',s.dob as "Date of birth",s.pob as "Place of birth",s.mother_name,s.phone as "Mother Phone",s.address,ds.Districts,ors.name as orphan_status,concat(sp.id,' -',sp.sponsor_name) as sponsor_name,sp.mobile as s_phone,sp.address as "Guardian address",s.relation,ifnull(bg.name,"N/A") as "Blood Group",s.address as address,s.photo, concat(c.class_name,'/',se.section) as class,s.status,s.class_id,st.status_name as status_name,sl.id as school_levels,concat(sc.school_name,' - ',l.name) as school_name,s.date FROM students s JOIN class_sections cs on s.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id  JOIN levels l on sl.level_id = l.id left JOIN districts ds on ds.id=s.district 
JOIN orphan_status ors ON ors.id=s.orphan_status
LEFT JOIN status st on st.id=s.status
inner join sponsors sp on s.sponsor_id = sp.id LEFT JOIN blood_groups bg on s.blood_group = bg.id where  s.student_id = p_student_id ORDER by s.id ASC;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_inspector` (IN `p_sidebar` INT, IN `p_user` INT)  NO SQL BEGIN	
SELECT p.added,p.edited,p.deleted into @added,@edited,@deleted FROM sidebar s left join company_permission cp on s.id=cp.sidebar_id INNER join permission p on p.profile_permission= cp.id and p.user_id= p_user and s.id=p_sidebar;
SELECT e.name 'Name',concat(sum(k.points),'/',ki.marks) 'Points',ac.Name 'Academic year',ki.date,u.full_name 'user',
(CASE
WHEN @edited=1 THEN CONCAT('<a href="Diiwaangalin/frm_edit_kormeer.php?id=',ki.id,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>')
 ELSE
 '-'
END)
 as 'Edit'  from kormeer_info ki INNER join kormeer_points kp on kp.kormeer_info=ki.id INNER join kormeer k on k.id=kp.kormeer_id INNER join employee e on e.id=ki.teacher_id INNER join academicyear ac on ac.id=ki.academic_year and ac.is_active='yes' INNER join users u on u.id=ki.user_id INNER join shaqada s on e.shaqada=s.id INNER join  qaybta q on q.id= e.qeybta INNER JOIN employee_permission ep on ep.qaybta=e.shaqada and ep.user_id=p_user GROUP by e.id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_lesson` ()   BEGIN
SELECT  c.class_name as class_name,a.Name 'academic_year_id',sg.name as subject_group,s.subject_name, 
GROUP_CONCAT(l.name SEPARATOR ' <br> ') as lesson ,CONCAT('<a href="../Resource/delete_lesson.php?academic_year_id=', l.academic_year_id,'&classes=', l.subject_group_class_sections_id,'&subject=', l.subject_group_subject_id,'" class="btn btn-xs delete_link"><i class="fas fa-trash-alt"></i></a>') `Delete`,
CONCAT('<a href="" data-href="../Form/frm_editlesson.php" data-academic_year_id="', l.academic_year_id,'" data-class="', l.subject_group_class_sections_id,'" data-subject="', l.subject_group_subject_id,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM lesson l 
inner JOIN subject_group_class_sections sgc on l.subject_group_class_sections_id =sgc.class_section_id 
JOIN subject_groups sg on sgc.subject_group_id = sg.id  and sg.status=0
JOIN subjects s on l.subject_group_subject_id = s.id 
join classes c on c.id=l.subject_group_class_sections_id
JOIN academicyear a on a.id=l.academic_year_id
GROUP BY l.subject_group_subject_id,l.academic_year_id ORDER BY l.name;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_levels` ()   SELECT l.name "Magaca Heerka",CONCAT('<a href="" data-id="',l.id,'" data-level="',l.name,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM levels l$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_meeting` ()   BEGIN

SELECT 
    s.student_name AS "Student",
    sp.sponsor_name AS "Guardian",
    CASE WHEN m.noted = 1 THEN 'Yes' ELSE 'No' END AS "Noted",
    chs.name AS "Status",
    m.issue AS "Reason",
    m.date_reported AS "Date",
    CONCAT(
        '<a href="" data-id="', m.id, 
        '" data-student_id="', m.student_id, 
        '" data-sponsor_id="', m.sponsor_id, 
        '" data-noted="', m.noted, 
        '" data-status="', m.status_id, 
        '" data-issue="', m.issue, 
        '" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a> ',
        '<form action="../Resource/print_info.php" method="POST" style="display: inline;">',
        '<input type="hidden" name="id" value="', m.id, '">',
        '<button type="submit" class="btn btn-xs ml-02 print_link">',
        '<i class="fa fa-print" aria-hidden="true"></i>',
        '</button>',
        '</form>'
    ) AS `Edit` 
FROM 
    meeting m 
JOIN 
    students s ON m.student_id = s.student_id 
JOIN 
    sponsors sp ON m.sponsor_id = sp.id 
JOIN 
    challenge_status chs ON m.status_id = chs.id;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_menu` ()  NO SQL BEGIN
SELECT mn.name,mn.icon,CONCAT('<a href="" data-toggle="tooltip" data-placement="left" title="Edit" class="btn btn-xs edit" data-id="',mn.id,'" data-menu="',mn.name,'" data-icon="',mn.icon,'" ><i class="fas fa-edit"></i></a>')as edit from menu mn;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_parents` (IN `p_user_name` VARCHAR(50))   BEGIN
SELECT u.id AS user_id, sp.id, sp.sponsor_name AS full_name, c.name, sp.mobile,
           CONCAT('../../', c.logo) AS logo, sp.address, c.id AS profile_id,
           rl.name AS Role, rl.id AS role_id, NULL AS image,  
           u.user_name, c.email, "success" AS msg, 
           "lib/Dashboard/parents.php" AS dashboard
    FROM user u 
    INNER JOIN roles rl ON rl.id = u.role_id AND rl.id NOT IN (8, 9)
    INNER JOIN sponsors sp ON sp.id = u.employee_id
    JOIN company_profile c ON c.id = u.profile
    WHERE u.user_name = p_user_name;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_parent_users` (IN `sp_id` INT)   BEGIN

SELECT sp.sponsor_name,u.user_name,u.password FROM user u 
JOIN sponsors sp ON u.employee_id=sp.id
WHERE sp.id=sp_id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_pass_mark` (IN `p_company_id` INT)   SELECT l.name 'HEERKA',p.type 'NOOCA',p.language 'LUQADA',p.marks 'DARAJADA',CONCAT('<a href="actions/edit.php?table=pass_mark&form=Diiwaangalin/frm_addpass_mark.php&sp=sp_add_pass_marks&id=',P.id,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') 'Edit'  FROM pass_mark p inner join levels l on l.id=p.level_id WHERE p.company_id=p_company_id$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_receipt_method` (IN `p_company_id` INT)   SELECT rm.method "Nooca",rm.description "Faahfaahin",CONCAT('<a href="actions/edit.php?table=receipt_method&form=Diiwaangalin/frm_addreceipt_method.php&sp=sp_add_receipt_method&id=',rm.id,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM receipt_method rm where rm.company_id = p_company_id$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_result_create` (IN `p_company_id` INT)   BEGIN
SELECT  sg.name as Name,ifnull(GROUP_CONCAT( DISTINCT s.subject_name ORDER BY s.id  SEPARATOR ' , '),'') 'Subjects',ifnull(GROUP_CONCAT( DISTINCT cs.class_name ORDER BY cs.id  SEPARATOR ' , '),'') 'Class' ,CONCAT('<a href="Diiwaangalin/frm_update_subject_group.php?id=',cs.id,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM subject_groups sg inner join subject_group_subjects sgs on sg.id=sgs.subject_group_id inner join subjects s on s.id=sgs.subject_id
INNER join subject_group_class_sections sgss on sgss.subject_group_id=sg.id INNER join classes cs on cs.id=sgss.class_section_id
GROUP by sg.id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_role` (IN `p_role_id` INT)  NO SQL BEGIN


if (p_role_id=7)THEN
SELECT rl.name as Role,CONCAT('<a href="" data-toggle="tooltip" data-placement="left" title="Edit" class="btn btn-xs edit" data-id=',rl.id,' data-role=',rl.name,' ><i class="fas fa-edit"></i></a>','<a href="Assign_Permission.php" data-role_id="',rl.id,'"  data-role_name="',rl.name,'"  data-toggle="tooltip" data-placement="left" title="Assign Permission" class="btn btn-xs update_link"><i class="fa fa-tag"></i></a>') `Edit` from roles rl ; 
ELSE
SELECT rl.name as Role,CONCAT('<a href="" data-toggle="tooltip" data-placement="left" title="Edit" class="btn btn-xs edit" data-id=',rl.id,' data-role=',rl.name,' ><i class="fas fa-edit"></i></a>','<a href="Assign_Permission.php" data-role_id="',rl.id,'"  data-role_name="',rl.name,'"  data-toggle="tooltip" data-placement="left" title="Assign Permission" class="btn btn-xs update_link"><i class="fa fa-tag"></i></a>') `Edit` from roles rl where id not in(7,8,9); 
END if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_schools` ()   BEGIN
SELECT s.school_name "Iskuulka",s.phone "Taleefoon-ka",s.address "Deegaanka",CONCAT('<a href="" data-id="',s.id,'" data-name="',s.school_name,'" data-phone="',s.phone,'" data-address="',s.address,'"   data-level="',GROUP_CONCAT(sl.level_id SEPARATOR ','),'"class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit`FROM schools s join school_levels sl on sl.school_id=s.id GROUP by s.id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_school_fee` (IN `p_company_id` INT)   BEGIN
SELECT s.school_name as "Magaca Iskuulka",ft.fee_name "Nooca Lacagta",sf.amount "Lacagta",a.Name "Sanad Waxbarasheedka",CONCAT('<a href="actions/edit.php?table=school_fee&form=Diiwaangalin/frm_addschool_fee.php&sp=sp_add_school_fee&id=',sf.id,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM school_fee sf join schools s on sf.school_id = s.id join fee_type ft on sf.fee_type_id= ft.id join academicyear a on sf.academidyear_id= a.id where sf.company_id = p_company_id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_sections` ()   SELECT s.section,CONCAT('<a href="" data-id="',s.id,'"  data-section="',s.section,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM sections s$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_signle_employee_detail` (IN `p_emp_id` INT)  NO SQL BEGIN
SELECT concat('EMP-',e.id) 'id',e.name,e.gender,cc.name 'Country',s.name 'States',c.name 'City',e.dob,e.tell_1,e.tell_2,e.address_1,e.address_2,e.email,e.mother_name,
e.martial_status,e.aqoonsi,e.jinsiyada_aqoonsiga,e.akoon_bangi,e.lambarka,
sh.Name 'shaqada',qa.Name 'Qaybta',e.galinka 'Galinka',e.mushaar 'Mushar',e.lacagta,e.Date_of_Issue,e.Date_of_Starting,es1.name 'Masul 1',e.xiriirka_1,concat(es1.tell1 ,' - ',es1.tell2) 'Tell_1',es2.name 'Masul 2',e.xiriirka_2,concat(es2.tell1 ,' - ',es2.tell2) 'Tell_2',a.Name 'Aqoonta',e.goobta,e.kuliyada,e.takhasuska,e.magaca,e.faafaahin,e.image from employee e 
INNER join cities c on e.cities=c.id 
INNER join states s on s.id=c.state_id 
INNER join countries cc on cc.id=s.country_id 
INNER join aqoonta a on a.id=e.aqoonta 
INNER join shaqada sh on sh.id=e.shaqada
INNER join qaybta qa on qa.id=e.qeybta
INNER join employee_sponser es1 on es1.id=e.masul_1
INNER join employee_sponser es2 on es2.id=e.masul_2
WHERE e.id=p_emp_id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_signle_students_detail` (IN `p_student_id` VARCHAR(100))  NO SQL BEGIN

SELECT s.student_id,s.student_name,s.gender,s.reg_date as 'Admission_date',s.dob as "Date of birth",s.pob as "Place of birth",s.mother_name,s.phone as "Mother Phone",s.address,concat(sp.id,' -',sp.sponsor_name) as sponsor_name,sp.mobile,sp.address as "Guardian address",s.relation,ifnull(bg.name,"N/A") as "Blood Group",concat(c.class_name,'/',se.section) as class,s.photo as Image,s.status,s.class_id,st.status_name,sl.id as school_levels,concat(sc.school_name,' - ',l.name) as school_name FROM students s JOIN class_sections cs on s.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id  JOIN levels l on sl.level_id = l.id
LEFT JOIN status st on st.id=s.status
inner join sponsors sp on s.sponsor_id = sp.id LEFT JOIN blood_groups bg on s.blood_group = bg.id where  s.id = p_student_id ORDER by s.id ASC;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_signle_students_detail1` (IN `p_student_id` INT)  NO SQL BEGIN
SELECT s.student_id,s.student_name,s.gender,s.reg_date as 'Admission_date',s.dob as "Date of birth",s.pob as "Place of birth",s.mother_name,s.phone as "Mother Phone",s.address,sp.sponsor_name,sp.mobile,sp.address as "Guardian address",s.relation,ifnull(bg.name,"N/A") as "Blood Group",concat(c.class_name,' ',se.section) as class,s.photo as Image,s.status,concat(sc.school_name,' - ',l.name) as school_name,sl.id as school_levels  FROM students s JOIN class_sections cs on s.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id  JOIN levels l on sl.level_id = l.id
inner join sponsors sp on s.sponsor_id = sp.id LEFT JOIN blood_groups bg on s.blood_group = bg.id where  s.id = p_student_id ORDER by s.id ASC;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_signle_students_detail2` (IN `p_student_id` VARCHAR(100))   BEGIN

SELECT s.student_id,s.student_name,s.gender,s.reg_date as 'Admission_date',s.dob as "Date of birth",s.pob as "Place of birth",s.mother_name,s.phone as "Mother Phone",s.address,concat(sp.id,' -',sp.sponsor_name) as sponsor_name,sp.mobile,sp.address as "Guardian address",s.relation,ifnull(bg.name,"N/A") as "Blood Group",concat(c.class_name,'-',se.section) as class,s.photo as Image,s.status,s.class_id,st.status_name,sl.id as school_levels,concat(sc.school_name,'-',l.name) as school_name FROM students s JOIN class_sections cs on s.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id  JOIN levels l on sl.level_id = l.id
LEFT JOIN status st on st.id=s.status
inner join sponsors sp on s.sponsor_id = sp.id LEFT JOIN blood_groups bg on s.blood_group = bg.id where  s.student_id = p_student_id ORDER by s.id ASC;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_signle_teacher_detail` (IN `p_id` INT)  NO SQL select * from employee where id=p_id$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_single_student_detail_Admision_Report` (IN `p_student_id` VARCHAR(100))   BEGIN
SELECT s.student_id,s.student_name,s.gender,s.reg_date as 'Admission_date',s.dob as "Date of birth",s.pob as "Place of birth",s.mother_name,s.phone as "Mother Phone",s.address,ds.Districts,ors.name as orphan_status,concat(sp.id,' -',sp.sponsor_name) as sponsor_name,sp.mobile as s_phone,sp.address as "Guardian address",s.relation,ifnull(bg.name,"N/A") as "Blood Group",s.address as address,s.photo, concat(c.class_name,'/',se.section) as class,s.status,s.class_id,st.status_name as status_name,sl.id as school_levels,concat(sc.school_name,' - ',l.name) as school_name,s.date FROM students s JOIN class_sections cs on s.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id  JOIN levels l on sl.level_id = l.id left JOIN districts ds on ds.id=s.district 
JOIN orphan_status ors ON ors.id=s.orphan_status
LEFT JOIN status st on st.id=s.status
inner join sponsors sp on s.sponsor_id = sp.id LEFT JOIN blood_groups bg on s.blood_group = bg.id where  s.student_id = p_student_id ORDER by s.id ASC;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_sponser_by_school` (IN `p_school` INT)   BEGIN


SELECT 
    sp.id,
   
    sp.sponsor_name,
    sp.mobile
FROM sponsors sp
LEFT JOIN students s ON s.sponsor_id = sp.id
LEFT JOIN class_sections cs ON cs.id = s.class_id
LEFT JOIN classes c ON c.id = cs.class_id
LEFT JOIN school_levels sl ON sl.id = c.school_level_id
LEFT JOIN schools sch ON sch.id = sl.school_id
JOIN sections se ON cs.section_id = se.id 

JOIN levels l ON sl.level_id = l.id
LEFT JOIN districts ds ON ds.id = s.district
INNER JOIN status ss ON ss.id = s.status 
WHERE sch.id = p_school
GROUP BY sp.id, sch.school_name, sp.sponsor_name, sp.mobile;


SELECT 
   COUNT(DISTINCT sp.id) AS total_sponsor
FROM sponsors sp
LEFT JOIN students s ON s.sponsor_id = sp.id
LEFT JOIN class_sections cs ON cs.id = s.class_id
LEFT JOIN classes c ON c.id = cs.class_id
LEFT JOIN school_levels sl ON sl.id = c.school_level_id
LEFT JOIN schools sch ON sch.id = sl.school_id
JOIN sections se ON cs.section_id = se.id 

JOIN levels l ON sl.level_id = l.id
LEFT JOIN districts ds ON ds.id = s.district
INNER JOIN status ss ON ss.id = s.status 
WHERE sch.id = p_school;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_sponsors` ()   BEGIN
SELECT s.sponsor_name "Waalidka",s.gender "Jinsiga",s.mobile,s.address "Deegaanka",CONCAT('<a href="" data-id="',s.id,'" data-href="../Form/frm_addsponsor.php" data-table="sponsors" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit`,
CONCAT('<a href="" data-id="',s.id,'" class="btn   btn-Light btn-sm delete_link"><i class="fas fa-trash"></a>')
     as  `Delete`


FROM sponsors s;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_std_behavior` (IN `p_level` INT, IN `p_class_id` INT, IN `p_section` VARCHAR(50), IN `p_accy` INT)   BEGIN

SELECT ac.id INTO @accy from academicyear ac where ac.is_active='yes';

SELECT s.student_id,s.student_name 'Student Name',concat(c.class_name,' ',se.section,' ',c.shift) as "Class"  FROM students s 
JOIN student_promotion ssp on ssp.student_id=s.student_id  and ssp.from_academic=p_accy
JOIN class_sections cs on ssp.to_class = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id 
  
 JOIN levels l on sl.level_id = l.id
left JOIN districts ds on ds.id=s.district
INNER join status ss on ss.id=s.status 
inner join sponsors sp on sp.id=s.sponsor_id where sl.id like p_level and c.id=p_class_id and cs.id like p_section
and s.status=2
ORDER by s.id ASC ;


END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_students` (IN `p_class_id` VARCHAR(50), IN `p_level` VARCHAR(50), IN `p_shift` VARCHAR(50), IN `p_user_id` INT, IN `p_school` INT)  NO SQL BEGIN

SELECT s.id as id,s.student_id,s.student_name 'Magaca Ardayga',s.Gender 'Jinsiga',concat(c.class_name,' ',se.section) as "Fasalka",s.phone 'Phone',sp.sponsor_name as 'Masuulka',sp.mobile as 'Masuulka No',s.dob ,s.pob,ds.Districts as district,s.address, 	s.mother_name 'Hooyada',ss.status_name 'Status',

    CONCAT('<a href="" data-href="../Form/frm_editstudent.php" data-id=',s.id,'" class="btn btn-xs update_student_link"><i class="fas fa-edit"></i></a>')
   
 as 'Edit',
 
CONCAT('<a href="" data-href="../Resource/student_profile.php" data-id="',s.id,'" class="btn btn-xs view_link"><i class="fas fa-eye"></i></a>') as 'View' FROM students s JOIN class_sections cs on s.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id 
 INNER join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user_id
 JOIN levels l on sl.level_id = l.id
left JOIN districts ds on ds.id=s.district
INNER join status ss on ss.id=s.status 
LEFT JOIN sponsors sp on sp.id=s.sponsor_id, (SELECT @rownum:=0) r where sl.id like p_level and cs.id like p_class_id and c.shift like p_shift

and s.status=2  and sc.id=p_school
ORDER by s.student_name ASC ;



END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_studentsByGender` (IN `p_class_id` VARCHAR(50), IN `p_level` VARCHAR(50), IN `p_shift` VARCHAR(50), IN `p_user_id` INT, IN `p_school` INT)  NO SQL BEGIN

SELECT count(s.student_id) as 'All',sum(case when s.gender = 'Male' then 1 else 0 end) as 'Male',sum(case when s.gender = 'FeMale' then 1 else 0 end) as 'Female' FROM students s JOIN class_sections cs on s.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id 
 INNER join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user_id
 JOIN levels l on sl.level_id = l.id
join sponsors sp on sp.id=s.sponsor_id where sl.id like p_level and cs.id like p_class_id and c.shift like p_shift
and s.status=2 and sc.id=p_school;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_students_info` (IN `p_class_id` INT, IN `p_academidyear_id` VARCHAR(50), IN `p_status` VARCHAR(50))  NO SQL BEGIN
SELECT s.id as 'StudentID', s.StudentName, a.Name as academicyear,sp.sponsor_name,sp.mobile FROM students s join sponsors sp on s.sponsor_id = sp.id join academicyear a on s.RegAcademicYear = a.id  where s.class_id = p_class_id and s.RegAcademicYear like p_academidyear_id and s.status like p_status;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_student_applyleave` (IN `p_class_id` VARCHAR(1000))   BEGIN
SELECT s.id,s.student_id,s.student_name 'Magaca Ardayga',sc.school_name as 'Dugsiga',concat(c.class_name,' ',se.section) as "Fasalka",sap.apply_date,sap.from_date,sap.to_date,sap.status as 'Status',CONCAT('<a href="Diiwaangalin/frm_edit_student_applyleave.php?id=',sap.id,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM 
student_applyleave sap
inner JOIN
students s on s.student_id=sap.student_id JOIN class_sections cs on s.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id JOIN levels l on sl.level_id = l.id

where  cs.id LIKE p_class_id 
ORDER by id DESC ;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_student_attendence_detail` (IN `p_student_id` VARCHAR(100), IN `p_from` DATE, IN `p_to` DATE)  NO SQL BEGIN
 SELECT s.id,s.student_id,s.student_name,concat(c.class_name,'(',se.section,')') as class_name,sa.`date`,ifnull(at.type,"Unattended") as `status`,x.xiisada,sa.remark FROM student_attendence sa RIGHT JOIN students s on sa.student_id = s.student_id JOIN attendence_type at on sa.attendence_type_id = at.id  JOIN class_sections cs on sa.class_id = cs.id JOIN classes c on cs.class_id = c.id JOIN sections se on cs.section_id = se.id inner join xiisadaha x on x.id=sa.xiisada WHERE s.student_id = p_student_id and sa.date BETWEEN p_from and p_to  ORDER BY sa.date ASC;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_student_behavior` (IN `p_user_id` INT)   BEGIN
SELECT s.student_id "Student ID",s.student_name "Student Name",concat(c.class_name,' ',se.section, ' ', c.shift) as class,b.name as 'Behavior',sb.subject "Subject",sb.reason "Reason",sb.date 'Date', CONCAT('<a href="" data-href="../Form/frm_update_student_behavior.php" data-tables="student_behavior" data-id="',sb.id,'" class="btn btn-xs update_link mr-2"><i class="fas fa-edit"></i></a>','<a href="" data-id="',sb.id,'" class="btn   btn-light btn-sm delete_link"><i class="fas fa-trash-alt"></i></a>') `Actions` FROM student_behavior sb JOIN students s on sb.student_id = s.student_id 
JOIN class_sections cs on s.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id 
 INNER join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user_id
 JOIN levels l on sl.level_id = l.id
JOIN behavior b on sb.type = b.id 
JOIN behavior_type bt on b.type = bt.id
HAVING concat( (case when  month(sb.date) BETWEEN 8 and 12 then Year(sb.date) ELSE Year(sb.date)-1 END),'-',(case when  month(sb.date) BETWEEN 8 and 12 then Year(sb.date)+1 ELSE Year(sb.date) END) )=(SELECT ac.Name from academicyear ac where ac.is_active='yes');
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_subjects` ()   BEGIN
SELECT s.code 'Code',s.subject_name "Maadada",s.Description "Faahfaahin",CONCAT('<a href=""  data-id="',s.id,'" data-code="',s.code,'"  data-subject="',s.subject_name,'" data-Description="',s.Description,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM subjects s;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_subject_group` ()   BEGIN
SELECT  sg.name as Name,ifnull(GROUP_CONCAT( DISTINCT s.subject_name ORDER BY s.id  SEPARATOR ' , '),'') 'Subjects',ifnull(GROUP_CONCAT( DISTINCT cs.class_name ORDER BY cs.id  SEPARATOR ' , '),'') 'Class',a.Name 'Acedemic Year' ,CONCAT('<a href="" data-href="../Form/frm_update_subject_group.php" data-id="',sg.id,'" data-table="" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit`,CONCAT('<a href="#" value="',sg.id,'" class="btn btn-xs delete_link"><i class="fas fa-trash-alt"></i></a>') `Delete` FROM subject_groups sg inner join subject_group_subjects sgs on sg.id=sgs.subject_group_id inner join subjects s on s.id=sgs.subject_id
INNER join subject_group_class_sections sgss on sgss.subject_group_id=sg.id INNER join classes cs on cs.id=sgss.class_section_id
INNER JOIN academicyear a on a.id=sg.acedemic_year where sg.status=0
GROUP by sg.id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_subject_track` (IN `p_from` DATE, IN `p_to` DATE, IN `p_class` INT, IN `p_section` VARCHAR(50), IN `p_subject_id` VARCHAR(100))   SELECT w.StartDate ,w.EndDate, Concat(c.class_name,'-',se.section) as class,su.subject_name as Subject,w.WeekNumber as Week, st.lesson as Lesson ,st.user_id, st.date FROM  `subject_tracking` st
JOIN class_sections cs on st.section= cs.id 
join classes c on st.class = c.id 
join sections se on cs.section_id = se.id 
JOIN school_levels sl on c.school_level_id = sl.id
JOIN schools sc on sl.school_id = sc.id 
 JOIN levels l on sl.level_id = l.id
 JOIN subjects su ON st.subject_id=su.id
 JOIN weeks w ON st.week_id=w.WeekID
JOIN user u ON st.user_id=u.id
JOIN employee em ON u.employee_id=em.id
 WHERE st.date BETWEEN p_from  AND  p_to  AND st.class like p_class AND st.section like p_section  AND su.subject_name=p_subject_id$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_sub_menu` (IN `p_id` INT)  NO SQL BEGIN
SELECT mn.name,sd.text,CONCAT('<a href="" data-toggle="tooltip" data-placement="left" title="Edit" class="btn btn-xs edit" data-id="',sd.id,'" data-menu="',mn.id,'" data-href="',sd.href,'" data-text="',sd.text,'"  data-view="',sd.enable_view,'"  data-add="',sd.enable_add,'"  data-edit="',sd.enable_edit,'"  data-delete="',sd.enable_delete,'"   data-is_system="',sd.is_system,'"  data-is_admin="',sd.is_admin,'" data-is_student="',sd.is_student,'" data-is_parent="',sd.is_parent,'" ><i class="fas fa-edit"></i></a>')as edit  FROM sidebar sd INNER join menu mn on mn.id=sd.menu_id where mn.id=p_id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_syllable_status` (IN `p_class_id` INT, IN `p_subject_id` INT)   BEGIN
SELECT l.name as lesson, GROUP_CONCAT(t.name SEPARATOR ',' ) as topic,ifnull(t.complete_date,'') as completion_date,t.status FROM  topic t JOIN lesson l on t.lesson_id = l.id JOIN subject_group_subjects sgs on l.subject_group_subject_id = sgs.id JOIN subject_groups sg on sgs.subject_group_id = sg.id JOIN subjects s on sgs.subject_id = s.id JOIN subject_group_class_sections sgc on sgc.subject_group_id = sg.id JOIN class_sections cs on sgc.class_section_id = cs.id JOIN classes c on cs.class_id = c.id JOIN sections se on cs.section_id = se.id WHERE l.subject_group_subject_id = p_subject_id and l.subject_group_class_sections_id = p_class_id  GROUP BY l.id ORDER BY l.name ASC;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_teachers` (IN `p_company_id` INT)   BEGIN
SELECT te.image 'Image',te.teacher_name "Macalinka",te.gender "Jinsiga",CONCAT(te.mobile,' - ',te.Emergency_Contact) 'mobile',CONCAT(te.address, '-',te.other_address)"Deegaanka",CONCAT('<a href="actions/edit.php?table=teachers&form=Diiwaangalin/frm_addteacher.php&sp=sp_add_teacher&id=',te.id,'" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>') `Edit`,CONCAT('<a href="Diiwaangalin/teacher_profile.php?id=',te.id,'" class="btn btn-xs view_link"><i class="fas fa-eye"></i></a>') as 'View' FROM teachers te  where te.company_id = p_company_id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_timetable` (IN `p_day` VARCHAR(50), IN `p_class_id` INT)   BEGIN
SELECT tt.id,ts.class_section_id,ts.subject_id,ts.teacher_id,tt.day_name,tt.start_time,tt.end_time FROM timetables tt CROSS JOIN teacher_subjects ts on tt.teacher_subject_id = ts.id WHERE ts.class_section_id = p_class_id and tt.day_name = p_day;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_topic` ()   BEGIN
SELECT c.class_name as Class,sg.name as 'Subject Group',s.subject_name 'Subject',ac.Name as Accy,l.name as lesson, GROUP_CONCAT(concat(t.name,' ',CONCAT('<a href="" data-href="../Form/frm_edittopic.php" data-id="',t.id,'" data-table="topics" class="btn btn-xs update_link"><i class="fas fa-edit"></i></a>')) SEPARATOR ' <br> ' ) as 'Edit',CONCAT('<a href="../Resource/delete_topic.php?id=', t.id,'" class="btn btn-xs delete_link"><i class="fas fa-trash-alt"></i></a>') `Delete`  FROM  topic t 
JOIN lesson l on t.lesson_id = l.id  
join subject_group_class_sections sgc on sgc.class_section_id=l.subject_group_class_sections_id
JOIN subjects s on l.subject_group_subject_id = s.id
join subject_group_subjects sgs on sgs.subject_id=s.id and sgs.subject_group_id=sgc.subject_group_id

JOIN subject_groups sg on sg.id=sgc.subject_group_id and sg.acedemic_year=l.academic_year_id  
JOIN academicyear ac on ac.id=l.academic_year_id
JOIN classes c on l.subject_group_class_sections_id = c.id GROUP BY l.id  ORDER BY l.name ASC;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_top_class_attendence_detail` (IN `p_from` DATE, IN `p_to` DATE, IN `p_user_id` INT, IN `p_type` VARCHAR(10))  NO SQL BEGIN
create TEMPORARY table table2 AS
(
SELECT concat(c.class_name,'(',se.section,')') as class_name,sa.`date`,
concat(at.type ,'(' ,COUNT(at.id),')') 'status',x.xiisada,sa.remark,COUNT(at.id) as total FROM student_attendence sa RIGHT JOIN students s on sa.student_id = s.student_id JOIN attendence_type at on sa.attendence_type_id = at.id  JOIN class_sections cs on s.class_id = cs.id JOIN classes c on cs.class_id = c.id JOIN sections se on cs.section_id = se.id 
    JOIN school_levels sl on c.school_level_id = sl.id  
		join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user_id
    inner join xiisadaha x on x.id=sa.xiisada  
    where sa.date BETWEEN p_from and p_to and s.status  in(1,2) and sa.attendence_type_id like p_type
GROUP by c.id,sa.attendence_type_id
ORDER BY sa.date ASC

);
SELECT class_name,ifnull(GROUP_CONCAT( DISTINCT status ORDER BY  class_name   SEPARATOR' <br> '),'') 'status',SUM(total) as total,ifnull(GROUP_CONCAT( DISTINCT remark ORDER BY  class_name  SEPARATOR' <br> '),'') 'remark'  from table2 Group by class_name ORDER by total DESC;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_top_class_behavior_detail` (IN `p_category` VARCHAR(50), IN `p_from` DATE, IN `p_to` DATE, IN `p_user_id` INT, IN `p_type` VARCHAR(100))   BEGIN
CREATE TEMPORARY TABLE table2 as(
SELECT   concat(c.class_name,' ' ,se.section) 'class',concat(b.name ,' :', COUNT(b.name)) 'behavior_type',sb.reason ,COUNT(b.name) as total FROM behavior_type bt JOIN behavior b on b.type = bt.id JOIN student_behavior sb on sb.type = b.id 
JOIN students s on sb.student_id = s.student_id
JOIN class_sections cs on sb.class_id = cs.id 
JOIN classes c on cs.class_id = c.id JOIN sections se on cs.section_id = se.id
    JOIN school_levels sl on c.school_level_id = sl.id  
    
		join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user_id
    where date(sb.date)BETWEEN p_from and p_to and bt.IsBad like p_type
    GROUP by  cs.id,bt.id,b.id
);
SELECT class, ifnull(GROUP_CONCAT( DISTINCT behavior_type   SEPARATOR ' <br> '),'') 'behavior_type',sum(total) as total from table2 GROUP by class ORDER by total desc;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_top_employee_behavior_detail` (IN `p_anshax` VARCHAR(50), IN `p_from` DATE, IN `p_to` DATE, IN `p_user_id` INT)   BEGIN
CREATE TEMPORARY TABLE table1  AS(
SELECT e.id,concat(es.name ,' :', COUNT(es.id)) 'Behavior',COUNT(es.id) as total from employee_behavior eb 
INNER join employee e on e.id=eb.Emp_id
INNER join employee_status es on es.id=eb.anshaxa
    INNER JOIN employee_permission ep on ep.qaybta=e.qeybta and ep.user_id=p_user_id
    WHERE es.id like p_anshax and eb.date BETWEEN p_from and p_to
    GROUP by e.id,es.id);
 
    CREATE TEMPORARY TABLE table3 
SELECT id, ifnull(GROUP_CONCAT( DISTINCT Behavior   SEPARATOR ' <br> '),'') 'Behavior',sum(total) as total from table1 GROUP by id;



CREATE TEMPORARY TABLE table2
SELECT e.id,e.name 'Name',ifnull(GROUP_CONCAT(  eb.remark   SEPARATOR ' <br> '),'') 'Reason' from employee_behavior eb 
INNER join employee e on e.id=eb.Emp_id
INNER join employee_status es on es.id=eb.anshaxa 
    WHERE es.id like p_anshax and eb.date BETWEEN p_from and p_to
    GROUP by e.id;

SELECT concat('EMP-',t2.id) 'id',t2.Name, t3.Behavior  ,T2.Reason,t3.total from  table2 t2   inner join table3 t3 on t3.id=t2.id ORDER by t3.total  DESC;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_top_level_behavior_detail` (IN `p_category` VARCHAR(50), IN `p_from` DATE, IN `p_to` DATE, IN `p_user_id` INT, IN `p_type` VARCHAR(100))   BEGIN
CREATE TEMPORARY TABLE table2 as(
SELECT  ls.name as 'level',concat(b.name ,' :', COUNT(b.name)) 'behavior_type',sb.reason ,COUNT(b.name) as total FROM behavior_type bt JOIN behavior b on b.type = bt.id JOIN student_behavior sb on sb.type = b.id 
JOIN students s on sb.student_id = s.student_id
JOIN class_sections cs on sb.class_id = cs.id 
JOIN classes c on cs.class_id = c.id JOIN sections se on cs.section_id = se.id
    JOIN school_levels sl on c.school_level_id = sl.id  
    JOIN levels ls on ls.id=sl.level_id
		join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user_id
     where date(sb.date)BETWEEN p_from and p_to and bt.IsBad like p_type
    GROUP by  ls.id,bt.id,b.id
);
SELECT level, ifnull(GROUP_CONCAT( DISTINCT behavior_type   SEPARATOR ' <br> '),'') 'behavior_type',sum(total) as total from table2 GROUP by level ORDER by total desc;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_top_student_attendence_detail` (IN `p_from` DATE, IN `p_to` DATE, IN `p_user_id` INT, IN `p_attendence_type` VARCHAR(100))  NO SQL BEGIN
create TEMPORARY table table2 AS
(
SELECT s.id,s.student_id,s.student_name,concat(c.class_name,'(',se.section,')') as class_name,sa.`date`,
concat(at.type ,'(' ,COUNT(at.id),')') 'status',COUNT(at.id) as total,x.xiisada,sa.remark FROM student_attendence sa RIGHT JOIN students s on sa.student_id = s.student_id JOIN attendence_type at on sa.attendence_type_id = at.id  JOIN class_sections cs on sa.class_id = cs.id JOIN classes c on cs.class_id = c.id JOIN sections se on cs.section_id = se.id 
    JOIN school_levels sl on c.school_level_id = sl.id  
	join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user_id
    inner join xiisadaha x on x.id=sa.xiisada  
    
    where sa.date BETWEEN p_from and p_to and s.status  in(1,2) and sa.attendence_type_id like p_attendence_type
GROUP by sa.student_id,sa.attendence_type_id
ORDER BY sa.date ASC

);
SELECT student_id,student_name,class_name,ifnull(GROUP_CONCAT( DISTINCT status ORDER BY  student_id   SEPARATOR' <br> '),'') 'status',sum(total) as total,ifnull(GROUP_CONCAT( DISTINCT remark ORDER BY  student_id  SEPARATOR' <br> '),'') 'remark'  from table2 Group by student_id ORDER by total DESC;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_top_student_behavior_detail` (IN `p_category` VARCHAR(50), IN `p_from` DATE, IN `p_to` DATE, IN `p_user_id` INT, IN `p_type` VARCHAR(10))   BEGIN
CREATE TEMPORARY TABLE table2 as(
SELECT   s.student_id,s.student_name,concat(bt.type ,' :', COUNT(bt.type)) 'behavior_category',concat(b.name ,' :', COUNT(b.name)) 'behavior_type',sb.reason,concat(c.class_name,' ',se.section) as 'class',COUNT(bt.type) as total FROM behavior_type bt JOIN behavior b on b.type = bt.id JOIN student_behavior sb on sb.type = b.id JOIN students s on sb.student_id = s.student_id
    JOIN class_sections cs on sb.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id JOIN levels l on sl.level_id = l.id
    join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user_id
 WHERE   bt.id like p_category and sb.date BETWEEN p_from and p_to and bt.IsBad like p_type
    GROUP by sb.student_id,BT.id,b.id
);
SELECT student_id,student_name, ifnull(GROUP_CONCAT( DISTINCT behavior_type   SEPARATOR ' <br> '),'') 'behavior_type',reason ,class,sum(total) as total from table2 GROUP by student_id ORDER by total DESC;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_total_sponsers_byschool` (IN `p_school` INT)   BEGIN

 SELECT 
        sch.school_name as school,
        COUNT(DISTINCT sp.id) AS wadarta_walidka
    FROM sponsors sp
    LEFT JOIN students s ON s.sponsor_id = sp.id
    LEFT JOIN class_sections cs ON cs.id = s.class_id
    LEFT JOIN classes c ON c.id = cs.class_id
    LEFT JOIN school_levels sl ON sl.id = c.school_level_id
    LEFT JOIN schools sch ON sch.id = sl.school_id
    JOIN sections se ON cs.section_id = se.id 
   
    JOIN levels l ON sl.level_id = l.id
    LEFT JOIN districts ds ON ds.id = s.district
    INNER JOIN status ss ON ss.id = s.status 
    WHERE sch.id = p_school;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `rp_users` (IN `p_company_id` INT)  NO SQL SELECT u.full_name,u.user_name,CONCAT('<a href="actions/edit.php?table=users&form=Diiwaangalin/frm_addusers.php&sp=Sp_users&id=',u.id,'" data-form-label="Edit Category" class="btn btn-primary update_link"><i class="fas fa-edit"></i></a>') `Edit` FROM users u where u.profile=p_company_id and id!=2$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SaveEvent` (IN `event_name` VARCHAR(255), IN `event_date` DATE, IN `is_recurring` TINYINT(1), IN `accy` INT)   BEGIN
    INSERT INTO Events (EventName, EventDate, IsRecurring,accy)
    VALUES (event_name, event_date, is_recurring,accy);
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SearchStudent` (IN `p_other` VARCHAR(100), IN `p_user_id` INT)   BEGIN
SELECT s.id as 'student_o_id', s.student_id,s.current_academic,concat(s.student_id,'-',s.student_name,' - ', sp.sponsor_name,'-' ,l.name, '-' ,c.class_name,'/',se.section) as "search",sl.id as schoolid,cs.class_id as class,s.class_id  FROM students s JOIN class_sections cs on s.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id 
 INNER join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user_id
 JOIN levels l on sl.level_id = l.id
inner join sponsors sp on sp.id=s.sponsor_id  where concat(s.student_id,'-',s.student_name) like p_other AND s.status not in (6,0)
 ORDER BY LOCATE(p_other, concat(s.student_id,'-',s.student_name))   ;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `show_studentlist_bybatch` (IN `p_batch` VARCHAR(50), IN `p_gender` VARCHAR(50))  NO SQL SELECT `StudentID`, `StudentName`,'' as Marks FROM `students` WHERE BatchCode=p_batch and gender like concat(p_gender,'%') and STATUS='enrolled'
order by StudentName$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `show_students` (IN `p_company_id` INT)  NO SQL BEGIN


SELECT s.id,s.student_id,s.student_name 'Magaca Ardayga',s.gender 'Jinsiga',sc.school_name as 'Dugsiga',c.class_name as "Fasalka",concat(sp.sponsor_name,"-",sp.mobile) as 'Masuulka',CONCAT('<a href="actions/edit.php?table=students&form=Diiwaangalin/frm_addStudents.php&sp=sp_AddNewStudent&id=',s.id,'" class="btn btn-primary update_link"><i class="fas fa-edit"></i></a>') as 'Edit' FROM students s join classes c on s.class_id = c.id join schools sc on sc.id = c.school_id 
inner join sponsors sp on sp.id=s.sponsor_id where sc.company_id = p_company_id

ORDER by id DESC ;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPAddBusCash` (IN `p_student_id` VARCHAR(50), IN `p_class` INT, IN `p_bus` INT, IN `p_user_id` INT, IN `p_boono` VARCHAR(100), IN `p_tran_date` DATE, IN `p_amount` DECIMAL(18,2), IN `p_receipt_type` INT, IN `p_ref` VARCHAR(100))  NO SQL BEGIN

START TRANSACTION;

if EXISTS (SELECT * FROM cash_receipt cr where cr.boono=p_boono and cr.user_id!=p_user_id and cr.status=1)THEN
UPDATE setupid set Number=(SELECT max(boono)+1 FROM cash_receipt) WHERE name='Rv';
SELECT max(boono)+1 into p_boono FROM cash_receipt;
end IF;

SELECT max(id) into @academic_year from academicyear;

Insert into cash_receipt (tran_date,student_id ,class_id  ,month ,amount ,usd_no  ,user_id ,academic_year ,boono  ,receipt_by,bus_id ,fee_type)

VALUES( p_tran_date,p_student_id,p_class,'Bus',p_amount,
p_ref,p_user_id,@academic_year,p_boono,p_receipt_type,p_bus,8 ) ;


UPDATE setupid set Number=(SELECT max(boono)+1 FROM cash_receipt) WHERE name='Rv';

SELECT p_boono as success;
COMMIT;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPAddToBuss` (IN `p_student` VARCHAR(100), IN `p_type` VARCHAR(100), IN `p_status` INT, IN `p_bus` INT, IN `p_location` INT, IN `p_month` INT, IN `p_date` DATE, IN `p_user` INT)   BEGIN
SELECT ac.id into @accy from academicyear ac where ac.is_active='yes';
if not EXISTS(SELECT * from studentbus sb where sb.student_id=p_student and sb.type=p_type and sb.status=0)THEN
INSERT INTO `studentbus`(`student_id`, `type`, `Bus`, `location`, `StartDate`,  `status`, `user_id`,accy,from_month ) 
VALUES (p_student,p_type,p_bus,p_location,p_date,p_status,p_user,@accy,p_month);

 select'success| Data Inserted successfully ' as msg;
ELSE
UPDATE studentbus set Bus=p_bus,location=p_location where student_id=p_student and type=p_type and status=0;

 select'success| Data updated successfully ' as msg;
END if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPAddToFood` (IN `p_student` VARCHAR(100), IN `p_status` INT, IN `p_month` INT, IN `p_date` DATE, IN `p_user` INT)   BEGIN
SELECT ac.id into @accy from academicyear ac where ac.is_active='yes';
if not EXISTS(SELECT * from studentfood sb where sb.student_id=p_student  and sb.status=0)THEN
INSERT INTO `studentfood`(`student_id`, `StartDate`,  `status`, `user_id`,accy,from_month ) 
VALUES (p_student,p_date,p_status,p_user,@accy,p_month);

 select'success| Data Inserted successfully ' as msg;
END if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPCheckStudentBus` (IN `p_student_id` VARCHAR(100), IN `p_accy` INT)  NO SQL BEGIN
SELECT COUNT(*) as "no" from studentbus sb where sb.student_id=p_student_id and sb.accy=p_accy ;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPCheckStudentFood` (IN `p_student_id` VARCHAR(100), IN `p_accy` INT)  NO SQL BEGIN
SELECT COUNT(*) as "no" from studentfood sb where sb.student_id=p_student_id and sb.accy=p_accy ;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPEditBuss` (IN `p_id` INT, IN `p_bus` INT, IN `p_village` INT, IN `p_month` INT, IN `p_date` DATE, IN `p_user` INT)   BEGIN
UPDATE studentbus sb set sb.StartDate=p_date ,sb.Bus= p_bus,sb.location=p_village,sb.from_month=p_month
where sb.id=p_id;
select'success| Data Updated successfully ' as msg;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPEditFood` (IN `p_id` INT, IN `p_month` INT, IN `p_date` DATE, IN `p_user` INT)   BEGIN
UPDATE studentfood sb set sb.StartDate=p_date ,sb.from_month=p_month
where sb.id=p_id;
select'success| Data Updated successfully ' as msg;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPExpenseBill` (IN `p_id` INT, IN `p_school` VARCHAR(100), IN `p_type` VARCHAR(100), IN `p_name` VARCHAR(100), IN `p_amount` DECIMAL(18,2), IN `p_date` DATE, IN `p_remark` TEXT, IN `p_user` INT)   BEGIN
if EXISTS(SELECT * from Expensebill ir where ir.id=p_id)THEN
UPDATE Expensebill ir set ir.school_name=p_school, ir.type=p_type,ir.name=p_name,ir.amount=p_amount,ir.remark=p_remark,ir.date=p_date where ir.id=p_id;
SELECT 'success|Si sax ah ayaA loo beddelay';
ELSE
INSERT INTO `Expensebill`(`school`,`type`, `name`, `amount`,  `remark`, `date`, `user`) VALUES
(p_school,p_type, p_name, p_amount,  p_remark, p_date, p_user);
select 'success|Waa la diiwaangaliyey';
END IF;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPExpensePayment` (IN `p_id` INT, IN `p_invoice` INT, IN `p_method` INT, IN `p_amount` DECIMAL(18,2), IN `p_date` DATE, IN `p_remark` TEXT, IN `p_user` INT)   BEGIN
if EXISTS(SELECT * from expensepayment ep where id=p_id)THEN
SELECT amount into @amount from expensepayment where id=p_id;
UPDATE expensebill eb set eb.paid=eb.paid-@amount where eb.id=p_invoice;
UPDATE expensepayment ep set ep.receiptmethod=p_method,ep.amount=p_amount,ep.date=p_date,ep.remark=p_remark where id=p_id;
UPDATE expensebill eb set eb.paid=eb.paid+p_amount where eb.id=p_invoice;
SELECT 'success|Si sax ah ayaa loo bedelay';
ELSE
INSERT INTO `expensepayment`( `invoice`, `receiptmethod`, `amount`, `remark`, `date`, `user_id`) VALUES (p_invoice,p_method,p_amount,p_remark,p_date,p_user);
UPDATE expensebill eb set eb.paid=eb.paid+p_amount where eb.id=p_invoice;
select 'success|Si sax ah ayaa loo xareeyey';
END if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPExpenseType` (IN `p_id` INT, IN `p_school` INT, IN `p_name` VARCHAR(100), IN `p_remark` TEXT)   BEGIN
if EXISTS(SELECT * from expensetype i where i.id=p_id)THEN
UPDATE expensetype i set i.Name=p_name,i.remark=	
p_remark where i.id=p_id;
SELECT 'success|Si sax ah ayaa loo bedelay';
ELSEIF EXISTS(SELECT * from expensetype i where i.Name=p_name)THEN
select 'warning|magacaan hore ayuu u jiray';
ELSE
INSERT into expensetype VALUES(NULL,p_school,p_name,p_remark);
select 'success|Si sax ah ayaa loo xareeyey';
END if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPFindResultview` (IN `p_studentid` VARCHAR(250), IN `p_accy` VARCHAR(250))   BEGIN

SELECT id into p_accy from academicyear ac where ac.Name=p_accy;
CREATE TEMPORARY TABLE fexamresult as

SELECT c.id from students ss 
 INNER join class_sections cs on cs.id=ss.class_id
     INNER join classes c on c.id=cs.class_id
where ss.student_id= p_studentid and ss.current_academic=p_accy
UNION 
SELECT c.id from student_promotion sp 
 INNER join class_sections css on css.id=sp.from_class
     INNER join classes c on c.id=css.class_id
where sp.student_id=p_studentid and sp.from_academic=p_accy;

SELECT id into @class from fexamresult LIMIT 1;

SELECT ec.id,ecr.Name FROM exam_result_class ec 
INNER JOIN exam_create_result ecr on ec.exam_result_info=ecr.id where ec.academic_year=p_accy and ec.class=@class ORDER by ec.id asc;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPGuardian` (IN `p_user` INT)   BEGIN
SELECT  id, concat(id,' ',sp.sponsor_name,' (',sp.mobile,')') as name FROM sponsors sp where sp.id IN (SELECT s.sponsor_id   FROM students s JOIN class_sections cs on s.class_id = cs.id join classes c on cs.class_id = c.id  JOIN school_levels sl on c.school_level_id = sl.id   INNER join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user

) OR sp.id  NOT IN (SELECT s.sponsor_id   FROM students s JOIN class_sections cs on s.class_id = cs.id join classes c on cs.class_id = c.id  JOIN school_levels sl on c.school_level_id = sl.id   INNER join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user

);
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPGuardianInvoice` (IN `p_sponsor` INT(100), IN `p_accy` INT, IN `p_month` TEXT, IN `p_user_id` INT)  NO SQL BEGIN





SET @mon = p_month;  

SELECT cr.student_id AS Student_id,
       s.student_name AS student_name,
       CONCAT(sc.school_name, ' ', ls.name) AS school_name,
       CONCAT(c.class_name, '/', sec.section) AS class_name,
       GROUP_CONCAT(mn.months ORDER BY mn.id SEPARATOR ',') AS month,
       SUM(cr.amount) AS amount,
       cr.boono,
       cr.tran_date,
       a.Name AS academicyear,
       rm.method AS receipt_method,
       cr.usd_no,
       cr.remark AS note,
       ft.fee_name
FROM cash_receipt cr
JOIN students s ON s.student_id = cr.student_id
JOIN class_sections cs ON cs.id = cr.class_id
JOIN classes c ON c.id = cs.class_id
JOIN sections sec ON sec.id = cs.section_id
JOIN school_levels sl ON sl.id = c.school_level_id
JOIN levels ls ON ls.id = sl.level_id
JOIN schools sc ON sc.id = sl.school_id
JOIN fee_type ft ON cr.fee_type = ft.id
JOIN academicyear a ON a.id = cr.academic_year
JOIN receipt_method rm ON cr.receipt_by = rm.id
JOIN sponsors sp ON sp.id = s.sponsor_id
JOIN months mn ON cr.month = mn.id
INNER JOIN level_permission lp ON lp.level_id = sl.id AND lp.user_id = p_user_id
WHERE sp.id = p_sponsor
  AND cr.fee_type IN (7, 8)
  AND FIND_IN_SET(cr.month, @mon)  
  AND cr.academic_year = p_Accy
  AND cr.status = 1
GROUP BY cr.boono, cr.student_id, cr.fee_type;





END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPIncomeReciept` (IN `p_id` INT, IN `p_school` INT, IN `p_type` INT, IN `p_name` VARCHAR(100), IN `p_amount` DECIMAL(18,2), IN `p_recieptby` INT, IN `p_Invoice` VARCHAR(100), IN `p_date` DATE, IN `p_remark` TEXT, IN `p_user` INT)   BEGIN
if EXISTS(SELECT * from incomereciept ir where ir.id=p_id
      
         )THEN
UPDATE incomereciept ir 

set ir.school=p_school,ir.type=p_type,ir.name=p_name,ir.amount=p_amount,ir.recieptby=p_recieptby,ir.remark=p_remark,ir.Invoice=p_Invoice,ir.date=p_date where ir.id=p_id;
SELECT 'success|Si sax ah ayaA loo beddelay';
ELSE
INSERT INTO `incomereciept`(`school`,`type`, `name`, `amount`, `recieptby`, `remark`, `Invoice`, `date`, `user`) VALUES
(p_school,p_type, p_name, p_amount, p_recieptby, p_remark, p_Invoice, p_date, p_user);
select 'success|Waa la diiwaangaliyey';
END IF;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPIncomeType` (IN `p_id` INT, IN `p_name` VARCHAR(100), IN `p_remark` TEXT)   BEGIN
if EXISTS(SELECT * from incometype i where i.id=p_id)THEN
UPDATE incometype i set i.Name=p_name,i.remark=	
p_remark where i.id=p_id;
SELECT 'success|Si sax ah ayaa loo bedelay';
ELSEIF EXISTS(SELECT * from incometype i where i.Name=p_name)THEN
select 'warning|magacaan hore ayuu u jiray';
ELSE
INSERT into incometype VALUES(NULL,p_name,p_remark);
select 'success|Si sax ah ayaa loo xareeyey';
END if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPRemoveToBuss` (IN `p_id` INT, IN `p_month` INT, IN `p_date` DATE, IN `p_reason` TEXT, IN `p_user` INT)   BEGIN
UPDATE studentbus sb set sb.EndDate=p_date,sb.to_month=p_month , sb.reason=p_reason,sb.status=1,sb.end_user=p_user where sb.id=p_id;
select'success| Data Removed successfully ' as msg;
end$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPRemoveToFood` (IN `p_id` INT, IN `p_month` INT, IN `p_date` DATE, IN `p_reason` TEXT, IN `p_user` INT)   BEGIN
UPDATE studentfood sb set sb.EndDate=p_date,sb.to_month=p_month , sb.reason=p_reason,sb.status=1,sb.end_user=p_user where sb.id=p_id;
select'success| Data Removed successfully ' as msg;
end$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `spsearchsingle` (IN `p_other` VARCHAR(100), IN `p_user_id` INT)  NO SQL BEGIN

SELECT @rownum:=@rownum+1 as id,s.student_id,s.id as 'student_o_id',s.student_name 'Magaca Ardayga',s.Gender 'Jinsiga',concat(c.class_name,' ',se.section) as "Fasalka",s.phone 'Phone',sp.sponsor_name as 'Masuulka',sp.mobile as 'Masuulka No',s.dob ,s.pob,ds.Districts as district,s.address, 	s.mother_name 'Hooyada',ss.status_name 'Status', (SELECT COUNT(*) from student_promotion sp where sp.student_id=s.student_id and sp.from_academic=s.current_academic) as active,

CONCAT('<button type="button" class="btn btn-secondary btn-xs collect" data-student_id="',s.student_id,'" data-student_o_id="',s.id,'"  data-acedemic_year="',s.current_academic,'" >Collect Fees</button>') as 'collect' FROM students s JOIN class_sections cs on s.class_id = cs.id join classes c on cs.class_id = c.id join sections se on cs.section_id = se.id JOIN school_levels sl on c.school_level_id = sl.id JOIN schools sc on sl.school_id = sc.id 
 INNER join level_permission lp on lp.level_id=sl.id and lp.user_id=p_user_id
 JOIN levels l on sl.level_id = l.id
left JOIN districts ds on ds.id=s.district
LEFT join status ss on ss.id=s.status 
inner join sponsors sp on sp.id=s.sponsor_id, (SELECT @rownum:=0) r where (s.student_id like p_other or s.student_name like concat('%',p_other,'%'))


ORDER by s.id ASC ;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPSingleStudentExamSummary` (IN `p_result` INT, IN `p_student` VARCHAR(1000))   BEGIN

DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;

DECLARE n1 INT DEFAULT 0;
DECLARE i1 INT DEFAULT 0;
DEClare pivot_subject LONGTEXT;
SET SESSION group_concat_max_len = 9999999999999999999;
SELECT GROUP_CONCAT(er.subjects SEPARATOR',') into @subject FROM exam_result_class_subject er where er.exam_result_class_info=p_result;

SELECT e.class,e.academic_year,e.exam_result_info,e.pass_mark into @class,@academic_year,@exam_result_info,@pass_mark FROM exam_result_class e WHERE id=p_result;
create table s( txt text );
insert into s values(@subject);

drop temporary table if exists temp_subjects;
create temporary table temp_subjects( val char(255) );
set @sql5 = concat("insert into temp_subjects (val) values ('", replace(( select group_concat(distinct txt) as data from s), ",", "'),('"),"');");
prepare stmt3 from @sql5;
execute stmt3;
CREATE TEMPORARY TABLE ttmp2
(exam int);
CREATE TEMPORARY TABLE ttmp1
(exam int,id int, result int,percentage decimal(18,2));

SELECT LENGTH(GROUP_CONCAT(Exams SEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(Exams SEPARATOR ','), ',', '')) + 1 into @id FROM `exam_result_calculation` WHERE result_id=@exam_result_info GROUP by result_id ;
set i=1;
WHILE i<=@id DO

insert into ttmp2
SELECT if(i>@id,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(Exams SEPARATOR ','), ',', i), ',', -1))  FROM `exam_result_calculation` WHERE result_id=@exam_result_info GROUP by result_id;
 SET i = i + 1;
 END WHILE;
 insert into ttmp1
 SELECT t1.exam,(SELECT er.id from exam_result_calculation er where 
 FIND_IN_SET(t1.exam, er.Exams) and er.result_id=@exam_result_info  ) as id,(SELECT er.result_id from exam_result_calculation er where 
 FIND_IN_SET(t1.exam, er.Exams) and er.result_id=@exam_result_info ) as result_id,(SELECT er.percentage from exam_result_calculation er where 
 FIND_IN_SET(t1.exam, er.Exams) and er.result_id=@exam_result_info ) as percentage from ttmp2 t1; 
CREATE TEMPORARY TABLE IF NOT EXISTS tmp AS (
     
     SELECT * from ttmp1 where result=@exam_result_info
     );

create TEMPORARY TABLE subject_class(
    class int,
    subject int
    );
    insert into subject_class
    SELECT cs.id 'class',s.id 'subject' FROM subject_group_class_sections clss 
    INNER join classes cs on cs.id=clss.class_section_id 
    INNER join subject_group_subjects sgs on sgs.subject_group_id=clss.subject_group_id 
    INNER join subjects s on s.id=sgs.subject_id
    INNER join subject_groups sg on sg.id=sgs.subject_group_id
    where cs.id=@class and  sg.status=0 and sg.acedemic_year=@academic_year and s.id in (SELECT val from temp_subjects) ;

CREATE TEMPORARY TABLE subject_exam
(subject int,class int,exam int );
SELECT COUNT(*) FROM subject_class INTO n1;
SELECT COUNT(*) FROM tmp INTO n;

SET i=0;
WHILE i<n DO 
SET i1=0;
WHILE i1<n1 DO 
INSERT INTO subject_exam
SELECT subject,class,(SELECT exam FROM tmp LIMIT i,1) 'exam' FROM subject_class LIMIT i1,1;


  SET i1 = i1 + 1;
END WHILE;
  SET i = i + 1;
END WHILE; 
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS (
SELECT * FROM( 
    SELECT s.student_id,s.student_name,c.id from students s 
     INNER join class_sections css on css.id=s.class_id
     INNER join classes c on c.id=css.class_id
     JOIN sections se ON css.section_id = se.id
    where css.class_id=@class and s.current_academic =@academic_year  
    UNION all select s.student_id,s.student_name,c.id from student_promotion sp 
    inner join students s on s.student_id=sp.student_id
      INNER join class_sections css on css.id=sp.from_class
     INNER join classes c on c.id=css.class_id
      JOIN sections se ON css.section_id = se.id
    where css.class_id=@class and sp.from_academic=@academic_year 
    
) as `Data` 
    );
 
    
CREATE TEMPORARY TABLE IF NOT EXISTS student_marks_step1 AS (
select s.id 'subject_id',ss.student_id,c.id 'class_id',et.id 'exam_id'
    ,ifnull((select ifnull(emk.marks,0) from exam_marks emk where emk.student_id=ss.student_id and emk.exam_id=ec.exam and emk.academicyear=@academic_year and emk.subject_id=ec.subject and emk.status=0 limit 1),0)'Marks' 
    from subject_exam ec 
    inner join exam_type et on et.id=ec.exam 
    INNER join classes c on c.id=ec.class 
     
    inner join 	subjects s on s.id=ec.subject  
    INNER join tmp_students ss on ss.id=c.id where c.id=@class);
   
     CREATE TEMPORARY TABLE IF NOT EXISTS student_marks_step2 AS (
    select sts.subject_id,s.subject_name,sts.student_id,t.id,sum(sts.Marks) 'marks',t.percentage from student_marks_step1 sts inner join subjects s on s.id=sts.subject_id INNER join tmp t on t.exam=sts.exam_id GROUP by student_id,subject_id,t.id
        );
  
 CREATE TEMPORARY TABLE IF NOT EXISTS student_marks_step3 AS (
 select sts.subject_name,sts.subject_id,sts.student_id,sum(if(sts.Marks!=0,(sts.percentage/100)*sts.Marks,sts.Marks)) 'Marks' from student_marks_step2 sts group by student_id,subject_id 
     );
     drop  table if exists table6;
CREATE  TABLE IF NOT EXISTS table6 AS (
     SELECT sms3.subject_name,sms3.subject_id,sms3.student_id,round(sms3.Marks,2)'Marks',round((SELECT sum(if(sm.Marks!=0,(sm.percentage/100)*sm.Marks,sm.Marks)) FROM student_marks_step2 sm where sm.student_id=sms3.student_id  group by sm.student_id),2)'Total' FROM student_marks_step3 sms3
     );
        CREATE TEMPORARY TABLE IF NOT EXISTS finalresultrank AS (
SELECT student_id,Total,    (SELECT COUNT(DISTINCT t2.Total) + 1
        FROM table6 t2
        WHERE t2.Total > t1.Total)  
            
          AS  `rank`  from table6 t1 GROUP by student_id order by Total DESC);

 

    
     set @No_subject=(SELECT COUNT(*) from subject_class);
     
  SELECT sum(eci.marks) into @sum_mark  from subject_exam se
  JOIN exam_class_info eci on  se.exam=eci.exam_type_id and eci.acedemic_year=@academic_year 
   join exam_class ec on ec.exam_info=eci.id and se.subject=ec.subject_id;
   
   


SET @Midterm = (
    SELECT MAX(eci.marks + 31)
    FROM exam_result_class ec 
    INNER JOIN exam_create_result ecr ON ec.exam_result_info = ecr.id
    INNER JOIN exam_result_calculation erc ON erc.result_id = ecr.id
    INNER JOIN exam_class_info eci ON eci.id = erc.result_id
    LIMIT 1
);

SET @OtherExam = (
    SELECT MAX(eci.marks)
    FROM exam_result_class ec 
    INNER JOIN exam_create_result ecr ON ec.exam_result_info = ecr.id
    INNER JOIN exam_result_calculation erc ON erc.result_id = ecr.id
    INNER JOIN exam_class_info eci ON eci.id = erc.result_id
    LIMIT 1
);



   
set @max_midterm_marks= @No_subject * @Midterm;
set @max_otherExam_marks= @No_subject * @OtherExam;


     
      CREATE TEMPORARY TABLE IF NOT EXISTS exam_mark_correction AS (
      SELECT SUM(eci.marks) as max_marks,SUM(eci.marks)/2 as min_marks,ec.subject_id  from subject_exam se JOIN exam_class_info eci on  se.exam=eci.exam_type_id and eci.acedemic_year=@academic_year AND eci.class_id=se.class join exam_class ec on ec.exam_info=eci.id and se.subject=ec.subject_id where eci.status=0 GROUP by eci.acedemic_year,eci.class_id,ec.subject_id
           );
  
          
      
  CREATE TEMPORARY TABLE IF NOT EXISTS tablepercentage AS (
       SELECT tl.subject_name,tl.Marks,tl.Total,CONCAT(ROUND ((CASE WHEN tl.Total > 108 THEN tl.Total / @max_midterm_marks ELSE tl.Total / @max_otherExam_marks END) * 100, 2),  '%') AS 'Percentage',fr.rank from  table6 tl INNER join exam_mark_correction em on em.subject_id=tl.subject_id JOIN finalresultrank fr on fr.student_id=tl.student_id where tl.student_id =p_student
         
         );
  SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT
         CONCAT('MAX(CASE WHEN subject_name = ''', TRIM(subject_name),
                ''' THEN Marks END) `', TRIM(subject_name), '`'))
  INTO @sql
  FROM tablepercentage;
  SET @sql = CONCAT('SELECT  ', @sql, ' ,Total,`rank`
                   FROM tablepercentage');
SET @createSQL = CONCAT('CREATE TEMPORARY TABLE sfinalresult AS ', @sql);

PREPARE stmt FROM @createSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
             SELECT * from sfinalresult;
    
    
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPSingleStudentExamSummary1` (IN `p_result` INT, IN `p_student` VARCHAR(1000))   BEGIN

DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;

DECLARE n1 INT DEFAULT 0;
DECLARE i1 INT DEFAULT 0;
DEClare pivot_subject LONGTEXT;
SET SESSION group_concat_max_len = 9999999999999999999;
SELECT GROUP_CONCAT(er.subjects SEPARATOR',') into @subject FROM exam_result_class_subject er where er.exam_result_class_info=p_result;

SELECT e.class,e.academic_year,e.exam_result_info,e.pass_mark into @class,@academic_year,@exam_result_info,@pass_mark FROM exam_result_class e WHERE id=p_result;
create table s( txt text );
insert into s values(@subject);

drop temporary table if exists temp_subjects;
create temporary table temp_subjects( val char(255) );
set @sql5 = concat("insert into temp_subjects (val) values ('", replace(( select group_concat(distinct txt) as data from s), ",", "'),('"),"');");
prepare stmt3 from @sql5;
execute stmt3;
CREATE TEMPORARY TABLE ttmp2
(exam int);
CREATE TEMPORARY TABLE ttmp1
(exam int,id int, result int,percentage decimal(18,2));

SELECT LENGTH(GROUP_CONCAT(Exams SEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(Exams SEPARATOR ','), ',', '')) + 1 into @id FROM `exam_result_calculation` WHERE result_id=@exam_result_info GROUP by result_id ;
set i=1;
WHILE i<=@id DO

insert into ttmp2
SELECT if(i>@id,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(Exams SEPARATOR ','), ',', i), ',', -1))  FROM `exam_result_calculation` WHERE result_id=@exam_result_info GROUP by result_id;
 SET i = i + 1;
 END WHILE;
 insert into ttmp1
 SELECT t1.exam,(SELECT er.id from exam_result_calculation er where 
 FIND_IN_SET(t1.exam, er.Exams) and er.result_id=@exam_result_info  ) as id,(SELECT er.result_id from exam_result_calculation er where 
 FIND_IN_SET(t1.exam, er.Exams) and er.result_id=@exam_result_info ) as result_id,(SELECT er.percentage from exam_result_calculation er where 
 FIND_IN_SET(t1.exam, er.Exams) and er.result_id=@exam_result_info ) as percentage from ttmp2 t1; 
CREATE TEMPORARY TABLE IF NOT EXISTS tmp AS (
     
     SELECT * from ttmp1 where result=@exam_result_info
     );
 
create TEMPORARY TABLE subject_class(
    class int,
    subject int
    );
    insert into subject_class
    SELECT cs.id 'class',s.id 'subject' FROM subject_group_class_sections clss 
    INNER join classes cs on cs.id=clss.class_section_id 
    INNER join subject_group_subjects sgs on sgs.subject_group_id=clss.subject_group_id 
    INNER join subjects s on s.id=sgs.subject_id
    INNER join subject_groups sg on sg.id=sgs.subject_group_id
    where cs.id=@class and  sg.status=0 and sg.acedemic_year=@academic_year and s.id in (SELECT val from temp_subjects) ;

CREATE TEMPORARY TABLE subject_exam
(subject int,class int,exam int );
SELECT COUNT(*) FROM subject_class INTO n1;
SELECT COUNT(*) FROM tmp INTO n;

SET i=0;
WHILE i<n DO 
SET i1=0;
WHILE i1<n1 DO 
INSERT INTO subject_exam
SELECT subject,class,(SELECT exam FROM tmp LIMIT i,1) 'exam' FROM subject_class LIMIT i1,1;


  SET i1 = i1 + 1;
END WHILE;
  SET i = i + 1;
END WHILE;
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS (
SELECT * FROM( 
    SELECT s.student_id,s.student_name,c.id from students s 
     INNER join class_sections css on css.id=s.class_id
     INNER join classes c on c.id=css.class_id
    where c.id=@class and s.current_academic =@academic_year 
    UNION all select s.student_id,s.student_name,c.id from student_promotion sp 
    inner join students s on s.student_id=sp.student_id
      INNER join class_sections css on css.id=sp.from_class
     INNER join classes c on c.id=css.class_id
    
    where c.id=@class and sp.from_academic=@academic_year 
    
) as `Data` 
    );
  
   
CREATE TEMPORARY TABLE IF NOT EXISTS student_marks_step1 AS (
select s.id 'subject_id',ss.student_id,c.id 'class_id',et.id 'exam_id'
    ,ifnull((select ifnull(emk.marks,0) from exam_marks emk where emk.student_id=ss.student_id and emk.exam_id=ec.exam and emk.academicyear=@academic_year and emk.subject_id=ec.subject and emk.status=0 limit 1),0)'Marks' 
    from subject_exam ec 
    inner join exam_type et on et.id=ec.exam 
    INNER join classes c on c.id=ec.class 
     
    inner join 	subjects s on s.id=ec.subject  
    INNER join tmp_students ss on ss.id=c.id where c.id=@class );
     CREATE TEMPORARY TABLE IF NOT EXISTS student_marks_step2 AS (
    select sts.subject_id,s.subject_name,sts.student_id,t.id,sum(sts.Marks) 'marks',t.percentage from student_marks_step1 sts inner join subjects s on s.id=sts.subject_id INNER join tmp t on t.exam=sts.exam_id GROUP by student_id,subject_id,t.id
        );

 CREATE TEMPORARY TABLE IF NOT EXISTS student_marks_step3 AS (
 select sts.subject_name,sts.subject_id,sts.student_id,sum(if(sts.Marks!=0,(sts.percentage/100)*sts.Marks,sts.Marks)) 'Marks' from student_marks_step2 sts group by student_id,subject_id 
     );
     drop  table if exists table6;
CREATE  TABLE IF NOT EXISTS table6 AS (
     SELECT sms3.subject_name,sms3.subject_id,sms3.student_id,round(sms3.Marks,2)'Marks',round((SELECT sum(if(sm.Marks!=0,(sm.percentage/100)*sm.Marks,sm.Marks)) FROM student_marks_step2 sm where sm.student_id=sms3.student_id  group by sm.student_id),2)'Total' FROM student_marks_step3 sms3
     );
        CREATE TEMPORARY TABLE IF NOT EXISTS finalresultrank AS (
SELECT student_id,Total,    (SELECT COUNT(DISTINCT t2.Total) + 1
        FROM table6 t2
        WHERE t2.Total > t1.Total)  
            
          AS  `rank`  from table6 t1 GROUP by student_id order by Total DESC);

 
     
     
     set @No_subject=(SELECT COUNT(*) from subject_class);
     SELECT sum(eci.marks) into @sum_mark  from subject_exam se
     JOIN exam_class_info eci on  se.exam=eci.exam_type_id and eci.acedemic_year=@academic_year 
     join exam_class ec on ec.exam_info=eci.id and se.subject=ec.subject_id 
    ;

     
      CREATE TEMPORARY TABLE IF NOT EXISTS exam_mark_correction AS (
      SELECT SUM(eci.marks) as max_marks,SUM(eci.marks)/2 as min_marks,ec.subject_id  from subject_exam se JOIN exam_class_info eci on  se.exam=eci.exam_type_id and eci.acedemic_year=@academic_year AND eci.class_id=se.class join exam_class ec on ec.exam_info=eci.id and se.subject=ec.subject_id where eci.status=0 GROUP by eci.acedemic_year,eci.class_id,ec.subject_id
           );
  
  set @max_marks_per_subject = 100;
   set @maxPossibleMarks = @max_marks_per_subject * @No_subject;
   
  CREATE TEMPORARY TABLE IF NOT EXISTS tablepercentage AS (
  SELECT tl.subject_name,tl.Marks,tl.Total,round((tl.Total/@maxPossibleMarks)*100,2) 'Percentage',fr.rank from  table6 tl INNER join exam_mark_correction em on em.subject_id=tl.subject_id JOIN finalresultrank fr on fr.student_id=tl.student_id where tl.student_id =p_student
       );
 



  SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT
         CONCAT('MAX(CASE WHEN subject_name = ''', TRIM(subject_name),
                ''' THEN Marks END) `', TRIM(subject_name), '`'))
  INTO @sql
  FROM tablepercentage;
  SET @sql = CONCAT('SELECT  ', @sql, ' ,Total,Percentage,`rank`
                   FROM tablepercentage');
SET @createSQL = CONCAT('CREATE TEMPORARY TABLE sfinalresult AS ', @sql);

PREPARE stmt FROM @createSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
             SELECT * from sfinalresult;
    
    
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPSingleStudentExamSummary3` (IN `p_result` INT, IN `p_student` VARCHAR(1000), IN `p_class` VARCHAR(100))   BEGIN

DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;

DECLARE n1 INT DEFAULT 0;
DECLARE i1 INT DEFAULT 0;
DEClare pivot_subject LONGTEXT;
SET SESSION group_concat_max_len = 9999999999999999999;
SELECT GROUP_CONCAT(er.subjects SEPARATOR',') into @subject FROM exam_result_class_subject er where er.exam_result_class_info=p_result;

SELECT e.class,e.academic_year,e.exam_result_info,e.pass_mark into @class,@academic_year,@exam_result_info,@pass_mark FROM exam_result_class e WHERE id=p_result;
create table s( txt text );
insert into s values(@subject);

drop temporary table if exists temp_subjects;
create temporary table temp_subjects( val char(255) );
set @sql5 = concat("insert into temp_subjects (val) values ('", replace(( select group_concat(distinct txt) as data from s), ",", "'),('"),"');");
prepare stmt3 from @sql5;
execute stmt3;
CREATE TEMPORARY TABLE ttmp2
(exam int);
CREATE TEMPORARY TABLE ttmp1
(exam int,id int, result int,percentage decimal(18,2));

SELECT LENGTH(GROUP_CONCAT(Exams SEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(Exams SEPARATOR ','), ',', '')) + 1 into @id FROM `exam_result_calculation` WHERE result_id=@exam_result_info GROUP by result_id ;
set i=1;
WHILE i<=@id DO

insert into ttmp2
SELECT if(i>@id,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(Exams SEPARATOR ','), ',', i), ',', -1))  FROM `exam_result_calculation` WHERE result_id=@exam_result_info GROUP by result_id;
 SET i = i + 1;
 END WHILE;
 insert into ttmp1
 SELECT t1.exam,(SELECT er.id from exam_result_calculation er where 
 FIND_IN_SET(t1.exam, er.Exams) and er.result_id=@exam_result_info  ) as id,(SELECT er.result_id from exam_result_calculation er where 
 FIND_IN_SET(t1.exam, er.Exams) and er.result_id=@exam_result_info ) as result_id,(SELECT er.percentage from exam_result_calculation er where 
 FIND_IN_SET(t1.exam, er.Exams) and er.result_id=@exam_result_info ) as percentage from ttmp2 t1; 
CREATE TEMPORARY TABLE IF NOT EXISTS tmp AS (
     
     SELECT * from ttmp1 where result=@exam_result_info
     );
 
create TEMPORARY TABLE subject_class(
    class int,
    subject int
    );
    insert into subject_class
    SELECT cs.id 'class',s.id 'subject' FROM subject_group_class_sections clss 
    INNER join classes cs on cs.id=clss.class_section_id 
    INNER join subject_group_subjects sgs on sgs.subject_group_id=clss.subject_group_id 
    INNER join subjects s on s.id=sgs.subject_id
    INNER join subject_groups sg on sg.id=sgs.subject_group_id
    where cs.id=@class and  sg.status=0 and sg.acedemic_year=@academic_year and s.id in (SELECT val from temp_subjects) ;

CREATE TEMPORARY TABLE subject_exam
(subject int,class int,exam int );
SELECT COUNT(*) FROM subject_class INTO n1;
SELECT COUNT(*) FROM tmp INTO n;

SET i=0;
WHILE i<n DO 
SET i1=0;
WHILE i1<n1 DO 
INSERT INTO subject_exam
SELECT subject,class,(SELECT exam FROM tmp LIMIT i,1) 'exam' FROM subject_class LIMIT i1,1;


  SET i1 = i1 + 1;
END WHILE;
  SET i = i + 1;
END WHILE;
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS (
SELECT * FROM( 
   SELECT s.student_id,s.student_name,c.id from students s 
     INNER join class_sections css on css.id=s.class_id
     INNER join classes c on c.id=css.class_id
     JOIN sections se ON css.section_id = se.id
    where concat( c.class_name,'-',se.section)=p_class and s.current_academic =@academic_year  
    UNION all select s.student_id,s.student_name,c.id from student_promotion sp 
    inner join students s on s.student_id=sp.student_id
      INNER join class_sections css on css.id=sp.from_class
     INNER join classes c on c.id=css.class_id
      JOIN sections se ON css.section_id = se.id
    where concat( c.class_name,'-',se.section)=p_class and sp.from_academic=@academic_year  
    
) as `Data` 
    );
  
   
CREATE TEMPORARY TABLE IF NOT EXISTS student_marks_step1 AS (
select s.id 'subject_id',ss.student_id,c.id 'class_id',et.id 'exam_id'
    ,ifnull((select ifnull(emk.marks,0) from exam_marks emk where emk.student_id=ss.student_id and emk.exam_id=ec.exam and emk.academicyear=@academic_year and emk.subject_id=ec.subject and emk.status=0 limit 1),0)'Marks' 
    from subject_exam ec 
    inner join exam_type et on et.id=ec.exam 
    INNER join classes c on c.id=ec.class 
     
    inner join 	subjects s on s.id=ec.subject  
    INNER join tmp_students ss on ss.id=c.id where c.id=@class );
     CREATE TEMPORARY TABLE IF NOT EXISTS student_marks_step2 AS (
    select sts.subject_id,s.subject_name,sts.student_id,t.id,sum(sts.Marks) 'marks',t.percentage from student_marks_step1 sts inner join subjects s on s.id=sts.subject_id INNER join tmp t on t.exam=sts.exam_id GROUP by student_id,subject_id,t.id
        );

 CREATE TEMPORARY TABLE IF NOT EXISTS student_marks_step3 AS (
 select sts.subject_name,sts.subject_id,sts.student_id,sum(if(sts.Marks!=0,(sts.percentage/100)*sts.Marks,sts.Marks)) 'Marks' from student_marks_step2 sts group by student_id,subject_id 
     );
     drop  table if exists table6;
CREATE  TABLE IF NOT EXISTS table6 AS (
     SELECT sms3.subject_name,sms3.subject_id,sms3.student_id,round(sms3.Marks,2)'Marks',round((SELECT sum(if(sm.Marks!=0,(sm.percentage/100)*sm.Marks,sm.Marks)) FROM student_marks_step2 sm where sm.student_id=sms3.student_id  group by sm.student_id),2)'Total' FROM student_marks_step3 sms3
     );
        CREATE TEMPORARY TABLE IF NOT EXISTS finalresultrank AS (
SELECT student_id,Total,    (SELECT COUNT(DISTINCT t2.Total) + 1
        FROM table6 t2
        WHERE t2.Total > t1.Total)  
            
          AS  `rank`  from table6 t1 GROUP by student_id order by Total DESC);

 
     
    
     set @No_subject=(SELECT COUNT(*) from subject_class);
     
  SELECT sum(eci.marks) into @sum_mark  from subject_exam se
  JOIN exam_class_info eci on  se.exam=eci.exam_type_id and eci.acedemic_year=@academic_year 
   join exam_class ec on ec.exam_info=eci.id and se.subject=ec.subject_id;
   
   


SET @Midterm = (
    SELECT MAX(eci.marks + 31)
    FROM exam_result_class ec 
    INNER JOIN exam_create_result ecr ON ec.exam_result_info = ecr.id
    INNER JOIN exam_result_calculation erc ON erc.result_id = ecr.id
    INNER JOIN exam_class_info eci ON eci.id = erc.result_id
    LIMIT 1
);

SET @OtherExam = (
    SELECT MAX(eci.marks)
    FROM exam_result_class ec 
    INNER JOIN exam_create_result ecr ON ec.exam_result_info = ecr.id
    INNER JOIN exam_result_calculation erc ON erc.result_id = ecr.id
    INNER JOIN exam_class_info eci ON eci.id = erc.result_id
    LIMIT 1
);



   
set @max_midterm_marks= @No_subject * @Midterm;
set @max_otherExam_marks= @No_subject * @OtherExam;


     
      CREATE TEMPORARY TABLE IF NOT EXISTS exam_mark_correction AS (
      SELECT SUM(eci.marks) as max_marks,SUM(eci.marks)/2 as min_marks,ec.subject_id  from subject_exam se JOIN exam_class_info eci on  se.exam=eci.exam_type_id and eci.acedemic_year=@academic_year AND eci.class_id=se.class join exam_class ec on ec.exam_info=eci.id and se.subject=ec.subject_id where eci.status=0 GROUP by eci.acedemic_year,eci.class_id,ec.subject_id
           );
  
          
      
  CREATE TEMPORARY TABLE IF NOT EXISTS tablepercentage AS (
       SELECT tl.subject_name,tl.Marks,tl.Total,CONCAT(ROUND ((CASE WHEN tl.Total > 108 THEN tl.Total / @max_midterm_marks ELSE tl.Total / @max_otherExam_marks END) * 100, 2),  '%') AS 'Percentage',fr.rank from  table6 tl INNER join exam_mark_correction em on em.subject_id=tl.subject_id JOIN finalresultrank fr on fr.student_id=tl.student_id where tl.student_id =p_student       
         );
         
  SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT
         CONCAT('MAX(CASE WHEN subject_name = ''', TRIM(subject_name),
                ''' THEN Marks END) `', TRIM(subject_name), '`'))
  INTO @sql
  FROM tablepercentage;
  SET @sql = CONCAT('SELECT  ', @sql, ' ,Total,Percentage,`rank`
                   FROM tablepercentage');
SET @createSQL = CONCAT('CREATE TEMPORARY TABLE sfinalresult AS ', @sql);

PREPARE stmt FROM @createSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
             SELECT * from sfinalresult;
    
    
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SPStudentTransfer` (IN `p_student_id` TEXT, IN `p_from_class` INT, IN `p_to_class` INT, IN `p_user_id` INT)   BEGIN


create table if NOT exists s( txt text );
insert into s values(p_student_id);

create  table temp_student( val char(255) );
set @sql2 = concat("insert into temp_student (val) values ('", replace(( select group_concat(distinct txt) as data from s), ",", "'),('"),"');");
prepare stmt2 from @sql2;
execute stmt2;

START TRANSACTION;
SELECT a.id into @current FROM academicyear A WHERE  A.is_active='yes';

UPDATE cash_receipt cr set cr.class_id=p_to_class where cr.student_id in (select val from temp_student) and cr.class_id=p_from_class and cr.academic_year=@current; 

update  students set class_id=p_to_class where student_id in (select val from temp_student) ;

UPDATE student_promotion sp set sp.to_class=p_to_class where sp.student_id in (SELECT val from temp_student) and sp.from_academic=@current    and to_class=p_from_class ;

UPDATE exam_marks exmk set exmk.class_id=p_to_class where exmk.student_id in (SELECT val from temp_student) and exmk.academicyear=@current and exmk.class_id=p_from_class;




SELECT 'success| record saved successfully';
COMMIT;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_AddNewStudent` (IN `p_id` INT, IN `p_StudentName` VARCHAR(50), IN `p_Gender` VARCHAR(50), IN `p_DOB` VARCHAR(50), IN `p_POB` VARCHAR(50), IN `p_mothername` VARCHAR(50), IN `p_SponsoredBy` VARCHAR(50), IN `p_Address` VARCHAR(50), IN `p_Phone` VARCHAR(50), IN `p_Fee` FLOAT, IN `p_Fee_Status` VARCHAR(50), IN `p_ContactName` VARCHAR(50), IN `p_ContactPhone` VARCHAR(50), IN `p_TransferedAcademicYear` VARCHAR(50), IN `p_RegAcademicYear` VARCHAR(50), IN `p_SchoolName` VARCHAR(50), IN `p_SchoolAddress` VARCHAR(50), IN `p_Ambarella` VARCHAR(50), IN `p_CompletedClass` VARCHAR(50), IN `p_TypeOfDeclaration` VARCHAR(50), IN `p_DateOfIssue` VARCHAR(50), IN `p_ClassToSit` VARCHAR(50), IN `p_ClassType` VARCHAR(50), IN `RegistrationType` VARCHAR(50), IN `P_Photo` VARCHAR(50), IN `p_ContactAddress` VARCHAR(50))  NO SQL BEGIN
SELECT s.Number+1 into @student_id FROM setupid s where s.Name = "Students";
if NOT EXISTS(SELECT id FROM `students` WHERE `id`=p_id) THEN
BEGIN

INSERT INTO students( StudentID,StudentName,
 Gender, DOB, POB, mothername, SponsoredBy, 
Address, Phone, Fee, Fee_Status, 
                       ContactName, ContactAddress, ContactPhone,
                        TransferedAcademicYear, RegAcademicYear, SchoolName, 
                        SchoolAddress, Ambarella, CompletedClass,
                        TypeOfDeclaration,
                         DateOfIssue, class_id, ClassType, RegType,
                           Photo) VALUES (

                           @student_id,p_StudentName,
 p_Gender, p_DOB,p_POB, p_mothername, p_SponsoredBy, 
p_Address, p_Phone, p_Fee, p_Fee_Status, 
                      p_ContactName, p_ContactAddress, p_ContactPhone,
                       p_TransferedAcademicYear, p_RegAcademicYear, p_SchoolName, 
                       p_SchoolAddress, p_Ambarella, p_CompletedClass,
                       p_TypeOfDeclaration,
                        p_DateOfIssue,p_ClassToSit, p_ClassType,RegistrationType,
                           p_Photo);
                           UPDATE `setupid` SET `Number`=Number+1 WHERE `Name`='Students';
select 'inserted success';
END;
ELSE 
BEGIN
UPDATE students SET
                        StudentName=p_StudentName,
                         Gender= p_Gender,
                          DOB =p_DOB,
                         POB =p_POB, 
                        mothername= p_mothername,
                         SponsoredBy=   p_SponsoredBy, 
                           Address= p_Address, 
                           Phone= p_Phone,
                            Fee=p_Fee,
                          Fee_Status=  p_Fee_Status, 
                          ContactName=  p_ContactName,
                           ContactAddress= p_ContactAddress,
                            ContactPhone=p_ContactPhone,
                     TransferedAcademicYear=  p_TransferedAcademicYear, 
                           RegAcademicYear= p_RegAcademicYear, 
                           SchoolName= p_SchoolName,
                             SchoolAddress= p_SchoolAddress, 
                             Ambarella= p_Ambarella, 
                             CompletedClass= p_CompletedClass,
                             TypeOfDeclaration= p_TypeOfDeclaration,
                                DateOfIssue=p_DateOfIssue,
                               class_id=p_ClassToSit, 
                                ClassType=p_ClassType,
                                RegType=RegistrationType,
                                Photo=p_Photo
                           where id=p_id;
                           SELECT 'success';
END;
END if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_addprofile` (IN `p_name` VARCHAR(50), IN `p_logo` VARCHAR(100), IN `p_tel1` VARCHAR(50), IN `p_tel2` VARCHAR(50), IN `p_tel3` VARCHAR(50), IN `p_tel4` VARCHAR(50), IN `p_user_id` INT)   BEGIN
IF EXISTS(SELECT * FROM foundation_profile WHERE id = p_id) THEN
UPDATE foundation_profile set name = p_name,logo = p_logo,tel1 = p_tel1,tel2= p_tel2,tel3 = p_tel3,tel4 = p_tel4,updated_user = p_user_id;
SELECT 'success| Company Profile Updated';
ELSEIF EXISTS(SELECT * FROM foundation_profile WHERE name = p_name) THEN 
SELECT 'warning| Company Profile already exist';
ELSE
INSERT INTO `foundation_profile`(`name`, `Logo`, `tel1`, `tel2`, `tel3`, `tel4`, `user_id`) VALUES (p_name, p_logo,p_tel1,p_tel2,p_tel3,p_tel4,p_user_id);
SELECT 'success| Company Profile registered';
END IF;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_addscholarship` (IN `p_student_id` VARCHAR(100), IN `p_type` VARCHAR(100), IN `p_percentage` DECIMAL(18,2), IN `p_user` INT)   BEGIN

if not EXISTS(SELECT * from scholarship sc where sc.student_id=p_student_id and sc.type=p_type and sc.status=1)THEN
INSERT INTO `scholarship`(student_id, type, amount, date, user) 
VALUES (p_student_id,p_type,p_percentage/100,CURRENT_DATE(),p_user);
select 'success|Si sax ah ayaa loo xareeyey';
ELSE
select 'warning|Ardaygaan hore ayuu u jiray';
END if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `SP_AddStdBehavior` (IN `p_student_ids` VARCHAR(1000), IN `p_type` INT, IN `p_subject` VARCHAR(100), IN `p_reason` LONGTEXT, IN `p_date` DATE, IN `p_user_id` INT, IN `p_company_id` INT)   BEGIN
    
    DECLARE student_id_val VARCHAR(255);
    DECLARE comma_position INT;

    
    WHILE LENGTH(p_student_ids) > 0 DO
        SET comma_position = INSTR(p_student_ids, ',');
        IF comma_position = 0 THEN
            SET student_id_val = p_student_ids;
            SET p_student_ids = '';
        ELSE
            SET student_id_val = SUBSTRING(p_student_ids, 1, comma_position - 1);
            SET p_student_ids = SUBSTRING(p_student_ids, comma_position + 1);
        END IF;
SELECT max(id) into @accy FROM academicyear;
        
        INSERT INTO student_behavior (student_id, class_id, type, subject, reason, `date`, user_id, company_id, accy)
        SELECT student_id_val, s.class_id, p_type, p_subject, p_reason, p_date, p_user_id, p_company_id, @accy
        FROM students s
        WHERE s.student_id = student_id_val;
        
    END WHILE;

    
    SELECT 'success| Std. Behavior registered successfully';

    
    COMMIT;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_addstudent` (IN `_id` INT, IN `_stname` VARCHAR(50), IN `_sdob` DATE, IN `_spob` VARCHAR(50), IN `_StGender` VARCHAR(50), IN `_address` VARCHAR(50), IN `_Phone` VARCHAR(50), IN `_billtype` VARCHAR(50), IN `_Email` VARCHAR(50), IN `_faculties` VARCHAR(50), IN `_depts` VARCHAR(50), IN `_batch` VARCHAR(50), IN `_Semester` VARCHAR(50), IN `_Status` VARCHAR(50), IN `_ParentName1` VARCHAR(50), IN `_StGender1` VARCHAR(50), IN `_ParentAddress1` VARCHAR(50), IN `_Sponsor1` VARCHAR(50), IN `_ParentPhone1` VARCHAR(50), IN `_ParentPhone11` VARCHAR(50), IN `_ParentName2` VARCHAR(50), IN `_StGender2` VARCHAR(50), IN `_ParentAddress2` VARCHAR(50), IN `_Sponsor2` VARCHAR(50), IN `_ParentPhone2` VARCHAR(50), IN `_ParentPhone22` VARCHAR(50), IN `_SchoolName` VARCHAR(50), IN `_Ambarella` VARCHAR(50), IN `_RollNo` VARCHAR(50), IN `_academicyear` VARCHAR(50), IN `_CertificateNO` VARCHAR(50), IN `_AverageMarks` VARCHAR(50), IN `_Dateofissue` DATE, IN `_Grade` VARCHAR(50), IN `_photo` TEXT, IN `_mothername` VARCHAR(50), IN `p_bloodgroup` VARCHAR(50))  NO SQL BEGIN
declare _parentid int unsigned;

    if NOT EXISTS(SELECT id FROM `students` WHERE `id`=_id) THEN
BEGIN
SELECT max(id)+1 as id into _parentid FROM `parent`;
INSERT INTO `parent`(`id`, `Name`, `sex`, `address`, `sponsor`, `phone1`, `phone2`,Name2,sex2,address2,sponsor2,phone21,phone22)
 VALUES (_parentid,_ParentName1,_StGender1,_ParentAddress1,_Sponsor1,_ParentPhone1,_ParentPhone11,_ParentName2,_StGender2,_ParentAddress2,_Sponsor2,_ParentPhone2,_ParentPhone22);



  INSERT INTO `students`(`id`,StudentID, JU_ID,`StudentName`, `Gender`,
     `ParentID`, `DOB`, `POB`, `Address`, `Phone`, `Email`, 
                         `Status`, `AcademicYear`
    , `Semester`, `FacultyID`
    , `Dept_ID`, `BatchCode`
    , `BillType`,photo,Dateofissue,SchoolName,Ambarella,RollNo,CertificateNO,AverageMarks,Grade,mothername,Blood_Group) 
    VALUES(_id,concat(_batch,frm_show_JU_ID(_id,_faculties)),
                      frm_show_JU_ID(_id,_faculties) 
           ,_stname,_StGender,_parentid,_sdob,_spob,_address,
            _Phone,_Email,_Status,_academicyear,_Semester,
            _faculties,_depts,_batch,
                                   _billtype,_photo
                                   ,_Dateofissue,_SchoolName,_Ambarella,_RollNo,_CertificateNO,_AverageMarks,_Grade,_mothername,p_bloodgroup);
UPDATE `setupid` SET `Number`=Number+1 WHERE Name='students';
call  Sp_update_f_JU_ID(_faculties);
                                 select "Student Inserted!" as msg;
                                 
end;
   ELSE   BEGIN


                                 
end;
   end if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_addstudents_admission` (IN `_id` INT, IN `_stname` VARCHAR(50), IN `_sdob` DATE, IN `_spob` VARCHAR(50), IN `_StGender` VARCHAR(50), IN `_address` VARCHAR(50), IN `_Phone` VARCHAR(50), IN `_billtype` VARCHAR(50), IN `_Email` VARCHAR(50), IN `_faculties` VARCHAR(50), IN `_depts` VARCHAR(50), IN `_batch` VARCHAR(50), IN `_Semester` VARCHAR(50), IN `_Status` VARCHAR(50), IN `_ParentName1` VARCHAR(50), IN `_StGender1` VARCHAR(50), IN `_ParentAddress1` VARCHAR(50), IN `_Sponsor1` VARCHAR(50), IN `_ParentPhone1` VARCHAR(50), IN `_ParentPhone11` VARCHAR(50), IN `_ParentName2` VARCHAR(50), IN `_StGender2` VARCHAR(50), IN `_ParentAddress2` VARCHAR(50), IN `_Sponsor2` VARCHAR(50), IN `_ParentPhone2` VARCHAR(50), IN `_ParentPhone22` VARCHAR(50), IN `_SchoolName` VARCHAR(50), IN `_Ambarella` VARCHAR(50), IN `_RollNo` VARCHAR(50), IN `_academicyear` VARCHAR(50), IN `_CertificateNO` VARCHAR(50), IN `_AverageMarks` VARCHAR(50), IN `_Dateofissue` DATE, IN `_Grade` VARCHAR(50), IN `_photo` TEXT, IN `_mothername` VARCHAR(50), IN `p_bloodgroup` VARCHAR(50))  NO SQL BEGIN
declare _parentid int unsigned;

    if NOT EXISTS(SELECT id FROM `students` WHERE `id`=_id) THEN
BEGIN
SELECT max(id)+1 as id into _parentid FROM `parent`;
INSERT INTO `parent`(`id`, `Name`, `sex`, `address`, `sponsor`, `phone1`, `phone2`,Name2,sex2,address2,sponsor2,phone21,phone22)
 VALUES (_parentid,_ParentName1,_StGender1,_ParentAddress1,_Sponsor1,_ParentPhone1,_ParentPhone11,_ParentName2,_StGender2,_ParentAddress2,_Sponsor2,_ParentPhone2,_ParentPhone22);



  INSERT INTO `students`(`id`, `StudentName`, `Gender`,
     `ParentID`, `DOB`, `POB`, `Address`, `Phone`, `Email`, 
                         `Status`, `AcademicYear`
    , `Semester`, `FacultyID`
    , `Dept_ID`, `BatchCode`,StudentID
    , `BillType`,photo,Dateofissue,SchoolName,Ambarella,RollNo,CertificateNO,AverageMarks,Grade,mothername,Blood_Group) 
    VALUES(_id,_stname,_StGender,_parentid,_sdob,_spob,_address,
            _Phone,_Email,_Status,_academicyear,_Semester,
            _faculties,_depts,_batch,concat(_batch,'0',_id),
                                   _billtype,_photo
                                   ,_Dateofissue,_SchoolName,_Ambarella,_RollNo,_CertificateNO,_AverageMarks,_Grade,_mothername,p_bloodgroup);
UPDATE `setupid` SET `Number`=Number+1 WHERE Name='students';
                                   select "Student Inserted!" as msg;
                                 
end;
   ELSE   BEGIN
SELECT `ParentID` into _parentid FROM `students` WHERE id=_id;
update parent set  `Name`=_ParentName1, `sex`=_StGender1, `address`=_ParentAddress1,
 `sponsor`=_Sponsor1, `phone1`=_ParentPhone1, `phone2`=_ParentPhone11,
Name2=_ParentName2,sex2=_StGender2,address2=_ParentAddress2,
sponsor2=_Sponsor2,phone21=_ParentPhone2,phone22=_ParentPhone22 where id=_parentid;

update students set `StudentName`=_stname, `Gender`=_StGender,
    `DOB`=_sdob, `POB`=_spob, `Address`=_address, `Phone`=_Phone, `Email`=_Email, mothername=_mothername,
                          `AcademicYear`=_academicyear
    , `Semester`=_Semester, `FacultyID`=_faculties
    , `Dept_ID`=_depts, `BatchCode`=_batch
    , `BillType`=_billtype,Dateofissue=_Dateofissue,photo=_photo
    ,SchoolName=_SchoolName,Ambarella=_Ambarella,RollNo=_RollNo,
    CertificateNO=_CertificateNO,AverageMarks=_AverageMarks,
    Grade=_Grade,Blood_Group=p_bloodgroup
    where id=_id;


                                   select "Student Updated!" as msg;
                                 
end;
   end if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_addstudents_admission1` (IN `_id` INT, IN `_stname` VARCHAR(50), IN `_sdob` DATE, IN `_spob` VARCHAR(50), IN `_StGender` VARCHAR(50), IN `_address` VARCHAR(50), IN `_Phone` VARCHAR(50), IN `_billtype` VARCHAR(50), IN `_Email` VARCHAR(50), IN `_faculties` VARCHAR(50), IN `_depts` VARCHAR(50), IN `_batch` VARCHAR(50), IN `_Semester` VARCHAR(50), IN `_Status` VARCHAR(50), IN `_ParentName1` VARCHAR(50), IN `_StGender1` VARCHAR(50), IN `_ParentAddress1` VARCHAR(50), IN `_Sponsor1` VARCHAR(50), IN `_ParentPhone1` VARCHAR(50), IN `_ParentPhone11` VARCHAR(50), IN `_ParentName2` VARCHAR(50), IN `_StGender2` VARCHAR(50), IN `_ParentAddress2` VARCHAR(50), IN `_Sponsor2` VARCHAR(50), IN `_ParentPhone2` VARCHAR(50), IN `_ParentPhone22` VARCHAR(50), IN `_SchoolName` VARCHAR(50), IN `_Ambarella` VARCHAR(50), IN `_RollNo` VARCHAR(50), IN `_academicyear` VARCHAR(50), IN `_CertificateNO` VARCHAR(50), IN `_AverageMarks` VARCHAR(50), IN `_Dateofissue` DATE, IN `_Grade` VARCHAR(50), IN `_photo` TEXT, IN `_mothername` VARCHAR(50))  NO SQL BEGIN
declare _parentid int unsigned;

    if NOT EXISTS(SELECT id FROM `students` WHERE `id`=_id) THEN
BEGIN
SELECT max(id)+1 as id into _parentid FROM `parent`;
INSERT INTO `parent`(`id`, `Name`, `sex`, `address`, `sponsor`, `phone1`, `phone2`,Name2,sex2,address2,sponsor2,phone21,phone22)
 VALUES (_parentid,_ParentName1,_StGender1,_ParentAddress1,_Sponsor1,_ParentPhone1,_ParentPhone11,_ParentName2,_StGender2,_ParentAddress2,_Sponsor2,_ParentPhone2,_ParentPhone22);



  INSERT INTO `students`(`id`, `StudentName`, `Gender`,
     `ParentID`, `DOB`, `POB`, `Address`, `Phone`, `Email`, 
                         `Status`, `AcademicYear`
    , `Semester`, `FacultyID`
    , `Dept_ID`, `BatchCode`,StudentID
    , `BillType`,photo,Dateofissue,SchoolName,Ambarella,RollNo,CertificateNO,AverageMarks,Grade,mothername) 
    VALUES(_id,_stname,_StGender,_parentid,_sdob,_spob,_address,
            _Phone,_Email,_Status,_academicyear,_Semester,
            _faculties,_depts,_batch,concat(_batch,'0',_id),
                                   _billtype,_photo
                                   ,_Dateofissue,_SchoolName,_Ambarella,_RollNo,_CertificateNO,_AverageMarks,_Grade,_mothername);
UPDATE `setupid` SET `Number`=Number+1 WHERE Name='students';
                                   select "Student Inserted!" as msg;
                                 
end;
   ELSE   BEGIN
SELECT `ParentID` into _parentid FROM `students` WHERE id=_id;
update parent set  `Name`=_ParentName1, `sex`=_StGender1, `address`=_ParentAddress1,
 `sponsor`=_Sponsor1, `phone1`=_ParentPhone1, `phone2`=_ParentPhone11,
Name2=_ParentName2,sex2=_StGender2,address2=_ParentAddress2,
sponsor2=_Sponsor2,phone21=_ParentPhone2,phone22=_ParentPhone22 where id=_parentid;

update students set `StudentName`=_stname, `Gender`=_StGender,
    `DOB`=_sdob, `POB`=_spob, `Address`=_address, `Phone`=_Phone, `Email`=_Email, mothername=_mothername,
                          `AcademicYear`=_academicyear
    , `Semester`=_Semester, `FacultyID`=_faculties
    , `Dept_ID`=_depts, `BatchCode`=_batch
    , `BillType`=_billtype,Dateofissue=_Dateofissue,photo=_photo
    ,SchoolName=_SchoolName,Ambarella=_Ambarella,RollNo=_RollNo,
    CertificateNO=_CertificateNO,AverageMarks=_AverageMarks,
    Grade=_Grade
    where id=_id;


                                   select "Student Updated!" as msg;
                                 
end;
   end if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_addstudents_admission_update` (IN `_id` INT, IN `_stname` VARCHAR(50), IN `_sdob` DATE, IN `_spob` VARCHAR(50), IN `_StGender` VARCHAR(50), IN `_address` VARCHAR(50), IN `_Phone` VARCHAR(50), IN `_billtype` VARCHAR(50), IN `_Email` VARCHAR(50), IN `_faculties` VARCHAR(50), IN `_depts` VARCHAR(50), IN `_batch` VARCHAR(50), IN `_Semester` VARCHAR(50), IN `_Status` VARCHAR(50), IN `_ParentName1` VARCHAR(50), IN `_StGender1` VARCHAR(50), IN `_ParentAddress1` VARCHAR(50), IN `_Sponsor1` VARCHAR(50), IN `_ParentPhone1` VARCHAR(50), IN `_ParentPhone11` VARCHAR(50), IN `_ParentName2` VARCHAR(50), IN `_StGender2` VARCHAR(50), IN `_ParentAddress2` VARCHAR(50), IN `_Sponsor2` VARCHAR(50), IN `_ParentPhone2` VARCHAR(50), IN `_ParentPhone22` VARCHAR(50), IN `_SchoolName` VARCHAR(50), IN `_Ambarella` VARCHAR(50), IN `_RollNo` VARCHAR(50), IN `_academicyear` VARCHAR(50), IN `_CertificateNO` VARCHAR(50), IN `_AverageMarks` VARCHAR(50), IN `_Dateofissue` DATE, IN `_Grade` VARCHAR(50), IN `_photo` TEXT, IN `_mothername` VARCHAR(50), IN `p_bloodgroup` VARCHAR(50))  NO SQL BEGIN
declare _parentid int unsigned;
    if EXISTS(SELECT id FROM `students` WHERE `id`=_id) THEN
BEGIN
SELECT `ParentID` into _parentid FROM `students` WHERE id=_id;
update parent set  `Name`=_ParentName1, `sex`=_StGender1, `address`=_ParentAddress1,
 `sponsor`=_Sponsor1, `phone1`=_ParentPhone1, `phone2`=_ParentPhone11,
Name2=_ParentName2,sex2=_StGender2,address2=_ParentAddress2,
sponsor2=_Sponsor2,phone21=_ParentPhone2,phone22=_ParentPhone22 where id=_parentid;

update students set `StudentName`=_stname, `Gender`=_StGender,
    `DOB`=_sdob, `POB`=_spob, `Address`=_address, `Phone`=_Phone, `Email`=_Email, mothername=_mothername,
                          `AcademicYear`=_academicyear
    , `Semester`=_Semester, `FacultyID`=_faculties
    , `Dept_ID`=_depts, `BatchCode`=_batch,StudentID=concat(_batch,frm_show_JU_ID(_id,_faculties)),JU_ID=frm_show_JU_ID(_id,_faculties)
    , `BillType`=_billtype,Dateofissue=_Dateofissue,photo=_photo
    ,SchoolName=_SchoolName,Ambarella=_Ambarella,RollNo=_RollNo,
    CertificateNO=_CertificateNO,AverageMarks=_AverageMarks,
    Grade=_Grade,Blood_Group=p_bloodgroup
    where id=_id;


                                   select "Student Updated!" as msg;
                                 
end;
   end if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_addstudent_behavior` (IN `p_id` INT, IN `p_student_id` VARCHAR(100), IN `p_type` INT, IN `p_subject` VARCHAR(50), IN `p_reason` LONGTEXT, IN `p_date` DATE, IN `p_user_id` INT, IN `p_company_id` INT)   BEGIN
SELECT bt.type,bh.name into @btype,@behavior FROM behavior bh INNER join behavior_type bt on bt.id=bh.type where bh.id=p_type;
select ss.class_id into @class_id from students ss where ss.student_id=p_student_id;
SELECT max(id) into @accy FROM academicyear;
IF EXISTS(SELECT * FROM student_behavior where id = p_id) THEN
SELECT 'Updated' into @type;
UPDATE student_behavior set type = p_type, subject = p_subject, reason = p_reason, `date` = p_date,student_id=p_student_id WHERE id = p_id;

SELECT 'success| student behavior updated successfully';
ELSEIF EXISTS(SELECT * FROM student_behavior WHERE student_id = p_student_id and type = p_type and date = p_date) THEN
SELECT 'EXISTS' into @type;
SELECT 'warning| student behavior already registered';
ELSE
SELECT 'Inserted' into @type;
INSERT INTO student_behavior(student_id,class_id,type,subject,reason,`date`,user_id,company_id,accy) VALUES (p_student_id,@class_id,p_type,p_subject,p_reason,p_date,p_user_id,p_company_id, @accy);

SELECT 'success| Student behavior registered successfully';
END IF;
SELECT u.role_id into @role from user u where u.id=p_user_id;
INSERT INTO `logs`( `date`, `user`,role, `log`) 
SELECT  CURRENT_TIMESTAMP(),p_user_id,@role,CONCAT('<b>Student</b>: ',p_student_id ,'    <b>Behavior:</b>',@btype,'[',@behavior,'] <b>Status</b>:',@type);
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_addstudent_behavior1` (IN `p_id` INT, IN `p_student_id` VARCHAR(100), IN `p_type` INT, IN `p_subject` VARCHAR(50), IN `p_reason` LONGTEXT, IN `p_date` DATE, IN `p_user_id` INT, IN `p_company_id` INT)   BEGIN
SELECT bt.type,bh.name into @btype,@behavior FROM behavior bh INNER join behavior_type bt on bt.id=bh.type where bh.id=p_type;
select ss.class_id into @class_id from students ss where ss.student_id=p_student_id;
IF EXISTS(SELECT * FROM student_behavior where id = p_id) THEN
SELECT 'Updated' into @type;
UPDATE student_behavior set type = p_type, subject = p_subject, reason = p_reason, `date` = p_date,student_id=p_student_id WHERE id = p_id;

SELECT 'success| student behavior updated successfully';
ELSEIF EXISTS(SELECT * FROM student_behavior WHERE student_id = p_student_id and type = p_type and date = p_date) THEN
SELECT 'EXISTS' into @type;
SELECT 'warning| student behavior already registered';
ELSE
SELECT 'Inserted' into @type;
INSERT INTO student_behavior(student_id,class_id,type,subject,reason,`date`,user_id,company_id) VALUES (p_student_id,@class_id,p_type,p_subject,p_reason,p_date,p_user_id,p_company_id);

SELECT 'success| Student behavior registered successfully';
END IF;
SELECT u.role_id into @role from user u where u.id=p_user_id;
INSERT INTO `logs`( `date`, `user`,role, `log`) 
SELECT  CURRENT_TIMESTAMP(),p_user_id,@role,CONCAT('<b>Student</b>: ',p_student_id ,'    <b>Behavior:</b>',@btype,'[',@behavior,'] <b>Status</b>:',@type);
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_addstudent_multibehavior	` (IN `p_id` INT, IN `p_student_id` VARCHAR(100), IN `p_date` DATE, IN `p_user_id` INT, IN `p_company_id` INT, IN `p_type` TEXT, IN `p_subject` TEXT, IN `p_reason` TEXT)   BEGIN
  DECLARE i INT DEFAULT 1;
    DECLARE type VARCHAR(255);
    DECLARE subject VARCHAR(255);
    DECLARE reason VARCHAR(255);
    
    DECLARE category_count INT;
 SELECT bt.type,bh.name into @btype,@behavior FROM behavior bh INNER join behavior_type bt on bt.id=bh.type where bh.id=p_type;
select ss.class_id into @class_id from students ss where ss.student_id=p_student_id;
SELECT max(id) into @accy FROM academicyear;

    
    SET category_count = LENGTH(p_type) - LENGTH(REPLACE(p_type, ',', '')) + 1;
    
    WHILE i <= category_count DO
        
      
        SET type = SUBSTRING_INDEX(SUBSTRING_INDEX(p_type, ',', i), ',', -1);
        SET subject = SUBSTRING_INDEX(SUBSTRING_INDEX(p_subject, ',', i), ',', -1);
        SET reason = SUBSTRING_INDEX(SUBSTRING_INDEX(p_reason, ',', i), ',', -1);

        
        INSERT INTO student_behavior(student_id,class_id,type,subject,reason,`date`,user_id,company_id,accy) VALUES (p_student_id,@class_id,type,subject,reason,p_date,p_user_id,p_company_id, @accy);

       
      

        SET i = i + 1;
    END WHILE;



SELECT 'success| Student behavior registered successfully';

SELECT u.role_id into @role from user u where u.id=p_user_id;
INSERT INTO `logs`( `date`, `user`,role, `log`) 
SELECT  CURRENT_TIMESTAMP(),p_user_id,@role,CONCAT('<b>Student</b>: ',p_student_id ,'    <b>Behavior:</b>',@btype,'[',@behavior,'] <b>Status</b>:',@type);
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_addstudent_Personal` (IN `_id` INT, IN `_studentid` VARCHAR(50), IN `_sname` VARCHAR(50), IN `_sgender` VARCHAR(50))  NO SQL BEGIN

if not EXISTS(SELECT id from students WHERE id=_id) THEN
BEGIN
INSERT into students (`StudentID`,`StudentName`,`Gender`) VALUES(_studentid,_sname,_sgender);
select "Student Inserted!" as msg;
END;
ELSE
BEGIN
UPDATE students SET StudentID=_studentid,StudentName=_sname,Gender=_sgender WHERE id=_id;
select "Student updated!" as msg;
end;
end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_applicants` (IN `p_id` INT, IN `p_name` VARCHAR(50), IN `p_gender` VARCHAR(50), IN `p_mothername` VARCHAR(50), IN `p_dob` DATE, IN `p_pob` VARCHAR(50), IN `p_address` VARCHAR(50), IN `p_school` INT, IN `p_sponsor_id` INT, IN `p_relation` VARCHAR(50), IN `p_reg_cademicYear` VARCHAR(50), IN `p_photo` VARCHAR(100), IN `p_user_id` INT, IN `p_company_id` INT)  NO SQL BEGIN
START TRANSACTION;
SELECT ft.amount into @amount FROM fee_type ft where ft.fee_name='Imtixaan' and ft.company_id = p_company_id;
if exists(select * from applicants where id = p_id) THEN
update applicants set StudentName=p_name,Gender=p_gender,POB=p_pob, mothername=p_mothername, Address=p_address,RegAcademicYear=p_reg_cademicYear, Photo=p_photo,sponsor_id=p_sponsor_id, relation=p_relation,DOB=p_dob,school=p_school WHERE id = p_id;
SELECT 'success| Xogta codsadaha waa la bedelay';
ELSEIF NOT EXISTS(select * from applicants where studentName=p_name and photo=p_photo)THEN
INSERT INTO applicants(StudentName, Gender, POB, mothername, Address,RegAcademicYear, Photo, user_id,sponsor_id, relation,DOB,company_id,school) VALUES (
p_name,p_gender,p_pob,p_mothername,p_address,p_reg_cademicYear,p_photo,p_user_id,p_sponsor_id,p_relation,p_dob,p_company_id,p_school);
set @st_id= (select max(id)+1 from applicants);
insert into student_status_change(`student_id`, `to_status`, `detail`, `date`, `user_id`)
VALUES(@st_id,1,'New Applicant Student',date(now()),p_user_id);


select 'success|Si sax ah ayaa loo diiwaangaliyey';
ELSE
select 'warning|Ardaygan hore Ayaa loo diiwaangaliyey';
end IF;
COMMIT;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_applicantsponsor` (IN `p_id` INT, IN `p_sponsor_name` VARCHAR(50), IN `p_gender` VARCHAR(6), IN `p_mobile` VARCHAR(10), IN `p_address` VARCHAR(100), IN `p_company_id` INT, IN `p_user_id` INT)  NO SQL COMMENT 'add Masuul' BEGIN
IF EXISTS(select * from applicants_sponsors where sponsor_name=p_sponsor_name and mobile=p_mobile and company_id=p_company_id)
THEN
SELECT 'warning|Waalidka Horay Ayuu u jiray';
ELSE
INSERT INTO applicants_sponsors(sponsor_name, gender, address, mobile,company_id, user_id) VALUES 
( p_sponsor_name, p_gender, p_address, p_mobile, p_company_id, p_user_id);
SELECT 'success|Waalidka waa la diiwaangaliyey';
end IF;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_applyleave` (IN `p_id` INT, IN `p_class_id` VARCHAR(50), IN `p_student_id` VARCHAR(50), IN `p_apply` VARCHAR(11), IN `p_from` VARCHAR(11), IN `p_to` VARCHAR(11), IN `p_status` VARCHAR(50), IN `p_reason` VARCHAR(100), IN `p_user` INT)  NO SQL BEGIN
set p_from=date_format(str_to_date(p_from,'%d/%m/%Y'),'%Y-%m-%d');
set p_to=date_format(str_to_date(p_to,'%d/%m/%Y'),'%Y-%m-%d');
set p_apply=date_format(str_to_date(p_apply,'%d/%m/%Y'),'%Y-%m-%d');
if EXISTS(SELECT * FROM student_applyleave where id=p_id)THEN
update student_applyleave  set from_date=p_from,to_date=p_to,apply_date=p_apply,status=p_status,
student_id=p_student_id,class_id=p_class_id where id=p_id;
select 'success|Waa la diiwaangaliyey';
ELSEIF EXISTS(SELECT * FROM student_applyleave WHERE student_id=p_student_id and class_id=p_class_id and from_date=p_from and to_date=p_to) THEN
select 'warning|Hore ayuu u jiray';
ELSE
INSERT INTO student_applyleave( student_id, class_id, from_date, to_date, apply_date, user_id, reason,status)
VALUES(p_student_id,p_class_id,p_from,p_to,p_apply,p_user,p_reason,p_status);
select 'success|Waa la diiwaangaliyey';
end if;
end$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_assessment` (IN `p_id` INT, IN `p_teacher` INT, IN `p_week` TEXT, IN `p_month` TEXT, IN `p_assessent_id` TEXT, IN `p_points` TEXT, IN `p_mark` TEXT, IN `p_remark` TEXT, IN `p_date` DATE, IN `p_user_id` INT)  NO SQL BEGIN
DECLARE i int;


SELECT a.id into @current FROM academicyear A WHERE  A.is_active='yes';
create  table t(assessent_id text,points text,mark text,remark text);
insert into t values(p_assessent_id,p_points,p_mark,p_remark);
        drop  table  if exists a ;
create  table a(assessent_id text,points text,mark text,remark text);
SELECT LENGTH(assessent_id) - LENGTH(REPLACE(assessent_id, ',', '')) + 1,LENGTH(points) - LENGTH(REPLACE(points, ',', '')) + 1 ,LENGTH(mark) - LENGTH(REPLACE(mark, ',', '')) + 1 ,LENGTH(remark) - LENGTH(REPLACE(remark, ',', '')) + 1  into @assessent_id,@points,@mark,@remark FROM `t` ;
set i=1;
WHILE i<=@assessent_id DO
insert into a(assessent_id,points,mark,remark)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(assessent_id, ',', i), ',', -1), if(i>@points,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(points, ',', i), ',', -1)),if(i>@mark,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(mark, ',', i), ',', -1)),if(i>@remark,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(remark, ',', i), ',', -1))  FROM `t`;
 SET i = i + 1;
 END WHILE;
 
if EXISTS(SELECT * from assessment_info where id=p_id)THEN
 SELECT sum(mark) into @mark from a;
 update assessment_info set total_marks=@mark 
 where id=p_id;
 set @assessment_info=p_id;
 ELSE
INSERT INTO `assessment_info`( `employee_id`, `total_marks`, `month`, `week`, `accy`, `user_id`, `date`) 
 
 SELECT p_teacher,sum(mark),p_month,p_week,@current,p_user_id,p_date from a;
 
 set @assessment_info=(SELECT LAST_INSERT_ID()); 
 END if;
   INSERT into assessment_detail(assessment_info, point_id,marks,remark)
SELECT @assessment_info,points,mark,remark
FROM   a
WHERE  NOT EXISTS (SELECT 1 
                   FROM   assessment_detail 
                   WHERE  a.assessent_id = assessment_detail.id and assessment_detail.assessment_info=@assessment_info);
                   UPDATE assessment_detail ad
        INNER JOIN a a 
             ON a.assessent_id = ad.id
SET ad.marks = a.mark,ad.remark=a.remark ;

select 'success|Si sax ah ayaa loo xareeyey';
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_behavior` (IN `p_id` INT, IN `p_school` INT, IN `p_type` INT, IN `p_name` VARCHAR(50), IN `p_mark` DECIMAL(18,2), IN `p_user_id` INT, IN `p_company_id` INT)   BEGIN
IF EXISTS(SELECT * FROM behavior where id = p_id) THEN
UPDATE behavior set name = p_name,type = p_type, modified_user = p_user_id,marks=p_mark where id = p_id;
SELECT 'success| Behavior type updated successfully';
ELSEIF EXISTS( SELECT * FROM behavior where name = p_name and type = p_type )THEN

SELECT 'warning| Behavior type already registered';
ELSE
INSERT INTO behavior(school,name,type,user_id,company_id,marks) VALUES(p_school,p_name,p_type,p_user_id,p_company_id,p_mark);
SELECT 'success| Behavior type registered successfully';
END IF;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_behavior1` (IN `p_id` INT, IN `p_type` INT, IN `p_name` VARCHAR(50), IN `p_user_id` INT, IN `p_company_id` INT)   BEGIN
IF EXISTS(SELECT * FROM behavior where id = p_id) THEN
UPDATE behavior set name = p_name,type = p_type, modified_user = p_user_id where id = p_id;
SELECT 'success| Behavior type updated successfully';
ELSEIF EXISTS( SELECT * FROM behavior where name = p_name and type = p_type )THEN

SELECT 'warning| Behavior type already registered';
ELSE
INSERT INTO behavior(name,type,user_id,company_id) VALUES(p_name,p_type,p_user_id,p_company_id);
SELECT 'success| Behavior type registered successfully';
END IF;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_behavior_type` (IN `p_id` INT, IN `p_type` VARCHAR(50), IN `p_Isbad` INT, IN `p_school` INT, IN `p_user_id` INT, IN `p_company_id` INT)   BEGIN
IF EXISTS(SELECT * FROM behavior_type where id = p_id) THEN
UPDATE behavior_type set type = p_type, IsBad =p_Isbad,modified_user = p_user_id  where id = p_id;
SELECT 'success| Behavior category updated successfully';
ELSEIF EXISTS( SELECT * FROM behavior_type where type = p_type )THEN

SELECT 'warning| Behavior category already registered';
ELSE
INSERT INTO behavior_type(type,IsBad,user_id,company_id,school) VALUES (p_type,p_Isbad,p_user_id,p_company_id,p_school);
SELECT 'success| Behavior category registered successfully';
END IF;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_bus` (IN `p_id` INT, IN `p_bus_name` VARCHAR(100), IN `p_number` VARCHAR(100), IN `p_NoStudent` INT, IN `p_driver_id` VARCHAR(100), IN `p_user_id` INT)  NO SQL BEGIN
if EXISTS(SELECT * from buses b where b.id = p_id) THEN
UPDATE buses set bus_name = p_bus_name,driver_id = p_driver_id,NoStudent=p_NoStudent,number  = p_number where id = p_id;
SELECT 'success|Si sax ah aya loo beddelay';
ELSEIF EXISTS(select * from buses WHERE  bus_name=p_bus_name ) THEN
select 'warning|Baskan hore ayuu u jiray';
ELSE
INSERT INTO buses( bus_name, driver_id,NoStudent, number, user_id) VALUES
(p_bus_name ,p_driver_id,p_NoStudent ,p_number ,p_user_id );
select 'success|Baskan waa la diiwaangaliyey';
end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_bus1` (IN `p_id` INT, IN `p_bus_name` VARCHAR(50), IN `p_driver_name` VARCHAR(50), IN `p_mobile` VARCHAR(10), IN `p_user_id` INT, IN `p_company_id` INT)  NO SQL BEGIN
if EXISTS(SELECT * from buses b where b.id = p_id) THEN
UPDATE buses set bus_name = p_bus_name,driver_name = p_driver_name,mobile = p_mobile where id = p_id;
SELECT 'success|Si sax ah aya loo beddelay';
ELSEIF EXISTS(select * from buses WHERE company_id =p_company_id   and bus_name=p_bus_name ) THEN
select 'warning|Baskan hore ayuu u jiray';
ELSE
INSERT INTO buses( bus_name, driver_name, mobile, company_id, user_id) VALUES (p_bus_name ,p_driver_name ,p_mobile ,p_company_id ,p_user_id );
select 'success|Baskan waa la diiwaangaliyey';
end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_buslocation` (IN `p_id` INT, IN `p_bus` INT, IN `p_shift` TEXT, IN `p_user_id` INT, IN `p_sub_district` TEXT)   BEGIN
DECLARE i int;
START TRANSACTION; 
create  table t(sub_district text);
insert into t values(p_sub_district);

     drop  table  if exists a ;
create  table a(sub_district text);
SELECT LENGTH(sub_district) - LENGTH(REPLACE(sub_district, ',', '')) + 1 into @id FROM `t` ;
set i=1;
WHILE i<=@id DO
insert into a(sub_district)
SELECT  if(i>@id,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(sub_district, ',', i), ',', -1))  FROM `t`;
 SET i = i + 1;
 END WHILE;
 COMMIT;
  if(p_id!=0)THEN
  insert into deletebuslocation
  SELECT * from buslocation  where location not in (SELECT sub_district from a) and bus_info=p_id;
DELETE from buslocation  where location not in (SELECT sub_district from a) and bus_info=p_id;
END if;
 if EXISTS(SELECT * from buslocationinfo where bus=p_bus and shift=p_shift) and p_id=0 THEN
 SELECT id into p_id from buslocationinfo where bus=p_bus and shift=p_shift;
 END if;
 
 
IF not EXISTS(SELECT * from buslocationinfo where id=p_id)THEN
INSERT INTO `buslocationinfo`( `bus`, `shift`, `user_id`)
VALUES (p_bus,p_shift,p_user_id);
SELECT LAST_INSERT_ID() into p_id;
ELSE
UPDATE buslocationinfo set bus=p_bus,shift=p_shift where id=p_id;
END if;


INSERT IGNORE INTO `buslocation`( `bus_info`, `location`, `user_id`)
 SELECT	 p_id,sub_district,p_user_id from a where sub_district!=0;
 SELECT COUNT(*) into @number  from buslocation where  bus_info=p_id;
 if(@number<1)THEN
 DELETE from buslocationinfo where id=p_id;
 END if;

 select'success| Data updated successfully ' as msg; 
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_cash_receipt` (IN `p_tran_date` DATE, IN `p_student_id` VARCHAR(50), IN `p_fee_type` INT, IN `p_month` TEXT, IN `p_amount` TEXT, IN `p_usd_no` VARCHAR(50), IN `p_remark` TEXT, IN `p_user_id` VARCHAR(50), IN `p_academic_year` VARCHAR(50), IN `p_boono` INT, IN `p_receipt_type` INT, IN `p_class` TEXT, IN `p_force` INT)  NO SQL BEGIN
DECLARE i int;
START TRANSACTION;
if(p_force!=1)THEN
if EXISTS (SELECT * FROM cash_receipt cr where cr.boono=p_boono and cr.user_id!=p_user_id and cr.status=1)THEN
UPDATE setupid set Number=(SELECT max(boono)+1 FROM cash_receipt) WHERE name='Rv';
SELECT max(boono)+1 into p_boono FROM cash_receipt;

end IF;
end IF;



create  table t(month text,amount text,class text);
insert into t values(p_month,p_amount,p_class);


     drop  table  if exists a ;
create  table a(month text,amount text,class text);
SELECT LENGTH(month) - LENGTH(REPLACE(month, ',', '')) + 1,LENGTH(amount) - LENGTH(REPLACE(amount, ',', '')) + 1,LENGTH(class) - LENGTH(REPLACE(class, ',', '')) + 1 into @month, @amount,@class FROM `t` ;
set i=1;
WHILE i<=@month DO
insert into a(month,amount,class)
SELECT  if(i>@month,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(month, ',', i), ',', -1)),if(i>@amount,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(amount, ',', i), ',', -1)),if(i>@class,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(class, ',', i), ',', -1))  FROM `t`;
 SET i = i + 1;
 END WHILE;
 if (p_fee_type=7)THEN
Insert into cash_receipt (tran_date,student_id ,class_id ,fee_type ,month ,amount ,usd_no ,remark ,user_id ,academic_year ,boono  ,receipt_by)

SELECT p_tran_date,p_student_id,class,p_fee_type,month,amount,
p_usd_no,p_remark,p_user_id,p_academic_year,p_boono,p_receipt_type from a where month!="" ;
ELSE
Insert into cash_receipt (tran_date,student_id ,class_id ,fee_type ,month ,amount ,usd_no ,remark ,user_id ,academic_year ,boono  ,receipt_by)

SELECT p_tran_date,p_student_id,class,p_fee_type,month,amount,
p_usd_no,p_remark,p_user_id,p_academic_year,p_boono,p_receipt_type from a;
END if;
if (p_fee_type=1)THEN
UPDATE students ss set ss.status=2 where ss.student_id=p_student_id;
insert into student_status_change(`student_id`, `to_status`, `detail`, `date`, `user_id`)
VALUES(p_student_id,2,'Enrolled',p_tran_date,p_user_id);
END if;

UPDATE setupid set Number=(SELECT max(boono)+1 FROM cash_receipt) WHERE name='Rv';

SELECT p_boono as success;
COMMIT;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_caties` (IN `p_id` INT, IN `p_country` INT, IN `p_states` INT, IN `p_city` VARCHAR(50))  NO SQL BEGIN
if EXISTS(SELECT * from cities c where c.id=p_id)THEN
UPDATE cities c set c.name=p_city where c.id=p_id;
SELECT 'success | Cities type updated successfully';
ELSE
INSERT INTO `cities`( `name`, `state_id`, `country_id`) VALUES (p_city,p_states,p_country);
SELECT 'success| Cities type registered successfully';
END if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_class` (IN `p_id` INT, IN `p_class_name` VARCHAR(50), IN `p_school_level_id` INT, IN `p_type` VARCHAR(50), IN `p_language` VARCHAR(50), IN `p_shift` VARCHAR(50), IN `p_section` VARCHAR(100), IN `p_nextclass` INT, IN `p_company_id` INT, IN `p_user_id` INT)  NO SQL BEGIN
if EXISTS(select * from classes c where  c.company_id = p_company_id and c.id=p_id)THEN
update classes set class_name = p_class_name, school_level_id = p_school_level_id, type = p_type,shift=p_shift ,`language`= p_language, updated_user = p_user_id ,NextClass=p_nextclass
WHERE id = p_id and company_id = p_company_id ;
SELECT 'success|Si sax ah aya loo beddelay' as msg;
ELSEIF EXISTS(select * from classes where class_name = p_class_name and school_level_id =p_school_level_id and type = p_type and `language`= p_language  and shift=p_shift and company_id = p_company_id ) THEN
select 'warning|Fasalkaan hore ayuu u jiray';
else
insert into classes(class_name,school_level_id,type,`language`,shift,NextClass,company_id,user_id) values(p_class_name,p_school_level_id,p_type,p_language,p_shift,p_nextclass,p_company_id,p_user_id);
SELECT LAST_INSERT_ID()into p_id;
select 'success|Si sax ah aya loo diiwaangaliyay' as msg;
end if;

create table s( txt text );
insert into s values(p_section);

drop temporary table if exists temp_classes;
create temporary table temp_classes( val char(255) );
set @sql1 = concat("insert into temp_classes (val) values ('", replace(( select group_concat(distinct txt) as data from s), ",", "'),('"),"');");
prepare stmt1 from @sql1;
execute stmt1;

DELETE from class_sections  WHERE class_id = p_id and section_id  not in (SELECT val from temp_classes );


INSERT IGNORE INTO class_sections (class_id,section_id,user_id) 
select p_id,val,p_user_id from temp_classes;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_class_section` (IN `p_class_id` VARCHAR(50), IN `p_section_id` VARCHAR(50), IN `p_user_id` INT)   BEGIN
create table s( txt text );
insert into s values(p_section_id);

drop temporary table if exists temp_classes;
create temporary table temp_classes( val char(255) );
set @sql1 = concat("insert into temp_classes (val) values ('", replace(( select group_concat(distinct txt) as data from s), ",", "'),('"),"');");
prepare stmt1 from @sql1;
execute stmt1;

DELETE from class_sections  WHERE class_id = p_class_id and p_section_id not in (SELECT val from temp_classes );


INSERT IGNORE INTO class_sections (class_id,section_id,user_id) 
select p_class_id,val,p_user_id from temp_classes;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_class_teacher` (IN `p_id` INT, IN `p_class_id` INT, IN `p_teacher_id` VARCHAR(200), IN `p_academic_year_id` INT)   BEGIN
create table t( txt text );
insert into t values(p_teacher_id);

drop temporary table if exists temp_teachers;
create temporary table temp_teachers( val char(255) );
set @sql1 = concat("insert into temp_teachers (val) values ('", replace(( select group_concat(distinct txt) as data from t), ",", "'),('"),"');");
prepare stmt1 from @sql1;
execute stmt1;

INSERT INTO class_teacher(`class_id`, `teacher_id`, `academic_year`) 
SELECT p_class_id,val,p_academic_year_id FROM temp_teachers;
SELECT 'success| Record saved successfully';
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_driver` (IN `p_id` INT, IN `p_driver_name` VARCHAR(100), IN `p_phone` VARCHAR(100), IN `p_school` INT, IN `p_user_id` INT)   BEGIN
IF EXISTS(SELECT * FROM driver WHERE id = p_id) THEN
UPDATE driver set driver_name = p_driver_name,school=p_school,phone = p_phone WHERE id = p_id;
SELECT 'success|Si sax ah ayaa loo bedelay';
ELSEIF EXISTS(select * from driver WHERE school =p_school  and driver_name =p_driver_name ) THEN
select 'warning|Darawalkan hore ayuu u jiray';
ELSE
INSERT INTO driver(driver_name,phone,user_id,school) VALUES (p_driver_name,p_phone,p_user_id,p_school );
select 'success|Si sax ah ayaa loo xareeyey';
end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_employee_attendence` (IN `p_id` INT, IN `p_emp_id` INT, IN `p_date` DATE, IN `p_laga_rabay` TIME, IN `p_soo_galay` TIME, IN `p_faafaahin` VARCHAR(1000), IN `p_user_id` INT)   BEGIN

IF EXISTS(SELECT * from employee_attendece where id = p_id) THEN
UPDATE employee_attendece e set e.laga_rabay=p_laga_rabay,e.soo_galay=p_soo_galay,e.faafaahin=p_faafaahin  WHERE id = p_id;
SELECT 'success| Attendence updated successfully';
ELSE
INSERT IGNORE INTO employee_attendece(  	Emp_id,laga_rabay,soo_galay,faafaahin,date,user_id) VALUES(p_emp_id,p_laga_rabay,
p_soo_galay,p_faafaahin,p_date,p_user_id);
SELECT 'success|Attendence inserted successfully';
end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_employee_behavior` (IN `p_id` INT, IN `p_emp_id` INT, IN `p_date` DATE, IN `p_anshaxa` INT, IN `p_faafaahin` VARCHAR(1000), IN `p_user_id` INT)   BEGIN

IF EXISTS(SELECT * from employee_behavior where id = p_id) THEN
UPDATE employee_behavior e set e.anshaxa=p_anshaxa,e.remark=p_faafaahin,e.date=p_date ,e.Emp_id=p_emp_id WHERE id = p_id;
SELECT 'success| Employee Behavior updated successfully';
ELSE
INSERT  INTO employee_behavior( `Emp_id`, `anshaxa`, `remark`, `date`, `p_user`)
VALUES(p_emp_id,
p_anshaxa,p_faafaahin,p_date,p_user_id);
SELECT 'success| Employee Behavior inserted successfully';
end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_employee_sponser` (IN `p_id` INT, IN `p_name` VARCHAR(100), IN `p_image` VARCHAR(1000), IN `p_tell1` VARCHAR(100), IN `p_tell2` VARCHAR(100), IN `p_adderss` VARCHAR(100), IN `p_job` VARCHAR(100), IN `p_Darajada` VARCHAR(100), IN `p_AQoonsiga` VARCHAR(100), IN `p_date` DATE, IN `p_user_id` INT)   BEGIN
IF EXISTS(SELECT * FROM  employee_sponser WHERE id = p_id) THEN
UPDATE `employee_sponser` SET name=p_name,`tell1`=p_tell1,`tell2`=p_tell2,`address`=p_adderss,`job`=p_job,
`darajada`=p_Darajada,`Aqoonsiga`=p_AQoonsiga,Date=p_date,`image`=p_image
where id=p_id;
SELECT 'success| Sponser updated successfully';
ELSE
INSERT INTO `employee_sponser`( `name`, `tell1`, `tell2`, `address`, `job`, `darajada`, `Aqoonsiga`, `image`, `Date`, `user_id`) VALUES
(p_name,p_tell1,p_tell2,p_adderss,p_job,p_Darajada,p_AQoonsiga,p_image,p_date,p_user_id);
SELECT 'success| Sponser added successfully';
END IF;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_employee_status` (IN `p_id` INT, IN `p_states` VARCHAR(100))  NO SQL BEGIN
if EXISTS(SELECT * from employee_status e where e.id=p_id)THEN
UPDATE employee_status e set e.Name=p_states where e.id=p_id;
SELECT 'success | status type updated successfully';
ELSE
INSERT INTO `employee_status`( `Name`) VALUES (p_states);
SELECT 'success| status type registered successfully';
END if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_exam_class` (IN `p_id` INT, IN `p_exam_type` INT, IN `p_class` INT, IN `p_subject` VARCHAR(1000), IN `p_marks` DECIMAL(18,2), IN `p_acedemic_year` INT, IN `p_user_id` INT, IN `p_company_id` INT)  NO SQL BEGIN
create table s( txt text );
insert into s values(p_subject);

drop temporary table if exists temp_subjects;
create temporary table temp_subjects( val char(255) );
set @sql2 = concat("insert into temp_subjects (val) values ('", replace(( select group_concat(distinct txt) as data from s), ",", "'),('"),"');");
prepare stmt2 from @sql2;
execute stmt2;

START TRANSACTION;
DELETE from exam_class_info  where id not in (
SELECT exam_info FROM exam_class );



if NOT EXISTS(select * from exam_class_info ec where ec.exam_type_id=p_exam_type and ec.acedemic_year=p_acedemic_year and ec.class_id=p_class and ec.marks=p_marks) and p_id=0 THEN
INSERT  INTO  exam_class_info(exam_type_id,class_id ,marks,acedemic_year ,user_id,company_id) VALUES (p_exam_type,p_class,p_marks,p_acedemic_year ,p_user_id,p_company_id);
SELECT LAST_INSERT_ID() into @id;
ELSEIF  NOT EXISTS(select * from exam_class_info ec where ec.exam_type_id=p_exam_type and ec.acedemic_year=p_acedemic_year and ec.class_id=p_class)  and p_id!=0 THEN
UPDATE exam_class_info set exam_type_id=p_exam_type,marks=p_marks,acedemic_year=p_acedemic_year where id=p_id;
SELECT p_id into @id;
DELETE from exam_class where exam_info=@id;
ELSEIF   EXISTS(select * from exam_class_info ec where ec.exam_type_id=p_exam_type and ec.acedemic_year=p_acedemic_year and ec.class_id=p_class and  ec.id=p_id and ec.marks=p_marks)THEN
UPDATE exam_class_info set exam_type_id=p_exam_type,marks=p_marks,acedemic_year=p_acedemic_year where id=p_id;
SELECT p_id into @id;
DELETE from exam_class where exam_info=@id;
ELSEIF   EXISTS(select * from exam_class_info ec where ec.exam_type_id=p_exam_type and ec.acedemic_year=p_acedemic_year and ec.class_id=p_class and  ec.id=p_id and ec.marks!=p_marks)THEN
if EXISTS(select * from exam_class_info ec where ec.exam_type_id=p_exam_type and ec.acedemic_year=p_acedemic_year and ec.class_id=p_class  and ec.marks=p_marks)THEN
select ec.id into @id from exam_class_info ec where ec.exam_type_id=p_exam_type and ec.acedemic_year=p_acedemic_year and ec.class_id=p_class  and ec.marks=p_marks;
DELETE from exam_class_info where id= p_id;
SELECT p_id into @delete_id;
 UPDATE exam_class   set    exam_info= @id where NOT EXISTS (SELECT 1 
                 FROM   exam_class  ec
                  WHERE  ec.exam_info =@delete_id); 
ELSE
UPDATE exam_class_info set exam_type_id=p_exam_type,marks=p_marks,acedemic_year=p_acedemic_year where id=p_id;
SELECT p_id into @id;
DELETE from exam_class where exam_info=@id;
END if;



end if;
INSERT IGNORE INTO exam_class (exam_info ,subject_id)
SELECT @id,val FROM temp_subjects  where NOT EXISTS (SELECT 1 
                 FROM   exam_class  ec
                 WHERE  ec.exam_info =@id and ec.subject_id=temp_subjects.val);
                   




SELECT 'success| record saved successfully';
COMMIT;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_exam_class1` (IN `p_id` INT, IN `p_exam_type` INT, IN `p_class` INT, IN `p_subject` VARCHAR(1000), IN `p_marks` DECIMAL(18,2), IN `p_acedemic_year` INT, IN `p_user_id` INT, IN `p_company_id` INT)  NO SQL BEGIN
create table s( txt text );
insert into s values(p_subject);

drop temporary table if exists temp_subjects;
create temporary table temp_subjects( val char(255) );
set @sql2 = concat("insert into temp_subjects (val) values ('", replace(( select group_concat(distinct txt) as data from s), ",", "'),('"),"');");
prepare stmt2 from @sql2;
execute stmt2;

START TRANSACTION;


if NOT EXISTS(select * from exam_class_info where id=p_id)THEN
INSERT  INTO  exam_class_info(exam_type_id,class_id ,marks,acedemic_year ,user_id,company_id) VALUES (p_exam_type,p_class,p_marks,p_acedemic_year ,p_user_id,p_company_id);
SELECT LAST_INSERT_ID() into @id;
ELSE
UPDATE exam_class_info set exam_type_id=p_exam_type,marks=p_marks,acedemic_year=p_acedemic_year where id=p_id;
SELECT p_id into @id;
end if;

DELETE from exam_class where exam_info=@id;
INSERT INTO exam_class (exam_info ,subject_id)
SELECT @id,val FROM temp_subjects;
SELECT 'success| record saved successfully';
COMMIT;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_exam_pass_mark` (IN `p_id` INT, IN `p_class` TEXT, IN `p_marks` DECIMAL(18,2), IN `p_acedemic_year` INT, IN `p_user_id` INT, IN `p_company_id` INT)  NO SQL BEGIN
create table s( txt text );
insert into s values(p_class);

drop temporary table if exists temp_class;
create temporary table temp_class( val char(255) );
set @sql2 = concat("insert into temp_class (val) values ('", replace(( select group_concat(distinct txt) as data from s), ",", "'),('"),"');");
prepare stmt2 from @sql2;
execute stmt2;

START TRANSACTION;

if NOT EXISTS(select * from exam_pass_mark_info where id=p_id)THEN
INSERT  INTO  exam_pass_mark_info(marks,academic_year ,user_id) VALUES (p_marks,p_acedemic_year ,p_user_id);
SELECT LAST_INSERT_ID() into @id;
ELSE
UPDATE exam_pass_mark_info set marks=p_marks,academic_year=p_acedemic_year where id=p_id;
SELECT p_id into @id;
end if;

DELETE from exam_pass_mark  where pass_mark_info=@id;
INSERT INTO exam_pass_mark (pass_mark_info ,classes )
SELECT @id,val FROM temp_class;
SELECT 'success| record saved successfully';
COMMIT;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_exam_result_view` (IN `p_id` INT, IN `p_class` INT, IN `p_exam_info` INT, IN `p_acedemic_year` INT, IN `p_marks` DECIMAL(18,2), IN `p_weak_marks` DECIMAL(18,2), IN `p_IsFinal` INT, IN `p_grade` VARCHAR(100), IN `p_subject` TEXT)  NO SQL BEGIN
create table s( txt text );
insert into s values(p_subject);

drop temporary table if exists temp_subjects;
create temporary table temp_subjects( val char(255) );
set @sql2 = concat("insert into temp_subjects (val) values ('", replace(( select group_concat(distinct txt) as data from s), ",", "'),('"),"');");
prepare stmt2 from @sql2;
execute stmt2;
START TRANSACTION;

if NOT EXISTS(select * from exam_result_class e where e.id=p_id )THEN
INSERT IGNORE  INTO  exam_result_class(`class`, `academic_year`, `exam_result_info`, `pass_mark`,weak_marks,Final, `Grade_view`) VALUES (p_class,p_acedemic_year ,p_exam_info,p_marks,p_weak_marks,p_IsFinal,p_grade);
SELECT LAST_INSERT_ID() into @id;
ELSE

UPDATE exam_result_class e set e.class=p_class,e.pass_mark=p_marks,e.academic_year=p_acedemic_year
,e.exam_result_info=p_exam_info,e.Grade_view=p_grade,e.weak_marks=p_weak_marks,e.Final=p_IsFinal where id=p_id;
SELECT p_id into @id;
end if;

DELETE from exam_result_class_subject  where exam_result_class_info=@id;
INSERT IGNORE INTO exam_result_class_subject (exam_result_class_info ,subjects)
SELECT @id,val FROM temp_subjects;
SELECT 'success| record saved successfully';
COMMIT;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_exam_type` (IN `p_id` INT, IN `p_type` VARCHAR(100), IN `p_user_id` INT, IN `p_company_id` INT)  NO SQL BEGIN
IF EXISTS(SELECT * FROM exam_type where id = p_id) THEN
UPDATE exam_type set type = p_type where id = p_id;
SELECT 'success|Exam waa la bedelay';
ELSEIF EXISTS(select * from exam_type WHERE type = p_type and company_id =  p_company_id) THEN
select 'warning|Exam hore ayaa loo diiwaangaliyey';
ELSE
INSERT INTO exam_type(type, user_id, company_id) 
VALUES 
(p_type, p_user_id,p_company_id) ;
select 'success|Exam si sax ah ayaa loo diiwaangaliyey';
end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_fee_type` (IN `p_id` INT, IN `p_fee_name` VARCHAR(50), IN `p_pay_method` VARCHAR(50), IN `p_amount` DOUBLE, IN `p_description` VARCHAR(200), IN `p_user_id` INT, IN `p_company_id` INT)  NO SQL BEGIN
IF EXISTS(SELECT * FROM fee_type WHERE id = p_id) THEN
UPDATE fee_type set fee_name = p_fee_name,pay_method = p_pay_method,amount=p_amount,Description = p_description WHERE id = p_id;
SELECT 'success|Si sax ah ayaa loo bedelay';
ELSEIF EXISTS(select * from fee_type WHERE company_id =p_company_id  and fee_name =p_fee_name ) THEN
select 'warning|lacagta nocaan ah hore ayuu u jiray';
ELSE
INSERT INTO fee_type( fee_name,Description, pay_method,amount,company_id, user_id) VALUES (p_fee_name ,p_description ,p_pay_method,p_amount,p_company_id ,p_user_id );
select 'success|Si sax ah ayaa loo xareeyey';
end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_foundation` (IN `_name` VARCHAR(50), IN `_logo` VARCHAR(50), IN `_user_name` VARCHAR(50), IN `_password` VARCHAR(50), IN `_user_id` INT)  NO SQL BEGIN

if exists(select * from foundation_profile WHERE user_name =_user_name and password = _password) THEN
select 'muasasada hore ayaa loo diiwaangaliyey';
else
insert into foundation_profile(name,logo,user_name,password,user_id) values(_name,_logo,_user_name,_password,_user_id);

select 'success';
end if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_grade` (IN `p_id` INT, IN `p_Grade_name` VARCHAR(100), IN `p_Percent_from` DECIMAL(18,2), IN `p_Percent_to` DECIMAL(18,2), IN `p_remark` VARCHAR(1000), IN `p_user_id` INT, IN `p_company` INT)  NO SQL BEGIN


IF not EXISTS(SELECT * from exam_grades where id=p_id)THEN
INSERT INTO `exam_grades`(`Grade_name`, `Percent_from`, `Percent_to`, `Remark`, `user_id`,  `company_id`) VALUES (p_Grade_name,p_Percent_from,p_Percent_to ,p_remark,p_user_id,p_company);
SELECT 'success| record saved successfully';
ELSE
UPDATE exam_grades set Grade_name=p_Grade_name,Percent_from=p_Percent_from,
Percent_to=p_Percent_to,Remark=p_remark where id=p_id;
SELECT 'success| record updated successfully';
END if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_lesson` (IN `p_id` INT, IN `p_assign` INT, IN `p_user_id` INT, IN `p_week` TEXT, IN `p_lesson_id` TEXT, IN `p_lesson` TEXT)   BEGIN
DECLARE i int;
create  table t(id text,lesson text,week text);
insert into t values(p_lesson_id,p_lesson,p_week);
        drop  table  if exists a ;
create  table a(id text,lesson text,week text);
SELECT LENGTH(id) - LENGTH(REPLACE(id, ',', '')) + 1,LENGTH(lesson) - LENGTH(REPLACE(lesson, ',', '')) + 1,LENGTH(week) - LENGTH(REPLACE(week, ',', '')) + 1 into @id,@lesson,@week FROM `t` ;
set i=1;
WHILE i<=@id DO
insert into a(id,lesson,week)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(id, ',', i), ',', -1), if(i>@lesson,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(lesson, ',', i), ',', -1)), if(i>@week,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(week, ',', i), ',', -1)) FROM `t`;
 SET i = i + 1;
 END WHILE;
if not EXISTS(select * from lessoninfo where assign=p_assign)THEN
INSERT INTO lessoninfo ( assign )
VALUES (p_assign);
SELECT LAST_INSERT_ID() into p_id;
ELSE
select id into p_id  from lessoninfo where assign=p_assign;
end if;

DELETE 
FROM lessondetail where id not in (
 select id from a
   WHERE   a.id!=0) and info_id=p_id;
   
INSERT INTO lessondetail (info_id, week, lesson)
SELECT p_id, a.week, a.lesson
FROM a
WHERE a.id = 0;
UPDATE lessondetail l
JOIN a a ON l.id = a.id
SET l.lesson = a.lesson, l.week = a.week
WHERE a.id != 0;








SELECT 'success|Lesson Created successfully';
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_lessons` (IN `p_school` INT, IN `p_level` INT, IN `p_class` INT, IN `p_date` DATE, IN `p_xiisad` INT, IN `p_lesson` TEXT)   BEGIN
INSERT INTO lessons(school, level,class,date,xiisad, lesson) VALUES
(p_school ,p_level,p_class ,p_date,p_xiisad ,p_lesson );
select 'success|casharka si sax ah ayaa loo diiwaangaliyey';


END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_levels` (IN `p_id` INT, IN `p_name` VARCHAR(50), IN `p_user_id` INT, IN `p_company_id` INT)  NO SQL COMMENT 'add level' BEGIN
IF EXISTS(SELECT * from levels where id = p_id) THEN
UPDATE levels set levels.name =p_name WHERE id = p_id;
SELECT 'success|Si sax ah ayaa loo bedelay';
ELSEIF EXISTS(SELECT * FROM levels WHERE name =p_name  and company_id = p_company_id ) THEN
select 'warning|heerka hore ayuu u jiray';

else

INSERT INTO levels(name,company_id,user_id) VALUES(p_name,p_company_id,p_user_id);
select 'success|Si sax ah ayaa loo xareeyey';
end if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_location` (IN `p_district` INT, IN `p_accy` INT, IN `p_user_id` INT, IN `p_id` TEXT, IN `p_sub_district` TEXT, IN `p_amount` TEXT)   BEGIN
DECLARE i int;
START TRANSACTION; 
create  table t(id text,sub_district text,amount text);
insert into t values(p_id,p_sub_district,p_amount);

     drop  table  if exists a ;
create  table a(id text,sub_district text,amount text);
SELECT LENGTH(id) - LENGTH(REPLACE(id, ',', '')) + 1 into @id FROM `t` ;
set i=1;
WHILE i<=@id DO
insert into a(id,sub_district,amount)
SELECT  if(i>@id,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(id, ',', i), ',', -1)),
		if(i>@id,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(sub_district, ',', i), ',', -1)),
        if(i>@id,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(amount, ',', i), ',', -1))  FROM `t`;
 SET i = i + 1;
 END WHILE;
 COMMIT;
 UPDATE IGNORE location l join a on a.id=l.id set l.accy=p_accy, l.sub_district=a.sub_district,l.District=p_district,l.amount=a.amount;
 insert into deletelocation
 SELECT * from location where id not in (SELECT id from a) and District=p_district  and accy=p_accy;
 DELETE from location where id not in (SELECT id from a) and District=p_district  and accy=p_accy;
 INSERT IGNORE location(District,sub_district,amount,user_id,accy)
 SELECT	 p_district,sub_district,amount,p_user_id,p_accy from a where id=0 AND sub_district!='';
 

 select'success| Data updated successfully ' as msg; 
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_meeting` (IN `p_id` INT, IN `p_student_id` VARCHAR(100), IN `p_sponsor_id` INT, IN `p_note` INT, IN `p_status` INT, IN `p_issue` LONGTEXT, IN `p_date` INT, IN `p_user_id` INT)   BEGIN
IF EXISTS(SELECT * from meeting where id = p_id) THEN
UPDATE meeting set meeting.student_id=p_student_id,meeting.sponsor_id=p_sponsor_id, meeting.noted =p_note, meeting.status_id=p_status,meeting.date_reported=p_date,meeting.issue=p_issue  WHERE id = p_id;
SELECT 'success|Si sax ah ayaa loo bedelay';

ELSEIF EXISTS(SELECT * FROM meeting mm WHERE mm.student_id =p_student_id and mm.date_reported = p_date  ) THEN
select 'warning|meeting kan hore ayuu u jiray';
else

INSERT INTO meeting(student_id,sponsor_id,noted,status_id,issue,date_reported,user_id) VALUES(p_student_id,p_sponsor_id,	
p_note,p_status,p_issue,p_date,p_user_id);


select 'success|Si sax ah ayaa loo xareeyey';
end if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_pass_marks` (IN `p_id` INT, IN `p_level_id` INT, IN `p_type` VARCHAR(50), IN `p_language` VARCHAR(50), IN `p_mark` INT, IN `p_company_id` INT, IN `p_user_id` INT)  NO SQL BEGIN
if EXISTS(SELECT * FROM pass_mark WHERE id=p_id)THEN
UPDATE pass_mark set level_id=p_level_id,type=p_type,language=p_language,marks=p_mark,updated_user=p_user_id where id=p_id and company_id = p_company_id;
SELECT 'success|Si sax ah ayaa loo bedelay';
elseIF EXISTS(SELECT * FROM pass_mark WHERE level_id =p_level_id and company_id = p_company_id and type=p_type and language=p_language) THEN
select 'warning|Hore ayuu u jiray';
ELSE
insert into pass_mark(level_id,type,language,marks,company_id,user_id) VALUES
(p_level_id,p_type,p_language,p_mark,p_company_id,p_user_id);

select 'success|Si sax ah ayaa loo xareeyey';
end if;
end$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_receipt_method` (IN `p_id` INT, IN `p_method` VARCHAR(50), IN `p_description` VARCHAR(50), IN `p_user_id` INT, IN `p_company_id` INT)  NO SQL BEGIN
if exists(SELECT * from receipt_method where id = p_id) THEN
UPDATE receipt_method set method= p_method,description = p_description where id= p_id;
SELECT 'success|Nooca qabashada waa la bedelay';
ELSEIF EXISTS(select * from receipt_method WHERE company_id = p_company_id and method =p_method ) THEN
select 'warning|Nooca qabashada hore ayuu u jiray';
ELSE
INSERT INTO receipt_method( company_id, method, description, user_id) VALUES ( p_company_id,p_method,p_description,p_user_id) ;
select 'success|Nooca qabashada waa laxareeyey!';
END if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_role` (IN `p_id` INT, IN `p_role` VARCHAR(1000))  NO SQL BEGIN

IF EXISTS(SELECT * from roles where id = p_id) THEN
UPDATE roles set name =p_role WHERE id = p_id;
SELECT 'success|Si sax ah ayaa loo bedelay';
ELSEIF EXISTS(SELECT * FROM roles WHERE name =p_role   ) THEN
select 'warning|heerka hore ayuu u jiray';

else

INSERT INTO roles(name) VALUES(p_role);
select 'success|Si sax ah ayaa loo xareeyey';
end if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_salary` (IN `p_school` INT, IN `p_type` VARCHAR(100), IN `p_name` INT, IN `p_amount` FLOAT(11,2), IN `p_date` DATE, IN `p_user` INT)   BEGIN
IF EXISTS (SELECT 1 FROM salary WHERE name = p_name) THEN
  SELECT 'Warning | employee already exists';
ELSE
    INSERT INTO salary (school, type, name, amount, date, user)
    VALUES (p_school, p_type, p_name, p_amount, p_date, p_user);
    SELECT 'Success | The record has been inserted';
END IF;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_school` (IN `p_id` INT, IN `p_school_name` VARCHAR(50), IN `p_phone` VARCHAR(50), IN `p_address` VARCHAR(100), IN `p_company_id` INT, IN `p_user_id` INT, IN `p_level_id` TEXT)  NO SQL BEGIN
if EXISTS(select * from schools WHERE id=p_id)THEN
update schools set school_name = p_school_name, address = p_address, phone = p_phone, updated_user = p_user_id where id = p_id and company_id = p_company_id;
set @school_id=p_id;
SELECT 'success| Iskuulka waa la badaly' as msg;
elseif EXISTS(select * from schools WHERE school_name = p_school_name) THEN
select 'warning|Iskuulka hore ayuu u jiray';
else
INSERT INTO schools(school_name, address, phone, company_id, user_id)
VALUES (p_school_name,p_address,p_phone,p_company_id,p_user_id);
select LAST_INSERT_ID() into @school_id;
select 'success| Iskuulka waa la diiwaangaliyay' as msg;
end if;
create table s( txt text );
insert into s values(p_level_id);

drop temporary table if exists temp_classes;
create temporary table temp_classes( val char(255) );
set @sql1 = concat("insert into temp_classes (val) values ('", replace(( select group_concat(distinct txt) as data from s), ",", "'),('"),"');");
prepare stmt1 from @sql1;
execute stmt1;

DELETE from school_levels  WHERE school_id = @school_id and level_id not in (SELECT val from temp_classes );
INSERT IGNORE  INTO  school_levels (school_id,level_id,user_id)
select @school_id,val,p_user_id from temp_classes;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_school_fee` (IN `p_id` INT, IN `p_school_id` INT, IN `p_fee_type_id` INT, IN `p_academidyear_id` INT, IN `p_amount` DOUBLE(18,2), IN `p_company_id` INT, IN `p_user_id` INT)  NO SQL BEGIN
if EXISTS(SELECT * FROM school_fee WHERE id=p_id)THEN
update school_fee set school_id=p_school_id, academidyear_id=p_academidyear_id, fee_type_id=p_fee_type_id, amount=p_amount,updated_user=p_user_id
where   id=p_id;
SELECT 'success|Si sax ah aya loo beddelay';
ELSEIF EXISTS(SELECT * FROM school_fee WHERE school_id = p_school_id and company_id = p_company_id and academidyear_id=p_academidyear_id AND fee_type_id=p_fee_type_id) THEN
select 'warning|Hore ayuu u jiray';

else
INSERT INTO school_fee( school_id, academidyear_id, fee_type_id, amount, company_id, user_id) VALUES( p_school_id, p_academidyear_id, p_fee_type_id, p_amount, p_company_id,p_user_id);
select 'success|Waa la diiwaangaliyey';
end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_school_level` (IN `p_school_id` VARCHAR(50), IN `p_level_id` VARCHAR(50), IN `p_user_id` INT)   BEGIN
