protected override void FillDataTable(DataTable dt)
{
BillingUnit summaryUnit = summaryUnits.First();
Compile mCompile = new Compile();
DataTable dtRoomDB = mCompile.CalcCost("Room", string.Empty, "ChargeTypeID", 5, EndPeriod.AddMonths(-1), 0, 0, Compile.AggType.CliAcctType);
DataTable dtClientWithCharges = mCompile.GetTable(1);
double roomCapCost = mCompile.CapCost;
//*****************************************************************************
//2008-01-22 The code below is an EXTRA step for calculating the cost of room charge
// Right now the strategy is not to change Compile.CalcCost at all and if I want to
// add new features that would affect CalcCost, I would rather do it after CalcCost is called.
// But future new design is required else the system will get too complicated.
//2208-05-15 the reason why we are doing this extra step is to show NAP rooms (as of now, it's DC Test lab and Chem room)
//with correct monthly fee on the JE
//dtNAPRoomForAllChargeType's columns
//CostID
//ChargeTypeID
//TableNameOrDescript
//RoomID
//AcctPer
//AddVal
//RoomCost
//effDate
//Get all active NAP Rooms with their costs, all chargetypes are returned
//This is a temporary table, it's used to derive the really useful table below
DataTable dtNAPRoomForAllChargeType = BLL.RoomManager.GetAllNAPRoomsWithCosts(EndPeriod);
//filter out the chargetype so that we only have Internal costs with each NAP room
DataRow[] drsNAPRoomForInternal = dtNAPRoomForAllChargeType.Select("ChargeTypeID = 5");
//Loop through each room and find out this specified month's apportionment data.
foreach (DataRow dr1 in drsNAPRoomForInternal)
{
DataTable dtApportionData = BLL.RoomApportionDataManager.GetNAPRoomApportionDataByPeriod(StartPeriod, EndPeriod, dr1.Field <int>("RoomID"));
foreach (DataRow dr2 in dtApportionData.Rows)
{
DataRow[] drs = dtRoomDB.Select(string.Format("ClientID = {0} AND AccountID = {1} AND RoomID = {2}", dr2["ClientID"], dr2["AccountID"], dr2["RoomID"]));
if (drs.Length == 1)
{
drs[0].SetField("TotalCalcCost", (dr2.Field <double>("Percentage") * dr1.Field <double>("RoomCost")) / 100);
}
}
}
dtRoomDB.Columns.Add("DebitAccount", typeof(string));
dtRoomDB.Columns.Add("CreditAccount", typeof(string));
dtRoomDB.Columns.Add("LineDesc", typeof(string));
dtRoomDB.Columns.Add("TotalAllAccountCost", typeof(double));
//dtRoom - ClientID, AccountID, RoomID, TotalCalCost, TotalEntries, TotalHours
// cap costs - capping is per clientorg, thus apportion cappeing across charges
// note that this assumes that there is only one org for internal academic!!!
object temp;
double totalRoomCharges;
foreach (DataRow drCWC in dtClientWithCharges.Rows)
{
temp = dtRoomDB.Compute("SUM(TotalCalcCost)", string.Format("ClientID = {0}", drCWC["ClientID"]));
if (temp == null || temp == DBNull.Value)
{
totalRoomCharges = 0;
}
else
{
totalRoomCharges = Convert.ToDouble(temp);
}
if (totalRoomCharges > roomCapCost)
{
DataRow[] fdr = dtRoomDB.Select(string.Format("ClientID = {0}", drCWC["ClientID"]));
for (int i = 0; i < fdr.Length; i++)
{
fdr[i].SetField("TotalCalcCost", fdr[i].Field <double>("TotalCalcCost") * roomCapCost / totalRoomCharges);
}
}
}
DataTable dtClient = ClientDA.GetAllClient(StartPeriod, EndPeriod);
DataTable dtAccount = AccountDA.GetAllInternalAccount(StartPeriod, EndPeriod);
DataTable dtClientAccount = ClientDA.GetAllClientAccountWithManagerName(StartPeriod, EndPeriod); //used to find out manager's name
//Get the general lab account ID and lab credit account ID
GlobalCost gc = GlobalCostDA.GetGlobalCost();
//2008-05-15 very complicated code - trying to figure out the percentage distribution for monthly users, since the "TotalCalcCost" has
//been calculated based on percentage in the CalcCost function, so we need to figure out the percentage here again by findind out the total
//and divide the individual record's "TotalCalcCost'
foreach (DataRow drCWC in dtClientWithCharges.Rows)
{
DataRow[] fdr = dtRoomDB.Select(string.Format("ClientID = {0} AND RoomID = {1}", drCWC["ClientID"], (int)BLL.LabRoom.CleanRoom));
if (fdr.Length > 1)
{
//this user has multiple account for the clean room usage, so we have to find out the total of all accounts on this clean room
double tempTotal = Convert.ToDouble(dtRoomDB.Compute("SUM(TotalCalcCost)", string.Format("ClientID = {0} AND RoomID = {1}", drCWC["ClientID"], (int)BLL.LabRoom.CleanRoom)));
DataRow[] fdrRoom = dtRoomDB.Select(string.Format("ClientID = {0} AND RoomID = {1}", drCWC["ClientID"], (int)BLL.LabRoom.CleanRoom));
for (int i = 0; i < fdrRoom.Length; i++)
{
fdrRoom[i].SetField("TotalAllAccountCost", tempTotal); //assign the total to each record
}
}
}
//2008-08-28 Get Billing Type
DataTable dtBillingType = BillingTypeDA.GetAllBillingTypes();
foreach (DataRow dr in dtRoomDB.Rows)
{
dr["DebitAccount"] = dtAccount.Rows.Find(dr.Field <int>("AccountID"))["Number"];
dr["CreditAccount"] = dtAccount.Rows.Find(gc.LabCreditAccountID)["Number"];
//2007-06-19 financial manager may not be an administrator, but their username must be on JE
dr["LineDesc"] = GetLineDesc(dr, dtClient, dtBillingType);
//2008-05-15 the code below handles the clean room monthly users - it's special code that we have to get rid of when all
//billingtype are all gone
int billingTypeId = dr.Field <int>("BillingType");
if (dr.Field <BLL.LabRoom>("RoomID") == BLL.LabRoom.CleanRoom) //6 is clean room
{
if (BillingTypes.IsMonthlyUserBillingType(billingTypeId))
{
if (dr["TotalAllAccountCost"] == DBNull.Value)
{
//if TotalAllAccountCost is nothing, it means this user has only one account
//2008-10-27 but it might also that the user has only one internal account, and he apportion all hours to his external accouts
//so we must also check 'TotalHours' to make sure the user has more than 0 hours
if (dr.Field <double>("TotalHours") != 0)
{
dr.SetField("TotalCalcCost", BLL.BillingTypeManager.GetTotalCostByBillingType(billingTypeId, 0, 0, BLL.LabRoom.CleanRoom, 1315));
}
}
else
{
double total = dr.Field <double>("TotalAllAccountCost");
dr.SetField("TotalCalcCost", (dr.Field <double>("TotalCalcCost") / total) * BLL.BillingTypeManager.GetTotalCostByBillingType(billingTypeId, 0, 0, BLL.LabRoom.CleanRoom, 1315));
}
}
}
}
//****** apply filters ******
//Get the list below so that we can exclude users who spent less than X minutes in lab(Clean or Chem) in this month
DataTable dtlistClean = RoomUsageData.GetUserListLessThanXMin(StartPeriod, EndPeriod, int.Parse(ConfigurationManager.AppSettings["CleanRoomMinTimeMinute"]), "CleanRoom");
DataTable dtlistChem = RoomUsageData.GetUserListLessThanXMin(StartPeriod, EndPeriod, int.Parse(ConfigurationManager.AppSettings["ChemRoomMinTimeMinute"]), "ChemRoom");
//For performance issue, we have to calculate something first, since it's used on all rows
string depRefNum = string.Empty;
double fTotal = 0;
string creditAccount = dtAccount.Rows.Find(gc.LabCreditAccountID)["Number"].ToString();
string creditAccountShortCode = dtAccount.Rows.Find(gc.LabCreditAccountID)["ShortCode"].ToString();
//Do not show an item if the charge and xcharge accounts are the 'same' - can only happen for 941975
//Do not show items that are associated with specific accounts - need to allow users to add manually here in future
foreach (DataRow sdr in dtRoomDB.Rows)
{
if (sdr.Field <double>("TotalCalcCost") > 0)
{
var excludedAccounts = new[] { gc.LabAccountID, 143, 179, 188 };
if (!excludedAccounts.Contains(sdr.Field <int>("AccountID")) && sdr.Field <int>("BillingType") != BillingTypes.Other)
{
//2006-12-21 get rid of people who stayed in the lab less than 30 minutes in a month
string expression = string.Format("ClientID = {0}", sdr["ClientID"]);
DataRow[] foundRows;
bool flag = false;
if (sdr.Field <BLL.LabRoom>("RoomID") == BLL.LabRoom.CleanRoom) //6 = clean room
{
foundRows = dtlistClean.Select(expression);
}
else if (sdr.Field <BLL.LabRoom>("RoomID") == BLL.LabRoom.ChemRoom) //25 = chem room
{
foundRows = dtlistChem.Select(expression);
}
else //DCLab
{
foundRows = null;
}
if (foundRows == null)
{
flag = true; //add to the SUB
}
else
{
if (foundRows.Length == 0)
{
flag = true;
}
}
if (flag) //if no foundrow, we can add this client to JE
{
DataRow ndr = dt.NewRow();
DataRow drAccount = dtAccount.Rows.Find(sdr.Field <int>("AccountID"));
string debitAccount = drAccount["Number"].ToString();
string shortCode = drAccount["ShortCode"].ToString();
//get manager's name
DataRow[] drClientAccount = dtClientAccount.Select(string.Format("AccountID = {0}", sdr["AccountID"]));
if (drClientAccount.Length > 0)
{
depRefNum = drClientAccount[0]["ManagerName"].ToString();
}
else
{
depRefNum = "No Manager Found";
}
AccountNumber debitAccountNum = AccountNumber.Parse(debitAccount);
ndr["CardType"] = 1;
ndr["ShortCode"] = shortCode;
ndr["Account"] = debitAccountNum.Account;
ndr["FundCode"] = debitAccountNum.FundCode;
ndr["DeptID"] = debitAccountNum.DeptID;
ndr["ProgramCode"] = debitAccountNum.ProgramCode;
ndr["Class"] = debitAccountNum.Class;
ndr["ProjectGrant"] = debitAccountNum.ProjectGrant;
ndr["VendorID"] = "0000456136";
ndr["InvoiceDate"] = EndPeriod.AddMonths(-1).ToString("yyyy/MM/dd");
ndr["InvoiceID"] = $"{ReportSettings.CompanyName} Room Charge";
ndr["Uniqname"] = dtClient.Rows.Find(sdr.Field <int>("ClientID"))["UserName"];
ndr["DepartmentalReferenceNumber"] = depRefNum;
ndr["ItemDescription"] = GetItemDesc(sdr, dtClient, dtBillingType);
ndr["QuantityVouchered"] = "1.0000";
double chargeAmount = Math.Round(sdr.Field <double>("TotalCalcCost"), 5);
ndr["UnitOfMeasure"] = chargeAmount;
ndr["MerchandiseAmount"] = Math.Round(chargeAmount, 2);
ndr["CreditAccount"] = creditAccount;
//Used to calculate the total credit amount
fTotal += chargeAmount;
dt.Rows.Add(ndr);
}
}
}
}
//Summary row
summaryUnit.CardType = 1;
summaryUnit.ShortCode = creditAccountShortCode;
summaryUnit.Account = creditAccount.Substring(0, 6);
summaryUnit.FundCode = creditAccount.Substring(6, 5);
summaryUnit.DeptID = creditAccount.Substring(11, 6);
summaryUnit.ProgramCode = creditAccount.Substring(17, 5);
summaryUnit.ClassName = creditAccount.Substring(22, 5);
summaryUnit.ProjectGrant = creditAccount.Substring(27, 7);
summaryUnit.InvoiceDate = EndPeriod.AddMonths(-1).ToString("yyyy/MM/dd");
summaryUnit.Uniqname = ReportSettings.FinancialManagerUserName;
summaryUnit.DepartmentalReferenceNumber = depRefNum;
summaryUnit.ItemDescription = ReportSettings.FinancialManagerUserName;
summaryUnit.MerchandiseAmount = Math.Round(-fTotal, 2);
summaryUnit.CreditAccount = creditAccount;
//Clean things up manually might help performance in general
dtRoomDB.Clear();
dtClient.Clear();
dtAccount.Clear();
}
private void MakeSummary()
{
if (ddlAccount.SelectedValue == "0")
{
return;
}
// ending date is the end of the previous month
DateTime endDate = new DateTime(DateTime.Now.Date.Year, DateTime.Now.Date.Month, 1);
DateTime sDate = new DateTime(2004, 1, 1);
if (rblTimeFrame.SelectedValue != "0")
{
sDate = endDate.AddMonths(int.Parse(rblTimeFrame.SelectedValue));
}
// now, aggregate all of this into a single table to facilitate display
DataTable dtDisplay = new DataTable();
dtDisplay.Columns.Add("Period", typeof(DateTime));
dtDisplay.Columns.Add("ClientID", typeof(int));
dtDisplay.Columns.Add("DisplayName", typeof(string));
dtDisplay.Columns.Add("Room", typeof(double));
dtDisplay.Columns.Add("Tool", typeof(double));
dtDisplay.Columns.Add("StoreInv", typeof(double));
dtDisplay.Columns.Add("Misc", typeof(double));
bool bRowAdded;
DataRow ndr;
DataRow[] fdr;
DataTable dtAggCost;
string[] costType = { "Room", "StoreInv", "Tool", "Misc" };
Compile mCompile = new Compile();
while (sDate < endDate)
{
// will be a header row
ndr = dtDisplay.NewRow();
ndr["Period"] = sDate;
ndr["DisplayName"] = string.Empty;
dtDisplay.Rows.Add(ndr);
bRowAdded = false;
for (int i = 0; i < costType.Length; i++)
{
dtAggCost = mCompile.CalcCost(costType[i], string.Empty, "AccountID", int.Parse(ddlAccount.SelectedValue), sDate, 0, 0, Compile.AggType.CliAcct);
foreach (DataRow drAggCost in dtAggCost.Rows)
{
bRowAdded = true;
fdr = dtDisplay.Select(string.Format("ClientID = {0} AND Period ='{1}'", drAggCost["ClientID"], sDate));
if (fdr.Length == 0)
{
ndr = dtDisplay.NewRow();
ndr["Period"] = sDate;
ndr["ClientID"] = drAggCost["ClientID"];
ndr["DisplayName"] = dsReport.Tables["Client"].Rows.Find(drAggCost["ClientID"])["DisplayName"];
dtDisplay.Rows.Add(ndr);
}
else
{
ndr = fdr[0];
}
ndr[costType[i]] = drAggCost["TotalCalcCost"];
}
}
if (!bRowAdded)
{
ndr = dtDisplay.NewRow();
ndr["Period"] = sDate;
ndr["DisplayName"] = "No usage this month";
dtDisplay.Rows.Add(ndr);
}
sDate = sDate.AddMonths(1);
}
dtDisplay.DefaultView.Sort = "Period, DisplayName";
dgReport.DataSource = dtDisplay.DefaultView;
dgReport.DataBind();
Table1.Visible = true;
}
protected void BtnReport_Click(object sender, EventArgs e)
{
if (!int.TryParse(txtNumMonths.Text, out int numMonths))
{
return;
}
if (numMonths == 0 || numMonths > 12)
{
return;
}
// get time frame
DateTime sd = pp1.SelectedPeriod;
DateTime ed = sd.AddMonths(1);
// create table of clients along with associated orgs
// add columns for room/store/tool for each month that report is run and set all values to 0
DataTable dtOrg = null;
DataTable dtClientOrg = null;
dtClientOrg = DataCommand().Param("Action", "ForExpCost").FillDataTable("dbo.ClientOrg_Select");
for (int i = 0; i < numMonths; i++)
{
dtClientOrg.Columns.Add(string.Format("mn{0}Room", i), typeof(double));
dtClientOrg.Columns.Add(string.Format("mn{0}Tool", i), typeof(double));
}
foreach (DataRow dr in dtClientOrg.Rows)
{
for (int i = 0; i < numMonths; i++)
{
dr.SetField(string.Format("mn{0}Room", i), 0);
dr.SetField(string.Format("mn{0}Tool", i), 0);
}
}
dtOrg = DataCommand().MapSchema()
.Param("Action", "AllActive")
.Param("sDate", sd)
.Param("eDate", ed)
.FillDataTable("dbo.Org_Select");
// loop by month, then org - follow example in invoice to sum and cap
DataRow[] drClientOrg;
DataTable dtAggCost;
string[] CostType = { "Room", "Tool" };
Compile mCompile = new Compile();
DataTable dtClientWithCharges;
double CapCost;
double TotalCharges;
for (int mnOff = 0; mnOff < numMonths; mnOff++)
{
foreach (DataRow drOrg in dtOrg.Rows)
{
for (int i = 0; i < CostType.Length; i++)
{
dtAggCost = mCompile.CalcCost(CostType[i], string.Empty, "OrgID", drOrg.Field <int>("OrgID"), sd.AddMonths(mnOff), 0, 0, Compile.AggType.CliAcct, true, "Exp");
dtClientWithCharges = mCompile.GetTable(1);
CapCost = mCompile.CapCost;
foreach (DataRow drCWC in dtClientWithCharges.Rows)
{
object temp = dtAggCost.Compute("SUM(TotalCalcCost)", string.Format("ClientID = {0}", drCWC["ClientID"]));
if (temp == DBNull.Value)
{
TotalCharges = 0.0;
}
else
{
TotalCharges = Convert.ToDouble(temp);
}
if (TotalCharges > CapCost)
{
DataRow[] fdr = dtAggCost.Select(string.Format("ClientID = {0}", drCWC["ClientID"]));
for (int j = 0; j < fdr.Length; j++)
{
fdr[j].SetField("TotalCalcCost", fdr[j].Field <double>("TotalCalcCost") * CapCost / TotalCharges);
}
}
// now, add this to the clientOrg table
drClientOrg = dtClientOrg.Select(string.Format("ClientID = {0} AND OrgID = {1}", drCWC["ClientID"], drOrg["OrgID"]));
if (drClientOrg.Length > 0) // should always be 1
{
drClientOrg[0].SetField(string.Format("mn{0}{1}", mnOff, CostType[i]), Math.Min(TotalCharges, CapCost));
}
}
}
}
}
// now, create output table and show results
DataTable dtResults = new DataTable();
dtResults.Columns.Add("DisplayName", typeof(string));
dtResults.Columns.Add("OrgName", typeof(string));
dtResults.Columns.Add("RoomCost", typeof(double));
dtResults.Columns.Add("ToolCost", typeof(double));
DataRow ndr;
Double RoomCost, ToolCost;
foreach (DataRow dr in dtClientOrg.Rows)
{
RoomCost = 0;
ToolCost = 0;
for (int i = 0; i < numMonths; i++)
{
RoomCost += dr.Field <double>(string.Format("mn{0}Room", i));
ToolCost += dr.Field <double>(string.Format("mn{0}Tool", i));
}
if (RoomCost > 0 || ToolCost > 0)
{
ndr = dtResults.NewRow();
ndr["DisplayName"] = dr["DisplayName"];
ndr["OrgName"] = dtOrg.Rows.Find(dr["OrgID"]).Field <string>("OrgName");
ndr["RoomCost"] = RoomCost;
ndr["ToolCost"] = ToolCost;
dtResults.Rows.Add(ndr);
}
}
dtResults.DefaultView.Sort = "OrgName, DisplayName";
dgCost.DataSource = dtResults;
dgCost.DataBind();
Table1.Visible = true;
}
protected override void FillDataTable(DataTable dt)
{
BillingUnit summaryUnit = summaryUnits.First();
Compile mCompile = new Compile();
DataTable dtToolDB = mCompile.CalcCost("Tool", string.Empty, "ChargeTypeID", 5, EndPeriod.AddMonths(-1), 0, 0, Compile.AggType.CliAcct);
DataTable dtClientWithCharges = mCompile.GetTable(1);
double toolCapCost = mCompile.CapCost;
// cap costs - capping is per ClientOrg, thus apportion capping across charges
// note that this assumes that there is only one org for internal academic!!!
object temp;
double totalToolCharges;
foreach (DataRow drCWC in dtClientWithCharges.Rows)
{
temp = dtToolDB.Compute("SUM(TotalCalcCost)", string.Format("ClientID = {0}", drCWC["ClientID"]));
if (temp == null || temp == DBNull.Value)
{
totalToolCharges = 0;
}
else
{
totalToolCharges = Convert.ToDouble(temp);
}
if (totalToolCharges > toolCapCost)
{
DataRow[] fdr = dtToolDB.Select(string.Format("ClientID = {0}", drCWC["ClientID"]));
for (int i = 0; i < fdr.Length; i++)
{
fdr[i].SetField("TotalCalcCost", fdr[i].Field <double>("TotalCalcCost") * toolCapCost / totalToolCharges);
}
}
}
DataTable dtClient = ClientDA.GetAllClient(StartPeriod, EndPeriod);
DataTable dtAccount = AccountDA.GetAllInternalAccount(StartPeriod, EndPeriod);
DataTable dtClientAccount = ClientDA.GetAllClientAccountWithManagerName(StartPeriod, EndPeriod); //used to find out manager's name
DataTable dtBillingType = BillingTypeDA.GetAllBillingTypes();
//Get the general lab account ID and lab credit account ID
GlobalCost gc = GlobalCostDA.GetGlobalCost();
//For performance issue, we have to calculate something first, since it's used on all rows
string depRefNum = string.Empty;
double fTotal = 0;
string creditAccount = dtAccount.Rows.Find(gc.LabCreditAccountID)["Number"].ToString();
string creditAccountShortCode = dtAccount.Rows.Find(gc.LabCreditAccountID)["ShortCode"].ToString();
//Do not show an item if the charge and xcharge accounts are the 'same' - can only happen for 941975
//Do not show items that are associated with specific accounts - need to allow users to add manually here in future
foreach (DataRow sdr in dtToolDB.Rows)
{
if (sdr.Field <double>("TotalCalcCost") > 0)
{
var excludedAccounts = new[] { gc.LabAccountID, 143, 179, 188 };
if (!excludedAccounts.Contains(sdr.Field <int>("AccountID")) && sdr.Field <int>("BillingType") != BillingTypes.Other)
{
DataRow ndr = dt.NewRow();
DataRow drAccount = dtAccount.Rows.Find(sdr.Field <int>("AccountID"));
string debitAccount = drAccount["Number"].ToString();
string shortCode = drAccount["ShortCode"].ToString();
//get manager's name
DataRow[] drClientAccount = dtClientAccount.Select(string.Format("AccountID = {0}", sdr["AccountID"]));
if (drClientAccount.Length > 0)
{
depRefNum = drClientAccount.First()["ManagerName"].ToString();
}
else
{
depRefNum = "No Manager Found";
}
AccountNumber debitAccountNum = AccountNumber.Parse(debitAccount);
ndr["CardType"] = 1;
ndr["ShortCode"] = shortCode;
ndr["Account"] = debitAccountNum.Account;
ndr["FundCode"] = debitAccountNum.FundCode;
ndr["DeptID"] = debitAccountNum.DeptID;
ndr["ProgramCode"] = debitAccountNum.ProgramCode;
ndr["Class"] = debitAccountNum.Class;
ndr["ProjectGrant"] = debitAccountNum.ProjectGrant;
ndr["VendorID"] = "0000456136";
ndr["InvoiceDate"] = EndPeriod.AddMonths(-1).ToString("yyyy/MM/dd");
ndr["InvoiceID"] = $"{ReportSettings.CompanyName} Tool Charge";
ndr["Uniqname"] = dtClient.Rows.Find(sdr.Field <int>("ClientID"))["UserName"];
ndr["DepartmentalReferenceNumber"] = depRefNum;
ndr["ItemDescription"] = GetItemDesc(sdr, dtClient, dtBillingType);
ndr["QuantityVouchered"] = "1.0000";
double chargeAmount = Math.Round(sdr.Field <double>("TotalCalcCost"), 5);
ndr["UnitOfMeasure"] = chargeAmount.ToString();
ndr["MerchandiseAmount"] = Math.Round(chargeAmount, 2).ToString();
ndr["CreditAccount"] = creditAccount;
//Used to calculate the total credit amount
fTotal += chargeAmount;
dt.Rows.Add(ndr);
}
}
}
//Summary row
AccountNumber creditAccountNum = AccountNumber.Parse(creditAccount);
summaryUnit.CardType = 1;
summaryUnit.ShortCode = creditAccountShortCode;
summaryUnit.Account = creditAccountNum.Account;
summaryUnit.FundCode = creditAccountNum.FundCode;
summaryUnit.DeptID = creditAccountNum.DeptID;
summaryUnit.ProgramCode = creditAccountNum.ProgramCode;
summaryUnit.ClassName = creditAccountNum.Class;
summaryUnit.ProjectGrant = creditAccountNum.ProjectGrant;
summaryUnit.InvoiceDate = EndPeriod.AddMonths(-1).ToString("yyyy/MM/dd");
summaryUnit.Uniqname = ReportSettings.FinancialManagerUserName;
summaryUnit.DepartmentalReferenceNumber = depRefNum;
summaryUnit.ItemDescription = ReportSettings.FinancialManagerUserName;
summaryUnit.MerchandiseAmount = Math.Round(-fTotal, 2);
summaryUnit.CreditAccount = creditAccount;
//Clean things up manually might help performance in general
dtToolDB.Clear();
dtClient.Clear();
dtAccount.Clear();
}
protected override void FillDataTable(DataTable dt)
{
BillingUnit summaryUnit1 = summaryUnits[0];
BillingUnit summaryUnit2 = summaryUnits[1];
Compile mCompile = new Compile();
//Get Cleints who order items in store in the Period with Credit and Debit and TotalCost calculated
DataTable dtStoreDB = mCompile.CalcCost("StoreJE", string.Empty, string.Empty, 0, EndPeriod.AddMonths(-1), 0, 0, Compile.AggType.CliAcct);
//Return dtStoreDB
//DataTable dtStore = new DataTable();
//BuildDataTable(dtStore);
DataTable dtClient = ClientDA.GetAllClient(StartPeriod, EndPeriod);
DataTable dtAccount = AccountDA.GetAllInternalAccount(StartPeriod, EndPeriod);
DataTable dtClientAccount = ClientDA.GetAllClientAccountWithManagerName(StartPeriod, EndPeriod); //used to find out manager's name
//Get the general lab account ID and lab credit account ID
GlobalCost gc = GlobalCostDA.GetGlobalCost();
//For performance issue, we have to calculate something first, since it's used on all rows
string depRefNum = string.Empty;
double fTotal = 0;
string lastCreditAccount = "default";
string creditAccount = string.Empty;
string creditAccountShortCode = string.Empty; //we also have to show those credit accounts' shortcodes
string lastCreditAccountShortCode = string.Empty; //we need this, just like we need 'LastCreditAccount' to track the changes
AccountNumber creditAccountNum;
DataView dv = dtStoreDB.DefaultView;
dv.Sort = "CreditAccountID";
//This for loop will loop through each transaction record and create SUB record on every transactional record
foreach (DataRowView sdr in dv)
{
//do not show an item if the charge and xcharge accounts are the 'same' - can only happen for 941975
if (!(Convert.ToInt32(sdr["DebitAccountID"]) == gc.LabAccountID && Convert.ToInt32(sdr["CreditAccountID"]) == gc.LabCreditAccountID))
{
DataRow ndr = dt.NewRow();
DataRow drAccount = dtAccount.Rows.Find(Convert.ToInt32(sdr["DebitAccountID"]));
string debitAccount = string.Empty;
string shortCode = string.Empty;
if (drAccount == null)
{
debitAccount = string.Format("unknown_{0}", sdr["DebitAccountID"]);
shortCode = string.Format("unknown_{0}", sdr["DebitAccountID"]);
}
else
{
debitAccount = drAccount["Number"].ToString();
shortCode = drAccount["ShortCode"].ToString();
}
//get manager's name
DataRow[] drClientAccount = dtClientAccount.Select(string.Format("AccountID = {0}", sdr["DebitAccountID"]));
if (drClientAccount.Length > 0)
{
depRefNum = drClientAccount[0]["ManagerName"].ToString();
}
else
{
depRefNum = "No Manager Found";
}
ndr["CardType"] = 1;
ndr["ShortCode"] = shortCode;
AccountNumber debitAccountNum = AccountNumber.Parse(debitAccount);
ndr["Account"] = debitAccountNum.Account;
ndr["FundCode"] = debitAccountNum.FundCode;
ndr["DeptID"] = debitAccountNum.DeptID;
ndr["ProgramCode"] = debitAccountNum.ProgramCode;
ndr["Class"] = debitAccountNum.Class;
ndr["ProjectGrant"] = debitAccountNum.ProjectGrant;
ndr["InvoiceDate"] = StartPeriod.ToString("yyyy/MM/dd");
ndr["InvoiceID"] = $"{ReportSettings.CompanyName} Store Charge";
ndr["Uniqname"] = dtClient.Rows.Find(Convert.ToInt32(sdr["ClientID"]))["UserName"];
ndr["DepartmentalReferenceNumber"] = depRefNum;
ndr["ItemDescription"] = dtClient.Rows.Find(Convert.ToInt32(sdr["ClientID"]))["DisplayName"].ToString().Substring(0, 30);
ndr["QuantityVouchered"] = "1.0000";
double chargeAmount = Math.Round(Convert.ToDouble(sdr["TotalCalcCost"]), 5);
ndr["UnitOfMeasure"] = chargeAmount;
ndr["MerchandiseAmount"] = Math.Round(chargeAmount, 2);
creditAccount = dtAccount.Rows.Find(Convert.ToInt32(sdr["CreditAccountID"]))["Number"].ToString();
ndr["CreditAccount"] = creditAccount;
//2008-10-09 Depend on credit account, we have different vendor ID
creditAccountNum = AccountNumber.Parse(creditAccount);
if (creditAccountNum.ProjectGrant == "U023440")
{
ndr["VendorID"] = "0000456136";
}
else
{
ndr["VendorID"] = "0000456133";
}
//Used to calculate the total credit amount
fTotal += chargeAmount;
//2008-10-08 We have to find out the shortcode for the credit account as well, requested by Sandrine
creditAccountShortCode = dtAccount.Rows.Find(Convert.ToInt32(sdr["CreditAccountID"]))["ShortCode"].ToString();
if (creditAccount != lastCreditAccount && lastCreditAccount != "default")
{
//Summary row
fTotal -= chargeAmount; //we have to deduct the charge amount again because its no longer belong to this group
AccountNumber lastCreditAccountNum = AccountNumber.Parse(lastCreditAccount);
summaryUnit2.CardType = 1;
summaryUnit2.ShortCode = lastCreditAccountShortCode;
summaryUnit2.Account = lastCreditAccountNum.Account;
summaryUnit2.FundCode = lastCreditAccountNum.FundCode;
summaryUnit2.DeptID = lastCreditAccountNum.DeptID;
summaryUnit2.ProgramCode = lastCreditAccountNum.ProgramCode;
summaryUnit2.ClassName = lastCreditAccountNum.Class;
summaryUnit2.ProjectGrant = lastCreditAccountNum.ProjectGrant;
summaryUnit2.InvoiceDate = EndPeriod.AddMonths(-1).ToString("yyyy/MM/dd");
summaryUnit2.Uniqname = "CreditAccount";
summaryUnit2.DepartmentalReferenceNumber = depRefNum;
summaryUnit2.ItemDescription = "CreditAccount";
summaryUnit2.MerchandiseAmount = Math.Round(-fTotal, 2);
summaryUnit2.CreditAccount = creditAccount;
fTotal = chargeAmount; //add the chargeamount back, because we have new group
}
lastCreditAccount = creditAccount;
lastCreditAccountShortCode = creditAccountShortCode;
dt.Rows.Add(ndr);
}
}
//Summary row
creditAccountNum = AccountNumber.Parse(creditAccount);
summaryUnit1.CardType = 1;
summaryUnit1.ShortCode = creditAccountShortCode;
summaryUnit1.Account = creditAccountNum.Account;
summaryUnit1.FundCode = creditAccountNum.FundCode;
summaryUnit1.DeptID = creditAccountNum.DeptID;
summaryUnit1.ProgramCode = creditAccountNum.ProgramCode;
summaryUnit1.ClassName = creditAccountNum.Class;
summaryUnit1.ProjectGrant = creditAccountNum.ProjectGrant;
summaryUnit1.InvoiceDate = EndPeriod.AddMonths(-1).ToString("yyyy/MM/dd");
summaryUnit1.Uniqname = "CreditAccount";
summaryUnit1.DepartmentalReferenceNumber = depRefNum;
summaryUnit1.ItemDescription = "CreditAccount";
summaryUnit1.MerchandiseAmount = Math.Round(-fTotal, 2);
summaryUnit1.CreditAccount = creditAccount;
//Clean things up manually might help performance in general
dtStoreDB.Clear();
dtClient.Clear();
dtAccount.Clear();
}
protected void BtnValidate_Click(object sender, EventArgs e)
{
tableNamePrefix = Convert.ToString(Session["Exp"]);
// create instance of compile class
Compile mCompile = new Compile();
int i;
string strType = rblFormulaType.SelectedItem.Text;
int selType = Convert.ToInt32(rblFormulaType.SelectedValue);
// now try formula
try
{
DateTime Period = new DateTime(DateTime.Now.Date.AddMonths(-1).Year, DateTime.Now.Date.AddMonths(-1).Month, 1);
DataTable dtData = mCompile.CalcCost(strType, txtFormula.Text.Trim(), "", 0, Period, 0, 0, Compile.AggType.None, true, tableNamePrefix);
if (dtData.Rows.Count > 0)
{
// add calcCost as a array element to facilitate display
IList <string> temp;
temp = mCompile.aryVars[selType].ToList();
temp.Add("CalcCost");
mCompile.aryVars[selType] = temp.ToArray();
temp = mCompile.aryVarFormats[selType].ToList();
temp.Add("{0:$#,##0.00}");
mCompile.aryVarFormats[selType] = temp.ToArray();
BoundColumn bc;
for (i = 0; i < mCompile.aryVars[selType].Length; i++)
{
bc = new BoundColumn {
DataField = mCompile.aryVars[selType][i]
};
bc.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
bc.HeaderText = mCompile.aryVars[selType][i];
bc.HeaderStyle.Font.Bold = true;
bc.HeaderStyle.HorizontalAlign = HorizontalAlign.Right;
if (mCompile.aryVarFormats[selType][i].Length > 0)
{
bc.DataFormatString = mCompile.aryVarFormats[selType][i];
}
dgSample.Columns.Add(bc);
}
dgSample.DataSource = dtData;
dgSample.DataBind();
}
}
catch (Exception ex)
{
lblError.Text = ex.Message;
lblError.Visible = true;
return;
}
// if no errors, store new formula.
// if a new formula has been stored, over-write it
DataRow[] fdr = dsFormula.Tables["Formula"].Select(string.Format("FormulaType = '{0}'", rblFormulaType.SelectedItem.Text), "EffDate ASC");
if (fdr.Length > 1)
{
dsFormula.Tables["Formula"].Rows.Remove(fdr[1]);
}
DataRow ndr = dsFormula.Tables["Formula"].NewRow();
ndr["FormulaType"] = rblFormulaType.SelectedItem.Text;
ndr["Formula"] = txtFormula.Text;
ndr["EffDate"] = DateTime.Now;
dsFormula.Tables["Formula"].Rows.Add(ndr);
ContextBase.SetCacheData(dsFormula);
btnRevert.Enabled = true;
}
//public static string GenerateInvoiceExcelReport(DateTime startPeriod, DateTime endPeriod, int clientId, bool showRemote, ExternalInvoiceHeader orgItem, string subFolder, bool deleteWorkDir, ref string alert)
//{
// ExternalInvoiceManager mgr = new ExternalInvoiceManager(orgItem.AccountID, startPeriod, endPeriod, showRemote);
// string result = GenerateInvoiceExcelReport(mgr, clientId, orgItem, subFolder, deleteWorkDir, ref alert);
// return result;
//}
public static string MakeSpreadSheet(int accountId, string invoiceNumber, string deptRef, string orgName, int currentUserClientId, DateTime startPeriod, DateTime endPeriod)
{
var dtAddress = DataCommand.Create()
.Param("Action", "ByAccount")
.Param("AccountID", accountId)
.FillDataTable("dbo.Address_Select");
// get client data
// using All is hackish. It was ByOrg, but this caused a problem with remote users
// the other option is to select for each client, but that is probably even less efficient
var dtClient = DataCommand.Create()
.Param("Action", "All")
.Param("sDate", startPeriod)
.Param("eDate", endPeriod)
.FillDataTable("dbo.Client_Select");
dtClient.PrimaryKey = new[] { dtClient.Columns["ClientID"] };
// NOTE: all reports here are for external users
DataRow drUsage;
DataTable dtUsage = new DataTable();
dtUsage.Columns.Add("Descrip", typeof(string));
dtUsage.Columns.Add("Quantity", typeof(double));
dtUsage.Columns.Add("Cost", typeof(double));
DataRow cdr;
DataTable dtAggCost;
string[] costType = { "Room", "StoreInv", "Tool", "Misc" };
Compile mCompile = new Compile();
DataTable dtClientWithCharges;
double capCost;
object temp;
double totalCharges;
//2009-01 this for loop will loop through different Cost types, so when added code for each specific type, remember to distinguish each CostType
for (int i = 0; i < costType.Length; i++)
{
dtAggCost = mCompile.CalcCost(costType[i], string.Empty, "AccountID", accountId, startPeriod, 0, 0, Compile.AggType.CliAcct);
//dtAggCost is the main table that contains chargeable items
//0 ClientID
//1 AccountID
//2 RoomID
//3 TotalCalcCost
//4 TotalEntries
//5 TotalHours
//Only Room costtype will execute the code below
if (costType[i] == "Room")
{
//******** The code below handles the cost of NAP rooms, because at this point, all NAP rooms access data have total cost of zero****
//Get all active NAP Rooms with their costs, all chargetypes are returned
DataTable dtNAPRoomForAllChargeType = RoomManager.GetAllNAPRoomsWithCosts(startPeriod);
//filter out the chargetype so that we only have Internal costs with each NAP room
DataRow[] dtdrsNAPRoomForExternal = dtNAPRoomForAllChargeType.Select(string.Format("ChargeTypeID = {0}", AccountDA.GetChargeType(accountId)));
//Loop through each room and find out this specified month's apportionment data.
foreach (DataRow dr1 in dtdrsNAPRoomForExternal)
{
DataTable dtApportionData = RoomApportionDataManager.GetNAPRoomApportionDataByPeriod(startPeriod, endPeriod, dr1.Field <int>("RoomID"));
foreach (DataRow dr2 in dtApportionData.Rows)
{
DataRow[] drs = dtAggCost.Select(String.Format("ClientID = {0} AND AccountID = {1} AND RoomID = {2}", dr2["ClientID"], dr2["AccountID"], dr2["RoomID"]));
if (drs.Length == 1)
{
//2008-06-19 Sandrine requested all monthly room should have charge of the same across all organizations
//so if a guy works for two diferent companies, he should be charged full amount for both companeis on all monthly rooms.
//the only exception is when the apportionment percentage is 0 for this organization. When it's 0 percent, we simply
//cannot charge this organizaiton at all
if (dr2.Field <double>("Percentage") > 0)
{
drs[0]["TotalCalcCost"] = dr1["RoomCost"];
}
}
}
}
//2009-01 remember not to charge clean/chem room usage for less than x amount of minutes
int cleanRoomMinMinutes = int.Parse(ConfigurationManager.AppSettings["CleanRoomMinTimeMinute"]);
int chemRoomMinMinutes = int.Parse(ConfigurationManager.AppSettings["ChemRoomMinTimeMinute"]);
//we simply set totalCalcCost to 0.0 at this point, then those 0.0 charges items will not be published to excel report
foreach (DataRow drAggCost in dtAggCost.Rows)
{
if (drAggCost.Field <LabRoom>("RoomID") == LabRoom.CleanRoom)
{
if (drAggCost.Field <double>("TotalHours") < cleanRoomMinMinutes / 60)
{
drAggCost.SetField("TotalCalcCost", 0.0);
}
}
else if (drAggCost.Field <LabRoom>("RoomID") == LabRoom.ChemRoom)
{
if (drAggCost.Field <double>("TotalHours") < chemRoomMinMinutes / 60)
{
drAggCost.SetField("TotalCalcCost", 0.0);
}
}
}
}
if (costType[i] != "Misc")
{
dtClientWithCharges = mCompile.GetTable(1);
capCost = mCompile.CapCost;
foreach (DataRow drCWC in dtClientWithCharges.Rows)
{
temp = dtAggCost.Compute("SUM(TotalCalcCost)", string.Format("ClientID = {0}", drCWC["ClientID"]));
if (temp == null || temp == DBNull.Value)
{
totalCharges = 0.0;
}
else
{
totalCharges = Convert.ToDouble(temp);
}
//BUG FIX: I have to exclude StoreInv charge here since the CapCost for it is always 0
if (totalCharges > capCost && costType[i] != "StoreInv")
{
DataRow[] fdr = dtAggCost.Select(string.Format("ClientID = {0}", drCWC["ClientID"]));
for (int j = 0; j < fdr.Length; j++)
{
fdr[j].SetField("TotalCalcCost", fdr[j].Field <double>("TotalCalcCost") * capCost / totalCharges);
}
}
}
}
foreach (DataRow drAggCost in dtAggCost.Rows)
{
cdr = dtClient.Rows.Find(drAggCost.Field <int>("ClientID"));
drUsage = dtUsage.NewRow();
if (costType[i] == "Misc")
{
drUsage["Descrip"] = drAggCost["Description"];
drUsage["Quantity"] = drAggCost["Quantity"];
drUsage["Cost"] = drAggCost["UnitCost"];
}
else
{
drUsage["Descrip"] = string.Format("{0} usage for {1}. {2}", costType[i].Substring(0, 5), cdr["FName"].ToString().Substring(0, 1), cdr["LName"]);
drUsage["Quantity"] = 1;
drUsage["Cost"] = drAggCost["TotalCalcCost"];
}
dtUsage.Rows.Add(drUsage);
}
}
// Write to excel
using (var mgr = NewExcelManager())
{
string fileName = Utility.GetRequiredAppSetting("Invoice_Template");
mgr.OpenWorkbook(GetTemplatePath(fileName));
mgr.SetActiveWorksheet("Invoice");
// show invoice date
mgr.SetCellTextValue("F4", DateTime.Now.ToShortDateString());
int startRow;
int useRow = 0;
foreach (DataRow drAddr in dtAddress.Rows)
{
if (drAddr["AddrType"].ToString() == "Billing")
{
startRow = 7;
}
else
{
startRow = 13;
}
FillField(mgr, "C", startRow, orgName, ref useRow);
FillField(mgr, "C", 0, drAddr.Field <string>("InternalAddress"), ref useRow);
FillField(mgr, "C", 0, drAddr.Field <string>("StrAddress1"), ref useRow);
FillField(mgr, "C", 0, drAddr.Field <string>("StrAddress2"), ref useRow);
FillField(mgr, "C", 0, drAddr.Field <string>("City") + ", " + drAddr.Field <string>("State") + " " + drAddr.Field <string>("Zip"), ref useRow);
}
// invoice number that needs to be entered
mgr.SetCellTextValue("E12", invoiceNumber);
mgr.SetCellTextValue("I14", deptRef);
int rowRef = 21;
string rowCell;
// now print charges
foreach (DataRow dr in dtUsage.Rows)
{
if (dr.Field <double>("Cost") != 0)
{
rowCell = "C" + rowRef.ToString();
mgr.SetCellTextValue(rowCell, startPeriod.ToString("MM/yyyy"));
rowCell = "D" + rowRef.ToString();
mgr.SetCellTextValue(rowCell, dr["Descrip"]);
rowCell = "H" + rowRef.ToString();
mgr.SetCellNumberValue(rowCell, dr["Quantity"]);
rowCell = "I" + rowRef.ToString();
mgr.SetCellNumberValue(rowCell, dr["Cost"]);
rowRef += 1;
}
}
string workFilePath = Path.Combine(GetWorkPath(currentUserClientId), orgName + Path.GetExtension(fileName));
mgr.SaveAs(workFilePath);
return(workFilePath);
}
}
private void GetDataForAllDataGrids(DateTime period, int clientId)
{
// empty datagrids
dgRoom.DataSource = null;
dgRoom.DataBind();
gvTool.DataSource = null;
gvTool.DataBind();
dgStore.DataSource = null;
dgStore.DataBind();
lblRoom.Visible = true;
lblTool.Visible = true;
lblStore.Visible = true;
object sumCost;
Compile compile = new Compile();
//Room realted
//2008-01-15
//dtRoomCost has the following columns
//0 "ClientID"
//1 "AccountID"
//2 "RoomID"
//3 "BillingType"
//4 "TotalCalcCost"
//5 "TotalEntries"
//6 "TotalHours"
DataTable dtRoomCost = compile.CalcCost("Room", string.Empty, string.Empty, 0, period, 0, clientId, Compile.AggType.CliAcctType);
dtRoomCost.Columns.Add("Room", typeof(string));
dtRoomCost.Columns.Add("Name", typeof(string));
dtRoomCost.Columns.Add("BillingTypeName", typeof(string));
dtRoomCost.Columns.Add("OrgName", typeof(string));
dtRoomCost.Columns.Add("LineCost", typeof(double));
dtRoomCost.Columns.Add("ShortCode", typeof(string));
//Create the list to contain all summary total for each organization
//List<UsageSummaryTotal> mylist = new List<UsageSummaryTotal>();
DataTable SummaryTable = new DataTable();
SummaryTable.Columns.Add("OrgID", typeof(int));
SummaryTable.Columns.Add("OrgName", typeof(string));
SummaryTable.Columns.Add("BillingTypeID", typeof(int));
SummaryTable.Columns.Add("RoomTotal", typeof(double));
SummaryTable.Columns.Add("ToolTotal", typeof(double));
SummaryTable.Columns.Add("StoreTotal", typeof(double));
//It's possible that the above code makes the table row count to 0.
//If it's the case, we have to skip the code below, but why delete the only row?
if (dtRoomCost.Rows.Count > 0)
{
//we have to get the total lab hours for monthly users because we have to find out the appropriate proportion of monthly fee distribution
int currentBillingTypeId = dtRoomCost.Rows[0].Field <int>("BillingTypeID");
decimal totalCleanRoomHours = 0; //this stores the total clean room hours for this user at this month
decimal totalChemRoomHours = 0; //this stores the total chem room hours
int[] specialBillingTypesForSomeUnknownReason = { BillingTypes.ExtAc_Ga, BillingTypes.ExtAc_Si, BillingTypes.Int_Si, BillingTypes.Int_Ga };
if (specialBillingTypesForSomeUnknownReason.Contains(currentBillingTypeId))
{
try
{
//2008-06-12 it's possible that user access only chem room and no clean room at all, so it will return DB Null if no clean room hours
totalCleanRoomHours = Convert.ToDecimal(dtRoomCost.Compute("SUM(TotalHours)", "RoomID = 6"));
}
catch
{
totalCleanRoomHours = 0;
}
try
{
//2008-06-12 it's possible that user access only chem room and no clean room at all, so it will return DB Null if no clean room hours
totalChemRoomHours = Convert.ToDecimal(dtRoomCost.Compute("SUM(TotalHours)", "RoomID = 25"));
}
catch
{
totalChemRoomHours = 0;
}
}
int previousOrgId = dtRoomCost.Rows[0].Field <int>("OrgID");
DataRow nr = SummaryTable.NewRow();
nr["OrgID"] = previousOrgId;
nr["OrgName"] = dsReport.Tables["Org"].Rows.Find(previousOrgId)["OrgName"];
nr["BillingTypeID"] = dtRoomCost.Rows[0]["BillingType"];
nr["RoomTotal"] = 0;
nr["ToolTotal"] = 0;
nr["StoreTotal"] = 0;
SummaryTable.Rows.Add(nr);
//**************** NAP room handling ******************
//Get all active NAP Rooms with their costs, all chargetypes are returned
//This is a temporary table, it's used to derive the really useful table below
DataTable dtNAPRoomForAllChargeType = AppCode.BLL.RoomManager.GetAllNAPRoomsWithCosts(period);
//filter out the chargetype so that we only have Internal costs with each NAP room
//2009-04-05 the chartype id is difficult to get here, so we assume everyone is interanl. This is okay, because we need to find out the percentage, not the actual cost
DataRow[] drsNAPRoomForInternal = dtNAPRoomForAllChargeType.Select("ChargeTypeID = 5");
//Loop through each room and find out this specified month's apportionment data.
foreach (DataRow dr1 in drsNAPRoomForInternal)
{
DataTable dtApportionData = RoomApportionDataManager.GetNAPRoomApportionDataByPeriod(period, dr1.Field <int>("RoomID"));
foreach (DataRow dr2 in dtApportionData.Rows)
{
DataRow[] drs = dtRoomCost.Select(string.Format("ClientID = {0} AND AccountID = {1} AND RoomID = {2}", dr2["ClientID"], dr2["AccountID"], dr2["RoomID"]));
if (drs.Length == 1)
{
drs[0].SetField("TotalCalcCost", dr2.Field <double>("Percentage") * dr1.Field <double>("RoomCost") / 100D);
}
else
{
//?
}
}
////We now have the data of each room on this specific month, next we loop through the accounts
//foreach (DataRow dr2 in dtRoom.Rows)
//{
// if (dr2.Field<int>("RoomID") == dr1.Field<int>("RoomID"))
// {
// dr2.SetField("TotalCalcCost", (drs[0].Field<double>("Percentage") * dr1.Field<double>("RoomCost")) / 100D);
// DataRow[] drs = dtApportionData.Select();
// }
//}
}
//**************** main loop to do the fee calculation ******
decimal tempTotalHours = 0;
foreach (DataRow dr in dtRoomCost.Rows)
{
dr["Room"] = dsReport.Tables["Room"].Rows.Find(dr["RoomID"])["Room"];
dr["Name"] = dsReport.Tables["Account"].Rows.Find(dr["AccountID"])["Name"];
dr["ShortCode"] = dsReport.Tables["Account"].Rows.Find(dr["AccountID"])["ShortCode"];
DataRow drBillingType = dsReport.Tables["BillingType"].Rows.Find(dr["BillingType"]);
if (drBillingType != null)
{
dr["BillingTypeName"] = drBillingType["BillingTypeName"];
}
else
{
dr["BillingTypeName"] = "[nothing]";
}
dr["OrgName"] = dsReport.Tables["Org"].Rows.Find(dr["OrgID"])["OrgName"];
if (dr.Field <int>("RoomID") == 6)
{
tempTotalHours = totalCleanRoomHours;
}
else if (dr.Field <int>("RoomID") == 25)
{
tempTotalHours = totalChemRoomHours;
}
dr["LineCost"] = BillingTypeManager.GetTotalCostByBillingType(dr.Field <int>("BillingType"), dr.Field <decimal>("TotalHours"), dr.Field <decimal>("TotalEntries"), dr.Field <LabRoom>("RoomID"), dr.Field <decimal>("TotalCalcCost"), tempTotalHours);
int currentOrgId = dr.Field <int>("OrgID");
if (previousOrgId != currentOrgId)
{
nr = SummaryTable.NewRow();
nr["OrgID"] = currentOrgId;
nr["OrgName"] = dr["OrgName"];
nr["BillingTypeID"] = dr["BillingType"];
SummaryTable.Rows.Add(nr);
previousOrgId = currentOrgId;
}
}
foreach (DataRow r in SummaryTable.Rows)
{
r["RoomTotal"] = dtRoomCost.Compute("SUM(LineCost)", string.Format("OrgID = {0}", r["OrgID"]));
}
//Get total cost for this table
sumCost = dtRoomCost.Compute("SUM(LineCost)", string.Empty);
lblRoom.Text = string.Format("Total room usage fees: {0:C}", sumCost);
lblRoom.ForeColor = System.Drawing.Color.Red;
dgRoom.DataSource = dtRoomCost;
dgRoom.DataBind();
}
else
{
lblRoom.Text = "No room usage during period";
lblRoom.ForeColor = System.Drawing.Color.Red;
foreach (DataRow r in SummaryTable.Rows)
{
r["RoomTotal"] = 0;
}
}
//***************** Tool related *******************************
//2007-02-23 Must handle the exception here because if the user doesn't exist on that period, a error occur inside the CalcCost function
DataTable dtToolCost;
//0 ClientID
//1 AccountID
//2 ResourceID
//3 IsStarted
//4 TotalCalcCost
//5 TotalUses
//6 TotalActDuration
try
{
dtToolCost = compile.CalcCost("Tool", string.Empty, string.Empty, 0, period, 0, clientId, Compile.AggType.CliAcctType);
}
catch
{
dtToolCost = null;
}
if (dtToolCost.Rows.Count > 0)
{
dtToolCost.Columns.Add("ResourceName", typeof(string));
dtToolCost.Columns.Add("AccountName", typeof(string));
dtToolCost.Columns.Add("OrgName", typeof(string));
dtToolCost.Columns.Add("ShortCode", typeof(string));
//Populate the two new columns, ResourceName and AccountName
foreach (DataRow dr in dtToolCost.Rows)
{
dr["ResourceName"] = dsReport.Tables["Resource"].Rows.Find(dr["ResourceID"])["ResourceName"];
dr["AccountName"] = dsReport.Tables["Account"].Rows.Find(dr["AccountID"])["Name"];
dr["ShortCode"] = dsReport.Tables["Account"].Rows.Find(dr["AccountID"])["ShortCode"];
dr["OrgName"] = dsReport.Tables["Org"].Rows.Find(dr["OrgID"])["OrgName"];
}
DataTable dtCloneActivated = dtToolCost.Clone();
DataTable dtCloneCancelled = dtToolCost.Clone();
DataTable dtCloneForgiven = dtToolCost.Clone();
DataRow nr;
foreach (DataRow dr in dtToolCost.Rows)
{
if (dr.Field <int>("IsStarted") == 1)
{
if (dr.Field <int>("ToolChargeMultiplierMul") == 1)
{
nr = dtCloneActivated.NewRow();
nr["ResourceName"] = dr["ResourceName"];
nr["TotalUses"] = dr["TotalUses"];
nr["TotalActDuration"] = dr["TotalActDuration"];
nr["TotalCalcCost"] = dr["TotalCalcCost"];
nr["AccountName"] = dr["AccountName"];
nr["OrgName"] = dr["OrgName"];
nr["OrgID"] = dr["OrgID"];
dtCloneActivated.Rows.Add(nr);
}
else
{
//all forgiven records are collected here
nr = dtCloneForgiven.NewRow();
nr["ResourceName"] = dr["ResourceName"];
nr["TotalUses"] = dr["TotalUses"];
nr["TotalActDuration"] = dr["TotalActDuration"];
nr["TotalCalcCost"] = dr["TotalCalcCost"];
nr["AccountName"] = dr["AccountName"];
nr["OrgName"] = dr["OrgName"];
nr["OrgID"] = dr["OrgID"];
dtCloneForgiven.Rows.Add(nr);
}
}
else
{
nr = dtCloneCancelled.NewRow();
nr["ResourceName"] = dr["ResourceName"];
nr["TotalUses"] = dr["TotalUses"];
nr["TotalActDuration"] = dr["TotalActDuration"];
nr["TotalCalcCost"] = dr["TotalCalcCost"];
nr["AccountName"] = dr["AccountName"];
nr["OrgName"] = dr["OrgName"];
nr["OrgID"] = dr["OrgID"];
dtCloneCancelled.Rows.Add(nr);
}
}
//We claculate the total for each tables accordingly
double totalToolCost = 0;
if (dtCloneActivated.Rows.Count > 0)
{
//to calculate the sum for activated too usage fee
sumCost = dtCloneActivated.Compute("SUM(TotalCalcCost)", string.Empty);
totalToolCost = Convert.ToDouble(sumCost);
lblActivatedToolFee.Text = string.Format("Sub Total: {0:C}", sumCost);
foreach (DataRow r in SummaryTable.Rows)
{
r["ToolTotal"] = dtCloneActivated.Compute("SUM(TotalCalcCost)", string.Format("OrgID = {0}", r["OrgID"]));
}
}
else
{
lblActivatedToolFee.Text = "Sub Total: $0";
}
if (dtCloneCancelled.Rows.Count > 0)
{
//to calculate the sum for cancelled too usage fee
sumCost = dtCloneCancelled.Compute("SUM(TotalCalcCost)", string.Empty);
totalToolCost += Convert.ToDouble(sumCost);
lblCancelledToolFee.Text = string.Format("Sub Total: {0:C}", sumCost);
foreach (DataRow r in SummaryTable.Rows)
{
object obj = dtCloneCancelled.Compute("SUM(TotalCalcCost)", string.Format("OrgID = {0}", r["OrgID"]));
if (obj != null && obj != DBNull.Value)
{
r["ToolTotal"] = r.Field <double>("ToolTotal") + Convert.ToDouble(obj);
}
}
}
else
{
lblCancelledToolFee.Text = "Sub Total: $0";
}
if (dtCloneForgiven.Rows.Count > 0)
{
sumCost = dtCloneForgiven.Compute("SUM(TotalCalcCost)", string.Empty);
lblForgivenToolFee.Text = string.Format("Sub Total: {0:$#,##0.00;($#,##0.00)}", sumCost);
}
else
{
lblForgivenToolFee.Text = "Sub Total: $0";
}
gvTool.DataSource = dtCloneActivated;
gvTool.DataBind();
gvToolCancelled.DataSource = dtCloneCancelled;
gvToolCancelled.DataBind();
gvToolForgiven.DataSource = dtCloneForgiven;
gvToolForgiven.DataBind();
lblTool.Text = string.Format("Total tool usage fees: {0:C}", totalToolCost);
//lblTool.Text += " (This doesn't include the fees that have been forgiven by tool engineers)";
lblTool.ForeColor = System.Drawing.Color.Red;
divTool.Visible = true;
}
else
{
lblTool.Text = "No tool usage during period";
lblTool.ForeColor = System.Drawing.Color.Red;
divTool.Visible = false;
foreach (DataRow r in SummaryTable.Rows)
{
r["ToolTotal"] = 0;
}
}
//********************* Store related ***************************
DataTable dtStoreCost = compile.CalcCost("StoreInv", string.Empty, string.Empty, 0, period, 0, clientId, Compile.AggType.None);
dtStoreCost.Columns.Add("Item", typeof(string));
dtStoreCost.Columns.Add("Name", typeof(string));
dtStoreCost.Columns.Add("OrgName", typeof(string));
dtStoreCost.Columns.Add("ShortCode", typeof(string));
if (dtStoreCost.Rows.Count > 0)
{
foreach (DataRow dr in dtStoreCost.Rows)
{
dr["Item"] = dsReport.Tables["Item"].Rows.Find(dr["ItemID"])["Item"];
dr["Name"] = dsReport.Tables["Account"].Rows.Find(dr["AccountID"])["Name"];
dr["ShortCode"] = dsReport.Tables["Account"].Rows.Find(dr["AccountID"])["ShortCode"];
dr["OrgName"] = dsReport.Tables["Org"].Rows.Find(dr["OrgID"])["OrgName"];
}
sumCost = dtStoreCost.Compute("SUM(CalcCost)", string.Empty);
lblStore.Text = string.Format("Total store usage fees: {0:C}", sumCost);
foreach (DataRow r in SummaryTable.Rows)
{
r["StoreTotal"] = dtStoreCost.Compute("SUM(CalcCost)", string.Format("OrgID = {0}", r["OrgID"]));
if (r["StoreTotal"] == null || r["StoreTotal"] == DBNull.Value)
{
r["StoreTotal"] = 0.0;
}
}
dgStore.DataSource = dtStoreCost;
dgStore.DataBind();
lblStore.ForeColor = System.Drawing.Color.Red;
}
else
{
lblStore.Text = "No store usage during period";
lblStore.ForeColor = System.Drawing.Color.Red;
foreach (DataRow r in SummaryTable.Rows)
{
r["StoreTotal"] = 0.0;
}
}
dtStore2 = dtStoreCost;
foreach (DataRow r in SummaryTable.Rows)
{
int billingTypeId = r.Field <int>("BillingTypeID");
int[] specialBillingTypesForSomeUnknownReason = { BillingTypes.NonAc, BillingTypes.ExtAc_Ga, BillingTypes.ExtAc_Si, BillingTypes.Int_Ga, BillingTypes.Int_Si, BillingTypes.Other };
if (specialBillingTypesForSomeUnknownReason.Contains(billingTypeId))
{
r["ToolTotal"] = 0.0;
}
}
dlSummary.DataSource = SummaryTable;
dlSummary.DataBind();
}