MySQL transpose function of aggregation

September 13th, 2016 No comments

An article published at the following providing a lot of examples.

At the stackoverflow website there is an example as below. It helps me a lot.

The following text from the site.


Here is what my current mysql table looks like:

PunchID EmpID PunchEvent PunchDateTime
1 0456 clockin 5/14/2013 8:36:26 AM
48 0456 breakout 5/14/2013 12:01:29 PM
53 0456 breakin 5/14/2013 12:28:31 PM
54 0456 clockout 5/14/2013 2:28:33 PM
57 0456 clockin 5/15/2013 7:38:34 AM
58 0456 breakout 5/15/2013 7:38:39 AM
59 0456 breakin 5/15/2013 7:38:41 AM
60 0456 clockout 5/15/2013 7:38:42 AM
Now I want to return a result set that is grouped based on the day of the week like so:

Day ClockIn BreakOut BreakIn ClockOut
Tuesday 8:36:26 AM 12:01:29 PM 12:28:31 PM 2:28:33 PM
Wednesday 7:38:34 AM etc, etc…
This is my current query. But it only returns the first punch for each day.


MAX(CASE WHEN PunchEvent = ‘ClockIn’ THEN DATE_FORMAT(PunchDateTime, ‘%r’) END) ClockIn,
MAX(CASE WHEN PunchEvent = ‘BreakOut’ THEN DATE_FORMAT(PunchDateTime, ‘%r’) END) BreakOut,
MAX(CASE WHEN PunchEvent = ‘BreakIn’ THEN DATE_FORMAT(PunchDateTime, ‘%r’) END) BreakIn,
MAX(CASE WHEN PunchEvent = ‘ClockOut’ THEN DATE_FORMAT(PunchDateTime, ‘%r’) END) ClockOut
FROM tableName
WHERE EmpID = 456
ORDER BY PunchDateTime
SQLFiddle Demo

║ Tuesday ║ 08:36:26 AM ║ 12:01:29 PM ║ 12:28:31 PM ║ 02:28:33 PM ║
║ Wednesday ║ 07:38:34 AM ║ 07:38:39 AM ║ 07:38:41 AM ║ 07:38:42 AM ║

Categories: MySQL Tags: , , , ,

Removing the password of an Excel VBA project

August 10th, 2016 No comments
' to remove the password of a excel with .xlam extension
' 1. use the Savexlam2xls subroutine to save a copy of xlam file to xls file
' 2. run the moveprotect subroutine to remove the password of the xls file
' 3. save as the xls file to xlam (vba addin)

' 把2007加载宏xlam文件修改成工作簿,再另存为2003格式工作簿可以用下面代码:
Sub Savexlam2xls()
    Dim strFile, wb As Workbook
    strFile = Application.GetOpenFilename(FileFilter:="Micrsofe Excel文件(*.xlam), *.xlam")
    If strFile = False Then Exit Sub
    With Workbooks.Open(strFile)
        .IsAddin = False
        .SaveAs FileName:=Replace(strFile, "xlam", "xls"), FileFormat:=xlExcel8
    End With
End Sub

Sub RemoveProtect()
    Dim FileName As String
    FileName = Application.GetOpenFilename("Excel文件(*.xls & *.xla),*.xls;*.xla", , "VBA破解")
    If FileName = CStr(False) Then
       Exit Sub
       VBAPassword FileName, False
    End If
End Sub

Sub SetProtect()
    Dim FileName As String
    FileName = Application.GetOpenFilename("Excel文件(*.xls & *.xla),*.xls;*.xla", , "VBA破解")
    If FileName = CStr(False) Then
       Exit Sub
       VBAPassword FileName, True
    End If
End Sub

Private Function VBAPassword(FileName As String, Optional Protect As Boolean = False)
      If Dir(FileName) = "" Then
         Exit Function
         FileCopy FileName, FileName & ".bak"
      End If

      Dim GetData As String * 5
      Open FileName For Binary As #1
      Dim CMGs As Long
      Dim DPBo As Long
      For i = 1 To LOF(1)
          Get #1, i, GetData
          If GetData = "CMG=""" Then CMGs = i
          If GetData = "[Host" Then DPBo = i - 2: Exit For
      If CMGs = 0 Then
         MsgBox "请先对VBA编码设置一个保护密码...", 32, "提示"
         Exit Function
      End If
      If Protect = False Then
         Dim St As String * 2
         Dim s20 As String * 1
         Get #1, CMGs - 2, St
         Get #1, DPBo + 16, s20
         For i = CMGs To DPBo Step 2
             Put #1, i, St
         If (DPBo - CMGs) Mod 2 <> 0 Then
            Put #1, DPBo + 1, s20
         End If
         MsgBox "文件解密成功......", 32, "提示"
         Dim MMs As String * 5
         MMs = "DPB="""
         Put #1, CMGs, MMs
         MsgBox "对文件特殊加密成功......", 32, "提示"
      End If
      Close #1
End Function
Categories: Excel, VBA/ASP Tags: , , ,

fix dovecot namespace error

March 15th, 2016 No comments

OS: Ubuntu 14.04 LTS

Dovecot: v2.2.9

Problem: after I ran a “apt-get upgrade” command, imap server refused to work. It was noticed by logging in to mail server using roundcubemail interface. It said that imap server cannot be connected.

Checking and testing:

view /var/log/mail.err and found the following message:

Mar 15 09:57:01 emerald dovecot: lmtp(3748, Error: user Initialization failed: namespace configuration error: inbox=yes namespace missing


Basically the latest version of Dovecot, its configuration is quite particular about namespaces, which it wasn’t in the past. Anyhow adding this to the dovecot.conf file help resolved the issue:

namespace inbox {
inbox = yes

Categories: Tips Tags: ,

Clean the boot partition of ubuntu OS

March 14th, 2016 No comments

Every time you installed a new Linux kernel, it left a footprint in the grub_config, making the boot menu longer each time. Furthermore, it clogs the boot partition of the Ubuntu system. When the boot partition is full, you will get trouble to boot the machine properly. Does Ubuntu provide any easier way to clean them up or keep them from showing in the boot list?

The following information is from the internet and works for me perfectly.

Open terminal and check your current kernel:

uname -r 


Next, type the command below to view/list all installed kernels on your system.

dpkg --list | grep linux-image 

Find all the kernels that lower than your current kernel. When you know which kernel to remove, continue below to remove it. Run the commands below to remove the kernel you selected.

sudo apt-get purge linux-image-x.x.x.x-generic 

Finally, run the commands below to update grub2

sudo update-grub2 

Reboot your system.

Categories: Software, Tips Tags: , , ,

Add a worksheet function to extract hyperlinks

September 28th, 2014 No comments

There is no worksheet function to extract hyperlink in a given cell. We can use VBA to create a function to do this. The simple function is listed here.

Function getlink(rng)
getlink = rng.Hyperlinks(1).Address
End Function

In your worksheet, you can simply use = getlink(A1) to get the hyperlink tied to the cell A1.


Categories: Excel, Tips, VBA/ASP Tags: , ,