public static sysGlobalClass.CResutlWebMethod ApproveWithEditContent(int FormID)
{
PTTGC_EPIEntities env = new PTTGC_EPIEntities();
sysGlobalClass.CResutlWebMethod r = new sysGlobalClass.CResutlWebMethod();
int nOperationType = 0;
if (!UserAcc.UserExpired())
{
var gData = env.TEPI_Forms.FirstOrDefault(f => f.FormID == FormID);
if (gData != null)
{
r.Msg = SystemFunction.ReturnPath(gData.IDIndicator, gData.OperationTypeID, gData.FacilityID.ToString(), gData.sYear, "27");
r.Status = SystemFunction.process_Success;
}
else
{
r.Msg = "";
r.Status = SystemFunction.process_Failed;
}
}
else
{
r.Msg = "";
r.Status = SystemFunction.process_SessionExpired;
}
return(r);
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities epi = new PTTGC_EPIEntities();
List <Data_Perm> lstPerm = new List <Data_Perm>();
int nRole = UserAcc.GetObjUser().nRoleID;
int nUserID = UserAcc.GetObjUser().nUserID;
string sSearch = CommonFunction.ReplaceInjection(itemSearch.sSearch.Trims().ToLower());
int nFacID = 0;
if (nRole == 3 || nRole == 4)
{
#region L1, L2
string sCondition = "";
if (itemSearch.nGroupIndID.HasValue)
{
sCondition += " AND f.IDIndicator=" + itemSearch.nGroupIndID.Value;
}
if (!string.IsNullOrEmpty(sSearch))
{
sCondition += " AND LOWER(mF.Name) LIKE '%" + sSearch + "%'";
}
if (itemSearch.nStatus.HasValue)
{
sCondition += " AND d.nHistoryStatusID =" + itemSearch.nStatus;
}
if (!string.IsNullOrEmpty(itemSearch.sFacID))
{
sCondition += " AND p.IDFac = " + itemSearch.sFacID;
}
#region SQL L1, L2
string _Query = "SELECT" +
" p.IDFac AS nFacilities" +
" ,p.IDIndicator AS nIndicator" +
" ,p.L1 AS nL1" +
" ,p.L2 AS nL2 " +
" ,f.FormID AS nFormID" +
" ,f.sYear AS sYear" +
" ,d.nMonth AS nMonth" +
" ,d.nHistoryStatusID AS nStatus" +
" ,d.nReportID AS nReportID" +
" ,mF.Name AS sNameFacilities" +
" ,mI.Indicator AS sNameIndicator" +
" ,CASE " +
" WHEN d.nMonth = 1 THEN 'Jan.'" +
" WHEN d.nMonth = 2 THEN 'Feb.'" +
" WHEN d.nMonth = 3 THEN 'Mar.'" +
" WHEN d.nMonth = 4 THEN 'Apr.'" +
" WHEN d.nMonth = 5 THEN 'May'" +
" WHEN d.nMonth = 6 THEN 'Jun.'" +
" WHEN d.nMonth = 7 THEN 'Jul.'" +
" WHEN d.nMonth = 8 THEN 'Aug.'" +
" WHEN d.nMonth = 9 THEN 'Sep.'" +
" WHEN d.nMonth = 10 THEN 'Oct.'" +
" WHEN d.nMonth = 11 THEN 'Nov.'" +
" WHEN d.nMonth = 12 THEN 'Dec.'" +
" ELSE 'Dec.'" +
" END sMonth" +
" ,d.nHistoryStatusID AS nStatus" +
" ,sf.sStatusName AS sStatus" +
" ,d.dAction" +
" FROM mTWorkFlow p" +
" INNER JOIN TEPI_Forms f ON f.FacilityID = p.IDFac AND f.IDIndicator = p.IDIndicator" +
" INNER JOIN TEPI_Workflow d ON d.FormID = f.FormID" +
" INNER JOIN mTFacility mF ON mF.ID = p.IDFac AND mF.cDel = 'N' AND mF.nLevel = 2" +
" INNER JOIN mTIndicator mI ON mI.ID = p.IDIndicator" +
" INNER JOIN TStatus_Workflow sf ON sf.nStatustID = d.nHistoryStatusID" +
" WHERE p.cDel = 'N' AND (p.L1 = " + nUserID + " OR p.L2 = " + nUserID + ") AND f.sYear=" + itemSearch.nYear + sCondition + " ORDER BY d.dAction DESC "; //f.sYear,d.nMonth,mF.Name,mI.Indicator
#endregion
lstPerm = epi.Database.SqlQuery <Data_Perm>(_Query).ToList();
int[] lstStatusL1 = { 1 };
int[] lstStatusL2 = { 4, 2 };
int[] lstStatusL0 = { 0, 8, 9, 24, 18 };
if (lstPerm.Any())
{
if (nRole == 3)//L1
{
lstPerm = lstPerm.Where(w => w.nL1 == nUserID && lstStatusL1.Contains(w.nStatus)).OrderByDescending(o => o.dAction).ThenBy(o => o.sYear).ThenBy(o => o.nMonth).ThenBy(o => o.sNameFacilities).ThenBy(o => o.sNameIndicator).ToList();
}
else if (nRole == 4)//L2
{
lstPerm = lstPerm.Where(w => w.nL2 == nUserID && lstStatusL2.Contains(w.nStatus)).OrderByDescending(o => o.dAction).ThenBy(o => o.sYear).ThenBy(o => o.nMonth).ThenBy(o => o.sNameFacilities).ThenBy(o => o.sNameIndicator).ToList();
}
}
#endregion
}
else if (nRole == 2)
{
#region L0
Func <int, string> GetMonthName = (month) =>
{
string sMonthName = "";
if (month >= 1 && month <= 12)
{
sMonthName = new DateTime(DateTime.Now.Year, month, 1).ToString("MMM", new CultureInfo("en-US"));
}
else
{
sMonthName = month + "";
}
return(sMonthName);
};
string sCondition = "";
if (itemSearch.nGroupIndID.HasValue)
{
sCondition += " AND TUF.nGroupIndicatorID=" + itemSearch.nGroupIndID.Value;
}
if (!string.IsNullOrEmpty(itemSearch.sFacID))
{
nFacID = int.Parse(itemSearch.sFacID);
}
//if (itemSearch.nStatus.HasValue)
//{
// sCondition += " AND TEPIWF.nHistoryStatusID =" + itemSearch.nStatus;
//}
#region sql
string sqlL0 = @"SELECT TF.ID 'nFacilityID',TF.OperationtypeID,TF.Name 'sFacilityName',TIND.ID 'nIndID',TIND.Indicator 'sIndicator'
,TEPI.FormID,TEPIWF.nReportID,TEPIWF.nMonth,TEPIWF.nHistoryStatusID ,TSWF.sStatusName,TEPIWF.dAction
FROM mTUser_FacilityPermission TUF
INNER JOIN mTFacility TF ON TUF.nFacilityID=TF.ID AND TF.nLevel=2
INNER JOIN mTFacility TFH ON TF.nHeaderID=TFH.ID AND TFH.nLevel=1
INNER JOIN mTIndicator TIND ON TUF.nGroupIndicatorID=TIND.ID
LEFT JOIN TEPI_Forms TEPI ON TUF.nGroupIndicatorID=TEPI.IDIndicator AND TF.ID=TEPI.FacilityID AND TF.OperationTypeID=TEPI.OperationTypeID AND TEPI.sYear='" + itemSearch.nYear + @"'
LEFT JOIN TEPI_Workflow TEPIWF ON TEPI.FormID=TEPIWF.FormID
LEFT JOIN TStatus_Workflow TSWF ON ISNULL(TEPIWF.nHistoryStatusID,0)=TSWF.nStatustID
WHERE TUF.nUserID=" + nUserID + " AND TUF.nRoleID=" + nRole + @" AND TUF.nPermission=2 AND TF.cDel='N' AND TF.cActive='Y' AND TFH.cDel='N' AND TFH.cActive='Y'
AND (TEPIWF.nHistoryStatusID IS NULL OR TEPIWF.nHistoryStatusID IN (0,8,9,24,18)) " + sCondition + @"
order by TEPIWF.dAction DESC";
#endregion
DataTable dt = new DataTable();
dt = CommonFunction.Get_Data(SystemFunction.strConnect, sqlL0);
var Query = dt.AsEnumerable().Select(s => new
{
nFacilityID = s.Field <int>("nFacilityID"),
OperationtypeID = s.Field <int>("OperationtypeID"),
sFacilityName = s.Field <string>("sFacilityName"),
nIndID = s.Field <int>("nIndID"),
sIndicator = s.Field <string>("sIndicator"),
FormID = s.Field <int?>("FormID"),
nReportID = s.Field <int?>("nReportID"),
nMonth = s.Field <int?>("nMonth"),
nStatusID = s.Field <int?>("nHistoryStatusID"),
sStatusName = s.Field <string>("sStatusName"),
dAction = s.Field <DateTime?>("dAction"),
});
lstPerm = Query.Where(w => w.FormID.HasValue).Select(s => new Data_Perm
{
nFacilities = s.nFacilityID,
nIndicator = s.nIndID,
sYear = itemSearch.nYear + "",
nFormID = s.FormID ?? 0,
nMonth = s.nMonth ?? 0,
sMonth = GetMonthName(s.nMonth ?? 0),
nReportID = s.nReportID ?? 0,
sNameFacilities = s.sFacilityName,
sNameIndicator = s.sIndicator,
nStatus = s.nStatusID ?? 0,
sStatus = s.sStatusName,
nOperationtypeID = s.OperationtypeID,
dAction = s.dAction
}).ToList();
var QueryNoAction = Query.Where(w => !w.FormID.HasValue).ToList();
foreach (var item in QueryNoAction)
{
for (var i = 1; i <= 12; i++)
{
lstPerm.Add(new Data_Perm
{
nFacilities = item.nFacilityID,
nOperationtypeID = item.OperationtypeID,
nIndicator = item.nIndID,
sYear = itemSearch.nYear + "",
nFormID = item.FormID ?? 0,
nMonth = i,
sMonth = GetMonthName(i),
nReportID = 0,
sNameFacilities = item.sFacilityName,
sNameIndicator = item.sIndicator,
nStatus = -1,
sStatus = "No Action",
dAction = null
});
}
}
lstPerm = lstPerm.Where(w => (itemSearch.nStatus != null ? w.nStatus == itemSearch.nStatus : true) && (!string.IsNullOrEmpty(itemSearch.sFacID) ? w.nFacilities == nFacID : true)).OrderByDescending(o => o.dAction).ThenBy(o => o.sYear).ThenBy(o => o.nMonth).ThenBy(o => o.sNameFacilities).ThenBy(o => o.sNameIndicator).ToList();
#endregion
}
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstPerm = lstPerm.OrderBy(o => int.Parse(o.sYear)).ToList(); break;
case 2: lstPerm = lstPerm.OrderBy(o => o.nMonth).ToList(); break;
case 3: lstPerm = lstPerm.OrderBy(o => o.sNameFacilities).ToList(); break;
case 4: lstPerm = lstPerm.OrderBy(o => o.sNameIndicator).ToList(); break;
//case 5: lstPerm = lstPerm.OrderBy(o => o.nStatus).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstPerm = lstPerm.OrderByDescending(o => int.Parse(o.sYear)).ToList(); break;
case 2: lstPerm = lstPerm.OrderByDescending(o => o.nMonth).ToList(); break;
case 3: lstPerm = lstPerm.OrderByDescending(o => o.sNameFacilities).ToList(); break;
case 4: lstPerm = lstPerm.OrderByDescending(o => o.sNameIndicator).ToList(); break;
//case 5: lstPerm = lstPerm.OrderByDescending(o => o.nStatus).ToList(); break;
}
}
break;
}
#endregion
#region//Final Action >> Skip Take Data For Javasacript
sysGlobalClass.Pagination dataPage = new sysGlobalClass.Pagination();
dataPage = SystemFunction.GetPaginationSmall(SystemFunction.GetIntNullToZero(itemSearch.sPageSize), SystemFunction.GetIntNullToZero(itemSearch.sPageIndex), lstPerm.Count);
lstPerm = lstPerm.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
foreach (var item in lstPerm)
{
if (nRole == 3 || nRole == 4)//L1,L2
{
item.sBtn = "<a class='btn btn-primary' href='epi_mytask_view.aspx?strid=" +
HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nFormID.ToString())) + "&strlevel=" +
HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(nRole.ToString())) + "'>" +
"<i class='fa fa-search'></i> View Detail</a>";
}
else if (nRole == 2)//L0
{
string shref = SystemFunction.ReturnPath(item.nIndicator, item.nOperationtypeID ?? 0, item.nFacilities + "", item.sYear, "");
item.sBtn = "<a class='btn btn-warning btn-sm' title='Action' href='" + shref + "'><i class='fa fa-edit'></i></a>";
}
}
result.lstData = lstPerm;
result.nPageCount = dataPage.nPageCount;
result.nPageIndex = dataPage.nPageIndex;
result.sPageInfo = dataPage.sPageInfo;
result.sContentPageIndex = dataPage.sContentPageIndex;
result.nStartItemIndex = dataPage.nStartItemIndex;
#endregion
}
else
{
result.Status = SystemFunction.process_SessionExpired;
}
return(result);
}