CO5044 Continued Professional Development Level 2: Reflective Report

Reflection of achievements during work placement and project

 

For my project and work placement I did both in the same organisation and was based at the RECORD office in Chester Zoo, working on the programming development of a statistical analysis package for the enhancement and augmentation of the RECORD web site data portal.

 

Andrew Jones

4/26/2010

 

Tutor: Tricia Kosuth-Phillips


 

Table of Contents

Project Plan. 5

Introduction. 5

Project Goals. 5

Executive Summary. 5

Scope. 5

General Project Information. 5

Problem/Opportunity Statement 5

Project Objectives. 6

Project Description. 6

System boundary: 6

Business Benefits: 6

Estimated Project Duration: 6

Feasibility. 6

Resource requirement 7

Project Deliverables. 7

In Analysis. 7

In Design. 7

§        Data structure. 7

§        Statistical pack. 7

§        Classification queries. 7

In Implementation. 7

Project Schedule. 8

Estimated Project Timeline. 8

Actual Timeline. 9

Supporting Plans. 10

Human Resource Plan. 10

Communications Plan. 10

Risk Management Plan. 11

Research. 11

Question 1. 11

Question 2. 12

Question 3. 12

Question 5. 13

Conclusion. 13

Learning Agreement 14

Overview of Placement 16

Overview of Project 16

Introduction. 16

Benefit to the Organisation. 16

Reflection on Learning Targets. 17

Learning Target 1. 17

Through my project I will improve my mathematical knowledge by means of statistical models, charting and graphs; together with the development of my programming skills in a new language C#. 17

Learning Target 2. 18

Though my project I develop my skills in learning and applying MS SQL, jQuery and C# through the development of the statistical analysis package for the RECORD website. 18

Learning Target 3. 19

Through my project I will improve my knowledge and skills of utilising databases, MS SQL and queries, jQuery to ensure that the statistical analysis package of the RECORD web site is successfully developed. 19

Learning Target 4. 20

Through my project I will improve my verbal communication skills, by ensuring that my view and comments are successfully conveyed to the project team. 20

Learning Target 5. 21

Through my project I will build my team working skills by ensuring that I make significant and timely contributions to the development of the RECORD statistical analysis package. 21

Evaluation of placement and project 21

Reflective Diary. 23

Timesheets. 28

Evidence of Learning Target 1. 34

Through my project I will improve my mathematical knowledge by means of statistical models, charting and graphs; together with the development of my programming skills in a new language C#. 34

ChartData.cs. 34

getTaxonNameClassification.cs. 36

Taxon.cs. 41

TaxonClassification.cs. 41

TaxonDictionary.cs. 43

TaxonHierarchy.cs. 44

nbnTaxonGroupChart.aspx.cs. 45

TaxonGroupChart.aspx.cs. 48

SpeciesByYear.aspx.cs. 50

Evidence of Learning Target 2. 61

Though my project I develop my skills in learning and applying MS SQL, jQuery and C# through the development of the statistical analysis package for the RECORD website. 61

SQL Statements. 61

jQuery. 63

Further C# examples. 65

Evidence of Learning Target 4. 82

Through my project I will improve my verbal communication skills, by ensuring that my view and comments are successfully conveyed to the project team. 82

Bibliography. 83

 


 

Project Plan

Introduction

This document aims to present the: project goals, scope, project deliverables, project schedule and supporting plans for the project. It will consider the feasibility of the project as a whole and requirements necessary for its successful completion.

Project Goals

Executive Summary

RECORD is the local biological record centre serving Cheshire, Halton, Warrington and Wirral – ‘The Cheshire region’. The centre provides a local facility for the storage; validation and usage of the Cheshire based biological data under the Nation Biodiversity Network (NBN) project.

RECORD aims to record and investigate all the different species of wildlife in the Cheshire region and provide the public with the help they need to collate and log information.

RECORD is currently in the process of developing a Data Portal, this web based package that will allow users to access RECORD data from their own personal computers. The project is to work on the existing datasets to provide a statistical analysis of geographic and taxonomic species related data. This project is known as the Data Portal Statistical Enhancements project.

Scope

General Project Information

Project name:                  RECORD Data Portal Statistical Enhancements

Funder/sponsor:              Luck Lunla, RECORD

Project Manager:             Luck Lunla

Problem/Opportunity Statement

RECORD is currently in the process of developing a data portal. This web based package will allow users to access RECORD data from their own personal computers. The focus is on geographical and taxonomic statistics utilising the current datasets.

Project Objectives

To facilitate provide a graphed and statistical analysis for species selected via RECORD Data Portal querying from the RECORD database.

Project Description

A new information system will be constructed that will enable the graphical representation of statistical data from the RECORD database.

System boundary:

§  Database structure

Business Benefits:

Improve understanding of the data and statistical analysis available to user groups.

Estimated Project Duration:

Feasibility

The project as outlined is feasible.

Resource requirement

Resources for the project include:

Project Deliverables

The following tasks and deliverables reflect the current understanding of the project:

In Analysis

In Design

In Implementation

The project will implement a Data Portal tab for statistical enhancements which facilitates a statistical analysis of the taxonomical species.

Statistical models are yet to be defined.

Project Schedule

Estimated Project Timeline

This timeline has been developed to show what the expected tasks and their respective durations. It outlines what I think the project requires in task terms and a best guess estimate on the length of time that a task may take. This gantt chart has been constructed based solely on my perceptions of how the project will be planned and is fully expected to change based on more information and the development of the project.

Actual Timeline

This is the actual analysis of tasks carried out in the completion of the project, although it deals with actual task and time taken to complete them, it bears little or no resemblance to the planned gantt chart which was constructed on headings of expected activity and their estimated durations. There was a minor over-run in time between planned and actual, but the most significant differences are the tasks and the areas that these tasks could be grouped. The planned gantt chart deals with area headings, which do not fit neatly into the what actually happened gantt chart, although overall the project achieved the goals set out for it how it got there was significant different from the planned gantt chart.

Supporting Plans

Human Resource Plan

Name

Position

Role within the Project

Andrew Jones

Project Worker

Main worker on the project

Luck Lunla

Project Manager and IT officer

Project Manager and resource co-ordinator

Hannah Wood

IT assistant

Resources management

Eric Fletcher

Manager

 

Communications Plan

Communications for the project are relatively simple close contact is kept Luck Lunla to ensure that the project is progressing as to his wants and needs. Other areas of communication include:

§  Learning Agreement

§  Interviews

§  Meetings

§  Terms of reference

§  SCOPE document

§  e-mail

Name

Position

Contact Information

Andrew Jones

Project Worker

00e5@west-cheshire.ac.uk

Luck Lunla

Project Manager and IT officer

luck@record-lrc.co.uk

Hannah Wood

IT assistant

hannah@record-lrc.co.uk

Eric Fletcher

Manager

eric@record-lrc.co.uk

Risk Management Plan

Risk

Contingency

·         Just because Data Portal Statistical Enhancement can be used does not mean RECORD will find it useful.

·         Carry out research to ensure that the proposed developments are what the key or primary users of the data portal want

·         The types of analysis and the mix of chart types

·         Ensure that the analysis is what is required by RECORD and that there are a mix of chart types

·         Weather

·         Ensure that I will be able to walk to the RECORD office

·         Complex classification system

·         Ensure that the presentation of information simplifies the complexities of the classification system

·         Links to the NBN data system

·         Ensure that the external links to the NBN data system are as speedy as possible

·         Licensing graph tools

·         The graphing tool for use with ASP.NET has to be free

·         Poor goal setting and a lack of clarity in project management

·         Ensure that I understand what is expected of me and when it is to be delivered

Research

Question 1

Chart 1 How often do you use RECORD's enquiry service

Question 2

Chart 2 Would a statistical presentation of data be helpful

Question 3

Chart 3 What statistics would you like to see presented

Question 5

Chart 4 What form would you like to see the statistical analysis presented in

Conclusion

In brief the project is both feasible and achievable. The research carried out supports a user demand for the proposed Data Portal Statistical Enchantments. Meeting the requirements of RECORD will prove to be equally academically challenging and personally developmental.


 

Learning Agreement

Description: See full size image

Continual Professional Development

Learning Agreement

(Summarises what the student aims to learn in the placement)

NAME OF STUDENT:

Andrew Jones

NAME OF ORGANISATION:

RECORD

ADDRESS OF PLACEMENT:

RECORD, Oakfield House, Chester Zoological Gardens, Upton, CHESTER, CH2 1LH

Tel:         01244 383749

Fax:         01244 383749

ADDRESS OF PROJECT:

RECORD, Oakfield House, Chester Zoological Gardens, Upton, CHESTER, CH2 1LH

Tel:         01244 383749

Fax:         01244 383749

WORKPLACE/PROJECT MENTOR:

Luck Lunla

CONTACT DETAILS:

RECORD, Oakfield House, Chester Zoological Gardens, Upton, CHESTER, CH2 1LH

Tel:         01244 383749

Fax:         01244 383749

WCC  CPD TUTOR:

 

DATES OF PLACEMENT:

FROM: 02/11/09                    TO: 16/04/2010

DATES OF PROJECT:

FROM: 02/11/09                    TO: 16/04/2010

BRIEF DESCRIPTION OF PLACEMENT:

 

To work on the Data Portal Statistical Enhancements project.

 

BRIEF DESCRIPTION OF PROJECT:

 

To implement a statistical analysis tab for the RECORD data portal.

 

PROVISIONAL LEARNING TARGETS

 

 In the section below identify a total of FIVE learning targets to be achieved during the placement and project,

·          Two Interpersonal targets (Project Based)

·         Two Practical targets (Project Based)

·          One may be from either category (this must be a work based skill that is developed during your placement if undertaken within a different organisation from your project

 It is important that these have been negotiated with and agreed by your placement provider and your

 CPD tutor and that they sign to indicate this.

a)              Practical skills and abilities including, where appropriate, the application of academic subject skills/knowledge.

1. Through my project I will improve my mathematical knowledge by means of statistical models, charting and graphs; together with the development of my programming skills in a new language C#.

2. Though my project I develop my skills in learning and applying MS SQL, jQuery and C# through the development of the statistical analysis package for the RECORD website.

3. Through my project I will improve my knowledge and skills of utilising databases, MS SQL and queries, jQuery to ensure that the statistical analysis package of the RECORD web site is successfully developed.

b)    Interpersonal/ transferable skills.

1. Through my project I will improve my verbal communication skills, by ensuring that my view and comments are successfully conveyed to the project team.

2. Through my project I will build my team working skills by ensuring that I make significant and timely contributions to the development of the RECORD statistical analysis package.

 

Signature of CPD tutor:        …………………………………………………………………………..………………...

(to be obtained during Support Programme)

 

Signature of placement provider:         ……………………………………………………………..…………………….

(to be obtained on commencement of placement)

 

If you modify your learning targets during the placement, note these above and agree them with your CPD tutor and your placement provider.  These modifications are likely to occur once you commence the placement and realise that its learning potential is either greater, lesser or different from what you had anticipated.  Modifications may also occur at any stage during the placement.

ADDITIONAL INFORMATION

1.              I am familiar with the Health and Safety regulations of the organisation.

2.              I have satisfied myself that I have adequate personal accident insurance.

Signed:    …………………………………………………………

To be completed at the request of the placement provider if there is an issue concerning confidentiality.  The student, CPD tutor and any other parties who may be privy to confidential information should sign the following declaration:

Unless required by law, you should not disclose to any person without the express permission of the placement provider, any confidential information contained in your written report.

Signature:

 

 

Name:

 

 

 


Overview of Placement

For my work placement I worked at the RECORD office based in Chester Zoo, on the development of Data Portal Statistical Enhancements project. The project was entirely carried out on a computer in the RECORD office using Microsoft Visual Studio 2008 and will take place between November 2009 and the end of March 2010. My placement time was solely devoted to the project which was managed by Luck Lunla.

