Experiences of an OpenSource developer.. "Everyday trying to get closer to the metal".
Wednesday, July 30, 2014
WorkmateHR
Payroll and HR management the easy way....
Let this short video say the rest....
WorkmateHR from Mabura ZeGuru on Vimeo.
Friday, June 27, 2014
Dynamic SQL UPDATE
NEED TO UPDATE table columns with dynamic values (not known in advance).
A. ORACLE
i.Single-Column update
[Working example.. just replace table names]
UPDATE station s
SET vhf_network_id =
(
SELECT vhf_network_id
FROM vhf_network v
WHERE s.client_license_id = v.client_license_id
)
WHERE s.station_charge_id = 5;
ii. Multiple update
The key here is to maintain the order and count of the columns in the source columnlist and destination columnlist
UPDATE
SET (
(
SELECT
FROM
WHERE
SELECT
FROM
WHERE
)
WHERE ;
B. POSTGRES EQUIVALENT
Working example (difference with the above is the absence of INNER SELECT)
UPDATE station s
SET vhf_network_id = v.vhf_network_id
FROM vhf_network v
WHERE s.client_license_id = vhf_network.client_license_id;
Friday, June 6, 2014
Delivery Reports (DLR) in kannel
Good Mawwwwwning nerdmigos,
You (name hidden) requested my assistance on doing DLR in kannel. There you are, kaz qwako
There are a few approaches but the following (and derivaties) has never let me down.
I dont know if its only me, but on kannel release 1.4.3 you are ONLY able to get DLR when u set dlr-mask to 31
The following approach (or similar) has never let me down...
a. kanne.conf
#TRIGGER
group = sms-service
keyword = trigger
aliases = triger
get-url = "http://192.168.1.45:8080/mwasima/trigger?keyword=%k&client=%s&service=%s&sender=%p&time=%t&dlrmask=31&dbtype=postgres&smsgateway=192.168.1.30"
omit-empty = true
and the following (proxy) function to push sms
b. IOLogic.java
String clean_sms = sms.replace(" ","%20"); //we can do better than this !!!!!
String reply = "";
log.info("ATTEMPTING to send [" + sms + "] to " + receiver);
dlrurl = encodeURL(webapp + "/dlr?smsid=" + smsid + "&dest=" + receiver + "&type=%d&dlr=%A");
try{
URL send_sms_url = new URL("http://" + smsgateway + ":13013/cgi-bin/sendsms?username=" + ux +"&password=" + px +(flash==true?"&mclass=0":"")+ "&to=" + receiver +"&text=" + clean_sms + "&dlr-mask=" + dlrmask + "&dlr-url=" + dlrurl);
log.info("send sms url = " + send_sms_url);
URLConnection url_con = send_sms_url.openConnection();
BufferedReader in = new BufferedReader(new InputStreamReader(url_con.getInputStream()));
String inputLine;
while ((inputLine = in.readLine()) != null){
reply += inputLine;
}
in.close();
return reply;
}
catch(IOException e){
log.severe("Error at sendSMS(): " + e.getMessage());
return "IOException [" + receiver + ", " + sms + "]";
}
}
c. IOLogic.java
//INSERT DELIVERY REPORTS
public static void insDLR(DataSource d, int _smsID, String _smsText, String _dest, String _status, String _report){
try{
Connection con = null;
con = d.getConnection();
con.setAutoCommit(false);
PreparedStatement ps = con.prepareStatement("INSERT INTO sms_dlr_log(sms_id, sms_text, destination, dlr_status, dlr_report) VALUES(?,?,?,?,?)");
ps.setInt(1, _smsID);
ps.setString(2, _smsText);
ps.setString(3, _dest);
ps.setString(4, _status);
ps.setString(5, _report);
int u = ps.executeUpdate();
con.commit(); //explicit commit
con.setAutoCommit(true);
con.close();
}
catch(SQLException ex){
log.severe("Message:" + ex.getMessage() + ": Failed to log DLR");
}
catch(ClassCastException exx){
log.severe("Message:" + exx.getMessage() + ": Failed to log DLR");
}
}
Easy isnt it ??
Question: what if a Servlet wants to reply directly (instead of calling sendsms url) to the request and at the same time ask for a DLR ??
Answer (tongue in cheek): we need to employ x-kannel-headers
In ur doGet() method do the following
this.request = request;
this.response = response;
response.setContentType("text/html");
webapp = "http://" + request.getServerName() + ":" + request.getLocalPort() + request.getContextPath();
//We need to set accept-x-kannel-headers = true in kannel.conf
response.addHeader("X-Kannel-DLR-Mask", "31");
smsbody = request.getParameter("smsbody");
receiver = request.getParameter("receiver");
dlrurl = webapp + "/dlr?smsid=1&dest=" + receiver + "&type=%d&dlr=%A&localsmsid=%I&remotesmsid=%F&smsuser=%n";
response.addHeader("X-Kannel-DLR-Url", dlrurl);
this.out = response.getWriter();
//DO UR MAGIC HERE
...NOW U CAN HUG URSELF
You (name hidden) requested my assistance on doing DLR in kannel. There you are, kaz qwako
There are a few approaches but the following (and derivaties) has never let me down.
I dont know if its only me, but on kannel release 1.4.3 you are ONLY able to get DLR when u set dlr-mask to 31
The following approach (or similar) has never let me down...
a. kanne.conf
#TRIGGER
group = sms-service
keyword = trigger
aliases = triger
get-url = "http://192.168.1.45:8080/mwasima/trigger?keyword=%k&client=%s&service=%s&sender=%p&time=%t&dlrmask=31&dbtype=postgres&smsgateway=192.168.1.30"
omit-empty = true
and the following (proxy) function to push sms
b. IOLogic.java
public static String sendSMS(int smsid, String receiver, String sms, boolean flash, String webapp, String smsgateway){String clean_sms = sms.replace(" ","%20"); //we can do better than this !!!!!
String reply = "";
log.info("ATTEMPTING to send [" + sms + "] to " + receiver);
dlrurl = encodeURL(webapp + "/dlr?smsid=" + smsid + "&dest=" + receiver + "&type=%d&dlr=%A");
try{
URL send_sms_url = new URL("http://" + smsgateway + ":13013/cgi-bin/sendsms?username=" + ux +"&password=" + px +(flash==true?"&mclass=0":"")+ "&to=" + receiver +"&text=" + clean_sms + "&dlr-mask=" + dlrmask + "&dlr-url=" + dlrurl);
log.info("send sms url = " + send_sms_url);
URLConnection url_con = send_sms_url.openConnection();
BufferedReader in = new BufferedReader(new InputStreamReader(url_con.getInputStream()));
String inputLine;
while ((inputLine = in.readLine()) != null){
reply += inputLine;
}
in.close();
return reply;
}
catch(IOException e){
log.severe("Error at sendSMS(): " + e.getMessage());
return "IOException [" + receiver + ", " + sms + "]";
}
}
and the finaly the following function to persist DLR
//INSERT DELIVERY REPORTS
public static void insDLR(DataSource d, int _smsID, String _smsText, String _dest, String _status, String _report){
try{
Connection con = null;
con = d.getConnection();
con.setAutoCommit(false);
PreparedStatement ps = con.prepareStatement("INSERT INTO sms_dlr_log(sms_id, sms_text, destination, dlr_status, dlr_report) VALUES(?,?,?,?,?)");
ps.setInt(1, _smsID);
ps.setString(2, _smsText);
ps.setString(3, _dest);
ps.setString(4, _status);
ps.setString(5, _report);
int u = ps.executeUpdate();
con.commit(); //explicit commit
con.setAutoCommit(true);
con.close();
}
catch(SQLException ex){
log.severe("Message:" + ex.getMessage() + ": Failed to log DLR");
}
catch(ClassCastException exx){
log.severe("Message:" + exx.getMessage() + ": Failed to log DLR");
}
}
Easy isnt it ??
Question: what if a Servlet wants to reply directly (instead of calling sendsms url) to the request and at the same time ask for a DLR ??
Answer (tongue in cheek): we need to employ x-kannel-headers
In ur doGet() method do the following
public void doGet(HttpServletRequest request, HttpServletResponse response)throws IOException, ServletException {
this.request = request;
this.response = response;
response.setContentType("text/html");
webapp = "http://" + request.getServerName() + ":" + request.getLocalPort() + request.getContextPath();
//We need to set accept-x-kannel-headers = true in kannel.conf
response.addHeader("X-Kannel-DLR-Mask", "31");
smsbody = request.getParameter("smsbody");
receiver = request.getParameter("receiver");
dlrurl = webapp + "/dlr?smsid=1&dest=" + receiver + "&type=%d&dlr=%A&localsmsid=%I&remotesmsid=%F&smsuser=%n";
response.addHeader("X-Kannel-DLR-Url", dlrurl);
this.out = response.getWriter();
//DO UR MAGIC HERE
//then finaly send ur reply
out.println("UR REPLY HERE");
}...NOW U CAN HUG URSELF
Thursday, May 8, 2014
Django QuerySet Operators
Supports the following
exact
iexact
contains
icontains
in
gt
gte
lt
lte
startswith
istartswith
endswith
iendswith
range
year
month
day
week_day
isnull
search
regex
iregex
Example:
queryset = Tender.objects.filter(deadline__gt = datetime.datetime.now())
Wednesday, April 30, 2014
Gaffes
Since this blog is about my own experience while coding away my sanity, i think it would be fir enought to share my blunders too... not just wat works!!!!!!!!
In no particular order....
1. Strange PostgreSQL Error message
"ERROR: input of anonymous composite types is not implemented "
This is an error message i had never encountered before but , against my own advice, instead of backtracking and looking for documentation of the error or googling, i went straight ahead to my new ui code and tried to debug.... i wasted almost 8 man hours... a whole official day in other words.
Later on i discovered that i had forgoten to specify a datataype for a db column in the sql script !!!!!!!!
2. New Django Models 'Refusing' to appear on the Admin site
Was in 'debug' mode for about 2 days
Found out that i had forgoten to register the new Models at admin.py
Subscribe to:
Posts (Atom)