set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: hy
-- Create date: <Create Date,,>
-- Description: 后台企业管理
-- [P_V_EffectiveInfo_getTable] '',1,'','','','','','','','','','',-1,'',1,20
-- =============================================
ALTER PROCEDURE [dbo].[P_V_EffectiveInfo_getTable]
@strKeys varchar(300), ---搜索关键字
@intSerachType int, ---关键字类型
@intParentIndustry VARCHAR(5), -- 行业大类别
@intIndustry varchar(10), -- 行业类别
@intEffectiveType varchar(2), ---企业性质
@intOverdue varchar(2), ---核实
@dtRegStart varchar(40),--起始日期
@dtRegEnd varchar(40),--dtEndDate
@intMemberLv varchar(2),--会员级别
@intCurrState varchar(2),--状态
@strCrty varchar(30),--单位所在地
@strForm varchar(20), --来源
@intFMdredge INT, -- 是否加入自由市场
@strAdd varchar(20),--追加
@Information varchar(100),--信息完善度
@strEntLog varchar(30),--企业Logo
@PageIndex int ,--当前页码
@PageSize int--每页数据条数
AS
BEGIN
--创建临时表存储数据
if object_id('tempdb.dbo.#temp999') is not null drop table #temp999 ;
declare @iEnd int
declare @iStart int
---根据当前页和每页显示的调试获取数据跨度范围
SET @iStart = (@PageIndex-1)*@PageSize+1
SET @iEnd = (@PageIndex-1)*@PageSize+@PageSize ;
----创建带行号的零时数据插入临时表里面
with #temp1 as (
---普通的查询
select ROW_NUMBER() over(order by dtRegDate desc) as PageIndex ,strClientID ,strAccount ,strEffectiveName
,tb.strName +ISNULL((SELECT strName FROM zh_Sys_crty AS tc WHERE tc.intCrtyCode=dbo.SPLIT(ta.intAdderCode,',',1) AND tc.intParentCrtyCode=dbo.SPLIT(ta.intAdderCode,',',0) ),'') AS ctryNameintAdderCode --省+城市
,(SELECT Explainss FROM zh_Sys_Position WHERE dictNO=ta.intIndustry) AS intIndustrExplainss ,strEffectiveTel ,dtRegDate,CASE WHEN intOverdue=1 THEN '已' ELSE '未' END AS intOverdue --,MemberLv
,intStat,case WHEN ISNULL(strBlImg,'')='' THEN '无' ELSE '有' END AS strBlImg1,strEffectivephone
--是否追加
,case WHEN ISNULL(strAdditional,'')='' THEN '无' ELSE '有' END AS strAdditional
--获取后台向个人发送信息条数
,(SELECT COUNT(*) FROM dbo.zh_Sys_MessageLog tf WHERE ta.strClientID=tf.strClient AND intType=10)AS noteCount
,strForm,CASE WHEN ISNULL(strEntLog,'')='' THEN '无' ELSE '有' END AS strEntLog,strSysPerfectRecord
from V_EffectiveInfo ta LEFT JOIN dbo.zh_Sys_crty tb
ON (dbo.SPLIT(ta.intAdderCode,',',0)=tb.intCrtyCode AND tb.intParentCrtyCode=-1)
WHERE
--行业搜索
(@intParentIndustry='' OR intParentIndustry=@intParentIndustry)
AND (@intIndustry='' OR intIndustry=@intIndustry)
AND (@intEffectiveType='' OR intEffectiveType=@intEffectiveType )
AND ( @intOverdue='' OR intOverdue=@intOverdue)
AND ( @dtRegStart='' OR dtRegDate>@dtRegStart )
AND ( @dtRegEnd='' OR dtRegDate<@dtRegEnd )
AND (@intMemberLv='' OR MemberLv=@intMemberLv )
AND (@intCurrState='' or intStat=@intCurrState)
AND ((@strCrty='' or dbo.split(intAdderCode,',',0)+','=@strCrty) or intAdderCode=@strCrty)
and (@strForm='' or strform=@strForm)
-- 是否开启加入自由市场
AND (@intFMdredge=-1 OR intFMdredge=@intFMdredge)
--追加
and (@strAdd='' or (@strAdd='1' and isnull(strAdditional,'')<>'') or (@strAdd='0' and isnull(strAdditional,'')='') )
--企业信息完善度
and (@Information='' or (@Information='1' and isnull(strSysPerfectRecord,'')<>'') or (@Information='0' and isnull(strSysPerfectRecord,'')='') )
--企业Logo
and (@strEntLog='' or (@strEntLog='1' and isnull(strEntLog,'')<>'') or (@strEntLog='0' and isnull(strEntLog,'')='') )
--AND((@intSerachType=1 AND (strAccount=@strKeys OR @strKeys='' ))
AND((@intSerachType=1 AND (@strKeys='' OR strAccount like '%'+@strKeys+'%' ))
OR (@intSerachType=2 AND ( @strKeys='' OR strEffectiveName like '%'+@strKeys+'%'))
OR (@intSerachType=3 AND ( @strKeys='' OR strEffectiveTel=@strKeys))
OR (@intSerachType=4 AND ( @strKeys='' OR strForm like '%'+@strKeys+'%'))
OR (@intSerachType=5 AND ( @strKeys='' OR strClientID like '%'+@strKeys+'%'))
)
--连接表，根据ID查询省市中文名
--AND dbo.SPLIT(ta.intAdderCode,',',1)=tb.intCrtyCode
) select * into #temp999 from #temp1
----查询临时表里面的数据并且输出
select * from #temp999 where PageIndex between CAST(@iStart as varchar) and CAST(@iEnd as varchar)ORDER BY PageIndex asc
----- 查询总数据条数
select COUNT(*) as SunPage from #temp999
----查询当前企业对应的招聘条数总数和
select COUNT(*) as SunJob from zh_u_PositionManage WHERE strClientID in(SELECT strClientID FROM #temp999)
END
