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); } }