public Result <List <string> > getLineUDChangedData(string startTime, string endTime)
{
Result <List <string> > result = new Result <List <string> >();
string sql = $"select distinct(ud.线路id) from syn_log s ,gj_公交线路上下行表 ud, gj_公交线路表 l, gj_公交线路组 g, gj_depart d where s.ybid=ud.f_id and ud.线路id = l.f_id and l.所属线路组 = g.f_id and g.单位id = d.f_id and d.f_id in {ConstInfo.DepartRight} and s.adddatetime >= to_date('{startTime}', 'yyyy-MM-dd HH:mi:ss') and s.adddatetime < to_date('{endTime}', 'yyyy-MM-dd HH:mi:ss') and s.sjly = 'GJ_公交线路上下行表'";
var context = new ORACLEHelper();
try
{
DataTable dt = context.QueryTable(sql);
LogTimesFactory factory = new LogTimesFactory();
factory.createLogTimes(LogTimeType.ChangeData);
factory.logTimes(sql);
List <string> list = new List <string>();
foreach (DataRow item in dt.Rows)
{
list.Add(item[0].ToString());
}
result.data = list;
}
catch (Exception err)
{
result.addError(err.Message);
log.Error(MethodBase.GetCurrentMethod() + err.Message);
}
return(result);
}
public Result <List <LineStation> > getLineStationByUDID(int UDID)
{
Result <List <LineStation> > result = new Result <List <LineStation> >();
string sql = $" select ls.线路上下行id,ls.顺序, ls.站点id,s.名称,s.gpsx2,s.gpsy2,decode(substr(s.名称,0,1),'.',1,0) realStation from gj_线路站点表 ls,gj_站点 s,gj_公交线路上下行表 ud, gj_公交线路表 l, gj_公交线路组 g, gj_depart d where ls.站点id = s.f_id and ls.线路上下行id = ud.f_id and ud.线路id = l.f_id and l.所属线路组 = g.f_id and g.单位id = d.f_id and d.f_id in {ConstInfo.DepartRight} and ls.线路上下行id ={UDID} order by ls.顺序 ";
var context = new ORACLEHelper();
try
{
DataTable dt = context.QueryTable(sql);
LogTimesFactory factory = new LogTimesFactory();
factory.createLogTimes(LogTimeType.LineStation);
factory.logTimes(sql);
result.data = TableToList(dt);
}
catch (Exception err)
{
result.addError(err.Message);
log.Error(MethodBase.GetCurrentMethod() + err.Message);
}
return(result);
}
public Result <Bus> getBusById(int id)
{
Result <Bus> result = new Result <Bus>();
string sql = $"select c.f_id,c.线路id,c.车牌,c.是否报废 from gj_公交车 c ,gj_公交线路表 l,gj_公交线路组 g ,gj_depart d where c.线路id = l.f_id and l.所属线路组 = g.f_id and g.单位id = d.f_id and d.f_id in {ConstInfo.DepartRight} and c.f_id={id}";
var context = new ORACLEHelper();
try
{
DataTable dt = context.QueryTable(sql);
LogTimesFactory factory = new LogTimesFactory();
factory.createLogTimes(LogTimeType.Bus);
factory.logTimes(sql);
result.data = TableToList(dt).FirstOrDefault();
}
catch (Exception err)
{
result.addError(err.Message);
log.Error(MethodBase.GetCurrentMethod() + err.Message);
}
return(result);
}
public Result <Depart> getDepartById(int id)
{
Result <Depart> result = new Result <Depart>();
string sql = $"select d.f_id,d.f_name,d.f_pid,decode(d.f_pid,0,1,2) departLevel from gj_depart d where d.f_id in {ConstInfo.DepartAll} and d.f_id={id}";
var context = new ORACLEHelper();
try
{
DataTable dt = context.QueryTable(sql);
LogTimesFactory factory = new LogTimesFactory();
factory.createLogTimes(LogTimeType.Depart);
factory.logTimes(sql);
result.data = TableToList(dt).FirstOrDefault();
}
catch (Exception err)
{
result.addError(err.Message);
log.Error(MethodBase.GetCurrentMethod() + err.Message);
}
return(result);
}
public Result <LineUD> getLineUDById(int id)
{
Result <LineUD> result = new Result <LineUD>();
string sql = $"select ud.线路id,ud.f_id,ud.公交线路,decode(ud.updown,1,2,1) 方向,ud.票价,ud.首班车时间,ud.末班车时间 from gj_公交线路上下行表 ud, gj_公交线路表 l, gj_公交线路组 g, gj_depart d where ud.线路id = l.f_id and l.所属线路组 = g.f_id and g.单位id = d.f_id and d.f_id in {ConstInfo.DepartRight} and ud.f_id = { id}";
var context = new ORACLEHelper();
try
{
DataTable dt = context.QueryTable(sql);
LogTimesFactory factory = new LogTimesFactory();
factory.createLogTimes(LogTimeType.LineUD);
factory.logTimes(sql);
result.data = TableToList(dt).FirstOrDefault();
}
catch (Exception err)
{
result.addError(err.Message);
log.Error(MethodBase.GetCurrentMethod() + err.Message);
}
return(result);
}
public Result <DriverClock> getBusById(int id)
{
Result <DriverClock> result = new Result <DriverClock>();
string sql = $"select c.车牌, lpad(t.driver_id, 5, '0') driverNo, t.duty_time tradeTime, t.duty_flag from v_driver_ban_last@ytiic t, bus_info@ytiic b, gj_公交车 c where c.车牌 =b.bus_card_no and t.bus_id = b.bus_id and c.f_id ={id}";
var context = new ORACLEHelper();
try
{
DataTable dt = context.QueryTable(sql);
LogTimesFactory factory = new LogTimesFactory();
factory.createLogTimes(LogTimeType.Driver);
factory.logTimes(sql);
result.data = TableToList_DriverClock(dt).FirstOrDefault();
}
catch (Exception err)
{
result.addError(err.Message);
log.Error(MethodBase.GetCurrentMethod() + err.Message);
}
return(result);
}
public Result <Driver> getDriverById(string id)
{
Result <Driver> result = new Result <Driver>();
//select lpad(OPER_ID,5,'0') driverNo,trim(OPER_NAME)driverName, nvl(d.线路id,-1) lineid,nvl(d.using,1)isWork from EMPLOYEE_INFO@ytiic e,gj_驾驶员表 d,gj_公交线路表 l,gj_公交线路组 g, gj_depart t where e.driver_flag='Y' and trim(e.oper_name)=d.姓名(+) and d.线路id=l.f_id and l.所属线路组=g.f_id and g.单位id=t.f_id and t.f_id in(2,3,4,5,10)
string sql = $"select lpad(OPER_ID,5,'0') 员工号,trim(OPER_NAME) 姓名, nvl(d.线路id,-1) 线路id,nvl(d.using,1)isWork from EMPLOYEE_INFO@ytiic e,(select * from (select d.线路id,d.姓名,d.using,row_number() over(partition by d.姓名 order by d.modify_time desc)sn from gj_驾驶员表 d)d where d.sn=1) d,gj_公交线路表 l,gj_公交线路组 g, gj_depart t where e.driver_flag='Y' and trim(e.oper_name)=d.姓名(+) and d.线路id=l.f_id(+) and l.所属线路组=g.f_id(+) and g.单位id=t.f_id(+) and (t.f_id in {ConstInfo.DepartRight} or t.f_id is null) and lpad(OPER_ID,5,'0')='{id}'";
var context = new ORACLEHelper();
try
{
DataTable dt = context.QueryTable(sql);
LogTimesFactory factory = new LogTimesFactory();
factory.createLogTimes(LogTimeType.Driver);
factory.logTimes(sql);
result.data = TableToList(dt).FirstOrDefault();
}
catch (Exception err)
{
result.addError(err.Message);
log.Error(MethodBase.GetCurrentMethod() + err.Message);
}
return(result);
}
public Result <List <Line> > getLineByDepartId(int?id)
{
Result <List <Line> > result = new Result <List <Line> >();
string sql = $"select l.f_id,l.线路名称,d.f_id departId from gj_公交线路表 l,gj_公交线路组 g ,gj_depart d where l.所属线路组 = g.f_id and g.单位id = d.f_id and d.f_id in {ConstInfo.DepartRight} ";
sql += id == null ? "" : $" and d.f_id ={id} ";
var context = new ORACLEHelper();
try
{
DataTable dt = context.QueryTable(sql);
LogTimesFactory factory = new LogTimesFactory();
factory.createLogTimes(LogTimeType.Line);
factory.logTimes(sql);
result.data = TableToList(dt);
}
catch (Exception err)
{
result.addError(err.Message);
log.Error(MethodBase.GetCurrentMethod() + err.Message);
}
return(result);
}