/// <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="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);
}
}