<?xml version="1.0" ?>

<resources>

  <!-- PODs database -->
  <!-- ============= -->

  <resource name="SYSTEM">

    <sql name='getMaxId'          query='select max([tableid]) as max_[tableid] from [tablename]' />
    <sql name="getTableStatus"    query="show table status like '[tablename]'" />
    <sql name="getTableStatus2"   query="show table status in [DB] like '[tablename]'" />

    <!-- language -->

    <sql name='putLanguage'        query='insert into [DB].languages (langcode, locale, langname) values ({langcode}, {locale}, {langname})' />
    <sql name='getLanguages'       query='select * from [DB].languages order by langname' />
    <sql name='putLanguageStore'   query='insert into [DB].languagestorage (owner, storemode, store, inmem) values ({owner}, {storemode}, {store}, {inmem})' />
    <sql name='getLanguageStore'   query='select * from [DB].languagestorage where owner={owner} and store={store}' />
    <sql name='getLanguageStores'  query='select * from [DB].languagestorage' />
    <sql name='makeLanguageStore'  query="create table [DB].[store] (langcode varchar(3) not null default '',phraseref varchar(20) not null default '',phrase text not null default '', PRIMARY KEY(langcode,phraseref)) ENGINE=MyISAM DEFAULT CHARSET=utf8" />
    <sql name='putPhraseStore'     query='replace into [DB].[store] (langcode,phraseref,phrase) values ({langcode},{phraseref},{phrase})' />
    <sql name='getPhraseStore'     query='select * from [DB].[store] where langcode={langcode} and phraseref={phraseref}' />
    <sql name='putPhrase'          query='replace into [DB].languagephrases (langcode,phraseref,translatedby,translatedon,phrase,lstoreid) values ({langcode},{phraseref},{translatedby},{translatedon},{phrase},{lstoreid})' />
    <sql name='getPhrases'         query='select * from [DB].languagephrases where langcode={langcode}' />
    <sql name='getPhrase'          query='select * from [DB].languagephrases where langcode={langcode} and phraseref={phraseref}' />

    <!-- general lists -->

    <sql name='getLists'              query='select * from lists order by listname, listcode' />
    <sql name='getListByName'         query='select * from lists where listname={listname} order by listorder' />
    <sql name='getListEntry'          query='select * from lists where listname={listname} and listcode={listcode}' />
    <sql name='modListEntry'          query='replace into lists (listname, listcode, listdesc) values ({listname}, {listcode}, {listdesc})' />
    <sql name='delListEntry'          query='delete from lists where listname={listname} and listcode={listcode}' />

    <!-- content management -->

    <sql name='getPageMenu'           query='select * from pagemenus where parentid={parentid} and menuid={menuid}' />
    <sql name='putPageMenu'           query='insert into pagemenus (parentid, menuid, title, updatedby, updatedon) values ({parentid}, {menuid}, {title}, {updatedby}, {updatedon})' />
    <sql name='delPageMenu'           query='delete from pagemenus where parentid={parentid} and menuid={menuid}' />
    <sql name='getPageBlocks'         query='select pageid,blockid,updatedby,updatedon from pageblocks' />
    <sql name='getPageBlock'          query='select * from pageblocks where pageid={pageid} and blockid={blockid}' />
    <sql name='putPageBlock'          query='replace into pageblocks (pageid, blockid, content, updatedby, updatedon) values ({pageid}, {blockid}, {content}, {updatedby}, {updatedon})' />

    <!-- lucene -->
    <sql name='checkDocid'            query='select docid from lucenedocids where docid={docid}' />
    <sql name='addDocid'              query='insert into lucenedocids (docid) values ({docid})' />
    <sql name='putDocid'              query='replace into lucenedocids (docid) values ({docid})' />
    <sql name='delDocid'              query='delete from lucenedocids where docid={docid}' />
    <sql name='delDocidsByRep'        query="delete from lucenedocids where docid like '[repository]%'" />
    <sql name='setDocidsIndex'        query="alter table lucenedocids add primary key (docid)" />
    <sql name='delDocidsIndex'        query="alter table lucenedocids drop primary key" />

    <!-- activity logging -->

    <sql name='putActivity'          query='insert into activitylog (userid, timeofact, pagename, fields) values ({userid}, {when}, {pagename}, {fields})' />
    <sql name='getActivity'          query='select * from activitylog where activityid={activityid}' />
    <sql name='getActivityByTime'    query='select * from activitylog where timeofact={timeofact}' />
    <sql name='searchActivity'       query='select * from activitylog [whereclause]' />

    <!-- general category trees -->

    <sql name='getTrees'             query='select * from trees order by treename' />
    <sql name='getTreeByName'        query='select * from trees where treename={treename}' />
    <sql name='getTreeSets'          query='select * from treesets where treeid={treeid} order by depth,setname' />
    <sql name='putTree'              query='insert into trees (treename, treedesc, maxchild, mcscale, userid) values ({treename}, {treedesc}, {maxchild}, {mcscale}, {userid})' />
    <sql name='modTree'              query='update trees set treename={treename},treedesc={treedesc},maxchild={maxchild},mcscale={mcscale},userid={userid} where treeid={treeid}' />
    <sql name='delTree'              query='delete from trees where treeid={treeid}' />
    <sql name='putTreeSet'           query='replace into treesets (setid, treeid, setcode, setname, colour, source, dstamp, depth, setright, setleft)
                                                         values ({setid}, {treeid}, {setcode}, {setname}, {colour}, {source}, {dstamp}, {depth}, {setright}, {setleft})' />
    <sql name='delTreeSet'           query='delete from treesets where setid={setid}' />
    <sql name='delTreeSetsAll'       query='delete from treesets where treeid={treeid}' />
    <sql name="getMaxSetId"          query="select max(setid) as maxid from treesets" />

    <!-- scheduler -->

    <sql name='addScheduledTask'     query='insert into scheduledtasks (userid,taskname,taskcat,taskdesc,employ,schedinfo,data,resultdata,resultpage)
                                                             values ({userid},{taskname},{taskcat},{taskdesc},{employ},{schedinfo},{data},{resultdata},{resultpage})' />
    <sql name='getScheduledTasks'    query='select * from scheduledtasks where userid={userid}' />
    <sql name='getScheduledTask'     query='select * from scheduledtasks where taskid={taskid}' />
    <sql name='delScheduledTask'     query='delete from scheduledtasks where taskid={taskid}' />
    <sql name='setTaskStatus'        query='update scheduledtasks set status={status}, elapse={elapse} where taskid={taskid}' />
    <sql name='getTaskByName'        query='select * from scheduledtasks where userid={userid} and taskname={taskname}' />
    <sql name='delTaskByName'        query='delete from scheduledtasks where userid={userid} and taskname={taskname}' />

    <!-- user admin -->

    <sql name='addGroup' query='insert into groups (groupname,sharecases,accesslevel,notes) values ({groupname},{sharecases},{accesslevel},{notes})' />
    <sql name='modGroup' query='update groups set groupname={groupname}, sharecases={sharecases}, accesslevel={accesslevel}, notes={notes} where groupid={groupid}' />
    <sql name='delGroup' query='delete from groups where groupid={groupid}' />
    <sql name='getGroupById'   query='select * from groups where groupid={groupid}' />
    <sql name='getGroupByName' query='select * from groups where groupname={groupname}' />
    <sql name='getGroups'      query='select * from groups order by groupname' />

    <sql name='addUserMinimal' query='insert into users (username,email,stayin,lastip,lasthost,enabled) values ({username},{emailaddr},{stayin},{lastip},{lasthost},{enabled})' />

    <sql name='addUser' query='insert into users ( firstname,lastname,midname,title,schemename,language,currency,tts,username,nickname,password,questions,answers,
                       lastip,lasthost,lastlogin,stayin,regcode,regconfirmed,accesscode,multilogin,tandc,promocode,telephone,mobile,fax,email,
                       pager,allowmsgs,howfind,addressline1,addressline2,addressline3,city,region,postcode,countrycode,groupcode,
                       enabled,regdate,notes,updatedon,updatedby,groupid,dob,sex,jobtitle,hobbies
                       ) values
                      ({firstname},{lastname},{midname},{title},{schemename},{language},{currency},{tts},{username},{nickname},{password},{questions},{answers},
                       {lastip},{lasthost},{lastlogin},{stayin},{regcode},{regconfirmed},{accesscode},{multilogin},{tandc},{promocode},{telephone},{mobile},{fax},{email},
                       {pager},{allowmsgs},{howfind},{addressline1},{addressline2},{addressline3},{city},{region},{postcode},{countrycode},{groupcode},
                       {enabled},{regdate},{notes},{updatedon},{updatedby},{groupid},{dob},{sex},{jobtitle},{hobbies})' />



    <sql name='modUser' query='update users set firstname={firstname},lastname={lastname},midname={midname},title={title},schemename={schemename},language={language},currency={currency},tts={tts},username={username},nickname={nickname},password={password},questions={questions},answers={answers},
                                 lastip={lastip},lasthost={lasthost},lastlogin={lastlogin},stayin={stayin},regcode={regcode},regconfirmed={regconfirmed},accesscode={accesscode},multilogin={multilogin},tandc={tandc},promocode={promocode},telephone={telephone},mobile={mobile},fax={fax},email={email},
                                 pager={pager},allowmsgs={allowmsgs},howfind={howfind},addressline1={addressline1},addressline2={addressline2},addressline3={addressline3},city={city},region={region},postcode={postcode},countrycode={countrycode},groupcode={groupcode},
                                 enabled={enabled},regdate={regdate},notes={notes},updatedon={updatedon},updatedby={updatedby},groupid={groupid},dob={dob},sex={sex},jobtitle={jobtitle},hobbies={hobbies}
                                 where userid={userid}' />

    <sql name='delUser' query='delete from users where userid={userid}' />
    <sql name='setUserConfirm' query='update users set regconfirmed={regconfirmed} where userid={userid}' />
    <sql name='setUserLogin'   query='update users set lastlogin={lastlogin}, lasthost={host}, lastip={ipaddress} where userid={userid}' />

    <sql name='getUserByUsername' query='select * from users where username={username}' />
    <sql name='getUserByEmail'    query='select * from users where email={emailaddr}' />

    <sql name='getUsersForMsg'   query='select userid, title, firstname, lastname, email, username, mobile from users where allowmsgs=1' />
    <sql name='getUser'          query='select * from users where userid={userid}' />
    <sql name='getUserById'      query='select * from users where userid={userid}' />

    <sql name='getUsersInGroup'  query='select userid from users where groupid={groupid}' />

    <sql name='getNickname' query="select nickname, CONCAT_WS(' ', firstname, lastname) as fullname from users where userid={userid}" />

    <sql name='getUsers'           query="select *,CONCAT_WS(' ', title, firstname, lastname) as fullname from users order by fullname" />
    <sql name='getUserIds'         query="select userid,CONCAT_WS(' ', title, firstname, lastname) as fullname from users order by fullname" />

    <!-- search arguments -->

    <sql name='getCurrentSearches' query="select * from searchargs where userid={userid} and status!=''" />
    <sql name='getSearches'        query='select searchid,searchname from searchargs where userid={userid} and formname={formname}' />
    <sql name='getSearch'          query='select * from searchargs where searchid={searchid}' />
    <sql name='getSearchByName'    query='select * from searchargs where userid={userid} and formname={formname} and searchname={searchname}' />
    <sql name='putSearch'          query='insert into searchargs (userid, formname, searchname, args) values ({userid}, {formname}, {searchname}, {args})' />
    <sql name='modSearch'          query='update searchargs set searchname={searchname}, args={args} where searchid={searchid}' />
    <sql name='setSearchStatus'    query='update searchargs set status={status} where searchid={searchid}' />
    <sql name='setSearchTimes'     query='update searchargs set runat={runat}, elapse={elapse} where searchid={searchid}' />
    <sql name='delSearch'          query='delete from searchargs where searchid={searchid}' />

    <!-- licence management -->
    <sql name='addLicence' query='insert into licences ( luid, userid, licname, lictype, serviceid, servicetype, refurl, startdate, enddate, maxsize, notifydate, options, enabled )
                      values ({luid}, {userid}, {licname}, {lictype}, {serviceid}, {servicetype}, {refurl}, {startdate}, {enddate}, {maxsize}, {notifydate}, {options}, {enabled} )' />

    <sql name='modLicence' query='update licences set licname={licname}, startdate={startdate}, enddate={enddate}, lictype={lictype}, maxsize={maxsize}, refurl={refurl}, notify={notifydate}, options={options}, enabled={enabled} where licenseid={licenseid}' />

    <sql name='payLicence' query='update licences set startdate={startdate}, enddate={enddate}, lictype={lictype}, enabled={enabled} where licenseid={licenseid}' />

    <sql name='getLicenceById'    query='select * from licences where licenseid={licenseid}' />
    <sql name='getLicenceByUID'   query='select * from licences where luid={luid}' />
    <sql name='getLicences'       query='select * from licences where userid={userid}' />

    <!-- colour schemes -->

    <sql name="getAllColourSchemes" query="SELECT schemename,schemedef FROM colourschemes" />
    <sql name="getColourSchemes"    query="SELECT schemeid,schemename FROM colourschemes" />
    <sql name="getColourScheme"     query="SELECT * FROM colourschemes where schemename={schemename}" />
    <sql name="putColourScheme"     query="INSERT INTO colourschemes (schemename, schemedef, enabled, dstamp) values ({schemename}, {schemedef}, {enabled}, {dstamp})" />
    <sql name="modColourScheme"     query="UPDATE colourschemes SET schemedef={schemedef}, enabled={enabled}, dstamp={dstamp} where schemename={schemename}" />
    <sql name="getUserScheme"       query="SELECT schemename FROM users WHERE userid={userid}" />
    <sql name="setUserScheme"       query="UPDATE users SET schemename={schemename} where userid={userid}" />

    <!-- copy tables to memory for fast access -->
    <sql name='dropprods'           query='DROP TABLE products_m' />
    <sql name='copyprods'           query='CREATE TABLE products_m ENGINE=MEMORY AS SELECT * FROM products' />



  </resource>

</resources>
