Shangz r brown
Download
1 / 13

SHANGZ R BROWN - PowerPoint PPT Presentation


  • 118 Views
  • Uploaded on

SHANGZ R BROWN. shangbaby@gmail.com 818-235-9779 http://www.shangbaby.net/joomlajoy. Contents. MSSQL SERVER user stored procedure SSRS – Customer Transaction Summary HTML Form – The HTML behind it! PHP Form Validation – The PHP behind it!. An Excerpt from my PiggyBank Project.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SHANGZ R BROWN' - ulmer


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Shangz r brown

SHANGZ R BROWN

shangbaby@gmail.com

818-235-9779

http://www.shangbaby.net/joomlajoy


Contents
Contents

MSSQL SERVER user stored procedure

SSRS – Customer Transaction Summary

HTML Form – The HTML behind it!

PHP Form Validation – The PHP behind it!


An excerpt from my piggybank project
An Excerpt from my PiggyBank Project

  • 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
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,

  • @TransactionAmountmoney = null,

  • @TransactionTypeIDint = null,

  • @NewBalance moneyOUTPUT

  • AS

  • SET XACT_ABORT ON

  • --Validate that @AccountID belongs to the same customer (linked in customer Accounts table)

  • IF(SELECTCustomerAccountID

  • FROMCustomerAccount

  • WHEREAccountID = @AccountID

    andCustomerID = @CustomerID)is null

  • BEGIN

  • raiserror('Account must be linked to the CustomerID entered', 11, 1)

  • RETURN

  • END


Shangz r brown
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;


Shangz r brown
This part of the stored proc records the entire transaction (overdraft and all) to the Transactions table.

  • -- 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)

  • ANDexists(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])


Shangz r brown

  • VALUES (overdraft and all) to the Transactions table.

  • (@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


Shangz r brown

SSRS – (overdraft and all) to the Transactions table.SQL Server Reporting Services – 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.


Html form
HTML FORM (overdraft and all) to the Transactions table.

  • <!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>


Php form validation
PHP FORM VALIDATION (overdraft and all) to the Transactions table.

  • <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>';

  • }


Shangz r brown

  • //*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>';

  • }


This part of the php form returns a thank you message if every field in the form is validated
This part of the PHP form returns a ‘Thank You’ necessary*//message if every field in the form is validated.

  • // If everything is okay, print the message.

  • if ($name && $email && $gender && $comments){

  • echo '<p>Thankyou<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>