--/d
/* FRAC, IRAC, HRAC 統合テンポラリテーブル */
drop table if exists rac_mg;
create temp table rac_mg as select 'F:'||mid as mid, sayoten, sayokiko, risk from frac_mg;
insert into rac_mg select 'I:'||mid, sayoten, sayokiko, null from irac_mg;
insert into rac_mg select 'H:'||mid, sayoten, null, null from hrac_mg;
drop table if exists rac_sg;
create temp table rac_sg as select 'F:'||mid as mid, 'F:'||sid as sid, keito, fgroup from frac_sg;
insert into rac_sg select 'I:'||mid, 'I:'||sid as sid, keito, null from irac_sg;
insert into rac_sg select 'H:'||mid, 'H:'||sid as sid, keito, null from hrac_sg;
drop table if exists rac_ai;
create temp table rac_ai as select 'F:'||sid as sid, ippanmei from frac_ai;
insert into rac_ai select 'I:'||sid as sid, ippanmei from irac_ai;
insert into rac_ai select 'H:'||sid as sid, ippanmei from hrac_ai;
/* FRAC, IRAC, HRAC 統合テンポラリビュー */
drop view if exists rac_g;
create temp view rac_g as select mid, sid, keito, sayoten, sayokiko, fgroup, risk from rac_sg left join rac_mg using(mid);
drop view if exists rac;
create temp view rac as select ippanmei, keito, mid, sayoten, sayokiko, fgroup, risk from rac_ai left join rac_g using(sid);

select * from seibun left join rac using(ippanmei) where bango in (select bango from m_kihon where meisho like '%ベトファイター%');

--/d
/* FRAC, IRAC, HRAC 統合テーブル */
drop table if exists spec.rac_mg;
create table spec.rac_mg as select 'F:'||mid as mid, sayoten, sayokiko, risk from frac_mg;
insert into spec.rac_mg select 'I:'||mid, sayoten, sayokiko, null from irac_mg;
insert into spec.rac_mg select 'H:'||mid, sayoten, null, null from hrac_mg;
drop table if exists spec.rac_sg;
create table spec.rac_sg as select 'F:'||mid as mid, 'F:'||sid as sid, keito, fgroup from frac_sg;
insert into spec.rac_sg select 'I:'||mid, 'I:'||sid as sid, keito, null from irac_sg;
insert into spec.rac_sg select 'H:'||mid, 'H:'||sid as sid, keito, null from hrac_sg;
drop table if exists spec.rac_ai;
create table spec.rac_ai as select 'F:'||sid as sid, ippanmei from frac_ai;
insert into spec.rac_ai select 'I:'||sid as sid, ippanmei from irac_ai;
insert into spec.rac_ai select 'H:'||sid as sid, ippanmei from hrac_ai;
/* FRAC, IRAC, HRAC 統合ビュー */
drop view if exists spec.rac_g;
create view spec.rac_g as select mid, sid, keito, sayoten, sayokiko, fgroup, risk from rac_sg left join rac_mg using(mid);
drop view if exists spec.rac;
create view spec.rac as select ippanmei, keito, mid, sayoten, sayokiko, fgroup, risk from rac_ai left join rac_g using(sid);

drop view if exists rac;
create temp view rac as select ippanmei, keito, concat(', ', mid) as mid, concat(', ', sayoten) as sayoten, concat(', ', sayokiko) as sayokiko, fgroup, risk from rac_ai left join rac_g using(sid) group by ippanmei;

drop view if exists rac;
create temp view rac as select ippanmei, concat(', ', keito) as keito, concat(', ', mid) as mid, concat(', ', sayoten) as sayoten, concat(', ', sayokiko) as sayokiko, fgroup, risk from rac_ai left join rac_g using(sid) group by ippanmei;

