public Dictionary <string, object> PurchasePage(string index, string size, string name, string number)
{
var LikeSql = "";
if (number.Trim() != "")
{
LikeSql += " and PurchaseNumber like '%" + number + "%' ";
}
if (name.Trim() != "")
{
LikeSql += " and SCompanyName like '%" + name + "%' ";
}
string PageSql = @"select top " + size + " p.PurchaseName,PurchaseSum,PurchaseMoney,UnitName , PurchaseID,DepotName,n.Wid,PurchaseNumber,d.Department,PurchaseMan,SCompanyName,PurchaseType,PurchaseDate ,PurchaseNote ,CheckId from Purchase p inner join Supplier s on p.Supplierid=s.SId inner join WareHouse w on p.Depotid=w.Depotid inner join WareName n on w.wid=n.Wid inner join DepartMent d on p.DepartId=d.DepartId inner join Unit t on t.Unitid=p.Unitid where p.PDeleted=0 " + LikeSql + " and(PurchaseID not in (select top (" + size + "*(" + index + "-1))PurchaseID from Purchase order by PurchaseID)) order by PurchaseID";
string CountSql = @"select count(*) from Purchase p
inner join Supplier s on p.Supplierid=s.SId
inner join WareHouse w on p.Depotid=w.Depotid
inner join DepartMent d on p.DepartId=d.DepartId
inner join WareName n on w.wid=n.Wid
inner join Unit t on t.Unitid=p.Unitid
where p.PDeleted=0";
string DataSql = Common.CommonClass.DataTableToJson(com.Selcets(PageSql));
string DataCount = Common.CommonClass.DataTableToJson(com.Selcets(CountSql));
Dictionary <string, object> dic = new Dictionary <string, object>();
dic.Add("DataSql", DataSql);
dic.Add("DataCount", DataCount);
return(dic);
}
/// <summary>
/// 供应商资料
/// </summary>
/// <returns></returns>
public Dictionary <string, object> GetSupplierPage(string index, string size, string name, string contacts, string contactnumber, string createdate, string enddate)
{
string AddToSql = "";
if (name.Trim() != "")
{
AddToSql += " and SCompanyName like '%" + name + "%' ";
}
if (contacts.Trim() != "")
{
AddToSql += " and SContacts like '%" + contacts + "%' ";
}
if (contactnumber.Trim() != "")
{
AddToSql += " and SPhone like '%" + contactnumber + "%' ";
}
if (createdate.Trim() != "" && enddate.Trim() != "")
{
AddToSql += " and '" + createdate + "'< SCreateTime and SCreateTime <'" + enddate + "' ";
}
// string PageSQL = "select top "+size+ " Superid,SCompanyName,SContacts,SAddress,SPhone,SEmail,SCreateTime,SUpdateTime,SFax from Supplier where SDelete=0 " + AddToSql+" and (SId not in (select top (" + size+"*("+index+"-1)) SId from Supplier order by Sid))order by Sid";
string SupperPage = @"select* from (select Superid, SCompanyName, SContacts, SAddress, SPhone, SEmail, SCreateTime, SUpdateTime, SFax, ROW_NUMBER() OVER(ORDER BY Superid desc) as rank from Supplier where SDelete = 0 " + AddToSql + " ) as t where t.rank between (((" + index + " - 1) * " + size + ") + 1) and(" + index + " * " + size + ") ORDER BY Superid desc";
//string SuperPageSQL = "select top "+size+ " Superid,SCompanyName,SContacts,SAddress,SPhone,SEmail,SCreateTime,SUpdateTime,SFax from Supplier where SDelete=0 " + AddToSql + " and (Superid not in(select top (" + size+"*("+index+"-1)) Superid from Supplier order by Superid)) order by Superid ";
string PageTotal = "select count(*) from Supplier where SDelete=0 " + AddToSql + "";
string Data = Common.CommonClass.DataTableToJson(Com.Selcets(SupperPage));
string Count = Common.CommonClass.DataTableToJson(Com.Selcets(PageTotal));
Dictionary <string, object> dic = new Dictionary <string, object>();
dic.Add("Data", Data);
dic.Add("Count", Count);
return(dic);
}
public string GetDepartMentInfo()
{
string SelDepartSql = "select DepartId,Department from DepartMent";
string result = Common.CommonClass.DataTableToJson(Com.Selcets(SelDepartSql));
return(result);
}
/// <summary>
/// 入库单分页显示
/// </summary>
/// <param name="index"></param>
/// <param name="size"></param>
/// <returns></returns>
public Dictionary <string, object> PageStock(string index, string size)
{
string PageSql = @"select top " + size + " s.Stockid,s.Mid,u.Mnumber,u.Mname,u.Msellingprice,u.MCreateDate,t.UnitName ,d.DataText,UserName from StockIn s inner join Merchandise u on s.Mid=u.Mid inner join Unit t on u.Unitid=t.Unitid inner join Dictionary d on u.DictionaryID=d.DictionaryID left join Users r on r.UId=u.UId where StockDelete=0 and (Stockid not in (select top (" + size + "*(" + index + "-1)) s.Stockid from StockIn order by s.Stockid)) order by Stockid ";
string CountSql = @"select count(*) from StockIn";//计算总条数
string PageStr = Common.CommonClass.DataTableToJson(com.Selcets(PageSql));
string CountStr = Common.CommonClass.DataTableToJson(com.Selcets(CountSql));
Dictionary <string, object> dic = new Dictionary <string, object>();
dic.Add("PageStr", PageStr);
dic.Add("CountStr", CountStr);
return(dic);
}
/// <summary>
/// lookup加载客户信息
/// </summary>
/// <param name="name"></param>
/// <param name="index"></param>
/// <param name="size"></param>
/// <returns></returns>
public Dictionary <string, object> SelectAccount(string name, string index, string size)
{
string AddSQl = "";
if (name.Trim() != "")
{
AddSQl += " and AccountName like '%" + name + "%'";
}
string GetAccount = "select * from (select AccountId,AccountNo,AccountName,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="index"></param>
/// <param name="size"></param>
/// <returns></returns>
public Dictionary <string, object> EditGoodsPage(string index, string size, string product)
{
string AddSQl = "";
if (product.Trim() != "")
{
AddSQl = " and Mname like '%" + product + "%'";
}
string PageSQL = @"SELECT TOP " + size + " m.Mid,Mname,UnitName,Mnumber,m.Unitid,DataText,Msellingprice,ISNULL(InvertorySum,0) as InvertorySum,Mnote,MCreateDate FROM Merchandise m inner join Unit u on m.Unitid=u.Unitid inner join Dictionary d on m.DictionaryID=d.DictionaryID left join Inventory i on m.Mid=i.Mid WHERE MDelete=0 " + AddSQl + " and(m.Mid NOT IN(SELECT TOP (" + size + "*(" + index + "-1)) Mid FROM Merchandise ORDER BY Mid))ORDER BY m.Mid";
string CountSQL = @"select count(*) from Merchandise where MDelete=0 " + AddSQl + "";
string StrPage = Common.CommonClass.DataTableToJson(com.Selcets(PageSQL));
string StrCount = Common.CommonClass.DataTableToJson(com.Selcets(CountSQL));
Dictionary <string, object> dic = new Dictionary <string, object>();
dic.Add("StrPage", StrPage);
dic.Add("StrCount", StrCount);
return(dic);
}
/// <summary>
/// 子窗体显示供应商
/// </summary>
/// <param name="index"></param>
/// <param name="size"></param>
/// <param name="name"></param>
/// <returns></returns>
public Dictionary <string, object> PuSupplier(string index, string size, string name)
{
string LikeSql = "";
if (name.Trim() != "")
{
LikeSql += " and SCompanyName like '%" + name + "%' ";
}
string PageSql = @"select * from (select Superid ,SCompanyName,SupplierNumber,ROW_NUMBER() OVER(ORDER BY Superid)AS RANK FROM Supplier WHERE SDelete= 0) as t where t.rank between (((" + index + "-1)*" + size + ")+1) and (" + index + "*" + size + ") order by Superid";
string CountSql = @"select count(*) from Supplier where SDelete=0 " + LikeSql + "";
Common.CommonClass com = new Common.CommonClass();
string DataSql = Common.CommonClass.DataTableToJson(com.Selcets(PageSql));
string DataCount = Common.CommonClass.DataTableToJson(com.Selcets(CountSql));
Dictionary <string, object> dic = new Dictionary <string, object>();
dic.Add("DataSql", DataSql);
dic.Add("DataCount", DataCount);
return(dic);
}
/// <summary>
/// 显示子窗口仓库列表
/// </summary>
/// <param name="index"></param>
/// <param name="size"></param>
/// <returns></returns>
public Dictionary <string, object> DpotPage(string index, string size, string name)
{
string LikeSql = "";
if (name.Trim() != "")
{
LikeSql += " and WareHouseName like '%" + name + "%' ";
}
string PageSql = @"select top " + size + " n.Wid, Depotid,WareHousDate,DepotName,DepotStatus ,WareHouseAddress from WareHouse w inner join WareName n on w.wid=n.Wid inner join Depot d on d.WHid=w.WHid where WDelete=0 " + LikeSql + " and (Depotid not in (select top (" + size + "*(" + index + "-1))Depotid from WareHouse order by Depotid)) order by Depotid";
string CountSql = @"select count(*) from WareHouse w inner join WareName n on w.wid=n.Wid inner join Depot d on d.WHid=w.WHid where WDelete=0 " + LikeSql + "";
Common.CommonClass com = new Common.CommonClass();
string DataSql = Common.CommonClass.DataTableToJson(com.Selcets(PageSql));
string DataCount = Common.CommonClass.DataTableToJson(com.Selcets(CountSql));
Dictionary <string, object> dic = new Dictionary <string, object>();
dic.Add("DataSql", DataSql);
dic.Add("DataCount", DataCount);
return(dic);
}