summaryrefslogtreecommitdiff
path: root/LedgerSMB/User.pm
blob: f38251a048d0746abc6b839327af4b01a5b7b2d7 (plain)
  1. #=====================================================================
  2. # LedgerSMB
  3. # Small Medium Business Accounting software
  4. # http://www.ledgersmb.org/
  5. #
  6. # Copyright (C) 2006
  7. # This work contains copyrighted information from a number of sources all used
  8. # with permission.
  9. #
  10. # This file contains source code included with or based on SQL-Ledger which
  11. # is Copyright Dieter Simader and DWS Systems Inc. 2000-2005 and licensed
  12. # under the GNU General Public License version 2 or, at your option, any later
  13. # version. For a full list including contact information of contributors,
  14. # maintainers, and copyright holders, see the CONTRIBUTORS file.
  15. #
  16. # Original Copyright Notice from SQL-Ledger 2.6.17 (before the fork):
  17. # Copyright (C) 2000
  18. #
  19. # Author: DWS Systems Inc.
  20. # Web: http://www.sql-ledger.org
  21. #
  22. # Contributors: Jim Rawlings <jim@your-dba.com>
  23. #
  24. #======================================================================
  25. #
  26. # This file has undergone whitespace cleanup.
  27. #
  28. #======================================================================
  29. #
  30. # user related functions
  31. #
  32. #=====================================================================
  33. package LedgerSMB::User;
  34. use LedgerSMB::Sysconfig;
  35. use LedgerSMB::Session;
  36. use Data::Dumper;
  37. sub new {
  38. my ( $type, $login ) = @_;
  39. my $self = {};
  40. if ( $login ne "" ) {
  41. # use central db
  42. my $dbh = ${LedgerSMB::Sysconfig::GLOBALDBH};
  43. # for now, this is querying the table directly... ugly
  44. my $fetchUserPrefs = $dbh->prepare(
  45. "SELECT acs, address, businessnumber,
  46. company, countrycode, currency,
  47. dateformat, dbdriver, dbhost, dbname,
  48. dboptions, dbpasswd, dbport, dbuser,
  49. email, fax, menuwidth, name, numberformat,
  50. password, print, printer, role, sid,
  51. signature, stylesheet, tel, templates,
  52. timeout, vclimit, u.username
  53. FROM users_conf as uc, users as u
  54. WHERE u.username = ?
  55. AND u.id = uc.id;"
  56. );
  57. $fetchUserPrefs->execute($login);
  58. my $userHashRef = $fetchUserPrefs->fetchrow_hashref;
  59. while ( my ( $key, $value ) = each( %{$userHashRef} ) ) {
  60. $self->{$key} = $value;
  61. }
  62. chomp( $self->{dbport} );
  63. chomp( $self->{dbname} );
  64. chomp( $self->{dbhost} );
  65. $self->{dbconnect} =
  66. 'dbi:Pg:dbname='
  67. . $self->{dbname}
  68. . ';host='
  69. . $self->{dbhost}
  70. . ';port='
  71. . $self->{dbport};
  72. if ( $self->{username} ) {
  73. $self->{login} = $login;
  74. }
  75. }
  76. bless $self, $type;
  77. }
  78. sub country_codes {
  79. use Locale::Country;
  80. use Locale::Language;
  81. my %cc = ();
  82. # scan the locale directory and read in the LANGUAGE files
  83. opendir DIR, "${LedgerSMB::Sysconfig::localepath}";
  84. my @dir = grep !/^\..*$/, readdir DIR;
  85. foreach my $dir (@dir) {
  86. $dir = substr( $dir, 0, -3 );
  87. $cc{$dir} = code2language( substr( $dir, 0, 2 ) );
  88. $cc{$dir} .= ( "/" . code2country( substr( $dir, 3, 2 ) ) )
  89. if length($dir) > 2;
  90. $cc{$dir} .= ( " " . substr( $dir, 6 ) ) if length($dir) > 5;
  91. }
  92. closedir(DIR);
  93. %cc;
  94. }
  95. sub fetch_config {
  96. #I'm hoping that this function will go and is a temporary bridge
  97. #until we get rid of %myconfig elsewhere in the code
  98. my ( $self, $login ) = @_;
  99. if ( !$login ) {
  100. &error( $self, "Access Denied" );
  101. }
  102. # use central db
  103. my $dbh = ${LedgerSMB::Sysconfig::GLOBALDBH};
  104. # for now, this is querying the table directly... ugly
  105. my $fetchUserPrefs = $dbh->prepare(
  106. "SELECT acs, address, businessnumber,
  107. company, countrycode, currency,
  108. dateformat, dbdriver, dbhost, dbname,
  109. dboptions, dbpasswd, dbport, dbuser,
  110. email, fax, menuwidth, name, numberformat,
  111. password, print, printer, role, sid,
  112. signature, stylesheet, tel, templates,
  113. timeout, vclimit, u.username
  114. FROM users_conf as uc, users as u
  115. WHERE u.username = ?
  116. AND u.id = uc.id;"
  117. );
  118. $fetchUserPrefs->execute($login);
  119. my $userHashRef = $fetchUserPrefs->fetchrow_hashref;
  120. if ( !$userHashRef ) {
  121. &error( $self, "Access Denied" );
  122. }
  123. while ( my ( $key, $value ) = each( %{$userHashRef} ) ) {
  124. $myconfig{$key} = $value;
  125. }
  126. chomp( $myconfig{'dbport'} );
  127. chomp( $myconfig{'dbname'} );
  128. chomp( $myconfig{'dbhost'} );
  129. $myconfig{'login'} = $login;
  130. $myconfig{'dbconnect'} =
  131. 'dbi:Pg:dbname='
  132. . $myconfig{'dbname'}
  133. . ';host='
  134. . $myconfig{'dbhost'}
  135. . ';port='
  136. . $myconfig{'dbport'};
  137. return \%myconfig;
  138. }
  139. sub login {
  140. my ( $self, $form ) = @_;
  141. my $rc = -1;
  142. if ( $self->{login} ne "" ) {
  143. if (
  144. !Session::password_check(
  145. $form, $form->{login}, $form->{password}
  146. )
  147. )
  148. {
  149. return -1;
  150. }
  151. #this is really dumb, but %myconfig will have to stay until 1.3
  152. while ( my ( $key, $value ) = each( %{$self} ) ) {
  153. $myconfig{$key} = $value;
  154. }
  155. # check if database is down
  156. my $dbh =
  157. DBI->connect( $myconfig{dbconnect}, $myconfig{dbuser},
  158. $myconfig{dbpasswd} )
  159. or $self->error( __FILE__ . ':' . __LINE__ . ': ' . $DBI::errstr );
  160. $dbh->{pg_enable_utf8} = 1;
  161. # we got a connection, check the version
  162. my $query = qq|
  163. SELECT value FROM defaults
  164. WHERE setting_key = 'version'|;
  165. my $sth = $dbh->prepare($query);
  166. $sth->execute || $form->dberror( __FILE__ . ':' . __LINE__ . $query );
  167. my ($dbversion) = $sth->fetchrow_array;
  168. $sth->finish;
  169. # add login to employee table if it does not exist
  170. # no error check for employee table, ignore if it does not exist
  171. my $login = $self->{login};
  172. $login =~ s/@.*//;
  173. $query = qq|SELECT id FROM employee WHERE login = ?|;
  174. $sth = $dbh->prepare($query);
  175. $sth->execute($login);
  176. my ($id) = $sth->fetchrow_array;
  177. $sth->finish;
  178. if ( !$id ) {
  179. my ($employeenumber) =
  180. $form->update_defaults( \%myconfig, "employeenumber", $dbh );
  181. $query = qq|
  182. INSERT INTO employee
  183. (login, employeenumber, name,
  184. workphone, role)
  185. VALUES (?, ?, ?, ?, ?)|;
  186. $sth = $dbh->prepare($query);
  187. $sth->execute(
  188. $login, $employeenumber, $myconfig{name},
  189. $myconfig{tel}, $myconfig{role}
  190. );
  191. }
  192. $dbh->disconnect;
  193. $rc = 0;
  194. if ( $form->{dbversion} ne $dbversion ) {
  195. $rc = -3;
  196. $dbupdate =
  197. ( calc_version($dbversion) < calc_version( $form->{dbversion} ) );
  198. }
  199. if ($dbupdate) {
  200. $rc = -4;
  201. # if DB2 bale out
  202. if ( $myconfig{dbdriver} eq 'DB2' ) {
  203. $rc = -2;
  204. }
  205. }
  206. }
  207. $rc;
  208. }
  209. sub check_recurring {
  210. my ( $self, $form ) = @_;
  211. my $dbh =
  212. DBI->connect( $self->{dbconnect}, $self->{dbuser}, $self->{dbpasswd} )
  213. or $form->dberror( __FILE__ . ':' . __LINE__ );
  214. $dbh->{pg_encode_utf8} = 1;
  215. my $query = qq|
  216. SELECT count(*) FROM recurring
  217. WHERE enddate >= current_date AND nextdate <= current_date|;
  218. ($_) = $dbh->selectrow_array($query);
  219. $dbh->disconnect;
  220. $_;
  221. }
  222. sub dbconnect_vars {
  223. my ( $form, $db ) = @_;
  224. my %dboptions = (
  225. 'Pg' => {
  226. 'yy-mm-dd' => 'set DateStyle to \'ISO\'',
  227. 'mm/dd/yy' => 'set DateStyle to \'SQL, US\'',
  228. 'mm-dd-yy' => 'set DateStyle to \'POSTGRES, US\'',
  229. 'dd/mm/yy' => 'set DateStyle to \'SQL, EUROPEAN\'',
  230. 'dd-mm-yy' => 'set DateStyle to \'POSTGRES, EUROPEAN\'',
  231. 'dd.mm.yy' => 'set DateStyle to \'GERMAN\''
  232. }
  233. );
  234. $form->{dboptions} = $dboptions{ $form->{dbdriver} }{ $form->{dateformat} };
  235. $form->{dbconnect} = "dbi:$form->{dbdriver}:dbname=$db";
  236. $form->{dbconnect} .= ";host=$form->{dbhost}";
  237. $form->{dbconnect} .= ";port=$form->{dbport}";
  238. }
  239. sub dbdrivers {
  240. my @drivers = DBI->available_drivers();
  241. # return (grep { /(Pg|Oracle|DB2)/ } @drivers);
  242. return ( grep { /Pg$/ } @drivers );
  243. }
  244. sub dbsources {
  245. my ( $self, $form ) = @_;
  246. my @dbsources = ();
  247. my ( $sth, $query );
  248. $form->{dbdefault} = $form->{dbuser} unless $form->{dbdefault};
  249. $form->{sid} = $form->{dbdefault};
  250. &dbconnect_vars( $form, $form->{dbdefault} );
  251. my $dbh =
  252. DBI->connect( $form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd} )
  253. or $form->dberror( __FILE__ . ':' . __LINE__ );
  254. $dbh->{pg_enable_utf8} = 1;
  255. if ( $form->{dbdriver} eq 'Pg' ) {
  256. $query = qq|SELECT datname FROM pg_database|;
  257. $sth = $dbh->prepare($query);
  258. $sth->execute || $form->dberror( __FILE__ . ':' . __LINE__ . $query );
  259. while ( my ($db) = $sth->fetchrow_array ) {
  260. if ( $form->{only_acc_db} ) {
  261. next if ( $db =~ /^template/ );
  262. &dbconnect_vars( $form, $db );
  263. my $dbh =
  264. DBI->connect( $form->{dbconnect}, $form->{dbuser},
  265. $form->{dbpasswd} )
  266. or $form->dberror( __FILE__ . ':' . __LINE__ );
  267. $dbh->{pg_enable_utf8} = 1;
  268. $query = qq|
  269. SELECT tablename FROM pg_tables
  270. WHERE tablename = 'defaults'
  271. AND tableowner = ?|;
  272. my $sth = $dbh->prepare($query);
  273. $sth->execute( $form->{dbuser} )
  274. || $form->dberror( __FILE__ . ':' . __LINE__ . $query );
  275. if ( $sth->fetchrow_array ) {
  276. push @dbsources, $db;
  277. }
  278. $sth->finish;
  279. $dbh->disconnect;
  280. next;
  281. }
  282. push @dbsources, $db;
  283. }
  284. }
  285. $sth->finish;
  286. $dbh->disconnect;
  287. return @dbsources;
  288. }
  289. sub dbcreate {
  290. my ( $self, $form ) = @_;
  291. my %dbcreate =
  292. ( 'Pg' => qq|CREATE DATABASE "$form->{db}" WITH ENCODING = 'UNICODE'| );
  293. $form->{sid} = $form->{dbdefault};
  294. &dbconnect_vars( $form, $form->{dbdefault} );
  295. # The below line connects to Template1 or another template file in order
  296. # to create the db. One must disconnect and reconnect later.
  297. if ( $form->{dbsuperuser} ) {
  298. my $superdbh =
  299. DBI->connect( $form->{dbconnect}, $form->{dbsuperuser},
  300. $form->{dbsuperpasswd} )
  301. or $form->dberror( __FILE__ . ':' . __LINE__ );
  302. $superdbh->{pg_enable_utf8} = 1;
  303. my $query = qq|$dbcreate{$form->{dbdriver}}|;
  304. $superdbh->do($query)
  305. || $form->dberror( __FILE__ . ':' . __LINE__ . $query );
  306. $superdbh->disconnect;
  307. }
  308. #Reassign for the work below
  309. &dbconnect_vars( $form, $form->{db} );
  310. my $dbh =
  311. DBI->connect( $form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd} )
  312. or $form->dberror( __FILE__ . ':' . __LINE__ );
  313. $dbh->{pg_enable_utf8} = 1;
  314. if ( $form->{dbsuperuser} ) {
  315. my $superdbh =
  316. DBI->connect( $form->{dbconnect}, $form->{dbsuperuser},
  317. $form->{dbsuperpasswd} )
  318. or $form->dberror( __FILE__ . ':' . __LINE__ );
  319. $superdbh->{pg_enable_utf8} = 1;
  320. # JD: We need to check for plpgsql,
  321. # if it isn't there create it, if we can't error
  322. # Good chance I will have to do this twice as I get
  323. # used to the way the code is structured
  324. my %langcreate = ( 'Pg' => qq|CREATE LANGUAGE plpgsql| );
  325. my $query = qq|$langcreate{$form->{dbdriver}}|;
  326. $superdbh->do($query);
  327. $superdbh->disconnect;
  328. }
  329. # create the tables
  330. my $dbdriver =
  331. ( $form->{dbdriver} =~ /Pg/ )
  332. ? 'Pg'
  333. : $form->{dbdriver};
  334. my $filename = qq|sql/Pg-database.sql|;
  335. $self->process_query( $form, $dbh, $filename );
  336. # load gifi
  337. ($filename) = split /_/, $form->{chart};
  338. $filename =~ s/_//;
  339. $self->process_query( $form, $dbh, "sql/${filename}-gifi.sql" );
  340. # load chart of accounts
  341. $filename = qq|sql/$form->{chart}-chart.sql|;
  342. $self->process_query( $form, $dbh, $filename );
  343. # create custom tables and functions
  344. my $item;
  345. foreach $item (qw(tables functions)) {
  346. $filename = "sql/${dbdriver}-custom_${item}.sql";
  347. if ( -f "$filename" ) {
  348. $self->process_query( $form, $dbh, $filename );
  349. }
  350. }
  351. $dbh->disconnect;
  352. }
  353. sub process_query {
  354. my ( $self, $form, $dbh, $filename ) = @_;
  355. return unless ( -f $filename );
  356. $ENV{PGPASSWORD} = $form->{dbpasswd};
  357. $ENV{PGUSER} = $form->{dbuser};
  358. $ENV{PGDATABASE} = $form->{db};
  359. $ENV{PGHOST} = $form->{dbhost};
  360. $ENV{PGPORT} = $form->{dbport};
  361. $results = `psql -f $filename 2>&1`;
  362. if ($?) {
  363. $form->error($!);
  364. }
  365. elsif ( $results =~ /error/i ) {
  366. $form->error($results);
  367. }
  368. }
  369. sub dbdelete {
  370. my ( $self, $form ) = @_;
  371. $form->{sid} = $form->{dbdefault};
  372. &dbconnect_vars( $form, $form->{dbdefault} );
  373. my $dbh =
  374. DBI->connect( $form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd} )
  375. or $form->dberror( __FILE__ . ':' . __LINE__ );
  376. $dbh->{pg_enable_utf8} = 1;
  377. my $query = qq|DROP DATABASE "$form->{db}"|;
  378. $dbh->do($query) || $form->dberror( __FILE__ . ':' . __LINE__ . $query );
  379. $dbh->disconnect;
  380. }
  381. sub dbsources_unused {
  382. my ( $self, $form, $memfile ) = @_;
  383. my @dbexcl = ();
  384. my @dbsources = ();
  385. $form->error( __FILE__ . ':' . __LINE__ . ": $memfile locked!" )
  386. if ( -f "${memfile}.LCK" );
  387. # open members file
  388. open( FH, '<', "$memfile" )
  389. or $form->error( __FILE__ . ':' . __LINE__ . ": $memfile : $!" );
  390. while (<FH>) {
  391. if (/^dbname=/) {
  392. my ( $null, $item ) = split /=/;
  393. push @dbexcl, $item;
  394. }
  395. }
  396. close FH;
  397. $form->{only_acc_db} = 1;
  398. my @db = &dbsources( "", $form );
  399. push @dbexcl, $form->{dbdefault};
  400. foreach $item (@db) {
  401. unless ( grep /$item$/, @dbexcl ) {
  402. push @dbsources, $item;
  403. }
  404. }
  405. return @dbsources;
  406. }
  407. sub dbneedsupdate {
  408. my ( $self, $form ) = @_;
  409. my %dbsources = ();
  410. my $query;
  411. $form->{sid} = $form->{dbdefault};
  412. &dbconnect_vars( $form, $form->{dbdefault} );
  413. my $dbh =
  414. DBI->connect( $form->{dbconnect}, $form->{dbuser}, $form->{dbpasswd} )
  415. or $form->dberror( __FILE__ . ':' . __LINE__ );
  416. $dbh->{pg_enable_utf8} = 1;
  417. if ( $form->{dbdriver} =~ /Pg/ ) {
  418. $query = qq|
  419. SELECT d.datname
  420. FROM pg_database d, pg_user u
  421. WHERE d.datdba = u.usesysid
  422. AND u.usename = ?|;
  423. my $sth = $dbh->prepare($query);
  424. $sth->execute( $form->{dbuser} )
  425. || $form->dberror( __FILE__ . ':' . __LINE__ . $query );
  426. while ( my ($db) = $sth->fetchrow_array ) {
  427. next if ( $db =~ /^template/ );
  428. &dbconnect_vars( $form, $db );
  429. my $dbh =
  430. DBI->connect( $form->{dbconnect}, $form->{dbuser},
  431. $form->{dbpasswd} )
  432. or $form->dberror( __FILE__ . ':' . __LINE__ );
  433. $dbh->{pg_enable_utf8};
  434. $query = qq|
  435. SELECT tablename
  436. FROM pg_tables
  437. WHERE tablename = 'defaults'|;
  438. my $sth = $dbh->prepare($query);
  439. $sth->execute
  440. || $form->dberror( __FILE__ . ':' . __LINE__ . $query );
  441. if ( $sth->fetchrow_array ) {
  442. $query = qq|
  443. SELECT value FROM defaults
  444. WHERE setting_key = 'version'|;
  445. my $sth = $dbh->prepare($query);
  446. $sth->execute;
  447. if ( my ($version) = $sth->fetchrow_array ) {
  448. $dbsources{$db} = $version;
  449. }
  450. $sth->finish;
  451. }
  452. $sth->finish;
  453. $dbh->disconnect;
  454. }
  455. $sth->finish;
  456. }
  457. $dbh->disconnect;
  458. %dbsources;
  459. }
  460. sub dbupdate {
  461. my ( $self, $form ) = @_;
  462. $form->{sid} = $form->{dbdefault};
  463. my @upgradescripts = ();
  464. my $query;
  465. my $rc = -2;
  466. if ( $form->{dbupdate} ) {
  467. # read update scripts into memory
  468. opendir SQLDIR, "sql/."
  469. or $form->error( __FILE__ . ':' . __LINE__ . ': ' . $! );
  470. @upgradescripts =
  471. sort script_version grep /$form->{dbdriver}-upgrade-.*?\.sql$/,
  472. readdir SQLDIR;
  473. closedir SQLDIR;
  474. }
  475. foreach my $db ( split / /, $form->{dbupdate} ) {
  476. next unless $form->{$db};
  477. # strip db from dataset
  478. $db =~ s/^db//;
  479. &dbconnect_vars( $form, $db );
  480. my $dbh = DBI->connect(
  481. $form->{dbconnect}, $form->{dbuser},
  482. $form->{dbpasswd}, { AutoCommit => 0 }
  483. ) or $form->dberror( __FILE__ . ':' . __LINE__ );
  484. $dbh->{pg_enable_utf8} = 1;
  485. # check version
  486. $query = qq|
  487. SELECT value FROM defaults
  488. WHERE setting_key = 'version'|;
  489. my $sth = $dbh->prepare($query);
  490. # no error check, let it fall through
  491. $sth->execute;
  492. my $version = $sth->fetchrow_array;
  493. $sth->finish;
  494. next unless $version;
  495. $version = calc_version($version);
  496. my $dbversion = calc_version( $form->{dbversion} );
  497. foreach my $upgradescript (@upgradescripts) {
  498. my $a = $upgradescript;
  499. $a =~ s/(^$form->{dbdriver}-upgrade-|\.sql$)//g;
  500. my ( $mindb, $maxdb ) = split /-/, $a;
  501. $mindb = calc_version($mindb);
  502. $maxdb = calc_version($maxdb);
  503. next if ( $version >= $maxdb );
  504. # exit if there is no upgrade script or version == mindb
  505. last if ( $version < $mindb || $version >= $dbversion );
  506. # apply upgrade
  507. $self->process_query( $form, $dbh, "sql/$upgradescript" );
  508. $dbh->commit;
  509. $version = $maxdb;
  510. }
  511. $rc = 0;
  512. $dbh->disconnect;
  513. }
  514. $rc;
  515. }
  516. sub calc_version {
  517. my @v = split /\./, $_[0];
  518. my $version = 0;
  519. my $i;
  520. for ( $i = 0 ; $i <= $#v ; $i++ ) {
  521. $version *= 1000;
  522. $version += $v[$i];
  523. }
  524. return $version;
  525. }
  526. sub script_version {
  527. my ( $my_a, $my_b ) = ( $a, $b );
  528. my ( $a_from, $a_to, $b_from, $b_to );
  529. my ( $res_a, $res_b, $i );
  530. $my_a =~ s/.*-upgrade-//;
  531. $my_a =~ s/.sql$//;
  532. $my_b =~ s/.*-upgrade-//;
  533. $my_b =~ s/.sql$//;
  534. ( $a_from, $a_to ) = split( /-/, $my_a );
  535. ( $b_from, $b_to ) = split( /-/, $my_b );
  536. $res_a = calc_version($a_from);
  537. $res_b = calc_version($b_from);
  538. if ( $res_a == $res_b ) {
  539. $res_a = calc_version($a_to);
  540. $res_b = calc_version($b_to);
  541. }
  542. return $res_a <=> $res_b;
  543. }
  544. sub save_member {
  545. my ($self) = @_;
  546. # replace \r\n with \n
  547. for (qw(address signature)) { $self->{$_} =~ s/\r?\n/\\n/g }
  548. # use central db
  549. my $dbh = ${LedgerSMB::Sysconfig::GLOBALDBH};
  550. #check to see if the user exists already
  551. my $userCheck = $dbh->prepare("SELECT id FROM users WHERE username = ?");
  552. $userCheck->execute( $self->{login} );
  553. my ($userID) = $userCheck->fetchrow_array;
  554. if ( !$self->{dbhost} ) {
  555. $self->{dbhost} = 'localhost';
  556. }
  557. if ( !$self->{dbport} ) {
  558. $self->{dbport} = '5432';
  559. }
  560. my $userConfExists = 0;
  561. if ($userID) {
  562. #got an id, check to see if it's in the users_conf table
  563. my $userConfCheck =
  564. $dbh->prepare("SELECT password, 1 FROM users_conf WHERE id = ?");
  565. $userConfCheck->execute($userID);
  566. ( $oldPassword, $userConfExists ) = $userConfCheck->fetchrow_array;
  567. }
  568. else {
  569. my $userConfAdd = $dbh->prepare("SELECT create_user(?);");
  570. $userConfAdd->execute( $self->{login} );
  571. ($userID) = $userConfAdd->fetchrow_array;
  572. }
  573. if ($userConfExists) {
  574. # for now, this is updating the table directly... ugly
  575. my $userConfUpdate = $dbh->prepare(
  576. "UPDATE users_conf
  577. SET acs = ?, address = ?, businessnumber = ?,
  578. company = ?, countrycode = ?, currency = ?,
  579. dateformat = ?, dbdriver = ?,
  580. dbhost = ?, dbname = ?, dboptions = ?,
  581. dbpasswd = ?, dbport = ?, dbuser = ?,
  582. email = ?, fax = ?, menuwidth = ?,
  583. name = ?, numberformat = ?,
  584. print = ?, printer = ?, role = ?,
  585. sid = ?, signature = ?, stylesheet = ?,
  586. tel = ?, templates = ?, timeout = ?,
  587. vclimit = ?
  588. WHERE id = ?;"
  589. );
  590. $userConfUpdate->execute(
  591. $self->{acs}, $self->{address},
  592. $self->{businessnumber}, $self->{company},
  593. $self->{countrycode}, $self->{currency},
  594. $self->{dateformat}, $self->{dbdriver},
  595. $self->{dbhost}, $self->{dbname},
  596. $self->{dboptions}, $self->{dbpasswd},
  597. $self->{dbport}, $self->{dbuser},
  598. $self->{email}, $self->{fax},
  599. $self->{menuwidth}, $self->{name},
  600. $self->{numberformat}, $self->{print},
  601. $self->{printer}, $self->{role},
  602. $self->{sid}, $self->{signature},
  603. $self->{stylesheet}, $self->{tel},
  604. $self->{templates}, $self->{timeout},
  605. $self->{vclimit}, $userID
  606. );
  607. if ( $oldPassword ne $self->{password} ) {
  608. # if they're supplying a 32 char password that matches their old password
  609. # assume they don't want to change passwords
  610. $userConfUpdate = $dbh->prepare(
  611. "UPDATE users_conf
  612. SET password = md5(?)
  613. WHERE id = ?"
  614. );
  615. $userConfUpdate->execute( $self->{password}, $userID );
  616. }
  617. }
  618. else {
  619. my $userConfInsert = $dbh->prepare(
  620. "INSERT INTO users_conf(acs, address, businessnumber,
  621. company, countrycode, currency,
  622. dateformat, dbdriver,
  623. dbhost, dbname, dboptions, dbpasswd,
  624. dbport, dbuser, email, fax, menuwidth,
  625. name, numberformat, print, printer, role,
  626. sid, signature, stylesheet, tel, templates,
  627. timeout, vclimit, id, password)
  628. VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
  629. ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
  630. ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, md5(?));"
  631. );
  632. $userConfInsert->execute(
  633. $self->{acs}, $self->{address},
  634. $self->{businessnumber}, $self->{company},
  635. $self->{countrycode}, $self->{currency},
  636. $self->{dateformat}, $self->{dbdriver},
  637. $self->{dbhost}, $self->{dbname},
  638. $self->{dboptions}, $self->{dbpasswd},
  639. $self->{dbport}, $self->{dbuser},
  640. $self->{email}, $self->{fax},
  641. $self->{menuwidth}, $self->{name},
  642. $self->{numberformat}, $self->{print},
  643. $self->{printer}, $self->{role},
  644. $self->{sid}, $self->{signature},
  645. $self->{stylesheet}, $self->{tel},
  646. $self->{templates}, $self->{timeout},
  647. $self->{vclimit}, $userID,
  648. $self->{password}
  649. );
  650. }
  651. if ( !$self->{'admin'} ) {
  652. $self->{dbpasswd} =~ s/\\'/'/g;
  653. $self->{dbpasswd} =~ s/\\\\/\\/g;
  654. # format dbconnect and dboptions string
  655. &dbconnect_vars( $self, $self->{dbname} );
  656. # check if login is in database
  657. my $dbh = DBI->connect(
  658. $self->{dbconnect}, $self->{dbuser},
  659. $self->{dbpasswd}, { AutoCommit => 0 }
  660. ) or $self->error($DBI::errstr);
  661. $dbh->{pg_enable_utf8} = 1;
  662. # add login to employees table if it does not exist
  663. my $login = $self->{login};
  664. $login =~ s/@.*//;
  665. my $sth = $dbh->prepare("SELECT id FROM employee WHERE login = ?;");
  666. $sth->execute($login);
  667. my ($id) = $sth->fetchrow_array;
  668. $sth->finish;
  669. my $employeenumber;
  670. my @values;
  671. if ($id) {
  672. $query = qq|UPDATE employee SET
  673. role = ?,
  674. email = ?,
  675. name = ?
  676. WHERE login = ?|;
  677. @values = ( $self->{role}, $self->{email}, $self->{name}, $login );
  678. }
  679. else {
  680. my ($employeenumber) =
  681. Form::update_defaults( "", \%$self, "employeenumber", $dbh );
  682. $query = qq|
  683. INSERT INTO employee
  684. (login, employeenumber, name,
  685. workphone, role, email, sales)
  686. VALUES (?, ?, ?, ?, ?, ?, '1')|;
  687. @values = (
  688. $login, $employeenumber, $self->{name},
  689. $self->{tel}, $self->{role}, $self->{email}
  690. );
  691. }
  692. $sth = $dbh->prepare($query);
  693. $sth->execute(@values);
  694. $dbh->commit;
  695. $dbh->disconnect;
  696. }
  697. }
  698. sub delete_login {
  699. my ( $self, $form ) = @_;
  700. my $dbh = DBI->connect(
  701. $form->{dbconnect}, $form->{dbuser},
  702. $form->{dbpasswd}, { AutoCommit => 0 }
  703. ) or $form->dberror( __FILE__ . ':' . __LINE__ );
  704. $dbh->{pg_enable_utf8} = 1;
  705. my $login = $form->{login};
  706. $login =~ s/@.*//;
  707. my $query = qq|SELECT id FROM employee WHERE login = ?|;
  708. my $sth = $dbh->prepare($query);
  709. $sth->execute($login)
  710. || $form->dberror( __FILE__ . ':' . __LINE__ . ': ' . $query );
  711. my ($id) = $sth->fetchrow_array;
  712. $sth->finish;
  713. my $query = qq|
  714. UPDATE employee
  715. SET login = NULL,
  716. enddate = current_date
  717. WHERE login = ?|;
  718. $sth = $dbh->prepare($query);
  719. $sth->execute($login);
  720. $dbh->commit;
  721. $dbh->disconnect;
  722. }
  723. sub config_vars {
  724. my @conf = qw(acs address businessnumber company countrycode
  725. currency dateformat dbconnect dbdriver dbhost dbname dboptions
  726. dbpasswd dbport dbuser email fax menuwidth name numberformat
  727. password printer role sid signature stylesheet tel templates
  728. timeout vclimit);
  729. @conf;
  730. }
  731. sub error {
  732. my ( $self, $msg ) = @_;
  733. if ( $ENV{GATEWAY_INTERFACE} ) {
  734. print qq|Content-Type: text/html\n\n|
  735. . qq|<body bgcolor=ffffff>\n\n|
  736. . qq|<h2><font color=red>Error!</font></h2>\n|
  737. . qq|<p><b>$msg</b>|;
  738. }
  739. die "Error: $msg\n";
  740. }
  741. 1;