Trigger, Stored Proc and some ASP.Net

--trigger

Create trigger Employee.tr_FiveTimeDiscount
on Employee.VehicleService
For Insert
As
Declare @VehicleID int
Declare @PersonID int
Declare @Count int

Select @VehicleID =VehicleID
From Inserted

Select @PersonID=PersonKey
From Customer.vehicle
Where VehicleId=@VehicleID

if exists
(Select RegisteredCustomerID
From Customer.RegisteredCustomer
Where PersonKey=@PersonID)
Begin
Select @Count=COUNT(VehicleServiceID)
From Employee.VehicleService vs
Inner Join Customer.Vehicle v
on v.VehicleId=vs.VehicleID
Where PersonKey=@PersonID

if @Count >= 5
Begin
print 'Congratulations, you qualify for an extra 10% discount'
End
End

Select v.Personkey, COUNT(VehicleServiceID)
From Employee.VehicleService vs
Inner Join Customer.vehicle v
on v.VehicleId=vs.VehicleID
Group by v.PersonKey

Select * from Customer.vehicle
Where PersonKey=24

Insert into Employee.VehicleService
(VehicleID, LocationID, ServiceDate, ServiceTime)
Values(17,1,GETDATE(),'10:45:00')

---

--login procedure
Go
Create proc Customer.usp_CustomerLogin
@email nvarchar(255),
@password nvarchar(20)
As

If exists
(Select RegisteredCustomerID
From customer.RegisteredCustomer
Where Email=@email
And CustomerPassword=@password)
Begin
Select PersonKey from customer.RegisteredCustomer
Where Email=@email
And CustomerPassword=@password
End

---

Alter proc Customer.TestGetVehicle
@PersonID int
As
Select * from vehicle
Where PersonKey=@personID
GO

---

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!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">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<p>
<asp:Label ID="Label1" runat="server" Text="Enter Email"></asp:Label>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox> <br />
<asp:Label ID="Label2" runat="server" Text="Enter password"></asp:Label>
<asp:TextBox ID="txtPassword" runat="server" TextMode="Password"></asp:TextBox> <br />
<asp:Button ID="btnSubmit" runat="server" Text="Submit"
onclick="btnSubmit_Click" />

</p>

<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>

---

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void btnSubmit_Click(object sender, EventArgs e)
{
int personkey=0;
SqlConnection connect = new SqlConnection
("Data source=localhost;initial catalog=Automart;user=CustomerLogin;password=pass");
SqlCommand cmd = new SqlCommand();
cmd.Connection = connect;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Customer.usp_CustomerLogin";
cmd.Parameters.AddWithValue("@email", txtEmail.Text);
cmd.Parameters.AddWithValue("@password", txtPassword.Text);
DataTable ds = new DataTable();
SqlDataReader reader = null;

connect.Open();
reader = cmd.ExecuteReader();
ds.Load(reader);
reader.Close();
connect.Close();

foreach (DataRow row in ds.Rows)
{
personkey = int.Parse(row["PersonKey"].ToString());
}

SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = connect;
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = "Customer.TestGetVehicle";
cmd2.Parameters.AddWithValue("@PersonID", personkey);

SqlDataReader reader2 = null;
connect.Open();
reader2 = cmd2.ExecuteReader();
GridView1.DataSource = reader2;
GridView1.DataBind();

reader2.Dispose();
connect.Close();




}
}

Loops,Selection, Simple File IO

#include <iostream>
#include <cmath>
#include <ctime>
#include <string>
#include <Windows.h>
#include <fstream>

/*
these are examples of loops
and branches
*/
using namespace std;

void SimpleForLoop();
void WhileLoop();
void DoLoops ();
void IfExamples();
void Menu();
void Pause();
void WriteFile();
void ReadFile();



int main()
{
//SimpleForLoop();
//WhileLoop();
//DoLoops();
//IfExamples();
Menu();
char c;
cin >> c;

}

void SimpleForLoop()
{
int myArray[5];

int i;
int total=0;
srand(time(0));
for(i =0; i<5; i++)
{

int number=rand();
myArray[i]=number;
}

for(int x=0; x<5; x++)
{
cout << myArray[x] <<endl;
total += myArray[x];
}

cout << "the total is " << total << " the Average is " << double(total) / i << endl;



}

void WhileLoop()
{
int i=1;
int number;
int total=0;
while( i != 0)
{
cout << "Enter an integer, 0 to exit" << endl;
cin >> i;
total += i;

}

cout << "Your total is " << total;
}

