public IEnumerable <PIDSearch> SearchPID(PIDSearch PIDSearch)
{
string sql = "";
string sqlWhere = "";
//LIKE
//if (PIDSearch.PID_RECEIPT_NO != null && PIDSearch.PID_RECEIPT_NO != "")
//{
// sqlWhere = sqlWhere + "(UPPER(t.pid_receipt_no) LIKE '%" + PIDSearch.PID_RECEIPT_NO.ToUpper() + "%') AND";
//}
//if (PIDSearch.PID_AGT_CODE != null && PIDSearch.PID_AGT_CODE != "")
//{
// sqlWhere = sqlWhere + "(UPPER(t.pid_agt_code) LIKE '%" + PIDSearch.PID_AGT_CODE.ToUpper() + "%') AND";
//}
//if (PIDSearch.PID_PROPOSAL_NO != null && PIDSearch.PID_PROPOSAL_NO != "")
//{
// sqlWhere = sqlWhere + "(UPPER(t.pid_proposal_no) LIKE '%" + PIDSearch.PID_PROPOSAL_NO.ToUpper() + "%') AND";
//}
//if (PIDSearch.PID_POLICY_NO != null && PIDSearch.PID_POLICY_NO != "")
//{
// sqlWhere = sqlWhere + "(UPPER(t.pid_policy_no) LIKE '%" + PIDSearch.PID_POLICY_NO.ToUpper() + "%') AND";
//}
if (PIDSearch.PID_RECEIPT_NO != null && PIDSearch.PID_RECEIPT_NO != "")
{
sqlWhere = sqlWhere + "substr(t.pid_receipt_no,0,13) = '" + PIDSearch.PID_RECEIPT_NO.ToUpper() + "' AND";
}
if (PIDSearch.PID_AGT_CODE != null && PIDSearch.PID_AGT_CODE != "")
{
sqlWhere = sqlWhere + "t.pid_agt_code = '" + PIDSearch.PID_AGT_CODE.ToUpper() + "' AND";
}
if (PIDSearch.PID_PROPOSAL_NO != null && PIDSearch.PID_PROPOSAL_NO != "")
{
sqlWhere = sqlWhere + "t.pid_proposal_no = '" + PIDSearch.PID_PROPOSAL_NO.ToUpper() + "' AND";
}
if (PIDSearch.PID_POLICY_NO != null && PIDSearch.PID_POLICY_NO != "")
{
sqlWhere = sqlWhere + "t.pid_policy_no = '" + PIDSearch.PID_POLICY_NO.ToUpper() + "' AND";
}
if (sqlWhere.Length > 0)
{
sqlWhere = sqlWhere.Substring(0, sqlWhere.Length - 3);
}
//sqlWhere = sqlWhere + "and t.agt_effective_end_date is null";
List <PIDSearch> AgentList = new List <PIDSearch>();
DataTable dataTable = new DataTable();
OracleDataReader dataReader = null;
OracleConnection connection = new OracleConnection(ConnectionString);
OracleCommand command;
sql = " select t.pid_receipt_no,t.pid_receipt_date,t.pid_customer_name,t.pid_proposal_no,t.pid_policy_no, " +
" CASE WHEN t.pid_receipt_amt IS NULL THEN 0 ELSE t.pid_receipt_amt END as pid_receipt_amt ,t.pid_time_slab, CASE WHEN t.pid_confirm_amt IS NULL THEN 0 ELSE t.pid_confirm_amt END as pid_confirm_amt, t.pid_confirm_date, t.pid_rv_no, t.pid_bal_type,t.pid_agt_code, t.pid_payment_mtd " +
" from HCI_TBL_MAY_PID_ACC_01 t where(" + sqlWhere + ")";
command = new OracleCommand(sql, connection);
try
{
connection.Open();
dataReader = command.ExecuteReader();
dataTable.Load(dataReader);
dataReader.Close();
connection.Close();
AgentList = (from DataRow drow in dataTable.Rows
select new PIDSearch()
{
PID_RECEIPT_NO = drow[0].ToString(),
PID_RECEIPT_DATE = drow[1].ToString(),
PID_CUSTOMER = drow[2].ToString(),
PID_PROPOSAL_NO = drow[3].ToString(),
PID_POLICY_NO = drow[4].ToString(),
PID_RECEIPT_AMT = Convert.ToDouble(drow[5].ToString()),
PID_TIME_SLAB = drow[6].ToString(),
PID_CONFIRM_AMT = Convert.ToDouble(drow[7].ToString()),
PID_CONFIRM_DATE = drow[8].ToString(),
PID_RV_NO = drow[9].ToString(),
PID_BAL_TYPE = drow[10].ToString(),
PID_AGT_CODE = drow[11].ToString(),
PID_CHEQUE_NO = drow[12].ToString()
}).ToList();
}
catch (Exception exception)
{
if (dataReader != null)
{
dataReader.Close();
}
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(AgentList);
}
public IEnumerable <PIDSearch> SearchPIDByProposalNo(PIDSearch PIDSearch)
{
string sql = "";
string sqlWhere = "";
if (PIDSearch.PID_PROPOSAL_NO != null && PIDSearch.PID_PROPOSAL_NO != "")
{
sqlWhere = sqlWhere + "t.pid_proposal_no = '" + PIDSearch.PID_PROPOSAL_NO.ToUpper() + "' AND";
}
if (sqlWhere.Length > 0)
{
sqlWhere = sqlWhere.Substring(0, sqlWhere.Length - 3);
}
List <PIDSearch> AgentList = new List <PIDSearch>();
DataTable dataTable = new DataTable();
OracleDataReader dataReader = null;
OracleConnection connection = new OracleConnection(ConnectionString);
OracleCommand command;
sql = " SELECT PID_RECEIPT_NO, PID_RECEIPT_DATE, PID_RECEIPT_AMT , PID_PAYMENT_MTD FROM HCI_TBL_APR_PID_ACC_T4 " +
" WHERE PID_PROPOSAL_NO = '" + PIDSearch.PID_PROPOSAL_NO + "' " +
" AND PID_AVAILABLE_AMT > 0 " +
" ORDER BY PID_RECEIPT_DATE DESC, " +
" CASE WHEN LENGTH(PID_RECEIPT_NO) = 13 THEN 1 ELSE 2 END, SUBSTR(PID_RECEIPT_NO, 13, 6) DESC, " +
" CASE WHEN PID_BAL_TYPE = 'NMT' THEN 0 WHEN PID_BAL_TYPE = 'FMT' THEN 1 WHEN PID_BAL_TYPE = 'OPB' THEN 2 " +
" WHEN PID_BAL_TYPE = 'TFR' THEN 3 ELSE NULL END DESC";
command = new OracleCommand(sql, connection);
try
{
connection.Open();
dataReader = command.ExecuteReader();
dataTable.Load(dataReader);
dataReader.Close();
connection.Close();
AgentList = (from DataRow drow in dataTable.Rows
select new PIDSearch()
{
PID_RECEIPT_NO = drow[0].ToString(),
PID_RECEIPT_DATE = drow[1].ToString(),
PID_CUSTOMER = "",
PID_PROPOSAL_NO = "",
PID_POLICY_NO = "",
PID_RECEIPT_AMT = Convert.ToDouble(drow[2].ToString()),
PID_TIME_SLAB = "",
PID_CONFIRM_AMT = 0,
PID_CONFIRM_DATE = "",
PID_RV_NO = "",
PID_BAL_TYPE = "",
PID_AGT_CODE = "",
PID_CHEQUE_NO = drow[3].ToString()
}).ToList();
}
catch (Exception exception)
{
if (dataReader != null)
{
dataReader.Close();
}
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(AgentList);
}
