Month: April 2020

Add below crontab :-

20 3 * * * /usr/bin/curl --fail -o /tmp/DMRIds.dat -s http://www.pistar.uk/downloads/DMRIds.dat > /dev/null 2>&1

/path/update_newestdmrid_datfromtmp.sh :-

#!/bin/bash
/usr/bin/mysql --user=dbuser --password='password' -e "source /path/update_newestdmrid_datfromtmp.sql"

/path/update_newestdmrid_datfromtmp.sql :-

USE table;

CREATE TABLE IF NOT EXISTS temp_table SELECT ccs7, callsign, name FROM table LIMIT 0;

CREATE INDEX ccs7 on temp_table (ccs7) USING BTREE;
CREATE INDEX callsign on temp_table (callsign) USING BTREE;
CREATE INDEX name on temp_table (name) USING BTREE;

LOAD DATA LOCAL INFILE '/tmp/DMRIds.dat'
INTO TABLE temp_table
FIELDS TERMINATED BY '\t';

INSERT INTO table
(ccs7, callsign, name)
SELECT t1.ccs7, t1.callsign, t1.name FROM temp_table t1
WHERE NOT EXISTS(SELECT ccs7 FROM table t2
WHERE t2.ccs7 = t1.ccs7 and t2.callsign = t1.callsign);

DROP TABLE temp_table;

***remark your dbuser,password,table & temp_table to your db/user and table in MySQL database

#!/usr/bin/perl

# Use the DBI module
#use strict;
use DBI;
#use warnings;
#use LWP::Simple;
use JSON::XS qw( decode_json );
use Data::Dumper;
use String::Util qw(trim);
use LWP::Simple qw(get);
use LWP::UserAgent;

#qrz api
use Ham::Reference::QRZ;
use Data::Dumper;


my $ua = LWP::UserAgent->new();
$ua->agent('Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/74.0.3729.169 Safari/537.36');
# PERL SQL Update Example
# my $callsign = uc("9W2LWK");
sub ucwords
{
my $str = shift;
$str = lc($str);
$str =~ s/\b(\w)/\u$1/g;
return $str;
}

print "Enter callsign please: ";
my $callsign = <STDIN>;
chomp $callsign;
$callsign = uc(trim($callsign));
print "\n";
print "Please enter first name first or last name first: (0=firstnamefirst/1=lastnamefirst): ";
my $flchoice = <STDIN>;
chomp $flchoice;
$flchoice = trim($flchoice);

#-------------------------------------
# qrz.com api
#-------------------------------------

my $qrz = Ham::Reference::QRZ->new(
callsign => $callsign,
username => '9w2lwk',
password => 'xxxxxxx'
);

my $listing = $qrz->get_listing;
my $dxcc = $qrz->get_dxcc;
my $session = $qrz->get_session;

$qrz->set_callsign('9W4GWK');
#debug
print "QRZ.com Surname and lastname\n";
#print Dumper($listing);
#print Dumper($dxcc);
#print Dumper($session);
#end-debug

print "Name: $listing->{name}\n";
print "Last Name: $listing->{fname}\n";

my $fname2 = $listing->{fname};
my $name2 = $listing->{name};
#--------------------------------------
# end of qrz.com api
#--------------------------------------

#my $url = 'http://www.radioid.net/api/dmr/user/?callsign=ON2RVQ';
my $url = 'https://radioid.net/api/dmr/user/?callsign='.$callsign;
my $response = $ua->get($url);
my $content = $response->content;
#my $content = get($url);
#print $content;
die "Couldn't get $url" unless defined $content;
my $json_array = decode_json($content);
my $dsn = 'DBI:mysql:database=databasename;host=localhost';
my $db_user_name = 'dbuser';
my $db_password = 'dbpassword';
my $password = "dbpassword";

print "\n";
print "This is the contents from radioio.net : -"."\n";
print "\n";

print $content;
print "\n";
print "\n";

