public static void Delete(EpTransaction epTran, PurchaseOrderItem 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
PurchaseOrderItem checkEntity = RetrieveByKey(epTran, entity.OrderNumber, entity.PurchaseItemSequenceNumber);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "DELETE FROM PURDTL WHERE EBELN=@EBELN AND EBELP=@EBELP";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.OrderNumber;
SqlParameter p2 = new SqlParameter("@EBELP", SqlDbType.Char, 5);
cm.Parameters.Add(p2);
p2.Value = entity.PurchaseItemSequenceNumber;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
public static void Delete(EpTransaction epTran, PurchaseOrderHeaderText 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
PurchaseOrderHeaderText checkEntity = RetrieveByKey(epTran, entity.OrderNumber, entity.TextSerialNumber);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "DELETE FROM puhtxt WHERE EBELN=@EBELN AND TXTITM=@TXTITM";
SqlParameter p3 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p3);
p3.Value = entity.OrderNumber;
SqlParameter p4 = new SqlParameter("@TXTITM", SqlDbType.Char, 5);
cm.Parameters.Add(p4);
p4.Value = entity.TextSerialNumber;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
public static void Delete(EpTransaction epTran, Supplier 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
Supplier checkEntity = RetrieveByKey(epTran, entity.SupplierID);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "DELETE FROM vndmst WHERE LIFNR=@LIFNR";
SqlParameter p1 = new SqlParameter("@LIFNR", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.SupplierID;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
public static void Insert(EpTransaction epTran, PurchaseOrderServiceItem 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
PurchaseOrderServiceItem checkEntity = RetrieveByKey(epTran, entity.OrderNumber, entity.ItemSequenceNumber, entity.ServiceLineNumber);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO PURSRV ([EBELN],[EBELP],[LBLN1],[KTEXT1], [MENGE], [PREIS], [RECSTS]) VALUES(@EBELN,@EBELP, @LBLN1, @KTEXT1, @MENGE, @PREIS, @RECSTS)";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.OrderNumber;
SqlParameter p2 = new SqlParameter("@EBELP", SqlDbType.Char, 5);
cm.Parameters.Add(p2);
p2.Value = entity.ItemSequenceNumber;
SqlParameter p3 = new SqlParameter("@LBLN1", SqlDbType.Char, 10);
cm.Parameters.Add(p3);
p3.Value = entity.ServiceLineNumber;
SqlParameter p4 = new SqlParameter("@KTEXT1", SqlDbType.VarChar, 40);
cm.Parameters.Add(p4);
p4.Value = entity.ServiceDescription;
SqlParameter p5 = new SqlParameter("@MENGE", SqlDbType.Decimal,13);
cm.Parameters.Add(p5);
if (entity.ServiceQuantity.HasValue)
p5.Value = entity.ServiceQuantity.Value;
else
p5.Value = DBNull.Value;
SqlParameter p6 = new SqlParameter("@PREIS", SqlDbType.Decimal,11);
cm.Parameters.Add(p6);
if (entity.ServicePrice.HasValue)
p6.Value = entity.ServicePrice.Value;
else
p6.Value = DBNull.Value;
SqlParameter p7 = new SqlParameter("@RECSTS", SqlDbType.Char,1);
cm.Parameters.Add(p7);
p7.Value = entity.RecordStatus;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
public static void Update(EpTransaction epTran,PurchaseOrderServiceItem 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
PurchaseOrderServiceItem checkEntity = RetrieveByKey(epTran, entity.OrderNumber, entity.ItemSequenceNumber,entity.ServiceLineNumber);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "UPDATE PURSRV SET KTEXT1=@LTEXT1,MENGE=@MENGE,PREIS=@PREIS,RECSTS=@RECSTS WHERE EBELN=@EBELN AND EBELP=@EBELP AND LBLN1=@LBLN1";
SqlParameter p1 = new SqlParameter("@KTEXT1", SqlDbType.VarChar, 40);
cm.Parameters.Add(p1);
p1.Value = entity.ServiceDescription;
SqlParameter p2 = new SqlParameter("@MENGE", SqlDbType.Decimal, 13);
cm.Parameters.Add(p2);
if (entity.ServiceQuantity.HasValue)
p6.Value = entity.ServiceQuantity.Value;
else
p6.Value = DBNull.Value;
SqlParameter p3 = new SqlParameter("@PREIS", SqlDbType.Decimal, 11);
cm.Parameters.Add(p3);
if (entity.ServicePrice.HasValue)
p3.Value = entity.ServicePrice.Value;
else
p3.Value = DBNull.Value;
SqlParameter p4 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p4);
p4.Value = entity.RecordStatus;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
public static void Insert(EpTransaction epTran, Supplier 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
Supplier checkEntity = RetrieveByKey(epTran, entity.SupplierID);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO vndmst ([LIFNR],[PASSWD],[ANRED],[NAME],[ADDRESS],[PSTLZ],[KORT],[REGIO],[LAND1],[TELF1],[TELF2],[TELFX],[SPREQ],[EMAIL],[RECSTS]) VALUES(@LIFNR,@PASSWD,@ANRED,@NAME,@ADDRESS,@PSTLZ,@KORT,@REGIO,@LAND1,@TELF1,@TELF2,@TELFX,@SPREQ,@EMAIL,@RECSTS))";
SqlParameter p1 = new SqlParameter("@LIFNR", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.SupplierID;
SqlParameter p2 = new SqlParameter("@PASSWD", SqlDbType.Char, 10);
cm.Parameters.Add(p2);
p2.Value = entity.Password;
SqlParameter p3 = new SqlParameter("@ANRED", SqlDbType.VarChar, 15);
cm.Parameters.Add(p3);
p3.Value = entity.Title;
SqlParameter p4 = new SqlParameter("@NAME", SqlDbType.VarChar, 80);
cm.Parameters.Add(p4);
p4.Value = entity.SupplierName;
SqlParameter p5 = new SqlParameter("@ADDRESS", SqlDbType.VarChar, 60);
cm.Parameters.Add(p5);
p5.Value = entity.SupplierAddress;
SqlParameter p6 = new SqlParameter("@PSTLZ", SqlDbType.VarChar, 10);
cm.Parameters.Add(p6);
p6.Value = entity.PostalCode;
SqlParameter p7 = new SqlParameter("@KORT", SqlDbType.VarChar, 40);
cm.Parameters.Add(p7);
p7.Value = entity.Country;
SqlParameter p8 = new SqlParameter("@REGIO", SqlDbType.Char, 3);
cm.Parameters.Add(p8);
p8.Value = entity.Region;
SqlParameter p9 = new SqlParameter("@LAND1", SqlDbType.Char, 3);
cm.Parameters.Add(p9);
p9.Value = entity.CountryCode;
SqlParameter p10 = new SqlParameter("@TELF1", SqlDbType.VarChar, 16);
cm.Parameters.Add(p10);
p10.Value = entity.Telephone1;
SqlParameter p11 = new SqlParameter("@TELF2", SqlDbType.VarChar, 16);
cm.Parameters.Add(p11);
p11.Value = entity.Telephone2;
SqlParameter p12 = new SqlParameter("@TELFX", SqlDbType.VarChar, 31);
cm.Parameters.Add(p12);
p12.Value = entity.FaxNo;
SqlParameter p13 = new SqlParameter("@SPREQ", SqlDbType.Char, 3);
cm.Parameters.Add(p13);
p13.Value = entity.SPREQ;
SqlParameter p14 = new SqlParameter("@EMAIL", SqlDbType.VarChar, 100);
cm.Parameters.Add(p14);
p14.Value = entity.EmailID;
SqlParameter p15 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p15);
p15.Value = entity.RecordStatus;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
public static void Update(EpTransaction epTran, PurchaseOrderItemSchedule 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
PurchaseOrderItemSchedule checkEntity = RetrieveByKey(epTran, entity.PurchaseOrderNumber, entity.PurchaseOrderItemSequence, entity.PurchaseOrderScheduleSequence);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "UPDATE PURSCH SET MATNR = @MATNR, SLFDAT = @SLFDAT , MENGE = @MENGE, EINDT = @EINDT , WEMNG = @WEMNG, ACKDT = @ACKDT , RECSTS = @RECSTS, PRMDT = @PRMDT WHERE EBELN=@EBELN AND EBELP=@EBELP AND ETENR = @ETENR ";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.PurchaseOrderNumber;
SqlParameter p2 = new SqlParameter("@EBELP", SqlDbType.Char, 5);
cm.Parameters.Add(p2);
p2.Value = entity.PurchaseOrderItemSequence;
SqlParameter p3 = new SqlParameter("@MATNR", SqlDbType.Char, 18);
cm.Parameters.Add(p3);
p3.Value = entity.MaterialNumber;
SqlParameter p4 = new SqlParameter("@ETENR", SqlDbType.Char, 4);
cm.Parameters.Add(p4);
p4.Value = entity.PurchaseOrderScheduleSequence;
SqlParameter p5 = new SqlParameter("@SLFDAT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p5);
if(entity.OrderItemScheduleDate.HasValue)
p5.Value = entity.OrderItemScheduleDate.Value;
else
p5.Value = DBNull.Value ;
SqlParameter p6 = new SqlParameter("@MENGE", SqlDbType.Decimal, 4);
cm.Parameters.Add(p6);
if (entity.DeliveryScheduleQuantity.HasValue)
p6.Value = entity.DeliveryScheduleQuantity;
else
p6.Value = DBNull.Value;
SqlParameter p7 = new SqlParameter("@EINDT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p7);
if (entity.DeliveryDate.HasValue)
p7.Value = entity.DeliveryDate;
else
p7.Value = DBNull.Value;
SqlParameter p8 = new SqlParameter("@WEMNG", SqlDbType.Decimal, 4);
cm.Parameters.Add(p8);
if (entity.DeliveredQuantity.HasValue)
p8.Value = entity.DeliveredQuantity;
else
p8.Value = DBNull.Value;
SqlParameter p9 = new SqlParameter("@ACKDT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p9);
if (entity.AcknowledgementDate.HasValue)
p9.Value = entity.AcknowledgementDate;
else
p9.Value = DBNull.Value;
SqlParameter p10 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p10);
p10.Value = entity.RecordStatus;
SqlParameter p11 = new SqlParameter("@PRMDT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p11);
if (entity.ExpeditingPromiseDate.HasValue)
p11.Value = entity.ExpeditingPromiseDate;
else
p11.Value = DBNull.Value;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
private static Collection<PurchaseOrderHeader> Retrieve(EpTransaction epTran, string whereClause, string sortClaues)
{
Collection<PurchaseOrderHeader> entities = new Collection<PurchaseOrderHeader>();
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],[LIFNR],[BEDAT],[AMTPR],[GSTPR],[WAERS],[ZTERM],[BUYER],[AD_TLNMBR],[VERKF],[ADRNR_TXT],[REMARK],[STAT],[RECSTS],[ACKSTS] FROM purhdr";
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())
{
PurchaseOrderHeader entity = new PurchaseOrderHeader();
entity.OrderNumber = rd["EBELN"].ToString();
entity.SupplierID = rd["LIFNR"].ToString();
if (rd.IsDBNull(2))
entity.OrderDate = null;
else
entity.OrderDate = Convert.ToInt64(rd["BEDAT"]);
if (rd.IsDBNull(3))
entity.OrderAmount = null;
else
entity.OrderAmount = Convert.ToInt64(rd["AMTPR"]);
if (rd.IsDBNull(3))
entity.GstAmount = null;
else
entity.GstAmount = Convert.ToInt64(rd["GSTPR"]);
entity.CurrencyCode = rd["WAERS"].ToString();
entity.PaymentTerms = rd["ZTERM"].ToString();
entity.BuyerName = rd["BUYER"].ToString();
entity.AddressNumber = rd["AD_TLNMBR"].ToString();
entity.SalesPerson = rd["VERKF"].ToString();
entity.ShipmentAddress = rd["ADRNR_TXT"].ToString();
entity.Remarks = rd["REMARK"].ToString();
entity.OrderStatus = rd["STAT"].ToString();
entity.RecordStatus = rd["RECSTS"].ToString();
entity.AcknowledgeStatus = rd["ACKSTS"].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 Insert(EpTransaction epTran, ContractItem 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
ContractItem checkEntity = RetrieveByKey(epTran, entity.ContractNumber, entity.ContractItemSequence);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO contitm ([EBELN],[EBELP],[TXZ01],[MATNR],[WERKS],[MATKL],[KTMNG],[MEINS],[NETPR],[PEINH],[BRTWR],[ANFNR],[BANFN],[AFNAM]) VALUES(@EBELN,@EBELP,@TXZ01,@MATNR,@WERKS,@MATKL,@KTMNG,@MEINS,@NETPR,@PEINH,@BRTWR,@ANFNR,@BANFN,@AFNAM)";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.VarChar, 10);
cm.Parameters.Add(p1);
p1.Value = entity.ContractNumber;
SqlParameter p2 = new SqlParameter("@EBELP", SqlDbType.VarChar, 5);
cm.Parameters.Add(p2);
p2.Value = entity.ContractItemSequence;
SqlParameter p3 = new SqlParameter("@TXZ01", SqlDbType.VarChar, 40);
cm.Parameters.Add(p3);
p3.Value = entity.Description;
SqlParameter p4 = new SqlParameter("@MATNR", SqlDbType.VarChar, 18);
cm.Parameters.Add(p4);
p4.Value = entity.MaterialNumber;
SqlParameter p5 = new SqlParameter("@WERKS", SqlDbType.Char, 4);
cm.Parameters.Add(p5);
p5.Value = entity.Plant;
SqlParameter p6 = new SqlParameter("@MATKL", SqlDbType.VarChar, 9);
cm.Parameters.Add(p6);
p6.Value = entity.MaterialGroup;
SqlParameter p7 = new SqlParameter("@KTMNG", SqlDbType.Decimal, 13);
cm.Parameters.Add(p7);
if (entity.TargetQuantity.HasValue)
p7.Value = entity.TargetQuantity;
else
p7.Value = DBNull.Value;
SqlParameter p8 = new SqlParameter("@MEINS", SqlDbType.Char, 3);
cm.Parameters.Add(p8);
p8.Value = entity.UnitOfMeasure;
SqlParameter p9 = new SqlParameter("@NETPR", SqlDbType.Decimal, 11);
cm.Parameters.Add(p9);
if (entity.UnitPrice.HasValue)
p9.Value = entity.UnitPrice;
else
p9.Value = DBNull.Value;
SqlParameter p10 = new SqlParameter("@PEINH", SqlDbType.Decimal, 5);
cm.Parameters.Add(p10);
if (entity.PricePerUnit.HasValue)
p10.Value = entity.PricePerUnit;
else
p10.Value = DBNull.Value;
SqlParameter p11 = new SqlParameter("@BRTWR", SqlDbType.Decimal, 13);
cm.Parameters.Add(p11);
if (entity.NetValue.HasValue)
p11.Value = entity.NetValue;
else
p11.Value = DBNull.Value;
SqlParameter p12 = new SqlParameter("@ANFNR", SqlDbType.VarChar, 10);
cm.Parameters.Add(p12);
p12.Value = entity.RFQNumber;
SqlParameter p13 = new SqlParameter("@BANFN", SqlDbType.VarChar, 10);
cm.Parameters.Add(p13);
p13.Value = entity.RequisitionNumber;
SqlParameter p14 = new SqlParameter("@AFNAM", SqlDbType.VarChar, 12);
cm.Parameters.Add(p14);
p14.Value = entity.Requisitioner;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
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 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();
}
}
}
private static Collection<Supplier> Retrieve(EpTransaction epTran, string whereClause, string sortClaues)
{
Collection<Supplier> entities = new Collection<Supplier>();
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 [LIFNR],[PASSWD],[ANRED],[NAME],[ADDRESS],[PSTLZ],[KORT],[REGIO],[LAND1],[TELF1],[TELF2],[TELFX],[SPREQ],[EMAIL],[RECSTS] FROM vndmst";
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())
{
Supplier entity = new Supplier();
entity.SupplierID = rd["LIFNR"].ToString();
entity.Password = rd["PASSWD"].ToString();
entity.Title = rd["ANRED"].ToString();
entity.SupplierName = rd["NAME"].ToString();
entity.SupplierAddress = rd["ADDRESS"].ToString();
entity.PostalCode = rd["PSTLZ"].ToString();
entity.Country = rd["KORT"].ToString();
entity.Region = rd["REGIO"].ToString();
entity.CountryCode = rd["LAND1"].ToString();
entity.Telephone1 = rd["TELF1"].ToString();
entity.Telephone2 = rd["TELF2"].ToString();
entity.FaxNo = rd["TELFX"].ToString();
entity.SPREQ = rd["SPREQ"].ToString();
entity.EmailID = rd["EMAIL"].ToString();
entity.RecordStatus = rd["RECSTS"].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 Insert(EpTransaction epTran, Supplier 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
Supplier checkEntity = RetrieveByKey(epTran, entity.SupplierID);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO vndmst ([LIFNR],[PASSWD],[ANRED],[NAME],[ADDRESS],[PSTLZ],[KORT],[REGIO],[LAND1],[TELF1],[TELF2],[TELFX],[SPREQ],[EMAIL],[RECSTS]) VALUES(@LIFNR,@PASSWD,@ANRED,@NAME,@ADDRESS,@PSTLZ,@KORT,@REGIO,@LAND1,@TELF1,@TELF2,@TELFX,@SPREQ,@EMAIL,@RECSTS))";
SqlParameter p1 = new SqlParameter("@LIFNR", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.SupplierID;
SqlParameter p2 = new SqlParameter("@PASSWD", SqlDbType.Char, 10);
cm.Parameters.Add(p2);
p2.Value = entity.Password;
SqlParameter p3 = new SqlParameter("@ANRED", SqlDbType.VarChar, 15);
cm.Parameters.Add(p3);
p3.Value = entity.Title;
SqlParameter p4 = new SqlParameter("@NAME", SqlDbType.VarChar, 80);
cm.Parameters.Add(p4);
p4.Value = entity.SupplierName;
SqlParameter p5 = new SqlParameter("@ADDRESS", SqlDbType.VarChar, 60);
cm.Parameters.Add(p5);
p5.Value = entity.SupplierAddress;
SqlParameter p6 = new SqlParameter("@PSTLZ", SqlDbType.VarChar, 10);
cm.Parameters.Add(p6);
p6.Value = entity.PostalCode;
SqlParameter p7 = new SqlParameter("@KORT", SqlDbType.VarChar, 40);
cm.Parameters.Add(p7);
p7.Value = entity.Country;
SqlParameter p8 = new SqlParameter("@REGIO", SqlDbType.Char, 3);
cm.Parameters.Add(p8);
p8.Value = entity.Region;
SqlParameter p9 = new SqlParameter("@LAND1", SqlDbType.Char, 3);
cm.Parameters.Add(p9);
p9.Value = entity.CountryCode;
SqlParameter p10 = new SqlParameter("@TELF1", SqlDbType.VarChar, 16);
cm.Parameters.Add(p10);
p10.Value = entity.Telephone1;
SqlParameter p11 = new SqlParameter("@TELF2", SqlDbType.VarChar, 16);
cm.Parameters.Add(p11);
p11.Value = entity.Telephone2;
SqlParameter p12 = new SqlParameter("@TELFX", SqlDbType.VarChar, 31);
cm.Parameters.Add(p12);
p12.Value = entity.FaxNo;
SqlParameter p13 = new SqlParameter("@SPREQ", SqlDbType.Char, 3);
cm.Parameters.Add(p13);
p13.Value = entity.SPREQ;
SqlParameter p14 = new SqlParameter("@EMAIL", SqlDbType.VarChar, 100);
cm.Parameters.Add(p14);
p14.Value = entity.EmailID;
SqlParameter p15 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p15);
p15.Value = entity.RecordStatus;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
public static void Insert(EpTransaction epTran, PurchaseOrderServiceTask 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
PurchaseOrderServiceTask checkEntity = RetrieveByKey(epTran, entity.ServiceLineNumber, entity.ServiceLineSequence);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO PURSRH ([LBLN1],[EXTROW],[SRVPOS],[MENGE], [MEINS], [SBRTWR], [KTEXT1]) VALUES(@LBLN1,@EXTROW, @SRVPOS, @MENGE, @MEINS, @SBRTWR, @KTEXT1)";
SqlParameter p1 = new SqlParameter("@LBLN1", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.ServiceLineNumber;
SqlParameter p2 = new SqlParameter("@EXTROW", SqlDbType.Char, 10);
cm.Parameters.Add(p2);
p2.Value = entity.ServiceLineSequence;
SqlParameter p3 = new SqlParameter("@SRVPOS", SqlDbType.Char, 18);
cm.Parameters.Add(p3);
p3.Value = entity.ServiceMaterial;
SqlParameter p4 = new SqlParameter("@MENGE", SqlDbType.Decimal, 13);
cm.Parameters.Add(p4);
if (entity.ServiceQuantity.HasValue)
{
p4.Value = entity.ServiceQuantity;
}
else
{
p4.Value = DBNull.Value;
}
SqlParameter p5 = new SqlParameter("@MEINS", SqlDbType.Char, 3);
cm.Parameters.Add(p5);
p5.Value = entity.UnitOfMeasure;
SqlParameter p6 = new SqlParameter("@SBRTWR", SqlDbType.Decimal, 11);
cm.Parameters.Add(p6);
if (entity.ServicePrice.HasValue)
{
p6.Value = entity.ServicePrice;
}
else
{
p6.Value = DBNull.Value;
}
SqlParameter p7 = new SqlParameter("@KTEXT1", SqlDbType.VarChar, 40);
cm.Parameters.Add(p7);
p7.Value = entity.ServiceText;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
private static Collection <PurchaseOrderServiceTask> Retrieve(EpTransaction epTran, string whereClause, string sortClaues)
{
Collection <PurchaseOrderServiceTask> entities = new Collection <PurchaseOrderServiceTask>();
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 [LBLN1],[EXTROW],[SRVPOS],[MENGE],[MEINS],[SBRTWR],[KTEXT1] FROM PURSRH";
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())
{
PurchaseOrderServiceTask entity = new PurchaseOrderServiceTask();
entity.ServiceLineNumber = rd["LBLN1"].ToString();
entity.ServiceLineSequence = rd["EXTROW"].ToString();
entity.ServiceMaterial = rd["SRVPOS"].ToString();
if (rd.IsDBNull(3))
{
entity.ServiceQuantity = null;
}
else
{
entity.ServiceQuantity = Convert.ToDecimal(rd["MENGE"].ToString());
}
entity.UnitOfMeasure = rd["MEINS"].ToString();
if (rd.IsDBNull(5))
{
entity.ServicePrice = null;
}
else
{
entity.ServicePrice = Convert.ToDecimal(rd["SBRTWR"].ToString());
}
entity.ServiceText = rd["KTEXT1"].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 Update(EpTransaction epTran, PurchaseOrderServiceTask 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
PurchaseOrderServiceTask checkEntity = RetrieveByKey(epTran, entity.ServiceLineNumber, entity.ServiceLineSequence);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "UPDATE PURSRH SET SRVPOS=@SRVPOS,MENGE=@MENGE,MEINS=@MEINS,SBRTWR=@SBRTWR, KTEXT1=@KTEXT1 WHERE LBLN1=@LBLN1 AND EXTROW=@EXTROW";
SqlParameter p1 = new SqlParameter("@SRVPOS", SqlDbType.Char, 18);
cm.Parameters.Add(p1);
p1.Value = entity.ServiceLineSequence;
SqlParameter p2 = new SqlParameter("@MENGE", SqlDbType.Decimal);
cm.Parameters.Add(p2);
if (entity.ServiceQuantity.HasValue)
{
p2.Value = entity.ServiceQuantity;
}
else
{
p2.Value = DBNull.Value;
}
SqlParameter p3 = new SqlParameter("@MEINS", SqlDbType.Char, 3);
cm.Parameters.Add(p3);
p3.Value = entity.UnitOfMeasure;
SqlParameter p4 = new SqlParameter("@SBRTWR", SqlDbType.Decimal, 13);
cm.Parameters.Add(p4);
if (entity.ServicePrice.HasValue)
{
p4.Value = entity.ServicePrice;
}
else
{
p4.Value = DBNull.Value;
}
SqlParameter p5 = new SqlParameter("@KTEXT1", SqlDbType.VarChar, 40);
cm.Parameters.Add(p5);
p5.Value = entity.ServiceText;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
private static Collection <PurchaseOrderServiceItem> Retrieve(EpTransaction epTran, string whereClause, string sortClaues)
{
Collection <PurchaseOrderServiceItem> entities = new Collection <PurchaseOrderServiceItem>();
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],[EBELP],[LBLN1],[KTEXT1],[MENGE],[PREIS],[RECSTS] FROM PURSRV";
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())
{
PurchaseOrderServiceItem entity = new PurchaseOrderServiceItem();
entity.OrderNumber = rd["EBELN"].ToString();
entity.ItemSequenceNumber = rd["EBELP"].ToString();
entity.ServiceLineNumber = rd["LBLN1"].ToString();
entity.ServiceDescription = rd["KTEXT1"].ToString();
if (rd.IsDBNull(4))
{
entity.ServiceQuantity = null;
}
else
{
entity.ServiceQuantity = Convert.ToDecimal(rd["MENGE"].ToString());
}
if (rd.IsDBNull(5))
{
entity.ServicePrice = null;
}
else
{
entity.ServicePrice = Convert.ToDecimal(rd["PREIS"].ToString());
}
entity.RecordStatus = rd["RECSTS"].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 Update(EpTransaction epTran, PurchaseOrderHeader 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
PurchaseOrderHeader checkEntity = RetrieveByKey(epTran, entity.OrderNumber);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "UPDATE purhdr SET LIFNR=@LIFNR,BEDAT=@BEDAT,AMTPR=@AMTPR,GSTPR=@GSTPR,WAERS=@WAERS,ZTERM=@ZTERM,BUYER=@BUYER,AD_TLNMBR=@AD_TLNMBR,VERKF=@VERKF,ADRNR_TXT=@ADRNR_TXT,REMARK=@REMARK,STAT=@STAT,RECSTS=@RECSTS,ACKSTS=@ACKSTS WHERE EBELN=@EBELN";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.OrderNumber;
SqlParameter p2 = new SqlParameter("@LIFNR", SqlDbType.Char, 10);
cm.Parameters.Add(p2);
p2.Value = entity.SupplierID;
SqlParameter p3 = new SqlParameter("@BEDAT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p3);
if (entity.OrderDate.HasValue)
{
p3.Value = entity.OrderDate;
}
else
{
p3.Value = DBNull.Value;
}
SqlParameter p4 = new SqlParameter("@AMTPR", SqlDbType.Decimal, 11);
cm.Parameters.Add(p4);
if (entity.OrderAmount.HasValue)
{
p4.Value = entity.OrderAmount;
}
else
{
p4.Value = DBNull.Value;
}
SqlParameter p5 = new SqlParameter("@GSTPR", SqlDbType.Decimal, 11);
cm.Parameters.Add(p5);
if (entity.GstAmount.HasValue)
{
p5.Value = entity.GstAmount;
}
else
{
p5.Value = DBNull.Value;
}
SqlParameter p6 = new SqlParameter("@WAERS", SqlDbType.Char, 5);
cm.Parameters.Add(p6);
p6.Value = entity.CurrencyCode;
SqlParameter p7 = new SqlParameter("@ZTERM", SqlDbType.Char, 4);
cm.Parameters.Add(p7);
p7.Value = entity.PaymentTerms;
SqlParameter p8 = new SqlParameter("@BUYER", SqlDbType.VarChar, 31);
cm.Parameters.Add(p8);
p8.Value = entity.BuyerName;
SqlParameter p9 = new SqlParameter("@AD_TLNMBR", SqlDbType.VarChar, 30);
cm.Parameters.Add(p9);
p9.Value = entity.AddressNumber;
SqlParameter p10 = new SqlParameter("@VERKF", SqlDbType.VarChar, 30);
cm.Parameters.Add(p10);
p10.Value = entity.SalesPerson;
SqlParameter p11 = new SqlParameter("@ADRNR_TXT", SqlDbType.VarChar, 80);
cm.Parameters.Add(p11);
p11.Value = entity.ShipmentAddress;
SqlParameter p12 = new SqlParameter("@REMARK", SqlDbType.VarChar, 60);
cm.Parameters.Add(p12);
p12.Value = entity.Remarks;
SqlParameter p13 = new SqlParameter("@STAT", SqlDbType.Char, 3);
cm.Parameters.Add(p13);
p13.Value = entity.OrderStatus;
SqlParameter p14 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p14);
p14.Value = entity.RecordStatus;
SqlParameter p15 = new SqlParameter("@ACKSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p15);
p15.Value = entity.AcknowledgeStatus;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
public static void Update(EpTransaction epTran, PurchaseOrderHeader 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
PurchaseOrderHeader checkEntity = RetrieveByKey(epTran, entity.OrderNumber);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "UPDATE purhdr SET LIFNR=@LIFNR,BEDAT=@BEDAT,AMTPR=@AMTPR,GSTPR=@GSTPR,WAERS=@WAERS,ZTERM=@ZTERM,BUYER=@BUYER,AD_TLNMBR=@AD_TLNMBR,VERKF=@VERKF,ADRNR_TXT=@ADRNR_TXT,REMARK=@REMARK,STAT=@STAT,RECSTS=@RECSTS,ACKSTS=@ACKSTS WHERE EBELN=@EBELN";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.OrderNumber;
SqlParameter p2 = new SqlParameter("@LIFNR", SqlDbType.Char, 10);
cm.Parameters.Add(p2);
p2.Value = entity.SupplierID;
SqlParameter p3 = new SqlParameter("@BEDAT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p3);
if (entity.OrderDate.HasValue)
p3.Value = entity.OrderDate;
else
p3.Value = DBNull.Value;
SqlParameter p4 = new SqlParameter("@AMTPR", SqlDbType.Decimal, 11);
cm.Parameters.Add(p4);
if (entity.OrderAmount.HasValue)
p4.Value = entity.OrderAmount;
else
p4.Value = DBNull.Value;
SqlParameter p5 = new SqlParameter("@GSTPR", SqlDbType.Decimal, 11);
cm.Parameters.Add(p5);
if (entity.GstAmount.HasValue)
p5.Value = entity.GstAmount;
else
p5.Value = DBNull.Value;
SqlParameter p6 = new SqlParameter("@WAERS", SqlDbType.Char, 5);
cm.Parameters.Add(p6);
p6.Value = entity.CurrencyCode;
SqlParameter p7 = new SqlParameter("@ZTERM", SqlDbType.Char, 4);
cm.Parameters.Add(p7);
p7.Value = entity.PaymentTerms;
SqlParameter p8 = new SqlParameter("@BUYER", SqlDbType.VarChar, 31);
cm.Parameters.Add(p8);
p8.Value = entity.BuyerName;
SqlParameter p9 = new SqlParameter("@AD_TLNMBR", SqlDbType.VarChar, 30);
cm.Parameters.Add(p9);
p9.Value = entity.AddressNumber;
SqlParameter p10 = new SqlParameter("@VERKF", SqlDbType.VarChar, 30);
cm.Parameters.Add(p10);
p10.Value = entity.SalesPerson;
SqlParameter p11 = new SqlParameter("@ADRNR_TXT", SqlDbType.VarChar, 80);
cm.Parameters.Add(p11);
p11.Value = entity.ShipmentAddress;
SqlParameter p12 = new SqlParameter("@REMARK", SqlDbType.VarChar, 60);
cm.Parameters.Add(p12);
p12.Value = entity.Remarks;
SqlParameter p13 = new SqlParameter("@STAT", SqlDbType.Char, 3);
cm.Parameters.Add(p13);
p13.Value = entity.OrderStatus;
SqlParameter p14 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p14);
p14.Value = entity.RecordStatus;
SqlParameter p15 = new SqlParameter("@ACKSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p15);
p15.Value = entity.AcknowledgeStatus;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
public static void Insert(EpTransaction epTran, PurchaseOrderItem 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
PurchaseOrderItem checkEntity = RetrieveByKey(epTran, entity.PurchaseOrderNumber, entity.PurchaseItemSequenceNumber);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO PURDTL ([EBELN],[EBELP],[PSTYP],[MATNR],[TXZ01],[BISMT],[MENGE],[PEINH],[MEINS],[NETPR],[REMARK],[WEMNG],[TEXT80],[AUFNR],[LGORT_D],[ASFNR],[STS2],[RECSTS],[ACKSTS]) VALUES(@EBELN, @EBELP, @PSTYP, @MATNR, @TXZ01,@BISMT, @MENGE, @PEINH, @MEINS , @NETPR, @REMARK, @WEMNG, @TEXT80, @AUFNR, @LGORT_D, @ASFNR, @STS2, @RECSTS, @ACKSTS)";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.PurchaseOrderNumber;
SqlParameter p2 = new SqlParameter("@EBELP", SqlDbType.Char, 5);
cm.Parameters.Add(p2);
p2.Value = entity.PurchaseItemSequenceNumber;
SqlParameter p3 = new SqlParameter("@PSTYP", SqlDbType.Char, 1);
cm.Parameters.Add(p3);
p3.Value = entity.PurchaseOrderType;
SqlParameter p4 = new SqlParameter("@MATNR", SqlDbType.Char, 18);
cm.Parameters.Add(p4);
p4.Value = entity.MaterialNumber;
SqlParameter p5 = new SqlParameter("@TXZ01", SqlDbType.VarChar, 40);
cm.Parameters.Add(p5);
p5.Value = entity.ShortText;
SqlParameter p6 = new SqlParameter("@BISMT", SqlDbType.Char, 18);
cm.Parameters.Add(p6);
p6.Value = entity.OldMaterialNumber;
SqlParameter p7 = new SqlParameter("@MENGE", SqlDbType.Decimal, 13);
cm.Parameters.Add(p7);
if (entity.OrderQuantity.HasValue)
{
p7.Value = entity.OrderQuantity.Value;
}
else
{
p7.Value = DBNull.Value;
}
SqlParameter p8 = new SqlParameter("@PEINH", SqlDbType.Decimal, 13);
cm.Parameters.Add(p8);
if (entity.PricePerUnit.HasValue)
{
p8.Value = entity.PricePerUnit.Value;
}
else
{
p7.Value = DBNull.Value;
}
SqlParameter p9 = new SqlParameter("@MEINS", SqlDbType.Char, 3);
cm.Parameters.Add(p9);
p9.Value = entity.UnitofMeasure;
SqlParameter p10 = new SqlParameter("@NETPR", SqlDbType.Decimal, 13);
cm.Parameters.Add(p10);
if (entity.NetPrice.HasValue)
{
p10.Value = entity.NetPrice.Value;
}
else
{
p10.Value = DBNull.Value;
}
SqlParameter p11 = new SqlParameter("@REMARK", SqlDbType.VarChar, 60);
cm.Parameters.Add(p11);
p11.Value = entity.Remarks;
SqlParameter p12 = new SqlParameter("@WEMNG", SqlDbType.Decimal, 13);
cm.Parameters.Add(p12);
if (entity.DeliveredQuantity.HasValue)
{
p12.Value = entity.DeliveredQuantity.Value;
}
else
{
p12.Value = DBNull.Value;
}
SqlParameter p13 = new SqlParameter("@TEXT80", SqlDbType.VarChar, 80);
cm.Parameters.Add(p13);
p13.Value = entity.LongTextDescription;
SqlParameter p14 = new SqlParameter("@AUFNR", SqlDbType.VarChar, 12);
cm.Parameters.Add(p14);
p14.Value = entity.OrderNumber;
SqlParameter p15 = new SqlParameter("@LGORT_D", SqlDbType.Char, 4);
cm.Parameters.Add(p15);
p15.Value = entity.StorageLocation;
SqlParameter p16 = new SqlParameter("@ASFNR", SqlDbType.Char, 3);
cm.Parameters.Add(p16);
p16.Value = entity.ItemStatus;
SqlParameter p17 = new SqlParameter("@STS2", SqlDbType.Char, 1);
cm.Parameters.Add(p17);
p17.Value = entity.DeletionStatusIndicator;
SqlParameter p18 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p18);
p18.Value = entity.RecordStatus;
SqlParameter p19 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p19);
p19.Value = entity.RecordStatus;
SqlParameter p20 = new SqlParameter("@ACKSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p20);
p20.Value = entity.AcknowledgementStatus;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
public static void Delete(EpTransaction epTran, ContractItem 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
ContractItem checkEntity = RetrieveByKey(epTran, entity.ContractNumber, entity.ContractItemSequence);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "DELETE FROM contitm WHERE EBELN=@EBELN AND EBELP=@EBELP";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.VarChar, 10);
cm.Parameters.Add(p1);
p1.Value = entity.ContractNumber;
SqlParameter p2 = new SqlParameter("@EBELP", SqlDbType.VarChar, 5);
cm.Parameters.Add(p2);
p2.Value = entity.ContractItemSequence;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
public static void Insert(EpTransaction epTran, PurchaseOrderServiceTask 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
PurchaseOrderServiceTask checkEntity = RetrieveByKey(epTran, entity.ServiceLineNumber, entity.ServiceLineSequence);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO PURSRH ([LBLN1],[EXTROW],[SRVPOS],[MENGE], [MEINS], [SBRTWR], [KTEXT1]) VALUES(@LBLN1,@EXTROW, @SRVPOS, @MENGE, @MEINS, @SBRTWR, @KTEXT1)";
SqlParameter p1 = new SqlParameter("@LBLN1", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.ServiceLineNumber;
SqlParameter p2 = new SqlParameter("@EXTROW", SqlDbType.Char, 10);
cm.Parameters.Add(p2);
p2.Value = entity.ServiceLineSequence;
SqlParameter p3 = new SqlParameter("@SRVPOS", SqlDbType.Char, 18);
cm.Parameters.Add(p3);
p3.Value = entity.ServiceMaterial;
SqlParameter p4 = new SqlParameter("@MENGE", SqlDbType.Decimal,13);
cm.Parameters.Add(p4);
if (entity.ServiceQuantity.HasValue)
p4.Value = entity.ServiceQuantity;
else
p4.Value = DBNull.Value;
SqlParameter p5 = new SqlParameter("@MEINS", SqlDbType.Char,3);
cm.Parameters.Add(p5);
p5.Value = entity.UnitOfMeasure;
SqlParameter p6 = new SqlParameter("@SBRTWR", SqlDbType.Decimal,11);
cm.Parameters.Add(p6);
if (entity.ServicePrice.HasValue)
p6.Value = entity.ServicePrice;
else
p6.Value = DBNull.Value;
SqlParameter p7 = new SqlParameter("@KTEXT1", SqlDbType.VarChar, 40);
cm.Parameters.Add(p7);
p7.Value = entity.ServiceText;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
private static Collection <PurchaseOrderHeader> Retrieve(EpTransaction epTran, string whereClause, string sortClaues)
{
Collection <PurchaseOrderHeader> entities = new Collection <PurchaseOrderHeader>();
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],[LIFNR],[BEDAT],[AMTPR],[GSTPR],[WAERS],[ZTERM],[BUYER],[AD_TLNMBR],[VERKF],[ADRNR_TXT],[REMARK],[STAT],[RECSTS],[ACKSTS] FROM purhdr";
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())
{
PurchaseOrderHeader entity = new PurchaseOrderHeader();
entity.OrderNumber = rd["EBELN"].ToString();
entity.SupplierID = rd["LIFNR"].ToString();
if (rd.IsDBNull(2))
{
entity.OrderDate = null;
}
else
{
entity.OrderDate = Convert.ToInt64(rd["BEDAT"]);
}
if (rd.IsDBNull(3))
{
entity.OrderAmount = null;
}
else
{
entity.OrderAmount = Convert.ToInt64(rd["AMTPR"]);
}
if (rd.IsDBNull(3))
{
entity.GstAmount = null;
}
else
{
entity.GstAmount = Convert.ToInt64(rd["GSTPR"]);
}
entity.CurrencyCode = rd["WAERS"].ToString();
entity.PaymentTerms = rd["ZTERM"].ToString();
entity.BuyerName = rd["BUYER"].ToString();
entity.AddressNumber = rd["AD_TLNMBR"].ToString();
entity.SalesPerson = rd["VERKF"].ToString();
entity.ShipmentAddress = rd["ADRNR_TXT"].ToString();
entity.Remarks = rd["REMARK"].ToString();
entity.OrderStatus = rd["STAT"].ToString();
entity.RecordStatus = rd["RECSTS"].ToString();
entity.AcknowledgeStatus = rd["ACKSTS"].ToString();
entities.Add(entity);
}
// close reader
rd.Close();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
return(entities);
}
private static Collection <Supplier> Retrieve(EpTransaction epTran, string whereClause, string sortClaues)
{
Collection <Supplier> entities = new Collection <Supplier>();
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 [LIFNR],[PASSWD],[ANRED],[NAME],[ADDRESS],[PSTLZ],[KORT],[REGIO],[LAND1],[TELF1],[TELF2],[TELFX],[SPREQ],[EMAIL],[RECSTS] FROM vndmst";
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())
{
Supplier entity = new Supplier();
entity.SupplierID = rd["LIFNR"].ToString();
entity.Password = rd["PASSWD"].ToString();
entity.Title = rd["ANRED"].ToString();
entity.SupplierName = rd["NAME"].ToString();
entity.SupplierAddress = rd["ADDRESS"].ToString();
entity.PostalCode = rd["PSTLZ"].ToString();
entity.Country = rd["KORT"].ToString();
entity.Region = rd["REGIO"].ToString();
entity.CountryCode = rd["LAND1"].ToString();
entity.Telephone1 = rd["TELF1"].ToString();
entity.Telephone2 = rd["TELF2"].ToString();
entity.FaxNo = rd["TELFX"].ToString();
entity.SPREQ = rd["SPREQ"].ToString();
entity.EmailID = rd["EMAIL"].ToString();
entity.RecordStatus = rd["RECSTS"].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, PurchaseOrderServiceTask 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
PurchaseOrderServiceTask checkEntity = RetrieveByKey(epTran, entity.ServiceLineNumber, entity.ServiceLineSequence);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "DELETE FROM PURSRH WHERE LBLN1=@LBLN1 AND EXTROW=@EXTROW";
SqlParameter p1 = new SqlParameter("@LBLN1", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.ServiceLineNumber;
SqlParameter p2 = new SqlParameter("@EXTROW", SqlDbType.Char, 10);
cm.Parameters.Add(p2);
p2.Value = entity.ServiceLineSequence;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
public static void Insert(EpTransaction epTran, PurchaseOrderHeader 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
PurchaseOrderHeader checkEntity = RetrieveByKey(epTran, entity.OrderNumber);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO purhdr ([EBELN],[LIFNR],[BEDAT],[AMTPR],[GSTPR],[WAERS],[ZTERM],[BUYER],[AD_TLNMBR],[VERKF],[ADRNR_TXT],[REMARK],[STAT],[RECSTS],[ACKSTS]) VALUES(@EBELN,@LIFNR,@BEDAT,@AMTPR,@GSTPR,@WAERS,@ZTERM,@BUYER,@AD_TLNMBR,@VERKF,@ADRNR_TXT,@REMARK,@STAT,@RECSTS,@ACKSTS)";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.OrderNumber;
SqlParameter p2 = new SqlParameter("@LIFNR", SqlDbType.Char, 10);
cm.Parameters.Add(p2);
p2.Value = entity.SupplierID;
SqlParameter p3 = new SqlParameter("@BEDAT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p3);
if (entity.OrderDate.HasValue)
{
p3.Value = entity.OrderDate;
}
else
{
p3.Value = DBNull.Value;
}
SqlParameter p4 = new SqlParameter("@AMTPR", SqlDbType.Decimal, 11);
cm.Parameters.Add(p4);
if (entity.OrderAmount.HasValue)
{
p4.Value = entity.OrderAmount;
}
else
{
p4.Value = DBNull.Value;
}
SqlParameter p5 = new SqlParameter("@GSTPR", SqlDbType.Decimal, 11);
cm.Parameters.Add(p5);
if (entity.GstAmount.HasValue)
{
p5.Value = entity.GstAmount;
}
else
{
p5.Value = DBNull.Value;
}
SqlParameter p6 = new SqlParameter("@WAERS", SqlDbType.Char, 5);
cm.Parameters.Add(p6);
p6.Value = entity.CurrencyCode;
SqlParameter p7 = new SqlParameter("@ZTERM", SqlDbType.Char, 4);
cm.Parameters.Add(p7);
p7.Value = entity.PaymentTerms;
SqlParameter p8 = new SqlParameter("@BUYER", SqlDbType.VarChar, 31);
cm.Parameters.Add(p8);
p8.Value = entity.BuyerName;
SqlParameter p9 = new SqlParameter("@AD_TLNMBR", SqlDbType.VarChar, 30);
cm.Parameters.Add(p9);
p9.Value = entity.AddressNumber;
SqlParameter p10 = new SqlParameter("@VERKF", SqlDbType.VarChar, 30);
cm.Parameters.Add(p10);
p10.Value = entity.SalesPerson;
SqlParameter p11 = new SqlParameter("@ADRNR_TXT", SqlDbType.VarChar, 80);
cm.Parameters.Add(p11);
p11.Value = entity.ShipmentAddress;
SqlParameter p12 = new SqlParameter("@REMARK", SqlDbType.VarChar, 60);
cm.Parameters.Add(p12);
p12.Value = entity.Remarks;
SqlParameter p13 = new SqlParameter("@STAT", SqlDbType.Char, 3);
cm.Parameters.Add(p13);
p13.Value = entity.OrderStatus;
SqlParameter p14 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p14);
p14.Value = entity.RecordStatus;
SqlParameter p15 = new SqlParameter("@ACKSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p15);
p15.Value = entity.AcknowledgeStatus;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
private static Collection <PurchaseOrderHeaderText> Retrieve(EpTransaction epTran, string whereClause, string sortClaues)
{
Collection <PurchaseOrderHeaderText> entities = new Collection <PurchaseOrderHeaderText>();
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],[TXTITM],[LTXT],[RECSTS] FROM puhtxt";
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())
{
PurchaseOrderHeaderText entity = new PurchaseOrderHeaderText();
entity.OrderNumber = rd["EBELN"].ToString();
entity.TextSerialNumber = rd["TXTITM"].ToString();
entity.LongText = rd["LTXT"].ToString();
entity.RecordStatus = rd["RECSTS"].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 Update(EpTransaction epTran, PurchaseOrderSubcontractComponent 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
PurchaseOrderSubcontractComponent checkEntity = RetrieveByKey(epTran, entity.OrderNumber, entity.ItemSequenceNumber, entity.ComponentSequenceNumber);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "UPDATE PURSBC SET MATNR=@MATNR,MAKTX=@MAKTX,BDMNG=@BDMNG,MEINS=@MEINS,STS=@STS,RECSTS=@RECSTS WHERE EBELN=@EBELN AND EBELP=@EBELP AND COMPL=@COMPL";
SqlParameter p1 = new SqlParameter("@MATNR", SqlDbType.Char, 18);
cm.Parameters.Add(p1);
p1.Value = entity.MaterialNumber;
SqlParameter p2 = new SqlParameter("@MAKTX", SqlDbType.VarChar,40);
cm.Parameters.Add(p2);
p2.Value = entity.MaterialDescription;
SqlParameter p3 = new SqlParameter("@BDMNG", SqlDbType.Decimal);
cm.Parameters.Add(p3);
if (entity.ComponentQuantity.HasValue)
p3.Value = entity.ComponentQuantity;
else
p3.Value = DBNull.Value;
SqlParameter p4 = new SqlParameter("@MEINS", SqlDbType.Char, 3);
cm.Parameters.Add(p4);
p4.Value = entity.UnitOfMeasure;
SqlParameter p5 = new SqlParameter("@STS", SqlDbType.Char, 1);
cm.Parameters.Add(p5);
p5.Value = entity.ItemStatus;
SqlParameter p6 = new SqlParameter("@MEINS", SqlDbType.Char, 1);
cm.Parameters.Add(p6);
p6.Value = entity.RecordStatus;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
private static Collection<PurchaseOrderItemSchedule> Retrieve(
EpTransaction epTran, string whereClause, string sortClaues)
{
Collection<PurchaseOrderItemSchedule> entities = new Collection<PurchaseOrderItemSchedule>();
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], [EBELP], [MATNR], [ETENR], [SLFDAT], [MENGE], [EINDT], " +
" [WEMNG], [ACKDT] , [RECSTS], [PRMDT] FROM PURSCH";
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())
{
PurchaseOrderItemSchedule entity = new PurchaseOrderItemSchedule();
entity.PurchaseOrderNumber = rd["EBELN"].ToString();
entity.PurchaseOrderItemSequence = rd["EBELP"].ToString();
entity.MaterialNumber = rd["MATNR"].ToString();
entity.PurchaseOrderScheduleSequence = rd["ETENR"].ToString();
if (rd.IsDBNull(4))
entity.OrderItemScheduleDate = null;
else
entity.OrderItemScheduleDate =Convert.ToInt64(rd["SLFDAT"]);
if (rd.IsDBNull(5))
entity.DeliveryScheduleQuantity = null;
else
entity.DeliveryScheduleQuantity = Convert.ToDecimal(rd["MENGE"]);
if (rd.IsDBNull(6))
entity.DeliveryDate = null;
else
entity.DeliveryDate = Convert.ToInt64(rd["EINDT"]);
if (rd.IsDBNull(7))
entity.DeliveredQuantity = null;
else
entity.DeliveredQuantity = Convert.ToDecimal(rd["WEMNG"]);
if (rd.IsDBNull(8))
entity.AcknowledgementDate = null;
else
entity.AcknowledgementDate = Convert.ToInt64(rd["ACKDT"]);
entity.RecordStatus = rd["RECSTS"].ToString();
if (rd.IsDBNull(10))
entity.ExpeditingPromiseDate = null;
else
entity.ExpeditingPromiseDate = Convert.ToInt64(rd["PRMDT"]);
entities.Add(entity);
}
// close reader
rd.Close();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
return entities;
}
private static Collection<PurchaseOrderSubcontractComponent> Retrieve(EpTransaction epTran, string whereClause, string sortClaues)
{
Collection<PurchaseOrderSubcontractComponent> entities = new Collection<PurchaseOrderSubcontractComponent>();
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],[EBELP],[COMPL],[MATNR],[MAKTX],[BDMNG],[MEINS],[STS],[RECSTS] FROM PURSBC";
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())
{
PurchaseOrderSubcontractComponent entity = new PurchaseOrderSubcontractComponent();
entity.OrderNumber = rd["EBELN"].ToString();
entity.ItemSequenceNumber = rd["EBELP"].ToString();
entity.ComponentSequenceNumber = rd["COMPL"].ToString();
entity.MaterialNumber = rd["MATNR"].ToString();
entity.MaterialDescription = rd["MAKTX"].ToString();
if (rd.IsDBNull(5))
entity.ComponentQuantity = null;
else
entity.ComponentQuantity = Convert.ToDecimal(rd["BDMNG"].ToString());
entity.UnitOfMeasure = rd["MEINS"].ToString();
entity.ItemStatus = rd["STS"].ToString();
entity.RecordStatus = rd["RECSTS"].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 Insert(EpTransaction epTran, PurchaseOrderSubcontractComponent 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
PurchaseOrderSubcontractComponent checkEntity = RetrieveByKey(epTran, entity.OrderNumber, entity.ItemSequenceNumber, entity.ComponentSequenceNumber);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO PURSBC ([EBELN],[EBELP],[COMPL],[MATNR], [MAKTX], [BDMNG], [MEINS], [STS], [RECSTS]) VALUES(@EBELN,@EBELP, @COMPL, @MATNR, @MAKTX, @BDMNG, @MEINS, @STS, @RECSTS)";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.OrderNumber;
SqlParameter p2 = new SqlParameter("@EBELP", SqlDbType.Char, 5);
cm.Parameters.Add(p2);
p2.Value = entity.ItemSequenceNumber;
SqlParameter p3 = new SqlParameter("@COMPL", SqlDbType.Char, 5);
cm.Parameters.Add(p3);
p3.Value = entity.ComponentSequenceNumber;
SqlParameter p4 = new SqlParameter("@MATNR", SqlDbType.Char, 18);
cm.Parameters.Add(p4);
p4.Value = entity.MaterialNumber;
SqlParameter p5 = new SqlParameter("@MAKTX", SqlDbType.VarChar, 40);
cm.Parameters.Add(p5);
p5.Value = entity.MaterialDescription;
SqlParameter p6 = new SqlParameter("@BDMNG", SqlDbType.Decimal);
cm.Parameters.Add(p6);
if (entity.ComponentQuantity.HasValue)
{
p6.Value = entity.ComponentQuantity;
}
else
{
p6.Value = DBNull.Value;
}
SqlParameter p7 = new SqlParameter("@MEINS", SqlDbType.Char, 3);
cm.Parameters.Add(p7);
p7.Value = entity.UnitOfMeasure;
SqlParameter p8 = new SqlParameter("@STS", SqlDbType.Char, 1);
cm.Parameters.Add(p8);
p8.Value = entity.UnitOfMeasure;
SqlParameter p9 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p9);
p7.Value = entity.RecordStatus;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
public static void Insert(EpTransaction epTran, PurchaseOrderSubcontractComponent 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
PurchaseOrderSubcontractComponent checkEntity = RetrieveByKey(epTran, entity.OrderNumber, entity.ItemSequenceNumber, entity.ComponentSequenceNumber);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO PURSBC ([EBELN],[EBELP],[COMPL],[MATNR], [MAKTX], [BDMNG], [MEINS], [STS], [RECSTS]) VALUES(@EBELN,@EBELP, @COMPL, @MATNR, @MAKTX, @BDMNG, @MEINS, @STS, @RECSTS)";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.OrderNumber;
SqlParameter p2 = new SqlParameter("@EBELP", SqlDbType.Char, 5);
cm.Parameters.Add(p2);
p2.Value = entity.ItemSequenceNumber;
SqlParameter p3 = new SqlParameter("@COMPL", SqlDbType.Char, 5);
cm.Parameters.Add(p3);
p3.Value = entity.ComponentSequenceNumber;
SqlParameter p4 = new SqlParameter("@MATNR", SqlDbType.Char, 18);
cm.Parameters.Add(p4);
p4.Value = entity.MaterialNumber;
SqlParameter p5 = new SqlParameter("@MAKTX", SqlDbType.VarChar, 40);
cm.Parameters.Add(p5);
p5.Value = entity.MaterialDescription;
SqlParameter p6 = new SqlParameter("@BDMNG", SqlDbType.Decimal);
cm.Parameters.Add(p6);
if (entity.ComponentQuantity.HasValue)
p6.Value = entity.ComponentQuantity;
else
p6.Value = DBNull.Value;
SqlParameter p7 = new SqlParameter("@MEINS", SqlDbType.Char, 3);
cm.Parameters.Add(p7);
p7.Value = entity.UnitOfMeasure;
SqlParameter p8 = new SqlParameter("@STS", SqlDbType.Char, 1);
cm.Parameters.Add(p8);
p8.Value = entity.UnitOfMeasure;
SqlParameter p9 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p9);
p7.Value = entity.RecordStatus;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
private static Collection<PurchaseOrderServiceItem> Retrieve(EpTransaction epTran,string whereClause,string sortClaues)
{
Collection<PurchaseOrderServiceItem> entities = new Collection<PurchaseOrderServiceItem>();
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],[EBELP],[LBLN1],[KTEXT1],[MENGE],[PREIS],[RECSTS] FROM PURSRV";
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())
{
PurchaseOrderServiceItem entity = new PurchaseOrderServiceItem();
entity.OrderNumber = rd["EBELN"].ToString();
entity.ItemSequenceNumber = rd["EBELP"].ToString();
entity.ServiceLineNumber = rd["LBLN1"].ToString();
entity.ServiceDescription = rd["KTEXT1"].ToString();
if (rd.IsDBNull(4))
entity.ServiceQuantity = null;
else
entity.ServiceQuantity = Convert.ToDecimal(rd["MENGE"].ToString());
if (rd.IsDBNull(5))
entity.ServicePrice = null;
else
entity.ServicePrice = Convert.ToDecimal(rd["PREIS"].ToString());
entity.RecordStatus = rd["RECSTS"].ToString();
entities.Add(entity);
}
// close reader
rd.Close();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
return entities;
}
private static Collection <ContractItem> Retrieve(EpTransaction epTran, string whereClause, string sortClaues)
{
Collection <ContractItem> entities = new Collection <ContractItem>();
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],[EBELP],[TXZ01],[MATNR],[WERKS],[MATKL],[KTMNG],[MEINS],[NETPR],[PEINH],[BRTWR],[ANFNR],[BANFN],[AFNAM] FROM contitm";
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())
{
ContractItem entity = new ContractItem();
entity.ContractNumber = rd["EBELN"].ToString();
entity.ContractItemSequence = rd["EBELP"].ToString();
entity.Description = rd["TXZ01"].ToString();
entity.MaterialNumber = rd["MATNR"].ToString();
entity.Plant = rd["WERKS"].ToString();
entity.MaterialGroup = rd["MATKL"].ToString();
if (rd.IsDBNull(6))
{
entity.TargetQuantity = null;
}
else
{
entity.TargetQuantity = Convert.ToInt64(rd["KTMNG"]);
}
entity.UnitOfMeasure = rd["MEINS"].ToString();
if (rd.IsDBNull(8))
{
entity.UnitPrice = null;
}
else
{
entity.UnitPrice = Convert.ToInt64(rd["NETPR"]);
}
if (rd.IsDBNull(9))
{
entity.PricePerUnit = null;
}
else
{
entity.PricePerUnit = Convert.ToInt64(rd["PEINH"]);
}
if (rd.IsDBNull(10))
{
entity.NetValue = null;
}
else
{
entity.NetValue = Convert.ToInt64(rd["BRTWR"]);
}
entity.RFQNumber = rd["ANFNR"].ToString();
entity.RequisitionNumber = rd["BANFN"].ToString();
entity.Requisitioner = rd["AFNAM"].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 Update(EpTransaction epTran, PurchaseOrderServiceItem 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
PurchaseOrderServiceItem checkEntity = RetrieveByKey(epTran, entity.OrderNumber, entity.ItemSequenceNumber, entity.ServiceLineNumber);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "UPDATE PURSRV SET KTEXT1=@LTEXT1,MENGE=@MENGE,PREIS=@PREIS,RECSTS=@RECSTS WHERE EBELN=@EBELN AND EBELP=@EBELP AND LBLN1=@LBLN1";
SqlParameter p1 = new SqlParameter("@KTEXT1", SqlDbType.VarChar, 40);
cm.Parameters.Add(p1);
p1.Value = entity.ServiceDescription;
SqlParameter p2 = new SqlParameter("@MENGE", SqlDbType.Decimal, 13);
cm.Parameters.Add(p2);
if (entity.ServiceQuantity.HasValue)
{
p6.Value = entity.ServiceQuantity.Value;
}
else
{
p6.Value = DBNull.Value;
}
SqlParameter p3 = new SqlParameter("@PREIS", SqlDbType.Decimal, 11);
cm.Parameters.Add(p3);
if (entity.ServicePrice.HasValue)
{
p3.Value = entity.ServicePrice.Value;
}
else
{
p3.Value = DBNull.Value;
}
SqlParameter p4 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p4);
p4.Value = entity.RecordStatus;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
public static void Insert(EpTransaction epTran, ContractItem 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
ContractItem checkEntity = RetrieveByKey(epTran, entity.ContractNumber, entity.ContractItemSequence);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO contitm ([EBELN],[EBELP],[TXZ01],[MATNR],[WERKS],[MATKL],[KTMNG],[MEINS],[NETPR],[PEINH],[BRTWR],[ANFNR],[BANFN],[AFNAM]) VALUES(@EBELN,@EBELP,@TXZ01,@MATNR,@WERKS,@MATKL,@KTMNG,@MEINS,@NETPR,@PEINH,@BRTWR,@ANFNR,@BANFN,@AFNAM)";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.VarChar, 10);
cm.Parameters.Add(p1);
p1.Value = entity.ContractNumber;
SqlParameter p2 = new SqlParameter("@EBELP", SqlDbType.VarChar, 5);
cm.Parameters.Add(p2);
p2.Value = entity.ContractItemSequence;
SqlParameter p3 = new SqlParameter("@TXZ01", SqlDbType.VarChar, 40);
cm.Parameters.Add(p3);
p3.Value = entity.Description;
SqlParameter p4 = new SqlParameter("@MATNR", SqlDbType.VarChar, 18);
cm.Parameters.Add(p4);
p4.Value = entity.MaterialNumber;
SqlParameter p5 = new SqlParameter("@WERKS", SqlDbType.Char, 4);
cm.Parameters.Add(p5);
p5.Value = entity.Plant;
SqlParameter p6 = new SqlParameter("@MATKL", SqlDbType.VarChar, 9);
cm.Parameters.Add(p6);
p6.Value = entity.MaterialGroup;
SqlParameter p7 = new SqlParameter("@KTMNG", SqlDbType.Decimal, 13);
cm.Parameters.Add(p7);
if (entity.TargetQuantity.HasValue)
{
p7.Value = entity.TargetQuantity;
}
else
{
p7.Value = DBNull.Value;
}
SqlParameter p8 = new SqlParameter("@MEINS", SqlDbType.Char, 3);
cm.Parameters.Add(p8);
p8.Value = entity.UnitOfMeasure;
SqlParameter p9 = new SqlParameter("@NETPR", SqlDbType.Decimal, 11);
cm.Parameters.Add(p9);
if (entity.UnitPrice.HasValue)
{
p9.Value = entity.UnitPrice;
}
else
{
p9.Value = DBNull.Value;
}
SqlParameter p10 = new SqlParameter("@PEINH", SqlDbType.Decimal, 5);
cm.Parameters.Add(p10);
if (entity.PricePerUnit.HasValue)
{
p10.Value = entity.PricePerUnit;
}
else
{
p10.Value = DBNull.Value;
}
SqlParameter p11 = new SqlParameter("@BRTWR", SqlDbType.Decimal, 13);
cm.Parameters.Add(p11);
if (entity.NetValue.HasValue)
{
p11.Value = entity.NetValue;
}
else
{
p11.Value = DBNull.Value;
}
SqlParameter p12 = new SqlParameter("@ANFNR", SqlDbType.VarChar, 10);
cm.Parameters.Add(p12);
p12.Value = entity.RFQNumber;
SqlParameter p13 = new SqlParameter("@BANFN", SqlDbType.VarChar, 10);
cm.Parameters.Add(p13);
p13.Value = entity.RequisitionNumber;
SqlParameter p14 = new SqlParameter("@AFNAM", SqlDbType.VarChar, 12);
cm.Parameters.Add(p14);
p14.Value = entity.Requisitioner;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
public static void Insert(EpTransaction epTran, PurchaseOrderServiceItem 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
PurchaseOrderServiceItem checkEntity = RetrieveByKey(epTran, entity.OrderNumber, entity.ItemSequenceNumber, entity.ServiceLineNumber);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO PURSRV ([EBELN],[EBELP],[LBLN1],[KTEXT1], [MENGE], [PREIS], [RECSTS]) VALUES(@EBELN,@EBELP, @LBLN1, @KTEXT1, @MENGE, @PREIS, @RECSTS)";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.OrderNumber;
SqlParameter p2 = new SqlParameter("@EBELP", SqlDbType.Char, 5);
cm.Parameters.Add(p2);
p2.Value = entity.ItemSequenceNumber;
SqlParameter p3 = new SqlParameter("@LBLN1", SqlDbType.Char, 10);
cm.Parameters.Add(p3);
p3.Value = entity.ServiceLineNumber;
SqlParameter p4 = new SqlParameter("@KTEXT1", SqlDbType.VarChar, 40);
cm.Parameters.Add(p4);
p4.Value = entity.ServiceDescription;
SqlParameter p5 = new SqlParameter("@MENGE", SqlDbType.Decimal, 13);
cm.Parameters.Add(p5);
if (entity.ServiceQuantity.HasValue)
{
p5.Value = entity.ServiceQuantity.Value;
}
else
{
p5.Value = DBNull.Value;
}
SqlParameter p6 = new SqlParameter("@PREIS", SqlDbType.Decimal, 11);
cm.Parameters.Add(p6);
if (entity.ServicePrice.HasValue)
{
p6.Value = entity.ServicePrice.Value;
}
else
{
p6.Value = DBNull.Value;
}
SqlParameter p7 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p7);
p7.Value = entity.RecordStatus;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
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, PurchaseOrderHeader 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
PurchaseOrderHeader checkEntity = RetrieveByKey(epTran, entity.OrderNumber);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "DELETE FROM purhdr WHERE EBELN=@EBELN";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.OrderNumber;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
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(EpTransaction epTran, PurchaseOrderHeader 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
PurchaseOrderHeader checkEntity = RetrieveByKey(epTran, entity.OrderNumber);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO purhdr ([EBELN],[LIFNR],[BEDAT],[AMTPR],[GSTPR],[WAERS],[ZTERM],[BUYER],[AD_TLNMBR],[VERKF],[ADRNR_TXT],[REMARK],[STAT],[RECSTS],[ACKSTS]) VALUES(@EBELN,@LIFNR,@BEDAT,@AMTPR,@GSTPR,@WAERS,@ZTERM,@BUYER,@AD_TLNMBR,@VERKF,@ADRNR_TXT,@REMARK,@STAT,@RECSTS,@ACKSTS)";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.OrderNumber;
SqlParameter p2 = new SqlParameter("@LIFNR", SqlDbType.Char, 10);
cm.Parameters.Add(p2);
p2.Value = entity.SupplierID;
SqlParameter p3 = new SqlParameter("@BEDAT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p3);
if (entity.OrderDate.HasValue)
p3.Value = entity.OrderDate;
else
p3.Value = DBNull.Value;
SqlParameter p4 = new SqlParameter("@AMTPR", SqlDbType.Decimal, 11);
cm.Parameters.Add(p4);
if (entity.OrderAmount.HasValue)
p4.Value = entity.OrderAmount;
else
p4.Value = DBNull.Value;
SqlParameter p5 = new SqlParameter("@GSTPR", SqlDbType.Decimal, 11);
cm.Parameters.Add(p5);
if (entity.GstAmount.HasValue)
p5.Value = entity.GstAmount;
else
p5.Value = DBNull.Value;
SqlParameter p6 = new SqlParameter("@WAERS", SqlDbType.Char, 5);
cm.Parameters.Add(p6);
p6.Value = entity.CurrencyCode;
SqlParameter p7 = new SqlParameter("@ZTERM", SqlDbType.Char, 4);
cm.Parameters.Add(p7);
p7.Value = entity.PaymentTerms;
SqlParameter p8 = new SqlParameter("@BUYER", SqlDbType.VarChar, 31);
cm.Parameters.Add(p8);
p8.Value = entity.BuyerName;
SqlParameter p9 = new SqlParameter("@AD_TLNMBR", SqlDbType.VarChar, 30);
cm.Parameters.Add(p9);
p9.Value = entity.AddressNumber;
SqlParameter p10 = new SqlParameter("@VERKF", SqlDbType.VarChar, 30);
cm.Parameters.Add(p10);
p10.Value = entity.SalesPerson;
SqlParameter p11 = new SqlParameter("@ADRNR_TXT", SqlDbType.VarChar, 80);
cm.Parameters.Add(p11);
p11.Value = entity.ShipmentAddress;
SqlParameter p12 = new SqlParameter("@REMARK", SqlDbType.VarChar, 60);
cm.Parameters.Add(p12);
p12.Value = entity.Remarks;
SqlParameter p13 = new SqlParameter("@STAT", SqlDbType.Char, 3);
cm.Parameters.Add(p13);
p13.Value = entity.OrderStatus;
SqlParameter p14 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p14);
p14.Value = entity.RecordStatus;
SqlParameter p15 = new SqlParameter("@ACKSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p15);
p15.Value = entity.AcknowledgeStatus;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
private static Collection<PurchaseOrderItem> Retrieve(EpTransaction epTran, string whereClause, string sortClaues)
{
Collection<PurchaseOrderItem> entities = new Collection<PurchaseOrderItem>();
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],[EBELP],[PSTYP],[MATNR],[TXZ01],[BISMT],[MENGE],[PEINH],[MEINS],[NETPR],[REMARK],[WEMNG],[TEXT80],[AUFNR],[LGORT_D],[ASFNR],[STS2],[RECSTS],[ACKSTS] FROM purdtl";
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())
{
PurchaseOrderItem entity = new PurchaseOrderItem();
entity.PurchaseOrderNumber = rd["EBELN"].ToString();
entity.PurchaseItemSequenceNumber = rd["EBELP"].ToString();
entity.PurchaseOrderType = rd["PSTYP"].ToString();
entity.MaterialNumber = rd["MATNR"].ToString();
entity.ShortText = rd["TXZ01"].ToString();
entity.OldMaterialNumber = rd["BISMT"].ToString();
if (rd.IsDBNull(6))
entity.OrderQuantity = null;
else
entity.OrderQuantity = Convert.ToDecimal(rd["MENGE"].ToString());
if (rd.IsDBNull(7))
entity.PricePerUnit = null;
else
entity.PricePerUnit = Convert.ToDecimal(rd["PEINH"].ToString());
entity.UnitofMeasure = rd["MEINS"].ToString();
if (rd.IsDBNull(8))
entity.NetPrice = null;
else
entity.NetPrice = Convert.ToDecimal(rd["NETPR"].ToString());
entity.Remarks = rd["REMARK"].ToString();
if (rd.IsDBNull(9))
entity.DeliveredQuantity = null;
else
entity.DeliveredQuantity = Convert.ToDecimal(rd["WEMNG"].ToString());
entity.LongTextDescription = rd["TEXT80"].ToString();
entity.OrderNumber = rd["AUFNR"].ToString();
entity.StorageLocation = rd["LGORT_D"].ToString();
entity.ItemStatus = rd["ASFNR"].ToString();
entity.DeletionStatusIndicator = rd["STS2"].ToString();
entity.RecordStatus = rd["RECSTS"].ToString();
entity.AcknowledgementStatus = rd["ACKSTS"].ToString();
entities.Add(entity);
}
// close reader
rd.Close();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
return entities;
}
private static Collection<ContractItem> Retrieve(EpTransaction epTran, string whereClause, string sortClaues)
{
Collection<ContractItem> entities = new Collection<ContractItem>();
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],[EBELP],[TXZ01],[MATNR],[WERKS],[MATKL],[KTMNG],[MEINS],[NETPR],[PEINH],[BRTWR],[ANFNR],[BANFN],[AFNAM] FROM contitm";
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())
{
ContractItem entity = new ContractItem();
entity.ContractNumber = rd["EBELN"].ToString();
entity.ContractItemSequence = rd["EBELP"].ToString();
entity.Description = rd["TXZ01"].ToString();
entity.MaterialNumber = rd["MATNR"].ToString();
entity.Plant = rd["WERKS"].ToString();
entity.MaterialGroup = rd["MATKL"].ToString();
if (rd.IsDBNull(6))
entity.TargetQuantity = null;
else
entity.TargetQuantity = Convert.ToInt64(rd["KTMNG"]);
entity.UnitOfMeasure = rd["MEINS"].ToString();
if (rd.IsDBNull(8))
entity.UnitPrice = null;
else
entity.UnitPrice = Convert.ToInt64(rd["NETPR"]);
if (rd.IsDBNull(9))
entity.PricePerUnit = null;
else
entity.PricePerUnit = Convert.ToInt64(rd["PEINH"]);
if (rd.IsDBNull(10))
entity.NetValue = null;
else
entity.NetValue = Convert.ToInt64(rd["BRTWR"]);
entity.RFQNumber = rd["ANFNR"].ToString();
entity.RequisitionNumber = rd["BANFN"].ToString();
entity.Requisitioner = rd["AFNAM"].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 Insert(EpTransaction epTran, PurchaseOrderItem 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
PurchaseOrderItem checkEntity = RetrieveByKey(epTran, entity.PurchaseOrderNumber, entity.PurchaseItemSequenceNumber);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO PURDTL ([EBELN],[EBELP],[PSTYP],[MATNR],[TXZ01],[BISMT],[MENGE],[PEINH],[MEINS],[NETPR],[REMARK],[WEMNG],[TEXT80],[AUFNR],[LGORT_D],[ASFNR],[STS2],[RECSTS],[ACKSTS]) VALUES(@EBELN, @EBELP, @PSTYP, @MATNR, @TXZ01,@BISMT, @MENGE, @PEINH, @MEINS , @NETPR, @REMARK, @WEMNG, @TEXT80, @AUFNR, @LGORT_D, @ASFNR, @STS2, @RECSTS, @ACKSTS)";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.PurchaseOrderNumber;
SqlParameter p2 = new SqlParameter("@EBELP", SqlDbType.Char, 5);
cm.Parameters.Add(p2);
p2.Value = entity.PurchaseItemSequenceNumber;
SqlParameter p3 = new SqlParameter("@PSTYP", SqlDbType.Char, 1);
cm.Parameters.Add(p3);
p3.Value = entity.PurchaseOrderType;
SqlParameter p4 = new SqlParameter("@MATNR", SqlDbType.Char, 18);
cm.Parameters.Add(p4);
p4.Value = entity.MaterialNumber;
SqlParameter p5 = new SqlParameter("@TXZ01", SqlDbType.VarChar, 40);
cm.Parameters.Add(p5);
p5.Value = entity.ShortText;
SqlParameter p6 = new SqlParameter("@BISMT", SqlDbType.Char, 18);
cm.Parameters.Add(p6);
p6.Value = entity.OldMaterialNumber;
SqlParameter p7 = new SqlParameter("@MENGE", SqlDbType.Decimal,13);
cm.Parameters.Add(p7);
if (entity.OrderQuantity.HasValue)
p7.Value = entity.OrderQuantity.Value;
else
p7.Value = DBNull.Value;
SqlParameter p8 = new SqlParameter("@PEINH", SqlDbType.Decimal, 13);
cm.Parameters.Add(p8);
if (entity.PricePerUnit.HasValue)
p8.Value = entity.PricePerUnit.Value;
else
p7.Value = DBNull.Value;
SqlParameter p9 = new SqlParameter("@MEINS", SqlDbType.Char, 3);
cm.Parameters.Add(p9);
p9.Value = entity.UnitofMeasure;
SqlParameter p10 = new SqlParameter("@NETPR", SqlDbType.Decimal, 13);
cm.Parameters.Add(p10);
if (entity.NetPrice.HasValue)
p10.Value = entity.NetPrice.Value;
else
p10.Value = DBNull.Value;
SqlParameter p11 = new SqlParameter("@REMARK", SqlDbType.VarChar, 60);
cm.Parameters.Add(p11);
p11.Value = entity.Remarks;
SqlParameter p12 = new SqlParameter("@WEMNG", SqlDbType.Decimal, 13);
cm.Parameters.Add(p12);
if (entity.DeliveredQuantity.HasValue)
p12.Value = entity.DeliveredQuantity.Value;
else
p12.Value = DBNull.Value;
SqlParameter p13 = new SqlParameter("@TEXT80", SqlDbType.VarChar, 80);
cm.Parameters.Add(p13);
p13.Value = entity.LongTextDescription;
SqlParameter p14 = new SqlParameter("@AUFNR", SqlDbType.VarChar, 12);
cm.Parameters.Add(p14);
p14.Value = entity.OrderNumber;
SqlParameter p15 = new SqlParameter("@LGORT_D", SqlDbType.Char, 4);
cm.Parameters.Add(p15);
p15.Value = entity.StorageLocation;
SqlParameter p16 = new SqlParameter("@ASFNR", SqlDbType.Char, 3);
cm.Parameters.Add(p16);
p16.Value = entity.ItemStatus;
SqlParameter p17 = new SqlParameter("@STS2", SqlDbType.Char, 1);
cm.Parameters.Add(p17);
p17.Value = entity.DeletionStatusIndicator;
SqlParameter p18 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p18);
p18.Value = entity.RecordStatus;
SqlParameter p19 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p19);
p19.Value = entity.RecordStatus;
SqlParameter p20 = new SqlParameter("@ACKSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p20);
p20.Value = entity.AcknowledgementStatus;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
public static void Update(EpTransaction epTran, PurchaseOrderItemText 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
PurchaseOrderItemText checkEntity = RetrieveByKey(epTran, entity.OrderNumber, entity.ItemSequenceNumber, entity.TextSerialNumber);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "UPDATE puitxt SET LTXT=@LTXT,RECSTS=@RECSTS WHERE EBELN=@EBELN AND EBELP=@EBELP AND TXTITM=@TXTITM";
SqlParameter p1 = new SqlParameter("@LTXT", SqlDbType.NVarChar, 255);
cm.Parameters.Add(p1);
p1.Value = entity.LongText;
SqlParameter p2 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p2);
p2.Value = entity.RecordStatus;
SqlParameter p3 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p3);
p3.Value = entity.OrderNumber;
SqlParameter p4 = new SqlParameter("@TXTITM", SqlDbType.Char, 5);
cm.Parameters.Add(p4);
p4.Value = entity.TextSerialNumber;
SqlParameter p5 = new SqlParameter("@EBELP", SqlDbType.Char, 5);
cm.Parameters.Add(p5);
p5.Value = entity.ItemSequenceNumber;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
public static void Update(EpTransaction epTran, PurchaseOrderItemText 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
PurchaseOrderItemText checkEntity = RetrieveByKey(epTran, entity.OrderNumber,entity.ItemSequenceNumber,entity.TextSerialNumber);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "UPDATE puitxt SET LTXT=@LTXT,RECSTS=@RECSTS WHERE EBELN=@EBELN AND EBELP=@EBELP AND TXTITM=@TXTITM";
SqlParameter p1 = new SqlParameter("@LTXT", SqlDbType.NVarChar, 255);
cm.Parameters.Add(p1);
p1.Value = entity.LongText;
SqlParameter p2 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p2);
p2.Value = entity.RecordStatus;
SqlParameter p3 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p3);
p3.Value = entity.OrderNumber;
SqlParameter p4 = new SqlParameter("@TXTITM", SqlDbType.Char, 5);
cm.Parameters.Add(p4);
p4.Value = entity.TextSerialNumber;
SqlParameter p5 = new SqlParameter("@EBELP", SqlDbType.Char, 5);
cm.Parameters.Add(p5);
p5.Value = entity.ItemSequenceNumber;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
private static Collection <PurchaseOrderItem> Retrieve(EpTransaction epTran, string whereClause, string sortClaues)
{
Collection <PurchaseOrderItem> entities = new Collection <PurchaseOrderItem>();
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],[EBELP],[PSTYP],[MATNR],[TXZ01],[BISMT],[MENGE],[PEINH],[MEINS],[NETPR],[REMARK],[WEMNG],[TEXT80],[AUFNR],[LGORT_D],[ASFNR],[STS2],[RECSTS],[ACKSTS] FROM purdtl";
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())
{
PurchaseOrderItem entity = new PurchaseOrderItem();
entity.PurchaseOrderNumber = rd["EBELN"].ToString();
entity.PurchaseItemSequenceNumber = rd["EBELP"].ToString();
entity.PurchaseOrderType = rd["PSTYP"].ToString();
entity.MaterialNumber = rd["MATNR"].ToString();
entity.ShortText = rd["TXZ01"].ToString();
entity.OldMaterialNumber = rd["BISMT"].ToString();
if (rd.IsDBNull(6))
{
entity.OrderQuantity = null;
}
else
{
entity.OrderQuantity = Convert.ToDecimal(rd["MENGE"].ToString());
}
if (rd.IsDBNull(7))
{
entity.PricePerUnit = null;
}
else
{
entity.PricePerUnit = Convert.ToDecimal(rd["PEINH"].ToString());
}
entity.UnitofMeasure = rd["MEINS"].ToString();
if (rd.IsDBNull(8))
{
entity.NetPrice = null;
}
else
{
entity.NetPrice = Convert.ToDecimal(rd["NETPR"].ToString());
}
entity.Remarks = rd["REMARK"].ToString();
if (rd.IsDBNull(9))
{
entity.DeliveredQuantity = null;
}
else
{
entity.DeliveredQuantity = Convert.ToDecimal(rd["WEMNG"].ToString());
}
entity.LongTextDescription = rd["TEXT80"].ToString();
entity.OrderNumber = rd["AUFNR"].ToString();
entity.StorageLocation = rd["LGORT_D"].ToString();
entity.ItemStatus = rd["ASFNR"].ToString();
entity.DeletionStatusIndicator = rd["STS2"].ToString();
entity.RecordStatus = rd["RECSTS"].ToString();
entity.AcknowledgementStatus = rd["ACKSTS"].ToString();
entities.Add(entity);
}
// close reader
rd.Close();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
return(entities);
}
private static Collection<PurchaseOrderItemText> Retrieve(EpTransaction epTran, string whereClause, string sortClaues)
{
Collection<PurchaseOrderItemText> entities = new Collection<PurchaseOrderItemText>();
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],[EBELP],[TXTITM],[LTXT],[RECSTS] FROM puitxt";
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())
{
PurchaseOrderItemText entity = new PurchaseOrderItemText();
entity.OrderNumber = rd["EBELN"].ToString();
entity.ItemSequenceNumber = rd["EBELP"].ToString();
entity.TextSerialNumber = rd["TXTITM"].ToString();
entity.LongText = rd["LTXT"].ToString();
entity.RecordStatus = rd["RECSTS"].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 Update(EpTransaction epTran, PurchaseOrderServiceTask 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
PurchaseOrderServiceTask checkEntity = RetrieveByKey(epTran, entity.ServiceLineNumber, entity.ServiceLineSequence);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "UPDATE PURSRH SET SRVPOS=@SRVPOS,MENGE=@MENGE,MEINS=@MEINS,SBRTWR=@SBRTWR, KTEXT1=@KTEXT1 WHERE LBLN1=@LBLN1 AND EXTROW=@EXTROW";
SqlParameter p1 = new SqlParameter("@SRVPOS", SqlDbType.Char, 18);
cm.Parameters.Add(p1);
p1.Value = entity.ServiceLineSequence;
SqlParameter p2 = new SqlParameter("@MENGE", SqlDbType.Decimal);
cm.Parameters.Add(p2);
if (entity.ServiceQuantity.HasValue)
p2.Value = entity.ServiceQuantity;
else
p2.Value = DBNull.Value;
SqlParameter p3 = new SqlParameter("@MEINS", SqlDbType.Char,3);
cm.Parameters.Add(p3);
p3.Value = entity.UnitOfMeasure;
SqlParameter p4 = new SqlParameter("@SBRTWR", SqlDbType.Decimal,13);
cm.Parameters.Add(p4);
if (entity.ServicePrice.HasValue)
p4.Value = entity.ServicePrice;
else
p4.Value = DBNull.Value;
SqlParameter p5 = new SqlParameter("@KTEXT1", SqlDbType.VarChar, 40);
cm.Parameters.Add(p5);
p5.Value = entity.ServiceText;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
public static void Insert(EpTransaction epTran, PurchaseOrderItemText 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
PurchaseOrderItemText checkEntity = RetrieveByKey(epTran, entity.OrderNumber, entity.ItemSequenceNumber, entity.TextSerialNumber);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO puitxt ([EBELN],[EBELP],[TXTITM],[LTXT],[RECSTS]) VALUES(@EBELN,@EBELP,@TXTITM,@LTXT,@RECSTS)";
SqlParameter p1 = new SqlParameter("@LTXT", SqlDbType.NVarChar, 255);
cm.Parameters.Add(p1);
p1.Value = entity.LongText;
SqlParameter p2 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p2);
p2.Value = entity.RecordStatus;
SqlParameter p3 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p3);
p3.Value = entity.OrderNumber;
SqlParameter p4 = new SqlParameter("@TXTITM", SqlDbType.Char, 5);
cm.Parameters.Add(p4);
p4.Value = entity.TextSerialNumber;
SqlParameter p5 = new SqlParameter("@EBELP", SqlDbType.Char, 5);
cm.Parameters.Add(p5);
p5.Value = entity.ItemSequenceNumber;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
private static Collection<PurchaseOrderServiceTask> Retrieve(EpTransaction epTran, string whereClause, string sortClaues)
{
Collection<PurchaseOrderServiceTask> entities = new Collection<PurchaseOrderServiceTask>();
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 [LBLN1],[EXTROW],[SRVPOS],[MENGE],[MEINS],[SBRTWR],[KTEXT1] FROM PURSRH";
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())
{
PurchaseOrderServiceTask entity = new PurchaseOrderServiceTask();
entity.ServiceLineNumber = rd["LBLN1"].ToString();
entity.ServiceLineSequence = rd["EXTROW"].ToString();
entity.ServiceMaterial = rd["SRVPOS"].ToString();
if (rd.IsDBNull(3))
entity.ServiceQuantity = null;
else
entity.ServiceQuantity = Convert.ToDecimal(rd["MENGE"].ToString());
entity.UnitOfMeasure= rd["MEINS"].ToString();
if (rd.IsDBNull(5))
entity.ServicePrice = null;
else
entity.ServicePrice = Convert.ToDecimal(rd["SBRTWR"].ToString());
entity.ServiceText = rd["KTEXT1"].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 Update(EpTransaction epTran, PurchaseOrderItemSchedule 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
PurchaseOrderItemSchedule checkEntity = RetrieveByKey(epTran, entity.PurchaseOrderNumber, entity.PurchaseOrderItemSequence, entity.PurchaseOrderScheduleSequence);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "UPDATE PURSCH SET MATNR = @MATNR, SLFDAT = @SLFDAT , MENGE = @MENGE, EINDT = @EINDT , WEMNG = @WEMNG, ACKDT = @ACKDT , RECSTS = @RECSTS, PRMDT = @PRMDT WHERE EBELN=@EBELN AND EBELP=@EBELP AND ETENR = @ETENR ";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.PurchaseOrderNumber;
SqlParameter p2 = new SqlParameter("@EBELP", SqlDbType.Char, 5);
cm.Parameters.Add(p2);
p2.Value = entity.PurchaseOrderItemSequence;
SqlParameter p3 = new SqlParameter("@MATNR", SqlDbType.Char, 18);
cm.Parameters.Add(p3);
p3.Value = entity.MaterialNumber;
SqlParameter p4 = new SqlParameter("@ETENR", SqlDbType.Char, 4);
cm.Parameters.Add(p4);
p4.Value = entity.PurchaseOrderScheduleSequence;
SqlParameter p5 = new SqlParameter("@SLFDAT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p5);
if (entity.OrderItemScheduleDate.HasValue)
{
p5.Value = entity.OrderItemScheduleDate.Value;
}
else
{
p5.Value = DBNull.Value;
}
SqlParameter p6 = new SqlParameter("@MENGE", SqlDbType.Decimal, 4);
cm.Parameters.Add(p6);
if (entity.DeliveryScheduleQuantity.HasValue)
{
p6.Value = entity.DeliveryScheduleQuantity;
}
else
{
p6.Value = DBNull.Value;
}
SqlParameter p7 = new SqlParameter("@EINDT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p7);
if (entity.DeliveryDate.HasValue)
{
p7.Value = entity.DeliveryDate;
}
else
{
p7.Value = DBNull.Value;
}
SqlParameter p8 = new SqlParameter("@WEMNG", SqlDbType.Decimal, 4);
cm.Parameters.Add(p8);
if (entity.DeliveredQuantity.HasValue)
{
p8.Value = entity.DeliveredQuantity;
}
else
{
p8.Value = DBNull.Value;
}
SqlParameter p9 = new SqlParameter("@ACKDT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p9);
if (entity.AcknowledgementDate.HasValue)
{
p9.Value = entity.AcknowledgementDate;
}
else
{
p9.Value = DBNull.Value;
}
SqlParameter p10 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p10);
p10.Value = entity.RecordStatus;
SqlParameter p11 = new SqlParameter("@PRMDT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p11);
if (entity.ExpeditingPromiseDate.HasValue)
{
p11.Value = entity.ExpeditingPromiseDate;
}
else
{
p11.Value = DBNull.Value;
}
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
private static Collection <PurchaseOrderItemSchedule> Retrieve(
EpTransaction epTran, string whereClause, string sortClaues)
{
Collection <PurchaseOrderItemSchedule> entities = new Collection <PurchaseOrderItemSchedule>();
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], [EBELP], [MATNR], [ETENR], [SLFDAT], [MENGE], [EINDT], " +
" [WEMNG], [ACKDT] , [RECSTS], [PRMDT] FROM PURSCH";
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())
{
PurchaseOrderItemSchedule entity = new PurchaseOrderItemSchedule();
entity.PurchaseOrderNumber = rd["EBELN"].ToString();
entity.PurchaseOrderItemSequence = rd["EBELP"].ToString();
entity.MaterialNumber = rd["MATNR"].ToString();
entity.PurchaseOrderScheduleSequence = rd["ETENR"].ToString();
if (rd.IsDBNull(4))
{
entity.OrderItemScheduleDate = null;
}
else
{
entity.OrderItemScheduleDate = Convert.ToInt64(rd["SLFDAT"]);
}
if (rd.IsDBNull(5))
{
entity.DeliveryScheduleQuantity = null;
}
else
{
entity.DeliveryScheduleQuantity = Convert.ToDecimal(rd["MENGE"]);
}
if (rd.IsDBNull(6))
{
entity.DeliveryDate = null;
}
else
{
entity.DeliveryDate = Convert.ToInt64(rd["EINDT"]);
}
if (rd.IsDBNull(7))
{
entity.DeliveredQuantity = null;
}
else
{
entity.DeliveredQuantity = Convert.ToDecimal(rd["WEMNG"]);
}
if (rd.IsDBNull(8))
{
entity.AcknowledgementDate = null;
}
else
{
entity.AcknowledgementDate = Convert.ToInt64(rd["ACKDT"]);
}
entity.RecordStatus = rd["RECSTS"].ToString();
if (rd.IsDBNull(10))
{
entity.ExpeditingPromiseDate = null;
}
else
{
entity.ExpeditingPromiseDate = Convert.ToInt64(rd["PRMDT"]);
}
entities.Add(entity);
}
// close reader
rd.Close();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
return(entities);
}
public static void Insert(EpTransaction epTran, PurchaseOrderItemSchedule 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
PurchaseOrderItemSchedule checkEntity = RetrieveByKey(epTran, entity.PurchaseOrderNumber, entity.PurchaseOrderItemSequence, entity.PurchaseOrderScheduleSequence);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO PURSCH ([EBELN], [EBELP], [MATNR], [ETENR], [SLFDAT], [MENGE], [EINDT], [WEMNG], [ACKDT], [RECSTS], [PRMDT]) VALUES(@EBELN, @EBELP, @MATNR, @ETENR, @SLFDAT, @MENGE, @EINDT, @WEMNG, @ACKDT, @RECSTS, @PRMDT)";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.PurchaseOrderNumber;
SqlParameter p2 = new SqlParameter("@EBELP", SqlDbType.Char, 5);
cm.Parameters.Add(p2);
p2.Value = entity.PurchaseOrderScheduleSequence;
SqlParameter p3 = new SqlParameter("@MATNR", SqlDbType.Char, 18);
cm.Parameters.Add(p3);
p3.Value = entity.MaterialNumber;
SqlParameter p4 = new SqlParameter("@ETENR", SqlDbType.Char, 4);
cm.Parameters.Add(p4);
p4.Value = entity.PurchaseOrderScheduleSequence;
SqlParameter p5 = new SqlParameter("@SLFDAT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p5);
if (entity.OrderItemScheduleDate.HasValue)
{
p5.Value = entity.OrderItemScheduleDate.Value;
}
else
{
p5.Value = DBNull.Value;
}
SqlParameter p6 = new SqlParameter("@MENGE", SqlDbType.Decimal, 4);
cm.Parameters.Add(p6);
if (entity.DeliveryScheduleQuantity.HasValue)
{
p6.Value = entity.DeliveryScheduleQuantity;
}
else
{
p6.Value = DBNull.Value;
}
SqlParameter p7 = new SqlParameter("@EINDT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p7);
if (entity.DeliveryDate.HasValue)
{
p7.Value = entity.DeliveryDate;
}
else
{
p7.Value = DBNull.Value;
}
SqlParameter p8 = new SqlParameter("@WEMNG", SqlDbType.Decimal, 4);
cm.Parameters.Add(p8);
if (entity.DeliveredQuantity.HasValue)
{
p8.Value = entity.DeliveredQuantity;
}
else
{
p8.Value = DBNull.Value;
}
SqlParameter p9 = new SqlParameter("@ACKDT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p9);
if (entity.AcknowledgementDate.HasValue)
{
p9.Value = entity.AcknowledgementDate;
}
else
{
p9.Value = DBNull.Value;
}
SqlParameter p10 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p10);
p10.Value = entity.RecordStatus;
SqlParameter p11 = new SqlParameter("@PRMDT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p11);
if (entity.ExpeditingPromiseDate.HasValue)
{
p11.Value = entity.ExpeditingPromiseDate;
}
else
{
p11.Value = DBNull.Value;
}
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
public static void Insert(EpTransaction epTran, PurchaseOrderHeaderText 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
PurchaseOrderHeaderText checkEntity = RetrieveByKey(epTran, entity.OrderNumber, entity.TextSerialNumber);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO puhtxt ([EBELN],[TXTITM],[LTXT],[RECSTS]) VALUES(@EBELN,@TXTITM,@LTXT,@RECSTS)";
SqlParameter p1 = new SqlParameter("@LTXT", SqlDbType.NVarChar, 255);
cm.Parameters.Add(p1);
p1.Value = entity.LongText;
SqlParameter p2 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p2);
p2.Value = entity.RecordStatus;
SqlParameter p3 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p3);
p3.Value = entity.OrderNumber;
SqlParameter p4 = new SqlParameter("@TXTITM", SqlDbType.Char, 5);
cm.Parameters.Add(p4);
p4.Value = entity.TextSerialNumber;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
public static void Update(EpTransaction epTran, PurchaseOrderSubcontractComponent 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
PurchaseOrderSubcontractComponent checkEntity = RetrieveByKey(epTran, entity.OrderNumber, entity.ItemSequenceNumber, entity.ComponentSequenceNumber);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "UPDATE PURSBC SET MATNR=@MATNR,MAKTX=@MAKTX,BDMNG=@BDMNG,MEINS=@MEINS,STS=@STS,RECSTS=@RECSTS WHERE EBELN=@EBELN AND EBELP=@EBELP AND COMPL=@COMPL";
SqlParameter p1 = new SqlParameter("@MATNR", SqlDbType.Char, 18);
cm.Parameters.Add(p1);
p1.Value = entity.MaterialNumber;
SqlParameter p2 = new SqlParameter("@MAKTX", SqlDbType.VarChar, 40);
cm.Parameters.Add(p2);
p2.Value = entity.MaterialDescription;
SqlParameter p3 = new SqlParameter("@BDMNG", SqlDbType.Decimal);
cm.Parameters.Add(p3);
if (entity.ComponentQuantity.HasValue)
{
p3.Value = entity.ComponentQuantity;
}
else
{
p3.Value = DBNull.Value;
}
SqlParameter p4 = new SqlParameter("@MEINS", SqlDbType.Char, 3);
cm.Parameters.Add(p4);
p4.Value = entity.UnitOfMeasure;
SqlParameter p5 = new SqlParameter("@STS", SqlDbType.Char, 1);
cm.Parameters.Add(p5);
p5.Value = entity.ItemStatus;
SqlParameter p6 = new SqlParameter("@MEINS", SqlDbType.Char, 1);
cm.Parameters.Add(p6);
p6.Value = entity.RecordStatus;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
public static void Delete(EpTransaction epTran, PurchaseOrderItemSchedule 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
PurchaseOrderItemSchedule checkEntity = RetrieveByKey
(epTran, entity.PurchaseOrderNumber, entity.PurchaseOrderItemSequence, entity.PurchaseOrderScheduleSequence);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "DELETE FROM PURSCH WHERE EBELN=@EBELN AND EBELP=@EBELP AND ETENR = @ETENR ";
SqlParameter p3 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p3);
p3.Value = entity.PurchaseOrderNumber ;
SqlParameter p4 = new SqlParameter("@EBELP", SqlDbType.Char, 5);
cm.Parameters.Add(p4);
p4.Value = entity.PurchaseOrderItemSequence ;
SqlParameter p5 = new SqlParameter("@ETENR", SqlDbType.Char, 4);
cm.Parameters.Add(p5);
p5.Value = entity.PurchaseOrderScheduleSequence ;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
private static Collection <PurchaseOrderSubcontractComponent> Retrieve(EpTransaction epTran, string whereClause, string sortClaues)
{
Collection <PurchaseOrderSubcontractComponent> entities = new Collection <PurchaseOrderSubcontractComponent>();
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],[EBELP],[COMPL],[MATNR],[MAKTX],[BDMNG],[MEINS],[STS],[RECSTS] FROM PURSBC";
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())
{
PurchaseOrderSubcontractComponent entity = new PurchaseOrderSubcontractComponent();
entity.OrderNumber = rd["EBELN"].ToString();
entity.ItemSequenceNumber = rd["EBELP"].ToString();
entity.ComponentSequenceNumber = rd["COMPL"].ToString();
entity.MaterialNumber = rd["MATNR"].ToString();
entity.MaterialDescription = rd["MAKTX"].ToString();
if (rd.IsDBNull(5))
{
entity.ComponentQuantity = null;
}
else
{
entity.ComponentQuantity = Convert.ToDecimal(rd["BDMNG"].ToString());
}
entity.UnitOfMeasure = rd["MEINS"].ToString();
entity.ItemStatus = rd["STS"].ToString();
entity.RecordStatus = rd["RECSTS"].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 Insert(EpTransaction epTran, PurchaseOrderItemSchedule 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
PurchaseOrderItemSchedule checkEntity = RetrieveByKey(epTran, entity.PurchaseOrderNumber, entity.PurchaseOrderItemSequence, entity.PurchaseOrderScheduleSequence);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO PURSCH ([EBELN], [EBELP], [MATNR], [ETENR], [SLFDAT], [MENGE], [EINDT], [WEMNG], [ACKDT], [RECSTS], [PRMDT]) VALUES(@EBELN, @EBELP, @MATNR, @ETENR, @SLFDAT, @MENGE, @EINDT, @WEMNG, @ACKDT, @RECSTS, @PRMDT)";
SqlParameter p1 = new SqlParameter("@EBELN", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.PurchaseOrderNumber;
SqlParameter p2 = new SqlParameter("@EBELP", SqlDbType.Char, 5);
cm.Parameters.Add(p2);
p2.Value = entity.PurchaseOrderScheduleSequence;
SqlParameter p3 = new SqlParameter("@MATNR", SqlDbType.Char, 18);
cm.Parameters.Add(p3);
p3.Value = entity.MaterialNumber;
SqlParameter p4 = new SqlParameter("@ETENR", SqlDbType.Char, 4);
cm.Parameters.Add(p4);
p4.Value = entity.PurchaseOrderScheduleSequence;
SqlParameter p5 = new SqlParameter("@SLFDAT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p5);
if (entity.OrderItemScheduleDate.HasValue)
p5.Value = entity.OrderItemScheduleDate.Value;
else
p5.Value = DBNull.Value;
SqlParameter p6 = new SqlParameter("@MENGE", SqlDbType.Decimal, 4);
cm.Parameters.Add(p6);
if (entity.DeliveryScheduleQuantity.HasValue)
p6.Value = entity.DeliveryScheduleQuantity;
else
p6.Value = DBNull.Value;
SqlParameter p7 = new SqlParameter("@EINDT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p7);
if (entity.DeliveryDate.HasValue)
p7.Value = entity.DeliveryDate;
else
p7.Value = DBNull.Value;
SqlParameter p8 = new SqlParameter("@WEMNG", SqlDbType.Decimal, 4);
cm.Parameters.Add(p8);
if (entity.DeliveredQuantity.HasValue)
p8.Value = entity.DeliveredQuantity;
else
p8.Value = DBNull.Value;
SqlParameter p9 = new SqlParameter("@ACKDT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p9);
if (entity.AcknowledgementDate.HasValue)
p9.Value = entity.AcknowledgementDate;
else
p9.Value = DBNull.Value;
SqlParameter p10 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p10);
p10.Value = entity.RecordStatus;
SqlParameter p11 = new SqlParameter("@PRMDT", SqlDbType.BigInt, 8);
cm.Parameters.Add(p11);
if (entity.ExpeditingPromiseDate.HasValue)
p11.Value = entity.ExpeditingPromiseDate;
else
p11.Value = DBNull.Value;
cm.ExecuteNonQuery();
if (epTran == null)
if (connection.State != System.Data.ConnectionState.Closed) connection.Close();
}
public static void Update(EpTransaction epTran, Supplier 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
Supplier checkEntity = RetrieveByKey(epTran, entity.SupplierID);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "UPDATE vndmst SET LIFNR=@LIFNR,VN=@VN,ANRED=@ANRED,NAME=@NAME,ADDRESS=@ADDRESS,PSTLZ=@PSTLZ,KORT=@KORT,REGIO=@REGIO,LAND1=@LAND1,TELF1=@TELF1,TELF2=@TELF2,TELFX=@TELFX,SPREQ=@SPREQ,EMAIL=@EMAIL,RECSTS=@RECSTS WHERE LIFNR=@LIFNR";
SqlParameter p1 = new SqlParameter("@LIFNR", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.SupplierID;
SqlParameter p2 = new SqlParameter("@PASSWD", SqlDbType.Char, 10);
cm.Parameters.Add(p2);
p2.Value = entity.Password;
SqlParameter p3 = new SqlParameter("@ANRED", SqlDbType.VarChar, 15);
cm.Parameters.Add(p3);
p3.Value = entity.Title;
SqlParameter p4 = new SqlParameter("@NAME", SqlDbType.VarChar, 80);
cm.Parameters.Add(p4);
p4.Value = entity.SupplierName;
SqlParameter p5 = new SqlParameter("@ADDRESS", SqlDbType.VarChar, 60);
cm.Parameters.Add(p5);
p5.Value = entity.SupplierAddress;
SqlParameter p6 = new SqlParameter("@PSTLZ", SqlDbType.VarChar, 10);
cm.Parameters.Add(p6);
p6.Value = entity.PostalCode;
SqlParameter p7 = new SqlParameter("@KORT", SqlDbType.VarChar, 40);
cm.Parameters.Add(p7);
p7.Value = entity.Country;
SqlParameter p8 = new SqlParameter("@REGIO", SqlDbType.Char, 3);
cm.Parameters.Add(p8);
p8.Value = entity.Region;
SqlParameter p9 = new SqlParameter("@LAND1", SqlDbType.Char, 3);
cm.Parameters.Add(p9);
p9.Value = entity.CountryCode;
SqlParameter p10 = new SqlParameter("@TELF1", SqlDbType.VarChar, 16);
cm.Parameters.Add(p10);
p10.Value = entity.Telephone1;
SqlParameter p11 = new SqlParameter("@TELF2", SqlDbType.VarChar, 16);
cm.Parameters.Add(p11);
p11.Value = entity.Telephone2;
SqlParameter p12 = new SqlParameter("@TELFX", SqlDbType.VarChar, 31);
cm.Parameters.Add(p12);
p12.Value = entity.FaxNo;
SqlParameter p13 = new SqlParameter("@SPREQ", SqlDbType.Char, 3);
cm.Parameters.Add(p13);
p13.Value = entity.SPREQ;
SqlParameter p14 = new SqlParameter("@EMAIL", SqlDbType.VarChar, 100);
cm.Parameters.Add(p14);
p14.Value = entity.EmailID;
SqlParameter p15 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p15);
p15.Value = entity.RecordStatus;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}