/// <summary> /// 行政费用支出明细表 事业及事业支出明细表 2014/4/20 /// </summary> /// <param name="index"></param> /// <returns></returns> public List<Model_报表类> GetAdministrativeExpenseDetail(int index,int parentID) { List<string> sqlList = new List<string>(); List<Model_报表类> list = new List<Model_报表类>(); string dropSql = "drop table IF EXISTS sbtemp"; sqlList.Add(dropSql); string _sql1 = "create table sbtemp(subject_name text,period int,fee decimal)"; sqlList.Add(_sql1); string _sql2 = "insert into sbtemp select b.subject_name,a.PERIOD,total(a.fee) from (SELECT a.DETAIL,b.PERIOD,total(a.DEBIT - a.CREDIT) AS fee FROM " + DBTablesName.T_VOUCHER_DETAIL + " a LEFT JOIN " + DBTablesName.T_VOUCHER + " b ON a.PARENTID = b.ID where b.REVIEW_MARK=1 and b.delete_mark<>-1 and a.detail like '" + parentID + "%' GROUP BY a.DETAIL,b.PERIOD ) a LEFT JOIN " + DBTablesName.T_SUBJECT + " b ON a.DETAIL = b.subject_id group by a.PERIOD,b.SUBJECT_NAME"; sqlList.Add(_sql2); bool flag = db.BatchOperate(sqlList); if ( flag ) { string _sql3 = "select b.subject_name,a.fee,b.fee from (select subject_name,total(fee) as fee from sbtemp where period<=" + index + " group by subject_name) b left join (select subject_name,fee from sbtemp where period=" + index + ") a on a.subject_name=b.subject_name"; DataTable dt = db.Query(_sql3).Tables[0]; decimal temp = 0m; foreach (DataRow d in dt.Rows) { Model_报表类 m = new Model_报表类(); m.编号 = d[0].ToString(); m.本期数 = d[1].ToString(); m.累计数 = d[2].ToString(); if (m.本期数.Contains(".")) { decimal.TryParse(m.本期数, out temp); m.本期数 = temp.ToString("f2"); } if (m.累计数.Contains(".")) { decimal.TryParse(m.累计数, out temp); m.累计数 = temp.ToString("f2"); } if (m.本期数.Equals("0")) { m.本期数 = ""; } if (m.累计数.Equals("0")) { m.累计数 = ""; } list.Add(m); } } return list; }
/// <summary> /// 获取资产负债表数据 /// </summary> /// <param name="index"></param> /// <returns></returns> public List<Model_报表类> GetBalanceSheet(int index) { List<Model_报表类> list = new List<Model_报表类>(); string sql = "SELECT a.SUBJECT_ID,a.fee,b.fee FROM (SELECT SUBJECT_ID,fee FROM " + DBTablesName.T_FEE + " WHERE PERIOD = " + index + ") a LEFT JOIN (SELECT SUBJECT_ID,total(fee) AS fee FROM " + DBTablesName.T_FEE + " WHERE PERIOD = 0 GROUP BY SUBJECT_ID ) b ON a.SUBJECT_ID = b.SUBJECT_ID " + "WHERE a.SUBJECT_ID IN (SELECT subject_id FROM " + DBTablesName.T_SUBJECT + " WHERE parent_id = '0') "; DataTable dt = db.Query(sql).Tables[0]; decimal temp = 0m; foreach (DataRow d in dt.Rows) { Model_报表类 m = new Model_报表类(); m.编号 = d[0].ToString(); m.年初数 = d[2].ToString(); m.期末数 = d[1].ToString(); if (m.年初数.Contains(".")) { decimal.TryParse(m.年初数, out temp); m.年初数 = temp.ToString("f2"); } if (m.期末数.Contains(".")) { decimal.TryParse(m.期末数, out temp); m.期末数 = temp.ToString("f2"); } if (m.年初数.Equals("0")) { m.年初数 = ""; } if (m.期末数.Equals("0")) { m.期末数 = ""; } list.Add(m); } return list; }
/// <summary> /// 获取收入支出报表数据 - 二级科目数据,20140512 改成支持4级 /// </summary> /// <param name="index"></param> /// <returns></returns> public List<Model_报表类> GetIncomeAndExpensesForTwoSubject(int index,List<string> subList) { string temp = string.Empty; foreach(string s in subList) { temp += ",'" + s + "'"; } List<Model_报表类> list = new List<Model_报表类>(); string sql = "SELECT a.DETAIL,a.fee as fee1,b.fee as fee2 FROM (SELECT substr(a.DETAIL,1,5) as detail,total(a.CREDIT) - total(a.DEBIT) AS fee FROM " + DBTablesName.T_VOUCHER_DETAIL + " a LEFT JOIN " + DBTablesName.T_VOUCHER + " b ON a.PARENTID = b.ID WHERE b.REVIEW_MARK = 1 AND b.PERIOD = " + index + " GROUP BY substr(a.DETAIL, 1, 5)) a LEFT JOIN (SELECT substr(a.DETAIL,1,5) as detail,total(a.CREDIT) - total(a.DEBIT) AS fee FROM " + DBTablesName.T_VOUCHER_DETAIL + " a LEFT JOIN " + DBTablesName.T_VOUCHER + " b ON a.PARENTID = b.ID WHERE b.REVIEW_MARK = 1 AND b.PERIOD <= " + index + " GROUP BY substr(a.DETAIL,1,5)) b ON a.DETAIL = b.DETAIL WHERE substr(a.DETAIL,1,5) IN (" + temp.Substring(1, temp.Length - 1) + ")"; DataTable dt = db.Query(sql).Tables[0]; decimal dd = 0m; foreach (DataRow d in dt.Rows) { Model_报表类 m = new Model_报表类(); m.编号 = d[0].ToString(); if (d[2].ToString().Equals("0")) { m.累计数 = ""; } else { m.累计数 = d[2].ToString().Replace("-",""); } if (d[1].ToString().Equals("0")) { m.本期数 = ""; } else { m.本期数 = d[1].ToString().Replace("-", ""); } if (m.本期数.Contains(".")) { decimal.TryParse(m.本期数, out dd); m.本期数 = dd.ToString("f2"); } if (m.累计数.Contains(".")) { decimal.TryParse(m.累计数, out dd); m.累计数 = dd.ToString("f2"); } list.Add(m); } return list; }
/// <summary> /// 获取收入支出报表数据 /// </summary> /// <param name="index"></param> /// <returns></returns> public List<Model_报表类> GetIncomeAndExpenses(int index, List<string> subList) { string temp = string.Empty; foreach (string s in subList) { temp += ",'" + s + "'"; } List<Model_报表类> list = new List<Model_报表类>(); string sql = "SELECT SUBJECT_ID,debit,credit,fee from " + DBTablesName.T_FEE + " WHERE period=" + index + " and SUBJECT_ID IN (" + temp.Substring(1, temp.Length - 1) + ") "; DataTable dt = db.Query(sql).Tables[0]; decimal dd = 0m; foreach (DataRow d in dt.Rows) { Model_报表类 m = new Model_报表类(); m.编号 = d[0].ToString(); if (d[3].ToString().Equals("0")) { m.累计数 = ""; } else { m.累计数 = d[3].ToString(); } decimal d1 = 0m; decimal d2 = 0m; decimal.TryParse(d[1].ToString(), out d1); decimal.TryParse(d[2].ToString(), out d2); m.本期数 = (d1 - d2).ToString().Replace("-", ""); if (m.本期数.Equals("0")) { m.本期数 = ""; } if (m.本期数.Contains(".")) { decimal.TryParse(m.本期数, out dd); m.本期数 = dd.ToString("f2"); } if (m.累计数.Contains(".")) { decimal.TryParse(m.累计数, out dd); m.累计数 = dd.ToString("f2"); } list.Add(m); } return list; }