MySQL操作程序三十六(Order summary网页程序1,整理1)

Construct a Hash from the Entire Lookup Table
*********************************************
使用Enum,or SET

# 形成HASH my %member; my $sth = $dbh->prepare("SELECT val FROM $tbl_name"); $sth->execute (); while (my ($val) = $sth->fetchrow_array()) { $member{$val} = 1; } # 然后使用 $valid = exists ($members{$val});

msc0.htm修改,增加ORDER SUMMARY按钮
modesum
msc.pm修改,增加mode
mscsum.pl==>显示程序
mscsum_cal.pl==>计算程序
enq1,enq2,quo2,order1表格
抽出子程序
C:\database\perl,mscsum_cal.pl,PERL程序试验
修改orderno时的对应
orderno是OURREF的抽出
追加orderno is not NULL
按年份排,10月1日到第二年的9月30日,WHERE time BETWEEN '2007-10-01' AND '2008-09-30';
受注金額(quo2),不包括packing charge/freight charge/delivery charge
仕入金額(enq2),日本厂家要加%5消费税=>mysql_test37.htm
仕入先(enq2)
仕入先NO.(enq2)
納期(enq2)
支払時期(enq2)=>追加支付日期的项目(paytime,date)=>mysql_test11.htm
納入先/時期(order1)=>读post项目
入金時期(order1)=>追加支付日期的项目(paytime,date),order画面,追加"入金時期"
新规生成order1时,注意写入paytime=>mscenq1_start.pl
作table sum的小程序,make_sum.pl,并插入
日期输出,'%y.%m.%d'
order画面,Date的手动修改
把mscsum_cal.pl计算结果写入sum table
把mscsum_cal.pl改造成在线程序
A072326A,部分ORDER的处理?
~~改造write_excel.pl,每次写入total~~
~~离线程序写入total~~

