public static Boolean isExist(this OracleConnection con, string table, string column, string value, string condition = "")
{
var param = new Dapper.Oracle.OracleDynamicParameters("returns");
param.Add("v_table", table);
param.Add("v_column", column);
param.Add("v_value", value);
param.Add("v_condition", condition);
var data = (decimal)con.ExecuteScalar("CHECK_EXIST", param, commandType: System.Data.CommandType.StoredProcedure);
//var qry = $"select * from {table} where {column}=:value";
//var data = Connection().QueryFirstOrDefault(qry, new { value = value });
//if (data != null) return true;
if (data > 0)
{
return(true);
}
return(false);
}
public async Task <IActionResult> GetReport([FromQuery] Paging paging)
{
try
{
using (var db = new TM.Core.Connection.Oracle())
{
var nd = db.Connection.getUserFromToken(TM.Core.HttpContext.Header("Authorization"));
if (nd == null)
{
return(Json(new { msg = TM.Core.Common.Message.error_token.ToString() }));
}
// Đơn vị
if (!nd.inRoles("donvi.select"))
{
paging.donvi_id = nd.donvi_id;
}
// Người dùng
if (!nd.inRoles("nguoidung.select"))
{
paging.ma_nd = nd.ma_nd;
}
var param = new Dapper.Oracle.OracleDynamicParameters("returnds");
param.Add("vthang_th", paging.thang_th);
param.Add("vdonvi_id", paging.donvi_id);
param.Add("vma_nd", paging.ma_nd);
param.Add("vnhomkh_id", paging.nhomkh_id);
param.Add("vtrang_thai", paging.flag);
param.Add("vket_qua", paging.ket_qua);
// Paging Params
return(Json(new
{
data = await db.Connection.QueryAsync("baocao.baocao_kehoach", param, commandType: System.Data.CommandType.StoredProcedure),
msg = TM.Core.Common.Message.success.ToString()
}));
}
}
catch (System.Exception) { return(Json(new { msg = TM.Core.Common.Message.danger.ToString() })); }
finally { }
}
public async Task <IActionResult> getContract(string key)
{
try
{
using (var db = new TM.Core.Connection.Oracle())
{
// HD_KHACHHANG
var param = new Dapper.Oracle.OracleDynamicParameters("returns");
param.Add("v_key", key);
param.Add("v_loaihd_id", 1);
param.Add("v_tthd_id", 6);
var khachhang = db.Connection.QueryFirstOrDefault <Models.Core.HD_KHACHHANG>("GET_DB_HD_KH", param, commandType: System.Data.CommandType.StoredProcedure);
// not exist
if (khachhang == null)
{
return(Json(new { msg = TM.Core.Common.Message.not_exist.ToString() }));
}
// exist
if (db.Connection.isExist("contract_customer", "hdkh_id", khachhang.hdkh_id.ToString(), "flag=1"))
{
return(Json(new { msg = TM.Core.Common.Message.exist.ToString() }));
}
// HD_THUEBAO
param = new Dapper.Oracle.OracleDynamicParameters("returns");
param.Add("v_hdkh_id", khachhang.hdkh_id);
var thuebao = await db.Connection.QueryAsync <Models.Core.HD_THUEBAO>("GET_DB_HD_TB", param, commandType : System.Data.CommandType.StoredProcedure);
// not exist
if (thuebao == null)
{
return(Json(new { msg = TM.Core.Common.Message.not_exist.ToString() }));
}
return(Json(new { data = new { khachhang = khachhang, thuebao = thuebao }, msg = TM.Core.Common.Message.success.ToString() }));
}
}
catch (System.Exception) { return(Json(new { msg = TM.Core.Common.Message.danger.ToString() })); }
finally { }
}
public async Task <IActionResult> GetByDonVi([FromQuery] Paging paging)
{
try
{
using (var db = new TM.Core.Connection.Oracle())
{
var nd = db.Connection.getUserFromToken(TM.Core.HttpContext.Header("Authorization"));
if (nd == null)
{
return(Json(new { msg = TM.Core.Common.Message.error_token.ToString() }));
}
// Query
var qry = "ten_dv,ma_gd,ma_hd,ma_kh,ten_kh,so_dt,ten_loaihd loaihd,nguoi_cn,to_char(ngay_cn,'yyyy/MM/dd')ngay_cn,";
qry += "REPLACE(attach,'Uploads/HopDong/','')file_hd,created_by nguoi_nhap,to_char(created_at,'yyyy/MM/dd')ngay_nhap,descs ghichu";//,deleted_by,to_char(deleted_at,'dd/MM/yyyy')";
qry = $"select {(paging.is_export ? qry : "*")} from CONTRACT_CUSTOMER_KH where flag in({paging.flag})";
// Search
if (!string.IsNullOrEmpty(paging.search))
{
qry += $@" and (ma_gd='{paging.search}'
or ma_hd='{paging.search}'
or ma_kh='{paging.search}'
or CONVERTTOUNSIGN(ten_kh) like CONVERTTOUNSIGN('%{paging.search}%')
or so_dt='{paging.search}' or so_gt='{paging.search}'
or created_by like '%{paging.search}%')";
}
// Extras
if (nd.donvi_id > 0)
{
qry += $" and donvi_id in({nd.donvi_id})";
}
else
if (paging.donvi_id > 0)
{
qry += $" and donvi_id in({paging.donvi_id})";
}
if (paging.start_at.HasValue)
{
qry += $" and created_at>={paging.start_at.Value.ParseDateTime()}";
}
if (paging.end_at.HasValue)
{
qry += $" and created_at<={TM.Core.Format.Formating.AbsoluteEnd(paging.end_at.Value).ParseDateTime()}";
}
// Paging Params
if (paging.is_export)
{
paging.rowsPerPage = 0;
paging.sortBy = "ngay_nhap";
}
var param = new Dapper.Oracle.OracleDynamicParameters("v_data");
param.Add("v_sql", qry);
param.Add("v_offset", paging.page);
param.Add("v_limmit", paging.rowsPerPage);
param.Add("v_order", paging.sortBy);
param.Add("v_total", 0);
// var data = await db.Connection.QueryAsync<ContractCustomerKH>("PAGING", param, commandType: System.Data.CommandType.StoredProcedure);
// var data = await db.Connection.QueryAsync<ContractCustomerKH>(qry);
if (paging.is_export)// Export data
{
return(Json(new
{
data = await db.Connection.QueryAsync("PAGING", param, commandType: System.Data.CommandType.StoredProcedure),
total = param.Get <int>("v_total"),
msg = TM.Core.Common.Message.success.ToString()
}));
}
else // View data
{
return(Json(new
{
data = await db.Connection.QueryAsync <ContractCustomerKH>("PAGING", param, commandType: System.Data.CommandType.StoredProcedure),
total = param.Get <int>("v_total"),
msg = TM.Core.Common.Message.success.ToString()
}));
}
}
}
catch (System.Exception) { return(Json(new { msg = TM.Core.Common.Message.danger.ToString() })); }
finally { }
}
public static IEnumerable <VisitaModel> GetVisitas(decimal codgrupo, string empresa, FiltroModelView filtro)
{
using (var db = new Oracle.ManagedDataAccess.Client.OracleConnection(ApiConsorcioNet.Conexao.ConnectionStrings.AcessoOracleODP(empresa)))
{
Dapper.Oracle.OracleDynamicParameters param = new Dapper.Oracle.OracleDynamicParameters();
var vcursor = @" select distinct CODVISITA,
COD_GRUPO,
DATA_VISITA,
NOMECOMPLETO AS VISITADO,
TELEFONE as FONE,
logradouro as ENDERECO,
VENDEU,
MOTIVO_NAO_VENDA,
OBSERVACOES,
DATA_REAGENDAMENTO,
DATA_ATUALIZACAO,
USUARIO_ATUALIZACAO,
REAGENDAMENTO_REALIZADO,
EMAIL,
VALORBEM,
MARCABEM,
MODELOBEM,
UF,
CIDADE,
DDDCELULAR,
CELULAR,
DDDFONE,
WHATSAPP,
ATIVIDADE,
NOME,
TIPO_GRUPO,
datainclusao,
COD_GRUPOOUTRO,
DATA_REAGENDAMENTO,
PERCEPCAO,
CHECK_RESULT,
TIPOCONTATO,
NVL(IDPESSOA,0)IDPESSOA
from (select distinct V.CODVISITA,
V.COD_GRUPO,
V.DATA_VISITA,
V.VENDEU,
V.MOTIVO_NAO_VENDA,
V.OBSERVACOES,
V.DATA_REAGENDAMENTO,
V.DATA_ATUALIZACAO,
V.USUARIO_ATUALIZACAO,
V.REAGENDAMENTO_REALIZADO,
V.VALORBEM,
V.DTAEXCLUSAO,
V.USUARIOEXCLUSAO,
V.MARCABEM,
V.MODELOBEM,
V.DATAINCLUSAO,
V.USUARIOINCLUSAO,
V.MOTIVOVISITA,
V.PERCEPCAO,
V.TIPOCONTATO,
vp.idpessoa,
vp.nomecompleto,
vp.tipopessoa,
vp.documento,
vp.dddfone,
vp.telefone,
vp.dddcelular,
vp.celular,
vp.whatsapp,
vp.datanascimento,
vp.email,
vp.cep,
vp.logradouro,
vp.uf,
vp.cidade,
vp.bairro,
vp.atividade,
vp.informacao,
G.COD_GRUPO RESPONSAVEL,
GRUPOS.NOME,
GRUPOS.TIPO_GRUPO,
-- G.SEQREVENDA,
VORTICE.f_responsavel_cod(G.SEQREVENDA, 1, decode(USER,'CNMF',1,'CNV',2, 3)) COORDENADOR_RESPONSAVEL,
VORTICE.f_responsavel_cod(G.SEQREVENDA, 4, decode(USER,'CNMF',1,'CNV',2, 3)) SUPERVISOR_RESPONSAVEL,
VORTICE.f_responsavel_cod(G.SEQREVENDA, 11, decode(USER,'CNMF',1,'CNV',2, 3)) GERENTE_RESPONSAVEL,
VORTICE.f_responsavel_cod(G.SEQREVENDA, 6, decode(USER,'CNMF',1,'CNV',2, 3)) ADM_RESPONSAVEL,
TB.cod_grupo COD_GRUPOOUTRO
from CN_VISITAS V
INNER JOIN CN_VISITA_PESSOA VP ON V.IDPESSOA = VP.IDPESSOA
inner join CN_GRUPOS_GEPESSOA G
on G.COD_GRUPO = V.COD_GRUPO
join (select distinct a.seqrevenda,
a.cod_grupo
from cn_grupos_gepessoa a
where a.seqrevenda in (select g.seqrevenda
from cn_grupos_gepessoa g
where g.cod_grupo = :CODGRUPOLOGIN
and g.sit_grupo = 1)
and a.cod_grupo = :CODGRUPOLOGIN) TB
on TB.SEQREVENDA = G.SEQREVENDA
inner join CN_GRUPOS GRUPOS
on GRUPOS.COD_GRUPO = V.COD_GRUPO
where 1 = 1 ";
#region FILTROS DINAMICOS
if (filtro.DataVisitaInicial != null)
{
vcursor += @" and v.data_visita between :DTAVISITAINI and :DTAVISITAFIM ";
param.Add("DTAVISITAINI", filtro.DataVisitaInicial);
param.Add("DTAVISITAFIM", filtro.DataVisitaFinal);
}
if (filtro.DataReagentamentoInicial != null)
{
vcursor += @" and v.Data_Reagendamento between :DTAREAGENDAMENTO and :DTAREAGENDAMENTOFIM ";
param.Add("DTAREAGENDAMENTO", filtro.DataReagentamentoInicial);
param.Add("DTAREAGENDAMENTOFIM", filtro.DataReagentamentoFinal);
}
if (!string.IsNullOrEmpty(filtro.Vendedor))
{
vcursor += @" and V.COD_GRUPO = :VENDEDOR";
param.Add("VENDEDOR", filtro.Vendedor);
}
if (filtro.isVendaRealizada)
{
vcursor += @" and v.vendeu = '1' ";
}
vcursor += @" and v.dtaexclusao is null) UNPIVOT(CHECK_RESULT for COD_GRUPOS in(RESPONSAVEL,COORDENADOR_RESPONSAVEL, SUPERVISOR_RESPONSAVEL,
GERENTE_RESPONSAVEL,
ADM_RESPONSAVEL))VW
where CHECK_RESULT = :CODGRUPOLOGIN
AND DATA_VISITA = (SELECT MAX(VT.DATA_VISITA) FROM CN_VISITAS VT WHERE VT.IDPESSOA = VW.IDPESSOA)";
param.Add("CODGRUPOLOGIN", codgrupo);
#endregion
// var motivos = MotivoViewModel.GetAll();
var response = db.Query(vcursor, param).Select(m => new VisitaModel
{
Id = m.CODVISITA,
Cliente = new PessoaModel
{
NomeCompleto = m.VISITADO,
DddCelular = m.DDDCELULAR,
Celular = m.CELULAR,
Telefone = m.FONE,
WhatsApp = m.WHATSAPP,
Email = m.EMAIL,
Atividade = m.ATIVIDADE,
IdPessoa = Convert.ToInt32(m.IDPESSOA),
Endereco = new EnderecoModel
{
Logradouro = m.ENDERECO,
UF = m.UF,
Cidade = m.CIDADE
}
},
BemViewModel = new BemViewModel
{
Modelo = m.MODELOBEM,
Marca = m.MARCABEM
},
ValorBemAux = m.VALORBEM != null ? Convert.ToDecimal(m.VALORBEM).ToString("N2") : "",
ValorBem = m.VALORBEM,
DataVisita = m.DATA_VISITA,
IsVendaRealizada = m.VENDEU == "1",
MotivoNaoVenda = m.MOTIVO_NAO_VENDA,
Agendamento = new AgendaModel
{
DataAgendamento = m.DATA_REAGENDAMENTO,
},
NomeVendedor = m.NOME,
HistoricoVisita = m.OBSERVACOES,
Usuario = m.USUARIO_ATUALIZACAO,
DataInclusao = Convert.ToDateTime(m.DATAINCLUSAO),
MotivoVisita = m.MOTIVOVISITA,
Percepcao = m.PERCEPCAO
//IdMotivo = motivos.First(x => x.Motivo == m.MOTIVO_NAO_VENDA).IdMotivo
}).OrderByDescending(a => a.DataVisita).ToList();
//AGRUPAMENTO
//var result = response.GroupBy(a => a.Cliente.IdPessoa)
// .Select(m => new VisitaModel
// {
// Cliente = new PessoaModel
// {
// NomeCompleto = m.First().Cliente.NomeCompleto,
// DddCelular = m.First().Cliente.DDDFone,
// Celular = m.First().Cliente.Celular,
// Telefone = m.First().Cliente.Telefone,
// WhatsApp = m.First().Cliente.WhatsApp,
// Email = m.First().Cliente.Email,
// Atividade = m.First().Cliente.Atividade,
// IdPessoa = m.Key,
// Endereco = new EnderecoModel
// {
// Logradouro = m.First().Cliente.Endereco.Logradouro,
// UF = m.First().Cliente.Endereco.UF,
// Cidade = m.First().Cliente.Endereco.Cidade
// }
// },
// Id = m.Max(a => a.Id),
// CodGrupo = m.OrderByDescending(a => a.DataInclusao).First().CodGrupo,
// DataInclusao = m.Max(a => a.DataInclusao),
// DataVisita = m.Max(a => a.DataVisita),
// HistoricoVisita = m.OrderByDescending(a => a.DataInclusao).First().HistoricoVisita,
// NomeVendedor = m.OrderByDescending(a => a.DataInclusao).First().NomeVendedor,
// IsVendaRealizada = m.OrderByDescending(a => a.DataInclusao).First().IsVendaRealizada,
// MotivoNaoVenda = m.OrderByDescending(a => a.DataInclusao).First().MotivoNaoVenda,
// IdPessoa = m.Key,
// Agendamento = new AgendaModel
// {
// DataAgendamento = m.OrderByDescending(a => a.DataVisita).First().Agendamento.DataAgendamento,
// },
// Percepcao = m.OrderByDescending(a => a.DataInclusao).First().Percepcao,
// QtdVisita = m.Count(),
// MotivoVisita = m.OrderByDescending(a => a.DataInclusao).First().MotivoVisita,
// Usuario = m.OrderByDescending(a => a.DataInclusao).First().Usuario,
// BemViewModel = m.OrderByDescending(a => a.DataInclusao).First().BemViewModel,
// }).ToList().Where(a => a.Cliente.IdPessoa == 1703);
return(response);
}
}
public async Task <IActionResult> Get([FromQuery] Paging paging)
{
try
{
using (var db = new TM.Core.Connection.Oracle())
{
// var data = await db.Connection.GetAllAsync<Models.Core.Groups>();
// return Json(new { data = data, msg = TM.Core.Common.Message.success.ToString()});
var nd = db.Connection.getUserFromToken(TM.Core.HttpContext.Header("Authorization"));
if (nd == null)
{
return(Json(new { msg = TM.Core.Common.Message.error_token.ToString() }));
}
// Query
var qry = "app_key,code,dependent,levels,title,icon,image,url,orders,quantity,position,descs,content,tags,";
qry += "to_char(start_at,'dd/MM/yyyy')start_at,to_char(end_at,'dd/MM/yyyy')end_at,";
qry += "created_by,to_char(created_at,'dd/MM/yyyy')created_at,created_ip,";
qry += "updated_by,to_char(updated_at,'dd/MM/yyyy')updated_at,updated_ip,";
qry += "deleted_by,to_char(deleted_at,'dd/MM/yyyy')deleted_at,deleted_ip,flag";
qry = $"select {(paging.is_export ? qry : "*")} from Groups where flag in({paging.flag})";
// Extras
if (!string.IsNullOrEmpty(paging.app_key))
{
qry += $" and app_key in('{paging.app_key}')";
}
if (!string.IsNullOrEmpty(paging.dependent))
{
qry += $" and dependent like('%,{paging.dependent},%')";
}
// Search
if (!string.IsNullOrEmpty(paging.search))
{
qry += $@" and (or CONVERTTOUNSIGN(title) like CONVERTTOUNSIGN('%{paging.search}%'))";
}
// Paging Params
if (paging.is_export)
{
paging.rowsPerPage = 0;
}
var param = new Dapper.Oracle.OracleDynamicParameters("v_data");
param.Add("v_sql", qry);
param.Add("v_offset", paging.page);
param.Add("v_limmit", paging.rowsPerPage);
param.Add("v_order", paging.sortBy);
param.Add("v_total", 0);
if (paging.is_export) // Export data
{
return(Json(new
{
data = await db.Connection.QueryAsync("PAGING", param, commandType: System.Data.CommandType.StoredProcedure),
total = param.Get <int>("v_total"),
msg = TM.Core.Common.Message.success.ToString()
}));
}
else // View data
{
return(Json(new
{
data = await db.Connection.QueryAsync <Models.Core.Groups>("PAGING", param, commandType: System.Data.CommandType.StoredProcedure),
total = param.Get <int>("v_total"),
msg = TM.Core.Common.Message.success.ToString()
}));
}
}
}
catch (System.Exception) { return(Json(new { msg = TM.Core.Common.Message.danger.ToString() })); }
}
public async Task <IActionResult> GetThucHienTB([FromQuery] Paging paging)
{
try
{
using (var db = new TM.Core.Connection.Oracle())
{
var nd = db.Connection.getUserFromToken(TM.Core.HttpContext.Header("Authorization"));
if (nd == null)
{
return(Json(new { msg = TM.Core.Common.Message.error_token.ToString() }));
}
// Query
var qry = "";
if (paging.is_export)
{
qry = "select dv.ten_dv,gr.title,tb.ma_tb,tb.diachi_tb,tb.so_dt,tb.de_xuat,tb.thang_bd,tb.thang_kt,";
qry += "th.ma_nd,th.ngay_th,decode(th.ket_qua,1,'Chưa hoàn thành','Đã hoàn thành')ket_qua,th.ghichu ";
qry += "from kehoach_th th,kehoach_tb tb,db_donvi dv,groups gr ";
qry += $"where th.kehoachtb_id=tb.id and tb.donvi_id=dv.donvi_id and tb.nhom_kh=gr.id and th.ket_qua in({paging.flag})";
}
else
{
qry = "select th.*,tb.nhom_kh,tb.thuebao_id,tb.ma_tb,tb.ten_tb,tb.diachi_tb,tb.so_dt,tb.de_xuat,";
qry += "tb.thang_bd,tb.thang_kt,tb.nguoi_nhap,tb.ngay_nhap,tb.ip_nhap,tb.trang_thai,tb.donvi_id ";
qry += "from kehoach_th th,kehoach_tb tb ";
qry += $"where th.kehoachtb_id=tb.id and th.ket_qua in({paging.flag}) and thang_bd={paging.thang_th} and thang_kt={paging.thang_th}";
}
// Đơn vị
if (nd.cap_quyen > 1)
{
qry += $" and tb.donvi_id in({nd.donvi_id})";
if (nd.cap_quyen > 2)
{
qry += $" and th.ma_nd in('{nd.ma_nd}')";
}
else
if (paging.ma_nd != null) // if (!string.IsNullOrEmpty(paging.ma_nd))
{
qry += $" and th.ma_nd in('{String.Join("','", paging.ma_nd)}')";
}
}
if (nd.inRoles("donvi.select") && paging.donvi_id > 0)
{
qry += $" and tb.donvi_id in({String.Join(",", paging.donvi_id)})";
}
if (nd.inRoles("nguoidung.select") && paging.ma_nd != null)
{
qry += $" and th.ma_nd in('{String.Join("','", paging.ma_nd)}')";
}
// Nhóm kế hoạch
if (paging.nhomkh_id > 0)
{
qry += $" and tb.nhom_kh in({String.Join(",", paging.nhomkh_id)})";
}
// Search
if (!string.IsNullOrEmpty(paging.search))
{
qry += $@" and (CONVERTTOUNSIGN(tb.ma_tb) like CONVERTTOUNSIGN('%{paging.search}%')";
qry += $@" or CONVERTTOUNSIGN(tb.diachi_tb) like CONVERTTOUNSIGN('%{paging.search}%')";
qry += $@" or CONVERTTOUNSIGN(tb.so_dt) like CONVERTTOUNSIGN('%{paging.search}%')";
qry += $@" or CONVERTTOUNSIGN(th.ma_nd) like CONVERTTOUNSIGN('%{paging.search}%'))";
}
// Paging Params
if (paging.is_export)
{
paging.rowsPerPage = 0;
// paging.sortBy="ten_dv";
}
var param = new Dapper.Oracle.OracleDynamicParameters("v_data");
param.Add("v_sql", qry);
param.Add("v_offset", paging.page);
param.Add("v_limmit", paging.rowsPerPage);
param.Add("v_order", paging.sortBy);
param.Add("v_total", 0);
if (paging.is_export) // Export data
{
return(Json(new
{
data = await db.Connection.QueryAsync("PAGING", param, commandType: System.Data.CommandType.StoredProcedure),
total = param.Get <int>("v_total"),
msg = TM.Core.Common.Message.success.ToString()
}));
}
else // View data
{
return(Json(new
{
data = await db.Connection.QueryAsync <kehoach_TH>("PAGING", param, commandType: System.Data.CommandType.StoredProcedure),
total = param.Get <int>("v_total"),
msg = TM.Core.Common.Message.success.ToString()
}));
}
}
}
catch (System.Exception) { return(Json(new { msg = TM.Core.Common.Message.danger.ToString() })); }
finally { }
}
public async Task <IActionResult> Get([FromQuery] Paging paging)
{
try
{
using (var db = new TM.Core.Connection.Oracle())
{
// var data = await db.Connection.GetAllAsync<Models.Core.Items>();
// return Json(new { data = data, msg = TM.Core.Common.Message.success.ToString()});
var nd = db.Connection.getUserFromToken(TM.Core.HttpContext.Header("Authorization"));
if (nd == null)
{
return(Json(new { msg = TM.Core.Common.Message.error_token.ToString() }));
}
// Query
var qry = "";
if (paging.is_export)
{
qry = "select dv.ten_dv,gr.title nhom_kh,tb.ma_tb,tb.ten_tb,tb.diachi_tb,tb.so_dt,tb.ma_nd,tb.ghichu,tb.nguoi_nhap,to_char(tb.ngay_nhap,'MM/DD/YYYY')ngay_nhap,";
qry += "decode(th.ket_qua,1,'Thành công',2,'Không thành công','Chưa thực hiện')ket_qua,th.de_xuat,goicuoc.title goicuoc,lydo.title lydo,th.ghichu ghichu_th,to_char(th.ngay_th,'MM/DD/YYYY')ngay_th,th.nguoi_cn nguoi_th,th.ip_cn ip_th ";
qry += "from kehoach_tb tb,kehoach_th th,items lydo,items goicuoc,groups gr,admin_bkn.donvi dv ";
qry += $"where tb.id=th.kehoachtb_id(+) and th.lydo=lydo.id(+) and th.goicuoc=goicuoc.id(+) and tb.nhom_kh=gr.id and tb.donvi_id=dv.donvi_id and tb.trang_thai in({paging.flag})";
}
else
{
qry = "select tb.*,th.ngay_th,th.ket_qua,th.de_xuat,goicuoc.title goicuoc,lydo.title lydo,th.ghichu ghichu_th,th.nguoi_cn nguoi_cn_th,th.ip_cn ip_cn_th,th.ngay_cn ngay_cn_th ";
qry += "from kehoach_tb tb,kehoach_th th,items lydo,items goicuoc ";
qry += $"where tb.id=th.kehoachtb_id(+) and th.lydo=lydo.id(+) and th.goicuoc=goicuoc.id(+) and tb.trang_thai in({paging.flag}) and thang_bd={paging.thang_th} and thang_kt={paging.thang_th}";
}
if (paging.flag == 2 && paging.ket_qua != null)
{
qry += $" and th.ket_qua in({paging.ket_qua})";
}
// Đơn vị
if (nd.inRoles("donvi.select") && paging.donvi_id > 0)
{
qry += $" and tb.donvi_id in({String.Join(",", paging.donvi_id)})";
}
else
{
qry += $" and tb.donvi_id in({nd.donvi_id})";
}
if (nd.inRoles("nguoidung.select"))
{
if (!string.IsNullOrEmpty(paging.ma_nd))
{
if (paging.ma_nd != "0")
{
qry += $" and tb.ma_nd in('{String.Join("','", paging.ma_nd)}')";
}
}
else
{
qry += $" and tb.ma_nd is null";
}
}
else
{
qry += $" and tb.ma_nd='{nd.ma_nd}'";
}
// Nhóm kế hoạch
if (paging.nhomkh_id > 0)
{
qry += $" and tb.nhom_kh in({String.Join(",", paging.nhomkh_id)})";
}
// Search
if (!string.IsNullOrEmpty(paging.search))
{
qry += $@" and (CONVERTTOUNSIGN(tb.ma_tb) like CONVERTTOUNSIGN('%{paging.search}%')";
qry += $@" or CONVERTTOUNSIGN(tb.diachi_tb) like CONVERTTOUNSIGN('%{paging.search}%')";
qry += $@" or CONVERTTOUNSIGN(tb.so_dt) like CONVERTTOUNSIGN('%{paging.search}%')";
qry += $@" or CONVERTTOUNSIGN(tb.ma_nd) like CONVERTTOUNSIGN('%{paging.search}%'))";
}
// Paging Params
if (paging.is_export)
{
paging.rowsPerPage = 0;
// paging.sortBy="ten_dv";
}
var param = new Dapper.Oracle.OracleDynamicParameters("v_data");
param.Add("v_sql", qry);
param.Add("v_offset", paging.page);
param.Add("v_limmit", paging.rowsPerPage);
param.Add("v_order", paging.sortBy);
param.Add("v_total", 0);
if (paging.is_export) // Export data
{
return(Json(new
{
data = await db.Connection.QueryAsync("PAGING", param, commandType: System.Data.CommandType.StoredProcedure),
total = param.Get <int>("v_total"),
msg = TM.Core.Common.Message.success.ToString()
}));
}
else // View data
{
return(Json(new
{
data = await db.Connection.QueryAsync <kehoach_TH>("PAGING", param, commandType: System.Data.CommandType.StoredProcedure),
total = param.Get <int>("v_total"),
msg = TM.Core.Common.Message.success.ToString()
}));
}
}
}
catch (System.Exception) { return(Json(new { msg = TM.Core.Common.Message.danger.ToString() })); }
finally { }
}
public async Task <IActionResult> Get([FromQuery] Paging paging)
{
try
{
using (var db = new TM.Core.Connection.Oracle("DHSX"))
{
var nd = db.Connection.getUserFromToken(TM.Core.HttpContext.Header("Authorization"));
if (nd == null)
{
return(Json(new { msg = TM.Core.Common.Message.error_token.ToString() }));
}
// Query
var qry = @"select dv.ten_dv ""ten_dv"",ct.group_id ""group_id"", g.title ""nhom_dv"",ct.kieuld_id ""kieuld_id"",";
qry += @"ct.ma_hd ""ma_hd"",ct.ten_kh ""ten_kh"",ct.diachi_kh ""diachi_kh"",ct.nguoi_dd ""nguoi_dd"",";
qry += @"ct.sdt ""sdt"",ct.stk ""stk"",ct.mst ""mst"",ct.sgt ""sgt"",ct.tep_dk ""tep_dk"",";
qry += @"to_char(ct.ngay_cap,'yyyy/MM/dd') ""ngay_cap"",ct.noi_cap ""noi_cap"",";
qry += @"to_char(ct.ngay_bd,'yyyy/MM/dd') ""ngay_bd"",to_char(ct.ngay_kt,'yyyy/MM/dd') ""ngay_kt"",";
qry += @"ct.so_luong ""so_luong"",ct.tien ""tien"",ct.thue ""thue"",ct.nguoi_gt ""nguoi_gt"",ct.nguoi_tao ""nguoi_tao"",";
qry += @"to_char(ct.ngay_tao,'yyyy/MM/dd') ""ngay_tao"",ct.trang_thai ""trang_thai"",ct.noi_dung ""noi_dung"",ct.ghi_chu ""ghi_chu"" ";
// var qry = @"select ct.*,dv.ten_dv ""ten_dv""";
qry += "from ttkd_bkn.contract_enterprise ct,admin_bkn.donvi dv,ttkd_bkn.groups g ";
qry += $"where ct.donvi_id=dv.donvi_id and ct.group_id=g.id and ct.trang_thai in({paging.flag})";
// Search
if (!string.IsNullOrEmpty(paging.search))
{
qry += $@" ct.ma_hd='{paging.search}'
or CONVERTTOUNSIGN(ct.ten_kh) like CONVERTTOUNSIGN('%{paging.search}%')
or ct.so_dt='{paging.search}' or stk='{paging.search}'
or ct.mst='{paging.search}' or so_gt='{paging.search}'
or ct.nguoi_tao like '%{paging.search}%')";
}
// Đơn vị
if (nd.inRoles("donvi.select"))
{
if (paging.donvi_id > 0)
{
qry += $" and ct.donvi_id in({paging.donvi_id})";
}
}
else
{
qry += $" and ct.donvi_id in({nd.donvi_id})";
}
// Paging Params
if (paging.is_export)
{
paging.rowsPerPage = 0;
paging.sortBy = @"""ngay_tao""";
}
var param = new Dapper.Oracle.OracleDynamicParameters("v_data");
param.Add("v_sql", qry);
param.Add("v_offset", paging.page);
param.Add("v_limmit", paging.rowsPerPage);
param.Add("v_order", paging.sortBy);
param.Add("v_total", 0);
//var data = await db.Connection.QueryAsync("PAGING", param, commandType: System.Data.CommandType.StoredProcedure);
return(Json(new
{
data = await db.Connection.QueryAsync("ttkd_bkn.PAGING", param, commandType: System.Data.CommandType.StoredProcedure),
total = param.Get <int>("v_total"),
msg = TM.Core.Common.Message.success.ToString()
}));
}
}
catch (Exception) { return(Json(new { msg = TM.Core.Common.Message.danger.ToString() })); }
}
public async Task <IActionResult> Get([FromQuery] Paging paging)
{
try
{
using (var db = new TM.Core.Connection.Oracle())
{
// var data = await db.Connection.GetAllAsync<Models.Core.Items>();
// return Json(new { data = data, msg = TM.Core.Common.Message.success.ToString()});
var nd = db.Connection.getUserFromToken(TM.Core.HttpContext.Header("Authorization"));
if (nd == null)
{
return(Json(new { msg = TM.Core.Common.Message.error_token.ToString() }));
}
// Query
var qry = "";
if (paging.is_export)
{
qry = "select db.* ";
qry += "from ttkd_bkn.db_nguoidung db,ttkd_bkn.nguoidung nd,ttkd_bkn.roles r ";
qry += $"where db.nguoidung_id=nd.nguoidung_id(+) and nd.roles_id=r.id(+) and db.trangthai in({paging.flag})";
}
else
{
qry = "select db.*,nd.roles_id,r.name roles_name,r.color ";
qry += "from ttkd_bkn.db_nguoidung db,ttkd_bkn.nguoidung nd,ttkd_bkn.roles r ";
qry += $"where db.nguoidung_id=nd.nguoidung_id(+) and nd.roles_id=r.id(+) and db.trangthai in({paging.flag})";
}
// Đơn vị
if (nd.inRoles("donvi.select"))
{
if (paging.donvi_id != null && paging.donvi_id.Count > 0)
{
qry += $" and db.donvi_id in({String.Join(",", paging.donvi_id)})";
}
else
{
qry += $" and db.donvi_id in(-1)";
}
}
else
{
qry += $" and db.donvi_id in({nd.donvi_id})";
}
// Search
if (!string.IsNullOrEmpty(paging.search))
{
qry += $@" and (CONVERTTOUNSIGN(tb.ma_tb) like CONVERTTOUNSIGN('%{paging.search}%')";
qry += $@" or CONVERTTOUNSIGN(tb.diachi_tb) like CONVERTTOUNSIGN('%{paging.search}%')";
qry += $@" or CONVERTTOUNSIGN(tb.so_dt) like CONVERTTOUNSIGN('%{paging.search}%')";
qry += $@" or CONVERTTOUNSIGN(tb.ma_nd) like CONVERTTOUNSIGN('%{paging.search}%'))";
}
// Paging Params
if (paging.is_export)
{
paging.rowsPerPage = 0;
// paging.sortBy="ten_dv";
}
var param = new Dapper.Oracle.OracleDynamicParameters("v_data");
param.Add("v_sql", qry);
param.Add("v_offset", paging.page);
param.Add("v_limmit", paging.rowsPerPage);
param.Add("v_order", paging.sortBy);
param.Add("v_total", 0);
if (paging.is_export) // Export data
{
return(Json(new
{
data = await db.Connection.QueryAsync("PAGING", param, commandType: System.Data.CommandType.StoredProcedure),
total = param.Get <int>("v_total"),
msg = TM.Core.Common.Message.success.ToString()
}));
}
else // View data
{
return(Json(new
{
data = await db.Connection.QueryAsync <Authentication.Core.DBnguoidungRoles>("PAGING", param, commandType: System.Data.CommandType.StoredProcedure),
total = param.Get <int>("v_total"),
msg = TM.Core.Common.Message.success.ToString()
}));
}
}
}
catch (System.Exception) { return(Json(new { msg = TM.Core.Common.Message.danger.ToString() })); }
// try
// {
// var qry = $"select db.*,nd.roles_id,r.name roles_name,r.color from ttkd_bkn.db_nguoidung db,ttkd_bkn.nguoidung nd,ttkd_bkn.roles r where db.nguoidung_id=nd.nguoidung_id(+) and nd.roles_id=r.id(+)";
// var data = await db.Connection.QueryAsync<Authentication.Core.DBnguoidungRoles>(qry);
// return Json(new { data = data, msg = TM.Core.Common.Message.success.ToString() });
// }
// catch (System.Exception) { return Json(new { msg = TM.Core.Common.Message.danger.ToString() }); }
finally { }
}
