Esempio n. 1
0
        public static void Delete(EpTransaction epTran, ContractHeader entity)
        {
            SqlCommand cm = new SqlCommand();

            cm.CommandType = CommandType.Text;

            //set connection
            SqlConnection connection;

            if (epTran == null)
            {
                connection = DataManager.GetConnection();
            }
            else
            {
                connection = epTran.GetSqlConnection();
            }
            if (connection.State != System.Data.ConnectionState.Open)
            {
                connection.Open();
            }
            cm.Connection = connection;

            //set transaction
            if (epTran != null)
            {
                cm.Transaction = epTran.GetSqlTransaction();
            }

            //Check whether record exists
            ContractHeader checkEntity = RetrieveByKey(epTran, entity.ContractNumber);

            if (checkEntity == null)
            {
                throw new Exception("Record doesn't exist.");
            }

            //Update
            cm.CommandText = "DELETE FROM conthdr WHERE EBELN=@EBELN";
            SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10);

            cm.Parameters.Add(p1);
            p1.Value = entity.ContractNumber;

            cm.ExecuteNonQuery();

            if (epTran == null)
            {
                if (connection.State != System.Data.ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
        }
Esempio n. 2
0
        public static ContractHeader RetrieveByKey(EpTransaction epTran, string ContractNumber)
        {
            ContractHeader entity      = null;
            string         whereClause = " EBELN='" + DataManager.EscapeSQL(ContractNumber) + "' ";

            Collection <ContractHeader> entities = Retrieve(epTran, whereClause, "");

            if (entities.Count > 0)
            {
                entity = entities[0];
            }

            return(entity);
        }
        public void UpdateContract ()
        {
            int wstep;

            try
            {
                EpTransaction tran = DataManager.BeginTransaction();
                try
                {
                    wstep = 100 / contractHeader.Count; 
                    foreach (ZCONTRACT_HDR x in contractHeader)
                    {
                        ContractHeader hrd = new ContractHeader();
                        hrd.ContractNumber = x.Ebeln;
                        hrd.ContractDate = Convert.ToInt64(x.Bedat);
                        hrd.ContractCategory = x.Bstyp;
                        hrd.DocumentType = x.Bsart;
                        hrd.ContractValue = x.Ktwrt;
                        hrd.CreatedBy = x.Ernam;
                        hrd.Currency = x.Waers;
                        hrd.ExchangeRate = x.Wkurs;
                        hrd.InternalReference = "";
                        hrd.PaymentTerms = x.Zterm;
                        hrd.PurchasingGroup = x.Ekgrp;
                        hrd.SalesContactPerson = x.Verkf;
                        hrd.SupplierId = x.Lifnr;
                        hrd.Telephone = x.Telf1;
                        hrd.ValidityEnd = Convert.ToInt64(x.Kdate);
                        hrd.ValidityStart = Convert.ToInt64(x.Kdatb);                   

                        if (ContractHeaderDAO.RetrieveByKey(tran,x.Ebeln) != null)  
                            ContractHeaderDAO.Update(tran, hrd);  
                        else
                            ContractHeaderDAO.Insert(tran, hrd);

                        wstr = wstr + x.Ebeln + ", ";
                        aForm.getProgressBar().Increment(wstep);
                    }

                    this.setParameters();
                    wstep = 100 / contractItem.Count; 
                    foreach (ZCONTRACT_ITM x in contractItem)
                    {
                        ContractItem itm = new ContractItem();
                        itm.ContractNumber = x.Ebeln;
                        itm.ContractItemSequence = x.Ebelp;
                        itm.Description = x.Txz01;
                        itm.MaterialGroup = x.Matkl;
                        itm.MaterialNumber = x.Matnr;
                        itm.NetValue = x.Brtwr;
                        itm.Plant = x.Werks;
                        itm.PricePerUnit = x.Peinh;
                        itm.Requisitioner = x.Afnam;
                        itm.RequisitionNumber = x.Banfn;
                        itm.RFQNumber = x.Anfnr;
                        itm.TargetQuantity = x.Ktmng;
                        itm.UnitOfMeasure = x.Meins;
                        itm.UnitPrice = x.Netpr;

                        if (ContractItemDAO.RetrieveByKey(tran,x.Ebeln, x.Ebelp) != null)
                            ContractItemDAO.Update(tran, itm);  
                        else
                            ContractItemDAO.Insert(tran, itm); 

                        wstr = wstr + x.Ebeln + ", ";
                        aForm.getProgressBar().Increment(wstep);
                    }
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    throw (ex);
                }
                finally
                {
                    tran.Dispose();
                } 

                this.RemoveContractDetails();
            }
            catch (Exception ex)
            {
                Utility.ExceptionLog(ex);
                throw (ex);
            }
        }
        public static void Insert(EpTransaction epTran, ContractHeader entity)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandType = CommandType.Text;

            //set connection
            SqlConnection connection;
            if (epTran == null)
                connection = DataManager.GetConnection();
            else
                connection = epTran.GetSqlConnection();
            if (connection.State != System.Data.ConnectionState.Open) connection.Open();
            cm.Connection = connection;

            //set transaction
            if (epTran != null)
                cm.Transaction = epTran.GetSqlTransaction();

            //Check whether record exists
            ContractHeader checkEntity = RetrieveByKey(epTran, entity.ContractNumber);
            if (checkEntity != null)
            {
                throw new Exception("Record already exists.");
            }

            //Insert 
            cm.CommandText = "INSERT INTO conthdr ([EBELN],[BEDAT],[BSTYP],[BSART],[ERNAM],[LIFNR],[ZTERM],[EKGRP],[WAERS],[WKURS],[KDATB],[KDATE],[VERKF],[TELF1],[KTWRT],[IHERZ]) VALUES(@EBELN,@BEDAT,@BSTYP,@BSART,@ERNAM,@LIFNR,@ZTERM,@EKGRP,@WAERS,@WKURS,@KDATB,@KDATE,@VERKF,@TELF1,@KTWRT,@IHERZ)";
            
            SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.VarChar, 10);
            cm.Parameters.Add(p1);
            p1.Value = entity.ContractNumber;

            SqlParameter p2 = new SqlParameter("@BEDAT", SqlDbType.BigInt, 8);
            cm.Parameters.Add(p2);
            if (entity.ContractDate.HasValue)
                p2.Value = entity.ContractDate;
            else
                p2.Value = DBNull.Value;

            SqlParameter p3 = new SqlParameter("@BSTYP", SqlDbType.Char, 1);
            cm.Parameters.Add(p3);
            p3.Value = entity.ContractCategory;

            SqlParameter p4 = new SqlParameter("@BSART", SqlDbType.Char, 4);
            cm.Parameters.Add(p4);
            p4.Value = entity.DocumentType;

            SqlParameter p5 = new SqlParameter("@ERNAM", SqlDbType.VarChar, 12);
            cm.Parameters.Add(p5);
            p5.Value = entity.CreatedBy;

            SqlParameter p6 = new SqlParameter("@LIFNR", SqlDbType.VarChar, 10);
            cm.Parameters.Add(p6);
            p6.Value = entity.SupplierId;

            SqlParameter p7 = new SqlParameter("@ZTERM", SqlDbType.Char, 4);
            cm.Parameters.Add(p7);
            p7.Value = entity.PaymentTerms;

            SqlParameter p8 = new SqlParameter("@EKGRP", SqlDbType.Char, 3);
            cm.Parameters.Add(p8);
            p8.Value = entity.PurchasingGroup;

            SqlParameter p9 = new SqlParameter("@WAERS", SqlDbType.Char, 5);
            cm.Parameters.Add(p9);
            p9.Value = entity.Currency;

            SqlParameter p10 = new SqlParameter("@WKURS", SqlDbType.Decimal, 11);
            cm.Parameters.Add(p10);
            if (entity.ExchangeRate.HasValue)
                p10.Value = entity.ExchangeRate;
            else
                p10.Value = DBNull.Value;

            SqlParameter p11 = new SqlParameter("@KDATB", SqlDbType.BigInt, 8);
            cm.Parameters.Add(p11);
            if (entity.ValidityStart.HasValue)
                p11.Value = entity.ValidityStart;
            else
                p11.Value = DBNull.Value;

            SqlParameter p12 = new SqlParameter("@KDATE", SqlDbType.BigInt, 8);
            cm.Parameters.Add(p12);
            if (entity.ValidityEnd.HasValue)
                p12.Value = entity.ValidityEnd;
            else
                p12.Value = DBNull.Value;

            SqlParameter p13 = new SqlParameter("@VERKF", SqlDbType.VarChar, 30);
            cm.Parameters.Add(p13);
            p13.Value = entity.SalesContactPerson;

            SqlParameter p14 = new SqlParameter("@TELF1", SqlDbType.VarChar, 16);
            cm.Parameters.Add(p14);
            p14.Value = entity.Telephone;

            SqlParameter p15 = new SqlParameter("@KTWRT", SqlDbType.Decimal, 13);
            cm.Parameters.Add(p15);
            if (entity.ContractValue.HasValue)
                p15.Value = entity.ContractValue;
            else
                p15.Value = DBNull.Value;

            SqlParameter p16 = new SqlParameter("@IHERZ", SqlDbType.VarChar, 12);
            cm.Parameters.Add(p16);
            p16.Value = entity.InternalReference;

            cm.ExecuteNonQuery();

            if (epTran == null)
                if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
        }
 public static void Insert(ContractHeader entity)
 {
     Insert(null, entity);
 }
        private static Collection<ContractHeader> Retrieve(EpTransaction epTran, string whereClause, string sortClaues)
        {
            Collection<ContractHeader> entities = new Collection<ContractHeader>();

            SqlCommand cm = new SqlCommand();
            cm.CommandType = CommandType.Text;

            //set connection
            SqlConnection connection;
            if (epTran == null)
                connection = DataManager.GetConnection();
            else
                connection = epTran.GetSqlConnection();
            if (connection.State != System.Data.ConnectionState.Open) connection.Open();
            cm.Connection = connection;

            //set transaction
            if (epTran != null)
                cm.Transaction = epTran.GetSqlTransaction();

            //Retrieve Data
            string selectCommand = "SELECT [EBELN],[BEDAT],[BSTYP],[BSART],[ERNAM],[LIFNR],[ZTERM],[EKGRP],[WAERS],[WKURS],[KDATB],[KDATE],[VERKF],[TELF1],[KTWRT],[IHERZ] FROM conthdr";
            if (!string.IsNullOrEmpty(whereClause)) selectCommand += " where " + whereClause;
            if (!string.IsNullOrEmpty(sortClaues)) selectCommand += " order by " + sortClaues;

            cm.CommandText = selectCommand;
            SqlDataReader rd = cm.ExecuteReader();
            while (rd.Read())
            {
                ContractHeader entity = new ContractHeader();
                entity.ContractNumber = rd["EBELN"].ToString();
                
                if (rd.IsDBNull(1))
                    entity.ContractDate = null;
                else
                    entity.ContractDate = Convert.ToInt64(rd["BEDAT"]);
                
                entity.ContractCategory = rd["BSTYP"].ToString();
                entity.DocumentType = rd["BSART"].ToString();
                entity.CreatedBy = rd["ERNAM"].ToString();
                entity.SupplierId = rd["LIFNR"].ToString();
                entity.PaymentTerms = rd["ZTERM"].ToString();
                entity.PurchasingGroup = rd["EKGRP"].ToString();
                entity.Currency = rd["WAERS"].ToString();

                if (rd.IsDBNull(9))
                    entity.ExchangeRate = null;
                else
                    entity.ExchangeRate = Convert.ToInt64(rd["WKURS"]);

                if (rd.IsDBNull(10))
                    entity.ValidityStart = null;
                else
                    entity.ValidityStart = Convert.ToInt64(rd["KDATB"]);

                if (rd.IsDBNull(11))
                    entity.ValidityEnd = null;
                else
                    entity.ValidityEnd = Convert.ToInt64(rd["KDATE"]);

                entity.SalesContactPerson = rd["VERKF"].ToString();
                entity.Telephone = rd["TELF1"].ToString();

                if (rd.IsDBNull(14))
                    entity.ContractValue = null;
                else
                    entity.ContractValue = Convert.ToInt64(rd["KTWRT"]);

                entity.InternalReference = rd["IHERZ"].ToString();

                entities.Add(entity);

            }
            // close reader
            rd.Close();

            if (epTran == null)
                if (connection.State != System.Data.ConnectionState.Closed) connection.Close();

            return entities;
        }
        public static void Delete(EpTransaction epTran, ContractHeader entity)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandType = CommandType.Text;

            //set connection
            SqlConnection connection;
            if (epTran == null)
                connection = DataManager.GetConnection();
            else
                connection = epTran.GetSqlConnection();
            if (connection.State != System.Data.ConnectionState.Open) connection.Open();
            cm.Connection = connection;

            //set transaction
            if (epTran != null)
                cm.Transaction = epTran.GetSqlTransaction();

            //Check whether record exists
            ContractHeader checkEntity = RetrieveByKey(epTran, entity.ContractNumber);
            if (checkEntity == null)
            {
                throw new Exception("Record doesn't exist.");
            }

            //Update 
            cm.CommandText = "DELETE FROM conthdr WHERE EBELN=@EBELN";
            SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
            cm.Parameters.Add(p1);
            p1.Value = entity.ContractNumber;
          
            cm.ExecuteNonQuery();

            if (epTran == null)
                if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
        }
 public static void Delete(ContractHeader entity)
 {
     Delete(null, entity);
 }
 public static void Update(ContractHeader entity)
 {
     Update(null, entity);
 }
