public DataTable getDatacaseCreation(CSPiHelper Helper)
        {
            string connectionStr = ConfigurationManager.ConnectionStrings["CSPiConnection"].ConnectionString;
            string fadvEntityId  = Convert.ToString(ConfigurationManager.AppSettings["Fadv_EntityId"]);

            AppLog.Debug("Step 2");
            using (OracleConnection con = new OracleConnection())
            {
                con.ConnectionString = connectionStr;
                con.Open();
                AppLog.Debug("Step 3");
                Console.WriteLine("Connected to Oracle Database {0}", con.ServerVersion);
                using (OracleCommand cmd = new OracleCommand())
                {
                    AppLog.Debug("Step 4");
                    string dateNow = DateTime.Today.ToShortDateString();
                    //UAT date time
                    string b = Convert.ToDateTime(dateNow).ToString("MM/dd/yyyy");
                    dateNow = b.Replace("-", "/");

                    string dateTommorow = DateTime.Today.AddDays(1).ToShortDateString();
                    //production date time
                    string c = Convert.ToDateTime(dateTommorow).ToString("MM/dd/yyyy");
                    dateTommorow = c.Replace("-", "/");

                    cmd.Connection  = con;
                    cmd.CommandText = "pkg_cspi_rpt_magik_data_apac.fn_get_clienthr_cases_data";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(CreateCursor("return_cursor", ParameterDirection.ReturnValue));
                    cmd.Parameters.Add("pi_entity_id", fadvEntityId);
                    cmd.Parameters.Add("pi_Start_date", dateNow);
                    cmd.Parameters.Add("pi_End_date", dateTommorow);
                    AppLog.Debug("Today :" + dateNow + "Tommorow :" + dateTommorow);
                    cmd.Parameters.Add(CreateCursor("po_case_clienthr_cases_data"));
                    cmd.Parameters.Add(CreateCursor("po_client_field_data"));
                    DataSet ds = new DataSet();
                    AppLog.Debug("Step 5");
                    OracleDataAdapter MyDa = new OracleDataAdapter(cmd);
                    MyDa.Fill(ds);
                    AppLog.Debug("Data fetch sucess from Oracle");
                    CSPiModelRequest obj = new CSPiModelRequest();
                    con.Close();
                    var countT = 0;
                    foreach (DataRow dr1 in ds.Tables[1].Rows)
                    {
                        countT++;
                        Json_Format = "";
                        string CRN = Regex.Replace(dr1["CASE_REF_NUMBER"].ToString(), @"[^\u0000-\u007F]+", string.Empty);
                        // if (CRN != "R233-6112490-WEST-2019") continue;
                        AppLog.Debug("Working On CRNNumber :" + CRN);
                        //   DataTable crnNO = CRNDetails(Regex.Replace(dr1["CASE_REF_NUMBER"].ToString(), @"[^\u0000-\u007F]+", string.Empty));
                        DBParameters param = new DBParameters(TBL_Requests.TableName);
                        param.AddParameter(TBL_Requests.CRN_Number, CRN);
                        List <DBTBL_Requests> TaskDestiny = Helper.DBRead_TBL_Requests(param, false);

                        string birthDateStr   = "";
                        string is_dob         = "0";
                        string crnCreatedDate = null;
                        if (TaskDestiny.Count == 0)
                        {
                            try
                            {
                                country = Regex.Replace(dr1["COUNTRY_NAME"].ToString(), @"[^\u0000-\u007F]+", string.Empty);
                                EmailId = Regex.Replace(dr1["EMAIL_ADDRESS"].ToString(), @"[^\u0000-\u007F]+", string.Empty);
                                DateTime birthDate  = DateTime.MinValue;
                                DateTime CreatedCse = DateTime.MinValue;
                                is_dob = "0";


                                if (!string.IsNullOrEmpty(Regex.Replace(dr1["BIRTH_DT"].ToString(), @"[^\u0000-\u007F]+", string.Empty)))
                                {
                                    // birthDate = DateTime.ParseExact(form.dateOfBirth, "dd/MM/yyyy", null);
                                    string[] s2          = (Regex.Replace(dr1["BIRTH_DT"].ToString(), @"[^\u0000-\u007F]+", string.Empty)).Split(' ');
                                    string   dateOfBirth = s2[0];
                                    birthDate = DateTime.Parse(dateOfBirth);
                                    // birthDate = DateTime.ParseExact(dateOfBirth,"dd/MM/yyyy", null);

                                    is_dob = "1";
                                }

                                if (!string.IsNullOrEmpty(Regex.Replace(dr1["Case Date"].ToString(), @"[^\u0000-\u007F]+", string.Empty)))
                                {
                                    string[] s1       = (Regex.Replace(dr1["Case Date"].ToString(), @"[^\u0000-\u007F]+", string.Empty)).Split(' ');
                                    string   caseDate = s1[0];
                                    // crnCreatedDate = (DateTime.ParseExact(CreatedCseDate, "dd/mm/yyyy", null)).ToString();
                                    // String result = String.Join("-", CreatedCseDate.Split('/').Reverse());
                                    //crnCreatedDate = result+" "+s1[1];
                                    CreatedCse     = DateTime.Parse(caseDate);
                                    crnCreatedDate = CreatedCse.ToString("yyyy-MM-dd");
                                }

                                birthDateStr = birthDate.ToString("yyyy-MM-dd");
                            }
                            catch (Exception ex)
                            {
                            }
                            try
                            {
                                stateId           = "REQ-0002";
                                obj.OperationName = "usp_save_request_data_engine";
                                obj.OperationType = "procedure";
                                //Json_Format = getJsonclientSpecificFields(country, EmailId);
                                DataTable ClientCode = ClientDetails(Regex.Replace(dr1["CLIENT_CODE"].ToString(), @"[^\u0000-\u007F]+", string.Empty));

                                if (ClientCode.Rows.Count > 0)
                                {
                                    DataTable SBUID = SBUDetails((Regex.Replace(dr1["SBU Name"].ToString(), @"[^\u0000-\u007F]+", string.Empty)), ClientCode.Rows[0]["ClientID"].ToString());
                                    if (SBUID.Rows.Count > 0)
                                    {
                                        DataTable PackageID = PackageDetails(Regex.Replace(dr1["PACKAGE_NAME"].ToString(), @"[^\u0000-\u007F]+", string.Empty), SBUID.Rows[0]["SBUID"].ToString(), ClientCode.Rows[0]["ClientID"].ToString());

                                        if (PackageID.Rows.Count > 0 && SBUID.Rows.Count > 0 && ClientCode.Rows.Count > 0)
                                        {
                                            // DataTable PackageIDList = PackckageDetailsNew(PackageID.Rows[0]["PackageID"].ToString());
                                            // if (PackageIDList.Rows.Count > 0)
                                            //  {

                                            Json_Format += "{";
                                            Json_Format += "\"requestoremail\":{\"request_field_id\": \"131\",\"data\":\"" + Regex.Replace(dr1["EMAIL_ADDRESS"].ToString(), @"[^\u0000-\u007F]+", string.Empty) + "\",\"text\": \"" + Regex.Replace(dr1["EMAIL_ADDRESS"].ToString(), @"[^\u0000-\u007F]+", string.Empty) + "\"},";
                                            foreach (DataRow dr2 in ds.Tables[2].Rows)
                                            {
                                                string CSPCCase_ID = Regex.Replace(dr2["CASE_ID"].ToString(), @"[^\u0000-\u007F]+", string.Empty);
                                                string Case_ID     = Regex.Replace(dr1["CASE_ID"].ToString(), @"[^\u0000-\u007F]+", string.Empty);
                                                string Clent_Field = Regex.Replace(dr2["Client_Field"].ToString(), @"[^\u0000-\u007F]+", string.Empty);
                                                string ANSWERS     = Regex.Replace(dr2["ANSWERS"].ToString(), @"[^\u0000-\u007F]+", string.Empty);
                                                if (Case_ID == CSPCCase_ID)
                                                {
                                                    DataTable CLIENTSpeccific = PackageDetailsNew(Clent_Field, ClientCode.Rows[0]["ClientID"].ToString());
                                                    if (Clent_Field != "requestoremail")
                                                    {
                                                        if (CLIENTSpeccific.Rows.Count > 0)
                                                        {
                                                            Json_Format += "\"" + CLIENTSpeccific.Rows[0]["field_json_tag"].ToString() + "\": {\"request_field_id\": \"" + CLIENTSpeccific.Rows[0]["request_field_id"].ToString() + "\",\"data\":\"" + ANSWERS + "\",\"text\": \"" + ANSWERS + "\"},";
                                                        }
                                                    }
                                                }
                                            }
                                            // }
                                            Json_Format  = Json_Format.TrimEnd(',');
                                            Json_Format += "}";

                                            //Json_Format = getJsonclientSpecificFields(country, EmailId,ClientCode.Rows[0]["ClientID"].ToString());
                                            AppLog.Debug("Case Strated for CRN :" + CRN);
                                            obj.ParameterList = new List <OperationParameter>();
                                            obj.ParameterList.Add(new OperationParameter("@p_request_id", "0"));
                                            obj.ParameterList.Add(new OperationParameter("@p_client_id", ClientCode.Rows[0]["ClientID"].ToString()));
                                            obj.ParameterList.Add(new OperationParameter("@p_crn_no", Regex.Replace(dr1["CASE_REF_NUMBER"].ToString(), @"[^\u0000-\u007F]+", string.Empty)));
                                            obj.ParameterList.Add(new OperationParameter("@p_crn_created_date", crnCreatedDate));

                                            obj.ParameterList.Add(new OperationParameter("@p_first_name", Regex.Replace(dr1["FIRST_NAME"].ToString(), @"[^\u0000-\u007F]+", string.Empty)));
                                            obj.ParameterList.Add(new OperationParameter("@p_middle_name", Regex.Replace(dr1["MIDDLE_NAME"].ToString(), @"[^\u0000-\u007F]+", string.Empty)));
                                            obj.ParameterList.Add(new OperationParameter("@p_last_name", Regex.Replace(dr1["LAST_NAME"].ToString(), @"[^\u0000-\u007F]+", string.Empty)));
                                            obj.ParameterList.Add(new OperationParameter("@p_client_ref_no", Regex.Replace(dr1["CLIENT_REFERNCE_NO"].ToString(), @"[^\u0000-\u007F]+", string.Empty)));
                                            obj.ParameterList.Add(new OperationParameter("@p_subject_detail", Regex.Replace(dr1["SUBJECT_DETAIL"].ToString(), @"[^\u0000-\u007F]+", string.Empty)));
                                            obj.ParameterList.Add(new OperationParameter("@p_subject_type", Regex.Replace(dr1["SUBJECT_TYPE"].ToString(), @"[^\u0000-\u007F]+", string.Empty)));
                                            obj.ParameterList.Add(new OperationParameter("@p_is_date_of_birth", is_dob));
                                            obj.ParameterList.Add(new OperationParameter("@p_date_of_birth", birthDateStr));
                                            obj.ParameterList.Add(new OperationParameter("@p_type_of_check", Regex.Replace(dr1["TYPE_OF_CHECK"].ToString(), @"[^\u0000-\u007F]+", string.Empty)));
                                            obj.ParameterList.Add(new OperationParameter("@p_candidateauthorizationletter", Regex.Replace(dr1["CANDIDATE_AUTHORIZATION_LETTER"].ToString(), @"[^\u0000-\u007F]+", string.Empty)));
                                            obj.ParameterList.Add(new OperationParameter("@p_package_type", "Soft Copy"));
                                            obj.ParameterList.Add(new OperationParameter("@p_srt_data", "SRT"));
                                            obj.ParameterList.Add(new OperationParameter("@p_sbu_entitiesid", SBUID.Rows[0]["SBUID"].ToString()));
                                            obj.ParameterList.Add(new OperationParameter("@p_loa_submitted", "Yes"));

                                            obj.ParameterList.Add(new OperationParameter("@p_bvf_submitted", "Yes"));

                                            obj.ParameterList.Add(new OperationParameter("@p_json_data", Json_Format));
                                            obj.ParameterList.Add(new OperationParameter("@p_userid", "165"));

                                            obj.ParameterList.Add(new OperationParameter("@p_comment", "Imported from CSPI"));
                                            obj.ParameterList.Add(new OperationParameter("@p_state_id", stateId));
                                            // obj.ParameterList.Add(new OperationParameter("@p_out_request_id", "", a)); s
                                            obj.ParameterList.Add(new OperationParameter("@p_out_request_id", "p_out_request_id"));

                                            CSPiHelper ctr  = new CSPiHelper(ConfigurationManager.AppSettings["username"], ConfigurationManager.AppSettings["password"]);
                                            DataTable  retv = ctr.ExecuteStoredProcedure(obj).Result;
                                            DataRow    dr   = retv.Rows[0];
                                            try
                                            {
                                                String columnValue = dr[0].ToString();
                                                if (retv.Rows.Count > 0)
                                                {
                                                    // dr = retv.Rows[1];
                                                    // generatedRequestId = dr[0].ToString();
                                                    generatedRequestId = columnValue;
                                                    retv.Dispose();
                                                    AppLog.Debug("Case creted" + CRN + "RequestId" + generatedRequestId);
                                                    //DELETEING EXISTING PACKAGE FROM CASE
                                                    obj.OperationName = "DELETE FROM tbl_request_packages WHERE RequestID=@p_request_id";
                                                    obj.OperationType = "query";
                                                    obj.ParameterList = new List <OperationParameter>();
                                                    obj.ParameterList.Add(new OperationParameter("@p_request_id", generatedRequestId));

                                                    DataTable retv1 = ctr.BotTaskExecuteQuery(obj).Result;

                                                    retv.Dispose();
                                                    //  END - DELETEING EXISTING PACKAGE FROM CASE

                                                    // ADDING NEWLY SELECTED PACKAGE INTO CASE
                                                    obj.OperationName = "INSERT INTO tbl_request_packages (`RequestID`,`PackageID`) VALUES (@p_request_id,@p_package_id)";
                                                    obj.OperationType = "query";
                                                    obj.ParameterList = new List <OperationParameter>();
                                                    obj.ParameterList.Add(new OperationParameter("@p_request_id", generatedRequestId));
                                                    obj.ParameterList.Add(new OperationParameter("@p_package_id", PackageID.Rows[0]["PackageID"].ToString()));
                                                    DataTable retv2 = ctr.BotTaskExecuteQuery(obj).Result;
                                                    //  END - ADDING NEWLY SELECTED PACKAGE INTO CASE
                                                    updateStatus(generatedRequestId);

                                                    JsonCaseCreationData  = "{\"Header\": {\"Type\": \"Request\",\"ServiceID\": \"CaseCreation\",\"MessageId\": \"" + Guid.NewGuid().ToString() + "\",\"MessageDate\": \"" + DateTime.Now.ToShortDateString() + "\",\"RequestID\": \"" + generatedRequestId + "\",";
                                                    JsonCaseCreationData += "\"Module\": \"" + "CaseCreation" + "\",\"RetrySequecne\": \"0\",\"BOTID\": \"\",\"CheckPoint\": \"\"},\"Data\":{\"CaseCreation\":{\"" + "requestId" + "\":  \"" + generatedRequestId + "\", \"" + "clientId" + "\":  \"" + ClientCode.Rows[0]["ClientID"].ToString() + "\",";
                                                    JsonCaseCreationData += "\"" + "crnNo" + "\":  \"" + Regex.Replace(dr1["CASE_REF_NUMBER"].ToString(), @"[^\u0000-\u007F]+", string.Empty) + "\",";
                                                    JsonCaseCreationData += "\"" + "crnCreatedDate" + "\":  \"" + Regex.Replace(dr1["Case Date"].ToString(), @"[^\u0000-\u007F]+", string.Empty) + "\",";
                                                    JsonCaseCreationData += "\"" + "firstName" + "\":  \"" + Regex.Replace(dr1["FIRST_NAME"].ToString(), @"[^\u0000-\u007F]+", string.Empty) + "\",\"" + "middleName" + "\":  \"" + Regex.Replace(dr1["MIDDLE_NAME"].ToString(), @"[^\u0000-\u007F]+", string.Empty) + "\",";
                                                    JsonCaseCreationData += "\"" + "lastName" + "\":  \"" + Regex.Replace(dr1["LAST_NAME"].ToString(), @"[^\u0000-\u007F]+", string.Empty) + "\",\"" + "dateOfBirth" + "\":  \"" + birthDateStr + "\",";
                                                    JsonCaseCreationData += "\"" + "sbuEntitiesId" + "\":  \"" + SBUID.Rows[0]["SBUID"].ToString() + "\",\"" + "clientReferenceNo" + "\":  \"" + Regex.Replace(dr1["CASE_REF_NUMBER"].ToString(), @"[^\u0000-\u007F]+", string.Empty) + "\",\"" + "subjectDetails" + "\":  \"" + Regex.Replace(dr1["SUBJECT_DETAIL"].ToString(), @"[^\u0000-\u007F]+", string.Empty) + "\",";
                                                    JsonCaseCreationData += "\"" + "subjectType" + "\":  \"" + Regex.Replace(dr1["SUBJECT_TYPE"].ToString(), @"[^\u0000-\u007F]+", string.Empty) + "\",\"" + "typeOfCheck" + "\":  \"" + Regex.Replace(dr1["TYPE_OF_CHECK"].ToString(), @"[^\u0000-\u007F]+", string.Empty) + "\",";
                                                    JsonCaseCreationData += "\"" + "authorizationLetter" + "\":  \"" + Regex.Replace(dr1["CANDIDATE_AUTHORIZATION_LETTER"].ToString(), @"[^\u0000-\u007F]+", string.Empty) + "\",\"" + "packageType" + "\":  \"" + "Soft Copy" + "\",";
                                                    JsonCaseCreationData += "\"" + "srtData" + "\":  \"" + "SRT" + "\",\"" + "lOASubmited" + "\":  \"" + "Yes" + "\",\"" + "bVFSubmitted" + "\":  \"" + "Yes" + "\",";
                                                    JsonCaseCreationData += "\"" + "isClientSpecificField" + "\":  \"" + "true" + "\",\"" + "clientSpecificFields" + "\":  " + Json_Format + ",";
                                                    JsonCaseCreationData += "\"" + "comment" + "\":  \"" + null + "\",\"" + "request_status" + "\":  \"" + "Complete" + "\",";
                                                    JsonCaseCreationData += "\"" + "packageList" + "\":[  \"" + PackageID.Rows[0]["PackageID"].ToString() + "\"],\"" + "clientName" + "\":  \"" + Regex.Replace(dr1["Client Name"].ToString(), @"[^\u0000-\u007F]+", string.Empty) + "\",";
                                                    JsonCaseCreationData += "\"" + "sbuEntitiesName" + "\":  \"" + Regex.Replace(dr1["SBU Name"].ToString(), @"[^\u0000-\u007F]+", string.Empty) + "\",\"" + "packageName" + "\":  \"" + Regex.Replace(dr1["PACKAGE_NAME"].ToString(), @"[^\u0000-\u007F]+", string.Empty) + "\"";
                                                    JsonCaseCreationData += "}},\"" + "Status" + "\":{  \"" + "Value" + "\":  \"" + "" + "\",";
                                                    JsonCaseCreationData += "\"" + "Description" + "\":  \"" + "" + "\",\"" + "Details" + "\":  \"" + null + "\"}}";
                                                    //SaveJsonData(generatedRequestId, JsonCaseCreationData, "CaseCreation", "1");
                                                    AppLog.Debug("Case Success CRNNumber :" + CRN);

                                                    //For document fetch
                                                    DataTable CheckPackegeCDEMDE = CheckPackegeCDEOrMDE(generatedRequestId);
                                                    if (CheckPackegeCDEMDE.Rows.Count == 0)
                                                    {
                                                        AppLog.Debug("MDE Case :" + CRN);
                                                        DataTable dtDocument = ds.Tables[0];
                                                        foreach (DataRow objDataRow in dtDocument.Rows)
                                                        {
                                                            string CSPCCase_ID  = Regex.Replace(objDataRow["CASE_ID"].ToString(), @"[^\u0000-\u007F]+", string.Empty);
                                                            string Case_ID      = Regex.Replace(dr1["CASE_ID"].ToString(), @"[^\u0000-\u007F]+", string.Empty);
                                                            string documentPath = objDataRow["DOCUMENT_PATH"].ToString();
                                                            if (CSPCCase_ID == Case_ID)
                                                            {
                                                                document.Add(Regex.Replace(objDataRow["DOCUMENT_PATH"].ToString(), @"[^\u0000-\u007F]+", string.Empty));
                                                            }
                                                        }
                                                        AppLog.Debug("Document Upload started :" + CRN);
                                                        Savedocument(generatedRequestId);
                                                    }
                                                }
                                            }
                                            catch (Exception ex)
                                            {
                                            }
                                        }
                                    }
                                    else
                                    {
                                        AppLog.Debug("Not Match CRNNumber :" + CRN);
                                    }
                                }
                            }
                            catch (Exception ex)
                            { }
                        }
                        //  }
                    }
                    return(null);
                }
            }
        }