webadmin.php

/var/www/enzatesting.onesolution.hk/EURO FORBIDDEN TO TOUCH/xls_ord_dtlEUR.php

<?php
$formid = "Order";
require_once "inc/configure.php";
require_once "inc/phpexcel_1.7.7/PHPExcel.php";    

if(havePermission("DCr")==false
|| havePermission("DPr")==false){

    myerror("Invalid Permission");    
}

function getAllMetal($orddtl_refid){
    global $dbh;
    $sql = "SELECT
            *
            FROM
            dbo.ord_item_bom
            WHERE
            dbo.ord_item_bom.orddtl_refid = :orddtl_refid AND
            (dbo.ord_item_bom.bomcategy = 'material' OR
            dbo.ord_item_bom.bomcategy = 'accessory')";        
    $sth = $dbh->prepare($sql);
    $sth->execute( array(':orddtl_refid'=>$orddtl_refid)  );
    //echo $sth->getSQL( array(':main_refid'=>$header['refid'])  );
    $allBOM = $sth->fetchAll(PDO::FETCH_ASSOC);
    return $allBOM;    
}

function getAllStone($orddtl_refid){
    global $dbh;
    $sql = "SELECT
            dbo.ord_item_bom.*
            FROM
            dbo.ord_item_bom            
            WHERE
            dbo.ord_item_bom.orddtl_refid = :orddtl_refid AND
            (dbo.ord_item_bom.bomcategy = 'stone' OR
            dbo.ord_item_bom.bomcategy = 'diamond')";        
    $sth = $dbh->prepare($sql);
    $sth->execute( array(':orddtl_refid'=>$orddtl_refid)  );
    //echo $sth->getSQL( array(':orddtl_refid'=>$orddtl_refid)  ).HTML_EOL;
    while( $aBOM = $sth->fetch(PDO::FETCH_ASSOC) ){
        //vdump($aBOM);    
        $sql = "SELECT
                sum(dbo.ord_subroute.unit_price * dbo.ord_subroute.opr_svc ) as cost,
                sum(dbo.ord_subroute.opr_svc) as opr_svc                
                FROM
                dbo.ord_subroute
                INNER JOIN dbo.ord_route ON dbo.ord_subroute.ordroute_refid = dbo.ord_route.refid
                WHERE
                dbo.ord_subroute.orddtl_refid = :orddtl_refid AND
                dbo.ord_route.opr_code = 'SETO' AND
                dbo.ord_subroute.itemno = :itemno"; // AND dbo.ord_subroute.opr_svc = :qty        
        $sth2 = $dbh->prepare($sql);
        $sth2->execute( array(':orddtl_refid'=>$orddtl_refid, ':itemno'=>$aBOM['itemno']));//, ':qty'=>$aBOM['qty'])  );
        //echo $sth2->getSQL( array(':orddtl_refid'=>$orddtl_refid, ':itemno'=>$aBOM['itemno'], ':qty'=>$aBOM['qty'])  ).HTML_EOL;
        if($row = $sth2->fetch(PDO::FETCH_ASSOC)){
            //append setting info from subroute    
            $aBOM['itemname'] = $itemname['name_en'];
            $aBOM['opr_svc'] = $row['opr_svc'];
            $aBOM['seto_unitcost'] = $row['cost'];
            
            //vdump($aBOM);
        }
        $allBOM[] = $aBOM;
        
        
    }
    return $allBOM;    
}

