Skip to content

Documentation

Jump to bottom
ZhaoxiongYang edited this page Dec 1, 2018 · 68 revisions

Guideline for using backend APIs

userActionHandler.php user related actions.

To call services in this file with AJAX, you need to specify url to this file and userService's name. Here is a sample AJAX call:

        /*Sample to call isDuplicate in userActionHandler.php*/
        $.ajax({
                type: "POST",
                url: "../php/userActionHandler.php" ,
                data: {'userService': 'isDuplicate' , 'servicePara': 'username'},
                success: function (serverResponse) {
                    /* serverResponse contains info sent from server 
                         Handle server response with proper functions*/
                },
                error : function(ajaxError) {
                   /*If the communication failed, explore ajaxError*/  
                }
            });

Specifications:

  1. Calling Service

    • isDuplicate

      • function : find out if the given userName existed in the database.
      • post data : {'userService' : 'isDuplicate ', 'userName': 'userName'}
      • server response : {'status' : status_code, 'msg': TRUE/FALSE}

    • signUp

      • function: store the user's registration info into the database.
      • post data : {'userService' : 'signUp', 'userName':'user name', 'userPass':'user passwork', 'userRole':'user role', 'userPhone': 'user phone', 'userEmail': 'user email'}
      • server response : {'status' : status_code, 'msg': SUCCESS} when signUp success

    • logIn

      • function: check if the userName and userPassword users input are correct by comparing with records from database.
      • post data : {'userService' : 'logIn', 'userName':'user name', 'userPassword':'user password'}
      • server response : {'status' : status_code, 'msg': true} when logIn success or {'status' : status_code, 'msg': false} when logIn fail
  2. Server Response
    • Service Success
      • {'status' : 200, 'msg': database response}
    • Service Failed
      • {'status' : 400, 'msg': 'No Service Selected'}
      • {'status' : 404, 'msg': 'Service Not Found'}
      • {'status' : 406, 'msg': 'No enough data'}
      • {'status' : 600, 'msg': 'Databae Connection Failed'}
      • {'status' : 601, 'msg': 'Unknown error in database'}

houseActionHandler.php: house related actions.

To call services in this file with AJAX, you need to specify URL to this file and userService's name. Here is a sample AJAX call:

        /*Sample to call uploadHouseInfo in houseActionHandler.php*/
        $.ajax({
                type: "POST",
                url: "../php/houseInfoHandler.php" ,
                data: {'houseService': 'uploadHouseInfo' , 'servicePara': },
                success: function (serverResponse) {
                    /* serverResponse contains info sent from server 
                         Handle server response with proper functions*/
                },
                error : function(ajaxError) {
                   /*If the communication failed, explore ajaxError*/  
                }
            });

Specifications:

  1. Calling Service

    • uploadHouseInfo

      • function : insert the houseInfo to the database.
      • method : POST
      • pass data : {'userService' : 'uploadHouseInfo', (*)'userName', (*)'address', (*)'city',(*)'state',(*)'bath',(*)'bed' ,(*)'price', (*)'imageUrl', (*)'livingSpace', (*)'zipCode' ,'buildTime', 'lotSpace' ,'description'}
      • server response : {'status' : status_code, 'msg': SUCESS} OR {'status' : status_code, 'msg': Unknown error in database} (*) means the tag is required.

    • getHouseInfo

      • function : return a list of houses with specific filters from the database.
      • method : POST
      • pass data: {'houseService': 'getHouseInfo', (*)'filtered': BOOLEAN, (*) filterVariables: {"city": STRING, "state": STRING, "zipCode": STRING, "livingSpace": {"min":INT, "max": INT}, price: {"min": INT, "max": INT}, "bed": INT, "bath": INT}, (*)"pageNum": INT, (*)"itemPerPage": INT}
      • server response : {'status': status_code, 'msg': SUCCESS/Error, 'foundHouse': a JSON list of houseInfo}
      • Note :
        • Before add filters to SQL Querry, CHECK the variable filtered. If it is false, ignore variable filterVariables and do not add filters to SQL Querry. If it is true, read the filterVariable (JSON format) and apply filters to SQL Querry.
        • Use PageNum and itemPerPage to decided the selected range. if PageNum and itemPerPage are both 5, select dbItems[21, 22, 23, 24, 25].

Guideline for using Front End Javascript

signUp.js contains all event controls related to sign up page.

Specifications:

  1. Syntax of each filed in SignUp form
    • userName (required): shouldn't be duplicate with any userName in Database
    • passWord (required)
    • repassWord (required): passWord should be the same with repassWord.
    • email (optional): xx@xxx.com
    • phone (optional): xxx-xxx-xxxx
  2. Check Validate of input
    • Check if all field in the form is filled.
      • If not, should prompt a notification to the user
    • Check if the password marched with password
    • Check if the userName is duplicated
  3. Ajax POST() and GET()
    • Get the userName information from Server.
      • get Status from the server about whether userName isDuplicate or not
    • Post all the information in 1. to Server
      • get status back from the server about if the Post succeeded or not

addItem.js contains the function for adding a DOM object of showing a single house info

