private bool kiemtra() { s_kho = ""; s_makp = ""; s_loaint = ""; s_tenkho = ""; s_tenkp = ""; s_tenloai = ""; if (kho.CheckedItems.Count == 0) { for (int i = 0; i < kho.Items.Count; i++) { kho.SetItemCheckState(i, CheckState.Checked); } } for (int i = 0; i < kho.Items.Count; i++) { if (kho.GetItemChecked(i)) { s_kho += dtdmkho.Rows[i]["id"].ToString() + ","; s_tenkho += dtdmkho.Rows[i]["ten"].ToString().Trim() + ","; } } for (int i = 0; i < makp.Items.Count; i++) { if (makp.GetItemChecked(i)) { s_makp += dtkp.Rows[i]["id"].ToString() + ","; s_tenkp += dtkp.Rows[i]["ten"].ToString().Trim() + ","; } } for (int i = 0; i < loai.Items.Count; i++) { if (loai.GetItemChecked(i)) { s_loaint += dtloai.Rows[i]["id"].ToString() + ","; s_tenloai += dtloai.Rows[i]["ten"].ToString().Trim() + ","; } } DateTime dt1 = d.StringToDate(tu.Text).AddDays(-d.iNgaykiemke); DateTime dt2 = d.StringToDate(den.Text).AddDays(d.iNgaykiemke); int y1 = dt1.Year, m1 = dt1.Month; int y2 = dt2.Year, m2 = dt2.Month; int itu, iden; string mmyy = ""; ds.Clear(); for (int i = y1; i <= y2; i++) { itu = (i == y1)?m1:1; iden = (i == y2)?m2:12; for (int j = itu; j <= iden; j++) { mmyy = j.ToString().PadLeft(2, '0') + i.ToString().Substring(2, 2); if (d.bMmyy(mmyy)) { get_xuat(mmyy); } } } if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show(lan.Change_language_MessageText("Không có số liệu !"), d.Msg); return(false); } return(true); }
void chuyen_bieu06(string tungay, string denngay) { DateTime dt1 = m.StringToDate(tungay).AddDays(-m.iNgaykiemke); DateTime dt2 = m.StringToDate(denngay).AddDays(m.iNgaykiemke); string sql = "", strInsert = "insert into " + user + ".bieu_06(id,ma,ngay,userid,c01,c02) select to_number(replace(ngay,'/','')) as id,ma,"; strInsert += " to_date(ngay,'dd/mm/yyyy') as ngay,-1 as userid,sum(c01) as c01,sum(c02) as c02 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 0 id,e.ma_medisoft as ma,to_char(a.ngay,'dd/mm/yyyy') as ngay,sum(b.solan) as c01,sum(decode(a.loaibn,1,solan,0)) as c02 "; sql += "select 0 id,e.ma_medisoft as ma,to_char(a.ngay,'dd/mm/yyyy') as ngay,sum(b.solan) as c01,sum((case a.loaibn=1 then solan else 0 end)) as c02 "; sql += "from " + usermmyy + ".cdha_bnll a inner join " + usermmyy + ".cdha_bnct b on a.id=b.id inner join "; sql += user + ".cdha_kythuat d on b.makt=d.id inner join " + user + ".v_giavp c on d.idvp=c.id inner join " + user + ".anhxa e on c.id_loai=e.ma_benhvien "; 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 e.loai='bieu_06' group by e.ma_medisoft,to_char(a.ngay,'dd/mm/yyyy')"; sql += " union all "; //sql += "select distinct a.id,e.ma_medisoft as ma,to_char(a.ngay,'dd/mm/yyyy') as ngay,d.tieuban as c01,decode(a.loaibn,1,d.tieuban,0) as c02 "; sql += "select distinct a.id,e.ma_medisoft as ma,to_char(a.ngay,'dd/mm/yyyy') as ngay,d.tieuban as c01,(case when a.loaibn=1 then d.tieuban else 0 end) as c02 "; sql += "from " + usermmyy + ".xn_phieu a inner join " + usermmyy + ".xn_ketqua b on a.id=b.id inner join " + user + ".xn_bv_chitiet e on b.id_ten=e.id inner join "; sql += user + ".xn_bv_ten d on e.id_bv_ten=d.id inner join " + user + ".v_giavp c on d.id_vienphi=c.id inner join " + user + ".anhxa e on c.id_loai=e.ma_benhvien "; 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 e.loai='bieu_06'"; } dt1 = dt1.AddMonths(1); } if (sql == "") { return; } strInsert += sql + ") group by to_number(replace(ngay,'/','')),ma,to_date(ngay,'dd/mm/yyyy')"; m.execute_data("delete from " + user + ".bieu_06 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"); m.execute_data(strInsert); }