function getAllLabor($orddtl_refid){
    global $dbh;
    //Casting------------------------------------------------------------
    $sql = "SELECT
            sum(opr_svc*unit_price) as ttl_opr_svc
            FROM
            dbo.ord_subroute
            WHERE
            dbo.ord_subroute.orddtl_refid = :orddtl_refid AND
            dbo.ord_subroute.opr_code = 'MELT'";        
    $sth = $dbh->prepare($sql);
    $sth->execute( array(':orddtl_refid'=>$orddtl_refid)  );
    //echo $sth->getSQL( array(':main_refid'=>$header['refid'])  );
    $allLabor['Casting'] = $sth->fetch(PDO::FETCH_ASSOC);
    
    //Filling------------------------------------------------------------
    $sql = "SELECT
            sum(opr_svc*unit_price) as ttl_opr_svc
            FROM
            dbo.ord_route
            WHERE
            dbo.ord_route.orddtl_refid = :orddtl_refid AND
            dbo.ord_route.opr_code = 'FILO'";        
    $sth = $dbh->prepare($sql);
    $sth->execute( array(':orddtl_refid'=>$orddtl_refid)  );
    //echo $sth->getSQL( array(':main_refid'=>$header['refid'])  );
    $allLabor['Filling'] = $sth->fetch(PDO::FETCH_ASSOC);

    //Polishing------------------------------------------------------------
    $sql = "SELECT
            sum(opr_svc*unit_price) as ttl_opr_svc
            FROM
            dbo.ord_route
            WHERE
            dbo.ord_route.orddtl_refid = :orddtl_refid AND
            dbo.ord_route.opr_code = 'POLO'";        
    $sth = $dbh->prepare($sql);
    $sth->execute( array(':orddtl_refid'=>$orddtl_refid)  );
    //echo $sth->getSQL( array(':main_refid'=>$header['refid'])  );
    $allLabor['Polishing'] = $sth->fetch(PDO::FETCH_ASSOC);
        
    //Engraving------------------------------------------------------------
    $sql = "SELECT
            sum(opr_svc*unit_price) as ttl_opr_svc
            FROM
            dbo.ord_subroute
            WHERE
            dbo.ord_subroute.orddtl_refid = :orddtl_refid AND
            dbo.ord_subroute.opr_code = 'POLE'";
    $sth = $dbh->prepare($sql);
    $sth->execute( array(':orddtl_refid'=>$orddtl_refid)  );
    //echo $sth->getSQL( array(':main_refid'=>$header['refid'])  );
    $POL1 = $sth->fetch(PDO::FETCH_ASSOC);
                    
    $sql = "SELECT
            sum(opr_svc*unit_price) as ttl_opr_svc
            FROM
            dbo.ord_route
            WHERE
            dbo.ord_route.orddtl_refid = :orddtl_refid AND
            dbo.ord_route.opr_code = 'EGRA'";        
    $sth = $dbh->prepare($sql);
    $sth->execute( array(':orddtl_refid'=>$orddtl_refid)  );
    //echo $sth->getSQL( array(':main_refid'=>$header['refid'])  );
    $POL2 = $sth->fetch(PDO::FETCH_ASSOC);
    $allLabor['Engraving']['ttl_opr_svc'] = $POL1['ttl_opr_svc'] + $POL2['ttl_opr_svc'];

    //Rhodium------------------------------------------------------------
    $sql = "SELECT
            sum(opr_svc*unit_price) as ttl_opr_svc
            FROM
            dbo.ord_subroute
            WHERE
            dbo.ord_subroute.orddtl_refid = :orddtl_refid AND
            dbo.ord_subroute.opr_code = 'POLR'";
    $sth = $dbh->prepare($sql);        
    $sth->execute( array(':orddtl_refid'=>$orddtl_refid)  );
    //echo $sth->getSQL( array(':orddtl_refid'=>$orddtl_refid)  );
    $RHO1 = $sth->fetch(PDO::FETCH_ASSOC);                
                
    $sql = "SELECT
            sum(opr_svc*unit_price) as ttl_opr_svc
            FROM
            dbo.ord_route
            WHERE
            dbo.ord_route.orddtl_refid = :orddtl_refid AND
            dbo.ord_route.opr_code = 'RHOO'";        
    $sth = $dbh->prepare($sql);
    $sth->execute( array(':orddtl_refid'=>$orddtl_refid)  );
    //echo $sth->getSQL( array(':main_refid'=>$header['refid'])  );
    $RHO2 = $sth->fetch(PDO::FETCH_ASSOC);
    $allLabor['Rhodium']['ttl_opr_svc'] = $RHO1['ttl_opr_svc'] + $RHO2['ttl_opr_svc'];    
        
    //Enamel------------------------------------------------------------
    $sql = "SELECT
            sum(opr_svc*unit_price) as ttl_opr_svc
            FROM
            dbo.ord_route
            WHERE
            dbo.ord_route.orddtl_refid = :orddtl_refid AND
            dbo.ord_route.opr_code = 'ENAL'";        
    $sth = $dbh->prepare($sql);
    $sth->execute( array(':orddtl_refid'=>$orddtl_refid)  );
    //echo $sth->getSQL( array(':main_refid'=>$header['refid'])  );
    $allLabor['Enamel'] = $sth->fetch(PDO::FETCH_ASSOC);
    
    foreach($allLabor as $op=>$cost){
        //vdump($cost['ttl_opr_svc']);
        if($cost['ttl_opr_svc']<=0){
            //echo "unset $op";
            unset($allLabor[$op]);    
        }
    }
    
    return $allLabor;    
}

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;
}

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

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

