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 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);
}
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(ContractHeader entity)
{
Delete(null, entity);
}
public static void Update(ContractHeader entity)
{
Update(null, entity);
}