text: /*
  Author:			This code was generated by DALGen version 1.1.0.0 available at https://github.com/H0r53/DALGen 
  Date:			12/9/2017
  Description:	Creates the securityuser table and respective stored procedures
  
  */
  
  
  USE applicationtemplate;
  
  
  
  -- ------------------------------------------------------------
  -- Drop existing objects
  -- ------------------------------------------------------------
  
  DROP TABLE IF EXISTS `applicationtemplate`.`securityuser`;
  DROP PROCEDURE IF EXISTS `applicationtemplate`.`usp_securityuser_Load`;
  DROP PROCEDURE IF EXISTS `applicationtemplate`.`usp_securityuser_LoadAll`;
  DROP PROCEDURE IF EXISTS `applicationtemplate`.`usp_securityuser_Add`;
  DROP PROCEDURE IF EXISTS `applicationtemplate`.`usp_securityuser_Update`;
  DROP PROCEDURE IF EXISTS `applicationtemplate`.`usp_securityuser_Delete`;
  DROP PROCEDURE IF EXISTS `applicationtemplate`.`usp_securityuser_Search`;
  
  
  -- ------------------------------------------------------------
  -- Create table
  -- ------------------------------------------------------------
  
  
  
  CREATE TABLE `applicationtemplate`.`securityuser` (
  Id INT AUTO_INCREMENT,
  Username VARCHAR(255),
  Password VARCHAR(255),
  Email VARCHAR(255),
  RoleId INT,
  CreateDate DATETIME,
  CONSTRAINT pk_securityuser_Id PRIMARY KEY (Id),
  CONSTRAINT fk_securityuser_RoleId_Role_Id FOREIGN KEY (RoleId) REFERENCES role (Id)
  );
  
  
  -- ------------------------------------------------------------
  -- Create default SCRUD sprocs for this table
  -- ------------------------------------------------------------
  
  
  DELIMITER //
  CREATE PROCEDURE `applicationtemplate`.`usp_securityuser_Load`
  (
  	 IN paramId INT
  )
  BEGIN
  	SELECT
  		`securityuser`.`Id` AS `Id`,
  		`securityuser`.`Username` AS `Username`,
  		`securityuser`.`Password` AS `Password`,
  		`securityuser`.`Email` AS `Email`,
  		`securityuser`.`RoleId` AS `RoleId`,
  		`securityuser`.`CreateDate` AS `CreateDate`
  	FROM `securityuser`
  	WHERE 		`securityuser`.`Id` = paramId;
  END //
  DELIMITER ;
  
  DELIMITER //
  CREATE PROCEDURE `applicationtemplate`.`usp_securityuser_LoadAll`
  ()
  BEGIN
  	SELECT
  		`securityuser`.`Id` AS `Id`,
  		`securityuser`.`Username` AS `Username`,
  		`securityuser`.`Password` AS `Password`,
  		`securityuser`.`Email` AS `Email`,
  		`securityuser`.`RoleId` AS `RoleId`,
  		`securityuser`.`CreateDate` AS `CreateDate`
  	FROM `securityuser`;
  END //
  DELIMITER ;
  
  DELIMITER //
  CREATE PROCEDURE `applicationtemplate`.`usp_securityuser_Add`
  (
  	 IN paramUsername VARCHAR(255),
  	 IN paramPassword VARCHAR(255),
  	 IN paramEmail VARCHAR(255),
  	 IN paramRoleId INT,
  	 IN paramCreateDate DATETIME
  )
  BEGIN
  	INSERT INTO `securityuser` (Username,Password,Email,RoleId,CreateDate)
  	VALUES (paramUsername, paramPassword, paramEmail, paramRoleId, paramCreateDate);
  	-- Return last inserted ID as result
  	SELECT LAST_INSERT_ID() as id;
  END //
  DELIMITER ;
  
  
  DELIMITER //
  CREATE PROCEDURE `applicationtemplate`.`usp_securityuser_Update`
  (
  	IN paramId INT,
  	IN paramUsername VARCHAR(255),
  	IN paramPassword VARCHAR(255),
  	IN paramEmail VARCHAR(255),
  	IN paramRoleId INT,
  	IN paramCreateDate DATETIME
  )
  BEGIN
  	UPDATE `securityuser`
  	SET Username = paramUsername
  		,Password = paramPassword
  		,Email = paramEmail
  		,RoleId = paramRoleId
  		,CreateDate = paramCreateDate
  	WHERE		`securityuser`.`Id` = paramId;
  END //
  DELIMITER ;
  
  
  DELIMITER //
  CREATE PROCEDURE `applicationtemplate`.`usp_securityuser_Delete`
  (
  	IN paramId INT
  )
  BEGIN
  	DELETE FROM `securityuser`
  	WHERE		`securityuser`.`Id` = paramId;
  END //
  DELIMITER ;
  
  
  DELIMITER //
  CREATE PROCEDURE `applicationtemplate`.`usp_securityuser_Search`
  (
  	IN paramId INT,
  	IN paramUsername VARCHAR(255),
  	IN paramPassword VARCHAR(255),
  	IN paramEmail VARCHAR(255),
  	IN paramRoleId INT,
  	IN paramCreateDate DATETIME
  )
  BEGIN
  	SELECT
  		`securityuser`.`Id` AS `Id`,
  		`securityuser`.`Username` AS `Username`,
  		`securityuser`.`Password` AS `Password`,
  		`securityuser`.`Email` AS `Email`,
  		`securityuser`.`RoleId` AS `RoleId`,
  		`securityuser`.`CreateDate` AS `CreateDate`
  	FROM `securityuser`
  	WHERE
  		COALESCE(securityuser.`Id`,0) = COALESCE(paramId,securityuser.`Id`,0)
  		AND COALESCE(securityuser.`Username`,'') = COALESCE(paramUsername,securityuser.`Username`,'')
  		AND COALESCE(securityuser.`Password`,'') = COALESCE(paramPassword,securityuser.`Password`,'')
  		AND COALESCE(securityuser.`Email`,'') = COALESCE(paramEmail,securityuser.`Email`,'')
  		AND COALESCE(securityuser.`RoleId`,0) = COALESCE(paramRoleId,securityuser.`RoleId`,0)
  		AND COALESCE(CAST(securityuser.`CreateDate` AS DATE), CAST(NOW() AS DATE)) = COALESCE(CAST(paramCreateDate AS DATE),CAST(securityuser.`CreateDate` AS DATE), CAST(NOW() AS DATE));
  END //
  DELIMITER ;
  
  
  