GSGJJ/8、对接�.../固始查询...

-----m_personinfo_net 个人信息表与m_unitinfo_net单位信息表是unit_account 关联，
-----------l_baseinfo_net贷款信息表与l_return_net贷款归还信息表是用loan_account关联，
-------------m_personinfo_net 与 l_baseinfo_net 是用unit_account 和person_account 关联的

--我们是先判断是否查义此人只有一条信息，如果是，则进入查询，否则不查询
select person_name as f_name,count(1) as f_cnt 
from m_personinfo_net 
where person_status < '2' and person_id = '622205196201010022' and dy9 = '123' 
group by person_name

--------------公积金基本信息-----------------------------

select a.person_name 职工姓名,a.person_id 身份证号,a.unit_account 单位账号,
	a.dy9 密码,a.person_account 职工账号,b.unit_name 所在单位,
	c.center_name 所属办事处,convert(varchar(10),a.accountant_date,121) 开户日期,
	(case a.frozen 
	when '0' then '正常' 
	 when  '1' then '贷款冻结' 
	 when  '2' then '担保冻结' 
	 when  '3' then '工资担保' 
	 when  '4' then '公积金+工资担保'
	 when  '5' then '异地贷款' 
	 when '6'	then '法院冻结' end)贷款状态,
	(case a.person_status
	when  '0' then '正常' 
	 when  '1' then '封存' 
	 when '2' then '转出' 
	 when  '3' then '销户' 
	 when '4' then '合户转出'
	 when  '5' then '跨中心转出' end)缴存状态,a.salary 缴存基数,
 	cast(a.unit_ratio as varchar) + '%' 单位缴存率,cast(a.person_ratio as varchar) + '%' 个人缴存率,
	a.person_part 个人月缴额,a.unit_part 单位月缴额,a.begin_balance 上年余额,
	a.thisyear_handin 本年缴交,a.thisyear_add 本年补缴,a.thisyear_draw 本年支取,
	a.lastyear_interest_deposit+a.lastyear_interest_current 上年结息,
	convert(varchar(7),a.last_date,120) 缴至年月,a.person_balance 账户余额,
	a.unit_part+a.person_part as 月缴额 
from m_personinfo_net a 
inner join m_unitinfo_net b on a.unit_account = b.unit_account 
left join superior_para_net c on a.belong = c.center_code
where --a.person_status < '2' and a.person_id = '622205196201010022' and a.dy9 = '123'
a.unit_account = '2010100001001' and a.person_account = '00000016'
--------------------公积金明细----------------
--先根据身份证号查询出来单位账号和个人账号，再把单位账号和个人账号传给存储过程
select unit_account,person_account 
from m_personinfo_net 
where person_status < '2' and person_id = '622205196201010022' and dy9 = '123'

exec net_p_query_gjj_detail @v_uaccount = '2010100001001',@v_paccount = '00000016'

--------------------贷款信息------------------------------------

select loan_account,a.contract_no 贷款合同编号,a.person_name 姓名,a.unit_account 单位账号,
	a.unit_name 单位名称,a.person_id 身份证号,a.bank_account 还款账户,
	a.loan_month 贷款年限,a.loan_amount 贷款金额,convert(varchar(10),a.loan_date,120) 放款日,
	convert(varchar(10),a.loan_date,120) 月还款日,	convert(varchar(10),dateadd(month,a.loan_month,a.loan_date),120)最后还款日, 
	b.r_describe 还款方式,a.interest_rate 贷款利率,
	d.已还利息,a.loan_amount - isnull(d.已还本金,0.00) 贷款余额,	
	case when a.if_logout = '0' then '未注销' else '已注销' end  还款状态,
	d.已还本金,c.assure_type 担保方式
from l_baseinfo_net a 
left outer join	l_returntype_net b on a.return_type = b.return_type 
left outer join	l_assuretype_net c on a.assure_type = c.assure_code 
left outer join	
(select loan_account,sum(return_principal) as 已还本金,sum(return_interest) 已还利息
from l_return_net  group by loan_account) d on a.loan_account = d.loan_account	
--left outer join m_personinfo_net e on a.unit_account = e.unit_account and a.person_account = e.person_account
where  --e.person_status < '2' and  e.person_id = '622205196201010022' and e.dy9 = '123'
a.unit_account = '2010100001001' and a.person_account = '00000016' AND a.if_logout = '0'
order by a.loan_date

------------------贷款还款信息-------------------------

select convert(char(10),b.return_date,121) 还款日期,
	case b.return_yearmonth when '1900-01' then '' else b.return_yearmonth end as 所属年月, 
	isnull(b.return_principal,0)归还本金,isnull(b.return_interest,0)归还利息,isnull(b.overdue_interest,0)归还罚息,
	isnull(b.return_principal + b.return_interest + b.overdue_interest,0) 合计,
	c.loan_amount -
(select isnull(sum(a.return_principal),0)as return_sum 
		from l_return_net a 
		where a.return_id <= b.return_id
		and a.loan_account = b.loan_account)余额
from l_baseinfo_net c
left join l_return_net b on c.loan_account = b.loan_account 
--left join m_personinfo_net d on c.unit_account = d.unit_account and c.person_account = d.person_account
where -- d.person_status < '2' and d.person_id = '622205196201010022'and  d.dy9 = '123'
c.unit_account = '2010100001001' and c.person_account = '00000016' AND c.if_logout = '0'

-----------------------------------------------

-------------ivr中公积金基本信息（根据身份证号查询）---------------
select person_balance 公积金余额
from m_personinfo_net
where --person_status < '2' and person_id = '622205196201010022' and dy9 = '123'
unit_account = '2010100001001' and person_account = '00000016'

select convert(varchar(10),last_date,121) 已缴至日期
from m_personinfo_net
where --person_status < '2' and person_id = '622205196201010022' and dy9 = '123'
unit_account = '2010100001001' and person_account = '00000016'

select unit_part + person_part 公积金月缴额
from m_personinfo_net
where --person_status < '2' and person_id = '622205196201010022' and dy9 = '123'
unit_account = '2010100001001' and person_account = '00000016'


select a.loan_amount - 已还本金 贷款余额
from l_baseinfo_net a 
left join
(select loan_account,isnull(sum(return_principal),0) as 已还本金
from l_return_net  group by loan_account) b
on a.loan_account = b.loan_account
--left join m_personinfo_net c on a.unit_account = c.unit_account and a.person_account = c.person_account
where --c.person_status < '2' and c.person_id = '622205196201010022' and c.dy9 = '123'
a.unit_account = '2010100001001' and a.person_account = '00000016'

select isnull(convert(varchar(10),max(return_date),121),'无还款信息')最后一次还款日
from l_baseinfo_net a
left join l_return_net b on a.loan_account = b.loan_account
--left join m_personinfo_net c on a.unit_account = c.unit_account and a.person_account = c.person_account
where --c.person_status < '2' and c.person_id = '622205196201010022' and c.dy9 = '123'
a.unit_account = '2010100001001' and a.person_account = '00000016'

-----------------ivr中公积金单位信息--------------
select unit_balance 单位余额,convert(varchar(7),last_date,121)缴至年月
from m_unitinfo
where unit_account  = '2010100001001'