I Language Homepage/lesson3

｛｝ I言語 Lesson3 家計簿のリストボックスによる入力改善の学習

●プログラムを実際に使って見ると、改善点が色々分かって来ます。今回は内容が毎月ほぼ同じ物なので、毎回入力するのではなく、事前に内容を作っておいて、リストボックスで選ぶ方法に入力作業を改善します。
★I言語は簡単を基本にしているので複雑なGUI（ボタン等のグラフィカルユーザーインターフェース）は使いませんが、キーボードからの入力を減らす意味で、既に値が決まっている物はリストボックスを使って入力しなくても、そこから選ぶ方法を使う事で入力作業を改善する機能を持っています。

(1.1)リストボックスに表示する為の内容一覧を、家計簿内容テーブルとして新たに作るのでテーブル構造を決めます。
■テーブル名＝ZZZYAA_家計簿内容実表
■列名1＝ZZZYAA_内容 NCHAR(20)(主キー、データ辞書の型1は"J"、最小文字数は１）
■列名2＝ZZZYAA_分類 NCHAR(6)（データ辞書の型1は"J"、最小文字数は１、値は「食費」か「その他」）
☆家計簿実表では食費としていましたが、将来別の物も分類出来るように、分類とします。分類は内容に付属する項目なので家計簿内容実表に置きます。取りあえず当初は「食費」と「その他」とします。’

(2.1)ZZZZ010225の「テストデータ辞書　更新」で内容は既に作られているので、分類を作成します。
☆分類は値が「食費」か「その他」で今後もそれほど増える事が無いと想定されるので、データ辞書で入力出来る値を設定します。
☆データ辞書で入力出来る値を設定する方法は大きくは３種類があります、チェックでどの方法を使うかを設定します。
☆①1つ目は値を直接コンマ区切りで指定する方法です。CHECK1は値を、CHECK2は値とその説明を交互に書き、リストボックス上に表示します、更にCHECK1は（最小値,最大値）で範囲指定も出来ますが、範囲指定を加えた場合はリストボックスは表示されません、
☆②２つ目はSELECTのSQL文で対応する方法です。SELECT??(VER18.1以前はLISTBOX??)はSELECTのSQLを使って値(1)、又は値とその説明(2)を表示します、尚、最後のDはDESCの略で値(1)か説明(2)を降順に表示します。
★★注意★★VER19.1よりLISTBOX改めSELECTとします。理由はSELECTのSQL文を使うので、より近い意味とする為です。（LISTBOXも互換保持で動きます）
☆③３つ目は入力後プログラムで判定する方法です。SECTION?で入力後に動くプログラムを書きます。

(2.2)今回は値の一覧表示ですので"CHECK1"を入力しチェック61に"食費,その他"と入れて完成です。

(3.1)ZZZZ010226の「(テストテーブル更新」で「ZZZYAA_家計簿内容実表」を作成します。主キー項目数は内容の１個となります。

(3.2)内容と分類の列を作成します。

(4.1)ZZZZ010221の「テスト全プログラム更新」でZZZZ303032に「家計簿内容更新」のプログラムを「家計簿内容実表」から作ります。

(4.2)出来たプログラムです。

(4.3)テスト実行すると分類の入力位置でリストボックスが表示されます。

●内容は既に家計簿実表に有るので、それを使って家計簿内容実表にデータを作成します。この作業は１回行えばよいのでプログラムを作らないで直接SQLを作って実行します。
(5.1)ZZZZ010212の「テストＳＱＬ発行」に行き、利用者を「INP」（更新のINPUTの意味）、SQLの種類を「=SQL」（INSERT,UPDATEは「=SQL」を使います、SELECTは「SQL=」で、DELETEはINPには権限が無いので「DBO」[管理者用]の「=SQL」を使います）

(5.2)ここでSQL文を作って最後の行で"0"で選択するとSQLを実行します。
☆ここでは許可や部分をプログラムで事前にメニューに指定する方法では無いのでSQLで直接指定する必要があります。「?_MM?」は「？_MP_ZZZY?AA」と許可も部分も指定し、「~+」は使えないので「ZZZYAA」を直接記入します。尚、?_Z_INSERT_NAME?はINSERTでI言語が使う列名で?_Z_INSERT_VALE?はその値を意味します。

(5.3)SQLを実行した結果5件のデータが作成されました。

(5.4)ZZZZ010221でZZZZ303032を実行してみると、データが作られている事が確認出来ます。

●家計簿内容実表が出来たので家計簿実表の更新プログラムでのリストボックス表示の対応をします。
(6.1)ZZZZ010225の「テストデータ辞書　更新」で内容に対するチェック機能を設定します。修正は"3"(修正）で行います。
☆データ辞書でのSELECTによるチェックは値のみか値と説明ですが、今回は内容を値とし、分類を説明として対応します。
☆２個の列を使うのでチェックに"SELECT21"(VER18.1以前は"LISTBOX21")（列を２つで最初の値で昇順に表示する）とします、許可では使用するテーブルの許可を指定するので"ZZZY"を選びます。。

(6.2)ここで使用するテーブルの「?_MP_ZZZY?AA_家計簿内容実表」を選びます。

(6.3)ここで値とする列名の「ZZZYAA_ZZZYAA_内容」を選びます。

(6.4)ここで説明として「ZZZYAA_分類」を選びます。

(6.5)これで入力完了なので"↓"で最終項目に行き'Enter'を押して修正完了です。

(7.1)ZZZZ010221でZZZZ303033に「新家計簿更新」を「家計簿実表」を使って作ります。

(7.2)食費は代わりに分類を使用するのでコメント化します。
★★注意★★VER14からVER18まではツールに問題が有って正しく動きません、VER19以降で正しく動きます。
☆最初のZZZZ303030のプログラムと比較すると、２行目に家計簿内容実表のテーブルの宣言が増えています。これは、データ辞書にテーブルが有っても使う場合はプログラムでも宣言しないと使えないようにする事で、どのプログラムがどのテーブルを使っているかを容易に解析可能にする為です。
☆1200行目にDATA={K,&X2}と有りますがこのデータで&X2のテーブルを使う宣言をしています。尚、&x2の宣言が有ってこの項目に対する説明（今回は分類が該当）も定義されている場合は検索のSQLもLEFT OUTER JOINでテーブルが結合されて説明分も表示されます。
☆1300行目にDATA={*OC}X2.?&X2.DATA&~+_分類?が新たに出来ました、*は更新対象でないを意味し、OはOUTPUTで入力対象でも無くCは並び替えの対象で無い事を意味します、データ辞書で説明も設定されているとこのように表示対象となります。

(7.3)Tでテスト実行してみると分類も含めて一覧が表示されています。

☆この時点で更に改善をしてみます。それは、その月に対し内容は１件のみ可とします（入力ミス防止で同じ内容が複数いる場合は枝番等を付けてユニークにする）、一覧に全てが表示されては困ります、そこでその月に既に登録されている内容はリストボックスに表示しないように改善します。
★★注意★★VER18以前では簡単に実現出来ない事が分かりました、そこで、VER19で_AND_SPACE変数を新設し、データ辞書で?_AND_SPACE?を条件内で単独で記入した場合、_AND_SPACEが空白は空白に(新機能)、中身が有る場合はAND(?_AND_SPACE?）となるようにし、「_LEFT数」の内容もこれにならって設定出来るようにしました。
(8.1)リストボックス表示時点で既に作成済みデータを表示しないようにしますが、検索のSELECT内では条件設定しないのでデータ辞書のCHECK64に?_AND_SPACE?を設定し,プログラムでコントロール出来るようにします。

(8.2)最初のSET=の下で検索のSQLでは条件設定不要なので=SET{_AND_SPACE=};で空白にします。内容で条件設定するので、年月日を入力した時点で年月を使って条件を_AND_SPACEに設定します。
◆1010 =SET{_AND_SPACE='NOT EXISTS(SELECT 0 FROM ?_TABLE? X1'
★=で始まる行はセクションと呼び命令を書く事が出来、SET=やDATA=の下に有るとそのデータの入力直後に命令が実行されます。
★命令は「命令{内容}...」が基本の構造です。今までのプログラミング言語とは文法が異質ですが、命令と内容を書く位置を完全に分離する事でプログラムのミスを起こしにくくしました。
★年月に対し内容が存在しないものを抜き出したいので「NOT　EXISTS(）」で一致データが無い物を条件設定します。「?_TABLE?」はPROGRAM=で指定したテーブルの「家計簿実表」を意味します。
◆1020 = +' WHERE X1.?_NE&X2.~+_内容? AND Z_CANCEL='' '''
★「+」で文字列を結合できます。「X1.?_NE&X2.~+_内容?」の"？"の次が"_"で途中に"&"が有る物は"&"の右辺のデータ名を、左辺の条件で編集します、N(NAME)が右側が名前（左側も名前で別名を取ります）で、E(EQUAL)が"="を意味し間に入れます、結果は「X1.~+_内容=X2.~+_内容」と成ります。「AND Z_CANCEL='' ''」で削除されていないデータのみ対象とします。尚、引用符の中で引用符を使う場合は２個連続で１個の引用符とします。
◆1030 = +' AND LEFT(X1.~+_年月日,6)=''?_DATA[1;6]?'')'};
★「LEFT(X1.~+_年月日,6)」SQLのLEFT関数を使って年月（６文字）にします。「''?_DATA[1;6]?''」の_DATAは現在処理中のデータ名「.~+_年月日」の内容で、先頭の６文字（[1;6]で1文字目から６文字)と成ります。

(8.3)2016年04月で未作成の２件だけがリストボックスに表示されているので完成です。

(9.1)テーブルを分割した場合の対応。
★テーブルを分割した場合は必ず次の点を考慮する必要があります、２個のテーブルは「内容」で関係付けされているので家計簿実表の作成する内容は、家計簿内容実表に存在している必要があります、今回は家計簿内容実表を使って家計簿実表に内容を設定しているのでこの問題は無いですが、家計簿実表に存在する内容を家計簿内容実表側で削除出来てしまう問題があります。この問題を解決する手段としてリレショナルデータベースでは外部キー制約を使って実現出来るようになっていますが、I言語では残念ながらこの方法は使えません。I言語では削除はDELETEでは無くUPDATEでZ_CANCELに日付けを入れる論理削除をするようになっているので、外部キーを設定しても役に立ちません。論理削除している理由は、削除した場合に削除した何時、何処で（どのマシンのどのジョブ）で、誰が削除したかを記録に残し問題が有った場合の原因究明が速やかに出来るようにしている為です。なのでプログラムに削除判定を組み込み対応をします。

(9.2)ZZZZ303032の「家計簿内容更新」のプログラムに削除時の判定機能を付加します。
◆410 START
★削除の判定はSTART以下で行います。プログラムの何処に書いても良いですが位置的にはSET=の後のDATA=の前に書く事をお勧めします。
◆420 =IF{_START!=1}EXIT{};
★削除の１の値は_STARTに設定されるので、_STARTが1以外はEXIT{};でSTARTセクションを終了します。
◆430 =SQL_SET{W0.COUNT}{SELECT COUNT(*) FROM ?_MM?_家計簿実表
★SQLで一件のデータを受け取る場合はSQL_SET{}{}を使い最初の{}内にI言語で受け取るデータ名、今回は件数を受け取りたいのでW0.COUNTと記入します。
★W(WORK)で始まるデータ名は特別でSET=やDATA=等で宣言しなくてもいきなり使えます、２桁目が数値の物は数値分の小数点以下が有る数値となるのでW0で小数点以下の無い整数が記憶出来るデータとなります。因みに、WCがCHAR用（半角文字）、WEがDATETIME用、WFがFLOAT用、以外（WNを推奨）がNCHAR用(全角文字用）と成ります。
◆440 = WHERE ?_VRE&.~+_内容? AND Z_CANCEL=' '};
★「?_VRE&.~+_内容?」はV(VALUE)が値で右辺がデータの内容と成ります,R(REMOVE)が別名を削除（通常はドットで始まっているか又はドットが１つも無いデータ名はPROGRAM=で指定した別名が付きます）します、E(EQUAL)が"="を意味します、結果は「ZZZYAA_内容=N'衣類購入'」(内容が"衣類購入"の物を削除しようとした場合)と成ります。「AND Z_CANCEL=' '」で削除されていないデータのみを対象とします。
◆450 =IF{W0.COUNT>0}ERROR{家計簿実表に内容が存在します};
★W0.COUNTが０件以上であれば内容が存在するのでERROR{}を使ってエラー表示をします。

(9.3)削除しようとすると２行目に「家計簿実表に内容が存在します」と赤字でエラーが表示されて削除できません。

(10.1)最後に「月別エンゲル係数表示」改め「家計簿月別分類別％表示」をZZZZ303034に作ります。

(10.2)分類は家計簿内容実表に有るので家計簿実表と２個のテーブルが必要です、更新プログラムではデータ辞書を使って結合していましたが、今回はプログラムで直接２個のテーブルを結合してみます。
★２個のテーブルを結合するので"TABLE　2"とします。

(10.3)最初は基本となる家計簿実表を選択します。

(10.4)JOIN2で結合方法を選びます、通常は"LEFT"が基本ですので"LEFT"とします

(10.5)IX2でインデックスを使って結合する場合のインデックス番号を指定します、今回は主キーで結合するので0（主キー）を選びます。

(10.6)もう一方のテーブルの「家計簿内容実表」を選びます。

(10.7)これで２個のテーブルを結合して表示するプログラムが出来ましたが、今回は分類別に％を求めたいので最初の２行の結合を指定している部分のみ残し後はすべて直します。

(10.8)今回は年月単位の合計金額が必要でGROUPを使ったSQLでは簡単には実現出来ません。そこで年月、分類単位の合計と年月単位の合計をサブクエリーで計算しそれを使って表示するようにします。
◆ 100 PROGRAM=,&X1,?_MM?_家計簿実表
◆ 200 LEFT=&X2,?_MM?_家計簿内容実表
★LEFT OUTER JOINのSQL文をしたで使うので設定を残します。
◆ 300 DATA={G}年月{6}
◆ 400 SELECT=?_SUBSTR(X1.~+_年月日;1;6)?
◆ 500 DATA={G}X2.?&X2.DATA&~+_分類?
★設定内容をSQL文で使うのでこのように設定してあります。
◆ 600 DATA={}％{3,,+,Z}
◆ 700 SQL=SELECT X1.~+_年月,~+_分類,100*X1.~+_金額/X2.~+_金額 FROM
◆ 800 REPEAT=2
★サブクエリを２個使いますが、分類を付けるか付けないかの違いなので２回繰り返しで対応します。
◆ 900 2+SQL=INNER JOIN
★2+は繰り返しの２回目のみ採用されます。
◆1000 SQL=(?_SELECT1? AS ~+_年月
★_SELECT1は最初のDATA=のSELECT=を使って「SELECT ?_SUBSTR(X1.~+_年月日;1;6)?」と成ります。
◆1100 1+SQL= ?_SELECT2?
★_SELECT2は２番目のDATA=の列名(SELECT=が無いので)を使って「,X2.~+_分類」と成ります。
◆1200 SQL= ,SUM(X1.~+_金額)AS ~+_金額 ?_FROM1? ?_FROM2? ?_GROUP1?
★_FORM1,_FROM2はPROGRAM=とLEFT=で設定された物です。
★_GROUP1は1つ目のDATA=のSELECT=を使って「GROUP BY ?_SUBSTR(X1.~+_年月日;1;6)?」となります。
◆1300 1+SQL= ?_GROUP2?
★_GROUP2は2つ目のDATA=の列名(SELECT=が無いので)を使って「,X2.~+_分類」となります。
★1+は繰り返しの１回目のみ採用されます。
◆1400 SQL= )AS X##
★##は繰り返しの回数に置き換えられます。
◆1500 REPEAT=
★繰り返しの終わりです。
◆1600 SQL=ON X1.?_NE&X2.~+_年月? WHERE X2.~+_金額!=0
◆1700 SQL=ORDER BY 1 DESC,CASE ~+_分類 WHEN
◆1800 SQL='その他' THEN 1 ELSE 0 END,~+_分類
★分類がその他は年月の最後にします。

(10.9)Tでテスト実行した結果です。（年月単位で合計が100では無いので、その他で補正するのが一般的な方法ですが、少し面倒ですので今回か割愛します）
★複雑なプログラムでもSQLとI言語をうまく使えば、少ない行数で実現出来る事が結構有ります。