Welcome to the LimeSurvey Community Forum

Ask the community, share ideas, and connect with other LimeSurvey users!

Limit one CNPJ (like EIN) per participant (VALIDATION DB)

  • WbTOne
  • WbTOne's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
5 years 1 week ago #190155 by WbTOne
Hello my friends!

I need to validate a field of my survey: CNPJ. It's a unique value per business on my form, like a EIN.

I want to limit just one register of business with same CNPJ number, example:
At first registry: 92.664.028/0001-41 VALIDATION: OK
If user try to register again as a new register: 92.664.028/0001-41 VALIDATION: ERROR!

Thank you very much!! ;-)
The topic has been locked.
  • holch
  • holch's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
5 years 1 week ago #190158 by holch
I am not sure why you reported this? Do you want it deleted or do you want an answer?

I answer at the LimeSurvey forum in my spare time, I'm not a LimeSurvey GmbH employee.
No support via private message.

The following user(s) said Thank You: DenisChenu, WbTOne
The topic has been locked.
  • WbTOne
  • WbTOne's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
5 years 1 week ago #190186 by WbTOne
No. I'm sorry...

I need help, not delete please. ;-)

Tks
The topic has been locked.
  • Joffm
  • Joffm's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
5 years 1 week ago - 5 years 1 week ago #190189 by Joffm
Hi,
since LS is a survey tool each respondent or each questionnaire is absolutely independent.
Meaning LS has no access to the answers of other respondents.

You have to use an ajax call to query the database.
Create a question of type "short free text" and include this snippet in the question text (source code mode).
Code:
<script type="text/javascript" charset="utf-8">
$(document).on('ready pjax:scriptcomplete',function(){
  $.post('https://www.myServer.com/myPath/getCount.php' , { sid: "{SID}"} ,function(data) {
       $('#question{QID} input[type="text"]').val(data)
  });
  $('#question{QID}').hide();
});
</script>

The php file now has to query the database and return the number of your used IDs (0 or 1).

The way you query the database is up to you.
Either with direct access to the LS database or by the API.

Now you have the result in this question.

Joffm

Volunteers are not paid.
Not because they are worthless, but because they are priceless
Last edit: 5 years 1 week ago by Joffm.
The following user(s) said Thank You: DenisChenu, holch, WbTOne
The topic has been locked.
  • WbTOne
  • WbTOne's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
5 years 1 week ago #190204 by WbTOne
Very nice, Joffm! Thanks a lot!!

My php is:
Code:
if ($db)
  {
      $ac_cnpjval = "%".$_GET['cnpjval']."%";
      $query = "SELECT id, 195818X4X52 FROM gfrm_survey_195818 WHERE 195818X4X52 LIKE :cnpjval";
      $result = $db->prepare($query);
      $result->bindValue(":cnpjval",$ac_cnpjval); 
      $result->execute(); 
 
  // Fetch data and store in array
      $row = $result->fetch();
 
      if ($row['id'] != NULL) 
      {
        echo "<pre>";
        echo "Duplicated value - id: " . $row['id'];
        echo "</pre>";
        echo "<br>";
      } else { 
        echo "The value is unique, OK!.";
      }
  }
It's Working \o/

Now, I need to get the typed field HTML after typed and send ajax requisition to validate it, real-time!
It's possible?

Thank you!!
The topic has been locked.
  • Joffm
  • Joffm's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
5 years 1 week ago #190208 by Joffm
Three things:
1. As you see, the ajax call sends a $_POST-parameter, so maybe you should also receive it by $_POST
2. You should only echo a "0" or "1", which is displayed in the short text question. This you can use to give a message to the respondent and to screenout.
3. I missed in my example to post the question ID of the text entry field and the entered value.

