#============================================================
# MySQLApplication
#============================================================
package MySQLApplication;
use MyApplication;
@ISA = qw(MyApplication);

use strict;
use SQLDB;

#==========================================
# 大域変数
#==========================================
my $DefaultSendmail = '/usr/sbin/sendmail';
my $SourceCharCode  = Jcode::getcode('月火水木金土日　');

#============================================================
#　静的関数
#============================================================

#============================================================
#　変数等取得関数
#============================================================
sub DBMName                  { return shift->{DBMName}; }
sub SQLServer                { return shift->{DBServer}; }
sub SQLUser                  { return shift->{DBUser}; }
sub SQLPassword              { return shift->{DBPassword}; }
sub DBName                   { return shift->{DBName}; }
sub DBTableName              { return shift->{DBTableName}; }
sub DB                       { return shift->{DB}; }
sub SetDB                    { my($this,$DB) = @_; return $this->{DB} = $DB; }

#============================================================
#　コンストラクタ、デストラクタ
#============================================================
BEGIN { }

sub new
{
	my ($module, $app) = @_;
	my $this = {};
	bless $this;
#	$this->SUPER::new(@_);
	return $this;
}

sub DESTROY
{
	my $this = shift;
#	$this->SUPER::DESTROY(@_);
}

#============================================================
#　継承クラスで定義しなおす関数
#============================================================

