public ActionResult Create(OrganisationRegister organisationRegister)
{
//hier kan ik niet werken met ModelState.IsValid doordat ik in niet alle waarden ophaal van het model OrganisationRegister
if (organisationRegister.Organisation.ID != 0 &&
!string.IsNullOrEmpty(organisationRegister.Register.RegisterName) &&
!string.IsNullOrEmpty(organisationRegister.Register.Device) &&
organisationRegister.Register.PurchaseDate != null &&
organisationRegister.Register.ExpiresDate != null)
{
//als er geen organisatie is toegekend, een record plaatsen in de tussentabel met "default" datums
if (organisationRegister.Organisation.ID == -1)
{
organisationRegister.FromDate = new DateTime(1970, 1, 1, 12, 0, 0);
organisationRegister.UntilDate = new DateTime(1970, 1, 1, 12, 0, 0);
}
//indien er wel een organisatie is toegekend, dag van en tot datum instellen
//dag van vandaag nemen voor "van" datum en niet gekocht op datum: kassa kan bv. gisteren aangekocht zijn en vandaag pas toegekend zijn
//voor "tot" datum vervaldag nemen: wanneer kassa vervalt, is de kassa ook niet meer in het bedrijf
else
{
organisationRegister.FromDate = DateTime.Today;
organisationRegister.UntilDate = organisationRegister.Register.ExpiresDate;
}
int registerID = RegisterDA.CreateRegister(organisationRegister.Register);
RegisterDA.CreateRegisterOrganisation(organisationRegister, registerID);
}
return(RedirectToAction("Index"));
}
public static OrganisationRegister GetOrganisationRegisterByIds(int OrganisationID, int RegisterID)
{
string sql = "SELECT Organisations.OrganisationName, Organisations.Login, Organisation_Register.OrganisationID, Registers.RegisterName, Registers.Device, Organisation_Register.RegisterID, Organisation_Register.FromDate, Organisation_Register.UntilDate";
sql += " FROM Organisation_Register";
sql += " INNER JOIN Organisations";
sql += " ON Organisation_Register.OrganisationID=Organisations.ID";
sql += " INNER JOIN Registers";
sql += " ON Organisation_Register.RegisterID=Registers.ID";
sql += " WHERE OrganisationID=@OrganisationID AND RegisterID=@RegisterID";
DbParameter par1 = Database.AddParameter("AdminDB", "@OrganisationID", OrganisationID);
DbParameter par2 = Database.AddParameter("AdminDB", "@RegisterID", RegisterID);
DbDataReader reader = Database.GetData(Database.GetConnection("AdminDB"), sql, par1, par2);
OrganisationRegister organisationRegister = new OrganisationRegister();
while (reader.Read())
{
organisationRegister.OrganisationID = (int)reader["OrganisationID"];
organisationRegister.RegisterID = (int)reader["RegisterID"];
organisationRegister.FromDate = (DateTime)reader["FromDate"];
organisationRegister.UntilDate = (DateTime)reader["UntilDate"];
organisationRegister.RegisterName = reader["RegisterName"].ToString();
organisationRegister.OrganisationName = reader["OrganisationName"].ToString();
organisationRegister.Login = reader["Login"].ToString();
organisationRegister.Device = reader["Device"].ToString();
}
return(organisationRegister);
}
public static int InsertOrganisationRegister(OrganisationRegister organisationRegister)
{
string sql = "INSERT INTO Organisation_Register VALUES(@OrganisationID,@RegisterID,@FromDate,@UntilDate)";
DbParameter par1 = Database.AddParameter("AdminDB", "@OrganisationID", organisationRegister.OrganisationID);
DbParameter par2 = Database.AddParameter("AdminDB", "@RegisterID", organisationRegister.RegisterID);
DbParameter par3 = Database.AddParameter("AdminDB", "@FromDate", organisationRegister.FromDate);
DbParameter par4 = Database.AddParameter("AdminDB", "@UntilDate", organisationRegister.UntilDate);
return(Database.InsertData(Database.GetConnection("AdminDB"), sql, par1, par2, par3, par4));
}
public static void UpdateRegisterOrganisation(OrganisationRegister or)
{
string sql = "UPDATE OrganisationRegister SET OrganisationID=@OrganisationID, FromDate=@FromDate, UntilDate=@UntilDate WHERE ID=@ID";
DbParameter par1 = Database.AddParameter(CONNECTIONSTRING, "@OrganisationID", or.Organisation.ID);
DbParameter par2 = Database.AddParameter(CONNECTIONSTRING, "@FromDate", or.FromDate.ToUnixTimestamp());
DbParameter par3 = Database.AddParameter(CONNECTIONSTRING, "@UntilDate", or.UntilDate.ToUnixTimestamp());
DbParameter par4 = Database.AddParameter(CONNECTIONSTRING, "@ID", or.ID);
Database.ModifyData(CONNECTIONSTRING, sql, par1, par2, par3, par4);
}
public static int UpdateOrganisationRegister(int oldOrganisationID, OrganisationRegister organisationRegister)
{
string sql = "UPDATE Organisation_Register SET OrganisationID=@OrganisationID, RegisterID=@RegisterID, FromDate=@FromDate, UntilDate=@UntilDate WHERE OrganisationID=@OrganisationIDold AND RegisterID=@RegisterID";
DbParameter par1 = Database.AddParameter("AdminDB", "@OrganisationID", organisationRegister.OrganisationID);
DbParameter par2 = Database.AddParameter("AdminDB", "@RegisterID", organisationRegister.RegisterID);
DbParameter par3 = Database.AddParameter("AdminDB", "@FromDate", organisationRegister.FromDate);
DbParameter par4 = Database.AddParameter("AdminDB", "@UntilDate", organisationRegister.UntilDate);
DbParameter par5 = Database.AddParameter("AdminDB", "@OrganisationIDold", oldOrganisationID);
return(Database.ModifyData(Database.GetConnection("AdminDB"), sql, par1, par2, par3, par4, par5));
}
public static void CreateRegisterOrganisation(OrganisationRegister or, int registerID)
{
string sql = "INSERT INTO OrganisationRegister (OrganisationID, RegisterID, FromDate, UntilDate) ";
sql += "VALUES (@OrganisationID, @RegisterID, @FromDate, @UntilDate)";
DbParameter par1 = Database.AddParameter(CONNECTIONSTRING, "@OrganisationID", or.Organisation.ID);
DbParameter par2 = Database.AddParameter(CONNECTIONSTRING, "@RegisterID", registerID);
DbParameter par3 = Database.AddParameter(CONNECTIONSTRING, "@FromDate", or.FromDate.ToUnixTimestamp());
DbParameter par4 = Database.AddParameter(CONNECTIONSTRING, "@UntilDate", or.UntilDate.ToUnixTimestamp());
Database.InsertData(CONNECTIONSTRING, sql, par1, par2, par3, par4);
}
public ActionResult EditRegister(int oldOrganisationID, int organisationID, DateTime fromDate, DateTime untilDate, int registerID)
{
if (User.Identity.Name == "")
{
return(RedirectToAction("ErrorLogin", "Home"));
}
OrganisationRegister organisationRegister = DAOrganisationRegister.GetOrganisationRegisterByIds(organisationID, registerID);
organisationRegister.FromDate = fromDate;
organisationRegister.OrganisationID = organisationID;
organisationRegister.RegisterID = registerID;
organisationRegister.UntilDate = untilDate;
DAOrganisationRegister.UpdateOrganisationRegister(oldOrganisationID, organisationRegister);
return(RedirectToAction("Register", new { id = oldOrganisationID }));
}
public static List <OrganisationRegister> GetRegisters()
{
Dictionary <int, Organisation> orgs = OrganisationDA.GetOrganisationsDict();
List <OrganisationRegister> results = new List <OrganisationRegister>();
string sql = "SELECT ID, RegisterName, Device, OrganisationID, ExternalID FROM Register";
DbDataReader reader = Database.GetData(Database.GetConnection(cs), sql);
while (reader.Read())
{
OrganisationRegister reg = CreateRegister(reader);
reg.Organisation = orgs[reg.OrganisationID];
results.Add(reg);
}
reader.Close();
return(results);
}
public ActionResult Edit(int?id)
{
if (!id.HasValue)
{
return(RedirectToAction("Index"));
}
OrganisationRegister organisationRegister = RegisterDA.GetRegisterByID(id.Value);
if (organisationRegister.ID == 0)
{
return(RedirectToAction("Index"));
}
ViewBag.Organisations = OrganisationDA.GetOrganisations();
return(View(organisationRegister));
}
public ActionResult Create()
{
OrganisationRegister organistationRegister = new OrganisationRegister();
//bij het toevoegen van een nieuw register
//bij gekocht op: dag van vandaag invullen en bij vervalt op: vijf jaar later
RegisterCompany register = new RegisterCompany()
{
PurchaseDate = DateTime.Today,
ExpiresDate = DateTime.Today.AddYears(5)
};
organistationRegister.Register = register;
ViewBag.Organisations = OrganisationDA.GetOrganisations();
return(View(organistationRegister));
}
//private const string CONNECTIONSTRING = "DefaultConnection";
public static List <OrganisationRegister> GetOrganisationRegisters()
{
string sql = "SELECT * FROM Organisation_Register";
DbDataReader reader = Database.GetData(Database.GetConnection("AdminDB"), sql);
List <OrganisationRegister> OrganisationRegisters = new List <OrganisationRegister>();
while (reader.Read())
{
OrganisationRegister organisationregister = new OrganisationRegister();
organisationregister.OrganisationID = (int)reader["OrganisationID"];
organisationregister.RegisterID = (int)reader["RegisterID"];
organisationregister.FromDate = (DateTime)reader["FromDate"];
organisationregister.UntilDate = (DateTime)reader["UntilDate"];
OrganisationRegisters.Add(organisationregister);
}
return(OrganisationRegisters);
}
public ActionResult Edit(OrganisationRegister organisationRegister)
{
//nog extra controle: geldig id
if (organisationRegister.ID == 0)
{
return(RedirectToAction("Index"));
}
//bestaand record ophalen
OrganisationRegister orOud = RegisterDA.GetRegisterByID(organisationRegister.ID);
//controle of organisatie wel bestaat
if (orOud.Organisation == null)
{
return(RedirectToAction("Index"));
}
//van beschikbaar (geen organisatie) naar niet beschikbaar (wel een organisatie)
if (orOud.Organisation.ID == -1 && organisationRegister.Organisation.ID != -1)
{
organisationRegister.FromDate = DateTime.Today;
organisationRegister.UntilDate = orOud.Register.ExpiresDate;
}
//van niet beschikbaar (wel een organisatie) naar beschikbaar (geen organisatie)
else if (orOud.Organisation.ID != -1 && organisationRegister.Organisation.ID == -1)
{
organisationRegister.FromDate = new DateTime(1970, 1, 1, 12, 0, 0);
organisationRegister.UntilDate = new DateTime(1970, 1, 1, 12, 0, 0);
}
//bv. veranderen van organisatie: van en tot datum behouden
else
{
organisationRegister.FromDate = orOud.FromDate;
organisationRegister.UntilDate = orOud.UntilDate;
}
RegisterDA.UpdateRegisterOrganisation(organisationRegister);
return(RedirectToAction("Index"));
}
public ActionResult NewRegister(int organisationID, int registerID, DateTime FromDate, DateTime FromTime, DateTime UntilDate, DateTime UntilTime)
{
if (User.Identity.Name == "")
{
return(RedirectToAction("ErrorLogin", "Home"));
}
if (FromDate >= UntilDate)
{
return(RedirectToAction("Register", new { id = organisationID }));
}
OrganisationRegister organisationRegister = new OrganisationRegister();
organisationRegister.OrganisationID = organisationID;
organisationRegister.RegisterID = registerID;
organisationRegister.FromDate = new DateTime(FromDate.Year, FromDate.Month, FromDate.Day, FromTime.Hour, FromTime.Minute, 0);
organisationRegister.UntilDate = new DateTime(UntilDate.Year, UntilDate.Month, UntilDate.Day, UntilTime.Hour, UntilTime.Minute, 0);
DAOrganisationRegister.InsertOrganisationRegister(organisationRegister);
return(RedirectToAction("Register", new { id = organisationID }));
}
public static OrganisationRegister GetRegisterByID(int id)
{
OrganisationRegister organisationRegister = new OrganisationRegister();
string sql = "SELECT o.OrganisationName, orgr.ID, orgr.OrganisationID, r.ID as RegisterID, orgr.FromDate, orgr.UntilDate , r.RegisterName, r.Device, r.PurchaseDate, r.ExpiresDate ";
sql += "FROM Organisations AS o ";
sql += "INNER JOIN OrganisationRegister AS orgr ON o.ID = orgr.OrganisationID ";
sql += "INNER JOIN Register as r ON orgr.RegisterID = r.ID ";
sql += "WHERE orgr.ID = @ID";
DbParameter par1 = Database.AddParameter(CONNECTIONSTRING, "@ID", id);
DbDataReader reader = Database.GetData(CONNECTIONSTRING, sql, par1);
while (reader.Read())
{
organisationRegister = Create(reader);
}
reader.Close();
return(organisationRegister);
}
public ActionResult EditRegister(int organisationID, int registerID)
{
if (User.Identity.Name == "")
{
return(RedirectToAction("ErrorLogin", "Home"));
}
PMOrganisationRegister organisationRegister = new PMOrganisationRegister();
organisationRegister.NewOrganisation = new MultiSelectList(DAOrganisation.GetOrganisations(), "Id", "OrganisationName");
OrganisationRegister or = DAOrganisationRegister.GetOrganisationRegisterByIds(organisationID, registerID);
organisationRegister.Device = or.Device;
organisationRegister.FromDate = or.FromDate;
organisationRegister.Login = or.Login;
organisationRegister.OrganisationID = or.OrganisationID;
organisationRegister.OrganisationName = or.OrganisationName;
organisationRegister.RegisterID = or.RegisterID;
organisationRegister.RegisterName = or.RegisterName;
organisationRegister.UntilDate = or.UntilDate;
ViewBag.Organisation = DAOrganisation.GetOrganisationById(organisationID);
ViewBag.oldId = organisationID;
return(View(organisationRegister));
}
public PMOrganisationRegister()
{
DataOrganisationRegister = new OrganisationRegister();
}
public static int Save(OrganisationRegister reg)
{
int rowsaffected = 0;
DbTransaction trans = null;
try
{
trans = Database.BeginTransaction(cs);
if (reg.ID == 0)
{
trans.Connection.ChangeDatabase(reg.Organisation.DbName);
string sql = @"INSERT INTO Register
(RegisterName, Device)
VALUES (@name, @dev)";
DbParameter par1 = Database.AddParameter(cs, "@name", reg.RegisterName);
DbParameter par2 = Database.AddParameter(cs, "@dev", reg.Device);
reg.ExternalID = Database.InsertData(trans, sql, par1, par2);
trans.Connection.ChangeDatabase("CashlessAdmin");
sql =
@"INSERT INTO Register
(RegisterName, Device, OrganisationID, ExternalID)
VALUES (@name, @dev, @orgid, @extid)";
DbParameter par3 = Database.AddParameter(cs, "@orgid", reg.OrganisationID);
DbParameter par4 = Database.AddParameter(cs, "@extid", reg.ExternalID);
rowsaffected += Database.ModifyData(trans, sql, par1, par2, par3, par4) + reg.ExternalID;
reg.ID = rowsaffected;
}
else
{
string sql = "UPDATE Register SET RegisterName = @name, Device = @dev, OrganisationID = @orgid, ExternalID = @extid WHERE ID = @ID";
DbParameter par0 = Database.AddParameter(cs, "@ID", reg.ID);
DbParameter par1 = Database.AddParameter(cs, "@name", reg.RegisterName);
DbParameter par2 = Database.AddParameter(cs, "@dev", reg.Device);
DbParameter par3 = Database.AddParameter(cs, "@orgid", reg.OrganisationID);
DbParameter par4 = Database.AddParameter(cs, "@extid", reg.ExternalID);
rowsaffected += Database.ModifyData(trans, sql, par0, par1, par2, par3, par4);
}
trans.Commit();
}
catch (Exception)
{
if (trans != null)
{
trans.Rollback();
}
}
finally
{
if (trans != null)
{
Database.ReleaseConnection(trans.Connection);
}
}
return(rowsaffected);
}
