private void dataGrid1_CurrentCellChanged(object sender, EventArgs e)
{
d = new LibDuoc.AccessData();
string mmyy = txtThang.Value.ToString().PadLeft(2, '0') + txtNam.Value.ToString().Substring(2);
if (d.bMmyy(mmyy))
{
string user = d.user;
string d_user = user + mmyy;
//string file = "d_dutrull";
string sql = "";
switch (dataGrid1[dataGrid1.CurrentRowIndex, 1].ToString())
{
case "2": sql = "select distinct to_char(a.ngay,'dd/mm/yyyy') as ngay,b.ten as tenphieu from xxx.d_xuatsdll a," + user + ".d_loaiphieu b,xxx.d_bucstt c where a.id=c.id and a.phieu=b.id and a.idduyet in(select id from " + d_user + ".d_xtutrucll where idduyet in(select id from " + d_user + ".d_duyet where id =" + dataGrid1[dataGrid1.CurrentRowIndex, 0].ToString() + "))";
break;
case "3": sql = "select distinct to_char(a.ngay,'dd/mm/yyyy') as ngay,b.ten as tenphieu from xxx.d_xuatsdll a," + user + ".d_loaiphieu b where a.phieu=b.id and a.idduyet in(select id from " + d_user + ".d_hoantrall where idduyet in(select id from " + d_user + ".d_duyet where id =" + dataGrid1[dataGrid1.CurrentRowIndex, 0].ToString() + "))";
break;
case "4": sql = "select distinct to_char(a.ngay,'dd/mm/yyyy') as ngay,b.ten as tenphieu from xxx.d_xuatsdll a," + user + ".d_loaiphieu b where a.phieu=b.id and a.idduyet in(select id from " + d_user + ".d_haophill where idduyet in(select id from " + d_user + ".d_duyet where id =" + dataGrid1[dataGrid1.CurrentRowIndex, 0].ToString() + "))";
break;
default: sql = "select distinct to_char(a.ngay,'dd/mm/yyyy') as ngay,b.ten as tenphieu from xxx.d_xuatsdll a," + user + ".d_loaiphieu b where a.phieu=b.id and a.idduyet in(select id from " + d_user + ".d_dutrull where idduyet in(select id from " + d_user + ".d_duyet where id =" + dataGrid1[dataGrid1.CurrentRowIndex, 0].ToString() + "))";
break;
}
;
dslinh = d.get_data_mmyy(sql, txtTungay.Text, txtDenngay.Text, true);
dataGrid2.DataSource = dslinh.Tables[0];
}
}
private void butXem_Click(object sender, EventArgs e)
{
d = new LibDuoc.AccessData();
string mmyy = txtThang.Value.ToString().PadLeft(2, '0') + txtNam.Value.ToString().Substring(2);
if (d.bMmyy(mmyy))
{
string user = d.user;
string xxx = user + mmyy;
string exp = " where a.makp=" + cboKhoa.SelectedValue.ToString() + " and to_date(to_char(a.ngay,'dd/mm/yyyy'),'dd/mm/yyyy') between to_date('" + txtTungay.Text + "','dd/mm/yyyy') and to_date('" + txtDenngay.Text + "','dd/mm/yyyy') ";
string sql = "select distinct a.id,to_char(a.ngay,'dd/mm/yyyy') as ngay,b.ten as loaiphieu,c.ten as tenphieu,";
sql += " case when a.done=0 then 'x' else '' end as chuachuyen,";
sql += " case when a.done=1 then 'x' else '' end as chuyen,";
sql += " case when a.done=2 then 'x' else '' end as duyet,a.loai,d.hoten ";
sql += " from " + xxx + ".d_duyet a inner join " + xxx + ".d_dutrull a1 on a.id=a1.idduyet inner join " + user + ".d_dmphieu b on a.loai=b.id ";
sql += " inner join " + user + ".d_loaiphieu c on a.phieu=c.id ";
sql += " left join " + user + ".dlogin d on a.userid=d.id ";
sql += exp;
sql += " union all ";
sql += "select distinct a.id,to_char(a.ngay,'dd/mm/yyyy') as ngay,b.ten as loaiphieu,c.ten as tenphieu,";
sql += " case when a.done=0 then 'x' else '' end as chuachuyen,";
sql += " case when a.done=1 then 'x' else '' end as chuyen,";
sql += " case when a.done=2 then 'x' else '' end as duyet,a.loai,d.hoten ";
sql += " from " + xxx + ".d_duyet a inner join " + xxx + ".d_xtutrucll a1 on a.id=a1.idduyet inner join " + user + ".d_dmphieu b on a.loai=b.id ";
sql += " inner join " + user + ".d_loaiphieu c on a.phieu=c.id ";
sql += " left join " + user + ".dlogin d on a.userid=d.id ";
sql += exp;
sql += " union all ";
sql += "select distinct a.id,to_char(a.ngay,'dd/mm/yyyy') as ngay,b.ten as loaiphieu,c.ten as tenphieu,";
sql += " case when a.done=0 then 'x' else '' end as chuachuyen,";
sql += " case when a.done=1 then 'x' else '' end as chuyen,";
sql += " case when a.done=2 then 'x' else '' end as duyet,a.loai,d.hoten ";
sql += " from " + xxx + ".d_duyet a inner join " + xxx + ".d_hoantrall a1 on a.id=a1.idduyet inner join " + user + ".d_dmphieu b on a.loai=b.id ";
sql += " inner join " + user + ".d_loaiphieu c on a.phieu=c.id ";
sql += " left join " + user + ".dlogin d on a.userid=d.id ";
sql += exp;
sql += " union all ";
sql += "select distinct a.id,to_char(a.ngay,'dd/mm/yyyy') as ngay,b.ten as loaiphieu,c.ten as tenphieu,";
sql += " case when a.done=0 then 'x' else '' end as chuachuyen,";
sql += " case when a.done=1 then 'x' else '' end as chuyen,";
sql += " case when a.done=2 then 'x' else '' end as duyet,a.loai,d.hoten ";
sql += " from " + xxx + ".d_duyet a inner join " + xxx + ".d_haophill a1 on a.id=a1.idduyet inner join " + user + ".d_dmphieu b on a.loai=b.id ";
sql += " inner join " + user + ".d_loaiphieu c on a.phieu=c.id ";
sql += " left join " + user + ".dlogin d on a.userid=d.id ";
sql += exp;
dsdutru = d.get_data(sql);
dataGrid1.DataSource = dsdutru.Tables[0];
}
}
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);
}
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);
}
