public Company SelectCompany(string code, IDbConnection conn)
{
string sql = @"
SELECT ID,CompanyCode,CompanyName,CompanyType,Actived,Remark
FROM MD_Company
WHERE Code=@Code";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Code", code));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
Company company = null;
while (reader.Read())
{
company = new Company();
company.ID = reader.GetInt32(0);
company.Code = reader.GetString(1);
company.Name = reader.GetString(2);
company.CompanyType = (CompanyType)reader.GetInt32(3);
company.Actived = reader.GetBoolean(4);
if (!reader.IsDBNull(5))
{
company.Remark = reader.GetString(5);
}
}
reader.Close();
return(company);
}
public List <EventLog> SelectEventLogs(DateTime fromDate, DateTime toDate, IDbConnection conn)
{
string sql = @"
SELECT ID,Maker,MakeDate,Message,ClientPC,Remark
FROM MD_EventLog
WHERE MakeDate BETWEEN @FromDate AND @ToDate";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@FromDate", fromDate));
paramList.Add(new SqlParameter("@ToDate", toDate));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
List <EventLog> list = new List <EventLog>();
while (reader.Read())
{
EventLog log = new EventLog();
log.ID = reader.GetInt32(0);
log.Maker = reader.GetString(1);
log.MakeDate = reader.GetDateTime(2);
log.Message = reader.GetString(3);
if (!reader.IsDBNull(4))
{
log.IP = reader.GetString(4);
}
if (!reader.IsDBNull(5))
{
log.Remark = reader.GetString(5);
}
list.Add(log);
}
reader.Close();
return(list);
}
public List <Company> SearchCompanies(string codeCond, string nameCond, IDbConnection conn)
{
string sql = @"
SELECT ID,CompanyCode,CompanyName,CompanyType,Actived,Remark
FROM MD_Company
WHERE (CompanyCode LIKE @CodeCond)AND(CompanyName LIKE @NameCond)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@CodeCond", string.Format("'%{0}%'", codeCond)));
paramList.Add(new SqlParameter("@NameCond", string.Format("'%{0}%'", nameCond)));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, new List <SqlParameter>(), (SqlConnection)conn);
List <Company> list = new List <Company>();
while (reader.Read())
{
Company company = new Company();
company.ID = reader.GetInt32(0);
company.Code = reader.GetString(1);
company.Name = reader.GetString(2);
company.CompanyType = (CompanyType)reader.GetInt32(3);
company.Actived = reader.GetBoolean(4);
if (!reader.IsDBNull(5))
{
company.Remark = reader.GetString(5);
}
list.Add(company);
}
reader.Close();
return(list);
}
public Storehouse SelectStorehouse(string code, IDbConnection conn)
{
string sql = @"
SELECT ID,StorehouseCode,StorehouseName,Actived,Remark
FROM MD_Storehouse
WHERE StorehouseCode=@Code";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Code", code));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
Storehouse result = null;
while (reader.Read())
{
result = new Storehouse();
result.ID = reader.GetInt32(0);
result.Code = reader.GetString(1);
result.Name = reader.GetString(2);
result.Actived = reader.GetBoolean(3);
if (!reader.IsDBNull(4))
{
result.Remark = reader.GetString(4);
}
}
reader.Close();
return(result);
}
public Stock SelectStockByGoodsAndStorehouse(int goodsID, int storehouseID, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
SELECT
Ms.ID, Ms.Count,
Msh.ID,Msh.StorehouseCode,Msh.StorehouseName,Msh.Actived,Msh.Remark,
Mg.ID,Mg.GoodsCode,Mg.GoodsName,Mg.Actived,Mg.Remark,Mg.Unit,Mg.Standard,
Mgf.ID,Mgf.GoodsFromCode,Mgf.GoodsFromName,Mgf.Actived,
Mgc.ID,Mgc.GoodsCategoryCode,Mgc.GoodsCategoryName,Mgc.Actived
FROM MD_Stock AS Ms
INNER JOIN MD_Storehouse AS Msh ON Ms.Storehouse_ID=Msh.ID
INNER JOIN MD_Goods AS Mg ON Ms.Goods_ID=Mg.ID
INNER JOIN MD_GoodsFrom AS Mgf ON Mg.GoodsFrom_ID=Mgf.ID
INNER JOIN MD_GoodsCategory AS Mgc ON Mg.GoodsCategory_ID=Mgc.ID
WHERE Ms.Goods_ID=@GoodsID AND Ms.Storehouse_ID=@StorehouseID
";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@GoodsID", goodsID));
paramList.Add(new SqlParameter("@StorehouseID", storehouseID));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlTransaction)trans);
List <Stock> list = this.SelectStocks(sql, paramList, reader);
if (list.Count == 0)
{
return(null);
}
return(list[0]);
}
public Goods SelectGoods(string code, IDbConnection conn)
{
string sql = @"
SELECT
Mg.ID,Mg.GoodsCode,Mg.GoodsName,Mg.Actived,Mg.Remark,
Mgf.ID,Mgf.GoodsFromCode,Mgf.GoodsFromName,Mgf.Actived,
Mgc.ID,Mgc.GoodsCategoryCode,Mgc.GoodsCategoryName,Mgc.Actived,
Mg.Unit,Mg.Standard
FROM MD_Goods AS Mg
INNER JOIN MD_GoodsCategory AS Mgc ON Mg.GoodsCategory_ID=Mgc.ID
INNER JOIN MD_GoodsFrom AS Mgf ON Mg.GoodsFrom_ID=Mgf.ID
WHERE Mg.GoodsCode=@Code
";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Code", code));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
Goods g = null;
while (reader.Read())
{
g = new Goods();
g.ID = reader.GetInt32(0);
g.Code = reader.GetString(1);
g.Name = reader.GetString(2);
g.Actived = reader.GetBoolean(3);
if (!reader.IsDBNull(4))
{
g.Remark = reader.GetString(4);
}
g.From = new GoodsFrom();
g.From.ID = reader.GetInt32(5);
g.From.Code = reader.GetString(6);
g.From.Name = reader.GetString(7);
g.From.Actived = reader.GetBoolean(8);
g.Category = new GoodsCategory();
g.Category.ID = reader.GetInt32(9);
g.Category.Code = reader.GetString(10);
g.Category.Name = reader.GetString(11);
g.Category.Actived = reader.GetBoolean(12);
g.Unit = reader.GetString(13);
g.Standard = reader.GetString(14);
}
reader.Close();
return(g);
}
public List <Resource> GetAllResources(IDbConnection conn)
{
string sql = "SELECT ID,ResourceCode,ResourceName FROM MD_Resource";
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, new List <SqlParameter>(), (SqlConnection)conn);
List <Resource> resList = new List <Resource>();
while (reader.Read())
{
Resource res = new Resource();
res.ID = reader.GetInt32(0);
res.Code = reader.GetString(1);
res.Name = reader.GetString(2);
resList.Add(res);
}
reader.Close();
return(resList);
}
public List <GoodsCategory> SelectAllGoodsCategories(IDbConnection conn)
{
string sql = "SELECT ID,GoodsCategoryCode,GoodsCategoryName,Actived FROM MD_GoodsCategory";
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, new List <SqlParameter>(), (SqlConnection)conn);
List <GoodsCategory> list = new List <GoodsCategory>();
while (reader.Read())
{
GoodsCategory sh = new GoodsCategory();
sh.ID = reader.GetInt32(0);
sh.Code = reader.GetString(1);
sh.Name = reader.GetString(2);
sh.Actived = reader.GetBoolean(3);
list.Add(sh);
}
reader.Close();
return(list);
}
public List <StockBillType> SelectAllBillTypes(IDbConnection conn)
{
string sql = "SELECT ID,Name,Actived,IsOut FROM MD_BillType";
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, new List <SqlParameter>(), (SqlConnection)conn);
List <StockBillType> list = new List <StockBillType>();
while (reader.Read())
{
StockBillType sh = new StockBillType();
sh.ID = reader.GetInt32(0);
sh.Name = reader.GetString(1);
sh.Actived = reader.GetBoolean(2);
sh.IsOutStorehouse = reader.GetBoolean(3);
list.Add(sh);
}
reader.Close();
return(list);
}
public List <Stock> SelectAllStocks(IDbConnection conn)
{
string sql = @"
SELECT
Ms.ID, Ms.Count,
Msh.ID,Msh.StorehouseCode,Msh.StorehouseName,Msh.Actived,Msh.Remark,
Mg.ID,Mg.GoodsCode,Mg.GoodsName,Mg.Actived,Mg.Remark,Mg.Unit,Mg.Standard,
Mgf.ID,Mgf.GoodsFromCode,Mgf.GoodsFromName,Mgf.Actived,
Mgc.ID,Mgc.GoodsCategoryCode,Mgc.GoodsCategoryName,Mgc.Actived
FROM MD_Stock AS Ms
INNER JOIN MD_Storehouse AS Msh ON Ms.Storehouse_ID=Msh.ID
INNER JOIN MD_Goods AS Mg ON Ms.Goods_ID=Mg.ID
INNER JOIN MD_GoodsFrom AS Mgf ON Mg.GoodsFrom_ID=Mgf.ID
INNER JOIN MD_GoodsCategory AS Mgc ON Mg.GoodsCategory_ID=Mgc.ID
";
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, new List <SqlParameter>(), (SqlConnection)conn);
return(this.SelectStocks(sql, new List <SqlParameter>(), reader));
}
public List <Storehouse> SelectAllStorehouses(IDbConnection conn)
{
string sql = "SELECT ID,StorehouseCode,StorehouseName,Actived,Remark FROM MD_Storehouse";
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, new List <SqlParameter>(), (SqlConnection)conn);
List <Storehouse> list = new List <Storehouse>();
while (reader.Read())
{
Storehouse sh = new Storehouse();
sh.ID = reader.GetInt32(0);
sh.Code = reader.GetString(1);
sh.Name = reader.GetString(2);
sh.Actived = reader.GetBoolean(3);
if (!reader.IsDBNull(4))
{
sh.Remark = reader.GetString(4);
}
list.Add(sh);
}
reader.Close();
return(list);
}
public List <User> GetAllUsers(IDbConnection conn)
{
string sql = "SELECT ID,UserCode,UserName,Password,Actived,Remark FROM MD_User";
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, new List <SqlParameter>(), (SqlConnection)conn);
List <User> userList = new List <User>();
while (reader.Read())
{
User user = new User();
user.ID = reader.GetInt32(0);
user.Code = reader.GetString(1);
user.Name = reader.GetString(2);
user.Password = reader.GetString(3);
user.Actived = reader.GetBoolean(4);
if (!reader.IsDBNull(5))
{
user.Remark = reader.GetString(5);
}
userList.Add(user);
}
reader.Close();
return(userList);
}
public GoodsFrom SelectGoodsFrom(int id, IDbConnection conn)
{
string sql = @"
SELECT ID,GoodsFromCode,GoodsFromName,Actived,Remark
FROM MD_GoodsFrom
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", id));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
GoodsFrom result = null;
while (reader.Read())
{
result = new GoodsFrom();
result.ID = reader.GetInt32(0);
result.Code = reader.GetString(1);
result.Name = reader.GetString(2);
result.Actived = reader.GetBoolean(3);
}
reader.Close();
return(result);
}
public List <Company> SelectAllCompanies(IDbConnection conn)
{
string sql = "SELECT ID,CompanyCode,CompanyName,CompanyType,Actived,Remark FROM MD_Company";
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, new List <SqlParameter>(), (SqlConnection)conn);
List <Company> list = new List <Company>();
while (reader.Read())
{
Company company = new Company();
company.ID = reader.GetInt32(0);
company.Code = reader.GetString(1);
company.Name = reader.GetString(2);
company.CompanyType = (CompanyType)reader.GetInt32(3);
company.Actived = reader.GetBoolean(4);
if (!reader.IsDBNull(5))
{
company.Remark = reader.GetString(5);
}
list.Add(company);
}
reader.Close();
return(list);
}
public GoodsCategory SelectGoodsCategory(string code, IDbConnection conn)
{
string sql = @"
SELECT ID,GoodsCategoryCode,GoodsCategoryName,Actived,Remark
FROM MD_GoodsCategory
WHERE Code=@Code";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Code", code));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
GoodsCategory result = null;
while (reader.Read())
{
result = new GoodsCategory();
result.ID = reader.GetInt32(0);
result.Code = reader.GetString(1);
result.Name = reader.GetString(2);
result.Actived = reader.GetBoolean(3);
}
reader.Close();
return(result);
}
public List <Resource> GetResources(int userID, IDbConnection conn)
{
string sql = @"
SELECT ID,ResourceCode,ResourceName
FROM MD_Resource AS Mr
INNER JOIN MD_User_Resource AS Mur ON Mr.ID=Mur.Resource_ID
WHERE Mur.User_ID=@UserID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@UserID", userID));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
List <Resource> resList = new List <Resource>();
while (reader.Read())
{
Resource res = new Resource();
res.ID = reader.GetInt32(0);
res.Code = reader.GetString(1);
res.Name = reader.GetString(2);
resList.Add(res);
}
reader.Close();
return(resList);
}
public List <StockBill> SearchBills(DateTime fromDate, DateTime toDate,
string companyCond, string makerCond,
IDbConnection conn)
{
string sql = @"
SELECT
Mb.ID,Mb.Maker,Mb.MakeDate,Mb.CreateDate,Mb.Actived,Mb.IsCancelOut,Mb.IsRedBill,Mb.Remark,
Mbt.ID,Mbt.Name,Mbt.Actived,Mbt.IsOut,
Ms.ID,Ms.StorehouseCode,Ms.StorehouseName,Ms.Actived,Ms.Remark,
Mc.ID,Mc.CompanyCode,Mc.CompanyName,Mc.CompanyType,Mc.Actived,Mc.Remark
FROM MD_Bill AS Mb
INNER JOIN MD_BillType AS Mbt ON Mb.BillType_ID=Mbt.ID
INNER JOIN MD_Storehouse AS Ms ON Mb.Storehouse_ID=Ms.ID
INNER JOIN MD_Company AS Mc ON Mb.Company_ID=Mc.ID
WHERE (Mb.MakeDate BETWEEN @FromDate AND @ToDate)
AND(Mc.CompanyCode LIKE @CompanyCond OR Mc.CompanyName LIKE @CompanyCond)
AND(Mb.Maker LIKE @MakerCond)
";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@FromDate", fromDate));
paramList.Add(new SqlParameter("@ToDate", toDate));
paramList.Add(new SqlParameter("@CompanyCond", "%" + companyCond == null ? "" : companyCond + "%"));
paramList.Add(new SqlParameter("@MakerCond", "%" + makerCond == null ? "" : makerCond + "%"));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
List <StockBill> billList = new List <StockBill>();
while (reader.Read())
{
StockBill bill = new StockBill();
bill.ID = reader.GetInt32(0);
bill.Maker = reader.GetString(1);
bill.MakeDate = reader.GetDateTime(2);
bill.CreateDate = reader.GetDateTime(3);
bill.Actived = reader.GetBoolean(4);
bill.IsCancelOut = reader.GetBoolean(5);
bill.IsRedBill = reader.GetBoolean(6);
if (!reader.IsDBNull(7))
{
bill.Remark = reader.GetString(7);
}
bill.BillType = new StockBillType();
bill.BillType.ID = reader.GetInt32(8);
bill.BillType.Name = reader.GetString(9);
bill.BillType.Actived = reader.GetBoolean(10);
bill.BillType.IsOutStorehouse = reader.GetBoolean(11);
// Ms.ID,Ms.StorehouseCode,Ms.StorehouseName,Ms.Actived,Ms.Remark,
bill.Storehouse = new Storehouse();
bill.Storehouse.ID = reader.GetInt32(12);
bill.Storehouse.Code = reader.GetString(13);
bill.Storehouse.Name = reader.GetString(14);
bill.Storehouse.Actived = reader.GetBoolean(15);
if (!reader.IsDBNull(16))
{
bill.Storehouse.Remark = reader.GetString(16);
}
//Mc.ID,Mc.CompanyCode,Mc.CompanyName,Mc.CompanyType,Mc.Actived,Mc.Remark
bill.Company = new Company();
bill.Company.ID = reader.GetInt32(17);
bill.Company.Code = reader.GetString(18);
bill.Company.Name = reader.GetString(19);
bill.Company.CompanyType = (CompanyType)reader.GetInt32(20);
bill.Company.Actived = reader.GetBoolean(21);
if (!reader.IsDBNull(22))
{
bill.Company.Remark = reader.GetString(22);
}
billList.Add(bill);
}
reader.Close();
return(billList);
}
public StockBill SelectBill(int id, IDbConnection conn)
{
string sql = @"
SELECT
Mb.ID,Mb.Maker,Mb.MakeDate,Mb.CreateDate,Mb.Actived,Mb.IsCancelOut,Mb.IsRedBill,Mb.Remark,
Mbt.ID,Mbt.Name,Mbt.Actived,Mbt.IsOut,
Ms.ID,Ms.StorehouseCode,Ms.StorehouseName,Ms.Actived,Ms.Remark,
Mc.ID,Mc.CompanyCode,Mc.CompanyName,Mc.CompanyType,Mc.Actived,Mc.Remark
FROM MD_Bill AS Mb
INNER JOIN MD_BillType AS Mbt ON Mb.BillType_ID=Mbt.ID
INNER JOIN MD_Storehouse AS Ms ON Mb.Storehouse_ID=Ms.ID
INNER JOIN MD_Company AS Mc ON Mb.Company_ID=Mc.ID
WHERE Mb.ID=@ID
";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", id));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
StockBill bill = null;
while (reader.Read())
{
bill = new StockBill();
bill.ID = reader.GetInt32(0);
bill.Maker = reader.GetString(1);
bill.MakeDate = reader.GetDateTime(2);
bill.CreateDate = reader.GetDateTime(3);
bill.Actived = reader.GetBoolean(4);
bill.IsCancelOut = reader.GetBoolean(5);
bill.IsRedBill = reader.GetBoolean(6);
if (!reader.IsDBNull(7))
{
bill.Remark = reader.GetString(7);
}
bill.BillType = new StockBillType();
bill.BillType.ID = reader.GetInt32(8);
bill.BillType.Name = reader.GetString(9);
bill.BillType.Actived = reader.GetBoolean(10);
bill.BillType.IsOutStorehouse = reader.GetBoolean(11);
// Ms.ID,Ms.StorehouseCode,Ms.StorehouseName,Ms.Actived,Ms.Remark,
bill.Storehouse = new Storehouse();
bill.Storehouse.ID = reader.GetInt32(12);
bill.Storehouse.Code = reader.GetString(13);
bill.Storehouse.Name = reader.GetString(14);
bill.Storehouse.Actived = reader.GetBoolean(15);
if (!reader.IsDBNull(16))
{
bill.Storehouse.Remark = reader.GetString(16);
}
//Mc.ID,Mc.CompanyCode,Mc.CompanyName,Mc.CompanyType,Mc.Actived,Mc.Remark
bill.Company = new Company();
bill.Company.ID = reader.GetInt32(17);
bill.Company.Code = reader.GetString(18);
bill.Company.Name = reader.GetString(19);
bill.Company.CompanyType = (CompanyType)reader.GetInt32(20);
bill.Company.Actived = reader.GetBoolean(21);
if (!reader.IsDBNull(22))
{
bill.Company.Remark = reader.GetString(22);
}
}
reader.Close();
if (bill != null)
{
sql = @"
SELECT
Mbi.ID,Mbi.UnitPrice,Mbi.Count,Mbi.Remark,
Mg.ID,Mg.GoodsCode,Mg.GoodsName,Mg.Actived,Mg.Remark,
Mgf.ID,Mgf.GoodsFromCode,Mgf.GoodsFromName,Mgf.Actived,
Mgc.ID,Mgc.GoodsCategoryCode,Mgc.GoodsCategoryName,Mgc.Actived
FROM MD_BillItem AS Mbi
INNER JOIN MD_Goods AS Mg ON Mbi.Goods_ID=Mg.ID
INNER JOIN MD_GoodsCategory AS Mgc ON Mg.GoodsCategory_ID=Mgc.ID
INNER JOIN MD_GoodsFrom AS Mgf ON Mg.GoodsFrom_ID=Mgf.ID
WHERE Mbi.Bill_ID=@ID
";
paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", id));
reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
while (reader.Read())
{
StockBillItem item = new StockBillItem();
item.UnitPrice = reader.GetDecimal(1);
item.Count = reader.GetInt32(2);
item.Goods = new Goods();
item.Goods.ID = reader.GetInt32(4);
item.Goods.Code = reader.GetString(5);
item.Goods.Name = reader.GetString(6);
item.Goods.Actived = reader.GetBoolean(7);
if (!reader.IsDBNull(8))
{
item.Goods.Remark = reader.GetString(8);
}
item.Goods.From = new GoodsFrom();
item.Goods.From.ID = reader.GetInt32(9);
item.Goods.From.Code = reader.GetString(10);
item.Goods.From.Name = reader.GetString(11);
item.Goods.From.Actived = reader.GetBoolean(12);
item.Goods.Category = new GoodsCategory();
item.Goods.Category.ID = reader.GetInt32(13);
item.Goods.Category.Code = reader.GetString(14);
item.Goods.Category.Name = reader.GetString(15);
item.Goods.Category.Actived = reader.GetBoolean(16);
bill.Items.Add(item);
}
reader.Close();
}
return(bill);
}