private void Window_Loaded(object sender, RoutedEventArgs e)
{
PopulateMonthsAndYears();
ClsServer cls = new ClsServer();
cls.ClsConnect();
string sql = "select PO_MA,PO_TEN from DMPOS order by PO_MA";
//var sql = BienBll.NdCapbc.Trim() == "1" ? string.Format("select PO_MA,PO_TEN from DMPOS where PO_MA='{0}'", BienBll.NdMadv.Trim()) : "select PO_MA,PO_TEN from DMPOS where right(PO_MA,2)<>'00'";
var dtpos = cls.LoadDataText(sql);
for (int i = 0; i < dtpos.Rows.Count; i++)
{
CboPos.Items.Add(dtpos.Rows[i][0] + " | " + dtpos.Rows[i][1]);
}
DataTable dtchon = new DataTable();
string sqlch = "select CHTRINH,TEN_CT from DM_CHTRINH where CHTRINH in ('01','09','19') order by CHTRINH";
dtchon = cls.LoadDataText(sqlch);
for (int i = 0; i < dtchon.Rows.Count; i++)
{
CboChTr.Items.Add(dtchon.Rows[i][0] + " | " + dtchon.Rows[i][1]);
}
CboChTr.SelectedIndex = 0;
CboPos.SelectedIndex = 0;
cls.DongKetNoi();
}
private void btnInsert_Click(object sender, RoutedEventArgs e)
{
ClsServer cls = new ClsServer();
cls.ClsConnect();
try
{
string FileName = "C:\\TEXT\\" + bll.XoaHetKyTuTrang(bll.Left(bll.Right(txtPath.Text.Trim(), 16), 12) + '_' + bll.Right(bll.Left(CboSheet.SelectedValue.ToString().Trim(), 3), 2)) + ".txt";
MessageBox.Show(FileName);
int thamso = 1;
string[] bien = new string[thamso];
object[] giatri = new object[thamso];
bien[0] = "@PathDir";
giatri[0] = FileName;
if (File.Exists(giatri[0].ToString().Trim()))
{
cls.UpdateLdbf("usp_InsertDS", bien, giatri, thamso);
File.Delete(giatri[0].ToString().Trim());
MessageBox.Show("Insert OK : " + giatri[1]);
}
else
{
MessageBox.Show(" Chưa có file : " + giatri[1].ToString().Trim());
}
// lbl.Content = "";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Thong bao", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void btnOK_Click(object sender, RoutedEventArgs e)
{
ClsServer cls = new ClsServer();
try
{
cls.ClsConnect();
int thamso = 4;
string[] bien = new string[thamso];
object[] giatri = new object[thamso];
bien[0] = "@Ngay";
if (dtpNgay.SelectedDate != null)
{
giatri[0] = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
}
bien[1] = "@MaPos";
giatri[1] = bll.Left(CboPos.SelectedValue.ToString().Trim(), 6);
bien[2] = "@MaXa";
giatri[2] = bll.Left(CboXa.SelectedValue.ToString().Trim(), 6);
bien[3] = "@Nam";
giatri[3] = comboBoxYear.SelectedValue.ToString().Trim();
dt = cls.LoadLdbf("usp_TTDSHN01", bien, giatri, thamso);
rpt_ChkDoituong01 rpt = new rpt_ChkDoituong01();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
//MessageBox.Show("Insert OK", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
catch (Exception ex)
{
MessageBox.Show("Error + " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void CboPos_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
try
{
ClsServer cls = new ClsServer();
if (bll.Left(CboPos.SelectedValue.ToString().Trim(), 6) != "003000")
{
CboXa.Items.Clear();
cls.ClsConnect();
DataTable dtxa = new DataTable();
string sql = "select MA,TEN from DMXA where PGD_QL= " + "'" +
bll.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "'" + " order by MA";
dtxa = cls.LoadDataText(sql);
for (int i = 0; i < dtxa.Rows.Count; i++)
{
CboXa.Items.Add(dtxa.Rows[i][0] + " | " + dtxa.Rows[i][1]);
}
}
else
{
CboXa.Items.Add("003000 | Tất cả");
}
CboXa.SelectedIndex = 0;
cls.DongKetNoi();
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex.Message, "Mess", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void cboTkC3_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
cboTk.Items.Clear();
ClsServer cls = new ClsServer();
cls.ClsConnect();
DataTable dtgl = new DataTable();
dtgl = cls.LoadDataText("select BANK_AC as TKGL,TK_CAP5 as TKSBV ,AC_DESC as TENTK from dmtkgl where left(BANK_AC,4) ='" + str.Left(cboTkC3.SelectedValue.ToString().Trim(), 4) + "' and len(BANK_AC)=10 order by BANK_AC");
for (int i = 0; i < dtgl.Rows.Count; i++)
{
cboTk.Items.Add(dtgl.Rows[i][0] + "|" + dtgl.Rows[i][1] + "|" + dtgl.Rows[i][2]);
}
cls.DongKetNoi();
}
private void btnOK_Click(object sender, RoutedEventArgs e)
{
string ins = "";
ClsServer cls = new ClsServer();
String name = CboSheet.SelectedValue.ToString().Trim();
//String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +"d:\\DBIMP\\DBEXCEL.xlsx" +";Extended Properties='Excel 12.0 XML;HDR=YES;';";
String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtPath.Text.Trim() +
";Extended Properties='Excel 12.0 XML;HDR=YES;';";
try
{
OleDbConnection con = new OleDbConnection(constr);
OleDbCommand cmd =
new OleDbCommand(
"Select ct,nam,dp,ttho,tttv,hoten,namsinh,quanhe,namsinh1,ten,cmt From [" + name + "]", con);
con.Open();
OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
ad.Fill(dt);
if (dt.Rows.Count > 0)
{
dgvData.ItemsSource = dt.DefaultView;
}
//MessageBox.Show("Read Excel OK", "Mess", MessageBoxButton.OK, MessageBoxImage.Information);
//btnWrite.IsEnabled = true;
else
{
MessageBox.Show("Không có bản ghi nào", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
con.Close();
con.Dispose();
cls.ClsConnect();
foreach (DataRow dr in dt.Rows)
{
ins = "insert into DANHSACH (CT,NAM,DP,TTHO,TTTV,HOTEN,NAMSINH,QUANHE,NAMSINH1,TEN,CMT)"
+ " values ('" + dr["CT"] + "','" + dr["NAM"] + "','" + dr["DP"] + "','" + dr["TTHO"] + "','" +
dr["TTTV"] + "',N'" + dr["HOTEN"] + "','" + dr["NAMSINH"] + "',N'" + dr["QUANHE"] + "','" + dr["NAMSINH1"] + "',N'" + dr["TEN"] + "','" +
dr["CMT"] + "')";
//MessageBox.Show(upd);
cls.UpdateDataText(ins);
}
MessageBox.Show("Insert OK", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
catch (Exception ex)
{
MessageBox.Show("Error + " + ex.Message + " " + ins, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
// kiem tra da ton tai du lieu hay chua
public bool ExitsData(string strsql)
{
ClsServer cls = new ClsServer();
cls.ClsConnect();
DataTable dt = new DataTable();
string str = strsql;
dt = cls.LoadDataText(str);
if (dt.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
PopulateMonthsAndYears();
ClsServer cls = new ClsServer();
cls.ClsConnect();
string sql = "select PO_MA,PO_TEN from DMPOS order by PO_MA";
//var sql = BienBll.NdCapbc.Trim() == "1" ? string.Format("select PO_MA,PO_TEN from DMPOS where PO_MA='{0}'", BienBll.NdMadv.Trim()) : "select PO_MA,PO_TEN from DMPOS where right(PO_MA,2)<>'00'";
var dtpos = cls.LoadDataText(sql);
for (int i = 0; i < dtpos.Rows.Count; i++)
{
CboPos.Items.Add(dtpos.Rows[i][0] + " | " + dtpos.Rows[i][1]);
}
CboPos.SelectedIndex = 0;
var dtng = cls.LoadDataText("select MAX(convert(date,NGAYKU,105)) as NGMAX from U_HSTD");
dtpNgay.SelectedDate = DateTime.Parse(dtng.Rows[0]["NGMAX"].ToString());
cls.DongKetNoi();
}
private void btnOK_Click(object sender, RoutedEventArgs e)
{
ClsServer cls = new ClsServer();
try
{
bll.TaoThuMuc(Thumuc);
string pos = bll.Right(bll.Left(CboPos.SelectedValue.ToString().Trim(), 6), 4);
cls.ClsConnect();
string strsql = "with lst1 as ( select MA, TEN from DMTHON where LEFT(MA,4)= '" + pos + "' and TRANGTHAI = 'A' ), lst2 as ( "
+ " select a.CT,a.NAM,LEFT(a.DP, 4) POS,LEFT(a.DP, 6) Xa, a.DP THON,(select ten from dmthon where a.dp = MA) TENTHON,COUNT(a.HOTEN) DEM "
+ " from DANHSACH a where LEFT(DP,4)= '" + pos + "' and CT = '" + bll.Left(CboChTr.SelectedValue.ToString(), 2) + "' and NAM = '" + comboBoxYear.SelectedValue + "' "
+ " group by LEFT(a.DP, 4),LEFT(a.DP, 6), a.DP,a.CT,a.NAM ) "
+ " select LEFT(a.MA, 4) POS,(select po_ten from dmpos where LEFT(a.MA, 4) = right(po_ma, 4)) TENPOS "
+ " ,left(a.MA, 6) N'Xã' ,(select TEN from dmxa where MA = left(a.MA, 6)) N'Tên Xã',a.MA N'Thôn',a.TEN N'Tên Thôn','" + comboBoxYear.SelectedValue + "' NAM,'" + bll.Left(CboChTr.SelectedValue.ToString(), 2) + "' CT "
+ " ,(select GIATRI from dmkhac where khoa_1 = '07' and MOTA = b.CT) N'Tên CHTR',isnull(b.DEM, 0) N'Số khẩu' "
+ " from lst1 a left join lst2 b on a.MA = b.THON order by a.MA";
dt = cls.LoadDataText(strsql);
FileName = Thumuc + "\\" + pos + "_" + comboBoxYear.SelectedValue + "_" + bll.Left(CboChTr.SelectedValue.ToString(), 2) + "_Danh sách" + ".csv";
if (dt.Rows.Count > 0)
{
bll.ExportToExcel(dt, FileName);
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
bll.OpenExcel(FileName);
}
else
{
MessageBox.Show("Không có số liệu", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Warning);
}
//MessageBox.Show(strsql, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
catch (Exception ex)
{
MessageBox.Show("Error + " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
cls.DongKetNoi();
}
private void LblCheck_OnMouseDown(object sender, MouseButtonEventArgs e)
{
try
{
ClsServer cls = new ClsServer();
cls.ClsConnect();
string nam = comboBoxYear.SelectedValue.ToString().Trim();
string pos = bll.Right(bll.Left(CboPos.SelectedValue.ToString(), 6), 4);
string chtr = bll.Left(CboChTr.SelectedValue.ToString(), 2);
FileName = thumuc + "\\" + nam + "_" + pos + "_" + chtr + "_" + DateTime.Now.ToString("ddMMyyyy") + ".csv";
string chk = "with lst1 as ( select b.MA MAXA, b.TEN TENXA, a.MA MATHON, a.TEN TENTHON from DMTHON a, DMXA b where a.XA = b.MA and left(a.MA, 4) ='" + pos + "'"
+ " ), lst2 as (select distinct a.CT,a.NAM,a.DP from DANHSACH a, DMTHON b where a.NAM = '" + nam + "' and a.CT = '" + chtr + "' and left(a.DP, 4)= '" + pos + "'"
+ ") select " + chtr + " N'Chương Trình'," + nam + " N'Năm', a.MAXA N'Mã Xã',a.TENXA N'Tên Xã',a.MATHON N'Mã Thôn',a.TENTHON N'Tên Thôn',(select 'X' from lst2 where DP = a.MATHON) N'Đã nhập' from lst1 a order by a.MATHON";
var dt = cls.LoadDataText(chk);
bll.ExportToExcel(dt, FileName);
bll.OpenExcel(FileName);
cls.DongKetNoi();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
dtpTuNgay.SelectedDate = DateTime.Parse("01/01/" + DateTime.Now.ToString("yyyy"));
dtpDenNgay.SelectedDate = DateTime.Now.Date;
ClsServer cls = new ClsServer();
cls.ClsConnect();
/*
* DataTable dtgl = new DataTable();
* dtgl = cls.LoadDataText("select BANK_AC as TKGL,TK_CAP5 as TKSBV ,AC_DESC as TENTK from dmtkgl where left(BANK_AC,2) in ('92','94','97','98','93','99') order by BANK_AC");
* for (int i = 0; i<dtgl.Rows.Count; i++)
* {
* cboTk.Items.Add(dtgl.Rows[i][0] + "|" + dtgl.Rows[i][1] + "|" + dtgl.Rows[i][2]);
* }
*/
DataTable dtglc3 = new DataTable();
dtglc3 = cls.LoadDataText("select BANK_AC as TKGL,TK_CAP5 as TKSBV ,AC_DESC as TENTK from dmtkgl where len(BANK_AC)=4 order by BANK_AC");
for (int i = 0; i < dtglc3.Rows.Count; i++)
{
cboTkC3.Items.Add(dtglc3.Rows[i][0] + "|" + dtglc3.Rows[i][1] + "|" + dtglc3.Rows[i][2]);
}
DataTable dtpos = new DataTable();
cls.ClsConnect();
string sql = "select PO_MA,PO_TEN from DMPOS where PO_MACN=" + "'" + BienBll.MainPos + "'" + " order by PO_MA";
dtpos = cls.LoadDataText(sql);
for (int i = 0; i < dtpos.Rows.Count; i++)
{
cboPos.Items.Add(dtpos.Rows[i][0] + " | " + dtpos.Rows[i][1]);
}
cboPos.SelectedIndex = 7;
// cls.DongKetNoi();
}
