Ejemplo n.º 1
0
        /// <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 -------------------------
        }
Ejemplo n.º 2
0
        /// <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 -------------------------
        }