summaryrefslogtreecommitdiff
path: root/IkiWiki/Plugin/websetup.pm
blob: 0a3d90aec0d9654269fd70c63f6e9825f219c6a8 (plain)
  1. #!/usr/bin/perl
  2. package IkiWiki::Plugin::websetup;
  3. use warnings;
  4. use strict;
  5. use IkiWiki 3.00;
  6. sub import {
  7. hook(type => "getsetup", id => "websetup", call => \&getsetup);
  8. hook(type => "checkconfig", id => "websetup", call => \&checkconfig);
  9. hook(type => "sessioncgi", id => "websetup", call => \&sessioncgi);
  10. hook(type => "formbuilder_setup", id => "websetup",
  11. call => \&formbuilder_setup);
  12. }
  13. sub getsetup () {
  14. return
  15. plugin => {
  16. safe => 1,
  17. rebuild => 0,
  18. section => "web",
  19. },
  20. websetup_force_plugins => {
  21. type => "string",
  22. example => [],
  23. description => "list of plugins that cannot be enabled/disabled via the web interface",
  24. safe => 0,
  25. rebuild => 0,
  26. },
  27. websetup_unsafe => {
  28. type => "string",
  29. example => [],
  30. description => "list of additional setup field keys to treat as unsafe",
  31. safe => 0,
  32. rebuild => 0,
  33. },
  34. websetup_show_unsafe => {
  35. type => "boolean",
  36. example => 1,
  37. description => "show unsafe settings, read-only, in web interface?",
  38. safe => 0,
  39. rebuild => 0,
  40. },
  41. }
  42. sub checkconfig () {
  43. if (! exists $config{websetup_show_unsafe}) {
  44. $config{websetup_show_unsafe}=1;
  45. }
  46. }
  47. sub formatexample ($$) {
  48. my $example=shift;
  49. my $value=shift;
  50. if (defined $value && length $value) {
  51. return "";
  52. }
  53. elsif (defined $example && ! ref $example && length $example) {
  54. return "<br/ ><small>Example: <tt>$example</tt></small>";
  55. }
  56. else {
  57. return "";
  58. }
  59. }
  60. sub issafe ($) {
  61. my $key=shift;
  62. return ! grep { $_ eq $key } @{$config{websetup_unsafe}};
  63. }
  64. sub showfields ($$$@) {
  65. my $form=shift;
  66. my $plugin=shift;
  67. my $enabled=shift;
  68. my @show;
  69. my %plugininfo;
  70. while (@_) {
  71. my $key=shift;
  72. my %info=%{shift()};
  73. if ($key eq 'plugin') {
  74. %plugininfo=%info;
  75. next;
  76. }
  77. # skip internal settings
  78. next if defined $info{type} && $info{type} eq "internal";
  79. # XXX hashes not handled yet
  80. next if ref $config{$key} && ref $config{$key} eq 'HASH' || ref $info{example} eq 'HASH';
  81. # maybe skip unsafe settings
  82. next if ! ($config{websetup_show_unsafe} && $config{websetup_advanced}) &&
  83. (! $info{safe} || ! issafe($key));
  84. # maybe skip advanced settings
  85. next if $info{advanced} && ! $config{websetup_advanced};
  86. # these are handled specially, so don't show
  87. next if $key eq 'add_plugins' || $key eq 'disable_plugins';
  88. push @show, $key, \%info;
  89. }
  90. my $section=defined $plugin
  91. ? sprintf(gettext("%s plugin:"), $plugininfo{section})." ".$plugin
  92. : "main";
  93. my %enabledfields;
  94. my $shownfields=0;
  95. my $plugin_forced=defined $plugin && (! $plugininfo{safe} ||
  96. (exists $config{websetup_force_plugins} && grep { $_ eq $plugin } @{$config{websetup_force_plugins}}));
  97. if ($plugin_forced && ! $enabled) {
  98. # plugin is forced disabled, so skip its settings
  99. @show=();
  100. }
  101. my $section_fieldset;
  102. if (defined $plugin) {
  103. # Define the combined fieldset for the plugin's section.
  104. # This ensures that this fieldset comes first.
  105. $section_fieldset=sprintf(gettext("%s plugins"), $plugininfo{section});
  106. $form->field(name => "placeholder.$plugininfo{section}",
  107. type => "hidden",
  108. fieldset => $section_fieldset);
  109. }
  110. # show plugin toggle
  111. if (defined $plugin && (! $plugin_forced || $config{websetup_advanced})) {
  112. my $name="enable.$plugin";
  113. $form->field(
  114. name => $name,
  115. label => "",
  116. type => "checkbox",
  117. fieldset => $section,
  118. options => [ [ 1 => sprintf(gettext("enable %s?"), $plugin) ]]
  119. );
  120. if (! $form->submitted) {
  121. $form->field(name => $name, value => $enabled);
  122. }
  123. if ($plugin_forced) {
  124. $form->field(name => $name, disabled => 1);
  125. }
  126. else {
  127. $enabledfields{$name}=[$name, \%plugininfo];
  128. }
  129. }
  130. # show plugin settings
  131. while (@show) {
  132. my $key=shift @show;
  133. my %info=%{shift @show};
  134. my $description=$info{description};
  135. if (exists $info{link} && length $info{link}) {
  136. if ($info{link} =~ /^\w+:\/\//) {
  137. $description="<a href=\"$info{link}\">$description</a>";
  138. }
  139. else {
  140. $description=htmllink("", "", $info{link}, noimageinline => 1, linktext => $description);
  141. }
  142. }
  143. # multiple plugins can have the same field
  144. my $name=defined $plugin ? $plugin.".".$key : $section.".".$key;
  145. my $value=$config{$key};
  146. if (! defined $value) {
  147. $value="";
  148. }
  149. if (ref $value eq 'ARRAY' || ref $info{example} eq 'ARRAY') {
  150. $value=[(ref $value eq 'ARRAY' ? map { Encode::encode_utf8($_) } @{$value} : "")];
  151. push @$value, "", "" if $info{safe} && issafe($key); # blank items for expansion
  152. }
  153. else {
  154. $value=Encode::encode_utf8($value);
  155. }
  156. if ($info{type} eq "string") {
  157. $form->field(
  158. name => $name,
  159. label => $description,
  160. comment => formatexample($info{example}, $value),
  161. type => "text",
  162. value => $value,
  163. size => 60,
  164. fieldset => $section,
  165. );
  166. }
  167. elsif ($info{type} eq "pagespec") {
  168. $form->field(
  169. name => $name,
  170. label => $description,
  171. comment => formatexample($info{example}, $value),
  172. type => "text",
  173. value => $value,
  174. size => 60,
  175. validate => \&IkiWiki::pagespec_valid,
  176. fieldset => $section,
  177. );
  178. }
  179. elsif ($info{type} eq "integer") {
  180. $form->field(
  181. name => $name,
  182. label => $description,
  183. comment => formatexample($info{example}, $value),
  184. type => "text",
  185. value => $value,
  186. size => 5,
  187. validate => '/^[0-9]+$/',
  188. fieldset => $section,
  189. );
  190. }
  191. elsif ($info{type} eq "boolean") {
  192. $form->field(
  193. name => $name,
  194. label => "",
  195. type => "checkbox",
  196. options => [ [ 1 => $description ] ],
  197. fieldset => $section,
  198. );
  199. if (! $form->submitted ||
  200. ($info{advanced} && $form->submitted eq 'Advanced Mode')) {
  201. $form->field(name => $name, value => $value);
  202. }
  203. }
  204. if (! $info{safe} || ! issafe($key)) {
  205. $form->field(name => $name, disabled => 1);
  206. }
  207. else {
  208. $enabledfields{$name}=[$key, \%info];
  209. }
  210. $shownfields++;
  211. }
  212. # if no fields were shown for the plugin, drop it into a combined
  213. # fieldset for its section
  214. if (defined $plugin && (! $plugin_forced || $config{websetup_advanced}) &&
  215. ! $shownfields) {
  216. $form->field(name => "enable.$plugin", fieldset => $section_fieldset);
  217. }
  218. return %enabledfields;
  219. }
  220. sub enable_plugin ($) {
  221. my $plugin=shift;
  222. $config{disable_plugins}=[grep { $_ ne $plugin } @{$config{disable_plugins}}];
  223. push @{$config{add_plugins}}, $plugin;
  224. }
  225. sub disable_plugin ($) {
  226. my $plugin=shift;
  227. $config{add_plugins}=[grep { $_ ne $plugin } @{$config{add_plugins}}];
  228. push @{$config{disable_plugins}}, $plugin;
  229. }
  230. sub showform ($$) {
  231. my $cgi=shift;
  232. my $session=shift;
  233. IkiWiki::needsignin($cgi, $session);
  234. if (! defined $session->param("name") ||
  235. ! IkiWiki::is_admin($session->param("name"))) {
  236. error(gettext("you are not logged in as an admin"));
  237. }
  238. if (! exists $config{setupfile}) {
  239. error(gettext("setup file for this wiki is not known"));
  240. }
  241. eval q{use CGI::FormBuilder};
  242. error($@) if $@;
  243. my $form = CGI::FormBuilder->new(
  244. title => "setup",
  245. name => "setup",
  246. header => 0,
  247. charset => "utf-8",
  248. method => 'POST',
  249. javascript => 0,
  250. reset => 1,
  251. params => $cgi,
  252. fieldsets => [
  253. [main => gettext("main")],
  254. ],
  255. action => IkiWiki::cgiurl(),
  256. template => {type => 'div'},
  257. stylesheet => 1,
  258. );
  259. $form->field(name => "do", type => "hidden", value => "setup",
  260. force => 1);
  261. $form->field(name => "rebuild_asked", type => "hidden");
  262. $form->field(name => "showadvanced", type => "hidden");
  263. if ($form->submitted eq 'Basic Mode') {
  264. $form->field(name => "showadvanced", type => "hidden",
  265. value => 0, force => 1);
  266. }
  267. elsif ($form->submitted eq 'Advanced Mode') {
  268. $form->field(name => "showadvanced", type => "hidden",
  269. value => 1, force => 1);
  270. }
  271. my $advancedtoggle;
  272. if ($form->field("showadvanced")) {
  273. $config{websetup_advanced}=1;
  274. $advancedtoggle="Basic Mode";
  275. }
  276. else {
  277. $config{websetup_advanced}=0;
  278. $advancedtoggle="Advanced Mode";
  279. }
  280. my $buttons=["Save Setup", $advancedtoggle, "Cancel"];
  281. IkiWiki::decode_form_utf8($form);
  282. IkiWiki::run_hooks(formbuilder_setup => sub {
  283. shift->(form => $form, cgi => $cgi, session => $session,
  284. buttons => $buttons);
  285. });
  286. my %fields=showfields($form, undef, undef, IkiWiki::getsetup());
  287. # record all currently enabled plugins before all are loaded
  288. my %enabled_plugins=%IkiWiki::loaded_plugins;
  289. # per-plugin setup
  290. require IkiWiki::Setup;
  291. foreach my $pair (IkiWiki::Setup::getsetup()) {
  292. my $plugin=$pair->[0];
  293. my $setup=$pair->[1];
  294. my %shown=showfields($form, $plugin, $enabled_plugins{$plugin}, @{$setup});
  295. if (%shown) {
  296. $fields{$_}=$shown{$_} foreach keys %shown;
  297. }
  298. }
  299. IkiWiki::decode_form_utf8($form);
  300. if ($form->submitted eq "Cancel") {
  301. IkiWiki::redirect($cgi, IkiWiki::baseurl(undef));
  302. return;
  303. }
  304. elsif (($form->submitted eq 'Save Setup' || $form->submitted eq 'Rebuild Wiki') && $form->validate) {
  305. # Push values from form into %config, avoiding unnecessary
  306. # changes, and keeping track of which changes need a
  307. # rebuild.
  308. my %rebuild;
  309. foreach my $field (keys %fields) {
  310. my %info=%{$fields{$field}->[1]};
  311. my $key=$fields{$field}->[0];
  312. my @value=$form->field($field);
  313. if (! @value) {
  314. @value=0;
  315. }
  316. if (! $info{safe} || ! issafe($key)) {
  317. error("unsafe field $key"); # should never happen
  318. }
  319. if (exists $info{rebuild} &&
  320. ($info{rebuild} || ! defined $info{rebuild})) {
  321. $rebuild{$field}=$info{rebuild};
  322. }
  323. if ($field=~/^enable\.(.*)/) {
  324. my $plugin=$1;
  325. $value[0]=0 if ! length $value[0];
  326. if ($value[0] != exists $enabled_plugins{$plugin}) {
  327. if ($value[0]) {
  328. enable_plugin($plugin);
  329. }
  330. else {
  331. disable_plugin($plugin);
  332. }
  333. }
  334. else {
  335. delete $rebuild{$field};
  336. }
  337. next;
  338. }
  339. if (ref $config{$key} eq "ARRAY" || ref $info{example} eq "ARRAY") {
  340. @value=sort grep { length $_ } @value;
  341. my @oldvalue=sort grep { length $_ }
  342. (defined $config{$key} ? @{$config{$key}} : ());
  343. my $same=(@oldvalue) == (@value);
  344. for (my $x=0; $same && $x < @value; $x++) {
  345. $same=0 if $value[$x] ne $oldvalue[$x];
  346. }
  347. if ($same) {
  348. delete $rebuild{$field};
  349. }
  350. else {
  351. $config{$key}=\@value;
  352. }
  353. }
  354. elsif (ref $config{$key} || ref $info{example}) {
  355. error("complex field $key"); # should never happen
  356. }
  357. else {
  358. if (defined $config{$key} && $config{$key} eq $value[0]) {
  359. delete $rebuild{$field};
  360. }
  361. elsif (! defined $config{$key} && ! length $value[0]) {
  362. delete $rebuild{$field};
  363. }
  364. elsif ((! defined $config{$key} || ! $config{$key}) &&
  365. ! $value[0] && $info{type} eq "boolean") {
  366. delete $rebuild{$field};
  367. }
  368. else {
  369. $config{$key}=$value[0];
  370. }
  371. }
  372. }
  373. if (%rebuild && ! $form->field("rebuild_asked")) {
  374. my $required=0;
  375. foreach my $field ($form->field) {
  376. $required=1 if $rebuild{$field};
  377. next if exists $rebuild{$field};
  378. $form->field(name => $field, type => "hidden");
  379. }
  380. if ($required) {
  381. $form->text(gettext("The configuration changes shown below require a wiki rebuild to take effect."));
  382. $buttons=["Rebuild Wiki", "Cancel"];
  383. }
  384. else {
  385. $form->text(gettext("For the configuration changes shown below to fully take effect, you may need to rebuild the wiki."));
  386. $buttons=["Rebuild Wiki", "Save Setup", "Cancel"];
  387. }
  388. $form->field(name => "rebuild_asked", value => 1, force => 1);
  389. $form->reset(0); # doesn't really make sense here
  390. }
  391. else {
  392. my $oldsetup=readfile($config{setupfile});
  393. IkiWiki::Setup::dump($config{setupfile});
  394. IkiWiki::saveindex();
  395. IkiWiki::unlockwiki();
  396. # Print the top part of a standard cgitemplate,
  397. # then show the rebuild or refresh, live.
  398. my $divider="\0";
  399. my $html=IkiWiki::cgitemplate($cgi, "setup", $divider);
  400. IkiWiki::printheader($session);
  401. my ($head, $tail)=split($divider, $html, 2);
  402. print $head."<pre>\n";
  403. my @command;
  404. if ($form->submitted eq 'Rebuild Wiki') {
  405. @command=("ikiwiki", "-setup", $config{setupfile},
  406. "-rebuild", "-v");
  407. }
  408. else {
  409. @command=("ikiwiki", "-setup", $config{setupfile},
  410. "-refresh", "-wrappers", "-v");
  411. }
  412. close STDERR;
  413. open(STDERR, ">&STDOUT");
  414. my $ret=system(@command);
  415. print "\n<\/pre>";
  416. if ($ret != 0) {
  417. print '<p class="error">'.
  418. sprintf(gettext("Error: %s exited nonzero (%s). Discarding setup changes."),
  419. join(" ", @command), $ret).
  420. '</p>';
  421. open(OUT, ">", $config{setupfile}) || error("$config{setupfile}: $!");
  422. print OUT Encode::encode_utf8($oldsetup);
  423. close OUT;
  424. }
  425. print $tail;
  426. exit 0;
  427. }
  428. }
  429. IkiWiki::showform($form, $buttons, $session, $cgi);
  430. }
  431. sub sessioncgi ($$) {
  432. my $cgi=shift;
  433. my $session=shift;
  434. if ($cgi->param("do") eq "setup") {
  435. showform($cgi, $session);
  436. exit;
  437. }
  438. }
  439. sub formbuilder_setup (@) {
  440. my %params=@_;
  441. my $form=$params{form};
  442. if ($form->title eq "preferences" &&
  443. IkiWiki::is_admin($params{session}->param("name"))) {
  444. push @{$params{buttons}}, "Setup";
  445. if ($form->submitted && $form->submitted eq "Setup") {
  446. showform($params{cgi}, $params{session});
  447. exit;
  448. }
  449. }
  450. }
  451. 1
