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);
}
