package rpics;

;#--- pod Start -----------------------------------------------------------#
=pod

=head1 NAME

rpics.pm - 共通サブルーチン群

=head1 概要

DBアクセス用サブルーチン、文字変換ルーチン
オーク共通処理用サブルーチン、その他細かいルーチンの集まり

=head1 サブルーチン

=cut
;#--- pod Start -----------------------------------------------------------#

;; our $VERSION = '0.00';
@ISA = qw(DBI);
use strict;
use DBI;
use Jcode;
#Hori require 'rpics.pl';

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

=head2 ＤＢオープン [new]

webdb-param.plの内容でＤＢをオープンして
データベースハンドルを返す

=cut
	my $class = shift;
	my $self ;

####	my $user = 'erp_usr1';
####	my $user = 'test_usr1';

	# rpics.plファイルからID,Passwordを取得する  2008.6.11
#Hori 	my @user_pass = split( /\// , OraConfig::initset() );
#Hori 	my $user = $user_pass[0];
#Hori 	my $pass = $user_pass[1];

	# shell変数セット
#Hori 	local $ENV{ORACLE_HOME} = '/usr/lib/oracle/10.1.0.4/client';
#Hori 	local $ENV{NLS_LANG} = 'japanese_Japan.JA16SJIS'; # EUCだとerrorになる

#Hori 	eval{
#Hori 		$self->{'dbh'} = DBI->connect('dbi:Oracle:rpics',$user,$pass)
#Hori 			or die $DBI::errstr;
#Hori 	};
	bless $self,$class;
	$self->{wh} = '80031';
#Hori	$self->{user} = lc($user);	# ｢RPiCSは---環境です｣の表示条件が小文字

	return  $self ;
}

