public void DeleteGoodsFrom(int id, IDbConnection conn)
{
string sql = "DELETE MD_GoodsFrom WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", id));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
public void DeleteBillType(int id, IDbConnection conn, IDbTransaction trans)
{
string sql = "DELETE MD_BillType WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", id));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void AddResourceIntoUser(int resourceID, int userID, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_User_Resource(User_ID,Resource_ID)
VALUES(@UserID,@ResourceID)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@UserID", userID));
paramList.Add(new SqlParameter("@ResourceID", resourceID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void InsertResource(Resource res, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_Resource(ResourceCode,ResourceName)
VALUES(@ResourceCode,@ResourceName)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ResourceCode", res.Code));
paramList.Add(new SqlParameter("@ResourceName", res.Name));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void InsertStock(Stock stock, IDbConnection conn)
{
string sql = @"
INSERT INTO MD_Stock(Storehouse_ID,Goods_ID,Count)
VALUES(@StorehouseID,@GoodsID,@Count)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@StorehouseID", stock.Storehouse.ID));
paramList.Add(new SqlParameter("@GoodsID", stock.Goods.ID));
paramList.Add(new SqlParameter("@Count", stock.Count));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
public void InsertGoodsCategory(GoodsCategory goodsCategory, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_GoodsCategory(GoodsCategoryCode,GoodsCategoryName,Actived)
VALUES(@GoodsCategoryCode,@GoodsCategoryName,@Actived)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@GoodsCategoryCode", goodsCategory.Code));
paramList.Add(new SqlParameter("@GoodsCategoryName", goodsCategory.Name));
paramList.Add(new SqlParameter("@Actived", goodsCategory.Actived));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void InsertBillType(StockBillType stockBillType, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_BillType(Name,Actived,IsOut)
VALUES(@Name,@Actived,@IsOut)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Name", stockBillType.Name));
paramList.Add(new SqlParameter("@Actived", stockBillType.Actived));
paramList.Add(new SqlParameter("@isOut", stockBillType.IsOutStorehouse));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void InsertGoodsFrom(GoodsFrom goodsFrom, IDbConnection conn)
{
string sql = @"
INSERT INTO MD_GoodsFrom(GoodsFromCode,GoodsFromName,Actived)
VALUES(@GoodsFromCode,@GoodsFromName,@Actived)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@GoodsFromCode", goodsFrom.Code));
paramList.Add(new SqlParameter("@GoodsFromName", goodsFrom.Name));
paramList.Add(new SqlParameter("@Actived", goodsFrom.Actived));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
public void InsertStorehouse(Storehouse storehouse, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_Storehouse(StorehouseCode,StorehouseName,Actived,Remark)
VALUES(@StorehouseCode,@StorehouseName,@Actived,@Remark)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@StorehouseCode", storehouse.Code));
paramList.Add(new SqlParameter("@StorehouseName", storehouse.Name));
paramList.Add(new SqlParameter("@Actived", storehouse.Actived));
paramList.Add(new SqlParameter("@Remark", storehouse.Remark));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void DeleteResource(int resID, IDbConnection conn, IDbTransaction trans)
{
string sql = "DELETE MD_User_Resource WHERE Resource_ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", resID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
sql = "DELETE MD_Resource WHERE ID=@ID";
paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", resID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateResource(Resource res, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
UPDATE MD_Resource SET
ResourceCode=@ResourceCode,ResourceName=@ResourceName
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ResourceCode", res.Code));
paramList.Add(new SqlParameter("@ResourceName", res.Name));
paramList.Add(new SqlParameter("@ID", res.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void InsertUser(User user, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_User(UserCode,UserName,Password,Actived,Remark)
VALUES(@UserCode,@UserName,@Password,@Actived,@Remark)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@UserCode", user.Code));
paramList.Add(new SqlParameter("@UserName", user.Name));
paramList.Add(new SqlParameter("@Password", user.Password));
paramList.Add(new SqlParameter("@Actived", user.Actived));
paramList.Add(new SqlParameter("@Remark", user.Remark));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void InsertEventLog(EventLog eventLog, IDbConnection conn)
{
string sql = @"
INSERT INTO MD_EventLog(Maker,MakeDate,Message,ClientPC,Remark)
VALUES(@Maker,@MakeDate,@Message,@ClientPC,@Remark)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Maker", eventLog.Maker));
paramList.Add(new SqlParameter("@MakeDate", eventLog.MakeDate));
paramList.Add(new SqlParameter("@Message", eventLog.Message));
paramList.Add(new SqlParameter("@ClientPC", eventLog.IP));
paramList.Add(new SqlParameter("@Remark", eventLog.Remark));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
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 void UpdateBillType(StockBillType stockBillType, IDbConnection conn)
{
string sql = @"
UPDATE MD_BillType SET
Name=@Name,Actived=@Actived,IsOut=@IsOut
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Name", stockBillType.Name));
paramList.Add(new SqlParameter("@Actived", stockBillType.Actived));
paramList.Add(new SqlParameter("@isOut", stockBillType.IsOutStorehouse));
paramList.Add(new SqlParameter("@ID", stockBillType.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
public void InsertCompany(Company company, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_Company(CompanyCode,CompanyName,CompanyType,Actived,Remark)
VALUES(@CompanyCode,@CompanyName,@CompanyType,@Actived,@Remark)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@CompanyCode", company.Code));
paramList.Add(new SqlParameter("@CompanyName", company.Name));
paramList.Add(new SqlParameter("@CompanyType", (int)company.CompanyType));
paramList.Add(new SqlParameter("@Actived", company.Actived));
paramList.Add(new SqlParameter("@Remark", company.Remark));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateStock(Stock stock, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
UPDATE MD_Stock SET
Storehouse_ID=@StorehouseID,Goods_ID=@GoodsID,Count=@Count
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@StorehouseID", stock.Storehouse.ID));
paramList.Add(new SqlParameter("@GoodsID", stock.Goods.ID));
paramList.Add(new SqlParameter("@Count", stock.Count));
paramList.Add(new SqlParameter("@ID", stock.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateGoodsFrom(GoodsFrom goodsFrom, IDbConnection conn)
{
string sql = @"
UPDATE MD_GoodsFrom SET
GoodsFromCode=@GoodsFromCode,GoodsFromName=@GoodsFromName,
Actived=@Actived
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@GoodsFromCode", goodsFrom.Code));
paramList.Add(new SqlParameter("@GoodsFromName", goodsFrom.Name));
paramList.Add(new SqlParameter("@Actived", goodsFrom.Actived));
paramList.Add(new SqlParameter("@ID", goodsFrom.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
public void UpdateBillType(StockBillType stockBillType, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
UPDATE MD_BillType SET
StockBillTypeCode=@StockBillTypeCode,StockBillTypeName=@StockBillTypeName,
Actived=@Actived
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@StockBillTypeCode", stockBillType.Code));
paramList.Add(new SqlParameter("@StockBillTypeName", stockBillType.Name));
paramList.Add(new SqlParameter("@Actived", stockBillType.Actived));
paramList.Add(new SqlParameter("@ID", stockBillType.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateGoodsCategory(GoodsCategory goodsCategory, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
UPDATE MD_GoodsCategory SET
GoodsCategoryCode=@GoodsCategoryCode,GoodsCategoryName=@GoodsCategoryName,
Actived=@Actived
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@GoodsCategoryCode", goodsCategory.Code));
paramList.Add(new SqlParameter("@GoodsCategoryName", goodsCategory.Name));
paramList.Add(new SqlParameter("@Actived", goodsCategory.Actived));
paramList.Add(new SqlParameter("@ID", goodsCategory.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateBill(StockBill bill, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
UPDATE MD_Bill SET
Actived=@Actived,IsCancelOut=@IsCancelOut,IsRedBill=@IsRedBill,Remark=@Remark
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Actived", bill.Actived));
paramList.Add(new SqlParameter("@IsCancelOut", bill.IsCancelOut));
paramList.Add(new SqlParameter("@IsRedBill", bill.IsRedBill));
paramList.Add(new SqlParameter("@Remark", bill.Remark));
paramList.Add(new SqlParameter("@ID", bill.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateStorehouse(Storehouse storehouse, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
UPDATE MD_Storehouse SET
StorehouseCode=@StorehouseCode,StorehouseName=@StorehouseName,
Actived=@Actived,Remark=@Remark
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@StorehouseCode", storehouse.Code));
paramList.Add(new SqlParameter("@StorehouseName", storehouse.Name));
paramList.Add(new SqlParameter("@Actived", storehouse.Actived));
paramList.Add(new SqlParameter("@Remark", storehouse.Remark));
paramList.Add(new SqlParameter("@ID", storehouse.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void InsertGoods(Goods goods, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_Goods(GoodsCode,GoodsName,Actived,Remark,GoodsFrom_ID,GoodsCategory_ID,Unit,Standard)
VALUES(@GoodsCode,@GoodsName,@Actived,@Remark,@GoodsFromID,@GoodsCategoryID,@Unit,@Standard)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@GoodsCode", goods.Code));
paramList.Add(new SqlParameter("@GoodsName", goods.Name));
paramList.Add(new SqlParameter("@Actived", goods.Actived));
paramList.Add(new SqlParameter("@Remark", goods.Remark));
paramList.Add(new SqlParameter("@GoodsFromID", goods.From.ID));
paramList.Add(new SqlParameter("@GoodsCategoryID", goods.Category.ID));
paramList.Add(new SqlParameter("@Unit", goods.Unit));
paramList.Add(new SqlParameter("@Standard", goods.Standard));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
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 void UpdateUser(User user, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
UPDATE MD_User SET
UserCode=@UserCode,UserName=@UserName,
Actived=@Actived,Password=@Password,Remark=@Remark
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@UserCode", user.Code));
paramList.Add(new SqlParameter("@UserName", user.Name));
paramList.Add(new SqlParameter("@Password", user.Password));
paramList.Add(new SqlParameter("@Actived", user.Actived));
paramList.Add(new SqlParameter("@Remark", user.Remark));
paramList.Add(new SqlParameter("@ID", user.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateCompany(Company company, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
UPDATE MD_Company SET
CompanyCode=@CompanyCode,CompanyName=@CompanyName,
Actived=@Actived,CompanyType=@CompanyType,Remark=@Remark
WHERE ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@CompanyCode", company.Code));
paramList.Add(new SqlParameter("@CompanyName", company.Name));
paramList.Add(new SqlParameter("@CompanyType", (int)company.CompanyType));
paramList.Add(new SqlParameter("@Actived", company.Actived));
paramList.Add(new SqlParameter("@Remark", company.Remark));
paramList.Add(new SqlParameter("@ID", company.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
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 <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 void InsertStock(Stock stock, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
INSERT INTO MD_Stock(Storehouse_ID,Goods_ID,Count)
VALUES(@StorehouseID,@GoodsID,@Count)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@StorehouseID", stock.Storehouse.ID));
paramList.Add(new SqlParameter("@GoodsID", stock.Goods.ID));
paramList.Add(new SqlParameter("@Count", stock.Count));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
object idObj = DataAccessUtil.ExecuteScalar("SELECT @@IDENTITY", new List <SqlParameter>(), (SqlTransaction)trans);
if (idObj != null)
{
stock.ID = int.Parse(idObj.ToString());
}
}
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));
}