# Date: 07/07/06
# Version: 105

$DOC(REPORT_INVESTORS_PIF)
 name=Отчет Список пайщиков ПИФ
 class=6
 product=ALD_PIF
 name_formula=null
 remark_formula=null

 $STATE(NEW)
  name=Начальный
  class=1
 $ENDSTATE

 $FORM(D_PIF_INVESTOR_REP_DET)
  name=Перечень отчета
  class=4
  filter=FORM2
  target_state=
  uo=null
  dw=d_pif_investor_rep_det
  procedure=ap_pif_investor_rep_det
  patterns=GENERIC_VIEW_LIST
  user_define=1
  isvisible=1
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(13063795)
create procedure dbo.%PROC% @sid varchar(30) = null
      ,
@in_date_de_d varchar(36) = null,
@owner_id_s varchar(30) = null,
@payer_id_s varchar(30) = null,
@payer_name varchar(255) = null
as
.BEGIN('N')

.CHECK_USER
.CHECK_VIEW_LIST

--.ARGCONVERT_FILTER

.ID_VAR(@owner_id)
.ID_VAR(@payer_id)
.ID_VAR(@fund_plan_id)
.ID_VAR(@acc_id)
declare @oper_date datetime

select @owner_id=convert(numeric,@owner_id_s)
select @payer_id=convert(numeric,@payer_id_s)
select @fund_plan_id = .ARG_PLAN('PIF_ACCOUNT')
select @acc_id = .SD_ACCID_FROM_EFFNO(@fund_plan_id,'96.1')
select @oper_date=convert(datetime,@in_date_de_d,3)
if @oper_date is null
 select @oper_date = .OPERDAY
if @owner_id is null
 select @owner_id=.ORGANIZATION_PIF


.TOTALS_QUERY_SUB(@owner_id,@fund_plan_id,@oper_date,@oper_date,@acc_id,N,N,'partners',N,N,N,L,N,Y,N,Y)

declare @pays_qty_all .PIF_PAY_TYPE, @payer_name_s varchar(255)
if isnull(@payer_name,'')='' select @payer_name_s='%'
 else select @payer_name_s='%'+rtrim(ltrim(upper(@payer_name)))+'%'