Esempio n. 10
0
        public static void Insert(EpTransaction epTran, ContractHeader entity)
        {
            SqlCommand cm = new SqlCommand();

            cm.CommandType = CommandType.Text;

            //set connection
            SqlConnection connection;

            if (epTran == null)
            {
                connection = DataManager.GetConnection();
            }
            else
            {
                connection = epTran.GetSqlConnection();
            }
            if (connection.State != System.Data.ConnectionState.Open)
            {
                connection.Open();
            }
            cm.Connection = connection;

            //set transaction
            if (epTran != null)
            {
                cm.Transaction = epTran.GetSqlTransaction();
            }

            //Check whether record exists
            ContractHeader checkEntity = RetrieveByKey(epTran, entity.ContractNumber);

            if (checkEntity != null)
            {
                throw new Exception("Record already exists.");
            }

            //Insert
            cm.CommandText = "INSERT INTO conthdr ([EBELN],[BEDAT],[BSTYP],[BSART],[ERNAM],[LIFNR],[ZTERM],[EKGRP],[WAERS],[WKURS],[KDATB],[KDATE],[VERKF],[TELF1],[KTWRT],[IHERZ]) VALUES(@EBELN,@BEDAT,@BSTYP,@BSART,@ERNAM,@LIFNR,@ZTERM,@EKGRP,@WAERS,@WKURS,@KDATB,@KDATE,@VERKF,@TELF1,@KTWRT,@IHERZ)";

            SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.VarChar, 10);

            cm.Parameters.Add(p1);
            p1.Value = entity.ContractNumber;

            SqlParameter p2 = new SqlParameter("@BEDAT", SqlDbType.BigInt, 8);

            cm.Parameters.Add(p2);
            if (entity.ContractDate.HasValue)
            {
                p2.Value = entity.ContractDate;
            }
            else
            {
                p2.Value = DBNull.Value;
            }

            SqlParameter p3 = new SqlParameter("@BSTYP", SqlDbType.Char, 1);

            cm.Parameters.Add(p3);
            p3.Value = entity.ContractCategory;

            SqlParameter p4 = new SqlParameter("@BSART", SqlDbType.Char, 4);

            cm.Parameters.Add(p4);
            p4.Value = entity.DocumentType;

            SqlParameter p5 = new SqlParameter("@ERNAM", SqlDbType.VarChar, 12);

            cm.Parameters.Add(p5);
            p5.Value = entity.CreatedBy;

            SqlParameter p6 = new SqlParameter("@LIFNR", SqlDbType.VarChar, 10);

            cm.Parameters.Add(p6);
            p6.Value = entity.SupplierId;

            SqlParameter p7 = new SqlParameter("@ZTERM", SqlDbType.Char, 4);

            cm.Parameters.Add(p7);
            p7.Value = entity.PaymentTerms;

            SqlParameter p8 = new SqlParameter("@EKGRP", SqlDbType.Char, 3);

            cm.Parameters.Add(p8);
            p8.Value = entity.PurchasingGroup;

            SqlParameter p9 = new SqlParameter("@WAERS", SqlDbType.Char, 5);

            cm.Parameters.Add(p9);
            p9.Value = entity.Currency;

            SqlParameter p10 = new SqlParameter("@WKURS", SqlDbType.Decimal, 11);

            cm.Parameters.Add(p10);
            if (entity.ExchangeRate.HasValue)
            {
                p10.Value = entity.ExchangeRate;
            }
            else
            {
                p10.Value = DBNull.Value;
            }

            SqlParameter p11 = new SqlParameter("@KDATB", SqlDbType.BigInt, 8);

            cm.Parameters.Add(p11);
            if (entity.ValidityStart.HasValue)
            {
                p11.Value = entity.ValidityStart;
            }
            else
            {
                p11.Value = DBNull.Value;
            }

            SqlParameter p12 = new SqlParameter("@KDATE", SqlDbType.BigInt, 8);

            cm.Parameters.Add(p12);
            if (entity.ValidityEnd.HasValue)
            {
                p12.Value = entity.ValidityEnd;
            }
            else
            {
                p12.Value = DBNull.Value;
            }

            SqlParameter p13 = new SqlParameter("@VERKF", SqlDbType.VarChar, 30);

            cm.Parameters.Add(p13);
            p13.Value = entity.SalesContactPerson;

            SqlParameter p14 = new SqlParameter("@TELF1", SqlDbType.VarChar, 16);

            cm.Parameters.Add(p14);
            p14.Value = entity.Telephone;

            SqlParameter p15 = new SqlParameter("@KTWRT", SqlDbType.Decimal, 13);

            cm.Parameters.Add(p15);
            if (entity.ContractValue.HasValue)
            {
                p15.Value = entity.ContractValue;
            }
            else
            {
                p15.Value = DBNull.Value;
            }

            SqlParameter p16 = new SqlParameter("@IHERZ", SqlDbType.VarChar, 12);

            cm.Parameters.Add(p16);
            p16.Value = entity.InternalReference;

            cm.ExecuteNonQuery();

            if (epTran == null)
            {
                if (connection.State != System.Data.ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
        }
Esempio n. 11
0
 public static void Insert(ContractHeader entity)
 {
     Insert(null, entity);
 }
Esempio n. 12
0
        private static Collection <ContractHeader> Retrieve(EpTransaction epTran, string whereClause, string sortClaues)
        {
            Collection <ContractHeader> entities = new Collection <ContractHeader>();

            SqlCommand cm = new SqlCommand();

            cm.CommandType = CommandType.Text;

            //set connection
            SqlConnection connection;

            if (epTran == null)
            {
                connection = DataManager.GetConnection();
            }
            else
            {
                connection = epTran.GetSqlConnection();
            }
            if (connection.State != System.Data.ConnectionState.Open)
            {
                connection.Open();
            }
            cm.Connection = connection;

            //set transaction
            if (epTran != null)
            {
                cm.Transaction = epTran.GetSqlTransaction();
            }

            //Retrieve Data
            string selectCommand = "SELECT [EBELN],[BEDAT],[BSTYP],[BSART],[ERNAM],[LIFNR],[ZTERM],[EKGRP],[WAERS],[WKURS],[KDATB],[KDATE],[VERKF],[TELF1],[KTWRT],[IHERZ] FROM conthdr";

            if (!string.IsNullOrEmpty(whereClause))
            {
                selectCommand += " where " + whereClause;
            }
            if (!string.IsNullOrEmpty(sortClaues))
            {
                selectCommand += " order by " + sortClaues;
            }

            cm.CommandText = selectCommand;
            SqlDataReader rd = cm.ExecuteReader();

            while (rd.Read())
            {
                ContractHeader entity = new ContractHeader();
                entity.ContractNumber = rd["EBELN"].ToString();

                if (rd.IsDBNull(1))
                {
                    entity.ContractDate = null;
                }
                else
                {
                    entity.ContractDate = Convert.ToInt64(rd["BEDAT"]);
                }

                entity.ContractCategory = rd["BSTYP"].ToString();
                entity.DocumentType     = rd["BSART"].ToString();
                entity.CreatedBy        = rd["ERNAM"].ToString();
                entity.SupplierId       = rd["LIFNR"].ToString();
                entity.PaymentTerms     = rd["ZTERM"].ToString();
                entity.PurchasingGroup  = rd["EKGRP"].ToString();
                entity.Currency         = rd["WAERS"].ToString();

                if (rd.IsDBNull(9))
                {
                    entity.ExchangeRate = null;
                }
                else
                {
                    entity.ExchangeRate = Convert.ToInt64(rd["WKURS"]);
                }

                if (rd.IsDBNull(10))
                {
                    entity.ValidityStart = null;
                }
                else
                {
                    entity.ValidityStart = Convert.ToInt64(rd["KDATB"]);
                }

                if (rd.IsDBNull(11))
                {
                    entity.ValidityEnd = null;
                }
                else
                {
                    entity.ValidityEnd = Convert.ToInt64(rd["KDATE"]);
                }

                entity.SalesContactPerson = rd["VERKF"].ToString();
                entity.Telephone          = rd["TELF1"].ToString();

                if (rd.IsDBNull(14))
                {
                    entity.ContractValue = null;
                }
                else
                {
                    entity.ContractValue = Convert.ToInt64(rd["KTWRT"]);
                }

                entity.InternalReference = rd["IHERZ"].ToString();

                entities.Add(entity);
            }
            // close reader
            rd.Close();

            if (epTran == null)
            {
                if (connection.State != System.Data.ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return(entities);
        }
Esempio n. 13
0
 public static void Delete(ContractHeader entity)
 {
     Delete(null, entity);
 }
Esempio n. 14
0
 public static void Update(ContractHeader entity)
 {
     Update(null, entity);
 }