John Soer

Archive for June, 2009

It is this time of year again where the government wants a chunk of your hard earned money. Having had two bad experiences with accountants we decided this year to file the taxes our self again. The problem is that on our accountant recommendation we switched to QuickBooks which is not a program for people that have no clue about taxes. The problem is that I needed to get the info out of QuickBooks back into an Excel file QuickBooks provided numerous options to export data but has no option to export the General Journal entries.

The following code snippet will pull out all the General Journal entries out of QuickBooks

Initialize();
IJournalEntryQuery JournalEntryQuery = ivMsgSetRequest.AppendJournalEntryQueryRq();
JournalEntryQuery.IncludeLineItems.SetValue(true);
IMsgSetResponse msgSetRs = ivSessionManager.DoRequests(ivMsgSetRequest);
TearDownSession();

IResponseList responseList = msgSetRs.ResponseList;
if (responseList != null)
{
    IResponse response = responseList.GetAt(0);
    IJournalEntryRetList tvJournalEntryRetList = (IJournalEntryRetList)response.Detail;

    for (int i = 0; i < tvJournalEntryRetList.Count; i++)
    {
        IJournalEntryRet tvJournalEntryRet = tvJournalEntryRetList.GetAt(i);

        List<QBStruct_JournalLine> JournalEntries = new List<QBStruct_JournalLine>();
        for (int j = 0; j < tvJournalEntryRet.ORJournalLineList.Count; j++)
        {
            IORJournalLine tvORJournalLine = tvJournalEntryRet.ORJournalLineList.GetAt(j);
            QBStruct_JournalLine tvQBStruct_JournalLine = new QBStruct_JournalLine(tvORJournalLine);
            JournalEntries.Add(tvQBStruct_JournalLine);
        }

        DateTime TxnDate = tvJournalEntryRet.TxnDate.GetValue();
        int TxnNumber = tvJournalEntryRet.TxnNumber.GetValue();
        string RefNumber = tvJournalEntryRet.RefNumber.GetValue();
    }
}

If you leave in the Canada Ontario London area and file coperate taxes and treat your customers with respect leave a comment to gain a potential client.

It has been some time since I wrote a complex sql statement but I really like the beauty of this one

DECLARE @ListCol VARCHAR(2000)
DECLARE @Query VARCHAR(4000)  

--Format the data to get it in the correct syntax
WITH BaseData(SampleToResearchStudyID, Test, Value)
as
(
  --SQL statement to clean up the data
)

--Create a list of the columns that I want to use in the pivot
SELECT @ListCol = STUFF((
  select DISTINCT '],['+ test FROM BaseData
  FOR XML PATH('')),1,2,'') + ']'

--Define the pivot statement
SET @Query =
'
WITH BaseData(SampleToResearchStudyID, Test, Value)
as
(
  --SQL statement to clean up the data
)
Select
  SampleToResearchStudyID,
  ' + @ListCol + '
from
(select * from BaseData) as PivotData
Pivot
(
  MAX(Value)
  FOR Test in
  (' + @ListCol + ')
) as PivotResult
'

--Exec the statement
EXEC(@Query)

Generaly a pivot statement does not have to be this complex the CTE statements formats the data to be in the right format the user entered the data incorrectly the data was supposed to be entered as follows

but instead was entered as follows

I am not very happy that i have to declare the CTE twice once to get the column names and once to pivot the data, apperently it is not possible to reference an external CTE in a dynamic sql statement

The general idea is
1) @ListCol contains all the columns that you want to pivot on
2) @Query is a pivot statement using dynamic sql to inject the columns

When having to do group by operations on a date field that also contain time information the report will not be grouped by day but is grouped by the time instead, most likely the grouped report will contain the same number of rows as the raw data.

The following funtion returns the date portion of a datetime field

dateadd(dd,datediff(dd,0,PatientMedications.LastModified),0) as LastModified

E.g. if the value was 2008-03-02 14:28:17 it will return 2008-03-02. This will allow us to do a group by report on a per day basis.

In some case you may want to do a group by report on a date field grouped by week. This is a very common question asked in a data warehouse. In a datawarehouse we would have a date dimension which would contain all the week information. In our production database we do not really want to maintain a date dimension so that we can do a the odd group by query by week.

Here is where the datepart funtion comes in this changes all dates to the first date of the week.

DateAdd(day, -1 * datepart(dw, LastModified) + 1, LastModified)

E.g. if the date is 2009-Jun-09 (Tuesday) it will return 2009-Jun-07 (Sunday) which we can than use to create our group by report.

At times you want to trigger a server ontextchanged event from javascript. A typical example would be a javascript popul calendar, the data selection is done on the client by as soon as a date has been selected some server event should happen.

From javascript call this method
document.getElementById(‘TextBox’).onchange();

From asp.net

The two tricks are
1) Call onchange from javascript
2) Have AutoPostBack set to true on the asp.net textbox control