/// <summary>
/// datagrid分页 传入 5个人参数
/// </summary>
/// <param name="index"></param>
/// <param name="count"></param>
/// <param name="orderid"></param>
/// <param name="begin"></param>
/// <param name="end"></param>
/// <param name="dataid"></param>
/// <returns></returns>
public static List <t_BuyOrder> getIDdataa(int index, int count, string orderid, string begin, string end, int dataid)
{
string sql = "";
sql = string.Format(@"select top {0} BuyOrderID,b.State,BuyOrderDate,HouseName,TotalPrice,
SubareaName,UserName,t.StateName from t_BuyOrder b ,t_StoreHouse st ,t_Ste t
,t_StoreHouseDetail sh where b.StoreHouseID=st.HouseID and t.StateID=b.State
and b.HouseDetailID=sh.ID and BuyOrderID not
in(select top {1} BuyOrderID from t_BuyOrder)
and (BuyOrderDate
between '{2}' and '{3}' and BuyOrderID= '{4}' and b.State={5} )", count, (index - 1) * count, begin, end, orderid, dataid);
DataTable dt = DBHelper.GetDatatable(sql);
List <t_BuyOrder> list = new List <t_BuyOrder>();
foreach (DataRow item in dt.Rows)
{
t_BuyOrder e = new t_BuyOrder();
e.BuyOrderID = item["BuyOrderID"].ToString();
e.BuyOrderDate = item["BuyOrderDate"].ToString();
e.HouseName = item["HouseName"].ToString();
e.SubareaName = item["SubareaName"].ToString();
e.TotalPrice = Convert.ToDouble(item["TotalPrice"]);
e.StateName = item["StateName"].ToString();
e.UserName = item["UserName"].ToString();
list.Add(e);
}
return(list);
}
/// <summary>
/// 登录验证
/// </summary>
/// <returns></returns>
public static int getLogin(t_User u)
{
string sql = string.Format(@"select *from t_User where UserName='******' and Password='******'", u.UserName, u.Password);
DataTable ds = DBHelper.GetDatatable(sql);
return(ds.Rows.Count);
}
public static List <t_BuyReceipt> getinfobuyreceipt(int index, int count)
{
string sql = "";
sql = string.Format(@"select top {0} r.ReceiptOrderID,r.ReceiptOrderDate ,s.HouseName,h.SubareaName
,r.UserName,r.TotalPrice,t.StateName
from t_BuyReceipt r,t_StoreHouse s,t_StoreHouseDetail h,t_Ste t where
r.State=t.StateID and r.HouseDetailID=s.HouseID and r.StoreHouseID=h.ID
and t.StateID=r.State and ReceiptOrderID not
in(select top {1} ReceiptOrderID from t_BuyReceipt) ", count, (index - 1) * count);
DataTable dt = DBHelper.GetDatatable(sql);
List <t_BuyReceipt> list = new List <t_BuyReceipt>();
foreach (DataRow item in dt.Rows)
{
t_BuyReceipt e = new t_BuyReceipt();
e.ReceiptOrderID = item["ReceiptOrderID"].ToString();
e.ReceiptOrderDate = item["ReceiptOrderDate"].ToString();
e.HouseName = item["HouseName"].ToString();
e.SubareaName = item["SubareaName"].ToString();
e.TotalPrice = Convert.ToInt32(item["TotalPrice"]);
e.StateName = item["StateName"].ToString();
e.UserName = item["UserName"].ToString();
list.Add(e);
}
return(list);
}
public static int Getcounta()
{
string sql = string.Format(@"select BuyOrderID,BuyOrderDate,HouseName,TotalPrice,
SubareaName,UserName,t.StateName from t_BuyOrder b ,t_StoreHouse st ,t_Ste t
,t_StoreHouseDetail sh where b.StoreHouseID=st.HouseID and t.StateID=b.State
and b.HouseDetailID=sh.ID ");
DataTable dt = DBHelper.GetDatatable(sql);
return(dt.Rows.Count);
}
public static DataTable getindexd(int a)
{
string sql = string.Format(@"select distinct dd.AuthorityID,dd.WebUrl, dd.TypeID,dd.AuthorityName ,dd.GroupID from t_User u join
(select t.AuthorityID, t.WebUrl, t.AuthorityName, g.GroupID, t.TypeID from t_Authority
t join t_GroupAuthority g on t.AuthorityID = g.AuthorityID)
dd on u.GroupID = dd.GroupID and dd.GroupID='{0}' and dd.TypeID=4", a);
DataTable dt = DBHelper.GetDatatable(sql);
return(dt);
}
public static int Getcount()
{
string sql = string.Format(@"select r.ReceiptOrderID,r.ReceiptOrderDate ,s.HouseName,h.SubareaName
,r.UserName,r.TotalPrice,t.StateName
from t_BuyReceipt r,t_StoreHouse s,t_StoreHouseDetail h,t_Ste t where
r.State=t.StateID and r.HouseDetailID=s.HouseID and r.StoreHouseID=h.ID
and t.StateID=r.State");
DataTable dt = DBHelper.GetDatatable(sql);
return(dt.Rows.Count);
}
public static int getLoginTypeid(t_User u)
{
string sql = string.Format("select GroupID from t_User where UserName='******' and Password='******'", u.UserName, u.Password);
DataTable ds = DBHelper.GetDatatable(sql);
t_User f = new t_User();
foreach (DataRow item in ds.Rows)
{
f.TypeID = Convert.ToInt32(item["GroupID"]);
}
return(f.TypeID);
}
public static List <t_BuyOrder> GetEmp(int index, int count)
{
string sql = string.Format(@"select top {0} BuyOrderID,BuyOrderDate,HouseName,TotalPrice,
SubareaName,UserName,t.StateName from t_BuyOrder b ,t_StoreHouse st ,t_Ste t
,t_StoreHouseDetail sh where b.StoreHouseID=st.HouseID and t.StateID=b.State
and b.HouseDetailID=sh.ID and BuyOrderID not in(select top {1} BuyOrderID from t_BuyOrder)
", count, (index - 1) * count);
DataTable dt = DBHelper.GetDatatable(sql);
List <t_BuyOrder> list = new List <t_BuyOrder>();
foreach (DataRow item in dt.Rows)
{
t_BuyOrder e = new t_BuyOrder();
e.BuyOrderID = item["BuyOrderID"].ToString();
e.BuyOrderDate = item["BuyOrderDate"].ToString();
e.HouseName = item["HouseName"].ToString();
e.SubareaName = item["SubareaName"].ToString();
e.TotalPrice = Convert.ToDouble(item["TotalPrice"]);
e.StateName = item["StateName"].ToString();
e.UserName = item["UserName"].ToString();
list.Add(e);
}
return(list);
}