Database

SELECT cleaner_id, cleaner_name FROM cleaners WHERE cleaner_id NOT IN (SELECT cleaner_id FROM job_orders WHERE job_order_date = param_job_order_date AND start_time BETWEEN param_start_time AND param_end_time) AND is_active = TRUE ORDER BY cleaner_name;

CREATE TABLE [dbo].[aspnet_Genealogy] (
    [GenealogyId]       INT              IDENTITY (1, 1) NOT NULL,
    [UserId]            UNIQUEIDENTIFIER NULL,
    [FamilyName]        CHAR (200)       NOT NULL,
    [FirstName]         CHAR (200)       NULL,
    [MiddleName1]       CHAR (200)       NULL,
    [MiddleName2]       CHAR (200)       NULL,
    [MiddleName3]       CHAR (200)       NULL,
    [Gender]            CHAR (10)        NULL,
    [DOB]               VARCHAR (20)     NOT NULL,
    [COB]               CHAR (200)       NULL,
    [SOB]               CHAR (200)       NULL,
    [COOB]              CHAR (200)       NULL,
    [Newsletter]        CHAR (10)        NULL,
    [DateTimeGenealogy] DATETIME         NOT NULL,
    PRIMARY KEY CLUSTERED ([GenealogyId] ASC),
    CONSTRAINT [FK_aspnet_Genealogy_aspnet_Membership] FOREIGN KEY ([UserId]) REFERENCES [dbo].[aspnet_Membership] ([UserId])
);

CREATE TABLE [dbo].[aspnet_Membership] (
    [ApplicationId]                          UNIQUEIDENTIFIER NOT NULL,
    [UserId]                                 UNIQUEIDENTIFIER NOT NULL,
    [Password]                               NVARCHAR (128)   NOT NULL,
    [PasswordFormat]                         INT              DEFAULT ((0)) NOT NULL,
    [PasswordSalt]                           NVARCHAR (128)   NOT NULL,
    [MobilePIN]                              NVARCHAR (16)    NULL,
    [Email]                                  NVARCHAR (256)   NULL,
    [LoweredEmail]                           NVARCHAR (256)   NULL,
    [PasswordQuestion]                       NVARCHAR (256)   NULL,
    [PasswordAnswer]                         NVARCHAR (128)   NULL,
    [IsApproved]                             BIT              NOT NULL,
    [IsLockedOut]                            BIT              NOT NULL,
    [CreateDate]                             DATETIME         NOT NULL,
    [LastLoginDate]                          DATETIME         NOT NULL,
    [LastPasswordChangedDate]                DATETIME         NOT NULL,
    [LastLockoutDate]                        DATETIME         NOT NULL,
    [FailedPasswordAttemptCount]             INT              NOT NULL,
    [FailedPasswordAttemptWindowStart]       DATETIME         NOT NULL,
    [FailedPasswordAnswerAttemptCount]       INT              NOT NULL,
    [FailedPasswordAnswerAttemptWindowStart] DATETIME         NOT NULL,
    [Comment]                                NTEXT            NULL,
    PRIMARY KEY NONCLUSTERED ([UserId] ASC),
    FOREIGN KEY ([ApplicationId]) REFERENCES [dbo].[aspnet_Applications] ([ApplicationId]),
    FOREIGN KEY ([UserId]) REFERENCES [dbo].[aspnet_Users] ([UserId])
);


GO
EXECUTE sp_tableoption @TableNamePattern = N'[dbo].[aspnet_Membership]', @OptionName = N'text in row', @OptionValue = N'3000';


GO
CREATE CLUSTERED INDEX [aspnet_Membership_index]
    ON [dbo].[aspnet_Membership]([ApplicationId] ASC, [LoweredEmail] ASC);

CREATE PROCEDURE [dbo].[aspnet_AddGenealogy]
(
	@FamilyName char(200),
	@FirstName char(200),
	@MiddleName1 char(200),
	@MiddleName2 char(200),
	@MiddleName3 char(200),
	@Gender char(10),
	@DOB varchar(20),
	@COB char(200),
	@SOB char(200),
	@COOB char(200),
	@Newsletter char(200),
	@DateTimeGenealogy DateTime
)
	
AS
BEGIN
 
	INSERT INTO aspnet_Genealogy (FamilyName, FirstName, MiddleName1, MiddleName2, MiddleName3, Gender, DOB, COB, SOB, COOB, Newsletter, DateTimeGenealogy)
	VALUES (@FamilyName, @FirstName, @MiddleName1, @MiddleName2, @MiddleName3, @Gender, @DOB, @COB, @SOB, @COOB, @Newsletter, @DateTimeGenealogy)

	DECLARE @UserId uniqueidentifier
	DECLARE @GenealogyId int

