public bool UpdateMonthlyBillingEntry(MonthlyBillingPerPa mb)
{
NpgsqlConnection con = DB.DBConnector.GetConnection();
NpgsqlCommand command = new NpgsqlCommand(null, con);
command.CommandText = "UPDATE Monatsabrechnung SET anzahprivatlkm=@privat,stundenanzahl=@stunde, " +
"anzahlkmabgerechnet=@kmabger WHERE agid=@agid AND paid=@paid " +
"AND monat=@monat AND jahr=@jahr";
DB.DBConnector.AddToCommand("@paid", NpgsqlTypes.NpgsqlDbType.Integer, command, mb.Pa.Id);
DB.DBConnector.AddToCommand("@agid", NpgsqlTypes.NpgsqlDbType.Integer, command, mb.Pur.Purchaser.Id);
DB.DBConnector.AddToCommand("@monat", NpgsqlTypes.NpgsqlDbType.Integer, command, mb.Month);
DB.DBConnector.AddToCommand("@jahr", NpgsqlTypes.NpgsqlDbType.Integer, command, mb.Year);
DB.DBConnector.AddToCommand("@privat", NpgsqlTypes.NpgsqlDbType.Integer, command, mb.PrivateKm);
DB.DBConnector.AddToCommand("@stunde", NpgsqlTypes.NpgsqlDbType.Integer, command, mb.WorkingHours);
DB.DBConnector.AddToCommand("@kmabger", NpgsqlTypes.NpgsqlDbType.Integer, command, mb.BillableKm);
try { command.ExecuteNonQuery(); } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "Error"); }
con.Close();
return(true);
}
public MonthlyBillingPerPa InsertMonthlyBilling(MonthlyBillingPerPa mb)
{
NpgsqlConnection con = DB.DBConnector.GetConnection();
NpgsqlCommand command = new NpgsqlCommand(null, con);
command.CommandText = "INSERT INTO monatsabrechnung (monat,jahr,agid,paid,anzahprivatlkm,stundenanzahl," +
"anzahlkmabgerechnet) VALUES (@monat,@jahr,@agid,@paid,@anzahlprivatKm, " +
"@stundenanzahl,@anzahlKmabgerechnet)";
DB.DBConnector.AddToCommand("@paid", NpgsqlTypes.NpgsqlDbType.Integer, command, mb.Pa.Id);
DB.DBConnector.AddToCommand("@agid", NpgsqlTypes.NpgsqlDbType.Integer, command, mb.Pur.Purchaser.Id);
DB.DBConnector.AddToCommand("@jahr", NpgsqlTypes.NpgsqlDbType.Integer, command, mb.Year);
DB.DBConnector.AddToCommand("@monat", NpgsqlTypes.NpgsqlDbType.Integer, command, mb.Month);
DB.DBConnector.AddToCommand("@anzahlprivatKm", NpgsqlTypes.NpgsqlDbType.Integer, command, mb.PrivateKm);
DB.DBConnector.AddToCommand("@stundenanzahl", NpgsqlTypes.NpgsqlDbType.Integer, command, mb.WorkingHours);
DB.DBConnector.AddToCommand("@anzahlKmabgerechnet", NpgsqlTypes.NpgsqlDbType.Integer, command, mb.BillableKm);
try { command.ExecuteNonQuery(); } catch (Exception e) { MessageBox.Show(e.Message.ToString(), "Error"); }
con.Close();
return(null);
}
public static void TestMonthlyBilling()
{
List <PurchaserData> pds = (new PurchaserDataDAO()).SelectAll();
PurchaserData pData = new PurchaserData();
pData.Purchaser = new Purchaser();
pData.Purchaser.Id = 998;
// pData.Month = 4;
// pData.Year = 2013;
pData = (new PurchaserDataDAO()).Select(pData);
PersonalAssistant pa = new PersonalAssistant(996);
pa = (new PersonalAssistantDAO()).select(pa);
MonthlyBillingPerPa mbPerPa = new MonthlyBillingPerPa();
mbPerPa.Pur = pData;
mbPerPa.Pa = pa;
mbPerPa.Month = 2;
mbPerPa.Year = 2015;
mbPerPa = (new MonthlyBillingDAO()).selectMBEntry(mbPerPa);
}
public bool UpdateMonthlyBillingEntry(MonthlyBillingPerPa mb)
{
return(MbDao.UpdateMonthlyBillingEntry(mb));
}
public MonthlyBillingPerPa InsertMonthlyBilling(MonthlyBillingPerPa mb)
{
return(MbDao.InsertMonthlyBilling(mb));
}
/// <summary>
/// die Monatsabrechnung für einen PA aus der DB holen
/// Thomas: hier müssen Jahr, Monat und AG auch angegeben werden ansonsten brauchen wir hier eine Liste.
/// </summary>
/// <param name="mb"></param>
/// <returns></returns>
public MonthlyBillingPerPa SelectMb(MonthlyBillingPerPa mb)
{
return(MbDao.selectMBEntry(mb));
}
public static void setUp(TestContext testContext)
{
eeDao = new EffortEntryDAO();
mbDao = new MonthlyBillingDAO();
pdDao = new PurchaserDataDAO();
paDao = new PersonalAssistantDAO();
Purchaser p = new Purchaser();
//Person Insert
p.FirstName = "testpersonDao";
p.LastName = "testpersonDao";
p.PhoneNumber = "9999";
p.MobilePhone = "99999";
p.EMail = "*****@*****.**";
p.HomeAdress.Street = "Am Hügel";
p.HomeAdress.HouseNumber = 12;
p.HomeAdress.City = "LINZ";
p.HomeAdress.Country = "schland";
p.HomeAdress.DoorNumber = 11115;
p.HomeAdress.StairNumber = 2345;
p.HomeAdress.Etage = 2;
p.HomeAdress.ZipCode = 5040;
p.IBAN = "AT1254859888898";
p.BIC = "SPPR2586XX";
p.AccountHolder = "Helmut Günther";
p.SVN = 1225020496;
p.nationality = "Auenland";
p.InfoField = "Infotext";
//purchaser
p.Active = true;
p.ApprovalBegin = new DateTime(2015, 05, 02);
p.ApprovalEnd = new DateTime(2017, 05, 02);
p.EntryDate = new DateTime(2015, 05, 02);
p.hasContract = true;
p.hasIntroCourse = true;
p.DistrictCommision = "Perg";
//purchaserData
d = new PurchaserData();
d.Purchaser = p;
d.AssistenceDemand = 12;
d.CareAllowance = 2;
d.HourlyRate = 30;
d.HourlyRatePayoff = 20;
d.Income = 29;
d.InputIncome = 100;
d.Month = 11;
d.TravellingAllowance = 12;
d.TravellingAllowanceKM = 11;
d.CareAllowanceMaximum = 16;
d.Year = 1998;
pa = new PersonalAssistant();
//Person Insert
pa.FirstName = "persöhnlicher";
pa.LastName = "assistent";
pa.PhoneNumber = "9999";
pa.MobilePhone = "99999";
pa.EMail = "*****@*****.**";
pa.HomeAdress.Street = "Am Hügel";
pa.HomeAdress.HouseNumber = 12;
pa.HomeAdress.City = "LINZ";
pa.HomeAdress.Country = "schland";
pa.HomeAdress.DoorNumber = 11115;
pa.HomeAdress.StairNumber = 2345;
pa.HomeAdress.Etage = 2;
pa.HomeAdress.ZipCode = 5040;
pa.IBAN = "AT1254859888898";
pa.BIC = "SPPR2586XX";
pa.AccountHolder = "Helmut Günther";
pa.SVN = 1225020496;
pa.nationality = "Auenland";
pa.InfoField = "Infotext";
//pa
pa.Active = true;
pa.ClosingDateDocuments = new DateTime(2016, 02, 03);
pa.SV = true;
pa.Dienstvertrag = true;
pa.BestBH = true;
pa.Grundkurs = true;
pa.consumedHours = 2.4M;
pa.deadLineHours = new DateTime(2017, 02, 04);
pa = paDao.insert(pa);
d = pdDao.Insert(d);
mb = new MonthlyBillingPerPa();
mb.PrivateKm = 21;
mb.Month = 7;
mb.Year = 1998;
mb.Pa = pa;
mb.Pur = d;
mb.WorkingHours = 15;
mb.BillableKm = 10;
effortEntries = new List <EffortEntry>();
EffortEntry a = new EffortEntry();
a.Month = mb.Month;
a.Year = mb.Year;
a.Pa = mb.Pa;
a.Purchaser = mb.Pur.Purchaser;
a.Day = 1;
a.From = new DateTime(a.Year, a.Month, a.Day, 12, 30, 0);
a.To = new DateTime(a.Year, a.Month, a.Day, 18, 45, 0);
a.Km = 10;
a.A1 = new Activity(Activity.State.Begleitung);
a.A2 = new Activity(Activity.State.Hauswirtschaft);
EffortEntry b = new EffortEntry();
b.Month = mb.Month;
b.Year = mb.Year;
b.Pa = mb.Pa;
b.Purchaser = mb.Pur.Purchaser;
b.Day = 2;
b.From = new DateTime(b.Year, b.Month, b.Day, 12, 30, 0);
b.To = new DateTime(b.Year, b.Month, b.Day, 18, 45, 0);
b.Km = 10;
b.A1 = new Activity(Activity.State.Begleitung);
b.A2 = new Activity(Activity.State.Hauswirtschaft);
effortEntries.Add(a);
effortEntries.Add(b);
}
public void InsertMBEntry()
{
//given
pa = paDao.insert(pa);
d = pdDao.Insert(d);
//when
mbDao.InsertMonthlyBilling(mb);
//then
mb = mbDao.selectMBEntry(mb);
Assert.AreEqual(mb.Pur.Purchaser.FirstName, "testpersonDao");
Assert.AreEqual(mb.Pur.Purchaser.LastName, "testpersonDao");
Assert.AreEqual(mb.Pur.Purchaser.PhoneNumber, "9999");
Assert.AreEqual(mb.Pur.Purchaser.MobilePhone, "99999");
Assert.AreEqual(mb.Pur.Purchaser.EMail, "*****@*****.**");
Assert.AreEqual(mb.Pur.Purchaser.HomeAdress.Street, "Am Hügel");
Assert.AreEqual(mb.Pur.Purchaser.HomeAdress.HouseNumber, 12);
Assert.AreEqual(mb.Pur.Purchaser.HomeAdress.City, "LINZ");
Assert.AreEqual(mb.Pur.Purchaser.HomeAdress.Country, "schland");
Assert.AreEqual(mb.Pur.Purchaser.HomeAdress.DoorNumber, 11115);
Assert.AreEqual(mb.Pur.Purchaser.HomeAdress.StairNumber, 2345);
Assert.AreEqual(mb.Pur.Purchaser.HomeAdress.Etage, 2);
Assert.AreEqual(mb.Pur.Purchaser.HomeAdress.ZipCode, 5040);
Assert.AreEqual(mb.Pur.Purchaser.IBAN, "AT1254859888898");
Assert.AreEqual(mb.Pur.Purchaser.BIC, "SPPR2586XX");
Assert.AreEqual(mb.Pur.Purchaser.AccountHolder, "Helmut Günther");
Assert.AreEqual(mb.Pur.Purchaser.SVN, 1225020496);
Assert.AreEqual(mb.Pur.Purchaser.nationality, "Auenland");
Assert.AreEqual(mb.Pur.Purchaser.InfoField, "Infotext");
//purchaser
Assert.AreEqual(mb.Pur.Purchaser.Active, true);
Assert.AreEqual(mb.Pur.Purchaser.ApprovalBegin, new DateTime(2015, 05, 02));
Assert.AreEqual(mb.Pur.Purchaser.ApprovalEnd, new DateTime(2017, 05, 02));
Assert.AreEqual(mb.Pur.Purchaser.EntryDate, new DateTime(2015, 05, 02));
Assert.AreEqual(mb.Pur.Purchaser.hasContract, true);
Assert.AreEqual(mb.Pur.Purchaser.hasIntroCourse, true);
Assert.AreEqual(mb.Pur.Purchaser.DistrictCommision, "Perg");
//purchaserData
Assert.AreEqual(mb.Pur.AssistenceDemand, 12);
Assert.AreEqual(mb.Pur.CareAllowance, 2);
Assert.AreEqual(mb.Pur.HourlyRate, 30);
Assert.AreEqual(mb.Pur.HourlyRatePayoff, 20);
Assert.AreEqual(mb.Pur.Income, 29);
Assert.AreEqual(mb.Pur.InputIncome, 100);
Assert.AreEqual(mb.Pur.Month, 11);
Assert.AreEqual(mb.Pur.TravellingAllowance, 12);
Assert.AreEqual(mb.Pur.TravellingAllowanceKM, 11);
Assert.AreEqual(mb.Pur.CareAllowanceMaximum, 16);
Assert.AreEqual(mb.Pur.Year, 1998);
//Person Insert
Assert.AreEqual(mb.Pa.FirstName, "persöhnlicher");
Assert.AreEqual(mb.Pa.LastName, "assistent");
Assert.AreEqual(mb.Pa.PhoneNumber, "9999");
Assert.AreEqual(mb.Pa.MobilePhone, "99999");
Assert.AreEqual(mb.Pa.EMail, "*****@*****.**");
Assert.AreEqual(mb.Pa.HomeAdress.Street, "Am Hügel");
Assert.AreEqual(mb.Pa.HomeAdress.HouseNumber, 12);
Assert.AreEqual(mb.Pa.HomeAdress.City, "LINZ");
Assert.AreEqual(mb.Pa.HomeAdress.Country, "schland");
Assert.AreEqual(mb.Pa.HomeAdress.DoorNumber, 11115);
Assert.AreEqual(mb.Pa.HomeAdress.StairNumber, 2345);
Assert.AreEqual(mb.Pa.HomeAdress.Etage, 2);
Assert.AreEqual(mb.Pa.HomeAdress.ZipCode, 5040);
Assert.AreEqual(mb.Pa.IBAN, "AT1254859888898");
Assert.AreEqual(mb.Pa.BIC, "SPPR2586XX");
Assert.AreEqual(mb.Pa.AccountHolder, "Helmut Günther");
Assert.AreEqual(mb.Pa.SVN, 1225020496);
Assert.AreEqual(mb.Pa.nationality, "Auenland");
Assert.AreEqual(mb.Pa.InfoField, "Infotext");
//pa
Assert.AreEqual(mb.Pa.Active, true);
Assert.AreEqual(mb.Pa.ClosingDateDocuments, new DateTime(2016, 02, 03));
Assert.AreEqual(mb.Pa.SV, true);
Assert.AreEqual(mb.Pa.Dienstvertrag, true);
Assert.AreEqual(mb.Pa.BestBH, true);
Assert.AreEqual(mb.Pa.Grundkurs, true);
Assert.AreEqual(mb.Pa.consumedHours, 2.4M);
Assert.AreEqual(mb.Pa.deadLineHours, new DateTime(2017, 02, 04));
Assert.AreEqual(mb.PrivateKm, 21);
Assert.AreEqual(mb.Month, 5);
Assert.AreEqual(mb.Year, 1998);
Assert.AreEqual(mb.Pa, pa);
Assert.AreEqual(mb.Pur, d);
Assert.AreEqual(mb.WorkingHours, 15);
Assert.AreEqual(mb.BillableKm, 10);
}
public List <MonthlyBilling> selectAllFrom(DateTime?nullable)
{
List <MonthlyBilling> mb = new List <MonthlyBilling>();
MonthlyBilling mb1 = new MonthlyBilling();
MonthlyBilling mb2 = new MonthlyBilling();
MonthlyBillingPerPa mbppa1 = new MonthlyBillingPerPa();
MonthlyBillingPerPa mbppa2 = new MonthlyBillingPerPa();
MonthlyBillingPerPa mbppa3 = new MonthlyBillingPerPa();
MonthlyBillingPerPa mbppa4 = new MonthlyBillingPerPa();
MonthlyBillingPerPa mbppa5 = new MonthlyBillingPerPa();
mb1.Month = 6;
mb1.Year = 2015;
mb2.Month = 5;
mb2.Year = 2015;
EffortEntry e1 = new EffortEntry(2011, 06, 12,
new Purchaser(new Person("Hansi", "Müller", new Adress("Teststrasse", 2, 2323, "City"))), new PersonalAssistant(new Person("Elisabeth", "Schütz", new Adress("Hausstrasse", 12, 3445, "Linz"))), new DateTime(2011, 5, 6), new DateTime(2012, 7, 7), 22);
EffortEntry e2 = new EffortEntry(2011, 06, 12,
new Purchaser(new Person("Lisa", "Bauer", new Adress("Hochstrasse", 34, 7654, "Town"))), new PersonalAssistant(new Person("Hans", "Kunz", new Adress("testtest", 87, 6354, "Enns"))), new DateTime(2013, 5, 6), new DateTime(2015, 7, 7), 242);
EffortEntry e3 = new EffortEntry(2011, 06, 12,
new Purchaser(new Person("Thomas", "Mann", new Adress("Klammstrasse", 2, 2323, "City"))), new PersonalAssistant(new Person("Uwe", "Lamm", new Adress("Hausstrasse", 12, 1234, "Steyr"))), new DateTime(2011, 5, 6), new DateTime(2012, 7, 7), 22);
EffortEntry e4 = new EffortEntry(2011, 06, 12,
new Purchaser(new Person("Tom", "Bauer", new Adress("Friedlstrasse", 344, 9876, "Hall"))), new PersonalAssistant(new Person("Julia", "Niedermaier", new Adress("Lastenstrasse", 56, 6354, "Wels"))), new DateTime(2013, 5, 6), new DateTime(2015, 7, 7), 242);
e1.A1 = new Activity();
e1.A2 = new Activity();
e1.A3 = new Activity();
e1.A1.Name = new Activity.State();
e1.A2.Name = new Activity.State();
e1.A3.Name = new Activity.State();
e1.A1.Name = Activity.State.Nachtbereitschaft;
e1.A2.Name = Activity.State.Grundversorgung;
e1.A3.Name = Activity.State.Begleitung;
e1.Day = 1;
e1.Month = 2;
e1.Year = 2015;
e1.From = new DateTime(2015, 2, 1, 10, 23, 00);
e1.To = new DateTime(2015, 2, 1, 20, 00, 00);
e1.Km = 15;
e2.A1 = new Activity();
e2.A2 = new Activity();
e2.A1.Name = new Activity.State();
e2.A2.Name = new Activity.State();
e2.A1.Name = Activity.State.Begleitung;
e2.A2.Name = Activity.State.Freizeitgestaltung;
e2.Day = 3;
e2.Month = 7;
e2.Year = 2015;
e2.From = new DateTime(2015, 7, 3, 6, 20, 00);
e2.To = new DateTime(2015, 7, 3, 6, 20, 00);
e2.Km = 15;
e3.A1 = new Activity();
e3.A2 = new Activity();
e3.A1.Name = new Activity.State();
e3.A2.Name = new Activity.State();
e3.A1.Name = Activity.State.Begleitung;
e3.A2.Name = Activity.State.Freizeitgestaltung;
e3.Day = 3;
e3.Month = 7;
e3.Year = 2015;
e3.From = new DateTime();
e3.From.AddHours(12);
e3.From.AddMinutes(00);
e3.To = new DateTime();
e3.To.AddHours(15);
e3.To.AddMinutes(00);
e3.Km = 15;
e4.A1 = new Activity();
e4.A2 = new Activity();
e4.A1.Name = new Activity.State();
e4.A2.Name = new Activity.State();
e4.A1.Name = Activity.State.Begleitung;
e4.A2.Name = Activity.State.Freizeitgestaltung;
e4.Day = 3;
e4.Month = 7;
e4.Year = 2015;
e4.From = new DateTime(2015, 2, 1, 07, 25, 00);
e4.To = new DateTime(2015, 2, 1, 16, 00, 00);
e4.Km = 15;
mbppa1.EffortList = new List <EffortEntry>();
mbppa2.EffortList = new List <EffortEntry>();
mbppa3.EffortList = new List <EffortEntry>();
mbppa1.EffortList.Add(e2);
mbppa1.EffortList.Add(e1);
mbppa1.EffortList.Add(e4);
mbppa2.EffortList.Add(e3);
mbppa2.EffortList.Add(e4);
mbppa1.BillableKm = 22;
mbppa1.Pa = new PersonalAssistant(new Person("Franz", "Müller", new Adress("Sandl", 2, 2345, "Altstadt")));
mbppa1.Pur = new PurchaserData();
mbppa1.Pur.Purchaser.Active = true;
mbppa1.Pur.Purchaser.FirstName = "Hugo";
mbppa1.Pur.Purchaser.LastName = "Huber";
mbppa1.Pur.Purchaser.MobilePhone = "982374283745";
mbppa1.Pur.Purchaser.EntryDate = new DateTime(2010, 12, 12);
mbppa1.Pur.Purchaser.EMail = "*****@*****.**";
mbppa1.Pur.Purchaser.ApprovalBegin = new DateTime(2015, 01, 01);
mbppa1.Pur.Purchaser.ApprovalEnd = new DateTime(2015, 12, 31);
mbppa1.Pur.Purchaser.ContactPerson = new Person("Maria", "Huber", new Adress("Hauptstrasse", 12, 4040, "Linz"));
mbppa1.Pur.Purchaser.Employees = new List <EmploymentStatus>();
EmploymentStatus e0 =
new EmploymentStatus(
new PersonalAssistant(new Person("Brigitte", "Fritte",
new Adress("Landstrasse", 22, 4209, "Engerwitzdorf"))), mbppa1.Pur.Purchaser);
EmploymentStatus e5 =
new EmploymentStatus(
new PersonalAssistant(new Person("Max", "Moritz",
new Adress("Maienweg", 22, 4209, "Engerwitzdorf"))), mbppa1.Pur.Purchaser);
mbppa1.Pur.Purchaser.HomeAdress = new Adress("Heimatstrasse", 124, 2345, "Wien");
mbppa1.Pur.Purchaser.PhoneNumber = "28374937453987";
mbppa1.Pur.Income = 1500;
mbppa1.Pur.InputIncome = 150;
mbppa1.Pur.Month = 6;
mbppa1.Pur.Year = 2015;
mbppa1.Pur.TravellingAllowanceKM = (decimal)0.22;
mbppa1.Pur.HourlyRate = 20;
mbppa1.Pur.HourlyRatePayoff = (decimal)20.5;
mbppa1.Pur.CareAllowanceMaximum = 300;
mbppa1.Pur.CareAllowance = 2;
mbppa1.Pur.AssistenceDemand = 60;
mbppa2.BillableKm = 34;
mbppa2.Pa = new PersonalAssistant(new Person("Franz", "Müller", new Adress("Sandl", 2, 2345, "Altstadt")));
mbppa2.Pur = new PurchaserData();
mbppa2.Pur.Purchaser.Active = true;
mbppa2.Pur.Purchaser.FirstName = "Hugo";
mbppa2.Pur.Purchaser.LastName = "Huber";
mbppa2.Pur.Purchaser.MobilePhone = "982374283745";
mbppa2.Pur.Purchaser.EntryDate = new DateTime(2010, 12, 12);
mbppa2.Pur.Purchaser.EMail = "*****@*****.**";
mbppa2.Pur.Purchaser.ApprovalBegin = new DateTime(2015, 01, 01);
mbppa2.Pur.Purchaser.ApprovalEnd = new DateTime(2015, 12, 31);
mbppa2.Pur.Purchaser.ContactPerson = new Person("Maria", "Huber", new Adress("Hauptstrasse", 12, 4040, "Linz"));
mbppa2.Pur.Purchaser.Employees = new List <EmploymentStatus>();
EmploymentStatus e9 =
new EmploymentStatus(
new PersonalAssistant(new Person("Brigitte", "Fritte",
new Adress("Landstrasse", 22, 4209, "Engerwitzdorf"))), mbppa2.Pur.Purchaser);
EmploymentStatus e7 =
new EmploymentStatus(
new PersonalAssistant(new Person("Max", "Moritz",
new Adress("Maienweg", 22, 4209, "Engerwitzdorf"))), mbppa2.Pur.Purchaser);
mbppa2.Pur.Purchaser.HomeAdress = new Adress("Heimatstrasse", 124, 2345, "Wien");
mbppa2.Pur.Purchaser.PhoneNumber = "28374937453987";
mbppa2.Pur.Income = 1500;
mbppa2.Pur.InputIncome = 150;
mbppa2.Pur.Month = 6;
mbppa2.Pur.Year = 2015;
mbppa2.Pur.TravellingAllowanceKM = (decimal)0.22;
mbppa2.Pur.HourlyRate = 20;
mbppa2.Pur.HourlyRatePayoff = (decimal)20.5;
mbppa2.Pur.CareAllowanceMaximum = 300;
mbppa2.Pur.CareAllowance = 2;
mbppa2.Pur.AssistenceDemand = 60;
mb1.MbPerPaList = new List <MonthlyBillingPerPa>();
mb2.MbPerPaList = new List <MonthlyBillingPerPa>();
mb1.MbPerPaList.Add(mbppa1);
mb1.MbPerPaList.Add(mbppa2);
mb2.MbPerPaList.Add(mbppa2);
List <MonthlyBilling> list = new List <MonthlyBilling>();
list.Add(mb1);
list.Add(mb2);
return(list);
}
public bool UpdateMonthlyBillingEntry(MonthlyBillingPerPa mb)
{
throw new NotImplementedException();
}
public MonthlyBillingPerPa InsertMonthlyBilling(MonthlyBillingPerPa mb)
{
throw new NotImplementedException();
}
public MonthlyBillingPerPa selectMBEntry(MonthlyBillingPerPa mb)
{
throw new NotImplementedException();
}
public List <MonthlyBilling> selectAllFrom(DateTime?nullable)
{
NpgsqlConnection con = DB.DBConnector.GetConnection();
if (nullable == null)
{
nullable = new DateTime(2000, 01, 01);
}
;
NpgsqlCommand command = new NpgsqlCommand(null, con);
command.CommandText = "select * from monatsabrechnung where (monat >= @monat and jahr=@jahr) or jahr > @jahr ";
DB.DBConnector.AddToCommand("@monat", NpgsqlTypes.NpgsqlDbType.Numeric, command, nullable.Value.Month);
DB.DBConnector.AddToCommand("@jahr", NpgsqlTypes.NpgsqlDbType.Numeric, command, nullable.Value.Year);
NpgsqlDataReader reader = command.ExecuteReader();
List <MonthlyBilling> mbs = new List <MonthlyBilling>();
MonthlyBilling mb = new MonthlyBilling();
mb.MbPerPaList = new List <MonthlyBillingPerPa>();
MonthlyBillingPerPa currmba = null;
while (reader.Read())
{
MonthlyBillingPerPa mbPerPA = new MonthlyBillingPerPa();
if (!(reader.IsDBNull(0)))
{
mbPerPA.PrivateKm = reader.GetInt32(0);
}
if (!(reader.IsDBNull(1)))
{
mbPerPA.Month = Convert.ToInt32(reader.GetDecimal(1));
mb.Month = mbPerPA.Month;
}
if (!(reader.IsDBNull(2)))
{
mbPerPA.Year = Convert.ToInt32(reader.GetDecimal(2));
mb.Year = mbPerPA.Year;
}
if (!(reader.IsDBNull(3)))
{
mbPerPA.Pa = new PersonalAssistant(reader.GetInt32(3));
}
if (!(reader.IsDBNull(4)))
{
mbPerPA.Pur = new PurchaserData();
mbPerPA.Pur.Purchaser = new Purchaser();
mbPerPA.Pur.Purchaser.Id = reader.GetInt32(4);
if (mb.Purchaser == null)
{
mb.Purchaser = new PurchaserData();
mb.Purchaser.Purchaser = new Purchaser();
mb.Purchaser.Purchaser.Id = mbPerPA.Pur.Purchaser.Id;
}
}
if (!(reader.IsDBNull(5)))
{
mbPerPA.WorkingHours = reader.GetInt32(5);
}
if (!(reader.IsDBNull(6)))
{
mbPerPA.BillableKm = reader.GetInt32(6);
}
if (currmba != null && currmba.Month == mbPerPA.Month && currmba.Year == mbPerPA.Year && currmba.Pur.Purchaser.Id == mbPerPA.Pur.Purchaser.Id)
{
}
else if (currmba != null)
{
mbs.Add(mb);
mb = new MonthlyBilling();
mb.MbPerPaList = new List <MonthlyBillingPerPa>();
}
else
{
currmba = mbPerPA;
}
mb.MbPerPaList.Add(mbPerPA);
currmba = mbPerPA;
}
reader.Close();
con.Close();
return(mbs);
}
public List <MonthlyBilling> selectAllMB()
{
NpgsqlConnection con = DB.DBConnector.GetConnection();
NpgsqlCommand command = new NpgsqlCommand(null, con);
command.CommandText = "SELECT * FROM Monatsabrechnung ORDER BY agid,monat,jahr";
NpgsqlDataReader reader = command.ExecuteReader();
List <MonthlyBilling> mbs = new List <MonthlyBilling>();
MonthlyBilling mb = new MonthlyBilling();
mb.MbPerPaList = new List <MonthlyBillingPerPa>();
MonthlyBillingPerPa currmba = null;
while (reader.Read())
{
MonthlyBillingPerPa mbPerPA = new MonthlyBillingPerPa();
if (!(reader.IsDBNull(0)))
{
mbPerPA.PrivateKm = reader.GetInt32(0);
}
if (!(reader.IsDBNull(1)))
{
mbPerPA.Month = Convert.ToInt32(reader.GetDecimal(1));
mb.Month = mbPerPA.Month;
}
if (!(reader.IsDBNull(2)))
{
mbPerPA.Year = Convert.ToInt32(reader.GetDecimal(2));
mb.Year = mbPerPA.Year;
}
if (!(reader.IsDBNull(3)))
{
mbPerPA.Pa = new PersonalAssistant(reader.GetInt32(3));
}
if (!(reader.IsDBNull(4)))
{
mbPerPA.Pur = new PurchaserData();
mbPerPA.Pur.Purchaser = new Purchaser();
mbPerPA.Pur.Purchaser.Id = reader.GetInt32(4);
if (mb.Purchaser == null)
{
mb.Purchaser = new PurchaserData();
mb.Purchaser.Purchaser = new Purchaser();
mb.Purchaser.Purchaser.Id = mbPerPA.Pur.Purchaser.Id;
}
}
if (!(reader.IsDBNull(5)))
{
mbPerPA.WorkingHours = reader.GetInt32(5);
}
if (!(reader.IsDBNull(6)))
{
mbPerPA.BillableKm = reader.GetInt32(6);
}
if (currmba != null && currmba.Month == mbPerPA.Month && currmba.Year == mbPerPA.Year && currmba.Pur.Purchaser.Id == mbPerPA.Pur.Purchaser.Id)
{
}
else if (currmba != null)
{
mbs.Add(mb);
mb = new MonthlyBilling();
mb.MbPerPaList = new List <MonthlyBillingPerPa>();
}
else
{
currmba = mbPerPA;
}
mb.MbPerPaList.Add(mbPerPA);
currmba = mbPerPA;
}
reader.Close();
con.Close();
return(mbs);
}
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);
*/
}
