MySQL操作程序五十三(船东管理,mscowner_start1.pl,mscown_star1.htm)
返回
- 未出荷時的不要进入入金一覧顺序排列,新设计增加HTML输出文件
- 入金時間前记入金额
<![CDATA[
记入金额参考mscsummary02.pl, 取total和quo2的moneyid
=========================================================================
$t{moneyid} = $self->dbh->selectrow_array("SELECT money FROM quo2 WHERE id = $t{id1}");
$t{money0} = $self->dbh->selectrow_array("SELECT English FROM money WHERE id = $t{moneyid}");
$t{total1} = commify($t{total1});
=========================================================================
mscowner1.pl的网页指定!
$t{line1} = $t{line1} . '<input type="hidden" name="tmpl" value="mscowner_arrival">';
$t{line1} = $t{line1} . '<input type="hidden" name="tmpl" value="mscowner_money">';
mscowner_man.pl
} elsif ( $t{pat2} eq 'nopost' or $t{pat2} eq 'nopay' or $t{pat2} eq 'afterpost' or $t{pat2} eq 'afterpay') {
改为
if ( $t{pat2} eq 'nopost' or $t{pat2} 'afterpost' ) {
# html ==>mscowner_arrival.htm
} elsif ( $t{pat2} eq 'nopay' or $t{pat2} eq 'afterpay') {
# html ==>mscowner_money.htm
}
新设计, 把出荷和入金完全分开!
---------------------------------------------------
pat2 html
未出荷订单一览 nopost mscowner_arrival.htm,显示未出荷订单,取消
出荷済み一览 afterpost mscowner_arrival.htm,显示出荷済み订单
未入金订单一览 nopay mscowner_money.htm,只显示出荷済み订单(未出荷订单不显示),增加计算金额显示/计算按钮,EXCEL输出
入金済み一览 afterpay mscowner_money.htm,显示入金済み订单,增加计算金额
现状
---------------------------------------------------
pat2 html
未出荷订单一览 nopost mscowner_arrival.htm
出荷済み一览 afterpost mscowner_arrival.htm
未入金订单一览 nopay mscowner_arrival.htm
入金済み一览 afterpay mscowner_arrival.htm
]]>
- total项目的写入(现在是mscwrite_excel.pl临时计算)
- 统计项目的EXCEL输出功能
- ourref修改owner时,联动修改owners的项目
- 增加ourref时自动地增加owners的项目
- 客户管理(mscowner1.pl,mscowner1.htm)
分别复制mscown_start.pl,mscown_start.htm,然后修改
现在是一个担当者一行,同一个公司有复数担当者的话,就有复数行的情况下.改为一个公司一行(顾客代号相同)
- PERL给owners的ourref赋值的离线程序,/database/perl/in2ourref.pl
ship,money的日期也要写!
- owners的TABLE修改,order1的几个时间整理
time:order的时间
posttime:出荷
paytime:入金
arrivaltime:
invoicetime:
- ************************************************
- owners生成时,项目的增加,msc244.pl
- owners修改时,项目的增加,msc241.pl
- 增加owners_area表格
- 按area分类的网页(mscown_start1.pl,mscown_start1.htm)
- PERL给owners的area项目赋值的离线程序
- owners的项目area的DEFAULT设为9
- owners增加money项目(TEXT),记入每一个ourref的入金状态
1:未入金
2:部分入金
3:全部入金
- owners增加ship项目(TEXT),记入每一个ourref的出荷状态
1:ENQ
2:ORDER
3:部分出荷
4:全部出荷
5:取消
注意在ORDER时重写states
- owners增加ourref项目(TEXT),把跟该owner有关的ourref全部写上
注意在换owners时同时重写ourref
- owners增加area项目(INT),分区,(台湾:1;香港:2;中国:3;欧州:4;日本:5;韩国:6;中东:7;东南亚:8;其他;9)
用程序写入area编号(1次操作)
个别修改
- 船东(owners)为中心进行操作。
<![CDATA[
# 取出数据
$t{sth} = $self->dbh->prepare("SELECT id,company,name1,area FROM owners ORDER BY name1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{owners1} = $rec[0];
$t{company1} = $rec[1];
$t{name1} = $rec[2];
$t{area1} = $rec[3];
if ( $t{area1} == $t{mode} ) {
push(@{ $t{owners} },$t{owners1});
push(@{ $t{companies} },$t{company1});
push(@{ $t{names} },$t{name1});
push(@{ $t{noss} },$t{area1});
}
}
for $n ( 0 .. $#{ $t{owners} } ) {
$t{owners1} = $t{owners}[$n];
$t{company1} = $t{companies}[$n];
$t{name1} = $t{names}[$n];
my %row = (
area_id => $t{mode},
owner_id => $t{owners1},
owner_company => $t{company1},
owner_name => $t{name1}
);
push(@loop, \%row);
}
修改如下:
有复数个name1相同的话,loop的一行合并,但是每个owner的要保留,owner1.htm的company改为担当者较好
-------------------------------------------------------
owners的部分ID不存在?
-----------------------------------------
187 is NG, and length is 1
213 is NG, and length is 38
245 is NG, and length is 3
308 is NG, and length is 1
310 is NG, and length is 1
mysql> show columns from owners_area;
+---------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| Chinese | char(30) | YES | | NULL | |
+---------+----------+------+-----+---------+----------------+
2 rows in set (0.11 sec)
c:\database\sql>mysql -u cbuser -p cookbook < owners_area.sql
Enter password: ******
owners_area.sql
---------------------------------------
DROP TABLE IF EXISTS owners_area;
CREATE TABLE owners_area
(
id INT AUTO_INCREMENT,
Chinese char(30),
PRIMARY KEY (id)
);
INSERT INTO owners_area (Chinese) VALUES("台湾");
INSERT INTO owners_area (Chinese) VALUES("香港");
INSERT INTO owners_area (Chinese) VALUES("中国");
INSERT INTO owners_area (Chinese) VALUES("欧州");
INSERT INTO owners_area (Chinese) VALUES("日本");
INSERT INTO owners_area (Chinese) VALUES("韩国");
INSERT INTO owners_area (Chinese) VALUES("中东");
INSERT INTO owners_area (Chinese) VALUES("东南亚");
INSERT INTO owners_area (Chinese) VALUES("其他");
]]>
<![CDATA[
PERL给owners的area项目赋值的离线程序
1.取出area项目,作成arealist.txt, obtainarealist.pl
2.手动修改arealist.txt
3.把arealist.txt代入owners, insertarealist.pl
#1. obtainarealist.pl
#-------------------------------------------------------------------
use strict;
use DBI;
my ( %t, $n, @fld, @rec, @listid,@listar,@listco);
$t{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$t{dbh} = DBI->connect($t{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$t{dbh}->do("SET NAMES sjis");
if(!$t{dbh}){
print "SQL read ERROR!\n";
exit;
}
$t{sth} = $t{dbh}->prepare("SELECT * FROM owners");
$t{sth}->execute;
while (@rec = $t{sth}->fetchrow_array) {
push(@listid,$rec[0]);
push(@listar,$rec[9]);
push(@listco,$rec[1]);
}
$t{sth}->finish;
$t{dbh}->disconnect;
# 出力
$t{file} = 'arealist.txt';
open(OUT,">../txt/$t{file}");
print OUT "Filename=$t{file}\n";
for $n ( 0 .. $#listid ) {
print OUT 'LIST==>';
print OUT $listid[$n],'==>';
if ( $listar[$n] ) {
print OUT $listar[$n],'==>';
} else {
print OUT '9==>';
}
print OUT $listco[$n],"\n";
}
close(OUT);
__END__
#3 insertarealist.pl
#-------------------------------------------------------------------
use strict;
use DBI;
my(%t,$n,$n1,@fld,@listid,@listar,$pref);
# open arealist.txt文件
open(IN,"../txt/arealist.txt") or die "Can't open the file arealist.txt\n";
while(<IN>){
@fld = split(/==>/);
if ( $fld[0] eq 'LIST' ) {
push(@listid,$fld[1]);
push(@listar,$fld[2]);
}
}
close(IN);
# 连接数据库
$$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;
}
for $n ( 0 .. $#listid ) {
$t{id} = $listid[$n];
$t{area} = $listar[$n];
$t{sql} = 'UPDATE owners SET area = "';
$t{sql} .= $t{area} . '" WHERE id = "' . $t{id} . '"';
# print "sql=$t{sql}\n";
$t{DO} = $$pref{dbh}->do($t{sql});
if ( $t{DO} == 0 ) {
print "$n==>$t{DO}\n";
print "sql==>$t{sql}\n";
exit;
} else {
print "$t{id}==>OK\n";
}
}
# 关闭数据库
$$pref{dbh}->disconnect;
确认=>
mysql> select area from owners where id < 20;
+------+
| area |
+------+
| 1 |
| 1 |
| 1 |
| 3 |
| 1 |
| 1 |
| 1 |
| 1 |
| 3 |
| 4 |
| 2 |
| 4 |
| 9 |
| 9 |
| 9 |
| 9 |
| 9 |
| 9 |
| 9 |
+------+
]]>
<![CDATA[
mysql> ALTER TABLE owners ADD area INT;
Query OK, 333 rows affected (1.95 sec)
Records: 333 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE owners ADD ourref TEXT;
Query OK, 333 rows affected (0.38 sec)
Records: 333 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE owners ADD ship TEXT;
Query OK, 333 rows affected (0.27 sec)
Records: 333 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE owners ADD money TEXT;
Query OK, 333 rows affected (0.44 sec)
Records: 333 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE owners ALTER area SET DEFAULT 9;
Query OK, 0 rows affected (0.72 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM owners;
+----------+-----------+------+-----+---------+----------------+
| 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(100) | YES | | NULL | |
| area | int(11) | YES | | 9 | |
| ourref | text | YES | | NULL | |
| ship | text | YES | | NULL | |
| money | text | YES | | NULL | |
+----------+-----------+------+-----+---------+----------------+
13 rows in set (0.13 sec)
mysql> SHOW COLUMNS FROM owners;
+----------+-----------+------+-----+---------+----------------+
| 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(100) | YES | | NULL | |
| area | int(11) | YES | | NULL | |
| ourref | text | YES | | NULL | |
| ship | text | YES | | NULL | |
| money | text | YES | | NULL | |
+----------+-----------+------+-----+---------+----------------+
13 rows in set (0.08 sec)
mysql> show columns from owners;
+----------+-----------+------+-----+---------+----------------+
| 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(100) | YES | | NULL | |
+----------+-----------+------+-----+---------+----------------+
9 rows in set (1.19 sec)
]]>
返回