public static DataTable GetContractReport(ReportParameters parameters)
{
DataTable dtReturn = new DataTable();
dtReturn.Columns.Add("Contract Name", typeof(string));
dtReturn.Columns["Contract Name"].ExtendedProperties["Width"] = "500px";
dtReturn.Columns.Add("Contract Description", typeof(string));
dtReturn.Columns["Contract Description"].ExtendedProperties["Width"] = "500px";
dtReturn.Columns.Add("Contract Start Date", typeof(DateTime));
dtReturn.Columns["Contract Start Date"].ExtendedProperties["DataFormatString"] = "{0:dd-MMM-yyyy}";
dtReturn.Columns.Add("Contract End Date", typeof(DateTime));
dtReturn.Columns["Contract End Date"].ExtendedProperties["DataFormatString"] = "{0:dd-MMM-yyyy}";
dtReturn.Columns.Add("Contract Terms", typeof(string));
dtReturn.Columns.Add("Contract Warranty", typeof(string));
dtReturn.Columns.Add("Contract Sum($)", typeof(decimal));
dtReturn.Columns.Add("Contract Manager", typeof(string));
dtReturn.Columns.Add("Contact Person Name", typeof(string));
dtReturn.Columns.Add("Contact Person Cellphone", typeof(string));
dtReturn.Columns.Add("Contact Person Email", typeof(string));
dtReturn.Columns.Add("Provide Adhoc Maintenance", typeof(string));
dtReturn.Columns.Add("Locations", typeof(string));
dtReturn.Columns["Locations"].ExtendedProperties["Width"] = "500px";
dtReturn.Columns.Add("Type of Services", typeof(string));
dtReturn.Columns["Type of Services"].ExtendedProperties["Width"] = "500px";
dtReturn.Columns.Add("Provide Purchasing Agreement", typeof(string));
dtReturn.Columns.Add("Materials Agreement", typeof(string));
dtReturn.Columns["Materials Agreement"].ExtendedProperties["Width"] = "500px";
dtReturn.Columns.Add("Service Agreement", typeof(string));
dtReturn.Columns["Service Agreement"].ExtendedProperties["Width"] = "800px";
dtReturn.Columns.Add("Vendor Name", typeof(string));
dtReturn.Columns.Add("Vendor Type", typeof(string));
dtReturn.Columns.Add("Vendor Classification", typeof(string));
dtReturn.Columns.Add("Contact Person Fax", typeof(string));
dtReturn.Columns.Add("Contact Person Phone", typeof(string));
dtReturn.Columns.Add("Person 1 To Remind", typeof(string));
dtReturn.Columns.Add("Person 2 To Remind", typeof(string));
dtReturn.Columns.Add("Person 3 To Remind", typeof(string));
dtReturn.Columns.Add("Person 4 To Remind", typeof(string));
dtReturn.Columns.Add("Reminder Days 1", typeof(int));
dtReturn.Columns.Add("Reminder Days 2", typeof(int));
dtReturn.Columns.Add("Reminder Days 3", typeof(int));
dtReturn.Columns.Add("Reminder Days 4", typeof(int));
ExpressionCondition cond = GetAccessibleCondition(parameters.GetString("UserID"),true, TablesLogic.tContract.Locations.HierarchyPath, "");
if (cond == null)
cond = Query.False;
DataTable dt = TablesLogic.tContract.SelectDistinct(TablesLogic.tContract.ObjectID,
TablesLogic.tContract.ObjectName,
TablesLogic.tContract.Description,
TablesLogic.tContract.ContractStartDate,
TablesLogic.tContract.ContractEndDate,
TablesLogic.tContract.Terms,
TablesLogic.tContract.Warranty,
TablesLogic.tContract.ContractSum,
TablesLogic.tContract.ContractManager.ObjectName.As("Manager"),
TablesLogic.tContract.ContactPerson,
TablesLogic.tContract.ContactCellphone,
TablesLogic.tContract.ContactPhone,
TablesLogic.tContract.ContactEmail,
TablesLogic.tContract.ContactFax,
TablesLogic.tContract.Reminder1User.ObjectName.As("User1"),
TablesLogic.tContract.Reminder2User.ObjectName.As("User2"),
TablesLogic.tContract.Reminder3User.ObjectName.As("User3"),
TablesLogic.tContract.Reminder4User.ObjectName.As("User4"),
TablesLogic.tContract.EndReminderDays1,
TablesLogic.tContract.EndReminderDays2,
TablesLogic.tContract.EndReminderDays3,
TablesLogic.tContract.EndReminderDays4,
TablesLogic.tContract.Vendor.ObjectName.As("Vendor"),
TablesLogic.tContract.Vendor.VendorClassification.ObjectName.As("VendorClassification"),
TablesLogic.tContract.ProvideMaintenance,
TablesLogic.tContract.ProvidePricingAgreement)
.Where(TablesLogic.tContract.IsDeleted == 0
& TablesLogic.tContract.ObjectName.Like("%" + parameters.GetString("FILTER_CONTRACTNAME") + "%")
& TablesLogic.tContract.Description.Like("%" + parameters.GetString("FILTER_DESCRIPTION") + "%")
& TablesLogic.tContract.Terms.Like("%" + parameters.GetString("FILTER_TERMS") + "%")
& TablesLogic.tContract.Insurance.Like("%" + parameters.GetString("FILTER_INSURANTCE") + "%")
& TablesLogic.tContract.Warranty.Like("%" + parameters.GetString("FILTER_WARRANTY") + "%")
& TablesLogic.tContract.Vendor.ObjectName.Like("%" + parameters.GetString("FILTER_VENDORNAME") + "%")
& (parameters.GetString("FILTER_CONTRACTSTARTFROM") == "" ? Query.True :
TablesLogic.tContract.ContractStartDate >= parameters.GetDateTime("FILTER_CONTRACTSTARTFROM"))
& (parameters.GetString("FILTER_CONTRACTSTARTTO") == "" ? Query.True :
TablesLogic.tContract.ContractStartDate < parameters.GetDateTime("FILTER_CONTRACTSTARTTO").Value.AddDays(1))
& (parameters.GetString("FILTER_CONTRACTENDFROM") == "" ? Query.True :
TablesLogic.tContract.ContractEndDate >= parameters.GetDateTime("FILTER_CONTRACTENDFROM"))
& (parameters.GetString("FILTER_CONTRACTENDTO") == "" ? Query.True :
TablesLogic.tContract.ContractEndDate < parameters.GetDateTime("FILTER_CONTRACTENDTO").Value.AddDays(1))
& (parameters.GetString("FILTER_CONTRACTSUMFROM") == "" ? Query.True :
TablesLogic.tContract.ContractSum >= parameters.GetDecimal("FILTER_CONTRACTSUMFROM"))
& (parameters.GetString("FILTER_CONTRACTSUMTO") == "" ? Query.True :
TablesLogic.tContract.ContractSum <= parameters.GetDecimal("FILTER_CONTRACTSUMTO"))
& (parameters.GetString("TreeviewID") == "" ? cond :
TablesLogic.tContract.Locations.HierarchyPath.Like(TablesLogic.tLocation.Load(new Guid(parameters.GetString("TreeviewID"))).HierarchyPath + "%"))
& (parameters.GetString("FILTER_CONTRACTEXPIRED") == "" ? Query.True :
TablesLogic.tContract.ContractEndDate <= DateTime.Now.AddMonths(parameters.GetInteger("FILTER_CONTRACTEXPIRED").Value))
& (parameters.GetString("FILTER_SHOWCLOSEDCONTRACT") == "" ? TablesLogic.tContract.CurrentActivity.ObjectName != "CONTRACT_CLOSED" :
Query.True)
& (parameters.GetString("FILTER_VENDORTYPE") == "" ? Query.True : TablesLogic.tContract.Vendor.VendorTypes.ObjectID == parameters.GetString("FILTER_VENDORTYPE"))
& (parameters.GetString("FILTER_VENDORCLASSIFICATION") == "" ? Query.True : TablesLogic.tContract.Vendor.VendorClassificationID == parameters.GetString("FILTER_VENDORCLASSIFICATION")));
foreach (DataRow row in dt.Rows)
{
DataRow returnRow = dtReturn.NewRow();
returnRow["Contract Name"] = row["ObjectName"];
returnRow["Contract Description"] = row["Description"];
returnRow["Contract Start Date"] = row["ContractStartDate"];
returnRow["Contract End Date"] = row["ContractEndDate"];
returnRow["Contract Terms"] = row["Terms"];
returnRow["Contract Warranty"] = row["Warranty"];
returnRow["Contract Sum($)"] = row["ContractSum"];
returnRow["Contract Manager"] = row["Manager"];
returnRow["Contact Person Name"] = row["ContactPerson"];
returnRow["Contact Person Cellphone"] = row["ContactCellphone"];
returnRow["Contact Person Email"] = row["ContactEmail"];
returnRow["Contact Person Fax"] = row["ContactFax"];
returnRow["Contact Person Phone"] = row["ContactPhone"];
returnRow["Person 1 To Remind"] = row["User1"];
returnRow["Person 2 To Remind"] = row["User2"];
returnRow["Person 3 To Remind"] = row["User3"];
returnRow["Person 4 To Remind"] = row["User4"];
returnRow["Reminder Days 1"] = row["EndReminderDays1"];
returnRow["Reminder Days 2"] = row["EndReminderDays2"];
returnRow["Reminder Days 3"] = row["EndReminderDays3"];
returnRow["Reminder Days 4"] = row["EndReminderDays4"];
returnRow["Vendor Name"] = row["Vendor"];
returnRow["Vendor Classification"] = row["VendorClassification"];
OContract contract = TablesLogic.tContract[new Guid(row["ObjectID"].ToString())];
string vendortypestring = "";
foreach (OCode codename in contract.Vendor.VendorTypes)
{
vendortypestring = vendortypestring + codename.ObjectName + ", ";
}
if (vendortypestring != "")
returnRow["Vendor Type"] = vendortypestring.Remove(vendortypestring.Length - 2);
//Retrieve all locations, type of services,. of the contract
if (row["ProvideMaintenance"] != DBNull.Value && row["ProvideMaintenance"].ToString() == "1")
{
returnRow["Provide Adhoc Maintenance"] = Resources.Strings.General_Yes;
string locations = "";
foreach (OLocation loc in contract.Locations)
{
if (locations == "")
locations = loc.Path;
else
locations += ", " + loc.Path;
}
returnRow["Locations"] = locations;
string services = "";
foreach (OCode code in contract.TypeOfServices)
{
if (services == "")
services = code.Path;
else
services += ", " + code.Path;
}
returnRow["Type of Services"] = services;
}
else
returnRow["Provide Adhoc Maintenance"] = Resources.Strings.General_No;
if (row["ProvidePricingAgreement"] != DBNull.Value && row["ProvidePricingAgreement"].ToString() == "1")
{
returnRow["Provide Purchasing Agreement"] = Resources.Strings.General_Yes;
string materials = "";
foreach (OContractPriceMaterial contractMat in contract.ContractPriceMaterials)
{
if (materials == "")
materials = contractMat.Catalogue.Path;
else
materials += ", " + contractMat.Catalogue.Path;
}
returnRow["Materials Agreement"] = materials;
string services = "";
foreach (OContractPriceService contractSev in contract.ContractPriceServices)
{
if (services == "")
services = contractSev.FixedRate.Path;
else
services += ", " + contractSev.FixedRate.Path;
}
returnRow["Service Agreement"] = services;
}
else
returnRow["Provide Purchasing Agreement"] = Resources.Strings.General_No;
dtReturn.Rows.Add(returnRow);
}
return dtReturn;
}
/// <summary>
/// caller history report
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataTable CallerHistoryReport(ReportParameters parameters)
{
DataTable table = new DataTable();
table.Columns.Add("Location");
table.Columns.Add("Equipment");
table.Columns.Add("Created Date", typeof(DateTime));
table.Columns.Add("Name");
table.Columns.Add("CellPhone");
table.Columns.Add("Email");
table.Columns.Add("Fax");
table.Columns.Add("Phone");
table.Columns.Add("Priority");
table.Columns.Add("Work Description");
table.Columns.Add("Work Type");
table.Columns.Add("Type of service");
table.Columns.Add("Fault Type");
table.Columns.Add("Cost");
table.Columns.Add("Chargeable Amount");
table.Columns.Add("Status");
table.Columns.Add("Rejection Reason");
//string strLocationHirachyPath = LocationHirachyPath(parameters.GetString("TreeviewID"));
DateTime? FILTER_STARTDATEFROM = parameters.GetDateTime("FILTER_STARTDATEFROM");
DateTime? FILTER_STARTDATETO = parameters.GetDateTime("FILTER_STARTDATETO");
DateTime? FILTER_ENDDATEFROM = parameters.GetDateTime("FILTER_ENDDATEFROM");
DateTime? FILTER_ENDDATETO = parameters.GetDateTime("FILTER_ENDDATETO");
DataTable tempTable = TablesLogic.tWork.Select(
TablesLogic.tWork.ObjectID,
TablesLogic.tWork.LocationID,
TablesLogic.tWork.CreatedDateTime,
TablesLogic.tWork.CallerName,
TablesLogic.tWork.CallerCellPhone,
TablesLogic.tWork.CallerEmail,
TablesLogic.tWork.CallerFax,
TablesLogic.tWork.CallerPhone,
TablesLogic.tWork.Priority,
TablesLogic.tWork.WorkDescription,
TablesLogic.tWork.TypeOfWork.ObjectName,
TablesLogic.tWork.TypeOfService.ObjectName,
TablesLogic.tWork.TypeOfProblem.ObjectName,
TablesLogic.tWork.CurrentActivity.ObjectName)
.Where(TablesLogic.tWork.IsDeleted == 0 &
// & TablesLogic.tWork.Location.HierarchyPath.Like(strLocationHirachyPath + "%")
(parameters.GetString("TreeviewID") == "" ? GetAccessibleCondition(parameters.GetString("UserID"),true, TablesLogic.tWork.Location.HierarchyPath, "") :
TablesLogic.tWork.Location.HierarchyPath.Like(TablesLogic.tLocation.Load(new Guid(parameters.GetString("TreeviewID"))).HierarchyPath + "%"))
& (FILTER_STARTDATEFROM <= DateTime.Parse("1/1/1855") ? Query.True :
TablesLogic.tWork.CreatedDateTime >= parameters.GetDateTime("FILTER_STARTDATEFROM"))
& (FILTER_STARTDATETO <= DateTime.Parse("1/1/1855") ? Query.True :
TablesLogic.tWork.CreatedDateTime < parameters.GetDateTime("FILTER_STARTDATETO").Value.AddDays(1))
& TablesLogic.tWork.CallerName.Like("%" + parameters.GetString("FILTER_CALLERNAME") + "%")
);
foreach (DataRow row in tempTable.Rows)
{
OWork work = TablesLogic.tWork[new Guid(row["ObjectID"].ToString())];
switch (parameters.GetInteger("FILTER_CHARGEABLEAMOUNT"))
{
case 1: if ((parameters.GetDecimal("FILTER_COST") == null ? true : work.TotalActualCost == parameters.GetDecimal("FILTER_COST")) && work.TotalChargeOut < 2000)
table.Rows.Add(new object[] { row[1].ToString()==null?"":LocationPath(new Guid(row[1].ToString())),
row[2].ToString()==""?"":EquipmentPath(new Guid(row[2].ToString())),
row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13],
work.TotalActualCost, work.TotalChargeOut, TranslateWorkStatusToString(row[14].ToString()), row[14].ToString()=="WORK_REJECTED" ? row[15].ToString() : ""});
break;
case 2: if ((parameters.GetDecimal("FILTER_COST") == null ? true : work.TotalActualCost == parameters.GetDecimal("FILTER_COST")) && work.TotalChargeOut <= 5000 && work.TotalChargeOut >= 2000)
table.Rows.Add(new object[] { row[1].ToString()==null?"":LocationPath(new Guid(row[1].ToString())),
row[2].ToString()==""?"":EquipmentPath(new Guid(row[2].ToString())),
row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13],
work.TotalActualCost, work.TotalChargeOut, TranslateWorkStatusToString(row[14].ToString()), row[14].ToString()=="WORK_REJECTED" ? row[15].ToString() : ""});
break;
case 3: if ((parameters.GetDecimal("FILTER_COST") == null ? true : work.TotalActualCost == parameters.GetDecimal("FILTER_COST")) && work.TotalChargeOut > 5000)
table.Rows.Add(new object[] { row[1].ToString()==null?"":LocationPath(new Guid(row[1].ToString())),
row[2].ToString()==""?"":EquipmentPath(new Guid(row[2].ToString())),
row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13],
work.TotalActualCost, work.TotalChargeOut, TranslateWorkStatusToString(row[14].ToString()), row[14].ToString()=="WORK_REJECTED" ? row[15].ToString() : ""});
break;
default: if (parameters.GetDecimal("FILTER_COST") == null ? true : work.TotalActualCost == parameters.GetDecimal("FILTER_COST"))
table.Rows.Add(new object[] { row[1].ToString()==null?"":LocationPath(new Guid(row[1].ToString())),
row[2].ToString()==""?"":EquipmentPath(new Guid(row[2].ToString())),
row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13],
work.TotalActualCost, work.TotalChargeOut, TranslateWorkStatusToString(row[14].ToString()), row[14].ToString()=="WORK_REJECTED" ? row[15].ToString() : ""});
break;
}
}
return table;
}