<?php

ob_start();
require_once('../../../../wp-config.php');

$order_id = $_POST["order_id"];

$shipment_tracking_items = get_post_meta( $order_id, '_wc_shipment_tracking_items', true );
$customer_name =  $shipment_tracking_items[0]["tracking_provider"];
if($customer_name=="vcs") $customer_name = "VCS";
elseif ($customer_name=="mp") $customer_name = "MNP";
elseif ($customer_name=="callcourier") $customer_name = "CC";
elseif ($customer_name=="swyft-logistics") $customer_name = "SL";
$track_id = $shipment_tracking_items[0]["tracking_number"];
$track_id = substr($track_id, -5);

//echo "new,211";
//$order_id = 5749;
//$customer_name = "VCS";
//$track_id = "12345";

$table_prefix = "0_";
$reference = $order_id."/".$track_id."/".$customer_name;
$debtor_no = get_debtor_no($table_prefix, $customer_name);

$isnewtrans = isnewtrans($table_prefix, $order_id);

if ($isnewtrans > 1){ // existing transaction
	$fa = get_post_meta( $order_id , '_fa', true );
	if(empty($fa))
			add_post_meta($order_id, '_fa', $isnewtrans);
	else
			update_post_meta($order_id, '_fa', $isnewtrans);
	ob_end_clean();
	echo "exits,";
	echo $isnewtrans;

}elseif ($isnewtrans == 1){ // new transaction
	if (verifyskucodes($order_id)==0){
		ob_end_clean();
		echo "skuerror,Missing SKU for one or more product(s)";
	}else{
		$id = addnewtransaction($table_prefix, $order_id, $reference, $debtor_no);
		$fa = get_post_meta( $order_id , '_fa', true );
		if(empty($fa))
				add_post_meta($order_id, '_fa', $id);
		else
				update_post_meta($order_id, '_fa', $id);
		ob_end_clean();
		echo "new,";
		echo $id;
	}
}

function verifyskucodes($order_id){
	$order = new WC_Order( $order_id );
    $items = $order->get_items();

	foreach( $items as $item ) {
		$product_id = $item->get_product_id();
		$product = new WC_Product($item['product_id']);
		$sku = $product->get_sku();
		if (empty($sku)){
			return 0;
		}
	}
	return 1;
}

function addnewtransaction($table_prefix, $order_id, $reference, $debtor_no){


// CREATE NEW REFERENCE
$id = refs($table_prefix, $reference);
echo "<hr >";
// $id = 208;

// Sales Order Entry
sales_order($table_prefix, $id, $order_id, $debtor_no);
echo "<hr >";
// Sales Order Details Entry
sales_order_details($table_prefix, $id, $order_id);
echo "<hr >";
// Debtor Trans Entry
debtor_trans($table_prefix, $id, $order_id,$debtor_no, $reference);
echo "<hr >";
// Debtor Trans Detail Entery
debtor_trans_details($table_prefix, $id, $order_id,$debtor_no, $reference);
echo "<hr >";
// Trans Tax Detail
trans_tax_details($table_prefix, $id, $order_id,$debtor_no, $reference);
echo "<hr >";
// GLtransacgtions
gl_trans($table_prefix, $id, $order_id);
echo "<hr >";
stock_moves($table_prefix, $id, $order_id);
echo "<hr >";

return $id;

}

function isnewtrans($table_prefix, $order_id){

	$conn = db();
	echo $sql = "SELECT id FROM $table_prefix"."refs where reference in (".$order_id.")";
	echo "<br />";
	$result = $conn->query($sql);
	$row = $result->fetch_assoc();
	if (!empty($row["id"]))
		return $row["id"];
	else
		return 1;
}

