package	mmtora;

=pod

=head1 NAME

mmtora.pm - マスターメンテツールモジュール（Oracle用）

=cut


use CGI qw/:standard :html3 -no_xhtml/;
use webdb;
use myconstant;
#mmtora
use rpicseuc;
use mmt;
@ISA = qw(mmt);
#mmtora

#---------------------------------------------------------------#
sub new{
#---------------------------------------------------------------#

=head2 new コンストラクタ

$mmt = new mmt({'table'=>'テーブル名称'});

メンテナンスしたいテーブル名をtableにセットして呼び出す。

=cut

	my $class = shift;
	my $self = {};
	$self->{'m'} = shift;
	bless $self,$class;
	$self->{'dbh'} = new webdb;
	$self->{'in'} = new CGI;
#mmtora
	$self->{dbh}->secure($self->{in});
	$self->{rpicseuc} = new rpicseuc;
	$self->{dbh}->{dbh} = $self->{rpicseuc}->{dbh};
#mmtora
	$self->{'cons'} = new myconstant;
	$self->{'m'}->{table} ||= $self->{in}->param('table');
	$self->{m}->{-explain} ||= $self->{m}->{table};
	$self->desc_table();
	$self->initialize();
	return $self;
}
#---------------------------------------------------------------#
sub desc_param{
#---------------------------------------------------------------#

	my $s = shift;
	my $m = shift;
#mmtora
	my $table = $s->{$m}->{table};
	return qq{
		SELECT 
		 A.column_name                                          as "Field"
		,lower(A.data_type)
		 || DECODE(A.data_type, 'NUMBER','(' || TO_CHAR(A.data_precision) || ',' || TO_CHAR(A.data_scale) || ')'
					,'DATE', NULL
					,'(' ||  A.data_length || ')')
									as "Type"
		,DECODE(A.nullable,     'Y', 'YES', 'NO')               as "Null"
		,DECODE(B.column_name, NULL, NULL, 'PRI')               as "Key"
		,A.data_default                                         as "Default"
		FROM  user_tab_columns A 
		     ,(SELECT 
		              B1.table_name 
		             ,B1.column_name 
		             ,B1.constraint_name 
		       FROM  user_cons_columns B1 
		            ,user_constraints  B2 
		       WHERE B1.table_name      = '$table'
		        AND  B1.constraint_name = B2.constraint_name 
		        AND  B2.constraint_type = 'P' 
		       ORDER BY B1.table_name, B1.position 
		      ) B 
		     ,user_col_comments C 
		WHERE A.table_name = '$table'
		 AND  '$table'            = B.table_name(+) 
		 AND  A.table_name        = B.table_name(+) 
		 AND  A.column_name       = B.column_name(+) 
		 AND  '$table'            = C.table_name(+) 
		 AND  A.table_name        = C.table_name(+) 
		 AND  A.column_name       = C.column_name(+) 
		ORDER BY A.column_id 
	};
#mmtora
}
#---------------------------------------------------------------#
sub size{
#---------------------------------------------------------------#

=head2 size 項目桁数取得

フィールドタイプより桁数を計算する

=over 2

=item C<$ret = $mmt->size($ref->{Type})>

 $ref->{Type}: データベースより取得したフィールドのタイプ
 $ret: フィールドタイプより計算した項目の桁数を返す

=back

=cut

	my $s = shift;
	my $type = shift;
	my ($t1,$t2,$t3);
	$type =~ /\w+\((\d+)\,{0,1}(\d{0,1})\)/;
	$t1 = $1;
	$t1++ if($2);
	$t1+= $2;
	$t1++ if($s->type_numeric2($type));
#mmtora
	if($type =~ /date/){
		$t1 = 20;
	}
#mmtora
	return $t1;
}
#---------------------------------------------------------------#
sub err_str{
#---------------------------------------------------------------#
	my $s = shift;
#mmtora
	my $e = shift;
	return $e;
#mmtora
}

