//koushik code
public int Insert_Json_in_requestracker(string RequestID, string json = "")
{
int Value = 0;
fadv_touchlessEntities entit = new fadv_touchlessEntities();
tbl_request_tracker tbl = new tbl_request_tracker();
List <tbl_request_tracker> lst = new List <tbl_request_tracker>();
try
{
//tbl.RequestID = Convert.ToUInt32(RequestID);
tbl.Type = "";
tbl.Json_Data = json;
tbl.Operation_Date = DateTime.Now;
lst.Add(tbl);
entit.tbl_request_tracker.AddRange(lst);
entit.SaveChanges();
Value = 1;
}
catch (Exception ex)
{
Value = 0;
throw ex;
}
return(Value);
}
//koushik code
public int Insert_FilePathIndocument_upload(List<string> Files, long requestID)
{
int Value = 0;
fadv_touchlessEntities entit = new fadv_touchlessEntities();
tbl_document_upload tbl = new tbl_document_upload();
List<tbl_document_upload> lst = new List<tbl_document_upload>();
DML_Utility objDML = new DML_Utility();
try
{
foreach (var file in Files)
{
tbl = new tbl_document_upload();
tbl.Document_Path = file;
tbl.Date_Created = DateTime.Now;
tbl.Date_Modified = DateTime.Now;
tbl.PartitionKey = 0;
tbl.Requestid = requestID;
lst.Add(tbl);
}
entit.tbl_document_upload.AddRange(lst);
entit.SaveChanges();
Value = 1;
}
catch (Exception ex)
{
objDML.Add_Exception_Log(ex.Message, "Insert_FilePathIndocument_upload");
Value = 0;
throw ex;
}
return Value;
}
public int Add_Request_Json_Detail(string message_id, string request_type, string json = "")
{
int Value = 0;
fadv_touchlessEntities entit = new fadv_touchlessEntities();
tbl_request_details tbl = new tbl_request_details();
List <tbl_request_details> lst = new List <tbl_request_details>();
try
{
tbl.messageid = message_id;
tbl.json_text = json;
tbl.request_type = request_type;
lst.Add(tbl);
entit.tbl_request_details.AddRange(lst);
entit.SaveChanges();
Value = 1;
}
catch (Exception ex)
{
Value = 0;
throw ex;
}
return(Value);
}
public int Insert_Json_in_requesStateInstanse(long RequestID, int sequence, string state, long userID, string comment, sbyte isCurrent, sbyte IsReview, sbyte PartitionKey)
{
int Value = 0;
fadv_touchlessEntities entit = new fadv_touchlessEntities();
tbl_request_state_instance tbl = new tbl_request_state_instance();
try
{
tbl.RequestID = RequestID;
tbl.Sequence = sequence;// 1;
tbl.StateID = state;// "REQ-0002";
tbl.UserID = userID;// 165;
tbl.Date_Created = DateTime.Now;
tbl.Comments = comment;// "Case Creation by Touchless";
tbl.Is_Current = isCurrent;// 1;
tbl.IS_Review = IsReview;
tbl.PartitionKey = PartitionKey;
entit.tbl_request_state_instance.Add(tbl);
entit.SaveChanges();
Value = 1;
}
catch (Exception ex)
{
Value = 0;
throw ex;
}
return Value;
}
public DataTable updateStateID(string requestid)
{
fadv_touchlessEntities entity = new fadv_touchlessEntities();
string sql = @"select p.PackageID from tbl_packages p inner join tbl_request_packages rp on p.PackageID = rp.PackageID and LCASE(p.DataEntry_Type)='cde'
inner join tbl_requests r on r.RequestID = rp.RequestID and r.Is_Active = 1
where rp.RequestID = " + requestid + "";
string sql2 = @"select 1 as isAuto from tbl_packages p inner
join tbl_request_packages rp on
p.PackageID = rp.PackageID and LCASE(p.DataEntry_Type) = 'auto'
inner join tbl_requests r on r.RequestID = rp.RequestID and r.Is_Active = 1
where rp.RequestID = " + requestid + "";
if (getIsExist(sql) > 0)
{
execcute_usp_insert_case_history(requestid, "TL-0003", "0", DateTime.Now.ToString(), "Task Awaiting for CDE");
}
else if (getIsExist(sql2) > 0)
{
execcute_usp_insert_case_history(requestid, "TL-0010", "0", DateTime.Now.ToString(), "Task Allocated by Robo");
}
else
{
execcute_usp_insert_case_history(requestid, "TL-0004", "0", DateTime.Now.ToString(), "Task Allocated by Robo");
}
return(new DataTable());
}
private void execcute_usp_insert_case_history(string p_requestid, string p_state_masterid, string p_userid, string p_date, string p_message)
{
fadv_touchlessEntities entity = new fadv_touchlessEntities();
int in_Sequence = 0;
string sql = "select 1 from tbl_request_state_instance where RequestID=" + p_requestid + " and Is_Current=1 limit 1";
if (getIsExist(sql) > 0)
{
sql = " select Sequence from tbl_request_state_instance where RequestID=" + p_requestid + " and Is_Current=1 order by Date_Created desc limit 1;";
string seq = entity.Database.SqlQuery <string>(sql).FirstOrDefault();
in_Sequence = Convert.ToInt32(seq) + 1;
}
else
{
in_Sequence = 1;
}
sql = @"update tbl_request_state_instance set `Is_Current`=0 where `requestid`=@p_requestid;
insert into tbl_request_state_instance (`RequestID`,`Sequence`,`StateID`,`UserID`,`Date_Created`,`Comments`,`Is_Current`,`IS_Review`,`PartitionKey`)
values (@p_requestid,@in_Sequence,@p_state_masterid,@p_userid,Now(),@p_message,1,0,0); ";
MySqlParameter[] queryParams = new MySqlParameter[] {
new MySqlParameter("p_requestid", p_requestid),
new MySqlParameter("in_Sequence", in_Sequence),
new MySqlParameter("p_state_masterid", p_state_masterid),
new MySqlParameter("p_userid", p_userid),
new MySqlParameter("p_date", p_date),
new MySqlParameter("p_message", p_message)
};
var sequenceQueryResult = entity.Database.ExecuteSqlCommand(sql, queryParams);
}
public List <tbl_yettostart_casecreation_data> Get_tbl_yettostart_casecreation_data(long ExpressReqID)
{
List <tbl_yettostart_casecreation_data> lstObj = new List <tbl_yettostart_casecreation_data>();
fadv_touchlessEntities entity = new fadv_touchlessEntities();
lstObj = entity.tbl_yettostart_casecreation_data.Where(x => x.ExpressRequestID == ExpressReqID).ToList <tbl_yettostart_casecreation_data>();
return(lstObj);
}
public int Insert_data_in_requests(string RequestID, List <tbl_yettostart_casecreation_data> list)
{
int Value = 0;
fadv_touchlessEntities entit = new fadv_touchlessEntities();
tbl_requests tbl = new tbl_requests();
List <tbl_requests> lst = new List <tbl_requests>();
sbyte Dateofbirth = 0;
int intRequestID = Convert.ToInt32(RequestID);
try
{
foreach (tbl_yettostart_casecreation_data item in list)
{
if (item.first_name != string.Empty)
{
Dateofbirth = 1;
}
tbl.RequestID = Convert.ToUInt32(intRequestID);
// tbl.ClientID = 0;
tbl.First_Name = item.first_name;
tbl.Last_Name = item.last_name;
tbl.Middle_Name = "";
tbl.Client_Ref_No = item.client_ref_no;
tbl.Case_Date = DateTime.Now;
tbl.Subject_Detail = "";
tbl.Subject_Type = "";
tbl.Is_Date_of_Birth = Dateofbirth;
tbl.Date_Of_Birth = Convert.ToDateTime(item.date_of_birth);
tbl.Type_Of_Check = ""; //Pre employment or Post employment or Both
tbl.Candidate_Authorization_Letter = ""; // Yes or No
tbl.Package_Type = ""; // Soft Copy or Hard copy
tbl.Srt_Data = "SRT";
tbl.Date_Created = DateTime.Now;
tbl.Date_Modified = DateTime.Now;
tbl.Is_Active = true;
// tbl.SBUID =0;
tbl.LOA_Submitted = "Yes";
tbl.BVF_Submitted = "Yes";
tbl.PartitionKey = 0;
tbl.JSON_Data = "";
lst.Add(tbl);
Value = 1;
intRequestID++;
}
entit.tbl_requests.AddRange(lst);
entit.SaveChanges();
}
catch (Exception ex)
{
Value = 0;
throw ex;
}
return(Value);
}
public List <DatabaseCheckRecords> getDatabaseCheckRecords(string requestid)
{
fadv_touchlessEntities entity = new fadv_touchlessEntities();
string query = @"select First_Name,Middle_Name,Last_Name,CAST(DATE_FORMAT(Date_Of_Birth, '%d/%m/%Y') AS char) As Date_Of_Birth,JSON_Data,a.ClientID,Is_Bulk from tbl_requests a inner join
tbl_client_master b on a.ClientID = b.ClientID and b.is_active = 1 where a.RequestID = " + requestid;
var dt = entity.Database.SqlQuery <DatabaseCheckRecords>(query, requestid).ToList();
return(dt);
}
public bool IsDuplicate(string Candidte_Id, string Check_Initiated)
{
fadv_touchlessEntities entity = new fadv_touchlessEntities();
//lstObj = entity.tbl_input_request_data.SqlQuery("select * from tbl_input_request_data where ImportKey=" + ImportKey + " and Request_ID not in (select request_id from tbl_initiation_tracker)").ToList<tbl_input_request_data>();
string Query = "select * from tbl_Wipro_details where Candidte_Id=" + Candidte_Id + " and Check_Initiated='" + Check_Initiated + "'";
List <tbl_Wipro_Details> list = entity.Database.SqlQuery <tbl_Wipro_Details>(Query).ToList <tbl_Wipro_Details>();;
return((list.Count > 1) ? true : false);
}
public List <tbl_response_detail> Get_Response_Data_ToBe_Process(string ServiceId)
{
List <tbl_response_detail> lstObj = new List <tbl_response_detail>();
fadv_touchlessEntities entity = new fadv_touchlessEntities();
lstObj = entity.tbl_response_detail.SqlQuery("SELECT * FROM fadv_touchless.tbl_response_detail where status=1 and service_id='" + ServiceId + "' order by CreatedOn desc limit 1;").ToList <tbl_response_detail>();
return(lstObj);
}
public bool IsLateralDuplicate(string ResumeID)
{
fadv_touchlessEntities entity = new fadv_touchlessEntities();
//lstObj = entity.tbl_input_request_data.SqlQuery("select * from tbl_input_request_data where ImportKey=" + ImportKey + " and Request_ID not in (select request_id from tbl_initiation_tracker)").ToList<tbl_input_request_data>();
string Query = "select * from tbl_wipro_lateral_data where `Resume Number`='" + ResumeID + "'";
List <tbl_wipro_lateral_data> list = entity.Database.SqlQuery <tbl_wipro_lateral_data>(Query).ToList <tbl_wipro_lateral_data>();;
return((list.Count > 1) ? true : false);
}
public List <tbl_input_request_data> Get_New_Request_Id_List1(string ImportKey)
{
List <tbl_input_request_data> lstObj = new List <tbl_input_request_data>();
fadv_touchlessEntities entity = new fadv_touchlessEntities();
lstObj = entity.tbl_input_request_data.SqlQuery("select * from tbl_input_request_data where ImportKey=" + ImportKey + " and Request_ID not in (select request_id from tbl_initiation_tracker)").ToList <tbl_input_request_data>();
return(lstObj);
}
public List <tbl_wipro_contract_data> Get_UnProcessedContractRequests()
{
fadv_touchlessEntities entity = new fadv_touchlessEntities();
//lstObj = entity.tbl_input_request_data.SqlQuery("select * from tbl_input_request_data where ImportKey=" + ImportKey + " and Request_ID not in (select request_id from tbl_initiation_tracker)").ToList<tbl_input_request_data>();
string Query = "select `Resume Number` as ResumeNumber, `Candidate Name` as CandidateName from tbl_wipro_contract_data where IsProcessed=0;";
List <tbl_wipro_contract_data> list = entity.Database.SqlQuery <tbl_wipro_contract_data>(Query).ToList <tbl_wipro_contract_data>();;
return(list);
}
public List <string> Get_AutoDataCaseCreationJson(string sql)
{
fadv_touchlessEntities entity = new fadv_touchlessEntities();
DataTable retVal = new DataTable();
retVal = entity.Database.SqlQuery <DataTable>(sql).FirstOrDefault();
var sequenceQueryResult = entity.Database.SqlQuery <string>(sql).ToList();
return(sequenceQueryResult);
}
public int getIsExist(string sql)
{
fadv_touchlessEntities entity = new fadv_touchlessEntities();
DataTable retVal = new DataTable();
//string sql = "select count(*) from tbl_document_data where RequestID=" + rquestID + ";";
//retVal = entity.Database.SqlQuery<DataTable>(sql).FirstOrDefault();
var sequenceQueryResult = entity.Database.SqlQuery <string>(sql).ToList();
return((sequenceQueryResult.Count == 0) ? 0 : Convert.ToInt32(sequenceQueryResult[0]));
}
public List <string> GetDocumentDataCount(string rquestID)
{
fadv_touchlessEntities entity = new fadv_touchlessEntities();
DataTable retVal = new DataTable();
string sql = "select count(*) from tbl_document_data where RequestID=" + rquestID + ";";
retVal = entity.Database.SqlQuery <DataTable>(sql).FirstOrDefault();
var sequenceQueryResult = entity.Database.SqlQuery <string>(sql).ToList();
return(sequenceQueryResult);
}
public int Check_Rule_Engine_CVT_UT(long request_id, long response_id, List <Yet2Start_College> lst)
{
int Value = 0;
try
{
Get_Data_Utility objGet = new Get_Data_Utility();
foreach (var obj in lst)
{
int cvt = 0;
int ut = 0;
string college = obj.college.Trim();
string degree = obj.degree.Trim();
sbyte active = 1;
List <tbl_college_details> lstResponse = new List <tbl_college_details>();
lstResponse = objGet.Get_College_Details(request_id, response_id, college, degree);
foreach (var cd in lstResponse)
{
long cdid = cd.id;
string fieldsource = cd.field_source.Trim();
if (cd.field_source.ToLower() == "cvt")
{
cvt = 1;
}
else if (cd.field_source.ToLower() == "ut")
{
ut = 1;
}
if (cvt > 0 & ut > 0)
{
using (fadv_touchlessEntities entit = new fadv_touchlessEntities())
{
List <tbl_college_details> processData = entit.tbl_college_details.Where(x => x.field_source == "UT" && x.reqid == request_id && x.resid == response_id && x.college == college && x.degree == degree).ToList <tbl_college_details>();
foreach (var uddt in processData)
{
tbl_college_details updatedata = entit.tbl_college_details.Where(x => x.id == uddt.id).First();
updatedata.active = 9;
entit.SaveChanges();
}
}
}
}
}
Value = 1;
}
catch (Exception ex)
{
Value = 0;
throw ex;
}
return(Value);
}
public List <tbl_config_value> Get_Cofig_Details(string configType)
{
List <tbl_config_value> lstObj = new List <tbl_config_value>();
fadv_touchlessEntities entity = new fadv_touchlessEntities();
try
{
lstObj = entity.tbl_config_value.Where(x => x.configtype == configType).ToList <tbl_config_value>();
}
catch (Exception ex)
{
throw ex;
}
return(lstObj);
}
public List <tbl_request_details> Get_Last_Request()
{
List <tbl_request_details> lstObj = new List <tbl_request_details>();
string Query = "select * from tbl_request_details where request_type='download' order by CreatedOn desc limit 1";
fadv_touchlessEntities entity = new fadv_touchlessEntities();
try
{
lstObj = entity.tbl_request_details.SqlQuery(Query).ToList <tbl_request_details>();
}
catch (Exception ex)
{
throw ex;
}
return(lstObj);
}
public List <tbl_request_state_instance> GetAllOpenState()
{
List <tbl_request_state_instance> lstObj = new List <tbl_request_state_instance>();
fadv_touchlessEntities entity = new fadv_touchlessEntities();
string Query = "select * from tbl_request_state_instance where Is_Current=1 and requestID in(select requestID from tbl_request_state_instance where Comments= 'Case Created by Touchless');";
try
{
lstObj = entity.tbl_request_state_instance.SqlQuery(Query).ToList <tbl_request_state_instance>();
}
catch (Exception ex)
{
throw ex;
}
return(lstObj);
}
public List <tbl_document_data> Get_DataEntry(string requesID)
{
List <tbl_document_data> lstObj = new List <tbl_document_data>();
string Query = "select * from tbl_document_data Where RequestID=" + requesID;
fadv_touchlessEntities entity = new fadv_touchlessEntities();
try
{
lstObj = entity.tbl_document_data.SqlQuery(Query).ToList <tbl_document_data>();
}
catch (Exception ex)
{
throw ex;
}
return(lstObj);
}
// koushik code
public List <tbl_requests> Get_RequestID()
{
List <tbl_requests> lstObj = new List <tbl_requests>();
string Query = "select * from tbl_requests order by RequestID desc limit 1";
fadv_touchlessEntities entity = new fadv_touchlessEntities();
try
{
lstObj = entity.tbl_requests.SqlQuery(Query).ToList <tbl_requests>();
}
catch (Exception ex)
{
throw ex;
}
return(lstObj);
}
public List <tbl_college_details> Get_College_Details(long reqid, long resid, string college, string degree)
{
List <tbl_college_details> lstObj = new List <tbl_college_details>();
string Query = "select * from tbl_college_details where active=1 and reqid=" + reqid + " and resid=" + resid + " and college='" + college + "' and degree='" + degree + "' order by field_source";
fadv_touchlessEntities entity = new fadv_touchlessEntities();
try
{
lstObj = entity.tbl_college_details.SqlQuery(Query).ToList <tbl_college_details>();
}
catch (Exception ex)
{
throw ex;
}
return(lstObj);
}
public long Get_Request_Id_new(string strMessageId, string RequestType)
{
List <tbl_request_details> lstObj = new List <tbl_request_details>();
string Query = "select * from tbl_request_details where messageid='" + strMessageId + "' and request_type = '" + RequestType + "'";
//Query = "select * from tbl_request_details where messageid='" + strMessageId + "'";
fadv_touchlessEntities entity = new fadv_touchlessEntities();
try
{
lstObj = entity.tbl_request_details.SqlQuery(Query).ToList <tbl_request_details>();
return(lstObj.Count > 0 ? string.IsNullOrEmpty(lstObj[0].id.ToString()) ? 0 : long.Parse(lstObj[0].id.ToString()) : 0);
}
catch (Exception ex)
{
throw ex;
}
}
public long Get_FreshCase_Response_Id(string strRequestId)
{
List <tbl_yettostart_casecreation_data> lstObj = new List <tbl_yettostart_casecreation_data>();
tbl_yettostart_casecreation_data tbl = new tbl_yettostart_casecreation_data();
string Query = "select * from tbl_yettostart_casecreation_data where queue_request_id='" + strRequestId + "'";
fadv_touchlessEntities entity = new fadv_touchlessEntities();
try
{
lstObj = entity.tbl_yettostart_casecreation_data.SqlQuery(Query).ToList <tbl_yettostart_casecreation_data>();
return(lstObj.Count > 0 ? string.IsNullOrEmpty(lstObj[0].id.ToString()) ? 0 : long.Parse(lstObj[0].id.ToString()) : 0);
}
catch (Exception ex)
{
throw ex;
}
}
public int Add_Response_Json(long request_id, long response_id, List <Yet2Start_College> lst)
{
int Value = 0;
try
{
if (lst.Count > 0)
{
tbl_college_details tbl = new tbl_college_details();
List <tbl_college_details> lstResponse = new List <tbl_college_details>();
foreach (var obj in lst)
{
string college = obj.college.Trim();
string degree = obj.degree.Trim();
sbyte active = 1;
tbl = new tbl_college_details();
tbl.college = college.Trim();
tbl.degree = degree.Trim();
tbl.field_source = obj.field_source.Trim();
tbl.reqid = request_id;
tbl.resid = response_id;
tbl.active = active;
lstResponse.Add(tbl);
}
using (fadv_touchlessEntities entit = new fadv_touchlessEntities())
{
entit.tbl_college_details.AddRange(lstResponse);
entit.SaveChanges();
}
// Check for Check_Rule_Engine_CVT_UT //
Check_Rule_Engine_CVT_UT_New(request_id, response_id, lst);
//Check_Rule_Engine_CVT_UT(request_id, response_id, lst);
}
Value = 1;
}
catch (Exception ex)
{
Value = 0;
throw ex;
}
return(Value);
}
public int updateYetTostart(long resId, long ExpresReqID)
{
int Value = 0;
try
{
fadv_touchlessEntities entit = new fadv_touchlessEntities();
tbl_yettostart_casecreation_data processData = entit.tbl_yettostart_casecreation_data.Where(x => x.queue_request_id == resId).First();
processData.ExpressRequestID = ExpresReqID;
entit.SaveChanges();
Value = 1;
}
catch (Exception ex)
{
Value = 0;
throw ex;
}
return Value;
}
public string getPackageID(string package, string SBUID)
{
DML_Utility objDML = new DML_Utility();
try
{
fadv_touchlessEntities entity = new fadv_touchlessEntities();
DataTable retVal = new DataTable();
string sql = "select PackageID from tbl_packages where Package_name='" + package + "' and SBUID=" + SBUID + ";";
retVal = entity.Database.SqlQuery <DataTable>(sql).FirstOrDefault();
var sequenceQueryResult = entity.Database.SqlQuery <string>(sql).ToList();
return(sequenceQueryResult[0]);
}
catch (Exception ex)
{
objDML.Add_Exception_Log("Wipro exception : " + "Package is not found in CSPI Express for" + package + "And" + SBUID, ex.Message);
throw;
}
}
public int Update_Response_Status(long resId)
{
int Value = 0;
try
{
using (fadv_touchlessEntities entities = new fadv_touchlessEntities())
{
tbl_response_detail processData = entities.tbl_response_detail.Where(x => x.id == resId).First();
processData.status = 2;
entities.SaveChanges();
}
Value = 1;
}
catch (Exception ex)
{
Value = 0;
throw ex;
}
return Value;
}