# mscsum_cal.pl use strict; use DBI; my(%t,@rec,$pref,$n,$n1,$n2); # 连接数据库 $$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; } $t{ym1} = "WHERE time BETWEEN '2007-10-01' AND '2008-09-30' AND orderno is not NULL ORDER BY time DESC"; $t{ym2} = "WHERE time BETWEEN '2008-10-01' AND '2009-09-30' AND orderno is not NULL ORDER BY time DESC"; $t{ym3} = "WHERE time BETWEEN '2009-10-01' AND '2010-09-30' AND orderno is not NULL ORDER BY time DESC"; $t{ym4} = "WHERE time BETWEEN '2010-10-01' AND '2011-09-30' AND orderno is not NULL ORDER BY time DESC"; @{ $t{ylist} } = qw/1 2 3 4/; print "Please input the year 2008=>1;2009=>2;2010=>3;2011=>4;select=>"; chop($t{y1}=<STDIN>); $t{yms1} = 'ym' . $t{y1}; if ( $t{$t{yms1}} ) { print "You selection $t{y1} exists!\n"; } else { print "You selection $t{y1} does not exist!\n"; exit; } # 处理order1, 取出所有ORDER的id和orderno $t{sth} = $$pref{dbh}->prepare("SELECT id,orderno FROM order1"); $t{sth}->execute; while ( @rec = $t{sth}->fetchrow_array ) { if ( $rec[1] =~ /\w/ ) { push(@{ $t{ids} },$rec[0]); } } $t{sth}->finish; # 处理order1, 取出指定年度的ORDER $t{sth} = $$pref{dbh}->prepare("SELECT id,date_format(time,'%y.%m.%d'),orderno,post,date_format(paytime,'%y.%m.%d') FROM order1 $t{$t{yms1}}"); $t{sth}->execute; while ( @rec = $t{sth}->fetchrow_array ) { if ( $rec[2] =~ /\w/ ) { push(@{ $t{id1} },$rec[0]); push(@{ $t{md1} },$rec[1]); push(@{ $t{orderno1} },$rec[2]); push(@{ $t{post1} },$rec[3]); push(@{ $t{paytime1} },$rec[4]); } } $t{sth}->finish; # 处理enq1,取出所有ids的our ref $t{ourrefs} = ''; for $n ( 0 .. $#{ $t{ids} } ) { ($t{ourref1}) = $$pref{dbh}->selectrow_array("SELECT ourref FROM enq1 WHERE id = $t{ids}[$n]"); $t{ourrefs} = $t{ourrefs} . ' ' . $t{ourref1}; } # 删除指定年度的ORDER的orderno是OURREF的ORDER for $n ( 0 .. $#{ $t{id1} } ) { if ( $t{ourrefs} =~ $t{orderno1}[$n] ) { # print $t{id1}[$n],'==>',$t{orderno1}[$n],'==>',$t{year1}[$n],"\n"; } else { push(@{ $t{id2} },$t{id1}[$n]); push(@{ $t{md2} },$t{md1}[$n]); push(@{ $t{orderno2} },$t{orderno1}[$n]); if ($t{post1}[$n]) { push(@{ $t{post2} },$t{post1}[$n]); } else { push(@{ $t{post2} },'NO'); } push(@{ $t{paytime2} },$t{paytime1}[$n]); } } # 处理指定年度的ORDER,计算价格和其他费用 for $n ( 0 .. $#{ $t{id2} } ) { $t{id} = $t{id2}[$n]; ($t{ourref1},$t{ownerid},$t{hullnoid}) = $$pref{dbh}->selectrow_array("SELECT ourref,owner,hullnoid FROM enq1 WHERE id = $t{id}"); # 船主 $t{owner1} = $$pref{dbh}->selectrow_array("SELECT company FROM owners WHERE id = $t{ownerid}"); @{ $t{owner1s} } = split(/\s/,$t{owner1}); $t{owner1} = $t{owner1s}[0] . ' ' . $t{owner1s}[1]; # 取两个单词 # 船名 $t{ship1} = $$pref{dbh}->selectrow_array("SELECT name FROM hull_no WHERE id = $t{hullnoid}"); # 注文番号 # 受注金額 ($t{QTY},$t{enq2s}) = $$pref{dbh}->selectrow_array("select QTY,enq2s from enq1 where id = $t{id}"); @{ $t{QT1} } = split(/==/,$t{QTY}); @{ $t{QTYs} } = (); for $n1 ( 0 .. $#{ $t{QT1} } ) { @{ $t{QT2} } = split(/=/,$t{QT1}[$n1]); push(@{ $t{QTYs} },@{ $t{QT2} }); } ($t{money1},$t{pp1}) = $$pref{dbh}->selectrow_array("SELECT money,price FROM quo2 WHERE id = $t{id}"); @{ $t{pps} } = split(/=/,$t{pp1}); $t{total1} = 0; for $n1 ( 0 .. $#{ $t{pps} } ) { $t{total1} = $t{total1} + $t{pps}[$n1]*$t{QTYs}[$n1]; } $t{disc} = $$pref{dbh}->selectrow_array("SELECT disc FROM order1 WHERE id = $t{id}"); $t{total1} = $t{total1} + $t{disc}; # 仕入先,仕入先NO. # 仕入れ金額 # 納期 # 支払時期 @{ $t{enq2} } = split(/=/,$t{enq2s}); @{ $t{makers} } = (); @{ $t{moneys} } = (); @{ $t{makerrefs} } = (); @{ $t{makerps} } = (); @{ $t{deliverys} } = (); @{ $t{time1s} } = (); for $n1 ( 0 .. $#{ $t{enq2} } ) { $t{enq21} = $t{enq2}[$n1]; ($t{time1},$t{makerid},$t{delivery},$t{QTY},$t{price},$t{money},$t{makerref}) = $$pref{dbh}->selectrow_array("SELECT date_format(time,'%y.%m.%d'),makerid,DELIVERY,QTY,price,money,makerref FROM enq2 WHERE id = $t{enq21}"); ($t{maker1},$t{nationid}) = $$pref{dbh}->selectrow_array("SELECT company,nationid FROM makers WHERE id = $t{makerid}"); @{ $t{maker1s} } = split(/\s/,$t{maker1}); $t{maker1} = $t{maker1s}[0] . ' ' . $t{maker1s}[1]; # 取两个单词 @{ $t{QT1} } = split(/==/,$t{QTY}); @{ $t{QTYs} } = (); for $n2 ( 0 .. $#{ $t{QT1} } ) { @{ $t{QT2} } = split(/=/,$t{QT1}[$n2]); push(@{ $t{QTYs} },@{ $t{QT2} }); } @{ $t{pps} } = split(/=/,$t{price}); $t{total2} = 0; for $n2 ( 0 .. $#{ $t{pps} } ) { $t{total2} = $t{total2} + $t{pps}[$n2]*$t{QTYs}[$n2]; } if ($t{nationid} == 1 ) { $t{total2} = $t{total2}*1.05; # 消费税 } push(@{ $t{makers} },$t{maker1}); push(@{ $t{moneys} },$t{money}); if ( $t{makerref} ) { push(@{ $t{makerrefs} },$t{makerref}); } else { push(@{ $t{makerrefs} },'NO'); } push(@{ $t{makerps} },$t{total1}); if ($t{delivery}) { push(@{ $t{deliverys} },$t{delivery}); } else { push(@{ $t{deliverys} },'NO'); } push(@{ $t{time1s} },$t{time1}); } $t{makers1} = join('=',@{ $t{makers} }); $t{moneys1} = join('=',@{ $t{moneys} }); $t{makerrefs1} = join('=',@{ $t{makerrefs} }); $t{makerps1} = join('=',@{ $t{makerps} }); $t{deliverys1} = join('=',@{ $t{deliverys} }); $t{time1s1} = join('=',@{ $t{time1s} }); # 納入先/納入時期 $t{post21} = $t{post2}[$n]; # 入金時期 $t{paytime21} = $t{paytime2}[$n]; # 放入配列 push(@{ $t{stime} },$t{md2}[$n]); push(@{ $t{sref} },$t{ourref1}); push(@{ $t{sowner} },$t{owner1}); push(@{ $t{sship} },$t{ship1}); push(@{ $t{sorderno} },$t{orderno2}[$n]); push(@{ $t{sordermoneyid} },$t{money1}); push(@{ $t{sordermoney} },$t{total1}); push(@{ $t{spost} },$t{post2}[$n]); push(@{ $t{spaytime} },$t{paytime2}[$n]); push(@{ $t{senq2name} },$t{makers1}); push(@{ $t{senq2moneyid} },$t{moneys1}); push(@{ $t{senq2no} },$t{makerrefs1}); push(@{ $t{senq2money} },$t{makerps1}); push(@{ $t{senq2delivery} },$t{deliverys1}); push(@{ $t{senq2pay} },$t{time1s1}); # print "$t{md2}[$n]=>$t{ourref1}/$t{owner1}=>$t{ship1}=>$t{money1}=>$t{paytime21}\n"; # print "****$t{orderno2}[$n]*****$t{total1}"; # print "$t{ourref1}=>@{ $t{enq2} }=>@{ $t{makerps} },@{ $t{deliverys} },@{ $t{time1s} }\n"; } $t{stime1} = join('==',@{ $t{stime} }); $t{sref1} = join('==',@{ $t{sref} }); $t{sowner1} = join('==',@{ $t{sowner} }); $t{sship1} = join('==',@{ $t{sship} }); $t{sorderno1} = join('==',@{ $t{sorderno} }); $t{sordermoneyid1} = join('==',@{ $t{sordermoneyid} }); $t{sordermoney1} = join('==',@{ $t{sordermoney} }); $t{spost1} = join('==',@{ $t{spost} }); $t{spaytime1} = join('==',@{ $t{spaytime} }); $t{senq2name1} = join('==',@{ $t{senq2name} }); $t{senq2moneyid1} = join('==',@{ $t{senq2moneyid} }); $t{senq2money1} = join('==',@{ $t{senq2money} }); $t{senq2delivery1} = join('==',@{ $t{senq2delivery} }); $t{senq2no1} = join('==',@{ $t{senq2no} }); $t{senq2pay1} = join('==',@{ $t{senq2pay} }); $t{sql} = 'UPDATE sum SET time = "' . $t{stime1} . '" WHERE id = ' . $t{y1}; #print "sql=$t{sql}\n"; $t{DO} = $$pref{dbh}->do($t{sql}); print "time=>$t{DO}\n"; $t{sql} = 'UPDATE sum SET ref = "' . $t{sref1} . '" WHERE id = ' . $t{y1}; #print "sql=$t{sql}\n"; $t{DO} = $$pref{dbh}->do($t{sql}); print "ref=>$t{DO}\n"; $t{sql} = 'UPDATE sum SET owner = "' . $t{sowner1} . '" WHERE id = ' . $t{y1}; #print "sql=$t{sql}\n"; $t{DO} = $$pref{dbh}->do($t{sql}); print "owner=>$t{DO}\n"; $t{sql} = 'UPDATE sum SET shipname = "' . $t{sship1} . '" WHERE id = ' . $t{y1}; #print "sql=$t{sql}\n"; $t{DO} = $$pref{dbh}->do($t{sql}); print "shipname=>$t{DO}\n"; $t{sql} = 'UPDATE sum SET orderno = "' . $t{sorderno1} . '" WHERE id = ' . $t{y1}; #print "sql=$t{sql}\n"; $t{DO} = $$pref{dbh}->do($t{sql}); print "orderno=>$t{DO}\n"; $t{sql} = 'UPDATE sum SET ordermoneyid = "' . $t{sordermoneyid1} . '" WHERE id = ' . $t{y1}; #print "sql=$t{sql}\n"; $t{DO} = $$pref{dbh}->do($t{sql}); print "ordermoneyid=>$t{DO}\n"; $t{sql} = 'UPDATE sum SET ordermoney = "' . $t{sordermoney1} . '" WHERE id = ' . $t{y1}; #print "sql=$t{sql}\n"; $t{DO} = $$pref{dbh}->do($t{sql}); print "ordermoney=>$t{DO}\n"; $t{sql} = 'UPDATE sum SET enq2name = "' . $t{senq2name1} . '" WHERE id = ' . $t{y1}; #print "sql=$t{sql}\n"; $t{DO} = $$pref{dbh}->do($t{sql}); print "enq2name=>$t{DO}\n"; $t{sql} = 'UPDATE sum SET enq2moneyid = "' . $t{senq2moneyid1} . '" WHERE id = ' . $t{y1}; #print "sql=$t{sql}\n"; $t{DO} = $$pref{dbh}->do($t{sql}); print "enq2moenyid=>$t{DO}\n"; $t{sql} = 'UPDATE sum SET enq2money = "' . $t{senq2money1} . '" WHERE id = ' . $t{y1}; #print "sql=$t{sql}\n"; $t{DO} = $$pref{dbh}->do($t{sql}); print "enq2money=>$t{DO}\n"; $t{sql} = 'UPDATE sum SET enq2no = "' . $t{senq2no1} . '" WHERE id = ' . $t{y1}; #print "sql=$t{sql}\n"; $t{DO} = $$pref{dbh}->do($t{sql}); print "enq2no=>$t{DO}\n"; $t{sql} = 'UPDATE sum SET enq2delivery = "' . $t{senq2delivery1} . '" WHERE id = ' . $t{y1}; #print "sql=$t{sql}\n"; $t{DO} = $$pref{dbh}->do($t{sql}); print "enq2delivery=>$t{DO}\n"; $t{sql} = 'UPDATE sum SET enq2pay = "' . $t{senq2pay1} . '" WHERE id = ' . $t{y1}; #print "sql=$t{sql}\n"; $t{DO} = $$pref{dbh}->do($t{sql}); print "enq2pay=>$t{DO}\n"; $t{sql} = 'UPDATE sum SET post = "' . $t{spost1} . '" WHERE id = ' . $t{y1}; #print "sql=$t{sql}\n"; $t{DO} = $$pref{dbh}->do($t{sql}); print "post=>$t{DO}\n"; $t{sql} = 'UPDATE sum SET paytime = "' . $t{spaytime1} . '" WHERE id = ' . $t{y1}; #print "sql=$t{sql}\n"; $t{DO} = $$pref{dbh}->do($t{sql}); print "paytime=>$t{DO}\n"; # 关闭数据库 $$pref{dbh}->disconnect; print "Finished.\n";

