protected override LRyderCiscoSncycCnt AddEntity(AccellosContext entityContext, LRyderCiscoSncycCnt entity)
{
string sql = @"INSERT INTO l_ryder_cisco_sncyc_cnt (
cust_code,
loc_code,
item_code,
serial,
pros_date_time,
username,
bulk_item,
item_type) VALUES
(:1, :2, :3, :4, :5, :6, :7, :8)";
IList <OracleParameter> parameters = new List <OracleParameter>
{
new OracleParameter(":1", OracleDbType.Varchar2, entity.CustCode, ParameterDirection.Input),
new OracleParameter(":2", OracleDbType.Varchar2, entity.LocCode, ParameterDirection.Input),
new OracleParameter(":3", OracleDbType.Varchar2, entity.ItemCode, ParameterDirection.Input),
new OracleParameter(":4", OracleDbType.Varchar2, entity.Serial, ParameterDirection.Input),
new OracleParameter(":5", OracleDbType.Date, entity.ProsDateTime, ParameterDirection.Input),
new OracleParameter(":6", OracleDbType.Varchar2, entity.UserName, ParameterDirection.Input),
new OracleParameter(":7", OracleDbType.Varchar2, entity.BulkItem, ParameterDirection.Input),
new OracleParameter(":8", OracleDbType.Varchar2, entity.ItemType, ParameterDirection.Input)
};
using (var cn = (OracleConnection)entityContext.DbConnection)
{
var result = OracleManager.ExecuteSql(cn, sql, parameters);
return(entity);
}
}
public void Execute()
{
var ctx = new AccellosContext();
var copyFmt = @"INSERT INTO l_ryder_cisco_sncyc_cnt_hist
SELECT s.cust_code, s.loc_code, s.item_code,
s.serial, s.pros_date_time, s.username, s.bulk_item, s.item_type, 'OC'
{0}
";
var from = string.Format(FROM_FMT, this.Days);
var copy = string.Format(copyFmt, from);
var deleteFmt = @"DELETE FROM l_ryder_cisco_sncyc_cnt WHERE EXISTS (
SELECT * FROM c_pros_mvt m
WHERE l_ryder_cisco_sncyc_cnt.cust_code = m.cust_code
AND l_ryder_cisco_sncyc_cnt.item_code = m.invt_lev1
AND (l_ryder_cisco_sncyc_cnt.serial = m.pros_value OR l_ryder_cisco_sncyc_cnt.serial = ('S' || m.pros_value))
AND m.pros_trans_tp = 'CO'
AND m.pros_trans_date BETWEEN sysdate - {0} AND sysdate
)";
var delete = string.Format(deleteFmt, this.Days);
var oracleParams = new List <OracleParameter>();
using (OracleConnection cn = (OracleConnection)ctx.DbConnection)
{
cn.Open();
using (var tx = cn.BeginTransaction()) {
try
{
OracleManager.ExecuteSql(tx, copy, oracleParams);
OracleManager.ExecuteSql(tx, delete, oracleParams);
tx.Commit();
}
catch (Exception)
{
tx.Rollback();
throw;
}
}
}
}
public void Execute(RecountLRyderCiscoSncycCntParams data)
{
var ctx = new AccellosContext();
StringBuilder copy = new StringBuilder();
StringBuilder delete = new StringBuilder();
copy.Append(@"INSERT INTO l_ryder_cisco_sncyc_cnt_hist
SELECT s.cust_code, s.loc_code, s.item_code,
s.serial, s.pros_date_time, s.username, s.bulk_item, s.item_type, 'RC'
FROM l_ryder_cisco_sncyc_cnt s
WHERE 1=1
");
delete.Append(@"DELETE FROM l_ryder_cisco_sncyc_cnt
WHERE 1=1
");
var copyParams = new List <OracleParameter>();
var deleteParams = new List <OracleParameter>();
if (!string.IsNullOrWhiteSpace(data.CustCode))
{
copy.Append("AND cust_code = :1 ");
delete.Append("AND cust_code = :1 ");
copyParams.Add(new OracleParameter(":1", OracleDbType.Varchar2, data.CustCode, ParameterDirection.Input));
deleteParams.Add(new OracleParameter(":1", OracleDbType.Varchar2, data.CustCode, ParameterDirection.Input));
}
if (!string.IsNullOrWhiteSpace(data.LocCode))
{
copy.Append("AND loc_code = :2 ");
delete.Append("AND loc_code = :2 ");
copyParams.Add(new OracleParameter(":2", OracleDbType.Varchar2, data.LocCode, ParameterDirection.Input));
deleteParams.Add(new OracleParameter(":2", OracleDbType.Varchar2, data.LocCode, ParameterDirection.Input));
}
if (!string.IsNullOrWhiteSpace(data.ItemCode))
{
copy.Append("AND item_code = :3 ");
delete.Append("AND item_code = :3 ");
copyParams.Add(new OracleParameter(":3", OracleDbType.Varchar2, data.ItemCode, ParameterDirection.Input));
deleteParams.Add(new OracleParameter(":3", OracleDbType.Varchar2, data.ItemCode, ParameterDirection.Input));
}
using (OracleConnection cn = (OracleConnection)ctx.DbConnection)
{
cn.Open();
using (var tx = cn.BeginTransaction())
{
try
{
OracleManager.ExecuteSql(tx, copy.ToString(), copyParams);
OracleManager.ExecuteSql(tx, delete.ToString(), deleteParams);
tx.Commit();
}
catch (Exception)
{
tx.Rollback();
throw;
}
}
}
}