<?php

function get_Querystring($Case)
{
     
    if($Case=="Division"){
         return "select SRCode,SR_Name_MY from qry_01_stateregion";
    }
    else if($Case=="NrcCode"){
         return "SELECT Distinct(DivCode),DivMCode FROM `TblNrccode` ORDER BY DivCode;";
    }
    else if($Case=="NrcType"){
         return "SELECT Nrc_Type FROM TblNrcType";
    }
    //
     else if($Case=="CsTyp"){
         return "SELECT CaseTypetId,Name FROM TblCaseType";
    }
     else if($Case=="Bnk"){
         return "SELECT BnakId,Name FROM TblBank";
    } else if($Case=="BseObTyp"){
         return "SELECT BaseObjTypeId,BaseObjType FROM TblBaseObjType";
    } else if($Case=="BldTyp"){
         return "SELECT BuildingTypeId,Name FROM TblBuildingType";
    } else if($Case=="GdTyp"){
         return "SELECT GroundTypeId,Name FROM TblGroundType";
    } else if($Case=="OnrTyp"){
         return "SELECT OwnerTypeId,Name FROM TblOwnerType";
    }
    //
    else if($Case=="PTyp"){
         return "SELECT PayTypeId,Name FROM TblPayType";
    }else if($Case=="Prty"){
         return "SELECT PropertyId,Name FROM TblProperty";
    }else if($Case=="PrtyTyp"){
         return "SELECT PropertyTypeId,Name FROM TblPropertyType";
    }else if($Case=="Unt"){
         return "SELECT UnitId,Name FROM TblUnit";
    }
    // else if($Case=="OnrTyp"){
    //      return "SELECT OwnerTypeId,Name FROM TblOwnerType";
    // }else if($Case=="OnrTyp"){
    //      return "SELECT OwnerTypeId,Name FROM TblOwnerType";
    // }else if($Case=="OnrTyp"){
    //      return "SELECT OwnerTypeId,Name FROM TblOwnerType";
    // }
    
    //TblPayType
}
function get_getPerOne($Case,$value)
{
     if($Case=="NrcCity"){
         return "SELECT CityCode FROM TblNrccode WHERE DivCode='" . $value. "';";
    }
    if($Case=="District"){
         return "SELECT District_Name_MY,SRCode,DistrictCode FROM qry_02_district WHERE SRCode='" . $value. "';";
    }
  else if($Case=="DivisonTownShip"){
         return "SELECT Township_Name_MY,TownshipNameLong,SRCode,DistrictCode,TownshipCode FROM qry_03_townships  WHERE SRCode='" . $value. "'";
    }
     else if($Case=="DivisionTown"){
         return "SELECT Town_Name_MY,TownNameLong,SRCode,DistrictCode,TownshipCode,TownCode FROM qry_04_towns  WHERE SRCode='" . $value. "'";
    }
     else if($Case=="DistrictTownship"){
          return "SELECT Township_Name_MY,TownshipNameLong,SRCode,DistrictCode,TownshipCode FROM qry_03_townships  WHERE DistrictCode='" . $value. "'";
    }
    else if($Case=="DistrictTown"){
          return "SELECT Town_Name_MY,TownNameLong,SRCode,DistrictCode,TownshipCode,TownCode FROM qry_04_towns  WHERE DistrictCode='" . $value. "'";
    }
    else if($Case=="TownShipTown"){
            return "SELECT Town_Name_MY,TownNameLong,SRCode,DistrictCode,TownshipCode,TownCode FROM qry_04_towns  WHERE TownshipCode='" . $value. "'";
    }
     else if($Case=="TownShipWard"){
         return "SELECT Ward_Name_MY,SRCode,DistrictCode,TownshipCode,TownCode,WardCode FROM qry_05_wards WHERE TownshipCode='" . $value. "';";
    }
    else if($Case=="TownShipVillageTract"){
         return "SELECT VT_Name_MY,SRCode,DistrictCode,TownshipCode,VTCode FROM qry_06_villagetracts WHERE TownshipCode='" . $value. "';";
    }
    else if($Case=="TownShipVillage"){
         return "SELECT Village_Name_MY,SRCode,DistrictCode,TownshipCode,VTCode,VillageCode FROM qry_07_villages WHERE TownshipCode='" . $value. "';";
    }
     else if($Case=="TownWard"){
          return "SELECT Ward_Name_MY,SRCode,DistrictCode,TownshipCode,TownCode,WardCode FROM qry_05_wards WHERE TownCode='" . $value. "';";
    }
    else if($Case=="VillageTractVillage"){
         return "SELECT Village_Name_MY,SRCode,DistrictCode,TownshipCode,VTCode,VillageCode FROM qry_07_villages WHERE VTCode='" . $value. "';";
    }
    else if($Case=="t_"){
         return "SELECT Village_Name_MY,SRCode,DistrictCode,TownshipCode,VTCode,VillageCode FROM qry_07_villages WHERE VTCode='" . $value. "';";
    }
}

function get_DlData($Case,$value)
{
     if($Case=="DlData"&&$value=='dis'){
         return "SELECT SRCode,SR_Name_MY FROM qry_02_district;";
    }
    if($Case=="District"){
         return "SELECT DistrictCode,District_Name_MY FROM qry_02_district WHERE SRCode='" . $value. "';";
    }
   else if($Case=="DivisonTownShip"){
         return "SELECT TownshipCode,Township_Name_MY,TownshipNameLong FROM qry_03_townships  WHERE SRCode='" . $value. "'";
    }
     else if($Case=="DivisionTown"){
         return "SELECT TownCode,Town_Name_MY,TownNameLong FROM qry_04_towns  WHERE SRCode='" . $value. "'";
    }
     else if($Case=="DistrictTownship"){
          return "SELECT TownshipCode,Township_Name_MY,TownshipNameLong FROM qry_03_townships  WHERE DistrictCode='" . $value. "'";
    }
    else if($Case=="DistrictTown"){
          return "SELECT TownCode,Town_Name_MY,TownNameLong FROM qry_04_towns  WHERE DistrictCode='" . $value. "'";
    }
    else if($Case=="TownShipTown"){
            return "SELECT TownCode,Town_Name_MY,TownNameLong FROM qry_04_towns  WHERE TownshipCode='" . $value. "'";
    }
     else if($Case=="TownShipWard"){
         return "SELECT WardCode,Ward_Name_MY FROM qry_05_wards WHERE TownshipCode='" . $value. "';";
    }
    else if($Case=="TownShipVillageTract"){
         return "SELECT VTCode,VT_Name_MY FROM qry_06_villagetracts WHERE TownshipCode='" . $value. "';";
    }
    else if($Case=="TownShipVillage"){
         return "SELECT VillageCode,Village_Name_MY FROM qry_07_villages WHERE TownshipCode='" . $value. "';";
    }
     else if($Case=="TownWard"){
          return "SELECT WardCode,Ward_Name_MY FROM qry_05_wards WHERE TownCode='" . $value. "';";
    }
    else if($Case=="VillageTractVillage"){
         return "SELECT VillageCode,Village_Name_MY FROM qry_07_villages WHERE VTCode='" . $value. "';";
    }
    else if($Case=="t_"){
         return "SELECT VillageCode,Village_Name_MY FROM qry_07_villages WHERE VTCode='" . $value. "';";
    }
}
function insertOrGetNrcId($NrcNo,$Name)
{
     global $conn;
    $response = array();
    //  Check if name exists
    $stmt = $conn->prepare("SELECT NrcId FROM TblNrc WHERE NrcNo =? and Name = ?");
    $stmt->bind_param("ss", $NrcNo,$Name);
    $stmt->execute();
    $stmt->bind_result($existingId);
    if ($stmt->fetch()) 
    {
        $response["status"] = 1;
        $response["message"] = "Name already exists";
        $response["id"] = $existingId;
        $stmt->close();
    }
    else
    {
         $stmt = $conn->prepare("INSERT INTO TblNrc (NrcNo,Name) VALUES (?,?)");
    $stmt->bind_param("ss", $NrcNo,$Name);

        if ($stmt->execute()) 
        {
            $response["status"] = 1;
            $response["message"] = "Name inserted";
            $response["id"] = $stmt->insert_id;
        } 
        else 
        {
            $response["status"] = 0;
            $response["message"] = "NRC Insert failed: " . $stmt->error;
        }
    }    
    return $response;
}
function GetCase_dis($Case)
{
      global $conn;
    $response = array();
    $sql="";
    if($Case=='dis')
        $sql="SELECT SRCode,SR_Name_MY FROM qry_02_district;";
     else  if($Case=='ts')
        $sql="SELECT TownshipCode,Township_Name_MY,TownshipNameLong FROM qry_03_townships;";
     else  if($Case=='t')
        $sql="SELECT TownCode,Town_Name_MY,TownNameLong FROM qry_04_towns;";    
      else  if($Case=='w')
        $sql="SELECT WardCode,Ward_Name_MY FROM qry_05_wards;"; 
       else  if($Case=='vt')
        $sql="SELECT VTCode,VT_Name_MY FROM qry_06_villagetracts;"; 
       else  if($Case=='v')
        $sql="SELECT VillageCode,Village_Name_MY FROM qry_07_villages;"; 
   
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;
        $response["msg"] = "No record found in table";
    }

    return $response;
   
}
// function GetCase()
// {
    