#============================================================
#　一般関数
#============================================================
sub MergeDBVars
{
	my ($this, $pParams) = @_;
	my $DB = $this->DB();
	return if($pParams->{DontReadDBVars});
	return if(!$DB);
	return if($this->{nDidMergeDBVars} > 0 and $pParams->{DoNotAllowDBVariables});
	return if(!$DB->IsExistTable($pParams->{DBName}, $pParams->{DBVariablesTableName}));
#$this->H3("merge");
	$this->{nDidMergeDBVars}++;
	$this->DB()->Search($pParams->{DBVariablesTableName}, "order by sn", "");
	my $nHit = $this->DB()->nHit();
#	while(1) {
	for(my $i = 0 ; $i < $nHit ; $i++) {
		my %Hash = $this->DB()->GetNextHit();
		next if(!defined $Hash{VarName} or $Hash{VarName} eq '');
		
		if($Hash{VarName} =~ /^\$/) {
#$this->print(" eval: $Hash{VarName} = $Hash{Value};\n");
			eval("$Hash{VarName} = $Hash{Value};");
			if($Hash{VarName} !~ /\[/ and $Hash{VarName} !~ /\{/) {
				$Hash{VarName} =~ s/^\$//;
				$pParams->{$Hash{VarName}} = $Hash{Value};
			}
		}
		else {
#$this->print(" set: $Hash{VarName} = $Hash{Value}\n");
			$pParams->{$Hash{VarName}} = $Hash{Value};# if($pParams->{$Hash{VarName}} ne '');
		}
	}
#バグ？: evalの後で変数が更新されないので、強制的に参照させるための余計なコードを入れる
#$SendNotificationForPrivilegedAccess;
#	$this->UpdateParams($pParams);
}

sub SaveDBVars
{
	my ($App, $pParams, $pVars) = @_;

	my $ret = 1;
	my $db = $App->DuplicateDB();
	foreach my $key (@$pVars) {
#$App->print("Check $key [$pParams->{$key}]\n");
		next if(!defined $pParams->{$key});
		next if(!$App->CanDBVariable($key));

		my $val = $pParams->{$key};
		if($key =~ /^d_(.*)$/) {
			$key = "\$$1";
		}
#$App->print("update $key: $val\n");
		my $ret0 = $db->UpdateDataByAutoJudge($pParams->{DBVariablesTableName},
					"VarName='$key'", VarName => $key, Value => $val);
		$ret = 0 if(!$ret0);
	}
	return $ret;
}

sub SaveDBVar
{
	my ($this, $DBVarName, $val) = @_;
	return $this->SaveDBTemplateVar($DBVarName, $val);
}

sub SaveDBTemplateVar
{
	my ($this, $DBVarName, $val) = @_;
	my $pParams = $this->pParams();
	$pParams->{$DBVarName} = Utils::URLDecode($val);
	if($this->SaveDBVars($pParams, [$DBVarName])) {
		$this->PrintRawHTML("<i>  [$val] has been saved to DBVar '$DBVarName'.</i><br>\n");
		return 1;
	}
	else {
		$this->PrintRawHTML("<font color='red'>  Warning: Save [$val] to DBVar '$DBVarName' failed.</font><br>\n");
	}
	return 0;
}

sub GetSearchedDBHash
{
	my ($App, $DBTableName, $condition) = @_;

#	$App->DB()->Search($DBTableName, $condition, "");
#	my %Hash = $App->DB()->GetNextHit();
	my $db = $App->DuplicateDB();
	$db->Search($DBTableName, $condition, "");
	my %Hash = $db->GetNextHit();
	return \%Hash;
}

sub GetSearchedDBValue
{
	my ($App, $DBTableName, $condition, $Field) = @_;

#	$App->DB()->Search($DBTableName, $condition, "$Field");
#	my %Hash = $App->DB()->GetNextHit();
	my $db = $App->DuplicateDB();
	$db->Search($DBTableName, $condition, "$Field");
	my %Hash = $db->GetNextHit();
	return $Hash{$Field};
}

sub GetUniqueElements
{
	my ($App, $DBTableName, $condition, $Field, $separator, $NotUseDISTINCT) = @_;
	$NotUseDISTINCT = 0 if(!defined $NotUseDISTINCT);
	if(!defined $separator) {
		$condition = '';
		($Field, $separator) = ($condition, $Field);
	}
	if($condition ne '') {
		$condition = "where $condition";
	}

	my $db = $App->DuplicateDB();
	if($NotUseDISTINCT) {
		$db->Search($DBTableName, $condition, "$Field");
	}
	else {
		$db->Search($DBTableName, $condition, "DISTINCT $Field");
	}
	my $nHit = $db->nHit();
	my %List;
	for(my $i = 0 ; $i < $nHit ; $i++) {
		my %Hash = $db->GetNextHit();
		my @a = ($Hash{$Field});
#$App->print("a[$Field]:$Hash{$Field}\n");
		if($separator ne '') {
			@a = Utils::Split($separator, $Hash{$Field});
		}
		for(my $i = 0 ; $i < @a ; $i++) {
			$List{$a[$i]}++;
		}
	}
	return sort keys %List;
}


#==========================================
# DB関係
#==========================================
sub GetDBConfig
{
	my ($this, $idx, $pDBConfigArray) = @_;
	my $i = $idx * 7;
	return ($pDBConfigArray->[$i  ], $pDBConfigArray->[$i+1], $pDBConfigArray->[$i+2], $pDBConfigArray->[$i+3],
			$pDBConfigArray->[$i+4], $pDBConfigArray->[$i+5], $pDBConfigArray->[$i+6]);
}

sub FindDBConfig
{
	my ($this, $conf) = @_;
	for(my $i = 0 ; ; $i++) {
		my ($lDBConfigName, $lDBMName, $lDBServer, $lDBUser, $lDBPassword, $lDBName, $pTableNames)
				= $this->GetDBConfig($i);
		return undef if(!$lDBConfigName);
		return ($lDBConfigName, $lDBMName, $lDBServer, $lDBUser, $lDBPassword, $lDBName, $pTableNames)
				if($lDBConfigName eq $conf);
	}
	return undef;
}

sub OpenDB
{
	my ($this, $config, $pDBConfigArray) = @_;

	my $DB;
	my ($DBConfigName, $DBMName, $DBServer, $DBUser, $DBPassword, $DBName, $pTableNames);
#$this->print("config: $config\n");
	if($config ne '' and $config !~ /^auto$/i) {
		for(my $i = 0 ; ; $i += 7) {
			$DBConfigName      = $pDBConfigArray->[$i+0];
			$DBMName           = $pDBConfigArray->[$i+1];
#$this->print("DB[$i]: $DBConfigName, $DBMName\n");
			if(!$DBConfigName or !$DBMName) {
				undef $DB;
				return (undef);
			}
			next unless($pDBConfigArray->[$i] =~ /^$config$/i);
			$DBServer    = $pDBConfigArray->[$i+2];
			$DBUser      = $pDBConfigArray->[$i+3];
			$DBPassword  = $pDBConfigArray->[$i+4];
			$DBName      = $pDBConfigArray->[$i+5];
			$pTableNames = $pDBConfigArray->[$i+6];
			$DB = new SQLDB($DBMName);
			$DB->SetApplication($this);
			$DB->SetDBCharCode($this->SQLCharCode());
			$DB->SetSourceCharCode($this->WebCharCode());
			$DB->SetDebug($this->Debug());
#$this->print("Open($DBServer, $DBUser, $DBPassword, $DBName, undef, 0, 0)\n");
			$DB->Open($DBServer, $DBUser, $DBPassword, $DBName, undef, 0, 0);
			if(!defined $DB or !$DB->dbh()) {
$this->H3("Error: Can not open for $config with [$DBServer:$DBUser:$DBName].\n");
				return (undef);
			}
			last;
		}
	}
	if(!defined $DB or !$DB->dbh()) {
		for(my $i = 0 ; ; $i += 7) {
			$DBConfigName      = $pDBConfigArray->[$i+0];
			$DBMName           = $pDBConfigArray->[$i+1];
#$this->print("DB[$i]: $DBConfigName, $DBMName\n");
			if(!$DBConfigName or !$DBMName) {
				undef $DB;
				return (undef);
			}
			$DBServer    = $pDBConfigArray->[$i+2];
			$DBUser      = $pDBConfigArray->[$i+3];
			$DBPassword  = $pDBConfigArray->[$i+4];
			$DBName      = $pDBConfigArray->[$i+5];
			$pTableNames = $pDBConfigArray->[$i+6];
#$this->print("DB[$i]: $DBConfigName, $DBMName: $DBServer, $DBUser, $DBPassword, $DBName, ", join(':', @$pTableNames), "\n");
			$DB = new SQLDB($DBMName);
			$DB->SetApplication($this);
			$DB->SetDBCharCode($this->SQLCharCode());
			$DB->SetSourceCharCode($this->WebCharCode());
			$DB->SetDebug($this->Debug());
			$DB->Open($DBServer, $DBUser, $DBPassword, $DBName, undef, 0, 0);
			last if($DB->dbh());
		}
	}
	return (undef) if(!$DB or !$DB->dbh());

	return ($DB, $DBConfigName, $DBMName, $DBServer, $DBUser, $DBPassword, $DBName, @$pTableNames);
}

sub DuplicateDB
{
	my ($this) = @_;
	my $pParams = $this->{pParams};
	return new SQLDB($this->{DBMName}, $pParams->{SQLCharCode}, 
					$this->{DBServer}, $this->{DBUser}, $this->{DBPassword}, $this->{DBName});
}

sub nHit
{
	my ($App, $db) = @_;
	$db = $App->DB() if(!defined $db);
	return 0 if(!$db);

	return $db->nHit();
}

sub GetNextHit
{
	my ($App, $db) = @_;
	$db = $App->DB() if(!defined $db);
	return () if(!$db);

	return $db->GetNextHit();
}

sub ConstructSQL1
{
	my ($App, $pParams, $key0, $target0, $pTargetHash) = @_;

Jcode::convert(\$key0, $pParams->{SQLCharCode}) if($pParams->{SQLCharCode} ne '');
	my $sql0 = '';
	if($key0 ne '') {
		my $fword0 = "\'%$key0%\'";
		if($pTargetHash->{$target0}) {
			my $pTarget = $pTargetHash->{$target0};
			my $s = '';
			for(my $i = 0 ; $i < @$pTarget ; $i++) {
				$s .= ($s eq '')? "$pTarget->[$i] like $fword0" : " or $pTarget->[$i] like $fword0";
			}
			$sql0 = "($s)";
		}
		else {
			$sql0 .= "($target0 like $fword0)";
		}
	}
	return $sql0;
}

sub ConstructSQLAll
{
	my ($App, $pParams, $pHash, $OtherCondition, $pTargetHash, $pOrderArray) = (@_);

	my $sql  = "";
	my $sql1 = $App->ConstructSQL1($pParams, $pHash->{SearchKeyword1}, $pHash->{SearchTarget1}, $pTargetHash);
 	if($pHash->{SearchKeyword1} ne '' and $pHash->{SearchKeyword2} ne '') {
		my $sql2 = $App->ConstructSQL1($pParams, $pHash->{SearchKeyword2}, $pHash->{SearchTarget2}, $pTargetHash);
		$sql .= "($sql1 $pHash->{SearchOperator1} $sql2)";
	}
	else {
		$sql .= $sql1;
	}
	
	if($OtherCondition ne '') {
		if($sql eq '') {
			$sql = "($OtherCondition)";
		}
		else {
			$sql .= " and ($OtherCondition)";
		}
	}

	if($pHash->{SearchOrderTarget} eq '') {
		if(lc $pHash->{SearchOrderDirection} eq 'ascend') {
			$sql .= " order by "; #$pHash->{SearchOrderTarget}";
			for(my $i = 0 ; $i < @$pOrderArray ; $i++) {
				$sql .= ($i == 0)? "$pOrderArray->[$i]" : ",$pOrderArray->[$i]";
			}
		}
		else {
			$sql .= " order by "; #$pHash->{SearchOrderTarget} desc";
			for(my $i = 0 ; $i < @$pOrderArray ; $i++) {
				$sql .= ($i == 0)? "$pOrderArray->[$i] desc" : ",$pOrderArray->[$i] desc";
			}
		}
	}
	else {
		if(lc $pHash->{SearchOrderDirection} eq 'ascend') {
			$sql .= " order by $pHash->{SearchOrderTarget}";
		}
		else {
			$sql .= " order by $pHash->{SearchOrderTarget} desc";
		}
	}
	$sql =~ s/[\r\n\s]+/ /sg;
#$App->H3("<br>[$sql]<br>\n");
	return $sql;
}

#==========================================
# パラメータハッシュ
#==========================================
sub CanDBVariable
{
	my ($this, $VarName, $pNoDBVarNames) = @_;
#$this->print("aaa[$VarName][$pNoDBVarNames]\n");
	for(my $i = 0 ; $i < @$pNoDBVarNames ; $i++) {
#$this->print("$i: [$VarName] [@$pNoDBVarNames->[$i]]\n");
		return 0 if($VarName =~ /$pNoDBVarNames->[$i]/);
	}
	return 1;
}

sub SearchDBVariables
{
	my ($App, $condition, $vars, $DBVariablesTableName) = @_;
	my $DB = $App->DuplicateDB();
	return 0 if(!$DB);

	my $pParams = $App->pParams();
	return if(!$DB->IsExistTable($pParams->{DBName}, $DBVariablesTableName));

	my $pParams = $App->pParams();
	my $ret =  $DB->Search($DBVariablesTableName, $condition, $vars);
	return -1 if($ret <= 0);
	return $DB->rows();
}

sub GetDBVar
{
	my ($this, $pParams, $VarName, $DefVar, $DBName, $DBVariablesTableName) = @_;
#$this->print("pParams1[$pParams][$DBName]\n");
	if($pParams !~ /^HASH/) {
#	if(ref $pParams !~ /^HASH/) {
		($this, $VarName, $DefVar, $DBName, $DBVariablesTableName) = @_;
		$pParams = $this->pParams();
	}
	return '' if($VarName eq '');

#$this->print("pParams2[$pParams][$DBName]\n");
	$DBName = $pParams->{DBName} if($DBName eq '');
#$this->print("pParams3[$pParams][$DBName]\n");
	$DBVariablesTableName = $pParams->{DBVariablesTableName} if($DBVariablesTableName eq '');

	return if($this->{nDidMergeDBVars} > 0 and $pParams->{DoNotAllowDBVariables});
	my $DB = $this->DuplicateDB();
	return if(!$DB);
	return if(!$DB->IsExistTable($DBName, $DBVariablesTableName));

	$DB->Search($DBVariablesTableName, "VarName='$VarName' order by sn", "");
	my %Hash = $DB->GetNextHit();
#$this->print("Var[$VarName][$Hash{$VarName}]\n");
	return $Hash{Value} if(defined $Hash{Value});
	return $DefVar;
}

sub GetDBListVar
{
	my ($App, $varname, $separator) = @_;
	$separator = "[\\r\\n]+" if(!defined $separator);

	my $value = $App->GetDBVar($varname);
	return Utils::Split($separator, $value);
}

sub SearchUsers
{
	my ($App, $condition, $vars, $DBUsersTableName) = @_;
	
	my $pParams = $App->pParams();
	my $ret =  $App->DB()->Search($DBUsersTableName, $condition, $vars);
#	my $db = $App->DuplicateDB();
#	my $ret =  $Adb->Search($pParams->{DBUsersTableName}, $condition, $vars);
	return -1 if($ret <= 0);
	return $App->DB()->rows();
}

1;