Unlock TFS Files Locked by Other Developers

It is a common experience that someone needs to unlock a file, locked in Team Foundation Server by another developer at a moment that the developer who locked the file is not available to unlock it. Same thing happens if the original login used for lock a file is no longer exists. For the both cases one solution would be the use of 'tf undo' command from Visual Studio command prompt. The undo command removes any locks so that other developers can check out the file. It should be noted that you should have enough privileges to execute this command. Command syntax is:

tf undo /Projects/Project1/Controls/FileName.cs"

/WORKSPACE:Dev123;Domain\UserName /s:http://tfs01:8080 /recursive

Here the Workspace and the User are the ones who locked the file. This information can obtain from source control explorer. If you want to find list of files locked by a developer use the 'tf status' command.

One important thing to remember when using 'tf undo' command is, you may have to use server name as "http://…" if you are executing command from a developer box. Otherwise you will get the ‘cannot find server’ error message for server name as well as for IP address though you are in the same domain, etc. By using server name as a URL it will uses web services to invoke the command.

You can read more detail about “Tf Command Line Utility Commands” from MSDN here.

Session Timeout During Execution – SSRS Reports

This is a common issue in reports, if you are working on generating reports with several hundred thousand records and complex joins. The general solution for this problem would be reducing the time that report takes to run by simplifying the report or improving performance of stored procedures if used. But this is not always possible often with our rigid requirements. For these situations following two solutions can be helpful;

• Run the report offline – This can be done by scheduling report to run on free hours like night. Then the users only see this snapshot of the data. But this approach may not be enough flexible, as we cannot directly change parameters for each run.

• Deliver the reports via a subscription – Here user has to come to the browser and subscribe to the report with parameters if needed. Then can leave the page without waiting until the report is generated. Report will be delivered to the person via email or a file share.

To brief the Subscription method, it is a request to deliver a report at a specific time or in response to an event rather than running a report on demand. There are two types of Subscriptions supported by SQL Server Reporting Services.

Standard Subscriptions – Usually created and managed by individual users and they consist of static values that cannot be varied during subscription processing.

Data-Driven Subscriptions – This is a dynamic subscription. Here the presentation, delivery, and parameter values are retrieved at run time from a data source. Data-driven subscriptions are a good choice if you want to vary report output for each recipient.

A subscription consists of several parts, which we should specify. They include the report that should run, report delivery method, the rendering format, conditions for processing the subscription, and parameters used when running the report.

For more information visit official msdn site at following locations,

How to Subscribe to a Report (Report Manager)
How to Schedule a Report Snapshot (Report Manager)

Virtual Reality and Migration to Virtual Space

Abstract: Virtual Reality is an artificial environment created by computers in which people can immerse themselves and get the sense of a real environment. It is a way for humans to visualize, manipulate and interact with computers and extremely complex data. This technology allows a user to interact with a computer simulated environment, which can be real or imagined one. Virtual Reality environments are varying from visual experiences to simulations with sensory information and getting popular in several areas like trainings in health care, treatments for anxiety disorders, learning environments, business and marketing, entertainment and scientific visualization. This paper briefs the application of Virtual Reality in some of above areas, technologies used, problems caused, and the quality of the interaction.

This is the abstract of the literature survey conducted by me as my final year independent study in the University. If anyone interested in this area here is the link for full document.

It should be noted that, this is not based on any research conducted by me, but a literature survey of studying research papers. I have maintained the complete list of referred papers in the document.

Windows 7 & Final day of Windows XP

There is a buzz among the community that Microsoft is going to end its availability of our popular operating system Windows XP, to retailers and major computer makers. The date set for this is 30th June 2008. As it is saying several top PC sellers including Dell, HP and Acer going to quit from selling notebooks and other products offering Windows XP. June 30 is not Microsoft's final deadline for all computer makers as they will sell licenses till Jan. 31, 2009.

Anyhow Windows XP won't disappear so quickly as its high performance and less resource usage compare to Windows Vista. Also some PC sellers have indicated that they are going to offer Windows XP Professional by taking advantage of Windows Vista's downgrade rights. But those PCs will also include Vista installation media allowing later upgrade.

It seems that Microsoft is more focusing on Windows Vista business, while they are working on a new operating system, currently called Windows 7. Like Windows Server 2008, Windows 7 also will be built upon Windows Vista foundation, particularly on subsystems such as graphics, audio, and storage. Windows 7 is expected to be released in 2010.