Overview of Project

My project was to deliver the Data Portal Statistical Enhancements for the RECORD web site. This was, put simply, to develop a number of graphs for the presentation of information based on the users search. As the user’s searched for species sightings they generate lists of information about the various species they are interested in, this information was taken and broken down into the charts; two of the charts deal with the taxonomical classification of species and the third deals with a comparison of the species by year. In addition to the creation of these a charts, a user tab and interface was developed for the web site together with supporting downloadable files.

Introduction

In this section of the report I will be reflecting on my project and work placement together with assessing the degree of success or failure to meet my learning targets which are split into two distinct areas: three practical and two interpersonal targets.

My work placement and project took approximately 211 hours, which was 61 hours over the required 150 hours, but more importantly the project only over ran the projected timeline by 31 hours, which was due to the complexity of the tasks required to complete the project.

Benefit to the Organisation

The benefits of the project to RECORD was the implementation of these Data Portal Statistical Enhancements, this would provide RECORD with a nationally unique extension to an already impressive technological lead over other local species recording offices. The project is one of RECORD’s strategic development goals for IT.

Reflection on Learning Targets

Learning Target 1

Through my project I will improve my mathematical knowledge by means of statistical models, charting and graphs; together with the development of my programming skills in a new language C#.

Reflection

Looking at this learning target, it can be broken down into two parts: improve my mathematical knowledge and learning a computer programming language C#.

Looking at the improving my mathematical knowledge I do not feel that I have significantly expanded my basic knowledge through the project, this is because the charts we required for the project were not particularly complex in terms of graph and chart theory, although they were complex in programming terms. My main learning under the mathematics heading was in programming logic, which has improved significantly through the project.

Looking at the second element: learning the programming language C#, I feel that I have made excellent progress, although I still have an enormous amount to learn, and would not consider myself proficient by any stretch of the imagination, even though the evidence detailed in Evidence of Learning Target 1, which shows the completed project sections of code may appear to contradict my proficiency. My learning of this language consisted of internet research, the Microsoft Visual Studio 2008 application and guidance from Luck. The research gave me examples of how others had approached similar problems and the application gave me the correct syntax structure. Luck was extremely helpful when I was at a complete loss of how to proceed, by showing me either different internet examples or highlighting a different approach to the problem (Berlo, 1960).

I would have to say that I am content with the level of feedback and support I received throughout the project. However, I am a little bit less pleased with the process and project management, because at times I felt lost and without a clear understanding of what we were trying to achieve. I still, with the benefit of hindsight, am uncertain about how some of the project goals were achieved, only that I achieved them! (Maslow, 1943)

Justification

Overall I think I did a good job! Even though programming in any computer language is similar and the C# language has a lot in common with Java, which we study at college, it was a completely new language structure and syntax for me to learn and I am proud of the results I have accomplished.

If I ask myself would I do the project in the same way again or approach it differently? I have to say that I would approach it differently. I would put a lot more effort into learning the syntax and language constructs of C# even though I do not want to be a programmer; I would also put more effort into the graph and charting theory besides it not being required.

If I had the opportunity to do it again would I? And, the answer to that is no, although I have learnt a new programming language and achieved the project goals, the time I spent totally out of my depth together with a lack of understanding in where “we” were going have left me thankful that the experience is over.

The project was developed in a number of small steps, with the theory being that, I feel, the less about the whole I knew the better it was for me, this is not how I like to be managed, I prefer to know the whole picture and then look at the individual tasks necessary to complete the project. (Maslow, 1943)

Learning Target 2

Though my project I develop my skills in learning and applying MS SQL, jQuery and C# through the development of the statistical analysis package for the RECORD website.

Reflection

This learning target again can be broken down into constituent parts: learning and applying MS SQL, jQuery and C#. Taking them as a whole I was very happy with the feedback, support and project management of this learning target, as these three areas where more in m y comfort zone and fits neatly with my activists learning style (Honey & Mumford, 2000) where I learn through a combination of trial and error as I experience these new programming languages.

Overall I feel that I did a good job in achieving this learning target, as evidenced in Evidence of Learning Target 2, where the queries, jQuery code and further examples of C# code are detailed.

Justification

In my approach to achieving this learning target I would, if given the opportunity, tackle it in the same way. I was much more content with the tasks associated with this learning target as it fits neatly into my learning styles: Mathematical/Logical and Naturalistic (Gardner, 1993)

In accomplishing this learning target I had a natural affinity to the tasks required and I took to these tasks with relish. My feeling is that I attained success in this learning target because it fitted so neatly with my learning styles, although I have to note that my learning styles, particularly the three senses style (Rose, 1987) have changed significantly through the duration of the project, from a very evenly balanced style to significantly visual learning orientated style.

Learning Target 3

Through my project I will improve my knowledge and skills of utilising databases, MS SQL and queries, jQuery to ensure that the statistical analysis package of the RECORD web site is successfully developed.

Reflection

I was happy with the way in which I was provided with feedback together with the level of support I received in the realization of this learning target. Although I did not utilise the database as I hoped to: designing and creating, the other elements of this learning target where fully met: see Evidence of Learning Target 2. The process of creating and implementing these elements was ok; the project management of this section of work was not within my comfort zone as I have explained in Learning Target 1.

I feel that I did a good job in achieving this learning target as in the main I did not require any help to develop the MS SQL queries or the jQuery code.

If I had the opportunity to do it again would I? The answer to that question is yes! I thoroughly enjoyed implementing my knowledge, Evidence of Learning Target 2, as well as extending and developing my learning.

Justification

Because my approach to attaining this learning target suited my learning styles and the targets themselves felt comfortably reachable I would adopt the same approach if I were to embark upon these tasks again.

My methodology for accomplishing this learning target as with the other learning targets was dictated to by the language structure and syntax of MS SQL and jQuery, which either worked or it didn’t, this again is a significantly preferred way of my learning style; from a personal perspective the lack of ambiguity is enormously comforting.

Learning Target 4

Through my project I will improve my verbal communication skills, by ensuring that my view and comments are successfully conveyed to the project team.

Reflection

I would approach this learning target differently if I were to attempt the project again; from the onset I would be more confident in both my position and my ability. This lack of confidence is what I primarily wished to overcome by improving my communication

My approach to this leaning target changed as my confidence grew; this in no small part was due to Lucks’ very understanding attitude. Although this is not really reflected in evidence as the communications process was very informal conversations across the desk, I have included a copy of e-mails sent and received during one of Luck’s absences from the office.

Justification

The feedback and support I received throughout the project was more than satisfactory, however the process and project management were not within my comfort zone. My aim with this learning target was to improve my verbal communication which I feel that I achieved as, although there was no project team – only me and the programme lead Luck, I feel that I conveyed how I was feeling about the project development thoroughly (Berlo, 1960)

I feel that I effectively communicated my needs, verbally, to Luck throughout the project, as familiarity grew and my sense of jeopardy faded the fear of failure and being fired from the project, I feel that my communications effectiveness grew (Berlo, 1960).

Learning Target 5

Through my project I will build my team working skills by ensuring that I make significant and timely contributions to the development of the RECORD statistical analysis package.

Reflection

Although I was very pleased with the feedback and support I received throughout the project, I feel that my needs were not met for process and project management (Maslow, 1943)

Even though there was no team as such, only me, I worked well as the whole team, according to (Belbin, 1970) my preferred team roles are those of plant, shaper and resource investigator. Through the project I had to adopt, with the exception of chairman, all of the other roles to ensure that the project was completed successfully.

Justification

If I were to approach the project again I would almost certainly set about the project in the same way; although if I had the opportunity to do it again I wouldn’t.

Overall I feel that my approach to the project was stereotypical as my entire raison d'etre can be clinically classified into predictable behaviours.

Evaluation of placement and project

Taken as a whole, the project and work placement was way beyond my expectation in terms of complexity. The placement element was very much as expected, working alone on a project in an amiable working environment. My expectation of the project was nowhere near: the project was far more challenging that I thought it was going to be, and I originally thought it was going to be complex and challenging. My major dissatisfaction was with the process and project management of the project. I like to know about the whole picture and then the individual tasks necessary to achieve these overall goals. Luck’s management style was very different with me being given elements of the project to complete without understanding how these rudiments fitted together into the completed projects.

Even though I did not fully comprehend how the project was going to come together I did a proficient job of the fundamental constituent parts of the project. What surprised me most of all is the development of my C# programming skills, they have gone from nothing to the completed project, see Evidence of Learning Target 1 and further examples of C# in Evidence of Learning Target 2.

If I were, and it would be with great reluctance, to approach the project again I would be more confident in my abilities to learn and produce completed code; ensuring that the project management and process functions better suited my needs (Maslow, 1943).

Having completed the project my view has changed significantly: from one of I cannot do that to one that I can do that. This is in no small part due to Luck’s guidance when I was despondent with the whole project.

I achieved my goals, in the main, by completing the project that produces the strategic requirement of RECORDs Data Portal Statistical Enhancements project.

Throughout the project I learn a great deal of ASP coding which was not part of my learning targets but essential to the successful completion of the project.

By and large the balance of learning targets was slewed in favour of C# with the other targets being achieved but not with the overkill of the C# elements. I particularly enjoyed the MS SQL and the MS Chart building components of the project as they were, I found, easy to learn and in line with my expectations of the project from the onset. There were many occasions in the project were I felt completely and utterly out of my depth when developing the C# code.

Other than being out of my depth and way beyond my comfort zone there were no significant problems that we were unable to solve. I feel that I coped satisfactorily with the project challenges, even if I could have been better prepared. There was a tremendous amount of information to absorb and most of it highly complex in nature.

The project has given me an enormous insight into how complex something as simple as a click on a web site link to generate a chart actually is. I learnt how to programme in C#, write jQuery code, create MS SQL queries and develop ASP pages; together with developing my verbal communication skills and my ability to work as part of a team.


 

Reflective Diary

03/11/2009

  • Downloaded MS Charting application for ASP.NET and other associated applications and help files
  • Installed and configured MS Charting application and associated applications and help files
  • Updated the .NET framework to 3.5 service pack 1
  • Familiarised myself with the MS SQL database structure and investigated queries

06/11/2009

  • Designing queries for the selection of species and its’ parent
  • Wrote a C# method to connect to the database
  • Wrote a C# method to query the database for the species and parent

10/11/2009

  • Wrote C# method to query the database for the top of the classification tree
  • Created a C# class for the return of the top of the classification tree
  • Implemented testing code for the return of the top of the classification tree

13/11/2009

  • Created session variables for use in the statistics tab
  • Created a statistics tab link

17/11/2009

  • Formatted the statistics tab link
  • Researched JQuery methods, application, selectors and attributes

20/11/2009

  • Created a chart only page

24/11/2009

  • Implemented JQuery script to load chart page
  • Used AJAX methods to ensure page loading was successful

27/11/2009

  • Debugged SQL queries to make the searching faster
  • Added additional links to the statistics tab for other charts

01/12/2009

  • Planned the species by year chart

04/12/2009

  • Created data dictionaries for the creation of the species by year chart

08/12/2009

  • Created a design layout for the species by year chart
  • Created code for the group by year element of the chart

11/12/2009

  • Worked on the species by year chart to ensure that both record and NBN datasets are represented correctly

05/12/2010

  • Worked on the species by year chart to ensure that both record and NBN datasets are represented correctly

12/01/2010

  • Worked on the species by year chart to ensure that both record and NBN datasets are represented correctly

15/01/2010

  • Made formatting changes to the species by year chart, including: font styles, legends, x axis intervals and titles

19/01/2010

  • Made changes to the code for the species by year chart to ensure that the chart groups by record and NBN datasets
  • Commented code
  • Formatted the taxonomical classification chart with layout changes, font styles and titles
  • Changed the size of the taxonomical classification chart

22/01/2010

  • Researched the predicate function in C#
  • Researched the use of data Dictionaries and List objects in C#

