protected void Page_Load(object sender, EventArgs e)
{
Ajax.Utility.RegisterTypeForAjax(typeof(BOLHistDetail));
if (!IsPostBack)
{
lblSuccessMessage.Text = "Enter BOL Number";
BOLNumberBox.Focus();
// if the page is called from GERFilter page
if (Request.QueryString["BOLNo"] != null &&
Request.QueryString["BOLNo"].ToString() != "")
{
lblErrorMessage.Text = "";
BOLNumberBox.Text = Request.QueryString["BOLNo"].ToString();
if (BindBOLData(BOLNumberBox.Text))
{
BOLDetail.Focus();
}
lblSuccessMessage.Text = "";
}
}
if (IsPostBack)
{
lblErrorMessage.Text = "";
if (BindBOLData(BOLNumberBox.Text))
{
BOLDetail.Focus();
}
lblSuccessMessage.Text = "";
}
}
public Boolean BindBOLData(string BOLNumber)
{
DataSet dsGER = new DataSet();
DataSet dsBOLDetail = new DataSet();
string ColumnNames = "*, convert(varchar,RcptQty) + BaseUOM as UMQty, UOMatlAmt/RcptQty as MatlCost";
ColumnNames += ", UODutyAmt/RcptQty as DutyPerUnit";
ColumnNames += ", UOOceanFrghtAmt/RcptQty as OceanPerUnit";
ColumnNames += ", UOBrokerageAmt/RcptQty as BrokerPerUnit";
ColumnNames += ", UODrayAmt/RcptQty as DrayPerUnit";
ColumnNames += ", (UOMerchProcFee+UOHarborMaintFee)/RcptQty as HarborPerUnit";
ColumnNames += ", UOMiscFeeAmt/RcptQty as MiscPerUnit";
ColumnNames += ", UOTrkFrghtAmt/RcptQty as TruckPerUnit";
ColumnNames += ", (1000*UOMatlAmt/PcsPerAlt)/RcptQty as MatlK";
dsGER = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["ReportsConnectionString"].ToString(), "UGEN_SP_Select",
new SqlParameter("@tableName", "GERHeaderHist"),
new SqlParameter("@columnNames", "*"),
new SqlParameter("@whereClause", "BOLNo='" + BOLNumber + "'"));
// get the detail data
dsBOLDetail = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["ReportsConnectionString"].ToString(), "UGEN_SP_Select",
new SqlParameter("@tableName", "GERDetailHist"),
new SqlParameter("@columnNames", ColumnNames),
new SqlParameter("@whereClause", "BOLNo='" + BOLNumber + "'"));
if (dsGER.Tables[0] != null)
{
dtBOLData = dsGER.Tables[0];
BOLHeaderLeft.DataSource = dtBOLData;
BOLHeaderLeft.DataBind();
BOLHeaderCenter.DataSource = dtBOLData;
BOLHeaderCenter.DataBind();
BOLHeaderRight.DataSource = dtBOLData;
BOLHeaderRight.DataBind();
if (dtBOLData.Rows.Count == 0)
{
lblErrorMessage.Text = "BOL Number not on file";
return(false);
}
else
{
//
// Assign the BOL number in header[Used to print the report]
//
lblBOLNumber.Text = dtBOLData.Rows[0]["BOLNo"].ToString();
dtBOLDetail = dsBOLDetail.Tables[0];
BOLDetail.DataSource = dtBOLDetail;
BOLDetail.DataBind();
//PrintButton.Visible = true;
return(true);
}
}
else
{
lblErrorMessage.Text = "BOL Number not on file";
return(false);
}
}
public Boolean BindBOLData(string BOLNumber)
{
DataSet dsGER = new DataSet();
DataSet dsBOLDetail = new DataSet();
string ColumnNames = "";
dsGER = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["ReportsConnectionString"].ToString(), "UGEN_SP_Select",
new SqlParameter("@tableName", "GERHeaderHist"),
new SqlParameter("@columnNames", "*"),
new SqlParameter("@whereClause", "BOLNo='" + BOLNumber + "'"));
// get the detail data
dsBOLDetail = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["ReportsConnectionString"].ToString(), "UGEN_SP_Select",
new SqlParameter("@tableName", "GERDetailHist"),
new SqlParameter("@columnNames", "*"),
new SqlParameter("@whereClause", "BOLNo='" + BOLNumber + "'"));
if (dsGER.Tables[0] != null)
{
dtBOLData = dsGER.Tables[0];
BOLHeaderLeft.DataSource = dtBOLData;
BOLHeaderLeft.DataBind();
BOLHeaderCenter.DataSource = dtBOLData;
BOLHeaderCenter.DataBind();
BOLHeaderRight.DataSource = dtBOLData;
BOLHeaderRight.DataBind();
if (dtBOLData.Rows.Count == 0)
{
lblErrorMessage.Text = "BOL Number not on file";
return(false);
}
else
{
//
// Assign the BOL number in header[Used to print the report]
//
lblBOLNumber.Text = dtBOLData.Rows[0]["BOLNo"].ToString();
dtBOLDetail = dsBOLDetail.Tables[0];
BOLDetail.DataSource = dtBOLDetail;
BOLDetail.DataBind();
//PrintButton.Visible = true;
return(true);
}
}
else
{
lblErrorMessage.Text = "BOL Number not on file";
return(false);
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
lblSuccessMessage.Text = "Enter BOL Number";
BOLNumberBox.Focus();
}
if (IsPostBack)
{
lblErrorMessage.Text = "";
if (BindBOLData(BOLNumberBox.Text))
{
BOLDetail.Focus();
}
lblSuccessMessage.Text = "";
}
}
public void Sort_Grid(Object sender, DataGridSortCommandEventArgs e)
{
// Retrieve the data source from session state.
//DataTable dt = (DataTable)Session["Source"];
// Create a DataView from the DataTable.
DataView dv = new DataView(dtBOLDetail);
// The DataView provides an easy way to sort. Simply set the
// Sort property with the name of the field to sort by.
dv.Sort = e.SortExpression;
// Re-bind the data source and specify that it should be sorted
// by the field specified in the SortExpression property.
BOLDetail.DataSource = dv;
BOLDetail.DataBind();
}
//生成Excel版本的BOL并返回完整路径
public string GenerateExcelBol(int referenceId, string orderType, IList <FBABOLDetail> bolDetailList, string freightCharge, BOLDetail bolDetail)
{
var orderNumber = "N/A";
var address = " ";
_ws = _wb.Worksheets[1];
//设置BOL时间
_ws.Cells[2, 1] = "Date: " + DateTime.Now.ToString("yyyy-MM-dd");
if (orderType == FBAOrderType.ShipOrder)
{
var orderInDb = _context.FBAShipOrders.Find(referenceId);
var addressBookInDb = _context.FBAAddressBooks.SingleOrDefault(x => x.WarehouseCode == orderInDb.Destination);
var warehouseLocationInDb = _context.WarehouseLocations.SingleOrDefault(x => x.WarehouseCode == orderInDb.WarehouseLocation);
if (addressBookInDb != null)
{
address = addressBookInDb.Address;
}
// 设置BOL提货地址
_ws.Cells[4, 1] = "Grand Channel Inc. " + orderInDb.WarehouseLocation;
_ws.Cells[5, 1] = warehouseLocationInDb.Address;
//设置BOL#
_ws.Cells[3, 6] = orderInDb.BOLNumber;
//设置地址
_ws.Cells[7, 2] = orderInDb.Destination;
_ws.Cells[8, 1] = address;
//设置carrier
_ws.Cells[6, 6] = orderInDb.Carrier;
orderNumber = orderInDb.ShipOrderNumber;
//设置Ship Order #
_ws.Cells[18, 1] = "Ship Order#: " + orderNumber;
}
else
{
var orderInDb = _context.FBAMasterOrders.Find(referenceId);
var warehouseLocationInDb = _context.WarehouseLocations.SingleOrDefault(x => x.WarehouseCode == orderInDb.WarehouseLocation);
// 设置BOL提货地址
_ws.Cells[4, 1] = "Grand Channel Inc. " + orderInDb.WarehouseLocation;
_ws.Cells[5, 1] = warehouseLocationInDb.Address;
//设置BOL#
_ws.Cells[3, 6] = bolDetail.BOLNumber;
//设置地址
_ws.Cells[7, 2] = bolDetail.WarehouseCode;
_ws.Cells[8, 1] = bolDetail.Address;
//设置carrier
_ws.Cells[6, 6] = bolDetail.Carrier;
orderNumber = bolDetail.BOLNumber;
//设置Ship Order #
_ws.Cells[18, 1] = "Ship Order#: " + orderNumber;
}
//设置Freight Charge
if (freightCharge == "Prepaid")
{
_ws.Cells[16, 4] = "Prepaid ☑ Collect ☐ 3rd Party ☐";
}
else if (freightCharge == "Collect")
{
_ws.Cells[16, 4] = "Prepaid ☐ Collect ☑ 3rd Party ☐";
}
else if (freightCharge == "3rd Party")
{
_ws.Cells[16, 4] = "Prepaid ☐ Collect ☐ 3rd Party ☑";
}
else
{
_ws.Cells[16, 4] = "Prepaid ☐ Collect ☐ 3rd Party ☐";
}
var startRow = 21;
var mergeStartRow = 21;
var mergeEndRow = 21;
var mergeRange = _ws.get_Range("G" + mergeStartRow, "G" + mergeEndRow);
foreach (var b in bolDetailList)
{
_ws.Cells[startRow, 1] = b.CustomerOrderNumber;
_ws.Cells[startRow, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
_ws.Cells[startRow, 2] = b.Contianer;
_ws.Cells[startRow, 3] = b.AmzRef;
_ws.Cells[startRow, 5] = Math.Round(b.Weight, 2);
_ws.Cells[startRow, 6] = b.CartonQuantity;
//_ws.Cells[startRow, 7] = b.IsMainItem ? b.PalletQuantity.ToString() : " ";
//_ws.Cells[startRow, 8] = b.Location;
if (b.IsMainItem)
{
mergeRange = _ws.get_Range("G" + mergeStartRow, "G" + mergeEndRow);
mergeRange.Merge(mergeRange.MergeCells);
_ws.Cells[startRow, 7] = b.ActualPallets.ToString();
mergeStartRow = startRow;
mergeEndRow = startRow;
}
else
{
mergeEndRow += 1;
}
startRow += 1;
}
mergeRange = _ws.get_Range("G" + mergeStartRow, "G" + mergeEndRow);
mergeRange.Merge(mergeRange.MergeCells);
var lastRow = startRow + 2;
if (lastRow < 37)
{
lastRow = 37;
}
_ws.Cells[lastRow, 1] = "Total";
_ws.Cells[lastRow, 6] = bolDetailList.Sum(x => x.CartonQuantity);
_ws.Cells[lastRow, 7] = bolDetailList.Sum(x => x.ActualPallets);
if (orderType == FBAOrderType.MasterOrder)
{
_ws.Cells[lastRow, 7] = bolDetail.PltQuantity;
_ws.Cells[21, 7] = bolDetail.PltQuantity;
}
//for(int i = 21; i <= lastRow; i++)
//{
// for(int j = 1; j <= 7; j++)
// {
// _ws.Cells[i, j].HorizontalAlignment = XlHAlign.xlHAlignCenter;
// _ws.Cells[i, j].HorizontalAlignment = XlVAlign.xlVAlignCenter;
// }
//}
var range = _ws.get_Range("A20", "G50");
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.HorizontalAlignment = XlVAlign.xlVAlignCenter;
range.VerticalAlignment = XlHAlign.xlHAlignCenter;
range.VerticalAlignment = XlVAlign.xlVAlignCenter;
range.WrapText = true;
var fullPath = @"D:\BOL\FBA-BOL-" + orderNumber + "-" + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ".xlsx";
_wb.SaveAs(fullPath, Type.Missing, "", "", Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, 1, false, Type.Missing, Type.Missing, Type.Missing);
_excel.Quit();
return(fullPath);
}
