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