private void upd_tinhtrang_giuong()
{
//Kiem tra trang thai giuong
m.execute_data("update " + s_user + ".dmgiuong set tinhtrang= 1,soluong = 1 where id in(select idgiuong from " + s_user + ".datgiuong where den is null)");
m.execute_data("update " + s_user + ".dmgiuong set tinhtrang= 2,soluong = 1 where id in(select idgiuong from " + s_user + ".theodoigiuong where den is null)");
sql = "select a.idgiuong,to_char(den,'dd/mm/yyyy hh24:mi') ngayra,b.ma,b.idphong from " + s_user + ".theodoigiuong a,dmgiuong b,(select idgiuong from " + s_user + ".theodoigiuong where den is null union all select idgiuong from " + s_user + ".datgiuong where den is null) c where a.idgiuong=b.id and a.den is not null and a.idgiuong=c.idgiuong(+) and c.idgiuong is null and instr(b.ma,'TG')>0 and to_date(a.den,'dd/mm/yy') between to_date('" + s_tungay + "','dd/mm/yy') and to_date('" + s_denngay + "','dd/mm/yy')";
foreach (DataRow row in m.get_data(sql).Tables[0].Rows)
{
int idex1 = row["ma"].ToString().IndexOf("TG") + 2;
int idex2 = row["ma"].ToString().IndexOf("/");
string s_stt = row["ma"].ToString().Substring(idex1, idex2 - idex1) + "," + row["ma"].ToString().Substring(idex2 + 1);
m.execute_data("update " + s_user + ".dmgiuong set tinhtrang=0,soluong=0 where tinhtrang<>3 and id=" + int.Parse(row["idgiuong"].ToString()));
foreach (DataRow row1 in m.get_data("select * from " + s_user + ".dmgiuong where idphong=" + int.Parse(row["idphong"].ToString()) + " and stt in(" + s_stt + ")").Tables[0].Rows)
{
m.execute_data("update " + s_user + ".dmgiuong set tinhtrang=0,soluong=0 where tinhtrang<>3 and id=" + int.Parse(row1["id"].ToString()) + " and id not in(select idgiuong from " + s_user + ".theodoigiuong where den is null union all select idgiuong from " + s_user + ".datgiuong where den is null)");
}
}
sql = "select a.idgiuong,to_char(a.den,'dd/mm/yyyy hh24:mi') ngayra,b.ma,b.idphong from " + s_user + ".xuatvien xv, " + s_user + ".theodoigiuong a, " + s_user + ".dmgiuong b,(select idgiuong from " + s_user + ".theodoigiuong where den is null union all select idgiuong from " + s_user + ".datgiuong where den is null) c where xv.maql=a.maql and a.idgiuong=b.id and a.den is not null and a.idgiuong=c.idgiuong(+) and c.idgiuong is null and to_char(a.den,'dd/mm/yyyy')='" + m.ngayhienhanh_server.Substring(0, 10) + "'";
foreach (DataRow row in m.get_data(sql).Tables[0].Rows)
{
string file = "";
if (m.bNgaygio(row["ngayra"].ToString(), m.ngayhienhanh_server))
{
m.execute_data("update " + s_user + ".dmgiuong set tinhtrang=2 where tinhtrang<>3 and id=" + int.Parse(row["idgiuong"].ToString()));
file = "tinhtrang=2";
}
else
{
m.execute_data("update " + s_user + ".dmgiuong set tinhtrang=0,soluong=0 where tinhtrang<>3 and id=" + int.Parse(row["idgiuong"].ToString()) + " and idphong not in(select idphong from " + s_user + ".dmgiuong where idphong=" + int.Parse(row["idphong"].ToString()) + " and instr(ma,'TG')>0 and tinhtrang<>3)");
file = "tinhtrang=0,soluong=0";
}
if (row["ma"].ToString().IndexOf("TG") != -1)
{
int idex1 = row["ma"].ToString().IndexOf("TG") + 2;
int idex2 = row["ma"].ToString().IndexOf("/");
string s_stt = row["ma"].ToString().Substring(idex1, idex2 - idex1) + "," + row["ma"].ToString().Substring(idex2 + 1);
m.execute_data("update " + s_user + ".dmgiuong set " + file + " where tinhtrang<>3 and idphong=" + int.Parse(row["idphong"].ToString()) + " and stt in(" + s_stt + ") and id not in(select idgiuong from " + s_user + ".datgiuong where den is null union all select idgiuong from " + s_user + ".theodoigiuong where den is null)");
}
}
sql = "select a.*,b.ma,b.idphong from " + s_user + ".theodoigiuong a, " + s_user + ".dmgiuong b where a.idgiuong=b.id and a.den is null and instr(b.ma,'TG')>0";
foreach (DataRow row in m.get_data(sql).Tables[0].Rows)
{
int idex1 = row["ma"].ToString().IndexOf("TG") + 2;
int idex2 = row["ma"].ToString().IndexOf("/");
string s_stt = row["ma"].ToString().Substring(idex1, idex2 - idex1) + "," + row["ma"].ToString().Substring(idex2 + 1);
m.execute_data("update " + s_user + ".dmgiuong set tinhtrang=2 where tinhtrang<>3 and idphong=" + int.Parse(row["idphong"].ToString()) + " and stt in(" + s_stt + ")");
}
sql = "select a.*,b.ma,b.idphong from " + s_user + ".datgiuong a, " + s_user + ".dmgiuong b where a.idgiuong=b.id and a.den is null and instr(b.ma,'TG')>0";
foreach (DataRow row in m.get_data(sql).Tables[0].Rows)
{
int idex1 = row["ma"].ToString().IndexOf("TG") + 2;
int idex2 = row["ma"].ToString().IndexOf("/");
string s_stt = row["ma"].ToString().Substring(idex1, idex2 - idex1) + "," + row["ma"].ToString().Substring(idex2 + 1);
m.execute_data("update " + s_user + ".dmgiuong set tinhtrang=1 where tinhtrang<>3 and idphong=" + int.Parse(row["idphong"].ToString()) + " and stt in(" + s_stt + ")");
}
m.execute_data("update " + s_user + ".dmgiuong set ghichu='' where tinhtrang=0");
//End kiem tra
}