public string summaryBatchIdAndCnts(List <LoanBatchIdModel> loanBatchIds, String neededDates2)
{
List <LoanBatchIdModel> loanBatchIdCountDataResult = new List <LoanBatchIdModel>();
string[] arrDates;
arrDates = neededDates2.Split('|');
string txnDate = arrDates[0];
string createTime = arrDates[1];
string ret = "";
try
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["EXPRESS_SBC_CONN"].ConnectionString))
{
for (var i = 0; i < loanBatchIds.Count; i++)
{
string sqslGetBatchCount = @"SELECT batch_id,
total_instrument
FROM tbl_batch
WHERE batch_id IN (SELECT batch_id AS BATCH_ID FROM TBL_BATCH WHERE BATCH_ID IN(SELECT BATCH_ID AS BATCH_ID " +
"FROM TBL_OUTWARD " +
"WHERE TRANSACTION_DATE = '" + txnDate + "' AND CREATE_TIME > '" + createTime + " " + createTimeSeconds3 +
"' AND CREATE_TIME < '" + createTime + " " + createTimeSeconds4 + "' AND BOFD_SORTCODE = '" + BOFD_SORTCODE_2 + "' AND batch_id = '{batch_id}'))";
sqslGetBatchCount = sqslGetBatchCount.Replace("{batch_id}", loanBatchIds[i].BATCH_ID);
SqlCommand cmd = new SqlCommand(sqslGetBatchCount, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
loanBatchIds[i].batch_cnt = reader["total_instrument"].ToString();
//loanBatchIdDataResultList.fldNotEqual = reader["fldNotEqual"].ToString();
}
connection.Close();
}
}
ret = JsonConvert.SerializeObject(loanBatchIds);
}
catch (Exception e)
{
LoanBatchIdModel lexceptionLoanBatch = new LoanBatchIdModel();
lexceptionLoanBatch.ERROR_MSG = "Exception Occured. please try again." + e.ToString();
ret = JsonConvert.SerializeObject(lexceptionLoanBatch);
}
return(ret);
}
public string getBatchIds(string neededDates2)
{
string[] arrDates;
arrDates = neededDates2.Split('|');
string txnDate = arrDates[0];
string createTime = arrDates[1];
List <LoanBatchIdModel> loanBatchIdDataResult = new List <LoanBatchIdModel>();
List <LoanBatchIdModel> loanBatchIdCountDataResult = new List <LoanBatchIdModel>();
string ret = "";
string sqlString = @"SELECT BATCH_ID AS BATCH_ID,
COUNT(*) AS TOTAL_INSTRUMENT, '0' as fldNotEqual
FROM TBL_OUTWARD
WHERE TRANSACTION_DATE = '" + txnDate + "' AND CREATE_TIME > '" + createTime + " " + createTimeSeconds3 + "' " +
"AND CREATE_TIME < '" + createTime + " " + createTimeSeconds4 + "' AND BOFD_SORTCODE = '" + BOFD_SORTCODE_2 + "' " +
"GROUP BY BATCH_ID " +
"INTERSECT " +
"SELECT BATCH_ID, TOTAL_INSTRUMENT, '0' as fldNotEqual " +
"FROM TBL_BATCH WHERE BATCH_ID IN (SELECT BATCH_ID AS BATCH_ID " +
"FROM TBL_OUTWARD " +
"WHERE TRANSACTION_DATE = '" + txnDate + "' AND CREATE_TIME > '" + createTime + " " + createTimeSeconds3 +
"' AND CREATE_TIME < '" + createTime + " " + createTimeSeconds4 + "' AND BOFD_SORTCODE = '" + BOFD_SORTCODE_2 + "') " +
"UNION " +
"SELECT BATCH_ID AS BATCH_ID, " +
"COUNT(*) AS TOTAL_INSTRUMENT, '1' as fldNotEqual " +
"FROM TBL_OUTWARD " +
"WHERE TRANSACTION_DATE ='" + txnDate + "' AND CREATE_TIME > '" + createTime + " " + createTimeSeconds3 +
"' AND CREATE_TIME < '" + createTime + " " + createTimeSeconds4 + "' AND BOFD_SORTCODE = '" + BOFD_SORTCODE_2 + "' " +
"GROUP BY BATCH_ID " +
"EXCEPT " +
"SELECT BATCH_ID, TOTAL_INSTRUMENT, '1' as fldNotEqual " +
"FROM TBL_BATCH WHERE BATCH_ID IN (SELECT BATCH_ID AS BATCH_ID " +
"FROM TBL_OUTWARD " +
"WHERE TRANSACTION_DATE = '" + txnDate + "' AND CREATE_TIME > '" + createTime + " " + createTimeSeconds3 +
"' AND CREATE_TIME < '" + createTime + " " + createTimeSeconds4 + "' AND BOFD_SORTCODE = '" + BOFD_SORTCODE_2 + "') order by fldNotEqual desc";
try
{
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["EXPRESS_SBC_CONN"].ConnectionString))
{
SqlCommand cmd = new SqlCommand(sqlString, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
LoanBatchIdModel loanBatchIdDataResultList = new LoanBatchIdModel();
loanBatchIdDataResultList.BATCH_ID = reader["BATCH_ID"].ToString();
loanBatchIdDataResultList.TOTAL_INSTRUMENT = reader["TOTAL_INSTRUMENT"].ToString();
loanBatchIdDataResultList.fldNotEqual = reader["fldNotEqual"].ToString();
loanBatchIdDataResult.Add(loanBatchIdDataResultList);
}
ret = summaryBatchIdAndCnts(loanBatchIdDataResult, neededDates2);
}
}
catch (Exception e)
{
LoanBatchIdModel loanBatchIdDataResultList = new LoanBatchIdModel();
loanBatchIdDataResultList.ERROR_MSG = e.Message;
loanBatchIdDataResult.Add(loanBatchIdDataResultList);
ret = JsonConvert.SerializeObject(loanBatchIdDataResult);
return(ret);
}
return(ret);
}