26/01/2010

  • Researched the .load() and .ajaxStop() functions for use in the JQuery loading of the charts
  • Created ajax and JQuery functions for loading the charts

29/01/2010

  • Modified the  ajax and JQuery functions to load the taxonomical chart

09/02/2010

  • Created a class for the species by year chart
  • Debugged the species by year chart loading
  • Created a method in the chart data class for the species by year chart

12/02/2010

  • Debugged the species by year chart not displaying data correctly

23/02/2010

  • Updated the code to Luck’s version of the coded project
  • Re-worked the loading of the charts in the JQuery loader

26/02/2010

  • Continued to re-work the JQuery code for loading the charts
  • Created a new project and three new classes to update an access table with data from the main database
  • Started to investigate ways of using the newly created table to speed up the loading of the taxonomical charts

02/03/2010

  • Debugged project
  • Created a new application project for updating Taxon Hierarchy table
  • Debugged the application

09/03/2010

  • Debugged the search page to resolve NBN connection errors

12/03/2010

  • Continued debugging the search page
  • Created a new database for species designation

16/03/2010

  • Created a web application for the creation of a species designation table

19/03/2010

  • Ran the application for the designation of species to create tables for the designation of species lists
  • Created MS Access database for the designation of species
  • Created queries for the retrieval of species list

23/03/2010

  • Created data dictionaries for the designation of species
  • Created MS SQL group by query for the selection of species

26/03/2010

  • Debugged the selection of species by species list application

30/03/2010

  • Completed the debugging of the species selection list
  • Created a downloadable CSV file for the species selection list
  • Created hyperlinks to the CSV download file

13/04/2010

  • Completed debugging the species designation application
  • Completed formatting changes to the application web pages

 

Timesheets




 


 


 


 

Evidence of Learning Target 1

Through my project I will improve my mathematical knowledge by means of statistical models, charting and graphs; together with the development of my programming skills in a new language C#.

The evidence contatianed in this section is made up of C# code. It is this code that displays the three charts and the statistics tab. The process is one of creating a taxon dictionary which is used with the classes taxon hierarchy and taxon classification to get the taxon name classification and presents this to the chart data class. The output of which is the used by the three chart generators: taxon group chart, nbn taxon group chart and species by year chart.

ChartData.cs

using System;

using System.Data;

using System.Configuration;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Collections.Generic;

 

/// <summary>

/// Summary description for ChartData

/// </summary>

public class ChartData

{

    public String Species;

    public int Year;

    public int Count;

 

    public Dictionary<String, int> TaxonGroupChartData = new Dictionary<string, int>();

    public Dictionary<String, List<ChartData>> TaxonBarChartData = new Dictionary<string, List<ChartData>>();

    //public Dictionary<String, int> recordTaxonGroupChartData = new Dictionary<string, int>();

    //public Dictionary<String, int> nbnTaxonGroupChartData = new Dictionary<string, int>();

 

    public ChartData()

    {

    }

    public ChartData(String Species, int Year, int Count)

    {

        this.Species = Species;

        this.Year = Year;

        this.Count = Count;

    }

    //create the data for the record data set in the Taxon Classification pie chart

    public void addTaxonGroupChartData(String TaxonName, int Count)

    {

        if (TaxonGroupChartData.ContainsKey(TaxonName))

        {

            TaxonGroupChartData[TaxonName] += Count;

        }

        else

        {

            TaxonGroupChartData.Add(TaxonName, Count);

        }

    }

 

    //create the data set for the species by year chart

    public void addTaxonBarChartData(String TaxonName, int Year, int Count)

    {

        ChartData chartData = new ChartData(TaxonName, Year, Count);

        //create list to support chartData and add data to the list

        List<ChartData> listChartData = new List<ChartData>();

        listChartData.Add(chartData);

        // Increasing count if same species and year

        if (TaxonBarChartData.ContainsKey(TaxonName))

        {

            // If same year same species then increase

            if (TaxonBarChartData[TaxonName].Exists(

                    delegate(ChartData data)

                    {

                        if (data.Species == TaxonName && data.Year == Year) return true;

                        return false;

                    }

                ))

            {

                int MatchingYear = Year;

                int indexValue = TaxonBarChartData[TaxonName].FindIndex(

                    delegate(ChartData ifYearExists)

                    {

                        return ifYearExists.Year == MatchingYear;

                    });

 

                TaxonBarChartData[TaxonName][indexValue].Count += Count;

            }

            else // Add

            {

                TaxonBarChartData[TaxonName].Add(chartData);

            }

        }

        else

        {

            TaxonBarChartData.Add(TaxonName, listChartData);

        }

 

    }

 

 

    // Explicit predicate delegate.

    private static bool FindYear(ChartData yearExists, int matchingYear)

    {

 

        if (yearExists.Year == matchingYear)

        {

            return true;

        }

        {

            return false;

        }

 

 

 

 

    }

 

 

    //create the data for the nbn data set in the Taxon Classification pie chart

    //public void addNBNTaxonGroupChartData(String TaxonName, int Count)

   //{

      //  if (nbnTaxonGroupChartData.ContainsKey(TaxonName))

      //  {

      //      nbnTaxonGroupChartData[TaxonName] += Count;

      //  }

      // else

       // {

       //     nbnTaxonGroupChartData.Add(TaxonName, Count);

       // }

   // }

 

 

}

getTaxonNameClassification.cs

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

using System.Web.Configuration;

 

/// <summary>

/// Summary description for getTaxonNameClassification

/// </summary>

public class getTaxonNameClassification

{

    public string getTaxonName;

    private string taxonName;

    private string hierarchyClassification;

    private string parentName;

    private string parentNameClassification;

 

 

    public getTaxonNameClassification(string species, TaxonClassification taxonClassification)

    {

        getTheTaxonNameClassification(species, taxonClassification);

    }

    private void getTheTaxonNameClassification(string species, TaxonClassification taxonClassification)

    {

        bool hasRows;

 

        SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["main"].ConnectionString);

        try

        {

            //Database connection string

 

            conn.Open();

        }

        catch (Exception Ex)

        {

            // Try to close the connection

            if (conn != null)

                conn.Dispose();

 

            // Create a (useful) error message

            string ErrorMessage = "A error occurred while trying to connect to the server.";

            ErrorMessage += Environment.NewLine;

            ErrorMessage += Environment.NewLine;

            ErrorMessage += Ex.Message;

            Console.Beep();

            // Show error message (this = the parent Form object)

            //MessageBox.Show(ErrorMessage, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);

            System.Diagnostics.Debug.Print("Connection error");

            // Stop here

            return;

        }

 

        using (SqlCommand cmd = conn.CreateCommand())

        {

            cmd.CommandTimeout = 30000;

          

            cmd.CommandText = "SELECT dbo.TAXON.ITEM_NAME AS [Taxon Name], dbo.TAXON_LIST_ITEM.PREFERRED_NAME, dbo.TAXON_LIST_ITEM.PARENT, dbo.TAXON_RANK.TAXON_RANK_KEY, dbo.TAXON_RANK.LONG_NAME AS [Hierarchy Classification], TAXON_1.ITEM_NAME AS [Parent Name], dbo.TAXON_LIST.PREFERRED, dbo.TAXON_LIST.ITEM_NAME AS [List Name], TAXON_RANK_1.LONG_NAME AS [Parent Name Classification], TAXON_RANK_1.TAXON_RANK_KEY AS [Parent Taxon Rank] FROM dbo.TAXON_LIST INNER JOIN dbo.TAXON_LIST_VERSION INNER JOIN  dbo.INDEX_TAXON_NAME ON dbo.TAXON_LIST_VERSION.TAXON_LIST_VERSION_KEY = dbo.INDEX_TAXON_NAME.TAXON_LIST_VERSION_KEY ON dbo.TAXON_LIST.TAXON_LIST_KEY = dbo.TAXON_LIST_VERSION.TAXON_LIST_KEY INNER JOIN dbo.TAXON_LIST_ITEM INNER JOIN dbo.TAXON_VERSION ON dbo.TAXON_LIST_ITEM.TAXON_VERSION_KEY = dbo.TAXON_VERSION.TAXON_VERSION_KEY INNER JOIN dbo.TAXON_RANK ON dbo.TAXON_LIST_ITEM.TAXON_RANK_KEY = dbo.TAXON_RANK.TAXON_RANK_KEY INNER JOIN dbo.TAXON_LIST_ITEM AS TAXON_LIST_ITEM_1 ON dbo.TAXON_LIST_ITEM.PARENT = TAXON_LIST_ITEM_1.TAXON_LIST_ITEM_KEY INNER JOIN dbo.TAXON_VERSION AS TAXON_VERSION_1 ON TAXON_LIST_ITEM_1.TAXON_VERSION_KEY = TAXON_VERSION_1.TAXON_VERSION_KEY INNER JOIN dbo.TAXON AS TAXON_1 ON TAXON_VERSION_1.TAXON_KEY = TAXON_1.TAXON_KEY ON dbo.INDEX_TAXON_NAME.TAXON_LIST_VERSION_KEY = dbo.TAXON_LIST_ITEM.TAXON_LIST_VERSION_KEY AND dbo.TAXON_LIST_VERSION.TAXON_LIST_VERSION_KEY = dbo.TAXON_LIST_ITEM.TAXON_LIST_VERSION_KEY INNER JOIN dbo.TAXON_RANK AS TAXON_RANK_1 ON TAXON_LIST_ITEM_1.TAXON_RANK_KEY = TAXON_RANK_1.TAXON_RANK_KEY RIGHT OUTER JOIN dbo.TAXON INNER JOIN dbo.TAXON_NAME_TYPE ON dbo.TAXON.TAXON_NAME_TYPE_KEY = dbo.TAXON_NAME_TYPE.TAXON_NAME_TYPE_KEY ON dbo.TAXON_VERSION.TAXON_KEY = dbo.TAXON.TAXON_KEY WHERE (dbo.TAXON.ITEM_NAME = \'" + species.Replace("'", "''") + "\') AND (dbo.TAXON_LIST.PREFERRED = 1)";

 

            SqlDataReader reader = cmd.ExecuteReader();

 

            hierarchyClassification = "";

            parentName = "";

            taxonName = "";

            parentNameClassification = "";

 

            hasRows = reader.HasRows;

 

            if (reader.HasRows == true)

            {

                reader.Read();

 

                hierarchyClassification = reader["Hierarchy Classification"].ToString();

                parentName = reader["Parent Name"].ToString();

                taxonName = reader["Taxon Name"].ToString();

                getTaxonName = parentName;

                parentNameClassification = reader["Parent Name Classification"].ToString();

 

                reader.Close();

            }

        }

        conn.Close();

 

        if (hasRows == false)

        {

            getTheNonPreferedTaxonNameClassification(species, taxonClassification);

        }

       

        if (parentNameClassification == taxonClassification.ToString())

        {

            getTaxonName = parentName;

            return;

        }

       

        if (hierarchyClassification != taxonClassification.ToString() && hierarchyClassification != "")

        {

            getTheTaxonNameClassification(parentName, taxonClassification);

        }

        else

