private void Compute(AMSCM.Models.AMSCM amscm, AMSCM.Models.tbMonthlyBalance tbMonthlyBalance, string strOperId, string strOperName)
{
int icnnCheckStatus = (int)StockStatus.Check;
List <tbStockMainAndDetail> ltbStockMainAndDetail = (from main in amscm.tbStockMain
join d in amscm.tbStockDetail on main.cnnMainId equals d.cnnMainId into md
from detail in md.DefaultIfEmpty()
where main.cnnYear == tbMonthlyBalance.cnnYear && main.cnnMonth == tbMonthlyBalance.cnnMonth &&
main.cnnStatus == icnnCheckStatus
orderby main.cnnMainId
select new tbStockMainAndDetail
{
cnnMainId = main.cnnMainId,
//cnvcSupplierCode = main.cnvcSupplierCode,
cnvcWhCode = main.cnvcWhCode,
cnvcDeptId = main.cnvcDeptId,
cnnOperType = main.cnnOperType,
//cndCreateDate = main.cndCreateDate,
//cnvcCreaterId = main.cnvcCreaterId,
//cnvcCreaterName = main.cnvcCreaterName,
//cndCheckDate = main.cndCheckDate,
//cnvcCheckerId = main.cnvcCheckerId,
//cnvcCheckerName = main.cnvcCheckerName,
//cndBusinessDate = main.cndBusinessDate,
cnnYear = main.cnnYear,
cnnMonth = main.cnnMonth,
//cnnStatus = main.cnnStatus,
//cnvcComments = main.cnvcComments,
cnnDetailId = detail.cnnDetailId,
cnvcInvCode = detail.cnvcInvCode,
cnvcComUnitCode = detail.cnvcComUnitCode,
cnnQuantity = detail.cnnQuantity,
cnvcMainComUnitCode = detail.cnvcMainComUnitCode,
cnnMainQuantity = detail.cnnMainQuantity,
cnnPrice = detail.cnnPrice,
cnnAmount = detail.cnnAmount,
isHave = false
//cnnMainPrice = detail.cnnPrice==0?0:detail.cnnAmount / detail.cnnPrice
}).ToList();
var ltbInventory = (from d in amscm.tbInventory
join d1 in amscm.tbComputationUnit on d.cnvcSTComUnitCode equals d1.cnvcComunitCode into dd1
from dd1s in dd1.DefaultIfEmpty()
join d2 in amscm.tbProductClass on d.cnvcInvCCode equals d2.cnvcProductClassCode into dd2
from dd2s in dd2.DefaultIfEmpty()
join d3 in amscm.tbComputationUnit on d.cnvcProduceUnitCode equals d3.cnvcComunitCode into dd3
from dd3s in dd3.DefaultIfEmpty()
select new
{
d.cnvcInvCode,
dd2s.cnvcProductType,
d.cnvcProduceUnitCode,
d.cnvcSTComUnitCode,
cniChangRate = dd1s == null ? 0 : dd1s.cniChangRate,
cniChangeRate2 = dd3s == null ? 0 : dd3s.cniChangRate
}).ToList();
//foreach (tbStockMainAndDetail tbStockMainAndDetail in ltbStockMainAndDetail)
//{
// tbStockMainAndDetail.cnnMainPrice = tbStockMainAndDetail.cnnAmount / tbStockMainAndDetail.cnnMainQuantity;
//}
//利用期初、采购的数据计算成本
var g1 = (from d in ltbStockMainAndDetail
where d.cnnOperType == 0 || d.cnnOperType == 1
group d by new { d.cnvcInvCode, d.cnvcWhCode, d.cnvcDeptId } into g
select new
{
cnvcInvCode = g.Key.cnvcInvCode,
cnvcWhCode = g.Key.cnvcWhCode,
cnvcDeptId = g.Key.cnvcDeptId,
//cnnAvgPrice = g.Sum(s => s.cnnAmount) / g.Sum(s => s.cnnMainQuantity)
cnnAvgPrice = g.Sum(s => s.cnnMainQuantity) == 0?g.Sum(s => s.cnnAmount):
g.Sum(s => s.cnnAmount) / g.Sum(s => s.cnnMainQuantity)
}).ToList();
//var q1 = (from d in ltbStockMainAndDetail where d.cnnOperType==4 || d.cnnOperType==6 select d).ToList();
//1、已有的取出成本
foreach (var g2 in g1)
{
ltbStockMainAndDetail.ForEach(delegate(tbStockMainAndDetail md)
{
if (md.cnvcInvCode == g2.cnvcInvCode && md.cnvcWhCode == g2.cnvcWhCode &&
md.cnvcDeptId == g2.cnvcDeptId &&
md.cnnOperType != 0 && md.cnnOperType != 1
)
{
md.cnnMainPrice = g2.cnnAvgPrice;
md.cnnPrice = md.cnnMainPrice * (md.cnnQuantity == 0?1:md.cnnMainQuantity / md.cnnQuantity);
md.cnnAmount = md.cnnMainPrice * md.cnnMainQuantity;
md.isHave = true;
}
});
}
//2、根据配方算出用量
var lInv = (from d in ltbStockMainAndDetail where d.cnnOperType != 0 && d.cnnOperType != 1 && !d.isHave select d.cnvcInvCode).Distinct().ToList();
List <AMSCM.Models.tbBillOfMaterials> lbom = ServiceHelper.getBOM(amscm);
List <tbStockMainAndDetail> lm = new List <tbStockMainAndDetail>();
foreach (string strInvCode in lInv)
{
if (!string.IsNullOrEmpty(strInvCode))
{
//获取所有子件
List <AMSCM.Models.tbBillOfMaterials> lComponentInv = ServiceHelper.ProcBOM(lbom, strInvCode);
//获取子件生产单位数量
var lcis = from d in lComponentInv
group d by d.cnvcComponentInvCode into g
select new { cnvcInvCode = g.Key, cnnQuantity = g.Sum(s => s.cnnBaseQtyN) };
//获取主计量单位成本
decimal dPrice = (from d in lcis
join d1 in ltbInventory on d.cnvcInvCode equals d1.cnvcInvCode into dd1
from dd1s in dd1.DefaultIfEmpty()
join d2 in g1 on d.cnvcInvCode equals d2.cnvcInvCode into dd2
from dd2s in dd2.DefaultIfEmpty()
select d.cnnQuantity *(dd1s == null ? 0 : dd1s.cniChangeRate2) * (dd2s == null || !dd2s.cnnAvgPrice.HasValue ? 0 : dd2s.cnnAvgPrice.Value)).Sum();
lm.Add(new tbStockMainAndDetail()
{
cnvcInvCode = strInvCode, cnnMainPrice = dPrice
});
}
}
;
ltbStockMainAndDetail.ForEach(delegate(tbStockMainAndDetail md)
{
if (md.cnnOperType != 0 && md.cnnOperType != 1 && !md.isHave)
{
decimal?dcnnMainPrice = (from d in lm where d.cnvcInvCode == md.cnvcInvCode select d.cnnMainPrice).FirstOrDefault();
md.cnnMainPrice = dcnnMainPrice;
md.cnnAmount = dcnnMainPrice * md.cnnMainQuantity;
md.cnnPrice = md.cnnQuantity == 0?md.cnnAmount:md.cnnAmount / md.cnnQuantity;
}
});
//3、更新成本
ltbStockMainAndDetail.ForEach(delegate(tbStockMainAndDetail md)
{
if (md.cnnOperType != 0 && md.cnnOperType != 1 && md.cnnDetailId.HasValue)
{
AMSCM.Models.tbStockDetail tbStockDetail = amscm.tbStockDetail.Find(md.cnnDetailId);
tbStockDetail.cnnPrice = md.cnnPrice.Value;
tbStockDetail.cnnAmount = md.cnnAmount.Value;
}
});
amscm.SaveChanges();
}
private void tbStockMainExportToExcel(HttpContext context)
{
string fileName = "库存主表.xls";
List <AMSCM.Models.tbStockMain> ltbStockMain = new List <AMSCM.Models.tbStockMain>();
using (AMSCM.Models.AMSCM amscm = new AMSCM.Models.AMSCM())
{
var q = QueryCondition(context, amscm);
ltbStockMain = q.OrderBy(o => o.cnnMainId).ToList();
}
DataTable dt = List2DataTable(context, ltbStockMain);
GridView view = new GridView();
view.AutoGenerateColumns = false;
BoundField field;
field = new BoundField();
field.DataField = "cnnId";
field.HeaderText = "";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcSupplierCode";
field.HeaderText = "";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcWhCode";
field.HeaderText = "";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcDeptId";
field.HeaderText = "";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcOperType";
field.HeaderText = "";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cndCreateDate";
field.HeaderText = "";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcCreaterId";
field.HeaderText = "";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcCreaterName";
field.HeaderText = "";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cndCheckDate";
field.HeaderText = "";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcCheckerId";
field.HeaderText = "";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcCheckerName";
field.HeaderText = "";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cndBusinessDate";
field.HeaderText = "";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnnYear";
field.HeaderText = "";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnnMonth";
field.HeaderText = "";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnnStatus";
field.HeaderText = "";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcComments";
field.HeaderText = "";
view.Columns.Add(field);
view.DataSource = dt;
view.DataBind();
ServiceHelper.DoExportToExcel(context, fileName, view);
}
private void ComputationGroupExportToExcel(HttpContext context)
{
string fileName = "库存统计表.xls";
List <tbStockReportData> ltbStockReportData = new List <tbStockReportData>();
using (AMSCM.Models.AMSCM amscm = new AMSCM.Models.AMSCM())
{
var q = QueryCondition(context, amscm);
ltbStockReportData = q.OrderBy(o => o.cnvcInvCode).ToList();
}
DataTable dt = List2DataTable(context, ltbStockReportData);
GridView view = new GridView();
view.AutoGenerateColumns = false;
BoundField field = new BoundField();
field.DataField = "cnvcInvCode";
field.HeaderText = "存货编码";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcInvName";
field.HeaderText = "存货名称";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcSTComUnitName";
field.HeaderText = "单位";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnnInitQuantity";
field.HeaderText = "期初数量";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnnInQuantity";
field.HeaderText = "本期入库数量";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnnOutQuantity";
field.HeaderText = "本期出库数量";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnnEndQuantity";
field.HeaderText = "结余数量";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnnSTPrice";
field.HeaderText = "单位成本";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnnEndAmount";
field.HeaderText = "库存成本";
view.Columns.Add(field);
view.DataSource = dt;
view.DataBind();
ServiceHelper.DoExportToExcel(context, fileName, view);
}
private void tbWarehouseExportToExcel(HttpContext context)
{
string fileName = "仓库.xls";
List <AMSCM.Models.tbWarehouse> ltbWarehouse = new List <AMSCM.Models.tbWarehouse>();
using (AMSCM.Models.AMSCM amscm = new AMSCM.Models.AMSCM())
{
var q = QueryCondition(context, amscm);
ltbWarehouse = q.OrderBy(o => o.cnvcWhCode).ToList();
}
DataTable dt = List2DataTable(context, ltbWarehouse);
GridView view = new GridView();
view.AutoGenerateColumns = false;
BoundField field;
field = new BoundField();
field.DataField = "cnvcWhCode";
field.HeaderText = "仓库编码";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcWhName";
field.HeaderText = "仓库名称";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcDepID";
field.HeaderText = "部门编码";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcDepIDComments";
field.HeaderText = "部门名称";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcWhAddress";
field.HeaderText = "地址";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcWhPhone";
field.HeaderText = "电话";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcWhPerson";
field.HeaderText = "负责人编码";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcWhPersonComments";
field.HeaderText = "负责人姓名";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnvcWhComments";
field.HeaderText = "描述";
view.Columns.Add(field);
field = new BoundField();
field.DataField = "cnbInvalidComments";
field.HeaderText = "是否失效";
view.Columns.Add(field);
view.DataSource = dt;
view.DataBind();
ServiceHelper.DoExportToExcel(context, fileName, view);
}