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