webadmin.php

/var/www/enzatesting.onesolution.hk/01_20240711_full_backup/xls_ivcrpt_template7.php

<?php
$formid = "Order";
require_once "inc/configure.php";
require_once "inc/phpexcel_1.7.7/PHPExcel.php";
//$rpt_lang = 'sc';
define('RPT_LANG', 'sc');

if(havePermission("GNr")==false
|| havePermission("DPr")==false
//|| havePermission("MDr")==false
//|| havePermission("TDr")==false
){

    myerror("Invalid Permission");
}

//--------temp function-------------------
function getPAKOMetalWt($txpako_refid){
    $pako = get_txpako($txpako_refid);
    return $pako['metal_wt'];
}
// end of temp function-------------

function getAllMetal($orddtl_refid){
    global $dbh;
    $result = array();
    $main_mat = '';
    $matcolor = '';
    //build description based on BOM
    $sql = "SELECT
dbo.ord_item_bom.orddtl_refid,
dbo.ord_item_bom.bomcategy,
dbo.ord_item_bom.qty,
dbo.ord_item_bom.actual_qty,
dbo.ord_item_bom.uom_qty,
dbo.ord_item_bom.itemno,
dbo.ord_item_bom.itemnoid,
dbo.inv_material.name_en,
dbo.inv_material.name_sc,
dbo.inv_material.mattype,
dbo.inv_material.matcolor,
dbo.ord_item_bom.uom_wt,
dbo.ord_dtl.qty as ordqty

FROM
dbo.ord_item_bom
INNER JOIN dbo.inv_material ON dbo.ord_item_bom.itemnoid = dbo.inv_material.refid
INNER JOIN dbo.ord_dtl ON dbo.ord_item_bom.orddtl_refid = dbo.ord_dtl.refid
WHERE
dbo.ord_item_bom.orddtl_refid = :orddtl_refid AND
dbo.ord_item_bom.bomcategy = 'Material'
ORDER BY
dbo.ord_item_bom.qty DESC";
    $sth = $dbh->prepare($sql);
    $sth->execute( array(':orddtl_refid' => $orddtl_refid) );
     //echo $sth->getSQL( array(':orddtl_refid' => $orddtl_refid) );
    $i = 0;
    while($row = $sth->fetch()){
        $acc_wt = 0;
        if( empty($main_mat) ){ //register as main material
            $main_mat = $row['mattype'];
            $matcolor = $row['matcolor'];

            //accseeory using main material
            $sql2 = "SELECT
            dbo.ord_item_bom.orddtl_refid,
            dbo.ord_item_bom.bomcategy,
            dbo.ord_item_bom.qty,
            dbo.ord_item_bom.actual_qty,
            dbo.ord_item_bom.uom_qty,
            dbo.ord_item_bom.itemno,
            dbo.ord_item_bom.itemnoid,
            dbo.inv_accessory.name_en,
            dbo.inv_accessory.name_sc,
            dbo.inv_accessory.accstype,
            dbo.inv_accessory.accscategy,
            dbo.inv_accessory.mattype,
            dbo.inv_accessory.matcolor,
            dbo.inv_accessory.finishing,
            dbo.inv_accessory.weight,
            dbo.inv_accessory.uom_wt,
            dbo.ord_dtl.qty as ordqty

            FROM
            dbo.ord_item_bom
            INNER JOIN dbo.inv_accessory ON dbo.ord_item_bom.itemnoid = dbo.inv_accessory.refid
            INNER JOIN dbo.ord_dtl ON dbo.ord_item_bom.orddtl_refid = dbo.ord_dtl.refid
            WHERE
            dbo.ord_item_bom.orddtl_refid = :orddtl_refid AND
            dbo.ord_item_bom.bomcategy = 'Accessory' AND
            dbo.inv_accessory.mattype = :mattype AND
            dbo.inv_accessory.matcolor = :matcolor
            ORDER BY
            dbo.ord_item_bom.qty DESC
            ";

            $sth2 = $dbh->prepare($sql2);
            $sth2->execute( array(':orddtl_refid' => $orddtl_refid, ':mattype' => $main_mat, ':matcolor' => $matcolor) );
            //echo $sth2->getSQL( array(':orddtl_refid' => $orddtl_refid, ':mattype' => $main_mat, ':matcolor' => $matcolor) );
            while($row2 = $sth2->fetch()){
                $acc_wt += $row2['actual_qty'];
            }
        }
        $result[$i]['itemno'] = $row['itemno'];
        //$result[$i]['type'] = resolveCode_master_type_code('MATTYPE', $row['mattype'], false, 'en').' '.$row['matcolor'];
                                            //resolveCode_master_type_code('MATCOLOR', $row['matcolor'], false, 'en');
        $result[$i]['weight'] = ($row['actual_qty'] +$acc_wt);
        $result[$i]['uom_wt'] = $row['uom_wt'];
        $i++;
    }


    $sql = "SELECT
dbo.ord_item_bom.orddtl_refid,
dbo.ord_item_bom.bomcategy,
dbo.ord_item_bom.qty,
dbo.ord_item_bom.actual_qty,
dbo.ord_item_bom.uom_qty,
dbo.ord_item_bom.itemno,
dbo.ord_item_bom.itemnoid,
dbo.inv_accessory.name_en,
dbo.inv_accessory.name_sc,
dbo.inv_accessory.accstype,
dbo.inv_accessory.accscategy,
dbo.inv_accessory.mattype,
dbo.inv_accessory.matcolor,
dbo.inv_accessory.finishing,
dbo.inv_accessory.weight,
dbo.inv_accessory.uom_wt,
dbo.ord_dtl.qty as ordqty

FROM
dbo.ord_item_bom
INNER JOIN dbo.inv_accessory ON dbo.ord_item_bom.itemnoid = dbo.inv_accessory.refid
INNER JOIN dbo.ord_dtl ON dbo.ord_item_bom.orddtl_refid = dbo.ord_dtl.refid
WHERE
dbo.ord_item_bom.orddtl_refid = :orddtl_refid AND
dbo.ord_item_bom.bomcategy = 'Accessory' AND
(dbo.inv_accessory.mattype <> :mattype OR dbo.inv_accessory.matcolor <> :matcolor)
ORDER BY
dbo.ord_item_bom.qty DESC
";
    $sth = $dbh->prepare($sql);
    $sth->execute( array(':orddtl_refid' => $orddtl_refid, ':mattype' => $main_mat, ':matcolor'=>$matcolor) );
     //echo $sth->getSQL( array(':orddtl_refid' => $orddtl_refid, ':mattype' => $main_mat, ':matcolor'=>$matcolor) );
    while($row = $sth->fetch()){
        $result[$i]['itemno'] = $row['itemno'];
        //$result[$i]['type'] = resolveCode_master_type_code('MATTYPE', $row['mattype'], false, 'en').' '.$row['matcolor'];
                                            //resolveCode_master_type_code('MATCOLOR', $row['matcolor'], false, 'en');
        $result[$i]['weight'] = $row['actual_qty'];
        $result[$i]['uom_wt'] = $row['uom_wt'];
        $i++;
    }
    //vdump($result);
    return $result;
}

