MySQL操作程序三十九(Order summary網(wǎng)頁(yè)程序3,整理3)
返回
作業(yè)內(nèi)容
- enq2的迄今內(nèi)容整理
全部已付錢的status設(shè)定置換程序
- order1的迄今內(nèi)容整理
全部已付錢的status設(shè)定置換程序
- order1畫面同時(shí)多個(gè)OURREF的post輸入
- enq2的status變更操作
enq2刪除時(shí),status恢復(fù)為零
- enq2,time,支払時(shí)期
輸入"2011-00-00"時(shí),未收貨的置status為0
輸入其他"支払時(shí)期"時(shí),未收貨的置status為1
輸入其他"支払時(shí)期"時(shí),已收貨的置status為3
- enq2,post,入荷確認(rèn)
輸入'NO'時(shí),時(shí)間是"2011-00-00"的置status為0
輸入'NO'時(shí),其他時(shí)間置status為1
輸入'NO'以外時(shí),已付錢的置status為3
輸入'NO'以外時(shí),未付錢的置status為2
- ******************************************************************
- enq2的time的default改為"2011-00-00"
有關(guān)改寫時(shí)間停止,mscenq1_start.pl,mscquo2.pl
==>enq的時(shí)間寫入取消(已有缺省值)
- enq2增加收貨狀態(tài)確認(rèn)按鈕,mscqtn_input.htm,mscqtn_input.pl
- 開(kāi)始畫面不顯示'NO',mscenq1_start.pl
- order1的status變更操作,mscorder1.htm
輸入客戶ORDER號(hào)碼時(shí),置status為1
輸入OURREF號(hào)碼時(shí),置status為2
輸入"入金時(shí)期"時(shí),已發(fā)貨的置status為5
輸入"入金時(shí)期"時(shí),未發(fā)貨的置status為3
輸入"納入先/時(shí)期"時(shí),未收貨款的置status為4
輸入"納入先/時(shí)期"時(shí),已收貨款的置status為5
- enq2增加項(xiàng)目時(shí),插入0=>意義不大,已自動(dòng)賦零
mscquo2.pl,複數(shù)仕入先
- order1增加項(xiàng)目時(shí),插入0,mscenq1_start.pl=>意義不大,已自動(dòng)賦零
- order1增加status, INT, 0=>沒(méi)有ORDER; 1=>ORDER開(kāi)始,2=>和其他ORDER合并,3=>已收到貨款,4=>已發(fā)貨,4=>全部完成
- enq2增加status, INT, 0=>沒(méi)有ORDER; 1=>ORDER開(kāi)始,2=>已付貨款,3=>已收到貨物
<![CDATA[
mysql> ALTER TABLE enq2 CHANGE time time DATE DEFAULT '2011-00-00';
Query OK, 13929 rows affected (2.63 sec)
Records: 13929 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE enq2 ADD post CHAR(100) NOT NULL DEFAULT 'NO';
Query OK, 13929 rows affected (2.08 sec)
Records: 13929 Duplicates: 0 Warnings: 0
mysql> show columns from enq2;
+------------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| enq1id | int(11) | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| makerid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| type1id | text | YES | | NULL | |
| partsid | text | YES | | NULL | |
| QTY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| price | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| discount0 | int(11) | YES | | NULL | |
| money | int(11) | YES | | NULL | |
| price1 | text | YES | | NULL | |
| makerref | text | YES | | NULL | |
| disc | text | YES | | NULL | |
| charges | text | YES | | NULL | |
| status | int(11) | NO | | 0 | |
| post | char(100) | NO | | NO | |
+------------+-----------+------+-----+---------+----------------+
23 rows in set (0.02 sec)
mysql> show columns from order1;
+-----------------+-----------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| orderno | text | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| consignee | text | YES | | NULL | |
| changeprice | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| total | text | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| packing_charge | text | YES | | NULL | |
| freight_charges | text | YES | | NULL | |
| delivery_charge | text | YES | | NULL | |
| delivery_place | text | YES | | NULL | |
| disc | text | YES | | NULL | |
| shipping | text | YES | | NULL | |
| packing | text | YES | | NULL | |
| packing_info | text | YES | | NULL | |
| post | char(100) | NO | | NO | |
| paytime | date | NO | | 0000-00-00 | |
| status | int(11) | NO | | 0 | |
+-----------------+-----------+------+-----+------------+----------------+
24 rows in set (0.05 sec)
mysql> ALTER TABLE order1 CHANGE post post CHAR(100) NOT NULL DEFAULT 'NO';
Query OK, 12380 rows affected (0.75 sec)
Records: 12380 Duplicates: 0 Warnings: 0
mysql> UPDATE order1 SET post = 'NO' where post IS NULL;
Query OK, 8 rows affected (0.16 sec)
Rows matched: 8 Changed: 8 Warnings: 0
mysql> select post from order1 where id = 12373;
+------+
| post |
+------+
| NULL |
+------+
1 row in set (0.06 sec)
mysql> ALTER TABLE order1 CHANGE post post CHAR(100) NOT NULL DEFAULT 'NO';
ERROR 1265 (01000): Data truncated for column 'post' at row 12373
mysql> ALTER TABLE order1 CHANGE post post TEXT NOT NULL DEFAULT 'NO';
ERROR 1101 (42000): BLOB/TEXT column 'post' can't have a default value
mysql> select id post from order1 where post is NULL;
+-------+
| post |
+-------+
| 12373 |
| 12374 |
| 12375 |
| 12376 |
| 12377 |
| 12378 |
| 12379 |
| 12380 |
+-------+
8 rows in set (0.03 sec)
# statusを更新
# 輸入是否是OURREF的確認(rèn)
# 形成HASH
my %member;
my $sth = $self->dbh->prepare("SELECT ourref FROM enq1");
$sth->execute ();
while (my ($val) = $sth->fetchrow_array())
{
$member{$val} = 1;
}
# 然后使用
$t{valid} = exists ($member{$t{content}});
if ( $t{valid} == 1 ) {
$t{sql} = 'UPDATE order1 set status = 2 where id = ';
$t{sql} .= $t{order1_id};
$t{DO} = $self->dbh->do($t{sql});
} else {
$t{sql} = 'UPDATE order1 set status = 1 where id = ';
$t{sql} .= $t{order1_id};
$t{DO} = $self->dbh->do($t{sql});
}
} elsif ( $t{item} eq 'paytime' ) { # status
$t{post} = $self->dbh->selectrow_array("SELECT post FROM order1 WHERE id = $t{order1_id} and post is NOT NULL");
if ( $t{post} ne 'NO' && $t{content} ne '2010-00-00' ) {
$t{sql} = 'UPDATE order1 set status = 5 where id = ';
$t{sql} .= $t{order1_id};
$t{DO} = $self->dbh->do($t{sql});
} elsif ( $t{content} eq '2010-00-00' ) {
$t{sql} = 'UPDATE order1 set status = 1 where id = ';
$t{sql} .= $t{order1_id};
$t{DO} = $self->dbh->do($t{sql});
} else {
$t{sql} = 'UPDATE order1 set status = 3 where id = ';
$t{sql} .= $t{order1_id};
$t{DO} = $self->dbh->do($t{sql});
}
} elsif ( $t{item} eq 'post' ) { # status
$t{paytime} = $self->dbh->selectrow_array("SELECT paytime FROM order1 WHERE id = $t{order1_id}");
if ( $t{paytime} eq '2010-00-00' ) {
$t{sql} = 'UPDATE order1 set status = 4 where id = ';
$t{sql} .= $t{order1_id};
$t{DO} = $self->dbh->do($t{sql});
} elsif ( $t{content} eq 'NO' ) {
$t{sql} = 'UPDATE order1 set status = 1 where id = ';
$t{sql} .= $t{order1_id};
$t{DO} = $self->dbh->do($t{sql});
} else {
$t{sql} = 'UPDATE order1 set status = 5 where id = ';
$t{sql} .= $t{order1_id};
$t{DO} = $self->dbh->do($t{sql});
}
}
******************************************************************************
mysql> show columns from enq2;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| enq1id | int(11) | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| makerid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| type1id | text | YES | | NULL | |
| partsid | text | YES | | NULL | |
| QTY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| price | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| discount0 | int(11) | YES | | NULL | |
| money | int(11) | YES | | NULL | |
| price1 | text | YES | | NULL | |
| makerref | text | YES | | NULL | |
| disc | text | YES | | NULL | |
| charges | text | YES | | NULL | |
+------------+---------+------+-----+---------+----------------+
21 rows in set (0.24 sec)
mysql> ALTER TABLE enq2 ADD status INT NOT NULL DEFAULT '0';
Query OK, 13921 rows affected (0.88 sec)
Records: 13921 Duplicates: 0 Warnings: 0
mysql> show columns from enq2;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| enq1id | int(11) | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| makerid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| type1id | text | YES | | NULL | |
| partsid | text | YES | | NULL | |
| QTY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| price | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| discount0 | int(11) | YES | | NULL | |
| money | int(11) | YES | | NULL | |
| price1 | text | YES | | NULL | |
| makerref | text | YES | | NULL | |
| disc | text | YES | | NULL | |
| charges | text | YES | | NULL | |
| status | int(11) | NO | | 0 | |
+------------+---------+------+-----+---------+----------------+
22 rows in set (0.01 sec)
]]>
<![CDATA[
mysql> show columns from order1;
+-----------------+---------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| orderno | text | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| consignee | text | YES | | NULL | |
| changeprice | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| total | text | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| packing_charge | text | YES | | NULL | |
| freight_charges | text | YES | | NULL | |
| delivery_charge | text | YES | | NULL | |
| delivery_place | text | YES | | NULL | |
| disc | text | YES | | NULL | |
| shipping | text | YES | | NULL | |
| packing | text | YES | | NULL | |
| packing_info | text | YES | | NULL | |
| post | text | YES | | NULL | |
| paytime | date | NO | | 0000-00-00 | |
+-----------------+---------+------+-----+------------+----------------+
23 rows in set (0.84 sec)
mysql> ALTER TABLE order1 ADD status INT NOT NULL DEFAULT '0';
Query OK, 12374 rows affected (2.00 sec)
Records: 12374 Duplicates: 0 Warnings: 0
mysql> show columns from order1;
+-----------------+---------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| orderno | text | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| consignee | text | YES | | NULL | |
| changeprice | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| total | text | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| packing_charge | text | YES | | NULL | |
| freight_charges | text | YES | | NULL | |
| delivery_charge | text | YES | | NULL | |
| delivery_place | text | YES | | NULL | |
| disc | text | YES | | NULL | |
| shipping | text | YES | | NULL | |
| packing | text | YES | | NULL | |
| packing_info | text | YES | | NULL | |
| post | text | YES | | NULL | |
| paytime | date | NO | | 0000-00-00 | |
| status | int(11) | NO | | 0 | |
+-----------------+---------+------+-----+------------+----------------+
24 rows in set (0.14 sec)
]]>
返回
醴陵市|
河源市|
营山县|
同仁县|
思茅市|
康乐县|
台东县|
饶河县|
大英县|
田阳县|
盐亭县|
宜章县|
东兰县|
西峡县|
竹溪县|
象山县|
浮山县|
台山市|
密山市|
奎屯市|
万州区|
公主岭市|
板桥市|
广州市|
抚顺县|
宁明县|
铁力市|
聂荣县|
宝山区|
都江堰市|
读书|
龙江县|
商水县|
琼海市|
堆龙德庆县|
浪卡子县|
台北市|
大名县|
陵水|
昭觉县|
韶关市|