MySQL操作程序四十一(mscown_start.pl,mscown_start.htm)
返回
- shipped:出荷;awaiting:未出荷;received:入金;unconfirmed:未入金
- 仕事画面２へ削除
- 仕事画面１へ削除
- 检索窗口的仕事画面1/2削除
- 增加一个实际付钱(cost)和收钱(收到钱)的items,用手动填入(具体的各种问题用memo记入。)
- 有复数OURREF发送时,在各个order1中同时写入"梱包情報/サイズ/重量"
- mscorder1.pl的写status部分删除
客户检索功能的实现==>废除
mscown_start.pl,增加一张客户管理画面2
mscown_start.pl,是否要利用enq1list写入中间检索结果?(和work1/work2一样)
- 恢复order1画面的postmemo和paymemo的修改功能
- 恢复order1画面的posttime和paytime的修改功能
- 显示paytime=2030-01-01的所有单子(未入金一览)
- 显示posttime=2030-01-01的所有订单(未出荷订单一览)
- 入金状況实现(以入金の日付排序),order1/paytime
追加paymemo,用来记载分批付钱的情况。
- 出荷明細实现(以出荷の日付排序),order1/posttime,納入時期,納入先,注意:如果是分批出货,把旧的出货时间写入"納入先"
把"order1.htm的納入時期,納入先"移到"客户管理"画面,变成"出荷時期"と"出荷メモ"
删除order1.htm的納入時期,納入先(posttime,post)
- 仕入先三つの場合,仕入先と入庫時期と入庫メモは自動に三行になる。
- enq2增加arrivaltime,arrivalmemo,(order1不用!)
- 取消qtn_input.htm画面的"入荷確認"(enq2.post)
- 把"出荷明細"和"入金状況"合并,全部合并到一齐。
- mscorder_arrival.htm,仕入先company=>name1に変更
"入庫時期"と"入庫時期"从order1.htm移到mscorder_arrival.htm
- 船东追加的msc244.htm修改,name=>name1
- makers关联程序的修改
mscdatabase.htm,msc231.htm,msc232.htm,msc233.htm,msc234.htm
- 增加makers的简称name1项目,把company全部复写过来。
- "客户管理"和"开始画面"等互相来往
- 入庫状況实现(以ORDERの日付排序),order1/arrivaltime
mscorder_arrival.htm(msc241.htm复制而来)
mscorder_man.pl(mscshowall.pl复制而来,msc.pm增加sub指定),pat=arrival
- enq1的owner已经知道,检索enq1的id,再检索order1的arrivaltime并排序
- 入庫状況的メモ記入機能(order1已有)
- 从order1跳到入庫状況的功能。
- 从arrivaltime跳到order1的画面功能(OURREF,enq1.id)
- 仕入先的公司名
- 入庫状況后面的公司名
- mscenq1_start.pl的paytime赋值"2010-00-00"==>删除
- 把以前的所有posttime,paytime,arrivaltime设为一个早的日期,如'2010-01-01'.
- posttime, paytime, arrivaltime的缺省值改为2030-01-01,为最大,这样新的就排到了前面。
- enq1的columns
- 顾客代号/Company实现,id=1-5显示
旧的THIS_LOOP解除
- # 最大IDを読む==>删除
- 生成新的enq1项目==>删除
- $t{mode} eq 'new' or $t{mode} eq 'copy2',$t{mode} eq 'copy'==>删除
- # COPY一个项目(time/ourref两项不COPY),sub copy_one2删除
- # COPY一个项目(time/ourref两项不COPY),sub copy_one22删除
- # COPY一个项目(time一项也COPY),sub copy_one1删除
- # 取得现在时间的子程序,sub obtain_time删除
- "客户管理"mode,文件名显示
- 表格:顾客代号/Company/入庫状況/出荷明細/入金状況
- 船名/配達删除
- OURREF删除
- 全文複製删除
- COPY删除
- 生成新的OURREF=>删除
- HTML::Template : Attempt to set nonexistent parameter 'enq1_id' - this parameter name doesn't match any declarations in the template file : (die_on_bad_params => 1) at ./pro/mscown_start.pl line 365
- mscown_start.pl==>模仿mscenq1_start.pl
- mscown_start.htm==>模仿mscenq1_start.htm
<![CDATA[
debug1
--------------------------------------
[Mon Jan 02 22:27:06 2012] [error] [client 127.0.0.1] "my" variable %seen masks
earlier declaration in same statement at ./pro/mscown_start.pl line 12.\r, referer: http://127.0.0.1/
my(%t,@rec,$n,$a_ref,$enq1ref,$aref,%seen,$item,$row,@loop,%seen);
==>有两个%seen!
my(%t,@rec,$n,$a_ref,$enq1ref,$aref,%seen,$item,$row,@loop);
]]>
<![CDATA[
mysql> UPDATE order1 SET posttime = '2010-01-01';
Query OK, 12394 rows affected (0.39 sec)
Rows matched: 12394 Changed: 12394 Warnings: 0
mysql> UPDATE order1 SET paytime = '2010-01-01';
Query OK, 12394 rows affected (0.33 sec)
Rows matched: 12394 Changed: 12394 Warnings: 0
mysql> ALTER TABLE order1 DROP COLUMN paytime;
Query OK, 12394 rows affected (1.83 sec)
Records: 12394 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE order1 DROP COLUMN posttime;
Query OK, 12394 rows affected (1.05 sec)
Records: 12394 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE order1 ADD posttime DATE NOT NULL DEFAULT '2030-01-01' AFTER
post;
Query OK, 12394 rows affected (1.24 sec)
Records: 12394 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE order1 ADD paytime DATE NOT NULL DEFAULT '2030-01-01' AFTER p
osttime;
Query OK, 12394 rows affected (0.89 sec)
Records: 12394 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 | char(100) | NO | | NO | |
| 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 | |
| posttime | date | NO | | 2030-01-01 | |
| paytime | date | NO | | 2030-01-01 | |
| status | int(11) | NO | | 0 | |
| arrivaltime | date | NO | | 2030-00-00 | |
| arrivalmemo | text | YES | | NULL | |
| size | text | YES | | NULL | |
| weight | text | YES | | NULL | |
| paymemo | text | YES | | NULL | |
+-----------------+-----------+------+-----+------------+----------------+
30 rows in set (0.31 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 | char(100) | NO | | NO | |
| 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 | |
| posttime | date | NO | | 2030-00-00 | |
| paytime | date | NO | | 2030-00-00 | |
| status | int(11) | NO | | 0 | |
| arrivaltime | date | NO | | 2030-00-00 | |
| arrivalmemo | text | YES | | NULL | |
| size | text | YES | | NULL | |
| weight | text | YES | | NULL | |
| paymemo | text | YES | | NULL | |
+-----------------+-----------+------+-----+------------+----------------+
30 rows in set (0.00 sec)
假设现在的ORDER都已到货!
mysql> UPDATE enq2 SET arrivaltime = '2010-01-01';
Query OK, 13944 rows affected (0.44 sec)
Rows matched: 13944 Changed: 13944 Warnings: 0
mysql> show columns from enq2;
+-------------+-----------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | 2011-00-00 | |
| 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 | |
| arrivaltime | date | NO | | 2030-01-01 | |
| arrivalmemo | text | YES | | NULL | |
+-------------+-----------+------+-----+------------+----------------+
25 rows in set (0.00 sec)
mysql> ALTER TABLE enq2 ADD arrivaltime DATE NOT NULL DEFAULT '2030-01-01';
Query OK, 13944 rows affected (2.05 sec)
Records: 13944 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE enq2 ADD arrivalmemo TEXT;
Query OK, 13944 rows affected (1.17 sec)
Records: 13944 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 | | 2011-00-00 | |
| 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.41 sec)
<tr bgcolor="lightcyan" align="center"><td>入荷確認</td><td>
<!-- post -->
<form action="" method="post">
<input type=text size=50 name="content" value="<TMPL_VAR NAME="post">">
<input type="submit" value="入力">
<input type="hidden" name="pat" value="r_header">
<input type="hidden" name="table" value="enq2">
<input type="hidden" name="item" value="post">
<input type="hidden" name="id" value=<TMPL_VAR NAME="enq2_id">>
<input type="hidden" name="rm" value="modeqtn_input">
</form>
</td></tr>
mysql> show columns from makers;
+----------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| company | char(100) | YES | | NULL | |
| name1 | char(100) | YES | | NULL | |
| address | char(100) | YES | | NULL | |
| person | char(50) | YES | | NULL | |
| telfax | char(100) | YES | | NULL | |
| email | char(100) | YES | | NULL | |
| homepage | char(100) | YES | | NULL | |
| memo | char(200) | YES | | NULL | |
| nationid | int(11) | NO | | 2 | |
+----------+-----------+------+-----+---------+----------------+
10 rows in set (0.02 sec)
mysql> ALTER TABLE makers ADD name1 CHAR(100) AFTER company;
Query OK, 771 rows affected (1.42 sec)
Records: 771 Duplicates: 0 Warnings: 0
mysql> UPDATE makers SET name1 = company;
Query OK, 771 rows affected (0.30 sec)
Rows matched: 771 Changed: 771 Warnings: 0
mysql> show columns from makers;
+----------+-----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| company | char(100) | YES | | NULL | |
| address | char(100) | YES | | NULL | |
| person | char(50) | YES | | NULL | |
| telfax | char(100) | YES | | NULL | |
| email | char(100) | YES | | NULL | |
| homepage | char(100) | YES | | NULL | |
| memo | char(200) | YES | | NULL | |
| nationid | int(11) | NO | | 2 | |
+----------+-----------+------+-----+---------+----------------+
9 rows in set (0.59 sec)
]]>
<![CDATA[
mysql> SELECT makers.company FROM makers,enq2 WHERE enq2.makerid=makers.id AND enq2.id = 2021;
mysql> UPDATE order1 SET posttime = '2010-01-01';
Query OK, 12388 rows affected (0.73 sec)
Rows matched: 12388 Changed: 12388 Warnings: 0
mysql> UPDATE order1 SET paytime = '2010-01-01';
Query OK, 12388 rows affected (0.38 sec)
Rows matched: 12388 Changed: 12388 Warnings: 0
mysql> UPDATE order1 SET arrivaltime = '2010-01-01';
Query OK, 12388 rows affected (1.03 sec)
Rows matched: 12388 Changed: 12388 Warnings: 0
mysql> SELECT enq1.owner,order1.id,order1.orderno FROM order1,enq1 WHERE order1.
id=enq1.id AND enq1.owner = 1 LIMIT 5;
+-------+----+---------+
| owner | id | orderno |
+-------+----+---------+
| 1 | 1 | NO |
| 1 | 2 | NO |
| 1 | 9 | NO |
| 1 | 10 | NO |
| 1 | 12 | NO |
+-------+----+---------+
5 rows in set (0.00 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 | char(100) | NO | | NO | |
| 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 | |
| posttime | date | NO | | 2030-01-01 | |
| paytime | date | NO | | 2030-01-01 | |
| status | int(11) | NO | | 0 | |
| arrivaltime | date | NO | | 2030-01-01 | |
| arrivalmemo | text | YES | | NULL | |
| size | text | YES | | NULL | |
| weight | text | YES | | NULL | |
+-----------------+-----------+------+-----+------------+----------------+
29 rows in set (0.53 sec)
mysql> ALTER TABLE order1 ALTER posttime SET DEFAULT '2030-01-01';
Query OK, 0 rows affected (0.48 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE order1 ALTER paytime SET DEFAULT '2030-01-01';
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE order1 ALTER arrivaltime SET DEFAULT '2030-01-01';
Query OK, 0 rows affected (0.11 sec)
Records: 0 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 | char(100) | NO | | NO | |
| 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 | |
| posttime | date | NO | | 2011-00-00 | |
| paytime | date | NO | | 0000-00-00 | |
| status | int(11) | NO | | 0 | |
| arrivaltime | date | NO | | 2011-00-00 | |=>入庫状況,最新的入庫时间,注意如果是分批入庫,旧的时间写在下面的memo
| arrivalmemo | text | YES | | NULL | |
| size | text | YES | | NULL | |
| weight | text | YES | | NULL | |
+-----------------+-----------+------+-----+------------+----------------+
29 rows in set (0.39 sec)
mysql> show columns from enq1;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| ourref | char(11) | YES | | NULL | |
| owner | int(11) | YES | | NULL | |
| ownerno | varchar(100) | YES | | NULL | |
| hullnoid | int(11) | YES | | NULL | |
| type1id | text | YES | | NULL | |
| partsid | text | YES | | NULL | |
| QTY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| makerid | int(11) | YES | | NULL | |
| enq2s | text | YES | | NULL | |
| seriesid | text | YES | | NULL | |
| tmp_pname | text | YES | | NULL | |
| tmp_pcode | text | YES | | NULL | |
| tmp_pqty | text | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
17 rows in set (0.16 sec)
mysql> show columns from enq2;
+------------+-----------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | 2011-00-00 | |
| 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.97 sec)
]]>
返回