//     global $conn;
//     $response = array();
//     $sql="SELECT 
// 	-- Related Val
// 	prop.Name as 'PropertyTypeName',CaseType.Name as 'CaseTypeName',Property.Name as 'PropertyName',GroundUnit.Name as 'GroundUnitName',BuildingAreaUnit.Name as 'BuildingAreaUnitName',BuildingType.Name as 'BuildingTypeName' ,OnerType.Name as 'OwnerTypeName',
//     PyType.Name as 'PayTypeName',bnk.Name as 'BankName',Ste.Name as 'CaseStateName', NrcSte.Name as 'NrcState',
//     RegisterSte.Name as 'RegisterState',
//     -- Case Info
//     r.RegisterNo, r.Date, r.CaseTypeId, r.PropertyTypeId, r.PropertyId, r.Lat_Long,
//     r.GroundArea, r.GroundUnitId, r.BuildingArea, r.BuildingAreaUnitId, 
//     r.BuildingTypeId, r.BuildingType, r.ContractPrice, r.ProposedPrice, 
//     r.OwnerTypeId, r.PayTypeId, r.BnakId, r.OwnerPh, r.GuestPh, r.ProposerPh,
//     r.OtherType, r.StateId, r.NrcConfimStateId, r.RegisterStateId,
//     r.OtherArea, r.OtherUnit, r.FloorType,

//     -- Case Address
//     ca.Address_Dv AS CaseAddress_Dv,
//     ca.Address_Ds AS CaseAddress_Ds,
//     ca.Address_T AS CaseAddress_T,
//     ca.Address_TS AS CaseAddress_TS,
//     ca.Address_Q AS CaseAddress_Q,
//     ca.Address_VT AS CaseAddress_VT,
//     ca.Address_V AS CaseAddress_V,

//     -- Importer Address
//     ia.Address_Dv AS ImporterAddress_Dv,
//     ia.Address_Ds AS ImporterAddress_Ds,
//     ia.Address_T AS ImporterAddress_T,
//     ia.Address_TS AS ImporterAddress_TS,
//     ia.Address_Q AS ImporterAddress_Q,
//     ia.Address_VT AS ImporterAddress_VT,
//     ia.Address_V AS ImporterAddress_V,

//     -- Buyer Address
//     ba.Address_Dv AS BuyerAddress_Dv,
//     ba.Address_Ds AS BuyerAddress_Ds,
//     ba.Address_T AS BuyerAddress_T,
//     ba.Address_TS AS BuyerAddress_TS,
//     ba.Address_Q AS BuyerAddress_Q,
//     ba.Address_VT AS BuyerAddress_VT,
//     ba.Address_V AS BuyerAddress_V,

//     -- Seller Address
//     sa.Address_Dv AS SellerAddress_Dv,
//     sa.Address_Ds AS SellerAddress_Ds,
//     sa.Address_T AS SellerAddress_T,
//     sa.Address_TS AS SellerAddress_TS,
//     sa.Address_Q AS SellerAddress_Q,
//     sa.Address_VT AS SellerAddress_VT,
//     sa.Address_V AS SellerAddress_V,

//     -- NRC Data (Owner)
//     owner.NrcNo AS OwnerNrcNo,
//     owner.Name AS OwnerName,

//     -- NRC Data (Guest)
//     guest.NrcNo AS GuestNrcNo,
//     guest.Name AS GuestName,

//     -- NRC Data (Proposer)
//     proposer.NrcNo AS ProposerNrcNo,
//     proposer.Name AS ProposerName

// FROM TblRegister r
// inner join TblPropertyType prop ON prop.PropertyTypeId=r.PropertyTypeId
// INNER JOIN TblCaseType CaseType on CaseType.CaseTypetId=r.CaseTypeId
// INNER JOIN TblProperty Property on Property.PropertyId=r.PropertyId
// LEFT JOIN TblUnit GroundUnit on GroundUnit.UnitId=r.GroundUnitId 
// LEFT JOIN TblUnit BuildingAreaUnit on BuildingAreaUnit.UnitId=r.BuildingAreaUnitId 
// INNER JOIN TblBuildingType BuildingType on BuildingType.BuildingTypeId=r.BuildingTypeId 
// INNER JOIN TblOwnerType OnerType on OnerType.OwnerTypeId=r.OwnerTypeId
// INNER JOIN TblPayType PyType on PyType.PayTypeId=r.PayTypeId 
// INNER JOIN TblBank bnk on bnk.BnakId=r.BnakId 
// LEFT JOIN TblState Ste on Ste.StateId=r.StateId 
// LEFT JOIN TblState NrcSte on NrcSte.StateId=r.NrcConfimStateId 
// LEFT JOIN TblState RegisterSte on RegisterSte.StateId=r.RegisterStateId 


// -- Address joins  NrcConfimStateId, r.RegisterStateId
// LEFT JOIN TblAddress ca ON r.CaseAddressId = ca.AddressId
// LEFT JOIN TblAddress ia ON r.ImporterAddressId = ia.AddressId
// LEFT JOIN TblAddress ba ON r.BuyerAddressId = ba.AddressId
// LEFT JOIN TblAddress sa ON r.SellerAddressId = sa.AddressId

// -- NRC joins
// LEFT JOIN TblNrc owner ON r.OwnerNrcId = owner.NrcId
// LEFT JOIN TblNrc guest ON r.GuestNrcId = guest.NrcId
// LEFT JOIN TblNrc proposer ON r.ProposerNrcId = proposer.NrcId";
// //     $sql = "SELECT 
// //     -- Case Info
// //     r.RegisterNo, r.Date, r.CaseTypeId, r.PropertyTypeId, r.PropertyId, r.Lat_Long,
// //     r.GroundArea, r.GroundUnitId, r.BuildingArea, r.BuildingAreaUnitId, 
// //     r.BuildingTypeId, r.BuildingType, r.ContractPrice, r.ProposedPrice, 
// //     r.OwnerTypeId, r.PayTypeId, r.BnakId, r.OwnerPh, r.GuestPh, r.ProposerPh,
// //     r.OtherType, r.StateId, r.NrcConfimStateId, r.RegisterStateId,
// //     r.OtherArea, r.OtherUnit, r.FloorType,

// //     -- Case Address
// //     ca.Address_Dv AS CaseAddress_Dv,
// //     ca.Address_Ds AS CaseAddress_Ds,
// //     ca.Address_T AS CaseAddress_T,
// //     ca.Address_TS AS CaseAddress_TS,
// //     ca.Address_Q AS CaseAddress_Q,
// //     ca.Address_VT AS CaseAddress_VT,
// //     ca.Address_V AS CaseAddress_V,

