$name .= $value . ",";
} else {
$vars->$name = "";
$vars->$name .= $value . ",";
}
}
foreach ( $vars as $key => $val ) {
$ar = array();
$ar = explode( ",", $val );
$vars->$key = $ar;
}
//print_r($vars);
return $vars;
}
$mydata = getRealPOST();
//echo count($mydata) . "\n";
$action = $mydata->action[ 0 ];
switch ( $action ) {
case "insert":
insert_data( $mydata, $added_date_time );
break;
case "update":
update_data( $mydata );
break;
case "delete":
delete_data( $mydata );
break;
case "load":
load_data( $mydata );
break;
case "bidders":
bidder_data( $mydata );
break;
case "countries":
countries( $mydata );
break;
case "get_user_bids":
get_users_bids( $mydata );
break;
case "get_suggested_bid_price":
get_suggested_bid_price( $mydata );
break;
case "user_bids_data":
user_bids_data( $mydata );
break;
case "update_bid":
user_bids_data( $mydata );
break;
case "set_token":
set_token( $mydata );
break;
case "sayuri":
get_chassis_manufacturing_year($mydata);
break;
case "get_lot_no":
get_lot_no_detail($mydata);
break;
case "get_bids_price":
get_bids_price($mydata);
break;
case "get_un_sold_chassis":
get_un_sold_chassis($mydata);
break;
case "delete_existing_bids":
delete_existing_bids($mydata);
break;
case "get_auction_bids_data":
get_auction_bids_data($mydata);
break;
case "get_auction_bids_data_uss":
get_auction_bids_data_uss($mydata);
break;
case "get_suggested_bid_price_uss":
get_suggested_bid_price_uss($mydata);
break;
case "save_uss_user_data":
save_uss_user_data($mydata);
break;
case "get_uss_user_data":
get_uss_user_data($mydata);
break;
case "delete_uss_user_data":
delete_uss_user_data($mydata);
break;
case "modelist":
load_user_selected_models($mydata);
break;
case "savemodelist":
save_selected_models($mydata);
break;
}
function countries( $mydata ) {
$conn = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "ahmedjpn", "testpass", "purchase_prod" );
mysqli_set_charset( $conn, "utf8mb4" );
//username and password
$username = $mydata->username[ 0 ];
$password = $mydata->password[ 0 ];
//"SELECT ur.id FROM users_roles ur INNER JOIN roles_countries rc ON rc.role_id = ur.role_id WHERE ur.user_id = '$user_id' AND rc.country_id = '$jans_country'"
$result = mysqli_query( $conn, "SELECT * FROM users_for_ext where login_id = '$username' AND password = '$password'" );
if ( mysqli_num_rows( $result ) > 0 ) {
while ( $row = mysqli_fetch_assoc( $result ) ) {
$user_id = $row[ 'user_id' ];
}
/////
$result = mysqli_query( $conn, "SELECT hr.id, hr.hr_name
FROM hr_level_detail AS hr
INNER JOIN roles_countries AS rc
ON rc.country_id = hr.id
INNER JOIN users_roles AS ur
ON ur.role_id = rc.role_id
WHERE ur.deleted_at IS NULL
AND ur.user_id = '$user_id'" );
if ( mysqli_num_rows( $result ) > 0 ) {
echo '';
while ( $row = mysqli_fetch_assoc( $result ) ) {
echo "";
}
} else {
//echo 'no country rights assigned';
echo 1;
}
} else {
echo 2;
//echo 'Sorry, username OR pwd is incorrect ' . $username;
}
}
/////////////////////////////
function set_token( $mydata ) {
$conn = mysqli_connect("jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "jansadminpanel", "jjap!@#$", "purchase_prod");
mysqli_set_charset( $conn, "utf8mb4" );
//username and password
$username = $mydata->username[ 0 ];
$password = $mydata->password[ 0 ];
//"SELECT ur.id FROM users_roles ur INNER JOIN roles_countries rc ON rc.role_id = ur.role_id WHERE ur.user_id = '$user_id' AND rc.country_id = '$jans_country'"
$result = mysqli_query( $conn, "SELECT * FROM users_for_ext where login_id = '$username' AND password = '$password'" );
if ( mysqli_num_rows( $result ) > 0 ) {
while ( $row = mysqli_fetch_assoc( $result ) ) {
$user_id = $row[ 'user_id' ];
}
$token = rand() * $user_id;
/////
$sql = mysqli_query( $conn, "UPDATE users SET remember_token='$token' WHERE id='$user_id'" );
$arr['token'] = $token;
$arr['user_id'] = $user_id;
echo json_encode($arr);
/////
}
}
/////////////////////////////
function load_data( $mydata ) {
global $active_table;
$conn = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "ahmedjpn", "testpass", "purchase_prod" );
//
mysqli_set_charset( $conn, "utf8mb4" );
$aucnumlotnum = $mydata->aucnumlotnum[ 0 ];
$username = $mydata->username[ 0 ];
$password = $mydata->password[ 0 ];
$car_detail_url = $mydata->car_detail_url[ 0 ];
//"SELECT ur.id FROM users_roles ur INNER JOIN roles_countries rc ON rc.role_id = ur.role_id WHERE ur.user_id = '$user_id' AND rc.country_id = '$jans_country'"
$jans_auction_date = $mydata->jans_auction_date[ 0 ];
/////
$jans_auction_date = DateTime::createFromFormat( 'd/m/Y', $jans_auction_date );
$jans_auction_date = $jans_auction_date->format( 'Y-m-d' );
////
/*
"SELECT ad.id, ad.lot_no, ad.bid_price, ad.company_name, ad.detail_link, ad.remarks, ufx.login_id
FROM $active_table AS ad, users_for_ext AS ufx
WHERE ufx.user_id =ad.added_by
AND ad.lot_no ='$aucnumlotnum'
AND ad.auction_date='$jans_auction_date'"
*/
//user roles not contain 2
$result = mysqli_query( $conn, "SELECT ad.id, ad.lot_no, ad.bid_price, ad.company_name, ad.detail_link, ad.remarks, hl.hr_name, ufx.login_id
FROM $active_table AS ad
RIGHT JOIN roles_countries rc
ON ad.purchase_country=rc.country_id
RIGHT JOIN users_roles ur
ON rc.role_id=ur.role_id
RIGHT JOIN users_for_ext uf
ON ur.user_id=uf.user_id
RIGHT JOIN hr_level_detail hl
ON ad.purchase_country=hl.id
RIGHT JOIN users_for_ext ufx
ON ufx.user_id = ad.added_by
WHERE uf.login_id = '$username'
AND ur.deleted_at IS NULL
AND ad.deleted_at IS NULL
AND ad.lot_no='$aucnumlotnum'
AND ad.auction_date='$jans_auction_date'
AND ad.detail_link LIKE '%$car_detail_url%'
GROUP BY ufx.login_id
ORDER BY IF(ad.bid_price>500, ad.bid_price/10000, ad.bid_price) * 1 DESC
" );
if ( mysqli_num_rows( $result ) > 0 ) {
while ( $row = mysqli_fetch_assoc( $result ) ) {
echo $row[ "lot_no" ] . ",," . $row[ "company_name" ] . ",," . $row[ "detail_link" ] . ",," . $row[ "bid_price" ] . ",," . $row[ "remarks" ] . ",," . $row[ "hr_name" ] . ",," . $row[ "id" ] . ",," . $row[ "login_id" ] . ";";
}
} else {
echo 'no data';
}
}
function bidder_data( $mydata ) {
$conn = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "ahmedjpn", "testpass", "purchase_prod" );
mysqli_set_charset( $conn, "utf8mb4" );
//username and password
$username = $mydata->username[ 0 ];
$password = $mydata->password[ 0 ];
//"SELECT ur.id FROM users_roles ur INNER JOIN roles_countries rc ON rc.role_id = ur.role_id WHERE ur.user_id = '$user_id' AND rc.country_id = '$jans_country'"
$result = mysqli_query( $conn, "SELECT * FROM users_for_ext where login_id = '$username' AND password = '$password'" );
if ( mysqli_num_rows( $result ) > 0 ) {
while ( $row = mysqli_fetch_assoc( $result ) ) {
$user_id = $row[ 'user_id' ];
}
//date
$jans_auction_date = $mydata->jans_auction_date[ 0 ];
/////
$jans_auction_date = DateTime::createFromFormat( 'd/m/Y', $jans_auction_date );
$jans_auction_date = $jans_auction_date->format( 'Y-m-d' );
////
/*
"SELECT ad.id, ad.lot_no, ad.bid_price, ad.company_name, ad.detail_link, ad.remarks, ufx.login_id
FROM $active_table AS ad, users_for_ext AS ufx
WHERE ufx.user_id =ad.added_by
AND ad.lot_no ='$aucnumlotnum'
AND ad.auction_date='$jans_auction_date'"
*/
$result = mysqli_query( $conn, "SELECT * FROM $active_table WHERE auction_date = '$jans_auction_date' AND deleted_at IS NULL " );
if ( mysqli_num_rows( $result ) > 0 ) {
while ( $row = mysqli_fetch_assoc( $result ) ) {
echo $row[ "id" ] . ",,".$row[ "company_name" ] . ",," .$row[ "detail_link" ] . ",," .$row[ "lot_no" ] . ",," .$row[ "year" ] . ",," .$row[ "car_name" ] . ",," .$row["grade"] . ",," . $row[ "type" ] . ",," .$row[ "cc" ] . ",," .$row[ "inspect" ] . ",," .$row[ "mileage" ] .",," .$row[ "color" ] . ",," .$row[ "shift" ] . ",," .$row[ "ac" ] . ",," .$row["condition" ] . ",," .$row[ "ext_grade" ] . ",," .$row[ "int_grade" ] . ",," .$row[ "start_price" ] . ",," .$row[ "bid_price" ] . ",," .$row[ "chassis_code" ] . ",," .$row["remarks" ] . ",," .$row["extension" ] . ",," . $row[ "purchase_country" ] . ",," .$row[ "auction_sheet" ] . ",," . $row[ "front_image"] . ",," .$row[ "rear_image" ] . ",," . $row[ "added_by" ] . ",," . $row[ "deleted_by" ] . ";;";
}
} else {
echo 'no data';
}
} else {
echo 'Sorry, username OR pwd is incorrect ' . $username;
}
}
function update_data( $mydata ) {
global $active_table;
$id = $mydata->id[ 0 ];
$jans_bid_price = $mydata->jans_bid_price[ 0 ];
$remarks = $mydata->remarks[ 0 ];
$username = $mydata->username[ 0 ];
$password = $mydata->password[ 0 ];
$jans_country = $mydata->jans_country[ 0 ];
$conn = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "ahmedjpn", "testpass", "purchase_prod" );
mysqli_set_charset( $conn, "utf8mb4" );
$result = mysqli_query( $conn, "SELECT * FROM users_for_ext where login_id = '$username' AND password = '$password'" );
if ( mysqli_num_rows( $result ) > 0 ) {
while ( $row = mysqli_fetch_assoc( $result ) ) {
$user_id = $row[ 'user_id' ];
}
/* SELECT ad.id, uf.login_id,ad.added_by,uf.user_id,ur.role_id,rc.role_id,rc.country_id,ad.purchase_country FROM $active_table AS ad, roles_countries AS rc,users_roles AS ur, users_for_ext AS uf WHERE ad.id='$id' AND uf.login_id='$user_id' AND ad.added_by=uf.user_id AND ur.role_id=rc.role_id AND rc.country_id=ad.purchase_country
*/
$result = mysqli_query( $conn, "SELECT ad.id, ad.added_by,ur.role_id,rc.role_id,rc.country_id,ad.purchase_country
FROM $active_table AS ad, users_roles AS ur, roles_countries AS rc
WHERE rc.country_id=ad.purchase_country
AND ur.user_id='$user_id'
AND ur.role_id=rc.role_id AND ad.id='$id'" );
if ( mysqli_num_rows( $result ) > 0 ) {
$browser = $_SERVER['HTTP_USER_AGENT'];
$ip = $_SERVER['REMOTE_ADDR'];
$u_date = date('Y-m-d H:i:s');
/////
$get_sql = "SELECT * FROM $active_table where id = '$id' AND (in_process =1 OR shifted = 1)";
$q_result = mysqli_query( $conn,$get_sql);
if ( mysqli_num_rows( $q_result ) > 0 )
{
echo 'Lot Number already transfered.You can not chnage the price';
return false;
}
/////////////
$sql = mysqli_query( $conn, "UPDATE $active_table SET bid_price='$jans_bid_price', remarks='$remarks', last_edit_by='$user_id', edit_by_browser='$browser', edit_by_ip = '$ip', updated_at = '$u_date',
updated_from = 'dbonline'
WHERE id='$id'" );
/*
$sql = mysqli_query( $conn, "UPDATE $active_table SET bid_price='$jans_bid_price', remarks='$remarks', last_edit_by='$user_id', purchase_country='$jans_country', edit_by_browser='$browser', edit_by_ip = '$ip', updated_at = '$u_date',
updated_from = 'dbonline'
WHERE id='$id'" );
*/
} else {
echo 'You are not allowed to alter this entry' . $id . " " . $user_id;
return false;
}
} else {
echo 'Sorry, username OR pwd is incorrect ' . $username;
}
}
function delete_data( $mydata ) {
global $active_table;
$id = $mydata->id[ 0 ];
$username = $mydata->username[ 0 ];
$password = $mydata->password[ 0 ];
$conn = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "ahmedjpn", "testpass", "purchase_prod" );
mysqli_set_charset( $conn, "utf8mb4" );
$result = mysqli_query( $conn, "SELECT * FROM users_for_ext where login_id = '$username' AND password = '$password'" );
if ( mysqli_num_rows( $result ) > 0 ) {
while ( $row = mysqli_fetch_assoc( $result ) ) {
$user_id = $row[ 'user_id' ];
}
$result = mysqli_query( $conn, "SELECT ad.id, ad.added_by,ur.role_id,rc.role_id,rc.country_id,ad.purchase_country FROM $active_table AS ad, roles_countries AS rc,users_roles AS ur WHERE ad.id='$id' AND ad.added_by='$user_id' AND ur.role_id=rc.role_id AND rc.country_id=ad.purchase_country" );
if ( mysqli_num_rows( $result ) > 0 ) {
$sql = mysqli_query( $conn, "DELETE FROM $active_table WHERE id='$id'" );
} else {
echo 'You are not allowed to alter this entry';
return false;
}
} else {
echo 'Sorry, username OR pwd is incorrect ' . $username;
}
}
function insert_data( $mydata, $added_date_time ) {
global $active_table;
///print_r($mydata);
$username = $mydata->username[ 0 ];
$password = $mydata->password[ 0 ];
$user_id = '';
$conn = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "jansadminpanel", "jjap!@#$", "purchase_prod" );
mysqli_set_charset( $conn, "utf8mb4" );
$result = mysqli_query( $conn, "SELECT * FROM users_for_ext where login_id = '$username' AND password = '$password'" );
if ( mysqli_num_rows( $result ) > 0 ) {
while ( $row = mysqli_fetch_assoc( $result ) ) {
$user_id = $row[ 'user_id' ];
}
if(empty($user_id))
{
echo "Please first login";
return false;
}
$jans_auction_date = $mydata->jans_auction_date[ 0 ];
if(empty($jans_auction_date))
{
echo "Your auction date is not properly set!";
return false;
}
/////
//$jans_auction_date = DateTime::createFromFormat( 'd/m/Y', $jans_auction_date );
//echo $jans_auction_date = $jans_auction_date->format( 'Y-m-d' );
$phpdate = strtotime($jans_auction_date);
$jans_auction_date = date( 'Y-m-d', $phpdate );
////
$auction_companys = $mydata->auction_company;
$jans_country = $mydata->jans_country[ 0 ];
$lot_nos1 = $mydata->lot_no1;
$lot_nos2 = $mydata->lot_no2;
$modelyears = $mydata->modelyear;
$carnames = $mydata->carname;
$grades = $mydata->grade;
$types = $mydata->ctype;
$ccs = $mydata->cc;
$rates = $mydata->crate;
$exts = $mydata->ext;
$inters = $mydata->inter;
$colors = $mydata->color;
$colornos = $mydata->color_no;
$shifts = $mydata->shift;
$acs = $mydata->ac;
$chassiscodes = $mydata->chassiscode;
$inspections = $mydata->inspection;
$mileages = $mydata->mileage;
$jans_bid_prices = $mydata->jans_bid_price;
$car_detail_urls = $mydata->car_detail_url;
$remarks = $mydata->remark;
$start_prices = $mydata->start_price;
$aimage = $mydata->aimage;
$fimage = $mydata->fimage;
$rimage = $mydata->rimage;
///extended///
$ex_documents = $mydata->ex_document;
$ex_notes = $mydata->ex_note;
$ex_vehicle_historys = $mydata->ex_vehicle_history;
$ex_drives = $mydata->ex_drive;
$ex_accessoriess = $mydata->ex_accessories;
$ex_fuels = $mydata->ex_fuel;
$ex_seatss = $mydata->ex_seats;
$ex_load_capacitys = $mydata->ex_load_capacity;
$ex_registeration_months = $mydata->ex_registeration_month;
$ex_doorss = $mydata->ex_doors;
//echo count($colors);
$check_user_country = mysqli_query( $conn, "SELECT ur.id FROM users_roles ur
INNER JOIN roles_countries rc ON rc.role_id = ur.role_id
WHERE ur.user_id = '$user_id' AND rc.country_id = '$jans_country'" );
if ( mysqli_num_rows( $check_user_country ) == 0 ) {
echo "Selected country not in your rights";
return false;
// exit();
}
////check user if is lock or ounlci
$check_user_country_lock = mysqli_query( $conn, "SELECT id FROM hr_level_detail WHERE id = '$jans_country' AND
transfer_lock = 1" );
if ( mysqli_num_rows( $check_user_country_lock ) > 0 ) {
echo "Purchase for this Market has been stopped by Japan Office.";
return false;
// exit();
}
//////
$rc = 0;
//mysqli_select_db($auction,'shippings');
for ( $j = 0; $j < count( $auction_companys ) - 1; $j++ ) {
$lot_no1 = $lot_nos1[ $j ];
$lot_no2 = $lot_nos2[ $j ];
$modelyear = $modelyears[ $j ];
$carname = $carnames[ $j ];
$grade = $grades[ $j ];
$type = $types[ $j ];
$cc = $ccs[ $j ];
$rate = $rates[ $j ];
$ext = $exts[ $j ];
$inter = $inters[ $j ];
$color = $colors[ $j ];
$colorno = $colornos[ $j ];
$shift = $shifts[ $j ];
$ac = $acs[ $j ];
$auctionsheet = $aimage[ $j ];
$frontimage = $fimage[ $j ];
$rearimage = $rimage[ $j ];
$chassiscode = $chassiscodes[ $j ];
$inspection = $inspections[ $j ];
$mileage = $mileages[ $j ];
$jans_bid_price = $jans_bid_prices[ $j ];
$car_detail_url = $car_detail_urls[ $j ];
$remark = $remarks[ $j ];
$start_price = $start_prices[ $j ];
$auction_company = $auction_companys[ $j ];
$auction_company = strip_tags($auction_company);
$lot_no = $lot_no1."//".$lot_no2;
///$lot_no=preg_replace("/[^0-9]/", "", $lot_no);
$start_price=preg_replace("/[^0-9]/", "", $start_price);
$company_id = get_auction_company_id($auction_company);
///
///extended///
$ex_document = $ex_documents[$j];
$ex_note = $ex_notes[$j];
$ex_vehicle_history = $ex_vehicle_historys[$j];
$ex_drive = $ex_drives[$j];
$ex_accessories = $ex_accessoriess[$j];
$ex_fuel = $ex_fuels[$j];
$ex_seats = $ex_seatss[$j];
$ex_load_capacity = $ex_load_capacitys[$j];
$ex_registeration_month = $ex_registeration_months[$j];
$ex_doors = $ex_doorss[$j];
///
/*
$check_record = mysqli_query( $conn, "SELECT id FROM $active_table where deleted_at is null and auction_date = '$jans_auction_date' AND company_name = '$auction_company' AND purchase_country = '$jans_country' AND lot_no = '$lot_no'" );
*/
$check_record = mysqli_query( $conn, "SELECT id FROM $active_table where deleted_at is null and auction_date = '$jans_auction_date' AND company_name_id = '$company_id' AND purchase_country = '$jans_country' AND lot_no2 = '$lot_no2'" );
//echo "Hello";
//echo mysqli_num_rows($check_record);
if ( mysqli_num_rows( $check_record ) == 0 ) {
$browser = $_SERVER['HTTP_USER_AGENT'];
$ip = $_SERVER['REMOTE_ADDR'];
$sql = "INSERT INTO $active_table(`company_name`,`lot_no`,`year`,`car_name`,`grade`,`type`,`cc`,`condition`,`ext_grade`,`int_grade`,`color`,`color_no`,`shift`,`ac`,`auction_sheet`,`front_image`, `rear_image`, `inspect`,`mileage`,`auction_date`,`added_by`,`purchase_country`,`bid_price`, `chassis_code`, `extension`, `remarks`, `start_price`, `created_at`,`detail_link`, `browser`, `ip`,`auction_no`,`lot_no2`,`company_name_id`,`bid_price_ori`) VALUES('$auction_company','$lot_no','$modelyear','$carname','$grade','$type','$cc','$rate','$ext','$inter','$color','$colorno','$shift','$ac','$auctionsheet','$frontimage','$rearimage','$inspection','$mileage','$jans_auction_date','$user_id','$jans_country','$jans_bid_price', '$chassiscode', 'dbonline', '$remark', '$start_price', '$added_date_time','$car_detail_url', '$browser', '$ip','$lot_no1','$lot_no2','$company_id','$jans_bid_price');";
if ( $conn->query( $sql ) === TRUE ) {
$last_id = $conn->insert_id;
$ex_sql = "INSERT INTO auction_data_extended(`auction_data_id`,`document`,`note`,`vehicle_history`,
`drive`,`accessories`,`fuel`,`seats`,`load_capacity`,`registeration_month`,`doors`)
VALUES('$last_id','$ex_document','$ex_note','$ex_vehicle_history','$ex_drive','$ex_accessories',
'$ex_fuel','$ex_seats','$ex_load_capacity','$ex_registeration_month','$ex_doors')";
$conn->query($ex_sql);
///////////////////////
$url = "https://www.JJPurchase.com/api/update_market_cap_rates";
$data = array (
'auction_data_id' => $last_id,
);
$post = http_build_query($data);
$x = curl_init($url);
curl_setopt($x, CURLOPT_POST, true);
curl_setopt($x, CURLOPT_RETURNTRANSFER, true);
curl_setopt($x, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($x, CURLOPT_HTTPAUTH, CURLAUTH_BASIC);
curl_setopt($x, CURLOPT_POSTFIELDS, $post);
$y = curl_exec($x);
///print_r($y);
curl_close($x);
///////////////////////
//echo "data from db is " . $lot_no . ", " . $car_detail_url . ",####";
} else {
echo "Error: " . $sql . "
" . $conn->error;
} {
/// $rtnjsonobj->message= mysqli_error($auction);
//echo $_POST['callback']. '('. json_encode($rtnjsonobj) . ');';
}
$rc++;
}
else
{
echo "Lot number is already exist in database!";
die();
}
}
if ( $rc > 0 ) {
//echo $rc . ' cars added successfully';
echo $last_id;
}
/////////////////////////////////////////
} else {
//echo "Sorry username/pwd is incorrect";
echo 'Sorry, username OR pwd is incorrect ' . $username;
}
}
function get_countires_id_from_user_id($username)
{
$conn = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "jansadminpanel", "jjap!@#$", "purchase_prod" );
mysqli_set_charset( $conn, "utf8mb4" );
$result = mysqli_query( $conn, "SELECT * FROM users_for_ext where login_id = '$username'" );
$user_id = '';
if ( mysqli_num_rows( $result ) > 0 ) {
while ( $row = mysqli_fetch_assoc( $result ) ) {
$user_id = $row[ 'user_id' ];
}
}
$result = mysqli_query( $conn, "SELECT hr.id, hr.hr_name
FROM hr_level_detail AS hr
INNER JOIN roles_countries AS rc
ON rc.country_id = hr.id
INNER JOIN users_roles AS ur
ON ur.role_id = rc.role_id
WHERE ur.deleted_at IS NULL
AND ur.user_id = '$user_id'" );
$arr = array();
if ( mysqli_num_rows( $result ) > 0 ) {
while ( $row = mysqli_fetch_assoc( $result ) ) {
$arr[] = $row[ "id" ];
}
}
return $arr;
}
////////////////tariq function//////////
function get_users_bids($mydata)
{
$login_id = $_POST["username"];
global $active_table;
$con = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "jansadminpanel", "jjap!@#$", "purchase_prod" );
mysqli_set_charset( $conn, "utf8mb4" );
$sql = "SELECT ac.remarks,ac.chassis_code,ac.id,u.login_id,ac.purchase_country,REPLACE(hr.hr_name,' ','-') as country_name,
LOWER(hr.`css_class`) as hr_name,ac.`bid_price`,ac.`company_name`,ac.`lot_no2` AS lot_no,ac.`auction_date`,ac.company_name_id,
null AS auction_details_bid
FROM ". $active_table ." ac
INNER JOIN users_for_ext u ON u.`user_id`= ac.`added_by`
INNER JOIN hr_level_detail hr ON ac.`purchase_country` = hr.`id` AND hr.`hr_level_id` = 2";
$sql .= " Where (";
$data = json_decode($_POST["data"]);
$length = count($data)-1;
$i=0;
$is_run = false;
$company_arr = array();
foreach($data as $row)
{
$is_run = true;
$mysqldate = null;
if(!empty($row->date))
{
$phpdate = strtotime($row->date );
$mysqldate = date( 'Y-m-d', $phpdate );
//$mysqldate = "2018-11-09";
}
$row->lot_no = trim($row->lot_no);
////////////////////////company id selection ////////
$company_name = $row->name;
$company_id = get_auction_company_id($company_name);
if(!isset($company_arr[$company_id]))
{
$company_arr[$company_id] = $company_name;
}
//////
if($i == $length)
{
$sql .= "( ac.`company_name_id` = '". $company_id ."'
AND ac.`auction_date` = '" . $mysqldate ."'
AND ac.`lot_no2` like '%".$row->lot_no ."')
";
}
else
{
$sql .= "( ac.`company_name_id` = '". $company_id ."'
AND ac.`auction_date` = '" . $mysqldate ."'
AND ac.`lot_no2` like '%". $row->lot_no ."') OR ";
}
$i++;
}
$arr=get_countires_id_from_user_id($login_id);
$arr = implode(',',$arr);
$sql .= " )AND ac.deleted_by = 0 AND ac.purchase_country IN($arr) order by bid_price DESC";
//$sql .= " ) AND ac.purchase_country IN($arr) order by bid_price DESC";
$response = array();
$response['status']= 404;
$response['data'] = "Data Not Found!";
//echo $sql;
if($is_run)
{
$result = mysqli_query($con,$sql);
$response['status'] = 200;
$data_result = mysqli_fetch_all($result,MYSQLI_ASSOC);
$i=0;
foreach($data_result as $row_data)
{
if(isset($row_data["chassis_code"]))
{
$row_data["chassis_code"] = trim($row_data["chassis_code"]);
}
if(isset($company_arr[$row_data["company_name_id"]]))
{
$data_result[$i]["company_name"] = $company_arr[$row_data["company_name_id"]];
}
$i++;
}
$response['data'] = $data_result;
}
echo json_encode($response);
}
///////////////////////
function get_suggested_bid_price( $mydata)
{
$conn = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "ahmedjpn", "testpass", "purchase_prod" );
mysqli_set_charset( $conn, "utf8mb4" );
$data = json_decode($_POST["data"]);
$length = count($data)-1;
$i=0;
$sql = "SELECT AVG(bid_price) as avg_price FROM auction_data Where ";
$is_one= 0;
if($data)
{
if(isset($data->Year))
{
$year = explode(".",$data->Year);
if($year[1]){
$is_one = 1;
$sql .= " YEAR = '" . $year[1] . "'";
}
}
/*
if($data->cc)
{
if($is_one = 1)
{
$sql .= " AND ";
}
else
{
$is_one = 1;
}
$data->cc = str_replace('cc','',$data->cc);
$sql .= " cc like '" . $data->cc ."%'";
}
*/
////
if(isset($data->part_1))
{
if($is_one = 1)
{
$sql .= " AND ";
}
else
{
$is_one = 1;
}
$data->cc = str_replace('cc','',$data->part_1);
$sql .= " type like '" . $data->part_1 ."%'";
}
///
if(isset($data->country_id))
{
if($is_one = 1)
{
$sql .= " AND ";
}
else
{
$is_one = 1;
}
$sql .= " purchase_country = '" . $data->country_id ."' AND bid_price <= 500";
}
/*if($data->Color)
{
if($is_one = 1)
{
$sql .= " AND ";
}
else
{
$is_one = 1;
}
$sql .= " color = '" . $data->Color ."'";
}*/
//echo $sql;
$result = mysqli_query( $conn,$sql);
$res = mysqli_fetch_all($result,MYSQLI_ASSOC);
$res = current($res);
if($res["avg_price"])
{
echo round($res["avg_price"]);
}
}
}
///
function get_auction_company_id($company_name)
{
$conn = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "ahmedjpn", "testpass", "purchase_prod" );
mysqli_set_charset( $conn, "utf8mb4" );
$sql = "SELECT * from auction_companies_other where auction_company_name = '$company_name'";
$result = mysqli_query($conn,$sql) or die(mysqli_error());
$company_id = '';
while ( $row = mysqli_fetch_assoc( $result ) ) {
$company_id = $row[ 'company_name_id_master' ];
}
return $company_id;
}
//////////////////////////////////////////////////////sayuri////
function get_chassis_manufacturing_year($mydata) {
$chassis_no = $mydata->chassis[ 0 ];
$response = array();
// var_dump("JJONLINE");
// var_dump("$chassis_no");
// die;
try {
$chassis_no = trim($chassis_no);
if(empty($chassis_no)){
throw new Exception("Chassis No Missing");
}
$chassis_parts = explode("-", $chassis_no);
if(count($chassis_parts) < 2){
throw new Exception("Invalid Chassis Format. Requires -(hyphen)");
}
// $chassis_code = 'HE21S';
// $chass_no = '658439';
list($chassis_code, $chass_no) = $chassis_parts;
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "http://sayuri.co.jp/en/api/check_chassis_no");
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, "chassis_code=$chassis_code&chassis_no=$chass_no");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$server_output = curl_exec($ch);
curl_close($ch);
if (!empty($server_output)) {
$doc = new DOMDocument();
$doc->loadHTML($server_output);
$h1 = $doc->getElementsByTagName('h1');
// kas_pr($h1);
if ($h1->length == 0) {
$div = $doc->getElementsByTagName('div');
$div = $div->item(0);
$msg = trim($div->textContent);
throw new Exception($msg);
}
// kas_pr($h1->item(0));
$h1 = $h1->item(0);
$year = trim($h1->textContent);
if(is_null($year) || $year == "A Database Error Occurred"){
throw new Exception("*Chassis Not Found");
}
$response['mfg_year'] = $year;
$response['success'] = TRUE;
}
} catch (Exception $ex) {
$response['msg'] = $ex->getMessage();
$response['mfg_year'] = -1;
$response['error'] = TRUE;
}
echo json_encode($response);
}
/////////////////////////
function get_login_users_cntry($user_id)
{
$conn = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "ahmedjpn", "testpass", "purchase_prod" );
mysqli_set_charset( $conn, "utf8mb4" );
$result = mysqli_query($conn, "SELECT hr.id, hr.hr_name
FROM hr_level_detail AS hr
INNER JOIN roles_countries AS rc
ON rc.country_id = hr.id
INNER JOIN users_roles AS ur
ON ur.role_id = rc.role_id
WHERE ur.deleted_at IS NULL
AND ur.user_id = '$user_id'" );
$country_arr = array();
if ( mysqli_num_rows( $result ) > 0 ) {
while ( $row = mysqli_fetch_assoc( $result ) ) {
//echo "";
$country_arr[] = $row[ "id" ];
}
}
return $country_arr;
}
function get_lot_no_detail($mydata)
{
$country_id = $_POST["country_id"];
$con = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "jansadminpanel", "jjap!@#$", "janjapannew1" );
mysqli_set_charset( $con, "utf8mb4" );
$user_id = $_POST['user_id'];
$country_arr = get_login_users_cntry($user_id);
//$current_date = date("Y-m-d");
$current_date = strtotime($_POST["date"]);
$current_date = date( 'Y-m-d', $current_date );
$sql = "SELECT lot_no FROM car_record
WHERE delete_state=0 AND salable_registered_day = '".$current_date."'
AND parent_id IN ( SELECT id FROM hr_level_detail WHERE delete_state=0 AND hr_level_id=3 AND parent_id
IN (" . implode(',', $country_arr) . "))";
$response = array();
$result = mysqli_query($con,$sql);
$response['status'] = 200;
$response['data'] = mysqli_fetch_all($result,MYSQLI_ASSOC);
mysqli_close($con);
echo json_encode($response);
}
/////////////////////////////////////
function get_bids_price($mydata)
{
$company_name = $_POST["compnay_name"];
if(!empty($company_name))
{
$country_id = $_POST["country_id"];
$current_date = strtotime($_POST["date"]);
$current_date = date( 'Y-m-d', $current_date );
$type = $_POST["type"];
//$current_date = date("d-m-Y");
//echo $company_name . " : " . $country_id ." : " . $type ." : " . $current_date;
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "https://www.jjpurchase.com/api/get_not_purchased_cars");
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, "app_user_id=155
&api_token=pwM0rcVzZ9gA4rdOGgWz2gyRLebg84qN7cQRD0AfrzugWKwlNiQOns9N8QAt
&device_id=216875912¤t=1
&rowCount=-1
&form_data[0][name]=auction_date
&form_data[0][value]=$current_date
&form_data[1][name]=iauc_auction_name
&form_data[1][value]=$company_name
&form_data[2][name]=country_id
&form_data[2][value]=$country_id
&form_data[3][name]=update_auction_sold_prices
&form_data[3][value]=1
&form_data[4][name]=auction_status
&form_data[4][value]=Not Sold
&form_data[5][name]=auction_sold_price_status
&form_data[5][value]=$type");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$server_output = curl_exec($ch);
$server_output = json_decode($server_output,true);
$arr = array();
if(!empty($server_output["rows"]))
{
foreach($server_output["rows"] AS $row)
{
if(isset($row["lot_no"]))
{
array_push($arr,$row["lot_no"]);
}
}
}
echo json_encode($arr);
curl_close($ch);
}
}
////////////////
function get_un_sold_chassis($mydata)
{
$chassis_arr = $_POST["chassis_code_arr"];
$date_arr = $_POST["date_arr"];
$country_id = $_POST["country_id"];
if(!empty($chassis_arr))
{
$chassis_arr = json_decode($chassis_arr);
}
if(!empty($date_arr))
{
$date_arr = json_decode($date_arr);
}
$string_arr= "";
$date_arr_string = "";
if(isset($chassis_arr[0] ))
{
$string_arr = "'" . $chassis_arr[0] . "'";
}
if(isset($date_arr[0] ))
{
$date_arr_string = "'" . $date_arr[0] . "'";
}
foreach($chassis_arr as $row)
{
$string_arr .= ",'". $row ."'";
}
foreach($date_arr as $row)
{
$date_arr_string .= ",'". $row ."'";
}
$con = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "jansadminpanel", "jjap!@#$", "purchase_prod" );
mysqli_set_charset( $con, "utf8mb4" );
$sql = "SELECT company_name,lot_no2,chassis_code,bid_price,remarks FROM auction_data WHERE auction_date NOT IN ($date_arr_string) AND purchase_country = '$country_id' AND chassis_code IN ($string_arr) GROUP BY chassis_code
ORDER BY auction_date DESC";
//echo $sql;
$response = array();
$result = mysqli_query($con,$sql);
$response['status'] = 200;
$response['data'] = mysqli_fetch_all($result,MYSQLI_ASSOC);
mysqli_close($con);
echo json_encode($response);
}
///////////////delete exsisting bid
function delete_existing_bids($mydata)
{
$username = $mydata->username[ 0 ];
$password = $mydata->password[ 0 ];
$bid_id = $mydata->id[ 0 ];
$action = 'login';
$fields = array(
'login_id' => $username,
'password' => $password,
);
$user_data = api_call($action,$fields);
$user_id = $user_data->user->id;
$api_token = $user_data->user->api_token;
$action = 'remove_pre_auction_bids';
$bid_ids = array($bid_id);
$fields = array(
'api_token' => $api_token,
'app_user_id' => $user_id,
'auction_data_ids[0]' => $bid_id,
);
$response = api_call($action,$fields);
echo json_encode($response);
//url-ify the data for the POST
}
function api_call($action,$fields=array()){
$base_url = 'https://www.JJPurchase.com/api/';
$url = $base_url.$action;
$fields['device_id'] = 216875912;
foreach($fields as $key=>$value) { $fields_string .= $key.'='.$value.'&'; }
rtrim($fields_string, '&');
//open connection
$ch = curl_init();
//set the url, number of POST vars, POST data
curl_setopt($ch, CURLOPT_USERAGENT, "IAUC Extention");
curl_setopt($ch,CURLOPT_URL, $url);
curl_setopt($ch,CURLOPT_POST, count($fields));
curl_setopt($ch,CURLOPT_POSTFIELDS, $fields_string);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
//execute post
$result = curl_exec($ch);
$result = json_decode($result);
//close connection
curl_close($ch);
return $result;
}
/////////////////function get bids data
function get_auction_bids_data($mydata)
{
$country_id = $_POST["country_id"];
$auction_date = strtotime($_POST["date"]);
$auction_date = date( 'Y-m-d', $auction_date );
$username = $_POST["username"];
$start_bids = $_POST['start_bids'];
$con = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "jansadminpanel", "jjap!@#$", "purchase_prod" );
mysqli_set_charset( $con, "utf8mb4" );
//////////////////
$result = mysqli_query( $con, "SELECT * FROM users_for_ext where login_id = '$username'" );
$user_id = '';
if ( mysqli_num_rows( $result ) > 0 ) {
while ( $row = mysqli_fetch_assoc( $result ) ) {
$user_id = $row[ 'user_id' ];
}
}
//////////////////
/*
$sql = "SELECT lot_no2 as lot_no FROM auction_data
WHERE purchase_country=$country_id
AND auction_date = '$auction_date' AND added_by = '$user_id'
";
*/
$sql = "SELECT lot_no2 as lot_no FROM auction_data
WHERE purchase_country=$country_id
AND auction_date = '$auction_date'
LIMIT 1000 OFFSET $start_bids
";
//echo $sql;
$response = array();
$result = mysqli_query($con,$sql);
$response['status'] = 200;
$response['data'] = mysqli_fetch_all($result,MYSQLI_ASSOC);
mysqli_close($con);
echo json_encode($response);
}
/////////////////////////////////////
/// USS//
function get_auction_bids_data_uss($mydata)
{
$country_id = $_POST["country_id"];
$con = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "jansadminpanel", "jjap!@#$", "janjapannew1" );
mysqli_set_charset( $con, "utf8mb4" );
//$current_date = date("Y-m-d");
$current_date = strtotime($_POST["date"]);
$current_date = date( 'Y-m-d', $current_date );
$company_name = $_POST["site_name"];
$sql = 'SELECT lot_no FROM janjapannew1.car_record
WHERE delete_state=0 AND salable_registered_day = "'.$current_date.'"
AND auction_company_id IN (SELECT auction_company_id FROM purchase_prod.auction_companies_other
WHERE auction_company_name= "'.$company_name.'" )
AND parent_id IN ( SELECT id FROM janjapannew1.hr_level_detail WHERE delete_state=0 AND hr_level_id=3 AND parent_id='.$country_id.')';
$response = array();
$result = mysqli_query($con,$sql);
$response['status'] = 200;
$response['data'] = mysqli_fetch_all($result,MYSQLI_ASSOC);
mysqli_close($con);
echo json_encode($response);
}
function get_suggested_bid_price_uss( $mydata)
{
$conn = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "ahmedjpn", "testpass", "purchase_prod" );
mysqli_set_charset( $conn, "utf8mb4" );
$data_arr = json_decode($_POST["data"]);
$length = count($data)-1;
$i=0;
$total_result = array();
foreach($data_arr as $data)
{
$sql = "SELECT lot_no2,year,AVG(bid_price) as avg_price FROM auction_data Where ";
$is_one= 0;
if($data)
{
if(isset($data->Year))
{
$year = explode(".",$data->Year);
if($year[1]){
$is_one = 1;
$sql .= " YEAR = '" . $year[1] . "'";
}
}
if(isset($data->part_1))
{
if($is_one = 1)
{
$sql .= " AND ";
}
else
{
$is_one = 1;
}
$data->cc = str_replace('cc','',$data->part_1);
$sql .= " type like '" . $data->part_1 ."%'";
}
///
if(isset($data->country_id))
{
if($is_one = 1)
{
$sql .= " AND ";
}
else
{
$is_one = 1;
}
$sql .= " purchase_country = '" . $data->country_id ."' AND bid_price <= 500";
}
//echo $sql;
$result = mysqli_query( $conn,$sql);
$res = mysqli_fetch_all($result,MYSQLI_ASSOC);
$res = current($res);
if($res["avg_price"])
{
$total_result[$data->key] = round($res["avg_price"]);
}
}
}
echo json_encode($total_result);
}
function save_uss_user_data($mydata)
{
$conn = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "jansadminpanel", "jjap!@#$", "purchase_prod" );
mysqli_set_charset( $conn, "utf8mb4" );
$username = $_POST["username"];
$grade = $_POST["grade"];
$ex_sql = "INSERT INTO uss_ext_user_detail(`user_id`,`value`)VALUES('$username','$grade')";
$conn->query($ex_sql);
echo "save";
}
function get_uss_user_data($mydata)
{
$conn = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "jansadminpanel", "jjap!@#$", "purchase_prod" );
mysqli_set_charset( $conn, "utf8mb4" );
$username = $_POST["username"];
$sql = "SELECT * from uss_ext_user_detail Where user_id='$username'";
$result = mysqli_query( $conn,$sql);
$res = mysqli_fetch_all($result,MYSQLI_ASSOC);
echo json_encode($res);
}
function delete_uss_user_data($mydata)
{
$conn = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "jansadminpanel", "jjap!@#$", "purchase_prod" );
mysqli_set_charset( $conn, "utf8mb4" );
$username = $_POST["username"];
$grade = $_POST["grade"];
$ex_sql = "DELETE FROM uss_ext_user_detail WHERE `user_id` = '$username' AND `value` = '$grade'";
$conn->query($ex_sql);
echo "delete";
}
/////////////////////////load user save models
function load_user_selected_models($data)
{
$username = $_POST['username'];
$password = $_POST['password'];
$country_id = $_POST['country_id'];
$user_id = '';
$conn = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "jansadminpanel", "jjap!@#$", "purchase_prod" );
mysqli_set_charset( $conn, "utf8mb4" );
$result = mysqli_query( $conn, "SELECT * FROM users_for_ext where login_id = '$username' AND password = '$password'" );
if ( mysqli_num_rows( $result ) > 0 ) {
while ( $row = mysqli_fetch_assoc( $result ) ) {
$user_id = $row[ 'user_id' ];
}
}
$ex_sql = "SELECT id,model_name,bool_1 AS checked FROM user_select_models_per_country where country_id = '$country_id' AND user_id = '$user_id'";
$result =$conn->query($ex_sql);
$arr_data = array();
while ( $row = mysqli_fetch_assoc( $result ) ) {
$arr_data[] = json_encode($row);
}
echo json_encode($arr_data);
}
//////////////////////////save users interested models//////////
function save_selected_models($data)
{
$username = $_POST['username'];
$password = $_POST['password'];
$country_id = $_POST['country_id'];
$modellist = $_POST['modellist'];
$modellist = json_decode($modellist);
$user_id = '';
$conn = mysqli_connect( "jansnew.cgc8qcjgyunt.eu-central-1.rds.amazonaws.com", "jansadminpanel", "jjap!@#$", "purchase_prod" );
mysqli_set_charset( $conn, "utf8mb4" );
$result = mysqli_query( $conn, "SELECT * FROM users_for_ext where login_id = '$username' AND password = '$password'" );
if ( mysqli_num_rows( $result ) > 0 ) {
while ( $row = mysqli_fetch_assoc( $result ) ) {
$user_id = $row[ 'user_id' ];
}
}
foreach($modellist as $mode_row)
{
$model_name = $mode_row->model_name;
$model_check = $mode_row->checked;
$ex_sql = "SELECT id FROM user_select_models_per_country WHERE `user_id` = '$user_id' AND
`country_id` = '$country_id' AND model_name = '$model_name'";
$is_exist_models =$conn->query($ex_sql);
if ( mysqli_num_rows($is_exist_models) == 0 )
{
$ex_sql = "INSERT INTO user_select_models_per_country(user_id,country_id,model_name,created_at,updated_at,bool_1)
VALUES('$user_id','$country_id','$model_name','$added_date_time','$added_date_time','$model_check')";
$result =$conn->query($ex_sql);
}
else
{
$ex_sql = "UPDATE user_select_models_per_country SET bool_1 = '$model_check' WHERE `user_id` = '$user_id' AND
`country_id` = '$country_id' AND model_name = '$model_name'";
$conn->query($ex_sql);
}
}
}
///////////////////////////////////////////////////////////////
?>