public ActionResult ExportTable(DateTime?timestart, DateTime?timeend, string DepartmentId, string CostItemCode)
{
if (Session["Power"] == null || Session["UserCode"] == null)
{
return(null);
}
else
{
string UserCode = Session["UserCode"].ToString(); //员工编号
string Power = Session["Power"].ToString(); //权限字符串
var list = CostTotalService.LoadEntities(a => Power.Contains(a.DepartmentId));
if (CostItemCode != null && CostItemCode != "")
{
list = list.Where(a => a.CostItemCode == CostItemCode);
}
if (timestart != null)
{
list = list.Where(a => a.BillDate >= timestart);
}
if (timeend != null)
{
list = list.Where(a => a.BillDate < timeend.GetValueOrDefault().AddDays(1));
}
//生成Datatable
DataTable dt = new DataTable();
dt.Columns.Add("单据类型");
dt.Columns.Add("单据编码");
dt.Columns.Add("单据日期");
dt.Columns.Add("制单人");
dt.Columns.Add("审核人");
dt.Columns.Add("费用编码");
dt.Columns.Add("费用项目");
dt.Columns.Add("金额");
dt.Columns.Add("备注");
foreach (var item in list)
{
DataRow dr = dt.NewRow();
dr["单据类型"] = item.billType;
dr["单据编码"] = item.BillCode;
dr["单据日期"] = item.BillDate;
dr["制单人"] = item.MakePerson;
dr["审核人"] = item.ExaminePerson;
dr["费用编码"] = item.CostItemCode;
dr["费用项目"] = item.CostItemName;
dr["金额"] = item.AllAmount;
dr["备注"] = item.Remark;
dt.Rows.Add(dr);
}
MemoryStream file = NPIOHelper.RenderToMemory(dt, "sheet1");
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
Response.ContentType = "application / vnd.openxmlformats - officedocument.spreadsheetml.sheet";// "application/ms-excel/msword";
Response.AddHeader("Content-Disposition", "attachment;fileName=费用项目明细表.xls");
Response.BinaryWrite(file.ToArray());
return(new EmptyResult());
}
}
/// <summary>
/// 导出交货单
/// </summary>
/// <param name="billId"></param>
/// <returns></returns>
private MemoryStream ExportGiveBill(Guid billId, string TemplateUrl)
{
GiveBill bill = CurrentDBSession.GiveBillDal.LoadEntities(a => a.Id == billId).FirstOrDefault();
if (bill == null)
{
return(null);
}
//根据模板创建Datatable
DataTable dt = ExcelHelp.ExcelToDT(TemplateUrl);
foreach (var record in bill.Record)
{
DataRow dr = dt.NewRow();
dr["LBBillCode"] = bill.LBBillCode;
dr["LBBillDate"] = bill.LBBillDate;
dr["LBTaskBillCode"] = bill.LBTaskBillCode;
dr["LBLine"] = bill.LBLine;
dr["LBContacts"] = bill.LBContacts;
dr["LBPhone"] = bill.LBPhone;
dr["LBMailCode"] = bill.LBMailCode;
dr["LBCustomerCode"] = bill.LBCustomerCode;
dr["LBCustomerName"] = bill.LBCustomerName;
dr["LBSendAddress"] = bill.LBSendAddress;
dr["LBRemark"] = bill.LBRemark;
dr["CreateDate"] = bill.CreateDate;
dr["ChargePerson"] = bill.ChargePerson;
dr["MakePerson"] = bill.MakePerson;
dr["Warehouse"] = bill.Warehouse;
dr["WarehouseId"] = bill.WarehouseId;
dr["LoadGoodsType"] = bill.LoadGoodsType;
dr["OutputType"] = bill.OutputType;
dr["OutputTypeId"] = bill.OutputTypeId;
dr["BusinessType"] = bill.BusinessType;
dr["LineWay"] = bill.LineWay;
dr["Remark"] = bill.Remark;
dr["Department"] = bill.Department;
dr["DepartmentId"] = bill.DepartmentId;
dr["Company"] = bill.Company;
dr["CompanyId"] = bill.CompanyId;
//子表
dr["ItemCode"] = record.ItemCode;
dr["ItemLocationId"] = record.ItemLocationId;
dr["ItemBatch"] = record.ItemBatch;
dr["Count"] = record.Count;
dt.Rows.Add(dr);
}
return(NPIOHelper.RenderToMemory(dt, "sheet1"));
}
/// <summary>
/// 导出入库单
/// </summary>
/// <param name="billId"></param>
/// <param name="TemplateUrl"></param>
/// <returns></returns>
private MemoryStream ExportTransferBill(Guid billId, string TemplateUrl)
{
TransferBill bill = CurrentDBSession.TransferBillDal.LoadEntities(a => a.Id == billId).FirstOrDefault();
if (bill == null)
{
return(null);
}
DataTable dt = ExcelHelp.ExcelToDT(TemplateUrl);
foreach (var record in bill.Record)
{
DataRow dr = dt.NewRow();
dr["LBBillCode"] = bill.LBBillCode;
dr["LBBillDate"] = bill.LBBillDate;
dr["LBFrom"] = bill.LBFrom;
dr["LBMoveType"] = bill.LBMoveType;
dr["LBRemark"] = bill.LBRemark;
dr["MakePerson"] = bill.MakePerson;
dr["CreateDate"] = bill.CreateDate;
dr["ChargePerson"] = bill.ChargePerson;
dr["Warehouse"] = bill.Warehouse;
dr["WarehouseId"] = bill.WarehouseId;
dr["LoadGoodsType"] = bill.LoadGoodsType;
dr["InputType"] = bill.InputType;
dr["InputTypeId"] = bill.InputTypeId;
dr["BusinessType"] = bill.BusinessType;
dr["Remark"] = bill.Remark;
dr["Department"] = bill.Department;
dr["DepartmentId"] = bill.DepartmentId;
dr["Company"] = bill.Company;
dr["CompanyId"] = bill.CompanyId;
//子表
dr["ItemCode"] = record.ItemCode;
dr["ItemLocationId"] = record.ItemLocationId;
dr["ItemBatch"] = record.ItemBatch;
dr["Count"] = record.Count;
dt.Rows.Add(dr);
}
return(NPIOHelper.RenderToMemory(dt, "sheet1"));
}
/// <summary>
/// 导出Excel
/// </summary>
/// <returns></returns>
public ActionResult ExportExcel(string DepartmentId, string WarehouseId, string ItemLocationId, string ItemCode, string InOutTypeId, string BillTypes, string ItemBatch, string LBBillCode, string BillCode, string timestart, string timeend)
{
int?WarehouseId1 = null;
if (WarehouseId != "undefined")
{
WarehouseId1 = Convert.ToInt32(WarehouseId);
}
string[] temp = InOutTypeId.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries);//分割出入库类型id
int?[] InOutTypeIds = new int?[temp.Length];
for (int i = 0; i < temp.Length; i++)
{
InOutTypeIds[i] = Convert.ToInt32(temp[i]);
}
string[] BillTypes1 = BillTypes.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries); //分割单据类型
DateTime?timestart1 = null; //开始时间
DateTime?timeend1 = null; //结束时间
if (timestart != "")
{
timestart1 = Convert.ToDateTime(timestart);
}
if (timeend != "")
{
timeend1 = Convert.ToDateTime(timeend).AddDays(1);
}
if (ItemLocationId == "")
{
ItemLocationId = null;
}
if (ItemCode == "")
{
ItemCode = null;
}
if (ItemBatch == "")
{
ItemBatch = null;
}
if (LBBillCode == "")
{
LBBillCode = null;
}
if (BillCode == "")
{
BillCode = null;
}
//获取全部数据
var list = RecordService.ExportExcel(DepartmentId, WarehouseId1, ItemLocationId, ItemCode, InOutTypeIds, BillTypes1, ItemBatch, LBBillCode, BillCode, timestart1, timeend1);
//转换成DataTable
DataTable dt = new DataTable();
dt.Columns.Add("单据类型");
dt.Columns.Add("系统单号");
dt.Columns.Add("客户单号");
dt.Columns.Add("物料编码");
dt.Columns.Add("物料名称");
dt.Columns.Add("仓库名称");
dt.Columns.Add("货位编码");
dt.Columns.Add("批次");
dt.Columns.Add("数量");
dt.Columns.Add("出入库类型");
foreach (var item in list)
{
DataRow dr = dt.NewRow();
dr["单据类型"] = ChangeTableType(item.MainTableType);
dr["系统单号"] = item.BillCode;
dr["客户单号"] = item.LBBillCode;
dr["物料编码"] = item.ItemCode;
dr["物料名称"] = item.ItemName;
dr["仓库名称"] = item.Warehouse;
dr["货位编码"] = item.ItemLocationId;
dr["批次"] = item.ItemBatch;
dr["数量"] = item.Count;
dr["出入库类型"] = item.InOutTypeName;
dt.Rows.Add(dr);
}
MemoryStream file = NPIOHelper.RenderToMemory(dt, "sheet1");
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
Response.ContentType = "application / vnd.openxmlformats - officedocument.spreadsheetml.sheet";// "application/ms-excel/msword";
Response.AddHeader("Content-Disposition", "attachment;fileName=出入库记录.xls");
Response.BinaryWrite(file.ToArray());
return(new EmptyResult());
}
public ActionResult ExportTable(DateTime?timestart, DateTime?timeend, string DepartmentId)
{
if (Session["Power"] == null || Session["UserCode"] == null)
{
return(null);
}
else
{
string Power = Session["Power"].ToString();//权限字符串
//获取装卸费力资费明细
var list1 = LoadingAndLaborCostDetailService.LoadEntities(a => Power.Contains(a.DepartmentId)).ToList();
if (timestart != null)
{
list1 = list1.Where(a => a.ExaminaDate >= timestart).ToList();
}
if (timeend != null)
{
list1 = list1.Where(a => a.ExaminaDate < timeend.GetValueOrDefault().AddDays(1)).ToList();
}
//获取其他明细
var list2 = LoadingAndLaborCostDetailService.LoadEntities(a => Power.Contains(a.DepartmentId)).ToList();
if (timestart != null)
{
list2 = list2.Where(a => a.ExaminaDate >= timestart).ToList();
}
if (timeend != null)
{
list2 = list2.Where(a => a.ExaminaDate < timeend.GetValueOrDefault().AddDays(1)).ToList();
}
//合并list1 list2 提取有用字段 返回
var res = list1.Select(a => new { a.CostItemCode, a.CostItemName, a.AllAmount }).ToList();
var res2 = list2.Select(a => new { a.CostItemCode, a.CostItemName, a.AllAmount }).ToList();
//合并成一个list
res.AddRange(res2);
//分组求和
var temp = from a in res
group a by new { a.CostItemCode, a.CostItemName } into g
select new { g.Key.CostItemCode, g.Key.CostItemName, Price = g.Sum(a => a.AllAmount) };
var tempNew = temp.ToList().Select(a => new { a.CostItemCode, a.CostItemName, a.Price, Type = a.CostItemCode.Substring(0, 2) == "64" ? "成本" : "收入" });
//转换成DataTable
DataTable dt = new DataTable();
dt.Columns.Add("科目编码");
dt.Columns.Add("科目名称");
dt.Columns.Add("科目分类");
dt.Columns.Add("发生金额");
foreach (var item in tempNew)
{
DataRow dr = dt.NewRow();
dr["科目编码"] = item.CostItemCode;
dr["科目名称"] = item.CostItemName;
dr["科目分类"] = item.Type;
dr["发生金额"] = item.Price;
dt.Rows.Add(dr);
}
MemoryStream file = NPIOHelper.RenderToMemory(dt, "sheet1");
Response.Charset = "UTF-8";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
Response.ContentType = "application / vnd.openxmlformats - officedocument.spreadsheetml.sheet";// "application/ms-excel/msword";
Response.AddHeader("Content-Disposition", "attachment;fileName=费用项目汇总表.xls");
Response.BinaryWrite(file.ToArray());
return(new EmptyResult());
}
}