Exemple #1
0
        /// <summary>
        /// inserts PurchaserData WITHOUT the according purchaser
        /// </summary>
        /// <param name="purData"></param>
        /// <returns></returns>
        public PurchaserData InsertPurData(PurchaserData purData)
        {
            PurchaserDAO     pdao = new PurchaserDAO();
            NpgsqlConnection con  = DB.DBConnector.GetConnection();


            NpgsqlCommand command = new NpgsqlCommand(null, con);

            command.CommandText = "INSERT INTO auftraggeberdaten (agid,monat,jahr, stundensatzauszahlung, " +
                                  "Stundensatz,BeitragEinkommen,FahrtkostenZusatz,BetreuungsbedarfH, EinkommenMonat, PflegegeldStufe, " +
                                  "FahrtkostenzusatzKM, agdatenid) VALUES (@agid,@month, @year, @stundensatzauszahlung, @Stundensatz, " +
                                  "@BeitragEinkommen,@FahrtkostenZusatz,@BetreuungsbedarfH, @EinkommenMonat, @PflegegeldStufe, @FahrtkostenzusatzKM,nextval('AGDatenIDGen') )";


            DB.DBConnector.AddToCommand("@agid", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.Purchaser.Id);
            DB.DBConnector.AddToCommand("@month", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.Month);
            DB.DBConnector.AddToCommand("@year", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.Year);
            DB.DBConnector.AddToCommand("@stundensatzauszahlung", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.HourlyRatePayoff);
            DB.DBConnector.AddToCommand("@Stundensatz", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.HourlyRate);
            DB.DBConnector.AddToCommand("@BeitragEinkommen", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.InputIncome);
            DB.DBConnector.AddToCommand("@FahrtkostenZusatz", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.TravellingAllowance);
            DB.DBConnector.AddToCommand("@BetreuungsbedarfH", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.AssistenceDemand);
            DB.DBConnector.AddToCommand("@EinkommenMonat", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.Income);
            DB.DBConnector.AddToCommand("@PflegegeldStufe", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.CareAllowance);
            DB.DBConnector.AddToCommand("@FahrtkostenzusatzKM", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.TravellingAllowanceKM);

            try { command.ExecuteNonQuery(); } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "Error"); }

            con.Close();

            return(purData);
        }
Exemple #2
0
        //-----------------------------------------------------------------------------------------------------
        public List <EffortEntry> GetEntries(long paid, long agid, int month, int year)
        {
            PurchaserDAO         agDao = new PurchaserDAO();
            PersonalAssistantDAO paDao = new PersonalAssistantDAO();

            NpgsqlConnection con     = DB.DBConnector.GetConnection();
            NpgsqlCommand    command = new NpgsqlCommand(null, con);



            command.CommandText = "SELECT eintragId,von,bis,tag,abrechenbareKm,taetigkeit1,taetigkeit2,taetigkeit3,paId From leistungseintrag WHERE paId=@paId AND monat=@monat AND jahr=@jahr AND agId=@agId";


            DB.DBConnector.AddToCommand("@paId", NpgsqlTypes.NpgsqlDbType.Numeric, command, paid);
            DB.DBConnector.AddToCommand("@monat", NpgsqlTypes.NpgsqlDbType.Numeric, command, month);
            DB.DBConnector.AddToCommand("@jahr", NpgsqlTypes.NpgsqlDbType.Numeric, command, year);
            DB.DBConnector.AddToCommand("@agId", NpgsqlTypes.NpgsqlDbType.Numeric, command, agid);

            //using the Connection to get Datas
            NpgsqlDataReader reader = command.ExecuteReader();

            //creating a new Data Container and filling it
            List <EffortEntry> ees = new List <EffortEntry>();

            while (reader.Read())
            {
                EffortEntry ee = new EffortEntry();
                FillEffortEntry(ee, reader);
                ee.Month = month;
                ee.Year  = year;
                ees.Add(ee);
            }

            reader.Close();
            con.Close();

            foreach (EffortEntry ee in ees)
            {
                if (ee.Purchaser != null)
                {
                    agDao.Select(ee.Purchaser);
                }
                if (ee.Pa != null)
                {
                    paDao.select(ee.Pa);
                }
            }

            return(ees);
        }
