<?php
//exit();
header('Content-Type: text/plain');
ini_set('max_execution_time', 0);
$dbserver = '127.0.0.1';
$dbuser = 'root';
$dbpass = '';
$dbname = 'rai004';
$db = mysqli_connect($dbserver, $dbuser, $dbpass, $dbname);
mysqli_set_charset($db, "utf8");
$app_l1_name_query = "SELECT DISTINCT passengers.user_id FROM passengers";
$res = mysqli_query($db, $app_l1_name_query);
$client = new SoapClient('http://172.23.30.160:9517/WebserviceGeneral/WebServiceGenerals.svc?wsdl');
$fullcounter = 0;
$query_str = "INSERT INTO allusersdata ( user_id, f_fname, f_lname, f_nc, f_rel, org_name, tel, f_ROW) VALUES ";
while($usersql = mysqli_fetch_assoc($res))
{
	$ucode = $usersql['user_id'];
	$arr = array(
			'StroeName' =>		"USP_call_sp_mehmansara_taki",
			'XML_Arguments' =>	"<Arguments><uniq_code>$ucode</uniq_code></Arguments>",
			'XML_RESULT' =>		'',
			'USER_NAME' =>		"prs_mehman",
			'USER_PASSWORD' =>	"mehman123456",
			'USR_SESSIONID' =>	'',
			'CNTOFPAGE' =>		0);
		$user = simplexml_load_string($client->FirstRequestForStore_Fast($arr)->XML_RESULT);
		/*
		$userid = $user->row[0]->UNIQ_CODE; // bedim begirim
		$usrename = $user->row[0]->name;
		$usersurname = $user->row[0]->SurName;
		$usernationalcode = $user->row[0]->NATIONAL_CODE;
		$userdborn = $user->row[0]->D_born;
		$userjensiat = $user->row[0]->jensiat;
		$uservahedasli =$user->row[0]->vahedasli;
		$uservahedtabe =$user->row[0]->vahedtabe;
		$userorgid = $user->row[0]->code_vahedasli;
		$useremail = $user->row[0]->email;
		$userphone = $user->row[0]->tell_hamrah;
		$usersharh = $user->row[0]->sharh_post;
		*/
		foreach($user->row as $vrow)
		{
			$userid = $ucode;
			$ffname = $vrow->name;
			$flname = $vrow->SurName;
			$fnc =  $vrow->NATIONAL_CODE;
			$frel = $vrow->nesbat;
			$frow = $vrow->ROW;
			$ftel = $vrow->tell_hamrah;
			$forgname = $vrow->vahedasli;
			if($fullcounter == 0)
				$query_str .= "('$userid','$ffname','$flname','$fnc','$frel','$forgname','$ftel','$frow')\r\n";
			else
				$query_str .= ",('$userid','$ffname','$flname','$fnc','$frel','$forgname','$ftel','$frow')\r\n";
			$fullcounter++;
		}
}
echo("$query_str");
?>
-------------------------------------
CREATE TABLE fullpassengers SELECT passengers.user_id as Emp_Id ,
NULL as Emp_Fname , NULL as Emp_Lname,NULL as Emp_Org
,NULL as Emp_Tel,pas_states.state_name as Pas_State , locations.place_name as Destination,accepted.acc_date as TimeSpan,accepted.acc_manual as RandomOrNot
,Null as Pas_Fname ,Null as Pas_Lname , Null as Pas_NC , Null as Pas_Rel , passengers.pas_rownum as prn

 FROM
((passengers
LEFT JOIN pas_states ON passengers.pas_state = pas_states.state_id)
LEFT JOIN accepted on passengers.user_id = accepted.user_id)
LEFT JOIN locations on accepted.acc_loc = locations.place_id
WHERE pas_state in (0,1)

Union

SELECT passengers.user_id as Emp_Id ,
NULL , NULL , NULL , NULL,
pas_states.state_name,locations.place_name,accepted.acc_date,accepted.acc_manual,
familyothers.fo_name,familyothers.fo_lastname,familyothers.fo_melli,familyothers.fo_nesbat
,passengers.pas_rownum as prn
FROM
(((passengers
LEFT JOIN familyothers on passengers.user_id = familyothers.user_id)
LEFT JOIN pas_states on passengers.pas_state = pas_states.state_id)
LEFT JOIN accepted ON passengers.user_id = accepted.user_id)
LEFT JOIN locations on accepted.acc_loc = locations.place_id

WHERE  passengers.pas_state = 2 AND passengers.pas_rownum = familyothers.fo_id

ORDER by Emp_Id,prn
------------------------------
ALTER TABLE `fullpassengers` CHANGE `Emp_Fname` `Emp_Fname` VARCHAR(100) NULL DEFAULT NULL;
ALTER TABLE `fullpassengers` CHANGE `Emp_Lname` `Emp_Lname` VARCHAR(100) NULL DEFAULT NULL;
ALTER TABLE `fullpassengers` CHANGE `Emp_Org` `Emp_Org` VARCHAR(120) NULL DEFAULT NULL;
ALTER TABLE `fullpassengers` CHANGE `Emp_Tel` `Emp_Tel` VARCHAR(20) NULL DEFAULT NULL;
-----------------------------
UPDATE fullpassengers,allusersdata
SET
fullpassengers.Emp_Fname = allusersdata.f_fname,
fullpassengers.Emp_Lname = allusersdata.f_lname,
fullpassengers.Emp_Org = allusersdata.org_name,
fullpassengers.Emp_Tel = allusersdata.tel
WHERE
fullpassengers.Emp_Id = allusersdata.user_id
AND
allusersdata.f_ROW = '0';
---------------------------------
UPDATE fullpassengers,allusersdata
SET
fullpassengers.Pas_Fname = allusersdata.f_fname,
fullpassengers.Pas_Lname = allusersdata.f_lname,
fullpassengers.Pas_NC = allusersdata.f_nc,
fullpassengers.Pas_Rel = allusersdata.f_rel
WHERE
fullpassengers.Emp_Id = allusersdata.user_id
AND
fullpassengers.Pas_State != 'اغیار'
AND
fullpassengers.prn = allusersdata.f_ROW;
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
ALTER TABLE `fullpassengers`  ADD `indx` INT NOT NULL AUTO_INCREMENT  FIRST,  ADD   PRIMARY KEY  (`indx`);
-----------------------------------------------------------------------------------------
111:
DELETE FROM fullpassengers WHERE
Pas_Fname = '' AND Pas_Lname = '' AND Pas_NC = '' AND Pas_Rel = ''
--------------------------------------------------------------------------------------------
209:
SELECT * FROM fullpassengers as f1 WHERE f1.Pas_State = 'اغیار' AND EXISTS
(SELECT * FROM fullpassengers as f2 WHERE f2.Pas_State != 'اغیار' and f1.Emp_Id = f2.Emp_Id and f1.Pas_NC = f2.Pas_NC)
--------------------------------------------------------------------------------------------
105:
SELECT * FROM fullpassengers as f1 WHERE f1.Pas_State = 'اغیار' AND EXISTS
(SELECT * FROM fullpassengers as f2 WHERE f2.Pas_State != 'اغیار' and f1.Emp_Id = f2.Emp_Id and cast(f1.Pas_NC as unsigned) = cast(f2.Pas_NC as unsigned))
--------------------------------------------------------------------------------------------