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); } }