public HttpResponseMessage SaveTripMainl(TripMain obj)
{
OracleConnection connection = new OracleConnection(ConnectionString);
OracleCommand command;
try
{
connection.Open();
command = new OracleCommand("INSERT_WELF_TRIP_2017_MAIN");
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;
command.Parameters.Add("V_SEQ_ID", OracleType.Number).Value = obj.SeqId;
command.Parameters.Add("V_COMPANY", OracleType.VarChar).Value = obj.Company;
command.Parameters.Add("V_BRANCH_DEPT", OracleType.VarChar).Value = obj.BranchDept;
command.Parameters.Add("V_EPF_NO", OracleType.Number).Value = obj.EpfNo;
command.Parameters.Add("V_MEMBER_NAME", OracleType.VarChar).Value = obj.MemberName;
command.Parameters.Add("V_MARITIAL_STATUS", OracleType.VarChar).Value = obj.MaritialStatus;
command.Parameters.Add("V_SPOUSE_NAME", OracleType.VarChar).Value = obj.SpouseName;
command.Parameters.Add("V_IS_MEMBER_PARTICIPATE", OracleType.Number).Value = obj.IsMemberParticipate;
command.Parameters.Add("V_IS_SPOUSE_PARTICIPATE", OracleType.Number).Value = obj.IsSpouseParticipate;
command.ExecuteNonQuery();
connection.Close();
return(Request.CreateResponse(HttpStatusCode.OK));
}
catch (Exception exception)
{
connection.Close();
return(Request.CreateResponse(HttpStatusCode.ExpectationFailed));
}
}
public HttpResponseMessage UpdateTripMainl(TripMain obj)
{
OracleConnection connection = new OracleConnection(ConnectionString);
OracleCommand command;
try
{
connection.Open();
command = new OracleCommand("UPDATE_WELF_TRIP_2017_MAIN");
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;
command.Parameters.Add("V_SEQ_ID", OracleType.Number).Value = obj.SeqId;
command.Parameters.Add("V_IS_MEMBER_PARTICIPATE", OracleType.Number).Value = obj.IsMemberParticipate;
command.Parameters.Add("V_IS_SPOUSE_PARTICIPATE", OracleType.Number).Value = obj.IsSpouseParticipate;
command.Parameters.Add("V_IS_CH1_PARTICIPATE", OracleType.Number).Value = obj.IsCh1Participate;
command.Parameters.Add("V_IS_CH2_PARTICIPATE", OracleType.Number).Value = obj.IsCh2Participate;
command.Parameters.Add("V_IS_CH3_PARTICIPATE", OracleType.Number).Value = obj.IsCh3Participate;
command.Parameters.Add("V_IS_CH4_PARTICIPATE", OracleType.Number).Value = obj.IsCh4Participate;
command.Parameters.Add("V_IS_CH5_PARTICIPATE", OracleType.Number).Value = obj.IsCh5Participate;
command.Parameters.Add("V_PARTICIPATE_DATE", OracleType.VarChar).Value = obj.ParticipateDate;
command.Parameters.Add("V_MEMBER_COST", OracleType.Number).Value = obj.MemberCost;
command.Parameters.Add("V_REMARKS", OracleType.VarChar).Value = obj.Remarks;
command.ExecuteNonQuery();
connection.Close();
return(Request.CreateResponse(HttpStatusCode.OK));
}
catch (Exception exception)
{
connection.Close();
return(Request.CreateResponse(HttpStatusCode.ExpectationFailed));
}
}
public HttpResponseMessage SaveTripMainl(TripMain obj)
{
OracleConnection connection = new OracleConnection(ConnectionString);
OracleCommand command;
try
{
connection.Open();
command = new OracleCommand("INSERT_WELF_TRIP_2017_MAIN");
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;
command.Parameters.Add("V_SEQ_ID", OracleType.Number).Value = obj.SeqId;
command.Parameters.Add("V_COMPANY", OracleType.VarChar).Value = obj.Company;
command.Parameters.Add("V_BRANCH_DEPT", OracleType.VarChar).Value = obj.BranchDept;
command.Parameters.Add("V_EPF_NO", OracleType.Number).Value = obj.EpfNo;
command.Parameters.Add("V_MEMBER_NAME", OracleType.VarChar).Value = obj.MemberName;
command.Parameters.Add("V_MARITIAL_STATUS", OracleType.VarChar).Value = obj.MaritialStatus;
command.Parameters.Add("V_SPOUSE_NAME", OracleType.VarChar).Value = obj.SpouseName;
command.Parameters.Add("V_CH1_NAME", OracleType.VarChar).Value = obj.Ch1Name;
command.Parameters.Add("V_CH1_DOB", OracleType.DateTime).Value = obj.Ch1Dob;
command.Parameters.Add("V_CH2_NAME", OracleType.VarChar).Value = obj.Ch2Name;
command.Parameters.Add("V_CH2_DOB", OracleType.DateTime).Value = obj.Ch2Dob;
command.Parameters.Add("V_CH3_NAME", OracleType.VarChar).Value = obj.Ch3Name;
command.Parameters.Add("V_CH3_DOB", OracleType.DateTime).Value = obj.Ch3Dob;
command.Parameters.Add("V_CH4_NAME", OracleType.VarChar).Value = obj.Ch4Name;
command.Parameters.Add("V_CH4_DOB", OracleType.DateTime).Value = obj.Ch4Dob;
command.Parameters.Add("V_CH5_NAME", OracleType.VarChar).Value = obj.Ch5Name;
command.Parameters.Add("V_CH5_DOB", OracleType.DateTime).Value = obj.Ch5Dob;
command.Parameters.Add("V_IS_MEMBER_PARTICIPATE", OracleType.Number).Value = obj.IsMemberParticipate;
command.Parameters.Add("V_IS_SPOUSE_PARTICIPATE", OracleType.Number).Value = obj.IsSpouseParticipate;
command.Parameters.Add("V_IS_CH1_PARTICIPATE", OracleType.Number).Value = obj.IsCh1Participate;
command.Parameters.Add("V_IS_CH2_PARTICIPATE", OracleType.Number).Value = obj.IsCh2Participate;
command.Parameters.Add("V_IS_CH3_PARTICIPATE", OracleType.Number).Value = obj.IsCh3Participate;
command.Parameters.Add("V_IS_CH4_PARTICIPATE", OracleType.Number).Value = obj.IsCh4Participate;
command.Parameters.Add("V_PARTICIPATE_DATE", OracleType.VarChar).Value = obj.ParticipateDate;
command.Parameters.Add("V_IS_CH5_PARTICIPATE", OracleType.Number).Value = obj.IsCh5Participate;
command.Parameters.Add("V_PARTICIPATE_DATE", OracleType.VarChar).Value = obj.ParticipateDate;
command.Parameters.Add("V_MEMBER_COST", OracleType.Number).Value = obj.MemberCost;
command.Parameters.Add("V_ROOM_SEQ_ID", OracleType.VarChar).Value = obj.RoomSeqId;
command.Parameters.Add("V_REMARKS", OracleType.VarChar).Value = obj.Remarks;
command.ExecuteNonQuery();
connection.Close();
return(Request.CreateResponse(HttpStatusCode.OK));
}
catch (Exception exception)
{
connection.Close();
return(Request.CreateResponse(HttpStatusCode.ExpectationFailed));
}
}
public HttpResponseMessage UpdateTripMainl(TripMain obj)
{
OracleConnection connection = new OracleConnection(ConnectionString);
OracleCommand command;
try
{
connection.Open();
command = new OracleCommand("UPDATE_WELF_DD_2017");
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;
command.Parameters.Add("V_SEQ_ID", OracleType.Number).Value = obj.SeqId;
command.Parameters.Add("V_IS_MEMBER_PARTICIPATE", OracleType.Number).Value = obj.IsMemberParticipate;
command.Parameters.Add("V_IS_SPOUSE_PARTICIPATE", OracleType.Number).Value = obj.IsSpouseParticipate;
command.ExecuteNonQuery();
connection.Close();
return(Request.CreateResponse(HttpStatusCode.OK));
}
catch (Exception exception)
{
connection.Close();
return(Request.CreateResponse(HttpStatusCode.ExpectationFailed));
}
}
public TripMain Get(int id)
{
TripMain tripMain = new TripMain();
OracleDataReader dataReader = null;
OracleConnection connection = new OracleConnection(ConnectionString);
OracleCommand command;
string sql = "SELECT " +
"CASE WHEN t.SEQ_ID IS NULL THEN 0 ELSE t.SEQ_ID END, " +
"CASE WHEN t.COMPANY IS NULL THEN '' ELSE t.COMPANY END, " +
"CASE WHEN t.BRANCH_DEPT IS NULL THEN '' ELSE t.BRANCH_DEPT END, " +
"CASE WHEN t.EPF_NO IS NULL THEN 0 ELSE t.EPF_NO END, " +
"CASE WHEN t.MEMBER_NAME IS NULL THEN '' ELSE t.MEMBER_NAME END, " +
"CASE WHEN t.MARITIAL_STATUS IS NULL THEN '' ELSE t.MARITIAL_STATUS END, " +
"CASE WHEN t.SPOUSE_NAME IS NULL THEN '' ELSE t.SPOUSE_NAME END, " +
"CASE WHEN t.CH1_NAME IS NULL THEN '' ELSE t.CH1_NAME END, " +
"CASE WHEN t.CH1_DOB IS NULL THEN to_date('01/01/1900', 'DD/MM/RRRR') ELSE to_date(t.CH1_DOB, 'DD/MM/RRRR') END, " +
"CASE WHEN t.CH2_NAME IS NULL THEN '' ELSE t.CH2_NAME END, " +
"CASE WHEN t.CH2_DOB IS NULL THEN to_date('01/01/1900', 'DD/MM/RRRR') ELSE to_date(t.CH2_DOB, 'DD/MM/RRRR') END, " +
"CASE WHEN t.CH3_NAME IS NULL THEN '' ELSE t.CH3_NAME END, " +
"CASE WHEN t.CH3_DOB IS NULL THEN to_date('01/01/1900', 'DD/MM/RRRR') ELSE to_date(t.CH3_DOB, 'DD/MM/RRRR') END, " +
"CASE WHEN t.CH4_NAME IS NULL THEN '' ELSE t.CH4_NAME END, " +
"CASE WHEN t.CH4_DOB IS NULL THEN to_date('01/01/1900', 'DD/MM/RRRR') ELSE to_date(t.CH4_DOB, 'DD/MM/RRRR') END, " +
"CASE WHEN t.CH5_NAME IS NULL THEN '' ELSE t.CH5_NAME END, " +
"CASE WHEN t.CH5_DOB IS NULL THEN to_date('01/01/1900', 'DD/MM/RRRR') ELSE to_date(t.CH5_DOB, 'DD/MM/RRRR') END, " +
"CASE WHEN t.IS_MEMBER_PARTICIPATE IS NULL THEN 0 ELSE t.IS_MEMBER_PARTICIPATE END, " +
"CASE WHEN t.IS_SPOUSE_PARTICIPATE IS NULL THEN 0 ELSE t.IS_SPOUSE_PARTICIPATE END, " +
"CASE WHEN t.IS_CH1_PARTICIPATE IS NULL THEN 0 ELSE t.IS_CH1_PARTICIPATE END, " +
"CASE WHEN t.IS_CH2_PARTICIPATE IS NULL THEN 0 ELSE t.IS_CH2_PARTICIPATE END, " +
"CASE WHEN t.IS_CH3_PARTICIPATE IS NULL THEN 0 ELSE t.IS_CH3_PARTICIPATE END, " +
"CASE WHEN t.IS_CH4_PARTICIPATE IS NULL THEN 0 ELSE t.IS_CH4_PARTICIPATE END, " +
"CASE WHEN t.IS_CH5_PARTICIPATE IS NULL THEN 0 ELSE t.IS_CH5_PARTICIPATE END, " +
"CASE WHEN t.PARTICIPATE_DATE IS NULL THEN '' ELSE t.PARTICIPATE_DATE END, " +
"CASE WHEN t.MEMBER_COST IS NULL THEN 0 ELSE t.MEMBER_COST END, " +
"CASE WHEN t.ROOM_SEQ_ID IS NULL THEN '' ELSE t.ROOM_SEQ_ID END, " +
"CASE WHEN t.REMARKS IS NULL THEN '' ELSE t.REMARKS END, " +
"CASE WHEN t.SYSTEM_DATE IS NULL THEN to_date('01/01/1900', 'DD/MM/RRRR') ELSE to_date(t.SYSTEM_DATE, 'DD/MM/RRRR') END, " +
"CASE WHEN t.MEMBER_GENDER IS NULL THEN '' ELSE t.MEMBER_GENDER END " +
" FROM WELF_TRIP_2017_MAIN t WHERE t.SEQ_ID=:V_SEQ_ID";
command = new OracleCommand(sql, connection);
command.Parameters.Add(new OracleParameter("V_SEQ_ID", id));
connection.Open();
try
{
dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
dataReader.Read();
tripMain.SeqId = Convert.ToInt32(dataReader[0]);
tripMain.Company = dataReader[1].ToString();
tripMain.BranchDept = dataReader[2].ToString();
tripMain.EpfNo = Convert.ToInt32(dataReader[3]);
tripMain.MemberName = dataReader[4].ToString();
tripMain.MaritialStatus = dataReader[5].ToString();
tripMain.SpouseName = dataReader[6].ToString();
tripMain.IsMemberParticipate = Convert.ToInt32(dataReader[17]);
tripMain.IsSpouseParticipate = Convert.ToInt32(dataReader[18]);
tripMain.SystemDate = dataReader[28].ToString();
tripMain.MemberGender = dataReader[29].ToString();
dataReader.Close();
connection.Close();
}
else
{
return(null);
}
}
catch (Exception exception)
{
if (dataReader != null || connection.State == ConnectionState.Open)
{
dataReader.Close();
connection.Close();
}
}
finally
{
connection.Close();
}
return(tripMain);
}
public TripMain GetMainDetailsByEpf(string company, int epf)
{
TripMain tripMain = new TripMain();
OracleDataReader dataReader = null;
OracleConnection connection = new OracleConnection(ConnectionString);
OracleCommand command;
string sql = "SELECT " +
"CASE WHEN t.SEQ_ID IS NULL THEN 0 ELSE t.SEQ_ID END, " + //0
"CASE WHEN t.COMPANY IS NULL THEN '' ELSE t.COMPANY END, " + //1
"CASE WHEN t.BRANCH_DEPT IS NULL THEN '' ELSE t.BRANCH_DEPT END, " + //2
"CASE WHEN t.EPF_NO IS NULL THEN 0 ELSE t.EPF_NO END, " + //3
"CASE WHEN t.MEMBER_NAME IS NULL THEN '' ELSE t.MEMBER_NAME END, " + //4
"CASE WHEN t.MARITIAL_STATUS IS NULL THEN '' ELSE t.MARITIAL_STATUS END, " + //5
"CASE WHEN t.SPOUSE_NAME IS NULL THEN '' ELSE t.SPOUSE_NAME END, " + //6
"CASE WHEN t.IS_MEMBER_PARTICIPATE IS NULL THEN 0 ELSE t.IS_MEMBER_PARTICIPATE END, " + //7
"CASE WHEN t.IS_SPOUSE_PARTICIPATE IS NULL THEN 0 ELSE t.IS_SPOUSE_PARTICIPATE END, " + //8
"CASE WHEN t.SYSTEM_DATE IS NULL THEN to_date('01/01/1900', 'DD/MM/RRRR') ELSE to_date(t.SYSTEM_DATE, 'DD/MM/RRRR') END " + //9
" FROM WELF_TRIP_2017_MAIN t WHERE t.COMPANY=:V_COMPANY AND t.EPF_NO=:V_EPF_NO";
command = new OracleCommand(sql, connection);
command.Parameters.Add(new OracleParameter("V_COMPANY", company));
command.Parameters.Add(new OracleParameter("V_EPF_NO", epf));
connection.Open();
try
{
dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
dataReader.Read();
tripMain.SeqId = Convert.ToInt32(dataReader[0]);
tripMain.Company = dataReader[1].ToString();
tripMain.BranchDept = dataReader[2].ToString();
tripMain.EpfNo = Convert.ToInt32(dataReader[3]);
tripMain.MemberName = dataReader[4].ToString();
tripMain.MaritialStatus = dataReader[5].ToString();
tripMain.SpouseName = dataReader[6].ToString();
tripMain.IsMemberParticipate = Convert.ToInt32(dataReader[7]);
tripMain.IsSpouseParticipate = Convert.ToInt32(dataReader[8]);
tripMain.SystemDate = dataReader[9].ToString();
dataReader.Close();
connection.Close();
}
else
{
return(null);
}
}
catch (Exception exception)
{
if (dataReader != null || connection.State == ConnectionState.Open)
{
dataReader.Close();
connection.Close();
}
}
finally
{
connection.Close();
}
return(tripMain);
}
public TripMain GetMainDetailsByEpf(string company, int epf)
{
TripMain tripMain = new TripMain();
OracleDataReader dataReader = null;
OracleConnection connection = new OracleConnection(ConnectionString);
OracleCommand command;
string sql = "SELECT " +
"CASE WHEN t.SEQ_ID IS NULL THEN 0 ELSE t.SEQ_ID END, " +
"CASE WHEN t.COMPANY IS NULL THEN '' ELSE t.COMPANY END, " +
"CASE WHEN t.BRANCH_DEPT IS NULL THEN '' ELSE t.BRANCH_DEPT END, " +
"CASE WHEN t.EPF_NO IS NULL THEN 0 ELSE t.EPF_NO END, " +
"CASE WHEN t.MEMBER_NAME IS NULL THEN '' ELSE t.MEMBER_NAME END, " +
"CASE WHEN t.MARITIAL_STATUS IS NULL THEN '' ELSE t.MARITIAL_STATUS END, " +
"CASE WHEN t.SPOUSE_NAME IS NULL THEN '' ELSE t.SPOUSE_NAME END, " +
"CASE WHEN t.CH1_NAME IS NULL THEN '' ELSE t.CH1_NAME END, " +
"CASE WHEN t.CH1_DOB IS NULL THEN to_date('01/01/1900', 'DD/MM/RRRR') ELSE to_date(t.CH1_DOB, 'DD/MM/RRRR') END, " +
"CASE WHEN t.CH2_NAME IS NULL THEN '' ELSE t.CH2_NAME END, " +
"CASE WHEN t.CH2_DOB IS NULL THEN to_date('01/01/1900', 'DD/MM/RRRR') ELSE to_date(t.CH2_DOB, 'DD/MM/RRRR') END, " +
"CASE WHEN t.CH3_NAME IS NULL THEN '' ELSE t.CH3_NAME END, " +
"CASE WHEN t.CH3_DOB IS NULL THEN to_date('01/01/1900', 'DD/MM/RRRR') ELSE to_date(t.CH3_DOB, 'DD/MM/RRRR') END, " +
"CASE WHEN t.CH4_NAME IS NULL THEN '' ELSE t.CH4_NAME END, " +
"CASE WHEN t.CH4_DOB IS NULL THEN to_date('01/01/1900', 'DD/MM/RRRR') ELSE to_date(t.CH4_DOB, 'DD/MM/RRRR') END, " +
"CASE WHEN t.CH5_NAME IS NULL THEN '' ELSE t.CH5_NAME END, " +
"CASE WHEN t.CH5_DOB IS NULL THEN to_date('01/01/1900', 'DD/MM/RRRR') ELSE to_date(t.CH5_DOB, 'DD/MM/RRRR') END, " +
"CASE WHEN t.IS_MEMBER_PARTICIPATE IS NULL THEN 0 ELSE t.IS_MEMBER_PARTICIPATE END, " +
"CASE WHEN t.IS_SPOUSE_PARTICIPATE IS NULL THEN 0 ELSE t.IS_SPOUSE_PARTICIPATE END, " +
"CASE WHEN t.IS_CH1_PARTICIPATE IS NULL THEN 0 ELSE t.IS_CH1_PARTICIPATE END, " +
"CASE WHEN t.IS_CH2_PARTICIPATE IS NULL THEN 0 ELSE t.IS_CH2_PARTICIPATE END, " +
"CASE WHEN t.IS_CH3_PARTICIPATE IS NULL THEN 0 ELSE t.IS_CH3_PARTICIPATE END, " +
"CASE WHEN t.IS_CH4_PARTICIPATE IS NULL THEN 0 ELSE t.IS_CH4_PARTICIPATE END, " +
"CASE WHEN t.IS_CH5_PARTICIPATE IS NULL THEN 0 ELSE t.IS_CH5_PARTICIPATE END, " +
"CASE WHEN t.PARTICIPATE_DATE IS NULL THEN '' ELSE t.PARTICIPATE_DATE END, " +
"CASE WHEN t.MEMBER_COST IS NULL THEN 0 ELSE t.MEMBER_COST END, " +
"CASE WHEN t.ROOM_SEQ_ID IS NULL THEN '' ELSE t.ROOM_SEQ_ID END, " +
"CASE WHEN t.REMARKS IS NULL THEN '' ELSE t.REMARKS END, " +
"CASE WHEN t.SYSTEM_DATE IS NULL THEN to_date('01/01/1900', 'DD/MM/RRRR') ELSE to_date(t.SYSTEM_DATE, 'DD/MM/RRRR') END, " +
"CASE WHEN t.MEMBER_GENDER IS NULL THEN '' ELSE t.MEMBER_GENDER END " +
" FROM WELF_TRIP_2017_MAIN t WHERE t.COMPANY=:V_COMPANY AND t.EPF_NO=:V_EPF_NO";
command = new OracleCommand(sql, connection);
command.Parameters.Add(new OracleParameter("V_COMPANY", company));
command.Parameters.Add(new OracleParameter("V_EPF_NO", epf));
connection.Open();
try
{
dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
dataReader.Read();
tripMain.SeqId = Convert.ToInt32(dataReader[0]);
tripMain.Company = dataReader[1].ToString();
tripMain.BranchDept = dataReader[2].ToString();
tripMain.EpfNo = Convert.ToInt32(dataReader[3]);
tripMain.MemberName = dataReader[4].ToString();
tripMain.MaritialStatus = dataReader[5].ToString();
tripMain.SpouseName = dataReader[6].ToString();
tripMain.Ch1Name = dataReader[7].ToString();
tripMain.Ch1Dob = dataReader[8].ToString().Remove(10);
tripMain.Ch2Name = dataReader[9].ToString();
tripMain.Ch2Dob = dataReader[10].ToString().Remove(10);
tripMain.Ch3Name = dataReader[11].ToString();
tripMain.Ch3Dob = dataReader[12].ToString().Remove(10);
tripMain.Ch4Name = dataReader[13].ToString();
tripMain.Ch4Dob = dataReader[14].ToString().Remove(10);
tripMain.Ch5Name = dataReader[15].ToString();
tripMain.Ch5Dob = dataReader[16].ToString().Remove(10);
tripMain.IsMemberParticipate = Convert.ToInt32(dataReader[17]);
tripMain.IsSpouseParticipate = Convert.ToInt32(dataReader[18]);
tripMain.IsCh1Participate = Convert.ToInt32(dataReader[19]);
tripMain.IsCh2Participate = Convert.ToInt32(dataReader[20]);
tripMain.IsCh3Participate = Convert.ToInt32(dataReader[21]);
tripMain.IsCh4Participate = Convert.ToInt32(dataReader[22]);
tripMain.IsCh5Participate = Convert.ToInt32(dataReader[23]);
tripMain.ParticipateDate = dataReader[24].ToString();
tripMain.MemberCost = Convert.ToInt32(dataReader[25]);
tripMain.RoomSeqId = dataReader[26].ToString();
tripMain.Remarks = dataReader[27].ToString();
tripMain.SystemDate = dataReader[28].ToString();
tripMain.MemberGender = dataReader[29].ToString();
dataReader.Close();
connection.Close();
}
else
{
return(null);
}
}
catch (Exception exception)
{
if (dataReader != null || connection.State == ConnectionState.Open)
{
dataReader.Close();
connection.Close();
}
}
finally
{
connection.Close();
}
return(tripMain);
}
