public IEnumerable <IToolBilling> ForSUBReport(DateTime StartPeriod, DateTime EndPeriod, IList <SubLineItem> lineItems)
{
int[] excludedBillingTypeIds = new int[] { 14, 99 };
var result = Session.Query <ToolBilling>().Where(x => x.Period >= StartPeriod && x.Period < EndPeriod && x.ChargeTypeID == 5 && !excludedBillingTypeIds.Contains(x.BillingTypeID)).ToList();
foreach (var tb in result)
{
if (lineItems.FirstOrDefault(x => x.Period == tb.Period && x.ClientID == tb.ClientID && x.AccountID == tb.AccountID) == null)
{
lineItems.Add(new SubLineItem()
{
Period = tb.Period, ClientID = tb.ClientID, AccountID = tb.AccountID
});
}
}
MiscBillingCharge mbc = null;
Account acct = null;
Org org = null;
OrgType ot = null;
ChargeType ct = null;
var miscQuery = Session.QueryOver(() => mbc)
.JoinEntityAlias(() => acct, () => mbc.AccountID == acct.AccountID)
.JoinEntityAlias(() => org, () => acct.Org == org)
.JoinEntityAlias(() => ot, () => org.OrgType == ot)
.JoinEntityAlias(() => ct, () => ot.ChargeType == ct)
.Where(() => mbc.Period >= StartPeriod && mbc.Period < EndPeriod && mbc.SUBType == "tool" && ct.ChargeTypeID == 5)
.List <MiscBillingCharge>();
foreach (var mb in miscQuery)
{
if (lineItems.FirstOrDefault(x => x.Period == mb.Period && x.ClientID == mb.ClientID && x.AccountID == mb.AccountID) == null)
{
lineItems.Add(new SubLineItem()
{
Period = mb.Period, ClientID = mb.ClientID, AccountID = mb.AccountID
});
}
}
return(result);
}
public IMiscBillingCharge GetMiscBillingCharge(int expId)
{
using (var cmd = NewCommand("sselData.dbo.MiscBillingCharge_Select"))
using (var adap = new SqlDataAdapter(cmd))
{
cmd.Parameters.AddWithValue("Action", "ByExpID", SqlDbType.NVarChar, 50);
cmd.Parameters.AddWithValue("ExpID", expId, SqlDbType.Int);
var dt = new DataTable();
adap.Fill(dt);
if (dt.Rows.Count == 0)
{
return(null);
}
var dr = dt.Rows[0];
var result = new MiscBillingCharge
{
ExpID = dr.Field <int>("ExpID"),
ClientID = dr.Field <int>("ClientID"),
AccountID = dr.Field <int>("AccountID"),
SUBType = dr.Field <string>("SUBType"),
Period = dr.Field <DateTime>("Period"),
ActDate = dr.Field <DateTime>("ActDate"),
Description = dr.Field <string>("Description"),
Quantity = dr.Field <double>("Quantity"),
UnitCost = dr.Field <decimal>("UnitCost"),
SubsidyDiscount = dr.Field <decimal>("SubsidyDiscount"),
Active = dr.Field <bool>("Active")
};
return(result);
}
}
