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 object ExecScalar(string commandID, params object[] parameters)
{
string cmdText = GetCommandText(commandID);
CommandType commandType = GetCommandType(commandID);
OracleParameter[] param = ConvertParam(parameters, ref cmdText, commandType);
Util.Log.Debug(GetNoParamCommandText(commandID, cmdText, param));
return(ORACLEHelper.ExecuteScalar(ConnectionString, commandType, cmdText, param));
}
public DataTable FillDataTable(string commandID, params object[] parameters)
{
string cmdText = GetCommandText(commandID);
CommandType commandType = GetCommandType(commandID);
OracleParameter[] param = ConvertParam(parameters, ref cmdText, commandType);
Util.Log.Debug(GetNoParamCommandText(commandID, cmdText, param));
DataTable dtData = ORACLEHelper.ExecuteDataset(ConnectionString, commandType, cmdText, param).Tables[0];
dtData.TableName = "Table";
return(dtData);
}
public DataTable GetDataPage(string commandID, int currentPage, int pageSize, out int TotalCount, out int PageCount, params object[] parameters)
{
TotalCount = 0;
PageCount = 0;
string cmdText = GetCommandText(commandID);
CommandType commandType = GetCommandType(commandID);
OracleParameter[] param = ConvertParam(parameters, ref cmdText, commandType);
//计算总记录条数
if (commandType == CommandType.Text)
{
string countCmd = cmdText;
Regex regex = new Regex("[\\s]order[\\s]", RegexOptions.IgnoreCase);
if (regex.IsMatch(countCmd))
{
Match match = regex.Match(countCmd);
countCmd = countCmd.Substring(0, match.Index);
}
if (!string.IsNullOrEmpty(countCmd))
{
countCmd = "\r\n SELECT COUNT(0) FROM (" + countCmd + ") T";
Util.Log.Debug(GetNoParamCommandText(string.Empty, countCmd, param));
TotalCount = (int)ORACLEHelper.ExecuteScalar(ConnectionString, commandType, countCmd, param);
}
}
else if (commandType == CommandType.StoredProcedure)
{
throw new Exception("不支持存储过程分页查询");
}
Util.Log.Debug(GetNoParamCommandText(commandID, cmdText, param));
PageCount = GetPageCount(TotalCount, pageSize);
if (currentPage == 0 || currentPage > PageCount)
{
currentPage = PageCount;
}
if (currentPage == 0)
{
currentPage = 1;
}
return(ORACLEHelper.ExecuteDataset(ConnectionString, commandType, cmdText, currentPage, pageSize, commandID, param).Tables[0]);
}
//打卡：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);
}
}
/// <summary>
/// 根据时间 判断数据库中是否存在当天数据，
/// 如果存在，删除
/// </summary>
/// <param name="entityList"></param>
public void deleteSameData(List <OpenRecord> entityList, ref bool ERRFLAG)
{
if (entityList.Count > 0)
{
try
{
string recordDate = DateTime.Parse(entityList.First().recordTime, new DateTimeFormatInfo()
{
FullDateTimePattern = "yyyy-MM-dd HH:mi:ss"
}).ToString("yyyy-MM-dd");
string sql_delete = "delete gj_opencaserecords t where to_char(t.记录时间,'yyyy-MM-dd') = '" + recordDate + "'";
int count = ORACLEHelper.ExecuteSql(sql_delete);
}
catch (Exception err)
{
ERRFLAG = true;
AppInfo.WriteLogs(err.Message);
}
}
}
public int ExecTran(string[] commandIDs, List <object[]> parameters)
{
int retval = 0;
using (OracleConnection connection = new OracleConnection(ConnectionString))
{
connection.Open();
OracleTransaction transaction = connection.BeginTransaction();
try
{
for (int i = 0; i < commandIDs.Length; i++)
{
string cmdText = GetCommandText(commandIDs[i]);
CommandType commandType = GetCommandType(commandIDs[i]);
OracleParameter[] param = (parameters == null ? null : ConvertParam(parameters[i], ref cmdText, commandType));
Util.Log.Debug(GetNoParamCommandText(commandIDs[i], cmdText, param));
int ret = ORACLEHelper.ExecuteNonQuery(transaction, GetCommandType(commandIDs[i]), cmdText, param);
retval = retval + ret;
}
transaction.Commit();
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
catch (Exception ex)
{
retval = 0;
transaction.Rollback();
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
throw ex;
}
}
return(retval);
}
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);
}
/// <summary>
/// 向数据库插入数据
/// </summary>
/// <param name="entityList"></param>
private void insertDataToDataBase(List <OpenRecord> entityList, ref bool ERRFLAG)
{
if (ERRFLAG)
{
return;
}
int minCount = 0;
try
{
while (minCount < entityList.Count)
{
int maxCount = entityList.Count > 30 + minCount ? 30 + minCount : entityList.Count;
StringBuilder sql = new StringBuilder("insert into gj_opencaserecords (线路名称,车牌号,持卡人,换出内胆编号,换入内胆编号,记录时间) select * from ( ");
for (int i = minCount; i < maxCount; i++)
{
OpenRecord entity = entityList[i];
sql.Append("select '").Append(entity.lineName + "' 线路名称,'").Append(entity.carNum + "' 车牌号,'").Append(entity.owiner + "' 持卡人,'").Append(entity.ouCardNum + "' 换出内胆编号,'").Append(entity.inCardNum + " ' 换入内胆编号,to_date('").Append(entity.recordTime).Append("','yyyy-MM-dd HH24:mi:ss') 记录时间 from dual ");
if (i < maxCount - 1)
{
sql.Append(" union all ");
}
}
sql.Append(" )");
//AppInfo.WriteLogs(sql.ToString());
//ORACLEHelper helper = new ORACLEHelper();
ORACLEHelper.ExecuteSql(sql.ToString());
//bool result = RCD.RCDB.Execute(sql.ToString());
minCount += 30;
}
}
catch (Exception err)
{
ERRFLAG = true;
AppInfo.WriteLogs(err.Message);
}
}
