Showing posts with label Programming. Show all posts
Showing posts with label Programming. Show all posts

Tuesday, March 22, 2011

Upload image to Picture Library using SharePoint 2010 Web Services

We can use http:///_vti_bin/Copy.asmx Web service to upload files to a SharePoint document library or a picture library from another list. Here we use "GetItem" method to download files from the source list and "CopyIntoItems" method to upload files to the destination list. But there is a limitation that you can't copy between locations that are not on the same Web Application directly.

Following is a workaround for that limitation using two references to the source and destination sites. In this way it is possible to post an image from a SharePoint server located in one domain to another SharePoint server located in a separate domain.

In here “IntranetBlogCopyService” is a Web Reference to the http://moss2010/sites/IntranetBlog/_vti_bin/Copy.asmx service and “InternetBlogCopyService” is a a Web Reference to the ‘http://prasadmoss.domainx.local/sites/InternetBlog/_vti_bin/Copy.asmx” service.


///
/// Initialize Intranet Blog Copy Service.
///

private IntranetBlogCopyService.Copy InitializeIntranetBlogCopyService()
{
IntranetBlogCopyService.Copy intranetBlogCopyService = new IntranetBlogCopyService.Copy();
intranetBlogCopyService.Credentials = CredentialCache.DefaultCredentials;
intranetBlogCopyService.Url = "http://moss2010/sites/IntranetBlog/_vti_bin/Copy.asmx";
return intranetBlogCopyService;
}


///
/// Initialize Internet Blog Copy Service.
///

private InternetBlogCopyService.Copy InitializeInternetBlogCopyService()
{
InternetBlogCopyService.Copy internetBlogCopyService = new InternetBlogCopyService.Copy();
internetBlogCopyService.Credentials = CredentialCache.DefaultCredentials;
internetBlogCopyService.Url = "http://prasadmoss.domainx.local/sites/InternetBlog/_vti_bin/Copy.asmx";
return internetBlogCopyService;
}


///
/// Copy image between two Picture Libraries.
///

private void PostAnImageFromIntranetSiteToInternetSite()
{
IntranetBlogCopyService.Copy intranetBlogCopyService = InitializeIntranetBlogCopyService();
InternetBlogCopyService.Copy internetBlogCopyService = InitializeInternetBlogCopyService();

string sourceAbsoluteUrl = "http://moss2010/sites/InternetBlog/Lists/Photos/image01.png";
string[] destinationAbsoluteUrls = { "http://prasadmoss/sites/InternetBlog/Lists/Photos/image01.png" };

IntranetBlogCopyService.FieldInformation intranetFieldInfo = new IntranetBlogCopyService.FieldInformation();
IntranetBlogCopyService.FieldInformation[] intranetFieldInfoArray = { intranetFieldInfo };
byte[] imageByteArray;
uint getUint = intranetBlogCopyService.GetItem(sourceAbsoluteUrl, out intranetFieldInfoArray, out imageByteArray);

// Create FieldInformation of InternetCopyService type.
InternetBlogCopyService.FieldInformation internetFieldInfo = new InternetBlogCopyService.FieldInformation();

// Copy individual fields.
internetFieldInfo.DisplayName = intranetFieldInfoArray[0].DisplayName;
internetFieldInfo.InternalName = intranetFieldInfoArray[0].InternalName;
internetFieldInfo.Value = intranetFieldInfoArray[0].Value;
internetFieldInfo.Id = intranetFieldInfoArray[0].Id;

InternetBlogCopyService.FieldInformation[] internetFieldInfoArray = { internetFieldInfo };

InternetBlogCopyService.CopyResult copyResult1 = new InternetBlogCopyService.CopyResult();
InternetBlogCopyService.CopyResult[] copyResultArray = { copyResult1 };

try
{
uint myCopyUint = internetBlogCopyService.CopyIntoItems(sourceAbsoluteUrl, destinationAbsoluteUrls, internetFieldInfoArray, imageByteArray, out copyResultArray);
if (myCopyUint == 0)
{
int idx = 0;
foreach (InternetBlogCopyService.CopyResult myCopyResult in copyResultArray)
{
string opString = (idx + 1).ToString();
if (copyResultArray[idx].ErrorMessage == null)
{
// Copy operation success.
}
else
{
// Copy operation failed.
}
idx++;
}
}
}
catch (Exception ex)
{
// Error
}
}

Wednesday, March 9, 2011

IIS URL Rewrite Module - Redirect HTTP to HTTPS with IIS 7

Using IIS URL Rewrite Module, Web administrators can easily set up rules to define URL rewriting behavior based on HTTP headers, HTTP response or request headers, IIS server variables, and programmatic rules. Here the main purpose is to implement URLs that are easier for users to remember and easier for search engines to find.

