MySQL操作程序四十二(mscowner1.pl,mscowner_man.pl,mscorder_man.pl)

返回
mscowner_arrival.htm的最右側增加OURREF顯示 增加如下code # line_id $t{line_id} = '<td rowspan='; $t{line_id} = $t{line_id} . $t{enq2leng} . '>'; $t{line_id} = $t{line_id} . '<form action="" method="post"><input type="submit" value="ORDER">'; $t{line_id} = $t{line_id} . $t{ourref1} . '<input type="hidden" name="id" value="'; $t{line_id} = $t{line_id} . $t{id1} . '"><input type="hidden" name="pat" value="NO">'; $t{line_id} = $t{line_id} . '<input type="hidden" name="pat2" value="' . $t{pat2} . '">'; $t{line_id} = $t{line_id} . '<input type="hidden" name="rm" value="modeorder1"></form></td></tr>';
INVOICE的時間修改==>自動生成(EXCEL寫入時) ==>2011年以后的訂單! AND YEAR(order1.time) > '2011' mscowner_man.pl的修改注意! $t{sth} = $self->dbh->prepare("SELECT order1.time,order1.orderno,order1.post,order1.posttime,order1.paytime,order1.paymemo,order1.invoicetime,enq1.id,enq1.ourref,enq1.owner,enq1.hullnoid,enq1.enq2s FROM order1,enq1 # $t{sth} = $self->dbh->prepare("SELECT order1.time,order1.orderno,order1.post,order1.posttime,order1.paytime,order1.paymemo,enq1.id,enq1.ourref,enq1.owner,enq1.hullnoid FROM order1,enq1 mscowner1.pl的起動畫面 mscowner_finish.htm ==>歸并到 mscowner_arrival.htm 要找到給invoicetime賦值的程序,修改相應位置! mysql> select id, time, invoicetime from order1 where invoicetime != '2030-01-01 ' AND YEAR(invoicetime) > 2011 LIMIT 10; +-------+------------+-------------+ | id | time | invoicetime | +-------+------------+-------------+ | 9437 | 2010-07-20 | 2012-01-18 | | 10756 | 2011-12-26 | 2012-05-03 | | 10793 | 2012-04-02 | 2012-06-11 | | 13566 | 2011-05-04 | 2012-01-23 | | 13567 | 2011-05-04 | 2012-01-23 | | 13568 | 2011-05-04 | 2012-01-23 | | 13570 | 2011-05-04 | 2012-01-23 | | 14657 | 2011-06-13 | 2012-04-26 | | 14883 | 2012-04-03 | 2012-05-24 | | 14901 | 2012-04-03 | 2012-05-23 | +-------+------------+-------------+ 10 rows in set (0.02 sec)
それぞれの定義 出荷:全部出荷的列表; posttime ==>invoicetime 未出荷:含部分出荷的情況、寫在出荷メモ(post) 入金:全部入金的情況,設有一定時期以前不顯示的功能(如兩年); paytime 未入金:含部分入金的情況、寫在入金メモ(paymemo) 出荷済み(owner_finish.htm)=>order1.htm, 然后回到owner_finish.htm(現(xiàn)在是order_finish.htm) <form action="" method="post"> <input type="submit" value="客戶管理(未入金)へ"><p> <input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">"> <input type="hidden" name="tmpl" value="mscorder_arrival"> <input type="hidden" name="pat" value="NO"> <input type="hidden" name="pat2" value="nopay"> <input type="hidden" name="rm" value="modeorder_man"> </form> <form action="" method="post"> <input type="submit" value="客戶管理(入金済み)へ"><p> <input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">"> <input type="hidden" name="tmpl" value="mscorder_finish"> <input type="hidden" name="pat" value="NO"> <input type="hidden" name="pat2" value="afterpay"> <input type="hidden" name="rm" value="modeorder_man"> </form> ==>改為 <form action="" method="post"> <input type="submit" value="客戶管理(未入金)へ"><p> <input type="hidden" name="area_id" value="<TMPL_VAR NAME="area_id">"> <input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">"> <input type="hidden" name="tmpl" value="mscowner_arrival"> <input type="hidden" name="pat" value="NO"> <input type="hidden" name="pat2" value="nopay"> <input type="hidden" name="rm" value="modeowner_man"> </form> <form action="" method="post"> <input type="submit" value="客戶管理(入金済み)へ"><p> <input type="hidden" name="area_id" value="<TMPL_VAR NAME="area_id">"> <input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">"> <input type="hidden" name="tmpl" value="mscowner_finish"> <input type="hidden" name="pat" value="NO"> <input type="hidden" name="pat2" value="afterpay"> <input type="hidden" name="rm" value="modeowner_man"> </form> <form action="" method="post"> <input type="submit" value="客戶管理(未出荷)へ"><p> <input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">"> <input type="hidden" name="tmpl" value="mscorder_arrival"> <input type="hidden" name="pat" value="NO"> <input type="hidden" name="pat2" value="nopost"> <input type="hidden" name="rm" value="modeorder_man"> </form> ==>改為 <form action="" method="post"> <input type="submit" value="客戶管理(未出荷)へ"><p> <input type="hidden" name="area_id" value="<TMPL_VAR NAME="area_id">"> <input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">"> <input type="hidden" name="tmpl" value="mscowner_arrival"> <input type="hidden" name="pat" value="NO"> <input type="hidden" name="pat2" value="nopost"> <input type="hidden" name="rm" value="modeowner_man"> </form> <form action="" method="post"> <input type="submit" value="客戶管理(出荷済み)へ"><p> <input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">"> <input type="hidden" name="tmpl" value="mscorder_finish"> <input type="hidden" name="pat" value="NO"> <input type="hidden" name="pat2" value="afterpost"> <input type="hidden" name="rm" value="modeorder_man"> </form> ==>改為 <form action="" method="post"> <input type="submit" value="客戶管理(出荷済み)へ"><p> <input type="hidden" name="area_id" value="<TMPL_VAR NAME="area_id">"> <input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">"> <input type="hidden" name="tmpl" value="mscowner_finish"> <input type="hidden" name="pat" value="NO"> <input type="hidden" name="pat2" value="afterpost"> <input type="hidden" name="rm" value="modeowner_man"> </form> mscorder1.pl,增加area_id $t{area_id} = $self->dbh->selectrow_array("SELECT area FROM owners WHERE id = $t{owner_id}"); $t{template}->param(area_id => $t{area_id}); 客戶管理的company長度限制 客戶管理按顧客代號排隊=>完 ================================================================ mscown_start.htm <form action="" method="post"> <input type="submit" value="未入金"> <input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">"> <input type="hidden" name="tmpl" value="mscorder_arrival"> <input type="hidden" name="pat" value="NO"> <input type="hidden" name="pat2" value="nopay"> <input type="hidden" name="rm" value="modeorder_man"> </form> ==> mscowner1.htm <form action="" method="post"> <input type="submit" value="未入金"> <input type="hidden" name="area_id" value="<TMPL_VAR NAME="area_id">"> <input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">"> <input type="hidden" name="tmpl" value="mscowner_arrival"> <input type="hidden" name="pat" value="NO"> <input type="hidden" name="pat2" value="nopay"> <input type="hidden" name="rm" value="modeowner_man"> </form> htm不要? 和未出荷一樣? ================================================================ mscown_start.htm <form action="" method="post"> <input type="submit" value="入金"> <input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">"> <input type="hidden" name="tmpl" value="mscorder_finish"> <input type="hidden" name="pat" value="NO"> <input type="hidden" name="pat2" value="afterpay"> <input type="hidden" name="rm" value="modeorder_man"> </form> ==> mscowner1.htm <form action="" method="post"> <input type="submit" value="入金"> <input type="hidden" name="area_id" value="<TMPL_VAR NAME="area_id">"> <input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">"> <input type="hidden" name="tmpl" value="mscowner_finish"> <input type="hidden" name="pat" value="NO"> <input type="hidden" name="pat2" value="afterpay"> <input type="hidden" name="rm" value="modeowner_man"> </form> htm不要? 和出荷一樣? ================================================================ mscown_start.htm <form action="" method="post"> <input type="submit" value="未出荷"> <input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">"> <input type="hidden" name="tmpl" value="mscorder_arrival"> <input type="hidden" name="pat" value="NO"> <input type="hidden" name="pat2" value="nopost"> <input type="hidden" name="rm" value="modeorder_man"> </form> ==> mscowner1.htm <form action="" method="post"> <input type="submit" value="未出荷"> <input type="hidden" name="area_id" value="<TMPL_VAR NAME="area_id">"> <input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">"> <input type="hidden" name="tmpl" value="mscowner_arrival"> <input type="hidden" name="pat" value="NO"> <input type="hidden" name="pat2" value="nopost"> <input type="hidden" name="rm" value="modeowner_man"> </form> 復寫mscorder_arrival=>mscowner_arrival.htm ================================================================ mscown_start.htm <form action="" method="post"> <input type="submit" value="出荷"> <input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">"> <input type="hidden" name="tmpl" value="mscorder_finish"> <input type="hidden" name="pat" value="NO"> <input type="hidden" name="pat2" value="afterpost"> <input type="hidden" name="rm" value="modeorder_man"> </form> ==> mscowner1.htm <form action="" method="post"> <input type="submit" value="出荷"> <input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">"> <input type="hidden" name="tmpl" value="mscowner_finish"> <input type="hidden" name="pat" value="NO"> <input type="hidden" name="pat2" value="afterpost"> <input type="hidden" name="rm" value="modeowner_man"> </form> 復寫mscorder_finish.htm=>mscowner_finish.htm mscown_start1 => mscowner1.htm mode(area_id),mscowner1.pl mscowner_finish.htm => mscowner1.htm mode(area_id),mscowner1.pl
# check_owners_orders.pl use strict; use DBI; my(%t,$aref,$pref,$row,@rec,%seen,$n); # 連接數(shù)據(jù)庫 $$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook"; $$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n"; $$pref{dbh}->do("SET NAMES utf8"); if(!$$pref{dbh}){ print "SQL read ERROR!\n"; exit; } # 取出數(shù)據(jù) $t{sth} = $$pref{dbh}->prepare("SELECT owners.id,owners.company,owners.name1,order1.id,order1.orderno,enq1.id,enq1.owner FROM owners,order1,enq1 WHERE order1.id=enq1.id AND order1.orderno != 'NO' AND enq1.owner = owners.id ORDER BY owners.company"); $t{sth}->execute; %seen = (); while ( @rec = $t{sth}->fetchrow_array ) { $t{ownersid1} = $rec[0]; $t{company1} = $rec[1]; $t{name1} = $rec[2]; $t{orderid1} = $rec[3]; $t{orderno1} = $rec[4]; $t{enq1id1} = $rec[5]; $t{enq1owner} = $rec[6]; unless ( $seen{$t{company1}} ) { push(@{ $t{ownersids} },$t{ownersid1}); push(@{ $t{companies} },$t{company1}); if ( $t{name1} ) { push(@{ $t{names} },$t{name1}); } else { push(@{ $t{names} },'NO'); } } $seen{$t{company1}}++; } # 關閉數(shù)據(jù)庫 $$pref{dbh}->disconnect; for $n ( 0 .. $#{ $t{companies} } ) { $t{ownersid1} = $t{ownersids}[$n]; $t{company1} = $t{companies}[$n]; $t{name1} = $t{names}[$n]; $t{nos} = $seen{$t{company1}}; print "ownersid1=$t{ownersid1},name1=$t{name1},company1=$t{company1},nos=$t{nos}\n"; } $t{sum1} = $#{ $t{companies} } + 1; print "sum1=$t{sum1}\n"; --------------------------------------------------------------------------- # check_owners_orders.pl use strict; use DBI; my(%t,$aref,$pref,$row,@rec); # 連接數(shù)據(jù)庫 $$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook"; $$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n"; $$pref{dbh}->do("SET NAMES utf8"); if(!$$pref{dbh}){ print "SQL read ERROR!\n"; exit; } # 取出數(shù)據(jù) $t{sth} = $$pref{dbh}->prepare("SELECT owners.id,owners.company,owners.name1,order1.id,order1.orderno,enq1.id,enq1.owner FROM owners,order1,enq1 WHERE order1.id=enq1.id AND order1.orderno != 'NO' AND enq1.owner = owners.id ORDER BY owners.company"); $t{sth}->execute; while ( @rec = $t{sth}->fetchrow_array ) { $t{ownersid1} = $rec[0]; $t{company1} = $rec[1]; $t{orderid1} = $rec[3]; $t{orderno1} = $rec[4]; $t{enq1id1} = $rec[5]; $t{enq1owner} = $rec[6]; print "ownersid=$t{ownersid1},company=$t{company1},"; print "orderid=$t{orderid1},orderno=$t{orderno1},"; print "enq1id=$t{enq1id1},enq1owner=$t{enq1owner}\n"; } # 關閉數(shù)據(jù)庫 $$pref{dbh}->disconnect; ---------------------------------------------------------------------------- mysql> SELECT id,company,name1 from owners ORDER BY company; 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 | | | invoicetime | date | NO | | 2030-01-01 | | +-----------------+-----------+------+-----+------------+----------------+ 31 rows in set (0.14 sec) mysql> ALTER TABLE order1 ADD invoicetime DATE NOT NULL DEFAULT '2030-01-01'; Query OK, 12395 rows affected (2.78 sec) Records: 12395 Duplicates: 0 Warnings: 0 程序修改 $t{line_one} = $t{line_one} . '<td>' . $t{post1} . '</td>'; ===>PERL連接NULL數(shù)據(jù)有問題!!! if ( $t{post1} ) { $t{line_one} = $t{line_one} . '<td>' . $t{post1} . '</td>'; } else { $t{line_one} = $t{line_one} . '<td>NO</td>'; } 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.11 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 | | | arrivaltime | date | NO | | 2030-01-01 | | | arrivalmemo | text | YES | | NULL | | +-------------+-----------+------+-----+------------+----------------+ 25 rows in set (0.19 sec)
返回
墨脱县| 威远县| 龙山县| 阳新县| 新竹市| 仁布县| 永顺县| 凭祥市| 寻甸| 乌拉特前旗| 错那县| 沁水县| 龙川县| 青河县| 龙江县| 天祝| 保定市| 太保市| 千阳县| 闸北区| 东辽县| 攀枝花市| 沿河| 平昌县| 迁安市| 祁阳县| 昌都县| 兴隆县| 许昌市| 贞丰县| 临猗县| 和龙市| 宿迁市| 万宁市| 石家庄市| 义乌市| 崇义县| 工布江达县| 白山市| 观塘区| 武乡县|