//查询全部
public List <Book.Model.T_Stock_In> GetAll()
{
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select * from V_InHead_Provider";
SqlDataReader reader = cmd.ExecuteReader();
List <Book.Model.T_Stock_In> list = new List <Model.T_Stock_In>();
while (reader.Read())
{
Book.Model.T_Stock_In item = new Model.T_Stock_In();
Book.Model.T_Stock_InHead head = new Model.T_Stock_InHead();
Book.Model.T_Base_Provider provider = new Model.T_Base_Provider();
head.Id = Convert.ToInt32(reader["Id"]);
head.UserName = Convert.ToString(reader["UserName"]);
head.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
head.TotalMoney = Convert.ToDecimal(reader["TotalMoney"]);
head.ProviderId = Convert.ToInt32(reader["ProviderId"]);
provider.Id = Convert.ToInt32(reader["ProviderId"]);
provider.ProviderName = Convert.ToString(reader["ProviderName"]);
provider.ProviderTel = Convert.ToString(reader["ProviderTel"]);
provider.Memo = Convert.ToString(reader["Memo"]);
head.Provider = provider;
item.Head = head;
item.Items = null;
list.Add(item);
}
reader.Close();
co.Close();
return(list);
}
public JsonResult AddSave(Book.Model.T_Stock_InHead Head, Book.Model.T_Stock_InItems[] Items)
{
Book.BLL.T_Stock_In bll = new BLL.T_Stock_In();
Book.Model.T_Stock_In inStock = new Model.T_Stock_In();
inStock.Head = Head;
inStock.Items = Items.ToList();
bll.AddSave(inStock);
return(Json(1));
}
public bool Add(Model.T_Stock_In inStock)
{
//throw new NotImplementedException();
decimal totalMoney = 0;
foreach (Book.Model.T_Stock_InItems item in inStock.Items)
{
totalMoney += item.Amount * item.Discount * item.Price;
}
inStock.Head.TotalMoney = totalMoney;
return(dal.Add(inStock));
}
public JsonResult AddSave(Book.Model.T_Stock_InHead Head, Book.Model.T_Stock_InItems[] Items)
{
Book.BLL.T_Stock_In bll = new BLL.T_Stock_In();
Book.Model.T_Stock_In inStock = new Model.T_Stock_In();
inStock.Head = Head;
inStock.Items = Items.ToList();
bool result = bll.Add(inStock);
return(Json(new Book.Model.Message()
{
Code = 1, Content = "保存成功"
}));
}
public JsonResult EditSave(Book.Model.T_Stock_InHead Head, Book.Model.T_Stock_InItems[] Items)
{
Book.BLL.T_Stock_In bll = new BLL.T_Stock_In();
String [] tmp = new String[1];
tmp[0] = "" + Head.Id;
bll.Delete(tmp);
Book.Model.T_Stock_In inStock = new Model.T_Stock_In();
inStock.Head = Head;
inStock.Items = Items.ToList();
bool result = bll.Add(inStock);
return(Json(new Book.Model.Message()
{
Code = 1, Content = "保存成功"
}));
}
public bool Add(Model.T_Stock_In inStock)
{
//throw new NotImplementedException();
SqlConnection co = new SqlConnection();
co.ConnectionString = connstring;
co.Open();
SqlTransaction tran = co.BeginTransaction();
SqlCommand cm = new SqlCommand();
cm.Connection = co;
cm.Transaction = tran;
try
{
cm.Parameters.Clear();
cm.CommandText = "insert into t_stock_inhead (UserName,CreateTime,ProviderId,TotalMoney) values (@UserName,@CreateTime,@ProviderId,@TotalMoney); select @@identity";
cm.Parameters.AddWithValue("@UserName", inStock.Head.UserName);
cm.Parameters.AddWithValue("@ProviderId", inStock.Head.ProviderId);
cm.Parameters.AddWithValue("@CreateTime", inStock.Head.CreateTime);
cm.Parameters.AddWithValue("@TotalMoney", inStock.Head.TotalMoney);
object result = cm.ExecuteScalar();
int headId = Convert.ToInt32(result);
foreach (Book.Model.T_Stock_InItems item in inStock.Items)
{
cm.Parameters.Clear();
cm.CommandText = "insert into t_stock_initems (HeadId,BookId,Amount,Discount) values (@HeadId,@BookId,@Amount,@Discount)";
cm.Parameters.AddWithValue("@HeadId", headId);
cm.Parameters.AddWithValue("@BookId", item.BookId);
cm.Parameters.AddWithValue("@Amount", item.Amount);
cm.Parameters.AddWithValue("@Discount", item.Discount);
cm.ExecuteNonQuery();
}
tran.Commit();//提交
return(true);
}
catch (Exception ex)
{
tran.Rollback();//遇到错误，回滚
}
finally
{
co.Close();
}
return(false);
}
public List <Book.Model.T_Stock_In> GetList(int CurrentPage, int PageSize)
{
SqlConnection co = new SqlConnection();
co.ConnectionString = connstring;
co.Open();
SqlCommand cm = new SqlCommand();
cm.Connection = co;
cm.CommandText = "select top " + PageSize + " * from [V_InHead_Provider] where id not in (select top " + PageSize * (CurrentPage - 1) + " id from [V_InHead_Provider] )";
SqlDataReader dr = cm.ExecuteReader();
List <Book.Model.T_Stock_In> lst = new List <Model.T_Stock_In>();
while (dr.Read())
{
Book.Model.T_Stock_In inStock = new Model.T_Stock_In();
Book.Model.T_Stock_InHead head = new Model.T_Stock_InHead();
inStock.Id = Convert.ToInt32(dr["Id"]);
inStock.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
inStock.ProviderId = Convert.ToInt32(dr["ProviderId"]);
inStock.TotalMoney = Convert.ToDecimal(dr["TotalMoney"]);
inStock.UserName = Convert.ToString(dr["UserName"]);
head.Id = Convert.ToInt32(dr["Id"]);
head.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
head.ProviderId = Convert.ToInt32(dr["ProviderId"]);
head.TotalMoney = Convert.ToDecimal(dr["TotalMoney"]);
head.UserName = Convert.ToString(dr["UserName"]);
Book.Model.T_Base_Provider provider = new Model.T_Base_Provider();
provider.Id = Convert.ToInt32(dr["Id"]);
provider.Name = Convert.ToString(dr["Name"]);
provider.Tel = Convert.ToString(dr["Tel"]);
provider.Fax = Convert.ToString(dr["Fax"]);
provider.Memo = Convert.ToString(dr["Memo"]);
head.Provider = provider;
inStock.Head = head;
inStock.Items = null;
lst.Add(inStock);
}
dr.Close();
co.Close();
return(lst);
}
public Book.Model.T_Stock_In GetModel(int HeadId)
{
SqlConnection co = new SqlConnection();
co.ConnectionString = connstring;
co.Open();
Book.Model.T_Stock_In stockIn = new Model.T_Stock_In();
//stockIn.Head.Id = HeadId;
stockIn.Head = null;
stockIn.Items = new List <Model.T_Stock_InItems>();
SqlCommand cm = new SqlCommand();
cm.CommandText = "select * from V_InItem_Book where headid = @headid";
cm.Parameters.AddWithValue("@headid", HeadId);
cm.Connection = co;
SqlDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
Book.Model.T_Stock_InItems item = new Model.T_Stock_InItems();
item.HeadId = HeadId;
item.Id = Convert.ToInt32(dr["Id"]);
item.Discount = Convert.ToDecimal(dr["Discount"]);
Book.Model.T_Base_Book book = new Model.T_Base_Book();
book.Author = Convert.ToString(dr["Author"]);
book.BookName = Convert.ToString(dr["BookName"]);
book.Id = Convert.ToInt32(dr["BookId"]);
book.PressName = Convert.ToString(dr["PressName"]);
book.Price = Convert.ToDecimal(dr["Price"]);
book.SN = Convert.ToString(dr["SN"]);
book.Version = Convert.ToInt32(dr["Version"]);
item.BookId = book.Id;
item.Book = book;
item.Amount = Convert.ToInt32(dr["Amount"]);
stockIn.Items.Add(item);
}
dr.Close();
co.Close();
return(stockIn);
}
///分页实现过程
public List <Book.Model.T_Stock_In> GetList(int pageNumber, int pageSize)
{
List <Book.Model.T_Stock_In> list = new List <Model.T_Stock_In>();
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select top " + pageSize + " * from v_InHead_Provider where Id not in(select top " + (pageNumber - 1) * pageSize + " Id from v_InHead_Provider)";
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Book.Model.T_Stock_In item = new Model.T_Stock_In();
Book.Model.T_Stock_InHead head = new Model.T_Stock_InHead();
Book.Model.T_Base_Provider provider = new Model.T_Base_Provider();
head.Id = Convert.ToInt32(reader["Id"]);
head.UserName = Convert.ToString(reader["UserName"]);
head.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
head.TotalMoney = Convert.ToDecimal(reader["TotalMoney"]);
head.ProviderId = Convert.ToInt32(reader["ProviderId"]);
provider.Id = Convert.ToInt32(reader["ProviderId"]);
provider.ProviderName = Convert.ToString(reader["ProviderName"]);
provider.ProviderTel = Convert.ToString(reader["ProviderTel"]);
provider.Memo = Convert.ToString(reader["Memo"]);
head.Provider = provider;
item.Head = head;
item.Items = null;
list.Add(item);
}
reader.Close();
co.Close();
int result = GetCount();
return(list);
}
public ActionResult AddSave()
{
Model.T_Stock_In in_stock = new Model.T_Stock_In();
//获得表头数据
in_stock.Head = new book.Model.T_Stock_InHead();
in_stock.Head.Creater = Request.Form["StockName"];
in_stock.Head.CreateTime = Convert.ToDateTime(Request.Form["StockTime"]);
in_stock.Head.ProviderId = Convert.ToInt16(Request.Form["Provider.Id"]);
//获取详细内容表数据
in_stock.items = new List <Model.T_Stock_InItems>();
int i = 0;
while (Request.Form["items[" + i + "].item.BookName"] != null)
{
Model.T_Stock_InItems item = new Model.T_Stock_InItems();
item.BookId = Convert.ToInt32(Request.Form["items[" + i + "].item.Id"].Replace(",", ""));
item.Discount = Convert.ToDecimal(Request.Form["items[" + i + "].item.Discount"]);
item.Amount = Convert.ToInt32(Request.Form["items[" + i + "].item.Amount"]);
in_stock.items.Add(item);
i++;
}
BLL.T_Stock_InBLL bllIn = new BLL.T_Stock_InBLL();
bool result = bllIn.Add(in_stock);
if (result)
{
Response.Write("{\"statusCode\":\"200\",\"message\":\"操作成功\",\"navTabId\":\"bookmanager\",\"rel\":\"bookmanager\",\"callbackType\":\"closeCurrent\",\"forwardUrl\":\"\"}");
}
else
{
Response.Write("{\"statusCode\":\"300\",\"message\":\"操作失败\",\"navTabId\":\"bookmanager\",\"rel\":\"bookmanager\",\"callbackType\":\"closeCurrent\",\"forwardUrl\":\"\"}");
}
return(null);
}
