<?php

// include woo-vtiger login access file
include_once(WOO_VTIGER_INCLUDES_DIR . 'woo-vtiger-login-access.php');

// Get last synchronize date from database
$module_type = 'import orders';
$last_sync_date = get_last_sync_date($module_type);


//-------------------------- Get data from vTiger SalesOrder Module --------------------

    //query to select data from the server.
    $query = "SELECT count(*) from SalesOrder;";

    //urlencode to as its sent over http.
    $queryParam = urlencode($query);

    //sessionId is obtained from login result.
    $params = "sessionName=$sessionId&operation=query&query=$queryParam";

    //query must be GET Request.
    $curl->get("$endpointUrl?$params");
    $response = $curl->response;

    //decode the json encode response from the server.
    $jsonResponse = json_decode($response, true);

    //operation was successful get the token from the reponse.
    //if($jsonResponse['success']==false)
        //handle the failure case.
        //die('query failed:'.$jsonResponse['errorMsg']);

    //Array of vtigerObjects
   	$retrievedObjects = array();
    $retrievedObjects = $jsonResponse['result'];

    $total_items = $retrievedObjects[0]['count'];
    $recievedItems = Array();

    // Array Match status order
    $arrMatchStatus = array(
        'pending' => 'Pending payment',
        'processing' => 'Processing',
        'on-hold' => 'On hold',
        'completed' => 'Approved',
        'cancelled' => 'Cancelled',
        'refunded' => 'Refunded',
        'failed' => 'Failed'
    );


    for($i=0; $i <= $total_items;)
    {
    	ob_start();
        $query = "SELECT * from SalesOrder limit ".$i.", ".($i+100).";";

        $queryParam = urlencode($query);
        $params = "sessionName=$sessionId&operation=query&query=$queryParam";

        $curl->get("$endpointUrl?$params");
        $response = $curl->response;
        $jsonResponse = json_decode($response, true);

        //if($jsonResponse['success']==false)
            //die('query failed:'.$jsonResponse['errorMsg']);

        $retrievedObjects = array();
        $retrievedObjects = $jsonResponse['result'];

        foreach ($retrievedObjects as $order) {
            $recievedItems[] = $order;
        }

        $i += 100;

        echo ob_get_contents();
        ob_end_flush();
    }

    $all_salesorder_no = array();
    $all_modifiedtime = array();

    foreach ($recievedItems as $item) 
    {
    	ob_start();
    	$all_salesorder_no[] = $item['salesorder_no'];

    	// it is for to count only modified orders
    	if($item['modifiedtime'] > $last_sync_date)
    	{
    		$all_modifiedtime[] = $item['modifiedtime'];
    	}
    	echo ob_get_contents();
        ob_end_flush();
    }

    // get uniques sales_order_no
    $unique_salesorder_no_array = array_unique($all_salesorder_no);

    // it is for to count only modified orders
    $unique_modifiedtime_array = array_unique($all_modifiedtime);

    //count total sales_orders
    $count_sales_orders = count($unique_salesorder_no_array);

    $count_created_orders = 0;
    $count_modified_orders = 0;
    $count_deleted_orders = 0;

    // loop running according to uniques sales_order_no
    foreach ($unique_salesorder_no_array as $unique_salesorder_no)
    {
    	ob_start();
    	$order_no = $unique_salesorder_no;
    	$created_product_items_array = array();
    	$updated_product_items_array = array();
    	$created_product_item = array();
    	$updated_product_item = array();
    	
    	// Get discount and tax value //
    	$sale_details = mysqli_query($db_connect,"SELECT * FROM vtiger_salesorder where salesorder_no = '$order_no' ");
        $sale_info = mysqli_fetch_array($sale_details);
        $sales_discount= $sale_info['discount_percent'];
        $sub_total = $sale_info['subtotal'];
        
        $shipping_charge = $sale_info['s_h_amount'];
        $shipping_tax = $sale_info['s_h_percent'];
       
        if($shipping_tax != '' && $shipping_tax != '0'){
			$shipping_tax_total = ($shipping_charge * $shipping_tax)/100;
        }else{
        	$shipping_tax_total = '0';
        }
        
        $shipping_charge_total = $shipping_charge + $shipping_tax_total;
        $shipping_charge_total = round($shipping_charge_total,2);
        
        if($sales_discount != '' && $sales_discount != '0'){
			$sales_discount_total = ($sub_total * $sales_discount)/100;
			$sales_discount_total = round($sales_discount_total,2);
        }else{
        	$sales_discount_total = 0;
        }

    	foreach ($recievedItems as $retrievedOrder)
    	{
		    $retrievedSalesorderNoArray = array();

		    $args = array(
			  'post_type' => 'shop_order',
              'post_status' => array('wc-pending','wc-processing','wc-on-hold','wc-completed','wc-cancelled','wc-refunded','wc-failed'),
			  'meta_key' => '_customer_user',
			  'posts_per_page' => '-1'
			);

			$my_query = new WP_Query($args);

			// get sales orders by posttype "shop_order"
			$customer_orders = $my_query->posts;

		    foreach ($customer_orders as $customer_order) 
			{
				$order = new WC_Order();
				$order->populate($customer_order);
//				$orderdata = (array) $order;
//              WooCommerce since 3.0.0.
                $orderdata = $order->get_data();
		        // get order data
				$id = $orderdata['id'];

		        // get sales order post meta
				$order_meta = get_post_meta($id);
				$salesorder_no = $order_meta['salesorder_no'][0];

		        array_push($retrievedSalesorderNoArray, $salesorder_no);
		    }

		    // check current "salesorder_no" out of array of "salesorder_no" values
        	$result = in_array($retrievedOrder['salesorder_no'], $retrievedSalesorderNoArray);

    		if($retrievedOrder['createdtime'] > $last_sync_date && $result == false && $retrievedOrder['salesorder_no'] == $order_no)
    		{

    			//------------------ Insert new sales order into woocommerce ---------------

    			//allows WP to display an error on failure
	            $wp_show_error = true;
	            
	            $current_date = date("F j, Y"); 
				$current_time = date('G:i A');

	            //insert the new sales order and get the sales order id
	            $order_id = wp_insert_post(array(
	            'post_title' => 'Order &ndash; '.$current_date.' @ '.$current_time,
	            'post_content' => $retrievedOrder['description'],
	            'post_status' => 'wc-pending',
	            'post_type' => 'shop_order',
	            'post_author' => 1   //the id of the author
	            ), $wp_show_error);

                $_order = new WC_Order($order_id);
                $order_status = (array_search($retrievedOrder['sostatus'], $arrMatchStatus)) ? array_search($retrievedOrder['sostatus'], $arrMatchStatus) : 'pending';
                $_order->set_status($order_status);
                $_order->save();

	            // get user information for sales order
	            global $wpdb;
	            $usermeta_row = $wpdb->get_results( "SELECT * FROM ".$wpdb->prefix."usermeta WHERE meta_key = 'contact_crm_id' AND meta_value='".$retrievedOrder['contact_id']."' " );
	            
	            foreach($usermeta_row as $usermeta) 
	            {
	                $user_id = $usermeta->user_id;
	                $urow = $wpdb->get_results( "SELECT * FROM ".$wpdb->prefix."usermeta WHERE user_id='".$user_id."' ");
	               
	                // get user meta
			        $user_meta = get_user_meta($user_id);

                	$billing_first_name = $user_meta['billing_first_name'][0];
                	$billing_last_name = $user_meta['billing_last_name'][0];
                	$billing_phone = $user_meta['billing_phone'][0];
                	$billing_email = $user_meta['billing_email'][0];
                	$shipping_first_name = $user_meta['shipping_first_name'][0];
                	$shipping_last_name = $user_meta['shipping_last_name'][0];
	            }

	            $total_price = round($retrievedOrder['hdnGrandTotal']);

	            //update the default order meta info
	            update_post_meta($order_id, '_edit_lock', '');
	            update_post_meta($order_id, '_order_shipping', $shipping_charge_total);
	            update_post_meta($order_id, '_cart_discount', $sales_discount_total);
	            update_post_meta($order_id, '_cart_discount_tax', $sales_discount);
	            update_post_meta($order_id, '_order_total', $total_price);
	            update_post_meta($order_id, '_order_tax', 0);
	            update_post_meta($order_id, '_order_shipping_tax', $shipping_tax_total);
	            update_post_meta($order_id, '_order_currency', '');
	            update_post_meta($order_id, '_order_version', '');
	            update_post_meta($order_id, '_edit_last', '');
	            update_post_meta($order_id, '_order_key', '');
	            update_post_meta($order_id, '_customer_user', $user_id);
	            
	            update_post_meta($order_id, '_billing_first_name', $billing_first_name);
	            update_post_meta($order_id, '_billing_last_name', $billing_last_name);
	            update_post_meta($order_id, '_billing_company', '');
	            update_post_meta($order_id, '_billing_address_1', $retrievedOrder['bill_street']);
	            update_post_meta($order_id, '_billing_address_2', '');
	            update_post_meta($order_id, '_billing_city', $retrievedOrder['bill_city']);
	            update_post_meta($order_id, '_billing_postcode', $retrievedOrder['bill_code']);
	            update_post_meta($order_id, '_billing_country', $retrievedOrder['bill_country']);
	            update_post_meta($order_id, '_billing_state', $retrievedOrder['bill_state']);
	            update_post_meta($order_id, '_billing_email', $billing_email);
	            update_post_meta($order_id, '_billing_phone', $billing_phone);

	            update_post_meta($order_id, '_shipping_first_name', $shipping_first_name);
	            update_post_meta($order_id, '_shipping_last_name', $shipping_last_name);
	            update_post_meta($order_id, '_shipping_company', '');
	            update_post_meta($order_id, '_shipping_address_1', $retrievedOrder['ship_street']);
	            update_post_meta($order_id, '_shipping_address_2', '');
	            update_post_meta($order_id, '_shipping_city', $retrievedOrder['ship_city']);
	            update_post_meta($order_id, '_shipping_postcode', $retrievedOrder['ship_code']);
	            update_post_meta($order_id, '_shipping_country', $retrievedOrder['ship_country']);
	            update_post_meta($order_id, '_shipping_state', $retrievedOrder['ship_state']);
	            update_post_meta($order_id, '_transaction_id', '');

	            update_post_meta($order_id, 'salesorder_no', $retrievedOrder['salesorder_no']);
	            update_post_meta($order_id, 'salesorder_crm_id', $retrievedOrder['id']);

	        
	            // count total new created orders
	            $count_created_orders = $count_created_orders + 1;
    		}
    		else if($retrievedOrder['modifiedtime'] > $last_sync_date && $retrievedOrder['salesorder_no'] == $order_no)
    		{
    			//-------------------- Update sales order into woocommerce --------------------


    			global $wpdb;
	            $postmeta_row = $wpdb->get_results( "SELECT * FROM ".$wpdb->prefix."postmeta WHERE meta_key = 'salesorder_no' AND meta_value='".$retrievedOrder['salesorder_no']."' " );
	            foreach($postmeta_row as $postmeta) {
	                $order_id = $postmeta->post_id;
	            }

                $_order = new WC_Order($order_id);
                $order_status = array_search($retrievedOrder['sostatus'], $arrMatchStatus);
                if ($order_status !== '') {
                    $_order->set_status($order_status);
                    $_order->save();
                }


    			//allows WP to display an error on failure
	            $wp_show_error = true;
	            
	            $current_date = date("F j, Y"); 
				$current_time = date('G:i A');

				$my_post = array(
                        'ID'           => $order_id,
                        'post_content' => $retrievedOrder['description'],
                        'post_type' => 'shop_order',
                        'post_author' => 1   //the id of the author
                    );

            	wp_update_post( $my_post );

	            // get user information for sales order
	            global $wpdb;
	            $usermeta_row = $wpdb->get_results( "SELECT * FROM ".$wpdb->prefix."usermeta WHERE meta_key = 'contact_crm_id' AND meta_value='".$retrievedOrder['contact_id']."' " );
	            
	            foreach($usermeta_row as $usermeta) 
	            {
	                $user_id = $usermeta->user_id;
	                $urow = $wpdb->get_results( "SELECT * FROM ".$wpdb->prefix."usermeta WHERE user_id='".$user_id."' ");
	               
	                // get user meta
			        $user_meta = get_user_meta($user_id);

                	$billing_first_name = $user_meta['billing_first_name'][0];
                	$billing_last_name = $user_meta['billing_last_name'][0];
                	$billing_phone = $user_meta['billing_phone'][0];
                	$billing_email = $user_meta['billing_email'][0];
                	$shipping_first_name = $user_meta['shipping_first_name'][0];
                	$shipping_last_name = $user_meta['shipping_last_name'][0];
	            }

	            $total_price = round($retrievedOrder['hdnGrandTotal']);

	            //update the default order meta info
	            update_post_meta($order_id, '_edit_lock', '');
	            update_post_meta($order_id, '_order_shipping', $shipping_charge_total);
	            update_post_meta($order_id, '_cart_discount', $sales_discount_total);
	            update_post_meta($order_id, '_cart_discount_tax', $sales_discount);
	            update_post_meta($order_id, '_order_total', $total_price);
	            update_post_meta($order_id, '_order_tax', 0);
	            update_post_meta($order_id, '_order_shipping_tax', $shipping_tax_total);
	            update_post_meta($order_id, '_order_currency', '');
	            update_post_meta($order_id, '_order_version', '');
	            update_post_meta($order_id, '_edit_last', '');
	            update_post_meta($order_id, '_order_key', '');
	            update_post_meta($order_id, '_customer_user', $user_id);
	            
	            update_post_meta($order_id, '_billing_first_name', $billing_first_name);
	            update_post_meta($order_id, '_billing_last_name', $billing_last_name);
	            update_post_meta($order_id, '_billing_company', '');
	            update_post_meta($order_id, '_billing_address_1', $retrievedOrder['bill_street']);
	            update_post_meta($order_id, '_billing_address_2', '');
	            update_post_meta($order_id, '_billing_city', $retrievedOrder['bill_city']);
	            update_post_meta($order_id, '_billing_postcode', $retrievedOrder['bill_code']);
	            update_post_meta($order_id, '_billing_country', $retrievedOrder['bill_country']);
	            update_post_meta($order_id, '_billing_state', $retrievedOrder['bill_state']);
	            update_post_meta($order_id, '_billing_email', $billing_email);
	            update_post_meta($order_id, '_billing_phone', $billing_phone);

	            update_post_meta($order_id, '_shipping_first_name', $shipping_first_name);
	            update_post_meta($order_id, '_shipping_last_name', $shipping_last_name);
	            update_post_meta($order_id, '_shipping_company', '');
	            update_post_meta($order_id, '_shipping_address_1', $retrievedOrder['ship_street']);
	            update_post_meta($order_id, '_shipping_address_2', '');
	            update_post_meta($order_id, '_shipping_city', $retrievedOrder['ship_city']);
	            update_post_meta($order_id, '_shipping_postcode', $retrievedOrder['ship_code']);
	            update_post_meta($order_id, '_shipping_country', $retrievedOrder['ship_country']);
	            update_post_meta($order_id, '_shipping_state', $retrievedOrder['ship_state']);
	            update_post_meta($order_id, '_transaction_id', '');

	            update_post_meta($order_id, 'salesorder_no', $retrievedOrder['salesorder_no']);
	            update_post_meta($order_id, 'salesorder_crm_id', $retrievedOrder['id']);

	            //count updated orders
	            $count_modified_orders = count($unique_modifiedtime_array);

	            // delete previous order items
		    	global $wpdb;
	        	$table_name = $wpdb->prefix.'woocommerce_order_items';
				$wpdb->query($wpdb->prepare("DELETE FROM ".$table_name." WHERE order_id = '%d'",$order_id));
    		}
    		
    		if($retrievedOrder['createdtime'] > $last_sync_date && $retrievedOrder['salesorder_no'] == $order_no)
    		{
    			//------------------ Insert product items for sales order ---------------

	    		$created_product_item['productid'] = $retrievedOrder['productid'];
	    		$created_product_item['quantity'] = $retrievedOrder['quantity'];
	    		$created_product_item['listprice'] = round($retrievedOrder['listprice']);
	    		$created_product_item['comment'] = $retrievedOrder['comment'];
	    		$created_product_item['pre_tax_total'] = round($retrievedOrder['pre_tax_total']);
	    		$created_product_item['hdnS_H_Percent'] = $retrievedOrder['hdnS_H_Percent'];
	    		array_push($created_product_items_array, $created_product_item);
		    }
		    else if($retrievedOrder['modifiedtime'] > $last_sync_date && $retrievedOrder['salesorder_no'] == $order_no)
    		{
    			//------------------ Update product items for sales order ---------------

	    		$updated_product_item['productid'] = $retrievedOrder['productid'];
	    		$updated_product_item['quantity'] = $retrievedOrder['quantity'];
	    		$updated_product_item['listprice'] = round($retrievedOrder['listprice']);
	    		$updated_product_item['comment'] = $retrievedOrder['comment'];
	    		$updated_product_item['pre_tax_total'] = round($retrievedOrder['pre_tax_total']);
	    		$updated_product_item['hdnS_H_Percent'] = $retrievedOrder['hdnS_H_Percent'];
	    		array_push($updated_product_items_array, $updated_product_item);
		    }
	    }

	    foreach ($created_product_items_array as $created_order_item)
	    {
	    	// get user information for sales order
            global $wpdb;
            $product_row = $wpdb->get_results( "SELECT * FROM ".$wpdb->prefix."postmeta WHERE meta_key = 'product_crm_id' AND meta_value='".$created_order_item['productid']."' " );
            foreach($product_row as $prodmeta)
            {
                $product_id = $prodmeta->post_id;

                $prow = $wpdb->get_results( "SELECT * FROM ".$wpdb->prefix."posts WHERE ID='".$product_id."' ");
                foreach($prow as $prod) {
                	$product_name = $prod->post_title;
                }
	        }

	        // insert order items
	    	global $wpdb;
            $table_name = $wpdb->prefix . 'woocommerce_order_items';
	    	$wpdb->insert($table_name, array(
										'order_item_name' => $product_name,
										'order_item_type' => 'line_item',
										'order_id'=> $order_id
										));
	    	$order_item_id = $wpdb->insert_id;
	    	$sub_total = $created_order_item['quantity']*$created_order_item['listprice'];
	    	$sub_total = round($sub_total,2);

	    	// insert order itemmeta info
	    	global $wpdb;
	    	$table_name = $wpdb->prefix.'woocommerce_order_itemmeta';
	    	$wpdb->insert($table_name, array('order_item_id' => $order_item_id, 'meta_key' => '_qty', 'meta_value'=> $created_order_item['quantity'] ));
	    	$wpdb->insert($table_name, array('order_item_id' => $order_item_id, 'meta_key' => '_tax_class', 'meta_value'=> '' ));
	    	$wpdb->insert($table_name, array('order_item_id' => $order_item_id, 'meta_key' => '_product_id', 'meta_value'=> $product_id ));
	    	$wpdb->insert($table_name, array('order_item_id' => $order_item_id, 'meta_key' => '_variation_id', 'meta_value'=> '' ));
	    	$wpdb->insert($table_name, array('order_item_id' => $order_item_id, 'meta_key' => '_line_subtotal', 'meta_value'=> $sub_total ));
	    	$wpdb->insert($table_name, array('order_item_id' => $order_item_id, 'meta_key' => '_line_subtotal_tax', 'meta_value'=> 0 ));
	    	$wpdb->insert($table_name, array('order_item_id' => $order_item_id, 'meta_key' => '_line_total', 'meta_value'=> $sub_total ));
	    	$wpdb->insert($table_name, array('order_item_id' => $order_item_id, 'meta_key' => '_line_tax', 'meta_value'=> 0 ));
	    	$wpdb->insert($table_name, array('order_item_id' => $order_item_id, 'meta_key' => '_line_tax_data', 'meta_value'=> '' ));
	    }

	    foreach ($updated_product_items_array as $updated_order_item)
	    {
	    	// get user information for updated sales order
            global $wpdb;
            $product_row = $wpdb->get_results( "SELECT * FROM ".$wpdb->prefix."postmeta WHERE meta_key = 'product_crm_id' AND meta_value='".$updated_order_item['productid']."' " );
            foreach($product_row as $prodmeta)
            {
                $product_id = $prodmeta->post_id;

                $prow = $wpdb->get_results( "SELECT * FROM ".$wpdb->prefix."posts WHERE ID='".$product_id."' ");
                foreach($prow as $prod) {
                	$product_name = $prod->post_title;
                }
	        }

	        $sub_total = $updated_order_item['quantity']*$updated_order_item['listprice'];
	    	$sub_total = round($sub_total,2);
	        // update new order items
	    	global $wpdb;
	    	$table_name = $wpdb->prefix.'woocommerce_order_items';
	    	$wpdb->insert($table_name, array(
										'order_item_name' => $product_name,
										'order_item_type' => 'line_item',
										'order_id'=> $order_id
										));
	    	$order_item_id = $wpdb->insert_id;

	    	// update new order items meta
	    	global $wpdb;
	    	$table_name = $wpdb->prefix.'woocommerce_order_itemmeta';
	    	$wpdb->insert($table_name, array('order_item_id' => $order_item_id, 'meta_key' => '_qty', 'meta_value'=> $updated_order_item['quantity'] ));
	    	$wpdb->insert($table_name, array('order_item_id' => $order_item_id, 'meta_key' => '_tax_class', 'meta_value'=> '' ));
	    	$wpdb->insert($table_name, array('order_item_id' => $order_item_id, 'meta_key' => '_product_id', 'meta_value'=> $product_id ));
	    	$wpdb->insert($table_name, array('order_item_id' => $order_item_id, 'meta_key' => '_variation_id', 'meta_value'=> '' ));
	    	$wpdb->insert($table_name, array('order_item_id' => $order_item_id, 'meta_key' => '_line_subtotal', 'meta_value'=> $sub_total ));
	    	$wpdb->insert($table_name, array('order_item_id' => $order_item_id, 'meta_key' => '_line_subtotal_tax', 'meta_value'=> 0 ));
	    	$wpdb->insert($table_name, array('order_item_id' => $order_item_id, 'meta_key' => '_line_total', 'meta_value'=> $sub_total ));
	    	$wpdb->insert($table_name, array('order_item_id' => $order_item_id, 'meta_key' => '_line_tax', 'meta_value'=> 0 ));
	    	$wpdb->insert($table_name, array('order_item_id' => $order_item_id, 'meta_key' => '_line_tax_data', 'meta_value'=> '' ));
	    }
	    echo ob_get_contents();
        ob_end_flush();
    }


