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