public static sysGlobalClass.CResutlWebMethod getOperationTypePTT(string sFacilityPTT_ID)
{
sysGlobalClass.CResutlWebMethod result = new sysGlobalClass.CResutlWebMethod();
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
if (!UserAcc.UserExpired())
{
int nFacilityPTT_ID = SystemFunction.GetIntNullToZero(sFacilityPTT_ID);
var item = db.mTFacility.FirstOrDefault(w => w.ID == nFacilityPTT_ID);
if (item != null)
{
result.Content = item.OperationTypeID + "";
result.Status = SystemFunction.process_Success;
}
else
{
result.Msg = "Data operation type not found.";
result.Status = SystemFunction.process_Failed;
}
}
else
{
result.Status = SystemFunction.process_SessionExpired;
}
return(result);
}
protected void Page_Load(object sender, EventArgs e)
{
if (UserAcc.UserExpired())
{
SetBodyEventOnLoad(SystemFunction.PopupLogin());
}
else
{
if (!IsPostBack)
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
string str = Request.QueryString["strid"];
if (!string.IsNullOrEmpty(str))
{
int nFacID = SystemFunction.GetIntNullToZero(STCrypt.Decrypt(str));
hdfFacID.Value = str;
ltrCreate.Text = "<a class=\"btn btn-primary btn-sm btn-block\" href=\"admin_asset_update.aspx?strid=" + HttpUtility.UrlEncode(str) + "\"><i class=\"fa fa-plus\"></i> Create Sub-facility</a>";
var itemHeader = db.mTFacility.FirstOrDefault(w => w.ID == nFacID);
if (itemHeader != null)
{
var itemCompany = db.mTCompany.FirstOrDefault(w => w.ID == itemHeader.CompanyID);
ltrHeader.Text = "<a href='admin_company_lst.aspx' style='color:white'>Organization</a> > <a style='color:white' href='admin_facility_lst.aspx?strid=" + HttpUtility.UrlEncode(STCrypt.Encrypt(itemCompany.ID + "")) + "'> " + itemCompany.Name + "</a> > " + itemHeader.Name;//กำหนด Header
}
}
SystemFunction.BindDropdownPageSize(ddlPageSize, null);
}
}
}
private void SETDATA(string sID)
{
if (!string.IsNullOrEmpty(sID))
{
int nID = SystemFunction.GetIntNullToZero(sID);
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
DateTime now = DateTime.Now;
var Query = db.TContactUs.FirstOrDefault(w => w.cDel == "N" && w.nContactID == nID);
if (Query != null)
{
if (Query.cStatusAns == null)
{
Query.cStatusAns = "1";
Query.dUpdate = now;
db.SaveChanges();
}
if (Query.cStatusAns == "2")
{
txtDesc.Text = Query.sAnswer;
txtDesc.Attributes.Add("disabled", "true");
DivFileContactUS.Visible = false;
// lbUrlFileAdmin.Text = Query.sAnsPath + Query.sAnsFile;
if (!string.IsNullOrEmpty(Query.sAnsFile))
{
lbUrlFileAdmin.Text = "<a class='btn btn-primary'href=" + Query.sAnsPath + Query.sAnsSysFile + " target='_blank'><i class='fa fa-search'></i> " + Query.sAnsFile + "</a>";
}
else
{
lbUrlFileAdmin.Text = "-";
}
}
else
{
DivShowFileAdmin.Visible = false;
}
hidsStatus.Value = Query.cStatusAns;
lbName.Text = Query.sContactName;
lbEmail.Text = Query.sContactEmail;
lbSubject.Text = Query.sSubject;
lbTel.Text = Query.sContactTel;
lbUserDes.Text = Query.sDetail;
lbAddDate.Text = Query.dCreate.ToString();
//lbUrlFileUser.Text = Query.sContactPath + Query.sContactFile;
if (!string.IsNullOrEmpty(Query.sContactFile))
{
lbUrlFileUser.Text = "<a class='btn btn-primary'href=" + Query.sContactPath + Query.sContactSysFile + " target='_blank'><i class='fa fa-search'></i> " + Query.sContactFile + "</a>";
}
else
{
lbUrlFileUser.Text = "-";
}
lbStatus.Text = Query.cStatusAns == null ? "Wait" : Query.cStatusAns == "1" ? "Read" : "Success";
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
string str = Request.QueryString["strid"];
string strFacID = Request.QueryString["strFacID"];
int nFacilityID = 0;
bool IsNew = false;
if (!string.IsNullOrEmpty(str))
{
hdfReturnStr.Value += "&&strid=" + HttpUtility.UrlEncode(str);
int nComID = SystemFunction.GetIntNullToZero(STCrypt.Decrypt(str));
hdfComType.Value = nComID == 1 ? "PTT" : "GC";
hdfComID.Value = str;
var itemCompany = db.mTCompany.FirstOrDefault(w => w.ID == nComID && w.cDel == "N");
if (itemCompany != null)
{
if (!string.IsNullOrEmpty(strFacID))
{
IsNew = false;
nFacilityID = SystemFunction.GetIntNullToZero(STCrypt.Decrypt(strFacID));
hdfFacilityID.Value = strFacID;
var itemFacility = db.mTFacility.FirstOrDefault(w => w.cDel == "N" && w.ID == nFacilityID);
if (itemFacility != null)
{
ltrHeader.Text = "<a href='admin_company_lst.aspx' style='color:white'>Organization</a> > <a href='admin_facility_lst.aspx?strid=" + HttpUtility.UrlEncode(str) + "' style='color:white'>" + itemCompany.Name + "</a> > " + itemFacility.Name + " > Edit";//กำหนด Header กรณีเข้ามา EDIT
}
}
else
{
IsNew = true;
ltrHeader.Text = "<a href='admin_company_lst.aspx' style='color:white'>Organization</a> > <a href='admin_facility_lst.aspx?strid=" + HttpUtility.UrlEncode(str) + "' style='color:white'>" + itemCompany.Name + "</a> > Facility > Create";//กำหนด Header กรณีเข้ามา ADD
}
hdfIsNew.Value = IsNew ? "C" : "E";
}
setDDL_Cbl(IsNew, nComID);
setData(nFacilityID);
}
if (string.IsNullOrEmpty(str) && string.IsNullOrEmpty(strFacID))
{
SetBodyEventOnLoad(SystemFunction.DialogWarningRedirect(SystemFunction.Msg_HeadWarning, "Invalid Data", "admin_company_lst.aspx"));// กรณีเข้ามาด้วย link ที่ไม่มี Querystring
}
}
else
{
SetBodyEventOnLoad(SystemFunction.PopupLogin());
}
}
}
public static sysGlobalClass.CResutlWebMethod ForgetPassword(string sEmail, string sUsername)
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
sysGlobalClass.CResutlWebMethod result = new sysGlobalClass.CResutlWebMethod();
sEmail = sEmail.ToLower();
sUsername = sUsername.ToLower();
var lstUser = db.mTUser.Where(w => w.cActive == "Y" && w.cDel == "N" && w.Username.ToLower() == sUsername && w.Email.ToLower() == sEmail).ToList();
if (lstUser.Any())
{
var Data = lstUser.First();
string sTitle = "";
string sText = "";
string subject = "";
string message = "";
string sURL = "";
string sFoot = "";// "Should you have any questions about RD&T work process.";
// sURL = Applicationpath + "login_forget.aspx?str=" + SystemFunction.Encrypt_UrlEncrypt(nDocID + "");
subject = "Password Confirmation | " + SystemFunction.SystemName + "";
sText += "<p>Your password is " + STCrypt.Decrypt(Data.PasswordEncrypt) + "</p>";
string From = SystemFunction.GetSystemMail;
string To = Data.Email;
message = string.Format(GET_TemplateEmail(),
"Dear " + Data.Firstname + ' ' + Data.Lastname,
sText,
sURL,
sFoot,
"",
"");
Workflow.DataMail_log log = new Workflow.DataMail_log();
log = SystemFunction.SendMailAll(From, To, "", "", subject, message, "");
log.nDataID = SystemFunction.GetIntNullToZero(Data.ID + "");
log.sPageName = "login.aspx";
new Workflow().SaveLogMail(log);
if (log.bStatus)
{
result.Status = SystemFunction.process_Success;
}
}
else
{
result.Status = SystemFunction.process_Failed;
result.Msg = "data not found user";
}
return(result);
}
public static sysGlobalClass.TRetunrLoadData SaveData(sysGlobalClass.TRetunrLoadData lst, string sFacility, string sYear)
{
sysGlobalClass.TRetunrLoadData o = new sysGlobalClass.TRetunrLoadData();
Intensity_Function.IntensityClass ic = new Intensity_Function.IntensityClass();
ic.rt = lst;
ic.nIndicator = nIndicator;
ic.nOperationType = nOperationType;
ic.nFacility = SystemFunction.GetIntNullToZero(sFacility);
ic.sYear = sYear;
ic.sFolderInPathSave = sFolderInPathSave;
ic.lstMonth = lst.lstMonth;
o = Intensity_Function.SaveData(ic);
return(o);
}
public static sysGlobalClass.TRetunrLoadData CreateLinkExport(string sFacility, string sYear)
{
sysGlobalClass.TRetunrLoadData o = new sysGlobalClass.TRetunrLoadData();
Intensity_Function.IntensityClass ic = new Intensity_Function.IntensityClass();
ic.rt = o;
ic.nIndicator = nIndicator;
ic.nOperationType = nOperationType;
ic.nFacility = SystemFunction.GetIntNullToZero(sFacility);
ic.sYear = sYear;
ic.sFolderInPathSave = sFolderInPathSave;
ic.lstMonth = new List <int>();
o = Intensity_Function.CreateLinkExport(ic);
return(o);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
SystemFunction.BindDropdownPageSize(ddlPageSize, null);
SystemFunction.BindDropdownPageSize(ddlPageSizeGC, null);
if (!UserAcc.UserExpired())
{
string str = Request.QueryString["strid"];
if (!string.IsNullOrEmpty(str))
{
if (SystemFunction.GetIntNullToZero(STCrypt.Decrypt(str)) != 1)
{
SetBodyEventOnLoad("$('div[id$=divContentGC]').show();$('div[id$=divContent]').hide()");
}
else
{
SetBodyEventOnLoad("$('div[id$=divContentGC]').hide();$('div[id$=divContent]').show()");
}
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
int nComID = SystemFunction.GetIntNullToZero(STCrypt.Decrypt(str));
hdfComID.Value = STCrypt.Encrypt(nComID + "");
var itemCompany = db.mTCompany.FirstOrDefault(w => w.ID == nComID && w.cDel == "N");
if (itemCompany != null)
{
ltrHeader.Text = "<a href='admin_company_lst.aspx' style='color:white'>Organization</a> > " + itemCompany.Name;//กำหนด Header
}
ltrCreateGC.Text = "<a class=\"btn btn-primary btn-sm btn-block\" href=\"admin_facility_update.aspx?strid=" + HttpUtility.UrlEncode(STCrypt.Encrypt(nComID + "")) + "\"><i class=\"fa fa-plus\"></i> Create Facility</a>";
ltrCreate.Text = "<a class=\"btn btn-primary btn-sm btn-block\" href=\"admin_facility_update.aspx?strid=" + HttpUtility.UrlEncode(STCrypt.Encrypt(nComID + "")) + "\"><i class=\"fa fa-plus\"></i> Create Facility</a>";
}
else
{
SetBodyEventOnLoad(SystemFunction.DialogWarningRedirect(SystemFunction.Msg_HeadWarning, "Invalid Data", "admin_company_lst.aspx"));// กรณีเข้ามาด้วย link ที่ไม่มี Querystring
}
}
else
{
SetBodyEventOnLoad(SystemFunction.PopupLogin());
}
}
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
var lstHistory = db.TViewMenu_History.ToList();
DateTime dStartValue = DateTime.ParseExact(itemSearch.sStartDate, "dd/MM/yyyy", new CultureInfo("en-US"));
DateTime dEndValue = DateTime.ParseExact(itemSearch.sEndDate, "dd/MM/yyyy", new CultureInfo("en-US"));
var lstMenu = db.TMenu.Where(w => w.cActive == "Y").ToList();
var lstUser = db.mTUser.ToList();
sslstData = new List <TDataTable>();
lstHistory = lstHistory.Where(w => (w.dAction.Value.Date >= dStartValue.Date && w.dAction.Value.Date <= dEndValue)).ToList();
var lstData = (from a in lstHistory
from b in lstMenu.Where(w => w.nMenuID == a.nMenuID)
from c in lstUser.Where(w => w.ID == a.nUserID)
select new TDataTable
{
nLogID = a.nLogID,
nMenuID = a.nMenuID,
nUserID = a.nUserID,
dActionDate = a.dAction,
Firstname = c.Firstname,
Username = c.Username,
Lastname = c.Lastname,
sMenu = b.sMenuName,
nLevel = b.nLevel,
nHead = b.sMenuHeadID
}).ToList();
lstData.ForEach(f =>
{
f.sAction = SystemFunction.CovertDateEn2Th(f.dActionDate.Value + "", "EN4");
if (f.nLevel == 2)
{
var mu = lstMenu.FirstOrDefault(w => w.nMenuID == f.nHead);
if (mu != null)
{
f.sMenu = mu.sMenuName;
var mu2 = lstMenu.FirstOrDefault(w => w.nMenuID == mu.sMenuHeadID);
if (mu != null)
{
f.sMenuHead = mu2.sMenuName;
}
}
}
else if (f.nLevel == 1)
{
var mu = lstMenu.FirstOrDefault(w => w.nMenuID == f.nHead);
if (mu != null)
{
f.sMenuHead = mu.sMenuName;
}
}
else
{
f.sMenuHead = f.sMenu;
f.sMenu = "";
}
});
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.Username).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.Firstname).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.sMenuHead).ToList(); break;
case 4: lstData = lstData.OrderBy(o => o.sMenu).ToList(); break;
case 5: lstData = lstData.OrderBy(o => o.dActionDate).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.Username).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.Firstname).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.sMenuHead).ToList(); break;
case 4: lstData = lstData.OrderByDescending(o => o.sMenu).ToList(); break;
case 5: lstData = lstData.OrderByDescending(o => o.dActionDate).ToList(); break;
}
}
break;
}
#endregion
sslstData = lstData;
#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), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
result.lstData = lstData;
result.nPageCount = dataPage.nPageCount;
result.nPageIndex = dataPage.nPageIndex;
result.sPageInfo = dataPage.sPageInfo;
result.sContentPageIndex = dataPage.sContentPageIndex;
result.nStartItemIndex = dataPage.nStartItemIndex;
#endregion
return(result);
}
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);
}
private void Export_EXCEL(string sFacility, string sYear)
{
int nFacility = SystemFunction.GetIntNullToZero(sFacility);
string[] alphabet = { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z" };
MemoryStream fsExport = new MemoryStream();
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
string[] Arr_Column = { "Indicator", "Unit", "Target", "Q1 : Jan", "Q1 : Feb", "Q1 : Mar", "Q2 : Apr", "Q2 : May", "Q2 : Jun", "Q3 : Jul", "Q3 : Aug", "Q3 : Sep", "Q4 : Oct", "Q4 : Nov", "Q4 : Dec", "Remark" };
double[] Arr_ColumnWidth = { 50, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 50 };
using (XLWorkbook wb = new XLWorkbook())
{
#region LG
var ws = wb.Worksheets.Add("EPIFROM_Intensity");
//สร้างหัวข้อ
string sCol = alphabet[Arr_Column.Length - 1].ToUpper();
#region CreatHeadReport
ws.Cell("A1").Value = "Indicator : Intensity Denominator ";
ws.Cell("A1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Cell("A1").Style.Font.FontName = "Cordia New";
ws.Cell("A1").Style.Font.FontSize = 14.00;
ws.Cell("A2").Value = "Operation Type : Refinery";
ws.Cell("A2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Cell("A2").Style.Font.FontName = "Cordia New";
ws.Cell("A2").Style.Font.FontSize = 14.00;
var Facility = db.mTFacility.FirstOrDefault(w => w.cActive == "Y" && w.cDel == "N" && w.nLevel == 2 && w.ID + "" == sFacility);
if (Facility != null)
{
sFacility = Facility.Name;
}
ws.Cell("A3").Value = "Sub-facility : " + sFacility;
ws.Cell("A3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Cell("A3").Style.Font.FontName = "Cordia New";
ws.Cell("A3").Style.Font.FontSize = 14.00;
ws.Cell("A4").Value = "Year : " + sYear;
ws.Cell("A4").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
ws.Cell("A4").Style.Font.FontName = "Cordia New";
ws.Cell("A4").Style.Font.FontSize = 14.00;
#endregion
//สร้างหัวตาราง
int nRow = 5;
var RowsHead = ws;
string sCC = "#dbea97";
int nRowna = 1;
#region สร้างตาราง
var lst = ssTRetunrLoadData_1;
lst.lstIn.ForEach(f =>
{
sCC = "#dbea97";
if (f.ProductID != 89 && f.ProductID != 90)
{
for (int i = 0; i < Arr_Column.Length; i++)
{
sCol = alphabet[i].ToUpper();
GetCellHead(RowsHead, Arr_ColumnWidth[i], sCol, nRow + "", Arr_Column[i] + "", sCC);
}
}
if (f.ProductID != 89 && f.ProductID != 90)
{
nRow++;
}
string sText = "";
XLAlignmentHorizontalValues align = XLAlignmentHorizontalValues.Center;
for (int j = 0; j < Arr_Column.Length; j++)
{
sCol = alphabet[j].ToUpper();
sCC = "#dbea97";
if (f.cTotalAll != "Y")
{
sCC = "#ffedc4";
}
#region CaseRow
if (f.ProductID != 89 && f.ProductID != 90)
{
sText = SetText(j, f);
}
else
{
sysGlobalClass.T_TIntensity_Other ftemp = new sysGlobalClass.T_TIntensity_Other();
ftemp.sIndicator = f.ProductName;
ftemp.sUnit = f.sUnit;
ftemp.sTarget = f.Target;
ftemp.M1 = f.M1;
ftemp.M2 = f.M2;
ftemp.M3 = f.M3;
ftemp.M4 = f.M4;
ftemp.M5 = f.M5;
ftemp.M6 = f.M6;
ftemp.M7 = f.M7;
ftemp.M8 = f.M8;
ftemp.M9 = f.M9;
ftemp.M10 = f.M10;
ftemp.M11 = f.M11;
ftemp.M12 = f.M12;
sCC = "#FFFFFF";
sText = SetTextDetail(j, ftemp);
}
align = SetAlign(j);
#endregion
GetCellText(RowsHead, Arr_ColumnWidth[j], sCol, nRow + "", sText, align, j + "", sCC);
}
nRow++;
f.lstarrDetail.ForEach(f2 =>
{
for (int k = 0; k < Arr_Column.Length; k++)
{
sCC = "#FFFFFF";
sCol = alphabet[k].ToUpper();
sText = SetTextDetail(k, f2);
align = SetAlign(k);
GetCellText(RowsHead, Arr_ColumnWidth[k], sCol, nRow + "", sText, align, k + "", sCC);
}
nRow++;
});
});
#endregion
#endregion
#region Deviate
var dv = wb.Worksheets.Add("Deviate");
//สร้างหัวข้อ
sCol = alphabet[Arr_Column.Length - 1].ToUpper();
#region CreatHeadReport
dv.Cell("A1").Value = "Indicator : Intensity Denominator ";
dv.Cell("A1").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
dv.Cell("A1").Style.Font.FontName = "Cordia New";
dv.Cell("A1").Style.Font.FontSize = 14.00;
dv.Cell("A2").Value = "Operation Type : Refinery";
dv.Cell("A2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
dv.Cell("A2").Style.Font.FontName = "Cordia New";
dv.Cell("A2").Style.Font.FontSize = 14.00;
dv.Cell("A3").Value = "Sub-facility : " + sFacility;
dv.Cell("A3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
dv.Cell("A3").Style.Font.FontName = "Cordia New";
dv.Cell("A3").Style.Font.FontSize = 14.00;
dv.Cell("A4").Value = "Year : " + sYear;
dv.Cell("A4").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
dv.Cell("A4").Style.Font.FontName = "Cordia New";
dv.Cell("A4").Style.Font.FontSize = 14.00;
#endregion
//สร้างหัวตาราง
nRow = 5;
RowsHead = dv;
sCC = "#9cb726";
nRowna = 1;
#region สร้างตาราง
var lstdv = SystemFunction.GetDeviate(nIndicator, nOperationType, nFacility, sYear);
GetCellHead(RowsHead, 10, "A", nRow + "", "No." + "", sCC);
GetCellHead(RowsHead, 20, "B", nRow + "", "Month" + "", sCC);
GetCellHead(RowsHead, 20, "C", nRow + "", "Remark" + "", sCC);
GetCellHead(RowsHead, 20, "D", nRow + "", "Action By" + "", sCC);
GetCellHead(RowsHead, 20, "E", nRow + "", "Date" + "", sCC);
nRow++;
sCC = "#ffffff";
lstdv.ForEach(f =>
{
XLAlignmentHorizontalValues align = XLAlignmentHorizontalValues.Center;
GetCellText(RowsHead, 10, "A", nRow + "", nRowna + "", align, 0 + "", sCC);
GetCellText(RowsHead, 20, "B", nRow + "", f.sMonth + "", align, 0 + "", sCC);
GetCellText(RowsHead, 20, "C", nRow + "", f.sRemark + "", XLAlignmentHorizontalValues.Left, 0 + "", sCC);
GetCellText(RowsHead, 20, "D", nRow + "", f.sActionBy + "", align, 0 + "", sCC);
GetCellText(RowsHead, 20, "E", nRow + "", f.sDate + "", align, 0 + "", sCC);
nRow++;
nRowna++;
});
#endregion
#endregion
wb.SaveAs(fsExport);
}
string saveAsFileName = "Input_Intensity_" + sFacility + "_" + DateTime.Now.ToString("ddMMyyHHmmss", new CultureInfo("en-US")) + ".xlsx";
fsExport.Position = 0;
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=" + saveAsFileName);
Response.ContentType = "application/vnd.ms-excel";
try
{
Response.BinaryWrite(fsExport.ToArray());
}
catch (Exception ee)
{ }
ScriptManager.RegisterStartupScript(this, this.GetType(), "SessionExpire", "UnblockUI();", true);
Response.End();
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <TDataTable> lstData = new List <TDataTable>();
string sCon = "";
string sSearch = itemSearch.sSearch.Trims().ToLower();
string sStatus = itemSearch.sStatus.Trims().ToLower();
if (!string.IsNullOrEmpty(sSearch))
{
sCon += @" AND sName LIKE '%" + sSearch + "%' ";
}
if (!string.IsNullOrEmpty(sStatus))
{
sCon += @" AND (LOWER(cActive) = '" + sStatus + @"') ";
}
var TB_Effluent_Product = db.TEffluent_OtherProduct_Point.ToList(); //IsUse
string _SQL = @" select nUnitID
,sName
,CASE WHEN cActive = 'Y' THEN 'Active' ELSE 'Inactive' END as sStatus
,dUpdate
from TM_Effluent_Unit
where cDel = 'N' " + sCon + @"
ORDER BY dUpdate DESC";
DataTable dt = CommonFunction.Get_Data(SystemFunction.strConnect, _SQL);
lstData = CommonFunction.ConvertDatableToList <TDataTable>(dt).ToList();
foreach (var item in lstData)
{
item.sUpdate = item.dUpdate.DateString();
item.sLink = "<a class='btn btn-warning' href='admin_master_effluent_otherunit_update.aspx?str=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nUnitID.ToString())) + "'><i class='fa fa-edit'></i> Edit</a>";
item.nIsUse = TB_Effluent_Product.Any(a => a.nUnitID == item.nUnitID) ? TB_Effluent_Product.Where(a => a.nUnitID == item.nUnitID).Count() : 0;
}
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.sName).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.sStatus).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.sUpdate).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.sName).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.sStatus).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.sUpdate).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), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
var lstDataFac = db.mTFacility.Where(w => w.cDel == "N").ToList();
result.lstData = lstData;
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);
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <TDataTable> lstData = new List <TDataTable>();
string sCon = "";
string sSearch = itemSearch.sSearch.Trims().ToLower();
string sStatus = itemSearch.sStatus.Trims().ToLower();
if (!string.IsNullOrEmpty(sSearch))
{
sCon += @" AND (LOWER(w.sCode) LIKE '%" + sSearch + "%' OR LOWER(w.sName) LIKE '%" + sSearch + "%' OR LOWER(td.sName) LIKE '%" + sSearch + "%') ";
}
if (!string.IsNullOrEmpty(sStatus))
{
sCon += @" AND (LOWER(w.cActive) = '" + sStatus + @"') ";
}
var Data_type = db.TData_Type.AsEnumerable().Where(w => w.cActive == "Y").ToList(); //GetDataTYPE_Name
var TB_Waste_Product = db.TWaste_Product_data.ToList(); //IsUse
string _SQL = @" SELECT w.ID as nWasteID
,w.sCode as sCode
,w.sName as sName
,w.nTypeID as nTypeID
,w.dUpdate
,td.sName as sTypeName
,CASE WHEN w.cActive = 'Y' THEN 'Active' ELSE 'Inactive' END as sStatus
FROM TM_WasteDisposal w
LEFT JOIN TData_Type td ON w.nTypeID = td.nID
WHERE w.cDel = 'N' " + sCon + @"
ORDER BY w.dUpdate DESC ";
DataTable dt = CommonFunction.Get_Data(SystemFunction.strConnect, _SQL);
lstData = CommonFunction.ConvertDatableToList <TDataTable>(dt).ToList();
//lstData = db.TM_WasteDisposal.Where(w => w.cDel == "N" && (!string.IsNullOrEmpty(sSearch) ? w.sName.ToLower().Contains(sSearch) || w.sCode.ToLower().Contains(sSearch) : true) && (!string.IsNullOrEmpty(sStatus) ? w.cActive.ToLower().Contains(sStatus) : true)).Select(s => new TDataTable
//{
// nWasteID = s.ID,
// sCode = s.sCode,
// sName = s.sName,
// nTypeID = s.nTypeID,
// sStatus = s.cActive == "Y" ? "Active" : "Inactive",
// dUpdate = s.dUpdate
//}).OrderByDescending(o => o.dUpdate).ToList();
foreach (var item in lstData)
{
item.sUpdate = item.dUpdate.DateString();
item.sLink = "<a class='btn btn-warning' href='admin_master_waste_update.aspx?str=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nWasteID.ToString())) + "'><i class='fa fa-edit'></i> Edit</a>";
//item.sTypeName = Data_type.Any(a => a.nID == item.nTypeID) ? Data_type.First(a => a.nID == item.nTypeID).sName : "";
item.nIsUse = TB_Waste_Product.Any(a => a.nDisposalID == item.nWasteID) ? TB_Waste_Product.Where(a => a.nDisposalID == item.nWasteID).Count() : 0;
}
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.sTypeName).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.sName).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.sStatus).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.sTypeName).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.sName).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.sStatus).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), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
var lstDataFac = db.mTFacility.Where(w => w.cDel == "N").ToList();
result.lstData = lstData;
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);
}
public static CReturnData LoadData(CParam param)
{
CReturnData result = new CReturnData();
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
if (!UserAcc.UserExpired())
{
result.incData = new cDataInc();
result.lstComplaint = new List <cComplaint>();
result.lstStatus = new List <sysGlobalClass.T_TEPI_Workflow>();
int nIndID = SystemFunction.GetIntNullToZero(param.sIndID);
int nOprtID = SystemFunction.GetIntNullToZero(param.sOprtID);
int nFacID = SystemFunction.GetIntNullToZero(param.sFacID);
result.hdfPRMS = SystemFunction.GetPermission_EPI_FROMS(nIndID, nFacID) + "";
string sYear = param.sYear;
var itemPrdInc = db.mTProductIndicator.FirstOrDefault(w => w.IDIndicator == 1);
result.incData.ProductID = itemPrdInc.ProductID;
result.incData.sUnit = itemPrdInc.sUnit;
result.incData.sProductName = itemPrdInc.ProductName;
result.incData.nUnitID = 66;
result.incData.sTarget = "";
#region EPI_FORM
var itemEPI_FORM = db.TEPI_Forms.FirstOrDefault(w => w.sYear == sYear && w.IDIndicator == nIndID && w.OperationTypeID == nOprtID && w.FacilityID == nFacID);
if (itemEPI_FORM != null)
{
var itemDataComplaint = db.TComplaint_Product.FirstOrDefault(w => w.FormID == itemEPI_FORM.FormID);
#region Data Indicator
if (itemDataComplaint != null)
{
var itemUnit = db.mTUnit.FirstOrDefault(w => w.UnitID == itemDataComplaint.UnitID);
result.incData.nUnitID = itemDataComplaint.UnitID;
result.incData.sUnit = itemUnit != null ? itemUnit.UnitName : result.incData.sUnit;
result.incData.sTarget = itemDataComplaint.Target;
result.incData.M1 = itemDataComplaint.M1;
result.incData.M2 = itemDataComplaint.M2;
result.incData.M3 = itemDataComplaint.M3;
result.incData.M4 = itemDataComplaint.M4;
result.incData.M5 = itemDataComplaint.M5;
result.incData.M6 = itemDataComplaint.M6;
result.incData.M7 = itemDataComplaint.M7;
result.incData.M8 = itemDataComplaint.M8;
result.incData.M9 = itemDataComplaint.M9;
result.incData.M10 = itemDataComplaint.M10;
result.incData.M11 = itemDataComplaint.M11;
result.incData.M12 = itemDataComplaint.M12;
result.incData.IsCheckM1 = itemDataComplaint.IsCheckM1;
result.incData.IsCheckM2 = itemDataComplaint.IsCheckM2;
result.incData.IsCheckM3 = itemDataComplaint.IsCheckM3;
result.incData.IsCheckM4 = itemDataComplaint.IsCheckM4;
result.incData.IsCheckM5 = itemDataComplaint.IsCheckM5;
result.incData.IsCheckM6 = itemDataComplaint.IsCheckM6;
result.incData.IsCheckM7 = itemDataComplaint.IsCheckM7;
result.incData.IsCheckM8 = itemDataComplaint.IsCheckM8;
result.incData.IsCheckM9 = itemDataComplaint.IsCheckM9;
result.incData.IsCheckM10 = itemDataComplaint.IsCheckM10;
result.incData.IsCheckM11 = itemDataComplaint.IsCheckM11;
result.incData.IsCheckM12 = itemDataComplaint.IsCheckM12;
}
#endregion
#region Data Complaint
result.lstComplaint = db.TComplaint.Where(w => w.FormID == itemEPI_FORM.FormID).Select(s => new cComplaint
{
nComplaintID = s.nComplaintID,
nComplaintTypeID = s.nComplaintTypeID,
ComplaintDate = s.ComplaintDate.HasValue ? s.ComplaintDate.Value : (DateTime?)null,
sIssueBy = s.sIssueBy,
sSubject = s.sSubject,
nComplaintByID = s.nComplaintByID,
sComplaintByOther = s.sComplaintByOther,
sDetail = s.sDetail,
sCorrectiveAction = s.sCorrectiveAction,
sStatus = s.sStatus,
nMonth = s.ComplaintDate.HasValue ? s.ComplaintDate.Value.Month : 0,
IsDel = false,
IsNew = false,
IsSubmited = true,
IsShow = true,
}).ToList();
result.lstComplaint.ForEach(f =>
{
f.sIssueDate = f.ComplaintDate.HasValue ? f.ComplaintDate.Value.ToString("dd/MM/yyyy") : "";
f.ComplaintDate = null;
});
var lstFileComplaint = db.TComplaint_File.Where(w => w.FormID == itemEPI_FORM.FormID).ToList();
var lstImpact = db.TComplaint_Impact.Where(w => w.FormID == itemEPI_FORM.FormID).ToList();
foreach (var itemComplaint in result.lstComplaint)
{
itemComplaint.lstImpact = new List <TComplaint_Impact>();
itemComplaint.lstImpact = lstImpact.Where(w => w.nComplaintID == itemComplaint.nComplaintID).Select(s => new TComplaint_Impact
{
nImpactTypeID = s.nImpactTypeID,
sOther = s.sOther
}).ToList();
itemComplaint.lstFile = new List <sysGlobalClass.FuncFileUpload.ItemData>();
itemComplaint.lstFile = lstFileComplaint.Where(w => w.nComplaintID == itemComplaint.nComplaintID).Select(s => new sysGlobalClass.FuncFileUpload.ItemData
{
ID = s.nFileID,
FileName = s.sFileName,
SaveToFileName = s.sSysFileName,
SaveToPath = s.sPath,
url = s.sPath + s.sSysFileName,
IsNewFile = false,
IsCompleted = true,
sDelete = "N",
sDescription = s.sDescription,
}).ToList();
}
#endregion
#region Status Month
result.lstStatus = db.TEPI_Workflow.Where(w => w.FormID == itemEPI_FORM.FormID).Select(s => new sysGlobalClass.T_TEPI_Workflow {
nMonth = s.nMonth, nStatusID = s.nStatusID
}).ToList();
#endregion
}
else
{
for (int i = 1; i <= 12; i++)
{
result.lstStatus.Add(new sysGlobalClass.T_TEPI_Workflow {
nMonth = i, nStatusID = 0
});
}
result.incData.sTarget = "";
result.incData.M1 = "";
result.incData.M2 = "";
result.incData.M3 = "";
result.incData.M4 = "";
result.incData.M5 = "";
result.incData.M6 = "";
result.incData.M7 = "";
result.incData.M8 = "";
result.incData.M9 = "";
result.incData.M10 = "";
result.incData.M11 = "";
result.incData.M12 = "";
result.incData.IsCheckM1 = "N";
result.incData.IsCheckM2 = "N";
result.incData.IsCheckM3 = "N";
result.incData.IsCheckM4 = "N";
result.incData.IsCheckM5 = "N";
result.incData.IsCheckM6 = "N";
result.incData.IsCheckM7 = "N";
result.incData.IsCheckM8 = "N";
result.incData.IsCheckM9 = "N";
result.incData.IsCheckM10 = "N";
result.incData.IsCheckM11 = "N";
result.incData.IsCheckM12 = "N";
}
#endregion
result.Status = SystemFunction.process_Success;
}
else
{
result.Status = SystemFunction.process_SessionExpired;
}
return(result);
}
public static sysGlobalClass.CResutlWebMethod saveToDB(DataValue dataValue)
{
sysGlobalClass.CResutlWebMethod result = new sysGlobalClass.CResutlWebMethod();
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
if (!UserAcc.UserExpired())
{
dataValue.sComID = STCrypt.Decrypt(dataValue.sComID);
dataValue.sFacilityID = !string.IsNullOrEmpty(dataValue.sFacilityID) ? STCrypt.Decrypt(dataValue.sFacilityID) : "";
Func <string, int, int?, int, bool> CheckDuplicateName = (name, comID, facID, nLevel) =>
{
bool Isdup = false;
var q = db.mTFacility.Where(w => w.CompanyID == comID && w.nLevel == nLevel && (facID.HasValue ? w.ID != facID : true) && w.Name == name && w.cDel == "N");
Isdup = q.Any();
return(Isdup);
};
#region Add / Update
if (CheckDuplicateName(dataValue.sFacilityName, SystemFunction.GetIntNullToZero(dataValue.sComID), dataValue.IsNew ? (int?)null : SystemFunction.GetIntNullToZero(dataValue.sFacilityID), (dataValue.sComType == "GC" ? 1 : 0)))
{
result.Status = SystemFunction.process_Failed;
result.Msg = "Duplicate Facility name";
return(result);
}
int nFacility_PTT = SystemFunction.GetIntNullToZero(dataValue.sFacilityPTT_ID);
int nFacilityID = SystemFunction.GetIntNullToZero(dataValue.sFacilityID);
if (dataValue.sComType == "GC" && nFacility_PTT != 0)
{
var query = db.mTFacility.Where(w => w.cDel == "N" && w.nLevel == 1 && w.nHeaderID == nFacility_PTT && w.ID != nFacilityID);
if (query.Any())
{
result.Status = SystemFunction.process_Failed;
result.Msg = "Cannot reference PTT facility, because exist used.";
return(result);
}
}
int nComID = SystemFunction.GetIntNullToZero(dataValue.sComID);
var itemFacility = db.mTFacility.FirstOrDefault(w => w.ID == nFacilityID && w.CompanyID == nComID);
int nMaxID = db.mTFacility.Any() ? db.mTFacility.Max(m => m.ID) + 1 : 1;
if (dataValue.IsNew)
{
itemFacility = new mTFacility();
itemFacility.ID = nMaxID;
itemFacility.cDel = "N";
itemFacility.CreateID = UserAcc.GetObjUser().nUserID;
itemFacility.dCreate = DateTime.Now;
}
itemFacility.sMappingCodePTT = (dataValue.sMapPTTCode + "").Trim();
itemFacility.Name = dataValue.sFacilityName;
itemFacility.nLevel = dataValue.sComType == "GC" ? 1 : 0;
itemFacility.nHeaderID = dataValue.sComType == "GC" ? (nFacility_PTT == 0 ? (int?)null : nFacility_PTT) : (int?)null;
itemFacility.sRelation = dataValue.sComType == "GC" ? (nFacility_PTT == 0 ? itemFacility.ID + "" : nFacility_PTT + "-" + itemFacility.ID) : itemFacility.ID + "";
//itemFacility.sRelation = dataValue.sComType == "GC" ? nFacility_PTT + "-" + itemFacility.ID : itemFacility.ID + "";
itemFacility.OperationTypeID = SystemFunction.GetIntNullToZero(dataValue.sOperationTypePTT_ID);
itemFacility.CompanyID = SystemFunction.GetIntNullToZero(dataValue.sComID);
itemFacility.Description = dataValue.sDescription;
itemFacility.cActive = dataValue.sActive;
itemFacility.sRemark = dataValue.sActive == "Y" ? null : dataValue.sRemark;
itemFacility.UpdateID = dataValue.IsNew ? (int?)null : UserAcc.GetObjUser().nUserID;
itemFacility.dUpdate = DateTime.Now;
if (dataValue.IsNew)
{
db.mTFacility.Add(itemFacility);
}
if (dataValue.sComType == "GC")
{
//db.mTFacility_Operationtype.RemoveRange(db.mTFacility_Operationtype.Where(w => w.nFacID == itemFacility.ID));
//foreach (var item in dataValue.lstOperationTypeGC_ID)
//{
// mTFacility_Operationtype oprt = new mTFacility_Operationtype();
// oprt.nFacID = itemFacility.ID;
// oprt.nOperationtypeID = SystemFunction.GetIntNullToZero(item);
// db.mTFacility_Operationtype.Add(oprt);
//}
}
else
{
if (!dataValue.IsNew)
{
db.mTFacility.Where(w => w.nHeaderID == itemFacility.ID && w.cDel == "N").ToList().ForEach(f =>
{
f.OperationTypeID = itemFacility.OperationTypeID;
});
}
}
db.SaveChanges();
result.Status = SystemFunction.process_Success;
#endregion
}
else
{
result.Status = SystemFunction.process_SessionExpired;
}
return(result);
}
public static TRetunrLoadData LoadData_Indicator(CSearch itemSearch)
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
TRetunrLoadData result = new TRetunrLoadData();
List <TDataIndicator> lstData = new List <TDataIndicator>();
if (!UserAcc.UserExpired())
{
lstData = db.mTIndicator.Select(s => new TDataIndicator
{
nID = s.ID,
sIndicator = s.Indicator,
nOrder = s.nOrder,
sPermission = "1",
}).OrderBy(o => o.nOrder).ToList();
//#region//SORT
//int? nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
//switch ((itemSearch.sOrderBy + "").ToLower())
//{
// case SystemFunction.ASC:
// {
// switch (nSortCol)
// {
// case 1: lstData = lstData.OrderBy(o => o.sCompName).ToList(); break;
// case 2: lstData = lstData.OrderBy(o => o.sStatus).ToList(); break;
// case 3: lstData = lstData.OrderBy(o => o.dUpdate).ToList(); break;
// case 4: lstData = lstData.OrderBy(o => o.nCountFacility).ToList(); break;
// }
// }
// break;
// case SystemFunction.DESC:
// {
// switch (nSortCol)
// {
// case 1: lstData = lstData.OrderByDescending(o => o.sCompName).ToList(); break;
// case 2: lstData = lstData.OrderByDescending(o => o.sStatus).ToList(); break;
// case 3: lstData = lstData.OrderByDescending(o => o.dUpdate).ToList(); break;
// case 4: lstData = lstData.OrderByDescending(o => o.nCountFacility).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), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
//foreach (var item in lstData)
//{
// item.sUpdate = item.dUpdate.DateString();
// item.sLink = "<a class='btn btn-warning' href='admin_company_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nCompID.ToString())) + "'><i class='fa fa-edit'></i> Edit</a>";
//}
result.lstDataIndicator = lstData;
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);
}
public static sysGlobalClass.CResutlWebMethod SaveToDB(CSave_Data item, List <dataFileContactUs> objFile)
{
sysGlobalClass.CResutlWebMethod result = new sysGlobalClass.CResutlWebMethod();
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
DateTime now = DateTime.Now;
if (UserAcc.UserExpired())
{
result.Status = SystemFunction.process_SessionExpired;
}
else
{
UserAcc ua = UserAcc.GetObjUser();
try
{
string sPathSend = "";
int nID = db.TContactUs.Any() ? db.TContactUs.Max(m => m.nContactID) + 1 : 1;
TContactUs t = new TContactUs();
t.nContactID = nID;
t.sContactName = item.sName;
t.sContactEmail = item.sEmail;
t.sContactTel = item.sTel;
t.sSubject = item.sSubject;
t.sDetail = item.sDec;
t.dCreate = now;
t.dUpdate = now;
t.cDel = "N";
if (objFile.Count > 0 && objFile != null)
{
string sPathSave = string.Format(sFolderInPathSave, nID);
SystemFunction.CreateDirectory(sPathSave);
//string sPathSave = string.Format(sFolderInPathSave, nID);
//SystemFunction.CreateDirectory(sPathSave);
var f = objFile.First();
string sSystemFileName = nID + "_" + "-" + DateTime.Now.ToString("ddMMyyyyHHmmss") + "." + SystemFunction.GetFileNameFromFileupload(f.sFileName, ""); //SystemFunction2.GetFileType(item.SaveToFileName);
SystemFunction.UpFile2Server(f.sPath, sPathSave, f.sSysFileName, sSystemFileName);
t.sContactFile = f.sFileName;
t.sContactSysFile = sSystemFileName;
t.sContactPath = sPathSave;
sPathSend = HttpContext.Current.Server.MapPath("./") + sPathSave + sSystemFileName;
}
//t.cStatusAns = "NULL";
db.TContactUs.Add(t);
var Ispass = db.SaveChanges() > 0;
if (Ispass)
{
string From = "";
var lstUser = db.mTUser.FirstOrDefault(w => w.ID == ua.nUserID);
if (lstUser != null)
{
From = lstUser.Email;
}
// string From = item.sEmail;
#region Get_Email_Admin
string AdminContactmail = "";
var lst_Email_Admin = db.TMenu_Permission.Where(w => w.nRoleID == 1 && w.nMenuID == 65 && w.nPermission == 2).ToList();
if (lst_Email_Admin.Any())
{
lst_Email_Admin.ForEach(f =>
{
int nUserID = f.nUserID;
var lst_Tuser = db.mTUser.FirstOrDefault(w => w.cActive == "Y" && w.ID == nUserID);
if (lst_Tuser != null)
{
AdminContactmail += "," + lst_Tuser.Email + " ";
}
});
AdminContactmail = AdminContactmail.Remove(0, 1);
}
else
{
AdminContactmail = WebConfigurationManager.AppSettings["ContactMail"] + "";
}
#endregion
string To = "*****@*****.**";
string sSubject = SystemFunction.sAbbrSystem + " : Contact Us." + item.sSubject;
string sGurl = SystemFunction.RequestUrl() + WebConfigurationManager.AppSettings["DefaultPage"] + "";
string sHtml = @"
<table align='left' border='0' cellspacing='2' cellpadding='3'>
<tr>
<td align='left'>Dear administrator: " + SystemFunction.SystemName + @" </td>
</tr>
<tr>
<td align='left'>Subject: " + item.sSubject + @"</td>
</tr>
<tr>
<td align='left'>Description: " + item.sDec + @"</td>
</tr>
<tr>
<td align='left'>From: " + item.sName + " E-mail: " + item.sEmail + " Contact phone number: " + item.sTel + @"</td>
</tr>
<tr>
<td align='left'><b>Check out more details.</b>: <a href='" + sGurl + @"' target='_blank'>Please click here</a></td>
</tr>
</table>";
//sPathSend != "" ? sPathSend.Replace("/", "\\") : ""
Workflow.DataMail_log log = new Workflow.DataMail_log();
log = SystemFunction.SendMailAll(From, AdminContactmail, "", "", sSubject, sHtml, sPathSend != "" ? sPathSend.Replace("/", "\\") : "");
log.nDataID = SystemFunction.GetIntNullToZero(nID + "");
log.sPageName = "f_ContactUs.aspx";
new Workflow().SaveLogMail(log);
if (log.bStatus)
{
result.Status = SystemFunction.process_Success;
}
}
}
catch (Exception e)
{
result.Msg = e.Message;
result.Status = SystemFunction.process_Failed;
}
result.Status = SystemFunction.process_Success;
}
return(result);
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <TDataTable> lstData = new List <TDataTable>();
string sSearch = itemSearch.sSearch.Trims().ToLower();
string sStatus = itemSearch.sStatus.Trims().ToLower();
if (!string.IsNullOrEmpty(sStatus))
{
sStatus = sStatus == "w" ? null : sStatus;
}
lstData = db.TContactUs.Where(w => w.cDel == "N" && (!string.IsNullOrEmpty(sSearch) ? w.sContactName.ToLower().Contains(sSearch) : true) && (sStatus != "" ? w.cStatusAns == sStatus : true)).Select(s => new TDataTable
{
nContactID = s.nContactID,
sContactName = s.sContactName,
cStatusAns = s.cStatusAns == null ? "Wait" : s.cStatusAns == "1" ? "Read" : "Success",
cStatus = s.cStatusAns,
dCreate = s.dCreate,
dUpdate = s.dUpdate,
}).OrderByDescending(o => o.dUpdate).ToList();
//lstData = db.mTCompany.Where(w => w.cDel == "N" && (!string.IsNullOrEmpty(sSearch) ? w.Name.ToLower().Contains(sSearch) : true)).Select(s => new TDataTable
//{
// nCompID = s.ID,
// sCompName = s.Name,
// sStatus = s.cActive == "Y" ? "Active" : "Inactive",
// dUpdate = s.dUpdate
//}).OrderByDescending(o => o.dUpdate).ToList();
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.sContactName).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.dCreate).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.cStatus).ToList(); break;
//case 4: lstData = lstData.OrderBy(o => o.nCountFacility).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.sContactName).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.dCreate).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.cStatus).ToList(); break;
//case 4: lstData = lstData.OrderByDescending(o => o.nCountFacility).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), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
foreach (var item in lstData)
{
var color = item.cStatus == null ? "warning" : item.cStatus == "1" ? "primary" : "success";
var Wordding = item.cStatus == null ? "Reply" : item.cStatus == "1" ? "Read" : "View";
var icon = item.cStatus == null ? "edit" : item.cStatus == "1" ? "search" : "search";
item.sUpdate = item.dUpdate.DateString();
item.sCreate = item.dCreate.DateString();
item.sLink = "<a class='btn btn-" + color + "' href='admin_ContactUs_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nContactID.ToString())) + "'><i class='fa fa-" + icon + "'></i> " + Wordding + "</a>";
}
result.lstData = lstData;
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);
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <TDataTable> lstData = new List <TDataTable>();
//var Indicator = db.mTIndicator.ToList();
//var Facility = db.mTFacility.Where(w => w.cActive == "Y" && w.cDel == "N").ToList();
//var Operation = db.mOperationType.Where(w => w.cActive == "Y" && w.cDel == "N").ToList();
//var TbUser = db.mTUser.Where(w => w.cActive == "Y" && w.cDel == "N").ToList();
int Operation_ID = 0;
string sCon = "";
int nRoleID = UserAcc.GetObjUser().nRoleID;
int nUserID = UserAcc.GetObjUser().nUserID;
if (nRoleID == 6)
{
var Get_Fac = db.mTUser_FacilityPermission.Where(w => w.nUserID == nUserID && w.nRoleID == nRoleID).ToList(); // User And Role Get Facility
var lst_SubFac = Get_Fac.Select(id => id.nFacilityID).ToList(); //Facility ID in Company_admin
if (lst_SubFac.Any())
{
string Format = "";
lst_SubFac.ForEach(f =>
{
int FacID = f;
Format += "," + FacID + "";
});
Format = Format.Remove(0, 1);
sCon += @" AND mtf.IDFac IN (" + Format + ") ";
//sCon += @" AND tu.ID IN (" + Format + ") ";
}
}
string sSearch = itemSearch.sSearch.Trims().ToLower();
if (!string.IsNullOrEmpty(sSearch))
{
sCon += @" AND (LOWER(tu.Firstname) LIKE '%" + sSearch + "%' COLLATE THAI_CI_AI OR LOWER(tu.Lastname) LIKE '%" + sSearch + "%' COLLATE THAI_CI_AI OR LOWER(tu1.Firstname) LIKE '%" + sSearch + "%' COLLATE THAI_CI_AI OR LOWER(tu1.Lastname) LIKE '%" + sSearch + "%' COLLATE THAI_CI_AI) ";
}
if (!string.IsNullOrEmpty(itemSearch.sOperationID))
{
sCon += @" AND mf.OperationTypeID = " + itemSearch.sOperationID + @" ";
}
if (!string.IsNullOrEmpty(itemSearch.sFacilityID))
{
sCon += @" AND mtf.IDFac = " + itemSearch.sFacilityID + @" ";
}
if (!string.IsNullOrEmpty(itemSearch.sIndicatorID))
{
sCon += @" AND mtf.IDIndicator = " + itemSearch.sIndicatorID + @" ";
}
//&& (!string.IsNullOrEmpty(sSearch) ? w.Name.ToLower().Contains(sSearch) : true)
string _SQL = @" SELECT mtf.IDFac as IDFac
,mtf.IDIndicator as IDIndicator
,mtf.L1 as L1
,mtf.L2 as L2
,mtf.dUpdate as dUpdate
,mti.Indicator as sGroupName
,mf.OperationTypeID as IDOperation
,ISNULL(mot.Name,'') as sOperationName
,ISNULL(mf.Name,'') as sFacilityName
,ISNULL(tu.Firstname,'') +' '+ ISNULL(tu.Lastname,'') AS sManagerName
,ISNULL(tu1.Firstname,'') +' '+ ISNULL(tu1.Lastname,'') AS sEnvironName
FROM mTWorkFlow mtf
INNER JOIN mTIndicator mti ON mtf.IDIndicator = mti.ID
INNER JOIN mTFacility mf ON mf.ID = mtf.IDFac AND mf.cActive = 'Y' AND mf.cDel = 'N'
INNER JOIN mOperationType mot ON mot.ID = mf.OperationTypeID AND mot.cActive = 'Y' AND mot.cDel = 'N'
LEFT JOIN mTUser tu ON tu.ID = mtf.L1 AND tu.cActive = 'Y' AND tu.cDel = 'N'
LEFT JOIN mTUser tu1 ON tu1.ID = mtf.L2 AND tu1.cActive = 'Y' AND tu1.cDel = 'N'
WHERE mtf.cDel ='N' " + sCon + @"
ORDER BY mtf.dUpdate DESC ";
DataTable dt = CommonFunction.Get_Data(SystemFunction.strConnect, _SQL);
lstData = CommonFunction.ConvertDatableToList <TDataTable>(dt).ToList();
//CommonFunction.
//lstData = db.mTWorkFlow.Where(w => w.cDel == "N").AsEnumerable().Select(s => new TDataTable
//{
// IDFac = s.IDFac,
// IDOperation = Operation_ID = Facility.Any(a => a.ID == s.IDFac && a.cDel == "N" && a.cActive == "Y") ? Facility.First(a => a.ID == s.IDFac && a.cDel == "N" && a.cActive == "Y").OperationTypeID : 0,
// IDIndicator = s.IDIndicator,
// sFacilityName = Facility.Any(a => a.ID == s.IDFac) ? Facility.First(a => a.ID == s.IDFac && a.cDel == "N" && a.cActive == "Y").Name : "",
// sGroupName = Indicator.Any(a => a.ID == s.IDIndicator) ? Indicator.First(a => a.ID == s.IDIndicator).Indicator : "",
// sOperationName = Operation.Any(a => a.ID == Operation_ID) ? Operation.First(a => a.ID == Operation_ID).Name : "",
// L1 = s.L1,
// L2 = s.L2,
// dUpdate = s.dUpdate,
// sManagerName = TbUser.Any(a => a.ID == s.L1) ? TbUser.First(a => a.ID == s.L1 && a.cActive == "Y" && a.cDel == "N").Firstname + ' ' + TbUser.First(a => a.ID == s.L1 && a.cActive == "Y" && a.cDel == "N").Lastname : "",
// sEnvironName = TbUser.Any(a => a.ID == s.L2) ? TbUser.First(a => a.ID == s.L2 && a.cActive == "Y" && a.cDel == "N").Firstname + ' ' + TbUser.First(a => a.ID == s.L2 && a.cActive == "Y" && a.cDel == "N").Lastname : "",
//}).OrderByDescending(o => o.dUpdate).ToList();
lstData = lstData.Where(w => w.sOperationName != "" && w.sFacilityName != "").ToList();
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.sOperationName).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.sFacilityName).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.sGroupName).ToList(); break;
case 4: lstData = lstData.OrderBy(o => o.sManagerName).ToList(); break;
case 5: lstData = lstData.OrderBy(o => o.sEnvironName).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.sOperationName).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.sFacilityName).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.sGroupName).ToList(); break;
case 4: lstData = lstData.OrderByDescending(o => o.sManagerName).ToList(); break;
case 5: lstData = lstData.OrderByDescending(o => o.sEnvironName).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), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
//foreach (var item in lstData)
//{
// item.sUpdate = item.dUpdate.DateString();
// item.sLink = "<a class='btn btn-warning' href='admin_company_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.IDOperation.ToString())) + "'><i class='fa fa-edit'></i> Edit</a>";
//}
result.lstData = lstData;
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);
}
public static sysGlobalClass.CResutlWebMethod SaveToDB(string sDesc, string sID, List <dataFileContactUs> objFile)
{
sysGlobalClass.CResutlWebMethod result = new sysGlobalClass.CResutlWebMethod();
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
DateTime now = DateTime.Now;
if (UserAcc.UserExpired())
{
result.Status = SystemFunction.process_SessionExpired;
}
else
{
try
{
UserAcc ua = UserAcc.GetObjUser();
if (!string.IsNullOrEmpty(sID))
{
string sPathSend = "";
int nID = int.Parse(STCrypt.Decrypt(sID));
var Query = db.TContactUs.FirstOrDefault(w => w.cDel == "N" && w.nContactID == nID);
if (Query != null)
{
Query.sAnswer = sDesc;
Query.cStatusAns = "2";
Query.dAnswer = now;
Query.dUpdate = now;
Query.nAnswerBy = UserAcc.GetObjUser().nUserID;
if (objFile.Count > 0 && objFile != null)
{
string sPathSave = string.Format(sFolderInPathSave, nID);
SystemFunction.CreateDirectory(sPathSave);
//string sPathSave = string.Format(sFolderInPathSave, nID);
//SystemFunction.CreateDirectory(sPathSave);
var f = objFile.First();
string sSystemFileName = nID + "_" + "-" + DateTime.Now.ToString("ddMMyyyyHHmmss") + "." + SystemFunction.GetFileNameFromFileupload(f.sFileName, ""); //SystemFunction2.GetFileType(item.SaveToFileName);
SystemFunction.UpFile2Server(f.sPath, sPathSave, f.sSysFileName, sSystemFileName);
Query.sAnsFile = f.sFileName;
Query.sAnsSysFile = sSystemFileName;
Query.sAnsPath = sPathSave;
sPathSend = HttpContext.Current.Server.MapPath("./") + sPathSave + sSystemFileName;
}
var Ispass = db.SaveChanges() > 0;
if (Ispass)
{
string To = Query.sContactEmail; // Contact Mail
string AdminContactmail = WebConfigurationManager.AppSettings["ContactMail"] + ""; // Mail Admin
//string To1 = "*****@*****.**";
string sSubject = SystemFunction.sAbbrSystem + " : Contact Us." + Query.sSubject;
string sGurl = SystemFunction.RequestUrl() + WebConfigurationManager.AppSettings["DefaultPage"] + "";
string sHtml = @"
<table align='left' border='0' cellspacing='2' cellpadding='3'>
<tr>
<td align='left'>Dear: " + Query.sContactName + @"</td>
</tr>
<tr>
<td align='left'>Subject: " + Query.sSubject + @"</td>
</tr>
<tr>
<td align='left'>Description: " + Query.sDetail + @"</td>
</tr>
<tr>
<td align='left'>Answer: " + Query.sAnswer + @"</td>
</tr>
</table>";
Workflow.DataMail_log log = new Workflow.DataMail_log();
string ccMail = "";
var lstUser = db.mTUser.FirstOrDefault(w => w.ID == ua.nUserID);
if (lstUser != null)
{
ccMail = lstUser.Email;
}
log = SystemFunction.SendMailAll(AdminContactmail, To, ccMail, "", sSubject, sHtml, sPathSend != "" ? sPathSend.Replace("/", "\\") : "");
log.nDataID = SystemFunction.GetIntNullToZero(nID + "");
log.sPageName = "admin_ContactUs_update.aspx";
new Workflow().SaveLogMail(log);
if (log.bStatus)
{
result.Status = SystemFunction.process_Success;
}
}
}
}
}
catch (Exception e)
{
result.Msg = e.Message;
result.Status = SystemFunction.process_Failed;
}
result.Status = SystemFunction.process_Success;
}
return(result);
}
public static CReturnLoadData LoadData(CSearch itemSearch)
{
CReturnLoadData result = new CReturnLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
int nRoleID = UserAcc.GetObjUser().nRoleID;
List <TDataTable> lstData = new List <TDataTable>();
int nYear = itemSearch.sYear.toIntNullToZero();
#region SQL Sub-facility
string _sConditonSubFac = "";
if (!string.IsNullOrEmpty(itemSearch.sIndicatorID))
{
_sConditonSubFac += " AND TEPI.IDIndicator='" + CommonFunction.ReplaceInjection(itemSearch.sIndicatorID) + "'";
}
string sqlSubFac = @"SELECT T1.FormID,T1.sYear, ISNULL(T1.IDIndicator,0) 'IDIndicator',ISNULL(T1.OperationtypeID,0) 'OperationtypeID',ISNULL(T1.FacilityID,0) 'FacilityID',T1.sFacName,ISNULL(T1.nHeaderID,0) 'nHeaderID', T1.nQuarter FROM
(
--Q1
SELECT TEPI.FormID,TEPI.sYear,TEPI.IDIndicator,TEPI.OperationtypeID,TEPI.FacilityID,TFAC.Name 'sFacName',TFAC.nHeaderID,1 'nQuarter',CASE WHEN (COUNT(TWF.nMonth)) = 3 THEN 1 ELSE 0 END AS nStatus
FROM TEPI_Forms TEPI
INNER JOIN TEPI_Workflow TWF ON TEPI.FormID=TWF.FormID
INNER JOIN mTFacility TFAC ON TEPI.FacilityID=TFAC.ID AND TEPI.OperationtypeID=TFAC.OperationTypeID AND TFAC.cActive='Y' AND TFAC.cDel='N'
WHERE TEPI.sYear='{0}' AND TWF.nStatusID=5 AND TWF.nMonth IN (1,2,3) " + _sConditonSubFac + @"
GROUP BY TEPI.FormID,TEPI.sYear,TEPI.IDIndicator,TEPI.OperationtypeID,TEPI.FacilityID,TFAC.nHeaderID,TFAC.Name
UNION
--Q2
SELECT TEPI.FormID,TEPI.sYear,TEPI.IDIndicator,TEPI.OperationtypeID,TEPI.FacilityID,TFAC.Name 'sFacName',TFAC.nHeaderID,2 'nQuarter',CASE WHEN (COUNT(TWF.nMonth)) = 3 THEN 1 ELSE 0 END AS nStatus
FROM TEPI_Forms TEPI
INNER JOIN TEPI_Workflow TWF ON TEPI.FormID=TWF.FormID
INNER JOIN mTFacility TFAC ON TEPI.FacilityID=TFAC.ID AND TEPI.OperationtypeID=TFAC.OperationTypeID AND TFAC.cActive='Y' AND TFAC.cDel='N'
WHERE TEPI.sYear='{0}' AND TWF.nStatusID=5 AND TWF.nMonth IN (4,5,6) " + _sConditonSubFac + @"
GROUP BY TEPI.FormID,TEPI.sYear,TEPI.IDIndicator,TEPI.OperationtypeID,TEPI.FacilityID,TFAC.nHeaderID,TFAC.Name
UNION
--Q3
SELECT TEPI.FormID,TEPI.sYear,TEPI.IDIndicator,TEPI.OperationtypeID,TEPI.FacilityID,TFAC.Name 'sFacName',TFAC.nHeaderID,3 'nQuarter',CASE WHEN (COUNT(TWF.nMonth)) = 3 THEN 1 ELSE 0 END AS nStatus
FROM TEPI_Forms TEPI
INNER JOIN TEPI_Workflow TWF ON TEPI.FormID=TWF.FormID
INNER JOIN mTFacility TFAC ON TEPI.FacilityID=TFAC.ID AND TEPI.OperationtypeID=TFAC.OperationTypeID AND TFAC.cActive='Y' AND TFAC.cDel='N'
WHERE TEPI.sYear='{0}' AND TWF.nStatusID=5 AND TWF.nMonth IN (7,8,9) " + _sConditonSubFac + @"
GROUP BY TEPI.FormID,TEPI.sYear,TEPI.IDIndicator,TEPI.OperationtypeID,TEPI.FacilityID,TFAC.nHeaderID,TFAC.Name
UNION
--Q4
SELECT TEPI.FormID,TEPI.sYear,TEPI.IDIndicator,TEPI.OperationtypeID,TEPI.FacilityID,TFAC.Name 'sFacName',TFAC.nHeaderID,4 'nQuarter',CASE WHEN (COUNT(TWF.nMonth)) = 3 THEN 1 ELSE 0 END AS nStatus
FROM TEPI_Forms TEPI
INNER JOIN TEPI_Workflow TWF ON TEPI.FormID=TWF.FormID
INNER JOIN mTFacility TFAC ON TEPI.FacilityID=TFAC.ID AND TEPI.OperationtypeID=TFAC.OperationTypeID AND TFAC.cActive='Y' AND TFAC.cDel='N'
WHERE TEPI.sYear='{0}' AND TWF.nStatusID=5 AND TWF.nMonth IN (10,11,12) " + _sConditonSubFac + @"
GROUP BY TEPI.FormID,TEPI.sYear,TEPI.IDIndicator,TEPI.OperationtypeID,TEPI.FacilityID,TFAC.nHeaderID,TFAC.Name
) AS T1
WHERE T1.nStatus=1
";
#endregion
string _sqlSub = string.Format(sqlSubFac, nYear);
var lstDataSubFac = db.Database.SqlQuery <TDataSubFac>(_sqlSub).ToList();
#region SQL Main facility
string sqlMainFac = @"SELECT TFPTT.ID 'nPTTFacID',TFPTT.Name 'sPTTFacName',TFPTT.sMappingCodePTT
,TFGC.ID 'nGCFacID',TFGC.Name 'sGCFacName'
FROM mTFacility TFPTT
INNER JOIN mTFacility TFGC ON TFPTT.ID=TFGC.nHeaderID AND TFGC.nLevel=1 AND TFGC.cActive='Y' AND TFGC.cDel='N'
WHERE TFPTT.nLevel=0 and TFPTT.cActive='Y' and TFPTT.cDel='N'";
#endregion
var lstDataMainFac = db.Database.SqlQuery <TDataMainFaciltiy>(sqlMainFac).ToList();
var dataStausWF = db.TStatus_Workflow.Where(w => w.cTypeUse == "TNF" && w.cActive == "Y").ToList();
int?nIndID = itemSearch.sIndicatorID.toIntNull();
int?nStatusID = itemSearch.sStatus.toIntNull();
int?nFacID = itemSearch.sFacID.toIntNull();
lstData = (from mf in lstDataMainFac
from sf in lstDataSubFac.Where(w => w.nHeaderID == mf.nGCFacID)
from ind in db.mTIndicator.Where(w => w.ID == sf.IDIndicator)
from wf in db.TEPI_TransferPTT.Where(w => w.nYear == nYear && w.nFacilityID == sf.nHeaderID && w.nIndicatorID == sf.IDIndicator && w.nQuarter == sf.nQuarter).DefaultIfEmpty()
from st in dataStausWF.Where(w => (wf != null ? w.nStatustID == wf.nStatusID : false)).DefaultIfEmpty()
group new { mf, sf, st, ind } by new { mf.nGCFacID, mf.sGCFacName, mf.sPTTFacName, mf.sMappingCodePTT, sf.nQuarter, nIndID = ind.ID, ind.Indicator, nStatustID = (st != null ? st.nStatustID : 0), sStatusName = (st != null ? st.sShorttStatus : "Waiting Transfer"), dAction = wf != null ? wf.dAction : null } into grp
select new TDataTable
{
nYear = nYear,
nGCFacID = grp.Key.nGCFacID,
sGCFacName = grp.Key.sGCFacName,
sPTTFacilityCode = grp.Key.sMappingCodePTT,
sPTTFacilityName = grp.Key.sPTTFacName,
nQuarter = grp.Key.nQuarter,
nIndicatorID = grp.Key.nIndID,
sIndicatorName = grp.Key.Indicator,
nStatus = grp.Key.nStatustID,
sStatusName = grp.Key.sStatusName,
dAction = grp.Key.dAction
}).Where(w => (nIndID.HasValue ? w.nIndicatorID == nIndID : true) && (nStatusID.HasValue ? w.nStatus == nStatusID : true) && (nFacID.HasValue ? w.nGCFacID == nFacID : true))
.OrderByDescending(o => o.dAction).ThenBy(o => o.nYear).ThenBy(o => o.sGCFacName).ThenBy(o => o.nQuarter).ThenBy(o => o.sIndicatorName).ToList();
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.nYear).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.nQuarter).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.sGCFacName).ToList(); break;
case 4: lstData = lstData.OrderBy(o => o.sIndicatorName).ToList(); break;
case 5: lstData = lstData.OrderBy(o => o.sStatusName).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.nYear).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.nQuarter).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.sGCFacName).ToList(); break;
case 4: lstData = lstData.OrderByDescending(o => o.sIndicatorName).ToList(); break;
case 5: lstData = lstData.OrderByDescending(o => o.sStatusName).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), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
foreach (var item in lstData)
{
if (!string.IsNullOrEmpty(item.sPTTFacilityCode))
{
item.sBtnClass = "btn btn-primary";
item.sBtnText = "View Detail";
item.sWarningPTTCode = "N";
if (nRoleID == 4) //ENVI Corporate(L2)
{
if (item.nStatus == 0 || item.nStatus == 29 || item.nStatus == 33) //Waiting Submit/ Reject by PTT / Accept Edit Request by PTT
{
item.sLink = "epi_transfertoptt.aspx?stryear=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nYear + "")) + "&strfacid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nGCFacID + "")) + "&strindid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nIndicatorID + "")) + "&strq=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nQuarter + "")) + "&strmode=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt("S"));
}
else if (item.nStatus == 28 || item.nStatus == 30)//Submitted to ptt / Approve by PTT
{
item.sBtnClass = "btn btn-warning";
item.sBtnText = "Request Edit";
item.sLink = "epi_transfertoptt.aspx?stryear=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nYear + "")) + "&strfacid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nGCFacID + "")) + "&strindid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nIndicatorID + "")) + "&strq=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nQuarter + "")) + "&strmode=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt("R"));
}
else
{
item.sLink = "epi_transfertoptt.aspx?stryear=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nYear + "")) + "&strfacid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nGCFacID + "")) + "&strindid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nIndicatorID + "")) + "&strq=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt("0")) + "&strmode=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt("V"));
}
}
else
{
item.sLink = "epi_transfertoptt.aspx?stryear=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nYear + "")) + "&strfacid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nGCFacID + "")) + "&strindid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nIndicatorID + "")) + "&strq=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt("0")) + "&strmode=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt("V"));
}
}
else
{
item.sWarningPTTCode = "Y";
}
}
result.lstData = lstData;
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_Failed;
}
return(result);
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <TDataTable> lstData = new List <TDataTable>();
string sSearch = itemSearch.sSearch.Trims().ToLower();
string sStatus = itemSearch.sStatus.Trims().ToLower();
//lstData = db.mTCompany.Where(w => w.cDel == "N" && (!string.IsNullOrEmpty(sSearch) ? w.Name.ToLower().Contains(sSearch) : true)).Select(s => new TDataTable
//{
// nCompID = s.ID,
// sCompName = s.Name,
// sStatus = s.cActive == "Y" ? "Active" : "Inactive",
// dUpdate = s.dUpdate
//}).OrderByDescending(o => o.dUpdate).ToList();
var query = from oType in db.mOperationType.Where(w => w.cDel == "N" && w.cManage == "Y").OrderBy(o => o.ID)
from f in db.mTFacility.Where(w => w.OperationTypeID == oType.ID && w.cDel == "N" && w.cActive == "Y").DefaultIfEmpty()
from g in db.mTFacility_Operationtype.Where(w => w.nOperationtypeID == w.nOperationtypeID).DefaultIfEmpty()
where (!string.IsNullOrEmpty(sSearch) ? oType.Name.ToLower().Contains(sSearch) : true) && (!string.IsNullOrEmpty(sStatus) ? oType.cActive.ToLower().Contains(sStatus) : true)
group new { oType, g } by new { oType.ID, oType.Name, oType.Description, oType.sCode, oType.sRemark, oType.cActive, oType.dUpdate } into grp
select new
{
nID = grp.Key.ID,
sName = grp.Key.Name,
sRemark = grp.Key.sRemark,
sCode = grp.Key.sCode,
sStatus = grp.Key.cActive,
sDescription = grp.Key.Description,
dUpdate = grp.Key.dUpdate,
nFacility = grp.Where(w => w.g.nOperationtypeID == grp.Key.ID).Count(),
};
lstData = query.ToList().Select(s => new TDataTable
{
nID = s.nID,
sName = s.sName,
sDescription = s.sDescription,
nFacility = s.nFacility,
sCode = s.sCode,
sStatus = s.sStatus,
sRemark = s.sRemark,
dUpdate = s.dUpdate,
}).OrderByDescending(o => o.dUpdate).ToList();
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.sName).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.sDescription).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.nFacility).ToList(); break;
case 4: lstData = lstData.OrderBy(o => o.sStatus).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.sName).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.sDescription).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.nFacility).ToList(); break;
case 4: lstData = lstData.OrderByDescending(o => o.sStatus).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), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
// bool isView = hdfPrmsMenu.Value == 1;
foreach (var item in lstData)
{
if (IsView)
{
item.sLink = "<a class='btn btn-primary' href='admin_operation_type_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nID.ToString())) + "'><i class='fa fa-search'></i> View</a>";
}
else
{
item.sLink = "<a class='btn btn-warning' href='admin_operation_type_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nID.ToString())) + "'><i class='fa fa-edit'></i> Edit</a>";
}
//item.sUpdate = item.dUpdate.DateString();
}
result.lstData = lstData;
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);
}
public static sysGlobalClass.TRetunrLoadData SaveData(IntensityClass it)
{
sysGlobalClass.TRetunrLoadData result = new sysGlobalClass.TRetunrLoadData();
string sStatus = it.rt.nStatusWF + "";
string sStatusTemp = sStatus;
int nFormID = 0;
try
{
if (!UserAcc.UserExpired())
{
if (it.rt.lstIn != null)
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
var ef = db.TEPI_Forms.FirstOrDefault(w => w.IDIndicator == it.nIndicator && w.sYear == it.sYear && w.OperationTypeID == it.nOperationType && w.FacilityID == it.nFacility);
if (ef == null)
{
nFormID = (db.TEPI_Forms.Any() ? db.TEPI_Forms.Max(m => m.FormID) : 0) + 1;
ef = new TEPI_Forms();
ef.FormID = nFormID;
ef.sAddBy = UserAcc.GetObjUser().nUserID;
ef.dAddDate = DateTime.Now;
ef.sYear = it.sYear;
ef.IDIndicator = it.nIndicator;
ef.OperationTypeID = it.nOperationType;
ef.FacilityID = it.nFacility;
db.TEPI_Forms.Add(ef);
}
else
{
nFormID = ef.FormID;
}
if (!SystemFunction.IsSuperAdmin())
{
ef.ResponsiblePerson = UserAcc.GetObjUser().nUserID;
ef.sUpdateBy = UserAcc.GetObjUser().nUserID;
ef.dUpdateDate = DateTime.Now;
}
var ot = db.TIntensity_Other.Where(w => w.FormID == nFormID).ToList();
int nReportID = (db.TEPI_Workflow.Any() ? db.TEPI_Workflow.Max(m => m.nReportID) : 0) + 1;
if (sStatus == "24")
{
sStatus = "0";
}
List <string> lstMonthAdd = new List <string>();
for (int i = 1; i <= 12; i++)
{
lstMonthAdd.Add(i + "");
}
foreach (string i in lstMonthAdd)
{
int nM = SystemFunction.GetIntNullToZero(i);
var ef_wf = db.TEPI_Workflow.FirstOrDefault(w => w.FormID == nFormID && w.nMonth == nM);
int?nStatus = 0;
int nStatusID = SystemFunction.GetIntNullToZero(sStatus);
if (ef_wf == null)
{
ef_wf = new TEPI_Workflow();
ef_wf.nReportID = nReportID;
ef_wf.FormID = nFormID;
ef_wf.nMonth = nM;
db.TEPI_Workflow.Add(ef_wf);
if (!SystemFunction.IsSuperAdmin())
{
ef_wf.nStatusID = nStatus;
ef_wf.nActionBy = UserAcc.GetObjUser().nUserID;
ef_wf.dAction = DateTime.Now;
}
nReportID++;
}
}
if (sStatusTemp != "24" && sStatusTemp != "2")
{
db.TIntensity_Other.RemoveRange(ot);
db.SaveChanges();
it.rt.lstIn.ForEach(f =>
{
var td = db.TIntensityDominator.FirstOrDefault(w => w.FormID == nFormID && w.ProductID == f.ProductID);
int nVersion = (db.TIntensity_Remark.Any(w => w.FormID == nFormID && w.ProductID == f.ProductID) ? db.TIntensity_Remark.Where(w => w.FormID == nFormID && w.ProductID == f.ProductID).Max(m => m.nVersion) : 0) + 1;
if (td == null)
{
td = new TIntensityDominator();
td.ProductID = f.ProductID;
td.FormID = nFormID;
db.TIntensityDominator.Add(td);
}
td.UnitID = 0;
td.M1 = f.M1;
td.M2 = f.M2;
td.M3 = f.M3;
td.M4 = f.M4;
td.M5 = f.M5;
td.M6 = f.M6;
td.M7 = f.M7;
td.M8 = f.M8;
td.M9 = f.M9;
td.M10 = f.M10;
td.M11 = f.M11;
td.M12 = f.M12;
td.UnitID = f.UnitID;
if (!string.IsNullOrEmpty(f.nTotal))
{
td.nTotal = f.nTotal;
}
td.Target = f.Target;
var rm = new TIntensity_Remark();
rm.FormID = nFormID;
rm.ProductID = f.ProductID;
rm.nVersion = nVersion;
rm.sRemark = f.sRemark;
if (!SystemFunction.IsSuperAdmin())
{
rm.dAddDate = DateTime.Now;
rm.sAddBy = UserAcc.GetObjUser().nUserID;
}
int nUnit = f.UnitID;
db.TIntensity_Remark.Add(rm);
f.lstarrDetail.Where(w => w.IsActive == true).ToList().ForEach(f2 =>
{
var to = db.TIntensity_Other.Where(w => w.FormID == nFormID && w.ProductID == f2.nProductID && w.UnderProductID == f2.UnderProductID).FirstOrDefault();
if (to == null)
{
to = new TIntensity_Other();
to.ProductID = f2.nProductID;
to.FormID = nFormID;
to.UnderProductID = f2.UnderProductID;
db.TIntensity_Other.Add(to);
}
to.UnitID = nUnit;
to.UnitID = 0;
to.M1 = f2.M1;
to.M2 = f2.M2;
to.M3 = f2.M3;
to.M4 = f2.M4;
to.M5 = f2.M5;
to.M6 = f2.M6;
to.M7 = f2.M7;
to.M8 = f2.M8;
to.M9 = f2.M9;
to.M10 = f2.M10;
to.M11 = f2.M11;
to.M12 = f2.M12;
to.Target = f2.sTarget;
to.ProductName = f2.sIndicator;
});
});
#region SAVE FILE
if (it.rt.lstDataFile.Count > 0)
{
string sPathSave = string.Format(it.sFolderInPathSave, nFormID);
SystemFunction.CreateDirectory(sPathSave);
//ลบไฟล์เดิมที่เคยมีและกดลบจากหน้าเว็บ
var qDelFile = it.rt.lstDataFile.Where(w => w.IsNewFile == false && w.sDelete == "Y").ToList();
if (qDelFile.Any())
{
foreach (var qf in qDelFile)
{
var query = db.TEPI_Forms_File.FirstOrDefault(w => w.FormID == nFormID && w.sSysFileName == qf.SaveToFileName);
if (query != null)
{
new SystemFunction().DeleteFileInServer(query.sPath, query.sSysFileName);
// new SystemFunction2().DeleteFileInServer(query.sSysPath, query.sSysFileName);
db.TEPI_Forms_File.Remove(query);
///db.TAuditPlan_AttachFile.Remove(query);
}
}
db.SaveChanges();
}
//Save New File Only
var lstSave = it.rt.lstDataFile.Where(w => w.sDelete == "N").ToList();
if (lstSave.Any())
{
int nFileID = db.TEPI_Forms_File.Where(w => w.FormID == nFormID).Any() ? db.TEPI_Forms_File.Where(w => w.FormID == nFormID).Max(m => m.nFileID) + 1 : 1;
lstSave.ForEach(f =>
{
string sSystemFileName = "";
if (f.IsNewFile == true)
{
sSystemFileName = nFormID + "_" + nFileID + "-" + DateTime.Now.ToString("ddMMyyyyHHmmss") + "." + SystemFunction.GetFileNameFromFileupload(f.SaveToFileName, "");
SystemFunction.UpFile2Server(f.SaveToPath, sPathSave, f.SaveToFileName, sSystemFileName);
f.ID = nFileID;
}
var t = db.TEPI_Forms_File.FirstOrDefault(w => w.nFileID == f.ID && w.FormID == nFormID);
if (t == null)
{
t = new TEPI_Forms_File();
t.FormID = nFormID;
t.nFileID = nFileID;
t.sSysFileName = sSystemFileName;
t.sFileName = f.FileName;
t.sPath = sPathSave;
db.TEPI_Forms_File.Add(t);
nFileID++;
}
t.sDescription = f.sDescription;
if (!SystemFunction.IsSuperAdmin())
{
t.dAdd = DateTime.Now;
t.nAddBy = UserAcc.GetObjUser().nUserID;
}
});
}
}
#endregion
}
db.SaveChanges();
new EPIFunc().RecalculateMaterial(it.nOperationType, it.nFacility, it.sYear);
new EPIFunc().RecalculateWaste(it.nOperationType, it.nFacility, it.sYear);
new EPIFunc().RecalculateWater(it.nOperationType, it.nFacility, it.sYear);
}
if (sStatusTemp != "27")
{
new Workflow().UpdateHistoryStatus(nFormID);
}
int nStatusTemp = SystemFunction.GetIntNullToZero(sStatusTemp);
if (nStatusTemp != 0)
{
string sMode = "";
switch (nStatusTemp)
{
case 1:
sMode = "SM";
break;
case 2:
sMode = "RQ";
break;
case 24:
sMode = "RC";
break;
case 27:
sMode = "APC";
break;
}
if (!string.IsNullOrEmpty(sMode))
{
var wf = new Workflow().WorkFlowAction(nFormID, it.lstMonth, sMode, UserAcc.GetObjUser().nUserID, UserAcc.GetObjUser().nRoleID, it.rt.sComment);
result.sStatus = wf.Status;
result.sMsg = wf.Msg;
}
else
{
if (UserAcc.GetObjUser().nRoleID == 4)//ENVI Corporate (L2) >> Req.09.04.2019 Send email to L0 on L2 Modified data.
{
new Workflow().SendEmailToL0onL2EditData(it.sYear, it.nIndicator, it.nFacility, it.nOperationType);
}
result.sStatus = SystemFunction.process_Success;
result.sMsg = "";
}
}
else
{
result.sStatus = SystemFunction.process_Success;
result.sMsg = "";
}
}
else
{
result.sStatus = SystemFunction.process_SessionExpired;
result.sMsg = "Session Expired !!";
}
}
catch (Exception ex)
{
result.sStatus = SystemFunction.process_Failed;
result.sMsg = ex.ToString();
}
string sPath = "#";
switch (it.nOperationType)
{
case 4:
sPath = "epi_input_intensity_1.aspx";
ssTRetunrLoadData_1 = GetLoadData(it);
break;
case 11:
sPath = "epi_input_intensity_2.aspx";
ssTRetunrLoadData_2 = GetLoadData(it);
break;
case 13:
sPath = "epi_input_intensity_3.aspx";
ssTRetunrLoadData_3 = GetLoadData(it);
break;
case 14:
sPath = "epi_input_intensity_4.aspx";
ssTRetunrLoadData_4 = GetLoadData(it);
break;
}
if (sPath != "#")
{
sPath = sPath + "?in=" + HttpUtility.UrlEncode(STCrypt.Encrypt(it.nIndicator + "")) + "&&ot=" + HttpUtility.UrlEncode(STCrypt.Encrypt(it.nOperationType + ""));
}
result.sPath = sPath;
return(result);
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <TDataTable> lstData = new List <TDataTable>();
int nRoleID = UserAcc.GetObjUser().nRoleID;
int nUserID = UserAcc.GetObjUser().nUserID;
string sCon = "";
string sSearch = itemSearch.sSearch.Trims().ToLower();
if (!string.IsNullOrEmpty(sSearch))
{
sCon += @" AND (tu.Firstname LIKE '%" + sSearch + "%' OR tu.Lastname LIKE '%" + sSearch + "%' OR tu.Username LIKE '%" + sSearch + "%') ";
}
if (!string.IsNullOrEmpty(itemSearch.sUserRole))
{
sCon += @" AND (tr.nRoleID = " + itemSearch.sUserRole + @") ";
}
if (!string.IsNullOrEmpty(itemSearch.sOperationSearch))
{
sCon += @" AND tf.OperationTypeID = " + itemSearch.sOperationSearch + @" ";
}
if (!string.IsNullOrEmpty(itemSearch.sFacilitySearch))
{
sCon += @" AND tmf.nFacilityID = " + itemSearch.sFacilitySearch + @" ";
}
if (!string.IsNullOrEmpty(itemSearch.sStatus))
{
sCon += @" AND tu.cActive = '" + itemSearch.sStatus + @"' ";
}
if (nRoleID == 6)
{
var Get_Fac = db.mTUser_FacilityPermission.Where(w => w.nUserID == nUserID && w.nRoleID == nRoleID).Select(s => s.nFacilityID).Distinct().ToList(); // User And Role Get Facility
if (Get_Fac.Any())
{
string Format = "";
Get_Fac.ForEach(f =>
{
int nFac = f;
Format += "," + nFac + "";
});
Format = Format.Remove(0, 1);
sCon += @" AND tmf.nFacilityID IN (" + Format + ") ";
}
//var Get_Operation = db.mTFacility.Where(w => Get_Fac.Contains(w.ID) && itemSearch.sOperationSearch != "" ? w.ID == int.Parse(itemSearch.sOperationSearch) : true).Select(s => s.OperationTypeID).Distinct().ToList();
var lst_User = db.mTUser_FacilityPermission.Where(w => Get_Fac.Contains(w.nFacilityID)).Select(s => s.nUserID).Distinct().ToList(); // User IN Facility Company_Role
if (lst_User.Any())
{
string Format = "";
lst_User.ForEach(f =>
{
int nUserId = f;
Format += "," + nUserId + "";
});
Format = Format.Remove(0, 1);
sCon += @" AND tu.ID IN (" + Format + ") ";
}
}
string _SQL = @" SELECT tu.ID as nID
,ISNULL(tu.Username,'') as Username
,ISNULL(tu.Firstname,'') as Firstname
,ISNULL(tu.Lastname,'') as Lastname
, ISNULL(tu.Company,'') as Company
, ISNULL(tu.Email,'') as Email
,CASE WHEN tu.cActive = 'Y' THEN 'Active' WHEN tu.cActive = 'N' THEN 'Inactive' ELSE '' END as sStatus
,tu.dUpdate as dUpdate
,ISNULL(tu.sUserCode,'') as sUserCode
,(SELECT COUNT(tr.nUID) FROM mTUserInRole tr WHERE tr.nUID = tu.ID) as nCountRole
FROM mTUser tu
LEFT JOIN mTUser_FacilityPermission tmf ON tmf.nUserID = tu.ID
LEFT JOIN TMenu_Permission tmu ON tmu.nUserID = tu.ID
LEFT JOIN mTFacility tf ON tf.ID = tmf.nFacilityID
LEFT JOIN mTUserInRole tr ON tr.nUID = tu.ID
WHERE tu.cDel = 'N' and tu.ID != 1 " + sCon + @"
GROUP BY tu.ID,tu.Username,tu.Firstname,tu.Lastname,tu.Company,tu.Email,tu.cActive,tu.dUpdate,tu.sUserCode
ORDER BY tu.dUpdate DESC ";
DataTable dt = CommonFunction.Get_Data(SystemFunction.strConnect, _SQL);
lstData = CommonFunction.ConvertDatableToList <TDataTable>(dt).ToList();
//lstData = db.mTUser.Where(w => w.cDel == "N" && (!string.IsNullOrEmpty(sSearch) ? w.Firstname.ToLower().Contains(sSearch) || w.Lastname.ToLower().Contains(sSearch) : true)).Select(s => new TDataTable
//{
// //nCompID = s.ID,
// //sCompName = s.Name,
// //sStatus = s.cActive == "Y" ? "Active" : "Inactive",
// dUpdate = s.dUpdate,
// nID = s.ID,
// Firstname = (string.IsNullOrEmpty(s.Firstname)) ? "" : s.Firstname,
// Lastname = (string.IsNullOrEmpty(s.Lastname)) ? "" : s.Lastname,
// Username = (string.IsNullOrEmpty(s.Username)) ? "" : s.Username,
// Email = (string.IsNullOrEmpty(s.Email)) ? "" : s.Email,
// sUserCode = (string.IsNullOrEmpty(s.sUserCode)) ? "N/A" : s.sUserCode,
// Company = s.Company,
// sStatus = s.cActive == "Y" ? "Active" : "Inactive",
//}).OrderByDescending(o => o.dUpdate).ToList();
foreach (var item in lstData)
{
List <TDataRole> lstData1 = new List <TDataRole>();
item.sUpdate = item.dUpdate.DateString();
if (IsView)
{
item.sLink = "<a class='btn btn-primary' href='admin_user_info_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nID.ToString())) + "'><i class='fa fa-search'></i> View</a>";
}
else
{
item.sLink = "<a class='btn btn-warning' href='admin_user_info_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nID.ToString())) + "'><i class='fa fa-edit'></i> Edit</a>";
}
}
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.Username).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.Firstname).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.Company).ToList(); break;
case 4: lstData = lstData.OrderBy(o => o.nCountRole).ToList(); break;
case 5: lstData = lstData.OrderBy(o => o.Email).ToList(); break;
case 6: lstData = lstData.OrderBy(o => o.sStatus).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.Username).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.Firstname).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.Company).ToList(); break;
case 4: lstData = lstData.OrderByDescending(o => o.nCountRole).ToList(); break;
case 5: lstData = lstData.OrderByDescending(o => o.Email).ToList(); break;
case 6: lstData = lstData.OrderByDescending(o => o.sStatus).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), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
result.lstData = lstData;
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);
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <TDataTable> lstData = new List <TDataTable>();
string sSearch = itemSearch.sSearch.Trims().ToLower();
string sStatus = itemSearch.sStatus.Trims().ToLower();
int nComID = SystemFunction.GetIntNullToZero(STCrypt.Decrypt(itemSearch.sComID));
lstData = db.mTFacility.Where(w => w.cDel == "N" && w.nLevel == 0 && w.CompanyID == nComID && (!string.IsNullOrEmpty(sSearch) ? w.Name.ToLower().Contains(sSearch) : true) && (!string.IsNullOrEmpty(sStatus) ? w.cActive.ToLower().Contains(sStatus) : true)).Select(s => new TDataTable
{
nCompanyID = s.CompanyID,
nFacilityID = s.ID,
sFacilityName = s.Name,
sStatus = s.cActive == "Y" ? "Active" : "Inactive",
dUpdate = s.dUpdate,
}).OrderByDescending(o => o.dUpdate).ToList();
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.sFacilityName).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.sStatus).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.dUpdate).ToList(); break;
case 4: lstData = lstData.OrderBy(o => o.dUpdate).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.sFacilityName).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.sStatus).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.dUpdate).ToList(); break;
case 4: lstData = lstData.OrderByDescending(o => o.dUpdate).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), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
foreach (var item in lstData)
{
item.sUpdate = item.dUpdate.DateString();
item.sLink = "<a class='btn btn-warning' href='admin_facility_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nCompanyID.ToString())) + "&&strFacID=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nFacilityID.ToString())) + "'><i class='fa fa-edit'></i> Edit</a>";
}
result.lstData = lstData;
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);
}
public static sysGlobalClass.TRetunrLoadData GetLoadData(IntensityClass it)
{
sysGlobalClass.TRetunrLoadData o = new sysGlobalClass.TRetunrLoadData();
try
{
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
bool IsNew = false;
int nFormID = 0;
var qEPIFomr = db.TEPI_Forms.Where(w => w.IDIndicator == it.nIndicator && w.sYear == it.sYear && w.OperationTypeID == it.nOperationType && w.FacilityID == it.nFacility).FirstOrDefault();
if (qEPIFomr == null)
{
IsNew = true;
string sYearDel = (SystemFunction.GetIntNullToZero(it.sYear) - 1) + "";
qEPIFomr = db.TEPI_Forms.Where(w => w.IDIndicator == it.nIndicator && w.sYear == sYearDel && w.OperationTypeID == it.nOperationType && w.FacilityID == it.nFacility).FirstOrDefault();
}
if (qEPIFomr != null)
{
nFormID = qEPIFomr.FormID;
}
var lstOther = db.TIntensity_Other.Where(w => w.FormID == nFormID).AsEnumerable().Select(s => new sysGlobalClass.T_TIntensity_Other
{
UnderProductID = s.UnderProductID, // Header
nProductID = s.ProductID,
sIndicator = s.ProductName,
cTotal = "N",
cTotalAll = "N",
FormID = s.FormID,
M1 = s.M1 != null && !IsNew ? s.M1 : "",
M2 = s.M2 != null && !IsNew ? s.M2 : "",
M3 = s.M3 != null && !IsNew ? s.M3 : "",
M4 = s.M4 != null && !IsNew ? s.M4 : "",
M5 = s.M5 != null && !IsNew ? s.M5 : "",
M6 = s.M6 != null && !IsNew ? s.M6 : "",
M7 = s.M7 != null && !IsNew ? s.M7 : "",
M8 = s.M8 != null && !IsNew ? s.M8 : "",
M9 = s.M9 != null && !IsNew ? s.M9 : "",
M10 = s.M10 != null && !IsNew ? s.M10 : "",
M11 = s.M11 != null && !IsNew ? s.M11 : "",
M12 = s.M12 != null && !IsNew ? s.M12 : "",
IsActive = true,
sTarget = !IsNew ? s.Target : "",
nFactor = s.Factor,
}).ToList();
var efi_wf = db.TEPI_Workflow.Where(w => w.FormID == nFormID).Select(s => new sysGlobalClass.T_TEPI_Workflow
{
nReportID = s.nReportID,
FormID = s.FormID,
nMonth = s.nMonth,
nStatusID = IsNew ? 0 : s.nStatusID.HasValue ? s.nStatusID : 0,
nActionBy = s.nActionBy,
dAction = s.dAction
}).ToList();
if (IsNew)
{
efi_wf = new List <sysGlobalClass.T_TEPI_Workflow>();
}
o.lstwf = efi_wf;
o.lstIn = (from i in db.mTProductIndicator.Where(w => w.IDIndicator.Value == it.nIndicator).AsEnumerable()
from u in db.TIntensityUseProduct.Where(w => w.OperationTypeID == it.nOperationType && w.ProductID == i.ProductID).AsEnumerable()
from d in db.TIntensityDominator.Where(w => w.ProductID == i.ProductID && w.FormID == nFormID).AsEnumerable().DefaultIfEmpty()
from s in db.mTProductIndicatorUnit.Where(w => w.ProductID == u.ProductID && w.IDIndicator == i.IDIndicator).DefaultIfEmpty()
from un in db.mTUnit.Where(w => w.UnitID == s.UnitID).DefaultIfEmpty()
select new sysGlobalClass.TData_Intensity
{
ProductID = i.ProductID,
ProductName = i.ProductName,
cTotal = i.cTotal,
cTotalAll = i.cTotalAll,
nGroupCalc = i.nGroupCalc ?? 0,
nOrder = u.nOrder ?? 0,
sUnit = s != null && un != null ? un.UnitName : i.sUnit,
UnitID = s != null && un != null ? s.UnitID : 0,
sType = i.sType,
M1 = d != null && !IsNew ? d.M1 != null ? d.M1 : "" : "",
M2 = d != null && !IsNew ? d.M2 != null ? d.M2 : "" : "",
M3 = d != null && !IsNew ? d.M3 != null ? d.M3 : "" : "",
M4 = d != null && !IsNew ? d.M4 != null ? d.M4 : "" : "",
M5 = d != null && !IsNew ? d.M5 != null ? d.M5 : "" : "",
M6 = d != null && !IsNew ? d.M6 != null ? d.M6 : "" : "",
M7 = d != null && !IsNew ? d.M7 != null ? d.M7 : "" : "",
M8 = d != null && !IsNew ? d.M8 != null ? d.M8 : "" : "",
M9 = d != null && !IsNew ? d.M9 != null ? d.M9 : "" : "",
M10 = d != null && !IsNew ? d.M10 != null ? d.M10 : "" : "",
M11 = d != null && !IsNew ? d.M11 != null ? d.M11 : "" : "",
M12 = d != null && !IsNew ? d.M12 != null ? d.M12 : "" : "",
nTotal = d != null && !IsNew ? d.nTotal != null ? d.nTotal : "" : "",
Target = d != null && !IsNew ? d.Target : "",
lstarrDetail = lstOther.Where(w => w.UnderProductID == i.ProductID).ToList(),
sRemark = !IsNew ? db.TIntensity_Remark.Any(w => w.FormID == nFormID && w.ProductID == i.ProductID) ? db.TIntensity_Remark.AsEnumerable().LastOrDefault(w => w.FormID == nFormID && w.ProductID == i.ProductID).sRemark : "" : "",
}).OrderBy(k => k.nOrder).ToList();
o.lstDataFile = db.TEPI_Forms_File.Where(w => w.FormID == nFormID).Select(s => new sysGlobalClass.FuncFileUpload.ItemData
{
ID = s.nFileID,
FileName = s.sFileName,
SaveToFileName = s.sSysFileName,
url = s.sPath + s.sSysFileName,
SaveToPath = s.sPath,
IsNewFile = false,
sDelete = "N",
sDescription = s.sDescription,
}).ToList();
if (IsNew)
{
o.lstDataFile = new List <sysGlobalClass.FuncFileUpload.ItemData>();
}
o.hdfPRMS = SystemFunction.GetPermission_EPI_FROMS(it.nIndicator, it.nFacility);
o.sStatus = SystemFunction.process_Success;
o.sMsg = "";
}
else
{
o.sStatus = SystemFunction.process_SessionExpired;
o.sMsg = "Session Expired !!";
}
}
catch (Exception ex)
{
o.sStatus = SystemFunction.process_Failed;
o.sMsg = ex.ToString();
}
return(o);
}
public static TRetunrLoadData LoadData_Facility(CSearch itemSearch, string sID)
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
TRetunrLoadData result = new TRetunrLoadData();
List <TDataFacility> lstData = new List <TDataFacility>();
if (!UserAcc.UserExpired())
{
string sOperationID = sID.STCDecrypt();
if (!string.IsNullOrEmpty(sOperationID))
{
int nOperationID = sOperationID.toIntNullToZero();
var lst_Facility = db.mTFacility_Operationtype.Where(w => w.nOperationtypeID == nOperationID).ToList();
if (lst_Facility != null)
{
lst_Facility.ForEach(f =>
{
var lst = db.mTFacility.Where(w => w.ID == f.nFacID).Select(s => new TDataFacility
{
nID = s.ID,
sName = s.Name,
}).ToList();
lstData.AddRange(lst);
});
}
}
//#region//SORT
//int? nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
//switch ((itemSearch.sOrderBy + "").ToLower())
//{
// case SystemFunction.ASC:
// {
// switch (nSortCol)
// {
// case 1: lstData = lstData.OrderBy(o => o.sCompName).ToList(); break;
// case 2: lstData = lstData.OrderBy(o => o.sStatus).ToList(); break;
// case 3: lstData = lstData.OrderBy(o => o.dUpdate).ToList(); break;
// case 4: lstData = lstData.OrderBy(o => o.nCountFacility).ToList(); break;
// }
// }
// break;
// case SystemFunction.DESC:
// {
// switch (nSortCol)
// {
// case 1: lstData = lstData.OrderByDescending(o => o.sCompName).ToList(); break;
// case 2: lstData = lstData.OrderByDescending(o => o.sStatus).ToList(); break;
// case 3: lstData = lstData.OrderByDescending(o => o.dUpdate).ToList(); break;
// case 4: lstData = lstData.OrderByDescending(o => o.nCountFacility).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), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
//foreach (var item in lstData)
//{
// item.sUpdate = item.dUpdate.DateString();
// item.sLink = "<a class='btn btn-warning' href='admin_company_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nCompID.ToString())) + "'><i class='fa fa-edit'></i> Edit</a>";
//}
result.lstDataFacility = lstData;
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);
}
public void ExportData(string sIncID, string sOprtID, string sFacID, string sYear)
{
HttpResponse httpResponse = Response;
XLWorkbook wb = new XLWorkbook();
int nRow = 1;
int nCol = 1;
#region Action
Action <IXLWorksheet, string, int, int, int, bool, XLAlignmentHorizontalValues, XLAlignmentVerticalValues, bool, int?, double?> SetTbl = (sWorkSheet, sTxt, row, col, FontSize, Bold, Horizontal, Vertical, wraptext, dec, width) =>
{
if (sTxt == null)
{
sTxt = "";
}
sWorkSheet.Cell(row, col).Value = sTxt;
sWorkSheet.Cell(row, col).Style.Font.FontSize = FontSize;
sWorkSheet.Cell(row, col).Style.Font.Bold = Bold;
sWorkSheet.Cell(row, col).Style.Alignment.WrapText = true;
sWorkSheet.Cell(row, col).Style.Alignment.Horizontal = Horizontal;
sWorkSheet.Cell(row, col).Style.Alignment.Vertical = Vertical;
if (width != null)
{
sWorkSheet.Column(col).Width = width.Value;
}
if (dec != null && sTxt != "0")
{
string sformate = "#,##";
sWorkSheet.Cell(row, col).Style.NumberFormat.Format = sformate;
}
var nIndex = sTxt.Split('/').Length;
if (nIndex == 3)
{
sWorkSheet.Cell(row, col).Style.DateFormat.Format = "dd/MM/yyyy";
}
};
#endregion
#region QUERY
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <cReport> lstProduct = new List <cReport>();
string[] arrShortMonth = new string[12] {
"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
};
int nIndID = SystemFunction.GetIntNullToZero(sIncID);
int nOprtID = SystemFunction.GetIntNullToZero(sOprtID);
int nFacID = SystemFunction.GetIntNullToZero(sFacID);
string sIncName = db.mTIndicator.Any(w => w.ID == nIndID) ? db.mTIndicator.FirstOrDefault(w => w.ID == nIndID).Indicator : "";
string sOprtName = db.mOperationType.Any(w => w.ID == nOprtID) ? db.mOperationType.FirstOrDefault(w => w.ID == nOprtID).Name : "";
string sFacName = db.mTFacility.Any(w => w.ID == nFacID) ? db.mTFacility.FirstOrDefault(w => w.ID == nFacID).Name : "";
var lstComplaintType = db.TM_ComplaintType.Where(w => w.cDel != "Y").ToList();
bool IsNew = true;
var itemEPI_FORM = db.TEPI_Forms.FirstOrDefault(w => w.sYear == sYear && w.IDIndicator == nIndID && w.OperationTypeID == nOprtID && w.FacilityID == nFacID);
int EPI_FORMID = 0;
if (itemEPI_FORM != null)
{
IsNew = false;
EPI_FORMID = itemEPI_FORM.FormID;
}
var lstImpactInFROM = db.TComplaint_Impact.Where(w => w.FormID == EPI_FORMID).ToList();
string sProductName = "";
var dataComplaint = db.mTProductIndicator.FirstOrDefault(w => w.IDIndicator == 2);
if (dataComplaint != null)
{
sProductName = dataComplaint.ProductName;
}
lstProduct = db.TComplaint.Where(w => w.FormID == EPI_FORMID).OrderBy(o => o.ComplaintDate).Select(s => new cReport
{
nComplaintID = s.nComplaintID,
ComplaintDate = s.ComplaintDate.HasValue ? s.ComplaintDate.Value : (DateTime?)null,
sProductName = sProductName,
nMonth = s.ComplaintDate.HasValue ? s.ComplaintDate.Value.Month : 0,
sIssueBy = s.sIssueBy,
sSubject = s.sSubject,
sDetail = s.sDetail,
nComplaintTypeID = s.nComplaintTypeID,
nComplaintByID = s.nComplaintByID,
sComplaintByOther = s.sComplaintByOther,
sCorrectiveAction = s.sCorrectiveAction,
}).ToList();
lstProduct.ForEach(f =>
{
var dataComplaintType = lstComplaintType.FirstOrDefault(w => w.nType == 1 && w.nID == f.nComplaintTypeID);
var dataComplaintBy = lstComplaintType.FirstOrDefault(w => w.nType == 3 && w.nID == f.nComplaintByID);
f.sIssueDate = f.ComplaintDate.HasValue ? f.ComplaintDate.Value.ToString("dd/MM/yyyy") : "";
f.sComplaintBy = dataComplaintBy != null ? dataComplaintBy.sName + (!string.IsNullOrEmpty(f.sComplaintByOther) ? " (" + f.sComplaintByOther + ")" : "") : "";
lstImpactInFROM.Where(w => w.nComplaintID == f.nComplaintID).ForEach(ipct =>
{
var dataImpact = lstComplaintType.FirstOrDefault(w => w.nType == 2 && w.nID == ipct.nImpactTypeID);
f.sImpact += dataImpact != null ? " ," + dataImpact.sName + (!string.IsNullOrEmpty(ipct.sOther) ? " (" + ipct.sOther + ")" : "") : "";
});
f.sImpact = f.sImpact.Length > 0 ? f.sImpact.Remove(0, 2) : f.sImpact;
f.sComplaintType = dataComplaintType != null ? dataComplaintType.sName : "";
});
var lstDeviate = SystemFunction.GetDeviate(nIndID, nOprtID, nFacID, sYear);
#endregion
#region BIND DATA
IXLWorksheet ws1 = wb.Worksheets.Add("Data");
ws1.PageSetup.Margins.Top = 0.2;
ws1.PageSetup.Margins.Bottom = 0.2;
ws1.PageSetup.Margins.Left = 0.1;
ws1.PageSetup.Margins.Right = 0;
ws1.PageSetup.Margins.Footer = 0;
ws1.PageSetup.Margins.Header = 0;
ws1.Style.Font.FontName = "Cordia New";
nRow = 1;
nCol = 1;
SetTbl(ws1, "Indicator : " + sIncName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
SetTbl(ws1, "Operation : " + sOprtName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
SetTbl(ws1, "Facility : " + sFacName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
SetTbl(ws1, "Year : " + sYear, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
SetTbl(ws1, "Product", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 45);
nCol++;
SetTbl(ws1, "Month", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 17);
nCol++;
SetTbl(ws1, "Complaint Type", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 17);
nCol++;
SetTbl(ws1, "Impact Type", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 17);
nCol++;
SetTbl(ws1, "Issue date", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 12);
nCol++;
SetTbl(ws1, "Issued by", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 15);
nCol++;
SetTbl(ws1, "Subject", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 35);
nCol++;
SetTbl(ws1, "Complaint by", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 17);
nCol++;
SetTbl(ws1, "Detail", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 35);
nCol++;
SetTbl(ws1, "Corrective action", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 35);
ws1.Range(nRow, 1, nRow, nCol).Style.Fill.BackgroundColor = XLColor.FromHtml("#9cb726");
ws1.Range(nRow, 1, nRow, nCol).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws1.Range(nRow, 1, nRow, nCol).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
foreach (var item in lstProduct)
{
nRow++;
nCol = 1;
SetTbl(ws1, "'" + item.sProductName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + arrShortMonth[item.nMonth.Value - 1], nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sComplaintType, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sImpact, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sIssueDate, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sIssueBy, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sSubject, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sComplaintBy, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sDetail, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws1, "'" + item.sCorrectiveAction, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
ws1.Range(nRow, 1, nRow, nCol).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws1.Range(nRow, 1, nRow, nCol).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
#endregion
#region BIND DATA DEVIATE
IXLWorksheet ws2 = wb.Worksheets.Add("Deviate");
ws2.PageSetup.Margins.Top = 0.2;
ws2.PageSetup.Margins.Bottom = 0.2;
ws2.PageSetup.Margins.Left = 0.1;
ws2.PageSetup.Margins.Right = 0;
ws2.PageSetup.Margins.Footer = 0;
ws2.PageSetup.Margins.Header = 0;
ws2.Style.Font.FontName = "Cordia New";
nRow = 1;
nCol = 1;
SetTbl(ws2, "Indicator : " + sIncName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
ws2.Range(nRow, nCol, nRow, nCol + 1).Merge();
nCol++;
SetTbl(ws2, sIncName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
nCol = 1;
SetTbl(ws2, "Operation : " + sOprtName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
ws2.Range(nRow, nCol, nRow, nCol + 1).Merge();
nCol++;
SetTbl(ws2, sOprtName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
nCol = 1;
SetTbl(ws2, "Facility : " + sFacName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
ws2.Range(nRow, nCol, nRow, nCol + 1).Merge();
nCol++;
SetTbl(ws2, sFacName, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
nCol = 1;
SetTbl(ws2, "Year : " + sYear, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
ws2.Range(nRow, nCol, nRow, nCol + 1).Merge();
nCol++;
SetTbl(ws2, sYear, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nRow++;
nCol = 1;
SetTbl(ws2, "No.", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 10);
nCol++;
SetTbl(ws2, "Month", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 13);
nCol++;
SetTbl(ws2, "Remark", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 50);
nCol++;
SetTbl(ws2, "Action By", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 20);
nCol++;
SetTbl(ws2, "Date", nRow, nCol, 14, true, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, 15);
ws2.Range(nRow, 1, nRow, nCol).Style.Fill.BackgroundColor = XLColor.FromHtml("#9cb726");
ws2.Range(nRow, 1, nRow, nCol).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws2.Range(nRow, 1, nRow, nCol).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
for (int i = 0; i < lstDeviate.Count(); i++)
{
nRow++;
nCol = 1;
SetTbl(ws2, "'" + (i + 1), nRow, nCol, 14, false, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws2, lstDeviate[i].sMonth, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws2, lstDeviate[i].sRemark, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws2, lstDeviate[i].sActionBy, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Left, XLAlignmentVerticalValues.Center, true, null, null);
nCol++;
SetTbl(ws2, lstDeviate[i].sDate, nRow, nCol, 14, false, XLAlignmentHorizontalValues.Center, XLAlignmentVerticalValues.Center, true, null, null);
ws2.Range(nRow, 1, nRow, nCol).Style.Border.InsideBorder = XLBorderStyleValues.Thin;
ws2.Range(nRow, 1, nRow, nCol).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
}
#endregion
#region CreateEXCEL
httpResponse.Clear();
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string sName = "Input_" + sIncName + "_" + sFacName + "_" + DateTime.Now.ToString("ddMMyyHHmmss", new CultureInfo("en-US"));
httpResponse.AddHeader("content-disposition", "attachment;filename=" + sName + ".xlsx");
// Flush the workbook to the Response.OutputStream
using (MemoryStream memoryStream = new MemoryStream())
{
wb.SaveAs(memoryStream);
memoryStream.WriteTo(httpResponse.OutputStream);
memoryStream.Close();
}
httpResponse.End();
#endregion
}
public static TRetunrLoadData LoadData(CSearch itemSearch)
{
TRetunrLoadData result = new TRetunrLoadData();
if (!UserAcc.UserExpired())
{
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
List <TDataTable> lstData = new List <TDataTable>();
string sSearch = itemSearch.sSearch.Trims().ToLower();
string sStatus = itemSearch.sStatus.Trims().ToLower();
lstData = db.mTCompany.Where(w => w.cDel == "N" && (!string.IsNullOrEmpty(sSearch) ? w.Name.ToLower().Contains(sSearch) : true) && (!string.IsNullOrEmpty(sStatus) ? w.cActive.ToLower().Contains(sStatus) : true)).Select(s => new TDataTable
{
nCompID = s.ID,
sCompName = s.Name,
sStatus = s.cActive == "Y" ? "Active" : "Inactive",
dUpdate = s.dUpdate,
SearchBy = "",
}).OrderByDescending(o => o.dUpdate).ToList();
var lstDataFac = db.mTFacility.Where(w => w.cDel == "N").ToList();
var lstCompanyID_SearchBySub = db.mTFacility.Where(w => w.cActive == "Y" && w.cDel == "N" && w.nLevel == 2 && (!string.IsNullOrEmpty(sSearch) ? w.Name.ToLower().Contains(sSearch) : false)).Select(s => new { s.CompanyID }).Distinct().ToList();
var lstCompanyID_SearchBySubLve1 = db.mTFacility.Where(w => w.cActive == "Y" && w.cDel == "N" && w.nLevel == 1 && (!string.IsNullOrEmpty(sSearch) ? w.Name.ToLower().Contains(sSearch) : false)).Select(s => new { s.CompanyID }).Distinct().ToList();
if (lstCompanyID_SearchBySub.Any())
{
List <int> lstComID = new List <int>();
lstComID = lstCompanyID_SearchBySub.Select(s => s.CompanyID).ToList();
var lstCompany = db.mTCompany.Where(w => w.cDel == "N" && lstComID.Contains(w.ID)).Select(s => s.ID).ToList();
if (lstCompany.Any())
{
foreach (var item in lstCompany)
{
///// Check ว่ามีไอดีนี้ในบริษัทไหม ถ้าไม่มีค่อย แอด เพิ่ม
var q = lstData.FirstOrDefault(w => w.nCompID == item);
if (q == null)
{
List <TDataTable> lstDataSub2 = new List <TDataTable>();
lstDataSub2 = db.mTCompany.Where(w => w.cDel == "N" && w.ID == item).Select(s => new TDataTable
{
nCompID = s.ID,
sCompName = s.Name,
sStatus = s.cActive == "Y" ? "Active" : "Inactive",
dUpdate = s.dUpdate,
SearchBy = "Y",
}).OrderByDescending(o => o.dUpdate).ToList();
lstData.AddRange(lstDataSub2);
}
}
}
}
if (lstCompanyID_SearchBySubLve1.Any())
{
List <int> lstComID = new List <int>();
lstComID = lstCompanyID_SearchBySubLve1.Select(s => s.CompanyID).ToList();
var lstCompany = db.mTCompany.Where(w => w.cDel == "N" && lstComID.Contains(w.ID)).Select(s => s.ID).ToList();
if (lstCompany.Any())
{
foreach (var item in lstCompany)
{
///// Check ว่ามีไอดีนี้ในบริษัทไหม ถ้าไม่มีค่อย แอด เพิ่ม
var q = lstData.FirstOrDefault(w => w.nCompID == item);
if (q == null)
{
List <TDataTable> lstDataSub1 = new List <TDataTable>();
lstDataSub1 = db.mTCompany.Where(w => w.cDel == "N" && w.ID == item).Select(s => new TDataTable
{
nCompID = s.ID,
sCompName = s.Name,
sStatus = s.cActive == "Y" ? "Active" : "Inactive",
dUpdate = s.dUpdate,
SearchBy = "Y",
}).OrderByDescending(o => o.dUpdate).ToList();
lstData.AddRange(lstDataSub1);
}
}
}
}
foreach (var item in lstData)
{
string sLinkFacility = "";
item.nCountFacility = lstDataFac.Where(w => w.CompanyID == item.nCompID && w.cDel == "N" && (item.nCompID == 1 ? w.nLevel == 0 : w.nLevel == 1)).Count();
sLinkFacility = "<div class='btn-group'>" +
"<a class='btn btn-sm btn-primary' href='admin_facility_lst.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nCompID.ToString())) + "'><span class='badge'>" + lstDataFac.Where(w => w.CompanyID == item.nCompID && w.cDel == "N" && (item.nCompID == 1 ? w.nLevel == 0 : w.nLevel == 1)).Count() + "</span></a>" +
"<a class='btn btn-sm btn-info' href='admin_facility_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nCompID.ToString())) + "'><i class='fa fa-plus'></i></a>" +
"</div>";
item.sUpdate = item.dUpdate.DateString();
item.sLink = "<a class='btn btn-warning' href='admin_company_update.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nCompID.ToString())) + "'><i class='fa fa-edit'></i> Edit</a>";
//item.sLinkFacility = "<a class='btn btn-info' href='admin_facility_lst.aspx?strid=" + HttpContext.Current.Server.UrlEncode(STCrypt.Encrypt(item.nCompID.ToString())) + "'><i class='fa fa-search'></i></a>";
item.sLinkFacility = sLinkFacility;
}
#region//SORT
int?nSortCol = SystemFunction.GetIntNull(itemSearch.sIndexCol);
switch ((itemSearch.sOrderBy + "").ToLower())
{
case SystemFunction.ASC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderBy(o => o.sCompName).ToList(); break;
case 2: lstData = lstData.OrderBy(o => o.sStatus).ToList(); break;
case 3: lstData = lstData.OrderBy(o => o.dUpdate).ToList(); break;
case 4: lstData = lstData.OrderBy(o => o.nCountFacility).ToList(); break;
}
}
break;
case SystemFunction.DESC:
{
switch (nSortCol)
{
case 1: lstData = lstData.OrderByDescending(o => o.sCompName).ToList(); break;
case 2: lstData = lstData.OrderByDescending(o => o.sStatus).ToList(); break;
case 3: lstData = lstData.OrderByDescending(o => o.dUpdate).ToList(); break;
case 4: lstData = lstData.OrderByDescending(o => o.nCountFacility).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), lstData.Count);
lstData = lstData.Skip(dataPage.nSkipData).Take(dataPage.nTakeData).ToList();
result.lstData = lstData;
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);
}
public static sysGlobalClass.CResutlWebMethod saveToDB(DataValue dataValue)
{
sysGlobalClass.CResutlWebMethod result = new sysGlobalClass.CResutlWebMethod();
PTTGC_EPIEntities db = new PTTGC_EPIEntities();
if (!UserAcc.UserExpired())
{
dataValue.sFacilityID = STCrypt.Decrypt(dataValue.sFacilityID);
dataValue.sAssetID = !string.IsNullOrEmpty(dataValue.sAssetID) ? STCrypt.Decrypt(dataValue.sAssetID) : "";
Func <string, int?, bool> CheckDuplicateName = (name, AssetID) =>
{
bool Isdup = false;
var q = db.mTFacility.Where(w => (AssetID.HasValue ? w.ID != AssetID : true) && w.nLevel == 2 && w.Name.Trim().ToLower() == name.Trim().ToLower() && w.cDel == "N");
Isdup = q.Any();
return(Isdup);
};
Func <string, int?, bool> CheckDuplicateNameSAP = (name, AssetID) =>
{
bool Isdup = false;
var q = db.mTFacility.Where(w => (AssetID.HasValue ? w.ID != AssetID : true) && w.nLevel == 2 && w.sSAPName.Trim().ToLower() == name.Trim().ToLower() && w.cDel == "N");
Isdup = q.Any();
return(Isdup);
};
Func <string, int?, bool> CheckDuplicateCodeSAP = (code, AssetID) =>
{
bool Isdup = false;
var q = db.mTFacility.Where(w => (AssetID.HasValue ? w.ID != AssetID : true) && w.nLevel == 2 && w.sRefFacCode.Trim().ToLower() == code.Trim().ToLower() && w.cDel == "N");
Isdup = q.Any();
return(Isdup);
};
#region Add / Update
if (dataValue.sRefFacType == "N" && CheckDuplicateName(dataValue.sAssetName, dataValue.IsNew ? (int?)null : SystemFunction.GetIntNullToZero(dataValue.sAssetID)))
{
result.Status = SystemFunction.process_Failed;
result.Msg = "Already Sub-facility Name !";
return(result);
}
if (dataValue.sRefFacType == "P" && !string.IsNullOrEmpty(dataValue.sRefFacCode))
{
if (CheckDuplicateCodeSAP(dataValue.sRefFacCode, dataValue.IsNew ? (int?)null : SystemFunction.GetIntNullToZero(dataValue.sAssetID)))
{
result.Status = SystemFunction.process_Failed;
result.Msg = "Already SAP Code !";
return(result);
}
}
bool IsPass = true;
int sAssetID = SystemFunction.GetIntNullToZero(dataValue.sAssetID);
int nFacilityID = SystemFunction.GetIntNullToZero(dataValue.sFacilityID);
var itemAsset = db.mTFacility.FirstOrDefault(w => w.ID == sAssetID);
var itemFacility = db.mTFacility.FirstOrDefault(w => w.ID == nFacilityID);
int nMaxID = db.mTFacility.Any() ? db.mTFacility.Max(m => m.ID) + 1 : 1;
if (dataValue.IsNew)
{
itemAsset = new mTFacility();
itemAsset.ID = nMaxID;
itemAsset.cDel = "N";
itemAsset.CreateID = UserAcc.GetObjUser().nUserID;
itemAsset.dCreate = DateTime.Now;
}
if (dataValue.sRefFacType == "P")
{
#region SAP Master
var dataComp = db.mTCompany.FirstOrDefault(w => w.ID == itemFacility.CompanyID);
if (dataComp != null)
{
var qSAPFac = db.v_TM_SAP_ALLFAC.FirstOrDefault(w => w.sCode == dataValue.sRefFacCode && w.sCompCode == dataComp.sCode);
if (qSAPFac != null)
{
itemAsset.sRefFacCode = dataValue.sRefFacCode;
itemAsset.Name = qSAPFac.sName.Trims();
itemAsset.sRefFacSubType = qSAPFac.sType;
itemAsset.sInternalCode = "";
}
else
{
IsPass = false;
result.Status = SystemFunction.process_Failed;
result.Msg = "Not found SAP master data !";
}
}
else
{
IsPass = false;
result.Status = SystemFunction.process_Failed;
result.Msg = "Not found company data !";
}
#endregion
}
else
{
#region Manual Create
var dataComp = db.mTCompany.FirstOrDefault(w => w.ID == itemFacility.CompanyID);
if (dataComp != null)
{
if (!string.IsNullOrEmpty(dataValue.sRefFacCode))//มีการอัพเดท SAP Code หลังจากที่ manual create
{
var qSAPFac = db.v_TM_SAP_ALLFAC.FirstOrDefault(w => w.sCode == dataValue.sRefFacCode && w.sCompCode == dataComp.sCode);
if (qSAPFac != null)
{
itemAsset.sRefFacCode = dataValue.sRefFacCode;
itemAsset.Name = qSAPFac.sName.Trims();
}
else
{
IsPass = false;
result.Status = SystemFunction.process_Failed;
result.Msg = "Not found SAP master data !";
}
}
else
{
itemAsset.Name = dataValue.sAssetName;
itemAsset.sRefFacCode = "";
}
itemAsset.sRefFacSubType = dataValue.sRefFacSubType;
if (dataValue.IsNew || string.IsNullOrEmpty(itemAsset.sInternalCode))
{
string sType = dataValue.sRefFacSubType == "O" ? "Z" : dataValue.sRefFacSubType;
char cType = '-';
switch (sType)
{
case "O": cType = 'Z'; break;
case "S": cType = 'S'; break;
case "P": cType = 'P'; break;
}
Func <string, int> GetNumber = (code) =>
{
string[] arr = code.Split(cType);
return((arr[arr.Length - 1]).toIntNullToZero());
};
var qFacInternalCode = db.mTFacility.Where(w => w.cDel == "N" && w.CompanyID == itemFacility.CompanyID && w.sInternalCode.Contains(sType)).AsEnumerable().Select(s => new
{
nNumber = GetNumber(s.sInternalCode)
}).ToList();
int nNumber = qFacInternalCode.Any() ? qFacInternalCode.Max(x => x.nNumber) + 1 : 1;
itemAsset.sInternalCode = dataComp.sCode + sType + nNumber; //EX. 10Z1
}
}
else
{
IsPass = false;
result.Status = SystemFunction.process_Failed;
result.Msg = "Not found company data !";
}
#endregion
}
if (IsPass)
{
itemAsset.sRefFacType = dataValue.sRefFacType;
itemAsset.nLevel = 2;
itemAsset.nHeaderID = itemFacility.ID;
itemAsset.sRelation = itemFacility.sRelation + "-" + itemAsset.ID;
itemAsset.OperationTypeID = itemFacility.OperationTypeID;
itemAsset.CompanyID = itemFacility.CompanyID;
itemAsset.Description = dataValue.sDescription;
itemAsset.cActive = dataValue.sActive;
itemAsset.sRemark = dataValue.sActive == "Y" ? null : dataValue.sRemark;
itemAsset.UpdateID = dataValue.IsNew ? (int?)null : UserAcc.GetObjUser().nUserID;
itemAsset.dUpdate = DateTime.Now;
if (dataValue.IsNew)
{
db.mTFacility.Add(itemAsset);
}
db.SaveChanges();
result.Status = SystemFunction.process_Success;
}
#endregion
}
else
{
result.Status = SystemFunction.process_SessionExpired;
}
return(result);
}