function getAllMetallAL($orddtl_refid){
    global $dbh;
    $result = array();
    $main_mat = '';
    $matcolor = '';
    //build description based on BOM
    $sql = "SELECT
dbo.ord_item_bom.orddtl_refid,
dbo.ord_item_bom.bomcategy,
dbo.ord_item_bom.qty,
dbo.ord_item_bom.actual_qty,
dbo.ord_item_bom.uom_qty,
dbo.ord_item_bom.itemno,
dbo.ord_item_bom.itemnoid,
dbo.inv_material.name_en,
dbo.inv_material.name_sc,
dbo.inv_material.mattype,
dbo.inv_material.matcolor,
dbo.ord_item_bom.uom_wt,
dbo.ord_dtl.qty as ordqty

FROM
dbo.ord_item_bom
INNER JOIN dbo.inv_material ON dbo.ord_item_bom.itemnoid = dbo.inv_material.refid
INNER JOIN dbo.ord_dtl ON dbo.ord_item_bom.orddtl_refid = dbo.ord_dtl.refid
WHERE
dbo.ord_item_bom.orddtl_refid = :orddtl_refid AND
dbo.ord_item_bom.bomcategy = 'Material'
ORDER BY
dbo.ord_item_bom.qty DESC";
    $sth = $dbh->prepare($sql);
    $sth->execute( array(':orddtl_refid' => $orddtl_refid) );
     //echo $sth->getSQL( array(':orddtl_refid' => $orddtl_refid) );
    $f = 0;
    while($row = $sth->fetch()){
        $acc_wt = 0;
        if( empty($main_mat) ){ //register as main material
            $main_mat = $row['mattype'];
            $matcolor = $row['matcolor'];

            //accessory using main material
            $sql2 = "SELECT
            dbo.ord_item_bom.orddtl_refid,
            dbo.ord_item_bom.bomcategy,
            dbo.ord_item_bom.qty,
            dbo.ord_item_bom.actual_qty,
            dbo.ord_item_bom.uom_qty,
            dbo.ord_item_bom.itemno,
            dbo.ord_item_bom.itemnoid,
            dbo.inv_accessory.name_en,
            dbo.inv_accessory.name_sc,
            dbo.inv_accessory.accstype,
            dbo.inv_accessory.accscategy,
            dbo.inv_accessory.mattype,
            dbo.inv_accessory.matcolor,
            dbo.inv_accessory.finishing,
            dbo.inv_accessory.weight, 
            dbo.inv_accessory.uom_wt,
            dbo.ord_dtl.qty as ordqty

            FROM
            dbo.ord_item_bom
            INNER JOIN dbo.inv_accessory ON dbo.ord_item_bom.itemnoid = dbo.inv_accessory.refid
            INNER JOIN dbo.ord_dtl ON dbo.ord_item_bom.orddtl_refid = dbo.ord_dtl.refid
            WHERE
            dbo.ord_item_bom.orddtl_refid = :orddtl_refid AND
            dbo.ord_item_bom.bomcategy = 'Accessory' AND
            dbo.inv_accessory.mattype = :mattype AND
            dbo.inv_accessory.matcolor = :matcolor
            ORDER BY
            dbo.ord_item_bom.qty DESC
            ";

            $sth2 = $dbh->prepare($sql2);
            $sth2->execute( array(':orddtl_refid' => $orddtl_refid, ':mattype' => $main_mat, ':matcolor' => $matcolor) );
            //echo $sth2->getSQL( array(':orddtl_refid' => $orddtl_refid, ':mattype' => $main_mat, ':matcolor' => $matcolor) );
            while($row2 = $sth2->fetch()){
                $acc_wt = $row2['actual_qty'];
            }
        }
        $result[$f]['itemno'] = $row['itemno'];
        $result[$f]['type'] = resolveCode_master_type_code('MATTYPE', $row['mattype'], false, 'en').' '.$row['matcolor'];
                                            resolveCode_master_type_code('MATCOLOR', $row['matcolor'], false, 'en');
        // $result[$f]['weight'] = ($row['actual_qty'] );
        $result[$f]['weight'] = numf($row['actual_qty'] / (($row['ordqty']<1)?1:$row['ordqty']),3);                                                                                             
        $result[$f]['uom_wt'] = $row['uom_wt'];
        $f++;
    }

    $sql = "SELECT
dbo.ord_item_bom.orddtl_refid,
dbo.ord_item_bom.bomcategy,
dbo.ord_item_bom.qty,
dbo.ord_item_bom.actual_qty,
dbo.ord_item_bom.uom_qty,
dbo.ord_item_bom.itemno,
dbo.ord_item_bom.itemnoid,
dbo.inv_accessory.name_en,
dbo.inv_accessory.name_sc,
dbo.inv_accessory.accstype,
dbo.inv_accessory.accscategy,
dbo.inv_accessory.mattype,
dbo.inv_accessory.matcolor,
dbo.inv_accessory.finishing,
dbo.inv_accessory.weight,
dbo.inv_accessory.uom_wt,
dbo.ord_dtl.qty as ordqty

FROM
dbo.ord_item_bom
INNER JOIN dbo.inv_accessory ON dbo.ord_item_bom.itemnoid = dbo.inv_accessory.refid
INNER JOIN dbo.ord_dtl ON dbo.ord_item_bom.orddtl_refid = dbo.ord_dtl.refid
WHERE
dbo.ord_item_bom.orddtl_refid = :orddtl_refid AND
dbo.ord_item_bom.bomcategy = 'Accessory' AND
(dbo.inv_accessory.mattype <> :mattype OR dbo.inv_accessory.matcolor <> :matcolor)
ORDER BY
dbo.ord_item_bom.qty DESC
";
    $sth = $dbh->prepare($sql);
    $sth->execute( array(':orddtl_refid' => $orddtl_refid, ':mattype' => $main_mat, ':matcolor'=>$matcolor) );
     //echo $sth->getSQL( array(':orddtl_refid' => $orddtl_refid, ':mattype' => $main_mat, ':matcolor'=>$matcolor) );
    while($row = $sth->fetch()){
        $acc_wt = $row['actual_qty'];
        $result[$f]['itemno'] = $row['itemno'];
        $result[$f]['type'] = resolveCode_master_type_code('MATTYPE', $row['mattype'], false, 'en').' '.$row['matcolor'];
                                            resolveCode_master_type_code('MATCOLOR', $row['matcolor'], false, 'en');
        // $result[$f]['weight'] = ($row['actual_qty']);
        $result[$f]['weight'] = numf($row['actual_qty'] / (($row['ordqty']<1)?1:$row['ordqty']),3);                                                                                                                     
        $result[$f]['uom_wt'] = $row['uom_wt'];
        $f++;
    }
    //vdump($result);
    return $result;
}

