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 = p_school_id and level_id not in (SELECT val from temp_classes );
INSERT IGNORE  INTO  school_levels (school_id,level_id,user_id)
select p_school_id,val,p_user_id from temp_classes;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_section` (IN `p_id` INT, IN `p_section` VARCHAR(50), IN `p_user_id` INT, IN `p_company_id` INT)   BEGIN
IF EXISTS(SELECT * FROM sections WHERE id = p_id) THEN
UPDATE sections s set s.section = p_section where id = p_id;
SELECT 'success| Section updated successfully';
ELSEIF EXISTS(SELECT * FROM sections WHERE section = p_section) THEN
SELECT 'warning| Section already registered';
ELSE
INSERT INTO sections(section,user_id,company_id) VALUES(p_section,p_user_id,p_company_id);
SELECT 'success| Section added successfully';

END IF;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_sponsor` (IN `p_id` INT, IN `p_sponsor_name` VARCHAR(50), IN `p_gender` VARCHAR(15), IN `p_address` VARCHAR(100), IN `p_mobile` VARCHAR(50), IN `p_company_id` INT, IN `p_user_id` INT, IN `p_role_id` INT, IN `p_date` DATE)   BEGIN
    DECLARE last_sponsor_id INT;
    DECLARE p_password VARCHAR(6);
    
    IF EXISTS (SELECT * FROM sponsors WHERE id = p_id) THEN
        UPDATE sponsors 
        SET sponsor_name = p_sponsor_name,
            gender = p_gender,
            address = p_address,
            mobile = p_mobile,
            updated_user = p_user_id
        WHERE id = p_id;
        SELECT 'success|Si sax ah aya loo beddelay';
    
    ELSEIF EXISTS (SELECT * FROM sponsors WHERE mobile = p_mobile AND company_id = p_company_id) THEN
        SELECT 'warning|Masuulka hore ayuu u jiray';
    
    ELSE
        INSERT INTO sponsors(sponsor_name, gender, address, mobile,user_id, company_id, role_id) VALUES 
        (p_sponsor_name, p_gender, p_address, p_mobile,p_user_id, p_company_id,p_role_id );
       SELECT LAST_INSERT_ID() into @spId; 
        SET last_sponsor_id = @spId;
        SELECT 'success|Masuulka waa la diiwaangaliyey';
    END IF;

    
 SET p_password = RIGHT(p_mobile, 4);  
     
    IF NOT EXISTS (SELECT * FROM `user` WHERE employee_id = last_sponsor_id) THEN
        INSERT INTO `user`(`profile`, `employee_id`, `role_id`, `user_name`, `password`, `status`, `date`) 
        SELECT p_company_id, last_sponsor_id, p_role_id, 
                CONCAT('P-', LPAD(last_sponsor_id, 6, '0')), 
                p_password, 
                0, p_date;  
    ELSE
        UPDATE `user`
        SET role_id = p_role_id, status = 1, date = p_date  
        WHERE employee_id = last_sponsor_id;
    END IF;
   

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_sponsor1` (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 sponsors where id=p_id)THEN
UPDATE sponsors 
SET sponsor_name=p_sponsor_name,
gender=p_gender,
address=p_address,
mobile=p_mobile,
updated_user=p_user_id
WHERE id=p_id;
SELECT 'success|Si sax ah aya loo beddelay';
ELSEIF EXISTS(select * from sponsors where  mobile=p_mobile and company_id=p_company_id)
THEN
select 'warning|Masuulka hore ayuu u jiray';
ELSE
INSERT INTO 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|Masuulka waa la diiwaangaliyey';
end IF;
END$$

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

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_status` (IN `p_id` INT, IN `p_status` VARCHAR(50), IN `p_company_id` INT, IN `p_user_id` INT)  NO SQL BEGIN
if EXISTS(select * from status WHERE company_id = p_company_id and id = p_id )THEN
update status set status_name=p_status , updated_user=p_user_id where id=p_id;
SELECT 'success|Si sax ah aya loo beddelay';
elseif EXISTS(select * from status WHERE company_id = p_company_id and status_name = p_status) THEN
select 'warning|Lacagtaan hore ayay u jirtay';
ELSE
INSERT INTO status(status_name , company_id, user_id) VALUES (p_status, p_company_id, p_user_id);
select 'success|Lacagtaan waa la diiwaangaliyey';
end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_students` (IN `p_id` INT, IN `p_student_id` VARCHAR(100), IN `p_student_name` VARCHAR(100), IN `p_gender` VARCHAR(50), IN `p_district` VARCHAR(100), IN `p_address` VARCHAR(50), IN `p_dob` DATE, IN `p_pob` VARCHAR(50), IN `p_mother_name` VARCHAR(50), IN `p_phone` VARCHAR(50), IN `p_sponsor_id` INT, IN `p_relation` VARCHAR(50), IN `p_orphan_status` VARCHAR(50), IN `p_reg_date` VARCHAR(50), IN `p_school_name` VARCHAR(50), IN `p_completed_class` VARCHAR(50), IN `p_class_id` INT, IN `p_reg_academic_year` VARCHAR(100), IN `p_blood_group` VARCHAR(50), IN `p_document` TEXT, IN `p_document_name` TEXT, IN `p_document_id` TEXT, IN `p_reason` TEXT, IN `p_photo` VARCHAR(50), IN `p_user_id` INT)   BEGIN

SELECT a.id into @current FROM academicyear A WHERE  A.is_active='yes';
IF (p_id=0)THEN
SELECT concat('TY-',p_student_id) INTO p_student_id;
END if;
if exists (select * from students where  student_id=p_student_id) and p_id!=0 then 
SELECT ss.class_id,ss.current_academic into @classes,@accy from students ss where  ss.id=p_id;
IF(@classes!=p_class_id)THEN
UPDATe student_behavior sb set sb.class_id=p_class_id where sb.student_id=p_student_id and sb.class_id=@classes;
UPDATE exam_marks em set em.class_id= p_class_id where em.student_id=p_student_id and em.class_id=@classes and em.academicyear=@accy;
UPDATE student_attendence sa set sa.class_id=p_class_id where sa.student_id=p_student_id and sa.class_id=@classes;
UPDATE student_promotion sp set sp.to_class=p_class_id where sp.student_id=p_student_id and sp.from_class=@classes;
UPDATE cash_receipt cr set cr.class_id=p_class_id where cr.student_id=p_student_id and cr.class_id=@classes and cr.academic_year=@accy;
END if;
update students set student_name= p_student_name,gender= p_gender,dob= p_dob,pob= p_pob,address= p_address,
orphan_status= p_orphan_status,phone= p_phone,mother_name= p_mother_name,school_name= p_school_name,
completed_class= p_completed_class,reg_date=p_reg_date,reason=p_reason,reg_academic_year= p_reg_academic_year
,class_id= p_class_id,photo= p_photo,user_id= p_user_id,
sponsor_id= p_sponsor_id,relation= p_relation,district=p_district where id = p_id ;
select'success| Data updated successfully ' as msg; 
ELSEIF EXISTS(SELECT * FROM students where student_name = p_student_name and sponsor_id =p_sponsor_id and mother_name=p_mother_name) THEN
SELECT 'warning| Student already registered!';
else 
if not EXISTS (SELECT * from students ss where ss.student_id=concat('TY-',p_student_id))THEN
set @student_id=p_student_id;
ELSE
SELECT  concat('TY-',s.number) into @student_id from setupid s where s.name = "students";
END if;

insert IGNORE into students(student_id,student_name,gender,address,dob,pob,mother_name,phone,sponsor_id,relation,orphan_status,reg_date,school_name,completed_class,class_id,`status`,reg_academic_year,blood_group,district,photo,user_id,reason,current_academic ) Values(@student_id,p_student_name,p_gender,p_address,p_dob,p_pob,p_mother_name,p_phone,p_sponsor_id,
p_relation,p_orphan_status,p_reg_date,p_school_name,p_completed_class,p_class_id,2,p_reg_academic_year,p_blood_group,p_district,p_photo,p_user_id,p_reason,@current);
insert into student_status_change(`student_id`, `to_status`, `detail`, `date`, `user_id`)
VALUES(@student_id,2,'New Student',p_reg_date,p_user_id);
SELECT cl.type into @type from classes cl where cl.id=p_class_id;
if(@type='Normal')THEN
INSERT INTO `student_promotion`( `student_id`, `from_class`, `from_academic`, `to_class`, `from_month`, `to_month`, `user_id`, `date`)
VALUES(@student_id,0,@current,p_class_id,1,12,p_user_id,date(now()));
ELSE

INSERT INTO `student_promotion`( `student_id`, `from_class`, `from_academic`, `to_class`, `from_month`, `to_month`, `user_id`, `date`)
VALUES(@student_id,0,@current,p_class_id,1,6,p_user_id,date(now()));
END if;
UPDATE setupid s set s.number = s.number + 1 where s.name = "students";
select'success| Data inserted successfully' as msg;
end if; 

create table a( document_id text);
insert into a values(p_document_id);

create table b( documents text );
insert into b values(p_document);

create table c (  document_name text );
insert into c values(p_document_name);

drop temporary table if exists document_id;
create temporary table document_id(id int PRIMARY KEY AUTO_INCREMENT, val varchar(1000) );
set @sql1 = concat("insert into document_id (val) values ('", replace(( select group_concat(distinct document_id) as data from a), ",", "'),('"),"');");
prepare stmt1 from @sql1;
execute stmt1;


drop temporary table if exists documents;
create temporary table documents(id int PRIMARY KEY AUTO_INCREMENT, val varchar(1000) );
set @sql2 = concat("insert into documents (val) values ('", replace(( select group_concat(distinct documents) as data from b), ",", "'),('"),"');");
prepare stmt2 from @sql2;
execute stmt2;


drop temporary table if exists document_name;
create temporary table document_name(id int PRIMARY KEY AUTO_INCREMENT, val varchar(1000) );
set @sql3 = concat("insert into document_name (val) values ('", replace(( select group_concat(distinct document_name) as data from c), ",", "'),('"),"');");
prepare stmt3 from @sql3;
execute stmt3;
create TEMPORARY TABLE table2 
SELECT di.val 'id',d.val 'document',dn.val 'name' FROM document_id di 
INNER join documents d on d.id=di.id INNER join document_name dn on dn.id=di.id;

DELETE from student_documents  where student_id=p_student_id;

INSERT INTO student_documents (`student_id`, `Document`, `Document_name`, `user_id`, `date`)
SELECT p_student_id,concat('images/',document),name,p_user_id,p_reg_date FROM table2 where id!='';



END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_students1` (IN `p_id` INT, IN `p_StudentName` VARCHAR(50), IN `p_Gender` VARCHAR(50), IN `p_mothername` VARCHAR(50), IN `p_DOB` VARCHAR(50), IN `p_POB` VARCHAR(50), IN `P_Photo` VARCHAR(50), IN `p_Address` VARCHAR(50), IN `p_Phone` VARCHAR(50), IN `p_Fee_Status` VARCHAR(50), IN `p_discount` DOUBLE, IN `p_SponsoredBy` INT, IN `p_relation` VARCHAR(50), IN `p_SchoolName` VARCHAR(50), IN `p_SchoolAddress` VARCHAR(50), IN `p_Ambarella` VARCHAR(50), IN `p_TypeOfDeclaration` VARCHAR(50), IN `p_DateOfIssue` VARCHAR(50), IN `p_CompletedClass` VARCHAR(50), IN `p_ClassToSit` VARCHAR(50), IN `p_RegAcademicYear` VARCHAR(50), IN `p_company_id` INT, IN `p_user_id` INT)  NO SQL BEGIN
if NOT EXISTS(SELECT id FROM `students` WHERE `id`=p_id) THEN
BEGIN
INSERT INTO `students`(`StudentName`, `Gender`, `DOB`, `POB`, `Address`, `Phone`, `mothername`, `Fee_Status`, `discount`, `SchoolName`, `SchoolAddress`, `CompletedClass`, `Ambarella`, `TypeOfDeclaration`, `Date_of_issue`, `RegAcademicYear`, `class_id`, `Photo`, `user_id`,  `sponsor_id`, `relation`)
VALUES (`p_StudentName`, `p_Gender`, `p_DOB`, `p_POB`, `p_Address`, `p_Phone`, `p_mothername`, `p_Fee_Status`, `p_discount`, `p_SchoolName`, `p_SchoolAddress`, `p_CompletedClass`, `p_Ambarella`, `p_TypeOfDeclaration`, `p_DateOfIssue`, `p_RegAcademicYear`, `p_ClassToSit`, `p_Photo`, `p_user_id`,  `p_SponsoredBy`, `p_relation`);
select 'success| Si sax ah ayaa loo diiwaangaliyey';
END;
ELSE 
BEGIN
UPDATE students SET StudentName=p_StudentName,Gender= p_Gender,DOB =p_DOB, POB =p_POB, mothername= p_mothername, Address= p_Address, Phone= p_Phone, discount=p_discount, Fee_Status=  p_Fee_Status, sponsor_id=  p_SponsoredBy, 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, Photo=p_Photo where id=p_id;
SELECT 'success| Si sax ah ayaa loo beddelay';
END;
END if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_student_attendence` (IN `p_id` TEXT, IN `p_class` INT, IN `p_date` DATE, IN `p_student_id` TEXT, IN `p_attendence_type` TEXT, IN `p_xiisada` INT, IN `p_remarks` TEXT, IN `p_user_id` INT)   BEGIN
DECLARE i INT DEFAULT 0;
SELECT u.role_id into @role from user u where u.id=p_user_id;
create  table t(id text,student_id text,attendence_type text,remark text);
insert into t values(p_id,p_student_id,p_attendence_type,p_remarks);
        drop  table  if exists a ;
create  table a(id text,student_id text,attendence_type text,remark text);
SELECT LENGTH(id) - LENGTH(REPLACE(id, ',', '')) + 1,LENGTH(student_id) - LENGTH(REPLACE(student_id, ',', '')) + 1,LENGTH(attendence_type) - LENGTH(REPLACE(attendence_type, ',', '')) + 1,LENGTH(remark) - LENGTH(REPLACE(remark, ',', '')) + 1 into @id,@student_id,@attendence_type,@remark FROM `t` ;
set i=1;
WHILE i<=@id DO
insert into a(id ,student_id ,attendence_type ,remark )
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(id, ',', i), ',', -1)as "id", if(i>@student_id,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(student_id, ',', i), ',', -1)) as "name", SUBSTRING_INDEX(SUBSTRING_INDEX(attendence_type, ',', i), ',', -1)as "attendence_type", SUBSTRING_INDEX(SUBSTRING_INDEX(remark, ',', i), ',', -1)as "remark"  FROM `t`;
 SET i = i + 1;
 END WHILE;
  INSERT IGNORE INTO student_attendence( `date`,`student_id`, `attendence_type_id`,xiisada, `remark`,user_id,class_id) 
  
SELECT p_date,student_id,attendence_type,p_xiisada,remark,p_user_id,p_class
FROM   a
WHERE  NOT EXISTS (SELECT 1 
                   FROM   student_attendence 
                   WHERE  a.ID = student_attendence.ID and student_attendence.student_id=a.student_id) and a.attendence_type!=0 ;
                  
                  CREATE TEMPORARY table tmp as
                  SELECT concat('<b>Student</b>: ',GROUP_CONCAT(student_id SEPARATOR ','),' <b>Attendece</b> : ',(SELECT type from attendence_type  where id=attendence_type)) as attendence
FROM   a
WHERE  NOT EXISTS (SELECT 1 
                   FROM   student_attendence 
                   WHERE  a.ID = student_attendence.ID and student_attendence.student_id=a.student_id) and a.attendence_type!=0 Group by attendence_type;
                   
                  
                  INSERT INTO `logs`( `date`, `user`,role, `log`) 
SELECT CURRENT_TIMESTAMP(),p_user_id,@role,concat('<b>Date : </b>',p_date ,GROUP_CONCAT(attendence SEPARATOR '<br>')) from tmp;
                   


                   UPDATE student_attendence st
        INNER JOIN a a 
             ON a.id = st.id
SET st.attendence_type_id = a.attendence_type ,st.remark=a.remark,st.xiisada=p_xiisada;
DELETE FROM student_attendence WHERE attendence_type_id=0;
SELECT 'success|Attendence inserted successfully';


END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_student_behavior_marks` (IN `p_date` DATE, IN `p_exam_type` INT, IN `p_student_id` VARCHAR(100), IN `p_class` VARCHAR(100), IN `p_subject` INT, IN `p_marks` DECIMAL, IN `p_acedemic_year` INT, IN `p_user` INT)   BEGIN


if (p_subject!=0)THEN

 

SELECT ifnull(sum(bb.marks),0) into @reduction from student_behavior sb JOIN behavior bb on bb.id=sb.type where sb.student_id=p_student_id 
and sb.accy=p_acedemic_year and sb.exam=0;
            
            

 INSERT  INTO exam_marks( `student_id`, `class_id`, `academicyear`, `exam_id`, `subject_id`, `marks`, `date`, `user_id`, `company_id`) 
SELECT p_student_id,(SELECT ss.class_id FROM students ss where ss.student_id=p_student_id limit 1),p_acedemic_year,p_exam_type,p_subject,(p_marks-@reduction),p_date,p_user,1;
UPDATE  student_behavior sb set sb.exam=p_exam_type where sb.student_id=p_student_id 
and sb.accy=p_acedemic_year and sb.exam=0;
 END if;
                 
SELECT 'success| Student Marks inserted successfully';

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_student_behavior_marks1` (IN `p_date` DATE, IN `p_exam_type` INT, IN `p_student_id` VARCHAR(100), IN `p_class` INT, IN `p_subject` INT, IN `p_marks` DECIMAL, IN `p_acedemic_year` INT, IN `p_user` INT, IN `p_points` TEXT)   BEGIN
create table s( txt text );
insert into s values(p_points);

drop temporary table if exists temp_points;
create temporary table temp_points( val char(255) );
set @sql1 = concat("insert into temp_points (val) values ('", replace(( select group_concat(distinct txt) as data from s), ",", "'),('"),"');");
prepare stmt1 from @sql1;
execute stmt1;
if (p_subject!=0)THEN
 INSERT IGNORE INTO `student_exam_behavior_point`( `student_id`, `class`, `accy`, `exam`, `point`, `marks`, `date`) 
 SELECT p_student_id,(SELECT ss.class_id FROM students ss where ss.student_id=p_student_id limit 1),p_acedemic_year,p_exam_type,val,p_marks/10,p_date
FROM   temp_points;

            
            

 INSERT  INTO exam_marks( `student_id`, `class_id`, `academicyear`, `exam_id`, `subject_id`, `marks`, `date`, `user_id`, `company_id`) 
SELECT p_student_id,(SELECT ss.class_id FROM students ss where ss.student_id=p_student_id limit 1),p_acedemic_year,p_exam_type,p_subject,p_marks,p_date,p_user,1;
 END if;
                 
SELECT 'success| Student Marks inserted successfully';

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_student_charge` (IN `p_student_id` TEXT, IN `p_class` INT, IN `p_section` VARCHAR(10), IN `p_accyr` INT(50), IN `p_fee_type` INT, IN `p_chargeno` VARCHAR(100), IN `p_user_id` VARCHAR(50))  NO SQL BEGIN
DECLARE i int;
START TRANSACTION;

create  table t(student text);
insert into t values(p_student_id);

     drop  table  if exists a ;
create  table a(student text);
SELECT LENGTH(student) - LENGTH(REPLACE(student, ',', '')) + 1 into @student FROM `t` ;
set i=1;
WHILE i<=@student DO
insert into a(student)
SELECT  if(i>@student,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(student, ',', i), ',', -1))  FROM `t`;
 SET i = i + 1;
 END WHILE;

INSERT INTO `student_charge`( Chargeno,`student_id`, `class`, `accyr`, `fee_type`, `user_id`, `date`) 

SELECT  p_chargeno,student,(SELECT sp.to_class from student_promotion sp  where sp.student_id=student and sp.from_academic=p_accyr LIMIT 1),p_accyr,p_fee_type,p_user_id,CURRENT_DATE() from a;

UPDATE setupid set Number=Number+1 where id=11;

select 'success| waa la Charge gareeyay';
COMMIT;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_student_marks` (IN `p_id` TEXT, IN `p_date` DATE, IN `p_exam_type` INT, IN `p_student_id` TEXT, IN `p_class` INT, IN `p_subject` INT, IN `p_marks` TEXT, IN `p_acedemic_year` INT, IN `p_user` INT, IN `p_company` INT)   BEGIN
DECLARE i int;
create  table t(id text,student text,marks text);
insert into t values(p_id,p_student_id,p_marks);
        drop  table  if exists a ;
create  table a(id text,student text,marks text);
SELECT LENGTH(id) - LENGTH(REPLACE(id, ',', '')) + 1,LENGTH(student) - LENGTH(REPLACE(student, ',', '')) + 1,LENGTH(marks) - LENGTH(REPLACE(marks, ',', '')) + 1 into @id,@student,@marks FROM `t` ;
set i=1;
WHILE i<=@id DO
insert into a(id,student,marks)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(id, ',', i), ',', -1)as "id", if(i>@student,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(student, ',', i), ',', -1)) as "student",if(i>@marks,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(marks, ',', i), ',', -1)) as "marks" FROM `t`;
 SET i = i + 1;
 END WHILE;
 
 
 INSERT IGNORE INTO exam_marks( `student_id`, `class_id`, `academicyear`, `exam_id`, `subject_id`, `marks`, `date`, `user_id`, `company_id`) 
SELECT student,p_class,p_acedemic_year,p_exam_type,p_subject,marks,p_date,p_user,p_company
FROM   a
WHERE  NOT EXISTS (SELECT 1 
                   FROM   exam_marks 
                   WHERE  a.ID = exam_marks.ID);
                   UPDATE exam_marks em

        INNER JOIN a a 
             ON a.id = em.id
SET em.marks = a.marks ;
SELECT sb.subject_name into @subject_name  FROM subjects sb where sb.id=p_subject LIMIT 1; 
SELECT sb.type into @examtype  FROM exam_type sb where sb.id=p_exam_type LIMIT 1; 
SELECT concat(cl.class_name,'-',se.section) into @classsection  FROM class_sections cs JOIN classes cl on cl.id=cs.class_id JOIN sections se on se.id=cs.section_id  where cs.id=p_class LIMIT 1; 
SELECT GROUP_CONCAT(student SEPARATOR ',') into @Newstudents from a where id=0;
SELECT GROUP_CONCAT(student SEPARATOR ',') into @updatestudents from a where id!=0;
SELECT u.role_id into @role from user u where u.id=p_user;
if(@Newstudents!='')THEN
INSERT INTO `logs`( `date`, `user`,role, `log`) 
SELECT CURRENT_TIMESTAMP(),p_user,@role,CONCAT('<b>Class</b>:',@classsection, '  Waxaa luu Diiwaan galiyay <b>Maadada </b>:  ',@subject_name,'<b>    Exam</b>:   ',@examtype) ;
ENd if;
if(@updatestudents!='')THEN
INSERT INTO `logs`( `date`, `user`,role, `log`) 
SELECT  CURRENT_TIMESTAMP(),p_user,@role,CONCAT('<b>Class</b>: ',@classsection,'  Waxaa luu Update gareeyay <b>  Maadada:  </b>',@subject_name,'     <b>Exam</b>:  ',@examtype);
ENd if;

SELECT 'success| Student Marks inserted successfully';









END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_student_marks_import` (IN `p_date` DATE, IN `p_exam_type` INT, IN `p_student_id` TEXT, IN `p_class` INT, IN `p_subject` INT, IN `p_marks` TEXT, IN `p_acedemic_year` INT, IN `p_user` INT)   BEGIN
DECLARE i int;
create  table t(student text,marks text);
insert into t values(p_student_id,p_marks);
        drop  table  if exists a ;
create  table a(student text,marks text);
SELECT LENGTH(student) - LENGTH(REPLACE(student, ',', '')) + 1,LENGTH(marks) - LENGTH(REPLACE(marks, ',', '')) + 1 into @student,@marks FROM `t` ;
set i=1;
WHILE i<=@student DO
insert into a(student,marks)
SELECT if(i>@student,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(student, ',', i), ',', -1)) as "student",if(i>@marks,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(marks, ',', i), ',', -1)) as "marks" FROM `t`;
 SET i = i + 1;
 END WHILE;
 
 
 INSERT IGNORE INTO exam_marks( `student_id`, `class_id`, `academicyear`, `exam_id`, `subject_id`, `marks`, `date`, `user_id`) 
SELECT student,p_class,p_acedemic_year,p_exam_type,p_subject,marks,p_date,p_user
FROM   a
WHERE  NOT EXISTS (SELECT 1 
                   FROM   exam_marks 
                   WHERE  a.student = exam_marks.student_id AND exam_id=p_exam_type and exam_marks.academicyear=p_acedemic_year and exam_marks.class_id=p_class and exam_marks.subject_id=p_subject);
                   UPDATE exam_marks 
        INNER JOIN a a 
             ON  a.student = exam_marks.student_id AND exam_marks.exam_id=p_exam_type and exam_marks.academicyear=p_acedemic_year and exam_marks.class_id=p_class and exam_marks.subject_id=p_subject
SET exam_marks.marks = a.marks ;

SELECT sb.subject_name into @subject_name  FROM subjects sb where sb.id=p_subject LIMIT 1; 
SELECT sb.type into @examtype  FROM exam_type sb where sb.id=p_exam_type LIMIT 1; 
SELECT concat(cl.class_name,'-',se.section) into @classsection  FROM class_sections cs JOIN classes cl on cl.id=cs.class_id JOIN sections se on se.id=cs.section_id  where cs.id=p_class LIMIT 1; 
SELECT GROUP_CONCAT(student SEPARATOR ',') into @Newstudents from a WHERE  NOT EXISTS (SELECT 1 
                   FROM   exam_marks 
                   WHERE  a.student = exam_marks.student_id AND exam_id=p_exam_type and exam_marks.academicyear=p_acedemic_year and exam_marks.class_id=p_class and exam_marks.subject_id=p_subject);
SELECT GROUP_CONCAT(student SEPARATOR ',') into @updatestudents from a a      INNER JOIN exam_marks 
             ON  a.student = exam_marks.student_id AND exam_marks.exam_id=p_exam_type and exam_marks.academicyear=p_acedemic_year and exam_marks.class_id=p_class and exam_marks.subject_id=p_subject;
SELECT u.role_id into @role from user u where u.id=p_user;
if(@Newstudents!='')THEN
INSERT INTO `logs`( `date`, `user`,role, `log`) 
SELECT CURRENT_TIMESTAMP(),p_user,@role,CONCAT('<b>Class</b>:',@classsection,': <b>Imported  Students</b>:','waxaa luu Diiwaan galiyay <b>Maadada</b>:',@subject_name,'<b>Exam</b>:  ',@examtype) ;
ENd if;
if(@updatestudents!='')THEN
INSERT INTO `logs`( `date`, `user`,role, `log`) 
SELECT  CURRENT_TIMESTAMP(),p_user,@role,CONCAT('<b>Class</b>: ',@classsection,': <b>Imported Students</b>: ','		waxaa luu Update gareeyay <b>Maadada:  </b> ',@subject_name,'<b>Exam</b> : ',@examtype);
ENd if;

SELECT 'success| Student Marks inserted successfully';
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_subject` (IN `p_id` INT, IN `p_code` VARCHAR(50), IN `p_subject_name` VARCHAR(50), IN `p_description` VARCHAR(50), IN `p_user_id` INT, IN `p_company_id` INT)  NO SQL BEGIN
IF EXISTS(SELECT * FROM subjects where id = p_id) THEN
UPDATE subjects set `code` = p_code, subject_name=p_subject_name,Description =p_description where id = p_id;
SELECT 'success|Maadada waa la bedelay';
ELSEIF EXISTS(select * from subjects WHERE `code` = p_code and company_id =  p_company_id and subject_name =p_subject_name ) THEN
select 'warning|Maadadan hore ayaa loo diiwaangaliyey';
ELSE
INSERT INTO subjects(`code`,subject_name, Description, company_id, user_id) 
VALUES 
(p_code,p_subject_name,p_description,p_company_id, p_user_id) ;
select 'success|Maadada si sax ah ayaa loo diiwaangaliyey';
end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_subject_group` (IN `p_id` INT, IN `p_name` VARCHAR(50), IN `p_description` VARCHAR(200), IN `p_class` VARCHAR(500), IN `p_subject` VARCHAR(500), IN `p_session` INT, IN `p_academic_year` INT)   BEGIN

create table c( txt text );
insert into c values(p_class);

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

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 c), ",", "'),('"),"');");
prepare stmt1 from @sql1;
execute stmt1;

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 EXISTS(SELECT * from subject_groups where id=p_id)THEN
update subject_groups set name=p_name,description=p_description,acedemic_year =p_academic_year
where id=p_id;

set @subject_group=p_id;
ELSE
INSERT INTO subject_groups(`name`, `description`, `session_id`,acedemic_year) VALUES(p_name,p_description,p_session,p_academic_year);
SELECT LAST_INSERT_ID() into @subject_group;
END if;
DELETE from subject_group_subjects where subject_group_id=p_id AND subject_id NOT IN(SELECT val FROM temp_subjects);
INSERT INTO subject_group_subjects(`subject_group_id`, `session_id`, `subject_id`)
SELECT @subject_group,p_session,val FROM temp_subjects WHERE  NOT EXISTS (SELECT 1 
                   FROM   subject_group_subjects 
                   WHERE  temp_subjects.val = subject_group_subjects.subject_id and subject_group_subjects.subject_group_id=@subject_group);

