承志医疗管理系统技术解析住院发药与退药（七）

dbo.KC_StockList

Primary Key(s):  StockID

表3：库房表

Primary Key(s):  StoreroomID

表4：药品表

Primary Key(s):  MedicineID

打开界面，显示的是对应某个病人的药品，实现的功能代码；

数据库存储过程

  if @Type='Frm_ZhuYuanFaYao_SelectYaoPing'begin
SELECT     AttributeMingXiList.AttributeMingXiName AS Office, AttributeMingXiList_1.AttributeMingXiName AS Usage, AttributeMingXiList_2.AttributeMingXiName AS UseMedicineFrequency, AttributeMingXiList_3.AttributeMingXiName AS AtEverturnDse, MZ_PrescriptionBillsMingXiList.PrescriptionBillsMingXiID, MZ_PrescriptionBillsMingXiList.SendOutMedicineNo, MZ_PrescriptionBillsMingXiList.AtEveryTurnNumber, KC_MedicineList.Specification, AttributeMingXiList_4.AttributeMingXiName AS Unit, KC_MedicineList.RetailPrice_J, KC_MedicineList.ParturitionManufacturers, AttributeMingXiList_5.AttributeMingXiName AS SkinTest, KC_MedicineList.[Content], ISNULL(MZ_PrescriptionBillsMingXiList.RetreatMedicineNumber, 0) AS RetreatMedicineNumber, KC_MedicineList.MedicineName, MZ_PrescriptionBillsMingXiList.Number, MZ_PrescriptionBillsMingXiList.MedicineID
FROM         AttributeMingXiList AS AttributeMingXiList_5 INNER JOINMZ_PrescriptionBillsMingXiList INNER JOINAttributeMingXiList ON MZ_PrescriptionBillsMingXiList.OfficeID = AttributeMingXiList.AttributeMingXiID INNER JOINAttributeMingXiList AS AttributeMingXiList_1 ON MZ_PrescriptionBillsMingXiList.UsageID = AttributeMingXiList_1.AttributeMingXiID INNER JOINAttributeMingXiList AS AttributeMingXiList_2 ON MZ_PrescriptionBillsMingXiList.UseMedicineFrequencyID = AttributeMingXiList_2.AttributeMingXiID INNER JOINAttributeMingXiList AS AttributeMingXiList_3 ON MZ_PrescriptionBillsMingXiList.AtEveryTurnDoseID = AttributeMingXiList_3.AttributeMingXiID ON AttributeMingXiList_5.AttributeMingXiID = MZ_PrescriptionBillsMingXiList.SkinTestID INNER JOINAttributeMingXiList AS AttributeMingXiList_4 INNER JOINKC_MedicineList ON AttributeMingXiList_4.AttributeMingXiID = KC_MedicineList.MeteringUnitID ON MZ_PrescriptionBillsMingXiList.MedicineID = KC_MedicineList.MedicineID               where MZ_PrescriptionBillsMingXiList.InhospitalNo=1 and MZ_PrescriptionBillsMingXiList.PrescriptionBillsID=@PrescriptionBillsID andMZ_PrescriptionBillsMingXiList.SendOutMedicineNo=@SendOutMedicineNoend

  [OperationContract]public DataSet Frm_ZhuYuanFaYao_SelectYaoPing(int PrescriptionBillsID, bool SendOutMedicineNo){SqlParameter[] mySqlParameters = {new SqlParameter ("@Type",SqlDbType .Char),new SqlParameter ("@PrescriptionBillsID",SqlDbType.Int),new SqlParameter ("@SendOutMedicineNo",SqlDbType.Bit),};mySqlParameters[0].Value = "Frm_ZhuYuanFaYao_SelectYaoPing";mySqlParameters[1].Value = PrescriptionBillsID;mySqlParameters[2].Value = SendOutMedicineNo;DataTable dt = myDALMethod.QueryDataTable("住院登记_Frm_ZhuYuanFaYao", mySqlParameters);DataSet ds = new DataSet();ds.Tables.Add(dt);return ds;}

界面层

