public DataTable GetAgents(String strWhere) { String sql = @"select Id,(Id+'--'+Name) as Name,Name as AgentsName ,[Province] ,[City] ,[Village] ,[Birthday] ,[CareerStatus] ,[JoinDate] ,[Rank] ,[RefereeId] ,[RefereeName] ,[AgencyId] ,[AgencyName] ,[AccountBank] ,[AccountBankBranch] ,[Account] ,[Address] ,[ZipCode] ,[Phone] ,[CreateTime] ,[CreatePerson] ,[UpdateTime] ,[UpdatePerson] ,[State] ,[AgentsStatus] from Agents where 1=1" + strWhere; return(DataBase.Base_dt(sql)); }
//产品销售汇总 public DataTable HuiZongOrdersDetail(int yearMonth) { String sql = @"select MAX(a.ProductName) as ProductName,MAX(a.UnitPrice) as UnitPrice,sum(a.Num) as Num,SUM(a.Price) as Price from OrdersDetail a left join Orders b on a.OrdersId = b.Id where a.State = 1 and b.State=1 and b.YearMonth = " + yearMonth + "group by a.ProductId"; return(DataBase.Base_dt(sql)); }
public DataTable getBank(String strWhere) { String sql = "select * from DictBank where 1=1 "; if (String.IsNullOrWhiteSpace(strWhere)) { sql += strWhere; } return(DataBase.Base_dt(sql)); }
public DataTable GetAllProducts(Model.Products products) { String sql = "select * from Products where 1=1 "; if (!String.IsNullOrWhiteSpace(products.ProductName)) { sql += " and ProductName='" + products.ProductName + "'"; } return(DataBase.Base_dt(sql)); }
public DataTable HuiZongOrders(int yearMonth) { String sql = @"select a.AgentId,MAX(b.Name) as AgentName,MAX(b.CareerStatus) as CareerStatus,Count(a.Id)as CountOrders, MAX(b.Rank) as Rank,MAX(b.State) as State,Max(AgentsStatus) as AgentsStatus,MAX(a.Price) as Price from [dbo].[Orders] a left join [dbo].[Agents] b on a.AgentId=b.Id where a.State=1 and a.YearMonth=" + yearMonth + "group by a.AgentId"; return(DataBase.Base_dt(sql)); }
public DataTable GetByOrdersId(Model.OrdersDetail ordersDetail) { String sql = "select * from OrdersDetail where 1=1 "; if (!String.IsNullOrWhiteSpace(ordersDetail.OrdersId)) { sql += " and OrdersId='" + ordersDetail.OrdersId + "'"; } return(DataBase.Base_dt(sql)); }
public DataTable GetListByPage(string strWhere, string orderby, int startIndex, int endIndex) { StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT * FROM ( "); strSql.Append(" SELECT ROW_NUMBER() OVER ("); if (!string.IsNullOrEmpty(orderby.Trim())) { strSql.Append("order by T." + orderby); } else { strSql.Append("order by T.CreateTime desc"); } strSql.Append(")AS Row, T.* from Orders T "); if (!string.IsNullOrEmpty(strWhere.Trim())) { strSql.Append(" WHERE " + strWhere); } strSql.Append(" ) TT"); strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex); return(DataBase.Base_dt(strSql.ToString())); }
public DataTable getOrdersByObject(String strWhere) { return(DataBase.Base_dt("select * from Orders where 1=1 " + strWhere)); }
public DataTable getRegion(String strWhere) { String sql = "select * from DictRegion where 1=1 " + strWhere; return(DataBase.Base_dt(sql)); }
public DataTable getProductsById(String id) { String sql = "select * from Products where Id='" + id + "'"; return(DataBase.Base_dt(sql)); }
public DataTable getProducts() { String sql = "select * from Products where state=1"; return(DataBase.Base_dt(sql)); }