DELETE from subject_group_class_sections where subject_group_id=p_id AND class_section_id NOT IN(SELECT val FROM temp_classes);
INSERT INTO subject_group_class_sections(`subject_group_id`, `class_section_id`, `session_id`, `description`)
SELECT @subject_group,val,p_session,p_description FROM temp_classes WHERE  NOT EXISTS (SELECT 1 
                   FROM   subject_group_class_sections 
                   WHERE  temp_classes.val = subject_group_class_sections.class_section_id and subject_group_class_sections.subject_group_id=@subject_group);
SELECT 'success| record saved successfully';
COMMIT;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_subtopic` (IN `p_lessonid` INT, IN `p_user_id` INT, IN `p_topic_id` TEXT, IN `p_topic` TEXT, IN `p_remark` TEXT)   BEGIN
DECLARE i int;
create  table t(id text,topic text,remark text);
insert into t values(p_topic_id,p_topic,p_remark);
        drop  table  if exists a ;
create  table a(id text,topic text,remark text);
SELECT LENGTH(id) - LENGTH(REPLACE(id, ',', '')) + 1,LENGTH(topic) - LENGTH(REPLACE(topic, ',', '')) + 1,LENGTH(remark) - LENGTH(REPLACE(remark, ',', '')) + 1 into @id,@topic,@remark FROM `t` ;
set i=1;
WHILE i<=@id DO
insert into a(id,topic,remark)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(id, ',', i), ',', -1), if(i>@topic,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(topic, ',', i), ',', -1)), if(i>@remark,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(remark, ',', i), ',', -1)) FROM `t`;
 SET i = i + 1;
 END WHILE;


DELETE 
FROM topicdetail where id not in (
 select id from a
   WHERE   a.id!=0) and lesson_id=p_lessonid;
   
INSERT INTO topicdetail (lesson_id, Topic, Reason)
SELECT p_lessonid, a.topic, a.remark
FROM a
WHERE a.id = 0;
UPDATE topicdetail l
JOIN a a ON l.id = a.id
SET l.Topic = a.topic, l.Reason = a.remark
WHERE a.id != 0;








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

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_teacher` (IN `p_id` INT, IN `p_teacher_name` VARCHAR(1000), IN `p_gender` VARCHAR(50), IN `p_mobile_1` VARCHAR(10), IN `p_mobile_2` VARCHAR(10), IN `p_address_1` VARCHAR(1000), IN `p_address_2` VARCHAR(1000), IN `p_Qualification` VARCHAR(1000), IN `p_Experience` VARCHAR(1000), IN `p_image` VARCHAR(1000), IN `p_user_id` INT, IN `p_company_id` INT)  NO SQL BEGIN
IF EXISTS(SELECT * FROM teachers WHERE id=p_id) THEN
if(p_image!='') then
update teachers  set image=p_image WHERE id = p_id;
end if;
UPDATE teachers set teacher_name = p_teacher_name,gender=p_gender,mobile=p_mobile_1,
address=p_address_1,Emergency_Contact=p_mobile_2,
other_address =p_address_2,Qualification =p_Qualification,Work_Experience =p_Experience
where id = p_id;
SELECT 'success|Xogta macalinka waa la bedelay';
ELSEIF EXISTS(SELECT * FROM teachers WHERE teacher_name = p_teacher_name and mobile=p_mobile_1 and company_id = p_company_id) THEN
select 'warning|Macalinka hore ayuu u jiray';

ELSE
if(p_image = '' ) THEN
     set p_image='images/No_image_available.png';
    END IF;
INSERT INTO teachers( teacher_name, gender, mobile, address, company_id,user_id,Emergency_Contact ,other_address ,Qualification ,Work_Experience,image ) values ( p_teacher_name, p_gender, p_mobile_1, p_address_1, p_company_id,p_user_id,p_mobile_2,p_address_2,
p_Qualification,p_Experience,p_image) ;
select 'success|Macalinka waa la diiwaangaliyey';
end if;
end$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_teacher_attendence` (IN `p_id` INT, IN `p_perriod` INT, IN `p_date` DATE, IN `p_teacher_id` VARCHAR(100), IN `p_attendence_type` INT, IN `p_remarks` VARCHAR(200))   BEGIN

IF EXISTS(SELECT * from teacher_attendence where id = p_id) THEN
UPDATE teacher_attendence set `attendence_type_id`=p_attendence_type,`remark`=p_remarks WHERE id = p_id;
SELECT 'success| Attendence updated successfully';
ELSE
INSERT IGNORE INTO teacher_attendence( date,teacher_id, attendence_type_id,period  ,remark) VALUES(p_date,p_teacher_id,p_attendence_type,p_perriod,p_remarks);
SELECT 'success|Attendence inserted successfully';
end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_timetables` (IN `p_id` INT, IN `p_class_id` INT, IN `p_subject_id` INT, IN `p_teacher_id` INT, IN `p_day_name` VARCHAR(50), IN `p_start_time` VARCHAR(50), IN `p_end_time` VARCHAR(50), IN `p_academic_year_id` INT)   BEGIN
START TRANSACTION;
IF EXISTS(SELECT * FROM teacher_subjects ts where ts.class_section_id = p_class_id and ts.subject_id =p_subject_id and ts.teacher_id = p_teacher_id) THEN
SELECT id into @teacher_subject_id FROM teacher_subjects ts where ts.class_section_id = p_class_id and ts.subject_id =p_subject_id and ts.teacher_id = p_teacher_id;
ELSE
INSERT IGNORE INTO teacher_subjects(`class_section_id`, `subject_id`, `teacher_id`, `academic_year`) VALUES(p_class_id,p_subject_id,p_teacher_id,p_academic_year_id);
SELECT LAST_INSERT_ID() into @teacher_subject_id;
END IF;
IF EXISTS(SELECT * FROM timetables WHERE id = p_id) THEN
UPDATE timetables tt SET  tt.teacher_subject_id = @teacher_subject_id, tt.start_time = p_start_time,tt.end_time =p_end_time WHERE tt.id = p_id;
ELSE
INSERT INTO `timetables`(`teacher_subject_id`, `day_name`, `start_time`, `end_time`)
VALUES(@teacher_subject_id,p_day_name,p_start_time,p_end_time);
END IF;
SELECT 'success| Record saved successfully';
COMMIT;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_topic` (IN `p_id` INT, IN `p_lesson_id` INT, IN `p_name` VARCHAR(200), IN `p_user_id` INT)   BEGIN
SELECT id INTO @academic_year FROM  academicyear a where a.is_active = 'yes';
IF EXISTS(SELECT * FROM topic where id = p_id ) THEN 
UPDATE topic set lesson_id=p_lesson_id,name = p_name,academic_year_id = @academic_year,updated_user = p_user_id WHERE id = p_id;
SELECT 'success|Topic updated successfully';
ELSEIF EXISTS(SELECT * FROM topic where lesson_id = p_lesson_id and name = p_name and academic_year_id=@academic_year) THEN
SELECT 'warning| Topic already inserted';
ELSE
INSERT INTO topic(lesson_id,name,academic_year_id,user_id) VALUES(p_lesson_id,p_name,@academic_year,p_user_id);
SELECT 'success| Topic registered successfully';
END IF;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_add_topics` (IN `p_id` INT, IN `p_academic_year_id` INT, IN `p_lesson_id` INT, IN `p_name` TEXT, IN `p_Objectives` TEXT, IN `p_Reference` TEXT, IN `p_Assignment` TEXT, IN `p_Teaching_Method` TEXT, IN `p_Practical` TEXT, IN `p_Practical_Detail` TEXT, IN `p_from_Date` DATE, IN `p_to_Date` DATE, IN `p_Remarks` TEXT, IN `p_user_id` INT)  NO SQL BEGIN
if EXISTS(SELECT * from topic  tp where tp.id=p_id)THEN
UPDATE topic tp set tp.lesson_id=p_lesson_id,tp.name=p_name,tp.academic_year_id=p_academic_year_id,
tp.Objectives=p_Objectives,tp.Reference=p_Reference,tp.Assignment=p_Assignment,
tp.Teaching_Method=p_Teaching_Method,tp.Practical=p_Practical,
tp.Practical_Detail=p_Practical_Detail,tp.from_Date=p_from_Date,tp.to_date=p_to_Date,
tp.Remarks=p_Remarks,tp.updated_user=p_user_id where tp.id=p_id;
SELECT 'success|Topic updated successfully';
ELSE
INSERT INTO `topic`( `lesson_id`, `name`, `academic_year_id`, `Objectives`, `Reference`, `Assignment`, `Teaching_Method`, `Practical`, `Practical_Detail`, `from_Date`,to_date, `Remarks`, `user_id`, `date`) VALUES
(p_lesson_id, p_name, p_academic_year_id, p_Objectives, p_Reference, p_Assignment, p_Teaching_Method,p_Practical, p_Practical_Detail, p_from_Date,p_to_Date, p_Remarks, p_user_id, CURRENT_DATE());
SELECT 'success| Topic registered successfully';
END if;
END$$

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

     drop  table  if exists a ;
create  table a(id text,sub_district 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)
SELECT  if(i>@id,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(id, ',', i), ',', -1)),
		if(i>@id,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(sub_district, ',', i), ',', -1))
        FROM `t`;
 SET i = i + 1;
 END WHILE;
 COMMIT;
 UPDATE IGNORE sub_district l join a on a.id=l.id set l.village=sub_district;
 DELETE from sub_district where id not in (SELECT id from a) and District=p_district;
 INSERT IGNORE sub_district(District,village,user_id)
 SELECT	 p_district,sub_district,p_user_id from a where id=0 AND sub_district!='';
 

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

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_applicants_list` (IN `user_id` INT)   BEGIN
SELECT a.id,a.`StudentName` 'Magaca',a.`Gender` 'Jinsiga',a.DOB 'Dhashay',a.POB 'Ku dhashay',a.Photo 'Sawirka',sp.sponsor_name 'Masuulka', sp.mobile as 'Mobile-ka Masuulka',
CONCAT('<a href="" data-href="../Form/frm_addApplicant.php" \r\ndata-table="applicants" data-id="',a.id,'" class="btn btn-xs update_link" ><i class="fas fa-edit"></i></a> ') `Edit` FROM applicants a
inner join sponsors sp on sp.id=a.sponsor_id
JOIN schools sc on a.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
where  a.status='0' GROUP BY a.studentName;


END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_applicants_list1` ()   BEGIN
SELECT a.id,a.`StudentName` 'Magaca',a.`Gender` 'Jinsiga',a.DOB 'Dhashay',a.POB 'Ku dhashay',a.Photo 'Sawirka',sp.sponsor_name 'Masuulka', sp.mobile as 'Mobile-ka Masuulka',
CONCAT('<a href="" data-href="../Form/frm_addApplicant.php" \r\ndata-table="applicants" data-id="',a.id,'" class="btn btn-xs update_link" ><i class="fas fa-edit"></i></a>') `Edit` FROM applicants a
inner join sponsors sp on sp.id=a.sponsor_id   where  a.status='0';

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_AssignClassToTeacher` (IN `p_class` INT, IN `p_accy` INT, IN `p_subject` TEXT, IN `p_section` TEXT, IN `p_teacher` TEXT, IN `p_user` INT)  NO SQL BEGIN
create  table tmp_assignclassteacher( subject text,section text,teacher text);
insert into tmp_assignclassteacher values(p_subject,p_section,p_teacher);

create  table temp_subject( id int PRIMARY key AUTO_INCREMENT,subject text );
set @sql1 = concat("insert into temp_subject (subject) values ('", replace(( select group_concat( subject) as data from tmp_assignclassteacher), "|", "'),('"),"');");

prepare stmt1 from @sql1;
execute stmt1;
DEALLOCATE PREPARE stmt1;



drop temporary table if exists temp_section;
create temporary table temp_section( id int PRIMARY key AUTO_INCREMENT,section text );
set @sql2 = concat("insert into temp_section (section) values ('", replace(( select group_concat(DISTINCT section) as data from tmp_assignclassteacher), "|", "'),('"),"');");

prepare stmt2 from @sql2;
execute stmt2;
DEALLOCATE PREPARE stmt2;


drop temporary table if exists temp_teacher;
create temporary table temp_teacher( id int PRIMARY key AUTO_INCREMENT,teacher text );
set @sql3 = concat("insert into temp_teacher (teacher) values ('", replace(( select group_concat( teacher) as data from tmp_assignclassteacher ), "|", "'),('"),"');");

prepare stmt3 from @sql3;
execute stmt3;
DEALLOCATE PREPARE stmt3;

drop temporary table if exists temp_subjects1;
create temporary table temp_subjects1(id int PRIMARY key AUTO_INCREMENT, subject char(255) );
set @sql4 = concat("insert into temp_subjects1 (subject) values ('", replace(( select group_concat( subject) as data from temp_subject ), ",", "'),('"),"');");
prepare stmt4 from @sql4;
execute stmt4;



 
    

CREATE TEMPORARY TABLE IF NOT EXISTS tmp AS (SELECT te.subject,tp.section,tr.teacher from temp_subjects1 te INNER join temp_section tp on tp.id=te.id inner JOIN temp_teacher tr on tr.id=tp.id and tr.id =te.id );
DELETE from assign_teacher_class where class=p_class;
INSERT IGNORE  INTO `assign_teacher_class`(`class`, `accy`, `teacher`, `subject`, `section`, `user_id`, `date`) 
SELECT DISTINCT p_class,p_accy,teacher,subject,section,p_user,CURRENT_DATE() FROM   tmp ;
 
SELECT 'success|Teacher Assigned  successfully';

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_cancelscholarship` (IN `p_id` INT, IN `p_comment` TEXT, IN `p_user` INT)   BEGIN
UPDATE scholarship sc set sc.status=0,canceldate=CURRENT_TIME(),sc.canceluser=p_user,
sc.comment=p_comment where id=p_id;
select'success| Data updated successfully ' as msg; 
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_cancel_receipt` (IN `p_rec_no` TEXT, IN `p_cancel_date` DATE, IN `p_cancel_reason` VARCHAR(100), IN `p_cancel_user` INT, IN `p_type` INT)  NO SQL BEGIN
DECLARE i int;
START TRANSACTION;

create  table t(RVNO text);
insert into t values(p_rec_no);

     drop  table  if exists a ;
create  table a(RVNO text);
SELECT LENGTH(RVNO) - LENGTH(REPLACE(RVNO, ',', '')) + 1 into @RVNO FROM `t` ;
set i=1;
WHILE i<=@RVNO DO
insert into a(RVNO)
SELECT  if(i>@RVNO,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(RVNO, ',', i), ',', -1))  FROM `t`;
 SET i = i + 1;
 END WHILE;
UPDATE cash_receipt cr  Set  `status`  = p_type ,cancel_date =p_cancel_date  ,cancel_reason = p_cancel_reason ,cancel_user =p_cancel_user where id in (SELECT RVNO from a);
if EXISTS(SELECT * FROM cash_receipt cr where cr.id in (SELECT RVNO from a) and cr.fee_type=1)THEN
    UPDATE students ss set ss.status=1 WHERE ss.student_id in (SELECT cr.student_id FROM cash_receipt cr where cr.id in (SELECT RVNO from a) and cr.fee_type=1)    ;   
  END if;        
SELECT 1 as success;

COMMIT;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_cancel_salary` (IN `p_id` INT)   BEGIN
    DELETE FROM salaryinfo  WHERE id = p_id;

    UPDATE salary_detail sd 
    SET sd.status = '1',
        sd.base_salary = '0.00',
        sd.net_salary = '0.00'
    WHERE sd.net_salary > 0 AND sd.salid = p_id;

    SELECT 'success| Generated month canceled successfully' AS msg;
END$$

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


SELECT @rownum:=@rownum+1 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', s.class_id FROM student_charge ssc
join student_promotion ssp on ssp.student_id=ssc.student_id and ssp.to_class=ssc.class and ssp.from_academic=ssc.accyr

join students s on s.student_id= ssc.student_id
JOIN class_sections cs on ssc.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 
 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 
inner join sponsors sp on sp.id=s.sponsor_id, (SELECT @rownum:=0) r where ssc.Chargeno=p_chargeno
ORDER by s.id ASC ;



END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_charged_search_by_class1` (IN `p_class_id` VARCHAR(50), IN `p_level` VARCHAR(50), IN `p_section` VARCHAR(50), IN `p_accyr` INT, IN `p_fee_type` INT, IN `p_user_id` INT)  NO SQL BEGIN
CREATE TEMPORARY TABLE view1 as
SELECT @rownum:=@rownum+1 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', 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
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, (SELECT @rownum:=0) r 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 ;

SELECT * from view1 where student_id  in(SELECT sc.student_id from student_charge sc where sc.class in (SELECT cs.id from class_sections cs where cs.class_id =p_class_id and cs.id like p_section )  and sc.accyr=p_accyr and sc.fee_type=p_fee_type);
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_charge_bus` (IN `p_user` INT, IN `p_month` INT)   BEGIN
INSERT IGNORE INTO charges(student_id, student_type,month, `year`, amount, user_id,date)

SELECT sb.student_id,sb.type,p_month,61,ll.amount-ifnull(ll.amount * sc.amount,0) as amount,p_user,CURRENT_TIME() from  studentbus sb 
JOIN buslocationinfo bli on bli.id=sb.Bus and sb.status=0 
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  and ll.accy=(SELECT ac.id from academicyear ac where ac.is_active='yes')
JOIN districts ds on ds.id=ll.District 

 

LEFT JOIN scholarship sc on sc.student_id = sb.student_id and sc.status=1 having amount > 0;

SELECT concat('success|',row_count() , ' Students have been charged') as msg;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_check_acedemic_year_fee` (IN `p_school_level` VARCHAR(100), IN `p_acedemic_year` INT, IN `p_fee` INT)  NO SQL BEGIN

SELECT sf.amount from school_fee sf where sf.school_id=p_school_level and sf.academidyear_id=p_acedemic_year and sf.fee_type_id=p_fee;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_check_list_fee_charge` (IN `p_student` VARCHAR(100), IN `p_class` INT, IN `p_fee_type` INT, IN `p_accyr` INT)  NO SQL BEGIN

SELECT COUNT(*) into @charged from student_charge sc where sc.class=p_class and sc.accyr=p_accyr and sc.fee_type=p_fee_type and sc.student_id=p_student;

if (@charged>0)THEN
SELECT  COUNT(*) into @paid from cash_receipt cr where cr.class_id=p_class and cr.academic_year=p_accyr and cr.fee_type=p_fee_type and cr.student_id=p_student and cr.status=1;
if (@paid=@charged)THEN 

SELECT	 1 as 'no';
ELSE

SELECT	 2 as 'no';
END if;
ELSE

SELECT	 0 as 'no';
END if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_check_prev_balance` (IN `p_student_id` VARCHAR(100))   BEGIN
select ac.Name,ss.student_id into @reg_academic,@student_id  FROM students ss JOIN academicyear ac on ss.reg_academic_year = ac.id where ss.student_id = p_student_id;
create TEMPORARY TABLE temp_prev_balance(
    prev_academic varchar(100), cur_academic varchar(100)
    );
   call sp_get_academic_year1();
    
    SELECT   prev_academic,cur_academic into @prev, @current  FROM temp_prev_balance;
   
   
    CREATE TEMPORARY TABLE temp_months
    (
    month varchar(50)
    );
    if(@Prev='2023-2024')THEN
    INSERT into temp_months
  
 SELECT months FROM months WHERE id  NOT IN (
SELECT cr.month FROM `cash_receipt` cr JOIN academicyear a on a.id = cr.academic_year  WHERE a.Name = @Prev AND cr.student_id =@student_id  and cr.status=1 and cr.fee_type=7); 
    ELSE
     INSERT into temp_months
  
 SELECT months FROM months WHERE id  NOT IN (
SELECT cr.month FROM `cash_receipt` cr JOIN academicyear a on a.id = cr.academic_year  WHERE a.Name = @Prev AND cr.student_id =@student_id  and cr.status=1 and cr.fee_type=7) AND id!=13;
    
    END if;

if(@reg_academic <> @current) then
if EXISTS(SELECT * from temp_months) THEN
SELECT concat('Sanad dugsiyeedka ', @Prev,' ayaa lacag looga leeyahay bilaha ', group_concat(month 	separator ',')) as `msg` FROM temp_months;
ELSE
SELECT 0 as   `msg`;
end if;
ELSE
SELECT 0 as   `msg`;
end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_check_prev_balance1` (IN `p_student_id` VARCHAR(100))  NO SQL BEGIN
select ac.Name,ss.student_id into @reg_academic,@student_id  FROM students ss JOIN academicyear ac on ss.reg_academic_year = ac.id where ss.student_id = p_student_id;
create TEMPORARY TABLE temp_prev_balance(
    prev_academic varchar(100), cur_academic varchar(100)
    );
   call sp_get_academic_year1();
    
    SELECT   prev_academic,cur_academic into @prev, @current  FROM temp_prev_balance;
    
  
    CREATE TEMPORARY TABLE temp_months
    (
    month varchar(50)
    );
INSERT into temp_months

 SELECT months FROM months WHERE months  NOT IN (
SELECT cr.month FROM `cash_receipt` cr JOIN academicyear a on a.id = cr.academic_year  WHERE a.Name = @Prev AND cr.student_id =@student_id  and cr.status=1 );


if(@reg_academic <> @current) then
if EXISTS(SELECT * from temp_months) THEN
SELECT concat('Sanad dugsiyeedka ', @prev,' ayaa lacag looga leeyahay bilaha ', group_concat(month 	separator ',')) as `msg` FROM temp_months;
ELSE
SELECT 0 as   `msg`;
end if;
ELSE
SELECT 0 as   `msg`;
end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_check_student_Fees_months` (IN `p_fee_type` INT, IN `p_school_id` INT, IN `p_Academic_Year` INT, IN `p_student_id` VARCHAR(100))   BEGIN
if (p_fee_type=7)THEN
SELECT cr.boono,
ifnull((SELECT sl.amount from class_sections cs 
        INNER join classes c on cs.class_id=c.id 
        INNER JOIN school_fee sl on sl.school_id=c.school_level_id and sl.academidyear_id=p_Academic_Year
        and sl.fee_type_id=p_fee_type
        INNER JOIN student_promotion sp on sp.to_class=cs.id and sp.from_academic=p_Academic_Year
        WHERE sl.fee_type_id=p_fee_type and mn.id BETWEEN sp.from_month and sp.to_month and sp.from_academic=p_Academic_Year and sp.student_id=p_student_id 
        
       )
,'disabled' )Bill,
ifnull((SELECT cs.id from class_sections cs 
        INNER join classes c on cs.class_id=c.id 
        INNER JOIN student_promotion sp on sp.to_class=cs.id and sp.from_academic=p_Academic_Year
        WHERE  mn.id BETWEEN sp.from_month and sp.to_month and sp.from_academic=p_Academic_Year and sp.student_id=p_student_id
   
       ),'0') as class_section,
ifnull(cr.month,mn.id) month_id,mn.months,
if(cr.month is NULL,'','disabled') 'type',
ifnull(cr.amount,0) as piad from months mn 
LEFT join cash_receipt cr on cr.month=mn.id and cr.student_id=p_student_id 
and cr.academic_year=p_Academic_Year and cr.fee_type=p_fee_type and cr.status=1;

ELSEif(p_fee_type=8)THEN


SELECT cr.boono,
ifnull((
        SELECT (ll.amount)  FROM  studentbus sb  
    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
    where  sb.accy=p_Academic_Year and sb.student_id=p_student_id  and mn.id BETWEEN sb.from_month 
    and sb.to_month LIMIT 1
              
       )
,'disabled' )Bill,
ifnull((SELECT cs.id from class_sections cs 
        INNER join classes c on cs.class_id=c.id 
        INNER JOIN student_promotion sp on sp.to_class=cs.id and sp.from_academic=p_Academic_Year
        WHERE  mn.id BETWEEN sp.from_month and sp.to_month and sp.from_academic=p_Academic_Year and sp.student_id=p_student_id
             LIMIT 1
       ),'0') as class_section,
ifnull(cr.month,mn.id) month_id,mn.months,
if(cr.month is NULL,'','disabled') 'type',
ifnull(cr.amount,0) as piad from months mn 
LEFT join cash_receipt cr on cr.month=mn.id and cr.student_id=p_student_id 
and cr.academic_year=p_Academic_Year and cr.fee_type=p_fee_type and cr.status=1;



ELSEif(p_fee_type=3)THEN

SELECT cr.boono,
ifnull((
        SELECT sl.amount  FROM  studentfood sb  
    JOIN student_promotion sp on sp.student_id=sb.student_id
  JOIN   class_sections cs  on cs.id=sp.to_class
        INNER join classes c on cs.class_id=c.id 
        INNER JOIN school_fee sl on sl.school_id=c.school_level_id and sl.academidyear_id=p_Academic_Year
        and sl.fee_type_id=p_fee_type
    where  sb.accy=p_Academic_Year and sb.student_id=p_student_id and mn.id BETWEEN sb.from_month 
    and sb.to_month and sl.fee_type_id=p_fee_type and sb.status=0
              
       )
,'disabled' )Bill,
ifnull((SELECT cs.id from class_sections cs 
        INNER join classes c on cs.class_id=c.id 
        INNER JOIN student_promotion sp on sp.to_class=cs.id and sp.from_academic=p_Academic_Year
        WHERE  mn.id BETWEEN sp.from_month and sp.to_month and sp.from_academic=p_Academic_Year and sp.student_id=p_student_id 
   
       ),'0') as class_section,
ifnull(cr.month,mn.id) month_id, mn.months,
if(cr.month is NULL,'','disabled') 'type',
ifnull(cr.amount,0) as piad from months mn 
LEFT join cash_receipt cr on cr.month=mn.id and cr.student_id=p_student_id 
and cr.academic_year=p_Academic_Year and cr.fee_type=p_fee_type and cr.status=1;
   

ELSE

SELECT cr.boono,
ifnull((
      SELECT sl.amount from class_sections cs 
        INNER join classes c on cs.class_id=c.id 
        INNER JOIN school_fee sl on sl.school_id=c.school_level_id and sl.academidyear_id=p_Academic_Year
        and sl.fee_type_id=p_fee_type 
        INNER JOIN student_promotion sp on sp.to_class=cs.id and sp.from_academic=p_Academic_Year
        WHERE sl.fee_type_id=p_fee_type and mn.id BETWEEN sp.from_month and sp.to_month and sp.from_academic=p_Academic_Year and sp.student_id=p_student_id 
              
       )
,'disabled' )Bill,
ifnull((SELECT cs.id from class_sections cs 
        INNER join classes c on cs.class_id=c.id 
        INNER JOIN student_promotion sp on sp.to_class=cs.id and sp.from_academic=p_Academic_Year
        WHERE  mn.id BETWEEN sp.from_month and sp.to_month and sp.from_academic=p_Academic_Year and sp.student_id=p_student_id 
   
       ),'0') as class_section,
ifnull(cr.month,mn.id) month_id,mn.months,
if(cr.month is NULL,'','disabled') 'type',
ifnull(cr.amount,0) as piad from months mn 
LEFT join cash_receipt cr on cr.month=mn.id and cr.student_id=p_student_id 
and cr.academic_year=p_Academic_Year and cr.fee_type=p_fee_type and cr.status=1;
     
END if;



END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_check_student_Fees_months1` (IN `p_fee_type` INT, IN `p_school_id` INT, IN `p_Academic_Year` INT, IN `p_student_id` VARCHAR(100))  NO SQL BEGIN

SELECT cr.boono,
ifnull((SELECT sl.amount from class_sections cs 
        INNER join classes c on cs.class_id=c.id 
        INNER JOIN school_fee sl on sl.school_id=c.school_level_id and sl.academidyear_id=p_Academic_Year
        and sl.fee_type_id=p_fee_type 
        INNER JOIN student_promotion sp on sp.to_class=cs.id and sp.from_academic=p_Academic_Year
        WHERE sl.fee_type_id=p_fee_type and mn.id BETWEEN sp.from_month and sp.to_month and sp.from_academic=p_Academic_Year and sp.student_id=p_student_id 
        
       )
,'disabled' )Bill,
ifnull((SELECT cs.id from class_sections cs 
        INNER join classes c on cs.class_id=c.id 
        INNER JOIN student_promotion sp on sp.to_class=cs.id and sp.from_academic=p_Academic_Year
        WHERE  mn.id BETWEEN sp.from_month and sp.to_month and sp.from_academic=p_Academic_Year and sp.student_id=p_student_id 
   
       ),'0') as class_section,
ifnull(cr.month,mn.months) months,
if(cr.month is NULL,'','disabled') 'type',
ifnull(cr.amount,0) as piad from months mn 
LEFT join cash_receipt cr on mn.months=cr.month and cr.student_id=p_student_id 
and cr.academic_year=p_Academic_Year and cr.fee_type=p_fee_type and cr.status=1;








 
 