private void dgSickInformation_CellClick(object sender, DataGridViewCellEventArgs e){try{  //表格单击时获取病人住院IDZhuYuanID = Convert.ToInt32(dgSickInformation.CurrentRow.Cells["RegisterInhospitalID"].Value);//根据ID来绑定对应的药品dgMedicinalInformation.DataSource = myFrm_ZhuYuanFaYaoClient.Frm_ZhuYuanFaYao_SelectYaoPing(ZhuYuanID, false).Tables[0];JiSuJinE();}catch { }}

住院发药，针对每天提交医嘱的病人进行发药，可以一键全选，可以取消一键全选，实现的功能和代码如下；

一键全选的实现

private void btnSelecttAll_Click(object sender, EventArgs e){    //遍历每一行foreach (DataGridViewRow dgv in dgMedicinalInformation.Rows){  //如果“确认”的单元格的值为假if(Convert.ToBoolean( dgv.Cells["确认"].Value)==false){   //就赋值为turedgv.Cells["确认"].Value= true;}}}
一键取消的实现private void btnCancelAllSelect_Click(object sender, EventArgs e){ //遍历每一行foreach (DataGridViewRow dgv in dgMedicinalInformation.Rows){//如果“确认”的单元格的值为真if (Convert.ToBoolean(dgv.Cells["确认"].Value) == true){ //就赋值为faledgv.Cells["确认"].Value = false;}}}

选好了以后，点击发药处置。完成发药。功能代码实现；

数据库存储过程

 if @Type='Frm_ZhuYuanFaYao_InsertSendOutMedicineList'begininsert       ZY_SendOutMedicineList( MedicineName, Gross, UserFrequency,UserMethod, Specification, Unit, UnitPrice, Money, ParturitionManufacturers, OnceDose, [Content], SendOutMedicineDate, SendOutMedicineNo, RegisterInhospitalID)values (@MedicineName, @Gross, @UserFrequency, @UserMethod,@Specification, @Unit, @UnitPrice, @Money, @ParturitionManufacturers,@OnceDose, @Content, @SendOutMedicineDate, @SendOutMedicineNo, @RegisterInhospitalID)end

逻辑层

 [OperationContract]public int Frm_ZhuYuanFaYao_InsertSendOutMedicineList(string MedicineName, decimal Gross,string UserFrequency, string UserMethod,string Specification,string Unit ,decimal  UnitPrice,decimal Money,string ParturitionManufacturers,string  OnceDose,string  Content,DateTime SendOutMedicineDate,bool SendOutMedicineNo,int RegisterInhospitalID){SqlParameter[] mySqlParameters = {new SqlParameter ("@Type",SqlDbType .Char),new SqlParameter ("@MedicineName",SqlDbType.Char),new SqlParameter ("@Gross",SqlDbType.Decimal),new SqlParameter ("@UserFrequency",SqlDbType.Char),new SqlParameter ("@UserMethod",SqlDbType.Char),new SqlParameter ("@Specification",SqlDbType.Char),new SqlParameter ("@Unit",SqlDbType.Char),new SqlParameter ("@UnitPrice",SqlDbType.Decimal),new SqlParameter ("@Money",SqlDbType.Decimal),new SqlParameter ("@ParturitionManufacturers",SqlDbType.Char),new SqlParameter ("@OnceDose",SqlDbType.Char),new SqlParameter ("@Content",SqlDbType.Char),new SqlParameter ("@SendOutMedicineDate",SqlDbType.DateTime),new SqlParameter ("@SendOutMedicineNo",SqlDbType.Bit),new SqlParameter ("@RegisterInhospitalID",SqlDbType.Int),};mySqlParameters[0].Value = "Frm_ZhuYuanFaYao_InsertSendOutMedicineList";mySqlParameters[1].Value = MedicineName;mySqlParameters[2].Value = Gross;mySqlParameters[3].Value = UserFrequency;mySqlParameters[4].Value = UserMethod;mySqlParameters[5].Value = Specification;mySqlParameters[6].Value = Unit;mySqlParameters[7].Value = UnitPrice;mySqlParameters[8].Value = Money;mySqlParameters[9].Value = ParturitionManufacturers;mySqlParameters[10].Value = OnceDose;mySqlParameters[11].Value = Content;mySqlParameters[12].Value = SendOutMedicineDate;mySqlParameters[13].Value = SendOutMedicineNo;mySqlParameters[14].Value = RegisterInhospitalID;return myDALMethod.UpdateData("住院登记_Frm_ZhuYuanFaYao", mySqlParameters);}

界面层

    /// <summary>/// foreach遍历获取值，遍历一行一行的新增/// </summary>int ChengGong;int i;private void btnFaYaoChuZhi_Click(object sender, EventArgs e){foreach (DataGridViewRow dgv in dgMedicinalInformation.Rows){if (Convert.ToBoolean(dgv.Cells["确认"].Value)){string MedicineNam = Convert.ToString(dgv.Cells["MedicineName"].Value);decimal Gross = Convert.ToDecimal(dgv.Cells["Number"].Value);string UserFrequency = Convert.ToString(dgv.Cells["UseMedicineFrequency"].Value);string UserMethod = Convert.ToString(dgv.Cells["Usage"].Value);string Specification = Convert.ToString(dgv.Cells["Specification"].Value);string Unit = Convert.ToString(dgv.Cells["Unit"].Value);decimal UnitPrice = Convert.ToDecimal(dgv.Cells["RetailPrice_J"].Value);decimal Money = Convert.ToDecimal(dgv.Cells["SumMoney"].Value);string ParturitionManufacturers = Convert.ToString(dgv.Cells["ParturitionManufacturers"].Value);string OnceDose = Convert.ToString(dgv.Cells["AtEverturnDse"].Value);string Content = Convert.ToString(dgv.Cells["Content"].Value);DateTime SendOutMedicineDate = DateTime.Now;bool SendOutMedicineNo = true;int RegisterInhospitalID = ZhuYuanID;int FaYaoDanID = Convert.ToInt32(dgv.Cells["PrescriptionBillsMingXiID"].Value);ChengGong = myFrm_ZhuYuanFaYaoClient.Frm_ZhuYuanFaYao_InsertSendOutMedicineList(MedicineNam, Gross, UserFrequency, UserMethod,Specification, Unit, UnitPrice, Money, ParturitionManufacturers, OnceDose,Content, SendOutMedicineDate, SendOutMedicineNo, RegisterInhospitalID);i = myFrm_ZhuYuanFaYaoClient.Frm_ZhuYuanFaYao_UpdateSendOutMedicineNo(FaYaoDanID);}}if (ChengGong > 0 && i > 0){MessageBox.Show("发药成功");try{//保存成功后刷新ZhuYuanID = Convert.ToInt32(dgSickInformation.CurrentRow.Cells["RegisterInhospitalID"].Value);dgMedicinalInformation.DataSource = myFrm_ZhuYuanFaYaoClient.Frm_ZhuYuanFaYao_SelectYaoPing(ZhuYuanID, false).Tables[0];JiSuJinE();}catch { }}}

除了发药还可以进行病人的退药处理，退药时会对病人开药时的费用进行退还，填写对应的退药数量，根据病人的医疗性质，进行对应的费用合计，退还。

根据对应的退药数量计算退款。功能实现

/// <summary>/// 单元格编辑事件，计算退款/// </summary>/// <param name="sender"></param>/// <param name="e"></param>private void dgMedicinalInformation_CellEndEdit(object sender, DataGridViewCellEventArgs e){JiSuJinE();//调用计算退款方法//遍历每个输入数量是否大于总数量foreach (DataGridViewRow dgv in dgMedicinalInformation.Rows){decimal ShuRuShuLiang = Convert.ToDecimal(dgv.Cells["TuYaoShuLiang"].Value);if (ShuRuShuLiang >Convert.ToDecimal (dgv.Cells["Number"].Value)){MessageBox.Show("输入数量已经过总数量！请重新输入退药数量");}}}#region 计算退药金额void JiSuJinE(){decimal DanJia = 0;decimal ShuLiang = 0;decimal TuYaoShuLiang = 0;decimal tuikuang = 0;for (int i = 0; i < dgMedicinalInformation.Rows.Count; i++){//获取当前行单价DanJia = Convert.ToDecimal(dgMedicinalInformation.Rows[i].Cells["RetailPrice_J"].Value);//获取数量ShuLiang = Convert.ToDecimal(dgMedicinalInformation.Rows[i].Cells["Number"].Value);// 获取药品IDint YaoPingID = Convert.ToInt32(dgMedicinalInformation.Rows[i].Cells["MedicineID"].Value);//获取退药数量TuYaoShuLiang = Convert.ToDecimal(dgMedicinalInformation.Rows[i].Cells["TuYaoShuLiang"].Value);//查询折扣率DataTable dtZheKouLv = myFrm_ZhuYuanJiZhangClient.Frm_ZhuYuanJiZhang_SelectZheKouLi(PulicStatic.YiLiaoFangShi, YaoPingID).Tables[0];// 如果该药品对应查询到折扣率if (dtZheKouLv.Rows.Count > 0){//则单元格退款金额=单价*退药数量*折扣率dgMedicinalInformation.Rows[i].Cells["退款金额"].Value =(DanJia * TuYaoShuLiang * Convert.ToDecimal(dtZheKouLv.Rows[0][0])).ToString("0.00");}else{//否则退款金额=单价*退款数量dgMedicinalInformation.Rows[i].Cells["退款金额"].Value = (DanJia * TuYaoShuLiang).ToString("0.00");}dgMedicinalInformation.Rows[i].Cells["SumMoney"].Value = (DanJia * ShuLiang).ToString("0.00");//计算总退款金额tuikuang += Convert.ToDecimal(dgMedicinalInformation.Rows[i].Cells["退款金额"].Value);}//赋值文本lblTuiKuang.Text = "-" + tuikuang.ToString("0.00");}#endregion

输入好退药数量无误后，进行退药保存

数据存储过程

if @Type='Frm_ZhuYuanTuiYao_TuiYaoUpDateKuFangSHuLiang'--退药修改库房数量
begin 
update   KC_StockList 
Set        StockNumber=StockNumber+@StockNumber
where   KC_StockList.StoreroomID=@StoreroomID and KC_StockList.MedicineID=@MedicineID
end
if @Type='Frm_ZhuYuanTuiYao_InsertKuCun'--如果没有就新增库房数据
begin 
insert     KC_StockList(  StockNumber,StoreroomID, MedicineID, StockUpperLimit, StockLowerLimit)
values      ( @StockNumber,@StoreroomID, @MedicineID, @StockUpperLimit, @StockLowerLimit)   
end

逻辑层代码

[OperationContract]public int Frm_ZhuYuanTuiYao_TuiYaoUpDateKuFangSHuLiang(decimal StockNumber, int StoreroomID, int MedicineID){SqlParameter[] mySqlParameters = {new SqlParameter ("@Type",SqlDbType .Char),new SqlParameter ("@StockNumber",SqlDbType.Decimal ),new SqlParameter ("@StoreroomID",SqlDbType.Int),new SqlParameter ("@MedicineID",SqlDbType.Int),};mySqlParameters[0].Value = "Frm_ZhuYuanTuiYao_TuiYaoUpDateKuFangSHuLiang";mySqlParameters[1].Value = StockNumber;mySqlParameters[2].Value = StoreroomID;mySqlParameters[3].Value = MedicineID;return myDALMethod.UpdateData("住院登记_Frm_ZhuYuanTuiYao", mySqlParameters);}[OperationContract]public int Frm_ZhuYuanTuiYao_InsertKuCun(decimal StockNumber, int StoreroomID, int MedicineID,decimal StockUpperLimit,decimal StockLowerLimit){SqlParameter[] mySqlParameters = {new SqlParameter ("@Type",SqlDbType .Char),new SqlParameter ("@StockNumber",SqlDbType.Decimal ),new SqlParameter ("@StoreroomID",SqlDbType.Int),new SqlParameter ("@MedicineID",SqlDbType.Int),new SqlParameter ("@StockUpperLimit",SqlDbType.Decimal),new SqlParameter ("@StockLowerLimit",SqlDbType.Decimal),};mySqlParameters[0].Value = "Frm_ZhuYuanTuiYao_InsertKuCun";mySqlParameters[1].Value = StockNumber;mySqlParameters[2].Value = StoreroomID;mySqlParameters[3].Value = MedicineID;mySqlParameters[4].Value = StockUpperLimit;mySqlParameters[5].Value = StockLowerLimit;return myDALMethod.UpdateData("住院登记_Frm_ZhuYuanTuiYao", mySqlParameters);}

界面层代码

#region 退药int intStoreroomIDMingXi;int inCount = 0;private void btnQuitMedicine_Click(object sender, EventArgs e){int ChengGong = 0;int j = 0;intStoreroomIDMingXi = Convert.ToInt32(cboPharmacy.SelectedValue);DataTable dt = myFrm__ZhuYuanTuiYaoClient.Frm_ZhuYuanTuiYao_SelectKuFangID().Tables[0];foreach (DataGridViewRow dgv in dgMedicinalInformation.Rows){decimal TuiYaoShuLiang = Convert.ToDecimal(dgv.Cells["TuYaoShuLiang"].Value);decimal ZongShu = Convert.ToDecimal(dgv.Cells["Number"].Value);if (TuiYaoShuLiang > ZongShu){MessageBox.Show("请注意当前有退要数比总数大，请检查后再退药！");return;}}for (int i = 0; i < dgMedicinalInformation.Rows.Count; i++){if (Convert.ToBoolean(dgMedicinalInformation.Rows[i].Cells["确认"].Value)){  //对应获取decimal TuiYaoShuLiang = Convert.ToDecimal(dgMedicinalInformation.Rows[i].Cells["TuYaoShuLiang"].Value);decimal YiTuiShuLiang = Convert.ToDecimal(dgMedicinalInformation.Rows[i].Cells["RetreatMedicineNumber"].Value);int ChuFangDanID = Convert.ToInt32(dgMedicinalInformation.Rows[i].Cells["PrescriptionBillsMingXiID"].Value);int YaoPingID = Convert.ToInt32(dgMedicinalInformation.Rows[i].Cells["MedicineID"].Value);for (int k = 0; k < dt.Rows.Count; k++){//获取药品Idint intMedicineID = Convert.ToInt32(dt.Rows[k]["MedicineID"]);//获取库房IDint intStoreroomID = Convert.ToInt32(dt.Rows[k]["StoreroomID"]);//如果库房药品ID=退药药品ID并且库房的库房ID=获取的库房IDif (intMedicineID == YaoPingID && intStoreroomID == intStoreroomIDMingXi){//证明库房表里有该药品记录好inCount++;}}//记录>0if (inCount > 0){//如果存在则修改myFrm__ZhuYuanTuiYaoClient.Frm_ZhuYuanTuiYao_TuiYaoUpDateKuFangSHuLiang(TuiYaoShuLiang, intStoreroomIDMingXi, YaoPingID);}else{//如果不存在则新增退药myFrm__ZhuYuanTuiYaoClient.Frm_ZhuYuanTuiYao_InsertKuCun(TuiYaoShuLiang, intStoreroomIDMingXi, YaoPingID, 100000, 111);}ChengGong = myFrm__ZhuYuanTuiYaoClient.Frm_ZhuYuanTuiYao_UpdatePrescriptionBillsMingXiNumnber(TuiYaoShuLiang, ChuFangDanID);j = myFrm__ZhuYuanTuiYaoClient.Frm_ZhuYuanTuiYao_UpdatePrescriptionBillsMingXiRetreatMedicineNumber(YiTuiShuLiang + TuiYaoShuLiang, ChuFangDanID);}}if (ChengGong > 0 && j > 0){DataTable dtJIZhang = myFrm_ZhuYuanJiZhangClient.Frm_ZhuYuanJiZhang_insertJiZhangDan(ZhuYuanID, 0, DateTime.Now).Tables[0];//记录一条退药费用int k = myFrm_ZhuYuanJiZhangClient.Frm_ZhuYuanJiZhang_insertJiZhangDanMingXi(0, Convert.ToInt32(dtJIZhang.Rows[0][0]),0, 0, 0, Convert.ToDecimal(lblTuiKuang.Text), "退药费");if (k > 0){MessageBox.Show("退药成功！");dgMedicinalInformation.DataSource = myFrm_ZhuYuanFaYaoClient.Frm_ZhuYuanFaYao_SelectYaoPing(ZhuYuanID, true).Tables[0];JiSuJinE();}}}#endregion