--/d
/* パーマネント統合テーブル＆ビュー作成 */
--旧バージョン個別ビュー削除
drop view if exists spec.frac_g;
drop view if exists spec.frac;
drop view if exists spec.irac_g;
drop view if exists spec.irac;
--ACFinderテンポラリテーブル＆ビュー削除 
drop table if exists temp.rac_mg;
drop table if exists temp.rac_sg;
drop table if exists temp.rac_ai;
drop view if exists temp.rac_g;
drop view if exists temp.rac;
--新バージョンテーブル＆ビュー作成 
create table if not exists spec.rac_mg (cid integer, mid varchar primary key, sayoten varchar, sayokiko varchar, risk varchar);
delete from spec.rac_mg where mid like 'F:%';
insert into spec.rac_mg select 1, 'F:'||mid, sayoten, sayokiko, risk from frac_mg;
delete from spec.rac_mg where mid like 'I:%';
insert into spec.rac_mg select 2, 'I:'||mid, sayoten, sayokiko, null from irac_mg;
delete from spec.rac_mg where mid like 'H:%';
insert into spec.rac_mg select 3, 'H:'||mid, sayoten, null, null from hrac_mg;
create table if not exists spec.rac_sg (mid varchar, sid varchar primary key, keito varchar, fgroup varchar);
delete from spec.rac_sg where sid like 'F:%';
insert into spec.rac_sg select 'F:'||mid as mid, 'F:'||sid as sid, keito, fgroup from frac_sg;
delete from spec.rac_sg where sid like 'I:%';
insert into spec.rac_sg select 'I:'||mid as mid, 'I:'||sid as sid, keito, null from irac_sg;
delete from spec.rac_sg where sid like 'H:%';
insert into spec.rac_sg select 'H:'||mid as mid, 'H:'||sid as sid, keito, null from hrac_sg;
create table if not exists spec.rac_ai (sid varchar, ippanmei varchar);
delete from spec.rac_ai where sid like 'F:%';
insert into spec.rac_ai select 'F:'||sid as sid, ippanmei from frac_ai;
delete from spec.rac_ai where sid like 'I:%';
insert into spec.rac_ai select 'I:'||sid as sid, ippanmei from irac_ai;
delete from spec.rac_ai where sid like 'H:%';
insert into spec.rac_ai select 'H:'||sid as sid, ippanmei from hrac_ai;
drop index if exists spec.idxrac;
create index spec.idxRac on rac_ai (ippanmei);
create view if not exists spec.rac_g as select cid, mid, sid, keito, sayoten, sayokiko, fgroup, risk from rac_sg left join rac_mg using(mid);
create view if not exists spec.rac as select ippanmei, concat(', ', keito) as keito, concat(', ', mid) as mid, concat(', ', sayoten) as sayoten, concat(', ', sayokiko) as sayokiko, fgroup, risk from rac_ai left join rac_g using(sid) group by ippanmei;
create view if not exists spec.raccode as select cid, mid, ippanmei from rac_ai left join rac_g using(sid);

--/d
DROP TABLE IF EXISTS ttTekiyoSaku;
CREATE TEMP TABLE ttTekiyoSaku AS SELECT
  bango,shurui,meisho,tsusho,idsaku,sakumotsu,idbyochu,byochu,mokuteki,jiki,baisu,ekiryo,hoho,basho,jikan,ondo,dojo,chitai,tekiyaku,kongo,kaisu,
  seibun1,keito1,kaisu1,(select mid from raccode where ippanmei = seibun1 and cid = substr(idbyochu, 1, 1)) as mid1,
  seibun2,keito2,kaisu2,(select mid from raccode where ippanmei = seibun2 and cid = substr(idbyochu, 1, 1)) as mid2,
  seibun3,keito3,kaisu3,(select mid from raccode where ippanmei = seibun3 and cid = substr(idbyochu, 1, 1)) as mid3,
  seibun4,keito4,kaisu4,(select mid from raccode where ippanmei = seibun4 and cid = substr(idbyochu, 1, 1)) as mid4,
  seibun5,keito5,kaisu5,(select mid from raccode where ippanmei = seibun5 and cid = substr(idbyochu, 1, 1)) as mid5,
  yoto,koka,zaikei,ryakusho
FROM tekiyo WHERE idSaku IN (SELECT idSaku FROM m_sakumotsu WHERE idSaku REGEXP ...);

