<html lang="ja">
<head>
<title>地域別人口</title>
<meta http-equiv="content-type" charset="utf-8">
<meta http-equiv="Cache-Control" content="no-cache">
<style>.top { vertical-align: top; }</style>
<script src='./javascript/jquery-3.4.0.min.js'></script>
<script src='./javascript/vue.js'></script>
<script>
var noCache = new Date().getTime();
document.write('<script src="./javascript/cgjs.js?' + noCache + '"><\/script>');
</script>
<script>
var app_table, name_obj;
// 「ログアウト」ボタンのイベント
function to_login() {
location.href = "login.html";
}
// 「検索」ボタンのイベント
function show_list() {
let send_obj = {
arg: { "Prefecture": $('#Prefecture').val(),
"sel_sex": $('#sel_sex').val(),
"sel_age": $('#sel_age').val(),
"db": $('#db_name').val(),
},
include:"demo_module/get_pref_list",
execute:"get_pref_list(arg);",
timeout: 1000000,
}
let obj = { };
let ret = CGJS.cgjs_if(obj, send_obj);
if (ret != "") {
alert(ret);
return;
}
app_table.header = [ ];
app_table.records = [ ];
if (obj.CGI_CODE != 0) {
alert("Error code:" + obj.CGI_CODE + " ,message:" + obj.CGI_MESSAGE);
return;
}
else if (obj.CGI_MESSAGE != "") {
alert("message:" + obj.CGI_MESSAGE);
}
else {
// Ajaxからヘッダーの配列を取得
let header = obj.CGI_OBJ.header;
if (header && header instanceof Array) {
app_table.header = header;
}
// AjaxからTABLEの配列データを取得
let records = obj.CGI_OBJ.records;
if (records || records instanceof Array) {
app_table.records = records;
}
}
// Vueのコンポーネントに変更を知らせる
app_table.seq = app_table.seq + 1;
return;
}
</script>
</head>
<body>
<div id="name_header" style="font-size:12px;">
<table>
<tr>
<td>会社：{{ company }}</td>
<td> </td>
<td>本部：{{ division }}</td>
<td> </td>
<td>部室：{{ department }}</td>
<td> </td>
<td>名前：{{ user_mame }}</td>
</tr>
</table>
</div>
<div style="margin: 10px 0px 10px 0px;">
地域別人口 データベース
<select id="db_name" name="db_name">
<option value="SQLITE">Sqlite</option>
<option value="DB2">DB2</option>
<option value="ORACLE">Oracle</option>
<option value="PGSQL">PostgreSql</option>
<option value="SQLSVR">SqlServer</option>
</select>
</div>
<form method="POST" action="">
<div id="app">
<div id="cond">
<table border="0">
<tr>
<td class="top">都道府県</td>
<td class="top">
<select id="Prefecture" name="Prefecture" size="5" multiple>
<option value="00" selected>全国</option> <option value="01">北海道</option>
<option value="02">青森県</option> <option value="03">岩手県</option>
<option value="04">宮城県</option> <option value="05">秋田県</option>
<option value="06">山形県</option> <option value="07">福島県</option>
<option value="08">茨城県</option> <option value="09">栃木県</option>
<option value="10">群馬県</option> <option value="11">埼玉県</option>
<option value="12">千葉県</option> <option value="13">東京都</option>
<option value="14">神奈川県</option><option value="15">新潟県</option>
<option value="16">富山県</option> <option value="17">石川県</option>
<option value="18">福井県</option> <option value="19">山梨県</option>
<option value="20">長野県</option> <option value="21">岐阜県</option>
<option value="22">静岡県</option> <option value="23">愛知県</option>
<option value="24">三重県</option> <option value="25">滋賀県</option>
<option value="26">京都府</option> <option value="27">大阪府</option>
<option value="28">兵庫県</option> <option value="29">奈良県</option>
<option value="30">和歌山県</option><option value="31">鳥取県</option>
<option value="32">島根県</option> <option value="33">岡山県</option>
<option value="34">広島県</option> <option value="35">山口県</option>
<option value="36">徳島県</option> <option value="37">香川県</option>
<option value="38">愛媛県</option> <option value="39">高知県</option>
<option value="40">福岡県</option> <option value="41">佐賀県</option>
<option value="42">長崎県</option> <option value="43">熊本県</option>
<option value="44">大分県</option> <option value="45">宮崎県</option>
<option value="46">鹿児島県</option><option value="47">沖縄県</option>
</select>
</td>
<td> </td>
<td class="top">性別</td>
<td class="top">
<select id="sel_sex" name="sel_sex" size="3" multiple>
<option value="00">男女合計</option>
<option value="01" selected>男</option>
<option value="02" selected>女</option>
</select>
</td>
<td> </td>
<td class="top">年齢別</td>
<td class="top">
<select id="sel_age" name="sel_age" size="5" multiple>
<option value="0000">全年齢合計</option>
<option value="0014" selected>１５歳未満</option>
<option value="1564" selected>１５～６４歳</option>
<option value="6574" selected>６５～７４歳</option>
<option value="7599" selected>７５歳以上</option>
</select>
</td>
<td class="top"> </td>
<td class="top"><input type="button" value="検 索" onClick="show_list();"></td>
<td class="top"> </td>
<td class="top"><input type="button" value="ログアウト" onClick="to_login();"></td>
</tr>
</table>
</div>
<hr>
<div id="population">
<table border="1">
<template v-if="header">
<tr>
<th v-for="name in header">{{ name }}</th>
</tr>
</template>
<tr v-for="row in records">
<td v-for="(index, col) in row">
<template v-if="index == 0">
<a href="#" v-on:click="pref_detail(col[0])">{{ col[1] }}</a>
</template>
<template v-else>{{ col }}</template>
</td>
</tr>
</table>
</div>
</div>
</form>
<script>
app_table = new Vue({
el: '#population',
data: {
seq: 0,
header: [ ],
records:[ ]
},
methods: {
pref_detail: function(pref) {
location.href = "prefecture.html?pref=" + pref + "&db=" + $('#db_name').val();
}
}
});
name_obj = new Vue({
el: '#name_header',
data: {
seq: 0,
company: "",
division: "",
department: "",
user_mame: ""
}
});
name_obj.company = sessionStorage.getItem('company');
name_obj.division = sessionStorage.getItem('division');
name_obj.department = sessionStorage.getItem('department');
name_obj.user_mame = sessionStorage.getItem('user_mame');
name_obj.seq = name_obj.seq + 1;
let db_mame = sessionStorage.getItem('db_mame');
let op = $('#db_name').children();
for(let i = 0; i < op.length; i++) {
if (op.eq(i).val() == db_mame) {
op.eq(i).prop('selected', true);
break;
}
}
</script>
</body>
</html>
// 画面の人口テーブル配列を作成
function get_pref_array(conn, sql, h_cnt) {
let c_cd = "";
let records = [ ];
let arr = [ ];
let sm;
// sql文の検索を行い、[[PrefCd, Name, val],[PrefCd, Name, val], ...]を処理する
let tmp_arr = select(conn, sql);
// 配列の各レコードで処理を繰り返す
for (let rec of tmp_arr) {
if (rec[0] != c_cd) {
if (arr.length > 0) {
if (h_cnt > 1)
arr.push(sm);
records.push(arr);
}
c_cd = rec[0];
arr = [ [ rec[0], rec[1] ], rec[2] ];
sm = rec[2] * 1;
}
else {
arr.push(rec[2]);
sm += rec[2] * 1;
}
}
if (arr.length > 0) {
if (h_cnt > 1)
arr.push(sm);
records.push(arr);
}
return records;
}
// 配列から検索条件を作成
function make_sql_condition(codes, cond_str) {
// 条件を作成 in ('01', '02', ... ) を作成
cond = cond_str;
sepa = '';
for (let elm of codes) {
cond += sepa + " '" + elm + "'";
sepa = ',';
}
cond += ")";
return cond;
}
// 都道府県別、年齢別, 男女別の人口の一覧を検索する
function get_pref_list(arg) {
let conn = null;
let records;
let header = [ "都道府県" ];
try {
let sql_header, sql_data;
let h_cnt = 0, case_no = 0;
let pref_cond = "";
let age_cond = "";
let sex_cond = "";
// データベースに接続
conn = openDb(DB_NAME, DB_LIB, DB_PARAM);
// 都道府県の条件を作成 in ('01', '02', ... ) を作成
let code = arg.Prefecture;
if (code.length > 0 && code.indexOf("00") < 0) { // "00"は全国
pref_cond = make_sql_condition(code, "where PrefCd in (");
}
// 年齢別の条件を作成 in ('01', '02', ... ) を作成
code = arg.sel_age;
if (code.length > 0 && code.indexOf("0000") < 0) {
age_cond = make_sql_condition(code, " and Code in (");
case_no += 2;
}
// 男女別の条件を作成 in ('01', '02', ... ) を作成
code = arg.sel_sex;
if (code.length > 0 && code.indexOf("00") < 0) {
sex_cond = make_sql_condition(code, " and Code in (");
case_no += 1;
}
switch (case_no) {
case 3:
// 年齢別、男女別のsql
// 画面の表のヘッダーを検索するSQL
if (DB_NAME == "sqlserver")
sql_header =
"select age.Name + '(' + sex.Name + ')' as nm " +
`from (select Code, Name from Column_M where Type = '01' ${age_cond}) age, ` +
`(select Code, Name from Column_M where Type = '02' ${sex_cond}) sex ` +
"order by age.Code, sex.Code ";
else
sql_header =
"select age.Name || '(' || sex.Name || ')' as nm " +
`from (select Code, Name from Column_M where Type = '01' ${age_cond}) age, ` +
`(select Code, Name from Column_M where Type = '02' ${sex_cond}) sex ` +
"order by age.Code, sex.Code ";
// 画面の人口を検索するSQL
sql_data =
"select x.PrefCd, x.Name, y.val " +
"from (select prf.PrefCd, prf.Name, age.Code as a_cd, sex.Code as s_cd " +
`from (select PrefCd, Name from Prefecture_M ${pref_cond}) prf, ` +
`(select Code from Column_M where Type = '01' ${age_cond}) age, ` +
`(select Code from Column_M where Type = '02' ${sex_cond}) sex) x ` +
"left outer join " +
"(select PrefCd, AgeCd, SexCd, sum(Value) as val " +
"from Population_T group by PrefCd, AgeCd, SexCd) y " +
"on (y.PrefCd = x.PrefCd and y.AgeCd = x.a_cd and y.SexCd = x.s_cd) " +
"order by x.PrefCd, x.a_cd, x.s_cd ";
break;
case 2:
// 男女別の区別はなし、年齢別だけで集計
// 画面の表のヘッダーを検索するSQL
sql_header =
`select Name from Column_M where Type = '01' ${age_cond} order by Code`;
// 画面の人口を検索するSQL
sql_data =
"select x.PrefCd, x.Name, y.val " +
"from (select prf.PrefCd, prf.Name, age.Code as a_cd " +
`from (select PrefCd, Name from Prefecture_M ${pref_cond}) prf, ` +
`(select Code from Column_M where Type = '01' ${age_cond}) age) x ` +
"left outer join " +
"(select PrefCd, AgeCd, sum(Value) as val " +
"from Population_T group by PrefCd, AgeCd) y " +
"on (y.PrefCd = x.PrefCd and y.AgeCd = x.a_cd) " +
"order by x.PrefCd, x.a_cd ";
break;
case 1:
// 男女別だけで集計
// 画面の表のヘッダーを検索するSQL
sql_header =
`select Name from Column_M where Type = '02' ${sex_cond} order by Code`;
// 画面の人口を検索するSQL
sql_data =
"select x.PrefCd, x.Name, y.val " +
"from (select prf.PrefCd, prf.Name, sex.Code as s_cd " +
"from (select PrefCd, Name " +
`from Prefecture_M ${pref_cond}) prf, ` +
"(select Code from Column_M " +
`where Type = '02' ${sex_cond}) sex) x ` +
"left outer join " +
"(select PrefCd, SexCd, sum(Value) as val " +
"from Population_T group by PrefCd, SexCd) y " +
"on (y.PrefCd = x.PrefCd and y.SexCd = x.s_cd) " +
"order by x.PrefCd, x.s_cd ";
break;
case 0:
// 年齢別、男女の区別はなしで集計
header.push("合計");
sql_header = null;
sql_data =
"select p.PrefCd, p.Name, x.val " +
`from (select PrefCd, Name from Prefecture_M ${pref_cond}) p ` +
"left outer join " +
"(select PrefCd, sum(Value) as val " +
"from Population_T group by PrefCd) x " +
"on (x.PrefCd = p.PrefCd) " +
"order by p.PrefCd ";
}
// ヘッダーを生成
if (sql_header != null) {
// 検索結果[[Name], [Name], ...]から表の配列を作成
let tmp_arr = select(conn, sql_header);
for (let nm of tmp_arr) {
header.push(nm[0]);
h_cnt++;
}
}
if (h_cnt > 1)
header.push('合計');
// 検索結果[[PrefCd, Name, val], [PrefCd, Name, val], ...]から表の配列を作成
records = get_pref_array(conn, sql_data, h_cnt)
} catch (ex) {
let str = ex.toString();
alertToFront(ex);
}
finally {
// データベースのセッションを終了
if (conn) {
closeDb(conn);
}
}
// フロントのJavaScriptに転送する
sendToFront(header);
sendToFront(records);
}
