private void btnOk_Click(object sender, RoutedEventArgs e)
{
try
{
cls.ClsConnect();
const 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] = "@Maxa";
giatri[1] = bll.Left(CboXa.SelectedValue.ToString(), 6);
bien[2] = "@TuNgay";
if (dtpTuNgay.SelectedDate != null)
{
giatri[2] = dtpTuNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
bien[3] = "@DenNgay";
if (dtpDenNgay.SelectedDate != null)
{
giatri[3] = dtpDenNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
}
}
}
dt = cls.LoadDataProcPara("usp_SkeSvRaTruong", bien, giatri, thamso);
if (dt.Rows.Count > 0)
{
FileName = Thumuc + "\\" + giatri[0] + "_" + giatri[1] + "_Sao kê sinh viên ra trường_" + 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);
//rpt_DinhSv rpt = new rpt_DinhSv();
//RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
}
else
{
MessageBox.Show("Không có bản ghi nào ", "Mess");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
cls.DongKetNoi();
}
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 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)
{
// MessageBox.Show(BienBll.Ndma);
ServerInfor srv = new ServerInfor();
_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] = _str.Left(cboPos.SelectedValue.ToString().Trim(), 6);
_dt = _cls.LoadDataProcPara("usp_NVBQIN", bien, giatri, thamso);
if (_dt.Rows.Count > 0)
{
rpt_NVBQ rpt = new rpt_NVBQ();
RPUtility.ShowRp(rpt, _dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
FileName = Thumuc + "\\" + giatri[1] + "_SODU_NGUONVON_BQ_" + 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("Chưa có số liệu", "Thông báo");
}
_cls.DongKetNoi();
}
private void OK_OnClick(object sender, RoutedEventArgs e)
{
try
{ cls.ClsConnect();
int thamso = 3;
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");
dt = cls.LoadDataProcPara("usp_SkeDnKhoanh", bien, giatri, thamso);
if (dt.Rows.Count > 0)
{
if (RadioButton1.IsChecked == true)
{
rpt_SkeKhoanh rpt = new rpt_SkeKhoanh();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
}
else
{
FileName = Thumuc + "\\" + bll.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_" +
DtpDenNgay.SelectedDate.Value.ToString("ddMMyyyy") + "_Ske_Khoanh.csv";
/*bll.WriteDataTableToExcel(dt, "Person Details", FileName, "Details");
* bll.ExportToExcel(dt, FileName);
* MessageBox.Show("OK đã xuất file Excel " + FileName, "Mess", MessageBoxButton.OK, MessageBoxImage.Information);
* bll.OpenExcel(FileName);
*/
//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("Export to Excel : " + FileName, "Thông báo");
bll.OpenExcel(FileName);
}
}
else
{
MessageBox.Show("Không có dữ liệu", "Mess", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error \n" + ex.Message, "Mess", MessageBoxButton.OK, MessageBoxImage.Error);
}
finally
{
cls.DongKetNoi();
}
}
private void Lblin01kt_OnMouseDown(object sender, MouseButtonEventArgs e)
{
try
{
cls.ClsConnect();
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] = "@TuNgay";
if (dtpTuNgay.SelectedDate != null)
{
giatri[1] = dtpTuNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
bien[2] = "@DenNgay";
if (dtpDenNgay.SelectedDate != null)
{
giatri[2] = dtpDenNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
}
}
}
var dtin = cls.LoadDataProcPara("usp_ChovayNangmuc04", bien, giatri, thamso);
if (dtin.Rows.Count > 0)
{
rpt_ChovayNangmuc01a rpt = new rpt_ChovayNangmuc01a();
RPUtility.ShowRp(rpt, dtin, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
FileName = Thumuc + "\\" + str.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_Mẫu 01_KT " + dtpTuNgay.SelectedDate.Value.ToString("ddMMyyyy") + "_Đến ngày_" + dtpDenNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
//MessageBox.Show(FileName);
str.ExportToExcel(dtin, FileName);
//str.WriteDataTableToExcel(dtin,"M01",FileName,"true");
////FileStream fs = new FileStream(FileName, FileMode.Create);
////StreamWriter sw = new StreamWriter(fs, Encoding.Unicode);
////str.ToCSV(dtin, sw, true);
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
str.OpenExcel(FileName);
}
else
{
MessageBox.Show("Không có món vay nào !", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
}
catch (Exception ex)
{
MessageBox.Show("Lỗi : " + ex.Message, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Error);
}
cls.DongKetNoi();
}
private void lblThKe_MouseDown(object sender, MouseButtonEventArgs e)
{
try
{
cls.ClsConnect();
var dtthke = cls.LoadDataText("select b.ND_MADV N'Mã POS',c.PO_TEN N'Tên POS',a.USERNAME,b.ND_TEN N'Tên CB',a.VONG N'Số vòng thi'"
+ ", COUNT(TT) N'Số câu hỏi', count( case when a.TRALOI is not null then a.TRALOI end) N'Số câu trả lời'"
+ ", count(case when a.TRALOI = a.DAPAN then a.DAPAN end) N'Số câu đúng', N'Câu đúng chỉ mang tính chất tham khảo' N'Ghi chú' "
+ " from KQTHINV a, NG_DUNG b, DMPOS c where a.USERNAME = b.ND_MA and b.ND_MADV = c.PO_MA "
+ "group by a.USERNAME, a.VONG, b.ND_MADV, ND_TEN, c.PO_TEN order by b.ND_MADV, a.USERNAME, a.VONG");
string _path = Thumuc + "\\" + "thongke.csv";
str.ExportToExcel(dtthke, _path);
MessageBox.Show("Copy Excel to : " + _path, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
str.OpenExcel(_path);
}
catch (Exception ex)
{
MessageBox.Show("Lổi : " + ex.Message, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Error);
}
cls.DongKetNoi();
}
private void BtnBaoCao_OnClick(object sender, RoutedEventArgs e)
{
try
{
_cls.ClsConnect();
int thamso = 3;
string[] bien = new string[thamso];
object[] giatri = new object[thamso];
bien[0] = "@MaPos";
giatri[0] = RadCboPos.SelectedValue;
bien[1] = "@Ngay"; giatri[1] = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
bien[2] = "@TH";
if (RadCboPos.SelectedValue.ToString() == "003000")
{
giatri[2] = "1";
}
else
{
giatri[2] = "0";
}
var dtin = _cls.LoadDataProcPara("usp_TT_BaoCao", bien, giatri, thamso);
if (dtin.Rows.Count > 0)
{
rpt_TuyenTruyen01 rpt = new rpt_TuyenTruyen01(); RPUtility.ShowRp(rpt, dtin, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
FileName = Thumuc + "\\" + giatri[0] + "_Tuyen Truyen_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
bll.ExportToExcel(dtin, FileName);
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
bll.OpenExcel(FileName);
}
else
{
MessageBox.Show("Không có tin nào ! : ", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
catch (Exception ex)
{
MessageBox.Show("Lỗi : " + ex.Message, "Thông báo", 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 btnOK_Click(object sender, RoutedEventArgs e)
{
ClsServer cls = new ClsServer();
try
{
bll.TaoThuMuc(Thumuc);
string pos = bll.Right(bll.Left(CboPos.SelectedValue.ToString().Trim(), 6), 4);
cls.ClsConnect();
string strsql = "with lst1 as ( select MA, TEN from DMTHON where LEFT(MA,4)= '" + pos + "' and TRANGTHAI = 'A' ), lst2 as ( "
+ " select a.CT,a.NAM,LEFT(a.DP, 4) POS,LEFT(a.DP, 6) Xa, a.DP THON,(select ten from dmthon where a.dp = MA) TENTHON,COUNT(a.HOTEN) DEM "
+ " from DANHSACH a where LEFT(DP,4)= '" + pos + "' and CT = '" + bll.Left(CboChTr.SelectedValue.ToString(), 2) + "' and NAM = '" + comboBoxYear.SelectedValue + "' "
+ " group by LEFT(a.DP, 4),LEFT(a.DP, 6), a.DP,a.CT,a.NAM ) "
+ " select LEFT(a.MA, 4) POS,(select po_ten from dmpos where LEFT(a.MA, 4) = right(po_ma, 4)) TENPOS "
+ " ,left(a.MA, 6) N'Xã' ,(select TEN from dmxa where MA = left(a.MA, 6)) N'Tên Xã',a.MA N'Thôn',a.TEN N'Tên Thôn','" + comboBoxYear.SelectedValue + "' NAM,'" + bll.Left(CboChTr.SelectedValue.ToString(), 2) + "' CT "
+ " ,(select GIATRI from dmkhac where khoa_1 = '07' and MOTA = b.CT) N'Tên CHTR',isnull(b.DEM, 0) N'Số khẩu' "
+ " from lst1 a left join lst2 b on a.MA = b.THON order by a.MA";
dt = cls.LoadDataText(strsql);
FileName = Thumuc + "\\" + pos + "_" + comboBoxYear.SelectedValue + "_" + bll.Left(CboChTr.SelectedValue.ToString(), 2) + "_Danh sách" + ".csv";
if (dt.Rows.Count > 0)
{
bll.ExportToExcel(dt, FileName);
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
bll.OpenExcel(FileName);
}
else
{
MessageBox.Show("Không có số liệu", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Warning);
}
//MessageBox.Show(strsql, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
catch (Exception ex)
{
MessageBox.Show("Error + " + ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
cls.DongKetNoi();
}
private void LblCheck_OnMouseDown(object sender, MouseButtonEventArgs e)
{
try
{
ClsServer cls = new ClsServer();
cls.ClsConnect();
string nam = comboBoxYear.SelectedValue.ToString().Trim();
string pos = bll.Right(bll.Left(CboPos.SelectedValue.ToString(), 6), 4);
string chtr = bll.Left(CboChTr.SelectedValue.ToString(), 2);
FileName = thumuc + "\\" + nam + "_" + pos + "_" + chtr + "_" + DateTime.Now.ToString("ddMMyyyy") + ".csv";
string chk = "with lst1 as ( select b.MA MAXA, b.TEN TENXA, a.MA MATHON, a.TEN TENTHON from DMTHON a, DMXA b where a.XA = b.MA and left(a.MA, 4) ='" + pos + "'"
+ " ), lst2 as (select distinct a.CT,a.NAM,a.DP from DANHSACH a, DMTHON b where a.NAM = '" + nam + "' and a.CT = '" + chtr + "' and left(a.DP, 4)= '" + pos + "'"
+ ") select " + chtr + " N'Chương Trình'," + nam + " N'Năm', a.MAXA N'Mã Xã',a.TENXA N'Tên Xã',a.MATHON N'Mã Thôn',a.TENTHON N'Tên Thôn',(select 'X' from lst2 where DP = a.MATHON) N'Đã nhập' from lst1 a order by a.MATHON";
var dt = cls.LoadDataText(chk);
bll.ExportToExcel(dt, FileName);
bll.OpenExcel(FileName);
cls.DongKetNoi();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
private void 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)
{
#region
try
{
cls.ClsConnect();
int thamso = 6;
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[5] = "@DenNgay";
if (dtpDenNgay.SelectedDate != null)
{
giatri[5] = dtpDenNgay.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);
bien[3] = "@Htvay";
if (Ration1.IsChecked == true)
{
giatri[3] = "1";
}
else if (Ration2.IsChecked == true)
{
giatri[3] = "2";
}
else
{
giatri[3] = "3";
}
bien[4] = "@Nguon";
if (Ration4.IsChecked == true)
{
giatri[4] = "1";
}
else if (Ration5.IsChecked == true)
{
giatri[4] = "2";
}
else
{
giatri[4] = "3";
}
dt = cls.LoadLdbf("usp_NdhCT", bien, giatri, thamso);
if (dt.Rows.Count > 0)
{
if (Ration7.IsChecked == true)
{
if (Ration6.IsChecked == true)
{
rpt_NdhCtNewKHB rpt = new rpt_NdhCtNewKHB();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(),
srv.DbUserSerVer(), srv.DbPassSerVer());
}
else
{
rpt_NdhCtNew rpt = new rpt_NdhCtNew();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(),
srv.DbUserSerVer(), srv.DbPassSerVer());
}
}
else
{
FileName = Thumuc + "\\" + giatri[1] + "_" + giatri[2] + "_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
FileStream fs = new FileStream(FileName, FileMode.Create);
StreamWriter sw = new StreamWriter(fs, Encoding.Unicode);
str.ToCSV(dt, sw, true);
//str.exportDataTableToExcel(dt, FileName);
// //str.ExportToExcel(dt, FileName);
//MessageBox.Show("Export to Excel : " + FileName, "Thông báo");
// str.OpenExcel(FileName);
//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
}
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)
{
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 Excel_OnMouseDown(object sender, MouseButtonEventArgs e)
{
try
{
string ng = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
string pos = CboPos.SelectedValue.ToString().Trim();
cls.ClsConnect();
if (pos.Trim() == "003000")
{
str = "select '" + ng +
"' NGAY,a.POS_CD,(case when a.MA_NHANHIEU_TS in ('MM11','MM12') then N'Máy Chủ'"
+ " when a.MA_NHANHIEU_TS = 'TI11' then N'Máy tính để bàn (PC)'"
+ " when a.MA_NHANHIEU_TS = 'TI12' then N'Máy tính xách tay'"
+ " when a.MA_NHANHIEU_TS = 'TI13' then N'Máy in'"
+ " when a.MA_NHANHIEU_TS = 'TI19' then N'Thiết bị khác'"
+
" end ) LOAITB,a.MA_TS,a.TEN_TS,a.SO_LUONG,a.NGUYEN_GIA,NGAY_MUA,MOTA CAUHINH, isnull(MAPHONG,'') MAPHONG,isnull(TENPHONG, '') TENPHONG,CB_QUANLY,a.TRANGTHAI"
+ " ,(case when a.TRANGTHAI = 'C' then 'X' else '' end) THANHLY"
+ " ,(case when DATEPART(YEAR, a.NGAY_MUA)= DATEPART(YEAR, '" + ng +
"') then 'X' else '' end) MUAMOI"
+
" ,DATEPART(YEAR, a.NGAY_MUA) NAM from LUU_TSCC a where LOAI_TS_CHITIET in ('MM1','TI1') and MA_NHANHIEU_TS in ('MM11','MM12','TI11','TI12','TI13','TI19')"
+ " order by a.MA_NHANHIEU_TS,a.POS_CD,a.MAPHONG,a.NGAY_MUA";
str1 = "select '" + ng +
"' NGAY,a.POS_CD,(case when a.MA_NHANHIEU_TS in ('MM11','MM12') then N'Máy Chủ'"
+ " when a.MA_NHANHIEU_TS = 'TI11' then N'Máy tính để bàn (PC)'"
+ " when a.MA_NHANHIEU_TS = 'TI12' then N'Máy tính xách tay'"
+ " when a.MA_NHANHIEU_TS = 'TI13' then N'Máy in'"
+ " when a.MA_NHANHIEU_TS = 'TI19' then N'Thiết bị khác'"
+
" end ) LOAITB,a.MA_TS,a.TEN_TS,a.SO_LUONG,a.NGUYEN_GIA,NGAY_MUA,MOTA CAUHINH, isnull(MAPHONG,'') MAPHONG,isnull(TENPHONG, '') TENPHONG,CB_QUANLY,a.TRANGTHAI"
+ " ,(case when a.TRANGTHAI = 'C' then 'X' else '' end) THANHLY"
+ " ,(case when DATEPART(YEAR, a.NGAY_MUA)= DATEPART(YEAR, '" + ng +
"') then 'X' else '' end) MUAMOI"
+
" ,DATEPART(YEAR, a.NGAY_MUA) NAM from LUU_TSCC a where a.TRANGTHAI<>'C' and LOAI_TS_CHITIET in ('MM1','TI1') and MA_NHANHIEU_TS in ('MM11','MM12','TI11','TI12','TI13','TI19')"
+ " order by a.MA_NHANHIEU_TS,a.POS_CD,a.MAPHONG,a.NGAY_MUA";
str2 = "select '" + ng +
"' NGAY,a.POS_CD,(case when a.MA_NHANHIEU_TS in ('MM11','MM12') then N'Máy Chủ'"
+ " when a.MA_NHANHIEU_TS = 'TI11' then N'Máy tính để bàn (PC)'"
+ " when a.MA_NHANHIEU_TS = 'TI12' then N'Máy tính xách tay'"
+ " when a.MA_NHANHIEU_TS = 'TI13' then N'Máy in'"
+ " when a.MA_NHANHIEU_TS = 'TI19' then N'Thiết bị khác'"
+
" end ) LOAITB,a.MA_TS,a.TEN_TS,a.SO_LUONG,a.NGUYEN_GIA,NGAY_MUA,MOTA CAUHINH, isnull(MAPHONG,'') MAPHONG,isnull(TENPHONG, '') TENPHONG,CB_QUANLY,a.TRANGTHAI"
+ " ,(case when a.TRANGTHAI = 'C' then 'X' else '' end) THANHLY"
+ " ,(case when DATEPART(YEAR, a.NGAY_MUA)= DATEPART(YEAR, '" + ng +
"') then 'X' else '' end) MUAMOI"
+
" ,DATEPART(YEAR, a.NGAY_MUA) NAM from LUU_TSCC a where a.TRANGTHAI<>'C' and LOAI_TS_CHITIET in ('MM1','TI1') and MA_NHANHIEU_TS in ('MM11','MM12','TI11','TI12','TI13','TI19')"
+ " and DATEPART(YEAR, a.NGAY_MUA)= DATEPART(YEAR, '" + ng + "') order by a.MA_NHANHIEU_TS,a.POS_CD,a.MAPHONG,a.NGAY_MUA";
}
else // theo pos
{
str = "select '" + ng +
"' NGAY,a.POS_CD,(case when a.MA_NHANHIEU_TS in ('MM11','MM12') then N'Máy Chủ'"
+ " when a.MA_NHANHIEU_TS = 'TI11' then N'Máy tính để bàn (PC)'"
+ " when a.MA_NHANHIEU_TS = 'TI12' then N'Máy tính xách tay'"
+ " when a.MA_NHANHIEU_TS = 'TI13' then N'Máy in'"
+ " when a.MA_NHANHIEU_TS = 'TI19' then N'Thiết bị khác'"
+
" end ) LOAITB,a.MA_TS,a.TEN_TS,a.SO_LUONG,a.NGUYEN_GIA,NGAY_MUA,MOTA CAUHINH, isnull(MAPHONG,'') MAPHONG,isnull(TENPHONG, '') TENPHONG,CB_QUANLY,a.TRANGTHAI"
+ " ,(case when a.TRANGTHAI = 'C' then 'X' else '' end) THANHLY"
+ " ,(case when DATEPART(YEAR, a.NGAY_MUA)= DATEPART(YEAR, '" + ng +
"') then 'X' else '' end) MUAMOI"
+
" ,DATEPART(YEAR, a.NGAY_MUA) NAM from LUU_TSCC a where a.POS_CD='" + pos + "' and LOAI_TS_CHITIET in ('MM1','TI1') and MA_NHANHIEU_TS in ('MM11','MM12','TI11','TI12','TI13','TI19')"
+ " order by a.MA_NHANHIEU_TS,a.POS_CD,a.MAPHONG,a.NGAY_MUA";
str1 = "select '" + ng +
"' NGAY,a.POS_CD,(case when a.MA_NHANHIEU_TS in ('MM11','MM12') then N'Máy Chủ'"
+ " when a.MA_NHANHIEU_TS = 'TI11' then N'Máy tính để bàn (PC)'"
+ " when a.MA_NHANHIEU_TS = 'TI12' then N'Máy tính xách tay'"
+ " when a.MA_NHANHIEU_TS = 'TI13' then N'Máy in'"
+ " when a.MA_NHANHIEU_TS = 'TI19' then N'Thiết bị khác'"
+
" end ) LOAITB,a.MA_TS,a.TEN_TS,a.SO_LUONG,a.NGUYEN_GIA,NGAY_MUA,MOTA CAUHINH, isnull(MAPHONG,'') MAPHONG,isnull(TENPHONG, '') TENPHONG,CB_QUANLY,a.TRANGTHAI"
+ " ,(case when a.TRANGTHAI = 'C' then 'X' else '' end) THANHLY"
+ " ,(case when DATEPART(YEAR, a.NGAY_MUA)= DATEPART(YEAR, '" + ng +
"') then 'X' else '' end) MUAMOI"
+
" ,DATEPART(YEAR, a.NGAY_MUA) NAM from LUU_TSCC a where a.POS_CD='" + pos + "' and a.TRANGTHAI<>'C' and LOAI_TS_CHITIET in ('MM1','TI1') and MA_NHANHIEU_TS in ('MM11','MM12','TI11','TI12','TI13','TI19')"
+ " order by a.MA_NHANHIEU_TS,a.POS_CD,a.MAPHONG,a.NGAY_MUA";
str2 = "select '" + ng +
"' NGAY,a.POS_CD,(case when a.MA_NHANHIEU_TS in ('MM11','MM12') then N'Máy Chủ'"
+ " when a.MA_NHANHIEU_TS = 'TI11' then N'Máy tính để bàn (PC)'"
+ " when a.MA_NHANHIEU_TS = 'TI12' then N'Máy tính xách tay'"
+ " when a.MA_NHANHIEU_TS = 'TI13' then N'Máy in'"
+ " when a.MA_NHANHIEU_TS = 'TI19' then N'Thiết bị khác'"
+
" end ) LOAITB,a.MA_TS,a.TEN_TS,a.SO_LUONG,a.NGUYEN_GIA,NGAY_MUA,MOTA CAUHINH, isnull(MAPHONG,'') MAPHONG,isnull(TENPHONG, '') TENPHONG,CB_QUANLY,a.TRANGTHAI"
+ " ,(case when a.TRANGTHAI = 'C' then 'X' else '' end) THANHLY"
+ " ,(case when DATEPART(YEAR, a.NGAY_MUA)= DATEPART(YEAR, '" + ng +
"') then 'X' else '' end) MUAMOI"
+
" ,DATEPART(YEAR, a.NGAY_MUA) NAM from LUU_TSCC a where a.POS_CD='" + pos + "' and a.TRANGTHAI<>'C' and LOAI_TS_CHITIET in ('MM1','TI1') and MA_NHANHIEU_TS in ('MM11','MM12','TI11','TI12','TI13','TI19')"
+ " and DATEPART(YEAR, a.NGAY_MUA)= DATEPART(YEAR, '" + ng + "') order by a.MA_NHANHIEU_TS,a.POS_CD,a.MAPHONG,a.NGAY_MUA";
}
//MessageBox.Show(str);
dt = cls.LoadDataText(str);
var dt1 = cls.LoadDataText(str1);
var dt2 = cls.LoadDataText(str2);
if (dt.Rows.Count > 0)
{
FileName = Thumuc + "\\" + pos + "_" + "Sao kê thiết bị" + ng + "_" + ".xlsx";
string FileName1 = Thumuc + "\\" + pos + "_" + "Sao kê thiết bị đang dùng " + ng + "_" + ".xlsx";
string FileName2 = Thumuc + "\\" + pos + "_" + "Sao kê thiết bị mua mới trong năm " + ng + "_" + ".xlsx";
s.ExportToExcel(dt, FileName);
s.ExportToExcel(dt1, FileName1);
s.ExportToExcel(dt2, FileName2);
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
s.OpenExcel(FileName);
s.OpenExcel(FileName1);
s.OpenExcel(FileName2);
}
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 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)
{
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)
{
cls.ClsConnect();
try
{
int thamso = 5;
string[] bien = new string[thamso];
object[] giatri = new object[thamso];
bien[0] = "@Mato";
if (CboTo != null)
{
giatri[0] = bll.Left(CboTo.SelectedValue.ToString().Trim(), 7);
}
else
{
MessageBox.Show("Chọn Tổ", "Mess");
return;
}
bien[1] = "@Ngay";
if (dtpNgay.SelectedDate != null)
{
giatri[1] = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
}
else
{
MessageBox.Show("Chọn Ngày", "Mess");
return;
}
bien[2] = "@MaPos";
giatri[2] = bll.Left(CboPos.SelectedValue.ToString().Trim(), 6);
bien[3] = "@Mau";
if (Ration1.IsChecked == true)
{
giatri[3] = '1';
FileName = Thumuc + "\\" + bll.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_LAITHANG_XA_" + bll.Left(CboTo.SelectedValue.ToString().Trim(), 7) + "_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
}
else
{
giatri[3] = '2';
FileName = Thumuc + "\\" + bll.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_LAITHANG_CHTR_" + bll.Left(CboTo.SelectedValue.ToString().Trim(), 7) + "_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
}
bien[4] = "@MaXa";
giatri[4] = bll.Left(CboXa.SelectedValue.ToString().Trim(), 6);
dt = cls.LoadDataProcPara("usp_LAIDT", bien, giatri, thamso);
if (dt.Rows.Count > 0)
{
if (Ration1.IsChecked == true)
{
rpt_LAIDT rpt = new rpt_LAIDT();
RPUtility.ShowRp(rpt, dt, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
}
else
{
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ó bản ghi nào ", "Mess");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
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 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
}