function stock_moves($table_prefix, $id, $order_id){
	$order = new WC_Order( $order_id );
	$tran_date = $order->order_date;
	$dt = new DateTime($tran_date);
	$tran_date = $dt->format('Y-m-d');	
    $items = $order->get_items();

	foreach( $items as $item ) {
		$trans_no = $id ;
		$type = 13 ;
		$loc_code ="DEF";
		$product_id = $item->get_product_id();
		$product = new WC_Product($item['product_id']);
		$stock_id = $product->get_sku();

		$price = $item['line_total'] / $item['qty'];
		$reference = "wofa";
		$qty = $item['qty'];
		$qty = $qty * (-1);
		$standard_cost = getfastandardcost($table_prefix, $stock_id);

		$conn = db();
		echo $sql = "insert into $table_prefix"."stock_moves (trans_no, stock_id, type, loc_code, tran_date, price, reference, qty, standard_cost)
		values ($trans_no, '$stock_id', $type, '$loc_code', '$tran_date', $price, '$reference', $qty, $standard_cost)";
		echo "<br />";
		if ($conn->query($sql) === TRUE) {
		 // $last_id = $conn->insert_id;
		  echo "New row in stock_moves created successfully. ";
		} else {
		  echo "Error creating new stock_moves " . $sql . "<br>" . $conn->error;
		}
		echo "<br />";
	}
}

function gl_trans($table_prefix, $id, $order_id){
	$order = new WC_Order( $order_id );
	$tran_date = $order->order_date;
	$dt = new DateTime($tran_date);
	$tran_date = $dt->format('Y-m-d');

	/////////////////////// Debit: COGS (5010), Credit: Ineventory(1510)  /////////////////////
	echo $sql = "SELECT * FROM $table_prefix"."debtor_trans_details where debtor_trans_no = $id and debtor_trans_type = 13";
	echo "<br />";
	$conn = db();
	$result = $conn->query($sql);
	while($row = $result->fetch_assoc()) {
		$amount = $row["standard_cost"] * $row["quantity"];
		echo $sql = "insert into $table_prefix"."gl_trans (type, type_no, tran_date, account, memo_, amount, dimension_id, dimension2_id)
		values (13, $id, '$tran_date', 5010,'wofa', $amount, 0, 0)";
		echo "<br />";
		if ($conn->query($sql) === TRUE) {
		 $last_id = $conn->insert_id;
		  echo "New row in gl_trans ( Debit: COGS (5010), Credit: Ineventory(1510) ) created successfully. ".$last_id;
		} else {
		  echo "Error creating new gl_trans ( Debit: COGS (5010), Credit: Ineventory(1510) ): " . $sql . "<br>" . $conn->error;
		}
		echo "<br />";

		$amount = $amount * -1;
		echo $sql = "insert into $table_prefix"."gl_trans (type, type_no, tran_date, account, memo_, amount, dimension_id, dimension2_id)
		values (13, $id, '$tran_date', 1510, 'wofa', $amount, 0, 0)";
		echo "<br />";
		if ($conn->query($sql) === TRUE) {
		 $last_id = $conn->insert_id;
		  echo "New row in gl_trans (13) created successfully. ".$last_id;
		} else {
		  echo "Error creating new gl_trans (13): " . $sql . "<br>" . $conn->error;
		}
		echo "<br />";
  }

	/////////////////////// Debit: AR (1200), Credit: Sales(4010) /////////////////////

	echo $sql = "SELECT * FROM $table_prefix"."debtor_trans_details where debtor_trans_no = $id and debtor_trans_type = 10";
	echo "<br />";
	$conn = db();
	$result = $conn->query($sql);
	while($row = $result->fetch_assoc()) {
		$amount = $row["unit_price"] * $row["quantity"] * -1;
		echo $sql = "insert into $table_prefix"."gl_trans (type, type_no, tran_date, account, memo_, amount, dimension_id, dimension2_id)
		values (10, $id, '$tran_date', 4010,'wofa', $amount, 0, 0)";
		echo "<br />";
		if ($conn->query($sql) === TRUE) {
		 $last_id = $conn->insert_id;
		  echo "New row in gl_trans ( Debit: AR (1200), Credit: Sales(4010) ) created successfully. ".$last_id;
		} else {
		  echo "Error creating new gl_trans ( Debit: AR (1200), Credit: Sales(4010) ): " . $sql . "<br>" . $conn->error;
		}
		echo "<br />";
	}

	/////////////////////// Debit: AR (1200), Credit: Sales(4010) /////////////////////

	echo $sql = "SELECT * FROM $table_prefix"."debtor_trans where trans_no = $id and type = 10";
	echo "<br />";
	$conn = db();
	$result = $conn->query($sql);
	while($row = $result->fetch_assoc()) {
		$shipping = $row["ov_freight"];
		$amount = $row["ov_amount"] + $row["ov_freight"] ;
		echo $sql = "insert into $table_prefix"."gl_trans (type, type_no, tran_date, account, memo_, amount, dimension_id, dimension2_id, person_type_id)
		values (10, $id, '$tran_date', 1200,'wofa', $amount, 0, 0, 2)";
		echo "<br / >";
		if ($conn->query($sql) === TRUE) {
		 $last_id = $conn->insert_id;
		  echo "New row in gl_trans ( Debit: AR (1200), Credit: Sales(4010) ) created successfully. ".$last_id;
		} else {
		  echo "Error creating new gl_trans ( Debit: AR (1200), Credit: Sales(4010) ): " . $sql . "<br>" . $conn->error;
		}
		echo "<br />";
	}

/////////////////////// Credit: Shipping and Handling(4430) /////////////////////
	if ( $shipping > 0 ){
		$shipping = $shipping * -1;
		echo $sql = "insert into $table_prefix"."gl_trans (type, type_no, tran_date, account, memo_, amount, dimension_id, dimension2_id)
		values (10, $id, '$tran_date', 4430,'wofa', $shipping, 0, 0)";
		echo "<br />";
		if ($conn->query($sql) === TRUE) {
		 $last_id = $conn->insert_id;
		  echo "New row in gl_trans ( Credit: Shipping and Handling(4430) ) created successfully. ".$last_id;
		} else {
		  echo "Error creating new gl_trans ( Credit: Shipping and Handling(4430) ): " . $sql . "<br>" . $conn->error;
		}
		echo "<br />";
	}

}

