//判断条件,都满足的情况下
public static List <addorder> GetSelect(int rows, int page, string outid, string create, string trade, int statename)
{
string sqlstr = string.Format(@"select top {0} outs.DetailID, p.ProductsName,out.OutID,out.CreateDate,st.HouseName,
sto.SubareaName,out.TradeDate,outs.Quantity,outs.Price,s.StateName
from t_OutStock out,t_OutStockDetail outs,t_Products p,t_Ste s,t_StoreHouse st
,t_StoreHouseDetail sto where out.OutID=outs.OutID and p.ProductsID=outs.ProductsID
and s.StateID=out.State and out.StoreHouseID=st.HouseID and out.HouseDetailID=sto.ID
and outs.DetailID not in (select top {1} DetailID
from t_OutStock out,t_OutStockDetail outs,t_Products p,t_Ste s,t_StoreHouse st
,t_StoreHouseDetail sto where out.OutID=outs.OutID and p.ProductsID=outs.ProductsID
and s.StateID=out.State and out.StoreHouseID=st.HouseID and out.HouseDetailID=sto.ID)and out.OutID='{2}'
and out.CreateDate between '{3}' and '{4}' and s.StateID='{5}'", rows, (page - 1) * rows, outid, create, trade, statename);
List <addorder> list = new List <addorder>();
DataTable dt = DBHelper.GetDataTable(sqlstr);
foreach (DataRow item in dt.Rows)
{
addorder ad = new addorder();
ad.DetailID = Convert.ToInt32(item["DetailID"].ToString());
ad.ProductsName = item["ProductsName"].ToString();
ad.OutID = item["OutID"].ToString();
ad.CreateDate = item["CreateDate"].ToString();
ad.HouseName = item["HouseName"].ToString();
ad.SubareaName = item["SubareaName"].ToString();
ad.TradeDate = item["TradeDate"].ToString();
ad.Quantity = Convert.ToInt32(item["Quantity"].ToString());
ad.Price = item["Price"].ToString();
ad.StateName = item["StateName"].ToString();
list.Add(ad);
}
return(list);
}
//分页显示总行数
public static List <addorder> GetAll(int rows, int page)
{
string sqlstr = string.Format(@"select top {0} sa.SalesOutID,p.ProductsName,sa.CreateDate,st.HouseName,sto.SubareaName,
sa.TradeDate,sale.Quantity,sale.Price,s.StateName from t_SalesOut sa,t_SalesOutDetail sale,
t_Products p,t_StoreHouse st,t_StoreHouseDetail sto,t_Ste s where sa.SalesOutID=sale.SalesOutID
and sale.ProductsID=p.ProductsID and sale.StoreHouseID=st.HouseID
and sale.HouseDetailID=sto.ID and sa.State=s.StateID and sale.DetailID not in
(select top {1} DetailID from t_SalesOut sa,t_SalesOutDetail sale,t_Products p,t_StoreHouse st,
t_StoreHouseDetail sto,t_Ste s where sa.SalesOutID=sale.SalesOutID
and sale.ProductsID=p.ProductsID and sale.StoreHouseID=st.HouseID
and sale.HouseDetailID=sto.ID and sa.State=s.StateID )", rows, (page - 1) * rows);
List <addorder> list = new List <addorder>();
DataTable dt = DBHelper.GetDataTable(sqlstr);
foreach (DataRow item in dt.Rows)
{
addorder ad = new addorder();
ad.SalesOutID = item["SalesOutID"].ToString();
ad.ProductsName = item["ProductsName"].ToString();
ad.CreateDate = item["CreateDate"].ToString();
ad.HouseName = item["HouseName"].ToString();
ad.SubareaName = item["SubareaName"].ToString();
ad.TradeDate = item["TradeDate"].ToString();
ad.Quantity = Convert.ToInt32(item["Quantity"].ToString());
ad.Price = item["Price"].ToString();
ad.StateName = item["StateName"].ToString();
list.Add(ad);
}
return(list);
}
//采购订单管理根据订单编号以及其他条件进行全部查询
public static List <addorder> GetAdminAll(string BuyOrderID, string BuyOrderDate, string TradeDate, string Statename, int count, int index)
{
List <addorder> list = new List <addorder>();
string sqlstr = string.Format(@"select top '{0}' buy.BuyOrderID ,buy.BuyOrderDate ,buy.TradeDate ,st.HouseName ,
ho.SubareaName ,buy.UserName ,buy.TotalPrice ,ste.StateName
from t_BuyOrder buy,t_BuyOrderDetail buyo,t_StoreHouse st,
t_StoreHouseDetail ho,t_Ste ste where buy.BuyOrderID=buyo.BuyOrderID
and buy.StoreHouseID=st.HouseID and buy.HouseDetailID=ho.HouseID
and ste.StateID=buy.State and buy.BuyOrderID not in(select top '{1}' BuyOrderID
from t_BuyOrder) and (buy.BuyOrderID
between '{2}' and '{3}' and buy.BuyOrderID='{4}' and ste.statename='{5}' ",
count, (index - 1) * count, BuyOrderDate, TradeDate, BuyOrderID, Statename);
DataTable dt = DBHelper.GetDataTable(sqlstr);
foreach (DataRow item in dt.Rows)
{
addorder ad = new addorder();
ad.BuyOrderID = item["BuyOrderID"].ToString();
ad.BuyOrderDate = item["BuyOrderDate"].ToString();
ad.HouseName = item["HouseName"].ToString();
ad.SubareaName = item["SubareaName"].ToString();
ad.userName = item["userName"].ToString();
ad.TotalPrice = item["TotalPrice"].ToString();
ad.State = item["Statename"].ToString();
list.Add(ad);
}
return(list);
}
//商品总信息显示datagrid控件中
public static List <addorder> GetNum(int count, int index)
{
string sqlstr = string.Format(@"select top {0} p.ProductsID,p.ProductsName,su.SupplierName,buyo.Quantity,buy.TotalPrice,
buyo.TaxRate,buyo.DiscountRate,buyo.Price from t_Products p,t_Supplier su,t_BuyReceipt buy,
t_BuyReceiptDetail buyo where buy.ReceiptOrderID=buyo.ReceiptOrderID and buyo.ProductsID=p.ProductsID
and su.SupplierID=buyo.SupplierID
and buyo.DetailID not in(select top {1} buyo.DetailID from t_Products p,
t_Supplier su,t_BuyReceipt buy,t_BuyReceiptDetail buyo where buy.ReceiptOrderID=buyo.ReceiptOrderID and
buyo.ProductsID=p.ProductsID and su.SupplierID=buyo.SupplierID)order by buyo.DetailID", count, (index - 1) * count);
DataTable dt = DBHelper.GetDataTable(sqlstr);
List <addorder> list = new List <addorder>();
foreach (DataRow item in dt.Rows)
{
addorder ad = new addorder();
ad.ProductsID = Convert.ToInt32(item["ProductsID"].ToString());
ad.ProductsName = item["ProductsName"].ToString();
ad.SupplierName = item["SupplierName"].ToString();
ad.Quantity = Convert.ToInt32(item["Quantity"].ToString());
ad.TotalPrice = item["TotalPrice"].ToString();
ad.TaxRate = item["TaxRate"].ToString();
ad.DiscountRate = item["DiscountRate"].ToString();
ad.Price = item["Price"].ToString();
list.Add(ad);
}
return(list);
}
//显示购买退货的所有信息
public static List <addorder> GetAdd(int rows, int page)
{
string sqlstr = string.Format(@" select top {0} * from t_BuyReturn buy,t_BuyReturnDetail buyr,t_Supplier s,t_Products p
where buy.BuyReturnID=buyr.BuyReturnID and buyr.ProductsID=p.ProductsID and
buyr.SupplierID=s.SupplierID and buy.ReceiptOrderID not in( select top {1} buy.ReceiptOrderID from t_BuyReturn buy,t_BuyReturnDetail buyr,t_Supplier s,t_Products p
where buy.BuyReturnID=buyr.BuyReturnID and buyr.ProductsID=p.ProductsID and
buyr.SupplierID=s.SupplierID) ", rows, (page - 1) * rows);
List <addorder> list = new List <addorder>();
DataTable dt = DBHelper.GetDataTable(sqlstr);
foreach (DataRow item in dt.Rows)
{
addorder ad = new addorder();
ad.BuyReturnID = item["BuyReturnID"].ToString();
ad.ProductsName = item["ProductsName"].ToString();
ad.SupplierName = item["SupplierName"].ToString();
ad.Quantity = Convert.ToInt32(item["Quantity"].ToString());
ad.Price = item["Price"].ToString();
ad.ReceiptOrderID = item["ReceiptOrderID"].ToString();
ad.TotalPrice = item["TotalPrice"].ToString();
ad.AlreadyPay = item["AlreadyPay"].ToString();
list.Add(ad);
}
return(list);
}
//修改
public static bool GteUpdata(addorder ad)
{
string sqlstr = string.Format(@"update t_OutStock set State='{0}' where OutID='{1}'", ad.State, ad.OutID);
bool dt = DBHelper.ExecuteNon(sqlstr);
return(dt);
}
protected void Button1_Click(object sender, EventArgs e)
{
addorder ad = new addorder();
ad.BuyReceiptID = this.DropDownList2.SelectedValue;
ad.CreateDate = this.TextBox1.Text;
ad.Ticket = this.TextBox2.Text;
ad.RealPay = this.TextBox5.Text;
ad.AttachPay = this.TextBox6.Text;
ad.PayType = this.DropDownList1.SelectedItem.Text;
ad.Description = this.TextBox7.Text;
ad.userName = "******";
ad.State = "1";
bool row = SalePayManager.GetAdd(ad);
DataTable dt = SalePayManager.Get();
string buy = dt.Rows[0]["BuyReceiptID"].ToString();
if (this.DropDownList2.SelectedValue == buy)
{
Response.Write("<script>alert('已经有相关订单的数据，在此添加不可用！');</script>");
return;
}
else
{
if (row == true)
{
Response.Write("<script>alert('采购成功！');</script>");
}
else
{
Response.Write("<script>alert('采购失败！');</script>");
}
}
}
//信息添加入库
public static bool GetAdd(addorder ad)
{
string sqlstr = string.Format(@"insert into t_BuyPay(BuyReceiptID,CreateDate,Ticket,RealPay,AttachPay,PayType,Description,UserName,State)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')", ad.BuyReceiptID, ad.CreateDate, ad.Ticket, ad.RealPay, ad.AttachPay, ad.PayType, ad.Description, ad.userName, ad.State);
bool rows = DBHelper.ExecuteNon(sqlstr);
return(rows);
}
//添加商品信息
public static bool AddProduct(addorder ad)
{
string sqlstr = string.Format(@"insert into t_BuyOrderDetail(ProductsID,Price,Quantity,TaxRate,DiscountRate,SupplierID,Description,BuyOrderID)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", ad.ProductsID, ad.Price, ad.Quantity, ad.TaxRate, ad.DiscountRate, ad.SupplierID, ad.Description, ad.BuyOrderID);
bool rows = DBHelper.ExecuteNon(sqlstr);
return(rows);
}
//添加到退货数据库
public static bool GetALL(addorder ad)
{
string sqlstr = string.Format(@"insert into t_BuyReturn (BuyReturnID,BuyReturnDate,StoreHouseID,HouseDetailID,ReceiptOrderID,UserName,TradeDate,TotalPrice,AlreadyPay,Description,State)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')", ad.BuyReturnID, ad.BuyReturnDate, ad.StoreHouseID, ad.HouseDetailID, ad.ReceiptOrderID, ad.userName, ad.TradeDate, ad.TotalPrice, ad.AlreadyPay, ad.Description, ad.State);
bool rows = DBHelper.ExecuteNon(sqlstr);
return(rows);
}
//通过orderid 添加商品信息
public static bool GetA(addorder ad)
{
string sqlstr = string.Format(@"insert into t_BuyReturnDetail(BuyReturnID,ProductsID,SupplierID,Quantity,Price,Description)
values('{0}','{1}','{2}','{3}','{4}','{5}')", ad.BuyReturnID, ad.ProductsID, ad.SupplierID, ad.Quantity, ad.Price, ad.Description);
bool rows = DBHelper.ExecuteNon(sqlstr);
return(rows);
}
//添加采购订单
public static bool AddSaleorder(addorder ad)
{
string strsql = string.Format(@"insert into t_BuyOrder (BuyOrderID,BuyOrderDate,UserName,StoreHouseID,HouseDetailID,SignDate,TradeDate,TradeAddress,Description,TotalPrice,state)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')",
ad.BuyOrderID, ad.BuyOrderDate, ad.userName, ad.StoreHouseID, ad.HouseDetailID, ad.SignDate, ad.TradeDate, ad.TradeAddress, ad.Description, ad.TotalPrice, ad.State);
bool rows = DBHelper.ExecuteNon(strsql);
return(rows);
}
//添加信息到采购入库中
public static bool GetSale(addorder ad)
{
string sqlstr = string.Format(@"insert into t_BuyReceipt(ReceiptOrderID,ReceiptOrderDate,StoreHouseID,
HouseDetailID,BuyOrderID,TradeDate,UserName,TotalPrice,State,Description)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')", ad.ReceiptOrderID, ad.ReceiptOrderDate,
ad.StoreHouseID, ad.HouseDetailID, ad.BuyOrderID, ad.TradeDate, ad.userName, ad.TotalPrice, ad.State, ad.Description);
bool dt = DBHelper.ExecuteNon(sqlstr);
return(dt);
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
addorder ad = new addorder();
ad.SalesOutID = context.Request["saleid"].ToString();
ad.State = context.Request["statename"].ToString();
bool rows = adminSaleManager.GteUpdata(ad);
context.Response.Write(rows);
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
addorder add = new addorder();
add.ProductsID = Convert.ToInt32(context.Request["ProductsID"].ToString());
add.Price = context.Request["Price"].ToString();
add.Quantity = Convert.ToInt32(context.Request["Quantity"].ToString());
add.TaxRate = context.Request["TaxRate"].ToString();
add.DiscountRate = context.Request["DiscountRate"].ToString();
add.SupplierID = context.Request["SupplierID"].ToString();
add.Description = context.Request["Description"].ToString();
add.BuyOrderID = context.Request["BuyOrderID"].ToString();
bool rows = addorderManages.AddProduct(add);
context.Response.Write(rows);
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
addorder ad = new addorder();
ad.ReceiptOrderID = context.Request["ReceiptOrderID"].ToString();
ad.ReceiptOrderDate = context.Request["ReceiptOrderDate"].ToString();
ad.BuyOrderID = context.Request["BuyOrderID"].ToString();
ad.StoreHouseID = Convert.ToInt32(context.Request["StoreHouseID"].ToString());
ad.HouseDetailID = Convert.ToInt32(context.Request["HouseDetailID"].ToString());
ad.TradeDate = context.Request["TradeDate"].ToString();
ad.userName = context.Request["UserName"].ToString();
ad.Description = context.Request["Description"].ToString();
ad.TotalPrice = context.Request["TotalPrice"].ToString();
ad.State = context.Request["State"].ToString();
bool rows = SaleInManager.GetSale(ad);
context.Response.Write(rows);
}
protected void Button1_Click(object sender, EventArgs e)
{
addorder ad = new addorder();
ad.BuyReturnID = this.TextBox1.Text;
ad.ProductsID = Convert.ToInt32(this.DropDownList4.SelectedValue);
ad.SupplierID = this.DropDownList5.SelectedValue;
ad.Quantity = Convert.ToInt32(this.TextBox7.Text);
ad.Price = this.TextBox6.Text;
ad.Description = this.TextBox8.Text;
bool rows = salereturnManager.GetA(ad);
if (rows == true)
{
Response.Write("<script>alert('添加成功！')</script>");
}
else
{
Response.Write("<script>alert('添加失败！')</script>");
}
}
//信息添加入库
public static bool GetAdd(addorder ad)
{
return(SalePay.GetAdd(ad));
}
//添加信息到采购入库详情
public static bool insertProduct(addorder ad)
{
return(SaleInService.insertProduct(ad));
}
//添加信息到采购入库中
public static bool GetSale(addorder ad)
{
return(SaleInService.GetSale(ad));
}
//修改
public static bool GteUpdata(addorder ad)
{
return(adminInPService.GteUpdata(ad));
}
//通过orderid 添加商品信息
public static bool GetA(addorder ad)
{
return(salereturnService.GetA(ad));
}
//添加采购订单
public static bool AddSaleorder(addorder ad)
{
return(addorderService.AddSaleorder(ad));
}
//添加商品信息
public static bool AddProduct(addorder ad)
{
return(addorderService.AddProduct(ad));
}