function getAllStone($orddtl_refid){
    global $dbh;

    $aBOM = array();
    $allBOM = array();
    $sql = "SELECT
dbo.ord_item_bom.orddtl_refid,
dbo.ord_item_bom.bomcategy,
dbo.ord_item_bom.qty,
dbo.ord_item_bom.actual_qty,
dbo.ord_item_bom.uom_qty,
dbo.ord_item_bom.itemno,
dbo.ord_item_bom.itemnoid,
dbo.inv_stone.name_en,
dbo.inv_stone.name_sc,
dbo.inv_stone.stonetype,
dbo.inv_stone.stonecolor,
dbo.inv_stone.stoneqlty,
dbo.inv_stone.weight,
dbo.inv_stone.uom_wt,
dbo.inv_stone.uom_unitwt,
dbo.ord_dtl.qty as ordqty

FROM
dbo.ord_item_bom
INNER JOIN dbo.inv_stone ON dbo.ord_item_bom.itemnoid = dbo.inv_stone.refid
INNER JOIN dbo.ord_dtl ON dbo.ord_item_bom.orddtl_refid = dbo.ord_dtl.refid
WHERE
dbo.ord_item_bom.orddtl_refid = :orddtl_refid AND
dbo.ord_item_bom.bomcategy = 'Stone'
ORDER BY
dbo.ord_item_bom.qty DESC
";
    $sth = $dbh->prepare($sql);
    $sth->execute( array(':orddtl_refid' => $orddtl_refid) );
     //echo $sth->getSQL( array(':orddtl_refid' => $orddtl_refid) );
    while($row = $sth->fetch()){
        $aBOM['itemdesc'] = resolveCode_master_type_code('STONECOLOR', $row['stonecolor'], false, RPT_LANG). ' '.
                resolveCode_master_type_code('STONETYPE', $row['stonetype'], false, RPT_LANG);
        $aBOM['qty'] = numf($row['qty'] * ($row['ordqty']<1 ? $row['ordqty']:1));
        $aBOM['actual_qty'] = numf($row['actual_qty'] / (($row['ordqty']<1)?1:$row['ordqty']),3);
        $aBOM['uom_wt'] = $row['uom_wt'];
        $allBOM[] = $aBOM;
    }

    //graded Diamond
    $sql = "SELECT
Sum(dbo.ord_item_bom.qty) as qty,
Sum(dbo.ord_item_bom.actual_qty) AS actual_qty,
dbo.inv_diamond.stoneqlty,
dbo.ord_dtl.qty AS ordqty,
dbo.inv_diamond.uom_wt,
dbo.ord_item_bom.uom_qty

FROM
dbo.ord_item_bom
INNER JOIN dbo.inv_diamond ON dbo.inv_diamond.refid = dbo.ord_item_bom.itemnoid
INNER JOIN dbo.ord_dtl ON dbo.ord_item_bom.orddtl_refid = dbo.ord_dtl.refid
WHERE
dbo.ord_item_bom.orddtl_refid = :orddtl_refid AND
dbo.ord_item_bom.bomcategy = 'Diamond' AND
(dbo.inv_diamond.stoneqlty <> 'XX' AND dbo.inv_diamond.stoneqlty<>'@@')
GROUP BY
dbo.inv_diamond.uom_wt,
dbo.inv_diamond.stoneqlty,
dbo.ord_item_bom.uom_qty,
dbo.ord_dtl.qty
ORDER BY
actual_qty DESC
";
    $sth = $dbh->prepare($sql);
    $sth->execute( array(':orddtl_refid' => $orddtl_refid) );
     //echo $sth->getSQL( array(':orddtl_refid' => $orddtl_refid) );
    while($row = $sth->fetch()){
        $aBOM['itemdesc'] = resolveCode_master_type_code('STONEQLTY', $row['stoneqlty'], false, RPT_LANG). '钻石';
        $aBOM['qty'] = numf($row['qty'] * ($row['ordqty']<1 ? $row['ordqty']:1));
        $aBOM['actual_qty'] = numf($row['actual_qty'] / (($row['ordqty']<1)?1:$row['ordqty']),3);
        $aBOM['uom_wt'] = $row['uom_wt'];
        $allBOM[] = $aBOM;
    }

    //unknown graded Diamond
    $sql = "SELECT
Sum(dbo.ord_item_bom.qty) as qty,
Sum(dbo.ord_item_bom.actual_qty) AS actual_qty,
dbo.inv_diamond.stonecolor,
dbo.ord_dtl.qty AS ordqty,
dbo.inv_diamond.uom_wt,
dbo.ord_item_bom.uom_qty
FROM
dbo.ord_item_bom
INNER JOIN dbo.inv_diamond ON dbo.inv_diamond.refid = dbo.ord_item_bom.itemnoid
INNER JOIN dbo.ord_dtl ON dbo.ord_item_bom.orddtl_refid = dbo.ord_dtl.refid
WHERE
dbo.ord_item_bom.orddtl_refid = :orddtl_refid AND
dbo.ord_item_bom.bomcategy = 'Diamond' AND
(dbo.inv_diamond.stoneqlty = 'XX' OR dbo.inv_diamond.stoneqlty = '@@')
GROUP BY
dbo.inv_diamond.uom_wt,
dbo.inv_diamond.stonecolor,
dbo.ord_item_bom.uom_qty,
dbo.ord_dtl.qty
ORDER BY
actual_qty DESC
";
    $sth = $dbh->prepare($sql);
    $sth->execute( array(':orddtl_refid' => $orddtl_refid) );
     //echo $sth->getSQL( array(':orddtl_refid' => $orddtl_refid) );
    while($row = $sth->fetch()){
        $aBOM['itemdesc'] = resolveCode_master_type_code('STONECOLOR', $row['stonecolor'], false, RPT_LANG). '钻石';
        $aBOM['qty'] = numf($row['qty'] * ($row['ordqty']<1 ? $row['ordqty']:1));
        $aBOM['actual_qty'] = numf($row['actual_qty'] / (($row['ordqty']<1)?1:$row['ordqty']),3);
        $aBOM['uom_wt'] = $row['uom_wt'];
        $allBOM[] = $aBOM;
    }

    return $allBOM;
}

