<%@ pgmtype="srvpgm" runasowner="*YES" owner="QPGMR" free="*YES" pgmopt="export(*ALL)"%>
<%
ctl-opt copyright('System & Method (C), 2018');
ctl-opt decEdit('0,') datEdit(*YMD.) nomain; 
ctl-opt bndDir('JSONXML':'ICEUTILITY');

 // -----------------------------------------------------------------------------
 // Service . . . : Booking
 //
 //  CRTICEPGM STMF('/www/dev/basext/customized/application/booking/services/Booking.rpgle') 
 //
 // By     Date       PTF     Description
 // ------ ---------- ------- ---------------------------------------------------
 //
 // -----------------------------------------------------------------------------

 /include qasphdr,iceBreak
 /include qasphdr,iceUtility
 /include qasphdr,jsonParser
 
/* -------------------------------------------------------------------- *\  
	
	// List data: 
	dksrv206:60000/router/Booking/search?payload={
 		"query"    :  "lower(desc) like '%digital%'",
		"start"    : 1,
		"limit"    : 25
   	}


 * -------------------------------------------------------------------- */
dcl-proc search export;

	dcl-pi *n pointer;
		pInput pointer value;
	end-pi;

	dcl-s pOutput  		pointer;
	dcl-s pSort			pointer;
	dcl-s sqlStmt 		varchar(4096);
	dcl-s query  		varchar(4096);
	dcl-s start  		int(10);
	dcl-s limit 		int(10);

	// Get parameter from request
	start = json_getInt (pInput : 'start'); 
	limit = json_getInt (pInput : 'limit' : -1); // Default to all rows
	query = json_getStr (pInput : 'textSearch');

	// TODO!! 
	json_moveObjectInto (pInput: 'sort' : json_parseString(json_getStr (pInput : 'sort')));
	
	// Build the basic select statement
	sqlStmt = (`
		select * from dhldevdb.DHLBOKET00_grid_booking_detail;
	`);

	// Add the where clause if any query parameter is found
	// NOTE: Check for injections
	if query > '';
		sqlStmt += (`
			where  lower(
				shipmentnumber concat 
				qaregion concat 
				qaroute concat 
				qastop 
			)  
			like lower( ${ strQuot('%' +query + '%' )}) 		
		`);
	endif;

	// Do we have a sort object? Then add the "Order by" in the SQL 	
	pSort = json_locate(pInput : 'sort[0]');
	if pSort <> *NULL;
		strAppend (sqlStmt : ' ' : 'order by');
		strAppend (sqlStmt : ' ' : json_getStr ( pSort : 'property'));
		strAppend (sqlStmt : ' ' : json_getStr ( pSort : 'direction'));
	endif;

	// We can log the result if needed
	// consoleLog (sqlStmt);

	pOutput = json_sqlResultSet ( 
		 sqlStmt
		:start
		:limit 
		:JSON_META + JSON_TOTALROWS  
	);

	if pOutput = *NULL;
		return json_getMessageObject();
	endif;

	return pOutput;

end-proc;

/* -------------------------------------------------------------------- *\  
	
	// get one row
	dksrv206:60000/router/Booking/getrow?payload={
		"id"    :  100
 	}


 * -------------------------------------------------------------------- */
dcl-proc getRow export;

	dcl-pi *n pointer;
		pInput pointer value;
	end-pi;

	dcl-s pOutput  		pointer;
	dcl-s sqlStmt 		varchar(4096);
	dcl-s id            int(20);

	id  = json_getInt(pInput : 'SHIPMENTNUMBER');

	sqlStmt = (
		  'select * from dhldevdb.DHLBOKET00_grid_booking_detail;' 
		+ ' where SHIPMENTNUMBER = ' + %char(id) 
	);	
	
	pOutput = json_sqlResultSet ( 
		 sqlStmt
		:1 // start
		:1 //limit 
		:JSON_META + JSON_TOTALROWS  
	);
	
	if pOutput = *NULL;
		return json_getMessageObject();
	endif;

	if json_getLength(json_locate (pOutput : 'rows')) = 0;
		json_delete (pOutput);
		return json_getMessageObject('APP1000' : 'Row not found');
	endif;

	return pOutput;