In addition, we can define rules to force SSL on selected pages of a Website which is hosted in IIS 7. First of all we need to install Microsoft URL Rewrite Module in IIs. Then we need to install SSL certificate, create HTTPS bindings to our IIS Web site and assign the certificate. Then select our Web site under Sites node and make sure "Require SSL" is NOT checked under SSL Settings.

After that we have to add following config section to the web.config file in the Web site root directory.
















In here a rewrite rule is defined by specifying a pattern to use for matching the URL string and an action to perform if a pattern is matched. In addition an optional condition is also specified which will be checked in the matching step.

Thanks Carlos Redondo for your suggestion!

Friday, February 4, 2011

How to Configure SSL on Particular Pages of an IIS 7 Website

Today I wanted to force SSL on selected pages of a Website which is hosted in IIS 7. I could find lot of articles in the Web describing how to configure SSL for the whole website. But I needed it for few specific pages only. This is the way finally I achieved my goal.

Using Server Certificates feature of the IIS server install my certificate.
Create a SSL Binding for my Web site and make sure all the pages are accessible via both HTTP and HTTPS.

Then in order to force SSL on selected pages I used following method on Page_Load event of those pages. In here I used Request.ServerVariables collection to see if the protocol being used is HTTP or HTTPS.


protected void Page_Load(object sender, EventArgs e)
{
// Redirect to the corresponding secure page.
RedirectToSecurePage();
}




///
/// Redirect to the corresponding secure page.
/// Assumption: IIS web site is configured in port 80.
///

public void RedirectToSecurePage()
{
var httpsMode = string.Empty;
var serverName = string.Empty;
var url = string.Empty;

for (var i = 0; i < Request.ServerVariables.Keys.Count; i++)
{
var key = Request.ServerVariables.Keys[i];
if (key.Equals("HTTPS"))
{
httpsMode = Request.ServerVariables[key];
}
else if (key.Equals("SERVER_NAME"))
{
serverName = Request.ServerVariables[key];
}
else if (key.Equals("URL"))
{
url = Request.ServerVariables[key];
}
}
if (httpsMode.Equals("off"))
{
Response.Redirect(string.Concat("https://", serverName, url));
}
}


So when each page is browsed, the code that is contained in the Page_Load event detects if HTTP is used. If HTTP is used, the browser will be redirected to the same page by using HTTPS.

Comments are really appreciated on how to handle this scenario in another (better) way...


Wednesday, December 1, 2010

SharePoint - CRM Integration: Create a SharePoint Document Library on CRM Opportunity Creation

In CRM 4.0 there is no way to maintain a set of related documents for Opportunities created within the CRM. Therefore it is a good idea to integrate CRM and SharePoint here, so that for each Opportunity creation in CRM a document library will get created in a specified SharePoint site automatically. We can achieve this task by registering a plugin to execute in Opportunity Create step.

Plugin implementation

Add a Web Reference to the Lists service of the SP site (or sub-site) located in "/_vti_bin/Lists.asmx"

Document library creation method could be like this.


///<summary>
/// Create a SP document library for a specified Opportunity.
///</summary>
private string CreateDocLibrary(string opportunityName, string spSiteUrl)
{
ListsService.Lists listService = new ListsService.Lists();
listService.Url = String.Concat(spSiteUrl, "/_vti_bin/Lists.asmx");
listService.Credentials = CredentialCache.DefaultCredentials;

XmlNode xnodeList = listService.AddList("Opportunity-" + opportunityName,
opportunityName, 101);

// Get document library URL name.
string rootFolder = xnodeList.Attributes.GetNamedItem("RootFolder").Value;
return rootFolder.Remove(0, rootFolder.LastIndexOf("/"));
}

Call document library creation method with proper parameters and maintain information about new document libraries in another custom SharePoint list.


public void Execute(IPluginExecutionContext context)
{
string opportunityName;
string stateCode;

if (context.MessageName.Equals("Create"))
{
opportunityName = ((DynamicEntity)context.PostEntityImages["Target"]).
Properties["name"].ToString();
stateCode = ((DynamicEntity)context.PostEntityImages["Target"]).
Properties["statecode"].ToString();
string docLibraryUrl = CreateDocLibrary(opportunityName,
"http://prasadmoss2010/sites/CRMTest");
AddConfigurationData(opportunityName, "http://prasadmoss2010/sites/CRMTest");
}
}

