private void StarCheck()
{
try {
Tracer.Debug("结算开始");
using (SMT_FB_EFModelContext context = new SMT_FB_EFModelContext())
{
DateTime thisYear = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
SetLog("开始获取总账数据，请稍等......");
List<T_FB_BUDGETACCOUNT> allItem = new List<T_FB_BUDGETACCOUNT>();
var q = from ent in context.T_FB_BUDGETACCOUNT.Include("T_FB_SUBJECT")
where ent.BUDGETYEAR>=DateTime.Now.Year
&& ent.T_FB_SUBJECT.SUBJECTNAME!="活动经费"
&& ent.T_FB_SUBJECT.SUBJECTNAME != "部门经费"
orderby ent.OWNERCOMPANYID, ent.OWNERDEPARTMENTID, ent.OWNERPOSTID
select ent;
allItem=q.ToList();
SetLog("获取总账数据完毕,开始获取年度预算");
//年度预算
List<T_FB_COMPANYBUDGETAPPLYMASTER> T_FB_COMPANYBUDGETAPPLYMASTERList =
(from ent in context.T_FB_COMPANYBUDGETAPPLYMASTER
where ent.BUDGETYEAR>=DateTime.Now.Year
&& ent.CHECKSTATES==2
&& ent.EDITSTATES==1
select ent).ToList();
List<T_FB_COMPANYBUDGETAPPLYDETAIL> T_FB_COMPANYBUDGETAPPLYDETAILList =
(from ent in context.T_FB_COMPANYBUDGETAPPLYDETAIL.Include("T_FB_COMPANYBUDGETAPPLYMASTER").Include("T_FB_SUBJECT")
where ent.T_FB_COMPANYBUDGETAPPLYMASTER.BUDGETYEAR>=DateTime.Now.Year
&& ent.T_FB_COMPANYBUDGETAPPLYMASTER.CHECKSTATES==2
&& ent.T_FB_COMPANYBUDGETAPPLYMASTER.EDITSTATES==1
select ent).ToList();
SetLog("获取年度预算完毕，开始获取年底增补");
//年度增补
List<T_FB_COMPANYBUDGETMODMASTER> T_FB_COMPANYBUDGETMODMASTERList =
(from ent in context.T_FB_COMPANYBUDGETMODMASTER
where ent.BUDGETYEAR >= DateTime.Now.Year
&& ent.CHECKSTATES==2
select ent).ToList();
List<T_FB_COMPANYBUDGETMODDETAIL> T_FB_COMPANYBUDGETMODDETAILList =
(from ent in context.T_FB_COMPANYBUDGETMODDETAIL.Include("T_FB_COMPANYBUDGETMODMASTER").Include("T_FB_SUBJECT")
where ent.T_FB_COMPANYBUDGETMODMASTER.BUDGETYEAR>= DateTime.Now.Year
&& ent.T_FB_COMPANYBUDGETMODMASTER.CHECKSTATES==2
select ent).ToList();
SetLog("获取年度增补数据完毕，开始获取月度预算");
//月度预算
List<T_FB_DEPTBUDGETAPPLYMASTER> T_FB_DEPTBUDGETAPPLYMASTERList =
(from ent in context.T_FB_DEPTBUDGETAPPLYMASTER
where ent.BUDGETARYMONTH >= thisYear
&& ent.CHECKSTATES==2
&& ent.EDITSTATES==1
select ent).ToList();
List<T_FB_DEPTBUDGETAPPLYDETAIL> T_FB_DEPTBUDGETAPPLYDETAILList =
(from ent in context.T_FB_DEPTBUDGETAPPLYDETAIL.Include("T_FB_DEPTBUDGETAPPLYMASTER").Include("T_FB_SUBJECT")
where ent.T_FB_DEPTBUDGETAPPLYMASTER.BUDGETARYMONTH >= thisYear
&& ent.T_FB_DEPTBUDGETAPPLYMASTER.CHECKSTATES==2
&& ent.T_FB_DEPTBUDGETAPPLYMASTER.EDITSTATES==1
select ent).ToList();
SetLog("获取月度预算完毕，开始获取月度增补");
//月度增补
List<T_FB_DEPTBUDGETADDMASTER> T_FB_DEPTBUDGETADDMASTERList =
(from ent in context.T_FB_DEPTBUDGETADDMASTER
where ent.BUDGETARYMONTH >= thisYear
&& ent.CHECKSTATES==2
select ent).ToList();
List<T_FB_DEPTBUDGETADDDETAIL> T_FB_DEPTBUDGETADDDETAILList
= (from ent in context.T_FB_DEPTBUDGETADDDETAIL.Include("T_FB_DEPTBUDGETADDMASTER").Include("T_FB_SUBJECT")
where ent.T_FB_DEPTBUDGETADDMASTER.BUDGETARYMONTH >= thisYear
&& ent.T_FB_DEPTBUDGETADDMASTER.CHECKSTATES==2
select ent).ToList();
SetLog("获取月度增补完毕，开始获取个人预算申请及增补");
List<T_FB_PERSONBUDGETAPPLYDETAIL> T_FB_PERSONBUDGETAPPLYDETAILList =
(from ent in context.T_FB_PERSONBUDGETAPPLYDETAIL.Include("T_FB_DEPTBUDGETAPPLYDETAIL").Include("T_FB_SUBJECT")
where ent.T_FB_DEPTBUDGETAPPLYDETAIL.T_FB_DEPTBUDGETAPPLYMASTER.BUDGETARYMONTH >= thisYear
&& ent.T_FB_DEPTBUDGETAPPLYDETAIL.T_FB_DEPTBUDGETAPPLYMASTER.CHECKSTATES==2
&& ent.T_FB_DEPTBUDGETAPPLYDETAIL.T_FB_DEPTBUDGETAPPLYMASTER.EDITSTATES==1
select ent).ToList();
List<T_FB_PERSONBUDGETADDDETAIL> T_FB_PERSONBUDGETADDDETAILList =
(from ent in context.T_FB_PERSONBUDGETADDDETAIL.Include("T_FB_DEPTBUDGETADDDETAIL").Include("T_FB_SUBJECT")
where ent.T_FB_DEPTBUDGETADDDETAIL.T_FB_DEPTBUDGETADDMASTER.BUDGETARYMONTH >= thisYear
&& ent.T_FB_DEPTBUDGETADDDETAIL.T_FB_DEPTBUDGETADDMASTER.CHECKSTATES==2
select ent).ToList();
SetLog("获取个人预算申请及增补完毕，开始获取费用报销");
//个人费用报销
List<T_FB_CHARGEAPPLYMASTER> T_FB_CHARGEAPPLYMASTERList =
(from ent in context.T_FB_CHARGEAPPLYMASTER
where ent.BUDGETARYMONTH >= thisYear
&& ent.CHECKSTATES==2
select ent).ToList();
List<T_FB_CHARGEAPPLYDETAIL> T_FB_CHARGEAPPLYDETAILList =
(from ent in context.T_FB_CHARGEAPPLYDETAIL.Include("T_FB_CHARGEAPPLYMASTER").Include("T_FB_SUBJECT")
where ent.T_FB_CHARGEAPPLYMASTER.BUDGETARYMONTH >= thisYear
&& ent.T_FB_CHARGEAPPLYMASTER.CHECKSTATES==2
select ent).ToList();
SetLog("获取费用报销完毕，开始处理......");
int allCount = allItem.Count;
int i = 0;
foreach (var item in allItem)
{
i++;
if (item.T_FB_SUBJECT.SUBJECTID=="08c1d9c6-2396-43c3-99f9-227e4a7eb417")// == "部门经费")
{
continue;
}
if (item.T_FB_SUBJECT.SUBJECTID == "d5134466-c207-44f2-8a36-cf7b96d5851f")//活动经费
{
continue;
}
if (item.OWNERDEPARTMENTID == "7f663615-56a0-46ed-96a3-0e9a79d2cb51"
&& item.T_FB_SUBJECT.SUBJECTID == "00161652-e3bf-4e9f-9a57-a9e1ff8cef74")
{
}
string id= item.T_FB_SUBJECT.SUBJECTID;
decimal A=0;//年度预算
decimal B=0;//年度增补
decimal C=0;//部门月度预算=公共+个人
decimal D=0;//部门月度增补=公共+个人
decimal C1 = 0;//月度预算-部门公共
decimal D1 = 0;//月度增补-部门公共
decimal C2 = 0;//月度预算-个人
decimal D2 = 0;//月度增补-个人
decimal E = 0;//个人费用报销已终审-部门公共
decimal F = 0;//个人费用报销审核中-部门公共
decimal G = 0;//个人费用报销已终审-个人
decimal H = 0;//个人费用报销审核中-个人
GetABCD(T_FB_COMPANYBUDGETAPPLYMASTERList,
T_FB_COMPANYBUDGETAPPLYDETAILList,
T_FB_COMPANYBUDGETMODMASTERList,
T_FB_COMPANYBUDGETMODDETAILList,
T_FB_DEPTBUDGETAPPLYMASTERList,
T_FB_DEPTBUDGETAPPLYDETAILList,
T_FB_DEPTBUDGETADDMASTERList,
T_FB_DEPTBUDGETADDDETAILList,
T_FB_CHARGEAPPLYMASTERList,
T_FB_CHARGEAPPLYDETAILList,
T_FB_PERSONBUDGETAPPLYDETAILList,
T_FB_PERSONBUDGETADDDETAILList,
item,
ref A, ref B, ref C,
ref C1, ref D1,
ref C2, ref D2,
ref D, ref E,
ref F, ref G,
ref H);
decimal BUDGETMONEY = item.BUDGETMONEY.Value;
decimal USABLEMONEY = item.USABLEMONEY.Value;
decimal ACTUALMONEY = item.ACTUALMONEY.Value;
decimal PAIEDMONEY = item.PAIEDMONEY.Value;
switch(item.ACCOUNTOBJECTTYPE.Value.ToString())
{
case "1"://部门年度预算
item.BUDGETMONEY = A+B;//预算金额
item.USABLEMONEY = A+B-C-D;//可用额度
item.ACTUALMONEY = A+B-C-D;//实际额度
item.PAIEDMONEY = C+D;//已用额度
break;
case "2"://部门月度预算--公共科目
item.BUDGETMONEY = C1+D1;//预算金额
item.USABLEMONEY = C1+D1-E-F;//可用额度
item.ACTUALMONEY = C1+D1-E;//实际额度
item.PAIEDMONEY = E;//已用额度
break;
case "3"://部门月度预算--个人月度预算
item.BUDGETMONEY = C2+D2;//预算金额
item.USABLEMONEY = C2+D2-G-H;//可用额度
item.ACTUALMONEY = C2+D2-G;//实际额度
item.PAIEDMONEY = G;//已用额度
break;
}
string msg = string.Empty;
msg = "总 " + allCount + " 第 " + i + " 条:";
if (BUDGETMONEY != item.BUDGETMONEY.Value)
{
msg += " BUDGETMONEY 不对，公司：" + item.OWNERCOMPANYID
//+ " 部门：" + item.OWNERDEPARTMENTID
//+ " 岗位：" + item.OWNERPOSTID
//+ " 科目id：" + item.T_FB_SUBJECT.SUBJECTID
+" 总账id"+item.BUDGETACCOUNTID
+ " 预算类型：" + item.ACCOUNTOBJECTTYPE.Value.ToString()
+ " 科目：" + item.T_FB_SUBJECT.SUBJECTNAME
+ " 修改前 BUDGETMONEY:" + BUDGETMONEY
+ " 修改后 BUDGETMONEY -> " + item.BUDGETMONEY.Value
+ " A:" + A
+ " B:" + B
+ " C:" + C
+ " D:" + D
+ " C1:" + C1
+ " D1:" + D1
+ " C2:" + C2
+ " D2:" + D2
+ " E:" + E
+ " F:" + F
+ " G:" + G
+ " H:" + H;
}
if (USABLEMONEY != item.USABLEMONEY.Value)
{
msg += " USABLEMONEY 不对，公司：" + item.OWNERCOMPANYID
//+ " 部门：" + item.OWNERDEPARTMENTID
//+ " 岗位：" + item.OWNERPOSTID
//+ " 科目id：" + item.T_FB_SUBJECT.SUBJECTID
+ " 总账id" + item.BUDGETACCOUNTID
+ " 预算类型：" + item.ACCOUNTOBJECTTYPE.Value.ToString()
+ " 科目：" + item.T_FB_SUBJECT.SUBJECTNAME
+ " 修改前 USABLEMONEY:" + USABLEMONEY
+ " 修改后 USABLEMONEY -> " + item.USABLEMONEY.Value
+ " A:" + A
+ " B:" + B
+ " C:" + C
+ " D:" + D
+ " C1:" + C1
+ " D1:" + D1
+ " C2:" + C2
+ " D2:" + D2
+ " E:" + E
+ " F:" + F
+ " G:" + G
+ " H:" + H;
}
if (ACTUALMONEY != item.ACTUALMONEY.Value)
{
msg += " ACTUALMONEY 不对，公司：" + item.OWNERCOMPANYID
//+ " 部门：" + item.OWNERDEPARTMENTID
//+ " 岗位：" + item.OWNERPOSTID
//+ " 科目id：" + item.T_FB_SUBJECT.SUBJECTID
+ " 总账id" + item.BUDGETACCOUNTID
+ " 预算类型：" + item.ACCOUNTOBJECTTYPE.Value.ToString()
+ " 科目：" + item.T_FB_SUBJECT.SUBJECTNAME
+ " 修改前 ACTUALMONEY:" + ACTUALMONEY
+ " 修改后 ACTUALMONEY -> " + item.ACTUALMONEY.Value
+ " A:" + A
+ " B:" + B
+ " C:" + C
+ " D:" + D
+ " C1:" + C1
+ " D1:" + D1
+ " C2:" + C2
+ " D2:" + D2
+ " E:" + E
+ " F:" + F
+ " G:" + G
+ " H:" + H;
}
if (PAIEDMONEY != item.PAIEDMONEY.Value)
{
msg +=" PAIEDMONEY 不对，公司：" + item.OWNERCOMPANYID
//+ " 部门id：" + item.OWNERDEPARTMENTID
//+ " 岗位id：" + item.OWNERPOSTID
//+ " 科目id："+item.T_FB_SUBJECT.SUBJECTID
+ " 总账id" + item.BUDGETACCOUNTID
+ " 预算类型：" + item.ACCOUNTOBJECTTYPE.Value.ToString()
+ " 科目：" + item.T_FB_SUBJECT.SUBJECTNAME
+ " 修改前 PAIEDMONEY:" + PAIEDMONEY
+ " 修改后 PAIEDMONEY -> " + item.PAIEDMONEY.Value
+ " A:" + A
+ " B:" + B
+ " C:" + C
+ " D:" + D
+ " C1:" + C1
+ " D1:" + D1
+ " C2:" + C2
+ " D2:" + D2
+ " E:" + E
+ " F:" + F
+ " G:" + G
+ " H:" + H;
}
SetLog(msg);
}
MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
DialogResult dr = MessageBox.Show("确定要更新异常的数据吗?", "确认", messButton);
if (dr == DialogResult.OK)//如果点击“确定”按钮
{
int j = context.SaveChanges();
MessageBox.Show("处理完毕,共处理了: " + j + " 条数据");
}
i = 0;
}
MessageBox.Show("处理完毕！");
}catch(Exception ex)
{
MessageBox.Show("异常："+ex.ToString());
SetLog(ex.ToString());
}
}
/// <summary>
/// 检查活动经费流水，调整额度
/// </summary>
private void starChechHDJF()
{
try
{
Tracer.Debug("结算开始");
using (SMT_FB_EFModelContext context = new SMT_FB_EFModelContext())
{
DateTime thisYear = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
SetLog("开始获取总账数据，请稍等......");
List<T_FB_BUDGETACCOUNT> allItem = new List<T_FB_BUDGETACCOUNT>();
List<T_FB_PERSONMONEYASSIGNMASTER> T_FB_PERSONMONEYASSIGNMASTERList = new List<T_FB_PERSONMONEYASSIGNMASTER>();
List<T_FB_PERSONMONEYASSIGNDETAIL> T_FB_PERSONMONEYASSIGNDETAILList = new List<T_FB_PERSONMONEYASSIGNDETAIL>();
List<T_FB_CHARGEAPPLYMASTER> T_FB_CHARGEAPPLYMASTERList = new List<T_FB_CHARGEAPPLYMASTER>();
List<T_FB_CHARGEAPPLYDETAIL> T_FB_CHARGEAPPLYDETAILList = new List<T_FB_CHARGEAPPLYDETAIL>();
if (allItem.Count < 1)
{
List<string> employeeALl = (from ent in context.T_FB_BUDGETACCOUNT.Include("T_FB_SUBJECT")
where ent.T_FB_SUBJECT.SUBJECTID == "d5134466-c207-44f2-8a36-cf7b96d5851f"//活动经费
&& ent.ACCOUNTOBJECTTYPE == 3
select ent.OWNERID).Distinct().ToList();
if (!string.IsNullOrEmpty(txtEmployee.Text))
{
employeeALl.Clear();
employeeALl.Add(txtEmployee.Text);
}
int allCount = employeeALl.Count;
int i = 0;
string msg = string.Empty;
//经费下拨额度
T_FB_PERSONMONEYASSIGNMASTERList
= (from ent in context.T_FB_PERSONMONEYASSIGNMASTER.Include("T_FB_PERSONMONEYASSIGNDETAIL")
where ent.CHECKSTATES == 2
select ent).ToList();
foreach (var employeeid in employeeALl)
{
i++;
var q = from ent in context.T_FB_BUDGETACCOUNT.Include("T_FB_SUBJECT")
where ent.T_FB_SUBJECT.SUBJECTID == "d5134466-c207-44f2-8a36-cf7b96d5851f"//活动经费
&& ent.ACCOUNTOBJECTTYPE == 3
&& ent.OWNERID == employeeid
select ent;
allItem = q.ToList();
if (allItem.Count > 1)
{
//MessageBox.Show("活动经费总账大于1，请手动处理，可能是异动导致");
foreach (var itm in allItem)
{
SetLog("活动经费总账多条：id:" + itm.BUDGETACCOUNTID+" ownerid="+itm.OWNERID+" ownerPostId="+itm.OWNERPOSTID);
}
continue;
}
SetLog("获取总账数据完毕,开始获取下拨经费");
T_FB_PERSONMONEYASSIGNDETAILList
= (from ent in context.T_FB_PERSONMONEYASSIGNDETAIL.Include("T_FB_PERSONMONEYASSIGNMASTER").Include("T_FB_SUBJECT")
where ent.T_FB_PERSONMONEYASSIGNMASTER.CHECKSTATES == 2
&& ent.OWNERID == employeeid
select ent).ToList();
SetLog("获取个人预算申请及增补完毕，开始获取费用报销");
//个人费用报销
T_FB_CHARGEAPPLYMASTERList =
(from ent in context.T_FB_CHARGEAPPLYMASTER
join b in context.T_FB_CHARGEAPPLYDETAIL on ent.CHARGEAPPLYMASTERID equals b.T_FB_CHARGEAPPLYMASTER.CHARGEAPPLYMASTERID
where ent.CHECKSTATES == 2 && b.T_FB_SUBJECT.SUBJECTID == "d5134466-c207-44f2-8a36-cf7b96d5851f"
&& ent.OWNERID == employeeid
select ent).ToList();
T_FB_CHARGEAPPLYDETAILList =
(from ent in context.T_FB_CHARGEAPPLYDETAIL.Include("T_FB_CHARGEAPPLYMASTER").Include("T_FB_SUBJECT")
join b in context.T_FB_CHARGEAPPLYMASTER on ent.T_FB_CHARGEAPPLYMASTER.CHARGEAPPLYMASTERID equals b.CHARGEAPPLYMASTERID
where ent.T_FB_SUBJECT.SUBJECTID == "d5134466-c207-44f2-8a36-cf7b96d5851f" && b.CHECKSTATES == 2
&& ent.T_FB_CHARGEAPPLYMASTER.OWNERID == employeeid
select ent).ToList();
SetLog("获取费用报销完毕，开始处理......");
var item = allItem.FirstOrDefault();
string id = item.T_FB_SUBJECT.SUBJECTID;
decimal A = 0;//年度预算
decimal B = 0;//年度增补
decimal C = 0;//部门月度预算=公共+个人
decimal D = 0;//部门月度增补=公共+个人
decimal C1 = 0;//月度预算-部门公共
decimal D1 = 0;//月度增补-部门公共
decimal C2 = 0;//月度预算-个人
decimal D2 = 0;//月度增补-个人
decimal E = 0;//个人费用报销已终审-部门公共
decimal F = 0;//个人费用报销审核中-部门公共
decimal G = 0;//个人费用报销已终审-个人
decimal H = 0;//个人费用报销审核中-个人
string ownerName = string.Empty;
GetJFABCD(T_FB_PERSONMONEYASSIGNMASTERList,
T_FB_PERSONMONEYASSIGNDETAILList,
T_FB_CHARGEAPPLYMASTERList,
T_FB_CHARGEAPPLYDETAILList,
item,
ref A, ref B, ref C,
ref C1, ref D1,
ref C2, ref D2,
ref D, ref E,
ref F, ref G,
ref H,
ref ownerName);
msg = "总 " + allCount + " 第 " + i + " 条:";
decimal USABLEMONEY = 0;
if (item.USABLEMONEY != null)
{
USABLEMONEY = item.USABLEMONEY.Value;
}
decimal ACTUALMONEY = 0;
if (item.ACTUALMONEY != null)
{
ACTUALMONEY = item.ACTUALMONEY.Value;
}
//decimal PAIEDMONEY = item.PAIEDMONEY.Value;
switch (item.ACCOUNTOBJECTTYPE.Value.ToString())
{
case "3"://部门月度预算--个人月度预算
//item.BUDGETMONEY = C2 + D2;//预算金额
item.USABLEMONEY = C2 - E - F;//可用额度
item.ACTUALMONEY = C2 - E;//实际额度
//item.PAIEDMONEY = G;//已用额度
break;
}
if (item.USABLEMONEY == null
|| USABLEMONEY != item.USABLEMONEY.Value)
{
if (item.USABLEMONEY == null)
{
msg += " 总账id" + item.BUDGETACCOUNTID + " USABLEMONEY==Null";
}
if (USABLEMONEY != item.USABLEMONEY.Value)
{
msg += " 总账id" + item.BUDGETACCOUNTID + " USABLEMONEY 不对，员工：" + ownerName
+ " 科目：" + item.T_FB_SUBJECT.SUBJECTNAME
+ " 修改前 USABLEMONEY:" + USABLEMONEY
+ " 修改后 USABLEMONEY -> " + item.USABLEMONEY.Value
+ " C2:" + C2
+ " E:" + E
+ " F:" + F;
if (USABLEMONEY > item.USABLEMONEY.Value)
{
msg +=System.Environment.NewLine+ "总账金额大于实际金额，可能导致报超，请重点关注---------------------------------->";
}
}
}
//if (item.ACTUALMONEY==null || ACTUALMONEY != item.ACTUALMONEY.Value)
//{
// if (item.ACTUALMONEY == null)
// {
// msg += " 总账id" + item.BUDGETACCOUNTID + " ACTUALMONEY==Null";
// }
// if (ACTUALMONEY != item.ACTUALMONEY.Value)
// {
// msg += " 总账id" + item.BUDGETACCOUNTID +" ACTUALMONEY 不对，员工：" + ownerName
// + " 预算类型：" + item.ACCOUNTOBJECTTYPE.Value.ToString()
// + " 科目：" + item.T_FB_SUBJECT.SUBJECTNAME
// + " 修改前 ACTUALMONEY:" + ACTUALMONEY
// + " 修改后 ACTUALMONEY -> " + item.ACTUALMONEY.Value
// + " C2:" + C2
// + " E:" + E
// + " F:" + F;
// }
//}
SetLog(msg);
}
i = 0;
if (msg.Contains("不对"))
{
MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
DialogResult dr = MessageBox.Show("确定要更新异常的数据吗?", "确认", messButton);
if (dr == DialogResult.OK)//如果点击“确定”按钮
{
int j = context.SaveChanges();
MessageBox.Show("处理完毕,共处理了: " + j + " 条数据");
}
}
SetLog("处理完毕！");
}
}
}
catch (Exception ex)
{
MessageBox.Show("异常：" + ex.ToString());
SetLog(ex.ToString());
}
}