�ݺ�ߣ

shangbaby@gmail.com
818-235-9779
http://www.shangbaby.net/joomlajoy

?MSSQL SERVER user stored procedure
?SSRS �C Customer Transaction Summary
?HTML Form �C The HTML behind it!
?PHP Form Validation �C The PHP behind it!

? This particular piece of code creates a stored procedure. Upon
execution it initially validates that the customer is linked to
the account.

? There are two other validations that has to do with entry of
negative numbers in the ��TransactionAmount�� field and
adding an overdraft service charge when there isn?t any funds
to cover the transaction.

Microsoft SQL
? USE [PiggyBank]
? GO
? /* Object: StoredProcedure [dbo].[WithdrawalODFees] Script Date: 02/24/2011 13:23:09 */
? SET ANSI_NULLS ON
? GO
? SET QUOTED_IDENTIFIER ON
? GO
? ---- =============================================
? ---- Author: Shangz Brown
? ---- Create date: 02/22/2011
? ---- Description Withdrawal with overdraft fee generation
? ---- =============================================
? CREATE PROCEDURE [dbo].[WithdrawalODFees]
? --Input and output parameters to create account
? @CustomerID int = null,
? @AccountID int = null,
? @TransactionAmount money = null,
? @TransactionTypeID int = null,
? @NewBalance money OUTPUT
? AS
? SET XACT_ABORT ON
? --Validate that @AccountID belongs to the same customer (linked in customer Accounts table)
? IF (SELECT CustomerAccountID
? FROM CustomerAccount
? WHERE AccountID = @AccountID
and CustomerID = @CustomerID) is null
? BEGIN
? raiserror('Account must be linked to the CustomerID entered', 11, 1)
? RETURN
? END

This part of the T-SQL stored procedure code blocks
activity on closed accounts that may be entered by users
? --Block Transactions on inactive accounts.
? IF (Select A.AccountID
? FROM Account AS A
? JOIN CustomerAccount AS CA
? ON A.AccountID=CA.AccountID
? WHERE AccountStatusID=2
? AND CA.CustomerID=@CustomerID
? AND A.AccountID =@AccountID) =@AccountID
? BEGIN
? raiserror('Transactions are not allowed on inactive accounts', 11, 1)
? RETURN
? END
? --D/E CHECK: Prevent negative money amounts to be entered in the Transaction Amount parameter.
? --Logic: You can not make a deposit via the withdraw proc.
? IF @TransactionAmount <=0
? BEGIN
? raiserror('Transaction Amount can not be a negative number.', 11, 1)
? RETURN
? END
? BEGIN
? -- SET NOCOUNT ON added to prevent extra result sets from
? -- interfering with SELECT statements.
? SET NOCOUNT ON;

? -- Encapsulate procedure in a try catch block
? BEGIN TRY
? BEGIN TRAN
? --make insert conditional
? -- If TA >CB, AND test if EXIST [OD] is true, if TRUE proceed.
? IF @TransactionAmount >
? (SELECT CurrentBalance
? FROM Account
? WHERE AccountID =@AccountID)
? AND exists (SELECT AccountID
? FROM Account
? WHERE OverDraftAccountID is not null)
? SET @NewBalance =
? (SELECT CurrentBalance
? FROM Account
? WHERE AccountID = @AccountID) - (@TransactionAmount+10)
? -- record transaction.
? INSERT INTO [PiggyBank].[dbo].[Transactions]
? ([AccountID]
? ,[TransactionTypeID]
? ,[CustomerID]
? ,[TransactionDate]
? ,[TransactionAmount]
? ,[NewBalance])
?

? VALUES
? (@AccountID
? ,@TransactionTypeID
? ,@CustomerID
? ,GETDATE()
? ,@TransactionAmount
? ,@NewBalance)
? UPDATE dbo.Account
? SET CurrentBalance =@NewBalance
? WHERE AccountID = @AccountID
? Print 'Not enough funds in main account to cover transaction.
? Transaction amount + $10 service fee HAS BEEN debited from this account'
? COMMIT TRAN
? END TRY
? --Catch errors
? BEGIN CATCH
? --Rollback transaction
? IF @@trancount > 0
? ROLLBACK TRAN
? DECLARE @ErrorMessage NVARCHAR(4000),
? @ErrorState INT
? SELECT @ErrorMessage = ERROR_MESSAGE(),
? @ErrorState = ERROR_STATE()
? RAISERROR ( @ErrorMessage, 11, @ErrorState)
? RETURN
? END CATCH
? END
? GO

