public timesheetEntity getTimesheetDetails(string TransactionID) { logger.Debug("Service:getTimesheetDetails() called"); logger.Info(string.Format("Transaction ID received as {0} to get details ", TransactionID)); try { timesheetEntity result = new timesheetEntity(); timesheetInterface getTS_SI = new timesheetInterface(); result = getTS_SI.getTimesheetDetails_SI(TransactionID); return result; } catch (OracleException ex) { webServiceExHandling.ExceptionLog(ex); string mailBody = string.Format(timesheet_Constants.mail_BodyFormat, System.DateTime.Now.ToString("dd-MMM-yyyy", CultureInfo.CreateSpecificCulture("en-US")), TransactionID, ex.TargetSite.ToString(), ex.ToString()); webServiceExHandling.Send_Email(timesheet_Constants.Email_Dic, mailBody); timesheetEntity Error = new timesheetEntity(0); Error.TS_headerDetails.ErrorCode = ex.Code; string expCode = ExpType(ex); Error.TS_headerDetails.ErrorMessage = timesheet_Constants.cnfgErrMessages[expCode]; logger.Error(string.Format(" ErrorCode : {0}", Error.TS_headerDetails.ErrorCode.ToString())); logger.Error(string.Format(" ErrorMessage : {0}", Error.TS_headerDetails.ErrorMessage)); logger.Error(string.Format(" ErrorStack : {0}", ex.StackTrace)); logger.Error("Service:getTimesheetDetails() returning error"); return Error; } catch (Exception ex) { webServiceExHandling.ExceptionLog(ex); string mailBody = string.Format(timesheet_Constants.mail_BodyFormat, System.DateTime.Now.ToString("dd-MMM-yyyy", CultureInfo.CreateSpecificCulture("en-US")), TransactionID, ex.TargetSite.ToString(), ex.ToString()); webServiceExHandling.Send_Email(timesheet_Constants.Email_Dic, mailBody); timesheetEntity Error = new timesheetEntity(0); Error.TS_headerDetails.ErrorCode = 1; Error.TS_headerDetails.ErrorMessage = timesheet_Constants.Error; logger.Error(string.Format(" ErrorCode : {0}", Error.TS_headerDetails.ErrorCode.ToString())); logger.Error(string.Format(" ErrorMessage : {0}", Error.TS_headerDetails.ErrorMessage)); logger.Error(string.Format(" ErrorStack : {0}", ex.StackTrace)); logger.Error("Service:getTimesheetDetails() returning error"); return Error; } }
public timesheetEntity getTimesheetDetails_BAL(string TransactionID_BAL) { try { logger.Debug("timesheet_BAL:getTimesheetDetails_BAL() called"); logger.Debug("Transaction ID value : " + TransactionID_BAL); if ( string.IsNullOrEmpty(TransactionID_BAL) ) { timesheetEntity ts_value = new timesheetEntity(count); ts_value.TS_headerDetails.ErrorCode = 21; ts_value.TS_headerDetails.ErrorMessage = timesheet_Constants.TransIdNull; logger.Debug("Method getTimesheetDetails_BAL : ErrorCode = " + ts_value.TS_headerDetails.ErrorCode.ToString()); logger.Debug("Method getTimesheetDetails_BAL : ErrorMessage = " + ts_value.TS_headerDetails.ErrorMessage); logger.Error("Method : getTimesheetDetails_BAL validation failed"); return ts_value; } timesheet_DAL getTS_DAL = new timesheet_DAL(); return (getTS_DAL.getTimesheetDetails_DAL(TransactionID_BAL)); } catch (OracleException dbEx) { logger.Error("Exception At BAL - getTimesheetDetails_BAL : " + dbEx.Message.ToString()); logger.Error("timesheet_BAL:getTimesheetDetails_BAL() returning error"); throw dbEx; } catch(Exception ex) { logger.Error("Exception At BAL - getTimesheetDetails_BAL : " + ex.Message.ToString()); logger.Error("timesheet_BAL:getTimesheetDetails_BAL() returning error"); throw ex; } }
/// <summary> /// This function fetches the timesheet Details from the database based on Transaction ID /// </summary> /// <param name="TransactionID"></param> /// <returns> /// <paramref name="timesheetEntity"/> /// </returns> /// <history> /// Hari haran 02/05/2012 created /// </history> public timesheetEntity getTimesheetDetails_DAL(string TransactionID) { logger.Debug("timesheet_DAL:getTimesheetDetails_DAL() called"); logger.Debug("Transaction ID value : " + TransactionID); databaseLayer dbConStr = new databaseLayer(); string connStr = dbConStr.connectionInfo; //logger.Debug("Connection string : " + connStr); OracleConnection conn = new OracleConnection(connStr); //OracleTransaction tsTrans = null; logger.Debug("Connetion to the database established"); try { if (conn.State == System.Data.ConnectionState.Closed) { conn.Open(); logger.Debug("Connection Status opened "); } //tsTrans = conn.BeginTransaction(); /* string headerQuery = " SELECT TTM_ID,TE_T_APPROVER,TTM_EMPNO,NAME,TTM_WEEK,TTM_SUBDATE,TTM_US_REM" + " FROM T_TS_EMPLOYEE T,T_EMPLOYEE E, T_TS_MAS_SUB H" + " WHERE T.EMPNO = E.TE_EMPNO AND T.EMPNO = H.TTM_EMPNO AND H.TTM_ID = :TransID"; */ string headerQuery = "SUP_GETTIMESHEETMAS"; OracleCommand header_cmd = new OracleCommand(headerQuery, conn); header_cmd.CommandType = CommandType.StoredProcedure; header_cmd.Parameters.AddWithValue("TRANSID", TransactionID); header_cmd.Parameters.Add("RESULTSET", OracleType.Cursor).Direction = ParameterDirection.Output; //header_cmd.Transaction = tsTrans; logger.Debug("Header_cmd Query parameters initialised"); QueryLog.CmdInfo(header_cmd); OracleDataReader reader = header_cmd.ExecuteReader(); logger.Debug("Header_cmd Executed by ExecuteReader()"); //string childQuery = " SELECT TTD_PROJECT as ChargeCode,p1.pm_desc as ProjectName,b1.trad_rm_per as Billable,TTD_ACTIVITY,t2.ta_desc as ActivityName,"; //childQuery = childQuery + " MON,TUE,WED,THU,FRI,SAT,SUN,MON+TUE+WED+THU+FRI+SAT+SUN Total_hrs"; //childQuery = childQuery + " FROM ( SELECT TTD_PROJECT,TTD_ACTIVITY,"; //childQuery = childQuery + " max(decode(DAY,'Monday ',ttd_hours,0))Mon,"; //childQuery = childQuery + " max(decode(DAY,'Tuesday ',ttd_hours,0))Tue,"; //childQuery = childQuery + " max(decode(DAY,'Wednesday',ttd_hours,0))Wed,"; //childQuery = childQuery + " max(decode(DAY,'Thursday ',ttd_hours,0))Thu,"; //childQuery = childQuery + " max(decode(DAY,'Friday ',ttd_hours,0))Fri,"; //childQuery = childQuery + " max(decode(DAY,'Saturday ',ttd_hours,0))Sat,"; //childQuery = childQuery + " max(decode(DAY,'Sunday ',ttd_hours,0))Sun "; //childQuery = childQuery + " FROM ( SELECT ttd_ttmid, TO_CHAR(ttd_date, 'Day') Day ,ttd_hours ,TTD_PROJECT,TTD_ACTIVITY "; //childQuery = childQuery + " FROM T_TS_DET_SUB WHERE ttd_ttmid = :TransID)"; //childQuery = childQuery + " GROUP BY(TTD_PROJECT,TTD_ACTIVITY,ttd_ttmid)"; //childQuery = childQuery + " ) t1,project_master p1,t_activity t2,"; //childQuery = childQuery + " (SELECT prjId,trad_rm_per,rm.trad_empno"; //childQuery = childQuery + " FROM(SELECT t.ttd_ttmid as Transactn,TTM_EMPNO,min(t.ttd_date) as mindate, t.ttd_project as prjId from T_TS_DET_SUB t, T_TS_MAS_SUB u"; //childQuery = childQuery + " WHERE u.ttm_id = t.ttd_ttmid and t.ttd_ttmid = :TransID group by t.ttd_ttmid,TTM_EMPNO, t.ttd_project"; //childQuery = childQuery + " )tl,ts_rm_allocation_data rm"; //childQuery = childQuery + " WHERE tl.mindate >= rm.trad_alloc_from and tl.mindate <= rm.trad_alloc_to and TTM_EMPNO = rm.trad_empno and"; //childQuery = childQuery + " tl.prjId = rm.trad_charge_code order by 1) b1"; //childQuery = childQuery + " WHERE t1.ttd_project=p1.pm_id and t1.ttd_activity=t2.ta_id and t1.TTD_PROJECT = b1.prjId"; string childQuery = "SUP_GetTimesheetDetails"; OracleCommand child_cmd = new OracleCommand(childQuery, conn); child_cmd.CommandType = CommandType.StoredProcedure; logger.Debug("Child_cmd Query parameters initialised"); child_cmd.Parameters.AddWithValue("TTMID", TransactionID); child_cmd.Parameters.Add("RESULTSET", OracleType.Cursor).Direction = ParameterDirection.Output; QueryLog.CmdInfo(child_cmd); OracleDataAdapter da = new OracleDataAdapter(); da.SelectCommand = child_cmd; DataSet dSet = new DataSet(); da.Fill(dSet); logger.Debug("Child_cmd excuted by OracleDataAdapter()"); int count = dSet.Tables[0].Rows.Count; timesheetEntity ts_value = new timesheetEntity(count); int cntHeader=0; if (reader.HasRows) { reader.Read(); ts_value.TS_headerDetails.TransactionID = reader["TTM_ID"].ToString(); ts_value.TS_headerDetails.EmpManagerID = Convert.ToInt32(reader["TE_T_APPROVER"]); ts_value.TS_headerDetails.EmployeeNo = Convert.ToInt32(reader["TTM_EMPNO"]); ts_value.TS_headerDetails.EmployeeName = reader["NAME"].ToString(); ts_value.TS_headerDetails.TimesheetWeek = Convert.ToDateTime(reader["TTM_WEEK"]); ts_value.TS_headerDetails.SubmittedDate = Convert.ToDateTime(reader["TTM_SUBDATE"]); ts_value.TS_headerDetails.ReporteeComments = Convert.ToString(reader["TTM_US_REM"]); cntHeader = cntHeader + 1; } else { //Error handling : For Invaild Transaction ID ts_value.TS_headerDetails.ErrorCode = 31; ts_value.TS_headerDetails.ErrorMessage = timesheet_Constants.TransIdInvalid; logger.Debug("ErrorCode = " + ts_value.TS_headerDetails.ErrorCode.ToString()); logger.Debug("ErrorMessage = " + ts_value.TS_headerDetails.ErrorMessage); return ts_value; } logger.Info(string.Format(" Number of returned records for Header Details : {0}", cntHeader)); logger.Info(string.Format(" Number of returned records for Child Details : {0}", count)); int objCounter = 0; foreach (DataRow dr in dSet.Tables[0].Rows) { timesheetEntity.childDetails ts_ch = new timesheetEntity.childDetails(); ts_ch.ChargeCode = dr["ChargeCode"].ToString(); ts_ch.ProjectName = dr["ProjectName"].ToString(); ts_ch.Billable = Convert.ToInt32(dr["Billable"]); ts_ch.ActivityName = dr["ActivityName"].ToString(); ts_ch.Mon = Convert.ToDouble(dr["MON"]); ts_ch.Tue = Convert.ToDouble(dr["TUE"]); ts_ch.Wed = Convert.ToDouble(dr["WED"]); ts_ch.Thur = Convert.ToDouble(dr["THU"]); ts_ch.Fri = Convert.ToDouble(dr["FRI"]); ts_ch.Sat = Convert.ToDouble(dr["SAT"]); ts_ch.Sun = Convert.ToDouble(dr["SUN"]); ts_ch.TotalHours = Convert.ToDouble(dr["Total_hrs"]); logger.Debug("Row Count = " + objCounter.ToString()); ts_value.TS_childDetails[objCounter++] = ts_ch; } logger.Info("In Success case : ErrorCode = " + ts_value.TS_headerDetails.ErrorCode.ToString()); logger.Info("In Success case : ErrorMessage = " + ts_value.TS_headerDetails.ErrorMessage); //tsTrans.Commit(); return ts_value; } catch (OracleException dbEx) { logger.Error("Exception Occured At timesheet_DAL - getTimesheetDetails_DAL : "); logger.Error("Exception Code : " + dbEx.ErrorCode.ToString()); logger.Error("Exception Description : " + dbEx.Message.ToString()); logger.Error("timesheet_DAL:getTimesheetDetails_DAL() returning error"); //tsTrans.Rollback(); throw dbEx; } catch (Exception ex) { logger.Error("Exception Occured At timesheet_DAL - getTimesheetDetails_DAL : " + ex.Message.ToString()); logger.Error("timesheet_DAL:getTimesheetDetails_DAL() returning error"); //tsTrans.Rollback(); //throw new myCustomException(31, ex.Message); throw ex; } finally { logger.Debug("Connection Status Closed "); conn.Dispose(); } }