Exemple #3
0
        public bool Update(PurchaserData purData)
        {
            PersonDAO pDao = new PersonDAO();

            if (purData.Purchaser.ContactPerson != null && purData.Purchaser.ContactPerson.Id == 0)
            {
                pDao.Insert(purData.Purchaser.ContactPerson);
            }
            else if (purData.Purchaser.ContactPerson != null)
            {
                purData.Purchaser.ContactPerson = pDao.Update(purData.Purchaser.ContactPerson);
            }
            PurchaserDAO purDAO = new PurchaserDAO();

            purDAO.Update(purData.Purchaser);

            NpgsqlConnection con = DB.DBConnector.GetConnection();

            NpgsqlCommand command = new NpgsqlCommand(null, con);

            command.CommandText = "UPDATE auftraggeberdaten SET stundensatzauszahlung=@stundensatzauszahlung, " +
                                  "Stundensatz=@Stundensatz,BeitragEinkommen=@BeitragEinkommen,FahrtkostenZusatz=@FahrtkostenZusatz,BetreuungsbedarfH=@BetreuungsbedarfH, EinkommenMonat=@EinkommenMonat, PflegegeldStufe=@PflegegeldStufe, " +
                                  "FahrtkostenzusatzKM=@FahrtkostenzusatzKM WHERE agid=@agid AND monat=@month AND jahr=@year";


            DB.DBConnector.AddToCommand("@agid", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.Purchaser.Id);
            DB.DBConnector.AddToCommand("@month", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.Month);
            DB.DBConnector.AddToCommand("@year", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.Year);
            DB.DBConnector.AddToCommand("@stundensatzauszahlung", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.HourlyRatePayoff);
            DB.DBConnector.AddToCommand("@Stundensatz", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.HourlyRate);
            DB.DBConnector.AddToCommand("@BeitragEinkommen", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.InputIncome);
            DB.DBConnector.AddToCommand("@FahrtkostenZusatz", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.TravellingAllowance);
            DB.DBConnector.AddToCommand("@BetreuungsbedarfH", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.AssistenceDemand);
            DB.DBConnector.AddToCommand("@EinkommenMonat", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.Income);
            DB.DBConnector.AddToCommand("@PflegegeldStufe", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.CareAllowance);
            DB.DBConnector.AddToCommand("@FahrtkostenzusatzKM", NpgsqlTypes.NpgsqlDbType.Numeric, command, purData.TravellingAllowanceKM);

            try { command.ExecuteNonQuery(); } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "Error"); }

            con.Close();
            return(true);
        }
Exemple #4
0
        /// <summary>
        /// lädt alle purchaser in die Liste des Persönlichen Assistenen, die mit ihm ein Dienstverhältnis haben.
        /// </summary>
        /// <param name="pa"></param>
        public void selectPurchaserList(PersonalAssistant pa)
        {
            NpgsqlConnection con = DB.DBConnector.GetConnection();



            NpgsqlCommand command = new NpgsqlCommand(null, con);

            command.CommandText = "SELECT * FROM Dienstverhaeltnis p WHERE p.paid=@id";

            DB.DBConnector.AddToCommand("@id", NpgsqlTypes.NpgsqlDbType.Integer, command, pa.Id);

            NpgsqlDataReader reader = command.ExecuteReader();
            List <int>       agIDs  = new List <int>();

            while (reader.Read())
            {
                if (!reader.IsDBNull(0))
                {
                    agIDs.Add(reader.GetInt32(0));
                }
            }
            reader.Close();
            con.Close();

            PurchaserDAO purDAO = new PurchaserDAO();

            if (pa.Purchasers == null)
            {
                pa.Purchasers = new List <Purchaser>();
            }
            foreach (int i in agIDs)
            {
                Purchaser pur = new Purchaser();
                pur.Id = i;
                pur    = purDAO.Select(pur);
                pa.Purchasers.Add(pur);
            }
        }
Exemple #5
0
        public List <Purchaser> SelectPurchasers(PersonalAssistant p)
        {
            NpgsqlConnection con     = DB.DBConnector.GetConnection();
            NpgsqlCommand    command = new NpgsqlCommand(null, con);

            command.CommandText = "SELECT * FROM Dienstverhaeltnis p WHERE p.paid=@id";

            DB.DBConnector.AddToCommand("@id", NpgsqlTypes.NpgsqlDbType.Integer, command, p.Id);

            NpgsqlDataReader reader = command.ExecuteReader();
            List <int>       purIds = new List <int>();

            while (reader.Read())
            {
                if (!reader.IsDBNull(0))
                {
                    purIds.Add(reader.GetInt32(0));
                }
            }
            reader.Close();
            con.Close();

            PurchaserDAO purDAO = new PurchaserDAO();

            p.Purchasers = new List <Purchaser>();
            foreach (int i in purIds)
            {
                Purchaser pur = new Purchaser();
                pur.Id = i;
                pur    = purDAO.Select(pur);
                EmploymentStatus empStatus = new EmploymentStatus();
                empStatus.Assistant = p;
                empStatus.Purchaser = pur;
                p.Purchasers.Add(pur);
            }

            return(p.Purchasers);
        }