function getMainMat($orddtl_refid){
    global $dbh;

    $sql = "SELECT top 1
            dbo.inv_material.mattype

            FROM
            dbo.ord_item_bom
            INNER JOIN dbo.inv_material ON dbo.ord_item_bom.itemnoid = dbo.inv_material.refid
            WHERE
            dbo.ord_item_bom.orddtl_refid = :orddtl_refid AND
            dbo.ord_item_bom.bomcategy = 'Material'
            ORDER BY
            dbo.ord_item_bom.qty DESC
            ";
    $sth = $dbh->prepare($sql);
    $sth->execute( array(':orddtl_refid' => $orddtl_refid) );
     //echo $sth->getSQL( array(':orddtl_refid' => $orddtl_refid) );
    //return $sth->fetch(PDO::FETCH_ASSOC);
    $row=$sth->fetch(PDO::FETCH_ASSOC);
    return $row['mattype'];
}

function getmatdata($bom){
/*    if($bom['bomcategy']=='accessory'){
        $a['weight'] = $bom['weight'];
        $a['price'] = $a['weight'] * $bom['unitprice'];
    }elseif($bom['bomcategy']=='material'){
        $a['weight'] = $bom['weight'] * $bom['qty'];
        $a['price'] = $a['weight'] * $bom['unitprice'];
    }*/
    $a['weight'] = $bom['weight'] * $bom['qty'];
    $a['price'] = $a['weight'] * $bom['unitprice'];
    return $a;
}

function ivc_summary(&$ttl_var, $row2){
    $ttl_var['amount'] += $row2['unitprice'];
    $ttl_var['qty'] += $row2['qty'];
    $ttl_var['product_wt'] += $row2['product_wt'];
    $ttl_var['dia_wt'] += $row2['dia_wt'];
    $ttl_var['stone_wt'] += $row2['stone_wt'];

/*    $bomlist = getMainMat( $row2['orddtl_refid'] );
    vdump($bomlist);
    if(is_array($bomlist)){
        foreach($bomlist as $bom){
            vdump($bom['mattype']);
            $ttl_var['metal_wt'][$bom['mattype']] += $row2['metal_wt'];
        }
    }*/
    $mainMat = getMainMat( $row2['orddtl_refid'] );
    $ttl_var['metal_wt'][$mainMat] += $row2['metal_wt'];

    //vdump($ttl_var);
    return $ttl_var;
}

/*--------------------------------END OF FUNCTIONS--------------------------------------*/

$refid = setDefaultReqVar("refid", '');

$refid = (int)setDefaultReqVar("refid", '');
//$editable = setDefaultReqVar("edit", 0);

if($refid){
    $sql = "SELECT * from ivc_cus WHERE refid = :refid";
    $sth = $dbh->prepare($sql);
    $sth->execute(  array(':refid' => $refid) );
} else {
    exit;
}
$row = $sth->fetch();
$header = $row;
$customer = getDB_master_customer($row['ivc_customer']);
unset($sth);

//TODO: check customer currency is the same as the invoice

@set_time_limit(0);
//ini_set('max_execution_time', '999');
ini_set('post_max_size', '10M');
ini_set('memory_limit','1024M');

$objPHPexcel = PHPExcel_IOFactory::load('templates/ivcrpt_template7.xls');

$objWorksheet = $objPHPexcel->getSheetByName('Invoice');

$default_rowh = 16;//16pt~21pixel

//chinese label
$objWorksheet->getCell("A2")->setValue("客戶名稱:");
$objWorksheet->getCell("A6")->setValue("收貨地址:");
$objWorksheet->getCell("K2")->setValue("发票日期:");
$objWorksheet->getCell("K3")->setValue("发票號碼:");
$objWorksheet->getCell("K6")->setValue("運送公司:");
$objWorksheet->getCell("K7")->setValue("運送單號碼:");

/*
#inv_company_name        A3
#inv_company_address    A4
#del_company_name        A7
#del_company_address    A8

#launch_date            M2
#invoice_nbr            M3
#transporter            M6
#tracking_number        M7
*/
$objWorksheet->getCell('A3')->setValue( html_entity_decode($customer['companyname_en'], ENT_QUOTES) );
$objWorksheet->getCell('A4')->setValue( html_entity_decode(buildAddressFormat($customer['invaddr1'], $customer['invpostcode'], $customer['invcity'], $customer['invcountry'], $customer['invtelno'], $customer['invfaxno']), ENT_QUOTES) );
$objWorksheet->getCell('A7')->setValue( html_entity_decode($customer['companyname_en'], ENT_QUOTES) );
$objWorksheet->getCell('A8')->setValue( html_entity_decode(buildAddressFormat($customer['deladdr'], $customer['delpostcode'], $customer['delcity'], $customer['delcountry'], $customer['deltelno'], $customer['delfaxno']), ENT_QUOTES) );

