private Model_科目明细账 GetModel_Subject(decimal a,decimal b,decimal c,bool flag)
{
Model_科目明细账 m = new Model_科目明细账();
List<string> _list = new List<string>();
_list = ut.Turn(a.ToString(), 12, flag);
m.借方金额1 = _list[0];
m.借方金额2 = _list[1];
m.借方金额3 = _list[2];
m.借方金额4 = _list[3];
m.借方金额5 = _list[4];
m.借方金额6 = _list[5];
m.借方金额7 = _list[6];
m.借方金额8 = _list[7];
m.借方金额9 = _list[8];
m.借方金额10 = _list[9];
m.借方金额11 = _list[10];
m.借方金额12 = _list[11];
_list.Clear();
_list = ut.Turn(b.ToString(), 12, flag);
m.贷方金额1 = _list[0];
m.贷方金额2 = _list[1];
m.贷方金额3 = _list[2];
m.贷方金额4 = _list[3];
m.贷方金额5 = _list[4];
m.贷方金额6 = _list[5];
m.贷方金额7 = _list[6];
m.贷方金额8 = _list[7];
m.贷方金额9 = _list[8];
m.贷方金额10 = _list[9];
m.贷方金额11 = _list[10];
m.贷方金额12 = _list[11];
_list.Clear();
_list = ut.Turn(c.ToString(), 12);
m.余额1 = _list[0];
m.余额2 = _list[1];
m.余额3 = _list[2];
m.余额4 = _list[3];
m.余额5 = _list[4];
m.余额6 = _list[5];
m.余额7 = _list[6];
m.余额8 = _list[7];
m.余额9 = _list[8];
m.余额10 = _list[9];
m.余额11 = _list[10];
m.余额12 = _list[11];
return m;
}
/// <summary>
/// 科目明细账查询方法
/// </summary>
/// <param name="subject_id">一级科目</param>
/// <param name="detail">二级科目</param>
/// <reut.Turns></reut.Turns>
public List<Model_科目明细账> GetSubjectDetail(string subject_id,string detail)
{
List<Model_科目明细账> list = new List<Model_科目明细账>();
List<string> _list = new List<string>();
bool flag = true;
subject_id = subject_id.Split('\t')[0];
detail = detail.Split('\t')[0];
string sql = "select strftime(b.op_time),a.voucher_no,a.abstract,a.debit,a.credit from "
+ DBTablesName.T_VOUCHER_DETAIL
+ " a left join "
+ DBTablesName.T_VOUCHER
+ " b on a.parentid=b.id where a.subject_id='"
+ subject_id
+ "'" + " and b.delete_mark=0 and b.REVIEW_MARK=1 and a.detail='"
+ detail + "' order by b.op_time";
//查年初数
string sql2 = "select case when b.borrow_mark=1 then '借' else '贷' end,a.fee*b.borrow_mark from "
+ DBTablesName.T_YEAR_FEE + " a left join " + DBTablesName.T_SUBJECT
+ " b on a.subject_id=b.subject_id where "
+ " a.subject_id='" + detail + "' and a.bookid='" + CommonInfo.账薄号 + "'";
DataRow dr = db.Query(sql2).Tables[0].Rows[0];
Model_科目明细账 firstRow = new Model_科目明细账();
firstRow.摘要 = "承上年结余";
firstRow.借或贷 = dr[0].ToString();
firstRow.余额 = dr[1].ToString();
_list = ut.Turn(firstRow.余额, 12);
firstRow.余额1 = _list[0];
firstRow.余额2 = _list[1];
firstRow.余额3 = _list[2];
firstRow.余额4 = _list[3];
firstRow.余额5 = _list[4];
firstRow.余额6 = _list[5];
firstRow.余额7 = _list[6];
firstRow.余额8 = _list[7];
firstRow.余额9 = _list[8];
firstRow.余额10 = _list[9];
firstRow.余额11 = _list[10];
firstRow.余额12 = _list[11];
list.Add(firstRow);
decimal yearfee = 0;
decimal.TryParse(firstRow.余额,out yearfee);
DataTable dt = db.Query(sql).Tables[0];
String MonthLastValue = "01";
//月合计
decimal MonthDebit = 0;
decimal MonthCredit = 0;
//月累计
decimal YearDebit = 0;
decimal YearCredit = 0;
if (dt.Rows.Count > 0)
{
bool isHasData = false;//这个月是否有数据，有才打印合计累计
foreach (DataRow d in dt.Rows)
{
Model_科目明细账 m = new Model_科目明细账();
string date = d[0].ToString().Split(' ')[0];
m.年 = date.Split('-')[0];
m.月 = date.Split('-')[1];
m.日 = date.Split('-')[2];
m.号数 = d[1].ToString();
m.摘要 = d[2].ToString();
m.借方金额 = d[3].ToString();
m.贷方金额 = d[4].ToString();
if (m.借方金额.Contains("-") || m.贷方金额.Contains("-"))
{
m.红字标记 = 1;
flag = false;
}
else
{
flag = true;
}
yearfee -= Convert.ToDecimal(m.贷方金额) - Convert.ToDecimal(m.借方金额);
string tempvalue = yearfee.ToString();
_list.Clear();
_list = ut.Turn(m.贷方金额, 12);
m.贷方金额1 = _list[0];
m.贷方金额2 = _list[1];
m.贷方金额3 = _list[2];
m.贷方金额4 = _list[3];
m.贷方金额5 = _list[4];
m.贷方金额6 = _list[5];
m.贷方金额7 = _list[6];
m.贷方金额8 = _list[7];
m.贷方金额9 = _list[8];
m.贷方金额10 = _list[9];
m.贷方金额11 = _list[10];
m.贷方金额12 = _list[11];
_list.Clear();
_list = ut.Turn(m.借方金额, 12, flag);
m.借方金额1 = _list[0];
m.借方金额2 = _list[1];
m.借方金额3 = _list[2];
m.借方金额4 = _list[3];
m.借方金额5 = _list[4];
m.借方金额6 = _list[5];
m.借方金额7 = _list[6];
m.借方金额8 = _list[7];
m.借方金额9 = _list[8];
m.借方金额10 = _list[9];
m.借方金额11 = _list[10];
m.借方金额12 = _list[11];
_list.Clear();
_list = ut.Turn(tempvalue, 12);
m.余额1 = _list[0];
m.余额2 = _list[1];
m.余额3 = _list[2];
m.余额4 = _list[3];
m.余额5 = _list[4];
m.余额6 = _list[5];
m.余额7 = _list[6];
m.余额8 = _list[7];
m.余额9 = _list[8];
m.余额10 = _list[9];
m.余额11 = _list[10];
m.余额12 = _list[11];
_list.Clear();
decimal dValue = 0;
if (MonthLastValue.Equals(m.月))
{
decimal.TryParse(m.借方金额, out dValue);
MonthDebit += dValue;
YearDebit += dValue;
decimal.TryParse(m.贷方金额, out dValue);
MonthCredit += dValue;
YearCredit += dValue;
isHasData = true;
}
else
{
if (isHasData)
{
Model_科目明细账 mm = new Model_科目明细账();
mm = GetModel_Subject(MonthDebit, MonthCredit, MonthDebit - MonthCredit + decimal.Parse(firstRow.余额),flag);
mm.摘要 = "本月合计";
mm.借或贷 = GetMark(yearfee);
list.Add(mm);
if (!MonthLastValue.Equals("01"))
{
Model_科目明细账 mmm = new Model_科目明细账();
mmm = GetModel_Subject(YearDebit, YearCredit, MonthDebit - MonthCredit + decimal.Parse(firstRow.余额),flag);
mmm.摘要 = "本月累计";
mmm.借或贷 = GetMark(yearfee);
list.Add(mmm);
}
}
MonthDebit = 0;
MonthCredit = 0;
decimal.TryParse(m.借方金额, out dValue);
MonthDebit += dValue;
YearDebit += dValue;
decimal.TryParse(m.贷方金额, out dValue);
MonthCredit += dValue;
YearCredit += dValue;
}
MonthLastValue = m.月;
list.Add(m);
}
Model_科目明细账 mlast = new Model_科目明细账();
mlast = GetModel_Subject(MonthDebit, MonthCredit, yearfee,flag);
mlast.摘要 = "本月合计";
mlast.借或贷 = GetMark(yearfee);
list.Add(mlast);
if (!MonthLastValue.Equals("01"))
{
Model_科目明细账 mmm = new Model_科目明细账();
mmm = GetModel_Subject(YearDebit, YearCredit, yearfee,flag);
if (MonthLastValue.Equals("12"))
{
mmm.摘要 = "本年结账";
}
else
{
mmm.摘要 = "本月累计";
}
mmm.借或贷 = GetMark(yearfee);
list.Add(mmm);
}
}
return list;
}
private void Button_查询_Click(object sender, RoutedEventArgs e)
{
if (string.IsNullOrEmpty(TextBox_一级科目.Text))
{
MessageBoxCommon.Show("请选择一级科目");
TextBox_一级科目.Focus();
return;
}
else if (string.IsNullOrEmpty(TextBox_二级科目.Text))
{
MessageBoxCommon.Show("请选择二级科目");
TextBox_二级科目.Focus();
return;
}
else
{
string a = TextBox_一级科目.Text.ToString();
string b = TextBox_二级科目.Text.ToString();
List<Model_科目明细账> lm = vmk.GetSubjectDetail(a, b);
if (lm.Count > 0)
{
this.Label_年.Content = lm[0].年 + "年";
}
else
{
Model_科目明细账 m = new Model_科目明细账();
m.摘要 = "查询不到数据！";
lm.Add(m);
}
this.DataGrid_科目明细.ItemsSource = lm;
}
}
