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