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 IList <MCompH> GetByExample(MCompHParams example)
{
var ctx = new AccellosContext();
using (OracleConnection cn = (OracleConnection)ctx.DbConnection)
{
cn.Open();
StringBuilder sql = new StringBuilder();
sql.Append("SELECT comp_code, comp_name, global_code, comp_stat FROM m_comp_h WHERE 1=1 ");
var parameters = new List <OracleParameter>();
if (!string.IsNullOrWhiteSpace(example.CompCode))
{
sql.Append("AND comp_code = :1 ");
parameters.Add(new OracleParameter(":1", OracleDbType.Varchar2, example.CompCode, ParameterDirection.Input));
}
IList <MCompH> companies = new List <MCompH>();
OracleManager.ExecuteReader(cn, sql.ToString(), parameters,
(reader) => companies.Add(getEntityFromReader(reader))
);
return(companies);
}
}
public IList <MLoc> GetByExample(MLocParams location)
{
var ctx = new AccellosContext();
using (OracleConnection cn = (OracleConnection)ctx.DbConnection)
{
cn.Open();
StringBuilder sql = new StringBuilder();
sql.Append("SELECT comp_code, loc_code, loc_des, loc_stat FROM m_loc WHERE 1=1 ");
var parameters = new List <OracleParameter>();
if (!string.IsNullOrWhiteSpace(location.CompCode))
{
sql.Append("AND comp_code = :1 ");
parameters.Add(new OracleParameter(":1", OracleDbType.Varchar2, location.CompCode, ParameterDirection.Input));
}
if (!string.IsNullOrWhiteSpace(location.LocCode))
{
sql.Append("AND loc_code = :2 ");
parameters.Add(new OracleParameter(":2", OracleDbType.Varchar2, location.LocCode, ParameterDirection.Input));
}
var locations = new List <MLoc>();
OracleManager.ExecuteReader(cn, sql.ToString(), parameters,
(reader) => locations.Add(getEntityFromReader(reader))
);
return(locations);
}
}
public IList <LocationNotCountedModel> GetLocationsNotCounted(string compCode, string custCode)
{
var ctx = new AccellosContext();
using (OracleConnection cn = (OracleConnection)ctx.DbConnection)
{
cn.Open();
StringBuilder sqlFormat = new StringBuilder();
sqlFormat.Append(@"SELECT
loc.cust_code,
loc.loc_code,
loc.invt_lev1 as item_code,
loc.on_hand_qty
FROM (select l.comp_code, l.cust_code, l.loc_code, l.invt_lev1, l.on_hand_qty
from c_loc l
join m_item_h p
on l.comp_code = p.comp_code
and l.cust_code = p.cust_code
and l.invt_lev1 = p.item_code
where p.item_stat = 'A'
and p.pros_prof_code = 'SN'
and l.on_hand_qty > 0) loc
LEFT JOIN (select cust_code, loc_code, item_code
from l_ryder_cisco_sncyc_cnt
group by cust_code, loc_code, item_code
) cyc ON loc.comp_code = '{0}'
AND cyc.cust_code = loc.cust_code
AND cyc.loc_code = loc.loc_code
AND cyc.item_code = loc.invt_lev1
WHERE 1=1
AND cyc.item_code IS NULL ");
var parameters = new List <OracleParameter>();
sqlFormat.Append("AND loc.cust_code = :1 ");
parameters.Add(new OracleParameter(":1", OracleDbType.Varchar2, custCode, ParameterDirection.Input));
IList <LocationNotCountedModel> entities =
new List <LocationNotCountedModel>();
var sql = string.Format(sqlFormat.ToString(), compCode);
OracleManager.ExecuteReader(cn, sql, parameters,
(reader) => entities.Add(new LocationNotCountedModel
{
CustomerCode = reader.GetString(reader.GetOrdinal("cust_code")),
LocationCode = reader.GetString(reader.GetOrdinal("loc_code")),
ItemCode = reader.GetString(reader.GetOrdinal("item_code")),
OnHandQty = reader.GetInt32(reader.GetOrdinal("on_hand_qty"))
}
)
);
return(entities);
}
}
public IList <ScannedQtyMismatchModel> GetScannedQtyMismatches(string compCode, string custCode, string locCode)
{
var ctx = new AccellosContext();
using (OracleConnection cn = (OracleConnection)ctx.DbConnection)
{
cn.Open();
StringBuilder sqlFormat = new StringBuilder();
sqlFormat.Append(@"SELECT
cyc.cust_code,
cyc.loc_code,
cyc.item_code,
loc.invt_lev1,
cyc.cycle_count_qty,
loc.on_hand_qty
FROM (select cust_code, loc_code, item_code, count(serial) cycle_count_qty
from l_ryder_cisco_sncyc_cnt
group by cust_code, loc_code, item_code
) cyc
JOIN c_loc loc ON loc.comp_code = '{0}'
AND cyc.cust_code = loc.cust_code
AND cyc.loc_code = loc.loc_code
AND cyc.item_code = loc.invt_lev1
WHERE 1=1
AND loc.on_hand_qty <> cyc.cycle_count_qty ");
var parameters = new List <OracleParameter>();
sqlFormat.Append("AND cyc.cust_code = :1 ");
parameters.Add(new OracleParameter(":1", OracleDbType.Varchar2, custCode, ParameterDirection.Input));
sqlFormat.Append("AND cyc.loc_code = :2 ");
parameters.Add(new OracleParameter(":2", OracleDbType.Varchar2, locCode, ParameterDirection.Input));
IList <ScannedQtyMismatchModel> entities = new List <ScannedQtyMismatchModel>();
var sql = string.Format(sqlFormat.ToString(), compCode);
OracleManager.ExecuteReader(cn, sql, parameters,
(reader) => entities.Add(new ScannedQtyMismatchModel
{
CustomerCode = reader.GetString(reader.GetOrdinal("cust_code")),
LocationCode = reader.GetString(reader.GetOrdinal("loc_code")),
ItemCode = reader.GetString(reader.GetOrdinal("item_code")),
CycleCountQty = reader.GetInt32(reader.GetOrdinal("cycle_count_qty")),
LocOnHandQty = reader.GetInt32(reader.GetOrdinal("on_hand_qty"))
}
)
);
return(entities);
}
}
public IList <LRyderCiscoSncycCnt> GetByExample(LRyderCiscoSncycCntParams example)
{
var ctx = new AccellosContext();
using (OracleConnection cn = (OracleConnection)ctx.DbConnection)
{
cn.Open();
StringBuilder sql = new StringBuilder();
sql.Append(@"SELECT cust_code, loc_code, item_code, serial, pros_date_time,
username, bulk_item, item_type
FROM l_ryder_cisco_sncyc_cnt WHERE 1=1 ");
var parameters = new List <OracleParameter>();
if (!string.IsNullOrWhiteSpace(example.CustCode))
{
sql.Append("AND cust_code = :1 ");
parameters.Add(new OracleParameter(":1", OracleDbType.Varchar2, example.CustCode, ParameterDirection.Input));
}
if (!string.IsNullOrWhiteSpace(example.LocCode))
{
sql.Append("AND loc_code = :2 ");
parameters.Add(new OracleParameter(":2", OracleDbType.Varchar2, example.LocCode, ParameterDirection.Input));
}
if (!string.IsNullOrWhiteSpace(example.ItemCode))
{
sql.Append("AND item_code = :3 ");
parameters.Add(new OracleParameter(":3", OracleDbType.Varchar2, example.ItemCode, ParameterDirection.Input));
}
if (!string.IsNullOrWhiteSpace(example.Serial))
{
sql.Append("AND serial = :4 ");
parameters.Add(new OracleParameter(":4", OracleDbType.Varchar2, example.Serial, ParameterDirection.Input));
}
IList <LRyderCiscoSncycCnt> entities = new List <LRyderCiscoSncycCnt>();
OracleManager.ExecuteReader(cn, sql.ToString(), parameters,
(reader) => entities.Add(getEntityFromReader(reader))
);
return(entities);
}
}
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 int GetSerialCount(string custCode, string locCode, string itemCode)
{
var ctx = new AccellosContext();
string sql = @"SELECT COUNT(serial) cnt FROM l_ryder_cisco_sncyc_cnt
WHERE cust_code = :1 AND loc_code = :2 AND item_code = :3";
IList <OracleParameter> parameters = new List <OracleParameter>
{
new OracleParameter(":1", OracleDbType.Varchar2, custCode, ParameterDirection.Input),
new OracleParameter(":2", OracleDbType.Varchar2, locCode, ParameterDirection.Input),
new OracleParameter(":3", OracleDbType.Varchar2, itemCode, ParameterDirection.Input)
};
using (var cn = (OracleConnection)ctx.DbConnection)
{
return(OracleManager.ExecuteScalar(cn, sql, parameters));
}
}
public IList <CLoc> GetByExample(CLocParams location)
{
var ctx = new AccellosContext();
using (OracleConnection cn = (OracleConnection)ctx.DbConnection)
{
cn.Open();
StringBuilder sql = new StringBuilder();
sql.Append(@"SELECT comp_code, loc_code, cust_code, on_hand_qty, invt_access, invt_lev1, hold_code
FROM c_loc WHERE 1=1 ");
var parameters = new List <OracleParameter>();
if (!string.IsNullOrWhiteSpace(location.CompCode))
{
sql.Append("AND comp_code = :1 ");
parameters.Add(new OracleParameter(":1", OracleDbType.Varchar2, location.CompCode, ParameterDirection.Input));
}
if (!string.IsNullOrWhiteSpace(location.LocCode))
{
sql.Append("AND loc_code = :2 ");
parameters.Add(new OracleParameter(":2", OracleDbType.Varchar2, location.LocCode, ParameterDirection.Input));
}
if (!string.IsNullOrWhiteSpace(location.CustCode))
{
sql.Append("AND cust_code = :3 ");
parameters.Add(new OracleParameter(":3", OracleDbType.Varchar2, location.CustCode, ParameterDirection.Input));
}
if (!string.IsNullOrWhiteSpace(location.InvtLev1))
{
sql.Append("AND invt_lev1 = :4 ");
parameters.Add(new OracleParameter(":4", OracleDbType.Varchar2, location.InvtLev1, ParameterDirection.Input));
}
var locations = new List <CLoc>();
OracleManager.ExecuteReader(cn, sql.ToString(), parameters,
(reader) => locations.Add(getEntityFromReader(reader))
);
return(locations);
}
}
public IList <CProsMvt> GetByExample(CProsMvtParams example)
{
var ctx = new AccellosContext();
using (OracleConnection cn = (OracleConnection)ctx.DbConnection)
{
cn.Open();
StringBuilder sql = new StringBuilder();
sql.Append(@"SELECT loc_code, pros_code, invt_lev1, pros_trans_date, pros_trans_tp
FROM c_pros_mvt WHERE 1=1 ");
var parameters = new List <OracleParameter>();
if (!string.IsNullOrWhiteSpace(example.LocCode))
{
sql.Append("AND loc_code = :1 ");
parameters.Add(new OracleParameter(":1", OracleDbType.Varchar2, example.LocCode, ParameterDirection.Input));
}
if (!string.IsNullOrWhiteSpace(example.InvtLev1))
{
sql.Append("AND invt_lev1 = :2 ");
parameters.Add(new OracleParameter(":2", OracleDbType.Varchar2, example.InvtLev1, ParameterDirection.Input));
}
if (!string.IsNullOrWhiteSpace(example.ProsCode))
{
sql.Append("AND pros_code = :3 ");
parameters.Add(new OracleParameter(":3", OracleDbType.Varchar2, example.ProsCode, ParameterDirection.Input));
}
var entities = new List <CProsMvt>();
OracleManager.ExecuteReader(cn, sql.ToString(), parameters,
(reader) => entities.Add(getEntityFromReader(reader))
);
return(entities);
}
}
public IList <MItemH> GetByExample(MItemHParams example)
{
var ctx = new AccellosContext();
using (OracleConnection cn = (OracleConnection)ctx.DbConnection)
{
cn.Open();
StringBuilder sql = new StringBuilder();
sql.Append(@"SELECT cust_code, item_code, item_stat, comp_code,
item_des1, pros_prof_code FROM m_item_h WHERE 1=1 ");
var parameters = new List <OracleParameter>();
if (!string.IsNullOrWhiteSpace(example.CustCode))
{
sql.Append("AND cust_code = :1 ");
parameters.Add(new OracleParameter(":1", OracleDbType.Varchar2, example.CustCode, ParameterDirection.Input));
}
if (!string.IsNullOrWhiteSpace(example.CompCode))
{
sql.Append("AND comp_code = :2 ");
parameters.Add(new OracleParameter(":2", OracleDbType.Varchar2, example.CompCode, ParameterDirection.Input));
}
if (!string.IsNullOrWhiteSpace(example.ItemCode))
{
sql.Append("AND item_code = :3 ");
parameters.Add(new OracleParameter(":3", OracleDbType.Varchar2, example.ItemCode, ParameterDirection.Input));
}
IList <MItemH> entities = new List <MItemH>();
OracleManager.ExecuteReader(cn, sql.ToString(), parameters,
(reader) => entities.Add(getEntityFromReader(reader))
);
return(entities);
}
}
protected override MItemH GetEntity(AccellosContext entityContext, string id)
{
using (OracleConnection cn = (OracleConnection)entityContext.DbConnection)
{
cn.Open();
string sql = @"SELECT cust_code, item_code, item_stat, comp_code, item_des1, pros_prof_code
FROM m_item_h
WHERE item_code = :1";
var parameters = new List <OracleParameter> {
new OracleParameter(":1", OracleDbType.Varchar2, id, ParameterDirection.Input)
};
MItemH entity = null;
OracleManager.ExecuteReader(cn, sql, parameters,
(reader) => entity = getEntityFromReader(reader)
);
return(entity);
}
}
protected override MCompH GetEntity(AccellosContext entityContext, string id)
{
using (OracleConnection cn = (OracleConnection)entityContext.DbConnection)
{
cn.Open();
string sql = @"SELECT comp_code, comp_name, global_code, comp_stat
FROM m_comp_h
WHERE comp_code = :1";
var parameters = new List <OracleParameter> {
new OracleParameter(":1", OracleDbType.Varchar2, id, ParameterDirection.Input)
};
MCompH company = null;
OracleManager.ExecuteReader(cn, sql, parameters,
(reader) => company = getEntityFromReader(reader)
);
return(company);
}
}
protected override MLoc GetEntity(AccellosContext entityContext, string id)
{
using (OracleConnection cn = (OracleConnection)entityContext.DbConnection)
{
cn.Open();
string sql = @"SELECT comp_code, loc_code, loc_des, loc_stat
FROM m_loc
WHERE loc_code = :1";
var parameters = new List <OracleParameter> {
new OracleParameter(":1", OracleDbType.Varchar2, id, ParameterDirection.Input)
};
MLoc loc = null;
OracleManager.ExecuteReader(cn, sql, parameters,
(reader) => loc = getEntityFromReader(reader)
);
return(loc);
}
}
protected override MOp GetEntity(AccellosContext entityContext, string id)
{
using (OracleConnection cn = (OracleConnection)entityContext.DbConnection)
{
cn.Open();
string sql = @"SELECT op_code, op_name, op_stat, op_pword, comp_code
FROM m_op
WHERE op_code = :1";
var parameters = new List <OracleParameter> {
new OracleParameter(":1", OracleDbType.Varchar2, id, ParameterDirection.Input)
};
MOp op = null;
OracleManager.ExecuteReader(cn, sql, parameters,
(reader) => op = getEntityFromReader(reader)
);
return(op);
}
}
protected override IEnumerable <MItemH> GetEntities(AccellosContext entityContext)
{
throw new NotImplementedException();
}
protected override IEnumerable <LRyderCiscoSncycCnt> GetEntities(AccellosContext entityContext)
{
throw new NotImplementedException();
}
protected override CProsMvt GetEntity(AccellosContext entityContext, string id)
{
throw new NotImplementedException();
}
protected override LRyderCiscoSncycCnt GetEntity(AccellosContext entityContext, int id)
{
throw new NotImplementedException();
}
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;
}
}
}
}
