<file_sep>﻿drop schema if exists sbkavtal cascade;

create schema sbkavtal;


drop table if exists sbkavtal.avtal;
drop table if exists sbkavtal.person;
drop table if exists sbkavtal.intaktskontering;
drop table if exists sbkavtal.avtalsinnehåll;

drop type if exists sbkavtal.avtalsstatus;
create type sbkavtal.avtalsstatus as enum('Aktivt', 'Inaktivt');

drop type if exists sbkavtal.motpartstyp;
create type sbkavtal.motpartstyp as enum('Extern', 'Förvaltning', 'Kommunalt bolag', 'Uppgift saknas);

drop type if exists sbkavtal.avtalsinnehallstyp;
create type sbkavtal.avtalsinnehallstyp as enum('avtalsinnehåll 1', 'avtalsinnehåll 2', 'avtalsinnehåll 3');

create table sbkavtal.person(
	id			serial primary key,
	first_name		varchar(50),
	last_name		varchar(50),
	belagenhetsadress	varchar(50),
	postnummer		varchar(20),
	postort			varchar(50),
	tfn_nummer		varchar(20),
	epost			varchar(50)
);

create table sbkavtal.fakturaadress(
	id			serial primary key,
	first_name		varchar,
	last_name		varchar,
	belagenhetsadress	varchar,
	postnummer		varchar,
	postort			varchar,
	referens		varchar
);

create table sbkavtal.avtal(
	id			serial primary key,
	diarienummer		bigint,
	status			avtalsstatus,
	startdate		date,
	enddate			date,
	orgnummer		varchar(20),

	motpartstyp		motpartstyp,
	SBKavtalsid		int,
	scan_url		varchar(512),
	enligt_avtal		text,
	internt_alias		text,
	kommentar		text,

	avtalstecknare		integer references person,
	avtalskontakt		integer references person,

	upphandlat_av		integer references person,
	ansvarig_SBK		integer references person,
	ansvarig_avd		varchar(50),
	ansvarig_enhet		varchar(50),

-- 	avtalsinnehall		text,
	avtalsvärde		bigint,

	datakontakt		person,

-- 	intaktskontering	integer references intaktskontering,
	konto			varchar not null,
	kstl			varchar not null,
	vht			varchar not null,
	mtp			varchar not null,
	aktivitet		varchar not null,
	objekt			varchar not null,
	projekt			varchar not null,
	
	fakt_adress		integer references sbkavtal.fakturaadress,

	vitalt_avtal		boolean,
	gallringsår		date
	
);

create table sbkavtal.avtalsinnehall(
	id			serial primary key,
	innehall		sbkavtal.avtalsinnehallstyp,
	avtalsid		integer references avtal not null
);

-- insert into sbkavtal.person(first_name, last_name, belagenhetsadress, postnummer, postort, tfn_nummer, epost)
-- values	('Sven', 'Andersson', 'Svedalavägen', '111 11', 'Svedala', '010-100100', '<EMAIL>');
-- 
-- insert into sbkavtal.avtal(diarienummer, startdate, enddate, orgnummer, status, avtalstecknare, avtalskontakt)
-- values	(314, '2017-01-01', CURRENT_DATE, '820403', 'Aktivt', 1, 1);
-- -- 	(1000, CURRENT_DATE, CURRENT_DATE, '820403', 'Aktivt'),
-- -- 	(666, '1982-04-03', CURRENT_DATE, '140807', 'Inaktivt');
-- 
-- select * from sbkavtal.avtal inner join sbkavtal.person
-- on sbkavtal.avtal.avtalstecknare = sbkavtal.person.id;
-- --select * from person;
-- 
-- insert into sbkavtal.avtalsinnehall(innehall, avtalsid)
-- select 'avtalsinnehåll 1', id from sbkavtal.avtal where diarienummer = 314;
-- 
-- 
-- 
-- select * from sbkavtal.avtalsinnehall;

<file_sep>﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Configuration;
using Npgsql;

namespace SBKAvtalskatalog.Controllers
{
    [RoutePrefix("")]
    [Route("{action=index}")]
    public class KatalogController : Controller
    {
        // GET: Katalog
        [Route("")]
        public ActionResult Index()
        {
            return View();
        }

        [Route("Start")]
        public ActionResult Start()
        {
            return View();
        }

        public ActionResult TestPage()
        {
            return View();
        }

        [Route("editpage")]
        [AcceptVerbs(HttpVerbs.Get)]
        public ActionResult EditPage()
        {
            var model = new Models.Avtalsmodel
            {
                diarienummer = 555,
                startdate = DateTime.Now,
                enddate = DateTime.Now,
                orgnummer = "7707077777"
            };
            return View(model);
        }

        [Route("editpage")]
        [AcceptVerbs(HttpVerbs.Post)]
        public ActionResult EditPage(Models.Avtalsmodel model)
        {
            System.Diagnostics.Debug.WriteLine("Diarienummer:" + model.diarienummer);
            return Redirect("/Tabell");
        }

        [Route("Tabell")]
        public ActionResult Tabell()
        {
            var cs = connstr();
            var lst = new List<SBKAvtalskatalog.Models.Avtalsmodel>();

            using (var conn = new NpgsqlConnection("Host=localhost;Username=postgres;Database=avtalskatalogSBK"))
            {
                conn.Open();
                using (var cmd = new NpgsqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "select diarienummer, startdate, enddate, orgnummer from avtal";
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            lst.Add(new Models.Avtalsmodel
                            {
                                diarienummer = reader.GetInt64(0),
                                startdate = reader.GetDateTime(1),
                                enddate = reader.GetDateTime(2),
                                orgnummer = reader.GetString(3)
                            });
                        }
                    }
                }
            }
            return View(lst);
        }

        private string connstr()
        {
            return "Host=localhost;Username=postgres;Database=mydb";
        }
    }
}