<?php
header("Content-type: text/html; charset=windows-1256");

session_start();
if($_SESSION['dbName']==""){
header("location:../index.php");
}


include("config.inc.php");

 if($export ==1){
	$filename = "Exp_Visit" . date('Ymd') . ".xls";
	header("Content-Disposition: attachment; filename=\"$filename\"");
	header("Content-Type: application/vnd.ms-excel");
}  

$connection = mysql_connect($config['dbServer'],$config['dbUser'],$config['dbPass']) or die("Could not connect to DB");
mysql_select_db($_SESSION['dbName'],$connection) or die("Could not find DB");


$querylog="SELECT print as printaccess from ".$_SESSION['dbLabel'].".login where user_account='".$_SESSION['useraccount']."' 	and Upper(Login_Schema) = Upper('".$HTTP_SESSION_VARS['dbName']."')	";
$resultlog=mysql_query($querylog,$connection) or die(mysql_error());
while($rows=mysql_fetch_array($resultlog)){
	extract($rows);
}


if ($printaccess==0 or $printaccess=='')
echo'<BODY oncontextmenu="return false;">';
else
echo'<BODY>';
//Read From Setup Table							
$querysetup= " select DecimalBase, DecimalBase1, DecimalBase2, base1, base2, grid_color1, grid_color2, ".
             " report_color1, report_color2, Company_Name As CompanyName from setup ";
$resultsetup=mysql_query($querysetup,$connection) or die(mysql_error());
while($rowsetup=mysql_fetch_array($resultsetup)){
	extract($rowsetup);
}


if (empty($fromdate)) $fromdate = displayToday();
if (empty($todate))   $todate   = displayToday();

$ndate = explode('/',$fromdate);
$fromdate = $ndate[2]."-".$ndate[1]."-".$ndate[0]; 
$ndate = explode('/',$todate);
$todate = $ndate[2]."-".$ndate[1]."-".$ndate[0]; 

$message = ereg_replace("'"," ",$message);

$now=current_server_time($connection);
$dt=split(" ",$now[0]);
$printdate=split("-",$dt[0]);

 if($export !=1)
print <<<HERE
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1256" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1"> 
<meta name="viewport" content="width=device-width, initial-scale=1.0"> 


<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Visit Reports</title>

<style type="text/css">
table.params td { font-size: 12px; 
                  font-family:Times-New-Roman;
                 }

table.master th { font-size: 14px; 
                  font-weight: bold;
                  font-family:Times-New-Roman;
                      }				 
table.master td { font-size: 12px; 
                  font-family:Times-New-Roman;
                 }
table.group td { font-size: 12px; 
                 font-weight: bold;
				 font-style: italic;
                 font-family:Times-New-Roman;
               }				 
				 
.totals { font-size: 12px; 
          font-weight: bold;
          font-family:Times-New-Roman;
        }					  
				 
h4 { border-top-style: solid; 
	 border-top-color: black; 
	 border-top-width: 2px;
   }
h5 { border-top-style: solid; 
	 border-top-color: black; 
	 border-top-width: 1px;
   }   
  .header2{
	font-size:12px;
	font-weight:bold;
} 
.body2{
	font-size:12px;
}
</style>

</head>
<body >

HERE;

//$message='';

if($masterby == 2) {
	$title = 'List of Visits by Salesman';
	$tdtitle = 'Customer';
	$frmto = 'Salesman';
	$frmto2 = $fromsalesmancode ." - ". $tosalesmancode;
	$order = 1;
}
else if($masterby == 1) {
	$title = 'List of Visits by Customer';
	$tdtitle = 'Salesman';
	$frmto = 'Customer';
	$frmto2 = $fromcustomercode ." - ". $tocustomercode;
	$order = 2;
}

   $bright='border-right:black solid 1px;';
	$brleft='border-left:black solid 1px;';
	$brtop='border-top:black solid 1px;';
	$brbottom='border-bottom:black solid 1px;';
	//style='font-size: 11pt;font-weight: bold;$brleft;$bright;$brtop;'
