public DataTable GetConsumablesUseGoupBy(ConsumablesUseGetInput input)
{
string str = @"select b.PatDpm,c.Name,c.Dosage,SUM(c.Dosage*c.Price *c.IsCost) sumPrice
from Mzjld_ConsumablesUse c
inner join Adims_OTypesetting b on c.patid=b.patid
inner join Adims_Mzjld a on c.MzjldID=a.Id
where a.Otime between '{0}' and '{1}'
group by b.PatDpm,c.Name,c.Dosage";
string sql = string.Format(str, input.BeginTime, input.EndTime);
return(dBConn.GetDataTable(sql));
}
private void BindGridView()
{
ConsumablesUseGetInput input = new ConsumablesUseGetInput();
input.BeginTime = dtStart.Value.Date;
input.EndTime = dtEnd.Value.Date.AddDays(1);
DataTable dt = dal.GetConsumablesUseGoupBy(input);
this.dgvUseList.DataSource = dt;
double sum = 0.0;
foreach (DataRow dr in dt.Rows)
{
sum += Convert.ToDouble(dr["SumPrice"].ToString());
}
tbSum.Text = String.Format("{0:F}", sum);
}
private void BindGridView()
{
ConsumablesUseGetInput input = new ConsumablesUseGetInput();
input.BeginTime = dtStart.Value.Date;
input.EndTime = dtEnd.Value.Date.AddDays(1);
input.PatZhuyuanId = tbZhuyuanID.Text.Trim();
input.PatDpm = cmbKeshi.Text;
input.PatName = tbPatName.Text.Trim();
DataTable dt = dal.GetConsumablesUseList(input);
this.dgvUseList.DataSource = dt;
var rows = dt.Select(" isCost=1");
double sum = 0.0;
foreach (var dr in rows)
{
sum += Convert.ToDouble(dr["Dosage"].ToString()) * Convert.ToDouble(dr["Price"].ToString());
}
tbSum.Text = String.Format("{0:F}", sum);
}
public DataTable GetConsumablesUseList(ConsumablesUseGetInput input)
{
string str = @"select c.Name,c.Dosage,c.Unit,c.IsCost,c.Price,c.UseTime ,b.PatID,b.Patname,b.PatZhuYuanID
from Mzjld_ConsumablesUse c
inner join Adims_OTypesetting b on c.patid=b.patid
inner join Adims_Mzjld a on c.MzjldID=a.Id
where a.Otime between '{0}' and '{1}' ";
string sql = string.Format(str, input.BeginTime, input.EndTime);
if (!string.IsNullOrEmpty(input.PatZhuyuanId))
{
sql += string.Format("and ( b.PatZhuyuanId ='{0}')", input.PatZhuyuanId);
}
if (!string.IsNullOrEmpty(input.PatDpm))
{
sql += string.Format("and ( b.PatDpm ='{0}')", input.PatDpm);
}
if (!string.IsNullOrEmpty(input.PatName))
{
sql += string.Format("and ( b.PatName ='{0}')", input.PatName);
}
return(dBConn.GetDataTable(sql));
}
