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); }