END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_check_student_Fees_months2` (IN `p_fee_type` INT, IN `p_school_id` INT, IN `p_Academic_Year` INT, IN `p_student_id` VARCHAR(100))  NO SQL BEGIN

SELECT cr.boono,
ifnull((SELECT sl.amount from class_sections cs 
        INNER join classes c on cs.class_id=c.id 
        INNER JOIN school_fee sl on sl.school_id=c.school_level_id and sl.academidyear_id=p_Academic_Year
        and sl.fee_type_id=p_fee_type 
        INNER JOIN student_promotion sp on sp.to_class=cs.id and sp.from_academic=p_Academic_Year
        WHERE sl.fee_type_id=p_fee_type and mn.id BETWEEN sp.from_month and sp.to_month and sp.from_academic=p_Academic_Year and sp.student_id=p_student_id 
        
       )
,'disabled' )Bill,
ifnull((SELECT cs.id from class_sections cs 
        INNER join classes c on cs.class_id=c.id 
        INNER JOIN student_promotion sp on sp.to_class=cs.id and sp.from_academic=p_Academic_Year
        WHERE  mn.id BETWEEN sp.from_month and sp.to_month and sp.from_academic=p_Academic_Year and sp.student_id=p_student_id 
   
       ),'0') as class_section,
ifnull(cr.month,mn.months) months,
if(cr.month is NULL,'','disabled') 'type',
ifnull(cr.amount,0) as piad from months mn 
LEFT join cash_receipt cr on mn.months=cr.month and cr.student_id=p_student_id 
and cr.academic_year=p_Academic_Year and cr.fee_type=p_fee_type and cr.status=1;



END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_check_student_Fees_months3` (IN `p_fee_type` INT, IN `p_school_id` INT, IN `p_Academic_Year` INT, IN `p_student_id` VARCHAR(100))  NO SQL BEGIN
if (p_fee_type=7)THEN
SELECT cr.boono,
ifnull((SELECT sl.amount from class_sections cs 
        INNER join classes c on cs.class_id=c.id 
        INNER JOIN school_fee sl on sl.school_id=c.school_level_id and sl.academidyear_id=p_Academic_Year
        and sl.fee_type_id=p_fee_type 
        INNER JOIN student_promotion sp on sp.to_class=cs.id and sp.from_academic=p_Academic_Year
        WHERE sl.fee_type_id=p_fee_type and mn.id BETWEEN sp.from_month and sp.to_month and sp.from_academic=p_Academic_Year and sp.student_id=p_student_id 
        
       )
,'disabled' )Bill,
ifnull((SELECT cs.id from class_sections cs 
        INNER join classes c on cs.class_id=c.id 
        INNER JOIN student_promotion sp on sp.to_class=cs.id and sp.from_academic=p_Academic_Year
        WHERE  mn.id BETWEEN sp.from_month and sp.to_month and sp.from_academic=p_Academic_Year and sp.student_id=p_student_id 
   
       ),'0') as class_section,
ifnull(cr.month,mn.months) months,
if(cr.month is NULL,'','disabled') 'type',
ifnull(cr.amount,0) as piad from months mn 
LEFT join cash_receipt cr on mn.months=cr.month and cr.student_id=p_student_id 
and cr.academic_year=p_Academic_Year and cr.fee_type=p_fee_type and cr.status=1;
ELSEif(p_fee_type=8)THEN

SELECT cr.boono,
ifnull((
        SELECT (ll.amount)  FROM  studentbus sb  
    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
    where  sb.accy=p_Academic_Year and sb.student_id=p_student_id and mn.id BETWEEN sb.from_month 
    and sb.to_month
              
       )
,'disabled' )Bill,
ifnull((SELECT cs.id from class_sections cs 
        INNER join classes c on cs.class_id=c.id 
        INNER JOIN student_promotion sp on sp.to_class=cs.id and sp.from_academic=p_Academic_Year
        WHERE  mn.id BETWEEN sp.from_month and sp.to_month and sp.from_academic=p_Academic_Year and sp.student_id=p_student_id 
   
       ),'0') as class_section,
ifnull(cr.month,mn.months) months,
if(cr.month is NULL,'','disabled') 'type',
ifnull(cr.amount,0) as piad from months mn 
LEFT join cash_receipt cr on mn.months=cr.month and cr.student_id=p_student_id 
and cr.academic_year=p_Academic_Year and cr.fee_type=p_fee_type and cr.status=1;

ELSEif(p_fee_type=3)THEN

SELECT cr.boono,
ifnull((
        SELECT sl.amount  FROM  studentfood sb  
    JOIN student_promotion sp on sp.student_id=sb.student_id
  JOIN   class_sections cs  on cs.id=sp.to_class
        INNER join classes c on cs.class_id=c.id 
        INNER JOIN school_fee sl on sl.school_id=c.school_level_id and sl.academidyear_id=p_Academic_Year
        and sl.fee_type_id=p_fee_type 
    where  sb.accy=p_Academic_Year and sb.student_id=p_student_id and mn.id BETWEEN sb.from_month 
    and sb.to_month and sl.fee_type_id=p_fee_type and sb.status=0
              
       )
,'disabled' )Bill,
ifnull((SELECT cs.id from class_sections cs 
        INNER join classes c on cs.class_id=c.id 
        INNER JOIN student_promotion sp on sp.to_class=cs.id and sp.from_academic=p_Academic_Year
        WHERE  mn.id BETWEEN sp.from_month and sp.to_month and sp.from_academic=p_Academic_Year and sp.student_id=p_student_id 
   
       ),'0') as class_section,
ifnull(cr.month,mn.months) months,
if(cr.month is NULL,'','disabled') 'type',
ifnull(cr.amount,0) as piad from months mn 
LEFT join cash_receipt cr on mn.months=cr.month and cr.student_id=p_student_id 
and cr.academic_year=p_Academic_Year and cr.fee_type=p_fee_type and cr.status=1;
ELSE

SELECT cr.boono,
ifnull((
      SELECT sl.amount from class_sections cs 
        INNER join classes c on cs.class_id=c.id 
        INNER JOIN school_fee sl on sl.school_id=c.school_level_id and sl.academidyear_id=p_Academic_Year
        and sl.fee_type_id=p_fee_type 
        INNER JOIN student_promotion sp on sp.to_class=cs.id and sp.from_academic=p_Academic_Year
        WHERE sl.fee_type_id=p_fee_type and mn.id BETWEEN sp.from_month and sp.to_month and sp.from_academic=p_Academic_Year and sp.student_id=p_student_id 
              
       )
,'disabled' )Bill,
ifnull((SELECT cs.id from class_sections cs 
        INNER join classes c on cs.class_id=c.id 
        INNER JOIN student_promotion sp on sp.to_class=cs.id and sp.from_academic=p_Academic_Year
        WHERE  mn.id BETWEEN sp.from_month and sp.to_month and sp.from_academic=p_Academic_Year and sp.student_id=p_student_id 
   
       ),'0') as class_section,
ifnull(cr.month,mn.months) months,
if(cr.month is NULL,'','disabled') 'type',
ifnull(cr.amount,0) as piad from months mn 
LEFT join cash_receipt cr on mn.months=cr.month and cr.student_id=p_student_id 
and cr.academic_year=p_Academic_Year and cr.fee_type=p_fee_type and cr.status=1;
END if;



END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_company_permission` (IN `p_profile` INT, IN `p_sidebar` INT, IN `p_action` VARCHAR(50))  NO SQL BEGIN

if (p_action = 'grant') THEN
if not EXISTS(SELECT * from company_permission where sidebar_id=p_sidebar and profile=p_profile) THEN
insert into company_permission (sidebar_id,profile) values (p_sidebar,p_profile);
SELECT LAST_INSERT_ID() into @id;
INSERT INTO `roles_permissions` (`id`, `role_id`, `sidebar_id`, `can_view`, `can_add`, `can_edit`, `can_delete`, `created_at`) VALUES (NULL, '7',  @id, '1', '1', '1', '1', '2022-08-10 07:17:50'); 
end if;
else

delete from company_permission where  sidebar_id = p_sidebar and profile=p_profile;
DELETE from roles_permissions where sidebar_id=p_sidebar;

end if;


END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_create_result` (IN `p_id` INT, IN `p_result` VARCHAR(100), IN `p_result_id` TEXT, IN `p_exams` TEXT, IN `percentage` TEXT)  NO SQL BEGIN
create  table tmp_exam_result(id text,exams text,percentage text);
insert into tmp_exam_result values(p_result_id,p_exams,percentage);

drop temporary table if exists temp_exams;
create temporary table temp_exams( id int PRIMARY key AUTO_INCREMENT,exams text );
set @sql1 = concat("insert into temp_exams (exams) values ('", replace(( select group_concat(distinct exams) as data from tmp_exam_result), "|", "'),('"),"');");

prepare stmt1 from @sql1;
execute stmt1;
DEALLOCATE PREPARE stmt1;

drop temporary table if exists temp_percentage;
create temporary table temp_percentage( id int PRIMARY key AUTO_INCREMENT,percentage text );
set @sql2 = concat("insert into temp_percentage (percentage) values ('", replace(( select group_concat(distinct percentage) as data from tmp_exam_result), "|", "'),('"),"');");

prepare stmt2 from @sql2;
execute stmt2;
DEALLOCATE PREPARE stmt2;


drop temporary table if exists temp_result_id;
create temporary table temp_result_id( id int PRIMARY key AUTO_INCREMENT,result_id text );
set @sql3 = concat("insert into temp_result_id (result_id) values ('", replace(( select group_concat(distinct id) as data from tmp_exam_result), "|", "'),('"),"');");



prepare stmt3 from @sql3;
execute stmt3;
DEALLOCATE PREPARE stmt3;

CREATE TEMPORARY TABLE IF NOT EXISTS tmp AS (
SELECT te.id,te.exams,tp.percentage,tr.result_id from temp_exams te INNER join temp_percentage tp on tp.id=te.id inner JOIN temp_result_id tr on tr.id=tp.id and tr.id =te.id

);
if not EXISTS (select * from exam_create_result where id=p_id)THEN
insert  into  exam_create_result (Name)VALUES(p_result);
set @result_id=(SELECT id from exam_create_result where Name=p_result);


ELSE
update exam_create_result set Name=p_result where id=p_id;
set @result_id=p_id;
END IF;
DELETE from exam_result_calculation where result_id=@result_id and id not in (SELECT result_id from tmp );
UPDATE exam_result_calculation t1 
        INNER JOIN tmp t2 
             ON t1.id = t2.result_id
SET t1.Exams = t2.exams, t1.percentage=t2.percentage;
insert into exam_result_calculation (result_id,Exams,percentage)
SELECT @result_id,t.exams,t.percentage from tmp t where result_id=0;
SELECT 'success| record saved successfully';
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_dashboard` (IN `p_role` INT, IN `p_company` INT)   BEGIN
SELECT  s.`id` as `sidebar`, s.`text`,concat( '../../',s.`href`) as href,dashboard_link.icon,dashboard_link.color  FROM sidebar s inner join company_permission cp on s.id=cp.sidebar_id 
join roles_permissions rp on rp.sidebar_id=cp.id 
join dashboard_link  on  s.id=dashboard_link.sidebar
where  
 cp.profile=p_company  AND rp.role_id=p_role ORDER BY dashboard_link.id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_deleteBehaviorMARKS` (IN `p_class` INT(100), IN `p_section` VARCHAR(100), IN `p_accy` INT, IN `p_exam` INT)   BEGIN
    
    SELECT GROUP_CONCAT(cs.id SEPARATOR ',') INTO @classess 
    FROM class_sections cs 
    WHERE cs.class_id = p_class AND cs.id LIKE p_section;

    
    UPDATE student_behavior sb 
    SET sb.exam = 0 
    WHERE FIND_IN_SET(sb.class_id, @classess) AND sb.exam = p_exam AND sb.accy = p_accy;

    
    DELETE FROM exam_marks  
    WHERE FIND_IN_SET(class_id, @classess) AND academicyear = p_accy AND exam_id = p_exam AND subject_id = 24;
END$$

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

DELETE FROM expensebill  WHERE id=p_id;

END$$

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

delete FROM incomereciept WHERE id=p_id;

END$$

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

DELETE FROM applicants WHERE id=p_id;

END$$

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

DELETE FROM behavior_type WHERE id=p_id;

END$$

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

DELETE FROM driver WHERE id=p_id;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_delete_employee` (IN `p_student_id` TEXT)  NO SQL BEGIN
create table t( txt text );
insert into t values(p_student_id);

drop temporary table if exists temp_student;
create temporary table temp_student( val char(255) );
set @sql1 = concat("insert into temp_student (val) values ('", replace(( select group_concat(distinct txt) as data from t), ",", "'),('"),"');");
prepare stmt1 from @sql1;
execute stmt1;
insert into deleted_employee
select * from employee where id in (select val from temp_student );
DELETE  from employee where id in (select val from temp_student );










  END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_delete_exam_class` (IN `p_class` INT, IN `p_exam` INT, IN `p_accy` INT)   BEGIN
if EXISTS( SELECT * from exam_marks em where em.class_id=p_class and em.academicyear=p_accy and em.exam_id=p_exam)THEN
SELECT 'warning| This Exam has already in Student Marks';
ELSEIF EXISTS (SELECT * from exam_result_class er 
INNER JOIN exam_result_calculation erc on er.id=erc.result_id where er.class=p_class and er.academic_year=p_accy
GROUP by er.class,er.academic_year  HAVING FIND_IN_SET(p_exam,GROUP_CONCAT(Exams SEPARATOR ',')))THEN
SELECT 'warning| This Exam has already in Class Result View';
ELSE
SELECT id into @exam_info from exam_class_info eci WHERE eci.acedemic_year= p_accy and eci.exam_type_id=p_exam and eci.class_id =p_class;

DELETE from exam_class_info  WHERE acedemic_year= p_accy and exam_type_id=p_exam and class_id =p_class;

DELETE from exam_class  where exam_info=@exam_info;
SELECT 'success| This Exam Deleted';

END if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_delete_exam_marks` (IN `p_class_section` INT, IN `p_subject` INT, IN `p_academic_year` INT, IN `p_exam` INT)  NO SQL BEGIN

if NOT EXISTS (SELECT * FROM  exam_marks e  where e.academicyear=p_academic_year and 
  e.exam_id=p_exam and e.class_id =p_class_section and e.class_id=p_class_section and status=0)THEN
  
   SELECT 'warning|This class has no exam';
  ELSE
  update exam_marks e set status=1 where e.academicyear=p_academic_year and 
  e.exam_id=p_exam and e.class_id =p_class_section and e.class_id=p_class_section and status=0;
  SELECT 'success|This Exam has been successfully deleted';
  END IF;
  END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_delete_lesson` (IN `p_academic_year` INT, IN `p_class` INT, IN `p_subject` INT)  NO SQL BEGIN

SELECT GROUP_CONCAT( DISTINCT id  SEPARATOR ',') into @lesson_id  FROM lesson L
 where subject_group_subject_id=p_subject and subject_group_class_sections_id=p_class and academic_year_id=p_academic_year GROUP by subject_group_subject_id,academic_year_id ,subject_group_class_sections_id;
 DELETE from topic where lesson_id in ( @lesson_id);
DELETE FROM lesson 
 where subject_group_subject_id=p_subject and subject_group_class_sections_id=p_class and academic_year_id=p_academic_year;
 DELETE FROM lesson_teacher WHERE subject_id=p_subject  AND accy=p_academic_year AND class_id=p_class; 
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_delete_ResultView` (IN `p_id` INT)   BEGIN
DELETE from exam_result_class_subject where exam_result_class_info=p_id;
DELETE from exam_result_class where id=p_id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_delete_schoolfee` (IN `p_id` INT)   Begin


delete FROM school_fee WHERE id=p_id;


END$$

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

DELETE FROM sponsors where id=p_id;



END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_delete_student` (IN `p_student_id` TEXT)  NO SQL BEGIN
create table t( txt text );
insert into t values(p_student_id);

drop temporary table if exists temp_student;
create temporary table temp_student( val char(255) );
set @sql1 = concat("insert into temp_student (val) values ('", replace(( select group_concat(distinct txt) as data from t), ",", "'),('"),"');");
prepare stmt1 from @sql1;
execute stmt1;
insert into deleted_students
select * from students where student_id in (select val from temp_student );
DELETE  from students where student_id in (select val from temp_student );










  END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_delete_student_behavior` (IN `p_id` INT)   Begin


delete FROM student_behavior WHERE id=p_id;


END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_delete_subject_group` (IN `p_id` INT)  NO SQL BEGIN
SELECT  ifnull(GROUP_CONCAT( DISTINCT CONCAT('''', cs.ID,'''') ORDER BY cs.id  SEPARATOR ' , '),'') 'Class',a.id 'Acedemic Year' into @class,@academic_year  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 AND SG.id=p_id
GROUP by sg.id;
if EXISTS (SELECT * FROM exam_class_info e  where e.acedemic_year=@academic_year  and e.class_id in (@class))THEN
  
   SELECT 'warning|You can\'t delete this row';
  ELSE
  update subject_groups e set E.status=1 where id=p_id;
  SELECT 'success|This row has been successfully deleted';
  END IF;
  END$$

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

DELETE FROM behavior  WHERE id=p_id;


END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_delete_topic` (IN `p_id` INT)   BEGIN
DELETE from topic where id=p_id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_document` (IN `P_document_id` TEXT, IN `p_document` TEXT, IN `p_document_name` TEXT)  NO SQL BEGIN
create table a( document_id text);
insert into a values(p_document_id);

create table b( documents text );
insert into b values(p_document);

create table c (  document_name text );
insert into c values(p_document_name);

drop temporary table if exists document_id;
create temporary table document_id(id int PRIMARY KEY AUTO_INCREMENT, val varchar(1000) );
set @sql1 = concat("insert into document_id (val) values ('", replace(( select group_concat(distinct document_id) as data from a), ",", "'),('"),"');");
prepare stmt1 from @sql1;
execute stmt1;


drop temporary table if exists documents;
create temporary table documents(id int PRIMARY KEY AUTO_INCREMENT, val varchar(1000) );
set @sql2 = concat("insert into documents (val) values ('", replace(( select group_concat(distinct documents) as data from b), ",", "'),('"),"');");
prepare stmt2 from @sql2;
execute stmt2;


drop temporary table if exists document_name;
create temporary table document_name(id int PRIMARY KEY AUTO_INCREMENT, val varchar(1000) );
set @sql3 = concat("insert into document_name (val) values ('", replace(( select group_concat(distinct document_name) as data from c), ",", "'),('"),"');");
prepare stmt3 from @sql3;
execute stmt3;
create TEMPORARY TABLE table2 
SELECT di.val 'id',d.val 'document',dn.val 'name' FROM document_id di INNER join documents d on d.id=di.id INNER join document_name dn on dn.id=di.id;

SELECT * from table2;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_edit_receipt` (IN `p_rec_no` TEXT, IN `p_tran_date` DATE, IN `p_amount` TEXT, IN `p_month` TEXT, IN `p_description` TEXT, IN `p_user_id` INT)  NO SQL BEGIN

DECLARE i int;
START TRANSACTION;

create  table t(RVNO text,amount text);
insert into t values(p_rec_no,p_amount);

     drop  table  if exists a ;
create  table a(RVNO text,amount text);
SELECT LENGTH(RVNO) - LENGTH(REPLACE(RVNO, ',', '')) + 1 into @RVNO FROM `t` ;
set i=1;
WHILE i<=@RVNO DO
insert into a(RVNO,amount)
SELECT  if(i>@RVNO,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(RVNO, ',', i), ',', -1)),if(i>@RVNO,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(amount, ',', i), ',', -1))  FROM `t`;
 SET i = i + 1;
 END WHILE;
UPDATE cash_receipt cr JOIN a on a.RVNO=cr.id set tran_date=p_tran_date,cr.amount=a.amount,edited=p_user_id ;
SELECT 1 as success;

COMMIT;
end$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_edit_student_exam_behavior` (IN `p_id` TEXT, IN `p_exam_type` INT, IN `p_student_id` VARCHAR(100), IN `p_class` INT, IN `p_subject` INT, IN `p_marks` TEXT, IN `p_acedemic_year` INT, IN `p_user` INT)   BEGIN
DECLARE i int;
create  table t(id  text,marks text);
insert into t values(p_id,p_marks);
        drop  table  if exists a ;
create  table a(id text,marks text);
SELECT LENGTH(id) - LENGTH(REPLACE(id, ',', '')) + 1,LENGTH(marks) - LENGTH(REPLACE(marks, ',', '')) + 1 into @id,@marks FROM `t` ;
set i=1;
WHILE i<=@id DO
insert into a(id,marks)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(id, ',', i), ',', -1)as "id", if(i>@marks,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(marks, ',', i), ',', -1)) as "marks" FROM `t`;
 SET i = i + 1;
 END WHILE; 
          UPDATE student_exam_behavior_point em
        INNER JOIN a a 
             ON a.id = em.id
SET em.marks = a.marks ;
UPDATE exam_marks em set em.marks=(SELECT sum(marks) from student_exam_behavior_point where id in (SELECT id from a) )where em.student_id=p_student_id and em.class_id=p_class and em.academicyear=p_acedemic_year and em.exam_id=p_exam_type and em.subject_id=24;

SELECT 'success| Student Marks inserted successfully';









END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_employee` (IN `p_id` INT, IN `p_school` VARCHAR(100), IN `p_name` VARCHAR(100), IN `p_gender` VARCHAR(100), IN `p_cities` INT, IN `p_dob` DATE, IN `p_tell_1` VARCHAR(100), IN `p_tell_2` VARCHAR(100), IN `p_address_1` VARCHAR(100), IN `p_address_2` VARCHAR(100), IN `p_email` VARCHAR(100), IN `p_mother` VARCHAR(100), IN `p_martial_status` VARCHAR(100), IN `p_Aqoonsiga` VARCHAR(100), IN `p_jinsiyada_aqoonsiga` VARCHAR(100), IN `p_akoon_bangi` VARCHAR(100), IN `p_lambar` VARCHAR(100), IN `p_shaqada` INT, IN `p_qeybta` INT, IN `p_galinka` VARCHAR(100), IN `p_mushaar` VARCHAR(100), IN `p_lacagta` VARCHAR(50), IN `p_date_of_Issue` DATE, IN `p_date_of_Starting` DATE, IN `p_masul1` VARCHAR(50), IN `p_xiriir1` VARCHAR(100), IN `p_masul2` VARCHAR(50), IN `p_xiriir2` VARCHAR(100), IN `p_aqoonta` INT, IN `p_goobta` VARCHAR(100), IN `p_kuliyada` VARCHAR(100), IN `p_takhasuska` VARCHAR(100), IN `p_magaca` VARCHAR(100), IN `p_image` VARCHAR(1000), IN `p_document_id` VARCHAR(1000), IN `document_name` VARCHAR(1000), IN `p_document` TEXT, IN `p_status` INT, IN `p_user` INT, IN `p_role_id` INT, IN `p_profile` INT)  NO SQL BEGIN
DECLARE i INT DEFAULT 0;
if EXISTS (SELECT * from employee where id=p_id)THEN
UPDATE employee set 
`name`=p_name, `gender` =p_gender, `cities`=p_cities, `dob`=p_dob, `tell_1`=p_tell_1
, `tell_2`=p_tell_2, `address_1`=p_address_1
, `address_2` =p_address_2, `email` =p_email, `mother_name`= p_mother, `martial_status` =p_martial_status, `aqoonsi` =p_Aqoonsiga, `jinsiyada_aqoonsiga` =p_jinsiyada_aqoonsiga, `akoon_bangi`=p_akoon_bangi, `lambarka`=p_lambar, `shaqada` =p_shaqada, `qeybta` =p_qeybta, `galinka`=p_galinka, `mushaar`= p_mushaar, `lacagta`= p_lacagta, `Date_of_Issue` =p_date_of_Issue, `Date_of_Starting` =p_date_of_Starting, `masul_1` =p_masul1, `xiriirka_1`=p_xiriir1
, `masul_2` =p_masul2, `xiriirka_2`=p_xiriir2,`aqoonta` =p_aqoonta, `goobta` =p_goobta, `kuliyada` =p_kuliyada, `takhasuska` =p_takhasuska, magaca=p_magaca,image=p_image,status=p_status WHERE id=p_id;
UPDATE user u set u.status=p_status WHERE u.employee_id=p_id;
set @emp=p_id;
select 'success|Si sax ah ayaa loo badalay';
ELSE
INSERT INTO `employee`(`school`, `name`, `gender`, `cities`, `dob`, `tell_1`, `tell_2`, `address_1`, `address_2`, `email`, `mother_name`, `martial_status`, `aqoonsi`, `jinsiyada_aqoonsiga`, `akoon_bangi`, `lambarka`, `shaqada`, `qeybta`, `galinka`, `mushaar`, `lacagta`, `Date_of_Issue`, `Date_of_Starting`, `masul_1`, `xiriirka_1`, `masul_2`, `xiriirka_2`,`aqoonta`, `goobta`, `kuliyada`, `takhasuska`, magaca, `p_user`,image) 
VALUES
( p_school, p_name ,  p_gender ,  p_cities ,  p_dob ,  p_tell_1 ,  p_tell_2 ,  p_address_1 ,  p_address_2 ,  p_email ,  p_mother ,  p_martial_status ,  p_Aqoonsiga ,  p_jinsiyada_aqoonsiga ,  p_akoon_bangi,p_lambar ,  p_shaqada ,  p_qeybta ,  p_galinka ,  p_mushaar  ,  p_lacagta ,  p_date_of_Issue ,  p_date_of_Starting ,  p_masul1 ,  p_xiriir1 ,  p_masul2 ,  p_xiriir2 ,  p_aqoonta ,  p_goobta ,  p_kuliyada  ,  p_takhasuska  ,  p_magaca ,  p_user,p_image );

SELECT LAST_INSERT_ID() into @emp; 
        
select 'success|Si sax ah ayaa loo xareeyey';
END if;
create  table t(id text,name text,document text);
insert into t values(p_document_id,document_name,p_document);
        drop  table  if exists a ;
create  table a(id text,name text,document TEXT);
SELECT LENGTH(id) - LENGTH(REPLACE(id, ',', '')) + 1,LENGTH(name) - LENGTH(REPLACE(name, ',', '')) + 1,LENGTH(document) - LENGTH(REPLACE(document, ',', '')) + 1 into @id,@name,@document FROM `t` ;
set i=1;
WHILE i<=@id DO
insert into a(id,name,document)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(id, ',', i), ',', -1)as "id", if(i>@name,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(name, ',', i), ',', -1)) as "name",if(i>@document,Null,concat('Images/',SUBSTRING_INDEX(SUBSTRING_INDEX(document, ',', i), ',', -1)) )as "document" FROM `t`;
 SET i = i + 1;
 END WHILE;
  INSERT into employee_document(`Emp_id`, `Document_name`, `Document`, `date`, `user_id`)
SELECT @emp,name,document,p_date_of_Issue,p_user
FROM   a
WHERE  NOT EXISTS (SELECT 1 
                   FROM   employee_document 
                   WHERE  a.ID = employee_document.ID and employee_document.Emp_id=@emp);
                   UPDATE employee_document emp
        INNER JOIN a a 
             ON a.id = emp.id
SET emp.Document_name = a.name ,emp.Document=a.document;
if NOT EXISTS(SELECT * from user where employee_id=@emp)THEN
INSERT INTO `user`(`profile`, `employee_id`, `role_id`, `user_name`, `password`, `status`, `date`) 
SELECT p_profile,@emp,p_role_id,concat('T-',LPAD(@emp, 6, '0')),LPAD(FLOOR(RAND() * 99999999.99), 8, '0'),1,p_date_of_Starting  ;
ELSE
update user set role_id=p_role_id where employee_id=@emp;
              END if;
END$$

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

DELETE FROM employee WHERE id=p_id;



END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_enroll` (IN `p_id` INT, IN `p_class` INT)  NO SQL BEGIN
START TRANSACTION; 
SET @student_id=(SELECT s.Number+1 FROM setupid s where s.Name = "Students");
SET @current=(SELECT a.id FROM academicyear a where a.is_active="yes");
SELECT cl.type into @type from classes cl where cl.id=p_class;
SELECT user_id into @user_id from applicants ap where ap.id=p_id;

INSERT INTO students (`student_id`,`student_name`, `Gender`, `DOB`, `POB`, `Address`, `Phone`, `mother_name`, `Fee_Status`, `discount`, `Reg_academic_year`,`Photo`, `status`, `user_id`, `sponsor_id`,`relation`,`class_id`,current_academic ) 
 SELECT concat('TY-',@student_id),`StudentName`, `Gender`, `DOB`, `POB`, `Address`, `Phone`, `mothername`, `Fee_Status`, `discount`, `RegAcademicYear`, `Photo`, 2, `user_id`, `sponsor_id`,`relation`,p_class,@current FROM applicants where id = p_id;

insert into student_status_change(`student_id`, `to_status`, `detail`, `date`, `user_id`)
 SELECT concat('TY-',@student_id),2,'Entrolled' ,date(now()),`user_id` FROM applicants where id = p_id;
if(@type='Normal')THEN
INSERT INTO `student_promotion`( `student_id`, `from_class`, `from_academic`, `to_class`, `from_month`, `to_month`, `user_id`, `date`)
VALUES(concat('TY-',@student_id),0,@current,p_class,1,12,@user_id,date(now()));
ELSE

INSERT INTO `student_promotion`( `student_id`, `from_class`, `from_academic`, `to_class`, `from_month`, `to_month`, `user_id`, `date`)
VALUES(concat('TY-',@student_id),0,@current,p_class,1,6,@user_id,date(now()));
END if;
 UPDATE setupid set Number = Number+1 where Name = "Students";
 UPDATE applicants set status =1 where id = p_id;
COMMIT;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_exam_permission` (IN `p_user` INT, IN `p_accy` INT, IN `p_exam` INT, IN `p_action` VARCHAR(15))   BEGIN

