public static void UpdateRecords()
{
var siftaDB = new SiftaDBDataContext();
foreach (var agreement in siftaDB.Agreements.Where(p => p.RecordID == null))
{
agreement.RecordID = RecordIdentifier(agreement);
siftaDB.SubmitChanges();
}
foreach (var mod in siftaDB.AgreementMods.Where(p => p.RecordID == null))
{
mod.RecordID = RecordIdentifier(mod);
siftaDB.SubmitChanges();
}
foreach (var s in siftaDB.FundingSites.Where(p => p.RecordID == null))
{
s.RecordID = RecordIdentifier(s);
siftaDB.SubmitChanges();
}
foreach (var s in siftaDB.FundingStudies.Where(p => p.RecordID == null))
{
s.RecordID = RecordIdentifier(s);
siftaDB.SubmitChanges();
}
}
public static void Delete(this Customer _customer, bool StoreValues = false)
{
var siftaDB = new SiftaDBDataContext();
if (StoreValues)
{
var address = String.Format("http://sifta.water.usgs.gov/Services/REST/Customer/CustomerInformation.ashx?CustomerID={0}&AllRecords=true", _customer.CustomerID);
System.Net.WebClient wc = new System.Net.WebClient();
var xml = wc.DownloadString(address);
siftaDB.Archives.InsertOnSubmit(new Archive()
{
Type = "Customer", CreatedBy = new User().ID, CreatedOn = DateTime.Now, Data = xml
});
}
var customer = siftaDB.Customers.FirstOrDefault(p => p.CustomerID == _customer.CustomerID);
//Delete all Agreements
foreach (var agreement in customer.Agreements)
{
//Delete the agreement
Delete(agreement, false);
}
//Delete Contacts
foreach (var contact in customer.CustomerContacts)
{
foreach (var address in contact.CustomerContactAddresses)
{
siftaDB.CustomerContactAddresses.DeleteOnSubmit(address);
}
siftaDB.CustomerContacts.DeleteOnSubmit(contact);
}
siftaDB.Customers.DeleteOnSubmit(customer);
siftaDB.SubmitChanges();
}
public static string RecordIdentifier(this FundingStudy fs)
{
SiftaDBDataContext siftaDB = new SiftaDBDataContext();
string id;
do
{
id = String.Format("SIFTA-{0}RF{1}", fs.FundingStudyID, RandomNumbers).Substring(0, RecordIDSize);
} while (siftaDB.FundingStudies.FirstOrDefault(p => p.RecordID == id) != null);
return(id);
}
public static string RecordIdentifier(this AgreementMod am)
{
SiftaDBDataContext siftaDB = new SiftaDBDataContext();
string id;
do
{
id = String.Format("SIFTA-{0}M{1}", am.AgreementModID, RandomNumbers).Substring(0, RecordIDSize);
} while (siftaDB.AgreementMods.FirstOrDefault(p => p.RecordID == id) != null);
return(id);
}
public ExcelFundingOverviewReport(List <vReportFundingOverview> data, String OrgCode)
{
_data = data;
_orgCode = OrgCode;
SiftaDBDataContext siftaDB = new SiftaDBDataContext();
var center = siftaDB.Centers.FirstOrDefault(p => p.OrgCode == _orgCode);
if (center != null)
{
CenterName = center.State + "_WSC";
}
}
public static bool ValidEmployeeID(this string employeeID)
{
SiftaDBDataContext siftaDB = new SiftaDBDataContext();
//Check to see if they aren't being tracked yet.
if (siftaDB.Employees.FirstOrDefault(p => p.EmployeeID == employeeID) == null)
{
//Call the service for Active Directory
var service = new ActiveDirectoryService();
//Try to get the employee with the same id
var employee = service.GetEmployee(employeeID);
//Check to see that the employee is not null
if (employee != null)
{
var e = new Employee()
{
FirstName = employee.FirstName,
MiddleName = employee.MiddleName,
LastName = employee.LastName,
OrgCode = employee.OrgCode,
EmployeeID = employee.EmployeeID,
StreetOne = employee.StreetOne,
StreetTwo = employee.StreetTwo,
City = employee.City,
State = employee.State,
ZipCode = employee.ZipCode,
Department = employee.Department,
Email = employee.Email,
Title = employee.Title,
PhoneFax = employee.PhoneFax,
PhoneWork = employee.PhoneWork
};
siftaDB.Employees.InsertOnSubmit(e);
siftaDB.SubmitChanges();
return(true);
}
//Employee is not real return false
else
{
return(false);
}
}
else
{
return(true);
}
}
public static void Delete(this Agreement agreement, bool StoreValues = false)
{
var siftaDB = new SiftaDBDataContext();
var _agreement = siftaDB.Agreements.FirstOrDefault(p => p.AgreementID == agreement.AgreementID);
if (StoreValues)
{
var address = String.Format("http://sifta.water.usgs.gov/Services/REST/Agreement/AgreementInformation.ashx?AgreementID={0}", _agreement.AgreementID);
System.Net.WebClient wc = new System.Net.WebClient();
var xml = wc.DownloadString(address);
siftaDB.Archives.InsertOnSubmit(new Archive()
{
Type = "Agreement", CreatedBy = new User().ID, CreatedOn = DateTime.Now, Data = xml
});
}
//Go through each mod in the agreement
foreach (var mod in _agreement.AgreementMods)
{
//Delete all teh site funding
foreach (var site in mod.FundingSites)
{
siftaDB.FundingSites.DeleteOnSubmit(site);
}
//Delete all studies funding
foreach (var study in mod.FundingStudies)
{
siftaDB.FundingStudies.DeleteOnSubmit(study);
}
//Delete all logs
foreach (var log in mod.AgreementModLogs)
{
siftaDB.AgreementModLogs.DeleteOnSubmit(log);
}
//Delete all coop funding
foreach (var coop in mod.CooperativeFundings)
{
siftaDB.CooperativeFundings.DeleteOnSubmit(coop);
}
siftaDB.AgreementMods.DeleteOnSubmit(mod);
}
siftaDB.Agreements.DeleteOnSubmit(_agreement);
siftaDB.SubmitChanges();
}
public static void AddLinks(this Telerik.Web.UI.RadMenu menu)
{
menu.DataBind();
SiftaDBDataContext siftaDB = new SiftaDBDataContext();
User user;
String OrgCode;
var reportsItem = menu.Items.FirstOrDefault(p => p.Text == "Reports");
if (!String.IsNullOrEmpty(HttpContext.Current.Request.QueryString["OrgCode"]))
{
OrgCode = HttpContext.Current.Request.QueryString["OrgCode"];
user = new User(OrgCode);
reportsItem.Items.Add(new Telerik.Web.UI.RadMenuItem()
{
Text = "Center", NavigateUrl = String.Format("~/Reports/Center/CenterReport.aspx?OrgCode={0}", OrgCode)
});
reportsItem.Items.Add(new Telerik.Web.UI.RadMenuItem()
{
Text = "Coop Funding", NavigateUrl = String.Format("~/Reports/Center/CoopFunding.aspx?OrgCode={0}", OrgCode)
});
if (user.AdminPortalVisible)
{
menu.Items.Add(new Telerik.Web.UI.RadMenuItem()
{
Text = "Center Admin", NavigateUrl = String.Format("~/Admin.aspx?OrgCode={0}", OrgCode), OuterCssClass = "adminMenuItem"
});
}
if (user.IsSuperUser)
{
AddSiftaAdminLinks(menu);
}
}
else if (!String.IsNullOrEmpty(HttpContext.Current.Request.QueryString["CustomerID"]))
{
var CustomerID = Convert.ToInt32(HttpContext.Current.Request.QueryString["CustomerID"]);
OrgCode = siftaDB.Customers.FirstOrDefault(p => p.CustomerID == CustomerID).OrgCode;
user = new User(OrgCode);
reportsItem.Items.Add(new Telerik.Web.UI.RadMenuItem()
{
Text = "Center", NavigateUrl = String.Format("~/Reports/Center/CenterReport.aspx?OrgCode={0}", OrgCode)
});
if (user.AdminPortalVisible)
{
menu.Items.Add(new Telerik.Web.UI.RadMenuItem()
{
Text = "Center Admin", NavigateUrl = String.Format("~/Admin.aspx?OrgCode={0}", OrgCode), OuterCssClass = "adminMenuItem"
});
}
if (user.IsSuperUser)
{
AddSiftaAdminLinks(menu);
}
}
else if (!String.IsNullOrEmpty(HttpContext.Current.Request.QueryString["AgreementID"]))
{
var AgreementID = Convert.ToInt32(HttpContext.Current.Request.QueryString["AgreementID"]);
OrgCode = siftaDB.Agreements.FirstOrDefault(p => p.AgreementID == AgreementID).Customer.OrgCode;
user = new User(OrgCode);
reportsItem.Items.Add(new Telerik.Web.UI.RadMenuItem()
{
Text = "Center", NavigateUrl = String.Format("~/Reports/Center/CenterReport.aspx?OrgCode={0}", OrgCode)
});
reportsItem.Items.Add(new Telerik.Web.UI.RadMenuItem()
{
Text = "Agreement", NavigateUrl = AppendBaseURL(String.Format("Reports/Agreement/AgreementReport.aspx?AgreementID={0}", AgreementID))
});
if (user.AdminPortalVisible)
{
menu.Items.Add(new Telerik.Web.UI.RadMenuItem()
{
Text = "Center Admin", NavigateUrl = String.Format("~/Admin.aspx?OrgCode={0}", OrgCode), OuterCssClass = "adminMenuItem"
});
}
if (user.IsSuperUser)
{
AddSiftaAdminLinks(menu);
}
}
else if (!String.IsNullOrEmpty(HttpContext.Current.Request.QueryString["SalesOrderNumber"]))
{
var SalesOrderNumber = HttpContext.Current.Request.QueryString["SalesorderNumber"];
var agreement = siftaDB.Agreements.FirstOrDefault(p => p.SalesDocument == SalesOrderNumber);
OrgCode = agreement.Customer.OrgCode;
user = new User(OrgCode);
reportsItem.Items.Add(new Telerik.Web.UI.RadMenuItem()
{
Text = "Center", NavigateUrl = String.Format("~/Reports/Center/CenterReport.aspx?OrgCode={0}", OrgCode)
});
reportsItem.Items.Add(new Telerik.Web.UI.RadMenuItem()
{
Text = "Agreement", NavigateUrl = AppendBaseURL(String.Format("Reports/Agreement/AgreementReport.aspx?AgreementID={0}", agreement.AgreementID))
});
if (user.AdminPortalVisible)
{
menu.Items.Add(new Telerik.Web.UI.RadMenuItem()
{
Text = "Center Admin", NavigateUrl = String.Format("~/Admin.aspx?OrgCode={0}", OrgCode), OuterCssClass = "adminMenuItem"
});
}
if (user.IsSuperUser)
{
AddSiftaAdminLinks(menu);
}
}
else if (!String.IsNullOrEmpty(HttpContext.Current.Request.QueryString["PurchaseOrderNumber"]))
{
var PurchaseOrderNumber = HttpContext.Current.Request.QueryString["PurchaseOrderNumber"];
var agreement = siftaDB.Agreements.FirstOrDefault(p => p.PurchaseOrderNumber == PurchaseOrderNumber);
OrgCode = agreement.Customer.OrgCode;
user = new User(OrgCode);
reportsItem.Items.Add(new Telerik.Web.UI.RadMenuItem()
{
Text = "Center", NavigateUrl = String.Format("~/Reports/Center/CenterReport.aspx?OrgCode={0}", OrgCode)
});
reportsItem.Items.Add(new Telerik.Web.UI.RadMenuItem()
{
Text = "Agreement", NavigateUrl = AppendBaseURL(String.Format("Reports/Agreement/AgreementReport.aspx?AgreementID={0}", agreement.AgreementID))
});
if (user.AdminPortalVisible)
{
menu.Items.Add(new Telerik.Web.UI.RadMenuItem()
{
Text = "Center Admin", NavigateUrl = String.Format("~/Admin.aspx?OrgCode={0}", OrgCode), OuterCssClass = "adminMenuItem"
});
}
if (user.IsSuperUser)
{
AddSiftaAdminLinks(menu);
}
}
else
{
user = new User();
if (user.IsSuperUser)
{
AddSiftaAdminLinks(menu);
}
}
}
public void ProcessRequest(HttpContext context)
{
var siftaDB = new SiftaDBDataContext();
string agID = context.Request.QueryString["AgreementID"];
if (agID == null)
{
context.Response.Write("Invalid AgreementID");
}
var entries = siftaDB.vSiteFundingInformations.Where(x => x.AgreementID == int.Parse(agID));
var modID = siftaDB.Agreements.FirstOrDefault(x => x.AgreementID == int.Parse(agID));
var pID = modID.PurchaseOrderNumber;
//Create an excel package from the file stream
using (ExcelPackage package = new ExcelPackage())
{
//A workbook must have at least on cell, so lets add one...
var ws = package.Workbook.Worksheets.Add(pID);
//To set values in the spreadsheet use the Cells indexer.
ws.Cells["A1"].Value = "SiteName";
ws.Cells["B1"].Value = "SiteNumber";
ws.Cells["C1"].Value = "CollectionCode";
ws.Cells["D1"].Value = "Units";
ws.Cells["E1"].Value = "DifficultyFactor";
ws.Cells["F1"].Value = "USGSCMFFunding";
ws.Cells["G1"].Value = "CustomerFunds";
ws.Cells["H1"].Value = "StartDate";
ws.Cells["I1"].Value = "EndDate";
ws.Cells["J1"].Value = "Remarks";
int i = 2;
foreach (var entry in entries)
{
ws.Cells["A" + i].Value = entry.SiteName;
ws.Cells["B" + i].Value = entry.SiteNumber;
ws.Cells["C" + i].Value = entry.Code;
ws.Cells["D" + i].Value = entry.CollectionUnits;
ws.Cells["E" + i].Value = entry.DifficultyFactor;
ws.Cells["F" + i].Value = entry.FundingUSGSCMF;
ws.Cells["G" + i].Value = entry.FundingCustomer;
ws.Cells["H" + i].Value = entry.StartDate.ToString().Replace(" 12:00:00 AM", "");
ws.Cells["I" + i].Value = entry.EndDate.ToString().Replace(" 12:00:00 AM", "");
ws.Cells["J" + i].Value = entry.Remarks;
i++;
}
// Changed it to not depend on a D drive
var dir = new DirectoryInfo(context.Server.MapPath("~/Temporary"));
if (!dir.Exists)
{
dir.Create();
}
package.Save();
// Create an id for the temporary file
var id = Guid.NewGuid().ToString();
var tempPath = Path.Combine(dir.FullName, $"{id}.xlsx");
var tempFile = new FileInfo(tempPath);
package.SaveAs(tempFile);
//Write excel file to http web response
context.Response.Clear();
context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
context.Response.AddHeader("content-disposition", String.Format("attachment; filename=agreements" + agID + ".xlsx"));
context.Response.BinaryWrite(File.ReadAllBytes(tempFile.FullName));
context.Response.Flush();
// Added to clean up file after it has finished downloading
tempFile.Delete();
context.Response.End();
}
}