end-proc;
/* -------------------------------------------------------------------- *\  
	
	// Update a row, the id must be untouched in the row:
	dksrv206:60000/router/Booking/updateRow?payload={
		"row" : {
			"desc"  : "Test Test",
			"stockdate" : "2018-01-30"
		}
 	}

 * -------------------------------------------------------------------- */
dcl-proc updateRow export;

	dcl-pi *n pointer;
		pInput pointer value;
	end-pi;

	dcl-s pRow   		pointer;
	dcl-s sqlStmt 		varchar(4096);
	dcl-s err 			ind;
	dcl-s id            int(20);
	dcl-s pError  		pointer;


	// First validate the input:
	pError = validate ( pInput) ; 
	if pError <> *NULL;
		return pError;
	endif;	

	pRow = json_locate (pInput : 'row');
	id   = json_getInt (pRow   : 'SHIPMENTNUMBER');

	err = json_sqlUpdate ( 
		'Booking'
		:pRow
		: 'SHIPMENTNUMBER = ' + %char(id)
	 );

	if err ;
		return json_getMessageObject();
	endif;

	return json_successTrue();


end-proc;
/* -------------------------------------------------------------------- *\  
	
	// Insert into table
	dksrv206:60000/router/Booking/createRow?payload={
		"row" : {
			"column1"  : "value1",
			"column2"  : "value2"
		}
 	}


 * -------------------------------------------------------------------- */
dcl-proc createRow export;

	dcl-pi *n pointer;
		pInput pointer value;
	end-pi;

	dcl-s sqlStmt 		varchar(4096);
	dcl-s err 			ind;
	dcl-s pRow   		pointer;
	dcl-s pError  		pointer;


	// First validate the input:
	pError = validate ( pInput) ; 
	if pError <> *NULL;
		return pError;
	endif;	

	pRow = json_locate (pInput : 'row');

	err = json_sqlInsert (
		'Booking'
		:pRow
	 );

	if err ;
		return json_getMessageObject();
	endif;

	return json_successTrue();

end-proc;
/* -------------------------------------------------------------------- *\  
	
	// get one row for delete
	dksrv206:60000/router/Booking/createRow?payload={
		"id"  : 123
 	}

 * -------------------------------------------------------------------- */
dcl-proc deleteRow export;

	dcl-pi *n pointer;
		pInput pointer value;
	end-pi;

	dcl-s err 			ind;
	dcl-s id            int(20);

	id  = json_getInt(pInput : 'SHIPMENTNUMBER');

	err = json_sqlExec  (
		'Delete from Booking where SHIPMENTNUMBER = ' 
		+ %char(id)
	 );

	if err ;
		return json_getMessageObject();
	endif;

	return json_successTrue();

end-proc;
/* -------------------------------------------------------------------- *\  
	Validate the row brfore update / insert
	note: if the pInpu object has an "id" attribute, then it is a update
	This function returns *NULL if no errors was found
 * -------------------------------------------------------------------- */
