maya.public

Parameters

Name	Type	Mode
in_serialnumber	character varying	IN

Definition

/* 
Configures a device to be keyboard capable 
v20151123 RRB sends to log the affected devices
v20171116 RRB sets consecutive portnumbers
*/
DECLARE
_iddevice int;
_portnumber int;
_idport int;
_isdirty bool; /* whether it was necessary to fix the configuration for the current device */
BEGIN
SELECT iddevice INTO _iddevice FROM device WHERE serialnumber = in_serialnumber;

/* configure the type of device */
PERFORM 1 FROM device d JOIN device_deviceconf dd ON d.iddevice = dd.iddevice JOIN deviceconf dc ON dc.iddeviceconf = dd.iddeviceconf WHERE d.serialnumber = in_serialnumber AND dc.iddeviceconf = 1 AND dd.configval = 'LM_DIRECT'; 
IF NOT FOUND THEN
_isdirty := true;
INSERT INTO device_deviceconf(iddevice,iddeviceconf,configval,setbyuser,setdate) VALUES(_iddevice,1,'LM_DIRECT',0,now()); 
END IF;

/* configure serialports */
PERFORM 1 FROM device d JOIN device_deviceconf dd ON d.iddevice = dd.iddevice JOIN deviceconf dc ON dc.iddeviceconf = dd.iddeviceconf WHERE d.serialnumber = in_serialnumber AND dc.iddeviceconf = 2; 
IF NOT FOUND THEN
_isdirty := true;
INSERT INTO device_deviceconf(iddevice,iddeviceconf,configval,setbyuser,setdate) VALUES(_iddevice,2,'',0,now()); 
END IF;

/*configure imei*/
PERFORM 1 FROM device d JOIN device_deviceconf dd ON d.iddevice = dd.iddevice JOIN deviceconf dc ON dc.iddeviceconf = dd.iddeviceconf WHERE d.serialnumber = in_serialnumber AND dc.iddeviceconf = 5; 
IF NOT FOUND THEN
_isdirty := true;
INSERT INTO device_deviceconf(iddevice,iddeviceconf,configval,setbyuser,setdate) VALUES(_iddevice,5,'',0,now()); 
END IF;

/*configure intervalconnection*/
PERFORM 1 FROM device d JOIN device_deviceconf dd ON d.iddevice = dd.iddevice JOIN deviceconf dc ON dc.iddeviceconf = dd.iddeviceconf WHERE d.serialnumber = in_serialnumber AND dc.iddeviceconf = 10; 
IF NOT FOUND THEN
_isdirty := true;
INSERT INTO device_deviceconf(iddevice,iddeviceconf,configval,setbyuser,setdate) VALUES(_iddevice,10,'5',0,now()); 
END IF;

/*configure intervaldistance*/
PERFORM 1 FROM device d JOIN device_deviceconf dd ON d.iddevice = dd.iddevice JOIN deviceconf dc ON dc.iddeviceconf = dd.iddeviceconf WHERE d.serialnumber = in_serialnumber AND dc.iddeviceconf = 11; 
IF NOT FOUND THEN
_isdirty := true;
INSERT INTO device_deviceconf(iddevice,iddeviceconf,configval,setbyuser,setdate) VALUES(_iddevice,11,'10',0,now()); 
END IF;

/* configure timeout */
PERFORM 1 FROM device d JOIN device_deviceconf dd ON d.iddevice = dd.iddevice JOIN deviceconf dc ON dc.iddeviceconf = dd.iddeviceconf WHERE d.serialnumber = in_serialnumber AND dc.iddeviceconf = 12; 
IF NOT FOUND THEN
_isdirty := true;
INSERT INTO device_deviceconf(iddevice,iddeviceconf,configval,setbyuser,setdate) VALUES(_iddevice,12,'60',0,now()); 
END IF;

/* configure breaktime */
PERFORM 1 FROM device d JOIN device_deviceconf dd ON d.iddevice = dd.iddevice JOIN deviceconf dc ON dc.iddeviceconf = dd.iddeviceconf WHERE d.serialnumber = in_serialnumber AND dc.iddeviceconf = 13; 
IF NOT FOUND THEN
_isdirty := true;
INSERT INTO device_deviceconf(iddevice,iddeviceconf,configval,setbyuser,setdate) VALUES(_iddevice,13,'4320',0,now()); 
END IF;

/* configure breaktime */
PERFORM 1 FROM device d JOIN device_deviceconf dd ON d.iddevice = dd.iddevice JOIN deviceconf dc ON dc.iddeviceconf = dd.iddeviceconf WHERE d.serialnumber = in_serialnumber AND dc.iddeviceconf = 33; 
IF NOT FOUND THEN
_isdirty := true;
INSERT INTO device_deviceconf(iddevice,iddeviceconf,configval,setbyuser,setdate) VALUES(_iddevice,33,'5',0,now()); 
END IF;

/* configure ports */

