public string getUserIDDateDetails(string userIDDate)
{
string[] paramDetails;
paramDetails = userIDDate.Split('|');
string varUserID = paramDetails[0];
string varDate = paramDetails[1];
var fileLoc = ConfigurationManager.AppSettings["IWOWLoc"];
var retSql = "";
using (StreamReader file = new StreamReader(fileLoc))
{
//int counter = 0;
string ln;
while (((ln = file.ReadLine()) != null) && !ln.Contains("--"))
{
retSql += ln;
}
file.Close();
}
string ret = "";
var sqlQuery = retSql + " WHERE TIW.SVS_FIRST = '" + varUserID + "' AND TIW.TRANSACTION_DATE = '" + varDate + "'";
//var sqlQuery = @"SELECT TIW.TRANSACTION_DATE AS 'TRANSACTION DATE',
// TIW.INSTRUMENT_NUMBER AS 'CHECK NO.',
// TIW.CAR_AMOUNT AS 'AMOUNT',
// TIW.ZP_AMT_CREATOR AS 'AMOUNT WISE USER',
// TIW.SVSFV_TIME AS 'SVS VERIFICATION TIME',
// TIW.SVS_FIRST AS 'SVS VERIFY BY USER ID',
// TUR.FNAME AS 'SVS VERIFY BY USER NAME',
// TIW.PAYEE_BANK_CITY_CODE +
// TIW.PAYEE_BANK_CODE +
// TIW.PAYEE_BANK_BRANCH_CODE +
// TIW.PAYEE_CHECK_DIGIT AS 'CHECK BRSTN' ,
// TEN.BRANCH_NAME AS 'CHECK BRANCH NAME'
// FROM TBL_INWARD TIW INNER JOIN TBL_USER TUR
// ON TIW.SVS_FIRST = TUR.ID
// INNER JOIN TBL_ENTITY TEN ON TIW.PAYEE_BANK_CITY_CODE +
// TIW.PAYEE_BANK_CODE +
// TIW.PAYEE_BANK_BRANCH_CODE +
// TIW.PAYEE_CHECK_DIGIT = TEN.ENTITY_ID
// WHERE TIW.SVS_FIRST = '" + varUserID + "' AND TIW.TRANSACTION_DATE ='" + varDate + "'";
if (paramDetails.Length == 4)
{
string varHour = paramDetails[2];
int varMxHour = Convert.ToInt16(varHour) + 1;
string varMin = paramDetails[3];
sqlQuery = sqlQuery + " AND TIW.SVSFV_TIME >= '" + varDate + " " + varHour + ":" + varMin + ":00.000000' AND TIW.SVSFV_TIME <= '" + varDate + " " + varMxHour + ":" + varMin + ":00.000000'";
}
sqlQuery = sqlQuery + " ORDER BY TIW.SVSFV_TIME ASC";
List <IWOWModel> searchResult = new List <IWOWModel>();
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())
{
IWOWModel iwowResultList = new IWOWModel();
iwowResultList.CHECK_BRSTN = reader["CHECK BRSTN"].ToString();
iwowResultList.TRANSACTION_DATE = reader["TRANSACTION DATE"].ToString();
iwowResultList.INSTRUMENT_NUMBER = reader["CHECK NO."].ToString();
iwowResultList.ZP_AMOUNT = reader["AMOUNT"].ToString();
iwowResultList.ZP_AMT_CREATOR = reader["AMOUNT WISE USER"].ToString();
iwowResultList.SVSFV_TIME = reader["SVS VERIFICATION TIME"].ToString();
iwowResultList.SVS_FIRST = reader["SVS VERIFY BY USER ID"].ToString();
iwowResultList.FNAME = reader["SVS VERIFY BY USER NAME"].ToString();
iwowResultList.BRANCH_NAME = reader["CHECK BRANCH NAME"].ToString();
searchResult.Add(iwowResultList);
}
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 getByAcctNo(string acctNoDate)
{
string[] paramDetails;
paramDetails = acctNoDate.Split('|');
//string varCheckNo = paramDetails[0].PadLeft(10, '0');
string varAcctNo = paramDetails[0];
string varDate = paramDetails[1];
string varCheckNo = "";
var fileLoc = ConfigurationManager.AppSettings["IWOWLoc"];
var retSql = "";
using (StreamReader file = new StreamReader(fileLoc))
{
//int counter = 0;
string ln;
while (((ln = file.ReadLine()) != null) && !ln.Contains("--"))
{
retSql += ln;
}
file.Close();
}
string ret = "";
var sqlQuery = "";
if (varDate.Contains("to"))
{
String[] dateRange = varDate.Split(new[] { "to" }, StringSplitOptions.None);
sqlQuery = retSql + " WHERE (TIW.TRANSACTION_DATE >= '" + dateRange[0].TrimEnd() + "' and TIW.TRANSACTION_DATE <= '" + dateRange[1].TrimStart() + "') AND TIW.PAYEE_ACNO = '" + varAcctNo + "'";
}
else
{
sqlQuery = retSql + " WHERE TIW.TRANSACTION_DATE = '" + varDate + "' AND TIW.PAYEE_ACNO = '" + varAcctNo + "'";
}
//sqlQuery = retSql + " WHERE TIW.INSTRUMENT_NUMBER = '" + varCheckNo + "'";
if (paramDetails[2] != "")
{
varCheckNo = paramDetails[2].PadLeft(10, '0');
sqlQuery += " AND TIW.INSTRUMENT_NUMBER = '" + varCheckNo + "'";
}
sqlQuery = sqlQuery + " ORDER BY TIW.SVSFV_TIME ASC";
List <IWOWModel> searchResult = new List <IWOWModel>();
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())
{
IWOWModel iwowResultList = new IWOWModel();
iwowResultList.CHECK_BRSTN = reader["CHECK BRSTN"].ToString();
iwowResultList.TRANSACTION_DATE = reader["TRANSACTION DATE"].ToString();
iwowResultList.INSTRUMENT_NUMBER = reader["CHECK NO."].ToString();
iwowResultList.ZP_AMOUNT = reader["AMOUNT"].ToString();
iwowResultList.ZP_AMT_CREATOR = reader["AMOUNT WISE USER"].ToString();
iwowResultList.SVSFV_TIME = reader["SVS VERIFICATION TIME"].ToString();
iwowResultList.SVS_FIRST = reader["SVS VERIFY BY USER ID"].ToString();
iwowResultList.FNAME = reader["SVS VERIFY BY USER NAME"].ToString();
iwowResultList.BRANCH_NAME = reader["CHECK BRANCH NAME"].ToString();
searchResult.Add(iwowResultList);
}
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 getCheckDetails(string checkNumber)
{
string[] paramDetails;
paramDetails = checkNumber.Split('|');
string varCheckNo = paramDetails[0].PadLeft(10, '0');
string varDate;
string varUserID;
var fileLoc = ConfigurationManager.AppSettings["IWOWLoc"];
var retSql = "";
using (StreamReader file = new StreamReader(fileLoc))
{
//int counter = 0;
string ln;
while (((ln = file.ReadLine()) != null) && !ln.Contains("--"))
{
retSql += ln;
}
file.Close();
}
string ret = "";
var sqlQuery = retSql + " WHERE TIW.INSTRUMENT_NUMBER = '" + varCheckNo + "'";
//string ret = "";
//var sqlQuery = @"SELECT TIW.TRANSACTION_DATE AS 'TRANSACTION DATE',
// TIW.INSTRUMENT_NUMBER AS 'CHECK NO.',
// TIW.CAR_AMOUNT AS 'AMOUNT',
// TIW.ZP_AMT_CREATOR AS 'AMOUNT WISE USER',
// TIW.SVSFV_TIME AS 'SVS VERIFICATION TIME',
// TIW.SVS_FIRST AS 'SVS VERIFY BY USER ID',
// TUR.FNAME AS 'SVS VERIFY BY USER NAME',
// TIW.PAYEE_BANK_CITY_CODE +
// TIW.PAYEE_BANK_CODE +
// TIW.PAYEE_BANK_BRANCH_CODE +
// TIW.PAYEE_CHECK_DIGIT AS 'CHECK BRSTN' ,
// TEN.BRANCH_NAME AS 'CHECK BRANCH NAME'
// FROM TBL_INWARD TIW INNER JOIN TBL_USER TUR
// ON TIW.SVS_FIRST = TUR.ID
// INNER JOIN TBL_ENTITY TEN ON TIW.PAYEE_BANK_CITY_CODE +
// TIW.PAYEE_BANK_CODE +
// TIW.PAYEE_BANK_BRANCH_CODE +
// TIW.PAYEE_CHECK_DIGIT = TEN.ENTITY_ID
// WHERE TIW.INSTRUMENT_NUMBER = '" + varCheckNo + "'";
if (paramDetails.Length > 1)
{
if (paramDetails[1] != "" && paramDetails[2] != "")
{
varDate = paramDetails[1];
varUserID = paramDetails[2];
if (varDate.Contains("to"))
{
String[] dateRange = varDate.Split(new[] { "to" }, StringSplitOptions.None);
sqlQuery = retSql + " WHERE (TIW.TRANSACTION_DATE >= '" + dateRange[0].TrimEnd() + "' and TIW.TRANSACTION_DATE < '" + dateRange[1].TrimStart() + "') AND TIW.SVS_FIRST = '" + varUserID + "'";
}
else
{
sqlQuery = retSql + " WHERE TIW.TRANSACTION_DATE = '" + varDate + "' AND TIW.SVS_FIRST = '" + varUserID + "'";
}
//sqlQuery = sqlQuery + " AND TIW.TRANSACTION_DATE = '" + varDate + "' AND TIW.SVS_FIRST = '" + varUserID + "'";
}
else
{
if (paramDetails[1] != "")
{
varDate = paramDetails[1];
if (varDate.Contains("to"))
{
String[] dateRange = varDate.Split(new[] { "to" }, StringSplitOptions.None);
sqlQuery += " AND (TIW.TRANSACTION_DATE >= '" + dateRange[0].TrimEnd() + "' and TIW.TRANSACTION_DATE <= '" + dateRange[1].TrimStart() + "')";
}
else
{
sqlQuery += " AND TIW.TRANSACTION_DATE = '" + varDate + "'";
}
//sqlQuery = sqlQuery + " AND TIW.TRANSACTION_DATE = '" + varDate + "'";
}
else if (paramDetails[2] != "")
{
varUserID = paramDetails[2];
sqlQuery = sqlQuery + " AND TIW.SVS_FIRST = '" + varUserID + "'";
}
}
}
sqlQuery = sqlQuery + " ORDER BY TIW.SVSFV_TIME ASC";
List <IWOWModel> searchResult = new List <IWOWModel>();
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())
{
IWOWModel iwowResultList = new IWOWModel();
iwowResultList.CHECK_BRSTN = reader["CHECK BRSTN"].ToString();
iwowResultList.TRANSACTION_DATE = reader["TRANSACTION DATE"].ToString();
iwowResultList.INSTRUMENT_NUMBER = reader["CHECK NO."].ToString();
iwowResultList.ZP_AMOUNT = reader["AMOUNT"].ToString();
iwowResultList.ZP_AMT_CREATOR = reader["AMOUNT WISE USER"].ToString();
iwowResultList.SVSFV_TIME = reader["SVS VERIFICATION TIME"].ToString();
iwowResultList.SVS_FIRST = reader["SVS VERIFY BY USER ID"].ToString();
iwowResultList.FNAME = reader["SVS VERIFY BY USER NAME"].ToString();
iwowResultList.BRANCH_NAME = reader["CHECK BRANCH NAME"].ToString();
searchResult.Add(iwowResultList);
}
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);
}
