/// <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)
{
return(BillService.GetBills(count, billsFilter, out errMsg));
}
/// <summary>
/// 获取加油单导出列表加油单个数
/// </summary>
/// <param name="count"></param>
/// <param name="billsFilter"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static int GetBillsCountForExport(
BillsFilter billsFilter,
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 (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 count(*) from [Bill] {0}", where);
Object reCount = DBHelper.ExecuteScalar(CommandType.Text, sql, para);
return((int)reCount);
}
catch (Exception ex)
{
errMsg = ex.Message;
return(-1);
}
}
public HttpResponseMessage Get(int?count, int?project, int?department, DateTime startDate, DateTime endDate, int?lastId)
{
BillsFilter billsFilter = new BillsFilter();
billsFilter.Project = project;
billsFilter.Department = department;
billsFilter.StartDate = startDate;
billsFilter.EndDate = endDate;
billsFilter.LastId = lastId;
string errMsg = "";
IList <Model.Bill> listBill = BLL.BillManager.GetBills(count, billsFilter, out errMsg);
HttpStatusCode status;
string json;
//errMsg = "网络繁忙，请稍稍再度";
if (errMsg.Length > 0)
{
status = (HttpStatusCode)422;
var msg = new
{
errors = new
{
ServerSideError = errMsg
}
};
json = JsonConvert.SerializeObject(msg);
}
else
{
status = HttpStatusCode.OK;
var msg2 = new
{
bills = listBill
};
json = JsonConvert.SerializeObject(msg2);
}
return(new HttpResponseMessage(status)
{
Content = new StringContent(json, System.Text.Encoding.GetEncoding("UTF-8"), "application/json")
});
}
/// <summary>
/// 获取加油单导出列表加油单个数
/// </summary>
/// <param name="count"></param>
/// <param name="billsFilter"></param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static int GetBillsCountForExport(
BillsFilter billsFilter,
out string errMsg)
{
return(BillService.GetBillsCountForExport(billsFilter, out errMsg));
}
public HttpResponseMessage Get(int?project, int?department, DateTime startDate, DateTime endDate)
{
BillsFilter billsFilter = new BillsFilter();
billsFilter.Project = project;
billsFilter.Department = department;
billsFilter.StartDate = startDate;
billsFilter.EndDate = endDate;
billsFilter.LastId = 0;
string errMsg = "";
IList <Model.Export> listExport = new List <Model.Export>();
int billsCount = BLL.BillManager.GetBillsCountForExport(billsFilter, out errMsg);
if (billsCount == 0)
{
errMsg = "指定条件下没有找到加油单！";
}
else if (billsCount > 0)
{
try
{
Model.Export export = new Model.Export();
export.Id = 1;
export.Length = 0;
export.Total = billsCount;
listExport.Add(export);
}
catch (Exception ex)
{
errMsg = ex.Message;
}
}
HttpStatusCode status;
string json;
//errMsg = "网络繁忙，请稍稍再度";
if (errMsg.Length > 0)
{
status = (HttpStatusCode)422;
var msg = new
{
errors = new
{
ServerSideError = errMsg
}
};
json = JsonConvert.SerializeObject(msg);
}
else
{
status = HttpStatusCode.OK;
var msg2 = new
{
exports = listExport
};
json = JsonConvert.SerializeObject(msg2);
}
return(new HttpResponseMessage(status)
{
Content = new StringContent(json, System.Text.Encoding.GetEncoding("UTF-8"), "application/json")
});
}
public HttpResponseMessage Get(int?count, int?project, int?department, DateTime startDate, DateTime endDate, int?lastId, string name)
{
BillsFilter billsFilter = new BillsFilter();
billsFilter.Project = project;
billsFilter.Department = department;
billsFilter.StartDate = startDate;
billsFilter.EndDate = endDate;
billsFilter.LastId = lastId;
string errMsg = "";
IList <Model.Export> listExport = new List <Model.Export>();
IList <Model.BillPrinter> listBill = BLL.BillManager.GetBillsForExport(count, billsFilter, out errMsg);
if (listBill == null)
{
errMsg = "数据量过大,请适当缩小时间范围！";
}
else if (lastId == 0 && listBill.Count == 0)
{
errMsg = "指定条件下没有找到加油单！";
}
else
{
try
{
Model.Export export = new Model.Export();
int newLastId = (int)lastId;
if (listBill.Count > 0)
{
newLastId = listBill[listBill.Count - 1].Id;
StringBuilder sb = GetBillTextForExport(listBill, (lastId > 0 ? false : true));
byte[] data = Encoding.GetEncoding("gb2312").GetBytes(sb.ToString());
//byte[] data = new UTF8Encoding().GetBytes(sb.ToString());
string filePath = HttpContext.Current.Server.MapPath("../download//" + name + ".csv");
Function.BytesToFile(data, filePath);
}
export.Id = 1;
export.Length = listBill.Count;
export.LastId = newLastId;
listExport.Add(export);
}
catch (Exception ex)
{
errMsg = ex.Message;
}
}
HttpStatusCode status;
string json;
//errMsg = "网络繁忙，请稍稍再度";
if (errMsg.Length > 0)
{
status = (HttpStatusCode)422;
var msg = new
{
errors = new
{
ServerSideError = errMsg
}
};
json = JsonConvert.SerializeObject(msg);
}
else
{
status = HttpStatusCode.OK;
var msg2 = new
{
exports = listExport
};
json = JsonConvert.SerializeObject(msg2);
}
return(new HttpResponseMessage(status)
{
Content = new StringContent(json, System.Text.Encoding.GetEncoding("UTF-8"), "application/json")
});
}
/// <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="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);
}
}
