protected void btnSearch_OnClick(object sender, EventArgs e)
{
objReportBiz = new ReportBiz();
ReportDocument rd = new ReportDocument();
CompanyInformation objClass = new CompanyInformation();
DataTable dt = new DataTable();
dt = objReportBiz.InvoicewisePurchaseDetails(txtInvoiceNumber.Text.Trim());
rd.Load(Server.MapPath("InvoicewisePurchaseDetail.rpt"));
//Property for Header And Footer - Start
TextObject txtCompanyName = rd.ReportDefinition.ReportObjects["txtCompanyName"] as TextObject;
txtCompanyName.Text = objClass.txtCompanyName;
TextObject txtTradeLicense = rd.ReportDefinition.ReportObjects["txtTradeLicence"] as TextObject;
txtTradeLicense.Text = objClass.txtTradeLicense;
TextObject txtCompanyAddress = rd.ReportDefinition.ReportObjects["txtCompanyAddress"] as TextObject;
txtCompanyAddress.Text = objClass.txtCompanyAddress;
//Property for Header And Footer - End
rd.SetDataSource(dt);
Session["CrystalReport"] = rd;
ScriptManager.RegisterStartupScript(Page, typeof(Page), "OpenWindow",
"window.open('../ReportViewer.aspx');", true);
}
protected void ShowAlert()
{
var reportBiz = new ReportBiz(SPContext.Current.Site.ID);
var listId = new Guid(Request.QueryString["id"]);
_DAO.Command = "SELECT TableName FROM RPTList WHERE RPTListID=@RPTListID";
_DAO.AddParam("@RPTListID", listId);
string sTableName = Request.QueryString["name"];
DataTable refTables = reportBiz.GetReferencingTables(_DAO, sTableName);
string sLists = GetRefLists(refTables);
string redirectURL = SPContext.Current.Site.ServerRelativeUrl;
if (!redirectURL.EndsWith("/"))
{
redirectURL = redirectURL + "/";
}
redirectURL = redirectURL + "_layouts/epmlive/ListMappings.aspx";
string sAlert =
"<script language='javascript' type='text/javascript'> if (confirm('Before this list can be deleted the following lists must be deleted first: " +
sLists + "'))"
+ "{ window.location.href ='" + redirectURL + "'; }" +
"else { window.location.href = '" + redirectURL + "';} </script>";
ClientScript.RegisterStartupScript(GetType(), "EPMLiveWarning", sAlert);
}
public void Initialize()
{
shimsContext = ShimsContext.Create();
SetupShims();
reportBiz = new ReportBiz(DummyGuid);
privateObject = new PrivateObject(reportBiz);
}
private bool AddField(SPListEventProperties properties)
{
bool isSuccessful = true;
string tableName = string.Empty;
string ssTableName = string.Empty;
var rb = new ReportBiz(properties.SiteId);
try
{
var rd = new ReportData(properties.SiteId);
var cols = new ColumnDefCollection();
tableName = rd.GetTableName(properties.ListId);
ssTableName = rd.GetTableNameSnapshot(properties.ListId);
if (!rd.ColumnExists(tableName, properties.Field.InternalName))
{
cols.AddColumn(properties.Field);
rd.AddColumns(tableName, cols);
rd.AddColumns(ssTableName, cols);
rd.InsertListColumns(properties.ListId, cols);
}
rd.Dispose();
}
catch (Exception ex)
{
var DAO = new EPMData(properties.SiteId);
DAO.LogStatus(properties.ListId.ToString(), properties.FieldName,
"Database column add attempt: Unable to add column " + properties.FieldName + ". " + ex.Message,
ex.StackTrace, 2, 5, Guid.NewGuid().ToString()); //Logged in the RefreshAll event log.
DAO.Dispose();
}
if (properties.Field is SPFieldLookup)
{
try
{
//FOREIGN IMPLEMENTATION -- START
var DAO = new EPMData(properties.SiteId);
rb.UpdateForeignKeys(DAO);
DAO.Dispose();
// -- END
}
catch (Exception ex)
{
isSuccessful = false;
SPSecurity.RunWithElevatedPrivileges(delegate
{
if (!EventLog.SourceExists("EPMLive Reporting - UpdateForeignKeys"))
{
EventLog.CreateEventSource("EPMLive Reporting - UpdateForeignKeys", "EPM Live");
}
LogEntry(ex, "EPMLive Reporting - UpdateForeignKeys");
});
}
}
return(isSuccessful);
}
public void Constructor_SiteIdParameter_ShouldCreateInstance()
{
// Arrange, Act
reportBiz = new ReportBiz(DummyGuid);
privateObject = new PrivateObject(reportBiz);
var siteId = privateObject.GetFieldOrProperty("_siteId") as Guid?;
// Assert
reportBiz.ShouldSatisfyAllConditions(
() => reportBiz.ShouldNotBeNull(),
() => siteId.ShouldNotBeNull(),
() => siteId.Value.ShouldBe(DummyGuid));
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Request["DeleteId"] != null)
{
var webAppId = new Guid(Request["WebAppId"]);
var siteId = new Guid(Request["DeleteId"]);
var rb = new ReportBiz(siteId, webAppId);
bool canDeleteDb = rb.RemoveDatabaseMapping();
}
FillData();
}
}
public ActionResult TransactionSummary()
{
ReportBiz biz = new ReportBiz();
SearchInfo searchInfo = new SearchInfo();
searchInfo.search = new DtSearch()
{
value = ""
};
searchInfo.search.fields = new string[] { "startDate", "endDate" };
searchInfo.search.values = new string[] { DateTime.Now.AddDays(-30).ToString("yyyy-MM-dd"), DateTime.Now.ToString("yyyy-MM-dd") };
return(Json(biz.SearchReportTransactionSummary(searchInfo)));
}
protected void Page_Init(object sender, EventArgs e)
{
try
{
try
{
reportingV2Enabled =
Convert.ToBoolean(CoreFunctions.getConfigSetting(SPContext.Current.Site.RootWeb, "reportingV2"));
}
catch (Exception)
{
reportingV2Enabled = false;
SPContext.Current.Site.RootWeb.AllowUnsafeUpdates = true;
CoreFunctions.setConfigSetting(SPContext.Current.Site.RootWeb, "reportingV2", "false");
}
_DAO = new EPMData(SPContext.Current.Site.ID);
var rb = new ReportBiz(SPContext.Current.Site.ID, SPContext.Current.Web.ID, reportingV2Enabled);
if (!rb.SiteExists())
{
string msg =
"<p>This site is not currently configured to allow list mapping. A Site Collection Administrator must first map this site to a database.</p>";
lblError.Text = msg;
lblError.Visible = true;
BuildMenu(false);
return;
}
InitQueueStatus();
BuildMenu(true);
FillData();
if (Request.QueryString["delete"] != null)
{
ShowAlert();
}
}
catch (Exception ex)
{
//Response.Write(ex.Message);
}
}
private void ConfigureServerForm()
{
Guid webAppId = Guid.Empty;
if (Request.QueryString["id"] != null)
{
webAppId = new Guid(Request.QueryString["id"]);
var rb = new ReportBiz(SPContext.Current.Site.ID, webAppId);
Dictionary <string, string> databases = rb.GetDistinctDatabaseList();
DataRow SAInfo = rb.SAccountInfo(webAppId);
if (SAInfo != null && (bool)SAInfo["SAccount"])
{
username.Text = SAInfo["Username"].ToString();
password.Text = SAInfo["Password"].ToString();
sacccount.Checked = true;
}
}
btnExisting.Checked = true; //Default setting
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
if (Request.QueryString["id"] != null)
{
try
{
SPSecurity.RunWithElevatedPrivileges(delegate
{
var webAppId = new Guid(Request.QueryString["id"]);
var siteId = new Guid(SiteAdministrationSelector1.CurrentId);
var rb = new ReportBiz(SPContext.Current.Site.ID, webAppId);
Dictionary <string, string> databases = rb.GetDatabaseMappings();
string dbServer = txtDatabaseServer.Text;
string dbName = txtDatabaseName.Text;
string un = username.Text;
string pw = password.Text;
bool useSA = sacccount.Checked;
bool dbExists = btnExisting.Checked;
bool processed = false;
string statusMsg = string.Empty;
var oEPM = new EPMData(siteId);
processed = oEPM.MapDataBase(siteId, webAppId, dbServer, dbName, un, pw, useSA, dbExists,
out statusMsg);
if (processed)
{
Response.Redirect(SPContext.Current.Site.ServerRelativeUrl +
"_admin/EPMLive/ReportDatabases.aspx");
}
else
{
ShowDatabaseError(statusMsg);
sacccount.Checked = false;
Show(trNew3, false);
Show(trNew4, false);
}
});
}
catch (Exception ex) { }
}
}
public void Constructor_SiteIdWebIdReportEnabledParameters_ShouldCreateInstance()
{
// Arrange
var guid = Guid.NewGuid();
// Act
reportBiz = new ReportBiz(DummyGuid, guid, true);
privateObject = new PrivateObject(reportBiz);
var siteId = privateObject.GetFieldOrProperty("_siteId") as Guid?;
var webId = privateObject.GetFieldOrProperty("_webId") as Guid?;
var reportingEnabled = privateObject.GetFieldOrProperty("_reportingV2Enabled") as bool?;
// Assert
reportBiz.ShouldSatisfyAllConditions(
() => reportBiz.ShouldNotBeNull(),
() => siteId.ShouldNotBeNull(),
() => siteId.Value.ShouldBe(DummyGuid),
() => webId.ShouldNotBeNull(),
() => webId.Value.ShouldBe(guid),
() => reportingEnabled.GetValueOrDefault().ShouldBeTrue());
}
protected void btnExportExcel_Click(object sender, EventArgs e)
{
try
{
if (tdAssignDateFrom.DateValue.Year == 1 || tdAssignDateTo.DateValue.Year == 1)
{
AppUtil.ClientAlert(Page, "กรุณาระบุช่วงเวลาค้นหาให้ครบถ้วน");
return;
}
TimeSpan ts = tdAssignDateTo.DateValue.Subtract(tdAssignDateFrom.DateValue);
if ((ts.TotalDays + 1) > AppConstant.SnapReportDateRange)
{
AppUtil.ClientAlert(Page, "ไม่สามารถเลือกช่วงเวลาได้เกิน " + AppConstant.SnapReportDateRange + " วัน");
return;
}
string datefrom = tdAssignDateFrom.DateValue.Year.ToString() + tdAssignDateFrom.DateValue.ToString("-MM-dd");
string dateto = tdAssignDateTo.DateValue.Year.ToString() + tdAssignDateTo.DateValue.ToString("-MM-dd");
Int64 countrow = 0;
countrow = ReportBiz.CheckReportStaffWorkingExist(datefrom, dateto);
if (countrow == 0)
{
AppUtil.ClientAlert(Page, "ไม่พบข้อมูล");
}
else
{
string script = "window.open('" + Page.ResolveUrl("~/ExportReport.aspx?reportflag=RPT_SNAP_MONITORING&teamlist=" + Server.UrlEncode(txtTeamList.Text.Trim()) + "&datefrom=" + datefrom + "&dateto=" + dateto) + "', 'reports', 'status=yes, toolbar=no, scrollbars=no, menubar=no, width=600, height=400, resizable=no');";
//string script = "window.open('" + Request.ApplicationPath + "ExportReport.aspx?reportflag=RPT_SNAP_MONITORING&teamlist=" + Server.UrlEncode(txtTeamList.Text.Trim()) + "&datefrom=" + datefrom + "&dateto=" + dateto + "', 'reports', 'status=yes, toolbar=no, scrollbars=no, menubar=no, width=800, height=600, resizable=no');";
ScriptManager.RegisterStartupScript(Page, GetType(), "reportsnapmonitoring", script, true);
}
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
_log.Debug(message);
AppUtil.ClientAlert(Page, message);
}
}
private void DeleteCheck()
{
var reportBiz = new ReportBiz(SPContext.Current.Site.ID);
EPMData _DAO = new EPMData(SPContext.Current.Site.ID);
_DAO.Command = "SELECT TableName FROM RPTList WHERE RPTListID=@RPTListID";
_DAO.AddParam("@RPTListID", _listId);
string sTableName = _DAO.ExecuteScalar(_DAO.GetClientReportingConnection).ToString();
DataTable refTables = reportBiz.GetReferencingTables(_DAO, sTableName);
if (refTables.Rows.Count == 0)
{
output = "true";
//reportBiz.GetListBiz(new Guid(Request["List"])).Delete();
}
else
{
string sLists = GetRefLists(refTables, _DAO);
//SPUtility.Redirect("epmlive/ListMappings.aspx?delete=true&id=" + param + "&name=" + sTableName, SPRedirectFlags.RelativeToLayoutsPage, HttpContext.Current);
output = "false," + sLists;
}
}
private bool DeleteList(SPListEventProperties properties)
{
bool isSuccessful = true;
var reportBiz = new ReportBiz(properties.SiteId);
Guid listId = properties.ListId;
var DAO = new EPMData(properties.SiteId);
DAO.Command = "SELECT TableName FROM RPTList WHERE RPTListID=@RPTListID";
DAO.AddParam("@RPTListID", listId);
string sTableName = string.Empty;
try
{
sTableName = DAO.ExecuteScalar(DAO.GetClientReportingConnection).ToString();
DataTable refTables = reportBiz.GetReferencingTables(DAO, sTableName);
if (refTables.Rows.Count == 0)
{
reportBiz.GetListBiz(listId).Delete();
}
else
{
isSuccessful = false;
DAO.LogStatus(listId.ToString(), sTableName,
"Database table delete attempt: Unable to delete " + sTableName + ".",
sTableName + " is referenced by other tables.", 2, 5, Guid.NewGuid().ToString());
//Logged in the RefreshAll event log.
}
}
catch (Exception ex)
{
DAO.LogStatus(listId.ToString(), sTableName,
"Database table delete attempt: Unable to delete " + sTableName + ". " + ex.Message, ex.StackTrace,
2, 5, Guid.NewGuid().ToString()); //Logged in the RefreshAll event log.
}
reportBiz = null;
DAO.Dispose();
return(isSuccessful);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
var reportBiz = new ReportBiz(SPContext.Current.Site.ID);
if (reportBiz.SiteExists())
{
return;
}
NotConfiguredLabel.Visible = true;
ConfiguredLabel.Visible = false;
UpgradeButton.Enabled = false;
UpgradeButton.CssClass += " button-green-disabled";
}
else
{
TitleLiteral.Text = "Upgrade Reporting Database - Completed";
ConfiguredLabel.Visible = false;
UpgradeButton.Visible = false;
}
}
public void RaisePostBackEvent(string sAction)
{
string param = sAction.Remove(sAction.LastIndexOf("_"));
sAction = sAction.Substring(sAction.LastIndexOf("_") + 1);
switch (sAction)
{
case "cleanup":
CleanupLists(param);
break;
case "snapshot":
SnapshotLists(param);
break;
case "delete":
var reportBiz = new ReportBiz(SPContext.Current.Site.ID, SPContext.Current.Web.ID,
reportingV2Enabled);
var listId = new Guid(param);
_DAO.Command = "SELECT TableName FROM RPTList WHERE RPTListID=@RPTListID";
_DAO.AddParam("@RPTListID", param);
string sTableName = _DAO.ExecuteScalar(_DAO.GetClientReportingConnection).ToString();
DataTable refTables = reportBiz.GetReferencingTables(_DAO, sTableName);
if (refTables.Rows.Count == 0)
{
reportBiz.GetListBiz(new Guid(param)).Delete();
}
else
{
SPUtility.Redirect("epmlive/ListMappings.aspx?delete=true&id=" + param + "&name=" + sTableName,
SPRedirectFlags.RelativeToLayoutsPage, HttpContext.Current);
}
break;
}
SPUtility.Redirect("epmlive/ListMappings.aspx?", SPRedirectFlags.RelativeToLayoutsPage, HttpContext.Current);
}
private void AddReport(SPList oList, int listParentMessageId)
{
if (bVerifyOnly)
{
addMessage(ErrorLevels.NoError, "Add to Reporting Database", string.Empty, listParentMessageId);
}
else
{
try
{
var reportingV2Enabled = false;
try
{
reportingV2Enabled = Convert.ToBoolean(CoreFunctions.getConfigSetting(SPContext.Current.Site.RootWeb, "reportingV2"));
}
catch (Exception ex)
{
Trace.WriteLine(ex.ToString());
}
var reportBiz = new ReportBiz(oList.ParentWeb.Site.ID, oList.ParentWeb.ID, reportingV2Enabled);
var report = reportBiz.GetListBiz(oList.ID);
if (string.IsNullOrEmpty(report.ListName))
{
reportBiz.CreateListBiz(oList.ID);
addMessage(ErrorLevels.NoError, "Add to Reporting Database", string.Empty, listParentMessageId);
}
}
catch (Exception ex)
{
addMessage(ErrorLevels.Error, "Add to Reporting Database", ex.Message, listParentMessageId);
Trace.WriteLine(ex.ToString());
}
}
}
public override bool Perform()
{
storeurl = CoreFunctions.getFarmSetting("workenginestore");
solutions = (SPDocumentLibrary)base.SPWeb.Site.GetCatalog(SPListTemplateType.ListTemplateCatalog);
LogMessage("Activating List Features");
SPSite.Features.Add(new Guid("e08e676e-81fb-497e-9590-9d1c2673b85c"), true);
//==============================================================
LogMessage("Removing old settings list");
try
{
SPList settings = SPWeb.Lists.TryGetList("EPM Live Settings");
if (settings != null)
{
settings.Delete();
}
}
catch (Exception ex)
{
LogMessage("", ex.Message, 3);
}
//==============================================================
LogMessage("Downloading and Installing List Templates");
DownloadAndInstallList("Departments", "Departments", "Departments");
DownloadAndInstallList("HolidaySchedules", "HolidaySchedules", "Holiday Schedules");
DownloadAndInstallList("Holidays", "Holidays", "Holidays");
if (base.bIsPfe)
{
DownloadAndInstallList("EPM Live Settings", "pfe epm live settings", "EPM Live Settings");
}
else
{
DownloadAndInstallList("EPM Live Settings", "epm live settings", "EPM Live Settings");
}
DownloadAndInstallList("Roles", "Roles", "Roles");
DownloadAndInstallList("WorkHours", "WorkHours", "Work Hours");
DownloadAndInstallList("Time Off", "TimeOff", "Time Off");
//==============================================================
LogMessage("Checking Holidays Lookup");
SPList oHolidays = SPWeb.Lists.TryGetList("Holidays");
SPList oHolidaySchedules = SPWeb.Lists.TryGetList("Holiday Schedules");
SPList oResourcePool = SPWeb.Lists.TryGetList("Resources");
if (oHolidays == null)
{
LogMessage("", "Holidays list missing", 3);
}
else if (oHolidaySchedules == null)
{
LogMessage("", "Holiday Schedules list missing", 3);
}
else
{
SPField oField = null;
try
{
oField = oHolidays.Fields.GetFieldByInternalName("HolidaySchedule");
}
catch { }
if (oField == null)
{
LogMessage("\tCreating Holidays Lookup");
oField = oHolidays.Fields.GetFieldByInternalName(oHolidays.Fields.AddLookup("HolidaySchedule", oHolidaySchedules.ID, true));
oField.Title = "Holiday Schedule";
oField.Update();
var spFieldLookup = (SPFieldLookup)oField;
spFieldLookup.LookupField = "Title";
spFieldLookup.Update();
}
else
{
LogMessage("\tUpdating Holidays Lookup");
SPFieldLookup oLookup = (SPFieldLookup)oField;
if (new Guid(oLookup.LookupList) != oHolidaySchedules.ID)
{
oField.Delete();
oHolidays.Update();
oField = oHolidays.Fields.GetFieldByInternalName(oHolidays.Fields.AddLookup("HolidaySchedule", oHolidaySchedules.ID, true));
oField.Title = "Holiday Schedule";
oField.Update();
var spFieldLookup = (SPFieldLookup)oField;
spFieldLookup.LookupField = "Title";
spFieldLookup.Update();
}
}
oHolidays.Update();
}
//Fixing Department Lookup
SPList oDepartments = SPWeb.Lists.TryGetList("Departments");
if (oDepartments == null)
{
LogMessage("", "Departments list missing", 3);
}
else
{
LogMessage("Departments list lookups");
try
{
SPFieldLookup lookup = (SPFieldLookup)oDepartments.Fields.GetFieldByInternalName("RBS");
if (new Guid(lookup.LookupList) != oDepartments.ID)
{
bool bSealed = lookup.Sealed;
if (bSealed)
{
lookup.Sealed = false;
lookup.Update();
}
lookup.AllowDeletion = true;
lookup.Update();
lookup.Delete();
oDepartments.Fields.AddLookup("RBS", oDepartments.ID, false);
lookup = (SPFieldLookup)oDepartments.Fields.GetFieldByInternalName("RBS");
lookup.LookupField = "DisplayName";
lookup.Title = "Parent Department";
lookup.Update();
if (bSealed)
{
lookup.Sealed = true;
lookup.Update();
}
LogMessage("\tField RBS");
}
}
catch (Exception ex)
{
LogMessage("", "Field RBS: " + ex.Message, 3);
}
try
{
SPFieldLookup lookup = (SPFieldLookup)oDepartments.Fields.GetFieldByInternalName("Managers");
if (new Guid(lookup.LookupList) != oResourcePool.ID)
{
bool bSealed = lookup.Sealed;
if (bSealed)
{
lookup.Sealed = false;
lookup.Update();
}
lookup.AllowDeletion = true;
lookup.Update();
lookup.Delete();
oDepartments.Fields.AddLookup("Managers", oResourcePool.ID, true);
lookup = (SPFieldLookup)oDepartments.Fields.GetFieldByInternalName("Managers");
lookup.LookupField = "Title";
lookup.Update();
if (bSealed)
{
lookup.Sealed = true;
lookup.Update();
}
LogMessage("\tField Managers");
}
}
catch (Exception ex)
{
LogMessage("", "Field Managers: " + ex.Message, 3);
}
try
{
SPFieldLookup lookup = (SPFieldLookup)oDepartments.Fields.GetFieldByInternalName("Executives");
if (new Guid(lookup.LookupList) != oResourcePool.ID)
{
bool bSealed = lookup.Sealed;
if (bSealed)
{
lookup.Sealed = false;
lookup.Update();
}
lookup.AllowDeletion = true;
lookup.Update();
lookup.Delete();
oDepartments.Fields.AddLookup("Executives", oResourcePool.ID, false);
lookup = (SPFieldLookup)oDepartments.Fields.GetFieldByInternalName("Executives");
lookup.LookupField = "Title";
lookup.Update();
if (bSealed)
{
lookup.Sealed = true;
lookup.Update();
}
LogMessage("\tField Executives");
}
}
catch (Exception ex)
{
LogMessage("", "Field Executives: " + ex.Message, 3);
}
}
LogMessage("Processing Time Off Temp Data");
SPList oNonWork = SPWeb.Lists.TryGetList("Non Work");
SPList oTimeOff = SPWeb.Lists.TryGetList("Time Off");
if (oNonWork == null)
{
LogMessage("", "Non Work list missing", 3);
}
else if (oTimeOff == null)
{
LogMessage("", "Time Off list missing", 3);
}
else
{
if (!oTimeOff.Fields.ContainsFieldWithInternalName("TempType"))
{
oTimeOff.Fields.Add("TempType", SPFieldType.Text, false);
oTimeOff.Update();
try
{
SPField oField = oTimeOff.Fields.GetFieldByInternalName("TempType");
oField.ShowInDisplayForm = false;
oField.ShowInEditForm = false;
oField.ShowInNewForm = false;
oField.Update();
}
catch { }
}
SPField oTimeOffType = null;
try
{
oTimeOffType = oTimeOff.Fields.GetFieldByInternalName("TimeOffType");
}
catch { }
if (oTimeOffType != null && oTimeOffType.Type == SPFieldType.Choice)
{
LogMessage("\tProcessing Temp Items");
foreach (SPListItem li in oTimeOff.Items)
{
try
{
li["TempType"] = li[oTimeOffType.Id].ToString();
li.Update();
}
catch (Exception ex)
{
LogMessage("\t", li.Title + ": " + ex.Message, 3);
}
}
}
SPField oWorkDetail = null;
try
{
oWorkDetail = oTimeOff.Fields.GetFieldByInternalName("WorkDetail");
}
catch { }
if (oWorkDetail == null)
{
try
{
oTimeOff.Fields.AddFieldAsXml(@"<Field Type=""DaysHoursBreakdownField"" DisplayName=""WorkDetail"" Required=""FALSE"" EnforceUniqueValues=""FALSE"" StaticName=""WorkDetail"" Name=""WorkDetail"" ><Customization><ArrayOfProperty><Property><Name>StartDateField</Name><Value xmlns:q1=""http://www.w3.org/2001/XMLSchema"" p4:type=""q1:string"" xmlns:p4=""http://www.w3.org/2001/XMLSchema-instance"">StartDate</Value></Property><Property><Name>FinishDateField</Name><Value xmlns:q2=""http://www.w3.org/2001/XMLSchema"" p4:type=""q2:string"" xmlns:p4=""http://www.w3.org/2001/XMLSchema-instance"">DueDate</Value></Property><Property><Name>HoursField</Name><Value xmlns:q3=""http://www.w3.org/2001/XMLSchema"" p4:type=""q3:string"" xmlns:p4=""http://www.w3.org/2001/XMLSchema-instance"">Work</Value></Property><Property><Name>HolidaySchedulesField</Name><Value xmlns:q4=""http://www.w3.org/2001/XMLSchema"" p4:type=""q4:string"" xmlns:p4=""http://www.w3.org/2001/XMLSchema-instance"">HolidaySchedule</Value></Property><Property><Name>ResourcePoolList</Name><Value xmlns:q5=""http://www.w3.org/2001/XMLSchema"" p4:type=""q5:string"" xmlns:p4=""http://www.w3.org/2001/XMLSchema-instance"">Resources</Value></Property><Property><Name>WorkHoursList</Name><Value xmlns:q6=""http://www.w3.org/2001/XMLSchema"" p4:type=""q6:string"" xmlns:p4=""http://www.w3.org/2001/XMLSchema-instance"">Work Hours</Value></Property><Property><Name>HolidaysList</Name><Value xmlns:q7=""http://www.w3.org/2001/XMLSchema"" p4:type=""q7:string"" xmlns:p4=""http://www.w3.org/2001/XMLSchema-instance"">Holidays</Value></Property></ArrayOfProperty></Customization></Field>");
SPField oField = oTimeOff.Fields.GetFieldByInternalName("WorkDetail");
oField.Title = "Work Detail";
oField.Update();
LogMessage("\tAdd Work Detail Field");
}
catch (Exception ex)
{
LogMessage("", "Add Work Detail: " + ex.Message, 3);
}
}
}
try
{
if (!oResourcePool.Fields.ContainsFieldWithInternalName("EXTID"))
{
oResourcePool.Fields.Add("EXTID", SPFieldType.Text, false);
SPField oField = oResourcePool.Fields.GetFieldByInternalName("EXTID");
oField.Hidden = true;
oField.Update();
LogMessage("Add EXTID Field to Resources");
}
}
catch (Exception ex)
{
LogMessage("", "Add EXTID Field to Resources: " + ex.Message, 3);
}
//Process dept event
LogMessage("Processing Department Events");
try
{
bool badding = false;
bool bupdating = false;
bool bdeleted = false;
string sClass = "EPMLiveCore.DepartmentEvent";
string sAssembly = "EPM Live Core, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9f4da00116c38ec5";
foreach (SPEventReceiverDefinition e in oDepartments.EventReceivers)
{
if (e.Assembly == sAssembly && e.Class == sClass)
{
if (e.Type == SPEventReceiverType.ItemAdding)
{
badding = true;
}
else if (e.Type == SPEventReceiverType.ItemUpdating)
{
bupdating = true;
}
else if (e.Type == SPEventReceiverType.ItemDeleted)
{
bdeleted = true;
}
}
}
if (!badding)
{
oDepartments.EventReceivers.Add(SPEventReceiverType.ItemAdding, sAssembly, sClass);
oDepartments.Update();
}
if (!bupdating)
{
oDepartments.EventReceivers.Add(SPEventReceiverType.ItemUpdating, sAssembly, sClass);
oDepartments.Update();
}
if (!bdeleted)
{
oDepartments.EventReceivers.Add(SPEventReceiverType.ItemDeleted, sAssembly, sClass);
oDepartments.Delete();
}
}
catch (Exception ex)
{
LogMessage("", ex.Message, 3);
}
LogMessage("Mapping Reporting lists.");
var reportData = new ReportData(SPSite.ID);
DataTable dbMappings = reportData.GetDbMappings();
if (!dbMappings.Select(string.Format("SiteId = '{0}'", SPSite.ID)).Any())
{
LogMessage("", "Reporting is not configured.", 2);
}
else
{
try
{
var reportBiz = new ReportBiz(SPSite.ID, SPSite.WebApplication.Id);
reportBiz.GetDatabaseMappings();
foreach (var list in new[] { "Work Hours", "Holiday Schedules", "Holidays", "Time Off" })
{
try
{
LogMessage("Mapping " + list + " list to Reporting.");
SPList spList = SPWeb.Lists[list];
ListBiz listBiz = reportBiz.CreateListBiz(spList.ID);
if (string.IsNullOrEmpty(listBiz.ListName))
{
reportBiz.CreateListBiz(spList.ID);
LogMessage("", "Mapped successfully.", 1);
}
else
{
LogMessage("", "Already mapped.", 2);
}
}
catch (Exception ex)
{
LogMessage("", ex.Message, 3);
}
}
}
catch (Exception ex)
{
LogMessage("", ex.Message, 3);
}
}
return(true);
}
protected void btnGenReport_Click(object sender, EventArgs e)
{
try
{
if (tdmStartDate.DateValue.Year.ToString() == "1" || tdmEndDate.DateValue.Year.ToString() == "1")
{
AppUtil.ClientAlert(Page, "กรุณากรอกวันที่ให้ครบถ้วน");
return;
}
if (tdmStartDate.DateValue.Year.ToString() != "1" && tdmEndDate.DateValue.Year.ToString() != "1")
{
if (tdmStartDate.DateValue > tdmEndDate.DateValue)
{
AppUtil.ClientAlert(Page, "วันที่เริ่มต้นต้องน้อยกว่าหรือเท่ากับวันที่สิ้นสุด");
return;
}
}
if (tdmStartDate.DateValue > DateTime.Today || tdmEndDate.DateValue > DateTime.Today)
{
AppUtil.ClientAlert(Page, "ไม่สามารถเลือกช่วงเวลาเกินวันปัจจุบันได้");
return;
}
TimeSpan ts = tdmEndDate.DateValue.Subtract(tdmStartDate.DateValue);
if ((ts.TotalDays + 1) > AppConstant.SnapReportDateRange)
{
AppUtil.ClientAlert(Page, "ไม่สามารถเลือกช่วงเวลาได้เกิน " + AppConstant.SnapReportDateRange + " วัน");
return;
}
string datefrom = tdmStartDate.DateValue.Year.ToString() + tdmStartDate.DateValue.ToString("-MM-dd");
string dateto = tdmEndDate.DateValue.Year.ToString() + tdmEndDate.DateValue.ToString("-MM-dd");
Int64 countrow = 0;
if (rbAppReport.Checked == true)
{
countrow = ReportBiz.CheckReportWorkDetailExist(datefrom, dateto);
if (countrow == 0)
{
AppUtil.ClientAlert(Page, "ไม่พบข้อมูล");
return;
}
else
{
string script = "window.open('" + Page.ResolveUrl("~/ExportReport.aspx?reportflag=RPT_WORKDETAIL&countrows=" + countrow + "&datefrom=" + datefrom + "&dateto=" + dateto) + "', 'reports', 'status=yes, toolbar=no, scrollbars=no, menubar=no, width=600, height=400, resizable=no');";
//string script = "window.open('" + (Request.ApplicationPath.EndsWith("/") ? Request.ApplicationPath : Request.ApplicationPath + "/") + "ExportReport.aspx?reportflag=RPT_WORKDETAIL&countrows=" + countrow + "&datefrom=" + datefrom + "&dateto=" + dateto + "', 'reports', 'status=yes, toolbar=no, scrollbars=no, menubar=no, width=800, height=600, resizable=no');";
ScriptManager.RegisterStartupScript(Page, GetType(), "reportgeneral", script, true);
}
}
else if (rbAccessReport.Checked == true)
{
countrow = ReportBiz.CheckReportStaffWorkingExist(datefrom, dateto);
if (countrow == 0)
{
AppUtil.ClientAlert(Page, "ไม่พบข้อมูล");
return;
}
else
{
string script = "window.open('" + Page.ResolveUrl("~/ExportReport.aspx?reportflag=RPT_STAFF_WORKING&countrows=" + countrow + "&datefrom=" + datefrom + "&dateto=" + dateto) + "', 'reports', 'status=yes, toolbar=no, scrollbars=no, menubar=no, width=600, height=400, resizable=no');";
//string script = "window.open('" + (Request.ApplicationPath.EndsWith("/") ? Request.ApplicationPath : Request.ApplicationPath + "/") + "ExportReport.aspx?reportflag=RPT_STAFF_WORKING&countrows=" + countrow + "&datefrom=" + datefrom + "&dateto=" + dateto + "', 'reports', 'status=yes, toolbar=no, scrollbars=no, menubar=no, width=800, height=600, resizable=no');";
ScriptManager.RegisterStartupScript(Page, GetType(), "reportgeneral", script, true);
}
}
}
catch (Exception ex)
{
string message = ex.InnerException != null ? ex.InnerException.Message : ex.Message;
_log.Debug(message);
AppUtil.ClientAlert(Page, message);
}
}
public static DataSet GetReportingData(SPWeb web, string list, bool bRollup, string query, string orderby, int page, int pagesize)
{
var reportBiz = new ReportBiz(web.Site.ID);
if (reportBiz.SiteExists())
{
var oList = web.Lists[list];
EPMData data = null;
try
{
data = new EPMData(web.Site.ID);
var clientReportingConnection = data.GetClientReportingConnection;
var borderBy = false;
using (var sqlCommand = new SqlCommand(
"select * from information_schema.parameters where specific_name='spGetReportListData' and parameter_name='@orderby'",
clientReportingConnection))
{
using (var reader = sqlCommand.ExecuteReader())
{
if (reader.Read())
{
borderBy = true;
}
}
}
var dataSet = new DataSet();
using (var sqlCommand = new SqlCommand("spGetReportListData", clientReportingConnection))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.AddWithValue("@siteid", web.Site.ID);
sqlCommand.Parameters.AddWithValue("@webid", web.ID);
sqlCommand.Parameters.AddWithValue("@weburl", web.ServerRelativeUrl);
sqlCommand.Parameters.AddWithValue("@userid", web.CurrentUser.ID);
sqlCommand.Parameters.AddWithValue("@rollup", bRollup);
sqlCommand.Parameters.AddWithValue("@list", list);
sqlCommand.Parameters.AddWithValue("@query", query);
sqlCommand.Parameters.AddWithValue("@pagesize", pagesize);
sqlCommand.Parameters.AddWithValue("@page", page);
sqlCommand.Parameters.AddWithValue("@listid", oList.ID);
if (borderBy)
{
sqlCommand.Parameters.AddWithValue("@orderby", orderby);
}
using (var dataAdapter = new SqlDataAdapter(sqlCommand))
{
dataAdapter.Fill(dataSet);
}
}
return(dataSet);
}
catch (Exception ex)
{
Trace.WriteLine(ex.ToString());
throw;
}
finally
{
if (data != null)
{
data.Dispose();
}
}
}
else
{
throw new Exception("Reporting Not Setup.");
}
}
public ActionResult SearchReportA(SearchInfo searchInfo)
{
ReportBiz biz = new ReportBiz();
return(Json(biz.SearchReportA(searchInfo)));
}
public ActionResult SearchTransactionSummary(SearchInfo searchInfo)
{
ReportBiz biz = new ReportBiz();
return(Json(biz.SearchReportTransactionSummary(searchInfo)));
}
private bool UpdateField(SPListEventProperties properties)
{
bool isSuccessful = true;
string tableName = string.Empty;
string ssTableName = string.Empty;
var rb = new ReportBiz(properties.SiteId);
bool isUpdateRequire = false;
try
{
var rd = new ReportData(properties.SiteId);
var cols = new ColumnDefCollection();
// Actual table name
tableName = rd.GetTableName(properties.ListId);
// Snapshot table name
ssTableName = rd.GetTableNameSnapshot(properties.ListId);
// Create column definition collection to process with
cols.AddColumn(properties.Field);
var DAO = new EPMData(properties.SiteId);
// Update only when field type is changed from single to multi value and vice-verse
if (IsUpdateRequire(DAO, properties, tableName, cols[0].SqlColumnName))
{
isUpdateRequire = true;
// Get Foreign Key
DataTable foreignKeyTable = rb.GetSpecificForeignKey(DAO, properties.ListId.ToString(), tableName, cols[0].SqlColumnName);
// Delete Foreign Key
if (foreignKeyTable.Rows.Count > 0)
{
rb.AddORRemoveForeignKey(DAO, foreignKeyTable.Rows[0], false);
}
// Delete & Add Field
rd.UpdateColumns(tableName, cols);
rd.UpdateColumns(ssTableName, cols);
rd.UpdateListColumns(properties.ListId, cols);
// Add Foreign Key only if lookup type is NOT allowed multi select
if (foreignKeyTable.Rows.Count > 0 && !properties.Field.TypeAsString.Equals("LookupMulti", StringComparison.InvariantCultureIgnoreCase))
{
rb.AddORRemoveForeignKey(DAO, foreignKeyTable.Rows[0], true);
}
}
DAO.Dispose();
rd.Dispose();
}
catch (Exception ex)
{
var DAO = new EPMData(properties.SiteId);
DAO.LogStatus(properties.ListId.ToString(), properties.FieldName,
"Database column update attempt: Unable to update column " + properties.FieldName + ". " + ex.Message,
ex.StackTrace, 2, 5, Guid.NewGuid().ToString()); //Logged in the RefreshAll event log.
DAO.Dispose();
}
if (properties.Field is SPFieldLookup && isUpdateRequire)
{
#region Update all foreign keys
try
{
//FOREIGN IMPLEMENTATION -- START
var DAO = new EPMData(properties.SiteId);
rb.UpdateForeignKeys(DAO);
DAO.Dispose();
// -- END
}
catch (Exception ex)
{
isSuccessful = false;
SPSecurity.RunWithElevatedPrivileges(delegate
{
if (!EventLog.SourceExists("EPMLive Reporting - UpdateForeignKeys"))
{
EventLog.CreateEventSource("EPMLive Reporting - UpdateForeignKeys", "EPM Live");
}
LogEntry(ex, "EPMLive Reporting - UpdateForeignKeys");
});
}
#endregion
#region Cleanup list after field data type update to retain value
try
{
// List clean up -- START
var _DAO = new EPMData(properties.SiteId);
CleanupListAfterFieldUpdate(_DAO, properties.ListTitle);
// -- END
}
catch (Exception ex)
{
isSuccessful = false;
SPSecurity.RunWithElevatedPrivileges(delegate
{
if (!EventLog.SourceExists("EPMLive Reporting - CleanupListAfterFieldUpdate"))
{
EventLog.CreateEventSource("EPMLive Reporting - CleanupListAfterFieldUpdate", "EPM Live");
}
LogEntry(ex, "EPMLive Reporting - CleanupListAfterFieldUpdate");
});
}
#endregion
}
return(isSuccessful);
}