select @pays_qty_all=sum(isnull((t.q_saldo_ce-t.q_saldo_de),0))
  from #tmp_oss t, t_partners p
 where p.id=t.subc2_id and .ITEMS_EXISTS(p.id)
   and ((t.subc2_id=@payer_id and @payer_id is not null)
--       or ((isnull(rtrim(ltrim(upper(p.partner_name))),'') LIKE isnull('%'+rtrim(ltrim(upper(@payer_name)))+'%','%'))
       or ((isnull(rtrim(ltrim(upper(p.partner_name))),'') LIKE @payer_name_s)
          and @payer_id is null))


select
@sid as sid,
isnull(.STR_FOR_IF(p.partner_code),'Не задан') as investor_code,
isnull(.STR_FOR_IF(p.partner_name),'Не задано') as investor_name,
--isnull(isnull(.STR_FOR_IF(t_items.item_remark),.STR_FOR_IF(p.partner_name)),'Не задан') as investor_name,
.PAYS_QTY_CALC((t.q_saldo_ce-t.q_saldo_de),@owner_id) as pays_qty,
.PAYS_QTY_CALC(@pays_qty_all,@owner_id) as pays_qty_all
from #tmp_oss t, t_partners p, .ACTUAL_TABLES
where p.id=t.subc2_id
--and isnull((t.q_saldo_ce-t.q_saldo_de),0) > 0
.ACTUAL_WHERE(p.id)
and ((t.subc2_id=@payer_id and @payer_id is not null)
-- or ((isnull(rtrim(ltrim(upper(p.partner_name))),'') LIKE isnull('%'+rtrim(ltrim(upper(@payer_name)))+'%','%'))
or ((isnull(rtrim(ltrim(upper(p.partner_name))),'') LIKE @payer_name_s)
 and @payer_id is null))

order by investor_code

.DROP_QUERY_TABLES(#tmp_oss)

.END
  $ENDTEXT(13063795)
 $ENDFORM

 $FORM(D_PIF_INVESTOR_REP_HEADER)
  name=Заголовок отчета
  class=4
  filter=FORM2
  target_state=
  uo=uo_list_rep
  dw=d_pif_investor_rep_header
  procedure=ap_pif_investor_rep_header
  patterns=GENERIC_VIEW_LIST
  user_define=1
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=0
  height=0
  proc_text=$TEXT(13063795)
create procedure dbo.%PROC% @sid varchar(30) = null
   ,
@in_date_de datetime = null,
@owner_id_s varchar(30) = null,
@payer_id_s varchar(30) = null,
@payer_name varchar(255) = null
as
.BEGIN('N')

.CHECK_USER
.CHECK_VIEW_LIST

.ID_VAR(@owner_id)
declare @in_date_s varchar(36)

select @owner_id=convert(numeric,@owner_id_s)
if @owner_id is null
 select @owner_id=.ORGANIZATION_PIF
.TEST_ORGANIZATION_PIF(@owner_id)

select @in_date_s=convert(varchar(36),@in_date_de,104)
if @in_date_s is null
 select @in_date_s = convert(varchar(36),(.OPERDAY),104)

select
 @sid as sid,
 @in_date_s as in_date_s,
 (select t_partners.partner_name from t_partners where t_partners.id=@owner_id) as full_name,
 convert(varchar,@in_date_de,3) as in_date_de_d,
 @payer_id_s as payer_id_s,
 @payer_name as payer_name,
 @owner_id_s as owner_id_s

.END
  $ENDTEXT(13063795)

  $FILTER(PAYER_NAME)
   type=1
   label=payer_name
   prop=null
   arg_code=payer_name
   arg_type=varchar
   arg_length=255
   arg_prec=null
   arg_scale=null
  $ENDFILTER

  $FILTER(OWNER_ID_S)
   type=1
   label=owner_id_s
   prop=null
   arg_code=owner_id_s
   arg_type=varchar
   arg_length=30
   arg_prec=null
   arg_scale=null
  $ENDFILTER

  $FILTER(PAYER_ID_S)
   type=1
   label=payer_id_s
   prop=null
   arg_code=payer_id_s
   arg_type=varchar
   arg_length=30
   arg_prec=null
   arg_scale=null
  $ENDFILTER

  $FILTER(IN_DATE_DE)
   type=1
   label=in_date_de
   prop=null
   arg_code=in_date_de
   arg_type=datetime
   arg_length=null
   arg_prec=null
   arg_scale=null
  $ENDFILTER
 $ENDFORM

 $FORM(FORM2)
  name=Фильтр
  class=6
  filter=null
  target_state=
  uo=uo_filter
  dw=d_pif_investor_rep_filter
  procedure=ap_10000000000088453
  patterns=GENERIC_MOVE
  user_define=0
  isvisible=0
  istab=0
  posx=0
  posy=0
  width=2167
  height=592
  proc_text=$TEXT(13063795)
create procedure dbo.%PROC% @sid varchar(30) = null
as
begin
declare @owner_id numeric
select @owner_id=.ORGANIZATION_PIF

select
convert(datetime,null) as in_date_de,
convert(varchar(30),null) as payer_id_s,
convert(varchar(255),null) as payer_name,
convert(varchar(30),@owner_id) as owner_id_s,
(select partner_name from t_partners where id=@owner_id) as organization_name
end
  $ENDTEXT(13063795)
 $ENDFORM

 $STATE2ACTION(NEW.D_PIF_INVESTOR_REP_DET)
  state=NEW
  action=D_PIF_INVESTOR_REP_DET
  is_available=0
 $ENDSTATE2ACTION

 $STATE2ACTION(NEW.D_PIF_INVESTOR_REP_HEADER)
  state=NEW
  action=D_PIF_INVESTOR_REP_HEADER
  is_available=1
 $ENDSTATE2ACTION

 $STATE2ACTION(NEW.FORM2)
  state=NEW
  action=FORM2
  is_available=1
 $ENDSTATE2ACTION
$ENDDOC