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
Topic Author
Offline
New Member

More

4 years 5 months ago #190155 by WbTOne

Limit one CNPJ (like EIN) per participant (VALIDATION DB) was created 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
Offline
LimeSurvey Community Team

More

4 years 5 months ago #190158 by holch

Replied by holch on topic Limit one CNPJ (like EIN) per participant (VALIDATION DB)

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
Topic Author
Offline
New Member

More

4 years 5 months ago #190186 by WbTOne

Replied by WbTOne on topic Limit one CNPJ (like EIN) per participant (VALIDATION DB)

No. I'm sorry...

I need help, not delete please. ;-)

Tks

The topic has been locked.

Joffm
Offline
LimeSurvey Community Team

More

4 years 5 months ago - 4 years 5 months ago #190189 by Joffm

Replied by Joffm on topic Limit one CNPJ (like EIN) per participant (VALIDATION DB)

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: 4 years 5 months ago by Joffm.

The following user(s) said Thank You: DenisChenu, holch, WbTOne

The topic has been locked.

WbTOne
Topic Author
Offline
New Member

More

4 years 5 months ago #190204 by WbTOne

Replied by WbTOne on topic Limit one CNPJ (like EIN) per participant (VALIDATION DB)

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
Offline
LimeSurvey Community Team

More

4 years 5 months ago #190208 by Joffm

Replied by Joffm on topic Limit one CNPJ (like EIN) per participant (VALIDATION DB)

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
Offline
LimeSurvey Community Team

More

4 years 5 months ago #190213 by tpartner

Replied by tpartner on topic Limit one CNPJ (like EIN) per participant (VALIDATION DB)

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
Offline
LimeSurvey Community Team

More

4 years 5 months ago #190224 by DenisChenu

Replied by DenisChenu on topic Limit one CNPJ (like EIN) per participant (VALIDATION DB)

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 service on demand , plugin development .
I don't answer to private message.

The following user(s) said Thank You: WbTOne

The topic has been locked.

jelo
Offline
Platinum Member

More

4 years 5 months ago #190239 by jelo

Replied by jelo on topic Limit one CNPJ (like EIN) per participant (VALIDATION DB)

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
Offline
LimeSurvey Community Team

More

4 years 5 months ago #190250 by tpartner

Replied by tpartner on topic Limit one CNPJ (like EIN) per participant (VALIDATION DB)

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
Topic Author
Offline
New Member

More

4 years 5 months ago - 4 years 5 months ago #190274 by WbTOne

Replied by WbTOne on topic Limit one CNPJ (like EIN) per participant (VALIDATION DB)

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('http://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: Q1

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

Thanks again :-D

Last edit: 4 years 5 months ago by WbTOne.

The topic has been locked.

WbTOne
Topic Author
Offline
New Member

More

4 years 5 months ago - 4 years 5 months ago #190400 by WbTOne

Replied by WbTOne on topic Limit one CNPJ (like EIN) per participant (VALIDATION DB)

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('http://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: 4 years 5 months ago by WbTOne.

The topic has been locked.

Lime-years ahead

Online-surveys for every purse and purpose

Pricing & Plans

Get started

Welcome to the LimeSurvey Community Forum

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

File Attachment:

Lime-years ahead

Legal

About Us

Open Source