IF ( @GenealogyId = +1 )

        UPDATE   dbo.aspnet_Genealogy
        SET      UserId = @UserId
        FROM     dbo.aspnet_Membership u, aspnet_Genealogy m
        WHERE    @UserId = u.UserId AND @UserId = m.UserId

        IF ( @@ROWCOUNT = 0 ) -- User ID not found
            RETURN -1
    END

    SELECT  m.UserId, m.FamilyName, m.FirstName, m.MiddleName1, m.MiddleName2, m.MiddleName3, m.Gender, m.DOB, m.COB, m.SOB, m.COOB, m.Newsletter, m.DateTimeGenealogy, u.UserId
    FROM    dbo.aspnet_Membership u, dbo.aspnet_Genealogy m
    WHERE   @UserId = u.UserId AND u.UserId = m.UserId

    IF ( @@ROWCOUNT = 0 ) -- User ID not found
       RETURN -1

    RETURN 0

CREATE VIEW [dbo].[vw_aspnet_Genealogy]
	AS SELECT [dbo].[aspnet_Genealogy].[UserId], [FamilyName], [dbo].[aspnet_Genealogy].[FirstName], [dbo].[aspnet_Genealogy].[MiddleName1], [dbo].[aspnet_Genealogy].[MiddleName2], [dbo].[aspnet_Genealogy].[MiddleName3], [dbo].[aspnet_Genealogy].[Gender], [dbo].[aspnet_Genealogy].[DOB], [dbo].[aspnet_Genealogy].[COB], [dbo].[aspnet_Genealogy].[SOB], [dbo].[aspnet_Genealogy].[COOB], [dbo].[aspnet_Genealogy].[Newsletter], [dbo].[aspnet_Genealogy].[DateTimeGenealogy] FROM [dbo].[aspnet_Membership] INNER JOIN [dbo].[aspnet_Genealogy]
      ON [dbo].[aspnet_Membership].[UserId] = [dbo].[aspnet_Genealogy].[UserId]

CREATE TABLE IF NOT EXISTS FARM
(
   FARM_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
   FARM_NAME VARCHAR(45) NOT NULL,
   ADDRESS VARCHAR(45) NOT NULL,
   OWNER VARCHAR(45) NOT NULL
)ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS PADDOCK
(
   PADDOCK_NUM INT NOT NULL,
   FARM_FARM_ID INT NOT NULL,
   PRIMARY KEY(FARM_FARM_ID, PADDOCK_NUM),   
   AREA VARCHAR(45) NOT NULL,
   CONSTRAINT FARM_FARM_ID_CON FOREIGN KEY(FARM_FARM_ID)
   REFERENCES FARM(FARM_ID)
)ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS VISIT
(
  VISIT_ID INT NOT NULL PRIMARY KEY, 
  GATHERING DATE,
  PADDOCK_PADDOCK_NUM INT NOT NULL, 
  PADDOCK_FARM_ID INT NOT NULL, 
  CONSTRAINT PADDOCK_PADDOCK_NUM_CON FOREIGN KEY(PADDOCK_PADDOCK_NUM)
  REFERENCES PADDOCK(PADDOCK_NUM),
  CONSTRAINT PADDOCK_PADDOCK_FARM_ID_CON FOREIGN KEY(PADDOCK_FARM_ID)
  REFERENCES PADDOCK(FARM_FARM_ID)
)ENGINE = INNODB;  

CREATE TABLE IF NOT EXISTS VISIT
(
  VISIT_ID INT NOT NULL PRIMARY KEY, 
  GATHERING DATE,
  PADDOCK_PADDOCK_NUM INT NOT NULL, 
  PADDOCK_FARM_ID INT NOT NULL, 
  CONSTRAINT PADDOCK_PADDOCK_NUM_CON FOREIGN KEY(PADDOCK_PADDOCK_NUM)
  REFERENCES PADDOCK(PADDOCK_NUM),
  CONSTRAINT PADDOCK_PADDOCK_FARM_ID_CON FOREIGN KEY(PADDOCK_FARM_ID)
  REFERENCES PADDOCK(FARM_FARM_ID),
  UNIQUE KEY(PADDOCK_PADDOCK_NUM, PADDOCK_FARM_ID, GATHERING)
)ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS SAMPLE
(
   SAMPLE_NUM INT UNSIGNED NOT NULL AUTO_INCREMENT,
   VISIT_VISIT_ID INT NOT NULL,
   PRIMARY KEY(SAMPLE_NUM, VISIT_VISIT_ID),
   COUNT INT,
   CONSTRAINT VISIT_VISIT_ID_CON FOREIGN KEY(VISIT_VISIT_ID)
   REFERENCES VISIT(VISIT_ID)
)ENGINE = INNODB;