William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

CGI

package cgi_test;

# William Perl Demo for CGI+DBI+mod_perl+Apache Session.
use CONFIG;
use Exporter;
use HTMLInterface;
@ISA = qw(Exporter HTMLInterface);
@EXPORT_OK = qw( &getAccessSql &getConditionSql );

use strict;
use warnings;

our ($session_id, $com, $sql, $userinfo_id);

#==============================
# input template file.
#==============================
sub new {
	my ($proto, $api_obj, $sessionid, $userinfoid) = @_;
	my $class = ref($proto) || $proto;

	# create HTML Template for display.
	my $self = HTMLInterface->new(
		filename => CGI_LIST->{odq}[0], path => CGI_LIST->{path} );

	# load COMMON's cgi and dbi handlers into this module.
	$com = $self->{com} = $api_obj || die;

	$session_id = $sessionid;
	$userinfo_id = $userinfoid;

	# initialize HTML::Template display elements.
	$self->{'display'} = {
		HEADER_NAME		=> CGI_LIST->{odq}[1],
		HEADER_TITLE	=> CGI_LIST->{odq}[2],
		SESSION_ID		=> $session_id,
		Need_JavaScript	=> 1,
		no_odqHome		=> 1,
	};

	bless $self, $class;
}

#==============================
# main part.
#==============================
sub readParse {
	my $self = shift; 
	my $q = $com->{cgi};

	if( $q->param() ) {
		$self->customerId( $q->param('CUSTOMER_ID') );
		$self->customerName( $q->param('CUSTOMER_NAME') );
		$self->shipNoFrom( $q->param('SHIP_NO_FROM') );
		$self->shipNoTo( $q->param('SHIP_NO_TO') );
		$self->goNoFrom( $q->param('GO_NO_FROM') );
		$self->goNoTo( $q->param('GO_NO_TO') );
		$self->coNoFrom( $q->param('CO_NO_FROM') );
		$self->coNoTo( $q->param('CO_NO_TO') );
		$self->plantShipDateFrom( $q->param('SHIPDATE_FROM'));
		$self->plantShipDateTo( $q->param('SHIPDATE_TO') );
		$self->singleRadio( $q->param('SINGLE_RADIO') );
		$self->shipOrderStatus( $q->param('SHIP_ORDER_STATUS') );
		$self->sortRadio( $q->param('SORT_RADIO') );

		$self->coheaderId( $q->param('COHEADER_ID') );
		$self->goheaderId( $q->param('GOHEADER_ID') );
		$self->soheaderId( $q->param('SOHEADER_ID') );
		$self->error( $q->param('ERROR') );

		return 1;
	}
	return 0;
}


#==============================
# decide the access privilege according to userinfo_id.
# since this method called by gateway.pm, so it uses 
# $self->{com} instead of $com. 
#==============================
sub getAccessSql {
	my $self = shift;
	my $id = shift if @_;

	$userinfo_id ||= $id;
	return unless $userinfo_id; #distinguish return '' and undef.

	my ($p, $t, $s) = ({}, '', undef);

	# ENTITY_TYPE, ENTITY_FIELD, ENTITY_VALUE, ACCOUNT, USERINFO_ID 
	$s = qq{ select * from vw_privilege where USERINFO_ID = "$userinfo_id" };

	my @privilege_ary = $self->{com}->{dbh}->selectrow_array( $s );
	#$self->{com}->writeLog(\@privilege_ary);

	@$p{(type, field, value, account)} = @privilege_ary[0..3];

	# if CUSTOMER_ID exists, certain customer.
	if( $p->{type} =~ m"CUSTOMER" ) {

		# william for hide ERROR for customer, except broker and admin.
		$self->{TYPE} = 1;

		if( ($p->{field} =~ m/CUSTOMER_ID/i) && $p->{value} ) {
			$t=qq{ where CUSTOMER_ID="$p->{value}" and ACCOUNT="$p->{account}"};
		}
		else {
			$t=qq{ where CUSTOMER_ID="$self->customerId()" and ACCOUNT="$p->{account}" };
		}
	}
	# if 'BROKER', see certain account.
	elsif($p->{type} =~ m'BROKER'i) {
		$t = qq{ where ACCOUNT="$p->{account}" };
	}
	# how about 'ADMIN'? see all accounts.
	else {
		$t = qq{ where ACCOUNT="$p->{account}" };
	}
	return $t;
}


