public string getICLFileName(string iclFileName) { string[] paramDetails; paramDetails = iclFileName.Split('|'); String ret = ""; String strICLFileName = paramDetails[0].Replace("!!!", ".") .Replace(",", "','"); String strTranDate = paramDetails[1]; var fileLoc = ConfigurationManager.AppSettings["ILOLoc"]; var retSql = ""; var sqlQuery = ""; using (StreamReader file = new StreamReader(fileLoc)) { //int counter = 0; string ln; while (((ln = file.ReadLine()) != null) && !ln.Contains("--")) { retSql += ln; } file.Close(); } sqlQuery = retSql + " WHERE TOW.chi_ref in ('" + strICLFileName + "')"; if (strTranDate.Contains("to")) { String[] dateRange = strTranDate.Split(new[] { "to" }, StringSplitOptions.None); sqlQuery += " AND (transaction_date >= '" + dateRange[0].TrimEnd() + "' and transaction_date <= '" + dateRange[1].TrimStart() + "') "; //AND transaction_date = '" + strTranDate +"' } else { sqlQuery += " AND transaction_date = '" + strTranDate + "'"; } List <ILOModel> searchResult = new List <ILOModel>(); try { using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["EXPRESS_SBC_CONN"].ConnectionString)) { SqlCommand cmd = new SqlCommand(sqlQuery, connection); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { ILOModel iloResultList = new ILOModel(); iloResultList.BUSINESS_DATE = reader["BUSINESS DATE"].ToString(); iloResultList.MODULE = reader["MODULE"].ToString(); iloResultList.BATCH_ID = reader["BATCH ID NO."].ToString(); iloResultList.CHECK_NUMBER = reader["CHECK NUMBER"].ToString(); iloResultList.CHECK_STATUS = reader["CHECK STATUS"].ToString(); iloResultList.AMOUNT = reader["AMOUNT"].ToString(); iloResultList.SCAN_ACCOUNT = reader["SCAN ACCOUNT NO."].ToString(); iloResultList.SCAN_BRSTN = reader["SCAN BRSTN"].ToString(); iloResultList.SCANNED_BY = reader["SCAN BY"].ToString(); iloResultList.SCANNED_TIME = reader["SCAN TIME"].ToString(); iloResultList.AMOUNT_KEYING_USR = reader["AMOUNT KEYING/PDC PROCESSING USER"].ToString(); iloResultList.AMOUNT_KEYING_TIME = reader["AMOUNT KEYING/PDC PROCESSING TIME"].ToString(); iloResultList.ACCOUNT_NO_KEYING_TIME = reader["ACCOUNT NO. KEYING TIME"].ToString(); iloResultList.ACCOUNT_NO_KEYING_USR = reader["ACCOUNT NO. KEYING USER"].ToString(); iloResultList.PDC_VERIFICATION_USER = reader["PDC VERIFICATION USER"].ToString(); iloResultList.PDC_VERIFICATION_TIME = reader["PDC VERIFICATION TIME"].ToString(); iloResultList.PDC_ALLOW_HOLD_USER = reader["PDC ALLOW/HOLD USER"].ToString(); iloResultList.PDC_ALLOW_HOLD_TIME = reader["PDC ALLOW/HOLD TIME"].ToString(); iloResultList.BRANCH_NAME = reader["BRANCH_NAME"].ToString(); searchResult.Add(iloResultList); } ret = JsonConvert.SerializeObject(searchResult); } } catch (Exception e) { //IWOWModel iwowResultList = new IWOWModel(); //iwowResultList.ERROR_MSG = e.Message; //searchResult.Add(iwowResultList); ret = JsonConvert.SerializeObject(searchResult); return(ret); } return(ret); }
public string getBrstnDateDetails(string brstnDate) { string[] paramDetails; paramDetails = brstnDate.Split('|'); string varBrstn = paramDetails[0]; string varDate = paramDetails[1]; string varCheckNumber = paramDetails[2]; var fileLoc = ConfigurationManager.AppSettings["ILOLoc"]; var retSql = ""; var sqlQuery = ""; using (StreamReader file = new StreamReader(fileLoc)) { //int counter = 0; string ln; while (((ln = file.ReadLine()) != null) && !ln.Contains("--")) { retSql += ln; } file.Close(); } string ret = ""; if (varDate.Contains("to")) { String [] dateRange = varDate.Split(new[] { "to" }, StringSplitOptions.None); sqlQuery = retSql + " WHERE (TOW.TRANSACTION_DATE >= '" + dateRange[0].TrimEnd() + "' and TOW.TRANSACTION_DATE <= '" + dateRange[1].TrimStart() + "') AND TOW.BOFD_SORTCODE = '" + varBrstn + "'"; } else { sqlQuery = retSql + " WHERE TOW.TRANSACTION_DATE = '" + varDate + "' AND TOW.BOFD_SORTCODE = '" + varBrstn + "'"; } if (!varCheckNumber.Equals("")) { sqlQuery += " AND TOW.SCAN_INSTRUMENT_NUMBER = '" + varCheckNumber.PadLeft(10, '0') + "' "; } List <ILOModel> searchResult = new List <ILOModel>(); try { using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["EXPRESS_SBC_CONN"].ConnectionString)) { SqlCommand cmd = new SqlCommand(sqlQuery, connection); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { ILOModel iloResultList = new ILOModel(); iloResultList.BUSINESS_DATE = reader["BUSINESS DATE"].ToString(); iloResultList.MODULE = reader["MODULE"].ToString(); iloResultList.BATCH_ID = reader["BATCH ID NO."].ToString(); iloResultList.CHECK_NUMBER = reader["CHECK NUMBER"].ToString(); iloResultList.CHECK_STATUS = reader["CHECK STATUS"].ToString(); iloResultList.AMOUNT = reader["AMOUNT"].ToString(); iloResultList.SCAN_ACCOUNT = reader["SCAN ACCOUNT NO."].ToString(); iloResultList.SCAN_BRSTN = reader["SCAN BRSTN"].ToString(); iloResultList.SCANNED_BY = reader["SCAN BY"].ToString(); iloResultList.SCANNED_TIME = reader["SCAN TIME"].ToString(); iloResultList.AMOUNT_KEYING_USR = reader["AMOUNT KEYING/PDC PROCESSING USER"].ToString(); iloResultList.AMOUNT_KEYING_TIME = reader["AMOUNT KEYING/PDC PROCESSING TIME"].ToString(); iloResultList.ACCOUNT_NO_KEYING_TIME = reader["ACCOUNT NO. KEYING TIME"].ToString(); iloResultList.ACCOUNT_NO_KEYING_USR = reader["ACCOUNT NO. KEYING USER"].ToString(); iloResultList.PDC_VERIFICATION_USER = reader["PDC VERIFICATION USER"].ToString(); iloResultList.PDC_VERIFICATION_TIME = reader["PDC VERIFICATION TIME"].ToString(); iloResultList.PDC_ALLOW_HOLD_USER = reader["PDC ALLOW/HOLD USER"].ToString(); iloResultList.PDC_ALLOW_HOLD_TIME = reader["PDC ALLOW/HOLD TIME"].ToString(); iloResultList.BRANCH_NAME = reader["BRANCH_NAME"].ToString(); searchResult.Add(iloResultList); } ret = JsonConvert.SerializeObject(searchResult); } } catch (Exception e) { //IWOWModel iwowResultList = new IWOWModel(); //iwowResultList.ERROR_MSG = e.Message; //searchResult.Add(iwowResultList); ret = JsonConvert.SerializeObject(searchResult); return(ret); } return(ret); }