Example #1
0
        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);
        }
Example #2
0
        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);
        }