        {

            return;

        }

 

    }

 

    private void getTheNonPreferedTaxonNameClassification(string species, TaxonClassification taxonClassification)

    {

        bool hasRows;

 

        SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["main"].ConnectionString);

        try

        {

            //Database connection string

 

            conn.Open();

        }

        catch (Exception Ex)

        {

            // Try to close the connection

            if (conn != null)

                conn.Dispose();

 

            // Create a (useful) error message

            string ErrorMessage = "A error occurred while trying to connect to the server.";

            ErrorMessage += Environment.NewLine;

            ErrorMessage += Environment.NewLine;

            ErrorMessage += Ex.Message;

            Console.Beep();

            // Show error message (this = the parent Form object)

            //MessageBox.Show(ErrorMessage, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);

            System.Diagnostics.Debug.Print("Connection error");

 

            // Stop here

            return;

        }

 

        using (SqlCommand cmd = conn.CreateCommand())

        {

            cmd.CommandTimeout = 30000;

 

          

            cmd.CommandText = "SELECT dbo.TAXON.ITEM_NAME AS [Taxon Name], dbo.TAXON_LIST_ITEM.PREFERRED_NAME, dbo.TAXON_LIST_ITEM.PARENT, dbo.TAXON_RANK.TAXON_RANK_KEY, dbo.TAXON_RANK.LONG_NAME AS [Hierarchy Classification], TAXON_1.ITEM_NAME AS [Parent Name], dbo.TAXON_LIST.PREFERRED, dbo.TAXON_LIST.ITEM_NAME AS [List Name], TAXON_RANK_1.LONG_NAME AS [Parent Name Classification], TAXON_RANK_1.TAXON_RANK_KEY AS [Parent Taxon Rank], dbo.TAXON_LIST.TAXON_LIST_KEY FROM dbo.TAXON_LIST INNER JOIN dbo.TAXON_LIST_VERSION INNER JOIN dbo.INDEX_TAXON_NAME ON dbo.TAXON_LIST_VERSION.TAXON_LIST_VERSION_KEY = dbo.INDEX_TAXON_NAME.TAXON_LIST_VERSION_KEY ON dbo.TAXON_LIST.TAXON_LIST_KEY = dbo.TAXON_LIST_VERSION.TAXON_LIST_KEY INNER JOIN dbo.TAXON_LIST_ITEM INNER JOIN dbo.TAXON_VERSION ON dbo.TAXON_LIST_ITEM.TAXON_VERSION_KEY = dbo.TAXON_VERSION.TAXON_VERSION_KEY INNER JOIN dbo.TAXON_RANK ON dbo.TAXON_LIST_ITEM.TAXON_RANK_KEY = dbo.TAXON_RANK.TAXON_RANK_KEY INNER JOIN dbo.TAXON_LIST_ITEM AS TAXON_LIST_ITEM_1 ON dbo.TAXON_LIST_ITEM.PARENT = TAXON_LIST_ITEM_1.TAXON_LIST_ITEM_KEY INNER JOIN dbo.TAXON_VERSION AS TAXON_VERSION_1 ON TAXON_LIST_ITEM_1.TAXON_VERSION_KEY = TAXON_VERSION_1.TAXON_VERSION_KEY INNER JOIN dbo.TAXON AS TAXON_1 ON TAXON_VERSION_1.TAXON_KEY = TAXON_1.TAXON_KEY ON dbo.INDEX_TAXON_NAME.TAXON_LIST_VERSION_KEY = dbo.TAXON_LIST_ITEM.TAXON_LIST_VERSION_KEY AND dbo.TAXON_LIST_VERSION.TAXON_LIST_VERSION_KEY = dbo.TAXON_LIST_ITEM.TAXON_LIST_VERSION_KEY INNER JOIN dbo.TAXON_RANK AS TAXON_RANK_1 ON TAXON_LIST_ITEM_1.TAXON_RANK_KEY = TAXON_RANK_1.TAXON_RANK_KEY RIGHT OUTER JOIN dbo.TAXON INNER JOIN dbo.TAXON_NAME_TYPE ON dbo.TAXON.TAXON_NAME_TYPE_KEY = dbo.TAXON_NAME_TYPE.TAXON_NAME_TYPE_KEY ON dbo.TAXON_VERSION.TAXON_KEY = dbo.TAXON.TAXON_KEY WHERE (dbo.TAXON.ITEM_NAME =\'" + species.Replace("'", "''") + "\') AND (dbo.TAXON_LIST.TAXON_LIST_KEY = 'NBNSYS0000000074') AND (dbo.TAXON_LIST.PREFERRED = 0)";

 

            SqlDataReader reader = cmd.ExecuteReader();

 

            hierarchyClassification = "";

            parentName = "";

            taxonName = "";

            parentNameClassification = "";

 

            hasRows = reader.HasRows;

 

            if (reader.HasRows == true)

            {

                reader.Read();

 

                hierarchyClassification = reader["Hierarchy Classification"].ToString();

                parentName = reader["Parent Name"].ToString();

                taxonName = reader["Taxon Name"].ToString();

                getTaxonName = parentName;

                parentNameClassification = reader["Parent Name Classification"].ToString();

 

                reader.Close();

            }

 

        }

        conn.Close();

 

        if (hasRows == false && getTaxonName == null)

        {

            getTaxonName = "Unknown";

        }

 

        if (parentNameClassification == taxonClassification.ToString())

        {

            getTaxonName = parentName;

            return;

        }

 

        if (hierarchyClassification != taxonClassification.ToString() && hierarchyClassification != "")

        {

            getTheTaxonNameClassification(parentName, taxonClassification);

        }

        else

        {

            return;

        }

 

    }

 

}

Taxon.cs

using System;

using System.Data;

using System.Configuration;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

 

public class Taxon

{

    public String Family;

    public String Genus;

    public String Order;

 

    public Taxon()

    {

    }

 

    public Taxon(String family, String genus, String order)

    {

        Family = family;

        Genus = genus;

        Order = order;

    }

 

    public void set(String family, String genus, String order)

    {

        Family = family;

        Genus = genus;

        Order = order;

    }

}

TaxonClassification.cs

using System;

using System.Data;

using System.Configuration;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

 

/// <summary>

/// Summary description for TaxonClassification

/// </summary>

public enum TaxonClassification

{

    Unknown,

    Kingdom,

    Division,

    Phylum,

    Subphylum,

    Subdivision,

    Superclass,

    Class,

    Subclass,

    Superorder,

    Order,

    Infraorder,

    Suborder,

    Series,

    Section,

    Superfamily,

    Family,

    Subfamily,

    Tribe,

    GenericHybrid,

    Genus,

    Subgenus,

    SpeciesGroup,

    SpeciesAggregate,

    Species,

    SpeciesHybrid,

    SubspeciesHybrid,

    Subspecies,

    Race,

    Form,

    Abberation,

    Variety,

    FormHybrid,

    ecad,

    FunctionalGroup,

    SubspeciesAggregate,

    Subkingdom,

    Infraclass,

    Subseries,

    Supertribe,

    Subtribe,

    Subsection,

    Microspecies,

    Nothosubspecies,

    Microgene,

    Praespecies,

    Sublusus,

    VarietalHybrid,

    Nothovariety,

    Cultivar,

    Subvariety,

    Convariety,

    Facies,

    FamilyAggregate,

    OrdinalAggregate,

    Morphotype,

    Cohort,

    FormaSpecialis,

}

TaxonDictionary.cs

using System;

using System.Data;

using System.Configuration;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Collections.Generic;

using System.Data.SqlClient;

/// <summary>

/// Summary description for TaxonDictionary

/// </summary>

public class TaxonDictionary

{

    public List<String> TaxonName = new List<string>();

    public List<String> AllSynonyms = new List<string>();

    public List<String> AllTaxonVersionKeys = new List<String>();

 

      public TaxonDictionary(List<String> TaxonName)

      {

        this.TaxonName = TaxonName;

        getSynonyms();

        getTaxonVersionKeys();

      }

    protected void getSynonyms()

    {

        //Dictionary<String, List<String>> result = new Dictionary<String, List<String>>();

       

        //foreach (String eachSpecies in this.TaxonName)

        //{

            String sql = @"

                        SELECT INDEX_TAXON_NAME_1.ACTUAL_NAME

                        FROM dbo.INDEX_TAXON_NAME INNER JOIN

                        dbo.INDEX_TAXON_SYNONYM ON

                        dbo.INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY = dbo.INDEX_TAXON_SYNONYM.TAXON_LIST_ITEM_KEY INNER JOIN

                        dbo.INDEX_TAXON_NAME INDEX_TAXON_NAME_1 ON

                        dbo.INDEX_TAXON_SYNONYM.SYNONYM_LIST_ITEM_KEY = INDEX_TAXON_NAME_1.TAXON_LIST_ITEM_KEY

                        GROUP BY dbo.INDEX_TAXON_NAME.ACTUAL_NAME, INDEX_TAXON_NAME_1.ACTUAL_NAME

                        HAVING      (dbo.INDEX_TAXON_NAME.ACTUAL_NAME IN ('" + Utils.Implode(TaxonName, "','", true, "','") + "'))";

 

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["main"].ToString());

            con.Open();

 

            SqlCommand com = new SqlCommand(sql, con);

            SqlDataReader reader = com.ExecuteReader();

 

            while(reader.Read())

            {

                if(!AllSynonyms.Contains(reader[0].ToString())) AllSynonyms.Add(reader[0].ToString().Replace("'","''"));

 

            }

     

            com.Dispose();

            con.Close();

    }

    protected void getTaxonVersionKeys()

    {

        String sql = @"

                        SELECT        TAXON_LIST_ITEM.TAXON_VERSION_KEY

                        FROM            INDEX_TAXON_NAME AS INDEX_TAXON_NAME_1 INNER JOIN

                            INDEX_TAXON_NAME ON INDEX_TAXON_NAME_1.RECOMMENDED_TAXON_LIST_ITEM_KEY = INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY INNER JOIN

                            TAXON_LIST_ITEM ON INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY = TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY

                        WHERE        (INDEX_TAXON_NAME_1.ACTUAL_NAME IN ('" + Utils.Implode(TaxonName, "','", true, "','") + "')) GROUP BY TAXON_LIST_ITEM.TAXON_VERSION_KEY";

 

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["main"].ToString());

        con.Open();

 

        SqlCommand com = new SqlCommand(sql, con);

        SqlDataReader reader = com.ExecuteReader();

 

        while (reader.Read())

        {

            if (!AllTaxonVersionKeys.Contains(reader[0].ToString())) AllTaxonVersionKeys.Add(reader[0].ToString());

 

        }

        com.Dispose();

        con.Close();

    }

}

TaxonHierarchy.cs

using System;

using System.Data;

using System.Configuration;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Collections.Generic;

using System.Data.SqlClient;

 

/// <summary>

/// Summary description for TaxonHierarchy

/// </summary>

public class TaxonHierarchy

{

    public TaxonHierarchy()

    {

 

    }

 

    public static Dictionary<String, Taxon> getTaxonHierarchy(String TaxonName)

    {

        String sql = @"SELECT * FROM TaxonHierarchy WHERE ScientificName = @TaxonName";

 

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["main"].ToString());

        con.Open();

 

        SqlCommand com = new SqlCommand(sql, con);

        com.Parameters.AddWithValue("@TaxonName",TaxonName);

        SqlDataReader reader = com.ExecuteReader();

 

        Dictionary<String, Taxon> result = new Dictionary<String, Taxon>();

 

        while (reader.Read())

        {

            if (!result.ContainsKey(TaxonName))

            {

                result.Add(TaxonName, new Taxon(reader["Family"].ToString(), reader["Genus"].ToString(), reader["Order"].ToString()));

            }

        }

 

        if (reader.HasRows == false)

        {

            result.Add(TaxonName, new Taxon("Unknown", "Unknown", "Unknown"));

        }

 

        com.Dispose();

        con.Close();

 

        return result;

    }

}

 

nbnTaxonGroupChart.aspx.cs

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

using System.Web.Configuration;

using System.Collections.Generic;

using System.Web.UI.DataVisualization.Charting;

using System.Drawing;

 

 

