private void CZ_FB_GetParam(CZ_FB_EnFBObject _fbo, ref string _FValueSource, ref string _ObjMstIDSch, ref string _ObjAcctWhile)
{
switch (_fbo.ToString())
{
case "BD_Customer":
_FValueSource = "BD_Customer";
_ObjAcctWhile = " and a.FNumber in('1221.03') "; //客户 取科目1221.03，借+贷-
_ObjMstIDSch = "select FMasterID FObjMID from t_BD_Customer where FCUSTID=#objID# ";
break;
case "BD_Department":
_FValueSource = "BD_Department";
_ObjAcctWhile = "";
_ObjMstIDSch = "select FMasterID FObjMID from t_BD_Department where FDeptID=#objID# ";
break;
case "BD_Empinfo":
_FValueSource = "BD_Empinfo";
_ObjAcctWhile = " and a.FNumber in('1221.01') "; //员工 取科目1221.01，借+贷-
_ObjMstIDSch = "select FMasterID FObjMID from t_Hr_Empinfo where FID=#objID# ";
break;
case "BD_Supplier":
_FValueSource = "BD_Supplier";
_ObjAcctWhile = " and a.FNumber in('2202.01','2241.01') "; //供应商 取科目2202.01及2241.01，按余额进行合并后借+贷-
_ObjMstIDSch = "select FMasterID FObjMID from t_BD_Supplier where FSupplierID=#objID# ";
break;
case "ORG_Organizations":
_FValueSource = "ORG_Organizations";
_ObjAcctWhile = "";
_ObjMstIDSch = "select FOrgID FObjMID from t_ORG_Organizations where FOrgID=#objID# ";
break;
case "CN_BANKACNT":
_FValueSource = "CN_BANKACNT";
_ObjAcctWhile = "";
_ObjMstIDSch = "select FMasterID FObjMID from t_CN_BANKACNT where FBankAcntID=#objID# ";
break;
default:
break;
}
}
private string CZ_FB_GetObjBalDetail(CZ_FB_EnFBObject _fbo, string _FObjID)
{
if (_FObjID == "" || _FObjID == "0")
{
return("warning:未传入有效的查询对象ID");
}
string _FValueSource = ""; //t_bd_FlexItemProperty：FValueSource
string _ObjMstIDSch = ""; //查询对象表主键FMasterID的语句
string _ObjAcctWhile = ""; //对象查询科目定义 如果为空 不拼入查询语句 [每个公司需求不同]
string _FFlexNumber = ""; //t_bd_FlexItemProperty：FFLexNumber，应用于t_bd_FlexItemDetailV的列名
CZ_FB_GetParam(_fbo, ref _FValueSource, ref _ObjMstIDSch, ref _ObjAcctWhile);
_ObjMstIDSch = _ObjMstIDSch.Replace("#objID#", _FObjID);
#region step 01:获取 t_bd_FlexItemProperty-FFLexNumber
string _sqlGetFFlex = "select FValueSource,FFLexNumber from t_bd_FlexItemProperty where FValueSource='" + _FValueSource + "'";
DataTable _dtFFlex = new DataTable();
try
{
_dtFFlex = DBUtils.ExecuteDataSet(this.Context, _sqlGetFFlex).Tables[0];
}
catch (Exception _ex)
{
return("Err:获取FFlexNumber时发生错误 ErrMsg:" + _ex.Message);
}
if (_dtFFlex.Rows.Count == 0)
{
return("warning:t_bd_FlexItemProperty 未设置核算对象");
}
_FFlexNumber = _dtFFlex.Rows[0]["FFLexNumber"].ToString();
#endregion
#region step 02:获取往来余额
StringBuilder _sb = new StringBuilder();
//T_GL_BALANCE 为往来余额
//T_GL_VOUCHER 为凭证
switch (_fbo.ToString())
{
case "BD_Empinfo": //员工
_sb.Append("/*dialect*/ ");
_sb.Append("select i.FORGID,ab.FBookID,sy.FValue FCBYear,sp.FValue FCBPeriod, ");
_sb.Append("convert(datetime,CONVERT(varchar,sy.FValue)+'-'+CONVERT(varchar,sp.FValue)+'-01')FBegDate ");
_sb.Append("into #aco from T_ORG_Organizations i inner join t_bd_AccountBook ab on i.FOrgID=ab.FAccountOrgID ");
_sb.Append("inner join T_BAS_SystemProFile sy on ab.FBOOKID=sy.FAccountBookID and sy.FCategory='GL' and sy.FKEY='CurrentYear' ");
_sb.Append("inner join T_BAS_SystemProFile sp on ab.FBOOKID=sp.FAccountBookID and sp.FCategory='GL' and sp.FKEY='CurrentPeriod';");
_sb.Append("select b.FBeginBalance FGOBAmt,aco.FOrgID FOrgID,SUBSTRING(CONVERT(varchar(100), a.FMODIFYDATE, 120 ),1,10) Date,'' fexplanation,'B' mType from(" + _ObjMstIDSch + ")o1 ");
_sb.Append("inner join t_bd_FlexItemDetailV f on o1.FObjMID=f." + _FFlexNumber + " inner join #aco aco on 1=1 ");
_sb.Append("inner join T_GL_BALANCE b on f.FID=b.FDetailID and aco.FBOOKID=b.FACCOUNTBOOKID and aco.FCBYear=b.FYEAR and aco.FCBPeriod=b.FPeriod and b.FCURRENCYID=0 ");
_sb.Append("inner join T_BD_ACCOUNT a on b.FACCOUNTID=a.FACCTID ");
_sb.Append("where aco.FOrgID like('%') " + _ObjAcctWhile + " union all ");
_sb.Append("select ve.FDC*ve.FAmount FGOBAmt,aco.FOrgID FOrgID,SUBSTRING(CONVERT(varchar(100), v.FDATE, 120 ),1,10) Date,ve.fexplanation,'R' mType from(" + _ObjMstIDSch + ")o1 ");
_sb.Append("inner join t_bd_FlexItemDetailV f on o1.FObjMID=f." + _FFlexNumber + " inner join #aco aco on 1=1 ");
_sb.Append("inner join T_GL_VOUCHER v on aco.FBookID=v.FAccountBookID and v.FInvalid=0 ");
_sb.Append("inner join T_GL_VOUCHERENTRY ve on v.FVoucherID=ve.FVoucherID and f.FID=ve.FDetailID ");
_sb.Append("inner join T_BD_ACCOUNT a on ve.FAccountID=a.FACCTID ");
_sb.Append("where aco.FOrgID like('%') " + _ObjAcctWhile + " order by mType,Date ");
break;
default: //供应商或者客户
_sb.Append("/*dialect*/ ");
_sb.Append("select i.FORGID,ab.FBookID,sy.FValue FCBYear,sp.FValue FCBPeriod, ");
_sb.Append("convert(datetime,CONVERT(varchar,sy.FValue)+'-'+CONVERT(varchar,sp.FValue)+'-01')FBegDate ");
_sb.Append("into #aco from T_ORG_Organizations i inner join t_bd_AccountBook ab on i.FOrgID=ab.FAccountOrgID ");
_sb.Append("inner join T_BAS_SystemProFile sy on ab.FBOOKID=sy.FAccountBookID and sy.FCategory='GL' and sy.FKEY='CurrentYear' ");
_sb.Append("inner join T_BAS_SystemProFile sp on ab.FBOOKID=sp.FAccountBookID and sp.FCategory='GL' and sp.FKEY='CurrentPeriod';");
_sb.Append("select b.FBeginBalance FGOBAmt,aco.FOrgID FOrgID from(" + _ObjMstIDSch + ")o1 ");
_sb.Append("inner join t_bd_FlexItemDetailV f on o1.FObjMID=f." + _FFlexNumber + " inner join #aco aco on 1=1 ");
_sb.Append("inner join T_GL_BALANCE b on f.FID=b.FDetailID and aco.FBOOKID=b.FACCOUNTBOOKID and aco.FCBYear=b.FYEAR and aco.FCBPeriod=b.FPeriod and b.FCURRENCYID=0 ");
_sb.Append("inner join T_BD_ACCOUNT a on b.FACCOUNTID=a.FACCTID ");
_sb.Append("where aco.FOrgID like('%') " + _ObjAcctWhile + " union all ");
_sb.Append("select ve.FDC*ve.FAmount FGOBAmt,aco.FOrgID FOrgID from(" + _ObjMstIDSch + ")o1 ");
_sb.Append("inner join t_bd_FlexItemDetailV f on o1.FObjMID=f." + _FFlexNumber + " inner join #aco aco on 1=1 ");
_sb.Append("inner join T_GL_VOUCHER v on aco.FBookID=v.FAccountBookID and aco.FBegDate<=v.FBusDate and v.FInvalid=0 ");
_sb.Append("inner join T_GL_VOUCHERENTRY ve on v.FVoucherID=ve.FVoucherID and f.FID=ve.FDetailID ");
_sb.Append("inner join T_BD_ACCOUNT a on ve.FAccountID=a.FACCTID ");
_sb.Append("where aco.FOrgID like('%') " + _ObjAcctWhile);
break;
}
string _sqlGetFOBAmt = _sb.ToString();
try
{
var objs = DBUtils.ExecuteDynamicObject(this.Context, _sqlGetFOBAmt);
//按照工廠拆分
List <DynamicObject> dList2100 = objs.Where(d => d["FOrgID"].ToString().Equals("156140")).ToList();
List <DynamicObject> dList2200 = objs.Where(d => d["FOrgID"].ToString().Equals("156141")).ToList();
List <DynamicObject> dList2300 = objs.Where(d => d["FOrgID"].ToString().Equals("156142")).ToList();
List <DynamicObject> dList2600 = objs.Where(d => d["FOrgID"].ToString().Equals("293065")).ToList();
List <DynamicObject> dList2700 = objs.Where(d => d["FOrgID"].ToString().Equals("293066")).ToList();
List <DynamicObject> dList2800 = objs.Where(d => d["FOrgID"].ToString().Equals("156139")).ToList();
List <DynamicObject> dList2900 = objs.Where(d => d["FOrgID"].ToString().Equals("293067")).ToList();
List <DynamicObject> dList3000 = objs.Where(d => d["FOrgID"].ToString().Equals("293070")).ToList();
switch (_fbo.ToString())
{
case "BD_Empinfo": //员工
setRowValue(dList2100, "2100");
setRowValue(dList2200, "2200");
setRowValue(dList2300, "2300");
setRowValue(dList2600, "2600");
setRowValue(dList2700, "2700");
setRowValue(dList2800, "2800");
setRowValue(dList2900, "2900");
setRowValue(dList3000, "3000");
break;
default: //供应商或者客户
setCompValue(dList2100, "2100");
setCompValue(dList2200, "2200");
setCompValue(dList2300, "2300");
setCompValue(dList2600, "2600");
setCompValue(dList2700, "2700");
setCompValue(dList2800, "2800");
setCompValue(dList2900, "2900");
setCompValue(dList3000, "3000");
break;
}
}
catch (Exception _ex)
{
this.View.ShowErrMessage(_ex.Message, "获取往来余额时发生错误");
return("Err:获取往来余额时发生错误 ErrMsg:" + _ex.Message);
}
return("success");
#endregion
}
/// <summary>
/// FinBal_财务方法 获取指定对象 往来余额 [期初往来余额+本期凭证]
/// </summary>
/// <param name="_fbo">枚举类型 CZ_FB_EnFBObject 对象</param>
/// <param name="_FObjID">对象的ID</param>
/// <param name="_FAcctOrgID">指定组织ID 全部组织='%' or ''</param>
/// <param name="_FAcctID">指定科目ID 全科目='%' ｜ ''=使用预设置的科目条件 ｜ id=指定科目ID</param>
/// <param name="_backAmt">往来余额 取得后按实际需求调整显示</param>
/// <returns>方法值行结果备注 用于未取得金额分析</returns>
private string CZ_FB_GetObjBal(CZ_FB_EnFBObject _fbo, string _FObjID, string _FAcctOrgID, string _FAcctID, ref double _backAmt)
{
if (_FObjID == "" || _FObjID == "0")
{
_backAmt = 0;
return("warning:未传入有效的查询对象ID");
}
string _FValueSource = ""; //t_bd_FlexItemProperty：FValueSource
string _ObjMstIDSch = ""; //查询对象表主键FMasterID的语句
string _ObjAcctWhile = ""; //对象查询科目定义 如果为空 不拼入查询语句 [每个公司需求不同]
string _FFlexNumber = ""; //t_bd_FlexItemProperty：FFLexNumber，应用于t_bd_FlexItemDetailV的列名
CZ_FB_GetParam(_fbo, ref _FValueSource, ref _ObjMstIDSch, ref _ObjAcctWhile);
_ObjMstIDSch = _ObjMstIDSch.Replace("#objID#", _FObjID);
#region step 01:获取 t_bd_FlexItemProperty-FFLexNumber
string _sqlGetFFlex = "select FValueSource,FFLexNumber from t_bd_FlexItemProperty where FValueSource='" + _FValueSource + "'";
DataTable _dtFFlex = new DataTable();
try
{
_dtFFlex = DBUtils.ExecuteDataSet(this.Context, _sqlGetFFlex).Tables[0];
}
catch (Exception _ex)
{
_backAmt = 0;
return("Err:获取FFlexNumber时发生错误 ErrMsg:" + _ex.Message);
//this.View.ShowErrMessage(_ex.Message + " SQL:" + _sqlGetFFlex, "获取FFlexNumber时发生错误");
}
if (_dtFFlex.Rows.Count == 0)
{
_backAmt = 0;
return("warning:t_bd_FlexItemProperty 未设置核算对象");
}
_FFlexNumber = _dtFFlex.Rows[0]["FFLexNumber"].ToString();
#endregion
#region 原始语句
/*
* select i.FORGID,ab.FBookID,sy.FValue FCBYear,sp.FValue FCBPeriod,
* convert(datetime,CONVERT(varchar,sy.FValue)+'-'+CONVERT(varchar,sp.FValue)+'-01')FBegDate
* into #aco from T_ORG_Organizations i
* inner join t_bd_AccountBook ab on i.FOrgID=ab.FAccountOrgID
* inner join T_BAS_SystemProFile sy on ab.FBOOKID=sy.FAccountBookID and sy.FCategory='GL' and sy.FKEY='CurrentYear'
* inner join T_BAS_SystemProFile sp on ab.FBOOKID=sp.FAccountBookID and sp.FCategory='GL' and sp.FKEY='CurrentPeriod';
*
* select sum(FGOBAmt)FGOBAmt
* from(
* --select o1.FObjMID,aco.FOrgID,aco.FBookID,aco.FCBYear,aco.FCBPeriod,b.FDetailID,b.FAccountID,b.FBeginBalance,a.FNUMBER
* select b.FBeginBalance FGOBAmt
* from(select FMasterID FObjMID from t_bd_Supplier where FSupplierID=315438)o1
* inner join t_bd_FlexItemDetailV f on o1.FObjMID=f.FFLEX4
* inner join #aco aco on 1=1
* inner join T_GL_BALANCE b on f.FID=b.FDetailID and aco.FBOOKID=b.FACCOUNTBOOKID and aco.FCBYear=b.FYEAR and aco.FCBPeriod=b.FPeriod and b.FCURRENCYID=0
* inner join T_BD_ACCOUNT a on b.FACCOUNTID=a.FACCTID
* where aco.FOrgID like('%') and a.FAcctID like('%')
* union all
* --select o1.FObjMID,aco.*,ve.FAccountID,ve.FDC*ve.FAmount FVeAmount
* select ve.FDC*ve.FAmount FGOBAmt
* from(select FMasterID FObjMID from t_bd_Supplier where FSupplierID=837574)o1
* inner join t_bd_FlexItemDetailV f on o1.FObjMID=f.FFLEX4
* inner join #aco aco on 1=1
* inner join T_GL_VOUCHER v on aco.FBookID=v.FAccountBookID and aco.FBegDate<=v.FBusDate and v.FInvalid=0
* inner join T_GL_VOUCHERENTRY ve on v.FVoucherID=ve.FVoucherID and f.FID=ve.FDetailID
* inner join T_BD_ACCOUNT a on ve.FAccountID=a.FACCTID
* where aco.FOrgID like('%') and a.FAcctID like('%')
* )t
*/
#endregion
#region step 02:获取往来余额
StringBuilder _sb = new StringBuilder();
_FAcctOrgID = _FAcctOrgID == "" ? "%" : _FAcctOrgID;
//_FAcctID = _FAcctID == "" ? "%" : _FAcctID;
if (_FAcctID == "%")
{
_ObjAcctWhile = "";
}
else if (_FAcctID != "")
{
_ObjAcctWhile = " and a.FAcctID='" + _FAcctID + "' ";
}
_sb.Append("/*dialect*/ ");
_sb.Append("select i.FORGID,ab.FBookID,sy.FValue FCBYear,sp.FValue FCBPeriod, ");
_sb.Append("convert(datetime,CONVERT(varchar,sy.FValue)+'-'+CONVERT(varchar,sp.FValue)+'-01')FBegDate ");
_sb.Append("into #aco from T_ORG_Organizations i inner join t_bd_AccountBook ab on i.FOrgID=ab.FAccountOrgID ");
_sb.Append("inner join T_BAS_SystemProFile sy on ab.FBOOKID=sy.FAccountBookID and sy.FCategory='GL' and sy.FKEY='CurrentYear' ");
_sb.Append("inner join T_BAS_SystemProFile sp on ab.FBOOKID=sp.FAccountBookID and sp.FCategory='GL' and sp.FKEY='CurrentPeriod';");
_sb.Append("select isnull(sum(FGOBAmt),0)FGOBAmt from(select b.FBeginBalance FGOBAmt from(" + _ObjMstIDSch + ")o1 ");
_sb.Append("inner join t_bd_FlexItemDetailV f on o1.FObjMID=f." + _FFlexNumber + " inner join #aco aco on 1=1 ");
_sb.Append("inner join T_GL_BALANCE b on f.FID=b.FDetailID and aco.FBOOKID=b.FACCOUNTBOOKID and aco.FCBYear=b.FYEAR and aco.FCBPeriod=b.FPeriod and b.FCURRENCYID=0 ");
_sb.Append("inner join T_BD_ACCOUNT a on b.FACCOUNTID=a.FACCTID ");
_sb.Append("where aco.FOrgID like('" + _FAcctOrgID + "') " + _ObjAcctWhile + " union all ");
_sb.Append("select ve.FDC*ve.FAmount FGOBAmt from(" + _ObjMstIDSch + ")o1 ");
_sb.Append("inner join t_bd_FlexItemDetailV f on o1.FObjMID=f." + _FFlexNumber + " inner join #aco aco on 1=1 ");
_sb.Append("inner join T_GL_VOUCHER v on aco.FBookID=v.FAccountBookID and aco.FBegDate<=v.FBusDate and v.FInvalid=0 ");
_sb.Append("inner join T_GL_VOUCHERENTRY ve on v.FVoucherID=ve.FVoucherID and f.FID=ve.FDetailID ");
_sb.Append("inner join T_BD_ACCOUNT a on ve.FAccountID=a.FACCTID ");
_sb.Append("where aco.FOrgID like('" + _FAcctOrgID + "') " + _ObjAcctWhile);
//_sb.Append("union all select 888 FGOBAmt "); //测试用行
_sb.Append(")t ");
string _sqlGetFOBAmt = _sb.ToString();
DataTable _dtFGOBAmt = new DataTable();
try
{
_dtFGOBAmt = DBUtils.ExecuteDataSet(this.Context, _sqlGetFOBAmt).Tables[0];
}
catch (Exception _ex)
{
_backAmt = 0;
return("Err:获取往来余额时发生错误 ErrMsg:" + _ex.Message);
//this.View.ShowErrMessage(_ex.Message + " SQL:" + _sqlGetFOBAmt, "获取往来余额时发生错误");
}
if (_dtFGOBAmt.Rows.Count == 0)
{
_backAmt = 0;
return("warning:获取往来余额时,返回行数为0行");
}
_backAmt = -double.Parse(_dtFGOBAmt.Rows[0]["FGOBAmt"].ToString());
//this.View.ShowMessage(_sqlGetFOBAmt);
return("success");
#endregion
}
/// <summary>
/// FinBal_财务方法 获取指定对象 往来余额 [期初往来余额+本期凭证] 简化方法 取全部组织，过滤预设置的科目
/// </summary>
/// <param name="_fbo">枚举类型 CZ_FB_EnFBObject 对象</param>
/// <param name="_FObjID">对象的ID</param>
/// <param name="_backAmt">往来余额 取得后按实际需求调整显示</param>
/// <returns></returns>
private string CZ_FB_GetObjBal(CZ_FB_EnFBObject _fbo, string _FObjID, ref double _backAmt)
{
return(CZ_FB_GetObjBal(_fbo, _FObjID, "%", "", ref _backAmt));
}
