<?php namespace myretail\controllers;use Yii;use yii\web\Controller;use myretail\models\SyncConfig;class ExportController extends Controller{public function actionWarehouse(){$connMyRetail=SyncConfig::getConnection('MyRetail');$stmt=mysqli_query($connMyRetail,"SELECT fpkey, fcode FROM inv_item_transfer_header");$list=mysqli_fetch_all($stmt,MYSQLI_ASSOC);$list=array_column($list,'fcode','fpkey');return $this->render('warehouse',[ 'list'=>$list ]);}public function actionPurchasePayment(){return $this->render('purchase-payment');}public function actionPurchase(){return $this->render('purchase');}public function actionCreateWarehouseTransfer($id){$connMyRetail=SyncConfig::getConnection('MyRetail');$stmt=mysqli_query($connMyRetail,"SELECT b.fname AS ware_from, c.fname AS ware_to, a.fdate 
            FROM inv_item_transfer_header a
            JOIN inv_warehouse b ON a.ffromwarehouse = b.fpkey
            JOIN inv_warehouse c ON a.ftowarehouse = c.fpkey
            WHERE a.fpkey = $id");$row=mysqli_fetch_assoc($stmt);$result=array();$result[0]['index']='From*';$result[0]['detail']=$row['ware_from'];$result[1]['index']='To*';$result[1]['detail']=$row['ware_to'];$result[2]['index']='Date*';$result[2]['detail']=$row['fdate'];$result[3]['index']='';$result[3]['detail']='';$result[4]['index']='ProductName*';$result[4]['detail']='TransferQuantity*';$stmt=mysqli_query($connMyRetail,"SELECT b.fname, fitem_qty
            FROM inv_item_transfer_detail a
            JOIN inv_item b ON a.fitemkey = b.fpkey
            WHERE frefkey = '$id'");$i=5;while($row=mysqli_fetch_assoc($stmt)){$result[$i]['product_name']=$row['fname'];$result[$i]['item_qty']=$row['fitem_qty'];$i++;}$fh=fopen("../export/warehouse_transfer.csv","w");foreach($result as $field){fputcsv($fh,$field);}fclose($fh);return "Done";}public function actionCreatePurchasePayment($start,$end){if(!empty($start))$filterStart="WHERE a.fap_date >= '$start'";else $filterStart="";if(!empty($end))$filterEnd="AND a.fap_date <= '$end'";else $filterEnd="";$connMyRetail=SyncConfig::getConnection('MyRetail');$dbMyConnection='myconnection';$headers=array('*Payment Date','*Payment Number','*Invoice Number','*Pay from account(Code)','*Amount','*PaymentMethod','Tags(use ; to separate tags)');$stmt=mysqli_query($connMyRetail,"SELECT b.fcreateddt, a.fap_code, a.fref_code, b.famount, c.ftype, d.number_akun
            FROM ap_ap a
            JOIN ap_ap_detail b ON a.fpkey = b.fap_key
            JOIN ap_payment_type c ON b.fpayment_type = c.fpkey
            JOIN $dbMyConnection.export_account_to_jurnal d ON b.fpayment_type = d.payment_fpkey
            $filterStart $filterEnd");$result=array();$i=0;while($row=mysqli_fetch_assoc($stmt)){$result[$i]['payment_date']=$row['fcreateddt'];$result[$i]['payment_code']=$row['fap_code'];$result[$i]['invoice_number']=$row['fref_code'];$result[$i]['account']=$row['number_akun'];$result[$i]['amount']=$row['famount'];$result[$i]['payment_method']=$row['ftype'];$result[$i]['tags']="";$i++;}$fh=fopen("../export/payment_purchase.csv","w");fputcsv($fh,$headers);foreach($result as $field){fputcsv($fh,$field);}fclose($fh);return "Done";}public function actionCreatePurchase($start,$end){if(!empty($start))$filterStart="WHERE a.fdate >= '$start'";else $filterStart="";if(!empty($end))$filterEnd="AND a.fdate <= '$end'";else $filterEnd="";$connMyRetail=SyncConfig::getConnection('MyRetail');$headers=array('*Vendor','Email','BillingAddress','ShippingAddress','*PurchaseDate','*DueDate','ShippingDate','ShipVia','TrackingNo','VendorRefNo','*PurchaseNumber','Message','Memo','*ProductName','Description','*Quantity','Unit','*UnitPrice','ProductDiscountRate(%)','PurchaseDiscountRate(%)','TaxName','TaxRate(%)','ShippingFee','#paid?(yes/no)','#PaymentMethod','#PaidFromAccountCode','Tags (use ; to separate tags)','WarehouseName');$stmt=mysqli_query($connMyRetail,"SELECT b.fname AS supp_name, femail AS email, a.fdate AS purch_date, a.fdue_date AS due_date, a.fcode AS purch_numb, d.fname AS prod_name, fitem_qty AS prod_qty, c.fprice AS unit_price, e.fname AS warehouse
            FROM prc_header a
            JOIN bp_supplier b ON a.fsuppkey = b.fpkey
            JOIN prc_detail c ON a.fpkey = c.frefkey
            JOIN inv_item d ON c.fitemkey = d.fpkey
            JOIN inv_warehouse e ON a.fwarehouse = e.fpkey
            $filterStart $filterEnd");$result=array();$i=0;while($row=mysqli_fetch_assoc($stmt)){$result[$i]['Vendor']=$row['supp_name'];$result[$i]['Email']=$row['email'];$result[$i]['BillingAddress']="";$result[$i]['ShippingAddress']="";$result[$i]['PurchaseDate']=$row['purch_date'];$result[$i]['DueDate']=$row['due_date'];$result[$i]['ShippingDate']="";$result[$i]['ShipVia']="";$result[$i]['TrackingNo']="";$result[$i]['VendorRefNo']="";$result[$i]['PurchaseNumber']=$row['purch_numb'];$result[$i]['Message']="";$result[$i]['Memo']="";$result[$i]['ProductName']=$row['prod_name'];$result[$i]['Description']="";$result[$i]['Quantity']=$row['prod_qty'];$result[$i]['Unit']="";$result[$i]['UnitPrice']=$row['unit_price'];$result[$i]['ProductDiscountRate']="";$result[$i]['PurchaseDiscountRate']="";$result[$i]['TaxName']="";$result[$i]['TaxRate']="";$result[$i]['ShippingFee']="";$result[$i]['paid']="no";$result[$i]['PaymentMethod']="";$result[$i]['PaidFromAccountCode']="";$result[$i]['tags']="";$result[$i]['WarehouseName']=$row['warehouse'];$i++;}$fh=fopen("../export/purchase.csv","w");fputcsv($fh,$headers);foreach($result as $field){fputcsv($fh,$field);}fclose($fh);return "Done";}}