webadmin.php

/var/www/enzatesting.onesolution.hk/xls_ord_disd_report.php

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

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

    myerror("Invalid Permission");    
}


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--------------------------------------*/
$settings['incUOM'] = false;
$settings['groupDiamond'] = false;
$settings['lang'] = 'sc';
$settings['deliverydate'] = setDefaultReqVar("deliverydate", '');
$settings['custcode'] = setDefaultReqVar("customer", '');


if( !empty($_REQUEST['packed_time_start']) ){
    $sql_clause .= ' AND dbo.ord_txpako.packed_time >= :packed_time_start ';
    $sql_param[':packed_time_start'] = $_REQUEST['packed_time_start'];            
}
if( !empty($_REQUEST['packed_time_end']) ){
    $sql_clause .= ' AND dbo.ord_txpako.packed_time < :packed_time_end ';
    $sql_param[':packed_time_end'] = $_REQUEST['packed_time_end'];            
}
if(!empty($settings['deliverydate'])){
    $sql_clause .= 'AND dbo.ord_main.del_date = :deliverydate ';
    $sql_param[':deliverydate'] = $settings['deliverydate'];
}

if( !empty($_REQUEST['customer']) ){
    $sql_clause .= ' AND (dbo.master_customer.custcode = :custcode OR dbo.master_customer.custname_en LIKE :custname_en OR dbo.master_customer.custname_sc LIKE :custname_sc)';
    $sql_param[':custcode'] = $_REQUEST['customer'];            
    $sql_param[':custname_en'] = '%'.$_REQUEST['customer'].'%';            
    $sql_param[':custname_sc'] = '%'.$_REQUEST['customer'].'%';            
}


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

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

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


$objWorksheet->getCell('B1')->setValue("DISPATCH FORM  收发出货表");
$objWorksheet->getCell('S2')->setValue("Date 日期：".date("Y-m-d"));

// $objWorksheet->getCell('T2')->setValue(date("Y-m-d"));

$objWorksheet->getCell('B3')->setValue("Customer\n客户");
$objWorksheet->getCell('C3')->setValue("Job sheet no\n流程单号");
$objWorksheet->getCell('D3')->setValue("Casting Gold Price\n倒模金价");
$objWorksheet->getCell('E3')->setValue("Order NO\n单号");
$objWorksheet->getCell('F3')->setValue("ITEM REF\n款号");
$objWorksheet->getCell('G3')->setValue("QTY\n件数");
$objWorksheet->getCell('H3')->setValue("Gross wt (GR)\n出货毛重 (克)");
$objWorksheet->getCell('I3')->setValue("MATERIAL & ACCESSORY 物料与配件");
$objWorksheet->getCell('M3')->setValue("STONES 石头");

$objWorksheet->getCell('T3')->setValue("Unit Cost\n成本");
$objWorksheet->getCell('U3')->setValue("Factory Cost\n工厂成本");
$objWorksheet->getCell('V3')->setValue("Unit Price\n单价");

$objWorksheet->getCell('I4')->setValue("TYPE\n类别");
$objWorksheet->getCell('J4')->setValue("COLOR\n成色");
// $objWorksheet->getCell('K4')->setValue("Gross wt (GR)\n出货毛重 (克)");
$objWorksheet->getCell('L4')->setValue("WEIGHT NET (GR)\n净重 (克)");
$objWorksheet->getCell('M4')->setValue("TYPE STONE & COLOR\n石头类别&颜色");
$objWorksheet->getCell('N4')->setValue("CUT\n形状");
$objWorksheet->getCell('O4')->setValue("SIZE (MM)\n尺寸");
$objWorksheet->getCell('P4')->setValue("QTY\n粒数");
$objWorksheet->getCell('Q4')->setValue("WEIGHT (CT)\n重量 (克拉)");
$objWorksheet->getCell('R4')->setValue("Price\n单价");
$objWorksheet->getCell('S4')->setValue("Amount\n总额");
// $objWorksheet->getCell('Q4')->setValue("UNIT PRICE PER PC (USD)\n单价/粒数 (美金)");
$excel_row=5;

//products:
$sql = "SELECT
dbo.ord_txpako.serialno,
dbo.ord_main.del_date,
dbo.ord_main.custcode,
dbo.master_customer.custname_en,
dbo.master_customer.custname_sc,
dbo.ord_dtl.refid as orddtl_refid,
dbo.ord_main.goldrate,
dbo.ord_main.order_nbr,
dbo.ord_dtl.itemno,
dbo.ord_dtl.qty,
dbo.ord_txpako.product_wt,
dbo.ord_txpako.txqty,
dbo.ord_txpako.unitcost,
dbo.ord_txpako.unitfactcost,
dbo.ord_txpako.unitprice,
dbo.ord_txpako.status,
dbo.ord_txpako.refid as txpako_refid
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
INNER JOIN dbo.master_customer ON dbo.ord_main.custcode = dbo.master_customer.custcode
WHERE
dbo.ord_txpako.status = 'PACKED' 
$sql_clause
order by dbo.ord_txpako.orddtl_refid,dbo.ord_txpako.serialno
";        