if (p_action = 'grant') THEN
if not EXISTS(SELECT * from exam_permission ep where ep.exam=p_exam and ep.accy=p_accy and ep.user_id=p_user) then
insert into exam_permission (exam,accy,user_id) values (p_exam,p_accy,p_user);
SELECT 'insert';
END if;
else
delete from exam_permission  where exam=p_exam and accy=p_accy and user_id=p_user;
SELECT 'deleted';
end if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_fetch_dashboard` ()   BEGIN
SELECT ac.id into @current from academicyear ac where ac.is_active='yes';
SELECT COUNT(ss.id) into @Enrolled from students ss where ss.status=2;
SELECT COUNT(ss.id) into @Dropped from students ss where ss.status=3 and ss.current_academic=@current;
SELECT COUNT(ss.id) into @Graduated from students ss where ss.status=4 and ss.current_academic=@current;
SELECT COUNT(ss.id) into @Admitted from students ss where  ss.reg_academic_year=@current;
SELECT @Admitted as Admitted,@Enrolled as Enrolled, @Dropped as Dropped,@Graduated as Graduated;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_fetch_edit_finance` (IN `p_user` INT, IN `p_invoice` VARCHAR(100), IN `p_fee` VARCHAR(100))  NO SQL BEGIN
CREATE TEMPORARY TABLE tmp as
SELECT cr.id as 'RVNo',ss.student_id,ss.student_name,cr.boono,ft.fee_name as month,cr.amount, concat(sc.school_name,'-',l.name) school,c.class_name as "Fasalka",ft.id as fee_type,cr.usd_no,cr.receipt_by  from cash_receipt cr INNER JOIN students ss on ss.student_id=cr.student_id and cr.status=1 join fee_type ft on ft.id=cr.fee_type and ft.id=9  
join classes c on cr.class_id = c.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
UNION 
SELECT cr.id,ss.student_id,ss.student_name,cr.boono,(case when ft.id=7 then mn.months else ft.fee_name END) as month,cr.amount, concat(sc.school_name,'-',l.name) school,concat(c.class_name,' ',se.section) as "Fasalka",ft.id as fee_type,cr.usd_no,cr.receipt_by from cash_receipt cr INNER JOIN students ss on ss.student_id=cr.student_id and cr.status=1 join fee_type ft on ft.id=cr.fee_type and ft.id!=9 JOIN months mn ON cr.month=mn.id 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.boono = p_invoice and ft.id like p_fee;
SELECT * from tmp ORDER by student_id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_fetch_exam_result_calculation` (IN `p_result` INT)   SELECT e.id 'result_id',e.Exams 'exams',e.percentage 'Percentage' from exam_result_calculation e where e.result_id=p_result order by id DESC$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_fetch_lesson_update` (IN `p_academic_year` INT, IN `p_class` INT, IN `p_subject` INT)  NO SQL BEGIN
SELECT DISTINCT l.subject_group_class_sections_id,l.subject_group_subject_id,sl.id as level_id,l.academic_year_id,sl.school_id,sg.id,GROUP_CONCAT( DISTINCT lt.teacher_id  SEPARATOR ',') as teacher_id FROM lesson l 
left JOIN lesson_teacher lt on lt.subject_id=l.subject_group_subject_id and lt.accy =l.academic_year_id and lt.class_id=subject_group_class_sections_id
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
INNER JOIN school_levels sl on sl.id=c.school_level_id where l.subject_group_subject_id=p_subject and l.subject_group_class_sections_id=p_class and l.academic_year_id=p_academic_year
GROUP by lt.subject_id,lt.accy ,lt.class_id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_fetch_students` (IN `p_id` INT)  NO SQL BEGIN

SELECT s.id,s.StudentID,`StudentName`,s.`Gender`,s.Phone,c.id as class_id,sp.id as sponsor_id ,s.discount,s.Fee_Status,s.POB,s.mothername,s.Address,s.TransferedAcademicYear,s.RegAcademicYear,s.SchoolName,s.SchoolAddress,s.Ambarella,s.CompletedClass,s.TypeOfDeclaration,s.Date_of_issue,c.school_id,s.Photo,s.status,s.DOB,s.relation FROM students s join classes c on s.class_id = c.id join schools sc on sc.id = c.school_id join levels l on l.id = sc.level_id inner join sponsors sp on sp.id=s.sponsor_id 
where s.id=p_id ;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_fetch_student_attendence` (IN `p_date` VARCHAR(100), IN `p_class` VARCHAR(100), IN `p_xiisada` VARCHAR(100))  NO SQL BEGIN
SELECT ifnull(sa.id,0) as attendence_id,ifnull(sa.date,p_date) as date,s.student_id as student_id,s.student_name,ss.mobile,ifnull(sa.attendence_type_id,0) as attendence_type_id,ifnull(sa.remark,'') as remark FROM students s
                        inner join sponsors ss on ss.id=s.sponsor_id
                         LEFT JOIN student_attendence sa on sa.student_id = s.student_id and sa.date=p_date and xiisada=p_xiisada  where s.class_id =p_class and s.status  in (1,2);
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_find_busunpaid` (IN `p_month` TEXT, IN `p_accy` INT, IN `p_bus` INT, IN `p_state` VARCHAR(100), IN `p_user` INT)   BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;

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

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

create  table temp_month( val char(255) );
set @sql2 = concat("insert into temp_month (val) values ('", replace(( select group_concat(distinct txt) as data from s), "-", "'),('"),"');");
prepare stmt2 from @sql2;
execute stmt2;
SELECT min(id) into @minmonth from months mn where mn.months in(SELECT val from temp_month);
SELECT max(id) into @maxmonth from months mn where mn.months in(SELECT val from temp_month);


CREATE TEMPORARY TABLE  tmp_students AS 
    select s.student_id,s.student_name,s.sponsor_id,sb.from_month as from_month,sb.to_month as to_month,
    concat(cs.class_name,'/',se.section) as classname,sp.to_class as class_id,concat(dd.Districts,' (', sd.village,')') as Districts  from studentbus sb 
     INNER JOIN student_promotion sp  on sb.student_id=sp.student_id and sp.from_academic=p_accy
    inner join students s on s.student_id=sp.student_id 
    
     JOIN buslocationinfo bi ON bi.id = sb.Bus
    JOIN buslocation bl ON bl.bus_info = bi.id AND bl.id = sb.location
    
    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
    
    INNER JOIN class_sections cl on cl.id=sp.to_class
    INNER JOIN classes cs on cs.id=cl.class_id
    INNER JOIN sections se on se.id=cl.section_id
    where sb.Bus LIKE p_bus AND sb.accy LIKE p_accy   and sp.from_academic= p_accy and 
  (sb.from_month <=@maxmonth and sb.to_month >=  @minmonth  );
  
  
SELECT COUNT(*) FROM temp_month INTO n;
CREATE TEMPORARY TABLE studentfee
(student_id text,student_name text,month text,amount decimal(18,2),status int,sponsor_id int,ClassMonth int ,classname text , Districts text);
SET i=0;
WHILE i<n DO 
SELECT mn.id into  @months from months mn where mn.months=(SELECT val FROM temp_month LIMIT i,1);
INSERT INTO studentfee
SELECT student_id,student_name, (SELECT val FROM temp_month LIMIT i,1)as month,FindBusPaidAmount(tm.student_id,p_accy,@months) as bill, ifnull((SELECT 1 from cash_receipt  where cash_receipt.academic_year=p_accy and cash_receipt.month=(SELECT val FROM temp_month LIMIT i,1) and cash_receipt.student_id=tm.student_id  and cash_receipt.class_id=tm.class_id and cash_receipt.status=1 and cash_receipt.fee_type=8),0) as status,sponsor_id ,(case WHEN @months BETWEEN tm.from_month and tm.to_month THEN 1 else 0 END) as ClassMonth,classname, Districts  from tmp_students tm; 

  SET i = i + 1;
END WHILE;
  CREATE TEMPORARY table tblsort as
SELECT  student_id,student_name,GROUP_CONCAT(month SEPARATOR '-')as month,count(month) as No,
    sum(amount) as Bill,status,classname from studentfee  WHERE  status=p_state  and ClassMonth=1 
    GROUP by student_id
;
SELECT * from tblsort ORDER by No,student_name;


END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_find_unpaid` (IN `p_month` TEXT, IN `p_school_id` INT, IN `p_class` VARCHAR(100), IN `p_section` VARCHAR(100), IN `p_accy` INT, IN `p_fee` INT, IN `p_state` INT, IN `p_user` INT, IN `p_sort` INT)   BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;

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

create table s( txt text );
insert into s values(p_month);
create  table temp_month( val char(255) );
set @sql2 = concat("insert into temp_month (val) values ('", replace(( select group_concat(distinct txt) as data from s), "-", "'),('"),"');");
prepare stmt2 from @sql2;
execute stmt2;
SELECT min(id) into @minmonth from months mn where mn.id  in(SELECT val from temp_month);
SELECT max(id) into @maxmonth from months mn where mn.id in(SELECT val from temp_month);
if (p_fee=7)THEN
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS (
    
SELECT * FROM( 
    select s.student_id,s.student_name,s.sponsor_id,sp.from_month,sp.to_month,
    concat(cs.class_name,'/',se.section) as classname,sp.to_class as class_id from student_promotion sp 
    inner join students s on s.student_id=sp.student_id and s.status=2
    INNER JOIN class_sections cl on cl.id=sp.to_class
    INNER JOIN classes cs on cs.id=cl.class_id
    INNER JOIN sections se on se.id=cl.section_id
    where cs.school_level_id=p_school_id and cl.class_id like p_class and  sp.to_class like p_section   and sp.from_academic= p_accy and 
     (sp.from_month <= @minmonth  and sp.to_month >=  @maxmonth)
    
    

)
    
 as `Data` 
    );

    

    
SELECT COUNT(*) FROM temp_month INTO n;
CREATE TEMPORARY TABLE studentfee
(student_id text,student_name text,month text,amount decimal(18,2),status int,sponsor_id int,ClassMonth int ,classname text );
SET i=0;
WHILE i<n DO 
SELECT mn.id  into  @month from months mn where mn.id=(SELECT val FROM temp_month LIMIT i,1);
INSERT INTO studentfee
SELECT student_id,student_name, (SELECT val FROM temp_month LIMIT i,1)as month,
ifnull((
        
        SELECT cr.amount from class_sections cs INNER join classes c on cs.class_id=c.id 
        INNER JOIN school_fee sl on sl.school_id=c.school_level_id and sl.academidyear_id=p_accy
        and sl.fee_type_id=p_fee 
          INNER JOIN student_promotion sp on sp.to_class=cs.id 
    JOIN cash_receipt cr on cr.student_id=sp.student_id and cr.class_id=cs.id and cr.academic_year=p_accy and cr.status=1
    and cr.fee_type=p_fee and cr.month=(SELECT val FROM temp_month LIMIT i,1)
        WHERE sl.fee_type_id=p_fee and @month BETWEEN sp.from_month 
        and sp.to_month and sp.from_academic=p_accy and sp.student_id=tm.student_id and sp.to_class=tm.class_id)
,(
SELECT sf.amount  from school_fee sf 
 where sf.academidyear_id=p_accy and sf.fee_type_id=p_fee 
 and sf.school_id=p_school_id
) ) as bill,ifnull((SELECT 1 from cash_receipt cr where cr.academic_year=p_accy and cr.month=(SELECT val FROM temp_month LIMIT i,1) and cr.student_id=tm.student_id  and cr.class_id=tm.class_id and cr.status=1 and cr.fee_type= p_fee),0) as status,
sponsor_id,(case WHEN @month BETWEEN tm.from_month and tm.to_month THEN 1 else 0 END)as ClassMonth,classname  from tmp_students tm; 
  SET i = i + 1;
END WHILE;
CREATE TEMPORARY table tblsort as
SELECT  student_id,student_name,GROUP_CONCAT(findmonthName(month) SEPARATOR '-')as month,count(month) as No,
    sum(amount) as Bill,status,classname from studentfee  WHERE  status=p_state  and ClassMonth=1
    GROUP by student_id,ClassMonth,status
;



if(p_sort=0)THEN
SELECT * from tblsort ORDER by No,student_name,classname ;
ELSEIF (p_sort=1)THEN
SELECT * from tblsort ORDER by student_name ;
ELSEIF (p_sort=2)THEN
SELECT * from tblsort ORDER by No,student_name ;
ELSE
SELECT * from tblsort ORDER by classname,student_name;
END if;

ELSEIF (p_fee=3)THEN




CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS 
 select s.student_id,s.student_name,s.sponsor_id,sf.from_month as from_month,sf.to_month as to_month,
    concat(cs.class_name,'/',se.section) as classname,sp.to_class as class_id from studentfood sf
    join student_promotion sp on sf.student_id=sp.student_id and sf.accy=sp.from_academic
    inner join students s on s.student_id=sp.student_id 
    INNER JOIN class_sections cl on cl.id=sp.to_class
    INNER JOIN classes cs on cs.id=cl.class_id
    INNER JOIN sections se on se.id=cl.section_id
    where cs.school_level_id=p_school_id and cl.class_id like p_class and  sp.to_class like p_section   and sp.from_academic= p_accy and 
     (sp.from_month <= @minmonth  and sp.to_class >=  @maxmonth)
 
   
;

    

    
SELECT COUNT(*) FROM temp_month INTO n;
CREATE TEMPORARY TABLE studentfee
(student_id text,student_name text,month text,amount decimal(18,2),status int,sponsor_id int,ClassMonth int ,classname text );
SET i=0;
WHILE i<n DO 
SELECT mn.id into  @month from months mn where mn.id=(SELECT val FROM temp_month LIMIT i,1);
INSERT INTO studentfee
SELECT student_id,student_name, (SELECT val FROM temp_month LIMIT i,1)as month,
ifnull((
        
        SELECT cr.amount from class_sections cs INNER join classes c on cs.class_id=c.id 
        INNER JOIN school_fee sl on sl.school_id=c.school_level_id and sl.academidyear_id=p_accy
        and sl.fee_type_id=p_fee 
          INNER JOIN student_promotion sp on sp.to_class=cs.id 
    JOIN studentfood sf on sf.student_id=sp.student_id and sf.accy=p_accy
    JOIN cash_receipt cr on cr.student_id=sp.student_id and cr.class_id=cs.id and cr.academic_year=p_accy and cr.status=1
    and cr.fee_type=p_fee and cr.month=(SELECT val FROM temp_month LIMIT i,1)
        WHERE sl.fee_type_id=p_fee and @month BETWEEN sf.from_month 
        and sf.to_month and sf.accy=p_accy and sp.student_id=tm.student_id and sp.to_class=tm.class_id)
,(
SELECT sf.amount  from school_fee sf 
 where sf.academidyear_id=p_accy and sf.fee_type_id=p_fee 
 and sf.school_id=p_school_id
) ) as bill,ifnull((SELECT 1 from cash_receipt cr where cr.academic_year=p_accy and cr.month=(SELECT val FROM temp_month LIMIT i,1) and cr.student_id=tm.student_id  and cr.class_id=tm.class_id and cr.status=1 and  cr.fee_type= p_fee ),0) as status,
sponsor_id,(case WHEN @month BETWEEN tm.from_month and tm.to_month THEN 1 else 0 END)as ClassMonth,classname  from tmp_students tm; 
  SET i = i + 1;
END WHILE;
CREATE TEMPORARY table tblsort as
SELECT  student_id,student_name,GROUP_CONCAT(findmonthName(month) SEPARATOR '-')as month,count(month) as No,
    sum(amount) as Bill,status,classname from studentfee  WHERE  status=p_state  and ClassMonth=1
    GROUP by student_id,ClassMonth,status
;



if(p_sort=0)THEN
SELECT * from tblsort ORDER by No,student_name,classname ;
ELSEIF (p_sort=1)THEN
SELECT * from tblsort ORDER by student_name ;
ELSEIF (p_sort=2)THEN
SELECT * from tblsort ORDER by No,student_name ;
ELSE
SELECT * from tblsort ORDER by classname,student_name;
END if;

ELSEIF (p_fee=8)THEN
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS 
SELECT s.student_id, s.student_name, s.sponsor_id, sf.from_month AS from_month, sf.to_month AS to_month,
       CONCAT(cs.class_name, '/', se.section) AS classname, sp.to_class AS class_id 
FROM studentbus sf
JOIN student_promotion sp ON sf.student_id = sp.student_id AND sp.from_academic = sf.accy
INNER JOIN students s ON s.student_id = sp.student_id 
INNER JOIN class_sections cl ON cl.id = sp.to_class
INNER JOIN classes cs ON cs.id = cl.class_id
INNER JOIN sections se ON se.id = cl.section_id
WHERE cs.school_level_id = p_school_id AND cl.class_id LIKE p_class AND sp.to_class LIKE p_section 
      AND sp.from_academic = p_accy AND (sp.from_month <= @minmonth AND sp.to_class >= @maxmonth);

SELECT COUNT(*) INTO n FROM temp_month;
CREATE TEMPORARY TABLE studentfee (
    student_id TEXT,
    student_name TEXT,
    month TEXT,
    amount DECIMAL(18,2),
    status INT,
    sponsor_id INT,
    ClassMonth INT,
    classname TEXT
);

SET i = 0;
WHILE i < n DO 
    SELECT mn.id INTO @month FROM months mn WHERE mn.id = (SELECT val FROM temp_month LIMIT i,1);
    
    INSERT INTO studentfee
    SELECT student_id, student_name, (SELECT val FROM temp_month LIMIT i,1) AS month,
    IFNULL((
        SELECT cr.amount 
        FROM class_sections cs 
        INNER JOIN classes c ON cs.class_id = c.id 
        INNER JOIN school_fee sl ON sl.school_id = c.school_level_id AND sl.academidyear_id = p_accy
        AND sl.fee_type_id = p_fee 
        INNER JOIN student_promotion sp ON sp.to_class = cs.id 
        JOIN studentbus sf ON sf.student_id = sp.student_id AND sf.accy = p_accy
        JOIN cash_receipt cr ON cr.student_id = sp.student_id AND cr.class_id = cs.id 
        AND cr.academic_year = p_accy AND cr.status = 1 AND cr.fee_type = p_fee 
        AND cr.month = (SELECT val FROM temp_month LIMIT i,1)
        WHERE sl.fee_type_id = p_fee AND @month BETWEEN sf.from_month AND sf.to_month 
        AND sf.accy = p_accy AND sp.student_id = tm.student_id AND sp.to_class = tm.class_id
        LIMIT 1  
    ), (
        SELECT ll.amount 
        FROM studentbus ssb 
        JOIN buslocationinfo bbl ON bbl.id = ssb.Bus 
        JOIN buslocation bll ON bll.id = ssb.location 
        JOIN sub_district sd ON sd.id = bll.location
        JOIN location ll ON ll.sub_district = sd.id 
        WHERE ll.accy = p_accy AND ssb.student_id = tm.student_id
        LIMIT 1  
    )) AS bill,
    IFNULL((SELECT 1 
            FROM cash_receipt cr 
            WHERE cr.academic_year = p_accy AND cr.month = (SELECT val FROM temp_month LIMIT i,1) 
            AND cr.student_id = tm.student_id AND cr.class_id = tm.class_id AND cr.status = 1 
            AND cr.fee_type = p_fee), 0) AS status,
    sponsor_id,
    (CASE WHEN @month BETWEEN tm.from_month AND tm.to_month THEN 1 ELSE 0 END) AS ClassMonth,
    classname  
    FROM tmp_students tm; 
    
    SET i = i + 1;
END WHILE;

CREATE TEMPORARY TABLE tblsort AS
SELECT student_id, student_name, 
       GROUP_CONCAT(findmonthName(month) SEPARATOR '-') AS month,
       COUNT(month) AS No,
       SUM(amount) AS Bill,
       status,
       classname 
FROM studentfee  
WHERE status = p_state AND ClassMonth = 1
GROUP BY student_id, ClassMonth, status;

IF (p_sort = 0) THEN
    SELECT * FROM tblsort ORDER BY No, student_name, classname;
ELSEIF (p_sort = 1) THEN
    SELECT * FROM tblsort ORDER BY student_name;
ELSEIF (p_sort = 2) THEN
    SELECT * FROM tblsort ORDER BY No, student_name;
ELSE
    SELECT * FROM tblsort ORDER BY classname, student_name;
END IF;
ELSE
CREATE  TABLE IF NOT EXISTS tmp_students1 AS (
SELECT s.student_id,s.student_name,COUNT(sc.student_id) as count, ft.fee_name as month,(SELECT sf.amount from school_fee sf where sf.school_id=cs.school_level_id and sf.academidyear_id=sc.accyr and sf.fee_type_id=sc.fee_type) as Bill,concat(cs.class_name,'/',se.section) as classname,sc.class as class_id,ifnull((SELECT ifnull(COUNT(cr.student_id),0) from cash_receipt cr where cr.student_id=sc.student_id and cr.class_id=sc.class and cr.academic_year=sc.accyr and cr.fee_type=sc.fee_type and cr.status=1 GROUP by cr.student_id),0) as stat FROM student_charge sc  
    inner join students s on s.student_id=sc.student_id and s.status=2
    
    INNER JOIN class_sections cl on cl.id=sc.class
    INNER JOIN classes cs on cs.id=cl.class_id
    INNER JOIN sections se on se.id=cl.section_id
    INNER JOIN fee_type ft on ft.id=sc.fee_type
   
    where cs.school_level_id=p_school_id and cl.class_id like p_class and  sc.class like p_section   and sc.accyr= p_accy and  sc.fee_type=p_fee GROUP by sc.student_id
    );
    CREATE TEMPORARY table tblstatus as
    SELECT student_id,student_name,classname,stat,count as No ,month,Bill,IF(stat IS NULL OR count IS NULL, 0, IF(stat = count, '1', '0'))as statuss from tmp_students1;
    
   
 
  


   if(p_sort=0)THEN
SELECT * from tblstatus where statuss=p_state ORDER by No,student_name,classname ;
ELSEIF (p_sort=1)THEN
SELECT * from tblstatus where statuss=p_state ORDER by student_name ;
ELSEIF (p_sort=2)THEN
SELECT * from tblstatus where statuss=p_state ORDER by No,student_name ;
ELSE
SELECT * from tblstatus where statuss=p_state ORDER by classname,student_name;
END if;
    
END if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_find_unpaid1` (IN `p_month` TEXT, IN `p_school_id` INT, IN `p_class` VARCHAR(100), IN `p_section` VARCHAR(100), IN `p_accy` INT, IN `p_fee` INT, IN `p_state` INT, IN `p_user` INT, IN `p_sort` INT)   BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;

DECLARE n1 INT DEFAULT 0;
DECLARE i1 INT DEFAULT 0;
if (p_fee=7)THEN
create table s( txt text );
insert into s values(p_month);


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

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS (
    
SELECT * FROM( 
  
    
   
    
    select s.student_id,s.student_name,s.sponsor_id,sp.from_month,sp.to_month,
    concat(cs.class_name,'/',se.section) as classname,sp.to_class as class_id from student_promotion sp 
    inner join students s on s.student_id=sp.student_id and s.status=2
    INNER JOIN class_sections cl on cl.id=sp.to_class
    INNER JOIN classes cs on cs.id=cl.class_id
    INNER JOIN sections se on se.id=cl.section_id
    where cs.school_level_id=p_school_id and cl.class_id like p_class and  sp.to_class like p_section   and sp.from_academic= p_accy and (SELECT min(id) from months mn where mn.months in(SELECT val from temp_month) ) >= sp.from_month   and   (SELECT max(id) from months mn where mn.months in(SELECT val from temp_month) <= sp.to_month )
    UNION
     select s.student_id,s.student_name,s.sponsor_id,sp.from_month,sp.to_month, concat(cs.class_name,'/',se.section) as classname,sp.from_class as class_id from student_promotion sp 
    inner join students s on s.student_id=sp.student_id and s.status=2
    INNER JOIN class_sections cl on cl.id=sp.from_class
      INNER JOIN classes cs on cs.id=cl.class_id
    INNER JOIN sections se on se.id=cl.section_id
    where  cs.school_level_id=p_school_id AND cl.class_id like p_class and  sp.from_class like p_section  and sp.from_academic= p_accy and (SELECT min(id) from months mn where mn.months in(SELECT val from temp_month) ) >= sp.from_month   and  (SELECT max(id) from months mn where mn.months in(SELECT val from temp_month) <= sp.to_month  )
    
) as `Data` 
    );

    

    
SELECT COUNT(*) FROM temp_month INTO n;
CREATE TEMPORARY TABLE studentfee
(student_id int,student_name text,month text,amount decimal(18,2),status int,sponsor_id int,ClassMonth int ,classname text );
SET i=0;
WHILE i<n DO 
SELECT mn.id into  @month from months mn where mn.months=(SELECT val FROM temp_month LIMIT i,1);
INSERT INTO studentfee
SELECT student_id,student_name, (SELECT val FROM temp_month LIMIT i,1)as month,
ifnull((SELECT sl.amount from class_sections cs INNER join classes c on cs.class_id=c.id 
        INNER JOIN school_fee sl on sl.school_id=c.school_level_id and sl.academidyear_id=p_accy
        and sl.fee_type_id=p_fee 
        INNER JOIN student_promotion sp on sp.from_class=cs.id  
        WHERE sl.fee_type_id=p_fee and @month BETWEEN sp.from_month 
        and sp.to_month and sp.from_academic=p_accy and sp.student_id=tm.student_id 
        union
        SELECT sl.amount from class_sections cs INNER join classes c on cs.class_id=c.id 
        INNER JOIN school_fee sl on sl.school_id=c.school_level_id and sl.academidyear_id=p_accy
        and sl.fee_type_id=p_fee 
        INNER JOIN student_promotion sp on sp.to_class=cs.id  
        WHERE sl.fee_type_id=p_fee and @month BETWEEN sp.from_month 
        and sp.to_month and sp.to_class=p_accy and sp.student_id=tm.student_id)
,(
SELECT sf.amount  from school_fee sf 
 where sf.academidyear_id=p_accy and sf.fee_type_id=p_fee 
 and sf.school_id=p_school_id
) ) as bill,ifnull((SELECT 1 from cash_receipt cr where cr.academic_year=p_accy and cr.month=(SELECT val FROM temp_month LIMIT i,1) and cr.student_id=tm.student_id  and cr.class_id=tm.class_id and cr.status=1 ),0) as status,
sponsor_id,(case WHEN @month BETWEEN tm.from_month and tm.to_month THEN 1 else 0 END)as ClassMonth,classname  from tmp_students tm; 
  SET i = i + 1;
END WHILE;
CREATE TEMPORARY table tblsort as
SELECT  student_id,student_name,GROUP_CONCAT(month SEPARATOR '-')as month,count(month) as No,
    sum(amount) as Bill,status,classname from studentfee  WHERE  status=p_state  and ClassMonth=1
    GROUP by student_id,status
;
if(p_sort=0)THEN
SELECT * from tblsort ORDER by No,student_name,classname ;
ELSEIF (p_sort=1)THEN
SELECT * from tblsort ORDER by student_name ;
ELSEIF (p_sort=2)THEN
SELECT * from tblsort ORDER by No,student_name ;
ELSE
SELECT * from tblsort ORDER by classname,student_name;
END if;
END if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_find_unpaid2` (IN `p_month` TEXT, IN `p_school_id` INT, IN `p_class` VARCHAR(100), IN `p_section` VARCHAR(100), IN `p_accy` INT, IN `p_fee` INT, IN `p_state` INT, IN `p_user` INT, IN `p_sort` INT)   BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;

DECLARE n1 INT DEFAULT 0;
DECLARE i1 INT DEFAULT 0;
if (p_fee=7)THEN
create table s( txt text );
insert into s values(p_month);


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

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS (
    
SELECT * FROM( 
     select s.student_id,s.student_name,s.sponsor_id,sp.from_month,sp.to_month,
    concat(cs.class_name,'/',se.section) as classname,sp.to_class as class_id from student_promotion sp 
    inner join students s on s.student_id=sp.student_id and s.status=2
    INNER JOIN class_sections cl on cl.id=sp.to_class
    INNER JOIN classes cs on cs.id=cl.class_id
    INNER JOIN sections se on se.id=cl.section_id
    where cs.school_level_id=p_school_id and cl.class_id like p_class and  sp.to_class like p_section   and sp.from_academic= p_accy and (sp.from_month BETWEEN @minmonth AND @maxmonth OR sp.to_month BETWEEN @minmonth AND @maxmonth)
    
    
    
) as `Data` 
    );

    

    