$objWorksheet->getCell('N2')->setValue( date_format(date_create($row['launch_date']), 'l, F d, Y') );
$objWorksheet->getCell('N3')->setValue( $row['ivc_nbr'] );
$objWorksheet->getCell('N6')->setValue( resolveCode_master_type_code('TRANSPORTER', $customer['favtransporteur']) );
$objWorksheet->getCell('N7')->setValue( $row['tracking_nbr'] );

/*
Chinese labels
A10 Photo
B10 Product Number
C10 Product Type
D10 Qty
E10 Material
I10 Stones
M10 Total Weight
O10 Unit Price
P10 Amount USD

E11 Type
G11 Weight
H11 Type
J11 Qty
K11 Weight
*/
$objWorksheet->getCell("A10")->setValue("图片");
$objWorksheet->getCell("B10")->setValue("款号");
$objWorksheet->getCell("C10")->setValue("产品");
$objWorksheet->getCell("D10")->setValue("数量");
$objWorksheet->getCell("E10")->setValue("物料");
$objWorksheet->getCell("G10")->setValue("石头");
$objWorksheet->getCell("M10")->setValue("出货毛重\n(克)");
$objWorksheet->getCell("O10")->setValue("单价\n(人民币)");
$objWorksheet->getCell("P10")->setValue("价格\n(人民币)");

$objWorksheet->getCell("E11")->setValue("成色");
$objWorksheet->getCell("G11")->setValue("净金重\n(克)");
$objWorksheet->getCell("I11")->setValue("类别, 颜色");
$objWorksheet->getCell("J11")->setValue("粒数");
$objWorksheet->getCell("K11")->setValue("重量\n(克拉)");


$excel_row=12;

$sql = "SELECT
    tmp_ord.main_refid AS refid,
    dbo.ord_main.order_nbr,
    dbo.ord_main.custorder_ref,
    dbo.ord_main.launch_date,
    dbo.ord_main.del_date,
    dbo.ord_main.confirm_date,
    dbo.ord_main.remark,
    dbo.ord_main.ttl_qty,
    dbo.master_customer.custname_en,
    dbo.master_customer.custname_sc,
    SUM (tmp_ord.qty_ready) AS qty_ready,
    SUM (tmp_ord.ttlcost) AS ttlcost,
    SUM (tmp_ord.ttlfactcost) AS ttlfactcost,
    SUM (tmp_ord.ttlprice) AS ttlprice
FROM
    (
        (
            SELECT
                dbo.ord_dtl.main_refid,
                dbo.ord_txpako.txqty AS qty_ready,
                dbo.ord_txpako.unitcost AS ttlcost,
                dbo.ord_txpako.unitfactcost AS ttlfactcost,
                (
                    dbo.ord_txpako.unitprice * isnull(dbo.ord_txpako.discount, 1)
                ) AS ttlprice
            FROM
                dbo.ord_txpako
            INNER JOIN dbo.ord_dtl ON dbo.ord_txpako.orddtl_refid = dbo.ord_dtl.refid
            INNER JOIN dbo.ord_main ON dbo.ord_dtl.main_refid = dbo.ord_main.refid
            WHERE
                dbo.ord_txpako.deleted=0 AND
                ( dbo.ord_txpako.ivccus_refid = :ivccus_refid_1 $sql_clause )
        )
        UNION ALL
            (
                SELECT
                    dbo.ord_dtl_extra.main_refid,
                    dbo.ord_dtl_extra.qty AS qty_ready,
                    0 AS ttlcost,
                    0 AS ttlfactcost,
                    (
                        dbo.ord_dtl_extra.unitprice * dbo.ord_dtl_extra.qty * dbo.ord_dtl_extra.discount
                    ) AS ttlprice
                FROM
                    dbo.ord_dtl_extra
                INNER JOIN dbo.ord_main ON dbo.ord_dtl_extra.main_refid = dbo.ord_main.refid
                WHERE
                    dbo.ord_dtl_extra.ivccus_refid = :ivccus_refid_2
                $sql_clause_extra
            )
        UNION ALL
            (
                SELECT
                    dbo.ord_dtl_rm.main_refid,
                    '0' AS qty_ready,
                    '0' AS ttlcost,
                    '0' AS ttlfactcost,
                    '0' AS ttlprice
                FROM
                    dbo.ord_txpako_rm
                INNER JOIN dbo.ord_dtl_rm ON dbo.ord_txpako_rm.orddtlrm_refid = dbo.ord_dtl_rm.refid
                INNER JOIN dbo.ord_main ON dbo.ord_dtl_rm.main_refid = dbo.ord_main.refid
                WHERE
                    dbo.ord_txpako_rm.ivccus_refid = :ivccus_refid_3
                $sql_clause_rm
            )
    ) AS tmp_ord
INNER JOIN dbo.ord_main ON tmp_ord.main_refid = dbo.ord_main.refid
INNER JOIN dbo.master_customer ON dbo.ord_main.custcode = dbo.master_customer.custcode
GROUP BY
    tmp_ord.main_refid,
    dbo.ord_main.order_nbr,
    dbo.ord_main.custorder_ref,
    dbo.ord_main.launch_date,
    dbo.ord_main.del_date,
    dbo.ord_main.confirm_date,
    dbo.ord_main.remark,
    dbo.ord_main.ttl_qty,
    dbo.master_customer.custname_en,
    dbo.master_customer.custname_sc
ORDER BY dbo.ord_main.order_nbr ASC    ";
$sth = $dbh->prepare($sql);

$sql_param[':ivccus_refid_1'] = $refid;
$sql_param[':ivccus_refid_2'] = $refid;
$sql_param[':ivccus_refid_3'] = $refid;

$q = $sth->execute( $sql_param  );
//echo $sth->getSQL( $sql_param ) . HTML_EOL;
pdo_showerror($sth, $q);

