MySQL操作程序三十(name/type/maker輸入的修改)
返回
- 檢索畫面的生成,mscs_engine.htm
- 檢索結(jié)果寫入中間變量=>完成
- 機(jī)械名/型式/maker的排序按鈕=>完成
- TYPE的修改功能合并過來,TYPE修改,機(jī)械名/maker只能選擇
原有mscshowtype1.pl改名mscshowtype1_old.pl,mscshowone.pl復(fù)制一個(gè)到mscshowtype1.pl改造
msc262.htm復(fù)寫一個(gè)為msctype1.htm,TYPE修改功能=>完成
- 機(jī)械名/maker選擇功能,mscengine_nm.htm,mscengine_nm.pl=>完成
- 新型式追加,機(jī)械名/maker自動(dòng)寫入1,mscengine_add.pl,mscengine_add.htm=>完成
- 新機(jī)械名輸入功能,mscshowadd.pl=>mscsgname.pl,msc284.htm,修改功能也移過來==>完成
mscshowname1.pl,mscname1.htm,管理畫面
mscshowname2.pl,mscname2.htm,修改一個(gè)name畫面
- maker輸入功能,mscshowadd.pl=>mscsmaker.pl,msc294.htm,修改功能也移過來==>完成
mscshowmaker1.pl,mscmaker1.htm,管理畫面
mscshowmaker2.pl,mscmaker2.htm,修改一個(gè)name畫面
- enq1畫面的主機(jī)刪除有問題?==>忘記了enq1_id的傳送==>完成
- enq1的選擇功能,mscenq1.pl,pat=u_engine==>完成
- write_excel.pl寫出主機(jī)名時(shí)的檢查
- ENQ畫面的處理,mscenq1.htm
- 主機(jī)[新規(guī)追加]取消=>完成
- 機(jī)械名/maker的選擇按鈕取消=>完成
- mscdatabase.htm的3個(gè)按鈕刪除
main_type1管理=>完成
main_name1管理=>完成
main_maker1管理=>完成
- 統(tǒng)一同時(shí)檢索,name/type/maker
- 三者完全相同時(shí),不追加;只要有一個(gè)不同,即追加
- 沒有type時(shí),type和name相同
- ENQ畫面的name/maker的選擇功能移動(dòng)(涉及到mscenq1.pl?)
- 先把main_type1的gname_id和maker_id要改成INT(現(xiàn)為TEXT)
- 關(guān)聯(lián)HTML文件
mscenq1.htm
msc262.htm
mscengine_add.htm=>在該界面上選擇name1/maker1或追加name1/maker1
msc284.htm(main_name1)
msc294.htm(main_maker1)
mscengine_nm.htm
- 關(guān)聯(lián)PERL程序
- mscs_engine.pl=>name1/type1/maker1都找
沒有輸入時(shí),返回
讀main_name1和main_maker1并形成聯(lián)想行列==>完成,見下面
- mscenq1.pl
pat=u_engine有問題?
- mscengine_add.pl
- mscshowadd.pl
- 關(guān)聯(lián)TABLE,main_type1/main_name1/main_maker1
main_type1的gname_id和maker_id是否要改成INT(現(xiàn)為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上執(zhí)行
#---------追加一個(gè)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的一個(gè)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中的指定項(xiàng)目
# 更新enq1的type1id,partsid和QTY
# 調(diào)出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號(hào)
mscenq1.htm,取消以下部分
<form action="" method="post">
<input type="submit" value="新規(guī)追加"><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[
#---------更新主機(jī)項(xiàng)目
新
舊
} 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="新規(guī)追加"><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%>機(jī)械名</th>
<th width=20%>型式</th>
<th width=20%>メーカー</th>
<th width=10%>削除釦</th>
舊
<th width=10%>順番</th>
<th width=20%>機(jī)械名
<form action="" method="post">
<input type="submit" value="新規(guī)追加"><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="新規(guī)追加"><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="新規(guī)追加"><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 => "請選一個(gè)");
$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)
]]>
返回
绥江县|
孙吴县|
车险|
青海省|
全州县|
齐河县|
太仓市|
克山县|
泸州市|
新津县|
苗栗县|
余姚市|
和硕县|
宁远县|
酉阳|
永州市|
蓝田县|
海林市|
万宁市|
怀来县|
增城市|
怀化市|
高安市|
蒙山县|
沽源县|
张家川|
太和县|
涟源市|
临泉县|
邮箱|
凤冈县|
民丰县|
通道|
广平县|
东台市|
闽侯县|
英吉沙县|
石门县|
西充县|
墨竹工卡县|
拉萨市|