function trans_tax_details($table_prefix, $id, $order_id,$debtor_no, $reference){
	$order = new WC_Order( $order_id );
	$tran_date = $order->order_date;
	$dt = new DateTime($tran_date);
	$tran_date = $dt->format('Y-m-d');
	$net_amount = $order->get_total() - $order->get_total_shipping();

	$trans_type = 13;
	$trans_no = $id;
	$tax_type_id = 0;
	$rate = 0;
	$ex_rate = 1;
	$included_in_price = 1;
	$amount	= 0;
	$memo = $reference;
	// $reg_type =
	$conn = db();
	echo $sql = "insert into $table_prefix"."trans_tax_details (trans_type, trans_no, tran_date, tax_type_id, rate, ex_rate, included_in_price, net_amount, amount, memo)
	values ($trans_type, $trans_no, '$tran_date', $tax_type_id, $rate, $ex_rate, $included_in_price, $net_amount, $amount, 'wofa')";

	echo "<br />";
	if ($conn->query($sql) === TRUE) {
	 $last_id = $conn->insert_id;
	  echo "New row in trans_tax_details (13) created successfully. ". $last_id;
	} else {
	  echo "Error creating new trans_tax_details (13): " . $sql . "<br>" . $conn->error;
	}
	echo "<br />";

	$trans_type = 10;

	echo $sql = "insert into $table_prefix"."trans_tax_details (trans_type, trans_no, tran_date, tax_type_id, rate, ex_rate, included_in_price, net_amount, amount, memo, reg_type)
	values ($trans_type, $trans_no, '$tran_date', $tax_type_id, $rate, $ex_rate, $included_in_price, $net_amount, $amount, '$memo', 0)";

	echo "<br />";

	if ($conn->query($sql) === TRUE) {
	 $last_id = $conn->insert_id;
	  echo "New row in trans_tax_details (10) created successfully. ". $last_id;
	} else {
	  echo "Error creating new trans_tax_details (10): " . $sql . "<br>" . $conn->error;
	}
	echo "<br />";
}