void DoLoops ()
{
int i = 0;
int total=0;
do
{
cout << "Enter an integer, 0 to exit" << endl;
cin >> i;
total += i;

}while(i !=0);

cout << "the total is " << total << " the Average is " << double(total) / i << endl;
}

void TwoDArray()
{
string myTwoDArray[3] [2] =
{
{"Twilight", "Meyers"},
{"Ulysses", "Joyce"},
{ "King Lear", "Shakespear"}
};

/*myTwoDArray[1,0]="Ulysses";
myTwoDArray[1,1]="Joyce";
myTwoDArray[2,0]="King Lear";
myTwoDArray[2,1]="Shakespear";*/


}

void IfExamples()
{
string message;

cout << "Enter the temperature" << endl;
int temp;
cin >> temp;
//or is ||
if (temp > -30 && temp < 0)
{
message="Way too cold";
}
else
if (temp >1 && temp < 20)
{
message ="Still too Cold";
}
else
if(temp >21 && temp < 50)
{
message="Cool";
}
else
if(temp >51 && temp < 80)
{
message="comfortable";
}
else
{
message="hot";
}

cout << message << endl;
}

void Menu()
{
int choice=999;

/* SimpleForLoop();
void WhileLoop();
void DoLoops ();
void IfExamples();
void Menu();*/
while (choice != 0)
{

system("cls");
cout << "Simple For Loop: 1" <<endl;
cout << "While Loops: 2" <<endl;
cout << "Do Loops: 3" << endl;
cout << "If Examples: 4" <<endl;
cout << "Write Example 5"<<endl;
cout << "Read File 6"<<endl;
cout <<"Exit: 0" << endl;

cout << "Enter your menu choice" << endl;
cin >> choice;

switch (choice)
{
case 1:
SimpleForLoop();
Pause();
break;
case 2:
WhileLoop();
Pause();
break;
case 3:
DoLoops();
Pause();
break;
case 4:
IfExamples();
Pause();
break;
case 5:
WriteFile();
Pause();
break;
case 6:
ReadFile();
Pause();
break;
case 0:
return;
default:
cout << "Enter a menu choice";
Pause();



}
}
}

void Pause()
{
cout<<"\n\nPress any key to continue";
char c;
cin >> c;
}

void WriteFile()
{
cin.get();
string message;
fstream outfile("C:\\Users\\sconger\\Desktop\\Test.txt",ios::out);
cout << "Enter your message" <<endl;
getline(cin, message);

outfile<<message;
outfile.close();

cout << "The file has been written" << endl;

}

void ReadFile()
{
cin.get();
string message;
fstream infile("C:\\Users\\sconger\\Desktop\\Test.txt",ios::in );
getline(infile,message);
cout << message << endl;

}

Indexes

--Indexes
--Clustered -- physically orders the table--by default the
--primary key is a clustered index
--unclustered indexes which form a B-Tree
--unique indexes

Use CommunityAssist

--dropped the primary key and its clustered index
Alter table personAddress
Drop Constraint PK__PersonAd__7CE0EF7203317E3D

--Add a new unclusted primary key
Alter Table PersonAddress
Add Constraint PK_PersonAddress primary key nonclustered(PersonAddressKey)

--create a new unclustered index
Create clustered index Ix_ClusteredLastName on PersonAddress(PersonKey)

--not sure if any change can be seen
Select * From PersonAddress

--creating non clustered indexes
Create index ix_LastName on Person(lastName)
--non clustered index on multiple fields
Create index ix_cityState on PersonAddress(City, [State])

--creating a unique index--fails because non unique data
--in table
Create unique index ix_ContactInfo on PersonContact(ContactInfo)

--disable an index
Alter index ix_LastName on Person Disable

--re-enable and rebuild an index
Alter index ix_LastName on Person Rebuild

--select with forced use of indexes
--generally SQL's query optimizer will ignore
--indexes if the number of table rows is too small
Select Lastname, Firstname, street, city, [State], zip
From Person p with (index (ix_LastName))
inner join PersonAddress pa with (index (Ix_ClusteredLastName))
on p.PersonKey=pa.PersonKey
Where LastName='Smith'

Functions and Views

--serviceprice is in Customer.Autoservice
--Discount percent is in Employee.VehicleService detail
--tax percent is in Employee.VehicleServiceDetail

--maybe three functions
--one for price with discounts for each service,
--one for tax
--one for total
--used with sums

--then a view that shows these

Select * from Customer.AutoService
Select * From Employee.VehicleServiceDetail

Go
Create function Employee.func_PriceWithDiscount
--parameters the user provides
(@ServiceID int, @Discount decimal(3,2))
returns money --datatype returned
As
Begin --begin function
--declare variables
Declare @price money
Declare @ServiceCost money
--Getting the price from the autoservice table
Select @price = ServicePrice from Customer.AutoService
Where AutoServiceID=@ServiceID
--calculating discount

Set @ServiceCost=@price - (@price * @Discount)

Return @ServiceCost
End


--using the Function
Select
vd.VehicleServiceID,ServiceDate,
Sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent))
as [Before Tax]
From Employee.VehicleServiceDetail vd
Inner Join Employee.VehicleService vs
on vs.VehicleServiceID=vd.VehicleServiceID
Where vd.VehicleServiceID=3
Group By vd.VehicleServiceID, ServiceDate

--using the function again
Select *, Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent)
as [Before Tax]
From Employee.VehicleServiceDetail
where VehicleServiceID=3

Select * from Customer.AutoService where AutoServiceID=7

Go
--create the tax function
Create Function Employee.GetTax
(@subtotal money, @TaxPercent decimal(3,2))
Returns money
As
Begin
return @subtotal * @TaxPercent
End

Go
--create a view that uses the two functions to return the details
--of a transaction
Create View Employee.PriceDetail
As
Select VehicleServiceID,
Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent) as Subtotal,
Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent)
as Tax,
Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent) +
Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent)
as Total
From Employee.VehicleServiceDetail
Group by VehicleServiceID

Go
--create a view that shows the summary of a transaction
Create View Employee.PriceTotal
As
Select VehicleServiceID,
Sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent)) as Subtotal,
Sum(Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent))
as Tax,
sum(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent) +
Employee.GetTax(Employee.func_PriceWithDiscount(AutoServiceID,DiscountPercent),TaxPercent))
as Total
From Employee.VehicleServiceDetail
Group by VehicleServiceID


--select from the two views
Select * from Employee.PriceDetail where VehicleServiceID=24
Select * From Employee.PriceTotal Where VehicleServiceID=24

Views, Procedures

Here is the code, such as it is from the morning class


Select * from Customer.RegisteredCustomer
Select * from Person


--views, parameterized views
--Service history for a particular vehicle by license plate
--Customer name and vehicles by license plate
--service costs and descriptions

--things that need to be done stored procedures
--record the services performed --stored procedure
--get the amount due--function
--maintenance recommendations--stored procedure
--Edit to correct mis-entry or change customer information--stored procedure
--
--
--we did this both as a view and as a stored procedure. The difference is the view
--does not have the parameter @license nor the where clause at the end
Go
Create Proc Employee.usp_VehicleHistory
@License nvarchar(10)
As
Select LastName as [Last Name],
LicenseNumber as [License],
VehicleMake as [Make],
VehicleYear as [Year],
ServiceDate as [Service Date],
ServiceName as [Service],
serviceNotes as [Notes]
From Person p
Inner Join Customer.Vehicle v
on p.PersonKey = v.PersonKey
Inner Join Employee.VehicleService vs
on v.VehicleID=vs.VehicleID
Inner Join Employee.VehicleServiceDetail vd
on vs.VehicleServiceId=vd.VehicleServiceID
Inner Join Customer.AutoService a
on a.AutoServiceID=vd.AutoServiceID
Where LicenseNumber=@License


Select * from Customer.AutoService

Select * from Employee.VehicleHistory
Where License='NET200'

--this view we made with the graphical designer
Select * from dbo.vw_HumanResources
Where LocationName='Federal Way'
Order by LastName


Execute Employee.usp_VehicleHistory 'ITC226'

--vehicleID get by license plate
--LocationID
--ServiceDate Service time

Alter procedure Employee.usp_NewService
@License nvarchar(10),
@LocationID int
As
Begin try
Declare @serviceDate Date
Declare @ServiceTime Time
Set @serviceDate=GETDATE()
Set @ServiceTime=GETDATE()

Declare @VehicleID int
Select @VehicleID=VehicleID From Customer.vehicle
Where LicenseNumber=@License


Insert into Employee.VehicleService
(VehicleID,LocationID,ServiceDate,ServiceTime)
Values (@VehicleID, @LocationID, @serviceDate, @ServiceTime)
End Try
Begin Catch
Print error_message()
End Catch

Exec Employee.usp_NewService
@License= 'ITC226',
@LocationID=2

Arrays, Enums and Structures

