public double sumAcc(string CorrespondFinancialAccountDim_code, int FinancialGeneralLedgerByYear_FactId, int MonthDimId, bool CreditOrDebit)
{
double sum = 0;
CorrespondFinancialAccountDim CFA = BO.get_CorrespondFinancialAccountDim(session, CorrespondFinancialAccountDim_code, Utility.Constant.ROWSTATUS_ACTIVE);
if (CFA != null)
{
XPCollection <FinancialGeneralLedgerByMonth> FGLM = BO.get_xp_FinancialGeneralLedgerByMonth_1(session, CFA.CorrespondFinancialAccountDimId, FinancialGeneralLedgerByYear_FactId, MonthDimId, Utility.Constant.ROWSTATUS_ACTIVE);
if (FGLM != null)
{
foreach (FinancialGeneralLedgerByMonth fglm in FGLM)
{
if (fglm.CreditSum > 0 && CreditOrDebit == true)
{
sum += fglm.CreditSum;
}
if (fglm.DebitSum > 0 && CreditOrDebit == false)
{
sum += fglm.DebitSum;
}
}
}
}
return(sum);
}
public void FixInvokedBussinessObjects(Session session, XPCollection <BusinessObject> invokedBussinessObjects)
{
if (invokedBussinessObjects == null || invokedBussinessObjects.Count == 0)
{
return;
}
CriteriaOperator criteria_0 = CriteriaOperator.Parse("not(IsNull(FinancialTransactionDimId))");
CriteriaOperator criteria_1 = new InOperator("FinancialTransactionDimId.RefId", invokedBussinessObjects.Select(i => i.RefId));
CriteriaOperator criteria_2 = new BinaryOperator("RowStatus", 0, BinaryOperatorType.Greater);
CriteriaOperator criteria = new GroupOperator(GroupOperatorType.And, criteria_0, criteria_1, criteria_2);
CorrespondFinancialAccountDim defaultCorrespondAccDim = CorrespondFinancialAccountDim.GetDefault(session, CorrespondFinancialAccountDimEnum.NAAN_DEFAULT);
XPCollection <FinancialActualPriceDetail> neededToBeFixList = new XPCollection <FinancialActualPriceDetail>(session, criteria);
FinancialActualPriceSummary_Fact fact = null;
if (neededToBeFixList != null && neededToBeFixList.Count > 0)
{
foreach (FinancialActualPriceDetail detail in neededToBeFixList)
{
fact = detail.FinancialActualPriceSummary_FactId;
detail.RowStatus = Utility.Constant.ROWSTATUS_DELETED;
detail.Save();
fact.Save();
}
}
}
private double SumAcc(string CorrespondFinancialAccountDimId_Code, Guid FinancialCustomerLiabilitySummary_FactId, bool Credit_Debit)
{
double sum_column = 0;
CorrespondFinancialAccountDim corr = BO.get_CorrespondFinancialAccountDimId(session, CorrespondFinancialAccountDimId_Code, Utility.Constant.ROWSTATUS_ACTIVE);
if (corr != null)
{
XPCollection <FinancialCustomerLiabilityDetail> fcld_xp1 = BO.get_xp_FinancialCustomerLiabiltiyDetailId_8(session, FinancialCustomerLiabilitySummary_FactId, corr.CorrespondFinancialAccountDimId, Utility.Constant.ROWSTATUS_ACTIVE);
if (fcld_xp1 != null)
{
foreach (FinancialCustomerLiabilityDetail FCLD1 in fcld_xp1)
{
if (Credit_Debit == true)
{
sum_column += (double)FCLD1.Credit;
}
else
{
sum_column += (double)FCLD1.Debit;
}
}
}
}
return(sum_column);
}
private double SumAcc(Session session, string CorrespondFinancialAccountDim_Code, Guid OnTheWayBuyingGoodArtifactId, bool Credit_Debit)
{
double sum = 0;
CorrespondFinancialAccountDim corr = BO.get_CorrespondFinancialAccountDim1(session, CorrespondFinancialAccountDim_Code, Utility.Constant.ROWSTATUS_ACTIVE);
if (corr != null)
{
XPCollection <FinancialOnTheWayBuyingGoodDetail> FOTWB = BO.get_xp_FinancialOnTheWayBuyingGoodDetail1(session, corr.CorrespondFinancialAccountDimId, OnTheWayBuyingGoodArtifactId, Utility.Constant.ROWSTATUS_ACTIVE);
if (FOTWB != null)
{
foreach (FinancialOnTheWayBuyingGoodDetail fotwb in FOTWB)
{
if (Credit_Debit == true && fotwb.Credit > 0)
{
sum += fotwb.Credit;
}
else if (Credit_Debit == false && fotwb.Debit > 0)
{
sum += fotwb.Debit;
}
}
}
}
return(sum);
}
public DataTable DT_getvalueDebit(int FinancialAccountDimId, int YearDimId)
{
try
{
DataTable dt = DT_header();
DataTable dt_xp_all = DT_xp_ALL(FinancialAccountDimId, YearDimId, true, true, true, false);
if (dt_xp_all == null || dt == null)
{
return(null);
}
foreach (DataColumn dc_all in dt_xp_all.Columns)
{
DataRow dr = dt.NewRow();
CorrespondFinancialAccountDim Corr = BO.get_CorrespondFinancialAccountDim_1(session, int.Parse(dc_all.ColumnName), Utility.Constant.ROWSTATUS_ACTIVE);
FinancialGeneralLedgerByYear_Fact FGLY = BO.get_FinancialGeneralLedgerByYear_Fact_1(session, FinancialAccountDimId, YearDimId, Utility.Constant.ROWSTATUS_ACTIVE);
#region
if (Corr != null)
{
foreach (DataColumn dc in dt.Columns)
{
if (dc.ColumnName.Equals("TK"))
{
dr[dc.ColumnName] = Corr.Code;
}
if (!dc.ColumnName.Equals("TK"))
{
for (int i = 1; i <= 12; i++)
{
if (FGLY != null && dc.ColumnName.Equals(i.ToString()))
{
FinancialGeneralLedgerByMonth FGLM = BO.get_FinancialGeneralLedgerByMonth_Debit(session, FGLY.FinancialGeneralLedgerByYear_FactId, int.Parse(dc_all.ColumnName), i, Utility.Constant.ROWSTATUS_ACTIVE);
MonthDim MD = BO.get_MonthDimId(session, i.ToString(), Utility.Constant.ROWSTATUS_ACTIVE);
if (FGLM != null && MD != null)
{
if (FGLM.DebitSum > 0)
{
dr[dc.ColumnName] = sumAcc(FGLM.CorrespondFinancialAccountDimId.Code, FGLM.FinancialGeneralLedgerByYear_FactId.FinancialGeneralLedgerByYear_FactId, MD.MonthDimId, false);
}
else
{
dr[dc.ColumnName] = 0;
}
}
}
}
}
}
dt.Rows.Add(dr);
}
#endregion
}
return(dt);
}
catch (Exception) { return(null); }
}
public DataTable DT_getRowValue(DataTable dt, int FinancialAccountDimId, int OwnerOrgDimId, int MonthDimId, int YearDimId)
{
try
{
double row_sum = 0;
DataTable dt_left = DT_getValue(FinancialAccountDimId, OwnerOrgDimId, MonthDimId, YearDimId);
foreach (DataRow dr_left in dt_left.Rows)
{
DataRow row = dt.NewRow();
row["STT"] = dr_left["STT"].ToString();;
row["Description"] = dr_left["Description"].ToString();
row["BeginBalance"] = dr_left["BeginBalance"];
row["LegalInvoiceCode"] = dr_left["LegalInvoiceCode"].ToString();
row["LegalInvoiceIssuedDate"] = dr_left["LegalInvoiceIssuedDate"].ToString();
row["InvoiceCode"] = dr_left["InvoiceCode"].ToString();
row["InvoiceIssuedDate"] = dr_left["InvoiceIssuedDate"].ToString();
#region cac tai khoan no 151
foreach (DataColumn dt_all in DT_get_xp_AllId(FinancialAccountDimId, OwnerOrgDimId, MonthDimId, YearDimId, true, true, true, true).Columns)
{
CorrespondFinancialAccountDim cfad = BO.get_CorrespondFinancialAccountDim(session, int.Parse(dt_all.ColumnName.ToString()), Utility.Constant.ROWSTATUS_ACTIVE);
if (cfad != null)
{
if (cfad.Code.Equals("152"))
{
row["GTT"] = dr_left["152ActualPrice"];
row["GHT"] = dr_left["152BookingPrice"];
}
else if (cfad.Code.Equals("153"))
{
row["GTT"] = dr_left["153ActualPrice"];
row["GHT"] = dr_left["153BookingPrice"];
}
else
{
if (!cfad.Code.Equals("NAAN_DEFAULT"))
{
row[cfad.Code] = dr_left[cfad.Code];
}
}
}
}
#endregion
row["congco"] = dr_left["congco"];
row["EndBalance"] = dr_left["EndBalance"];
dt.Rows.Add(row);
}
dt.Rows.Add(SumlTotal_column(dt));
return(dt);
}
catch (Exception) { throw; }
}
public DataTable table_temple_debit(int Account, int month, int year, string asset)
{
DataTable data_table_temple = new DataTable();
XPCollection <MonthDim> md = new XPCollection <MonthDim>(session, CriteriaOperator.Parse("Name='" + month + "'"));
XPCollection <YearDim> yd = new XPCollection <YearDim>(session, CriteriaOperator.Parse("Name='" + year + "'"));
XPCollection <FinancialAccountDim> fad = new XPCollection <FinancialAccountDim>(session, CriteriaOperator.Parse("Code='" + Account + "'"));
//TK Default
CorrespondFinancialAccountDim cfad = CorrespondFinancialAccountDim.GetDefault(session, CorrespondFinancialAccountDimEnum.NAAN_DEFAULT);
if (md.Count != 0 && yd.Count != 0 && fad.Count != 0)
{
XPCollection <FinancialSupplierLiabilitySummary_Fact> fslsf =
new XPCollection <FinancialSupplierLiabilitySummary_Fact>(
session, CriteriaOperator.Parse("MonthDimId='" + md[0].MonthDimId + "' AND YearDimId='" + yd[0].YearDimId + "' AND FinancialAccountDimId='" + fad[0].FinancialAccountDimId + "' AND RowStatus='1'"));
if (fslsf.Count != 0)
{
foreach (FinancialSupplierLiabilitySummary_Fact each_fslsf in fslsf)
{
// all debit 331
XPCollection <FinancialSupplierLiabilityDetail> fsld = new XPCollection <FinancialSupplierLiabilityDetail>(session, CriteriaOperator.Parse("FinancialAccountDimId='" + fad[0].FinancialAccountDimId + "' AND Debit>0 AND CorrespondFinancialAccountDimId='" + cfad.CorrespondFinancialAccountDimId + "' AND FinancialSupplierLiabilitySummary_FactId='" + each_fslsf.FinancialSupplierLiabilitySummary_FactId + "' AND RowStatus='1'"));
if (fsld.Count != 0)
{
foreach (FinancialSupplierLiabilityDetail each_fsld in fsld)
{
// chứa 2 transaction
XPCollection <FinancialSupplierLiabilityDetail> fsld1 = new XPCollection <FinancialSupplierLiabilityDetail>(session, CriteriaOperator.Parse("FinancialTransactionDimId='" + each_fsld.FinancialTransactionDimId.FinancialTransactionDimId + "' AND RowStatus='1'"));
foreach (FinancialSupplierLiabilityDetail each_fsld1 in fsld1)
{
if (each_fsld1.FinancialCustomerLiabilityDetailId != each_fsld.FinancialCustomerLiabilityDetailId)
{
if (each_fsld1.CorrespondFinancialAccountDimId.CorrespondFinancialAccountDimId != cfad.CorrespondFinancialAccountDimId)
{
try
{
data_table_temple.Columns.Add(each_fsld1.CorrespondFinancialAccountDimId.Code);
}
catch { continue; }
}
}
}
}
}
}
}
}
return(data_table_temple);
}
public CorrespondFinancialAccountDim get_CorrespondFinancialAccountDim(Session session, string Code, short RowStatus)
{
try
{
CorrespondFinancialAccountDim CFAD = session.FindObject <CorrespondFinancialAccountDim>(
CriteriaOperator.And(
new BinaryOperator("Code", Code, BinaryOperatorType.Equal),
new BinaryOperator("RowStatus", RowStatus, BinaryOperatorType.Equal)
));
if (CFAD == null)
{
return(null);
}
return(CFAD);
}
catch (Exception) { return(null); }
}
public CorrespondFinancialAccountDim get_CorrespondFinancialAccountDimId_1(Session session, Guid CorrespondFinancialAccountDimId, short RowStatus)
{
try
{
CorrespondFinancialAccountDim CFAD_id = session.FindObject <CorrespondFinancialAccountDim>(
CriteriaOperator.And(
new BinaryOperator("CorrespondFinancialAccountDimId", CorrespondFinancialAccountDimId, BinaryOperatorType.Equal),
new BinaryOperator("RowStatus", RowStatus, BinaryOperatorType.Equal)
));
if (CFAD_id == null)
{
return(null);
}
return(CFAD_id);
}
catch (Exception) { throw; }
}
public override void FixInvokedBussinessObjects(DevExpress.Xpo.Session session, DevExpress.Xpo.XPCollection <DAL.System.Log.BusinessObject> invokedBussinessObjects)
{
if (invokedBussinessObjects == null || invokedBussinessObjects.Count == 0)
{
return;
}
CriteriaOperator criteria_0 = CriteriaOperator.Parse("not(IsNull(FinancialTransactionDimId))");
CriteriaOperator criteria_1 = new InOperator("FinancialTransactionDimId.RefId", invokedBussinessObjects.Select(i => i.RefId));
CriteriaOperator criteria_2 = new BinaryOperator("RowStatus", 0, BinaryOperatorType.Greater);
CriteriaOperator criteria = new GroupOperator(GroupOperatorType.And, criteria_0, criteria_1, criteria_2);
CorrespondFinancialAccountDim defaultCorrespondAccDim = CorrespondFinancialAccountDim.GetDefault(session, CorrespondFinancialAccountDimEnum.NAAN_DEFAULT);
XPCollection <GoodsInInventoryDetail> neededToBeFixList = new XPCollection <GoodsInInventoryDetail>(session, criteria);
GoodsInInventorySummary_Fact fact = null;
if (neededToBeFixList != null && neededToBeFixList.Count > 0)
{
foreach (GoodsInInventoryDetail detail in neededToBeFixList)
{
fact = detail.GoodsInInventorySummary_FacftId;
detail.RowStatus = Utility.Constant.ROWSTATUS_DELETED;
detail.Save();
//fact.CreditSum = fact.GoodsInInventoryDetails.Where(i => i.RowStatus == 1
// && i.Credit > 0 && i.CorrespondFinancialAccountDimId == defaultCorrespondAccDim).Sum(d => d.Credit);
//fact.DebitSum = fact.GoodsInInventoryDetails.Where(i => i.RowStatus == 1
// && i.Debit > 0 && i.CorrespondFinancialAccountDimId == defaultCorrespondAccDim).Sum(d => d.Debit);
//fact.EndCreditBalance
// = fact.BeginCreditBalance +
// fact.CreditSum -
// fact.DebitSum;
//fact.EndDebitBalance
// = fact.BeginDebitBalance +
// fact.DebitSum -
// fact.CreditSum;
//fact.Save();
}
}
}
public void FixInvokedBussinessObjects(Session session, XPCollection <DAL.System.Log.BusinessObject> invokedBussinessObjects)
{
if (invokedBussinessObjects == null || invokedBussinessObjects.Count == 0)
{
return;
}
CriteriaOperator criteria_0 = CriteriaOperator.Parse("not(IsNull(FinancialTransactionDimId))");
CriteriaOperator criteria_1 = new InOperator("FinancialTransactionDimId.RefId", invokedBussinessObjects.Select(i => i.RefId));
CriteriaOperator criteria_2 = new BinaryOperator("RowStatus", 0, BinaryOperatorType.Greater);
CriteriaOperator criteria = new GroupOperator(GroupOperatorType.And, criteria_0, criteria_1, criteria_2);
CorrespondFinancialAccountDim defaultAccDim = CorrespondFinancialAccountDim.GetDefault(session, CorrespondFinancialAccountDimEnum.NAAN_DEFAULT);
XPCollection <FinancialPrepaidExpenseDetail> neededToBeFixList = new XPCollection <FinancialPrepaidExpenseDetail>(session, criteria);
FinancialPrepaidExpenseSummary_Fact fact = null;
if (neededToBeFixList != null && neededToBeFixList.Count > 0)
{
foreach (FinancialPrepaidExpenseDetail detail in neededToBeFixList)
{
fact = detail.FinancialPrepaidExpenseSummary_FactId;
detail.RowStatus = Utility.Constant.ROWSTATUS_DELETED;
detail.Save();
//if (defaultAccDim != null && detail.CorrespondFinancialAccountDimId != null
// && detail.CorrespondFinancialAccountDimId.Code.Equals(defaultAccDim.Code))
//{
// fact.CreditSum -= detail.Credit;
// fact.DebitSum -= detail.Debit;
//}
//fact.EndCreditBalance
// = fact.BeginCreditBalance +
// fact.CreditSum -
// fact.DebitSum;
//fact.EndDebitBalance
// = fact.BeginDebitBalance +
// fact.DebitSum -
// fact.CreditSum;
//fact.Save();
}
}
}
private DataTable DT_Credit151(int FinancialAccountDimId, int OwnerOrgDimId, int MonthDimId, int YearDimId)
{
try
{
DataTable dt_debit = new DataTable();
XPCollection <FinancialOnTheWayBuyingGoodSummary> FOTW = BO.get_xp_FinancialOnTheWayBuyingGoodSummary(session, FinancialAccountDimId, OwnerOrgDimId, MonthDimId, YearDimId);
if (FOTW != null && FOTW.Count > 0)
{
foreach (FinancialOnTheWayBuyingGoodSummary fotw1 in FOTW)
{
XPCollection <OnTheWayBuyingGoodArtifact> OTWB = BO.get_xp_OnTheWayBuyingGoodArtifact(session, fotw1.FinancialOnTheWayBuyingGoodSummaryId, Utility.Constant.ROWSTATUS_ACTIVE);
if (OTWB != null)
{
foreach (OnTheWayBuyingGoodArtifact otwb1 in OTWB)
{
XPCollection <FinancialOnTheWayBuyingGoodDetail> FOTWBGD = BO.get_xp_FinancialOnTheWayBuyingGoodDetail(session, FinancialAccountDimId, otwb1.OnTheWayBuyingGoodArtifactId, Utility.Constant.ROWSTATUS_ACTIVE);
if (FOTWBGD != null)
{
foreach (FinancialOnTheWayBuyingGoodDetail fotwbgd1 in FOTWBGD)
{
CorrespondFinancialAccountDim CFAD = BO.get_CorrespondFinancialAccountDim(session, fotwbgd1.CorrespondFinancialAccountDimId.CorrespondFinancialAccountDimId, Utility.Constant.ROWSTATUS_ACTIVE);
if (CFAD != null)
{
try
{
dt_debit.Columns.Add(CFAD.Code);
}
catch { continue; }
}
}
}
}
}
}
}
return(dt_debit);
}
catch (Exception) { return(null); }
}
public void FixInvokedBussinessObjects(Session session, XPCollection <BusinessObject> invokedBussinessObjects)
{
if (invokedBussinessObjects == null || invokedBussinessObjects.Count == 0)
{
return;
}
// chua link transaction
CriteriaOperator criteria_0 = CriteriaOperator.Parse("not(IsNull(FinancialTransactionDimId))");
CriteriaOperator criteria_1 = new InOperator("FinancialTransactionDimId.RefId", invokedBussinessObjects.Select(i => i.RefId));
CriteriaOperator criteria_2 = new BinaryOperator("RowStatus", 0, BinaryOperatorType.Greater);
CriteriaOperator criteria = new GroupOperator(GroupOperatorType.And, criteria_0, criteria_1, criteria_2);
CorrespondFinancialAccountDim defaultAccDim = CorrespondFinancialAccountDim.GetDefault(session, CorrespondFinancialAccountDimEnum.NAAN_DEFAULT);
XPCollection <FinancialOnTheWayBuyingGoodDetail> neededToBeFixList = new XPCollection <FinancialOnTheWayBuyingGoodDetail>(session, criteria);
//FinancialOnTheWayBuyingGoodSummary fact = null;
//List<OnTheWayBuyingGoodArtifact> relevantArtifacts = new List<OnTheWayBuyingGoodArtifact>();
if (neededToBeFixList != null && neededToBeFixList.Count > 0)
{
foreach (FinancialOnTheWayBuyingGoodDetail detail in neededToBeFixList)
{
detail.RowStatus = Utility.Constant.ROWSTATUS_DELETED;
detail.Save();
//relevantArtifacts.Add(detail.OnTheWayBuyingGoodArtifactId);
//if (defaultAccDim != null && detail.CorrespondFinancialAccountDimId != null
// && detail.CorrespondFinancialAccountDimId.Code.Equals(defaultAccDim.Code))
//{
// fact.CreditSum -= (decimal)detail.Credit;
// fact.DebitSum -= (decimal)detail.Debit;
//}
//fact.EndBalance
// = fact.BeginBalance -
// fact.CreditSum +
// fact.DebitSum;
//fact.Save();
}
}
}
//TK header (f & c)
public List <string> list_header()
{
#region tham số truyền
int month = Int32.Parse(this.hS04b4DN_month.Get("month_id").ToString());
int year = Int32.Parse(this.hS04b4DN_year.Get("year_id").ToString());
string owner = "QUASAPHARCO";
//string asset = "";
#endregion
List <string> list_header = new List <string>();
MonthDim md = session.FindObject <MonthDim>(CriteriaOperator.Parse(String.Format("Name='{0}'", month)));
YearDim yd = session.FindObject <YearDim>(CriteriaOperator.Parse(String.Format("Name='{0}'", year)));
OwnerOrgDim ood = session.FindObject <OwnerOrgDim>(CriteriaOperator.Parse(String.Format("Code='{0}'",
owner)));
int CorrespondFinancialAccountDimId_default = CorrespondFinancialAccountDim.GetDefault(session,
CorrespondFinancialAccountDimEnum.NAAN_DEFAULT).CorrespondFinancialAccountDimId;
if (md != null && yd != null && ood != null)
{
// chung
FinancialSalesOrManufactureExpenseSummary_Fact FinancialFact_General =
session.FindObject <FinancialSalesOrManufactureExpenseSummary_Fact>(
CriteriaOperator.Parse(String.Format("MonthDimId='{0}' AND YearDimId='{1}' AND "
+ "OwnerOrgDimId='{2}' AND RowStatus='1'",
md.MonthDimId,
yd.YearDimId,
ood.OwnerOrgDimId)));
// 154 621 622 623 627 631
support_list_header("154", FinancialFact_General, CorrespondFinancialAccountDimId_default, list_header);
support_list_header("621", FinancialFact_General, CorrespondFinancialAccountDimId_default, list_header);
support_list_header("622", FinancialFact_General, CorrespondFinancialAccountDimId_default, list_header);
support_list_header("623", FinancialFact_General, CorrespondFinancialAccountDimId_default, list_header);
support_list_header("627", FinancialFact_General, CorrespondFinancialAccountDimId_default, list_header);
support_list_header("631", FinancialFact_General, CorrespondFinancialAccountDimId_default, list_header);
//
}
list_header.Sort();
return(list_header);
}
public void FixInvokedBussinessObjects(Session session, XPCollection <DAL.System.Log.BusinessObject> invokedBussinessObjects)
{
if (invokedBussinessObjects == null || invokedBussinessObjects.Count == 0)
{
return;
}
CriteriaOperator criteria_0 = CriteriaOperator.Parse("not(IsNull(FinancialTransactionDimId))");
CriteriaOperator criteria_1 = new InOperator("FinancialTransactionDimId.RefId", invokedBussinessObjects.Select(i => i.RefId));
CriteriaOperator criteria_2 = new BinaryOperator("RowStatus", 0, BinaryOperatorType.Greater);
CriteriaOperator criteria = new GroupOperator(GroupOperatorType.And, criteria_0, criteria_1, criteria_2);
CorrespondFinancialAccountDim defaultAccDim = CorrespondFinancialAccountDim.GetDefault(session, CorrespondFinancialAccountDimEnum.NAAN_DEFAULT);
XPCollection <FinancialSalesOrManufactureExpenseDetail> neededToBeFixList
= new XPCollection <FinancialSalesOrManufactureExpenseDetail>(session, criteria);
List <SalesOrManufactureExpenseByGroup> relevantGroups = new List <SalesOrManufactureExpenseByGroup>();
if (neededToBeFixList != null && neededToBeFixList.Count > 0)
{
foreach (FinancialSalesOrManufactureExpenseDetail detail in neededToBeFixList)
{
SalesOrManufactureExpenseByGroup group = null;
group = detail.SalesOrManufactureExpenseByGroupId;
detail.RowStatus = Utility.Constant.ROWSTATUS_DELETED;
detail.Save();
relevantGroups.Add(group);
}
}
if (relevantGroups != null && relevantGroups.Count > 0)
{
foreach (SalesOrManufactureExpenseByGroup group in relevantGroups)
{
group.SumExpense = group.FinancialSalesOrManufactureExpenseDetails.Where(i => i.RowStatus >= 1 && i.Debit > 0).
Sum(r => r.Debit);
group.Save();
}
}
}
public CorrespondFinancialAccountDim CreateCorrespondFinancialAccountDim(Session session, string Code)
{
try
{
Util util = new Util();
Account account = null;
CriteriaOperator criteria_RowStaus = new BinaryOperator("RowStatus", Constant.ROWSTATUS_ACTIVE, BinaryOperatorType.Equal);
CriteriaOperator criteria_Code = new BinaryOperator("Code", Code, BinaryOperatorType.Equal);
CriteriaOperator criteria = CriteriaOperator.And(criteria_Code, criteria_RowStaus);
account = session.FindObject <Account>(criteria);
if (account == null)
{
return(null);
}
if (!Util.IsExistXpoObject <CorrespondFinancialAccountDim>(session, "Code", Code))
{
CorrespondFinancialAccountDim accountDim = new CorrespondFinancialAccountDim(session);
accountDim.Code = Code;
accountDim.Description = account.Name;
accountDim.Name = Code;
accountDim.RowStatus = Constant.ROWSTATUS_ACTIVE;
accountDim.Save();
return(accountDim);
}
}
catch (Exception)
{
return(null);
}
return(null);
}
public void CreateDiaryJournalDetail(Session session, DiaryJournalTemplate diaryJournal, string accountCode, char debitOrCredit)
{
try
{
Util util = new Util();
ETLAccountingBO accountingBO = new ETLAccountingBO();
DiaryJournal_Fact Fact = GetDiaryJournalFact(session, diaryJournal.OwnerOrgId, diaryJournal.IssueDate, accountCode);
DiaryJournal_Detail newDetail = new DiaryJournal_Detail(session);
if (Fact == null)
{
Fact = CreateDiaryJournalFact(session, diaryJournal.OwnerOrgId, diaryJournal.IssueDate, diaryJournal, accountCode);
if (Fact == null)
{
return;
}
}
CorrespondFinancialAccountDim correspondFinancialAccountDim = util.GetXpoObjectByFieldName <CorrespondFinancialAccountDim, string>(session, "Code", diaryJournal.CorrespondFinancialAccountDimCode, BinaryOperatorType.Equal);
FinancialAccountDim financialAccountDim = util.GetXpoObjectByFieldName <FinancialAccountDim, string>(session, "Code", accountCode, BinaryOperatorType.Equal);
FinancialTransactionDim financialTransactionDim = util.GetXpoObjectByFieldName <FinancialTransactionDim, Guid>(session, "RefId", diaryJournal.TransactionId, BinaryOperatorType.Equal);
CurrencyDim currencyDim = util.GetXpoObjectByFieldName <CurrencyDim, string>(session, "Code", diaryJournal.CurrencyDimCode, BinaryOperatorType.Equal);
if (financialTransactionDim == null)
{
financialTransactionDim = accountingBO.CreateFinancialTransactionDim(session, diaryJournal.TransactionId);
if (financialTransactionDim == null)
{
return;
}
}
if (financialAccountDim == null)
{
financialAccountDim = accountingBO.CreateFinancialAccountDim(session, accountCode);
}
if (correspondFinancialAccountDim == null)
{
correspondFinancialAccountDim = accountingBO.CreateCorrespondFinancialAccountDim(session, diaryJournal.CorrespondFinancialAccountDimCode);
}
if (currencyDim == null)
{
currencyDim = accountingBO.CreateCurrencyDim(session, diaryJournal.CurrencyDimCode);
}
Fact.Save();
if (correspondFinancialAccountDim.Code == "131")
{
newDetail.Credit = diaryJournal.Credit;
}
if (correspondFinancialAccountDim != null)
{
if (correspondFinancialAccountDim.Code == "")
{
if (debitOrCredit == 'C')
{
newDetail.Credit = diaryJournal.Credit;
}
else
{
newDetail.Debit = diaryJournal.Debit;
}
newDetail.FinancialAccountDimId = financialAccountDim;
}
else
{
if (debitOrCredit == 'C')
{
newDetail.Debit = diaryJournal.Debit;
}
else
{
newDetail.Credit = diaryJournal.Credit;
}
newDetail.CorrespondFinancialAccountDimId = correspondFinancialAccountDim;
}
}
newDetail.CurrencyDimId = currencyDim;
newDetail.DiaryJournal_FactId = Fact;
newDetail.FinancialTransactionDimId = financialTransactionDim;
newDetail.Save();
}
catch (Exception)
{
return;
}
}
public void load_data()
{
WebModule.Accounting.Report.S04b6_DN s04b6_dn = new Report.S04b6_DN();
#region tham số truyền
int month = Int32.Parse(this.hS04b6DN_month.Get("month_id").ToString());
int year = Int32.Parse(this.hS04b6DN_year.Get("year_id").ToString());
string owner = "QUASAPHARCO";
string fAccount = this.hS04b6dnAccount.Get("account_id").ToString();
//string asset = "";
#endregion
if (fAccount != "142" && fAccount != "242" && fAccount != "335")
{
return;
}
s04b6_dn.xrMonth.Text = month.ToString();
s04b6_dn.xrYear.Text = year.ToString();
if (fAccount == "142" || fAccount == "242")
{
s04b6_dn.xrTitle.Text = String.Format("Tập hợp chi phí trả trước (TK {0})", fAccount);
}
if (fAccount == "335")
{
s04b6_dn.xrTitle.Text = String.Format("Tập hợp chi phí phải trả (TK {0})", fAccount);
}
try
{
#region object
MonthDim md = session.FindObject <MonthDim>(CriteriaOperator.Parse(String.Format("Name='{0}'", month)));
YearDim yd = session.FindObject <YearDim>(CriteriaOperator.Parse(String.Format("Name='{0}'", year)));
OwnerOrgDim ood = session.FindObject <OwnerOrgDim>(CriteriaOperator.Parse(String.Format("Code='{0}'",
owner)));
int CorrespondFinancialAccountDimId_default = CorrespondFinancialAccountDim.GetDefault(session,
CorrespondFinancialAccountDimEnum.NAAN_DEFAULT).CorrespondFinancialAccountDimId;
string rowStatusActive = Utility.Constant.ROWSTATUS_ACTIVE.ToString();
XPCollection <FinancialAccountDim> f_c_FinancialAccountDim = new XPCollection <FinancialAccountDim>(session,
CriteriaOperator.Parse(String.Format("Code like '{0}%' AND RowStatus='{1}'", fAccount, rowStatusActive)));
#endregion
#region header và table báo cáo
grid_header();
DataTable datatable = table_pri();
#endregion
#region all row list_transaction
List <int> list_transaction = new List <int>();
if (f_c_FinancialAccountDim.Count != 0)
{
foreach (FinancialAccountDim each_tk in f_c_FinancialAccountDim)
{
if (md != null && yd != null && ood != null)
{
FinancialPrepaidExpenseSummary_Fact FinancialSummary_Fact =
session.FindObject <FinancialPrepaidExpenseSummary_Fact>(CriteriaOperator.Parse(
String.Format("MonthDimId='{0}' AND "
+ "YearDimId='{1}' AND "
+ "OwnerOrgDimId='{2}' AND "
+ "FinancialAccountDimId='{3}' AND "
+ "RowStatus='{4}'",
md.MonthDimId,
yd.YearDimId,
ood.OwnerOrgDimId,
each_tk.FinancialAccountDimId,
rowStatusActive
)));
if (FinancialSummary_Fact != null)
{
////
XPCollection <FinancialPrepaidExpenseDetail> collec_detail_credit =
new XPCollection <FinancialPrepaidExpenseDetail>(session, CriteriaOperator.Parse(
String.Format("FinancialPrepaidExpenseSummary_FactId='{0}' AND "
+ "Credit>0 AND "
+ "CorrespondFinancialAccountDimId!='{1}' AND "
+ "RowStatus='{2}'",
FinancialSummary_Fact.FinancialPrepaidExpenseSummary_FactId,
CorrespondFinancialAccountDimId_default,
rowStatusActive
)));
if (collec_detail_credit.Count != 0)
{
foreach (FinancialPrepaidExpenseDetail each_detail in collec_detail_credit)
{
if (!list_transaction.Contains(each_detail.FinancialTransactionDimId.FinancialTransactionDimId))
{
list_transaction.Add(each_detail.FinancialTransactionDimId.FinancialTransactionDimId);
}
}
}
////
XPCollection <FinancialPrepaidExpenseDetail> collec_detail_debit =
new XPCollection <FinancialPrepaidExpenseDetail>(session, CriteriaOperator.Parse(
String.Format("FinancialPrepaidExpenseSummary_FactId='{0}' AND "
+ "Debit>0 AND "
+ "CorrespondFinancialAccountDimId!='{1}' AND "
+ "RowStatus='{2}'",
FinancialSummary_Fact.FinancialPrepaidExpenseSummary_FactId,
CorrespondFinancialAccountDimId_default,
rowStatusActive
)));
if (collec_detail_debit.Count != 0)
{
foreach (FinancialPrepaidExpenseDetail each_detail in collec_detail_debit)
{
if (!list_transaction.Contains(each_detail.FinancialTransactionDimId.FinancialTransactionDimId))
{
list_transaction.Add(each_detail.FinancialTransactionDimId.FinancialTransactionDimId);
}
}
}
}
}
}
}
#endregion
#region đổ dữ liệu
#region dòng số dư đầu tháng
DataRow dr = datatable.NewRow();
dr["dien_giai"] = "Kỳ trước chuyển sang";
double no_dau = 0, co_dau = 0;
if (f_c_FinancialAccountDim.Count != 0)
{
foreach (FinancialAccountDim each_tk in f_c_FinancialAccountDim)
{
if (md != null && yd != null && ood != null)
{
FinancialPrepaidExpenseSummary_Fact FinancialSummary_Fact =
session.FindObject <FinancialPrepaidExpenseSummary_Fact>(CriteriaOperator.Parse(
String.Format("MonthDimId='{0}' AND "
+ "YearDimId='{1}' AND "
+ "OwnerOrgDimId='{2}' AND "
+ "FinancialAccountDimId='{3}' AND "
+ "RowStatus='{4}'",
md.MonthDimId,
yd.YearDimId,
ood.OwnerOrgDimId,
each_tk.FinancialAccountDimId,
rowStatusActive
)));
if (FinancialSummary_Fact != null)
{
no_dau += (double)FinancialSummary_Fact.BeginDebitBalance;
co_dau += (double)FinancialSummary_Fact.BeginCreditBalance;
}
}
}
}
dr["no_dau"] = no_dau;
dr["co_dau"] = co_dau;
datatable.Rows.Add(dr);
#endregion
int STTu = 1;
// từng dòng
foreach (int each_row in list_transaction)
{
#region
FinancialTransactionDim transaction = session.FindObject <FinancialTransactionDim>(
CriteriaOperator.Parse(String.Format("FinancialTransactionDimId='{0}' AND "
+ "RowStatus='{1}'",
each_row,
rowStatusActive
)));
//
dr = datatable.NewRow();
dr["stt"] = STTu++;
dr["dien_giai"] = transaction.Description;
#endregion
//từng cột
#region credit correspond
double cong_no = 0;
foreach (string header_column in header_credit_correspond())
{
double cell = 0;
//
CorrespondFinancialAccountDim CorrespondId = session.FindObject <CorrespondFinancialAccountDim>(
CriteriaOperator.Parse(String.Format("Code='{0}'", header_column)));
//
if (f_c_FinancialAccountDim.Count != 0)
{
foreach (FinancialAccountDim each_tk in f_c_FinancialAccountDim)
{
if (md != null && yd != null && ood != null)
{
FinancialPrepaidExpenseSummary_Fact FinancialSummary_Fact =
session.FindObject <FinancialPrepaidExpenseSummary_Fact>(CriteriaOperator.Parse(
String.Format("MonthDimId='{0}' AND "
+ "YearDimId='{1}' AND "
+ "OwnerOrgDimId='{2}' AND "
+ "FinancialAccountDimId='{3}' AND "
+ "RowStatus='{4}'",
md.MonthDimId,
yd.YearDimId,
ood.OwnerOrgDimId,
each_tk.FinancialAccountDimId,
rowStatusActive
)));
if (FinancialSummary_Fact != null)
{
XPCollection <FinancialPrepaidExpenseDetail> collec_detail_credit =
new XPCollection <FinancialPrepaidExpenseDetail>(session, CriteriaOperator.Parse(
String.Format("FinancialPrepaidExpenseSummary_FactId='{0}' AND "
+ "Credit>0 AND "
+ "CorrespondFinancialAccountDimId!='{1}' AND "
+ "RowStatus='{2}' AND "
+ "FinancialTransactionDimId='{3}' AND "
+ "CorrespondFinancialAccountDimId='{4}'",
FinancialSummary_Fact.FinancialPrepaidExpenseSummary_FactId,
CorrespondFinancialAccountDimId_default,
rowStatusActive,
each_row,
CorrespondId.CorrespondFinancialAccountDimId
)));
if (collec_detail_credit.Count != 0)
{
foreach (FinancialPrepaidExpenseDetail each_detail in collec_detail_credit)
{
cell += (double)each_detail.Credit;
cong_no += (double)each_detail.Credit;
}
}
}
}
}
}
//
dr[header_column + "_co"] = cell;
}
dr["cong_no"] = cong_no;
#endregion
// từng cột
#region debit correspond
double cong_co = 0;
foreach (string header_column in header_debit_correspond())
{
double cell = 0;
//
CorrespondFinancialAccountDim CorrespondId = session.FindObject <CorrespondFinancialAccountDim>(
CriteriaOperator.Parse(String.Format("Code='{0}'", header_column)));
//
if (f_c_FinancialAccountDim.Count != 0)
{
foreach (FinancialAccountDim each_tk in f_c_FinancialAccountDim)
{
if (md != null && yd != null && ood != null)
{
FinancialPrepaidExpenseSummary_Fact FinancialSummary_Fact =
session.FindObject <FinancialPrepaidExpenseSummary_Fact>(CriteriaOperator.Parse(
String.Format("MonthDimId='{0}' AND "
+ "YearDimId='{1}' AND "
+ "OwnerOrgDimId='{2}' AND "
+ "FinancialAccountDimId='{3}' AND "
+ "RowStatus='{4}'",
md.MonthDimId,
yd.YearDimId,
ood.OwnerOrgDimId,
each_tk.FinancialAccountDimId,
rowStatusActive
)));
if (FinancialSummary_Fact != null)
{
XPCollection <FinancialPrepaidExpenseDetail> collec_detail_debit =
new XPCollection <FinancialPrepaidExpenseDetail>(session, CriteriaOperator.Parse(
String.Format("FinancialPrepaidExpenseSummary_FactId='{0}' AND "
+ "Debit>0 AND "
+ "CorrespondFinancialAccountDimId!='{1}' AND "
+ "RowStatus='{2}' AND "
+ "FinancialTransactionDimId='{3}' AND "
+ "CorrespondFinancialAccountDimId='{4}'",
FinancialSummary_Fact.FinancialPrepaidExpenseSummary_FactId,
CorrespondFinancialAccountDimId_default,
rowStatusActive,
each_row,
CorrespondId.CorrespondFinancialAccountDimId
)));
if (collec_detail_debit.Count != 0)
{
foreach (FinancialPrepaidExpenseDetail each_detail in collec_detail_debit)
{
cell += (double)each_detail.Debit;
cong_co += (double)each_detail.Debit;
}
}
}
}
}
}
dr[header_column + "_no"] = cell;
}
dr["cong_co"] = cong_co;
#endregion
datatable.Rows.Add(dr);
}
#endregion
#region dòng cộng
dr = datatable.NewRow();
dr["dien_giai"] = "Tổng cộng";
int column_count = datatable.Columns.Count - 3;
int row_count = datatable.Rows.Count - 1;
for (int c = 2; c <= column_count; c++)
{
double sumT = 0;
for (int r = 1; r <= row_count; r++)
{
double tt;
double.TryParse(datatable.Rows[r][c].ToString(), out tt);
sumT += tt;
}
dr[datatable.Columns[c]] = sumT;
}
//
double no_cuoi = 0;
if (f_c_FinancialAccountDim.Count != 0)
{
foreach (FinancialAccountDim each_tk in f_c_FinancialAccountDim)
{
if (md != null && yd != null && ood != null)
{
FinancialPrepaidExpenseSummary_Fact FinancialSummary_Fact =
session.FindObject <FinancialPrepaidExpenseSummary_Fact>(CriteriaOperator.Parse(
String.Format("MonthDimId='{0}' AND "
+ "YearDimId='{1}' AND "
+ "OwnerOrgDimId='{2}' AND "
+ "FinancialAccountDimId='{3}' AND "
+ "RowStatus='{4}'",
md.MonthDimId,
yd.YearDimId,
ood.OwnerOrgDimId,
each_tk.FinancialAccountDimId,
rowStatusActive
)));
no_cuoi += (double)FinancialSummary_Fact.EndDebitBalance;
}
}
}
//
dr["no_cuoi"] = no_cuoi;
datatable.Rows.Add(dr);
#endregion
#region out gridview
GridView_S04b6DN.DataSource = datatable;
GridView_S04b6DN.DataBind();
#endregion
}
catch { }
#region export report
s04b6_dn.printableCC_S04b6DN.PrintableComponent = new PrintableComponentLinkBase()
{
Component = GridViewExporter_S04b6DN
};
ReportViewer_S04b6DN.Report = s04b6_dn;
#endregion
}
public List <string> header_debit_correspond()
{
List <string> debit_correspond = new List <string>();
#region tham số
int month = Int32.Parse(this.hS04b6DN_month.Get("month_id").ToString());
int year = Int32.Parse(this.hS04b6DN_year.Get("year_id").ToString());
string owner = "QUASAPHARCO";
string fAccount = this.hS04b6dnAccount.Get("account_id").ToString();
#endregion
#region object
MonthDim md = session.FindObject <MonthDim>(CriteriaOperator.Parse(String.Format("Name='{0}'", month)));
YearDim yd = session.FindObject <YearDim>(CriteriaOperator.Parse(String.Format("Name='{0}'", year)));
OwnerOrgDim ood = session.FindObject <OwnerOrgDim>(CriteriaOperator.Parse(String.Format("Code='{0}'",
owner)));
int CorrespondFinancialAccountDimId_default = CorrespondFinancialAccountDim.GetDefault(session,
CorrespondFinancialAccountDimEnum.NAAN_DEFAULT).CorrespondFinancialAccountDimId;
string rowStatusActive = Utility.Constant.ROWSTATUS_ACTIVE.ToString();
#endregion
//// tk 142
XPCollection <FinancialAccountDim> f_c_FinancialAccountDim = new XPCollection <FinancialAccountDim>(session,
CriteriaOperator.Parse(String.Format("Code like '{0}%' AND RowStatus='{1}'", fAccount, rowStatusActive)));
if (f_c_FinancialAccountDim.Count != 0)
{
foreach (FinancialAccountDim each_tk in f_c_FinancialAccountDim)
{
if (md != null && yd != null && ood != null)
{
FinancialPrepaidExpenseSummary_Fact FinancialSummary_Fact =
session.FindObject <FinancialPrepaidExpenseSummary_Fact>(CriteriaOperator.Parse(
String.Format("MonthDimId='{0}' AND "
+ "YearDimId='{1}' AND "
+ "OwnerOrgDimId='{2}' AND "
+ "FinancialAccountDimId='{3}' AND "
+ "RowStatus='{4}'",
md.MonthDimId,
yd.YearDimId,
ood.OwnerOrgDimId,
each_tk.FinancialAccountDimId,
rowStatusActive
)));
if (FinancialSummary_Fact != null)
{
XPCollection <FinancialPrepaidExpenseDetail> collec_detail =
new XPCollection <FinancialPrepaidExpenseDetail>(session, CriteriaOperator.Parse(
String.Format("FinancialPrepaidExpenseSummary_FactId='{0}' AND "
+ "Debit>0 AND "
+ "CorrespondFinancialAccountDimId!='{1}' AND "
+ "RowStatus='{2}'",
FinancialSummary_Fact.FinancialPrepaidExpenseSummary_FactId,
CorrespondFinancialAccountDimId_default,
rowStatusActive
)));
if (collec_detail.Count != 0)
{
foreach (FinancialPrepaidExpenseDetail each_detail in collec_detail)
{
if (!debit_correspond.Contains(each_detail.CorrespondFinancialAccountDimId.Code))
{
debit_correspond.Add(each_detail.CorrespondFinancialAccountDimId.Code);
}
}
}
}
}
}
}
////
debit_correspond.Sort();
return(debit_correspond);
}
private double SumAcc(Session session, string CorrANDFnanicialACC, int FinancialTransactionDimId, int DiaryJournal_FactId, bool CreditOrDebit)
{
double sum = 0;
if (!CorrANDFnanicialACC.Equals("311") ||
!CorrANDFnanicialACC.Equals("315") ||
!CorrANDFnanicialACC.Equals("341") ||
!CorrANDFnanicialACC.Equals("342") ||
!CorrANDFnanicialACC.Equals("343"))
{
CorrespondFinancialAccountDim corr = BO.get_CorrespondFinancialAccountDim(session, CorrANDFnanicialACC, Utility.Constant.ROWSTATUS_ACTIVE);
if (corr != null)
{
if (corr != null)
{
XPCollection <DiaryJournal_Detail> DJD = BO.get_Xp_DiaryJournal_Detail_6(session, corr.CorrespondFinancialAccountDimId, FinancialTransactionDimId, DiaryJournal_FactId);
if (DJD != null)
{
foreach (DiaryJournal_Detail djd in DJD)
{
if (CreditOrDebit == true && djd.Credit > 0)
{
sum += djd.Credit;
}
else if (CreditOrDebit == false && djd.Debit > 0)
{
sum += djd.Debit;
}
}
}
}
}
}
if (CorrANDFnanicialACC.Equals("311") ||
CorrANDFnanicialACC.Equals("315") ||
CorrANDFnanicialACC.Equals("341") ||
CorrANDFnanicialACC.Equals("342") ||
CorrANDFnanicialACC.Equals("343"))
{
FinancialAccountDim FAD = BO.get_FinancialAccountDim(session, CorrANDFnanicialACC, Utility.Constant.ROWSTATUS_ACTIVE);
{
XPCollection <DiaryJournal_Detail> DJD = BO.get_Xp_DiaryJournal_Detail_5(session, FAD.FinancialAccountDimId, FinancialTransactionDimId, DiaryJournal_FactId);
if (DJD != null)
{
foreach (DiaryJournal_Detail djd in DJD)
{
if (CreditOrDebit == true && djd.Credit > 0)
{
sum += djd.Credit;
}
else if (CreditOrDebit == false && djd.Debit > 0)
{
sum += djd.Debit;
}
}
}
}
}
return(sum);
}
public void load_data()
{
WebModule.Accounting.Report.S04b3_DN s04b3_dn = new Report.S04b3_DN();
//try
//{
#region tham số truyền
int month = Int32.Parse(this.hS04b3DN_month.Get("month_id").ToString());
int year = Int32.Parse(this.hS04b3DN_year.Get("year_id").ToString());
string owner = "QUASAPHARCO";
//string asset = "";
s04b3_dn.xrMonth.Text = month.ToString();
s04b3_dn.xrYear.Text = year.ToString();
#endregion
#region Id
XPCollection <FinancialAccountDim> FinancialAccountDim_152 = new XPCollection <FinancialAccountDim>(session, CriteriaOperator.Parse("Code like '152%' AND Code!='152'"));
XPCollection <FinancialAccountDim> FinancialAccountDim_153 = new XPCollection <FinancialAccountDim>(session, CriteriaOperator.Parse("Code like '153%' AND Code!='153'"));
//int FinancialAccountDimId_152 = session.FindObject<FinancialAccountDim>(CriteriaOperator.Parse("Name='152'")).FinancialAccountDimId;
//int FinancialAccountDimId_153 = session.FindObject<FinancialAccountDim>(CriteriaOperator.Parse("Name='153'")).FinancialAccountDimId;
int MonthDimId = session.FindObject <MonthDim>(CriteriaOperator.Parse(String.Format("Name='{0}'", month))).MonthDimId;
int YearDimId = session.FindObject <YearDim>(CriteriaOperator.Parse(String.Format("Name='{0}'", year))).YearDimId;
int OwnerOrgDimId = session.FindObject <OwnerOrgDim>(CriteriaOperator.Parse(String.Format("Code='{0}'", owner))).OwnerOrgDimId;
int CorrespondFinancialAccountDimId_default = CorrespondFinancialAccountDim.GetDefault(session, CorrespondFinancialAccountDimEnum.NAAN_DEFAULT).CorrespondFinancialAccountDimId;
FinancialActualPriceSummary_Fact FinancialActualPriceSummary_Fact = null;
#endregion
#region header và table báo cáo
grid_header();
DataTable datatable = table_pri();
#endregion
#region danh sách đối ứng
// danh sach doi ung 152
List <string> list_Correspond_152_153 = new List <string>();
if (FinancialAccountDim_152.Count != 0)
{
foreach (FinancialAccountDim each_FinancialAccountDim_152 in FinancialAccountDim_152)
{
FinancialActualPriceSummary_Fact = FinancialActualPriceSummary_Fact_152_153(each_FinancialAccountDim_152.FinancialAccountDimId,
OwnerOrgDimId, YearDimId, MonthDimId);
list_correstpond(FinancialActualPriceSummary_Fact, each_FinancialAccountDim_152.FinancialAccountDimId, CorrespondFinancialAccountDimId_default, list_Correspond_152_153);
}
}
// danh sach doi ung 153
if (FinancialAccountDim_153.Count != 0)
{
foreach (FinancialAccountDim each_FinancialAccountDim_153 in FinancialAccountDim_153)
{
FinancialActualPriceSummary_Fact = FinancialActualPriceSummary_Fact_152_153(each_FinancialAccountDim_153.FinancialAccountDimId,
OwnerOrgDimId, YearDimId, MonthDimId);
list_correstpond(FinancialActualPriceSummary_Fact, each_FinancialAccountDim_153.FinancialAccountDimId, CorrespondFinancialAccountDimId_default, list_Correspond_152_153);
}
}
list_Correspond_152_153.Sort();
#endregion
#region đổ dữ liệu
#region dòng tĩnh
// dòng 2
DataRow dr = datatable.NewRow();
dr["stt"] = 1;
dr["chi_tieu"] = "I. Số dư đầu tháng";
foreach (string each_column_TT in column_gridview())
{
FinancialActualPriceSummary_Fact fapsf = FinancialActualPriceSummary_Fact_152_153(FinancialAccountDimId(each_column_TT), OwnerOrgDimId, YearDimId, MonthDimId);
dr[each_column_TT + "TT"] = fapsf != null ? fapsf.BeginDebitBalance : 0;
}
datatable.Rows.Add(dr);
// dòng 3
dr = datatable.NewRow();
dr["stt"] = 2;
dr["chi_tieu"] = "II. Số phát sinh trong tháng";
datatable.Rows.Add(dr);
#endregion
#region dòng động
int STTu = 3;
// từng dòng
foreach (string each_TK in list_Correspond_152_153)
{
dr = datatable.NewRow();
dr["stt"] = STTu++;
#region cột chỉ tiêu
if (each_TK == "111")
{
dr["chi_tieu"] = "Từ NKCT số 1 (Ghi có TK " + each_TK + ")";
}
else if (each_TK == "112")
{
dr["chi_tieu"] = "Từ NKCT số 2 (Ghi có TK " + each_TK + ")";
}
else if (each_TK == "113")
{
dr["chi_tieu"] = "Từ NKCT số 3 (Ghi có TK " + each_TK + ")";
}
else if (each_TK == "331")
{
dr["chi_tieu"] = "Từ NKCT số 5 (Ghi có TK " + each_TK + ")";
}
else if (each_TK == "151")
{
dr["chi_tieu"] = "Từ NKCT số 6 (Ghi có TK " + each_TK + ")";
}
else if (list_NKCT4().Contains(each_TK))
{
dr["chi_tieu"] = "Từ NKCT số 4 (Ghi có TK " + each_TK + ")";
}
else if (list_NKCT7().Contains(each_TK))
{
dr["chi_tieu"] = "Từ NKCT số 7 (Ghi có TK " + each_TK + ")";
}
else if (list_NKCT8().Contains(each_TK))
{
dr["chi_tieu"] = "Từ NKCT số 8 (Ghi có TK " + each_TK + ")";
}
else if (list_NKCT9().Contains(each_TK))
{
dr["chi_tieu"] = "Từ NKCT số 9 (Ghi có TK " + each_TK + ")";
}
else if (list_NKCT10().Contains(each_TK))
{
dr["chi_tieu"] = "Từ NKCT số 10 (Ghi có TK " + each_TK + ")";
}
else
{
dr["chi_tieu"] = "Ghi có TK " + each_TK + "";
}
#endregion
#region cột dữ liệu
// từng cột
foreach (string each_column in column_gridview())
{
XPCollection <CorrespondFinancialAccountDim> cfad = new XPCollection <CorrespondFinancialAccountDim>(session, CriteriaOperator.Parse(String.Format("Code like '{0}%'", each_TK)));
double column_mount = 0;
if (cfad.Count != 0)
{
foreach (CorrespondFinancialAccountDim each_cfad in cfad)
{
FinancialActualPriceSummary_Fact FinancialActualPriceSummary_Fact_15 =
FinancialActualPriceSummary_Fact_152_153(FinancialAccountDimId(each_column), OwnerOrgDimId, YearDimId, MonthDimId);
if (FinancialActualPriceSummary_Fact_15 != null)
{
XPCollection <FinancialActualPriceDetail> fapd_15 = new XPCollection <FinancialActualPriceDetail>(session, CriteriaOperator.Parse(String.Format("FinancialAccountDimId={0} AND FinancialActualPriceSummary_FactId='{1}' AND CorrespondFinancialAccountDimId={2} AND Credit>0 AND RowStatus='1'",
FinancialAccountDimId(each_column),
FinancialActualPriceSummary_Fact_15.FinancialActualPriceSummary_FactId,
each_cfad.CorrespondFinancialAccountDimId
)));
if (fapd_15.Count != 0)
{
foreach (FinancialActualPriceDetail each_fapd_152 in fapd_15)
{
column_mount += (double)each_fapd_152.Credit;
}
}
}
}
}
if (column_mount != 0)
{
dr["" + each_column + "TT"] = column_mount;
}
}
datatable.Rows.Add(dr);
#endregion
}
#endregion
#region row Cộng số dư đầu tháng và phát sinh trong tháng
dr = datatable.NewRow();
dr["stt"] = STTu++;
dr["chi_tieu"] = "III. Cộng số dư đầu tháng và phát sinh trong tháng(I+II)";
int column_count = datatable.Columns.Count - 1;
int row_count = datatable.Rows.Count - 1;
for (int c = 2; c <= column_count; c++)
{
double sumT = 0;
for (int r = 1; r <= row_count; r++)
{
double tt;
double.TryParse(datatable.Rows[r][c].ToString(), out tt);
sumT += tt;
}
dr[datatable.Columns[c]] = sumT;
}
datatable.Rows.Add(dr);
#endregion
#region row Hệ số chênh lệch
dr = datatable.NewRow();
dr["stt"] = STTu++;
dr["chi_tieu"] = "IV. Hệ số chênh lệch";
datatable.Rows.Add(dr);
#endregion
#region row Xuất dùng trong tháng
dr = datatable.NewRow();
dr["stt"] = STTu++;
dr["chi_tieu"] = "V. Xuất dùng trong tháng";
//XPCollection<CorrespondFinancialAccountDim> cfad_TK6 = new XPCollection<CorrespondFinancialAccountDim>(session, CriteriaOperator.Parse("Code like '6%'"));
//XPCollection<CorrespondFinancialAccountDim> cfad_TK8 = new XPCollection<CorrespondFinancialAccountDim>(session, CriteriaOperator.Parse("Code like '8%'"));
foreach (string name_152_153 in column_gridview())
{
//double debit_TK6_TK8_152 = 0;
int FinancialAccountDimId = session.FindObject <FinancialAccountDim>(CriteriaOperator.Parse("Code='" + name_152_153 + "'")).FinancialAccountDimId;
FinancialActualPriceSummary_Fact FinancialActualPriceSummary_Fact_15 = session.FindObject <FinancialActualPriceSummary_Fact>(CriteriaOperator.Parse("FinancialAccountDimId='" + FinancialAccountDimId + "' AND OwnerOrgDimId='" + OwnerOrgDimId + "' AND YearDimId='" + YearDimId + "' AND MonthDimId='" + MonthDimId + "' AND RowStatus='1'"));
//if (FinancialActualPriceSummary_Fact_15 != null)
//{
// //TK6
// if (cfad_TK6.Count != 0)
// {
// foreach (CorrespondFinancialAccountDim tk6 in cfad_TK6)
// {
// XPCollection<FinancialActualPriceDetail> details = new XPCollection<FinancialActualPriceDetail>(session, CriteriaOperator.Parse("FinancialActualPriceSummary_FactId='" + FinancialActualPriceSummary_Fact_15.FinancialActualPriceSummary_FactId + "' AND CorrespondFinancialAccountDimId='" + tk6.CorrespondFinancialAccountDimId + "' AND Debit>0 AND RowStatus='1'"));
// if (details.Count != 0)
// {
// foreach (FinancialActualPriceDetail detail in details)
// {
// debit_TK6_TK8_152 += (double)detail.Debit;
// }
// }
// }
// }
// //TK8
// if (cfad_TK8.Count != 0)
// {
// foreach (CorrespondFinancialAccountDim tk8 in cfad_TK8)
// {
// XPCollection<FinancialActualPriceDetail> details = new XPCollection<FinancialActualPriceDetail>(session, CriteriaOperator.Parse("FinancialActualPriceSummary_FactId='" + FinancialActualPriceSummary_Fact_15.FinancialActualPriceSummary_FactId + "' AND CorrespondFinancialAccountDimId='" + tk8.CorrespondFinancialAccountDimId + "' AND Debit>0 AND RowStatus='1'"));
// if (details.Count != 0)
// {
// foreach (FinancialActualPriceDetail detail in details)
// {
// debit_TK6_TK8_152 += (double)detail.Debit;
// }
// }
// }
// }
//}
//dr["" + name_152_153 + "TT"] = debit_TK6_TK8_152;
dr["" + name_152_153 + "TT"] = FinancialActualPriceSummary_Fact_15.CreditSum;
}
datatable.Rows.Add(dr);
#endregion
#region row Tồn kho cuối tháng
dr = datatable.NewRow();
dr["stt"] = STTu;
dr["chi_tieu"] = "VI. Tồn kho cuối tháng(III-V)";
foreach (string name_152_153 in column_gridview())
{
//double TK_III = (from DataRow dr1 in datatable.Rows where dr1["chi_tieu"] == "III. Cộng số dư đầu tháng và phát sinh trong tháng(I+II)" select (double)dr1["" + name_152_153 + "TT"]).FirstOrDefault();
//double TK_V = (from DataRow dr1 in datatable.Rows where dr1["chi_tieu"] == "V. Xuất dùng trong tháng" select (double)dr1["" + name_152_153 + "TT"]).FirstOrDefault();
//dr["" + name_152_153 + "TT"] = TK_III - TK_V;
int FinancialAccountDimId = session.FindObject <FinancialAccountDim>(CriteriaOperator.Parse("Code='" + name_152_153 + "'")).FinancialAccountDimId;
FinancialActualPriceSummary_Fact FinancialActualPriceSummary_Fact_15 = session.FindObject <FinancialActualPriceSummary_Fact>(CriteriaOperator.Parse("FinancialAccountDimId='" + FinancialAccountDimId + "' AND OwnerOrgDimId='" + OwnerOrgDimId + "' AND YearDimId='" + YearDimId + "' AND MonthDimId='" + MonthDimId + "' AND RowStatus='1'"));
dr["" + name_152_153 + "TT"] = FinancialActualPriceSummary_Fact_15.EndDebitBalance;
}
datatable.Rows.Add(dr);
#endregion
#endregion
GridView_S04b3DN.DataSource = datatable;
GridView_S04b3DN.DataBind();
//}
//catch
//{
//}
#region export report
s04b3_dn.printableCC_S04b3DN.PrintableComponent = new PrintableComponentLinkBase()
{
Component = GridViewExporter_S04b3DN
};
ReportViewer_S04b3DN.Report = s04b3_dn;
#endregion
}
public DataTable DT_xp_ALL(int FinancialAccountDimId, int YearDimId, bool FinancialGeneralLedgerByYear_Fact, bool FinancialGeneralLedgerByMonth, bool CorrespondFinancialAccountDim, bool CreditOrDebit)
{
try
{
DataTable dt = new DataTable();
XPCollection <FinancialGeneralLedgerByYear_Fact> FGLY = BO.get_xp_FinancialGeneralLedgerByYear_Fact(session, FinancialAccountDimId, YearDimId, Utility.Constant.ROWSTATUS_ACTIVE);
if (FGLY != null && FinancialGeneralLedgerByYear_Fact == true)
{
foreach (FinancialGeneralLedgerByYear_Fact fgly in FGLY)
{
XPCollection <FinancialGeneralLedgerByMonth> FGLM = BO.get_xp_FinancialGeneralLedgerByMonth(session, fgly.FinancialGeneralLedgerByYear_FactId, Utility.Constant.ROWSTATUS_ACTIVE);
if (FGLM != null && FinancialGeneralLedgerByMonth == true)
{
FGLM.Sorting.Add(new SortProperty("CorrespondFinancialAccountDimId", DevExpress.Xpo.DB.SortingDirection.Ascending));
foreach (FinancialGeneralLedgerByMonth fglm in FGLM)
{
CorrespondFinancialAccountDim corr = BO.get_CorrespondFinancialAccountDim(session, fglm.CorrespondFinancialAccountDimId.Code, Utility.Constant.ROWSTATUS_ACTIVE);
if (corr != null && CorrespondFinancialAccountDim == true)
{
try
{
if (fglm.CreditSum > 0 && CreditOrDebit == true)
{
dt.Columns.Add(corr.CorrespondFinancialAccountDimId.ToString());
}
if (fglm.DebitSum > 0 && CreditOrDebit == false)
{
dt.Columns.Add(corr.CorrespondFinancialAccountDimId.ToString());
}
}
catch { continue; }
}
else if (CorrespondFinancialAccountDim == false)
{
try
{
if (fglm.CreditSum > 0 && CreditOrDebit == true)
{
dt.Columns.Add(fglm.FinancialGeneralLedgerByMonthId.ToString());
}
if (fglm.DebitSum > 0 && CreditOrDebit == false)
{
dt.Columns.Add(fglm.FinancialGeneralLedgerByMonthId.ToString());
}
}
catch { continue; }
}
}
}
else if (FinancialGeneralLedgerByMonth == false)
{
try
{
dt.Columns.Add(fgly.FinancialGeneralLedgerByYear_FactId.ToString());
}
catch { continue; }
}
}
}
else
{
return(null);
}
return(dt);
}
catch (Exception) { return(null); }
}
public void CreateGoodsInInventoryDetail(
Session session,
ETL_GoodsInInventoryDetail Detail,
string MainAccountCode)
{
try
{
Util util = new Util();
CorrespondFinancialAccountDim defaultCorrespondindAcc = CorrespondFinancialAccountDim.GetDefault(session, CorrespondFinancialAccountDimEnum.NAAN_DEFAULT);
FinancialAccountDim defaultFinancialAcc = FinancialAccountDim.GetDefault(session, FinancialAccountDimEnum.NAAN_DEFAULT);
ETLAccountingBO accountingBO = new ETLAccountingBO();
GoodsInInventorySummary_Fact Fact = GetGoodsInInventorySummaryFact(session, Detail.OwnerOrgId, Detail.IssueDate, MainAccountCode);
GoodsInInventoryDetail newDetail = new GoodsInInventoryDetail(session);
if (Fact == null)
{
Fact = CreateGoodsInInventorySummaryFact(session, Detail.OwnerOrgId, Detail.IssueDate, MainAccountCode, Detail.IsBalanceForward);
if (Fact == null)
{
return;
}
}
else
{
var date = new DateTime(Detail.IssueDate.Year, Detail.IssueDate.Month, 1);
GoodsInInventorySummary_Fact previousSummary = GetGoodsInInventorySummaryFact(session,
Detail.OwnerOrgId, date.AddMonths(-1), MainAccountCode);
if (previousSummary != null)
{
Fact.BeginCreditBalance = previousSummary.EndCreditBalance;
Fact.BeginDebitBalance = previousSummary.EndDebitBalance;
}
}
CorrespondFinancialAccountDim correspondFinancialAccountDim = null;
FinancialAccountDim financialAccountDim = null;
if (!Detail.CorrespondAccountCode.Equals(string.Empty))
{
correspondFinancialAccountDim = util.GetXpoObjectByFieldName <CorrespondFinancialAccountDim, string>(session, "Code", Detail.CorrespondAccountCode, BinaryOperatorType.Equal);
}
if (!MainAccountCode.Equals(string.Empty))
{
financialAccountDim = util.GetXpoObjectByFieldName <FinancialAccountDim, string>(session, "Code", MainAccountCode, BinaryOperatorType.Equal);
}
FinancialTransactionDim financialTransactionDim = util.GetXpoObjectByFieldName <FinancialTransactionDim, Guid>(session, "RefId", Detail.TransactionId, BinaryOperatorType.Equal);
CurrencyDim currencyDim = util.GetXpoObjectByFieldName <CurrencyDim, string>(session, "Code", Detail.CurrencyCode, BinaryOperatorType.Equal);
InventoryCommandDim inventoryCommandDim = null;
if (!Detail.ArtifactId.Equals(Guid.Empty))
{
inventoryCommandDim = util.GetXpoObjectByFieldName <InventoryCommandDim, Guid>(session, "RefId", Detail.ArtifactId, BinaryOperatorType.Equal);
if (inventoryCommandDim == null)
{
DimBO dimBO = new DimBO();
inventoryCommandDim = dimBO.GetInventoryCommandDim(session, Detail.ArtifactId);
}
}
else
{
inventoryCommandDim = InventoryCommandDim.GetDefault(session, InventoryCommandDimEnum.UNKNOWN);
}
if (financialTransactionDim == null)
{
financialTransactionDim = accountingBO.CreateFinancialTransactionDim(session, Detail.TransactionId);
if (financialTransactionDim == null)
{
return;
}
}
if (financialAccountDim == null && !MainAccountCode.Equals(string.Empty))
{
financialAccountDim = accountingBO.CreateFinancialAccountDim(session, MainAccountCode);
}
if (correspondFinancialAccountDim == null && !Detail.CorrespondAccountCode.Equals(string.Empty))
{
correspondFinancialAccountDim = accountingBO.CreateCorrespondFinancialAccountDim(session, Detail.CorrespondAccountCode);
}
if (currencyDim == null && !Detail.CurrencyCode.Equals(string.Empty))
{
currencyDim = accountingBO.CreateCurrencyDim(session, Detail.CurrencyCode);
}
if (Detail.IsBalanceForward)
{
Fact.BeginCreditBalance = Fact.EndCreditBalance = Detail.Credit;
Fact.BeginDebitBalance = Fact.EndDebitBalance = Detail.Debit;
Fact.CreditSum = 0;
Fact.DebitSum = 0;
}
else
{
Fact.CreditSum = Fact.GoodsInInventoryDetails.Where(i => i.RowStatus == 1 &&
i.Credit > 0 && i.CorrespondFinancialAccountDimId == defaultCorrespondindAcc).Sum(d => d.Credit);
Fact.DebitSum = Fact.GoodsInInventoryDetails.Where(i => i.RowStatus == 1 &&
i.Debit > 0 && i.CorrespondFinancialAccountDimId == defaultCorrespondindAcc).Sum(d => d.Debit);
Fact.EndCreditBalance = Fact.BeginCreditBalance + Fact.CreditSum - Fact.DebitSum;
Fact.EndDebitBalance = Fact.BeginDebitBalance + Fact.DebitSum - Fact.CreditSum;
}
Fact.Save();
newDetail.CorrespondFinancialAccountDimId = correspondFinancialAccountDim;
newDetail.Credit = Detail.Credit;
newDetail.Debit = Detail.Debit;
newDetail.Quantity = Detail.Quantity;
newDetail.CurrencyDimId = currencyDim;
newDetail.FinancialAccountDimId = financialAccountDim;
newDetail.GoodsInInventorySummary_FacftId = Fact;
newDetail.FinancialTransactionDimId = financialTransactionDim;
newDetail.InventoryCommandDimId = inventoryCommandDim;
if (newDetail.FinancialAccountDimId == null)
{
newDetail.FinancialAccountDimId = defaultFinancialAcc;
}
if (newDetail.CorrespondFinancialAccountDimId == null)
{
newDetail.CorrespondFinancialAccountDimId = defaultCorrespondindAcc;
}
newDetail.RowStatus = Constant.ROWSTATUS_ACTIVE;
newDetail.Save();
}
catch (Exception)
{
return;
}
}
public void load_data()
{
WebModule.Accounting.Report.S04b4_DN s04b4_dn = new Report.S04b4_DN();
#region tham số truyền
int month = Int32.Parse(this.hS04b4DN_month.Get("month_id").ToString());
int year = Int32.Parse(this.hS04b4DN_year.Get("year_id").ToString());
string owner = "QUASAPHARCO";
//string asset = "";
#endregion
s04b4_dn.xrMonth.Text = month.ToString();
s04b4_dn.xrYear.Text = year.ToString();
#region object
MonthDim md = session.FindObject <MonthDim>(CriteriaOperator.Parse(String.Format("Name='{0}'", month)));
YearDim yd = session.FindObject <YearDim>(CriteriaOperator.Parse(String.Format("Name='{0}'", year)));
OwnerOrgDim ood = session.FindObject <OwnerOrgDim>(CriteriaOperator.Parse(String.Format("Code='{0}'",
owner)));
int CorrespondFinancialAccountDimId_default = CorrespondFinancialAccountDim.GetDefault(session,
CorrespondFinancialAccountDimEnum.NAAN_DEFAULT).CorrespondFinancialAccountDimId;
FinancialSalesOrManufactureExpenseSummary_Fact FinancialFact_General = null;
#endregion
#region header và table báo cáo
grid_header();
DataTable datatable = table_pri();
#endregion
#region all row
List <string> all_row_f_c = new List <string>();
if (md != null && yd != null && ood != null)
{
// chung
FinancialFact_General =
session.FindObject <FinancialSalesOrManufactureExpenseSummary_Fact>(
CriteriaOperator.Parse(String.Format("MonthDimId='{0}' AND YearDimId='{1}' AND "
+ "OwnerOrgDimId='{2}' AND RowStatus='1'",
md.MonthDimId,
yd.YearDimId,
ood.OwnerOrgDimId)));
// f & c
all_row_f_c.AddRange(support_find_row_TK(FinancialFact_General, "154"));
all_row_f_c.AddRange(support_find_row_TK(FinancialFact_General, "621"));
all_row_f_c.AddRange(support_find_row_TK(FinancialFact_General, "622"));
all_row_f_c.AddRange(support_find_row_TK(FinancialFact_General, "623"));
all_row_f_c.AddRange(support_find_row_TK(FinancialFact_General, "627"));
all_row_f_c.AddRange(support_find_row_TK(FinancialFact_General, "631"));
//
}
#endregion
#region đổ dữ liệu
int STTu = 1;
// từng dòng
foreach (string each_row in all_row_f_c)
{
#region
FinancialAccountDim fFinancialAccountDim = session.FindObject <FinancialAccountDim>(
CriteriaOperator.Parse(String.Format("Code='{0}'", each_row.Substring(0, 3))));
//
DataRow dr = datatable.NewRow();
if (each_row == "154" || each_row == "621" || each_row == "622" || each_row == "623" ||
each_row == "627" || each_row == "631")
{
dr["stt"] = STTu++;
}
FinancialAccountDim get_Description = session.FindObject <FinancialAccountDim>(
CriteriaOperator.Parse(String.Format("Code='{0}'", each_row)));
if (each_row == "154" || each_row == "621" || each_row == "622" || each_row == "623" ||
each_row == "627" || each_row == "631")
{
dr["tk_no"] = String.Format("TK {0} - {1}", each_row, get_Description.Description);
}
else
{
dr["tk_no"] = get_Description.Description;
}
#endregion
double sum_CPTT = 0;
// từng cột
foreach (string each_column in list_header())
{
#region
int TK_column_CorrespondFinancialAccountDimId = session.FindObject <CorrespondFinancialAccountDim>(
CriteriaOperator.Parse(String.Format("Code='{0}'", each_column))).CorrespondFinancialAccountDimId;
////
if (md != null && yd != null && ood != null)
{
// only
FinancialFact_General =
session.FindObject <FinancialSalesOrManufactureExpenseSummary_Fact>(
CriteriaOperator.Parse(String.Format("MonthDimId='{0}' AND YearDimId='{1}' AND "
+ "OwnerOrgDimId='{2}' AND RowStatus='1'",
md.MonthDimId,
yd.YearDimId,
ood.OwnerOrgDimId)));
if (FinancialFact_General != null && fFinancialAccountDim != null)
{
SalesOrManufactureExpenseByGroup SalesByGroup = session.FindObject <
SalesOrManufactureExpenseByGroup>(CriteriaOperator.Parse(
String.Format("FinancialSalesOrManufactureExpenseSummary_FactId='{0}' AND "
+ "FinancialAccountDimId='{1}' AND RowStatus='1'",
FinancialFact_General.FinancialSalesOrManufactureExpenseSummary_FactId,
fFinancialAccountDim.FinancialAccountDimId
)));
if (SalesByGroup != null)
{
//tìm tập hợp của tài khoản cha, con với từng tk header
XPCollection <FinancialSalesOrManufactureExpenseDetail> all_detail =
new XPCollection <FinancialSalesOrManufactureExpenseDetail>(session,
CriteriaOperator.Parse(String.Format("SalesOrManufactureExpenseByGroupId='{0}' AND "
+ "CorrespondFinancialAccountDimId='{1}' AND "
+ "Credit>0 AND "
+ "RowStatus='1'",
SalesByGroup.SalesOrManufactureExpenseByGroupId,
TK_column_CorrespondFinancialAccountDimId
)));
if (all_detail.Count != 0)
{
if (each_row == "154" || each_row == "621" || each_row == "622" || each_row == "623" ||
each_row == "627" || each_row == "631")
{
double sum_fFinancialAccountDim = 0;
foreach (FinancialSalesOrManufactureExpenseDetail each_detail in all_detail)
{
// tổng
sum_fFinancialAccountDim += (double)each_detail.Credit;
//chi phí thực tế
sum_CPTT += (double)each_detail.Credit;
}
dr[each_column] = sum_fFinancialAccountDim;
}
else
{
double cell = 0;
foreach (FinancialSalesOrManufactureExpenseDetail each_detail in all_detail)
{
if (each_row == each_detail.FinancialAccountDimId.Code)
{
cell += (double)each_detail.Credit;
//chi phí thực tế
sum_CPTT += (double)each_detail.Credit;
}
}
dr[each_column] = cell;
}
}
if (each_row == "154" || each_row == "621" || each_row == "622" || each_row == "623" ||
each_row == "627" || each_row == "631")
{
dr["cong_tt"] = SalesByGroup.SumExpense;
}
else
{
dr["cong_tt"] = sum_CPTT;
}
}
}
}
////
#endregion
}
datatable.Rows.Add(dr);
}
#endregion
#region dòng cộng
DataRow dr_c = datatable.NewRow();
dr_c["tk_no"] = "CỘNG";
List <string> all_column = list_header();
all_column.Add("cong_tt");
foreach (string each_column in all_column)
{
double sum = 0;
try
{
sum += (from DataRow dr1 in datatable.Rows where dr1["stt"].Equals("1") select(double) dr1[each_column]).FirstOrDefault();
}
catch { }
try
{
sum += (from DataRow dr1 in datatable.Rows where dr1["stt"].Equals("2") select(double) dr1[each_column]).FirstOrDefault();
}
catch
{ }
try
{
sum += (from DataRow dr1 in datatable.Rows where dr1["stt"].Equals("3") select(double) dr1[each_column]).FirstOrDefault();
}
catch
{ }
try
{
sum += (from DataRow dr1 in datatable.Rows where dr1["stt"].Equals("4") select(double) dr1[each_column]).FirstOrDefault();
}
catch
{ }
try
{
sum += (from DataRow dr1 in datatable.Rows where dr1["stt"].Equals("5") select(double) dr1[each_column]).FirstOrDefault();
}
catch
{ }
try
{
sum += (from DataRow dr1 in datatable.Rows where dr1["stt"].Equals("6") select(double) dr1[each_column]).FirstOrDefault();
}
catch
{ }
dr_c[each_column] = sum;
}
datatable.Rows.Add(dr_c);
#endregion
#region out gridview
GridView_S04b4DN.DataSource = datatable;
GridView_S04b4DN.DataBind();
#endregion
#region export report
s04b4_dn.printableCC_S04b4DN.PrintableComponent = new PrintableComponentLinkBase()
{
Component = GridViewExporter_S04b4DN
};
ReportViewer_S04b4DN.Report = s04b4_dn;
#endregion
}
public DataTable DT_get_xp_AllId(int FinancialAccountDimId, int OwnerOrgDimId, int MonthDimId, int YearDimId, bool getXp_FinancialOnTheWayBuyingGoodSummary, bool getXp_OnTheWayBuyingGoodArtifact, bool getXp_FinancialOnTheWayBuyingSummaryDetail, bool get_CrossepondFinancialAccountDim)
{
try
{
DataTable dt = new DataTable();
XPCollection <FinancialOnTheWayBuyingGoodSummary> FOTW = BO.get_xp_FinancialOnTheWayBuyingGoodSummary(session, FinancialAccountDimId, OwnerOrgDimId, MonthDimId, YearDimId);
if (FOTW != null && getXp_FinancialOnTheWayBuyingGoodSummary == true)
{
foreach (FinancialOnTheWayBuyingGoodSummary fotw in FOTW)
{
#region OnTheWayBuyingGoodArtifact
XPCollection <OnTheWayBuyingGoodArtifact> OTWB = BO.get_xp_OnTheWayBuyingGoodArtifact(session, fotw.FinancialOnTheWayBuyingGoodSummaryId, Utility.Constant.ROWSTATUS_ACTIVE);
if (OTWB != null && getXp_OnTheWayBuyingGoodArtifact == true)
{
foreach (OnTheWayBuyingGoodArtifact otwb in OTWB)
{
#region FinancialOnTheWayBuyingGoodDetail
XPCollection <FinancialOnTheWayBuyingGoodDetail> FOTWBGD = BO.get_xp_FinancialOnTheWayBuyingGoodDetail(session, FinancialAccountDimId, otwb.OnTheWayBuyingGoodArtifactId, Utility.Constant.ROWSTATUS_ACTIVE);
if (FOTWBGD != null && getXp_FinancialOnTheWayBuyingSummaryDetail == true)
{
foreach (FinancialOnTheWayBuyingGoodDetail fotwbgd in FOTWBGD)
{
#region CorrespondFinancialAccountDim
CorrespondFinancialAccountDim CFAD = BO.get_CorrespondFinancialAccountDim(session, fotwbgd.CorrespondFinancialAccountDimId.CorrespondFinancialAccountDimId, Utility.Constant.ROWSTATUS_ACTIVE);
if (CFAD != null && get_CrossepondFinancialAccountDim == true)
{
try { dt.Columns.Add(CFAD.CorrespondFinancialAccountDimId.ToString()); }
catch { continue; }
}
#endregion
else if (get_CrossepondFinancialAccountDim == false)
{
try { dt.Columns.Add(fotwbgd.FinancialOnTheWayBuyingGoodDetailId.ToString()); }
catch { continue; }
}
}
}
#endregion
else if (getXp_FinancialOnTheWayBuyingSummaryDetail == false)
{
try { dt.Columns.Add(otwb.OnTheWayBuyingGoodArtifactId.ToString()); }
catch { continue; }
}
}
}
#endregion
else if (getXp_FinancialOnTheWayBuyingGoodSummary == true)
{
try { dt.Columns.Add(fotw.FinancialOnTheWayBuyingGoodSummaryId.ToString()); }
catch { continue; }
}
}
}
else
{
return(null);
}
return(dt);
}
catch (Exception) { throw; }
}
public void load_data()
{
WebModule.Accounting.Report.S04b8_DN s04b8_dn = new Report.S04b8_DN();
#region tham số truyền
int month = Int32.Parse(this.hS04b8DN_month.Get("month_id").ToString());
int year = Int32.Parse(this.hS04b8DN_year.Get("year_id").ToString());
string owner = "QUASAPHARCO";
string fAccount = this.hS04b8dnAccount.Get("account_id").ToString();
//string asset = "";
#endregion
if (fAccount != "155" && fAccount != "156" && fAccount != "158")
{
return;
}
try
{
#region object
MonthDim md = session.FindObject <MonthDim>(CriteriaOperator.Parse(String.Format("Name='{0}'", month)));
YearDim yd = session.FindObject <YearDim>(CriteriaOperator.Parse(String.Format("Name='{0}'", year)));
OwnerOrgDim ood = session.FindObject <OwnerOrgDim>(CriteriaOperator.Parse(String.Format("Code='{0}'",
owner)));
int CorrespondFinancialAccountDimId_default = CorrespondFinancialAccountDim.GetDefault(session,
CorrespondFinancialAccountDimEnum.NAAN_DEFAULT).CorrespondFinancialAccountDimId;
string rowStatusActive = Utility.Constant.ROWSTATUS_ACTIVE.ToString();
XPCollection <FinancialAccountDim> f_c_FinancialAccountDim = new XPCollection <FinancialAccountDim>(session,
CriteriaOperator.Parse(String.Format("Code like '{0}%' AND RowStatus='{1}'", fAccount, rowStatusActive)));
#endregion
#region display label report
s04b8_dn.xrMonth.Text = month.ToString();
s04b8_dn.xrYear.Text = year.ToString();
if (fAccount == "155")
{
s04b8_dn.xrTitle.Text = String.Format("Thành phẩm (TK {0})", fAccount);
}
if (fAccount == "156")
{
s04b8_dn.xrTitle.Text = String.Format("Hàng hóa (TK {0})", fAccount);
}
if (fAccount == "158")
{
s04b8_dn.xrTitle.Text = String.Format("Hàng hóa kho bảo thuế (TK {0})", fAccount);
}
// số dư đẩu và cuối kì
double no_dau_ki = 0, no_cuoi_ki = 0;
if (f_c_FinancialAccountDim.Count != 0)
{
foreach (FinancialAccountDim each_tk in f_c_FinancialAccountDim)
{
if (md != null && yd != null && ood != null)
{
GoodsInInventorySummary_Fact FinancialSummary_Fact =
session.FindObject <GoodsInInventorySummary_Fact>(CriteriaOperator.Parse(
String.Format("MonthDimId='{0}' AND "
+ "YearDimId='{1}' AND "
+ "OwnerOrgDimId='{2}' AND "
+ "FinancialAccountDimId='{3}' AND "
+ "RowStatus='{4}'",
md.MonthDimId,
yd.YearDimId,
ood.OwnerOrgDimId,
each_tk.FinancialAccountDimId,
rowStatusActive
)));
if (FinancialSummary_Fact != null)
{
no_dau_ki += (double)FinancialSummary_Fact.BeginDebitBalance;
no_cuoi_ki += (double)FinancialSummary_Fact.EndDebitBalance;
}
}
}
}
s04b8_dn.xrdauki.Text = String.Format("{0:#,#}", no_dau_ki);
s04b8_dn.xrCuoiKi.Text = String.Format("{0:#,#}", no_cuoi_ki);
#endregion
#region header và table báo cáo
grid_header();
DataTable datatable = table_pri();
#endregion
#region all row list_inventory
List <int> list_inventory = new List <int>();
if (f_c_FinancialAccountDim.Count != 0)
{
foreach (FinancialAccountDim each_tk in f_c_FinancialAccountDim)
{
if (md != null && yd != null && ood != null)
{
GoodsInInventorySummary_Fact GoodsSummary_Fact =
session.FindObject <GoodsInInventorySummary_Fact>(CriteriaOperator.Parse(
String.Format("MonthDimId='{0}' AND "
+ "YearDimId='{1}' AND "
+ "OwnerOrgDimId='{2}' AND "
+ "FinancialAccountDimId='{3}' AND "
+ "RowStatus='{4}'",
md.MonthDimId,
yd.YearDimId,
ood.OwnerOrgDimId,
each_tk.FinancialAccountDimId,
rowStatusActive
)));
if (GoodsSummary_Fact != null)
{
////
XPCollection <GoodsInInventoryDetail> collec_detail =
new XPCollection <GoodsInInventoryDetail>(session, CriteriaOperator.Parse(
String.Format("GoodsInInventorySummary_FacftId='{0}' AND "
+ "Credit>0 AND "
+ "CorrespondFinancialAccountDimId!='{1}' AND "
+ "RowStatus='{2}'",
GoodsSummary_Fact.GoodsInInventorySummary_FactId,
CorrespondFinancialAccountDimId_default,
rowStatusActive
)));
if (collec_detail.Count != 0)
{
foreach (GoodsInInventoryDetail each_detail in collec_detail)
{
if (!list_inventory.Contains(each_detail.InventoryCommandDimId.InventoryCommandDimId))
{
list_inventory.Add(each_detail.InventoryCommandDimId.InventoryCommandDimId);
}
}
}
////
collec_detail =
new XPCollection <GoodsInInventoryDetail>(session, CriteriaOperator.Parse(
String.Format("GoodsInInventorySummary_FacftId='{0}' AND "
+ "Debit>0 AND "
+ "CorrespondFinancialAccountDimId!='{1}' AND "
+ "RowStatus='{2}'",
GoodsSummary_Fact.GoodsInInventorySummary_FactId,
CorrespondFinancialAccountDimId_default,
rowStatusActive
)));
if (collec_detail.Count != 0)
{
foreach (GoodsInInventoryDetail each_detail in collec_detail)
{
if (!list_inventory.Contains(each_detail.InventoryCommandDimId.InventoryCommandDimId))
{
list_inventory.Add(each_detail.InventoryCommandDimId.InventoryCommandDimId);
}
}
}
}
}
}
}
#endregion
#region đổ dữ liệu
DataRow dr;
int STTu = 1;
// từng dòng
foreach (int each_row in list_inventory)
{
#region object Inventory
InventoryCommandDim inventory = session.FindObject <InventoryCommandDim>(
CriteriaOperator.Parse(String.Format("InventoryCommandDimId='{0}' AND "
+ "RowStatus='{1}'",
each_row,
rowStatusActive
)));
#endregion
dr = datatable.NewRow();
dr["stt"] = STTu++;
dr["so_hieu"] = inventory.Code;
dr["ngay_thang"] = String.Format("{0:dd/MM/yyyy}", inventory.IssueDate);
dr["dien_giai"] = inventory.Description;
// từng cột
#region credit correspond
double cong_no_TT = 0;
foreach (string header_column in header_credit_correspond())
{
double cell = 0, quantity = 0;;
//
CorrespondFinancialAccountDim CorrespondId = session.FindObject <CorrespondFinancialAccountDim>(
CriteriaOperator.Parse(String.Format("Code='{0}'", header_column)));
//
if (f_c_FinancialAccountDim.Count != 0)
{
foreach (FinancialAccountDim each_tk in f_c_FinancialAccountDim)
{
if (md != null && yd != null && ood != null)
{
GoodsInInventorySummary_Fact GoodsSummary_Fact =
session.FindObject <GoodsInInventorySummary_Fact>(CriteriaOperator.Parse(
String.Format("MonthDimId='{0}' AND "
+ "YearDimId='{1}' AND "
+ "OwnerOrgDimId='{2}' AND "
+ "FinancialAccountDimId='{3}' AND "
+ "RowStatus='{4}'",
md.MonthDimId,
yd.YearDimId,
ood.OwnerOrgDimId,
each_tk.FinancialAccountDimId,
rowStatusActive
)));
if (GoodsSummary_Fact != null)
{
XPCollection <GoodsInInventoryDetail> collec_detail_credit =
new XPCollection <GoodsInInventoryDetail>(session, CriteriaOperator.Parse(
String.Format("GoodsInInventorySummary_FacftId='{0}' AND "
+ "Credit>0 AND "
+ "CorrespondFinancialAccountDimId!='{1}' AND "
+ "RowStatus='{2}' AND "
+ "InventoryCommandDimId='{3}' AND "
+ "CorrespondFinancialAccountDimId='{4}'",
GoodsSummary_Fact.GoodsInInventorySummary_FactId,
CorrespondFinancialAccountDimId_default,
rowStatusActive,
each_row,
CorrespondId.CorrespondFinancialAccountDimId
)));
if (collec_detail_credit.Count != 0)
{
foreach (GoodsInInventoryDetail each_detail in collec_detail_credit)
{
cell += (double)each_detail.Credit;
cong_no_TT += (double)each_detail.Credit;
quantity += each_detail.Quantity;
}
}
}
}
}
}
//
dr[header_column + "_SL_co"] = quantity;
dr[header_column + "_TT_co"] = cell;
}
dr["cong_no_TT"] = cong_no_TT;
#endregion
// từng cột
#region debit correspond
double cong_co_TT = 0;
foreach (string header_column in header_debit_correspond())
{
double cell = 0, quantity = 0;
//
CorrespondFinancialAccountDim CorrespondId = session.FindObject <CorrespondFinancialAccountDim>(
CriteriaOperator.Parse(String.Format("Code='{0}'", header_column)));
//
if (f_c_FinancialAccountDim.Count != 0)
{
foreach (FinancialAccountDim each_tk in f_c_FinancialAccountDim)
{
if (md != null && yd != null && ood != null)
{
GoodsInInventorySummary_Fact GoodsSummary_Fact =
session.FindObject <GoodsInInventorySummary_Fact>(CriteriaOperator.Parse(
String.Format("MonthDimId='{0}' AND "
+ "YearDimId='{1}' AND "
+ "OwnerOrgDimId='{2}' AND "
+ "FinancialAccountDimId='{3}' AND "
+ "RowStatus='{4}'",
md.MonthDimId,
yd.YearDimId,
ood.OwnerOrgDimId,
each_tk.FinancialAccountDimId,
rowStatusActive
)));
if (GoodsSummary_Fact != null)
{
XPCollection <GoodsInInventoryDetail> collec_detail_debit =
new XPCollection <GoodsInInventoryDetail>(session, CriteriaOperator.Parse(
String.Format("GoodsInInventorySummary_FacftId='{0}' AND "
+ "Debit>0 AND "
+ "CorrespondFinancialAccountDimId!='{1}' AND "
+ "RowStatus='{2}' AND "
+ "InventoryCommandDimId='{3}' AND "
+ "CorrespondFinancialAccountDimId='{4}'",
GoodsSummary_Fact.GoodsInInventorySummary_FactId,
CorrespondFinancialAccountDimId_default,
rowStatusActive,
each_row,
CorrespondId.CorrespondFinancialAccountDimId
)));
if (collec_detail_debit.Count != 0)
{
foreach (GoodsInInventoryDetail each_detail in collec_detail_debit)
{
cell += (double)each_detail.Debit;
cong_co_TT += (double)each_detail.Debit;
quantity += each_detail.Quantity;
}
}
}
}
}
}
dr[header_column + "_SL_no"] = quantity;
dr[header_column + "_TT_no"] = cell;
}
dr["cong_co_TT"] = cong_co_TT;
#endregion
datatable.Rows.Add(dr);
}
#endregion
#region dòng cộng
dr = datatable.NewRow();
dr["dien_giai"] = "Cộng";
int column_count = datatable.Columns.Count - 1;
int row_count = datatable.Rows.Count - 1;
for (int c = 4; c <= column_count; c++)
{
double sumT = 0;
for (int r = 1; r <= row_count; r++)
{
double tt;
double.TryParse(datatable.Rows[r][c].ToString(), out tt);
sumT += tt;
}
dr[datatable.Columns[c]] = sumT;
}
datatable.Rows.Add(dr);
#endregion
#region out gridview
GridView_S04b8DN.DataSource = datatable;
GridView_S04b8DN.DataBind();
#endregion
}
catch { }
#region export report
s04b8_dn.printableCC_S04b8DN.PrintableComponent = new PrintableComponentLinkBase()
{
Component = GridViewExporter_S04b8DN
};
ReportViewer_S04b8DN.Report = s04b8_dn;
#endregion
}
public void load_data()
{
WebModule.Accounting.Report.S04a5_DN s04a5_dn = new WebModule.Accounting.Report.S04a5_DN();
try
{
#region tham số truyền vào
int Account = 331;
int month = Int32.Parse(this.hS04a5month.Get("month_id").ToString());
int year = Int32.Parse(this.hS04a5year.Get("year_id").ToString());
string owner = "";
string asset = "";//this.hAsset.Get("asset_id").ToString();
s04a5_dn.xrLabel_month.Text = month.ToString();
s04a5_dn.xrLabel_year.Text = year.ToString();
CorrespondFinancialAccountDim cfad = CorrespondFinancialAccountDim.GetDefault(session, CorrespondFinancialAccountDimEnum.NAAN_DEFAULT);
#endregion
#region tập hợp sử dụng lấy dữ liệu
XPCollection <MonthDim> md = new XPCollection <MonthDim>(session, CriteriaOperator.Parse("Name='" + month + "'"));
XPCollection <YearDim> yd = new XPCollection <YearDim>(session, CriteriaOperator.Parse("Name='" + year + "'"));
XPCollection <FinancialAccountDim> fad = new XPCollection <FinancialAccountDim>(session, CriteriaOperator.Parse("Code='" + Account + "'"));
//XPCollection<FinancialAssetDim> fad = new XPCollection<FinancialAssetDim>(session, CriteriaOperator.Parse("Name='" + asset + "'"));
#endregion
#region xây dựng header và table báo cáo
GridView_header(Account, month, year, asset);
DataTable data_table = table_pri(Account, month, year, asset);
#endregion
#region dòng STT cột
int STT = 1;
DataRow dr = data_table.NewRow();
foreach (DataColumn dc in data_table.Columns)
{
dr[dc.ColumnName] = STT++;
}
data_table.Rows.Add(dr);
#endregion
#region danh sách supplier
XPCollection <FinancialSupplierLiabilitySummary_Fact> fslsf_1 = new XPCollection <FinancialSupplierLiabilitySummary_Fact>(session, CriteriaOperator.Parse("MonthDimId='" + md[0].MonthDimId + "' AND YearDimId='" + yd[0].YearDimId + "' AND FinancialAccountDimId='" + fad[0].FinancialAccountDimId + "' AND RowStatus='1'"));
DataTable table_supplier = new DataTable();
foreach (FinancialSupplierLiabilitySummary_Fact each_fslsf in fslsf_1)
{
try
{
if (each_fslsf.SupplierOrgDimId.Name == "NAAN_DEFAULT")
{
table_supplier.Columns.Add("Nhà cung cấp mặc định");
}
else
{
table_supplier.Columns.Add(each_fslsf.SupplierOrgDimId.Name);
}
}
catch { continue; }
}
#endregion
#region đổ dữ liệu
// dựa theo danh sách supplier
int STTu = 1;
foreach (DataColumn each_supplier in table_supplier.Columns)
{
DataRow[] drow = data_table.Select("ten_don_vi='" + each_supplier.ColumnName + "'");
if (drow.Length == 0)
{
// supplier id
Guid return_supplierID;
if (each_supplier.ColumnName == "Nhà cung cấp mặc định")
{
XPCollection <SupplierOrgDim> supplier_id = new XPCollection <SupplierOrgDim>(session, CriteriaOperator.Parse("Name='NAAN_DEFAULT'"));
return_supplierID = supplier_id[0].SupplierOrgDimId;
}
else
{
XPCollection <SupplierOrgDim> supplier_id = new XPCollection <SupplierOrgDim>(session, CriteriaOperator.Parse("Name='" + each_supplier.ColumnName + "'"));
return_supplierID = supplier_id[0].SupplierOrgDimId;
}
DataRow row = data_table.NewRow();
row["STT"] = STTu++;
row["ten_don_vi"] = each_supplier.ColumnName;
XPCollection <FinancialSupplierLiabilitySummary_Fact> fslsf =
new XPCollection <FinancialSupplierLiabilitySummary_Fact>(
session, CriteriaOperator.Parse("MonthDimId='" + md[0].MonthDimId + "' AND YearDimId='" + yd[0].YearDimId + "' AND FinancialAccountDimId='" + fad[0].FinancialAccountDimId + "' AND SupplierOrgDimId='" + return_supplierID + "' AND RowStatus='1'"));
row["no_dau"] = (double)fslsf[0].BeginDebitBalance > 0 ? (double)fslsf[0].BeginDebitBalance : 0;
row["co_dau"] = (double)fslsf[0].BeginCreditBalance > 0 ? (double)fslsf[0].BeginCreditBalance : 0;
#region xét từng cột phần Có TK 331
double row_sum_credit = 0;
foreach (DataColumn dcCredit331 in table_Credit_331(Account, month, year, asset).Columns)
{
double column_mount = 0;
#region không chứa GiaHT, không chứa GiaTT
if (!dcCredit331.ColumnName.Contains("_GiaHT") && !dcCredit331.ColumnName.Contains("_GiaTT"))
{
//chứa credit 331
XPCollection <FinancialSupplierLiabilityDetail> fsld = new XPCollection <FinancialSupplierLiabilityDetail>(session, CriteriaOperator.Parse("FinancialAccountDimId='" + fad[0].FinancialAccountDimId + "' AND Credit>0 AND CorrespondFinancialAccountDimId='" + cfad.CorrespondFinancialAccountDimId + "' AND FinancialSupplierLiabilitySummary_FactId='" + fslsf[0].FinancialSupplierLiabilitySummary_FactId + "' AND RowStatus='1'"));
if (fsld.Count != 0)
{
foreach (FinancialSupplierLiabilityDetail each_fsld in fsld)
{
// chứa 2 transaction
XPCollection <FinancialSupplierLiabilityDetail> fsld1 = new XPCollection <FinancialSupplierLiabilityDetail>(session, CriteriaOperator.Parse("FinancialTransactionDimId='" + each_fsld.FinancialTransactionDimId.FinancialTransactionDimId + "' AND FinancialSupplierLiabilitySummary_FactId='" + fslsf[0].FinancialSupplierLiabilitySummary_FactId + "' AND RowStatus='1'"));
foreach (FinancialSupplierLiabilityDetail each_fsld1 in fsld1)
{
if (each_fsld1.FinancialCustomerLiabilityDetailId != each_fsld.FinancialCustomerLiabilityDetailId)
{
if (each_fsld1.CorrespondFinancialAccountDimId.Code == dcCredit331.ColumnName)
{
column_mount += (double)each_fsld1.Debit;
row_sum_credit += (double)each_fsld1.Debit;
}
}
}
}
}
if (column_mount != 0)
{
row[dcCredit331.ColumnName] = column_mount;
}
else
{
//row[dcCredit331.ColumnName] = "";
}
}
#endregion
#region không chứa GiaHT, chứa GiaTT
if (!dcCredit331.ColumnName.Contains("_GiaHT") && dcCredit331.ColumnName.Contains("_GiaTT"))
{
// id debit
XPCollection <FinancialSupplierLiabilityDetail> fsld = new XPCollection <FinancialSupplierLiabilityDetail>(session, CriteriaOperator.Parse("FinancialAccountDimId='" + fad[0].FinancialAccountDimId + "' AND Credit>0 AND CorrespondFinancialAccountDimId='" + cfad.CorrespondFinancialAccountDimId + "' AND FinancialSupplierLiabilitySummary_FactId='" + fslsf[0].FinancialSupplierLiabilitySummary_FactId + "' AND RowStatus='1'"));
if (fsld.Count != 0)
{
foreach (FinancialSupplierLiabilityDetail each_fsld in fsld)
{
// chứa 2 transaction
XPCollection <FinancialSupplierLiabilityDetail> fsld1 = new XPCollection <FinancialSupplierLiabilityDetail>(session, CriteriaOperator.Parse("FinancialTransactionDimId='" + each_fsld.FinancialTransactionDimId.FinancialTransactionDimId + "' AND FinancialSupplierLiabilitySummary_FactId='" + fslsf[0].FinancialSupplierLiabilitySummary_FactId + "' AND RowStatus='1'"));
foreach (FinancialSupplierLiabilityDetail each_fsld1 in fsld1)
{
if (each_fsld1.FinancialCustomerLiabilityDetailId != each_fsld.FinancialCustomerLiabilityDetailId && each_fsld1.CorrespondFinancialAccountDimId.Code == dcCredit331.ColumnName.Substring(0, dcCredit331.ColumnName.Length - 6))
{
column_mount += (double)each_fsld1.Debit;
row_sum_credit += (double)each_fsld1.Debit;
}
}
}
}
if (column_mount != 0)
{
row[dcCredit331.ColumnName] = column_mount;
}
else
{
//row[dcCredit331.ColumnName] = "";
}
}
#endregion
}
row["Cong_Co"] = row_sum_credit;
#endregion
#region xét từng cột phần Nợ TK 331
double row_sum_debit = 0;
foreach (DataColumn dcDebit331 in table_Debit_331(Account, month, year, asset).Columns)
{
double column_mount = 0;
// id credit
XPCollection <FinancialSupplierLiabilityDetail> fsld = new XPCollection <FinancialSupplierLiabilityDetail>(session, CriteriaOperator.Parse("FinancialAccountDimId='" + fad[0].FinancialAccountDimId + "' AND Debit>0 AND CorrespondFinancialAccountDimId='" + cfad.CorrespondFinancialAccountDimId + "' AND FinancialSupplierLiabilitySummary_FactId='" + fslsf[0].FinancialSupplierLiabilitySummary_FactId + "' AND RowStatus='1'"));
if (fsld.Count != 0)
{
foreach (FinancialSupplierLiabilityDetail each_fsld in fsld)
{
// chứa 2 transaction
XPCollection <FinancialSupplierLiabilityDetail> fsld1 = new XPCollection <FinancialSupplierLiabilityDetail>(session, CriteriaOperator.Parse("FinancialTransactionDimId='" + each_fsld.FinancialTransactionDimId.FinancialTransactionDimId + "' AND FinancialSupplierLiabilitySummary_FactId='" + fslsf[0].FinancialSupplierLiabilitySummary_FactId + "' AND RowStatus='1'"));
foreach (FinancialSupplierLiabilityDetail each_fsld1 in fsld1)
{
if (each_fsld1.FinancialCustomerLiabilityDetailId != each_fsld.FinancialCustomerLiabilityDetailId && each_fsld1.CorrespondFinancialAccountDimId.Code == dcDebit331.ColumnName.Substring(0, 3))
{
column_mount += (double)each_fsld1.Credit;
row_sum_debit += (double)each_fsld1.Credit;
}
}
}
}
if (column_mount != 0)
{
row[dcDebit331.ColumnName] = column_mount;
}
else
{
//row[dcDebit331.ColumnName] = "";
}
}
row["Cong_No"] = row_sum_debit;
#endregion
row["no_cuoi"] = (double)fslsf[0].EndDebitBalance > 0 ? (double)fslsf[0].EndDebitBalance : 0;
row["co_cuoi"] = (double)fslsf[0].EndCreditBalance > 0 ? (double)fslsf[0].EndCreditBalance : 0;
data_table.Rows.Add(row);
}
}
#endregion
#region dòng tính tổng
DataRow row1 = data_table.NewRow();
row1["ten_don_vi"] = "Tổng Cộng";
int column_count = data_table.Columns.Count - 1;
int row_count = data_table.Rows.Count - 1;
for (int c = 2; c <= column_count; c++)
{
double sumT = 0;
for (int r = 1; r <= row_count; r++)
{
double tt;
double.TryParse(data_table.Rows[r][c].ToString(), out tt);
sumT += tt;
}
row1[data_table.Columns[c]] = sumT;
}
data_table.Rows.Add(row1);
#endregion
#region bind data vào gridview
xGridView.DataSource = data_table;
xGridView.DataBind();
#endregion
}
catch
{ }
#region xuất report
xGridViewExporter.GridViewID = "xGridView";
s04a5_dn.printableCC.PrintableComponent = new PrintableComponentLinkBase()
{
Component = xGridViewExporter
};
ReportViewer.Report = s04a5_dn;
#endregion
}
public DataTable DT_getValue(int FinancialAccountDimId, int OwnerOrgDimId, int MonthDimId, int YearDimId)
{
try
{
int stt = 2;
DataTable dt = new DataTable();
dt.Columns.Add("STT");
dt.Columns.Add("Description");
dt.Columns.Add("BeginBalance");
dt.Columns.Add("LegalInvoiceCode");
dt.Columns.Add("LegalInvoiceIssuedDate");
dt.Columns.Add("InvoiceCode");
dt.Columns.Add("InvoiceIssuedDate");
#region cac cot tai khoan no 151
foreach (DataColumn dt_all in DT_get_xp_AllId(FinancialAccountDimId, OwnerOrgDimId, MonthDimId, YearDimId, true, true, true, false).Columns)
{
FinancialOnTheWayBuyingGoodDetail FTBGD = BO.get_FinancialOnTheWayBuyingGoodDetail(session, Guid.Parse(dt_all.ColumnName.ToString()), Utility.Constant.ROWSTATUS_ACTIVE);
if (FTBGD != null)
{
CorrespondFinancialAccountDim CFAD = BO.get_CorrespondFinancialAccountDim(session, FTBGD.CorrespondFinancialAccountDimId.CorrespondFinancialAccountDimId, Utility.Constant.ROWSTATUS_ACTIVE);
if (CFAD != null && FTBGD.Debit > 0)
{
try
{
if (CFAD.Code.Equals("152"))
{
dt.Columns.Add("152ActualPrice");
dt.Columns.Add("152BookingPrice");
}
else if (CFAD.Code.Equals("153"))
{
dt.Columns.Add("153ActualPrice");
dt.Columns.Add("153BookingPrice");
}
else
{
if (!CFAD.Code.Equals("NAAN_DEFAULT"))
{
dt.Columns.Add(CFAD.Code);
}
}
}
catch { continue; }
}
}
}
dt.Columns.Add("congco");
dt.Columns.Add("EndBalance");
#endregion
#region load money Debit vao tung tai khoan - tug Row
DataRow dr = dt.NewRow();
#region gan 1 lan BeginBalance
foreach (DataColumn dc in dt.Columns)
{
if (dc.ColumnName.Equals("STT"))
{
dr[dc.ColumnName] = stt++;
}
foreach (DataColumn dt_all in DT_get_xp_AllId(FinancialAccountDimId, OwnerOrgDimId, MonthDimId, YearDimId, true, false, false, false).Columns)
{
FinancialOnTheWayBuyingGoodSummary fotwbgosu = BO.get_FinancialOntheWayBuyingGoodSummary(session, Guid.Parse(dt_all.ToString()), Utility.Constant.ROWSTATUS_ACTIVE);
if (dc.ColumnName.Equals("BeginBalance"))
{
dr[dc.ColumnName] = fotwbgosu.BeginBalance;
}
}
}
dt.Rows.Add(dr);
#endregion
#region gan cac tai khoan no cua 151
foreach (DataColumn dt_all in DT_get_xp_AllId(FinancialAccountDimId, OwnerOrgDimId, MonthDimId, YearDimId, true, true, false, false).Columns)
{
OnTheWayBuyingGoodArtifact OTWGA = BO.get_OnTheWayBuyingGoodArtifact(session, Guid.Parse(dt_all.ColumnName.ToString()), Utility.Constant.ROWSTATUS_ACTIVE);
if (OTWGA != null)
{
try
{
dr = dt.NewRow();
foreach (DataColumn dc in dt.Columns)
{
#region lay thong tin chi tiet
if (dc.ColumnName.Equals("STT"))
{
dr[dc.ColumnName] = stt++;
}
if (dc.ColumnName.Equals("Description"))
{
dr[dc.ColumnName] = OTWGA.Description;
}
if (dc.ColumnName.Equals("LegalInvoiceCode"))
{
dr[dc.ColumnName] = OTWGA.LegalInvoiceCode;
}
if (dc.ColumnName.Equals("LegalInvoiceIssuedDate"))
{
dr[dc.ColumnName] = String.Format("{0:d}", OTWGA.LegalInvoiceIssuedDate);
}
if (dc.ColumnName.Equals("InvoiceCode"))
{
dr[dc.ColumnName] = OTWGA.InvoiceCode;
}
if (dc.ColumnName.Equals("InvoiceIssuedDate"))
{
dr[dc.ColumnName] = String.Format("{0:d}", OTWGA.InvoiceIssuedDate);
}
XPCollection <FinancialOnTheWayBuyingGoodDetail> fotwbgd = BO.get_xp_FinancialOnTheWayBuyingGoodDetail(session, FinancialAccountDimId, OTWGA.OnTheWayBuyingGoodArtifactId, Utility.Constant.ROWSTATUS_ACTIVE);
if (fotwbgd != null)
{
foreach (FinancialOnTheWayBuyingGoodDetail ftd in fotwbgd)
{
if (dc.ColumnName.Equals("152ActualPrice"))
{
if (ftd.CorrespondFinancialAccountDimId.Code.Equals("152"))
{
if (ftd.Debit > 0)
{
dr[dc.ColumnName] = SumAcc(session, ftd.CorrespondFinancialAccountDimId.Code, OTWGA.OnTheWayBuyingGoodArtifactId, false);
}
}
}
if (dc.ColumnName.Equals("152BookingPrice"))
{
if (ftd.CorrespondFinancialAccountDimId.Code.Equals("152"))
{
dr[dc.ColumnName] = 0;
}
}
if (dc.ColumnName.Equals("153ActualPrice"))
{
if (ftd.CorrespondFinancialAccountDimId.Code.Equals("153"))
{
if (ftd.Debit > 0)
{
dr[dc.ColumnName] = SumAcc(session, ftd.CorrespondFinancialAccountDimId.Code, OTWGA.OnTheWayBuyingGoodArtifactId, false);
}
}
}
if (dc.ColumnName.Equals("153BookingPrice"))
{
if (ftd.CorrespondFinancialAccountDimId.Code.Equals("153"))
{
dr[dc.ColumnName] = 0;
}
}
if (!dc.ColumnName.Equals("NAAN_DEFAULT"))
{
if (ftd.CorrespondFinancialAccountDimId.Code.Equals(dc.ColumnName))
{
if (ftd.Debit > 0)
{
dr[dc.ColumnName] = SumAcc(session, dc.ColumnName, OTWGA.OnTheWayBuyingGoodArtifactId, false);
}
}
}
}
if (dc.ColumnName.Equals("congco"))
{
dr[dc.ColumnName] = SumAcc(session, "NAAN_DEFAULT", OTWGA.OnTheWayBuyingGoodArtifactId, true);
}
}
#endregion
}
dt.Rows.Add(dr);
}
catch { continue; }
}
}
#endregion
#region gan 1 lan EndBanlance
dr = dt.NewRow();
foreach (DataColumn dc in dt.Columns)
{
if (dc.ColumnName.Equals("STT"))
{
dr[dc.ColumnName] = stt++;
}
foreach (DataColumn dt_all in DT_get_xp_AllId(FinancialAccountDimId, OwnerOrgDimId, MonthDimId, YearDimId, true, false, false, false).Columns)
{
FinancialOnTheWayBuyingGoodSummary fotwbgosu = BO.get_FinancialOntheWayBuyingGoodSummary(session, Guid.Parse(dt_all.ToString()), Utility.Constant.ROWSTATUS_ACTIVE);
if (dc.ColumnName.Equals("EndBalance"))
{
dr[dc.ColumnName] = fotwbgosu.EndBalance;
}
}
}
dt.Rows.Add(dr);
#endregion
#endregion
return(dt);
}
catch (Exception) { throw; }
}
private void CreateFinancialActualPriceDetail(
Session session,
ETL_FinanciaActualPriceDetail Detail,
string MainAccountCode)
{
try
{
Util util = new Util();
CorrespondFinancialAccountDim defaultCorrespondindAcc = CorrespondFinancialAccountDim.GetDefault(session, CorrespondFinancialAccountDimEnum.NAAN_DEFAULT);
FinancialAccountDim defaultFinancialAcc = FinancialAccountDim.GetDefault(session, FinancialAccountDimEnum.NAAN_DEFAULT);
ETLAccountingBO accountingBO = new ETLAccountingBO();
FinancialActualPriceSummary_Fact Fact = GetFinancialActualPriceSummaryFact(session, Detail.OwnerOrgId, Detail.IssueDate, MainAccountCode);
FinancialActualPriceDetail newDetail = new FinancialActualPriceDetail(session);
if (Fact == null)
{
Fact = CreateFinancialActualPriceSummaryFact(session, Detail.OwnerOrgId, Detail.IssueDate, MainAccountCode, Detail.IsBalanceForward);
if (Fact == null)
{
return;
}
}
else
{
var date = new DateTime(Detail.IssueDate.Year, Detail.IssueDate.Month, 1);
FinancialActualPriceSummary_Fact previousSummary = GetFinancialActualPriceSummaryFact(session,
Detail.OwnerOrgId, date.AddMonths(-1), MainAccountCode);
if (previousSummary != null)
{
Fact.BeginDebitBalance = previousSummary.EndDebitBalance;
Fact.BeginCreditBalance = previousSummary.EndCreditBalance;
}
}
CorrespondFinancialAccountDim correspondFinancialAccountDim = null;
FinancialAccountDim financialAccountDim = null;
if (!Detail.CorrespondAccountCode.Equals(string.Empty))
{
correspondFinancialAccountDim = util.GetXpoObjectByFieldName <CorrespondFinancialAccountDim, string>(session, "Code", Detail.CorrespondAccountCode, BinaryOperatorType.Equal);
}
if (!MainAccountCode.Equals(string.Empty))
{
financialAccountDim = util.GetXpoObjectByFieldName <FinancialAccountDim, string>(session, "Code", MainAccountCode, BinaryOperatorType.Equal);
}
FinancialTransactionDim financialTransactionDim = util.GetXpoObjectByFieldName <FinancialTransactionDim, Guid>(session, "RefId", Detail.TransactionId, BinaryOperatorType.Equal);
CurrencyDim currencyDim = util.GetXpoObjectByFieldName <CurrencyDim, string>(session, "Code", Detail.CurrencyCode, BinaryOperatorType.Equal);
if (financialTransactionDim == null)
{
financialTransactionDim = accountingBO.CreateFinancialTransactionDim(session, Detail.TransactionId);
if (financialTransactionDim == null)
{
return;
}
}
if (financialAccountDim == null && !MainAccountCode.Equals(string.Empty))
{
financialAccountDim = accountingBO.CreateFinancialAccountDim(session, MainAccountCode);
}
if (correspondFinancialAccountDim == null && !Detail.CorrespondAccountCode.Equals(string.Empty))
{
correspondFinancialAccountDim = accountingBO.CreateCorrespondFinancialAccountDim(session, Detail.CorrespondAccountCode);
}
if (currencyDim == null && !Detail.CurrencyCode.Equals(string.Empty))
{
currencyDim = accountingBO.CreateCurrencyDim(session, Detail.CurrencyCode);
}
newDetail.CorrespondFinancialAccountDimId = correspondFinancialAccountDim;
newDetail.Credit = Detail.Credit;
newDetail.Debit = Detail.Debit;
newDetail.CurrencyDimId = currencyDim;
newDetail.FinancialAccountDimId = financialAccountDim;
newDetail.FinancialTransactionDimId = financialTransactionDim;
newDetail.FinancialActualPriceSummary_FactId = Fact;
/*2014-02-22 ERP-1417 Duc.Vo INS START*/
if (newDetail.FinancialAccountDimId == null)
{
newDetail.FinancialAccountDimId = defaultFinancialAcc;
}
if (newDetail.CorrespondFinancialAccountDimId == null)
{
newDetail.CorrespondFinancialAccountDimId = defaultCorrespondindAcc;
}
/*2014-02-22 ERP-1417 Duc.Vo INS END*/
newDetail.RowStatus = Constant.ROWSTATUS_ACTIVE;
newDetail.Save();
if (Detail.IsBalanceForward)
{
Fact.BeginCreditBalance = Fact.EndCreditBalance = Detail.Credit;
Fact.BeginDebitBalance = Fact.EndDebitBalance = Detail.Debit;
Fact.CreditSum = 0;
Fact.DebitSum = 0;
}
else
{
Fact.CreditSum = Fact.FinancialActualPriceDetails.Where(i => i.RowStatus == 1 &&
i.Credit > 0 && i.CorrespondFinancialAccountDimId == defaultCorrespondindAcc).Sum(d => d.Credit);
Fact.DebitSum = Fact.FinancialActualPriceDetails.Where(i => i.RowStatus == 1 &&
i.Debit > 0 && i.CorrespondFinancialAccountDimId == defaultCorrespondindAcc).Sum(d => d.Debit);
Fact.EndCreditBalance = Fact.BeginCreditBalance + Fact.CreditSum - Fact.DebitSum;
Fact.EndDebitBalance = Fact.BeginDebitBalance + Fact.DebitSum - Fact.CreditSum;
}
Fact.Save();
}
catch (Exception)
{
return;
}
}
private void load_data()
{
WebModule.Accounting.Report.S12_DN s12_dn = new Report.S12_DN();
#region truyền data
int month = Int32.Parse(this.hS12DN_month.Get("month_id").ToString());
int year = Int32.Parse(this.hS12DN_year.Get("year_id").ToString());
string owner = "QUASAPHARCO";
short rowStatus = Utility.Constant.ROWSTATUS_ACTIVE;
string item = this.hs12dnItem.Get("Item_id").ToString(); //"ACAPELLA S";
string unit = "Thùng";
#endregion
#region display report
s12_dn.xrDate.Text = String.Format("Ngày lập thẻ: {0:dd/MM/yyyy}", DateTime.Now);
s12_dn.xrNumber.Text = "Tờ số: ..............";
s12_dn.xrName.Text = String.Format("Tên, nhãn hiệu, quy cách vật tư: {0}", item);
s12_dn.xrUnit.Text = String.Format("Đơn vị tính: {0}", unit);
s12_dn.xrCode.Text = "Mã số: ................";
#endregion
#region object
MonthDim monthDim = session.FindObject <MonthDim>(CriteriaOperator.Parse(String.Format(
"Name='{0}' AND RowStatus='{1}'", month, rowStatus)));
YearDim yearDim = session.FindObject <YearDim>(CriteriaOperator.Parse(String.Format(
"Name='{0}' AND RowStatus='{1}'", year, rowStatus)));
ItemDim itemDim = session.FindObject <ItemDim>(CriteriaOperator.Parse(String.Format(
"Name='{0}' AND RowStatus='{1}'", item, rowStatus)));
UnitDim unitDim = session.FindObject <UnitDim>(CriteriaOperator.Parse(String.Format(
"Code='{0}' AND RowStatus='{1}'", unit, rowStatus)));
OwnerOrgDim ownerOrgDim = session.FindObject <OwnerOrgDim>(CriteriaOperator.Parse(
String.Format("Code='{0}' AND RowStatus='{1}'", owner, rowStatus)));
int CorrespondFinancialAccountDimId_default = CorrespondFinancialAccountDim.GetDefault(session,
CorrespondFinancialAccountDimEnum.NAAN_DEFAULT).CorrespondFinancialAccountDimId;
#endregion
#region header và dataTable
grid_header();
DataTable datatable = table_pri();
#endregion
#region row
XPCollection <ItemInventoryByArtifact> ItemArtifacts =
new XPCollection <ItemInventoryByArtifact>(session, CriteriaOperator.Parse(
String.Format("RowStatus='{0}' AND CorrespondFinancialAccountDimId='{1}'",
rowStatus, CorrespondFinancialAccountDimId_default)));
DataRow dr;
if (ItemArtifacts.Count != 0)
{
int STT = 1;
foreach (ItemInventoryByArtifact fact in ItemArtifacts)
{
if (itemDim != null && unitDim != null && ownerOrgDim != null)
{
if (fact.FinancialItemInventorySummary_FactId.ItemDimId.Code == itemDim.Code &&
fact.FinancialItemInventorySummary_FactId.UnitDimId.Code == unitDim.Code &&
fact.FinancialItemInventorySummary_FactId.OwnerOrgDimId.Code == ownerOrgDim.Code &&
fact.FinancialItemInventorySummary_FactId.RowStatus == rowStatus &&
fact.FinancialItemInventorySummary_FactId.MonthDimId.MonthDimId == monthDim.MonthDimId &&
fact.FinancialItemInventorySummary_FactId.YearDimId.YearDimId == yearDim.YearDimId)
{
dr = datatable.NewRow();
dr["stt"] = STT++;
//dr["date"] = String.Format("");
if (fact.InventoryCommandDimId.Code.Contains("ICMD"))
{
dr["nhap_dau"] = fact.InventoryCommandDimId.Code;
}
if (fact.InventoryCommandDimId.Code.Contains("OUTCMD"))
{
dr["xuat_dau"] = fact.InventoryCommandDimId.Code;
}
dr["dien_giai"] = fact.InventoryCommandDimId.Description;
dr["date_nhap_xuat"] = String.Format("{0:dd/MM/yyyy}", fact.InventoryCommandDimId.IssueDate);
dr["nhap_cuoi"] = fact.FinancialItemInventorySummary_FactId.DebitItemSum;
dr["xuat_cuoi"] = fact.FinancialItemInventorySummary_FactId.CreditItemSum;
dr["ton_cuoi"] = fact.CurrentBalanceItem;
datatable.Rows.Add(dr);
}
}
}
}
#endregion
#region dòng cộng
dr = datatable.NewRow();
dr["dien_giai"] = "Cộng";
int column_count = datatable.Columns.Count - 2;
int row_count = datatable.Rows.Count - 1;
for (int c = 6; c <= column_count; c++)
{
double sumT = 0;
for (int r = 1; r <= row_count; r++)
{
double tt;
double.TryParse(datatable.Rows[r][c].ToString(), out tt);
sumT += tt;
}
dr[datatable.Columns[c]] = sumT;
}
datatable.Rows.Add(dr);
#endregion
#region out gridview
GridView_S12DN.DataSource = datatable;
GridView_S12DN.DataBind();
#endregion
#region export report
s12_dn.printableCC_S12DN.PrintableComponent =
new PrintableComponentLinkBase()
{
Component = GridViewExporter_S12DN
};
ReportViewer_S12DN.Report = s12_dn;
#endregion
}