SSRS �C SQL Server Reporting Services �C This is a report I created using Visual Studio
2008. The source was a stored procedure in my PiggyBank project. I wanted to create a
statement listing the transactions along with customer name and address. The result
follows on the next slide.

? <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<metahttp-equiv="content-type" content="text/html; charset=iso-8859-1" />
<title>Simple HTML Form</title>
</head>
<body>

<form action="handle_form.php" method="post">

<fieldset><legend>Enter your information in the form below:</legend>

<p> <b>Name:</b>
? <input type="text" name="name" size="20�� maxlength="40" />
? </p>
<p> <b> Email Address: </b>
? <input type="text" name="email" size="40�� maxlength="60" />
? </p>
<p> <b>Gender:</b>
? <input type="radio" name="gender" value="M" />Male
? <input type="radio" name="gender" value="F" /> Female
? </p>
<p> <b>Age:</b>
<select name="age">
<option value="0-29"> Under 30 </option>
<option value="30-60"> Between 30 and 60 </option>
<option value="60+"> Over 60 </option>
</select>
? </p>
<p> <b>Comments:</b>
? <textarea name="comments" rows="3" cols="40"></textarea>
? </p>
</fieldset>
<div align="center"><input type="submit" name="submit" value="Submit My Information" />
</div>
</form>
</body>

? <html>
? <head>
? <title>PHP Test</title>
? </head>
? <body>
? <?php # handle_form.php
? // Create a shorthand for the form data.
? $name = $_REQUEST['name'];
? $email = $_REQUEST['email'];
? $comments = $_REQUEST['comments'];
? $age = $_REQUEST['age'];
? $gender = $_REQUEST['gender'];
? //Validate the name and combat Magic Quotes, if necessary.
? if (!empty($_REQUEST['name']))
? { $name = stripslashes($_REQUEST['name']);
? } else {$name = NULL; echo ? <p><font color ="red">You forgot to enter your
name!</font></p>';
? }
? //Validate the email address.
? if (!empty($_REQUEST['email'])) {
? $email = $_REQUEST['email'];
? } else {
? $email = NULL;
? echo '<p><font color="red"> You forgot to enter your email address! </font></p>';
? }

? //*Validate the comments and combat Magic Quotes, if necessary*//
? if ( !empty($_REQUEST['comments?])) {
? $comments = stripslashes($_REQUEST['comments?]);
? } else { $comments = NULL;
? echo '<p><font color ="red">What? Nothing to say! You forgot to leave some comments! <font/>
? </p> ';
? }
? if (isset($_REQUEST['gender?])) {
? $gender = $_REQUEST['gender'];
? if ($gender == 'M') {
? $message = '<p><b>Good day, Sir!</b></p>';
? } elseif ($'gender == 'F?) {
? $message = '<p><b>Good day, Madam!</b> </p>';
? } else { // Unacceptable value.
? $message=NULL;
? echo '<p><font color="red">Gender should be either male or female!</font></p>';
? }
? } else { //gender is not set.
? $gender = NULL;
? echo ? <p><font color="red">You forgot to select your gender! </font></p>';
? }

? // If everything is okay, print the message.
? if ($name && $email && $gender && $comments) {
? echo '<p>Thank you <b>$name</b> for the following comments: <br/>
? <tt>$comments</tt> </p>';
? echo '<p> We will reply to you at <i>$email</i></p> n';
? echo $message; // From the $gender conditional.
? } else { // One form element was not filled out properly.
? echo ?<p><font color="red">Please go back and fill out the form again.</font></p>';
? }
? ?>
? </body>
? </html>

�ݺ�ߣ

Shangz R Brown Presentation

More Related Content

What's hot (20)

Viewers also liked (7)

Similar to Shangz R Brown Presentation (20)

More from shangbaby (6)

Shangz R Brown Presentation