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