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 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 { }
}
