private FinancialSalesOrManufactureExpenseSummary_Fact CreateFinancialSalesOrManufactureExpenseSummary( Session session, Guid OwnerOrgId, DateTime IssueDate, bool IsBalanceForward ) { FinancialSalesOrManufactureExpenseSummary_Fact result = new FinancialSalesOrManufactureExpenseSummary_Fact(session); try { ETLAccountingBO accountingBO = new ETLAccountingBO(); DimBO dimBO = new DimBO(); result.MonthDimId = dimBO.GetMonthDim(session, (short)IssueDate.Month); result.YearDimId = dimBO.GetYearDim(session, (short)IssueDate.Year); result.OwnerOrgDimId = dimBO.GetOwnerOrgDim(session, OwnerOrgId); result.RowStatus = Constant.ROWSTATUS_ACTIVE; if (result.MonthDimId == null || result.YearDimId == null || result.OwnerOrgDimId == null) { return(null); } result.Save(); return(result); } catch (Exception) { return(null); } }
public List <string> support_find_row_TK(FinancialSalesOrManufactureExpenseSummary_Fact FinancialFact_General, string TK) { List <string> contain_fc = new List <string>(); FinancialAccountDim fFinancialAccountDim = session.FindObject <FinancialAccountDim>( CriteriaOperator.Parse(String.Format( "Code='{0}'", TK))); 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) { XPCollection <FinancialSalesOrManufactureExpenseDetail> find_fc_throw_detail = new XPCollection <FinancialSalesOrManufactureExpenseDetail>(session, CriteriaOperator.Parse( String.Format("SalesOrManufactureExpenseByGroupId='{0}' AND " + "CorrespondFinancialAccountDimId='128' AND " + "Debit>0 AND " + "RowStatus='1'", SalesByGroup.SalesOrManufactureExpenseByGroupId ))); if (find_fc_throw_detail.Count != 0) { foreach (FinancialSalesOrManufactureExpenseDetail each_detail in find_fc_throw_detail) { if (!contain_fc.Contains(each_detail.FinancialAccountDimId.Code)) { contain_fc.Add(each_detail.FinancialAccountDimId.Code); // nếu tồn tại con thì lấy cha if (!contain_fc.Contains(TK)) { contain_fc.Add(TK); } } } } } } contain_fc.Sort(); return(contain_fc); }
private SalesOrManufactureExpenseByGroup CreateSalesOrManufactureExpenseByGroup(Session session, int summaryFactId, string HighestAccountCode) { SalesOrManufactureExpenseByGroup result = new SalesOrManufactureExpenseByGroup(session); try { Util util = new Util(); FinancialAccountDim GroupAccountDim = null; FinancialSalesOrManufactureExpenseSummary_Fact summary = session.GetObjectByKey <FinancialSalesOrManufactureExpenseSummary_Fact>(summaryFactId); if (summary == null) { return(null); } if (!HighestAccountCode.Equals(string.Empty)) { GroupAccountDim = util.GetXpoObjectByFieldName <FinancialAccountDim, string>( session, "Code", HighestAccountCode, BinaryOperatorType.Equal); } if (GroupAccountDim == null && !HighestAccountCode.Equals(string.Empty)) { GroupAccountDim = accountingBO.CreateFinancialAccountDim(session, HighestAccountCode); } if (GroupAccountDim == null) { return(null); } result.RowStatus = Constant.ROWSTATUS_ACTIVE; result.FinancialSalesOrManufactureExpenseSummary_FactId = summary; result.FinancialAccountDimId = GroupAccountDim; result.Save(); return(result); } catch (Exception) { return(null); } }
public void support_list_header(string TK_fFinancialAccountDim, FinancialSalesOrManufactureExpenseSummary_Fact FinancialFact_General, int CorrespondFinancialAccountDimId_default, List <string> list_header ) { FinancialAccountDim fFinancialAccountDim = session.FindObject <FinancialAccountDim>( CriteriaOperator.Parse(String.Format( "Code='{0}'", TK_fFinancialAccountDim))); 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) { XPCollection <FinancialSalesOrManufactureExpenseDetail> collect_Detail = new XPCollection <FinancialSalesOrManufactureExpenseDetail>(session, CriteriaOperator.Parse( String.Format("SalesOrManufactureExpenseByGroupId='{0}' AND " + "CorrespondFinancialAccountDimId!='{1}' AND " + "Credit>0 AND " + "RowStatus='1'", SalesByGroup.SalesOrManufactureExpenseByGroupId, CorrespondFinancialAccountDimId_default ))); if (collect_Detail.Count != 0) { foreach (FinancialSalesOrManufactureExpenseDetail each_Detail in collect_Detail) { if (!list_header.Contains(each_Detail.CorrespondFinancialAccountDimId.Code)) { list_header.Add(each_Detail.CorrespondFinancialAccountDimId.Code); } } } } } }
private FinancialSalesOrManufactureExpenseSummary_Fact GetFinancialSalesOrManufactureExpenseSummary( Session session, Guid OwnerOrgId, DateTime IssueDate ) { FinancialSalesOrManufactureExpenseSummary_Fact result = null; try { Util util = new Util(); OwnerOrgDim ownerOrgDim = util.GetXpoObjectByFieldName <OwnerOrgDim, Guid>(session, "RefId", OwnerOrgId, BinaryOperatorType.Equal); MonthDim monthDim = util.GetXpoObjectByFieldName <MonthDim, string>(session, "Name", IssueDate.Month.ToString(), BinaryOperatorType.Equal); YearDim yearDim = util.GetXpoObjectByFieldName <YearDim, string>(session, "Name", IssueDate.Year.ToString(), BinaryOperatorType.Equal); if (ownerOrgDim == null || monthDim == null || yearDim == null) { return(null); } else { CriteriaOperator criteria_RowStatus = new BinaryOperator("RowStatus", Constant.ROWSTATUS_ACTIVE, BinaryOperatorType.GreaterOrEqual); CriteriaOperator criteria_OwnerOrg = new BinaryOperator("OwnerOrgDimId", ownerOrgDim, BinaryOperatorType.Equal); CriteriaOperator criteria_Month = new BinaryOperator("MonthDimId", monthDim, BinaryOperatorType.Equal); CriteriaOperator criteria_Year = new BinaryOperator("YearDimId", yearDim, BinaryOperatorType.Equal); CriteriaOperator criteria = CriteriaOperator.And(criteria_RowStatus, criteria_OwnerOrg, criteria_Month, criteria_Year); FinancialSalesOrManufactureExpenseSummary_Fact fact = session.FindObject <FinancialSalesOrManufactureExpenseSummary_Fact>(criteria); if (fact == null) { return(null); } result = fact; } } catch (Exception) { return(result); } return(result); }
//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); }
private SalesOrManufactureExpenseByGroup GetSalesOrManufactureExpenseByGroup(Session session, int summaryFactId, string HighestAccountCode) { try { Util util = new Util(); FinancialSalesOrManufactureExpenseSummary_Fact summary = session.GetObjectByKey <FinancialSalesOrManufactureExpenseSummary_Fact>(summaryFactId); if (summary == null) { return(null); } CriteriaOperator criteria_0 = new BinaryOperator("RowStatus", Constant.ROWSTATUS_ACTIVE, BinaryOperatorType.GreaterOrEqual); CriteriaOperator criteria_1 = new BinaryOperator("FinancialSalesOrManufactureExpenseSummary_FactId", summary, BinaryOperatorType.Equal); CriteriaOperator criteria_2 = CriteriaOperator.Parse("not(IsNull(FinancialAccountDimId))"); CriteriaOperator criteria_3 = new BinaryOperator("FinancialAccountDimId.Code", HighestAccountCode, BinaryOperatorType.Equal); CriteriaOperator criteria = CriteriaOperator.And(criteria_0, criteria_1, criteria_2, criteria_3); return(session.FindObject <SalesOrManufactureExpenseByGroup>(criteria)); } catch (Exception) { return(null); } }
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 void CreateFinancialSalesOrManufacturerExpenseDetail( Session session, ETL_SalesOrManufacturerExpenseDetail Detail, string MainAccountCode, string HighestAccountCode) { try { if (Detail == null || MainAccountCode.Equals(string.Empty) || Detail.OwnerOrgId.Equals(Guid.Empty) || Detail.IssueDate == null) { return; } #region prepare Summary header data Util util = new Util(); ETLAccountingBO accountingBO = new ETLAccountingBO(); FinancialSalesOrManufactureExpenseSummary_Fact summary = GetFinancialSalesOrManufactureExpenseSummary( session, Detail.OwnerOrgId, Detail.IssueDate); FinancialSalesOrManufactureExpenseDetail newDetail = new FinancialSalesOrManufactureExpenseDetail(session); if (summary == null) { summary = CreateFinancialSalesOrManufactureExpenseSummary( session, Detail.OwnerOrgId, Detail.IssueDate, Detail.IsBalanceForward); if (summary == null) { return; } } #endregion #region prepare group data CorrespondFinancialAccountDim correspondFinancialAccountDim = null; FinancialAccountDim financialAccountDim = null; FinancialAccountDim GroupAccountDim = null; if (!HighestAccountCode.Equals(string.Empty)) { GroupAccountDim = util.GetXpoObjectByFieldName <FinancialAccountDim, string>( session, "Code", HighestAccountCode, BinaryOperatorType.Equal); } if (!MainAccountCode.Equals(string.Empty)) { financialAccountDim = util.GetXpoObjectByFieldName <FinancialAccountDim, string>( session, "Code", MainAccountCode, BinaryOperatorType.Equal); } if (!Detail.CorrespondAccountCode.Equals(string.Empty)) { correspondFinancialAccountDim = util.GetXpoObjectByFieldName <CorrespondFinancialAccountDim, string>( session, "Code", Detail.CorrespondAccountCode, BinaryOperatorType.Equal); } if (GroupAccountDim == null && !HighestAccountCode.Equals(string.Empty)) { GroupAccountDim = accountingBO.CreateFinancialAccountDim(session, HighestAccountCode); } 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); } SalesOrManufactureExpenseByGroup group = GetSalesOrManufactureExpenseByGroup( session, summary.FinancialSalesOrManufactureExpenseSummary_FactId, HighestAccountCode); if (group == null) { group = CreateSalesOrManufactureExpenseByGroup( session, summary.FinancialSalesOrManufactureExpenseSummary_FactId, HighestAccountCode); if (group == null) { return; } } #endregion #region prepare Detail CurrencyDim currencyDim = util.GetXpoObjectByFieldName <CurrencyDim, string>( session, "Code", Detail.CurrencyCode, BinaryOperatorType.Equal); if (currencyDim == null && !Detail.CurrencyCode.Equals(string.Empty)) { currencyDim = accountingBO.CreateCurrencyDim(session, Detail.CurrencyCode); } FinancialTransactionDim financialTransactionDim = util.GetXpoObjectByFieldName <FinancialTransactionDim, Guid>( session, "RefId", Detail.TransactionId, BinaryOperatorType.Equal); if (financialTransactionDim == null) { financialTransactionDim = accountingBO.CreateFinancialTransactionDim(session, Detail.TransactionId); if (financialTransactionDim == null) { return; } } newDetail.Credit = Detail.Credit; newDetail.Debit = Detail.Debit; newDetail.CurrencyDimId = currencyDim; newDetail.FinancialTransactionDimId = financialTransactionDim; newDetail.FinancialAccountDimId = financialAccountDim; newDetail.CorrespondFinancialAccountDimId = correspondFinancialAccountDim; newDetail.SalesOrManufactureExpenseByGroupId = group; CorrespondFinancialAccountDim defaultCorrespondindAcc = CorrespondFinancialAccountDim.GetDefault(session, CorrespondFinancialAccountDimEnum.NAAN_DEFAULT); FinancialAccountDim defaultFinancialAcc = FinancialAccountDim.GetDefault(session, FinancialAccountDimEnum.NAAN_DEFAULT); if (newDetail.FinancialAccountDimId == null) { newDetail.FinancialAccountDimId = defaultFinancialAcc; } if (newDetail.CorrespondFinancialAccountDimId == null) { newDetail.CorrespondFinancialAccountDimId = defaultCorrespondindAcc; } newDetail.RowStatus = Constant.ROWSTATUS_ACTIVE; newDetail.Save(); #endregion if (Detail.IsBalanceForward) { group.SumExpense = Detail.Credit; } else { group.SumExpense = group.FinancialSalesOrManufactureExpenseDetails.Where(i => i.RowStatus >= 1 && i.Debit > 0). Sum(r => r.Debit); group.Save(); } group.Save(); summary.Save(); } catch (Exception) { return; } }