Here are some images I found claiming Windows 7 will look like this...

Microsoft DreamSpark

I accidently found this place called "Microsoft DreamSpark" from MSDN, where several Microsoft products giving for students at no cost. In their words,
"DreamSpark is simple, it's all about giving students Microsoft professional-level developer and design tools at no charge so you can chase your dreams and create the next big breakthrough in technology - or just get head start on your career"

But currently this is only for students in the United States, the United Kingdom, Canada, China, Germany, France, Finland, Spain, Sweden, Switzerland and Belgium. But they are saying that they are working on adding more countries, so keep checking it.

Here is the Microsoft DreamSpark link for more details.

Active Directory

Active Directory is a centralized and standardized system that automates network management of user data, security, and distributed resources, and enables interoperation with other directories. It is an implementation of LDAP directory services which is designed especially for distributed networking environments.

Its main purpose is to provide central authentication and authorization services for Windows based computers. Additionally it allows administrators to assign policies, maintain software, maintain user and group rights assignments, and apply critical updates to an organization.

Active Directory structure is a hierarchical framework of objects. These objects fall into three main categories

• Resources
• Services
• Users

The Active Directory provides information on the objects, organizes objects, controls access and sets security. In the Active Directory, an object is uniquely identified by its name and object has a set of attributes (characteristics and information that the object can contain) defined by a schema, which also limit the kind of objects that can be stored in the directory.

The framework that holds the objects is viewed at a number of levels. The top level of the structure is “Forest“. Forest is the collection of every object, its attributes, and rules in the directory. The forest holds one or more transitive, trust linked “Trees”. A tree holds one or more “Domains” and domain trees, again linked in a transitive trust hierarchy. Domains are identified by their DNS name structure, the namespace.

Features of Active Directory

• Support for the X.500 global directory standard
• Provide the capability for secure extension of network operations to the Web
• A hierarchical organization that provides a single point of access for system administration to reduce redundancy and errors
• Provide single logon capability
• An object oriented storage organization, which allows easier access to information
• Support for LDAP (Lightweight Directory Access Protocol) to enable inter-directory operability
• Designed to be both backward and forward compatible

How to install Active Directory in Windows Server 2003

This is a simple guide to install Active Directory with a Domain Controller and a new Domain in a Windows Server 2003 machine as a fresh installation. Before begin the installation make sure you have the Windows Server 2003 installation CD.

1. Go to Start -> Run

2. Type the command “dcpromo”, hit “Enter”

You will get “Active Directory Installation Wizard”

3. To continue, click “Next”

4. Click “Next” again after reading OS Compatibility

5. Here you will be given two options to create a “Domain Controller for a new domain” or to create “Additional domain controller for an existing domain”.

Assume here we are going to create a “Domain Controller for a new domain”. Select 1^st option and click “Next”

---

Domain controllers

When you create the first domain controller in your organization, you are also creating the first domain, the first forest, the first site, and installing Active Directory. Domain controllers running Windows Server 2003 store directory data and manage user and domain interactions, including user logon processes, authentication, and directory searches.

Determining the number of domain controllers you need

A small organization using a single local area network (LAN) might need only one domain with two domain controllers for high availability and fault tolerance. A larger organization with many network locations will need one or more domain controllers in each site.

---

6. Now you will get the “Create New Domain” window with 3 options to create a new “Domain in a new forest” or “Child domain in an existing domain tree” or “Domain tree in an existing forest”

Assume we are going to create a new “Domain in a new forest”. Select 1^st option and click “Next”

---

Domains

Domains are units of replication. All of the domain controllers in a particular domain can receive changes and replicate those changes to all other domain controllers in the domain. Each domain in Active Directory is identified by a Domain Name System (DNS) domain name and requires one or more domain controllers. If your network requires more than one domain, you can easily create multiple domains. One or more domains that share a common schema and global catalog are referred to as a forest. The first domain in a forest is referred to as the forest root domain.

---

7. You will be taking into “Install or Configure DNS” window with 2 options to “Configure the DNS client”, if DNS is already running on the network or to “Install and configure DNS on this computer”.

I’ll select second option here. So I have to Install and configure a DNS. Click “Next” to continue.

