DECLARE
    N           NUMBER :=0;
    N1          NUMBER :=0;
    V_SUBJECT   VARCHAR2(500);
	V_BRANCH    VARCHAR2(500);
	V_ADDRESS_ENG  VARCHAR2(500);
    v_blob      BLOB := empty_blob();
    l_step      NUMBER :=22500;
    l_mime_type VARCHAR2(1000);
    V_LVL_01_ID NUMBER;
    V_LVL_10_ID NUMBER;


 CURSOR AUTH IS
            select  USERS,
                            REQ_NO,
                            BRANCH_ID,
                            PREPARED,
                            USER_LEVEL,
                            DESIG,
                            AUTHORITY,
                            ZONE,
                            REMARKS_DATE,
                       x.MIME_TYPE attch_mimetype, x.FILE_NAME,t.USER_SIL attch 
                       from 
                        (SELECT distinct A. login_id USERS,
                            A.REQ_NO,
                            A.BRANCH_ID,
                            C.FULL_NAME_ENG PREPARED,
                            C.USER_LEVEL,
                            D.NAME_ENG DESIG,
                            E.AUTHORITY,
                            E.NAME_ENG ZONE,
                            max(a.sign_date)--max(TO_CHAR (f.SIGN_DATE, 'DD-MM-YYYY HH24:MI:SS AM'))
                               REMARKS_DATE,
                       g.MIME_TYPE, g.FILE_NAME--t.USER_SIL attaches
                       FROM (SELECT distinct LOGIN_ID, REQ_NO, BRANCH_ID,max(to_char(SIGN_DATE, 'DD-MM-YYYY HH24:MI:SS AM')) SIGN_DATE
                            FROM BUDGET_SIGNS
                            WHERE LOGIN_ID IS NOT NULL
                        group by LOGIN_ID, REQ_NO, BRANCH_ID
                       
                       UNION
                   
                       SELECT distinct CREATED_BY LOGIN_ID , REQ_NO, BRANCH_ID,max(to_char(SIGN_DATE, 'DD-MM-YYYY HH24:MI:SS AM')) SIGN_DATE
                       FROM BUDGET_SIGNS
                       WHERE LOGIN_ID IS NOT NULL
                       group by  CREATED_BY , REQ_NO, BRANCH_ID
                        ) A,
                            APP_USERS C,
                            budget.designations D,
                            BRANCHS E,
                            BUDGET_SIGNS f,
                            USER_DEG_SIL g,
                            BDG_REQ_MST h
                      WHERE     A.BRANCH_ID IS NOT NULL
                            AND A.LOGIN_ID = C.LOGIN_ID
                            AND C.DESIG_ID = D.ID
                            AND A.BRANCH_ID = E.ID
                            AND H.BDG_REQ_MST_ID = :P154_REQ_MST_ID
                             AND A.REQ_NO= h.BDG_REQ_MST_ID
                            AND A.BRANCH_ID = :G_BRANCH_ID
                            and a.REQ_NO = f.REQ_NO       
                            and a.LOGIN_ID=g. LOGIN_ID (+)       
                   GROUP BY A.LOGIN_ID,
                            A.REQ_NO,
                            A.BRANCH_ID,
                            C.FULL_NAME_ENG,
                            C.USER_LEVEL,
                            D.NAME_ENG,
                            E.AUTHORITY,
                            E.NAME_ENG,
                            g.MIME_TYPE, g.FILE_NAME) x 
                            left join USER_DEG_SIL t on 
                            x.USERS=t.LOGIN_ID (+)
                            ;
                         


	CURSOR LVL_01  IS 
	   select SHORT_NAME,ID 
	 from HEAD_INFOS
      where PARENT_ID  is null;

	  CURSOR LVL_03 IS 
	  
                    SELECT X.ID,
                           X.U_ID,
                           X.HEAD_NAME,
                           Y.OLD_PRV_ACTUAL_EXPENSE_AMT,
                           Y.PRV_APPROVE_AMT,
                           Y.PRV_CURRENT_EXPENSE_AMT,
                           Y.PRV_EXPECT_EXPENSE_AMT,
                           Y.BDG_REQ_AMT,
                           Y.BDG_REMARKS
                      FROM (SELECT ID, U_ID, NAME_ENG || ' (' || NAME_BNG || ')' HEAD_NAME
                              FROM HEAD_INFOS
                             WHERE ID IN
                                       (SELECT ID
                                          FROM HEAD_INFOS
                                         WHERE PARENT_ID =V_LVL_10_ID))X,
                           (SELECT A.HEAD_ID,
                                   A.OLD_PRV_ACTUAL_EXPENSE_AMT,
                                   A.PRV_APPROVE_AMT,
                                   A.PRV_CURRENT_EXPENSE_AMT,
                                   A.PRV_EXPECT_EXPENSE_AMT,
                                   A.BDG_REQ_AMT,
                                   A.BDG_REMARKS
                              FROM BDG_REQ_DTL A, BDG_REQ_MST C
                             WHERE     A.REQ_MST_ID = C.BDG_REQ_MST_ID
                                   AND C.BRANCH_ID = (SELECT BRANCH_ID
                                                        FROM APP_USERS
                                                       WHERE UPPER (TRIM (LOGIN_ID)) = :APP_USER))
                           Y
                     WHERE Y.HEAD_ID = X.ID(+)
                     AND X.ID IS NOT NULL;


 CURSOR LVL_10 IS 
             select x.PARENT_ID,y.head_name 
                  from (SELECT  distinct h.PARENT_ID
                      FROM BDG_REQ_DTL A, BDG_REQ_MST C,HEAD_INFOS h
                       WHERE A.REQ_MST_ID = C.BDG_REQ_MST_ID
                            and A.HEAD_ID=h.id
                           AND C.BRANCH_ID = (SELECT BRANCH_ID
                                    FROM APP_USERS
                                   WHERE UPPER (TRIM (LOGIN_ID)) = :APP_USER)) x,
                           (select ID,NAME_ENG || ' (' || NAME_BNG || ')' HEAD_NAME from HEAD_INFOS where PARENT_ID=V_LVL_01_ID) y
                           where x.PARENT_ID=y.id;

	  
    --    SELECT ID, U_ID, NAME_ENG || ' (' || NAME_BNG || ')' HEAD_NAME
    --                   FROM HEAD_INFOS
    --                  WHERE PARENT_ID = V_LVL_01_ID ;

 


 BEGIN  


         /* BEGIN
            SELECT  USER_IMG, MIME_TYPE
              INTO  v_blob, l_mime_type
              FROM  USER_IMG_SIG
             WHERE  UPPER(TRIM(LOGIN_ID)) = UPPER(TRIM(:APP_USER)) 
               AND  ROWNUM = 1;
          
          exception when no_data_found then
           v_blob := null;
           l_step := 'Empty!';
           END; */

        BEGIN
        SELECT B.SUBJECT_NAME, A.NAME_BNG, A.ADDRESS_BNG INTO V_SUBJECT, V_BRANCH, V_ADDRESS_ENG
          FROM BRANCHS A, SUBJECTS B,  BDG_REQ_MST C
         WHERE C.BDG_SUBJECT = B.SUBJECT_ID(+)
		   AND C.BRANCH_ID   = A.ID(+)
           AND C.BRANCH_ID   = (SELECT BRANCH_ID FROM APP_USERS WHERE UPPER(TRIM(LOGIN_ID)) = :APP_USER)
                and rownum=1;
         EXCEPTION WHEN NO_DATA_FOUND THEN
         V_SUBJECT    := null;
        END;

        
 htp.p('

 <script language="javascript">
function printdiv(printpage)
{
var headstr = "<html><head><title></title></head><body>";
var footstr = "</body>";
var newstr = document.all.item(printpage).innerHTML;
var oldstr = document.body.innerHTML;
document.body.innerHTML = headstr+newstr+footstr;
window.print();
document.body.innerHTML = oldstr;
return false;
}
</script>');


-- htp.p('
-- <input id="b_print" type="button" style="float:right;"  onClick="printdiv(''div_print9'');window.location.reload();" value=" Print ">
--      <input id="b_print" type="button" style="float:right;"  onClick="tableToExcel(''div_print1'');" value=" Excel ">');
       htp.p('<div  class="page">   
       <div id="div_print9" align="center">');

Htp.p('<div id="left">
            <th> <img src="#APP_FILES#wasa.png" height="60px" width="60px"/> </th></tr>');
     Htp.p('</div>');

Htp.p('<div id="center">
      <table width="100%">

      <th rowspan="1"  width="100%">ঢাকা পানি সরবরাহ ও পয়: নিষ্কাশন কর্তৃপক্ষ<br> নির্বাহী প্রকৌশলীর দপ্তর, '||' '||V_BRANCH||' <br> '||V_ADDRESS_ENG||' </th>
      </tr> ');
    htp.p('</table>');
      Htp.p('</div>');

     Htp.p('<div id="right">
            <th> <img src="#APP_FILES#MUJIB.png" height="60px" width="60px"/> <br> <h6>শেখ হাসিনার মূলনীতি <br>গ্রাম শহরের উন্নতি </h6></th></tr>');
     Htp.p('</div>');


htp.p('<div class="NOBORDER">
      <table width="100%"><tr><td font-weight="bold"  align="left">&nbsp;</td></tr>');
 htp.p('</table><br></div>');

htp.p('<div class="NOBORDER">
      <table width="100%"><tr><td font-weight="bold"  align="right">Print Date: '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS')||'</td></tr>');

 htp.p('</table><br></div>'); 
 


      htp.p('<div align="center" style= "font-size: 14px; font-weight:bold; margin-top: 10px;">
            <tr>
          <th colspan="1" align="center">'||'Subject Name:'||'</th>
          <td align="center" >'||V_SUBJECT||'</td>
         </tr> 
            </div>');


htp.p('<div class="dcpl">  <table width="100%" style="border-collapse: collapse;margin-top: 20px;">
       <thead>
            <tr bgcolor="#e0e0e0">

            <th class="head1" align="CENTER" style="color: white;"><span style="color: white;">Serial</span></th> 
             <th class="head1" align="CENTER" style="color: white;">Memo No</th>    
              <th class="head1" align="CENTER" style="color: white;">Requisition Date</th>
               <th class="head1" align="CENTER" style="color: white;">Financial Year</th>
                <th class="head1" align="CENTER" style="color: white;"> PRESENT USER</th>
                 <th class="head1" align="CENTER" style="color: white;">APPROVE STUTAS</th>
                  <th class="head1" align="CENTER" style="color: white;">FINAL APPROVE STUTAS</th>
                  <th class="head1" align="CENTER" style="color: white;">PRIORITY</th>

        </tr>
      </thead>'); 
     
       FOR  T IN (  SELECT A.MEMO_NO, A.REQ_DT, B.NAME_ENG, A.PRESENT_USER, A.APPROVE_STUTAS,  A.FINAL_APPROVE_STUTAS, C.PRIORITY_NAME
                      FROM BDG_REQ_MST A, FINANCIAL_YEARS B, PRIORITYS C
                     WHERE A.FI_YEAR_ID     = B.ID
                       AND A.BDG_PRIORITY   = C.PRIORITY_ID
                       AND A.BRANCH_ID   = (SELECT BRANCH_ID FROM APP_USERS WHERE UPPER(TRIM(LOGIN_ID)) = :APP_USER)
       )
  LOOP
   
 n:=n+1;
    htp.p('<tr>
           <td align="center">'||N||'</td>
           <td align="center">'||T.MEMO_NO||'</td>
            <td align="center">'||T.REQ_DT||'</td>
             <td align="center">'||T.NAME_ENG||'</td>
              <td align="center">'||T.PRESENT_USER||'</td>
               <td align="center">'||T.APPROVE_STUTAS||'</td>
                <td align="center">'||T.FINAL_APPROVE_STUTAS||'</td>
                 <td align="center">'||T.PRIORITY_NAME||'</td>
          </tr>');
 END LOOP;

 htp.p('</table><br></div>'); 

htp.p('<div class="dcpl">  <table width="100%" style="border-collapse: collapse; margin-top: 20px;">');
            FOR I IN LVL_01 
              LOOP
             V_LVL_01_ID := I.ID;
             
            htp.p('
                <thead>
                        <tr><td style="border:none;" colspan="7"; font-size="10"; background-color="#66CDAA;">'||I.SHORT_NAME||'<b></b></td></tr>
 
                            <tr bgcolor="#e0e0e0">
                              <th class="head1" align="CENTER">Serial</th>
                              <th class="head1" align="CENTER">UID</th>
                              <th class="head1" align="CENTER">Head Name</th>
                              <th class="head1" align="CENTER">Estimate<br>2023-24</th>
                              <th class="head1" align="CENTER">Revise<br>2022-23</th>
                              <th class="head1" align="CENTER">Approve<br>2022-23</th>
                              <th class="head1" align="CENTER">Actual Expence<br>2022-23</th>
                              <th class="head1" align="CENTER">Actual Expence<br>2021-22</th>
                              <th class="head1" align="CENTER">Remarks</th>
                              </tr>
                              </thead>');

    FOR Z IN LVL_10 
      LOOP
        V_LVL_10_ID := Z.PARENT_ID;
        n1 :=0;
        htp.p('<tr><td style="border:none;" colspan="7"; font-size="10"; background-color="#66CDAA;">'||Z.HEAD_NAME||'<b></b></td></tr>');
    FOR J IN LVL_03 
        
         LOOP 
         
          n1:=n1+1;
           
            htp.p('
            <tr>
                  <td align="center">'||N1||'</td>
           <td align="Left">'||J.U_ID||'</td> 
           <td align="Left">'||J.HEAD_NAME||'</td>
             <td align="Right">'||J.BDG_REQ_AMT||'</td>
              <td align="Right">'||J.PRV_EXPECT_EXPENSE_AMT||'</td>
               <td align="Right">'||J.PRV_APPROVE_AMT||'</td> 
                <td align="Right">'||J.PRV_CURRENT_EXPENSE_AMT||'</td>
                 <td align="Right">'||J.OLD_PRV_ACTUAL_EXPENSE_AMT||'</td>
                   <td align="center">'||J.BDG_REMARKS||'</td>
             

          </tr>');

 END LOOP;
             for t in (select sum(x.OLD_PRV_ACTUAL_EXPENSE_AMT) OLD_PRV_ACTUAL_EXPENSE_AMT
                 ,sum(x.PRV_APPROVE_AMT) PRV_APPROVE_AMT
                 ,sum(x.PRV_CURRENT_EXPENSE_AMT) PRV_CURRENT_EXPENSE_AMT
                 ,sum(x.PRV_EXPECT_EXPENSE_AMT) PRV_EXPECT_EXPENSE_AMT
                 ,sum(x.BDG_REQ_AMT) BDG_REQ_AMT
                from (select head_id, OLD_PRV_ACTUAL_EXPENSE_AMT,PRV_APPROVE_AMT,PRV_CURRENT_EXPENSE_AMT,BDG_REQ_AMT,PRV_EXPECT_EXPENSE_AMT from BDG_REQ_DTL d,BDG_REQ_MST C where d.REQ_MST_ID = c.BDG_REQ_MST_ID
                and c.BRANCH_ID = (SELECT BRANCH_ID
                                     FROM APP_USERS
                                     WHERE UPPER (TRIM (LOGIN_ID)) = :APP_USER)) x, 
                (select id ,NAME_BNG from HEAD_INFOS where parent_id=V_LVL_10_ID) y
                where x.head_id=y.id )
                loop
 htp.p('<tr bgcolor ="#FAF0E6">   
          <td colspan="3" align="right">SUBTOTAL</td>
          <td align="RIGHT">'||t.BDG_REQ_AMT||'</td>
          <td align="RIGHT">'||t.PRV_EXPECT_EXPENSE_AMT||'</td>
          <td align="RIGHT">'||t.PRV_APPROVE_AMT||'</td>
		  <td align="RIGHT">'||t.PRV_CURRENT_EXPENSE_AMT||'</td>
		  <td align="RIGHT">'||t.OLD_PRV_ACTUAL_EXPENSE_AMT||'</td>
		  <td align="RIGHT"></td>
         </tr> ');
         end loop;
 end loop;

--  FOR  CN IN 
--        (SELECT A.REQ_MST_ID,
--                sum(A.OLD_PRV_ACTUAL_EXPENSE_AMT) OLD_PRV_ACTUAL_EXPENSE_AMT,
--                sum(A.PRV_APPROVE_AMT) PRV_APPROVE_AMT,
--                sum(A.PRV_CURRENT_EXPENSE_AMT) PRV_CURRENT_EXPENSE_AMT,
--                sum(A.PRV_EXPECT_EXPENSE_AMT) PRV_EXPECT_EXPENSE_AMT,
--                sum(A.BDG_REQ_AMT) BDG_REQ_AMT
          
--           FROM BDG_REQ_DTL A, BDG_REQ_MST C
--          WHERE     A.REQ_MST_ID = C.BDG_REQ_MST_ID
		           
--                   AND C.BRANCH_ID = (SELECT BRANCH_ID
--                                     FROM APP_USERS
--                                    WHERE UPPER (TRIM (LOGIN_ID)) = :APP_USER)
		
-- 									   GROUP BY A.REQ_MST_ID
-- 								   )
                 
--   LOOP
--    htp.p('<tr bgcolor ="#FAF0E6">   
--           <td colspan="3" align="right">'||'Sub Total'||'</td>
--           <td align="center">'||CN.BDG_REQ_AMT||'</td>
--           <td align="center">'||CN.PRV_EXPECT_EXPENSE_AMT||'</td>
--           <td align="center">'||CN.PRV_APPROVE_AMT||'</td>
-- 		  <td align="center">'||CN.PRV_CURRENT_EXPENSE_AMT||'</td>
-- 		  <td align="center">'||CN.OLD_PRV_ACTUAL_EXPENSE_AMT||'</td>
-- 		  <td align="center"></td>
         
       
        
--          </tr> ');
--       end loop;
     
   END LOOP;

  FOR  CT IN 
       (SELECT A.REQ_MST_ID,
               sum(A.OLD_PRV_ACTUAL_EXPENSE_AMT) OLD_PRV_ACTUAL_EXPENSE_AMT,
               sum(A.PRV_APPROVE_AMT) PRV_APPROVE_AMT,
               sum(A.PRV_CURRENT_EXPENSE_AMT) PRV_CURRENT_EXPENSE_AMT,
               sum(A.PRV_EXPECT_EXPENSE_AMT) PRV_EXPECT_EXPENSE_AMT,
               sum(A.BDG_REQ_AMT) BDG_REQ_AMT
          
          FROM BDG_REQ_DTL A, BDG_REQ_MST C
         WHERE     A.REQ_MST_ID = C.BDG_REQ_MST_ID
                  AND C.BRANCH_ID = (SELECT BRANCH_ID
                                    FROM APP_USERS
                                   WHERE UPPER (TRIM (LOGIN_ID)) = :APP_USER)
		
									   GROUP BY A.REQ_MST_ID
								   )
                 
  LOOP
   htp.p('</br><tr bgcolor ="#F0F8F">   
          <td colspan="3" align="right">'||'Grand Total'||'</td>
          <td align="RIGHT">'||CT.BDG_REQ_AMT||'</td>
          <td align="RIGHT">'||CT.PRV_EXPECT_EXPENSE_AMT||'</td>
          <td align="RIGHT">'||CT.PRV_APPROVE_AMT||'</td>
		  <td align="RIGHT">'||CT.PRV_CURRENT_EXPENSE_AMT||'</td>
		  <td align="RIGHT">'||CT.OLD_PRV_ACTUAL_EXPENSE_AMT||'</td>
		  <td align="RIGHT"></td>
         
       
        
         </tr> ');
      end loop;
 htp.p('</table><br></DIV>');

   htp.p('
            <table style="width: 100%">
                <tr>
                <td style="width: 25%; text-align: center; vertical-align: top;">');
                    htp.p('<div id="signature" style="height: 100%; width: 100%; align-items: end; justify-content: end;">');
                     for c1 in AUTH loop
                     IF c1.USER_LEVEL = 4 THEN
                      htp.p(
                        '<img src="data:'
                        || c1.attch_mimetype
                        ||'; base64,'
                        || apex_web_service.blob2clobbase64( c1.attch)
                        ||' " alt="Signature Image" width="150" height="50" margin-right: "10";">
                         ');
                          htp.p('</div>
                          <div style="height: auto; width: 100%; border-top: 2px solid #8300CE; text-align: center; color: #8300CE; margin-top: 4px; font-size: 12px; font-weight: bold;">
                           <p style="margin: 0px;">'||c1.REMARKS_DATE||' '||c1.USER_LEVEL|| '</p>
                            <p style="margin: 0px;">'||c1.Prepared||'</p>
                            <p style="margin: 0px;">'||C1.DESIG||'</p>
                           <p style="margin: 0px;">'||C1.ZONE||', ঢাকা ওয়াসা</p>
                           </div>');
                     END IF;
                    end loop;
                    htp.p('</div>');
            
                htp.p('</td>
                <td style="width: 25%; text-align: center; vertical-align: top;">');
                    htp.p('<div id="signature" style="height: 100%; width: 100%; align-items: end; justify-content: end;">');
                    for c1 in AUTH loop
                     IF c1.USER_LEVEL = 3 THEN
                      htp.p(
                        '<img src="data:'
                        || c1.attch_mimetype
                        ||'; base64,'
                        || apex_web_service.blob2clobbase64( c1.attch)
                        ||' " alt="Signature Image" width="150" height="50" margin-right: "10";">
                         ');
                          htp.p('</div>
                          <div style="height: auto; width: 100%; border-top: 2px solid #8300CE; text-align: center; color: #8300CE; margin-top: 4px; font-size: 12px; font-weight: bold;">
                           <p style="margin: 0px;">'||c1.REMARKS_DATE||'</p>
                            <p style="margin: 0px;">'||c1.Prepared||'</p>
                            <p style="margin: 0px;">'||C1.DESIG||'</p>
                           <p style="margin: 0px;">'||C1.ZONE||', ঢাকা ওয়াসা</p>
                           </div>');
                     END IF;
                    end loop;
                    htp.p('</div>');
                    
                htp.p('</td>
                <td style="width: 25%; text-align: center; vertical-align: top;">');
                    htp.p('<div id="signature" style="height: 100%; width: 100%; align-items: end; justify-content: end;">');
                     for c1 in AUTH loop
                     IF c1.USER_LEVEL = 2 THEN
                      htp.p(
                        '<img src="data:'
                        || c1.attch_mimetype
                        ||'; base64,'
                        || apex_web_service.blob2clobbase64( c1.attch)
                        ||' " alt="Signature Image" width="150" height="50" margin-right: "10";">
                         ');
                          htp.p('</div>
                          <div style="height: auto; width: 100%; border-top: 2px solid #8300CE; text-align: center; color: #8300CE; margin-top: 4px; font-size: 12px; font-weight: bold;">
                           <p style="margin: 0px;">'||c1.REMARKS_DATE||'</p>
                            <p style="margin: 0px;">'||c1.Prepared||'</p>
                            <p style="margin: 0px;">'||C1.DESIG||'</p>
                           <p style="margin: 0px;">'||C1.ZONE||', ঢাকা ওয়াসা</p>
                           </div>');
                     END IF;
                    end loop;
                    htp.p('</div>');
            
                htp.p('</td>
                <td style="width: 25%; text-align: center; vertical-align: top;">');
                    htp.p('<div id="signature" style="height: 100%; width: 100%; align-items: end; justify-content: end;">');
                     for c1 in AUTH loop
                     IF c1.USER_LEVEL = 1 THEN
                      htp.p(
                        '<img src="data:'
                        || c1.attch_mimetype
                        ||'; base64,'
                        || apex_web_service.blob2clobbase64( c1.attch)
                        ||' " alt="Signature Image" width="150" height="50" margin-right: "10";">
                         ');
                          htp.p('</div>
                          <div style="height: auto; width: 100%; border-top: 2px solid #8300CE; text-align: center; color: #8300CE; margin-top: 4px; font-size: 12px; font-weight: bold;">
                           <p style="margin: 0px;">'||c1.REMARKS_DATE||'</p>
                            <p style="margin: 0px;">'||c1.Prepared||'</p>
                            <p style="margin: 0px;">'||C1.DESIG||'</p>
                           <p style="margin: 0px;">'||C1.ZONE||', ঢাকা ওয়াসা</p>
                           </div>');
                     END IF;
                    end loop;
                    htp.p('</div>');
                    
                htp.p('</td>
                </tr>
                
            </table>
        ');
    htp.p('</br></br></br></br></br></br></br>');

 

END;