-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.0.22-community-nt


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema commandcoveragedb
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ commandcoveragedb;
USE commandcoveragedb;

--
-- Table structure for table `commandcoveragedb`.`channel`
--

DROP TABLE IF EXISTS `channel`;
CREATE TABLE `channel` (
  `channelid` int(10) unsigned NOT NULL auto_increment,
  `product` varchar(45) NOT NULL default '',
  `platform` varchar(45) NOT NULL default '',
  `language` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`channelid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `commandcoveragedb`.`channel`
--

/*!40000 ALTER TABLE `channel` DISABLE KEYS */;
INSERT INTO `channel` (`channelid`,`product`,`platform`,`language`) VALUES 
 (1,'InDesign','Win','English'),
 (2,'InDesign','Win','Japanese'),
 (3,'InDesign','Mac','Japanese'),
 (4,'InDesign','Mac','English'),
 (5,'InDesignServer','Win','English'),
 (6,'InDesignServer','Mac','English'),
 (7,'InDesignServer','Mac','Japanese'),
 (8,'InDesignServer','Win','Japanese'),
 (9,'InDesignCopy','Mac','Japanese'),
 (10,'InDesignCopy','Win','Japanese'),
 (11,'InDesignCopy','Win','English'),
 (12,'InDesignCopy','Mac','English');
/*!40000 ALTER TABLE `channel` ENABLE KEYS */;


--
-- Table structure for table `commandcoveragedb`.`command`
--

DROP TABLE IF EXISTS `command`;
CREATE TABLE `command` (
  `commandid` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(200) NOT NULL default '',
  `description` varchar(250) NOT NULL default '',
  `channelid` int(10) unsigned NOT NULL default '0',
  `isActive` varchar(1) NOT NULL default '0',
  `cmdIntroducedBuild` varchar(45) NOT NULL default '',
  `cmdInActiveBuild` varchar(45) NOT NULL default '',
  `UpdatedBy` varchar(45) NOT NULL default '',
  `pluginid` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`commandid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `commandcoveragedb`.`command`
--

/*!40000 ALTER TABLE `command` DISABLE KEYS */;
/*!40000 ALTER TABLE `command` ENABLE KEYS */;


--
-- Table structure for table `commandcoveragedb`.`notes`
--

DROP TABLE IF EXISTS `notes`;
CREATE TABLE `notes` (
  `NoteID` int(10) unsigned NOT NULL auto_increment,
  `CommandID` int(10) unsigned NOT NULL default '0',
  `Note` varchar(50000) NOT NULL default '',
  `Date` datetime NOT NULL default '0000-00-00 00:00:00',
  `Author` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`NoteID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `commandcoveragedb`.`notes`
--

/*!40000 ALTER TABLE `notes` DISABLE KEYS */;
/*!40000 ALTER TABLE `notes` ENABLE KEYS */;


--
-- Table structure for table `commandcoveragedb`.`plugin`
--

DROP TABLE IF EXISTS `plugin`;
CREATE TABLE `plugin` (
  `pluginid` int(10) unsigned NOT NULL auto_increment,
  `channelid` int(10) unsigned NOT NULL default '0',
  `pluginname` varchar(45) NOT NULL default '',
  `prefix` varchar(45) NOT NULL default '',
  `owner` varchar(45) NOT NULL default '',
  `isactive` varchar(1) NOT NULL default '',
  `pluginIntroducedBuild` varchar(45) NOT NULL default '',
  `pluginInactiveBuild` varchar(45) NOT NULL default '',
  `upDatedBy` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`pluginid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `commandcoveragedb`.`plugin`
--

/*!40000 ALTER TABLE `plugin` DISABLE KEYS */;
/*!40000 ALTER TABLE `plugin` ENABLE KEYS */;


--
-- Table structure for table `commandcoveragedb`.`result_channelid1`
--

DROP TABLE IF EXISTS `result_channelid1`;
CREATE TABLE `result_channelid1` (
  `ResultID` int(10) unsigned NOT NULL auto_increment,
  `CommandID` int(10) unsigned NOT NULL default '0',
  `BuildNo` varchar(45) NOT NULL default '',
  `TCMTestCaseID` varchar(45) NOT NULL default '',
  `Author` varchar(45) NOT NULL default '',
  `cmdDone` varchar(1) NOT NULL default '',
  PRIMARY KEY  (`ResultID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `commandcoveragedb`.`result_channelid1`
--

/*!40000 ALTER TABLE `result_channelid1` DISABLE KEYS */;
/*!40000 ALTER TABLE `result_channelid1` ENABLE KEYS */;


--
-- Table structure for table `commandcoveragedb`.`result_channelid10`
--

DROP TABLE IF EXISTS `result_channelid10`;
CREATE TABLE `result_channelid10` (
  `ResultID` int(10) unsigned NOT NULL auto_increment,
  `CommandID` int(10) unsigned NOT NULL default '0',
  `BuildNo` varchar(45) NOT NULL default '',
  `TCMTestCaseID` varchar(45) NOT NULL default '',
  `Author` varchar(45) NOT NULL default '',
  `cmdDone` varchar(1) NOT NULL default '',
  PRIMARY KEY  (`ResultID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `commandcoveragedb`.`result_channelid10`
--

/*!40000 ALTER TABLE `result_channelid10` DISABLE KEYS */;
/*!40000 ALTER TABLE `result_channelid10` ENABLE KEYS */;


--
-- Table structure for table `commandcoveragedb`.`result_channelid11`
--

DROP TABLE IF EXISTS `result_channelid11`;
CREATE TABLE `result_channelid11` (
  `ResultID` int(10) unsigned NOT NULL auto_increment,
  `CommandID` int(10) unsigned NOT NULL default '0',
  `BuildNo` varchar(45) NOT NULL default '',
  `TCMTestCaseID` varchar(45) NOT NULL default '',
  `Author` varchar(45) NOT NULL default '',
  `cmdDone` varchar(1) NOT NULL default '',
  PRIMARY KEY  (`ResultID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `commandcoveragedb`.`result_channelid11`
--

/*!40000 ALTER TABLE `result_channelid11` DISABLE KEYS */;
/*!40000 ALTER TABLE `result_channelid11` ENABLE KEYS */;


--
-- Table structure for table `commandcoveragedb`.`result_channelid12`
--

DROP TABLE IF EXISTS `result_channelid12`;
CREATE TABLE `result_channelid12` (
  `ResultID` int(10) unsigned NOT NULL auto_increment,
  `CommandID` int(10) unsigned NOT NULL default '0',
  `BuildNo` varchar(45) NOT NULL default '',
  `TCMTestCaseID` varchar(45) NOT NULL default '',
  `Author` varchar(45) NOT NULL default '',
  `cmdDone` varchar(1) NOT NULL default '',
  PRIMARY KEY  (`ResultID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `commandcoveragedb`.`result_channelid12`
--

/*!40000 ALTER TABLE `result_channelid12` DISABLE KEYS */;
/*!40000 ALTER TABLE `result_channelid12` ENABLE KEYS */;


--
-- Table structure for table `commandcoveragedb`.`result_channelid2`
--

DROP TABLE IF EXISTS `result_channelid2`;
CREATE TABLE `result_channelid2` (
  `ResultID` int(10) unsigned NOT NULL auto_increment,
  `CommandID` int(10) unsigned NOT NULL default '0',
  `BuildNo` varchar(45) NOT NULL default '',
  `TCMTestCaseID` varchar(45) NOT NULL default '',
  `Author` varchar(45) NOT NULL default '',
  `cmdDone` varchar(1) NOT NULL default '',
  PRIMARY KEY  (`ResultID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `commandcoveragedb`.`result_channelid2`
--

/*!40000 ALTER TABLE `result_channelid2` DISABLE KEYS */;
/*!40000 ALTER TABLE `result_channelid2` ENABLE KEYS */;


--
-- Table structure for table `commandcoveragedb`.`result_channelid3`
--

DROP TABLE IF EXISTS `result_channelid3`;
CREATE TABLE `result_channelid3` (
  `ResultID` int(10) unsigned NOT NULL auto_increment,
  `CommandID` int(10) unsigned NOT NULL default '0',
  `BuildNo` varchar(45) NOT NULL default '',
  `TCMTestCaseID` varchar(45) NOT NULL default '',
  `Author` varchar(45) NOT NULL default '',
  `cmdDone` varchar(1) NOT NULL default '',
  PRIMARY KEY  (`ResultID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `commandcoveragedb`.`result_channelid3`
--

/*!40000 ALTER TABLE `result_channelid3` DISABLE KEYS */;
/*!40000 ALTER TABLE `result_channelid3` ENABLE KEYS */;


--
-- Table structure for table `commandcoveragedb`.`result_channelid4`
--

DROP TABLE IF EXISTS `result_channelid4`;
CREATE TABLE `result_channelid4` (
  `ResultID` int(10) unsigned NOT NULL auto_increment,
  `CommandID` int(10) unsigned NOT NULL default '0',
  `BuildNo` varchar(45) NOT NULL default '',
  `TCMTestCaseID` varchar(45) NOT NULL default '',
  `Author` varchar(45) NOT NULL default '',
  `cmdDone` varchar(1) NOT NULL default '',
  PRIMARY KEY  (`ResultID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `commandcoveragedb`.`result_channelid4`
--

/*!40000 ALTER TABLE `result_channelid4` DISABLE KEYS */;
/*!40000 ALTER TABLE `result_channelid4` ENABLE KEYS */;


--
-- Table structure for table `commandcoveragedb`.`result_channelid5`
--

DROP TABLE IF EXISTS `result_channelid5`;
CREATE TABLE `result_channelid5` (
  `ResultID` int(10) unsigned NOT NULL auto_increment,
  `CommandID` int(10) unsigned NOT NULL default '0',
  `BuildNo` varchar(45) NOT NULL default '',
  `TCMTestCaseID` varchar(45) NOT NULL default '',
  `Author` varchar(45) NOT NULL default '',
  `cmdDone` varchar(1) NOT NULL default '',
  PRIMARY KEY  (`ResultID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `commandcoveragedb`.`result_channelid5`
--

/*!40000 ALTER TABLE `result_channelid5` DISABLE KEYS */;
/*!40000 ALTER TABLE `result_channelid5` ENABLE KEYS */;


--
-- Table structure for table `commandcoveragedb`.`result_channelid6`
--

DROP TABLE IF EXISTS `result_channelid6`;
CREATE TABLE `result_channelid6` (
  `ResultID` int(10) unsigned NOT NULL auto_increment,
  `CommandID` int(10) unsigned NOT NULL default '0',
  `BuildNo` varchar(45) NOT NULL default '',
  `TCMTestCaseID` varchar(45) NOT NULL default '',
  `Author` varchar(45) NOT NULL default '',
  `cmdDone` varchar(1) NOT NULL default '',
  PRIMARY KEY  (`ResultID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `commandcoveragedb`.`result_channelid6`
--

/*!40000 ALTER TABLE `result_channelid6` DISABLE KEYS */;
/*!40000 ALTER TABLE `result_channelid6` ENABLE KEYS */;


--
-- Table structure for table `commandcoveragedb`.`result_channelid7`
--

DROP TABLE IF EXISTS `result_channelid7`;
CREATE TABLE `result_channelid7` (
  `ResultID` int(10) unsigned NOT NULL auto_increment,
  `CommandID` int(10) unsigned NOT NULL default '0',
  `BuildNo` varchar(45) NOT NULL default '',
  `TCMTestCaseID` varchar(45) NOT NULL default '',
  `Author` varchar(45) NOT NULL default '',
  `cmdDone` varchar(1) NOT NULL default '',
  PRIMARY KEY  (`ResultID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `commandcoveragedb`.`result_channelid7`
--

/*!40000 ALTER TABLE `result_channelid7` DISABLE KEYS */;
/*!40000 ALTER TABLE `result_channelid7` ENABLE KEYS */;


--
-- Table structure for table `commandcoveragedb`.`result_channelid8`
--

DROP TABLE IF EXISTS `result_channelid8`;
CREATE TABLE `result_channelid8` (
  `ResultID` int(10) unsigned NOT NULL auto_increment,
  `CommandID` int(10) unsigned NOT NULL default '0',
  `BuildNo` varchar(45) NOT NULL default '',
  `TCMTestCaseID` varchar(45) NOT NULL default '',
  `Author` varchar(45) NOT NULL default '',
  `cmdDone` varchar(1) NOT NULL default '',
  PRIMARY KEY  (`ResultID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `commandcoveragedb`.`result_channelid8`
--

/*!40000 ALTER TABLE `result_channelid8` DISABLE KEYS */;
/*!40000 ALTER TABLE `result_channelid8` ENABLE KEYS */;


--
-- Table structure for table `commandcoveragedb`.`result_channelid9`
--

DROP TABLE IF EXISTS `result_channelid9`;
CREATE TABLE `result_channelid9` (
  `ResultID` int(10) unsigned NOT NULL auto_increment,
  `CommandID` int(10) unsigned NOT NULL default '0',
  `BuildNo` varchar(45) NOT NULL default '',
  `TCMTestCaseID` varchar(45) NOT NULL default '',
  `Author` varchar(45) NOT NULL default '',
  `cmdDone` varchar(1) NOT NULL default '',
  PRIMARY KEY  (`ResultID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `commandcoveragedb`.`result_channelid9`
--

/*!40000 ALTER TABLE `result_channelid9` DISABLE KEYS */;
/*!40000 ALTER TABLE `result_channelid9` ENABLE KEYS */;


--
-- Procedure `commandcoveragedb`.`CountActiveCommands`
--

DROP PROCEDURE IF EXISTS `CountActiveCommands`;
DELIMITER $$

CREATE DEFINER=`temp`@`%` PROCEDURE `CountActiveCommands`(

channelid integer)
BEGIN

set @cmd = 'select count(distinct name) from command where isactive = "y" and channelid =';

set @cmd = concat(@cmd,channelid);

prepare stmt from @cmd;

execute stmt;



END $$

DELIMITER ;

--
-- Procedure `commandcoveragedb`.`CountActivePlugins`
--

DROP PROCEDURE IF EXISTS `CountActivePlugins`;
DELIMITER $$

CREATE DEFINER=`temp`@`%` PROCEDURE `CountActivePlugins`(

channelid integer)
BEGIN

set @cmd = 'select count(distinct pluginname) from plugin where isactive = "y" and channelid =';

set @cmd = concat(@cmd,channelid);

prepare stmt from @cmd;

execute stmt;



END $$

DELIMITER ;

--
-- Procedure `commandcoveragedb`.`CountCoveredCommands`
--

DROP PROCEDURE IF EXISTS `CountCoveredCommands`;
DELIMITER $$

CREATE DEFINER=`temp`@`%` PROCEDURE `CountCoveredCommands`(

channelid integer)
BEGIN

set @cmd = 'select count(distinct name) from command where commandid in(select commandid from result_channelid';

set @cmd = concat(@cmd,channelid, ' )');

set @cmd = concat(@cmd,' and channelid = ',channelid);

prepare stmt from @cmd;

-- select @cmd;

execute stmt;





END $$

DELIMITER ;

--
-- Procedure `commandcoveragedb`.`CountRowsInResultTable`
--

DROP PROCEDURE IF EXISTS `CountRowsInResultTable`;
DELIMITER $$

CREATE DEFINER=`temp`@`%` PROCEDURE `CountRowsInResultTable`(

channelID int,

commandID int)
BEGIN

set @cmd = 'select count(tcmtestcaseid) from result_channelid';



set @cmd = concat(@cmd,channelid);

set @cmd = concat(@cmd,' where commandid=',commandID);

prepare stmt from @cmd;

execute stmt;

END $$

DELIMITER ;

--
-- Procedure `commandcoveragedb`.`CountTotalCommands`
--

DROP PROCEDURE IF EXISTS `CountTotalCommands`;
DELIMITER $$

CREATE DEFINER=`temp`@`%` PROCEDURE `CountTotalCommands`(

channelid integer)
BEGIN

set @cmd = 'select count(distinct name) from command where channelid =';

set @cmd = concat(@cmd,channelid);

prepare stmt from @cmd;

execute stmt;



END $$

DELIMITER ;

--
-- Procedure `commandcoveragedb`.`CountTotalPlugins`
--

DROP PROCEDURE IF EXISTS `CountTotalPlugins`;
DELIMITER $$

CREATE DEFINER=`temp`@`%` PROCEDURE `CountTotalPlugins`(

channelid integer)
BEGIN

set @cmd = 'select count(distinct pluginname) from plugin where channelid =';

set @cmd = concat(@cmd,channelid);

prepare stmt from @cmd;

execute stmt;



END $$

DELIMITER ;

--
-- Procedure `commandcoveragedb`.`GetFromResultsTable`
--

DROP PROCEDURE IF EXISTS `GetFromResultsTable`;
DELIMITER $$

CREATE DEFINER=`temp`@`%` PROCEDURE `GetFromResultsTable`(

cmdid integer,

channelid integer

)
BEGIN

set @cmd = 'select * from ';

set @cmd = concat(@cmd ,'result_channelid',channelid,'  where commandid = ',cmdid);



prepare stmt from @cmd;

execute stmt ;



END $$

DELIMITER ;

--
-- Procedure `commandcoveragedb`.`GetLargestBuildNo`
--

DROP PROCEDURE IF EXISTS `GetLargestBuildNo`;
DELIMITER $$

CREATE DEFINER=`temp`@`%` PROCEDURE `GetLargestBuildNo`(

channelid integer)
Begin

     set @cmd = 'select max(cmdintroducedbuild) from command where channelid = ';

     set @cmd = concat(@cmd,channelid);



     prepare stmt from @cmd;

     execute stmt ;

END $$

DELIMITER ;

--
-- Procedure `commandcoveragedb`.`GetLargestBuildNoInPlugin`
--

DROP PROCEDURE IF EXISTS `GetLargestBuildNoInPlugin`;
DELIMITER $$

CREATE DEFINER=`temp`@`%` PROCEDURE `GetLargestBuildNoInPlugin`(

channelid integer)
Begin

     set @cmd = 'select max(pluginintroducedbuild) from plugin where channelid = ';

     set @cmd = concat(@cmd,channelid);



     prepare stmt from @cmd;

     execute stmt ;

END $$

DELIMITER ;

--
-- Procedure `commandcoveragedb`.`getNoteResults`
--

DROP PROCEDURE IF EXISTS `getNoteResults`;
DELIMITER $$

CREATE DEFINER=`temp`@`%` PROCEDURE `getNoteResults`(

commandID int)
BEGIN

  set @cmd = 'select * from notes where commandid = ';

  set @cmd = concat(@cmd, commandID);

  prepare stmt from @cmd;

  execute stmt;

END $$

DELIMITER ;

--
-- Procedure `commandcoveragedb`.`GetResults`
--

DROP PROCEDURE IF EXISTS `GetResults`;
DELIMITER $$

CREATE DEFINER=`temp`@`%` PROCEDURE `GetResults`(commandsCovered varchar(10),

commandsActive varchar(10),

pluginName varchar(40),

commandName varchar(200),

commandSearchType varchar(10),

tcmTestCase varchar(20),

tcmTestCaseSearchType varchar(10),

prefix varchar(40),

prefixSearchType varchar(10),

channelID int

)
BEGIN





     DECLARE temp varchar(1000);

     DECLARE tableName varchar(40);

     DECLARE columnsToBeFetched varchar(8000);

     DECLARE joinType varchar(20);

     set joinType = 'inner join';

     SET tableName = CONCAT('result_channelid',channelID);

     set columnsToBeFetched = "";

--     if(colCommandName <> 'y') then



          set columnsToBeFetched = concat(columnsToBeFetched, ' distinct name ,');

          set columnsToBeFetched = concat(columnsToBeFetched, ' cmd.commandid ,');

--     end if;

  --   if(colDescription <> 'y') then



          set columnsToBeFetched = concat(columnsToBeFetched, 'cmd.description ,');

--     end if;

--     if(colPrefix <> 'y') then



          set columnsToBeFetched = concat(columnsToBeFetched, 'prefix ,');

--     end if;

  --   if(colPlugin <> 'y') then



          set columnsToBeFetched = concat(columnsToBeFetched, 'pluginname ,');

--     end if;

   --  if(colIsActive <> 'y') then



          set columnsToBeFetched = concat(columnsToBeFetched, 'cmd.isActive ,');

   --  end if;

  --   if(colCmdIntroducedBuild <> 'y') then



          set columnsToBeFetched = concat(columnsToBeFetched, 'cmdIntroducedBuild ,');

   --  end if;

   --  if(colCmdInactiveBuild <> 'y') then



          set columnsToBeFetched = concat(columnsToBeFetched, 'cmdInactivebuild ,');

    -- end if;

   --  if(colCmdUpdatedBy <> 'y') then



        set columnsToBeFetched = concat(columnsToBeFetched, 'cmd.updatedby ,');

    -- end if;

   --  if(colTCMTestCaseid <> 'y') then





     SET @cmd = 'Select ';

     SET columnsToBeFetched = SUBSTRING(columnsToBeFetched,1,(LENGTH(columnsToBeFetched)-2));

     set @cmd = concat(@cmd , columnsToBeFetched,' from plugin as pl, command as cmd where ');

     -- if ( commandsCovered = 'ALL') then

        --  SET joinType = 'left join';

    -- end if;

    if(commandsCovered <> 'ALL') then

         if(commandsCovered = 'y') then

                  set @cmd = concat(@cmd, ' cmd.commandid in (select distinct commandid from ',tableName);

         end if;

         if(commandsCovered = 'n') then

                  set @cmd = concat(@cmd, ' cmd.commandid not in (select distinct commandid from ',tableName);

         end if;

         if(tcmtestcase<>'ALL') then

              set @cmd = concat(@cmd , '  where tcmtestcaseid ',tcmTestCaseSearchType,' "',tcmtestcase,'" ');

         end if;

         set @cmd = concat(@cmd,') AND ');

    end if;

    if(commandsCovered ='ALL') then

         if(tcmtestcase<>'ALL') then

              set @cmd = concat(@cmd, ' cmd.commandid in (select distinct commandid from ',tableName);

              set @cmd = concat(@cmd , ' where tcmtestcaseid ',tcmTestCaseSearchType,' "',tcmtestcase,'" ');

              set @cmd = concat(@cmd,') AND ');

         end if;



    end if;



    if(commandsActive ='y') then

        SET @cmd = CONCAT(@cmd, ' cmd.isActive = "y" AND ' );

    end if;

    if(commandsActive = 'n') then

        SET @cmd = CONCAT(@cmd, ' cmd.isActive = "n" AND ');

    end if;



    if(pluginName <> 'ALL') then

         set @cmd = CONCAT(@cmd, ' cmd.pluginid in (select pluginid from plugin where pluginname = "',pluginname,'" ');

         set @cmd = concat(@cmd,' and channelid =', channelID);

	 set @cmd = concat(@cmd,') and ');

     end if;





     if(prefix <>'ALL') then

          set @cmd = CONCAT(@cmd,' prefix ', prefixSearchType,' "',prefix, '" and ');

     end if;



     if(commandName <> 'ALL') then

        set @cmd = concat(@cmd, ' cmd.name  ', commandSearchType,' "',commandName, '" and ');

     end if;







set @cmd = concat(@cmd, ' pl.pluginid = cmd.pluginid and ');



set @cmd = concat(@cmd, ' cmd.channelid = ',channelID);



-- select @cmd;

prepare stmt from @cmd;

  execute stmt ;

END $$

DELIMITER ;

--
-- Procedure `commandcoveragedb`.`getUniquePluginNames`
--

DROP PROCEDURE IF EXISTS `getUniquePluginNames`;
DELIMITER $$

CREATE DEFINER=`temp`@`%` PROCEDURE `getUniquePluginNames`(

IN channelID INTEGER

)
BEGIN

set @cmd = 'SELECT distinct pluginname  from plugin  where channelid = ';

set @cmd = concat(@cmd, channelid);

prepare stmt from @cmd;

-- select @cmd;

execute stmt;



END $$

DELIMITER ;

--
-- Procedure `commandcoveragedb`.`insertIntoNotes`
--

DROP PROCEDURE IF EXISTS `insertIntoNotes`;
DELIMITER $$

CREATE DEFINER=`temp`@`%` PROCEDURE `insertIntoNotes`(

author varchar(100),

note varchar(65000),

notedate date,

commandID int)
BEGIN



set @cmd = 'insert into notes (commandid,author,date,note) values(';

set @cmd = concat(@cmd,commandid);

set @cmd = concat(@cmd,',"' ,author,'"');

set @cmd = concat(@cmd,',"' ,notedate,'" ');

set @cmd = concat(@cmd,',"' ,note,'")');

-- select @cmd;

prepare stmt from @cmd;

execute stmt;

END $$

DELIMITER ;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;