function debtor_trans_details($table_prefix, $id, $order_id, $debtor_no, $reference){
	$order = new WC_Order( $order_id );
    $items = $order->get_items();

	foreach( $items as $item ) {
		$debtor_trans_no = $id ;
		$debtor_trans_type = 13 ;
		$product_id = $item->get_product_id();
		// $stock_id = getfaitemcode($product_id);

		$product = new WC_Product($item['product_id']);
		$stock_id = $product->get_sku();

		$description = $item['name'];
		$unit_price = $item['line_total'] / $item['qty'];
		$unit_tax = 0;
		$quantity = $item['qty'];
		$discount_percent = 0;
		$standard_cost = getfastandardcost($table_prefix, $stock_id);
		$qty_done = $item['qty'];;
		$src_id = getfasourceid($table_prefix, $stock_id, $id);

		$conn = db();
		echo $sql = "insert into $table_prefix"."debtor_trans_details (debtor_trans_no, debtor_trans_type, stock_id, description, unit_price, unit_tax, quantity, discount_percent, standard_cost, qty_done, src_id)
		values ($debtor_trans_no, $debtor_trans_type, '$stock_id', '$description', $unit_price, $unit_tax, $quantity, $discount_percent, $standard_cost, $qty_done, $src_id)";
		echo "<br />";
		if ($conn->query($sql) === TRUE) {
		 // $last_id = $conn->insert_id;
		  echo "New row in debtor_trans_details (Credit) created successfully. ";
		} else {
		  echo "Error creating new debtor_trans_details (Credit): " . $sql . "<br>" . $conn->error;
		}
		echo "<br />";
	}
	$debtor_trans_no = $id ;
	$debtor_trans_type = 10 ;

	echo $sql = "SELECT * FROM $table_prefix"."debtor_trans_details where debtor_trans_no = $id and debtor_trans_type = 13";
	echo "<br />";
	$conn = db();
	$result = $conn->query($sql);
	while($row = $result->fetch_assoc()) {
		$stock_id = $row["stock_id"];
		$description =  $row["description"];
		$unit_price =  $row["unit_price"];
		$unit_tax = $row["unit_tax"];
		$quantity = $row["quantity"];
		$discount_percent = $row["discount_percent"];
		$standard_cost = $row["standard_cost"];
		$qty_done = 0;
		$src_id = $row["id"];


		echo $sql = "insert into $table_prefix"."debtor_trans_details (debtor_trans_no, debtor_trans_type, stock_id, description, unit_price, unit_tax, quantity, discount_percent, standard_cost, qty_done, src_id)
		values ($debtor_trans_no, $debtor_trans_type, '$stock_id', '$description', $unit_price, $unit_tax, $quantity, $discount_percent, $standard_cost, $qty_done, $src_id)";
		echo "<br />";
		if ($conn->query($sql) === TRUE) {
		 // $last_id = $conn->insert_id;
		  echo "New row in debtor_trans_details (Credit) created successfully. ";
		} else {
		  echo "Error creating new debtor_trans_details (Credit): " . $sql . "<br>" . $conn->error;
		}
		echo "<br />";
  }
}

function getfasourceid($table_prefix, $stk_code, $id){

	$conn = db();
	$sql = "SELECT id FROM $table_prefix"."sales_order_details where order_no = $id and stk_code='$stk_code'";
	$result = $conn->query($sql);
	$row = $result->fetch_assoc();
	return $row["id"];
}

function getfastandardcost($table_prefix, $stock_id){

	$conn = db();
	$sql = "SELECT material_cost FROM $table_prefix"."stock_master where stock_id='$stock_id'";
	$result = $conn->query($sql);
	$row = $result->fetch_assoc();
	return $row["material_cost"];
}