SELECT COUNT(*) FROM temp_month INTO n;
CREATE TEMPORARY TABLE studentfee
(student_id text,student_name text,month text,amount decimal(18,2),status int,sponsor_id int,ClassMonth int ,classname text );
SET i=0;
WHILE i<n DO 
SELECT mn.id into  @month from months mn where mn.months=(SELECT val FROM temp_month LIMIT i,1);
INSERT INTO studentfee
SELECT student_id,student_name, (SELECT val FROM temp_month LIMIT i,1)as month,
ifnull((SELECT sl.amount from class_sections cs INNER join classes c on cs.class_id=c.id 
        INNER JOIN school_fee sl on sl.school_id=c.school_level_id and sl.academidyear_id=p_accy
        and sl.fee_type_id=p_fee 
        INNER JOIN student_promotion sp on sp.to_class=cs.id 
        WHERE sl.fee_type_id=p_fee and @month BETWEEN sp.from_month 
        and sp.to_month and sp.from_academic=p_accy and sp.student_id=tm.student_id 
        union
        SELECT sl.amount from class_sections cs INNER join classes c on cs.class_id=c.id 
        INNER JOIN school_fee sl on sl.school_id=c.school_level_id and sl.academidyear_id=p_accy
        and sl.fee_type_id=p_fee 
        INNER JOIN student_promotion sp on sp.to_class=cs.id  
        WHERE sl.fee_type_id=p_fee and @month BETWEEN sp.from_month 
        and sp.to_month and sp.to_class=p_accy and sp.student_id=tm.student_id)
,(
SELECT sf.amount  from school_fee sf 
 where sf.academidyear_id=p_accy and sf.fee_type_id=p_fee 
 and sf.school_id=p_school_id
) ) as bill,ifnull((SELECT 1 from cash_receipt cr where cr.academic_year=p_accy and cr.month=(SELECT val FROM temp_month LIMIT i,1) and cr.student_id=tm.student_id  and cr.class_id=tm.class_id and cr.status=1 ),0) as status,
sponsor_id,(case WHEN @month BETWEEN tm.from_month and tm.to_month THEN 1 else 0 END)as ClassMonth,classname  from tmp_students tm; 
  SET i = i + 1;
END WHILE;



CREATE TEMPORARY table tblsort as
SELECT  student_id,student_name,GROUP_CONCAT(month SEPARATOR '-')as month,count(month) as No,sum(amount) as Bill,status,classname from studentfee  WHERE  status=p_state  and ClassMonth=1 GROUP by student_id,status;
if(p_sort=0)THEN
SELECT * from tblsort ORDER by No,student_name,classname ;
ELSEIF (p_sort=1)THEN
SELECT * from tblsort ORDER by student_name ;
ELSEIF (p_sort=2)THEN
SELECT * from tblsort ORDER by No,student_name ;
ELSE
SELECT * from tblsort ORDER by classname,student_name;
END if;
END if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_find_unpaidtest` (IN `p_month` TEXT, IN `p_school_id` INT, IN `p_class` VARCHAR(100), IN `p_section` VARCHAR(100), IN `p_accy` INT, IN `p_fee` INT, IN `p_state` INT, IN `p_user` INT, IN `p_sort` INT)   BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;

DECLARE n1 INT DEFAULT 0;
DECLARE i1 INT DEFAULT 0;
if (p_fee=7)THEN
create table s( txt text );
insert into s values(p_month);


create  table temp_month( val char(255) );
set @sql2 = concat("insert into temp_month (val) values ('", replace(( select group_concat(distinct txt) as data from s), "-", "'),('"),"');");
prepare stmt2 from @sql2;
execute stmt2;
SELECT min(id) into @minmonth from months mn where mn.months in(SELECT val from temp_month);
SELECT max(id) into @maxmonth from months mn where mn.months in(SELECT val from temp_month);


CREATE  TABLE IF NOT EXISTS tmp_students AS (
    
SELECT * FROM( 
  
    
   
    
    select s.student_id,s.student_name,s.sponsor_id,sp.from_month,sp.to_month,
    concat(cs.class_name,'/',se.section) as classname,sp.to_class as class_id from student_promotion sp 
    inner join students s on s.student_id=sp.student_id and s.status=2
    INNER JOIN class_sections cl on cl.id=sp.to_class
    INNER JOIN classes cs on cs.id=cl.class_id
    INNER JOIN sections se on se.id=cl.section_id
    where cs.school_level_id=p_school_id and cl.class_id like p_class and  sp.to_class like p_section   and sp.from_academic= p_accy and 
     (sp.from_month <= @minmonth  and sp.to_month >=  @maxmonth)
    
    

)
    
 as `Data` 
    );

    

    
SELECT COUNT(*) FROM temp_month INTO n;
CREATE TEMPORARY TABLE studentfee
(student_id text,student_name text,month text,amount decimal(18,2) ,
status int,sponsor_id int,ClassMonth int ,classname text );
SET i=0;
WHILE i<n DO 
SELECT mn.id into  @month from months mn where mn.months=(SELECT val FROM temp_month LIMIT i,1);
INSERT INTO studentfee
SELECT student_id,student_name,
ifnull((
        
        SELECT cr.amount from class_sections cs INNER join classes c on cs.class_id=c.id 
        INNER JOIN school_fee sl on sl.school_id=c.school_level_id and sl.academidyear_id=p_accy
        and sl.fee_type_id=p_fee 
          INNER JOIN student_promotion sp on sp.to_class=cs.id 
    JOIN cash_receipt cr on cr.student_id=sp.student_id and cr.class_id=cs.id and cr.academic_year=p_accy
    and cr.fee_type=p_fee and cr.month=(SELECT val FROM temp_month LIMIT i,1)
        WHERE sl.fee_type_id=p_fee and @month BETWEEN sp.from_month 
        and sp.to_month and sp.from_academic=p_accy and sp.student_id=tm.student_id and sp.to_class=tm.class_id)
,(
SELECT sf.amount  from school_fee sf 
 where sf.academidyear_id=p_accy and sf.fee_type_id=p_fee 
 and sf.school_id=p_school_id
) ) as bill, (SELECT val FROM temp_month LIMIT i,1)as month,ifnull((SELECT 1 from cash_receipt cr where cr.academic_year=p_accy and cr.month=(SELECT val FROM temp_month LIMIT i,1) and cr.student_id=tm.student_id  and cr.class_id=tm.class_id and cr.status=1 ),0) as status,
sponsor_id,(case WHEN @month BETWEEN tm.from_month and tm.to_month THEN 1 else 0 END)as ClassMonth,classname  from tmp_students tm; 
  SET i = i + 1;
END WHILE;
SELECT * from tmp_students ORDER by student_id;
   
END if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_find_unpaid_lastOld` (IN `p_month` TEXT, IN `p_school_id` INT, IN `p_class` VARCHAR(100), IN `p_section` VARCHAR(100), IN `p_accy` INT, IN `p_fee` INT, IN `p_state` INT, IN `p_user` INT, IN `p_sort` INT)   BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;

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

create table s( txt text );
insert into s values(p_month);
create  table temp_month( val char(255) );
set @sql2 = concat("insert into temp_month (val) values ('", replace(( select group_concat(distinct txt) as data from s), "-", "'),('"),"');");
prepare stmt2 from @sql2;
execute stmt2;
SELECT min(id) into @minmonth from months mn where mn.months in(SELECT val from temp_month);
SELECT max(id) into @maxmonth from months mn where mn.months in(SELECT val from temp_month);
if (p_fee=7)THEN
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS (
    
SELECT * FROM( 
    select s.student_id,s.student_name,s.sponsor_id,sp.from_month,sp.to_month,
    concat(cs.class_name,'/',se.section) as classname,sp.to_class as class_id from student_promotion sp 
    inner join students s on s.student_id=sp.student_id and s.status=2
    INNER JOIN class_sections cl on cl.id=sp.to_class
    INNER JOIN classes cs on cs.id=cl.class_id
    INNER JOIN sections se on se.id=cl.section_id
    where cs.school_level_id=p_school_id and cl.class_id like p_class and  sp.to_class like p_section   and sp.from_academic= p_accy and 
     (sp.from_month <= @minmonth  and sp.to_month >=  @maxmonth)
    
    

)
    
 as `Data` 
    );

    

    
SELECT COUNT(*) FROM temp_month INTO n;
CREATE TEMPORARY TABLE studentfee
(student_id text,student_name text,month text,amount decimal(18,2),status int,sponsor_id int,ClassMonth int ,classname text );
SET i=0;
WHILE i<n DO 
SELECT mn.id into  @month from months mn where mn.months=(SELECT val FROM temp_month LIMIT i,1);
INSERT INTO studentfee
SELECT student_id,student_name, (SELECT val FROM temp_month LIMIT i,1)as month,
ifnull((
        
        SELECT cr.amount from class_sections cs INNER join classes c on cs.class_id=c.id 
        INNER JOIN school_fee sl on sl.school_id=c.school_level_id and sl.academidyear_id=p_accy
        and sl.fee_type_id=p_fee 
          INNER JOIN student_promotion sp on sp.to_class=cs.id 
    JOIN cash_receipt cr on cr.student_id=sp.student_id and cr.class_id=cs.id and cr.academic_year=p_accy and cr.status=1
    and cr.fee_type=p_fee and cr.month=(SELECT val FROM temp_month LIMIT i,1)
        WHERE sl.fee_type_id=p_fee and @month BETWEEN sp.from_month 
        and sp.to_month and sp.from_academic=p_accy and sp.student_id=tm.student_id and sp.to_class=tm.class_id)
,(
SELECT sf.amount  from school_fee sf 
 where sf.academidyear_id=p_accy and sf.fee_type_id=p_fee 
 and sf.school_id=p_school_id
) ) as bill,ifnull((SELECT 1 from cash_receipt cr where cr.academic_year=p_accy and cr.month=(SELECT val FROM temp_month LIMIT i,1) and cr.student_id=tm.student_id  and cr.class_id=tm.class_id and cr.status=1 and cr.fee_type= p_fee),0) as status,
sponsor_id,(case WHEN @month BETWEEN tm.from_month and tm.to_month THEN 1 else 0 END)as ClassMonth,classname  from tmp_students tm; 
  SET i = i + 1;
END WHILE;
CREATE TEMPORARY table tblsort as
SELECT  student_id,student_name,GROUP_CONCAT(month SEPARATOR '-')as month,count(month) as No,
    sum(amount) as Bill,status,classname from studentfee  WHERE  status=p_state  and ClassMonth=1
    GROUP by student_id,ClassMonth,status
;



if(p_sort=0)THEN
SELECT * from tblsort ORDER by No,student_name,classname ;
ELSEIF (p_sort=1)THEN
SELECT * from tblsort ORDER by student_name ;
ELSEIF (p_sort=2)THEN
SELECT * from tblsort ORDER by No,student_name ;
ELSE
SELECT * from tblsort ORDER by classname,student_name;
END if;

ELSEIF (p_fee=3)THEN




CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS 
 select s.student_id,s.student_name,s.sponsor_id,sf.from_month as from_month,sf.to_month as to_month,
    concat(cs.class_name,'/',se.section) as classname,sp.to_class as class_id from studentfood sf
    join student_promotion sp on sf.student_id=sp.student_id and sf.accy=sp.from_academic
    inner join students s on s.student_id=sp.student_id 
    INNER JOIN class_sections cl on cl.id=sp.to_class
    INNER JOIN classes cs on cs.id=cl.class_id
    INNER JOIN sections se on se.id=cl.section_id
    where cs.school_level_id=p_school_id and cl.class_id like p_class and  sp.to_class like p_section   and sp.from_academic= p_accy and 
     (sp.from_month <= @minmonth  and sp.to_class >=  @maxmonth)
 
   
;

    

    
SELECT COUNT(*) FROM temp_month INTO n;
CREATE TEMPORARY TABLE studentfee
(student_id text,student_name text,month text,amount decimal(18,2),status int,sponsor_id int,ClassMonth int ,classname text );
SET i=0;
WHILE i<n DO 
SELECT mn.id into  @month from months mn where mn.months=(SELECT val FROM temp_month LIMIT i,1);
INSERT INTO studentfee
SELECT student_id,student_name, (SELECT val FROM temp_month LIMIT i,1)as month,
ifnull((
        
        SELECT cr.amount from class_sections cs INNER join classes c on cs.class_id=c.id 
        INNER JOIN school_fee sl on sl.school_id=c.school_level_id and sl.academidyear_id=p_accy
        and sl.fee_type_id=p_fee 
          INNER JOIN student_promotion sp on sp.to_class=cs.id 
    JOIN studentfood sf on sf.student_id=sp.student_id and sf.accy=p_accy
    JOIN cash_receipt cr on cr.student_id=sp.student_id and cr.class_id=cs.id and cr.academic_year=p_accy and cr.status=1
    and cr.fee_type=p_fee and cr.month=(SELECT val FROM temp_month LIMIT i,1)
        WHERE sl.fee_type_id=p_fee and @month BETWEEN sf.from_month 
        and sf.to_month and sf.accy=p_accy and sp.student_id=tm.student_id and sp.to_class=tm.class_id)
,(
SELECT sf.amount  from school_fee sf 
 where sf.academidyear_id=p_accy and sf.fee_type_id=p_fee 
 and sf.school_id=p_school_id
) ) as bill,ifnull((SELECT 1 from cash_receipt cr where cr.academic_year=p_accy and cr.month=(SELECT val FROM temp_month LIMIT i,1) and cr.student_id=tm.student_id  and cr.class_id=tm.class_id and cr.status=1 and  cr.fee_type= p_fee ),0) as status,
sponsor_id,(case WHEN @month BETWEEN tm.from_month and tm.to_month THEN 1 else 0 END)as ClassMonth,classname  from tmp_students tm; 
  SET i = i + 1;
END WHILE;
CREATE TEMPORARY table tblsort as
SELECT  student_id,student_name,GROUP_CONCAT(month SEPARATOR '-')as month,count(month) as No,
    sum(amount) as Bill,status,classname from studentfee  WHERE  status=p_state  and ClassMonth=1
    GROUP by student_id,ClassMonth,status
;



if(p_sort=0)THEN
SELECT * from tblsort ORDER by No,student_name,classname ;
ELSEIF (p_sort=1)THEN
SELECT * from tblsort ORDER by student_name ;
ELSEIF (p_sort=2)THEN
SELECT * from tblsort ORDER by No,student_name ;
ELSE
SELECT * from tblsort ORDER by classname,student_name;
END if;

ELSEIF (p_fee=8)THEN




CREATE TEMPORARY TABLE IF NOT EXISTS tmp_students AS 
 select s.student_id,s.student_name,s.sponsor_id,sf.from_month as from_month,sf.to_month as to_month,
    concat(cs.class_name,'/',se.section) as classname,sp.to_class as class_id from studentbus sf
    join student_promotion sp on sf.student_id=sp.student_id and sp.from_academic=sf.accy
    inner join students s on s.student_id=sp.student_id 
    INNER JOIN class_sections cl on cl.id=sp.to_class
    INNER JOIN classes cs on cs.id=cl.class_id
    INNER JOIN sections se on se.id=cl.section_id
    where cs.school_level_id=p_school_id and cl.class_id like p_class and  sp.to_class like p_section   and sp.from_academic= p_accy and 
      (sp.from_month <= @minmonth  and sp.to_class >=  @maxmonth)
 
   
;

    

    
SELECT COUNT(*) FROM temp_month INTO n;
CREATE TEMPORARY TABLE studentfee
(student_id text,student_name text,month text,amount decimal(18,2),status int,sponsor_id int,ClassMonth int ,classname text );
SET i=0;
WHILE i<n DO 
SELECT mn.id into  @month from months mn where mn.months=(SELECT val FROM temp_month LIMIT i,1);
INSERT INTO studentfee
SELECT student_id,student_name, (SELECT val FROM temp_month LIMIT i,1)as month,
ifnull((
        
        SELECT cr.amount from class_sections cs INNER join classes c on cs.class_id=c.id 
        INNER JOIN school_fee sl on sl.school_id=c.school_level_id and sl.academidyear_id=p_accy
        and sl.fee_type_id=p_fee 
          INNER JOIN student_promotion sp on sp.to_class=cs.id 
    JOIN studentbus sf on sf.student_id=sp.student_id and sf.accy=p_accy
    JOIN cash_receipt cr on cr.student_id=sp.student_id and cr.class_id=cs.id and cr.academic_year=p_accy and cr.status=1
    and cr.fee_type=p_fee and cr.month=(SELECT val FROM temp_month LIMIT i,1)
        WHERE sl.fee_type_id=p_fee and @month BETWEEN sf.from_month 
        and sf.to_month and sf.accy=p_accy and sp.student_id=tm.student_id and sp.to_class=tm.class_id)
,(
SELECT ll.amount from studentbus ssb join buslocationinfo bbl on bbl.id=ssb.Bus join buslocation bll on bll.id=ssb.location JOIN sub_district sd ON sd.id=bll.location
join location ll on ll.sub_district=sd.id where ll.accy=p_accy and ssb.student_id=tm.student_id
) ) as bill,ifnull((SELECT 1 from cash_receipt cr where cr.academic_year=p_accy and cr.month=(SELECT val FROM temp_month LIMIT i,1) and cr.student_id=tm.student_id  and cr.class_id=tm.class_id and cr.status=1 and cr.fee_type= p_fee),0) as status,
sponsor_id,(case WHEN @month BETWEEN tm.from_month and tm.to_month THEN 1 else 0 END)as ClassMonth,classname  from tmp_students tm; 
  SET i = i + 1;
END WHILE;
CREATE TEMPORARY table tblsort as
SELECT  student_id,student_name,GROUP_CONCAT(month SEPARATOR '-')as month,count(month) as No,
    sum(amount) as Bill,status,classname from studentfee  WHERE  status=p_state  and ClassMonth=1
    GROUP by student_id,ClassMonth,status
;



if(p_sort=0)THEN
SELECT * from tblsort ORDER by No,student_name,classname ;
ELSEIF (p_sort=1)THEN
SELECT * from tblsort ORDER by student_name ;
ELSEIF (p_sort=2)THEN
SELECT * from tblsort ORDER by No,student_name ;
ELSE
SELECT * from tblsort ORDER by classname,student_name;
END if;
ELSE
CREATE  TABLE IF NOT EXISTS tmp_students1 AS (
SELECT s.student_id,s.student_name,COUNT(sc.student_id) as count, ft.fee_name as month,(SELECT sf.amount from school_fee sf where sf.school_id=cs.school_level_id and sf.academidyear_id=sc.accyr and sf.fee_type_id=sc.fee_type) as Bill,concat(cs.class_name,'/',se.section) as classname,sc.class as class_id,ifnull((SELECT ifnull(COUNT(cr.student_id),0) from cash_receipt cr where cr.student_id=sc.student_id and cr.class_id=sc.class and cr.academic_year=sc.accyr and cr.fee_type=sc.fee_type and cr.status=1 GROUP by cr.student_id),0) as stat FROM student_charge sc  
    inner join students s on s.student_id=sc.student_id and s.status=2
    
    INNER JOIN class_sections cl on cl.id=sc.class
    INNER JOIN classes cs on cs.id=cl.class_id
    INNER JOIN sections se on se.id=cl.section_id
    INNER JOIN fee_type ft on ft.id=sc.fee_type
   
    where cs.school_level_id=p_school_id and cl.class_id like p_class and  sc.class like p_section   and sc.accyr= p_accy and  sc.fee_type=p_fee GROUP by sc.student_id
    );
    CREATE TEMPORARY table tblstatus as
    SELECT student_id,student_name,classname,stat,count as No ,month,Bill,IF(stat IS NULL OR count IS NULL, 0, IF(stat = count, '1', '0'))as statuss from tmp_students1;
    
   
 
  


   if(p_sort=0)THEN
SELECT * from tblstatus where statuss=p_state ORDER by No,student_name,classname ;
ELSEIF (p_sort=1)THEN
SELECT * from tblstatus where statuss=p_state ORDER by student_name ;
ELSEIF (p_sort=2)THEN
SELECT * from tblstatus where statuss=p_state ORDER by No,student_name ;
ELSE
SELECT * from tblstatus where statuss=p_state ORDER by classname,student_name;
END if;
    
END if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_f_evolution` (IN `p_emp` INT, IN `p_acedemic` INT)  NO SQL BEGIN
SELECT e.name,sum(fe.marks) 'Points',sum(fe.marks)/2 'AVG' from employee e 
INNER join f_evolution_info fe on fe.teacher_id=e.id where e.id=p_emp and fe.academic_year=p_acedemic
GROUP by fe.teacher_id,fe.academic_year;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_f_p_evolution` (IN `p_id` INT, IN `p_teacher` INT, IN `p_evolution_id` TEXT, IN `p_evolution` TEXT, IN `p_points` TEXT, IN `p_no_times` INT, IN `p_date` DATE, IN `p_user_id` INT)  NO SQL BEGIN
DECLARE i int;

SELECT e.qeybta into @qeybta from employee e where e.id=p_teacher;
SELECT a.id into @current FROM academicyear A WHERE  A.is_active='yes';
create  table t(id text,evolution text,points text);
insert into t values(p_evolution_id,p_evolution,p_points);
        drop  table  if exists a ;
create  table a(id text,evolution text,points text);
SELECT LENGTH(id) - LENGTH(REPLACE(id, ',', '')) + 1,LENGTH(evolution) - LENGTH(REPLACE(evolution, ',', '')) + 1,LENGTH(points) - LENGTH(REPLACE(points, ',', '')) + 1 into @id,@evolution,@points FROM `t` ;
set i=1;
WHILE i<=@id DO
insert into a(id,evolution,points)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(id, ',', i), ',', -1), if(i>@evolution,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(evolution, ',', i), ',', -1)),if(i>@points,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(points, ',', i), ',', -1))  FROM `t`;
 SET i = i + 1;
 END WHILE;
 
if EXISTS(SELECT * from f_evolution_info where id=p_id)THEN
 SELECT sum(points) into @points from a;
 update f_evolution_info set No_times=p_no_times,marks=@points 
 where id=p_id;
 set @evolution_info=p_id;
 ELSE
 INSERT INTO `f_evolution_info`( `teacher_id`,qaybta ,`academic_year`, `No_times`, `marks`, `user_id`, `date`)
 
 SELECT p_teacher,@qeybta,@current,p_no_times,sum(points),p_user_id,p_date from a;
 
 set @evolution_info=(SELECT LAST_INSERT_ID()); 
 END if;
   INSERT into f_evolution_points(evolution_info, evolution_id,points)
SELECT @evolution_info,evolution,points
FROM   a
WHERE  NOT EXISTS (SELECT 1 
                   FROM   f_evolution_points 
                   WHERE  a.ID = f_evolution_points.ID and f_evolution_points.evolution_info=@evolution_info);
                   UPDATE f_evolution_points fe
        INNER JOIN a a 
             ON a.id = fe.id
SET fe.points = a.points ;

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

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

select month(CURRENT_TIMESTAMP) into @month;
if(@month BETWEEN 8 and 12) then
select concat(year(CURRENT_TIMESTAMP),'-',year(CURRENT_TIMESTAMP)+1) 'Acedemic_year' into @acc;
    ELSE
 select concat(year(CURRENT_TIMESTAMP)-1,'-',year(CURRENT_TIMESTAMP)) 'Acedemic_year' into @acc;
end if;


if(@month BETWEEN 8 and 12) then
select concat(year(CURRENT_TIMESTAMP)-1,'-',year(CURRENT_TIMESTAMP)) 'prev_year' into @pre;
    ELSE
   select concat(year(CURRENT_TIMESTAMP)-2,'-',year(CURRENT_TIMESTAMP)-1) 'prev_year'into @pre;
end if;
select @pre,@acc ;
END$$

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

select month(CURRENT_TIMESTAMP) into @month;
if(@month BETWEEN 8 and 12) then
select concat(year(CURRENT_TIMESTAMP),'-',year(CURRENT_TIMESTAMP)+1) 'Acedemic_year' into @acc;
    ELSE
 select concat(year(CURRENT_TIMESTAMP)-1,'-',year(CURRENT_TIMESTAMP)) 'Acedemic_year' into @acc;
end if;


if(@month BETWEEN 8 and 12) then
select concat(year(CURRENT_TIMESTAMP)-1,'-',year(CURRENT_TIMESTAMP)) 'prev_year' into @pre;
    ELSE
   select concat(year(CURRENT_TIMESTAMP)-2,'-',year(CURRENT_TIMESTAMP)-1) 'prev_year'into @pre;
end if;
 INSERT INTO temp_prev_balance
select @pre,@acc ;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_g_attendence` (IN `p_month` VARCHAR(100), IN `p_type` INT)  NO SQL BEGIN
DECLARE i int ;
SET SESSION group_concat_max_len = 1000000;


select Year(CURRENT_DATE()) into @year;

SELECT concat(@year,'-',p_month,'-01') into @covert;
SELECT  STR_TO_DATE( @covert ,'%Y-%M-%d')  into @newdate;
SELECT DAY(LAST_DAY(@newdate)) as DaysInMonth  into @month;
CREATE  table   daterange (id int PRIMARY key AUTO_INCREMENT,dates date);
set i=0;
WHILE i<@month DO
insert into daterange(dates)
SELECT DATE_SUB(LAST_DAY(@newdate),INTERVAL DAY(LAST_DAY(@newdate))-
1 DAY)+ INTERVAL i DAY;

 SET i = i + 1;
 END WHILE;
 if(p_type!=2)THEN
CREATE TEMPORARY TABLE table1
SELECT dates from daterange HAVING  DAYOFWEEK(dates)!=6 order by id asc;
ELSE
CREATE TEMPORARY TABLE table1
SELECT dates from daterange HAVING DAYOFWEEK(dates) not in (5,6)  order by id asc;
END if;

SELECT DAY(dates)AS dates from table1;

 END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_history` (IN `p_accy` INT, IN `p_school_level` INT, IN `p_student` VARCHAR(100))  NO SQL BEGIN
SET @runtot:=0;
CREATE TEMPORARY TABLE view1 as
SELECT 7 as id,CURRENT_DATE() AS DATE,'Charge' as Type,'' AS Invoice,'Tuition fees' as 'ACCOUNT',
group_concat(mn.months order by mn.id) 'Month','' MEMO,round(ifnull(
sum(1)*(SELECT sf.amount  from school_fee sf where sf.academidyear_id=p_accy and sf.school_id=p_school_level and sf.fee_type_id=7),0.00),2) Increase,
'0.00' as Descrease from months mn  

group by floor((mn.id - 1) / 3) 
union ALL
SELECT ft.id,CURRENT_DATE() AS DATE,'Charge' as Type,'' AS Invoice,ft.fee_name as 'ACCOUNT',
'-' Month,'' MEMO,round(sf.amount)Increase,
'0.00' as Descrease    FROM student_charge sce JOIN fee_type ft on ft.id=sce.fee_type  
INNER join school_fee sf on sf.fee_type_id=sce.fee_type and sf.academidyear_id=sce.accyr and sf.school_id=p_school_level where sce.student_id=p_student and sce.accyr=p_accy;
if EXISTS(SELECT * from students ss where ss.student_id=p_student and ss.reg_academic_year=p_accy)THEN
CREATE TEMPORARY TABLE view2 as
SELECT 9 as id,CURRENT_DATE() AS DATE,'Charge' as Type,'' AS Invoice,'Registration' as 'ACCOUNT',
'-' Month,'' MEMO,round(ifnull(
(SELECT sf.amount  from school_fee sf where sf.academidyear_id=p_accy and sf.school_id=p_school_level and sf.fee_type_id=9),0.00),2) Increase,
'0.00' as Descrease
UNION ALL
SELECT 1 as id,CURRENT_DATE() AS DATE,'Charge' as Type,'' AS Invoice,'Admission' as 'ACCOUNT',
'' Month,'' MEMO,round(ifnull(
(SELECT sf.amount  from school_fee sf where sf.academidyear_id=p_accy and sf.school_id=p_school_level and sf.fee_type_id=1),0.00),2) Increase,
'0.00' as Descrease 
UNION ALL
SELECT id,DATE,Type,Invoice,ACCOUNT,Month,MEMO,Increase,Descrease from view1;

ELSE
CREATE TEMPORARY TABLE view2 as
SELECT * from view1;
end if;
CREATE TEMPORARY TABLE view3 as
SELECT id,DATE,Type,Invoice,ACCOUNT,Month,MEMO,Increase,Descrease from view2
UNION ALL
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.months=cr.month 
where cr.student_id=p_student and cr.academic_year=p_accy and cr.status!=2
group by floor((months.id - 1) / 3),cr.boono;

SELECT  id,DATE,Type,Invoice,ACCOUNT,Month,MEMO,Increase,Descrease, @runtot:=@runtot - (Increase-Descrease) AS Balance from view3 ORDER by 
FIELD(id, "9", "1","7","2","8","4"),
FIELD(Month,'Aug,Sep,Oct','Nov,Dec,Jan','Feb,Mar,Apr','May,Jun,July');