#==============================
# set array for CUSTOMER_IDs, set HoA for HTML::Template.
#==============================

sub getCustomerIdsTmpl {
	my $self = shift;
	# my ($s, $all, $dup, %t, @ids);
	my ($s, $all, $dup);

	# why getAccessSql return 'undef' otherwise '' ???
	# failure if not adding: || ''.
	# $s = $self->getAccessSql();
	$s = $self->getAccessSql() || '';

	$sql = qq{ select * from vw_odq_customer_id } . $s;
	$com->writeLog( " ".$sql );

	$all = $com->{dbh}->selectall_arrayref($sql);

	# 06/Dec/2007. the following sql return null.
	# select * from vw_odq_customer_id where CUSTOMER_ID="ICD-1 " and ACCOUNT="TOWERICD"
	return unless( @$all );

	# just 1 record, return array
	if (@$all == 1) {
		return [$all->[0][0], $all->[0][1]];
	}

=comment
	@$dup = map ($_->[0], @$all);
	# if no 'sort', then the display is random.
	%t = map { $_, '' } @$dup; @ids = sort keys( %t );
=cut

	my $id_names = [];
	@$id_names = map{ "$_->[0], $_->[1]" } @$all;

	foreach (@$id_names) {
		my $id = {}; $id->{option} = $_;
		push @{$self->{custids}}, $id;
	}
	return $self->{custids};
}

#==============================
# How to distinguish how many rows are affected after 'select'? 2 ways:
# 1. $rv=$sth->rows; 		(available for non-select execute.)
# 2. select count(*) from ...  (available for select statements.)
#==============================
sub getConditionSql {
	my $self = shift;
	my $STATUS = shift;
	return unless (defined $userinfo_id);
	
	my $AccountSql = $self->getAccessSql() || '';
	my $condition_sql = $AccountSql .
			$self->getCustomerIdSql() .
			$self->getCustomerNameSql() .
			$self->getShipOrderStatusSql($STATUS) .
			$self->getRadioSql() .
			$self->getOrderBySql();

	# advoid sql such as select * from vw_pogoco and STATUS in ("SHIPPED").
	$condition_sql =~ s"and"where" if ($condition_sql !~ 'where');

	return $condition_sql;
}

sub getSelectRows {

    my ($self, $choice) = @_;
	return unless $choice;

	my $STATUS = undef;
	if ($choice == 1) { $STATUS = 'SHSTATUS' }
	elsif ($choice == 2) { $STATUS = 'GHSTATUS' }
	elsif ($choice == 3) { $STATUS = 'CHSTATUS' };

	my $sql_str = q{ select * from vw_pogoco }.$self->getConditionSql($STATUS);
	$com->writeLog(" ".$sql_str);

    my $dup_ids = $com->{dbh}->selectall_arrayref( qq{$sql_str} );

	# unless ($dup_ids) is not right. only unless($dup_ids->[0]) works.
    return unless(defined $dup_ids->[0][0]); 

	# distribute duplicate to distinct.
	my ($ids, %tmp);
	if ($choice == 1) {
		%tmp = map { $_->[9], '' } @{$dup_ids};
	}
	elsif ($choice == 2) {
		%tmp = map { $_->[10], '' } @{$dup_ids};
	}
	elsif ($choice == 3) {
		%tmp = map { $_->[0], '' } @{$dup_ids};
	}
	@$ids = keys( %tmp );
=comment
	my $ids = [];
	if( $choice == 1 ) {
		@$ids = map( $_->[9], @$dup_ids);
	}
	elsif( $choice == 2 ) {
		@$ids = map( $_->[10], @$dup_ids);
	}
	elsif( $choice == 3 ) {
		@$ids = map( $_->[0], @$dup_ids);
	}
=cut
    my $total_count = @{$ids};

	# if multi records, return total count of them.
	return $total_count if ($total_count > 1);

	# if single record, return reference.
    return $ids;
}


#==============================
# odq input form params to SQL.
#==============================

sub getCustomerIdSql {
	my $self = shift;
	my $t = $self->customerId();
	return $t ? qq{ and CUSTOMER_ID = "$t" } : '';
}

#==============================
# odq input form params to SQL.
#==============================

sub getCustomerNameSql {
	my $self = shift;
	my $t = $self->customerName();
	return $t ? qq{ and upper(CUSTOMER_NAME) like "%$t%" } : '';
}

#==============================
#transfer 'pre-released','released','shipped','errors' into
#==============================

