public Dictionary <string, object> WareHousePage(string index, string size, string WareName)
{
string AddSQl = "";
if (WareName.Trim() != "")
{
AddSQl += " and WareName like '%" + WareName + "%'";
}
string PageSql = @"select top " + size + " Depotid,WareName,WareHousDate,WareHouseAddress,WHContacts,WHPhone from WareHouse where WDelete=0 " + AddSQl + " and (Depotid not in (select top (" + size + "*(" + index + "-1))Depotid from WareHouse order by Depotid)) order by Depotid desc";
string CountSql = @"select count(*) from WareHouse where WDelete=0 " + AddSQl + "";
string Page = Common.CommonClass.DataTableToJson(Com.Selcets(PageSql));
string Count = Common.CommonClass.DataTableToJson(Com.Selcets(CountSql));
Dictionary <string, object> dic = new Dictionary <string, object>();
dic.Add("Page", Page);
dic.Add("Count", Count);
return(dic);
}
/// <summary>
/// 用户列表
/// </summary>
/// <returns></returns>
public Dictionary <string, object> GetUserList(string index, string size, string username, string departname)
{
string AddSQL = "";
if (username.Trim() != "")
{
AddSQL = " and UserName like '%" + username + "%'";
}
if (departname.Trim() != "")
{
AddSQL = " and Department like '%" + departname + "%'";
}
string UserSql = "select * from (select u.UId,UserName,Department,LoginName,UCreateTime,UPhone,UEmail,Row_NUMBER()OVER(ORDER BY u.Uid desc)as rank from Users u inner join DepartMent d on u.UDepartId=d.DepartId Where UDelete=0 " + AddSQL + " ) as t where t.rank between (((" + index + " - 1) * " + size + ")+1) and(" + index + " * " + size + ")";
string UserCount = @"select count(*) from Users u inner join DepartMent d on u.UDepartId = d.DepartId Where UDelete = 0 " + AddSQL + "";
string resultData = Common.CommonClass.DataTableToJson(Com.Selcets(UserSql));
string resultCount = Common.CommonClass.DataTableToJson(Com.Selcets(UserCount));
Dictionary <string, object> dic = new Dictionary <string, object>();
dic.Add("resultData", resultData);
dic.Add("resultCount", resultCount);
return(dic);
}
/// <summary>
/// 分页显示客户信息
/// </summary>
/// <param name="index"></param>
/// <param name="size"></param>
/// <returns></returns>
public Dictionary <string, object> Get_AccountInfo(string name, string begin, string end, string index, string size)
{
string AddSQl = "";
if (name.Trim() != "")
{
AddSQl += " and AccountName like '%" + name + "%'";
}
if (begin.Trim() != "" && end.Trim() != "")
{
AddSQl += " and '" + begin + "'< AccountCreate and AccountCreate <'" + end + "' ";
}
string GetAccount = "select * from (select AccountId,AccountNo,AccountName,AccountMoney,AccountComTel,AccountPhone,AccountEmail,AccountCreate ,ROW_NUMBER()OVER(ORDER by AccountId desc)as rank from Account where AccountDelete=0 " + AddSQl + ") as t where t.rank between(((" + index + "-1)*" + size + ")+1 )and (" + index + "*" + size + ") ";
string GetAccountCount = "select count(*) from Account where AccountDelete=0 " + AddSQl;
string DoGetAccount = Common.CommonClass.DataTableToJson(com.Selcets(GetAccount));
string DoGetAccountCount = Common.CommonClass.DataTableToJson(com.Selcets(GetAccountCount));
Dictionary <string, object> dic = new Dictionary <string, object>();
dic.Add("Page", DoGetAccount);
dic.Add("Count", DoGetAccountCount);
return(dic);
}
/// <summary>
/// 商品信息分页展示
/// </summary>
/// <param name="PageSize">每页显示条数</param>
/// <param name="PageIndex">当前页</param>
/// <returns></returns>
public Dictionary <string, object> GetGoodsList(string goodsname, string goodsmodel, string PageSize, string PageIndex)
{
string AddSQL = "";
if (goodsname.Trim() != "")
{
AddSQL += " and Mname like '%" + goodsname + "%'";
}
//9.14
if (goodsmodel == "请选择")
{
goodsmodel = "";
}
if (goodsmodel.Trim() != "")
{
AddSQL += " and d.DictionaryID ='" + goodsmodel + "'";
}
//每页显示2条
//当前页
// string PageSql = @"SELECT TOP " + PageSize + " Mid,Mname,UnitName,Mnumber,m.Unitid,DataText,Msellingprice,Mnote,MCreateDate FROM Merchandise m inner join Unit u on m.Unitid=u.Unitid inner join Dictionary d on m.DictionaryID=d.DictionaryID WHERE MDelete=0 and(Mid NOT IN(SELECT TOP (" + PageSize + "*(" + PageIndex + "-1)) Mid FROM Merchandise ORDER BY Mid))ORDER BY Mid";
string PageSQL = @" select * from (select Mid,Mname,UnitName,Mnumber,m.Unitid,DataText,Msellingprice,Mnote,MCreateDate ,ROW_NUMBER() OVER(ORDER BY Mid desc) as rank from Merchandise m
inner join Unit u on m.Unitid=u.Unitid inner join Dictionary d on m.DictionaryID=d.DictionaryID where MDelete=0 " + AddSQL + " ) as t where t.rank between (((" + PageIndex + " - 1) * " + PageSize + ")+1) and(" + PageIndex + " * " + PageSize + ") ORDER BY Mid desc";
string PageCount = @"select count(*) from Merchandise m
inner join Unit u on m.Unitid=u.Unitid inner join Dictionary d on m.DictionaryID=d.DictionaryID where MDelete=0 " + AddSQL + "";
string ComStr = CommonClass.DataTableToJson(Com.Selcets(PageSQL));
string ComCount = CommonClass.DataTableToJson(Com.Selcets(PageCount));
Dictionary <string, object> dic = new Dictionary <string, object>();
dic.Add("ComStr", ComStr);
dic.Add("ComCount", ComCount);
return(dic);
}