/// <summary>
/// 获取指定厂家的中药饮片分类信息
/// </summary>
/// <param name="drugsBase_Manufacturer">厂家名称</param>
/// <returns></returns>
private List <DrugsPharm> GetZYCPharmInfo(string drugsBase_Manufacturer)
{
List <DrugsPharm> rtnObjs = null;
try
{
string sql = string.Format(@"SELECT DISTINCT t1.*, t2.DrugNumber FROM
(SELECT
a.DrugsBase_Manufacturer AS DrugsBase_Manufacturer,
b.ProductionClassId AS Pharm_ID,
c.ProductionClassName AS Pharm_Name
FROM
dbo.product_online_v a,
dbo.DrugsBase_ZYC b ,
dbo.DrugsBase_ZYC_ProductionClass c
WHERE
a.DrugsBase_Manufacturer = '{0}' AND
b.DrugsBase_ID = a.DrugsBase_ID AND
b.ProductionClassId = c.ProductionClassId) AS t1,
(SELECT
b.ProductionClassId AS pharmID,
count(b.ProductionClassId) AS DrugNumber
FROM
dbo.product_online_v a,
dbo.DrugsBase_ZYC b
WHERE
a.DrugsBase_Manufacturer = '{0}' AND
b.DrugsBase_ID = a.DrugsBase_ID
GROUP BY b.ProductionClassId) AS t2
WHERE t1.pharm_ID=t2.pharmID
ORDER BY DrugNumber DESC;", drugsBase_Manufacturer);
DataTable dt = base.ExecuteTableForCache(sql, DateTime.Now.AddDays(1));
if (dt != null && dt.Rows != null && dt.Rows.Count > 0)
{
rtnObjs = new List <DrugsPharm>();
foreach (DataRow rowObj in dt.Rows)
{
DrugsPharm obj = new DrugsPharm();
obj.DrugsBase_Manufacturer = drugsBase_Manufacturer;
obj.Pharm_ID_Path = ((int)Enum_中药饮片.中药饮片).ToString();
obj.Pharm_ID = (int)rowObj["Pharm_ID"];
obj.Pharm_Name = rowObj["Pharm_Name"].ToString();
obj.DrugNumber = (int)rowObj["DrugNumber"];
obj.Root_Pharm_ID = (int)Enum_中药饮片.中药饮片;
obj.Root_Pharm_Name = Enum_中药饮片.中药饮片.ToString();
rtnObjs.Add(obj);
}
}
}
catch
{
}
return(rtnObjs);
}
/// <summary>
/// 获取指定厂家的二级药品分类
/// </summary>
/// <param name="drugsBase_Manufacturer">厂家名称</param>
/// <returns></returns>
public List <DrugsPharm> GetDrugsPharm(string drugsBase_Manufacturer)
{
List <DrugsPharm> rtnObjs = new List <DrugsPharm>();
List <DrugsPharm> drugsPharms = GetDrugsPharmInfo(drugsBase_Manufacturer); //指定药厂的药品分类
List <PharmInfo> secondPharms = GetPharmInfo(2); //二级分类的基础信息
if (drugsPharms != null && drugsPharms.Count > 0 && secondPharms != null && secondPharms.Count > 0)
{
foreach (PharmInfo s in secondPharms)
{
if (s.Pharm_ID_Path != ((int)Enum_中药饮片.中药饮片).ToString())
{
//查询药品的分类路径是否包含二级分类的分类路径
List <DrugsPharm> tmpDrugs = drugsPharms.FindAll(d => d.Pharm_ID_Path.Contains(s.Pharm_ID_Path));
if (tmpDrugs != null && tmpDrugs.Count > 0)
{
DrugsPharm tmpDrug = new DrugsPharm();
tmpDrug.DrugsBase_Manufacturer = drugsBase_Manufacturer;
tmpDrug.Pharm_ID = s.Pharm_ID;
tmpDrug.Pharm_Name = s.Pharm_Name;
tmpDrug.Pharm_ID_Path = s.Pharm_ID_Path;
//根级分类Id(二级分类的父Id即为该药品的根Id)
tmpDrug.Root_Pharm_ID = s.Pharm_Parent_ID;
//获取根级分类名称
string rootName = s.Pharm_Name_Path;
if (!string.IsNullOrEmpty(s.Pharm_Name_Path) && s.Pharm_Name_Path.Contains(@"\"))
{
int i = s.Pharm_Name_Path.IndexOf(@"\");
rootName = s.Pharm_Name_Path.Substring(0, i);
}
tmpDrug.Root_Pharm_Name = rootName;
int num = 0;
foreach (DrugsPharm d in tmpDrugs)
{
num += d.DrugNumber;
}
tmpDrug.DrugNumber = num;
rtnObjs.Add(tmpDrug);
}
}
}
}
//指定药厂的中药饮片分类
List <DrugsPharm> ZYCPharms = GetZYCPharmInfo(drugsBase_Manufacturer);
if (ZYCPharms != null && ZYCPharms.Count > 0)
{
rtnObjs.AddRange(ZYCPharms);
}
List <DrugsPharm> results = rtnObjs.OrderByDescending(d => d.DrugNumber).OrderBy(d => d.Root_Pharm_ID).ToList <DrugsPharm>();
return(results);
}
/// <summary>
/// 获取全部进口药品的分类信息
/// </summary>
/// <returns></returns>
private List <DrugsPharm> GetImpDrugsPharmInfo()
{
List <DrugsPharm> rtnObjs = null;
string sql = string.Format(@"SELECT drugsBase_Manufacturer_ID,
drugsBase_Manufacturer,
drugsBase_Id,
pharm_id,
pharm_name,
pharm_parent_id,
pharm_id_path
FROM View_ImportedPharm_Manufacturer_Pharm
ORDER BY drugsBase_Manufacturer
");
DataTable dt = base.ExecuteTableForCache(sql, DateTime.Now.AddHours(1));
if (dt != null && dt.Rows != null && dt.Rows.Count > 0)
{
rtnObjs = new List <DrugsPharm>();
foreach (DataRow rowObj in dt.Rows)
{
//如果该药品Id已添加，则不再添加（因为一种药品的子分类可能有多个）
if (rtnObjs.Find(p => (p.DrugsBase_ID == (int)rowObj["DrugsBase_ID"]) && (p.ParentId == (int)rowObj["pharm_parent_id"])) == null)
{
DrugsPharm obj = new DrugsPharm();
obj.DrugsBase_Manufacturer_Id = (int)rowObj["drugsBase_Manufacturer_id"];
obj.DrugsBase_Manufacturer = rowObj["drugsBase_Manufacturer"].ToString();
obj.DrugsBase_ID = (int)rowObj["drugsBase_Id"];
//最后的字符是否“\”，不是则添加。为后续做路径判断做准备。
string tmpPath = rowObj["pharm_id_path"].ToString();
if (tmpPath.Substring(tmpPath.Length - 1, 1) != @"\")
{
obj.Pharm_ID_Path = tmpPath + @"\";
}
else
{
obj.Pharm_ID_Path = tmpPath;
}
obj.Pharm_ID = (int)rowObj["pharm_id"];
obj.Pharm_Name = rowObj["pharm_name"].ToString();
obj.ParentId = (int)rowObj["pharm_parent_id"];
//obj.DrugNumber = (int)rowObj["pharmNum"];
rtnObjs.Add(obj);
}
}
}
return(rtnObjs);
}
/// <summary>
/// 获取指定厂家的二级进口药品分类
/// </summary>
/// <param name="drugsBase_Manufacturer_Id">厂家名称</param>
/// <returns></returns>
public List <DrugsPharm> GetImpDrugsPharm(int drugsBase_Manufacturer_Id)
{
List <DrugsPharm> rtnObjs = new List <DrugsPharm>();
List <DrugsPharm> drugsPharms = GetImpDrugsPharmInfo(); //全部药品分类
List <PharmInfo> secondPharms = GetPharmInfo(); //二级分类的基础信息
if (drugsPharms != null && drugsPharms.Count > 0 && secondPharms != null && secondPharms.Count > 0)
{
foreach (PharmInfo s in secondPharms)
{
//查询药品的分类路径是否包含二级分类的分类路径
List <DrugsPharm> tmpDrugs = null;
tmpDrugs = drugsPharms.FindAll(d => d.Pharm_ID_Path.Contains(s.Pharm_ID_Path + ((s.Pharm_ID_Path.Substring(s.Pharm_ID_Path.Length - 1, 1) != @"\") ? @"\" : "")));
if (tmpDrugs != null && tmpDrugs.Count > 0)
{
DrugsPharm tmpDrug = new DrugsPharm();
tmpDrug.Pharm_ID = s.Pharm_ID;
tmpDrug.Pharm_Name = s.Pharm_Name;
tmpDrug.Pharm_ID_Path = s.Pharm_ID_Path;
//统计分类数量
int num = 0;
foreach (DrugsPharm d in tmpDrugs)
{
if (drugsBase_Manufacturer_Id > 0) //指定药厂
{
if (d.DrugsBase_Manufacturer_Id == drugsBase_Manufacturer_Id)
{
num++;
}
}
else //未指定药厂
{
num++;
}
}
tmpDrug.DrugNumber = num;
rtnObjs.Add(tmpDrug);
}
}
}
List <DrugsPharm> results = rtnObjs.OrderByDescending(d => d.DrugNumber).ToList <DrugsPharm>();
return(results);
}
