using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Web;
|
using System.Web.UI;
|
using System.Web.UI.WebControls;
|
using CY.BLL;
|
using CY.Model;
|
using CY.Infrastructure.Query;
|
using CY.Infrastructure.Common;
|
using CY.BLL.Sys;
|
using CY.BLL.OA;
|
using CY.BLL.EC;
|
using CY.BLL.Inquiry;
|
using CY.Model.Inquiry;
|
using System.Drawing;
|
using CY.Model.OA;
|
using CY.SQLDAL;
|
using System.Data.SqlClient;
|
using CY.Infrastructure.Logging;
|
using NPOI.SS.UserModel;
|
using NPOI.XSSF.UserModel;
|
using System.IO;
|
|
namespace CY.WebForm.Pages.business
|
{
|
public partial class DeliverPlanDakaList : BasePage
|
{
|
|
OA_DriverRecordBLL _oA_DriverRecordBLL = null;
|
|
OA_CarManageBll _OA_CarManageBll = null;
|
|
|
|
//初始化
|
public DeliverPlanDakaList()
|
{
|
|
_oA_DriverRecordBLL = new OA_DriverRecordBLL();
|
|
_OA_CarManageBll = new OA_CarManageBll();
|
|
}
|
|
protected void Page_Load(object sender, EventArgs e)
|
{
|
UCPager1.AspNetPager.PageChanged += AspNetPager1_PageChanged;
|
if (!IsPostBack)
|
{
|
|
|
|
switch (Request["Target"])
|
{
|
|
case "AcceptOrderAll":
|
|
break;
|
case "AcceptOrderAll2":
|
|
break;
|
default:
|
InitialSelectData();
|
BindList();
|
return;
|
}
|
Response.End();
|
}
|
}
|
|
|
//加载查询条件
|
protected void InitialSelectData()
|
{
|
|
|
this.selCarId.DataSource = _OA_CarManageBll.SelectListByFirmId(CurrentUser.MemberId);
|
this.selCarId.DataTextField = "CarNumber";
|
this.selCarId.DataValueField = "Keyid";
|
this.selCarId.DataBind();
|
this.selCarId.Items.Insert(0, new ListItem("全部", ""));
|
|
|
this.txtCreatTimestart.Value = DateTime.Now.ToString("yyyy-MM-dd");
|
this.txtCreatTimeend.Value = DateTime.Now.ToString("yyyy-MM-dd");
|
|
}
|
|
//分页事件
|
protected void AspNetPager1_PageChanged(object src, EventArgs e)
|
{
|
BindList();
|
}
|
|
//查询事件
|
protected void btn_Search_Click(object sender, EventArgs e)
|
{
|
BindList();
|
}
|
|
|
//绑定列表
|
private void BindList()
|
{
|
|
|
|
|
|
|
Pagination pa = new Pagination();
|
pa.PageSize = UCPager1.AspNetPager.PageSize;
|
pa.PageIndex = UCPager1.AspNetPager.CurrentPageIndex;
|
|
IEnumerable<OA_DriverRecord> oA_DriverRecords = _oA_DriverRecordBLL.SelectModelPage(pa,
|
this.txtCreatTimestart.Value,
|
this.txtCreatTimeend.Value,
|
this.selCarId.Value,
|
this.txtCreater.Value,
|
""
|
);
|
|
Database DC = new Database();
|
|
try
|
{
|
List<OA_CorporateClientsContract> fileDataList = null;
|
foreach (var oA_DriverRecord in oA_DriverRecords)
|
{
|
|
|
var oA_DriverRecord1 = _oA_DriverRecordBLL.GetModelByTimeCarIDClockType(oA_DriverRecord.CreatTime, oA_DriverRecord.CarID, 2);
|
if (oA_DriverRecord1 != null)
|
{
|
oA_DriverRecord1.CreatTimeName = oA_DriverRecord1.CreatTime.ToString("yyyy-MM-dd HH:mm:ss");
|
if (string.IsNullOrEmpty(oA_DriverRecord1.MileageImg))
|
{
|
oA_DriverRecord1.MileageImgName = "";
|
}
|
else
|
{
|
oA_DriverRecord1.MileageImgName = "<img src=\"" + oA_DriverRecord1.MileageImg + "\" width=\"20\" height=\"20\" >";
|
}
|
|
|
|
|
|
string sqlStr1 = string.Format(@" select * from [dbo].[OA_attachment] where [OA_Id]='{0}' and [AttachmentType]=4 order by [CreateTime] ", oA_DriverRecord1.Id);
|
|
SqlCommand myCmd1 = new SqlCommand(sqlStr1, DC.Connection);
|
//SqlDataAdapter myDa = new SqlDataAdapter(myCmd);
|
var a1 = myCmd1.ExecuteReader();
|
//myDa.Dispose();
|
fileDataList = new List<OA_CorporateClientsContract>();
|
OA_CorporateClientsContract Contract1;
|
var j = 1;
|
while (a1.Read())
|
{ //注意如何将每次读取到的记录添加到listbox1中（因为第一次做时出错了，嘿嘿）
|
Contract1 = new OA_CorporateClientsContract();
|
// Contract.Keyid = a.GetInt32(0);
|
//Contract.CorporateClientsid = a.GetGuid(1);
|
//Contract.CorporateClientsName = a.GetString(4);
|
Contract1.FileName = a1[4].ToString();
|
Contract1.PageCode = j;
|
Contract1.FilePath = a1.GetString(2);
|
// Contract.Remark = a.GetString(6);
|
j += 1;
|
|
fileDataList.Add(Contract1);
|
}
|
a1.Close(); //关闭sqldatareader
|
|
if (fileDataList.Count > 0)
|
{
|
oA_DriverRecord1.ZhuangcheImg = "<img src=\"" + fileDataList[0].FilePath + "\" width=\"20\" height=\"20\" >";
|
}
|
else
|
{
|
oA_DriverRecord1.ZhuangcheImg = "";
|
}
|
|
|
|
|
sqlStr1 = string.Format(@" select * from [dbo].[OA_attachment] where [OA_Id]='{0}' and [AttachmentType]=5 order by [CreateTime] ", oA_DriverRecord1.Id);
|
|
myCmd1 = new SqlCommand(sqlStr1, DC.Connection);
|
//SqlDataAdapter myDa = new SqlDataAdapter(myCmd);
|
a1 = myCmd1.ExecuteReader();
|
//myDa.Dispose();
|
fileDataList = new List<OA_CorporateClientsContract>();
|
j = 1;
|
while (a1.Read())
|
{ //注意如何将每次读取到的记录添加到listbox1中（因为第一次做时出错了，嘿嘿）
|
Contract1 = new OA_CorporateClientsContract();
|
// Contract.Keyid = a.GetInt32(0);
|
//Contract.CorporateClientsid = a.GetGuid(1);
|
//Contract.CorporateClientsName = a.GetString(4);
|
Contract1.FileName = a1[4].ToString();
|
Contract1.PageCode = j;
|
Contract1.FilePath = a1.GetString(2);
|
// Contract.Remark = a.GetString(6);
|
j += 1;
|
|
fileDataList.Add(Contract1);
|
}
|
a1.Close(); //关闭sqldatareader
|
|
if (fileDataList.Count > 0)
|
{
|
oA_DriverRecord1.CarConditionImg = "<img src=\"" + fileDataList[0].FilePath + "\" width=\"20\" height=\"20\" >";
|
}
|
else
|
{
|
oA_DriverRecord1.CarConditionImg = "";
|
}
|
|
oA_DriverRecord.oA_DriverRecord = oA_DriverRecord1;
|
}
|
else
|
{
|
oA_DriverRecord.oA_DriverRecord = new OA_DriverRecord();
|
}
|
|
|
|
if (string.IsNullOrEmpty(oA_DriverRecord.MileageImg))
|
{
|
oA_DriverRecord.MileageImgName = "";
|
}
|
else
|
{
|
oA_DriverRecord.MileageImgName = "<img src=\"" + oA_DriverRecord.MileageImg + "\" width=\"20\" height=\"20\" >";
|
}
|
|
|
|
|
|
string sqlStr = string.Format(@" select * from [dbo].[OA_attachment] where [OA_Id]='{0}' and [AttachmentType]=4 order by [CreateTime] ", oA_DriverRecord.Id);
|
|
SqlCommand myCmd = new SqlCommand(sqlStr, DC.Connection);
|
//SqlDataAdapter myDa = new SqlDataAdapter(myCmd);
|
var a = myCmd.ExecuteReader();
|
//myDa.Dispose();
|
fileDataList = new List<OA_CorporateClientsContract>();
|
OA_CorporateClientsContract Contract;
|
var i = 1;
|
while (a.Read())
|
{ //注意如何将每次读取到的记录添加到listbox1中（因为第一次做时出错了，嘿嘿）
|
Contract = new OA_CorporateClientsContract();
|
// Contract.Keyid = a.GetInt32(0);
|
//Contract.CorporateClientsid = a.GetGuid(1);
|
//Contract.CorporateClientsName = a.GetString(4);
|
Contract.FileName = a[4].ToString();
|
Contract.PageCode = i;
|
Contract.FilePath = a.GetString(2);
|
// Contract.Remark = a.GetString(6);
|
i += 1;
|
|
fileDataList.Add(Contract);
|
}
|
a.Close(); //关闭sqldatareader
|
|
if (fileDataList.Count > 0)
|
{
|
oA_DriverRecord.ZhuangcheImg = "<img src=\"" + fileDataList[0].FilePath + "\" width=\"20\" height=\"20\" >";
|
}
|
else
|
{
|
oA_DriverRecord.ZhuangcheImg = "";
|
}
|
|
|
|
|
sqlStr = string.Format(@" select * from [dbo].[OA_attachment] where [OA_Id]='{0}' and [AttachmentType]=5 order by [CreateTime] ", oA_DriverRecord.Id);
|
|
myCmd = new SqlCommand(sqlStr, DC.Connection);
|
//SqlDataAdapter myDa = new SqlDataAdapter(myCmd);
|
a = myCmd.ExecuteReader();
|
//myDa.Dispose();
|
fileDataList = new List<OA_CorporateClientsContract>();
|
i = 1;
|
while (a.Read())
|
{ //注意如何将每次读取到的记录添加到listbox1中（因为第一次做时出错了，嘿嘿）
|
Contract = new OA_CorporateClientsContract();
|
// Contract.Keyid = a.GetInt32(0);
|
//Contract.CorporateClientsid = a.GetGuid(1);
|
//Contract.CorporateClientsName = a.GetString(4);
|
Contract.FileName = a[4].ToString();
|
Contract.PageCode = i;
|
Contract.FilePath = a.GetString(2);
|
// Contract.Remark = a.GetString(6);
|
i += 1;
|
|
fileDataList.Add(Contract);
|
}
|
a.Close(); //关闭sqldatareader
|
|
if (fileDataList.Count > 0)
|
{
|
oA_DriverRecord.CarConditionImg = "<img src=\"" + fileDataList[0].FilePath + "\" width=\"20\" height=\"20\" >";
|
}
|
else
|
{
|
oA_DriverRecord.CarConditionImg = "";
|
}
|
|
|
}
|
}
|
catch (Exception ee)
|
{
|
new Log4NetAdapter().Log("查找送货单图片出错：" + ee.Message);
|
|
}
|
finally
|
{
|
if (DC.Connection.State != System.Data.ConnectionState.Closed)
|
DC.Connection.Close();
|
}
|
|
|
this.RepClientList.DataSource = oA_DriverRecords;
|
this.RepClientList.DataBind();
|
UCPager1.AspNetPager.RecordCount = pa.RecordCount;
|
}
|
|
|
|
|
//查询事件
|
protected void btn_Daochu_Click(object src, EventArgs e)
|
{
|
|
byte[] fileBytes = ExportToExcel();
|
if (fileBytes != null)
|
{
|
Response.Clear();
|
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
|
Response.AppendHeader("Content-Disposition", "attachment; filename=打卡导出.xlsx");
|
Response.BinaryWrite(fileBytes);
|
Response.End();
|
}
|
else
|
{
|
// 处理导出失败的情况
|
Response.Write("导出失败，请稍后重试！");
|
}
|
|
}
|
|
|
public byte[] ExportToExcel()
|
{
|
byte[] bt = null;
|
try
|
{
|
Pagination pa = new Pagination();
|
pa.PageSize = 1000;
|
pa.PageIndex = 1;
|
|
|
|
IEnumerable<OA_DriverRecord> results = _oA_DriverRecordBLL.SelectModelPage(pa,
|
this.txtCreatTimestart.Value,
|
this.txtCreatTimeend.Value,
|
this.selCarId.Value,
|
this.txtCreater.Value,
|
""
|
);
|
|
// 创建工作簿
|
IWorkbook workbook = new XSSFWorkbook();
|
ISheet sheet = workbook.CreateSheet("导出数据");
|
|
// 创建表头
|
IRow headerRow = sheet.CreateRow(0);
|
headerRow.CreateCell(0).SetCellValue("编号");
|
headerRow.CreateCell(1).SetCellValue("车辆");
|
headerRow.CreateCell(2).SetCellValue("驾驶员");
|
headerRow.CreateCell(3).SetCellValue("打卡时间");
|
headerRow.CreateCell(4).SetCellValue("打卡类型");
|
headerRow.CreateCell(5).SetCellValue("未装货情况");
|
headerRow.CreateCell(6).SetCellValue("车况");
|
headerRow.CreateCell(7).SetCellValue("备注");
|
headerRow.CreateCell(8).SetCellValue("里程(公里)");
|
|
|
|
// 添加数据
|
int rowIndex = 1;
|
foreach (var oA_DriverRecord in results)
|
{
|
IRow row = sheet.CreateRow(rowIndex++);
|
row.CreateCell(0).SetCellValue((rowIndex - 1).ToString());
|
row.CreateCell(1).SetCellValue(oA_DriverRecord.CarName);
|
row.CreateCell(2).SetCellValue(oA_DriverRecord.Creater);
|
row.CreateCell(3).SetCellValue(oA_DriverRecord.CreatTime.ToString("yyyy-MM-dd"));
|
row.CreateCell(4).SetCellValue(oA_DriverRecord.ClockTypeName);
|
row.CreateCell(5).SetCellValue(oA_DriverRecord.EmptyCause);
|
row.CreateCell(6).SetCellValue(oA_DriverRecord.CarCondition);
|
row.CreateCell(7).SetCellValue(oA_DriverRecord.Remark);
|
row.CreateCell(8).SetCellValue(oA_DriverRecord.Mileage.ToString());
|
|
|
|
}
|
|
// 将工作簿写入内存流
|
using (var ms = new MemoryStream())
|
{
|
workbook.Write(ms);
|
bt = ms.ToArray();
|
}
|
}
|
catch (Exception e)
|
{
|
bt = null;
|
// 记录日志
|
|
}
|
return bt;
|
}
|
|
|
}
|
}
|