public partial class chart_TaxonGroupChart : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        testPieChart();

    }

 

    private void testPieChart()

    {

        Dictionary<string, int> speciesListNBN = (Dictionary<string, int>)Session["nbnRecordedSpecies"];

        Dictionary<string, int> speciesListNBN00 = new Dictionary<string, int>();

 

        if (speciesListNBN != null)

        {

            foreach (KeyValuePair<string, int> kvp in speciesListNBN)

            {

                Dictionary<String, Taxon> taxonHierarchy = TaxonHierarchy.getTaxonHierarchy(kvp.Key);

 

                if (taxonHierarchy.Count != 0)

                {

                    if (speciesListNBN00.ContainsKey(taxonHierarchy[kvp.Key].Order))

                    {

                        speciesListNBN00[taxonHierarchy[kvp.Key].Order] += kvp.Value;

                    }

                    else

                    {

                        speciesListNBN00.Add(taxonHierarchy[kvp.Key].Order, kvp.Value);

                    }

 

                    /*getTaxonNameClassification TaxonClass = new getTaxonNameClassification(kvp.Key, TaxonClassification.Class);

                    if (TaxonClass.getTaxonName != null)

                    {

                        if (speciesListNBN00.ContainsKey(TaxonClass.getTaxonName))

                        {

                            speciesListNBN00[TaxonClass.getTaxonName] += kvp.Value;

                        }

                        else

                        {

                            speciesListNBN00.Add(TaxonClass.getTaxonName, kvp.Value);

                        }*/

                }

                else

                {

                    // Create a (useful) error message

                    string ErrorMessage = "A species selected does not appear in the dictionary!";

                    ErrorMessage += Environment.NewLine;

                    ErrorMessage += Environment.NewLine;

                    //ErrorMessage += Ex.Message;

                    Console.Beep();

                    // Show error message (this = the parent Form object)

                    //MessageBox.Show(ErrorMessage, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                }

            }

        }

        else

        {

            myChart2.Visible = false;

        }

 

        //create two lists for x and y values

        List<string> xNames = new List<string>(speciesListNBN00.Keys);

        List<int> yValues = new List<int>(speciesListNBN00.Values);

 

        //bind the chart data to X and Y

        myChart2.Series[0].Points.DataBindXY(xNames, yValues);

 

        //add labels

        myChart2.Series[0].Label = "#VALX\n#PERCENT";

        myChart2.Series[0]["PieLabelStyle"] = "Outside";

        myChart2.Series[0].Font = new Font("Tahoma", 10, FontStyle.Bold);

       

 

        //Enable 3D

        myChart2.ChartAreas["ChartArea1"].Area3DStyle.Enable3D = true;

 

        //Enable the Legend

        myChart2.Legends[0].Enabled = true;

 

        //format the legend

        myChart2.Legends[0].Font = new Font("Tahoma", 10, FontStyle.Bold);

        myChart2.Legends[0].Title = "Taxonomical Classification";

        myChart2.Legends[0].BorderColor = Color.Black;

        myChart2.Legends[0].BorderWidth = 2;

        myChart2.Legends[0].BorderDashStyle = ChartDashStyle.Solid;

        myChart2.Legends[0].ShadowOffset = 4;

 

        // Set chart title

        myChart2.Titles[0].Text = "NBN Species\nTaxonomical Classification";

 

        // Set chart title font

        myChart2.Titles[0].Font = new Font("Tahoma", 14, FontStyle.Bold);

 

        // Set Title Alignment

        myChart2.Titles[0].Alignment = System.Drawing.ContentAlignment.TopCenter;

 

    }

 

 

}

TaxonGroupChart.aspx.cs

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

using System.Web.Configuration;

using System.Collections.Generic;

using System.Web.UI.DataVisualization.Charting;

using System.Drawing;

 

 

public partial class chart_TaxonGroupChart : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        testPieChart();

    }

 

    private void testPieChart()

    {

        Dictionary<string, int> speciesList = (Dictionary<string, int>)Session["recordRecordedSpecies"];

        Dictionary<string, int> speciesList00 = new Dictionary<string, int>();

 

        if (speciesList != null)

        {

            foreach (KeyValuePair<string, int> kvp in speciesList)

            {

                Dictionary<String, Taxon> taxonHierarchy = TaxonHierarchy.getTaxonHierarchy(kvp.Key);

 

                //getTaxonNameClassification TaxonClass = new getTaxonNameClassification(kvp.Key, TaxonClassification.Class);

                if (taxonHierarchy.Count != 0)

                {

                    if (speciesList00.ContainsKey(taxonHierarchy[kvp.Key].Order))

                    {

                        speciesList00[taxonHierarchy[kvp.Key].Order] += kvp.Value;

                    }

                    else

                    {

                        speciesList00.Add(taxonHierarchy[kvp.Key].Order, kvp.Value);

                    }

 

                    /*

                    if (speciesList00.ContainsKey(TaxonClass.getTaxonName))

                    {

                        speciesList00[TaxonClass.getTaxonName] += kvp.Value;

                    }

                    else

                    {

                        speciesList00.Add(TaxonClass.getTaxonName, kvp.Value);

                    }

                     * */

                }

                else

                {

                    // Create a (useful) error message

                    string ErrorMessage = "A species selected does not appear in the dictionary!";

                    ErrorMessage += Environment.NewLine;

                    ErrorMessage += Environment.NewLine;

                    //ErrorMessage += Ex.Message;

                    Console.Beep();

                    // Show error message (this = the parent Form object)

                    //MessageBox.Show(ErrorMessage, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                    System.Diagnostics.Debug.Print("Connection error");

                }

            }

        }

        else

        {

            myChart.Visible = false;

        }

       

        //create two lists for x and y values

        List<string> xNames = new List<string>(speciesList00.Keys);

        List<int> yValues = new List<int>(speciesList00.Values);

 

        //bind the chart data to X and Y

        myChart.Series[0].Points.DataBindXY(xNames, yValues);

 

        //add labels

        myChart.Series[0].Label = "#VALX\n#PERCENT";

        myChart.Series[0]["PieLabelStyle"] = "Outside";

        myChart.Series[0].Font = new Font("Tahoma", 10, FontStyle.Bold);

       

 

        //Enable 3D

        myChart.ChartAreas["ChartArea1"].Area3DStyle.Enable3D = true;

 

        //Enable the Legend

        myChart.Legends[0].Enabled = true;

 

        //format the legend

        myChart.Legends[0].Font = new Font("Tahoma", 10, FontStyle.Bold);

        myChart.Legends[0].Title = "Record Species\nTaxonomical Classification";

        myChart.Legends[0].BorderColor = Color.Black;

        myChart.Legends[0].BorderWidth = 2;

        myChart.Legends[0].BorderDashStyle = ChartDashStyle.Solid;

        myChart.Legends[0].ShadowOffset = 4;

 

        // Set chart title

        myChart.Titles[0].Text = "Record Species\nTaxonomical Classification";

 

        // Set chart title font

        myChart.Titles[0].Font = new Font("Tahoma", 14, FontStyle.Bold);

 

        // Set Title Alignment

        myChart.Titles[0].Alignment = System.Drawing.ContentAlignment.TopCenter;

 

    }

 

 

}

SpeciesByYear.aspx.cs

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Web.UI.DataVisualization.Charting;

using System.Web.UI.DataVisualization.Charting.Utilities;

using System.Collections.Generic;

using System.Drawing;