// //     -- Importer Address
// //     ia.Address_Dv AS ImporterAddress_Dv,
// //     ia.Address_Ds AS ImporterAddress_Ds,
// //     ia.Address_T AS ImporterAddress_T,
// //     ia.Address_TS AS ImporterAddress_TS,
// //     ia.Address_Q AS ImporterAddress_Q,
// //     ia.Address_VT AS ImporterAddress_VT,
// //     ia.Address_V AS ImporterAddress_V,

// //     -- Buyer Address
// //     ba.Address_Dv AS BuyerAddress_Dv,
// //     ba.Address_Ds AS BuyerAddress_Ds,
// //     ba.Address_T AS BuyerAddress_T,
// //     ba.Address_TS AS BuyerAddress_TS,
// //     ba.Address_Q AS BuyerAddress_Q,
// //     ba.Address_VT AS BuyerAddress_VT,
// //     ba.Address_V AS BuyerAddress_V,

// //     -- Seller Address
// //     sa.Address_Dv AS SellerAddress_Dv,
// //     sa.Address_Ds AS SellerAddress_Ds,
// //     sa.Address_T AS SellerAddress_T,
// //     sa.Address_TS AS SellerAddress_TS,
// //     sa.Address_Q AS SellerAddress_Q,
// //     sa.Address_VT AS SellerAddress_VT,
// //     sa.Address_V AS SellerAddress_V,

// //     -- NRC Data (Owner)
// //     owner.NrcNo AS OwnerNrcNo,
// //     owner.Name AS OwnerName,

// //     -- NRC Data (Guest)
// //     guest.NrcNo AS GuestNrcNo,
// //     guest.Name AS GuestName,

// //     -- NRC Data (Proposer)
// //     proposer.NrcNo AS ProposerNrcNo,
// //     proposer.Name AS ProposerName

// // FROM TblRegister r

// // -- Address joins
// // LEFT JOIN TblAddress ca ON r.CaseAddressId = ca.AddressId
// // LEFT JOIN TblAddress ia ON r.ImporterAddressId = ia.AddressId
// // LEFT JOIN TblAddress ba ON r.BuyerAddressId = ba.AddressId
// // LEFT JOIN TblAddress sa ON r.SellerAddressId = sa.AddressId

// // -- NRC joins
// // LEFT JOIN TblNrc owner ON r.OwnerNrcId = owner.NrcId
// // LEFT JOIN TblNrc guest ON r.GuestNrcId = guest.NrcId
// // LEFT JOIN TblNrc proposer ON r.ProposerNrcId = proposer.NrcId;";
//     $result = $conn->query($sql);
//     if ($result && $result->num_rows > 0) {
//         $response["result"] = array();

//         while ($row = $result->fetch_assoc()) {
//             $caseInfo = array(
//                   "RegisterNo" => $row["RegisterNo"],
//                 "Date" => $row["Date"],
//                 "CaseTypeId" => (int)$row["CaseTypeId"],
//                 "PropertyTypeId" => (int)$row["PropertyTypeId"],
//                 "PropertyId" => (int)$row["PropertyId"],
//                 "Lat_Long" => $row["Lat_Long"],
//                 "GroundArea" => (float)$row["GroundArea"],
//                 "GroundUnitId" => (int)$row["GroundUnitId"],
//                 "BuildingArea" => $row["BuildingArea"],
//                 "BuildingAreaUnitId" => (int)$row["BuildingAreaUnitId"],
//                 "BuildingTypeId" => $row["BuildingTypeId"],
//                 "BuildingType" => $row["BuildingType"],
//                 "FloorType" => $row["FloorType"],
//                 "ContractPrice" => (float)$row["ContractPrice"],
//                 "ProposedPrice" => (float)$row["ProposedPrice"],
//                 "OwnerTypeId" => (int)$row["OwnerTypeId"],
//                 "PayTypeId" => (int)$row["PayTypeId"],
//                 "BnakId" => (int)$row["BnakId"],
//                 "OwnerPh" => $row["OwnerPh"],
//                 "GuestPh" => $row["GuestPh"],
//                 "ProposerPh" => $row["ProposerPh"],
//                 "OtherType" => $row["OtherType"],
//                 "StateId" => (int)$row["StateId"],
//                 "NrcConfimStateId" => (int)$row["NrcConfimStateId"],
//                 "RegisterStateId" => (int)$row["RegisterStateId"],
//                 "OtherArea" => (float)$row["OtherArea"],
//                 "OtherUnit" => (int)$row["OtherUnit"],

//                 "CaseAddressObj" => array(
//                     "Address_Dv" => $row["CaseAddress_Dv"],
//                     "Address_Ds" => $row["CaseAddress_Ds"],
//                     "Address_T" => $row["CaseAddress_T"],
//                     "Address_TS" => $row["CaseAddress_TS"],
//                     "Address_Q" => $row["CaseAddress_Q"],
//                     "Address_VT" => $row["CaseAddress_VT"],
//                     "Address_V" => $row["CaseAddress_V"]
//                 ),
//                 "ImporterAddressObj" => array(
//                     "Address_Dv" => $row["ImporterAddress_Dv"],
//                     "Address_Ds" => $row["ImporterAddress_Ds"],
//                     "Address_T" => $row["ImporterAddress_T"],
//                     "Address_TS" => $row["ImporterAddress_TS"],
//                     "Address_Q" => $row["ImporterAddress_Q"],
//                     "Address_VT" => $row["ImporterAddress_VT"],
//                     "Address_V" => $row["ImporterAddress_V"]
//                 ),
//                 "BuyerAddressObj" => array(
//                     "Address_Dv" => $row["BuyerAddress_Dv"],
//                     "Address_Ds" => $row["BuyerAddress_Ds"],
//                     "Address_T" => $row["BuyerAddress_T"],
//                     "Address_TS" => $row["BuyerAddress_TS"],
//                     "Address_Q" => $row["BuyerAddress_Q"],
//                     "Address_VT" => $row["BuyerAddress_VT"],
//                     "Address_V" => $row["BuyerAddress_V"]
//                 ),
//                 "SellerAddressObj" => array(
//                     "Address_Dv" => $row["SellerAddress_Dv"],
//                     "Address_Ds" => $row["SellerAddress_Ds"],
//                     "Address_T" => $row["SellerAddress_T"],
//                     "Address_TS" => $row["SellerAddress_TS"],
//                     "Address_Q" => $row["SellerAddress_Q"],
//                     "Address_VT" => $row["SellerAddress_VT"],
//                     "Address_V" => $row["SellerAddress_V"]
//                 ),
//                 "ImporterNrcData" => array(
//                     "NrcNo" => $row["OwnerNrcNo"],
//                     "Name" => $row["OwnerName"]
//                 ),
//                 "BuyerNrcData" => array(
//                     "NrcNo" => $row["GuestNrcNo"],
//                     "Name" => $row["GuestName"]
//                 ),
//                 "SellerNrcData" => array(
//                     "NrcNo" => $row["ProposerNrcNo"],
//                     "Name" => $row["ProposerName"]
//                 )
                
                
                
//             );

//             array_push($response["result"], $caseInfo);
//         }

//         $response["status"] = 0;
//         $response["msg"] = $result->num_rows . " record(s) found";
//     } else {
//         $response["status"] = 1;
//         $response["msg"] = "No record found in table";
//     }

