static void CopyDirectory(DirectoryInfo source, DirectoryInfo destination)
{
try
{
ToolBll bll = new ToolBll();
if (!destination.Exists)
{
destination.Create();
}
// Copy all files.
FileInfo[] files = source.GetFiles();
MessageBox.Show("Tong so file : ", files.Length.ToString());
if (files.Length == 0)
{
MessageBox.Show("Không có file nào!", "Mess", MessageBoxButton.OK, MessageBoxImage.Warning);
}
else
{
foreach (FileInfo file in files)
{
string pos = destination + file.Name.Substring(11, 4);
string tmcha = pos + @"\" + file.Name.Substring(17, 4).Trim();
string tmcon = tmcha + @"\" + file.Name.Substring(17, 6).Trim();
bll.TaoThuMuc(pos);
bll.TaoThuMuc(tmcha);
bll.TaoThuMuc(tmcon);
if (!Directory.Exists(tmcon))
{
MessageBox.Show("Không có thư mục : " + tmcon, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Warning);
}
else
if (!File.Exists(tmcon + @"\" + file.Name))
{
file.MoveTo(tmcon + @"\" + file.Name);
}
//MessageBox.Show("File tồn tại : " + tmcon + @"\" + file.Name, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
MessageBox.Show("Move OK!", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Warning);
//// Process subdirectories.
//DirectoryInfo[] dirs = source.GetDirectories();
//foreach (DirectoryInfo dir in dirs)
//{
// // Get destination directory.
// string destinationDir = Path.Combine(destination.FullName, dir.Name);
// // Call CopyDirectory() recursively.
// CopyDirectory(dir, new DirectoryInfo(destinationDir));
//}
}
catch (Exception ex)
{
MessageBox.Show("Error : " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void LblSaoKe_OnMouseDown(object sender, MouseButtonEventArgs e)
{
cls.ClsConnect();
try
{
str.TaoThuMuc(Thumuc);
string ng = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
string pos = str.Left(CboPos.SelectedValue.ToString().Trim(), 6);
string strsql = "with lst1 as ("
+ " select left(a.KU_MADP, 6) MAXA, a.KU_MATO, a.KU_MAKH, sum(a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH) DUNO "
+ " from HSCV_DAILY a where a.ku_ngaybc = '" + ng + "' and a.KU_MAPGD = '" + pos + "' group by a.KU_MAKH, left(a.KU_MADP, 6), a.KU_MATO "
+ " having sum(a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH) = 0), lst2 as ("
+ " select a.CS_MAKH,a.CS_SO_TK2,a.CS_MATO from CASA_DAILY a where a.CS_MATO is not null and a.CS_NGAYBC = '" + ng + "' and a.CS_SP_TK = '105' and a.CS_TTSO_TK <> 'C' and a.CS_MAPGD = '" + pos + "' "
+ " ) select a.MAXA,c.TEN,b.CS_MATO,d.TO_TENTT,a.KU_MAKH,e.KH_TENKH,a.DUNO,char(39) + b.CS_SO_TK2 SOTK from lst1 a "
+ " left join DMXA c on a.MAXA = c.MA "
+ " , hskh e, lst2 b,HSTO d where b.CS_MATO = d.TO_MATO and a.KU_MAKH = e.KH_MAKH and a.KU_MAKH = b.CS_MAKH order by a.MAXA, a.KU_MATO, a.KU_MAKH ";
var dtchk = cls.LoadDataText(strsql);
if (dtchk.Rows.Count > 0)
{
FileName = Thumuc + "\\" + str.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_" + str.Left(CboTo.SelectedValue.ToString().Trim(), 7) + "_DONG105_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
str.ExportToExcel(dtchk, FileName);
str.OpenExcel(FileName);
}
else
{
MessageBox.Show("Không có bản ghi nào ", "Mess");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnOk_Click(object sender, RoutedEventArgs e)
{
str.TaoThuMuc(Thumuc);
try
{
cls.ClsConnect();
string strsql = "select a.PLN_MAPGD,(select PO_TEN from dmpos where po_ma=a.pln_mapgd) TENPOS"
+ " , left(a.PLN_MADP, 6) MAXA,(select ten from dmxa where ma = left(a.PLN_MADP, 6)) TENXA "
+ " ,a.PLN_MATO,a.PLN_TENTT,a.PLN_MAKH,a.PLN_TENKH,CHAR(39) + a.PLN_SOKU SOKU "
+ " ,a.PLN_DNOTHAN,a.PLN_DNOQHAN,a.PLN_DNOKHOANH,a.PLN_K_KNTN_SODU from PLN_KNTN_CL a "
+ " where a.PLN_NGAYBC = '2018-06-30' and a.PLN_TT_MONVAY <> 'CLOSE' and a.PLN_TRANGTHAI = 'S' and a.PLN_K_KNTN_SODU > 0 "
+ " and a.PLN_K_KNTN_SD" + str.Left(CboNgNhan.SelectedValue.ToString(), 2) + ">0 order by a.PLN_MADP,a.PLN_MATO,a.PLN_MAKH";
dt = cls.LoadDataText(strsql);
FileName = Thumuc + "\\" + str.Left(cboPos.SelectedValue.ToString(), 6) + "_" + CboNgNhan.SelectedValue.ToString().Substring(4, CboNgNhan.SelectedValue.ToString().Trim().Length - 4) + "_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
MessageBox.Show(FileName);
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();
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
try
{
str.TaoThuMuc(Thumuc);
}
catch (Exception ex)
{
MessageBox.Show("Lổi tạo thư mục " + ex.Message, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Error);
}
cls.ClsConnect();
DataTable dtng = new DataTable();
DataTable dtpos = new DataTable();
dtng = cls.LoadDataText("select MAX(convert(date,NGAYKU,105)) as NGMAX from U_HSTD");
dtpNgay.SelectedDate = DateTime.Parse(dtng.Rows[0]["NGMAX"].ToString());
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 = 0;
cls.DongKetNoi();
}
private void btnOk_Click(object sender, RoutedEventArgs e)
{
cls.ClsConnect();
bll.TaoThuMuc(Thumuc);
try
{
if (txtPath.Text == "")
{
MessageBox.Show("Chưa chọn đường dẫn", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
else
{
string BackUpLocation = txtPath.Text.Trim();
string DatabaseName = CboDb.SelectedValue.ToString().Trim();
string BackUpFileName = CboDb.SelectedValue.ToString().Trim() + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".bak";
BackupDatabase(BackUpLocation, BackUpFileName, DatabaseName);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
cls.DongKetNoi();
}
/*
* 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 lblCauHoi_MouseDown(object sender, MouseButtonEventArgs e)
{
try
{
if (dtpNgay.SelectedDate != null)
{
ng = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
}
reset_clock();
string sql = "";
str.TaoThuMuc(Thumuc);
cls.ClsConnect();
var dtchk = cls.LoadDataText("select * from KQTHINV where USERNAME='******' and NGAY='" + ng + "'");
if (dtchk.Rows.Count == 0)
{
sql = "insert into KQTHINV select LOAI, CAUHOI, A, B, C, D, DAPAN, TT, POS,'" +
BienBll.NdTen.Trim() + "' NG_NHAP,0 VONG,'" + BienBll.Ndma.Trim() +
"' USERNAME, TRALOI,CANCU,NGAY from CAUHOI where NGAY='" + ng + "'";
cls.LoadDataText(sql);
}
dt = cls.LoadDataText("select * from KQTHINV where USERNAME='******' and NGAY='" + ng + "' and TRALOI is null");
if (dt.Rows.Count > 0)
{
str.WriteText(dt, filename);
StreamReader sr = new StreamReader(filename);
string readLine = sr.ReadLine();
if (readLine != null)
{
string[] arrStr = readLine.Split('#');
txtCauHoi.Text = arrStr[1];
txtA.Text = arrStr[2];
txtB.Text = arrStr[3];
txtC.Text = arrStr[4];
txtD.Text = arrStr[5];
dapan = arrStr[6];
TT = arrStr[7];
cancu = arrStr[13];
}
StreamReader srl = new StreamReader(filename);
while (srl.Peek() >= 0)
{
lst.Add(srl.ReadLine());
Sodong = Sodong + 1;
}
cls.DongKetNoi();
lblCauHoi.IsEnabled = false;
star_clock();
}
else
{
MessageBox.Show("Bạn đã làm xong bài thi của mình rồi !", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex.Message, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void bntOk_Click(object sender, RoutedEventArgs e)
{
try
{
bll.TaoThuMuc(Thumuc);
cls.ClsConnect();
int thamso = 2;
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);
//MessageBox.Show(giatri[0] + " " + giatri[1]);
//dt = cls.LoadDataProcPara("usp_KHB", bien, giatri, thamso);
dt1 = cls.LoadDataProcPara("usp_khb1", bien, giatri, thamso);
//dt2 = cls.LoadDataProcPara("usp_khb2", bien, giatri, thamso);
rpt_khb1 rpt1 = new rpt_khb1();
RPUtility.ShowRp(rpt1, dt1, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
//rpt_khb2 rpt2 = new rpt_khb2();
//RPUtility.ShowRp(rpt2, dt2, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
dt3 = cls.LoadDataProcPara("usp_khb3", bien, giatri, thamso);
rpt_khb3 rpt3 = new rpt_khb3();
RPUtility.ShowRp(rpt3, dt3, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
dt4 = cls.LoadDataProcPara("usp_KHB4", bien, giatri, thamso);
rpt_khb4 rpt4 = new rpt_khb4();
RPUtility.ShowRp(rpt4, dt4, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
// FileName = Thumuc + "\\KHB03_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".xlsx";
}
// bll.WriteDataTableToExcel(dt2, "Person Details", FileName, "Details");
// MessageBox.Show("Copy Excel to : " + FileName);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
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 btnOk_Click(object sender, RoutedEventArgs e)
{
try
{
str.TaoThuMuc(Thumuc);
dtNew = dt.Clone();
foreach (DataRow dr in dt.Rows)
{
if ((bool)dr[0] == true)
{
dtNew.ImportRow(dr);
}
}
//dtNew = dt.GetChanges();
if (dtNew == null || dtNew.Rows.Count == 0)
{
MessageBox.Show("Chưa có thay đổi ngày nào !", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Warning);
}
else
{
rpt_GiaoNhanTVM01 rpt = new rpt_GiaoNhanTVM01();
RPUtility.ShowRp(rpt, dtNew, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
rpt_GiaoNhanTVM02 rpt1 = new rpt_GiaoNhanTVM02();
RPUtility.ShowRp(rpt1, dtNew, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
dgvSource.ItemsSource = null;
FileName = Thumuc + "\\" + str.Left(CboXa.SelectedValue.ToString().Trim(), 6) + "_" + str.Left(CboToGiao.SelectedValue.ToString().Trim(), 7) + "_" + str.Left(CboToNhan.SelectedValue.ToString().Trim(), 7) + "_" + "_GIAONHAN_TV_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
str.ExportToExcel(dtNew, 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, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
cls.DongKetNoi();
}
private void btnLuu_Click(object sender, RoutedEventArgs e)
{
try
{
if (sua == true)
{
cls.ClsConnect();
foreach (DataRow dr in dtSua.Rows)
{
string upd = "update LUUVPP set SOLUONG=" + dr["SOLUONG"] + " where MAPOS='" +
bll.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "' and PHONGTO='" +
bll.Left(CboPB.SelectedValue.ToString().Trim(), 2) + "' and THANG = '" +
bll.Left(comboBoxMonth.SelectedValue.ToString().Trim(), 2) + "' and NAM='" +
comboBoxYear.SelectedValue.ToString().Trim() + "' and MA='" + dr["MA"] + "'";
cls.UpdateDataText(upd);
}
MessageBox.Show("Cập nhật thành công !", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Warning);
sua = false;
}
else
{
bll.TaoThuMuc(Thumuc);
dtNew = dt.Clone();
foreach (DataRow dr in dt.Rows)
{
if ((bool)dr[0] == true)
{
dtNew.ImportRow(dr);
}
}
//dtNew = dt.GetChanges();
if (dtNew == null || dtNew.Rows.Count == 0)
{
MessageBox.Show("Chưa có thay đổi ngày nào !", "Thông báo", MessageBoxButton.OK,
MessageBoxImage.Warning);
}
else
{
cls.ClsConnect();
foreach (DataRow dr in dtNew.Rows)
{
string chk = "select * from LUUVPP where MAPOS='" +
bll.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "' and PHONGTO='" +
bll.Left(CboPB.SelectedValue.ToString().Trim(), 2) + "' and THANG = '" +
bll.Left(comboBoxMonth.SelectedValue.ToString().Trim(), 2) + "' and NAM='" +
comboBoxYear.SelectedValue.ToString().Trim() + "' and MA='" + dr["MA"] + "'";
var dtchk = cls.LoadDataText(chk);
if (dtchk.Rows.Count > 0)
{
MessageBox.Show(dr["TEN"] + " đã tồn tại !", "Thông báo", MessageBoxButton.OK,
MessageBoxImage.Information);
}
else
{
string strin =
"insert into LUUVPP (CHON,MAPOS,TENPOS,PHONGTO,TENPB,THANG,NAM,NGAY,MA,TEN,DONVI,SOLUONG,QUYCACH,ND_MA,ND_TEN) " +
"VALUES (" + 0 + ",'" + dr["MAPOS"] + "',N'" + dr["TENPOS"] + "','" + dr["PHONGTO"] +
"',N'" + dr["TENPB"] + "','" + dr["THANG"] + "','" + dr["NAM"] + "','" +
DateTime.Now.ToString("yyyy-MM-dd") + "','" + dr["MA"] + "',N'" +
dr["TEN"] + "',N'" + dr["DONVI"] + "','" + dr["SOLUONG"] + "',N'" + dr["QUYCACH"] + "',N'" + BienBll.Ndma + "',N'" + BienBll.NdTen + "')";
cls.UpdateDataText(strin);
}
}
MessageBox.Show("OK chọn nút in !", "Thông báo", MessageBoxButton.OK,
MessageBoxImage.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
cls.DongKetNoi();
}
private void btnOk_Click(object sender, RoutedEventArgs e)
{
str.TaoThuMuc(Thumuc);
try
{
if (OptAll.IsChecked == true)
{
Mau = "1";
FileName = Thumuc + "\\" + str.Left(cboPos.SelectedValue.ToString().Trim(), 6) + "_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + "_SaokeKuAll.csv";
}
else if (OptChtr.IsChecked == true)
{
Mau = "2";
FileName = Thumuc + "\\" + str.Left(cboPos.SelectedValue.ToString().Trim(), 6) + "_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + "_Chtr" + str.Left(CboChon.SelectedValue.ToString().Trim(), 2) + ".csv";
}
else if (OptDvut.IsChecked == true)
{
Mau = "3";
FileName = Thumuc + "\\" + str.Left(cboPos.SelectedValue.ToString().Trim(), 6) + "_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + "_DVUT_" + str.Left(CboChon.SelectedValue.ToString().Trim(), 2) + ".csv";
}
else if (OptTT.IsChecked == true)
{
Mau = "4";
FileName = Thumuc + "\\" + str.Left(cboPos.SelectedValue.ToString().Trim(), 6) + "_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + "_VayTrucTiep.csv";
}
else if (OptXa.IsChecked == true)
{
Mau = "5";
FileName = Thumuc + "\\" + str.Left(cboPos.SelectedValue.ToString().Trim(), 6) + "_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + "_XA_" + str.Left(CboChon.SelectedValue.ToString().Trim(), 6) + ".csv";
}
cls.ClsConnect();
int thamso = 5;
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";
giatri[1] = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
bien[2] = "@MaXa";
giatri[2] = str.Left(CboChon.SelectedValue.ToString().Trim(), 6);
bien[3] = "@Bien";
giatri[3] = str.Left(CboChon.SelectedValue.ToString().Trim(), 2);
bien[4] = "@Mau";
giatri[4] = Mau;
//if (OptXa.IsChecked == true)
//{
// string strsql = "select substr(ku_madp,1,6) maxa, (select ten from dmxa where ma=substr(ku_madp,1,6)) tenxa,ku_mato,to_tentt"
// +",kh_makh,kh_tenkh,kh_diachi,ku_nguonvon,concat(chr(39),ku_soku) soku,to_char(ku_ngayvay, 'dd/MM/yyyy') ng_vay,to_char(ku_ngaydhan_1, 'dd/MM/yyyy') ng_dhan "
// +" ,to_char(ku_ngaydhan_2, 'dd/MM/yyyy') ng_dhan_ghan,to_char(ku_ngaydhan_3, 'dd/MM/yyyy') ng_dhan_gdx"
// + ",to_char(ku_ngayhhkh, 'dd/MM/yyyy') ng_hethankhoanh"
// + ",ku_maqd,(select giatri from dmkhac where khoa_1 = '07' and ku_maqd = khoa_2) ten_chtr"
// + ",ku_dnothan,ku_dnoqhan,ku_dnokhoanh,ku_laitonthan + ku_laitonqhan laiton,to_dvut"
// + ",(select giatri from dmkhac where khoa_1 = '17' and to_dvut = khoa_2) ten_dvut"
// +",ku_capqlv,(select giatri from dmkhac where khoa_1 = '19' and ku_capqlv = khoa_2) ten_capqlv"
// + ",ku_mandt,dt_tendt,sv_tensv,sv_ngnhaphoc,sv_ngrtruong from ("
// + " select * from hscv_daily left join hsto on ku_mato = to_mato"
// +" where ku_ngaybc ='"+dtpNgay.SelectedDate.Value.ToString("dd/MMM/yyyy")+"' and ku_ttmonvay <> 'CLOSE' and ku_dnothan+ku_dnoqhan + ku_dnokhoanh > 0 and substr(ku_madp,1, 6)='"+ str.Left(CboChon.SelectedValue.ToString().Trim(), 6) + "'"
// +") left join hsdt on ku_mandt = dt_madt left join hskh on ku_makh = kh_makh left join hssv on ku_soku = sv_soku";
// ora.ClsConnect();
// dt = ora.LoadDataText(strsql);
// ora.DongKetNoi();
//} else
dt = cls.LoadDataProcPara("usp_SkeKuAll", bien, giatri, thamso);
if (dt.Rows.Count > 0)
{
//if (OptAll.IsChecked == true)
//{
FileStream fs = new FileStream(FileName, FileMode.Create);
StreamWriter sw = new StreamWriter(fs, Encoding.Unicode);
str.ToCSV(dt, sw, true);
//}
//else
//{
// str.ExportToExcel(dt, FileName);
//}
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
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_Click(object sender, RoutedEventArgs e)
{
bll.TaoThuMuc(Thumuc);
try
{
ServerInfor srv = new ServerInfor();
cls.ClsConnect();
DataTable dt = new DataTable();
int thamso = 2;
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);
if (radioButton1.IsChecked == true)
{
FileName = Thumuc + "\\D15NHNN_" + bll.Left(cboPos.SelectedValue.ToString().Trim(), 6) + "_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".xlsx";
dt = cls.LoadDataProcPara("usp_D15NHNN", bien, giatri, thamso);
}
else if (radioButton2.IsChecked == true)
{
FileName = Thumuc + "\\D20NHNN_" + bll.Left(cboPos.SelectedValue.ToString().Trim(), 6) + "_" +
dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".xlsx";
dt = cls.LoadDataProcPara("usp_D20NHNN", bien, giatri, thamso);
}
else
{
dt =
cls.LoadDataText(
"select CS_MAPGD,COUNT(distinct CS_MAKH) DEM from casa where CS_NGAYBC='" + dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd") + "' and CS_SODU_TK>0 group by CS_MAPGD");
}
if (dt.Rows.Count > 0)
{
if (radioButton1.IsChecked == true || radioButton2.IsChecked == true)
{
bll.WriteDataTableToExcel(dt, "Details", FileName, "TUTM");
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK,
MessageBoxImage.Information);
}
if (radioButton1.IsChecked == true)
{
rpt_D15NHNN rpt = new rpt_D15NHNN();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(),
srv.DbPassSerVer());
}
else if (radioButton2.IsChecked == true)
{
rpt_D20NHNN rpt = new rpt_D20NHNN();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(),
srv.DbPassSerVer());
}
else
{
MessageBox.Show("CN01 : " + dt.Rows[0]["DEM"].ToString() + " | " + "CN02 : " + dt.Rows[1]["DEM"].ToString() + " | " + "CN03 : " + dt.Rows[2]["DEM"].ToString() + " | " + "CN04 : " + dt.Rows[3]["DEM"].ToString() + " | " + "CN05 : " + dt.Rows[4]["DEM"].ToString() + " | " + "CN06 : " + dt.Rows[5]["DEM"].ToString() + " | " + "CN07 : " + dt.Rows[6]["DEM"].ToString() + " | " + "CN08 : " + dt.Rows[7]["DEM"].ToString(), "Số hộ huy động vốn", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
else
{
MessageBox.Show("Chưa có số liệu", "Thông báo");
}
cls.DongKetNoi();
}
catch (Exception ex)
{
MessageBox.Show("Lổi " + ex.Message, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void lblCauHoi_MouseDown(object sender, MouseButtonEventArgs e)
{
try
{
reset_clock();
string sql = "";
str.TaoThuMuc(Thumuc);
cls.ClsConnect();
dtvong = cls.LoadDataText("select max(VONG) VONG,count(*) DEM from KQTHINV where USERNAME='******' and LOAI='" + CboChuDe.SelectionBoxItem.ToString().Trim() + "'");
if (dtvong.Rows[0]["DEM"].ToString() == "0")
{
vong = "0";
socau = "0";
}
else
{
vong = dtvong.Rows[0]["VONG"].ToString();
if (CboChuDe.SelectionBoxItem.ToString().Trim() == "AL")
{
dtsocau = cls.LoadDataText(
"select cast(max(VONG) as int) VONG,count(*) DEM from KQTHINV where USERNAME='******' and VONG='" + vong + "' and TRALOI is null ");
}
else
{
dtsocau = cls.LoadDataText(
"select cast(max(VONG) as int) VONG,count(*) DEM from KQTHINV where USERNAME='******' and VONG='" + vong + "' and TRALOI is null and LOAI='" + CboChuDe.SelectionBoxItem.ToString().Trim() + "'");
}
if (dtsocau.Rows.Count > 0)
{
socau = dtsocau.Rows[0]["DEM"].ToString();
}
txtVong.Text = BienBll.NdTen + " Đang Thi Vòng Số : " + vong + " Số Câu Chưa Trả Lời : " + socau;
}
if (vong == "0" & socau == "0")
{
if (CboChuDe.SelectionBoxItem.ToString().Trim() == "AL")
{
sql = "insert into KQTHINV select 'AL' LOAI, CAUHOI, A, B, C, D, DAPAN, TT, POS,'" +
BienBll.NdTen.Trim() +
"' NG_NHAP,1 VONG,'" + BienBll.Ndma.Trim() + "' USERNAME, TRALOI,CANCU from CAUHOI order by TT";
}
else
{
sql = "insert into KQTHINV select LOAI, CAUHOI, A, B, C, D, DAPAN, TT, POS,'" +
BienBll.NdTen.Trim() +
"' NG_NHAP,1 VONG,'" + BienBll.Ndma.Trim() + "' USERNAME, TRALOI,CANCU from CAUHOI where LOAI='" + CboChuDe.SelectionBoxItem.ToString().Trim() + "' order by TT";
}
cls.LoadDataText(sql);
}
else
{
if (socau == "0") //lam het cau tao vong moi
{
int i = 0;
string s = vong;
i = int.Parse(s);
i = Convert.ToInt32(s) + 1;
if (CboChuDe.SelectionBoxItem.ToString().Trim() == "AL")
{
sql = "insert into KQTHINV select 'AL' LOAI, CAUHOI, A, B, C, D, DAPAN, TT, POS,'" +
BienBll.NdTen.Trim() +
"' NG_NHAP," + i + " VONG,'" + BienBll.Ndma.Trim() +
"' USERNAME, TRALOI,CANCU from CAUHOI order by TT";
}
else
{
sql = "insert into KQTHINV select LOAI, CAUHOI, A, B, C, D, DAPAN, TT, POS,'" +
BienBll.NdTen.Trim() +
"' NG_NHAP," + i + " VONG,'" + BienBll.Ndma.Trim() + "' USERNAME, TRALOI,CANCU from CAUHOI where LOAI='" + CboChuDe.SelectionBoxItem.ToString().Trim() + "' order by TT";
}
cls.LoadDataText(sql);
}
}
//var dtsocau = cls.LoadDataText("select count(*) DEM from KQTHINV where USERNAME='******' and TRALOI is null");
var dtsl = cls.LoadDataText("select max(VONG) VONG,count(*) DEM from KQTHINV where USERNAME='******' and TRALOI is null and LOAI='" + CboChuDe.SelectionBoxItem.ToString().Trim() + "'");
if (dtsl.Rows.Count > 0)
{
vong = dtsl.Rows[0]["VONG" +
""].ToString();
socau = dtsl.Rows[0]["DEM"].ToString();
txtVong.Text = BienBll.NdTen + " Đang Thi Vòng Số : " + vong + " Số Câu Chưa Trả Lời : " + socau;
var dtsai =
cls.LoadDataText("select COUNT(*) dem from KQTHINV where VONG=" + vong + " and USERNAME='******' and DAPAN<>TRALOI and LOAI='" + CboChuDe.SelectionBoxItem.ToString().Trim() + "'");
socausai = (int)dtsai.Rows[0]["DEM"];
lblSai.Content = socausai.ToString();
lblCon.Content = datraloi.ToString();
}
if (CboChuDe.SelectionBoxItem.ToString().Trim() == "AL")
{
dt = cls.LoadDataText("select * from KQTHINV where USERNAME='******' and TRALOI is null");
}
else
{
dt = cls.LoadDataText("select * from KQTHINV where USERNAME='******' and LOAI='" + CboChuDe.SelectionBoxItem + "' and TRALOI is null");
}
str.WriteText(dt, filename);
StreamReader sr = new StreamReader(filename);
string readLine = sr.ReadLine();
if (readLine != null)
{
string[] arrStr = readLine.Split('#');
txtCauHoi.Text = arrStr[1];
txtA.Text = arrStr[2];
txtB.Text = arrStr[3];
txtC.Text = arrStr[4];
txtD.Text = arrStr[5];
dapan = arrStr[6];
TT = arrStr[7];
cancu = arrStr[13];
}
//MessageBox.Show(sql, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
StreamReader srl = new StreamReader(filename);
while (srl.Peek() >= 0)
{
lst.Add(srl.ReadLine());
Sodong = Sodong + 1;
}
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex.Message, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Error);
}
cls.DongKetNoi();
lblCauHoi.IsEnabled = false;
lblOk.IsEnabled = false;
Countdown(thoigian, TimeSpan.FromSeconds(1), cur => tb.Text = cur.ToString()); //tutm
star_clock();
}
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)
{
str.TaoThuMuc(Thumuc);
try
{
int thamso = 2;
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";
giatri[1] = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
if (dtpNgay.SelectedDate != null)
{
string nam = dtpNgay.SelectedDate.Value.ToString("yyyy");
string thang = dtpNgay.SelectedDate.Value.ToString("MM");
string pos = str.Left(CboPos.SelectedValue.ToString().Trim(), 6);
DateTime lastMonth = new DateTime(dtpNgay.SelectedDate.Value.Year, dtpNgay.SelectedDate.Value.Month, DateTime.DaysInMonth(dtpNgay.SelectedDate.Value.Year, dtpNgay.SelectedDate.Value.Month));
var lastDayOfTheYear = new DateTime(dtpNgay.SelectedDate.Value.Year, 12, 31);
string Enddayofyear = lastDayOfTheYear.ToString("yyyy-MM-dd");
string EnddayofyearPre = lastDayOfTheYear.AddYears(-1).ToString("yyyy-MM-dd");
string LastMonthPre = dtpNgay.SelectedDate.Value.AddMonths(-1).ToString("yyyy-MM-dd");
// DateTime LastWeek = dtpNgay.SelectedDate.Value.AddDays(-(int)dtpNgay.SelectedDate.Value.DayOfWeek-2);
if (dtpNgay.SelectedDate != null)
{
cls.ClsConnect();
ora.ClsConnect();
string mau = str.Left(CboMau.SelectedValue.ToString(), 3);
string ng = dtpNgay.SelectedDate.Value.ToString("dd/MMM/yyyy");
string ngsql = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
string strsql = "";
switch (mau)
{
case "M01":
if (dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd") == lastMonth.ToString("yyyy-MM-dd"))
{
strsql = "select b.cs_mapgd,(select po_ten from dmpos where po_ma=b.cs_mapgd) TENPOS "
+ " ,substr(b.cs_madp, 1, 6) maxa,(select ten from dmxa where ma = substr(b.cs_madp, 1, 6)) tenxa "
+ " ,b.cs_mato,(select to_tentt from hsto where to_mato = b.cs_mato) tentt,b.cs_makh,kh_tenkh,b.cs_tentk "
+ " ,concat(chr(39),b.cs_so_tk2) TK, b.cs_sodu_tk,b.cs_ttso_tk,b.cs_sp_tk from "
+ " ( select cs_makh, count(cs_makh)dem from casa where cs_ngaybc ='" + ng + "' and cs_sp_tk = '105' and cs_ttso_tk = 'A' group by cs_makh having count(cs_makh) > 1) a, "
+ " (select * from casa where cs_ngaybc ='" + ng + "' and cs_sp_tk = '105' and cs_ttso_tk = 'A') b left join hskh on kh_makh = b.cs_makh "
+ " where a.cs_makh = b.cs_makh order by substr(b.cs_madp, 1, 6),b.cs_mato, b.cs_makh";
}
else
{
strsql = "select b.cs_mapgd,(select po_ten from dmpos where po_ma=b.cs_mapgd) TENPOS "
+ " ,substr(b.cs_madp, 1, 6) maxa,(select ten from dmxa where ma = substr(b.cs_madp, 1, 6)) tenxa "
+ " ,b.cs_mato,(select to_tentt from hsto where to_mato = b.cs_mato) tentt,b.cs_makh,kh_tenkh,b.cs_tentk "
+ " ,concat(chr(39),b.cs_so_tk2) TK, b.cs_sodu_tk,b.cs_ttso_tk,b.cs_sp_tk from "
+ " ( select cs_makh, count(cs_makh)dem from casa_daily where cs_ngaybc ='" + dtpNgay.SelectedDate.Value.ToString("dd/MMM/yyyy") + "' and cs_sp_tk = '105' and cs_ttso_tk = 'A' group by cs_makh having count(cs_makh) > 1) a, "
+ " (select * from casa_daily where cs_ngaybc ='" + dtpNgay.SelectedDate.Value.ToString("dd/MMM/yyyy") + "' and cs_sp_tk = '105' and cs_ttso_tk = 'A') b left join hskh on kh_makh = b.cs_makh "
+ " where a.cs_makh = b.cs_makh order by substr(b.cs_madp, 1, 6),b.cs_mato, b.cs_makh";
}
break;
case "M02":
strsql = "with lst1 as ("
+ " select kh_cmt, count(kh_cmt) dem from hskh where kh_ttrang <> 'C' and substr(kh_madp, 1, 4) = substr(kh_mapgd, 3, 4) group by kh_cmt having count(kh_cmt) > 1"
+ " ) select a.kh_mapgd,substr(a.kh_madp, 1, 6) MAXA,C.TEN TENXA , a.kh_makh,a.kh_tenkh,a.kh_cmt,a.kh_ttrang from hskh a, lst1 b,dmxa c where a.kh_cmt = b.kh_cmt and substr(a.kh_madp, 1, 6)= c.ma "
+ " order by a.kh_cmt";
break;
case "M03":
strsql = "select a.ku_mapgd,substr(a.ku_madp,1,6) maxa,a.ku_makh,a.ku_mato,b.cs_mato from "
+ " (select * from hscv_daily where ku_ngaybc ='" + ng + "' and ku_ttmonvay <> 'CLOSE' and ku_dnothan + ku_dnoqhan + ku_dnokhoanh > 0 and ku_mato is not null) a, "
+ " (select * from casa_daily where cs_ngaybc ='" + ng + "' and cs_ttso_tk <> 'C' and cs_sp_tk = '105' and cs_mato is not null) b "
+ " where a.ku_makh = b.cs_makh and a.ku_mato<> b.cs_mato";
break;
case "M04":
strsql = "select a.ku_mapgd,substr(a.ku_madp,1,6) maxa,a.ku_makh,a.ku_mato mato1,b.ku_mato mato2 from"
+ "(select * from hscv_daily where ku_ngaybc ='" + ng + "' and ku_ttmonvay <> 'CLOSE' and ku_dnothan + ku_dnoqhan + ku_dnokhoanh > 0) a,"
+ " (select ku_makh, ku_mato from hscv_daily where ku_ngaybc='" + ng + "' and ku_ttmonvay <> 'CLOSE' and ku_dnothan+ku_dnoqhan + ku_dnokhoanh > 0 ) b"
+ " where a.ku_makh = b.ku_makh and a.ku_mato<> b.ku_mato";
break;
case "M06":
if (pos == "003000")
{
strsql = "with lst1 as ( select a.ku_mapgd, substr(a.ku_madp, 1, 6) maxa, a.ku_mato, count(a.ku_makh) dem from hscv_daily a where a.ku_ngaybc = '" + ng + "' and a.ku_ttmonvay <> 'CLOSE' and a.ku_dnothan + a.ku_dnoqhan + a.ku_dnokhoanh > 0 and a.ku_mato is not null and a.ku_hthucvay = '3' group by a.ku_mapgd, a.ku_madp, a.ku_mato having count(a.ku_makh) <= 5 or count(a.ku_makh) > 60 ) select a.ku_mapgd,b.po_ten,a.maxa,c.ten tenxa, a.ku_mato,d.to_tentt,a.dem sotv from lst1 a left join dmpos b on a.ku_mapgd = b.po_ma left join dmxa c on a.maxa = c.ma left join hsto d on a.ku_mato = d.to_mato order by a.maxa,a.ku_mato";
}
else
{
strsql = "with lst1 as ( select a.ku_mapgd, substr(a.ku_madp, 1, 6) maxa, a.ku_mato, count(a.ku_makh) dem from hscv_daily a where a.ku_ngaybc = '" + ng + "' and a.ku_mapgd='" + pos + "' and a.ku_ttmonvay <> 'CLOSE' and a.ku_dnothan + a.ku_dnoqhan + a.ku_dnokhoanh > 0 and a.ku_mato is not null and a.ku_hthucvay = '3' group by a.ku_mapgd, a.ku_madp, a.ku_mato having count(a.ku_makh) <= 5 or count(a.ku_makh) > 60 ) select a.ku_mapgd,b.po_ten,a.maxa,c.ten tenxa, a.ku_mato,d.to_tentt,a.dem sotv from lst1 a left join dmpos b on a.ku_mapgd = b.po_ma left join dmxa c on a.maxa = c.ma left join hsto d on a.ku_mato = d.to_mato order by a.maxa,a.ku_mato";
}
break;
case "M07":
if (pos == "003000")
{
strsql = "select c.ku_mapgd,substr(c.ku_madp,1,6) maxa,f.ten tenxa,c.ku_mato,g.to_tentt,e.kh_makh,e.kh_tenkh,c.ku_chtrinh,c.ku_maqd, d.ku_chtrinh chtr,d.ku_maqd MAQD from (select a.* from hscv_daily a where a.KU_NGAYBC = '" + ng + "' and a.KU_TTMONVAY <> 'CLOSE' and a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH > 0) c left join hskh e on c.ku_makh = e.kh_makh left join dmxa f on substr(c.ku_madp, 1, 6)= f.ma left join hsto g on substr(c.ku_mato, 1, 6)= g.to_mato ,(select b.* from hscv_daily b where b.KU_NGAYBC = '" + ng + "' and b.KU_TTMONVAY <> 'CLOSE' and b.KU_DNOTHAN + b.KU_DNOQHAN + b.KU_DNOKHOANH > 0) d where c.KU_MAKH = d.KU_MAKH and((c.KU_CHTRINH = '01' and d.KU_CHTRINH in ('09', '10', '15', '19')) or(c.KU_CHTRINH = '09' and d.KU_CHTRINH in ('10', '15', '19')) or(c.KU_CHTRINH = '19' and d.KU_CHTRINH in ('10', '15'))) order by c.ku_madp,c.ku_mato";
}
else
{
strsql = "select c.ku_mapgd,substr(c.ku_madp,1,6) maxa,f.ten tenxa,c.ku_mato,g.to_tentt,e.kh_makh,e.kh_tenkh,c.ku_chtrinh,c.ku_maqd, d.ku_chtrinh chtr,d.ku_maqd MAQD from (select a.* from hscv_daily a where a.ku_mapgd='" + pos + "' and a.KU_NGAYBC = '" + ng + "' and a.KU_TTMONVAY <> 'CLOSE' and a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH > 0) c left join hskh e on c.ku_makh = e.kh_makh left join dmxa f on substr(c.ku_madp, 1, 6)= f.ma left join hsto g on substr(c.ku_mato, 1, 6)= g.to_mato ,(select b.* from hscv_daily b where b.ku_mapgd='" + pos + "' and b.KU_NGAYBC = '" + ng + "' and b.KU_TTMONVAY <> 'CLOSE' and b.KU_DNOTHAN + b.KU_DNOQHAN + b.KU_DNOKHOANH > 0) d where c.KU_MAKH = d.KU_MAKH and((c.KU_CHTRINH = '01' and d.KU_CHTRINH in ('09', '10', '15', '19')) or(c.KU_CHTRINH = '09' and d.KU_CHTRINH in ('10', '15', '19')) or(c.KU_CHTRINH = '19' and d.KU_CHTRINH in ('10', '15'))) order by c.ku_madp,c.ku_mato";
}
break;
case "M08":
if (pos == "003000")
{
strsql = "select c.KH_MAPGD POS,substr(c.KH_MADP,1,6) MAXA,(select TEN from DMXA where MA=substr(c.KH_MADP,1,6)) TENXA ,b.CS_MATO,(select TO_TENTT from HSTO where TO_MATO = b.CS_MATO)TENTT, c.KH_MAKH,c.KH_TENKH,a.DUNO,b.CS_MAKH,b.CS_SODU_TK from ( select a.KU_MAKH, sum(a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH) DUNO from HSCV_DAILY a where a.KU_NGAYBC = '" + ng + "' group by a.KU_MAKH having sum(a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH) = 0 ) a , ( select a.CS_MATO,a.CS_MAKH,a.CS_SODU_TK from CASA_DAILY a where a.CS_NGAYBC = '" + ng + "' and a.CS_SP_TK = '105' and a.CS_TTSO_TK <> 'C' and a.CS_MATO is not null ) b,HSKH c where a.KU_MAKH = b.CS_MAKH and a.KU_MAKH = c.KH_MAKH order by c.KH_MADP,b.CS_MATO";
}
else
{
strsql = "select c.KH_MAPGD POS,substr(c.KH_MADP,1,6) MAXA,(select TEN from DMXA where MA=substr(c.KH_MADP,1,6)) TENXA ,b.CS_MATO,(select TO_TENTT from HSTO where TO_MATO = b.CS_MATO)TENTT, c.KH_MAKH,c.KH_TENKH,a.DUNO,b.CS_MAKH,b.CS_SODU_TK from ( select a.KU_MAKH, sum(a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH) DUNO from HSCV_DAILY a where a.KU_NGAYBC = '" + ng + "' and a.KU_MAPGD = '" + pos + "' group by a.KU_MAKH having sum(a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH) = 0 ) a , ( select a.CS_MATO,a.CS_MAKH,a.CS_SODU_TK from CASA_DAILY a where a.CS_NGAYBC = '" + ng + "' and a.CS_MAPGD = '" + pos + "' and a.CS_SP_TK = '105' and a.CS_TTSO_TK <> 'C' and a.CS_MATO is not null ) b,HSKH c where a.KU_MAKH = b.CS_MAKH and a.KU_MAKH = c.KH_MAKH order by c.KH_MADP,b.CS_MATO";
}
break;
case "M09":
if (pos == "003000")
{
strsql = "select a.ku_mapgd,c.po_ten,a.MAXA,d.ten tenxa,a.KU_MATO,e.to_tentt,b.KH_MAKH,b.KH_TENKH,concat(chr(39),a.KU_SOKU) SOKU,a.DUNO,a.KU_CAPQLV from ( select substr(a.KU_MADP, 1, 6) MAXA, a.ku_mapgd, a.ku_makh, a.KU_MATO, a.ku_soku, a.KU_CAPQLV, a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH DUNO from HSCV_DAILY a where a.KU_NGAYBC = '" + ng + "' and a.KU_TTMONVAY <> 'CLOSE' and a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH > 0 and a.KU_CHTRINH = '03' and a.KU_CAPQLV is null ) a left join dmpos c on a.ku_mapgd = c.po_ma left join dmxa d on a.maxa = d.ma left join hsto e on a.ku_mato = e.to_mato ,HSKH b where a.KU_MAKH = b.KH_MAKH order by a.KU_MAPGD,a.MAXA,a.KU_MATO";
}
else
{
strsql = "select a.ku_mapgd,c.po_ten,a.MAXA,d.ten tenxa,a.KU_MATO,e.to_tentt,b.KH_MAKH,b.KH_TENKH,concat(chr(39),a.KU_SOKU) SOKU,a.DUNO,a.KU_CAPQLV from ( select substr(a.KU_MADP, 1, 6) MAXA, a.ku_mapgd, a.ku_makh, a.KU_MATO, a.ku_soku, a.KU_CAPQLV, a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH DUNO from HSCV_DAILY a where a.ku_mapgd='" + pos + "' and a.KU_NGAYBC = '" + ng + "' and a.KU_TTMONVAY <> 'CLOSE' and a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH > 0 and a.KU_CHTRINH = '03' and a.KU_CAPQLV is null ) a left join dmpos c on a.ku_mapgd = c.po_ma left join dmxa d on a.maxa = d.ma left join hsto e on a.ku_mato = e.to_mato ,HSKH b where a.KU_MAKH = b.KH_MAKH order by a.KU_MAPGD,a.MAXA,a.KU_MATO";
}
break;
case "M10":
dt = cls.LoadDataProcPara("usp_KT74010", bien, giatri, thamso);
proc = "1";
break;
case "M11":
dt = cls.LoadDataProcPara("usp_KT74023", bien, giatri, thamso);
proc = "1";
break;
case "M12":
strsql = "select cs_mapgd,substr(cs_madp,1,6) maxa,ten TENXA,cs_makh,kh_tenkh,cs_mato,concat(chr(39),cs_so_tk2) sotk,"
+ " cs_sodu_tk ,cs_ttso_tk ,to_date(cs_ngayroito, 'DD-MM-YYYY') NG_ROITO,"
+ " N'KH đã rời tổ quá 90 ngày,đề nghị rà soát theo VB3497/NHCS-TDNN' GhiChu"
+ " from casa_daily, dmxa, hskh where cs_ngaybc = '" + ng + "' and to_date('" + ng + "') - cs_ngayroito > 90"
+ " and cs_sodu_tk >= 0 and cs_sodu_tk<= 100000 and NVL(cs_ttso_tk, 'A') <> 'C' and cs_mato is null"
+ " and substr(cs_madp,1, 6)= ma and cs_makh = kh_makh order by substr(cs_madp, 1, 6),cs_makh";
break;
case "M13":
strsql = "with lst1 as ( select ku_makh, ku_mato, sum(NVL(ku_dnothan, 0) + NVL(ku_dnoqhan, 0) + NVL(ku_dnokhoanh, 0)) duno "
+ " from hscv_daily where ku_ngaybc ='" + ng + "' and ku_ttmonvay <> 'CLOSE' and NVL(ku_dnothan, 0) + NVL(ku_dnoqhan, 0) + NVL(ku_dnokhoanh, 0) > 0 "
+ " group by ku_makh, ku_mato ) select ROW_NUMBER() OVER(PARTITION BY a.kh_mapgd ORDER BY a.kh_mapgd) STT, a.kh_mapgd, a.kh_madp, b.ku_mato, c.to_tentt, a.kh_makh, a.kh_tenkh, a.kh_diachi, b.duno, a.kh_ngaycap "
+ " , round((TO_DATE('" + ng + "') -TO_DATE(a.kh_ngaycap))/ 365,0) sonam "
+ " from hskh a, lst1 b left join hsto c on b.ku_mato = c.to_mato "
+ " where a.kh_makh = b.ku_makh and TO_DATE ('" + ng + "') -TO_DATE(a.kh_ngaycap) > 15 * 365 "
+ " order by a.kh_mapgd,a.kh_madp,b.ku_mato,a.kh_makh";
break;
case "M14":
strsql = "select ROW_NUMBER() OVER(PARTITION BY a.ku_mapgd ORDER BY a.ku_mapgd) STT,a.ku_mapgd,a.ku_madp,C.TEN TENXA"
+ " ,a.ku_mato,d.to_tentt ,a.ku_makh,b.kh_tenkh,concat(chr(39), a.ku_soku) SOKU,a.ku_ngayvay,a.ku_dnothan,a.ku_dnoqhan,a.ku_dnokhoanh "
+ " ,round((TO_DATE('" + ng + "') - TO_DATE(a.ku_ngayvay)) / 30, 0) sothang,a.ku_ttmonvay,a.ku_maqd,e.giatri from hscv_daily a"
+ " left join hskh b on a.ku_makh = b.kh_makh left join dmxa c on substr(a.ku_madp, 1, 6)= c.ma "
+ " left join hsto d on a.ku_mato = d.to_mato left join (select * from dmkhac where khoa_1='07') e on A.KU_MAQD=e.khoa_2"
+ " where a.ku_ngaybc ='" + ng + "' and a.ku_ttmonvay <> 'CLOSE' and a.ku_gngan = 0"
+ " and TO_DATE ('" + ng + "') -TO_DATE(a.ku_ngayvay) > 90 order by a.ku_mapgd,a.ku_madp,a.ku_mato,a.ku_makh";
break;
case "M15":
strsql = "with lst1 as ("
+ " select a.KU_MAPGD, KU_MADP, d.TEN TENXA, KU_MATO, c.TO_TENTT, b.KH_MAKH, b.KH_TENKH, concat(chr(39), a.KU_SOKU) SOKU, a.KU_NGAYVAY, a.KU_DNOTHAN, a.KU_DNOQHAN, a.KU_DNOKHOANH, a.KU_MANDT, a.KU_CAPQLV, a.KU_NGUONVON, a.KU_HTHUCVAY, a.PL_NGUONVON_BS "
+ ", (case when a.KU_NGUONVON = '1' and a.PL_NGUONVON_BS = '01' and a.KU_MANDT <> 'INV0107190050391' then 'F'"
+ " when a.KU_NGUONVON = '1' and a.PL_NGUONVON_BS = '02' and (a.KU_MANDT is not null or a.KU_MANDT<>'') then 'F'"
+ " when a.KU_NGUONVON = '1' and a.PL_NGUONVON_BS = '01' and (a.KU_MANDT is null or a.KU_MANDT='') then 'F'"
+ " when a.KU_NGUONVON = '2' and (a.KU_MANDT = 'INV0107190050391' or a.KU_MANDT is null) then 'F'"
+ " else 'T'end) TT from HSCV_DAILY a left join hskh b on a.KU_MAKH = b.KH_MAKH"
+ " left join hsto c on a.KU_MATO = c.TO_MATO left join DMXA d on substr(a.KU_MADP, 1, 6)= d.MA"
+ " where a.KU_NGAYBC = '" + ng + "' and a.KU_CHTRINH = '03' and a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH > 0 and a.KU_TTMONVAY <> 'CLOSE'"
+ " ) select* from lst1 where tt = 'F' order by KU_MADP,KU_MATO";
break;
case "M16":
strsql = "with lst1 as ("
+ " select ROW_NUMBER() OVER(PARTITION BY a.KU_MAKH ORDER BY a.KU_MADP, a.ku_makh) STT, a.ku_mapgd, substr(a.ku_madp, 1, 6) MAXA, concat(chr(39), " + " a.ku_soku) soku , a.ku_mato, a.ku_makh, a.ku_dnothan, a.ku_dnoqhan, a.ku_dnokhoanh, a.ku_laitonthan + a.ku_laitonqhan LAITON "
+ " from hscs_daily a where a.ku_ngaybc = '" + ng + "' and ku_ttmonvay <> 'CLOSE' and a.ku_laitonthan + a.ku_laitonqhan > 100000 "
+ " ),lst2 as ( select a.*,c.du from lst1 a"
+ " left join (select b.cs_makh, sum(b.cs_sodu_tk) du from casa_daily b where b.cs_ngaybc = '" + ng + "' and cs_sp_tk = '105' group by b.cs_makh " + " having sum(b.cs_sodu_tk) >= 100000) c on a.ku_makh = c.cs_makh and a.stt = 1 ), lst3 as "
+ " ( select a.* from lst2 a where a.du > 0 )"
+ " select a.stt,a.ku_mapgd,d.po_ten,a.maxa,e.ten tenxa, a.ku_mato,f.to_tentt,a.ku_makh,g.kh_tenkh,a.soku,"
+ " a.ku_dnothan,a.ku_dnoqhan,a.ku_dnokhoanh,a.LAITON,a.du DUTK105 from lst2 a left "
+ " join dmpos d on a.ku_mapgd = d.po_ma "
+ " left join dmxa e on a.maxa = e.ma "
+ " left join hsto f on a.ku_mato = f.to_mato "
+ " left join hskh g on a.ku_makh = g.kh_makh "
+ " where a.ku_makh in (select b.ku_makh from lst3 b where a.ku_makh = b.ku_makh) order by a.maxa,a.ku_mato,a.ku_makh,a.stt";
break;
case "M17":
strsql = "with lst1 as ( select a.KU_MAPGD, substr(a.KU_MADP, 1, 6) MAXA, a.KU_MADP, a.KU_MATO, a.KU_MAKH, a.KU_SOKU, a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH DUNO from HSCV_DAILY a where a.ku_ngaybc = '" + ng + "' and a.KU_MATO is not null ), lst2 as ( select a.CS_MAPGD,substr(a.CS_MADP, 1, 6) MAXA,a.CS_MADP,a.CS_MATO,a.cs_MAKH,a.CS_SO_TK,a.CS_SODU_TK DUTK from CASA_DAILY a where a.cs_ngaybc = '" + ng + "' and a.CS_SP_TK = '105' and a.CS_MATO is not null ), lst3 as ( select a.KU_MAPGD,a.MAXA,a.KU_MATO,sum(a.DUNO) DUNO from lst1 a group by a.KU_MAPGD,a.MAXA,a.KU_MATO having sum(a.DUNO) = 0 ), lst4 as ( select a.CS_MAPGD,a.MAXA,a.CS_MATO,sum(a.DUTK) DUTK from lst2 a group by a.CS_MAPGD,a.MAXA,a.CS_MATO having sum(a.DUTK) = 0 ) select a.*,b.CS_MATO,b.DUTK from lst3 a, lst4 b where a.KU_MATO = b.CS_MATO";
break;
case "M18":
strsql = "with lst1 as ( select a.KU_MAPGD, substr(a.KU_MADP, 1, 6) MAXA, a.KU_MADP, a.KU_MATO, a.KU_MAKH, a.KU_SOKU, a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH DUNO from HSCV_DAILY a where a.ku_ngaybc = '" + ng + "' and a.KU_MATO is not null ), lst2 as ( select a.CS_MAPGD,substr(a.CS_MADP, 1, 6) MAXA,a.CS_MADP,a.CS_MATO,a.cs_MAKH,a.CS_SO_TK,a.CS_SODU_TK DUTK from CASA_DAILY a where a.cs_ngaybc = '" + ng + "' and a.CS_SP_TK = '105' and a.CS_MATO is not null ), lst3 as ( select a.KU_MAPGD,a.MAXA,a.KU_MATO,sum(a.DUNO) DUNO from lst1 a group by a.KU_MAPGD,a.MAXA,a.KU_MATO having sum(a.DUNO) = 0 ), lst4 as ( select a.CS_MAPGD,a.MAXA,a.CS_MATO,sum(a.DUTK) DUTK from lst2 a group by a.CS_MAPGD,a.MAXA,a.CS_MATO having sum(a.DUTK) > 0 ) select a.*,b.CS_MATO,b.DUTK from lst3 a, lst4 b where a.KU_MATO = b.CS_MATO";
break;
case "M19":
strsql = "with lst1 as ( select a.KU_MAPGD, substr(a.KU_MADP, 1, 6) MAXA, a.KU_MADP, a.KU_MATO, a.KU_MAKH, a.KU_SOKU, a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH DUNO from HSCV_DAILY a where a.ku_ngaybc = '" + ng + "' and a.KU_MATO is not null ), lst2 as ( select a.CS_MAPGD,substr(a.CS_MADP, 1, 6) MAXA,a.CS_MADP,a.CS_MATO,a.cs_MAKH,a.CS_SO_TK,a.CS_SODU_TK DUTK from CASA_DAILY a where a.cs_ngaybc = '" + ng + "' and a.CS_SP_TK = '105' and a.CS_MATO is not null ), lst3 as ( select a.KU_MAPGD,a.MAXA,a.KU_MATO,sum(a.DUNO) DUNO from lst1 a group by a.KU_MAPGD,a.MAXA,a.KU_MATO having sum(a.DUNO) > 0 ), lst4 as ( select a.CS_MAPGD,a.MAXA,a.CS_MATO,sum(a.DUTK) DUTK from lst2 a group by a.CS_MAPGD,a.MAXA,a.CS_MATO having sum(a.DUTK) = 0 ) select a.*,b.CS_MATO,b.DUTK from lst3 a, lst4 b where a.KU_MATO = b.CS_MATO";
break;
case "M20":
strsql = "with lst1 as ( select a.KU_MAPGD, substr(a.KU_MADP, 1, 6) MAXA, a.KU_MADP, a.KU_MATO, a.KU_MAKH, a.KU_SOKU, a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH DUNO from HSCV_DAILY a where a.ku_ngaybc = '" + ng + "' and a.KU_MATO is not null and a.ku_maqd <> '26' ), lst2 as ( select a.CS_MAPGD,substr(a.CS_MADP, 1, 6) MAXA,a.CS_MADP,a.CS_MATO,a.cs_MAKH,a.CS_SO_TK,a.CS_SODU_TK DUTK from CASA_DAILY a where a.cs_ngaybc = '" + ng + "' and a.CS_SP_TK = '105' and a.CS_MATO is not null), lst3 as ( select a.KU_MAPGD,a.MAXA,a.KU_MATO,a.KU_MAKH,sum(a.DUNO) DUNO from lst1 a group by a.KU_MAPGD,a.MAXA,a.KU_MATO,a.ku_makh ), lst4 as ( select a.CS_MAPGD,a.MAXA,a.CS_MATO,a.CS_MAKH,sum(a.DUTK) DUTK from lst2 a group by a.CS_MAPGD,a.MAXA,a.CS_MATO,a.cs_makh ) select a.ku_mapgd,c.po_ten,a.maxa,d.ten tenxa, a.ku_mato,e.to_tentt,a.ku_makh,f.KH_TENKH,a.duno,a.duno / 10 DU_PT,b.DUTK from lst3 a left join dmpos c on a.ku_mapgd = c.po_ma left join dmxa d on a.maxa = d.ma left join hsto e on a.ku_mato = e.to_mato left join hskh f on a.ku_makh = f.kh_makh , lst4 b where a.KU_MATO = b.CS_MATO and a.ku_makh = b.cs_makh and b.dutk > a.duno / 10 order by a.maxa,a.ku_mato,a.ku_makh";
break;
case "M21":
strsql = "with lst1 as ( select a.KU_MAPGD, substr(a.KU_MADP, 1, 6) MAXA, a.KU_MADP, a.KU_MATO, a.KU_MAKH, a.KU_SOKU, a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH DUNO from HSCV_DAILY a where a.ku_ngaybc = '" + ng + "' and a.KU_MATO is not null and a.ku_maqd <> '26' ), lst2 as ( select a.CS_MAPGD,substr(a.CS_MADP, 1, 6) MAXA,a.CS_MADP,a.CS_MATO,a.cs_MAKH,a.CS_SO_TK,a.CS_SODU_TK DUTK from CASA_DAILY a where a.cs_ngaybc = '" + ng + "' and a.CS_SP_TK = '105' and a.CS_MATO is not null ), lst3 as ( select a.KU_MAPGD,a.MAXA,a.KU_MATO,a.KU_MAKH,sum(a.DUNO) DUNO from lst1 a group by a.KU_MAPGD,a.MAXA,a.KU_MATO,a.ku_makh having sum(a.DUNO) > 0 ), lst4 as ( select a.CS_MAPGD,a.MAXA,a.CS_MATO,a.CS_MAKH,sum(a.DUTK) DUTK from lst2 a group by a.CS_MAPGD,a.MAXA,a.CS_MATO,a.cs_makh having sum(a.DUTK) = 0 ) select a.ku_mapgd,c.po_ten,a.maxa,d.ten tenxa, a.ku_mato,e.to_tentt,a.ku_makh,f.KH_TENKH,a.duno,b.DUTK from lst3 a left join dmpos c on a.ku_mapgd = c.po_ma left join dmxa d on a.maxa = d.ma left join hsto e on a.ku_mato = e.to_mato left join hskh f on a.ku_makh = f.kh_makh , lst4 b where a.KU_MATO = b.CS_MATO and a.ku_makh = b.cs_makh order by a.maxa,a.ku_mato,a.ku_makh";
break;
case "M22":
dt = cls.LoadDataProcPara("usp_KT74004", bien, giatri, thamso);
proc = "1";
break;
case "M23":
dt = cls.LoadDataProcPara("usp_KT74005", bien, giatri, thamso);
proc = "1";
break;
case "M24":
dt = cls.LoadDataProcPara("usp_KT74025", bien, giatri, thamso);
proc = "1";
break;
case "M25":
dt = cls.LoadDataProcPara("usp_KT74018", bien, giatri, thamso);
proc = "1";
break;
case "M26":
dt = cls.LoadDataProcPara("usp_KT74019", bien, giatri, thamso);
proc = "1";
break;
case "M27":
dt = cls.LoadDataProcPara("usp_KT74023", bien, giatri, thamso);
proc = "1";
break;
case "M28":
dt = cls.LoadDataProcPara("usp_KT74024", bien, giatri, thamso);
proc = "1";
break;
case "M29":
dt = cls.LoadDataProcPara("usp_TangPhien", bien, giatri, thamso);
proc = "1";
break;
case "M30":
dt = cls.LoadDataProcPara("usp_kt74027", bien, giatri, thamso);
proc = "1";
break;
case "M31":
dt = cls.LoadDataProcPara("usp_kt74028", bien, giatri, thamso);
proc = "1";
break;
case "M32":
strsql = "select a.*, b.tong_duno, dnth, dnqh, dnkh from (SELECT substr(cs_madp, 1, 6) maxa, ten tenxa,cs_mato mato,to_tentt tentt,cs_makh makh,cs_tentk tenkh,sum(CS_SODU_TK) du_tk,cs_ngaybc ngaybc FROM casa_daily, hsto, dmxa WHERE cs_mapgd = '" + pos + "' AND CS_SP_TK = '105' AND cs_ngaybc = '" + ng + "' and cs_mato = to_mato and to_loaito = '01' and CS_TTSO_TK = 'A' and substr(cs_madp, 1, 6) = ma group by substr(cs_madp, 1, 6), ten, cs_mato, to_tentt, cs_makh, cs_tentk, cs_ngaybc having sum(CS_SODU_TK) >= 5000000) a left join (select SUBSTR(KU_MADP, 1, 6) MAXA, KU_MATO mato, to_tentt tentt, ku_makh makh, SUM(KU_DNOTHAN) + SUM(KU_DNOQHAN) + SUM(KU_DNOKHOANH) TONG_DUNO, SUM(KU_DNOTHAN) DNTH, SUM(KU_DNOQHAN) DNQH,SUM(KU_DNOKHOANH) DNKH FROM HSCV_DAILY, hsto WHERE KU_NGAYBC = '" + ng + "' AND KU_MApgd = '" + pos + "' and ku_mato = to_mato AND KU_TTMONVAY <> 'CLOSE' and trangthai = 'A' GROUP BY SUBSTR(KU_MADP, 1, 6), KU_MATO, to_tentt, ku_makh) B on a.makh = b.makh order by a.maxa, a.mato, a.makh";
break;
case "M33":
strsql = "select a.KU_MAPGD,LEFT(a.KU_MADP,6) MAXA,b.TEN TENXA,a.KU_MATO,c.TO_TENTT,a.KU_MAKH,d.KH_TENKH,char(39)+a.KU_SOKU SOKU,a.KU_NGAYGNDT NG_VAY,a.KU_DNOTHAN,a.KU_DNOQHAN,a.KU_DNOKHOANH,a.KU_MAQD,e.GIATRI CHTRINH from HSCV_DAILY a left join DMXA b on b.MA = LEFT(a.KU_MADP, 6) and b.TRANGTHAI = 'A' left join HSTO c on c.TO_MATO = a.KU_MATO and c.TRANGTHAI = 'A' left join hskh d on d.KH_MAKH = a.KU_MAKH left join (select * from DMKHAC where KHOA_1 = '07') e on a.KU_MAQD = e.KHOA_2 where a.KU_NGAYBC = '" + ngsql + "' and a.KU_MAPGD = '" + pos + "' and a.KU_TTMONVAY <> 'CLOSE' and a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH > 0 and a.KU_SOKU in (select SOKU from MAU06 where SOKU = A.KU_SOKU and MAPOS = '" + pos + "' and MAPOS = a.KU_MAPGD and TRANGTHAI1 = 1 and NGAY_VAY >= '2018-12-31') order by a.KU_MADP,a.KU_MATO,a.KU_MAKH";
sql = "1";
break;
}
//MessageBox.Show(mau);
if (proc == "0")
{
if (sql == "1")
{
dt = cls.LoadDataText(strsql);
}
else
{
dt = ora.LoadDataText(strsql);
}
}
if (dt.Rows.Count > 0)
{
FileName = Thumuc + "\\" + pos + "_" + CboMau.SelectedValue.ToString().Substring(5, CboMau.SelectedValue.ToString().Length - 5) + "_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
if (mau == "M12" || mau == "M20" || mau == "M21")
{
FileStream fs = new FileStream(FileName, FileMode.Create);
StreamWriter sw = new StreamWriter(fs, Encoding.Unicode);
str.ToCSV(dt, sw, true);
}
else
{
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, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
cls.DongKetNoi();
}
private void btnOk_Click(object sender, RoutedEventArgs e)
{
bll.TaoThuMuc(Thumuc);
cls.ClsConnect();
try
{
const int thamso = 5;
string[] bien = new string[thamso];
object[] giatri = new object[thamso];
bien[0] = "@Ngay";
if (dtpNgay.SelectedDate != null)
{
giatri[0] = dtpNgay.SelectedDate.Value.ToString("dd/MM/yyyy");
}
bien[1] = "@MaPos";
giatri[1] = bll.Left(CboPos.SelectedValue.ToString(), 6);
bien[2] = "@ChTr";
giatri[2] = bll.Left(CboChTr.SelectedValue.ToString(), 2);
bien[3] = "@Mau";
bien[4] = "@Ngayktah";
if (dtpNgayktah.SelectedDate != null)
{
giatri[4] = dtpNgayktah.SelectedDate.Value.ToString("dd/MM/yyyy");
}
if (radioButton1.IsChecked == true)
{
giatri[3] = "1";
Mau = "M1_";
}
else if (radioButton2.IsChecked == true)
{
giatri[3] = "2";
Mau = "M2_";
}
else if (radioButton3.IsChecked == true)
{
giatri[3] = "3";
Mau = "M3_";
}
else
{
giatri[3] = "4";
Mau = "M4_";
}
dt = cls.LoadDataProcPara("usp_KTNN", bien, giatri, thamso);
if (dt.Rows.Count > 0)
{
//FileName = Thumuc + "\\"+Mau.Trim()+ bll.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_" +bll.Left(CboChTr.SelectedValue.ToString().Trim(), 2) + "_" +dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".xlsx";
FileName = Thumuc + "\\" + Mau.Trim() + bll.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_" + bll.Left(CboChTr.SelectedValue.ToString().Trim(), 2) + "_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
//MessageBox.Show("Chú ý việc xuất Excel toàn bộ thực hiện khoảng 15p, cho đến khi thông báo OK : ","Thông báo",MessageBoxButton.OK,MessageBoxImage.Information);
FileStream fs = new FileStream(FileName, FileMode.Create);
StreamWriter sw = new StreamWriter(fs, Encoding.Unicode);
bll.ToCSV(dt, sw, true);
//bll.WriteDataTableToExcel(dt, "Person Details", FileName, "TUTM");
MessageBox.Show("Copy Excel to : " + FileName + " OK", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
else
{
MessageBox.Show("Không có bản ghi nào", "Thông báo", MessageBoxButton.OK,
MessageBoxImage.Information);
}
}
catch (Exception ex)
{
MessageBox.Show("Lổi, liên hệ phòng tin học " + ex.Message, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Error);
}
cls.DongKetNoi();
}
private void btnOk_Click(object sender, RoutedEventArgs e)
{
cls.ClsConnect();
bll.TaoThuMuc(Thumuc);
try
{
string NGAY = dtpNgay.SelectedDate.Value.ToString("dd/MM/yyyy");
string TransCd = bll.Left(CboTranCd.SelectedValue.ToString().Trim(), 10);
string strkt = "select * from OfflineUp where TransCd = '" + TransCd + "'" + " and NGAYOFL = '" + NGAY + "'";
cnn.ClsConnect();
dtkt = cnn.LoadDataText(strkt);
if (dtkt.Rows.Count > 0)
{
MessageBox.Show("Đã thực hiện xuất số liệu, không thực hiện nữa", "Thông báo", MessageBoxButton.OK,
MessageBoxImage.Warning);
btnOk.IsEnabled = false;
return;
}
else
{
#region
string str = "select '00'+left(a.CommuneId,4) as MAPOS,c.TransCd" +
" ,right(LEFT(b.BusinessDt,10),2)+'/'+SUBSTRING(LEFT(b.BusinessDt,10),5,2)+'/'+left(b.BusinessDt,4) as NGAY " +
" ,right(LEFT(b.BusinessDt,10),2)+'-'+SUBSTRING(LEFT(b.BusinessDt,10),5,2)+'-'+left(b.BusinessDt,4) as NGAYQB " +
" ,'KU' as MA,'11' as MNV,a.CommuneId as KH_MADP,left(a.CommuneId,6) as MAXA,c.GroupId as MATO, a.LoanNo as SOKU " +
" ,a.CustomerId as MAKH,c.FullName as TENKH,LEFT(a.LoanProgramShort,2) as CHTRINH,a.Amount as MUCVAY" +
",CONVERT(decimal(28,12),0) as TNKH,CONVERT(decimal(28,12),0) as TNQH,a.FreezeAmount as DNKH,a.OverDueAmount as DNQH, " +
" b.DisbursalAmount as SOTIEN ,right(LEFT(b.MakerDt,10),2)+'/'+SUBSTRING(LEFT(b.MakerDt,10),6,2)+'/'+left(b.MakerDt,4) as NGAYGD,'GN' as MOTA"+
" from vLoanDetail a, BulkDisbursal b,Customer c where b.DisbursalAmount>0 and c.GroupId<>'NOGROUP' and a.LoanNo=b.LoanNo and a.CustomerId=c.CustomerId " +
" and right(LEFT(b.BusinessDt,10),2)+'/'+SUBSTRING(LEFT(b.BusinessDt,10),5,2)+'/'+left(b.BusinessDt,4) ='" +
NGAY + "'" +
" union select '00'+left(a.CommuneId,4) as MAPOS,c.TransCd "+
" ,RIGHT(b.BussinessDt, 2) + '/' + SUBSTRING(convert(varchar(10),b.BussinessDt), 5, 2) + '/' + LEFT(b.BussinessDt, 4) as NGAY " +
" ,RIGHT(b.BussinessDt, 2) + '-' + SUBSTRING(convert(varchar(10),b.BussinessDt), 5, 2) + '-' + LEFT(b.BussinessDt, 4) as NGAYQB " +
" ,'KU' as MA,'12' as MNV,a.CommuneId as KH_MADP,left(a.CommuneId,6) as MAXA,c.GroupId as MATO, a.LoanNo as SOKU " +
" ,a.CustomerId as MAKH,c.FullName as TENKH,LEFT(a.LoanProgramShort,2) as CHTRINH,a.Amount as MUCVAY" +
",CONVERT(decimal(28,12),0) as TNKH,CONVERT(decimal(28,12),0) as TNQH,a.FreezeAmount as DNKH,a.OverDueAmount as DNQH, " +
" b.TotalPrinPaid as SOTIEN ,right(LEFT(b.MakerDt,10),2)+'/'+SUBSTRING(LEFT(b.MakerDt,10),6,2)+'/'+left(b.MakerDt,4) as NGAYGD,'TN' as MOTA "+
" from vLoanDetail a, BulkPayment b,Customer c where b.TotalPrinPaid>0 and c.GroupId<>'NOGROUP' and a.LoanNo=b.LoanNo and a.CustomerId=c.CustomerId " +
" and RIGHT(b.BussinessDt, 2) + '/' + SUBSTRING(convert(varchar(10),b.BussinessDt), 5, 2) + '/' + LEFT(b.BussinessDt, 4) = '" +
NGAY + "'" +
" union select '00'+left(d.CommuneId,4) as MAPOS,c.TransCd "+
" ,RIGHT(b.BussinessDt, 2) + '/' + SUBSTRING(convert(varchar(10),b.BussinessDt), 5, 2) + '/' + LEFT(b.BussinessDt, 4) as NGAY " +
" ,RIGHT(b.BussinessDt, 2) + '-' + SUBSTRING(convert(varchar(10),b.BussinessDt), 5, 2) + '-' + LEFT(b.BussinessDt, 4) as NGAYQB " +
" ,'TK' as MA,'13' as MNV,d.CommuneId as KH_MADP,left(d.CommuneId,6) as MAXA,c.GroupId as MATO, a.CasaNo as SOKU " +
" ,a.CustomerId as MAKH,c.FullName as TENKH,'105' as CHTRINH,0 as MUCVAY" +
",CONVERT(decimal(28,12),0) as TNKH,CONVERT(decimal(28,12),0) as TNQH,0 as DNKH,0 as DNQH, " +
" b.Deposit as SOTIEN,right(LEFT(b.MakerDt,10),2)+'/'+SUBSTRING(LEFT(b.MakerDt,10),6,2)+'/'+left(b.MakerDt,4) as NGAYGD,'GUITK' as MOTA " +
" from Casa a, MicroSaving b,Customer c,vLoanDetail d " +
" where c.GroupId<>'NOGROUP' and a.CasaNo=b.CasaNo and a.CustomerId=c.CustomerId and c.CustomerId=a.CustomerId and a.CustomerId=d.CustomerId and b.Deposit<>0 " +
" and RIGHT(b.BussinessDt, 2) + '/' + SUBSTRING(convert(varchar(10),b.BussinessDt), 5, 2) + '/' + LEFT(b.BussinessDt, 4) = '" +
NGAY + "'" +
" union select '00'+left(d.CommuneId,4) as MAPOS,c.TransCd "+
" ,RIGHT(b.BussinessDt, 2) + '/' + SUBSTRING(convert(varchar(10),b.BussinessDt), 5, 2) + '/' + LEFT(b.BussinessDt, 4) as NGAY " +
" ,RIGHT(b.BussinessDt, 2) + '-' + SUBSTRING(convert(varchar(10),b.BussinessDt), 5, 2) + '-' + LEFT(b.BussinessDt, 4) as NGAYQB " +
" ,'TK' as MA,'14' as MNV,d.CommuneId as KH_MADP,left(d.CommuneId,6) as MAXA,c.GroupId as MATO, a.CasaNo as SOKU " +
" ,a.CustomerId as MAKH,c.FullName as TENKH,'105' as CHTRINH,0 as MUCVAY" +
",CONVERT(decimal(28,12),0) as TNKH,CONVERT(decimal(28,12),0) as TNQH,0 as DNKH,0 as DNQH, " +
" b.Withdrawal as SOTIEN,right(LEFT(b.MakerDt,10),2)+'/'+SUBSTRING(LEFT(b.MakerDt,10),6,2)+'/'+left(b.MakerDt,4) as NGAYGD,'RUTTK' as MOTA " +
" from Casa a, MicroSaving b,Customer c,vLoanDetail d " +
" where c.GroupId<>'NOGROUP' and a.CasaNo=b.CasaNo and a.CustomerId=c.CustomerId and c.CustomerId=a.CustomerId and a.CustomerId=d.CustomerId and b.Withdrawal<>0 " +
" and RIGHT(b.BussinessDt, 2) + '/' + SUBSTRING(convert(varchar(10),b.BussinessDt), 5, 2) + '/' + LEFT(b.BussinessDt, 4) = '" +
NGAY + "'";
#endregion
dt = cls.LoadDataText(str);
if (dt.Rows.Count > 0)
{
#region
if (dt.Rows[0]["TransCd"].ToString().Trim() ==
bll.Left(CboTranCd.SelectedValue.ToString().Trim(), 10) &&
dt.Rows[0]["NGAY"].ToString().Trim() == NGAY)
{
dgvData.ItemsSource = dt.DefaultView;
//MessageBox.Show(dt.Rows[0]["TransCd"].ToString().Trim() + " " + dt.Rows[0]["NGAY"].ToString().Trim());
InsertToTable();
//ClsConnectLocal cn = new ClsConnectLocal();
cnn.ClsConnect();
string ver = "insert into OfflineUp (MAPOS,TransCd,NGAYOFL) values ('" + dt.Rows[0]["MAPOS"] +
"','" + dt.Rows[0]["TransCd"] + "','" + dt.Rows[0]["NGAY"] + "')";
cnn.UpdateDataText(ver);
cnn.DongKetNoi();
}
else
{
MessageBox.Show("Không đúng điểm giao dịch hoặc sai ngày ", "Thông báo", MessageBoxButton.OK,
MessageBoxImage.Warning);
return;
}
#endregion
}
else
{
MessageBox.Show("không có dữ liệu, có thể xem lại ngày");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
cls.DongKetNoi();
}
private void OK_OnClick(object sender, RoutedEventArgs e)
{
bll.TaoThuMuc(Thumuc);
try
{ cls.ClsConnect();
int thamso = 4;
string[] bien = new string[thamso];
object[] giatri = new object[thamso];
bien[0] = "@MaPos";
giatri[0] = bll.Left(CboPos.SelectedValue.ToString().Trim(), 6);
bien[1] = "@Ngay";
if (DtpNgay.SelectedDate != null)
{
giatri[1] = DtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
bien[2] = "@DenNgay";
if (DtpDenNgay.SelectedDate != null)
{
giatri[2] = DtpDenNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
bien[3] = "@Ky";
// MessageBox.Show(giatri[1].ToString()+" "+giatri[2].ToString());
if (RadioButton1.IsChecked == true)
{
FileName = Thumuc + "\\" + bll.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_" + DtpDenNgay.SelectedDate.Value.ToString("ddMMyyyy") + "_SkeNdh_UT.csv";
giatri[3] = "1";
}
else if (RadioButton2.IsChecked == true)
{
FileName = Thumuc + "\\" + bll.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_" + DtpDenNgay.SelectedDate.Value.ToString("ddMMyyyy") + "_SkeNdh_TT_KyCon.csv";
giatri[3] = "2";
}
else if (RadioButton3.IsChecked == true)
{
FileName = Thumuc + "\\" + bll.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_" + DtpDenNgay.SelectedDate.Value.ToString("ddMMyyyy") + "_SkeNdh_PhanKy.csv";
giatri[3] = "1";
}
else
{
FileName = Thumuc + "\\" + bll.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_" + DtpDenNgay.SelectedDate.Value.ToString("ddMMyyyy") + "_TongHop_NDH.csv";
giatri[3] = "1";
}
if (RadioButton1.IsChecked == true || RadioButton2.IsChecked == true)
{
dt = cls.LoadDataProcPara("usp_SkeNdh", bien, giatri, thamso);
}
else if (RadioButton3.IsChecked == true)
{
dt = cls.LoadDataProcPara("usp_SkeNdhPhanKy", bien, giatri, thamso);
}
else
{
dt = cls.LoadDataProcPara("usp_TH_NDH", bien, giatri, thamso);
}
if (dt.Rows.Count > 0)
{
// FileStream fs = new FileStream(FileName, FileMode.Create);
// StreamWriter sw = new StreamWriter(fs, Encoding.Unicode);
//bll.ToCSV(dt, sw, true);
bll.ExportToExcel(dt, FileName);
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
bll.OpenExcel(FileName);
//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ó KU đến hạn", "Mess", MessageBoxButton.OK,
MessageBoxImage.Information);
}
}
}
// bll.WriteDataTableToExcel(dt, "Person Details", FileName, "Details");
}
catch (Exception ex)
{
MessageBox.Show("Error \n" + ex.Message, "Mess", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
cls.DongKetNoi();
}
}
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_Click(object sender, RoutedEventArgs e)
{
bll.TaoThuMuc(Thumuc);
var lastMonth = new DateTime(dtpNgay.SelectedDate.Value.Year, dtpNgay.SelectedDate.Value.Month, DateTime.DaysInMonth(dtpNgay.SelectedDate.Value.Year, dtpNgay.SelectedDate.Value.Month));
try
{
ServerInfor srv = new ServerInfor();
cls.ClsConnect();
const int thamso = 3;
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] = "@Mato";
if (Ration3.IsChecked == true)
{
giatri[1] = bll.Left(cboXa.SelectedValue.ToString().Trim(), 6);
}
else
{
giatri[1] = bll.Left(cboTo.SelectedValue.ToString().Trim(), 7);
}
bien[2] = "@Mau";
if (Ration1.IsChecked == true || Ration5.IsChecked == true) // Mau KT
{
giatri[2] = "1";
}
else //Mau Tat toan
{
giatri[2] = "0";
}
if (Ration4.IsChecked == true)
{
if (dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd") == lastMonth.ToString("yyyy-MM-dd"))
{
str = "select a.KU_MAPGD,left(a.KU_MADP,6) MAXA,c.TEN TENXA,a.KU_MATO,(select TO_TENTT from HSTO where TO_MATO=a.KU_MATO) TENTT,b.KH_MAKH,b.KH_TENKH,b.KH_TENVC "
+ " ,char(39) + a.KU_SOKU SOKU, a.KU_CHTRINH,a.KU_MAQD,(select GIATRI from DMKHAC where KHOA_1='07' and KHOA_2=a.KU_MAQD) TENCHTR, a.KU_NGAYVAY, a.KU_NGAYDHAN_1, a.KU_NGAYDHAN_3 "
+
" , a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH DUNO, a.KU_LAITHAN + a.KU_LAIQHAN LAIDATHU, a.KU_LAITONTHAN + a.KU_LAITONQHAN LAITON "
+
" , (select SV_TENSV from HSSV where SV_SOKU = a.KU_SOKU) TENSV,a.KU_MAPNKT51,(select GIATRI from DMKHAC where KHOA_1 = '25' and KHOA_2 = a.KU_MAPNKT51) PNKT1 "
+
" ,a.KU_MAPNKT52,(select GIATRI from DMKHAC where KHOA_1 = '25' and KHOA_2 = a.KU_MAPNKT52) PNKT2,b.KH_DIACHI "
+ " from HSKU a, HSKH b,DMXA c "
+
" where a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH>0 and a.KU_TTMONVAY<>'CLOSE' and a.KU_NGAYBC = '" +
giatri[0] + "' and a.KU_MATO = '" + giatri[1] +
"' and a.KU_MAKH = b.KH_MAKH and left(a.KU_MADP, 6)= c.MA order by b.KH_MAKH,a.KU_CHTRINH";
}
else
{
str = "select a.KU_MAPGD,left(a.KU_MADP,6) MAXA,c.TEN TENXA,a.KU_MATO,(select TO_TENTT from HSTO where TO_MATO=a.KU_MATO) TENTT,b.KH_MAKH,b.KH_TENKH, b.KH_TENVC "
+ " ,char(39) + a.KU_SOKU SOKU, a.KU_CHTRINH,a.KU_MAQD,(select GIATRI from DMKHAC where KHOA_1='07' and KHOA_2=a.KU_MAQD) TENCHTR, a.KU_NGAYVAY, a.KU_NGAYDHAN_1, a.KU_NGAYDHAN_3 "
+
" , a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH DUNO, a.KU_LAITHAN + a.KU_LAIQHAN LAIDATHU, a.KU_LAITONTHAN + a.KU_LAITONQHAN LAITON "
+
" , (select SV_TENSV from HSSV where SV_SOKU = a.KU_SOKU) TENSV,a.KU_MAPNKT51,(select GIATRI from DMKHAC where KHOA_1 = '25' and KHOA_2 = a.KU_MAPNKT51) PNKT1 "
+
" ,a.KU_MAPNKT52,(select GIATRI from DMKHAC where KHOA_1 = '25' and KHOA_2 = a.KU_MAPNKT52) PNKT2,b.KH_DIACHI "
+ " from HSCV_DAILY a, HSKH b,DMXA c "
+
" where a.KU_DNOTHAN + a.KU_DNOQHAN + a.KU_DNOKHOANH>0 and a.KU_TTMONVAY<>'CLOSE' and a.KU_NGAYBC = '" +
giatri[0] + "' and a.KU_MATO = '" + giatri[1] +
"' and a.KU_MAKH = b.KH_MAKH and left(a.KU_MADP, 6)= c.MA order by b.KH_MAKH,a.KU_CHTRINH";
}
dt = cls.LoadDataText(str);
FileName = Thumuc + "\\" + bll.Left(cboPos.SelectedValue.ToString().Trim(), 6) + "_SKETO_" + bll.Left(cboTo.SelectedValue.ToString().Trim(), 7) + "_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
bll.ExportToExcel(dt, FileName);
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
bll.OpenExcel(FileName);
}
else
{
dt = cls.LoadDataProcPara(Ration3.IsChecked != true ? "usp_sketo" : "usp_SkeBsungTT", bien, giatri, thamso);
//rpt_kt740_01 rpt = new rpt_kt740_01();
if (dt.Rows.Count > 0)
{
if (Ration1.IsChecked == true)
{
rpt_SkeTo rpt = new rpt_SkeTo();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(),
srv.DbPassSerVer());
}
else if (Ration2.IsChecked == true)
{
rpt_SkeTo1 rpt = new rpt_SkeTo1();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(),
srv.DbPassSerVer());
}
else if (Ration3.IsChecked == true)
{
rpt_SkeBsungTT rpt = new rpt_SkeBsungTT();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(),
srv.DbPassSerVer());
}
else if (Ration5.IsChecked == true)
{
rpt_Mau06_01 rpt = new rpt_Mau06_01();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(),
srv.DbPassSerVer());
}
}
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
{
str.TaoThuMuc(Thumuc);
if (dt == null || dt.Rows.Count == 0)
{
MessageBox.Show("Chưa có dữ liệu !", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Warning);
}
else
{
cls.ClsConnect();
if (upda == false)
{
foreach (DataRow dr in dt.Rows)
{
if (dtpNgay.SelectedDate != null)
{
string strluu =
"insert into KHAOSAT(MAU,NAM,NGAY,POS,CHTR,MAXA,COT1,COT2,COT3,COT4,COT5,COT6,COT7,COT8,COT9,COT10,COT11,COT12,COT13,COT14)" +
" Values('" + _mau + "','" + dtpNgay.SelectedDate.Value.ToString("yyyy") + "','" +
dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd") + "',N'" + dr["POS"] +
"','" + dr["CHTR"] + "',N'" + dr["MAXA"] + "'," + dr["COT1"] + ",N'" + dr["COT2"]
+ "'," + dr["COT3"] + "," + dr["COT4"] + "," + dr["COT5"] + "," + dr["COT6"] + "," + dr["COT7"]
+ "," + dr["COT8"] + "," + dr["COT9"] + "," + dr["COT10"] + "," + dr["COT11"]
+ "," + dr["COT12"] + "," + dr["COT13"] + "," + dr["COT14"] + ")";
cls.LoadDataText(strluu);
}
}
}
else
{
foreach (DataRow dr in dt.Rows)
{
if (dtpNgay.SelectedDate != null)
{
string strluu =
"update KHAOSAT set COT3=" + dr["COT3"] + "" +
",COT4=" + dr["COT4"] + ",COT5=" + dr["COT5"] + ",COT6=" + dr["COT6"] + "" +
",COT7=" + dr["COT7"] + ",COT8=" + dr["COT8"] + ",COT9=" + dr["COT9"] + "" +
",COT10=" + dr["COT10"] + ",COT11=" + dr["COT11"] + ",COT12=" + dr["COT12"] +
",COT13=" + dr["COT13"] + ",COT14 = " + dr["COT14"] +
" where MAU='" + _mau + "' and NAM=" + dtpNgay.SelectedDate.Value.ToString("yyyy") + " and MAXA='" + dr["MAXA"] + "'";
cls.LoadDataText(strluu);
}
}
}
if (upda == false)
{
MessageBox.Show("Đã lưu thành công ", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
else
{
MessageBox.Show("Cập nhật thành công ", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
dgvSource.ItemsSource = null;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
cls.DongKetNoi();
}
private void btnOk_Click(object sender, RoutedEventArgs e)
{
try
{
str.TaoThuMuc(Thumuc);
if (dt == null || dt.Rows.Count == 0)
{
MessageBox.Show("Chưa có dữ liệu !", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Warning);
}
else
{
cls.ClsConnect();
if (upda == false)
{
foreach (DataRow dr in dt.Rows)
{
if (dtpNgay.SelectedDate != null)
{
string strluu =
"insert into LUU_KS01(NAM,NGAY,TO_MATO,TT,ChiTieu,DV,CT01,CT19,CT09,CT10,CT11,CT06,CT02,CT04,CT03,CT15,CT18,CT07,GC)" +
" Values('" + dtpNgay.SelectedDate.Value.ToString("yyyy") + "','" +
dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd") + "','" + dr["TO_MATO"] +
"','" + dr["TT"] + "',N'" + dr["ChiTieu"] + "',N'" + dr["DV"] + "','" + dr["CT01"] +
"','" + dr["CT19"] + "','" + dr["CT09"] + "','" + dr["CT10"] + "','" + dr["CT11"] +
"','" + dr["CT06"] + "','" + dr["CT02"] + "','" + dr["CT04"] +
"','" + dr["CT03"] + "','" + dr["CT15"] + "','" + dr["CT18"] + "','" + dr["CT07"] +
"','" + dr["GC"] + "')";
cls.LoadDataText(strluu);
}
}
}
else
{
foreach (DataRow dr in dt.Rows)
{
if (dtpNgay.SelectedDate != null)
{
string strluu =
"update LUU_KS01 set CT01=" + dr["CT01"] +
",CT19=" + dr["CT19"] + ",CT09=" + dr["CT09"] + "" +
",CT10=" + dr["CT10"] + ",CT11=" + dr["CT11"] + ",CT06=" + dr["CT06"] + "" +
",CT02=" + dr["CT02"] + ",CT04=" + dr["CT04"] + ",CT03=" + dr["CT03"] + "" +
",CT15=" + dr["CT15"] + ",CT18=" + dr["CT18"] + ",CT07=" + dr["CT07"] +
" where TO_MATO='" + str.Left(CboMaTo.SelectedValue.ToString().Trim(), 7)
+ "' and NAM=" + dtpNgay.SelectedDate.Value.ToString("yyyy") + " and TT=" + dr["TT"];
cls.LoadDataText(strluu);
}
}
}
if (upda == false)
{
MessageBox.Show("Đã lưu thành công " + FileName, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
else
{
MessageBox.Show("Cập nhật thành công " + FileName, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
dgvSource.ItemsSource = null;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
cls.DongKetNoi();
}