if($refid){
    $sql = "SELECT
            dbo.ord_main.*,
            dbo.master_customer.custname_en
            
            FROM
            dbo.ord_main
            INNER JOIN dbo.master_customer ON dbo.ord_main.custcode = dbo.master_customer.custcode
            WHERE
            dbo.ord_main.refid = :refid";
    $sth = $dbh->prepare($sql);
    $sth->execute(  array(':refid' => $refid) );    
} else {
    myerror("Invalid Request");
}
$header = $sth->fetch();
//vdump($header); exit;

@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/ord_dtl_cost.xls');    

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

/*
date: A1
ordernumber: A2
customer name: A3
customer ref: A4
gold price per ounce: A6
*/


$objWorksheet->getCell('A1')->setValue('Date: '.date("Y-m-d"));
$objWorksheet->getCell('A2')->setValue('Order Number: '.$header['order_nbr']);
$objWorksheet->getCell('A3')->setValue('Customer Name: '.$header['custname_en']);
$objWorksheet->getCell('A4')->setValue('Customer Reference: '.$header['custorder_ref']);
//$objWorksheet->getCell('A6')->setValue('Gold Price per Ounce: $'.numf(getMatPrice('gold', $header['createdate'])).' ('.substr(datef($header['createdate']),0,10).')');
$objWorksheet->getCell('A6')->setValue('Gold Price per Ounce: $'.numf(getMatPrice('gold')).' ('.substr(date("Y-m-d"),0,10).')');

$excel_row=10;

//products:
$sql = "SELECT
dbo.ord_dtl.*,
dbo.inv_product.itemname,
dbo.inv_product.name_en,
dbo.inv_product.name_sc,
dbo.inv_product.weight
FROM
dbo.ord_dtl
INNER JOIN dbo.inv_product ON dbo.ord_dtl.product_refid = dbo.inv_product.refid
WHERE
dbo.ord_dtl.main_refid = :main_refid
ORDER BY
dbo.ord_dtl.refid ASC
";        

