public string ValidarExclusao(UEN uen)
{
BIZObra bizObra = new BIZObra();
List <ObraEtapaGastoRealizado> lstObras = new List <ObraEtapaGastoRealizado>();
BIZOrdemPagamento bizOP = new BIZOrdemPagamento();
List <OrdemPagamentoItem> lstOPItens = new List <OrdemPagamentoItem>();
string Msg = string.Empty;
lstObras = bizObra.PesquisarGastosRealizados(new ObraEtapaGastoRealizado()
{
idUEN = uen.idUEN
});
if (lstObras.Count > 0)
{
Msg += Environment.NewLine + "Esta UEN está associada a alguma Obra";
}
lstOPItens = bizOP.PesquisarOrdemPagamentoItem(new OrdemPagamentoItem()
{
idUEN = uen.idUEN
});
if (lstOPItens.Count > 0)
{
Msg += Environment.NewLine + "Esta UEN está associada a alguma Ordem de Pagamento";
}
return(Msg);
}
private void VisualizarUEN()
{
UEN uen = new UEN();
int idUENSelecionada = 0;
int linhaGrid = 0;
if (gvUEN.RowCount == 0)
{
return;
}
linhaGrid = gvUEN.SelectedCells[0].RowIndex;
idUENSelecionada = int.Parse(gvUEN[0, linhaGrid].Value.ToString());
uen = bizUEN.PesquisarUEN(new UEN()
{
idUEN = idUENSelecionada
})[0];
UENManutencao form = new UENManutencao(uen);
form.ShowDialog();
btPesquisar_Click(null, null);
}
private void CarregarGrid(UEN uenFiltro)
{
List <UEN> lstUEN = new List <UEN>();
this.Cursor = Cursors.WaitCursor;
try
{
lstUEN = bizUEN.PesquisarUEN(uenFiltro).OrderBy(x => x.Descricao).ToList();
LimparGrid();
foreach (UEN itemCentroCusto in lstUEN)
{
gvUEN.Rows.Add(new object[]
{
itemCentroCusto.idUEN,
itemCentroCusto.Descricao
});
}
}
catch (SqlException)
{
MessageBox.Show(helper.RetornarMensagemPadraoErroAcessoBD(), "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception)
{
MessageBox.Show(helper.RetornarMensagemPadraoErroGenerico(), "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
this.Cursor = Cursors.Default;
}
private OrdemPagamento PreencherCamposOrigatorios()
{
UEN UEN = new UEN();
CentroCusto centroCusto = new CentroCusto();
Despesa despesa = new Despesa();
ObraEtapa obra = new ObraEtapa();
OrdemPagamento ordemPagamento = new OrdemPagamento();
ordemPagamento.idEmpresa = new BIZEmpresa().PesquisarEmpresa(new Empresa())[0].idEmpresa;
ordemPagamento.idObraEtapa = new BIZObra().PesquisarObraEtapa(new ObraEtapa()
{
UnitTest = 1
})[0].idObraEtapa;
ordemPagamento.idFavorecido = new BIZFornecedor().PesquisarFornecedor(new Fornecedor()
{
UnitTest = 1
})[0].idFornecedor;
ordemPagamento.idContaBancaria = new BIZFornecedor().PesquisarFornecedorContaBancaria(new FornecedorContaBancaria()
{
idFornecedor = ordemPagamento.idFavorecido
})[0].idContaBancaria;
ordemPagamento.idSolicitante = 1;
ordemPagamento.idAutorizado = 9;
ordemPagamento.dataSolicitacao = DateTime.Now;
ordemPagamento.Status = string.Empty;
UEN = new BIZUEN().PesquisarUEN(new UEN())[0];
centroCusto = new BIZCentroCusto().PesquisarCentroCusto(new CentroCusto())[0];
despesa = new BIZDespesa().PesquisarDespesa(new Despesa())[0];
ordemPagamento.lstItens = new List <OrdemPagamentoItem>();
ordemPagamento.lstItens.Add(new OrdemPagamentoItem()
{
idUEN = UEN.idUEN,
idCentroCusto = centroCusto.idCentroCusto,
idDespesa = despesa.idDespesa,
idUsuarioPagamento = new BIZUsuario().PesquisarUsuario(new Usuario())[0].idUsuario,
idAbastecimento = 9,
Valor = Decimal.Parse("999.99"),
valorPago = Decimal.Parse("999.99"),
dataVencimento = DateTime.Now,
dataPagamento = DateTime.Now,
numeroParcela = 9,
totalParcelas = 999,
Desconto = 111,
Multa = 222,
UnitTest = 1
});
ordemPagamento.Observacao = "Observacão";
ordemPagamento.Cancelada = 9;
ordemPagamento.observacaoCancelada = "teste cancelamento";
ordemPagamento.UnitTest = 1;
return(ordemPagamento);
}
public string IncluirUEN(int idUsuario, UEN uen, out int idUEN)
{
DataAccess dao = new DataAccess();
List <_Transacao> lstComandos = new List <_Transacao>();
Dictionary <string, string> lstParametros = new Dictionary <string, string>();
Dictionary <string, string> lstParametrosCentroCusto = new Dictionary <string, string>();
string Msg = string.Empty;
try
{
Msg = this.ValidarCamposObrigatorios(uen);
idUEN = 0;
if (Msg == string.Empty)
{
lstParametros = MontarParametrosExecutarUEN(uen);
using (DataSet ds = dao.Pesquisar("SP_UEN_INCLUIR", lstParametros))
{
DataRow dr = ds.Tables[0].Rows[0];
idUEN = int.Parse(dr[0].ToString());
}
lstComandos.AddRange(this.IncluirCentrosCustos(idUEN, uen.lstCentrosCustos));
dao.ExecutarTransacao(lstComandos);
lstParametros = new Dictionary <string, string>();
lstParametros.Add("@idUsuario", idUsuario.ToString());
lstParametros.Add("@idUEN", idUEN.ToString());
lstParametros.Add("@UnitTest", uen.UnitTest.ToString());
dao.Executar("SP_USUARIOSUEN_INCLUIR", lstParametros);
}
}
catch (Exception ex)
{
string parametrosSQL = string.Empty;
parametrosSQL = helper.ConcatenarParametrosSQL(lstParametros);
LogErro log = new LogErro()
{
procedureSQL = "SP_UEN_INCLUIR",
parametrosSQL = parametrosSQL,
mensagemErro = ex.ToString()
};
bizLogErro.IncluirLogErro(log);
throw ex;
}
return(Msg);
}
private Dictionary <string, string> MontarParametrosPesquisarUEN(UEN uen)
{
Dictionary <string, string> lstParametros = new Dictionary <string, string>();
lstParametros.Add("@idUEN", uen.idUEN.Equals(0) ? null : uen.idUEN.ToString());
lstParametros.Add("@Descricao", string.IsNullOrEmpty(uen.Descricao) ? null : uen.Descricao);
lstParametros.Add("@Administrativo", uen.Administrativo == null ? null : uen.Administrativo.ToString());
lstParametros.Add("@idUsuario", UsuarioLogado.idUsuario.ToString());
lstParametros.Add("@UnitTest", uen.UnitTest.Equals(0) ? null : uen.UnitTest.ToString());
return(lstParametros);
}
private string ValidarCamposObrigatorios(UEN uen)
{
string Msg = string.Empty;
if (string.IsNullOrEmpty(uen.Descricao))
{
Msg += Environment.NewLine + "Favor informar a Descrição";
}
return(Msg);
}
public string AlterarUEN(UEN uen)
{
DataAccess dao = new DataAccess();
List <_Transacao> lstComandos = new List <_Transacao>();
Dictionary <string, string> lstParametros = new Dictionary <string, string>();
Dictionary <string, string> lstExclusaoCentroCusto = new Dictionary <string, string>();
Dictionary <string, string> lstParametrosCentroCusto = new Dictionary <string, string>();
string Msg = string.Empty;
try
{
Msg = ValidarCamposObrigatorios(uen);
if (Msg == string.Empty)
{
lstComandos.Add(new _Transacao()
{
nomeProcedure = "SP_UEN_ALTERAR",
lstParametros = MontarParametrosExecutarUEN(uen)
});
lstExclusaoCentroCusto.Add("@idUEN", uen.idUEN.ToString());
lstComandos.Add(new _Transacao()
{
nomeProcedure = "SP_UEN_CENTROSCUSTOS_EXCLUIR",
lstParametros = lstExclusaoCentroCusto
});
lstComandos.AddRange(this.IncluirCentrosCustos(uen.idUEN, uen.lstCentrosCustos));
dao.ExecutarTransacao(lstComandos);
}
}
catch (Exception ex)
{
string parametrosSQL = string.Empty;
parametrosSQL = helper.ConcatenarParametrosSQL(lstParametros);
LogErro log = new LogErro()
{
procedureSQL = "SP_UEN_ALTERAR",
parametrosSQL = parametrosSQL,
mensagemErro = ex.ToString()
};
bizLogErro.IncluirLogErro(log);
throw ex;
}
return(Msg);
}
private Dictionary <string, string> MontarParametrosExecutarUEN(UEN uen)
{
Dictionary <string, string> lstParametros = new Dictionary <string, string>();
lstParametros.Add("@idUEN", uen.idUEN.ToString());
lstParametros.Add("@Descricao", uen.Descricao);
lstParametros.Add("@Administrativo", uen.Administrativo.ToString());
lstParametros.Add("@UnitTest", uen.UnitTest.ToString());
return(lstParametros);
}
public void UENAlterar()
{
string Msg = string.Empty;
UEN uen = new UEN();
uen = this.PreencherCamposObrigatorios();
uen.Descricao = "Alterado";
Msg = bizUEN.AlterarUEN(uen);
Assert.AreEqual(string.Empty, Msg);
}
public string ExcluirUEN(UEN uen)
{
DataAccess dao = new DataAccess();
List <_Transacao> lstComandos = new List <_Transacao>();
Dictionary <string, string> lstParametros = new Dictionary <string, string>();
string Msg = string.Empty;
try
{
Msg = this.ValidarExclusao(uen);
if (Msg == string.Empty || uen.UnitTest == 1)
{
lstParametros.Add("@idUEN", uen.idUEN.ToString());
lstParametros.Add("@UnitTest", uen.UnitTest.Equals(0) ? null : uen.UnitTest.ToString());
lstComandos.Add(new _Transacao()
{
nomeProcedure = "SP_UEN_CENTROSCUSTOS_EXCLUIR",
lstParametros = lstParametros
});
lstComandos.Add(new _Transacao()
{
nomeProcedure = "SP_UEN_EXCLUIR",
lstParametros = lstParametros
});
dao.ExecutarTransacao(lstComandos);
}
}
catch (Exception ex)
{
string parametrosSQL = string.Empty;
parametrosSQL = helper.ConcatenarParametrosSQL(lstParametros);
LogErro log = new LogErro()
{
procedureSQL = "SP_UEN_EXCLUIR",
parametrosSQL = parametrosSQL,
mensagemErro = ex.ToString()
};
bizLogErro.IncluirLogErro(log);
throw ex;
}
return(Msg);
}
private UEN PreencherCamposObrigatorios()
{
UEN uen = new UEN();
uen.Descricao = "Teste UEN";
uen.Administrativo = 9;
uen.lstCentrosCustos = new List <UENCentroCusto>();
uen.lstCentrosCustos.Add(new UENCentroCusto()
{
idUEN = 99,
idCentroCusto = 99,
UnitTest = 1
});
uen.UnitTest = 1;
return(uen);
}
public List <UEN> PesquisarUEN(UEN uen)
{
DataAccess dao = new DataAccess();
Dictionary <string, string> lstParametros = new Dictionary <string, string>();
List <UEN> lstUEN = new List <UEN>();
try
{
lstParametros = MontarParametrosPesquisarUEN(uen);
using (DataSet ds = dao.Pesquisar("SP_UEN_CONSULTAR", lstParametros))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
UEN uenItem = new UEN();
uenItem.idUEN = int.Parse(dr["idUEN"].ToString());
uenItem.Descricao = dr["Descricao"].ToString();
uenItem.Administrativo = int.Parse(dr["Administrativo"].ToString());
uenItem.lstCentrosCustos = this.PesquisarCentrosCustosAssociados(new UENCentroCusto()
{
idUEN = uenItem.idUEN
});
lstUEN.Add(uenItem);
}
}
}
catch (Exception ex)
{
string parametrosSQL = string.Empty;
parametrosSQL = helper.ConcatenarParametrosSQL(lstParametros);
LogErro log = new LogErro()
{
procedureSQL = "SP_UEN_CONSULTAR",
parametrosSQL = parametrosSQL,
mensagemErro = ex.ToString()
};
bizLogErro.IncluirLogErro(log);
throw ex;
}
return(lstUEN);
}
public void UENExcluir()
{
UEN uen = new UEN();
List <UEN> lstUEN = new List <UEN>();
string Msg = string.Empty;
uen.idUEN = 0;
uen.UnitTest = 1;
Msg = bizUEN.ExcluirUEN(uen);
lstUEN = bizUEN.PesquisarUEN(new UEN()
{
UnitTest = 1
});
Assert.AreEqual(true, lstUEN.Count == 0);
}
public void UENIncluir()
{
Dictionary <string, string> lstParametros = new Dictionary <string, string>();
UEN uen = new UEN();
int idUEN = 0;
string Msg = string.Empty;
uen = this.PreencherCamposObrigatorios();
Msg = bizUEN.IncluirUEN(1, uen, out idUEN);
lstParametros.Add("@idUsuario", UsuarioLogado.idUsuario.ToString());
lstParametros.Add("@idUEN", idUEN.ToString());
lstParametros.Add("@UnitTest", "1");
new DataAccess().Executar("SP_USUARIOSUEN_INCLUIR", lstParametros);
Assert.AreEqual(string.Empty, Msg);
}
private void CarregarUEN(UEN uenSelecionada)
{
try
{
lbCodigo.Text = uenSelecionada.idUEN.ToString();
tbDescricao.Text = uenSelecionada.Descricao;
chkAdministrativo.Checked = uenSelecionada.Administrativo == 1 ? true : false;
this.CarregarCentrosCustosCadastrados(lstCentrosCustosCadastrados);
this.CarregarCentrosCustosAssociados();
}
catch (SqlException)
{
MessageBox.Show(helper.RetornarMensagemPadraoErroAcessoBD(), "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception)
{
MessageBox.Show(helper.RetornarMensagemPadraoErroGenerico(), "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void LimparCampos()
{
lbCodigo.Text = "0";
tbDescricao.Text = string.Empty;
chkAdministrativo.Checked = false;
tbFiltroDespesa.Text = string.Empty;
uenSelecionada = new UEN();
uenSelecionada.lstCentrosCustos = new List <UENCentroCusto>();
while (gvCentrosCustosCadastrados.Rows.Count > 0)
{
gvCentrosCustosCadastrados.Rows.RemoveAt(0);
}
this.CarregarCentrosCustosCadastrados(lstCentrosCustosCadastrados);
while (gvCentrosCustosAssociados.Rows.Count > 0)
{
gvCentrosCustosAssociados.Rows.RemoveAt(0);
}
}
private void btPesquisar_Click(object sender, EventArgs e)
{
if (cbPesquisaCampo.FindStringExact(cbPesquisaCampo.Text) != -1)
{
UEN uenFiltro = new UEN();
uenFiltro = (UEN) new ClassProperties().RetornarObjetoFiltro(new UEN(), (PropertyInfo)cbPesquisaCampo.SelectedValue, tbPesquisaValor.Text);
if (uenFiltro != null)
{
CarregarGrid(uenFiltro);
}
else
{
MessageBox.Show("Informação inválida para " + cbPesquisaCampo.Text + ". Favor verificar.", "Valor incorreto", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
else
{
MessageBox.Show("Campo para o filtro de pesquisa inválido. Favor verificar.", "Opção inválida", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
public virtual List <UEN> Pesquisar(UEN uen)
{
return(new List <UEN>());
}
public virtual string Incluir(UEN uen, out int idUEN)
{
idUEN = 0;
return("");
}
public virtual string Alterar(UEN uen)
{
return("");
}
public virtual string ValidarExclusao(UEN uen)
{
return("");
}
public virtual string Excluir(UEN uen)
{
return("");
}
public List <GarmentStockReportViewModel> GetStockQuery(string ctg, string unitcode, DateTime?datefrom, DateTime?dateto, int offset)
{
DateTime DateFrom = datefrom == null ? new DateTime(1970, 1, 1) : (DateTime)datefrom;
DateTime DateTo = dateto == null ? DateTime.Now : (DateTime)dateto;
var lastdate = dbContext.BalanceStocks.OrderByDescending(x => x.CreateDate).Select(x => x.CreateDate).FirstOrDefault();
var BalaceStock = (from a in dbContext.BalanceStocks
where a.CreateDate == lastdate
group a by new { a.ArticleNo, a.EPOID, a.EPOItemId } into data
select new
{
BalanceStockId = data.FirstOrDefault().BalanceStockId,
ArticleNo = data.FirstOrDefault().ArticleNo,
EPOID = data.FirstOrDefault().EPOID,
EPOItemId = data.FirstOrDefault().EPOItemId,
CloseStock = (double)data.Sum(x => x.CloseStock),
ClosePrice = (decimal)data.Sum(x => x.ClosePrice)
}).ToList();
//var saldoawalbalanceepoid = SaldoAwal.Select(x => x.EPOID).ToList();
var SAkhir = (from a in dbContext.GarmentUnitReceiptNotes
join b in dbContext.GarmentUnitReceiptNoteItems on a.Id equals b.URNId
join h in dbContext.GarmentDeliveryOrderDetails on b.DODetailId equals h.Id
join f in dbContext.GarmentInternalPurchaseOrders on b.POId equals f.Id
join e in dbContext.GarmentReceiptCorrectionItems on b.Id equals e.URNItemId into RC
from ty in RC.DefaultIfEmpty()
join c in dbContext.GarmentUnitExpenditureNoteItems on b.Id equals c.URNItemId into UE
from ww in UE.DefaultIfEmpty()
join r in dbContext.GarmentUnitExpenditureNotes on ww.UENId equals r.Id into UEN
from dd in UEN.DefaultIfEmpty()
join epoItem in dbContext.GarmentExternalPurchaseOrderItems on b.EPOItemId equals epoItem.Id into EP
from epoItem in EP.DefaultIfEmpty()
join epo in dbContext.GarmentExternalPurchaseOrders on epoItem.GarmentEPOId equals epo.Id into EPO
from epo in EPO.DefaultIfEmpty()
where h.CodeRequirment == (String.IsNullOrWhiteSpace(ctg) ? h.CodeRequirment : ctg) &&
a.IsDeleted == false && b.IsDeleted == false &&
a.CreatedUtc.AddHours(offset).Date >= DateFrom.Date &&
a.CreatedUtc.AddHours(offset).Date <= DateTo.Date &&
a.UnitCode == (string.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode)
select new
{
URNId = a.Id,
URNItemsId = b.Id,
DODEtailId = h.Id,
IPOId = f.Id,
RCorrId = ty == null ? 0 : ty.Id,
UENItemsId = ww == null ? 0 : ww.Id,
UENId = dd == null ? 0 : dd.Id,
EPOItemId = epoItem == null ? 0 : epoItem.Id,
EPOId = epo == null ? 0 : epo.Id,
UnitCode = a.UnitCode,
UnitSenderCode = dd == null ? "-" : dd.UnitSenderCode
}).Distinct().ToList();
//SaldoAkhir = SaldoAkhir.Where(x => x.UENNo.Contains((String.IsNullOrWhiteSpace(unitcode) ? x.UnitCode : unitcode)) || x.UnitCode == ((String.IsNullOrWhiteSpace(unitcode) ? x.UnitCode : unitcode))).Select(x => x).ToList();
var SAkhirs = SAkhir.Select(x => x).ToList();
var unitreceiptnoteIds = SAkhirs.Select(x => x.URNId).ToList();
var unitreceiptnotes = dbContext.GarmentUnitReceiptNotes.Where(x => unitreceiptnoteIds.Contains(x.Id)).Select(s => new { s.ReceiptDate, s.URNType, s.UnitCode, s.Id }).ToList();
var unitreceiptnoteItemIds = SAkhirs.Select(x => x.URNItemsId).ToList();
var unitreceiptnoteItems = dbContext.GarmentUnitReceiptNoteItems.Where(x => unitreceiptnoteItemIds.Contains(x.Id)).Select(s => new { s.ProductCode, s.ProductName, s.ProductRemark, s.RONo, s.SmallUomUnit, s.POSerialNumber, s.ReceiptQuantity, s.PricePerDealUnit, s.Id }).ToList();
var deliveryorderdetailid = SAkhirs.Select(x => x.DODEtailId).ToList();
var deliveryorderdetails = dbContext.GarmentDeliveryOrderDetails.Where(x => deliveryorderdetailid.Contains(x.Id)).Select(s => new { s.CodeRequirment, s.Id }).ToList();
var purchaseorderIds = SAkhirs.Select(x => x.IPOId).ToList();
var purchaseorders = dbContext.GarmentInternalPurchaseOrders.Where(x => purchaseorderIds.Contains(x.Id)).Select(s => new { s.BuyerCode, s.Id, s.Article }).ToList();
var receiptCorrectionitemIds = SAkhirs.Select(x => x.RCorrId).ToList();
var receiptCorrectionitems = dbContext.GarmentReceiptCorrectionItems.Where(x => receiptCorrectionitemIds.Contains(x.Id)).Select(s => new { s.CorrectionQuantity, s.PricePerDealUnit, s.Id }).ToList();
var unitexpenditureitemIds = SAkhirs.Select(x => x.UENItemsId).ToList();
var unitexpenditureitems = dbContext.GarmentUnitExpenditureNoteItems.Where(x => unitexpenditureitemIds.Contains(x.Id)).Select(s => new { s.PricePerDealUnit, s.Quantity, s.Id }).ToList();
var unitexpenditureIds = SAkhirs.Select(x => x.UENId).ToList();
var unitexpenditures = dbContext.GarmentUnitExpenditureNotes.Where(x => unitexpenditureIds.Contains(x.Id)).Select(s => new { s.UnitRequestCode, s.UnitSenderCode, s.ExpenditureTo, s.Id }).ToList();
var externalpurchaseorderitemIds = SAkhirs.Select(x => x.EPOItemId).ToList();
var externalpurchaseorderitems = dbContext.GarmentExternalPurchaseOrderItems.Where(x => externalpurchaseorderitemIds.Contains(x.Id)).Select(s => new { s.Id }).ToList();
var externalpurchaseorderIds = SAkhirs.Select(x => x.EPOId).ToList();
var externalpurchaseorders = dbContext.GarmentExternalPurchaseOrders.Where(x => externalpurchaseorderIds.Contains(x.Id)).Select(s => new { s.PaymentMethod, s.Id }).ToList();
//var balancestocks = SaldoAwal.Where(x => externalpurchaseorderitemIds.Contains((long)x.EPOItemId)).Select(s => new { s.BeginningBalanceQty, s.BeginningBaancePrice, s.EPOItemId}).ToList();
List <GarmentStockReportViewModel> SaldoAkhir = new List <GarmentStockReportViewModel>();
foreach (var item in SAkhir)
{
var unitreceiptnote = unitreceiptnotes.Where(x => x.Id.Equals(item.URNId)).FirstOrDefault();
var unitreceiptnoteItem = unitreceiptnoteItems.FirstOrDefault(x => x.Id.Equals(item.URNItemsId));
var deliveryorderdetail = deliveryorderdetails.FirstOrDefault(x => x.Id.Equals(item.DODEtailId));
var purchaseorder = purchaseorders.FirstOrDefault(x => x.Id.Equals(item.IPOId));
var receiptCorrectionitem = receiptCorrectionitems.FirstOrDefault(x => x.Id.Equals(item.RCorrId));
var unitexpenditureitem = unitexpenditureitems.FirstOrDefault(x => x.Id.Equals(item.UENItemsId));
var unitexpenditure = unitexpenditures.FirstOrDefault(x => x.Id.Equals(item.UENId));
var externalpurchaseorderitem = externalpurchaseorderitems.FirstOrDefault(x => x.Id.Equals(item.EPOItemId));
var externalpurchaseorder = externalpurchaseorders.FirstOrDefault(x => x.Id.Equals(item.EPOId));
//var balancestock = balancestocks.FirstOrDefault(x => x.EPOItemId.Equals(item.EPOItemId));
SaldoAkhir.Add(new GarmentStockReportViewModel
{
BeginningBalanceQty = 0,
BeginningBalanceUom = unitreceiptnoteItem.SmallUomUnit,
Buyer = purchaseorder.BuyerCode,
EndingBalanceQty = 0,
EndingUom = unitreceiptnoteItem.SmallUomUnit,
ExpandUom = unitreceiptnoteItem.SmallUomUnit,
ExpendQty = unitexpenditureitem == null ? 0 : unitexpenditureitem.Quantity,
NoArticle = purchaseorder.Article,
PaymentMethod = externalpurchaseorder == null ? "-" : externalpurchaseorder.PaymentMethod,
PlanPo = unitreceiptnoteItem == null ? "-" : unitreceiptnoteItem.POSerialNumber,
POId = purchaseorder.Id,
ProductCode = unitreceiptnoteItem.ProductCode,
ProductName = unitreceiptnoteItem.ProductName,
ProductRemark = unitreceiptnoteItem.ProductRemark,
ReceiptCorrectionQty = receiptCorrectionitem == null ? 0 : (decimal)receiptCorrectionitem.CorrectionQuantity,
ReceiptQty = unitreceiptnoteItem.ReceiptQuantity,
ReceiptUom = unitreceiptnoteItem.SmallUomUnit,
RO = unitreceiptnoteItem.RONo,
UNitCode = unitreceiptnote.UnitCode,
UnitSenderCode = unitexpenditure == null ? "-" : unitexpenditure.UnitSenderCode,
UnitRequestCode = unitexpenditure == null ? "-" : unitexpenditure.UnitRequestCode
});
}
SaldoAkhir = (from query in SaldoAkhir
group query by new { query.ProductCode, query.ProductName, query.RO, query.PlanPo, query.POId, query.UNitCode, query.UnitSenderCode } into data
//group query by new { query.POId } into data
select new GarmentStockReportViewModel
{
BeginningBalanceQty = data.Sum(x => x.BeginningBalanceQty),
BeginningBalanceUom = data.FirstOrDefault().BeginningBalanceUom,
Buyer = data.FirstOrDefault().Buyer,
EndingBalanceQty = data.FirstOrDefault().EndingBalanceQty,
EndingUom = data.FirstOrDefault().EndingUom,
ExpandUom = data.FirstOrDefault().EndingUom,
ExpendQty = data.Sum(x => x.ExpendQty),
NoArticle = data.FirstOrDefault().NoArticle,
PaymentMethod = data.FirstOrDefault().PaymentMethod,
PlanPo = data.FirstOrDefault().PlanPo,
POId = data.FirstOrDefault().POId,
ProductCode = data.FirstOrDefault().ProductCode,
ProductName = data.FirstOrDefault().ProductName,
ProductRemark = data.FirstOrDefault().ProductRemark,
ReceiptCorrectionQty = data.Sum(x => x.ReceiptCorrectionQty),
//ReceiptQty = data.Sum(x => x.ReceiptQty),
ReceiptQty = data.FirstOrDefault().ReceiptQty,
ReceiptUom = data.FirstOrDefault().ReceiptUom,
RO = data.FirstOrDefault().RO,
UNitCode = data.FirstOrDefault().UNitCode,
UnitSenderCode = data.FirstOrDefault().UnitSenderCode,
UnitRequestCode = data.FirstOrDefault().UnitRequestCode
}).ToList();
var SaldoAkhirId = SaldoAkhir.Select(x => x.POId).ToList();
var SA = (from a in dbContext.GarmentUnitReceiptNotes
join b in dbContext.GarmentUnitReceiptNoteItems on a.Id equals b.URNId
join i in dbContext.GarmentInternalPurchaseOrders on b.POId equals i.Id
join c in dbContext.GarmentDeliveryOrderDetails on b.DODetailId equals c.Id
join e in dbContext.GarmentUnitExpenditureNoteItems on b.Id equals e.URNItemId into UENItem
from ee in UENItem.DefaultIfEmpty()
join h in dbContext.GarmentReceiptCorrectionItems on b.Id equals h.URNItemId into RC
from hh in RC.DefaultIfEmpty()
join f in dbContext.GarmentUnitExpenditureNotes on ee.UENId equals f.Id into UEN
from ff in UEN.DefaultIfEmpty()
join g in dbContext.GarmentExternalPurchaseOrders on b.EPOItemId equals g.Id into Exter
from gg in Exter.DefaultIfEmpty()
join epoItem in dbContext.GarmentExternalPurchaseOrderItems on b.EPOItemId equals epoItem.Id into EP
from epoItem in EP.DefaultIfEmpty()
join epo in dbContext.GarmentExternalPurchaseOrders on epoItem.GarmentEPOId equals epo.Id into EPO
from epo in EPO.DefaultIfEmpty()
where c.CodeRequirment == (string.IsNullOrWhiteSpace(ctg) ? c.CodeRequirment : ctg) &&
a.IsDeleted == false && b.IsDeleted == false &&
a.CreatedUtc > lastdate && a.CreatedUtc < DateFrom &&
a.UnitCode == (string.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode) &&
SaldoAkhirId.Contains(i.Id)
select new
{
//BSId = bb == null ? "" : bb.BalanceStockId,
URNItemsId = b == null ? 0 : b.Id,
UENItemId = ee == null ? 0 : ee.Id,
RCorItemId = hh != null ? hh.Id : 0,
URNId = a == null ? 0 : a.Id,
DODEtailId = c == null ? 0 : c.Id,
IPOId = i == null ? 0 : i.Id,
RCorrId = hh == null ? 0 : hh.Id,
//UENItemsId = ee == null ? 0 : ee.Id,
UENId = ff == null ? 0 : ff.Id,
UnitCode = a == null ? "-" : a.UnitCode,
UnitSenderCode = ff == null ? "-" : ff.UnitSenderCode,
EPOItemId = epoItem == null ? 0 : epoItem.Id,
EPOId = epo == null ? 0 : epo.Id,
//SAQty = (bb != null ? bb.CloseStock : 0) + (hh != null ? hh.CorrectionQuantity : 0) + (double)b.ReceiptQuantity - (ee != null ? ee.Quantity : 0),
//SAPrice = (bb != null ? bb.ClosePrice : 0) + (hh != null ? (decimal)hh.PricePerDealUnit * (decimal)hh.CorrectionQuantity : 0) + b.PricePerDealUnit * b.ReceiptQuantity - (ee != null ? (decimal)ee.PricePerDealUnit * (decimal)ee.Quantity : 0)
}).ToList();
var SaldoAwals = SA.Select(x => x).ToList().Distinct();
var saldoawalunitreceiptnoteIds = SaldoAwals.Select(x => x.URNId).ToList();
var saldoawalunitreceiptnotes = dbContext.GarmentUnitReceiptNotes.Where(x => saldoawalunitreceiptnoteIds.Contains(x.Id)).Select(s => new { s.ReceiptDate, s.URNType, s.UnitCode, s.Id }).ToList();
var saldoawalunitreceiptnoteItemIds = SaldoAwals.Select(x => x.URNItemsId).ToList();
var saldoawalunitreceiptnoteItems = dbContext.GarmentUnitReceiptNoteItems.Where(x => saldoawalunitreceiptnoteItemIds.Contains(x.Id)).Select(s => new { s.ProductCode, s.ProductName, s.ProductRemark, s.RONo, s.SmallUomUnit, s.POSerialNumber, s.ReceiptQuantity, s.PricePerDealUnit, s.Id }).ToList();
var saldoawaldeliveryorderdetailid = SaldoAwals.Select(x => x.DODEtailId).ToList();
var saldoawaldeliveryorderdetails = dbContext.GarmentDeliveryOrderDetails.Where(x => saldoawaldeliveryorderdetailid.Contains(x.Id)).Select(s => new { s.CodeRequirment, s.Id }).ToList();
var saldoawalpurchaseorderIds = SaldoAwals.Select(x => x.IPOId).ToList();
var saldoawalpurchaseorders = dbContext.GarmentInternalPurchaseOrders.Where(x => saldoawalpurchaseorderIds.Contains(x.Id)).Select(s => new { s.BuyerCode, s.Id, s.Article }).ToList();
var saldoawalreceiptCorrectionitemIds = SaldoAwals.Select(x => x.RCorrId).ToList();
var saldoawalreceiptCorrectionitems = dbContext.GarmentReceiptCorrectionItems.Where(x => saldoawalreceiptCorrectionitemIds.Contains(x.Id)).Select(s => new { s.CorrectionQuantity, s.PricePerDealUnit, s.Id }).ToList();
var saldoawalunitexpenditureitemIds = SaldoAwals.Select(x => x.UENItemId).ToList();
var saldoawalunitexpenditureitems = dbContext.GarmentUnitExpenditureNoteItems.Where(x => saldoawalunitexpenditureitemIds.Contains(x.Id)).Select(s => new { s.PricePerDealUnit, s.Quantity, s.Id }).ToList();
var saldoawalunitexpenditureIds = SaldoAwals.Select(x => x.UENId).ToList();
var saldoawalunitexpenditures = dbContext.GarmentUnitExpenditureNotes.Where(x => saldoawalunitexpenditureIds.Contains(x.Id)).Select(s => new { s.UnitRequestCode, s.UnitSenderCode, s.ExpenditureTo, s.Id }).ToList();
//var saldoawalbalancestockepoitemids = SaldoAwals.Select(x => x.BSId).ToList();
var saldoawalexternalpurchaseorderitemIds = SaldoAwals.Select(x => x.EPOItemId).ToList();
var saldoawalexternalpurchaseorderitems = dbContext.GarmentExternalPurchaseOrderItems.Where(x => saldoawalexternalpurchaseorderitemIds.Contains(x.Id)).Select(s => new { s.Id }).ToList();
var saldoawalexternalpurchaseorderIds = SaldoAwals.Select(x => x.EPOId).ToList();
var saldoawalexternalpurchaseorders = dbContext.GarmentExternalPurchaseOrders.Where(x => saldoawalexternalpurchaseorderIds.Contains(x.Id)).Select(s => new { s.PaymentMethod, s.Id }).ToList();
var saldoawalbalancestocks = BalaceStock.Where(x => saldoawalexternalpurchaseorderitemIds.Contains((long)x.EPOItemId)).Select(s => new { s.ArticleNo, s.ClosePrice, s.CloseStock, s.EPOID, s.EPOItemId, s.BalanceStockId }).ToList();
List <GarmentStockReportViewModel> SaldoAwal = new List <GarmentStockReportViewModel>();
foreach (var i in SaldoAwals)
{
var saldoawalunitreceiptnote = saldoawalunitreceiptnotes.AsParallel().FirstOrDefault(x => x.Id == i.URNId);
var saldoawalunitreceiptnoteItem = saldoawalunitreceiptnoteItems.AsParallel().FirstOrDefault(x => x.Id.Equals(i.URNItemsId));
var saldoawaldeliveryorderdetail = saldoawaldeliveryorderdetails.AsParallel().FirstOrDefault(x => x.Id.Equals(i.DODEtailId));
var saldoawalpurchaseorder = saldoawalpurchaseorders.AsParallel().FirstOrDefault(x => x.Id.Equals(i.IPOId));
var saldoawalreceiptCorrectionitem = saldoawalreceiptCorrectionitems.AsParallel().FirstOrDefault(x => x.Id.Equals(i.RCorrId));
var saldoawalunitexpenditureitem = saldoawalunitexpenditureitems.AsParallel().FirstOrDefault(x => x.Id.Equals(i.URNItemsId));
var saldoawalunitexpenditure = saldoawalunitexpenditures.AsParallel().FirstOrDefault(x => x.Id.Equals(i.URNId));
var saldoawalbalancestock = saldoawalbalancestocks.AsParallel().FirstOrDefault(x => x.EPOItemId.Equals(i.EPOItemId));
var saldoawalexternalpurchaseorderitem = saldoawalexternalpurchaseorderitems.AsParallel().FirstOrDefault(x => x.Id.Equals(i.EPOItemId));
var saldoawalexternalpurchaseorder = saldoawalexternalpurchaseorders.AsParallel().FirstOrDefault(x => x.Id.Equals(i.EPOId));
SaldoAwal.Add(new GarmentStockReportViewModel
{
BeginningBalanceQty = saldoawalbalancestock == null ? (saldoawalreceiptCorrectionitem != null ? (decimal)saldoawalreceiptCorrectionitem.CorrectionQuantity : 0) + saldoawalunitreceiptnoteItem.ReceiptQuantity - (saldoawalunitexpenditureitem != null ? (decimal)saldoawalunitexpenditureitem.Quantity : 0) : (decimal)saldoawalbalancestock.CloseStock + (saldoawalreceiptCorrectionitem != null ? (decimal)saldoawalreceiptCorrectionitem.CorrectionQuantity : 0) + saldoawalunitreceiptnoteItem.ReceiptQuantity - (saldoawalunitexpenditureitem != null ? (decimal)saldoawalunitexpenditureitem.Quantity : 0),
//BeginningBalanceQty = saldoawalbalancestock != null ? (decimal)saldoawalbalancestock.CloseStock : 0) + (saldoawalreceiptCorrectionitem != null ? (decimal)saldoawalreceiptCorrectionitem.CorrectionQuantity : 0) + saldoawalunitreceiptnoteItem.ReceiptQuantity - (saldoawalunitexpenditureitem != null ? (decimal)saldoawalunitexpenditureitem.Quantity : 0,
BeginningBalanceUom = saldoawalunitreceiptnoteItem.SmallUomUnit,
Buyer = saldoawalpurchaseorder.BuyerCode,
EndingBalanceQty = 0,
EndingUom = saldoawalunitreceiptnoteItem.SmallUomUnit,
ExpandUom = saldoawalunitreceiptnoteItem.SmallUomUnit,
ExpendQty = saldoawalunitexpenditureitem == null ? 0 : saldoawalunitexpenditureitem.Quantity,
NoArticle = saldoawalpurchaseorder.Article,
PaymentMethod = saldoawalexternalpurchaseorder == null ? "-" : saldoawalexternalpurchaseorder.PaymentMethod,
PlanPo = saldoawalunitreceiptnoteItem == null ? "-" : saldoawalunitreceiptnoteItem.POSerialNumber,
POId = saldoawalpurchaseorder == null ? 0 : saldoawalpurchaseorder.Id,
ProductCode = saldoawalunitreceiptnoteItem.ProductCode,
ProductName = saldoawalunitreceiptnoteItem.ProductName,
ProductRemark = saldoawalunitreceiptnoteItem.ProductRemark,
ReceiptCorrectionQty = saldoawalreceiptCorrectionitem == null ? 0 : (decimal)saldoawalreceiptCorrectionitem.CorrectionQuantity,
ReceiptQty = saldoawalunitreceiptnoteItem.ReceiptQuantity,
ReceiptUom = saldoawalunitreceiptnoteItem.SmallUomUnit,
RO = saldoawalunitreceiptnoteItem.RONo,
UNitCode = saldoawalunitreceiptnote.UnitCode,
UnitSenderCode = saldoawalunitexpenditure == null ? "-" : saldoawalunitexpenditure.UnitSenderCode,
UnitRequestCode = saldoawalunitexpenditure == null ? "-" : saldoawalunitexpenditure.UnitRequestCode
});
}
SaldoAwal = (from query in SaldoAwal
group query by new { query.ProductCode, query.ProductName, query.RO, query.PlanPo, query.POId, query.UNitCode, query.UnitSenderCode } into data
select new GarmentStockReportViewModel
{
BeginningBalanceQty = data.Sum(x => x.BeginningBalanceQty),
BeginningBalanceUom = data.FirstOrDefault().BeginningBalanceUom,
Buyer = data.FirstOrDefault().Buyer,
EndingBalanceQty = data.FirstOrDefault().EndingBalanceQty,
EndingUom = data.FirstOrDefault().EndingUom,
ExpandUom = data.FirstOrDefault().EndingUom,
ExpendQty = 0,
NoArticle = data.FirstOrDefault().NoArticle,
PaymentMethod = data.FirstOrDefault().PaymentMethod,
PlanPo = data.FirstOrDefault().PlanPo,
POId = data.FirstOrDefault().POId,
ProductCode = data.FirstOrDefault().ProductCode,
ProductName = data.FirstOrDefault().ProductName,
ProductRemark = data.FirstOrDefault().ProductRemark,
ReceiptCorrectionQty = 0,
ReceiptQty = 0,
ReceiptUom = data.FirstOrDefault().ReceiptUom,
RO = data.FirstOrDefault().RO,
UNitCode = data.FirstOrDefault().UNitCode,
UnitSenderCode = data.FirstOrDefault().UnitSenderCode,
UnitRequestCode = data.FirstOrDefault().UnitRequestCode
}).ToList();
var stock = (from a in SaldoAkhir
join b in SaldoAwal on a.POId equals b.POId into stockdata
from bb in stockdata.DefaultIfEmpty()
select new GarmentStockReportViewModel
{
BeginningBalanceQty = (bb == null ? 0 : bb.BeginningBalanceQty),
BeginningBalanceUom = a.BeginningBalanceUom,
Buyer = a.Buyer,
EndingBalanceQty = (bb == null ? 0 : bb.BeginningBalanceQty) + a.ReceiptQty + a.ReceiptCorrectionQty - (decimal)a.ExpendQty,
EndingUom = a.EndingUom,
ExpendQty = a.ExpendQty,
NoArticle = a.NoArticle,
PaymentMethod = a.PaymentMethod,
PlanPo = a.PlanPo,
POId = a.POId,
ProductCode = a.ProductCode,
ProductName = a.ProductName,
ProductRemark = a.ProductRemark,
ReceiptCorrectionQty = a.ReceiptCorrectionQty,
ReceiptQty = a.ReceiptQty,
ReceiptUom = a.ReceiptUom,
RO = a.RO,
UNitCode = a.UNitCode,
UnitSenderCode = a.UnitSenderCode,
UnitRequestCode = a.UnitRequestCode
}).ToList();
//var stock = SaldoAwal.Concat(SaldoAkhir).ToList();
//stock = (from query in stock
// group query by new { query.POId, query.ProductCode, query.RO } into data
// select new GarmentStockReportViewModel
// {
// BeginningBalanceQty = data.Sum(x => x.BeginningBalanceQty),
// BeginningBalanceUom = data.FirstOrDefault().BeginningBalanceUom,
// Buyer = data.FirstOrDefault().Buyer,
// EndingBalanceQty = data.Sum(x => x.BeginningBalanceQty) + data.Sum(x => x.ReceiptQty) + data.Sum(x => x.ReceiptCorrectionQty) - data.Sum(x => (decimal)x.ExpendQty),
// EndingUom = data.FirstOrDefault().EndingUom,
// ExpandUom = data.FirstOrDefault().EndingUom,
// ExpendQty = data.Sum(x => x.ExpendQty),
// NoArticle = data.FirstOrDefault().NoArticle,
// PaymentMethod = data.FirstOrDefault().PaymentMethod,
// PlanPo = data.FirstOrDefault().PlanPo,
// POId = data.FirstOrDefault().POId,
// ProductCode = data.FirstOrDefault().ProductCode,
// ProductName = data.FirstOrDefault().ProductName,
// ProductRemark = data.FirstOrDefault().ProductRemark,
// ReceiptCorrectionQty = data.Sum(x => x.ReceiptCorrectionQty),
// ReceiptQty = data.Sum(x => x.ReceiptQty),
// ReceiptUom = data.FirstOrDefault().ReceiptUom,
// RO = data.FirstOrDefault().RO,
// UNitCode = data.FirstOrDefault().UNitCode,
// UnitSenderCode = data.FirstOrDefault().UnitSenderCode,
// UnitRequestCode = data.FirstOrDefault().UnitRequestCode
// }).ToList();
return(stock);
//return SaldoAwal;
}
public UENManutencao(UEN _uenSelecionada)
{
InitializeComponent();
uenSelecionada = _uenSelecionada;
}
public IEnumerable <GarmentStockReportViewModel> GetStockQuery(string ctg, string unitcode, DateTime?datefrom, DateTime?dateto, int offset)
{
DateTime DateFrom = datefrom == null ? new DateTime(1970, 1, 1) : (DateTime)datefrom;
DateTime DateTo = dateto == null ? DateTime.Now : (DateTime)dateto;
var PPAwal = (from a in dbContext.GarmentUnitReceiptNotes
join b in dbContext.GarmentUnitReceiptNoteItems on a.Id equals b.URNId
join d in dbContext.GarmentDeliveryOrderDetails on b.POId equals d.POId
join f in dbContext.GarmentInternalPurchaseOrders on b.POId equals f.Id
join e in dbContext.GarmentReceiptCorrectionItems on b.Id equals e.URNItemId into RC
from ty in RC.DefaultIfEmpty()
join c in dbContext.GarmentUnitExpenditureNoteItems on b.UENItemId equals c.Id into UE
from ww in UE.DefaultIfEmpty()
join r in dbContext.GarmentUnitExpenditureNotes on ww.UENId equals r.Id into UEN
from dd in UEN.DefaultIfEmpty()
join epoItem in dbContext.GarmentExternalPurchaseOrderItems on b.POId equals epoItem.POId into EP
from epoItem in EP.DefaultIfEmpty()
join epo in dbContext.GarmentExternalPurchaseOrders on epoItem.GarmentEPOId equals epo.Id into EPO
from epo in EPO.DefaultIfEmpty()
where d.CodeRequirment == (String.IsNullOrWhiteSpace(ctg) ? d.CodeRequirment : ctg) &&
a.IsDeleted == false && b.IsDeleted == false
//String.IsNullOrEmpty(a.UENNo) ? false : a.UENNo.Contains(unitcode)
//|| a.UnitCode == unitcode
//a.UENNo.Contains(unitcode) || a.UnitCode == unitcode
//a.UnitCode == unitcode || a.UENNo.Contains(unitcode)
//&& a.ReceiptDate.AddHours(offset).Date >= DateFrom.Date
&& a.CreatedUtc.AddHours(offset).Date < DateFrom.Date
select new
{
ReceiptDate = a.ReceiptDate,
CodeRequirment = d.CodeRequirment,
ProductCode = b.ProductCode,
ProductName = b.ProductName,
ProductRemark = b.ProductRemark,
RO = b.RONo,
Uom = b.UomUnit,
Buyer = f.BuyerCode,
PlanPo = b.POSerialNumber,
NoArticle = f.Article,
QtyReceipt = b.ReceiptQuantity,
QtyCorrection = ty.POSerialNumber == null ? 0 : ty.Quantity,
QtyExpend = ww.POSerialNumber == null ? 0 : ww.Quantity,
PriceReceipt = b.PricePerDealUnit,
PriceCorrection = ty.POSerialNumber == null ? 0 : ty.PricePerDealUnit,
PriceExpend = ww.POSerialNumber == null ? 0 : ww.PricePerDealUnit,
POId = b.POId,
URNType = a.URNType,
UnitCode = a.UnitCode,
UENNo = a.UENNo,
UnitSenderCode = dd.UnitSenderCode == null ? "-" : dd.UnitSenderCode,
UnitRequestName = dd.UnitRequestName == null ? "-" : dd.UnitRequestName,
ExpenditureTo = dd.ExpenditureTo == null ? "-" : dd.ExpenditureTo,
PaymentMethod = epo.PaymentMethod == null ? "-": epo.PaymentMethod,
a.IsDeleted
});
var CobaPP = from a in PPAwal
//where a.ReceiptDate.AddHours(offset).Date < DateFrom.Date && a.CodeRequirment == (String.IsNullOrWhiteSpace(ctg) ? a.CodeRequirment : ctg) && a.IsDeleted == false
where a.UENNo.Contains((String.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode)) || a.UnitCode == (String.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode)
select a;
var PPAkhir = from a in dbContext.GarmentUnitReceiptNotes
join b in dbContext.GarmentUnitReceiptNoteItems on a.Id equals b.URNId
join d in dbContext.GarmentDeliveryOrderDetails on b.POId equals d.POId
join f in dbContext.GarmentInternalPurchaseOrders on b.POId equals f.Id
//join f in SaldoAwal on b.POId equals f.POID
join e in dbContext.GarmentReceiptCorrectionItems on b.Id equals e.URNItemId into RC
from ty in RC.DefaultIfEmpty()
join c in dbContext.GarmentUnitExpenditureNoteItems on b.UENItemId equals c.Id into UE
from ww in UE.DefaultIfEmpty()
join r in dbContext.GarmentUnitExpenditureNotes on ww.UENId equals r.Id into UEN
from dd in UEN.DefaultIfEmpty()
join epoItem in dbContext.GarmentExternalPurchaseOrderItems on b.POId equals epoItem.POId into EP
from epoItem in EP.DefaultIfEmpty()
join epo in dbContext.GarmentExternalPurchaseOrders on epoItem.GarmentEPOId equals epo.Id into EPO
from epo in EPO.DefaultIfEmpty()
where d.CodeRequirment == (String.IsNullOrWhiteSpace(ctg) ? d.CodeRequirment : ctg) &&
a.IsDeleted == false && b.IsDeleted == false
//String.IsNullOrEmpty(a.UENNo) ? false : a.UENNo.Contains(unitcode)
//|| a.UnitCode == unitcode
//a.UnitCode == unitcode || a.UENNo.Contains(unitcode)
// a.UENNo.Contains(unitcode) || a.UnitCode == unitcode /*String.IsNullOrEmpty(a.UENNo) ? true :*/
&& a.CreatedUtc.AddHours(offset).Date >= DateFrom.Date &&
a.CreatedUtc.AddHours(offset).Date <= DateTo.Date
select new
{
ReceiptDate = a.ReceiptDate,
CodeRequirment = d.CodeRequirment,
ProductCode = b.ProductCode,
ProductName = b.ProductName,
ProductRemark = b.ProductRemark,
RO = b.RONo,
Uom = b.UomUnit,
Buyer = f.BuyerCode,
PlanPo = b.POSerialNumber,
NoArticle = f.Article,
QtyReceipt = b.ReceiptQuantity,
QtyCorrection = ty.POSerialNumber == null ? 0 : ty.Quantity,
QtyExpend = ww.POSerialNumber == null ? 0 : ww.Quantity,
PriceReceipt = b.PricePerDealUnit,
PriceCorrection = ty.POSerialNumber == null ? 0 : ty.PricePerDealUnit,
PriceExpend = ww.POSerialNumber == null ? 0 : ww.PricePerDealUnit,
POId = b.POId,
URNType = a.URNType,
UnitCode = a.UnitCode,
UENNo = a.UENNo,
UnitSenderCode = dd.UnitSenderCode == null ? "-" : dd.UnitSenderCode,
UnitRequestName = dd.UnitRequestName == null ? "-" : dd.UnitRequestName,
ExpenditureTo = dd.ExpenditureTo == null ? "-" : dd.ExpenditureTo,
PaymentMethod = epo.PaymentMethod == null ? "-" : epo.PaymentMethod,
a.IsDeleted
};
var CobaPPAkhir = from a in PPAkhir
where a.UENNo.Contains((String.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode)) || a.UnitCode == (String.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode)
//where a.ReceiptDate.AddHours(offset).Date >= DateFrom.Date
// && a.ReceiptDate.AddHours(offset).Date <= DateTo.Date
// && a.CodeRequirment == (String.IsNullOrWhiteSpace(ctg) ? a.CodeRequirment : ctg)
// && a.IsDeleted == false
select a;
var SaldoAwal = from query in CobaPP
group query by new { query.ProductCode, query.ProductName, query.RO, query.PlanPo, query.POId, query.UnitCode, query.UnitSenderCode, query.UnitRequestName } into data
select new GarmentStockReportViewModel
{
ProductCode = data.Key.ProductCode,
RO = data.Key.RO,
PlanPo = data.FirstOrDefault().PlanPo,
NoArticle = data.FirstOrDefault().NoArticle,
ProductName = data.FirstOrDefault().ProductName,
ProductRemark = data.FirstOrDefault().ProductRemark,
Buyer = data.FirstOrDefault().Buyer,
BeginningBalanceQty = data.Sum(x => x.QtyReceipt) + Convert.ToDecimal(data.Sum(x => x.QtyCorrection)) - Convert.ToDecimal(data.Sum(x => x.QtyExpend)),
BeginningBalanceUom = data.FirstOrDefault().Uom,
ReceiptCorrectionQty = 0,
ReceiptQty = 0,
ReceiptUom = data.FirstOrDefault().Uom,
ExpendQty = 0,
ExpandUom = data.FirstOrDefault().Uom,
EndingBalanceQty = 0,
EndingUom = data.FirstOrDefault().Uom,
POId = data.FirstOrDefault().POId,
PaymentMethod = data.FirstOrDefault().PaymentMethod
};
var SaldoAkhir = from query in CobaPPAkhir
group query by new { query.ProductCode, query.ProductName, query.RO, query.PlanPo, query.POId, query.UnitCode, query.UnitSenderCode, query.UnitRequestName } into data
select new GarmentStockReportViewModel
{
ProductCode = data.Key.ProductCode,
RO = data.Key.RO,
PlanPo = data.FirstOrDefault().PlanPo,
NoArticle = data.FirstOrDefault().NoArticle,
ProductName = data.FirstOrDefault().ProductName,
ProductRemark = data.FirstOrDefault().ProductRemark,
Buyer = data.FirstOrDefault().Buyer,
BeginningBalanceQty = 0,
BeginningBalanceUom = data.FirstOrDefault().Uom,
ReceiptCorrectionQty = 0,
ReceiptQty = data.Sum(x => x.QtyReceipt),
ReceiptUom = data.FirstOrDefault().Uom,
ExpendQty = data.Sum(x => x.QtyExpend),
ExpandUom = data.FirstOrDefault().Uom,
EndingBalanceQty = Convert.ToDecimal(Convert.ToDouble(data.Sum(x => x.QtyReceipt)) - data.Sum(x => x.QtyExpend)),
EndingUom = data.FirstOrDefault().Uom,
POId = data.FirstOrDefault().POId,
PaymentMethod = data.FirstOrDefault().PaymentMethod
};
List <GarmentStockReportViewModel> Data1 = SaldoAwal.Concat(SaldoAkhir).ToList();
var Data = (from query in Data1
group query by new { query.POId, query.ProductCode, query.RO } into groupdata
select new GarmentStockReportViewModel
{
ProductCode = groupdata.FirstOrDefault().ProductCode == null ? "-" : groupdata.FirstOrDefault().ProductCode,
RO = groupdata.FirstOrDefault().RO == null ? "-" : groupdata.FirstOrDefault().RO,
PlanPo = groupdata.FirstOrDefault().PlanPo == null ? "-" : groupdata.FirstOrDefault().PlanPo,
NoArticle = groupdata.FirstOrDefault().NoArticle == null ? "-" : groupdata.FirstOrDefault().NoArticle,
ProductName = groupdata.FirstOrDefault().ProductName == null ? "-" : groupdata.FirstOrDefault().ProductName,
ProductRemark = groupdata.FirstOrDefault().ProductRemark,
Buyer = groupdata.FirstOrDefault().Buyer == null ? "-" : groupdata.FirstOrDefault().Buyer,
BeginningBalanceQty = groupdata.Sum(x => x.BeginningBalanceQty),
BeginningBalanceUom = groupdata.FirstOrDefault().BeginningBalanceUom,
ReceiptCorrectionQty = 0,
ReceiptQty = groupdata.Sum(x => x.ReceiptQty),
ReceiptUom = groupdata.FirstOrDefault().ReceiptUom,
ExpendQty = groupdata.Sum(x => x.ExpendQty),
ExpandUom = groupdata.FirstOrDefault().ExpandUom,
EndingBalanceQty = Convert.ToDecimal((groupdata.Sum(x => x.BeginningBalanceQty) + groupdata.Sum(x => x.ReceiptQty) + 0) - (Convert.ToDecimal(groupdata.Sum(x => x.ExpendQty)))),
EndingUom = groupdata.FirstOrDefault().EndingUom,
PaymentMethod = groupdata.FirstOrDefault().PaymentMethod
});
return(Data.AsQueryable());
}
private ObraEtapa PreencherCamposOrigatoriosObraEtapa()
{
UEN UEN = new UEN();
CentroCusto centroCusto = new CentroCusto();
Despesa despesa = new Despesa();
Fase fase = new Fase();
ObraEtapa obraEtapa = new ObraEtapa();
int idUsuarioTeste = 0;
obraEtapa.idEmpresa = new BIZEmpresa().PesquisarEmpresa(new Empresa())[0].idEmpresa;
obraEtapa.numeroLicitacao = "999999999";
obraEtapa.idCliente = new BIZCliente().PesquisarCliente(new Cliente())[0].idCliente;
obraEtapa.nomeCliente = "Cliente Teste";
obraEtapa.Descricao = "Etapa Teste";
obraEtapa.nomeEvento = "Evento Teste";
obraEtapa.valorContrato = Decimal.Parse("999.99");
obraEtapa.dataInicio = DateTime.Now;
obraEtapa.dataTermino = DateTime.Now;
UEN = new BIZUEN().PesquisarUEN(new UEN())[0];
centroCusto = new BIZCentroCusto().PesquisarCentroCusto(new CentroCusto())[0];
despesa = new BIZDespesa().PesquisarDespesa(new Despesa())[0];
fase = new BIZFase().PesquisarFases(new Fase())[0];
idUsuarioTeste = new BIZUsuario().PesquisarUsuario(new Usuario())[0].idUsuario;
obraEtapa.lstGastosPrevistos = new List <ObraEtapaGastoPrevisto>();
obraEtapa.lstGastosPrevistos.Add(new ObraEtapaGastoPrevisto()
{
idUEN = UEN.idUEN,
idCentroCusto = centroCusto.idCentroCusto,
idDespesa = despesa.idDespesa,
Valor = Decimal.Parse("999.99"),
Observacao = "Teste de lançamento previsto",
UnitTest = 1
});
obraEtapa.lstGastosRealizados = new List <ObraEtapaGastoRealizado>();
obraEtapa.lstGastosRealizados.Add(new ObraEtapaGastoRealizado()
{
idUEN = UEN.idUEN,
idCentroCusto = centroCusto.idCentroCusto,
idDespesa = despesa.idDespesa,
Data = DateTime.Now,
Valor = Decimal.Parse("999.99"),
Observacao = "Teste de lançamento realizado",
UnitTest = 1
});
obraEtapa.lstFases = new List <ObraEtapaFase>();
obraEtapa.lstFases.Add(new ObraEtapaFase()
{
idFase = fase.idFase,
dataInicio = DateTime.Today,
dataPrevTermino = DateTime.Today,
dataTermino = DateTime.Today,
UnitTest = 1
});
obraEtapa.lstFollowUps = new List <ObraEtapaFollowUp>();
obraEtapa.lstFollowUps.Add(new ObraEtapaFollowUp()
{
idUsuario = idUsuarioTeste,
Descricao = "TesteFU",
Data = DateTime.Today,
UnitTest = 1
});
//obraEtapa.lstPlanejamentos = new List<ObraEtapaPlanejamento>();
//obraEtapa.lstPlanejamentos.Add(new ObraEtapaPlanejamento()
//{
// idObraEtapa = obraEtapa.idObraEtapa,
// idUEN = UEN.idUEN,
// valorPrevisto = 99,
// UnitTest = 1
//});
//obraEtapa.lstPlanejamentos.Add(new ObraEtapaPlanejamento()
//{
// idObraEtapa = obraEtapa.idObraEtapa,
// idUEN = UEN.idUEN,
// valorPrevisto = 88,
// UnitTest = 1
//});
obraEtapa.UnitTest = 1;
return(obraEtapa);
}
public IEnumerable <AccountingStockReportViewModel> GetStockQuery(string ctg, string unitcode, DateTime?datefrom, DateTime?dateto, int offset)
{
DateTime DateFrom = datefrom == null ? new DateTime(1970, 1, 1) : (DateTime)datefrom;
DateTime DateTo = dateto == null ? DateTime.Now : (DateTime)dateto;
var PPAwal = (from a in dbContext.GarmentUnitReceiptNotes
join b in dbContext.GarmentUnitReceiptNoteItems on a.Id equals b.URNId
join d in dbContext.GarmentDeliveryOrderDetails on b.POId equals d.POId
join f in dbContext.GarmentInternalPurchaseOrders on b.POId equals f.Id
join e in dbContext.GarmentReceiptCorrectionItems on b.Id equals e.URNItemId into RC
from ty in RC.DefaultIfEmpty()
join c in dbContext.GarmentUnitExpenditureNoteItems on b.UENItemId equals c.Id into UE
from ww in UE.DefaultIfEmpty()
join r in dbContext.GarmentUnitExpenditureNotes on ww.UENId equals r.Id into UEN
from dd in UEN.DefaultIfEmpty()
where d.CodeRequirment == (String.IsNullOrWhiteSpace(ctg) ? d.CodeRequirment : ctg) &&
a.IsDeleted == false && b.IsDeleted == false
//String.IsNullOrEmpty(a.UENNo) ? false : a.UENNo.Contains(unitcode)
//|| a.UnitCode == unitcode
//a.UENNo.Contains(unitcode) || a.UnitCode == unitcode
//a.UnitCode == unitcode || a.UENNo.Contains(unitcode)
//&& a.ReceiptDate.AddHours(offset).Date >= DateFrom.Date
&& a.CreatedUtc.AddHours(offset).Date < DateFrom.Date
select new
{
ReceiptDate = a.ReceiptDate,
CodeRequirment = d.CodeRequirment,
ProductCode = b.ProductCode,
ProductName = b.ProductName,
RO = b.RONo,
Uom = b.UomUnit,
Buyer = f.BuyerCode,
PlanPo = b.POSerialNumber,
NoArticle = f.Article,
QtyReceipt = b.ReceiptQuantity,
QtyCorrection = ty.POSerialNumber == null ? 0 : ty.Quantity,
QtyExpend = ww.POSerialNumber == null ? 0 : ww.Quantity,
PriceReceipt = b.PricePerDealUnit,
PriceCorrection = ty.POSerialNumber == null ? 0 : ty.PricePerDealUnit,
PriceExpend = ww.POSerialNumber == null ? 0 : ww.PricePerDealUnit,
POId = b.POId,
URNType = a.URNType,
UnitCode = a.UnitCode,
UENNo = a.UENNo,
UnitSenderCode = dd.UnitSenderCode == null ? "-" : dd.UnitSenderCode,
UnitRequestName = dd.UnitRequestName == null ? "-" : dd.UnitRequestName,
ExpenditureTo = dd.ExpenditureTo == null ? "-" : dd.ExpenditureTo,
a.IsDeleted
});
var CobaPP = from a in PPAwal
//where a.ReceiptDate.AddHours(offset).Date < DateFrom.Date && a.CodeRequirment == (String.IsNullOrWhiteSpace(ctg) ? a.CodeRequirment : ctg) && a.IsDeleted == false
where a.UENNo.Contains((String.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode)) || a.UnitCode == (String.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode)
select a;
var PPAkhir = from a in dbContext.GarmentUnitReceiptNotes
join b in dbContext.GarmentUnitReceiptNoteItems on a.Id equals b.URNId
join d in dbContext.GarmentDeliveryOrderDetails on b.POId equals d.POId
join f in dbContext.GarmentInternalPurchaseOrders on b.POId equals f.Id
//join f in SaldoAwal on b.POId equals f.POID
join e in dbContext.GarmentReceiptCorrectionItems on b.Id equals e.URNItemId into RC
from ty in RC.DefaultIfEmpty()
join c in dbContext.GarmentUnitExpenditureNoteItems on b.UENItemId equals c.Id into UE
from ww in UE.DefaultIfEmpty()
join r in dbContext.GarmentUnitExpenditureNotes on ww.UENId equals r.Id into UEN
from dd in UEN.DefaultIfEmpty()
where d.CodeRequirment == (String.IsNullOrWhiteSpace(ctg) ? d.CodeRequirment : ctg) &&
a.IsDeleted == false && b.IsDeleted == false
//String.IsNullOrEmpty(a.UENNo) ? false : a.UENNo.Contains(unitcode)
//|| a.UnitCode == unitcode
//a.UnitCode == unitcode || a.UENNo.Contains(unitcode)
// a.UENNo.Contains(unitcode) || a.UnitCode == unitcode /*String.IsNullOrEmpty(a.UENNo) ? true :*/
&& a.CreatedUtc.AddHours(offset).Date >= DateFrom.Date &&
a.CreatedUtc.AddHours(offset).Date <= DateTo.Date
select new
{
ReceiptDate = a.ReceiptDate,
CodeRequirment = d.CodeRequirment,
ProductCode = b.ProductCode,
ProductName = b.ProductName,
RO = b.RONo,
Uom = b.UomUnit,
Buyer = f.BuyerCode,
PlanPo = b.POSerialNumber,
NoArticle = f.Article,
QtyReceipt = b.ReceiptQuantity,
QtyCorrection = ty.POSerialNumber == null ? 0 : ty.Quantity,
QtyExpend = ww.POSerialNumber == null ? 0 : ww.Quantity,
PriceReceipt = b.PricePerDealUnit,
PriceCorrection = ty.POSerialNumber == null ? 0 : ty.PricePerDealUnit,
PriceExpend = ww.POSerialNumber == null ? 0 : ww.PricePerDealUnit,
POId = b.POId,
URNType = a.URNType,
UnitCode = a.UnitCode,
UENNo = a.UENNo,
UnitSenderCode = dd.UnitSenderCode == null ? "-" : dd.UnitSenderCode,
UnitRequestName = dd.UnitRequestName == null ? "-" : dd.UnitRequestName,
ExpenditureTo = dd.ExpenditureTo == null ? "-" : dd.ExpenditureTo,
a.IsDeleted
};
var CobaPPAkhir = from a in PPAkhir
where a.UENNo.Contains((String.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode)) || a.UnitCode == (String.IsNullOrWhiteSpace(unitcode) ? a.UnitCode : unitcode)
//where a.ReceiptDate.AddHours(offset).Date >= DateFrom.Date
// && a.ReceiptDate.AddHours(offset).Date <= DateTo.Date
// && a.CodeRequirment == (String.IsNullOrWhiteSpace(ctg) ? a.CodeRequirment : ctg)
// && a.IsDeleted == false
select a;
var SaldoAwal = from query in CobaPP
group query by new { query.ProductCode, query.ProductName, query.RO, query.PlanPo, query.POId, query.UnitCode, query.UnitSenderCode, query.UnitRequestName } into data
select new AccountingStockReportViewModel
{
ProductCode = data.Key.ProductCode,
ProductName = data.FirstOrDefault().ProductName,
RO = data.Key.RO,
Buyer = data.FirstOrDefault().Buyer,
PlanPo = data.FirstOrDefault().PlanPo,
NoArticle = data.FirstOrDefault().NoArticle,
BeginningBalanceQty = data.Sum(x => x.QtyReceipt) + Convert.ToDecimal(data.Sum(x => x.QtyCorrection)) - Convert.ToDecimal(data.Sum(x => x.QtyExpend)),
BeginningBalanceUom = data.FirstOrDefault().Uom,
BeginningBalancePrice = data.Sum(x => x.PriceReceipt) + Convert.ToDecimal(data.Sum(x => x.PriceCorrection)) - Convert.ToDecimal(data.Sum(x => x.PriceExpend)),
ReceiptCorrectionQty = 0,
ReceiptPurchaseQty = 0,
ReceiptProcessQty = 0,
ReceiptKon2AQty = 0,
ReceiptKon2BQty = 0,
ReceiptKon2CQty = 0,
ReceiptKon1MNSQty = 0,
ReceiptKon2DQty = 0,
ReceiptCorrectionPrice = 0,
ReceiptPurchasePrice = 0,
ReceiptProcessPrice = 0,
ReceiptKon2APrice = 0,
ReceiptKon2BPrice = 0,
ReceiptKon2CPrice = 0,
ReceiptKon1MNSPrice = 0,
ReceiptKon2DPrice = 0,
ExpendReturQty = 0,
ExpendRestQty = 0,
ExpendProcessQty = 0,
ExpendSampleQty = 0,
ExpendKon2AQty = 0,
ExpendKon2BQty = 0,
ExpendKon2CQty = 0,
ExpendKon1MNSQty = 0,
ExpendKon2DQty = 0,
ExpendReturPrice = 0,
ExpendRestPrice = 0,
ExpendProcessPrice = 0,
ExpendSamplePrice = 0,
ExpendKon2APrice = 0,
ExpendKon2BPrice = 0,
ExpendKon2CPrice = 0,
ExpendKon1MNSPrice = 0,
ExpendKon2DPrice = 0,
EndingBalanceQty = 0,
EndingBalancePrice = 0,
POId = data.FirstOrDefault().POId
};
var SaldoAkhir = from query in CobaPPAkhir
group query by new { query.ProductCode, query.ProductName, query.RO, query.PlanPo, query.POId, query.UnitCode, query.UnitSenderCode, query.UnitRequestName } into data
select new AccountingStockReportViewModel
{
ProductCode = data.Key.ProductCode,
ProductName = data.Key.ProductName,
RO = data.Key.RO,
Buyer = data.FirstOrDefault().Buyer,
PlanPo = data.FirstOrDefault().PlanPo,
NoArticle = data.FirstOrDefault().NoArticle,
BeginningBalanceQty = /*data.Sum(x => x.QtyReceipt) + Convert.ToDecimal(data.Sum(x => x.QtyCorrection)) - Convert.ToDecimal(data.Sum(x => x.QtyExpend))*/ 0,
BeginningBalanceUom = data.FirstOrDefault().Uom,
BeginningBalancePrice = /*data.Sum(x => x.PriceReceipt) + Convert.ToDecimal(data.Sum(x => x.PriceCorrection)) - Convert.ToDecimal(data.Sum(x => x.PriceExpend))*/ 0,
ReceiptCorrectionQty = 0,
ReceiptPurchaseQty = data.FirstOrDefault().URNType == "PEMBELIAN" && data.FirstOrDefault().UnitCode == unitcode?data.Sum(x => x.QtyReceipt) : 0,
ReceiptProcessQty = data.FirstOrDefault().URNType == "PROSES" && data.FirstOrDefault().UnitCode == unitcode?data.Sum(x => x.QtyReceipt) : 0,
ReceiptKon2AQty = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C2A" ? data.Sum(x => x.QtyReceipt) : 0,
ReceiptKon2BQty = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C2B" ? data.Sum(x => x.QtyReceipt) : 0,
ReceiptKon2CQty = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C2C" ? data.Sum(x => x.QtyReceipt) : 0,
ReceiptKon1MNSQty = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C1A" ? data.Sum(x => x.QtyReceipt) : 0,
ReceiptKon2DQty = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C1B" ? data.Sum(x => x.QtyReceipt) : 0,
ReceiptCorrectionPrice = 0,
ReceiptPurchasePrice = data.FirstOrDefault().URNType == "PEMBELIAN" && data.FirstOrDefault().UnitCode == unitcode?data.Sum(x => x.PriceReceipt) : 0,
ReceiptProcessPrice = data.FirstOrDefault().URNType == "PROSES" && data.FirstOrDefault().UnitCode == unitcode?data.Sum(x => x.PriceReceipt) : 0,
ReceiptKon2APrice = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C2A" ? data.Sum(x => x.PriceReceipt) : 0,
ReceiptKon2BPrice = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C2B" ? data.Sum(x => x.PriceReceipt) : 0,
ReceiptKon2CPrice = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C2C" ? data.Sum(x => x.PriceReceipt) : 0,
ReceiptKon1MNSPrice = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C1A" ? data.Sum(x => x.PriceReceipt) : 0,
ReceiptKon2DPrice = data.FirstOrDefault().URNType == "GUDANG LAIN" && data.FirstOrDefault().UENNo.Substring(3, 3) == "C1B" ? data.Sum(x => x.PriceReceipt) : 0,
ExpendReturQty = data.FirstOrDefault().ExpenditureTo == "EXTERNAL" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.QtyExpend) : 0,
ExpendRestQty = 0,
ExpendProcessQty = data.FirstOrDefault().ExpenditureTo == "PROSES" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.QtyExpend) : 0,
ExpendSampleQty = data.FirstOrDefault().ExpenditureTo == "SAMPLE" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.QtyExpend) : 0,
ExpendKon2AQty = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 2A" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.QtyExpend) : 0,
ExpendKon2BQty = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 2B" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.QtyExpend) : 0,
ExpendKon2CQty = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 2C" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.QtyExpend) : 0,
ExpendKon1MNSQty = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 1A" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.QtyExpend) : 0,
ExpendKon2DQty = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 1B" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.QtyExpend) : 0,
ExpendReturPrice = data.FirstOrDefault().ExpenditureTo == "EXTERNAL" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.PriceExpend) : 0,
ExpendRestPrice = 0,
ExpendProcessPrice = data.FirstOrDefault().ExpenditureTo == "PROSES" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.PriceExpend) : 0,
ExpendSamplePrice = data.FirstOrDefault().ExpenditureTo == "SAMPLE" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.PriceExpend) : 0,
ExpendKon2APrice = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 2A" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.PriceExpend) : 0,
ExpendKon2BPrice = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 2B" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.PriceExpend) : 0,
ExpendKon2CPrice = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 2C" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.PriceExpend) : 0,
ExpendKon1MNSPrice = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 1A" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.PriceExpend) : 0,
ExpendKon2DPrice = data.FirstOrDefault().ExpenditureTo == "GUDANG LAIN" && data.FirstOrDefault().UnitRequestName == "CENTRAL 1B" && data.FirstOrDefault().UnitSenderCode == unitcode?data.Sum(x => x.PriceExpend) : 0,
EndingBalanceQty = Convert.ToDecimal(Convert.ToDouble(data.Sum(x => x.QtyReceipt)) - data.Sum(x => x.QtyExpend)),
EndingBalancePrice = Convert.ToDecimal(Convert.ToDouble(data.Sum(x => x.PriceReceipt)) - data.Sum(x => x.PriceExpend)),
POId = data.FirstOrDefault().POId
};
List <AccountingStockReportViewModel> Data1 = SaldoAwal.Concat(SaldoAkhir).ToList();
var Data = (from query in Data1
group query by new { query.POId, query.ProductCode, query.RO } into groupdata
select new AccountingStockReportViewModel
{
ProductCode = groupdata.FirstOrDefault().ProductCode == null ? "-" : groupdata.FirstOrDefault().ProductCode,
ProductName = groupdata.FirstOrDefault().ProductName == null ? "-" : groupdata.FirstOrDefault().ProductName,
RO = groupdata.FirstOrDefault().RO == null ? "-" : groupdata.FirstOrDefault().RO,
Buyer = groupdata.FirstOrDefault().Buyer == null ? "-" : groupdata.FirstOrDefault().Buyer,
PlanPo = groupdata.FirstOrDefault().PlanPo == null ? "-" : groupdata.FirstOrDefault().PlanPo,
NoArticle = groupdata.FirstOrDefault().NoArticle == null ? "-" : groupdata.FirstOrDefault().NoArticle,
BeginningBalanceQty = groupdata.Sum(x => x.BeginningBalanceQty),
BeginningBalanceUom = groupdata.FirstOrDefault().BeginningBalanceUom,
BeginningBalancePrice = groupdata.Sum(x => x.BeginningBalancePrice),
ReceiptCorrectionQty = 0,
ReceiptPurchaseQty = groupdata.Sum(x => x.ReceiptPurchaseQty),
ReceiptProcessQty = groupdata.Sum(x => x.ReceiptProcessQty),
ReceiptKon2AQty = groupdata.Sum(x => x.ReceiptKon2AQty),
ReceiptKon2BQty = groupdata.Sum(x => x.ReceiptKon2BQty),
ReceiptKon2CQty = groupdata.Sum(x => x.ReceiptKon2CQty),
ReceiptKon1MNSQty = groupdata.Sum(x => x.ReceiptKon1MNSQty),
ReceiptKon2DQty = groupdata.Sum(x => x.ReceiptKon2DQty),
ReceiptCorrectionPrice = 0,
ReceiptPurchasePrice = groupdata.Sum(x => x.ReceiptPurchasePrice),
ReceiptProcessPrice = groupdata.Sum(x => x.ReceiptProcessPrice),
ReceiptKon2APrice = groupdata.Sum(x => x.ReceiptKon2APrice),
ReceiptKon2BPrice = groupdata.Sum(x => x.ReceiptKon2BPrice),
ReceiptKon2CPrice = groupdata.Sum(x => x.ReceiptKon2CPrice),
ReceiptKon1MNSPrice = groupdata.Sum(x => x.ReceiptKon1MNSPrice),
ReceiptKon2DPrice = groupdata.Sum(x => x.ReceiptKon2DPrice),
ExpendReturQty = groupdata.Sum(x => x.ExpendReturQty),
ExpendRestQty = 0,
ExpendProcessQty = groupdata.Sum(x => x.ExpendProcessQty),
ExpendSampleQty = groupdata.Sum(x => x.ExpendSampleQty),
ExpendKon2AQty = groupdata.Sum(x => x.ExpendKon2AQty),
ExpendKon2BQty = groupdata.Sum(x => x.ExpendKon2BQty),
ExpendKon2CQty = groupdata.Sum(x => x.ExpendKon2CQty),
ExpendKon1MNSQty = groupdata.Sum(x => x.ExpendKon1MNSQty),
ExpendKon2DQty = groupdata.Sum(x => x.ExpendKon2DQty),
ExpendReturPrice = groupdata.Sum(x => x.ExpendReturPrice),
ExpendRestPrice = 0,
ExpendProcessPrice = groupdata.Sum(x => x.ExpendProcessPrice),
ExpendSamplePrice = groupdata.Sum(x => x.ExpendSamplePrice),
ExpendKon2APrice = groupdata.Sum(x => x.ExpendKon2APrice),
ExpendKon2BPrice = groupdata.Sum(x => x.ExpendKon2BPrice),
ExpendKon2CPrice = groupdata.Sum(x => x.ExpendKon2CPrice),
ExpendKon1MNSPrice = groupdata.Sum(x => x.ExpendKon1MNSPrice),
ExpendKon2DPrice = groupdata.Sum(x => x.ExpendKon2DPrice),
EndingBalanceQty = Convert.ToDecimal((groupdata.Sum(x => x.BeginningBalanceQty) + groupdata.Sum(x => x.ReceiptPurchaseQty) + groupdata.Sum(x => x.ReceiptProcessQty) + groupdata.Sum(x => x.ReceiptKon2AQty) + groupdata.Sum(x => x.ReceiptKon2BQty) + groupdata.Sum(x => x.ReceiptKon2CQty) + groupdata.Sum(x => x.ReceiptKon1MNSQty) + groupdata.Sum(x => x.ReceiptKon2DQty) + 0) - (Convert.ToDecimal(groupdata.Sum(x => x.ExpendProcessQty)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendSampleQty)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon2AQty)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon2BQty)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon2CQty)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon1MNSQty)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon2DQty)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendReturPrice)))),
EndingBalancePrice = Convert.ToDecimal((groupdata.Sum(x => x.BeginningBalancePrice) + groupdata.Sum(x => x.ReceiptPurchasePrice) + groupdata.Sum(x => x.ReceiptProcessPrice) + groupdata.Sum(x => x.ReceiptKon2APrice) + groupdata.Sum(x => x.ReceiptKon2BPrice) + groupdata.Sum(x => x.ReceiptKon2CPrice) + groupdata.Sum(x => x.ReceiptKon1MNSPrice) + groupdata.Sum(x => x.ReceiptKon2DPrice) + 0) - (Convert.ToDecimal(groupdata.Sum(x => x.ExpendReturPrice)) + 0 + Convert.ToDecimal(groupdata.Sum(x => x.ExpendProcessPrice)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendSamplePrice)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon2APrice)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon2BPrice)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon2CPrice)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon1MNSPrice)) + Convert.ToDecimal(groupdata.Sum(x => x.ExpendKon2DPrice))))
});
return(Data.AsQueryable());
}