function debtor_trans($table_prefix, $id, $order_id, $debtor_no, $reference){
	$order = new WC_Order( $order_id );
	$tran_date = $order->order_date;
	$dt = new DateTime($tran_date);
	$tran_date = $dt->format('Y-m-d');
	$due_date = date('Y-m-d', strtotime($tran_date. ' + 10 days'));
	$ov_freight = $order->get_total_shipping();
	$ov_amount = $order->get_total() - $ov_freight;

	$trans_type = 30;
	$trans_no = $id;
	$type = 10;
	$version = 0;

	$branch_code = get_branch_code($table_prefix, $debtor_no);
	$tpe = 1;
	$order_ = $id;
	$ov_gst = 0;
	$ov_freight_tax = 0;
	$ov_discount = 0;
	$alloc = 0;
	$prep_amount = 0;
	$rate = 1;
	$ship_via = 1;
	$dimension_id = 0;
	$dimension2_id = 0;
	$payment_terms = 3;
	$tax_included = 1;

	$conn = db();

	echo $sql = "insert into $table_prefix"."debtor_trans (trans_no, type, version, debtor_no, branch_code, tran_date, due_date, reference, tpe, order_, ov_amount, ov_gst, ov_freight, ov_freight_tax, ov_discount ,alloc, prep_amount, rate, ship_via ,dimension_id, dimension2_id, payment_terms, tax_included)
			values ($trans_no, $type, $version, $debtor_no, $branch_code, '$tran_date', '$due_date', '$reference', $tpe, $order_, $ov_amount, $ov_gst, $ov_freight, $ov_freight_tax, $ov_discount ,$alloc, $prep_amount, $rate, $ship_via , $dimension_id, $dimension2_id, $payment_terms, $tax_included)";
	echo "<br />";
	if ($conn->query($sql) === TRUE) {
	 // $last_id = $conn->insert_id;
	  echo "New row in debtor_trans (Credit) created successfully. ";
	} else {
	  echo "Error creating new debtor_trans (Credit): " . $sql . "<br>" . $conn->error;
	}

	echo "<br />";
		$type = 13;
		$version = 1;
		$reference = "wofa";

		echo $sql = "insert into $table_prefix"."debtor_trans (trans_no, type, version, debtor_no, branch_code, tran_date, due_date, reference, tpe, order_, ov_amount, ov_gst, ov_freight, ov_freight_tax, ov_discount ,alloc, prep_amount, rate, ship_via ,dimension_id, dimension2_id, payment_terms, tax_included)
			values ($trans_no, $type, $version, $debtor_no, $branch_code, '$tran_date', '$due_date', '$reference', $tpe, $order_, $ov_amount, $ov_gst, $ov_freight, $ov_freight_tax, $ov_discount ,$alloc, $prep_amount, $rate, $ship_via , $dimension_id, $dimension2_id, $payment_terms, $tax_included)";
	echo "<br />";
	if ($conn->query($sql) === TRUE) {
	  // $last_id = $conn->insert_id;
	  echo "New row in debtor_trans (Debit) created successfully. ";
	} else {
	  echo "Error creating new debtor_trans (Debit): " . $sql . "<br>" . $conn->error;
	}
	echo "<br />";

}

function sales_order_details($table_prefix, $id, $order_id){
	$order = new WC_Order( $order_id );
	$items = $order->get_items();
	$trans_type = 30;
	$conn = db();
	foreach( $items as $item ) {
		$product_id = $item->get_product_id();
		//$stk_code = getfaitemcode($product_id);

		$product = new WC_Product($item['product_id']);
		$stk_code = $product->get_sku();

		$description = $item['name'];
		$qty_sent = $item['qty'];
		$unit_price = $item['line_total'] / $item['qty'];
		$quantity = $qty_sent = $item['qty'];
		$invoiced = 0;
		$discount_percent = 0;
		echo $sql = "insert into $table_prefix"."sales_order_details (order_no, trans_type, stk_code, description, qty_sent, unit_price, quantity, invoiced, discount_percent)
				values ($id, $trans_type, '$stk_code','$description', $qty_sent, $unit_price, $quantity, $invoiced, $discount_percent)";
		echo "<br />";
		if ($conn->query($sql) === TRUE) {
	 	  $last_id = $conn->insert_id;
		  echo "New row in Sales Order Details created successfully. " . $last_id;
		} else {
		  echo "Error creating new reference: " . $sql . "<br>" . $conn->error;
		}
		echo "<br />";

		//a default
		// $variation_id = false;

		//check if this is a variation using is_type
//		if( $item->is_type('variable') ) {
//			$variation_id = $item->get_variation_id();
//		}
	}
}

