public void DeleteCompanyType(int id, IDbConnection conn, IDbTransaction trans)
{
string sql = @"Delete MD_CompanyType where ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", id));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void DeleteBill(int id, IDbConnection conn)
{
string sql = @"Delete MD_Bills where ID=@ID ";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", id));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
public void AddResourceIntoUser(int ResourceID, int UserID, IDbConnection conn, IDbTransaction trans)
{
string sql = @"insert into MD_User_Resource(User_ID,Resource_ID) values(@User_ID,@Resource_ID)";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Resource_ID", ResourceID));
paramList.Add(new SqlParameter("@User_ID", UserID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public List <Bills> SelectBills(DateTime fromDate, DateTime toDate, IDbConnection conn)
{
string sql = @"
select
Mb.ID,Mb.BillsCode,Mb.Maker,Mb.MakerDate,Mb.CreateDate,Mb.Actived,Mb.IsCancleOut,Mb.IsRedBill,Mb.Remark,
Msh.ID,Msh.StorehouseCode,Msh.StorehouseName,Msh.Actived,Msh.Remark,
Mbt.ID,Mbt.Name,Mbt.IsOut,Mbt.Actived,Mbt.Remark,
Mc.ID,Mc.CompanyCode,Mc.CompanyName,Mc.Actived,Mc.Remark,
Mct.ID,Mct.CompanyTypeCode,Mct.CompanyTypeName,Mct.Actived,Mct.Remark
from MD_Bills as Mb
inner join MD_Storehouse as Msh on Mb.Storehouse_ID=Msh.ID
inner join MD_BillsType as Mbt on Mb.BillsType_ID=Mbt.ID
inner join MD_Company as Mc on Mb.Company_ID=Mc.ID
inner join MD_CompanyType as Mct on Mc.CompanyType_ID=Mct.ID
where Mb.MakerDate 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 <Bills> list = new List <Bills>();
while (reader.Read())
{
Bills bill = new Bills();
BillModelHelper(reader, bill);
list.Add(bill);
}
reader.Close();
foreach (Bills b in list)
{
sql = @"
Select Mbi.ID,Mbi.Count,Mbi.UnitPrice,Mbi.Money,Mbi.Remark,
Md.ID,Md.DrugCode,Md.DrugName,Md.UnitPrice,Md.Standard,Md.Unit,Md.Actived,Md.Remark,
Mdf.ID,Mdf.DrugFromCode,Mdf.DrugFromName,Mdf.Actived,Mdf.Remark,
Mdc.ID,Mdc.DrugCategoryCode,Mdc.DrugCategoryName,Mdc.Actived,Mdc.Remark
from MD_BillsItem as Mbi
inner join MD_Drugs as Md on Mbi.Drugs_ID=Md.ID
inner join MD_DrugCategory as Mdc on Md.DrugCategory_ID=Mdc.ID
inner join MD_DrugFrom as Mdf on Md.DrugFrom_ID=Mdf.ID
where Mbi.Bills_ID=@ID ";
paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", b.ID));
reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
while (reader.Read())
{
BillsItem item = new BillsItem();
BillItemModelHelper(reader, item);
b.Items.Add(item);
}
reader.Close();
}
return(list);
}
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, IDbTransaction trans)
{
string sql = @"
Insert into MD_Stock(Storehouse_ID,Drugs_ID,Count)
values(@Storehouse_ID,@Drugs_ID,@Count) ";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Storehouse_ID", stock.Storehouse.ID));
paramList.Add(new SqlParameter("@Drugs_ID", stock.Drug.ID));
paramList.Add(new SqlParameter("@Count", stock.Count));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void InsertStorehouse(Storehouse house, 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", house.Code));
paramList.Add(new SqlParameter("@StorehouseName", house.Name));
paramList.Add(new SqlParameter("@Actived", house.Actived));
paramList.Add(new SqlParameter("@Remark", house.Remark));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void InsertDrugCategory(DrugCategory category, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
Insert into MD_DrugCategory(DrugCategoryCode,DrugCategoryName,Actived,Remark)
values(@DrugCategoryCode,@DrugCategoryName,@Actived,@Remark) ";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@DrugCategoryCode", category.Code));
paramList.Add(new SqlParameter("@DrugCategoryName", category.Name));
paramList.Add(new SqlParameter("@Actived", category.Actived));
paramList.Add(new SqlParameter("@Remark", category.Remark));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
private List <Stock> SelectStocks(string sql, List <SqlParameter> paramList, SqlConnection conn)
{
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
List <Stock> stockList = new List <Stock>();
while (reader.Read())
{
Stock stock = this.ModelHelper(reader);
stockList.Add(stock);
}
reader.Close();
return(stockList);
}
public void InsertDrugFrom(DrugFrom from, IDbConnection conn)
{
string sql = @"
Insert into MD_DrugFrom(DrugFromCode,DrugFromName,Actived,Remark)
values(@DrugFromCode,@DrugFromName,@Actived,@Remark) ";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@DrugFromCode", from.Code));
paramList.Add(new SqlParameter("@DrugFromName", from.Name));
paramList.Add(new SqlParameter("@Actived", from.Actived));
paramList.Add(new SqlParameter("@Remark", from.Remark));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
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 InsertCompanyType(CompanyType type, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
Insert into MD_CompanyType(CompanyTypeCode,CompanyTypeName,Actived,Remark)
values(@CompanyTypeCode,@CompanyTypeName,@Actived,@Remark) ";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@CompanyTypeCode", type.Code));
paramList.Add(new SqlParameter("@CompanyTypeName", type.Name));
paramList.Add(new SqlParameter("@Actived", type.Actived));
paramList.Add(new SqlParameter("@Remark", type.Remark));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
private List <Drugs> SelectDrugs(string sql, List <SqlParameter> paramList, SqlConnection conn)
{
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
List <Drugs> drugList = new List <Drugs>();
while (reader.Read())
{
Drugs drug = this.ModelHelper(reader);
drugList.Add(drug);
}
reader.Close();
return(drugList);
}
public void UpdateStock(Stock stock, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
Update MD_Stock set
Storehouse_ID=@Storehouse_ID,Drugs_ID=@Drugs_ID,Count=@Count
where ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Storehouse_ID", stock.Storehouse.ID));
paramList.Add(new SqlParameter("@Drugs_ID", stock.Drug.ID));
paramList.Add(new SqlParameter("@Count", stock.Count));
paramList.Add(new SqlParameter("@ID", stock.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void InsertEventLog(EventLog eventLog, IDbConnection conn)
{
string sql = @"insert into
MD_EventLog(Maker,MakeDate,ClientPC,Message,Remark)
values(@Maker,@MakeDate,@ClientPC,@Message,@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("@ClientPC", eventLog.ClientPC));
paramList.Add(new SqlParameter("@Message", eventLog.Message));
paramList.Add(new SqlParameter("@Remark", eventLog.Remark));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
public void InsertBillsType(BillsType type, IDbConnection conn)
{
string sql = @"
Insert into MD_BillsType(Code,Name,IsOut,Actived,Remark)
values(@Code,@Name,@IsOut,@Actived,@Remark) ";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Code", type.Code));
paramList.Add(new SqlParameter("@Name", type.Name));
paramList.Add(new SqlParameter("@IsOut", type.IsOut));
paramList.Add(new SqlParameter("@Actived", type.Actived));
paramList.Add(new SqlParameter("@Remark", type.Remark));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
public void InsertCompany(Company company, IDbConnection conn)
{
string sql = @"
Insert into MD_Company(CompanyCode,CompanyName,CompanyType_ID,Actived,Remark)
values(@CompanyCode,@CompanyName,@CompanyType_ID,@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_ID", company.CompanyType.ID));
paramList.Add(new SqlParameter("@Actived", company.Actived));
paramList.Add(new SqlParameter("@Remark", company.Remark));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
public void UpdateCompanyType(CompanyType type, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
Update MD_CompanyType set
CompanyTypeCode=@CompanyTypeCode,CompanyTypeName=@CompanyTypeName,Actived=@Actived,Remark=@Remark
where ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@CompanyTypeCode", type.Code));
paramList.Add(new SqlParameter("@CompanyTypeName", type.Name));
paramList.Add(new SqlParameter("@Actived", type.Actived));
paramList.Add(new SqlParameter("@Remark", type.Remark));
paramList.Add(new SqlParameter("@ID", type.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateDrugCategory(DrugCategory category, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
Update MD_DrugCategory set
DrugCategoryCode=@DrugCategoryCode,DrugCategoryName=@DrugCategoryName,Actived=@Actived,Remark=@Remark
where ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@DrugCategoryCode", category.Code));
paramList.Add(new SqlParameter("@DrugCategoryName", category.Name));
paramList.Add(new SqlParameter("@Actived", category.Actived));
paramList.Add(new SqlParameter("@Remark", category.Remark));
paramList.Add(new SqlParameter("@ID", category.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateDrugFrom(DrugFrom from, IDbConnection conn)
{
string sql = @"
Update MD_DrugFrom set
DrugFromCode=@DrugFromCode,DrugFromName=@DrugFromName,Actived=@Actived,Remark=@Remark
where ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@DrugFromCode", from.Code));
paramList.Add(new SqlParameter("@DrugFromName", from.Name));
paramList.Add(new SqlParameter("@Actived", from.Actived));
paramList.Add(new SqlParameter("@Remark", from.Remark));
paramList.Add(new SqlParameter("@ID", from.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
public void InsertUser(User user, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
Insert into MD_User(UserCode,UserName,Password,IsAdmin,Actived,Remark)
values(@UserCode,@UserName,@Password,@IsAdmin,@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("@IsAdmin", user.IsAdmin));
paramList.Add(new SqlParameter("@Actived", user.Actived));
paramList.Add(new SqlParameter("@Remark", user.Remark));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateStorehouse(Storehouse house, 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", house.Code));
paramList.Add(new SqlParameter("@StorehouseName", house.Name));
paramList.Add(new SqlParameter("@Actived", house.Actived));
paramList.Add(new SqlParameter("@Remark", house.Remark));
paramList.Add(new SqlParameter("@ID", house.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateCompany(Company company, IDbConnection conn)
{
string sql = @"
Update MD_Company set
CompanyCode=@CompanyCode,CompanyName=@CompanyName,CompanyType_ID=@CompanyType_ID, Actived=@Actived,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_ID", company.CompanyType.ID));
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, (SqlConnection)conn);
}
public void UpdateBillsType(BillsType type, IDbConnection conn)
{
string sql = @"
Update MD_BillsType set
Code=@Code,Name=@Name,IsOut=@IsOut,Actived=@Actived,Remark=@Remark
where ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Code", type.Code));
paramList.Add(new SqlParameter("@Name", type.Name));
paramList.Add(new SqlParameter("@IsOut", type.IsOut));
paramList.Add(new SqlParameter("@Actived", type.Actived));
paramList.Add(new SqlParameter("@Remark", type.Remark));
paramList.Add(new SqlParameter("@ID", type.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlConnection)conn);
}
public void UpdateBill(Bills bill, IDbConnection conn)
{
string sql = @"Update MD_Bills
set BillsCode=@BillsCode,Storehouse_ID=@Storehouse_ID,Actived=@Actived,IsCancleOut=@IsCancleOut,IsRedBill=@IsRedBill,Remark=@Remark
where ID=@ID
";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@BillsCode", bill.Code));
paramList.Add(new SqlParameter("Storehouse_ID", bill.Storehouse.ID));
paramList.Add(new SqlParameter("@Actived", bill.Actived));
paramList.Add(new SqlParameter("@IsCancleOut", 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, (SqlConnection)conn);
}
public List <Resource> SelectAllResource(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,Password=@Password,IsAdmin=@IsAdmin,Actived=@Actived,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("@IsAdmin", user.IsAdmin));
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 InsertDrug(Drugs drug, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
Insert into MD_Drugs(DrugCode,DrugName,UnitPrice,Standard,Unit,DrugFrom_ID,DrugCategory_ID,Actived,Remark)
values(@DrugsCode,@DrugsName,@UnitPrice,@Standard,@Unit,@DrugFrom_ID,@DrugCategory_ID,@Actived,@Remark) ";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@DrugsCode", drug.Code));
paramList.Add(new SqlParameter("@DrugsName", drug.Name));
paramList.Add(new SqlParameter("@UnitPrice", drug.UnitPrice));
paramList.Add(new SqlParameter("@Standard", drug.Standard));
paramList.Add(new SqlParameter("@Unit", drug.Unit));
paramList.Add(new SqlParameter("@DrugFrom_ID", drug.From.ID));
paramList.Add(new SqlParameter("@DrugCategory_ID", drug.Category.ID));
paramList.Add(new SqlParameter("@Actived", drug.Actived));
paramList.Add(new SqlParameter("@Remark", drug.Remark));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public void UpdateDrug(Drugs drug, IDbConnection conn, IDbTransaction trans)
{
string sql = @"
Update MD_Drugs set
DrugCode=@DrugCode,DrugName=@DrugName,UnitPrice=@UnitPrice,Standard=@Standard,Unit=@Unit,DrugFrom_ID=@DrugFrom_ID,DrugCategory_ID=@DrugCategory_ID,Actived=@Actived,Remark=@Remark
where ID=@ID";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@DrugCode", drug.Code));
paramList.Add(new SqlParameter("@DrugName", drug.Name));
paramList.Add(new SqlParameter("@UnitPrice", drug.UnitPrice));
paramList.Add(new SqlParameter("@Standard", drug.Standard));
paramList.Add(new SqlParameter("@Unit", drug.Actived));
paramList.Add(new SqlParameter("@DrugFrom_ID", drug.From.ID));
paramList.Add(new SqlParameter("@DrugCategory_ID", drug.Category.ID));
paramList.Add(new SqlParameter("@Actived", drug.Actived));
paramList.Add(new SqlParameter("@Remark", drug.Remark));
paramList.Add(new SqlParameter("@ID", drug.ID));
DataAccessUtil.ExecuteNonQuery(sql, paramList, (SqlTransaction)trans);
}
public List <DrugCategory> SelectAllDrugCategory(IDbConnection conn)
{
string sql = @"select ID,DrugCategoryCode,DrugCategoryName,Actived,Remark from MD_DrugCategory";
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, new List <SqlParameter>(), (SqlConnection)conn);
List <DrugCategory> categoryList = new List <DrugCategory>();
while (reader.Read())
{
DrugCategory category = new DrugCategory();
category.ID = reader.GetInt32(0);
category.Code = reader.GetString(1);
category.Name = reader.GetString(2);
category.Actived = reader.GetBoolean(3);
if (!reader.IsDBNull(4))
{
category.Remark = reader.GetString(4);
}
categoryList.Add(category);
}
reader.Close();
return(categoryList);
}