So now the call is:
Code:
<script type="text/javascript" charset="utf-8">
$(document).on('ready pjax:scriptcomplete',function(){
  $.post('https://www.myServer.com/myPath/getCount.php' , { sid: "{SID}", qid: "602X9024", vv: "{Q1}"} ,function(data) {
       $('#question{QID} input[type="text"]').val(data)
  });
//  $('#question{QID}').hide();
});
</script>
Explanation:
"602X9024": group and question ID of CNPJ question (Q1) (has to be adapted)

And the php is like that:
Code:
<?php
// database connection
 
$SID   = $_POST["sid"];
$QID   =  $SID."X".$_POST["qid"];
$value = $_POST["vv"];
 
 
$count = DB::queryFirstField("SELECT count(*) FROM lime_survey_".$SID." WHERE ".$QID."=".$value." and submitdate is not NULL");
 
if ($count>0) {$count=1;}
echo $count;
// Either 0 or 1 is returned
?>

LSS:

File Attachment:

File Name: limesurvey...0-22.lss
File Size:20 KB


Joffm

Volunteers are not paid.
Not because they are worthless, but because they are priceless
The following user(s) said Thank You: WbTOne
The topic has been locked.
  • tpartner
  • tpartner's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
5 years 1 week ago #190213 by tpartner
Not much use here, but in version 4.x, custom Twig extensions containing PHP will be available so we should be able to roll this all into a custom question theme without an AJAX call or a remote PHP file.

Cheers,
Tony Partner

Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.
The following user(s) said Thank You: WbTOne
The topic has been locked.
  • DenisChenu
  • DenisChenu's Avatar
  • Offline
  • LimeSurvey Community Team & Official Partner
  • LimeSurvey Community Team & Official Partner
More
5 years 1 week ago #190224 by DenisChenu

tpartner wrote: Not much use here, but in version 4.x, custom Twig extensions containing PHP will be available so we should be able to roll this all into a custom question theme without an AJAX call or a remote PHP file.

I think we can use a new Expression Manager function

See the push request : github.com/LimeSurvey/LimeSurvey/pull/1320
And see related feature fixed : bugs.limesurvey.org/view.php?id=11589

Then with a
Code:
statCountIf(self.sgqa, self.NAOK) == 0
It's fixed in PHP only…

Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member. - Professional support - Plugins, theme and development .
I don't answer to private message.
The following user(s) said Thank You: WbTOne
The topic has been locked.
More
5 years 1 week ago #190239 by jelo

tpartner wrote: Not much use here, but in version 4.x, custom Twig extensions containing PHP will be available so we should be able to roll this all into a custom question theme without an AJAX call or a remote PHP file.

Interesting. The workarounds with external PHPscripts are often begging for getting hacked/misused.
I wasn't aware that PHP code will be allowed in custom twig extensions in LS4.

The meaning of the word "stable" for users
www.limesurvey.org/forum/development/117...ord-stable-for-users
The following user(s) said Thank You: WbTOne
The topic has been locked.
  • tpartner
  • tpartner's Avatar
  • Offline
  • LimeSurvey Community Team
  • LimeSurvey Community Team
More
5 years 1 week ago #190250 by tpartner

jelo wrote:

tpartner wrote: Not much use here, but in version 4.x, custom Twig extensions containing PHP will be available so we should be able to roll this all into a custom question theme without an AJAX call or a remote PHP file.

Interesting. The workarounds with external PHPscripts are often begging for getting hacked/misused.
I wasn't aware that PHP code will be allowed in custom twig extensions in LS4.

Yes, by definition the extensions are PHP files.

Here is a quote from the comments in the sample extension:

NOTE 2: A twig extension is PHP code, so it can do anything on the server. So user should trust you to upload your twig extension.
* So if your goal is to sell your Survey / Question Theme on LimeStore, you'd rather use twig code as much as you can.


I'm interested in trying it with API calls but currently it's broken (at least I cannot get it to work) - bugs.limesurvey.org/view.php?id=15446

Cheers,
Tony Partner

