public static DataTable layLoaiDaiLy()
{
SqlCommand cmd = new SqlCommand(
@"SELECT DISTINCT AGTYPE01 as 'value'
FROM [DevDB].[dbo].[AGNTPF$]
",
Helper_DAO.createAndOpenConnection());
return(Helper_DAO.getTable(cmd));
}
public static DataTable layDanhSachChiNhanhh()
{
SqlCommand cmd = new SqlCommand(
@"SELECT [id] as 'value'
,[name]
, [name] + '['+cast([id] as varchar)+']' as 'label'
FROM [DevDB].[dbo].[PQDONVI$]",
Helper_DAO.createAndOpenConnection());
return(Helper_DAO.getTable(cmd));
}
public static DataTable layMaDaiLy(string NoiDung)
{
SqlCommand cmd = new SqlCommand(
@"SELECT TOP 10 [AGNTNUM] as 'value'
FROM [DevDB].[dbo].[AGNTPF$]
WHERE ([AGNTNUM] LIKE @NoiDung)",
Helper_DAO.createAndOpenConnection());
cmd.Parameters.Add("NoiDung", SqlDbType.NVarChar, 255);
cmd.Parameters["NoiDung"].Value = '%' + NoiDung.Trim() + '%';
return(Helper_DAO.getTable(cmd));
}
public static DataTable getIncomeReport()
{
string sql = $@"
SELECT DISTINCT
dbo.ZTRNPF$.CNTBRANCH AS 'MÃ CHI NHÁNH',
dbo.ZTRNPF$.BATCACTYR AS 'NĂM TÀI CHÍNH',
dbo.DESCPF$.LONGDESC AS 'TÊN PHÒNG BAN',
dbo.CHEXPF$.ZDEPTCD AS 'MÃ PHÒNG BAN',
dbo.ZTRNPF$.RLDGACCT AS 'MÃ ĐƠN',
dbo.CLNTPF$.LSURNAME AS 'HỌ VÀ TÊN LÓT',
dbo.CLNTPF$.LGIVNAME AS 'TÊN KHÁCH HÀNG',
dbo.ZTRNPF$.ACCNUM AS 'MÃ ĐẠI LÝ',
dbo.AGNTPF$.AGTYPE01 AS 'LOẠI ĐẠI LÝ',
dbo.ZTRNPF$.ORIGCURR AS 'ĐƠN VỊ TIỀN TỆ',
SUM(dbo.ZTRNPF$.TRANAMT01) AS 'PHÍ GROSS',
SUM(dbo.ZTRNPF$.TRANAMT03) AS 'GIẢM PHÍ',
SUM(dbo.ZTRNPF$.TRANAMT04) AS 'HOA HỒNG ĐẠI LÝ',
SUM(dbo.ZTRNPF$.TRANAMT06) AS 'PHÍ NET',
SUM(dbo.ZTRNPF$.TRANAMT07) AS 'THUẾ VAT',
SUM(dbo.ZTRNPF$.TRANAMT01 - dbo.ZTRNPF$.TRANAMT03 +dbo.ZTRNPF$.TRANAMT07) AS 'PHÍ KHÁCH HÀNG PHẢI TRẢ',
dbo.ZTRNPF$.CCDATE AS 'NGÀY HIỆU LỰC ĐƠN',
dbo.CHDRPF$.PAYPLAN AS 'MÃ KỲ PHÍ'
FROM dbo.AGNTPF$,dbo.CLNTPF$,dbo.DESCPF$, dbo.ZTRNPF$
INNER JOIN dbo.PREMPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.PREMPF$.CHDRNO
INNER JOIN dbo.RISKPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.RISKPF$.CHDRNO
INNER JOIN dbo.CHDRPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.CHDRPF$.CHDRNUM
INNER JOIN dbo.CHEXPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.CHEXPF$.CHDRNUM
WHERE
dbo.AGNTPF$.AGNTNUM = dbo.ZTRNPF$.ACCNUM
AND dbo.CLNTPF$.CLNTNUM= dbo.CHDRPF$.COWNNUM
AND dbo.DESCPF$.DESCITEM= dbo.CHEXPF$.ZDEPTCD
AND dbo.CHDRPF$.TRANNO = dbo.ZTRNPF$.TRANNO
GROUP BY
dbo.ZTRNPF$.CNTBRANCH ,
dbo.ZTRNPF$.BATCACTYR ,
dbo.DESCPF$.LONGDESC,
dbo.CHEXPF$.ZDEPTCD ,
dbo.ZTRNPF$.RLDGACCT ,
dbo.CLNTPF$.LSURNAME ,
dbo.CLNTPF$.LGIVNAME ,
dbo.ZTRNPF$.ACCNUM ,
dbo.AGNTPF$.AGTYPE01 ,
dbo.ZTRNPF$.ORIGCURR ,
dbo.ZTRNPF$.CCDATE ,
dbo.CHDRPF$.PAYPLAN
ORDER BY dbo.ZTRNPF$.RLDGACCT";
SqlCommand cmd = new SqlCommand(sql, Helper_DAO.createAndOpenConnection());
return(Helper_DAO.getTable(cmd));
}
public static DataTable layDanhSachPhongBanTheoTenHoacMa(string NoiDung)
{
SqlCommand cmd = new SqlCommand(
@"SELECT TOP 10 [DESCITEM] as 'value'
,[LONGDESC] as 'name'
, [LONGDESC] +' ['+[DESCITEM]+']' as 'label'
FROM [DevDB].[dbo].[DESCPF$]
WHERE [DESCITEM] <> '' AND ([DESCITEM] LIKE @NoiDung OR [LONGDESC] LIKE @NoiDung)",
Helper_DAO.createAndOpenConnection());
cmd.Parameters.Add("NoiDung", SqlDbType.NVarChar, 255);
cmd.Parameters["NoiDung"].Value = '%' + NoiDung.Trim() + '%';
return(Helper_DAO.getTable(cmd));
}
public static DataTable layHoTenKhachHang(string NoiDung)
{
SqlCommand cmd = new SqlCommand(
@"SELECT TOP 10 dbo.CLNTPF$.LSURNAME AS 'surName',
dbo.CLNTPF$.LGIVNAME AS 'givName',
LSURNAME + ' ' + LGIVNAME AS 'label'
FROM [DevDB].[dbo].[CLNTPF$]
WHERE ([LSURNAME] LIKE @NoiDung OR [LGIVNAME] LIKE @NoiDung)
",
Helper_DAO.createAndOpenConnection());
cmd.Parameters.Add("NoiDung", SqlDbType.NVarChar, 255);
cmd.Parameters["NoiDung"].Value = '%' + NoiDung.Trim() + '%';
return(Helper_DAO.getTable(cmd));
}
public static DataTable layNghiepVuHangHai()
{
SqlCommand cmd = new SqlCommand(
@"SELECT DISTINCT [dbo].[RISKPF$].RSKTYP,
[dbo].[PREMPF$].PREMCL
FROM dbo.AGNTPF$,dbo.CLNTPF$,dbo.DESCPF$, dbo.ZTRNPF$
INNER JOIN dbo.PREMPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.PREMPF$.CHDRNO
INNER JOIN dbo.RISKPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.RISKPF$.CHDRNO
INNER JOIN dbo.CHDRPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.CHDRPF$.CHDRNUM
INNER JOIN dbo.CHEXPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.CHEXPF$.CHDRNUM
WHERE
dbo.AGNTPF$.AGNTNUM = dbo.ZTRNPF$.ACCNUM
AND dbo.CLNTPF$.CLNTNUM= dbo.CHDRPF$.COWNNUM
AND dbo.DESCPF$.DESCITEM= dbo.CHEXPF$.ZDEPTCD
AND dbo.CHDRPF$.TRANNO = dbo.ZTRNPF$.TRANNO
AND LEFT([dbo].[ZTRNPF$].CNTTYPE,1) = 'M'",
Helper_DAO.createAndOpenConnection());
return(Helper_DAO.getTable(cmd));
}
public static DataTable getDetails(string RLDGACCT)
{
string sql = $@"
SELECT DISTINCT
dbo.ZTRNPF$.RLDGACCT AS 'MÃ ĐƠN',
dbo.ZTRNPF$.CNTTYPE AS'MÃ NGHIỆP VỤ',
dbo.ZTRNPF$.BATCTRCDE AS'TRANSACTION CODE',
dbo.ZTRNPF$.TRANNO AS 'SỐ TT GIAO DỊCH',
dbo.ZTRNPF$.TRANAMT01 AS 'PHÍ GROSS',
dbo.ZTRNPF$.TRANAMT03 AS 'GIẢM PHÍ',
dbo.ZTRNPF$.TRANAMT04 AS 'HOA HỒNG ĐẠI LÝ',
dbo.ZTRNPF$.TRANAMT06 AS 'PHÍ NET',
dbo.ZTRNPF$.TRANAMT07 AS 'THUẾ VAT',
dbo.ZTRNPF$.TRANAMT01 - dbo.ZTRNPF$.TRANAMT03 +dbo.ZTRNPF$.TRANAMT07 AS 'PHÍ KHÁCH HÀNG PHẢI TRẢ'
FROM dbo.AGNTPF$,dbo.CLNTPF$,dbo.DESCPF$, dbo.ZTRNPF$
INNER JOIN dbo.PREMPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.PREMPF$.CHDRNO
INNER JOIN dbo.RISKPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.RISKPF$.CHDRNO
INNER JOIN dbo.CHDRPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.CHDRPF$.CHDRNUM
INNER JOIN dbo.CHEXPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.CHEXPF$.CHDRNUM
WHERE
dbo.AGNTPF$.AGNTNUM = dbo.ZTRNPF$.ACCNUM
AND dbo.CLNTPF$.CLNTNUM= dbo.CHDRPF$.COWNNUM
AND dbo.DESCPF$.DESCITEM= dbo.CHEXPF$.ZDEPTCD
AND dbo.CHDRPF$.TRANNO = dbo.ZTRNPF$.TRANNO
AND [RLDGACCT]= @RLDGACCT
";
//Thêm parameter
SqlCommand cmd = new SqlCommand(sql, Helper_DAO.createAndOpenConnection());
if (string.IsNullOrEmpty(RLDGACCT))
{
return(new DataTable());
}
cmd.Parameters.Add("@RLDGACCT", SqlDbType.NVarChar);
cmd.Parameters["@RLDGACCT"].Value = RLDGACCT;
return(Helper_DAO.getTable(cmd));
}
public static DataTable getPrevIncomeReports(
string keyPrevBoundary,
int numDisplayItem
)
{
string sql = $@"
SELECT * FROM
(
SELECT DISTINCT TOP (@numDisplayIten)
dbo.ZTRNPF$.CNTBRANCH AS 'MÃ CHI NHÁNH',
dbo.ZTRNPF$.BATCACTYR AS 'NĂM TÀI CHÍNH',
dbo.DESCPF$.LONGDESC AS 'TÊN PHÒNG BAN',
dbo.CHEXPF$.ZDEPTCD AS 'MÃ PHÒNG BAN',
dbo.ZTRNPF$.RLDGACCT AS 'MÃ ĐƠN',
dbo.CLNTPF$.LSURNAME AS 'HỌ VÀ TÊN LÓT',
dbo.CLNTPF$.LGIVNAME AS 'TÊN KHÁCH HÀNG',
dbo.ZTRNPF$.ACCNUM AS 'MÃ ĐẠI LÝ',
dbo.AGNTPF$.AGTYPE01 AS 'LOẠI ĐẠI LÝ',
dbo.ZTRNPF$.ORIGCURR AS 'ĐƠN VỊ TIỀN TỆ',
SUM(dbo.ZTRNPF$.TRANAMT01) AS 'PHÍ GROSS',
SUM(dbo.ZTRNPF$.TRANAMT03) AS 'GIẢM PHÍ',
SUM(dbo.ZTRNPF$.TRANAMT04) AS 'HOA HỒNG ĐẠI LÝ',
SUM(dbo.ZTRNPF$.TRANAMT06) AS 'PHÍ NET',
SUM(dbo.ZTRNPF$.TRANAMT07) AS 'THUẾ VAT',
SUM(dbo.ZTRNPF$.TRANAMT01 - dbo.ZTRNPF$.TRANAMT03 +dbo.ZTRNPF$.TRANAMT07) AS 'PHÍ KHÁCH HÀNG PHẢI TRẢ',
dbo.ZTRNPF$.CCDATE AS 'NGÀY HIỆU LỰC ĐƠN',
dbo.CHDRPF$.PAYPLAN AS 'MÃ KỲ PHÍ'
FROM dbo.AGNTPF$,dbo.CLNTPF$,dbo.DESCPF$, dbo.ZTRNPF$
INNER JOIN dbo.PREMPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.PREMPF$.CHDRNO
INNER JOIN dbo.RISKPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.RISKPF$.CHDRNO
INNER JOIN dbo.CHDRPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.CHDRPF$.CHDRNUM
INNER JOIN dbo.CHEXPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.CHEXPF$.CHDRNUM
WHERE
dbo.AGNTPF$.AGNTNUM = dbo.ZTRNPF$.ACCNUM
AND dbo.CLNTPF$.CLNTNUM= dbo.CHDRPF$.COWNNUM
AND dbo.DESCPF$.DESCITEM= dbo.CHEXPF$.ZDEPTCD
AND dbo.CHDRPF$.TRANNO = dbo.ZTRNPF$.TRANNO
AND dbo.ZTRNPF$.RLDGACCT < @keyPrevBoundary
GROUP BY
dbo.ZTRNPF$.CNTBRANCH ,
dbo.ZTRNPF$.BATCACTYR ,
dbo.DESCPF$.LONGDESC,
dbo.CHEXPF$.ZDEPTCD ,
dbo.ZTRNPF$.RLDGACCT ,
dbo.CLNTPF$.LSURNAME ,
dbo.CLNTPF$.LGIVNAME ,
dbo.ZTRNPF$.ACCNUM ,
dbo.AGNTPF$.AGTYPE01 ,
dbo.ZTRNPF$.ORIGCURR ,
dbo.ZTRNPF$.CCDATE ,
dbo.CHDRPF$.PAYPLAN
ORDER BY dbo.ZTRNPF$.RLDGACCT DESC
) REVERSE_SET
ORDER BY REVERSE_SET.[MÃ ĐƠN] ";
SqlCommand cmd = new SqlCommand(sql, Helper_DAO.createAndOpenConnection());
cmd.Parameters.Add("@keyPrevBoundary", SqlDbType.NVarChar);
if (string.IsNullOrEmpty(keyPrevBoundary))
{
keyPrevBoundary = ".";
}
cmd.Parameters["@keyPrevBoundary"].Value = keyPrevBoundary;
return(Helper_DAO.getTable(cmd));
}
public static DataTable getNextIncomeReports(
string keyNextBoundary,
int numDisplayItem,
FilterIncomeReportViewModel SearchViewModel,
ICollection <Nhom> authorizedGroups
)
{
//Nếu permission rỗng
string sql = $@"
SELECT DISTINCT TOP (@numDisplayItem)
dbo.ZTRNPF$.CNTBRANCH AS 'MÃ CHI NHÁNH',
dbo.ZTRNPF$.BATCACTYR AS 'NĂM TÀI CHÍNH',
dbo.DESCPF$.LONGDESC AS 'TÊN PHÒNG BAN',
dbo.CHEXPF$.ZDEPTCD AS 'MÃ PHÒNG BAN',
dbo.ZTRNPF$.RLDGACCT AS 'MÃ ĐƠN',
dbo.CLNTPF$.LSURNAME AS 'HỌ VÀ TÊN LÓT',
dbo.CLNTPF$.LGIVNAME AS 'TÊN KHÁCH HÀNG',
dbo.ZTRNPF$.ACCNUM AS 'MÃ ĐẠI LÝ',
dbo.AGNTPF$.AGTYPE01 AS 'LOẠI ĐẠI LÝ',
dbo.ZTRNPF$.ORIGCURR AS 'ĐƠN VỊ TIỀN TỆ',
SUM(dbo.ZTRNPF$.TRANAMT01) AS 'PHÍ GROSS',
SUM(dbo.ZTRNPF$.TRANAMT03) AS 'GIẢM PHÍ',
SUM(dbo.ZTRNPF$.TRANAMT04) AS 'HOA HỒNG ĐẠI LÝ',
SUM(dbo.ZTRNPF$.TRANAMT06) AS 'PHÍ NET',
SUM(dbo.ZTRNPF$.TRANAMT07) AS 'THUẾ VAT',
SUM(dbo.ZTRNPF$.TRANAMT01 - dbo.ZTRNPF$.TRANAMT03 +dbo.ZTRNPF$.TRANAMT07) AS 'PHÍ KHÁCH HÀNG PHẢI TRẢ',
dbo.ZTRNPF$.CCDATE AS 'NGÀY HIỆU LỰC ĐƠN',
dbo.CHDRPF$.PAYPLAN AS 'MÃ KỲ PHÍ'
FROM dbo.AGNTPF$,dbo.CLNTPF$,dbo.DESCPF$, dbo.ZTRNPF$
INNER JOIN dbo.PREMPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.PREMPF$.CHDRNO
INNER JOIN dbo.RISKPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.RISKPF$.CHDRNO
INNER JOIN dbo.CHDRPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.CHDRPF$.CHDRNUM
INNER JOIN dbo.CHEXPF$ ON dbo.ZTRNPF$.RLDGACCT = dbo.CHEXPF$.CHDRNUM
WHERE
dbo.AGNTPF$.AGNTNUM = dbo.ZTRNPF$.ACCNUM
AND dbo.CLNTPF$.CLNTNUM= dbo.CHDRPF$.COWNNUM
AND dbo.DESCPF$.DESCITEM= dbo.CHEXPF$.ZDEPTCD
AND dbo.CHDRPF$.TRANNO = dbo.ZTRNPF$.TRANNO";
//Thêm parameter
SqlCommand cmd = new SqlCommand(sql, Helper_DAO.createAndOpenConnection());
if (!string.IsNullOrEmpty(keyNextBoundary)) // Nếu chuỗi rỗng không so sánh
{
cmd.CommandText += $" AND dbo.ZTRNPF$.RLDGACCT > @keyNextBoundary";
cmd.Parameters.Add("keyNextBoundary", SqlDbType.NVarChar);
cmd.Parameters["keyNextBoundary"].Value = keyNextBoundary;
}
/*Ngày Hiệu lực đơn từ*/
if (!string.IsNullOrEmpty(SearchViewModel.NgayHieuLucTu)) // Nếu chuỗi rỗng không so sánh
{
cmd.CommandText += $" AND dbo.ZTRNPF$.CCDATE >= @NgayHieuLucTu";
cmd.Parameters.Add("NgayHieuLucTu", SqlDbType.NVarChar);
cmd.Parameters["NgayHieuLucTu"].Value = SearchViewModel.NgayHieuLucTu;
}
/*Ngày Hiệu lực đơn tới*/
if (!string.IsNullOrEmpty(SearchViewModel.NgayHieuLucDen)) // Nếu chuỗi rỗng không so sánh
{
cmd.CommandText += $" AND dbo.ZTRNPF$.CCDATE <= @NgayHieuLucDen";
cmd.Parameters.Add("NgayHieuLucDen", SqlDbType.NVarChar);
cmd.Parameters["NgayHieuLucDen"].Value = SearchViewModel.NgayHieuLucDen;
}
/*Mã chi nhánh*/
//Đặt cờ nếu có it nhất 1 quyền thì Ok, không có quyền thì return datatable rỗng
bool IsHavingAtLeastOnePermision = false;
if (SearchViewModel.DanhSachChiNhanh != null && SearchViewModel.DanhSachChiNhanh.Count() > 0) // Nếu chuỗi rỗng không so sánh
{
//List<string> parameters = new List<string>();
//for (int i = 0; i <= SearchViewModel.DanhSachChiNhanh.Length-1; i++)
//{
// parameters.Add(string.Format("@DanhSachChiNhanh{0}", i));
// cmd.Parameters.AddWithValue(parameters[i], SearchViewModel.DanhSachChiNhanh[i].value[0]);
//}
//string parajoin = string.Join(",", parameters);
//cmd.CommandText += $" AND dbo.ZTRNPF$.CNTBRANCH in ({parajoin})";
Entities entities = new Entities();
foreach (Nhom group in authorizedGroups)
{
//Mỗi chi nhánh của nhóm
foreach (NhomThuocChiNhanh affiliationInGroup in group.NhomThuocChiNhanh)
{
if (SearchViewModel.DanhSachChiNhanh.Where(p => p.value == affiliationInGroup.id.ToString()).Count() > 0)
{
//Test quyền Quyền xem có không
if (createPermisionQuery(
cmd,
affiliationInGroup.QuyenXem_NhomThuocChiNhanh.Select(p => p.id).ToArray(),
affiliationInGroup.MaChiNhanh))
{
IsHavingAtLeastOnePermision = true;
}
}
}
}
}
else
{
//Lấy tất cả chi nhánh được cấp quyền
//Mỗi nhóm của tài khoản
foreach (Nhom group in authorizedGroups)
{
//Mỗi chi nhánh của nhóm
foreach (NhomThuocChiNhanh affiliationInGroup in group.NhomThuocChiNhanh)
{
//Quyền xem
if (createPermisionQuery(
cmd,
affiliationInGroup.QuyenXem_NhomThuocChiNhanh.Select(p => p.id).ToArray(),
affiliationInGroup.MaChiNhanh))
{
IsHavingAtLeastOnePermision = true;
}
}
}
}
//Nếu không có quyền nào trả về datatable rỗng
if (!IsHavingAtLeastOnePermision)
{
return(new DataTable());
}
///*Mã Phòng bah*/
//if (SearchViewModel.DanhSachPhongBan != null && SearchViewModel.DanhSachPhongBan.Count() > 0) // Nếu chuỗi rỗng không so sánh
//{
// List<string> parameters = new List<string>();
// for (int i = 0; i <= SearchViewModel.DanhSachPhongBan.Length - 1; i++)
// {
// parameters.Add(string.Format("@DanhSachPhongBan{0}", i));
// cmd.Parameters.AddWithValue(parameters[i], SearchViewModel.DanhSachPhongBan[i].value[0]);
// }
// string parajoin = string.Join(",", parameters);
// cmd.CommandText += $" AND dbo.CHEXPF$.ZDEPTCD in ({parajoin})";
//}
///*Loại đại lý*/
//if (SearchViewModel.DanhSachLoaiDaiLy != null && SearchViewModel.DanhSachLoaiDaiLy.Count() > 0) // Nếu chuỗi rỗng không so sánh
//{
// List<string> parameters = new List<string>();
// for (int i = 0; i <= SearchViewModel.DanhSachLoaiDaiLy.Length - 1; i++)
// {
// parameters.Add(string.Format("@DanhSachLoaiDaiLy{0}", i));
// cmd.Parameters.AddWithValue(parameters[i], SearchViewModel.DanhSachLoaiDaiLy[i].value[0]);
// }
// string parajoin = string.Join(",", parameters);
// cmd.CommandText += $" AND dbo.AGNTPF$.AGTYPE01 in ({parajoin})";
//}
///*Mã đại lý*/
//if (SearchViewModel.DanhSachMaDaiLy != null && SearchViewModel.DanhSachMaDaiLy.Count() > 0) // Nếu chuỗi rỗng không so sánh
//{
// List<string> parameters = new List<string>();
// for (int i = 0; i <= SearchViewModel.DanhSachMaDaiLy.Length - 1; i++)
// {
// parameters.Add(string.Format("@DanhSachMaDaiLy{0}", i));
// cmd.Parameters.AddWithValue(parameters[i], SearchViewModel.DanhSachMaDaiLy[i].value[0]);
// }
// string parajoin = string.Join(",", parameters);
// cmd.CommandText += $" AND dbo.ZTRNPF$.ACCNUM in ({parajoin})";
//}
/*Họ tên khách hàng*/
//if (SearchViewModel.DanhSachChiNhanh != null && SearchViewModel.DanhSachHoTenKhachHang.Count() > 0) // Nếu chuỗi rỗng không so sánh
//{
// List<string> parametersHoKhachHang = new List<string>();
// for (int i = 0; i <= SearchViewModel.DanhSachHoTenKhachHang.Length - 1; i++)
// {
// parametersHoKhachHang.Add(string.Format("@DanhSachHoKhachHang{0}", i));
// cmd.Parameters.AddWithValue(
// parametersHoKhachHang[i],
// SearchViewModel.DanhSachHoTenKhachHang[i].label);
// }
// string paraHojoin = string.Join(",", parametersHoKhachHang);
// cmd.CommandText += $" AND dbo.CLNTPF$.LSURNAME+' '+dbo.CLNTPF$.LGIVNAME in ({paraHojoin})";
//}
//if(!String.IsNullOrEmpty(SearchViewModel.HoTenKhachHang))
//{
// cmd.Parameters.Add("Surname", SqlDbType.NVarChar, 50);
// cmd.Parameters["Surname"].Value = SearchViewModel.HoTenKhachHang;
// cmd.Parameters.Add("Givname", SqlDbType.NVarChar, 50);
// cmd.Parameters["Givname"].Value = SearchViewModel.HoTenKhachHang;
// cmd.CommandText += $" AND ( dbo.CLNTPF$.LSURNAME COLLATE SQL_Latin1_General_CP1_CI_AI LIKE N'%'+@Surname+'%'";
// cmd.CommandText += $" OR dbo.CLNTPF$.LGIVNAME COLLATE SQL_Latin1_General_CP1_CI_AI LIKE N'%'+Givname+'%')";
//}
//cmd.CommandText += $" AND dbo.CLNTPF$. COLLATE SQL_Latin1_General_CP1_CI_AI LIKE N'%PHUONG%'";
/*Nghiệp vụ*/
if (SearchViewModel.DanhSachNghiepVu != null) // Nếu chuỗi rỗng không so sánh
{
SearchArray(cmd, "[dbo].[RISKPF$].PREMCL", "PREMCL", SearchViewModel.DanhSachNghiepVu.PREMCL);
SearchArray(cmd, "[dbo].[RISKPF$].RSKTYP", "RSKTYP", SearchViewModel.DanhSachNghiepVu.RSKTYP);
}
cmd.CommandText += @"
GROUP BY
dbo.ZTRNPF$.CNTBRANCH ,
dbo.ZTRNPF$.BATCACTYR ,
dbo.DESCPF$.LONGDESC,
dbo.CHEXPF$.ZDEPTCD ,
dbo.ZTRNPF$.RLDGACCT ,
dbo.CLNTPF$.LSURNAME ,
dbo.CLNTPF$.LGIVNAME ,
dbo.ZTRNPF$.ACCNUM ,
dbo.AGNTPF$.AGTYPE01 ,
dbo.ZTRNPF$.ORIGCURR ,
dbo.ZTRNPF$.CCDATE ,
dbo.CHDRPF$.PAYPLAN
ORDER BY dbo.ZTRNPF$.RLDGACCT";
//Para for numdisplay item
cmd.Parameters.Add("numDisplayItem", SqlDbType.Int);
//Nếu có quyền
cmd.Parameters["numDisplayItem"].Value = numDisplayItem;
//Nếu không có quyền
cmd.Parameters["numDisplayItem"].Value = 0;
return(Helper_DAO.getTable(cmd));
}