public partial class chart_SpeciesByYear : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        //create dictionaries for both record and nbn chart data

        Dictionary<String, List<ChartData>> recordData = new Dictionary<string, List<ChartData>>();

        Dictionary<String, List<ChartData>> nbnData = new Dictionary<string, List<ChartData>>();

 

        recordData = (Dictionary<String, List<ChartData>>)Session["recordRecordedSpeciesStackedBar"];

        nbnData = (Dictionary<String, List<ChartData>>)Session["nbnRecordedSpeciesStackedBar"];

 

        /*

                        collCustList.Sort

                       (delegate(Customer cust1,

                       Customer cust2) {

                         return Comparer<int>.Default.Compare

                            (cust1.Id, cust2.Id);

                    });

 

         */

        if (recordData != null)

        {

            foreach (KeyValuePair<String, List<ChartData>> eachData in recordData)

            {

                eachData.Value.Sort(

                        delegate(ChartData data1, ChartData data2)

                        {

                            return Comparer<int>.Default.Compare(data1.Year, data2.Year);

                        }

                    );

            }

        }

 

        if (nbnData != null)

        {

 

            foreach (KeyValuePair<String, List<ChartData>> eachData in nbnData)

            {

                eachData.Value.Sort(

                        delegate(ChartData data1, ChartData data2)

                        {

                            return Comparer<int>.Default.Compare(data1.Year, data2.Year);

                        }

                    );

            }

        }

 

        /*

        //add species data to the record chart data set

        List<ChartData> bufoData = new List<ChartData>();

        bufoData.Add(new ChartData("Bufo bufo", 2000, 150));

        bufoData.Add(new ChartData("Bufo bufo", 2001, 250));

        bufoData.Add(new ChartData("Bufo bufo", 2002, 225));

        bufoData.Add(new ChartData("Bufo bufo", 2003, 275));

 

        //add species data to the record chart data set

        List<ChartData> foxData = new List<ChartData>();

        foxData.Add(new ChartData("fox", 2000, 180));

        foxData.Add(new ChartData("fox", 2001, 210));

        foxData.Add(new ChartData("fox", 2002, 225));

        foxData.Add(new ChartData("fox", 2003, 250));

 

        //add species data to the record chart data set

        List<ChartData> ottaData = new List<ChartData>();

        ottaData.Add(new ChartData("otta", 2000, 60));

        ottaData.Add(new ChartData("otta", 2001, 55));

        ottaData.Add(new ChartData("otta", 2002, 65));

        ottaData.Add(new ChartData("otta", 2003, 75));

 

        //add species data to the nbn chart data set

        List<ChartData> NBNbufoData = new List<ChartData>();

        NBNbufoData.Add(new ChartData("Bufo bufo", 2000, 75));

        NBNbufoData.Add(new ChartData("Bufo bufo", 2001, 125));

        NBNbufoData.Add(new ChartData("Bufo bufo", 2002, 112));

        NBNbufoData.Add(new ChartData("Bufo bufo", 2003, 140));

 

        //add species data to the nbn chart data set

        List<ChartData> NBNfoxData = new List<ChartData>();

        NBNfoxData.Add(new ChartData("fox", 2000, 90));

        NBNfoxData.Add(new ChartData("fox", 2001, 105));

        NBNfoxData.Add(new ChartData("fox", 2002, 112));

        NBNfoxData.Add(new ChartData("fox", 2003, 125));

 

        //add species data to the nbn chart data set

        List<ChartData> NBNottaData = new List<ChartData>();

        NBNottaData.Add(new ChartData("otta", 2000, 30));

        NBNottaData.Add(new ChartData("otta", 2001, 28));

        NBNottaData.Add(new ChartData("otta", 2002, 33));

        NBNottaData.Add(new ChartData("otta", 2003, 38));

       

        //add the species name to the data set for the record data set

        recordData.Add("Bufo bufo", bufoData);

        recordData.Add("Fox fox", foxData);

        recordData.Add("Otta otta", ottaData);

 

        //add the species name to the data set for the nbn data set

        nbnData.Add("Bufo bufo", NBNbufoData);

        nbnData.Add("Fox fox", NBNfoxData);

        nbnData.Add("Otta otta", NBNottaData);

        **/

 

        //create two list for the years

        List<int> listOfYearRECORD = new List<int>();

        List<int> listOfYearNBN = new List<int>();

 

        //Work out The years for the record dataset

        if (recordData != null)

        {

            foreach (KeyValuePair<string, List<ChartData>> kvp in recordData)

            {

                List<ChartData> recordListData = new List<ChartData>(kvp.Value);

                for (int a = 0; a < recordListData.Count; a++)

                {

                    if (!listOfYearRECORD.Contains(recordListData[a].Year))

                    {

                        listOfYearRECORD.Add(recordListData[a].Year);

                    }

                }

            }

 

            listOfYearRECORD.Sort();

        }

 

        //Work out The years for the NBN dataset

        if (nbnData != null)

        {

            foreach (KeyValuePair<string, List<ChartData>> kvp in nbnData)

            {

                List<ChartData> nbnListData = new List<ChartData>(kvp.Value);

                for (int a = 0; a < nbnListData.Count; a++)

                {

                    if (!listOfYearNBN.Contains(nbnListData[a].Year))

                    {

                        listOfYearNBN.Add(nbnListData[a].Year);

                    }

                }

            }

 

            listOfYearNBN.Sort();

        }

 

        //declare the variable i and set it to zero

        int i = 0;

       

        //clear the chart series

        Chart1.Series.Clear();

 

        //Create legend and Series for the record data set

        //create a variable b and set it to zero for use in the definition of the legend names

        int b = 0;

       

        for (i = 0; i < listOfYearRECORD.Count; i++)

        {

            Chart1.Series.Add("RECORD-" + listOfYearRECORD[i].ToString());

            Chart1.Series[b].LegendText = "RECORD-" + listOfYearRECORD[i].ToString();

           

            b++;

        }

 

        //Create legend and Series for the record data set

        for (i = 0; i < listOfYearNBN.Count; i++)

        {

            Chart1.Series.Add("NBN-" + listOfYearNBN[i].ToString());

            Chart1.Series[b].LegendText = "NBN-" + listOfYearNBN[i].ToString();

 

            b++;

        }

 

        //construct the record data group

        for (i = 0; i < listOfYearRECORD.Count; i++)

        {

            foreach (KeyValuePair<string, List<ChartData>> kvp in recordData)

            {

                List<ChartData> recordListData = new List<ChartData>(kvp.Value);

 

                for (int a = 0; a < recordListData.Count; a++)

                {

                    String year = recordData[kvp.Key][a].Year.ToString();

                    Chart1.Series["RECORD-" + year].ChartType = SeriesChartType.StackedColumn;

 

                    if (year == listOfYearRECORD[i].ToString())

                    {

                        Chart1.Series["RECORD-" + year].Points.AddXY(kvp.Key, recordData[kvp.Key][a].Count);

                        Chart1.Series["RECORD-" + year].AxisLabel = kvp.Key;

                        //Chart1.Series["RECORD-" + year]["StackedGroupName"] = "RecordData";

                    }

                }

            }

        }

 

        //construct the nbn data group

        for (i = 0; i < listOfYearNBN.Count; i++)

        {

            foreach (KeyValuePair<string, List<ChartData>> kvp in nbnData)

            {

                List<ChartData> nbnListData = new List<ChartData>(kvp.Value);

 

                for (int a = 0; a < nbnListData.Count; a++)

                {

                    String year = nbnData[kvp.Key][a].Year.ToString();

                    Chart1.Series["NBN-" + year].ChartType = SeriesChartType.StackedColumn;

 

                    if (year == listOfYearNBN[i].ToString())

                    {

                        Chart1.Series["NBN-" + year].Points.AddXY(kvp.Key, nbnData[kvp.Key][a].Count);

                        int endPoint = Chart1.Series["NBN-" + year].Points.Count;

                        Chart1.Series["NBN-" + year].AxisLabel = kvp.Key;

                        //Chart1.Series["NBN-" + year]["StackedGroupName"] = "NBNData";

                    }

                }

            }

        }

       

        for (int a = 0; a < listOfYearRECORD.Count + listOfYearNBN.Count; a++)

        {

            if (Chart1.Series[a].Name.Contains("RECORD"))

            {

                Chart1.Series[a]["StackedGroupName"] = "RecordData";

            }

            else

            {

              Chart1.Series[a]["StackedGroupName"] = "NBNData";

    

            }

 

        }

 

 

        //reset the auto values of the chart

        //Chart1.ResetAutoValues();

 

        //set the X axis text orientation

        Chart1.ChartAreas[0].AxisX.LabelStyle.Angle = 45;   

 

        //set the x axis interval for labels

        Chart1.ChartAreas["ChartArea1"].AxisX.Interval = 1;

 

        /*//set the x axis titles

        Chart1.ChartAreas["ChartArea1"].AxisX.Title = "Species by year";

        Chart1.ChartAreas["ChartArea1"].AxisX.TitleFont = new Font("Tahoma", 14, FontStyle.Bold);

        * */

 

      //set the y axis titles

        Chart1.ChartAreas["ChartArea1"].AxisY.Title = "Records";

        Chart1.ChartAreas["ChartArea1"].AxisY.TitleFont = new Font("Tahoma", 12, FontStyle.Bold);

       

 

        //set the font and style for the x and y axis

        Chart1.ChartAreas["ChartArea1"].AxisX.LabelStyle.Font = new Font("Tahoma", 10, FontStyle.Bold);

        Chart1.ChartAreas["ChartArea1"].AxisY.LabelStyle.Font = new Font("Tahoma", 10, FontStyle.Bold);

 

 

        //show legend

        // Create a new legend called "Legend 1".

        Chart1.Legends.Add(new Legend("Legend 1"));

 

        //format the legend

        Chart1.Legends["Legend 1"].Font = new Font("Tahoma", 10, FontStyle.Bold);

        Chart1.Legends["Legend 1"].Title = "Years";

        Chart1.Legends[0].BorderColor = Color.Black;

        Chart1.Legends[0].BorderWidth = 2;

        Chart1.Legends[0].BorderDashStyle = ChartDashStyle.Solid;

        Chart1.Legends[0].ShadowOffset = 4;

/*

        // Show point labels

        for (int a = 0; a < listOfYearRECORD.Count + listOfYearNBN.Count; a++)

        {

            Chart1.Series[a].IsValueShownAsLabel = true;

            Chart1.Series[a].Label = "Y = #VALY\nX = #VALX\n#LEGENDTEXT";

        }

**/

 

        // Set chart title

        Chart1.Titles[0].Text = "Species by Year";

 

        // Set chart title font

        Chart1.Titles[0].Font = new Font("Tahoma", 14, FontStyle.Bold);

 

        // Set Title Alignment

        Chart1.Titles[0].Alignment = System.Drawing.ContentAlignment.TopCenter;

 

        //align the x lable points

        Chart1.AlignDataPointsByAxisLabel();

    }

}



 


 



 


 

Evidence of Learning Target 2

Though my project I develop my skills in learning and applying MS SQL, jQuery and C# through the development of the statistical analysis package for the RECORD website.

This evidence is broken down into three sections, these are SQL Statements which shows some example queries using MS SQL. jQueries which show the Ajax code necessary to display the statistics tab and the links for the three charts. Further C# is more C# code examples and covers a number of classes which deal with the extension to the applications functionality, they cover: the enumeration of species designation, the generation of a species designation list, together with, a species name by designation list crosstab, a managed species list, the get taxon name classification class, the program class, taxon class and taxon classification.

SQL Statements

String sql = @"SELECT * FROM TaxonHierarchy WHERE ScientificName = @TaxonName";

String sql = @"

                        SELECT INDEX_TAXON_NAME_1.ACTUAL_NAME

                        FROM dbo.INDEX_TAXON_NAME INNER JOIN

                        dbo.INDEX_TAXON_SYNONYM ON

                        dbo.INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY = dbo.INDEX_TAXON_SYNONYM.TAXON_LIST_ITEM_KEY INNER JOIN

                        dbo.INDEX_TAXON_NAME INDEX_TAXON_NAME_1 ON

                        dbo.INDEX_TAXON_SYNONYM.SYNONYM_LIST_ITEM_KEY = INDEX_TAXON_NAME_1.TAXON_LIST_ITEM_KEY

                        GROUP BY dbo.INDEX_TAXON_NAME.ACTUAL_NAME, INDEX_TAXON_NAME_1.ACTUAL_NAME

                        HAVING      (dbo.INDEX_TAXON_NAME.ACTUAL_NAME IN ('" + Utils.Implode(TaxonName, "','", true, "','") + "'))";

 

        String sql = @"

                        SELECT        TAXON_LIST_ITEM.TAXON_VERSION_KEY

                        FROM            INDEX_TAXON_NAME AS INDEX_TAXON_NAME_1 INNER JOIN

                            INDEX_TAXON_NAME ON INDEX_TAXON_NAME_1.RECOMMENDED_TAXON_LIST_ITEM_KEY = INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY INNER JOIN

                            TAXON_LIST_ITEM ON INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY = TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY

                        WHERE        (INDEX_TAXON_NAME_1.ACTUAL_NAME IN ('" + Utils.Implode(TaxonName, "','", true, "','") + "')) GROUP BY TAXON_LIST_ITEM.TAXON_VERSION_KEY";

 