Solutions, code and workarounds presented in these forums are given without any warranty, implied or otherwise.
The following user(s) said Thank You: WbTOne
The topic has been locked.
  • WbTOne
  • WbTOne's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
5 years 1 week ago - 5 years 1 week ago #190274 by WbTOne
wowww.. Very very nice!! Almost done!! \o/

Thanks for all your suport!! @Joffm @tpartner @DenisChenu @jelo
I hope this question can helps community. :)

Now, my JS, with addScriptToQuestion plugin, is:
Code:
$("#answer195818X4X52").keyup(function () {
 
    var total_chars = 18;
 
    if ($('#answer195818X4X52').val().length == total_chars) {
 
        $.post('https://url/script.php', { sid: "{SID}", qid: "4X52", vv: "{Q1}" }, function (data) {
            $('#question{QID} input[type="text"]').val(data)
        });
        //  $('#question{QID}').hide();
    }
 
});

My php is:
Code:
$SID   = $_POST["sid"];
$QID   = $SID."X".$_POST["qid"];
$value = $_POST["vv"];
 
$count = DB::queryFirstField("SELECT count(*) FROM lime_survey_".$SID." WHERE ".$QID."=".$value." and submitdate is not NULL");
 
if ($count>0) {$count=1;}
echo $count;

but, when I type all characters and the ajax is executed, is returned error 500 on php script.
In browser console, the variables are:
sid: 195818
qid: 4X52
vv: <span class='em-expression em-haveerror' ><span class='em-error'><span title='Undefined variable' class='em-var em-var-error' >Q1</span></span></span>

UPTDATE:
If I update {Q1} var to {P003} (P003 is my question code) the 'vv' var is null:
vv:



Thanks again :-D
Last edit: 5 years 1 week ago by WbTOne.
The topic has been locked.
  • WbTOne
  • WbTOne's Avatar Topic Author
  • Offline
  • New Member
  • New Member
More
5 years 5 days ago - 5 years 5 days ago #190400 by WbTOne
Hello!

I got it!! With the essential help of you my friends!
Code:
====
JS:
====
 
$("#answer{SID}X{GID}X{QID}").keyup(function () {
    var that = this;
    var value = $(this).val();
    var total_chars = 18;
 
    if ($('#answer{SID}X{GID}X{QID}').val().length == total_chars) {
 
        $.post('https://uri.com/script.php', { sid: "{SID}", qid: "{GID}X{QID}", vv: value}, function (data) {
 
            if (data == 1) {
                $('#question{QID}').removeClass("input-error").addClass( "input-error" );
                $('#vmsg_{QID} div').removeClass("ls-em-error").removeClass("text-danger").addClass("ls-em-error").addClass("text-danger");
                $('#vmsg_{QID} div').show().html("<span class='fa fa-exclamation-circle' aria-hidden='true'> </span> CNPJ exists. Please, try again.");
            } else { 
                $('#question{QID}').removeClass("input-error");
                $('#vmsg_{QID} div').removeClass("ls-em-error").removeClass("text-danger");
                $('#vmsg_{QID} div').hide();
            } 
 
        });
    }
 
});

Code:
====
PHP:
====
 
$dbUser = '';
$dbPassword = '';
$dbServer = '';
$dbName = '';
 
$pdo = new PDO("mysql:host=$dbServer;dbname=$dbName", $dbUser, $dbPassword);
 
$SID   = $_POST["sid"];
$QID   = $SID."X".$_POST["qid"];
$value = $_POST["vv"];
 
$sql = "SELECT COUNT(*) FROM gfrm_survey_$SID WHERE $QID='$value' and submitdate is not NULL";
 
$statement = $pdo->prepare($sql);
 
$statement -> execute();
$statement -> execute(array($num_rows));
$num_rows  = $statement->fetchColumn();
 
echo $num_rows;
Last edit: 5 years 5 days ago by WbTOne.
The topic has been locked.
Moderators: tpartnerholch

Lime-years ahead

Online-surveys for every purse and purpose