private RequisitionDateInfo GetRequisitionDateInfo(string AppNo)
        {
            RequisitionDateInfo ret = new RequisitionDateInfo();
            string sql = "select isnull(frm_submit_date,SYS_SUBMIT_DATE) frm_submit_date, receive_date ";

            sql += " from [" + dbLinkIP + "].PATENTSYSTEM.dbo.REQUISITION rq";
            sql += " where app_no='" + AppNo + "'";
            sql += " and formtype=1";

            DataTable dt = Engine.Common.FunctionEng.ExecuteDataTable(sql);

            if (dt.Rows.Count > 0)
            {
                if (Convert.IsDBNull(dt.Rows[0]["receive_date"]) == false)
                {
                    ret.FILING_DATE = Convert.ToDateTime(dt.Rows[0]["frm_submit_date"]);
                }
                if (Convert.IsDBNull(dt.Rows[0]["receive_date"]) == false)
                {
                    ret.RECEIVE_DATE = Convert.ToDateTime(dt.Rows[0]["receive_date"]);
                }
            }
            return(ret);
        }
        private void btnStart_Click(object sender, EventArgs e)
        {
            if (txtImportFile.Text.Trim() == "")
            {
                MessageBox.Show("กรุณาเลือกไฟล์ที่ต้องการนำเข้าข้อมูล");
                return;
            }


            IEDriver sIE = new IEAutomation.IEDriver();

            IE = sIE.GetIEBrowser("iexplore", "https://epatent.ipthailand.go.th/ePatent/App-Exam/DocImageAll.aspx");
            if (IE == null)
            {
                IE = sIE.GetIEBrowser("iexplore", "https://10.10.18.171/ePatent/App-Exam/DocImageAll.aspx");
            }
            if (IE == null)
            {
                IE = sIE.GetIEBrowser("iexplore", "http://epatent.ipthailand.go.th/ePatent/App-Exam/DocImageAll.aspx");
            }
            if (IE == null)
            {
                IE = sIE.GetIEBrowser("iexplore", "http://10.10.18.171/ePatent/App-Exam/DocImageAll.aspx");
            }

            if (IE != null)
            {
                DataTable dt = FunctionENG.getDataTableFromExcel(txtImportFile.Text.Trim());
                if (dt.Rows.Count > 0)
                {
                    progressBar1.Maximum = dt.Rows.Count;
                    progressBar1.Value   = 0;

                    foreach (DataRow dr in dt.Rows)
                    {
                        progressBar1.Value    += 1;
                        lblProgressStatus.Text = "App No: " + dr["app_no"].ToString() + "   Current Row:" + progressBar1.Value + "   Total Row:" + dt.Rows.Count;
                        Application.DoEvents();

                        string AppNo          = dr["app_no"].ToString();
                        string ReqTypeName    = dr["requisition_type_name"].ToString();
                        string PatentTypeName = dr["patent_type_name"].ToString();
                        //DateTime PrepareDate = Engine.Common.FunctionEng.cStrToDateTime3(dr["prepare_date"].ToString());
                        //DateTime SendDate = Engine.Common.FunctionEng.cStrToDateTime3(dr["send_date"].ToString());
                        RequisitionDateInfo DateInfo = GetRequisitionDateInfo(AppNo);

                        try
                        {
                            MsRequisitionTypeLinqDB rqLnq = new MsRequisitionTypeLinqDB();
                            rqLnq.ChkDataByWhere("requisition_type_name='" + ReqTypeName + "'", null);
                            if (rqLnq.ID == 0)
                            {
                                MessageBox.Show("ไม่พบข้อมูลประเภทคำขอ " + ReqTypeName + "  เลขที่คำขอ:" + AppNo + "   RowID:" + progressBar1.Value);
                                return;
                            }

                            MsPatentTypeLinqDB ptLnq = new MsPatentTypeLinqDB();
                            ptLnq.ChkDataByWhere("patent_type_name = '" + PatentTypeName + "'", null);
                            if (ptLnq.ID == 0)
                            {
                                MessageBox.Show("ไม่พบข้อมูลประเภทแฟ้ม " + PatentTypeName + "  เลขที่คำขอ:" + AppNo + "   RowID:" + progressBar1.Value);
                                return;
                            }

                            TsFileRequisitionInfoLinqDB infLnq = new TsFileRequisitionInfoLinqDB();
                            infLnq.ChkDataByAPP_NO_MS_REQUISITION_TYPE_ID(AppNo, rqLnq.ID, null);

                            infLnq.APP_NO = AppNo;
                            if (DateInfo.FILING_DATE.Year != 1)
                            {
                                infLnq.FILING_DATE = DateInfo.FILING_DATE;
                            }
                            if (DateInfo.RECEIVE_DATE.Year != 1)
                            {
                                infLnq.RECEIVE_DATE = DateInfo.RECEIVE_DATE;
                            }
                            infLnq.MS_REQUISITION_TYPE_ID = rqLnq.ID;
                            infLnq.MS_PATENT_TYPE_ID      = ptLnq.ID;
                            //infLnq.PREPARE_DATE = PrepareDate;
                            //infLnq.SEND_DATE = SendDate;

                            bool          re    = true;
                            TransactionDB trans = new TransactionDB();
                            if (infLnq.ID == 0)
                            {
                                re = infLnq.InsertData("Import", trans.Trans);
                            }
                            else
                            {
                                re = infLnq.UpdateByPK("Import", trans.Trans);
                            }

                            if (re == false)
                            {
                                trans.RollbackTransaction();
                                //MessageBox.Show(infLnq.ErrorMessage + "  เลขที่คำขอ:" + AppNo + "   RowID:" + progressBar1.Value);
                                Engine.Common.FunctionEng.CreateLogFile(Application.StartupPath + @"\ImportError_" + DateTime.Now.ToString("yyyyMMdd") + ".txt", "AppNo=" + AppNo + "  " + infLnq.ErrorMessage);
                                continue;
                            }

                            sIE.SetInputStringValue("ctl00$MainContent$txtAppNo", AppNo);
                            sIE.ClickButton("ctl00$MainContent$btnSearch");

                            HTMLTable tb = sIE.GetTableElement("ctl00_MainContent_gvMain");
                            if (tb.rows.length > 1)
                            {
                                TsFileScanSummaryLinqDB lnq = new TsFileScanSummaryLinqDB();
                                DataTable tmpDt             = lnq.GetDataList("app_no='" + AppNo + "'", "", trans.Trans);
                                if (tmpDt.Rows.Count > 0)
                                {
                                    //TransactionDB trans = new TransactionDB();
                                    foreach (DataRow tmpDr in tmpDt.Rows)
                                    {
                                        re = lnq.DeleteByPK(Convert.ToInt64(tmpDr["id"]), trans.Trans);
                                        if (re == false)
                                        {
                                            break;
                                        }
                                    }
                                    if (re == false)
                                    {
                                        trans.RollbackTransaction();
                                    }
                                    lnq = null;
                                }

                                if (re == true)
                                {
                                    int iRow = 0;
                                    foreach (HTMLTableRow tbr in tb.rows)
                                    {
                                        if (iRow == 0)
                                        {
                                            iRow += 1;
                                            continue;
                                        }

                                        HTMLTableCell CellReqNo      = (HTMLTableCell)tbr.cells.item(2);
                                        HTMLTableCell CellReqType    = (HTMLTableCell)tbr.cells.item(3);
                                        HTMLTableCell CellFilingDate = (HTMLTableCell)tbr.cells.item(4);
                                        HTMLTableCell CellImportDate = (HTMLTableCell)tbr.cells.item(5);
                                        HTMLTableCell CellDocType    = (HTMLTableCell)tbr.cells.item(9);
                                        HTMLTableCell CellPageQty    = (HTMLTableCell)tbr.cells.item(10);
                                        //HTMLTableCell CellVersion = (HTMLTableCell)tbr.cells.item(11);

                                        string ReqNo      = CellReqNo.innerHTML.Trim();
                                        string ReqType    = CellReqType.innerHTML.Trim();
                                        string FilingDate = CellFilingDate.innerHTML.Trim();
                                        string ImportDate = CellImportDate.innerHTML.Trim();
                                        string DocType    = CellDocType.innerHTML.Trim();
                                        string PageQty    = CellPageQty.innerHTML.Trim();
                                        string vVersion   = GetString((HTMLTableCell)tbr.cells.item(11));

                                        //string vData = "AppNo=" + AppNo + ": PatentType=" + PatentType + ": FilingDate=" + FilingDate + ": ImportDate=" + ImportDate + ": " + DocType + ": PageQty=" + PageQty + ": Verion=" + vVersion;

                                        //Engine.Common.FunctionEng.CreateLogFile(vData);
                                        lnq        = new TsFileScanSummaryLinqDB();
                                        lnq.APP_NO = AppNo;
                                        lnq.REQ_NO = ReqNo;
                                        lnq.REQUISITION_TYPE_NAME = ReqType;
                                        lnq.FILING_DATE           = Engine.Common.FunctionEng.cStrToDateTime2(FilingDate);
                                        lnq.IMPORT_DATE           = Engine.Common.FunctionEng.cStrToDateTime2(ImportDate);
                                        lnq.DOC_TYPE_NAME         = DocType;
                                        lnq.PAGE_QTY    = Convert.ToInt64(PageQty);
                                        lnq.DOC_VERSION = vVersion;

                                        re = lnq.InsertData("Import", trans.Trans);
                                        if (re == false)
                                        {
                                            Engine.Common.FunctionEng.CreateLogFile(Application.StartupPath + @"\ImportError_" + DateTime.Now.ToString("yyyyMMdd") + ".txt", "AppNo=" + AppNo + "  " + infLnq.ErrorMessage);
                                            continue;
                                        }
                                        iRow += 1;
                                    }
                                }
                            }

                            if (re == true)
                            {
                                trans.CommitTransaction();
                            }
                            else
                            {
                                trans.RollbackTransaction();
                            }
                        }
                        catch (Exception ex)
                        {
                            Engine.Common.FunctionEng.CreateLogFile(Application.StartupPath + @"\ImportError_" + DateTime.Now.ToString("yyyyMMdd") + ".txt", ex.Message + "\n" + ex.StackTrace);
                        }
                    }
                    MessageBox.Show("Complete");
                }
            }
            else
            {
                MessageBox.Show("กรุณาเปิดระบบ ePatent และเข้าหน้าจอ เมนูหลัก > แสดงเอกสาร");
            }
        }