/// <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);
}
//-----------------------------------------------------------------------------------------------------
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);
}
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);
}
/// <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);
}
}
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);
}
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);
}
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);
*/
}