l kwd">is_nan) {
  • $dbh->rollback;
  • return;
  • }
  • # save printed and queued
  • $form->save_status($dbh);
  • my %audittrail = (
  • tablename => $table,
  • reference => $form->{invnumber},
  • formname => 'transaction',
  • action => 'posted',
  • id => $form->{id}
  • );
  • $form->audittrail( $dbh, "", \%audittrail );
  • $form->save_recurring( $dbh, $myconfig );
  • my $rc = $dbh->commit;
  • $rc;
  • }
  • sub delete_transaction {
  • my ( $self, $myconfig, $form ) = @_;
  • # connect to database, turn AutoCommit off
  • my $dbh = $form->{dbh};
  • my $table = ( $form->{vc} eq 'customer' ) ? 'ar' : 'ap';
  • my %audittrail = (
  • tablename => $table,
  • reference => $form->{invnumber},
  • formname => 'transaction',
  • action => 'deleted',
  • id => $form->{id}
  • );
  • $form->audittrail( $dbh, "", \%audittrail );
  • my $query = qq|DELETE FROM $table WHERE id = $form->{id}|;
  • $dbh->do($query) || $form->dberror($query);
  • $query = qq|DELETE FROM acc_trans WHERE trans_id = ?|;
  • $dbh->prepare($query)->execute( $form->{id} ) || $form->dberror($query);
  • # get spool files
  • $query = qq|SELECT spoolfile
  • FROM status
  • WHERE trans_id = ?
  • AND spoolfile IS NOT NULL|;
  • my $sth = $dbh->prepare($query);
  • $sth->execute( $form->{id} ) || $form->dberror($query);
  • my $spoolfile;
  • my @spoolfiles = ();
  • while ( ($spoolfile) = $sth->fetchrow_array ) {
  • push @spoolfiles, $spoolfile;
  • }
  • $sth->finish;
  • $query = qq|DELETE FROM status WHERE trans_id = ?|;
  • $dbh->prepare($query)->execute( $form->{id} ) || $form->dberror($query);
  • # commit
  • my $rc = $dbh->commit;
  • if ($rc) {
  • foreach $spoolfile (@spoolfiles) {
  • unlink "${LedgerSMB::Sysconfig::spool}/$spoolfile" if $spoolfile;
  • }
  • }
  • $rc;
  • }
  • sub transactions {
  • my ( $self, $myconfig, $form ) = @_;
  • # connect to database
  • my $dbh = $form->{dbh};
  • my $null;
  • my $var;
  • my $paid = "a.paid";
  • my $ml = 1;
  • my $ARAP = 'AR';
  • my $table = 'ar';
  • my $buysell = 'buy';
  • my $acc_trans_join;
  • my $acc_trans_flds;
  • if ( $form->{vc} eq 'vendor' ) {
  • $ml = -1;
  • $ARAP = 'AP';
  • $table = 'ap';
  • $buysell = 'sell';
  • }
  • ( $form->{transdatefrom}, $form->{transdateto} ) =
  • $form->from_to( $form->{year}, $form->{month}, $form->{interval} )
  • if $form->{year} && $form->{month};
  • my @paidargs = ();
  • if ( $form->{outstanding} ) {
  • $paid = qq|
  • SELECT SUM(ac.amount) * -1 * $ml
  • FROM acc_trans ac
  • JOIN chart c ON (c.id = ac.chart_id)
  • WHERE ac.trans_id = a.id
  • AND (c.link LIKE '%${ARAP}_paid%'
  • OR c.link = '')|;
  • if ( $form->{transdateto} ) {
  • $paid .= qq|
  • AND ac.transdate <= ?|;
  • push @paidargs, $form->{transdateto};
  • }
  • $form->{summary} = 1;
  • }
  • if ( !$form->{summary} ) {
  • $acc_trans_flds = qq|
  • , c.accno, ac.source,
  • pr.projectnumber, ac.memo AS description,
  • ac.amount AS linetotal,
  • i.description AS linedescription|;
  • $acc_trans_join = qq|
  • JOIN acc_trans ac ON (a.id = ac.trans_id)
  • JOIN chart c ON (c.id = ac.chart_id)
  • LEFT JOIN project pr ON (pr.id = ac.project_id)
  • LEFT JOIN invoice i ON (i.id = ac.invoice_id)|;
  • }
  • my $query = qq|
  • SELECT a.id, a.invnumber, a.ordnumber, a.transdate,
  • a.duedate, a.netamount, a.amount, ($paid) AS paid,
  • a.invoice, a.datepaid, a.terms, a.notes,
  • a.shipvia, a.shippingpoint, e.name AS employee,
  • vc.name,
  • a.$form->{vc}_id, a.till, m.name AS manager, a.curr,
  • ex.$buysell AS exchangerate,
  • d.description AS department,
  • a.ponumber $acc_trans_flds
  • FROM $table a
  • JOIN $form->{vc} vc ON (a.$form->{vc}_id = vc.id)
  • LEFT JOIN employee e ON (a.employee_id = e.id)
  • LEFT JOIN employee m ON (e.managerid = m.id)
  • LEFT JOIN exchangerate ex ON (ex.curr = a.curr
  • AND ex.transdate = a.transdate)
  • LEFT JOIN department d ON (a.department_id = d.id)
  • $acc_trans_join|;
  • my %ordinal = (
  • id => 1,
  • invnumber => 2,
  • ordnumber => 3,
  • transdate => 4,
  • duedate => 5,
  • datepaid => 10,
  • shipvia => 13,
  • shippingpoint => 14,
  • employee => 15,
  • name => 16,
  • manager => 19,
  • curr => 20,
  • department => 22,
  • ponumber => 23,
  • accno => 24,
  • source => 25,
  • project => 26,
  • description => 27
  • );
  • my @a = ( transdate, invnumber, name );
  • push @a, "employee" if $form->{l_employee};
  • push @a, "manager" if $form->{l_manager};
  • my $sortorder = $form->sort_order( \@a, \%ordinal );
  • my $where = "1 = 1";
  • if ( $form->{"$form->{vc}_id"} ) {
  • $where .= qq| AND a.$form->{vc}_id = $form->{"$form->{vc}_id"}|;
  • }
  • else {
  • if ( $form->{ $form->{vc} } ) {
  • $var = $dbh->quote( $form->like( lc $form->{ $form->{vc} } ) );
  • $where .= " AND lower(vc.name) LIKE $var";
  • }
  • }
  • for (qw(department employee)) {
  • if ( $form->{$_} ) {
  • ( $null, $var ) = split /--/, $form->{$_};
  • $var = $dbh->quote($var);
  • $where .= " AND a.${_}_id = $var";
  • }
  • }
  • for (qw(invnumber ordnumber)) {
  • if ( $form->{$_} ) {
  • $var = $dbh->quote( $form->like( lc $form->{$_} ) );
  • $where .= " AND lower(a.$_) LIKE $var";
  • $form->{open} = $form->{closed} = 0;
  • }
  • }
  • if ( $form->{partsid} ) {
  • my $partsid = $dbh->quote( $form->{partsid} );
  • $where .= " AND a.id IN (select trans_id FROM invoice
  • WHERE parts_id = $partsid)";
  • }
  • for (qw(ponumber shipvia notes)) {
  • if ( $form->{$_} ) {
  • $var = $dbh->quote( $form->like( lc $form->{$_} ) );
  • $where .= " AND lower(a.$_) LIKE $var";
  • }
  • }
  • if ( $form->{description} ) {
  • if ($acc_trans_flds) {
  • $var = $dbh->quote( $form->like( lc $form->{description} ) );
  • $where .= " AND lower(ac.memo) LIKE $var
  • OR lower(i.description) LIKE $var";
  • }
  • else {
  • $where .= " AND a.id = 0";
  • }
  • }
  • if ( $form->{source} ) {
  • if ($acc_trans_flds) {
  • $var = $dbh->quote( $form->like( lc $form->{source} ) );
  • $where .= " AND lower(ac.source) LIKE $var";
  • }
  • else {
  • $where .= " AND a.id = 0";
  • }
  • }
  • my $transdatefrom = $dbh->quote( $form->{transdatefrom} );
  • $where .= " AND a.transdate >= $transdatefrom"
  • if $form->{transdatefrom};
  • my $transdateto = $dbh->quote( $form->{transdateto} );
  • $where .= " AND a.transdate <= $transdateto" if $form->{transdateto};
  • if ( $form->{open} || $form->{closed} ) {
  • unless ( $form->{open} && $form->{closed} ) {
  • $where .= " AND a.amount != a.paid" if ( $form->{open} );
  • $where .= " AND a.amount = a.paid" if ( $form->{closed} );
  • }
  • }
  • if ( $form->{till} ne "" ) {
  • $where .= " AND a.invoice = '1'
  • AND a.till = $form->{till}";
  • if ( $myconfig->{role} eq 'user' ) {
  • my $login = $dbh->quote( $form->{login} );
  • $where .= " AND e.login = $login";
  • }
  • }
  • if ( $form->{$ARAP} ) {
  • my ($accno) = split /--/, $form->{$ARAP};
  • $accno = $dbh->quote($accno);
  • $where .= qq|
  • AND a.id IN (SELECT ac.trans_id
  • FROM acc_trans ac
  • JOIN chart c ON (c.id = ac.chart_id)
  • WHERE a.id = ac.trans_id
  • AND c.accno = $accno)|;
  • }
  • if ( $form->{description} ) {
  • $var = $dbh->quote( $form->like( lc $form->{description} ) );
  • $where .= qq|
  • AND (a.id IN (SELECT DISTINCT trans_id
  • FROM acc_trans
  • WHERE lower(memo) LIKE $var)
  • OR a.id IN
  • (SELECT DISTINCT trans_id
  • FROM invoice
  • WHERE lower(description)
  • LIKE $var))|;
  • }
  • $query .= "WHERE $where
  • ORDER BY $sortorder";
  • my $sth = $dbh->prepare($query);
  • $sth->execute(@paidargs) || $form->dberror($query);
  • while ( my $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  • $form->db_parse_numeric(sth => $sth, hashref => $ref);
  • $ref->{exchangerate} = 1 unless $ref->{exchangerate};
  • if ( $ref->{linetotal} <= 0 ) {
  • $ref->{debit} = $ref->{linetotal} * -1;
  • $ref->{credit} = 0;
  • }
  • else {
  • $ref->{debit} = 0;
  • $ref->{credit} = $ref->{linetotal};
  • }
  • if ( $ref->{invoice} ) {
  • $ref->{description} ||= $ref->{linedescription};
  • }
  • if ( $form->{outstanding} ) {
  • next
  • if $form->round_amount( $ref->{amount}, 2 ) ==
  • $form->round_amount( $ref->{paid}, 2 );
  • }
  • push @{ $form->{transactions} }, $ref;
  • }
  • $sth->finish;
  • $dbh->commit;
  • }
  • # this is used in IS, IR to retrieve the name
  • sub get_name {
  • my ( $self, $myconfig, $form ) = @_;
  • # sanitize $form->{vc}
  • if ( $form->{vc} ne 'customer' ) {
  • $form->{vc} = 'vendor';
  • }
  • else {
  • $form->{vc} = 'customer';
  • }
  • # connect to database
  • my $dbh = $form->{dbh};
  • my $dateformat = $myconfig->{dateformat};
  • if ( $myconfig->{dateformat} !~ /^y/ ) {
  • my @a = split /\W/, $form->{transdate};
  • $dateformat .= "yy" if ( length $a[2] > 2 );
  • }
  • if ( $form->{transdate} !~ /\W/ ) {
  • $dateformat = 'yyyymmdd';
  • }
  • my $duedate;
  • $dateformat = $dbh->quote($dateformat);
  • my $tdate = $dbh->quote( $form->{transdate} );
  • $duedate = ( $form->{transdate} )
  • ? "to_date($tdate, $dateformat)
  • + c.terms"
  • : "current_date + c.terms";
  • $form->{"$form->{vc}_id"} *= 1;
  • # get customer/vendor
  • my $query = qq|
  • SELECT c.name AS $form->{vc}, c.discount, c.creditlimit,
  • c.terms, c.email, c.cc, c.bcc, c.taxincluded,
  • c.address1, c.address2, c.city, c.state,
  • c.zipcode, c.country, c.curr AS currency,
  • c.language_code, $duedate AS duedate,
  • c.notes AS intnotes,
  • b.discount AS tradediscount,
  • b.description AS business,
  • e.name AS employee, e.id AS employee_id
  • FROM $form->{vc} c
  • LEFT JOIN business b ON (b.id = c.business_id)
  • LEFT JOIN employee e ON (e.id = c.employee_id)
  • WHERE c.id = ?|;
  • @queryargs = ( $form->{"$form->{vc}_id"} );
  • my $sth = $dbh->prepare($query);
  • $sth->execute(@queryargs) || $form->dberror($query);
  • $ref = $sth->fetchrow_hashref(NAME_lc);
  • $form->db_parse_numeric(sth => $sth, hashref => $ref);
  • if ( $form->{id} ) {
  • for (qw(currency employee employee_id intnotes)) {
  • delete $ref->{$_};
  • }
  • }
  • for ( keys %$ref ) { $form->{$_} = $ref->{$_} }
  • $sth->finish;
  • my $buysell = ( $form->{vc} eq 'customer' ) ? "buy" : "sell";
  • # if no currency use defaultcurrency
  • $form->{currency} =
  • ( $form->{currency} )
  • ? $form->{currency}
  • : $form->{defaultcurrency};
  • $form->{exchangerate} = 0
  • if $form->{currency} eq $form->{defaultcurrency};
  • if ( $form->{transdate}
  • && ( $form->{currency} ne $form->{defaultcurrency} ) )
  • {
  • $form->{exchangerate} =
  • $form->get_exchangerate( $dbh, $form->{currency}, $form->{transdate},
  • $buysell );
  • }
  • $form->{forex} = $form->{exchangerate};
  • # if no employee, default to login
  • ( $form->{employee}, $form->{employee_id} ) = $form->get_employee($dbh)
  • unless $form->{employee_id};
  • my $arap = ( $form->{vc} eq 'customer' ) ? 'ar' : 'ap';
  • my $ARAP = uc $arap;
  • $form->{creditremaining} = $form->{creditlimit};
  • $query = qq|
  • SELECT SUM(amount - paid)
  • FROM $arap
  • WHERE $form->{vc}_id = ?|;
  • $sth = $dbh->prepare($query);
  • $sth->execute( $form->{"$form->{vc}_id"} )
  • || $form->dberror($query);
  • ( $form->{creditremaining} ) -= $sth->fetchrow_array;
  • $sth->finish;
  • if ( $form->{vc} ne "customer" ) {
  • $form->{vc} = 'vendor';
  • }
  • $query = qq|
  • SELECT o.amount, (SELECT e.$buysell FROM exchangerate e
  • WHERE e.curr = o.curr
  • AND e.transdate = o.transdate)
  • FROM oe o
  • WHERE o.$form->{vc}_id = ?
  • AND o.quotation = '0' AND o.closed = '0'|;
  • $sth = $dbh->prepare($query);
  • $sth->execute( $form->{"$form->{vc}_id"} ) || $form->dberror($query);
  • while ( my @ref = $sth->fetchrow_array ) {
  • $form->db_parse_numeric(sth => $sth, arrayref => \@ref);
  • my ($amount, $exch) = @ref;
  • $exch = 1 unless $exch;
  • $form->{creditremaining} -= $amount * $exch;
  • }
  • $sth->finish;
  • # get shipto if we did not converted an order or invoice
  • if ( !$form->{shipto} ) {
  • for (
  • qw(shiptoname shiptoaddress1 shiptoaddress2
  • shiptocity shiptostate shiptozipcode
  • shiptocountry shiptocontact shiptophone
  • shiptofax shiptoemail)
  • )
  • {
  • delete $form->{$_};
  • }
  • ## needs fixing (SELECT *)
  • $query = qq|
  • SELECT *
  • FROM shipto
  • WHERE trans_id = $form->{"$form->{vc}_id"}|;
  • $sth = $dbh->prepare($query);
  • $sth->execute || $form->dberror($query);
  • $ref = $sth->fetchrow_hashref(NAME_lc);
  • for ( keys %$ref ) { $form->{$_} = $ref->{$_} }
  • $sth->finish;
  • }
  • # get taxes
  • $query = qq|
  • SELECT c.accno
  • FROM chart c
  • JOIN $form->{vc}tax ct ON (ct.chart_id = c.id)
  • WHERE ct.$form->{vc}_id = ?|;
  • $sth = $dbh->prepare($query);
  • $sth->execute( $form->{"$form->{vc}_id"} ) || $form->dberror($query);
  • my %tax;
  • while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  • $tax{ $ref->{accno} } = 1;
  • }
  • $sth->finish;
  • $transdate = $dbh->quote( $form->{transdate} );
  • my $where = qq|AND (t.validto >= $transdate OR t.validto IS NULL)|
  • if $form->{transdate};
  • # get tax rates and description
  • $query = qq|
  • SELECT c.accno, c.description, t.rate, t.taxnumber
  • FROM chart c
  • JOIN tax t ON (c.id = t.chart_id)
  • WHERE c.link LIKE '%${ARAP}_tax%'
  • $where
  • ORDER BY accno, validto|;
  • $sth = $dbh->prepare($query);
  • $sth->execute || $form->dberror($query);
  • $form->{taxaccounts} = "";
  • my %a = ();
  • while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  • $form->db_parse_numeric(sth => $sth, hashref => $hashref);
  • if ( $tax{ $ref->{accno} } ) {
  • if ( not exists $a{ $ref->{accno} } ) {
  • for (qw(rate description taxnumber)) {
  • $form->{"$ref->{accno}_$_"} = $ref->{$_};
  • }
  • $form->{taxaccounts} .= "$ref->{accno} ";
  • $a{ $ref->{accno} } = 1;
  • }
  • }
  • }
  • $sth->finish;
  • chop $form->{taxaccounts};
  • # setup last accounts used for this customer/vendor
  • if ( !$form->{id} && $form->{type} !~ /_(order|quotation)/ ) {
  • $query = qq|
  • SELECT c.accno, c.description, c.link,
  • c.category,
  • ac.project_id,
  • a.department_id
  • FROM chart c
  • JOIN acc_trans ac ON (ac.chart_id = c.id)
  • JOIN $arap a ON (a.id = ac.trans_id)
  • WHERE a.$form->{vc}_id = ?
  • AND a.id = (SELECT max(id)
  • FROM $arap
  • WHERE $form->{vc}_id =
  • ?)
  • |;
  • $sth = $dbh->prepare($query);
  • $sth->execute( $form->{"$form->{vc}_id"}, $form->{"$form->{vc}_id"} )
  • || $form->dberror($query);
  • my $i = 0;
  • while ( $ref = $sth->fetchrow_hashref(NAME_lc) ) {
  • $form->{department_id} = $ref->{department_id};
  • if ( $ref->{link} =~ /_amount/ ) {
  • $i++;
  • $form->{"$form->{ARAP}_amount_$i"} =
  • "$ref->{accno}--$ref->{description}"
  • if $ref->{accno};
  • $form->{"projectnumber_$i"} =
  • "$ref->{projectnumber}--" . "$ref->{project_id}"
  • if $ref->{project_id};
  • }
  • if ( $ref->{link} eq $form->{ARAP} ) {
  • $form->{ $form->{ARAP} } = $form->{"$form->{ARAP}_1"} =
  • "$ref->{accno}--" . "$ref->{description}"
  • if $ref->{accno};
  • }
  • }
  • $sth->finish;
  • $query = "select description from department where id = ?";
  • $sth = $dbh->prepare($query);
  • $sth->execute($form->{department_id});
  • ($form->{department}) = $sth->fetchrow_array;
  • $form->{rowcount} = $i if ( $i && !$form->{type} );
  • }
  • $dbh->commit;
  • }
  • 1;