END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_h_add_school_bus` (IN `p_id` INT, IN `p_student_id` INT, IN `p_bus_id` INT, IN `p_address` VARCHAR(50), IN `p_amount` DOUBLE, IN `p_company_id` INT, IN `p_user_id` INT)  NO SQL BEGIN
if EXISTS(SELECT * from school_bus where id = p_id) THEN
UPDATE school_bus sb set sb.student_id = p_student_id,sb.bus_id= p_bus_id,sb.Address = p_address,sb.company_id = sb.Amount = p_amount,sb.updated_user = p_user_id where id = p_id;
SELECT 'success| Xogtan si sax ah ayaa loo beddelay';

ELSEIF EXISTS(select * from school_bus where student_id=p_student_id) THEN
SELECT	'warning| Ardaygan hore ayaa bas loogu daray.';
ELSE
INSERT into school_bus (student_id,bus_id,Address,Amount,company_id,user_id) VALUES
(p_student_id,p_bus_id,p_address,p_amount,p_company_id,p_user_id);
SELECT 'success|Ardaygan waa lagu daray baska';
end IF;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_h_attendence` (IN `p_st_id` INT, IN `p_class_id` INT, IN `p_p1` VARCHAR(50), IN `p_p2` VARCHAR(50), IN `p_p3` VARCHAR(50), IN `p_p4` VARCHAR(50), IN `p_p5` VARCHAR(50), IN `p_p6` VARCHAR(50), IN `p_p7` VARCHAR(50), IN `p_remark` VARCHAR(50), IN `p_user_id` INT, IN `p_date` DATE)  NO SQL begin
if not EXISTS(select * from attendece where st_id=p_st_id and date=p_date) THEN
insert into attendece( st_id, class_id, p1, p2, p3, p4, p5, p6, p7, remark, date, user_id) VALUES ( p_st_id,p_class_id,p_p1,p_p2, p_p3, p_p4, p_p5, p_p6, p_p7, p_remark, p_date, p_user_id);
ELSE
update attendece set p1=p_p1 ,p2=p_p2,p3=p_p3,p4=p_p4,p5=p_p5,p6=p_p6,p7=p_p7,remark=p_remark,updated_user=p_user_id ,modified_date=p_date where st_id=p_st_id  and date=p_date;
end IF;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_h_fetch_student_bus` (IN `_id` INT)  NO SQL BEGIN
SELECT s.id,s.StudentName, concat( sc.school_name,' ',l.name) 'school_name',c.class_name,b.bus_name,sp.sponsor_name 'SponsoredBy',sb.acedemic_year,sb.Address,sb.Amount,sb.bus_id FROM students s join classes c on c.id = s.class_id inner join schools sc on sc.id=c.school_id inner JOIN levels l on l.id=sc.level_id inner JOIN school_bus sb on sb.student_id=s.id inner join buses b on b.id=sb.bus_id inner join sponsors sp on sp.id=s.sponsor_id where sb.student_id=_id
;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_h_fetch_transfer_students` (IN `_id` INT)  NO SQL begin
select st.id,st.StudentName,st.status,CONCAT(s.school_name,' ',l.name ) as school_name,c.class_name ,c.type,c.language,sp.sponsor_name 'SponsoredBy' from students st inner join classes c on c.id=st.class_id inner join schools s on s.id=c.school_id inner join levels l on l.id=s.level_id
inner join sponsors sp on sp.id=st.sponsor_id

where st.id=_id;
end$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_h_show_student_bus` (IN `_company_id` INT)  NO SQL BEGIN
SELECT s.id,s.StudentName, concat( sc.school_name,' ',l.name) 'school_name',c.class_name,b.bus_name,sp.sponsor_name FROM students s join classes c on c.id = s.class_id inner join schools sc on sc.id=c.school_id inner JOIN levels l on l.id=sc.level_id inner JOIN school_bus sb on sb.student_id=s.id inner join buses b on b.id=sb.bus_id
inner join sponsors sp on sp.id=s.sponsor_id 
where sc.company_id=_company_id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_h_show_student_classes` (IN `_class_id` INT)  NO SQL select s.id,s.StudentID,s.StudentName,CONCAT(sh.school_name,' ', l.name) as schoolname,c.class_name from students s inner join classes c on s.class_id=c.id inner join schools sh on sh.id=c.school_id INNER join levels l on l.id=sh.level_id 

where s.is_graduation=0 AND s.class_id=_class_id$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_h_transfer_classes` (IN `p_id` INT, IN `p_class_id` INT)  NO SQL BEGIN
update students set class_id=p_class_id where id=p_id;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_h_update_school_bus` (IN `_id` INT, IN `_bus_id` INT, IN `_deegaan` VARCHAR(50), IN `_lacagta` DOUBLE, IN `_user_id` INT)  NO SQL BEGIN

update school_bus 

set bus_id=_bus_id,Address=_deegaan,Amount=_lacagta,updated_user=_user_id

where student_id=_id;

SELECT 'success';

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_import_students` (IN `p_student_id` VARCHAR(5000), IN `p_student_name` VARCHAR(50), IN `p_gender` VARCHAR(50), IN `p_dob` DATE, IN `p_pob` VARCHAR(50), IN `p_address` VARCHAR(50), IN `p_orphan_status` VARCHAR(50), IN `p_phone` VARCHAR(50), IN `p_mother_name` VARCHAR(50), IN `p_fee_status` VARCHAR(50), IN `p_discount` DOUBLE, IN `p_reg_academic_year` VARCHAR(50), IN `p_reg_date` DATE, IN `p_blood_group` VARCHAR(50), IN `p_guardian_name` VARCHAR(50), IN `p_guardian_gender` VARCHAR(50), IN `p_guardian_address` VARCHAR(50), IN `p_guardian_phone` VARCHAR(50), IN `p_relation` VARCHAR(50), IN `p_class` VARCHAR(50), IN `p_user_id` INT)   BEGIN
IF EXISTS(SELECT * FROM sponsors sp where sp.sponsor_name = p_guardian_name and sp.mobile = p_phone) THEN
SELECT sp.id into @sponsor_id from sponsors sp where sp.sponsor_name = p_guardian_name and sp.mobile = p_guardian_phone;
ELSE INSERT INTO sponsors(`sponsor_name`,`gender`,`address`,`mobile`,user_id) VALUES(p_guardian_name,p_guardian_gender,p_guardian_address,p_phone,p_user_id);
SELECT LAST_INSERT_ID() INTO @sponsor_id;
END IF;
IF EXISTS(SELECT * FROM academicyear a WHERE a.Name = p_reg_academic_year) THEN
SELECT id into @academic_year FROM academicyear where Name = p_reg_academic_year;
ELSE 
INSERT INTO academicyear(name) VALUES(p_reg_academic_year);
SELECT LAST_INSERT_ID() INTO @academic_year;
END IF;
IF EXISTS(SELECT * FROM orphan_status o WHERE o.name = p_orphan_status) THEN
SELECT id into @orphan_status FROM orphan_status where name = p_orphan_status;
ELSE 
SELECT '1' INTO @orphan_status;
END IF;

insert into students(student_id,student_name,gender,address,dob,pob,mother_name,phone,blood_group,sponsor_id,relation,orphan_status,reg_date,reg_academic_year,class_id,user_id) Values(p_student_id,p_student_name,p_gender,p_address,date(p_dob),p_pob,p_mother_name,p_phone,p_blood_group, @sponsor_id,p_relation,@orphan_status,date(p_reg_date),@academic_year,p_class,p_user_id);
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_import_students_new` (IN `p_student_id` VARCHAR(5000), IN `p_student_name` VARCHAR(50), IN `p_gender` VARCHAR(50), IN `p_dob` VARCHAR(50), IN `p_pob` VARCHAR(50), IN `p_district` VARCHAR(50), IN `p_address` VARCHAR(50), IN `p_phone` VARCHAR(50), IN `p_mother_name` VARCHAR(50), IN `p_reg_academic_year` VARCHAR(50), IN `p_reg_date` DATE, IN `p_guardian_name` VARCHAR(50), IN `p_guardian_gender` VARCHAR(50), IN `p_guardian_address` VARCHAR(50), IN `p_guardian_phone` VARCHAR(50), IN `p_relation` VARCHAR(50), IN `p_class` VARCHAR(50), IN `p_user_id` INT)   BEGIN
START TRANSACTION; 
SELECT a.id into @current_academic FROM academicyear a where a.is_active='yes';


IF EXISTS(SELECT * FROM sponsors sp where sp.sponsor_name = TRIM(p_guardian_name)  and sp.mobile = TRIM(p_phone)) THEN
SELECT sp.id into @sponsor_id from sponsors sp where sp.sponsor_name = TRIM(p_guardian_name) and sp.mobile =TRIM( p_guardian_phone);
ELSE 
INSERT INTO sponsors(`sponsor_name`,`gender`,`address`,`mobile`,user_id) 
VALUES
(p_guardian_name,p_guardian_gender,p_guardian_address,p_phone,p_user_id);
SELECT max(id ) INTO @sponsor_id from sponsors;
END IF;




IF EXISTS(SELECT * FROM districts d where d.Districts = p_district ) THEN
SELECT sp.id into @district_id from districts sp where sp.Districts = p_district ;
ELSE INSERT INTO districts(Districts) VALUES(p_district);
SELECT LAST_INSERT_ID() INTO @district_id;
END IF;


IF EXISTS(SELECT * FROM academicyear a WHERE a.Name = TRIM(p_reg_academic_year)) THEN
SELECT id into @academic_year FROM academicyear where Name = p_reg_academic_year;
ELSE 
INSERT INTO academicyear(name,is_active) VALUES(p_reg_academic_year,'no');
SELECT LAST_INSERT_ID() INTO @academic_year;
END IF;



SELECT '1' INTO @orphan_status;

if not EXISTS(SELECT * from students where student_id=CONCAT('MOA-',p_student_id))THEN
           insert into students(student_id,student_name,gender,address,dob,pob,mother_name,phone,                    sponsor_id,relation,orphan_status,reg_date,reg_academic_year,class_id,user_id,district,current_academic) Values(CONCAT('MOA-',p_student_id),p_student_name,p_gender,p_address,date(p_dob),p_pob,p_mother_name,p_phone, @sponsor_id,p_relation,@orphan_status,date(p_reg_date),@academic_year,p_class,p_user_id,@district_id,
@current_academic);
insert into student_status_change(`student_id`, `to_status`, `detail`, `date`, `user_id`)
VALUES(CONCAT('MOA-',p_student_id),2,'Imported New Student',p_reg_date,p_user_id);
              ELSE
              SET @student_id=(SELECT s.Number+1 FROM setupid s where s.Name = "Students");
insert into students(student_id,student_name,gender,
                     address,dob,pob,mother_name,phone,                    sponsor_id,relation,orphan_status,reg_date,
                     reg_academic_year,class_id,user_id,district,current_academic) Values(CONCAT('MOA-',@student_id),p_student_name,p_gender,p_address,date(p_dob),p_pob,p_mother_name,p_phone, @sponsor_id,p_relation,@orphan_status,date(p_reg_date),@academic_year,p_class,p_user_id,@district_id,
@current_academic);
insert into student_status_change(`student_id`, `to_status`, `detail`, `date`, `user_id`)
VALUES(CONCAT('MOA-',@student_id),2,'Imported New Student',p_reg_date,p_user_id);
               UPDATE setupid set Number = Number+1 where Name = "Students";
END if;

COMMIT;
              END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_import_topics` (IN `p_academic_year_id` INT, IN `p_class` INT, IN `p_subject_id` INT, IN `p_lesson` TEXT, IN `p_name` TEXT, IN `p_Objectives` TEXT, IN `p_Reference` TEXT, IN `p_Assignment` TEXT, IN `p_Teaching_Method` TEXT, IN `p_Practical` TEXT, IN `p_Practical_Detail` TEXT, IN `p_from_Date` DATE, IN `p_to_Date` DATE, IN `p_Remarks` TEXT, IN `p_teacher` TEXT, IN `p_user_id` INT)  NO SQL BEGIN

if EXISTS (SELECT * from lesson ls where ls.name=p_lesson and ls.academic_year_id=p_academic_year_id and ls.subject_group_subject_id=p_subject_id and ls.subject_group_class_sections_id=p_class)THEN
SELECT DISTINCT ls.id into @lesson_id from lesson ls where ls.name=p_lesson and ls.academic_year_id=p_academic_year_id and ls.subject_group_subject_id=p_subject_id and ls.subject_group_class_sections_id=p_class;
ELSE
INSERT INTO lesson(subject_group_subject_id,subject_group_class_sections_id,name,academic_year_id,user_id)
VALUES(p_subject_id,p_class,p_lesson,p_academic_year_id,p_user_id);
SELECT LAST_INSERT_ID() INTO @lesson_id;
end if;
INSERT INTO `topic`( `lesson_id`, `name`, `academic_year_id`, `Objectives`, `Reference`, `Assignment`, `Teaching_Method`, `Practical`, `Practical_Detail`, `from_Date`,to_date, `Remarks`, `user_id`, `date`) VALUES
(@lesson_id, p_name, p_academic_year_id, p_Objectives, p_Reference, p_Assignment, p_Teaching_Method,p_Practical, p_Practical_Detail, p_from_Date,p_to_Date, p_Remarks, p_user_id, CURRENT_DATE());


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

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 lesson_teacher  WHERE class_id = p_class and subject_id=p_subject_id and accy =p_academic_year_id  and teacher_id not in (SELECT val from temp_classes );



INSERT IGNORE INTO `lesson_teacher`( subject_id, accy, class_id, teacher_id)
select p_subject_id,p_academic_year_id,p_class,val from temp_classes;



END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_kormeer` (IN `p_id` INT, IN `p_teacher` INT, IN `p_kormeer_id` TEXT, IN `p_kormeer` TEXT, IN `p_points` TEXT, IN `p_date` DATE, IN `p_user_id` INT)  NO SQL BEGIN
DECLARE i int;
SELECT e.qeybta into @qeybta from employee e where e.id=p_teacher;
SELECT a.id into @current FROM academicyear A WHERE  A.is_active='yes';
create  table t(id text,kormeer text,points text);
insert into t values(p_kormeer_id,p_kormeer,p_points);
        drop  table  if exists a ;
create  table a(id text,kormeer text,points text);
SELECT LENGTH(id) - LENGTH(REPLACE(id, ',', '')) + 1,LENGTH(kormeer) - LENGTH(REPLACE(kormeer, ',', '')) + 1,LENGTH(points) - LENGTH(REPLACE(points, ',', '')) + 1 into @id,@kormeer,@points FROM `t` ;
set i=1;
WHILE i<=@id DO
insert into a(id,kormeer,points)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(id, ',', i), ',', -1), if(i>@kormeer,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(kormeer, ',', i), ',', -1)),if(i>@points,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(points, ',', i), ',', -1))  FROM `t`;
 SET i = i + 1;
 END WHILE;
 
if EXISTS(SELECT * from kormeer_info where id=p_id)THEN
 SELECT sum(points) into @points from a;
 update kormeer_info set marks=@points 
 where id=p_id;
 set @kormeer_info=p_id;
 ELSE
 INSERT INTO `kormeer_info`( `teacher_id`,qaybta, `academic_year`,  `marks`, `user_id`, `date`)
 
 SELECT p_teacher,@qeybta,@current,sum(points),p_user_id,p_date from a;
 
 set @kormeer_info=(SELECT LAST_INSERT_ID()); 
 END if;
   INSERT into kormeer_points(kormeer_info, kormeer_id,points)
SELECT @kormeer_info,kormeer,points
FROM   a
WHERE  NOT EXISTS (SELECT 1 
                   FROM   kormeer_points 
                   WHERE  a.ID = kormeer_points.ID and kormeer_points.kormeer_info=@kormeer_info);
                   UPDATE kormeer_points fe
        INNER JOIN a a 
             ON a.id = fe.id
SET fe.points = a.points ;

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

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_login` (IN `p_user_name` VARCHAR(50), IN `p_password` VARCHAR(50))   BEGIN
  IF EXISTS (SELECT * FROM user u WHERE u.user_name = p_user_name 
             AND u.password = p_password AND u.status = 0) THEN

    SELECT u.id AS user_id, em.id, em.name AS full_name, c.name, c.contact,
           CONCAT('../../', c.logo) AS logo, c.address, c.id AS profile_id,
           rl.name AS Role, rl.id AS role_id, CONCAT('../../', em.image) AS image,
           u.user_name, c.email, "success" AS msg,
           "lib/Dashboard/index.php" AS dashboard, case when em.school=0 then '%' else em.school end as school
    FROM user u 
    INNER JOIN roles rl ON rl.id = u.role_id AND rl.id NOT IN (8, 9,21)
    INNER JOIN employee em ON em.id = u.employee_id
    JOIN company_profile c ON c.id = u.profile
    WHERE u.user_name = p_user_name AND u.password = p_password 

    UNION ALL

    SELECT u.id AS user_id, sp.id, sp.sponsor_name AS full_name, c.name, c.contact,
           CONCAT('../../', c.logo) AS logo, c.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/parent.php" AS dashboard,'' as school
    FROM user u 
    INNER JOIN roles rl ON rl.id = u.role_id AND rl.id = 21
    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 AND u.password = p_password 

    UNION ALL

    SELECT u.id AS user_id, ss.id, ss.student_name AS full_name, c.name, c.contact,
           CONCAT('../../', c.logo) AS logo, c.address, c.id AS profile_id,
           rl.name AS Role, rl.id AS role_id, CONCAT('../../', ss.photo) AS image,
           u.user_name, c.email, "success" AS msg, 
           "lib/Dashboard/student.php" AS dashboard, '' as school
    FROM user u 
    INNER JOIN roles rl ON rl.id = u.role_id AND rl.id = 8
    INNER JOIN students ss ON ss.id = u.employee_id
    JOIN company_profile c ON c.id = u.profile
    WHERE u.user_name = p_user_name AND u.password = p_password ;

  ELSE
    SELECT "Username or password is incorrect" AS msg;
  END IF;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_login1` (IN `p_username` VARCHAR(50), IN `p_password` VARCHAR(500))  NO SQL BEGIN
if EXISTS(SELECT * FROM
          staff_user WHERE user_name=p_username and password =p_password) THEN
select 'success' as msg, su.id as 'id',user_name,company_id,image,f.name 'company_name',f.Logo,su.full_name from staff_user su join foundation_profile f on su.company_id = f.id where  user_name=p_username and password =p_password;
ELSE	
SELECT "Userka ama passwordka ayaa qaldan" as msg;
end if;
end$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_login2` (IN `p_user_name` VARCHAR(50), IN `p_password` VARCHAR(50))  NO SQL BEGIN
IF EXISTS(SELECT * FROM user u WHERE u.user_name=p_user_name and u.password=p_password and u.status=0) THEN
SELECT u.id as user_id,em.id,em.name as full_name,c.name,c.contact,concat('../../',c.logo) as logo,c.address,c.id 'profile_id',rl.name as Role,rl.id as role_id,concat('../../',em.image)as image,u.user_name,c.email,"success" msg,"lib/Dashboard/index.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 employee em on em.id=u.employee_id
JOIN company_profile c on c.id=u.profile
WHERE u.user_name=p_user_name and u.password=p_password
UNION ALL
SELECT u.id as user_id,ss.id,ss.student_name as full_name,c.name,c.contact,concat('../../',c.logo) as logo,c.address,c.id 'profile_id',rl.name as Role,rl.id as role_id,concat('../../',ss.photo) as image,u.user_name,c.email,"success" msg,"lib/Dashboard/student.php" as dashboard FROM user u 
INNER JOIN roles rl on rl.id=u.role_id and rl.id=8
INNER JOIN students ss on ss.id=u.employee_id
JOIN company_profile c on c.id=u.profile
WHERE u.user_name=p_user_name and u.password=p_password;

ELSE
	SELECT "Username or password is incorrect" msg;
END IF;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_menu` (IN `p_id` INT, IN `p_menu` VARCHAR(100), IN `p_icon` VARCHAR(100))  NO SQL BEGIN
if EXISTS(SELECT * from menu mn where mn.id=p_id)THEN
UPDATE menu mn set mn.name=p_menu,mn.icon=p_icon where id=p_id;
SELECT 'success|Si sax ah aya loo beddelay';
ELSE
INSERT into menu(name,icon)VALUES(p_menu,p_icon);
select 'success|Baskan waa la diiwaangaliyey';
end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_not_charged_search_by_class` (IN `p_class_id` VARCHAR(50), IN `p_level` VARCHAR(50), IN `p_section` VARCHAR(50), IN `p_accy` INT, IN `p_user_id` INT)  NO SQL BEGIN
SELECT ac.id INTO @accy from academicyear ac where ac.is_active='yes';

SELECT 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', ssp.to_class as class_id 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 
 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 
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 `sp_not_charged_search_by_class1` (IN `p_class_id` VARCHAR(50), IN `p_level` VARCHAR(50), IN `p_section` VARCHAR(50), IN `p_accyr` INT, IN `p_fee_type` INT, IN `p_user_id` INT)  NO SQL BEGIN
CREATE TEMPORARY TABLE view1 as
SELECT @rownum:=@rownum+1 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', 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
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, (SELECT @rownum:=0) r 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 ;

SELECT * from view1 where student_id not in(SELECT sc.student_id from student_charge sc where sc.class in (SELECT cs.id from class_sections cs where cs.class_id=p_class_id and cs.id like p_section) and sc.accyr=p_accyr and sc.fee_type=p_fee_type);
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_permission` (IN `p_user_id` INT, IN `p_sidebar` INT, IN `p_added` INT, IN `p_edited` INT, IN `p_deleted` INT)  NO SQL BEGIN


if not EXISTS(SELECT * from permission where profile_permission=p_sidebar and user_id=p_user_id) then
insert into permission (profile_permission,added,edited,deleted,user_id) values (p_sidebar,p_added,p_edited,p_deleted,p_user_id);

else
UPDATE permission  set added=p_added,edited=p_edited,deleted=p_deleted where 
profile_permission=p_sidebar and user_id=p_user_id;
DELETE from permission where profile_permission=p_sidebar and user_id=p_user_id and added=0 and edited=0 and deleted=0;
end if;


END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_permission_employee` (IN `p_user_id` INT, IN `p_qaybta` INT, IN `p_action` VARCHAR(50))  NO SQL BEGIN

if (p_action = 'grant') THEN
if not EXISTS(SELECT * from employee_permission where qaybta=p_qaybta and user_id=p_user_id) then
insert into employee_permission (qaybta,user_id) values (p_qaybta,p_user_id);
end if;
else
delete from employee_permission where user_id = p_user_id  and qaybta = p_qaybta ;
end if;


END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_permission_level` (IN `p_user_id` INT, IN `p_level` INT, IN `p_action` VARCHAR(50))  NO SQL BEGIN

if (p_action = 'grant') THEN
if not EXISTS(SELECT * from level_permission where level_id=p_level and user_id=p_user_id) then
insert into level_permission (level_id,user_id) values (p_level,p_user_id);
end if;
else
delete from level_permission where user_id = p_user_id  and level_id = p_level ;
end if;


END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_replace` (IN `p_a` TEXT, IN `p_b` TEXT)  NO SQL BEGIN
DECLARE i INT DEFAULT 0;
        drop  table if exists t ;
create  table t(A text,B text);
insert into t values(p_a,p_b);
        drop  table if exists a ;
create  table a(A text,B text,I TEXT);
SELECT LENGTH(A) - LENGTH(REPLACE(A, ',', '')) + 1,LENGTH(B) - LENGTH(REPLACE(B, ',', '')) + 1 into @count_A,@count_B FROM `t` ;
set i=1;
WHILE i<=@count_A DO
insert into a(A,B,I)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(A, ',', i), ',', -1)as "A", if(i>@count_B,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(B, ',', i), ',', -1)) as "B",I FROM `t`;
 SET i = i + 1;
END WHILE;
SELECT * FROM a;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_role_permission` (IN `p_role_id` VARCHAR(10), IN `p_sidebar` VARCHAR(10), IN `p_can_view` VARCHAR(10), IN `p_can_add` VARCHAR(10), IN `p_can_edit` VARCHAR(10), IN `p_can_delete` VARCHAR(10))  NO SQL BEGIN


if not EXISTS(SELECT * from roles_permissions rp where rp.sidebar_id=p_sidebar and rp.role_id=p_role_id) then

INSERT INTO `roles_permissions`( `role_id`, `sidebar_id`, `can_view`, `can_add`, `can_edit`, `can_delete`)VALUES(p_role_id,p_sidebar,p_can_view,p_can_add,p_can_edit,p_can_delete);

else

UPDATE roles_permissions rp set rp.can_view=p_can_view , rp.can_add=p_can_add , rp.can_edit=p_can_edit , rp.can_delete=p_can_delete where rp.role_id=p_role_id and rp.sidebar_id=p_sidebar;

