public virtual void Visit(DimensionMember dimensionMember) { List <T2> ancestors = memberAncestors.Where(x => x.AncestorId == dimensionMember.Member.Id).ToList(); List <GLRecord> gLRecords = data.Where(x => ancestors.Select(z => z.Id).Contains(x.GLMembers.Get(SegmentID))).ToList(); if (gLRecords != null && gLRecords.Count > 0) { foreach (GLRecord gLRecord in gLRecords) { string newUniqueKey = Utility.GetGLUniqueKey(gLRecord, VisitorType.Segment, dimensionMember.Member.Id, SegmentID); GLRecord objGLRecord = data.FirstOrDefault(x => x.UniqueKey == newUniqueKey); if (objGLRecord == null) { objGLRecord = gLRecord.CloneObjectSerializable <GLRecord>(); foreach (KeyValuePair <int, GLRecordAmount> gLRecordAmount in objGLRecord.Values.Amounts) { gLRecordAmount.Value.Mtd = 0; gLRecordAmount.Value.Qtd = 0; gLRecordAmount.Value.Ytd = 0; } objGLRecord.GLMembers.AddOrUpdate(SegmentID, dimensionMember.Member.Id); data.Add(objGLRecord); } T2 ancestor = ancestors.FirstOrDefault(x => x.Id == gLRecord.GLMembers.Get(SegmentID)); foreach (KeyValuePair <int, GLRecordAmount> cell in gLRecord.Values.Amounts) { GLRecordAmount existingRecordAmount = objGLRecord.Values.Amounts.FirstOrDefault(x => x.Key == cell.Key).Value; GLRecordAmount gLRecordAmount = new GLRecordAmount(existingRecordAmount.Mtd + (cell.Value.Mtd * ancestor.Operator), existingRecordAmount.Qtd + (cell.Value.Qtd * ancestor.Operator), existingRecordAmount.Ytd + (cell.Value.Ytd * ancestor.Operator)); objGLRecord.Values.AddOrUpdate(cell.Key, null, (updated) => { return(gLRecordAmount); }); } } } }
public GLRecord getDefaultARAccount() { SQL = "select top 1 * from gl where elt_account_number = " + elt_account_number + " and gl_account_type= '" + Account.ACCOUNT_RECEIVABLE + "'"; DataTable dt = new DataTable(); SqlDataAdapter ad = new SqlDataAdapter(SQL, Con); GeneralUtility gUtil = new GeneralUtility(); GLRecord gRec = new GLRecord(); ad.Fill(dt); try { gUtil.removeNull(ref dt); gRec.Control_no = dt.Rows[0]["control_no"].ToString(); gRec.Gl_account_balance = Decimal.Parse(dt.Rows[0]["gl_account_balance"].ToString()); gRec.Gl_account_cdate = dt.Rows[0]["gl_account_cdate"].ToString(); gRec.Gl_account_desc = dt.Rows[0]["gl_account_desc"].ToString(); gRec.Gl_account_number = Int32.Parse((dt.Rows[0]["gl_account_number"].ToString())); gRec.Gl_account_status = dt.Rows[0]["gl_account_status"].ToString(); gRec.Gl_account_type = dt.Rows[0]["gl_account_type"].ToString(); gRec.Gl_begin_balance = Decimal.Parse(dt.Rows[0]["gl_begin_balance"].ToString()); gRec.Gl_default = dt.Rows[0]["gl_default"].ToString(); gRec.Gl_last_modified = dt.Rows[0]["gl_last_modified"].ToString(); gRec.Gl_master_type = dt.Rows[0]["gl_master_type"].ToString(); } catch (Exception ex) { throw ex; } return(gRec); }
public GLRecord getGLAcct(int gl_account_number) { SQL = "select isnull(control_no,0) as control_no ,gl_account_balance,gl_account_cdate,gl_account_desc,gl_account_number,gl_account_status,gl_account_type,gl_begin_balance,gl_default,gl_last_modified,gl_master_type from gl where elt_account_number = " + elt_account_number + " and gl_account_number=" + gl_account_number; DataTable dt = new DataTable(); SqlDataAdapter ad = new SqlDataAdapter(SQL, Con); GeneralUtility gUtil = new GeneralUtility(); GLRecord gRec = new GLRecord(); ad.Fill(dt); try { gUtil.removeNull(ref dt); gRec.Control_no = dt.Rows[0]["control_no"].ToString(); gRec.Gl_account_balance = Decimal.Parse(dt.Rows[0]["gl_account_balance"].ToString()); gRec.Gl_account_cdate = dt.Rows[0]["gl_account_cdate"].ToString(); gRec.Gl_account_desc = dt.Rows[0]["gl_account_desc"].ToString(); gRec.Gl_account_number = Int32.Parse((dt.Rows[0]["gl_account_number"].ToString())); gRec.Gl_account_status = dt.Rows[0]["gl_account_status"].ToString(); gRec.Gl_account_type = dt.Rows[0]["gl_account_type"].ToString(); gRec.Gl_begin_balance = Decimal.Parse(dt.Rows[0]["gl_begin_balance"].ToString()); gRec.Gl_default = dt.Rows[0]["gl_default"].ToString(); gRec.Gl_last_modified = dt.Rows[0]["gl_last_modified"].ToString(); gRec.Gl_master_type = dt.Rows[0]["gl_master_type"].ToString(); } catch (Exception ex) { throw ex; } return(gRec); }
public static string GetGLUniqueKey(GLRecord objGLRecord, VisitorType visitorType, int newMemberId, int segmentId = 0) { switch (visitorType) { case VisitorType.Segment: Dictionary <int, int> SegmentValues = objGLRecord.GLMembers.SegmentValues.ToDictionary(x => x.Key, x => x.Value); if (SegmentValues != null && segmentId != 0) { if (SegmentValues.ContainsKey(segmentId)) { SegmentValues[segmentId] = newMemberId; } return(string.Format("{0}{1}{2}", objGLRecord.ScenarioId, objGLRecord.ReportingId, string.Concat(SegmentValues.OrderBy(o => o.Key).Select(s => s.Value.ToString())))); } return(objGLRecord.UniqueKey); default: return(objGLRecord.UniqueKey); } }
public GLRecord createGLAccount(int gl_account_number, string gl_account_desc, string gl_master_type, string gl_account_type) { GLRecord gRec = new GLRecord(); Cmd = new SqlCommand(); Cmd.Connection = Con; Con.Open(); SqlTransaction trans = Con.BeginTransaction(); Cmd.Transaction = trans; SQL = "INSERT INTO gl (elt_account_number, gl_account_number, gl_account_desc, gl_master_type, gl_account_type, gl_account_status, gl_account_cdate, gl_last_modified) VALUES (" + elt_account_number + "," + gl_account_number + "," + "'" + gl_account_desc + "'" + "," + "'" + gl_master_type + "'" + "," + "'" + gl_account_type + "'" + "," + "'A'" + "," + "'" + DateTime.Today.ToShortDateString() + "'" + "," + "'" + DateTime.Today.ToShortDateString() + "'" + ")"; try { Cmd.CommandText = SQL; Cmd.ExecuteNonQuery(); trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } finally { Con.Close(); } gRec.Gl_account_type = gl_account_type; gRec.Gl_master_type = gl_master_type; gRec.Gl_account_desc = gl_account_desc; gRec.Gl_account_number = gl_account_number; gRec.Gl_account_status = "A"; return(gRec); }
public virtual void Visit(DimensionWholePartHierarchy dimensionWholePartHierarchy) { int depth = dimensionWholePartHierarchy.Members.Min(z => z.Level); ExtendedHierarchyMember rootExtendedHierarchyMember = dimensionWholePartHierarchy.Members.FirstOrDefault(x => x.Level == depth); List <ExtendedHierarchyMember> childExtendedHierarchyMembers = dimensionWholePartHierarchy.Members.Where(x => x.Level == depth + 1).ToList(); List <GLRecord> gLRecords = data.Where(x => childExtendedHierarchyMembers.Select(z => z.Id).Contains(x.GLMembers.Get(SegmentID))).ToList(); Dictionary <int, int> childrenOperators = new Dictionary <int, int>(); childExtendedHierarchyMembers.ForEach(x => { childrenOperators.Add(x.Id, Utility.GetMemberOperator(x.Operator)); }); if (gLRecords != null && gLRecords.Count > 0) { foreach (GLRecord gLRecord in gLRecords) { string newUniqueKey = Utility.GetGLUniqueKey(gLRecord, VisitorType.Segment, rootExtendedHierarchyMember.Id, SegmentID); GLRecord objGLRecord = data.FirstOrDefault(x => x.UniqueKey == newUniqueKey); if (objGLRecord == null) { objGLRecord = gLRecord.CloneObjectSerializable <GLRecord>(); foreach (KeyValuePair <int, GLRecordAmount> gLRecordAmount in objGLRecord.Values.Amounts) { gLRecordAmount.Value.Mtd = 0; gLRecordAmount.Value.Qtd = 0; gLRecordAmount.Value.Ytd = 0; } objGLRecord.GLMembers.AddOrUpdate(SegmentID, rootExtendedHierarchyMember.Id); data.Add(objGLRecord); } int Operator = childrenOperators[gLRecord.GLMembers.Get(SegmentID)]; foreach (KeyValuePair <int, GLRecordAmount> cell in gLRecord.Values.Amounts) { GLRecordAmount existingRecordAmount = objGLRecord.Values.Amounts.FirstOrDefault(x => x.Key == cell.Key).Value; GLRecordAmount gLRecordAmount = new GLRecordAmount(existingRecordAmount.Mtd + (cell.Value.Mtd * Operator), existingRecordAmount.Qtd + (cell.Value.Qtd * Operator), existingRecordAmount.Ytd + (cell.Value.Ytd * Operator)); objGLRecord.Values.AddOrUpdate(cell.Key, null, (updated) => { return(gLRecordAmount); }); } } } }
public GLRecord getDefaultRetainedEarningAcct() { SQL = "select top 1 * from gl where elt_account_number = " + elt_account_number + " and gl_account_type= '" + Account.EQUITY_RETAINED_EARNINGS + "'"; DataTable dt = new DataTable(); SqlDataAdapter ad = new SqlDataAdapter(SQL, Con); GeneralUtility gUtil = new GeneralUtility(); GLRecord gRec = new GLRecord(); ad.Fill(dt); if (dt.Rows.Count > 0) { try { gUtil.removeNull(ref dt); gRec.Control_no = dt.Rows[0]["control_no"].ToString(); gRec.Gl_account_balance = Decimal.Parse(dt.Rows[0]["gl_account_balance"].ToString()); gRec.Gl_account_cdate = dt.Rows[0]["gl_account_cdate"].ToString(); gRec.Gl_account_desc = dt.Rows[0]["gl_account_desc"].ToString(); gRec.Gl_account_number = Int32.Parse((dt.Rows[0]["gl_account_number"].ToString())); gRec.Gl_account_status = dt.Rows[0]["gl_account_status"].ToString(); gRec.Gl_account_type = dt.Rows[0]["gl_account_type"].ToString(); gRec.Gl_begin_balance = Decimal.Parse(dt.Rows[0]["gl_begin_balance"].ToString()); gRec.Gl_default = dt.Rows[0]["gl_default"].ToString(); gRec.Gl_last_modified = dt.Rows[0]["gl_last_modified"].ToString(); gRec.Gl_master_type = dt.Rows[0]["gl_master_type"].ToString(); } catch (Exception ex) { throw ex; } } else { int gl_acct = 0; gl_acct = getNewGLAccount("EQUITY"); gRec = createGLAccount(gl_acct, "Retained Earnings", "EQUITY", Account.EQUITY_RETAINED_EARNINGS); } return(gRec); }
public ArrayList getGLAcctList(string gl_account_type) { SQL = "select isnull(control_no,0) as control_no ,gl_account_balance,gl_account_cdate,gl_account_desc,gl_account_number,gl_account_status,gl_account_type,gl_begin_balance,gl_default,gl_last_modified,gl_master_type from gl where elt_account_number = " + elt_account_number + " and gl_account_type='" + gl_account_type + "'"; DataTable dt = new DataTable(); SqlDataAdapter ad = new SqlDataAdapter(SQL, Con); GeneralUtility gUtil = new GeneralUtility(); ArrayList gRecList = new ArrayList(); ad.Fill(dt); try { gUtil.removeNull(ref dt); for (int i = 0; i < dt.Rows.Count; i++) { GLRecord gRec = new GLRecord(); gRec.Control_no = dt.Rows[i]["control_no"].ToString(); gRec.Gl_account_balance = Decimal.Parse(dt.Rows[i]["gl_account_balance"].ToString()); gRec.Gl_account_number = Int32.Parse((dt.Rows[i]["gl_account_number"].ToString())); if (gl_account_type == Account.BANK) { gRec.Gl_account_balance = getBankBalance(gRec.Gl_account_number); } gRec.Gl_account_cdate = dt.Rows[i]["gl_account_cdate"].ToString(); gRec.Gl_account_desc = dt.Rows[i]["gl_account_desc"].ToString(); gRec.Gl_account_status = dt.Rows[i]["gl_account_status"].ToString(); gRec.Gl_account_type = dt.Rows[i]["gl_account_type"].ToString(); gRec.Gl_begin_balance = Decimal.Parse(dt.Rows[i]["gl_begin_balance"].ToString()); gRec.Gl_default = dt.Rows[i]["gl_default"].ToString(); gRec.Gl_last_modified = dt.Rows[i]["gl_last_modified"].ToString(); gRec.Gl_master_type = dt.Rows[i]["gl_master_type"].ToString(); gRecList.Add(gRec); } } catch (Exception ex) { throw ex; } return(gRecList); }
public GLRecord getDefaultBankServiceChargeAcct() { SQL = "select top 1 * from gl where elt_account_number = " + elt_account_number + " and gl_account_type= '" + Account.OTHER_EXPENSE + "' AND gl_account_desc='BANK SERVICE'"; DataTable dt = new DataTable(); SqlDataAdapter ad = new SqlDataAdapter(SQL, Con); GeneralUtility gUtil = new GeneralUtility(); GLRecord gRec = new GLRecord(); ad.Fill(dt); if (dt.Rows.Count > 0) { try { gUtil.removeNull(ref dt); gRec.Control_no = dt.Rows[0]["control_no"].ToString(); gRec.Gl_account_balance = Decimal.Parse(dt.Rows[0]["gl_account_balance"].ToString()); gRec.Gl_account_cdate = dt.Rows[0]["gl_account_cdate"].ToString(); gRec.Gl_account_desc = dt.Rows[0]["gl_account_desc"].ToString(); gRec.Gl_account_number = Int32.Parse((dt.Rows[0]["gl_account_number"].ToString())); gRec.Gl_account_status = dt.Rows[0]["gl_account_status"].ToString(); gRec.Gl_account_type = dt.Rows[0]["gl_account_type"].ToString(); gRec.Gl_begin_balance = Decimal.Parse(dt.Rows[0]["gl_begin_balance"].ToString()); gRec.Gl_default = dt.Rows[0]["gl_default"].ToString(); gRec.Gl_last_modified = dt.Rows[0]["gl_last_modified"].ToString(); gRec.Gl_master_type = dt.Rows[0]["gl_master_type"].ToString(); } catch (Exception ex) { throw ex; } } else { int gl_acct = 0; gl_acct = getNewGLAccount("EXPENSE"); gRec = createGLAccount(gl_acct, "BANK SERVICE", "EXPENSE", Account.OTHER_EXPENSE); } return(gRec); }
public string setDefaultOFCostAcct() { string return_val = "false"; //--------- SQL = "select isnull(max(item_no),0) as item_no from item_cost where elt_account_number = " + elt_account_number; DataTable dt = new DataTable(); SqlDataAdapter ad2 = new SqlDataAdapter(SQL, Con); ad2.Fill(dt); int next_no = Int32.Parse(dt.Rows[0]["item_no"].ToString()); next_no += 1; dt.Clear(); SQL = "select top 1 * from gl where elt_account_number = " + elt_account_number + " and gl_account_type= '" + Account.COST_OF_SALES + "' and gl_master_type='EXPENSE' and gl_account_desc='Default Ocean Freight Cost'"; //----------------- SqlDataAdapter ad = new SqlDataAdapter(SQL, Con); GeneralUtility gUtil = new GeneralUtility(); GLRecord gRec = new GLRecord(); ad.Fill(dt); int gl_acct = 0; if (dt.Rows.Count == 0) { gl_acct = getNewGLAccount("EXPENSE"); gRec = createGLAccount(gl_acct, "Default Ocean Freight Cost", "EXPENSE", Account.COST_OF_SALES); } else { gl_acct = Int32.Parse(dt.Rows[0]["gl_account_number"].ToString()); } Cmd = new SqlCommand(); Cmd.Connection = Con; Con.Open(); SqlTransaction trans = Con.BeginTransaction(); Cmd.Transaction = trans; try { SQL = "INSERT INTO [item_cost] "; SQL += "( elt_account_number, "; SQL += "item_no,"; SQL += "item_name,"; SQL += "item_type,"; SQL += "item_desc,"; SQL += "unit_price,"; SQL += "account_expense) "; SQL += "VALUES"; SQL += "('" + elt_account_number; SQL += "','" + next_no; SQL += "','" + "OF"; SQL += "','" + "Ocean Freight"; SQL += "','" + "OCEAN FREIGHT"; SQL += "','" + "0"; SQL += "','" + gl_acct; SQL += "')"; Cmd.CommandText = SQL; Cmd.ExecuteNonQuery(); SQL = "UPDATE [user_profile] "; SQL += "set default_ocean_cost_item= '" + next_no + "'"; SQL += "where elt_account_number = " + elt_account_number; Cmd.CommandText = SQL; Cmd.ExecuteNonQuery(); trans.Commit(); return_val = "true"; } catch (Exception ex) { trans.Rollback(); throw ex; } finally { Con.Close(); } return(return_val); }