public IActionResult GetObjectListWithDataForApproval(string pModuleObjMapCode, int pApprovalStatus, string pFromDate, string pToDate, string pUserCode)
{
AppObjectInfoMap objAppObjectInfoMap = new AppObjectInfoMap();
objAppObjectInfoMap = _generalWaitingForApproval.GetAppObjectInfoMapByMapCode(pModuleObjMapCode);
List <AppObjectInfoMap> vList = new List <AppObjectInfoMap>();
vList = _generalWaitingForApproval.GetObjectListWithDataForApproval(pModuleObjMapCode, objAppObjectInfoMap, pApprovalStatus, pFromDate, pToDate, pUserCode);
return(Ok(new
{
vList
}));
}
public List <AppObjectInfoMap> GetObjectListWithDataForApproval(string pModuleObjMapCode, AppObjectInfoMap objAppObjectInfoMap, int pApprovalStatus, string pFromDate, string pToDate, string pUserCode)
{
List <AppObjectInfoMap> objAppObjectInfoMapList = new List <AppObjectInfoMap>();
int pResponseStatus = 0;
if (objAppObjectInfoMap == null || objAppObjectInfoMap.AppObjInfoMap_GridList_VW == null || objAppObjectInfoMap.AppObjInfoMap_GridList_VW.Count == 0)
{
return(objAppObjectInfoMapList);
}
if (pApprovalStatus != -1)
{
pResponseStatus = 1;
}
StringBuilder vComTxt = new StringBuilder();
string vOrderByClause = "";
vComTxt.Append(@"SELECT A.ApprovalWaitingCode, A.ModuleObjMapCode, A.ObjPKValue, A.AppLevel, A.AppProcessBatch, A.AppUserCode, A.IsFinalLevel, A.ResponseStatus ,ISNULL(C.AppStatus, 0) as ApprovalStatus
, C.ResponseRemarks,C1.AppUserCode, E.Name as PreviousApprover ");
foreach (AppObjInfoMap_Grid grid in objAppObjectInfoMap.AppObjInfoMap_GridList_VW)
{
if (!string.IsNullOrEmpty(grid.DBFunctionMapCode_FK))
{
vComTxt.Append(", dbo." + grid.DBFunctionName_VW + "( B." + grid.DBFieldName + ") as '" + grid.GridColumnHeaderText + "'");
}
else
{
vComTxt.Append(", B." + grid.DBFieldName + " as '" + grid.GridColumnHeaderText + "'");
}
if (grid.IsOrderBy == 1)
{
if (string.IsNullOrEmpty(vOrderByClause))
{
vOrderByClause = " Order by " + grid.DBFieldName;
}
else
{
vOrderByClause = vOrderByClause + " ," + grid.DBFieldName;
}
}
}
if (objAppObjectInfoMap.AppObjectInfoMapping_Report_VW != null && objAppObjectInfoMap.AppObjectInfoMapping_Report_VW.AppObjInfoMap_RptParamsList_VW != null && objAppObjectInfoMap.AppObjectInfoMapping_Report_VW.AppObjInfoMap_RptParamsList_VW.Count > 0)
{
foreach (AppObjInfoMap_RptParams param in objAppObjectInfoMap.AppObjectInfoMapping_Report_VW.AppObjInfoMap_RptParamsList_VW)
{
vComTxt.Append(" , B." + param.DBFieldForParameterValue);
}
}
vComTxt.Append(@" FROM General_WaitingForApproval A ");
vComTxt.Append(@"join " + objAppObjectInfoMap.DBObjName + " B on A.ObjPKValue= B." + objAppObjectInfoMap.PKColumnName + " ");
vComTxt.Append(@"left outer join General_ApprovalInfo C on C.ApprovalWaitingCode=A.ApprovalWaitingCode
left outer join General_ApprovalInfo C1 on C1.ObjPKValue=A.ObjPKValue AND C1.AppStatus = 1 and C1.AppLevel= (A.AppLevel-1)
left outer join General_UserMapEmployee UME on UME.UserId=C1.AppUserCode
left outer join HR_Employee E on E.EmployeeCode=UME.EmployeeCode
where A.ModuleObjMapCode = '" + pModuleObjMapCode + @"'
and A.ResponseStatus = " + pResponseStatus + @"
and A.ActionType <> 'Delete'");
if (objAppObjectInfoMap.IsDynamicPage == 0)
{
vComTxt.Append(@"and B.ActionType <> 'Delete'");
}
vComTxt.Append(@"and A.AppUserCode='" + pUserCode + @"'");
if (pApprovalStatus != -1)
{
vComTxt.Append(@" AND C.AppStatus = " + pApprovalStatus);
}
if (!String.IsNullOrEmpty(pFromDate) && !String.IsNullOrEmpty(pToDate))
{
vComTxt.Append(@" AND CONVERT(DATETIME,CONVERT(VARCHAR(11),A.ActionDate),111) BETWEEN CONVERT(DATETIME,CONVERT(VARCHAR(11),'" + pFromDate + "'),111) AND CONVERT(DATETIME,CONVERT(VARCHAR(11),'" + pToDate + "'),111) ");
}
if (!string.IsNullOrEmpty(vOrderByClause))
{
vComTxt.Append(vOrderByClause);
}
SqlConnection connection = _supplierDbContext.GetConn();
connection.Open();
SqlDataReader dr;
SqlCommand objDbCommand = new SqlCommand(vComTxt.ToString(), connection);
dr = objDbCommand.ExecuteReader();
try
{
while (dr.Read())
{
AppObjectInfoMap objAppObjectInfoMapNew = (AppObjectInfoMap)objAppObjectInfoMap.DeepClone();//new AppObjectInfoMap();
objAppObjectInfoMapNew.ApprovalWaitingCode_VW = dr["ApprovalWaitingCode"].ToString();
objAppObjectInfoMapNew.ModuleObjMapCode_PK = dr["ModuleObjMapCode"].ToString();
objAppObjectInfoMapNew.PKColumnValue_VW = dr["ObjPKValue"].ToString();
objAppObjectInfoMapNew.ApprovalLevel_VW = Convert.ToInt32(dr["AppLevel"]);
objAppObjectInfoMapNew.AppProcessBatch_VW = Convert.ToInt32(dr["AppProcessBatch"]);
objAppObjectInfoMapNew.ApprovalStatus_VW = Convert.ToInt32(dr["ApprovalStatus"]);
objAppObjectInfoMapNew.IsFinalLevelApproval_VW = Convert.ToInt32(dr["IsFinalLevel"]);
objAppObjectInfoMapNew.ResponseStatus_VW = Convert.ToInt32(dr["ResponseStatus"]);
if (dr["ResponseRemarks"] != DBNull.Value)
{
objAppObjectInfoMapNew.Remarks_VW = dr["ResponseRemarks"].ToString();
}
if (dr["PreviousApprover"] != DBNull.Value)
{
objAppObjectInfoMapNew.PreviousApprover_VW = dr["PreviousApprover"].ToString();
}
foreach (AppObjInfoMap_Grid grid in objAppObjectInfoMapNew.AppObjInfoMap_GridList_VW)
{
if (dr[grid.GridColumnHeaderText] != DBNull.Value)
{
try
{
grid.DBFieldValue_VW = dr.GetDateTime(dr.GetOrdinal(grid.GridColumnHeaderText)).ToString("dd-MM-yyyy");
}
catch
{
grid.DBFieldValue_VW = dr[grid.GridColumnHeaderText].ToString();
}
}
}
if (objAppObjectInfoMapNew.AppObjectInfoMapping_Report_VW != null && objAppObjectInfoMapNew.AppObjectInfoMapping_Report_VW.AppObjInfoMap_RptParamsList_VW != null && objAppObjectInfoMapNew.AppObjectInfoMapping_Report_VW.AppObjInfoMap_RptParamsList_VW.Count > 0)
{
foreach (AppObjInfoMap_RptParams param in objAppObjectInfoMapNew.AppObjectInfoMapping_Report_VW.AppObjInfoMap_RptParamsList_VW)
{
if (dr[param.DBFieldForParameterValue] != DBNull.Value)
{
param.DBFieldValue_VW = dr[param.DBFieldForParameterValue].ToString();
}
}
}
objAppObjectInfoMapList.Add(objAppObjectInfoMapNew);
}
}
catch (DbException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(objAppObjectInfoMapList);
}
public List <AppObjInfoMap_Grid> GetAppObjInfoMapGridListByMapCode(string pModuleObjMapCode, AppObjectInfoMap objAppObjectInfoMap)
{
List <AppObjInfoMap_Grid> objAppObjInfoMap_GridList = new List <AppObjInfoMap_Grid>();
string vComTxt = @"SELECT A.ObjAppInfoMapGridCode
,A.ModuleObjMapCode
,A.DBFieldName
,A.DBFunctionMapCode
,B.DBFunctionName
,A.GridColumnHeaderText
,A.GridColumnLength
,A.IsOrderBy
,A.ColumnSortOrder
,A.IsViewInMessage
FROM LS_AppObjInfoMap_Grid A
left outer join dbo.LS_AppDBFunctionMapping B
on A.DBFunctionMapCode = B.DBFunctionMapCode
where A.ModuleObjMapCode='" + pModuleObjMapCode + @"'
and A.ActionType <> 'Delete'
order by A.ColumnSortOrder ";
SqlConnection connection = _supplierDbContext.GetConn();
connection.Open();
SqlDataReader dr;
SqlCommand objDbCommand = new SqlCommand(vComTxt, connection);
dr = objDbCommand.ExecuteReader();
try
{
while (dr.Read())
{
AppObjInfoMap_Grid objIAppObjInfoMap_Grid = new AppObjInfoMap_Grid();
objIAppObjInfoMap_Grid.ObjAppInfoMapGridCode_PK = dr["ObjAppInfoMapGridCode"].ToString();
objIAppObjInfoMap_Grid.ModuleObjMapCode_FK = dr["ModuleObjMapCode"].ToString();
objIAppObjInfoMap_Grid.DBFieldName = dr["DBFieldName"].ToString();
if (!string.IsNullOrEmpty(dr["DBFunctionMapCode"].ToString()))
{
objIAppObjInfoMap_Grid.DBFunctionMapCode_FK = dr["DBFunctionMapCode"].ToString();
}
if (!string.IsNullOrEmpty(dr["DBFunctionName"].ToString()))
{
objIAppObjInfoMap_Grid.DBFunctionName_VW = dr["DBFunctionName"].ToString();
}
objIAppObjInfoMap_Grid.GridColumnHeaderText = dr["GridColumnHeaderText"].ToString();
objIAppObjInfoMap_Grid.GridColumnLength = Convert.ToInt32(dr["GridColumnLength"]);
objIAppObjInfoMap_Grid.IsOrderBy = Convert.ToInt32(dr["IsOrderBy"]);
objIAppObjInfoMap_Grid.ColumnSortOrder = Convert.ToInt32(dr["ColumnSortOrder"]);
if (!string.IsNullOrEmpty(dr["IsViewInMessage"].ToString()))
{
objIAppObjInfoMap_Grid.IsViewInMessage = Convert.ToInt32(dr["IsViewInMessage"]);
}
objAppObjInfoMap_GridList.Add(objIAppObjInfoMap_Grid);
}
}
catch (DbException ex)
{
throw ex;
}
finally
{
}
return(objAppObjInfoMap_GridList);
}
public AppObjectInfoMap GetAppObjectInfoMapByMapCode(string pModuleObjMapCode)
{
AppObjectInfoMap objAppObjectInfoMap = new AppObjectInfoMap();
string vComTxt = @"SELECT A.ModuleObjMapCode
,A.ModuleCode
,C.ModuleName
,A.ModuleObjName
,A.ModuleObjId
,B.DBObjName
,B.PKColumnName
,B.DashBoardCaption
,B.PageCode
,B.IsDynamicPage
FROM LS_AppModuleObjectMapping A
join LS_AppObjectInfoMap B on A.ModuleObjMapCode=B.ModuleObjMapCode AND B.Active=1 AND A.CompanyCode = B.CompanyCode
join LS_Module C on C.ModuleCode=A.ModuleCode
and A.ModuleObjMapCode='" + pModuleObjMapCode + @"'
and A.ActionType <> 'Delete' ";
SqlConnection connection = _supplierDbContext.GetConn();
connection.Open();
SqlDataReader dr;
SqlCommand objDbCommand = new SqlCommand(vComTxt, connection);
dr = objDbCommand.ExecuteReader();
try
{
while (dr.Read())
{
objAppObjectInfoMap.ModuleObjMapCode_PK = dr["ModuleObjMapCode"].ToString();
objAppObjectInfoMap.ModuleObjName_VW = dr["ModuleObjName"].ToString();
objAppObjectInfoMap.ModuleObjId = Convert.ToInt32(dr["ModuleObjId"]);
objAppObjectInfoMap.DBObjName = dr["DBObjName"].ToString();
objAppObjectInfoMap.PKColumnName = dr["PKColumnName"].ToString();
objAppObjectInfoMap.DashBoardCaption = dr["DashBoardCaption"].ToString();
if (!string.IsNullOrEmpty(dr["PageCode"].ToString()))
{
objAppObjectInfoMap.PageCode = Convert.ToInt32(dr["PageCode"].ToString());
}
if (!string.IsNullOrEmpty(dr["IsDynamicPage"].ToString()))
{
objAppObjectInfoMap.IsDynamicPage = Convert.ToInt32(dr["IsDynamicPage"]); //changed by tinne
}
if (objAppObjectInfoMap != null)
{
objAppObjectInfoMap.AppObjInfoMap_GridList_VW = GetAppObjInfoMapGridListByMapCode(objAppObjectInfoMap.ModuleObjMapCode_PK, objAppObjectInfoMap);
objAppObjectInfoMap.AppObjectInfoMapping_Report_VW = GetAppObjectInfoMappingReportByMapCode(objAppObjectInfoMap.ModuleObjMapCode_PK);
}
}
}
catch (DbException ex)
{
throw ex;
}
finally
{
}
return(objAppObjectInfoMap);
}