///<summary>
/// Update configuartion data custom list.
///</summary>
private void AddConfigurationData(string listName, string spSiteUrl)
{
ListsService.Lists listService = new ListsService.Lists();
listService.Url = String.Concat(spSiteUrl, "/_vti_bin/Lists.asmx");
listService.Credentials = CredentialCache.DefaultCredentials;

// Get Name attribute values (GUIDs) for list and view.
XmlNode node = listService.GetListAndView("CustomListName", "");
string listID = node.ChildNodes[0].Attributes["Name"].Value;
string viewID = node.ChildNodes[1].Attributes["Name"].Value;

// Add configuration entry - Construct a Batch element and its attributes.
XmlDocument doc = new XmlDocument();
XmlElement batch = doc.CreateElement("Batch");

batch.SetAttribute("OnError", "Continue");
batch.SetAttribute("ListVersion", "1");
batch.SetAttribute("ViewName", viewID);

// Specify update method for the batch post using CAML,
batch.InnerXml = "<Method ID='1' Cmd='New'>" +
"<Field Name='Title'>" + listName + "</Field>" +
"<Field Name='ProposalStatus'>Open</Field></Method>";

// Update SP list item using the list GUID.
try
{
XmlNode xnodeList = listService.UpdateListItems(listID, batch);
}
catch (Exception) {}
}

In addition, Opportunity status maintained in the custom SharePoint list can be updated when the opportunity is marked as a closed opportunity. For that purpose same plugin can be extended to execute on Win or Lose messages

(Ex: "context.MessageName.Equals("Lose")")

by registering plugin for the "Win" and "Lose" steps. In order to capture revert updates (Reopen opportunity), plugin should be registered for the "SetStateDynamicEntity" step as well.

Tuesday, June 15, 2010

Issue in Embedding a Flash Image Rollup – SharePoint 2010

My goal is to embed a Flash control in a SharePoint site that displays several images iteratively. So one approach is, upload the .SWF file and the images to a web location, preferably a library in the same SharePoint site. Then can develop a custom WebPart control or use content editor WebPart to include following code segment.

<div>
<object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000"
codebase="http://download.macromedia.com/pub/shockwave/
cabs/flash/swflash.cab#version=9,0,28,0"

width="310" height="160">
<param name="movie" value="/FlashImages/ControlFiles/abc.swf" />
<param name="quality" value="high" />
<param name="flashvars" value="configXML=/FlashImages/ControlFiles/data.xml"/>
<embed src="/FlashImages/ControlFiles/abc.swf"
flashvars="configXML=/FlashImages/ControlFiles/data.xml"
width="310" height="160" quality="high"
pluginspage="http://www.adobe.com/shockwave/
download/download.cgi?P1_Prod_Version=ShockwaveFlash"

type="application/x-shockwave-flash">
</embed>
</object>
</div>

In here data.xml contains the URLs of the images to be displayed, which is taken as a parameter by the swf. Basically that is it. Flash control should work properly. But we need one more additional step in order to flash control to work as expected. That is we have to set “Browser File Handling” to
“Permissive”. By default this option is set to “Strict”, which prevents flash control performing as expected.

So in SharePoint Central Administration;

  • Go to “Application Management” > “Manage web applications”
  • Select your Web Application and go to “General Settings” > “General Settings”


  • Select “Permissive” option in the Browser File Handling section as following screen shot.




Sunday, May 2, 2010

Architecture Modeling in VS 2010

Microsoft Visual Studio has now become a good Integrated Requirements, and Architecture and Modeling tool by embracing Unified Modeling Language (UML) into VS 2010, thereby making architecture, design, development, and testing seamlessly possible with its IDE. Visual Studio 2010 has support for following UML 2.1.2 diagramming types;
  • Class diagram
  • Sequence diagram
  • Use Case diagram
  • Activity diagram
  • Component diagram
The "Modeling Project" type is used for the architecture modeling. Here Activity and Use case diagrams can be used for requirements modeling. Then Layer diagrams can be used to represent the layered view of the system. If the system is component oriented, subsystems or components are represented using component diagram. Note: Code generation capability is added to the sequence and class diagrams only.

Thursday, January 15, 2009

How to Add a Tooltip to a Dropdown List

It is common in most web pages that whole text in dropdown lists cannot be seen due to width limitation. One solution for this would be adding tooltip for the dropdown, so that when selecting a value from dropdown, whole text will be appear as a tooltip. For most of browsers implementing tooltip on dropdown control is not a big deal. We can simply add tooltip text with 'title' attribute. C# implementation for this would be as follows;

public static void BindTooltip(DropDownList ddl) {
for (int i = 0; i < ddl.Items.Count; i++)
{
ddl.Items[i].Attributes.Add("title", ddl.Items[i].Text);
}
}