--/d
DROP TABLE IF EXISTS ttTekiyoByochu;
CREATE TEMP TABLE ttTekiyoByochu AS SELECT
  bango,shurui,meisho,tsusho,idsaku,sakumotsu,idbyochu,byochu,mokuteki,jiki,baisu,ekiryo,hoho,basho,jikan,ondo,dojo,chitai,tekiyaku,kongo,kaisu,
  seibun1,keito1,kaisu1,(select mid from raccode where ippanmei = seibun1 and cid = substr(idbyochu, 1, 1)) as mid1,
  seibun2,keito2,kaisu2,(select mid from raccode where ippanmei = seibun2 and cid = substr(idbyochu, 1, 1)) as mid2,
  seibun3,keito3,kaisu3,(select mid from raccode where ippanmei = seibun3 and cid = substr(idbyochu, 1, 1)) as mid3,
  seibun4,keito4,kaisu4,(select mid from raccode where ippanmei = seibun4 and cid = substr(idbyochu, 1, 1)) as mid4,
  seibun5,keito5,kaisu5,(select mid from raccode where ippanmei = seibun5 and cid = substr(idbyochu, 1, 1)) as mid5,
  yoto,koka,zaikei,ryakusho
FROM tekiyo WHERE bango IN (SELECT bango FROM m_tekiyo WHERE byochu IN (SELECT byochu FROM tByochu) OR mokuteki IN (SELECT byochu FROM tByochu));

VersionLabel.Caption := FileVerInfo1.ProductVersion;

unit VerInfo;

interface

uses
  Windows, SysUtils, Classes, StrUtils, Forms;

type
  EFileVerInfoError = class(Exception);
  TFileVerInfo = class(TComponent)
  private { Private 宣言 }
    FValid: boolean;
    FPBuf: pointer;
    FSize: DWORD;
    FSub: string;
    FPath: string;
    function GetComments: string;
    function GetCompanyName: string;
    function GetFileDescription: string;
    function GetFileVersion: string;
    function GetInternalName: string;
    function GetLegalCopyright: string;
    function GetLegalTradeMarks: string;
    function GetProductName: string;
    function GetProductVersion: string;
    function GetPrivateBuild: string;
    function GetSpecialBuild: string;
    procedure SetPath(TargetPath: string);
  protected { protected 宣言 }
  public { Public 宣言 }
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    function GetInfo(Name: string): string;
    property Valid: boolean read FValid;
    property Comments: string read GetComments;
    property CompanyName: string read GetCompanyName;
    property FileDescription: string read GetFileDescription;
    property FileVersion: string read GetFileVersion;
    property InternalName: string read GetInternalName;
    property LegalCopyright: string read GetLegalCopyright;
    property LegalTradeMarks: string read GetLegalTradeMarks;
    property ProductName: string read GetProductName;
    property ProductVersion: string read GetProductVersion;
    property PrivateBuild: string read GetPrivateBuild;
    property SpecialBuild: string read GetSpecialBuild;
  published { Published 宣言 }
    property Path: string read FPath write SetPath;
  end;

procedure Register;
function CopyrightSignToChar(Dst: string): string;
function RegisteredSignToChar(Dst: string): string;
function CopyrightCharToSign(Dst: string): string;
function RegisteredCharToSign(Dst: string): string;

implementation

procedure Register;
begin
  RegisterComponents('Hidemi', [TFileVerInfo]);
end;