//-------------------------- Delete orders in WooCommerce -----------------------------

    // Fetch deleted orders from vtiger crm
    $sql1 = "SELECT * FROM vtiger_crmentity WHERE setype = 'SalesOrder' AND deleted = '1' ";
    $res1 = mysqli_query($db_connect, $sql1);
    while($row1 = mysqli_fetch_array($res1))
    {
        if ($row1['modifiedtime'] > $last_sync_date)
        {
            $sql2 = "SELECT * FROM vtiger_salesorder WHERE salesorderid = ".$row1['crmid'];
            $res2 = mysqli_query($db_connect, $sql2);
            $row2 = mysqli_fetch_array($res2);
            $salesOrderNo = $row2['salesorder_no'];

            // get post info that have to delete
            global $wpdb;
            $meta = $wpdb->get_results("SELECT * FROM ".$wpdb->prefix."postmeta WHERE meta_key='salesorder_no' AND meta_value='".$salesOrderNo."' ");   
            foreach($meta as $postmeta)
            {
                // count deleted orders
                $count_deleted_orders = $count_deleted_orders + 1;
                // order id of delete orders
                $postId = $postmeta->post_id;
                // delete post
                wp_delete_post($postId);
                // delete postmeta
                delete_post_meta($postId, '', '');

                global $wpdb;
            	$table_name = $wpdb->prefix.'woocommerce_order_items';
                $delete_id = $wpdb->delete( $table_name, array( 'order_id' => $postId ) );
            }
        }
    }

    // Update last synchronization date
    //$date = current_time( 'mysql' );
    $date = date("Y-m-d H:i:s");
    global $wpdb;
    $wpdb->query("UPDATE ".$wpdb->prefix."WVS_sync_date_info SET last_sync_date = '".$date."' WHERE id = 5 ");

?>