DELIMITER $$

DROP PROCEDURE IF EXISTS `smsrhs1`.`auto_registration` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `auto_registration`(Pperiod_id bigint,Ffamily_id bigint,vUser int(10),vUsername varchar(20),AccessSchema varchar(20),vAccesslabels varchar(20),vJournalId integer,vjournal_project integer)
BEGIN
Declare ExitLoop ,Done tinyint(1);
Declare Vdate,fValuedate date;
Declare Pdesc,vfamilycode,vjournaldesc,vpaymentnumber varchar(200);
Declare vfamily_id,vFee_id bigint;
Declare vledger_number,vDLedger_number varchar(10);
Declare vCurrency_Code,vFeecurrency,vBase1,vBase2  varchar(6);
Declare vStudent_code,vStudent_name,vStudent_lastname,vstudent_email,vtel,vname ,vproject varchar(50);
Declare fAmount,vRate_dt1,vRate_dt2,vRate_dt,fAmount1,fAmount2,vDisc,vDiscAmount,vDiscAmountbase1,vDiscAmountbase2,vstudentid double;
Declare VFeeDate,VDeferredDate Date;
  Declare Familydeferred cursor for
     select   fees.fee_id,fees.family_id,FeesDeferredPayments.ledger_number2,
                feesstudent.currency_code,students.student_code,
                FeesDeferredPayments.ledger_number,fees.currency_code,
                FeeDeferredvalue_date,feedeferredpayment_amount,ifnull(FeeDeferredPayment_amountBase1,0),
                ifnull(FeeDeferredPayment_amountBase2,0),students.Student_FirstName,family_description,FeeDeferredPayment_discountper,
                (FeeDeferredPayment_amount*ifnull(FeeDeferredPayment_discountper,0))/100
                ,(FeeDeferredPayment_amountbase1*ifnull(FeeDeferredPayment_discountper,0))/100
                ,(FeeDeferredPayment_amountbase2*ifnull(FeeDeferredPayment_discountper,0))/100
                ,student_email,students.student_id,Fees.Fee_Date,FeeDeferredPayment_date,families.family_code,FeesTemplatename_name,FeeDeferredPayment_number
    from fees
    left join feesdeferredpayments on feesdeferredpayments.fee_id=fees.fee_id
    left join feesstudent on feesstudent.fee_id=fees.fee_id  and feesstudent.student_id=feesdeferredpayments.student_id
    left join students on students.student_id=feesdeferredpayments.student_id
    left join families on families.family_id=students.family_id
    left join feestemplatenames on feestemplatenames.FeesTemplatename_id=feesdeferredpayments.FeeTemplateDt_name
    where fees.family_id=Ffamily_id;
    
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET ExitLoop = 1;
  SET ExitLoop = 0;
  sET @jvid:=0;
  Select date_format(now(),'%Y-%m-%d') into Vdate from dual;
  select period_description into Pdesc from periods where period_id=Pperiod_id;
  open Familydeferred;
  Familydeferredloop: LOOP
  fetch Familydeferred into vFee_id,vfamily_id,vDLedger_number,vCurrency_Code,vStudent_code,vledger_number,vFeecurrency,
                              fValuedate,fAmount,fAmount1,fAmount2,vStudent_name,vStudent_lastname,vDisc,vDiscAmount
                              ,vDiscAmountbase1,vDiscAmountbase2,vstudent_email,vstudentid,VFeeDate,VDeferredDate,vfamilycode,vjournaldesc,vpaymentnumber;
  if ExitLoop then
      Leave Familydeferredloop;
    End if;

  if(vstudent_email is null) then set vstudent_email := '';
  end if;


  SELECT  base1,base2 into vBase1,vBase2 from setup;

  if(vCurrency_Code is null) then set vCurrency_Code := vBase1;  end if;


  if(vStudent_name is null) then set vStudent_name := '-1';  end if;
  if(vStudent_lastname is null) then set vStudent_lastname := '-1';  end if;

  if(vStudent_code is not null) then set vproject = concat('sms',vfamilycode);
      else set vproject = 'smsFamily';
  end if;

  if(vStudent_code is null) then set vStudent_code := '';  end if;



  BEGIN
      DECLARE CONTINUE HANDLER FOR 1329 SET vtel = '';

        select  familiesDtAddress_tel1 into vtel from familiesdtaddresses
				left join familiesdt on familiesdtaddresses.familydt_id=familiesdt.familydt_id
				left join students on students.familyDt_idmother=familiesdt.familydt_id where student_id =vstudentid limit 1;