#---------------------------------------------------------------#
sub type_numeric2{
#---------------------------------------------------------------#

=head2 type_numeric2

フィールドタイプが数値項目かチェックする

=cut

	my $s = shift;
	my $type = shift;
#mmtora
	return($type =~ /(number|binary_float|binary_double)/);
#mmtora
}
#---------------------------------------------------------------#
sub data_serch{
#---------------------------------------------------------------#

=head2 data_serch データ検索処理

検索ボタン投下後の処理でテーブル内容を一覧表示する。

=cut

	my $s = shift;
	my ($i,$url,@param);
	my $next = $s->{in}->param(next_line) || 0;
	my $max = defined $s->{m}->{maxline} || 2000;
	my $get = $max + 1;
	my $order ;
	my $serch_opt ;
	my $OpParam;
	if ($s->{in}->param('serch_opt') ne ''){
		$serch_opt = " and ".$s->{in}->param('serch_opt');
	}
	($serch_opt,@OpParam) = $s->option_add($serch_opt);
	$order = join(",",@{$s->{m}->{key}});
	$order = "order by $order" if($order);
	$order = $s->{in}->param('OrderBy') if($s->{in}->param('OrderBy'));
	$s->key_display();
	print qq{<table border=1 class="sortable" id="foo">\n};
	print "<thead>";
	$s->head_line_disp;
	print "</thead><tbody>";
#mmtora
	$s->{sql} = <<END;
select *
from
(
	select m.* from $s->{m}->{table} m where  
	m.@{[$s->{in}->param(serch_item)]} @{[$s->{in}->param(serch_op)]} ?
	$serch_opt
	$order
) m
where rownum between $next+1 and $get-1
END
	@param = ($s->{in}->param("serch_value"));
	$param[0] .= '%' if $s->{in}->param("serch_op") eq "like";

	if($s->{in}->param(serch_op) eq 'between'){
		$s->{sql} = <<END;
select *
from
(
	select m.* from $s->{m}->{table} m where
	m.@{[$s->{in}->param(serch_item)]} between ? and ?
	$serch_opt
	$order
) m
where rownum between $next+1 and $get-1
END
#mmtora
		@param = split ",",$s->{in}->param("serch_value");
	}
	push @param,@OpParam;
	$s->serch_param_set(\@param);
#	print "[$s->{sql}] @param";
	my $sth = $s->{dbh}->{dbh}->prepare($s->{sql});
	$sth->execute(@param);
	my $count = 0;
	while(my $ref = $sth->fetchrow_hashref()){
		$count++;
		last if $count > $max; 
		print "<tr>";
		$url=$s->{in}->url() . "?table=" .
			$s->{dbh}->urlencode($s->{in}->param('table'));
		$url .= "&action=get";
		$i=0;
		for(@{$s->{m}->{key}}){
			$url .= "&key$i=@{[$s->{dbh}->urlencode($ref->{$_})]}";
			$i++;
		}
		$s->{url} = $url;
		$s->line_disp($ref);
		print "</tr>\n";
	}
	print "</tbody>";
	print "</table>\n";
	$sth->finish();
	if($count >$max){
		$s->{in}->param('next_line',$next+$max);
		print "<A href=" . $s->{in}->self_url() . ">Next</a> \n";
		$s->{in}->param('next_line',$next-$max);
		print "<A href=" . $s->{in}->self_url() . ">back</a> \n";
	}else{	$s->{in}->param('next_line',0);
		print "<A href=" . $s->{in}->self_url() . ">Top</a> \n";
		$s->{in}->param('next_line',$next-$max);
		print "<A href=" . $s->{in}->self_url() . ">back</a> \n";
	}
	$s->{in}->param('next_line',0);
	$s->{in}->param('action',$s->{msg}->{action}->[7]);
	print "<a href=".$s->{in}->self_url()." target=_blank>excel</a>\n";
}
#---------------------------------------------------------------#
sub excel_put{
#---------------------------------------------------------------#

=head2 excel_put excel用データ出力

excelボタン投下後の処理でテーブル内容をTAB区切りで出力する。

=cut

	my $s = shift;
	my ($i,$url,@param);
#
#	print "Window-Target: _blank\n";
	print "Content-type: application/vnd.ms-excel\n";
	print "ContentDisposition: filename=output.xls\n\n";
#
	my $order ;
	my $serch_opt ;
	my @OpParam;
	if ($s->{in}->param('serch_opt') ne ''){
		$serch_opt = " and ".$s->{in}->param('serch_opt');
	}
	($serch_opt,@OpParam) = $s->option_add($serch_opt);
	$order = join(",",@{$s->{m}->{key}});
	$order = "order by $order" if($order);
	$order = $s->{in}->param('OrderBy') if($s->{in}->param('OrderBy'));
	$s->head_line_disp1;
#mmtora
	$s->{sql} = <<END;
select m.* from $s->{m}->{table} m where  
m.@{[$s->{in}->param(serch_item)]} @{[$s->{in}->param(serch_op)]} ?
$serch_opt
$order
END
	@param = ($s->{in}->param("serch_value"));
	$param[0] .= '%' if $s->{in}->param("serch_op") eq "like";

	if($s->{in}->param(serch_op) eq 'between'){
		$s->{sql} = <<END;
select m.* from $s->{m}->{table} m where
m.@{[$s->{in}->param(serch_item)]} between ? and ?
$serch_opt
$order
END
#mmtora
		@param = split ",",$s->{in}->param("serch_value");
	}
	push @param,@OpParam;
	$s->serch_param_set(\@param);
#	print "[$s->{sql}] @param";
	my $sth = $s->{dbh}->{dbh}->prepare($s->{sql});
	$sth->execute(@param);
	my $count = 0;
	while(my $ref = $sth->fetchrow_hashref()){
		$s->line_disp2($ref);
	}
	$sth->finish();
}
1;

=head1 AUTHOR INFORMATION

 ;######################################################################
 ;#
 ;# mmt.pm: Master Maintenance Tool perl module.
 ;#
 ;# Copyright (c) 2007 Chihiro Suzuki <suzu@japannet.co.jp>
 ;# JapanNet. All Rights Reserved.
 ;#
 ;#
 ;######################################################################

=cut