写入未成功一览 enq2name enq2money enq2no enq2delivery post $t{sql} = 'UPDATE sum SET enq2money = "' . $t{senq2money1} . '" WHERE id = ' . $t{y1}; print "sql=$t{sql}\n"; $t{DO} = $$pref{dbh}->do($t{sql}); DBD::mysql::db do failed: Got error 139 from storage engine at mscsum_cal.pl lin e 246, <STDIN> line 1. mysql> ALTER TABLE sum ENGINE=MyISAM; Query OK, 4 rows affected (0.53 sec) Records: 4 Duplicates: 0 Warnings: 0 MyISAMの場合、行サイズの上限は64KBで、かつTEXTやBLOBの保存に要するのは9〜12バイトなので、同じ問題はまず起こらない。

==取出零件单价 if ( $t{excel_pat} eq 'quo2' or $t{excel_pat} eq 'order1' or $t{excel_pat} eq 'shipping' or $t{excel_pat} eq 'invoice' ) { $t{pp1} = $self->dbh->selectrow_array("SELECT price FROM quo2 WHERE id = $t{enq1_id}"); @{ $t{pps} } = split(/=/,$t{pp1}); } elsif ( $t{excel_pat} eq 'enq2_order') { $t{pp1} = $self->dbh->selectrow_array("SELECT price FROM enq2 WHERE id = $t{enq2_id}"); @{ $t{pps} } = split(/=/,$t{pp1}); } ==取出零件数量 ($t{type1id},$t{partsid},$t{QTY}) = $self->dbh->selectrow_array("select type1id, partsid,QTY from enq2 where id = $t{enq2_id}"); ($t{type1id},$t{partsid},$t{QTY},$t{seriesid}) = $self->dbh->selectrow_array("select type1id, partsid,QTY,seriesid from enq1 where id = $t{enq1_id}"); @{ $t{QT1} } = split(/==/,$t{QTY}); $t{QTY2} = $t{QT1}[$n]; @{ $t{Q_list} } = split(/=/,$t{QTY2}); $t{Q1} = $t{Q_list}[$n1]; $t{Q1} = $t{plist}{QTY}{$t{dwg1}}[$n2]; ==计算零件总价 $t{pri1} = $t{pps}[$t{NO}-1]; $t{pris} = $t{Q1}*$t{pri1}; $t{total1} += $t{pris}; ==计算其他费用 $t{disc} = $self->dbh->selectrow_array("SELECT disc FROM order1 WHERE id = $t{enq1_id}"); $t{packing_charge} = $self->dbh->selectrow_array("SELECT packing_charge FROM order1 WHERE id = $t{enq1_id}"); $t{freight_charges} = $self->dbh->selectrow_array("SELECT freight_charges FROM order1 WHERE id = $t{enq1_id}"); $t{delivery_charge} = $self->dbh->selectrow_array("SELECT delivery_charge FROM order1 WHERE id = $t{enq1_id}"); $t{delivery_place} = $self->dbh->selectrow_array("SELECT delivery_place FROM order1 WHERE id = $t{enq1_id}"); $t{total1} = $t{total1} + $t{disc} + $t{packing_charge} + $t{freight_charges} + $t{delivery_charge};