//     return $response;
// }
function GetCase()
{
      global $conn;
    $response = array();
     $sql="SELECT 
	-- Related Val
	prop.Name as 'PropertyTypeName',CaseType.Name as 'CaseTypeName',Property.Name as 'PropertyName',GroundUnit.Name as 'GroundUnitName',BuildingAreaUnit.Name as 'BuildingAreaUnitName',BuildingType.Name as 'BuildingTypeName' ,OnerType.Name as 'OwnerTypeName',
    PyType.Name as 'PayTypeName',bnk.Name as 'BankName',Ste.Name as 'CaseStateName', NrcSte.Name as 'NrcState',
    RegisterSte.Name as 'RegisterState',
    -- Case Info
    r.RegisterNo, r.Date, r.CaseTypeId, r.PropertyTypeId, r.PropertyId, r.Lat_Long,
    r.GroundArea, r.GroundUnitId, r.BuildingArea, r.BuildingAreaUnitId, 
    r.BuildingTypeId, r.BuildingType, r.ContractPrice, r.ProposedPrice, 
    r.OwnerTypeId, r.PayTypeId, r.BnakId, r.OwnerPh, r.GuestPh, r.ProposerPh,
    r.OtherType, r.StateId, r.NrcConfimStateId, r.RegisterStateId,
    r.OtherArea, r.OtherUnit, r.FloorType,r.ImporterSt,r.SellerSt,r.BuyerSt,r.CaseSt,r.RegisterId,

    -- Case Address
    ca.Address_Dv AS CaseAddress_Dv,
    ca.Address_Ds AS CaseAddress_Ds,
    ca.Address_T AS CaseAddress_T,
    ca.Address_TS AS CaseAddress_TS,
    ca.Address_Q AS CaseAddress_Q,
    ca.Address_VT AS CaseAddress_VT,
    ca.Address_V AS CaseAddress_V,

    -- Importer Address
    ia.Address_Dv AS ImporterAddress_Dv,
    ia.Address_Ds AS ImporterAddress_Ds,
    ia.Address_T AS ImporterAddress_T,
    ia.Address_TS AS ImporterAddress_TS,
    ia.Address_Q AS ImporterAddress_Q,
    ia.Address_VT AS ImporterAddress_VT,
    ia.Address_V AS ImporterAddress_V,

    -- Buyer Address
    ba.Address_Dv AS BuyerAddress_Dv,
    ba.Address_Ds AS BuyerAddress_Ds,
    ba.Address_T AS BuyerAddress_T,
    ba.Address_TS AS BuyerAddress_TS,
    ba.Address_Q AS BuyerAddress_Q,
    ba.Address_VT AS BuyerAddress_VT,
    ba.Address_V AS BuyerAddress_V,

    -- Seller Address
    sa.Address_Dv AS SellerAddress_Dv,
    sa.Address_Ds AS SellerAddress_Ds,
    sa.Address_T AS SellerAddress_T,
    sa.Address_TS AS SellerAddress_TS,
    sa.Address_Q AS SellerAddress_Q,
    sa.Address_VT AS SellerAddress_VT,
    sa.Address_V AS SellerAddress_V,

    -- NRC Data (Owner)
    owner.NrcNo AS OwnerNrcNo,
    owner.Name AS OwnerName,

    -- NRC Data (Guest)
    guest.NrcNo AS GuestNrcNo,
    guest.Name AS GuestName,

    -- NRC Data (Proposer)
    proposer.NrcNo AS ProposerNrcNo,
    proposer.Name AS ProposerName

FROM TblRegister r
inner join TblPropertyType prop ON prop.PropertyTypeId=r.PropertyTypeId
INNER JOIN TblCaseType CaseType on CaseType.CaseTypetId=r.CaseTypeId
INNER JOIN TblProperty Property on Property.PropertyId=r.PropertyId
LEFT JOIN TblUnit GroundUnit on GroundUnit.UnitId=r.GroundUnitId 
LEFT JOIN TblUnit BuildingAreaUnit on BuildingAreaUnit.UnitId=r.BuildingAreaUnitId 
LEFT JOIN TblBuildingType BuildingType on BuildingType.BuildingTypeId=r.BuildingTypeId 
LEFT JOIN TblOwnerType OnerType on OnerType.OwnerTypeId=r.OwnerTypeId
LEFT JOIN TblPayType PyType on PyType.PayTypeId=r.PayTypeId 
LEFT JOIN TblBank bnk on bnk.BnakId=r.BnakId 
LEFT JOIN TblState Ste on Ste.StateId=r.StateId 
LEFT JOIN TblState NrcSte on NrcSte.StateId=r.NrcConfimStateId 
LEFT JOIN TblState RegisterSte on RegisterSte.StateId=r.RegisterStateId 


-- Address joins  NrcConfimStateId, r.RegisterStateId
LEFT JOIN TblAddress ca ON r.CaseAddressId = ca.AddressId
LEFT JOIN TblAddress ia ON r.ImporterAddressId = ia.AddressId
LEFT JOIN TblAddress ba ON r.BuyerAddressId = ba.AddressId
LEFT JOIN TblAddress sa ON r.SellerAddressId = sa.AddressId

-- NRC joins
LEFT JOIN TblNrc owner ON r.OwnerNrcId = owner.NrcId
LEFT JOIN TblNrc guest ON r.GuestNrcId = guest.NrcId
LEFT JOIN TblNrc proposer ON r.ProposerNrcId = proposer.NrcId";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
             $registerId = $row["RegisterId"];
             $row["Images"] = get_CaseImages($registerId);
            //   $row["Images"] = $registerId;
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;
        $response["msg"] = "No record found in table";
    }

    return $response;
   
}