#------------------------------------------------------------------
sub expand{		# 構成表展開処理
#------------------------------------------------------------------
	my $s = shift;
	my $tmp;
	my %x = (seq =>0,level =>1,days=>0,volume=>1,code=>'',budomari=>100,
		turn=>'正',nengetu=>29991231,
		sql=>qq{select ' ' 機種,
				0 展開員数,
				0 展開手番,
				0 SEQ,
				1 LEV,
				MA.*,
				MB.*
			from M_STRUCTURE MA,M_ITEM MB
			where PrmKey = 'XXXXXXXXXXXXXX'
			and MA.DEL_F = 0
			and 'YYYYMMDD' between MA.APP_STA_D and MA.APP_END_D
			and MB.ITEM_COD = MA.PrmNext
			and MB.DEL_F = 0
			and MB.ALTR_C = 0
			order by MA.PARE_ITEM_COD,MA.CHIL_ITEM_COD,
				MA.APP_STA_D DESC},
		@_);
	$x{sql} = $s->Xeuc2sj($x{sql});
	my ($key,$next);
	if($x{'turn'} ne '逆'){
		$key = 'PARE_ITEM_COD';
		$next = 'CHIL_ITEM_COD';
	}else{	$key = 'CHIL_ITEM_COD';
		$next = 'PARE_ITEM_COD';
	}
	$x{sql} =~ s/PrmKey/$key/g;
	$x{sql} =~ s/PrmNext/$next/g;
	$x{sql} =~ s/XXXXXXXXXXXXXX/$x{'code'}/g;
	$x{sql} =~ s/YYYYMMDD/$x{'nengetu'}/g;

	my $sth = $s->{dbh}->prepare($x{sql});
	$sth->execute();
	$x{'kisyu'} = $x{'code'} if($x{'level'} == 1);
	$x{NAME} = $sth->{NAME};
	my ($ref,$ret);
	while($ref = $sth->fetchrow_hashref()){
		$x{seq} += 1;
		$ref->{SEQ} = $x{seq};
		$ref->{LEV} = $x{level};
		$ref->{'展開員数'} = $x{volume}*
			$ref->{CHIL_P_U}/$ref->{PARE_P_U};
		$ref->{budomari} = $x{budomari} * $ref->{YIELD} / 100;
		$s->{expand}->{rskip} = 0;
		$s->tisel($ref,\%x);
		next if($s->{expand}->{rskip} == 1);
		if(($x{'type'} ne '単レベル' and $x{'type'} ne '仕掛') or
			($x{'type'} eq '仕掛' and
			$ref->{MAKING_C} eq '4 ')){		#仮は展開しない
			$ret = $s->expand('code'=> $ref->{$next},
				'level' =>$x{level}+1,
				'seq' => $x{seq},
				'days' => $x{days} + $ref->{LT},
				'volume' =>  $ref->{'展開員数'},
				'type' => $x{'type'},
				'turn' => $x{'turn'},
				'kisyu' => $x{'kisyu'},
				'budomari' => $ref->{'budomari'},
				'nengetu' => $x{'nengetu'},
				'tehai'=>$x{'tehai2'});
			$x{'seq'} = $ret->{'seq'};
		}
	}
	$sth->finish;
	return \%x;
}
sub tisel{
	my $s = shift;
	my $ref = shift;
	my @item;
	for(keys %{$ref}){
		push @item,"$_ = '$ref->{$_}'";
	}
}
sub csv_split{
	my $s = shift;
	my $text = shift;
	$text =~ s/(?:\x0D\x0A|[\x0D\x0A])?$/,/;
  	return map {/^"(.*)"$/ ? scalar($_ = $1, s/""/"/g, $_) : $_}
                ($text =~ /("[^"]*(?:""[^"]*)*"|[^,]*),/g);
}
sub Xeuc2sj{
	my $s = shift;
	my $text = shift;
	return $s->euc2sj($text);
}
sub euc2sj{
	my $s = shift;
	my $text = shift;
	return jcode($text,"euc")->sjis;
}
sub Xsj2euc{
	my $s = shift;
	my $text = shift;
	return $s->sj2euc($text);
}
sub sj2euc{
	my $s = shift;
	my $text = shift;
	return jcode($text,"sjis")->euc;
}
sub urlencode{
	my $s = shift;
	my $text = shift;
	$text =~ s/([^(\w|=|&)])/'%' . unpack('H2',$1)/eg;
	return $text;
}
sub making_c{
	my $s = shift;
	my $c = shift;
	my %n = ('1 '=>'内作','2 '=>'購入','3 '=>'外作',
			'4 '=>'仮','5 '=>'有償');
	return "$c:$n{$c}";
}
sub lt_u{
	my $s = shift;
	my $r = shift;
	my $ret = $r->{LT};
	if($r->{LT_UNIT} eq '1 '){ $ret .= "日";}
	elsif($r->{LT_UNIT} eq '2 '){ $ret .= "時";}
	elsif($r->{LT_UNIT} eq '3 '){ $ret .= "分";}
	elsif($r->{LT_UNIT} eq '4 '){ $ret .= "秒";}
	else{$ret .= "[$r->{LT_UNIT}]";}
	return $ret;
}
sub making_lt{
	my $s = shift;
	my $item = shift;
	my ($ref);
	my $sth = $s->{dbh}->prepare("select * from M_PROC where DEL_F = 0
				and ITEM_COD = ?");
	$sth->execute($item);
	my ($cnt,$lt) = (0,0);
	while($ref = $sth->fetchrow_hashref()){
		$cnt++;
		$lt += $ref->{'LT'};
	}
	return '' if($cnt == 0);
	return $lt;
}
sub tehai{
	my $s = shift;
	my $mk = shift;
	my $cod = shift;
	my $flag = shift;
	my ($sth,$ref);
	if($mk eq '1 '){
		$sth = $s->{dbh}->prepare(qq{select MA.POS_COD,POS_N_ABBR
					from M_PROC MA,M_POS MB
					where MA.ITEM_COD = '$cod'
					and MA.DEL_F = 0
					and MB.POS_COD = MA.POS_COD
					and MB.DEL_F = 0
					order by ROUTING
				});
	}else{	$sth = $s->{dbh}->prepare("select VENDOR_COD,DEAL_N_ABBR,
					P_O_U_P,T_U_P_C
					from M_ITEM_O MA,M_DEAL MB
					where MA.DEL_F = 0
					and	MA.ITEM_COD = '$cod'
					and	MB.DEAL_COD = MA.VENDOR_COD
					and	MB.DEL_F = 0
					order by ALTR_C,P_O_RATE desc
				");
	}
	$sth->execute();
	$ref = $sth->fetchrow_arrayref();
	$sth->finish;
	return $ref->[0],$ref->[1] if($flag == 1);
	return $s->Xsj2euc($ref->[0]),$s->Xsj2euc($ref->[1]),$ref->[2],
			$ref->[3],$ref->[0] if($flag == 2);
	return "<a href=ra160.cgi?code=$cod target=process>$ref->[0]</a>"
		,$s->Xsj2euc($ref->[1]),$ref->[2],$ref->[3],$ref->[0]
				if($mk eq '1 ');
	return "<a href=ra161.cgi?code=$cod target=price>$ref->[0]</a>"
		,$s->Xsj2euc($ref->[1]),$ref->[2],$ref->[3],$ref->[0];
}
#------------------------------------------------------------------
#  現在庫数カウント
#------------------------------------------------------------------
sub stock{
	my $s = shift;
	my %x = (code=>'',place=>$s->{wh},@_);
	my $sql;
	my ($where1,$where11);
	if($x{place} == $s->{wh}){
		$where1 = "and LOCA_COD is null";
		$where11 = "and 場所コード is null";
	}else{
		$where1 = "and LOCA_COD = '$x{place}'";
		$where11 = "and 場所コード = '$x{place}'";
	}
	
	$sql = <<END;
select ITEM_COD,sum(PASSQ) ZAIKO,'$x{place}' PLACE
	from (select ITEM_COD,PASSQ FROM T_INV_BY_MONTH
			where ITEM_COD = '$x{code}'
			and	trim(TOTALING_MONTH) =
				(select to_char(max(CLOSE_LAST_D),'YYYY/MM')
					from I_CLOSE
					where CLOSE_C in ('3') 
					and DEL_F = '0')
			$where1
			union all
			select 品目コード,受合格数+調整合格数-払合格数
			from 受払明細
			where 品目コード = '$x{code}'
			and 日付 >
				(select to_char(max(CLOSE_LAST_D),'YYYY/MM/DD')
					from I_CLOSE
					where CLOSE_C in ('3') 
					and DEL_F = '0')
			$where11
			)
	group by ITEM_COD
END
	if($x{place} == 100){
		$sql = <<END;
select GOODS_COD ITEM_COD,CEIL(SUM(PASS)) ZAIKO,'$x{place}' PLACE
	FROM (SELECT GOODS_COD,t2.G_STK_Q PASS
			FROM I_GDS_LOT t1,I_G_STK t2
			WHERE WAREHOUSE_COD = '100'
			AND GOODS_COD = '$x{code}'
			AND t2.ACC_CL_DATE =
				(select to_char(max(CLOSE_LAST_D),'YYYY/MM/DD')
					from I_CLOSE
					where CLOSE_C in ('3') 
					and DEL_F = '0')
			AND	t1.GDS_LOT_NO = t2.GDS_LOT_NO
			AND	t1.DEL_F = 0
			AND	t2.DEL_F = 0
			union all
			SELECT 商品コード,受数+調整数-払数 PASS
			FROM 製品受払明細
			WHERE 倉庫コード = '100'
			AND 商品コード = '$x{code}'
			AND	日付 > 
				(select to_char(max(CLOSE_LAST_D),'YYYY/MM/DD')
					from I_CLOSE
					where CLOSE_C in ('3') 
					and DEL_F = '0')
			)
			GROUP BY GOODS_COD
END
		$sql = <<END;
			select 商品コード ITEM_COD,sum(現在庫) ZAIKO,
				倉庫コード PLACE
			from 製品在庫
			where 商品コード = '$x{code}'
			and 倉庫コード = 100
			group by 商品コード,倉庫コード
END
	}
	$sql = $s->Xeuc2sj($sql);
	my $sth = $s->{dbh}->prepare($sql);
	$sth->execute();
	my $ref = $sth->fetchrow_hashref();
	$sth->finish;
	return $ref;
}
sub trim{
	my $s = shift;
	my $text = shift;
	$text =~ s/\s+$//;
	return $text;
}

=head2 日付分解 [ymd_split]

日付らしき文字列を年月日に分解する。

 ($y,$m,$d) = ymd_split("YYYY/MM/DD")
 日付: YYYYMMDD or ZZZ9/Z9/Z9
 $y: 年
 $m: 月
 $d: 日

=cut

#------------------------------------------------------------------------#
sub ymd_split{
#------------------------------------------------------------------------#

=head2 日付分解 [ymd_split]

日付らしき文字列を年、月、日に分解する

 ($y,$m,$d) = ymd_split("YYYY-MM-DD");

=cut

	my $shift = shift;
	my $tmp = shift;
	$tmp =~ /\D*(\d{1,4})\D*(\d{1,2})\D*(\d{1,2})\D*/;
	return $1,$2,$3;
}

sub getwday{
  my $self = shift;
  my($year, $mon, $mday) = @_;

  if ($mon == 1 or $mon == 2) {
    $year--;
    $mon += 12;
  }
  return int($year + int($year / 4) - int($year / 100) + int($year / 400)
      + int((13 * $mon + 8) / 5) + $mday) % 7;
}
#------------------------------------------------------------------
sub today{
#------------------------------------------------------------------

=head2 今日の日付 [today]

=over 2

今日の日付を返す

=item ($yy,$mm,$dd) = today();

=back

=cut

	my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
	return $year + 1900 ,$mon + 1,$mday;
}

sub serch_item{
	my $s = shift;
	my %x = (in=>'sub.word',out=>'form.code',
			submit =>'form.action',@_);
	my $sub = $s->urlencode(q{select '<a href=''close.cgi'' onClick=} .
		q{"window.opener.document.} .
		$x{'out'} . q{.value='''||rtrim(ITEM_COD)||''';} .
#		q{window.opener.document.} . $x{'out'} . q{.select();} .
		q{window.opener.document.} . $x{'submit'}.q{.click();} .
		q{">}.
		q{'||rtrim(ITEM_COD)||'</a>' 部品コード,ITEM_N 部品名称 from }.
		q{M_ITEM where ITEM_COD like '}) .
		qq{' + document.$x{'in'}.value + '%} .
		$s->urlencode(q{' and del_f = 0 order by ITEM_COD});
	my $sub2 = qq{window.open('rlook.cgi?sql=$sub','selectmenu',}.
		qq{'width=400,height=600,scrollbars=yes,statusbar=yes');}.
		qq{document.}.$x{'out'}.qq{.select()};
	return $sub2;
}
#---------------------------------------------------------------#
sub look_up{
#---------------------------------------------------------------#

=head2 look_up

参照テーブルを参照する。

=cut

	my $s =shift;
	my ($sql,$para) = @_;
	my @para;
	for (@$para){
		if(ref($_) eq 'CODE'){
			push @para,&$_;
		}else{ push @para,$_;
		}
	}
	$sql = $s->Xeuc2sj($sql);
	my $sth = $s->{dbh}->prepare(qq{$sql});
	$sth->execute(@para);
	my $ref = $sth->fetchrow_arrayref();
	return $ref;
}

#------------------------------------------------------------------
sub makeStructure{		# 構成展開（SPN_W_STRUCTURE にデータ作成）
#------------------------------------------------------------------
	my $s = shift;
	my $mode = shift;
	my $seq_no = shift;		# 参照渡し
	my $lv = shift;
	my $item = shift;
	my $pare_item = shift;
	my $inzuu = shift;
	my $tekiyobi = shift;

	my ($sql,$sth,$ref,$sth2,$ref2);

	if($pare_item eq ""){
		$pare_item = $item;
	}

	$sql = qq{
		select
			ms.PARE_ITEM_COD,
			ms.CHIL_ITEM_COD,
			decode(ms.PARE_P_U, 0, 0, ms.CHIL_P_U / ms.PARE_P_U * @{[$inzuu]}) as P_U,
			mi.MAKING_C
		from
			M_STRUCTURE ms,
			M_ITEM mi
		where
			ms.PARE_ITEM_COD = '@{[$item]}' and
			ms.APP_STA_D <= TO_DATE('@{[$tekiyobi]}','YYYY/MM/DD') and
			ms.APP_END_D >= TO_DATE('@{[$tekiyobi]}','YYYY/MM/DD') and
			ms.CHIL_ITEM_COD = mi.ITEM_COD and
			mi.ALTR_C = '0' and
			ms.DEL_F = '0' and
			mi.DEL_F = '0'
		order by
			case
				when trim(mi.MAKING_C) = '1' then 1
				when trim(mi.MAKING_C) = '3' then 2
				when trim(mi.MAKING_C) = '4' then 3
				when trim(mi.MAKING_C) = '2' then 4
				when trim(mi.MAKING_C) = '5' then 5
				else 9
			end,
			ms.CHIL_ITEM_COD};
	$sql = $s->euc2sj($sql);
	$sth = $s->{dbh}->prepare($sql);
	$sth->execute();
	while($ref = $sth->fetchrow_hashref()){
		$ref->{'PARE_ITEM_COD'} = $s->sj2euc($ref->{'PARE_ITEM_COD'});
		$ref->{'CHIL_ITEM_COD'} = $s->sj2euc($ref->{'CHIL_ITEM_COD'});

		# $mode=1の場合は仮品目自体は存在しない扱い
		if($mode != 1 or $ref->{'MAKING_C'} != 4){
			$sql = qq{
				insert into
					SPN_W_STRUCTURE
				values (
					@{[$$seq_no]},
					@{[$lv]},
					'@{[$pare_item]}',
					'@{[$ref->{'CHIL_ITEM_COD'}]}',
					'@{[$ref->{'MAKING_C'}]}',
					@{[$ref->{'P_U'}]},
					0)};
			$sql = $s->euc2sj($sql);
			$sth2 = $s->{dbh}->prepare($sql);
			$sth2->execute();
			$sth2->finish();
			$$seq_no++;
		}

		# 下位構成の展開処理（1:内作、3:外注、4:仮）　※再帰呼び出しで処理
		if ($ref->{'MAKING_C'} == 1 or $ref->{'MAKING_C'} == 3 or $ref->{'MAKING_C'} == 4){

			# $mode=1なら仮を$lvの値をアップさせずに展開（仮品目自体は存在しない扱い）
			if($mode == 1 and $ref->{'MAKING_C'} == 4){
				$s->makeStructure($mode, \$$seq_no, $lv, $ref->{'CHIL_ITEM_COD'}, $pare_item, $ref->{'P_U'}, $tekiyobi);
			}else{
				$s->makeStructure($mode, \$$seq_no, $lv+1, $ref->{'CHIL_ITEM_COD'}, "", $ref->{'P_U'}, $tekiyobi);
			}
		}
	}
	$sth->finish();
}

#------------------------------------------------------------------
sub getWorkingDay{		# 稼働日チェック
#------------------------------------------------------------------
	my $s = shift;
	my $ymd = shift;

#	my ($y,$m,$d) = $s->ymd_split($ymd);
#	my $wday = $s->getwday($y,$m,$d);
#
#	my $ret = 0;
#	my ($sql,$sth,$ref);
#	$sql = qq{
#		select
#			*
#		from (
#			select
#				PRIORITY,
#				SHIFT_COD
#			from
#				M_WORKING_D
#			where
#				DEL_F = '0'
#				and PATTERN_COD = '<FREE>'
#				and PATTERN_TYPE = '1'
#				and DATE_FROM <= '@{[$ymd]}'
#				and DATE_TO >= '@{[$ymd]}'
#			union all
#			select
#				PRIORITY,
#				SHIFT_COD
#			from
#				M_WORKING_D
#			where
#				DEL_F = '0'
#				and PATTERN_COD = '<FREE>'
#				and PATTERN_TYPE = '2'
#				and WEEKOFDAY_FROM <= '@{[$wday]}'
#				and WEEKOFDAY_TO >= '@{[$wday]}'
#			order by
#				1 desc)
#		where
#			rownum <= 1};
#	$sql = $s->Xeuc2sj($sql);
#	$sth = $s->{dbh}->prepare($sql);
#	$sth->execute();
#	if($ref = $sth->fetchrow_hashref()){
#		if($s->trim($s->Xsj2euc($ref->{'SHIFT_COD'})) ne "休日"){
#			$ret = 1;	#稼働日
#		}
#	}else{
#		$ret = 1;	#稼働日
#	}
#	$sth->finish();

	# ストアドプロシージャを使う様に変更
	my ($sql,$sth,$ref,$ret);
	$sql = qq{select SPN_F_WORKING_DAY('@{[$ymd]}') as RESULT from dual};
	$sql = $s->Xeuc2sj($sql);
	$sth = $s->{dbh}->prepare($sql);
	$sth->execute();
	if($ref = $sth->fetchrow_hashref()){
		$ret = $ref->{'RESULT'};
	}
	$sth->finish();

	return $ret;
}

#------------------------------------------------------------------
sub get_day{	# 曜日取得
#------------------------------------------------------------------
	my $s = shift;
	my $y = shift;
	my $m = shift;
	my $d = shift;

	if($m <= 2){
		$m += 12;
		$y--;
	}

	my $w = ($y + int($y / 4) - int($y / 100) + int($y / 400) + int((13 * $m + 8) / 5) + $d) % 7;

	return $w;
}

#------------------------------------------------------------------
sub writeOrderProgressWk{		# SPN_W_SEM発注残データをInsert
#------------------------------------------------------------------
	my $s = shift;
	my $in_file = shift;

	my ($sql,$sth);
	my ($in_line,$char,$edt_line,$sw,@arry);
	my $line_no = 0;

	# Oracleの日付書式を一時的に変更
	$sql = qq{alter session set NLS_DATE_LANGUAGE = 'AMERICAN'};
	$sql = $s->Xeuc2sj($sql);
	$sth = $s->{dbh}->prepare($sql);
	$sth->execute();
	$sth->finish();

	while(<$in_file>){
		chomp;
		$in_line = $s->Xsj2euc($_);

		if($line_no == 0){
			if(index($in_line,"Org.,P-Gr.,Material,") >= 0){
				$line_no++;
			}
		}else{
			#ダブルクォーテーションで括られた範囲内のカンマを除外
			$edt_line = "";
			$sw = 1;
			for(my $i=0; $i<length($in_line); $i++){
				$char = substr($in_line,$i,1);
				if($char eq '"'){
					$sw *= -1;
				}elsif($char eq ','){
					if($sw < 0){
						$char = "";
					}
				}
				$edt_line .= $char;
			}

			@arry = $s->csv_split($edt_line);
			if($s->trim($arry[0]) ne ""){
				$arry[2]  = $s->trim($arry[2]);
				$arry[3]  = $s->trim($arry[3]);
				$arry[5]  = $s->trim($arry[5]);
				$arry[6]  = $s->trim($arry[6]);
				$arry[7]  = $s->trim($arry[7]);
				$arry[9]  = $s->trim($arry[9]);
				$arry[10] += 0;
				$arry[12] = $s->trim($arry[12]);
				$arry[12] =~ s/-//g;
				$arry[17] = $s->trim($arry[17]);
				$arry[21] += 0;
				$arry[38] = $s->trim($arry[38]);
				$arry[25] = $s->trim($arry[25]);
				$arry[26] = $s->trim($arry[26]);
				$arry[27] += 0;
				$arry[28] = $s->trim($arry[28]);
				$arry[30] += 0;
				$arry[31] = $s->trim($arry[31]);
				$arry[32] = $s->trim($arry[32]);
				$arry[35] = $s->trim($arry[35]);

				# 注文番号（CustomerPO）の変換（SEM入力ミス、SEM都合、SNE入力ミスなど）
				if($arry[12] eq "107OR00.26088"){
					$arry[12] = "107OR0026088";		# SEM入力ミス
				}elsif($arry[12] eq "107OR00.26089"){
					$arry[12] = "107OR0026089";		# SEM入力ミス
				}elsif($arry[12] eq "1070R0039699"){
					$arry[12] = "107OR0039699";		# SEM入力ミス
				}elsif($arry[12] eq "1070R0039700"){
					$arry[12] = "107OR0039700";		# SEM入力ミス
				}elsif($arry[12] eq "107OR0055255247"){
					$arry[12] = "107OR0055247";		# SEM入力ミス 2007/09/28
				}elsif($arry[12] eq "107OR00553"){
					$arry[12] = "107OR0055326";		# SEM入力ミス 2007/09/28
				}elsif($arry[12] eq "K11012"){
					$arry[12] = "107OR0055378";		# SEM側発注数合算対応(KAMAYAへベンダ変更による) 2007/10/15
				}elsif($arry[12] eq "107OR0034121"){
					$arry[12] = "107OR0037505";		# SNE品目誤り再発注分
				}elsif($arry[12] eq "107OR0034122"){
					$arry[12] = "107OR0037506";		# SNE品目誤り再発注分
				}elsif($arry[12] eq "107OR0034123"){
					$arry[12] = "107OR0037507";		# SNE品目誤り再発注分
				}elsif($arry[12] eq "107OR0052195"){
					$arry[12] = "107OR0060630";		# SNE資材部注文取消ミス分 2007/10/15
				}elsif($arry[12] eq "107OR0130413"){
					$arry[12] = "107ORA030413";
				}elsif($arry[12] eq "107OR0230416"){
					$arry[12] = "107ORA030416";
				}elsif($arry[12] eq "107OR0330536"){
					$arry[12] = "107ORA030536";
				}elsif($arry[12] eq "107OR0430537"){
					$arry[12] = "107ORA030537";
				}elsif($arry[12] eq "107OR0530538"){
					$arry[12] = "107ORA030538";
				}elsif($arry[12] eq "107OR0630575"){
					$arry[12] = "107ORA030575";
				}elsif($arry[12] eq "107OR0730576"){
					$arry[12] = "107ORA030576";
				}elsif($arry[12] eq "107OR0830577"){
					$arry[12] = "107ORA030577";
				}elsif($arry[12] eq "107OR0930578"){
					$arry[12] = "107ORA030578";
				}elsif($arry[12] eq "107OR1030579"){
					$arry[12] = "107ORA030579";
				}elsif($arry[12] eq "107OR1130580"){
					$arry[12] = "107ORA030580";
				}elsif($arry[12] eq "107OR1230595"){
					$arry[12] = "107ORA030595";
				}elsif($arry[12] eq "107OR1330596"){
					$arry[12] = "107ORA030596";
				}elsif($arry[12] eq "107OR1430597"){
					$arry[12] = "107ORA030597";
				}

				if($arry[9] eq "596005" and $arry[10] == 50){
					$arry[12] = "107OR0039595";
				}elsif($arry[9] eq "596005" and $arry[10] == 60){
					$arry[12] = "107OR0040045";
				}elsif($arry[9] eq "596005" and $arry[10] == 70){
					$arry[12] = "107OR0039598";
				}elsif($arry[9] eq "596005" and $arry[10] == 90){
					$arry[12] = "107OR0040046";
				}elsif($arry[9] eq "596005" and $arry[10] == 100){
					$arry[12] = "107OR0039601";
				}elsif($arry[9] eq "596005" and $arry[10] == 110){
					$arry[12] = "107OR0040047";
				}elsif($arry[9] eq "596005" and $arry[10] == 120){
					$arry[12] = "107OR0039604";
				}elsif($arry[9] eq "596005" and $arry[10] == 130){
					$arry[12] = "107OR0040048";

				}elsif($arry[9] eq "623143" and $arry[10] == 10){
					$arry[12] = "107OR0071178";		# 2007/11/30 OP よりSNE注番が間違っている
				}elsif($arry[9] eq "623144" and $arry[10] == 10){
					$arry[12] = "107OR0071179";		# 2007/11/30 OP よりSNE注番が間違っている
				}elsif($arry[9] eq "623145" and $arry[10] == 10){
					$arry[12] = "107OR0071180";		# 2007/11/30 OP よりSNE注番が間違っている
				}elsif($arry[9] eq "623146" and $arry[10] == 10){
					$arry[12] = "107OR0071181";		# 2007/11/30 OP よりSNE注番が間違っている

				}elsif($arry[12] eq "107OR0071171186"){
					$arry[12] = "107OR0071186";
				}elsif($arry[12] eq "107OR0071171187"){
					$arry[12] = "107OR0071187";
				}elsif($arry[12] eq "107OR0071171188"){
					$arry[12] = "107OR0071188";
				}elsif($arry[12] eq "107OR0071171189"){
					$arry[12] = "107OR0071189";

				}elsif($arry[9] eq "635362" and $arry[10] == 10){
					$arry[12] = "108OR0008490";		# 2008/02/04 OP よりSNE注番が間違っている
				}elsif($arry[9] eq "635362" and $arry[10] == 20){
					$arry[12] = "108OR0008491";		# 2008/02/04 OP よりSNE注番が間違っている
				}elsif($arry[9] eq "635362" and $arry[10] == 30){
					$arry[12] = "108OR0008492";		# 2008/02/04 OP よりSNE注番が間違っている
				}elsif($arry[9] eq "635362" and $arry[10] == 40){
					$arry[12] = "108OR0008493";		# 2008/02/04 OP よりSNE注番が間違っている
				}elsif($arry[9] eq "635362" and $arry[10] == 50){
					$arry[12] = "108OR0008494";		# 2008/02/04 OP よりSNE注番が間違っている
				}elsif($arry[9] eq "635362" and $arry[10] == 60){
					$arry[12] = "108OR0008495";		# 2008/02/04 OP よりSNE注番が間違っている
				}elsif($arry[9] eq "635362" and $arry[10] == 70){
					$arry[12] = "108OR0008496";		# 2008/02/04 OP よりSNE注番が間違っている
				}elsif($arry[9] eq "635362" and $arry[10] == 80){
					$arry[12] = "108OR0008497";		# 2008/02/04 OP よりSNE注番が間違っている
				}elsif($arry[9] eq "635362" and $arry[10] == 90){
					$arry[12] = "108OR0008498";		# 2008/02/04 OP よりSNE注番が間違っている

				# 2008.5.28 SEM側監査の関係で古い中残が別の注文に変更された
				}elsif($arry[12] eq "107OR0O70022" or $arry[12] eq "107OR0070022"){
					$arry[12] = "107OR0070022";
					$arry[21] = 3031;
					$arry[9] = "622321";
					$arry[10] = "100";
				}elsif($arry[12] eq "107OR0O70023" or $arry[12] eq "107OR0070023"){
					$arry[12] = "107OR0070023";
					$arry[21] = 3031;
					$arry[9] = "622321";
					$arry[10] = "120";
				}elsif($arry[12] eq "107OR0O75741" or $arry[12] eq "107OR0075741"){
					$arry[12] = "107OR0075741";
					$arry[21] = 9126;
					$arry[9] = "626066";
					$arry[10] = "150";
					$arry[38] = "1-Feb-08";
				}elsif($arry[12] eq "107OR0O75743" or $arry[12] eq "107OR0075743"){
					$arry[12] = "107OR0075743";
					$arry[21] = 9126;
					$arry[9] = "626066";
					$arry[10] = "170";
				}elsif($arry[12] eq "107OR0O78089" or $arry[12] eq "107OR0078089"){
					$arry[12] = "107OR0078089";
					$arry[21] = 8120;
					$arry[9] = "628960";
					$arry[10] = "110";
				}elsif($arry[12] eq "107OR0O02935" or $arry[12] eq "108OR0002935"){
					$arry[12] = "108OR0002935";
					$arry[21] = 77;			# 注文全数
					$arry[9] = "630687";
					$arry[10] = "10";
				}elsif($arry[12] eq "107OR0O02936" or $arry[12] eq "108OR0002936"){
					$arry[12] = "108OR0002936";
					$arry[21] = 77;			# 注文全数
					$arry[9] = "630687";
					$arry[10] = "30";
				}elsif($arry[12] eq "107OR0O02939" or $arry[12] eq "108OR0002939"){
					$arry[12] = "108OR0002939";
					$arry[21] = 150;
					$arry[9] = "630687";
					$arry[10] = "50";
				}elsif($arry[12] eq "107OR0O02940" or $arry[12] eq "108OR0002940"){
					$arry[12] = "108OR0002940";
					$arry[21] = 150;
					$arry[9] = "630687";
					$arry[10] = "70";
				}elsif($arry[12] eq "107OR0O07820" or $arry[12] eq "108OR0007820"){
					$arry[12] = "108OR0007820";
					$arry[21] = 1331;
					$arry[9] = "633932";
					$arry[10] = "110";
				}elsif($arry[12] eq "107OR0O07821" or $arry[12] eq "108OR0007821"){
					$arry[12] = "108OR0007821";
					$arry[21] = 1331;
					$arry[9] = "633932";
					$arry[10] = "130";
				}elsif($arry[12] eq "107OR0O07822" or $arry[12] eq "108OR0007822"){
					$arry[12] = "108OR0007822";
					$arry[21] = 1008;
					$arry[9] = "633932";
					$arry[10] = "150";
				}elsif($arry[12] eq "107OR0O07823" or $arry[12] eq "108OR0007823"){
					$arry[12] = "108OR0007823";
					$arry[21] = 1008;
					$arry[9] = "633932";
					$arry[10] = "170";

				# 2008.5.28 SEM側監査の関係で古い中残が別の注文に変更された ← これの追加分だと思われる
				}elsif($arry[12] eq "108OR0011633" or $arry[12] eq "108OR0O11633"){
					$arry[12] = "108OR0011633";
					$arry[21] = 2250000;
					$arry[9] = "636329";
					$arry[10] = "10";

				# 円建てからドル建てに変更（7/2 再発注）SEM側は先行して切り替え済
				}elsif($arry[12] eq "108OR0030582"){
					$arry[12] = "108OR0036869";
				}elsif($arry[12] eq "108OR0030583"){
					$arry[12] = "108OR0036870";
				}elsif($arry[12] eq "108OR0030584"){
					$arry[12] = "108OR0036871";
				}elsif($arry[12] eq "108OR0030585"){
					$arry[12] = "108OR0036872";
				}elsif($arry[12] eq "108OR0030586"){
					$arry[12] = "108OR0036873";

				# 2008/09/30の発注分のCustomerPOががSEM側で最後の文字が切られている
				}elsif($arry[9] eq "681575" and $arry[10] == 10){
					$arry[12] = "108OR0051459";
				}elsif($arry[9] eq "681575" and $arry[10] == 20){
					$arry[12] = "108OR0051069";
				}elsif($arry[9] eq "681575" and $arry[10] == 30){
					$arry[12] = "108OR0051070";
				}elsif($arry[9] eq "681575" and $arry[10] == 40){
					$arry[12] = "108OR0051071";
				}elsif($arry[9] eq "681575" and $arry[10] == 50){
					$arry[12] = "108OR0051460";
				}elsif($arry[9] eq "681575" and $arry[10] == 60){
					$arry[12] = "108OR0051072";
				}elsif($arry[9] eq "681575" and $arry[10] == 70){
					$arry[12] = "108OR0051073";
				}elsif($arry[9] eq "681575" and $arry[10] == 80){
					$arry[12] = "108OR0051074";
				}elsif($arry[9] eq "681575" and $arry[10] == 90){
					$arry[12] = "108OR0051461";

				}

				$sql = qq{
					insert into
						SPN_W_SEM発注残データ
					values(
						'@{[$arry[2]]}',
						'@{[$arry[3]]}',
						'@{[$arry[5]]}',
						'@{[$arry[6]]}',
						'@{[$arry[7]]}',
						'@{[$arry[11]]}',
						'@{[$arry[9]]}',
						@{[$arry[10]]},
						'@{[$arry[12]]}',
						to_date('@{[$arry[17]]}','DD-MON-YY'),
						@{[$arry[21]]},
						to_date('@{[$arry[38]]}','DD-MON-YY'),
						'@{[$arry[25]]}',
						to_date('@{[$arry[26]]}','DD-MON-YY'),
						@{[$arry[27]]},
						'@{[$arry[28]]}',
						@{[$arry[30]]},
						to_date('@{[$arry[31]]}','DD-MON-YY'),
						'@{[$arry[32]]}',
						to_date('@{[$arry[35]]}','DD-MON-YY'))};
				$sql = $s->Xeuc2sj($sql);
				$sth = $s->{dbh}->prepare($sql);
				$sth->execute();
				$sth->finish();
			}
		}
	}

	return 0;
}

#------------------------------------------------------------------
sub writeInvoiceDetailWk{		# SPN_W_SEM出庫データをInsert
#------------------------------------------------------------------
	my $s = shift;
	my $in_file = shift;

	my ($sql,$sth);
	my ($in_line,@arry,$loca_cod,$err_f);
	my $line_no = 0;

	while(<$in_file>){
		chomp;
		$in_line = $s->Xsj2euc($_);

		if($line_no == 0){
			if(index($in_line,"Clien,INVNO,Sales,Customer,BOKNO,VES_2") >= 0){
				$line_no++;
			}
		}else{
			@arry = $s->csv_split($in_line);
			if($s->trim($arry[1]) ne ""){
				$arry[1] = $s->trim($arry[1]);
				$arry[3] = $s->trim($arry[3]);
				$arry[4] = $s->trim($arry[4]);
				$arry[5] = $s->trim($arry[5]);

				# 出庫先を判断する（基本はBOKNO列で判断）
				if(index($arry[4],"2NT") == 0){
					$loca_cod = "34352";		# 雅新
				}elsif(index($arry[4],"SMT") == 0){
					$loca_cod = "31354";		# 新進科技
				}elsif(index($arry[4],"FMOS") == 0){
					$loca_cod = "30031";		# FINEMOST 支給部品 (SEMへ出庫)
				}elsif(index($arry[4],"2NL") == 0){
					$loca_cod = " ";			# FINEMOST ユニット（出庫不要）
				}elsif(index($arry[5],"SMT") >= 0){
					$loca_cod = "31354";		# 新進科技２
				}else{
					$loca_cod = " ";
					$err_f = "2";
				}

				# BOKNO列とVES_2列の不整合をエラーファイルに出力する
				if((index($arry[4],"SMT") == 0 and index($arry[5],"SMT") < 0) or
					(index($arry[4],"SMT") < 0 and index($arry[5],"SMT") >= 0)){
					$err_f = "1";
				}else{
					$err_f = "0";
				}

				$sql = qq{
					insert into
						SPN_W_SEM出庫データ
					values(
						'@{[$arry[1]]}',
						'@{[$arry[3]]}',
						'@{[$loca_cod]}',
						'@{[$err_f]}')};
				$sql = $s->Xeuc2sj($sql);
				$sth = $s->{dbh}->prepare($sql);
				$sth->execute();
				$sth->finish();
			}
		}
	}

	return 0;
}

#------------------------------------------------------------------
sub getSlipNo{		# 伝票番号の連番取得
#------------------------------------------------------------------
	my $s = shift;
	my $inTransaction = shift;
	my $slip_cod = shift;
	my $slip_ymd = shift;

	my ($y,$m,$d);
	if($slip_ymd eq ""){
		($y,$m,$d) = $s->today();
	}else{
		($y,$m,$d) = $s->ymd_split($slip_ymd);
	}

	if($inTransaction == 0){
		$s->{'dbh'}->{AutoCommit} = 0;
		$s->{'dbh'}->{RaiseError} = 1;
	}

	# レコードロックをかける（他のセッションからの更新をwaitさせる）
	my ($sql,$sth,$ref);
	my $seq_no = 1;
	my $ins_sw = 1;
	$sql = qq{
		select
			SEQ_NO
		from
			SPN_伝票番号管理
		where
			SLIP_COD = '@{[$slip_cod]}' and
			SLIP_YEAR = @{[$y]}
		for update};
	$sql = $s->Xeuc2sj($sql);
	$sth = $s->{dbh}->prepare($sql);
	$sth->execute();
	if($ref = $sth->fetchrow_hashref()){
		$seq_no = $ref->{'SEQ_NO'} + 1;
		$ins_sw = 0;
	}
	$sth->finish();

	# 伝票番号を更新
	if($ins_sw == 1){
		$sql = qq{
			insert into
				SPN_伝票番号管理
			values(
				'@{[$slip_cod]}',
				@{[$y]},
				@{[$seq_no]})};
		$sql = $s->Xeuc2sj($sql);
		$sth = $s->{dbh}->prepare($sql);
		$sth->execute();
		$sth->finish();
	}else{
		$sql = qq{
			update
				SPN_伝票番号管理
			set
				SEQ_NO = @{[$seq_no]}
			where
				SLIP_COD = '@{[$slip_cod]}' and
				SLIP_YEAR = @{[$y]}};
		$sql = $s->Xeuc2sj($sql);
		$sth = $s->{dbh}->prepare($sql);
		$sth->execute();
		$sth->finish();
	}

	if($inTransaction == 0){
		$s->{'dbh'}->commit();
	}

	my $y1 = $y - int($y / 10) * 10;
	my $slip_no = sprintf("%1d%-2s%06d",$y1,$slip_cod,$seq_no);

	return $slip_no;
}
1;
__END__

=head1 AUTHOR INFORMATION

 ;######################################################################
 ;#
 ;# rpics.pm: R-PiCS ACCESS Sub Routine.
 ;#
 ;# Copyright (c) 2005 Masashi Hori <hori@japannet.co.jp>
 ;# JapanNet. All Rights Reserved.
 ;#
 ;#
 ;######################################################################

=cut