sub getShipOrderStatusSql {
	
	my $self = shift;
	my $STATUS = shift || 'STATUS';
	my $status = '';

	my $t = $self->shipOrderStatus();
	return unless $t;

	if ($t =~ m"PRE-RELEASED") {
		#$status = qq{ ("UNPROCESSED", "PROCESSED")};
		$status = "PROCESSED";
	}
	elsif ($t =~ m"RELEASED") {
		$status = "RELEASED";
	}
	elsif ($t =~ m"Order/Package Processed&Labeled"i) {
		$status = "SHIPPED";
	}
	elsif ($t =~ m"ERRORS") {
		$status = "ERROR";
	}

	my $exception = '';
	if ($self->{TYPE}) {
		$exception = qq{ and $STATUS != 'ERROR' };
	}

	return $exception . ($status ? qq{ and $STATUS = "$status" } : '');
}


#==============================
# odq input form params to SQL.
#==============================
# ['SHIP_NO', 'SHIP_NO_FROM', 'SHIP_NO_TO'],
# ['GO_NO', 'GO_NO_FROM', 'GO_NO_TO'],
# ['CO_NO', 'CO_NO_FROM', 'CO_NO_TO'],
# ['PLANT_SHIP_DATE', 'SHIPDATE_FROM', 'SHIPDATE_TO']
sub getRadioSql {

	my $self = shift;

	my ($state, $r) = ('', {}) ;

	# used for single radio box.
	$r = {
		'po' => ['SHIP_NO', $self->shipNoFrom(), $self->shipNoTo() ],
		'go' => ['GO_NO', $self->goNoFrom(), $self->goNoTo() ],
		'co' => ['CO_NO', $self->coNoFrom(), $self->coNoTo() ],
		'ship_date'=>['PLAN_PLANT_SHIPDATE',$self->plantShipDateFrom(),
		$self->plantShipDateTo() ],
	};

	my @t = keys ( %{$r} );

	foreach my $key ( @t ) {

		# date validate supplement. the first choice is javascript.

		my ($from, $to) = @{$r->{$key}}[1..2];

		if ($key eq 'ship_date') {
		 	if ($from && $from !~ m"\w{3}\s{1,2}\d{1,2}\s\d{4}") {
				$from = $com->GetDate("$from","%b %d %Y");
			}
		 	if ($to && $to !~ m"\w{3}\s+\d{1,2}\s\d{4}") {
				$to = $com->GetDate("$to","%b %d %Y");
			}
		}

		# 20/Dec for 19 Dec 
		if ($from && $to && $key eq 'ship_date') {

		    ($from, $to) = ($to, $from) unless ($com->CompareDate($from,$to));

			# Dec 20 2007 11:59:59PM
			$to .= " 11:59:59PM";
			$state .= qq{ and ($r->{$key}[0] between "$from" and "$to") };
		}

		# does PO_NO/GO_NO/CO_NO/Ship_Date field have values?
		elsif( $from && $to ) {
		    ($from, $to) = ($to, $from) if ($from gt $to);

			$state .= qq{ and ($r->{$key}[0] between "$from" and "$to") };
		}
		elsif( $from ) {
			$state .= qq{ and $r->{$key}[0] like "%$from%" };
		}
		elsif( $to ) {
			$state .= qq{ and $r->{$key}[0] like "%$to%" };
		}
	}
	return $state;
}

#==============================
# sort sequence. total 4. I use following as default:
# ORDER BY No, CUSTOMER_ID, CUSTOMER_NAME, PLAN_PLANT_SHIPDATE DESC
#==============================
sub getOrderBySql {

	my $self = shift;
	my $order = $self->sortRadio();
	my @SortBy=('SHIP_NO','CUSTOMER_ID','CUSTOMER_NAME','PLAN_PLANT_SHIPDATE');

	return qq{ order by } . (
		($order == 1) ?  join(',', @SortBy[0..3]) :
		($order == 2) ?  join(',', @SortBy[1,0,2,3]) :
		($order == 3) ?  join(',', @SortBy[2,0,1,3]) :
						join(',', @SortBy[3,0..2]) ); # . q{ desc };
}

1;

One response to “CGI

  1. powercashadvance.com 10/27/2011 at 7:16 am

    It was some sort of excitement discovering your site yesterday. I got here just now hoping to discover something new. I was not frustrated. Your ideas with new techniques on this thing were helpful and a fantastic help to me personally. Thank you for creating time to create these things and for sharing your mind.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: