public ModelMR GetMeetingRoomByID(string id) { string sql = string.Format(@"select a.roomid, a.roomname, a.roomabbr, a.siteid, b.site, b.plant, b.floor, a.eqipment, a.ext, a.cnpc, a.ip, a.accessright, a.postperiod, a.imgpath, a.minp, a.maxp from MBS_ROOM a, mbs_area b where a.siteid = b.siteid and roomid ='{0}' order by roomname", id); DataSet ds = DALService.ExecuteDataSet(sql); return(ds.Tables[0].Rows.Count == 1 ? GetMRList(ds).First() : null); //return GetMRList(ds); }
public IEnumerable <ModelRecord> GetBookingData(string Roomid, string sdate, string edate, string userid) { string sql = string.Format(@" select t.roomid, t.RECORDID, t.starttime, t.endtime, decode(lower(t.applierid),'{3}','APPL',t.booktype) booktype, t.updatet, t.applierid, t.applier, t.applierext, '' as dateday from MBS_RECORD t where t.starttime between to_date('{0}', 'yyyy/mm/dd') and to_date('{1}', 'yyyy/mm/dd')+1 and t.roomid in ('{2}') ", sdate, edate, Roomid, userid); DataSet ds = DALService.ExecuteDataSet(sql); IEnumerable <ModelRecord> ret = GetBRecordList(ds.Tables[0]); ret = ret.Concat( GenRangeSysBookRecord(new List <string> { Roomid }, sdate, edate)); ds.Dispose(); return(ret); }
public IEnumerable <ModelMR> GetMeetingRoom(string site, string plant, string floor) { string sSite = site == "" || site == null ? " 1=1 " : " b.site = '" + site + "'"; string sPlant = plant == "" || plant == null ? " 1=1 " : " b.plant = '" + plant + "'"; string sFloor = floor == "" || floor == null ? " 1=1 " : " b.floor = '" + floor + "'"; IEnumerable <ModelMR> ret = null; string sql = string.Format(@"select a.roomid, a.roomname, a.roomabbr, a.siteid, b.site, b.plant, b.floor, a.eqipment, a.ext, a.cnpc, a.ip, a.accessright, a.postperiod, a.imgpath, a.minp, a.maxp from MBS_ROOM a, mbs_area b where a.siteid = b.siteid and {0} and {1} and {2} order by roomname ", sSite, sPlant, sFloor); DataSet ds = DALService.ExecuteDataSet(sql); return(GetMRList(ds)); }
private string GetSiteid(string site, string plant, string floor) { string sql = string.Format(@" select siteid from MBS_AREA t where t.site = '{0}' and t.plant = '{1}' and t.floor = '{2}' ", site, plant, floor); return(DALService.ExecuteDataSet(sql).Tables[0].Rows[0][0].ToString()); }
private IEnumerable <ModelRecord> GenRangeSysBookRecord(List <string> roomid, string sdate, string edate) { List <ModelRecord> ret = new List <ModelRecord>(); string sql = string.Format(@" select t.roomid, t.sid as RECORDID, to_date('{0} ' || SUBSTR(starttime, 1, 2) || ':' || SUBSTR(starttime, 3, 2) || ':' || '00', 'yyyy/mm/dd hh24:mi:ss') as starttime, to_date('{0} ' || SUBSTR(endtime, 1, 2) || ':' || SUBSTR(endtime, 3, 2) || ':' || '00', 'yyyy/mm/dd hh24:mi:ss') as endtime, sysdate as UPDATET, 'SYSL' as booktype, 'SYSTEM' as APPLIER, 'SYSTEM' as applierid, '' as applierext, dateday from MBS_LOCKROOM t where t.roomid in ('{1}') ", sdate, string.Join("','", roomid.ToArray())); IEnumerable <ModelRecord> ret2 = GetBRecordList(DALService.ExecuteDataSet(sql).Tables[0]); DateTime dts1 = DateTime.Parse(sdate); DateTime dts = DateTime.Parse(sdate); DateTime dte = DateTime.Parse(edate); int i = 0; while (DateTime.Compare(dte, dts) >= 0) { IEnumerable <ModelRecord> ret3 = ret2 .Where(x => x.DATEDAY == ((int)dts.DayOfWeek).ToString()) .Select(x => x); if (ret3.Count() > 0) { ret3 = ret3.Select(x => { x.STARTTIME = DateTime.Parse(x.STARTTIME).AddDays(i).ToString("yyyy/MM/dd HH:mm:ss"); x.ENDTIME = DateTime.Parse(x.ENDTIME).AddDays(i).ToString("yyyy/MM/dd HH:mm:ss"); return(x); }); ret.AddRange(ret3.ToList()); } i = i + 1; dts = dts1.AddDays(i); } return(ret); }