Exemple #6
0
        internal List <EffortEntry> GetEntriesByPa(PersonalAssistant pa, int month, int year)
        {
            NpgsqlConnection con = DB.DBConnector.GetConnection();


            PurchaserDAO pDao = new PurchaserDAO();

            NpgsqlCommand command = new NpgsqlCommand(null, con);

            command.CommandText = "SELECT eintragId,von,bis,tag,abrechenbareKm,taetigkeit1,taetigkeit2,taetigkeit3,agid From leistungseintrag WHERE paId=@paId AND monat=@monat AND jahr=@jahr";

            DB.DBConnector.AddToCommand("@paId", NpgsqlTypes.NpgsqlDbType.Numeric, command, pa.Id);
            DB.DBConnector.AddToCommand("@monat", NpgsqlTypes.NpgsqlDbType.Numeric, command, month);
            DB.DBConnector.AddToCommand("@jahr", NpgsqlTypes.NpgsqlDbType.Numeric, command, year);

            NpgsqlDataReader reader = command.ExecuteReader();

            List <EffortEntry> ees = new List <EffortEntry>();

            while (reader.Read())
            {
                EffortEntry ee = new EffortEntry();
                ee.Pa        = pa;
                ee.Purchaser = new Purchaser();

                if (!reader.IsDBNull(0))
                {
                    ee.Id = reader.GetInt32(0);
                }
                if (!reader.IsDBNull(1))
                {
                    String   timeString = reader.GetString(1);
                    String[] times      = timeString.Split(':');
                    ee.From = new DateTime(2000, 1, 1, int.Parse(times[0]), int.Parse(times[1]), 0);
                }
                if (!reader.IsDBNull(2))
                {
                    String   timeString = reader.GetString(2);
                    String[] times      = timeString.Split(':');
                    ee.To = new DateTime(2000, 1, 1, int.Parse(times[0]), int.Parse(times[1]), 0);
                }
                if (!(reader.IsDBNull(3)))
                {
                    ee.Day = reader.GetInt32(3);
                }
                if (!(reader.IsDBNull(4)))
                {
                    ee.Km = reader.GetInt32(4);
                }
                if (!(reader.IsDBNull(5)))
                {
                    ee.A1      = new Activity();
                    ee.A1.Name = (Activity.State)(reader.GetInt32(5) - 1);
                }
                if (!(reader.IsDBNull(6)))
                {
                    ee.A2      = new Activity();
                    ee.A2.Name = (Activity.State)(reader.GetInt32(6) - 1);
                }
                if (!(reader.IsDBNull(7)))
                {
                    ee.A3      = new Activity();
                    ee.A3.Name = (Activity.State)(reader.GetInt32(7) - 1);
                }
                if (!reader.IsDBNull(8))
                {
                    ee.Purchaser.Id = reader.GetInt32(8);
                }
            }
            con.Close();

            foreach (EffortEntry ee in ees)
            {
                if (ee.Pa != null)
                {
                    pDao.Select(ee.Purchaser);
                }
            }

            return(ees);
        }
