USE master
GO
CREATE DATABASE Sales
ON 
  (NAME = Sales_dat,
   --Make sure you check the path
   FILENAME = 'c:\mssql\data\salesdata.mdf',
   SIZE = 10,
   MAXSIZE = 50,
   FILEGROWTH = 5 )
LOG ON
  (NAME = 'Sales_log',
   --Make sure you check the path
   FILENAME = 'c:\mssql\data\saleslog.ldf',
   SIZE = 5MB,
   MAXSIZE = 25MB,
   FILEGROWTH = 5MB )
GO

/*create the tables*/
Use Sales
Go
CREATE TABLE [dbo].[activity] (
  [ActivityId] [numeric](18, 0) IDENTITY (1000, 1) NOT NULL ,
  [LocationId] [int] NOT NULL ,
  [DateEntered] [datetime] NOT NULL ,
  [Sales] [money] NOT NULL ,
  [Margin] [numeric](18, 0) NOT NULL ,
  [NewAccounts] [smallint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[location] (
  [LocationId] [int] NOT NULL ,
  [ManagerId] [int] NOT NULL ,
  [Number] [varchar] (10) NOT NULL ,
  [Name] [varchar] (30) NOT NULL ,
  [City] [varchar] (30) NOT NULL ,
  [State] [char] (2) NOT NULL ,
  [Zip] [varchar] (10) NOT NULL ,
  [Phone] [varchar] (12) NULL ,
  [Fax] [varchar] (12) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[manager] (
  [ManagerId] [int] NOT NULL ,
  [FirstName] [varchar] (30) NOT NULL ,
  [LastName] [varchar] (50) NOT NULL ,
  [Email] [varchar] (150) NOT NULL ,
  [Password] [varchar] (10) NOT NULL ,
  [Active] [char] (1) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[report] (
  [ReportId] [int] IDENTITY (1000, 1) NOT NULL ,
  [Name] [varchar] (100) NOT NULL ,
  [XmlFile] [varchar] (100) NOT NULL ,
  [XslFile] [varchar] (100) NOT NULL ,
  [Active] [char] (1) NOT NULL 
) ON [PRIMARY]
GO

/*create stored procedure*/
CREATE PROCEDURE spACTIVITY_INSERT(
  @LocationId INT, 
  @DateEntered DATETIME, 
  @Sales MONEY,
  @Margin NUMERIC(18,0), 
  @NewAccounts SMALLINT)
AS BEGIN
  INSERT INTO activity(
    LocationId, 
    DateEntered, 
    Sales, 
    Margin,
    NewAccounts)
  VALUES(
    @LocationId, 
    @DateEntered, 
    @Sales, 
    @Margin,
    @NewAccounts)
END
GO


