void chuyen_bieu101(string tungay, string denngay)
{
string mavp = "", mabhyt = "", matongcong = "", matongthu = "";
foreach (DataRow r in m.get_data("select a.ma,b.ma_benhvien from " + user + ".dm_101 a inner join " + user + ".anhxa b on a.ma=b.ma_medisoft where b.loai='bieu_101' and ma_benhvien in(0,1,2,3)").Tables[0].Rows)
{
if (r["ma_benhvien"].ToString() == "1")
{
mavp = r["ma"].ToString().Trim();
}
else if (r["ma_benhvien"].ToString() == "3")
{
matongcong = r["ma"].ToString().Trim();
}
else if (r["ma_benhvien"].ToString() == "0")
{
matongthu = r["ma"].ToString().Trim();
}
else
{
mabhyt = r["ma"].ToString().Trim();
}
if (mavp != "" && mabhyt != "" && matongcong != "" && matongthu != "")
{
break;
}
}
if (mavp == "" && mabhyt == "" && matongcong == "" && matongthu == "")
{
MessageBox.Show(lan.Change_language_MessageText("Thông tin khai báo chưa chính xác."));
Cursor = Cursors.Default;
return;
}
DateTime dt1 = m.StringToDate(tungay).AddDays(-m.iNgaykiemke);
DateTime dt2 = m.StringToDate(denngay).AddDays(m.iNgaykiemke);
string sql = "", strInsert = "insert into " + user + ".bieu_101(id,ma,ngay,sotien,userid)";
strInsert = " select to_number(replace(ngay,'/','')) as id," + mavp + " as ma,ngay,sum(sotien)/1000 as sotien,-1 as userid from ( ";
while (DateTime.Compare(dt1, dt2.AddMonths(1)) < 0)
{
string mmyy = dt1.Month.ToString().PadLeft(2, '0') + dt1.Year.ToString().Substring(2);
if (m.bMmyy(mmyy))
{
string usermmyy = user + mmyy;
sql += sql == "" ? "" : " union all ";
sql += "select to_char(a.ngay,'dd/mm/yyyy') as ngay,sum(b.soluong*round(b.dongia,2)) sotien";
sql += " from " + usermmyy + ".v_vienphill a inner join " + usermmyy + ".v_vienphict b on a.id=b.id inner join ";
sql += "(select id from " + user + ".v_giavp union all select id from " + user + ".d_dmbd) c on b.mavp=c.id inner join " + user + ".doituong d on b.madoituong=d.madoituong ";
sql += " where to_date(to_char(a.ngay,'dd/mm/yyyy'),'dd/mm/yyyy') between to_date('" + tungay + "','dd/mm/yyyy') and to_date('" + denngay + "','dd/mm/yyyy')";
sql += " and d.mien=0 and b.madoituong<>5 group by to_char(a.ngay,'dd/mm/yyyy')";
sql += " union all ";
sql += "select to_char(a.ngay,'dd/mm/yyyy') as ngay,sum(b.soluong*round(b.dongia,2)) sotien";
sql += " from " + usermmyy + ".v_ttrvll a inner join " + usermmyy + ".v_ttrvct b on a.id=b.id inner join ";
sql += "(select id from " + user + ".v_giavp union all select id from " + user + ".d_dmbd) c on b.mavp=c.id inner join " + user + ".doituong d on b.madoituong=d.madoituong ";
sql += " where to_date(to_char(a.ngay,'dd/mm/yyyy'),'dd/mm/yyyy') between to_date('" + tungay + "','dd/mm/yyyy') and to_date('" + denngay + "','dd/mm/yyyy')";
sql += " and d.mien=0 and b.madoituong<>5 group by to_char(a.ngay,'dd/mm/yyyy')";
}
dt1 = dt1.AddMonths(1);
}
if (sql == "")
{
return;
}
strInsert += sql + ") group by to_number(replace(ngay,'/','')),ngay";
m.execute_data("delete from " + user + ".bieu_101 where to_date(to_char(ngay,'dd/mm/yyyy'),'dd/mm/yyyy') between to_date('" + tungay + "','dd/mm/yyyy') and to_date('" + denngay + "','dd/mm/yyyy') and userid=-1");
foreach (DataRow r in m.get_data(strInsert).Tables[0].Rows)
{
m.upd_bieu101(decimal.Parse(r["id"].ToString()), int.Parse(mavp), r["ngay"].ToString(), decimal.Parse(r["sotien"].ToString()), -1);
m.upd_bieu101(decimal.Parse(r["id"].ToString()), int.Parse(matongcong), r["ngay"].ToString(), decimal.Parse(r["sotien"].ToString()), -1);
m.upd_bieu101(decimal.Parse(r["id"].ToString()), int.Parse(matongthu), r["ngay"].ToString(), decimal.Parse(r["sotien"].ToString()), -1);
}
//bhyt
dt1 = m.StringToDate(tungay).AddDays(-m.iNgaykiemke);
dt2 = m.StringToDate(denngay).AddDays(m.iNgaykiemke);
sql = "";
strInsert = "select to_number(replace(ngay,'/','')) as id," + mavp + " as ma,ngay,sum(sotien)/1000 as sotien,sum(bhyt)/1000 as bhyt,-1 as userid from ( ";
while (DateTime.Compare(dt1, dt2.AddMonths(1)) < 0)
{
string mmyy = dt1.Month.ToString().PadLeft(2, '0') + dt1.Year.ToString().Substring(2);
if (m.bMmyy(mmyy))
{
string usermmyy = user + mmyy;
sql += sql == "" ? "" : " union all ";
sql += "select to_char(a.ngay,'dd/mm/yyyy') as ngay,sum(b.soluong*round(b.dongia,2)) as sotien,sum(bhyttra) as bhyt";
sql += " from " + usermmyy + ".v_ttrvll a inner join " + usermmyy + ".v_ttrvct b on a.id=b.id inner join ";
sql += "(select id from " + user + ".v_giavp union all select id from " + user + ".d_dmbd) c on b.mavp=c.id inner join " + user + ".doituong d on b.madoituong=d.madoituong ";
sql += " inner join " + usermmyy + ".v_ttrvbhyt f on a.id=f.id where to_date(to_char(a.ngay,'dd/mm/yyyy'),'dd/mm/yyyy') between to_date('" + tungay + "','dd/mm/yyyy') and to_date('" + denngay + "','dd/mm/yyyy')";
sql += " and b.madoituong=1 group by to_char(a.ngay,'dd/mm/yyyy')";
}
dt1 = dt1.AddMonths(1);
}
if (sql == "")
{
return;
}
strInsert += sql + ") group by to_number(replace(ngay,'/','')),ngay";
foreach (DataRow r in m.get_data(strInsert).Tables[0].Rows)
{
decimal dSotien = decimal.Parse(r["sotien"].ToString());
decimal dBhyt = decimal.Parse(r["bhyt"].ToString());
decimal dvp = dSotien - dBhyt;
m.upd_bieu101(decimal.Parse(r["id"].ToString()), int.Parse(mabhyt), r["ngay"].ToString(), dBhyt, -1);
m.upd_bieu101(decimal.Parse(r["id"].ToString()), int.Parse(mavp), r["ngay"].ToString(), dvp, -1);
m.upd_bieu101(decimal.Parse(r["id"].ToString()), int.Parse(matongcong), r["ngay"].ToString(), dSotien, -1);
m.upd_bieu101(decimal.Parse(r["id"].ToString()), int.Parse(matongthu), r["ngay"].ToString(), dSotien, -1);
}
}
