public DataTable GetRequestIDandprocess(string strProjectKey, string strLeaseKey) { string multicolumnjson = string.Empty; DataTable DtALLRequestIDfromLog = new DataTable(); try { string ActivityLogSectionName = Convert.ToString(_configuration.GetSection("REmaapSettings").GetSection("ActivityLogSectionName").Value); string ActivityLogFeildName = Convert.ToString(_configuration.GetSection("REmaapSettings").GetSection("ActivityLogFeildName").Value); dbc = new DBConnection(_configuration.GetSection("REmaapSettings").GetSection("strProjectsConn").Value.Replace("#PROJECTDB#", "REmaap_" + strProjectKey)); DataTable DtGetActivityLogFldNames = dbc.GenerateDataTable("SELECT DTL.FIELD_NAME,DTL.FIELD_DESCRIPTION,HDR.TABLE_NAME,DTL.FKEY_MASTER_SECTION, DTL.FKEY_MASTER_FIELD FROM " + "tbl_section_hdr HDR JOIN tbl_section_DTL DTL ON HDR.PKEY = DTL.FKEY_PARENT WHERE SECTION_NAME ='" + ActivityLogSectionName + "' AND IFNULL(is_hidden, '') = 'N' AND FIELD_DESCRIPTION IN(" + ActivityLogFeildName + ")"); if (DtGetActivityLogFldNames.Rows.Count > 0) { if (DtGetActivityLogFldNames.Select("FIELD_DESCRIPTION='Request ID'").Length > 0 && DtGetActivityLogFldNames.Select("FIELD_DESCRIPTION='Process Status'").Length > 0 && DtGetActivityLogFldNames.Select("FIELD_DESCRIPTION='Process'").Length > 0) { string StrReqIDName = Convert.ToString(DtGetActivityLogFldNames.Select("FIELD_DESCRIPTION='Request ID'")[0]["FIELD_NAME"]); string StrProcessStatusIDName = Convert.ToString(DtGetActivityLogFldNames.Select("FIELD_DESCRIPTION='Process Status'")[0]["FIELD_NAME"]); string StrTrackIDName = Convert.ToString(DtGetActivityLogFldNames.Select("FIELD_DESCRIPTION='Workflow track'")[0]["FIELD_NAME"]); string StrProcessIDName = Convert.ToString(DtGetActivityLogFldNames.Select("FIELD_DESCRIPTION='Process'")[0]["FIELD_NAME"]); string StrMasterSectionID = Convert.ToString(DtGetActivityLogFldNames.Select("FIELD_DESCRIPTION='Process'")[0]["FKEY_MASTER_SECTION"]); string StrFkeyMasterFieldID = Convert.ToString(DtGetActivityLogFldNames.Select("FIELD_DESCRIPTION='Process'")[0]["FKEY_MASTER_FIELD"]); string StrMasterTblName = dbc.retrieveScalar("SELECT TABLE_NAME FROM TBL_SECTION_HDR WHERE pkey IN " + "(" + StrMasterSectionID + ")"); string StrMasterFieldName = dbc.retrieveScalar("SELECT FIELD_NAME FROM TBL_SECTION_DTL WHERE PKEY IN" + "(" + StrFkeyMasterFieldID + ")"); string StrSectionName = Convert.ToString(DtGetActivityLogFldNames.Rows[0]["TABLE_NAME"]); DtALLRequestIDfromLog = dbc.GenerateDataTable("SELECT DISTINCT A_LOG." + StrReqIDName + " REQUEST_ID,IFNULL(A_LOG." + StrProcessStatusIDName + ",'') Description,IFNULL(ml.Description,'') TrackName,IFNULL(MS." + StrMasterFieldName + ",'') PROCESS_TYPE,MS.pkey AS PROCESS_TYPE_KEY FROM " + "REmaap_" + strProjectKey + "." + StrSectionName + " A_LOG LEFT JOIN REmaap_" + strProjectKey + ".Masterlist ML ON A_LOG." + StrTrackIDName + "= ML.PKEY " + "LEFT JOIN REmaap_" + strProjectKey + "." + StrMasterTblName + " MS ON MS.PKEY = A_LOG." + StrProcessIDName + " " + "WHERE A_LOG.fkey_parent IN('" + strLeaseKey + "') ORDER BY CAST(IFNULL(A_LOG." + StrReqIDName + ", 0) AS SIGNED) DESC;"); DataTable DtGetAcCompletedWf = dbc.GenerateDataTable("SELECT IFNULL(WS1.Description,0) Description,IFNULL(WTM.TRACK_Description,'') Track_Description,Wtm.PKEY FROM " + "REmaap_" + strProjectKey + ".WorkFlow_Stages WS1 JOIN REmaap_" + strProjectKey + ".WORKFLOW_RULES_DTL DTL ON DTL.FKEY_WORKFLOW = WS1.PKEY " + "JOIN remaap_mvc_master.WORKFLOW_RULES_HDR HDR ON HDR.PKEY = DTL.FKEY_PARENT " + "JOIN REmaap_" + strProjectKey + ".workflow_trackmaster WTM ON WTM.PKEY = DTL.FKEY_TRACKMASTER " + "WHERE HDR.STATIC_RULES = 'Activity Complete' AND HDR.IS_ACTIVE = 'Y' AND WTM.IS_ACTIVE = 'Y'"); foreach (DataRow _Dr in DtGetAcCompletedWf.Rows) { if (DtALLRequestIDfromLog.AsEnumerable().Where(A1 => (Convert.ToString(A1["Description"]).ToUpper().Trim() == Convert.ToString(_Dr["Description"]).ToUpper().Trim()) && (Convert.ToString(A1["TrackName"]).ToUpper().Trim() == Convert.ToString(_Dr["Track_Description"]).ToUpper().Trim())).Count() > 0) { DtALLRequestIDfromLog.Rows.Cast().Where(A1 => (Convert.ToString(A1["Description"]).ToUpper().Trim() == Convert.ToString(_Dr["Description"]).ToUpper().Trim()) && (Convert.ToString(A1["TrackName"]).ToUpper().Trim() == Convert.ToString(_Dr["Track_Description"]).ToUpper().Trim())).ToList().ForEach(r => r.Delete()); DtALLRequestIDfromLog.AcceptChanges(); } } DtALLRequestIDfromLog.Columns.Remove("TrackName"); DtALLRequestIDfromLog.Columns.Remove("Description"); } } //multicolumnjson = _DAL.ConvertToJson(DtALLRequestIDfromLog, false); } catch (Exception ex) { _logDetails.strprojkey = strProjectKey; Log.WriteLog(ex, _logDetails); } return DtALLRequestIDfromLog; } public string GetPicklistvalues(string strProjectKey, string strLeaseKey) { string Status = string.Empty; string Data = string.Empty; string Msg = string.Empty; DBConnection dbcMaster = new DBConnection(_configuration.GetSection("REmaapSettings").GetSection("MasterConnectionString").Value); dbc = new DBConnection(_configuration.GetSection("REmaapSettings").GetSection("strProjectsConn").Value.Replace("#PROJECTDB#", "REmaap_" + strProjectKey)); try { string StrPicklistJson = string.Empty; if (strProjectKey != null && strProjectKey.Trim() != "") { string Excludedsections = Convert.ToString(_configuration.GetSection("REmaapSettings").GetSection("ExcludedSections").Value); DataTable dtSectionNames = dbc.GenerateDataTable("SELECT PKEY ,SECTION_NAME as VALUE FROM TBL_SECTION_HDR WHERE IFNULL(ISACTIVE,'') = 'Y' AND Is_Parent = 0 " + "AND IFNULL(IS_MASTER_SECTION, '') = 'N' AND IFNULL(IS_REQUIREDFORMAPPING, '') <> 'Y' " + " AND SECTION_NAME NOT IN(" + Excludedsections + ") ORDER BY CAST(DISPLAY_ORDER AS UNSIGNED INTEGER) ASC"); DataTable dtFeedbacksource = dbcMaster.GenerateDataTable("SELECT PKEY,FEEDBACK_VALUE AS VALUE FROM report_feedback_master WHERE FEEDBACK_GROUP = 'Feedback_Source' AND IS_ACTIVE = 'Y' ORDER BY display_order"); DataTable dtFeedbacktype = dbcMaster.GenerateDataTable("SELECT PKEY,FEEDBACK_VALUE AS VALUE FROM report_feedback_master WHERE FEEDBACK_GROUP = 'Feedback_Type' AND IS_ACTIVE = 'Y' ORDER BY display_order"); DataTable DtALLRequestIDfromLog = GetRequestIDandprocess(strProjectKey, strLeaseKey); var Full_Source_Data = new { Sections = dtSectionNames, Feedbacksource = dtFeedbacksource, Feedbacktype = dtFeedbacktype, reuqestIdValues = DtALLRequestIDfromLog, }; if (dtSectionNames.Rows.Count > 0 && dtFeedbacksource.Rows.Count > 0 && dtFeedbacktype.Rows.Count > 0) { StrPicklistJson = _DAL.ConvertToJson(Full_Source_Data, false); if (!string.IsNullOrEmpty(StrPicklistJson)) { Status = "\"Status\" : " + "\"Success\""; Data = "\"Data\" : " + StrPicklistJson + ""; Msg = "\"Message\" : " + "\"\""; } else { Status = "\"Status\" : " + "\"Error\""; Data = "\"Data\" : " + "\"\""; Msg = "\"Message\" : \"" + "\"No data found. There are no items available to populate the dropdown.\""; } } } } catch (Exception ex) { _logDetails.strprojkey = strProjectKey; Log.WriteLog(ex, _logDetails); Status = "\"Status\" : " + "\"Error\""; Data = "\"Data\" : " + "\"\""; Msg = "\"Message\" : \"" + ex.Message.ToString() + "\""; } string strResult = "{" + Status + "," + Data + "," + Msg + "}"; return strResult; } public string RecordFeedbackDetails(string strProjectKey, string strLeaseKey) { string Status = string.Empty; string Data = string.Empty; string Msg = string.Empty; string StrMasterSectionID = string.Empty, StrFkeyMasterFieldID = string.Empty, StrMasterTblName = string.Empty, StrMasterFieldName = string.Empty, ActivityLogFieldsQuerry = string.Empty, ActivityLogJoinsQuerry = string.Empty; string ActivityLogSectionName = Convert.ToString(_configuration.GetSection("REmaapSettings").GetSection("ActivityLogSectionName").Value); string ActivityLogFeildName = Convert.ToString(_configuration.GetSection("REmaapSettings").GetSection("ActivityLogFeildName").Value); DBConnection dbcMaster = new DBConnection(_configuration.GetSection("REmaapSettings").GetSection("MasterConnectionString").Value); dbc = new DBConnection(_configuration.GetSection("REmaapSettings").GetSection("strProjectsConn").Value.Replace("#PROJECTDB#", "REmaap_" + strProjectKey)); try { string StrRecordFeedbackJson = string.Empty; if (strProjectKey != null && strLeaseKey != null && strProjectKey.Trim() != "" && strLeaseKey.Trim() != "") { DataTable dtRecordFeedbackHeader = dbcMaster.GenerateDataTable("SELECT PKEY AS pKey,FIELD_NAME AS columnName,FIELD_DESCRIPTION AS columnDescription FROM REPORT_FEEDBACK_FIELDS where IS_ACTIVE = 'Y'"); DataTable DtGetActivityLogFldNames = dbc.GenerateDataTable("SELECT DTL.FIELD_DESCRIPTION,HDR.TABLE_NAME,DTL.FKEY_MASTER_SECTION, DTL.FKEY_MASTER_FIELD FROM " + "tbl_section_hdr HDR JOIN tbl_section_DTL DTL ON HDR.PKEY = DTL.FKEY_PARENT WHERE SECTION_NAME ='" + ActivityLogSectionName + "' AND IFNULL(is_hidden, '') = 'N' AND FIELD_DESCRIPTION IN(" + ActivityLogFeildName + ")"); if (DtGetActivityLogFldNames.Rows.Count > 0) { if (DtGetActivityLogFldNames.Select("FIELD_DESCRIPTION='Request ID'").Length > 0 && DtGetActivityLogFldNames.Select("FIELD_DESCRIPTION='Process Status'").Length > 0 && DtGetActivityLogFldNames.Select("FIELD_DESCRIPTION='Process'").Length > 0) { StrMasterSectionID = Convert.ToString(DtGetActivityLogFldNames.Select("FIELD_DESCRIPTION='Process'")[0]["FKEY_MASTER_SECTION"]); StrFkeyMasterFieldID = Convert.ToString(DtGetActivityLogFldNames.Select("FIELD_DESCRIPTION='Process'")[0]["FKEY_MASTER_FIELD"]); StrMasterTblName = dbc.retrieveScalar("SELECT TABLE_NAME FROM TBL_SECTION_HDR WHERE pkey IN " + "(" + StrMasterSectionID + ")"); StrMasterFieldName = dbc.retrieveScalar("SELECT FIELD_NAME FROM TBL_SECTION_DTL WHERE PKEY IN" + "(" + StrFkeyMasterFieldID + ")"); ActivityLogFieldsQuerry = "rfd.FKEY_REQUEST_ID AS requestId,MS." + StrMasterFieldName + " AS processType,MS.PKEY AS processTypeKey, "; ActivityLogJoinsQuerry = "LEFT JOIN REmaap_" + strProjectKey + "." + StrMasterTblName + " MS ON MS.pkey = rfd.FKEY_PROCESS_TYPE "; } } DataTable dtRecordFeedbackDetail = dbcMaster.GenerateDataTable("SELECT rfd.PKEY,hdr.SECTION_NAME AS section,rfd.FKEY_SECTIONKEY AS sectionKey, rfd.REPORTED_DATE AS reportedDate,M.FEEDBACK_VALUE AS feedbackSource,rfd.FKEY_FEEDBACK_SOURCE AS feedbackSourceKey,rfd.FKEY_FEEDBACK_TYPE AS feedbackTypekey, M2.FEEDBACK_VALUE AS feedbackType,rfd.FEEDBACK_DESCRIPTION AS feedbackComment,rfd.FEEDBACK_RESPONSE_COMMENTS AS responseComment, " + ActivityLogFieldsQuerry + " " + "RFD.ADDED_BY AS userKey, U.USER_NAME AS addedBy,Added_on AS addedOn,U2.USER_NAME AS modifiedBy, Modified_On AS modifiedOn FROM report_feedback_details rfd " + "LEFT JOIN REmaap_" + strProjectKey + ".tbl_section_hdr hdr ON rfd.FKEY_SECTIONKEY = hdr.pkey" + " " + ActivityLogJoinsQuerry + "INNER JOIN report_feedback_master M ON M.pkey = RFD.FKEY_FEEDBACK_SOURCE" + " " + "LEFT JOIN report_feedback_master M2 ON M2.pkey = RFD.FKEY_FEEDBACK_TYPE" + " " + "LEFT JOIN users_master U ON U.PKEY = rfd.Added_by LEFT JOIN users_master U2 ON U2.PKEY = rfd.Modified_by " + " WHERE rfd.FKEY_PROJECT = '" + strProjectKey + "' AND rfd.FKEY_LEASEKEY ='" + strLeaseKey + "' ORDER BY rfd.MODIFIED_ON DESC"); var Full_Source_Data = new { RecordFeedbackHeader = dtRecordFeedbackHeader, RecordFeedbackDetail = dtRecordFeedbackDetail, }; if (dtRecordFeedbackHeader.Rows.Count > 0) { if (Full_Source_Data != null) { StrRecordFeedbackJson = _DAL.ConvertToJson(Full_Source_Data, false); } if (!string.IsNullOrEmpty(StrRecordFeedbackJson)) { Status = "\"Status\" : " + "\"Success\""; Data = "\"Data\" : " + StrRecordFeedbackJson + ""; Msg = "\"Message\" : " + "\"\""; } else { Status = "\"Status\" : " + "\"Error\""; Data = "\"Data\" : " + "\"\""; Msg = "\"Message\" : \"" + "\"An error occurred while loading data.\""; } } } } catch (Exception ex) { _logDetails.strprojkey = strProjectKey; Log.WriteLog(ex, _logDetails); Status = "\"Status\" : " + "\"Error\""; Data = "\"Data\" : " + "\"\""; Msg = "\"Message\" : \"" + ex.Message.ToString() + "\""; } string strResult = "{" + Status + "," + Data + "," + Msg + "}"; return strResult; } public string AddRecordFeedback(JObject RecordfdJsonvalue) { string Status = string.Empty; string Msg = string.Empty; string strResult = string.Empty; string strProjectKey = string.Empty, strSectionkey = string.Empty, strLeasekey = string.Empty, fkeyFeedbacksource = string.Empty, fkeyFeedbacktype = string.Empty, feedbackDescription = string.Empty, feedbackResponsecomments = string.Empty, strUserKey = string.Empty, strReporteddate = string.Empty, strRequestID = string.Empty, strProcessType = string.Empty; List lstQuery = new List(); try { dynamic lstfld = JsonConvert.DeserializeObject(RecordfdJsonvalue.ToString()); dynamic JSONValue = lstfld.CurrentRow; //dynamic JSONValue = JArray.Parse("[" + RecordfdJsonvalue + "]"); DBConnection dbcMaster = new DBConnection(_configuration.GetSection("REmaapSettings").GetSection("MasterConnectionString").Value); if (JSONValue.Count > 0) { strProjectKey = Convert.ToString(JSONValue[0].strProjectKey); strSectionkey = Convert.ToString(JSONValue[0].strSectionkey); strLeasekey = Convert.ToString(JSONValue[0].strLeasekey); fkeyFeedbacksource = Convert.ToString(JSONValue[0].fkeyFeedbacksource); fkeyFeedbacktype = Convert.ToString(JSONValue[0].fkeyFeedbacktype); strReporteddate = Convert.ToString(JSONValue[0].strReporteddate); feedbackDescription = Convert.ToString(JSONValue[0].feedbackDescription); feedbackResponsecomments = Convert.ToString(JSONValue[0].feedbackResponsecomments); strUserKey = Convert.ToString(JSONValue[0].addedBy); strRequestID = Convert.ToString(JSONValue[0].requestId); strProcessType = Convert.ToString(JSONValue[0].processType); } if (!string.IsNullOrEmpty(strProjectKey) && !string.IsNullOrEmpty(strLeasekey) && !string.IsNullOrEmpty(strSectionkey) && !string.IsNullOrEmpty(feedbackDescription) && !string.IsNullOrEmpty(strUserKey) && !string.IsNullOrEmpty(strReporteddate)) { //fkeyFeedbacktype = fkeyFeedbacktype != "0" ? fkeyFeedbacktype : "null"; //strRequestID = strRequestID != "0" ? strRequestID : "null"; //strProcessType = strProcessType != "0" ? strProcessType : "null"; lstQuery.Add("INSERT INTO Report_Feedback_Details(FKEY_PROJECT,FKEY_SECTIONKEY,FKEY_LEASEKEY,FKEY_FEEDBACK_SOURCE,FKEY_FEEDBACK_TYPE,REPORTED_DATE,FEEDBACK_DESCRIPTION,FEEDBACK_RESPONSE_COMMENTS,FKEY_REQUEST_ID,FKEY_PROCESS_TYPE,Added_by,Added_On,Modified_By,Modified_On) " + "VALUES ('" + strProjectKey + "','" + strSectionkey + "','" + strLeasekey + "','" + fkeyFeedbacksource + "','" + fkeyFeedbacktype + "','" + strReporteddate + "','" + _DAL.stripQuotes(feedbackDescription) + "','" + _DAL.stripQuotes(feedbackResponsecomments) + "','" + strRequestID + "','" + strProcessType + "','" + strUserKey + "', NOW(),'" + strUserKey + "',NOW() )"); if (lstQuery.Count > 0) { dbcMaster.executeNonQuery(lstQuery.ToArray()); bool Audittrail = SaveAuditTrail(RecordfdJsonvalue); if (Audittrail) { //_classicViewController.Error_Msg_Json("errmsg_009"); Status = "\"Status\" : " + "\"Success\""; Msg = "\"Message\" :" + "\"Saved Succesfully\""; } else { Status = "\"Status\" : " + "\"Error\""; Msg = "\"Message\" : \"Error In Saving Data\""; } } } else { Status = "\"Status\" : " + "\"Error\""; Msg = "\"Message\" : \"Error In Saving Data\""; } } catch (Exception ex) { _logDetails.strprojkey = strProjectKey; Log.WriteLog(ex, _logDetails); Status = "\"Status\" : " + "\"Error\""; Msg = "\"Message\" : \"" + ex.Message.ToString() + "\""; } strResult = "{" + Status + "," + Msg + "}"; return strResult; } public string UpdateRecordFeedback(JObject RecordfdJsonvalue) { string Status = string.Empty; string Msg = string.Empty; string strResult = string.Empty; string strProjectKey = string.Empty, strPkey = string.Empty, strSectionkey = string.Empty, strLeasekey = string.Empty, fkeyFeedbacksource = string.Empty, strUserKey = string.Empty, fkeyFeedbacktype = string.Empty, feedbackDescription = string.Empty, feedbackResponsecomments = string.Empty, strReporteddate = string.Empty, strRequestID = string.Empty, strProcessType = string.Empty; List lstQuery = new List(); try { dynamic lstfld = JsonConvert.DeserializeObject(RecordfdJsonvalue.ToString()); dynamic JSONValue = lstfld.CurrentRow; //dynamic JSONValue = JArray.Parse("[" + RecordfdJsonvalue + "]"); DBConnection dbcMaster = new DBConnection(_configuration.GetSection("REmaapSettings").GetSection("MasterConnectionString").Value); if (JSONValue.Count > 0) { strPkey = Convert.ToString(JSONValue[0].strPkey); strSectionkey = Convert.ToString(JSONValue[0].strSectionkey); fkeyFeedbacksource = Convert.ToString(JSONValue[0].fkeyFeedbacksource); fkeyFeedbacktype = Convert.ToString(JSONValue[0].fkeyFeedbacktype); strReporteddate = Convert.ToString(JSONValue[0].strReporteddate); feedbackDescription = Convert.ToString(JSONValue[0].feedbackDescription); feedbackResponsecomments = Convert.ToString(JSONValue[0].feedbackResponsecomments); strUserKey = Convert.ToString(JSONValue[0].modifiedby); strRequestID = Convert.ToString(JSONValue[0].requestId); strProcessType = Convert.ToString(JSONValue[0].processType); } if (!string.IsNullOrEmpty(strPkey) && !string.IsNullOrEmpty(strSectionkey) && !string.IsNullOrEmpty(feedbackDescription) && !string.IsNullOrEmpty(strUserKey) && !string.IsNullOrEmpty(strReporteddate)) { //fkeyFeedbacktype = fkeyFeedbacktype != "0" ? fkeyFeedbacktype : "null"; //strRequestID = strRequestID != "0" ? strRequestID : "null"; //strProcessType = strProcessType != "0" ? strProcessType : "null"; lstQuery.Add("UPDATE Report_Feedback_Details SET FKEY_SECTIONKEY = '" + strSectionkey + "',FKEY_FEEDBACK_SOURCE = '" + fkeyFeedbacksource + "',FKEY_FEEDBACK_TYPE = '" + fkeyFeedbacktype + "',REPORTED_DATE = '" + strReporteddate + "',FEEDBACK_DESCRIPTION ='" + _DAL.stripQuotes(feedbackDescription) + "'," + "FEEDBACK_RESPONSE_COMMENTS = '" + _DAL.stripQuotes(feedbackResponsecomments) + "',FKEY_REQUEST_ID = '" + strRequestID + "',FKEY_PROCESS_TYPE = '" + strProcessType + "', Modified_By = '" + strUserKey + "',Modified_On = NOW() WHERE PKEY = '" + strPkey + "'"); if (lstQuery.Count > 0) { dbcMaster.executeNonQuery(lstQuery.ToArray()); bool Audittrail = SaveAuditTrail(RecordfdJsonvalue); if (Audittrail) { Status = "\"Status\" : " + "\"Success\""; Msg = "\"Message\" : \"Updated Succesfully\""; } else { Status = "\"Status\" : " + "\"Error\""; Msg = "\"Message\" : \"Error on Updating the Values\""; } } } else { Status = "\"Status\" : " + "\"Error\""; Msg = "\"Message\" : \"Error on Updating the Values\""; } } catch (Exception ex) { _logDetails.strprojkey = strProjectKey; Log.WriteLog(ex, _logDetails); Status = "\"Status\" : " + "\"Error\""; Msg = "\"Message\" : \"" + ex.Message.ToString() + "\""; } strResult = "{" + Status + "," + Msg + "}"; return strResult; }