MySQL操作程序三十(name/type/maker输入的修改)
返回
- 检索画面的生成,mscs_engine.htm
- 检索结果写入中间变量=>完成
- 机械名/型式/maker的排序按钮=>完成
- TYPE的修改功能合并过来,TYPE修改,机械名/maker只能选择
原有mscshowtype1.pl改名mscshowtype1_old.pl,mscshowone.pl复制一个到mscshowtype1.pl改造
msc262.htm复写一个为msctype1.htm,TYPE修改功能=>完成
- 机械名/maker选择功能,mscengine_nm.htm,mscengine_nm.pl=>完成
- 新型式追加,机械名/maker自动写入1,mscengine_add.pl,mscengine_add.htm=>完成
- 新机械名输入功能,mscshowadd.pl=>mscsgname.pl,msc284.htm,修改功能也移过来==>完成
mscshowname1.pl,mscname1.htm,管理画面
mscshowname2.pl,mscname2.htm,修改一个name画面
- maker输入功能,mscshowadd.pl=>mscsmaker.pl,msc294.htm,修改功能也移过来==>完成
mscshowmaker1.pl,mscmaker1.htm,管理画面
mscshowmaker2.pl,mscmaker2.htm,修改一个name画面
- enq1画面的主机删除有问题?==>忘记了enq1_id的传送==>完成
- enq1的选择功能,mscenq1.pl,pat=u_engine==>完成
- write_excel.pl写出主机名时的检查
- ENQ画面的处理,mscenq1.htm
- 主机[新规追加]取消=>完成
- 机械名/maker的选择按钮取消=>完成
- mscdatabase.htm的3个按钮删除
main_type1管理=>完成
main_name1管理=>完成
main_maker1管理=>完成
- 统一同时检索,name/type/maker
- 三者完全相同时,不追加;只要有一个不同,即追加
- 没有type时,type和name相同
- ENQ画面的name/maker的选择功能移动(涉及到mscenq1.pl?)
- 先把main_type1的gname_id和maker_id要改成INT(现为TEXT)
- 关联HTML文件
mscenq1.htm
msc262.htm
mscengine_add.htm=>在该界面上选择name1/maker1或追加name1/maker1
msc284.htm(main_name1)
msc294.htm(main_maker1)
mscengine_nm.htm
- 关联PERL程序
- mscs_engine.pl=>name1/type1/maker1都找
没有输入时,返回
读main_name1和main_maker1并形成联想行列==>完成,见下面
- mscenq1.pl
pat=u_engine有问题?
- mscengine_add.pl
- mscshowadd.pl
- 关联TABLE,main_type1/main_name1/main_maker1
main_type1的gname_id和maker_id是否要改成INT(现为TEXT)
给main_name1的id=1赋值,update main_name1 set name = "1" where id = 1;
<![CDATA[
mscdatabase.htm删除
<LI>
<form action="" method="post">
<input type=text name=word1 value="">
<input type="submit" value="Equipment Types(main_type1)とパーツデータ修改"><p>
<input type="hidden" name="tmpl" value="msc261">
<input type="hidden" name="table" value="main_type1">
<input type="hidden" name="mode" value="SEARCH">
<input type="hidden" name="rm" value="modeshowtype1">
</form>
<LI><form action="" method="post">
<input type="submit" value="Main Ship Equipments管理(main_name1)"><p>
<input type="hidden" name="tmpl" value="msc281">
<input type="hidden" name="table" value="main_name1">
<input type="hidden" name="items" value="id,name,memo">
<input type="hidden" name="rm" value="modeshowall">
</form>
<LI><form action="" method="post">
<input type="submit" value="Main Marine Manufactureers管理(main_maker1)"><p>
<input type="hidden" name="tmpl" value="msc291">
<input type="hidden" name="table" value="main_maker1">
<input type="hidden" name="items" value="id,name,memo">
<input type="hidden" name="rm" value="modeshowall">
</form>
mscengine_add.htm
<input type="hidden" name="series" value="XXXSERIES">
<input type="hidden" name="DWG" value="XXXDWG">
<input type="hidden" name="table" value="main_type1">
<input type="hidden" name="table1" value="main_type1">
<input type="hidden" name="items" value="name,series,memo">
<input type="hidden" name="tmpl" value="mscengine_add2">
<input type="hidden" name="item1" value="type1id">
<input type="hidden" name="pat" value="insert_type1">
mscs_engine.htm
取消
item==><TMPL_VAR NAME="item"><br>
<input type="hidden" name="item" value=<TMPL_VAR NAME="item">>
mscenq1.pl,把改子程序移到mscs_engine.htm上执行
#---------追加一个type
} elsif ( $t{pat} eq 'insert_type1' ) {
$t{table} = $t{q}->param("table");
$t{table1} = $t{q}->param("table1");
$t{id2} = $t{q}->param("id2");
$t{item1} = $t{q}->param("item1");
$items = $t{q}->param("items");
@arr = split(/,/,$items);
@arr1 = ();
for $n ( 0 .. $#arr ) {
$t{name} = $arr[$n];
$t{one} = $t{q}->param($t{name});
push(@arr1,$t{one});
}
# 入力したname
$t{name1} = $t{q}->param($arr[0]);
# すべてのnameを取り出す
$aref = $self->dbh->selectcol_arrayref("SELECT name FROM $t{table}");
@{ $$a_ref{list} } = @$aref;
$$a_ref{name1} = $t{name1};
($a_ref) = check1($a_ref);
if ( $$a_ref{NG} == 1 ) {
$t{tmpl} = $t{q}->param("tmpl");
$t{html} = $t{tmpl} . '.htm';
$t{template} = $self->load_tmpl("$t{html}") || die "error loading tmpl";
$t{template}->param(enq1_id => $t{enq1_id});
$t{template}->param(id => $t{id2});
for $n ( 0 .. $#arr ) {
$t{name} = $arr[$n];
$t{value} = $arr1[$n];
$t{template}->param($t{name} => $t{value});
}
return $t{template}->output;
}
# パーツテーブルを作成する
$t{ptable} = sprintf("%06d",$t{id2});
$t{ptable} = 'a' . $t{ptable};
$t{sql} = 'DROP TABLE IF EXISTS ' . $t{ptable} . ';';
$self->dbh->do($t{sql});
$t{sql} = 'CREATE TABLE ' . $t{ptable};
$t{sql} .= ' (';
$t{sql} .= 'id INT AUTO_INCREMENT,';
$t{sql} .= 'name TEXT,';
$t{sql} .= 'code TEXT,';
$t{sql} .= 'dwg_id INT,';
$t{sql} .= 'Nuid INT,';
$t{sql} .= 'weight INT,';
$t{sql} .= 'price1 TEXT,';
$t{sql} .= 'price2 TEXT,';
$t{sql} .= 'memo TEXT,';
$t{sql} .= 'PRIMARY KEY (id));';
$t{DO} = $self->dbh->do($t{sql});
# $t{DO} = 0;
if ( !($t{DO}) ) { # Parts的TABLE不成功的话,返回
$t{template} = $self->load_tmpl("mscengine_addx.htm") || die "error loading mscengine_addx.htm";
$t{template}->param(enq1_id => $t{enq1_id});
$t{template}->param(id => $t{id2});
for $n ( 0 .. $#arr ) {
$t{name} = $arr[$n];
$t{value} = $arr1[$n];
$t{template}->param($t{name} => $t{value});
}
return $t{template}->output;
}
# 指定TABLE的一个id和所有item的追加(写入指定的TABLE)
$t{sql} = "INSERT INTO $t{table} (name,series,DWG,memo) ";
$t{sql} .= 'VALUES(';
$t{sql} .= '"' . $arr1[0] . '",';
if ( $arr1[1] ) {
$t{ses1} = 'XXXSERIES=' . $arr1[1];
} else {
$t{ses1} = 'XXXSERIES';
}
$t{sql} .= '"' . $t{ses1} . '","XXXDWG",';
$t{sql} .= '"' . $arr1[2] . '")';
$t{DO} = $self->dbh->do($t{sql});
# 更新enq1中的指定项目
# 更新enq1的type1id,partsid和QTY
# 调出enq1查看
($t{type1id},$t{partsid},$t{QTY},$t{seriesid}) = $self->dbh->selectrow_array("SELECT type1id,partsid,QTY,seriesid FROM enq1 WHERE id = $t{enq1_id}");
if ( $t{type1id} eq 'B' ) {
$t{type1id} = $t{id2};
$t{partsid} = "C";
$t{QTY} = "C";
} else {
$t{type1id} .= '==' . $t{id2};
$t{partsid} .= '==' . "C";
$t{QTY} .= '==' . "C";
}
if ( $t{seriesid} eq "C" ) {
if ( $arr1[1] ) {
@{ $t{type1ids} } = split(/==/,$t{type1id});
@{ $t{sess} } = ();
for $n ( 1 .. $#{ $t{type1ids} } ) {
push(@{ $t{sess} },"1");
}
push(@{ $t{sess} },"2");
$t{seriesid} = join("==",@{ $t{sess} });
}
} else {
if ( $arr1[1] ) {
$t{seriesid} .= '==' . "2";
} else {
$t{seriesid} .= '==' . "1";
}
}
$t{sql} = 'UPDATE enq1 set type1id = "';
$t{sql} .= $t{type1id} . '" where id = ';
$t{sql} .= $t{enq1_id};
$t{DO} = $self->dbh->do($t{sql});
$t{sql} = 'UPDATE enq1 set partsid = "';
$t{sql} .= $t{partsid} . '" where id = ';
$t{sql} .= $t{enq1_id};
$t{DO} = $self->dbh->do($t{sql});
$t{sql} = 'UPDATE enq1 set QTY = "';
$t{sql} .= $t{QTY} . '" where id = ';
$t{sql} .= $t{enq1_id};
$t{DO} = $self->dbh->do($t{sql});
$t{sql} = 'UPDATE enq1 set seriesid = "';
$t{sql} .= $t{seriesid} . '" where id = ';
$t{sql} .= $t{enq1_id};
$t{DO} = $self->dbh->do($t{sql});
# 更新series号
mscenq1.htm,取消以下部分
<form action="" method="post">
<input type="submit" value="新規追加"><p>
<input type="hidden" name="tmpl" value="mscengine_add">
<input type="hidden" name="table" value="main_type1">
<input type="hidden" name="id" value="<TMPL_VAR NAME="enq1_id">">
<input type="hidden" name="rm" value="modeengine_add">
</form>
]]>
<![CDATA[
#---------更新主机项目
新
旧
} elsif ( $t{pat} eq 'u_engine' ) {
$t{id1} = $t{q}->param("id2");
($t{old1},$t{new1p},$t{new1q}) = $self->dbh->selectrow_array("SELECT type1id,partsid,QTY FROM enq1 WHERE id = $t{enq1_id}");
if ( $t{old1} eq 'B' ) {
$t{new1} = $t{id1};
$t{new1p} = 'C';
$t{new1q} = 'C';
} else {
@{ $t{olds} } = split(/==/,$t{old1});
$t{NG} = 0;
for $n ( 0 .. $#{ $t{olds} } ) {
$t{id1_test} = $t{olds}[$n];
if ( $t{id1_test} == $t{id1} ) {
$t{NG} = 1;
}
}
if ( $t{NG} == 1 ) {
$t{template} = $self->load_tmpl("mscs_engine1.htm") || die "error loading tmpl";
$t{template}->param(id1 => $t{id1});
$t{template}->param(enq1_id => $t{enq1_id});
return $t{template}->output;
}
$t{new1} = $t{old1} . '==' . $t{id1};
# $t{new1p} .= '==1'; #取消这儿的加1
# $t{new1q} .= '==1';
$t{new1p} .= '==C';
$t{new1q} .= '==C';
}
if ( $t{id1} != 0 ) {
$t{sql} = 'UPDATE enq1 set type1id = "';
$t{sql} .= $t{new1} . '" where id = ';
$t{sql} .= $t{enq1_id};
$t{DO} = $self->dbh->do($t{sql});
$t{sql} = 'UPDATE enq1 set partsid = "';
$t{sql} .= $t{new1p} . '" where id = ';
$t{sql} .= $t{enq1_id};
$t{DO} = $self->dbh->do($t{sql});
$t{sql} = 'UPDATE enq1 set QTY = "';
$t{sql} .= $t{new1q} . '" where id = ';
$t{sql} .= $t{enq1_id};
$t{DO} = $self->dbh->do($t{sql});
}
]]>
<![CDATA[
mscenq1.htm
新
<th width=10%>
<form action="" method="post">
<input type="submit" value="新規追加"><p>
<input type="hidden" name="tmpl" value="mscengine_add">
<input type="hidden" name="table" value="main_type1">
<input type="hidden" name="id" value="<TMPL_VAR NAME="enq1_id">">
<input type="hidden" name="rm" value="modeengine_add">
</form>
<form action="" method="post">
<input type=text name=word1 value="">
<input type="submit" value="検索"><p>
<input type="hidden" name="table" value="main_type1">
<input type="hidden" name="enq1_id" value="<TMPL_VAR NAME="enq1_id">">
<input type="hidden" name="rm" value="modes_engine">
</form>
</th>
<th width=20%>機械名</th>
<th width=20%>型式</th>
<th width=20%>メーカー</th>
<th width=10%>削除釦</th>
旧
<th width=10%>順番</th>
<th width=20%>機械名
<form action="" method="post">
<input type="submit" value="新規追加"><p>
<input type="hidden" name="tmpl" value="msc284">
<input type="hidden" name="table" value="main_name1">
<input type="hidden" name="enq1_id" value="<TMPL_VAR NAME="enq1_id">">
<input type="hidden" name="rm" value="modeshowadd">
</form>
</th>
<th width=20%>型式
<form action="" method="post">
<input type="submit" value="新規追加"><p>
<input type="hidden" name="tmpl" value="mscengine_add">
<input type="hidden" name="table" value="main_type1">
<input type="hidden" name="id" value="<TMPL_VAR NAME="enq1_id">">
<input type="hidden" name="rm" value="modeengine_add">
</form>
<form action="" method="post">
<input type=text name=word1 value="">
<input type="submit" value="検索"><p>
<input type="hidden" name="table" value="main_type1">
<input type="hidden" name="enq1_id" value="<TMPL_VAR NAME="enq1_id">">
<input type="hidden" name="rm" value="modes_engine">
</form>
</th>
<th width=20%>メーカー
<form action="" method="post">
<input type="submit" value="新規追加"><p>
<input type="hidden" name="tmpl" value="msc294">
<input type="hidden" name="table" value="main_maker1">
<input type="hidden" name="enq1_id" value="<TMPL_VAR NAME="enq1_id">">
<input type="hidden" name="rm" value="modeshowadd">
</form>
</th>
]]>
<![CDATA[
mscs_engine.pl
if ( $t{word} ) {
# 读name1
$t{sth} = $self->dbh->prepare("SELECT id,name FROM main_name1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{name1}{$rec[0]} = $rec[1];
}
$t{sth}->finish;
# 读maker1
$t{sth} = $self->dbh->prepare("SELECT id,name FROM main_maker1 where name is not NULL");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{maker1}{$rec[0]} = $rec[1];
}
$t{sth}->finish;
# 检索main_type1
$t{sth} = $self->dbh->prepare("SELECT id,name,gname_id,maker_id FROM $t{table}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{gname} = $t{name1}{$rec[2]};
$t{maker} = $t{maker1}{$rec[3]};
$t{line} = $rec[1] . ' ' . $t{gname} . ' ' . $t{maker};
$t{line} = lc($t{line});
if ( $t{line} =~ /$t{word}/ ) {
my $row_ref = (); # 非常重要!
$$row_ref{id} = $rec[0];
$$row_ref{Line} = $t{gname} . '</td><td>' . $rec[1] . '</td><td>' . $t{maker};
$$row_ref{enq1_id} = $t{enq1_id};
$$row_ref{item} = $t{item};
push(@loop, $row_ref);
$t{NO}++;
}
}
$t{sth}->finish;
}
$t{template} = $self->load_tmpl("mscs_engine.htm") || die "error loading tmpl";
if ( $t{NO} == 0 ) {
$t{template}->param(explain => "没有找到,请返回。");
} else {
$t{template}->param(explain => "请选一个");
$t{template}->param(THIS_LOOP => \@loop);
}
]]>
<![CDATA[
mysql> show columns from main_type1;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| series | text | YES | | NULL | |
| gname_id | text | YES | | NULL | |
| maker_id | text | YES | | NULL | |
| GR | text | YES | | NULL | |
| DWG | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
8 rows in set (0.13 sec)
mysql> show columns from main_name1;
+-------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(100) | YES | | NULL | |
| memo | text | YES | | NULL | |
| cname | char(100) | YES | | NULL | |
| cmemo | text | YES | | NULL | |
| jname | char(100) | YES | | NULL | |
| jmemo | text | YES | | NULL | |
+-------+-----------+------+-----+---------+----------------+
mysql> show columns from main_maker1;
+-------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(100) | YES | | NULL | |
| memo | text | YES | | NULL | |
| cname | char(100) | YES | | NULL | |
| cmemo | text | YES | | NULL | |
| jname | char(100) | YES | | NULL | |
| jmemo | text | YES | | NULL | |
+-------+-----------+------+-----+---------+----------------+
7 rows in set (0.22 sec)
]]>
返回