private void btnOk_Click(object sender, RoutedEventArgs e)
{
dtNew = dt.Clone();
foreach (DataRow dr in dt.Rows)
{
if ((bool)dr[0] == true)
{
dtNew.ImportRow(dr);
}
}
if (dtNew == null || dtNew.Rows.Count == 0)
{
MessageBox.Show("Chưa chọn khách hàng nào ", "Mess");
}
else
{
if (Opt1.IsChecked == true)
{
rpt_SkePnkt rpt = new rpt_SkePnkt();
RPUtility.ShowRp(rpt, dtNew, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(),
srv.DbPassSerVer());
}
else
{
FileName = Thumuc + "\\" + bll.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_" + bll.Left(CboXa.SelectedValue.ToString().Trim(), 7) + "_PNKT_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
FileStream fs = new FileStream(FileName, FileMode.Create);
StreamWriter sw = new StreamWriter(fs, Encoding.Unicode);
//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);
}
}
}
private void btnOk_Click(object sender, RoutedEventArgs e)
{
cls.ClsConnect();
DataTable dt = new DataTable();
int thamso = 4;
string[] bien = new string[thamso];
object[] giatri = new object[thamso];
bien[0] = "@MaPos";
giatri[0] = str.Left(CboPos.SelectedValue.ToString().Trim(), 6);
bien[1] = "@Ngay";
if (dtpNgay.SelectedDate == null)
{
MessageBox.Show("Chưa chọn ngày", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
else
{
giatri[1] = dtpNgay.SelectedDate.Value.ToString("dd/MM/yyyy");
}
bien[2] = "@MaXa";
giatri[2] = str.Left(CboXa.SelectedValue.ToString().Trim(), 6);
bien[3] = "@Mau";
if (radioButton1.IsChecked == true)
{
giatri[3] = '1';
}
else
{
giatri[3] = '2';
}
dt = cls.LoadDataProcPara("usp_Pnkt", bien, giatri, thamso);
if (dt.Rows.Count > 0)
{
if (radioButton1.IsChecked == true)
{
FileName = Thumuc + "\\" + str.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_PNKT_" +
dtpNgay.SelectedDate.Value.ToString("ddMMyyyy_") + str.Right(CboXa.SelectedValue.ToString().Trim(), 2) + ".csv";
}
else
{
FileName = Thumuc + "\\" + str.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_CHTRINH_" +
dtpNgay.SelectedDate.Value.ToString("ddMMyyyy_") + str.Right(CboXa.SelectedValue.ToString().Trim(), 2) + ".csv";
}
FileStream fs = new FileStream(FileName, FileMode.Create);
StreamWriter sw = new StreamWriter(fs, Encoding.Unicode);
//bll.WriteDataTableToExcel(dt, "Details", FileName, "tutm : 0985165777");
str.ToCSV(dt, sw, true);
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
else
{
MessageBox.Show("Chưa có số liệu", "Thông báo");
}
cls.DongKetNoi();
}
private void btnOk_Click(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] = str.Left(CboPos.SelectedValue.ToString().Trim(), 6);
bien[1] = "@Ngay";
if (dtpNgay.SelectedDate == null)
{
MessageBox.Show("Chưa chọn ngày", "Thông báo", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
else
{
giatri[1] = dtpNgay.SelectedDate.Value.ToString("yyyy-MM-dd");
}
bien[2] = "@MaXa";
giatri[2] = str.Left(CboXa.SelectedValue.ToString().Trim(), 6);
//----------------------------------
dt = cls.LoadDataProcPara("usp_SkeNqh", bien, giatri, thamso);
if (dt.Rows.Count > 0)
{
FileName = Thumuc + "\\" + str.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_" + str.Left(CboXa.SelectedValue.ToString().Trim(), 6) + "_SKE_NQH_" + 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);
MessageBox.Show("OK đã xuất file Excel " + FileName, "Mess", MessageBoxButton.OK,
MessageBoxImage.Information);
//str.ExportToExcel(dt, FileName);
//bll.ExportDTToExcel(dt,FileName);
//bll.WriteDataTableToExcel(dt, "Details", FileName, "tutm : 0985165777");
}
else
{
MessageBox.Show(str.Left(CboXa.SelectedValue.ToString().Trim(), 6) + " Không có nợ quá hạn", "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)
{
#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)
{
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)
{
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)
{
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 lblOk_MouseDown(object sender, MouseButtonEventArgs e)
{
try
{
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
{
if (Opt1.IsChecked == true || Opt3.IsChecked == true)
{
rpt_DongCasa rpt = new rpt_DongCasa();
RPUtility.ShowRp(rpt, dtNew, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
if (lblGmt.IsChecked == true)
{
rpt_RoiTo rpt1 = new rpt_RoiTo();
RPUtility.ShowRp(rpt1, dtNew, this, srv.DbSourceSerVer(), srv.DbNameSerVer(), srv.DbUserSerVer(), srv.DbPassSerVer());
}
//dt = null;
//dtNew = null;
//dgvData.ItemsSource = null;
/*
* string mau = "";
* if (Opt3.IsChecked == true)
* {
* mau = "1";
* }
* else
* {
* mau = "0";
* }
* //dgvNew.ItemsSource = dtNew.DefaultView;
*
* if (Opt1.IsChecked == true || Opt2.IsChecked == true)
* {
* rpt_DongCasa rpt = new rpt_DongCasa();
* //RPUtility.ShowRp(rpt, dtNew, this, srv.DbSourceLocal(), srv.DbNameLocal(), srv.DbUserLocal(), srv.DbPassLocal());
* RPUtility.ShowRpOnePara(rpt, dtNew, mau, this, srv.DbSourceLocal(), srv.DbNameLocal(),
* srv.DbUserLocal(), srv.DbPassLocal());
* }
* else
* {
* rpt_DongCasa rpt = new rpt_DongCasa();
* //RPUtility.ShowRp(rpt, dtNew, this, srv.DbSourceLocal(), srv.DbNameLocal(), srv.DbUserLocal(), srv.DbPassLocal());
* RPUtility.ShowRpOnePara(rpt, dtNew, mau, this, srv.DbSourceLocal(), srv.DbNameLocal(),
* srv.DbUserLocal(), srv.DbPassLocal());
* }
*/
}
else
{
FileName = Thumuc + "\\" + str.Left(CboPos.SelectedValue.ToString().Trim(), 6) + "_" + str.Left(CboTo.SelectedValue.ToString().Trim(), 7) + "_DONG105_" + dtpNgay.SelectedDate.Value.ToString("ddMMyyyy") + ".csv";
FileStream fs = new FileStream(FileName, FileMode.Create);
StreamWriter sw = new StreamWriter(fs, Encoding.Unicode);
//bll.WriteDataTableToExcel(dt, "Details", FileName, "tutm : 0985165777");
str.ToCSV(dtNew, sw, true);
MessageBox.Show("Copy Excel to : " + FileName, "Thông báo", MessageBoxButton.OK, MessageBoxImage.Information);
}
//dtNew = dt.Clone();
//dtNew = null;
//dt = null;
// dgvData.ItemsSource = null;
// ChkAll.IsChecked = false;
}
}
catch (Exception ex)
{
MessageBox.Show("Bấm nút lấy dữ liệu !" + ex.Message, "Lỗi", MessageBoxButton.OK, MessageBoxImage.Error);
}
}