echo"<table cellpadding='0' cellspacing='0' border='0'  align='center' class='layout-width' width='1600px'>";
echo "<col width='200'>";
if($chkdetails != 1) 
echo "<col width=100><col width='40'><col width='40'>
     <col width='70'><col width='100'><col width='40'>
	 <col width='200'><col width='200'><col width='150'>
	<col width='120'><col width='120'><col width='100'><col width='120'>";
else echo "<col width=120><col width='80'><col width='80'><col width='100'><col width='120'><col width='80'>
			<col width='205'><col width='205'><col width='205'><col width='205'>";
$colnbr = 10;
if($chkdetails != 1) $colnbr = $colnbr + 3;
echo "<tr>
  <td colspan='4'><b>$CompanyName</b></td>
  <td colspan='".($colnbr-4)."' align='center'><font size=4><b>$title</b></font></td>
  <td colspan='2'><font size=1><b> Printed Date: </b> ";

echo $printdate[2]."/".$printdate[1]."/".$printdate[0];

echo "<br><b>Time: </b>".$dt[1]." ".$dt[2];
echo "</tr>";

echo "<tr height='5'><td colspan=".($colnbr+2)."><h5></td></tr>";

if (empty($fromdate))  $fromdate = 'null';
else  $fromdate = "'".$fromdate."'"; 
if (empty($todate))  $todate = 'null';
else  $todate = "'".$todate."'";

if (empty($fromsalesmancode))  $fromsalesmancode = 'null';
else  $fromsalesmancode = "'".$fromsalesmancode."'"; 
if (empty($tosalesmancode))  $tosalesmancode = 'null';
else  $tosalesmancode = "'".$tosalesmancode."'"; 

if (empty($fromcustomercode))  $fromcustomercode = 'null';
else  $fromcustomercode = "'".$fromcustomercode."'"; 
if (empty($tocustomercode))  $tocustomercode = 'null';
else  $tocustomercode = "'".$tocustomercode."'";

echo "<tr>
  <td colspan='6' style='font-size:12px;'><b>Date From/To</b> &nbsp; $fromdate - $todate </td>
  <td colspan='6' style='font-size:12px;'><b>$frmto From/To</b> &nbsp; ".$frmto2." </td></tr>";
 
$headerst = 'font-size:14px;font-family:Times-New-Roman;'; 
echo "<tr height='5'><td colspan=".($colnbr+1)."><h4></td></tr>";
echo "<tr><td style='$headerst;' align='left' align='middle' rowspan='1'><b>$tdtitle Code/Name </b></td>";
echo "<td style='$headerst;' align='left'><b>Visit Date</b></td>";
echo "<td style='$headerst;' align='left'><b>Start Time</b></td>";
echo "<td style='$headerst;' align='left'><b>End Time</b></td>";
echo "<td style='$headerst;' align='left'><b>Type</b></td>";
echo "<td style='$headerst;' align='left' ><b>Follow up <br>Date</b></td>";
echo "<td style='$headerst;' align='left' ><b>Follow up Time</b></td>";
if($chkdetails != 1){
echo "<td style='$headerst;' align='left' ><b>Main Office</b></td>";
echo "<td style='$headerst;' align='left' ><b>Branch</b></td>";
echo "<td style='$headerst;' align='left' ><b>Project</b></td>";
}
echo "<td style='$headerst;'align='left'><b>Price offered</b></td>";
echo "<td style='$headerst;'align='left'><b>Gift</b></td>";
echo "<td style='$headerst;'align='left'><b>Credit Limit</b></td>";
echo "<td style='$headerst;'align='left'><b>Payment Terms</b></td></tr>";
echo "<tr><td colspan=".($colnbr+1)."><h4></td></tr>";
echo"<tr><td></td>";
echo "<td style='$headerst;'align='left' colspan='".($chkdetails == 1?4:5)."'><b>Commission</b></td>";
echo "<td style='$headerst;'align='left' colspan='".($chkdetails == 1?3:2)."'><b>Results</b></td>";
echo "<td style='$headerst;'align='left' colspan='".($chkdetails == 1?2:3)."'><b>Terms & Conditions</b></td>";
echo "<td style='$headerst;' align='left' colspan='".($chkdetails == 1?1:3)."'><b>Remarks</b></td></tr>";

echo "<tr height='5'><td colspan=".($colnbr+1)."><h4></td></tr>";