$sth = $dbh->prepare($sql);
$sth->execute( array(':main_refid'=>$header['refid'])  );
//echo $sth->getSQL( array(':main_refid'=>$header['refid'])  );
while($dtl = $sth->fetch() ){
    $discountPrice = discountPrice($dtl['unitprice'], $header['discount']);
    $usdPrice = USD_CNY::getUSDAmount($dtl['uom_cy'], $discountPrice);
    $cnyPrice = USD_CNY::getCNYAmount($dtl['uom_cy'], $discountPrice);
    $usdPrice = USD_EUR::getUSDAmount($dtl['uom_cy'], $discountPrice);
    $eurPrice = USD_EUR::getEURAmount($dtl['uom_cy'], $discountPrice);
    $cost = 0;    
    $productrow = $excel_row;
    
    $objWorksheet->insertNewRowBefore($excel_row+1, 1);
    $objWorksheet->setCellValueByColumnAndRow(0, $productrow, $dtl['itemno'] );
    $objWorksheet->setCellValueByColumnAndRow(1, $productrow, $dtl['itemname'] );
    $objWorksheet->setCellValueByColumnAndRow(2, $productrow, $dtl['qty'] );
    $objWorksheet->setCellValueByColumnAndRow(3, $productrow, $dtl['size'] );

    $allMetal = getAllMetal($dtl['refid']);
    $allStone = getAllStone($dtl['refid']);    
    $allLabor = getAllLabor($dtl['refid']);
    //reset array pointer for each() function
    if(is_array($allMetal)){
        reset($allMetal);
    }
    if(is_array($allStone)){
        reset($allStone);
    }
    if(is_array($allLabor)){
        reset($allLabor);
    }
    //vdump($r);
//    vdump($dtl['itemno'],//$dtl['refid'], $allMetal, $allStone, $allLabor);exit;
    $addition_row = max( count($allMetal), count($allStone), count($allLabor) )-1;

    for($i=0; $i<=$addition_row; $i++){        
        //vdump( $r['key'], $r['value'] );
        if(is_array($allMetal)){
            $r = each($allMetal);
            if( !empty($r) ){
                $mat = getmatdata($r['value']);
                $itemname = resolveItemno($r['value']['itemno'], 'en');
                
                $objWorksheet->setCellValueByColumnAndRow(5, $excel_row, $itemname );
                $objWorksheet->setCellValueByColumnAndRow(6, $excel_row, numf($mat['weight'] * $dtl['qty']) );
                $objWorksheet->setCellValueByColumnAndRow(7, $excel_row, numf($mat['price'] * $dtl['qty']) );
                $cost += $mat['price'] * $dtl['qty'];
            }    
        }
            
        if(is_array($allStone)){    
            $r = each($allStone);
            if( !empty($r) ){
                //$stone = getmatdata($r['value']);
                $itemname = resolveItemno($r['value']['itemno'], 'en');
                            
                $objWorksheet->setCellValueByColumnAndRow(9, $excel_row, $itemname );
                $objWorksheet->setCellValueByColumnAndRow(10, $excel_row, numf($r['value']['qty'] * $dtl['qty']) );
                $objWorksheet->setCellValueByColumnAndRow(11, $excel_row, numf($r['value']['weight']* $r['value']['qty'] * $dtl['qty']) );
                $objWorksheet->setCellValueByColumnAndRow(12, $excel_row, numf( BOM_calculateItemCost($r['value']) * $dtl['qty']) );
                $objWorksheet->setCellValueByColumnAndRow(13, $excel_row, numf($r['value']['seto_unitcost'] * $dtl['qty']) );
                $cost += BOM_calculateItemCost($r['value']) * $dtl['qty'];
                $cost += $r['value']['seto_unitcost'] * $dtl['qty'];
            }
        }
        
        if(is_array($allLabor)){        
            $r = each($allLabor);
            if( $r['value']['ttl_opr_svc']>0 ){
                $objWorksheet->setCellValueByColumnAndRow(15, $excel_row, $r['key'] );
                $objWorksheet->setCellValueByColumnAndRow(16, $excel_row, numf($r['value']['ttl_opr_svc']*$dtl['qty']) );
                $cost += $r['value']['ttl_opr_svc']*$dtl['qty'];
            }        
        }
        $objWorksheet->insertNewRowBefore($excel_row+1, 1);
        $excel_row++;        
    }
    
    $objWorksheet->setCellValueByColumnAndRow(18, $productrow, numf($dtl['weight'] * $dtl['qty']) );
    $objWorksheet->setCellValueByColumnAndRow(19, $productrow, numf($cost) );
    $objWorksheet->setCellValueByColumnAndRow(20, $productrow, numf($usdPrice*$dtl['qty']) );
    $objWorksheet->setCellValueByColumnAndRow(21, $productrow, numf($cnyPrice*$dtl['qty']) );
    $objWorksheet->setCellValueByColumnAndRow(22, $productrow, numf($eurPrice*$dtl['qty']) );
    $objWorksheet->setCellValueByColumnAndRow(23, $productrow, numf(calcMargin($usdPrice*$dtl['qty'], $cost)) );
    
    $endrow = ($excel_row++)-1;    
    
    $objWorksheet->mergeCells("A$productrow:A$endrow");
    $objWorksheet->mergeCells("B$productrow:B$endrow");
    $objWorksheet->mergeCells("C$productrow:C$endrow");
    $objWorksheet->mergeCells("D$productrow:D$endrow");
    
    $objWorksheet->mergeCells("S$productrow:S$endrow");
    $objWorksheet->mergeCells("T$productrow:T$endrow");
    $objWorksheet->mergeCells("U$productrow:U$endrow");
    $objWorksheet->mergeCells("V$productrow:V$endrow");
    $objWorksheet->mergeCells("W$productrow:W$endrow");
    $objWorksheet->getRowDimension($excel_row-1)->setRowHeight(3);
    //$objWorksheet->mergeCells("B$$endrow:B$excel_row");
    //echo"---------------------------------------------";
}

$excel_row += 4;
//$objWorksheet->setCellValueByColumnAndRow(1, $excel_row, "THIS is my new item" );
//raw material =====================================================================


$sql = "SELECT 
*
FROM ord_dtl_rm
WHERE main_refid=:refid
ORDER BY refid ASC";        

