ITC Resources

Tuesday, January 19, 2016

Joins

Select * From sys.Databases
Use CommunityAssist
Select name from sys.procedures

--joins
-- cross join
Select PersonLastName, Street, City from Person, PersonAddress

Select PersonLastName, Street, City from Person
Cross Join PersonAddress

--inner joins
Select PersonLastName, ContactInfo, ContactType.ContactTypeKey,
[ContactTypename]
From Person, PersonContact, ContactType
Where Person.PersonKey=PersonContact.Personkey
And ContactType.ContactTypeKey=PersonContact.ContactTypeKey

Select PersonLastName, ct.ContactTypeKey, ContactInfo, ContactTypeName
From Person as p
inner join PersonContact as pc
On p.PersonKey=pc.PersonKey
Inner join ContactType ct
on pc.ContactTypeKey=ct.ContactTypeKey

Select Distinct PersonLastname,  donationDate, sum(DonationAmount)
from Person p
join PersonContact pc
on p. PersonKey = pc.PersonKey
join donation d
on d.PersonKey=p.PersonKey
Group by PersonLastname,  donationDate


--outer Joins
Select Distinct ServiceName, sg.ServiceKey from CommunityService cs
left outer join ServiceGrant sg
on cs.serviceKey=sg.ServiceKey
Where sg.ServiceKey is null

Select Distinct ServiceName, sg.ServiceKey from CommunityService cs
full outer join ServiceGrant sg
on cs.serviceKey=sg.ServiceKey
Where sg.ServiceKey is null

Select Distinct PersonLastName, ContactInfo, ContactTypeName
From Person p
Inner Join PersonContact pc
on p.PersonKey=pc.PersonKey
right join ContactType ct
On Ct.ContactTypeKey=pc.ContactTypeKey
Where ContactInfo is null

Thursday, January 14, 2016

Selects 2

Use CommunityAssist;

Select EmployeeKey, EmployeeMonthlySalary, 
   EmployeeMonthlySalary * 12 as [Annual Salary]
   From Employee;

Select Avg(EmployeeMonthlySalary) from Employee
Where Not EmployeeMonthlySalary =0;

Select Sum(EmployeeMonthlySalary) from Employee;
Select Min(EmployeeMonthlySalary) from Employee;
Select Max(EmployeeMonthlySalary) from Employee;
Select Count(EmployeeMonthlySalary) from Employee;

Select * From ServiceGrant;

Select ServiceKey, sum(GrantAllocation) as Total from ServiceGrant
Where ServiceKey > 2
Group by ServiceKey
having sum(GrantAllocation) > 2500;

Select ServiceKey, sum(GrantAllocation) as Total from ServiceGrant
Group by ServiceKey
having sum(GrantAllocation) > 2000
Order by total desc;

Select Distinct Year(GrantDate) from ServiceGrant;
Select Distinct Month(GrantDate) from ServiceGrant;

Select Year(GrantDate) as Year, Month(GrantDate) as [Month], 
case Month(GrantDate)
when 8 then 'August'
when 9 then 'September'
end as [word Month],
Sum(GrantAllocation) as Total from ServiceGrant
Group by Year(GrantDate), Month(GrantDate);

Select Distinct DatePart(mm,GrantDate), Datepart(dd,GrantDate) from serviceGrant
Select Distinct DatePart(hour, GrantDate) From ServiceGrant
Where grantKey=2;

Select GrantDate, GrantReviewDate, DateDiff(dd,GrantDate,GrantReviewDate) 
as [Processing Time]
From ServiceGrant;

Select DateDiff(minute,'1/14/2016','1/14/2020');

Select GetDate() as Today;

Select DateAdd(dd, 365, GetDate());

Select * from Employee;

Select EmployeeKey, EmployeeHireDate
From Employee
Where Year(EmployeeHireDate) between 2003 and 2008;

--###-##-####
Select substring(EmployeeSSNumber,1,3) + '-' 
+ substring(EmployeeSSNumber,4,2) + '-' 
+ substring(EmployeeSSNumber,6,4)  From Employee;

