public List <ENT.Routing> GetList(string search)
{
try
{
parFields.Clear();
QueryDisctionery.SelectPart = "select r.routeid,r.routetitle,o.operatorname,r.amountmethod,r.amountSpecific,r.amountrangefrom,r.amountrangeto,r.rechargemethod,r.SystemDateTime,isnull(a.apititle,'N/A') as apititle,isnull(s.smartmobile,'N/A') as smartmobile ,isnull(m.name,'N/A') as machinename,r.status";
QueryDisctionery.TablePart = @"from Routing as r left join Operators as o on(r.operatorid=o.operatorid) left join Apis as a on(r.apiid=a.apiid) left join SmartMobile as s on(r.smartid=s.mobileid) left join Machine as m on(r.machineid=m.machineid)";
if (!string.IsNullOrWhiteSpace(search))
{
QueryDisctionery.ParameterPart = "Where r.routetitle like '%" + search + "%'";
}
QueryDisctionery.OrderPart = " Order By r.routeid desc ";
using (SqlDataReader dr = objDBHelper.ExecuteReaderQuery(QueryDisctionery, parFields, objEntity))
{
lstEntity = COM.DBHelper.CopyDataReaderToEntity <ENT.Routing>(dr);
objDBHelper.Disposed();
}
}
catch (Exception)
{
throw;
}
finally
{
parFields.Clear();
}
return(lstEntity);
}
public List <ENT.OperatorSetup> GetList(string search)
{
try
{
parFields.Clear();
//Add Condition Parameters to dictionery
//parFields.Add(COM.HelperMethod.PropertyName<ENT.UserProfile>(x => x.usr_id), userid, COM.Enumration.Operators.WHERE);
//Add Query in to string builder object
QueryDisctionery.SelectPart = "select o.operatorid,o.Billerlogo,o.Onlinevalidation,o.reqtype_normal,reqtype_special,o.Paymentvalidation,o.operatorname,o.operatorcode,o.Status,o.CreatedDateTime,s.servicename,c.nicename,o.auth_name,o.auth_msg,o.auth_regex,o.auth_datatype,o.auth_name2,o.auth_msg2,o.auth_regex2,o.auth_datatype2";
QueryDisctionery.TablePart = @"from Operators as o inner join ServiceMaster as s on(o.serviceid=s.serviceid) inner join CountryMaster as c on(o.countryid=c.countryid)";
if (!string.IsNullOrWhiteSpace(search))
{
QueryDisctionery.ParameterPart = "where o.operatorname like '%" + search + "%'";
}
QueryDisctionery.OrderPart = "order by o.CreatedDateTime desc";
//Execute Query and get SQLDataReader
using (SqlDataReader dr = objDBHelper.ExecuteReaderQuery(QueryDisctionery, parFields, objEntity))
{
lstEntity = COM.DBHelper.CopyDataReaderToEntity <ENT.OperatorSetup>(dr);
objDBHelper.Disposed();
}
}
catch (Exception)
{
throw;
}
finally
{
parFields.Clear();
}
return(lstEntity);
}
public List <ENT.RofferDetailReport> GetList(string search)
{
try
{
parFields.Clear();
QueryDisctionery.SelectPart = "select u.up_username,u.up_userid";
QueryDisctionery.TablePart = @"from RofferMapped as r inner join UserProfile as u on(r.userid=u.up_userid)";
QueryDisctionery.GroupPart = "group by u.up_username,u.up_userid";
using (SqlDataReader dr = objDBHelper.ExecuteReaderQuery(QueryDisctionery, parFields, objEntity))
{
lstEntity = COM.DBHelper.CopyDataReaderToEntity <ENT.RofferDetailReport>(dr);
objDBHelper.Disposed();
}
}
catch (Exception)
{
throw;
}
finally
{
parFields.Clear();
}
return(lstEntity);
}
public List <ENT.CircleMaster> GetList(string search)
{
try
{
parFields.Clear();
//Add Condition Parameters to dictionery
//parFields.Add(COM.HelperMethod.PropertyName<ENT.UserProfile>(x => x.usr_id), userid, COM.Enumration.Operators.WHERE);
//Add Query in to string builder object
QueryDisctionery.SelectPart = "select c.circleid,c.countryid,c.circle_name,c.circle_code,c.CreatedDateTime,c.Status,cm.name as countryname";
QueryDisctionery.TablePart = @"from CircleMaster as c inner join CountryMaster as cm on(c.countryid=cm.countryid)";
if (!string.IsNullOrWhiteSpace(search))
{
QueryDisctionery.ParameterPart = "where c.circle_name like '%" + search + "%'";
}
QueryDisctionery.OrderPart = "order by c.CreatedDateTime desc";
//Execute Query and get SQLDataReader
using (SqlDataReader dr = objDBHelper.ExecuteReaderQuery(QueryDisctionery, parFields, objEntity))
{
lstEntity = COM.DBHelper.CopyDataReaderToEntity <ENT.CircleMaster>(dr);
objDBHelper.Disposed();
}
}
catch (Exception)
{
throw;
}
finally
{
parFields.Clear();
}
return(lstEntity);
}
public List <ENT.Recharge> GetList()
{
try
{
parFields.Clear();
//Add Condition Parameters to dictionery
//parFields.Add(COM.HelperMethod.PropertyName<ENT.UserProfile>(x => x.usr_id), userid, COM.Enumration.Operators.WHERE);
//Add Query in to string builder object
QueryDisctionery.SelectPart = "select * ";
QueryDisctionery.TablePart = @"from Recharge ";
//Execute Query and get SQLDataReader
using (SqlDataReader dr = objDBHelper.ExecuteReaderQuery(QueryDisctionery, parFields, objEntity))
{
lstEntity = COM.DBHelper.CopyDataReaderToEntity <ENT.Recharge>(dr);
objDBHelper.Disposed();
}
}
catch (Exception)
{
throw;
}
finally
{
parFields.Clear();
}
return(lstEntity);
}
public List <ENT.Apis> GetList(string search)
{
try
{
parFields.Clear();
//Add Condition Parameters to dictionery
//parFields.Add(COM.HelperMethod.PropertyName<ENT.UserProfile>(x => x.usr_id), userid, COM.Enumration.Operators.WHERE);
//Add Query in to string builder object
QueryDisctionery.SelectPart = "select apiid,apititle,api,CreatedDateTime,Status ";
QueryDisctionery.TablePart = @"from Apis ";
if (!string.IsNullOrWhiteSpace(search))
{
QueryDisctionery.ParameterPart = "where apititle like '%" + search + "%'";
}
QueryDisctionery.OrderPart = "order by CreatedDateTime desc";
//Execute Query and get SQLDataReader
using (SqlDataReader dr = objDBHelper.ExecuteReaderQuery(QueryDisctionery, parFields, objEntity))
{
lstEntity = COM.DBHelper.CopyDataReaderToEntity <ENT.Apis>(dr);
objDBHelper.Disposed();
}
}
catch (Exception)
{
throw;
}
finally
{
parFields.Clear();
}
return(lstEntity);
}
public List <ENT.Complain> GetList()
{
try
{
parFields.Clear();
//Add Query in to string builder object
QueryDisctionery.SelectPart = "select * ";
QueryDisctionery.TablePart = @"from Complain ";
//Execute Query and get SQLDataReader
using (SqlDataReader dr = objDBHelper.ExecuteReaderQuery(QueryDisctionery, parFields, objEntity))
{
lstEntity = COM.DBHelper.CopyDataReaderToEntity <ENT.Complain>(dr);
objDBHelper.Disposed();
}
}
catch (Exception)
{
throw;
}
finally
{
parFields.Clear();
}
return(lstEntity);
}
public List <ENT.ActivityLog> GetList(Guid userid)
{
try
{
parFields.Clear();
//Add Condition Parameters to dictionery
//parFields.Add(COM.HelperMethod.PropertyName<ENT.UserProfile>(x => x.usr_id), userid, COM.Enumration.Operators.WHERE);
//Add Query in to string builder object
QueryDisctionery.SelectPart = "select activityid,browser,ipaddress,macaddress,CreatedDatetime ";
QueryDisctionery.TablePart = @" from ActivityLog ";
QueryDisctionery.ParameterPart = "where userid='" + userid + "' and SystemDateTime >= dateadd(d,datediff(d, 0, getdate()), 0) and SystemDateTime<dateadd(d, datediff(d, 0, getdate())+1, 0)";
QueryDisctionery.OrderPart = "Order by SystemDateTime desc";
//Execute Query and get SQLDataReader
using (SqlDataReader dr = objDBHelper.ExecuteReaderQuery(QueryDisctionery, parFields, objEntity))
{
lstEntity = COM.DBHelper.CopyDataReaderToEntity <ENT.ActivityLog>(dr);
objDBHelper.Disposed();
}
}
catch (Exception)
{
throw;
}
finally
{
parFields.Clear();
}
return(lstEntity);
}
public List <ENT.OperatorMapping> GetList(string search)
{
try
{
parFields.Clear();
//Add Condition Parameters to dictionery
//parFields.Add(COM.HelperMethod.PropertyName<ENT.UserProfile>(x => x.usr_id), userid, COM.Enumration.Operators.WHERE);
//Add Query in to string builder object
QueryDisctionery.SelectPart = "SELECT om.operatormapid,a.apititle,o.operatorname,om.operatornomal,om.operatorspecial,om.SystemDateTime";
QueryDisctionery.TablePart = @"from OperatorMapping as om inner join Apis as a on(om.apiid=a.apiid) inner join operators as o on(om.operatorid=o.operatorid) ";
if (!string.IsNullOrWhiteSpace(search))
{
QueryDisctionery.ParameterPart = "where a.apititle like '%" + search + "%'";
}
QueryDisctionery.OrderPart = "order by om.SystemDateTime desc";
//Execute Query and get SQLDataReader
using (SqlDataReader dr = objDBHelper.ExecuteReaderQuery(QueryDisctionery, parFields, objEntity))
{
lstEntity = COM.DBHelper.CopyDataReaderToEntity <ENT.OperatorMapping>(dr);
objDBHelper.Disposed();
}
}
catch (Exception)
{
throw;
}
finally
{
parFields.Clear();
}
return(lstEntity);
}
public List <ENT.SlabCommission> GetList(Guid slabid)
{
try
{
parFields.Clear();
//Add Condition Parameters to dictionery
//parFields.Add(COM.HelperMethod.PropertyName<ENT.UserProfile>(x => x.usr_id), userid, COM.Enumration.Operators.WHERE);
//Add Query in to string builder object
QueryDisctionery.SelectPart = "select null as rechargeslabid,o.operatorname,o.operatorcode,0 as md,0 as sd,0 as r,0 as charge from Operators as o where o.operatorcode not in (select s.operatorcode from SlabCommision as s where s.slabid = '" + slabid + "')";
QueryDisctionery.SelectPart += " UNION";
QueryDisctionery.SelectPart += " select s.rechargeslabid,o.operatorname,o.operatorcode,isnull(s.md,0) as md,isnull(s.sd,0) as sd,isnull(s.r,0) as r,isnull(s.charge,0) as charge from SlabCommision as s inner join Operators as o on o.operatorcode = s.operatorcode where s.slabid = '" + slabid + "'";
QueryDisctionery.OrderPart = "order by o.operatorcode desc";
//Execute Query and get SQLDataReader
using (SqlDataReader dr = objDBHelper.ExecuteReaderQuery(QueryDisctionery, parFields, objEntity))
{
lstEntity = COM.DBHelper.CopyDataReaderToEntity <ENT.SlabCommission>(dr);
objDBHelper.Disposed();
}
}
catch (Exception)
{
throw;
}
finally
{
parFields.Clear();
}
return(lstEntity);
}
public List <ENT.DMT_SlabCommission> GetAllBYID(Guid id)
{
try
{
parFields.Clear();
QueryDisctionery.SelectPart = "select * ";
QueryDisctionery.TablePart = @"FROM DMT_SlabCommision";
QueryDisctionery.ParameterPart = "where dmtslabid='" + id + "'";
//Execute Query and get SQLDataReader
using (SqlDataReader dr = objDBHelper.ExecuteReaderQuery(QueryDisctionery, parFields, objEntity))
{
lstEntity = COM.DBHelper.CopyDataReaderToEntity <ENT.DMT_SlabCommission>(dr);
objDBHelper.Disposed();
}
}
catch (Exception)
{
throw;
}
finally
{
parFields.Clear();
}
return(lstEntity);
}
public List <ENT.RofferMapped> GetList(string search)
{
try
{
parFields.Clear();
//Add Condition Parameters to dictionery
//parFields.Add(COM.HelperMethod.PropertyName<ENT.UserProfile>(x => x.usr_id), userid, COM.Enumration.Operators.WHERE);
//Add Query in to string builder object
QueryDisctionery.SelectPart = "select m.roffermapid,m.userid,m.rofferid,r.title,u.up_username,m.creditlimit,m.expirydate,m.Status,m.rstatus,m.SystemDateTime,m.CreatedDateTime";
QueryDisctionery.TablePart = @"from RofferMapped as m inner join RofferAPI as r on(r.rofferid=m.rofferid) inner join UserProfile as u on(m.userid=u.up_userid)";
if (!string.IsNullOrWhiteSpace(search))
{
QueryDisctionery.ParameterPart = "where u.up_username like '%" + search + "%'";
}
QueryDisctionery.OrderPart = "order by r.SystemDateTime desc";
//Execute Query and get SQLDataReader
using (SqlDataReader dr = objDBHelper.ExecuteReaderQuery(QueryDisctionery, parFields, objEntity))
{
lstEntity = COM.DBHelper.CopyDataReaderToEntity <ENT.RofferMapped>(dr);
objDBHelper.Disposed();
}
}
catch (Exception)
{
throw;
}
finally
{
parFields.Clear();
}
return(lstEntity);
}
public List <ENT.WalletRequest> GetApprovedList(int ddrange, DateTime fromdate, DateTime todate, int ddData, COM.MyEnumration.Userlevel userlevel, Guid userid)
{
try
{
parFields.Clear();
QueryDisctionery.SelectPart += "SELECT w.wr_id,u.up_username as UserName,W.wr_status,(case W.wr_status when 0 then 'Pending' else 'Approved' end) as WalletStatus,w.wr_bankname,w.wr_accountno,w.wr_amount,(case w.wr_mode when 1 then 'NEFT' when 2 then 'IMPS' when 3 then 'CASH' else 'NONE' end) as Mode,w.wr_refrenceid,w.wr_remakrs,(CASE w.wr_userlevel WHEN 2 THEN 'SD' WHEN 1 THEN 'MD' WHEN 3 THEN 'R' WHEN 0 THEN 'ADMIN' WHEN 4 THEN 'APIUSER' END) AS UserLevel,w.SystemDateTime";
QueryDisctionery.TablePart += " FROM WalletRequest as w inner join UserProfile as u on(w.wr_userid=u.up_userid)";
QueryDisctionery.ParameterPart += " where w.wr_upperid='" + userid + "'";
if (ddrange == 0)
{
QueryDisctionery.ParameterPart += " AND w.SystemDateTime >= dateadd(d,datediff(d, 0, getdate()), 0) and w.SystemDateTime<dateadd(d, datediff(d, 0, getdate())+1, 0)";
}
else
{
if (!(String.IsNullOrEmpty(fromdate.ToString()) && String.IsNullOrEmpty(todate.ToString())))
{
QueryDisctionery.ParameterPart += " AND (w.SystemDateTime BETWEEN '" + Convert.ToDateTime(fromdate).ToString("yyyy-MM-dd") + " 00:00:01" + "' AND '" + Convert.ToDateTime(todate).ToString("yyyy-MM-dd") + " 23:59:59" + "')";
}
}
QueryDisctionery.OrderPart += " order by w.SystemDateTime desc";
using (SqlDataReader dr = objDBHelper.ExecuteReaderQuery(QueryDisctionery, parFields, objEntity))
{
lstEntity = COM.DBHelper.CopyDataReaderToEntity <ENT.WalletRequest>(dr);
objDBHelper.Disposed();
}
}
catch (Exception)
{
throw;
}
finally
{
parFields.Clear();
}
return(lstEntity);
}
/// <summary>
/// this method is used to get all MD and API users
/// </summary>
/// <returns></returns>
public List <ENT.UserProfile> GetApiUsers()
{
try
{
parFields.Clear();
//Add Query in to string builder object
QueryDisctionery.SelectPart = "select up_userid,up_username,up_mobile";
QueryDisctionery.TablePart = @"from UserProfile";
QueryDisctionery.ParameterPart = "where up_userlevel=4 and status=1";
QueryDisctionery.OrderPart = "order by up_username desc";
//Execute Query and get SQLDataReader
using (SqlDataReader dr = objDBHelper.ExecuteReaderQuery(QueryDisctionery, parFields, objEntity))
{
lstEntity = COM.DBHelper.CopyDataReaderToEntity <ENT.UserProfile>(dr);
objDBHelper.Disposed();
}
}
catch (Exception)
{
throw;
}
finally
{
parFields.Clear();
}
return(lstEntity);
}
public List <ENT.FundReport> GetListSearchList(int ddrange, DateTime fromdate, DateTime todate, int ddData, int ddUserType, string search, COM.MyEnumration.Userlevel userlevel, Guid userid)
{
try
{
parFields.Clear();
if (userlevel == 0)
{
QueryDisctionery.SelectPart += "SELECT F.fundid,(CASE z.up_userlevel WHEN 2 THEN 'SD' WHEN 1 THEN 'MD' WHEN 3 THEN 'R' WHEN 0 THEN 'ADMIN' WHEN 4 THEN 'APIUSER' END) AS UserLevel,z.up_username+'['+z.up_mobile+']' as 'UserName',U.up_username as 'Transby',F.credit,F.debit,F.tooldbal,F.toclosebal,F.fromoldbal,F.FromCLOSEBAL,F.SystemDateTime,F.reqvia,F.remarks";
QueryDisctionery.TablePart += " FROM UserProfile U,fundTransfer F ,UserProfile Z";
QueryDisctionery.ParameterPart += " WHERE F.FundFrom=U.up_userid and z.up_userid=F.FundTo AND U.up_userid IN(select URID from GETDOWNLINE('" + userid + "',1))";
QueryDisctionery.ParameterPart += " AND (Z.up_username LIKE (CASE WHEN '" + search + "' <> '' THEN '%'+ '" + search + "' + '%' ELSE Z.up_username END) OR Z.up_mobile LIKE (CASE WHEN '" + search + "' <> '' THEN '%'+ '" + search + "' + '%' ELSE Z.up_mobile END))";
QueryDisctionery.ParameterPart += " AND Z.up_userlevel = (CASE WHEN " + ddUserType + " <> -1 THEN " + ddUserType + " ELSE Z.up_userlevel END)";
if (ddrange == 0)
{
QueryDisctionery.ParameterPart += " AND F.SystemDateTime >= dateadd(d,datediff(d, 0, getdate()), 0) and F.SystemDateTime<dateadd(d, datediff(d, 0, getdate())+1, 0)";
}
else
{
if (!(String.IsNullOrEmpty(fromdate.ToString()) && String.IsNullOrEmpty(todate.ToString())))
{
QueryDisctionery.ParameterPart += " AND (F.SystemDateTime BETWEEN '" + Convert.ToDateTime(fromdate).ToString("yyyy-MM-dd") + " 00:00:01" + "' AND '" + Convert.ToDateTime(todate).ToString("yyyy-MM-dd") + " 23:59:59" + "')";
}
}
QueryDisctionery.OrderPart += " ORDER BY F.SystemDateTime DESC";
}
else if (userlevel > 0)
{
QueryDisctionery.SelectPart += "SELECT F.fundid,(SELECT TOP 1 Z.up_username FROM UserProfile z WHERE z.up_userid=F.FundFrom)AS 'Transby',(CASE U.up_userlevel WHEN 2 THEN 'SD' WHEN 1 THEN 'MD' WHEN 3 THEN 'R' WHEN 0 THEN 'ADMIN' WHEN 4 THEN 'APIUSER' END) AS UserLevel,U.up_username as UserName,F.credit,F.debit,F.tooldbal,F.toclosebal,0 as fromoldbal,0 as FromCLOSEBAL,F.SystemDateTime,F.reqvia,F.remarks";
QueryDisctionery.TablePart += " FROM UserProfile U,fundTransfer F";
QueryDisctionery.ParameterPart += " WHERE F.FundTo=U.up_userid ";
QueryDisctionery.ParameterPart += " AND U.up_userid IN(select URID from GETDOWNLINE('" + userid + "',1))";
QueryDisctionery.ParameterPart += " AND (U.up_username LIKE (CASE WHEN '" + search + "' <> '' THEN '%'+ '" + search + "' + '%' ELSE U.up_username END) OR U.up_mobile LIKE (CASE WHEN '" + search + "' <> '' THEN '%'+ '" + search + "' + '%' ELSE U.up_mobile END))";
QueryDisctionery.ParameterPart += " AND U.up_userlevel = (CASE WHEN " + ddUserType + " <> -1 THEN " + ddUserType + " ELSE U.up_userlevel END)";
if (ddrange == 0)
{
QueryDisctionery.ParameterPart += " AND F.SystemDateTime >= dateadd(d,datediff(d, 0, getdate()), 0) and F.SystemDateTime<dateadd(d, datediff(d, 0, getdate())+1, 0)";
}
else
{
if (!(String.IsNullOrEmpty(fromdate.ToString()) && String.IsNullOrEmpty(todate.ToString())))
{
QueryDisctionery.ParameterPart += " AND (F.SystemDateTime BETWEEN '" + Convert.ToDateTime(fromdate).ToString("yyyy-MM-dd") + " 00:00:01" + "' AND '" + Convert.ToDateTime(todate).ToString("yyyy-MM-dd") + " 23:59:59" + "')";
}
}
QueryDisctionery.OrderPart += " ORDER BY F.SystemDateTime DESC";
}
using (SqlDataReader dr = objDBHelper.ExecuteReaderQuery(QueryDisctionery, parFields, objEntity))
{
lstEntity = COM.DBHelper.CopyDataReaderToEntity <ENT.FundReport>(dr);
objDBHelper.Disposed();
}
}
catch (Exception)
{
throw;
}
finally
{
parFields.Clear();
}
return(lstEntity);
}
//public List<ENT.LedgerReport> GetListSearchList(int ddrange, DateTime fromdate, DateTime todate, int ddData, string search, COM.MyEnumration.Userlevel userlevel, Guid userid)
//{
// try
// {
// parFields.Clear();
// if (userlevel == 0)
// {
// QueryDisctionery.SelectPart += "SELECT * FROM (SELECT LD.ledgerid,LD.SystemDateTime,LD.BeforeBal,LD.Amount AS 'CREDIT',0 AS 'DEBIT',LD.AfterBal ,LD.remakrs,(U.up_username+'('+(CASE U.up_userlevel WHEN 2 THEN 'SD' WHEN 1 THEN 'MD' WHEN 3 THEN 'R' WHEN 0 THEN 'ADMIN' WHEN 4 THEN 'APIUSER' END)+')') AS UserName ";
// QueryDisctionery.SelectPart += "FROM LedgerDetail AS LD INNER JOIN UserProfile AS U ON(LD.userid=U.up_userid)";
// QueryDisctionery.SelectPart += "WHERE ";
// QueryDisctionery.SelectPart += "(U.up_mobile LIKE(CASE WHEN '" + search + "' <> '' THEN '%' + '" + search + "' + '%' ELSE U.up_mobile END)) ";
// if (ddrange == 0)
// {
// QueryDisctionery.SelectPart += "AND (LD.SystemDateTime >= dateadd(d,datediff(d, 0, getdate()), 0) and LD.SystemDateTime < dateadd(d, datediff(d, 0, getdate())+1, 0)) ";
// }
// else
// {
// if (!(String.IsNullOrEmpty(fromdate.ToString()) && String.IsNullOrEmpty(todate.ToString())))
// {
// QueryDisctionery.SelectPart += "AND (LD.SystemDateTime BETWEEN '" + Convert.ToDateTime(fromdate).ToString("yyyy-MM-dd") + " 00:00:01" + "' AND '" + Convert.ToDateTime(todate).ToString("yyyy-MM-dd") + " 23:59:59" + "')";
// }
// }
// QueryDisctionery.SelectPart += "AND (LD.LedgerType ='RC' OR LD.LedgerType='FTC' OR LD.LedgerType = 'BTC')";
// QueryDisctionery.SelectPart += " UNION ";
// QueryDisctionery.SelectPart += "SELECT LD.ledgerid,LD.SystemDateTime,LD.BeforeBal,0 AS 'CREDIT',LD.Amount AS 'DEBIT',LD.AfterBal ,LD.remakrs,(U.up_username+'('+(CASE U.up_userlevel WHEN 2 THEN 'SD' WHEN 1 THEN 'MD' WHEN 3 THEN 'R' WHEN 0 THEN 'ADMIN' WHEN 4 THEN 'APIUSER' END)+')') AS UserName ";
// QueryDisctionery.SelectPart += " FROM LedgerDetail AS LD INNER JOIN UserProfile AS U ON(LD.userid=U.up_userid)";
// QueryDisctionery.SelectPart += " WHERE ";
// QueryDisctionery.SelectPart += "(U.up_mobile LIKE(CASE WHEN '" + search + "' <> '' THEN '%' + '" + search + "' + '%' ELSE U.up_mobile END)) ";
// if (ddrange == 0)
// {
// QueryDisctionery.SelectPart += "AND (LD.SystemDateTime >= dateadd(d,datediff(d, 0, getdate()), 0) and LD.SystemDateTime < dateadd(d, datediff(d, 0, getdate())+1, 0)) ";
// }
// else
// {
// if (!(String.IsNullOrEmpty(fromdate.ToString()) && String.IsNullOrEmpty(todate.ToString())))
// {
// QueryDisctionery.SelectPart += "AND (LD.SystemDateTime BETWEEN '" + Convert.ToDateTime(fromdate).ToString("yyyy-MM-dd") + " 00:00:01" + "' AND '" + Convert.ToDateTime(todate).ToString("yyyy-MM-dd") + " 23:59:59" + "')";
// }
// }
// QueryDisctionery.SelectPart += "AND (LD.LedgerType ='RD' OR LD.LedgerType='FTD' OR LD.LedgerType = 'BTD'))";
// QueryDisctionery.OrderPart += "AS TEMPLEDGER ORDER BY SystemDateTime DESC";
// }
// else if (userlevel > 0)
// {
// QueryDisctionery.SelectPart += "SELECT * FROM (SELECT LD.ledgerid,LD.SystemDateTime,LD.BeforeBal,LD.Amount AS 'CREDIT',0 AS 'DEBIT',LD.AfterBal ,LD.remakrs,(U.up_username+'('+(CASE U.up_userlevel WHEN 2 THEN 'SD' WHEN 1 THEN 'MD' WHEN 3 THEN 'R' WHEN 0 THEN 'ADMIN' WHEN 4 THEN 'APIUSER' END)+')') AS UserName ";
// QueryDisctionery.SelectPart += "FROM LedgerDetail AS LD INNER JOIN UserProfile AS U ON(LD.userid=U.up_userid)";
// QueryDisctionery.SelectPart += "WHERE ";
// QueryDisctionery.SelectPart += "LD.userid='" + userid + "' AND U.up_userlevel=" + (int)userlevel + " ";
// if (ddrange == 0)
// {
// QueryDisctionery.SelectPart += "AND (LD.SystemDateTime >= dateadd(d,datediff(d, 0, getdate()), 0) and LD.SystemDateTime < dateadd(d, datediff(d, 0, getdate())+1, 0)) ";
// }
// else
// {
// if (!(String.IsNullOrEmpty(fromdate.ToString()) && String.IsNullOrEmpty(todate.ToString())))
// {
// QueryDisctionery.SelectPart += "AND (LD.SystemDateTime BETWEEN '" + Convert.ToDateTime(fromdate).ToString("yyyy-MM-dd") + " 00:00:01" + "' AND '" + Convert.ToDateTime(todate).ToString("yyyy-MM-dd") + " 23:59:59" + "')";
// }
// }
// QueryDisctionery.SelectPart += "AND (LD.LedgerType ='RC' OR LD.LedgerType='FTC' OR LD.LedgerType = 'BTC')";
// QueryDisctionery.SelectPart += " UNION ";
// QueryDisctionery.SelectPart += "SELECT LD.ledgerid,LD.SystemDateTime,LD.BeforeBal,0 AS 'CREDIT',LD.Amount AS 'DEBIT',LD.AfterBal ,LD.remakrs,(U.up_username+'('+(CASE U.up_userlevel WHEN 2 THEN 'SD' WHEN 1 THEN 'MD' WHEN 3 THEN 'R' WHEN 0 THEN 'ADMIN' WHEN 4 THEN 'APIUSER' END)+')') AS UserName ";
// QueryDisctionery.SelectPart += " FROM LedgerDetail AS LD INNER JOIN UserProfile AS U ON(LD.userid=U.up_userid)";
// QueryDisctionery.SelectPart += " WHERE ";
// QueryDisctionery.SelectPart += "LD.userid='" + userid + "' AND U.up_userlevel=" + (int)userlevel + " ";
// if (ddrange == 0)
// {
// QueryDisctionery.SelectPart += "AND (LD.SystemDateTime >= dateadd(d,datediff(d, 0, getdate()), 0) and LD.SystemDateTime < dateadd(d, datediff(d, 0, getdate())+1, 0)) ";
// }
// else
// {
// if (!(String.IsNullOrEmpty(fromdate.ToString()) && String.IsNullOrEmpty(todate.ToString())))
// {
// QueryDisctionery.SelectPart += "AND (LD.SystemDateTime BETWEEN '" + Convert.ToDateTime(fromdate).ToString("yyyy-MM-dd") + " 00:00:01" + "' AND '" + Convert.ToDateTime(todate).ToString("yyyy-MM-dd") + " 23:59:59" + "')";
// }
// }
// QueryDisctionery.SelectPart += "AND (LD.LedgerType ='RD' OR LD.LedgerType='FTD' OR LD.LedgerType = 'BTD'))";
// QueryDisctionery.OrderPart += "AS TEMPLEDGER ORDER BY SystemDateTime DESC";
// }
// using (SqlDataReader dr = objDBHelper.ExecuteReaderQuery(QueryDisctionery, parFields, objEntity))
// {
// lstEntity = COM.DBHelper.CopyDataReaderToEntity<ENT.LedgerReport>(dr);
// objDBHelper.Disposed();
// }
// }
// catch (Exception)
// {
// throw;
// }
// finally
// {
// parFields.Clear();
// }
// return lstEntity;
//}
public List <ENT.LedgerReport> GetListSearchList(int ddrange, DateTime fromdate, DateTime todate, int ddData, string search, COM.MyEnumration.Userlevel userlevel, Guid userid)
{
try
{
parFields.Clear();
if (userlevel == 0)
{
QueryDisctionery.SelectPart = @"SELECT *,(U.up_username+'('+(CASE U.up_userlevel WHEN 2 THEN 'SD' WHEN 1 THEN 'MD' WHEN 3 THEN 'R' WHEN 0 THEN 'ADMIN' WHEN 4 THEN 'APIUSER' END)+')') AS UserName ";
QueryDisctionery.TablePart = @"FROM VW_LEDGERREPORT AS LD INNER JOIN UserProfile AS U ON (LD.userid=U.up_userid)";
QueryDisctionery.ParameterPart = "WHERE 1=1";
if (ddrange == 0)
{
QueryDisctionery.ParameterPart += "AND (LD.SystemDateTime >= dateadd(d,datediff(d, 0, getdate()), 0) and LD.SystemDateTime < dateadd(d, datediff(d, 0, getdate())+1, 0)) ";
}
else
{
if (!(String.IsNullOrEmpty(fromdate.ToString()) && String.IsNullOrEmpty(todate.ToString())))
{
QueryDisctionery.ParameterPart += "AND (LD.SystemDateTime BETWEEN '" + Convert.ToDateTime(fromdate).ToString("yyyy-MM-dd") + " 00:00:01" + "' AND '" + Convert.ToDateTime(todate).ToString("yyyy-MM-dd") + " 23:59:59" + "')";
}
}
if (!String.IsNullOrEmpty(search))
{
QueryDisctionery.ParameterPart += "AND LD.remakrs like '%" + search + "%'";
}
QueryDisctionery.OrderPart += "ORDER BY LD.SystemDateTime DESC";
}
else if (userlevel > 0)
{
QueryDisctionery.SelectPart = @"SELECT *,(U.up_username+'('+(CASE U.up_userlevel WHEN 2 THEN 'SD' WHEN 1 THEN 'MD' WHEN 3 THEN 'R' WHEN 0 THEN 'ADMIN' WHEN 4 THEN 'APIUSER' END)+')') AS UserName ";
QueryDisctionery.TablePart = @"FROM VW_LEDGERREPORT AS LD INNER JOIN UserProfile AS U ON (LD.userid=U.up_userid)";
QueryDisctionery.ParameterPart = "WHERE LD.userid='" + userid + "' AND U.up_userlevel=" + (int)userlevel;
if (ddrange == 0)
{
QueryDisctionery.ParameterPart += "AND (LD.SystemDateTime >= dateadd(d,datediff(d, 0, getdate()), 0) and LD.SystemDateTime < dateadd(d, datediff(d, 0, getdate())+1, 0)) ";
}
else
{
if (!(String.IsNullOrEmpty(fromdate.ToString()) && String.IsNullOrEmpty(todate.ToString())))
{
QueryDisctionery.ParameterPart += "AND (LD.SystemDateTime BETWEEN '" + Convert.ToDateTime(fromdate).ToString("yyyy-MM-dd") + " 00:00:01" + "' AND '" + Convert.ToDateTime(todate).ToString("yyyy-MM-dd") + " 23:59:59" + "')";
}
}
if (!String.IsNullOrEmpty(search))
{
QueryDisctionery.ParameterPart += " AND LD.remakrs like '%" + search + "%'";
}
QueryDisctionery.OrderPart += " ORDER BY LD.SystemDateTime DESC";
}
using (SqlDataReader dr = objDBHelper.ExecuteReaderQuery(QueryDisctionery, parFields, objEntity))
{
lstEntity = COM.DBHelper.CopyDataReaderToEntity <ENT.LedgerReport>(dr);
objDBHelper.Disposed();
}
}
catch (Exception)
{
throw;
}
finally
{
parFields.Clear();
}
return(lstEntity);
}
public List <ENT.RechargeReport> GetListSearchList(int ddrange, DateTime fromdate, DateTime todate, int ddData, string search, COM.MyEnumration.Userlevel userlevel, Guid userid, int status, int operatorcode)
{
try
{
parFields.Clear();
if (userlevel == 0)
{
QueryDisctionery.SelectPart += "SELECT r.rechargeid,r.userid,o.operatorname,r.SystemDateTime,r.operatorcode,r.numbertorecharge,r.amount,r.txid,r.status,r.countrycode,r.beforebalance,r.balance,r.reqvia,r.proccessdate,r.webusername,r.accountref,r.requestid,r.ipaddress,u.up_mobile,u.up_userlevel,u.up_username,r.commimd,r.commisd,r.commir,r.charge,(case when r.rechargemethod=0 then (select top 1 apititle from Apis where apiid=r.routeid) when r.rechargemethod=3 then (select top 1 smartmobile from SmartMobile where mobileid=r.routeid) else (select top 1 name from machine where machineid=r.routeid) end) as ApiTitle,(CASE WHEN R.reqtype = 0 THEN 'NORMAL' ELSE 'SPECIAL'END) AS reqtype";
QueryDisctionery.TablePart += " FROM Recharge as r inner join Operators as o on(r.operatorcode = o.operatorcode) inner join UserProfile as u on(r.userid = u.up_userid)";
QueryDisctionery.ParameterPart += " WHERE ";
QueryDisctionery.ParameterPart += " (r.numbertorecharge LIKE(CASE WHEN '" + search + "' <> '' THEN '%' + '" + search + "' + '%' ELSE r.numbertorecharge END) OR U.up_username LIKE (CASE WHEN '" + search + "' <> '' THEN '%'+ '" + search + "' + '%' ELSE U.up_username END))";
QueryDisctionery.ParameterPart += " AND (R.STATUS = (CASE WHEN " + status + " <> '-1' THEN " + status + " ELSE R.STATUS END)) ";
QueryDisctionery.ParameterPart += " AND (R.operatorcode = (CASE WHEN " + operatorcode + " <> '-1' THEN " + operatorcode + " ELSE R.operatorcode END)) ";
if (ddrange == 0)
{
QueryDisctionery.ParameterPart += " AND (r.SystemDateTime >= dateadd(d,datediff(d, 0, getdate()), 0) and r.SystemDateTime < dateadd(d, datediff(d, 0, getdate())+1, 0)) ";
}
else
{
if (!(String.IsNullOrEmpty(fromdate.ToString()) && String.IsNullOrEmpty(todate.ToString())))
{
QueryDisctionery.ParameterPart += " AND (r.SystemDateTime BETWEEN '" + Convert.ToDateTime(fromdate).ToString("yyyy-MM-dd") + " 00:00:01" + "' AND '" + Convert.ToDateTime(todate).ToString("yyyy-MM-dd") + " 23:59:59" + "')";
}
}
QueryDisctionery.OrderPart += "order by r.SystemDateTime desc";
}
else if (userlevel > 0)
{
QueryDisctionery.SelectPart += "SELECT r.rechargeid,r.userid,o.operatorname,r.SystemDateTime,r.operatorcode,r.numbertorecharge,r.amount,r.txid,r.countrycode,r.status,r.beforebalance,r.balance,r.reqvia,r.proccessdate,r.webusername,r.accountref,r.requestid,r.ipaddress,u.up_mobile,u.up_userlevel,u.up_username,r.commimd,r.commisd,r.commir,r.charge,(case when r.rechargemethod=0 then (select top 1 apititle from Apis where apiid=r.routeid) when r.rechargemethod=3 then (select top 1 smartmobile from SmartMobile where mobileid=r.routeid) else (select top 1 name from machine where machineid=r.routeid) end) as ApiTitle,(CASE WHEN R.reqtype = 0 THEN 'NORMAL' ELSE 'SPECIAL'END) AS reqtype";
QueryDisctionery.TablePart += " FROM Recharge as r inner join Operators as o on(r.operatorcode = o.operatorcode) inner join UserProfile as u on(r.userid = u.up_userid)";
QueryDisctionery.ParameterPart += " WHERE ";
QueryDisctionery.ParameterPart += " r.userid='" + userid + "' AND u.up_userlevel=" + (int)userlevel + " ";
QueryDisctionery.ParameterPart += " AND (r.numbertorecharge LIKE(CASE WHEN '" + search + "' <> '' THEN '%' + '" + search + "' + '%' ELSE r.numbertorecharge END)) ";
QueryDisctionery.ParameterPart += " AND (R.STATUS = (CASE WHEN " + status + " <> '-1' THEN " + status + " ELSE R.STATUS END)) ";
QueryDisctionery.ParameterPart += " AND (R.operatorcode = (CASE WHEN " + operatorcode + " <> '-1' THEN " + operatorcode + " ELSE R.operatorcode END)) ";
if (ddrange == 0)
{
QueryDisctionery.ParameterPart += " AND (r.SystemDateTime >= dateadd(d,datediff(d, 0, getdate()), 0) and r.SystemDateTime < dateadd(d, datediff(d, 0, getdate())+1, 0)) ";
}
else
{
if (!(String.IsNullOrEmpty(fromdate.ToString()) && String.IsNullOrEmpty(todate.ToString())))
{
QueryDisctionery.ParameterPart += " AND (r.SystemDateTime BETWEEN '" + Convert.ToDateTime(fromdate).ToString("yyyy-MM-dd") + " 00:00:01" + "' AND '" + Convert.ToDateTime(todate).ToString("yyyy-MM-dd") + " 23:59:59" + "')";
}
}
QueryDisctionery.OrderPart += "order by r.SystemDateTime desc";
}
using (SqlDataReader dr = objDBHelper.ExecuteReaderQuery(QueryDisctionery, parFields, objEntity))
{
lstEntity = COM.DBHelper.CopyDataReaderToEntity <ENT.RechargeReport>(dr);
objDBHelper.Disposed();
}
}
catch (Exception)
{
throw;
}
finally
{
parFields.Clear();
}
return(lstEntity);
}
