/// <summary>
/// 查询货架名是否重复
/// </summary>
/// <param name="shelves">货架实体对象</param>
/// <returns></returns>
public int selectByName(GoodsShelves shelves)
{
//货架ID
string cmdText = "select count(goodsShelvesId) from T_GoodsShelves where goodsShelvesId=@goodsShelvesId";
String[] param = { "@goodsShelvesId" };
String[] values = { shelves.GoodsShelvesId };
int row = Convert.ToInt32(db.ExecuteScalar(cmdText, param, values));
//货架名称
string cmdTexts = "select count(goodsShelvesId) from T_GoodsShelves where regionId = @regionId and shelvesName=@shelvesName";
String[] parames = { "@shelvesName", "@regionId" };
String[] valuess = { shelves.ShelvesName, shelves.RegionId.RegionId.ToString() };
int rows = Convert.ToInt32(db.ExecuteScalar(cmdTexts, parames, valuess));
if (row > 0 && rows > 0)//都重复
{
return(2);
}
else if (row > 0 && rows <= 0)//货架ID重复
{
return(1);
}
else if (rows > 0 && row <= 0)//货架名称重复
{
return(-1);
}
else//都不重复
{
return(0);
}
}
/// <summary>
/// 判断单头编号
/// </summary>
/// <param name="saleHeadId">销售任务ID</param>
/// <returns></returns>
public int countHead()
{
string cmdText = "select count(saleHeadId) from T_SaleHead";
int row = Convert.ToInt32(db.ExecuteScalar(cmdText, null, null));
return(row);
}
/// <summary>
/// 查找组织名称是否存在
/// </summary>
/// <param name="regionName">组织名称</param>
/// <returns></returns>
public int isExit(string regionName)
{
string cmdText = "select count(regionName) from T_Region where regionName=@regionName and deleteState=0";
string[] param = { "@regionName" };
object[] values = { regionName };
int row = Convert.ToInt32(db.ExecuteScalar(cmdText, param, values));
return(row);
}
/// <summary>
/// 判断此书号是否有库存
/// </summary>
/// <param name="bookNum">书号</param>
/// <param name="goodsShelf">货架号</param>
/// <returns></returns>
public int GetByBookNum(string bookNum, string goodsShelf)
{
string cmdText = "select count(stockId) from T_Stock where bookNum=@bookNum and goodsShelvesId=@goodsShelf";
String[] param = { "@bookNum", "@goodsShelf" };
String[] values = { bookNum.ToString(), goodsShelf.ToString() };
int row = Convert.ToInt32(db.ExecuteScalar(cmdText, param, values));
return(row);
}
/// <summary>
/// 获取相应的时间数量
/// </summary>
/// <param name="time">时间</param>
/// <returns></returns>
public int getTimeCount(DateTime time)
{
string sql = "select count(makingTime) from T_SellOffHead where makingTime=@makingTime";
string[] param = { "@makingTime" };
object[] values = { time };
int row = int.Parse(db.ExecuteScalar(sql, param, values).ToString());
return(row);
}
/// <summary>
/// 通过书号查询销售单中是否有此数据
/// </summary>
/// <param name="bookNum"></param>
/// <returns></returns>
public int getBookCount(string bookNum)
{
string cmdText = "select count(bookNum) from V_SaleMonomer where bookNum=@bookNum";
string[] param = { "@bookNum" };
object[] values = { bookNum };
int row = int.Parse(db.ExecuteScalar(cmdText, param, values).ToString());
return(row);
}
/// <summary>
/// 查看单头数量
/// </summary>
/// <param name="type">类型，0：出库，1：入库，2：退货</param>
/// <returns></returns>
public int countHead(int type)
{
string cmdText = "select count(singleHeadId) from T_SingleHead where type=@type";
string[] param = { "@type" };
object[] values = { type };
int row = Convert.ToInt32(db.ExecuteScalar(cmdText, param, values));
return(row);
}
/// <summary>
/// 通过书号查看是否存在在单体中
/// </summary>
/// <param name="bookNum">书号</param>
/// <returns></returns>
public int selectByBookNum(string bookNum, string retailHeadId)
{
string cmdText = "select count(retailMonomerId) from T_RetailMonomer where bookNum=@bookNum and retailHeadId=@retailHeadId";
string[] param = { "@bookNum", "@retailHeadId" };
object[] values = { bookNum, retailHeadId };
int row = Convert.ToInt32(db.ExecuteScalar(cmdText, param, values));
return(row);
}
/// <summary>
/// 根据书号查找ISBN，单价，折扣
/// </summary>
/// <param name="ISBN">ISBN</param>
/// <returns></returns>
public DataSet SelectByBookNum(string bookNum)
{
MySqlHelp db = new MySqlHelp();
string comTexts = "select count(bookNum) from T_BookBasicData where bookNum=@bookNum";
string[] parames = { "@bookNum" };
object[] value = { bookNum };
int row = Convert.ToInt32(db.ExecuteScalar(comTexts, parames, value));
if (row == 0)
{
return(null);
}
else
{
string comText = "select bookNum,ISBN,price,remarks as discount,bookName from T_BookBasicData where bookNum=@bookNum";
string[] param = { "@bookNum" };
object[] values = { bookNum };
DataSet ds = db.FillDataSet(comText, param, values);
if (ds != null || ds.Tables[0].Rows.Count > 0)
{
return(ds);
}
else
{
return(null);
}
}
}
/// <summary>
/// 根据ISBN查找书号，单价，折扣
/// </summary>
/// <param name="ISBN">ISBN</param>
/// <returns></returns>
public DataSet SelectByIsbn(string ISBN)
{
MySqlHelp db = new MySqlHelp();
string comTexts = "select count(bookNum) from T_BookBasicData where ISBN=@ISBN";
string[] parames = { "@ISBN" };
object[] value = { ISBN };
int row = Convert.ToInt32(db.ExecuteScalar(comTexts, parames, value));
if (row == 0)
{
return(null);
}
else
{
string comText = "select bookNum,ISBN,price,author,bookName,supplier from T_BookBasicData where ISBN=@ISBN";
string[] param = { "@ISBN" };
object[] values = { ISBN };
DataSet ds = db.FillDataSet(comText, param, values);
if (ds != null || ds.Tables[0].Rows.Count > 0)
{
return(ds);
}
else
{
return(null);
}
}
}
/// <summary>
/// 获取当前时间
/// </summary>
/// <returns></returns>
public String getDate()
{
string cmdText = "select now()";
String[] param = null;
object[] values = null;
return(db.ExecuteScalar(cmdText, param, values).ToString());
}
/// <summary>
/// 查找账号是否存在
/// </summary>
/// <param name="customerId">账号</param>
/// <returns>符合条件的记录条数</returns>
public int SelectById(string customerId, string customName)
{
string cmdText = "select count(customerID) from T_Customer where customerID=@customerId or customerName=@customName";
string[] param = { "@customerId", "@customName" };
string[] values = { customerId, customName };
return(Convert.ToInt32(db.ExecuteScalar(cmdText, param, values)));
}
/// <summary>
/// 取行数
/// </summary>
/// <returns></returns>
public int SelectCount()
{
MySqlHelp db = new MySqlHelp();
string comText = "select count(bookNum) from T_BookBasicData";
int count = Convert.ToInt32(db.ExecuteScalar(comText, null, null));
return(count);
}
/// <summary>
/// 查询馆藏数据是否存在
/// </summary>
/// <param name="customerId">客户id</param>
/// <param name="bookNum">ISBN</param>
/// <returns>受影响行数</returns>
public int Selectbook(string customerId, string ISBN)
{
string comText = "select count(ISBN) from T_LibraryCollection where customerId=@customerId and ISBN=@ISBN";
string[] param = { "@customerId", "@ISBN" };
string[] values = { customerId, ISBN };
int rows = int.Parse(db.ExecuteScalar(comText, param, values).ToString());
return(rows);
}
public int isExist(string regionName)
{
string cmdText = "select count(regionName) from t_configuration where regionName=@regionName";
string[] param = { "@regionName" };
object[] values = { regionName };
int count = int.Parse(db.ExecuteScalar(cmdText, param, values).ToString());
return(count);
}
/// <summary>
/// 获取单头数量
/// </summary>
/// <returns>行数</returns>
public int countHead()
{
string cmdText = "select count(rsHeadID) from T_ReplenishmentHead";
int row = Convert.ToInt32(db.ExecuteScalar(cmdText, null, null));
return(row);
}
/// <summary>
/// 添加用户信息
/// </summary>
/// <param name="user">用户实体对象</param>
/// <returns>受影响行数</returns>
public int Insert(User user)
{
int row;
string userId = user.UserId;
string cmd = "select count(userId) from T_User where userId=@userId";
string[] param1 = { "@userId" };
object[] values1 = { userId };
row = Convert.ToInt32(db.ExecuteScalar(cmd, param1, values1));
if (row > 0)
{
return(row = 0);
}
else
{
string comText = "insert into T_User(userID,userPwd,userName,regionId,roleId) values(@userID, @userPwd,@userName,@regionId,@roleId)";
string[] param = { "@userID", "@userName", "@regionId", "@roleId", "@userPwd" };
object[] values = { user.UserId, user.UserName, user.ReginId.RegionId, user.RoleId.RoleId, user.Pwd };
row = db.ExecuteNoneQuery(comText, param, values);
return(row);
}
}
/// <summary>
/// 根据ISBN查找书号，单价，折扣
/// </summary>
/// <param name="ISBN">ISBN</param>
/// <returns></returns>
public DataTable SelectByIsbn(string ISBN)
{
MySqlHelp db = new MySqlHelp();
string comTexts = "select count(bookNum) from T_BookBasicData where ISBN=@ISBN";
string[] parames = { "@ISBN" };
object[] value = { ISBN };
int row = Convert.ToInt32(db.ExecuteScalar(comTexts, parames, value));
if (row == 0)
{
return(null);
}
else
{
string comText = "select bookNum,ISBN,price,remarks as discount,bookName from T_BookBasicData where ISBN=@ISBN";
string[] param = { "@ISBN" };
object[] values = { ISBN };
DataSet ds = db.FillDataSet(comText, param, values);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
DataTable excel = new DataTable();
excel.Columns.Add("bookNum");
excel.Columns.Add("ISBN");
excel.Columns.Add("price");
excel.Columns.Add("discount");
excel.Columns.Add("bookName");
DataRowCollection count = ds.Tables[0].Rows;
foreach (DataRow row1 in count)
{
string bookName = ToDBC(row1[4].ToString());
excel.Rows.Add(row1[0], row1[1], row1[2], row1[3], bookName);
}
return(excel);
}
else
{
return(null);
}
}
}
/// <summary>
/// 根据书号查找isbn，单价，折扣
/// </summary>
/// <param name="bookNum">书号</param>
/// <returns></returns>
public BookBasicData SelectByBookNum(long bookNum, string retailHeadId)
{
MySqlHelp db = new MySqlHelp();
string comTexts = "select count(id) from T_RetailMonomer where bookNum=@bookNum and retailHeadId=@retailHeadId";
string[] parames = { "@bookNum", "@retailHeadId" };
object[] value = { bookNum, retailHeadId };
int row = Convert.ToInt32(db.ExecuteScalar(comTexts, parames, value));
if (row == 0)
{
return(null);
}
else
{
string comText = "select ISBN,price,bookName,supplier,remarks from T_BookBasicData where bookNum=@bookNum";
string[] param = { "@bookNum" };
object[] values = { bookNum };
DataSet ds = db.FillDataSet(comText, param, values);
if (ds != null || ds.Tables[0].Rows.Count > 0)
{
string isbn = ds.Tables[0].Rows[0]["isbn"].ToString();
string price = ds.Tables[0].Rows[0]["price"].ToString();
string remarks = ds.Tables[0].Rows[0]["remarks"].ToString();
string bookName = ds.Tables[0].Rows[0]["bookName"].ToString();
string supplier = ds.Tables[0].Rows[0]["supplier"].ToString();
BookBasicData bookBasic = new BookBasicData();
bookBasic.Isbn = isbn;
bookBasic.Price = Convert.ToDouble(price);
bookBasic.Remarks = remarks;
bookBasic.BookName = bookName;
bookBasic.Publisher = supplier;
return(bookBasic);
}
else
{
return(null);
}
}
}
/// <summary>
/// 根据书号查找isbn，单价，折扣
/// </summary>
/// <param name="bookNum">书号</param>
/// <returns></returns>
public SaleMonomer SelectBookNum(string bookNum, string retailHeadId)
{
MySqlHelp db = new MySqlHelp();
string comTexts = "select count(*) from T_RetailMonomer where bookNum=@bookNum and retailHeadId=@retailHeadId";
string[] parames = { "@bookNum", "@retailHeadId" };
object[] value = { bookNum, retailHeadId };
int row = Convert.ToInt32(db.ExecuteScalar(comTexts, parames, value));
if (row == 0)
{
return(null);
}
else
{
string comText = "select bookName,bookNum,ISBN,number,unitPrice,realDiscount,totalPrice,realPrice from V_RetailMonomer where bookNum=@bookNum";
string[] param = { "@bookNum" };
object[] values = { bookNum };
DataSet ds = db.FillDataSet(comText, param, values);
if (ds != null || ds.Tables[0].Rows.Count > 0)
{
SaleMonomer retail = new SaleMonomer();
retail.ISBN1 = ds.Tables[0].Rows[0]["ISBN"].ToString();
retail.BookName = ds.Tables[0].Rows[0]["bookName"].ToString();
retail.BookNum = ds.Tables[0].Rows[0]["bookNum"].ToString();
retail.Number = Convert.ToInt32(ds.Tables[0].Rows[0]["number"]);
retail.UnitPrice = Convert.ToDouble(ds.Tables[0].Rows[0]["unitPrice"]);
retail.RealDiscount = Convert.ToDouble(ds.Tables[0].Rows[0]["realDiscount"]);
retail.TotalPrice = Convert.ToDouble(ds.Tables[0].Rows[0]["totalPrice"]);
retail.RealPrice = Convert.ToDouble(ds.Tables[0].Rows[0]["realPrice"]);
return(retail);
}
else
{
return(null);
}
}
}