private void simpleButton1_Click(object sender, EventArgs e)
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
try
{
if (String.IsNullOrEmpty(textEdit_tenDangNhap.Text) || String.IsNullOrEmpty(textEdit_matKhau.Text))
{
DevExpress.XtraEditors.XtraMessageBox.Show("Vui Lòng Nhập Đầy Đủ Thông Tin !");
return;
}
SQLConnect sql = (SQLConnect)SQL_con_object;
string s = "SELECT * FROM Taikhoan WHERE (TenDangNhap='" + textEdit_tenDangNhap.Text + "') AND (MatKhau='" + sql.StringToMD5(textEdit_matKhau.Text) + "')";
if (String.IsNullOrEmpty(sql.GetData(s, 0)))
{
DevExpress.XtraEditors.XtraMessageBox.Show("Username và Password không đúng !", "Cảnh Báo");
return;
}
if (checkEdit_luu.Checked)
{
Registry.SetValue(RegistryDocMIS, "Login_Check", "true");
Registry.SetValue(RegistryDocMIS, "Login_Username", textEdit_tenDangNhap.Text);
Registry.SetValue(RegistryDocMIS, "Login_Password", textEdit_matKhau.Text);
}
else
{
Registry.SetValue(RegistryDocMIS, "Login_Check", "false");
}
NguoiDung person = new NguoiDung(sql.GetData(s, 0), sql.GetData(s, 1), sql.GetData(s, 2), sql.GetData(s, 3), sql.GetData(s, 4), sql.GetData(s, 5), sql.GetData(s, 6), int.Parse(sql.GetData(s, 7)), sql.GetData(s, 8));
person_object = (NguoiDung)person;
s = "INSERT INTO Taikhoan_Lichsu VALUES (" + person.MaID + ",'" + DateTime.Now + "',NULL,N'" + sql.GetLocalIPAddress() + "',0)";
sql.ExecuteData(s);
form_Main = new F_Main(this);
form_Main.Show();
khay_HeThong.Visible = true;
khay_HeThong.ShowBalloonTip(10);
this.Hide();
}
catch
{
DevExpress.XtraEditors.XtraMessageBox.Show("Bạn chưa kết nối CSDL !", "Cảnh Báo");
form_DangNhap_CauHinh = new F_DangNhap_CauHinh();
form_DangNhap_CauHinh.Show();
}
Cursor = Cursors.Default;
}
private void toolStripMenuItem_Select_Click(object sender, EventArgs e)
{
try
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
int rowSel = -1;
int[] selectedRows = gridView1.GetSelectedRows();
foreach (int r in selectedRows)
{
rowSel = r;
}
string s = "SELECT [MaTochuc] ,[TenTochuc] ,[Diachi] ,[Masothue] ,[Giamdoc] ,convert(NVARCHAR, NgayTL, 103) as NgayTL FROM [GIAODAT].[dbo].[TenTochuc] WHERE MaTochuc='" + table_DSTenToChuc.Rows[rowSel].ItemArray[1].ToString() + "' ";
label_MTC.Text = sql.GetData(s, 0);
label_TTC.Text = sql.GetData(s, 1);
label_DiaChi.Text = sql.GetData(s, 2);
label_MST.Text = sql.GetData(s, 3);
label_GD.Text = sql.GetData(s, 4);
label_NTL.Text = sql.GetData(s, 5);
DataColumn[] Array_col2 = { c0, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11 };
string s1 = "SELECT ROW_NUMBER() OVER(ORDER BY Giaodat.TenDuan asc) AS Row#,Giaodat.TenDuan, Giaodat.Diadiem, DMDvhc.TenDvhc, a.TongDTTruoc, b.TongDTDuoc, Giaodat.SoQD, convert(NVARCHAR, Giaodat.NgayQD, 103) as NgayQD, convert(NVARCHAR, Giaodat.Ngaygiao, 103) as NgayGiao, convert(NVARCHAR, Giaodat.Thoihan, 103) as ThoiHan, DMHinhthucsudung.TenHinhthuc, Giaodat.TinhtrangPL FROM Giaodat, DMDvhc, DMHinhthucsudung,( SELECT LDTruocGiao.IDGD, SUM(Dientich) as TongDTTruoc FROM LDTruocGiao, Giaodat WHERE Giaodat.IDGD = LDTruocGiao.IDGD GROUP BY LDTruocGiao.IDGD ) a, (SELECT LDDuocGiao.IDGD, SUM(Dientich) as TongDTDuoc FROM LDDuocGiao, Giaodat WHERE Giaodat.IDGD = LDDuocGiao.IDGD GROUP BY LDDuocGiao.IDGD) b WHERE Giaodat.MaTochuc = '" + table_DSTenToChuc.Rows[rowSel].ItemArray[1].ToString() + "' and Giaodat.MaDvhc = DMDvhc.MaDvhc and Giaodat.MaHinhthuc = DMHinhthucsudung.MaHinhthuc and Giaodat.IDGD = a.IDGD and Giaodat.IDGD = b.IDGD GROUP BY Giaodat.TenDuan, Giaodat.Diadiem, a.TongDTTruoc, b.TongDTDuoc, DMDvhc.TenDvhc, Giaodat.SoQD, Giaodat.NgayQD, Giaodat.Ngaygiao, Giaodat.Thoihan, DMHinhthucsudung.TenHinhthuc, Giaodat.TinhtrangPL";
table_DSDuAn.Clear();
sql.ViewDataTable(s1, table_DSDuAn, Array_col2);
Cursor = Cursors.Default;
}
catch { }
}
private void F_Main_DMHuyen_Load(object sender, EventArgs e)
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
sql = F_DangNhap.SQL_con_object as SQLConnect;
nguoi = F_DangNhap.person_object as NguoiDung;
//Gridcontrol2 DSLoaiDat
table_DSHuyen = new DataTable();
co0 = new DataColumn();
co0.DataType = System.Type.GetType("System.Int32");
co0.ColumnName = "STT";
table_DSHuyen.Columns.Add(co0);
co1 = new DataColumn();
co1.DataType = System.Type.GetType("System.String");
co1.ColumnName = "Mã đơn vị Huyện";
table_DSHuyen.Columns.Add(co1);
co2 = new DataColumn();
co2.DataType = System.Type.GetType("System.String");
co2.ColumnName = "Tên đơn vị Huyện";
table_DSHuyen.Columns.Add(co2);
gridControl1.Width = int.MaxValue;
gridControl1.DataSource = table_DSHuyen;
gridView1.OptionsBehavior.Editable = false;
//gridView1.OptionsView.ColumnAutoWidth = false;
gridView1.ScrollStyle = DevExpress.XtraGrid.Views.Grid.ScrollStyleFlags.LiveHorzScroll;
gridView1.HorzScrollVisibility = DevExpress.XtraGrid.Views.Base.ScrollVisibility.Always;
gridView1.Columns[0].Width = 5;
gridView1.Columns[1].Width = 70;
gridView1.Columns[2].Width = 300;
//Chèn dữ liệu vào gridcontrol
string s1 = "SELECT COUNT(ID) as SoHuyen FROM [GIAODAT].[dbo].[DMDvhc]";
groupControl1.Text = "Danh sách đơn vị Huyện | Tổng số Huyện : " + sql.GetData(s1, 0);
DataColumn[] Array_col2 = { co0, co1, co2 };
s1 = "SELECT ROW_NUMBER() OVER(ORDER BY [TenDvhc] ASC) AS Row#,[MaDvhc],[TenDvhc] FROM [GIAODAT].[dbo].[DMDvhc]";
table_DSHuyen.Clear();
sql.ViewDataTable(s1, table_DSHuyen, Array_col2);
Cursor = Cursors.Default;
}
private void simpleButton_OK_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(textEdit_Username.Text) || String.IsNullOrEmpty(textEdit_Password.Text) || String.IsNullOrEmpty(textEdit_HT.Text) || String.IsNullOrEmpty(textEdit_DiaChi.Text))
{
DevExpress.XtraEditors.XtraMessageBox.Show("Vui Lòng Nhập Đầy Đủ Thông Tin !");
return;
}
DialogResult re = DialogResult.No;
re = DevExpress.XtraEditors.XtraMessageBox.Show("Bạn thực sự muốn thêm '" + textEdit_Username.Text + "' ?", "Cảnh Báo", MessageBoxButtons.YesNo);
if (re == DialogResult.Yes)
{
try
{
SQLConnect sql = F_DangNhap.SQL_con_object as SQLConnect;
string s1 = "SELECT TenDangNhap FROM Taikhoan WHERE TenDangNhap='" + textEdit_Username.Text + "'";
if (!String.IsNullOrEmpty(sql.GetData(s1, 0)))
{
DevExpress.XtraEditors.XtraMessageBox.Show("Tên đăng nhập đã tồn tại !", "Cảnh Báo");
return;
}
string user = textEdit_Username.Text;
string pass = textEdit_Password.Text;
string HoTen = textEdit_HT.Text;
string NgaySinh = dateEdit_NS.DateTime.Month + "/" + dateEdit_NS.DateTime.Day + "/" + dateEdit_NS.DateTime.Year;
string DiaChi = textEdit_DiaChi.Text;
string gtinh = "Nam";
if (radioGroup_GT.SelectedIndex.ToString().Equals("0"))
{
gtinh = "Nam";
}
else
{
gtinh = "Nữ";
}
int quyen = radioGroup_CapQuyen.SelectedIndex;
string s2 = "INSERT INTO Taikhoan(TenDangNhap, MatKhau, HoTen, NgaySinh, QueQuan, GioiTinh, Cap, NgayTao) VALUES (N'" + user + "',N'" + sql.StringToMD5(pass) + "',N'" + HoTen + "',N'" + NgaySinh + "',N'" + DiaChi + "',N'" + gtinh + "'," + quyen + ",'" + DateTime.Now + "');";
sql.ExecuteData(s2);
DevExpress.XtraEditors.XtraMessageBox.Show("Thêm thành công !");
this.Close();
}
catch { }
}
}
private void toolStripMenuItem1_Refesh_Click(object sender, EventArgs e)
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
table_DSHinhThucSuDung.Clear();
table_DSToChuc.Clear();
table_DSDuAn.Clear();
groupControl3.Text = "Danh sách tổ chức";
groupControl4.Text = "Danh sách dự án";
DataColumn[] Array_col1 = { col0, col1, col2, col3, col4 };
string s = "SELECT ROW_NUMBER() OVER(ORDER BY t1.MaHinhthuc asc) AS Row#, t1.MaHinhthuc, t1.TenHinhthuc, ISNULL(t2.SoToChuc, 0 ) as SoToChuc, ISNULL(t2.SoDuAn, 0 ) as SoDuAn FROM (SELECT MaHinhthuc,TenHinhthuc FROM DMHinhthucsudung) t1 LEFT JOIN (SELECT a.MaHinhthuc, a.SoDuAn , b.SoToChuc FROM (SELECT MaHinhthuc, COUNT(IDGD) as SoDuAn FROM Giaodat GROUP BY MaHinhthuc) a, (SELECT a.MaHinhthuc, COUNT(a.MaHinhthuc) as SoToChuc FROM (SELECT MaHinhthuc FROM Giaodat GROUP BY MaHinhthuc, MaTochuc) a GROUP BY a.MaHinhthuc) b WHERE a.MaHinhthuc=b.MaHinhthuc) t2 ON t1.MaHinhthuc = t2.MaHinhthuc";
sql.ViewDataTable(s, table_DSHinhThucSuDung, Array_col1);
string TCThueDat = "SELECT a.TongSoToChuc, b.TongDT FROM (SELECT SUM(a.SoToChuc) as TongSoToChuc FROM (SELECT a.MaHinhthuc, COUNT(a.MaHinhthuc) as SoToChuc FROM (SELECT MaHinhthuc FROM Giaodat WHERE MaHinhthuc = 'DT-KCN-THN' or MaHinhthuc = 'DT-KCN-TML' or MaHinhthuc = 'DT-THN' or MaHinhthuc = 'DT-TML' GROUP BY MaHinhthuc, MaTochuc) a GROUP BY a.MaHinhthuc) a) a, (SELECT SUM(b.TongDTTruoc) as TongDT FROM (SELECT LDTruocGiao.IDGD, SUM(Dientich) as TongDTTruoc FROM LDTruocGiao, Giaodat WHERE Giaodat.IDGD = LDTruocGiao.IDGD and (MaHinhthuc = 'DT-KCN-THN' or MaHinhthuc = 'DT-KCN-TML' or MaHinhthuc = 'DT-THN' or MaHinhthuc = 'DT-TML') GROUP BY LDTruocGiao.IDGD) b) b";
string TCGiaoDat = "SELECT a.TongSoToChuc, b.TongDT FROM (SELECT SUM(a.SoToChuc) as TongSoToChuc FROM (SELECT a.MaHinhthuc, COUNT(a.MaHinhthuc) as SoToChuc FROM (SELECT MaHinhthuc FROM Giaodat WHERE MaHinhthuc = 'DG-CTT' or MaHinhthuc = 'DG-KTT' or MaHinhthuc = 'DG-QL' or MaHinhthuc = 'CNQ-KTT' or MaHinhthuc = 'CNQ-CTT' GROUP BY MaHinhthuc, MaTochuc) a GROUP BY a.MaHinhthuc) a) a, (SELECT SUM(b.TongDTTruoc) as TongDT FROM (SELECT LDTruocGiao.IDGD, SUM(Dientich) as TongDTTruoc FROM LDTruocGiao, Giaodat WHERE Giaodat.IDGD = LDTruocGiao.IDGD and (MaHinhthuc = 'DG-CTT' or MaHinhthuc = 'DG-KTT' or MaHinhthuc = 'DG-QL' or MaHinhthuc = 'CNQ-KTT' or MaHinhthuc = 'CNQ-CTT') GROUP BY LDTruocGiao.IDGD) b) b";
groupControl2.Text = "Có " + sql.GetData(TCThueDat, 0) + " tổ chức thuê đất, tổng diện tích đất cho thuê là " + sql.GetData(TCThueDat, 1) + " m2 || Có " + sql.GetData(TCGiaoDat, 0) + " tổ chức được giao đất, tổng diện tích đất giao là " + sql.GetData(TCGiaoDat, 1) + " m2 ";
Cursor = Cursors.Default;
}
public F_Main_ThemToChuc(F_Main_TenToChuc frm, string ma, int so)
{
InitializeComponent();
sql = F_DangNhap.SQL_con_object as SQLConnect;
nguoi = F_DangNhap.person_object as NguoiDung;
this.formTenToChuc = frm;
this.maToChuc = ma;
this.soduan = so;
this.Text = "Chỉnh sửa thông tin tổ chức";
string s = "SELECT [MaTochuc] ,[TenTochuc] ,[Diachi] ,[Masothue] ,[Giamdoc] ,convert(NVARCHAR, NgayTL, 101) as NgayTL FROM [GIAODAT].[dbo].[TenTochuc] WHERE MaTochuc='" + ma + "' ";
textEdit_maToChuc.Text = sql.GetData(s, 0);
textEdit_TenToChuc.Text = sql.GetData(s, 1);
textEdit_DiaChi.Text = sql.GetData(s, 2);
textEdit_MST.Text = sql.GetData(s, 3);
textEdit_GiamDoc.Text = sql.GetData(s, 4);
dateEdit_NgayThanhLap.EditValue = sql.GetData(s, 5);
simpleButton_NhapLai.Enabled = false;
}
private void F_Main_HinhThucSuDung_Load(object sender, EventArgs e)
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
sql = F_DangNhap.SQL_con_object as SQLConnect;
nguoi = F_DangNhap.person_object as NguoiDung;
//GridControl DSHinhThucSuDung
table_DSHinhThucSuDung = new DataTable();
col0 = new DataColumn();
col0.DataType = System.Type.GetType("System.Int32");
col0.ColumnName = "STT";
table_DSHinhThucSuDung.Columns.Add(col0);
col1 = new DataColumn();
col1.DataType = System.Type.GetType("System.String");
col1.ColumnName = "Mã";
table_DSHinhThucSuDung.Columns.Add(col1);
col2 = new DataColumn();
col2.DataType = System.Type.GetType("System.String");
col2.ColumnName = "Hình thức sử dụng";
table_DSHinhThucSuDung.Columns.Add(col2);
col3 = new DataColumn();
col3.DataType = System.Type.GetType("System.Int32");
col3.ColumnName = "Số tổ chức";
table_DSHinhThucSuDung.Columns.Add(col3);
col4 = new DataColumn();
col4.DataType = System.Type.GetType("System.Int32");
col4.ColumnName = "Số dự án";
table_DSHinhThucSuDung.Columns.Add(col4);
gridControl1.DataSource = table_DSHinhThucSuDung;
gridView1.OptionsBehavior.Editable = false;
//gridView1.OptionsView.ColumnAutoWidth = false;
gridView1.Columns[0].Width = 25;
gridView1.Columns[1].Width = 80;
gridView1.Columns[2].Width = 300;
//gridView1.Columns[2].Width = 60;
//gridView1.Columns[3].Width = 50;
DataColumn[] Array_col1 = { col0, col1, col2, col3, col4 };
string s = "SELECT ROW_NUMBER() OVER(ORDER BY t1.MaHinhthuc asc) AS Row#, t1.MaHinhthuc, t1.TenHinhthuc, ISNULL(t2.SoToChuc, 0 ) as SoToChuc, ISNULL(t2.SoDuAn, 0 ) as SoDuAn FROM (SELECT MaHinhthuc,TenHinhthuc FROM DMHinhthucsudung) t1 LEFT JOIN (SELECT a.MaHinhthuc, a.SoDuAn , b.SoToChuc FROM (SELECT MaHinhthuc, COUNT(IDGD) as SoDuAn FROM Giaodat GROUP BY MaHinhthuc) a, (SELECT a.MaHinhthuc, COUNT(a.MaHinhthuc) as SoToChuc FROM (SELECT MaHinhthuc FROM Giaodat GROUP BY MaHinhthuc, MaTochuc) a GROUP BY a.MaHinhthuc) b WHERE a.MaHinhthuc=b.MaHinhthuc) t2 ON t1.MaHinhthuc = t2.MaHinhthuc";
sql.ViewDataTable(s, table_DSHinhThucSuDung, Array_col1);
string TCThueDat = "SELECT a.TongSoToChuc, b.TongDT FROM (SELECT SUM(a.SoToChuc) as TongSoToChuc FROM (SELECT a.MaHinhthuc, COUNT(a.MaHinhthuc) as SoToChuc FROM (SELECT MaHinhthuc FROM Giaodat WHERE MaHinhthuc = 'DT-KCN-THN' or MaHinhthuc = 'DT-KCN-TML' or MaHinhthuc = 'DT-THN' or MaHinhthuc = 'DT-TML' GROUP BY MaHinhthuc, MaTochuc) a GROUP BY a.MaHinhthuc) a) a, (SELECT SUM(b.TongDTTruoc) as TongDT FROM (SELECT LDTruocGiao.IDGD, SUM(Dientich) as TongDTTruoc FROM LDTruocGiao, Giaodat WHERE Giaodat.IDGD = LDTruocGiao.IDGD and (MaHinhthuc = 'DT-KCN-THN' or MaHinhthuc = 'DT-KCN-TML' or MaHinhthuc = 'DT-THN' or MaHinhthuc = 'DT-TML') GROUP BY LDTruocGiao.IDGD) b) b";
string TCGiaoDat = "SELECT a.TongSoToChuc, b.TongDT FROM (SELECT SUM(a.SoToChuc) as TongSoToChuc FROM (SELECT a.MaHinhthuc, COUNT(a.MaHinhthuc) as SoToChuc FROM (SELECT MaHinhthuc FROM Giaodat WHERE MaHinhthuc = 'DG-CTT' or MaHinhthuc = 'DG-KTT' or MaHinhthuc = 'DG-QL' or MaHinhthuc = 'CNQ-KTT' or MaHinhthuc = 'CNQ-CTT' GROUP BY MaHinhthuc, MaTochuc) a GROUP BY a.MaHinhthuc) a) a, (SELECT SUM(b.TongDTTruoc) as TongDT FROM (SELECT LDTruocGiao.IDGD, SUM(Dientich) as TongDTTruoc FROM LDTruocGiao, Giaodat WHERE Giaodat.IDGD = LDTruocGiao.IDGD and (MaHinhthuc = 'DG-CTT' or MaHinhthuc = 'DG-KTT' or MaHinhthuc = 'DG-QL' or MaHinhthuc = 'CNQ-KTT' or MaHinhthuc = 'CNQ-CTT') GROUP BY LDTruocGiao.IDGD) b) b";
groupControl2.Text = "Có " + sql.GetData(TCThueDat, 0) + " tổ chức thuê đất, tổng diện tích đất cho thuê là " + sql.GetData(TCThueDat, 1) + " m2 || Có " + sql.GetData(TCGiaoDat, 0) + " tổ chức được giao đất, tổng diện tích đất giao là " + sql.GetData(TCGiaoDat, 1) + " m2 ";
//Gridcontrol2 DSToChuc
table_DSToChuc = new DataTable();
co0 = new DataColumn();
co0.DataType = System.Type.GetType("System.Int32");
co0.ColumnName = "STT";
table_DSToChuc.Columns.Add(co0);
co1 = new DataColumn();
co1.DataType = System.Type.GetType("System.String");
co1.ColumnName = "Mã tổ chức";
table_DSToChuc.Columns.Add(co1);
co2 = new DataColumn();
co2.DataType = System.Type.GetType("System.String");
co2.ColumnName = "Tên tổ chức";
table_DSToChuc.Columns.Add(co2);
co3 = new DataColumn();
co3.DataType = System.Type.GetType("System.Int32");
co3.ColumnName = "Số dự án với hình thức sử dụng";
table_DSToChuc.Columns.Add(co3);
co4 = new DataColumn();
co4.DataType = System.Type.GetType("System.String");
co4.ColumnName = "Địa chỉ";
table_DSToChuc.Columns.Add(co4);
co5 = new DataColumn();
co5.DataType = System.Type.GetType("System.String");
co5.ColumnName = "Mã số thuế";
table_DSToChuc.Columns.Add(co5);
co6 = new DataColumn();
co6.DataType = System.Type.GetType("System.String");
co6.ColumnName = "Giám đốc";
table_DSToChuc.Columns.Add(co6);
co7 = new DataColumn();
co7.DataType = System.Type.GetType("System.String");
co7.ColumnName = "Ngày thành lập";
table_DSToChuc.Columns.Add(co7);
gridControl2.Width = int.MaxValue;
gridControl2.DataSource = table_DSToChuc;
gridView2.OptionsBehavior.Editable = false;
gridView2.OptionsView.ColumnAutoWidth = false;
gridView2.ScrollStyle = DevExpress.XtraGrid.Views.Grid.ScrollStyleFlags.LiveHorzScroll;
gridView2.HorzScrollVisibility = DevExpress.XtraGrid.Views.Base.ScrollVisibility.Always;
gridView2.Columns[0].Width = 25;
gridView2.Columns[1].Width = 70;
gridView2.Columns[2].Width = 300;
gridView2.Columns[3].Width = 170;
gridView2.Columns[4].Width = 350;
gridView2.Columns[5].Width = 100;
gridView2.Columns[6].Width = 100;
gridView2.Columns[7].Width = 100;
//GridControl3 DSDuAn
table_DSDuAn = new DataTable();
c0 = new DataColumn();
c0.DataType = System.Type.GetType("System.Int32");
c0.ColumnName = "STT";
table_DSDuAn.Columns.Add(c0);
c1 = new DataColumn();
c1.DataType = System.Type.GetType("System.String");
c1.ColumnName = "Tên dự án";
table_DSDuAn.Columns.Add(c1);
c2 = new DataColumn();
c2.DataType = System.Type.GetType("System.String");
c2.ColumnName = "Địa điểm";
table_DSDuAn.Columns.Add(c2);
c3 = new DataColumn();
c3.DataType = System.Type.GetType("System.String");
c3.ColumnName = "Huyện";
table_DSDuAn.Columns.Add(c3);
c4 = new DataColumn();
c4.DataType = System.Type.GetType("System.Double");
c4.ColumnName = "Diện tích trước giao (m2)";
table_DSDuAn.Columns.Add(c4);
c5 = new DataColumn();
c5.DataType = System.Type.GetType("System.Double");
c5.ColumnName = "Diện tích sau giao (m2)";
table_DSDuAn.Columns.Add(c5);
c6 = new DataColumn();
c6.DataType = System.Type.GetType("System.String");
c6.ColumnName = "Số quyết định";
table_DSDuAn.Columns.Add(c6);
c7 = new DataColumn();
c7.DataType = System.Type.GetType("System.String");
c7.ColumnName = "Ngày quyết định";
table_DSDuAn.Columns.Add(c7);
c8 = new DataColumn();
c8.DataType = System.Type.GetType("System.String");
c8.ColumnName = "Ngày giao";
table_DSDuAn.Columns.Add(c8);
c9 = new DataColumn();
c9.DataType = System.Type.GetType("System.String");
c9.ColumnName = "Thời hạn";
table_DSDuAn.Columns.Add(c9);
c10 = new DataColumn();
c10.DataType = System.Type.GetType("System.String");
c10.ColumnName = "Hình thức sử dụng";
table_DSDuAn.Columns.Add(c10);
c11 = new DataColumn();
c11.DataType = System.Type.GetType("System.String");
c11.ColumnName = "Tình trạng pháp lý";
table_DSDuAn.Columns.Add(c11);
gridControl3.Width = int.MaxValue;
gridControl3.DataSource = table_DSDuAn;
gridView3.OptionsBehavior.Editable = false;
gridView3.OptionsView.ColumnAutoWidth = false;
gridView3.ScrollStyle = DevExpress.XtraGrid.Views.Grid.ScrollStyleFlags.LiveHorzScroll;
gridView3.HorzScrollVisibility = DevExpress.XtraGrid.Views.Base.ScrollVisibility.Always;
gridView3.Columns[0].Width = 25;
gridView3.Columns[1].Width = 300;
gridView3.Columns[2].Width = 250;
gridView3.Columns[3].Width = 110;
gridView3.Columns[4].Width = 130;
gridView3.Columns[5].Width = 120;
gridView3.Columns[6].Width = 90;
gridView3.Columns[7].Width = 90;
gridView3.Columns[8].Width = 70;
gridView3.Columns[9].Width = 70;
gridView3.Columns[10].Width = 300;
gridView3.Columns[11].Width = 150;
Cursor = Cursors.Default;
}
private void toolStripMenuItem_Select_Click(object sender, EventArgs e)
{
try
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
int rowSel = -1;
int[] selectedRows = gridView1.GetSelectedRows();
foreach (int r in selectedRows)
{
rowSel = r;
}
string s = "SELECT Giaodat.IDGD, Giaodat.TenDuan, Giaodat.Diadiem, DMDvhc.TenDvhc, Giaodat.SoQD, convert(NVARCHAR, Giaodat.NgayQD, 103) as NgayQD, convert(NVARCHAR, Giaodat.Ngaygiao, 103) as NgayGiao, convert(NVARCHAR, Giaodat.Thoihan, 103) as ThoiHan, DMHinhthucsudung.TenHinhthuc, Giaodat.TinhtrangPL, TenTochuc.TenTochuc, a.TongDTTruoc, b.TongDTDuoc FROM Giaodat, TenTochuc, DMDvhc, DMHinhthucsudung,( SELECT LDTruocGiao.IDGD, SUM(Dientich) as TongDTTruoc FROM LDTruocGiao, Giaodat WHERE Giaodat.IDGD = LDTruocGiao.IDGD GROUP BY LDTruocGiao.IDGD ) a, (SELECT LDDuocGiao.IDGD, SUM(Dientich) as TongDTDuoc FROM LDDuocGiao, Giaodat WHERE Giaodat.IDGD = LDDuocGiao.IDGD GROUP BY LDDuocGiao.IDGD) b WHERE Giaodat.IDGD = " + table_DSTenDuAn.Rows[rowSel].ItemArray[1].ToString() + " and Giaodat.MaTochuc = TenTochuc.MaTochuc and Giaodat.MaDvhc = DMDvhc.MaDvhc and Giaodat.MaHinhthuc = DMHinhthucsudung.MaHinhthuc and Giaodat.IDGD = a.IDGD and Giaodat.IDGD = b.IDGD";
labelControl_Ma.Text = sql.GetData(s, 0);
labelControl_TenDA.Text = sql.GetData(s, 1);
labelControl_DiaDiem.Text = sql.GetData(s, 2);
labelControl_Huyen.Text = sql.GetData(s, 3);
labelControl_SoQD.Text = sql.GetData(s, 4);
labelControl_NgayQD.Text = sql.GetData(s, 5);
labelControl_NgayGiao.Text = sql.GetData(s, 6);
labelControl_ThoiHan.Text = sql.GetData(s, 7);
labelControl_HinhThuc.Text = sql.GetData(s, 8);
labelControl_TinhTrang.Text = sql.GetData(s, 9);
labelControl_ToChuc.Text = sql.GetData(s, 10);
groupControl4.Text = "Tổng diện tích trước giao : " + sql.GetData(s, 11) + " m2";
groupControl5.Text = "Tổng diện tích sau giao : " + sql.GetData(s, 12) + " m2";
DataColumn[] Array_col2 = { col_truoc1, col_truoc2 };
s = "SELECT DMLoaidat.TenLD,Dientich FROM DMLoaidat,LDTruocGiao WHERE LDTruocGiao.MaLD = DMLoaidat.MaLD and IDGD = " + table_DSTenDuAn.Rows[rowSel].ItemArray[1].ToString();
table_Truoc.Clear();
sql.ViewDataTable(s, table_Truoc, Array_col2);
DataColumn[] Array_col3 = { col_sau1, col_sau2 };
s = "SELECT DMLoaidat.TenLD,Dientich FROM DMLoaidat,LDDuocGiao WHERE LDDuocGiao.MaLD = DMLoaidat.MaLD and IDGD = " + table_DSTenDuAn.Rows[rowSel].ItemArray[1].ToString();
table_Sau.Clear();
sql.ViewDataTable(s, table_Sau, Array_col3);
Cursor = Cursors.Default;
}
catch { }
}
private void F_Main_DMToChuc_Load(object sender, EventArgs e)
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
sql = F_DangNhap.SQL_con_object as SQLConnect;
nguoi = F_DangNhap.person_object as NguoiDung;
//Gridcontrol2 DSToChuc
table_DSToChuc = new DataTable();
co0 = new DataColumn();
co0.DataType = System.Type.GetType("System.Int32");
co0.ColumnName = "STT";
table_DSToChuc.Columns.Add(co0);
co1 = new DataColumn();
co1.DataType = System.Type.GetType("System.String");
co1.ColumnName = "Mã tổ chức";
table_DSToChuc.Columns.Add(co1);
co2 = new DataColumn();
co2.DataType = System.Type.GetType("System.String");
co2.ColumnName = "Tên tổ chức";
table_DSToChuc.Columns.Add(co2);
co3 = new DataColumn();
co3.DataType = System.Type.GetType("System.String");
co3.ColumnName = "Địa chỉ";
table_DSToChuc.Columns.Add(co3);
co4 = new DataColumn();
co4.DataType = System.Type.GetType("System.String");
co4.ColumnName = "Mã số thuế";
table_DSToChuc.Columns.Add(co4);
co5 = new DataColumn();
co5.DataType = System.Type.GetType("System.String");
co5.ColumnName = "Giám đốc";
table_DSToChuc.Columns.Add(co5);
co6 = new DataColumn();
co6.DataType = System.Type.GetType("System.String");
co6.ColumnName = "Ngày thành lập";
table_DSToChuc.Columns.Add(co6);
gridControl1.Width = int.MaxValue;
gridControl1.DataSource = table_DSToChuc;
gridView1.OptionsBehavior.Editable = false;
//gridView1.OptionsView.ColumnAutoWidth = false;
gridView1.ScrollStyle = DevExpress.XtraGrid.Views.Grid.ScrollStyleFlags.LiveHorzScroll;
gridView1.HorzScrollVisibility = DevExpress.XtraGrid.Views.Base.ScrollVisibility.Always;
gridView1.Columns[0].Width = 5;
gridView1.Columns[1].Width = 70;
gridView1.Columns[2].Width = 300;
gridView1.Columns[3].Width = 350;
gridView1.Columns[4].Width = 100;
gridView1.Columns[5].Width = 100;
gridView1.Columns[6].Width = 100;
//Chèn dữ liệu vào gridcontrol
string s1 = "SELECT COUNT(IDTC) as SoToChuc FROM TenTochuc";
groupControl1.Text = "Danh sách tổ chức | Tổng số tổ chức : " + sql.GetData(s1, 0);
DataColumn[] Array_col2 = { co0, co1, co2, co3, co4, co5, co6 };
s1 = "SELECT ROW_NUMBER() OVER(ORDER BY [TenTochuc] ASC) AS Row#,[MaTochuc],[TenTochuc],[Diachi],[Masothue],[Giamdoc],convert(NVARCHAR, TenTochuc.NgayTL, 103) as NgayTL FROM [GIAODAT].[dbo].[TenTochuc]";
table_DSToChuc.Clear();
sql.ViewDataTable(s1, table_DSToChuc, Array_col2);
Cursor = Cursors.Default;
}
public F_XemDuAn(string soQD)
{
InitializeComponent();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
sql = F_DangNhap.SQL_con_object as SQLConnect;
nguoi = F_DangNhap.person_object as NguoiDung;
//Gridcontrol2 Trươc giao
table_Truoc = new DataTable();
col_truoc1 = new DataColumn();
col_truoc1.DataType = System.Type.GetType("System.String");
col_truoc1.ColumnName = "Loại đất";
table_Truoc.Columns.Add(col_truoc1);
col_truoc2 = new DataColumn();
col_truoc2.DataType = Type.GetType("System.Double");
col_truoc2.ColumnName = "Diện tích (m2)";
table_Truoc.Columns.Add(col_truoc2);
gridControl2.DataSource = table_Truoc;
gridView2.OptionsBehavior.Editable = false;
gridView2.Columns[0].Width = 150;
//Gridcontrol3 Sau giao
table_Sau = new DataTable();
col_sau1 = new DataColumn();
col_sau1.DataType = System.Type.GetType("System.String");
col_sau1.ColumnName = "Loại đất";
table_Sau.Columns.Add(col_sau1);
col_sau2 = new DataColumn();
col_sau2.DataType = Type.GetType("System.Double");
col_sau2.ColumnName = "Diện tích (m2)";
table_Sau.Columns.Add(col_sau2);
gridControl3.DataSource = table_Sau;
gridView3.OptionsBehavior.Editable = false;
gridView3.Columns[0].Width = 150;
// Chèn dữ liệu
string s = "SELECT Giaodat.IDGD, Giaodat.TenDuan, Giaodat.Diadiem, DMDvhc.TenDvhc, Giaodat.SoQD, convert(NVARCHAR, Giaodat.NgayQD, 103) as NgayQD, convert(NVARCHAR, Giaodat.Ngaygiao, 103) as NgayGiao, convert(NVARCHAR, Giaodat.Thoihan, 103) as ThoiHan, DMHinhthucsudung.TenHinhthuc, Giaodat.TinhtrangPL, TenTochuc.TenTochuc, a.TongDTTruoc, b.TongDTDuoc FROM Giaodat, TenTochuc, DMDvhc, DMHinhthucsudung,( SELECT LDTruocGiao.IDGD, SUM(Dientich) as TongDTTruoc FROM LDTruocGiao, Giaodat WHERE Giaodat.IDGD = LDTruocGiao.IDGD GROUP BY LDTruocGiao.IDGD ) a, (SELECT LDDuocGiao.IDGD, SUM(Dientich) as TongDTDuoc FROM LDDuocGiao, Giaodat WHERE Giaodat.IDGD = LDDuocGiao.IDGD GROUP BY LDDuocGiao.IDGD) b WHERE Giaodat.SoQD='" + soQD + "' and Giaodat.MaTochuc = TenTochuc.MaTochuc and Giaodat.MaDvhc = DMDvhc.MaDvhc and Giaodat.MaHinhthuc = DMHinhthucsudung.MaHinhthuc and Giaodat.IDGD = a.IDGD and Giaodat.IDGD = b.IDGD";
labelControl_Ma.Text = sql.GetData(s, 0);
labelControl_TenDA.Text = sql.GetData(s, 1);
labelControl_DiaDiem.Text = sql.GetData(s, 2);
labelControl_Huyen.Text = sql.GetData(s, 3);
labelControl_SoQD.Text = sql.GetData(s, 4);
labelControl_NgayQD.Text = sql.GetData(s, 5);
labelControl_NgayGiao.Text = sql.GetData(s, 6);
labelControl_ThoiHan.Text = sql.GetData(s, 7);
labelControl_HinhThuc.Text = sql.GetData(s, 8);
labelControl_TinhTrang.Text = sql.GetData(s, 9);
labelControl_ToChuc.Text = sql.GetData(s, 10);
groupControl4.Text = "Tổng diện tích trước giao : " + sql.GetData(s, 11) + " m2";
groupControl5.Text = "Tổng diện tích sau giao : " + sql.GetData(s, 12) + " m2";
DataColumn[] Array_col2 = { col_truoc1, col_truoc2 };
s = "SELECT DMLoaidat.TenLD,Dientich FROM DMLoaidat,LDTruocGiao WHERE LDTruocGiao.MaLD = DMLoaidat.MaLD and IDGD = " + labelControl_Ma.Text;
table_Truoc.Clear();
sql.ViewDataTable(s, table_Truoc, Array_col2);
DataColumn[] Array_col3 = { col_sau1, col_sau2 };
s = "SELECT DMLoaidat.TenLD,Dientich FROM DMLoaidat,LDDuocGiao WHERE LDDuocGiao.MaLD = DMLoaidat.MaLD and IDGD = " + labelControl_Ma.Text;
table_Sau.Clear();
sql.ViewDataTable(s, table_Sau, Array_col3);
Cursor = Cursors.Default;
}
private void simpleButton_Luu_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(textEdit_tenDuAn.Text) || String.IsNullOrEmpty(textEdit_diaDiem.Text) ||
String.IsNullOrEmpty(dateEdit_NgayGiao.EditValue.ToString()) || String.IsNullOrEmpty(lookUpEdit_Huyen.EditValue.ToString()) ||
String.IsNullOrEmpty(textEdit_SoQD.Text) || String.IsNullOrEmpty(dateEdit_NgayQD.EditValue.ToString()) ||
String.IsNullOrEmpty(lookUpEdit_HinhThuc.EditValue.ToString()) || String.IsNullOrEmpty(lookUpEdit_TinhTrangPL.EditValue.ToString()) ||
String.IsNullOrEmpty(textEdit_maToChuc.Text) || String.IsNullOrEmpty(textEdit_TenToChuc.Text) || String.IsNullOrEmpty(textEdit_DiaChi.Text) || String.IsNullOrEmpty(textEdit_MST.Text) ||
String.IsNullOrEmpty(textEdit_GiamDoc.Text) || String.IsNullOrEmpty(dateEdit_NgayThanhLap.EditValue.ToString()))
{
DevExpress.XtraEditors.XtraMessageBox.Show("Vui Lòng Nhập Đầy Đủ Thông Tin !");
return;
}
if (!checkError_Table_Truoc())
{
DevExpress.XtraEditors.XtraMessageBox.Show("Vui Lòng Nhập Đầy Đủ Thông Tin bảng loại đất 'Trước' khi giao !");
return;
}
if (!checkError_Table_Sau())
{
DevExpress.XtraEditors.XtraMessageBox.Show("Vui Lòng Nhập Đầy Đủ Thông Tin bảng loại đất 'Sau' khi giao !");
return;
}
string s = "SELECT MaTochuc FROM TenTochuc WHERE MaTochuc='" + textEdit_maToChuc.Text + "'";
if (!String.IsNullOrEmpty(sql.GetData(s, 0)))
{
DevExpress.XtraEditors.XtraMessageBox.Show("Mã tổ chức đã tồn tại !", "Cảnh Báo");
return;
}
s = "SELECT MaTochuc FROM Giaodat WHERE MaTochuc='" + textEdit_maToChuc.Text + "' and TenDuan='" + textEdit_tenDuAn.Text + "'";
if (!String.IsNullOrEmpty(sql.GetData(s, 0)))
{
DevExpress.XtraEditors.XtraMessageBox.Show("Mã tổ chức và tên dữ án đã tồn tại trong CSDL !", "Cảnh Báo");
return;
}
s = "SELECT SoQD FROM Giaodat WHERE SoQD='" + textEdit_SoQD.Text + "' ";
if (!String.IsNullOrEmpty(sql.GetData(s, 0)))
{
DevExpress.XtraEditors.XtraMessageBox.Show("Số quyết định đã tồn tại trong CSDL !", "Cảnh Báo");
return;
}
DialogResult re = DialogResult.No;
re = DevExpress.XtraEditors.XtraMessageBox.Show("Bạn thực sự muốn lưu \"'" + textEdit_tenDuAn.Text + "'\" ?", "Thông Báo", MessageBoxButtons.YesNo);
if (re == DialogResult.Yes)
{
try
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
string ngaythanhlap = dateEdit_NgayThanhLap.DateTime.Month + "/" + dateEdit_NgayThanhLap.DateTime.Day + "/" + dateEdit_NgayThanhLap.DateTime.Year;
string ngaygiao = dateEdit_NgayGiao.DateTime.Month + "/" + dateEdit_NgayGiao.DateTime.Day + "/" + dateEdit_NgayGiao.DateTime.Year;
string ngayQD = dateEdit_NgayQD.DateTime.Month + "/" + dateEdit_NgayQD.DateTime.Day + "/" + dateEdit_NgayQD.DateTime.Year;
string thoihan = dateEdit_NgayGiao.DateTime.Month + "/" + dateEdit_NgayGiao.DateTime.Day + "/" + (dateEdit_NgayGiao.DateTime.Year + spinEdit_ThoiHan.Value);
string s1 = "INSERT INTO TenTochuc(MaTochuc, TenTochuc, Diachi, Masothue, Giamdoc, NgayTL) VALUES (N'" + textEdit_maToChuc.Text + "',N'" + textEdit_TenToChuc.Text + "',N'" + textEdit_DiaChi.Text + "',N'" + textEdit_MST.Text + "',N'" + textEdit_GiamDoc.Text + "',N'" + ngaythanhlap + "');";
sql.ExecuteData(s1);
string s2 = "INSERT INTO Giaodat(MaTochuc, Ngaygiao, TenDuan, Diadiem, MaDvhc, SoQD, NgayQD, Thoihan, MaHinhthuc, TinhtrangPL, MaIDNguoiNhap) VALUES (N'" + textEdit_maToChuc.Text + "',N'" + ngaygiao + "',N'" + textEdit_tenDuAn.Text + "',N'" + textEdit_diaDiem.Text + "',N'" + lookUpEdit_Huyen.EditValue + "',N'" + textEdit_SoQD.Text + "',N'" + ngayQD + "',N'" + thoihan + "',N'" + lookUpEdit_HinhThuc.EditValue + "',N'" + lookUpEdit_TinhTrangPL.EditValue + "'," + nguoi.MaID + ");";
sql.ExecuteData(s2);
string s3 = "SELECT TOP 1 [IDGD] FROM [GIAODAT].[dbo].[Giaodat] ORDER BY IDGD desc";
this.Insert_Table_Truoc(sql.GetData(s3, 0));
this.Insert_Table_Sau(sql.GetData(s3, 0));
Cursor = Cursors.Default;
DevExpress.XtraEditors.XtraMessageBox.Show("Lưu dữ liệu thành công !");
this.Close();
}
catch {
DevExpress.XtraEditors.XtraMessageBox.Show("Đã xãy ra lỗi !");
}
}
}
private void F_Main_TinhTrangPL_Load(object sender, EventArgs e)
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
sql = F_DangNhap.SQL_con_object as SQLConnect;
nguoi = F_DangNhap.person_object as NguoiDung;
//GridControl DSDuAn_DaCap
table_DSDuAn_DaCap = new DataTable();
c0 = new DataColumn();
c0.DataType = System.Type.GetType("System.Int32");
c0.ColumnName = "STT";
table_DSDuAn_DaCap.Columns.Add(c0);
c1 = new DataColumn();
c1.DataType = System.Type.GetType("System.String");
c1.ColumnName = "Tên dự án";
table_DSDuAn_DaCap.Columns.Add(c1);
c2 = new DataColumn();
c2.DataType = System.Type.GetType("System.String");
c2.ColumnName = "Địa điểm";
table_DSDuAn_DaCap.Columns.Add(c2);
c3 = new DataColumn();
c3.DataType = System.Type.GetType("System.String");
c3.ColumnName = "Huyện";
table_DSDuAn_DaCap.Columns.Add(c3);
c4 = new DataColumn();
c4.DataType = System.Type.GetType("System.Double");
c4.ColumnName = "Diện tích trước giao (m2)";
table_DSDuAn_DaCap.Columns.Add(c4);
c5 = new DataColumn();
c5.DataType = System.Type.GetType("System.Double");
c5.ColumnName = "Diện tích sau giao (m2)";
table_DSDuAn_DaCap.Columns.Add(c5);
c6 = new DataColumn();
c6.DataType = System.Type.GetType("System.String");
c6.ColumnName = "Số quyết định";
table_DSDuAn_DaCap.Columns.Add(c6);
c7 = new DataColumn();
c7.DataType = System.Type.GetType("System.String");
c7.ColumnName = "Ngày quyết định";
table_DSDuAn_DaCap.Columns.Add(c7);
c8 = new DataColumn();
c8.DataType = System.Type.GetType("System.String");
c8.ColumnName = "Ngày giao";
table_DSDuAn_DaCap.Columns.Add(c8);
c9 = new DataColumn();
c9.DataType = System.Type.GetType("System.String");
c9.ColumnName = "Thời hạn";
table_DSDuAn_DaCap.Columns.Add(c9);
c10 = new DataColumn();
c10.DataType = System.Type.GetType("System.String");
c10.ColumnName = "Hình thức sử dụng";
table_DSDuAn_DaCap.Columns.Add(c10);
c11 = new DataColumn();
c11.DataType = System.Type.GetType("System.String");
c11.ColumnName = "Tình trạng pháp lý";
table_DSDuAn_DaCap.Columns.Add(c11);
gridControl1.Width = int.MaxValue;
gridControl1.DataSource = table_DSDuAn_DaCap;
gridView1.OptionsBehavior.Editable = false;
gridView1.OptionsView.ColumnAutoWidth = false;
gridView1.ScrollStyle = DevExpress.XtraGrid.Views.Grid.ScrollStyleFlags.LiveHorzScroll;
gridView1.HorzScrollVisibility = DevExpress.XtraGrid.Views.Base.ScrollVisibility.Always;
gridView1.Columns[0].Width = 25;
gridView1.Columns[1].Width = 300;
gridView1.Columns[2].Width = 250;
gridView1.Columns[3].Width = 110;
gridView1.Columns[4].Width = 130;
gridView1.Columns[5].Width = 120;
gridView1.Columns[6].Width = 90;
gridView1.Columns[7].Width = 90;
gridView1.Columns[8].Width = 70;
gridView1.Columns[9].Width = 70;
gridView1.Columns[10].Width = 300;
gridView1.Columns[11].Width = 150;
// Chèn dữ liệu đã cấp
string daCap = "SELECT COUNT(TinhtrangPL) as SoDuAn FROM Giaodat WHERE TinhtrangPL = N'Đã cấp giấy chứng nhận'";
groupControl1.Text = "Danh sách dự án | Tổng số dự án đã cấp giấy chứng nhận : " + sql.GetData(daCap, 0);
DataColumn[] Array_col2 = { c0, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11 };
string s1 = "SELECT ROW_NUMBER() OVER(ORDER BY Giaodat.TenDuan asc) AS Row#, Giaodat.TenDuan, Giaodat.Diadiem, DMDvhc.TenDvhc, a.TongDTTruoc, b.TongDTDuoc, Giaodat.SoQD, convert(NVARCHAR, Giaodat.NgayQD, 103) as NgayQD, convert(NVARCHAR, Giaodat.Ngaygiao, 103) as NgayGiao, convert(NVARCHAR, Giaodat.Thoihan, 103) as ThoiHan, DMHinhthucsudung.TenHinhthuc, Giaodat.TinhtrangPL FROM Giaodat, DMDvhc, DMHinhthucsudung,( SELECT LDTruocGiao.IDGD, SUM(Dientich) as TongDTTruoc FROM LDTruocGiao, Giaodat WHERE Giaodat.IDGD = LDTruocGiao.IDGD GROUP BY LDTruocGiao.IDGD ) a, (SELECT LDDuocGiao.IDGD, SUM(Dientich) as TongDTDuoc FROM LDDuocGiao, Giaodat WHERE Giaodat.IDGD = LDDuocGiao.IDGD GROUP BY LDDuocGiao.IDGD) b WHERE Giaodat.TinhtrangPL = N'Đã cấp giấy chứng nhận' and Giaodat.MaDvhc = DMDvhc.MaDvhc and Giaodat.MaHinhthuc = DMHinhthucsudung.MaHinhthuc and Giaodat.IDGD = a.IDGD and Giaodat.IDGD = b.IDGD GROUP BY Giaodat.TenDuan, Giaodat.Diadiem, a.TongDTTruoc, b.TongDTDuoc, DMDvhc.TenDvhc, Giaodat.SoQD, Giaodat.NgayQD, Giaodat.Ngaygiao, Giaodat.Thoihan, DMHinhthucsudung.TenHinhthuc, Giaodat.TinhtrangPL";
table_DSDuAn_DaCap.Clear();
sql.ViewDataTable(s1, table_DSDuAn_DaCap, Array_col2);
//GridControl DSDuAn_ChuaCap
table_DSDuAn_ChuaCap = new DataTable();
c0 = new DataColumn();
c0.DataType = System.Type.GetType("System.Int32");
c0.ColumnName = "STT";
table_DSDuAn_ChuaCap.Columns.Add(c0);
c1 = new DataColumn();
c1.DataType = System.Type.GetType("System.String");
c1.ColumnName = "Tên dự án";
table_DSDuAn_ChuaCap.Columns.Add(c1);
c2 = new DataColumn();
c2.DataType = System.Type.GetType("System.String");
c2.ColumnName = "Địa điểm";
table_DSDuAn_ChuaCap.Columns.Add(c2);
c3 = new DataColumn();
c3.DataType = System.Type.GetType("System.String");
c3.ColumnName = "Huyện";
table_DSDuAn_ChuaCap.Columns.Add(c3);
c4 = new DataColumn();
c4.DataType = System.Type.GetType("System.Double");
c4.ColumnName = "Diện tích trước giao (m2)";
table_DSDuAn_ChuaCap.Columns.Add(c4);
c5 = new DataColumn();
c5.DataType = System.Type.GetType("System.Double");
c5.ColumnName = "Diện tích sau giao (m2)";
table_DSDuAn_ChuaCap.Columns.Add(c5);
c6 = new DataColumn();
c6.DataType = System.Type.GetType("System.String");
c6.ColumnName = "Số quyết định";
table_DSDuAn_ChuaCap.Columns.Add(c6);
c7 = new DataColumn();
c7.DataType = System.Type.GetType("System.String");
c7.ColumnName = "Ngày quyết định";
table_DSDuAn_ChuaCap.Columns.Add(c7);
c8 = new DataColumn();
c8.DataType = System.Type.GetType("System.String");
c8.ColumnName = "Ngày giao";
table_DSDuAn_ChuaCap.Columns.Add(c8);
c9 = new DataColumn();
c9.DataType = System.Type.GetType("System.String");
c9.ColumnName = "Thời hạn";
table_DSDuAn_ChuaCap.Columns.Add(c9);
c10 = new DataColumn();
c10.DataType = System.Type.GetType("System.String");
c10.ColumnName = "Hình thức sử dụng";
table_DSDuAn_ChuaCap.Columns.Add(c10);
c11 = new DataColumn();
c11.DataType = System.Type.GetType("System.String");
c11.ColumnName = "Tình trạng pháp lý";
table_DSDuAn_ChuaCap.Columns.Add(c11);
gridControl2.Width = int.MaxValue;
gridControl2.DataSource = table_DSDuAn_ChuaCap;
gridView2.OptionsBehavior.Editable = false;
gridView2.OptionsView.ColumnAutoWidth = false;
gridView2.ScrollStyle = DevExpress.XtraGrid.Views.Grid.ScrollStyleFlags.LiveHorzScroll;
gridView2.HorzScrollVisibility = DevExpress.XtraGrid.Views.Base.ScrollVisibility.Always;
gridView2.Columns[0].Width = 25;
gridView2.Columns[1].Width = 300;
gridView2.Columns[2].Width = 250;
gridView2.Columns[3].Width = 110;
gridView2.Columns[4].Width = 130;
gridView2.Columns[5].Width = 120;
gridView2.Columns[6].Width = 90;
gridView2.Columns[7].Width = 90;
gridView2.Columns[8].Width = 70;
gridView2.Columns[9].Width = 70;
gridView2.Columns[10].Width = 300;
gridView2.Columns[11].Width = 150;
// Chèn dữ liệu chưa cấp
string chuaCap = "SELECT COUNT(TinhtrangPL) as SoDuAn FROM Giaodat WHERE TinhtrangPL = N'Chưa cấp giấy chứng nhận'";
groupControl2.Text = "Danh sách dự án | Tổng số dự án chưa cấp giấy chứng nhận : " + sql.GetData(chuaCap, 0);
DataColumn[] Array_col3 = { c0, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11 };
string s2 = "SELECT ROW_NUMBER() OVER(ORDER BY Giaodat.TenDuan asc) AS Row#, Giaodat.TenDuan, Giaodat.Diadiem, DMDvhc.TenDvhc, a.TongDTTruoc, b.TongDTDuoc, Giaodat.SoQD, convert(NVARCHAR, Giaodat.NgayQD, 103) as NgayQD, convert(NVARCHAR, Giaodat.Ngaygiao, 103) as NgayGiao, convert(NVARCHAR, Giaodat.Thoihan, 103) as ThoiHan, DMHinhthucsudung.TenHinhthuc, Giaodat.TinhtrangPL FROM Giaodat, DMDvhc, DMHinhthucsudung,( SELECT LDTruocGiao.IDGD, SUM(Dientich) as TongDTTruoc FROM LDTruocGiao, Giaodat WHERE Giaodat.IDGD = LDTruocGiao.IDGD GROUP BY LDTruocGiao.IDGD ) a, (SELECT LDDuocGiao.IDGD, SUM(Dientich) as TongDTDuoc FROM LDDuocGiao, Giaodat WHERE Giaodat.IDGD = LDDuocGiao.IDGD GROUP BY LDDuocGiao.IDGD) b WHERE Giaodat.TinhtrangPL = N'Chưa cấp giấy chứng nhận' and Giaodat.MaDvhc = DMDvhc.MaDvhc and Giaodat.MaHinhthuc = DMHinhthucsudung.MaHinhthuc and Giaodat.IDGD = a.IDGD and Giaodat.IDGD = b.IDGD GROUP BY Giaodat.TenDuan, Giaodat.Diadiem, a.TongDTTruoc, b.TongDTDuoc, DMDvhc.TenDvhc, Giaodat.SoQD, Giaodat.NgayQD, Giaodat.Ngaygiao, Giaodat.Thoihan, DMHinhthucsudung.TenHinhthuc, Giaodat.TinhtrangPL";
table_DSDuAn_ChuaCap.Clear();
sql.ViewDataTable(s2, table_DSDuAn_ChuaCap, Array_col3);
Cursor = Cursors.Default;
}
private void toolStripMenuItem1_Refesh_Click(object sender, EventArgs e)
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
table_DSDuAn_DaCap.Clear();
string daCap = "SELECT COUNT(TinhtrangPL) as SoDuAn FROM Giaodat WHERE TinhtrangPL = N'Đã cấp giấy chứng nhận'";
groupControl1.Text = "Danh sách dự án | Tổng số dự án đã cấp giấy chứng nhận : " + sql.GetData(daCap, 0);
DataColumn[] Array_col2 = { c0, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11 };
string s1 = "SELECT ROW_NUMBER() OVER(ORDER BY Giaodat.TenDuan asc) AS Row#, Giaodat.TenDuan, Giaodat.Diadiem, DMDvhc.TenDvhc, a.TongDTTruoc, b.TongDTDuoc, Giaodat.SoQD, convert(NVARCHAR, Giaodat.NgayQD, 103) as NgayQD, convert(NVARCHAR, Giaodat.Ngaygiao, 103) as NgayGiao, convert(NVARCHAR, Giaodat.Thoihan, 103) as ThoiHan, DMHinhthucsudung.TenHinhthuc, Giaodat.TinhtrangPL FROM Giaodat, DMDvhc, DMHinhthucsudung,( SELECT LDTruocGiao.IDGD, SUM(Dientich) as TongDTTruoc FROM LDTruocGiao, Giaodat WHERE Giaodat.IDGD = LDTruocGiao.IDGD GROUP BY LDTruocGiao.IDGD ) a, (SELECT LDDuocGiao.IDGD, SUM(Dientich) as TongDTDuoc FROM LDDuocGiao, Giaodat WHERE Giaodat.IDGD = LDDuocGiao.IDGD GROUP BY LDDuocGiao.IDGD) b WHERE Giaodat.TinhtrangPL = N'Đã cấp giấy chứng nhận' and Giaodat.MaDvhc = DMDvhc.MaDvhc and Giaodat.MaHinhthuc = DMHinhthucsudung.MaHinhthuc and Giaodat.IDGD = a.IDGD and Giaodat.IDGD = b.IDGD GROUP BY Giaodat.TenDuan, Giaodat.Diadiem, a.TongDTTruoc, b.TongDTDuoc, DMDvhc.TenDvhc, Giaodat.SoQD, Giaodat.NgayQD, Giaodat.Ngaygiao, Giaodat.Thoihan, DMHinhthucsudung.TenHinhthuc, Giaodat.TinhtrangPL";
table_DSDuAn_DaCap.Clear();
sql.ViewDataTable(s1, table_DSDuAn_DaCap, Array_col2);
Cursor = Cursors.Default;
}
private void simpleButton_Luu_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(textEdit_maToChuc.Text) || String.IsNullOrEmpty(textEdit_TenToChuc.Text) || String.IsNullOrEmpty(textEdit_DiaChi.Text) || String.IsNullOrEmpty(textEdit_MST.Text) ||
String.IsNullOrEmpty(textEdit_GiamDoc.Text) || String.IsNullOrEmpty(dateEdit_NgayThanhLap.EditValue.ToString()))
{
DevExpress.XtraEditors.XtraMessageBox.Show("Vui Lòng Nhập Đầy Đủ Thông Tin !");
return;
}
DialogResult re = DialogResult.No;
re = DevExpress.XtraEditors.XtraMessageBox.Show("Bạn thực sự muốn lưu \"'" + textEdit_maToChuc.Text + "'\" ?", "Thông Báo", MessageBoxButtons.YesNo);
if (re == DialogResult.Yes)
{
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
string ngaythanhlap = dateEdit_NgayThanhLap.DateTime.Month + "/" + dateEdit_NgayThanhLap.DateTime.Day + "/" + dateEdit_NgayThanhLap.DateTime.Year;
try
{
if (!String.IsNullOrEmpty(this.maToChuc))
{
if (!textEdit_maToChuc.Text.ToString().Equals(this.maToChuc))
{
string s = "SELECT MaTochuc FROM TenTochuc WHERE MaTochuc='" + textEdit_maToChuc.Text + "'";
if (!String.IsNullOrEmpty(sql.GetData(s, 0)))
{
DevExpress.XtraEditors.XtraMessageBox.Show("Mã tổ chức đã tồn tại !", "Cảnh Báo");
return;
}
}
if (this.soduan > 0)
{
string s10 = "ALTER TABLE Giaodat DROP CONSTRAINT FK__Giaodat__MaTochu__0A9D95DB";
sql.ExecuteData(s10);
s10 = "UPDATE Giaodat SET MaTochuc=N'" + textEdit_maToChuc.Text + "' WHERE MaTochuc='" + this.maToChuc + "' ";
sql.ExecuteData(s10);
s10 = "UPDATE TenTochuc SET MaTochuc=N'" + textEdit_maToChuc.Text + "', TenTochuc=N'" + textEdit_TenToChuc.Text + "', Diachi=N'" + textEdit_DiaChi.Text + "', Masothue=N'" + textEdit_MST.Text + "', Giamdoc=N'" + textEdit_GiamDoc.Text + "', NgayTL=N'" + ngaythanhlap + "' WHERE MaTochuc='" + this.maToChuc + "'";
sql.ExecuteData(s10);
s10 = "ALTER TABLE Giaodat ADD CONSTRAINT FK__Giaodat__MaTochu__0A9D95DB FOREIGN KEY([MaTochuc]) REFERENCES [dbo].[TenTochuc] ([MaTochuc])";
sql.ExecuteData(s10);
}
else
{
string s1 = "UPDATE TenTochuc SET MaTochuc=N'" + textEdit_maToChuc.Text + "', TenTochuc=N'" + textEdit_TenToChuc.Text + "', Diachi=N'" + textEdit_DiaChi.Text + "', Masothue=N'" + textEdit_MST.Text + "', Giamdoc=N'" + textEdit_GiamDoc.Text + "', NgayTL=N'" + ngaythanhlap + "' WHERE MaTochuc='" + this.maToChuc + "'";
sql.ExecuteData(s1);
}
this.formTenToChuc.RefeshDS();
}
else
{
string s = "SELECT MaTochuc FROM TenTochuc WHERE MaTochuc='" + textEdit_maToChuc.Text + "'";
if (!String.IsNullOrEmpty(sql.GetData(s, 0)))
{
DevExpress.XtraEditors.XtraMessageBox.Show("Mã tổ chức đã tồn tại !", "Cảnh Báo");
return;
}
string s1 = "INSERT INTO TenTochuc(MaTochuc, TenTochuc, Diachi, Masothue, Giamdoc, NgayTL) VALUES (N'" + textEdit_maToChuc.Text + "',N'" + textEdit_TenToChuc.Text + "',N'" + textEdit_DiaChi.Text + "',N'" + textEdit_MST.Text + "',N'" + textEdit_GiamDoc.Text + "',N'" + ngaythanhlap + "');";
sql.ExecuteData(s1);
}
Cursor = Cursors.Default;
DevExpress.XtraEditors.XtraMessageBox.Show("Lưu dữ liệu thành công !");
this.Close();
}
catch
{
DevExpress.XtraEditors.XtraMessageBox.Show("Đã xãy ra lỗi !");
}
}
}
public F_Main_ThemDuAn(F_Main_SoQuyetDinh frm, string id)
{
InitializeComponent();
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
this.LoadData();
this.Text = "Chỉnh sửa thông tin dự án";
string s = "SELECT TenDuan, Diadiem, convert(NVARCHAR, Ngaygiao, 101) as NgayGiao, MaDvhc, SoQD, convert(NVARCHAR, NgayQD, 101) as NgayQD, MaHinhthuc, YEAR(Thoihan)-YEAR(Ngaygiao) as SoNam , TinhtrangPL, MaTochuc FROM Giaodat WHERE IDGD = " + id;
this.formTenDuAn = frm;
this.IDGD = id;
this.soQD = sql.GetData(s, 4);
textEdit_tenDuAn.Text = sql.GetData(s, 0);
textEdit_diaDiem.Text = sql.GetData(s, 1);
dateEdit_NgayGiao.EditValue = sql.GetData(s, 2);
lookUpEdit_Huyen.EditValue = sql.GetData(s, 3);
textEdit_SoQD.Text = sql.GetData(s, 4);
dateEdit_NgayQD.EditValue = sql.GetData(s, 5);
lookUpEdit_HinhThuc.EditValue = sql.GetData(s, 6);
spinEdit_ThoiHan.Value = int.Parse(sql.GetData(s, 7).ToString().Trim());
lookUpEdit_TinhTrangPL.EditValue = sql.GetData(s, 8);
lookUpEdit_MaToChuc.EditValue = sql.GetData(s, 9);
DataColumn[] Array_coltruoc = { col_truoc1, col_truoc2 };
s = "SELECT [MaLD],[Dientich] FROM [GIAODAT].[dbo].[LDTruocGiao] WHERE IDGD = " + id;
sql.ViewDataTable(s, table_Truoc, Array_coltruoc);
DataColumn[] Array_colsau = { col_sau1, col_sau2 };
s = "SELECT [MaLD],[Dientich] FROM [GIAODAT].[dbo].[LDDuocGiao] WHERE IDGD = " + id;
sql.ViewDataTable(s, table_Sau, Array_colsau);
simpleButton_NhapLai.Enabled = false;
Cursor = Cursors.Default;
}