function get_CaseImages($registerId)
{
    global $conn;
    $images = array();
    $sql = "SELECT * FROM TblImage WHERE CaseId= $registerId";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
             $images[] = $row;
        }
    }
    else{
        $images=null;
    }
    return $images;
}
function GetCaseBY($RegNo)
{
      global $conn;
    $response = array();
     $sql="SELECT 
	-- Related Val
	CaseType.Name as 'CaseTypeName',prop.Name as 'PropertyTypeName',Property.Name as 'PropertyName',OnerType.Name as 'OwnerTypeName', r.BuildingArea,BuildingAreaUnit.Name as 'BuildingAreaUnitName',
    r.GroundArea,GroundUnit.Name as 'GroundUnitName',BuildingType.Name as 'BuildingTypeName',r.CaseFullAddress,r.ImporterFullAddress,r.SellerFullAddress,r.BuyerFullAddress,
    r.OtherArea, TblOtherUnit.Name as 'OtherUnitName' ,r.OtherType, r.FloorType, r.ContractPrice, owner.Name AS OwnerName,owner.NrcNo AS OwnerNrcNo,
    PyType.Name as 'PayTypeName',bnk.Name as 'BankName',Ste.Name as 'CaseStateName', NrcSte.Name as 'NrcState',
    RegisterSte.Name as 'RegisterState',
    -- Case Info
    r.RegisterNo, r.Date, r.CaseTypeId, r.PropertyTypeId, r.PropertyId, r.Lat_Long, r.GroundUnitId, r.BuildingAreaUnitId, 
    r.BuildingTypeId, r.BuildingType, r.ProposedPrice, 
    r.OwnerTypeId, r.PayTypeId, r.BnakId, r.OwnerPh, r.GuestPh, r.ProposerPh, r.StateId, r.NrcConfimStateId, r.RegisterStateId,r.ImporterSt,r.SellerSt,r.BuyerSt,r.CaseSt,r.RegisterId,

    -- Case Address
    ca.Address_Dv AS CaseAddress_Dv,
    ca.Address_Ds AS CaseAddress_Ds,
    ca.Address_T AS CaseAddress_T,
    ca.Address_TS AS CaseAddress_TS,
    ca.Address_Q AS CaseAddress_Q,
    ca.Address_VT AS CaseAddress_VT,
    ca.Address_V AS CaseAddress_V,

    -- Importer Address
    ia.Address_Dv AS ImporterAddress_Dv,
    ia.Address_Ds AS ImporterAddress_Ds,
    ia.Address_T AS ImporterAddress_T,
    ia.Address_TS AS ImporterAddress_TS,
    ia.Address_Q AS ImporterAddress_Q,
    ia.Address_VT AS ImporterAddress_VT,
    ia.Address_V AS ImporterAddress_V,

    -- Buyer Address
    ba.Address_Dv AS BuyerAddress_Dv,
    ba.Address_Ds AS BuyerAddress_Ds,
    ba.Address_T AS BuyerAddress_T,
    ba.Address_TS AS BuyerAddress_TS,
    ba.Address_Q AS BuyerAddress_Q,
    ba.Address_VT AS BuyerAddress_VT,
    ba.Address_V AS BuyerAddress_V,

    -- Seller Address
    sa.Address_Dv AS SellerAddress_Dv,
    sa.Address_Ds AS SellerAddress_Ds,
    sa.Address_T AS SellerAddress_T,
    sa.Address_TS AS SellerAddress_TS,
    sa.Address_Q AS SellerAddress_Q,
    sa.Address_VT AS SellerAddress_VT,
    sa.Address_V AS SellerAddress_V,

    -- NRC Data (Owner)
    

    -- NRC Data (Guest)
    guest.NrcNo AS GuestNrcNo,
    guest.Name AS GuestName,

    -- NRC Data (Proposer)
    proposer.NrcNo AS ProposerNrcNo,
    proposer.Name AS ProposerName

FROM TblRegister r
inner join TblPropertyType prop ON prop.PropertyTypeId=r.PropertyTypeId and r.RegisterId='".$RegNo."' 
INNER JOIN TblCaseType CaseType on CaseType.CaseTypetId=r.CaseTypeId
INNER JOIN TblProperty Property on Property.PropertyId=r.PropertyId
LEFT JOIN TblUnit TblOtherUnit on TblOtherUnit.UnitId=r.OtherUnit 
LEFT JOIN TblUnit GroundUnit on GroundUnit.UnitId=r.GroundUnitId 
LEFT JOIN TblUnit BuildingAreaUnit on BuildingAreaUnit.UnitId=r.BuildingAreaUnitId 
LEFT JOIN TblBuildingType BuildingType on BuildingType.BuildingTypeId=r.BuildingTypeId 
LEFT JOIN TblOwnerType OnerType on OnerType.OwnerTypeId=r.OwnerTypeId
LEFT JOIN TblPayType PyType on PyType.PayTypeId=r.PayTypeId 
LEFT JOIN TblBank bnk on bnk.BnakId=r.BnakId 
LEFT JOIN TblState Ste on Ste.StateId=r.StateId 
LEFT JOIN TblState NrcSte on NrcSte.StateId=r.NrcConfimStateId 
LEFT JOIN TblState RegisterSte on RegisterSte.StateId=r.RegisterStateId 


-- Address joins  NrcConfimStateId, r.RegisterStateId
LEFT JOIN TblAddress ca ON r.CaseAddressId = ca.AddressId
LEFT JOIN TblAddress ia ON r.ImporterAddressId = ia.AddressId
LEFT JOIN TblAddress ba ON r.BuyerAddressId = ba.AddressId
LEFT JOIN TblAddress sa ON r.SellerAddressId = sa.AddressId

-- NRC joins
LEFT JOIN TblNrc owner ON r.OwnerNrcId = owner.NrcId
LEFT JOIN TblNrc guest ON r.GuestNrcId = guest.NrcId
LEFT JOIN TblNrc proposer ON r.ProposerNrcId = proposer.NrcId";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
              $registerId = $row["RegisterId"];
             $row["Images"] = get_CaseImages($registerId);
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;
        $response["msg"] = "No record found in table";
    }

    return $response;
   
}
function insertCase($NrcNo,$Name)
{
     global $conn;
    $response = array();
    //  Check if name exists
    $stmt = $conn->prepare("SELECT NrcId FROM TblNrc WHERE NrcNo =? and Name = ?");
    $stmt->bind_param("ss", $NrcNo,$Name);
    $stmt->execute();
    $stmt->bind_result($existingId);
    if ($stmt->fetch()) 
    {
        $response["status"] = 1;
        $response["message"] = "Name already exists";
        $response["id"] = $existingId;
        $stmt->close();
    }
    else
    {
         $stmt = $conn->prepare("INSERT INTO TblNrc (NrcNo,Name) VALUES (?,?)");
    $stmt->bind_param("ss", $NrcNo,$Name);

        if ($stmt->execute()) 
        {
            $response["status"] = 1;
            $response["message"] = "Name inserted";
            $response["id"] = $stmt->insert_id;
        } 
        else 
        {
            $response["status"] = 0;
            $response["message"] = "Insert failed: " . $stmt->error;
        }
    }    
    return $response;
}

function ReturnVal($Address_Dv)
{
      
    $response = array();

        
        {
            $response["Result = "] = $Address_Dv;
            
        }
     
    return $response;
}

function insertOrGetId($Address_Dv,$Address_Ds, $Address_T,$Address_TS,$Address_Q,$Address_VT,$Address_V)
{
     global $conn;
    $response = array();
    //  Check if name exists
    $stmt = $conn->prepare("SELECT AddressId FROM TblAddress WHERE Address_Dv =? and Address_Ds = ? and Address_T = ? and Address_TS = ? and Address_Q = ? and Address_VT = ? and Address_V = ?");
    $stmt->bind_param("sssssss", $Address_Dv,$Address_Ds,$Address_T, $Address_TS, $Address_Q, $Address_VT, $Address_V);
    $stmt->execute();
    $stmt->bind_result($existingId);
    if ($stmt->fetch()) 
    {
        $response["status"] = 1;
        $response["message"] = "Name already exists";
        $response["id"] = $existingId;
        $stmt->close();
    }
    else
    {
         $stmt = $conn->prepare("INSERT INTO TblAddress (Address_Dv,Address_Ds,Address_T,Address_TS,Address_Q,Address_VT,Address_V) VALUES (?,?,?,?,?,?,?)");
    $stmt->bind_param("sssssss", $Address_Dv,$Address_Ds,$Address_T,$Address_TS,$Address_Q,$Address_VT,$Address_V);

        if ($stmt->execute()) 
        {
            $response["status"] = 1;
            $response["message"] = "Name inserted";
            $response["id"] = $stmt->insert_id;
        } 
        else 
        {
            $response["status"] = 0;
            $response["message"] = "Insert failed: " . $stmt->error;
        }
    }    
    return $response;
}

// function insertOrGetId($name)
// {
//      global $conn;
//     // $conn->set_charset("utf8mb4");
//     $response = array();
// //  $name = 'ကခကခ';
//     // Check if name exists
//     $stmt = $conn->prepare("SELECT NameId FROM TblName WHERE Name = ?");
//     $stmt->bind_param("s", $name);
//     $stmt->execute();
//     $stmt->bind_result($existingId);

//     if ($stmt->fetch()) {
//         $response["status"] = 1;
//         $response["message"] = "Name already exists";
//         $response["id"] = $existingId;
//         $stmt->close();
//         return $response;
//     }
//     else{
//          $stmt = $conn->prepare("INSERT INTO TblName (Name) VALUES (?)");
//     $stmt->bind_param("s", $name);

//     if ($stmt->execute()) {
//         $response["status"] = 1;
//         $response["message"] = "Name inserted";
//         $response["id"] = $stmt->insert_id;
//     } else {
//         $response["status"] = 0;
//         $response["message"] = "Insert failed: " . $stmt->error;
//     }
//     }
//     // $stmt->close();
//     // $query = "SELECT NameId FROM TblName WHERE Name='" . $name. "';";
//     //     $rr= getNumber($query);
//     // if($rr=0) {
        
        
//     // }     
//     // else        
//     // {
//     //     $response["status"] = 1;
//     //     $response["message"] = "Name already exists";
//     //     $response["id"] = $existingId;
//     // }
        
//     // Insert name if not exists
   
//     $stmt->close();
//     return $response;
// }
// function insertOrGetId($name,$insertQuery)
// {
//     //  global $conn;
//     // $response = array();
//     //     if ($conn->multi_query($insertQuery) === TRUE) {
//     //         $response["status"] = 1;
//     //         $response["message"] = "Added new record";
//     //         $conn->close();
//     //       }
//     // else {
//     //     $response["status"] = 0;
//     //     $response["message"] = "Record already exists";
//     // }
//     // return $response;
//     global $conn;
//      $conn->set_charset("utf8mb4");
//     $response = array();

