/// <summary>
/// 检查是否重复
/// </summary>
/// <param name="Model.Project"></param>
/// <returns></returns>
public static bool CheckRepeat(Model.Project model)
{
try
{
string sql = "select * from Project where Name = @Name and Id <> @Id";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@Name", model.Name.Trim()),
new SqlParameter("@Id", model.Id)
};
DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);
if (temp.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
ErrorLog e = new ErrorLog();
e.TargetIds = model.Id.ToString();
e.CodeTag = "CheckRepeat";
e.LogName = "检查是否重复-ForProject";
e.ErrorMsg = ex.Message.ToString();
ErrorLogService.AddErrorLog <Model.Project>(e, model);
return(true);
}
}
/// <summary>
/// 获取加油单报表
/// </summary>
/// <param name="billsFilter">筛选参数</param>
/// <param name="errMsg">错误信息</param>
/// <returns></returns>
public static IList <Model.Report> GetReports(
ReportsFilter reportsFilter,
out string errMsg)
{
errMsg = "";
try
{
string sql = "";
SqlParameter[] para = null;
List <SqlParameter> listPara = new List <SqlParameter>();
string where = "";
IList <string> listWhere = new List <string>();
if (reportsFilter != null && reportsFilter.Project > 0)
{
listPara.Add(new SqlParameter("@Project", reportsFilter.Project));
listWhere.Add("Project = @Project");
}
if (reportsFilter != null && reportsFilter.Department > 0)
{
listPara.Add(new SqlParameter("@Department", reportsFilter.Department));
listWhere.Add("Department = @Department");
}
if (reportsFilter != null)
{
listPara.Add(new SqlParameter("@StartDate", reportsFilter.StartDate));
listPara.Add(new SqlParameter("@EndDate", reportsFilter.EndDate));
listWhere.Add("Time between @StartDate and @EndDate");
}
para = listPara.ToArray();
if (listWhere.Count > 0)
{
where += "where ";
where += String.Join(" and ", listWhere.ToArray());
}
sql = string.Format("select convert(int,ROW_NUMBER() over(order by Project)) as Id,sum(Volume) as Volume,Project,Department,Oil,getdate() as CreatedDate from [Bill] {0} group by Project,Department,Oil", where);
DataTable dt = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);
IList <Model.Report> listReports = new List <Model.Report>();
foreach (DataRow dr in dt.Rows)
{
Model.Report report = new Model.Report();
report.Id = (int)dr["Id"];
report.Project = (int)dr["Project"];
report.Department = (int)dr["Department"];
report.Oil = (int)dr["Oil"];
report.Volume = double.Parse(dr["Volume"].ToString());
report.CreatedDate = (DateTime)dr["CreatedDate"];
listReports.Add(report);
}
return(listReports);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(null);
}
}
/// <summary>
/// 用户登录
/// </summary>
/// <returns></returns>
public static bool Login(ref Model.Login login, out string errMsg)
{
try
{
errMsg = "";
string sql = "select Id,Name,Role,IsEnable from [User] where (Phone = @LogName and Password = @LogPassword) or (Email = @LogName and Password = @LogPassword)";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@LogName", login.LogName.Trim()),
new SqlParameter("@LogPassword", login.LogPassword)
};
DataTable dtUser = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);
if (dtUser.Rows.Count > 0)
{
DataRow dr = dtUser.Rows[0];
if ((bool)dr["IsEnable"])
{
login.LogId = (int)dr["Id"];
login.IsPassed = true;
EventLog e = new EventLog();
e.TargetIds = login.LogId.ToString();
e.CodeTag = "Login";
e.LogName = "用户登录";
EventLogService.AddEventLog <Model.Login>(e, login);
return(true);
}
else
{
errMsg = "该用户已被禁用";
login.IsPassed = false;
return(false);
}
}
else
{
errMsg = "用户名或密码不正确";
login.IsPassed = false;
return(false);
}
}
catch (Exception ex)
{
errMsg = ex.Message;
ErrorLog e = new ErrorLog();
e.TargetIds = login.LogId.ToString();
e.CodeTag = "Login";
e.LogName = "用户登录";
e.ErrorMsg = ex.Message.ToString();
ErrorLogService.AddErrorLog <Model.Login>(e, login);
return(false);
}
}
/// <summary>
/// 获取加油单小票打印内容
/// </summary>
/// <param name="lastId"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static List <Model.BillPrinter> GetBillsForPrinter(int lastId, out string errMsg)
{
errMsg = "";
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@LastId", lastId)
};
DataTable dt = DBHelper.ExecuteGetDataTable(CommandType.StoredProcedure, "proc_BillsForPrinterSelect", para);
List <Model.BillPrinter> listBills = new List <Model.BillPrinter>();
foreach (DataRow dr in dt.Rows)
{
Model.BillPrinter bill = new Model.BillPrinter();
bill.Id = (int)dr["Id"];
bill.CarNumber = (string)dr["CarNumber"];
bill.CarVin = (string)dr["CarVin"];
bill.ProjectName = (string)dr["ProjectName"];
bill.DepartmentName = (string)dr["DepartmentName"];
bill.OilName = (string)dr["OilName"];
bill.Volume = double.Parse(dr["Volume"].ToString());
bill.Mileage = double.Parse(dr["Mileage"].ToString());
bill.DriverName = (string)dr["DriverName"];
bill.Rate = double.Parse(dr["Rate"].ToString());
bill.OilerName = (string)dr["OilerName"];
//bill.CreaterName = (string)dr["CreaterName"];
bill.Time = (DateTime)dr["Time"];
//bill.CreatedDate = (DateTime)dr["CreatedDate"];
bill.IsLost = (bool)dr["IsLost"];
bill.IsPrinted = (bool)dr["IsPrinted"];
listBills.Add(bill);
}
return(listBills);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(null);
}
}
/// <summary>
/// 获取签字内容
/// </summary>
/// <param name="id"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static Model.Signature GetSignatureById(int id, out string errMsg)
{
errMsg = "";
try
{
string sql = "";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@Id", id)
};
sql = "select * from [Signature] where Id = @Id";
DataTable dt = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);
Model.Signature signature = null;
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
string tempBase64String = Convert.ToBase64String((byte[])dr["Sign"]);
signature = new Signature
{
Id = (int)dr["Id"],
Name = (string)dr["Name"],
Sign = tempBase64String,
Creater = (int)dr["Creater"],
CreatedDate = (DateTime)dr["CreatedDate"]
};
}
else
{
errMsg = "该签字不存在或已被删除";
}
return(signature);;
}
catch (Exception ex)
{
errMsg = ex.Message;
return(null);
}
}
/// <summary>
/// 获取车辆申请单履历
/// </summary>
/// <param name="instance"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static IList <Model.Trace> GetTracesByInstance(int instance, out string errMsg)
{
errMsg = "";
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@Instance", instance)
};
string sql = "select * from [Trace] where Instance = @Instance order by Id desc";
DataTable dt = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);
IList <Model.Trace> listTraces = new List <Model.Trace>();
foreach (DataRow dr in dt.Rows)
{
Trace trace = new Trace();
trace.Id = (int)dr["Id"];
trace.Car = (int)dr["Car"];
trace.Instance = (int)dr["Instance"];
if (DBNull.Value.Equals(dr["PreviousTrace"]))
{
trace.PreviousTrace = null;
}
else
{
trace.PreviousTrace = new int?((int)dr["PreviousTrace"]);
}
trace.Status = (string)dr["Status"];
trace.IsFinished = (bool)dr["IsFinished"];
trace.IsArchived = (bool)dr["IsArchived"];
trace.Project = (int)dr["Project"];
trace.Department = (int)dr["Department"];
trace.UserName = (string)dr["UserName"];
if (!DBNull.Value.Equals(dr["Oils"]))
{
string oilsStr = (string)dr["Oils"];
string[] oilsArrayTemp = oilsStr.Split(new char[]
{
','
});
int[] oilsArray = Array.ConvertAll <string, int>(oilsArrayTemp, (string s) => int.Parse(s));
trace.Oils = oilsArray.ToList <int>();
}
trace.Goal = (string)dr["Goal"];
trace.StartDate = (DateTime)dr["StartDate"];
trace.EndDate = (DateTime)dr["EndDate"];
trace.StartInfo = (string)dr["StartInfo"];
trace.EndInfo = (string)dr["EndInfo"];
trace.Creater = (int)dr["Creater"];
trace.CreatedDate = (DateTime)dr["CreatedDate"];
trace.Modifier = (int)dr["Modifier"];
trace.ModifiedDate = (DateTime)dr["ModifiedDate"];
listTraces.Add(trace);
}
return(listTraces);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(null);
}
}
/// <summary>
/// 获取已归档试件
/// </summary>
/// <param name="key"></param>
/// <param name="count"></param>
/// <param name="lastId"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static IList <Model.Piece> GetArchivedPieces(string key, int count, int lastId, out string errMsg)
{
errMsg = "";
try
{
if (key == null)
{
key = "";
}
else
{
key = string.Format("%{0}%", key);
}
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@Key", (key == null ? "" : key)),
new SqlParameter("@Count", count),
new SqlParameter("@LastId", lastId)
};
string keyWhere = "";
if (key != null && key.Length > 0)
{
keyWhere = " (Name like @Key or Number like @Key or AccessoryFactory like @Key or VehicleType like @Key or Place like @Key) and ";
}
string lastIdWhere = "";
if (lastId > 0)
{
lastIdWhere = " and Id < @LastId ";
}
string sql = string.Format("select top (@Count) * from [Piece] where {0} IsArchived = 1 {1} order by Id desc", keyWhere, lastIdWhere);
DataTable dt = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);
IList <Model.Piece> listPieces = new List <Model.Piece>();
foreach (DataRow dr in dt.Rows)
{
Piece piece = new Piece();
piece.Id = (int)dr["Id"];
piece.Name = (string)dr["Name"];
piece.Number = (string)dr["Number"];
piece.Order = (string)dr["Order"];
piece.Count = (int)dr["Count"];
piece.PrintedCount = (int)dr["PrintedCount"];
piece.IsPrinted = (bool)dr["IsPrinted"];
piece.Ots = (string)dr["Ots"];
piece.DelegateNumber = (string)dr["DelegateNumber"];
piece.AccessoryFactory = (string)dr["AccessoryFactory"];
piece.VehicleType = (string)dr["VehicleType"];
piece.TestContent = (string)dr["TestContent"];
piece.SendPerson = (string)dr["SendPerson"];
piece.ChargePerson = (string)dr["ChargePerson"];
if (DBNull.Value.Equals(dr["SendDate"]))
{
piece.SendDate = null;
}
else
{
piece.SendDate = (DateTime?)dr["SendDate"];
}
piece.Place = (string)dr["Place"];
piece.IsEnable = (bool)dr["IsEnable"];
piece.IsArchived = (bool)dr["IsArchived"];
piece.Description = (string)dr["Description"];
piece.Creater = (int)dr["Creater"];
piece.CreatedDate = (DateTime)dr["CreatedDate"];
piece.Modifier = (int)dr["Modifier"];
piece.ModifiedDate = (DateTime)dr["ModifiedDate"];
listPieces.Add(piece);
}
return(listPieces);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(null);
}
}
/// <summary>
/// 获取加油单导出列表
/// </summary>
/// <param name="count">要返回的记录个数</param>
/// <param name="billsFilter">筛选参数</param>
/// <param name="lastId">最后（小）id值</param>
/// <param name="errMsg">错误信息</param>
/// <returns></returns>
public static IList <Model.BillPrinter> GetBillsForExport(
int?count,
BillsFilter billsFilter,
out string errMsg)
{
errMsg = "";
try
{
string sql = "";
SqlParameter[] para = null;
List <SqlParameter> listPara = new List <SqlParameter>();
if (count == null)
{
count = 2000;
}
listPara.Add(new SqlParameter("@Count", count));
string where = "";
IList <string> listWhere = new List <string>();
if (billsFilter.LastId > 0)
{
listPara.Add(new SqlParameter("@LastId", billsFilter.LastId));
listWhere.Add("Id > @LastId");
}
if (billsFilter != null && billsFilter.Project > 0)
{
listPara.Add(new SqlParameter("@Project", billsFilter.Project));
listWhere.Add("Project = @Project");
}
if (billsFilter != null && billsFilter.Department > 0)
{
listPara.Add(new SqlParameter("@Department", billsFilter.Department));
listWhere.Add("Department = @Department");
}
if (billsFilter != null)
{
listPara.Add(new SqlParameter("@StartDate", billsFilter.StartDate));
listPara.Add(new SqlParameter("@EndDate", billsFilter.EndDate));
listWhere.Add("Time between @StartDate and @EndDate");
}
para = listPara.ToArray();
if (listWhere.Count > 0)
{
where += "where ";
where += String.Join(" and ", listWhere.ToArray());
}
sql = string.Format(@"select top (@Count) Id,(select Number from Car where Car.Id = Bill.Car) as CarNumber,
(select Vin from Car where Car.Id = Bill.Car) as CarVin,
(select [Name] from Project where Project.Id = Bill.Project) as ProjectName,
(select [Name] from Department where Department.Id = Bill.Department) as DepartmentName,
(select [Name] from Oil where Oil.Id = Bill.Oil) as OilName,
Volume,Mileage,DriverName,Rate,
(select [Name] from [User] where [User].Id = Bill.Oiler) as OilerName,
(select [UserName] from Instance where [Instance].Id = Bill.Instance) as UserName,
(select [Name] from [User] where [User].Id = (select [Creater] from Instance where [Instance].Id = Bill.Instance)) as ApplicantName,
[Time],IsLost,IsPrinted from [Bill] {0} order by Id asc" , where);
DataTable dt = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);
IList <Model.BillPrinter> listBills = new List <Model.BillPrinter>();
foreach (DataRow dr in dt.Rows)
{
Model.BillPrinter bill = new Model.BillPrinter();
bill.Id = (int)dr["Id"];
bill.CarNumber = (string)dr["CarNumber"];
bill.CarVin = (string)dr["CarVin"];
bill.ProjectName = (string)dr["ProjectName"];
bill.DepartmentName = (string)dr["DepartmentName"];
bill.OilName = (string)dr["OilName"];
bill.UserName = (string)dr["UserName"];
bill.ApplicantName = (string)dr["ApplicantName"];
bill.Volume = double.Parse(dr["Volume"].ToString());
bill.Mileage = double.Parse(dr["Mileage"].ToString());
bill.DriverName = (string)dr["DriverName"];
bill.Rate = double.Parse(dr["Rate"].ToString());
bill.OilerName = (string)dr["OilerName"];
//bill.CreaterName = (string)dr["CreaterName"];
bill.Time = (DateTime)dr["Time"];
//bill.CreatedDate = (DateTime)dr["CreatedDate"];
bill.IsLost = (bool)dr["IsLost"];
bill.IsPrinted = (bool)dr["IsPrinted"];
listBills.Add(bill);
}
return(listBills);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(null);
}
}
/// <summary>
/// 获取单个加油单
/// </summary>
/// <param name="id"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static Model.Bill GetBillById(int id, out string errMsg)
{
errMsg = "";
try
{
string sql = "";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@Id", id)
};
sql = "select * from [Bill] where Id = @Id";
DataTable dt = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);
Model.Bill bill = null;
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
bill = new Bill();
bill.Id = (int)dr["Id"];
bill.Car = (int)dr["Car"];
bill.Instance = (int)dr["Instance"];
bill.Project = (int)dr["Project"];
bill.Department = (int)dr["Department"];
bill.Oil = (int)dr["Oil"];
bill.Volume = double.Parse(dr["Volume"].ToString());
bill.Mileage = double.Parse(dr["Mileage"].ToString());
bill.DriverName = (string)dr["DriverName"];
if (DBNull.Value.Equals(dr["Signature"]))
{
bill.Signature = null;
}
else
{
bill.Signature = new int?((int)dr["Signature"]);
}
if (DBNull.Value.Equals(dr["PreviousOil"]))
{
bill.PreviousOil = null;
}
else
{
bill.PreviousOil = new int?((int)dr["PreviousOil"]);
}
bill.Rate = double.Parse(dr["Rate"].ToString());
bill.Oiler = (int)dr["Oiler"];
bill.Time = (DateTime)dr["Time"];
bill.IsLost = (bool)dr["IsLost"];
bill.IsPrinted = (bool)dr["IsPrinted"];
bill.Creater = (int)dr["Creater"];
bill.CreatedDate = (DateTime)dr["CreatedDate"];
bill.Modifier = (int)dr["Modifier"];
bill.ModifiedDate = (DateTime)dr["ModifiedDate"];
}
else
{
errMsg = "该加油单不存在或已被删除";
}
return(bill);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(null);
}
}
/// <summary>
/// 获取指定申请单车辆上一次加油单
/// </summary>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static IList <Model.Bill> GetLastOneBillByCar(int id, int car, out string errMsg)
{
errMsg = "";
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@Id", id),
new SqlParameter("@Car", car)
};
string sql = "select top 1 * from [Bill] where Id < @Id and Car = @Car order by Id desc";
DataTable dt = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);
IList <Model.Bill> listBills = new List <Model.Bill>();
foreach (DataRow dr in dt.Rows)
{
Model.Bill bill = new Model.Bill();
bill.Id = (int)dr["Id"];
bill.Car = (int)dr["Car"];
bill.Instance = (int)dr["Instance"];
bill.Project = (int)dr["Project"];
bill.Department = (int)dr["Department"];
bill.Oil = (int)dr["Oil"];
bill.Volume = double.Parse(dr["Volume"].ToString());
bill.Mileage = double.Parse(dr["Mileage"].ToString());
bill.DriverName = (string)dr["DriverName"];
if (DBNull.Value.Equals(dr["Signature"]))
{
bill.Signature = null;
}
else
{
bill.Signature = new int?((int)dr["Signature"]);
}
if (DBNull.Value.Equals(dr["PreviousOil"]))
{
bill.PreviousOil = null;
}
else
{
bill.PreviousOil = new int?((int)dr["PreviousOil"]);
}
bill.Rate = double.Parse(dr["Rate"].ToString());
bill.Oiler = (int)dr["Oiler"];
bill.Time = (DateTime)dr["Time"];
bill.IsLost = (bool)dr["IsLost"];
bill.IsPrinted = (bool)dr["IsPrinted"];
bill.Creater = (int)dr["Creater"];
bill.CreatedDate = (DateTime)dr["CreatedDate"];
bill.Modifier = (int)dr["Modifier"];
bill.ModifiedDate = (DateTime)dr["ModifiedDate"];
listBills.Add(bill);
}
return(listBills);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(null);
}
}
/// <summary>
/// 获取加油单列表
/// </summary>
/// <param name="count">要返回的记录个数</param>
/// <param name="billsFilter">筛选参数</param>
/// <param name="lastId">最后（小）id值</param>
/// <param name="errMsg">错误信息</param>
/// <returns></returns>
public static IList <Model.Bill> GetBills(
int?count,
BillsFilter billsFilter,
out string errMsg)
{
errMsg = "";
try
{
string sql = "";
SqlParameter[] para = null;
List <SqlParameter> listPara = new List <SqlParameter>();
if (count == null)
{
count = 20;
}
listPara.Add(new SqlParameter("@Count", count));
string where = "";
IList <string> listWhere = new List <string>();
if (billsFilter.LastId > 0)
{
listPara.Add(new SqlParameter("@LastId", billsFilter.LastId));
listWhere.Add("Id > @LastId");
}
if (billsFilter != null && billsFilter.Project > 0)
{
listPara.Add(new SqlParameter("@Project", billsFilter.Project));
listWhere.Add("Project = @Project");
}
if (billsFilter != null && billsFilter.Department > 0)
{
listPara.Add(new SqlParameter("@Department", billsFilter.Department));
listWhere.Add("Department = @Department");
}
if (billsFilter != null)
{
listPara.Add(new SqlParameter("@StartDate", billsFilter.StartDate));
listPara.Add(new SqlParameter("@EndDate", billsFilter.EndDate));
listWhere.Add("Time between @StartDate and @EndDate");
}
para = listPara.ToArray();
if (listWhere.Count > 0)
{
where += "where ";
where += String.Join(" and ", listWhere.ToArray());
}
sql = string.Format("select top (@Count) * from [Bill] {0} order by Id asc", where);
DataTable dt = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);
IList <Model.Bill> listBills = new List <Model.Bill>();
foreach (DataRow dr in dt.Rows)
{
Model.Bill bill = new Model.Bill();
bill.Id = (int)dr["Id"];
bill.Car = (int)dr["Car"];
bill.Instance = (int)dr["Instance"];
bill.Project = (int)dr["Project"];
bill.Department = (int)dr["Department"];
bill.Oil = (int)dr["Oil"];
bill.Volume = double.Parse(dr["Volume"].ToString());
bill.Mileage = double.Parse(dr["Mileage"].ToString());
bill.DriverName = (string)dr["DriverName"];
if (DBNull.Value.Equals(dr["Signature"]))
{
bill.Signature = null;
}
else
{
bill.Signature = new int?((int)dr["Signature"]);
}
if (DBNull.Value.Equals(dr["PreviousOil"]))
{
bill.PreviousOil = null;
}
else
{
bill.PreviousOil = new int?((int)dr["PreviousOil"]);
}
bill.Rate = double.Parse(dr["Rate"].ToString());
bill.Oiler = (int)dr["Oiler"];
bill.Time = (DateTime)dr["Time"];
bill.IsLost = (bool)dr["IsLost"];
bill.IsPrinted = (bool)dr["IsPrinted"];
bill.Creater = (int)dr["Creater"];
bill.CreatedDate = (DateTime)dr["CreatedDate"];
bill.Modifier = (int)dr["Modifier"];
bill.ModifiedDate = (DateTime)dr["ModifiedDate"];
listBills.Add(bill);
}
return(listBills);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(null);
}
}
/// <summary>
/// 获取单个车辆
/// </summary>
/// <param name="id"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static Model.Car GetCarById(int id, out string errMsg)
{
errMsg = "";
try
{
string sql = "";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@Id", id)
};
sql = "select * from [Car] where Id = @Id";
DataTable dt = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);
Model.Car car = null;
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
car = new Car();
car.Id = (int)dr["Id"];
car.Number = (string)dr["Number"];
car.Vin = (string)dr["Vin"];
car.Model = (string)dr["Model"];
car.IsArchived = (bool)dr["IsArchived"];
car.InstanceCount = (int)dr["InstanceCount"];
car.BillCount = (int)dr["BillCount"];
if (DBNull.Value.Equals(dr["PreviousOil"]))
{
car.PreviousOil = null;
}
else
{
car.PreviousOil = new int?((int)dr["PreviousOil"]);
}
if (DBNull.Value.Equals(dr["LastOil"]))
{
car.LastOil = null;
}
else
{
car.LastOil = new int?((int)dr["LastOil"]);
}
car.LastVolume = (double)dr["LastVolume"];
car.LastMileage = (double)dr["LastMileage"];
car.LastRate = (double)dr["LastRate"];
car.Description = (string)dr["Description"];
car.Creater = (int)dr["Creater"];
car.CreatedDate = (DateTime)dr["CreatedDate"];
car.Modifier = (int)dr["Modifier"];
car.ModifiedDate = (DateTime)dr["ModifiedDate"];
}
else
{
errMsg = "该车辆不存在或已被删除";
}
return(car);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(null);
}
}
/// <summary>
/// 获取已归档车辆
/// </summary>
/// <param name="key"></param>
/// <param name="count"></param>
/// <param name="lastId"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static IList <Model.Car> GetArchivedCars(string key, int count, int lastId, out string errMsg)
{
errMsg = "";
try
{
if (key == null)
{
key = "";
}
else
{
key = string.Format("%{0}%", key);
}
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@Key", (key == null ? "" : key)),
new SqlParameter("@Count", count),
new SqlParameter("@LastId", lastId)
};
string keyWhere = "";
if (key != null && key.Length > 0)
{
keyWhere = " (Number like @Key or Vin like @Key or Model like @Key) and ";
}
string lastIdWhere = "";
if (lastId > 0)
{
lastIdWhere = " and Id < @LastId ";
}
string sql = string.Format("select top (@Count) * from [Car] where {0} IsArchived = 1 {1} order by Id desc", keyWhere, lastIdWhere);
DataTable dt = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);
IList <Model.Car> listCars = new List <Model.Car>();
foreach (DataRow dr in dt.Rows)
{
Car car = new Car();
car.Id = (int)dr["Id"];
car.Number = (string)dr["Number"];
car.Vin = (string)dr["Vin"];
car.Model = (string)dr["Model"];
car.IsArchived = (bool)dr["IsArchived"];
car.InstanceCount = (int)dr["InstanceCount"];
car.BillCount = (int)dr["BillCount"];
if (DBNull.Value.Equals(dr["PreviousOil"]))
{
car.PreviousOil = null;
}
else
{
car.PreviousOil = new int?((int)dr["PreviousOil"]);
}
if (DBNull.Value.Equals(dr["LastOil"]))
{
car.LastOil = null;
}
else
{
car.LastOil = new int?((int)dr["LastOil"]);
}
car.LastVolume = (double)dr["LastVolume"];
car.LastMileage = (double)dr["LastMileage"];
car.LastRate = (double)dr["LastRate"];
car.Description = (string)dr["Description"];
car.Creater = (int)dr["Creater"];
car.CreatedDate = (DateTime)dr["CreatedDate"];
car.Modifier = (int)dr["Modifier"];
car.ModifiedDate = (DateTime)dr["ModifiedDate"];
listCars.Add(car);
}
return(listCars);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(null);
}
}
/// <summary>
/// 获取单个申请单
/// </summary>
/// <param name="id"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static Model.Instance GetInstanceById(int id, out string errMsg)
{
errMsg = "";
try
{
string sql = "";
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@Id", id)
};
sql = "select * from [Instance] where Id = @Id";
DataTable dt = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);
Model.Instance instance = null;
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
instance = new Instance();
instance.Id = (int)dr["Id"];
instance.Car = (int)dr["Car"];
instance.Project = (int)dr["Project"];
instance.Department = (int)dr["Department"];
instance.UserName = (string)dr["UserName"];
if (!DBNull.Value.Equals(dr["Oils"]))
{
string oilsStr = (string)dr["Oils"];
string[] oilsArrayTemp = oilsStr.Split(new char[]
{
','
});
int[] oilsArray = Array.ConvertAll <string, int>(oilsArrayTemp, (string s) => int.Parse(s));
instance.Oils = oilsArray.ToList <int>();
}
instance.Goal = (string)dr["Goal"];
instance.StartDate = (DateTime)dr["StartDate"];
instance.EndDate = (DateTime)dr["EndDate"];
instance.IsReleased = (bool)dr["IsReleased"];
instance.IsPending = (bool)dr["IsPending"];
instance.IsArchived = (bool)dr["IsArchived"];
instance.IsEnable = (bool)dr["IsEnable"];
//if (DBNull.Value.Equals(dr["Message"]))
//{
// instance.Message = null;
//}
//else
//{
// instance.Message = new int?((int)dr["Message"]);
//}
instance.BillCount = (int)dr["BillCount"];
instance.Creater = (int)dr["Creater"];
instance.CreatedDate = (DateTime)dr["CreatedDate"];
instance.Modifier = (int)dr["Modifier"];
instance.ModifiedDate = (DateTime)dr["ModifiedDate"];
}
else
{
errMsg = "该申请单不存在或已被删除";
}
return(instance);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(null);
}
}
/// <summary>
/// 获取车辆申请单
/// </summary>
/// <param name="car"></param>
/// <param name="count"></param>
/// <param name="lastId"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static IList <Model.Instance> GetInstancesByCar(int car, int count, int lastId, out string errMsg)
{
errMsg = "";
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@Car", car),
new SqlParameter("@Count", count),
new SqlParameter("@LastId", lastId)
};
string lastIdWhere = "";
if (lastId > 0)
{
lastIdWhere = " and Id < @LastId ";
}
string sql = string.Format("select top (@Count) * from [Instance] where Car = @Car and IsArchived = 1 {0} order by Id desc", lastIdWhere);
DataTable dt = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);
IList <Model.Instance> listInstances = new List <Model.Instance>();
foreach (DataRow dr in dt.Rows)
{
Instance instance = new Instance();
instance.Id = (int)dr["Id"];
instance.Car = (int)dr["Car"];
instance.Project = (int)dr["Project"];
instance.Department = (int)dr["Department"];
instance.UserName = (string)dr["UserName"];
if (!DBNull.Value.Equals(dr["Oils"]))
{
string oilsStr = (string)dr["Oils"];
string[] oilsArrayTemp = oilsStr.Split(new char[]
{
','
});
int[] oilsArray = Array.ConvertAll <string, int>(oilsArrayTemp, (string s) => int.Parse(s));
instance.Oils = oilsArray.ToList <int>();
}
instance.Goal = (string)dr["Goal"];
instance.StartDate = (DateTime)dr["StartDate"];
instance.EndDate = (DateTime)dr["EndDate"];
instance.IsReleased = (bool)dr["IsReleased"];
instance.IsPending = (bool)dr["IsPending"];
instance.IsArchived = (bool)dr["IsArchived"];
instance.IsEnable = (bool)dr["IsEnable"];
//if (DBNull.Value.Equals(dr["Message"]))
//{
// instance.Message = null;
//}
//else
//{
// instance.Message = new int?((int)dr["Message"]);
//}
instance.BillCount = (int)dr["BillCount"];
instance.Creater = (int)dr["Creater"];
instance.CreatedDate = (DateTime)dr["CreatedDate"];
instance.Modifier = (int)dr["Modifier"];
instance.ModifiedDate = (DateTime)dr["ModifiedDate"];
listInstances.Add(instance);
}
return(listInstances);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(null);
}
}
