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 <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 <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);
}
}
private static void LoadSchemas(StringBuilder sb, string connectionString, string sql)
{
sb.Append("PUBLIC;");
var manager = new OracleManager(connectionString);
using (var reader = manager.ExecuteReader(System.Data.CommandType.Text, sql))
{
while (reader.Read())
{
string schemaName = reader.GetString(0);
if (!excluded.Contains(schemaName))
{
sb.Append(schemaName);
sb.Append(";");
}
}
}
}
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 <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);
}
}
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);
}
}
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);
}
}