$query = "select visit_reference, Customer.customer_code as customer_code,Customer_Name,
		salesmen.Ledger_number as Ledger_number,Ledger_name,
		date_format(visit_date,'%d-%m-%Y') as visit_date,date_format(visit_starttime,'%H:%i') as visit_starttime,
		date_format(visit_endtime,'%H:%i') as visit_endtime,visit_note, visit_request,
		if(Visit_type=1,'Visit',if(Visit_type=2,'Call',if(Visit_type=3,'Mail',''))) as Visit_type,
		visit_followup, date_format(Visit_followupdate,'%d-%m-%Y')  as Visit_followupdate, date_format(visit_followTime,'%H:%i')  as visit_followTime,
		visit_priceoffered, visit_paymentterms, visit_creditlimit, visit_gift, visit_result,
		visit_commission,c1.office_address as main_address, c2.office_address as branch_address,
		customerproject_desc,Customer_Telephone1,Customer_Address, 
		visit.visit_mainofficeId as visit_mainofficeId,visit.visit_branchofficeId as visit_branchofficeId,
		visit.visit_projectId as visit_projectId
		from visit
		left join salesmen on salesmen.Ledger_number=visit.salesmen_code
		left join Customer on Customer.Customer_Code=visit.customer_code
		left join Customer_Offices c1 on c1.customer_officeId = visit.visit_mainofficeId
		left join Customer_Offices c2 on c2.customer_officeId = visit.visit_branchofficeId
		left join customer_project p on p.customerproject_id = visit.visit_projectId
		where (date_format(visit_date,'%Y-%m-%d') >=$fromdate and date_format(visit_date,'%Y-%m-%d') <=$todate) 
		and if(ifnull($fromsalesmancode,'') = '' or ifnull($tosalesmancode,'') = '',1=1,(visit.salesmen_code >= $fromsalesmancode and visit.salesmen_code <=$tosalesmancode))
		and if(ifnull($fromcustomercode,'') = '' or ifnull($tocustomercode,'') = '',1=1,(visit.customer_code >= $fromcustomercode and visit.customer_code <=$tocustomercode)) ";
if($order == 1) $query = $query." order by visit.salesmen_code, visit_date ";
else if($order == 2) $query = $query." order by visit.customer_code, visit_date";	//echo $query;	
$result = mysql_query($query,$connection);

$code = '';
$code2='';
$codename='';

$codedt='';
$codenamedt='';

$color = "style='background-color:".$report_color1."'";
$color2 = "style='background-color:".$report_color2."'";

