|
|
using System;
|
|
|
using System.Collections.Generic;
|
|
|
using System.Linq;
|
|
|
using System.Web;
|
|
|
using Estsh.Web.Util;
|
|
|
using System.Data;
|
|
|
using Estsh.Web.Dal;
|
|
|
using System.Collections;
|
|
|
using System.IO;
|
|
|
using NPOI.HSSF.UserModel;
|
|
|
using Com.Estsh.MES.App;
|
|
|
using System.Text;
|
|
|
using Aspose.Cells;
|
|
|
using DbCommon;
|
|
|
|
|
|
/***************************************************************************************************
|
|
|
*
|
|
|
* 作者：王勇
|
|
|
*
|
|
|
* *************************************************************************************************/
|
|
|
namespace Estsh.Core.Services
|
|
|
{
|
|
|
/// <summary>
|
|
|
///
|
|
|
/// </summary>
|
|
|
public class VendorOrderPrintService
|
|
|
{
|
|
|
private VendorOrderPrintDal dal = new VendorOrderPrintDal(RemotingProxyProvider._remotingProxy);
|
|
|
private int PRE_PAGE_ITEMS = 6;
|
|
|
|
|
|
public Hashtable getStockVendorOrderPrintByPage(Pager pager, String direction, string str)
|
|
|
{
|
|
|
Hashtable result = new Hashtable();
|
|
|
result = dal.getStockVendorOrderPrintByPage(pager.pageSize, pager.pageNo, str);
|
|
|
return result;
|
|
|
}
|
|
|
|
|
|
public Hashtable getStockOrderDetailByPage(Pager pager, String direction, string str)
|
|
|
{
|
|
|
Hashtable result = new Hashtable();
|
|
|
result = dal.getStockOrderDetailByPage(pager.pageSize, pager.pageNo, str);
|
|
|
return result;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
///
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public bool changeOrderStatus(string PoNo_Data, string PartNo_Data, string TIME, string ztxm, string evrtp)
|
|
|
{
|
|
|
Hashtable result = new Hashtable();
|
|
|
|
|
|
bool boolResult = dal.changeOrderStatus(ztxm);
|
|
|
|
|
|
TIME = TIME.Substring(0, TIME.Length - 1);
|
|
|
TIME = Convert.ToDateTime(TIME).ToString("yyyyMMdd");
|
|
|
|
|
|
bool selectResult = dal.selectPrintState(PoNo_Data, PartNo_Data, TIME, evrtp);
|
|
|
if (selectResult == false)
|
|
|
{
|
|
|
dal.updateDspoSetStaflg(PoNo_Data, PartNo_Data, TIME, evrtp);
|
|
|
}
|
|
|
|
|
|
return boolResult;
|
|
|
}
|
|
|
|
|
|
|
|
|
public bool changeOrderStatus(string EBELN)
|
|
|
{
|
|
|
Hashtable result = new Hashtable();
|
|
|
|
|
|
bool boolResult = dal.updateDspoSetStaflg(EBELN);
|
|
|
|
|
|
return boolResult;
|
|
|
}
|
|
|
/// <summary>
|
|
|
/// 获取工厂
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public ArrayList GetFactory()
|
|
|
{
|
|
|
Hashtable result = new Hashtable();
|
|
|
DataTable dt = dal.GetFactory();
|
|
|
return DataTypeConvert.NewObject.DataTableToArrayList(dt);
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取供应商
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public ArrayList GetVendor()
|
|
|
{
|
|
|
Hashtable result = new Hashtable();
|
|
|
DataTable dt = dal.GetVendor();
|
|
|
return DataTypeConvert.NewObject.DataTableToArrayList(dt);
|
|
|
}
|
|
|
|
|
|
public DataTable updatePrintData(string ebeln)
|
|
|
{
|
|
|
DataTable dt = new DataTable();
|
|
|
dt = this.dal.updatePrintData(ebeln);
|
|
|
|
|
|
return dt;
|
|
|
}
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
|
/// 订单确认
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public bool dingDanQueRen(string ebeln)
|
|
|
{
|
|
|
Hashtable result = new Hashtable();
|
|
|
bool bool_1 = dal.dingDanQueRen(ebeln);
|
|
|
return bool_1;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 发运确认
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public bool shipQueRen(string ebeln)
|
|
|
{
|
|
|
Hashtable result = new Hashtable();
|
|
|
bool bool_1 = dal.shipQueRen(ebeln);
|
|
|
return bool_1;
|
|
|
}
|
|
|
|
|
|
public Hashtable exportBarcodeInfo(string excelTemplate, string folder, string weekNo)
|
|
|
{
|
|
|
Hashtable result = new Hashtable();
|
|
|
//判断条码是否生成
|
|
|
//if (!dal.IsBuildCode(weekNo))
|
|
|
//{
|
|
|
// result.Add("message", weekNo + ":该订单的条码未生成，请先生成条码！");
|
|
|
// result.Add("flag", "error");
|
|
|
// return result;
|
|
|
//}
|
|
|
|
|
|
DataTable dt = dal.GetCartonNo(weekNo);
|
|
|
|
|
|
//if (dt == null || dt.Rows.Count < 1)
|
|
|
//{
|
|
|
// result.Add("message", "没有需要导出的条码数据，请确认！");
|
|
|
// result.Add("flag", "error");
|
|
|
// return result;
|
|
|
//}
|
|
|
|
|
|
////判断条码是否已导出
|
|
|
//if (dal.GetDspoYN(weekNo))
|
|
|
//{
|
|
|
// //未导出,第一次打导出标志
|
|
|
// dal.UpdateData(weekNo);
|
|
|
//}
|
|
|
string saveFolder = folder;
|
|
|
//if (!Directory.Exists(saveFolder))
|
|
|
//{
|
|
|
// Directory.CreateDirectory(saveFolder);
|
|
|
//}
|
|
|
|
|
|
string str = Export(excelTemplate, saveFolder, weekNo);
|
|
|
if (!(string.IsNullOrEmpty(str)))
|
|
|
{
|
|
|
String zipFile = folder + weekNo + "-" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".zip";
|
|
|
result.Add("fileName", str);
|
|
|
result.Add("message", "导出数据成功！");
|
|
|
result.Add("flag", "OK");
|
|
|
return result;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
result.Add("message", "导出数据异常，请重试！");
|
|
|
result.Add("flag", "error");
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public string Export(string excelTemplate, string saveFolder, String weekNo)
|
|
|
{
|
|
|
DataTable dt = dal.GetCartonNo(weekNo);
|
|
|
|
|
|
string str = "";
|
|
|
|
|
|
try
|
|
|
{
|
|
|
//创建数据集，用来存放选中的数据
|
|
|
DataTable dtRePrintSome = new DataTable("dtData");
|
|
|
dtRePrintSome.Columns.Add("customer", Type.GetType("System.String")); //客户名称
|
|
|
dtRePrintSome.Columns.Add("VendorName", Type.GetType("System.String")); //供应商名称
|
|
|
dtRePrintSome.Columns.Add("ZTXM", Type.GetType("System.String")); //箱条码
|
|
|
dtRePrintSome.Columns.Add("MATNR", Type.GetType("System.String")); //零件号
|
|
|
dtRePrintSome.Columns.Add("MAKTX", Type.GetType("System.String")); //零件描述
|
|
|
dtRePrintSome.Columns.Add("ZDEV_NUM", Type.GetType("System.String")); //数量
|
|
|
dtRePrintSome.Columns.Add("ModelName", Type.GetType("System.String")); //项目名称
|
|
|
dtRePrintSome.Columns.Add("ProdDate", Type.GetType("System.String")); //生产日期
|
|
|
dtRePrintSome.Columns.Add("LotNo", Type.GetType("System.String")); //送货批次
|
|
|
dtRePrintSome.Columns.Add("SEQ", Type.GetType("System.String")); //标签序号
|
|
|
dtRePrintSome.Columns.Add("subEBELN", Type.GetType("System.String")); //订单号
|
|
|
|
|
|
string factoryName = dal.GetFactoryName();//获取客户名称
|
|
|
|
|
|
|
|
|
//初始化获取第一个单号
|
|
|
string _orderNo = dt.Rows[0]["order_no"].ToString();
|
|
|
string _vendorNname = dt.Rows[0]["vendor_name"].ToString();
|
|
|
string _vendor_code = dt.Rows[0]["vendor_code"].ToString();
|
|
|
int _CodeCount = 0;
|
|
|
|
|
|
int j = 0;
|
|
|
|
|
|
for (int i = 0; i < dt.Rows.Count; i++)
|
|
|
{
|
|
|
if (i == 0)
|
|
|
{
|
|
|
_CodeCount = dal.GetWmsCodeCount(_orderNo, dt.Rows[i]["part_no"].ToString(), dt.Rows[i]["evrtp"].ToString());
|
|
|
j = 0;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
if (_orderNo != dt.Rows[i]["order_no"].ToString() || dt.Rows[i]["part_no"].ToString() != dt.Rows[i - 1]["part_no"].ToString() || dt.Rows[i]["evrtp"].ToString() != dt.Rows[i - 1]["evrtp"].ToString())
|
|
|
{
|
|
|
_CodeCount = dal.GetWmsCodeCount(dt.Rows[i]["order_no"].ToString(), dt.Rows[i]["part_no"].ToString(), dt.Rows[i]["evrtp"].ToString());
|
|
|
j = 0;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
if (_orderNo == dt.Rows[i]["order_no"].ToString()) //判断是否与上条数据为同一单号
|
|
|
{
|
|
|
string orderNo = _orderNo + "_" + dt.Rows[j]["version"].ToString();
|
|
|
j++;
|
|
|
string seq = j.ToString() + "/" + _CodeCount.ToString();
|
|
|
//将选中的数据存放到数据集中
|
|
|
dtRePrintSome.Rows.Add(new Object[] { factoryName, dt.Rows[i]["vendor_name"].ToString(),
|
|
|
dt.Rows[i]["carton_no"].ToString(), dt.Rows[i]["part_no"].ToString(),
|
|
|
dt.Rows[i]["part_spec"].ToString(), Convert.ToDecimal(dt.Rows[i]["zdev_num"].ToString()).ToString ("0.###"),
|
|
|
dt.Rows[i]["option3"].ToString(),string.Empty,dt.Rows[i]["zdate"].ToString().Replace("-",""),
|
|
|
seq, orderNo});
|
|
|
}
|
|
|
|
|
|
else //新的单号，导出前table，重新赋值
|
|
|
{
|
|
|
//导出前table
|
|
|
string fileName = saveFolder + _orderNo + "-" + _vendor_code + "-" + _vendorNname + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx";
|
|
|
ExportSave(dtRePrintSome, fileName, dtRePrintSome.Rows.Count, _orderNo, excelTemplate);
|
|
|
|
|
|
//重新初始化
|
|
|
_orderNo = dt.Rows[i]["order_no"].ToString();
|
|
|
_vendorNname = dt.Rows[i]["vendor_name"].ToString();
|
|
|
_vendor_code = dt.Rows[i]["vendor_code"].ToString();
|
|
|
_CodeCount = dal.GetWmsCodeCount(dt.Rows[i]["order_no"].ToString(), dt.Rows[i]["part_no"].ToString(), dt.Rows[i]["evrtp"].ToString());
|
|
|
j = 1;
|
|
|
|
|
|
//删除所有行
|
|
|
dtRePrintSome.Rows.Clear();
|
|
|
|
|
|
string orderNo = _orderNo + "_" + dt.Rows[j]["version"].ToString();
|
|
|
|
|
|
string seq = j.ToString() + "/" + _CodeCount.ToString();
|
|
|
//将选中的数据存放到数据集中
|
|
|
dtRePrintSome.Rows.Add(new Object[] { factoryName, dt.Rows[i]["vendor_name"].ToString(),
|
|
|
dt.Rows[i]["carton_no"].ToString(), dt.Rows[i]["part_no"].ToString(),
|
|
|
dt.Rows[i]["part_spec"].ToString(), Convert.ToDecimal(dt.Rows[i]["zdev_num"].ToString()).ToString ("0.###"),
|
|
|
dt.Rows[i]["option3"].ToString(),string.Empty,dt.Rows[i]["zdate"].ToString().Replace("-",""),
|
|
|
seq, orderNo});
|
|
|
}
|
|
|
|
|
|
//判断是否为最后一条数据
|
|
|
if (i == dt.Rows.Count - 1)
|
|
|
{
|
|
|
//导出当前table
|
|
|
string strName = _orderNo + "-" + _vendor_code + "-" + _vendorNname + "-" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx";
|
|
|
string fileName = saveFolder + strName;
|
|
|
ExportSave(dtRePrintSome, fileName, dtRePrintSome.Rows.Count, _orderNo, excelTemplate);
|
|
|
str = strName;
|
|
|
}
|
|
|
|
|
|
}
|
|
|
return str;
|
|
|
}
|
|
|
catch (System.Exception ex)
|
|
|
{
|
|
|
//return false;
|
|
|
return "";
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
public void ExportSave(DataTable table, string FileName, int jCount, string orderNo, string excelTemplate)
|
|
|
{
|
|
|
int sheetIndex = 0;
|
|
|
int cellIndex = 0;
|
|
|
WorkbookDesigner designer = new WorkbookDesigner();
|
|
|
designer.Open(excelTemplate);
|
|
|
|
|
|
//获取汇总明细
|
|
|
//DataSet ds = dal.GetExpInfo(orderNo);
|
|
|
|
|
|
//DataTable dtHead = ds.Tables[2];
|
|
|
|
|
|
|
|
|
//designer.SetDataSource("recymd", ds.Tables[0].Rows[0]["recymd"] is DBNull ? "" : ds.Tables[0].Rows[0]["recymd"].ToString());
|
|
|
//designer.SetDataSource("vendorCode", ds.Tables[0].Rows[0]["lifnr"] is DBNull ? "" : ds.Tables[0].Rows[0]["lifnr"].ToString());
|
|
|
//designer.SetDataSource("vendorSale", ds.Tables[0].Rows[0]["vendor_sale"] is DBNull ? "" : ds.Tables[0].Rows[0]["vendor_sale"].ToString());
|
|
|
//designer.SetDataSource("vendorTel", ds.Tables[0].Rows[0]["vendor_tel"] is DBNull ? "" : ds.Tables[0].Rows[0]["vendor_tel"].ToString());
|
|
|
//designer.SetDataSource("vendorFax", ds.Tables[0].Rows[0]["vendor_fax"] is DBNull ? "" : ds.Tables[0].Rows[0]["vendor_fax"].ToString());
|
|
|
//designer.SetDataSource("warehouseDesc", ds.Tables[0].Rows[0]["lgort"] is DBNull ? "" : ds.Tables[0].Rows[0]["lgort"].ToString());
|
|
|
//designer.SetDataSource("emp_name", ds.Tables[0].Rows[0]["emp_name"] is DBNull ? "" : ds.Tables[0].Rows[0]["emp_name"].ToString());
|
|
|
|
|
|
////填写订单号
|
|
|
//designer.Workbook.Worksheets[0].Cells[4, 10].PutValue(orderNo);
|
|
|
|
|
|
//int BeginRow = 24;
|
|
|
|
|
|
|
|
|
////填写时间
|
|
|
//DataTable dtDate = dal.GetStartDate(orderNo);
|
|
|
|
|
|
//for (int i = 0; i < dtDate.Rows.Count; i++)
|
|
|
//{
|
|
|
// dtHead.Columns.Add(new DataColumn(dtDate.Rows[i]["InDate"].ToString(), typeof(string)));
|
|
|
// designer.Workbook.Worksheets[0].Cells[23, 2 + i].PutValue(dtDate.Rows[i]["InDate"].ToString());
|
|
|
//}
|
|
|
|
|
|
////插入行
|
|
|
//if (dtHead.Rows.Count > 1)
|
|
|
//{
|
|
|
// designer.Workbook.Worksheets[0].Cells.InsertRows(BeginRow, dtHead.Rows.Count - 1);
|
|
|
//}
|
|
|
|
|
|
////填写零件号
|
|
|
//for (int i = 0; i < dtHead.Rows.Count; i++)
|
|
|
//{
|
|
|
// designer.Workbook.Worksheets[0].Cells[i + BeginRow, 0].PutValue(dtHead.Rows[i]["matnr"].ToString());
|
|
|
// designer.Workbook.Worksheets[0].Cells[i + BeginRow, 1].PutValue(dtHead.Rows[i]["part_spec"].ToString());
|
|
|
//}
|
|
|
|
|
|
////填写数量
|
|
|
//DataTable dtDetail = ds.Tables[1];
|
|
|
|
|
|
//for (int i = 0; i < dtDetail.Rows.Count; i++)
|
|
|
//{
|
|
|
|
|
|
// for (int q = 0; q < dtHead.Rows.Count; q++)
|
|
|
// {
|
|
|
// if (dtDetail.Rows[i]["PartNo"].ToString() == dtHead.Rows[q]["matnr"].ToString())
|
|
|
// {
|
|
|
// for (int j = 0; j < dtHead.Columns.Count; j++)
|
|
|
// {
|
|
|
// if (dtDetail.Rows[i]["InDate"].ToString() == dtHead.Columns[j].ColumnName.ToString())
|
|
|
// {
|
|
|
// decimal dQty = Convert.ToDecimal(dtDetail.Rows[i]["Qty"].ToString());
|
|
|
// int sQty = Convert.ToInt32(dQty);
|
|
|
// designer.Workbook.Worksheets[0].Cells[BeginRow + q, j].PutValue(sQty.ToString());
|
|
|
// }
|
|
|
// }
|
|
|
// }
|
|
|
// }
|
|
|
//}
|
|
|
|
|
|
//designer.Process(0, true);
|
|
|
|
|
|
|
|
|
//sheet2写入条码明细
|
|
|
DataTable dtCode = dal.GetCodeDetail(orderNo);
|
|
|
for (int i = 0; i < dtCode.Rows.Count; i++)
|
|
|
{
|
|
|
designer.Workbook.Worksheets[0].Cells[i + 1, 0].PutValue(dtCode.Rows[i]["factory_name"].ToString());
|
|
|
designer.Workbook.Worksheets[0].Cells[i + 1, 1].PutValue(dtCode.Rows[i]["vendor_name"].ToString());
|
|
|
designer.Workbook.Worksheets[0].Cells[i + 1, 2].PutValue(dtCode.Rows[i]["ZTXM"].ToString());
|
|
|
designer.Workbook.Worksheets[0].Cells[i + 1, 3].PutValue(dtCode.Rows[i]["MATNR"].ToString());
|
|
|
designer.Workbook.Worksheets[0].Cells[i + 1, 4].PutValue(dtCode.Rows[i]["part_spec"].ToString());
|
|
|
designer.Workbook.Worksheets[0].Cells[i + 1, 5].PutValue(dtCode.Rows[i]["ZDEV_NUM"].ToString());
|
|
|
designer.Workbook.Worksheets[0].Cells[i + 1, 6].PutValue(dtCode.Rows[i]["option3"].ToString());
|
|
|
designer.Workbook.Worksheets[0].Cells[i + 1, 7].PutValue(dtCode.Rows[i]["ZDATE"].ToString());
|
|
|
designer.Workbook.Worksheets[0].Cells[i + 1, 8].PutValue(dtCode.Rows[i]["seq"].ToString());
|
|
|
designer.Workbook.Worksheets[0].Cells[i + 1, 9].PutValue(dtCode.Rows[i]["order_no"].ToString());
|
|
|
}
|
|
|
designer.Process(0, true);
|
|
|
|
|
|
//写入箱条码
|
|
|
for (int rowIndex = 0; rowIndex < jCount; rowIndex++)
|
|
|
{
|
|
|
cellIndex = (rowIndex % PRE_PAGE_ITEMS) + 1;
|
|
|
sheetIndex = (rowIndex / PRE_PAGE_ITEMS) + 1;
|
|
|
|
|
|
|
|
|
if (0 == rowIndex % PRE_PAGE_ITEMS)
|
|
|
{
|
|
|
designer.Workbook.Worksheets.AddCopy(sheetIndex);
|
|
|
}
|
|
|
|
|
|
designer.SetDataSource("barcode" + cellIndex, "*" + table.Rows[rowIndex]["ZTXM"].ToString() + "*");
|
|
|
designer.SetDataSource("supplier" + cellIndex, table.Rows[rowIndex]["VendorName"].ToString());
|
|
|
designer.SetDataSource("customer" + cellIndex, table.Rows[rowIndex]["customer"].ToString());
|
|
|
designer.SetDataSource("partNo" + cellIndex, table.Rows[rowIndex]["MATNR"].ToString());
|
|
|
designer.SetDataSource("partSpec" + cellIndex, table.Rows[rowIndex]["MAKTX"].ToString());
|
|
|
designer.SetDataSource("qty" + cellIndex, table.Rows[rowIndex]["ZDEV_NUM"].ToString());
|
|
|
designer.SetDataSource("lotNo" + cellIndex, table.Rows[rowIndex]["LotNo"].ToString());
|
|
|
designer.SetDataSource("model" + cellIndex, table.Rows[rowIndex]["ModelName"].ToString());
|
|
|
designer.SetDataSource("serialNo" + cellIndex, table.Rows[rowIndex]["SEQ"].ToString());
|
|
|
designer.SetDataSource("prodDate" + cellIndex, "" + table.Rows[rowIndex]["ProdDate"].ToString() + "");
|
|
|
designer.SetDataSource("poNo" + cellIndex, table.Rows[rowIndex]["subEBELN"].ToString());
|
|
|
|
|
|
designer.Process(sheetIndex, true);
|
|
|
designer.ClearDataSource();
|
|
|
}
|
|
|
|
|
|
designer.Save(FileName, FileFormatType.Excel2007Xlsx);
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
}
|
|
|
} |