[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
SF.net SVN: ledger-smb:[3301] trunk
- Subject: SF.net SVN: ledger-smb:[3301] trunk
- From: ..hidden..
- Date: Mon, 20 Jun 2011 12:49:53 +0000
Revision: 3301
http://ledger-smb.svn.sourceforge.net/ledger-smb/?rev=3301&view=rev
Author: einhverfr
Date: 2011-06-20 12:49:53 +0000 (Mon, 20 Jun 2011)
Log Message:
-----------
First draft at employee search routines, not working on new code yet
Modified Paths:
--------------
trunk/LedgerSMB/HR.pm
trunk/scripts/employee.pl
trunk/sql/modules/Employee.sql
Added Paths:
-----------
trunk/UI/employee/
trunk/UI/employee/filter.css
trunk/UI/employee/filter.html
Modified: trunk/LedgerSMB/HR.pm
===================================================================
--- trunk/LedgerSMB/HR.pm 2011-06-20 11:09:03 UTC (rev 3300)
+++ trunk/LedgerSMB/HR.pm 2011-06-20 12:49:53 UTC (rev 3301)
@@ -33,162 +33,6 @@
package HR;
-sub get_employee {
- my ( $self, $myconfig, $form ) = @_;
-
- my $dbh = $form->{dbh};
-
- my $query;
- my $sth;
- my $ref;
- my $notid = "";
-
- if ( $form->{id} ) {
- $query = qq|SELECT e.* FROM employee e WHERE e.employeenumber = ?|;
- $sth = $dbh->prepare($query);
- $sth->execute( $form->{id} )
- || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );
-
- $ref = $sth->fetchrow_hashref(NAME_lc);
-
- # check if employee can be deleted, orphaned
- $form->{status} = "orphaned" unless $ref->{login};
-
- $ref->{employeelogin} = $ref->{login};
- delete $ref->{login};
- for ( keys %$ref ) { $form->{$_} = $ref->{$_} }
-
- $sth->finish;
-
- # get manager
- $form->{manager_id} *= 1;
-
- $sth = $dbh->prepare("SELECT first_name FROM employee WHERE entity_id = ?");
- $sth->execute( $form->{manager_id} );
- ( $form->{manager} ) = $sth->fetchrow_array;
-
- $notid = qq|AND id != | . $dbh->quote( $form->{id} );
-
- }
- else {
-
- ( $form->{startdate} ) = $dbh->selectrow_array("SELECT current_date");
-
- }
-
- # get managers
- $query = qq|
- SELECT entity_id, first_name
- FROM employee
- WHERE sales = '1'
- AND role = 'manager'
- $notid
- ORDER BY 2|;
- $sth = $dbh->prepare($query);
- $sth->execute || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );
-
- while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
- push @{ $form->{all_manager} }, $ref;
- }
- $sth->finish;
-
- $dbh->commit;
-
-}
-
-sub save_employee {
- my ( $self, $myconfig, $form ) = @_;
- $form->{employeenumber} =
- $form->update_defaults( $myconfig, "employeenumber", $dbh )
- if !$form->{employeenumber};
-
- my $dbh = $form->{dbh};
- my $query;
- my $sth;
-
- if ( !$form->{id} ) {
- my $uid = localtime;
- $uid .= "$$";
-
- $query = qq|INSERT INTO employee (first_name) VALUES ('$uid')|;
- $dbh->do($query)
- || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );
-
- $query = qq|SELECT entity_id FROM employee WHERE first_name = '$uid'|;
- $sth = $dbh->prepare($query);
- $sth->execute
- || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );
-
- ( $form->{id} ) = $sth->fetchrow_array;
- $sth->finish;
- }
-
- my ( $null, $manager_id ) = split /--/, $form->{manager};
- $manager_id *= 1;
- $form->{sales} *= 1;
-
-
- $query = qq|
- UPDATE employee
- SET employeenumber = ?,
- first_name = ?,
- address1 = ?,
- address2 = ?,
- city = ?,
- state = ?,
- zipcode = ?,
- country = ?,
- workphone = ?,
- homephone = ?,
- startdate = ?,
- enddate = ?,
- notes = ?,
- role = ?,
- sales = ?,
- email = ?,
- ssn = ?,
- dob = ?,
- iban = ?,
- bic = ?,
- manager_id = ?
- WHERE id = ?|;
- $sth = $dbh->prepare($query);
- $form->{dob} ||= undef;
- $form->{startdate} ||= undef;
- $form->{enddate} ||= undef;
- $sth->execute(
- $form->{employeenumber}, $form->{first_name}, $form->{address1},
- $form->{address2}, $form->{city}, $form->{state},
- $form->{zipcode}, $form->{country}, $form->{workphone},
- $form->{homephone}, $form->{startdate}, $form->{enddate},
- $form->{notes}, $form->{role}, $form->{sales},
- $form->{email}, $form->{ssn}, $form->{dob},
- $form->{iban}, $form->{bic}, $manager_id,
- $form->{id}
- ) || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );
-
- $dbh->commit;
-
-}
-
-sub delete_employee {
- my ( $self, $myconfig, $form ) = @_;
-
- # connect to database
- my $dbh = $form->{dbh};
-
- # delete employee
-
- my $query = qq|
- DELETE FROM employee
- WHERE id = | . $dbh->quote( $form->{id} );
- $dbh->do($query)
- || $form->dberror( __FILE__ . ':' . __LINE__ . ':' . $query );
-
- $dbh->commit;
-
-}
-
sub employees {
my ( $self, $myconfig, $form ) = @_;
Added: trunk/UI/employee/filter.css
===================================================================
--- trunk/UI/employee/filter.css (rev 0)
+++ trunk/UI/employee/filter.css 2011-06-20 12:49:53 UTC (rev 3301)
@@ -0,0 +1,23 @@
+label {
+ width: 9em;
+ display: block;
+ float: left;
+ text-align: right;
+ margin-right: 3px;
+}
+
+div {
+ overflow: auto;
+}
+
+input.name {
+ width: 15em;
+}
+
+input.date {
+ width: 10em;
+}
+
+div.inputgroup {
+ float: left
+}
Added: trunk/UI/employee/filter.html
===================================================================
--- trunk/UI/employee/filter.html (rev 0)
+++ trunk/UI/employee/filter.html 2011-06-20 12:49:53 UTC (rev 3301)
@@ -0,0 +1,309 @@
+<?lsmb INCLUDE 'ui-header.html'
+ include_stylesheet = [
+ 'UI/employee/filter.css'
+ ]
+?>
+<?lsmb PROCESS 'elements.html' ?>
+<body>
+<div class="listtop"><?lsmb text('Search Employees') ?></div>
+<form action="employee.pl" method="POST" name="search_filter">
+<div class="inputrow">
+<div class="inputgroup">
+<?lsmb INCLUDE input element_data = {
+ name = 'employeenumber'
+ value = employeenumber
+ size = '32'
+ type = 'text'
+ class = 'controlcode'
+ label = text('Employee Number') #'
+} ?>
+</div>
+</div>
+<div class="inputrow" id="namerow">
+<div class="inputgroup">
+<?lsmb INCLUDE input element_data = {
+ name = 'first_name'
+ value = first_name
+ type = 'text'
+ class = 'name'
+ label = text('First Name')#'
+} ?>
+</div>
+<div class="inputgroup">
+<?lsmb INCLUDE input element_data = {
+ name = 'middle_name'
+ value = middle_name
+ type = 'text'
+ class = 'name'
+ label = text('Middle Name')#'
+} ?>
+</div>
+<div class="inputgroup">
+<?lsmb INCLUDE input element_data = {
+ name = 'last_name'
+ value = last_name
+ type = 'text'
+ class = 'name'
+ label = text('Last Name')#'
+} ?>
+</div>
+</div>
+<div class="inputrow" id="startdaterow">
+<label><?lsmb text('Start Date') ?></label>
+<div class="inputgroup">
+<?lsmb INCLUDE input element_data = {
+ name = 'startdate_from'
+ value = startdate_from
+ type = 'text'
+ class = 'date'
+ label = text('From')
+} ?>
+</div>
+<div class="inputgroup">
+<?lsmb INCLUDE input element_data = {
+ name = 'startdate_to'
+ value = startdate_to
+ type = 'text'
+ class = 'date'
+ label = text('To')
+} ?>
+</div>
+</div>
+<div class="listtop"><?lsmb text('Include In Report') ?></div>
+<div id="include_cols">
+<div class="inputgroup">
+<?lsmb IF l_position;
+ c_position = 'CHECKED';
+ END
+?>
+<?lsmb INCLUDE input element_data = {
+ name = 'l_position'
+ value = '1'
+ type = 'checkbox'
+ class = 'checkbox'
+ label = text('Position')
+ checked = c_position
+} ?>
+</div>
+<div class="inputgroup">
+<?lsmb IF l_id;
+ c_id = 'CHECKED';
+ END
+?>
+<?lsmb INCLUDE input element_data = {
+ name = 'l_id'
+ value = '1'
+ type = 'checkbox'
+ class = 'checkbox'
+ label = text('ID')
+ checked = c_id
+} ?>
+</div>
+<div class="inputgroup">
+<?lsmb IF l_employeenumber;
+ c_employeenumber = 'CHECKED';
+ END
+?>
+<?lsmb INCLUDE input element_data = {
+ name = 'l_employeenumber'
+ value = '1'
+ type = 'checkbox'
+ class = 'checkbox'
+ label = text('Employee Number') #'
+ checked = c_employeenumber
+} ?>
+</div>
+<div class="inputgroup">
+<?lsmb IF l_salutation;
+ c_salutation = 'CHECKED';
+ END
+?>
+<?lsmb INCLUDE input element_data = {
+ name = 'l_salutation'
+ value = '1'
+ type = 'checkbox'
+ class = 'checkbox'
+ label = text('Salutation')
+ checked = c_salutation
+} ?>
+</div>
+<div class="inputgroup">
+<?lsmb IF l_first_name;
+ c_first_name = 'CHECKED';
+ END
+?>
+<?lsmb INCLUDE input element_data = {
+ name = 'l_first_name'
+ value = '1'
+ type = 'checkbox'
+ class = 'checkbox'
+ label = text('First Name') #'
+ checked = c_first_name
+} ?>
+</div>
+<div class="inputgroup">
+<?lsmb IF l_middle_name;
+ c_middle_name = 'CHECKED';
+ END
+?>
+<?lsmb INCLUDE input element_data = {
+ name = 'l_middle_name'
+ value = '1'
+ type = 'checkbox'
+ class = 'checkbox'
+ label = text('Middle Name') #'
+ checked = c_middle_name
+} ?>
+</div>
+<div class="inputgroup">
+<?lsmb IF l_last_name;
+ c_last_name = 'CHECKED';
+ END
+?>
+<?lsmb INCLUDE input element_data = {
+ name = 'l_last_name'
+ value = '1'
+ type = 'checkbox'
+ class = 'checkbox'
+ label = text('Last Name') #'
+ checked = c_last_name
+} ?>
+</div>
+<div class="inputgroup">
+<?lsmb IF l_dob;
+ c_dob = 'CHECKED';
+ END
+?>
+<?lsmb INCLUDE input element_data = {
+ name = 'l_dob'
+ value = '1'
+ type = 'checkbox'
+ class = 'checkbox'
+ label = text('DOB')
+ checked = c_dob
+} ?>
+</div>
+<div class="inputgroup">
+<?lsmb IF l_startdate;
+ c_id = 'CHECKED';
+ END
+?>
+<?lsmb INCLUDE input element_data = {
+ name = 'l_startdate'
+ value = '1'
+ type = 'checkbox'
+ class = 'checkbox'
+ label = text('Start Date') #'
+ checked = c_startdate
+} ?>
+</div>
+<div class="inputgroup">
+<?lsmb IF l_enddate;
+ c_enddate = 'CHECKED';
+ END
+?>
+<?lsmb INCLUDE input element_data = {
+ name = 'l_enddate'
+ value = '1'
+ type = 'checkbox'
+ class = 'checkbox'
+ label = text('End Date') #'
+ checked = c_enddate
+} ?>
+</div>
+<div class="inputgroup">
+<?lsmb IF l_role;
+ c_role = 'CHECKED';
+ END
+?>
+<?lsmb INCLUDE input element_data = {
+ name = 'l_role'
+ value = '1'
+ type = 'checkbox'
+ class = 'checkbox'
+ label = text('Role')
+ checked = c_role
+} ?>
+</div>
+<div class="inputgroup">
+<?lsmb IF l_ssn;
+ c_ssn = 'CHECKED';
+ END
+?>
+<?lsmb INCLUDE input element_data = {
+ name = 'l_ssn'
+ value = '1'
+ type = 'checkbox'
+ class = 'checkbox'
+ label = text('SSN')
+ checked = c_ssn
+} ?>
+</div>
+<div class="inputgroup">
+<?lsmb IF l_sales;
+ c_sales = 'CHECKED';
+ END
+?>
+<?lsmb INCLUDE input element_data = {
+ name = 'l_sales'
+ value = '1'
+ type = 'checkbox'
+ class = 'checkbox'
+ label = text('Sales')
+ checked = c_sales
+} ?>
+</div>
+<div class="inputgroup">
+<?lsmb IF l_manager_id;
+ c_manager_id = 'CHECKED';
+ END
+?>
+<?lsmb INCLUDE input element_data = {
+ name = 'l_manager_id'
+ value = '1'
+ type = 'checkbox'
+ class = 'checkbox'
+ label = text('Manager ID') #'
+ checked = c_manager_id
+} ?>
+</div>
+<div class="inputgroup">
+<?lsmb IF l_manager_first_name;
+ c_manager_first_name = 'CHECKED';
+ END
+?>
+<?lsmb INCLUDE input element_data = {
+ name = 'l_manager_first_name'
+ value = '1'
+ type = 'checkbox'
+ class = 'checkbox'
+ label = text('Manager First Name') #'
+ checked = c_manager_first_name
+} ?>
+</div>
+<div class="inputgroup">
+<?lsmb IF l_manager_last_name;
+ c_manager_last_name = 'CHECKED';
+ END
+?>
+<?lsmb INCLUDE input element_data = {
+ name = 'l_manager_last_name'
+ value = '1'
+ type = 'checkbox'
+ class = 'checkbox'
+ label = text('Manager Last Name') #'
+ checked = c_manager_last_name
+} ?>
+</div>
+</div>
+<hr>
+<?lsmb INCLUDE button element_data = {
+ name = 'action'
+ value = 'search_results'
+ class = 'submit'
+ type = 'submit'
+ text = text('Continue')
+} ?>
+</form>
+</body>
+</html>
Modified: trunk/scripts/employee.pl
===================================================================
--- trunk/scripts/employee.pl 2011-06-20 11:09:03 UTC (rev 3300)
+++ trunk/scripts/employee.pl 2011-06-20 12:49:53 UTC (rev 3301)
@@ -170,6 +170,18 @@
_render_main_screen($employee);
}
+sub search {
+ my $request = shift @_;
+ my $template = LedgerSMB::Template->new(
+ user => $employee->{_user},
+ template => 'filter',
+ locale => $employee->{_locale},
+ path => 'UI/employee',
+ format => 'HTML'
+ );
+ $template->render($request);
+}
+
sub edit{
my $request = shift @_;
my $employee = LedgerSMB::DBObject::Employee->new({base => $request});
Modified: trunk/sql/modules/Employee.sql
===================================================================
--- trunk/sql/modules/Employee.sql 2011-06-20 11:09:03 UTC (rev 3300)
+++ trunk/sql/modules/Employee.sql 2011-06-20 12:49:53 UTC (rev 3301)
@@ -81,6 +81,30 @@
WHERE p.entity_id = $1;
$$ language sql;
+CREATE OR REPLACE FUNCTION employee__search
+(in_employeenumber text, in_startdate_from date, in_startdate_to date,
+in_first_name text, in_middle_name text, in_last_name text,
+in_notes text)
+RETURNS SETOF employee_result as
+$$
+SELECT p.entity_id, p.id, s.salutation,
+ p.first_name, p.middle_name, p.last_name,
+ ee.startdate, ee.enddate, ee.role, ee.ssn, ee.sales, ee.manager_id,
+ mp.first_name, mp.last_name, ee.employeenumber, ee.dob
+ FROM person p
+ JOIN entity_employee ee on (ee.entity_id = p.entity_id)
+LEFT JOIN salutation s on (p.salutation_id = s.id)
+LEFT JOIN person mp ON ee.manager_id = p.entity_id
+ WHERE ($7 is null or p.entity_id in (select ref_key from entity_note
+ WHERE note ilike '%' || $7 || '%'))
+ and ($1 is null or $1 = ee.employeenumber)
+ and ($2 is null or $2 <= ee.startdate)
+ and ($3 is null or $3 >= ee.startdate)
+ and ($4 is null or p.first_name ilike '%' || $4 || '%')
+ and ($5 is null or p.middle_name ilike '%' || $5 || '%')
+ and ($6 is null or p.last_name ilike '%' || $6 || '%');
+$$ language sql;
+
CREATE OR REPLACE FUNCTION employee__list_managers
(in_id integer)
RETURNS SETOF employees as
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.