Select format(cast(EmployeeSSNumber as int),'000-00-0000') from Employee;

Tuesday, January 12, 2016

Selects 1 Examples

use CommunityAssist

Select * from Person;

Select PersonFirstName as [First Name], PersonLastName [Last Name], PersonUserName as [User Name]
From Person
Order by PersonLastName Desc, PersonFirstName Desc;

Select * From PersonAddress
Where City='Kent';

Select * From PersonAddress
Where Not City='Seattle';

Select * From PersonAddress
Where City !='Seattle';

Select * From PersonAddress
Where City <>'Seattle';

Select * From PersonAddress 
Where Apartment is not null
And (City ='Seattle' Or City ='Bellevue');

Select GrantKey, GrantAmount, GrantAllocation from ServiceGrant
Where GrantAmount>GrantAllocation;


Select * From ServiceGrant
Where GrantDate Between '2013-08-09' and '2013-08-09';

Select PersonLastName 
from Person 
where PersonLastName like '_anner_';

Select Distinct Top(5) GrantAmount from ServiceGrant
Order by GrantAmount desc;


Select Distinct  GrantAmount from ServiceGrant
Order by GrantAmount desc
Offset 5 rows Fetch next 5 rows only;

Thursday, January 7, 2016

Creating and altering tables

Create database Assignment1Example;

use Assignment1Example;
/*
This is a multi line
comment
*/

Create Table Person
(
  -- identity autoincrements the integer
      PersonKey int identity(1,1) primary key,
   PersonLastName nvarchar(255) not null,
   PersonFirstname nvarchar(255) null,
   PersonAddress nvarchar(255) not null,
   PersonCity nvarchar(255) default 'Seattle',
   PersonState nchar(2),
   PersonZip nchar(10) not null

);

Create table Donation
(
 DonationKey int identity(1,1),
 PersonKey int not null,
 DonationDonationDate date default GetDate(),
 DonationAmount decimal(10,2),
 constraint PK_Donation  Primary key (DonationKey),
 constraint Fk_Person Foreign Key (PersonKey)
             references Person(PersonKey)

);

Create table Volunteer
(
 VolunteerKey int identity(1,1),
 PersonKey int not null,
 VolunteerStartDate Date not null,
 VolunteerEndDate Date not null
)

Alter table Volunteer 
Add Constraint PK_Volunteer Primary Key (VolunteerKey)

Alter Table Volunteer
Add Constraint FK_VolunteerPerson Foreign Key (PersonKey)
   references Person(PersonKey)

   Create table LinkToSomething
   (
      PersonKey int,
   DonationKey int,
   Constraint pK_Link primary key(PersonKey, DonationKey),
   constraint FK_PersonLink foreign key(PersonKey)
       references Person(PersonKey),
   Constraint FK_Donor Foreign key (DonationKey)
        references Donation(donationKey)
   )


   Alter table Person
   Drop column PersonState

   Alter Table Volunteer
   add VolunteerLocation Nvarchar(255)

   Begin tran

   Rollback tran
   Commit tran

Wednesday, January 6, 2016

History

Client list for a lawyer

The practice was small but grew, and it became harder to track people and billable time. Originally on paper, but paper gets lost and paper is hard to search. A database would tracking and keeping track of client.

Scope

The database must track client information. It should track all the paperwork associated with each client. Track case deadlines. Add and archive clients. Track cases and case types. Track lawyers and which cases they work on. Track each lawyer’s billable hours by case. Track retainers. Generate Bills, Reports, deadline alert. Only firm and secretaries lawyers should be able to access the database.

Constraints.

Will not write front end. Won’t talk directly to the state databases.

Time line and deliverables

Gather information—
Requirements and Business Rules
Design
Build prototype and add sample data
Test sql

Tuesday, December 8, 2015

WPF Form (Assignment 12)

Here is XAML

<Window x:Class="Assignment12.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:Assignment12"
        mc:Ignorable="d"
        Title="MainWindow" Height="350" Width="525">
    <Grid>
        <Label x:Name="label" Content="Enter the base Amount" HorizontalAlignment="Left" Margin="49,0,0,0" VerticalAlignment="Top" Height="27" Width="194"/>
        <TextBox x:Name="txtAmount" HorizontalAlignment="Left" Height="23" Margin="210,0,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="134"/>
        <RadioButton x:Name="rdbTenPercent" Content="Ten Percent" HorizontalAlignment="Left" Margin="63,45,0,0" VerticalAlignment="Top"/>
        <RadioButton x:Name="rdbFifteenPercent" Content="fifteen Percent" HorizontalAlignment="Left" Margin="63,65,0,0" VerticalAlignment="Top"/>
        <RadioButton x:Name="rdbTwentyPercent" Content="Twenty Percent" HorizontalAlignment="Left" Margin="63,85,0,0" VerticalAlignment="Top"/>
        <RadioButton x:Name="rdbOther" Content="Other" HorizontalAlignment="Left" Margin="63,105,0,0" VerticalAlignment="Top"/>
        <TextBox x:Name="txtOther" HorizontalAlignment="Left" Height="23" Margin="210,103,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="120"/>
        <Button x:Name="btnCalculate" Content="Calculate" HorizontalAlignment="Left" Margin="63,141,0,0" VerticalAlignment="Top" Width="75" Click="btnCalculate_Click"/>
        <Label x:Name="lblResult" Content="Label" HorizontalAlignment="Left" Margin="210,126,0,0" VerticalAlignment="Top" Height="97" Width="154"/>
        <Button x:Name="btnClear" Content="Clear" HorizontalAlignment="Left" Margin="63,175,0,0" VerticalAlignment="Top" Width="75" Click="btnClear_Click"/>
        <Button x:Name="btnExit" Content="Exit" HorizontalAlignment="Left" Margin="63,208,0,0" VerticalAlignment="Top" Width="75" Click="btnExit_Click"/>

    </Grid>
</Window>

Here is the Tip Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Assignment12
{
    class Tip
    {
       public double Amount { get; set; }
        public double TipPercent { get; set; }

        private const double TAX = .092;

        public double CalculateTip()
        {
            return Amount * TipPercent;
        }

        public double CalculateTax()
        {
            return Amount * TAX;
        }

        public double CalculateTotal()
        {
            return Amount + (Amount * TipPercent)
                + (Amount * TAX);
        }

        public override string ToString()
        {

            StringBuilder sb = new StringBuilder();
            sb.AppendLine("Amount: " + Amount.ToString("$#,###.00"));
            sb.AppendLine("Tip: " + CalculateTip().ToString("$#,###.00"));
            sb.AppendLine("Tax: " + CalculateTax().ToString("$#,###.00"));
            sb.AppendLine("Total: " + CalculateTotal().ToString("$#,###.00"));
            return sb.ToString();
        }
    }
}

Here is the code behind the Form

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace Assignment12
{
    /// 
    /// Interaction logic for MainWindow.xaml
    /// 
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        private void btnCalculate_Click(object sender, RoutedEventArgs e)
        {
            Calculate();
        }

        private void Calculate()
        {
            Tip t = new Tip();
            double amount;
            bool goodAmount = double.TryParse(txtAmount.Text, out amount);
            if (goodAmount)
            {
                t.Amount = amount;
            }
            else
            {
                MessageBox.Show("Enter a valid Amount");
                txtAmount.Clear();
                txtAmount.Focus();
                return;
            }
            t.TipPercent = GetTipPercent();

            lblResult.Content = t.ToString();

        }

        private double GetTipPercent()
        {
            double tipPercent = 0;
            if (rdbTenPercent.IsChecked == true)
                tipPercent = .1;
            if (rdbFifteenPercent.IsChecked == true)
                tipPercent = .15;
            if (rdbTwentyPercent.IsChecked == true)
                tipPercent = .2;
            if (rdbOther.IsChecked == true)
            {
                bool goodPercent = double.TryParse(txtOther.Text, out tipPercent);
                if (tipPercent > 1)
                    tipPercent /= 100;
            }
            return tipPercent;

        }

        private void btnClear_Click(object sender, RoutedEventArgs e)
        {
            Clear();
        }

        private void Clear()
        {
            txtAmount.Clear();
            txtOther.Clear();
            rdbTenPercent.IsChecked = false;
            rdbFifteenPercent.IsChecked = false;
            rdbTwentyPercent.IsChecked = false;
            rdbOther.IsChecked = false;
            lblResult.Content = "";
        }

        private void btnExit_Click(object sender, RoutedEventArgs e)
        {
            //this.Close();
            Application.Current.Shutdown();
        }
    }
}

