public string Read_Response()
        {
            DML_Utility objDML = new DML_Utility();
            string      output = "";
            // Read RABITMQ for Response //
            string Response_Data  = "";
            string Response_Error = "";

            try
            {
                RabbitMQ_Utility objQueue    = new RabbitMQ_Utility();
                bool             retResponse = objQueue.Receive(RabbitMQ_Utility.RabbitMQResponseQueue, out Response_Data, out Response_Error);
                //objDML.Add_Exception_Log(Response_Data, "");

                //objDML.Add_Exception_Log(Response_Data, "");
                if (!string.IsNullOrEmpty(Response_Data))
                {
                    string responce_MessageId = "";
                    string responce_ServiceId = "";

                    responce_MessageId = Read_Json_TagWise(Response_Data, "Header", "MessageID");
                    responce_ServiceId = Read_Json_TagWise(Response_Data, "Header", "ServiceId");
                    // Add Response into Database //
                    Get_Data_Utility obj = new Get_Data_Utility();
                    //DML_Utility objDML = new DML_Utility();
                    long reqId = obj.Get_Request_Id(responce_MessageId, responce_ServiceId);
                    //objDML.Add_Exception_Log("select * from tbl_request_details where messageid='" + responce_MessageId + "'", "");
                    //objDML.Add_Exception_Log(reqId.ToString(), "");

                    if (reqId > 0)
                    {
                        objDML.Add_Response_Json(reqId, Response_Data, responce_MessageId, responce_ServiceId);
                    }
                }
            }
            catch (Exception ex)
            {
                output = "exception : " + ex.Message.ToString();
                ////// Exception Log ///

                int iException = objDML.Add_Exception_Log(ex.Message, "Read_Response");
            }
            return(output);
        }
        public string Execute_Case_Creation()
        {
            string      output = "";
            DML_Utility objDML = new DML_Utility();

            try
            {
                // Read RABITMQ for Response //
                string           Response_Data  = "";
                string           Response_Error = "";
                RabbitMQ_Utility objQueue       = new RabbitMQ_Utility();
                //bool retResponse = objQueue.Receive("Response", out Response_Data, out Response_Error);
                bool retResponse = objQueue.Receive(RabbitMQ_Utility.RabbitMQResponseQueue, out Response_Data, out Response_Error);

                if (!string.IsNullOrEmpty(Response_Data))
                {
                    List <tbl_yettostart_casecreation_data> lst = new List <tbl_yettostart_casecreation_data>();
                    tbl_yettostart_casecreation_data        tbl = new tbl_yettostart_casecreation_data();
                    string responce_MessageId = "";
                    string responce_ServiceId = "";
                    string path = Read_Json_Case_Creation(Response_Data, out responce_MessageId, out responce_ServiceId);
                    if (responce_ServiceId.ToLower() == "freshcase")
                    {
                        string College_CVT = "";
                        string College_UT  = "";
                        string Degree_CVT  = "";
                        string Degree_UT   = "";

                        // get RequestId column from table //
                        Get_Data_Utility obj   = new Get_Data_Utility();
                        long             reqId = obj.Get_Request_Id_new(responce_MessageId, "YET2START");
                        if (reqId > 0)
                        {
                            tbl.queue_request_id        = reqId;
                            tbl.cognizent_tech_solution = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "cognizent_tech_solution");
                            tbl.clientcode           = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "clientcode");
                            tbl.candidate_name       = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "candidate_name");
                            tbl.client_ref_no        = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "client_ref_no");
                            tbl.bvg_type             = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "bvg_type");
                            tbl.package              = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "package");
                            tbl.specification        = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "specification");
                            tbl.project_id           = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "project_id");
                            tbl.project_name         = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "project_name");
                            tbl.doj                  = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "doj");
                            tbl.request_id           = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "request_id");
                            tbl.associate_id         = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "associate_id");
                            tbl.candidate_id         = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "candidate_id");
                            tbl.employee_id          = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "employee_id");
                            tbl.account_name         = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "account_name");
                            tbl.tensse               = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "tensse");
                            tbl.actual_case_created  = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "actual_case_created");
                            tbl.first_name           = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "First_Name");
                            tbl.last_name            = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Last_Name");
                            tbl.date_of_birth        = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Date_Of_Birth");
                            tbl.father_name          = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Father_Name");
                            tbl.nationality          = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Nationality");
                            tbl.mobile_number        = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Mobile_Number");
                            tbl.current_address      = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Current_Address");
                            tbl.permanent_address    = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Permanent_Address");
                            tbl.longest_stay_address = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Longest_Stay_Address");
                            tbl.LOA_Status           = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "LOA_Status");

                            College_CVT         = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "College_CVT_UG");
                            College_UT          = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "College_UT_UG");
                            Degree_CVT          = ""; // Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Degree_Type_CVT");
                            Degree_UT           = ""; // Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Degree_Type_UT");
                            tbl.college_cvt     = College_CVT;
                            tbl.college_ut      = College_UT;
                            tbl.degree_type_cvt = Degree_CVT;
                            tbl.degree_type_ut  = Degree_UT;

                            tbl.reference_type_cvt = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Reference_Type_CVT");
                            tbl.reference_type_ut  = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Reference_Type_UT");
                            tbl.company_name_cvt   = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Company_Name_CVT_Previous");
                            tbl.company_name_ut    = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Company_Name_UT_Previous");
                            tbl.id_cvt             = "";  // Read_Json_Case_Creation_TagValue(Response_Data, "Data", "ID_CVT");
                            tbl.id_ut = "";               //Read_Json_Case_Creation_TagValue(Response_Data, "Data", "ID_UT");
                            tbl.employment_type_cvt = ""; // Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Employment_Type_CVT");
                            tbl.employment_type_ut  = ""; // Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Employment_Type_UT");

                            string College_UT_PG            = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "College_UT_PG");
                            string College_CVT_PG           = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "College_CVT_PG");
                            string BU                       = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "BU");
                            string Company_Name_UT_Current  = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Company_Name_UT_Current");
                            string Company_Name_CVT_Current = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Company_Name_CVT_Current");
                            string FilePath                 = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "FilePath");
                            string LOA_Present              = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "LOA_Present");


                            lst.Add(tbl);
                            int intValue = objDML.Add_Response_Json(reqId, lst, Response_Data);
                            if (intValue > 0)
                            {
                                // Update college detail //
                                List <Yet2Start_College> lstCollege = new List <Yet2Start_College>();
                                Yet2Start_College        objCollege = new Yet2Start_College();

                                string[] strCollegeCVT = College_CVT.Split(new char[] { '+' });
                                string[] strCollegeUT  = College_UT.Split(new char[] { '+' });
                                string[] strDegreeCVT  = Degree_CVT.Split(new char[] { '+' });
                                string[] strDegreeUT   = Degree_UT.Split(new char[] { '+' });

                                for (int i = 0; i < strCollegeCVT.Length; i++)
                                {
                                    objCollege              = new Yet2Start_College();
                                    objCollege.college      = strCollegeCVT[i].ToString().Trim();
                                    objCollege.degree       = strDegreeCVT[i].ToString().Trim();
                                    objCollege.field_source = "CVT";
                                    lstCollege.Add(objCollege);
                                }
                                for (int i = 0; i < strCollegeCVT.Length; i++)
                                {
                                    objCollege              = new Yet2Start_College();
                                    objCollege.college      = strCollegeUT[i].ToString().Trim();
                                    objCollege.degree       = strDegreeUT[i].ToString().Trim();
                                    objCollege.field_source = "UT";
                                    lstCollege.Add(objCollege);
                                }

                                // Add this into database Table //
                                long resId = obj.Get_FreshCase_Response_Id(reqId.ToString());
                                if (resId > 0)
                                {
                                    intValue = objDML.Add_Response_Json(reqId, resId, lstCollege);
                                    if (intValue > 0)
                                    {
                                        //////////////////////////////// CASE CREATION JSON FINAL ////////////////////
                                        Create_Case_Creation_Json(reqId, resId, "", "", "", "");
                                    }
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                output = "ex";
                int iException = objDML.Add_Exception_Log(ex.Message, "");
            }
            return(output);
        }
        public string Create_Case_Creation_Json_For_FreshCase()
        {
            string           output = "";
            long             ResId  = 0;
            long             ReqId  = 0;
            DML_Utility      objDML = new DML_Utility();
            Get_Data_Utility obj    = new Get_Data_Utility();

            try
            {
                List <tbl_response_detail> lstResponse = obj.Get_Response_Data_ToBe_Process(FilePath_Container.FreshCase);
                if (lstResponse.Count > 0)
                {
                    //objDML.Add_Exception_Log(lstResponse.Count.ToString(), "");

                    string MessageId = "";
                    //MessageId.Split('a').Distinct;
                    string ServiceId = "";

                    string ResJson = "";
                    foreach (var res in lstResponse)
                    {
                        MessageId = res.message_id;
                        ServiceId = res.service_id;
                        ResId     = res.id;
                        ReqId     = (long)res.request_id;
                        ResJson   = res.response_json;
                    }
                    // objDML.Add_Exception_Log("Before API Call", "");

                    string responce_MessageId = "";
                    string responce_ServiceId = "";
                    string Response_Data      = "";
                    string path = Read_Json_Case_Creation(ResJson, out responce_MessageId, out responce_ServiceId);
                    //objDML.Add_Exception_Log(responce_MessageId, responce_ServiceId);

                    string status = Read_Json_Case_Creation_TagValue(ResJson, "Status", "Value");
                    //objDML.Add_Exception_Log(responce_MessageId, responce_ServiceId);

                    //objDML.Add_Exception_Log(status, "");
                    //objDML.Add_Exception_Log(responce_ServiceId, "");
                    fadv_touchlessEntities entit = new fadv_touchlessEntities();


                    if (status == "0000")
                    {
                        if (responce_ServiceId.ToLower() == "freshcase")
                        {
                            //objDML.Add_Exception_Log("Before API Call", ResJson);

                            string reqID = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "request_id");
                            List <tbl_input_request_data> InputData = entit.tbl_input_request_data.Where(x => x.Request_ID == reqID).ToList <tbl_input_request_data>();
                            //List<tbl_input_request_data> InputData = entit.tbl_input_request_data.Where(x => x.Request_ID == reqID).ToList<tbl_input_request_data>();



                            List <tbl_yettostart_casecreation_data> lst = new List <tbl_yettostart_casecreation_data>();
                            tbl_yettostart_casecreation_data        tbl = new tbl_yettostart_casecreation_data();
                            string College_CVT = "";
                            string College_UT  = "";
                            string Degree_CVT  = "";
                            string Degree_UT   = "";
                            Response_Data = ResJson;

                            //objDML.Add_Exception_Log("Before API Call", InputData.Count.ToString());

                            string Account         = (Read_Json_Case_Creation_TagValue(Response_Data, "Data", "account_name") == "") ? InputData[0].Account : Read_Json_Case_Creation_TagValue(Response_Data, "Data", "account_name");
                            string AccountGroup    = InputData[0].Account_Group;
                            string vbgType         = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "bvg_type");
                            string bvgsubtype      = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "bvg_sub_type");
                            string bu              = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "BU");
                            string labelName       = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "candidate_name");
                            string sbu             = "";
                            string outputParametrs = ConfigurationManager.AppSettings["outputParametrs"];
                            Dictionary <string, string> paravalues = getPackageName(Account, vbgType, bu, bvgsubtype, outputParametrs, labelName);
                            string PackageName = (paravalues.Keys.Contains("package")) ? paravalues["package"] : "";
                            sbu = (paravalues.Keys.Contains("sbu")) ? paravalues["sbu"] : "";
                            objDML.Add_Exception_Log(PackageName, "");

                            tbl.queue_request_id        = ReqId;
                            tbl.responseId              = ResId;
                            tbl.cognizent_tech_solution = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "cognizent_tech_solution");
                            tbl.clientcode              = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "clientcode");
                            tbl.candidate_name          = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "candidate_name");
                            tbl.client_ref_no           = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "client_ref_no");
                            tbl.bvg_type             = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "bvg_type");
                            tbl.package              = PackageName;// Read_Json_Case_Creation_TagValue(Response_Data, "Data", "package");
                            tbl.specification        = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "specification");
                            tbl.project_id           = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "project_id");
                            tbl.project_name         = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "project_name");
                            tbl.doj                  = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "doj");
                            tbl.request_id           = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "request_id");
                            tbl.associate_id         = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "associate_id");
                            tbl.candidate_id         = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "candidate_id");
                            tbl.employee_id          = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "employee_id");
                            tbl.account_name         = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "account_name");
                            tbl.tensse               = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "tensse");
                            tbl.actual_case_created  = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "actual_case_created");
                            tbl.first_name           = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "First_Name");
                            tbl.last_name            = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Last_Name");
                            tbl.date_of_birth        = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Date_Of_Birth");
                            tbl.father_name          = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Father_Name");
                            tbl.nationality          = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Nationality");
                            tbl.mobile_number        = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Mobile_Number");
                            tbl.current_address      = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Current_Address");
                            tbl.permanent_address    = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Permanent_Address");
                            tbl.longest_stay_address = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Longest_Stay_Address");
                            tbl.LOA_Status           = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "LOA_Status");

                            College_CVT         = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "College_CVT_UG");
                            College_UT          = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "College_UT_UG");
                            Degree_CVT          = ""; // Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Degree_Type_CVT");
                            Degree_UT           = ""; // Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Degree_Type_UT");
                            tbl.college_cvt     = College_CVT;
                            tbl.college_ut      = College_UT;
                            tbl.degree_type_cvt = Degree_CVT;
                            tbl.degree_type_ut  = Degree_UT;

                            tbl.reference_type_cvt = ""; // Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Reference_Type_CVT");
                            tbl.reference_type_ut  = ""; //Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Reference_Type_UT");
                            tbl.company_name_cvt   = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Company_Name_CVT_Previous");
                            tbl.company_name_ut    = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Company_Name_UT_Previous");
                            tbl.id_cvt             = "";  // Read_Json_Case_Creation_TagValue(Response_Data, "Data", "ID_CVT");
                            tbl.id_ut = "";               //Read_Json_Case_Creation_TagValue(Response_Data, "Data", "ID_UT");
                            tbl.employment_type_cvt = ""; // Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Employment_Type_CVT");
                            tbl.employment_type_ut  = ""; // Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Employment_Type_UT");

                            string College_UT_PG            = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "College_UT_PG");
                            string College_CVT_PG           = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "College_CVT_PG");
                            string BU                       = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "BU");
                            string Company_Name_UT_Current  = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Company_Name_UT_Current");
                            string Company_Name_CVT_Current = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Company_Name_CVT_Current");
                            string FilePath                 = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "FilePath").Replace(@"\", @"\\");
                            string LOA_Present              = Read_Json_Case_Creation_TagValue(Response_Data, "Data", "LOA_Present");
                            string clientID                 = (Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Client_ID") == "") ? "1400000003" : Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Client_ID");
                            string clientName               = (Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Client_Name") == "") ? "Cognizant Technology Solutions (IT)" : Read_Json_Case_Creation_TagValue(Response_Data, "Data", "Client_Name");

                            string SBUIID = (Read_Json_Case_Creation_TagValue(Response_Data, "Data", "SBU_ID") == "") ? "Cognizant Technology Solutions (IT)" : Read_Json_Case_Creation_TagValue(Response_Data, "Data", "SBU_ID");;
                            tbl.active = 1;
                            lst.Add(tbl);

                            int intValue = objDML.Add_Response_Json_FreshCase_Update(ReqId, ResId, lst, Response_Data);
                            //objDML.Add_Exception_Log("After Yet", "");
                            if (intValue > 0)
                            {
                                // Update college detail //
                                List <Yet2Start_College> lstCollege = new List <Yet2Start_College>();
                                Yet2Start_College        objCollege = new Yet2Start_College();

                                string[] strCollegeCVT = College_CVT.Split(new char[] { '+' });
                                string[] strCollegeUT  = College_UT.Split(new char[] { '+' });
                                string[] strDegreeCVT  = Degree_CVT.Split(new char[] { '+' });
                                string[] strDegreeUT   = Degree_UT.Split(new char[] { '+' });

                                for (int i = 0; i < strCollegeCVT.Length; i++)
                                {
                                    objCollege              = new Yet2Start_College();
                                    objCollege.college      = strCollegeCVT[i].ToString().Trim();
                                    objCollege.degree       = strDegreeCVT[i].ToString().Trim();
                                    objCollege.field_source = "CVT";
                                    lstCollege.Add(objCollege);
                                }
                                //objDML.Add_Exception_Log("After strCollegeCVT", "");

                                for (int i = 0; i < strCollegeUT.Length; i++)
                                {
                                    objCollege              = new Yet2Start_College();
                                    objCollege.college      = strCollegeUT[i].ToString().Trim();
                                    objCollege.degree       = strDegreeUT[i].ToString().Trim();
                                    objCollege.field_source = "UT";
                                    lstCollege.Add(objCollege);
                                }
                                //objDML.Add_Exception_Log("After strCollegeUT", "");

                                // Add this into database Table //
                                intValue = objDML.Add_Response_Json(ReqId, ResId, lstCollege);
                                if (intValue > 0)
                                {
                                    //////////////////////////////// CASE CREATION JSON FINAL ////////////////////
                                    output = Create_Case_Creation_Json(ReqId, ResId, FilePath, clientID, SBUIID, clientName);
                                    if (output.ToLower() == "success")
                                    {
                                        // Update Status in Response Table //
                                        objDML.Update_Response_Status(ResId);
                                    }
                                }
                                //objDML.Add_Exception_Log("After success", "");
                            }
                        }
                    }
                    else if (status == "0001")
                    {
                        using (fadv_touchlessEntities entities = new fadv_touchlessEntities())
                        {
                            string ReqMsg = Read_Json_Case_Creation_TagValue(ResJson, "Data", "Request_Id");
                            //objDML.Add_Exception_Log(ReqMsg, "");

                            tbl_initiation_tracker processData = entities.tbl_initiation_tracker.Where(x => x.request_id == ReqMsg.Trim()).First();
                            processData.active = 0;
                            entities.SaveChanges();
                            //objDML.Add_Exception_Log("After FailedJson", "");
                        }
                    }
                    objDML.Update_Response_Status(ResId);
                }
            }
            catch (Exception ex)
            {
                output = "ex";
                objDML.Update_Response_Status(ResId);
                //throw ex;
                int iException = objDML.Add_Exception_Log(ex.Message, "Create_Case_Creation_Json_For_FreshCase");
            }

            return(output);
        }