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