Semakin di depan
大まかな流れとしては、
となります。
検索条件を各項目の一時テーブルに入れて、ジョインするかしないかは、パラメータで指示してます。
[ SQL例 ]
<!-- 船スペックマスタリスト -->
<!-- 使用 -->
<select id="selVesselSpecList2" parameterType="ParamVesselSpec" resultType="VesselSpec">
select * from (
select
v.ukfld as id, v.ukfld as imo,
v.Name as vessel,
coalesce(fc_acc6m(v.Name), '') as acc6m,
coalesce(en.cnt_en, 0) as cnt_en,
coalesce(`in`.cnt_in, 0) as cnt_in,
coalesce(eo.cnt_eo, 0) as cnt_eo,
coalesce(io.cnt_io, 0) as cnt_io,
coalesce(ua.cnt_ua, 0) as cnt_ua,
v.TEU, v.`Year`, v.Speed, v.GrossT, v.DeadW, v.`call`, v.Cntry,
v.tm_add, v.tm_updt,
greatest(v.tm_add, v.tm_updt, coalesce(mt.TM_ADD, '2000-01-01 00:00:00')) as tm_lastupdt,
v.is_manadd,
coalesce(mt.LENGTH_OVERALL, 0) as `length`,
coalesce(mt.BREADTH_EXTREME, 0) as width,
coalesce(mt.DRAUGHT, 0) as draught,
coalesce(round(mt.LENGTH_OVERALL, 0), 0) as `length_int`,
coalesce(round(mt.BREADTH_EXTREME, 0), 0) as width_int,
coalesce(round(mt.DRAUGHT, 0), 0) as draught_int,
COALESCE(mt.LIQUID_OIL, 0) AS liquid_oil,
COALESCE(mt.FUEL_CONSUMPTION, '') AS fuel_consumption,
COALESCE(mt.OWNER, '') AS owner,
COALESCE(mt.MANAGER, '') AS manager,
COALESCE(mt.MANAGER_OWNER, '') AS manager_owner
from
vessel2 v
left outer join vessel_mt mt on (v.ukfld = mt.IMO)
<if test="!isTEUselAll">
inner join tmp_imo_teu tt on (
v.ukfld = tt.IMO and tt.ID_SESSION = #{idSession} and tt.NM_GRID = 'specs'
)
</if>
<if test="!isYearselAll">
inner join tmp_imo_year yt on (
v.ukfld = yt.IMO and yt.ID_SESSION = #{idSession} and yt.NM_GRID = 'specs'
)
</if>
<if test="!isSpeedselAll">
inner join tmp_imo_speed st on (
v.ukfld = st.IMO and st.ID_SESSION = #{idSession} and st.NM_GRID = 'specs'
)
</if>
<if test="!isLengthselAll">
inner join tmp_imo_length lg on (
mt.IMO = lg.IMO and lg.ID_SESSION = #{idSession} and lg.NM_GRID = 'specs'
)
</if>
<if test="!isWidthselAll">
inner join tmp_imo_width wd on (
mt.IMO = wd.IMO and wd.ID_SESSION = #{idSession} and wd.NM_GRID = 'specs'
)
</if>
<if test="!isDraughtselAll">
inner join tmp_imo_draught dr on (
mt.IMO = dr.IMO and dr.ID_SESSION = #{idSession} and dr.NM_GRID = 'specs'
)
</if>
left outer join (
select imo, count(*) as cnt_en from sch_export_source group by imo
) en
on (v.ukfld = en.imo)
left outer join (
select imo, count(*) as cnt_in from sch_import_source group by imo
) `in`
on (v.ukfld = `in`.imo)
left outer join (
select imo, count(*) as cnt_eo from sch_export_source_old group by imo
) eo
on (v.ukfld = eo.imo)
left outer join (
select imo, count(*) as cnt_io from sch_import_source_old group by imo
) io
on (v.ukfld = io.imo)
left outer join (
select u.vessel, count(u.id) as cnt_ua from sch_useradd u group by vessel
) ua on (v.Name = ua.vessel)
where
1 = 1 and v.Name like concat('%', #{vsllk}, '%') and
ukfld like concat('%', #{imolk}, '%')
<if test="isModOnly">
and is_manadd = 1
</if>
) a
order by
${sidx} ${sord}
limit ${skip}, ${pgrec}
</select>
[ 使っているテーブル ]
船マスタ
CREATE TABLE `vessel2` (
`Name` VARCHAR(36) NOT NULL DEFAULT '' COMMENT '船名',
`call` VARCHAR(9) NOT NULL DEFAULT '' COMMENT '無線局呼出符号',
`Year` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '建造年',
`Speed` FLOAT NOT NULL DEFAULT '0' COMMENT '速力',
`TEU` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '積載TEU',
`GrossT` INT(11) NOT NULL DEFAULT '0' COMMENT 'グロス重量',
`DeadW` INT(11) NOT NULL DEFAULT '0' COMMENT '重量重量',
`Cntry` VARCHAR(2) NOT NULL DEFAULT '' COMMENT '船籍国コード',
`ukfld` INT(11) NOT NULL DEFAULT '0' COMMENT 'IMOコード',
`tm_add` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
`tm_updt` DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00' COMMENT '最終更新時刻',
`is_manadd` TINYINT(4) NOT NULL DEFAULT '0' COMMENT 'ユーザー更新フラグ',
`ChgName` VARCHAR(36) NOT NULL DEFAULT '0' COMMENT '参照結合用変換船名',
PRIMARY KEY (`ukfld`),
INDEX `vessel2_Name` (`Name`),
INDEX `vessel2_ChgName` (`ChgName`)
)
COMMENT='船マスタ (利用)'
COLLATE='utf8_general_ci'
ENGINE=MyISAM
一時テーブル (一部)
・ID_SESSION はTomcatのセッションIDでユーザー別
・ほっておくと肥大するので、夜中に全レコード削除
CREATE TABLE `tmp_imo_teu` (
`ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'シーケンス番号',
`ID_SESSION` VARCHAR(70) NOT NULL DEFAULT '' COMMENT 'WEBサーバーセッションID',
`NM_GRID` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'jqGridグリッドID',
`IMO` INT(11) NOT NULL DEFAULT '0' COMMENT 'IMOコード',
`TM_ADD` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
PRIMARY KEY (`ID`),
INDEX `tmp_imo_ID_SESSION` (`ID_SESSION`),
INDEX `tmp_imo_NM_GRID` (`NM_GRID`),
INDEX `tmp_imo_IMO` (`IMO`)
)
COMMENT='船スペック検索用IMO一時テーブル'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=5627
;
CREATE TABLE `tmp_imo_year` (
`ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'シーケンス番号',
`ID_SESSION` VARCHAR(70) NOT NULL DEFAULT '' COMMENT 'WEBサーバーセッションID',
`NM_GRID` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'jqGridグリッドID',
`IMO` INT(11) NOT NULL DEFAULT '0' COMMENT 'IMOコード',
`TM_ADD` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
PRIMARY KEY (`ID`),
INDEX `tmp_imo_ID_SESSION` (`ID_SESSION`),
INDEX `tmp_imo_NM_GRID` (`NM_GRID`),
INDEX `tmp_imo_IMO` (`IMO`)
)
COMMENT='船スペック検索用IMO一時テーブル'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
[ 一時テーブル削除追加用SQL ]
・テーブル名を ${tmptbl} のパラメータで渡すことで、まとめてます。
<!-- マルチセレクト用一時テーブル更新 -->
<!-- 削除 -->
<delete id="delIMOTmpAll" parameterType="MyBatisParent">
delete from ${tmptbl} where ID_SESSION = #{idSession} and NM_GRID = #{nmGrid}
</delete>
<!-- 追加 -->
<insert id="insAIMOTmp" parameterType="MyBatisParent">
insert into ${tmptbl}
(ID_SESSION, NM_GRID, IMO)
select #{idSession}, #{nmGrid},
ukfld
from ${vslmsttbl}
where ${vslColumn} between #{insMinVal} and #{insMaxVal}
</insert>
<insert id="insAIMOTmpmt" parameterType="MyBatisParent">
insert into ${tmptbl}
(ID_SESSION, NM_GRID, IMO)
select #{idSession}, #{nmGrid}, IMO
from ${vslmsttbl}
where ${vslColumn} between #{insMinVal} and #{insMaxVal}
</insert>
[ js, cssのセット ]
<!-- MultipleSelect -->
<script type="text/javascript" src="Script/jquery.multiple.select.js"></script>
<link rel='stylesheet' href='CSS/multiple-select.css' />
[ コードサンプル ]
// マルチセレクト全選択数用 //
/* この変数は、外のfunctionで使うのでload前 :/
var teuselAllCnt;
var yearselAllCnt;
var speedselAllCnt;
var lengthselAllCnt;
var widthselAllCnt;
var draughtselAllCnt;
// マルチプルセレクト //
$('#teusel, #yearsel, #speedsel, #lengthsel, #widthsel, #draughtsel').multipleSelect({
//$('#filesel, #sheetsel, #suplsel, #psnsel').multipleSelect({
allSelected: 'All',
//height: '500px'
});
// 全選択して項目数を調べる //
$('#teusel').multipleSelect('checkAll');
$('#yearsel').multipleSelect('checkAll');
$('#speedsel').multipleSelect('checkAll');
$('#lengthsel').multipleSelect('checkAll');
$('#widthsel').multipleSelect('checkAll');
$('#draughtsel').multipleSelect('checkAll');
teuselAllCnt = $('#teusel').multipleSelect('getSelects', 'value').length;
yearselAllCnt = $('#yearsel').multipleSelect('getSelects', 'value').length;
speedselAllCnt = $('#speedsel').multipleSelect('getSelects', 'value').length;
lengthselAllCnt = $('#lengthsel').multipleSelect('getSelects', 'value').length;
widthselAllCnt = $('#widthsel').multipleSelect('getSelects', 'value').length;
draughtselAllCnt = $('#draughtsel').multipleSelect('getSelects', 'value').length;
/****** ロード時は常に全選択状態なので、全選択フラグを全部オンにする */
// **** 船スペックグリッド **** //
$("#specs").jqGrid({
url: "GetVesselSpecList",
//editurl: "paramCheck",
editurl: "UpdateAVesselSpec",
mtype: 'post',
datatype: "json",
serializeGridData: function (postData) {
//postData['mindt'] = '2017-12-01';
//postData['idsql'] = 'selDailyBatchLogList';
postData['vsllk'] = vsllklast;
postData['imolk'] = imolklast;
postData['isTEUSelAll'] = '1';
postData['isYearSelAll'] = '1';
postData['isSpeedSelAll'] = '1';
postData['isLengthSelAll'] = '1';
postData['isWidthSelAll'] = '1';
postData['isDraughtSelAll'] = '1';
postData['teuselext'] = "";
postData['yearselext'] = "";
postData['speedselext'] = "";
postData['lengthselext'] = "";
postData['widthselext'] = "";
postData['draughtselext'] = "";
postData['isModOnly'] = '0';
//postData['rows'] = rownum;
return postData;
},
/*************************************************************/
// == フィルターボタンを押した時 == //
$('#fltbtn').on('click', function () {
var teuselcnt = $('#teusel').multipleSelect('getSelects', 'value').length;
var yearselcnt = $('#yearsel').multipleSelect('getSelects', 'value').length;
var speedselcnt = $('#speedsel').multipleSelect('getSelects', 'value').length;
var lengthselcnt = $('#lengthsel').multipleSelect('getSelects', 'value').length;
var widthselcnt = $('#widthsel').multipleSelect('getSelects', 'value').length;
var draughtselcnt = $('#draughtsel').multipleSelect('getSelects', 'value').length;
// グリッドの更新 //
$('#specs').jqGrid('setGridParam', {
page: 1,
serializeGridData: function (postData) {
// 単純なので、JSONにしないで配列文字列にして送信 //
/* 最後に "" を必ずつける */
postData['teuselext'] = $('#teusel').multipleSelect("getSelects", "value") + "";
postData['yearselext'] = $('#yearsel').multipleSelect("getSelects", "value") + "";
postData['speedselext'] = $('#speedsel').multipleSelect("getSelects", "value") + "";
postData['lengthselext'] = $('#lengthsel').multipleSelect("getSelects", "value") + "";
postData['widthselext'] = $('#widthsel').multipleSelect("getSelects", "value") + "";
postData['draughtselext'] = $('#draughtsel').multipleSelect("getSelects", "value") + "";
// 全選択 or Not を送信用 //
postData['isTEUSelAll'] = teuselcnt == teuselAllCnt ? '1' : '0';
postData['isYearSelAll'] = yearselcnt == yearselAllCnt ? '1' : '0';
postData['isSpeedSelAll'] = speedselcnt == speedselAllCnt ? '1' : '0';
postData['isLengthSelAll'] = lengthselcnt == lengthselAllCnt ? '1' : '0';
postData['isWidthSelAll'] = widthselcnt == widthselAllCnt ? '1' : '0';
postData['isDraughtSelAll'] = draughtselcnt == draughtselAllCnt ? '1' : '0';
postData['vsllk'] = $('#nameinp').val();
postData['imolk'] = $('#imoinp').val();
return postData;
}
}).trigger('reloadGrid');
// ローカルストレージに保存 //
// これは使ってないが、今後の為 //
if (localStorage) {
var savemap = {};
savemap['teuselext'] = $('#teusel').multipleSelect("getSelects", "value");
savemap['yearselext'] = $('#yearsel').multipleSelect("getSelects", "value");
savemap['speedselext'] = $('#speedsel').multipleSelect("getSelects", "value");
savemap['lengthselext'] = $('#lengthsel').multipleSelect("getSelects", "value");
savemap['widthselext'] = $('#widthsel').multipleSelect("getSelects", "value");
savemap['draughtselext'] = $('#draughtsel').multipleSelect("getSelects", "value");
savemap['vsllk'] = $('#nameinp').val();
savemap['imolk'] = $('#imoinp').val();
localStorage.setItem('vesselsch_specs_filter', JSON.stringify(savemap));
}
});
MultipleSelect を使うと、エクセルのフィルターのような、複数アイテムの指定による検索が可能となり、検索機能が大幅に向上します。
[ 設置画面例 ]
[ アイテム用SQL例 ]
グループ化した各範囲値を結果レコードにする。concatで区切った左がselectタグのvalue、右がテキスト
<!-- 船スペックフィルター用 -->
<select id="selTEU1000CmbList" resultType="String">
/* TEU 1000 */
select
concat(
concat(truncate(v.TEU, -3), '-',truncate(v.TEU, -3) + 999), ',',
concat(cast(truncate(v.TEU, -3) as char), ' - ',
cast(truncate(v.TEU, -3) + 999 as char), ' X ', count(Name))
)
from
vessel2 v
group by
truncate(v.TEU, -3)
</select>
<select id="selYear5CmbList" resultType="String">
/* Year 5 */
select
concat(
concat(min(v.Year), '-', max(v.year)), ',',
cast(concat(min(v.Year), ' - ', max(v.year)) as char), ' X ', count(Name)
)
from
vessel2 v
group by
floor(v.Year / 5);
</select>
[ SQL実行結果例 ]
0-999,0 – 999 X 545
1000-1999,1000 – 1999 X 411
2000-2999,2000 – 2999 X 165
3000-3999,3000 – 3999 X 67
4000-4999,4000 – 4999 X 287
5000-5999,5000 – 5999 X 154
6000-6999,6000 – 6999 X 118
7000-7999,7000 – 7999 X 28
8000-8999,8000 – 8999 X 229
9000-9999,9000 – 9999 X 135
10000-10999,10000 – 10999 X 83
11000-11999,11000 – 11999 X 38
12000-12999,12000 – 12999 X 14
13000-13999,13000 – 13999 X 140
14000-14999,14000 – 14999 X 78
15000-15999,15000 – 15999 X 19
16000-16999,16000 – 16999 X 9
17000-17999,17000 – 17999 X 6
18000-18999,18000 – 18999 X 26
19000-19999,19000 – 19999 X 29
20000-20999,20000 – 20999 X 31
21000-21999,21000 – 21999 X 8
[ selectタグ例 (multiple未適用) ]