cmd.CommandText = "SELECT dbo.TAXON.ITEM_NAME AS [Taxon Name], dbo.TAXON_LIST_ITEM.PREFERRED_NAME, dbo.TAXON_LIST_ITEM.PARENT, dbo.TAXON_RANK.TAXON_RANK_KEY, dbo.TAXON_RANK.LONG_NAME AS [Hierarchy Classification], TAXON_1.ITEM_NAME AS [Parent Name], dbo.TAXON_LIST.PREFERRED, dbo.TAXON_LIST.ITEM_NAME AS [List Name], TAXON_RANK_1.LONG_NAME AS [Parent Name Classification], TAXON_RANK_1.TAXON_RANK_KEY AS [Parent Taxon Rank] FROM dbo.TAXON_LIST INNER JOIN dbo.TAXON_LIST_VERSION INNER JOIN  dbo.INDEX_TAXON_NAME ON dbo.TAXON_LIST_VERSION.TAXON_LIST_VERSION_KEY = dbo.INDEX_TAXON_NAME.TAXON_LIST_VERSION_KEY ON dbo.TAXON_LIST.TAXON_LIST_KEY = dbo.TAXON_LIST_VERSION.TAXON_LIST_KEY INNER JOIN dbo.TAXON_LIST_ITEM INNER JOIN dbo.TAXON_VERSION ON dbo.TAXON_LIST_ITEM.TAXON_VERSION_KEY = dbo.TAXON_VERSION.TAXON_VERSION_KEY INNER JOIN dbo.TAXON_RANK ON dbo.TAXON_LIST_ITEM.TAXON_RANK_KEY = dbo.TAXON_RANK.TAXON_RANK_KEY INNER JOIN dbo.TAXON_LIST_ITEM AS TAXON_LIST_ITEM_1 ON dbo.TAXON_LIST_ITEM.PARENT = TAXON_LIST_ITEM_1.TAXON_LIST_ITEM_KEY INNER JOIN dbo.TAXON_VERSION AS TAXON_VERSION_1 ON TAXON_LIST_ITEM_1.TAXON_VERSION_KEY = TAXON_VERSION_1.TAXON_VERSION_KEY INNER JOIN dbo.TAXON AS TAXON_1 ON TAXON_VERSION_1.TAXON_KEY = TAXON_1.TAXON_KEY ON dbo.INDEX_TAXON_NAME.TAXON_LIST_VERSION_KEY = dbo.TAXON_LIST_ITEM.TAXON_LIST_VERSION_KEY AND dbo.TAXON_LIST_VERSION.TAXON_LIST_VERSION_KEY = dbo.TAXON_LIST_ITEM.TAXON_LIST_VERSION_KEY INNER JOIN dbo.TAXON_RANK AS TAXON_RANK_1 ON TAXON_LIST_ITEM_1.TAXON_RANK_KEY = TAXON_RANK_1.TAXON_RANK_KEY RIGHT OUTER JOIN dbo.TAXON INNER JOIN dbo.TAXON_NAME_TYPE ON dbo.TAXON.TAXON_NAME_TYPE_KEY = dbo.TAXON_NAME_TYPE.TAXON_NAME_TYPE_KEY ON dbo.TAXON_VERSION.TAXON_KEY = dbo.TAXON.TAXON_KEY WHERE (dbo.TAXON.ITEM_NAME = \'" + species.Replace("'", "''") + "\') AND (dbo.TAXON_LIST.PREFERRED = 1)";

            cmd.CommandText = "SELECT dbo.TAXON.ITEM_NAME AS [Taxon Name], dbo.TAXON_LIST_ITEM.PREFERRED_NAME, dbo.TAXON_LIST_ITEM.PARENT, dbo.TAXON_RANK.TAXON_RANK_KEY, dbo.TAXON_RANK.LONG_NAME AS [Hierarchy Classification], TAXON_1.ITEM_NAME AS [Parent Name], dbo.TAXON_LIST.PREFERRED, dbo.TAXON_LIST.ITEM_NAME AS [List Name], TAXON_RANK_1.LONG_NAME AS [Parent Name Classification], TAXON_RANK_1.TAXON_RANK_KEY AS [Parent Taxon Rank], dbo.TAXON_LIST.TAXON_LIST_KEY FROM dbo.TAXON_LIST INNER JOIN dbo.TAXON_LIST_VERSION INNER JOIN dbo.INDEX_TAXON_NAME ON dbo.TAXON_LIST_VERSION.TAXON_LIST_VERSION_KEY = dbo.INDEX_TAXON_NAME.TAXON_LIST_VERSION_KEY ON dbo.TAXON_LIST.TAXON_LIST_KEY = dbo.TAXON_LIST_VERSION.TAXON_LIST_KEY INNER JOIN dbo.TAXON_LIST_ITEM INNER JOIN dbo.TAXON_VERSION ON dbo.TAXON_LIST_ITEM.TAXON_VERSION_KEY = dbo.TAXON_VERSION.TAXON_VERSION_KEY INNER JOIN dbo.TAXON_RANK ON dbo.TAXON_LIST_ITEM.TAXON_RANK_KEY = dbo.TAXON_RANK.TAXON_RANK_KEY INNER JOIN dbo.TAXON_LIST_ITEM AS TAXON_LIST_ITEM_1 ON dbo.TAXON_LIST_ITEM.PARENT = TAXON_LIST_ITEM_1.TAXON_LIST_ITEM_KEY INNER JOIN dbo.TAXON_VERSION AS TAXON_VERSION_1 ON TAXON_LIST_ITEM_1.TAXON_VERSION_KEY = TAXON_VERSION_1.TAXON_VERSION_KEY INNER JOIN dbo.TAXON AS TAXON_1 ON TAXON_VERSION_1.TAXON_KEY = TAXON_1.TAXON_KEY ON dbo.INDEX_TAXON_NAME.TAXON_LIST_VERSION_KEY = dbo.TAXON_LIST_ITEM.TAXON_LIST_VERSION_KEY AND dbo.TAXON_LIST_VERSION.TAXON_LIST_VERSION_KEY = dbo.TAXON_LIST_ITEM.TAXON_LIST_VERSION_KEY INNER JOIN dbo.TAXON_RANK AS TAXON_RANK_1 ON TAXON_LIST_ITEM_1.TAXON_RANK_KEY = TAXON_RANK_1.TAXON_RANK_KEY RIGHT OUTER JOIN dbo.TAXON INNER JOIN dbo.TAXON_NAME_TYPE ON dbo.TAXON.TAXON_NAME_TYPE_KEY = dbo.TAXON_NAME_TYPE.TAXON_NAME_TYPE_KEY ON dbo.TAXON_VERSION.TAXON_KEY = dbo.TAXON.TAXON_KEY WHERE (dbo.TAXON.ITEM_NAME =\'" + species.Replace("'", "''") + "\') AND (dbo.TAXON_LIST.TAXON_LIST_KEY = 'NBNSYS0000000074') AND (dbo.TAXON_LIST.PREFERRED = 0)";

jQuery

        jquery.Append(@"

 

                      //declair variable for use in aborting function calls

                      var ajaxTaxonGroupChart = null;

                      var ajaxNBNTaxonGroupChart = null;

                      var ajaxSpeciesByYearChart = null;

                      var times = 0;

 

                $('#tabs').bind('tabsshow', function(event, ui) {

           

 

             //console.log(ui.panel.id);

             if (ui.panel.id == 'tabMap') {

                resizeMap();

                mapToggling = 'True';

             }else{

                mapToggling = 'False';

             }

            

            //create the statistical analysis tab

             if(ui.panel.id == 'tabStatistics'){

                console.log('tabloStatistics');

                //hide the chart links section of the page

                 $('#chartLinks').hide();

 

                //when the species by year chart link is click load the species by year chart

                //Make AJAX call

                   $('#chartLinks').hide();

                   $('#chart').hide();

                   $('#chart2').hide();

                   $('#loadingChart').show();

 

                        ajaxSpeciesByYearChart = $('#chart').load('chart/SpeciesByYear.aspx #chartResult');

 

                //when the chart loads hide the loading icon and show the other chart links

                   $('#loadingChart').ajaxSuccess(function(){

                        $('#loadingChart').hide();

                        $('#chartLinks').show()

                        $('#chart').show();

                    }); 

                   }

 

                //when the species by year chart link is click load the species by year chart

                $('#chartLink01').click(function(){

                //Make AJAX call

                   $('#chartLinks').hide();

                   $('#chart').hide();

                   $('#chart2').hide();

                   $('#loadingChart').show();

 

                   //increament the times variable by 1 to ensure that the chart load only runs once

                   times++;

                   if (times == 1)

                    {

                     $('#chart').load('chart/SpeciesByYear.aspx #chartResult');

                     times = times;

                    }

                    else

                    {

                     times = 0;

                    }

 

                      

                   //when the chart loads hide the loading icon and show the other chart links

                   $('#loadingChart').unbind();

                    $('#loadingChart').ajaxSuccess(function(){

                        $('#loadingChart').hide();

                        $('#chartLinks').show()

                        $('#chart').show();

 

                    });

                  });

 

                //load the record taxon group chart

                   $('#chartLink02').click(function(){

                //Make AJAX call

                   $('#chartLinks').hide();

                   $('#chart').hide();

                   $('#chart2').hide();

                   $('#loadingChart').show();

 

                   //increament the times variable by 1 to ensure that the chart load only runs once

                   times++;

                   if (times == 1)

                    {

                     ajaxTaxonGroupChart = $('#chart').load('chart/TaxonGroupChart.aspx #chartResult');

                     times = times;

                    }

                    else

                    {

                     times = 0;

                    }

 

                   //when the chart loads hide the loading icon and show the other chart links

                   $('#loadingChart').ajaxSuccess(function(){

                        $('#loadingChart').hide();

                        $('#chartLinks').show()

                        $('#chart').show();

   

                    }); 

                  });

 

 

 

                //load the NBN taxon group chart

                   $('#chartLink03').click(function(){

                //Make AJAX call

                   $('#chartLinks').hide();

                   $('#chart').hide();

                   $('#chart2').hide();

                   $('#loadingChart').show();

                   times++;

                   if (times == 1)

                    {

                     ajaxNBNTaxonGroupChart = $('#chart').load('chart/nbnTaxonGroupChart.aspx #chartResult');

                     times = times;

                    }

                    else

                    {

                     times = 0;

                    }

 

                   //when the chart loads hide the loading icon and show the other chart links

                   $('#loadingChart').ajaxSuccess(function(){

                        $('#loadingChart').hide();

                        $('#chartLinks').show()

                        $('#chart').show();

                    }); 

                  });

 

        });

        ");

Further C# examples

enumSpeciesDesignation.cs

using System;

using System.Data;

using System.Configuration;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

 

/// <summary>

/// Summary description for enumSpeciesDesignation

/// </summary>

public enum enumSpeciesDesignation

{

    Protected_species,

    BAP_Species,

    CROW,

    Invasive,

    IUCN_PRE_1994_RDB,

    IUCN_RDB,

    LBAP,

    Locally_Scarce_Plants,

    NATIONAL_NOTABLE_LIST,

    S41_NERC,

    WC_ACT_1981,

    SPECIES_CONSERVATION_CONCERN

}

speciesDesignationList.cs

using System;

using System.Data;

using System.Configuration;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Web.Configuration;

 

/// <summary>

/// Summary description for speciesDesignationList

/// </summary>

public class speciesDesignationList

{

      public static String getDesignationListCSV()

      {

        List<String> result = new List<String>();

        SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["rECOrdConnectionString"].ConnectionString);

 

        con.Open();

 

        using (SqlCommand cmd = con.CreateCommand())

        {  

            cmd.CommandTimeout = 30000;

 

            cmd.CommandText = @"SELECT DESIGNATION_NAME FROM DESIGNATION_LIST";

 

 

            SqlDataReader reader = cmd.ExecuteReader();

 

            while (reader.Read())

            {

                result.Add(reader["DESIGNATION_NAME"].ToString().Trim());

            }

        }

        con.Close();

        return System.String.Join(",",result.ToArray());

      }

}

speciesNameByDesignationList.aspx

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Web.Configuration;

using System.Text;

 

public partial class speciesNameByDesignationList : System.Web.UI.Page

{

    //Dictionary<String, Boolean> speciesDesignationList = new Dictionary<String, Boolean>();

    Dictionary<String, List<enumSpeciesDesignation>> speciesWithDesignationList = new Dictionary<String, List<enumSpeciesDesignation>>();

    //Dictionary<String, Dictionary<speciesDesignationList>> speciesNames = new Dictionary<String, Dictionary<speciesDesignationList>>();

    protected void Page_Load(object sender, EventArgs e)

    {

        Server.ScriptTimeout = 60000;

        preLoadSpeciesDesignation();

        loadTaxonName();

        //addSpeciesDesignationToSpeciesWithDesignationList();

        saveToCSV();

 

 

    }

 

    private void loadTaxonName()

    {

        SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["rECOrdConnectionString"].ConnectionString);

 

        con.Open();

 

        using (SqlCommand cmd = con.CreateCommand())

        {

            cmd.CommandTimeout = 30000;

 

            cmd.CommandText = @"SELECT TAXON.ITEM_NAME FROM DESIGNATION_SPECIES INNER JOIN NAMESERVER ON DESIGNATION_SPECIES.TAXON_VERSION_KEY = NAMESERVER.INPUT_TAXON_VERSION_KEY INNER JOIN TAXON_VERSION ON DESIGNATION_SPECIES.TAXON_VERSION_KEY = TAXON_VERSION.TAXON_VERSION_KEY INNER JOIN TAXON ON TAXON_VERSION.TAXON_KEY = TAXON.TAXON_KEY GROUP BY TAXON.ITEM_NAME HAVING      (TAXON.ITEM_NAME = 'Arvicola terrestris')";

 

 

            SqlDataReader reader = cmd.ExecuteReader();

 

            while (reader.Read())

            {

                //speciesWithDesignationList.Add(reader[0].ToString(), new List<enumSpeciesDesignation>());

                speciesWithDesignationList.Add(reader[0].ToString(),

                    speciesDesignation[reader[0].ToString()]);

            }

 

 

 

        }