end if;
DELETE from roles_permissions WHERE  can_view=0 and can_add=0 and can_edit=0 and can_delete=0;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_search_assessment` (IN `p_week` INT, IN `p_month` VARCHAR(10), IN `p_state` INT)  NO SQL BEGIN


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

if (p_state=1)THEN

SELECT em.name,em.galinka,ai.month,ai.week,ai.date,u.user_name  from assessment_info ai INNER join  employee em on em.id=ai.employee_id INNER join user u on u.id=ai.user_id where ai.week=p_week and ai.month=p_month and ai.accy=@current;
ELSE

SELECT	 em.name,em.galinka,'N/A' as month,'N/A' as week,'N/A' as date,'N/A' as user_name  from employee em where em.id not in (SELECT ai.employee_id from assessment_info ai where ai.week=p_week and ai.month=p_month and ai.accy=@current) and em.qeybta in (2,37,8,9);
end if;
END$$

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

SELECT @rownum:=@rownum+1 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('<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 student_promotion ssp on ssp.student_id=s.student_id
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 
 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 
inner 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 in (1,2) AND ssp.from_academic !=61
ORDER by s.id ASC ;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_search_by_other` (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', 

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 student_promotion ssp on ssp.student_id=s.student_id
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 
 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 (s.student_id like p_other or s.student_name like p_other)

and ssp.from_academic!=61 and s.status in (2,1) 
ORDER by s.id ASC ;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_search_StudentSponsors` (IN `p_acedemic_year` INT, IN `p_sponser` INT)  NO SQL BEGIN

CREATE TEMPORARY TABLE  view1 as
SELECT ss.id,ss.student_id,ss.student_name,concat(sc.school_name,'',l.name) as school_name,concat(c.class_name,'-',se.section) as class_name,sf.amount,ss.current_academic 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 school_fee sf on sf.school_id=sl.id and sf.fee_type_id=7 and sf.academidyear_id=sp.from_academic where ss.sponsor_id=p_sponser and sp.from_academic=p_acedemic_year and ss.status in (1,2)   ORDER BY  concat(c.class_name ,'-',se.section) DESC;




select * from view1;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_show_bus` (IN `p_company_id` INT)  NO SQL BEGIN
select * from buses WHERE company_id=p_company_id;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_show_class` (IN `_company_id` INT)  NO SQL BEGIN
SELECT c.id,CONCAT(s.school_name,' ',l.name ) as schoolname,c.class_name,c.type,c.language FROM classes c inner join schools s on s.id=c.school_id inner join levels l on l.id=s.level_id WHERE c.company_id=_company_id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_show_fee_type` (IN `_company_id` INT)  NO SQL BEGIN
select * from fee_type where company_id = _company_id;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_show_levels` (IN `_company_id` INT)  NO SQL BEGIN
select * from levels WHERE company_id = _company_id;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_show_Main_Menu` (IN `p_role` INT, IN `p_company` INT)  NO SQL BEGIN
select DISTINCT m.id,m.name as menu,m.icon FROM menu m inner join sidebar s on s.menu_id=m.id
 INNER join company_permission cp on cp.sidebar_id=s.id   join roles_permissions rp on rp.role_id=p_role and rp.sidebar_id=cp.id where 
 cp.profile= p_company GROUP by m.id order by cp.id asc;




end$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_show_pass_mark` (IN `_company_id` INT)  NO SQL BEGIN
SELECT p.id,l.name,p.type,p.language,p.marks FROM pass_mark p inner join levels l on l.id=p.level_id WHERE p.company_id=_company_id;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_show_receipt_method` (IN `_company_id` INT)  NO SQL BEGIN
select * from receipt_method WHERE company_id = _company_id;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_show_schools` (IN `_company_id` INT)  NO SQL BEGIN
SELECT s.id,CONCAT(s.school_name,'-',l.name ) as schoolname,s.address,s.phone FROM schools s INNER JOIN levels l ON s.level_id = l.id where s.company_id = _company_id;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_show_school_fee` (IN `p_company_id` INT)  NO SQL BEGIN
select fs.id, concat(s.school_name,' ',l.name) as 'School',ft.fee_name 'Fee_type',fs.amount ,a.Name 'Academic_Year' from school_fee fs join academicyear a on a.id = fs.academidyear_id join fee_type ft on ft.id = fs.fee_type_id join schools s on s.id = fs.school_id JOIN levels l on s.level_id = l.id WHERE fs.company_id = p_company_id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_show_sponsor` (IN `_company_id` INT)  NO SQL BEGIN
select * from sponsors where company_id =_company_id;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_show_status` (IN `_company_id` INT)  NO SQL BEGIN
select * from status WHERE company_id=_company_id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_show_student_fee` (IN `_student_id` INT, IN `_fee_id` INT, IN `_academic_year_id` INT)  NO SQL BEGIN
SELECT `Fee_Status` into @fee_status FROM `students` WHERE id = _student_id;
if(@fee_status='free') and _fee_id =7 then
select 'free' as amount;
elseif(_fee_id =8) then
SELECT s.id,s.StudentName,sb.Amount amount FROM school_bus sb join students s on s.id = sb.student_id where s.id = _student_id;
else
SELECT s.id 'Student Id',s.StudentName,c.class_name, sc.id 'School Id',sc.school_name,sf.school_id 'School',ft.fee_name,a.Name 'Academic year',if(_fee_id =7,sf.amount-ifnull(s.discount,0),sf.amount) as amount from  students s join classes c on s.class_id = c.id join  schools sc on c.school_id = sc.id join school_fee sf on sf.school_id = sc.id join fee_type ft on sf.fee_type_id = ft.id join academicyear a on sf.academidyear_id = a.id where s.id = _student_id and ft.id = _fee_id and a.id = _academic_year_id  ORDER BY s.id;
end if;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_show_student_reciept` (IN `_student_id` INT)  NO SQL BEGIN
SELECT s.id ,s.StudentName,concat(sc.school_name,' ',l.name) school,c.class_name,sp.sponsor_name FROM students s join classes c on c.id = s.class_id join schools sc on sc.id = c.school_id join levels l on l.id = sc.level_id join sponsors sp on s.sponsor_id = sp.id where s.id= _student_id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_show_subject` (IN `_company_id` INT)  NO SQL BEGIN
select * from subjects WHERE company_id=_company_id;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_show_Sub_Menu` (IN `p_role` INT, IN `p_company` INT, IN `p_menu` INT)  NO SQL BEGIN
if (p_role=8)THEN
SELECT  s.`id` as `sidebar`, s.`text`,concat( '../../',s.`href`) as href  FROM sidebar s inner join company_permission cp on s.id=cp.sidebar_id  join roles_permissions rp on rp.sidebar_id=cp.id where
 cp.profile=p_company  AND rp.role_id=p_role order by cp.id ASC;
 ELSE
SELECT  s.`id` as `sidebar`, s.`text`,concat( '../../',s.`href`) as href  FROM sidebar s inner join company_permission cp on s.id=cp.sidebar_id  join roles_permissions rp on rp.sidebar_id=cp.id where menu_id = p_menu and 
 cp.profile=p_company  AND rp.role_id=p_role order by cp.id ASC;

END if;

end$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_show_teachers` (IN `_company_id` INT)  NO SQL BEGIN
select * from teachers WHERE company_id=_company_id;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_single_assessment_summary` (IN `p_employee` INT, IN `p_no` VARCHAR(50), IN `p_month` VARCHAR(100), IN `p_academic_year` INT)  NO SQL BEGIN
SELECT (case WHEN p_no='%'THEN sum(ass.marks) else sum(ass.marks/4) end) into @sum_all from assessment ass;
SELECT sum(ai.total_marks) into @total from assessment_info ai where ai.employee_id=p_employee and
ai.accy=p_academic_year and ai.month=p_month and ai.week like p_no ;
SELECT ROUND( ( sum( @total)/@sum_all)*100,2)  into @avarage  ;
SELECT avarage,class into @grade,@class from assessment_avarage  where assessment_avarage.From >=@avarage and assessment_avarage.to<=@avarage;
SELECT ass.points,ROUND((case WHEN p_no='%'THEN ass.marks else ass.marks/4 end),2) as full_mark,(case WHEN p_no='%'THEN sum(ad.marks) else ad.marks end) as marks,ad.remark, @total as total, @avarage as avarage,@grade as grade,@class as class from  assessment_info ai INNER join assessment_detail ad on ad.assessment_info=ai.id INNER JOIN assessment ass on ad.point_id=ass.id where ai.employee_id=p_employee and
ai.accy=p_academic_year and ai.month=p_month and ai.week like p_no GROUP by ass.id;


END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_single_evolution_all_summary` (IN `p_qaybta` VARCHAR(100), IN `p_academic_year` INT)  NO SQL BEGIN
SELECT sum(f.Points)*4 into @sum_all from f_evolution f;
CREATE TEMPORARY table evolution
SELECT concat('E-',e.id) id,e.name,q.Name 'Qaybta',COUNT(f.teacher_id) 'No',sum(f.marks) 'marks',ROUND( ( sum(f.marks)/@sum_all)*100,2)  as AVG, (SELECT Grade from avg_emp av where av._from >= ROUND( ( sum(f.marks)/@sum_all)*100,2) and av._to <= ROUND( ( sum(f.marks)/@sum_all)*100,2)) as Grade,(SELECT Detail from avg_emp av where av._from >= ROUND( ( sum(f.marks)/@sum_all)*100,2) and av._to <= ROUND( ( sum(f.marks)/@sum_all)*100,2)) as Detail  from f_evolution_info f 
INNER join employee e on e.id=f.teacher_id 
INNER join qaybta q on q.id=f.qaybta and q.id in (7,8,9)
INNER join academicyear ac on ac.id=f.academic_year
where q.id like p_qaybta and ac.id=p_academic_year
GROUP by  ac.id,e.id
;

SELECT * from evolution order by marks desc;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_single_evolution_point_summary` (IN `p_employee` INT, IN `p_no` VARCHAR(50), IN `p_academic_year` INT)  NO SQL BEGIN
SELECT ifnull(fep.id,0) as id,fe.id 'evolution',fe.Qiimaynta,fei.No_times,fe.Points,ifnull(fep.points,0) 'get',fei.date,u.user_name from employee e 
        LEFT join f_evolution_info fei on fei.teacher_id=e.id and fei.teacher_id=p_employee and (
CASE
when p_no='%' THEN
 fei.No_times like '%'
ELSE
fei.No_times in (p_no)
END)  and fei.academic_year=p_academic_year
        INNER JOIN f_evolution_points fep on fep.evolution_info=fei.id
        INNER join users u on u.id=fei.user_id
        LEFT JOIN f_evolution fe  on fe.id=fep.evolution_id;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_single_evolution_summary` (IN `p_employee` INT, IN `p_no` VARCHAR(50), IN `p_academic_year` INT)  NO SQL BEGIN
IF EXISTS(SELECT * from f_evolution_info fe where fe.teacher_id=p_employee and
fe.academic_year=p_academic_year)THEN

SELECT  fe.id,fe.teacher_id,COUNT(fe.teacher_id) as No_times,sum(fe.marks) AS points,ay.Name as academicyear  FROM f_evolution_info fe INNER join academicyear ay on ay.id=fe.academic_year  where fe.teacher_id=p_employee and
fe.academic_year=p_academic_year
AND
(
CASE
when p_no='%' THEN
 fe.No_times like '%'
ELSE
fe.No_times in (p_no)
END) 
GROUP by
fe.academic_year
;
ELSE
SELECT ac.Name as academicyear from academicyear ac where ac.id;
end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_single_evolution_sum_summary` (IN `p_employee` INT, IN `p_no` VARCHAR(50), IN `p_academic_year` INT)  NO SQL BEGIN
IF EXISTS(SELECT * from f_evolution_info fe where fe.teacher_id=p_employee and
fe.academic_year=p_academic_year)THEN
SELECT sum(f.Points)*4 into @sum_all from f_evolution f;
SELECT sum(fei.marks) AS points,ROUND( ( sum(fei.marks)/@sum_all)*100,2)  as avarage from employee e 
        LEFT join f_evolution_info fei on fei.teacher_id=e.id and fei.teacher_id=p_employee and (
CASE
when p_no='%' THEN
 fei.No_times like '%'
ELSE
fei.No_times in (p_no)
END)  and fei.academic_year=p_academic_year
GROUP by fei.academic_year
     ;
     
     ELSE
     SELECT 0 as points,0 as avarage ;
     end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_single_investigator_all_summary` (IN `p_qaybta` VARCHAR(100), IN `p_academic_year` INT)  NO SQL BEGIN
SELECT sum(k.points*k.no) into @sum_all from kormeer k;
CREATE TEMPORARY table evolution
SELECT concat('E-',e.id) id,e.name,q.Name 'Qaybta',sum(k.marks) 'marks',ROUND( ( sum(k.marks)/@sum_all)*100,2)  as AVG, (SELECT Grade from avg_emp av where av._from >= ROUND( ( sum(k.marks)/@sum_all)*100,2) and av._to <= ROUND( ( sum(k.marks)/@sum_all)*100,2)) as Grade,(SELECT Detail from avg_emp av where av._from >= ROUND( ( sum(k.marks)/@sum_all)*100,2) and av._to <= ROUND( ( sum(k.marks)/@sum_all)*100,2)) as Detail  from kormeer_info k 
INNER join employee e on e.id=k.teacher_id 
INNER join qaybta q on q.id=k.qaybta and q.id in (7,8,9)
INNER join academicyear ac on ac.id=k.academic_year
where q.id like p_qaybta and ac.id=p_academic_year
GROUP by  ac.id,e.id
;

SELECT * from evolution order by marks desc;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_single_investigator_point_summary` (IN `p_employee` INT, IN `p_investigator` VARCHAR(50), IN `p_academic_year` INT)  NO SQL BEGIN
SELECT k.kormeer,k.points,ifnull(kp.points,0) 'get',ki.date,u.user_name from employee e 
        LEFT join kormeer_info  ki on ki.teacher_id=e.id and ki.teacher_id=p_employee  and KI.academic_year=p_academic_year
        INNER JOIN kormeer_points kp on kp.kormeer_info=ki.id
        INNER join users u on u.id=ki.user_id
        LEFT JOIN kormeer k  on k.id=kp.kormeer_id
        and (
CASE
            
when p_investigator='%' THEN
 k.id like '%'
ELSE
k.id in (p_investigator)
END) 
        ;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_students_change` (IN `p_student_id` VARCHAR(100), IN `p_id` INT, IN `p_from_month` INT, IN `p_to_class` INT, IN `p_to_month` INT, IN `p_user_id` INT)  NO SQL BEGIN




START TRANSACTION;

SELECT ss.class_id,ss.current_academic into @class,@accy from students ss where ss.student_id=p_student_id;
update  students set class_id=p_to_class where student_id=p_student_id;
UPDATE cash_receipt cr set cr.class_id=p_to_class where cr.student_id=p_student_id  and cr.class_id=@class and cr.academic_year=@accy;
UPDATE student_promotion sp set sp.to_class=p_to_class,sp.from_month=p_from_month,sp.to_month=p_to_month where sp.id=p_id;


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

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_students_promote` (IN `p_student_id` TEXT, IN `p_from_class` INT, IN `p_from_month` INT, IN `p_to_class` INT, IN `p_to_month` INT, IN `p_user_id` INT)  NO SQL 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 limit 1 ), ",", "'),('"),"');");
prepare stmt2 from @sql2;
execute stmt2;

START TRANSACTION;
 if(p_from_month='200')THEN
 set p_from_month=2;
 END if;

set @academic_year=(select DISTINCT current_academic from students where student_id  in (select val from temp_student));
set @current_academic=(SELECT id from academicyear where is_active='yes');

update  students set current_academic=@current_academic,class_id=p_to_class where student_id in (select val from temp_student) and  NOT EXISTS (select * from student_promotion sp WHERE sp.student_id in (select val from temp_student)  and sp.from_academic=@current_academic and sp.to_class=p_to_class);

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_academic   and to_class=p_from_class ;

INSERT IGNORE into student_promotion(student_id,from_class,from_academic,from_month,to_class,to_month,user_id)
select val,p_from_class,@current_academic,p_from_month,p_to_class,p_to_month,p_user_id from temp_student where NOT EXISTS (select * from student_promotion sp WHERE sp.student_id=val and sp.from_academic=@current_academic and sp.to_class=p_to_class);


SELECT 'success| record saved successfully';
COMMIT;






END$$

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


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

drop temporary table if exists temp_student;
create temporary 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;


set @academic_year=(select DISTINCT current_academic from students where student_id  in (select val from temp_student));
set @current_academic=(SELECT id from academicyear where is_active='yes');


if(@academic_year!=@current_academic) THEN
INSERT into student_promotion(student_id,from_class,from_academic,to_class,user_id)
select val,p_from_class,@academic_year,p_to_class,p_user_id from temp_student;
update students set current_academic=@current_academic,class_id=p_to_class where student_id in (select val from temp_student);
ELSe
update students set class_id=p_to_class where student_id in (select val from temp_student);
end if;
SELECT 'success| record saved successfully';
COMMIT;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_studentuncharge` (IN `p_chargeno` VARCHAR(100), IN `p_student_id` TEXT)   BEGIN
DECLARE i int;
START TRANSACTION;

create  table t(student text);
insert into t values(p_student_id);

     drop  table  if exists a ;
create  table a(student text);
SELECT LENGTH(student) - LENGTH(REPLACE(student, ',', '')) + 1 into @student FROM `t` ;
set i=1;
WHILE i<=@student DO
insert into a(student)
SELECT  if(i>@student,Null,SUBSTRING_INDEX(SUBSTRING_INDEX(student, ',', i), ',', -1))  FROM `t`;
 SET i = i + 1;
 END WHILE;
 
 DELETE from student_charge where Chargeno=p_chargeno and student_id in (SELECT student from a);
 select 'success| waa la UnCharge gareeyay';
 
 
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_student_changestatus` (IN `p_student_id` VARCHAR(1000), IN `p_status` INT, IN `p_detail` VARCHAR(1000), IN `p_date` DATE, IN `user_id` INT)  NO SQL BEGIN
create table s( txt text );
insert into s values(p_student_id);

drop temporary table if exists temp_student;
create temporary 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;

set @active_academic=(select a.id from academicyear a where a.is_active='yes');
update students set status =p_status,current_academic=@active_academic  where student_id in (select val from temp_student);
insert into student_status_change(`student_id`, `to_status`, `detail`, `date`, `user_id`)
select val,p_status,p_detail,p_date,user_id from temp_student;

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

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_student_disabled` (IN `p_student_id` VARCHAR(1000), IN `p_status` INT, IN `p_date` DATE, IN `p_detail` VARCHAR(1000), IN `user_id` INT)  NO SQL BEGIN


START TRANSACTION;

set @active_academic=(select a.id from academicyear a where a.is_active='yes');
if (p_status=1)THEN
update students set status =2,current_academic=@active_academic  where student_id =p_student_id;
ELSE

update students set status =3,current_academic=@active_academic  
where student_id =p_student_id;
END if;


insert into student_status_change(`student_id`, `to_status`, `detail`, `date`, `user_id`)
VALUES(p_student_id,p_status,p_detail,p_date,user_id);

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

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_student_graduation` (IN `p_student_id` VARCHAR(1000), IN `user_id` INT)  NO SQL BEGIN
create table s( txt text );
insert into s values(p_student_id);

drop temporary table if exists temp_student;
create temporary 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;
set @academic_year=(SELECT DISTINCT current_academic  from students where student_id in (select val from temp_student) );
update students set is_graduation=1,status =4,g_academicyear =@academic_year  where student_id in (select val from temp_student);
SELECT 'success| record saved successfully';
COMMIT;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_subject_track` (IN `p_accy` INT, IN `p_class` INT, IN `p_section` VARCHAR(50), IN `p_subject` INT, IN `p_date` DATE, IN `p_week` INT, IN `p_lesson` TEXT, IN `p_user_id` INT)   BEGIN

INSERT INTO subject_tracking(accy, class, section,subject_id,date,week_id,lesson,user_id) VALUES 
(p_accy, p_class, p_section, p_subject,p_date, p_week,p_lesson,p_user_id);

Select 'success|casharka waa ladiwan galiyay';

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_sub_menu` (IN `p_id` INT, IN `p_menu` INT(100), IN `p_text` VARCHAR(1000), IN `p_href` TEXT, IN `p_enable_view` INT, IN `p_enable_add` INT, IN `p_enable_edit` INT, IN `p_enable_delete` INT, IN `p_is_system` INT, IN `p_is_admin` INT, IN `p_is_student` INT, IN `p_is_parent` INT)  NO SQL BEGIN
if EXISTS(SELECT * from sidebar sd where sd.id=p_id)THEN
UPDATE `sidebar` SET `menu_id`=p_menu,`href`=p_href,`text`=p_text,
`enable_view`=p_enable_view,`enable_add`=p_enable_add,
`enable_edit`=p_enable_edit,`enable_delete`=p_enable_delete,
`is_system`=p_is_system,`is_admin`=p_is_admin,
`is_student`=p_is_student,`is_parent`=p_is_parent WHERE id=p_id;
SELECT 'success|Si sax ah aya loo beddelay';
ELSE
INSERT INTO `sidebar`( `menu_id`, `href`, `text`, `enable_view`, `enable_add`, `enable_edit`, `enable_delete`, `is_system`, `is_admin`, `is_student`, `is_parent`, `date`) VALUES 
(p_menu,p_href,p_text,p_enable_view,p_enable_add,p_enable_edit,p_enable_delete,p_is_system,p_is_admin,p_is_student,p_is_parent
,CURRENT_DATE());
select 'success|Baskan waa la diiwaangaliyey';
end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_tracking` (IN `p_type` INT, IN `p_user` VARCHAR(100), IN `p_from` DATE, IN `p_to` DATE)   BEGIN
if(p_type=0)THEN

SELECT em.name as name,rl.name as Role,lg.log,date(lg.date) AS Date,time(lg.date) as time FROM logs lg JOIN user  us on us.id=lg.user JOIN employee em on em.id=us.employee_id JOIN roles rl on rl.id=lg.role where em.id like p_user and date(lg.date) BETWEEN p_from and p_to  ORDER by lg.id DESC;


ELSE
SELECT em.name as name,rl.name as Role,lg.log,date(lg.date) AS Date,time(lg.date) as time FROM logs lg JOIN user  us on us.id=lg.user JOIN employee em on em.id=us.employee_id JOIN roles rl on rl.id=lg.role where rl.id = p_user and date(lg.date) BETWEEN p_from and p_to ORDER by lg.id DESC;
END if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_update_exam_class` (IN `p_id` INT)  NO SQL BEGIN
SELECT ec.id,sl.school_id 'school',l.id 'level',sg.id 'subject_group',e.id "Exam",ifnull(GROUP_CONCAT( DISTINCT ec.class_id ORDER BY ec.id  SEPARATOR','),'')
as "Class",ifnull(GROUP_CONCAT( DISTINCT s.id ORDER BY s.id  SEPARATOR','),'')
as "Subject",ec.marks 'Mark',ec.acedemic_year 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.level_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
where ec.id=p_id and ec.status=0 and sg.status=0 and ec.status=0;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_update_form` (IN `p_table` VARCHAR(50), IN `p_id` INT)   BEGIN
IF p_table="buses" THEN
	SELECT b.id,b.no_chairs,b.bus_name 'Bus',b.driver_id 'Driver',ifnull(GROUP_CONCAT( DISTINCT dis.id ORDER BY dis.id SEPARATOR','),'')
as "Destination",b.date FROM buses b INNER JOIN bus_destination bd on bd.bus_id=b.id INNER join districts dis on dis.id=bd.destination_id INNER join employee e on e.id=b.driver_id where b.id=p_id  GROUP BY b.id;
     ELSEIF p_table="employee" THEN
     SELECT e.id,e.name,e.gender,cc.id 'Country',s.id 'States',c.id '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.id 'shaqada',qa.id 'Qaybta',e.galinka 'Galinka',e.mushaar 'Mushar',e.lacagta,e.Date_of_Issue,e.Date_of_Starting,es1.id 'Masul 1',e.xiriirka_1,es2.id 'Masul 2',e.xiriirka_2,e.aqoonta 'Aqoonta',e.goobta,e.kuliyada,e.takhasuska,e.magaca,e.faafaahin,e.image,u.role_id,u.status from employee e 
left join user u on u.employee_id=e.id
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_id; 
    ELSEIF p_table="employee_behavior" THEN
    SELECT eb.id 'id', eb.Emp_id 'Shaqaalaha',eb.date 'date',eb.anshaxa 'status',eb.remark 'faafaahin' from employee_behavior eb where id=p_id;
   ELSEIF p_table="topics" THEN
  
     SELECT t.id,t.academic_year_id as acemadic_year,sl.school_id as school,sl.id as levels,c.id as class,sg.id as subject_group,s.id as subject,l.id as lesson, t.name as topics,t.Objectives as objectives,t.Reference,t.Assignment,t.Teaching_Method as type,t.Practical,t.Practical_Detail as practical_detail,t.from_Date,t.to_date,t.Remarks 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 JOIN school_levels sl on sl.id=c.school_level_id where t.id=p_id;
   
   ELSEIF p_table="exam_pass_mark" THEN
SELECT ec.id,sl.school_id 'school',l.id 'level',ifnull(GROUP_CONCAT( DISTINCT ecc.classes ORDER BY ec.id  SEPARATOR','),'')
as "Class",ec.marks 'Mark',ec.academic_year 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 school_levels sl on sl.level_id=c.school_level_id
INNER join levels l on l.id=sl.level_id
where ec.id=p_id;
ELSEIF p_table="Result_View" THEN
SELECT sl.school_id as school,sl.id as level,er.id,er.class,er.academic_year,er.pass_mark,er.weak_marks,er.Final,er.exam_result_info,er.Grade_view,GROUP_CONCAT(erc.subjects SEPARATOR ',') subjects FROM exam_result_class er JOIN exam_result_class_subject erc on erc.exam_result_class_info=er.id INNER join classes c on c.id=er.class
INNER join school_levels sl on sl.id=c.school_level_id  where er.id=p_id;
      ELSEIF p_table="exam_type" THEN
select e.id 'id',e.type  from exam_type e WHERE e.id=p_id;  
 ELSEIF p_table="employee_sponser" THEN
SELECT  e.id,e.name 'employee_name', e.tell1 'tell_1', e.tell2 'tell_2',e.address 'address',e.job 'job',e.darajada 'Darajada',e.Aqoonsiga 'AQoonsiga',e.Date 'date' from employee_sponser e WHERE e.id=p_id; 
ELSEIF p_table="exam_pass_mark" THEN
SELECT ec.id,sl.school_id 'school',l.id 'level',ifnull(GROUP_CONCAT( DISTINCT ec.class_id ORDER BY ec.id  SEPARATOR','),'')
as "Class",ec.marks 'Mark',ec.academic_year FROM exam_pass_mark_info ec
inner join exam_class ecc on ecc.exam_info=ec.id
INNER join classes c on c.id=ecc.classes
INNER join school_levels sl on sl.level_id=c.school_level_id
INNER join levels l on l.id=sl.level_id
where ec.id=p_id;
 ELSEIF p_table='exam_result' THEN
SELECT e.id 'id',e.Name 'ResultName' from exam_create_result e where e.id=p_id;
    ELSEIF p_table='exam_grade' THEN
    SELECT eg.id 'id',eg.Grade_name 'Grade_name',eg.Percent_from 'Percent_From', eg.Percent_to 'Percent_Upto',eg.Remark 'description' from exam_grades eg WHERE eg.id=p_id;
    
    ELSEIF p_table="users" THEN
select u.id 'id',u.full_name 'name',u.user_name 'user',u.password 'Password',u.password 'confirm_password',u.status 'status',date(u.date) 'date' from users u WHERE u.id=p_id;

    ELSEIF p_table="student_applyleave" THEN
SELECT sap.id,s.student_id,sc.id as 'schools',l.id as 'levels',cs.id as "classes",date_format(str_to_date( date(sap.apply_date),'%Y-%m-%d'),'%d/%m/%Y')  'ApplyDate',date_format(str_to_date( date(sap.from_date),'%Y-%m-%d'),'%d/%m/%Y') 'FromDate',date_format(str_to_date( date(sap.to_date),'%Y-%m-%d'),'%d/%m/%Y')    'ToDate' ,sap.reason 'Reason',sap.status 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 sap.id=p_id;
ELSEIF p_table = "levels" THEN
	SELECT l.id,l.name "level_name" FROM levels l WHERE id = p_id;
ELSEIF p_table = "sections" THEN
	SELECT s.id,s.section "section_name" FROM sections s WHERE id = p_id;
ELSEIF p_table = "fee_type" THEN
SELECT ft.id,ft.fee_name,ft.Description "description",ft.pay_method,ft.amount FROM fee_type ft WHERE id = p_id;
ELSEIF p_table ="receipt_method" THEN
SELECT r.id,r.method "method",r.description "description" FROM receipt_method r where r.id = p_id;
ELSEIF p_table ="subjects" THEN
SELECT s.id,s.code,s.subject_name "subject_name",s.Description "description" FROM subjects s where s.id = p_id;
ELSEIF p_table = "teachers" THEN
SELECT tc.id,tc.teacher_name "teacher_name",tc.gender,tc.mobile "tell_1",tc.Emergency_Contact 'tell_2',tc.address 'address_1',tc.other_address 'address_2',tc.Work_Experience 'Experience',tc.Qualification 'Qualification' FROM teachers tc  where tc.id = p_id;

ELSEIF p_table = "status" THEN
SELECT s.id 'id',s.status_name 'Xaaladda' FROM status s where s.id = p_id;
ELSEIF p_table = "subject_group" THEN
SELECT  sg.id,GROUP_CONCAT(DISTINCT sgs.subject_id SEPARATOR ",") as Subjects,ifnull(GROUP_CONCAT( DISTINCT cs.id ORDER BY cs.id  SEPARATOR ','),'') 'Class',sll.id 'level',ss.id 'school',sg.name 'name',sg.description 'description',sg.acedemic_year 'acedemic_year' 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 school_levels sll on sll.id=cs.school_level_id INNER join schools ss on sll.school_id=ss.id  where sg.id=p_id
GROUP by sg.id;
ELSEIF p_table = "classes" THEN
SELECT c.id 'id',c.class_name 'class_name',GROUP_CONCAT(se.id SEPARATOR ",") as "sections",sl.id as "level",s.id 'school_id',c.language 'language',c.shift,c.type 'type',c.NextClass as 'next_class' FROM classes c JOIN class_sections cs 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 sl.level_id = l.id JOIN schools s on sl.school_id = s.id where c.id = p_id;
ELSEIF p_table = "pass_mark" THEN
SELECT p.id 'id', p.level_id 'level_id',p.type 'type',p.language 'language',p.marks 'mark'  FROM pass_mark p inner join levels l on l.id=p.level_id WHERE p.id=p_id;
ELSEIF p_table = "schools" THEN
SELECT s.id 'id',s.school_name 'school_name',GROUP_CONCAT(l.id SEPARATOR ",") as "levelid",s.phone "mobile",s.address "Address" FROM schools s JOIN school_levels sl on sl.school_id = s.id JOIN levels l on sl.level_id = l.id where  s.id=p_id;
ELSEIF p_table = "sponsors" THEN
SELECT s.id,s.sponsor_name "sponsor_name",s.gender "gender",s.mobile 'mobile',s.address "Address" FROM sponsors s where s.id=p_id;

ELSEIF p_table="school_fee" THEN
select sf.id 'id',sf.school_id 'school_id',sf.academidyear_id 'acemadic_year_id',sf.fee_type_id 'fee_type_id',sf.amount 'amount' from school_fee sf where sf.id=p_id;
ELSEIF p_table ="applicants" THEN
SELECT a.id,a.StudentName "applicant_name",a.Gender "applicant_gender",a.mothername "mother",a.DOB "dob",a.POB "pob",a.Address "address",a.sponsor_id "sponsor",a.relation,a.RegAcademicYear "academic",a.Photo "preimage",a.school as school FROM applicants a WHERE a.id = p_id;
ELSEIF p_table = "behavior" THEN
	SELECT b.id,b.type "behavior_type",b.name "behavior_name" FROM behavior b WHERE id = p_id;
ELSEIF p_table = "student_behavior" THEN
	SELECT sc.id as 'schools',sl.id as 'levels',cs.id as "classes",sb.id,sb.student_id as "student",bt.id "behavior_type",b.id "behavior",sb.subject "Subject",sb.reason,sb.date FROM student_behavior sb 
JOIN behavior b on sb.type = b.id 
JOIN behavior_type bt on b.type = bt.id
join students s on s.student_id=sb.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  WHERE sb.id = p_id;
ELSEIF p_table = "students" THEN
SELECT st.id,st.student_id,st.student_name "student_name",st.gender,st.address,st.dob,st.pob,st.mother_name "mother",st.phone,st.sponsor_id "guardian",st.relation "relation",st.orphan_status "orphan",st.reg_date "reg_date",st.school_name,st.completed_class,sc.id "school",sl.id "level",cs.id "class",st.status,st.reg_academic_year "academic_year",st.blood_group,st.district  "district",st.reason "reason",st.photo "preimage" FROM students st JOIN class_sections cs on st.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 st.id = p_id;
END IF;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_update_foundation` (IN `_name` VARCHAR(50), IN `_logo` VARCHAR(50), IN `_updated_user` INT, IN `_foundation_id` INT)  NO SQL BEGIN
update foundation_profile set name = _name, logo = _logo, updated_user = _updated_user where id = _foundation_id;

select 'success';

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_update_student_status` (IN `_id` INT, IN `_status` INT)  NO SQL BEGIN
update students set status=_status WHERE id=_id;
SELECT 'success';
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_users` (IN `p_id` INT, IN `p_full_name` VARCHAR(100), IN `p_user_name` VARCHAR(50), IN `p_status` VARCHAR(50), IN `p_password` VARCHAR(50), IN `p_date` DATE, IN `p_company_id` INT)  NO SQL BEGIN

 if EXISTS(SELECT * from users where id=p_id) THEN
 if EXISTS(SELECT * from users where id!=p_id AND user_name=p_user_name and profile = p_company_id) THEN
 SELECT "warning|Hore ayaa loo diiwangaliyey" msg;
 ELSE
update users u set u.full_name= p_full_name,u.user_name=p_user_name,u.password=p_password,u.status=p_status where u.id=p_id;
 
