//business
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;
List
<string> lstQuery
= new List
<string>();
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);
}
if (!string.IsNullOrEmpty(strProjectKey) && !string.IsNullOrEmpty(strLeasekey) && !string.IsNullOrEmpty(strSectionkey)
&& !string.IsNullOrEmpty(feedbackDescription) && !string.IsNullOrEmpty(strUserKey) && !string.IsNullOrEmpty(strReporteddate))
{
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,Added_by,Added_On,Modified_By,Modified_On) " +
"VALUES ('" + strProjectKey + "','" + strSectionkey + "','" + strLeasekey + "','" + fkeyFeedbacksource + "','" + fkeyFeedbacktype + "','" + strReporteddate + "','" + _DAL.stripQuotes(feedbackDescription) + "','" + _DAL.stripQuotes(feedbackResponsecomments) + "','" + strUserKey + "', NOW(),'" + strUserKey + "',NOW() )");
if (lstQuery.Count > 0)
{
dbcMaster.executeNonQuery(lstQuery.ToArray());
Status = "\"Status\" : " + "\"Success\"";
Msg = "\"Message\" : \"Saved Succesfully\"";
}
}
else
{
Status = "\"Status\" : " + "\"Error\"";
Msg = "\"Message\" : \"Insert values are empty\"";
}
bool Audittrail = SaveAuditTrail(RecordfdJsonvalue);
}
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;
List
<string> lstQuery
= new List
<string>();
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);
}
if (!string.IsNullOrEmpty(strPkey) && !string.IsNullOrEmpty(strSectionkey) && !string.IsNullOrEmpty(feedbackDescription)
&& !string.IsNullOrEmpty(strUserKey) && !string.IsNullOrEmpty(strReporteddate))
{
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) + "',Modified_By = '" + strUserKey + "',Modified_On = NOW() WHERE PKEY = '" + strPkey + "'");
if (lstQuery.Count > 0)
{
dbcMaster.executeNonQuery(lstQuery.ToArray());
Status = "\"Status\" : " + "\"Success\"";
Msg = "\"Message\" : \"Updated Succesfully\"";
}
}
else
{
Status = "\"Status\" : " + "\"Error\"";
Msg = "\"Message\" : \"Error on Updating the Values\"";
}
bool Audittrail = SaveAuditTrail(RecordfdJsonvalue);
}
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 DeleteRecordFeedback(JObject RecordfdJsonvalue)
{
string Status = string.Empty;
string Msg = string.Empty;
string strResult = string.Empty;
List
<string> lstQuery
= new List
<string>();
DBConnection dbcMaster
= new DBConnection
(_configuration
.GetSection("REmaapSettings").GetSection("MasterConnectionString").Value);
try
{
dynamic lstfld = JsonConvert.DeserializeObject(RecordfdJsonvalue.ToString());
dynamic JSONValue = lstfld.CurrentRow;
string RecordPkey = Convert.ToString(JSONValue[0].strPkey);
if (!string.IsNullOrEmpty(RecordPkey))
{
lstQuery.Add(" DELETE FROM report_feedback_details WHERE pkey ='" + RecordPkey + "' ");
if (lstQuery.Count > 0)
{
dbcMaster.executeNonQuery(lstQuery.ToArray());
Status = "\"Status\" : " + "\"Success\"";
Msg = "\"Message\" : \"Deleted Succesfully\"";
}
}
else
{
Status = "\"Status\" : " + "\"Error\"";
Msg = "\"Message\" : \"Error on Deleting the records\"";
}
bool Audittrail = SaveAuditTrail(RecordfdJsonvalue);
}
catch(Exception ex)
{
//dynamic JSONValue = JArray.Parse("[" + lstfld.CurrentRow + "]");
//dynamic JSONValue = lstfld.RecordFeedbackHeader;
//Dictionary<string, string> values = JsonConvert.DeserializeObject<Dictionary<string, string>>(jsonObj);
//var NewValues = JsonConvert.DeserializeObject<Dictionary<string, string>>(jsonObj[OldValue]);
//Dictionary<string, string> t = NewValues;
//Dictionary<string, string> k = OldValues;
Log.WriteLog(ex, _logDetails);
Status = "\"Status\" : " + "\"Error\"";
Msg = "\"Message\" : \"" + ex.Message.ToString() + "\"";
}
strResult = "{" + Status + "," + Msg + "}";
return strResult;
}
public bool SaveAuditTrail(JObject RecordfdJsonvalue)
{
bool flg = false;
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 , strRecordpkey = string.Empty,strAction = string.Empty,strModifieduser = string.Empty;
List
<string> lstQuery
= new List
<string>();
DBConnection dbcMaster
= new DBConnection
(_configuration
.GetSection("REmaapSettings").GetSection("MasterConnectionString").Value);
try
{
dynamic lstfld = JsonConvert.DeserializeObject(RecordfdJsonvalue.ToString());
//JObject jsonObj = RecordfdJsonvalue;
dynamic RecordFeedbackHeader= lstfld.RecordFeedbackHeader;
dynamic CurrentRow = lstfld.CurrentRow;
JArray oldValueArray = lstfld.OldValue;
JArray newValueArray = lstfld.NewValue;
Dictionary
<string,
string> oldValueDic
= new Dictionary
<string,
string>();
Dictionary
<string,
string> newValueDic
= new Dictionary
<string,
string>();
if (oldValueArray != null)
{
foreach (var item in oldValueArray.First())
{
var property = item as JProperty;
if (property != null)
oldValueDic.Add(property.Name, property.Value.ToString());
}
}
if (newValueArray != null)
{
foreach (var item in newValueArray.First())
{
var property = item as JProperty;
if (property != null)
newValueDic.Add(property.Name, property.Value.ToString());
}
}
List
<string> lstquery
= new List
<string>();
strProjectKey = Convert.ToString(CurrentRow[0].strProjectKey);
strSectionkey = Convert.ToString(CurrentRow[0].strSectionkey);
strLeasekey = Convert.ToString(CurrentRow[0].strLeasekey);
fkeyFeedbacksource = Convert.ToString(CurrentRow[0].fkeyFeedbacksource);
fkeyFeedbacktype = Convert.ToString(CurrentRow[0].fkeyFeedbacktype);
strReporteddate = Convert.ToString(CurrentRow[0].strReporteddate);
feedbackDescription = Convert.ToString(CurrentRow[0].feedbackDescription);
feedbackResponsecomments = Convert.ToString(CurrentRow[0].feedbackResponsecomments);
strUserKey = Convert.ToString(CurrentRow[0].addedBy);
strModifieduser = Convert.ToString(CurrentRow[0].modifiedby);
strRecordpkey = Convert.ToString(CurrentRow[0].strPkey);
strAction = Convert.ToString(CurrentRow[0].actionType);
if (strAction.Trim().ToUpper() == "A")
{
foreach (var _Field in RecordFeedbackHeader)
{
if (newValueDic.ContainsKey(Convert.ToString(_Field.columnName)))
lstquery.Add("INSERT INTO `REPORT_FEEDBACK_AUDIT_TRIAL` (`FKEY_PROJECT`,`FKEY_LEASEKEY`,`FKEY_RECORDKEY`,`FKEY_FIELDKEY`,`OLD_VALUE`,`NEW_VALUE`,`OLD_USER`,`NEW_USER`,`ACTION_TYPE`,`CHANGE_DATE`)" +
"VALUES('" + strProjectKey + "','" + strLeasekey + "','" + strRecordpkey + "','" + _Field.pkey + "',NULL,'" + stripQuotes(newValueDic[Convert.ToString(_Field.columnName)]) + "','" + strUserKey + "','" + strUserKey + "','A',now()); ");
}
flg = dbcMaster.executeNonQuery(lstquery.ToArray());
}
else if (strAction.Trim().ToUpper() == "E")
{
foreach (var _Field in RecordFeedbackHeader)
{
string aa = Convert.ToString(_Field.columnName);
if ((oldValueDic.ContainsKey(Convert.ToString(_Field.columnName)) && newValueDic.ContainsKey(Convert.ToString(_Field.columnName))) && (Convert.ToString(oldValueDic[Convert.ToString(_Field.columnName)]) != newValueDic[Convert.ToString(_Field.columnName)]))
{
lstquery.Add("INSERT INTO `REPORT_FEEDBACK_AUDIT_TRIAL` (`FKEY_PROJECT`,`FKEY_LEASEKEY`,`FKEY_RECORDKEY`,`FKEY_FIELDKEY`,`OLD_VALUE`,`NEW_VALUE`,`OLD_USER`,`NEW_USER`,`ACTION_TYPE`,`CHANGE_DATE`)" +
"VALUES('" + strProjectKey + "','" + strLeasekey + "','" + strRecordpkey + "','" + _Field.pkey + "','" + stripQuotes(oldValueDic[Convert.ToString(_Field.columnName)]) + "','" + stripQuotes(newValueDic[Convert.ToString(_Field.columnName)]) + "','" + strUserKey + "','" + strModifieduser + "','E',now()); ");
}
}
flg = dbcMaster.executeNonQuery(lstquery.ToArray());
}
else if (strAction.Trim().ToUpper() == "D")
{
foreach (var _Field in RecordFeedbackHeader)
{
if (oldValueDic.ContainsKey(Convert.ToString(_Field.columnName)))
lstquery.Add("INSERT INTO `REPORT_FEEDBACK_AUDIT_TRIAL` (`FKEY_PROJECT`,`FKEY_LEASEKEY`,`FKEY_RECORDKEY`,`FKEY_FIELDKEY`,`OLD_VALUE`,`NEW_VALUE`,`OLD_USER`,`NEW_USER`,`ACTION_TYPE`,`CHANGE_DATE`)" +
"VALUES('" + strProjectKey + "','" + strLeasekey + "','" + strRecordpkey + "','" + _Field.pkey + "','"+ stripQuotes(oldValueDic[Convert.ToString(_Field.columnName)]) + "',NULL,'" + strUserKey + "','" + strUserKey + "','D',now()); ");
}
flg = dbcMaster.executeNonQuery(lstquery.ToArray());
}
}
catch (Exception ex)
{
Log.WriteLog(ex, _logDetails);
}
return flg;
}
//controller
[Route("~/api/REmaapClassicView/DeleteRecordFeedback")]
[HttpPost]
public string DeleteRecordFeedback(JObject RecordfdJsonvalue)
{
string StrResult = string.Empty;
strIPAddressError = IsallowedIPAddress();
try
{
if (strIPAddressError.ToUpper() == "TRUE")
{
StrResult = _BLL.DeleteRecordFeedback(RecordfdJsonvalue);
}
else
{
return strIPAddressError;
}
}
catch (Exception ex)
{
Log.WriteLog(ex, _logdetails);
}
return StrResult;
}