$sth = $dbh->prepare($sql);
$sth->execute( array(':refid'=>$header['refid'])  );
//echo $sth->getSQL( array(':main_refid'=>$header['refid'])  );
while($dtl = $sth->fetch() ){
    $discountPrice = discountPrice($dtl['unitprice'], $header['discount']);
    $usdPrice = USD_CNY::getUSDAmount($dtl['uom_cy'], $discountPrice);
    $cnyPrice = USD_CNY::getCNYAmount($dtl['uom_cy'], $discountPrice);
    $table = inv_getBomcategyTablename($dtl['bomcategory']);
    $item = getDB($table, $dtl['itemnoid']);
        
    $objWorksheet->insertNewRowBefore($excel_row+1, 1);
    $objWorksheet->setCellValueByColumnAndRow(0, $excel_row, $item['itemno'] );
    $objWorksheet->setCellValueByColumnAndRow(2, $excel_row, $item['name_en'] );
    $objWorksheet->setCellValueByColumnAndRow(10, $excel_row, numf($dtl['qty'] ,3) );
    $objWorksheet->setCellValueByColumnAndRow(11, $excel_row, numf($dtl['wt'] ,3) );
    $objWorksheet->setCellValueByColumnAndRow(12, $excel_row, numf($dtl['unitcost']) );
    $objWorksheet->setCellValueByColumnAndRow(13, $excel_row, "/".$dtl['uom_price'] );
    
    $objWorksheet->setCellValueByColumnAndRow(15, $excel_row, numf($discountPrice) );
    $objWorksheet->setCellValueByColumnAndRow(16, $excel_row, "/".$dtl['uom_price'] );
    $objWorksheet->setCellValueByColumnAndRow(19, $excel_row, numf(calcPriceByUOM($dtl['qty'], $dtl['uom_qty'], $dtl['wt'], $dtl['uom_wt'], $dtl['unitcost'], $dtl['uom_price'])) );
    $objWorksheet->setCellValueByColumnAndRow(20, $excel_row, numf(calcPriceByUOM($dtl['qty'], $dtl['uom_qty'], $dtl['wt'], $dtl['uom_wt'], $usdPrice, $dtl['uom_price'])) );
    $objWorksheet->setCellValueByColumnAndRow(21, $excel_row, numf(calcPriceByUOM($dtl['qty'], $dtl['uom_qty'], $dtl['wt'], $dtl['uom_wt'], $cnyPrice, $dtl['uom_price'])) );
    $objWorksheet->setCellValueByColumnAndRow(22, $excel_row, numf(calcPriceByUOM($dtl['qty'], $dtl['uom_qty'], $dtl['wt'], $dtl['uom_wt'], $cnyPrice, $dtl['uom_price'])) );
    $objWorksheet->setCellValueByColumnAndRow(23, $excel_row, calcMargin($usdPrice, $dtl['unitcost']) );
    unset($dtl, $item);
    $excel_row++;
}

// free text extra item
    $sql = "SELECT 
*
FROM ord_dtl_extra
WHERE main_refid=:refid";        

$sth = $dbh->prepare($sql);
$sth->execute( array(':refid'=>$header['refid'])  );
//echo $sth->getSQL( array(':main_refid'=>$header['refid'])  );
while($dtl = $sth->fetch() ){
    $discountPrice = discountPrice($dtl['unitprice'], $header['discount']);
    $usdPrice = USD_CNY::getUSDAmount($dtl['uom_cy'], $discountPrice);
    $cnyPrice = USD_CNY::getCNYAmount($dtl['uom_cy'], $discountPrice);
    
    $objWorksheet->insertNewRowBefore($excel_row+1, 1);
    $objWorksheet->setCellValueByColumnAndRow(0, $excel_row, 'N/A' );
    $objWorksheet->setCellValueByColumnAndRow(2, $excel_row, $dtl['desc'] );
    $objWorksheet->setCellValueByColumnAndRow(10, $excel_row, numf($dtl['qty'] ,3) );
    $objWorksheet->setCellValueByColumnAndRow(11, $excel_row, numf($dtl['wt'] ,3) );
    $objWorksheet->setCellValueByColumnAndRow(12, $excel_row, numf($dtl['unitcost']) );
    $objWorksheet->setCellValueByColumnAndRow(13, $excel_row, "USD" );
    
    $objWorksheet->setCellValueByColumnAndRow(15, $excel_row, numf($discountPrice) );
    $objWorksheet->setCellValueByColumnAndRow(16, $excel_row, $dtl['uom_cy'] );
    $objWorksheet->setCellValueByColumnAndRow(19, $excel_row, numf($dtl['unitcost']*$dtl['qty']) );
    $objWorksheet->setCellValueByColumnAndRow(20, $excel_row, numf($usdPrice*$dtl['qty']) );
    $objWorksheet->setCellValueByColumnAndRow(21, $excel_row, numf($cnyPrice*$dtl['qty']) );
    $objWorksheet->setCellValueByColumnAndRow(22, $excel_row, numf($cnyPrice*$dtl['qty']) );
    $objWorksheet->setCellValueByColumnAndRow(23, $excel_row, numf(calcMargin($usdPrice, $dtl['unitcost'])) );
    unset($dtl);
    $excel_row++;
}

//hide/remove column22[V] for order currency is not CNY
if($header['uom_cy']!='CNY'){
    $objWorksheet->getColumnDimension('V')->setVisible(false);
}
if($header['uom_cy']!='EUR'){
    $objWorksheet->getColumnDimension('V')->setVisible(false);
}
//exit;

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Order_cost_'.$header['order_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();
?>