Here is the review of arrays and the examples for structures and enums

#include <iostream>
#include <string>
using namespace std;


void ArrayReview();
void PrintArray(int myArray3[], int size);
void CharacterArrays();
void StructureExample();
void ArrayOfStructures();
void EnumerationExamples();

struct product
{
string productName;
double productWeight;
double Price;
};

enum colors
{
red, green, blue, purple, orange, yellow
};


int main()
{
//ArrayReview();
//CharacterArrays();
//StructureExample();
//ArrayOfStructures();

EnumerationExamples();
char c;
cin >> c;
}

void ArrayReview()
{
int myArray[4];
myArray[0]=2;
myArray[1]=14;
myArray[2]=5;
myArray[3]=9;

int myArray2[]={2, 4, 5, 6, 9};

PrintArray(myArray,4);


}

void PrintArray(int myArray3[], int size)
{
for (int i =0;i<size;i++)
{
cout << myArray3[i] << endl;
}
}

void CharacterArrays()
{
char city[50];

cout << "Enter a City Name" << endl;
cin.getline(city,50);

cout << "\n\nYour city is : " << city;

}

void StructureExample()
{
product poprocks;
poprocks.Price=1.25;
poprocks.productName="pop rocks";
poprocks.productWeight=3;

cout << "You bought 3 packages of " << poprocks.productName << endl;
cout << "that will be $"<<poprocks.Price * 3 << endl;
cout << "thank you " << endl;

}

void ArrayOfStructures()
{
product grocery[3];

product bread;
bread.Price=3.25;
bread.productName="Daves Killer bread";
bread.productWeight=12;

product chocolate;
chocolate.Price=3.79;
chocolate.productName="Theo Dark Chocolate";
chocolate.productWeight=2.75;

product steaksauce;
steaksauce.Price=4.10;
steaksauce.productName="A1";
steaksauce.productWeight=8;


grocery[0]=bread;
grocery[1]=chocolate;
grocery[2]=steaksauce;

double total=0;

for(int i=0;i<3;i++)
{
cout << "you bought " << grocery[i].productName << " at a price of "
<< grocery[i].Price << endl;
total +=grocery[i].Price;
}

cout << "Your total is $" << total << endl;

}

void UnionExample()
{
union test
{
double double_val;
int int_val;
};

cout << sizeof(long) << " bytes";

test test1;

test1.int_val=45;

test test2;

test2.double_val=3.45;

cout << test1.int_val / test2.double_val << endl;

}

void EnumerationExamples()
{
colors spectrum;
spectrum= orange;
cout << "Our color is " << spectrum << " the next color is " << spectrum + 1
<< "the previous color was " << spectrum-1 << endl;
}

First Pointers

Below is the code we did in class on pointers. It includes basic pointers, A dynamic array using a pointer and the keyword "new" and a dynamic structure.


#include <iostream>
#include <string>

using namespace std;

//function proto types
void SimplePointer();
void DynamicArrays();
void DynamicStructure();

struct menu
{
string item;
double price;
};

int main()
{
//SimplePointer();
//DynamicArrays();
DynamicStructure();
char c;
cin >> c;
}

void SimplePointer()
{
int number=456;
int * p_number; // pointer
p_number= &number; //assigning the address of a variable to the pointer
int *p_number2; //a second pointer
p_number2=p_number; //assigning the address of one pointer to another


cout << " the number is " << number << " the address of the number is " << p_number
<< " the value stored at that address is " << *p_number
<< "the second pointer has an address of " << p_number2 << "and has an value of "
<< *p_number2 << endl;
}

void DynamicArrays()
{
cout << "Enter the size of the array you want " <<endl;
int size;
cin >> size;

//if you use a pointer you can assign the size
//of an array at run time
//otherwise it requires a constant
int *p_array=new int[size];

for (int i =0;i<size;i++)
{
cout << "Enter your value : ";
cin >> p_array[i];
}

cout << "the second element of the array is " << p_array[1] << "| " << p_array <<"| "
<< p_array + 2<< " |"<< *p_array + 2 << endl;

//you should always delete anything
//you allocate with "new"
delete p_array;

}

void DynamicStructure()
{
//a dynamic structure
//this allows you to assign values to the structure
//during run time
menu * p_menu = new menu;
cout << "Enter the menu item name " << endl;
getline(cin, p_menu->item);
cout << "Enter the Price "<< endl;
cin >> p_menu->price;

cout << "You ordered " << p_menu->item << " at " << p_menu ->price << endl;

delete p_menu;
}