Exemple #7
0
        public static void TestPersonDao()
        {
            TestMonthlyBilling();
            //TestPurchaser();
            PersonalAssistant    p    = new PersonalAssistant();
            PersonalAssistantDAO pDAO = new PersonalAssistantDAO();

            p.FirstName              = "testpersonDao";
            p.LastName               = "testpersonDao";
            p.PhoneNumber            = "9999";
            p.MobilePhone            = "99999";
            p.EMail                  = "*****@*****.**";
            p.HomeAdress.City        = "LINZ";
            p.HomeAdress.Country     = "schland";
            p.HomeAdress.DoorNumber  = 11115;
            p.HomeAdress.StairNumber = 2345;
            pDAO.insert(p);
            pDAO.update(p);
            pDAO.select(p);
            pDAO.SelectAll();

            PurchaserDAO purDAO = new PurchaserDAO();
            Purchaser    pur    = new Purchaser();

            pur.FirstName              = "testpersonDao";
            pur.LastName               = "testpersonDao";
            pur.PhoneNumber            = "9999";
            pur.MobilePhone            = "99999";
            pur.EMail                  = "*****@*****.**";
            pur.HomeAdress.City        = "LINZ";
            pur.HomeAdress.Country     = "schland";
            pur.HomeAdress.DoorNumber  = 11115;
            pur.HomeAdress.StairNumber = 2345;
            purDAO.Insert(pur);
            purDAO.Update(pur);
            purDAO.Select(pur);
            purDAO.SelectAll();

            PurchaserData    pd    = new PurchaserData();
            PurchaserDataDAO pdDao = new PurchaserDataDAO();

            pd.AssistenceDemand     = 20;
            pd.CareAllowance        = 1;
            pd.CareAllowanceMaximum = 22;
            pd.HourlyRate           = 12;
            pd.HourlyRatePayoff     = 13;
            pd.Income                = 1000;
            pd.InputIncome           = 2000;
            pd.Month                 = 2;
            pd.Year                  = 2000;
            pd.TravellingAllowance   = 15;
            pd.TravellingAllowanceKM = 25;
            pd.Purchaser             = pur;
            pdDao.Insert(pd);
            pdDao.Update(pd);
            pdDao.Select(pd);
            pdDao.SelectAll();

            MonthlyBillingPerPa mb    = new MonthlyBillingPerPa();
            MonthlyBillingDAO   mbDAO = new MonthlyBillingDAO();

            mb.Pa           = p;
            mb.Pur          = pd;
            mb.WorkingHours = 20;
            mb.PrivateKm    = 20;
            mb.EffortList   = new List <EffortEntry>();
            mb.Month        = 2;
            mb.Year         = 2000;
            mbDAO.InsertMonthlyBilling(mb);
            // mbDAO.InsertMonthlyBilling(mb);
            List <MonthlyBilling> mbs = mbDAO.selectAllMB();

            mbDAO.UpdateMonthlyBillingEntry(mb);

            // EffortEntry
            EffortEntry ee = new EffortEntry();

            ee.From         = DateTime.Today;
            ee.To           = DateTime.Today;
            ee.Month        = 2;
            ee.Year         = 2000;
            ee.Pa           = new PersonalAssistant();
            ee.Pa.Id        = p.Id;
            ee.Purchaser    = new Purchaser();
            ee.Purchaser.Id = pur.Id;
            ee.A1           = new Activity();
            ee.A1.Name      = (Activity.State) 1;
            ee.A2           = new Activity();
            ee.A2.Name      = (Activity.State) 2;

            EffortEntryDAO eeDAO = new EffortEntryDAO();

            eeDAO.InsertEntry(ee);
            eeDAO.GetEntries(p.Id, pur.Id, ee.Month, ee.Year);
            ee.A1.Name = (Activity.State) 3;
            eeDAO.UpdateEntry(ee);
            PersonalAssistant pa = new PersonalAssistant();

            pa.Id = 10;
            List <EffortEntry> ees = eeDAO.GetEntriesByPa(pa, 2, 1999);

            pur.Id = 199;
            ees    = eeDAO.GetEntriesByPurchaser(pur, 2, 1999);



            //TestPurchaser();

            /*// testPurchaser();
             * PurchaserDataDAO purdadao = new PurchaserDataDAO();
             * PurchaserData purda = new PurchaserData();
             * purda.purchaser.id = 998;
             * purda.month = 2;
             * purda.year = 2015;
             * purdadao.select(purda);
             *
             *
             * Purchaser pur = new Purchaser();
             * pur.id = 999;
             * PurchaserDAO purDao = new PurchaserDAO();
             * pur = purDao.select(pur);
             *
             *
             * /*
             * Person p = new Person();
             * IPersonDAO Pdao = new PersonDAO();
             *
             * //test Insert
             * p.firstName = "testpersonDao";
             * p.lastName = "testpersonDao";
             * p.phoneNumber = 9999;
             * p.mobilePhone = 99999;
             * p.eMail = "*****@*****.**";
             * p.homeAdress.city = "LINZ";
             * p.homeAdress.country = "schland";
             * p.homeAdress.doorNumber = 11115;
             * p.homeAdress.stairNumber = 2345;
             * Pdao.insert(p);
             *
             * //test update
             * p.homeAdress.city = "WIEN";
             * p.homeAdress.street = "updatestraße";
             * p.firstName = "updateName";
             * p.eMail = "updateEmail";
             * Pdao.update(p);
             *
             *
             *
             * Person p2 = new Person();
             * p2.id = p.id;
             *
             * //test select
             * Pdao.select(p);
             * Console.WriteLine(p.firstName);
             * Console.WriteLine(p.homeAdress.city);
             */
            //test pdao

            /* PersonalAssistant p = new PersonalAssistant();
             * PersonalAssistantDAO Pdao = new PersonalAssistantDAO();
             *
             *
             *
             * //test Insert
             * p.firstName = "Susanne";
             * p.lastName = "Ludwig";
             * p.phoneNumber = 9999;
             * p.mobilePhone = 99999;
             * p.eMail = "*****@*****.**";
             * p.homeAdress.city = "Wartberg";
             * //p.ClosingDateDocuments = new DateTime(1999, 12, 12);
             * Pdao.insert(p);
             *
             * /*
             * p.firstName = "paHans";
             * p.lastName = "paMeinzl";
             * p.homeAdress.city = "paWien";
             * p.active = true;
             * p.ClosingDateDocuments = DateTime.Today;
             * Pdao.insert(p);
             * p.homeAdress.city = "paUpdateLinz";
             *
             * Pdao.update(p);
             * Pdao.select(p);
             *
             * Console.Write(Pdao.SelectAll().ElementAt(0).lastName);
             */
        }