//     // First, check if name already exists
//     $stmt = $conn->prepare("SELECT NameId FROM TblName WHERE Name = ?");
//     $stmt->bind_param("s", $name);
//     $stmt->execute();
//     $stmt->bind_result($existingId);
//     if ($stmt->fetch()) {
//         // Name exists
//         $response["status"] = 1;
//         $response["message"] = "Name already exists";
//         $response["id"] = $existingId;
//         $stmt->close();
//         return $response;
//     }
//     $stmt->close();
//  // $response = array();
//         if ($conn->multi_query($insertQuery) === TRUE) {
//             $response["status"] = 1;
//             $response["message"] = "Added new record";
//             $conn->close();
//           }
//     else {
//         $response["status"] = 0;
//         $response["message"] = "Record already exists";
//     }
//     // Name does not exist — insert it
//     $stmt = $conn->prepare("INSERT INTO TblName (Name) VALUES (?)");
//     $stmt->bind_param("s", $name);
//     if ($stmt->execute()) {
//         $lastId = $stmt->insert_id;
//         $response["status"] = 1;
//         $response["message"] = "Name inserted";
//         $response["id"] = $lastId;
//     } else {
//         $response["status"] = 0;
//         $response["message"] = "Insert failed: " . $stmt->error;
//     }
//     $stmt->close();
//     return $response;
// }

function get_getPerTwo($Case,$value,$value1)
{
    if($Case=="Login"){
        $sql = "select Type from TblLogin where   UserName='". $value ."' and Password='". $value1 ."';";
    }
    if($Case=="Team"){
        $sql = "SELECT * FROM TblFootBallTeam where Country='". $value ."' and League='". $value1 ."';";
    }
   else if($Case=="LoginData"){
        $sql = "SELECT TblLogin.*, 
                (CASE 
                WHEN TblLogin.Type='Admin' THEN (SELECT TblAdmin.Name FROM TblAdmin INNER JOIN TblLogin ON TblLogin.JoinId=TblAdmin.Id AND TblLogin.Type='Admin')
                WHEN TblLogin.Type='SeniorMaster' THEN (SELECT TblSeniorMaster.Name FROM TblSeniorMaster INNER JOIN TblLogin ON TblLogin.JoinId=TblSeniorMaster.Id AND TblLogin.Type='SeniorMaster')
                WHEN TblLogin.Type='Master' THEN (SELECT TblMaster.Name FROM TblMaster INNER JOIN TblLogin ON TblLogin.JoinId=TblMaster.Id AND TblLogin.Type='Master')
                WHEN TblLogin.Type='Agent' THEN (SELECT TblAgent.Name FROM TblAgent INNER JOIN TblLogin ON TblLogin.JoinId=TblAgent.Id AND TblLogin.Type='Agent')
                END) AS Name,
                (CASE 
                WHEN TblLogin.Type='Admin' THEN (SELECT TblAdmin.PhoneNo FROM TblAdmin INNER JOIN TblLogin ON TblLogin.JoinId=TblAdmin.Id AND TblLogin.Type='Admin')
                WHEN TblLogin.Type='SeniorMaster' THEN (SELECT TblSeniorMaster.PhoneNo FROM TblSeniorMaster INNER JOIN TblLogin ON TblLogin.JoinId=TblSeniorMaster.Id AND TblLogin.Type='SeniorMaster')
                WHEN TblLogin.Type='Master' THEN (SELECT TblMaster.PhoneNo FROM TblMaster INNER JOIN TblLogin ON TblLogin.JoinId=TblMaster.Id AND TblLogin.Type='Master')
                WHEN TblLogin.Type='Agent' THEN (SELECT TblAgent.PhoneNo FROM TblAgent INNER JOIN TblLogin ON TblLogin.JoinId=TblAgent.Id AND TblLogin.Type='Agent')
                END) AS PhoneNum,
                (CASE 
                WHEN TblLogin.Type='Admin' THEN (SELECT TblAdmin.Address FROM TblAdmin INNER JOIN TblLogin ON TblLogin.JoinId=TblAdmin.Id AND TblLogin.Type='Admin')
                WHEN TblLogin.Type='SeniorMaster' THEN (SELECT TblSeniorMaster.Address FROM TblSeniorMaster INNER JOIN TblLogin ON TblLogin.JoinId=TblSeniorMaster.Id AND TblLogin.Type='SeniorMaster')
                WHEN TblLogin.Type='Master' THEN (SELECT TblMaster.Address FROM TblMaster INNER JOIN TblLogin ON TblLogin.JoinId=TblMaster.Id AND TblLogin.Type='Master')
                WHEN TblLogin.Type='Agent' THEN (SELECT TblAgent.Address FROM TblAgent INNER JOIN TblLogin ON TblLogin.JoinId=TblAgent.Id AND TblLogin.Type='Agent')
                END) AS Address,
                IFNULL((CASE 
                WHEN TblLogin.Type='Admin' THEN (SELECT TblUser_Data.User_Amount FROM TblUser_Data INNER JOIN TblLogin ON TblLogin.JoinId=TblUser_Data.UserId AND TblLogin.Type=TblUser_Data.UserType INNER JOIN TblAdmin ON TblLogin.JoinId=TblAdmin.Id AND TblLogin.Type='Admin')
                WHEN TblLogin.Type='SeniorMaster' THEN (SELECT TblUser_Data.User_Amount FROM TblUser_Data INNER JOIN TblLogin ON TblLogin.JoinId=TblUser_Data.UserId AND TblLogin.Type=TblUser_Data.UserType INNER JOIN TblSeniorMaster ON TblLogin.JoinId=TblSeniorMaster.Id AND TblLogin.Type='SeniorMaster')
                WHEN TblLogin.Type='Master' THEN (SELECT TblUser_Data.User_Amount FROM TblUser_Data INNER JOIN TblLogin ON TblLogin.JoinId=TblUser_Data.UserId AND TblLogin.Type=TblUser_Data.UserType INNER JOIN TblMaster ON TblLogin.JoinId=TblMaster.Id AND TblLogin.Type='Master')
                WHEN TblLogin.Type='Agent' THEN (SELECT TblUser_Data.User_Amount FROM TblUser_Data INNER JOIN TblLogin ON TblLogin.JoinId=TblUser_Data.UserId AND TblLogin.Type=TblUser_Data.UserType INNER JOIN TblAgent ON TblLogin.JoinId=TblAgent.Id AND TblLogin.Type='Agent')
                END),0) AS UserMoney
                 FROM TblLogin WHERE  TblLogin.UserName='". $value ."' and TblLogin.Password='". $value1 ."';";
    }
   return $sql;
}
function get_AccountPermission($CurrentUserId)
{
    global $conn;
    $response = array();
    $sql = "SELECT b.StateId ,b.Name,b.StatusTypeId,Ifnull((SELECT StatusId FROM TblPermission WHERE AccountId='". $CurrentUserId ."' and StatusId=b.StateId LIMIT 1),'0') as Permit FROM `TblState` b WHERE b.StatusTypeId=0;";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;"select * from ".
        $response["msg"] = "No record found in table";
    }

    return $response;
}
function get_Login($Username,$Password)
{
    global $conn;
    $response = array();
      $sql = "select * from TblLogin where   UserName='". $Username ."' and Password='". $Password ."';";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;
        $response["msg"] = "No record found in table";
    }

    return $response;
   
}
function get_AddressId($D,$T,$TS,$Q,$VT,$V)
{
    global $conn;
    $response = array();
   $sql="select AddressId from TblAddress where Address_D='" . $D. "' and Address_T='" . $T . "' and Address_TS='" . $TS. "' and Address_Q='" . $Q . "' and Address_VT='" . $VT. "' and Address_V='" . $V . "'";
      $sql = "select * from TblLogin where   UserName='". $Username ."' and Password='". $Password ."';";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 1;
        $response["msg"] = $result->num_rows . " records";
    } 
    else 
    {
        $RESPONSE["status"] = 0;
        $RESPONSE["msg"] = "NO RECORD FOUND IN TABLE";
    //       $stmt = $conn->prepare("INSERT INTO TblName (Address_D,Address_T,Address_TS,Address_Q,Address_VTAddress_V,) VALUES (??????)");
    // $stmt->bind_param("ssssss", $D,$T,$TS,$Q,$VT,$V);
    // if ($stmt->execute()) 
    // {
    //     $lastId = $stmt->insert_id;
    //     $response["status"] = 1;
    //     $response["message"] = "Name inserted";
    //     $response["id"] = $lastId;
    // } 
    // else 
    // {
    //     $response["status"] = 0;
    //     $response["message"] = "Insert failed: " . $stmt->error;
    // }
        
    }

    return $response;
   
}