        con.Close();

 

    }

 

    private void addSpeciesDesignationToSpeciesWithDesignationList()

    {

        foreach (KeyValuePair<String, List<enumSpeciesDesignation>> addingSpeciesDesignation in speciesWithDesignationList)

        {

            List<enumSpeciesDesignation> des = getSpeciesDesignation(addingSpeciesDesignation.Key);

            speciesWithDesignationList[addingSpeciesDesignation.Key].AddRange(des);

        }

 

    }

 

    private List<enumSpeciesDesignation> getSpeciesDesignation(String speciesName)

    {

        List<enumSpeciesDesignation> result = new List<enumSpeciesDesignation>();

 

        SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["rECOrdConnectionString"].ConnectionString);

 

        con.Open();

 

        using (SqlCommand cmd = con.CreateCommand())

        {

            cmd.CommandTimeout = 30000;

 

            cmd.CommandText = @"SELECT DESIGNATION_LIST.DESIGNATION_NAME, TAXON.ITEM_NAME

FROM NAMESERVER INNER JOIN TAXON_LIST_ITEM ON NAMESERVER.RECOMMENDED_TAXON_LIST_ITEM_KEY = TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY INNER JOIN TAXON INNER JOIN TAXON_VERSION ON TAXON.TAXON_KEY = TAXON_VERSION.TAXON_KEY ON TAXON_LIST_ITEM.TAXON_VERSION_KEY = TAXON_VERSION.TAXON_VERSION_KEY INNER JOIN DESIGNATION_SPECIES INNER JOIN DESIGNATION_LIST ON DESIGNATION_SPECIES.DESIGNATION_ID = DESIGNATION_LIST.DESIGNATION_ID ON NAMESERVER.INPUT_TAXON_VERSION_KEY = DESIGNATION_SPECIES.TAXON_VERSION_KEY GROUP BY DESIGNATION_LIST.DESIGNATION_NAME, TAXON.ITEM_NAME HAVING(TAXON.ITEM_NAME = '" + speciesName.Replace("'","''") + "')";

 

            SqlDataReader reader = cmd.ExecuteReader();

 

            while (reader.Read())

            {

                enumSpeciesDesignation e = (enumSpeciesDesignation) Enum.Parse(typeof(enumSpeciesDesignation), reader["DESIGNATION_NAME"].ToString().Trim().Replace(' ','_'));

                result.Add(e);

            }

 

 

 

        }

        con.Close();

        return result;

    }

 

    private void saveToCSV()

    {

        String des = speciesDesignationList.getDesignationListCSV();

        StringBuilder csv = new StringBuilder("Species Name,"+ des +"\n");

 

        String[] arrayDes = des.Split(',');

       

        System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex("\\{[0-9]*\\}*");

        String csvDesTemp = "";

        foreach (KeyValuePair<String, List<enumSpeciesDesignation>> exportCSV in speciesWithDesignationList)

        {

            csvDesTemp = "{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12}";

            StringBuilder sbCsv = new StringBuilder();

            //StringBuilder csvTemp = new StringBuilder();

            foreach (enumSpeciesDesignation sdl in exportCSV.Value)

            {

                //find the index position in arrayDes

                int i = Array.IndexOf(arrayDes, sdl.ToString().Replace("_", " "));

                if (i != -1)

                {

                    csvDesTemp = csvDesTemp.Replace("{" + i + "}", "true");

                   

                }

            }

           

            csvDesTemp = regex.Replace(csvDesTemp, "false");

           

            csv.Append(csvDesTemp + "\n");

        }

        

 

        string csvFile = csv.ToString();

        string fileName = "speciesWithDesignationList";

        try

        {

            System.IO.File.WriteAllText(Server.MapPath("~/downloadables/" + fileName + ".csv"), csvFile);

        }

        catch (Exception ex)

        {

            System.Diagnostics.Debug.WriteLine(ex.StackTrace);

           

        }

 

    }

 

    Dictionary<String, List<enumSpeciesDesignation>> speciesDesignation = new Dictionary<string, List<enumSpeciesDesignation>>();

 

    private void preLoadSpeciesDesignation()

    {

        List<enumSpeciesDesignation> result = new List<enumSpeciesDesignation>();

 

        SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["rECOrdConnectionString"].ConnectionString);

 

        con.Open();

 

        using (SqlCommand cmd = con.CreateCommand())

        {

            cmd.CommandTimeout = 30000;

            //Arvicola terrestris

            cmd.CommandText = @"SELECT     DESIGNATION_LIST.DESIGNATION_NAME, TAXON.ITEM_NAME

FROM         NAMESERVER INNER JOIN

                      TAXON_LIST_ITEM ON NAMESERVER.RECOMMENDED_TAXON_LIST_ITEM_KEY = TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY INNER JOIN

                      TAXON INNER JOIN

                      TAXON_VERSION ON TAXON.TAXON_KEY = TAXON_VERSION.TAXON_KEY ON

                      TAXON_LIST_ITEM.TAXON_VERSION_KEY = TAXON_VERSION.TAXON_VERSION_KEY INNER JOIN

                      DESIGNATION_SPECIES INNER JOIN

                      DESIGNATION_LIST ON DESIGNATION_SPECIES.DESIGNATION_ID = DESIGNATION_LIST.DESIGNATION_ID ON

                      NAMESERVER.INPUT_TAXON_VERSION_KEY = DESIGNATION_SPECIES.TAXON_VERSION_KEY

GROUP BY DESIGNATION_LIST.DESIGNATION_NAME, TAXON.ITEM_NAME

HAVING      (TAXON.ITEM_NAME = 'Arvicola terrestris')";

 

            SqlDataReader reader = cmd.ExecuteReader();

 

            while (reader.Read())

            {

                result.Clear();

 

                enumSpeciesDesignation e = (enumSpeciesDesignation)Enum.Parse(typeof(enumSpeciesDesignation), reader["DESIGNATION_NAME"].ToString().Trim().Replace(' ', '_'));

                if (!speciesDesignation.ContainsKey(reader[1].ToString()))

                {

                    result.Add(e);

                    speciesDesignation.Add(reader[1].ToString(),result);

                }

                else

                {

                    if (!speciesDesignation[reader[1].ToString()].Contains(e))

                    {

                        speciesDesignation[reader[1].ToString()].Add(e);

                    }

                }

                //result.Add(e);

            }

 

 

 

        }

        con.Close();

    }

}

manageSpeciesList.aspx.cs

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

 

public partial class manageSpeciesList : System.Web.UI.Page

{

 

    protected void Page_Load(object sender, EventArgs e)

    {

        Session["DESIGNATION_ID"] = Request["DESIGNATION_ID"];

 

        Label1.Text = Request["DESIGNATION_NAME"];

    }

    protected void btnAdd_Click(object sender, EventArgs e)

    {

        string[] speciesDesignation = txtSpecies.Text.Split(Environment.NewLine.ToCharArray());

 

        for (int i = 0; i < speciesDesignation.Length; i++)

        {

            if (speciesDesignation[i] != "")

            {

                String versionKey = getTaxonVersionKey(speciesDesignation[i]);

                if (versionKey != "")

                {

                    dsSpeciesList.InsertParameters["DESIGNATION_ID"].DefaultValue = Session["DESIGNATION_ID"].ToString();

                    dsSpeciesList.InsertParameters["TAXON_VERSION_KEY"].DefaultValue = versionKey;

 

                    dsSpeciesList.Insert();

                }

            } 

        }

    }

 

    public String getTaxonVersionKey(String Species)

    {

        String sql = @"SELECT     TAXON.ITEM_NAME, NAMESERVER.RECOMMENDED_TAXON_VERSION_KEY

                            FROM         TAXON INNER JOIN

                            TAXON_VERSION ON TAXON.TAXON_KEY = TAXON_VERSION.TAXON_KEY INNER JOIN

                            NAMESERVER ON TAXON_VERSION.TAXON_VERSION_KEY = NAMESERVER.INPUT_TAXON_VERSION_KEY

                            WHERE      TAXON.ITEM_NAME = '"+ Species.Replace("'","''") +"'";

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["rECOrdConnectionString"].ToString());

        con.Open();

        SqlCommand com = con.CreateCommand();

        com.CommandText = sql;

        SqlDataReader reader = com.ExecuteReader();

        String result = "";

        while (reader.Read())

        {

            result = reader[1].ToString();

        }

        con.Close();

        return result;

    }

 

 

    protected void btnDelete_Click(object sender, EventArgs e)

    {

        String designationID = Session["DESIGNATION_ID"].ToString();

 

        dsSpeciesList.DeleteCommand = "DELETE FROM DESIGNATION_SPECIES WHERE DESIGNATION_ID = "+ designationID +";";

        dsSpeciesList.Delete();

    }

}

getTaxonNameClassification.cs

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

//using System.Web.Security;

//using System.Web.UI;

//using System.Web.UI.HtmlControls;

//using System.Web.UI.WebControls;

//using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using MySql.Data.MySqlClient;

using System.Collections.Generic;

//using System.Web.Configuration;

 

/// <summary>

/// Summary description for getTaxonNameClassification

/// </summary>

 

namespace TaxonHierarchyWindowsApp

{

    public class getTaxonNameClassification

    {

        private string original_species_name;

        public string getTaxonName;

        private string taxonName;

        private string taxon_taxon_list_item_key = "";

        private string original_taxon_list_item_key = "";

        private string TAXON_KEY;

        private string hierarchyClassification;

        private string parentName;

        private string parentNameClassification;

 

        public string SpeciesOrder = ""

            , SpeciesFamily = ""

            , SpeciesGenus = ""

            , SpeciesClass = "";

 

 

        public getTaxonNameClassification(string originalSpeciesName, string taxon_list_item_key, TaxonClassification taxonClassification)

        {

            original_species_name = originalSpeciesName;

 

            //getTheTaxonNameClassification(taxon_list_item_key, taxonClassification, true);

        }

 

        public void getTheTaxonNameClassification(string taxon_list_item_key, TaxonClassification taxonClassification, Boolean isPreferred)

        {

            if (original_taxon_list_item_key == "") original_taxon_list_item_key = taxon_list_item_key;

            bool hasRows;

 

 

 

            MySqlConnection conn = new MySqlConnection("Server=localhost;Database=nbndata;Uid=root; Pwd=recordsyzygy;");

 

            conn.Open();

            /*

            try

            {

                //Database connection string

 

                conn.Open();

            }

            catch (Exception Ex)

            {

                // Try to close the connection

                if (conn != null)

                    conn.Dispose();

 

                // Create a (useful) error message

                string ErrorMessage = "A error occurred while trying to connect to the server.";

                ErrorMessage += Environment.NewLine;

                ErrorMessage += Environment.NewLine;

                ErrorMessage += Ex.Message;

                Console.Beep();

                // Show error message (this = the parent Form object)

                //MessageBox.Show(ErrorMessage, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);

 

                // Stop here

                return;

            }

            */

            //using ()

            //{

            MySqlCommand cmd = new MySqlCommand("", conn);

            cmd.CommandTimeout = 10000;

 

            cmd.CommandText = @"

                SELECT        taxon.ITEM_NAME AS `Taxon Name`, taxon_rank.LONG_NAME AS `Hierarchy Classification`, taxon_rank_1.LONG_NAME AS `Parent Name Classification`,

                         taxon_list_item_1.TAXON_LIST_ITEM_KEY, taxon_1.ITEM_NAME AS `Parent Name`

FROM            taxon INNER JOIN

                         taxon_version ON taxon.TAXON_KEY = taxon_version.TAXON_KEY INNER JOIN

                         nameserver ON taxon_version.TAXON_VERSION_KEY = nameserver.INPUT_TAXON_VERSION_KEY INNER JOIN

                         taxon_list_item ON nameserver.RECOMMENDED_TAXON_LIST_ITEM_KEY = taxon_list_item.TAXON_LIST_ITEM_KEY INNER JOIN

                         taxon_rank ON taxon_list_item.TAXON_RANK_KEY = taxon_rank.TAXON_RANK_KEY INNER JOIN

                         taxon_list_item taxon_list_item_1 ON taxon_list_item.PARENT = taxon_list_item_1.TAXON_LIST_ITEM_KEY INNER JOIN

                         nameserver nameserver_1 ON taxon_list_item_1.TAXON_VERSION_KEY = nameserver_1.INPUT_TAXON_VERSION_KEY INNER JOIN

                         taxon_version taxon_version_1 ON nameserver_1.RECOMMENDED_TAXON_VERSION_KEY = taxon_version_1.TAXON_VERSION_KEY INNER JOIN

                         taxon taxon_1 ON taxon_version_1.TAXON_KEY = taxon_1.TAXON_KEY INNER JOIN

                         taxon_rank taxon_rank_1 ON taxon_list_item_1.TAXON_RANK_KEY = taxon_rank_1.TAXON_RANK_KEY

WHERE      (TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY = '" + taxon_list_item_key + "') Limit 1";

 

            /*

                if (isPreferred)

                {