MySQL操作程序四十二(mscowner1.pl,mscowner_man.pl,mscorder_man.pl)
返回
<![CDATA[
mscowner_arrival.htm的最右侧增加OURREF显示
增加如下code
# line_id
$t{line_id} = '<td rowspan=';
$t{line_id} = $t{line_id} . $t{enq2leng} . '>';
$t{line_id} = $t{line_id} . '<form action="" method="post"><input type="submit" value="ORDER">';
$t{line_id} = $t{line_id} . $t{ourref1} . '<input type="hidden" name="id" value="';
$t{line_id} = $t{line_id} . $t{id1} . '"><input type="hidden" name="pat" value="NO">';
$t{line_id} = $t{line_id} . '<input type="hidden" name="pat2" value="' . $t{pat2} . '">';
$t{line_id} = $t{line_id} . '<input type="hidden" name="rm" value="modeorder1"></form></td></tr>';
]]>
<![CDATA[
INVOICE的时间修改==>自动生成(EXCEL写入时)
==>2011年以后的订单!
AND YEAR(order1.time) > '2011'
mscowner_man.pl的修改注意!
$t{sth} = $self->dbh->prepare("SELECT order1.time,order1.orderno,order1.post,order1.posttime,order1.paytime,order1.paymemo,order1.invoicetime,enq1.id,enq1.ourref,enq1.owner,enq1.hullnoid,enq1.enq2s FROM order1,enq1
# $t{sth} = $self->dbh->prepare("SELECT order1.time,order1.orderno,order1.post,order1.posttime,order1.paytime,order1.paymemo,enq1.id,enq1.ourref,enq1.owner,enq1.hullnoid FROM order1,enq1
mscowner1.pl的起动画面
mscowner_finish.htm
==>归并到
mscowner_arrival.htm
要找到给invoicetime赋值的程序,修改相应位置!
mysql> select id, time, invoicetime from order1 where invoicetime != '2030-01-01
' AND YEAR(invoicetime) > 2011 LIMIT 10;
+-------+------------+-------------+
| id | time | invoicetime |
+-------+------------+-------------+
| 9437 | 2010-07-20 | 2012-01-18 |
| 10756 | 2011-12-26 | 2012-05-03 |
| 10793 | 2012-04-02 | 2012-06-11 |
| 13566 | 2011-05-04 | 2012-01-23 |
| 13567 | 2011-05-04 | 2012-01-23 |
| 13568 | 2011-05-04 | 2012-01-23 |
| 13570 | 2011-05-04 | 2012-01-23 |
| 14657 | 2011-06-13 | 2012-04-26 |
| 14883 | 2012-04-03 | 2012-05-24 |
| 14901 | 2012-04-03 | 2012-05-23 |
+-------+------------+-------------+
10 rows in set (0.02 sec)
]]>
- 可以生成催款EXCEL一览表
- 开发mscowner_man.pl
- 将来放弃mscorder_man.pl,现暂存供参考
<![CDATA[
それぞれの定義
出荷:全部出荷的列表; posttime
==>invoicetime
未出荷:含部分出荷的情况、写在出荷メモ(post)
入金:全部入金的情况,设有一定时期以前不显示的功能(如两年); paytime
未入金:含部分入金的情况、写在入金メモ(paymemo)
出荷済み(owner_finish.htm)=>order1.htm,
然后回到owner_finish.htm(现在是order_finish.htm)
<form action="" method="post">
<input type="submit" value="客户管理(未入金)へ"><p>
<input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">">
<input type="hidden" name="tmpl" value="mscorder_arrival">
<input type="hidden" name="pat" value="NO">
<input type="hidden" name="pat2" value="nopay">
<input type="hidden" name="rm" value="modeorder_man">
</form>
<form action="" method="post">
<input type="submit" value="客户管理(入金済み)へ"><p>
<input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">">
<input type="hidden" name="tmpl" value="mscorder_finish">
<input type="hidden" name="pat" value="NO">
<input type="hidden" name="pat2" value="afterpay">
<input type="hidden" name="rm" value="modeorder_man">
</form>
==>改为
<form action="" method="post">
<input type="submit" value="客户管理(未入金)へ"><p>
<input type="hidden" name="area_id" value="<TMPL_VAR NAME="area_id">">
<input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">">
<input type="hidden" name="tmpl" value="mscowner_arrival">
<input type="hidden" name="pat" value="NO">
<input type="hidden" name="pat2" value="nopay">
<input type="hidden" name="rm" value="modeowner_man">
</form>
<form action="" method="post">
<input type="submit" value="客户管理(入金済み)へ"><p>
<input type="hidden" name="area_id" value="<TMPL_VAR NAME="area_id">">
<input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">">
<input type="hidden" name="tmpl" value="mscowner_finish">
<input type="hidden" name="pat" value="NO">
<input type="hidden" name="pat2" value="afterpay">
<input type="hidden" name="rm" value="modeowner_man">
</form>
<form action="" method="post">
<input type="submit" value="客户管理(未出荷)へ"><p>
<input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">">
<input type="hidden" name="tmpl" value="mscorder_arrival">
<input type="hidden" name="pat" value="NO">
<input type="hidden" name="pat2" value="nopost">
<input type="hidden" name="rm" value="modeorder_man">
</form>
==>改为
<form action="" method="post">
<input type="submit" value="客户管理(未出荷)へ"><p>
<input type="hidden" name="area_id" value="<TMPL_VAR NAME="area_id">">
<input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">">
<input type="hidden" name="tmpl" value="mscowner_arrival">
<input type="hidden" name="pat" value="NO">
<input type="hidden" name="pat2" value="nopost">
<input type="hidden" name="rm" value="modeowner_man">
</form>
<form action="" method="post">
<input type="submit" value="客户管理(出荷済み)へ"><p>
<input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">">
<input type="hidden" name="tmpl" value="mscorder_finish">
<input type="hidden" name="pat" value="NO">
<input type="hidden" name="pat2" value="afterpost">
<input type="hidden" name="rm" value="modeorder_man">
</form>
==>改为
<form action="" method="post">
<input type="submit" value="客户管理(出荷済み)へ"><p>
<input type="hidden" name="area_id" value="<TMPL_VAR NAME="area_id">">
<input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">">
<input type="hidden" name="tmpl" value="mscowner_finish">
<input type="hidden" name="pat" value="NO">
<input type="hidden" name="pat2" value="afterpost">
<input type="hidden" name="rm" value="modeowner_man">
</form>
mscorder1.pl,增加area_id
$t{area_id} = $self->dbh->selectrow_array("SELECT area FROM owners WHERE id = $t{owner_id}");
$t{template}->param(area_id => $t{area_id});
客户管理的company长度限制
客户管理按顾客代号排队=>完
================================================================
mscown_start.htm
<form action="" method="post">
<input type="submit" value="未入金">
<input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">">
<input type="hidden" name="tmpl" value="mscorder_arrival">
<input type="hidden" name="pat" value="NO">
<input type="hidden" name="pat2" value="nopay">
<input type="hidden" name="rm" value="modeorder_man">
</form>
==>
mscowner1.htm
<form action="" method="post">
<input type="submit" value="未入金">
<input type="hidden" name="area_id" value="<TMPL_VAR NAME="area_id">">
<input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">">
<input type="hidden" name="tmpl" value="mscowner_arrival">
<input type="hidden" name="pat" value="NO">
<input type="hidden" name="pat2" value="nopay">
<input type="hidden" name="rm" value="modeowner_man">
</form>
htm不要? 和未出荷一样?
================================================================
mscown_start.htm
<form action="" method="post">
<input type="submit" value="入金">
<input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">">
<input type="hidden" name="tmpl" value="mscorder_finish">
<input type="hidden" name="pat" value="NO">
<input type="hidden" name="pat2" value="afterpay">
<input type="hidden" name="rm" value="modeorder_man">
</form>
==>
mscowner1.htm
<form action="" method="post">
<input type="submit" value="入金">
<input type="hidden" name="area_id" value="<TMPL_VAR NAME="area_id">">
<input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">">
<input type="hidden" name="tmpl" value="mscowner_finish">
<input type="hidden" name="pat" value="NO">
<input type="hidden" name="pat2" value="afterpay">
<input type="hidden" name="rm" value="modeowner_man">
</form>
htm不要? 和出荷一样?
================================================================
mscown_start.htm
<form action="" method="post">
<input type="submit" value="未出荷">
<input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">">
<input type="hidden" name="tmpl" value="mscorder_arrival">
<input type="hidden" name="pat" value="NO">
<input type="hidden" name="pat2" value="nopost">
<input type="hidden" name="rm" value="modeorder_man">
</form>
==>
mscowner1.htm
<form action="" method="post">
<input type="submit" value="未出荷">
<input type="hidden" name="area_id" value="<TMPL_VAR NAME="area_id">">
<input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">">
<input type="hidden" name="tmpl" value="mscowner_arrival">
<input type="hidden" name="pat" value="NO">
<input type="hidden" name="pat2" value="nopost">
<input type="hidden" name="rm" value="modeowner_man">
</form>
复写mscorder_arrival=>mscowner_arrival.htm
================================================================
mscown_start.htm
<form action="" method="post">
<input type="submit" value="出荷">
<input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">">
<input type="hidden" name="tmpl" value="mscorder_finish">
<input type="hidden" name="pat" value="NO">
<input type="hidden" name="pat2" value="afterpost">
<input type="hidden" name="rm" value="modeorder_man">
</form>
==>
mscowner1.htm
<form action="" method="post">
<input type="submit" value="出荷">
<input type="hidden" name="owner_id" value="<TMPL_VAR NAME="owner_id">">
<input type="hidden" name="tmpl" value="mscowner_finish">
<input type="hidden" name="pat" value="NO">
<input type="hidden" name="pat2" value="afterpost">
<input type="hidden" name="rm" value="modeowner_man">
</form>
复写mscorder_finish.htm=>mscowner_finish.htm
mscown_start1 => mscowner1.htm
mode(area_id),mscowner1.pl
mscowner_finish.htm => mscowner1.htm
mode(area_id),mscowner1.pl
]]>
- 把check_owners_orders.pl的程序搬到mscowner_start.pl
- mscown_start.htm修改
- 没有ORDERNO的客户不显示,check_owners_orders.pl
统计ORDER的数量
统计客户的数量
- write_excel.pl时写入invoicetime==>MySQL操作程序二十一
- Invoice作成の日を追加し,画面に表示する。
order1,追加invoicetime,default 2030-01-01
- order1.htm增加到"出荷済み"と"入金済み"
- 程序修改,===>PERL连接NULL数据有问题!!!所有相关程序都要修改!
- 增加"入金済み",按"入金日付"排列的表(客户中心)。
mscorder_man.pl增加afterpay模式
- 增加"出荷済み",按"出荷日付"排列的表(客户中心),如果只显示order1和enq1的ourref并没有其他修改按钮,可能会相当快。
mscorder_man.pl增加afterpost模式
增加mscorder_finish.htm
- 仕入先の納期を追加
- 画面增加ourref的检索功能。(按钮以外)
<![CDATA[
# check_owners_orders.pl
use strict;
use DBI;
my(%t,$aref,$pref,$row,@rec,%seen,$n);
# 连接数据库
$$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{sth} = $$pref{dbh}->prepare("SELECT owners.id,owners.company,owners.name1,order1.id,order1.orderno,enq1.id,enq1.owner
FROM owners,order1,enq1
WHERE order1.id=enq1.id
AND order1.orderno != 'NO'
AND enq1.owner = owners.id
ORDER BY owners.company");
$t{sth}->execute;
%seen = ();
while ( @rec = $t{sth}->fetchrow_array ) {
$t{ownersid1} = $rec[0];
$t{company1} = $rec[1];
$t{name1} = $rec[2];
$t{orderid1} = $rec[3];
$t{orderno1} = $rec[4];
$t{enq1id1} = $rec[5];
$t{enq1owner} = $rec[6];
unless ( $seen{$t{company1}} ) {
push(@{ $t{ownersids} },$t{ownersid1});
push(@{ $t{companies} },$t{company1});
if ( $t{name1} ) {
push(@{ $t{names} },$t{name1});
} else {
push(@{ $t{names} },'NO');
}
}
$seen{$t{company1}}++;
}
# 关闭数据库
$$pref{dbh}->disconnect;
for $n ( 0 .. $#{ $t{companies} } ) {
$t{ownersid1} = $t{ownersids}[$n];
$t{company1} = $t{companies}[$n];
$t{name1} = $t{names}[$n];
$t{nos} = $seen{$t{company1}};
print "ownersid1=$t{ownersid1},name1=$t{name1},company1=$t{company1},nos=$t{nos}\n";
}
$t{sum1} = $#{ $t{companies} } + 1;
print "sum1=$t{sum1}\n";
---------------------------------------------------------------------------
# check_owners_orders.pl
use strict;
use DBI;
my(%t,$aref,$pref,$row,@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{sth} = $$pref{dbh}->prepare("SELECT owners.id,owners.company,owners.name1,order1.id,order1.orderno,enq1.id,enq1.owner
FROM owners,order1,enq1
WHERE order1.id=enq1.id
AND order1.orderno != 'NO'
AND enq1.owner = owners.id
ORDER BY owners.company");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{ownersid1} = $rec[0];
$t{company1} = $rec[1];
$t{orderid1} = $rec[3];
$t{orderno1} = $rec[4];
$t{enq1id1} = $rec[5];
$t{enq1owner} = $rec[6];
print "ownersid=$t{ownersid1},company=$t{company1},";
print "orderid=$t{orderid1},orderno=$t{orderno1},";
print "enq1id=$t{enq1id1},enq1owner=$t{enq1owner}\n";
}
# 关闭数据库
$$pref{dbh}->disconnect;
----------------------------------------------------------------------------
mysql> SELECT id,company,name1 from owners ORDER BY company;
mysql> show columns from order1;
+-----------------+-----------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| orderno | char(100) | NO | | NO | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| consignee | text | YES | | NULL | |
| changeprice | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| total | text | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| packing_charge | text | YES | | NULL | |
| freight_charges | text | YES | | NULL | |
| delivery_charge | text | YES | | NULL | |
| delivery_place | text | YES | | NULL | |
| disc | text | YES | | NULL | |
| shipping | text | YES | | NULL | |
| packing | text | YES | | NULL | |
| packing_info | text | YES | | NULL | |
| post | char(100) | NO | | NO | |
| posttime | date | NO | | 2030-01-01 | |
| paytime | date | NO | | 2030-01-01 | |
| status | int(11) | NO | | 0 | |
| arrivaltime | date | NO | | 2030-00-00 | |
| arrivalmemo | text | YES | | NULL | |
| size | text | YES | | NULL | |
| weight | text | YES | | NULL | |
| paymemo | text | YES | | NULL | |
| invoicetime | date | NO | | 2030-01-01 | |
+-----------------+-----------+------+-----+------------+----------------+
31 rows in set (0.14 sec)
mysql> ALTER TABLE order1 ADD invoicetime DATE NOT NULL DEFAULT '2030-01-01';
Query OK, 12395 rows affected (2.78 sec)
Records: 12395 Duplicates: 0 Warnings: 0
程序修改
$t{line_one} = $t{line_one} . '<td>' . $t{post1} . '</td>';
===>PERL连接NULL数据有问题!!!
if ( $t{post1} ) {
$t{line_one} = $t{line_one} . '<td>' . $t{post1} . '</td>';
} else {
$t{line_one} = $t{line_one} . '<td>NO</td>';
}
mysql> show columns from order1;
+-----------------+-----------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------+------+-----+------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time | date | YES | | NULL | |
| orderno | char(100) | NO | | NO | |
| ORIGINid | int(11) | YES | | NULL | |
| PRICEid | int(11) | YES | | NULL | |
| PAYMENTid | int(11) | YES | | NULL | |
| DELIVERY | text | YES | | NULL | |
| memo | text | YES | | NULL | |
| consignee | text | YES | | NULL | |
| changeprice | text | YES | | NULL | |
| discount | text | YES | | NULL | |
| total | text | YES | | NULL | |
| LANGUAGEid | int(11) | YES | | NULL | |
| packing_charge | text | YES | | NULL | |
| freight_charges | text | YES | | NULL | |
| delivery_charge | text | YES | | NULL | |
| delivery_place | text | YES | | NULL | |
| disc | text | YES | | NULL | |
| shipping | text | YES | | NULL | |
| packing | text | YES | | NULL | |
| packing_info | text | YES | | NULL | |
| post | char(100) | NO | | NO | |
| posttime | date | NO | | 2030-01-01 | |
| paytime | date | NO | | 2030-01-01 | |
| status | int(11) | NO | | 0 | |
| arrivaltime | date | NO | | 2030-00-00 | |
| arrivalmemo | text | YES | | NULL | |
| size | text | YES | | NULL | |
| weight | text | YES | | NULL | |
| paymemo | text | YES | | NULL | |
+-----------------+-----------+------+-----+------------+----------------+
30 rows in set (0.11 sec)
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.19 sec)
]]>
返回