Back to limesurvey.org

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)

More
6 years 11 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.
Partner Banner
More
6 years 11 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?
Help us to help you!
  • Provide your LS version and where it is installed (own server, uni/employer, SaaS hosting, etc.).
  • Always provide a LSS file (not LSQ or LSG).
Note: I answer at this forum in my spare time, I'm not a LimeSurvey GmbH employee.
The following user(s) said Thank You: DenisChenu, WbTOne
The topic has been locked.
More
6 years 11 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.
More
6 years 11 months ago - 6 years 11 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: 6 years 11 months ago by Joffm.
The following user(s) said Thank You: DenisChenu, holch, WbTOne
The topic has been locked.
More
6 years 11 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.
More
6 years 11 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:19.6 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.
More
6 years 11 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.
More
6 years 11 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 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
6 years 11 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.
More
6 years 11 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.
More
6 years 11 months ago - 6 years 11 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('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: 6 years 11 months ago by WbTOne.
The topic has been locked.
More
6 years 11 months ago - 6 years 11 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('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: 6 years 11 months ago by WbTOne.
The topic has been locked.
Moderators: tpartner, holch

Lime-years ahead

Online-surveys for every purse and purpose

Pricing & Plans
Get started

Legal

About Us

Open Source