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); }