$sth = $dbh->prepare($sql);
$sth->execute( $sql_param );
// echo $sth->getSQL( $sql_param ); exit;
while($dtl = $sth->fetch() ){
    $i = 0;
    $productrow = $excel_row;

    if($dtl['qty']>0 && $dtl['qty']<1){ 
        $dtl['unitprice'] = $dtl['unitprice'] / $dtl['qty']; //show unitprice of production unit
    }else{
        $dtl['qty'] = 1;
    }

    $objWorksheet->insertNewRowBefore($excel_row+1, 1);
    $objWorksheet->setCellValueByColumnAndRow($i++, $productrow, $dtl['serialno']);
    $objWorksheet->setCellValueByColumnAndRow($i++, $productrow, evl($dtl['custname_sc'], $dtl['custname_en']) );
    $objWorksheet->setCellValueByColumnAndRow($i++, $productrow, generateJCBarcode($dtl['orddtl_refid']) );
    $objWorksheet->setCellValueByColumnAndRow($i++, $productrow, $dtl['goldrate'] );
    $objWorksheet->setCellValueByColumnAndRow($i++, $productrow, $dtl['order_nbr'] );
    $objWorksheet->setCellValueByColumnAndRow($i++, $productrow, $dtl['itemno'] );
    $objWorksheet->setCellValueByColumnAndRow($i++, $productrow, $dtl['qty'] );
    $objWorksheet->setCellValueByColumnAndRow($i++, $productrow, $dtl['product_wt'] );
    // $objWorksheet->setCellValueByColumnAndRow($i++, $productrow, $dtl['order_nbr'] );

    $i+=11;

    $objWorksheet->setCellValueByColumnAndRow($i++, $productrow, $dtl['unitcost'] );
    $objWorksheet->setCellValueByColumnAndRow($i++, $productrow, $dtl['unitfactcost'] );
    $objWorksheet->setCellValueByColumnAndRow($i++, $productrow, $dtl['unitprice'] );

    $pak_desc = pak_itemdesc_array($dtl, $settings);
    // vdump($dtl['txpako_refid'], $pak_desc); exit;
    $allMetal = $pak_desc['material'];
    $allStone = array_merge((array)$pak_desc['stone'], (array)$pak_desc['diamond']);

    //reset array pointer for each() function
    if(is_array($allMetal)){
        reset($allMetal);
    }
    if(is_array($allStone)){
        reset($allStone);
    }

    //vdump($r);
//    vdump($dtl['itemno'],//$dtl['refid'], $allMetal, $allStone, $allLabor);exit;
    $addition_row = max( count($allMetal), count($allStone) )-1;

    for($i=0; $i<=$addition_row; $i++){        
        //vdump( $r['key'], $r['value'] );
        if(is_array($allMetal)){
            $r = each($allMetal);
            if( !empty($r) ){
                $j=8;
                $objWorksheet->setCellValueByColumnAndRow($j++, $excel_row, $r['value']['mattype'] );
                $objWorksheet->setCellValueByColumnAndRow($j++, $excel_row, $r['value']['matcolor'] );
                $j++;
                // $objWorksheet->setCellValueByColumnAndRow($j++, $excel_row, $r['value']['gross_wt'] );
                $objWorksheet->setCellValueByColumnAndRow($j++, $excel_row, $r['value']['weight'] );
            }    
        }
            
        if(is_array($allStone)){    
            $r = each($allStone);
            if( !empty($r) ){
                $j=12;
                $objWorksheet->setCellValueByColumnAndRow($j++, $excel_row, $r['value']['type'] );
                $objWorksheet->setCellValueByColumnAndRow($j++, $excel_row, $r['value']['shape'] );
                $objWorksheet->setCellValueByColumnAndRow($j++, $excel_row, $r['value']['size'] );
                $objWorksheet->setCellValueByColumnAndRow($j++, $excel_row, $r['value']['qty'] );
                $objWorksheet->setCellValueByColumnAndRow($j++, $excel_row, $r['value']['weight'] );
                $objWorksheet->setCellValueByColumnAndRow($j++, $excel_row, numf($r['value']['price_USD']) . '/' . $r['value']['uom_price'] );
                $objWorksheet->setCellValueByColumnAndRow($j++, $excel_row, $r['value']['amount_USD'] );
            }
        }
        
        $objWorksheet->insertNewRowBefore($excel_row+1, 1);
        $excel_row++;        
    }
    
    
    $endrow = ($excel_row++)-1;    
    if($endrow>$productrow){
        $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("E$productrow:E$endrow");
        $objWorksheet->mergeCells("F$productrow:F$endrow");
        $objWorksheet->mergeCells("G$productrow:G$endrow");
        $objWorksheet->mergeCells("H$productrow:H$endrow");
        $objWorksheet->mergeCells("T$productrow:T$endrow");
        $objWorksheet->mergeCells("U$productrow:U$endrow");
        $objWorksheet->mergeCells("V$productrow:V$endrow");
    }
    $objWorksheet->getRowDimension($excel_row-1)->setRowHeight(3);
}

$objWorksheet->getColumnDimension('A')->setVisible(false);
$objWorksheet->getColumnDimension('K')->setVisible(false);


// exit;

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Dispatch_form_'.date('Y-m-d-H-i-s').'.xls"');
header('Cache-Control: max-age=0');

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

flush();