private dynamic getThoaiTra_NLML_DoiVT(RqGrafana rq)
{
List <dynamic> data = new List <dynamic>();
var(startime, endtime) = m_common.convertToString(rq);
DateTime time = DateTime.ParseExact(startime, "dd/MM/yyyy", CultureInfo.InvariantCulture);
List <Unit> listDoiVT = m_common.getListDoiVT().FindAll(item => item.donvi_cha_id == (int)rq.scopedVars.unit.value);
List <ThoaitraNLMLModel_date> list = getThoaiTraNLMLDate_oracle(rq);
var list_dq = list
.OrderBy(ele => (ele.donvi_cha_id, ele.ngay_yc))
.GroupBy(l => new { l.ngay_yc.Month, l.ngay_yc.Year, l.donvi_id })
.Select(lg =>
new
{
lg.Key.donvi_id,
thoaitra_nlml = lg.Sum(l => l.thoaitra_nlml),
unix_date = time.Year == lg.Key.Year && time.Month == lg.Key.Month && time.Day != 1 ? m_common.convertDayToUnix(time.Day + 1, lg.Key.Month, lg.Key.Year) : m_common.convertDayToUnix(1, lg.Key.Month, lg.Key.Year)
});
foreach (Unit doivt in listDoiVT)
{
List <dynamic> points = new List <dynamic>();
var tmp = list_dq.Where(item => item.donvi_id == doivt.donvi_id);
foreach (var item in tmp)
{
points.Add(new List <dynamic> {
item.thoaitra_nlml, item.unix_date
});
}
data.Add(new { target = doivt.ten_dv, datapoints = points });
}
return(data);
}
public dynamic executeDetailLapmoi(RqGrafana rq)
{
(string, string)date;
if (rq.targets[0].data.graph == "pie_chart")
{
date = m_common.convertThisYearToString(rq);
}
else
{
date = m_common.convertToString(rq);
}
var vloaitb_id = (int)rq.scopedVars.service.value;
List <Detail_lapmoi> result = new List <Detail_lapmoi>();
var dyParam = new OracleDynamicParameters();
dyParam.Add("vtungay", OracleDbType.Varchar2, ParameterDirection.Input, date.Item1);
dyParam.Add("vdenngay", OracleDbType.Varchar2, ParameterDirection.Input, date.Item2);
dyParam.Add("vloaitb_id", OracleDbType.Int32, ParameterDirection.Input, vloaitb_id);
dyParam.Add("o_data", OracleDbType.RefCursor, ParameterDirection.Output);
var conn = GetConnection();
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
if (conn.State == ConnectionState.Open)
{
var query = "khanhnv.DASHBOARD.detail_lapmoi";
result = SqlMapper.Query <Detail_lapmoi>(conn, query, param: dyParam, commandType: CommandType.StoredProcedure).AsList <Detail_lapmoi>();
}
return(result);
}
public dynamic testQuery(RqGrafana rq)
{
DateTime tngay = Convert.ToDateTime(rq.range.from);
DateTime dngay = Convert.ToDateTime(rq.range.to);
var vtungay = (tngay.Day < 10 ? ("0" + tngay.Day.ToString()) : tngay.Day.ToString()) +
"/" + (tngay.Month < 10 ? "0" + tngay.Month.ToString() : tngay.Month.ToString()) + "/" + tngay.Year.ToString();
var vdenngay = (dngay.Day < 10 ? ("0" + dngay.Day.ToString()) : dngay.Day.ToString()) +
"/" + (dngay.Month < 10 ? "0" + dngay.Month.ToString() : dngay.Month.ToString()) + "/" + dngay.Year.ToString();
List <BscRespond> result = new List <BscRespond>();
var dyParam = new OracleDynamicParameters();
dyParam.Add("vtungay", OracleDbType.Varchar2, ParameterDirection.Input, vtungay);
dyParam.Add("vdenngay", OracleDbType.Varchar2, ParameterDirection.Input, vdenngay);
dyParam.Add("returnds", OracleDbType.RefCursor, ParameterDirection.Output);
var conn = GetConnection();
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
if (conn.State == ConnectionState.Open)
{
var query = "dashboard.i8_sudung_mobile_app_by_khanh";
result = SqlMapper.Query <BscRespond>(conn, query, param: dyParam, commandType: CommandType.StoredProcedure).AsList <BscRespond>();
}
return(result);
}
public dynamic getDetailRemove(RqGrafana rq)
{
List <dynamic> response = new List <dynamic>();
dynamic lst_donviId;
if ((string)rq.targets[0].data.center == "ttkd")
{
lst_donviId = rq.scopedVars.ttkd.value;
}
else
{
lst_donviId = rq.scopedVars.ttvt.value;
}
List <dynamic> total = new List <dynamic>();
List <dynamic> total_date = new List <dynamic>();
List <dynamic> total_timeseries = new List <dynamic>();
List <Unit> listUnit = m_common.getListCenter(rq);
List <dynamic> removeListByDate = m_detail_go.getRemoveByDate(rq);
foreach (var unit in listUnit)
{
List <dynamic> lst_remove = new List <dynamic>(removeListByDate.FindAll(r => r.donvi_id == unit.donvi_id));
if (lst_remove.Count() > 0)
{
List <dynamic> lst_remove_date = new List <dynamic>(lst_remove.Select(lg => lg.unix_date));
total_date.Add(lst_remove_date);
total.Add(lst_remove);
}
else
{
List <dynamic> temp = new List <dynamic> ();
temp.Add(new { unit.donvi_id, sl_huy = 0, removeListByDate[0].unix_date });
total.Add(temp);
}
}
total_date = m_common.getFullList(total_date);
total = m_detail_go.getEqualLengthRemove(listUnit, total, total_date);
total_timeseries = m_detail_go.getTimeseriesRemove(total);
if (rq.targets[0].data.graph == "pie_chart" || rq.targets[0].data.graph == "column_chart")
{
response = total_timeseries;
}
else
{
List <dynamic> max_points = m_common.getMaxPoints(total_timeseries);
List <dynamic> min_points = m_common.getMinPoints(total_timeseries);
List <dynamic> avr_points = m_common.getAveragePoints(total_timeseries);
response.Add(new { target = "GT MAX", datapoints = max_points });
response.Add(new { target = "GT trung bình", datapoints = avr_points });
response.Add(new { target = "GT MIN", datapoints = min_points });
if ((int)lst_donviId != 1)
{
var unit = listUnit.Where(r => r.donvi_id == (int)lst_donviId).FirstOrDefault();
var unit_data = total_timeseries.Where(r => r.target == unit.ten_dv).FirstOrDefault();
response.Add(unit_data);
}
;
}
return(response);
}
private dynamic getHSSDCQ(RqGrafana rq)
{
List <UsingPerformance> data = new List <UsingPerformance>();
data = m_pktReport.executeHSSDCQ(rq);
return(data);
}
private dynamic getLuykeThucTang_TTVT_date(RqGrafana rq)
{
List <dynamic> data = new List <dynamic>();
List <Unit> listTTVT = m_common.getListTTVT();
List <LuykeLapgoFiber_date> list = getLuykeLapgoFiber_oracle(rq);
var list_tt = list
.OrderBy(ele => (ele.donvi_id, ele.ngay))
.GroupBy(l => new { l.ngay.Day, l.ngay.Month, l.ngay.Year, l.donvi_id })
.Select(lg =>
new
{
lg.Key.donvi_id,
tyle_golm = Math.Round((double)(lg.Sum(l => l.go_yeucau_lk) + lg.Sum(l => l.go_lamsach_lk) + lg.Sum(l => l.dd_go_lk) + lg.Sum(l => l.cd_giam_lk)) * 100 / (lg.Sum(l => l.lapmoi_lk) + lg.Sum(l => l.phuchoi_lk) + lg.Sum(l => l.dd_lm_lk) + lg.Sum(l => l.chuyendoi_lk)), 2),
unix_date = m_common.convertDayToUnix(lg.Key.Day, lg.Key.Month, lg.Key.Year)
});
foreach (Unit ttvt in listTTVT)
{
List <dynamic> points = new List <dynamic>();
var tmp = list_tt.Where(item => item.donvi_id == ttvt.donvi_id);
foreach (var item in tmp)
{
points.Add(new List <dynamic> {
item.tyle_golm, item.unix_date
});
}
data.Add(new { target = ttvt.ten_dv, datapoints = points });
}
return(data);
}
private dynamic getLuykeLapMoi_TTVT_date(RqGrafana rq)
{
List <dynamic> data = new List <dynamic>();
List <Unit> listTTVT = m_common.getListTTVT();
List <LuykeLapgoFiber_date> list = getLuykeLapgoFiber_oracle(rq);
var list_lapmoi = list
.OrderBy(ele => (ele.donvi_id, ele.ngay))
.GroupBy(l => new { l.ngay.Day, l.ngay.Month, l.ngay.Year, l.donvi_id })
.Select(lg =>
new
{
lg.Key.donvi_id,
sl_lapmoi = lg.Sum(l => l.lapmoi_lk) + lg.Sum(l => l.phuchoi_lk) + lg.Sum(l => l.dd_lm_lk) + lg.Sum(l => l.chuyendoi_lk),
unix_date = m_common.convertDayToUnix(lg.Key.Day, lg.Key.Month, lg.Key.Year)
});
foreach (Unit ttvt in listTTVT)
{
List <dynamic> points = new List <dynamic>();
var tmp = list_lapmoi.Where(item => item.donvi_id == ttvt.donvi_id);
foreach (var item in tmp)
{
points.Add(new List <dynamic> {
item.sl_lapmoi, item.unix_date
});
}
data.Add(new { target = ttvt.ten_dv, datapoints = points });
}
return(data);
}
public dynamic executeTKMLLBTS(RqGrafana rq, string loai_mang)
{
var date = m_common.convertToString(rq);
List <tk_mll_bts> result = new List <tk_mll_bts>();
var dyParam = new OracleDynamicParameters();
dyParam.Add("loai_loi", OracleDbType.Varchar2, ParameterDirection.Input, loai_mang);
dyParam.Add("i_ttvt", OracleDbType.Int16, ParameterDirection.Input, (int)rq.scopedVars.unit.value);
dyParam.Add("vtungay", OracleDbType.Varchar2, ParameterDirection.Input, date.Item1);
dyParam.Add("vdenngay", OracleDbType.Varchar2, ParameterDirection.Input, date.Item2);
dyParam.Add("o_data", OracleDbType.RefCursor, ParameterDirection.Output);
var conn = GetConnection();
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
if (conn.State == ConnectionState.Open)
{
var query = "dashboard.tk_mll_bts";
result = SqlMapper.Query <tk_mll_bts>(conn, query, param: dyParam, commandType: CommandType.StoredProcedure).AsList <tk_mll_bts>();
conn.Close();
}
return(result);
}
public dynamic getThoaiTraPTC(RqGrafana rq)
{
List <dynamic> data = new List <dynamic>();
List <ThoaitraPTC> list = getThoaiTraDate(rq);
if ((int)rq.scopedVars.type.value == 1006)
{
if (rq.targets[0].data.graph == "line")
{
data = getThoaiTraTTVT_date(rq, list);
}
else
{
data = getThoaiTraTTVT(rq, list);
}
}
else if ((int)rq.scopedVars.type.value == 1007)
{
if (rq.targets[0].data.graph == "line")
{
data = getThoaiTraTTKD_date(rq, list);
}
else
{
data = getThoaiTraTTKD(rq, list);
}
}
return(data);
}
public dynamic GetData_Dissatisfied_ServeQuality(RqGrafana rq)
{
var date = m_common.convertToString(rq);
//string startdate = "01/" + date.Item1.Substring(3, 2) + "/" + date.Item1.Substring(6, 4);
//string enddate = DateTime.DaysInMonth(Int32.Parse(date.Item1.Substring(6, 4)), Int32.Parse(date.Item1.Substring(3, 2))).ToString()+"/"+ date.Item1.Substring(3, 2) + "/" + date.Item1.Substring(6, 4);
List <Dissatisfied> result = new List <Dissatisfied>();
var dyParam = new OracleDynamicParameters();
dyParam.Add("vtungay", OracleDbType.Varchar2, ParameterDirection.Input, date.Item1);
dyParam.Add("vdenngay", OracleDbType.Varchar2, ParameterDirection.Input, date.Item2);
dyParam.Add("ref_cur", OracleDbType.RefCursor, ParameterDirection.Output);
var conn = GetConnection();
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
if (conn.State == ConnectionState.Open)
{
var query = "dashboard.TK_KhongHaiLong_CLPV";
result = SqlMapper.Query <Dissatisfied>(conn, query, param: dyParam, commandType: CommandType.StoredProcedure).AsList <Dissatisfied>();
}
return(result);
}
public dynamic executeI8MobileApp(RqGrafana rq)
{
var date = m_common.convertToString(rq);
var vdonvi_id = (int)rq.scopedVars.ttvt.value;
List <UsageResponse> result = new List <UsageResponse>();
var dyParam = new OracleDynamicParameters();
dyParam.Add("vtungay", OracleDbType.Varchar2, ParameterDirection.Input, date.Item1);
dyParam.Add("vdenngay", OracleDbType.Varchar2, ParameterDirection.Input, date.Item2);
dyParam.Add("vdonvi_id", OracleDbType.Int32, ParameterDirection.Input, vdonvi_id);
dyParam.Add("returnds", OracleDbType.RefCursor, ParameterDirection.Output);
var conn = GetConnection();
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
if (conn.State == ConnectionState.Open)
{
var query = "dashboard.i8_sudung_mobile_app_by_khanh";
result = SqlMapper.Query <UsageResponse>(conn, query, param: dyParam, commandType: CommandType.StoredProcedure).AsList <UsageResponse>();
}
return(result);
}
private dynamic getThoaiTra_DQ_TTVT_date(RqGrafana rq)
{
List <dynamic> data = new List <dynamic>();
var(startime, endtime) = m_common.convertToString(rq);
List <Unit> listTTVT = m_common.getListTTVT();
List <ThoaitraNLMLModel_date> list = getThoaiTraNLMLDate_oracle(rq);
var list_dq = list
.OrderBy(ele => (ele.donvi_cha_id, ele.ngay_yc))
.GroupBy(l => new { l.ngay_yc.Day, l.ngay_yc.Month, l.ngay_yc.Year, l.donvi_cha_id })
.Select(lg =>
new
{
lg.Key.donvi_cha_id,
thoaitra_docquyen = lg.Sum(l => l.thoaitra_docquyen),
unix_date = m_common.convertDayToUnix(lg.Key.Day, lg.Key.Month, lg.Key.Year)
});
foreach (Unit ttvt in listTTVT)
{
List <dynamic> points = new List <dynamic>();
var tmp = list_dq.Where(item => item.donvi_cha_id == ttvt.donvi_id);
foreach (var item in tmp)
{
points.Add(new List <dynamic> {
item.thoaitra_docquyen, item.unix_date
});
}
data.Add(new { target = ttvt.ten_dv, datapoints = points });
}
return(data);
}
public dynamic getLydoThoaiTra(RqGrafana rq)
{
List <dynamic> data = new List <dynamic>();
switch ((int)rq.scopedVars.type.value)
{
case 2009: //NLML
if (rq.targets[0].data.graph == "line")
{
data = getThoaiTra_NLML_date(rq);
}
else
{
data = getThoaiTra_NLML(rq);
}
break;
case 2010: //Doc quyen
if (rq.targets[0].data.graph == "line")
{
data = getThoaiTra_DQ_date(rq);
}
else
{
data = getThoaiTra_DQ(rq);
}
break;
}
return(data);
}
private dynamic getCLPV_KHL(RqGrafana rq, List <TK_KhongHaiLong_CLPV> list)
{
List <dynamic> data = new List <dynamic>();
var date = m_common.convertToString(rq);
DateTime time = DateTime.ParseExact(date.Item1, "dd/MM/yyyy", CultureInfo.InvariantCulture);
if ((int)rq.scopedVars.unit.value == 0)
{
List <Unit> listTTVT = m_common.getListTTVT();
foreach (Unit ttvt in listTTVT)
{
var list_clpv_khl = list
.OrderBy(ele => (ele.donvi_cha_id, ele.ngay))
.GroupBy(l => new { l.ngay.Month, l.ngay.Year, l.donvi_cha_id })
.Select(lg =>
new
{
lg.Key.donvi_cha_id,
sl = lg.Sum(l => l.sl),
unix_date = time.Year == lg.Key.Year && time.Month == lg.Key.Month && time.Day != 1 ? m_common.convertDayToUnix(time.Day + 1, lg.Key.Month, lg.Key.Year) : m_common.convertDayToUnix(1, lg.Key.Month, lg.Key.Year)
})
.Where(item => item.donvi_cha_id == ttvt.donvi_id);
List <dynamic> points = new List <dynamic>();
foreach (var item in list_clpv_khl)
{
points.Add(new List <dynamic> {
item.sl, item.unix_date
});
}
data.Add(new { target = ttvt.ten_dv, datapoints = points });
}
}
else
{
int donvi_id = (int)rq.scopedVars.unit.value;
string ten_dv = (string)rq.scopedVars.unit.text;
var list_clpv_khl = list
.OrderBy(ele => (ele.donvi_cha_id, ele.ngay))
.GroupBy(l => new { l.ngay.Month, l.ngay.Year, l.donvi_cha_id })
.Select(lg =>
new
{
lg.Key.donvi_cha_id,
sl = lg.Sum(l => l.sl),
unix_date = time.Year == lg.Key.Year && time.Month == lg.Key.Month && time.Day != 1 ? m_common.convertDayToUnix(time.Day + 1, lg.Key.Month, lg.Key.Year) : m_common.convertDayToUnix(1, lg.Key.Month, lg.Key.Year)
})
.Where(item => item.donvi_cha_id == donvi_id);
List <dynamic> points = new List <dynamic>();
foreach (var item in list_clpv_khl)
{
points.Add(new List <dynamic> {
item.sl, item.unix_date
});
}
data.Add(new { target = ten_dv, datapoints = points });
}
return(data);
}
private dynamic getThoaiTraTTVT_date(RqGrafana rq, List <ThoaitraPTC> list)
{
List <dynamic> data = new List <dynamic>();
if ((int)rq.scopedVars.unit.value == 0)
{
List <Unit> listTTVT = m_common.getListTTVT();
foreach (Unit ttvt in listTTVT)
{
var list_thoaitra = list
.OrderBy(ele => (ele.donvi_id, ele.ngay_yc))
.GroupBy(l => new { l.ngay_yc.Day, l.ngay_yc.Month, l.ngay_yc.Year, l.donvi_id, l.donvi })
.Select(lg =>
new
{
lg.Key.donvi_id,
lg.Key.donvi,
pct_thoaitra_ttvt = lg.Sum(l => l.pct_thoaitra_ttvt),
unix_date = m_common.convertDayToUnix(lg.Key.Day, lg.Key.Month, lg.Key.Year)
})
.Where(item => item.donvi_id == ttvt.donvi_id);
List <dynamic> points = new List <dynamic>();
foreach (var item in list_thoaitra)
{
points.Add(new List <dynamic> {
item.pct_thoaitra_ttvt, item.unix_date
});
}
data.Add(new { target = ttvt.ten_dv, datapoints = points });
}
}
else
{
int donvi_id = (int)rq.scopedVars.unit.value;
var list_thoaitra = list
.OrderBy(ele => (ele.donvi_id, ele.ngay_yc))
.GroupBy(l => new { l.ngay_yc.Day, l.ngay_yc.Month, l.ngay_yc.Year, l.donvi_id, l.donvi })
.Select(lg =>
new
{
lg.Key.donvi_id,
lg.Key.donvi,
pct_thoaitra_ttvt = lg.Sum(l => l.pct_thoaitra_ttvt),
unix_date = m_common.convertDayToUnix(lg.Key.Day, lg.Key.Month, lg.Key.Year)
})
.Where(item => item.donvi_id == donvi_id);
List <dynamic> points = new List <dynamic>();
foreach (var item in list_thoaitra)
{
points.Add(new List <dynamic> {
item.pct_thoaitra_ttvt, item.unix_date
});
}
data.Add(new { target = list.FirstOrDefault(item => item.donvi_id == donvi_id).donvi, datapoints = points });
}
return(data);
}
private dynamic getCLPV_KHL_date(RqGrafana rq, List <TK_KhongHaiLong_CLPV> list)
{
List <dynamic> data = new List <dynamic>();
if ((int)rq.scopedVars.unit.value == 0)
{
List <Unit> listTTVT = m_common.getListTTVT();
foreach (Unit ttvt in listTTVT)
{
var list_clpv_khl = list
.OrderBy(ele => (ele.donvi_cha_id, ele.ngay))
.GroupBy(l => new { l.ngay.Day, l.ngay.Month, l.ngay.Year, l.donvi_cha_id })
.Select(lg =>
new
{
lg.Key.donvi_cha_id,
sl = lg.Sum(l => l.sl),
unix_date = m_common.convertDayToUnix(lg.Key.Day, lg.Key.Month, lg.Key.Year)
})
.Where(item => item.donvi_cha_id == ttvt.donvi_id);
List <dynamic> points = new List <dynamic>();
foreach (var item in list_clpv_khl)
{
points.Add(new List <dynamic> {
item.sl, item.unix_date
});
}
data.Add(new { target = ttvt.ten_dv, datapoints = points });
}
}
else
{
int donvi_id = (int)rq.scopedVars.unit.value;
string ten_dv = (string)rq.scopedVars.unit.text;
var list_clpv_khl = list
.OrderBy(ele => (ele.donvi_cha_id, ele.ngay))
.GroupBy(l => new { l.ngay.Day, l.ngay.Month, l.ngay.Year, l.donvi_cha_id })
.Select(lg =>
new
{
lg.Key.donvi_cha_id,
sl = lg.Sum(l => l.sl),
unix_date = m_common.convertDayToUnix(lg.Key.Day, lg.Key.Month, lg.Key.Year)
})
.Where(item => item.donvi_cha_id == donvi_id);
List <dynamic> points = new List <dynamic>();
foreach (var item in list_clpv_khl)
{
points.Add(new List <dynamic> {
item.sl, item.unix_date
});
}
data.Add(new { target = ten_dv, datapoints = points });
}
return(data);
}
public dynamic getNew(RqGrafana rq)
{
List <dynamic> response = new List <dynamic>();
dynamic lst_donviId;
if ((string)rq.targets[0].data.center == "ttkd")
{
lst_donviId = rq.scopedVars.ttkd.value;
}
else
{
lst_donviId = rq.scopedVars.ttvt.value;
}
if (rq.targets[0].type == "timeseries")
{
List <dynamic> total = new List <dynamic>();
List <dynamic> total_date = new List <dynamic>();
List <dynamic> total_timeseries = new List <dynamic>();
List <Unit> listUnit = m_common.getListCenter(rq);
List <dynamic> newListByDate = getNewByDate(rq);
foreach (var unit in listUnit)
{
List <dynamic> lst_new = new List <dynamic>(newListByDate.FindAll(r => r.donvi_id == unit.donvi_id));
List <dynamic> lst_new_date = new List <dynamic>(lst_new.Select(lg => lg.unix_date));
total_date.Add(lst_new_date);
total.Add(lst_new);
}
total_date = m_common.getFullList(total_date);
total = getEqualLengthNew(listUnit, total, total_date);
total_timeseries = getTimeseriesNew(total);
if (rq.targets[0].data.graph == "pie_chart")
{
response = total_timeseries;
}
else
{
List <dynamic> max_points = m_common.getMaxPoints(total_timeseries);
List <dynamic> min_points = m_common.getMinPoints(total_timeseries);
List <dynamic> avr_points = m_common.getAveragePoints(total_timeseries);
response.Add(new { target = "GT MAX", datapoints = max_points });
response.Add(new { target = "GT trung bình", datapoints = avr_points });
response.Add(new { target = "GT MIN", datapoints = min_points });
if ((int)lst_donviId != 1)
{
var unit = listUnit.Where(r => r.donvi_id == (int)lst_donviId).FirstOrDefault();
var unit_data = total_timeseries.Where(r => r.target == unit.ten_dv).FirstOrDefault();
response.Add(unit_data);
}
;
}
}
else if (rq.targets[0].type == "table")
{
}
return(response);
}
public dynamic getListCenter(RqGrafana rq)
{
UnitRequest type_center = new UnitRequest();
type_center.target = (string)rq.targets[0].data.center;
List <Unit> listUnit = m_organization.getAllCenter(type_center);
return(listUnit);
}
public dynamic getStatic(RqGrafana rq)
{
List <dynamic> data = new List <dynamic>();
switch ((int)rq.scopedVars.criteria.value)
{
case 1: //CCDV
data = m_ccdv.getCCDV(rq);
break;
case 2: //SCDV
data = m_scdv.getSCDV(rq);
break;
case 3: //HSSDC
data = m_hssdc.getHSSDC(rq);
break;
case 4: //XLSC
data = m_xlsc.getXLSC(rq);
break;
case 5: //CLDV
data = m_cldv.getCLDV(rq);
break;
case 6: //CLPV
data = m_clpv.getCLPV(rq);
break;
case 7: //Thoai tra PCT
data = m_thoaitra.getThoaiTraPTC(rq);
break;
case 8: //Ly do thoai tra
data = m_thoaiTraNLML.getLydoThoaiTra(rq);
break;
case 9: //MLL BTS TG
data = m_mllbts.getMLLBTS_TG(rq);
break;
case 11: //MLL BTS NN
data = m_mllbts.getMLLBTS_NN(rq);
break;
case 1014: //Ton LD Fiber
data = m_tonLDFiber.getTonLDFiber(rq);
break;
case 1016: //Luy ke go/lap moi FiberVNN
data = m_luykeLapgoFiber.getLuykeLapgoFiberVNN(rq);
break;
}
return(data);
}
public dynamic getXLSC(RqGrafana rq)
{
List <dynamic> data = new List <dynamic>();
var date = m_common.convertToString(rq);
if ((int)rq.scopedVars.type.value == 1011) //Bao hong
{
if (rq.targets[0].data.graph == "line")
{
data = getXLSC_SLBH_date((int)rq.scopedVars.cable.value, (int)rq.scopedVars.unit.value, date.Item1, date.Item2);
}
else
{
data = getXLSC_SLBH((int)rq.scopedVars.cable.value, (int)rq.scopedVars.unit.value, date.Item1, date.Item2);
}
}
else if ((int)rq.scopedVars.type.value == 1012) //Sua dung gio
{
if (rq.targets[0].data.graph == "line")
{
data = getXLSC_SLST_date((int)rq.scopedVars.cable.value, (int)rq.scopedVars.unit.value, date.Item1, date.Item2);
}
else
{
data = getXLSC_SLST((int)rq.scopedVars.cable.value, (int)rq.scopedVars.unit.value, date.Item1, date.Item2);
}
}
else if ((int)rq.scopedVars.type.value == 1013) //Sua qua gio
{
if (rq.targets[0].data.graph == "line")
{
data = getXLSC_SLQG_date((int)rq.scopedVars.cable.value, (int)rq.scopedVars.unit.value, date.Item1, date.Item2);
}
else
{
data = getXLSC_SLQG((int)rq.scopedVars.cable.value, (int)rq.scopedVars.unit.value, date.Item1, date.Item2);
}
}
else if ((int)rq.scopedVars.type.value == 6) //Ty le
{
if (rq.targets[0].data.graph == "line")
{
data = getXLSC_TL_date((int)rq.scopedVars.cable.value, (int)rq.scopedVars.unit.value, date.Item1, date.Item2);
}
else if (rq.targets[0].data.graph == "pie")
{
data = getXLSC_SLST((int)rq.scopedVars.cable.value, (int)rq.scopedVars.unit.value, date.Item1, date.Item2);
}
else
{
data = getXLSC_TL((int)rq.scopedVars.cable.value, (int)rq.scopedVars.unit.value, date.Item1, date.Item2);
}
}
return(data);
}
public (string, string) convertToString(RqGrafana rq)
{
DateTime tngay = Convert.ToDateTime(rq.range.from);
DateTime dngay = Convert.ToDateTime(rq.range.to);
var vtungay = (tngay.Day < 10 ? ("0" + tngay.Day.ToString()) : tngay.Day.ToString()) +
"/" + (tngay.Month < 10 ? "0" + tngay.Month.ToString() : tngay.Month.ToString()) + "/" + tngay.Year.ToString();
var vdenngay = (dngay.Day < 10 ? ("0" + dngay.Day.ToString()) : dngay.Day.ToString()) +
"/" + (dngay.Month < 10 ? "0" + dngay.Month.ToString() : dngay.Month.ToString()) + "/" + dngay.Year.ToString();
return(vtungay, vdenngay);
}
public dynamic execureI8MobileApp([FromBody] RqGrafana data)
{
List <BscRespond> result = m_bsc.testQuery(data);
//foreach (var ele in result)
//{
// ele.unix_time = m_common.convertToUnix((string)ele.ngay);
//}
return(result);
}
public dynamic getLuykeLapgoFiber(RqGrafana rq)
{
List <dynamic> response = new List <dynamic>();
List <LuykeLapgoFiber> list_LuykeLapgoFiber = executeLuykeLapgoFiber(rq);
DateTime dngay = Convert.ToDateTime(rq.range.to);
var date = m_common.convertToString(rq);
if (rq.targets[0].type == "table")
{
List <dynamic> col = new List <dynamic>
{
new { text = "Tên đơn vị", type = "string" },
new { text = "Thực tăng năm", type = "number" },
new { text = "Lắp mới lũy kế lắp đặt", type = "number" },
new { text = "Phục hồi lũy kế lắp đặt", type = "number" },
new { text = "Dịch chuyển lũy kế lắp đặt", type = "number" },
new { text = "Chuyển đổi lũy kế lắp đặt", type = "number" },
new { text = "Gỡ - Yêu cầu lũy kế hủy bỏ", type = "number" },
new { text = "Gỡ - Làm sạch lũy kế hủy bỏ", type = "number" },
new { text = "Dịch chuyển lũy kế hủy bỏ", type = "number" },
new { text = "Chuyển đổi lũy kế hủy bỏ", type = "number" },
new { text = "Tổng thuê bao", type = "number" },
new { text = "Tỉ lệ", type = "number" }
};
List <dynamic> row = new List <dynamic>();
foreach (LuykeLapgoFiber item in list_LuykeLapgoFiber)
{
row.Add(new List <dynamic> {
item.donvi, item.luyke_nam, item.lapmoi_lk, item.phuchoi_lk, item.dd_lm_lk, item.chuyendoi_lk,
item.go_yeucau_lk, item.go_lamsach_lk, item.dd_go_lk, item.cd_giam_lk, item.tong_thuebao, item.tile
});
}
response = new List <dynamic> {
new {
columns = col,
rows = row,
type = "table"
}
};
}
else
{
foreach (LuykeLapgoFiber item in list_LuykeLapgoFiber)
{
List <dynamic> points = new List <dynamic>();
points.Add(new List <dynamic> {
item.tile, m_common.convertDayToUnix(dngay.Day, dngay.Month, dngay.Year)
});
response.Add(new { target = item.donvi, datapoints = points });
}
}
return(response);
}
public dynamic getMLLNN(RqGrafana rq)
{
List <dynamic> response = new List <dynamic>();
List <MLLNN> list_MLLNN = executeMLLNN(rq);
DateTime dngay = Convert.ToDateTime(rq.range.to);
var date = m_common.convertToString(rq);
if (rq.targets[0].type == "table")
{
List <dynamic> col = new List <dynamic>
{
new { text = "Tên lỗi", type = "string" },
new { text = "Số lượng trạm MLL", type = "number" },
new { text = "Tổng số", type = "number" },
new { text = "Tỷ lệ", type = "double" },
new { text = "Tháng", type = "string" }
};
List <dynamic> row = new List <dynamic>();
foreach (MLLNN item in list_MLLNN)
{
row.Add(new List <dynamic> {
item.ten_loi, item.sl_mll, item.tongcong, item.ty_le, item.thangketthuc
});
}
response = new List <dynamic> {
new {
columns = col,
rows = row,
type = "table"
}
};
}
else
{
dynamic newList = list_MLLNN.GroupBy(g => new
{
g.ten_loi
}).Select(lg => new
{
lg.Key.ten_loi,
sl_mll = lg.Where(o => o.ten_loi == lg.Key.ten_loi).Sum(o => o.sl_mll)
}).OrderByDescending(o => o.sl_mll);
foreach (var item in newList)
{
List <dynamic> points = new List <dynamic>();
points.Add(new List <dynamic> {
item.sl_mll, m_common.convertDayToUnix(dngay.Day, dngay.Month, dngay.Year)
});
response.Add(new { target = item.ten_loi, datapoints = points });
}
}
return(response);
}
public (string, string) convertThisYearToString(RqGrafana rq)
{
var date = Convert.ToDateTime(rq.range.to);
DateTime tngay = Convert.ToDateTime(date.Year.ToString() + "-01-01");
DateTime dngay = Convert.ToDateTime(date.Year.ToString() + "-12-31");
var vtungay = (tngay.Day < 10 ? ("0" + tngay.Day.ToString()) : tngay.Day.ToString()) +
"/" + (tngay.Month < 10 ? "0" + tngay.Month.ToString() : tngay.Month.ToString()) + "/" + tngay.Year.ToString();
var vdenngay = (dngay.Day < 10 ? ("0" + dngay.Day.ToString()) : dngay.Day.ToString()) +
"/" + (dngay.Month < 10 ? "0" + dngay.Month.ToString() : dngay.Month.ToString()) + "/" + dngay.Year.ToString();
return(vtungay, vdenngay);
}
private dynamic getLuykeThucTang_TTVT(RqGrafana rq)
{
List <dynamic> data = new List <dynamic>();
var(startime, endtime) = m_common.convertToString(rq);
DateTime time = DateTime.ParseExact(startime, "dd/MM/yyyy", CultureInfo.InvariantCulture);
List <Unit> listTTVT = m_common.getListTTVT();
List <LuykeLapgoFiber_date> list = getLuykeLapgoFiber_oracle(rq);
var list_tt = list
.OrderBy(ele => (ele.donvi_id, ele.ngay))
.GroupBy(l => new { l.ngay.Month, l.ngay.Year, l.donvi_id })
.Select(lg =>
new
{
lg.Key.donvi_id,
tyle_golm = Math.Round((double)(lg.Where(l => l.ngay == new DateTime(l.ngay.Year, l.ngay.Month, DateTime.DaysInMonth(l.ngay.Year, l.ngay.Month)))
.Sum(l => l.go_yeucau_lk)
+ lg.Where(l => l.ngay == new DateTime(l.ngay.Year, l.ngay.Month, DateTime.DaysInMonth(l.ngay.Year, l.ngay.Month)))
.Sum(l => l.go_lamsach_lk)
+ lg.Where(l => l.ngay == new DateTime(l.ngay.Year, l.ngay.Month, DateTime.DaysInMonth(l.ngay.Year, l.ngay.Month)))
.Sum(l => l.dd_go_lk)
+ lg.Where(l => l.ngay == new DateTime(l.ngay.Year, l.ngay.Month, DateTime.DaysInMonth(l.ngay.Year, l.ngay.Month)))
.Sum(l => l.cd_giam_lk)
) * 100 / (lg.Where(l => l.ngay == new DateTime(l.ngay.Year, l.ngay.Month, DateTime.DaysInMonth(l.ngay.Year, l.ngay.Month)))
.Sum(l => l.lapmoi_lk)
+ lg.Where(l => l.ngay == new DateTime(l.ngay.Year, l.ngay.Month, DateTime.DaysInMonth(l.ngay.Year, l.ngay.Month)))
.Sum(l => l.phuchoi_lk)
+ lg.Where(l => l.ngay == new DateTime(l.ngay.Year, l.ngay.Month, DateTime.DaysInMonth(l.ngay.Year, l.ngay.Month)))
.Sum(l => l.dd_lm_lk)
+ lg.Where(l => l.ngay == new DateTime(l.ngay.Year, l.ngay.Month, DateTime.DaysInMonth(l.ngay.Year, l.ngay.Month)))
.Sum(l => l.chuyendoi_lk)), 2),
unix_date = time.Year == lg.Key.Year && time.Month == lg.Key.Month && time.Day != 1 ? m_common.convertDayToUnix(time.Day + 1, lg.Key.Month, lg.Key.Year) : m_common.convertDayToUnix(1, lg.Key.Month, lg.Key.Year)
});
foreach (Unit ttvt in listTTVT)
{
List <dynamic> points = new List <dynamic>();
var tmp = list_tt.Where(item => item.donvi_id == ttvt.donvi_id);
foreach (var item in tmp)
{
points.Add(new List <dynamic> {
item.tyle_golm, item.unix_date
});
}
data.Add(new { target = ttvt.ten_dv, datapoints = points });
}
return(data);
}
public dynamic query([FromBody] RqGrafana rq)
{
DataRespond datarp = new DataRespond();
try
{
List <dynamic> response = m_detail_lapmoi.getNew(rq);
return(response);
}
catch (Exception e)
{
datarp.error = e;
}
return(datarp);
}
private dynamic getThoaiTraNLMLDate_oracle(RqGrafana rq)
{
List <ThoaitraNLMLModel_date> data = new List <ThoaitraNLMLModel_date>();
var date = m_common.convertToString(rq);
var startdate = new DateTime(Int32.Parse(date.Item1.Substring(6, 4)), Int32.Parse(date.Item1.Substring(3, 2)), Int32.Parse(date.Item1.Substring(0, 2)));
var enddate = new DateTime(Int32.Parse(date.Item2.Substring(6, 4)), Int32.Parse(date.Item2.Substring(3, 2)), Int32.Parse(date.Item2.Substring(0, 2)));
var listmonth = m_common.GetMonthsBetween(startdate, enddate);
foreach (var month in listmonth)
{
m_thoaiTraNLML.GetThoaitraNLML(month.ToString("yyyyMM"));
}
data = m_thoaiTraNLML.GetThoaitraNLMLDate(rq);
return(data);
}
private dynamic getThoaiTra_NLML_date(RqGrafana rq)
{
List <dynamic> data = new List <dynamic>();
int unit = (int)rq.scopedVars.unit.value;
if (unit == 0)
{
data = getThoaiTra_NLML_TTVT_date(rq);
}
else
{
data = getThoaiTra_NLML_DoiVT_date(rq);
}
return(data);
}
public dynamic query([FromBody] RqGrafana rq)
{
DataRespond datarp = new DataRespond();
try
{
List <dynamic> response = m_combohome.getComboHome(rq);
return(response);
}
catch (Exception e)
{
datarp.error = e;
}
return(datarp);
}