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);
}
//打卡：http://117.34.118.23:9101/mediaplayer/Driver/reportDriverClockInfo?jsonStr=
//司机：http://117.34.118.23:9101/mediaplayer/Driver/reportDriverInfo?jsonStr=
private async void push()
{
try
{
log.Info($"API do timers {timers++}");
DateTime current = DateTime.Now;
string sql = $"select t.bus_card_no 车牌, t.DRIVER_ID,t.driver_name, t.tRADE_DATE|| t.TRADE_TIME tradeTime, t.DUTY_FLAG from MANAGE_REC_DRIVER_TOLED@ytiic t where t.tRADE_DATE='{current.ToString("yyyyMMdd")}' and t.TRADE_TIME<='{current.ToString("HHmmss")}'";
DataTable dt = new DataTable();
ORACLEHelper context = new ORACLEHelper();
dt = context.QueryTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
List <DriverClock> dataList = TableToList_DriverClock(dt);
HttpClient client = new HttpClient()
{
BaseAddress = new Uri(ConstInfo.URL_ZhongHangXun)
};
var jsonStr = JsonConvert.SerializeObject(dataList);
Dictionary <string, string> dict = new Dictionary <string, string>();
dict.Add("jsonStr", jsonStr);//bmpm/Driver/reportDriverClockInfo
var response = await client.PostAsync("mediaplayer/Driver/reportDriverClockInfo", new FormUrlEncodedContent(dict));
var result = await response.Content.ReadAsAsync <HttpError>();
if (response.StatusCode == HttpStatusCode.OK && result.code == "200")
{
sql = $"insert into gj_driverclock select * from MANAGE_REC_DRIVER_TOLED@ytiic t where t.tRADE_DATE='{current.ToString("yyyyMMdd")}' and t.TRADE_TIME<='{current.ToString("HHmmss")}'";
context.ExecuteSql(sql);
sql = $"delete from MANAGE_REC_DRIVER_TOLED@ytiic t where t.TRADE_TIME<='{current.ToString("HHmmss")}'";
int count = context.ExecuteSql(sql);
log.Info($"推送成功，删除记录{count}条");
}
else
{
log.Error("调用接口失败：" + result.code + result.message);
}
}
}
catch (Exception err)
{
log.Error(err);
}
}
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);
}
private async void push()
{
ORACLEHelper context = new ORACLEHelper();
DateTime current = DateTime.Now;
string sql = $"select lpad(OPER_ID,5,'0') 员工号,trim(OPER_NAME) 姓名,-1 线路id,flag isWork from DRIVER_INFO_CHANGE_TOLED@ytiic where UPDATE_TIME<=to_date('{current.ToString("yyyyMMdd HH:mm:ss")}','yyyy-MM-dd HH24:mi:ss')";
DataTable dt = new DataTable();
try
{
dt = context.QueryTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
List <Driver> dataList = TableToList(dt);
HttpClient client = new HttpClient()
{
BaseAddress = new Uri(ConstInfo.URL_ZhongHangXun)
};
var jsonStr = JsonConvert.SerializeObject(dataList);
Dictionary <string, string> dict = new Dictionary <string, string>();
dict.Add("jsonStr", jsonStr);//bmpm/Driver/reportDriverInfo
var response = await client.PostAsync("/mediaplayer/Driver/reportDriverInfo", new FormUrlEncodedContent(dict));
var result = await response.Content.ReadAsAsync <HttpError>();
if (response.StatusCode == HttpStatusCode.OK && result.code == "200")
{
sql = $"delete from DRIVER_INFO_CHANGE_TOLED@ytiic t where t.UPDATE_TIME<to_date('{current.ToString("yyyy-MM-dd HH:mm:ss")}','yyyy-MM-dd HH24:mi:ss')";
context.ExecuteSql(sql);
}
else
{
log.Error("调用接口失败：" + result.code + result.message);
}
}
}
catch (Exception err)
{
log.Error(err);
}
}
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);
}