/// <summary>
/// 插入车辆状态更改记录
/// </summary>
/// <param name="_parGuid"></param>
/// <param name="_parLicenseID"></param>
/// <param name="_parState"></param>
/// <returns></returns>
public XmlDocument insertChangeState(string _parLicenseID, string _parState)
{
result = XmlProvider.Document("configdata");
string _sql = readXml("insertChangeState");
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter _guid = new SqlParameter("Guid", SqlDbType.NVarChar, 50);
_guid.Value = Provider.GUID;
_cmd.Parameters.Add(_guid);
SqlParameter _licenseid = new SqlParameter("LicenseID", SqlDbType.NVarChar, 50);
_licenseid.Value = _parLicenseID;
_cmd.Parameters.Add(_licenseid);
SqlParameter _state = new SqlParameter("NewCarState", SqlDbType.NVarChar, 50);
_state.Value = _parState;
_cmd.Parameters.Add(_state);
SqlParameter _datetime = new SqlParameter("ChangeDatetime", SqlDbType.NVarChar, 50);
_datetime.Value = DateTime.Now;
_cmd.Parameters.Add(_datetime);
result = SQLProvider.Transcation(_cmd);
}
return(this.result);
}
//司机与车辆绑定
public XmlDocument Bound(string _licenseID, string _account)
{
result = XmlProvider.Document("data");
string _sql = readXml("Bound");
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);
SqlParameter account = new SqlParameter("DriverAccount", SqlDbType.NVarChar, 50);
account.Value = _account;
_cmd.Parameters.Add(account);
XmlDocument _api = XmlProvider.Document("api");
_api.Load("http://api.qgj.cn/webapi/profile.asmx/GetUser?User="******"ITEM/name").InnerText;
SqlParameter name = new SqlParameter("DriverName", SqlDbType.NVarChar, 50);
name.Value = _name;
_cmd.Parameters.Add(name);
result = SQLProvider.Transcation(_cmd);
}
return(result);
}
/// <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="_reqFixID"></param>
/// <returns></returns>
public XmlDocument endFixedStatue(string _reqFixID)
{
result = XmlProvider.Document("configdata");
string _sql = readXml("endFixedStatue");
try
{
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter _guid = new SqlParameter("FixID", SqlDbType.NVarChar, 50);
_guid.Value = _reqFixID;
_cmd.Parameters.Add(_guid);
SqlParameter _endTime = new SqlParameter("EndTime", SqlDbType.NVarChar, 50);
_endTime.Value = DateTime.Now.ToString();
_cmd.Parameters.Add(_endTime);
result = SQLProvider.Transcation(_cmd);
}
}
catch (Exception _exc)
{
Provider.LogErr(result, _exc);
}
return(this.result);
}
//拒绝预约
public XmlDocument upStauteRefuse(string _guid, string _statue, string _reson)
{
result = XmlProvider.Document("sqldata");
string _sql = readXml("upStauteRefuse");
//读取数据
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);
SqlParameter _refuseReson = new SqlParameter("RefuseReson", SqlDbType.NVarChar, 200);
_refuseReson.Value = _reson;
_cmd.Parameters.Add(_refuseReson);
SqlParameter _statues = new SqlParameter("AppliedStatue", SqlDbType.NVarChar, 50);
_statues.Value = _statue;
_cmd.Parameters.Add(_statues);
result = SQLProvider.Transcation(_cmd);
}
return(result);
}
private XmlDocument TokenExecute(string _txt, string _token)
{
XmlDocument _result = XmlProvider.Document("sqldata");
try
{
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _txt;
SqlParameter _id = new SqlParameter("id", SqlDbType.NVarChar, 50);
_id.Value = _token;
_cmd.Parameters.Add(_id);
SqlParameter _ip = new SqlParameter("ip", SqlDbType.NVarChar, 50);
_ip.Value = this.ip;
_cmd.Parameters.Add(_ip);
SqlParameter _client = new SqlParameter("client", SqlDbType.NVarChar, 50);
_client.Value = this.client;
_cmd.Parameters.Add(_client);
SqlParameter _expired = new SqlParameter("expired", SqlDbType.DateTime);
_expired.Value = DateTime.Now.AddHours(1);
_cmd.Parameters.Add(_expired);
_result = SQLProvider.Transcation(_cmd);
}
}
catch (Exception _exc) { Provider.LogErr(_result, _exc); }
return(_result);
}
//延期返回
public XmlDocument delayApply(string _guid, int _delayDays, DateTime _delyEndTime)
{
result = XmlProvider.Document("sqldata");
string _sql = readXml("delayApply");
//读取数据
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);
SqlParameter _endTime = new SqlParameter("EndTime", SqlDbType.NVarChar, 50);
_endTime.Value = _delyEndTime;
_cmd.Parameters.Add(_endTime);
SqlParameter _days = new SqlParameter("Days", SqlDbType.Int);
_days.Value = _delayDays;
_cmd.Parameters.Add(_days);
result = SQLProvider.Transcation(_cmd);
}
return(result);
}
//根据车牌查找所有对应的待出车记录
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 XmlDocument motifyDriver(string _driverID, string _driverName, string _driverAccount, string _carTelephone, string _licenseID)
{
result = XmlProvider.Document("configdata");
string _sql = readXml("motifyDriver");
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);
SqlParameter driverName = new SqlParameter("DriverName", SqlDbType.NVarChar, 50);
driverName.Value = _driverName;
_cmd.Parameters.Add(driverName);
SqlParameter driverAccount = new SqlParameter("DriverAccount", SqlDbType.NVarChar, 50);
driverAccount.Value = _driverAccount;
_cmd.Parameters.Add(driverAccount);
SqlParameter carTelephone = new SqlParameter("CarTelephone", SqlDbType.NVarChar, 50);
carTelephone.Value = _carTelephone;
_cmd.Parameters.Add(carTelephone);
SqlParameter licenseID = new SqlParameter("LicenseID", SqlDbType.NVarChar, 50);
licenseID.Value = _licenseID;
_cmd.Parameters.Add(licenseID);
result = SQLProvider.Transcation(_cmd);
}
return(result);
}
/// <summary>
/// 添加一个临时调度员
/// </summary>
/// <param name="_permitAccount">临时调度员账号</param>
/// <returns></returns>
public XmlDocument addLsDispatcher(string _permitAccount)
{
result = XmlProvider.Document("configdata");
string _sql = readXml("addLsDispather");//Dispather.xml中的添加临时调度员的标签的type为addLsDispather
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);
SqlParameter resourcePermit = new SqlParameter("ResourcePermit", SqlDbType.NVarChar, 50);
resourcePermit.Value = "LSADMIN";
_cmd.Parameters.Add(resourcePermit);
SqlParameter resourcePermitLev = new SqlParameter("ResourcePermitLev", SqlDbType.Int);
resourcePermitLev.Value = Convert.ToInt32(1);
_cmd.Parameters.Add(resourcePermitLev);
//临时调度员账号的账号名称
SqlParameter permitAccount = new SqlParameter("PermitAccount", SqlDbType.NVarChar, 50);
permitAccount.Value = _permitAccount;
_cmd.Parameters.Add(permitAccount);
result = SQLProvider.Transcation(_cmd);
}
return(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 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("");
}
}
static void Main(string[] args)
{
SQLConfig.SetConnectionString(ConfigurationManager.ConnectionStrings["sandbox"].ConnectionString);
Program program = new Program();
program.start();
}
/// <summary>
/// Constructor of MongoConnecter
/// </summary>
/// <param name="configuration">Contain all of config for connecter</param>
private SQLConnecter(IConfigurationSection configuration)
{
Config = new SQLConfig();
configuration.Bind(Config);
Option = new DbContextOptionsBuilder <SQLData>();
Option.UseSqlServer(Config.Connect);
SqlData = new SQLData(Option.Options);
}
//获取所有车辆
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());
}
//获取有出车天数的车牌
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 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);
}
/// <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 ResponseDto <SQLConfig> Save([FromBody] SQLConfig request)
{
ResponseDto <SQLConfig> response = new ResponseDto <SQLConfig>();
var _entity = _appSystemServices.GetEntitys <SQLConfig>();
if (string.IsNullOrEmpty(request.Id.ToStringExtension()) || request.Id.ToInt32() == 0)
{
_appSystemServices.Create <SQLConfig>(request);
}
else
{
_appSystemServices.Modify <SQLConfig>(request);
}
return(response);
}
public ResponseDto <Boolean> Remove([FromBody] SQLConfig request)
{
ResponseDto <Boolean> response = new ResponseDto <Boolean>();
if (string.IsNullOrEmpty(request.Id.ToStringExtension()))
{
response.Message = "Key 不能为空";
response.Success = false;
return(response);
}
var _entity = _appSystemServices.GetEntitys <SQLConfig>();
response.Data = _entity.Where(x => x.Id == request.Id).ToDelete().ExecuteAffrows() > 0;
return(response);
}
//获取需要调度员控制的车辆数量
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()));
}
//根据账号查找司机信息
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);
}
//获取司机详情
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);
}
//解除司机与车辆绑定
public XmlDocument unBound(string _licenseID)
{
result = XmlProvider.Document("data");
string _sql = readXml("unBound");
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.Transcation(_cmd);
}
return(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);
}
/// <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);
}
/// <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 upControlNum(int _num)
{
result = XmlProvider.Document("configdata");
string _sql = readXml("upControlCarNum");
using (SqlCommand _cmd = new SqlCommand())
{
_cmd.Connection = SQLConfig.Connection(this.conn);
_cmd.CommandText = _sql;
SqlParameter SetValue = new SqlParameter("SetValue", SqlDbType.NVarChar, 50);
SetValue.Value = _num;
_cmd.Parameters.Add(SetValue);
result = SQLProvider.Transcation(_cmd);
}
return(result);
}
