public static bool checkAANSstatus(string nuban)
{
try
{
string sql = "select serv_type,aans_status from t24_tbl_alerts where nuban = " + nuban + " AND AANS_STATUS = '1'";
MSQconn cn = new MSQconn(connstringICAD); //(connstringAlertSystem);
cn.SetSQL(sql);
DataSet ds = cn.Select();
int count = ds.Tables[0].Rows.Count;
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
new ErrorLog(ex.ToString());
}
return(true);
}
public static DataSet GetConfigs()
{
string sql = "select Tab_text as mKey, var1 as mVal from tbl_icad_config where tab_id = 5";
MSQconn cn = new MSQconn(connstringICAD);
cn.SetSQL(sql);
return(cn.Select());
}
//handles remarks that contain "NIP/" string
public static DataSet getIBSTransactionDetailsVer1(string uniqueRef)
{
string sql = "select a.AppId as channelCode,b.ApplicationName as channelName FROM vew_ft_nip_intra_bank a inner join tbl_applicationKey b ON a.AppId=b.Appid AND ReferenceId=@transcode";
MSQconn cn = new MSQconn(connstringICAD);
cn.SetSQL(sql);
cn.AddParam("@transcode", uniqueRef);
return(cn.Select());
}
//handles remarks that contain "NIP/" string
public static DataSet getNIPTransactionDetailsVer1(string uniqueRef)
{
string sql = "select a.channelCode as channelCode,b.channelNames as channelName FROM tbl_nibssmobile a inner join tbl_channelcodes b ON a.channelCode=b.codes AND sessionid=@transcode";
MSQconn cn = new MSQconn(connstringICAD);
cn.SetSQL(sql);
cn.AddParam("@transcode", uniqueRef);
return(cn.Select());
}
//This fetches data from the SqlServer which contains all alert messages
public static DataSet GetCustomerDetailsNewAccountsFromICADDataSource(int rowstart, int rowend, string startDate, string endDate)
{
//string sql = "select * from (select refid,ACCOUNTID,COCODE,SHORTTITLE,CURRENCY,CUSTOMER ,CATEGORY ,ACCOUNTTITLE1 ,ACCOUNTTITLE2,OPENINGDATE,ALTACCTID,HVTFLAG,ACCTOFFICER,INTRODUCER,STACODE, EMAIL,CUSTOMERSTATUS ,POSTRESTRICT,BVN ,TARGET,SECTOR ,INDUSTRY,DATETIME AS MOBILENUMBER,DATETIME1 AS DATEMODIFIED,status, ROW_NUMBER() OVER (order by refid) as row from ICAD.dbo.icadmain where OPENINGDATE BETWEEN @sd AND @ed AND (Accountid is not null) AND (bvn is not null) AND ( responsecode is null OR responsecode = '') ) a WHERE row > " + rowstart + " and row <= " + rowend + " order by refid desc";
string sql = "select * from (select refid,ACCOUNTID,COCODE,SHORTTITLE,CURRENCY,CUSTOMER ,CATEGORY ,ACCOUNTTITLE1 ,ACCOUNTTITLE2,OPENINGDATE,ALTACCTID,HVTFLAG,ACCTOFFICER,INTRODUCER,STACODE, EMAIL,CUSTOMERSTATUS ,POSTRESTRICT,BVN ,TARGET,SECTOR ,INDUSTRY,DATETIME AS MOBILENUMBER,DATETIME1 AS DATEMODIFIED,status, ROW_NUMBER() OVER (order by refid) as row from ICAD.dbo.icadmain where OPENINGDATE BETWEEN @sd AND @ed AND (Accountid is not null) AND ( responsecode is null OR responsecode = '') ) a WHERE row > " + rowstart + " and row <= " + rowend + " order by refid desc";
MSQconn cn = new MSQconn(connstringICAD);
cn.SetSQL(sql);
cn.AddParam("@sd", startDate);
cn.AddParam("@ed", endDate);
return(cn.Select());
}
//this method checks to see if customer has previously being included in a sample survey
//if customer has been sampled before then it checks if the difference in time is up to 60 days
public static bool hasCustomerBeingRecentlySampled(string nuban, int daysinconsideration)
{
try
{
DateTime endDate = DateTime.Now.AddDays(1);
DateTime startDate = endDate.AddDays(-daysinconsideration); //endDate.AddDays(-60);
string sql = "select refid, created_at from ISM.dbo.tbl_survey_master where nuban =" + nuban + " AND created_at BETWEEN '" + startDate + "' AND '" + endDate + "'";
MSQconn cn = new MSQconn(connstringICAD); //(connstringAlertSystem);
cn.SetSQL(sql);
DataSet ds = cn.Select();
int count = ds.Tables[0].Rows.Count;
if (count > 0)
{
return(true);
}
else
{
//check T24_TBL_ALERT for the aans_status
//this is an additional check to ensure customer is interested in getting transaction alerts
bool aans_stat = checkAANSstatus(nuban);
if (aans_stat == true)
{
return(false);
}
else
{
return(true);
}
}
}
catch (Exception ex)
{
new ErrorLog(ex.ToString());
}
return(true);
}
public static int updateResponseFromNibbs(int refid, string responsecode, string responsetext)
{
MSQconn cn = new MSQconn(connstringICAD);
string sysdate = DateTime.Now.ToString();
string sql = "update icadmain set updated_at=@sysdate ,status='1', responsecode=@respcode, responsetext=@responsedesc where refid=@refid";
cn.SetSQL(sql);
cn.AddParam("@sysdate", sysdate);
cn.AddParam("@respcode", responsecode);
cn.AddParam("@responsedesc", responsetext);
cn.AddParam("@refid", refid);
int cnt = cn.Update();
return(cnt);
}
public static int updateBulkResponseFromNibbs(string refidlist, string responsecode, string responsetext)
{
string inquerylist = "(";
inquerylist += refidlist + "'' )";
MSQconn cn = new MSQconn(connstringICAD);
string sysdate = DateTime.Now.ToString();
string sql = "update icadmain set updated_at=@sysdate ,status='1', responsecode=@respcode, responsetext=@responsedesc where refid IN " + "(" + @refidlist + " )";
cn.SetSQL(sql);
cn.AddParam("@sysdate", sysdate);
cn.AddParam("@respcode", responsecode);
cn.AddParam("@responsedesc", responsetext);
cn.AddParam("@refidlist", refidlist);
Console.WriteLine("Response Text for " + inquerylist + " Response code:" + responsecode);
int cnt = cn.Update();
return(cnt);
}
