google_map.html
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" href="http://code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
<script src="http://code.jquery.com/jquery-1.10.2.js"></script>
<script src="http://code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
<link rel="stylesheet" href="http://jqueryui.com/resources/demos/style.css">
<script src="http://maps.googleapis.com/maps/api/js"> </script>
<script>
//var coordinates = [[22.5805149,120.4227832,'大連化學','image3'],[22.5789818,120.3178123,'建榮冷凍食品','image2']];
var offices = {"5310" : [25.055308,121.593377, "台北分公司"],
"5320" : [24.9545639,121.2026673, "桃園分公司"],
"5330" : [24.673791, 120.887685, "竹苗分公司"],
"5340" : [24.181775, 120.617205, "台中分公司"],
"5350" : [23.041664, 120.234495, "台南分公司"],
"5360" : [22.595090, 120.359171, "高雄分公司"]
};
function initialize(locations)
{
var coordinates = locations;
var oea15 = document.getElementById("oea15");
//alert(oea15.value);
//alert(offices[oea15.value][0] + "," + offices[oea15.value][1]);
var myCenter=new google.maps.LatLng(offices[oea15.value][0],offices[oea15.value][1]);
//var myCenter=new google.maps.LatLng(22.595090, 120.359171);
var mapProp = {
center:myCenter,
zoom:13,
mapTypeId:google.maps.MapTypeId.ROADMAP
};
var map=new google.maps.Map(document.getElementById("googleMap"),mapProp);
map.data.loadGeoJson('./twtown2010.4.json');
//map.data.loadGeoJson('./twcounty2010.6.json');
var image1 = {url : "http://labs.google.com/ridefinder/images/mm_20_red.png", size : new google.maps.Size(32, 32)};
var image2 = {url : "http://labs.google.com/ridefinder/images/mm_20_yellow.png", size : new google.maps.Size(32, 32)};
var image3 = {url : "http://labs.google.com/ridefinder/images/mm_20_green.png", size : new google.maps.Size(32, 32)};
var marker=new google.maps.Marker({
position:myCenter,
title: offices[oea15.value][2],
});
marker.setMap(map);
var idx;
for (idx=0; idx<coordinates.length; idx++) {
//alert(coordinates[idx]["LAT"] + "," + coordinates[idx]["LNG"] + "," + coordinates[idx]["OCC18"] + "," + coordinates[idx]["IMG"]);
var str = "var marker=new google.maps.Marker({ position:new google.maps.LatLng(" + coordinates[idx]["LAT"] + "," + coordinates[idx]["LNG"] + "), title: '" + coordinates[idx]["OCC18"] + "', icon:" + coordinates[idx]["IMG"] + " }); marker.setMap(map);"
eval(str);
}
//var marker=new google.maps.Marker({
// position:new google.maps.LatLng(22.5805149,120.4227832),
// title: '大連化學工業股份有限公司',
// icon: image3
//});
//marker.setMap(map);
}
google.maps.event.addDomListener(window, 'load', initialize);
</script>
<script>
$(function() {
$( "#from" ).datepicker({
defaultDate: "+1w",
changeMonth: true,
numberOfMonths: 3,
onClose: function( selectedDate ) {
$( "#to" ).datepicker( "option", "minDate", selectedDate );
}
});
$("#from").datepicker("option", "dateFormat", "yy/mm/dd");
$( "#to" ).datepicker({
defaultDate: "+1w",
changeMonth: true,
numberOfMonths: 3,
onClose: function( selectedDate ) {
$( "#from" ).datepicker( "option", "maxDate", selectedDate );
}
});
$("#to").datepicker("option", "dateFormat", "yy/mm/dd");
});
</script>
<script>
var Submit=function(){
var URLs="http://10.1.100.201/eip/report/google_map/getDNLatLng.php";
$.ajax({
url: URLs,
data: $('#DNLatLng').serialize(),
type:"POST",
dataType:'json',
success: function(msg){
//alert(msg);
initialize(msg)
},
error:function(xhr, ajaxOptions, thrownError){
alert(xhr.status);
alert(thrownError);
}
});
}
</script>
</head>
<body>
<form id="DNLatLng">
<label for="分公司">分公司</label>
<select name="oea15" id="oea15">
<option value="NA" selected="selected">===請選擇===</option>
<option value="5310">台北分公司</option>
<option value="5320">桃園分公司</option>
<option value="5330">竹苗分公司</option>
<option value="5340">台中分公司</option>
<option value="5350">台南分公司</option>
<option value="5360">高雄分公司</option>
</select>
<label for="from">送貨日期 From</label>
<input type="text" id="from" name="from">
<label for="to"> to</label>
<input type="text" id="to" name="to">
<input type=button id=btn1 value=送出 onClick="Submit()"> </input>
</form>
<div id="googleMap" style="width:800;height:900px;"></div>
</body>
</html>
getDNLatLng.php
<?php
$oea15 = $_REQUEST['oea15'];
$oea02b = $_REQUEST['from'];
$oea02e = $_REQUEST['to'];
$dbstr = "(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST=tiptopdb)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = topprod)))";
$dbconn = oci_connect("echo01","echo01","$dbstr","utf8");
$sql = "
select lat,lng,occ18,case when max(交運方式) != min(交運方式) then 'image2' when max(交運方式) = '外車' then 'image1' when max(交運方式) = '其它' then 'image3' end img
from (
select substr(to_char(oea02,'yyyymm'),1,6) yyyymm,oea15,oea43,ged02,
case when regexp_like(ged02,'大榮|大誠|日通|超峰|黑貓') then '外車' else '其它' end 交運方式,
count(*) 次數,oea04,occ18,occ11,--oeb04,replace(oeb06,',',null) oeb06,
sum(oeb14*oea24) oeb14,oeb01,
oeauser,zx02,oeb04,oeb06,oeb12
from echo01.oea_file a,ged_file b,echo01.occ_file,echo01.oeb_file,echo01.zx_file,ima_file
where oea43 = b.ged01
and oea04 = occ01
and oea01 = oeb01
and oeaconf = 'Y'
and a.oeauser = zx01
and oeb04 = ima01
and imaud03 is null --冰品
and regexp_like(oea01,'SO[123789]-')
and ta_oea04 != '3'
and oea15 = :oea15
and oea02 between to_date(:oea02b,'yyyy/mm/dd') and to_date(:oea02e,'yyyy/mm/dd')
group by substr(to_char(oea02,'yyyymm'),1,6),oea43,ged02,oea15,
case when regexp_like(ged02,'大榮|大誠|日通|超峰|黑貓') then '外車' else '其它' end,oea04,occ18,occ11,--oeb04,oeb06,
oeauser,zx02,oeb01,oeb04,oeb06,oeb12
having sum(oeb12*ta_ima003/1000) < 200 -- < 200L
union all
select substr(to_char(oea02,'yyyymm'),1,6) yyyymm,oea15,oea43,ged02,
case when regexp_like(ged02,'大榮|大誠|日通|超峰|黑貓') then '外車' else '其它' end 交運方式,
count(*) 次數,oea04,occ18,occ11,--oeb04,replace(oeb06,',',null) oeb06,
sum(oeb14*oea24) oeb14,oeb01,
oeauser,zx02,oeb04,oeb06,oeb12
from echo02.oea_file a,ged_file b,echo01.occ_file,echo01.oeb_file,echo01.zx_file,ima_file
where oea43 = b.ged01
and oea04 = occ01
and oea01 = oeb01
and oeaconf = 'Y'
and oeb04 = ima01
and imaud03 is null --冰品
and regexp_like(oea01,'SO[123789]-')
and ta_oea04 != '3'
and oea15 = :oea15
and oea02 between to_date(:oea02b,'yyyy/mm/dd') and to_date(:oea02e,'yyyy/mm/dd')
group by substr(to_char(oea02,'yyyymm'),1,6),oea43,ged02,oea15,
case when regexp_like(ged02,'大榮|大誠|日通|超峰|黑貓') then '外車' else '其它' end,oea04,occ18,occ11,--oeb04,oeb06,
oeauser,zx02,oeb01,oeb04,oeb06,oeb12
having sum(oeb12*ta_ima003/1000) < 200
union all
select substr(to_char(oea02,'yyyymm'),1,6) yyyymm,oea15,oea43,ged02,
case when regexp_like(ged02,'大榮|大誠|日通|超峰|黑貓') then '外車' else '其它' end 交運方式,
count(*) 次數,oea04,occ18,occ11,--oeb04,replace(oeb06,',',null) oeb06,
sum(oeb14*oea24) oeb14,oeb01,
oeauser,zx02,oeb04,oeb06,oeb12
from echo03.oea_file a,ged_file b,echo01.occ_file,echo01.oeb_file,echo01.zx_file,ima_file
where oea43 = b.ged01
and oea04 = occ01
and oea01 = oeb01
and oeaconf = 'Y'
and oeb04 = ima01
and imaud03 is null --冰品
and regexp_like(oea01,'SO[123789]-')
and ta_oea04 != '3'
and oea15 = :oea15
and oea02 between to_date(:oea02b,'yyyy/mm/dd') and to_date(:oea02e,'yyyy/mm/dd')
group by substr(to_char(oea02,'yyyymm'),1,6),oea43,ged02,oea15,
case when regexp_like(ged02,'大榮|大誠|日通|超峰|黑貓') then '外車' else '其它' end,oea04,occ18,occ11,--oeb04,oeb06,
oeauser,zx02,oeb01,oeb04,oeb06,oeb12
having sum(oeb12*ta_ima003/1000) < 200
union all
select substr(to_char(oea02,'yyyymm'),1,6) yyyymm,oea15,oea43,ged02,
case when regexp_like(ged02,'大榮|大誠|日通|超峰|黑貓') then '外車' else '其它' end 交運方式,
count(*) 次數,oea04,occ18,occ11,--oeb04,replace(oeb06,',',null) oeb06,
sum(oeb14*oea24) oeb14,oeb01,
oeauser,zx02,oeb04,oeb06,oeb12
from echo3a.oea_file a,ged_file b,echo01.occ_file,echo01.oeb_file,echo01.zx_file,ima_file
where oea43 = b.ged01
and oea04 = occ01
and oea01 = oeb01
and oeaconf = 'Y'
and oeb04 = ima01
and imaud03 is null --冰品
and regexp_like(oea01,'SO[123789]-')
and ta_oea04 != '3'
and oea15 = :oea15
and oea02 between to_date(:oea02b,'yyyy/mm/dd') and to_date(:oea02e,'yyyy/mm/dd')
group by substr(to_char(oea02,'yyyymm'),1,6),oea43,ged02,oea15,
case when regexp_like(ged02,'大榮|大誠|日通|超峰|黑貓') then '外車' else '其它' end,oea04,occ18,occ11,--oeb04,oeb06,
oeauser,zx02,oeb01,oeb04,oeb06,oeb12
having sum(oeb12*ta_ima003/1000) < 200
union all
select substr(to_char(oea02,'yyyymm'),1,6) yyyymm,oea15,oea43,ged02,
case when regexp_like(ged02,'大榮|大誠|日通|超峰|黑貓') then '外車' else '其它' end 交運方式,
count(*) 次數,oea04,occ18,occ11,--oeb04,replace(oeb06,',',null) oeb06,
sum(oeb14*oea24) oeb14,oeb01,
oeauser,zx02,oeb04,oeb06,oeb12
from echo06.oea_file a,ged_file b,echo01.occ_file,echo01.oeb_file,echo01.zx_file,ima_file
where oea43 = b.ged01
and oea04 = occ01
and oea01 = oeb01
and oeaconf = 'Y'
and oeb04 = ima01
and imaud03 is null --冰品
and regexp_like(oea01,'SO[123789]-')
and ta_oea04 != '3'
and oea15 = :oea15
and oea02 between to_date(:oea02b,'yyyy/mm/dd') and to_date(:oea02e,'yyyy/mm/dd')
group by substr(to_char(oea02,'yyyymm'),1,6),oea43,ged02,oea15,
case when regexp_like(ged02,'大榮|大誠|日通|超峰|黑貓') then '外車' else '其它' end,oea04,occ18,occ11,--oeb04,oeb06,
oeauser,zx02,oeb01,oeb04,oeb06,oeb12
having sum(oeb12*ta_ima003/1000) < 200
),clq_file
where oea04 = occ01
and lat is not null
group by occ18,lat,lng
";
$stid = oci_parse($dbconn, $sql);
oci_bind_by_name($stid, ":oea15", $oea15);
oci_bind_by_name($stid, ":oea02b", $oea02b);
oci_bind_by_name($stid, ":oea02e", $oea02e);
oci_execute($stid);
$rows = array();
while($row = oci_fetch_assoc($stid)) {
$rows[] = $row;
}
$locations =(json_encode($rows));
echo $locations;
?>
