PERL操作程序五十八(mscsummary.pl,统计程序和界面)
返回
<![CDATA[
增加卖出消费税显示
if ( $t{tax1} == 1 ) {
$t{total13} = 0;
} else {
$t{addtax1} = $self->dbh->selectrow_array("SELECT tax1 FROM tax_ok WHERE id = $t{tax1}");
$t{total13} = $t{total12}*$t{addtax1}/100;
$t{total13} = sprintf("%.0f",$t{total13});
}
$t{total13s}{$t{NO}} = $t{total13};
]]>
<![CDATA[
mscsummary02.htm
界面的"梱包サイズ和梱包重量"合并为"梱包サイズ/重量"
删掉以下部分
# $t{line_one} = $t{line_one} . '<td rowspan=';
# $t{line_one} = $t{line_one} . $t{enq2leng} . '>';
# $t{line_one} = $t{line_one} . $t{weight1} . '</td>';
]]>
- 已order、未出荷的订单、（包括已入金）
- 已出荷、还没有发invoice的订单
- 已发invoice、还没有入金的订单
- 已入金、未出荷的订单
- 已入金、已出荷的订单
- 売りあげの統計(年計)
- 売りあげの統計(月計)
<![CDATA[
$t{moneyid} = $self->dbh->selectrow_array("SELECT money FROM enq2 WHERE id = $t{enq2_id}");
$t{money} = $self->dbh->selectrow_array("SELECT English FROM money WHERE id = $t{moneyid}");
----------------------------------------------------------------
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.03 sec)
----------------------------------------------------------------
mysql> select id, English from money;
+----+---------+
| id | English |
+----+---------+
| 1 | JPY |
| 2 | USD |
| 3 | EUR |
| 4 | GBP |
| 5 | RMB |
| 6 | SGD |
+----+---------+
6 rows in set (0.00 sec)
货币的单位的序列号是money
----------------------------------------------------------------
mysql> show columns from quo2;
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| money | int(11) | YES | | NULL | |
| percent0 | int(11) | YES | | NULL | |
| discount0 | int(11) | YES | | NULL | |
| percent | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| price | text | YES | | NULL | |
| D_FEE | text | YES | | NULL | |
| total | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| disc0 | int(11) | YES | | NULL | |
| disc | text | YES | | NULL | |
| price0 | text | YES | | NULL | |
| pi_ok | int(11) | NO | | 2 | |
| ti_ok | int(11) | YES | | 2 | |
+-----------+---------+------+-----+---------+----------------+
20 rows in set (0.00 sec)
通过时间选择pattern(年)
----------------------------------------------------------------
# timetest.pl
use strict;
my(%t,$n);
#$t{pat0} = '2011-11-03';
$t{pat0} = '2007-09-03';
print "old pat0=$t{pat0}\n";
$t{pat0} =~ s/-//g;
print "new pat0=$t{pat0}\n";
$t{T20081} = '20071001'; # pat2008
$t{T20082} = '20080930';
$t{T20091} = '20081001'; # pat2009
$t{T20092} = '20090930';
$t{T20101} = '20091001'; # pat2010
$t{T20102} = '20100930';
$t{T20111} = '20101001'; # pat2011
$t{T20112} = '20110930';
$t{T20121} = '20111001'; # pat2012
$t{T20122} = '20120930';
$t{T20131} = '20121001'; # pat2013
$t{T20132} = '20130930';
if ( $t{pat0} >= $t{T20081} && $t{pat0} <= $t{T20082} ) {
$t{pat} = 2008;
} elsif ( $t{pat0} >= $t{T20091} && $t{pat0} <= $t{T20092} ) {
$t{pat} = 2009;
} elsif ( $t{pat0} >= $t{T20101} && $t{pat0} <= $t{T20102} ) {
$t{pat} = 2010;
} elsif ( $t{pat0} >= $t{T20111} && $t{pat0} <= $t{T20112} ) {
$t{pat} = 2011;
} elsif ( $t{pat0} >= $t{T20121} && $t{pat0} <= $t{T20122} ) {
$t{pat} = 2012;
} elsif ( $t{pat0} >= $t{T20131} && $t{pat0} <= $t{T20132} ) {
$t{pat} = 2013;
}
print "pat=$t{pat}\n";
通过时间选择pattern(月)
----------------------------------------------------------------
# timetest.pl
use strict;
my(%t,$n);
$t{in1} = '2011-11-03';
#$t{in1} = '2012-09-03';
print "old in1=$t{in1}\n";
$t{in1} =~ s/-//g;
print "new in1=$t{in1}\n";
$t{T011} = '20111001'; # pat1
$t{T012} = '20111031';
$t{T021} = '20111101'; # pat2
$t{T022} = '20111130';
$t{T031} = '20111201'; # pat3
$t{T032} = '20111231';
$t{T041} = '20120101'; # pat4
$t{T042} = '20120131';
$t{T051} = '20120201'; # pat5
$t{T052} = '20120229';
$t{T061} = '20120301'; # pat6
$t{T062} = '20120331';
$t{T071} = '20120401'; # pat7
$t{T072} = '20120430';
$t{T081} = '20120501'; # pat8
$t{T082} = '20120531';
$t{T091} = '20120601'; # pat9
$t{T092} = '20120630';
$t{T101} = '20120701'; # pat10
$t{T102} = '20120731';
$t{T111} = '20120801'; # pat11
$t{T112} = '20120831';
$t{T121} = '20120901'; # pat12
$t{T122} = '20120930';
$t{T131} = '20121001'; # pat13
$t{T132} = '20121031';
if ( $t{in1} >= $t{T011} && $t{in1} <= $t{T012} ) {
$t{pat} = 1;
} elsif ( $t{in1} >= $t{T021} && $t{in1} <= $t{T022} ) {
$t{pat} = 2;
} elsif ( $t{in1} >= $t{T031} && $t{in1} <= $t{T032} ) {
$t{pat} = 3;
} elsif ( $t{in1} >= $t{T041} && $t{in1} <= $t{T042} ) {
$t{pat} = 4;
} elsif ( $t{in1} >= $t{T051} && $t{in1} <= $t{T052} ) {
$t{pat} = 5;
} elsif ( $t{in1} >= $t{T061} && $t{in1} <= $t{T062} ) {
$t{pat} = 6;
} elsif ( $t{in1} >= $t{T071} && $t{in1} <= $t{T072} ) {
$t{pat} = 7;
} elsif ( $t{in1} >= $t{T081} && $t{in1} <= $t{T082} ) {
$t{pat} = 8;
} elsif ( $t{in1} >= $t{T091} && $t{in1} <= $t{T092} ) {
$t{pat} = 9;
} elsif ( $t{in1} >= $t{T101} && $t{in1} <= $t{T102} ) {
$t{pat} = 10;
} elsif ( $t{in1} >= $t{T111} && $t{in1} <= $t{T112} ) {
$t{pat} = 11;
} elsif ( $t{in1} >= $t{T121} && $t{in1} <= $t{T122} ) {
$t{pat} = 12;
} elsif ( $t{in1} >= $t{T131} && $t{in1} <= $t{T132} ) {
$t{pat} = 13;
}
print "pat=$t{pat}\n";
pat分类
----------------------------------------------------------------
if ( $t{pat} == 1 ) {
$t{explain1} = '2011年10月1日-2011年10月31日';
$t{time1} = "\'2011-10-01\'";
$t{time2} = "\'2011-10-31\'";
} elsif ( $t{pat} == 2 ) {
$t{explain1} = '2011年11月1日-2011年11月30日';
$t{time1} = "\'2011-11-01\'";
$t{time2} = "\'2011-11-30\'";
} elsif ( $t{pat} == 3 ) {
$t{explain1} = '2011年12月1日-2011年12月31日';
$t{time1} = "\'2011-12-01\'";
$t{time2} = "\'2011-12-31\'";
} elsif ( $t{pat} == 4 ) {
$t{explain1} = '2012年1月1日-2012年1月31日';
$t{time1} = "\'2012-01-01\'";
$t{time2} = "\'2012-01-31\'";
} elsif ( $t{pat} == 5 ) {
$t{explain1} = '2012年2月1日-2012年2月29日';
$t{time1} = "\'2012-02-01\'";
$t{time2} = "\'2012-02-29\'";
} elsif ( $t{pat} == 6 ) {
$t{explain1} = '2012年3月1日-2012年3月31日';
$t{time1} = "\'2012-03-01\'";
$t{time2} = "\'2012-03-31\'";
} elsif ( $t{pat} == 7 ) {
$t{explain1} = '2012年4月1日-2012年4月30日';
$t{time1} = "\'2012-04-01\'";
$t{time2} = "\'2012-04-30\'";
} elsif ( $t{pat} == 8 ) {
$t{explain1} = '2012年5月1日-2012年5月31日';
$t{time1} = "\'2012-05-01\'";
$t{time2} = "\'2012-05-31\'";
} elsif ( $t{pat} == 9 ) {
$t{explain1} = '2012年6月1日-2012年6月30日';
$t{time1} = "\'2012-06-01\'";
$t{time2} = "\'2012-06-30\'";
} elsif ( $t{pat} == 10 ) {
$t{explain1} = '2012年7月1日-2012年7月31日';
$t{time1} = "\'2012-07-01\'";
$t{time2} = "\'2012-07-31\'";
} elsif ( $t{pat} == 11 ) {
$t{explain1} = '2012年8月1日-2012年8月31日';
$t{time1} = "\'2012-08-01\'";
$t{time2} = "\'2012-08-31\'";
} elsif ( $t{pat} == 12 ) {
$t{explain1} = '2012年9月1日-2012年9月30日';
$t{time1} = "\'2012-09-01\'";
$t{time2} = "\'2012-09-30\'";
} elsif ( $t{pat} == 13 ) {
$t{explain1} = '2012年10月1日-2012年10月31日';
$t{explain1} = $t{pat0};
$t{time1} = "\'2012-10-01\'";
$t{time2} = "\'2012-10-31\'";
}
注意对时间变量加引号!
----------------------------------------------------------------
$t{time1} = "\'2011-10-01\'";
$t{time2} = "\'2011-11-30\'";
$t{sth} = $self->dbh->prepare("SELECT id,time,orderno,post,posttime,paytime,paymemo,invoicetime FROM order1
WHERE time BETWEEN $t{time1} AND $t{time2}
AND orderno != 'NULL'
AND orderno != 'NO'
ORDER BY time DESC");
利用BETWEEN!!!!!!
----------------------------------------------------------------
mysql> select id, time, orderno from order1 where orderno != 'NULL' and orderno
!= 'NO' and time BETWEEN "2011-10-01" and "2012-09-30" ORDER by time limit 5;
+-------+------------+---------------+
| id | time | orderno |
+-------+------------+---------------+
| 17003 | 2011-10-05 | 5OCT-11 TAN |
| 17032 | 2011-10-05 | |
| 16780 | 2011-10-05 | BWL-SP-039-11 |
| 16726 | 2011-10-06 | 104 |
| 16987 | 2011-10-06 | GAM-SP-044-11 |
+-------+------------+---------------+
5 rows in set (0.05 sec)
下面的数据库访问的问题在什么地方?(不能执行!)
----------------------------------------------------------------
# 取出数据的条件
# $t{act1} = "SELECT id,time,orderno,post,posttime,paytime,paymemo,invoicetime FROM order1 WHERE orderno != 'NULL' AND orderno != 'NO' AND ((YEAR(time) = $t{year1} and MONTH(time) >= $t{month1} and DAYOFMONTH(time) >= $t{day1} ) OR (YEAR(time) = $t{year2} and MONTH(time) <= $t{month2} and DAYOFMONTH(time) <= $t{dya2} )) ORDER BY time DESC";
# $t{sth} = $self->dbh->prepare($t{act1});
# $t{sth}->execute;
# 取出数据
# @loop = ();
# while ( @arr1 = $t{sth}->fetchrow_array ) {
# my $row_ref = (); # 这个初始化非常重要!
# $t{id1} = $arr1[0];
# $t{time1} = $arr1[1];
# $t{orderno1} = $arr1[2];
# $t{post1} = $arr1[3];
# $t{posttime} = $arr1[4];
# $t{paytime} = $arr1[5];
# $t{paymemo} = $arr1[6];
# $t{invoice1} = $arr1[7];
# $t{line_one} = '<tr><td>';
# $t{line_one} = $t{line_one} . $t{id1} . '</td><td>';
# $t{line_one} = $t{line_one} . $t{time1} . '</td><td>';
# $t{line_one} = $t{line_one} . $t{orderno1} . '</td><td>';
# $t{line_one} = $t{line_one} . $t{post1} . '</td><td>';
# $t{line_one} = $t{line_one} . $t{posttime} . '</td><td>';
# $t{line_one} = $t{line_one} . $t{paytime} . '</td><td>';
# $t{line_one} = $t{line_one} . $t{paymemo} . '</td><td>';
# $t{line_one} = $t{line_one} . $t{invoice1} . '</td><td>';
# $t{line_one} = $t{line_one} . '</td></tr>';
#
# $$row_ref{line_one} = $t{line_one};
# push(@loop, $row_ref);
# }
# $t{sth}->finish;
----------------------------------------------------------------
mysql> select time,orderno,post,posttime,paytime,paymemo,invoicetime FROM order1
WHERE orderno != 'NULL' AND orderno != 'NO' AND ((YEAR(time) = '2011' and MONTH
(time) >= 10 and DAYOFMONTH(time) >= 1) OR (YEAR(time) = '2012' and MONTH(time)
<= 3 and DAYOFMONTH(time) <= 30)) ORDER BY time DESC;
----------------------------------------------------------------
取出2011年10月1日-2012年9月30日的订单
mysql> select id, time, orderno from order1 where orderno != 'NULL' and orderno
!='NO' and ((YEAR(time) = 2011 and MONTH(time) >= 10 and DAYOFMONTH(time) >= 1)
or (YEAR(time) = 2012 and MONTH(time) <=9 and DAYOFMONTH(time) <=30));
mysql> select id, time, orderno from order1 where orderno != 'NULL' and orderno
!='NO' and ((YEAR(time) = 2011 and MONTH(time) >= 10 and DAYOFMONTH(time) >= 1)
or (YEAR(time) = 2012 and MONTH(time) <=9 and DAYOFMONTH(time) <=30)) ORDER by t
ime limit 5;
+-------+------------+---------------+
| id | time | orderno |
+-------+------------+---------------+
| 17004 | 2011-10-03 | JDA-SP-028-11 |
| 17003 | 2011-10-05 | 5OCT-11 TAN |
| 17032 | 2011-10-05 | |
| 16780 | 2011-10-05 | BWL-SP-039-11 |
| 16726 | 2011-10-06 | 104 |
+-------+------------+---------------+
取出不是NULL和'NO'(取消的ORDER)的所有ORDER订单。
mysql> select id, time, orderno from order1 where orderno != 'NULL' and orderno
!='NO';
+----+------------+---------------+
| id | time | orderno |
+----+------------+---------------+
| 18 | 2008-06-03 | GIM-SP-036-08 |
| 24 | 2008-07-16 | TNF-ST-016-08 |
| 25 | 2008-08-29 | GGL-SP-015-08 |
| 27 | 2008-09-17 | B2229 |
| 29 | 2008-07-19 | JTG-SP-015-08 |
+----+------------+---------------+
]]>
返回