/// <summary>
/// 根据给定的一些条件来进行票据的模糊查询
/// </summary>
/// <param name="fromDate"></param>
/// <param name="toDate"></param>
/// <param name="companyCond"></param>
/// <param name="drugsCond"></param>
/// <param name="drugsFromCond"></param>
/// <param name="drugsCategoryCond"></param>
/// <param name="storeHouseCond"></param>
/// <param name="billMaker"></param>
/// <returns></returns>
public List <Bills> SearchBill(DateTime fromDate, DateTime toDate, string companyCond, string drugsCond, string drugsFromCond, string drugsCategoryCond, string storeHouseCond, User billMaker, string billsID, BillsType billsType)
{
using (IDbConnection conn = DAOFactory.Instance.OpenConnection())
{
IBillsDAO dao = DAOFactory.Instance.CreateBillsDAO();
SearchBillsTemplate sbt = new SearchBillsTemplate();
sbt.FromDate = fromDate;
sbt.ToDate = toDate;
sbt.CompanyCond = companyCond;
sbt.BillMaker = billMaker.Name;
List <Bills> billsList = dao.SearchBills(sbt, conn);
for (int i = billsList.Count - 1; i >= 0; i--)
{
Bills bill = billsList[i];
if (!bill.ID.ToString().Contains(billsID))
{
billsList.Remove(bill);
}
if (!billsType.Equals(bill.BillsType))
{
billsList.Remove(bill);
}
}
return(billsList);
}
}
public List <Bills> SearchBills(SearchBillsTemplate temp, 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
and (Mc.CompanyCode like @CompanyCond or Mc.CompanyName like @CompanyCond)
and (Mb.Maker like @Maker)
";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@FromDate", temp.FromDate));
paramList.Add(new SqlParameter("@ToDate", temp.ToDate));
paramList.Add(new SqlParameter("@CompanyCond", string.Format("%{0}%", temp.CompanyCond == null ? "" : temp.CompanyCond)));
paramList.Add(new SqlParameter("@Maker", string.Format("%{0}%", temp.BillMaker == null ? "" : temp.BillMaker)));
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);
}
/// <summary>
/// 根据给定的条件来进行票据的模糊查询
/// </summary>
/// <param name="sbt"></param>
/// <returns></returns>
public List <Bills> SearchBill(SearchBillsTemplate sbt)
{
using (IDbConnection conn = DAOFactory.Instance.OpenConnection())
{
IBillsDAO dao = DAOFactory.Instance.CreateBillsDAO();
return(dao.SearchBills(sbt, conn));
}
}
