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); }