/// <summary>
/// Speichern.
/// </summary>
/// <param name="param">Der Parameter.</param>
/// <returns>"KomissionIndustrieNaehrwertEntityA"</returns>
KomissionIndustrieNaehrwertEntityA IServiceKomissionIndustrieNaehrwertEntity.Speichern(KomissionIndustrieNaehrwertEntityF param)
{
#region ------------------------------ Initialisierung ------------------------------
KomissionIndustrieNaehrwertEntityA ret = new KomissionIndustrieNaehrwertEntityA();
ret.HatFehler = true;
// für ado.net
SqlConnection sqlConnection = null;
SqlCommand sqlCommand1 = null;
SqlCommand sqlCommand2 = null;
#endregion --------------------------- Initialisierung Ende -------------------------
#region ------------------------------ Implementierung ------------------------------
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
{
try
{
// was ist mit ... new DebuggerWriter(); // writing to the debugger log window
Speichern_VorImpl(param);
sqlConnection = new SqlConnection(VerbindungsEinstellung.ConnectionString);
sqlConnection.Open();
// speichern des eigentlichen Satzes mit sp_Table_Update
sqlCommand1 = new SqlCommand("Controlling.sp_KomissionIndustrieNaehrwert_UpdateCols", sqlConnection);
sqlCommand1.CommandTimeout = 300;
AddParameterDef4Update(sqlCommand1);
sqlCommand1.CommandType = System.Data.CommandType.StoredProcedure;
for (int i = 0; i < param.KomissionIndustrieNaehrwertEntity.Count; i++)
{
//if (param.KomissionIndustrieNaehrwertEntity[i].Timestamp != null) // update nicht mehr über timestamp
//{
System.Collections.Generic.List<string> changedColumns = param.KomissionIndustrieNaehrwertEntity[i].GetUpdatedColumns();
sqlCommand1.Parameters["@rowid"].Value = (Guid)param.KomissionIndustrieNaehrwertEntity[i].Rowid;
sqlCommand1.Parameters["@timestamp"].Value = param.KomissionIndustrieNaehrwertEntity[i].Timestamp;
sqlCommand1.Parameters["@mandant"].Value = (Guid)param.MandantenID;
sqlCommand1.Parameters["@geaendertvon"].Value = (string)param.UserName;
if (changedColumns.Contains("BlsNaehrwerteAllergene")) // && (param.KomissionIndustrieNaehrwertEntity[i].BlsNaehrwerteAllergene != Guid.Empty))
{
sqlCommand1.Parameters["@blsnaehrwerteallergene"].Value = (Guid)param.KomissionIndustrieNaehrwertEntity[i].BlsNaehrwerteAllergene;
}
else
{
sqlCommand1.Parameters["@blsnaehrwerteallergene"].Value = DBNull.Value;
}
if (changedColumns.Contains("KomissionArtikel")) // && (param.KomissionIndustrieNaehrwertEntity[i].KomissionArtikel != Guid.Empty))
{
sqlCommand1.Parameters["@Komissionartikel"].Value = (Guid)param.KomissionIndustrieNaehrwertEntity[i].KomissionArtikel;
}
else
{
sqlCommand1.Parameters["@Komissionartikel"].Value = DBNull.Value;
}
if (changedColumns.Contains("Menge"))
{
sqlCommand1.Parameters["@menge"].Value = (double)param.KomissionIndustrieNaehrwertEntity[i].Menge;
}
else
{
sqlCommand1.Parameters["@menge"].Value = DBNull.Value;
}
SqlDataReader sqlReader = sqlCommand1.ExecuteReader();
int timestampOid = sqlReader.GetOrdinal("timestamp");
int geaendertamOid = sqlReader.GetOrdinal("geaendertam");
int geaendertvonOid = sqlReader.GetOrdinal("geaendertvon");
int blsnaehrwerteallergeneOid = sqlReader.GetOrdinal("blsnaehrwerteallergene");
int KomissionartikelOid = sqlReader.GetOrdinal("Komissionartikel");
int mengeOid = sqlReader.GetOrdinal("menge");
if (sqlReader.Read())
{
KomissionIndustrieNaehrwertEntityDM rs = new KomissionIndustrieNaehrwertEntityDM();
rs.DisableUpdateMask = true;
rs.Rowid = param.KomissionIndustrieNaehrwertEntity[i].Rowid;
if (!sqlReader.IsDBNull(timestampOid))
{
byte[] tbyte = new byte[sqlReader.GetBytes(timestampOid, 0, null, 0, int.MaxValue)]; // länge ermitteln
sqlReader.GetBytes(timestampOid, 0, tbyte, 0, int.MaxValue); // timestamp in buffer einlesen
rs.Timestamp = tbyte;
}
rs.Mandant = param.MandantenID;
rs.ErstelltVon = param.KomissionIndustrieNaehrwertEntity[i].ErstelltVon;
rs.ErstelltAm = param.KomissionIndustrieNaehrwertEntity[i].ErstelltAm;
rs.GeaendertVon = sqlReader.IsDBNull(geaendertvonOid) ? string.Empty : sqlReader.GetString(geaendertvonOid);
rs.GeaendertAm = sqlReader.IsDBNull(geaendertamOid) ? DateTime.MinValue : sqlReader.GetDateTime(geaendertamOid);
rs.BlsNaehrwerteAllergene = sqlReader.IsDBNull(blsnaehrwerteallergeneOid) ? Guid.Empty : sqlReader.GetGuid(blsnaehrwerteallergeneOid);
rs.KomissionArtikel = sqlReader.IsDBNull(KomissionartikelOid) ? Guid.Empty : sqlReader.GetGuid(KomissionartikelOid);
if (!sqlReader.IsDBNull(mengeOid))
rs.Menge = sqlReader.GetDouble(mengeOid);
rs.DisableUpdateMask = false;
ret.KomissionIndustrieNaehrwertEntity.Add(rs);
}
else
{
ret.FehlerText = "Der Datensatz konnte nicht gespeichert werden!";
ret.HatFehler = true;
return ret;
}
sqlReader.Close();
//}
}
Speichern_NachImpl(param, ret);
scope.Complete();
}
catch (SqlException ex)
{
// logging des fehlers noch implementieren
ret = new KomissionIndustrieNaehrwertEntityA();
ret.HatFehler = true;
ret.FehlerText = "Sql-Fehler: " + ex.Number.ToString() + " " + ex.Message;
return ret;
}
catch (Exception e)
{
// logging des fehlers noch implementieren
ret = new KomissionIndustrieNaehrwertEntityA();
ret.HatFehler = true;
ret.FehlerText = e.Message;
return ret;
}
finally
{
if (sqlCommand1 != null)
sqlCommand1.Dispose();
if (sqlCommand2 != null)
sqlCommand2.Dispose();
if (sqlConnection != null)
sqlConnection.Close();
}
}
#endregion --------------------------- Implementierung Ende -------------------------
#region ------------------------------ Rueckgabe ------------------------------
ret.HatFehler = false;
return ret;
#endregion --------------------------- Rueckgabe Ende -------------------------
}
/// <summary>
/// Lesen.
/// </summary>
/// <param name="param">Der Parameter.</param>
/// <returns>"KomissionIndustrieNaehrwertEntityA"</returns>
KomissionIndustrieNaehrwertEntityA IServiceKomissionIndustrieNaehrwertEntity.Lesen(KomissionIndustrieNaehrwertEntityF param)
{
#region ------------------------------ Initialisierung ------------------------------
KomissionIndustrieNaehrwertEntityA ret = new KomissionIndustrieNaehrwertEntityA();
ret.HatFehler = true;
PagingA paging = Paging.CheckPaging(param);
// für ado.net
SqlConnection sqlConnection = null;
SqlCommand sqlCommand1 = null;
SqlCommand sqlCommand2 = null;
SqlDataReader sqlReader = null;
StringBuilder strFilter = new StringBuilder();
StringBuilder strSortierung = new StringBuilder();
// queryfilter aufbereiten
strFilter.Append(" KomissionIndustrieNaehrwert.mandant='");
strFilter.Append(param.MandantenID.ToString());
strFilter.Append("' ");
if ((Guid)param.Filter.Rowid != Guid.Empty)
{
strFilter.Append(" AND KomissionIndustrieNaehrwert.rowid='");
strFilter.Append(param.Filter.Rowid.ToString());
strFilter.Append("' ");
}
if (param.Filter.ErstelltVon.ToString().Length > 0)
{
strFilter.Append(" AND lower(KomissionIndustrieNaehrwert.ERSTELLTVON) LIKE '%");
strFilter.Append(param.Filter.ErstelltVon.ToString().ToLower());
strFilter.Append("%'");
}
if (param.Filter.ErstelltAm != DateTime.MinValue)
{
strFilter.Append(" AND CAST(FLOOR(CAST(KomissionIndustrieNaehrwert.ERSTELLTAM AS float)) AS datetime)='");
strFilter.Append(param.Filter.ErstelltAm.ToSqlDateFormat());
strFilter.Append("'");
}
if (param.Filter.GeaendertVon.ToString().Length > 0)
{
strFilter.Append(" AND lower(KomissionIndustrieNaehrwert.GEAENDERTVON) LIKE '%");
strFilter.Append(param.Filter.GeaendertVon.ToString().ToLower());
strFilter.Append("%'");
}
if (param.Filter.GeaendertAm != DateTime.MinValue)
{
strFilter.Append(" AND CAST(FLOOR(CAST(KomissionIndustrieNaehrwert.GEAENDERTAM AS float)) AS datetime)='");
strFilter.Append(param.Filter.GeaendertAm.ToSqlDateFormat());
strFilter.Append("'");
}
if ((Guid)param.Filter.BlsNaehrwerteAllergene != Guid.Empty)
{
strFilter.Append(" AND KomissionIndustrieNaehrwert.blsnaehrwerteallergene='");
strFilter.Append(param.Filter.BlsNaehrwerteAllergene.ToString());
strFilter.Append("' ");
}
if ((Guid)param.Filter.KomissionArtikel != Guid.Empty)
{
strFilter.Append(" AND KomissionIndustrieNaehrwert.Komissionartikel='");
strFilter.Append(param.Filter.KomissionArtikel.ToString());
strFilter.Append("' ");
}
if (param.Filter.Menge != 0.0)
{
int iDec = param.Filter.Menge.ToString().Length - (param.Filter.Menge.ToString().IndexOf(",") + 1);
strFilter.Append(" AND ROUND(KomissionIndustrieNaehrwert.menge,");
strFilter.Append(iDec.ToString());
strFilter.Append(")=");
strFilter.Append(param.Filter.Menge.ToString().Replace(",", "."));
}
// erweiterte Sql Bedingung aus Basisobjekt
if (!string.IsNullOrEmpty(param.ExtSqlRestriction))
{
strFilter.Append(" AND ");
strFilter.Append(param.ExtSqlRestriction);
}
// sortierung
#endregion --------------------------- Initialisierung Ende -------------------------
#region ------------------------------ Implementierung ------------------------------
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
{
try
{
// was ist mit ... new DebuggerWriter(); // writing to the debugger log window
Lesen_VorImpl(param);
sqlConnection = new SqlConnection(VerbindungsEinstellung.ConnectionString);
sqlConnection.Open();
sqlCommand1 = new SqlCommand("sp_SelectRecords", sqlConnection);
sqlCommand1.CommandTimeout = 300;
sqlCommand1.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand1.Parameters.AddWithValue("@tablename", "Controlling.KomissionIndustrieNaehrwert");
sqlCommand1.Parameters.AddWithValue("@queryfilter", strFilter.ToString());
sqlCommand1.Parameters.AddWithValue("@sortfield", strSortierung.ToString());
sqlCommand1.Parameters.AddWithValue("@userid", param.UserId);
if (!string.IsNullOrEmpty(param.TableSortRefTable))
sqlCommand1.Parameters.AddWithValue("@tablesortreftable", param.TableSortRefTable.ToString());
if (!string.IsNullOrEmpty(param.TableSortColumn))
sqlCommand1.Parameters.AddWithValue("@tablesortcolumn", param.TableSortColumn.ToString());
//sqlCommand1.Parameters.AddWithValue("@pagesize", 500);
//sqlCommand1.Parameters.AddWithValue("@pageindex", 1);
sqlReader = sqlCommand1.ExecuteReader();
int rowidOid = sqlReader.GetOrdinal("rowid");
int mandantOid = sqlReader.GetOrdinal("mandant");
int timestampOid = sqlReader.GetOrdinal("timestamp");
int erstelltamOid = sqlReader.GetOrdinal("erstelltam");
int erstelltvonOid = sqlReader.GetOrdinal("erstelltvon");
int geaendertamOid = sqlReader.GetOrdinal("geaendertam");
int geaendertvonOid = sqlReader.GetOrdinal("geaendertvon");
int blsnaehrwerteallergeneOid = sqlReader.GetOrdinal("blsnaehrwerteallergene");
int KomissionartikelOid = sqlReader.GetOrdinal("Komissionartikel");
int mengeOid = sqlReader.GetOrdinal("menge");
while (sqlReader.Read())
{
KomissionIndustrieNaehrwertEntityDM rs = new KomissionIndustrieNaehrwertEntityDM();
rs.DisableUpdateMask = true;
rs.Rowid = sqlReader.IsDBNull(rowidOid) ? Guid.Empty : sqlReader.GetGuid(rowidOid);
if (!sqlReader.IsDBNull(timestampOid))
{
byte[] tbyte = new byte[sqlReader.GetBytes(timestampOid, 0, null, 0, int.MaxValue)]; // länge ermitteln
sqlReader.GetBytes(timestampOid, 0, tbyte, 0, int.MaxValue); // timestamp in buffer einlesen
rs.Timestamp = tbyte;
}
rs.Mandant = sqlReader.IsDBNull(mandantOid) ? Guid.Empty : sqlReader.GetGuid(mandantOid);
rs.ErstelltVon = sqlReader.IsDBNull(erstelltvonOid) ? string.Empty : sqlReader.GetString(erstelltvonOid);
rs.ErstelltAm = sqlReader.IsDBNull(erstelltamOid) ? DateTime.MinValue : sqlReader.GetDateTime(erstelltamOid);
rs.GeaendertVon = sqlReader.IsDBNull(geaendertvonOid) ? string.Empty : sqlReader.GetString(geaendertvonOid);
rs.GeaendertAm = sqlReader.IsDBNull(geaendertamOid) ? DateTime.MinValue : sqlReader.GetDateTime(geaendertamOid);
rs.BlsNaehrwerteAllergene = sqlReader.IsDBNull(blsnaehrwerteallergeneOid) ? Guid.Empty : sqlReader.GetGuid(blsnaehrwerteallergeneOid);
rs.KomissionArtikel = sqlReader.IsDBNull(KomissionartikelOid) ? Guid.Empty : sqlReader.GetGuid(KomissionartikelOid);
if (!sqlReader.IsDBNull(mengeOid))
rs.Menge = sqlReader.GetDouble(mengeOid);
rs.DisableUpdateMask = false;
ret.KomissionIndustrieNaehrwertEntity.Add(rs);
}
// sind dem benutzer alle orgeinheiten zugewiesen?
if ((param.BenuterHatAlleMasterOrgEinheitenHolen) && (param.Filter.Rowid != Guid.Empty))
{
sqlCommand2 = new SqlCommand("sp_SelectHatAlleOrgEinheiten", sqlConnection);
sqlCommand2.CommandTimeout = 300;
sqlCommand2.CommandType = System.Data.CommandType.StoredProcedure;
sqlCommand2.Parameters.Add("@tablename", System.Data.SqlDbType.VarChar);
sqlCommand2.Parameters["@tablename"].DbType = System.Data.DbType.String;
sqlCommand2.Parameters["@tablename"].Direction = System.Data.ParameterDirection.Input;
sqlCommand2.Parameters["@tablename"].Value = "KomissionIndustrieNaehrwert";
sqlCommand2.Parameters.Add("@rowid2query", System.Data.SqlDbType.UniqueIdentifier);
sqlCommand2.Parameters["@rowid2query"].DbType = System.Data.DbType.Guid;
sqlCommand2.Parameters["@rowid2query"].Direction = System.Data.ParameterDirection.Input;
sqlCommand2.Parameters["@rowid2query"].Value = (Guid)param.Filter.Rowid;
sqlCommand2.Parameters.Add("@userid", System.Data.SqlDbType.UniqueIdentifier);
sqlCommand2.Parameters["@userid"].DbType = System.Data.DbType.Guid;
sqlCommand2.Parameters["@userid"].Direction = System.Data.ParameterDirection.Input;
sqlCommand2.Parameters["@userid"].Value = (Guid)param.UserId;
sqlCommand2.Parameters.Add("@hatalleorgeinheiten", System.Data.SqlDbType.Bit);
sqlCommand2.Parameters["@hatalleorgeinheiten"].DbType = System.Data.DbType.Boolean;
sqlCommand2.Parameters["@hatalleorgeinheiten"].Direction = System.Data.ParameterDirection.InputOutput;
sqlCommand2.Parameters["@hatalleorgeinheiten"].Value = false;
sqlReader.Close();
sqlReader = sqlCommand2.ExecuteReader();
ret.BenutzerHatAlleMasterOrgEinheiten = (bool)sqlCommand2.Parameters["@hatalleorgeinheiten"].Value;
}
Lesen_NachImpl(param, ret);
scope.Complete();
}
catch (SqlException ex)
{
// logging des fehlers noch implementieren
ret = new KomissionIndustrieNaehrwertEntityA();
ret.HatFehler = true;
ret.FehlerText = "Sql-Fehler: " + ex.Number.ToString() + " " + ex.Message;
return ret;
}
catch (Exception e)
{
// logging des fehlers noch implementieren
ret = new KomissionIndustrieNaehrwertEntityA();
ret.HatFehler = true;
ret.FehlerText = e.Message;
return ret;
}
finally
{
if (sqlReader != null)
sqlReader.Close();
if (sqlCommand1 != null)
sqlCommand1.Dispose();
if (sqlCommand2 != null)
sqlCommand2.Dispose();
if (sqlConnection != null)
sqlConnection.Close();
}
}
#endregion --------------------------- Implementierung Ende -------------------------
#region ------------------------------ Rueckgabe ------------------------------
ret.HatFehler = false;
return ret;
#endregion --------------------------- Rueckgabe Ende -------------------------
}