function CopyrightSignToChar;
begin
  Result := AnsiReplaceText(Dst, #$A9, '(C)');
end;

function RegisteredSignToChar;
begin
  Result := AnsiReplaceText(Dst, #$AE, '(R)');
end;

function CopyrightCharToSign;
begin
  Result := AnsiReplaceText(Dst, '(C)', #$A9);
end;

function RegisteredCharToSign;
begin
  Result := AnsiReplaceText(Dst, '(R)', #$AE);
end;

function TFileVerInfo.GetInfo;
var
  len: DWORD;
  pinfo: pointer;
begin
  Result := '';
  if not FValid then Exit;
  VerQueryValue(FPBuf, PChar(FSub + Name), pinfo, len);
  if len > 0 then Result := PChar(pinfo);
end;

function TFileVerInfo.GetComments;
begin
  Result := GetInfo('Comments');
end;

function TFileVerInfo.GetCompanyName;
begin
  Result := GetInfo('CompanyName');
end;

function TFileVerInfo.GetFileDescription;
begin
  Result := GetInfo('FileDescription');
end;

function TFileVerInfo.GetFileVersion;
begin
  Result := GetInfo('FileVersion');
end;

function TFileVerInfo.GetInternalName;
begin
  Result := GetInfo('InternalName');
end;

function TFileVerInfo.GetLegalCopyright;
begin
  Result := GetInfo('LegalCopyright');
end;

function TFileVerInfo.GetLegalTradeMarks;
begin
  Result := GetInfo('LegalTradeMarks');
end;

function TFileVerInfo.GetProductName;
begin
  Result := GetInfo('ProductName');
end;

function TFileVerInfo.GetProductVersion;
begin
  Result := GetInfo('ProductVersion');
end;

function TFileVerInfo.GetPrivateBuild;
begin
  Result := GetInfo('PrivateBuild');
end;

function TFileVerInfo.GetSpecialBuild;
begin
  Result := GetInfo('SpecialBuild');
end;

procedure TFileVerInfo.SetPath;
type
  TDWord = packed record Lo, Hi: word; end;
var
  pinfo: pointer;
  ret, len: DWORD;
begin
  FPath := TargetPath;
  if csDesigning in ComponentState then Exit;
  FValid := false;
  if FSize <> 0 then FreeMem(FPBuf, FSize);
  if TargetPath = '' then FPath := Application.ExeName;
  FSize := GetFileVersionInfoSize(PChar(FPath), ret);
  if FSize = 0 then begin
    raise EFileVerInfoError.Create('EFileVerInfoError: Cannot Get FileVerInfo Size');
    Exit;
  end;
  try
    GetMem(FPBuf, FSize);
  except
    FSize := 0;
    raise EFileVerInfoError.Create('EFileVerInfoError: Insufficient Memory');
    Exit;
  end;
  if not GetFileVersionInfo(PChar(FPath), 0, FSize, FPBuf) then begin
    FreeMem(FPBuf, FSize);
    FSize := 0;
    raise EFileVerInfoError.Create('EFileVerInfoError: Cannot Get FileVerInfo');
    Exit;
  end;
  if not VerQueryValue(FPBuf, '\VarFileInfo\Translation', pinfo, len) or (len = 0) then begin
    FreeMem(FPBuf, FSize);
    FSize := 0;
    raise EFileVerInfoError.Create('EFileVerInfoError: Cannot Get FileVerInfo');
    Exit;
  end;
  FSub := '\StringFileInfo\' + IntToHex(TDWord(pinfo^).Lo, 4) +
         IntToHex(TDWord(pinfo^).Hi, 4) + '\';
  FValid := true;
end;

constructor TFileVerInfo.Create;
begin
  inherited Create(AOwner);
  SetPath(FPath);
end;

destructor TFileVerInfo.Destroy;
begin
  if FSize <> 0 then FreeMem(FPBuf, FSize);
  inherited;
end;

end.

SELECT * FROM spec.sqlite_master WHERE type in ('table','view') ;

CREATE TEMP VIEW kihon AS SELECT
  bango, shurui, meisho, tsusho, ryakusho, ippanmei, a.seibun AS seibun, nodo, kongo, zaikei, yoto,
  CONCAT('・',
    (select koka from koka where koka.kokaid = b.kokaid & 3),
    (select koka from koka where koka.kokaid = b.kokaid & 4),
    (select koka from koka where koka.kokaid = b.kokaid & 8),
    (select koka from koka where koka.kokaid = b.kokaid & 16),
    (select koka from koka where koka.kokaid = b.kokaid & 32),
    (select koka from koka where koka.kokaid = b.kokaid & 64),
    (select koka from koka where koka.kokaid = b.kokaid & 128),
    (select koka from koka where koka.kokaid = b.kokaid & 256),
    (select koka from koka where koka.kokaid = b.kokaid & 512),
    (select koka from koka where koka.kokaid = b.kokaid & 1024),
    (select koka from koka where koka.kokaid = b.kokaid & 2048),
    (select koka from koka where koka.kokaid = b.kokaid & 4096),
    (select koka from koka where koka.kokaid = b.kokaid & 8192),
    (select koka from koka where koka.kokaid = b.kokaid & 16384),
    (select koka from koka where koka.kokaid = b.kokaid & 32768)
  ) AS koka,
  iso, b.keito as keito, c.keito as RAC系統, mid, sayoten, sayokiko, fgroup, risk
FROM seibun AS a
LEFT JOIN m_kihon USING(bango)
LEFT JOIN iso USING(ippanmei)
LEFT JOIN bunrui AS b ON a.ippanmei = b.seibun
LEFT JOIN rac AS c USING(ippanmei);

SELECT DISTINCT tsusho FROM kihon
WHERE concat('$',tsusho,shurui,keito,koka,iso,RAC系統,mid,sayoten, sayokiko) MATCH '%...%'
ORDER BY tsusho

SELECT DISTINCT bango,meisho FROM kihon
WHERE concat(' ',tsusho,shurui,keito,koka,iso,RAC系統,mid,sayoten, sayokiko) MATCH '%...%'
ORDER BY bango

WHERE concat('$',tsusho,shurui,keito,koka,iso,RAC系統,mid,sayoten, sayokiko) MATCH '%...%'
WHERE concat(' ',tsusho,shurui,keito,koka,iso,RAC系統,mid,sayoten, sayokiko) MATCH '%...%'

WHERE concat(x'09',tsusho,shurui,keito,koka,iso,RAC系統,mid,sayoten, sayokiko) MATCH '%...%'

create view spec.rac as select ippanmei, concat2(', ', keito) as keito, concat2(', ', mid) as mid, concat2(', ', sayoten) as sayoten, concat2(', ', ifnullstr(sayokiko, '-')) as sayokiko, fgroup, risk from rac_ai left join rac_g using(sid) group by ippanmei;

create view spec.rac as select ippanmei, concat2(', ', keito) as keito, concat2(', ', mid) as mid, concat2(', ', sayoten) as sayoten, concat2(', ', ifnullstr(sayokiko, '-')) as sayokiko, concat('', fgroup) as fgroup, concat('', risk) as risk from rac_ai left join rac_g using(sid) group by ippanmei;

SELECT DISTINCT
  ippanmei, seibun, ifnullstr(iso, ippanmei) as ISO物質名, s.keito as 系統,koka, JPP系統名, kagakumei, nodo, nodochu,
  MAX(gyodoku) AS gyodoku, seizaieikyo, seibuneikyo, dokusei, jogai, jyogai1, jyogai2, biko,
  mid, rac.keito as RAC系統, sayoten, sayokiko, fgroup, risk
FROM tvSeibun AS s LEFT JOIN rac using(ippanmei) LEFT JOIN iso using(ippanmei)
WHERE bango = nnnn GROUP BY ippanmei

/* 指定作物に登録されている農薬の登録番号(作物が指定されている時のみ実行) */
DROP TABLE IF EXISTS t_bango;
CREATE TEMP TABLE t_bango AS SELECT DISTINCT bango, sakumotsu FROM m_tekiyo
WHERE sakumotsu IN (SELECT sakumotsu FROM m_sakumotsu WHERE idSaku REGEXP '^(作物IDリスト)' AND sakumotsu NOT REGEXP '\((.*、)?(作物名リスト).*?(、.*)?を除く')
ORDER BY bango;

/* 該当薬剤リストボックス */
DROP TABLE IF EXISTS t_yaku;
CREATE TEMP TABLE t_yaku AS SELECT DISTINCT bango,meisho,tsusho FROM kihon
WHERE concat(x'09',tsusho,shurui,keito,koka,iso,RAC系統,mid,sayoten, sayokiko,fgroup) MATCH '%キーワード%'
AND bango IN (SELECT DISTINCT bango FROM t_bango)--作物が指定されている場合この行追加
;
--通称タブ
SELECT DISTINCT tsusho FROM t_yaku;
--登録番号タブ
SELECT DISTINCT bango,meisho FROM t_yaku;

/* 類似薬剤リストボックス */
--通称選択時の成分/系統/作用点一覧
SELECT DISTINCT ifnullstr(seibun,ippanmei) FROM kihon WHERE tsusho = '選択通称';
SELECT DISTINCT keito FROM kihon WHERE tsusho = '選択通称';
SELECT DISTINCT sayoten FROM kihon WHERE tsusho = '選択通称';
/*
--番号選択時の成分/系統/作用点一覧
SELECT DISTINCT ifnullstr(seibun,ippanmei) FROM kihon WHERE bango = 選択番号;
SELECT DISTINCT keito FROM kihon WHERE bango = 選択番号;
SELECT DISTINCT sayoten FROM kihon WHERE bango = 選択番号;
*/
--成分タブ
SELECT DISTINCT tsusho FROM kihon WHERE ifnullstr(seibun,ippanmei) = '選択成分'
AND bango IN (SELECT DISTINCT bango FROM t_bango)--作物が指定されている場合この行追加
ORDER BY tsusho;
--系統タブ
SELECT DISTINCT tsusho FROM kihon WHERE keito = '選択系統'
AND bango IN (SELECT DISTINCT bango FROM t_bango)--作物が指定されている場合この行追加
ORDER BY tsusho;
--作用点タブ
SELECT DISTINCT tsusho FROM kihon WHERE sayoten = '選択作用点'
AND bango IN (SELECT DISTINCT bango FROM t_bango)--作物が指定されている場合この行追加
ORDER BY tsusho;

/* 概要ペイン */
--剤の概要
SELECT bango,shurui,yoto,koka,zaikei,ryakusho,dokusei,torokubi,kousin,a.kigen AS kigen
FROM m_kihon as a LEFT JOIN seizai USING(bango) WHERE bango = 選択登録番号;
--有効成分表(変更無し)
SELECT DISTINCT ippanmei,seibun,ifnullstr(iso, seibun) AS ISO物質名,s.keito AS 系統,koka, JPP系統名, kagakumei,nodo,nodochu, MAX(gyodoku) AS gyodoku,seizaieikyo,seibuneikyo,dokusei,jogai,jyogai1,jyogai2,biko,mid,rac.keito as RAC系統,sayoten,sayokiko,fgroup,risk
FROM tvSeibun AS s LEFT JOIN rac USING(ippanmei) LEFT JOIN iso USING(ippanmei)
WHERE bango = 選択登録番号 GROUP BY ippanmei;
--水産動植物への影響(変更無し)
SELECT chuijiko,koshin FROM suisan WHERE bango = 選択登録番号;

/* 適用ペイン */
SELECT sakumotsu,byochu,mokuteki,baisu,ekiryo,jiki,kaisu,hoho,basho,jikan,ondo,dojo,chitai,tekiyaku,kaisu1,kaisu2,kaisu3,kaisu4,kaisu5
FROM m_tekiyo WHERE bango = 選択登録番号
AND sakumotsu IN (SELECT DISTINCT sakumotsu FROM t_bango)--作物が指定されている場合のみ追加
;

DROP TABLE IF EXISTS t_bango;
CREATE TEMP TABLE t_bango (bango integer primal key, sakumotsu varchar);
--作物が指定されている場合は下文実行
INSERT INTO t_bango SELECT DISTINCT bango, sakumotsu FROM m_tekiyo
WHERE sakumotsu IN (SELECT sakumotsu FROM m_sakumotsu WHERE idSaku REGEXP '^(作物IDリスト)' AND sakumotsu NOT REGEXP '\((.*、)?(作物名リスト).*?(、.*)?を除く')
ORDER BY bango;

/* 該当薬剤リストボックス */
DROP TABLE IF EXISTS t_yaku;
CREATE TEMP TABLE t_yaku AS SELECT DISTINCT bango,meisho,tsusho FROM kihon
WHERE concat(x'09',tsusho,shurui,keito,koka,iso,RAC系統,mid,sayoten, sayokiko,fgroup) MATCH '%キーワード%'
AND ((SELECT COUNT(*) FROM t_bango)=0 OR bango IN (SELECT DISTINCT bango FROM t_bango));
--通称タブ
SELECT DISTINCT tsusho FROM t_yaku;
--登録番号タブ
SELECT DISTINCT bango,meisho FROM t_yaku;

/* 類似薬剤リストボックス */
--通称選択時の成分/系統/作用点ドロップダウンリスト
SELECT DISTINCT ifnullstr(seibun,ippanmei) FROM kihon WHERE tsusho = '選択通称';
SELECT DISTINCT keito FROM kihon WHERE tsusho = '選択通称';
SELECT DISTINCT sayoten FROM kihon WHERE tsusho = '選択通称';
/*
--番号選択時の成分/系統/作用点ドロップダウンリスト
SELECT DISTINCT ifnullstr(seibun,ippanmei) FROM kihon WHERE bango = 選択番号;
SELECT DISTINCT keito FROM kihon WHERE bango = 選択番号;
SELECT DISTINCT sayoten FROM kihon WHERE bango = 選択番号;
*/
--成分タブ
SELECT DISTINCT tsusho FROM kihon WHERE ifnullstr(seibun,ippanmei) = '選択成分'
AND ((SELECT COUNT(*) FROM t_bango)=0 OR bango IN (SELECT DISTINCT bango FROM t_bango))
ORDER BY tsusho;
--系統タブ
SELECT DISTINCT tsusho FROM kihon WHERE keito = '選択系統'
AND ((SELECT COUNT(*) FROM t_bango)=0 OR bango IN (SELECT DISTINCT bango FROM t_bango))
ORDER BY tsusho;
--作用点タブ
SELECT DISTINCT tsusho FROM kihon WHERE sayoten = '選択作用点'
AND ((SELECT COUNT(*) FROM t_bango)=0 OR bango IN (SELECT DISTINCT bango FROM t_bango))
ORDER BY tsusho;

/* 概要ペイン */
--剤の概要
SELECT bango,shurui,yoto,koka,zaikei,ryakusho,dokusei,torokubi,kousin,a.kigen AS kigen
FROM m_kihon as a LEFT JOIN seizai USING(bango) WHERE bango = 選択登録番号;
--有効成分表(変更無し)
SELECT DISTINCT ippanmei,seibun,ifnullstr(iso, seibun) AS ISO物質名,s.keito AS 系統,koka, JPP系統名, kagakumei,nodo,nodochu, MAX(gyodoku) AS gyodoku,seizaieikyo,seibuneikyo,dokusei,jogai,jyogai1,jyogai2,biko,mid,rac.keito as RAC系統,sayoten,sayokiko,fgroup,risk
FROM tvSeibun AS s LEFT JOIN rac USING(ippanmei) LEFT JOIN iso USING(ippanmei)
WHERE bango = 選択登録番号 GROUP BY ippanmei;
--水産動植物への影響(変更無し)
SELECT chuijiko,koshin FROM suisan WHERE bango = 選択登録番号;

/* 適用ペイン */
SELECT sakumotsu,byochu,mokuteki,baisu,ekiryo,jiki,kaisu,hoho,basho,jikan,ondo,dojo,chitai,tekiyaku,kaisu1,kaisu2,kaisu3,kaisu4,kaisu5
FROM m_tekiyo WHERE bango = 選択登録番号
AND ((SELECT COUNT(*) FROM t_bango)=0 OR bango IN (SELECT DISTINCT bango FROM t_bango));

BEGIN TRANSACTION;
DROP TABLE IF EXISTS t_bango;
CREATE TEMP TABLE t_bango (bango integer,sakumotsu varchar);
--作物が指定されている場合は下文実行
INSERT INTO t_bango SELECT DISTINCT bango,sakumotsu FROM m_tekiyo
WHERE sakumotsu IN (SELECT sakumotsu FROM m_sakumotsu WHERE idSaku REGEXP '^(作物IDリスト)' AND sakumotsu NOT REGEXP '\((.*、)?(作物名リスト).*?(、.*)?を除く')
ORDER BY bango;
COMMIT;