<?php namespace myretail\controllers;use Yii;use yii\web\Controller;use myretail\models\SyncConfig;use myretail\models\ExportAccountToJurnal;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 actionStockIn(){$connMyRetail=SyncConfig::getConnection('MyRetail');$stmt=mysqli_query($connMyRetail,"SELECT fpkey, fname FROM inv_warehouse");$listWarehouse=mysqli_fetch_all($stmt,MYSQLI_ASSOC);$listWarehouse=array_column($listWarehouse,'fname','fpkey');$listAccount=ExportAccountToJurnal::getAccountData();return $this->render('stock-in',[ 'listWarehouse'=>$listWarehouse,'listAccount'=>$listAccount ]);}public function actionStockOut(){$connMyRetail=SyncConfig::getConnection('MyRetail');$stmt=mysqli_query($connMyRetail,"SELECT fpkey, fname FROM inv_warehouse");$listWarehouse=mysqli_fetch_all($stmt,MYSQLI_ASSOC);$listWarehouse=array_column($listWarehouse,'fname','fpkey');$listAccount=ExportAccountToJurnal::getAccountData();return $this->render('stock-out',[ 'listWarehouse'=>$listWarehouse,'listAccount'=>$listAccount ]);}public function actionStockAdjustment(){$connMyRetail=SyncConfig::getConnection('MyRetail');$stmt=mysqli_query($connMyRetail,"SELECT fpkey, fname FROM inv_warehouse");$listWarehouse=mysqli_fetch_all($stmt,MYSQLI_ASSOC);$listWarehouse=array_column($listWarehouse,'fname','fpkey');$listAccount=ExportAccountToJurnal::getAccountData();return $this->render('stock-adjustment',[ 'listWarehouse'=>$listWarehouse,'listAccount'=>$listAccount ]);}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);header("Content-type: text/csv");header("Content-disposition: attachment; filename = warehouse_transfer.csv");readfile("../export/warehouse_transfer.csv");}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);header("Content-type: text/csv");header("Content-disposition: attachment; filename = payment_purchase.csv");readfile("../export/payment_purchase.csv");}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, f.funit
            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
            JOIN inv_item_unit f ON d.fbuy_unitkey = f.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']=$row['funit'];$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);header("Content-type: text/csv");header("Content-disposition: attachment; filename = purchase.csv");readfile("../export/purchase.csv");}public function actionCreateStockIn($date,$wh_no,$wh_name,$acc_no,$acc_name){$connMyRetail=SyncConfig::getConnection('MyRetail');if(!empty($date))$filter="WHERE a.fdate >= '$date 00:00:00' AND a.fdate <= '$date 23:59:59'";else $filter="";$date_created=date_create($date);$tgl=date_format($date_created,'d/m/Y');$stmt=mysqli_query($connMyRetail,"SELECT c.fcode, a.fdate, b.fname, fitem_qty
            FROM inv_item_in_header a
            JOIN inv_item_in_detail b ON a.fpkey = b.frefkey
            JOIN inv_item c ON b.fitemkey = c.fpkey
            WHERE a.fdate >= '$date 00:00:00' AND a.fdate <= '$date 23:59:59' AND fwarehouse = '$wh_no'");$result=array();$result[0][0]="Tipe*";$result[0][1]="general";$result[0][2]="movement";$result[1][0]="Tanggal*";$result[1][1]=$tgl;$result[2][0]="Akun*";$result[2][1]=$acc_no;$result[2][2]=$acc_name;$result[3][0]="Gudang";$result[3][1]=$wh_name;$result[4][0]="NamaProduk*";$result[4][1]="KodeProduk";$result[4][2]="Jumlah tercatat";$result[4][3]="Jumlah Sebenarnya*";$result[4][4]="Difference*";$result[4][5]="HargaRataRata (hanya diisi bila harga kustom)";$i=5;while($row=mysqli_fetch_assoc($stmt)){$result[$i]['namaProduk']=$row['fname'];$result[$i]['kodeProduk']=$row['fcode'];$result[$i]['jml_catat']="";$result[$i]['jml_sbnr']="";$result[$i]['perubahan']=$row['fitem_qty'];$result[$i]['avg']="";$i++;}$fh=fopen("../export/stock_in.csv","w");foreach($result as $field){fputcsv($fh,$field);}fclose($fh);header("Content-type: text/csv");header("Content-disposition: attachment; filename = stock_in.csv");readfile("../export/stock_in.csv");}public function actionCreateStockOut($date,$wh_no,$wh_name,$acc_no,$acc_name){$connMyRetail=SyncConfig::getConnection('MyRetail');if(!empty($date))$filter="WHERE a.fdate >= '$date 00:00:00' AND a.fdate <= '$date 23:59:59'";else $filter="";$date_created=date_create($date);$tgl=date_format($date_created,'d/m/Y');$stmt=mysqli_query($connMyRetail,"SELECT c.fcode, a.fdate, b.fname, fitem_qty
            FROM inv_item_out_header a
            JOIN inv_item_out_detail b ON a.fpkey = b.frefkey
            JOIN inv_item c ON b.fitemkey = c.fpkey
            WHERE a.fdate >= '$date 00:00:00' AND a.fdate <= '$date 23:59:59' AND fwarehouse = '$wh_no'");$result=array();$result[0][0]="Tipe*";$result[0][1]="general";$result[0][2]="movement";$result[1][0]="Tanggal*";$result[1][1]=$tgl;$result[2][0]="Akun*";$result[2][1]=$acc_no;$result[2][2]=$acc_name;$result[3][0]="Gudang";$result[3][1]=$wh_name;$result[4][0]="NamaProduk*";$result[4][1]="KodeProduk";$result[4][2]="Jumlah tercatat";$result[4][3]="Jumlah Sebenarnya*";$result[4][4]="Difference*";$result[4][5]="HargaRataRata (hanya diisi bila harga kustom)";$i=5;while($row=mysqli_fetch_assoc($stmt)){$result[$i]['namaProduk']=$row['fname'];$result[$i]['kodeProduk']=$row['fcode'];$result[$i]['jml_catat']="";$result[$i]['jml_sbnr']="";$result[$i]['perubahan']="-".$row['fitem_qty'];$result[$i]['avg']="";$i++;}$fh=fopen("../export/stock_out.csv","w");foreach($result as $field){fputcsv($fh,$field);}fclose($fh);header("Content-type: text/csv");header("Content-disposition: attachment; filename = stock_out.csv");readfile("../export/stock_out.csv");}public function actionCreateStockAdjustment($date,$wh_no,$wh_name,$acc_no,$acc_name){$connMyRetail=SyncConfig::getConnection('MyRetail');if(!empty($date))$filter="WHERE a.fdate >= '$date 00:00:00' AND a.fdate <= '$date 23:59:59'";else $filter="";$date_created=date_create($date);$tgl=date_format($date_created,'d/m/Y');$stmt=mysqli_query($connMyRetail,"SELECT c.fcode, a.fdate, b.fname, fitem_before_qty, fitem_after_qty
            FROM inv_adjustment_header a
            JOIN inv_adjustment_detail b ON a.fpkey = b.frefkey
            JOIN inv_item c ON b.fitemkey = c.fpkey
            WHERE a.fdate >= '$date 00:00:00' AND a.fdate <= '$date 23:59:59' AND fwarehouse = '$wh_no'");$result=array();$result[0][0]="Tipe*";$result[0][1]="general";$result[0][2]="count";$result[1][0]="Tanggal*";$result[1][1]=$tgl;$result[2][0]="Akun*";$result[2][1]=$acc_no;$result[2][2]=$acc_name;$result[3][0]="Gudang";$result[3][1]=$wh_name;$result[4][0]="NamaProduk*";$result[4][1]="KodeProduk";$result[4][2]="Jumlah tercatat";$result[4][3]="Actual Qty*";$result[4][4]="Perubahan";$result[4][5]="HargaRataRata (hanya diisi bila harga kustom)";$i=5;while($row=mysqli_fetch_assoc($stmt)){$result[$i]['namaProduk']=$row['fname'];$result[$i]['kodeProduk']=$row['fcode'];$result[$i]['before']=$row['fitem_before_qty'];$result[$i]['after']=$row['fitem_after_qty'];$result[$i]['perubahan']="";$result[$i]['avg']="";$i++;}$fh=fopen("../export/stock_adjustment.csv","w");foreach($result as $field){fputcsv($fh,$field);}fclose($fh);header("Content-type: text/csv");header("Content-disposition: attachment; filename = stock_adjustment.csv");readfile("../export/stock_adjustment.csv");}}