# make_sum.pl # 生成sum表格 use strict; use DBI; my(%t,$n,@fld,$pref,@rec); # 连接数据库 $$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; } $t{sql} = 'DROP TABLE IF EXISTS sum;'; $$pref{dbh}->do($t{sql}); $t{sql} = 'CREATE TABLE sum'; $t{sql} .= ' ('; $t{sql} .= ' id INT AUTO_INCREMENT,'; $t{sql} .= ' year INT,'; $t{sql} .= ' time text,'; #時期 $t{sql} .= ' ref text,'; #REF.NO. $t{sql} .= ' owner text,'; #船主 $t{sql} .= ' shipname text,'; #船名 $t{sql} .= ' orderno text,'; #注文番号 $t{sql} .= ' ordermoneyid text,'; #货币种类 $t{sql} .= ' ordermoney text,'; #受注金額 $t{sql} .= ' enq2name text,'; #仕入先 $t{sql} .= ' enq2moneyid text,'; #货币种类 $t{sql} .= ' enq2money text,'; #仕入れ金額 $t{sql} .= ' enq2no text,'; #仕入先NO. $t{sql} .= ' enq2delivery text,'; #納期 $t{sql} .= ' enq2pay text,'; #支払時期 $t{sql} .= ' post text,'; #納入先/納入時期 $t{sql} .= ' paytime text,'; #入金時期 $t{sql} .= ' PRIMARY KEY (id)'; $t{sql} .= ' );'; $$pref{dbh}->do($t{sql}); @{ $t{ylist} } = qw/2008 2009 2010 2011 2012 2013/; for $n ( 0 .. 3 ) { $t{sql} = "INSERT INTO sum (year) VALUES($t{ylist}[$n])"; $$pref{dbh}->do($t{sql}); } # 关闭数据库 $$pref{dbh}->disconnect; print "The table sum is created!\n";