<?php
/**********************************************************************

	Released under the terms of the GNU General Public License, GPL,
***********************************************************************/
$page_security = 'SA_ITEMSVALREP';
// ----------------------------------------------------------------
// $ Revision:	2.0 $
// Creator:	Joe Hunt
// date_:	2005-05-19
// Title:	Stock Check Sheet
// ----------------------------------------------------------------
$path_to_root="..";

include_once($path_to_root . "/includes/session.inc");
include_once($path_to_root . "/includes/date_functions.inc");
include_once($path_to_root . "/includes/data_checks.inc");
include_once($path_to_root . "/gl/includes/gl_db.inc");
include_once($path_to_root . "/payroll/includes/db/employees_db.inc");
include_once($path_to_root . "/payroll/includes/db/job_details_db.inc");
//----------------------------------------------------------------------------------------------------

print_petty_cash_details();

function get_petty_cash_details_report($employee,$from_date,$to_date)
{
	 
    $sql = "SELECT ou.*, 
                st.value_name status_name,
                pt.description project_task,
                pm.description project, 
                sc.value_name status,
                em.*
            FROM " 
                .TB_PREF."i_owe_you ou, "
                .TB_PREF."employee_master em, "
                .TB_PREF."list_values st, "
                .TB_PREF."list_values sc, "
                .TB_PREF."project_tasks pt,"
                .TB_PREF."projects_master pm     
            WHERE ou.type = st.id AND 
                ou.project_id = pm.project_id AND pt.task_id=ou.task_id AND 
                ou.status_code=sc.value_code AND sc.list_code = 'RECORD_APPROVAL_STATUS' AND em.employee_code=ou.employee_code
                AND ou.tran_date >=".db_escape($from_date)." AND ou.tran_date <=".db_escape($to_date);
          
                
         if($employee != "")
         {
           $sql .= " AND em.employee_code = ".db_escape($employee);
         }
        
        return  $result= db_query($sql,"Petty Cash details could not be retreived");
}




 
function get_department_employees($date,$employee)
{
	 $sql = "SELECT  ed.*, 
                    IFNULL(d.value_code,'No Department Entered') department_code),
                    d.value_name department_name,
                    em.initials,
                    em.last_name,
                    em.first_name,
                    edl.designation
            FROM ".TB_PREF."employee_departments ed 
                    INNER JOIN ".TB_PREF."employee_master em ON 
                            ed.employee_code = em.employee_code 
                    INNER JOIN ".TB_PREF."list_values d ON 
                            ed.department_id = d.id 
                    LEFT JOIN ".TB_PREF."employment_details edl ON 
                            ed.employee_code = edl.employee_code AND 
                            edl.contract_start_date <= ".db_escape($date)." AND 
                            edl.contract_end_date >= ".db_escape($date)." 
                    WHERE ed.start_date <= ".db_escape($date)." AND 
                    ed.end_date >= ".db_escape($date);
         
         if($employee != "")
         {
             $sql .= " AND ed.employee_code =". db_escape($employee);
             
         }
             $sql .= " ORDER BY d.value_name, ed.employee_code";
                
        
        return  db_query($sql,"an employee could not be retreived");
}

function get_employeee_details($employee)
{
        $sql = "SELECT em.* 
                FROM ".TB_PREF."employee_master em ";
          if($employee != "")
          {
               $sql .= " WHERE em.employee_code = ".db_escape($employee);
            }
   	return $result = db_query($sql, "Employee details could not be retrieved");

       // return db_fetch($result);
}

function print_headers($rep)
{
    $count = count($rep->headers);
        for ($i = 0; $i < $count; $i++){
                $rep->LineTo($rep->cols[$i], $rep->row+15, $rep->cols[$i+1], $rep->row+15);
                $rep->TextCol($i, $i + 1, $rep->headers[$i]);
                $rep->LineTo($rep->cols[$i], $rep->row-5, $rep->cols[$i+1], $rep->row-5);
        }
}

function display_petty_cash_expences($petty_cash_id,$rep)
{
   
    $rep->NewLine(2);
    $cols = array(0, 150, 300, 400,500);
    $headers = array(_('Expence Description'), _('Date'),_('Invoice No.'),_('Amount'));
    $aligns = array('left','left','left','right'); 
    $rep->Info(null, $cols, $headers, $aligns);
    
    print_headers($rep);
    $rep->NewLine(2);
    $total_expences=0;
    $res_wo_expences_rows=get_i_owe_you_expences_for_report($petty_cash_id);
    while($res_wo_expences=db_fetch($res_wo_expences_rows))
    {
        $total_expences +=$res_wo_expences['amount'];
        $rep->TextCol(0, 1, $res_wo_expences['expence_description']);
        $rep->TextCol(1, 2, sql2date($res_wo_expences['expence_date']));	
        $rep->TextCol(2, 3, $res_wo_expences['bill_no']);
        $rep->TextCol(3, 4, number_format($res_wo_expences['amount'],2));
        $rep->NewLine();
    
    }
        $rep->NewLine();
        $rep->Font('B');
        $rep->TextCol(2, 3, "Total Expences");          
        $rep->TextCol(3, 4, number_format($total_expences,2));
        $rep->Font('');
        $rep->NewLine();
        return $total_expences;
    
    
}