8. Give “Full DNS name for new domain” (Ex: “name.company.com”) and click “Next”

9. Give “NetBIOS Domain Name” (This is the name that users of earlier versions of Windows will use to identify the new domain) and click “Next.

10. Select locations to store “Database and Log Folders” (It is recommended to store these files in a separate hard disk). Click “Next”.

11. Select a location as a “Shared System Volume”. This is the place where server store domain’s public files. This location must contain NTFS file system.

12. You will be given two options to select “Permissions” with backward compatibility of operating systems up to Windows 2000 or including pre-Windows 2000 server operating systems. I select up to Windows 2000. Click “Next”

13. Provide a password for restore mode and confirm it.

14. Here you can see the summary of what have done so far. Click “Next” to continue.

15. Wait several minutes till system configure the Active Directory. You may ask to insert Windows Server installation CD while configuring. (Note: If your system has dynamically assigned IP address, configuration process will complain. So you must have to get a static IP for your machine)

16. You’re done. Congratulations.

Imagine Cup 2008 – Sri Lanka Finals

The long waiting event among university students/ high educational institutes of Sri Lanka, the Imagine Cup 2008 final round of the local competition happened yesterday. Once again a team from University of Moratuwa consisting 2 members from Faculty of Information Technology and 2 members from Faculty of Engineering won the competition. Well done team!!!.

2^nd Place (1^st runner-up) of the competition went to a project called "EDUMS" from Sri Lanka Institute of Information Technology (SLIIT) and 3^rd place again won by a project called "Cleaning License Management System" form University of Moratuwa, Faculty of Engineering.

This is the 4^th consecutive time a team from University of Moratuwa won the competition. Competition held on last 3 years, won by teams from University of Moratuwa, Faculty of Information Technology.

The winning team will represent Sri Lanka in the global finals in Paris, France. Congratulations team and bring the pride to Sri Lanka.

Well. That is one side of the story. On the other side can we satisfied with the overall standard with the event this time. In my personal view I see the standard become decreasing year by year. I know this is not the feeling only of me, as I discussed with other regular Imagine Cup fans, they too feeling this. I know organizing of this kind of event in this scale is not an easy task. But as a winner of 2006 competition I feel the standard must be persisted. The support given to presenting teams, specially the basic facilities was not up to the level and the time management of overall event is another concern. Priority must be given to presentations, to run them smoothly, because presentations are the expectation of audience. Wish the organizers too have noticed this and hope to see 2009 event in good quality.

Again well done teams…

Maximum RAM support for today Windows operating systems

This is the maximum RAM limit for some of commonly used operating systems as of now.

Version	Limit in 32-bit Windows	Limit in 64-bit Windows
Physical Memory Limits for Windows Vista
Windows Vista Ultimate	4 GB	128 GB
Windows Vista Enterprise	4 GB	128 GB
Windows Vista Business	4 GB	128 GB
Windows Vista Home Premium	4 GB	128 GB
Windows Vista Home Basic	4 GB	128GB
Physical Memory Limits: Windows Server
Windows Server 2008 Enterprise	64 GB	2TB
Windows Server 2008 Standard	4GB	32GB
Windows Server 2003 Service Pack 2, Enterprise Edition	64GB	2TB
Windows Server 2003 R2 Enterprise Edition	64GB	1TB
Windows Server 2003 R2 Standard Edition	4GB	32GB
Windows Server 2003, Enterprise Edition	32GB	64GB
Windows Server 2003, Standard Edition	4GB	16GB
Physical Memory Limits: Windows XP
Windows XP	4GB	128GB

Even though operating system limit of RAM is as above, the actual RAM can be installed is depend on your hardware as well, specially on Motherboard.

Complete list of maximum RAM support is available here at Microsoft site.

Maximum RAM limit in Windows XP / Vista

The maximum RAM supported in Windows XP (4GB) has become a subject to lot of discussions. The reason for this limit is address-apace limit in 32-bit operating systems. Even though it is said to be 4GB of maximum, you won’t actually get 4GB, because of part of address-space is used to PCI devices, Graphics card, etc. So it is only 3.23GB is shown in System Properties.

What about Maximum RAM limit in Windows Vista

Does Vista resolve the problem? Even though Windows Vista consumes lot of RAM compare to earlier operating systems, the maximum limit of RAM supported in Vista is also 4GB.

