private void Window_Loaded(object sender, RoutedEventArgs e)
{
dtpNgay.SelectedDate = DateTime.Now.AddDays(-1);
//MessageBox.Show(str.Left(str.Right(dtpNgay.SelectedDate.Value.ToString("dd/MM/yyyy"),8),1));
string kitu = str.Left(str.Right(dtpNgay.SelectedDate.Value.ToString("dd/MM/yyyy"), 8), 1);
//MessageBox.Show(kitu);
if (str.Left(str.Right(dtpNgay.SelectedDate.Value.ToString("dd/MM/yyyy"), 8), 1) != "/")
{
MessageBox.Show("Vào Control Panel định dạng lại ngày tháng theo dd/MM/yyyy", "Eror", MessageBoxButton.OK, MessageBoxImage.Error);
//WpfKhGnTn f = new WpfKhGnTn();
this.Close();
// return;
}
CboPos.SelectedIndex = 5;
try
{
cls.ClsConnect();
//string sql = "select PO_MA,PO_TEN from DMPOS ";
string sql = "select PO_MA,PO_TEN from DMPOS where PO_MA= '" + BienBll.NdMadv + "'";
dtpos = cls.LoadDataText(sql);
for (int i = 0; i < dtpos.Rows.Count; i++)
{
CboPos.Items.Add(dtpos.Rows[i][0] + " | " + dtpos.Rows[i][1]);
}
}
catch (Exception ex)
{
MessageBox.Show("Tiếp tục" + ex.Message, "Mess");
}
cls.DongKetNoi();
}
private void btnOk_Click(object sender, RoutedEventArgs e)
{
cls.ClsConnect();
DataTable dt = new DataTable();
int thamso = 4;
string[] bien = new string[thamso];
object[] giatri = new object[thamso];
bien[0] = "@MaPos";
giatri[0] = str.Left(CboPos.SelectedValue.ToString().Trim(), 6);
bien[1] = "@Ngay";
if (dtpNgay.SelectedDate == null)
{
MessageBox.Show("Chưa chọn ngày", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
else
{
giatri[1] = dtpNgay.SelectedDate.Value.ToString("dd/MM/yyyy");
}
bien[2] = "@MaXa";
giatri[2] = str.Left(CboXa.SelectedValue.ToString().Trim(), 6);
bien[3] = "@Mau";
if (radioButton1.IsChecked == true)
{
giatri[3] = '1';
}
else
{
giatri[3] = '2';
}
dt = cls.LoadDataProcPara("usp_Pnkt", bien, giatri, thamso);
if (dt.Rows.Count > 0)
{
if (radioButton1.IsChecked == true)
{
FileName = Thumuc + "\\" + str.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_PNKT_" +
dtpNgay.SelectedDate.Value.ToString("ddMMyyyy_") + str.Right(CboXa.SelectedValue.ToString().Trim(), 2) + ".csv";
}
else
{
FileName = Thumuc + "\\" + str.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_CHTRINH_" +
dtpNgay.SelectedDate.Value.ToString("ddMMyyyy_") + str.Right(CboXa.SelectedValue.ToString().Trim(), 2) + ".csv";
}
FileStream fs = new FileStream(FileName, FileMode.Create);
StreamWriter sw = new StreamWriter(fs, Encoding.Unicode);
//bll.WriteDataTableToExcel(dt, "Details", FileName, "tutm : 0985165777");
str.ToCSV(dt, sw, true);
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
else
{
MessageBox.Show("Chưa có số liệu", "Thông báo");
}
cls.DongKetNoi();
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
cls.ClsConnect();
string field = "KH" + bll.Right(BienBll.NdMadv.Trim(), 2);
string str = "select STT,CHITIEU," + field + " KH from KHTC where NHAPTAY='T'";
dt = cls.LoadDataText(str);
dgvData.ItemsSource = dt.DefaultView;
cls.DongKetNoi();
}
private void OK_OnClick(object sender, RoutedEventArgs e)
{
try
{
_cls.ClsConnect();
int thamso = 6;
string[] bien = new string[thamso];
object[] giatri = new object[thamso];
foreach (DataRow dr in _dt.Rows)
{
bien[0] = "@MaPos";
giatri[0] = dr[2];
bien[1] = "@Nam";
giatri[1] = dr[0];
bien[2] = "@MaXa";
giatri[2] = dr[5];
bien[3] = "@STT";
giatri[3] = dr[7];
bien[4] = "@Diem";
giatri[4] = dr[12];
bien[5] = "@MOTA";
giatri[5] = dr[17];
// MessageBox.Show(dr[0].ToString());
// MessageBox.Show("Mapos : "+giatri[0]+" Nam: "+giatri[1]+" Maxa"+ giatri[2]+" STT"+ giatri[3]+" Diem"+giatri[4]);
_cls.UpdateDataProcPara("usp_UpdateCHAMDIEM_GDX", bien, giatri, thamso);
}
_cls.ClsConnect();
int thamso1 = 3;
string[] bien1 = new string[thamso1];
object[] giatri1 = new object[thamso1];
bien1[0] = "@MaPos";
giatri1[0] = bll.Left(CboPos.SelectedValue.ToString().Trim(), 6);
bien1[1] = "@Nam";
giatri1[1] = bll.Right(dtpNgay.SelectedDate.Value.ToString("dd/MM/yyyy"), 4);
bien1[2] = "@MaXa";
giatri1[2] = bll.Left(CboXa.SelectedValue.ToString().Trim(), 6);
_cls.UpdateDataProcPara("usp_UpdateCHAMDIEMGDX", bien1, giatri1, thamso1);
MessageBox.Show("Save data OK", "Mess", MessageBoxButton.OK, MessageBoxImage.Information);
}
catch (Exception ex)
{
MessageBox.Show("Error \n" + ex.Message, "Mess", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
_cls.DongKetNoi();
}
}
private void ShowGrid_OnMouseDown(object sender, MouseButtonEventArgs e)
{
try
{
DateTime newdate = new DateTime(dtpNgay.SelectedDate.Value.AddYears(-1).Year + 1, 1, 1);
//Enday off year
//MessageBox.Show(newdate.AddDays(-1).ToString("yyyy-MM-dd"));
cls.ClsConnect();
string sqlload =
"select a.* from LUU_TSCC a " +
"where a.TRANGTHAI='A' and a.POS_CD='" + CboPos.SelectedValue +
"' and right(MAPHONG,2)='" + s.Right(CboPhong.SelectedValue.ToString().Trim(), 2) + "' order by a.MA_NHANHIEU_TS,a.MA_TS";
//MessageBox.Show(sqlload);
dt = cls.LoadDataText(sqlload);
if (dt.Rows.Count > 0)
{
dgvData.ItemsSource = dt.DefaultView;
}
else
{
MessageBox.Show("Không có dữ liệu !", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
/*
* private void Ration3_Checked(object sender, RoutedEventArgs e)
* {
* Close();
* WpfDinhSv f = new WpfDinhSv();
* f.ShowDialog();
* }
*
* private void Ration2_Checked(object sender, RoutedEventArgs e)
* {
* Close();
* WpfDinhSv f = new WpfDinhSv();
* f.ShowDialog();
* }
*/
private void LblThKe_OnMouseDown(object sender, MouseButtonEventArgs e)
{
try
{
cls.ClsConnect();
bll.TaoThuMuc(Thumuc);
const int thamso = 5;
string[] bien = new string[thamso];
object[] giatri = new object[thamso];
bien[0] = "@MaPos";
giatri[0] = bll.Left(CboPos.SelectedValue.ToString().Trim(), 6);
bien[1] = "@MaXa";
if (bll.Right(giatri[0].ToString(), 2) == "00")
{
giatri[1] = giatri[0];
}
else
{
giatri[1] = bll.Left(CboXa.SelectedValue.ToString().Trim(), 6);
}
bien[2] = "@Ngay";
if (dtpNgay.SelectedDate != null)
{
giatri[2] = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
bien[3] = "@TuNgay";
if (dtpTuNgay.SelectedDate != null)
{
giatri[3] = dtpTuNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
bien[4] = "@DenNgay";
if (dtpDenNgay.SelectedDate != null)
{
giatri[4] = dtpDenNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
}
}
}
//MessageBox.Show(giatri[0].ToString() + giatri[1].ToString() + giatri[2].ToString() + giatri[3].ToString() + giatri[4].ToString());
dt = cls.LoadDataProcPara("usp_DinhSvThKe", bien, giatri, thamso);
if (dt.Rows.Count > 0)
{
FileName = Thumuc + "\\" + giatri[0] + "_" + giatri[1] + "_THONGKE_HSSV_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
bll.ExportToExcel(dt, FileName);
//bll.ExportDTToExcel(dt,FileName);
//bll.WriteDataTableToExcel(dt, "Details", FileName, "tutm : 0985165777");
//bll.ToCSV(dt, sw, true);
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
//bll.OpenCSVWithExcel(FileName);
bll.OpenExcel(FileName);
}
else
{
MessageBox.Show("Không có bản ghi nào ", "Mess");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
cls.DongKetNoi();
}
private void LblGetData_OnMouseDown(object sender, MouseButtonEventArgs e)
{
try
{
upda = false;
cls.ClsConnect();
if (dtpNgay.SelectedDate != null)
{
var dtchk =
cls.LoadDataText("select * from KHAOSAT where MAU='" + _mau + "' and POS='" +
str.Right(str.Left(CboPos.SelectedValue.ToString().Trim(), 6), 4) + "' and NAM=" + dtpNgay.SelectedDate.Value.ToString("yyyy"));
if (dtchk.Rows.Count > 0)
{
sql = "select * from KHAOSAT where MAU='" + _mau + "' and POS='" +
str.Right(str.Left(CboPos.SelectedValue.ToString().Trim(), 6), 4) + "' and NAM=" + dtpNgay.SelectedDate.Value.ToString("yyyy");
upda = true;
}
else if (dtpNgay.SelectedDate != null)
{
sql = "select '" + _mau + "' MAU," + dtpNgay.SelectedDate.Value.ToString("yyyy") + " NAM," + dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd") + " NGAY" +
"," + str.Left(CboPos.SelectedValue.ToString().Trim(), 6) + " POS,'" + _ct + "' CHTR,b.MA MAXA" +
",ROW_NUMBER() OVER (Order by b.MA) COT1,b.TEN COT2,cast(0 as NUMERIC(18,0)) COT3,cast(0 as NUMERIC(18,0)) COT4" +
",cast(0 as NUMERIC(18,0)) COT5 ,cast(0 as NUMERIC(18, 0)) COT6,cast(0 as NUMERIC(18, 0)) COT7,cast(0 as NUMERIC(18, 0)) COT8" +
",cast(0 as NUMERIC(18, 0)) COT9,cast(0 as NUMERIC(18, 0)) COT10,cast(0 as NUMERIC(18, 0)) COT11,cast(0 as NUMERIC(18, 0)) COT12" +
",cast(0 as NUMERIC(18, 0)) COT13,cast(0 as NUMERIC(18, 0)) COT14 from DMXA b " +
" where b.PGD_QL='" + str.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "' and right(b.MA,2)<>'00'";
}
}
dt = cls.LoadDataText(sql);
if (dt.Rows.Count > 0)
{
dgvSource.ItemsSource = dt.DefaultView;
}
else
{
MessageBox.Show("Chưa có số liệu", "Thông báo");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
cls.DongKetNoi();
}
private void btnOK_Click(object sender, RoutedEventArgs e)
{
try
{
cls.ClsConnect();
if (TxtMa.Text.Trim() == "")
{
MessageBox.Show("Hãy chọn sản phẩm cần thay đổi !", "Thông báo", MessageBoxButton.OK,
MessageBoxImage.Warning);
}
else
{
string strkt = "select mapos, nd_ma from dmvpp where ma='" + TxtMa.Text.Trim() + "'";
var dtkt = cls.LoadDataText(strkt);
if (dtkt.Rows.Count > 0)
{
DataRow dtr = dtkt.Rows[0];
string ndma = (string)dtr["ND_MA"];
string mapos = (string)dtr["MAPOS"];
if (BienBll.NdMadv == mapos && BienBll.Ndma == ndma)
{
string upd = "update DMVPP set G" + bll.Right(BienBll.NdMadv.Trim(), 2) + "=" +
TxtGia.Text.Trim()
+ ",TEN=N'" + TxtTen.Text.Trim() + "',DONVI=N'" + TxtDv.Text.Trim() +
"',QUYCACH=N'" + TxtQc.Text.Trim() + "' where MA='" + TxtMa.Text.Trim() + "'";
// MessageBox.Show(upd);
cls.UpdateDataText(upd);
MessageBox.Show("Cập nhật thành công sản phẩm : " + TxtTen.Text, "Thông báo",
MessageBoxButton.OK, MessageBoxImage.Information);
}
else
{
MessageBox.Show("Bạn không có quyền cập nhật thông tin của đơn vị hoặc người khác");
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
cls.DongKetNoi();
}
private void btnWrite_Click(object sender, RoutedEventArgs e)
{
if (txtPath.Text == "")
{
MessageBox.Show("Chưa chọn file Excel", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
else
{
try
{
//MessageBox.Show(bll.Left(bll.Right(txtPath.Text.Trim(), 16), 12)+'_'+bll.Right(bll.Left(CboSheet.SelectedValue.ToString().Trim(), 3),2));
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";
//string[] arrStr = FileName.Split('\\');
Encoding encode = Encoding.BigEndianUnicode;
_fw = new FileStream(FileName, FileMode.Create, FileAccess.Write, FileShare.None);
StreamWriter sw = new StreamWriter(_fw, encode);
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i + 1 < dt.Columns.Count)
{
sw.Write(row[i].ToString() + "$");
}
else
{
sw.Write(row[i].ToString());
}
}
sw.WriteLine();
}
sw.Close();
MessageBox.Show("Export OK", "Mess", MessageBoxButton.OK, MessageBoxImage.Information);
btnWrite.IsEnabled = false;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
string str = "";
// dtpNgay.SelectedDate = DateTime.Parse("31/12/" + DateTime.Now.AddYears(-1).ToString("yyyy"));
cls.ClsConnect();
if (BienBll.NdMadv == BienBll.MainPos)
{
str = "select * from VARMCN where MAU='QT' and NHAPTAY='T'";
}
else
{
string field = "CN" + bll.Right(BienBll.NdMadv, 2);
str = "select STT,TENBIEN," + field + " from VARMCN where MAU='QT'and NHAPTAY='T' ";
}
dt = cls.LoadDataText(str);
dgvData.ItemsSource = dt.DefaultView;
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 ShowGrid_OnMouseDown(object sender, MouseButtonEventArgs e)
{
try
{
string ng = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
DateTime newdate = new DateTime(dtpNgay.SelectedDate.Value.AddYears(-1).Year + 1, 1, 1);
DateTime LastDayYear = new DateTime(dtpNgay.SelectedDate.Value.Year, 12, 31);
string mau = ""; //1 đang dùng , 2 : thanh lý . 3 ; mua mới
//Enday off year
//MessageBox.Show(newdate.AddDays(-1).ToString("yyyy-MM-dd"));
cls.ClsConnect();
if (Ration1.IsChecked == true) //Đang dùng
{
//dt.Clear();
//string sqlload =
// "select a.MA_TS,a.MA_NHANHIEU_TS,a.TEN_TS,a.SO_LUONG,a.NGUYEN_GIA,a.NGAY_MUA,a.TENPHONG from QT_TSCC a " +
// "where a.NGAYBC='" + dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd") + "' and a.POS_CD='" +
// CboPos.SelectedValue +
// "' and LOAI_TS_CHITIET='" + CboLoaiTSCT.SelectedValue + "' and right(MAPHONG,2)='"+s.Right(CboPhong.SelectedValue.ToString().Trim(),2)+"' order by a.MA_NHANHIEU_TS,a.MA_TS";
//MessageBox.Show(sqlload);
//,b.PO_TEN from LUU_TSCC a left join DMPOS b on a.POS_CD=b.PO_MA
mau = "1";
string sqlload =
"select '" + mau + "' MAU,'" + ng + "' NGAY,a.*,b.PO_TEN from LUU_TSCC a left join DMPOS b on a.POS_CD=b.PO_MA " +
"where a.TRANGTHAI='A' and a.POS_CD='" + CboPos.SelectedValue + "' and right(MAPHONG,2)='" + s.Right(CboPhong.SelectedValue.ToString().Trim(), 2) + "' order by a.LOAI_TS_CHITIET,a.MA_NHANHIEU_TS,a.MA_TS";
//MessageBox.Show(sqlload);
dt = cls.LoadDataText(sqlload);
if (dt.Rows.Count > 0)
{
dgvData.ItemsSource = dt.DefaultView;
}
else
{
MessageBox.Show("Không có dữ liệu !", "Thông báo", MessageBoxButton.OK,
MessageBoxImage.Information);
}
}
else if (Ration2.IsChecked == true) // thanh ly
{
//// dt.Clear();
// string str =
// "select a.* from QT_TSCC a " +
// "where a.NGAYBC='" + newdate.AddDays(-1).ToString("yyyy-MM-dd") + "' and a.POS_CD='" +
// CboPos.SelectedValue + "'and LOAI_TS_CHITIET='" + CboLoaiTSCT.SelectedValue + "' and right(MAPHONG,2)='" + s.Right(CboPhong.SelectedValue.ToString().Trim(), 2) + "' and a.MA_TS not in " +
// " (select MA_TS from QT_TSCC where NGAYBC='" +
// dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd") + "' " +
// "and POS_CD='" + CboPos.SelectedValue +
// "' and MA_TS=a.MA_TS) order by a.MA_NHANHIEU_TS,a.MA_TS";
// //MessageBox.Show(str);
// dt = cls.LoadDataText(str);
// if (dt.Rows.Count > 0)
// {
// dgvData.ItemsSource = dt.DefaultView;
// }
// else
// MessageBox.Show("Không có dữ liệu !", "Thông báo", MessageBoxButton.OK,
// MessageBoxImage.Information);
mau = "2";
string str = "select '" + mau + "' MAU,'" + ng + "' NGAY,a.*,b.PO_TEN from LUU_TSCC a left join DMPOS b on a.POS_CD=b.PO_MA where a.POS_CD='" +
CboPos.SelectedValue + "' and right(MAPHONG,2)='" + s.Right(CboPhong.SelectedValue.ToString().Trim(), 2) + "'" +
" and a.TRANGTHAI='C' and a.MA_TS " +
" in (select MA_TS from QT_TSCC where NGAYBC = '" + newdate.AddDays(-1).ToString("yyyy-MM-dd") + "')" +
" and a.MA_TS not in (select MA_TS from QT_TSCC where NGAYBC = '" + LastDayYear.ToString("yyyy-MM-dd") + "') order by a.LOAI_TS_CHITIET,a.MA_NHANHIEU_TS,a.MA_TS";
dt = cls.LoadDataText(str);
if (dt.Rows.Count > 0)
{
dgvData.ItemsSource = dt.DefaultView;
}
else
{
MessageBox.Show("Không có dữ liệu !", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
//MessageBox.Show(newdate.AddDays(-1).ToString("yyyy-MM-dd")+" "+ LastDayYear.ToString("yyyy-MM-dd"));
}
else // mua moi
{
// dt.Clear();
//string strmoi =
//"select a.* from QT_TSCC a " +
//"where a.NGAYBC='" + dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd") + "' and a.POS_CD='" +
//CboPos.SelectedValue + "'and LOAI_TS_CHITIET='" + CboLoaiTSCT.SelectedValue + "' and right(MAPHONG,2)='" + s.Right(CboPhong.SelectedValue.ToString().Trim(), 2) + "' and a.MA_TS not in " +
//" (select MA_TS from QT_TSCC where NGAYBC='" +
//newdate.AddDays(-1).ToString("yyyy-MM-dd") + "' " +
//"and POS_CD='" + CboPos.SelectedValue +
//"' and MA_TS=a.MA_TS) order by a.MA_NHANHIEU_TS,a.MA_TS";
mau = "3";
string str = "select '" + mau + "' MAU,'" + ng + "' NGAY,a.*,b.PO_TEN from LUU_TSCC a left join DMPOS b on a.POS_CD=b.PO_MA where a.POS_CD='" +
CboPos.SelectedValue + "' and right(a.MAPHONG,2)='" + s.Right(CboPhong.SelectedValue.ToString().Trim(), 2) + "' and year(a.NGAY_MUA)='" + dtpNgay.SelectedDate.Value.ToString("yyyy") + "' order by a.LOAI_TS_CHITIET,a.MA_NHANHIEU_TS,a.MA_TS";
//MessageBox.Show(str);
dt = cls.LoadDataText(str);
if (dt.Rows.Count > 0)
{
dgvData.ItemsSource = dt.DefaultView;
}
else
{
MessageBox.Show("Không có dữ liệu !", "Thông báo", MessageBoxButton.OK,
MessageBoxImage.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void btnOk_Click(object sender, RoutedEventArgs e)
{
str.TaoThuMuc(Thumuc);
try
{
cls.ClsConnect();
int thamso = 6;
string[] bien = new string[thamso];
object[] giatri = new object[thamso];
bien[0] = "@MaPos";
giatri[0] = str.Left(cboPos.SelectedValue.ToString().Trim(), 6);
bien[1] = "@TuNgay";
giatri[1] = dtpTuNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
bien[2] = "@DenNgay";
giatri[2] = dtpDenNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
bien[3] = "@Chtr";
giatri[3] = str.Left(CboChon.SelectedValue.ToString().Trim(), 2);
bien[4] = "@Mau";
if (OptSke.IsChecked == true)
{
giatri[4] = "0";
}
else if (OptChtr.IsChecked == true)
{
giatri[4] = "1"; // chi tiet theo chuong trinh
}
else if (OptM03.IsChecked == true)
{
giatri[4] = "2"; //
}
bien[5] = "@Ngay";
giatri[5] = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
/*
* else if (OptM01.IsChecked==true) giatri[4] = "A";
* else if (OptM02.IsChecked == true) giatri[4] = "B";//Tông hợp tất cả các chương trình theo xã
*/
if (OptSke.IsChecked == true)
{
if (str.Right(giatri[0].ToString(), 2) == "00")
{
strsql = " with lst1 as ( "
+ "select a.KU_MAPGD, left(a.KU_MADP, 6) MAXA, a.KU_MATO, a.KU_MAKH, a.KU_SOKU, a.KU_CHTRINH, a.KU_NGAYGNCC NG_VAY, a.KU_NGAYDHAN_1, a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH DUNO "
+ ", dbo.SoThang(a.KU_NGAYGNCC, a.KU_NGAYDHAN_1) SOTHANG, a.KU_MAPNKT51 from HSCV_DAILY a where a.KU_NGAYBC = '" + giatri[5] + "' and a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH > 0 and a.KU_TTMONVAY <> 'CLOSE' and a.KU_CHTRINH IN('01', '09', '19', '21') "
+ " ), lst1a as ( select a.* from lst1 a where a.NG_VAY between '" + giatri[1] + "' and '" + giatri[2] + "' )"
+ ", lst1b as ( select a.* from lst1 a, lst1a b where a.KU_MAKH = b.KU_MAKH ), lst2 as ( select a.KU_MAKH,count(a.KU_MAKH) DEM,sum(a.DUNO) DUNO from lst1b a group by a.KU_MAKH having sum(a.DUNO) > 50000000 )"
+ " select a.KU_MAPGD,a.MAXA,d.TEN,a.KU_MATO,f.TO_TENTT,a.KU_MAKH,c.KH_TENKH,char(39) + a.KU_SOKU SOKU, a.KU_CHTRINH,e.TENVT, a.NG_VAY, a.KU_NGAYDHAN_1 NG_DHAN, a.DUNO, a.SOTHANG, a.KU_MAPNKT51 PNKT, g.TEN TEN_PNKT"
+ " , (case when(a.NG_VAY < '" + giatri[1] + "' and b.DEM > 1) or (a.NG_VAY > '" + giatri[1] + "' and b.DEM = 1) then 1 else 2 end) LAN ,(case when (a.NG_VAY<'" + giatri[1] + "' and b.DEM>1) or (a.NG_VAY>'" + giatri[1] + "' and b.DEM=1) then a.DUNO else 0 end) DN_LAN1"
+ " ,(case when a.NG_VAY >= '" + giatri[1] + "' and b.DEM>1 then a.DUNO else 0 end) DN_LAN2 from lst1 a, lst2 b,hskh c, DMXA d,DM_CHTRINH e, HSTO f,DM_PNKT g "
+ " where a.KU_MAKH = b.KU_MAKH and a.KU_MAKH = c.KH_MAKH and a.MAXA = d.MA and a.KU_CHTRINH = e.CHTRINH and a.KU_MATO = f.TO_MATO and a.KU_MAPNKT51 = g.PNKT and c.KH_TTRANG='A' order by a.MAXA,a.KU_MAKH,a.KU_CHTRINH,a.NG_VAY";
}
else
{
strsql = " with lst1 as ( "
+ "select a.KU_MAPGD, left(a.KU_MADP, 6) MAXA, a.KU_MATO, a.KU_MAKH, a.KU_SOKU, a.KU_CHTRINH, a.KU_NGAYGNCC NG_VAY, a.KU_NGAYDHAN_1, a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH DUNO "
+ ", dbo.SoThang(a.KU_NGAYGNCC, a.KU_NGAYDHAN_1) SOTHANG, a.KU_MAPNKT51 from HSCV_DAILY a where a.KU_MAPGD = '" + giatri[0] + "' and a.KU_NGAYBC = '" + giatri[5] + "' and a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH > 0 and a.KU_TTMONVAY <> 'CLOSE' and a.KU_CHTRINH IN('01', '09', '19', '21') "
+ " ), lst1a as ( select a.* from lst1 a where a.NG_VAY between '" + giatri[1] + "' and '" + giatri[2] + "' )"
+ ", lst1b as ( select a.* from lst1 a, lst1a b where a.KU_MAKH = b.KU_MAKH ), lst2 as ( select a.KU_MAKH,count(a.KU_MAKH) DEM,sum(a.DUNO) DUNO from lst1b a group by a.KU_MAKH having sum(a.DUNO) > 50000000 )"
+ " select a.KU_MAPGD,a.MAXA,d.TEN,a.KU_MATO,f.TO_TENTT,a.KU_MAKH,c.KH_TENKH,char(39) + a.KU_SOKU SOKU, a.KU_CHTRINH,e.TENVT, a.NG_VAY, a.KU_NGAYDHAN_1 NG_DHAN, a.DUNO, a.SOTHANG, a.KU_MAPNKT51 PNKT, g.TEN TEN_PNKT"
+ " , (case when(a.NG_VAY < '" + giatri[1] + "' and b.DEM > 1) or (a.NG_VAY > '" + giatri[1] + "' and b.DEM = 1) then 1 else 2 end) LAN ,(case when (a.NG_VAY<'" + giatri[1] + "' and b.DEM>1) or (a.NG_VAY>'" + giatri[1] + "' and b.DEM=1) then a.DUNO else 0 end) DN_LAN1"
+ " ,(case when a.NG_VAY >= '" + giatri[1] + "' and b.DEM>1 then a.DUNO else 0 end) DN_LAN2 from lst1 a, lst2 b,hskh c, DMXA d,DM_CHTRINH e, HSTO f,DM_PNKT g "
+ " where a.KU_MAKH = b.KU_MAKH and a.KU_MAKH = c.KH_MAKH and a.MAXA = d.MA and a.KU_CHTRINH = e.CHTRINH and a.KU_MATO = f.TO_MATO and a.KU_MAPNKT51 = g.PNKT and c.KH_TTRANG='A' order by a.MAXA,a.KU_MAKH,a.KU_CHTRINH,a.NG_VAY";
}
dt = cls.LoadDataText(strsql);
FileName = Thumuc + "\\" + str.Left(cboPos.SelectedValue.ToString().Trim(), 6) + "_Cho vay nâng mức_từ ngày " + dtpTuNgay.SelectedDate.Value.ToString("ddMMyyyy") + "_Đến ngày_" + dtpDenNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
str.ExportToExcel(dt, FileName);
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
str.OpenExcel(FileName);
}
else if (str.Right(giatri[0].ToString(), 2) == "00")
{
dt = cls.LoadDataProcPara("usp_ChovayNangmuc03", bien, giatri, thamso);
}
else
{
dt = cls.LoadDataProcPara("usp_ChovayNangmuc", bien, giatri, thamso);
}
if (dt.Rows.Count > 0)
{
if (OptChtr.IsChecked == true)
{
rpt_ChovayNangmuc01 rpt = new rpt_ChovayNangmuc01();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
// MessageBox.Show(OptChtr.Content.ToString());
FileName = Thumuc + "\\" + giatri[0] + "_" + OptChtr.Content + "_" + str.Left(CboChon.SelectedValue.ToString().Trim(), 2) + "_" + giatri[1] + "_Đến ngày_" + giatri[2] + ".xlsx";
}
else if (OptM03.IsChecked == true)
{
rpt_ChovayNangmuc03 rpt = new rpt_ChovayNangmuc03();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
FileName = Thumuc + "\\" + giatri[0] + "_Mãu 03BC_" + str.Left(CboChon.SelectedValue.ToString().Trim(), 2) + "_" + giatri[1] + "_Đến ngày_" + giatri[2] + ".xlsx";
}
// str.ExportToExcel(dt, FileName);
// str.OpenExcel(FileName);
}
else
{
MessageBox.Show("Chưa có số liệu", "Thông báo");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
cls.DongKetNoi();
}
private void BtnOk_OnClick(object sender, RoutedEventArgs e)
{
_cls.ClsConnect();
try
{
sql = "select * from U_HSTD where NGAYKU='" + dtpNgay.SelectedDate.Value.ToString("dd/MM/yyyy") + "'";
_dt = _cls.LoadDataText(sql);
if (_dt.Rows.Count == 0)
{
MessageBox.Show("Chưa có HSTDCT ngày " + dtpNgay.SelectedDate.Value.ToString("dd/MM/yyyy"), "Thông báo", MessageBoxButton.OK, MessageBoxImage.Warning);
Close();
}
else
{
sql = "select top 1 * from LUU_DIENBAO where NGAY='" + dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd") + "'";
_dt = _cls.LoadDataText(sql);
#region
if (_dt.Rows.Count == 0)
{
int thamso = 1;
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");
}
_dt = _cls.LoadDataProcPara("usp_DienBao", bien, giatri, thamso);
}
if (BienBll.NdMadv.Trim() == "003005")
{
sql = "select * from LUU_DIENBAO where CHONIN='1' and NGAY='" + dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd") + "' order by TT";
}
else
{
sql = "select " + BienBll.NdMadv.Trim() + " P01, NGAY,STT,TT,TENCT,P" + _str.Right(BienBll.NdMadv.Trim(), 2) + " TONG,Indam,NHOM,SUB_NHOM from LUU_DIENBAO where CHONIN='1' and NGAY='" + dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd") + "' and P" + _str.Right(BienBll.NdMadv.Trim(), 2) + ">0 order by TT";
}
//MessageBox.Show(sql);
_dt = _cls.LoadDataText(sql);
if (BienBll.NdMadv.Trim() == "003005")
{
rpt_Dienbao rpt = new rpt_Dienbao();
RPUtility.ShowRp(rpt, _dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
/*
* ExportOptions CrExportOptions;
* DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions();
* ExcelFormatOptions CrFormatTypeOptions = new ExcelFormatOptions();
* CrDiskFileDestinationOptions.DiskFileName = "c:\\text\\csharp.net-informations.xls";
* CrExportOptions = rpt.ExportOptions;
* CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
* CrExportOptions.ExportFormatType = ExportFormatType.Excel;
* CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions;
* CrExportOptions.FormatOptions = CrFormatTypeOptions;
* rpt.Export();
*/
//rpt.ExportToDisk(ExportFormatType.Excel, "c:\\text\\DB.xls");
}
else
{
rpt_DienbaoH rpt = new rpt_DienbaoH();
RPUtility.ShowRp(rpt, _dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(),
srv.DbPassSerVer());
}
}
#endregion
}
catch (Exception ex)
{
MessageBox.Show("Error" + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
_cls.DongKetNoi();
}
private void ShowGrid_OnMouseDown(object sender, MouseButtonEventArgs e)
{
try
{
DateTime newdate = new DateTime(dtpNgay.SelectedDate.Value.AddYears(-1).Year + 1, 1, 1);
string ng = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
//Enday off year
//MessageBox.Show(newdate.AddDays(-1).ToString("yyyy-MM-dd"));
cls.ClsConnect();
string mau = "";
mau = "5";
string sqlload =
"select '" + mau + "' MAU,'" + ng + "' NGAY,a.*,b.PO_TEN from LUU_TSCC a left join DMPOS b on a.POS_CD=b.PO_MA " +
"where a.TRANGTHAI='A' and a.POS_CD='" + CboPos.SelectedValue + "' and right(MAPHONG,2)='" + s.Right(CboPhong.SelectedValue.ToString().Trim(), 2) + "' order by a.LOAI_TS_CHITIET,a.MA_NHANHIEU_TS,a.MA_TS";
//string sqlload =
// "select '4' MAU,'" + dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd") + "' NGAY,a.MA_TS, a.TEN_TS,a.LOAI_TS,a.TEN_LOAI_TS"
// +",a.LOAI_TS_CHITIET,a.TEN_LOAI_TS_CHITIET,a.MA_NHANHIEU_TS,a.TEN_NHANHIEU_TS,a.NGUYEN_GIA,a.SO_LUONG,a.VON_TW,a.VON_DP"
// +",a.VON_KHAC,a.HAOMON_LK,a.POS_CD,a.MAIN_POS,a.NAMQT,a.NGAYTAO,a.NGAYBC,a.NGAY_MUA,a.MAPHONG,a.TENPHONG,a.MOTA,a.CB_QUANLY"
// +",a.TRANGTHAI,a.NGAY,a.GDX,a.DG_THANHLY"
// +",b.PO_TEN from LUU_TSCC a left join DMPOS b on a.POS_CD=b.PO_MA " +
// "where a.TRANGTHAI='A' and a.POS_CD='" + CboPos.SelectedValue + "' and right(MAPHONG,2)='" + s.Right(CboPhong.SelectedValue.ToString().Trim(), 2) + "' order by a.LOAI_TS_CHITIET,a.MA_NHANHIEU_TS,a.MA_TS";
//"select '4' MAU,a.* from LUU_TSCC a " +
//"where a.POS_CD='" +CboPos.SelectedValue +
//"' and right(MAPHONG,2)='"+s.Right(CboPhong.SelectedValue.ToString().Trim(),2)+"' order by a.MA_NHANHIEU_TS,a.MA_TS";
//MessageBox.Show(sqlload);
dt = cls.LoadDataText(sqlload);
if (dt.Rows.Count > 0)
{
dgvData.ItemsSource = dt.DefaultView;
}
else
{
MessageBox.Show("Không có dữ liệu !", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void ShowGrid_OnMouseDown(object sender, MouseButtonEventArgs e)
{
try
{
DateTime newdate = new DateTime(dtpNgay.SelectedDate.Value.AddYears(-1).Year + 1, 1, 1);
string ng = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
cls.ClsConnect();
string sqlload =
"select *,'" + ng + "' NG_DG_SC,'' LYDO,'' DENGHI ,'0' MNV,'" + ng + "' NG_BAOTRI from LUU_TSCC " +
"where LOAI_TS_CHITIET='TI1' and TRANGTHAI='A' and POS_CD='" + CboPos.SelectedValue + "' and right(MAPHONG,2)='" + s.Right(CboPhong.SelectedValue.ToString().Trim(), 2) + "' order by LOAI_TS_CHITIET,MA_NHANHIEU_TS,MA_TS";
dt = cls.LoadDataText(sqlload);
if (dt.Rows.Count > 0)
{
dgvData.ItemsSource = dt.DefaultView;
}
else
{
MessageBox.Show("Không có dữ liệu !", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void ShowGrid_OnMouseDown(object sender, MouseButtonEventArgs e)
{
try
{
string ng = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
DateTime newdate = new DateTime(dtpNgay.SelectedDate.Value.AddYears(-1).Year + 1, 1, 1);
DateTime LastDayYear = new DateTime(dtpNgay.SelectedDate.Value.Year, 12, 31);
cls.ClsConnect();
string sqlload =
"select CAST (0 AS bit) AS CHON,'" + ng + "' NGAY,a.*,b.PO_TEN,c.LYDO,c.DENGHI from LUU_TSCC a left join DMPOS b on a.POS_CD=b.PO_MA " +
" left join TSCC_HIS c on a.MA_TS=c.MA_TS and c.NG_DG_SC='" + ng + "' where a.LOAI_TS_CHITIET='TI1' and a.TRANGTHAI='A' and a.POS_CD='" + CboPos.SelectedValue + "' and right(a.MAPHONG,2)='" + s.Right(CboPhong.SelectedValue.ToString().Trim(), 2) + "' order by a.LOAI_TS_CHITIET,a.MA_NHANHIEU_TS,a.MA_TS";
//MessageBox.Show(sqlload);
dt = cls.LoadDataText(sqlload);
if (dt.Rows.Count > 0)
{
dgvData.ItemsSource = dt.DefaultView;
}
else
{
MessageBox.Show("Không có dữ liệu !", "Thông báo", MessageBoxButton.OK,
MessageBoxImage.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void btnOk_Click(object sender, RoutedEventArgs e)
{
#region
try
{
if (str.Right(str.Left(CboPos.SelectedValue.ToString().Trim(), 6), 2) == "00")
{
MessageBox.Show("Không chọn POS tổng hợp", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Warning);
}
else
{
cls.ClsConnect();
if (Ration1.IsChecked == true)
{
int thamso = 2;
string[] bien = new string[thamso];
object[] giatri = new object[thamso];
bien[0] = "@NgayKu";
if (dtpNgayKu.SelectedDate != null)
{
giatri[0] = dtpNgayKu.SelectedDate.Value.ToString("yyyy-MM-dd");
}
bien[1] = "@MaPos";
giatri[1] = str.Left(CboPos.SelectedValue.ToString().Trim(), 6);
dt = cls.LoadLdbf("usp_SlVungBien", bien, giatri, thamso);
if (dt.Rows.Count > 0)
{
rpt_SlVungBien rpt = new rpt_SlVungBien();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
}
else
{
MessageBox.Show("Không có dữ liệu", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
else if (Ration2.IsChecked == true)
{
int thamso1 = 3;
string[] bien = new string[thamso1];
object[] giatri = new object[thamso1];
bien[0] = "@NgayKu";
if (dtpNgayKu.SelectedDate != null)
{
giatri[0] = dtpNgayKu.SelectedDate.Value.ToString("yyyy-MM-dd");
}
bien[1] = "@MaPos";
giatri[1] = str.Left(CboPos.SelectedValue.ToString().Trim(), 6);
bien[2] = "@MaXa";
giatri[2] = str.Left(cboXa.SelectedValue.ToString().Trim(), 6);
dt = cls.LoadLdbf("usp_SlVungBienCT", bien, giatri, thamso1);
if (dt.Rows.Count > 0)
{
rpt_SlVungBienCt rpt = new rpt_SlVungBienCt();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
}
else
{
MessageBox.Show("Không có dữ liệu", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
else
{
int thamso2 = 1;
string[] bien = new string[thamso2];
object[] giatri = new object[thamso2];
bien[0] = "@NgayKu";
if (dtpNgayKu.SelectedDate != null)
{
giatri[0] = dtpNgayKu.SelectedDate.Value.ToString("yyyy-MM-dd");
}
dt = cls.LoadLdbf("usp_SlVungBienTH", bien, giatri, thamso2);
if (dt.Rows.Count > 0)
{
FileName = Thumuc + "\\" + str.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_SoLieuXaVungBien_" + dtpNgayKu.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
str.ExportToExcel(dt, FileName);
//bll.ExportDTToExcel(dt,FileName);
//bll.WriteDataTableToExcel(dt, "Details", FileName, "tutm : 0985165777");
//bll.ToCSV(dt, sw, true);
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
//bll.OpenCSVWithExcel(FileName);
str.OpenExcel(FileName);
}
else
{
MessageBox.Show("Không có dữ liệu", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
cls.DongKetNoi();
#endregion
}
private void ShowGrid_OnMouseDown(object sender, MouseButtonEventArgs e)
{
try
{
DateTime newdate = new DateTime(dtpNgay.SelectedDate.Value.AddYears(-1).Year + 1, 1, 1);
//Enday off year
//MessageBox.Show(newdate.AddDays(-1).ToString("yyyy-MM-dd"));
cls.ClsConnect();
string sqlload =
"select a.* from LUU_TSCC a " +
"where a.TRANGTHAI='A' and a.POS_CD='" +
CboPos.SelectedValue +
"' and LOAI_TS_CHITIET='" + CboLoaiTSCT.SelectedValue + "' and right(MAPHONG,2)='" + s.Right(CboPhong.SelectedValue.ToString().Trim(), 2) + "' order by a.MA_NHANHIEU_TS,a.MA_TS";
//MessageBox.Show(sqlload);
dt = cls.LoadDataText(sqlload);
if (dt.Rows.Count > 0)
{
dgvData.ItemsSource = dt.DefaultView;
}
else
{
MessageBox.Show("Không có dữ liệu !", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
cauhinh = infor.GetSystemModel() + " , " + infor.GetProcessor() + "," + infor.GetPhysicalMemory() + " , " + infor.GetGraphic() + " , " + infor.GetDisk();
TxtNd.Text = cauhinh;
TxtCB.Text = BienBll.NdTen;
string strsql = "select * from DM_CANBO where ND_MADV='" + CboPos.SelectedValue.ToString().Trim() + "' and ND_PHONGBAN='" + CboPhong.SelectedValue.ToString().Trim() + "' and ND_TTHAI='A' order by ND_CHUCVU,MA_CIF";
var dtcbo = cls.LoadDataText(strsql);
CboCanBo.ItemsSource = dtcbo.DefaultView;
CboCanBo.DisplayMemberPath = "ND_TEN";
CboCanBo.SelectedValuePath = "ND_TEN";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void btnRead_Click(object sender, RoutedEventArgs e)
{
try
{
#region
if (ListBox.Items.Count > 0)
{
string destFolder = txtPath.Text.Trim();
string sourceFolder = txtSourcePath.Text.Trim();
foreach (object t in ListBox.Items)
{
string file = destFolder + t;
string pos = t.ToString().Substring(0, 6);
string ngay = t.ToString().Substring(7, 8);
string fileTxt = sourceFolder + pos + "_" + ngay + "_LEND30.txt";
//MessageBox.Show(file+" "+fileTxt);
bll.Pdf2Text(file, fileTxt);
}
ListText.Items.Clear();
DirectoryInfo dir = new DirectoryInfo(sourceFolder);
FileInfo[] files = dir.GetFiles("*.txt*");
foreach (FileInfo file in files)
{
string ngay = file.Name.Trim().Substring(7, 8);
if (dtpNgay.SelectedDate != null && ngay == dtpNgay.SelectedDate.Value.ToString("ddMMyyyy"))
{
//MessageBox.Show(file.ToString());
ListText.Items.Add(file);
}
}
if (ListText.Items.Count == 0)
{
MessageBox.Show("Not Files found ! ", "Mess", MessageBoxButton.OK, MessageBoxImage.Warning);
}
else
{
cls.ClsConnect();
foreach (object t in ListText.Items)
{
string mato = "";
string soku = "";
string file = sourceFolder + t;
string pos = t.ToString().Substring(0, 6);
string ngay = t.ToString().Substring(7, 8);
string ng = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
string sqlchk = "select top 1 * from PSPDF where NGAY='" + ng + "' and KU_MAPGD='" + pos + "'";
dt = cls.LoadDataText(sqlchk);
if (dt.Rows.Count > 0)
{
MessageBox.Show("Số liệu ngày : " + ng + " Pos : " + pos + " đã tồn tại", "Mess",
MessageBoxButton.OK, MessageBoxImage.Information);
}
else
{
string str = bll.XoaKyTuTrang(File.ReadAllText(file));
string[] arrStr = str.Split(' ');
foreach (var item in arrStr)
{
if ((item.Trim().Length == 8 && bll.Right(item.Trim(), 1) == ":") ||
(item.Trim().Length == 16 && bll.Left(item.Trim(), 2) == "66"))
{
if (item.Trim().Length == 8 && bll.Right(item.Trim(), 1) == ":")
{
mato = bll.Left(item, 7);
}
if (item.Trim().Length == 16 && bll.Left(item.Trim(), 2) == "66")
{
soku = item.Trim();
//MessageBox.Show(ng+" "+pos+" "+mato + " " + soku);
string sql = "insert into PSPDF (NGAY,KU_MAPGD,KU_MATO,KU_SOKU) values ('" +
ng + "','" + pos + "','" + mato + "','" + soku + "')";
cls.UpdateDataText(sql);
}
}
}
}
}
cls.DongKetNoi();
MessageBox.Show("OK", "Mess", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
else
{
MessageBox.Show("Không có file nào!", "Mess", MessageBoxButton.OK, MessageBoxImage.Warning);
}
#region
/*
* string[] files = Directory.GetFiles(destFolder);
* foreach (string file in files)
* {
* string Ngay = file.Substring(14, 8);
* if (dtpNgay.SelectedDate != null && Ngay == dtpNgay.SelectedDate.Value.ToString("ddMMyyyy"))
* {
* MessageBox.Show(file);
* }
* }
*/
//MessageBox.Show(bll.PdfText(txtPath.Text.Trim()));
//string str = bll.XoaKyTuTrang(bll.PdfText(txtPath.Text.Trim()));
//MessageBox.Show(str);
/*
* // MessageBox.Show(bll.XoaKyTuTrang(bll.pdfText(txtPath.Text.Trim())));
* for (int i = 0; i < ListBox.Items.Count; i++)
* {
* string mato = "";
* string soku = "";
* string str = bll.XoaKyTuTrang(bll.pdfText(txtPath.Text.Trim()));
* MessageBox.Show(str);
*
* string[] arrStr = str.Split(' ');
* foreach (var item in arrStr)
* {
* if ((item.Trim().Length == 8 && bll.Right(item.Trim(), 1) == ":") ||
* (item.Trim().Length == 16 && bll.Left(item.Trim(), 2) == "66"))
* {
* if (item.Trim().Length == 8 && bll.Right(item.Trim(), 1) == ":")
* {
* //MessageBox.Show("Ma to "+bll.Left(item,7));
* mato = bll.Left(item, 7);
* //strmoi = strmoi + mato+" ";
* }
* if (item.Trim().Length == 16 && bll.Left(item.Trim(), 2) == "66")
* {
* //MessageBox.Show("soku " + item.Trim());
* soku = item.Trim();
* //strmoi = strmoi + soku+" ";
* MessageBox.Show(mato + " " + soku);
* }
* }
* }
* //MessageBox.Show(strmoi);
* }
*/
#endregion
}
catch (Exception ex)
{
MessageBox.Show("Error + " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
#endregion
bll.DeleteAllFile(txtPath.Text.Trim());
bll.DeleteAllFile(txtSourcePath.Text.Trim());
ListBox.Items.Clear();
ListText.Items.Clear();
}
private void btnOk_Click(object sender, RoutedEventArgs e)
{
str.TaoThuMuc(Thumuc);
try
{
string sqlstr = "";
if (str.Right(str.Left(cboPos.SelectedValue.ToString().Trim(), 6), 2) == "00")
{
if (Ration1.IsChecked == true)
{
sqlstr = "select a.TENHUYEN,a.TENXA,a.KU_MATO,a.TO_TENTT,a.KH_TENKH,CHAR(39)+a.KU_SOKU SOKU" +
",left(a.KU_NGAYGNCC,10) NGAYGN,LEFT(a.KU_NGAYVAY,10) NGAYVAY,LEFT(a.KU_NGAYDHAN_2,10) NGAYDHAN,LEFT(a.KU_NGAYDHAN_3,10) NGAYDHANHDX"
+ ",a.KU_DNOTHAN,a.KU_DNOQHAN,a.KU_DNOKHOANH,a.KU_DNOTHAN+a.KU_DNOQHAN+a.KU_DNOKHOANH DUNO from LDBF a "
+ " where a.NGAY='" + dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd") +
"' and a.KU_CHTRINH='" + str.Left(CboChon.SelectedValue.ToString().Trim(), 2) +
"' and a.KU_TTMONVAY<>'CLOSE'"
+ " order by a.MAXA,a.KU_MATO";
}
else
{
sqlstr = "select a.TENHUYEN,a.TENXA,a.KU_MATO,a.TO_TENTT,a.KH_TENKH,CHAR(39)+a.KU_SOKU SOKU" +
",left(a.KU_NGAYGNCC,10) NGAYGN,LEFT(a.KU_NGAYVAY,10) NGAYVAY,LEFT(a.KU_NGAYDHAN_2,10) NGAYDHAN,LEFT(a.KU_NGAYDHAN_3,10) NGAYDHANHDX"
+ ",a.KU_DNOTHAN,a.KU_DNOQHAN,a.KU_DNOKHOANH,a.KU_DNOTHAN+a.KU_DNOQHAN+a.KU_DNOKHOANH DUNO from LDBF a "
+ " where a.NGAY='" + dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd") +
"' and a.KU_CHTRINH='" + str.Left(CboChon.SelectedValue.ToString().Trim(), 2) +
"' and a.KU_TTMONVAY<>'CLOSE'"
+ " and convert(date,LEFT(a.KU_NGAYGNCC,10),103)>='" +
dtpTuNgay.SelectedDate.Value.ToString("yyyy-MM-dd") +
"' and convert(date,LEFT(a.KU_NGAYGNCC,10),103)<='" +
dtpDenNgay.SelectedDate.Value.ToString("yyyy-MM-dd") + "'"
+ " order by a.MAXA,a.KU_MATO";
}
}
else
{
if (Ration1.IsChecked == true)
{
sqlstr = "select a.TENHUYEN,a.TENXA,a.KU_MATO,a.TO_TENTT,a.KH_TENKH,CHAR(39)+a.KU_SOKU SOKU" +
",left(a.KU_NGAYGNCC,10) NGAYGN,LEFT(a.KU_NGAYVAY,10) NGAYVAY,LEFT(a.KU_NGAYDHAN_2,10) NGAYDHAN,LEFT(a.KU_NGAYDHAN_3,10) NGAYDHANHDX"
+ ",a.KU_DNOTHAN,a.KU_DNOQHAN,a.KU_DNOKHOANH,a.KU_DNOTHAN+a.KU_DNOQHAN+a.KU_DNOKHOANH DUNO from LDBF a "
+ " where a.NGAY='" + dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd") +
"' and a.KH_MAPGD='" + str.Left(cboPos.SelectedValue.ToString().Trim(), 6) +
"' and a.KU_CHTRINH='" + str.Left(CboChon.SelectedValue.ToString().Trim(), 2) +
"' and a.KU_TTMONVAY<>'CLOSE'"
+ " order by a.MAXA,a.KU_MATO";
}
else
{
sqlstr = "select a.TENHUYEN,a.TENXA,a.KU_MATO,a.TO_TENTT,a.KH_TENKH,CHAR(39)+a.KU_SOKU SOKU" +
",left(a.KU_NGAYGNCC,10) NGAYGN,LEFT(a.KU_NGAYVAY,10) NGAYVAY,LEFT(a.KU_NGAYDHAN_2,10) NGAYDHAN,LEFT(a.KU_NGAYDHAN_3,10) NGAYDHANHDX"
+ ",a.KU_DNOTHAN,a.KU_DNOQHAN,a.KU_DNOKHOANH,a.KU_DNOTHAN+a.KU_DNOQHAN+a.KU_DNOKHOANH DUNO from LDBF a "
+ " where a.NGAY='" + dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd") +
"' and a.KH_MAPGD='" + str.Left(cboPos.SelectedValue.ToString().Trim(), 6) +
"' and a.KU_CHTRINH='" + str.Left(CboChon.SelectedValue.ToString().Trim(), 2) +
"' and a.KU_TTMONVAY<>'CLOSE'"
+ " and convert(date,LEFT(a.KU_NGAYGNCC,10),103)>='" +
dtpTuNgay.SelectedDate.Value.ToString("yyyy-MM-dd") +
"' and convert(date,LEFT(a.KU_NGAYGNCC,10),103)<='" +
dtpDenNgay.SelectedDate.Value.ToString("yyyy-MM-dd") + "'"
+ " order by a.MAXA,a.KU_MATO";
}
}
//MessageBox.Show(sqlstr);
cls.ClsConnect();
dt = cls.LoadDataText(sqlstr);
if (dt.Rows.Count > 0)
{
FileName = Thumuc + "\\" + str.Left(cboPos.SelectedValue.ToString().Trim(), 6) + "_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + "_ChTr_" + str.Left(CboChon.SelectedValue.ToString().Trim(), 2) + ".csv";
FileStream fs = new FileStream(FileName, FileMode.Create);
StreamWriter sw = new StreamWriter(fs, Encoding.Unicode);
str.ToCSV(dt, sw, true);
MessageBox.Show("Export to Excel : " + FileName, "Thông báo");
}
else
{
MessageBox.Show("Chưa có số liệu", "Thông báo");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
cls.DongKetNoi();
}
private void btnOk_Click(object sender, RoutedEventArgs e)
{
#region
try
{
if (str.Right(str.Left(CboPos.SelectedValue.ToString().Trim(), 6), 2) == "00")
{
MessageBox.Show("Không chọn POS tổng hợp", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Warning);
}
else
{
cls.ClsConnect();
int thamso = 6;
string[] bien = new string[thamso];
object[] giatri = new object[thamso];
bien[0] = "@NgayKu";
if (dtpNgayKu.SelectedDate != null)
{
giatri[0] = dtpNgayKu.SelectedDate.Value.ToString("yyyy-MM-dd");
}
bien[1] = "@NgayBt";
if (dtpNgayBt.SelectedDate != null)
{
giatri[1] = dtpNgayBt.SelectedDate.Value.ToString("yyyy-MM-dd");
}
bien[2] = "@MaPos";
giatri[2] = str.Left(CboPos.SelectedValue.ToString().Trim(), 6);
bien[3] = "@MaXa";
giatri[3] = str.Left(cboXa.SelectedValue.ToString().Trim(), 6);
//MessageBox.Show(giatri[3].ToString());
bien[4] = "@Nguon";
if (Ration1.IsChecked == true)
{
giatri[4] = "1";
}
else if (Ration2.IsChecked == true)
{
giatri[4] = "2";
}
else
{
giatri[4] = "3";
}
bien[5] = "@Mau";
if (Ration4.IsChecked == true)
{
giatri[5] = "1";
}
else if (Ration5.IsChecked == true)
{
giatri[5] = "2";
}
else
{
giatri[5] = "3";
}
if (Ration4.IsChecked == true)
{
dt = cls.LoadLdbf("usp_SlDaily", bien, giatri, thamso);
if (Ration6.IsChecked == true)
{
rpt_SlDaily rpt = new rpt_SlDaily();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
}
else
{
FileName = Thumuc + "\\" + giatri[2] + "_" + giatri[3] + "_SLTD_XA_" +
dtpNgayBt.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
str.ExportToExcel(dt, FileName);
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK,
MessageBoxImage.Information);
//str.OpenExcel(FileName);
}
}
else
{
if (Ration5.IsChecked == true)
{
dt = cls.LoadLdbf("usp_SlDailyCT", bien, giatri, thamso);
if (Ration6.IsChecked == true)
{
rpt_SlDailyCt rpt = new rpt_SlDailyCt();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
}
else
{
FileName = Thumuc + "\\" + giatri[2] + "_" + giatri[3] + "_SLTD_CHTR_" +
dtpNgayBt.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
str.ExportToExcel(dt, FileName);
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK,
MessageBoxImage.Information);
//str.OpenExcel(FileName);
}
}
else
{
dt = cls.LoadLdbf("usp_SlDailyDvut", bien, giatri, thamso);
if (Ration6.IsChecked == true)
{
rpt_SlDaily rpt = new rpt_SlDaily();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
}
else
{
FileName = Thumuc + "\\" + giatri[2] + "_" + giatri[3] + "_SLTD_DVUT_" +
dtpNgayBt.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
str.ExportToExcel(dt, FileName);
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK,
MessageBoxImage.Information);
//str.OpenExcel(FileName);
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
cls.DongKetNoi();
#endregion
}
private void btnSua_Click(object sender, RoutedEventArgs e)
{
string strup = "";
try
{
cls.ClsConnect();
string phong = CboPhong.SelectedValue.ToString().Trim();
var dtin = cls.LoadDataText("select * from luu_tscc where TRANGTHAI='A' and POS_CD='" + CboPos.SelectedValue + "' and right(MAPHONG,2)='" + s.Right(CboPhong.SelectedValue.ToString().Trim(), 2) + "' order by LOAI_TS_CHITIET,MA_NHANHIEU_TS,MA_TS");
if (dtin.Rows.Count > 0)
{
rpt_TSCC_KiemKe rpt = new rpt_TSCC_KiemKe();
RPUtility.ShowRp(rpt, dtin, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(),
srv.DbPassSerVer());
}
else
{
MessageBox.Show("Không có dữ liệu !", "Thông báo", MessageBoxButton.OK,
MessageBoxImage.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Lỗi", MessageBoxButton.OK, MessageBoxImage.Error);
}
cls.DongKetNoi();
}
private void btnOk_Click(object sender, RoutedEventArgs e)
{
#region
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] = str.Left(CboPos.SelectedValue.ToString().Trim(), 6);
bien[2] = "@MaXa";
if (str.Right(CboPos.SelectedValue.ToString(), 2) != "00")
{
giatri[2] = str.Left(CboXa.SelectedValue.ToString().Trim(), 6);
}
else
{
giatri[2] = "";
}
bien[3] = "@Mau";
if (str.Right(CboPos.SelectedValue.ToString(), 2) != "00" && str.Right(CboXa.SelectedValue.ToString(), 2) != "00")
{
giatri[3] = "1";
}
else if (str.Right(CboPos.SelectedValue.ToString(), 2) != "00" && str.Right(CboXa.SelectedValue.ToString(), 2) == "00")
{
giatri[3] = "2";
}
if (str.Right(CboPos.SelectedValue.ToString(), 2) == "00")
{
giatri[3] = "3";
}
dt = cls.LoadLdbf("usp_NOXH", bien, giatri, thamso);
if (dt.Rows.Count > 0)
{
if (Ration1.IsChecked == true)
{
rpt_NOXH rpt = new rpt_NOXH();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
}
else
{
FileName = Thumuc + "\\" + giatri[2] + "_" + giatri[3] + "_NOXH_" +
dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
str.ExportToExcel(dt, FileName);
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK,
MessageBoxImage.Information);
str.OpenExcel(FileName);
}
}
else
{
MessageBox.Show("Không có số liệu", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
cls.DongKetNoi();
#endregion
}
