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);
}
