/// <summary>
/// 查询某日某车是否有维修记录
/// </summary>
/// <param name="_reqLicenseID">车牌</param>
/// <param name="_reqDate">日期</param>
/// <returns>维修记录</returns>
public XmlDocument getDayFixedByLicense(string _reqLicenseID, string _reqDate)
{
result = XmlProvider.Document("configdata");
string _sql = readXml("getDayFixedByLicense");
try
{
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter _licenseID = new SqlParameter("LicenseID", SqlDbType.NVarChar, 50);
_licenseID.Value = _reqLicenseID;
_cmd.Parameters.Add(_licenseID);
SqlParameter _startTime = new SqlParameter("StartTime", SqlDbType.NVarChar, 50);
_startTime.Value = Convert.ToDateTime(_reqDate);
_cmd.Parameters.Add(_startTime);
result = SQLProvider.GetData(_cmd);
}
}
catch (Exception _exc)
{
Provider.LogErr(result, _exc);
}
return(this.result);
}
/// <summary>
/// 查询某车牌的车辆在某天的车辆状态
/// </summary>
/// <param name="_LicenseID"></param>
/// <param name="_Day"></param>
/// <returns></returns>
public string getCarStateByLicenseIDAndDay(string _LicenseID, string _Day)
{
result = XmlProvider.Document("configdata");
string _sql = readXml("getCarStateByLicenseIDAndDay");//Car.xml 中的 查询某车牌的车辆在某天的车辆状态 的标签的 type 为 getCarStateByLicenseIDAndDay
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter _dChangeDateTime = new SqlParameter("ChangeDateTime", SqlDbType.NVarChar, 50);
_dChangeDateTime.Value = _Day;
_cmd.Parameters.Add(_dChangeDateTime);
SqlParameter _dLicenseID = new SqlParameter("LicenseID", SqlDbType.NVarChar, 50);
_dLicenseID.Value = _LicenseID;
_cmd.Parameters.Add(_dLicenseID);
result = SQLProvider.GetData(_cmd);
}
if (this.result.DocumentElement.SelectSingleNode("SchemaTable") == null)
{
return("");
}
else
{
return(this.result.DocumentElement.SelectSingleNode("SchemaTable/NewCarState").InnerText.ToString().Trim());//车辆状态
}
}
//根据车牌查找所有对应的待出车记录
public XmlDocument getListByLicenseID(string _reqLicenseID)
{
this.result = XmlProvider.Document("sqldata");
try
{
string _sql = readXml("getListByLicenseID");
//读取数据
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter _appLicenseID = new SqlParameter("LicenseID", SqlDbType.NVarChar, 50);
_appLicenseID.Value = _reqLicenseID;
_cmd.Parameters.Add(_appLicenseID);
result = SQLProvider.GetData(_cmd);
}
}
catch (Exception _exc)
{
Provider.LogErr(this.result, _exc);
}
return(this.result);
}
//判断是否为司机
public string isDriver(string _account)
{
XmlDocument _result = XmlProvider.Document("data");
string _sql = readXml("getDriver");
//判断司机表是否存在该用户
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter _driverAccount = new SqlParameter("DriverAccount", SqlDbType.NVarChar, 50);
_driverAccount.Value = _account;
_cmd.Parameters.Add(_driverAccount);
_result = SQLProvider.GetData(_cmd);
}
if (_result.DocumentElement.SelectSingleNode("SchemaTable") != null)
{
return(_result.DocumentElement.SelectSingleNode("SchemaTable/Player").InnerText.Trim());
}
else
{
return("");
}
}
//获取所有车辆
public XmlDocument getCarList()
{
result = XmlProvider.Document("configdata");
string _sql = readXml("getCarList");
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
result = SQLProvider.GetData(_cmd);
}
return(this.result);
}
//获取调度员账号
public string getSysAccount()
{
result = XmlProvider.Document("sqldata");
string _sql = readXml("getSysAccount");
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
result = SQLProvider.GetData(_cmd);
}
return(result.DocumentElement.SelectSingleNode("SchemaTable/PermitAccount").InnerText.ToString().Trim());
}
/// <summary>
/// 查询所有车辆状态为出行、维修、空闲的车辆的车牌集合
/// </summary>
/// <returns></returns>
public XmlDocument getEnableCarLicense()
{
result = XmlProvider.Document("configdata");
string _sql = readXml("getEnableCarLicense");//Car.xml中的获取所有车辆状态为出行、维修、空闲的车辆的车牌的标签的type为 getEnableCarLicense
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
this.result = SQLProvider.GetData(_cmd);
}
return(result);
}
//获取有出车天数的车牌
public XmlDocument getCarListOnDesc(string _licenseidList)
{
result = XmlProvider.Document("sqldata");
string _sql = " SELECT SUM(CAST(DAYS as int)) AS DAYS ,LicenseID FROM CAR_APPLIED WHERE AppliedStatue!='拒绝' and AppliedStatue!='取消' AND LicenseID!='NULL' AND " + _licenseidList + " group by licenseid order by DAYS ASC";
//读取数据
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
this.result = SQLProvider.GetData(_cmd);
}
return(result);
}
//获取有出车天数的车牌
public string getApplyCount(string _SqlLicenseID, DateTime _SqlStart, DateTime _SqlEnd)
{
result = XmlProvider.Document("sqldata");
string _sql = "Select Count(*) AS NUM from Car_Applied where licenseid='" + _SqlLicenseID + "' and StartTime>='" + _SqlStart.ToString() + "' and StartTime<='" + _SqlEnd.ToString() + "'";
//读取数据
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
this.result = SQLProvider.GetData(_cmd);
}
return(result.DocumentElement.SelectSingleNode("SchemaTable/Num").InnerText.ToString().Trim());
}
//获取需要调度员控制的车辆数量
public int getControlNum()
{
result = XmlProvider.Document("configdata");
string _sql = readXml("getControlCarNum");
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
result = SQLProvider.GetData(_cmd);
}
return(Convert.ToInt32(result.DocumentElement.SelectSingleNode("SchemaTable/SetValue").InnerText.ToString().Trim()));
}
/// <summary>
/// 查询临时调度员账号
/// </summary>
/// <returns></returns>
public XmlDocument getLsDispatcher()
{
result = XmlProvider.Document("configdata");
string _sql = readXml("getLsDispather");//Dispather.xml中的获取临时调度员账号的标签的type为getLsDispather
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter resourceID = new SqlParameter("ResourceID", SqlDbType.NVarChar, 50);
resourceID.Value = "LSSYSTEM";
_cmd.Parameters.Add(resourceID);
result = SQLProvider.GetData(_cmd);
}
return(this.result);
}
//根据账号查找司机信息
public XmlDocument getSingleDetailByAccount(string _account)
{
result = XmlProvider.Document("configdata");
string _sql = readXml("getByAccount");
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter account = new SqlParameter("DriverAccount", SqlDbType.NVarChar, 50);
account.Value = _account;
_cmd.Parameters.Add(account);
result = SQLProvider.GetData(_cmd);
}
return(this.result);
}
//获取待联系方式的车辆记录ByLicenseID
public XmlDocument getSingleCarByLicenseID(string _licenseID)
{
result = XmlProvider.Document("configdata");
string _sql = readXml("getSingleCarByLicenseID");
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter licenseID = new SqlParameter("LicenseID", SqlDbType.NVarChar, 50);
licenseID.Value = _licenseID;
_cmd.Parameters.Add(licenseID);
result = SQLProvider.GetData(_cmd);
}
return(this.result);
}
//获取待联系方式的车辆记录
public XmlDocument getSingleCarAndCarTel(string _guid)
{
result = XmlProvider.Document("configdata");
string _sql = readXml("getSingleCarAndCarTel");
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter guid = new SqlParameter("Guid", SqlDbType.NVarChar, 50);
guid.Value = _guid;
_cmd.Parameters.Add(guid);
result = SQLProvider.GetData(_cmd);
}
return(this.result);
}
//获取司机详情
public XmlDocument getDriverDetail(string _driverid)
{
result = XmlProvider.Document("configdata");
string _sql = readXml("getDriverDetail");
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter driverid = new SqlParameter("DriverID", SqlDbType.NVarChar, 50);
driverid.Value = _driverid;
_cmd.Parameters.Add(driverid);
result = SQLProvider.GetData(_cmd);
}
return(this.result);
}
/// <summary>
/// 获取车辆状态
/// </summary>
/// <param name="_reqLicenseID"></param>
/// <returns></returns>
public string getStatue(string _reqLicenseID)
{
this.result = XmlProvider.Document("sqldata");
string _sql = readXml("getCarStatue");
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter licenseID = new SqlParameter("LicenseID", SqlDbType.NVarChar, 50);
licenseID.Value = _reqLicenseID;
_cmd.Parameters.Add(licenseID);
result = SQLProvider.GetData(_cmd);
}
return(this.result.DocumentElement.SelectSingleNode("SchemaTable/CarStatue").InnerText.ToString().Trim());//车辆状态
}
//司机获取列表
public XmlDocument getListD(string _account)
{
result = XmlProvider.Document("sqldata");
string _sql = readXml("getListD");
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter _coAccount = new SqlParameter("COAccount", SqlDbType.NVarChar, 50);
_coAccount.Value = _account;
_cmd.Parameters.Add(_coAccount);
result = SQLProvider.GetData(_cmd);
}
return(this.result);
}
//根据ID查找某一预约信息
public XmlDocument getApply(string _guid)
{
result = XmlProvider.Document("sqldata");
string _sql = readXml("getApply");
//读取数据
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter _applyID = new SqlParameter("CarAppliedID", SqlDbType.NVarChar, 50);
_applyID.Value = _guid;
_cmd.Parameters.Add(_applyID);
result = SQLProvider.GetData(_cmd);
}
return(this.result);
}
/// <summary>
/// 车辆维修列表
/// </summary>
/// <returns></returns>
public XmlDocument getFixedList()
{
result = XmlProvider.Document("configdata");
string _sql = readXml("getFixdList");
try
{
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
result = SQLProvider.GetData(_cmd);
}
}
catch (Exception _exc)
{
Provider.LogErr(result, _exc);
}
return(result);
}
private XmlDocument TokenData()
{
XmlDocument _result = XmlProvider.Document("sqldata");
try
{
//获取用户
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = "select top 1 * from V_Token where TokenID=@id";
SqlParameter _id = new SqlParameter("id", SqlDbType.NVarChar, 50);
_id.Value = this.token;
_cmd.Parameters.Add(_id);
_result = SQLProvider.GetData(_cmd);
//XmlDocument _api = XmlProvider.Document("api");
//_api.Load("http://api.qgj.cn/webapi/profile.asmx/GetUser?User="******"SchemaTable/TokenAccount").InnerText.Trim());
//设置用户名
XmlElement _name = _result.CreateElement("TokenName");
GetUserInfo user = new GetUserInfo(_result.DocumentElement.SelectSingleNode("SchemaTable/TokenAccount").InnerText.Trim());
_name.InnerXml = "<![CDATA[" + user.getUserName() + "]]>";
_result.DocumentElement.SelectSingleNode("SchemaTable").AppendChild(_name);
//设置权限节点
XmlElement _realPermit = _result.CreateElement("TokenPermit");
_realPermit.InnerXml = "<![CDATA[ ]]>";
_result.DocumentElement.SelectSingleNode("SchemaTable").AppendChild(_realPermit);
}
}
catch (Exception _exc)
{
Provider.LogErr(_result, _exc);
}
return(_result);
}
//查找某车某天冲突记录(id)
public XmlDocument clashApply(string _delayLicenseid, DateTime _dalyStarTtime)
{
result = XmlProvider.Document("sqldata");
string _sql = readXml("delayClashApply");
//读取数据
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter _licenseid = new SqlParameter("LicenseID", SqlDbType.NVarChar, 50);
_licenseid.Value = _delayLicenseid;
_cmd.Parameters.Add(_licenseid);
SqlParameter _starttime = new SqlParameter("StartTime", SqlDbType.NVarChar, 50);
_starttime.Value = _dalyStarTtime;
_cmd.Parameters.Add(_starttime);
result = SQLProvider.GetData(_cmd);
}
return(result);
}
//生成每月车辆报表
public XmlDocument getMounthReport(string _date, string _nextDate, string _lincenseID)
{
this.result = XmlProvider.Document("data");
result = XmlProvider.Document("configdata");
string _sql = readXml("CarMounthReport");
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter licenseid = new SqlParameter("LicenseID", SqlDbType.NVarChar, 50);
licenseid.Value = _lincenseID;
_cmd.Parameters.Add(licenseid);
SqlParameter mounth = new SqlParameter("ThisMounth", SqlDbType.NVarChar, 50);
mounth.Value = _date;
_cmd.Parameters.Add(mounth);
SqlParameter nextMounth = new SqlParameter("NextMounth", SqlDbType.NVarChar, 50);
nextMounth.Value = _nextDate;
_cmd.Parameters.Add(nextMounth);
result = SQLProvider.GetData(_cmd);
}
return(result);
}
//查找某车某日记录
public XmlDocument getDayCar(string _reqLicenseID, string _stime)
{
result = XmlProvider.Document("sqldata");
string _sql = readXml("getDayCar");
DateTime _stimeDate = Convert.ToDateTime(_stime);
//读取数据
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter _licenseid = new SqlParameter("LicenseID", SqlDbType.NVarChar, 50);
_licenseid.Value = _reqLicenseID;
_cmd.Parameters.Add(_licenseid);
SqlParameter _starttime = new SqlParameter("starttime", SqlDbType.NVarChar, 50);
_starttime.Value = _stimeDate.Year + "-" + _stimeDate.Month + "-" + _stimeDate.Day + " 23:00:00";
_cmd.Parameters.Add(_starttime);
result = SQLProvider.GetData(_cmd);
}
return(this.result);
}
//获取权限
public string getPermit(string _account)
{
XmlDocument _result = XmlProvider.Document("sqldata");
string _sql = readXml("getPermit");
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter _permitAccount = new SqlParameter("PermitAccount", SqlDbType.NVarChar, 50);
_permitAccount.Value = _account;
_cmd.Parameters.Add(_permitAccount);
_result = SQLProvider.GetData(_cmd);
}
if (_result.DocumentElement.SelectSingleNode("SchemaTable") != null)
{
return(_result.DocumentElement.SelectSingleNode("SchemaTable/ResourcePermit").InnerText.Trim());
}
else
{
return("");
}
}
//获取部门领导账号
public string getDepartAccount(string _departID)
{
result = XmlProvider.Document("configdata");
string _sql = readXml("getDepartLeader");
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter departID = new SqlParameter("DepartID", SqlDbType.NVarChar, 50);
departID.Value = _departID;
_cmd.Parameters.Add(departID);
result = SQLProvider.GetData(_cmd);
}
if (result.DocumentElement.SelectNodes("SchemaTable").Count > 0)
{
return(result.DocumentElement.SelectSingleNode("SchemaTable/DepartLeaderAccount").InnerText.ToString().Trim());
}
else
{
return("");
}
}
public XmlDocument getFreeCarListOnDesc(DateTime _startReq, int _dayReq, int _tripNum)
{
_startReq = Convert.ToDateTime(_startReq.ToShortDateString() + " 00:00:00");
int _minWeekDays = int.MaxValue;
XmlDocument _desc = XmlProvider.Document("data");
XmlDocument _list = (XmlDocument)Provider.Invoke("CLDD.Providers.ApplyService.getFreeCarList", new object[] { this.context }, new object[] { _startReq, _dayReq, _tripNum });
string _licenseidList = "("; //符合条件的licenseid
//判断星期几
int[] _dayOfWeek = { 7, 1, 2, 3, 4, 5, 6 };
//查询车辆月出行天数
int _pre = 13 + _dayOfWeek[Convert.ToInt32(_startReq.DayOfWeek)];
foreach (XmlNode _section in _list.DocumentElement.SelectNodes("SchemaTable"))
{
if (_section.SelectSingleNode("LicenseID").InnerText.ToString().Trim() != _list.DocumentElement.LastChild.SelectSingleNode("LicenseID").InnerText.ToString().Trim())
{
_licenseidList += " LicenseID='" + _section.SelectSingleNode("LicenseID").InnerText.ToString().Trim() + "' or ";
}
else
{
_licenseidList += " LicenseID='" + _section.SelectSingleNode("LicenseID").InnerText.ToString().Trim() + "') ";
}
}
string _sql = " Select SUM(CAST(DAYS as int)) As MonthDays,LicenseID from Car_Applied where AppliedStatue!='拒绝' and AppliedStatue!='取消' AND LicenseID!='NULL' AND " + _licenseidList + " and StartTime>='" + _startReq.AddDays(-_pre).ToString() + "' and StartTime<='" + _startReq.AddDays(14).ToString() + "' group by licenseid order by MonthDays ASC";
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
_desc = SQLProvider.GetData(_cmd);
}
if (!_desc.DocumentElement.HasChildNodes)
{
foreach (XmlNode _section in _list.DocumentElement.SelectNodes("SchemaTable"))
{
int _preWeek = _dayOfWeek[Convert.ToInt32(_startReq.DayOfWeek)] - 1;
int _next = 7 - _dayOfWeek[Convert.ToInt32(_startReq.DayOfWeek)];
string count = "";
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = "Select SUM(CAST(DAYS as int)) As WeekDays from Car_Applied where licenseid='" + _section.SelectSingleNode("LicenseID").InnerText.ToString().Trim() + "' and AppliedStatue!='拒绝' and AppliedStatue!='取消' and StartTime>='" + _startReq.AddDays(-_preWeek).ToString() + "' and StartTime<='" + _startReq.AddDays(_next).ToString() + "'";
count = SQLProvider.GetData(_cmd).DocumentElement.SelectSingleNode("SchemaTable/WeekDays").InnerText.ToString().Trim();
}
if (count == "" || count == null)
{
count = "0";
}
XmlElement _countEle = _list.CreateElement("WeekDays");
_countEle.InnerXml = "<![CDATA[" + count + "]]>";
XmlElement _month = _list.CreateElement("MonthDays");
_month.InnerXml = "<![CDATA[0]]>";
_section.AppendChild(_month);
_section.AppendChild(_countEle);
if (_minWeekDays > Convert.ToInt32(count))
{
_minWeekDays = Convert.ToInt32(count);
}
_minWeekDays = Convert.ToInt32(count);
}
_list.DocumentElement.SetAttribute("MinWeekDays", _minWeekDays.ToString());
return(_list);
}
//查询车辆周出行天数
foreach (XmlNode _section in _desc.DocumentElement.SelectNodes("SchemaTable"))
{
int _preWeek = _dayOfWeek[Convert.ToInt32(_startReq.DayOfWeek)] - 1;
int _next = 7 - _dayOfWeek[Convert.ToInt32(_startReq.DayOfWeek)];
string count = "";
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = "Select SUM(CAST(DAYS as int)) As WeekDays from Car_Applied where licenseid='" + _section.SelectSingleNode("LicenseID").InnerText.ToString().Trim() + "' and AppliedStatue!='拒绝' and AppliedStatue!='取消' and StartTime>='" + _startReq.AddDays(-_preWeek).ToString() + "' and StartTime<='" + _startReq.AddDays(_next).ToString() + "'";
count = SQLProvider.GetData(_cmd).DocumentElement.SelectSingleNode("SchemaTable/WeekDays").InnerText.ToString().Trim();
}
if (count == "" || count == null)
{
count = "0";
}
XmlElement _countEle = _desc.CreateElement("WeekDays");
_countEle.InnerXml = "<![CDATA[" + count + "]]>";
_section.AppendChild(_countEle);
if (_minWeekDays > Convert.ToInt32(count))
{
_minWeekDays = Convert.ToInt32(count);
}
}
_desc.DocumentElement.SetAttribute("MinWeekDays", _minWeekDays.ToString());
return(_desc);
}