//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());
}
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);
}
//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());
}
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);
}