end;

  set vname := concat(vStudent_lastname,' ',vtel);



  set Pdesc='';
  
  SET Pdesc=concat(vjournaldesc,':',vStudent_name,' ',vStudent_lastname,' ',Pdesc,':payment # ',vpaymentnumber);
 


  Set @QJv:=Concat('call ', AccessSchema,'.Create_JV(',char(39),'RG',char(39),',',vJournalId,',',char(39),VFeeDate,char(39),',',
              vledger_number,',',char(39),vCurrency_Code,char(39),',',char(39),'D',char(39),',',char(39),Pdesc,char(39),
              ',','null',',',char(39),VDeferredDate,char(39),',',char(39),vproject,char(39),',',
              char(39),vproject,char(39),',',fAmount,',',fAmount1,',',
              fAmount2,',',1,',',char(39),AccessSchema,char(39),',',char(39),vAccesslabels,char(39),',',
              char(39),vUser,char(39),',',char(39),vUsername,char(39),',','null',',',char(39),vledger_number,char(39),',',0,',',char(39),vname,char(39),',',char(39),vstudent_email,char(39),',',vjournal_project,',@jvid',')');



      Prepare Stmtjv From @QJv;
      Execute Stmtjv;

  if (vJournalId = 0 or vJournalId is null) Then Set vJournalId := @jvid; End If;

   Set @QJv2:=Concat('call ', AccessSchema,'.Create_JV(',char(39),'RG',char(39),',',vJournalId,',',char(39),VFeeDate,char(39),',',
              char(39),vDLedger_number,char(39),',',char(39),vCurrency_Code,char(39)
             ,',',char(39),'C',char(39),',',char(39),Pdesc,char(39) ,
              ',','null',',',char(39),VDeferredDate,char(39),',',char(39),vproject,char(39),',',
             char(39),vproject,char(39),',',fAmount,',',fAmount1,',',
              fAmount2,',',1,',',char(39),AccessSchema,char(39),',',char(39),vAccesslabels,char(39),',',
              char(39),vUser,char(39),',',char(39),vUsername,char(39),',','null',',',char(39),vledger_number,char(39),',',0,',',char(39),vname,char(39),',',char(39),vstudent_email,char(39),',',vjournal_project,',@jvid',')');


      Prepare Stmtjv2 From @QJv2;
      Execute Stmtjv2;



    if(vDisc<>'' and vDisc is not null and vDisc<>'0') then
        Set @QJv3:=Concat('call ', AccessSchema,'.Create_JV(',char(39),'RG',char(39),',',vJournalId,',',char(39),VFeeDate,char(39),',',
              vledger_number,',',char(39),vCurrency_Code,char(39),',',char(39),'C',char(39),',',char(39),Pdesc,char(39),
              ',','null',',',char(39),VDeferredDate,char(39),',',char(39),vproject,char(39),',',char(39),vproject,char(39),','
              ,vDiscAmount,',',vDiscAmountbase1,',',
              vDiscAmountbase2,',',1,',',char(39),AccessSchema,char(39),',',char(39),vAccesslabels,char(39),',',
              char(39),vUser,char(39),',',char(39),vUsername,char(39),',','null',',',char(39),vledger_number,char(39),',',0,',',char(39),vname,char(39),',',char(39),vstudent_email,char(39),',',vjournal_project,',@jvid',')');

                  Prepare Stmtjv3 From @QJv3;
    Execute Stmtjv3;


    Set @QJv4:=Concat('call ', AccessSchema,'.Create_JV(',char(39),'RG',char(39),',',vJournalId,',',char(39),VFeeDate,char(39),',',
              char(39),vDLedger_number,char(39),',',char(39),vCurrency_Code,char(39)
             ,',',char(39),'D',char(39),',',char(39),Pdesc,char(39) ,
              ',','null',',',char(39),VDeferredDate,char(39),',',char(39),vproject,char(39),',',
             char(39),vproject,char(39),',',vDiscAmount,',',vDiscAmountbase1,',',
              vDiscAmountbase2,',',1,',',char(39),AccessSchema,char(39),',',char(39),vAccesslabels,char(39),',',
              char(39),vUser,char(39),',',char(39),vUsername,char(39),',','null',',',char(39),vledger_number,char(39),',',0,',',char(39),vname,char(39),',',char(39),vstudent_email,char(39),',',vjournal_project,',@jvid',')');

      Prepare Stmtjv4 From @QJv4;
      Execute Stmtjv4;


end if;
  END LOOP Familydeferredloop;
	Close Familydeferred;



    update fees set journal_id=@jvid where fee_id=vFee_id;
END $$

DELIMITER ;