But this will not work in Internet Explorer version 6, as IE V6 does not support the 'title' attribute on the <select> control. (IE 7, Firefox … support the 'title' attribute). This is because IE 6 creates a separate window/layer for the <select> tag that is above the rest of the browser window. And Tooltip property is also not working in IE V6.

A partial solution for this issue is add a separate <div> to the form, set the required text, and show/hide that <div> based on mouse activity over dropdown list. Anyway this method will not show the tooltip when selecting an item from the dropdown list. But once you selected an item and move the mouse over dropdown list, it will show the tool tip. Here is the implementation;

DropDown
ddlNames.ID = "DDL_NAMES_ID";
ddlNames.Width = 100;
ddlNames.DataSource = dtNames;
ddlNames.DataTextField = "Full_Name";
ddlNames.DataValueField = "Reg_ID";
ddlNames.DataBind();
ddlNames.Attributes.Add("onmouseover", "showDropDownToolTip(this);");
ddlNames.Attributes.Add("onmouseout", "hideDropDownToolTip();");

DIV
<div id="divDropDownToolTip" style="position:absolute; display:none; background:lightyellow; border:1px solid gray; padding:2px; font-size:8pt; font-family:Verdana;" onMouseOut
="hideDropDownToolTip()">
<span id="toolTipText"></span>
</div>

Java Script
protected override void OnPreRender(EventArgs e)
{
string script = string.Concat(@"
<script type=""text/javascript"" language=""javascript"">
function showDropDownToolTip(ddl){
if ( ddl.options[ddl.selectedIndex].value == '')
return;
var toolTipSpan = document.getElementById('toolTipText');
toolTipSpan.innerHTML = ddl.options[ddl.selectedIndex].text;
var toolTipDiv = document.getElementById('divDropDownToolTip');
toolTipDiv.style.top = window.event.clientY + 140;
toolTipDiv.style.left = window.event.clientX;
toolTipDiv.style.display = 'block';
}

function hideDropDownToolTip()
{
document.getElementById('divDropDownToolTip').style.display = 'none';
}
</script>");

Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "NamesToolTipControl", script);
}

Thursday, January 1, 2009

Passing an Event to the Parent Control by Using Delegates

For the purpose implementation of inner control should implement event handler, custom argument class and delegate. Custom argument class is used to pass inner control specific values to the container control.

using System.Web.UI.WebControls;

namespace InteractiveControls{
// Interactive Control
public class MyInteractiveControl : CompositeControl
{
/// <summary>
/// Event for delegate method
/// </summary>
public event ShowClickEventHandler ShowClicked;

/// <summary>
/// Button control
/// </summary>
private Button btnShow;

/// <summary>
/// CreateChildControls is responsible for creating the controls to form this composite control
/// </summary>
protected override void CreateChildControls()
{
this.btnShow = new
Button();
this.btnShow.Text = "Show";
this.btnShow.Click += new
EventHandler(btnShow_Click);
this.Controls.Add(btnShow);
}

private void btnShow_Click(object sender, EventArgs e)
{
OnShowClick(new ShowClickEventArgs(25, 100));
}
void OnShowClick(ShowClickEventArgs e)
{
ShowClicked(this, e);
}
}

/// <summary>
/// Delegate method for Show button clicked event handler
/// </summary>
public delegate void ShowClickEventHandler(object sender, ShowClickEventArgs e);
/// <summary>
/// Event argument class for ShowClickEventArgs
/// </summary>
public class ShowClickEventArgs : EventArgs
{
private int m_From;
private int m_To;
public ShowClickEventArgs(int from, int to)
{
this.m_From = from;
this.m_To = to;
}
public int From
{
get { return m_From; }
}
public int To
{
get { return m_To; }
}
}
}

'MyInteractiveControl_ShowClicked' method in the container control will be invoked in 'Show' button click, which is residing in the inner control. By implementing custom argument class appropriately, can access values in the interactive control from container control.

namespace ContainerControls
{
// Container Control
public class MyInteractiveControl : CompositeControl
{
protected override void CreateChildControls()
{
MyInteractiveControl ctrl = new MyInteractiveControl();
ctrl.ShowClicked += new ShowClickEventHandler(MyInteractiveControl_ShowClicked);
this.Controls.Add(ctrl);
}

/// <summary>
/// Event fires when the Show button clicked
/// </summary>
private void MyInteractiveControl_ShowClicked(object sender, ShowClickEventArgs e)
{
Controls.Add(new
LiteralControl(string.Concat("From:", e.From.ToString(), " - To:", e.To.ToString())));
}
}
}

More about delegates.....

Tuesday, December 30, 2008

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.

Wednesday, July 9, 2008

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)

Tuesday, April 15, 2008

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.

Friday, February 22, 2008

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;


Tuesday, January 8, 2008

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;