function search_data_parent($data, $type, $id)
{
if ($type == 1) {
$query = "SELECT PERSONNAME_OWNER_ID FROM personname LEFT JOIN person ON personname.PERSONNAME_OWNER_ID = person.ID WHERE (PERSONNAME_NAME LIKE '%{$data}%' OR PERSONNAME_SURNAME LIKE '%{$data}%') AND person.ID != {$id}";
} else {
if ($type == 2) {
$query = "SELECT person.ID FROM person LEFT JOIN chinaname ON person.CHINANAME_ID = chinaname.ID WHERE chinaname.CHINANAME_NAME LIKE '%{$data}%' OR chinaname.CHINANAME_PINYIN LIKE '%{$data}%' OR chinaname.CHINANAME_TH LIKE '%{$data}%'";
} else {
if ($type == 3) {
if (search_province_id($data) == 0) {
$query = "SELECT * FROM province WHERE PROVINCE_ID = 0";
} else {
$provice = implode(",", search_province_id($data));
$query = "SELECT addresslist.ADDRESSLIST_OWNER_ID FROM addresslist LEFT JOIN address ON addresslist.ADDRESSLIST_ADDRESS_ID = address.ID RIGHT JOIN person ON addresslist.ADDRESSLIST_OWNER_ID = person.ID WHERE address.ADDRESS_PROVINCE_ID IN ({$provice}) AND person.ID != {$id}";
}
} else {
if ($type == 4) {
if ($data[0] == 0 && strlen($data) < 4) {
$field = "CONTACT_ARER_CODE";
} else {
$field = "CONTACT_STRING";
}
// echo $field;
$query = "SELECT CONTACT_OWNER_ID FROM contact LEFT JOIN person ON contact.CONTACT_OWNER_ID = person.ID WHERE {$field} LIKE '%{$data}%' AND CONTACT_TYPE_ID IN (1,2) AND person.ID != {$id}";
}
}
}
}
// echo $query;
return $query;
// $query .= "LIMIT $start, $end";
// echo $query;
// return mysql_query($query);
}
function search_ancestorsaddr($data, $type)
{
if ($type == 1) {
$query = "SELECT PERSONNAME_OWNER_ID FROM personname LEFT JOIN person ON personname.PERSONNAME_OWNER_ID = person.ID WHERE (PERSONNAME_NAME LIKE '%{$data}%' OR PERSONNAME_SURNAME LIKE '%{$data}%') ORDER BY PERSONNAME_NAME";
} else {
if ($type == 2) {
if (strlen($data) == 3) {
$query = "SELECT person.ID FROM person LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = person.ID " . "LEFT JOIN chinaname ON person.CHINANAME_ID = chinaname.ID " . "LEFT JOIN generation ON person.GENERATION_ID = generation.ID " . "WHERE chinaname.CHINANAME_NAME LIKE '%{$data}%' OR chinaname.CHINANAME_PINYIN LIKE '%{$data}%' OR chinaname.CHINANAME_TH LIKE '%{$data}%' OR " . "generation.GENERATION_NAME LIKE '%{$data}%' OR generation.GENERATION_PINYIN LIKE '%{$data}%' OR generation.GENERATION_TH LIKE '%{$data}%' ORDER BY PERSONNAME_NAME ";
// echo $query;
} else {
if (strlen($data) == 6) {
$gen = substr($data, 0, 3);
$name = substr($data, 3, 6);
// echo $gen;
// echo "<br/>";
// echo $name;
$query = "SELECT person.ID FROM person " . "LEFT JOIN chinaname ON person.CHINANAME_ID = chinaname.ID LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = person.ID " . "LEFT JOIN generation ON person.GENERATION_ID = generation.ID " . "WHERE (chinaname.CHINANAME_NAME LIKE '%{$name}%' OR chinaname.CHINANAME_PINYIN LIKE '%{$name}%' OR chinaname.CHINANAME_TH LIKE '%{$name}%') AND " . "(generation.GENERATION_NAME LIKE '%{$gen}%' OR generation.GENERATION_PINYIN LIKE '%{$gen}%' OR generation.GENERATION_TH LIKE '%{$gen}%') ORDER BY PERSONNAME_NAME ";
}
}
// $query = "SELECT person.ID FROM person LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = person.ID LEFT JOIN chinaname ON person.CHINANAME_ID = chinaname.ID WHERE (chinaname.CHINANAME_NAME LIKE '%$data%' OR chinaname.CHINANAME_PINYIN LIKE '%$data%' OR chinaname.CHINANAME_TH LIKE '%$data%') ";
} else {
if ($type == 3) {
if (search_province_id($data) == 0) {
$query = "SELECT * FROM province WHERE PROVINCE_ID = 0";
} else {
$provice = implode(",", search_province_id($data));
$query = "SELECT addresslist.ADDRESSLIST_OWNER_ID FROM addresslist " . "LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = addresslist.ADDRESSLIST_OWNER_ID " . "LEFT JOIN address ON addresslist.ADDRESSLIST_ADDRESS_ID = address.ID " . "WHERE address.ADDRESS_PROVINCE_ID IN ({$provice}) ORDER BY PERSONNAME_NAME ";
}
} else {
if ($type == 4) {
if (strlen($data) == 10) {
$arer_code = substr($data, 0, 3);
$string = substr($data, 3, 7);
// echo $arer_code;
// echo $string;
$query = "SELECT CONTACT_OWNER_ID FROM contact " . "LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = contact.CONTACT_OWNER_ID " . "WHERE CONTACT_ARER_CODE LIKE '%{$arer_code}%' AND CONTACT_STRING LIKE '%{$string}%' AND CONTACT_TYPE_ID IN (1,2) " . "ORDER BY PERSONNAME_NAME ";
} else {
if (strlen($data) == 9) {
$check_code = substr($data, 0, 2);
if ($check_code == "02") {
$arer_code = substr($data, 0, 2);
$string = substr($data, 3, 7);
} else {
$arer_code = substr($data, 0, 3);
$string = substr($data, 3, 6);
}
// echo $arer_code;
// echo "<br/>";
// echo $string;
$query = "SELECT CONTACT_OWNER_ID FROM contact LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = contact.CONTACT_OWNER_ID " . "WHERE CONTACT_ARER_CODE LIKE '%{$arer_code}%' AND CONTACT_STRING LIKE '%{$string}%' AND CONTACT_TYPE_ID IN (1,2) ORDER BY PERSONNAME_NAME ";
} else {
if ($data[0] == 0 && strlen($data) < 4) {
$field = "CONTACT_ARER_CODE";
} else {
$field = "CONTACT_STRING";
}
// echo $field;
$query = "SELECT CONTACT_OWNER_ID FROM contact LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = contact.CONTACT_OWNER_ID " . "WHERE {$field} LIKE '%{$data}%' AND CONTACT_TYPE_ID IN (1,2) ORDER BY PERSONNAME_NAME ";
}
}
} else {
if ($type == 5) {
$query = "SELECT ID FROM person LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = person.ID " . "WHERE PERSONALID LIKE '%{$data}%' ORDER BY PERSONNAME_NAME ";
} else {
if ($type == 6) {
$query = "SELECT CONTACT_OWNER_ID FROM contact LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = contact.CONTACT_OWNER_ID " . "WHERE CONTACT_STRING LIKE '%{$data}%' AND CONTACT_TYPE_ID = 4 ORDER BY PERSONNAME_NAME ";
} else {
if ($type == 7) {
$query = "SELECT CONTACT_OWNER_ID FROM contact LEFT JOIN personname ON personname.PERSONNAME_OWNER_ID = contact.CONTACT_OWNER_ID " . "WHERE CONTACT_STRING LIKE '%{$data}%' AND CONTACT_TYPE_ID = 5 ORDER BY PERSONNAME_NAME ";
}
}
}
}
}
}
}
// echo $query;
return $query;
// $query .= "LIMIT $start, $end";
// echo $query;
// return mysql_query($query);
}
