MySQL操作程序十五(EXCEL文件输出)
返回
<![CDATA[
sub write_excel {
my($e_ref,$self) = @_;
my(%t,$n,$n1,$n2,$n3,$n4);
$t{excel_pat} = $$e_ref{excel_pat};
$t{excel} = $$e_ref{excel};
$t{enq1_id} = $$e_ref{enq1_id};
$t{enq2_id} = $$e_ref{enq2_id};
# LANGUAGE
if ( $t{excel_pat} eq 'enq1' ) {
$t{language1} = $self->dbh->selectrow_array("SELECT LANGUAGEid FROM enq1 WHERE id = $t{enq1_id}");
if ( $t{language1} == 1 ) {
$t{word1} = "ますますご清栄のこととお慶び申し上げます。";
$t{word2} = "御多忙中恐縮ですが、下記の船舶品を至急御見積りお願い致します。";
$t{word3} = "担当：中島 敬具";
} else {
$t{word1} = "Please quote us your best offer for the following items.";
}
} elsif ( $t{excel_pat} eq 'quo2' ) {
# Header
$t{word1} = "We thank you very much for your enquiry and have the pleasure to quote as following.";
$t{word2} = "Hope our offer can meet your requirements.";
} elsif ( $t{excel_pat} eq 'order1' ) {
$t{word1} = "We thank you very much for your good order and confirm as following.";
$t{word2} = "As soon as the goods are ready for dispatch we will keep you informed immediately.";
} elsif ( $t{excel_pat} eq 'enq2_order' ) {
$t{language1} = $self->dbh->selectrow_array("SELECT LANGUAGEid FROM enq2 WHERE id = $t{enq2_id}");
if ( $t{language1} == 1 ) {
$t{word1} = "よろしくお手配くださいますようお願いします。";
$t{word2} = "出荷する前に、梱包サイズと重量を教えてください。";
} else {
$t{word1} = "We confirm following order. Please advise us weight & size ";
$t{word2} = "when the goods is ready to dispatch.";
}
}
# OUR REF
($t{ourref1},$t{owner},$t{makerid}) = $self->dbh->selectrow_array("SELECT ourref,owner,makerid FROM enq1 WHERE id = $t{enq1_id}");
if ( $t{excel_pat} eq 'enq1' ) {
# 厂家
($t{maker1},$t{person},$t{telfax}) = $self->dbh->selectrow_array("SELECT company,person,telfax FROM makers WHERE id = $t{makerid}");
} elsif ( $t{excel_pat} eq 'quo2' or $t{excel_pat} eq 'order1' ) {
# 船东
($t{maker1},$t{person},$t{telfax}) = $self->dbh->selectrow_array("SELECT company,person,telfax FROM owners WHERE id = $t{owner}");
} elsif ( $t{excel_pat} eq 'enq2_order' ) {
# 厂家
($t{makerid},$t{makerref}) = $self->dbh->selectrow_array("SELECT makerid,makerref FROM enq2 WHERE id = $t{enq2_id}");
($t{maker1},$t{person},$t{telfax}) = $self->dbh->selectrow_array("SELECT company,person,telfax FROM makers WHERE id = $t{makerid}");
}
# HULL_NO和船名
$t{hullnoid} = $self->dbh->selectrow_array("SELECT hullnoid FROM enq1 WHERE id = $t{enq1_id}");
@{ $t{hullnos} } = $self->dbh->selectrow_array("SELECT * FROM hull_no WHERE id = $t{hullnoid}");
$t{ship_name} = $t{hullnos}[2];
$t{hullno} = $t{hullnos}[1];
# 船主ENQ番号
$t{ownerno} = $self->dbh->selectrow_array("SELECT ownerno FROM enq1 WHERE id = $t{enq1_id}");
# 打开EXCEL
$t{ex} = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;}) or die "Oops, cannot start Excel";
$t{ex}->{DisplayAlerts} = 'False';
# open the sample file map.xls
if ( $t{excel_pat} eq 'enq1' ) {
$book = $t{ex}->Workbooks->Open("C:\\wwwexcel\\samples\\sample_ENQ.xls");
} elsif ( $t{excel_pat} eq 'quo2' ) {
$book = $t{ex}->Workbooks->Open("C:\\wwwexcel\\samples\\sample_QTN.xls");
} elsif ( $t{excel_pat} eq 'order1' ) {
$book = $t{ex}->Workbooks->Open("C:\\wwwexcel\\samples\\sample_ORDER CONF.xls");
} elsif ( $t{excel_pat} eq 'enq2_order' ) {
$book = $t{ex}->Workbooks->Open("C:\\wwwexcel\\samples\\sample_ORDER.xls");
} elsif ( $t{excel_pat} eq 'shipping' ) {
$book = $t{ex}->Workbooks->Open("C:\\wwwexcel\\samples\\sample_INV.xls");
} elsif ( $t{excel_pat} eq 'packing' ) {
$book = $t{ex}->Workbooks->Open("C:\\wwwexcel\\samples\\sample_PACKING.xls");
} elsif ( $t{excel_pat} eq 'invoice' ) {
$book = $t{ex}->Workbooks->Open("C:\\wwwexcel\\samples\\sample_INVOICE.xls");
}
# 打开SHEET
$t{sheet} = $book->Worksheets("Sheet1");
# 写入OURREF
$t{ourref1} = 'OUR REF.NO. ' . $t{ourref1};
$t{sheet}->Cells(4,"B")->{'Value'} = Variant(VT_BSTR, $t{ourref1});
$t{sheet}->Cells(4,"B")->{Font}->{Size} = "12";
$t{sheet}->Cells(4,"B")->{Font}->{Bold} = "True";
# 写入日期
($t{day},$t{month},$t{year}) = (localtime)[3,4,5];
$t{year} = 1900+$t{year};
$t{month} = 1+$t{month};
$t{year} = sprintf("%04d",$t{year});
$t{month} = sprintf("%02d",$t{month});
$t{day} = sprintf("%02d",$t{day});
$t{Date} = 'DATE: ' . $t{year} . '.' . $t{month} . '.' . $t{day};
$t{Date} = decode("utf8",$t{Date});
$t{sheet}->Cells(5,"B")->{'Value'} = Variant(VT_BSTR, $t{Date});
# 写入厂家名
if ( $t{excel_pat} eq 'shipping' or $t{excel_pat} eq 'packing' ) {
$t{buyer} = 'Buyer';
$t{Consignee} = 'Consignee';
$t{sheet}->Cells(8,"B")->{'Value'} = Variant(VT_BSTR, $t{buyer});
$t{sheet}->Cells(8,"D")->{'Value'} = Variant(VT_BSTR, $t{Consignee});
($t{maker1},$t{address1},$t{person},$t{telfax}) = $self->dbh->selectrow_array("SELECT company,address,person,telfax FROM owners WHERE id = $t{owner}");
$t{maker1} = 'TO: ' . $t{maker1};
$t{person} = 'ATTN: ' . $t{person};
$t{address1} = 'Address: ' . $t{address1};
$t{maker1} = decode("utf8",$t{maker1});
$t{person} = decode("utf8",$t{person});
$t{address1} = decode("utf8",$t{address1});
$t{telfax} = decode("utf8",$t{telfax});
$t{word1} = decode("utf8",$t{word1});
$t{sheet}->Cells(9,"B")->{Font}->{Size} = "9";
$t{sheet}->Cells(9,"B")->{'Value'} = Variant(VT_BSTR, $t{maker1});
$t{sheet}->Cells(10,"B")->{Font}->{Size} = "9";
$t{sheet}->Cells(10,"B")->{'Value'} = Variant(VT_BSTR, $t{person});
$t{sheet}->Cells(11,"B")->{Font}->{Size} = "9";
$t{sheet}->Cells(11,"B")->{'Value'} = Variant(VT_BSTR, $t{telfax});
$t{sheet}->Cells(12,"B")->{WrapText} = "True";
$t{sheet}->Cells(12,"B")->{'Value'} = Variant(VT_BSTR, $t{address1});
$t{master} = 'Master of M/V ' . $t{ship_name};
$t{sheet}->Cells(9,"D")->{Font}->{Size} = "9";
$t{sheet}->Cells(9,"D")->{'Value'} = Variant(VT_BSTR, $t{master});
$t{transit} = 'SHIP PARTS IN TRANSIT';
$t{sheet}->Cells(10,"D")->{Font}->{Size} = "9";
$t{sheet}->Cells(10,"D")->{'Value'} = Variant(VT_BSTR, $t{transit});
$t{consignee} = $self->dbh->selectrow_array("SELECT consignee FROM order1 WHERE id = $t{enq1_id}");
($t{maker1},$t{address},$t{telfax},$t{person}) = $self->dbh->selectrow_array("SELECT company,address,telfax,person FROM makers WHERE id = $t{consignee}");
$t{maker1} = 'C/O: ' . $t{maker1};
$t{address} = 'Address: ' . $t{address};
$t{maker1} = decode("utf8",$t{maker1});
$t{person} = 'ATTN: ' . $t{person};
$t{person} = decode("utf8",$t{person});
$t{telfax} = decode("utf8",$t{telfax});
$t{word1} = decode("utf8",$t{word1});
$t{sheet}->Cells(11,"D")->{Font}->{Size} = "9";
$t{sheet}->Cells(11,"D")->{'Value'} = Variant(VT_BSTR, $t{maker1});
$t{sheet}->Cells(12,"D")->{Font}->{Size} = "9";
$t{sheet}->Cells(12,"D")->{WrapText} = "True";
$t{XY1} = 'D12';
$t{XY2} = 'H12';
$t{sheet}->Range("$t{XY1}:$t{XY2}")->{MergeCells} = "True";
$t{sheet}->Cells(12,"D")->{'Value'} = Variant(VT_BSTR, $t{address});
$t{sheet}->Cells(13,"D")->{Font}->{Size} = "9";
$t{sheet}->Cells(13,"D")->{'Value'} = Variant(VT_BSTR, $t{telfax});
$t{sheet}->Cells(14,"D")->{Font}->{Size} = "9";
$t{sheet}->Cells(14,"D")->{'Value'} = Variant(VT_BSTR, $t{person});
} elsif ($t{excel_pat} eq 'invoice') {
$t{buyer} = 'Buyer';
$t{sheet}->Cells(8,"B")->{Font}->{Size} = "9";
$t{sheet}->Cells(8,"B")->{'Value'} = Variant(VT_BSTR, $t{buyer});
($t{maker1},$t{person},$t{telfax}) = $self->dbh->selectrow_array("SELECT company,person,telfax FROM owners WHERE id = $t{owner}");
$t{maker1} = 'TO: ' . $t{maker1};
$t{person} = 'ATTN: ' . $t{person};
$t{maker1} = decode("utf8",$t{maker1});
$t{person} = decode("utf8",$t{person});
$t{telfax} = decode("utf8",$t{telfax});
$t{word1} = 'We have delivered all items according to your instruction. ';
$t{word2} = 'Herewith enclosed invoice for payment, kindly arrange the remittance to us.';
$t{word1} = decode("utf8",$t{word1});
$t{sheet}->Cells(9,"B")->{Font}->{Size} = "9";
$t{sheet}->Cells(9,"B")->{'Value'} = Variant(VT_BSTR, $t{maker1});
$t{sheet}->Cells(10,"B")->{Font}->{Size} = "9";
$t{sheet}->Cells(10,"B")->{'Value'} = Variant(VT_BSTR, $t{person});
$t{sheet}->Cells(11,"B")->{Font}->{Size} = "9";
$t{sheet}->Cells(11,"B")->{'Value'} = Variant(VT_BSTR, $t{telfax});
$t{sheet}->Cells(13,"B")->{Font}->{Size} = "9";
$t{sheet}->Cells(13,"B")->{'Value'} = Variant(VT_BSTR, $t{word1});
$t{sheet}->Cells(14,"B")->{Font}->{Size} = "9";
$t{sheet}->Cells(14,"B")->{'Value'} = Variant(VT_BSTR, $t{word2});
} else {
$t{maker1} = 'TO: ' . $t{maker1};
$t{person} = 'ATTN: ' . $t{person};
$t{maker1} = decode("utf8",$t{maker1});
$t{person} = decode("utf8",$t{person});
$t{telfax} = decode("utf8",$t{telfax});
$t{word1} = decode("utf8",$t{word1});
$t{sheet}->Cells(8,"B")->{Font}->{Size} = "9";
$t{sheet}->Cells(8,"B")->{'Value'} = Variant(VT_BSTR, $t{maker1});
$t{sheet}->Cells(9,"B")->{Font}->{Size} = "9";
$t{sheet}->Cells(9,"B")->{'Value'} = Variant(VT_BSTR, $t{person});
$t{sheet}->Cells(10,"B")->{Font}->{Size} = "9";
$t{sheet}->Cells(10,"B")->{'Value'} = Variant(VT_BSTR, $t{telfax});
$t{sheet}->Cells(12,"B")->{Font}->{Size} = "9";
$t{sheet}->Cells(12,"B")->{'Value'} = Variant(VT_BSTR, $t{word1});
if ( $t{excel_pat} eq 'enq1' ) {
$t{word2} = decode("utf8",$t{word2});
$t{sheet}->Cells(13,"B")->{Font}->{Size} = "9";
$t{sheet}->Cells(13,"B")->{'Value'} = Variant(VT_BSTR, $t{word2});
$t{word3} = decode("utf8",$t{word3});
$t{sheet}->Cells(14,"D")->{Font}->{Size} = "9";
$t{sheet}->Cells(14,"D")->{'Value'} = Variant(VT_BSTR, $t{word3});
} elsif ($t{excel_pat} eq 'quo2' or $t{excel_pat} eq 'order1') {
$t{word2} = decode("utf8",$t{word2});
$t{sheet}->Cells(13,"B")->{Font}->{Size} = "9";
$t{sheet}->Cells(13,"B")->{'Value'} = Variant(VT_BSTR, $t{word2});
} elsif ( $t{excel_pat} eq 'enq2_order' ) {
$t{word2} = decode("utf8",$t{word2});
$t{sheet}->Cells(13,"B")->{Font}->{Size} = "9";
$t{sheet}->Cells(13,"B")->{'Value'} = Variant(VT_BSTR, $t{word2});
$t{word3} = 'YOUR REF. ' . $t{makerref};
$t{word3} = decode("utf8",$t{word3});
with ($t{sheet}->Range("B15"), HorizontalAlignment => xlLeft );
$t{sheet}->Cells(15,"B")->{Font}->{Size} = "9";
$t{sheet}->Cells(15,"B")->{'Value'} = Variant(VT_BSTR, $t{word3});
}
}
if ( $t{excel_pat} eq 'quo2' or $t{excel_pat} eq 'order1' or $t{excel_pat} eq 'enq2_order' or $t{excel_pat} eq 'shipping') {
# 写入ORIGIN/PRICE/PAYMENT/DELIVERY TIME/VALIDITY
$t{VALIDITY} = '1 MONTH';
$t{WARRANTY} = '1 YEAR';
# 写入WARRANTY
if ( $t{excel_pat} eq 'enq2_order' ) {
$t{DELIVERY} = $self->dbh->selectrow_array("SELECT DELIVERY FROM enq2 WHERE id = $t{enq2_id}");
($t{ORIGINid},$t{PRICEid},$t{PAYMENTid}) = $self->dbh->selectrow_array("SELECT ORIGINid,PRICEid,PAYMENTid FROM quo2 WHERE id = $t{enq1_id}");
} elsif ( $t{excel_pat} eq 'quo2' ) {
($t{ORIGINid},$t{PRICEid},$t{PAYMENTid},$t{DELIVERY}) = $self->dbh->selectrow_array("SELECT ORIGINid,PRICEid,PAYMENTid,DELIVERY FROM quo2 WHERE id = $t{enq1_id}");
} elsif ( $t{excel_pat} eq 'order1' or $t{excel_pat} eq 'shipping' ) {
($t{ORIGINid},$t{PRICEid},$t{PAYMENTid},$t{DELIVERY}) = $self->dbh->selectrow_array("SELECT ORIGINid,PRICEid,PAYMENTid,DELIVERY FROM quo2 WHERE id = $t{enq1_id}");
}
$t{ORIGIN} = $self->dbh->selectrow_array("SELECT ORIGIN FROM enq2_origin WHERE id = $t{ORIGINid}");
$t{PRICE} = $self->dbh->selectrow_array("SELECT PRICE FROM enq2_price WHERE id = $t{PRICEid}");
$t{PAYMENT} = $self->dbh->selectrow_array("SELECT PAYMENT FROM enq2_payment WHERE id = $t{PAYMENTid}");
$t{ORIGIN} = 'ORIGIN: ' . $t{ORIGIN};
$t{PRICE} = 'PRICE: ' . $t{PRICE};
$t{PAYMENT} = 'PAYMENT: ' . $t{PAYMENT};
$t{DELIVERY} = 'DELIVERY TIME: ' . $t{DELIVERY};
$t{VALIDITY} = 'VALIDITY: ' . $t{VALIDITY};
$t{WARRANTY} = 'WARRANTY: ' . $t{WARRANTY};
$t{ORIGIN} = decode("utf8",$t{ORIGIN});
$t{PRICE} = decode("utf8",$t{PRICE});
$t{PAYMENT} = decode("utf8",$t{PAYMENT});
$t{DELIVERY} = decode("utf8",$t{DELIVERY});
$t{VALIDITY} = decode("utf8",$t{VALIDITY});
$t{WARRANTY} = decode("utf8",$t{WARRANTY});
$t{sheet}->Cells(17,"B")->{Font}->{Size} = "8";
$t{sheet}->Cells(17,"B")->{'Value'} = Variant(VT_BSTR, $t{ORIGIN});
$t{sheet}->Cells(18,"B")->{Font}->{Size} = "8";
$t{sheet}->Cells(18,"B")->{'Value'} = Variant(VT_BSTR, $t{PRICE});
$t{sheet}->Cells(19,"B")->{Font}->{Size} = "8";
$t{sheet}->Cells(19,"B")->{'Value'} = Variant(VT_BSTR, $t{PAYMENT});
$t{sheet}->Cells(17,"E")->{Font}->{Size} = "8";
$t{sheet}->Cells(17,"E")->{'Value'} = Variant(VT_BSTR, $t{DELIVERY});
$t{sheet}->Cells(18,"E")->{Font}->{Size} = "8";
$t{sheet}->Cells(18,"E")->{'Value'} = Variant(VT_BSTR, $t{VALIDITY});
$t{sheet}->Cells(19,"E")->{Font}->{Size} = "8";
$t{sheet}->Cells(19,"E")->{'Value'} = Variant(VT_BSTR, $t{WARRANTY});
}
# 写入船名,HULL_NO,船东ENQ
$t{ship_name} = 'MV: ' . $t{ship_name};
$t{ship_name} = decode("utf8",$t{ship_name});
$t{sheet}->Cells(23,"B")->{'Value'} = Variant(VT_BSTR, $t{ship_name});
$t{hullno} = 'HULL NO: ' . $t{hullno};
$t{hullno} = decode("utf8",$t{hullno});
$t{sheet}->Cells(24,"B")->{'Value'} = Variant(VT_BSTR, $t{hullno});
if ( $t{excel_pat} eq 'order1' or $t{excel_pat} eq 'shipping' or $t{excel_pat} eq 'packing' or $t{excel_pat} eq 'invoice' ) {
$t{orderno} = $self->dbh->selectrow_array("SELECT orderno FROM order1 WHERE id = $t{enq1_id} and orderno is not NULL");
if ( !($t{orderno}) ) {
$t{orderno} = $t{ownerno};
}
$t{orderno} = 'P.O. ' . $t{orderno};
$t{orderno} = decode("utf8",$t{orderno});
$t{sheet}->Cells(25,"B")->{'Value'} = Variant(VT_BSTR, $t{orderno});
} else {
$t{ownerno} = 'ENQ: ' . $t{ownerno};
$t{ownerno} = decode("utf8",$t{ownerno});
$t{sheet}->Cells(25,"B")->{'Value'} = Variant(VT_BSTR, $t{ownerno});
}
# 写入主机名/TYPE/Maker(B27/B28/B29)
# 写入Serieal号
# 写入图纸号
# 写入parts,No/Description/QTY/Unit/U/Price/Amount
if ( $t{excel_pat} eq 'enq2_order' ) {
$t{moneyid} = $self->dbh->selectrow_array("SELECT money FROM enq2 WHERE id = $t{enq2_id}");
} else {
$t{moneyid} = $self->dbh->selectrow_array("SELECT money FROM quo2 WHERE id = $t{enq1_id}");
}
# 取出type1id,partsid和QTY
if ( $t{excel_pat} eq 'enq2_order' ) {
($t{type1id},$t{partsid},$t{QTY}) = $self->dbh->selectrow_array("select type1id, partsid,QTY from enq2 where id = $t{enq2_id}");
@{ $t{type1s} } = split(/==/,$t{type1id});
@{ $t{part1} } = split(/==/,$t{partsid});
@{ $t{QT1} } = split(/==/,$t{QTY});
($t{type1idx},$t{seriesid}) = $self->dbh->selectrow_array("select type1id,seriesid from enq1 where id = $t{enq1_id}");
@{ $t{type1sx} } = split(/==/,$t{type1idx});
@{ $t{seri1} } = split(/==/,$t{seriesid});
for $n ( 0 .. $#{ $t{type1sx} } ) {
$t{seri_list}{$t{type1sx}[$n]} = $t{seri1}[$n];
}
} else {
($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{type1s} } = split(/==/,$t{type1id});
@{ $t{part1} } = split(/==/,$t{partsid});
@{ $t{QT1} } = split(/==/,$t{QTY});
@{ $t{seri1} } = split(/==/,$t{seriesid});
for $n ( 0 .. $#{ $t{type1s} } ) {
$t{seri_list}{$t{type1s}[$n]} = $t{seri1}[$n];
}
}
# if ( $t{excel_pat} eq 'enq1' ) {
# } elsif ( $t{excel_pat} eq 'quo2' or $t{excel_pat} eq 'order1' or $t{excel_pat} eq 'shipping' or $t{excel_pat} eq 'packing' or $t{excel_pat} eq 'invoice' ) {
# } elsif ( $t{excel_pat} eq 'enq2_order' ) {
# }
$t{Y1} = 26;
# 取出图纸号
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{NO} = 0;
$t{total1} = 0;
for $n ( 0 .. $#{ $t{type1s} } ) {
$t{type2} = $t{type1s}[$n];
$t{part2} = $t{part1}[$n];
$t{QTY2} = $t{QT1}[$n];
$t{seri2} = $t{seri_list}{$t{type2}};
# 不是第一台主机的情况
if ( $t{Y1} != 26 ) {
$t{Y1} = $t{Y1} + 2;
}
# 从main_type1中取出主机名,Serieal和DWG图号
($t{name},$t{series},$t{gname_id},$t{maker_id},$t{DWG}) = $self->dbh->selectrow_array("select name,series,gname_id,maker_id,DWG from main_type1 where id = $t{type2}");
@{ $t{DWGs} } = split(/=/,$t{DWG});
$t{gname} = $self->dbh->selectrow_array("select name from main_name1 where id = $t{gname_id}");
$t{maker} = $self->dbh->selectrow_array("select name from main_maker1 where id = $t{maker_id}");
# 写入主机的名称,type,厂家
$t{Y1}++;
$t{gname} = 'NAME: ' . $t{gname};
$t{gname} = decode("utf8",$t{gname});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{gname});
$t{Y1}++;
$t{type} = 'TYPE: ' . $t{name};
$t{type} = decode("utf8",$t{type});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{type});
$t{Y1}++;
$t{maker} = 'MAKER: ' . $t{maker};
$t{maker} = decode("utf8",$t{maker});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{maker});
$t{Y1}++;
if ( $t{seri2} > 1 ) {
@{ $t{seriess} } = split(/=/,$t{series});
for $n1 ( 1 .. $#{ $t{seriess} } ) {
$t{NS1} = $n1 + 1;
if ( $t{seri2} == $t{NS1} ) {
$t{series} = $t{seriess}[$n1];
}
}
$t{series} = 'SERIES: ' . $t{series};
} else {
$t{series} = 'SERIES: ';
}
$t{series} = decode("utf8",$t{series});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{series});
$t{Y1}++;
# 零件表名
$t{ptable} = sprintf("%06d",$t{type2});
$t{ptable} = 'a' . $t{ptable};
@{ $t{pid_list} } = split(/=/,$t{part2});
@{ $t{Q_list} } = split(/=/,$t{QTY2});
# 根据enq1的零件编号从数据库取出零件信息和所属图纸号
@{ $t{dwgs1} } = ();
for $n1 ( 0 .. $#{ $t{pid_list} } ) {
$t{pid1} = $t{pid_list}[$n1];
$t{Q1} = $t{Q_list}[$n1];
@{ $t{p1} } = $self->dbh->selectrow_array("select * from $t{ptable} where id = $t{pid1}");
$t{dwg1} = $t{p1}[3];
push(@{ $t{plist}{id}{$t{dwg1}} },$t{p1}[0]);
push(@{ $t{plist}{name}{$t{dwg1}} },$t{p1}[1]);
push(@{ $t{plist}{code}{$t{dwg1}} },$t{p1}[2]);
push(@{ $t{plist}{Nuid}{$t{dwg1}} },$t{p1}[4]);
push(@{ $t{plist}{QTY}{$t{dwg1}} },$t{Q1});
push(@{ $t{dwgs1} },$t{dwg1});
}
# 合并重复的图纸号
%seen = ();
@{ $t{dwgs2} } = ();
foreach $item (@{ $t{dwgs1} }) {
unless ( $seen{$item} ) {
$seen{$item} = 1;
push(@{ $t{dwgs2} },$item);
}
}
# 把数据写入EXCEL文件
for $n1 ( 0 .. $#{ $t{dwgs2} } ) {
$t{dwg1} = $t{dwgs2}[$n1];
$t{DWG1} = $t{DWGs}[$t{dwg1}-1];
# 取出图纸号
$t{Y1}++;
if ( $t{DWG1} eq 'XXXDWG' ) { # 没有图纸号的情况
$t{line1} = 'DWG. NO. ';
} else {
$t{line1} = 'DWG. NO. ' . $t{DWG1};
}
$t{DWG1} = decode("utf8",$t{line1});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{DWG1});
$t{PN1} = 'P/N';
$t{PN1} = decode("utf8",$t{PN1});
$t{sheet}->Cells($t{Y1},"C")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"C")->{'Value'} = Variant(VT_BSTR, $t{PN1});
# 处理零件
for $n2 ( 0 .. $#{ $t{plist}{id}{$t{dwg1}} } ) {
$t{NO}++; # enq1的所有Parts的编号
$t{pid1} = $t{plist}{id}{$t{dwg1}}[$n2];
$t{name1} = $t{plist}{name}{$t{dwg1}}[$n2];
$t{code1} = $t{plist}{code}{$t{dwg1}}[$n2];
$t{Q1} = $t{plist}{QTY}{$t{dwg1}}[$n2];
$t{Nuid1} = $t{plist}{Nuid}{$t{dwg1}}[$n2];
$t{NU1} = $self->dbh->selectrow_array("select parts_Unit from parts_nu where id = $t{Nuid1}");
$t{Y1}++;
$t{NO1} = decode("utf8",$t{NO});
$t{sheet}->Cells($t{Y1},"A")->{Font}->{Size} = "9";
$t{range} = "A" . $t{Y1};
with ($t{sheet}->Range($t{range}), HorizontalAlignment => xlCenter );
$t{sheet}->Cells($t{Y1},"A")->{'Value'} = Variant(VT_BSTR, $t{NO1});
if ( $t{name1} =~ /==/ ) { # 有换行的情况
@{ $t{names} } = split(/==/,$t{name1});
$t{name1} = $t{names}[0];
$t{name1} = decode("utf8",$t{name1});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{WrapText} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{name1});
# $t{code1} = decode("utf8",$t{code1});
$t{range} = "C" . $t{Y1};
with ($t{sheet}->Range($t{range}), HorizontalAlignment => xlLeft );
$t{sheet}->Cells($t{Y1},"C")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"C")->{NumberFormatLocal} = "@";
$t{sheet}->Cells($t{Y1},"C")->{'Value'} = $t{code1};
$t{sheet}->Cells($t{Y1},"D")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"D")->{'Value'} = Variant(VT_BSTR, $t{Q1});
$t{NU1} = decode("utf8",$t{NU1});
$t{range} = "E" . $t{Y1};
with ($t{sheet}->Range($t{range}), HorizontalAlignment => xlLeft );
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = Variant(VT_BSTR, $t{NU1});
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "False";
if ( $t{excel_pat} eq 'quo2' or $t{excel_pat} eq 'order1' or $t{excel_pat} eq 'enq2_order' or $t{excel_pat} eq 'invoice' ) {
$t{pri1} = $t{pps}[$t{NO}-1];
if ( $t{moneyid} == 1 ) {
} else {
$t{pri1} = sprintf("%.02f",$t{pri1});
}
$t{pri1c} = commify($t{pri1});
$t{XY1} = "F" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
$t{sheet}->Cells($t{Y1},"F")->{NumberFormatLocal} = "@";
$t{sheet}->Cells($t{Y1},"F")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"F")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"F")->{'Value'} = $t{pri1c};
$t{pris} = $t{Q1}*$t{pri1};
if ( $t{moneyid} == 1 ) {
$t{pris} = int($t{pris}+0.5);
} else {
$t{pris} = int(($t{pris}*100+0.5))/100;
$t{pris} = sprintf("%.02f",$t{pris});
}
$t{total1} += $t{pris};
$t{pris} = commify($t{pris});
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{XY1} = "G" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
$t{sheet}->Cells($t{Y1},"G")->{NumberFormatLocal} = "@";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{pris};
} elsif ( $t{excel_pat} eq 'shipping') {
$t{pri1} = $t{pps}[$t{NO}-1]*$$e_ref{changeprice};
if ( $t{moneyid} == 1 ) {
} else {
$t{pri1} = sprintf("%.02f",$t{pri1});
}
$t{pri1c} = commify($t{pri1});
$t{sheet}->Cells($t{Y1},"F")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"F")->{'Value'} = $t{pri1c};
$t{XY1} = "F" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
$t{sheet}->Cells($t{Y1},"F")->{NumberFormatLocal} = "@";
$t{sheet}->Cells($t{Y1},"F")->{Font}->{Bold} = "False";
$t{pris} = $t{Q1}*$t{pri1};
if ( $t{moneyid} == 1 ) {
$t{pris} = int($t{pris}+0.5);
} else {
$t{pris} = int(($t{pris}*100+0.5))/100;
$t{pris} = sprintf("%.02f",$t{pris});
}
$t{total1} += $t{pris};
$t{pris} = commify($t{pris});
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{XY1} = "G" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
$t{sheet}->Cells($t{Y1},"G")->{NumberFormatLocal} = "@";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{pris};
}
for $n3 ( 1 .. $#{ $t{names} } ) {
$t{Y1}++;
$t{name1} = $t{names}[$n3];
$t{name1} = decode("utf8",$t{name1});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{name1});
}
} else { # 没有换行的情况
$t{name1} = decode("utf8",$t{name1});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{WrapText} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{name1});
# $t{code1} = decode("utf8",$t{code1});
$t{range} = "C" . $t{Y1};
$t{sheet}->Cells($t{Y1},"C")->{Font}->{Size} = "9";
with ($t{sheet}->Range($t{range}), HorizontalAlignment => xlLeft );
$t{sheet}->Cells($t{Y1},"C")->{NumberFormatLocal} = "@";
$t{sheet}->Cells($t{Y1},"C")->{'Value'} = $t{code1};
$t{sheet}->Cells($t{Y1},"D")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"D")->{'Value'} = Variant(VT_BSTR, $t{Q1});
$t{NU1} = decode("utf8",$t{NU1});
$t{range} = "E" . $t{Y1};
with ($t{sheet}->Range($t{range}), HorizontalAlignment => xlLeft );
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = Variant(VT_BSTR, $t{NU1});
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "False";
if ( $t{excel_pat} eq 'quo2' or $t{excel_pat} eq 'order1' or $t{excel_pat} eq 'enq2_order' or $t{excel_pat} eq 'invoice' ) {
$t{pri1} = $t{pps}[$t{NO}-1];
if ( $t{moneyid} == 1 ) {
} else {
$t{pri1} = sprintf("%.02f",$t{pri1});
}
$t{pri1c} = commify($t{pri1});
$t{sheet}->Cells($t{Y1},"F")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"F")->{NumberFormatLocal} = "@";
$t{XY1} = "F" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
$t{sheet}->Cells($t{Y1},"F")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"F")->{'Value'} = $t{pri1c};
$t{pris} = $t{Q1}*$t{pri1};
if ( $t{moneyid} == 1 ) {
$t{pris} = int($t{pris}+0.5);
} else {
$t{pris} = int(($t{pris}*100+0.5))/100;
$t{pris} = sprintf("%.02f",$t{pris});
}
$t{total1} += $t{pris};
$t{pris} = commify($t{pris});
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"G")->{NumberFormatLocal} = "@";
$t{XY1} = "G" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{pris};
} elsif ( $t{excel_pat} eq 'shipping') {
$t{pri1} = $t{pps}[$t{NO}-1]*$$e_ref{changeprice};
if ( $t{moneyid} == 1 ) {
} else {
$t{pri1} = sprintf("%.02f",$t{pri1});
}
$t{pri1c} = commify($t{pri1});
$t{sheet}->Cells($t{Y1},"F")->{Font}->{Size} = "9";
$t{XY1} = "F" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
$t{sheet}->Cells($t{Y1},"F")->{NumberFormatLocal} = "@";
$t{sheet}->Cells($t{Y1},"F")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"F")->{'Value'} = $t{pri1c};
$t{pris} = $t{Q1}*$t{pri1};
if ( $t{moneyid} == 1 ) {
$t{pris} = int($t{pris}+0.5);
} else {
$t{pris} = int(($t{pris}*100+0.5))/100;
$t{pris} = sprintf("%.02f",$t{pris});
}
$t{total1} += $t{pris};
$t{pris} = commify($t{pris});
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{XY1} = "G" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
$t{sheet}->Cells($t{Y1},"G")->{NumberFormatLocal} = "@";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{pris};
}
}
}
# 必须置零,因为下一台主机的DWG极有可能同名!
@{ $t{plist}{id}{$t{dwg1}} } = ();
@{ $t{plist}{name}{$t{dwg1}} } = ();
@{ $t{plist}{code}{$t{dwg1}} } = ();
@{ $t{plist}{QTY}{$t{dwg1}} } = ();
@{ $t{plist}{Nuid}{$t{dwg1}} } = ();
}
}
# 写入 TOTAL MONEY
$t{money} = $self->dbh->selectrow_array("SELECT English FROM money WHERE id = $t{moneyid}");
$t{total} = 'TOTAL ' . $t{money} . ' ';
if ($t{excel_pat} eq 'invoice') {
$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};
if ( $t{moneyid} == 1 ) {
$t{total1} = int($t{total1}+0.5);
} else {
$t{total1} = int(($t{total1}*100+0.5))/100;
$t{total1} = sprintf("%.02f",$t{total1});
}
$t{total1} = commify($t{total1});
$t{Y1} = $t{Y1} + 4;
# $t{total} = decode("utf8",$t{total});
if ( $t{disc} ) {
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = 'Disc';
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{XY1} = "G" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
$t{sheet}->Cells($t{Y1},"G")->{NumberFormatLocal} = "@";
if ( $t{moneyid} == 1 ) {
# $t{disc} = int($t{disc}+0.5);
} else {
$t{disc} = sprintf("%.02f",$t{disc});
}
$t{disc} = commify($t{disc});
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{disc};
$t{Y1}++;
}
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = 'Packing & Handling';
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{packing_charge};
$t{Y1}++;
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = 'Freight Charge';
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{freight_charges};
$t{Y1}++;
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = 'Domestic Truck';
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{delivery_charge};
$t{Y1} = $t{Y1} + 2;
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "True";
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
# $t{sheet}->Cells($t{Y1},"E")->{'Value'} = Variant(VT_BSTR, $t{total});
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = $t{total};
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "True";
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{total1};
$t{XY1} = 'E' . $t{Y1};
$t{XY2} = 'F' . $t{Y1};
$t{sheet}->Range("$t{XY1}:$t{XY2}")->{MergeCells} = "True";
$t{XY1} = 'E' . $t{Y1};
$t{XY2} = 'G' . $t{Y1};
# $t{sheet}->Range("B5:D5")->Borders(xlEdgeBottom)->{LineStyle} = xlDouble;
$t{sheet}->Range("$t{XY1}:$t{XY2}")->Borders(xlEdgeBottom)->{LineStyle} = xlDouble;
$t{Y1}++;
$t{delivery_place} = 'TO ' . $t{delivery_place};
$t{delivery_place} = decode("utf8",$t{delivery_place});
$t{sheet}->Cells($t{Y1},"F")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"F")->{'Value'} = Variant(VT_BSTR, $t{delivery_place});
} elsif ( $t{excel_pat} eq 'enq2_order') {
($t{QTY0},$t{price},$t{discount},$t{discount0}) = $self->dbh->selectrow_array("SELECT QTY,price,discount,discount0 FROM enq2 WHERE id = $t{enq2_id}");
# 複数個TYPEの状況を考慮
@{ $t{QTY0s} } = split(/==/,$t{QTY0});
@{ $t{QTYs} } = ();
for $n ( 0 .. $#{ $t{QTY0s} } ) {
$t{QTY} = $t{QTY0s}[$n];
@{ $t{QTY1s} } = split(/=/,$t{QTY});
@{ $t{QTYs} } = (@{ $t{QTYs} },@{ $t{QTY1s} });
}
@{ $t{prices} } = split(/=/,$t{price});
$t{disc1} = 0;
if ( $t{discount0} < 100 ) {
$t{d_per1} = 100 - $t{discount0};
$t{d_name} = 'DISC ' . $t{d_per1} . '%';
if ( $t{discount} ne 'D' ) {
@{ $t{discounts} } = split(/=/,$t{discount});
}
$t{total_d1} = 0;
for $n ( 0 .. $#{ $t{prices} } ) {
if ( $t{discount} ne 'D' ) {
$t{total_d1} = $t{total_d1} + $t{QTYs}[$n]*$t{prices}[$n]*$t{discounts}[$n]/100;
} else {
$t{total_d1} = $t{total_d1} + $t{QTYs}[$n]*$t{prices}[$n]*$t{discount0}/100;
}
}
$t{disc1} = - $t{total1} + $t{total_d1};
} else {
$t{d_name} = 'DISC ';
}
$t{disc2} = $self->dbh->selectrow_array("SELECT disc FROM enq2 WHERE id = $t{enq2_id} and disc is not NULL");
$t{disc} = $t{disc1} + $t{disc2};
$t{charges} = $self->dbh->selectrow_array("SELECT charges FROM enq2 WHERE id = $t{enq2_id} and charges is not NULL");
$t{total1} = $t{total1} + $t{disc} + $t{charges};
if ( $t{moneyid} == 1 ) {
$t{total1} = int($t{total1}+0.5);
} else {
$t{total1} = int(($t{total1}*100+0.5))/100;
$t{total1} = sprintf("%.02f",$t{total1});
}
$t{total1} = commify($t{total1});
if ( $t{charges} ) {
$t{Y1} = $t{Y1} + 3;
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = 'EXPENSE';
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{charges};
}
if ( $t{disc} ) {
$t{Y1} = $t{Y1} + 2;
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = $t{d_name};
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"G")->{NumberFormatLocal} = "@";
$t{XY1} = "G" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
if ( $t{moneyid} == 1 ) {
# $t{disc} = int($t{disc}+0.5);
} else {
$t{disc} = sprintf("%.02f",$t{disc});
}
$t{disc} = commify($t{disc});
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{disc};
}
$t{Y1} = $t{Y1} + 2;
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "True";
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = $t{total};
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "True";
$t{sheet}->Cells($t{Y1},"G")->{NumberFormatLocal} = "@";
$t{XY1} = "G" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{total1};
$t{XY1} = 'E' . $t{Y1};
$t{XY2} = 'F' . $t{Y1};
$t{sheet}->Range("$t{XY1}:$t{XY2}")->{MergeCells} = "True";
$t{XY1} = 'E' . $t{Y1};
$t{XY2} = 'G' . $t{Y1};
$t{sheet}->Range("$t{XY1}:$t{XY2}")->Borders(xlEdgeBottom)->{LineStyle} = xlDouble;
} elsif ( $t{excel_pat} eq 'order1' ) {
$t{disc} = $self->dbh->selectrow_array("SELECT disc FROM order1 WHERE id = $t{enq1_id} and disc is not NULL");
$t{packing_charge} = $self->dbh->selectrow_array("SELECT packing_charge FROM order1 WHERE id = $t{enq1_id} and packing_charge is not NULL");
$t{delivery_charge} = $self->dbh->selectrow_array("SELECT delivery_charge FROM order1 WHERE id = $t{enq1_id} and delivery_charge is not NULL");
$t{total1} = $t{total1} + $t{disc} + $t{packing_charge} + $t{delivery_charge};
if ( $t{moneyid} == 1 ) {
$t{total1} = int($t{total1}+0.5);
} else {
$t{total1} = int(($t{total1}*100+0.5))/100;
$t{total1} = sprintf("%.02f",$t{total1});
}
$t{total1} = commify($t{total1});
$t{Y1} = $t{Y1} + 3;
if ( $t{disc} ) {
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = 'Disc';
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{XY1} = "G" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
$t{sheet}->Cells($t{Y1},"G")->{NumberFormatLocal} = "@";
if ( $t{moneyid} == 1 ) {
# $t{disc} = int($t{disc}+0.5);
} else {
$t{disc} = sprintf("%.02f",$t{disc});
}
$t{disc} = commify($t{disc});
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{disc};
$t{Y1}++;
$t{Y1}++;
}
if ( $t{packing_charge} ) {
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = 'Packing Charge';
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{XY1} = "G" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
$t{sheet}->Cells($t{Y1},"G")->{NumberFormatLocal} = "@";
$t{packing_charge} = commify($t{packing_charge});
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{packing_charge};
$t{XY1} = 'E' . $t{Y1};
$t{XY2} = 'F' . $t{Y1};
$t{sheet}->Range("$t{XY1}:$t{XY2}")->{MergeCells} = "True";
$t{Y1}++;
$t{Y1}++;
}
if ( $t{delivery_charge} ) {
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = 'Delivery Charge';
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{XY1} = "G" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
$t{sheet}->Cells($t{Y1},"G")->{NumberFormatLocal} = "@";
$t{delivery_charge} = commify($t{delivery_charge});
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{delivery_charge};
$t{XY1} = 'E' . $t{Y1};
$t{XY2} = 'F' . $t{Y1};
$t{sheet}->Range("$t{XY1}:$t{XY2}")->{MergeCells} = "True";
$t{Y1}++;
$t{Y1}++;
}
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "True";
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
# $t{sheet}->Cells($t{Y1},"E")->{'Value'} = Variant(VT_BSTR, $t{total});
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = $t{total};
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "True";
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{total1};
$t{XY1} = 'E' . $t{Y1};
$t{XY2} = 'F' . $t{Y1};
$t{sheet}->Range("$t{XY1}:$t{XY2}")->{MergeCells} = "True";
$t{XY1} = 'E' . $t{Y1};
$t{XY2} = 'G' . $t{Y1};
# $t{sheet}->Range("B5:D5")->Borders(xlEdgeBottom)->{LineStyle} = xlDouble;
$t{sheet}->Range("$t{XY1}:$t{XY2}")->Borders(xlEdgeBottom)->{LineStyle} = xlDouble;
} elsif ( $t{excel_pat} eq 'shipping' ) {
# 複数orderがある場合,追加出力を行う
$t{shipping} = $self->dbh->selectrow_array("SELECT shipping FROM order1 WHERE id = $t{enq1_id} and shipping is NOT NULL");
if ( $t{shipping} ) {
@{ $t{shippings} } = split(/=/,$t{shipping});
for $n ( 0 .. $#{ $t{shippings} } ) {
$t{shipping_enq1_id} = $t{shippings}[$n];
# OURREFを書く
$t{Y1} = $t{Y1} + 2;
$t{ourref1} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $t{shipping_enq1_id}");
$t{ourref1} = 'OURREF. ' . $t{ourref1};
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = $t{ourref1};
# ORDER NOを書く
$t{Y1}++;
$t{orderno} = $self->dbh->selectrow_array("SELECT orderno FROM order1 WHERE id = $t{shipping_enq1_id} and orderno is not NULL");
if ( !($t{orderno}) ) {
$t{orderno} = 'NO order NO.';
}
$t{orderno} = 'P.O. ' . $t{orderno};
$t{orderno} = decode("utf8",$t{orderno});
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{orderno});
# データを書く
($t{type1id},$t{partsid},$t{QTY}) = $self->dbh->selectrow_array("select type1id, partsid,QTY from enq1 where id = $t{shipping_enq1_id}");
@{ $t{type1s} } = split(/==/,$t{type1id});
@{ $t{part1} } = split(/==/,$t{partsid});
@{ $t{QT1} } = split(/==/,$t{QTY});
$t{pp1} = $self->dbh->selectrow_array("SELECT price FROM quo2 WHERE id = $t{shipping_enq1_id}");
@{ $t{pps} } = split(/=/,$t{pp1});
$t{NO} = 0;
for $n1 ( 0 .. $#{ $t{type1s} } ) {
$t{type2} = $t{type1s}[$n1];
$t{part2} = $t{part1}[$n1];
$t{QTY2} = $t{QT1}[$n1];
$t{Y1} = $t{Y1}++;
# 从main_type1中取出主机名,Serieal和DWG图号
($t{name},$t{series},$t{gname_id},$t{maker_id},$t{DWG}) = $self->dbh->selectrow_array("select name,series,gname_id,maker_id,DWG from main_type1 where id = $t{type2}");
@{ $t{DWGs} } = split(/=/,$t{DWG});
$t{gname} = $self->dbh->selectrow_array("select name from main_name1 where id = $t{gname_id}");
$t{maker} = $self->dbh->selectrow_array("select name from main_maker1 where id = $t{maker_id}");
# 写入主机的名称,type,厂家
$t{Y1}++;
$t{gname} = 'NAME: ' . $t{gname};
$t{gname} = decode("utf8",$t{gname});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{gname});
$t{Y1}++;
$t{type} = 'TYPE: ' . $t{name};
$t{type} = decode("utf8",$t{type});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{type});
$t{Y1}++;
$t{maker} = 'MAKER: ' . $t{maker};
$t{maker} = decode("utf8",$t{maker});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{maker});
$t{Y1}++;
$t{series} = 'SERIES: ' . $t{series};
$t{series} = decode("utf8",$t{series});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{series});
$t{Y1}++;
# 零件表名
$t{ptable} = sprintf("%06d",$t{type2});
$t{ptable} = 'a' . $t{ptable};
@{ $t{pid_list} } = split(/=/,$t{part2});
@{ $t{Q_list} } = split(/=/,$t{QTY2});
# 根据enq1的零件编号从数据库取出零件信息和所属图纸号
@{ $t{dwgs1} } = ();
for $n2 ( 0 .. $#{ $t{pid_list} } ) {
$t{pid1} = $t{pid_list}[$n2];
$t{Q1} = $t{Q_list}[$n2];
@{ $t{p1} } = $self->dbh->selectrow_array("select * from $t{ptable} where id = $t{pid1}");
$t{dwg1} = $t{p1}[3];
push(@{ $t{plist}{id}{$t{dwg1}} },$t{p1}[0]);
push(@{ $t{plist}{name}{$t{dwg1}} },$t{p1}[1]);
push(@{ $t{plist}{code}{$t{dwg1}} },$t{p1}[2]);
push(@{ $t{plist}{Nuid}{$t{dwg1}} },$t{p1}[4]);
push(@{ $t{plist}{QTY}{$t{dwg1}} },$t{Q1});
push(@{ $t{dwgs1} },$t{dwg1});
}
# 合并重复的图纸号
%seen = ();
@{ $t{dwgs2} } = ();
foreach $item (@{ $t{dwgs1} }) {
unless ( $seen{$item} ) {
$seen{$item} = 1;
push(@{ $t{dwgs2} },$item);
}
}
# 把数据写入EXCEL文件
for $n2 ( 0 .. $#{ $t{dwgs2} } ) {
$t{dwg1} = $t{dwgs2}[$n2];
$t{DWG1} = $t{DWGs}[$t{dwg1}-1];
# 取出图纸号
$t{Y1}++;
if ( $t{DWG1} eq 'XXXDWG' ) { # 没有图纸号的情况
$t{line1} = 'DWG. NO. ';
} else {
$t{line1} = 'DWG. NO. ' . $t{DWG1};
}
$t{DWG1} = decode("utf8",$t{line1});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{DWG1});
$t{PN1} = 'P/N';
$t{PN1} = decode("utf8",$t{PN1});
$t{sheet}->Cells($t{Y1},"C")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"C")->{'Value'} = Variant(VT_BSTR, $t{PN1});
# 处理零件
for $n3 ( 0 .. $#{ $t{plist}{id}{$t{dwg1}} } ) {
$t{NO}++; # enq1的所有Parts的编号
$t{pid1} = $t{plist}{id}{$t{dwg1}}[$n3];
$t{name1} = $t{plist}{name}{$t{dwg1}}[$n3];
$t{code1} = $t{plist}{code}{$t{dwg1}}[$n3];
$t{Q1} = $t{plist}{QTY}{$t{dwg1}}[$n3];
$t{Nuid1} = $t{plist}{Nuid}{$t{dwg1}}[$n3];
$t{NU1} = $self->dbh->selectrow_array("select parts_Unit from parts_nu where id = $t{Nuid1}");
$t{Y1}++;
$t{NO1} = decode("utf8",$t{NO});
$t{sheet}->Cells($t{Y1},"A")->{Font}->{Size} = "9";
$t{range} = "A" . $t{Y1};
with ($t{sheet}->Range($t{range}), HorizontalAlignment => xlCenter );
$t{sheet}->Cells($t{Y1},"A")->{'Value'} = Variant(VT_BSTR, $t{NO1});
if ( $t{name1} =~ /==/ ) {
@{ $t{names} } = split(/==/,$t{name1});
$t{name1} = $t{names}[0];
$t{name1} = decode("utf8",$t{name1});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{WrapText} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{name1});
# $t{code1} = decode("utf8",$t{code1});
$t{range} = "C" . $t{Y1};
with ($t{sheet}->Range($t{range}), HorizontalAlignment => xlLeft );
$t{sheet}->Cells($t{Y1},"C")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"C")->{NumberFormatLocal} = "@";
$t{sheet}->Cells($t{Y1},"C")->{'Value'} = $t{code1};
$t{sheet}->Cells($t{Y1},"D")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"D")->{'Value'} = Variant(VT_BSTR, $t{Q1});
$t{NU1} = decode("utf8",$t{NU1});
$t{range} = "E" . $t{Y1};
with ($t{sheet}->Range($t{range}), HorizontalAlignment => xlLeft );
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = Variant(VT_BSTR, $t{NU1});
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "False";
$t{pri1} = $t{pps}[$t{NO}-1]*$$e_ref{changeprice};
if ( $t{moneyid} == 1 ) {
} else {
$t{pri1} = sprintf("%.02f",$t{pri1});
}
$t{pri1c} = commify($t{pri1});
$t{sheet}->Cells($t{Y1},"F")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"F")->{'Value'} = $t{pri1c};
$t{XY1} = "F" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
$t{sheet}->Cells($t{Y1},"F")->{NumberFormatLocal} = "@";
$t{sheet}->Cells($t{Y1},"F")->{Font}->{Bold} = "False";
$t{pris} = $t{Q1}*$t{pri1};
if ( $t{moneyid} == 1 ) {
$t{pris} = int($t{pris}+0.5);
} else {
$t{pris} = int(($t{pris}*100+0.5))/100;
$t{pris} = sprintf("%.02f",$t{pris});
}
$t{total1} += $t{pris};
$t{pris} = commify($t{pris});
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{XY1} = "G" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
$t{sheet}->Cells($t{Y1},"G")->{NumberFormatLocal} = "@";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{pris};
for $n4 ( 1 .. $#{ $t{names} } ) {
$t{Y1}++;
$t{name1} = $t{names}[$n4];
$t{name1} = decode("utf8",$t{name1});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{name1});
}
} else {
$t{name1} = decode("utf8",$t{name1});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{WrapText} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{name1});
# $t{code1} = decode("utf8",$t{code1});
$t{range} = "C" . $t{Y1};
$t{sheet}->Cells($t{Y1},"C")->{Font}->{Size} = "9";
with ($t{sheet}->Range($t{range}), HorizontalAlignment => xlLeft );
$t{sheet}->Cells($t{Y1},"C")->{NumberFormatLocal} = "@";
$t{sheet}->Cells($t{Y1},"C")->{'Value'} = $t{code1};
$t{sheet}->Cells($t{Y1},"D")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"D")->{'Value'} = Variant(VT_BSTR, $t{Q1});
$t{NU1} = decode("utf8",$t{NU1});
$t{range} = "E" . $t{Y1};
with ($t{sheet}->Range($t{range}), HorizontalAlignment => xlLeft );
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = Variant(VT_BSTR, $t{NU1});
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "False";
$t{pri1} = $t{pps}[$t{NO}-1]*$$e_ref{changeprice};
if ( $t{moneyid} == 1 ) {
} else {
$t{pri1} = sprintf("%.02f",$t{pri1});
}
$t{pri1c} = commify($t{pri1});
$t{sheet}->Cells($t{Y1},"F")->{Font}->{Size} = "9";
$t{XY1} = "F" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
$t{sheet}->Cells($t{Y1},"F")->{NumberFormatLocal} = "@";
$t{sheet}->Cells($t{Y1},"F")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"F")->{'Value'} = $t{pri1c};
$t{pris} = $t{Q1}*$t{pri1};
if ( $t{moneyid} == 1 ) {
$t{pris} = int($t{pris}+0.5);
} else {
$t{pris} = int(($t{pris}*100+0.5))/100;
$t{pris} = sprintf("%.02f",$t{pris});
}
$t{total1} += $t{pris};
$t{pris} = commify($t{pris});
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{XY1} = "G" . $t{Y1};
with ($t{sheet}->Range($t{XY1}), HorizontalAlignment => xlRight );
$t{sheet}->Cells($t{Y1},"G")->{NumberFormatLocal} = "@";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{pris};
}
}
# 必须置零,因为下一台主机的DWG极有可能同名!
@{ $t{plist}{id}{$t{dwg1}} } = ();
@{ $t{plist}{name}{$t{dwg1}} } = ();
@{ $t{plist}{code}{$t{dwg1}} } = ();
@{ $t{plist}{QTY}{$t{dwg1}} } = ();
@{ $t{plist}{Nuid}{$t{dwg1}} } = ();
}
}
}
}
# まとめ出力
if ( $t{moneyid} == 1 ) {
$t{total1} = int($t{total1}+0.5);
} else {
$t{total1} = int(($t{total1}*100+0.5))/100;
$t{total1} = sprintf("%.02f",$t{total1});
}
$t{total1} = commify($t{total1});
$t{Y1} = $t{Y1} + 4;
# $t{total} = decode("utf8",$t{total});
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "True";
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
# $t{sheet}->Cells($t{Y1},"E")->{'Value'} = Variant(VT_BSTR, $t{total});
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = $t{total};
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "True";
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{total1};
$t{XY1} = 'E' . $t{Y1};
$t{XY2} = 'F' . $t{Y1};
$t{sheet}->Range("$t{XY1}:$t{XY2}")->{MergeCells} = "True";
$t{XY1} = 'E' . $t{Y1};
$t{XY2} = 'G' . $t{Y1};
# $t{sheet}->Range("B5:D5")->Borders(xlEdgeBottom)->{LineStyle} = xlDouble;
$t{sheet}->Range("$t{XY1}:$t{XY2}")->Borders(xlEdgeBottom)->{LineStyle} = xlDouble;
} elsif ($t{excel_pat} eq 'quo2' ) {
if ( $t{moneyid} == 1 ) {
$t{total1} = int($t{total1}+0.5);
} else {
$t{total1} = int(($t{total1}*100+0.5))/100;
$t{total1} = sprintf("%.02f",$t{total1});
}
$t{total1} = commify($t{total1});
$t{Y1} = $t{Y1} + 4;
# $t{total} = decode("utf8",$t{total});
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "True";
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
# $t{sheet}->Cells($t{Y1},"E")->{'Value'} = Variant(VT_BSTR, $t{total});
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = $t{total};
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"G")->{Font}->{Bold} = "True";
$t{sheet}->Cells($t{Y1},"G")->{'Value'} = $t{total1};
$t{XY1} = 'E' . $t{Y1};
$t{XY2} = 'F' . $t{Y1};
$t{sheet}->Range("$t{XY1}:$t{XY2}")->{MergeCells} = "True";
$t{XY1} = 'E' . $t{Y1};
$t{XY2} = 'G' . $t{Y1};
# $t{sheet}->Range("B5:D5")->Borders(xlEdgeBottom)->{LineStyle} = xlDouble;
$t{sheet}->Range("$t{XY1}:$t{XY2}")->Borders(xlEdgeBottom)->{LineStyle} = xlDouble;
}
# QTNにNOTEを出力
if ( $t{excel_pat} eq 'quo2' ) {
$t{memo} = $self->dbh->selectrow_array("select memo from quo2 where id = $t{enq1_id} and memo is not NULL");
if ( $t{memo} ) {
if ( $t{memo} =~ /==/ ) {
@{ $t{memos} } = split(/==/,$t{memo});
$t{Y1} = $t{Y1} + 1;
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"B")->{WrapText} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = $t{memos}[0];
for $n ( 1 .. $#{ $t{memos} } ) {
$t{Y1} = $t{Y1} + 1;
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"B")->{WrapText} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = $t{memos}[$n];
}
} else {
$t{Y1} = $t{Y1} + 1;
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"B")->{WrapText} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = $t{memo};
}
}
} elsif ( $t{excel_pat} eq 'enq2_order' ) {
$t{memo} = $self->dbh->selectrow_array("select memo from enq2 where id = $t{enq2_id} and memo is not NULL");
if ( $t{memo} ) {
$t{Y1} = $t{Y1} + 1;
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"B")->{WrapText} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = $t{memo};
}
} elsif ( $t{excel_pat} eq 'order1' ) {
$t{memo} = $self->dbh->selectrow_array("select memo from order1 where id = $t{enq1_id} and memo is not NULL");
if ( $t{memo} ) {
$t{Y1} = $t{Y1} + 1;
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"B")->{WrapText} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = $t{memo};
}
}
# 写入 複数オーダー,梱包情報
if ($t{excel_pat} eq 'packing') {
# 複数orderがある場合,追加出力を行う
$t{packing} = $self->dbh->selectrow_array("SELECT packing FROM order1 WHERE id = $t{enq1_id} and packing is NOT NULL");
if ( $t{packing} ) {
@{ $t{packings} } = split(/=/,$t{packing});
for $n ( 0 .. $#{ $t{packings} } ) {
$t{packing_enq1_id} = $t{packings}[$n];
# OURREFを書く
$t{Y1} = $t{Y1} + 2;
$t{ourref1} = $self->dbh->selectrow_array("SELECT ourref FROM enq1 WHERE id = $t{packing_enq1_id}");
$t{ourref1} = 'OURREF. ' . $t{ourref1};
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = $t{ourref1};
# ORDER NOを書く
$t{Y1}++;
$t{orderno} = $self->dbh->selectrow_array("SELECT orderno FROM order1 WHERE id = $t{packing_enq1_id} and orderno is not NULL");
if ( !($t{orderno}) ) {
$t{orderno} = 'NO order NO.';
}
$t{orderno} = 'P.O. ' . $t{orderno};
$t{orderno} = decode("utf8",$t{orderno});
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{orderno});
# データを書く
($t{type1id},$t{partsid},$t{QTY}) = $self->dbh->selectrow_array("select type1id, partsid,QTY from enq1 where id = $t{packing_enq1_id}");
@{ $t{type1s} } = split(/==/,$t{type1id});
@{ $t{part1} } = split(/==/,$t{partsid});
@{ $t{QT1} } = split(/==/,$t{QTY});
$t{pp1} = $self->dbh->selectrow_array("SELECT price FROM quo2 WHERE id = $t{packing_enq1_id}");
@{ $t{pps} } = split(/=/,$t{pp1});
$t{NO} = 0;
for $n1 ( 0 .. $#{ $t{type1s} } ) {
$t{type2} = $t{type1s}[$n1];
$t{part2} = $t{part1}[$n1];
$t{QTY2} = $t{QT1}[$n1];
$t{Y1} = $t{Y1}++;
# 从main_type1中取出主机名,Serieal和DWG图号
($t{name},$t{series},$t{gname_id},$t{maker_id},$t{DWG}) = $self->dbh->selectrow_array("select name,series,gname_id,maker_id,DWG from main_type1 where id = $t{type2}");
@{ $t{DWGs} } = split(/=/,$t{DWG});
$t{gname} = $self->dbh->selectrow_array("select name from main_name1 where id = $t{gname_id}");
$t{maker} = $self->dbh->selectrow_array("select name from main_maker1 where id = $t{maker_id}");
# 写入主机的名称,type,厂家
$t{Y1}++;
$t{gname} = 'NAME: ' . $t{gname};
$t{gname} = decode("utf8",$t{gname});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{gname});
$t{Y1}++;
$t{type} = 'TYPE: ' . $t{name};
$t{type} = decode("utf8",$t{type});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{type});
$t{Y1}++;
$t{maker} = 'MAKER: ' . $t{maker};
$t{maker} = decode("utf8",$t{maker});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{maker});
$t{Y1}++;
$t{series} = 'SERIES: ' . $t{series};
$t{series} = decode("utf8",$t{series});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{series});
$t{Y1}++;
# 零件表名
$t{ptable} = sprintf("%06d",$t{type2});
$t{ptable} = 'a' . $t{ptable};
@{ $t{pid_list} } = split(/=/,$t{part2});
@{ $t{Q_list} } = split(/=/,$t{QTY2});
# 根据enq1的零件编号从数据库取出零件信息和所属图纸号
@{ $t{dwgs1} } = ();
for $n2 ( 0 .. $#{ $t{pid_list} } ) {
$t{pid1} = $t{pid_list}[$n2];
$t{Q1} = $t{Q_list}[$n2];
@{ $t{p1} } = $self->dbh->selectrow_array("select * from $t{ptable} where id = $t{pid1}");
$t{dwg1} = $t{p1}[3];
push(@{ $t{plist}{id}{$t{dwg1}} },$t{p1}[0]);
push(@{ $t{plist}{name}{$t{dwg1}} },$t{p1}[1]);
push(@{ $t{plist}{code}{$t{dwg1}} },$t{p1}[2]);
push(@{ $t{plist}{Nuid}{$t{dwg1}} },$t{p1}[4]);
push(@{ $t{plist}{QTY}{$t{dwg1}} },$t{Q1});
push(@{ $t{dwgs1} },$t{dwg1});
}
# 合并重复的图纸号
%seen = ();
@{ $t{dwgs2} } = ();
foreach $item (@{ $t{dwgs1} }) {
unless ( $seen{$item} ) {
$seen{$item} = 1;
push(@{ $t{dwgs2} },$item);
}
}
# 把数据写入EXCEL文件
for $n2 ( 0 .. $#{ $t{dwgs2} } ) {
$t{dwg1} = $t{dwgs2}[$n2];
$t{DWG1} = $t{DWGs}[$t{dwg1}-1];
# 取出图纸号
$t{Y1}++;
if ( $t{DWG1} eq 'XXXDWG' ) { # 没有图纸号的情况
$t{line1} = 'DWG. NO. ';
} else {
$t{line1} = 'DWG. NO. ' . $t{DWG1};
}
$t{DWG1} = decode("utf8",$t{line1});
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{DWG1});
$t{PN1} = 'P/N';
$t{PN1} = decode("utf8",$t{PN1});
$t{sheet}->Cells($t{Y1},"C")->{'Value'} = Variant(VT_BSTR, $t{PN1});
# 处理零件
for $n3 ( 0 .. $#{ $t{plist}{id}{$t{dwg1}} } ) {
$t{NO}++; # enq1的所有Parts的编号
$t{pid1} = $t{plist}{id}{$t{dwg1}}[$n3];
$t{name1} = $t{plist}{name}{$t{dwg1}}[$n3];
$t{code1} = $t{plist}{code}{$t{dwg1}}[$n3];
$t{Q1} = $t{plist}{QTY}{$t{dwg1}}[$n3];
$t{Nuid1} = $t{plist}{Nuid}{$t{dwg1}}[$n3];
$t{NU1} = $self->dbh->selectrow_array("select parts_Unit from parts_nu where id = $t{Nuid1}");
$t{Y1}++;
$t{NO1} = decode("utf8",$t{NO});
$t{sheet}->Cells($t{Y1},"A")->{'Value'} = Variant(VT_BSTR, $t{NO1});
if ( $t{name1} =~ /==/ ) {
@{ $t{names} } = split(/==/,$t{name1});
$t{name1} = $t{names}[0];
$t{name1} = decode("utf8",$t{name1});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{WrapText} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{name1});
# $t{code1} = decode("utf8",$t{code1});
$t{range} = "C" . $t{Y1};
with ($t{sheet}->Range($t{range}), HorizontalAlignment => xlLeft );
$t{sheet}->Cells($t{Y1},"C")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"C")->{NumberFormatLocal} = "@";
$t{sheet}->Cells($t{Y1},"C")->{'Value'} = $t{code1};
$t{sheet}->Cells($t{Y1},"D")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"D")->{'Value'} = Variant(VT_BSTR, $t{Q1});
$t{NU1} = decode("utf8",$t{NU1});
$t{range} = "E" . $t{Y1};
with ($t{sheet}->Range($t{range}), HorizontalAlignment => xlLeft );
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = Variant(VT_BSTR, $t{NU1});
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "False";
for $n4 ( 1 .. $#{ $t{names} } ) {
$t{Y1}++;
$t{name1} = $t{names}[$n4];
$t{name1} = decode("utf8",$t{name1});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{name1});
}
} else {
$t{name1} = decode("utf8",$t{name1});
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{WrapText} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = Variant(VT_BSTR, $t{name1});
# $t{code1} = decode("utf8",$t{code1});
$t{range} = "C" . $t{Y1};
$t{sheet}->Cells($t{Y1},"C")->{Font}->{Size} = "9";
with ($t{sheet}->Range($t{range}), HorizontalAlignment => xlLeft );
$t{sheet}->Cells($t{Y1},"C")->{NumberFormatLocal} = "@";
$t{sheet}->Cells($t{Y1},"C")->{'Value'} = $t{code1};
$t{sheet}->Cells($t{Y1},"D")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"D")->{'Value'} = Variant(VT_BSTR, $t{Q1});
$t{NU1} = decode("utf8",$t{NU1});
$t{range} = "E" . $t{Y1};
with ($t{sheet}->Range($t{range}), HorizontalAlignment => xlLeft );
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"E")->{'Value'} = Variant(VT_BSTR, $t{NU1});
$t{sheet}->Cells($t{Y1},"E")->{Font}->{Bold} = "False";
}
}
# 必须置零,因为下一台主机的DWG极有可能同名!
@{ $t{plist}{id}{$t{dwg1}} } = ();
@{ $t{plist}{name}{$t{dwg1}} } = ();
@{ $t{plist}{code}{$t{dwg1}} } = ();
@{ $t{plist}{QTY}{$t{dwg1}} } = ();
@{ $t{plist}{Nuid}{$t{dwg1}} } = ();
}
}
}
}
$t{packing_info} = $self->dbh->selectrow_array("select packing_info from order1 where id = $t{enq1_id} and packing_info is not NULL");
if ( $t{packing_info} ) {
$t{Y1} = $t{Y1} + 2;
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"B")->{WrapText} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = $t{packing_info};
}
}
# 写入 Bank account
if ($t{excel_pat} eq 'invoice') {
$t{bank_line1} = " Please settle the invoicing amount by T.T.";
$t{bank_line2} = " remittance to our bank account:";
$t{bank_line3} = " Sumitomo Mitsui Banking Corporation";
$t{bank_line4} = " Nagata Branch A/C No.: 7480364";
$t{bank_line5} = " Beneficiary: MSC CORPORATION";
$t{bank_line6} = " bank swift code: SMBCJPJT";
$t{Y1} = $t{Y1};
$t{XY1} = 'B' . $t{Y1};
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = $t{bank_line1};
$t{Y1} = $t{Y1} + 1;
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Bold} = "False";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = $t{bank_line2};
$t{Y1} = $t{Y1} + 2;
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Bold} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = $t{bank_line3};
$t{Y1} = $t{Y1} + 1;
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Bold} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = $t{bank_line4};
$t{Y1} = $t{Y1} + 1;
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Bold} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = $t{bank_line5};
$t{Y1} = $t{Y1} + 1;
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Size} = "9";
$t{sheet}->Cells($t{Y1},"B")->{Font}->{Bold} = "True";
$t{sheet}->Cells($t{Y1},"B")->{'Value'} = $t{bank_line6};
$t{XY2} = 'B' . $t{Y1};
with ($t{sheet}->Range("$t{XY1}:$t{XY2}")->Borders(xlEdgeLeft),
Weight => xlMedium );
with ($t{sheet}->Range("$t{XY1}:$t{XY2}")->Borders(xlEdgeTop),
Weight => xlMedium );
with ($t{sheet}->Range("$t{XY1}:$t{XY2}")->Borders(xlEdgeBottom),
Weight => xlMedium );
with ($t{sheet}->Range("$t{XY1}:$t{XY2}")->Borders(xlEdgeRight),
Weight => xlMedium );
# $t{sheet}->Range("$t{XY1}:$t{XY2}")->Borders(xlEdgeBottom)->{LineStyle} = xlLine;
}
# Save the excel file
$t{excel} = decode("utf8",$t{excel});
$book->SaveAs("C:\\wwwexcel\\output\\$t{excel}");
# 关闭EXCEL
undef $book;
undef $t{ex};
return($e_ref,$self);
}
1;
]]>
返回