SELECT "success|Xogta isticmaalaha waa la beddelay!" msg;
 END if;
ELSEIF EXISTS(SELECT * FROM users WHERE user_name=p_user_name and profile = p_company_id) THEN
 SELECT "warning|Hore ayaa loo diiwangaliyey" msg;
ELSE
INSERT into users VALUES(null,p_company_id,p_full_name,1,p_user_name,p_password,'imageslogo.jpg',p_status,p_date);

 SELECT "success|Xogta Isticmaalaha waa la xareeyey!" msg;
end if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_ViewExamEntry` (IN `p_class` VARCHAR(100), IN `p_accy` INT, IN `p_exam` INT)  NO SQL BEGIN
SELECT DISTINCT concat(c.class_name,' ',se.section)as classes,ifnull((SELECT DISTINCT GROUP_CONCAT(DISTINCT concat(sb.subject_name ,'-->',em.date,'[',u.user_name,']') SEPARATOR '<br>'  )  FROM exam_marks em 
INNER join subjects sb on sb.id=em.subject_id INNER join user u on em.user_id=u.id
 WHERE em.academicyear=eci.acedemic_year and em.exam_id=p_exam and em.class_id=cs.id  ORDER by sb.id),'-')as Reg,
 (SELECT DISTINCT GROUP_CONCAT( DISTINCT sb.subject_name ORDER by sb.id SEPARATOR ',') FROM exam_class_info eeci JOIN exam_class ec on ec.exam_info=eeci.id INNER JOIN subjects sb on sb.id=ec.subject_id INNER JOIN classes cl on cl.id=eeci.class_id INNER JOIN class_sections ccs on ccs.class_id=cl.id WHERE sb.id not in (SELECT DISTINCT em.subject_id from exam_marks em where em.class_id=ccs.id and em.exam_id=eeci.exam_type_id and em.academicyear=eeci.acedemic_year) and eeci.exam_type_id=p_exam and eeci.acedemic_year=eci.acedemic_year and ccs.id=cs.id  GROUP by ccs.id) as unreg from exam_class_info eci 
INNER JOIN classes c on c.id=eci.class_id 
INNER JOIN class_sections cs on cs.class_id=c.id 
INNER join sections se on se.id=cs.section_id
INNER JOIN school_levels sl on sl.id=c.school_level_id
INNER JOIN levels l on l.id=sl.level_id
WHERE eci.acedemic_year=p_accy and eci.exam_type_id=p_exam and  sl.id like p_class;

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_viewExamPass` (IN `p_result` INT, IN `p_type` INT)   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.to_class
     INNER join classes c on c.id=css.class_id
    
    where c.id=@class and sp.from_academic=@academic_year  AND s.status=2
    
) 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),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.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 
     );
   
   
    CREATE TEMPORARY TABLE IF NOT EXISTS table6 AS (
     SELECT sms3.subject_name,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
     );
     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 tableaverage AS (
       SELECT subject_name,student_id,Marks,Total,round((Total/@No_subject),2)  'Average' from  table6 
         
         );
              CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (
       SELECT TRIM(subject_name) 'subject_name',student_id,Marks,Total,  Average,(SELECT Grade_name from exam_grades  eg where eg.Percent_from>=Average and eg.Percent_to<=Average  LIMIT 1) 'Grade' from  tableaverage );
        
     CREATE TEMPORARY TABLE IF NOT EXISTS table3 AS (
           SELECT sms3.student_id ,SUBSTRING_INDEX( student_name,' ',3) 'student_name'   FROM student_marks_step3 sms3
         inner join students s on s.student_id =sms3.student_id
        
         );
        SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
         CONCAT('MAX(CASE WHEN pa.subject_name = ''', subject_name,
                ''' THEN pa.Marks END) `', subject_name, '`'))
  INTO @sql
  FROM table2;
SET @sql = CONCAT('SELECT p.student_name ''Name''
                    , ', @sql, ' ,pa.Total,pa.Average ,pa.Grade
                   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 student_marks_step5 AS ', @sql);
PREPARE stmt FROM @createSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
if (p_type=0)THEN
SELECT *  from student_marks_step5 where Total<@pass_mark order by Total DESC;
ELSE
SELECT *  from student_marks_step5 where Total>=@pass_mark order by Total DESC;
END if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_ViewExamSummaryPercentage` ()   BEGIN
    SET SESSION group_concat_max_len = 9999999999999999999;

    CREATE TEMPORARY TABLE summary AS
    SELECT 
        1 AS nu, 
        'All students' AS No, 
        cl.class_name, 
        COUNT(DISTINCT ss.student_id) AS students  
    FROM 
        staticalsummary ss 
    JOIN 
        classes cl ON cl.id = ss.class 
    GROUP BY  
        cl.class_name

    UNION ALL

    SELECT 
        2 AS nu, 
        CONCAT('Pass - ', IFNULL(ss.gender, all_genders.gender)) AS No, 
        cl.class_name, 
        CONCAT(ROUND(COUNT(DISTINCT ss.student_id) * 100.0 / NULLIF(total_students.total, 0)), '%') AS students
    FROM 
        classes cl
    CROSS JOIN 
        (SELECT DISTINCT gender FROM staticalsummary) all_genders
    LEFT JOIN 
        staticalsummary ss ON ss.gender = all_genders.gender AND ss.class = cl.id AND ss.Total > ss.weak_marks 
    JOIN 
        (SELECT class, COUNT(DISTINCT student_id) AS total FROM staticalsummary GROUP BY class) total_students ON total_students.class = cl.id
    GROUP BY 
        cl.class_name, No

    UNION ALL 

    SELECT 
        4 AS nu, 
        CONCAT('Fail - ', IFNULL(ss.gender, all_genders.gender)) AS No, 
        cl.class_name, 
        CONCAT(ROUND(COUNT(DISTINCT ss.student_id) * 100.0 / NULLIF(total_students.total, 0)), '%') AS students
    FROM 
        classes cl
    CROSS JOIN 
        (SELECT DISTINCT gender FROM staticalsummary) all_genders
    LEFT JOIN 
        staticalsummary ss ON ss.gender = all_genders.gender AND ss.class = cl.id AND ss.Total < ss.pass_mark 
    JOIN 
        (SELECT class, COUNT(DISTINCT student_id) AS total FROM staticalsummary GROUP BY class) total_students ON total_students.class = cl.id
    GROUP BY 
        cl.class_name, No

    UNION ALL

    SELECT 
        3 AS nu, 
        CONCAT('Weak - ', IFNULL(ss.gender, all_genders.gender)) AS No, 
        cl.class_name, 
        CONCAT(ROUND(COUNT(DISTINCT ss.student_id) * 100.0 / NULLIF(total_students.total, 0)), '%') AS students
    FROM 
        classes cl
    CROSS JOIN 
        (SELECT DISTINCT gender FROM staticalsummary) all_genders
    LEFT JOIN 
        staticalsummary ss ON ss.gender = all_genders.gender AND ss.class = cl.id AND ss.Total BETWEEN ss.pass_mark AND ss.weak_marks 
    JOIN 
        (SELECT class, COUNT(DISTINCT student_id) AS total FROM staticalsummary GROUP BY class) total_students ON total_students.class = cl.id
    GROUP BY 
        cl.class_name, No

    ORDER BY 
        FIELD(No, 'Male', 'Female');

    SET @sql = NULL;
    SELECT 
        GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN class_name = ''', class_name, ''' THEN COALESCE(students,0) END) AS `', class_name, '`'))
    INTO 
        @sql
    FROM 
        summary;

    SET @sql = CONCAT('SELECT No, ', @sql, ' FROM summary GROUP BY No ORDER BY nu');

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

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_ViewExamSummaryStatistical` ()   BEGIN
SET SESSION group_concat_max_len = 9999999999999999999;
CREATE TEMPORARY table summary as
SELECT 1 as nu,'All students' as No,cl.class_name,COUNT(DISTINCT ss.student_id) as students FROM staticalsummary ss JOIN classes cl on cl.id=ss.class GROUP by ss.class
UNION
SELECT 2 as nu,concat('Pass - ', IFNULL(ss.gender, all_genders.gender)) as No, cl.class_name, COUNT(DISTINCT ss.student_id) as students
FROM (
  SELECT DISTINCT gender
  FROM staticalsummary
) all_genders
CROSS JOIN classes cl
LEFT JOIN staticalsummary ss ON ss.gender = all_genders.gender AND ss.class = cl.id AND ss.Total>ss.weak_marks where  cl.id in (SELECT sl.class from staticalsummary sl)
GROUP BY cl.class_name, No

UNION 
SELECT  4 as nu,concat('Fail - ', IFNULL(ss.gender, all_genders.gender)) as No, cl.class_name, COUNT(DISTINCT ss.student_id) as students
FROM (
  SELECT DISTINCT gender
  FROM staticalsummary
) all_genders
CROSS JOIN classes cl
LEFT JOIN staticalsummary ss ON ss.gender = all_genders.gender AND ss.class = cl.id AND ss.Average < ss.pass_mark where  cl.id in (SELECT sl.class from staticalsummary sl)
GROUP BY cl.class_name, No

UNION
SELECT  3 as nu,concat('weak - ', IFNULL(ss.gender, all_genders.gender)) as No, cl.class_name, COUNT(DISTINCT ss.student_id) as students
FROM (
  SELECT DISTINCT gender
  FROM staticalsummary
) all_genders
CROSS JOIN classes cl
LEFT JOIN staticalsummary ss ON ss.gender = all_genders.gender AND ss.class = cl.id AND ss.Average BETWEEN ss.pass_mark and ss.weak_marks where  cl.id in (SELECT sl.class from staticalsummary sl)
GROUP BY cl.class_name,No
ORDER by FIELD(No,'Male','Female')

;

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
         CONCAT('MAX(CASE WHEN class_name = ''', class_name,
                ''' THEN COALESCE(students,0) END) `', class_name, '`'))
  INTO @sql
FROM summary;

SET @sql = CONCAT('SELECT No, ', @sql, ' FROM summary group by No order by nu');

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

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_ViewExamSummaryStatistical1` ()   BEGIN
SET SESSION group_concat_max_len = 9999999999999999999;
CREATE TEMPORARY table summary as
SELECT 1 as nu,'All students' as No,cl.class_name,COUNT(DISTINCT ss.student_id) as students FROM staticalsummary ss JOIN classes cl on cl.id=ss.class GROUP by ss.class
UNION
SELECT 2 as nu,concat('Pass - ', IFNULL(ss.gender, all_genders.gender)) as No, cl.class_name, COUNT(DISTINCT ss.student_id) as students
FROM (
  SELECT DISTINCT gender
  FROM staticalsummary
) all_genders
CROSS JOIN classes cl
LEFT JOIN staticalsummary ss ON ss.gender = all_genders.gender AND ss.class = cl.id AND ss.Total>ss.weak_marks where  cl.id in (SELECT sl.class from staticalsummary sl)
GROUP BY cl.class_name, No

UNION 
SELECT  4 as nu,concat('Fail - ', IFNULL(ss.gender, all_genders.gender)) as No, cl.class_name, COUNT(DISTINCT ss.student_id) as students
FROM (
  SELECT DISTINCT gender
  FROM staticalsummary
) all_genders
CROSS JOIN classes cl
LEFT JOIN staticalsummary ss ON ss.gender = all_genders.gender AND ss.class = cl.id AND ss.Total < ss.pass_mark where  cl.id in (SELECT sl.class from staticalsummary sl)
GROUP BY cl.class_name, No

UNION
SELECT  3 as nu,concat('weak - ', IFNULL(ss.gender, all_genders.gender)) as No, cl.class_name, COUNT(DISTINCT ss.student_id) as students
FROM (
  SELECT DISTINCT gender
  FROM staticalsummary
) all_genders
CROSS JOIN classes cl
LEFT JOIN staticalsummary ss ON ss.gender = all_genders.gender AND ss.class = cl.id AND ss.Total BETWEEN ss.pass_mark and ss.weak_marks where  cl.id in (SELECT sl.class from staticalsummary sl)
GROUP BY cl.class_name,No
ORDER by FIELD(No,'Male','Female')

;

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
         CONCAT('MAX(CASE WHEN class_name = ''', class_name,
                ''' THEN COALESCE(students,0) END) `', class_name, '`'))
  INTO @sql
FROM summary;

SET @sql = CONCAT('SELECT No, ', @sql, ' FROM summary group by No order by nu');

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

END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_view_percentageresult` (IN `p_result` INT, 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,e.weak_marks into @class,@academic_year,@exam_result_info,@pass_mark,@weak_marks 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,concat(c.class_name,' ',se.section) as classname,css.id as sections from students s 
     INNER join class_sections css on css.id=s.class_id
     INNER join classes c on c.id=css.class_id
      INNER JOIN sections se on se.id=css.section_id
    where c.id=@class and s.current_academic =@academic_year and (css.id like p_class OR p_class LIKE '%') and  s.status=2
    UNION all select s.student_id,s.student_name,c.id,concat(c.class_name,' ',se.section) as classname,css.id as sections 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
    INNER JOIN sections se on se.id=css.section_id
    where c.id=@class and (css.id like p_class OR p_class LIKE '%')  and sp.from_academic=@academic_year and s.status=2
    
) as `Data` 
    );
    
   
CREATE TEMPORARY TABLE IF NOT EXISTS student_marks_step1 AS (
select s.id 'subject_id',ss.student_id,c.id 'class_id',ss.classname,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.class_id=ss.sections and emk.status=0),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,sts.classname 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,sts.classname,sum(if(sts.Marks!=0,(sts.percentage/100)*sts.Marks,sts.Marks)) 'Marks' from student_marks_step2 sts group by student_id,subject_id 
     );
     CREATE TEMPORARY TABLE IF NOT EXISTS table6 AS (
     SELECT sms3.subject_name,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
     );
     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 tableaverage AS (
       SELECT subject_name,student_id,Marks,Total,round((Total/@No_subject),2)  'Average' from  table6 
         
         
         
         );
             CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (
      SELECT TRIM(subject_name) 'subject_name',tb.student_id,ss.gender,Marks,Total,  Average ,@class as class,
round(@pass_mark,2) as pass_mark,round(@weak_marks,2) as weak_marks from  tableaverage tb  JOIN students ss on ss.student_id=tb.student_id
         
         );
       
insert into  staticalsummary

SELECT *  from table2   ;






END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_view_single_student_result` (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 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.to_class
     INNER join classes c on c.id=css.class_id
    
    where c.id=@class and sp.from_academic=@academic_year and s.student_id=p_student;

  
   
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 
     );
CREATE TEMPORARY 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
     );
     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
           );
  
          
      
  CREATE TEMPORARY TABLE IF NOT EXISTS tableaverage AS (
       SELECT tl.subject_name,em.max_marks 'Max Marks',round(em.min_marks,2) as 'Min Marks',tl.Marks,tl.Total,round((tl.Total/@No_subject),2)  'Average' from  table6 tl INNER join exam_mark_correction em on em.subject_id=tl.subject_id
         
         );
         
             SELECT * from tableaverage;
    
    
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_view_single_student_result1` (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 and s.student_id=p_student
    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 and s.student_id=p_student
    
) 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.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 
     );
CREATE TEMPORARY TABLE IF NOT EXISTS table6 AS (
     SELECT sms3.subject_name,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
     );
     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 @No_subject=(SELECT COUNT(*) from subject_class);
      
  CREATE TEMPORARY TABLE IF NOT EXISTS tableaverage AS (
       SELECT subject_name,Marks,Total,round((Total/@No_subject),2)  'Average' from  table6 
         
         );
         
              CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (
       SELECT TRIM(subject_name) 'subject_name',Marks,Total,  Average,ifnull((SELECT ifnull(Grade_name,'-') from exam_grades  eg where eg.Percent_from>=Average and eg.Percent_to<=Average ),'-') as  'Grade' from  tableaverage 
         );
 SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
         CONCAT('MAX(CASE WHEN subject_name = ''', subject_name,
                ''' THEN Marks END) `', subject_name, '`'))
  INTO @sql
FROM tableaverage;
SET @sql = CONCAT('SELECT ', @sql, ',Total,Average FROM tableaverage');

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

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_view_single_student_result2` (IN `p_result` INT, IN `p_student` VARCHAR(1000), IN `p_exam` 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 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.to_class
     INNER join classes c on c.id=css.class_id
    
    where c.id=@class and sp.from_academic=@academic_year and s.student_id=p_student;

  
   
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 
     );
CREATE TEMPORARY 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
     );
     
     
     SELECT

       CASE 
            WHEN excr.Name = 'Mid -Term Only' THEN eci.marks 
            WHEN excr.Name = 'Mid -Term All Exams' THEN eci.marks + 31  
            WHEN excr.Name = 'FINAL EXAM 2023-2024' THEN eci.marks + 91
            WHEN excr.Name = 'Final Khaas' THEN eci.marks
            WHEN excr.Name = 'Assignment 1 Result' THEN eci.marks 
            ELSE eci.marks 
        END  INTO  @exam_marks
    FROM exam_create_result excr 
    INNER JOIN exam_result_calculation exrc ON exrc.result_id = excr.id
    INNER JOIN exam_class_info eci ON exrc.Exams = eci.exam_type_id 
    WHERE excr.Name =p_exam and eci.acedemic_year=@academic_year and  eci.class_id=@class  AND  eci.marks NOT IN(0,10,18) GROUP BY eci.marks ;
     
     
     
     
     set @No_subject=(SELECT COUNT(*) from subject_class);
     
      set @TotalExamMarks= @No_subject * @exam_marks;
     
     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
           );
  
          
      
  CREATE TEMPORARY TABLE IF NOT EXISTS tablepercentage AS (
       SELECT tl.subject_name,em.max_marks 'Max Marks',round(em.min_marks,2) as 'Min Marks',tl.Marks,tl.Total,round((tl.Total/@TotalExamMarks) * 100,2)  'Percentage' from  table6 tl INNER join exam_mark_correction em on em.subject_id=tl.subject_id
         
         );
         
             SELECT * from tablepercentage;
    
    
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_view_single_student_result_w_subject` (IN `p_result` INT, IN `p_class` INT, IN `p_academic_year` 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;




CREATE TEMPORARY TABLE ttmp1
(exam int,id int, result int,percentage decimal(18,2));
insert into ttmp1
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 IF NOT EXISTS tmp AS (
     
     SELECT * from ttmp1 where id=p_result
     );
 
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=p_class and sg.status=0 and sg.acedemic_year=p_academic_year ;
   
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=p_class and s.current_academic =p_academic_year and s.student_id=p_student
    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=p_class and sp.from_academic=p_academic_year and s.student_id=p_student
    
) 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=p_academic_year and emk.subject_id=ec.subject and emk.status=0),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=p_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.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 
     );
CREATE TEMPORARY TABLE IF NOT EXISTS table6 AS (
     SELECT sms3.subject_name,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
     );
     set @No_subject=(SELECT COUNT(*) from subject_class);
     CREATE TEMPORARY TABLE IF NOT EXISTS tableaverage AS (
       SELECT subject_name,student_id,Marks,Total,round(Total/@No_subject,2)  'Average' from  table6 
         
         );
              CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (
       SELECT TRIM(subject_name) 'Subjects',Marks,Total,  Average,(SELECT Grade_name from exam_grades  eg where eg.Percent_from>=Average and eg.Percent_to<=Average ) 'Grade' from  tableaverage 
         
         );
     

SELECT *  from table2 order by Total DESC limit 7;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_view_student_pass_fail` (IN `p_result` INT, IN `p_class` INT, IN `p_academic_year` INT, IN `p_subjects` TEXT, IN `p_type` INT)   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` 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` 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)  ) as id,(SELECT er.result_id from exam_result_calculation er where 
 FIND_IN_SET(t1.exam, er.Exams)  ) as result_id,(SELECT er.percentage from exam_result_calculation er where 
 FIND_IN_SET(t1.exam, er.Exams)  ) 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,concat(c.class_name,' ',se.section) as classname,css.id as sections from students s 
     INNER join class_sections css on css.id=s.class_id
     INNER join classes c on c.id=css.class_id
      INNER JOIN sections se on se.id=css.section_id
    where c.id=@class and s.current_academic =@academic_year and css.id like p_class and  s.status=2
    UNION all select s.student_id,s.student_name,c.id,concat(c.class_name,' ',se.section) as classname,css.id as sections 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
    INNER JOIN sections se on se.id=css.section_id
    where c.id=@class and css.id like p_class and sp.from_academic=@academic_year and s.status=2
    
) as `Data` 
    );
    
   
CREATE TEMPORARY TABLE IF NOT EXISTS student_marks_step1 AS (
select s.id 'subject_id',ss.student_id,c.id 'class_id',ss.classname,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.class_id=ss.sections and emk.status=0),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,sts.classname 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,sts.classname,sum(if(sts.Marks!=0,(sts.percentage/100)*sts.Marks,sts.Marks)) 'Marks' from student_marks_step2 sts group by student_id,subject_id 
     );
     CREATE TEMPORARY TABLE IF NOT EXISTS table6 AS (
     SELECT sms3.subject_name,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
     );
     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 tableaverage AS (
       SELECT subject_name,student_id,Marks,Total,round((Total/@No_subject),2)  'Average' from  table6 
         
         );
              CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (
       SELECT TRIM(subject_name) 'subject_name',student_id,Marks,Total,  Average,(SELECT Grade_name from exam_grades  eg where eg.Percent_from>=Average and eg.Percent_to<=Average ) 'Grade' from  tableaverage 
         
         );
         
        
     CREATE TEMPORARY TABLE IF NOT EXISTS table3 AS (
           SELECT sms3.student_id ,sms3.classname,SUBSTRING_INDEX( student_name,' ',3) 'student_name'   FROM student_marks_step3 sms3
         inner join students s on s.student_id =sms3.student_id
        
         );
SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT
         CONCAT('MAX(CASE WHEN pa.subject_name = ''', subject_name,
                ''' THEN pa.Marks END) `', subject_name, '`'))
  INTO @sql
  FROM table2;
SET @sql = CONCAT('SELECT p.student_name ''Name''
                   ,p.classname ''class'' , ', @sql, ' ,pa.Total,pa.Average ,pa.Grade
                   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 student_marks_step5 AS ', @sql);

PREPARE stmt FROM @createSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
set @passmark=(SELECT emi.marks from exam_pass_mark_info emi  INNER JOIN exam_pass_mark em on emi.id=em.pass_mark_info WHERE em.classes=p_class and emi.academic_year=p_academic_year);


if (p_type=0) THEN
SELECT *  from student_marks_step5 where Average<@passmark  order by Total DESC;
ELSE
SELECT *  from student_marks_step5 where Average>@passmark  order by Total DESC;
END if;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_view_student_result` (IN `p_result` INT, IN `p_class` INT, IN `p_academic_year` INT, IN `p_subjects` TEXT)   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 = 1000000;
create table s( txt text );
insert into s values(p_subjects);

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 ttmp1
(exam int,id int, result int,percentage decimal(18,2));
insert into ttmp1
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 IF NOT EXISTS tmp AS (
     
     SELECT * from ttmp1 where id=p_result
     );
 
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=p_class and  sg.status=0 and sg.acedemic_year=p_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=p_class and s.current_academic =p_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=p_class and sp.from_academic=p_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=p_academic_year and emk.subject_id=ec.subject and emk.status=0),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=p_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.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 
     );
     CREATE TEMPORARY TABLE IF NOT EXISTS table6 AS (
     SELECT sms3.subject_name,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
     );
     set @No_subject=(SELECT COUNT(*) from subject_class);
     CREATE TEMPORARY TABLE IF NOT EXISTS tableaverage AS (
       SELECT subject_name,student_id,Marks,Total,round(Total/@No_subject,2)  'Average' from  table6 
         
         );
              CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (
       SELECT TRIM(subject_name) 'subject_name',student_id,Marks,Total,  Average,(SELECT Grade_name from exam_grades  eg where eg.Percent_from>=Average and eg.Percent_to<=Average ) 'Grade' from  tableaverage 
         
         );
         
        
     CREATE TEMPORARY TABLE IF NOT EXISTS table3 AS (
           SELECT sms3.student_id ,SUBSTRING_INDEX( student_name,' ',3) 'student_name'   FROM student_marks_step3 sms3
         inner join students s on s.student_id =sms3.student_id
        
         );
SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT
         CONCAT('MAX(CASE WHEN pa.subject_name = ''', subject_name,
                ''' THEN pa.Marks END) `', subject_name, '`'))
  INTO @sql
  FROM table2;
SET @sql = CONCAT('SELECT p.student_name ''Name''
                    , ', @sql, ' ,pa.Total,pa.Average ,pa.Grade
                   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 student_marks_step5 AS ', @sql);

PREPARE stmt FROM @createSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TEMPORARY TABLE IF NOT EXISTS finalresult AS (
  SELECT * FROM student_marks_step5 ORDER BY Total DESC
);
 


SELECT fr.*,
       @rank := CASE WHEN @prev_total = fr.Total THEN @rank ELSE  @rank + 1 END AS `rank`,  @prev_total := fr.Total as 'Marks'
      
FROM finalresult AS fr
CROSS JOIN (SELECT  @rank := 0, @prev_total := NULL) AS r
ORDER BY Total DESC, `rank` ASC, Name ASC;
END$$

CREATE DEFINER=`a6d307_dugsi`@`%` PROCEDURE `sp_view_student_result1` (IN `p_result` INT, IN `p_class` VARCHAR(100), IN `p_sort` INT, IN `p_exam` 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 s.student_id,s.student_name,c.id,concat(c.class_name,' ',se.section) as classname,css.id as sections from    student_promotion sp 
    inner join students s on s.student_id=sp.student_id
      INNER join class_sections css on css.id=sp.to_class
     INNER join classes c on c.id=css.class_id
    INNER JOIN sections se on se.id=css.section_id
    where c.id=@class and css.id like p_class and sp.from_academic=@academic_year and s.status=2;
    


CREATE TEMPORARY TABLE IF NOT EXISTS student_marks_step1 AS (
select s.id 'subject_id',ss.student_id,c.id 'class_id',ss.classname,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.class_id=ss.sections and emk.status=0),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,sts.classname 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,sts.classname,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

       CASE 
            WHEN excr.Name = 'Mid -Term Only' THEN eci.marks 
            WHEN excr.Name = 'Mid -Term All Exams' THEN eci.marks + 31  
            WHEN excr.Name = 'FINAL EXAM 2023-2024' THEN eci.marks + 91
            WHEN excr.Name = 'Final Khaas' THEN eci.marks
            WHEN excr.Name = 'Assignment 1 Result' THEN eci.marks 
            ELSE eci.marks 
        END  INTO  @exam_marks
    FROM exam_create_result excr 
    INNER JOIN exam_result_calculation exrc ON exrc.result_id = excr.id
    INNER JOIN exam_class_info eci ON exrc.Exams = eci.exam_type_id 
    WHERE excr.Name =p_exam and eci.acedemic_year=@academic_year and  eci.class_id=@class  AND  eci.marks NOT IN(0,10,18) GROUP BY eci.marks ;
    
   
   set @No_subject = (SELECT COUNT(*) FROM subject_class);
   
   set @TotalExamMarks= @No_subject * @exam_marks;




  CREATE TEMPORARY TABLE IF NOT EXISTS table6 AS (
    SELECT sms3.subject_name, 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 tablepercentage AS (
   SELECT subject_name, student_id, Marks, Total, CONCAT(ROUND(
    (Total / @TotalExamMarks) * 100, 2)) AS 'Percentage' FROM table6
);


     
CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (
       SELECT TRIM(subject_name) 'subject_name',student_id,Marks,Total,round((Total/@No_subject),2)  AS Average ,  Percentage,(SELECT Grade_name from exam_grades  eg where eg.Percent_from>=Percentage and eg.Percent_to<=Percentage LIMIT 1) 'Grade' from  tablepercentage 
         );

        
     CREATE TEMPORARY TABLE IF NOT EXISTS table3 AS (
           SELECT sms3.student_id ,sms3.classname,SUBSTRING_INDEX( student_name,' ',3) 'student_name'   FROM student_marks_step3 sms3
         inner join students s on s.student_id =sms3.student_id
        
         );
        
SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT
         CONCAT('MAX(CASE WHEN pa.subject_name = ''', subject_name,
                ''' THEN  pa.Marks END) `', subject_name, '`'))
  INTO @sql
  FROM table2;
SET @sql = CONCAT ('SELECT p.student_name ''Name''
                ,p.classname ''class'' , ', @sql, '  ,pa.Total,pa.Average,concat(pa.Percentage,''%'') as Percentage,
                    CASE
                       WHEN pa.Percentage >= 50 THEN ''PASSED''
                       ELSE ''FAILED''
                   END AS Status
                   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 student_marks_step5 AS ', @sql);

PREPARE stmt FROM @createSQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TEMPORARY TABLE IF NOT EXISTS finalresult AS (
  SELECT * FROM student_marks_step5 ORDER BY Total DESC
);
 


SELECT fr.*,
       @rank := CASE WHEN @prev_total = fr.Total THEN @rank ELSE  @rank + 1 END AS `rank`,  @prev_total := fr.Total as 'marks'
      
FROM finalresult AS fr
CROSS JOIN (SELECT  @rank := 0, @prev_total := NULL) AS r
ORDER BY Total DESC, `rank` ASC, Name ASC;