Specifications:

  1. calling service

    • generateItem(obj)

      • function: Create new HTML nodes for Item (show house information)
      • param obj: a JSON that contains house information var objSample = { "address":"street address", "city": "city name", "state":"State value", "zipCode":"zipcode number", "price":"price number" , "bed": "#bed", "bath": "#bath", "lotSpace":"#lotSpace", "buildTime":"#build year", "Url":" url for image", "description":"some description" }
      • return: a DOM object (essential to list a single house info)
      • note: You could try out the generateItem(obj) in addItemDemo.html which is located in CS561_Project/source/html

    • handleCreateItem()

      • function: Create new HTML nodes and append to itemContainer

Guideline for using database sql query

isDuplicated.sql checks a username is already existed in the table User_infoand return the result message.

To use these queries you need to specify [user name](requirement)

      -- isDuplicated
        SELECT CASE ( SELECT COUNT(`User_name`) FROM User_info WHERE `User_name` = [user name]) 
	       WHEN 0 THEN CONCAT ('{"status":'   ,  0, ','  '"msg":', '"user name is available!"}')
                      ELSE CONCAT ('{"status":'   ,  -1 , ','  '"msg":', '"user name is duplicated!"}') 
               END;

signUp.sql add a new user to table User_info and return the result of sign up.

To use these queries you need to specify [user name](requirement), [password](requirement),[user role](requirement),[email] and [phone]

     -- signUp
     INSERT INTO User_info (User_name, Password, User_role, Email, Phone) 
           VALUES ([user name], 
                    AES_ENCRYPT([password], UNHEX(SHA2(`My secret key`,512))), [user role], [email], [phone]);

     SELECT CASE ( SELECT COUNT(`User_name`) FROM `User_info` WHERE `User_name` = [user name]) 
	    WHEN 0 THEN CONCAT ('{"status":'   ,  -1 , ','  '"msg":', '"Sign up Failed!"}')
                   ELSE CONCAT ('{"status":'   ,  0 , ','  '"msg":', '"Sign up succeed!"}') 
            END;

logIn.sql check user's username and password in table User_info and return the result of log in.

To use these queries you need to specify [user name](requirement), [password](requirement)

     -- logIn
    SELECT COUNT(`User_name`) AS dbResult 
    FROM `User_info` 
    WHERE `User_name` = [user name] AND AES_DECRYPT (`Password`,UNHEX(SHA2('My secret passphrase',512))) = [password] ;

upLoad.sql upload a seller Houses info and image url in the database tables 'Houses', 'Houses_image' and return status of upload.

To use these queries you need to specify [user name](requirement), [Zipcode](requirement),[Address](requirement),[City](requirement),[State](requirement),[Price](requirement),[Beds](requirement),[Baths](requirement),[Built](requirement),[living_space](requirement),[Lot_space](requirement),[description](requirement),[Image_url](requirement)

     -- upLoad house info
    INSERT INTO `Houses2` (`User_id`, `Zipcode`, `Address`, `City`, `State`,`Price`,`Beds`, `Baths`, `Built`,`description`,`Space`,`Lot_space`) 
    VALUES(
	(SELECT `User_id` FROM `User_info` WHERE `User_name` = [user name]),[Zipcode],
	[Address],[City], [State], [Price], [Beds],[Baths],[Built],[description],[living_space],[Lot_space])
     -- upLoad image
    INSERT INTO `Houses_images` (`House_id`,`User_id`, `Url`) 
    VALUES(
	(SELECT `House_id` FROM `Houses2` ORDER BY `House_id` DESC LIMIT 1),(SELECT `User_id` FROM `Houses2` ORDER BY `House_id` DESC LIMIT 1),
	[Image_url])

deleteUploaded.sql delete a seller Houses info and image url in the database tables 'Houses', 'Houses_image' and return status of delete.

To use these queries you need to specify [house id](requirement),

     -- delete from houses table
     DELETE FROM `Houses2` WHERE `House_id` = [house id]
     -- delete from images table
     DELETE FROM `Houses_image` WHERE `House_id` = [house id]
     -- delete from favorites table
     DELETE FROM `User_favorites` WHERE `House_id` = [house id]

getUploadhouses.sql get house info of user's uploaded.

To use these queries you need to specify [user name](requirement), [x],[y](requirement)

     -- Get the count of house belong to the user
     SELECT COUNT(*) FROM `Houses2` H , `User_info` U WHERE H.`User_id` = U.`User_id` AND U.`User_name` = [user name];
     --page show for house info  
  
     SELECT H.`House_id`, H.`Address`,H.`City`,H.`State`,H.`Zipcode`,H.`Price`,H.`Beds`,H.`Baths`,H.`Built`,H.`Space`, H.`description`, Hi.`Url` FROM `Houses2` H  INNER JOIN  `Houses_images` Hi
     ON Hi.`House_id`= H.`House_id` WHERE H.`User_id` = (SELECT U.`User_id` FROM `User_info` U WHERE U.`User_name` = [user name])limit [x],[y]

getHousesinfo.sql get house info with several features.

