MySQL操作程序二十三(修改OURREF的頭文字)

返回
  1. 修改ourref的TYPE
  2. 修改msceqn1_start.pl的子程序,obtain_time
  3. 一天訂單不超過99部分的修改
  4. 同一天訂單的增加
元TYPE mysql> show columns from enq1; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | time | date | YES | | NULL | | | ourref | int(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 | | +------------+--------------+------+-----+---------+----------------+ 14 rows in set (0.02 sec) mysql> ALTER TABLE enq1 CHANGE ourref ourref CHAR; ERROR 1406 (22001): Data too long for column 'ourref' at row 1 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 1' at line 1 mysql> ALTER TABLE enq1 CHANGE ourref ourref CHAR(11); Query OK, 4656 rows affected (0.95 sec) Records: 4656 Duplicates: 0 Warnings: 0 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 | | +------------+--------------+------+-----+---------+----------------+ 14 rows in set (0.00 sec) ---------------------------------------------- mysql> select * from enq1 where id = max(id); ERROR 1111 (HY000): Invalid use of group function mysql> select * from enq1 where id is max(id); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'max(i d)' at line 1 mysql> select max(id) from enq1; +---------+ | max(id) | +---------+ | 4657 | +---------+ 1 row in set (0.00 sec)
# 取得現(xiàn)在時間的子程序(舊) sub obtain_time { my($a_ref) = @_; my(%t); ($t{day},$t{month},$t{year1}) = (localtime)[3,4,5]; $t{year} = substr($t{year1},-1); $t{month} = $t{month} + 1; $t{month} = sprintf("%02d",$t{month}); $t{day} = sprintf("%02d",$t{day}); $t{year1} = $t{year1} + 1900; $t{time1} = $t{year1} . '-' . $t{month} . '-' . $t{day}; $$a_ref{time1} = $t{time1}; $$a_ref{ymd} = $t{year} . $t{month} . $t{day}; return($a_ref); } # 取得現(xiàn)在時間的子程序(新) sub obtain_time { my($a_ref) = @_; my(%t); ($t{day},$t{month},$t{year1}) = (localtime)[3,4,5]; $t{year} = substr($t{year1},-1); if ( $t{year} == 0 ) { $t{year} = 'A'; } elsif ($t{year} == 1) { $t{year} = 'B'; } elsif ($t{year} == 2) { $t{year} = 'C'; } elsif ($t{year} == 3) { $t{year} = 'D'; } elsif ($t{year} == 4) { $t{year} = 'E'; } elsif ($t{year} == 5) { $t{year} = 'F'; } elsif ($t{year} == 6) { $t{year} = 'G'; } elsif ($t{year} == 7) { $t{year} = 'H'; } elsif ($t{year} == 8) { $t{year} = 'I'; } $t{month} = $t{month} + 1; $t{month} = sprintf("%02d",$t{month}); $t{day} = sprintf("%02d",$t{day}); $t{year1} = $t{year1} + 1900; $t{time1} = $t{year1} . '-' . $t{month} . '-' . $t{day}; $$a_ref{time1} = $t{time1}; $$a_ref{ymd} = $t{year} . $t{month} . $t{day}; return($a_ref); }
# 讀取表格enq1最大號碼的ourref $t{ourref1} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $t{enq1_maxid}"); # 自動生成一個ourref $t{ourref2} = $$a_ref{ymd} . '01'; $t{tmp1} = substr($t{ourref1},-2); if ( $t{tmp1} eq '99' ) { # 最大は99 $t{ourref0} = $t{ourref1}; $t{ourref0_OK} = 'NG'; } elsif ( ($t{ourref1} - $t{ourref2}) >= 0 ) { $t{ourref0} = $t{ourref1} + 1; $t{ourref0_OK} = 'OK'; } else { $t{ourref0} = $t{ourref2}; $t{ourref0_OK} = 'OK'; } --------------------------------------------------------------------------- # 讀取表格enq1最大號碼的ourref $t{ourref1} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $t{enq1_maxid}"); # 自動生成一個ourref $t{ourref2} = $$a_ref{ymd} . '01'; $t{tmp1} = substr($t{ourref1},-2); $t{tmp2} = $t{tmp1} + 1; $t{tmp2} = sprintf("%02d",$t{tmp2}); substr($t{ourref2},-2) = $t{tmp2}; if ( $t{tmp1} eq '99' ) { # 最大は99 $t{ourref0} = $t{ourref1}; $t{ourref0_OK} = 'NG'; # } elsif ( ($t{ourref1} - $t{ourref2}) >= 0 ) { # $t{ourref0} = $t{ourref1} + 1; # $t{ourref0_OK} = 'OK'; } else { $t{ourref0} = $t{ourref2}; $t{ourref0_OK} = 'OK'; }
# 讀取表格enq1最大號碼的ourref $t{ourref1} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $t{enq1_maxid}"); # 自動生成一個ourref $t{ourref2} = $$a_ref{ymd} . '01'; $t{tmp1} = substr($t{ourref1},-2); $t{tmp11} = substr($t{ourref1},1,4); $t{tmp21} = substr($t{ourref2},1,4); if ( $t{tmp1} eq '99' ) { # 最大は99 $t{ourref0} = $t{ourref1}; $t{ourref0_OK} = 'NG'; } elsif ( $t{tmp11} == $t{tmp21} ) { # 同じ日付 $t{tmp2} = $t{tmp1} + 1; $t{tmp2} = sprintf("%02d",$t{tmp2}); substr($t{ourref2},-2) = $t{tmp2}; $t{ourref0} = $t{ourref2}; $t{ourref0_OK} = 'OK'; } else { $t{ourref0} = $t{ourref2}; $t{ourref0_OK} = 'OK'; }
返回
恩施市| 仁怀市| 晋中市| 潜江市| 北碚区| 富宁县| 东光县| 樟树市| 温州市| 平谷区| 肇源县| 十堰市| 临漳县| 新沂市| 灌阳县| 中西区| 高雄市| 陆川县| 陇川县| 盈江县| 延长县| 兰考县| 泰顺县| 元阳县| 海宁市| 米泉市| 祁东县| 连州市| 盈江县| 通化县| 南郑县| 丰台区| 华阴市| 南郑县| 宣恩县| 琼海市| 南皮县| 舟山市| 会宁县| 会东县| 平果县|