function get_Login_Account($Username,$Password)
{
    global $conn;
    $response = array();
      //$sql = "SELECT a.*,b.AccountType,c.Name FROM `TblAccounts` a ,TblAccountType b,TblState c WHERE a.AccountTypeId=b.AccountTypeId and c.StateId=a.StatusId and UserName='". $Username ."' and Password='". $Password ."';";
   $sql="SELECT '' as 'MainDeptName', a.*,b.AccountType,c.Name FROM `TblAccounts` a ,TblAccountType b,TblState c where a.AccountTypeId=b.AccountTypeId and DeptId=0 and c.StateId=a.StatusId and  UserName='". $Username ."' and Password='". $Password ."' Union SELECT d.Name as 'MainDeptName', a.*,b.AccountType,c.Name FROM `TblAccounts` a ,TblAccountType b,TblState c , TblMenu d WHERE d.MenuId=a.DeptId and a.AccountTypeId=b.AccountTypeId and c.StateId=a.StatusId and UserName='". $Username ."' and Password='". $Password ."';";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;
        $response["msg"] = "No record found in table";
    }

    return $response;
   
}

function get_AccTblPermissionAll()
{
    global $conn;
    $response = array();
    $sql = "SELECT * FROM `TblPermission`;";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;
        $response["msg"] = "No record found in table";
    }

    return $response;
}
function get_StateAll()
{
    global $conn;
    $response = array();
    $sql = "SELECT * FROM `TblState`;";
   $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;
        $response["msg"] = "No record found in table";
    }

    return $response;
}
function get_NewAccId()
{
    global $conn;
    $response = array();
    $sql = "SELECT MAX(AccountId) as Mid FROM `TblAccounts`;";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;
        $response["msg"] = "No record found in table";
    }

    return $response;
}
function get_StateType()
{
    global $conn;
    $response = array();
    $sql = "SELECT * FROM TblState WHERE StatusTypeId=0;";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    }else {
        //SELECT COLUMN_NAME,COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'TblMenu' ORDER BY ORDINAL_POSITION
         $sql ="SELECT COLUMN_NAME,COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'TblState' ORDER BY ORDINAL_POSITION";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;
        $response["msg"] = "No record found in table";
    }
        // $response["status"] = 1;
        // $response["msg"] = "No record found in table";
    }

    return $response;
}
function get_StateTypeO()
{
    global $conn;
    $response = array();
    $sql = "SELECT * FROM TblState WHERE StatusTypeId=1;";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    }
     else {
        //SELECT COLUMN_NAME,COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'TblMenu' ORDER BY ORDINAL_POSITION
         $sql ="SELECT COLUMN_NAME,COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'TblState' ORDER BY ORDINAL_POSITION";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;
        $response["msg"] = "No record found in table";
    }
        // $response["status"] = 1;
        // $response["msg"] = "No record found in table";
    }

    return $response;
}
function get_AccountMenuPermission()
{
    global $conn;
    $response = array();
    $sql = "SELECT * FROM `TblAccountMenuPermission`;";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;
        $response["msg"] = "No record found in table";
    }

    return $response;
}
function get_AllTblAccounts()
{
    global $conn;
    $response = array();
    $sql = "SELECT * FROM `TblAccounts`;";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;
        $response["msg"] = "No record found in table";
    }

    return $response;
}
function get_MenuRelationData()
{
    global $conn;
    $response = array();

//          SELECT c.AccountType,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ParentMenuId),'') as Pmenu,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildMenuId),'') as Cmenu,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildSub1Id),'') as Cmenu,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildSub2Id),'') as Cmenu,a.ID,a.ParentMenuId,a.ChildMenuId,a.ChildSub1Id,a.ChildSub2Id FROM `TblMenuRelation` a , TblAccountType c WHERE a.AccountTypeId=c.AccountTypeId;
    $sql = "SELECT ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ParentMenuId),'') as Pmenu,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildMenuId),'') as Cmenu,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildSub1Id),'') as Sub1,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildSub2Id),'') as Sub2,a.ID,a.ParentMenuId,a.ChildMenuId,a.ChildSub1Id,a.ChildSub2Id FROM `TblMenuRelation` a ORDER BY a.ParentMenuId ASC,a.ChildMenuId ASC,a.ChildSub1Id ASC,a.ChildSub2Id ASC";
    // $sql = "SELECT c.AccountType,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ParentMenuId),'') as Pmenu,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildMenuId),'') as Cmenu,a.ID,a.ParentMenuId,a.ChildMenuId  FROM `TblMenuRelation` a , TblAccountType c WHERE a.AccountTypeId=c.AccountTypeId;";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;
        $response["msg"] = "No record found in table";
    }

    return $response;
}
function get_MenuRelationSqlite()
{
    global $conn;
    $response = array();

//          SELECT c.AccountType,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ParentMenuId),'') as Pmenu,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildMenuId),'') as Cmenu,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildSub1Id),'') as Cmenu,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildSub2Id),'') as Cmenu,a.ID,a.ParentMenuId,a.ChildMenuId,a.ChildSub1Id,a.ChildSub2Id FROM `TblMenuRelation` a , TblAccountType c WHERE a.AccountTypeId=c.AccountTypeId;
    $sql = "SELECT * FROM `TblMenuRelation` ORDER BY ID asc;";
    // $sql = "SELECT c.AccountType,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ParentMenuId),'') as Pmenu,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildMenuId),'') as Cmenu,a.ID,a.ParentMenuId,a.ChildMenuId  FROM `TblMenuRelation` a , TblAccountType c WHERE a.AccountTypeId=c.AccountTypeId;";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;
        $response["msg"] = "No record found in table";
    }

    return $response;
}
function get_AccountMenuSqlite()
{
    global $conn;
    $response = array();

//          SELECT c.AccountType,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ParentMenuId),'') as Pmenu,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildMenuId),'') as Cmenu,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildSub1Id),'') as Cmenu,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildSub2Id),'') as Cmenu,a.ID,a.ParentMenuId,a.ChildMenuId,a.ChildSub1Id,a.ChildSub2Id FROM `TblMenuRelation` a , TblAccountType c WHERE a.AccountTypeId=c.AccountTypeId;
    $sql = "SELECT * FROM `TblAccountMenu` ORDER BY Id asc;";
    // $sql = "SELECT c.AccountType,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ParentMenuId),'') as Pmenu,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildMenuId),'') as Cmenu,a.ID,a.ParentMenuId,a.ChildMenuId  FROM `TblMenuRelation` a , TblAccountType c WHERE a.AccountTypeId=c.AccountTypeId;";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;
        $response["msg"] = "No record found in table";
    }

    return $response;
}
function get_MenuNames()
{
    global $conn;
    $response = array();

//          SELECT c.AccountType,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ParentMenuId),'') as Pmenu,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildMenuId),'') as Cmenu,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildSub1Id),'') as Cmenu,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildSub2Id),'') as Cmenu,a.ID,a.ParentMenuId,a.ChildMenuId,a.ChildSub1Id,a.ChildSub2Id FROM `TblMenuRelation` a , TblAccountType c WHERE a.AccountTypeId=c.AccountTypeId;
    $sql = "SELECT * FROM `TblMenu` ORDER BY MenuId asc;";
    // $sql = "SELECT c.AccountType,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ParentMenuId),'') as Pmenu,ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildMenuId),'') as Cmenu,a.ID,a.ParentMenuId,a.ChildMenuId  FROM `TblMenuRelation` a , TblAccountType c WHERE a.AccountTypeId=c.AccountTypeId;";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;
        $response["msg"] = "No record found in table";
    }

    return $response;
}
//SELECT * FROM `TblAccounts` WHERE UserName='' and Password=''