To use these queries you need to specify [Zipcode](requirement),[City](requirement),[State](requirement),[Price_max](requirement),[Price_min](requirement),[Beds](requirement),[Baths](requirement),[Built](requirement),[living_space_max](requirement),[living_space_min](requirement),[house id](for getting house images) [x],[y](requirement)

     -- without filter Variables
     -- Get the count of house belong to the features
     SELECT COUNT(*) FROM `Houses2` H  WHERE 1;

     --page show for house info  
     SELECT H.`House_id`, H.`Address`,H.`City`,H.`State`,H.`Zipcode`,H.`Price`,H.`Beds`,H.`Baths`,H.`Built`,H.`Space`, H.`description`, Hi.`Url` FROM `Houses2` H  INNER JOIN  `Houses_images` Hi
     ON Hi.`House_id`= H.`House_id` GROUP BY H.`House_id` HAVING 1 limit [x],[y]
   
    -- with filter Variables 
    -- Get the count of house belong to the features
    SELECT COUNT(*) FROM `Houses2` H  WHERE H.`Address`=[Address] AND H.`City`=[City] AND H.`State`=[State] AND H.`Zipcode`=[Zipcode] AND H.`Price`>=[Price_min] AND H.`Price`<=[Price_max] AND H.`Beds`=[Beds] AND H.`Baths` = [Baths] AND H.`Built`>=[Built] AND H.`Space`>=[living_space_min] AND H.`Space`<=[living_space_max];

    --page show for house info  
    SELECT H.`House_id`, H.`Address`,H.`City`,H.`State`,H.`Zipcode`,H.`Price`,H.`Beds`,H.`Baths`,H.`Built`,H.`Space`, H.`description`, Hi.`Url` FROM `Houses2` H  INNER JOIN  `Houses_images` Hi
     ON Hi.`House_id`= H.`House_id` GROUP BY H.`House_id` HAVING H.`City`=[City] AND H.`State`=[State] AND H.`Zipcode`=[Zipcode] AND H.`Price`>=[Price_min] AND H.`Price`<=[Price_max] AND H.`Beds`=[Beds] AND H.`Baths` = [Baths] AND H.`Built`>=[Built] AND H.`Space`>=[living_space_min] AND H.`Space`<=[living_space_max] limit [X], [Y]

addFavoritehouse.sql add a house to user's favorite list.

To use these queries you need to specify [user name](requirement)

     INSERT INTO  `User_favorites` (`User_id` ,  `House_id` ,  `Create_Time` ) VALUES ((SELECT  `User_id` FROM `User_info` WHERE  `User_name` =  [user name]), [house id], (SELECT FROM_UNIXTIME( UNIX_TIMESTAMP( NOW( ) ) ,  '%Y-%m-%d %H:%i:%S' )))

deleteSavedhouse.sql delete a house from user's favorite list.

To use these queries you need to specify [user name](requirement), [house id](for getting house images

     -- delete from favorites table
     DELETE FROM `User_favorites` WHERE `House_id` = [house id] AND `User_id` = (SELECT `User_id` FROM `User_info` WHERE `User_name` = [user name])

getSavedhouses.sql get house info from user's favorite list.

To use these queries you need to specify [user name](requirement),[house id](for getting house images)

     -- Get house info
    SELECT H.`House_id`, H.`Address`,H.`City`,H.`State`,H.`Zipcode`,H.`Price`,H.`Beds`,H.`Baths`,H.`Built`,H.`Space`, H.`description`, Hi.`Url` FROM `Houses2` H  INNER JOIN  `Houses_images` Hi
     ON Hi.`House_id`= H.`House_id` WHERE H.`House_id` IN( SELECT F.`House_id`  FROM `User_favorites` F , `User_info` U WHERE F.`User_id` = U.`User_id` AND U.`User_name` = [user name]) limit [x],[y]

getUserinfo.sql get user info from User_info.

To use these queries you need to specify [user name](requirement)

     SELECT `User_name`,`User_role`,`Email`,`Phone` FROM `User_info` WHERE `User_name` = [user name]

Specifications:

  1. isDuplicate

    • function : find out if the given userName existed in the database.
    • input parameter: [user name]

  2. signUp

    • function : store the user's registration info into the database and return status of registration.
    • input parameter: [user name], [password], [user role], [email], [phone].

  3. logIn

    • function : check the user's username and password in the database table 'User_info' and return status of log in.
    • input parameter: [user name], [password].

  4. upLoad

    • function : upload a seller Houses info and image url in the database tables 'Houses', 'Houses_image' and return status of upload.
    • input parameter: [user name], [Zipcode],[Address],[City],[State],[Price],[Beds],[Baths],[Built],[living_space],[Lot_space],[description],[Image_url].

  5. deleteUploaded

    • function : delete a seller Houses info and image url in the database tables 'Houses', 'Houses_image' and return status of delete.
    • input parameter: [house id]

  6. getHousesinfo

    • function : get house info with several features.
    • input parameter:

  7. addFavoritehouse

    • function : add a house to user's favorite list.
    • input parameter: [user name], [house id] seller:1 Buyer:2

Clone this wiki locally