private void BindData()
{
StockAccountBLL bll = null;
DataPage dp = new DataPage();
WHMonthAccount condition = new WHMonthAccount();
string[] yearMonths = this.YearMonth.Text.Split("-".ToArray());
try
{
bll = BLLFactory.CreateBLL <StockAccountBLL>();
condition.Warehouse = this.Warehouse.SelectedValue;
condition.ProductType = this.ProductType.SelectedValue;
condition.MatID = this.MatID.SelectedValue;
condition.AccountYear = int.Parse(yearMonths[0]);
condition.AccountMonth = int.Parse(yearMonths[1]);
PagerHelper.InitPageControl(this.AspNetPager1, dp, true);
dp = bll.QueryMonthAccount(condition, dp);
List <WHMonthAccount> list = dp.Result as List <WHMonthAccount>;
this.GvList.DataSource = list;
this.GvList.DataBind();
PagerHelper.SetPageControl(AspNetPager1, dp, true);
}
catch (Exception ex)
{
throw ex;
}
}
public void Update(WHMonthAccount info)
{
using (IDataSession session = AppDataFactory.CreateMainSession())
{
session.Update <WHMonthAccount>(info);
}
}
/// <summary>
/// 库存台账查询
/// </summary>
/// <param name="condition"></param>
/// <param name="page"></param>
/// <returns></returns>
public DataPage QueryMonthAccount(WHMonthAccount condition, DataPage page)
{
if (this.LoginUser.UserName.ToLower() != "admin")
{
condition.UserID = this.LoginUser.UserID;
}
return(new StockAccountDAL().QueryMonthAccount(condition, page));
}
/// <summary>
/// 获取台账列表
/// </summary>
/// <param name="">条件</param>
/// <returns>列表</returns>
public List <WHMonthAccount> GetList(WHMonthAccount condition)
{
string sql = null;
List <WHMonthAccount> list = null;
List <DataParameter> parameters = new List <DataParameter>();
try
{
sql = "select * from T_WH_MonthAccount where AccountYear = @AccountYear and AccountMonth = @AccountMonth";
parameters.Add(new DataParameter("AccountYear", condition.AccountYear));
parameters.Add(new DataParameter("AccountMonth", condition.AccountMonth));
using (IDataSession session = AppDataFactory.CreateMainSession())
{
list = session.GetList <WHMonthAccount>(sql, parameters.ToArray()).ToList();
}
return(list);
}
catch (Exception ex)
{
throw ex;
}
}
public WHMonthAccount Get(WHMonthAccount condition)
{
string sql = null;
WHMonthAccount info = null;
try
{
sql = @"select * from T_WH_MonthAccount
where Warehouse = @Warehouse and MatID = @MatID and AccountYear = @AccountYear and AccountMonth = @AccountMonth";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
info = session.Get <WHMonthAccount>(sql
, new DataParameter("Warehouse", condition.Warehouse)
, new DataParameter("MatID", condition.MatID)
, new DataParameter("AccountYear", condition.AccountYear)
, new DataParameter("AccountMonth", condition.AccountMonth));
}
return(info);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 库存台账查询
/// </summary>
/// <param name="condition"></param>
/// <param name="page"></param>
/// <returns></returns>
public DataPage QueryMonthAccount(WHMonthAccount condition, DataPage page)
{
DataTable dt = new DataTable();
StringBuilder sql = null;
List <DataParameter> parameters = new List <DataParameter>();
try
{
sql = new StringBuilder(@"select t1.ID,t2.MatCode,t2.MatName,t3.Description as ProductType,t4.Description as WarehouseName,t5.Description as UnitName
,t1.PrimeAmount,t1.InAmount,t1.OutAmount,t1.GainAmount,t1.LossAmount,t1.LateAmount
from T_WH_MonthAccount t1
inner join T_WH_Mat t2 on t1.MatID=t2.ID
inner join T_WH_MatType t3 on t2.ProductType=t3.ID
inner join T_WH_Warehouse t4 on t1.Warehouse=t4.ID
inner join T_WH_MatUnit t5 on t2.UnitCode=t5.ID
where AccountYear = @AccountYear and AccountMonth = @AccountMonth ");
page.KeyName = "ID";
parameters.Add(new DataParameter("AccountYear", condition.AccountYear));
parameters.Add(new DataParameter("AccountMonth", condition.AccountMonth));
if (!string.IsNullOrEmpty(condition.Warehouse))
{
sql.Append(" and t1.Warehouse = @Warehouse");
parameters.Add(new DataParameter("Warehouse", condition.Warehouse));
}
if (!string.IsNullOrEmpty(condition.ProductType))
{
sql.Append(" and t4.ProductType = @ProductType");
parameters.Add(new DataParameter("ProductType", condition.ProductType));
}
if (!string.IsNullOrEmpty(condition.MatID))
{
sql.Append(" and t1.MatID = @MatID");
parameters.Add(new DataParameter("MatID", condition.MatID));
}
if (!string.IsNullOrEmpty(condition.UserID))
{
sql.Append(" and exists(select WarehouseID from T_WH_WHPower where UserID=@userID and WarehouseID=t1.Warehouse)");
parameters.Add(new DataParameter("userID", condition.UserID));
}
page.SortExpression = "WarehouseName,MatCode";
using (IDataSession session = AppDataFactory.CreateMainSession())
{
page = session.GetDataPage <WHMonthAccount>(sql.ToString(), parameters.ToArray(), page);
}
return(page);
}
catch (Exception ex)
{
throw ex;
}
}
public void ComputeAccount()
{
try
{
DateTime dataEndDate = DateTime.Parse(DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd 23:59:59"));
int year = dataEndDate.Year;
int month = dataEndDate.Month;
string startDate = dataEndDate.ToString("yyyy-MM-01");
string endDate = DateTime.Parse(startDate).AddMonths(1).ToString("yyyy-MM-dd");
//获取仓库列表
List <Warehouse> whList = new WarehouseBLL().GetList();
//获取货品列表
List <WHMat> matList = new WHMatBLL().GetList();
//获取月台账列表
List <WHMonthAccount> accountList = new StockAccountDAL().GetList(new WHMonthAccount {
AccountYear = year, AccountMonth = month
});
//获取入库数量列表
List <InStockQueryResult> inAmountList = new InStockQueryBLL().GetInAmountList(new InStockQueryResult {
StartDate = startDate, EndDate = endDate
});
//获取出库数量列表
List <OutStockQueryResult> outAmountList = new OutStockQueryBLL().GetOutAmountList(new OutStockQueryResult {
StartDate = startDate, EndDate = endDate
});
//获取盘点数量列表
List <CheckStockQueryResult> checkAmountList = new CheckStockBLL().GetCheckAmountList(new CheckStockQueryResult {
StartDate = startDate, EndDate = endDate
});
//按仓库+货品逐个计算
foreach (Warehouse wh in whList)
{
foreach (WHMat mat in matList)
{
WHMonthAccount currentAccount = accountList.Find(p => p.Warehouse == wh.ID && p.MatID == mat.ID && p.AccountYear == year && p.AccountMonth == month);
if (currentAccount == null)
{
//无当前月份台账
currentAccount = new WHMonthAccount();
//初始设置
currentAccount.Warehouse = wh.ID;
currentAccount.MatID = mat.ID;
currentAccount.MainUnitID = mat.UnitCode;
currentAccount.AccountYear = year;
currentAccount.AccountMonth = month;
//获取上月台帐
int prevYear = dataEndDate.AddMonths(-1).Year;
int prevMonth = dataEndDate.AddMonths(-1).Month;
WHMonthAccount prevAccount = new StockAccountDAL().Get(new WHMonthAccount {
Warehouse = wh.ID, MatID = mat.ID, AccountYear = prevYear, AccountMonth = prevMonth
});
if (prevAccount != null)
{
currentAccount.PrimeAmount = prevAccount.LateAmount;
}
}
//计算入库数量
InStockQueryResult inAmount = inAmountList.Find(p => p.WarehouseID == wh.ID && p.MatID == mat.ID);
currentAccount.InAmount = inAmount != null ? inAmount.Amount : 0;
//计算入库数量
OutStockQueryResult outAmount = outAmountList.Find(p => p.WarehouseID == wh.ID && p.MatID == mat.ID);
currentAccount.OutAmount = outAmount != null ? outAmount.Amount : 0;
//计算盘点数量
CheckStockQueryResult checkAmount = checkAmountList.Find(p => p.WarehouseID == wh.ID && p.MatID == mat.ID);
currentAccount.GainAmount = checkAmount != null ? checkAmount.ProfitAmount : 0;
currentAccount.LossAmount = checkAmount != null ? checkAmount.LossAmount : 0;
//计算期末余额
currentAccount.LateAmount = currentAccount.PrimeAmount + currentAccount.InAmount - currentAccount.OutAmount + currentAccount.GainAmount - currentAccount.LossAmount;
if (string.IsNullOrEmpty(currentAccount.ID) == false)
{
//更新
//currentAccount.UPDATEUSER = this.LoginUser.UserID;
currentAccount.UPDATETIME = currentAccount.CREATETIME;
new StockAccountDAL().Update(currentAccount);
}
else
{
if (currentAccount.LateAmount == 0 &&
currentAccount.InAmount == 0 &&
currentAccount.OutAmount == 0 &&
currentAccount.GainAmount == 0 &&
currentAccount.LossAmount == 0 &&
currentAccount.LateAmount == 0)
{
continue;
}
//插入
currentAccount.ID = Guid.NewGuid().ToString();
//currentAccount.CREATEUSER = this.LoginUser.UserID;
currentAccount.CREATETIME = DateTime.Now;
//currentAccount.UPDATEUSER = this.LoginUser.UserID;
currentAccount.UPDATETIME = currentAccount.CREATETIME;
new StockAccountDAL().Insert(currentAccount);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