function get_i_owe_you_expences_for_report($iou_id)
{
        $sql= "SELECT *
               FROM " .TB_PREF."i_owe_you_expenses
               WHERE iou_id=". db_escape($iou_id);


	$sql .=" ORDER BY id";
        
	return $result=db_query($sql,"Could not retrieve i owe you expences");	
       //  db_fetch($result);
    
}
function print_petty_cash_details()
{
    global $path_to_root, $pic_height;
        
        $employee = $_POST['PARAM_0'];       
        $from_date = $_POST['PARAM_1'];
        $to_date = $_POST['PARAM_2'];
        $destination = $_POST['PARAM_3'];
        
	if ($destination)
		include_once($path_to_root . "/reporting/includes/excel_report.inc");
	else
		include_once($path_to_root . "/reporting/includes/pdf_report.inc");
	
	$orientation = 'P';
        $rep = new FrontReport(_('Petty Cash Details'), "PettyCashDetails", 'A4', 9, $orientation);
        
        if($employee != "")
        {
            $employee_detials = get_employeee_details($employee);
            $name = "";
            if($employee_data=db_fetch($employee_detials))
             {
                $name = "[".$employee_data['employee_code']."] - ".$employee_data['initials'].
                            " ".$employee_data['last_name']." (".$employee_data["first_name"].") ";
             }
                $employee_name = $name;
            
        }
        else
        {
            $employee_name = "All";
            
        }
     
        
                
        $params =   array( 	0 => $employee_name,
                                1 => $from_date,
                                2 => $to_date  
                    );
        
        $rep->SetHeaderType('form_974');
        $rep->NewPage();
 
        $from_date=date2sql($from_date);
        $to_date=date2sql($to_date);
        $total_cash=0;
        $total_petty_cash_recved=0;
        $current_employee="";                 
        $result_petty_cash = get_petty_cash_details_report($employee,$from_date,$to_date);
         
            while ($petty_cash_rows = db_fetch($result_petty_cash))
            {
                
                $cols = array(0,150,250,350,450,500);
                $headers = array('Project','Task','Petty Cash Type','Date','Amount');
                $aligns = array('left',	'left','left','left','right');
                $rep->Font();
                $rep->Info($params, $cols, $headers, $aligns);
                
                
                if($current_employee !="")
                {
                    if($current_employee != $petty_cash_rows['employee_code'])
                    {
                        $rep->NewLine();
                        $rep->fontSize += 1;                  
                        $rep->Font('B');
                        $rep->TextCol(3, 4, "Petty Cash Total");
                        $rep->TextCol(4, 5, number_format($total_cash,2));
                        $rep->NewLine();                 
                        $rep->TextCol(3, 4, "Expences Total");
                        $rep->TextCol(4, 5, number_format($total_petty_cash_recved,2));
                        $rep->fontSize -= 1;
                        $rep->Font('');
                        $total_cash=0;
                        $total_petty_cash_recved=0;
                        
                    }
                }
                
                $name = "";
                $name = "[".$petty_cash_rows['employee_code']."] - ".$petty_cash_rows['initials'].
                        " ".$petty_cash_rows['last_name']." (".$petty_cash_rows["first_name"].") ";
                                   
                $employee_name = $name;
                
                if($current_employee != $petty_cash_rows['employee_code'])
                {
                    $rep->NewLine(2);
                    $rep->Font('B');
                    $rep->fontSize += 3;
                    $rep->TextCol(0, 3, $employee_name );
                    $rep->fontSize -= 3;
                    $rep->Font('');
                }
                $rep->NewLine();
                $rep->fontSize += 2;
                $rep->NewLine(2);
                $rep->Font('B');
                $rep->TextCol(0, 1, "Petty Cash No :".$petty_cash_rows['id']);
                $rep->fontSize -= 2;
                $rep->Font('');
                $rep->NewLine(2);
                print_headers($rep);                
                $rep->NewLine(2);
               
                $rep->TextCol(0, 1, $petty_cash_rows['project']);                                
                $rep->TextCol(1, 2, $petty_cash_rows['project_task']);    
                $rep->TextCol(2, 3, $petty_cash_rows['status_name']);
                $rep->TextCol(3, 4, sql2date($petty_cash_rows['tran_date']));
                $rep->TextCol(4, 5, number_format($petty_cash_rows['amount'],2)); 
                $rep->NewLine();  
                $total_cash += $petty_cash_rows['amount'];                
                $total_petty_cash_recved +=display_petty_cash_expences($petty_cash_rows['id'],$rep);
                $current_employee=$petty_cash_rows['employee_code'];
                $rep->NewLine(2);
               
            }
                 
                $rep->NewLine();
                $rep->fontSize += 1;                  
                $rep->Font('B');
                $rep->TextCol(2, 3, "Petty Cash Total");
                $rep->TextCol(3, 4, number_format($total_cash,2));
                $rep->NewLine();                 
                $rep->TextCol(2, 3, "Expences Total");
                $rep->TextCol(3, 4, number_format($total_petty_cash_recved,2));
                $rep->fontSize -= 1;
                $rep->Font('');
                $total_cash=0;
                $total_petty_cash_recved=0;
                        
                   
            
$rep->End();  
}