my $fname = $json_array->{results}->[0]->{fname};
my $surname = $json_array->{results}->[0]->{surname};
my $country = $json_array->{results}->[0]->{country};
my $state = $json_array->{results}->[0]->{state};

print "fname count:-\n";
print $fname2 =~ s/[FT]//g;
print "\n";

if (!defined $fname2)
{
print "not change";
}
else
{
$fname = $fname2;
$surname = $name2;
}

if ($flchoice eq '0')
{
$fullname = ucwords(ucwords(lc($fname)) . " " . ucwords(lc($surname)));
#my $fullname = $fname . $surname;
print "First Name = " . $fname ."\n";
print "Surname = " . $surname ."\n";
print "Full Name = " . $fullname ."\n";
}
elsif ($flchoice eq '1')
{
$fullname = ucwords(ucwords(lc($surname)) . " " . ucwords(lc($fname)));
print "First Name = " . $fname ."\n";
print "Surname = " . $surname ."\n";
print "Full Name = " . $fullname ."\n";
}
else
{
$fullname = ucwords(ucwords(lc($fname)) . " " . ucwords(lc($surname)));
print "First Name = " . $fname ."\n";
print "Surname = " . $surname ."\n";
print "Full Name = " . $fullname ."\n";
}


#print Dumper($json_array);

# Connect to the database
# Note this connection can be used to
# execute more than one statement
# on any number of tables in the database
if ($callsign eq '')
{
print "Input cannot be blank"."\n";
}
else
{
my $dbh = DBI->connect($dsn, $db_user_name, $db_password) or die "Unable to connect: $DBI::errstr\n" ;
print "Select the record out first"."\n";
my $stmt2 = "SELECT * FROM dmrid where callsign = " . "'" . trim("$callsign") . "'";
print "\n";
print $stmt2."\n";
my $sth2 = $dbh->prepare($stmt2) or die "Unable to connect: $DBI::errstr\n" ;
$sth2->execute();

my ($count) = $sth2->fetchrow_array;
if ($count == 0) {
print "NO rows fetched!\n";
#$sth2->finish();
#$dbh->disconnect();
}
else
{
print "Record found!\n";
my $dbh = DBI->connect($dsn, $db_user_name, $db_password) or die "Unable to connect: $DBI::errstr\n" ;

# Create the statement.
#print $flchoice ."\n";
print "debug: Full Name = " . $fullname ."\n";

my $fullname_ = trim($fullname);
my $stmt = "UPDATE dmrid SET name = " . $dbh->quote("$fullname_") . "," . "country = " . $dbh->quote("$country") . "," . "state = " . $dbh->quote("$state") . " WHERE callsign = " . $dbh->quote("$callsign") ;
print "\n";
print "\n";
print $stmt."\n";
# Prepare and execute the SQL query

my $sth = $dbh->prepare($stmt) or die "Unable to connect: $DBI::errstr\n" ;

# Execute the statement
$sth->execute();

$sth->finish();
$dbh->disconnect();
}

}

table structure:-

Original post and credit to : Ronald PE2KMV https://www.pe2kmv.nl/wp/en/dmr-en/sms-functions-via-dmr/

The Brandmeister network has some functions to request information via SMS messages. To access these functions specific keywords are to be sent to DMR ID 262993. Then the system responds as well via SMS on these requests. Below you’ll find an overview of these commands with some explanation. The commands are not case sensitive.

Furthermore it’s possible to route DMR messages into the DAPNet pager network. More information regarding DAPNet can be found at the website of Dutch Amateur Pagernetwork and  RWTH Aachen (Aachen University). Messages for DAPNet are to be sent to DMR ID 262994.

A pager message for DAPNet is to be sent in the format [callsign] [message], for instance PX0XXX This is a message from DMR to DAPNet . This sends a messagetext ‘This is a message from DMR to DAPNet’ to user PX0XXX . Prerequisite is that ‘PX0XXX’ is a registered DAPNet user.