SELECT p.idport INTO _idport FROM device d JOIN port p ON p.iddevice = d.iddevice JOIN portcfg pc ON pc.idport = p.idport JOIN serialcfg s ON pc.idport = s.idport WHERE serialnumber = in_serialnumber AND s.serialport_devicetype = 0 AND s.alias_port = 0;
IF NOT FOUND THEN
_isdirty := true;
SELECT coalesce(max(portnumber),0) + 1 INTO _portnumber FROM port WHERE iddevice = _iddevice;
INSERT INTO port(iddevice,idport,portnumber) VALUES(_iddevice,nextval('sec_port'),_portnumber);
INSERT INTO portcfg(idport) VALUES(currval('sec_port'));
INSERT INTO serialcfg(idport,serialport_devicetype,alias_port) VALUES(currval('sec_port'),0,0);
ELSE
SELECT portnumber INTO _portnumber FROM port WHERE idport = _idport;
IF _portnumber IS NULL THEN
SELECT coalesce(max(portnumber),0) + 1 INTO _portnumber FROM port WHERE iddevice = _iddevice;
UPDATE port SET portnumber = _portnumber WHERE idport = _idport;
END IF;
END IF;

SELECT p.idport INTO _idport FROM device d JOIN port p ON p.iddevice = d.iddevice JOIN portcfg pc ON pc.idport = p.idport JOIN serialcfg s ON pc.idport = s.idport WHERE serialnumber = in_serialnumber AND s.serialport_devicetype = 2 AND s.alias_port = 1;
IF NOT FOUND THEN
_isdirty := true;
SELECT coalesce(max(portnumber),0) + 1 INTO _portnumber FROM port WHERE iddevice = _iddevice;
INSERT INTO port(iddevice,idport,portnumber) VALUES(_iddevice,nextval('sec_port'),_portnumber);
INSERT INTO portcfg(idport) VALUES(currval('sec_port'));
INSERT INTO serialcfg(idport,serialport_devicetype,alias_port) VALUES(currval('sec_port'),2,1);
ELSE
SELECT portnumber INTO _portnumber FROM port WHERE idport = _idport;
IF _portnumber IS NULL THEN
SELECT coalesce(max(portnumber),0) + 1 INTO _portnumber FROM port WHERE iddevice = _iddevice;
UPDATE port SET portnumber = _portnumber WHERE idport = _idport;
END IF;
END IF;

SELECT p.idport INTO _idport FROM device d JOIN port p ON p.iddevice = d.iddevice JOIN portcfg pc ON pc.idport = p.idport JOIN serialcfg s ON pc.idport = s.idport WHERE serialnumber = in_serialnumber AND s.serialport_devicetype = 0 AND s.alias_port = 2;
IF NOT FOUND THEN
_isdirty := true;
SELECT coalesce(max(portnumber),0) + 1 INTO _portnumber FROM port WHERE iddevice = _iddevice;
INSERT INTO port(iddevice,idport,portnumber) VALUES(_iddevice,nextval('sec_port'),_portnumber);
INSERT INTO portcfg(idport) VALUES(currval('sec_port'));
INSERT INTO serialcfg(idport,serialport_devicetype,alias_port) VALUES(currval('sec_port'),0,2);
ELSE
SELECT portnumber INTO _portnumber FROM port WHERE idport = _idport;
IF _portnumber IS NULL THEN
SELECT coalesce(max(portnumber),0) + 1 INTO _portnumber FROM port WHERE iddevice = _iddevice;
UPDATE port SET portnumber = _portnumber WHERE idport = _idport;
END IF;
END IF;

SELECT p.idport INTO _idport FROM device d JOIN port p ON p.iddevice = d.iddevice JOIN portcfg pc ON pc.idport = p.idport JOIN onewirecfg o ON pc.idport = o.idport WHERE serialnumber = in_serialnumber;
IF NOT FOUND THEN
_isdirty := true;
SELECT coalesce(max(portnumber),0) + 1 INTO _portnumber FROM port WHERE iddevice = _iddevice;
INSERT INTO port(iddevice,idport,portnumber) VALUES(_iddevice,nextval('sec_port'),_portnumber);
INSERT INTO portcfg(idport) VALUES(currval('sec_port'));
INSERT INTO onewirecfg(idport,enable,updateinterval) VALUES(currval('sec_port'),false,5);
INSERT INTO onewire_sensor(idport,sensorid) VALUES(currval('sec_port'),0),(currval('sec_port'),1),(currval('sec_port'),2),(currval('sec_port'),3),(currval('sec_port'),4),(currval('sec_port'),5);
ELSE
SELECT portnumber INTO _portnumber FROM port WHERE idport = _idport;
IF _portnumber IS NULL THEN
SELECT coalesce(max(portnumber),0) + 1 INTO _portnumber FROM port WHERE iddevice = _iddevice;
UPDATE port SET portnumber = _portnumber WHERE idport = _idport;
END IF;
END IF;

PERFORM 1 FROM inputoutput WHERE iddevice = _iddevice;
IF NOT FOUND THEN
_isdirty := true;
INSERT INTO inputoutput SELECT null,_iddevice,0,0,0,0,0,0,0,0,0,0,0,0,null,0,0,0,nextval('sec_inputoutput'),0 FROM generate_series(1,4);
END IF;

IF _isdirty THEN
RAISE WARNING 'fixedconf: % ',in_serialnumber;
END IF;

RETURN true;
END;

ot_rw_keyboardconf(character varying)

Parameters

Definition