$ttl_var = array();
while($order = $sth->fetch()){

    $rowheight=$default_rowh;
    $mergecell=false;

    $objWorksheet->insertNewRowBefore($excel_row+1, 1);
    $styleArray = array(
        'borders' => array(
            'allborders' => array(
                'color' => array('argb' => 'FFFFFFFF'),
            ),
            'horizontal' => array(
                'color' => array('argb' => '00000000'),
            ),
        ),
    );
    $objWorksheet->getStyle("A$excel_row:O$excel_row")->applyFromArray($styleArray);
    $objWorksheet->getStyle("A$excel_row:O$excel_row")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

    $objWorksheet->mergeCells("A$excel_row:B$excel_row");
    $objWorksheet->mergeCells("C$excel_row:H$excel_row");
    $objWorksheet->mergeCells("I$excel_row:L$excel_row");
    $objWorksheet->mergeCells("M$excel_row:P$excel_row");
    $objWorksheet->setCellValueByColumnAndRow(0, $excel_row, "訂單號碼: ".$order['order_nbr'] );
    $objWorksheet->setCellValueByColumnAndRow(2, $excel_row, "客戶備註: ".html_entity_decode($order['custorder_ref'], ENT_QUOTES) );

    if($customer['ivc_showgoldprice']){
        $objWorksheet->setCellValueByColumnAndRow(8, $excel_row, "黄价: ".numf(getMatPrice('gold', $order['launch_date'])) );
    }

    $objWorksheet->setCellValueByColumnAndRow(12, $excel_row, "订单剩余:".ord_ivccus_getOrdRemainQTY($order['refid'], $row['refid']) );
    $excel_row++;

    //items:
    $sql2 = "SELECT DISTINCT
dbo.ord_txpako.refid,
dbo.ord_txpako.serialno,
dbo.ord_dtl.itemno,
dbo.ord_dtl.refid AS orddtl_refid,
dbo.ord_dtl.product_refid,
dbo.inv_product.producttype,
dbo.inv_product.refid AS product_refid,
dbo.ord_main.order_nbr,
dbo.ord_main.launch_date,
dbo.inv_product.name_en,
dbo.inv_product.name_sc,
dbo.ord_dtl.[size],
dbo.ord_txpako.product_wt,
dbo.ord_txpako.unitcost,
dbo.ord_txpako.unitfactcost,
dbo.ord_txpako.unitprice * isnull(dbo.ord_txpako.discount,1) as unitprice,
dbo.ord_txpako.metal_wt,
dbo.ord_txpako.dia_wt,
dbo.ord_txpako.stone_wt,
dbo.ord_dtl.qty
FROM
dbo.ord_main
INNER JOIN dbo.ord_dtl ON dbo.ord_dtl.main_refid = dbo.ord_main.refid
INNER JOIN dbo.ord_txmain ON dbo.ord_txmain.orddtl_refid = dbo.ord_dtl.refid
INNER JOIN dbo.ord_txpako ON dbo.ord_txpako.txmain_refid = dbo.ord_txmain.refid
INNER JOIN dbo.inv_product ON dbo.ord_dtl.product_refid = dbo.inv_product.refid
WHERE
(dbo.ord_txpako.ivccus_refid = :ivccus_refid) AND
dbo.ord_main.refid = :refid
order by dbo.ord_dtl.itemno"; //order by dbo.ord_txpako.refid 20240305
    $sth2 = $dbh->prepare($sql2);
    $q = $sth2->execute( array(':refid' => $order['refid'], ':ivccus_refid' => $refid) );
    //echo $sth2->getSQL( array(':refid' => $order['refid'], ':ivccus_refid' => $refid) ); exit;

    while( $row2 = $sth2->fetch() ) {

        $cost = 0;
        $productrow = $excel_row;
        ivc_summary($ttl_var, $row2);

        $objWorksheet->insertNewRowBefore($excel_row+1, 1);
        if($row2['qty'] && $row2['qty']<1){
            $row2['unitprice'] = $row2['unitprice'] / $row2['qty']; //show unitprice of production unit
        }else{
            $row2['qty'] = 1;
        }

        //$objWorksheet->setCellValueByColumnAndRow(0, $productrow, 'PHOTO' );
        $objWorksheet->setCellValueByColumnAndRow(1, $productrow, $row2['itemno'] );
        $objWorksheet->setCellValueByColumnAndRow(2, $productrow, resolveCode_master_type_code('PRODTYPE', $row2['producttype'], false, RPT_LANG).
                                                                    (empty($row2['size'])?'':PHP_EOL.'大小: '.$row2['size']) );
        $objWorksheet->setCellValueByColumnAndRow(3, $productrow, $row2['qty'] );

        //$allMetal = getPAKOMetal($row2['refid']);
        $allMetal = getAllMetal($row2['orddtl_refid']);
        $allMetall = getAllMetallAL($row2['orddtl_refid']);
        $allStone = getAllStone($row2['orddtl_refid']);
        //vdump($allStone);//, $allMetal);
        //reset array pointer for each() function
        $req_row = max( count($allMetal), count($allMetall), count($allStone) );
        $addition_row = $req_row-1;
        if($addition_row<0){
            $addition_row=0;
        }
        $resized_row=false;
        if($addition_row < 3){
            $resized_row=true;
            $rowheight = (3-$addition_row)*$default_rowh;
            $objWorksheet->getRowDimension($productrow)->setRowHeight( $rowheight );
        }

        if(is_array($allMetal)){
            reset($allMetal);
        }
        
        if(is_array($allMetall)){
            reset($allMetall);
        }

        //vdump($allMetal);
        if(is_array($allStone)){
            reset($allStone);
        }
        //vdump($r);
    //    vdump($dtl['itemno'],//$dtl['refid'], $allMetal, $allStone, $allLabor);exit;

        //vdump($addition_row);

        for($i=0; $i<=$addition_row; $i++){
            //vdump( $r['key'], $r['value'] );

            if(is_array($allMetal)){
                $r = each($allMetal);
                //vdump($r['value']); exit;
                if( !empty($r) ){
                    //$mat = getmatdata($r['value']);
                    $itemname = resolveItemno($r['value']['itemno'], RPT_LANG);
                    if($i==0 && pak_itemFinishingdesc($row2['orddtl_refid'])!=false){
                        $pak_itemFinishingdesc = pak_itemFinishingdesc($row2['orddtl_refid'], RPT_LANG);
                        $itemname .= "\n($pak_itemFinishingdesc)";
                        if($resized_row===false && !empty($pak_itemFinishingdesc) ){
                            $exrow = (int)strlen($itemname)/15;
                            $objWorksheet->getRowDimension($productrow)->setRowHeight( $default_rowh+($exrow*$rowheight) );
                        }

                    }
                    //vdump( $r['value']['type'], numf($r['value']['weight'],3), $itemname);

                    $objWorksheet->setCellValueByColumnAndRow(4, $excel_row, $itemname );
                    //$objWorksheet->setCellValueByColumnAndRow(5, $excel_row, numf($r['value']['weight'],3) );

                    //temp overwrite main metal weight
                    if($i==0 ){
                        $objWorksheet->setCellValueByColumnAndRow(5, $excel_row, numf(getPAKOMetalWt($row2['refid']),3) );
                    }
                    elseif($f==0) {
                        $objWorksheet->setCellValueByColumnAndRow(6, $excel_row, numf($tx['metal_wt'],3 ) );
                    }

                    else{
                        $objWorksheet->setCellValueByColumnAndRow(5, $excel_row, numf($tx['metal_wt'],3) );
                    }
                    /*if($i==0){
                    $objWorksheet->setCellValueByColumnAndRow(6, $excel_row, $r['value']['weight'],3 );
                    }*/
                        $objWorksheet->setCellValueByColumnAndRow(7, $excel_row, $r['value']['uom_wt'] );
                }
                
                if(is_array($allMetall)){
                    $r = each($allMetall);
                    //vdump($r['value']); exit;
                    if( !empty($r) ){
                        //$mat = getmatdata($r['value']);
                        $itemname = resolveItemno($r['value']['itemno'], 'en');
                        if($f==0 && pak_itemFinishingdesc($row2['orddtl_refid'])!=false){
                            $pak_itemFinishingdesc = pak_itemFinishingdesc($row2['orddtl_refid'], RPT_LANG);
                            $itemname .= "\n($pak_itemFinishingdesc)";
                            if($resized_row===false && !empty($pak_itemFinishingdesc) ){
                                $exrow = (int)strlen($itemname)/15;
                                $objWorksheet->getRowDimension($productrow)->setRowHeight( $default_rowh+($exrow*$rowheight) );
                            }
    
                        }
                        //vdump( $r['value']['type'], numf($r['value']['weight'],3), $itemname);
                        
                        $objWorksheet->setCellValueByColumnAndRow(4, $excel_row, $itemname );
                        if($f==0){
                            $objWorksheet->setCellValueByColumnAndRow(6, $excel_row, numf(getMainMat($row2['refid']),3) );
                                if($f==0){
                                    $objWorksheet->setCellValueByColumnAndRow(6,$excel_row, numf($r['value']['weight'],3 ) ); 
                                }
                            elseif ($i==0) {
                                $objWorksheet->setCellValueByColumnAndRow(5, $excel_row, numf($tx['metal_wt'],3) );
                            }   
                        }
                            
                        //$objWorksheet->setCellValueByColumnAndRow(6,$excel_row, numf($r['value']['weight'],3 ) );
                        
                        $objWorksheet->setCellValueByColumnAndRow(7, $excel_row, $r['value']['uom_wt'] );
                    }
                }
            }

            if(is_array($allStone)){
                $r = each($allStone);
                if( !empty($r) ){
                    //vdump($r);
                    //$stone = getmatdata($r['value']);
                    $objWorksheet->setCellValueByColumnAndRow(8, $excel_row, $r['value']['itemdesc'] );
                    $objWorksheet->setCellValueByColumnAndRow(9, $excel_row, numf($r['value']['qty']) );
                    $objWorksheet->setCellValueByColumnAndRow(10, $excel_row, numf($r['value']['actual_qty'],3 ) );
                    $objWorksheet->setCellValueByColumnAndRow(11, $excel_row, $r['value']['uom_wt'] );
                    }
            }
            if($i>0){
                $objWorksheet->insertNewRowBefore($excel_row+1, 1);
                //$objWorksheet->setCellValueByColumnAndRow(15, $excel_row, 'newline' );
            }
            //echo "NewLine".HTML_EOL;
            $objWorksheet->getRowDimension($excel_row)->setRowHeight(-1);
            $excel_row++;
        }

        $row2['unitprice'] = discountPrice($row2['unitprice'], $header['markup']);

        $objWorksheet->setCellValueByColumnAndRow(12, $productrow, numf($row2['product_wt'], 3) );
        $objWorksheet->setCellValueByColumnAndRow(13, $productrow, 'gr' );
        $objWorksheet->setCellValueByColumnAndRow(14, $productrow, numf($row2['unitprice']) );
        $objWorksheet->setCellValueByColumnAndRow(15, $productrow, numf($row2['unitprice']*$row2['qty'], 2) );

        $endrow = ($excel_row)-1;
        //vdump($productrow, $endrow);
        if($productrow<$endrow){
            $mergecell = true;
            //$rowheight = ($endrow-$productrow+1) * $default_rowh;
            //vdump($endrow-$productrow,$default_rowh,$rowheight);

            $objWorksheet->mergeCells("A$productrow:A$endrow");
            $objWorksheet->mergeCells("B$productrow:B$endrow");
            $objWorksheet->mergeCells("C$productrow:C$endrow");
            $objWorksheet->mergeCells("D$productrow:D$endrow");
            if(sizeof($allMetal)<=1){
                $objWorksheet->mergeCells("E$productrow:E$endrow");
                $objWorksheet->mergeCells("F$productrow:F$endrow");
                //$objWorksheet->mergeCells("G$productrow:G$endrow");
                $objWorksheet->mergeCells("H$productrow:H$endrow");
            }
            if(sizeof($allMetall)<=1){
                //$objWorksheet->mergeCells("E$productrow:E$endrow");
                $objWorksheet->mergeCells("G$productrow:G$endrow");
               //$objWorksheet->mergeCells("H$productrow:H$endrow");
            }
            if(sizeof($allStone)<=1){
            //$stoneRow = sizeof($allStone);
            //if($stoneRow<$req_row){
                $objWorksheet->mergeCells("I$productrow:I$endrow");
                $objWorksheet->mergeCells("J$productrow:J$endrow");
                $objWorksheet->mergeCells("K$productrow:K$endrow");
                $objWorksheet->mergeCells("L$productrow:L$endrow");
            }

            $objWorksheet->mergeCells("M$productrow:M$endrow");
            $objWorksheet->mergeCells("N$productrow:N$endrow");
            $objWorksheet->mergeCells("O$productrow:O$endrow");
            $objWorksheet->mergeCells("P$productrow:P$endrow");
        }

        //$n = $objWorksheet->getRowDimension($productrow)->getRowHeight();
        //$objWorksheet->setCellValueByColumnAndRow(0, $productrow, $rowheight );

        //echo "IMG----";
        // Create new picture object

 //20130419 tmp disable
        $imgpath=getProductPath($row2['product_refid'], true);
        if($imgpath && file_exists($imgpath)
        //&& is_readable($imgpath)
        ){
            $objDrawing = new PHPExcel_Worksheet_Drawing();
            $objDrawing->setName($dtl['itemno']);
            $objDrawing->setDescription($dtl['itemno']);
            $objDrawing->setPath($imgpath);
            $objDrawing->setHeight(40);//$rowheight-4);
            //echo getProductPath($row2['product_refid'], true); exit;
            // Insert picture
            $objDrawing->setCoordinates('A'.$productrow);
            $objDrawing->setOffsetX(5);
            $objDrawing->setOffsetY(3);
            $objDrawing->setWorksheet($objWorksheet);
            //echo "EOL IMG----";
        }


    }

        //raw material item
    $sql2 = "SELECT
            dbo.ord_dtl_rm.itemno,
            dbo.ord_dtl_rm.itemnoid,
            dbo.ord_dtl_rm.bomcategory,
            dbo.ord_txpako_rm.*
            FROM
            dbo.ord_txpako_rm
            INNER JOIN dbo.ord_dtl_rm ON dbo.ord_txpako_rm.orddtlrm_refid = dbo.ord_dtl_rm.refid
            INNER JOIN dbo.ord_main ON dbo.ord_dtl_rm.main_refid = dbo.ord_main.refid
            WHERE
            (dbo.ord_txpako_rm.ivccus_refid = :ivccus_refid $sql_clause_rm) AND
            main_refid = :refid
            ORDER BY
            dbo.ord_txpako_rm.refid ASC";
    $sth2 = $dbh->prepare($sql2);
    $q = $sth2->execute( array(':refid' => $order['refid'], ':ivccus_refid' => $refid) );
    //echo $sth2->getSQL( array(':refid' => $order['refid'], ':ivccus_refid' => $refid) );
    while( $row2 = $sth2->fetch() ) {
        $table = inv_getBomcategyTablename($row2['bomcategory']);
        $item = getDB($table, $row2['itemnoid']);
        $objWorksheet->insertNewRowBefore($excel_row+1, 1);

        $row2['discountprice'] = discountPrice($row2['unitprice'], $row2['discount']);
        $row2['discountprice'] = discountPrice($row2['discountprice'], $header['markup']);
        $objWorksheet->setCellValueByColumnAndRow(1, $excel_row, $item['itemno'] );
        $objWorksheet->setCellValueByColumnAndRow(2, $excel_row, resolveCode_master_type_code("BOMCATEGY", $row2['bomcategory'], false, RPT_LANG) );
        $objWorksheet->setCellValueByColumnAndRow(3, $excel_row, numf($row2['txqty'] ,3) );
        $objWorksheet->setCellValueByColumnAndRow(4, $excel_row, $item['name_'.RPT_LANG] );
        $objWorksheet->setCellValueByColumnAndRow(12, $excel_row, numf($row2['rm_wt'], 3) );
        $objWorksheet->setCellValueByColumnAndRow(13, $excel_row, $row2['uom_wt'] );

        $objWorksheet->setCellValueByColumnAndRow(14, $excel_row, numf($row2['discountprice'], 2) );
        $objWorksheet->setCellValueByColumnAndRow(15, $excel_row, numf($row2['discountprice']*$row2['qtyofprice'], 2) );
        $objWorksheet->mergeCells("E$excel_row:K$excel_row");
        $excel_row++;
        //vdump($row2);
    }

    //free text extra item
    $sql2 = "SELECT
            dbo.ord_dtl_extra.*
            FROM
            dbo.ord_dtl_extra
            INNER JOIN dbo.ord_main ON dbo.ord_dtl_extra.main_refid = dbo.ord_main.refid
            WHERE
            (dbo.ord_dtl_extra.ivccus_refid = :ivccus_refid ) AND
            main_refid = :refid
            ORDER BY
            dbo.ord_dtl_extra.refid ASC";
    $sth2 = $dbh->prepare($sql2);
    $q = $sth2->execute( array(':refid' => $order['refid'], ':ivccus_refid' => $refid) );
    //echo $sth2->getSQL( array(':refid' => $order['refid'], ':ivccus_refid' => $refid) );
    while( $row2 = $sth2->fetch() ) {

        $objWorksheet->insertNewRowBefore($excel_row+1, 1);

        $row2['discountprice'] = discountPrice($row2['unitprice'], $row2['discount']);
        $row2['discountprice'] = discountPrice($row2['discountprice'], $header['markup']);
        $objWorksheet->setCellValueByColumnAndRow(1, $excel_row, $row2['desc'] );
        $objWorksheet->setCellValueByColumnAndRow(3, $excel_row, numf($row2['qty'] ,3) );
        $objWorksheet->setCellValueByColumnAndRow(3, $excel_row, numf($row2['qty'] ,3) );
        $objWorksheet->setCellValueByColumnAndRow(14, $excel_row, numf($row2['discountprice'], 2) );
        $objWorksheet->setCellValueByColumnAndRow(15, $excel_row, numf($row2['discountprice']*$row2['qty'], 2) );
        $excel_row++;
        //vdump($row2);
    }
    //vdump($order); exit;
}




//warp cells
$objWorksheet->getStyle('E13:E'.$excel_row)->getAlignment()->setWrapText(true);

//hide empty rows
$objWorksheet->getRowDimension($excel_row)->setVisible(false);
$objWorksheet->getRowDimension($excel_row+1)->setVisible(false);


//exit;
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Invoice_'.$header['ivc_nbr'].'.xls"');
header('Cache-Control: max-age=0');

$objPHPexcel->setActiveSheetIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel5');
$objWriter->save('php://output');

//$objWriter = new PHPExcel_Writer_PDF($objPHPexcel);
//$objWriter->save("test.pdf");

flush();
?>