function get_SubMeusChild1($ParentMenuId,$CurrentChildMenuId)
{
    global $conn;
    $response = array();
        $sql = "SELECT DISTINCT ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildSub1Id),'') as Cmenu,a.ChildSub1Id FROM `TblMenuRelation` a WHERE a.ParentMenuId='". $ParentMenuId ."' and  a.ChildMenuId='". $CurrentChildMenuId ."' ORDER BY a.ChildSub1Id ASC;";
        // $sql = "SELECT DISTINCT ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildSub2Id),'') as Cmenu FROM `TblMenuRelation` a WHERE a.ParentMenuId='". $ParentMenuId ."' and  a.ChildMenuId='". $CurrentChildMenuId ."' and a.ChildSub1Id='". $ParentMenuId ."' ORDER BY a.ChildSub2Id ASC;";
       
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;"select * from ".
        $response["msg"] = "No record found in table";
    }

    return $response;
}
function get_SubMeusChild2($ParentMenuId,$CurrentChildMenuId,$CurrentChildSub1Id)
{
    global $conn;
    $response = array();
        // $sql = "SELECT DISTINCT ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildSub1Id),'') as Cmenu FROM `TblMenuRelation` a WHERE a.ParentMenuId='". $ParentMenuId ."' and  a.ChildMenuId='". $CurrentChildMenuId ."' ORDER BY a.ChildSub1Id ASC;";
        $sql = "SELECT DISTINCT ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildSub2Id),'') as Cmenu,a.ChildSub2Id FROM `TblMenuRelation` a WHERE a.ParentMenuId='". $ParentMenuId ."' and  a.ChildMenuId='". $CurrentChildMenuId ."' and a.ChildSub1Id='". $CurrentChildSub1Id ."' ORDER BY a.ChildSub2Id ASC;";
       
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;"select * from ".
        $response["msg"] = "No record found in table";
    }

    return $response;
}
function get_SubMeus($CurrentMenuId,$State)
{
    global $conn;
    $response = array();
    if($state=1)
        $sql = "SELECT DISTINCT ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildMenuId),'') as Cmenu,a.ChildMenuId FROM `TblMenuRelation` a WHERE a.ParentMenuId='". $CurrentMenuId ."' ORDER BY a.ChildMenuId ASC;";
    else if($state=2)   
        $sql = "SELECT DISTINCT ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildSub1Id),'') as Cmenu FROM `TblMenuRelation` a WHERE a.ChildMenuId='". $CurrentMenuId ."' ORDER BY a.ChildSub1Id ASC;";
    else    
        $sql = "SELECT DISTINCT ifnull((SELECT Name FROM TblMenu WHERE MenuId=a.ChildSub2Id),'') as Cmenu FROM `TblMenuRelation` a WHERE a.ChildSub1Id='". $CurrentMenuId ."' ORDER BY a.ChildSub2Id ASC;";
    
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;"select * from ".
        $response["msg"] = "No record found in table";
    }

    return $response;
}
#region OneD
function get_LocationTypeThree($CurrentUserId)
{
    global $conn;
    $response = array();

    $sql = "select Id from TblLocationType where GameId=(SELECT max(GameId) from TblMatch) and UserId='". $CurrentUserId ."' and Type=3;";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;"select * from ".
        $response["msg"] = "No record found in table";
    }

    return $response;
}
function get_ExistData($AccountTypeId,$ParentMenuId,$ChildMenuId,$ChildMenuId1,$ChildMenuId2)
{
    global $conn;
    $response = array();

    $sql = "SELECT * FROM `TblMenuRelation` where ChildSub1Id='".$ChildMenuId1."' and ChildSub2Id='".$ChildMenuId2."' and ParentMenuId='".$ParentMenuId."' and ChildMenuId='".$ChildMenuId."'";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        $response["status"] = 1;
        $response["msg"] = "No record found in table";
    }

    return $response;
}

function get_Items($tblName)
{
    global $conn;
    // echo $tblName;
    $response = array();
    if($tblName =='0')
    {
        $tblName="TblMenu";
        $sql = "select * from TblMenu where Action=0";
    }
    else if($tblName =='1')
    {
        $tblName="TblMenu";
        $sql = "select * from TblMenu where Action=1";
    }
    else
        $sql = "select * from ". $tblName."";
    $result = $conn->query($sql);
    // check for empty result
    if ($result->num_rows > 0) {
        $response["result"] = array();
        while ($row = $result->fetch_assoc()) {
            array_push($response["result"], $row);
        }
        $response["status"] = 0;
        $response["msg"] = $result->num_rows . " records";
    } else {
        //SELECT COLUMN_NAME,COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'TblMenu' ORDER BY ORDINAL_POSITION
    //      $sql ="SELECT COLUMN_NAME,COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'". $tblName."' ORDER BY ORDINAL_POSITION";
    // $result = $conn->query($sql);
    // // check for empty result
    // if ($result->num_rows > 0) {
    //     $response["result"] = array();
    //     while ($row = $result->fetch_assoc()) {
    //         array_push($response["result"], $row);
    //     }
    //     $response["status"] = 0;
    //     $response["msg"] = $result->num_rows . " records";
    // } else {
    //     $response["status"] = 1;
    //     $response["msg"] = "No record found in table";
    // }
        $response["status"] = 1;
        $response["msg"] = "No record found in table";
    }

    return $response;
}

function get_CallAllData($tblname,$colnm,$colnm1,$Val) 
{
    global $conn;
    $returnValue = array();
    // $Val='ဗန်းမော်ခရိုင်';
    // $colnm1='District_Name_MY';
    // $Query="SELECT * from qry_02_district WHERE SRCode=( SELECT SRCode FROM `qry_02_district` WHERE District_Name_MY='.$val.')";
    //  $result = $conn->query($Query); // makes the connection and executes the sql
    // $Query="SELECT * from ".$tblname." WHERE ".$colnm." =( SELECT ".$colnm." FROM ".$tblname." WHERE ".$colnm."='.$val.')";
    //   $Query="SELECT * from ".$tblname." where ".$colnm."='".$Val."'";
        $Query="SELECT * from ".$tblname." WHERE ".$colnm."=( SELECT ".$colnm." FROM ".$tblname." WHERE ".$colnm1."='".$Val."')";  
      
//   $Query="SELECT * from qry_02_district WHERE SRCode=( SELECT SRCode FROM `qry_02_district` WHERE District_Name_MY='ဗန်းမော်ခရိုင်')";  
     $result = $conn->query($Query); // makes the connection and executes the sql
    if ($result->num_rows > 0) {
            $response["result"] = array();
            while ($row = $result->fetch_assoc()) {
                array_push($response["result"], $row);
            }
            // $response["status"] = 0;
            // $response["msg"] = $result->num_rows . " records";
             
        }
  $returnValue= $response["result"];
  return $returnValue;

};
function get_AllData($Query) 
{
    global $conn;
    $returnValue = array();

     $result = $conn->query($Query); // makes the connection and executes the sql
    if ($result->num_rows > 0) {
            $response["result"] = array();
            while ($row = $result->fetch_assoc()) {
                array_push($response["result"], $row);
            }
            // $response["status"] = 0;
            // $response["msg"] = $result->num_rows . " records";
             
        }
  $returnValue= $response["result"];
    
    return $returnValue;

}
#endregion








