/// <summary>
/// 基础数据导出
/// </summary>
/// <returns></returns>
public DataTable excelBook(string search)
{
if (search == "")
{
String sql = @"select bookNum as '书号',ISBN,bookName as '书名',publishTime as '出版日期',price as '定价',supplier as '出版社',catalog as '预收数量',author as '进货折扣',remarks as '销售折扣',dentification as '备注' from t_bookbasicdata";
DataSet ds = db.FillDataSet(sql, null, null);
if (ds != null || ds.Tables[0].Rows.Count > 0)
{
DataTable dt = ds.Tables[0];
return(dt);
}
else
{
return(null);
}
}
else
{
String sql = @"select bookNum as '书号',ISBN,bookName as '书名',publishTime as '出版日期',price as '定价',supplier as '出版社',catalog as '预收数量',author as '进货折扣',remarks as '销售折扣',dentification as '备注' from t_bookbasicdata where " + search;
DataSet ds = db.FillDataSet(sql, null, null);
if (ds != null || ds.Tables[0].Rows.Count > 0)
{
DataTable dt = ds.Tables[0];
return(dt);
}
else
{
return(null);
}
}
}
public DataSet select()
{
string cmdText = "select * from t_configuration";
DataSet ds = db.FillDataSet(cmdText, null, null);
return(ds);
}
/// <summary>
/// 获取销退单头信息
/// </summary>
/// <returns></returns>
public DataSet Select(string sellOffHeadId)
{
string sql = "select sellOffHeadID,saleTaskId,userName,customerName,kinds,count,totalPrice,realPrice,makingTime,regionId,regionName from V_SellOffHead where sellOffHeadID=@sellOffHeadID";
string[] param = { "@sellOffHeadID" };
object[] values = { sellOffHeadId };
DataSet ds = db.FillDataSet(sql, param, values);
return(ds);
}
/// <summary>
/// 获取汇总数据
/// </summary>
/// <param name="cusId">客户ID</param>
/// <param name="search">查询条件</param>
/// <returns></returns>
public DataSet getSummary(int cusId, string search)
{
string sql = @"select count(bookNum) as kindsNum,sum(allTotalPrice) as atp,sum(allRealPrice) as arp,sum(allNum) as alln from((select ISBN,bookNum,bookName,unitPrice,sum(totalPrice) as allTotalPrice,sum(realPrice) as allRealPrice,sum(number) as allNum,realDiscount,dateTime,customerId from v_salemonomer where " + search + " GROUP BY bookNum) as temp)";
string[] param = { "@cusId" };
object[] value = { cusId };
DataSet ds = db.FillDataSet(sql, param, value);
return(ds);
}
/// <summary>
/// 获取所有角色信息
/// </summary>
/// <returns>数据集</returns>
public DataSet select()
{
string comText = "select roleId,roleName from T_Role";
DataSet ds = db.FillDataSet(comText, null, null);
if (ds != null || ds.Tables[0].Rows.Count > 0)
{
return(ds);
}
else
{
return(null);
}
}
/// <summary>
/// 获取所有地区信息
/// </summary>
/// <returns></returns>
public DataSet select()
{
string cmdText = "select regionId,regionName from T_Region where deleteState=0 order by convert(regionName using gbk) collate gbk_chinese_ci";
DataSet ds = db.FillDataSet(cmdText, null, null);
if (ds != null || ds.Tables[0].Rows.Count > 0)
{
return(ds);
}
else
{
return(null);
}
}
/// <summary>
/// 获取采集人
/// </summary>
/// <param name="strWhere">筛选条件</param>
/// <returns></returns>
public DataSet getUser(string strWhere)
{
String cmdText = "select userName from v_allsalemonomer where " + strWhere + " group by userName";
DataSet ds = db.FillDataSet(cmdText, null, null);
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 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>
/// 根据书号查找isbn，单价，折扣
/// </summary>
/// <param name="bookNum">书号</param>
/// <returns></returns>
public BookBasicData SelectById(string bookNum)
{
MySqlHelp db = new MySqlHelp();
string comText = "select ISBN,price,bookName,supplier,remarks,author 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();
string author = ds.Tables[0].Rows[0]["author"].ToString();
BookBasicData bookBasic = new BookBasicData();
bookBasic.Isbn = isbn;
bookBasic.Price = Convert.ToDouble(price);
bookBasic.Remarks = remarks;
bookBasic.BookName = bookName;
bookBasic.Publisher = supplier;
bookBasic.Author = author;
return(bookBasic);
}
else
{
return(null);
}
}
//导出当前查询
public DataSet exportTotal()
{
String cmdText = "";
string[] param = { };
object[] values = { };
DataSet ds = db.FillDataSet(cmdText, param, values);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
return(ds);
}
else
{
return(null);
}
}
/// <summary>
/// 根据地区id查询货架
/// </summary>
/// <param name="regionId">地区id</param>
/// <returns></returns>
public DataSet Select(int regionId)
{
string cmdText = "select goodsShelvesId,shelvesName,regionId,regionName from V_GoodsShelves where regionId = @regionId";
String[] param = { "@regionId" };
String[] values = { regionId.ToString() };
DataSet ds = db.FillDataSet(cmdText, param, values);
if (ds != null || ds.Tables[0].Rows.Count > 0)
{
return(ds);
}
else
{
return(null);
}
}
/// <summary>
/// 根据角色id查询功能数据
/// </summary>
/// <param name="roleId">角色ID</param>
/// <returns>返回查询到的表格数据</returns>
public DataSet SelectByRoleId(int roleId)
{
string cmdText = "select functionName,functionId from V_Permission where roleId=@roleId";
string[] param = { "@roleId" };
object[] values = { roleId };
DataSet ds = db.FillDataSet(cmdText, param, values);
if (ds != null || ds.Tables[0].Rows.Count > 0)
{
return(ds);
}
else
{
return(null);
}
}
/// <summary>
/// 获取补货单体信息
/// </summary>
/// <returns></returns>
public DataSet Select()
{
string cmdText = "SELECT * FROM V_ReplenishMentMononer";
DataSet ds = db.FillDataSet(cmdText, null, null);
return(ds);
}
/// <summary>
/// 根据书号，组织Id查询货架id，库存数量
/// </summary>
/// <param name="bookNum">书号</param>
/// <param name="regionId">组织Id</param>
/// <returns></returns>
public DataSet SelectByBookNum(string bookNum, int regionId)
{
string cmdText = "select goodsShelvesId,stockNum from T_Stock where bookNum = @bookNum and regionId = @regionId order by stockNum desc";
String[] param = { "@bookNum", "@regionId" };
String[] values = { bookNum.ToString(), regionId.ToString() };
DataSet ds = db.FillDataSet(cmdText, param, values);
if (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 0)
{
return(null);
}
else
{
return(ds);
}
}
/// <summary>
/// 查询客户数据
/// </summary>
/// <returns></returns>
public DataSet getCustomer()
{
MySqlHelp db = new MySqlHelp();
string comText = "select customerName,customerID from T_Customer where deleteState=0 order by convert(customerName using gbk) collate gbk_chinese_ci";
DataSet ds = db.FillDataSet(comText, null, null);
if (ds != null || ds.Tables[0].Rows.Count > 0)
{
return(ds);
}
else
{
return(null);
}
}
/// <summary>
/// 获取来源组织/收货组织
/// </summary>
/// <returns></returns>
public DataTable selectSource()
{
MySqlHelp db = new MySqlHelp();
string comText = "select distinct regionName from v_stockstatistics order by convert(regionName using gbk) collate gbk_chinese_ci";
DataSet ds = db.FillDataSet(comText, null, null);
if (ds != null || ds.Tables[0].Rows.Count > 0)
{
DataTable dt = ds.Tables[0];
return(dt);
}
else
{
return(null);
}
}
/// <summary>
/// 查询供应商
/// </summary>
/// <returns></returns>
public DataTable selectSupplier()
{
MySqlHelp db = new MySqlHelp();
string comText = "select distinct supplier from T_BookBasicData order by convert(supplier using gbk) collate gbk_chinese_ci";
DataSet ds = db.FillDataSet(comText, null, null);
if (ds != null || ds.Tables[0].Rows.Count > 0)
{
DataTable dt = ds.Tables[0];
return(dt);
}
else
{
return(null);
}
}
/// <summary>
/// 获取所有书本基础数据的ISBN，单价，书名
/// </summary>
/// <returns></returns>
public DataTable Select()
{
MySqlHelp db = new MySqlHelp();
string comText = "select ISBN,price,bookName,supplier,author,remarks from T_BookBasicData order by convert(supplier using gbk) collate gbk_chinese_ci";
DataSet ds = db.FillDataSet(comText, null, null);
if (ds != null || ds.Tables[0].Rows.Count > 0)
{
DataTable dt = ds.Tables[0];
return(dt);
}
else
{
return(null);
}
}
/// <summary>
/// 判断在另外一张表中是否有数据
/// </summary>
/// <param name="table">表名</param>
/// <param name="primarykeyname">主键列</param>
/// <param name="primarykey">主键参数</param>
/// <returns></returns>
public int isDelete(string table, string primarykeyname, string primarykey)
{
String cmdText = string.Format(" select count(*) as count from {0} where {1} = '{2}'", table, primarykeyname, primarykey);
string[] param = { };
object[] values = { };
DataSet ds = db.FillDataSet(cmdText, param, values);
if (int.Parse(ds.Tables[0].Rows[0]["count"].ToString()) > 0)
{
return(1);
}
else
{
return(0);
}
}
/// <summary>
/// 通过地区获取客户姓名和ID
/// </summary>
/// <returns></returns>
public DataSet getCustomerByReg(int regionId)
{
MySqlHelp db = new MySqlHelp();
string comText = "select customerName,customerID from T_Customer where regionId=@regionId";
string[] param = { "@regionId" };
string[] values = { regionId.ToString() };
DataSet ds = db.FillDataSet(comText, param, values);
if (ds != null || ds.Tables[0].Rows.Count > 0)
{
return(ds);
}
else
{
return(null);
}
}
/// <summary>
/// 获取所有客户馆藏数据的ISBN，客户id
/// </summary>
/// <returns></returns>
public DataTable Select(string customerId)
{
string comText = "select ISBN,bookName,price,collectionNum,customerId from T_LibraryCollection where customerId=@customerId";
string[] param = { "@customerId" };
string[] values = { customerId.ToString() };
DataSet ds = db.FillDataSet(comText, param, values);
if (ds != null || ds.Tables[0].Rows.Count > 0)
{
DataTable dt = ds.Tables[0];
return(dt);
}
else
{
return(null);
}
}
/// <summary>
/// 通过ISBN和书名获取书号 单价 进货折扣
/// </summary>
/// <param name="ISBN"></param>
/// <param name="bookName"></param>
/// <returns></returns>
public DataTable getBookNum(string ISBN, string bookName)
{
MySqlHelp db = new MySqlHelp();
string comText = "select bookNum,price,author from T_BookBasicData where ISBN=@ISBN and bookName=@bookName ";
string[] parames = { "@ISBN", "@bookName" };
object[] value = { ISBN, bookName };
DataSet ds = db.FillDataSet(comText, parames, value);
if (ds != null || ds.Tables[0].Rows.Count > 0)
{
DataTable dt = ds.Tables[0];
return(dt);
}
else
{
return(null);
}
}
/// <summary>
/// 通过书号 获取单价 进货折扣
/// </summary>
/// <param name="booknum"></param>
/// <returns></returns>
public DataTable getBookNumByNum(string booknum)
{
MySqlHelp db = new MySqlHelp();
string comText = "select bookNum,price,author from T_BookBasicData where bookNum=@bookNum";
string[] parames = { "@bookNum" };
object[] value = { booknum };
DataSet ds = db.FillDataSet(comText, parames, value);
if (ds != null || ds.Tables[0].Rows.Count > 0)
{
DataTable dt = ds.Tables[0];
return(dt);
}
else
{
return(null);
}
}
/// <summary>
/// 获取所有用户信息
/// </summary>
/// <returns></returns>
public DataSet Select()
{
string comText = "select userId,userName,regionName,roleName from V_User";
DataSet ds = db.FillDataSet(comText, null, null);
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 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);
}
}
}
/// <summary>
/// 获取销退单头信息
/// </summary>
/// <returns></returns>
public DataSet Select(string sellOffHeadId)
{
string cmdText = "select sellOffHead,sellOffMonomerId,bookNum,isbn,price,count,totalPrice,realDiscount,realPrice,dateTime,bookName from v_selloffmonomer where sellOffHead=@sellOffHeadId order by dateTime desc";
string[] param = { "@sellOffHeadId" };
object[] values = { sellOffHeadId };
DataSet ds = db.FillDataSet(cmdText, param, values);
return(ds);
}
/// <summary>
/// 获取该销退单头下所有的单据号和制单时间
/// </summary>
/// <returns></returns>
public DataSet getAllTime(int state)
{
string cmdText;
if (state == 2)
{
cmdText = "select retailHeadId,dateTime from T_RetailHead where state=2 ORDER BY retailHeadId desc";
}
else
{
cmdText = "select retailHeadId,dateTime from T_RetailHead where state=0 or state=1 ORDER BY retailHeadId desc";
}
DataSet ds = db.FillDataSet(cmdText, null, null);
if (ds.Tables[0].Rows.Count <= 0 || ds == null)
{
ds = null;
}
return(ds);
}
