private void btn_Search_Click(object sender, EventArgs e) { OrderSelected = -1; dtgv_BuyerInfor.Columns.Clear(); Database.ERPSOFT.t_TL_BuyerInfor BuyerInfor = new Database.ERPSOFT.t_TL_BuyerInfor(); DataTable dtData = BuyerInfor.GetdatabyTextSearch(txt_BuyerSearch.Text.Trim()); dtgv_BuyerInfor.DataSource = dtData; DataGridViewCheckBoxColumn checkBoxCell = new DataGridViewCheckBoxColumn(); checkBoxCell.Name = "checkbox"; checkBoxCell.HeaderText = "Select"; checkBoxCell.DisplayIndex = 0; dtgv_BuyerInfor.Columns.Add(checkBoxCell); }
public bool ExportCustomsDecalarationsGroupByProduct(string PathSave, DataTable dtexport, List <SummaryDelivery> summaries, string buyerCode, string shipmentCode) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; Excel.Worksheet xlWorkSheet2; Excel.Worksheet xlWorkSheet3; object misValue = System.Reflection.Missing.Value; try { xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Open(PathFormExportFGs, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); #region Sales Invoice xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); string strWorksheetName = xlWorkSheet.Name; xlWorkSheet.Cells[11, "K"] = dtexport.Rows[0]["Invoice"]; xlWorkSheet.Cells[12, "K"] = DateTime.Now.ToString("dd-MM-yyyy"); int TotalMoney = 0; double douTotalMonney = 0; //Thong tin buyer Database.ERPSOFT.t_TL_BuyerInfor t_TL_BuyerInfor = new Database.ERPSOFT.t_TL_BuyerInfor(); DataTable dtBuyer = t_TL_BuyerInfor.GetdatabyTextSearch(buyerCode); Database.ERPSOFT.t_TL_Shipment t_TL_Shipment = new Database.ERPSOFT.t_TL_Shipment(); DataTable dtShipment = t_TL_Shipment.GetdatabyTextSearch(shipmentCode); xlWorkSheet.Cells[11, "C"] = dtBuyer.Rows[0]["Buyer_Infor"].ToString(); xlWorkSheet.Cells[91, "B"] = dtBuyer.Rows[0]["Buyer_Consignee"].ToString(); //Thong tin transportation for (int i = 0; i < summaries.Count; i++) { xlWorkSheet.Cells[23 + i, "A"] = (i + 1).ToString("0000"); // xlWorkSheet.Cells[23 + i, "C"] = dtexport.Rows[i]["Product"]; xlWorkSheet.Cells[23 + i, "D"] = summaries[i].Product; xlWorkSheet.Cells[23 + i, "G"] = summaries[i].TotalQuantity; xlWorkSheet.Cells[23 + i, "H"] = summaries[i].Unit; if (summaries[i].Currency.Trim() == "USD") { xlWorkSheet.Cells[23 + i, "I"] = "$ " + summaries[i].PriceUnit; } else if (summaries[i].Currency.Trim() == "EUR") { xlWorkSheet.Cells[23 + i, "I"] = "€ " + summaries[i].PriceUnit; } xlWorkSheet.Cells[23 + i, "K"] = double.Parse(summaries[i].PriceUnit.ToString()) * double.Parse(summaries[i].TotalQuantity.ToString()); douTotalMonney += double.Parse(summaries[i].PriceUnit.ToString()) * double.Parse(summaries[i].TotalQuantity.ToString()); xlWorkSheet.Cells[23 + i, "E"] = "Vietnam"; string Product = summaries[i].Product.Trim(); Database.ERPSOFT.t_TL_Product t_TL_Product = new Database.ERPSOFT.t_TL_Product(); DataTable dt = new DataTable(); dt = t_TL_Product.GetdataExactllyProduct(Product); if (dt.Rows.Count == 1) { try { xlWorkSheet.Cells[23 + i, "C"] = dt.Rows[0]["MB004"].ToString().Trim(); } catch (Exception ex) { SystemLog.Output(SystemLog.MSG_TYPE.Err, "Error", ex.Message); } } } TotalMoney = (int)(douTotalMonney); var test = douTotalMonney - TotalMoney; int numberCent = (int)(Math.Round((douTotalMonney - TotalMoney) * 100, 2)); string ConvertToText = NumberConverterToText.Converter.ConvertNumberToString(TotalMoney); string convertToCentText = NumberConverterToText.Converter.ConvertNumberToString(numberCent); if (dtexport.Rows[0]["Currency"].ToString().Trim() == "USD") { xlWorkSheet.Cells[89, "A"] = "SAY TOTAL US DOLLAR " + ConvertToText.ToUpper() + " AND CENTS " + convertToCentText.ToUpper() + " ONLY"; xlWorkSheet.Cells[22, "I"] = "USD"; xlWorkSheet.Cells[22, "K"] = "USD"; xlWorkSheet.Cells[86, "I"] = "USD"; } else if (dtexport.Rows[0]["Currency"].ToString().Trim() == "EUR") { xlWorkSheet.Cells[89, "A"] = "SAY TOTAL EU DOLLAR " + ConvertToText.ToUpper() + " AND CENTS " + convertToCentText.ToUpper() + " ONLY"; xlWorkSheet.Cells[22, "I"] = "EUR"; xlWorkSheet.Cells[22, "K"] = "EUR"; xlWorkSheet.Cells[86, "I"] = "EUR"; } #endregion #region Delivery Notes xlWorkSheet2 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2); string strWorksheet2Name = xlWorkSheet2.Name; xlWorkSheet2.Cells[11, "K"] = dtexport.Rows[0]["Invoice"]; xlWorkSheet2.Cells[12, "K"] = DateTime.Now.ToString("dd-MM-yyyy"); xlWorkSheet2.Cells[11, "C"] = dtBuyer.Rows[0]["Buyer_Infor"].ToString(); xlWorkSheet2.Cells[15, "C"] = dtShipment.Rows[0]["ShipmentType"].ToString(); for (int i = 0; i < summaries.Count; i++) { xlWorkSheet2.Cells[23 + i, "A"] = (i + 1).ToString("0000"); // xlWorkSheet.Cells[23 + i, "C"] = dtexport.Rows[i]["Product"]; xlWorkSheet2.Cells[23 + i, "D"] = summaries[i].Product; xlWorkSheet2.Cells[23 + i, "G"] = summaries[i].TotalQuantity; xlWorkSheet2.Cells[23 + i, "H"] = summaries[i].Unit; if (summaries[i].Currency.ToString().Trim() == "USD") { xlWorkSheet2.Cells[23 + i, "I"] = "$ " + summaries[i].PriceUnit; } else if (summaries[i].Currency.ToString().Trim() == "EUR") { xlWorkSheet2.Cells[23 + i, "I"] = "€ " + summaries[i].PriceUnit; } xlWorkSheet2.Cells[23 + i, "K"] = double.Parse(summaries[i].PriceUnit.ToString()) * double.Parse(summaries[i].TotalQuantity.ToString()); // TotalMoney += (int)(double.Parse(dtexport.Rows[i]["PriceUnit"].ToString()) * double.Parse(dtexport.Rows[i]["Quantity"].ToString())); xlWorkSheet2.Cells[23 + i, "E"] = "Vietnam"; string Product = summaries[i].Product.Trim(); Database.ERPSOFT.t_TL_Product t_TL_Product = new Database.ERPSOFT.t_TL_Product(); DataTable dt = new DataTable(); dt = t_TL_Product.GetdataExactllyProduct(Product); if (dt.Rows.Count == 1) { try { xlWorkSheet2.Cells[23 + i, "C"] = dt.Rows[0]["MB004"].ToString().Trim(); } catch (Exception ex) { SystemLog.Output(SystemLog.MSG_TYPE.Err, "Error", ex.Message); } } } if (dtexport.Rows[0]["Currency"].ToString().Trim() == "USD") { xlWorkSheet2.Cells[22, "I"] = "USD"; xlWorkSheet2.Cells[22, "K"] = "USD"; } else if (dtexport.Rows[0]["Currency"].ToString().Trim() == "EUR") { xlWorkSheet2.Cells[22, "I"] = "EUR"; xlWorkSheet2.Cells[22, "K"] = "EUR"; } #endregion #region Sales Contract xlWorkSheet3 = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(3); string strWorksheet3Name = xlWorkSheet3.Name; xlWorkSheet3.Cells[25, "K"] = DateTime.Now.ToString("dd-MM-yyyy"); // xlWorkSheet3.Cells[12, "K"] = DateTime.Now.ToString("dd-MM-yyyy"); xlWorkSheet3.Cells[15, "C"] = dtBuyer.Rows[0]["Buyer_Infor"].ToString(); xlWorkSheet3.Cells[20, "B"] = dtBuyer.Rows[0]["Buyer_Consignee"].ToString(); xlWorkSheet3.Cells[109, "D"] = dtShipment.Rows[0]["ShipmentType"].ToString(); xlWorkSheet3.Cells[111, "D"] = dtShipment.Rows[0]["ShipmentInfor1"].ToString(); xlWorkSheet3.Cells[112, "D"] = dtShipment.Rows[0]["ShipmentInfor2"].ToString(); for (int i = 0; i < summaries.Count; i++) { xlWorkSheet3.Cells[33 + i, "A"] = (i + 1).ToString("0000"); // xlWorkSheet.Cells[23 + i, "C"] = dtexport.Rows[i]["Product"]; xlWorkSheet3.Cells[33 + i, "D"] = summaries[i].Product; xlWorkSheet3.Cells[33 + i, "G"] = summaries[i].TotalQuantity; xlWorkSheet3.Cells[33 + i, "H"] = summaries[i].Unit; if (summaries[i].Currency.Trim() == "USD") { xlWorkSheet3.Cells[33 + i, "I"] = "$ " + summaries[i].PriceUnit; } else if (summaries[i].Currency.Trim().Trim() == "EUR") { xlWorkSheet3.Cells[33 + i, "I"] = "€ " + summaries[i].PriceUnit; } xlWorkSheet3.Cells[33 + i, "K"] = double.Parse(summaries[i].PriceUnit.ToString()) * double.Parse(summaries[i].TotalQuantity.ToString()); // TotalMoney += (int)(double.Parse(dtexport.Rows[i]["PriceUnit"].ToString()) * double.Parse(dtexport.Rows[i]["Quantity"].ToString())); xlWorkSheet3.Cells[33 + i, "F"] = "Vietnam"; string Product = summaries[i].Product.Trim(); Database.ERPSOFT.t_TL_Product t_TL_Product = new Database.ERPSOFT.t_TL_Product(); DataTable dt = new DataTable(); dt = t_TL_Product.GetdataExactllyProduct(Product); if (dt.Rows.Count == 1) { try { xlWorkSheet3.Cells[33 + i, "C"] = dt.Rows[0]["MB004"].ToString().Trim(); } catch (Exception ex) { SystemLog.Output(SystemLog.MSG_TYPE.Err, "Error", ex.Message); } } } if (dtexport.Rows[0]["Currency"].ToString().Trim() == "USD") { xlWorkSheet3.Cells[99, "A"] = "SAY TOTAL US DOLLAR " + ConvertToText.ToUpper() + " AND CENTS " + convertToCentText.ToUpper() + " ONLY"; xlWorkSheet3.Cells[32, "I"] = "USD"; xlWorkSheet3.Cells[32, "K"] = "USD"; xlWorkSheet3.Cells[97, "I"] = "USD"; } else if (dtexport.Rows[0]["Currency"].ToString().Trim() == "EUR") { xlWorkSheet3.Cells[99, "A"] = "SAY TOTAL EU DOLLAR " + ConvertToText.ToUpper() + " AND CENTS " + convertToCentText.ToUpper() + " ONLY"; xlWorkSheet3.Cells[32, "I"] = "EUR"; xlWorkSheet3.Cells[32, "J"] = "EUR"; xlWorkSheet3.Cells[97, "I"] = "EUR"; } #endregion xlWorkBook.SaveAs(PathSave, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); reOject(xlWorkSheet); reOject(xlWorkSheet2); reOject(xlWorkSheet3); reOject(xlWorkBook); reOject(xlApp); } catch (Exception EX) { SystemLog.Output(SystemLog.MSG_TYPE.Err, "ExportCustomsDecalaration(string PathSave,DataTable dtexport)", "export fail"); return(false); } return(true); }