dcl-proc validate;

	dcl-pi *n pointer;
		pInput pointer value;
	end-pi;

	dcl-s pOutput 		pointer;
	dcl-s pError  		pointer;
	dcl-s pRow   		pointer;

	pRow = json_locate (pInput : 'row');

	pError = json_newArray();
	
	// Test for value: When an error occrs, 
	// add an new entry in the error list array - Field and message:
	if json_getStr(pRow : 'id') = '';
		pushMessage(pError:'id': 'ID can not be blank');
	endif;

	if json_getStr(pRow : 'shipmentnumber') = '';
		pushMessage(pError:'shipmentnumber': 'Shipment Number can not be blank');
	endif;

	if json_getStr(pRow : 'timestampdelivered') = '';
		pushMessage(pError:'timestampdelivered': 'Delivery Time can not be blank');
	endif;

	if json_getStr(pRow : 'ontime') = '';
		pushMessage(pError:'ontime': 'On Time can not be blank');
	endif;

	if json_getStr(pRow : 'qaregion') = '';
		pushMessage(pError:'qaregion': 'Region can not be blank');
	endif;

	if json_getStr(pRow : 'qaroute') = '';
		pushMessage(pError:'qaroute': 'Route can not be blank');
	endif;

	if json_getStr(pRow : 'qastop') = '';
		pushMessage(pError:'qastop': 'Stop can not be blank');
	endif;

	if json_getStr(pRow : 'packages') = '';
		pushMessage(pError:'packages': 'Kolli can not be blank');
	endif;

	if json_getStr(pRow : 'cmb') = '';
		pushMessage(pError:'cmb': 'Shipment Volume M3 can not be blank');
	endif;

	if json_getStr(pRow : 'qaproduct') = '';
		pushMessage(pError:'qaproduct': 'Product Type can not be blank');
	endif;

	if json_getStr(pRow : 'solar_orders') = '';
		pushMessage(pError:'solar_orders': 'Solarordrer can not be blank');
	endif;

	if json_getStr(pRow : 'gps_x') = '';
		pushMessage(pError:'gps_x': 'GPS X can not be blank');
	endif;

	if json_getStr(pRow : 'gps_y') = '';
		pushMessage(pError:'gps_y': 'GPS Y can not be blank');
	endif;

	if json_getStr(pRow : 'address1') = '';
		pushMessage(pError:'address1': 'Address 1 can not be blank');
	endif;

	if json_getStr(pRow : 'address2') = '';
		pushMessage(pError:'address2': 'Address 2 can not be blank');
	endif;

	if json_getStr(pRow : 'address3') = '';
		pushMessage(pError:'address3': 'Address 3 can not be blank');
	endif;

	if json_getStr(pRow : 'address4') = '';
		pushMessage(pError:'address4': 'Address 4 can not be blank');
	endif;

	if json_getStr(pRow : 'addressphone') = '';
		pushMessage(pError:'addressphone': 'Address (Phone Number) can not be blank');
	endif;

	if json_getStr(pRow : 'qadatebooking') = '';
		pushMessage(pError:'qadatebooking': 'Dato can not be blank');
	endif;

	if json_getStr(pRow : 'deliverytour') = '';
		pushMessage(pError:'deliverytour': 'Delivery tour can not be blank');
	endif;

	if json_getStr(pRow : 'receivedby') = '';
		pushMessage(pError:'receivedby': 'Received by can not be blank');
	endif;

	
	// If no errors was made, the delete the error list, and return OK
	if json_getLength (pError) = 0;
		json_delete ( pError);
		return *NULL; // No errors
	endif;


	// Build the message object
	pOutput = json_getMessageObject ('APP1002' : 'Error on page');
	json_moveObjectInto (pOutput : 'errors' : pError);
	return pOutput;


end-proc;
/* -------------------------------------------------------------------- *\  
	Push a message to the list of errors on a form
 * -------------------------------------------------------------------- */
dcl-proc pushMessage;
                                                                                    

	dcl-pi *n;
		pError	  pointer value;
		fieldName varchar(64)  const;
		msg       varchar(512) const;
	end-pi;

	dcl-s pMsg  		pointer;

	pMsg = json_newObject();
	json_setStr (pMsg : 'id'  : fieldName);
	json_setStr (pMsg : 'msg' : msg);

	json_arrayPush (pError : pMsg);
end-proc;
                                                                                    
                                                                                    
                                                                                    
                                                                                    
                                                                                    
                                                                                    
                                                                                    
                                                                                    
                                                                                    
                                                                                    
                                                                                    
                                                                                    
                                                                                    
                                                                                    




