MySQL操作程序五十三(船東管理,mscowner_start1.pl,mscown_star1.htm)
返回
- 幾張訂單的合計(jì)功能
- 未入金一覧のexcel輸出
- 未出荷時(shí)的不要進(jìn)入入金一覧順序排列,新設(shè)計(jì)增加HTML輸出文件
- 入金時(shí)間前記入金額
<![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的網(wǎng)頁(yè)指定!
$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
}
新設(shè)計(jì), 把出荷和入金完全分開!
---------------------------------------------------
pat2 html
未出荷訂單一覽 nopost mscowner_arrival.htm,顯示未出荷訂單,取消
出荷済み一覽 afterpost mscowner_arrival.htm,顯示出荷済み訂單
未入金訂單一覽 nopay mscowner_money.htm,只顯示出荷済み訂單(未出荷訂單不顯示),增加計(jì)算金額顯示/計(jì)算按鈕,EXCEL輸出
入金済み一覽 afterpay mscowner_money.htm,顯示入金済み訂單,增加計(jì)算金額
現(xiàn)狀
---------------------------------------------------
pat2 html
未出荷訂單一覽 nopost mscowner_arrival.htm
出荷済み一覽 afterpost mscowner_arrival.htm
未入金訂單一覽 nopay mscowner_arrival.htm
入金済み一覽 afterpay mscowner_arrival.htm
]]>
- total項(xiàng)目的寫入(現(xiàn)在是mscwrite_excel.pl臨時(shí)計(jì)算)
- 統(tǒng)計(jì)項(xiàng)目的EXCEL輸出功能
- ourref修改owner時(shí),聯(lián)動(dòng)修改owners的項(xiàng)目
- 增加ourref時(shí)自動(dòng)地增加owners的項(xiàng)目
- 客戶管理(mscowner1.pl,mscowner1.htm)
分別復(fù)制mscown_start.pl,mscown_start.htm,然后修改
現(xiàn)在是一個(gè)擔(dān)當(dāng)者一行,同一個(gè)公司有復(fù)數(shù)擔(dān)當(dāng)者的話,就有復(fù)數(shù)行的情況下.改為一個(gè)公司一行(顧客代號(hào)相同)
- PERL給owners的ourref賦值的離線程序,/database/perl/in2ourref.pl
ship,money的日期也要寫!
- owners的TABLE修改,order1的幾個(gè)時(shí)間整理
time:order的時(shí)間
posttime:出荷
paytime:入金
arrivaltime:
invoicetime:
- ************************************************
- owners生成時(shí),項(xiàng)目的增加,msc244.pl
- owners修改時(shí),項(xiàng)目的增加,msc241.pl
- 增加owners_area表格
- 按area分類的網(wǎng)頁(yè)(mscown_start1.pl,mscown_start1.htm)
- PERL給owners的area項(xiàng)目賦值的離線程序
- owners的項(xiàng)目area的DEFAULT設(shè)為9
- owners增加money項(xiàng)目(TEXT),記入每一個(gè)ourref的入金狀態(tài)
1:未入金
2:部分入金
3:全部入金
- owners增加ship項(xiàng)目(TEXT),記入每一個(gè)ourref的出荷狀態(tài)
1:ENQ
2:ORDER
3:部分出荷
4:全部出荷
5:取消
注意在ORDER時(shí)重寫states
- owners增加ourref項(xiàng)目(TEXT),把跟該owner有關(guān)的ourref全部寫上
注意在換owners時(shí)同時(shí)重寫ourref
- owners增加area項(xiàng)目(INT),分區(qū),(臺(tái)灣:1;香港:2;中國(guó):3;歐州:4;日本:5;韓國(guó):6;中東:7;東南亞:8;其他;9)
用程序?qū)懭隺rea編號(hào)(1次操作)
個(gè)別修改
- 船東(owners)為中心進(jìn)行操作。
<![CDATA[
# 取出數(shù)據(jù)
$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);
}
修改如下:
有復(fù)數(shù)個(gè)name1相同的話,loop的一行合并,但是每個(gè)owner的要保留,owner1.htm的company改為擔(dān)當(dāng)者較好
-------------------------------------------------------
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("臺(tái)灣");
INSERT INTO owners_area (Chinese) VALUES("香港");
INSERT INTO owners_area (Chinese) VALUES("中國(guó)");
INSERT INTO owners_area (Chinese) VALUES("歐州");
INSERT INTO owners_area (Chinese) VALUES("日本");
INSERT INTO owners_area (Chinese) VALUES("韓國(guó)");
INSERT INTO owners_area (Chinese) VALUES("中東");
INSERT INTO owners_area (Chinese) VALUES("東南亞");
INSERT INTO owners_area (Chinese) VALUES("其他");
]]>
<![CDATA[
PERL給owners的area項(xiàng)目賦值的離線程序
1.取出area項(xiàng)目,作成arealist.txt, obtainarealist.pl
2.手動(dòng)修改arealist.txt
3.把a(bǔ)realist.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);
# 連接數(shù)據(jù)庫(kù)
$$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";
}
}
# 關(guān)閉數(shù)據(jù)庫(kù)
$$pref{dbh}->disconnect;
確認(rèn)=>
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)
]]>
返回
常宁市|
大方县|
香港|
望谟县|
哈尔滨市|
新源县|
行唐县|
自贡市|
桦川县|
丹凤县|
上犹县|
天等县|
西乌|
丽水市|
青川县|
湄潭县|
航空|
宁城县|
江都市|
横山县|
盐津县|
惠水县|
迁安市|
容城县|
会昌县|
隆安县|
阿鲁科尔沁旗|
华蓥市|
祁阳县|
资源县|
天长市|
维西|
镇安县|
本溪市|
青海省|
宿迁市|
襄城县|
安康市|
元氏县|
沈阳市|
遵化市|