But some people may feel it is not enough 4GB of RAM, if they are used to run couple of VPCs in their workstation. The most suggested solution for this issue is move into 64-bit operating systems, which is not a practical option for most of us. (Because you need hardware upgrade too)

The other solution (for me the best solution) is to use a server operating system. Windows Server 2003, Enterprise Edition supports 32GB of RAM. Windows Server 2003 Service Pack 2 (SP2), Enterprise Edition supports 64GB of RAM. Please note that it is only Enterprise Edition supports this amount.

These operating systems support Intel-provided memory address extension called PAE (Physical Address Extension) to overcome address-space limit. Support for PAE is provided under Windows 2000 and 32-bit versions of Windows XP and Windows Server 2003. 64-bit versions of Windows do not support PAE.

Execute a JavaScript in PageLoad , when using custom controls in ASP.NET

I know that most of you are familiar with adding client side scripts in ASP.NET custom controls for various events, preferably for ‘onclick’ event of controls. The common way is adding script as an attribute of the control.

btnSubmit.Attributes.Add("onClick", "enableDateValidator();
document.getElementById('lblE').style.display='none';")


Here is the way to add a JavaScript, which will be run on pageload.

Protected Overrides Sub OnPreRender(ByVal e As System.EventArgs)

Dim script1 As String
script1 = " alert(‘Test’);"
Page.ClientScript.RegisterStartupScript(Me.GetType(), "EnableDisableControlScript1", script1)

End Sub

It is important to note that, if you are using ClientID of any control, implement the script inside the OnPreRender Overrides method. Otherwise you may face some problems with client side ID of your control.

Imagine Cup 2008 Sri Lanka - Software Design

CONGRATULATIONS... First Round WINNERS!

Team Name	Application Name	University/Faculty
Sasrutha	Haritha Prayathna	University of Moratuwa - Faculty of Information Technology/Faculty of Engineering
Will Power	Cleaning License Management System	University of Moratuwa - Computer Science and Engineering
Gagana	EDUMS	Sri Lanka Institute of Information Technology
e-C	e – cultivators	University of Colombo School of Computing
pehasara	GreenPlease	University of Moratuwa - Faculty of Information Technology
E – Co.	CarbonX	University of Colombo School of Computing

We are waiting for the Final Event on 22nd of April

Example Oracle Procedure with Cursors to loop inside and Table Types

PROCEDURE UpdateLeader(argMemId IN ChallengeMember.MemberId%TYPE) IS

CURSOR userChallenges IS
SELECT DISTINCT(CHALLENGEMEMBER.CHALLENGEID)
FROM CHALLENGEMEMBER, CHALLENGE
WHERE CHALLENGEMEMBER.MEMBERID = argMemId AND CHALLENGEMEMBER.CHALLENGEID = CHALLENGE.CHALLENGEID AND (CHALLENGE.CHALLENGESTATUSID = 2 OR CHALLENGE.CHALLENGESTATUSID = 3);

CURSOR ChallengeMembers (varCHALLENGEID CHALLENGEMEMBER.CHALLENGEID%TYPE) ISSELECT CHALLENGEMEMBER.MEMBERID AS MEMBERID,CHALLENGE.STARTDATE AS STARTDATE, CHALLENGE.ENDDATE AS ENDDATE
FROM CHALLENGEMEMBER, CHALLENGE
WHERE CHALLENGE.CHALLENGEID = varCHALLENGEID AND
CHALLENGE.CHALLENGEID = CHALLENGEMEMBER.CHALLENGEID AND
CHALLENGEMEMBER.MEMBERID <> 0;

vMemberPoints CHALLENGEMEMBER.POINTS%TYPE;
varMaxPoints CHALLENGEMEMBER.POINTS%TYPE;

varNotifyRequired NUMBER;

TYPE MAX_POINTS_REC IS RECORD
(MEMBER CHALLENGEMEMBER.MEMBERID%TYPE);

TYPE MAX_POINTS_TAB_TYPE IS TABLE OF MAX_POINTS_REC
INDEX BY BINARY_INTEGER;

MAX_POINTS_TAB MAX_POINTS_TAB_TYPE;
i NUMBER;
LEADER_CHANGE NUMBER;
VAR_ISLEADER NUMBER;
ISLEADER_RECS NUMBER;

BEGIN

FOR Challenge_REC IN userChallenges

LOOP
varMaxPoints := 0;
i := 1;

MAX_POINTS_TAB.DELETE;
LEADER_CHANGE := 1;

FOR Mem_REC IN ChallengeMembers(Challenge_REC.CHALLENGEID)

LOOP
vMemberPoints := 0;
vMemberPoints := nvl(CHALLENGEMEMBER_PKG.GetGHGByChallengeMem(Mem_REC.MEMBERID, Mem_REC.STARTDATE, Mem_REC.ENDDATE), 0);

IF (vMemberPoints > varMaxPoints) THEN
varMaxPoints := vMemberPoints;

MAX_POINTS_TAB.DELETE;
MAX_POINTS_TAB(1).MEMBER := Mem_REC.MEMBERID;

ELSIF (vMemberPoints = varMaxPoints AND vMemberPoints > 0) THEN
i := i +1;
MAX_POINTS_TAB(i).MEMBER := Mem_REC.MEMBERID;

END IF;
END LOOP;

SELECT NVL(COUNT(ISLEADER),0) INTO ISLEADER_RECS
FROM CHALLENGEMEMBER
WHERE CHALLENGEID = Challenge_REC.CHALLENGEID AND
ISLEADER = 1 ;

IF ISLEADER_RECS <> MAX_POINTS_TAB.COUNT THEN

UPDATE CHALLENGEMEMBER
SET UPDATENOTIFIED = 0
WHERE CHALLENGEMEMBER.CHALLENGEID = Challenge_REC.CHALLENGEID;

ELSE
FOR x IN 1 .. MAX_POINTS_TAB.COUNT
LOOP
SELECT ISLEADER INTO VAR_ISLEADER
FROM CHALLENGEMEMBER
WHERE CHALLENGEID = Challenge_REC.CHALLENGEID AND
MEMBERID = MAX_POINTS_TAB(x).MEMBER;

IF VAR_ISLEADER <> 1 THEN
UPDATE CHALLENGEMEMBER
SET UPDATENOTIFIED = 0
WHERE CHALLENGEMEMBER.CHALLENGEID = Challenge_REC.CHALLENGEID;
END IF;

END LOOP;

END IF;

UPDATE CHALLENGEMEMBER
SET ISLEADER = 0
WHERE CHALLENGEMEMBER.CHALLENGEID = Challenge_REC.CHALLENGEID;

FOR x IN 1 .. MAX_POINTS_TAB.COUNT
LOOP
UPDATE CHALLENGEMEMBER
SET ISLEADER = 1
WHERE CHALLENGEMEMBER.MEMBERID = MAX_POINTS_TAB(x).MEMBER AND
CHALLENGEMEMBER.CHALLENGEID = Challenge_REC.CHALLENGEID;

END LOOP;
END LOOP;

END UpdateLeader;

How to Install ColdFusion 8 on Linux?

Grant Execute permission to the Coldfusion‐8‐lin.bin binary file

chmod +x Coldfusion‐8‐lin.bin

Execute Coldfusion‐8‐lin.bin
./Coldfusion‐8‐lin.bin

Enter the Locale
1 (English)

Hit Enter 28 times to continue the agreement

Enter “Y” to accept the agreement

Enter the version to install
3 (Developer Edition)

Enter the next task to perform
1 (Server Configuration)

Enter whether you have an existing server configuration
2 (No existing server configuration)

Enter the next task to perform
5 (continue installation)

Enter the installation path
Hit enter to accept the default path

Hit Enter 30 times to continue the agreement

Enter “Y” to accept the agreement

Enter the serial number

Hit Enter for no serial number

Enter whether you have an existing CF installation
2 (no existing CF installation)

Enter the next task to perform
1 (Add Web Server Configuration)

Enter the web server to configure
1 (Apache)

Enter the path to the directory containing the Apache config file (httpd.conf)
/etc/httpd/conf

Enter the path to the Apache binary files
/usr/sbin/httpd

Enter the path to the Apache control file
/etc/init.d/httpd

Enter the next task to perform
4 (continue with the installation)

Enter the path to the web root

Hit enter to select the default location

Create a new Linux user

Enter the username / password

Confirm password

Enter whether you need to enable RDS
Y (Enable RDS)

Enter the password for RDS and confirm

Hit enter to exit the installer

When installation completes the installer will prompt you to start the ColdFusion server

Change directory to the ColdFusion binary directory
cd /opt/coldfusion8/bin

Start coldfusion8
./coldfusion start

There are three possible messages you will get
- Installation successful
- Installation failed
- Installation failed and retry for 12 times

If you are done with the installation successfully please ignore following instructions

Installer normally don’t update file paths correctly please edit following two files for the given file paths,

Files:
/opt/coldfusion8/bin/cf‐connectors.sh
/opt/coldfusion8/bin/connectors/apache_connector.sh

Paths:
Apache config file (httpd.conf): /etc/httpd/conf
Apache binary files: /usr/sbin/httpd
Apache control file: /etc/init.d/httpd

Check whether you have following file on the file system,
/opt/coldfusion8/runtime/lib/wsconfig/1/mod_jrun20.so

If Yes, go to label “Final”

If No, Continue with following instructions,

Grant permission to following three files (permissions should be same as the httpd file)

Files:
/opt/coldfusion8/bin/cf‐connectors.sh
/opt/coldfusion8/bin/connectors/apache_connector.sh
/opt/coldfusion8/runtime/lib/wsconfig.jar

Command:
chcon ‐‐reference=/usr/sbin/httpd \/opt/coldfusion8/bin/cf‐connectors.sh
chcon ‐‐reference=/usr/sbin/httpd \/opt/coldfusion8/bin/connectors/apache_connector.sh
chcon ‐‐reference=/usr/sbin/httpd \/opt/coldfusion8/runtime/lib/wsconfig.jar

Start ColdFusion Server

Change directory to the ColdFusion binary directory
cd /opt/coldfusion8/bin

Restart coldfusion8
./coldfusion stop
./coldfusion start

Check whether you have following file on the file system,
/opt/coldfusion8/runtime/lib/wsconfig/1/mod_jrun20.so

Yes it should be in the file system. Continue with the following instructions,


Final:

Grant permission to following file (permissions should be same as the httpd file)

File:
/opt/coldfusion8/runtime/lib/wsconfig/1/mod_jrun20.so

Command:
chcon ‐‐reference=/usr/sbin/httpd \/opt/coldfusion8/runtime/lib/wsconfig/1/mod_jrun20.so

Restart coldfusion8
./coldfusion stop
./coldfusion start

Start the apache server

You are done with the installation

Access the site http://localhost/CFIDE/administrator/login.cfm

Continue the wizard and you are ready to use the ColdFusion server.
Congratulations!

Thanks, Darshatha and Gayan for preparing this document. Hope it will help you all.

Big Oracle Procedure as an Example

PROCEDURE GetMapDetails(argMemberId IN Member.MemberId%TYPE, argProvinceId IN Member.POSTALCODEPROVINCEID%TYPE, argMapDetails OUT refCursorType)
IS

totalMemPoints1 NUMBER;
totalMemPoints2 NUMBER;
totalMemPoints NUMBER;
totalProvPoints1 NUMBER;
totalProvPoints2 NUMBER;
totalProvPoints NUMBER;
totalConPoints1 NUMBER;
totalConPoints2 NUMBER;
totalConPoints NUMBER;

BEGIN

SELECT SUM (ACTION_X_PROVINCE.POINTSPERDAY * COMMITMENT.QUENTITY * (COMMITMENT.ENDDATE - COMMITMENT.STARTDATE)) INTO totalMemPoints1
FROM ACTION_X_PROVINCE, COMMITMENT, MEMBER, dual
WHERE COMMITMENT.MEMBERID = argMemberId AND COMMITMENT.ACTIONID = ACTION_X_PROVINCE.ACTIONID AND MEMBER.MEMBERID = argMemberId AND MEMBER.POSTALCODEPROVINCEID = ACTION_X_PROVINCE.PROVINCEID
AND COMMITMENT.ENDDATE < sysdate AND COMMITMENT.STARTDATE < sysdate AND COMMITMENT.ENDDATE > COMMITMENT.STARTDATE;

SELECT SUM (ACTION_X_PROVINCE.POINTSPERDAY * COMMITMENT.QUENTITY * (round((sysdate - COMMITMENT.STARTDATE)-0.5))) INTO totalMemPoints2
FROM ACTION_X_PROVINCE, COMMITMENT, MEMBER, dual
WHERE COMMITMENT.MEMBERID = argMemberId AND COMMITMENT.ACTIONID = ACTION_X_PROVINCE.ACTIONID AND MEMBER.MEMBERID = argMemberId AND MEMBER.POSTALCODEPROVINCEID = ACTION_X_PROVINCE.PROVINCEID
AND COMMITMENT.ENDDATE > sysdate AND COMMITMENT.STARTDATE < sysdate;

SELECT SUM (ACTION_X_PROVINCE.POINTSPERDAY * COMMITMENT.QUENTITY * (COMMITMENT.ENDDATE - COMMITMENT.STARTDATE)) INTO totalProvPoints1
FROM ACTION_X_PROVINCE, COMMITMENT, MEMBER, dual
WHERE MEMBER.POSTALCODEPROVINCEID = argProvinceId AND MEMBER.MEMBERID = COMMITMENT.MEMBERID
AND COMMITMENT.ACTIONID = ACTION_X_PROVINCE.ACTIONID AND ACTION_X_PROVINCE.PROVINCEID = argProvinceId
AND COMMITMENT.ENDDATE < sysdate AND COMMITMENT.STARTDATE < sysdate AND COMMITMENT.ENDDATE > COMMITMENT.STARTDATE;

SELECT SUM (ACTION_X_PROVINCE.POINTSPERDAY * COMMITMENT.QUENTITY * (round((sysdate - COMMITMENT.STARTDATE)-0.5))) INTO totalProvPoints2
FROM ACTION_X_PROVINCE, COMMITMENT, MEMBER, dual
WHERE MEMBER.POSTALCODEPROVINCEID = argProvinceId AND MEMBER.MEMBERID = COMMITMENT.MEMBERID
AND COMMITMENT.ACTIONID = ACTION_X_PROVINCE.ACTIONID AND ACTION_X_PROVINCE.PROVINCEID = argProvinceId
AND COMMITMENT.ENDDATE > sysdate AND COMMITMENT.STARTDATE < sysdate;

SELECT SUM (ACTION_X_PROVINCE.POINTSPERDAY * COMMITMENT.QUENTITY * (COMMITMENT.ENDDATE - COMMITMENT.STARTDATE)) INTO totalConPoints1
FROM ACTION_X_PROVINCE, COMMITMENT, MEMBER
WHERE MEMBER.COUNTRYID = 1 AND MEMBER.MEMBERID = COMMITMENT.MEMBERID
AND COMMITMENT.ACTIONID = ACTION_X_PROVINCE.ACTIONID AND ACTION_X_PROVINCE.PROVINCEID = MEMBER.POSTALCODEPROVINCEID
AND COMMITMENT.ENDDATE < sysdate AND COMMITMENT.STARTDATE < sysdate AND COMMITMENT.ENDDATE > COMMITMENT.STARTDATE;

SELECT SUM (ACTION_X_PROVINCE.POINTSPERDAY * COMMITMENT.QUENTITY * (round((sysdate - COMMITMENT.STARTDATE)-0.5))) INTO totalConPoints2
FROM ACTION_X_PROVINCE, COMMITMENT, MEMBER, dual
WHERE MEMBER.COUNTRYID = 1 AND MEMBER.MEMBERID = COMMITMENT.MEMBERID
AND COMMITMENT.ACTIONID = ACTION_X_PROVINCE.ACTIONID AND ACTION_X_PROVINCE.PROVINCEID = MEMBER.POSTALCODEPROVINCEID
AND COMMITMENT.ENDDATE > sysdate AND COMMITMENT.STARTDATE < sysdate;

IF (totalMemPoints1 > 0 AND totalMemPoints2 > 0) THEN
SELECT totalMemPoints1 + totalMemPoints2 INTO totalMemPoints FROM dual;
ELSIF (totalMemPoints1 > 0) THEN
SELECT totalMemPoints1 INTO totalMemPoints FROM dual;
ELSIF (totalMemPoints2 > 0) THEN
SELECT totalMemPoints2 INTO totalMemPoints FROM dual;
ELSE
SELECT '-1' INTO totalMemPoints FROM dual;
END IF;

IF (totalProvPoints1 > 0 AND totalProvPoints2 > 0) THEN
SELECT totalProvPoints1 + totalProvPoints2 INTO totalProvPoints FROM dual;
ELSIF (totalProvPoints1 > 0) THEN
SELECT totalProvPoints1 INTO totalProvPoints FROM dual;
ELSIF (totalProvPoints2 > 0) THEN
SELECT totalProvPoints2 INTO totalProvPoints FROM dual;
ELSE
SELECT '-1' INTO totalProvPoints FROM dual;
END IF;

IF (totalConPoints1 > 0 AND totalConPoints2 > 0) THEN
SELECT totalConPoints1 + totalConPoints2 INTO totalConPoints FROM dual;
ELSIF (totalConPoints1 > 0) THEN
SELECT totalConPoints1 INTO totalConPoints FROM dual;
ELSIF (totalConPoints2 > 0) THEN
SELECT totalConPoints2 INTO totalConPoints FROM dual;
ELSE
SELECT '-1' INTO totalConPoints FROM dual;
END IF;

OPEN argMapDetails FOR SELECT '&' || PROVINCE.PROVINCECODE || 'Members=' || COUNT(MEMBER.MEMBERID) AS AllCanada
FROM MEMBER,PROVINCE
WHERE MEMBER.POSTALCODEPROVINCEID = PROVINCE.PROVINCEID AND PROVINCE.PROVINCECODE IS NOT NULL
GROUP BY PROVINCE.PROVINCECODE
UNION
SELECT '&' || PROVINCE.PROVINCECODE || 'Members=0'
FROM MEMBER,PROVINCE
WHERE PROVINCE.PROVINCECODE NOT IN (SELECT PROVINCE.PROVINCECODE FROM MEMBER,PROVINCE WHERE MEMBER.POSTALCODEPROVINCEID = PROVINCE.PROVINCEID AND PROVINCE.PROVINCECODE IS NOT NULL GROUP BY PROVINCE.PROVINCECODE)
GROUP BY PROVINCE.PROVINCECODE
UNION
SELECT '&' || PROVINCE.PROVINCECODE || 'Name=' || PROVINCE.NAME FROM PROVINCE
UNION
SELECT '&' || PROVINCE.PROVINCECODE || 'Actions=' || COUNT(COMMITMENT.ACTIONID)
FROM PROVINCE, COMMITMENT,MEMBER
WHERE PROVINCE.PROVINCEID = MEMBER.POSTALCODEPROVINCEID AND MEMBER.MEMBERID = COMMITMENT.MEMBERID AND PROVINCE.PROVINCECODE IS NOT NULL
GROUP BY PROVINCE.PROVINCECODE
UNION
SELECT '&' || PROVINCE.PROVINCECODE || 'Actions=0'
FROM PROVINCE, COMMITMENT, MEMBER
WHERE PROVINCE.PROVINCECODE NOT IN (SELECT PROVINCE.PROVINCECODE FROM PROVINCE, COMMITMENT, MEMBER WHERE PROVINCE.PROVINCEID = MEMBER.POSTALCODEPROVINCEID AND MEMBER.MEMBERID = COMMITMENT.MEMBERID AND PROVINCE.PROVINCECODE IS NOT NULL GROUP BY PROVINCE.PROVINCECODE)
GROUP BY PROVINCE.PROVINCECODE
UNION
SELECT '&YourPersonalSavings=' || TO_CHAR(ROUND(totalMemPoints,2)) FROM DUAL
UNION
SELECT '&YourProvince=' || PROVINCE.NAME FROM PROVINCE, MEMBER WHERE PROVINCE.PROVINCEID = MEMBER.POSTALCODEPROVINCEID AND MEMBER.MEMBERID = argMemberId
UNION
SELECT '&YourProvincialSavings=' || TO_CHAR(ROUND(totalProvPoints,2)) FROM DUAL
UNION
SELECT '&TotalCanadaSavings=' || TO_CHAR(ROUND(totalConPoints)) FROM DUAL
UNION
SELECT '&TotalGreenhouse=' || TO_CHAR(ROUND(totalConPoints)) FROM DUAL
UNION
SELECT '&TotalCanidians=' || TO_CHAR(COUNT(MEMBER.MEMBERID)) FROM MEMBER WHERE COUNTRYID = 1
UNION
SELECT '&DataLoaded=Done' FROM DUAL;

END GetMapDetails;