public List <ParkGrant> QueryByParkingAndLotAndCarType(string parkingId, string lots, BaseCarType carType, string excludeGrantId)
{
List <ParkGrant> models = new List <ParkGrant>();
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
string[] strLots = lots.TrimEnd(',').Split(',');
for (int i = 0; i < strLots.Length; i++)
{
dbOperator.ClearParameters();
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT G.* FROM ParkGrant G INNER JOIN ParkCarType P");
strSql.AppendFormat(" ON G.CarTypeID=P.CarTypeID WHERE g.GID!='{0}' AND G.PKID='{1}'", excludeGrantId, parkingId);
strSql.AppendFormat(" AND ','+G.PKLot+',' like '%,{0},%' AND P.BaseTypeID={1}", strLots[i], (int)carType);
strSql.AppendFormat(" AND G.DataStatus!={0} AND P.DataStatus!={0}", (int)DataStatus.Delete);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
while (reader.Read())
{
ParkGrant model = DataReaderToModel <ParkGrant> .ToModel(reader);
if (models.FirstOrDefault(p => p.GID == model.GID) == null)
{
models.Add(model);
}
}
}
}
return(models);
}
}
/// <summary>
/// 获取所有在班信息
/// </summary>
/// <returns></returns>
public List <ParkChangeshiftrecord> GetUnChangeshiftrecordALL()
{
try
{
List <ParkChangeshiftrecord> parkchangeshiftrecordlist = new List <ParkChangeshiftrecord>();
StringBuilder strSql = new StringBuilder();
strSql.Append("select a.RecordID,a.StartWorkTime,BoxName,UserName from ParkChangeshiftrecord a left join ParkBox b on a.BoxID=b.BoxID ");
strSql.Append("left join SysUser c on a.UserID=c.RecordID ");
strSql.Append(" where a.DataStatus!=2 and EndWorkTime IS NULL");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
while (reader.Read())
{
parkchangeshiftrecordlist.Add(DataReaderToModel <ParkChangeshiftrecord> .ToModel(reader));
}
}
}
return(parkchangeshiftrecordlist);
}
catch (Exception e)
{
}
return(null);
}
public bool Update(ParkArea model)
{
using (DbOperator dbOperator = ConnectionManager.CreateConnection())
{
model.DataStatus = DataStatus.Normal;
model.LastUpdateTime = DateTime.Now;
model.HaveUpdate = SystemDefaultConfig.DataUpdateFlag;
StringBuilder strSql = new StringBuilder();
strSql.Append("update ParkArea set AreaName=@AreaName,MasterID=@MasterID,CarbitNum=@CarbitNum,NeedToll=@NeedToll,CameraWaitTime=@CameraWaitTime,TwoCameraWait=@TwoCameraWait");
strSql.Append(",Remark=@Remark,LastUpdateTime=@LastUpdateTime,HaveUpdate=@HaveUpdate where AreaID=@AreaID");
dbOperator.ClearParameters();
dbOperator.AddParameter("AreaID", model.AreaID);
dbOperator.AddParameter("AreaName", model.AreaName);
dbOperator.AddParameter("MasterID", model.MasterID);
dbOperator.AddParameter("CarbitNum", model.CarbitNum);
dbOperator.AddParameter("NeedToll", (int)model.NeedToll);
dbOperator.AddParameter("CameraWaitTime", (int)model.CameraWaitTime);
dbOperator.AddParameter("TwoCameraWait", (int)model.TwoCameraWait);
dbOperator.AddParameter("Remark", model.Remark);
dbOperator.AddParameter("LastUpdateTime", model.LastUpdateTime);
dbOperator.AddParameter("HaveUpdate", model.HaveUpdate);
return(dbOperator.ExecuteNonQuery(strSql.ToString()) > 0);
}
}
public List <ParkFeeRuleDetail> QueryFeeRuleDetailByFeeRuleId(string feeRuleId)
{
List <ParkFeeRuleDetail> details = new List <ParkFeeRuleDetail>();
if (feeRuleId.IsEmpty())
{
return(details);
}
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,RuleDetailID,RuleID,StartTime,EndTime,Supplement,LoopType,Limit");
strSql.Append(",FreeTime,FirstTime,FirstFee,Loop1PerTime,Loop1PerFee,Loop2Start,Loop2PerTime,Loop2PerFee,LastUpdateTime,HaveUpdate,DataStatus");
strSql.AppendFormat(" from ParkFeeRuleDetail where RuleID in('{0}') and DataStatus!=@DataStatus order by id", feeRuleId);
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
return(GetParkFeeRuleDetails(reader));
}
}
}
public bool ModifyChangeshiftrecord(ParkChangeshiftrecord model, out string errorMsg)
{
errorMsg = "";
try
{
using (DbOperator dbOperator = ConnectionManager.CreateConnection())
{
model.DataStatus = DataStatus.Normal;
model.LastUpdateTime = DateTime.Now;
model.HaveUpdate = SystemDefaultConfig.DataUpdateFlag;
StringBuilder strSql = new StringBuilder();
strSql.Append("update ParkChangeshiftrecord set BoxID=@BoxID,DataStatus=@DataStatus,EndWorkTime=@EndWorkTime,HaveUpdate=@HaveUpdate,LastUpdateTime=@LastUpdateTime,PKID=@PKID,StartWorkTime=@StartWorkTime,UserID=@UserID");
strSql.Append(" where RecordID=@RecordID");
dbOperator.ClearParameters();
dbOperator.AddParameter("BoxID", model.BoxID);
dbOperator.AddParameter("DataStatus", model.DataStatus);
dbOperator.AddParameter("EndWorkTime", model.EndWorkTime);
dbOperator.AddParameter("HaveUpdate", 1);
dbOperator.AddParameter("LastUpdateTime", model.LastUpdateTime);
dbOperator.AddParameter("PKID", model.PKID);
dbOperator.AddParameter("RecordID", model.RecordID);
dbOperator.AddParameter("StartWorkTime", model.StartWorkTime);
dbOperator.AddParameter("UserID", model.UserID);
return(dbOperator.ExecuteNonQuery(strSql.ToString()) > 0);
}
}
catch (Exception e)
{
errorMsg = e.Message;
}
return(false);
}
public bool Add(List <SysRoleAuthorize> models, DbOperator dbOperator)
{
foreach (var item in models)
{
item.DataStatus = DataStatus.Normal;
item.LastUpdateTime = DateTime.Now;
item.HaveUpdate = SystemDefaultConfig.DataUpdateFlag;
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into SysRoleAuthorize(RecordID,RoleID,ModuleID,ParentID,LastUpdateTime,HaveUpdate,DataStatus)");
strSql.Append(" values(@RecordID,@RoleID,@ModuleID,@ParentID,@LastUpdateTime,@HaveUpdate,@DataStatus)");
dbOperator.ClearParameters();
dbOperator.AddParameter("RecordID", item.RecordID);
dbOperator.AddParameter("RoleID", item.RoleID);
dbOperator.AddParameter("ModuleID", item.ModuleID);
dbOperator.AddParameter("ParentID", item.ParentID);
dbOperator.AddParameter("LastUpdateTime", item.LastUpdateTime);
dbOperator.AddParameter("HaveUpdate", item.HaveUpdate);
dbOperator.AddParameter("DataStatus", (int)item.DataStatus);
bool result = dbOperator.ExecuteNonQuery(strSql.ToString()) > 0;
if (!result)
{
return(false);
}
}
return(true);
}
public bool Add(BaseVillage model, DbOperator dbOperator)
{
model.DataStatus = DataStatus.Normal;
model.LastUpdateTime = DateTime.Now;
model.HaveUpdate = SystemDefaultConfig.DataUpdateFlag;
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into BaseVillage(VID,VNo,VName,CPID,LinkMan,Mobile,Address,Coordinate,ProxyNo,LastUpdateTime,HaveUpdate,DataStatus,IsBoxWatch)");
strSql.Append(" values(@VID,@VNo,@VName,@CPID,@LinkMan,@Mobile,@Address,@Coordinate,@ProxyNo,@LastUpdateTime,@HaveUpdate,@DataStatus,@IsBoxWatch)");
dbOperator.ClearParameters();
dbOperator.AddParameter("VID", model.VID);
dbOperator.AddParameter("VNo", model.VNo);
dbOperator.AddParameter("VName", model.VName);
dbOperator.AddParameter("CPID", model.CPID);
dbOperator.AddParameter("LinkMan", model.LinkMan);
dbOperator.AddParameter("Mobile", model.Mobile);
dbOperator.AddParameter("Address", model.Address);
dbOperator.AddParameter("Coordinate", model.Coordinate);
dbOperator.AddParameter("ProxyNo", model.ProxyNo);
dbOperator.AddParameter("LastUpdateTime", model.LastUpdateTime);
dbOperator.AddParameter("HaveUpdate", model.HaveUpdate);
dbOperator.AddParameter("DataStatus", (int)model.DataStatus);
dbOperator.AddParameter("IsBoxWatch", model.IsBoxWatch);
return(dbOperator.ExecuteNonQuery(strSql.ToString()) > 0);
}
public List <ParkOrder> GetOrderByTimeseriesIDByStatus(string timeseriesID, int status, out string ErrorMessage)
{
List <ParkOrder> ParkOrders = new List <ParkOrder>();
ErrorMessage = "";
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select * from ParkOrder where DataStatus!=@DataStatus and Status=@Status and TagID=@TagID and (OrderType=7 or OrderType=8) ");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("TagID", timeseriesID);
dbOperator.AddParameter("Status", status);
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
while (reader.Read())
{
ParkOrders.Add(DataReaderToModel <ParkOrder> .ToModel(reader));
}
}
}
}
catch (Exception e)
{
ErrorMessage = e.Message;
}
return(ParkOrders);
}
public bool ModifyOrderStatusAndAmount(string recordID, decimal amount, decimal payAmount, decimal unPayamount, int status, decimal Discountamount, string Carderateid, int payWay, out string ErrorMessage)
{
ErrorMessage = "";
try
{
using (DbOperator dbOperator = ConnectionManager.CreateConnection())
{
string strSql = "";
strSql = @"update ParkOrder set Carderateid=@Carderateid,Discountamount=@Discountamount,Amount=@Amount,Status=@Status,HaveUpdate=@HaveUpdate,PayAmount=@PayAmount,UnPayamount=@UnPayamount,LastUpdateTime=@LastUpdateTime,PayWay=@PayWay where RecordID=@RecordID";
dbOperator.ClearParameters();
dbOperator.AddParameter("RecordID", recordID);
dbOperator.AddParameter("Discountamount", Discountamount);
dbOperator.AddParameter("Carderateid", Carderateid);
dbOperator.AddParameter("Amount", amount);
dbOperator.AddParameter("Status", status);
dbOperator.AddParameter("PayAmount", payAmount);
dbOperator.AddParameter("UnPayamount", unPayamount);
dbOperator.AddParameter("PayWay", payWay);
dbOperator.AddParameter("HaveUpdate", 1);
dbOperator.AddParameter("LastUpdateTime", DateTime.Now);
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
return(dbOperator.ExecuteNonQuery(strSql.ToString()) > 0);
}
}
catch (Exception e)
{
ErrorMessage = e.Message;
}
return(false);
}
public ParkCarType QueryCarTypesByCarTypeName(string parkingId, string carTypeName)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,CarTypeID,CarTypeName,PKID,BaseTypeID,RepeatIn,RepeatOut");
strSql.Append(",AffirmIn,AffirmOut,InBeginTime,InEdnTime,MaxUseMoney,AllowLose,LpDistinguish,InOutEditCar");
strSql.Append(",InOutTime,CarNoLike,LastUpdateTime,HaveUpdate,IsAllowOnlIne,Amount,MaxMonth,MaxValue,DataStatus");
strSql.Append(",OverdueToTemp,LotOccupy,Deposit,MonthCardExpiredEnterDay,AffirmBegin,AffirmEnd,IsNeedCapturePaper,IsNeedAuthentication,IsDispatch,OnlineUnit,IsIgnoreHZ FROM ParkCarType");
strSql.Append(" WHERE PKID =@PKID AND DataStatus!=@DataStatus and CarTypeName=@CarTypeName");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("CarTypeName", carTypeName);
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
dbOperator.AddParameter("PKID", parkingId);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
if (reader.Read())
{
return(DataReaderToModel <ParkCarType> .ToModel(reader));
}
return(null);
}
}
}
public ParkOrder GetCashMoneyCountByPlateNumber(string parkingID, OrderType orderType, string plateNumber, DateTime startTime, DateTime endtime, out string ErrorMessage)
{
ErrorMessage = "";
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@" SELECT * from ParkOrder where id = (SELECT max(O.id) FROM ParkOrder O left outer join ParkIOrecord I on O.TagID = I.RecordID
WHERE O.status = 1 and O.CashTime >= @StartTime and O.CashTime <= @EndTime and O.PKID = @PKID and O.OrderType =@OrderType
and O.DataStatus != @DataStatus and I.PlateNumber =@PlateNumber) ");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("StartTime", startTime);
dbOperator.AddParameter("EndTime", endtime);
dbOperator.AddParameter("PKID", parkingID);
dbOperator.AddParameter("OrderType", (int)orderType);
dbOperator.AddParameter("PlateNumber", plateNumber);
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
if (reader.Read())
{
return(DataReaderToModel <ParkOrder> .ToModel(reader));
}
}
}
}
catch (Exception e)
{
ErrorMessage = e.Message;
}
return(null);
}
public List <ParkCarType> QueryParkCarTypeByParkingIds(List <string> parkingIds)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ID,CarTypeID,CarTypeName,PKID,BaseTypeID,RepeatIn,RepeatOut");
strSql.Append(",AffirmIn,AffirmOut,InBeginTime,InEdnTime,MaxUseMoney,AllowLose,LpDistinguish,InOutEditCar");
strSql.Append(",InOutTime,CarNoLike,LastUpdateTime,HaveUpdate,IsAllowOnlIne,Amount,MaxMonth,MaxValue,DataStatus");
strSql.Append(",OverdueToTemp,LotOccupy,Deposit,MonthCardExpiredEnterDay,AffirmBegin,AffirmEnd,IsNeedCapturePaper,IsNeedAuthentication,IsDispatch,OnlineUnit,IsIgnoreHZ FROM ParkCarType");
strSql.AppendFormat(" WHERE PKID IN('{0}') AND DataStatus!=@DataStatus", string.Join("','", parkingIds));
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
List <ParkCarType> lists = new List <ParkCarType>();
while (reader.Read())
{
lists.Add(DataReaderToModel <ParkCarType> .ToModel(reader));
}
return(lists);
}
}
}
public List <ParkGrant> GetCardgrantByParkingID(string parkingID, out string ErrorMessage)
{
List <ParkGrant> ParkGrants = new List <ParkGrant>();
ErrorMessage = "";
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from ParkGrant where PKID=@PKID and DataStatus!=@DataStatus");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("PKID", parkingID);
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
while (reader.Read())
{
ParkGrants.Add(DataReaderToModel <ParkGrant> .ToModel(reader));
}
}
}
}
catch (Exception e)
{
ErrorMessage = e.Message;
}
return(ParkGrants);
}
public List <ParkGrant> Query(List <string> parkingIds, string plateNumber, BaseCarType carType)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT P.* FROM PARKGRANT P INNER JOIN EmployeePlate E ON P.PlateID=E.PlateID");
strSql.Append(" inner join ParkCarType t on t.CarTypeID=P.CarTypeID");
strSql.AppendFormat(" WHERE P.PKID in('{0}') AND t.BaseTypeID=@BaseTypeID", string.Join("','", parkingIds));
strSql.Append(" AND E.PlateNo=@PlateNo AND E.DataStatus!=@DataStatus AND P.DataStatus!=@DataStatus AND t.DataStatus!=@DataStatus");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("BaseTypeID", (int)carType);
dbOperator.AddParameter("PlateNo", plateNumber);
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
List <ParkGrant> models = new List <ParkGrant>();
while (reader.Read())
{
models.Add(DataReaderToModel <ParkGrant> .ToModel(reader));
}
return(models);
}
}
}
/// <summary>
/// 审核异常订单
/// </summary>
/// <param name="recordID"></param>
/// <param name="PayAmount"></param>
/// <returns></returns>
public bool AuditingDiffOrder(string recordID, decimal Amount, decimal PayAmount, out string ErrorMessage)
{
ErrorMessage = "";
try
{
using (DbOperator dbOperator = ConnectionManager.CreateConnection())
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update ParkOrder set Amount=@Amount, PayAmount=@PayAmount,UnPayAmount=(@Amount-DiscountAmount-@PayAmount), HaveUpdate=@HaveUpdate,LastUpdateTime=@LastUpdateTime where RecordID=@RecordID");
dbOperator.ClearParameters();
dbOperator.AddParameter("RecordID", recordID);
dbOperator.AddParameter("Amount", Amount);
dbOperator.AddParameter("PayAmount", PayAmount);
dbOperator.AddParameter("HaveUpdate", 1);
dbOperator.AddParameter("LastUpdateTime", DateTime.Now);
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
return(dbOperator.ExecuteNonQuery(strSql.ToString()) > 0);
}
}
catch (Exception e)
{
ErrorMessage = e.Message;
}
return(false);
}
public ParkOrder GetTimeseriesOrderChareFeeCount(string userID, OrderType OrderType, DateTime dt, int releaseType, out string ErrorMessage)
{
ErrorMessage = "";
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT ISNULL(sum(O.Amount),0) as Amount,ISNULL(sum(O.PayAmount),0) as PayAmount ,ISNULL(sum(O.UnPayAmount),0) as UnPayAmount,ISNULL(sum(O.DiscountAmount),0) as DiscountAmount FROM ParkOrder O
left outer JOIN ParkTimeseries I on O.TagID=I.TimeseriesID
WHERE O.OrderTime>@OrderTime and O.UserID=@UserID and O.OrderType=@OrderType and O.DataStatus!=@DataStatus and I.ReleaseType=@ReleaseType and Status=1 ");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("UserID", userID);
dbOperator.AddParameter("OrderType", (int)OrderType);
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
dbOperator.AddParameter("OrderTime", dt);
dbOperator.AddParameter("ReleaseType", releaseType);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
if (reader.Read())
{
return(DataReaderToModel <ParkOrder> .ToModel(reader));
}
}
}
}
catch (Exception e)
{
ErrorMessage = e.Message;
}
return(null);
}
public List <ParkOrder> GetOrderByCarDerateID(DateTime startTime, string carDerateID, out string ErrorMessage)
{
List <ParkOrder> ParkOrders = new List <ParkOrder>();
ErrorMessage = "";
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"SELECT * from ParkOrder where DataStatus != @DataStatus and CarderateID =@CarderateID and Status=1 and OrderTime>=@StartTime");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("CarderateID", carDerateID);
dbOperator.AddParameter("StartTime", startTime);
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
while (reader.Read())
{
ParkOrders.Add(DataReaderToModel <ParkOrder> .ToModel(reader));
}
}
}
}
catch (Exception e)
{
ErrorMessage = e.Message;
}
return(ParkOrders);
}
public List <ParkOrder> GetOrderByStatus(DateTime startTime, DateTime endtime, out string ErrorMessage)
{
List <ParkOrder> ParkOrders = new List <ParkOrder>();
ErrorMessage = "";
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select a.*,b.ReleaseType from ParkOrder a left join ParkIORecord b on a.TagID=b.RecordID where (Status=1 or Status=2) and a.DataStatus!=@DataStatus and OrderTime>@StartTime and OrderTime<@EndTime");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("StartTime", startTime);
dbOperator.AddParameter("EndTime", endtime);
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
while (reader.Read())
{
ParkOrders.Add(DataReaderToModel <ParkOrder> .ToModel(reader));
}
}
}
}
catch (Exception e)
{
ErrorMessage = e.Message;
}
return(ParkOrders);
}
public List <BaseVillage> QueryVillageByUserId(string userId)
{
StringBuilder strSql = new StringBuilder();
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
List <BaseVillage> models = new List <BaseVillage>();
strSql.Append("select distinct v.* from SysUserScopeMapping um ");
strSql.Append(" inner join SysScopeAuthorize sa on um.ASID=sa.ASID");
strSql.Append(" inner join BaseVillage v on sa.TagID =v.VID");
strSql.Append(" where v.DataStatus!=@DataStatus and sa.DataStatus!=@DataStatus and um.DataStatus!=@DataStatus and sa.ASType=@ASType and um.UserRecordID=@UserRecordID");
dbOperator.ClearParameters();
dbOperator.AddParameter("ASType", (int)ASType.Village);
dbOperator.AddParameter("UserRecordID", userId);
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
while (reader.Read())
{
models.Add(DataReaderToModel <BaseVillage> .ToModel(reader));
}
return(models);
}
}
}
public decimal QueryMonthExpiredNotPayAmount(DateTime start, DateTime end, string parkingId, List <string> plateNumbers)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select sum(o.PayAmount) PayAmount from ParkIORecord p inner join ParkOrder o on p.RecordID=o.TagID");
strSql.Append(" where p.IsExit=1 and p.EnterType=1 and o.Status=4");
strSql.Append(" and o.OrderTime>=@StartDate and o.OrderTime<=@EndDate and p.DataStatus!=@DataStatus and o.DataStatus!=@DataStatus");
strSql.AppendFormat(" and p.PlateNumber in('{0}') and p.ParkingID=@ParkingID", string.Join("','", plateNumbers));
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("StartDate", start.ToString("yyyy-MM-dd HH:mm:ss"));
dbOperator.AddParameter("EndDate", end.ToString("yyyy-MM-dd HH:mm:ss"));
dbOperator.AddParameter("ParkingID", parkingId);
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
if (reader.Read())
{
return(reader.GetDecimalDefaultZero(0));
}
return(0);
}
}
}
public bool Update(BaseVillage model)
{
using (DbOperator dbOperator = ConnectionManager.CreateConnection())
{
model.DataStatus = DataStatus.Normal;
model.LastUpdateTime = DateTime.Now;
model.HaveUpdate = SystemDefaultConfig.DataUpdateFlag;
StringBuilder strSql = new StringBuilder();
strSql.Append("update BaseVillage set VNo=@VNo,VName=@VName,CPID=@CPID,LinkMan=@LinkMan,Mobile=@Mobile,Address=@Address,Coordinate=@Coordinate,LastUpdateTime=@LastUpdateTime,HaveUpdate=@HaveUpdate");
strSql.Append(" ,IsBoxWatch=@IsBoxWatch where VID=@VID");
dbOperator.ClearParameters();
dbOperator.AddParameter("VID", model.VID);
dbOperator.AddParameter("VNo", model.VNo);
dbOperator.AddParameter("VName", model.VName);
dbOperator.AddParameter("CPID", model.CPID);
dbOperator.AddParameter("LinkMan", model.LinkMan);
dbOperator.AddParameter("Mobile", model.Mobile);
dbOperator.AddParameter("Address", model.Address);
dbOperator.AddParameter("Coordinate", model.Coordinate);
dbOperator.AddParameter("LastUpdateTime", model.LastUpdateTime);
dbOperator.AddParameter("HaveUpdate", model.HaveUpdate);
dbOperator.AddParameter("IsBoxWatch", model.IsBoxWatch);
return(dbOperator.ExecuteNonQuery(strSql.ToString()) > 0);
}
}
/// <summary>
/// 获取订单
/// </summary>
/// <param name="parkingid">车场编号</param>
/// <param name="boxid">岗亭编号</param>
/// <param name="starttime">开始时间</param>
/// <param name="endtime">结束时间</param>
/// <returns></returns>
public List <ParkOrder> GetOrdersByGateID(string parkingid, string gateid, DateTime starttime, DateTime endtime)
{
List <ParkOrder> orderlist = new List <ParkOrder>();
string strSql = string.Format(@"select p.PayWay,p.Amount,p.UnPayAmount,p.PayAmount,p.DiscountAmount,p.OrderType OrderType
from parkorder p
left join parkiorecord i on i.recordid=p.tagid
where p.status=1 and i.exitgateid=@gateid and p.pkid=@parkingid and p.DataStatus!=2
and p.ordertime>=@starttime and p.ordertime<=@endtime");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("parkingid", parkingid);
dbOperator.AddParameter("gateid", gateid);
dbOperator.AddParameter("starttime", starttime);
dbOperator.AddParameter("endtime", endtime);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql))
{
while (reader.Read())
{
orderlist.Add(DataReaderToModel <ParkOrder> .ToModel(reader));
}
}
}
return(orderlist);
}
/// <summary>
/// 获取设备状态
/// </summary>
/// <param name="parkingid">车场编号</param>
/// <returns></returns>
public List <ParkDevice> QueryParkDeviceDetectionByParkingID(string villageid)
{
List <ParkDevice> parkdevicelist = new List <ParkDevice>();
string strSql = string.Format(@"select p.PKID,d.ID,case d.connectionstate when 0 then '断开' when 1 then '连接' when 3 then '未知' else '' end ConnectionStateName,
case device.devicetype when 0 then '大华_NVR' when 1 then '海康_NVR' when 2 then '火眼_NVR' when 3 then '华夏_NVR'
when 4 then '欧冠LED' when 5 then '车场控制器' when 6 then 'XM抓拍相机' else '' end DeviceTypeName,case device.porttype when 0 then 'TCP/IP' when 1 then '串口' else '' end PortTypeName,
device.BaudRate,d.DisconnectTime,device.SerialPort,device.IpAddr,device.IpPort,device.LedNum,g.GateName,p.PKName ParkingName,b.BoxName
from parkdevicedetection d left join parkdevice device on d.deviceid=device.deviceid
left join parkgate g on g.gateid=device.gateid
left join parkbox b on b.boxid=g.boxid
left join parkarea a on a.areaid=b.areaid
left join baseparkinfo p on p.pkid=a.pkid
where p.vid=@villageid and d.datastatus!=2 and device.datastatus!=2");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("villageid", villageid);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
while (reader.Read())
{
parkdevicelist.Add(DataReaderToModel <ParkDevice> .ToModel(reader));
}
}
}
return(parkdevicelist);
}
/// <summary>
/// 通过通道获取订单
/// </summary>
/// <param name="parkingid"></param>
/// <param name="boxid"></param>
/// <param name="starttime"></param>
/// <param name="endtime"></param>
/// <returns></returns>
public List <ParkOrder> GetOrdersByBoxID(string parkingid, string boxid, DateTime starttime, DateTime endtime)
{
List <ParkOrder> orderlist = new List <ParkOrder>();
string strSql = string.Format(@"select p.PayWay,p.Amount,p.UnPayAmount,p.PayAmount,p.OrderType OrderType
from parkorder p where p.status=1 and
tagid in (select recordid from parkiorecord where parkingid=@parkingid and DataStatus!=2 and
exitgateid in (select GateID from parkgate where IoState=2 and boxid=@boxid)) and p.DataStatus!=2
and p.ordertime>=@starttime and p.ordertime<=@endtime");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("parkingid", parkingid);
dbOperator.AddParameter("boxid", boxid);
dbOperator.AddParameter("starttime", starttime);
dbOperator.AddParameter("endtime", endtime);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql))
{
while (reader.Read())
{
orderlist.Add(DataReaderToModel <ParkOrder> .ToModel(reader));
}
}
}
return(orderlist);
}
public ParkChangeshiftrecord GetUnChangeshiftrecord(string boxID, out string ErrorMessage)
{
ErrorMessage = "";
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from ParkChangeshiftrecord where BoxID=@BoxID and DataStatus!=@DataStatus and EndWorkTime IS NULL");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("BoxID", boxID);
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
if (reader.Read())
{
return(DataReaderToModel <ParkChangeshiftrecord> .ToModel(reader));
}
}
}
}
catch (Exception e)
{
ErrorMessage = e.Message;
}
return(null);
}
public bool ModifyOrderTagIDAndOrderType(string recordID, string tagid, int orderType, out string ErrorMessage)
{
ErrorMessage = "";
try
{
using (DbOperator dbOperator = ConnectionManager.CreateConnection())
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update ParkOrder set TagID=@TagID,OrderType=@OrderType,HaveUpdate=@HaveUpdate,LastUpdateTime=@LastUpdateTime where RecordID=@RecordID");
dbOperator.ClearParameters();
dbOperator.AddParameter("RecordID", recordID);
dbOperator.AddParameter("OrderType", orderType);
dbOperator.AddParameter("TagID", tagid);
dbOperator.AddParameter("HaveUpdate", 1);
dbOperator.AddParameter("LastUpdateTime", DateTime.Now);
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
return(dbOperator.ExecuteNonQuery(strSql.ToString()) > 0);
}
}
catch (Exception e)
{
ErrorMessage = e.Message;
}
return(false);
}
public bool Add(ParkArea model)
{
using (DbOperator dbOperator = ConnectionManager.CreateConnection())
{
model.DataStatus = DataStatus.Normal;
model.LastUpdateTime = DateTime.Now;
model.HaveUpdate = SystemDefaultConfig.DataUpdateFlag;
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into ParkArea(AreaID,AreaName,MasterID,PKID,CarbitNum,NeedToll,CameraWaitTime,TwoCameraWait,Remark,LastUpdateTime,HaveUpdate,DataStatus)");
strSql.Append(" values(@AreaID,@AreaName,@MasterID,@PKID,@CarbitNum,@NeedToll,@CameraWaitTime,@TwoCameraWait,@Remark,@LastUpdateTime,@HaveUpdate,@DataStatus)");
dbOperator.ClearParameters();
dbOperator.AddParameter("AreaID", model.AreaID);
dbOperator.AddParameter("AreaName", model.AreaName);
dbOperator.AddParameter("MasterID", model.MasterID);
dbOperator.AddParameter("PKID", model.PKID);
dbOperator.AddParameter("CarbitNum", model.CarbitNum);
dbOperator.AddParameter("NeedToll", (int)model.NeedToll);
dbOperator.AddParameter("CameraWaitTime", (int)model.CameraWaitTime);
dbOperator.AddParameter("TwoCameraWait", (int)model.TwoCameraWait);
dbOperator.AddParameter("Remark", model.Remark);
dbOperator.AddParameter("LastUpdateTime", model.LastUpdateTime);
dbOperator.AddParameter("HaveUpdate", model.HaveUpdate);
dbOperator.AddParameter("DataStatus", (int)model.DataStatus);
return(dbOperator.ExecuteNonQuery(strSql.ToString()) > 0);
}
}
/// <summary>
/// 获取当班差异订单
/// </summary>
/// <param name="startTime"></param>
/// <param name="endtime"></param>
/// <param name="userid"></param>
/// <param name="ErrorMessage"></param>
/// <returns></returns>
public List <ParkOrder> GetDifferenceOrder(DateTime startTime, DateTime endtime, string userid, out string ErrorMessage)
{
List <ParkOrder> ParkOrders = new List <ParkOrder>();
ErrorMessage = "";
try
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select * from ParkOrder where UserID=@UserID and amount!=payamount and DataStatus!=@DataStatus and (OrderType=1 or OrderType=6 or OrderType=7 or OrderType=8) and OrderTime>@StartTime and OrderTime<@EndTime");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("UserID", userid);
dbOperator.AddParameter("StartTime", startTime);
dbOperator.AddParameter("EndTime", endtime);
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
while (reader.Read())
{
ParkOrders.Add(DataReaderToModel <ParkOrder> .ToModel(reader));
}
}
}
}
catch (Exception e)
{
ErrorMessage = e.Message;
}
return(ParkOrders);
}
public bool Update(ParkCarModel model)
{
model.LastUpdateTime = DateTime.Now;
model.HaveUpdate = SystemDefaultConfig.DataUpdateFlag;
StringBuilder strSql = new StringBuilder();
strSql.Append("update ParkCarModel set CarModelName=@CarModelName,IsDefault=@IsDefault,LastUpdateTime=@LastUpdateTime,HaveUpdate=@HaveUpdate,MaxUseMoney=@MaxUseMoney,IsNaturalDay=@IsNaturalDay,PlateColor=@PlateColor ");
strSql.Append(",DayMaxMoney=@DayMaxMoney,NightMaxMoney=@NightMaxMoney,DayStartTime=@DayStartTime,DayEndTime=@DayEndTime,NightStartTime=@NightStartTime,NightEndTime=@NightEndTime where CarModelID=@CarModelID");
using (DbOperator dbOperator = ConnectionManager.CreateConnection())
{
UpdateDefault(model, dbOperator);
dbOperator.ClearParameters();
dbOperator.AddParameter("CarModelName", model.CarModelName);
dbOperator.AddParameter("IsDefault", (int)model.IsDefault);
dbOperator.AddParameter("CarModelID", model.CarModelID);
dbOperator.AddParameter("LastUpdateTime", model.LastUpdateTime);
dbOperator.AddParameter("HaveUpdate", model.HaveUpdate);
dbOperator.AddParameter("MaxUseMoney", model.MaxUseMoney);
dbOperator.AddParameter("IsNaturalDay", model.IsNaturalDay);
dbOperator.AddParameter("PlateColor", model.PlateColor);
dbOperator.AddParameter("DayMaxMoney", model.DayMaxMoney);
dbOperator.AddParameter("NightMaxMoney", model.NightMaxMoney);
dbOperator.AddParameter("DayStartTime", model.DayStartTime);
dbOperator.AddParameter("DayEndTime", model.DayEndTime);
dbOperator.AddParameter("NightStartTime", model.NightStartTime);
dbOperator.AddParameter("NightEndTime", model.NightEndTime);
return(dbOperator.ExecuteNonQuery(strSql.ToString()) > 0);
}
}
public bool Update(ParkGrant model, DbOperator dbOperator)
{
model.DataStatus = DataStatus.Normal;
model.LastUpdateTime = DateTime.Now;
model.HaveUpdate = SystemDefaultConfig.DataUpdateFlag;
StringBuilder strSql = new StringBuilder();
strSql.Append("update ParkGrant set CardID=@CardID,PKID=@PKID,BeginDate=@BeginDate,EndDate=@EndDate,CarTypeID=@CarTypeID,CarModelID=@CarModelID,PKLot=@PKLot,");
strSql.Append(" PlateID=@PlateID,ComdState=@ComdState,AreaIDS=@AreaIDS,GateID=@GateID,State=@State,LastUpdateTime=@LastUpdateTime,HaveUpdate=@HaveUpdate");
strSql.Append(" where GID=@GID");
dbOperator.ClearParameters();
dbOperator.AddParameter("GID", model.GID);
dbOperator.AddParameter("CardID", model.CardID);
dbOperator.AddParameter("PKID", model.PKID);
dbOperator.AddParameter("BeginDate", model.BeginDate);
dbOperator.AddParameter("EndDate", model.EndDate);
dbOperator.AddParameter("CarTypeID", model.CarTypeID);
dbOperator.AddParameter("CarModelID", model.CarModelID);
dbOperator.AddParameter("PKLot", model.PKLot);
dbOperator.AddParameter("PlateID", model.PlateID);
dbOperator.AddParameter("ComdState", (int)model.ComdState);
dbOperator.AddParameter("AreaIDS", model.AreaIDS);
dbOperator.AddParameter("GateID", model.GateID);
dbOperator.AddParameter("State", (int)model.State);
dbOperator.AddParameter("LastUpdateTime", model.LastUpdateTime);
dbOperator.AddParameter("HaveUpdate", model.HaveUpdate);
return(dbOperator.ExecuteNonQuery(strSql.ToString()) > 0);
}