Monday, December 7, 2015

Web page for Donors

the SQL for the DonorLogin

use Master;
Create Login DonorLogin with password='pass';
Use communityAssist;
Create user DonorLogin for login DonorLogin;
Create Role DonorRole;
Grant select, insert on Person to DonorRole;
Grant select, insert on PersonAddress to DonorRole;
Grant select, insert on PersonContact to DonorRole;
Grant select, insert on Donation to DonorRole;

exec sp_AddRoleMember 'DonorRole', 'DonorLogin'

Here is the asp.net code for the web page

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Donor Registration</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h1>Donor Registration</h1>
        <table>
            <tr>
                <td>Enter First Name</td>
                <td>
                    <asp:TextBox ID="FirstNameTextBox" runat="server">
                    </asp:TextBox>

                </td>
                </tr>
            <tr>
                <td>Enter Last Name</td>
                <td>
                    <asp:TextBox ID="LastNameTextBox" runat="server">
                    </asp:TextBox>

                </td>
                </tr>
            <tr>
                <td>Enter Street Address</td>
                <td>
                    <asp:TextBox ID="StreetTextBox" runat="server">
                    </asp:TextBox>

                </td>
                </tr>
            <tr>
                <td>Enter Email</td>
                <td>
                    <asp:TextBox ID="EmailTextBox" runat="server">
                    </asp:TextBox>

                </td>
                </tr>
            <tr>
                <td>Enter password</td>
                <td>
                    <asp:TextBox ID="PasswordTextBox" runat="server" TextMode="Password">
                    </asp:TextBox>

                </td>
                </tr>
            <tr>
                <td>
                    <asp:Button ID="SaveDonor" runat="server" Text="Button" OnClick="SaveDonor_Click" /></td>
                <td>
                    <asp:Label ID="ErrorLabel" runat="server" Text="Label"></asp:Label>

                </td>
            </tr>

        </table>
    </div>
    </form>
</body>
</html>

Here is the C# code with the caveat that we never got to run it, and you would need to add all the parameters to make it work.

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;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if(Session["loggedInUser"] == null)
        {
            Response.Redirect("Login.aspx");
        }
    }

    protected void SaveDonor_Click(object sender, EventArgs e)
    {
        SqlConnection connect =
            new SqlConnection(ConfigurationManager.
            ConnectionStrings["CommunityAssistConnection"].ToString());
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = connect;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "usp_NewDonorLogin";
        cmd.Parameters.AddWithValue("@lastName", LastNameTextBox.Text);
        cmd.Parameters.AddWithValue("@FirstName", FirstNameTextBox.Text);

        connect.Open();
        cmd.ExecuteNonQuery();
        connect.Close();

    }
}

And here is the web config file with the connections string

<?xml version="1.0"?>

<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->

<configuration>
<connectionStrings>
  <add connectionString="Data source=.\sqlexpress; initial catalog=communityAssist; user=DonorLogin; password=pass"
       name="communityAssistConnection"/>
</connectionStrings>
    <system.web>
      <compilation debug="true" targetFramework="4.5.2" />
      <httpRuntime targetFramework="4.5.2" />
    </system.web>

</configuration>