function sales_order($table_prefix, $id, $order_id, $debtor_no){
	// values from order detail starts
	$order = new WC_Order( $order_id );
	$ord_date = $order->order_date;
	$delivery_address = "";
	if ( (empty($order->get_shipping_address_1()))  && (empty($order->get_shipping_address_2())) ){
		$delivery_address = $order->get_billing_address_1().' '.$order->get_billing_address_2();}
	else{
		$delivery_address = $order->get_shipping_address_1().' '.$order->get_shipping_address_2();
	}
	$contact_phone = $order->billing_phone;
	$contact_email = $order->get_billing_email();
	$freight_cost = $order->get_total_shipping();
	$total = $order->get_total();
	// values from order detail end

	$order_no = $id;
	$trans_type = 30;
	$version = 1;
	$type = 0;
	$branch_code = get_branch_code($table_prefix, $debtor_no);
	$reference = "wofa";
	$customer_ref = $order_id;
	$comments = "";
	$dt = new DateTime($ord_date);
	$ord_date = $dt->format('Y-m-d');
	$order_type = 1;
	$ship_via = 1;
	$deliver_to = "MNP"; // get from input
	$from_stk_loc = "DEF";
	$delivery_date = date('Y-m-d', strtotime($ord_date. ' + 10 days'));
	$payment_terms = 3;
	$prep_amount = 0;
	$alloc = 0;

	echo $sql = "INSERT INTO $table_prefix"."sales_orders (order_no, trans_type, version, type, debtor_no, branch_code, reference, customer_ref, comments, ord_date, order_type, ship_via, delivery_address, contact_phone, contact_email, deliver_to, freight_cost, from_stk_loc, delivery_date, payment_terms, total, prep_amount, alloc )
	VALUES ($order_no, $trans_type, $version, $type, $debtor_no, $branch_code, '$reference', '$customer_ref', '$comments', '$ord_date', $order_type, $ship_via, '$delivery_address', '$contact_phone', '$contact_email', '$deliver_to', $freight_cost, '$from_stk_loc', '$delivery_date', $payment_terms, $total, $prep_amount, $alloc )";
	echo "<br />";
	$conn = db();
	if ($conn->query($sql) === TRUE) {
	  echo "Data recorded in Sales Order table successfully";
	} else {
	  echo "Error Data recorded in Sales Order table." . $sql . "<br>" . $conn->error;
	}
	echo "<br />";
}

function get_debtor_no($table_prefix, $debtor_name){

	$conn = db();
	$sql = "SELECT debtor_no FROM $table_prefix"."debtors_master where debtor_ref='$debtor_name'";
	$result = $conn->query($sql);
	$row = $result->fetch_assoc();
	return $row["debtor_no"];
}

function get_branch_code($table_prefix, $debtor_no){

	$conn = db();
	echo $sql = "SELECT branch_code FROM $table_prefix"."cust_branch where debtor_no = $debtor_no";
	echo "<br >";
	$result = $conn->query($sql);
	$row = $result->fetch_assoc();
	return $row["branch_code"];
}

function refs($table_prefix, $reference){

	$type = 10;
	$conn = db();
	$sql = "SELECT MAX(ID) as id FROM $table_prefix"."refs";
	$result = $conn->query($sql);
	$row = $result->fetch_assoc();
	// echo "id: " . $row["id"]."<hr />";
	$id = $row["id"] + 1;

	echo $sql = "INSERT INTO $table_prefix"."refs (id, type, reference)
			VALUES ($id, $type, '$reference')";
	echo "<br />";


	if ($conn->query($sql) === TRUE) {
	 // $last_id = $conn->insert_id;
	  echo "New reference created successfully. " . $id ;
	} else {
	  echo "Error creating new reference: " . $sql . "<br>" . $conn->error;
	}
	echo "<br />";
	return $id;
}

?>
