Changeset 2296 for trunk/LATMOS-Accounts/lib

Timestamp:

11/13/19 19:37:12 (5 years ago)

Author:

nanardon

Message:

Add Employmentsummary objects (summarize user activity)

Location:

trunk/LATMOS-Accounts/lib/LATMOS/Accounts/Bases

Files:

• Sql.pm (modified) (1 diff)
• Sql/Employment.pm (modified) (2 diffs)
• Sql/Employmentsummary.pm (added)
• Sql/User.pm (modified) (3 diffs)
• Sql/upgrade.pm (modified) (1 diff)

trunk/LATMOS-Accounts/lib/LATMOS/Accounts/Bases/Sql.pm

@@ -18 +18 @@
 our $VERSION = (q$Rev$ =~ /^Rev: (\d+) /)[0];
 
-sub SCHEMA_VERSION { 35 };
+sub SCHEMA_VERSION { 36 };
 
 =head1 NAME

trunk/LATMOS-Accounts/lib/LATMOS/Accounts/Bases/Sql/Employment.pm

@@ -345 +345 @@
     if ($res) {
         my $ouser = $base->get_object('user', $user);
+        $ouser->StoreEmployementSummary;
         $ouser->applyCurrentEmployment;
     }
@@ -362 +363 @@
         };
 
+        $user->StoreEmployementSummary;
         $user->applyCurrentEmployment;
     }

trunk/LATMOS-Accounts/lib/LATMOS/Accounts/Bases/Sql/User.pm

@@ -2107 +2107 @@
     });
     $sth->execute($self->id);
+    warn $self->id;
     my @values;
     my $prevContrat = ':';
@@ -2117 +2118 @@
         my $curContrat  = $Emp->get_attributes('contratType') || '';
         my $DTfirstday  = DateTime::Format::Pg->parse_datetime( $Emp->get_attributes('firstday') );
+
+        warn $DTfirstday->ymd('-');
 
         if ($curContrat ne $prevContrat) {
@@ -2150 +2153 @@
 
     @values
+}
+
+=head2 StoreEmployementSummary
+
+Store into database the employment summary
+
+=cut
+
+sub StoreEmployementSummary {
+    my ( $self ) = @_;
+
+    my $stu = $self->base->db->prepare_cached(q{
+        UPDATE employmentsum set
+            "user" = ?,
+            firstday = ?,
+            lastday  = ?,
+            contrattype = ?,
+            nodelete = 't'
+        WHERE name = ?
+    });
+    my $sti = $self->base->db->prepare_cached(q{
+        INSERT INTO employmentsum
+        ("user", firstday, lastday, contrattype, name, nodelete)
+        VALUES
+        (?, ?, ?, ?, ?, 1)
+    });
+
+    my @values = $self->EmploymentSummary;
+
+    my @names;
+
+    foreach (@values) {
+        my $name = $self->id . '-' . $_->{firstday};
+        my $res = $stu->execute($self->id, $_->{firstday}, $_->{lastday}, $_->{contrattype}, $name);
+        if ($res == 0) {
+            $sti->execute($self->id, $_->{firstday}, $_->{lastday}, $_->{contrattype}, $name);
+        }
+        push(@names, $name);
+    }
+
+    my $std = $self->base->db->prepare_cached(q{
+        DELETE FROM employmentsum WHERE "user" = ? AND name != ALL (?)
+    });
+    $std->execute($self->id, \@names);
 }
 

trunk/LATMOS-Accounts/lib/LATMOS/Accounts/Bases/Sql/upgrade.pm

@@ -1269 +1269 @@
             ],
         },
+        {
+            ver => 36,
+            sql => [
+                q{
+                CREATE TABLE public.employmentsum
+                (
+                  "user" text NOT NULL,
+                  firstday date NOT NULL DEFAULT now(),
+                  lastday date,
+                  contrattype text,
+                  CONSTRAINT employmentsum_pkey PRIMARY KEY (name),
+                  CONSTRAINT employmentsum_check CHECK (lastday IS NULL OR firstday <= lastday),
+                  CONSTRAINT employmentsum_name_user_fkey FOREIGN KEY ("user")
+                      REFERENCES public."user" (name) MATCH SIMPLE
+                      ON UPDATE CASCADE ON DELETE CASCADE
+                )
+                INHERITS (public.objects)
+                WITH (
+                  OIDS=FALSE
+                );
+
+                CREATE INDEX fki_employmentsum_name_user_fkey
+                  ON public.employmentsum
+                  USING btree
+                  (name COLLATE pg_catalog."default");
+
+                CREATE INDEX fki_employmentsum_user_fkey
+                  ON public.employmentsum
+                  USING btree
+                  (name COLLATE pg_catalog."default");
+
+                CREATE INDEX employmentsum_exported_idx
+                  ON public.employmentsum
+                  USING btree
+                  (exported);
+
+                CREATE INDEX employmentsum_rev_idx
+                  ON public.employmentsum
+                  USING btree
+                  (rev);
+
+                CREATE TRIGGER employmentsum_rev_tg
+                  BEFORE INSERT OR UPDATE OR DELETE
+                  ON public.employmentsum
+                  FOR EACH ROW
+                  EXECUTE PROCEDURE public.rev_tg_f();
+                }
+            ],
+        },
     );