示例#1
0
        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);
        }