while($rwdt = mysql_fetch_array($result)){
	extract($rwdt);
	if($order == 1) { $code2=$Ledger_number;$codename=$Ledger_name;$codedt=$customer_code;$codenamedt=$Customer_Name;}
	else if($order == 2) { $code2=$customer_code;$codename=$Customer_Name;$codedt=$Ledger_number;$codenamedt=$Ledger_name;}
	
	if($code != $code2)
	echo "<tr height='20px' $color><td colspan='".($colnbr+1)."' style='font-size:13px;font-family:Times-New-Roman;$brleft;$bright;$brtop;$brbottom'><b><i>$frmto &#8594; $code2 - $codename</b></i></td>
	</tr>";
	
	echo "<tr height='20px' $color2>";
	echo "<td align='left' style='padding-left:10px;font-size:12px;font-family:Times-New-Roman; $bright;$brtop;$brbottom' >$codedt - $codenamedt</td>";
	echo "<td style='font-size:12px;font-family:Times-New-Roman;$bright;$brtop;$brbottom'>$visit_date</td>";
	echo "<td style='font-size:12px;font-family:Times-New-Roman;$bright;$brtop;$brbottom'>$visit_starttime</td>";
	echo "<td style='font-size:12px;font-family:Times-New-Roman;$bright;$brtop;$brbottom'>$visit_endtime</td>";
	echo "<td style='font-size:12px;font-family:Times-New-Roman;$bright;$brtop;$brbottom'>$Visit_type</td>";
	echo "<td style='font-size:12px;font-family:Times-New-Roman;$bright;$brtop;$brbottom'>$Visit_followupdate</td>";
	echo "<td style='font-size:12px;font-family:Times-New-Roman;$bright;$brtop;$brbottom'>$visit_followTime</td>";
	
	if($chkdetails != 1){
	echo "<td style='font-size:12px;font-family:Times-New-Roman;$bright;$brtop;$brbottom' >$main_address</b></td>";
	echo "<td style='font-size:12px;font-family:Times-New-Roman;$bright;$brtop;$brbottom' >$branch_address</td>";
	echo "<td style='font-size:12px;font-family:Times-New-Roman;$bright;$brtop;$brbottom' >$customerproject_desc</td>";
	} 
	echo "<td style='font-size:12px;font-family:Times-New-Roman;$bright;$brtop;$brbottom'>$visit_priceoffered</td>";
	echo "<td style='font-size:12px;font-family:Times-New-Roman;$bright;$brtop;$brbottom'>$visit_gift</td>";
	echo "<td style='font-size:12px;font-family:Times-New-Roman;$bright;$brtop;$brbottom'>$visit_creditlimit</td>";
	echo "<td style='font-size:12px;font-family:Times-New-Roman;$bright;$brtop;$brbottom'>$visit_paymentterms</td></tr>";
	echo "<tr height='20px' $color2><td></td><td style='font-size:12px;font-family:Times-New-Roman;$brleft;$bright;$brtop;$brbottom;border-top:gray solid 1px;' colspan='".($chkdetails == 1?4:5)."'>$visit_commission</td>";
	echo "<td style='font-size:12px;font-family:Times-New-Roman;border-top:gray solid 1px;$bright;$brtop;$brbottom' colspan='".($chkdetails == 1?3:2)."'>$visit_result</td>";
	echo "<td style='font-size:12px;font-family:Times-New-Roman;border-top:gray solid 1px;$bright;$brtop;$brbottom' colspan='".($chkdetails == 1?2:3)."'>$visit_request</td>";
	echo "<td colspan='".($chkdetails == 1?1:3)."' style='font-size:12px;font-family:Times-New-Roman;$bright;$brtop;$brbottom;border-top:gray solid 1px;'>$visit_note</td>";
	echo "</tr>";
	if($chkdetails == 1){
	//main office	
		if($visit_mainofficeId != ''){
	echo "<tr height='10px' $color2 ><td></td><td colspan='5' style='font-size:12px;background-color:$report_color1;font-family:Times-New-Roman;'>
			<b><i>Main Office &#8594; $main_address</b></i></td><td colspan='".($colnbr-2)."'></td>
		</tr>";		
	echo "<tr><td></td><td colspan='10'>";
	echo "<table border='1' cellpadding='1' cellspacing='1'>";
	echo "<col width='230'><col width='230'><col width='150'><col width='100'><col width='120'><col width='120'><col width='120'><col width='165'><col width='165'>";
	echo "<tr $color><td><span class='header2'>First Name</span></td><td ><span class='header2'>last Name</span></td>
	<td colspan='2'><span class='header2'>Position</span></td><td><span class='header2'>Phone 1</span></td><td ><span class='header2'>Phone 2</span></td>
	<td><span class='header2'>Phone 3</span></td><td ><span class='header2'>Email 1</span></td><td ><span class='header2'>Email 2</span></td>
	</tr>";		
	
	$querymain = "select customer_officeId,contact_firstname,contact_lastname,
			contact_position, contact_phone1, contact_phone2, contact_phone3, contact_email1, 
			contact_email2
			from Customer_Offices,CustomerContactInfo
			where customeroffice_id = customer_officeId
			and  customer_officeId=$visit_mainofficeId "; 
	$resmain = mysql_query($querymain,$connection);
	$officeid='';
	$ct = 0;
	while($rowmain = mysql_fetch_array($resmain)){
	extract($rowmain);
	
	$ct+=1;
		//if($ct%2==0)
			$Color="style='background-color:".$grid_color1."'";
		//else			
		//	$Color="style='background-color:".$grid_color2."'";	
		
	echo  "<tr height='20px' $Color>";
	//if($customer_officeId != $officeid)
	echo "<td style='border-top:gray solid 1px;'><span class='body2'>$contact_firstname</span></td>";
	//else echo "<td ></td>";	
	
	echo"<td style='border-top:gray solid 1px;'><span class='body2'>$contact_lastname</span></td>".
		"<td style='border-top:gray solid 1px;' colspan='2'><span class='body2'>$contact_position</span></td>".
		"<td style='border-top:gray solid 1px;'><span class='body2'>$contact_phone1</span></td>".
		"<td style='border-top:gray solid 1px;'><span class='body2'>$contact_phone2</span></td>".
		"<td style='border-top:gray solid 1px;'><span class='body2'>$contact_phone3</span></td>".
		"<td style='border-top:gray solid 1px;'><span class='body2'>$contact_email1</span></td>".
		"<td style='border-top:gray solid 1px;'><span class='body2'>$contact_email2</span></td>".
		"</tr>";
	$officeid = $customer_officeId;	
	}	
	echo "</table></td></tr>";
	} //end of main office	
	//branch office	
		if($visit_branchofficeId != ''){
	echo "<tr $color2 height='5px'><td colspan=".($colnbr+2)."></td></tr>";		
	echo "<tr height='10px' $color2 ><td></td><td colspan='5' style='font-size:12px;background-color:$report_color1;font-family:Times-New-Roman;'>
			<b><i>Branch &#8594; $branch_address</b></i></td><td colspan='".($colnbr-2)."'></td>
		</tr>";	
	echo "<tr><td></td><td colspan='10'>";
	echo "<table border='1' cellpadding='1' cellspacing='1'>";
	echo "<col width='200'><col width='150'><col width='200'><col width='140'><col width='100'><col width='100'><col width='100'><col width='130'><col width='130'><col width='150'>";
		
	echo "<tr $color>
	<td ><span class='header2'>First Name</span></td>
	<td ><span class='header2'>Last Name</span></td>
	<td ><span class='header2'>Position</span></td>
	<td><span class='header2'>Phone 1</span></td>
	<td ><span class='header2'>Phone 2</span></td>
	<td><span class='header2'>Phone 3</span></td>
	<td ><span class='header2'>Email 1</span></td>
	<td ><span class='header2'>Email 2</span></td>
	</tr>";
	$querybranch = "select  c.customer_officeId as customer_officeId,contact_firstname,contact_lastname,
			contact_position, contact_phone1, contact_phone2, contact_phone3, contact_email1, 
			contact_email2,  c2.office_address as mainaddress
			from Customer_Offices as c
			left join CustomerContactInfo on customeroffice_id = c.customer_officeId
			left join Customer_Offices c2 on c2.customer_officeId = c.mainoffice_id
			where c.customer_officeId=$visit_branchofficeId"; 
			
			
	$resbranch = mysql_query($querybranch,$connection);
	$officeid='';
	$ct = 0;
//echo $querybranch; exit;
	while($rowbranch = mysql_fetch_array($resbranch)){
		extract($rowbranch);
		//$ct+=1;
		//if($ct%2==0)
			$Color="style='background-color:".$grid_color1."'";
		//else			
		//	$Color="style='background-color:".$grid_color2."'";	
		echo  "<tr height='20px' $Color>";
	//if($customer_officeId != $officeid)
	echo "<td style='border-top:gray solid 1px;'><span class='body2'>$contact_firstname</span></td>";
		
	//else echo "<td style='border-top:gray solid 1px;'></td><td colspan='2'></td>";	
	
	echo"<td style='border-top:gray solid 1px;'><span class='body2'>$contact_lastname</span></td>".
		"<td style='border-top:gray solid 1px;'><span class='body2'>$contact_position</span></td>".
		"<td style='border-top:gray solid 1px;'><span class='body2'>$contact_phone1</span></td>".
		"<td style='border-top:gray solid 1px;'><span class='body2'>$contact_phone2</span></td>".
		"<td style='border-top:gray solid 1px;'><span class='body2'>$contact_phone3</span></td>".
		"<td style='border-top:gray solid 1px;'><span class='body2'>$contact_email1</span></td>".
		"<td style='border-top:gray solid 1px;'><span class='body2'>$contact_email2</span></td>";
		
 //$officeid = $customer_officeId;
}
	echo "</table></td></tr>";
	}  //end branch table
	//project table
	if($visit_projectId != ''){
		echo "<tr $color2 height='5px'><td colspan=".($colnbr+2)."></td></tr>";		
		echo "<tr height='10px' $color2 ><td></td><td colspan='5' style='font-size:12px;background-color:$report_color1;font-family:Times-New-Roman;'>
			<b><i>Project &#8594; $customerproject_desc</b></i></td><td colspan='".($colnbr-2)."'></td>
		</tr>";	
	echo "<tr><td></td><td colspan='10'>";
	echo "<table border='1' cellpadding='1' cellspacing='1'>";
	echo "<col width='200'><col width='180'><col width='90'><col width='150'><col width='150'><col width='200'><col width='90'><col width='160'><col width='180'>";
		
	echo  "<tr $color>
	<td ><span class='header2'>Address</span></td>
	<td ><span class='header2'>Contact</span></td>
	<td><span class='header2'>Phone #</span></td>
	<td ><span class='header2'>Consultant Name</span>
	</td><td ><span class='header2'>Details</span></td>
	<td ><span class='header2'>Situation of the Construction</span>
	</td><td colspan='1'><span class='header2'>Iron Used</span></td>
	<td ><span class='header2'>Competitor Name</span></td>
	<td><span class='header2'>Related Project</span></td></tr>";
	
	$queryproj = "select p1.customerproject_id,p1.customerproject_desc as description, p1.customerproject_address as address,
			p1.customerproject_contact as contact,p1.customerproject_phone as phonenbr,
			p1.customerproject_consultantname as consultantname,p1.customerproject_consultantdetail as consultantdetail,
			p1.customerproject_situation as situation,p1.customerproject_ironname as ironname,
			p1.customerproject_competitorname as competitorname,p2.customerproject_desc as relatedproject_desc
			from customer_project p1
			left join customer_project p2 on p2.customerproject_id = p1.customerproject_parent
			where p1.customerproject_id = $visit_projectId"; 
	
/*$queryproj = "select customerproject_desc as description, customerproject_address as address,
              customerproject_contact as contact,customerproject_phone as phonenbr,
              customerproject_consultantname as consultantname,customerproject_consultantdetail as consultantdetail,
              customerproject_situation as situation,customerproject_ironname as ironname,
                customerproject_competitorname as competitorname
 from customer_project
			
			where customer_code = $customer_code"; 	*/
//echo $queryproj ; exit;			
	$resproj = mysql_query($queryproj,$connection);
$ct = 0;
while($rowproj = mysql_fetch_array($resproj)){
	extract($rowproj);
	
	//$ct+=1;
		//if($ct%2==0)
			$Color="style='background-color:".$grid_color1."'";
	//	else			
	///		$Color="style='background-color:".$grid_color2."'";	
		
	echo  "<tr height='15px' $Color>";
	
	echo "<td ><span class='body2'>$address</span></td>";
	echo"<td ><span class='body2'>$contact</span></td>".
		"<td><span class='body2'>$phonenbr</span></td>".
		"<td ><span class='body2'>$consultantname</span></td>".
		"<td ><span class='body2'>$consultantdetail</span></td>".
		"<td ><span class='body2'>$situation</span></td>".
		"<td><span class='body2'>$ironname</span></td>".
		"<td ><span class='body2'>$competitorname</span></td>".
		"<td><span class='body2'>$relatedproject_desc</span></td></tr>";

}	

	} //end project table	
	echo "</table></td></tr>";
	}
	$code = $code2;
	if($chkdetails != 1) echo "<tr $color2 height='5'><td colspan=".($colnbr)."><h4></td></tr>";
	else echo "<tr $color2 height='5'><td colspan=".($colnbr+2)."><h4></td></tr>";
}	

echo" </table>  ";//end of last table menu
function current_server_time($connection){

  $string="Select date_format(NOW(),'%Y-%m-%d %h:%i %p') ";
  $result=mysql_query($string,$connection) or die(mysql_error());
  $now=mysql_fetch_row($result);

  return $now;
}

function explodeword($date)
{

$trim = explode('/',$date);
$trim = "$trim[2]"."-"."$trim[1]"."-"."$trim[0]";
return $trim;
}
	

if ($printaccess==1)
print <<< HERE

<script language=javascript>
if (typeof(window.print) != 'undefined')
{
    window.print();
}
</script>
</body>

HERE;

?>