public bool CancelBillItems(int idnt, string items, string notes)
{
Bills bill = new Bills {
Id = idnt
};
Users user = new Users {
Id = long.Parse(HttpContext.User.FindFirst(ClaimTypes.Actor).Value)
};
List <string> Items = items.Split(new string[] { ",", " " }, StringSplitOptions.RemoveEmptyEntries).ToList();
foreach (var id in Items)
{
BillsItem item = new BillsItem {
Id = Convert.ToInt64(id),
Bill = bill,
VoidedBy = user,
VoidedReason = notes
};
item.Void();
}
bill.ProcessedBy = user;
bill.SetAutoFlag();
return(true);
}
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);
}
public BillsItem VoidBillsItem(BillsItem item)
{
SqlServerConnection conn = new SqlServerConnection();
conn.SqlServerUpdate("DECLARE @idnt INT=" + item.Id + ", @user INT=" + item.VoidedBy.Id + ", @reason NVARCHAR(MAX)='" + item.VoidedReason + "'; UPDATE BillsItem SET bi_void=1, bi_void_on=GETDATE(), bi_void_by=@user, bi_void_reason=@reason WHERE bi_idnt=@idnt");
return(item);
}
public BillsItem SaveBillsItem(BillsItem item)
{
SqlServerConnection conn = new SqlServerConnection();
item.Id = conn.SqlServerUpdate("DECLARE @idnt INT=" + item.Id + ", @bill INT=" + item.Bill.Id + ", @service INT=" + item.Service.Id + ", @amount FLOAT=" + item.Price + ", @user INT=" + item.CreatedBy.Id + ", @desc NVARCHAR(MAX)='" + item.Description + "'; IF NOT EXISTS (SELECT bi_idnt FROM BillsItem WHERE bi_idnt=@idnt) BEGIN INSERT INTO BillsItem (bi_bill, bi_service, bi_amount, bi_created_by, bi_description) output INSERTED.bi_idnt VALUES (@bill, @service, @amount, @user, @desc) END ELSE BEGIN UPDATE BillsItem SET bi_service=@service, bi_amount=@amount, bi_description=@desc output INSERTED.bi_idnt WHERE bi_idnt=@idnt END");
return(item);
}
public IActionResult Add(BillsItem billsItem)
{
if (ModelState.IsValid)
{
var res = BillsItemService.Add(billsItem);
return(Json(new { code = res }));
}
return(Json(new { code = -1 }));
}
public Bills SelectBill(int id, 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.ID=@ID
";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@ID", id));
SqlDataReader reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
Bills bill = null;
while (reader.Read())
{
bill = new Bills();
BillModelHelper(reader, bill);
}
reader.Close();
if (bill != null)
{
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", id));
reader = DataAccessUtil.ExecuteReader(sql, paramList, (SqlConnection)conn);
while (reader.Read())
{
BillsItem item = new BillsItem();
BillItemModelHelper(reader, item);
bill.Items.Add(item);
}
}
reader.Close();
return(bill);
}
/// <summary>
/// 保存单据并修改库存
/// </summary>
public void DoSave()
{
//verify
if (cmbCompany.SelectedItem == null)
{
throw new ApplicationException("请指定关联单位");
}
if (cmbStorehouse.SelectedItem == null)
{
throw new ApplicationException("请指定仓库");
}
if (dgvDrugsList.Rows.Count <= 1)
{
throw new ApplicationException("请指定药品");
}
//Contruct Model
Bills bill = new Bills();
bill.BillsType = this.BillsType;
bill.Actived = true;
bill.Code = DateTime.Now.ToString("yyyyMMddHHmmssfff");
bill.Company = (Company)cmbCompany.SelectedItem;
bill.IsCancelOut = false;
bill.IsRedBill = false;
bill.MakeDate = this.dtpMakeDate.Value;
bill.CreateDate = DateTime.Now;
bill.Maker = this.txtMaker.Text.Trim();
bill.Storehouse = (Storehouse)this.cmbStorehouse.SelectedItem;
bill.Remark = this.txtRemark.Text.Trim();
foreach (DataGridViewRow row in dgvDrugsList.Rows)
{
if (row.Tag != null)
{
BillsItem billsItem = (BillsItem)row.Tag;
if (billsItem.Remark == null)
{
billsItem.Remark = "没有备注";
}
bill.Items.Add(billsItem);
}
}
//Do Save
StockBillService stockBillService = new StockBillService();
stockBillService.MakeBill(bill);
if (MessageBox.Show("保存成功，是否打印单据？", "打印提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
new PrintService().PrintBill(bill);
}
this.DoReset();
}
public Queues()
{
Id = 0;
Date = "";
Start = "";
Ended = "";
Priority = new QueuesPriority();
Item = new BillsItem();
Visit = new Visit();
Room = new Room();
CreatedBy = new Users();
CreatedOn = DateTime.Now;
Notes = "";
}
public RegistrationVisitViewModel()
{
Visit = new Visit();
Bill = new Bills();
Item = new BillsItem();
Referral = new Referral();
Codes = new List <SelectListItem>();
Rooms = new List <SelectListItem>();
Types = new List <SelectListItem>();
Doctors = new List <SelectListItem>();
Referrals = new List <SelectListItem>();
MedicoLegal = new List <SelectListItem>();
DoctorString = "";
Doctor = "";
Waiver = false;
WaiverReason = "";
}
private void BillItemModelHelper(SqlDataReader reader, BillsItem item)
{
item.ID = reader.GetInt32(0);
item.Count = reader.GetInt32(1);
item.UnitPrice = reader.GetDecimal(2);
item.Money = reader.GetDecimal(3);
if (!reader.IsDBNull(4))
{
item.Remark = reader.GetString(4);
}
item.Drugs = new Drugs();
item.Drugs.ID = reader.GetInt32(5);
item.Drugs.Code = reader.GetString(6);
item.Drugs.Name = reader.GetString(7);
item.Drugs.UnitPrice = reader.GetDecimal(8);
item.Drugs.Standard = reader.GetString(9);
item.Drugs.Unit = reader.GetString(10);
item.Drugs.Actived = reader.GetBoolean(11);
if (!reader.IsDBNull(12))
{
item.Drugs.Remark = reader.GetString(12);
}
item.Drugs.From = new DrugFrom();
item.Drugs.From.ID = reader.GetInt32(13);
item.Drugs.From.Code = reader.GetString(14);
item.Drugs.From.Name = reader.GetString(15);
item.Drugs.From.Actived = reader.GetBoolean(16);
if (!reader.IsDBNull(17))
{
item.Drugs.From.Remark = reader.GetString(17);
}
item.Drugs.Category = new DrugCategory();
item.Drugs.Category.ID = reader.GetInt32(18);
item.Drugs.Category.Code = reader.GetString(19);
item.Drugs.Category.Name = reader.GetString(20);
item.Drugs.Category.Actived = reader.GetBoolean(21);
if (!reader.IsDBNull(22))
{
item.Drugs.Category.Remark = reader.GetString(22);
}
}
//todo
public int Edit(BillsItem billsItem)
{
GetById(billsItem.Id);
return(0);
}
public int Add(BillsItem billsItem)
{
DbContext.BillsItems.Add(billsItem);
return(DbContext.SaveChanges());
}
/// <summary>
/// 冲销一条票据
/// </summary>
/// <param name="bill">票据</param>
/// <param name="maker">冲票人</param>
/// <param name="makeDate">冲票日期</param>
/// <param name="remark">备注</param>
public Bills CancelOutBill(Bills bill, string maker, DateTime makeDate, string remark)
{
using (IDbConnection conn = DAOFactory.Instance.OpenConnection())
{
//1、构造负子票
Bills redBill = new Bills();
redBill.Actived = true;
redBill.BillsType = bill.BillsType;
redBill.Code = bill.Code;
redBill.Company = bill.Company;
redBill.CreateDate = DateTime.Now;
redBill.IsCancelOut = false;
redBill.IsRedBill = true;
redBill.MakeDate = makeDate;
redBill.Maker = maker;
redBill.Storehouse = bill.Storehouse;
redBill.Remark = "对冲票据" + bill.ID.ToString();
foreach (BillsItem item in bill.Items)
{
BillsItem redbillitem = new BillsItem();
redbillitem.Drugs = item.Drugs;
redbillitem.Count = item.Count * (-1);
redbillitem.UnitPrice = item.UnitPrice;
redbillitem.Money = item.Money * (-1);
redBill.Items.Add(redbillitem);
}
IDbTransaction trans = conn.BeginTransaction();
try
{
IBillsDAO billDao = DAOFactory.Instance.CreateBillsDAO();
// 2. 保存红票
billDao.InsertBill(redBill, conn, trans);
// 3. 保存原票据信息
bill.IsCancelOut = true;
bill.Remark += " 被冲销" + redBill.ID.ToString();
billDao.UpdateBill(bill, conn, trans);
// 4. 更改库存数据
IStockDAO stockDao = DAOFactory.Instance.CreateStockDAO();
foreach (BillsItem item in redBill.Items)
{
Stock stock = stockDao.SelectStockByDrugsAndStorehouse(item.Drugs.ID, bill.Storehouse.ID, conn, trans);
if (redBill.BillsType.IsOut)
{
stock.Count -= item.Count;
}
else
{
stock.Count += item.Count;
}
stockDao.UpdateStock(stock, conn, trans);
}
//5、提交
trans.Commit();
}
catch (Exception ex)
{
//6、回滚
bill.IsCancelOut = false;
trans.Rollback();
throw ex;
}
return(redBill);
}
}
private void dgvDrugsList_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
try
{
DataGridViewRow row = this.dgvDrugsList.Rows[e.RowIndex];
object value = this.dgvDrugsList.Rows[e.RowIndex].Cells[e.ColumnIndex].Value;
if (value == null)
{
return;
}
BillsItem item = null;
switch (e.ColumnIndex)
{
case 0: //输入药品编码
string drugCode = value.ToString();
ModelService modelService = new ModelService();
Drugs drug = modelService.GetDrugsByCode(drugCode);
//Fill drugs info into datagridview row
if (drug == null)
{
throw new ApplicationException("编码错误，没有匹配的药物");
}
DataGridViewTextBoxCell cell = null;
cell = new DataGridViewTextBoxCell();
cell.Value = drug.Name;
row.Cells[1] = cell;
cell = new DataGridViewTextBoxCell();
cell.Value = drug.UnitPrice;
row.Cells[2] = cell;
cell = new DataGridViewTextBoxCell();
cell.Value = drug.Standard;
row.Cells[3] = cell;
cell = new DataGridViewTextBoxCell();
cell.Value = drug.Unit;
row.Cells[4] = cell;
cell = new DataGridViewTextBoxCell();
cell.Value = drug.Category.Name;
row.Cells[5] = cell;
cell = new DataGridViewTextBoxCell();
cell.Value = drug.From.Name;
row.Cells[6] = cell;
//add BillsItem into row's tag
item = (BillsItem)row.Tag;
if (item == null)
{
item = new BillsItem();
row.Tag = item;
}
item.Drugs = drug;
item.UnitPrice = drug.UnitPrice;
break;
case 2: //输入单价编码
decimal unitPrice = decimal.Parse(value.ToString());
item = (BillsItem)row.Tag;
if (item == null)
{
item = new BillsItem();
row.Tag = item;
}
item.UnitPrice = unitPrice;
if (item.Count != 0 && item.UnitPrice != 0)
{
row.Cells[8].Value = item.Count * item.UnitPrice;
item.Money = decimal.Parse(row.Cells[8].Value.ToString());
}
break;
case 7: //输入数量编码
int count = int.Parse(value.ToString());
item = (BillsItem)row.Tag;
if (item == null)
{
item = new BillsItem();
row.Tag = item;
}
item.Count = count;
if (item.Count != 0 && item.UnitPrice != 0)
{
row.Cells[8].Value = item.Count * item.UnitPrice;
item.Money = decimal.Parse(row.Cells[8].Value.ToString());
}
break;
case 8: //输入金额编码
decimal money = decimal.Parse(value.ToString());
item = (BillsItem)row.Tag;
if (item == null)
{
item = new BillsItem();
row.Tag = item;
}
item.Money = money;
break;
default:
break;
}
}
catch (Exception ex)
{
ErrorHandler.OnError(ex);
}
}
public IActionResult RegisterVisit()
{
Users user = new Users {
Id = Int64.Parse(HttpContext.User.FindFirst(ClaimTypes.Actor).Value)
};
Patient patient = VisitModel.Visit.Patient;
patient.Status = new PatientStatus
{
Id = Constants.STATUS_ACTIVE
};
patient.UpdateStatus();
Encounter encounter = new Encounter
{
Type = new EncounterType {
Id = Constants.ENCOUNTER_VISIT
},
Patient = patient,
CreatedBy = user
};
encounter.Create(IPatientService);
Visit visit = VisitModel.Visit;
visit.Type = new VisitType {
Id = Constants.VISIT_FACILITY
};
visit.CreatedBy = user;
visit.CreatedOn = DateTime.Now;
visit.Save();
Referral referral = VisitModel.Referral;
if (!referral.Type.Id.Equals(0))
{
referral.Visit = visit;
referral.CreatedBy = user;
referral.Save();
var seps = new string[] { "," };
string[] doctors = VisitModel.DoctorString.Split(seps, StringSplitOptions.RemoveEmptyEntries);
foreach (string doc in doctors)
{
new ReferralDoctors
{
Referral = referral,
Doctor = new Doctor {
Id = Convert.ToInt64(doc)
}
}.Save();
}
}
Bills bill = VisitModel.Bill;
bill.Visit = visit;
bill.CreatedBy = user;
bill.Save();
if (VisitModel.Waiver)
{
bill.Waiver = bill.Amount;
bill.WaiverReason = VisitModel.WaiverReason;
bill.WaivedBy = user;
bill.UpdateWaiver();
bill.Flag = new BillsFlag {
Id = 1
};
bill.ProcessedBy = user;
bill.UpdateProcess();
}
BillsItem item = VisitModel.Item;
item.Bill = bill;
item.CreatedBy = user;
item.Save();
Queues queue = VisitModel.Queue;
queue.Bill = bill;
queue.CreatedBy = user;
queue.Save();
return(LocalRedirect("/registration/search"));
}
public IActionResult RegisterVisit()
{
Users user = new Users {
Id = Int64.Parse(HttpContext.User.FindFirst(ClaimTypes.Actor).Value)
};
Patient patient = VisitModel.Visit.Patient;
patient.Status = new PatientStatus
{
Id = Constants.STATUS_ACTIVE
};
patient.UpdateStatus();
Encounter encounter = new Encounter
{
Type = new EncounterType {
Id = Constants.ENCOUNTER_VISIT
},
Patient = patient,
CreatedBy = user
};
encounter.Create(IPatientService);
Visit visit = VisitModel.Visit;
visit.Type = new VisitType {
Id = Constants.VISIT_FACILITY
};
visit.CreatedBy = user;
visit.CreatedOn = DateTime.Now;
visit.Save();
Referral referral = VisitModel.Referral;
if (!referral.Type.Id.Equals(0))
{
referral.Visit = visit;
referral.CreatedBy = user;
referral.Save();
var seps = new string[] { "," };
string[] doctors = VisitModel.DoctorString.Split(seps, StringSplitOptions.RemoveEmptyEntries);
foreach (string doc in doctors)
{
new ReferralDoctors
{
Referral = referral,
Doctor = new Doctor {
Id = Convert.ToInt64(doc)
}
}.Save();
}
}
Bills bill = VisitModel.Bill;
bill.Visit = visit;
bill.Department = new Department {
Id = Constants.DEPT_REGISTRATION
};
bill.CreatedBy = user;
bill.Save();
if (VisitModel.Waiver)
{
bill.Waiver = bill.Amount;
bill.WaiverReason = VisitModel.WaiverReason;
bill.WaivedBy = user;
bill.UpdateWaiver();
bill.Flag = new BillingFlag {
Id = Constants.FLAG_CLEARED
};
bill.ProcessedBy = user;
bill.UpdateProcess();
}
BillsItem item = VisitModel.Item;
item.Bill = bill;
item.CreatedBy = user;
item.Save();
//INVOICE/FLAG/CLEAR
if (VisitModel.Waiver)
{
Invoice invoice = new Invoice {
Patient = patient,
Flag = new BillingFlag {
Id = Constants.FLAG_CLEARED
},
CreatedBy = user,
Notes = "Waivered Bill"
};
invoice.Save();
InvoiceDetails details = new InvoiceDetails {
Invoice = invoice,
Item = item,
CreatedBy = user,
Notes = "Waivered Item"
};
details.Save();
InvoicePayment payment = new InvoicePayment {
Invoice = invoice,
CreatedBy = user,
Notes = "Waiver Payment"
};
payment.Save();
InvoicePaymentDetails pd = new InvoicePaymentDetails {
Mode = new BillingMode {
Id = Constants.MODE_WAIVER
},
Reference = "CASHIER-00",
Amount = bill.Amount,
Notes = "Waiver Payment"
};
pd.Save();
}
Queues queue = VisitModel.Queue;
queue.Visit = visit;
queue.Item = item;
queue.CreatedBy = user;
queue.Save();
return(LocalRedirect("/registration/search"));
}
