public static List <Driver> GetDriverInfo(Driver dri, int pageSize, int pageIndex, out int recordCount) { recordCount = 0; Paging page = new Paging(); page.TableName = @"[Driver] d left join [Contact] c on d.DriverID=c.[FK_DriverID] left join [Truck] tt on c.FK_TruckID=tt.TruckID"; page.PrimaryKey = "DriverID"; page.Fields = @"[DriverID] ,[Name] ,[Sex] ,case [Sex] when 1 then '女' else '男' end as SexName ,[Birth] ,[Phone] ,d.[State] ,[IDCard] ,[Number] ,d.[FK_TeamID] ,[TruckID] ,d.[CheckInTime]" ; StringBuilder where = new StringBuilder(@"d.IsDelete=0"); List <string> wherelist = new List <string>(); if (!string.IsNullOrWhiteSpace(dri.Name)) { wherelist.Add($"Name like '%{dri.Name}%'"); } if (dri.Sex != -1) { wherelist.Add($"Sex={dri.Sex}"); } if (!string.IsNullOrWhiteSpace(dri.BeginBirth)) { wherelist.Add($"Birth > '{dri.BeginBirth}'"); } if (!string.IsNullOrWhiteSpace(dri.EndBirth)) { wherelist.Add($"Birth < '{dri.EndBirth}'"); } if (!string.IsNullOrWhiteSpace(dri.Phone)) { wherelist.Add($"Phone like '{dri.Phone}'"); } if (dri.FK_TeamID != -1) { wherelist.Add($"d.FK_TeamID='{dri.FK_TeamID}'"); } if (!string.IsNullOrWhiteSpace(dri.BeginInTime)) { wherelist.Add($"Birth > '{dri.BeginInTime}'"); } if (!string.IsNullOrWhiteSpace(dri.EndInTime)) { wherelist.Add($"Birth < '{dri.EndInTime}'"); } if (!string.IsNullOrWhiteSpace(dri.IDCard)) { wherelist.Add($"Phone like '%{dri.IDCard}%'"); } if (dri.State != 0) { if (dri.State == 2) { wherelist.Add($"d.DriverID NOT IN (SELECT c2.FK_DriverID FROM Contact AS c2)"); } else { wherelist.Add($"d.DriverID IN (SELECT c2.FK_DriverID FROM Contact AS c2)"); } } if (wherelist.Count() > 0) { where.Append($" and {string.Join(" and ", wherelist.ToArray())}"); } page.Condition = where.ToString(); page.PageSize = pageSize; page.PageIndex = pageIndex; DataTable dt = PublicPaging.ProcGetPageData(page, out recordCount); List <Driver> driverlist = new List <Driver>(); if (dt.Rows.Count > 0) { foreach (DataRow item in dt.Rows) { Driver diver = new Driver(); diver.DriverID = Convert.ToInt32(item["DriverID"]); diver.Name = item["Name"].ToString(); diver.Sex = Convert.ToInt32(item["Sex"]); diver.SexName = item["SexName"].ToString(); diver.Phone = item["Phone"].ToString(); diver.State = Convert.ToByte(item["State"]); diver.IDCard = item["IDCard"].ToString(); if (string.IsNullOrWhiteSpace(item["Number"].ToString())) { diver.Number = "未绑定"; } else { diver.Number = item["Number"].ToString(); } diver.FK_TeamID = Convert.ToInt32(item["FK_TeamID"]); diver.CheckInTime = Convert.ToDateTime(item["CheckInTime"]); diver.Birth = Convert.ToDateTime(item["Birth"]); if (string.IsNullOrWhiteSpace(item["TruckID"].ToString())) { diver.TruckID = 0; } else { diver.TruckID = Convert.ToInt32(item["TruckID"]); } driverlist.Add(diver); } } return(driverlist); }
public static List <Truck> GetTruck(Truck tk, int pageSize, int pageIndex, out int recordCount) { Paging page = new Paging(); page.TableName = "[Truck] tk inner join TruckTeam tm on tk.FK_TeamID = tm.TeamID"; page.PrimaryKey = "TruckID"; page.Fields = @"[TruckID] ,[Number] ,[BuyDate] ,[Type] ,[Length] ,[Tonnage] ,[TeamName] ,[State] ,tk.[CheckInTime] ,tk.[AlterTime] ,tk.[Remark] ,case [State] when 1 then " + "'承运中'" + " else " + "'空闲中'" + " end as [StateName]"; List <string> wherelist = new List <string>(); wherelist.Add("tk.IsDelete=0"); StringBuilder where = new StringBuilder(); if (tk.FK_TeamID != -1) { wherelist.Add($" FK_TeamID={tk.FK_TeamID}"); } if (!string.IsNullOrWhiteSpace(tk.Number)) { wherelist.Add($" Number like '%{tk.Number}%'"); } if (tk.Type != "-1") { wherelist.Add($" Type='{tk.Type}'"); } if (tk.State != -1) { wherelist.Add($" State={tk.State}"); } if (wherelist.Count > 0) { where.Append(string.Join(" and ", wherelist)); } page.Condition = where.ToString(); page.Sort = "TruckID"; page.PageIndex = pageIndex; page.PageSize = pageSize; DataTable dt = PublicPaging.ProcGetPageData(page, out recordCount); List <Truck> trucklist = new List <Truck>(); if (dt.Rows.Count > 0) { foreach (DataRow item in dt.Rows) { Truck truck = new Truck(); truck.TruckID = Convert.ToInt32(item["TruckID"]); truck.Number = item["Number"].ToString(); truck.BuyDate = Convert.ToDateTime(item["BuyDate"]); truck.Type = item["Type"].ToString(); truck.Length = item["Length"].ToString(); truck.Tonnage = Convert.ToInt32(item["Tonnage"]); truck.TeamName = item["TeamName"].ToString(); truck.State = Convert.ToInt32(item["State"]); truck.StateName = item["StateName"].ToString(); truck.CheckInTime = Convert.ToDateTime(item["CheckInTime"]); truck.AlterTime = Convert.ToDateTime(item["AlterTime"]); truck.Remark = item["Remark"].ToString(); trucklist.Add(truck); } } return(trucklist); }
public static List <Carriers> GetCarriersH(Carriers carr, int pageSize, int pageIndex, out int recordCount) { recordCount = 0; Paging page = new Paging(); page.TableName = "[Carriers]"; page.PrimaryKey = "[CarriersID]"; page.Fields = @"[CarriersID] ,[SendCompany] ,[SendLinkman] ,[ReceiveCompany] ,[ReceiveLinkman] ,[LeaverDate] ,[TotalCost] ,[FK_UserID]"; List <string> wherelist = new List <string>(); StringBuilder where = new StringBuilder(); where.Append("IsDelete=0 and FinishedState=3"); if (carr.CarriersID != 0) { wherelist.Add($" CarriersID='{carr.CarriersID}'"); } if (!string.IsNullOrWhiteSpace(carr.LeaverDateS)) { wherelist.Add($" LeaverDate>'{carr.LeaverDateS}'"); } if (!string.IsNullOrWhiteSpace(carr.LeaverDateE)) { wherelist.Add($" LeaverDate<'{carr.LeaverDateE}'"); } if (!string.IsNullOrWhiteSpace(carr.SendLinkman)) { wherelist.Add($" SendLinkman like '%{carr.SendLinkman}%'"); } if (!string.IsNullOrWhiteSpace(carr.ReceiveLinkman)) { wherelist.Add($" ReceiveLinkman like '%{carr.ReceiveLinkman}%'"); } if (!string.IsNullOrWhiteSpace(carr.UserName)) { wherelist.Add($" FK_UserID in (select UserID from [User] where UserName like '%{carr.UserName}%')"); } if (!string.IsNullOrWhiteSpace(carr.ReceiveDateS)) { wherelist.Add($" ReceiveDate>'{carr.ReceiveDateS}'"); } if (!string.IsNullOrWhiteSpace(carr.ReceiveDateE)) { wherelist.Add($" ReceiveDate<'{carr.ReceiveDateE}'"); } if (wherelist.Count > 0) { where.Append(" and " + string.Join($" and ", wherelist)); } page.Condition = where.ToString(); page.PageSize = pageSize; page.PageIndex = pageIndex; DataTable dt = PublicPaging.ProcGetPageData(page, out recordCount); List <Carriers> carrlist = new List <Carriers>(); if (dt.Rows.Count > 0) { foreach (DataRow item in dt.Rows) { Carriers ca = new Carriers(); ca.CarriersID = Convert.ToInt32(item["CarriersID"]); ca.SendCompany = item["SendCompany"].ToString(); ca.SendLinkman = item["SendLinkman"].ToString(); ca.ReceiveCompany = item["ReceiveCompany"].ToString(); ca.ReceiveLinkman = item["ReceiveLinkman"].ToString(); ca.LeaverDate = Convert.ToDateTime(item["LeaverDate"]); ca.TotalCost = Convert.ToDouble(item["TotalCost"]); ca.FK_UserID = Convert.ToInt32(item["FK_UserID"]); carrlist.Add(ca); } } return(carrlist); }
public static List <Carriers> GetCostByUserId(int userId, Carriers carr, int pageSize, int pageIndex, out int recordCount) { recordCount = 0; Paging page = new Paging(); page.TableName = "[Carriers] a inner join [Scheduling] b on a.CarriersID=b.FK_CarriersID"; page.PrimaryKey = "[SchedulingID]"; page.Fields = @"[SchedulingID] ,[CarriersID] ,[SendCompany] ,[ReceiveDate] ,[FinishedState] ,a.[TotalCost] as [TotalCostC] ,[OilCost] ,[Toll] ,[Fine] ,b.[OtherCost] ,b.[TotalCost] as [TotalCostS] ,b.[FK_UserID] ,b.[IsDelete] ,b.[AlterTime]"; List <string> wherelist = new List <string>(); StringBuilder where = new StringBuilder(); where.Append("b.IsDelete=0 and FinishedState>1"); if (carr.FinishedState != 0) { if (carr.FinishedState == 1) { wherelist.Add($" FinishedState=2"); } else { wherelist.Add($" FinishedState=3"); } } if (!string.IsNullOrWhiteSpace(carr.ReceiveDateS)) { wherelist.Add($" ReceiveDate>'{carr.ReceiveDateS}'"); } if (!string.IsNullOrWhiteSpace(carr.ReceiveDateE)) { wherelist.Add($" ReceiveDate<'{carr.ReceiveDateE}'"); } if (wherelist.Count > 0) { where.Append(" and " + string.Join($" and ", wherelist)); } page.Condition = where.ToString(); page.PageSize = pageSize; page.PageIndex = pageIndex; DataTable dt = PublicPaging.ProcGetPageData(page, out recordCount); List <Carriers> carrlist = new List <Carriers>(); if (dt.Rows.Count > 0) { foreach (DataRow item in dt.Rows) { Carriers ca = new Carriers(); ca.CarriersID = Convert.ToInt32(item["CarriersID"]); ca.SchedulingID = Convert.ToInt32(item["SchedulingID"]); ca.SendCompany = item["SendCompany"].ToString(); ca.ReceiveDate = Convert.ToDateTime(item["ReceiveDate"]); ca.FinishedState = Convert.ToByte(item["FinishedState"]); ca.TotalCost = Convert.ToDouble(item["TotalCostC"]); ca.OilCost = Convert.ToDouble(item["OilCost"]); ca.Toll = Convert.ToDouble(item["Toll"]); ca.Fine = Convert.ToDouble(item["Fine"]); ca.OtherCost = Convert.ToDouble(item["OtherCost"]); ca.TotalCostS = Convert.ToDouble(item["TotalCostS"]); ca.FK_UserID = Convert.ToInt32(item["FK_UserID"]); ca.IsDelete = Convert.ToByte(item["IsDelete"]); ca.AlterTime = Convert.ToDateTime(item["AlterTime"]); carrlist.Add(ca); } } return(carrlist); }
public static List <SysLog> GetAllSysLog(SysLog sys, int pageSize, int pageIndex, out int recordCount) { recordCount = 0; Paging page = new Paging(); page.TableName = "dbo.SysLog a INNER JOIN dbo.LogDic b ON a.FK_TypeID=b.TypeID INNER JOIN dbo.[User] c ON a.FK_UserID=c.UserID"; page.PrimaryKey = "LogID"; page.Fields = @" [LogID] ,[Behavior] ,[Account] ,b.TypeName ,[Parameters] ,[ProcName] ,[IP] ,a.[CheckInTime] ,[IsException]" ; List <string> wherelist = new List <string>(); StringBuilder where = new StringBuilder(); if (sys.FK_TypeID != -1) { wherelist.Add($"FK_TypeID={sys.FK_TypeID}"); } if (sys.IsException != 2) { wherelist.Add($"IsException={sys.IsException}"); } if (!string.IsNullOrWhiteSpace(sys.Account)) { wherelist.Add($" Account LIKE '%{sys.Account}%'"); } if (!string.IsNullOrWhiteSpace(sys.CheckInTimeS)) { wherelist.Add($"a.CheckInTime>'{sys.CheckInTimeS}'"); } if (!string.IsNullOrWhiteSpace(sys.CheckInTimeE)) { wherelist.Add($"a.CheckInTime<'{sys.CheckInTimeE}'"); } if (!string.IsNullOrWhiteSpace(sys.ProcName)) { wherelist.Add($"ProcName like '%{sys.ProcName}%'"); } if (wherelist.Count > 0) { where.Append(string.Join($" and ", wherelist)); } page.Condition = where.ToString(); page.PageIndex = pageIndex; page.PageSize = pageSize; DataTable dt = PublicPaging.ProcGetPageData(page, out recordCount); List <SysLog> syslist = new List <SysLog>(); if (dt.Rows.Count > 0) { foreach (DataRow item in dt.Rows) { SysLog sy = new SysLog(); sy.LogID = Convert.ToInt32(item["LogID"]); sy.Behavior = item["Behavior"].ToString(); sy.TypeName = item["TypeName"].ToString(); sy.Account = item["Account"].ToString(); sy.Parameters = item["Parameters"].ToString(); sy.ProcName = item["ProcName"].ToString(); sy.IP = Convert.ToString(item["IP"]); sy.CheckInTime = Convert.ToDateTime(item["CheckInTime"]); sy.IsException = Convert.ToByte(item["IsException"]); syslist.Add(sy); } } return(syslist); }
public static List <Users> BindgvUser(Users u, int pageSize, int pageIndex, out int recordCount) { recordCount = 0; Paging page = new Paging(); page.TableName = "[user] u inner join Role r on u.FK_RoleID = r.RoleID"; page.PrimaryKey = "UserID"; page.Fields = @"UserID,UserName,password,case Sex when 1 then '女' else '男' end as Sex,Account,phone,Email,RoleName,AlterTime, checkInTime,case IsDelete when 1 then '是' else '否' end as IsDelete"; StringBuilder where = new StringBuilder("u.IsDelete=0"); List <string> whereList = new List <string>(); if (!string.IsNullOrWhiteSpace(u.UserName)) { whereList.Add($" UserName like '{u.UserName}'"); } if (u.Sex != "2") { whereList.Add($" Sex={u.Sex}"); } if (!string.IsNullOrWhiteSpace(u.Account)) { whereList.Add($" Account like '{u.Account}'"); } if (!string.IsNullOrWhiteSpace(u.Phone)) { whereList.Add($" Phone like '{u.Phone}'"); } if (u.IsDelete != "2") { whereList.Add($" IsDelete={u.IsDelete}"); } if (!string.IsNullOrWhiteSpace(u.Email)) { whereList.Add($" Email like '{u.Email}'"); } if (u.FK_RoleID != 0) { whereList.Add($"u.FK_RoleID={u.FK_RoleID}"); } if (!string.IsNullOrWhiteSpace(u.BeginInTime)) { DateTime Dt = Convert.ToDateTime(u.BeginInTime); whereList.Add($" checkInTime > '{Dt}'"); } if (!string.IsNullOrWhiteSpace(u.EndInTime)) { DateTime Dt = Convert.ToDateTime(u.EndInTime); whereList.Add($" checkInTime < '{Dt}'"); } if (!string.IsNullOrWhiteSpace(u.UpdateBeginTime)) { DateTime Dt = Convert.ToDateTime(u.UpdateBeginTime); whereList.Add($" AlterTime > '{Dt}'"); } if (!string.IsNullOrWhiteSpace(u.UpdateEndTime)) { DateTime Dt = Convert.ToDateTime(u.UpdateEndTime); whereList.Add($" AlterTime < {Dt}"); } if (whereList.Count > 0) { where.Append("and" + string.Join(" and ", whereList.ToArray())); } page.Condition = where.ToString(); page.PageSize = pageSize; page.PageIndex = pageIndex; DataTable dt = PublicPaging.ProcGetPageData(page, out recordCount); List <Users> Userlist = new List <Users>(); if (dt.Rows.Count > 0) { foreach (DataRow item in dt.Rows) { Users user = new Users(); user.UserID = Convert.ToInt32(item["UserID"]); user.UserName = item["UserName"].ToString(); user.Sex = item["Sex"].ToString(); user.Account = item["Account"].ToString(); user.PassWord = item["Password"].ToString(); user.Phone = item["Phone"].ToString(); user.Email = item["Email"].ToString(); user.RoleName = item["RoleName"].ToString(); user.CheckInTime = Convert.ToDateTime(item["CheckInTime"]); user.IsDelete = item["IsDelete"].ToString(); user.AlterTime = Convert.ToDateTime(item["AlterTime"]); Userlist.Add(user); } } return(Userlist); }