/// <summary>
/// 通过信息类型ID和报文文件ID获取列表
/// </summary>
/// cais 16.06.06
/// <param name="infoTypeId">信息记录类型</param>
/// <param name="fileId">报文文件ID</param>
/// <returns></returns>
public List <InformationRecordInfo> FindInformationListByInfoTypeIdAndFileId(int infoTypeId, int fileId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT * FROM BANK_InformationRecord WHERE InfoTypeID = @InfoTypeId AND ReportID
IN(
SELECT br.ReportID FROM BANK_ReportFiles AS brf
LEFT JOIN BANK_Reports AS br ON br.ReportFileID = brf.FileID
WHERE brf.FileID = @FileId
) ORDER BY InfoTypeID");
DHelper.AddInParameter(comm, "@InfoTypeId", SqlDbType.Int, infoTypeId);
DHelper.AddInParameter(comm, "@FileId", SqlDbType.Int, fileId);
DataTable dt = DHelper.ExecuteDataTable(comm);
return(dt.Rows.Count > 0 ? LoadAll(dt.Rows) : null);
}
/// <summary>
/// 更新
/// </summary>
/// cais 16.05.25
/// <param name="value">信息记录实体</param>
/// <returns></returns>
public int Update(InformationRecordInfo value)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
UPDATE BANK_InformationRecord SET
Context=@Context,
InfoTypeID=@InfoTypeID,
ReportID=@ReportID
WHERE RecordID=@RecordID
");
DHelper.AddInParameter(comm, "@RecordID", SqlDbType.Int, value.RecordID);
DHelper.AddInParameter(comm, "@Context", SqlDbType.Text, value.Context);
DHelper.AddInParameter(comm, "@InfoTypeID", SqlDbType.NVarChar, value.InfoTypeID);
DHelper.AddInParameter(comm, "@ReportID", SqlDbType.Int, value.ReportID);
return(Convert.ToInt32(DHelper.ExecuteNonQuery(comm)));
}
/// <summary>
/// 代码校验，获取信息记录下的下拉框（下拉框中的值校验）
/// </summary>
/// yangj 2016.9.20
/// <param name="infoTypeId">信息记录标识</param>
/// <param name="htmlId">标签类型标识</param>
/// <returns></returns>
public DataTable CodeProofMethod(int infoTypeId, int htmlId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT BSR_ID FROM BANK_SegmentRules AS bs
LEFT JOIN BANK_DataSegment AS bd ON bd.BDS_ID = bs.BDS_ID
LEFT JOIN BANK_MetaComponents AS bm ON bm.MetaCode = bs.MetaCode
LEFT JOIN BANK_HtmlElement AS bh ON bh.BHE_ID = bm.BHE_ID
WHERE bd.BIT_ID = @BIT_ID AND bh.BHE_ID IN (@BHE_ID) AND bs.MetaCode NOT IN(7653)
");
DHelper.AddInParameter(comm, "@BIT_ID", SqlDbType.Int, infoTypeId);
DHelper.AddInParameter(comm, "@BHE_ID", SqlDbType.Int, htmlId);
var table = DHelper.ExecuteDataTable(comm);
return(table);
}
/// <summary>
/// 获取数据（段Code，段Id，数据元Code，必填）
/// </summary>
/// zouql 16.09.27
/// <param name="infoTypeId">信息记录Id</param>
/// <returns>字典</returns>
public Dictionary <string, string> FindDataByMetaId(int infoTypeId)
{
var comm = DHelper.GetSqlCommand(@"
SELECT
bds.ParagraphCode AS pCode
,bsr.BSR_ID AS seg
,bsr.IsRequired AS req
,bsr.MetaCode AS mCode
FROM BANK_InfoType AS bit
LEFT JOIN BANK_DataSegment AS bds
ON bds.BIT_ID = bit.BIT_ID
LEFT JOIN BANK_SegmentRules AS bsr
ON bsr.BDS_ID = bds.BDS_ID
LEFT JOIN BANK_Meta AS bm
ON bm.MetaCode = bsr.MetaCode
WHERE bit.BIT_ID = @infoTypeId
ORDER BY mCode
");
DHelper.AddInParameter(comm, "@infoTypeId", SqlDbType.Int, infoTypeId);
var dt = DHelper.ExecuteDataTable(comm);
// 实例化Dictionary容器
var dataDic = new Dictionary <string, string>();
// 声明key和value
var key = string.Empty;
var value = string.Empty;
// 将dataTable中的数据填入Dictionary<string, string>
for (var i = 0; i < dt.Rows.Count; i++)
{
// 拼装key
key = dt.Rows[i]["pCode"] + dt.Rows[i]["seg"].ToString();
// 拼装value
value = dt.Rows[i]["req"] + dt.Rows[i]["mCode"].ToString();
// 存储值
dataDic.Add(key, value);
}
// 返回数据
return(dataDic);
}
/// <summary>
/// 列表
/// </summary>
/// cais 16.05.25
/// <param name="reportFileID">报文文件ID</param>
/// <returns></returns>
public DataTable FindInformationRecord(int reportFileID)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT bir.*,bmt.Describe,bit.InfoName,br.* FROM BANK_InformationRecord AS bir
LEFT JOIN BANK_InfoType AS bit ON bit.BIT_ID = bir.InfoTypeID
LEFT JOIN BANK_MessageType AS bmt ON bmt.BMT_ID = bit.BMT_ID
LEFT JOIN BANK_Reports as br ON br.ReportID = bir.ReportID
WHERE bir.ReportID IN (
SELECT ReportID FROM BANK_Reports AS br WHERE ReportFileID = @ReportFileID
GROUP BY ReportID )
ORDER BY InfoTypeID
");
DHelper.AddInParameter(comm, "@ReportFileID", SqlDbType.Int, reportFileID);
return(DHelper.ExecuteDataTable(comm));
}
/// <summary>
/// 添加段规则
/// </summary>
/// zouql 16.07.06
/// <param name="sr">段规则实体</param>
/// <returns>结果</returns>
public int Create(SegmentRulesInfo sr)
{
var sql =
@"
INSERT INTO BANK_SegmentRules(Position,IsRequired,[Description],MetaCode)
VALUES(@Position,@IsRequired,@Description,@MetaCode)
SELECT SCOPE_IDENTITY()
";
SqlCommand cmd = DHelper.GetSqlCommand(sql);
DHelper.AddInParameter(cmd, "@Position", SqlDbType.Int, sr.Position);
DHelper.AddInParameter(cmd, "@IsRequired", SqlDbType.VarChar, sr.IsRequired);
DHelper.AddInParameter(cmd, "@Description", SqlDbType.NVarChar, sr.Description);
DHelper.AddInParameter(cmd, "@MetaCode", SqlDbType.Int, sr.MetaCode);
var result = DHelper.ExecuteNonQuery(cmd);
return(result);
}
/// <summary>
/// 更新
/// </summary>
/// yangj 16.09.21
/// <param name="value">临时数据记录实体</param>
/// <returns></returns>
public int Update(TempRecordInfo value)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
UPDATE Bank_TempRecord SET
Context=@Context,
BIT_ID=@BIT_ID,
ReportID=@ReportID,
UI_ID=@UI_ID
WHERE BTI_ID=@TempInfoID
");
DHelper.AddInParameter(comm, "@TempInfoID", SqlDbType.Int, value.TempInfoId);
DHelper.AddInParameter(comm, "@Context", SqlDbType.Text, value.Context);
DHelper.AddInParameter(comm, "@BIT_ID", SqlDbType.NVarChar, value.InfoTypeId);
DHelper.AddInParameter(comm, "@ReportID", SqlDbType.Int, value.ReportId);
DHelper.AddInParameter(comm, "@UI_ID", SqlDbType.Int, value.UserId);
return(Convert.ToInt32(DHelper.ExecuteNonQuery(comm)));
}
/// <summary>
/// 查询保证合同信息
/// </summary>
/// wangpf 16.08.03
/// <param name="financeId">融资Id</param>
/// <returns></returns>
public DataTable FindEnsureInfo(int financeId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT ma.Name AS '[承租人]', ma.[Identity] AS '[承租人身份证号]',
db.Name AS '[保证人]',db.[Identity] AS '[身份证号]',db.LiveHouseAddress AS '[住址]',db.Mobile AS '[联系电话]',
rz.OldName AS '[融资租赁合同]',
bz.OldName AS '[保证合同编号]'
FROM FANC_ApplicantInfo AS ma
RIGHT JOIN (
SELECT gt.FinanceId, gt.Name , gt.[Identity], gt.LiveHouseAddress ,gt.Mobile
FROM FANC_ApplicantInfo AS gt
WHERE gt.FinanceId = @FinanceId AND gt.Type = @Guarantee
) AS db ON ma.FinanceId = db.FinanceId
LEFT JOIN (
SELECT fl.OldName
FROM SYS_Reference ref
LEFT JOIN SYS_FileList fl ON ref.ReferenceId = fl.ReferenceId
WHERE ReferencedId = @ReferencedId
AND ReferencedModule = @ReferencedModule
AND ReferencedSid = @MainReferenceInfo
) AS rz on 1=1
LEFT JOIN (
SELECT fl.OldName
FROM SYS_Reference ref
LEFT JOIN SYS_FileList fl ON ref.ReferenceId = fl.ReferenceId
WHERE ReferencedId = @ReferencedId
AND ReferencedModule = @ReferencedModule
AND ReferencedSid = @GuaranteeReferenceInfo
) AS bz ON 1=1
WHERE ma.FinanceId = @FinanceId AND ma.Type = @MainApplicant
");
DHelper.AddInParameter(comm, "@FinanceId", SqlDbType.Int, financeId);
DHelper.AddInParameter(comm, "@ReferencedId", SqlDbType.Int, financeId);
DHelper.AddInParameter(comm, "@ReferencedModule", SqlDbType.Int, 4);
DHelper.AddInParameter(comm, "@MainApplicant", SqlDbType.TinyInt, ApplicantInfo.TypeEnum.主要申请人);
DHelper.AddInParameter(comm, "@Guarantee", SqlDbType.TinyInt, ApplicantInfo.TypeEnum.担保人);
DHelper.AddInParameter(comm, "@MainReferenceInfo", SqlDbType.Int, 1);
DHelper.AddInParameter(comm, "@GuaranteeReferenceInfo", SqlDbType.Int, 3);
return(DHelper.ExecuteDataTable(comm));
}
/// <summary>
/// 获取信息记录列表
/// </summary>
/// yand 16.08.02
/// <param name="infoTypeID"></param>
/// <returns></returns>
public List <ComboInfo> GetList(int infoTypeID)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT * FROM BANK_InfoType WHERE BIT_ID = @infoTypeID
");
DHelper.AddInParameter(comm, "@infoTypeID", SqlDbType.Int, infoTypeID);
DataTable dt = DHelper.ExecuteDataTable(comm);
List <ComboInfo> list = new List <ComboInfo>();
foreach (DataRow dr in dt.Rows)
{
ComboInfo cbi = new ComboInfo(dr["InfoCode"].ToString(), dr["InfoName"].ToString());
list.Add(cbi);
}
return(list);
}
/// <summary>
/// 更新
/// </summary>
/// yaoy 15.11.30
/// qiy 15.12.04
/// <param name="value"></param>
/// <returns></returns>
public bool Update(DictionaryTypeInfo value)
{
SqlCommand comm = DHelper.GetSqlCommand(
@"UPDATE SYS_DicType SET
Field = @Field,
Name = @Name,
IsCommon = @IsCommon,
Seed = @Seed
WHERE DT_ID = @TypeId
" );
DHelper.AddParameter(comm, "@TypeId", SqlDbType.Int, value.TypeId);
DHelper.AddInParameter(comm, "@Field", SqlDbType.NVarChar, value.Field);
DHelper.AddInParameter(comm, "@Name", SqlDbType.NVarChar, value.Name);
DHelper.AddInParameter(comm, "@IsCommon", SqlDbType.Bit, value.IsCommon);
DHelper.AddInParameter(comm, "@Seed", SqlDbType.Int, value.Seed);
return(DHelper.ExecuteNonQuery(comm) > 0);
}
/// <summary>
/// 下拉框列表
/// </summary>
/// yaoy 16.05.26
/// <param name="messageTypeId"></param>
/// <returns></returns>
public List <ComboInfo> FindComList(int messageTypeId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT * FROM BANK_DataSegment WHERE BIT_ID = @MessageTypeId
");
DHelper.AddInParameter(comm, "@MessageTypeId", SqlDbType.Int, messageTypeId);
DataTable dt = DHelper.ExecuteDataTable(comm);
List <ComboInfo> list = new List <ComboInfo>();
foreach (DataRow dr in dt.Rows)
{
ComboInfo cbi = new ComboInfo(dr["BDS_ID"].ToString(), dr["ParagraphName"].ToString());
list.Add(cbi);
}
return(list);
}
/// <summary>
/// 检查种子是否已被使用
/// </summary>
/// zouql 16.07.06
/// <param name="sr">实体</param>
/// <returns>结果</returns>
public object CheckSeed(SegmentRulesInfo sr)
{
var sql =
@"
SELECT * FROM BANK_SegmentRules
WHERE Position=@Position
AND [Description]=@Description
AND MetaCode=@MetaCode
";
SqlCommand cmd = DHelper.GetSqlCommand(sql);
DHelper.AddInParameter(cmd, "@Position", SqlDbType.Int, sr.Position);
DHelper.AddInParameter(cmd, "@IsRequired", SqlDbType.VarChar, sr.IsRequired);
DHelper.AddInParameter(cmd, "@Description", SqlDbType.NVarChar, sr.Description);
DHelper.AddInParameter(cmd, "@MetaCode", SqlDbType.Int, sr.MetaCode);
var result = DHelper.ExecuteScalar(cmd);
return(result);
}
/// <summary>
/// 修改段规则
/// </summary>
/// zouql 16.07.06
/// <param name="sr">段规则实体</param>
/// <returns>结果</returns>
public int Modify(SegmentRulesInfo sr)
{
var sql =
@"
UPDATE BANK_SegmentRules
SET Position=@Position,IsRequired=@IsRequired,
[Description]=@Description,MetaCode=@MetaCode
WHERE BSR_ID=@BSR_ID
";
SqlCommand cmd = DHelper.GetSqlCommand(sql);
DHelper.AddInParameter(cmd, "@BSR_ID", SqlDbType.Int, sr.SegmentRulesId);
DHelper.AddInParameter(cmd, "@Position", SqlDbType.Int, sr.Position);
DHelper.AddInParameter(cmd, "@IsRequired", SqlDbType.VarChar, sr.IsRequired);
DHelper.AddInParameter(cmd, "@Description", SqlDbType.NVarChar, sr.Description);
DHelper.AddInParameter(cmd, "@MetaCode", SqlDbType.Int, sr.MetaCode);
var result = DHelper.ExecuteNonQuery(cmd);
return(result);
}
/// <summary>
/// 通过文件类型获取下拉框列表
/// </summary>
/// yaoy 16.07.06
/// <param name="fileType"></param>
/// <returns></returns>
public List <ComboInfo> FindComListByFileType(int fileType)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT bmf.BMF_ID, bmf.FileName FROM BANK_MessageFileType AS mft
LEFT JOIN BANK_MessageFile AS bmf ON bmf.MFT_ID = mft.MFT_ID
WHERE mft.FileType = @FileType AND bmf.BMF_ID IS NOT NULL
");
DHelper.AddInParameter(comm, "@FileType", SqlDbType.Int, fileType);
DataTable dt = DHelper.ExecuteDataTable(comm);
List <ComboInfo> list = new List <ComboInfo>();
foreach (DataRow dr in dt.Rows)
{
ComboInfo cbi = new ComboInfo(dr["BMF_ID"].ToString(), dr["FileName"].ToString());
list.Add(cbi);
}
return(list);
}
/// <summary>
/// 信息记录类型下拉框
/// </summary>
/// yand 16.07.11
/// <param name="MessageTypeID">信息记录类型ID</param>
/// <returns></returns>
public List <ComboInfo> FindList(int MessageTypeID)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT * FROM BANK_MessageType WHERE BMT_ID = @MessageTypeID
");
DHelper.AddInParameter(comm, "@MessageTypeID", SqlDbType.Int, MessageTypeID);
DataTable dt = DHelper.ExecuteDataTable(comm);
List <ComboInfo> list = new List <ComboInfo>();
foreach (DataRow dr in dt.Rows)
{
ComboInfo cbi = new ComboInfo(dr["BMP_Code"].ToString(), dr["Describe"].ToString());
list.Add(cbi);
}
return(list);
}
/// <summary>
/// 更新
/// </summary>
/// zouql 16.08.29
/// <param name="noticeReceiver">通知实体</param>
/// <returns>更新结果</returns>
public int Update(NoticeInfo noticeReceiver)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
UPDATE
Notice_Notice
SET
UserId=@UserId,Content=@Content,[Time]=@Time,Title=@Title,IsRead=@IsRead,NoticeType=@NoticeType
WHERE
NR_ID=@NR_ID
");
// 变量赋值
DHelper.AddInParameter(comm, "@NR_ID", SqlDbType.Int, noticeReceiver.Id);
DHelper.AddParameter(comm, "@UserId", SqlDbType.Int, noticeReceiver.UserId);
DHelper.AddParameter(comm, "@Content", SqlDbType.NVarChar, noticeReceiver.Content);
DHelper.AddParameter(comm, "@Time", SqlDbType.DateTime, noticeReceiver.Time);
DHelper.AddParameter(comm, "@Title", SqlDbType.NVarChar, noticeReceiver.Title);
DHelper.AddParameter(comm, "@IsRead", SqlDbType.Bit, noticeReceiver.IsRead ? 1 : 0);
DHelper.AddParameter(comm, "@NoticeType", SqlDbType.NVarChar, noticeReceiver.NoticeType);
return(DHelper.ExecuteNonQuery(comm));
}
/// <summary>
/// 根据信息类型获取所有分组
/// </summary>
/// yaoy 16.09.21
/// <param name="infoTypeId"></param>
/// <returns></returns>
public List <int> FindGroupId(int infoTypeId)
{
List <int> list = new List <int>();
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT DISTINCT(GroupId) FROM BANK_SegmentRuleRelation WHERE InfoTypeId=@InfoTypeId
");
DHelper.AddInParameter(comm, "@InfoTypeId", SqlDbType.Int, infoTypeId);
DataTable dt = DHelper.ExecuteDataTable(comm);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(Convert.ToInt32(dr["GroupId"].ToString()));
}
}
return(list);
}
/// <summary>
/// 更新
/// </summary>
/// yangj 16.07.05
/// <param name="value">数据元实体</param>
/// <returns></returns>
public int Update(MetaInfo value)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
UPDATE BANK_Meta SET
MetaCode=@MetaCode,
Name=@Name,
DataType=@DataType,
DatasLength=@DatasLength,
Type=@Type
WHERE MetaCode=@OldMetaCode AND Type =@OldType
");
DHelper.AddParameter(comm, "@MetaCode", SqlDbType.Int, value.MetaCode);
DHelper.AddParameter(comm, "@Name", SqlDbType.NVarChar, value.Name);
DHelper.AddParameter(comm, "@DataType", SqlDbType.NVarChar, value.DataType);
DHelper.AddParameter(comm, "@DatasLength", SqlDbType.Int, value.DatasLength);
DHelper.AddParameter(comm, "@Type", SqlDbType.Int, value.Type);
DHelper.AddParameter(comm, "@OldMetaCode", SqlDbType.Int, value.OldMetaCode);
DHelper.AddInParameter(comm, "@OldType", SqlDbType.Int, value.OldType);
return(Convert.ToInt32(DHelper.ExecuteNonQuery(comm)));
}
/// <summary>
/// 获取段规则标识
/// </summary>
/// zouql 16.09.27
/// <param name="infoTypeId">信息记录Id</param>
/// <param name="metaCode">数据元Id</param>
/// <param name="paragraphCode">段Id</param>
/// <returns>段规则标识</returns>
public string FindIdByMetaAndInfoType(int infoTypeId, int metaCode, string paragraphCode)
{
var comm = DHelper.GetSqlCommand(@"
SELECT
bsr.BSR_ID
FROM BANK_InfoType AS bit
LEFT JOIN BANK_DataSegment AS bds
ON bds.BIT_ID = bit.BIT_ID
LEFT JOIN BANK_SegmentRules AS bsr
ON bsr.BDS_ID = bds.BDS_ID
LEFT JOIN BANK_Meta AS bm
ON bm.MetaCode = bsr.MetaCode
WHERE bit.BIT_ID = @infoTypeId AND ParagraphCode=@ParagraphCode AND bm.MetaCode=@metaCode
");
DHelper.AddInParameter(comm, "@infoTypeId", SqlDbType.Int, infoTypeId);
DHelper.AddInParameter(comm, "@metaCode", SqlDbType.Int, metaCode);
DHelper.AddInParameter(comm, "@ParagraphCode", SqlDbType.NVarChar, paragraphCode);
var segId = DHelper.ExecuteScalar(comm);
return(segId is DBNull || segId == null ? string.Empty : segId.ToString());
}
/// <summary>
/// 更新
/// </summary>
/// yaoy 16.05.25
/// <param name="value"></param>
/// <returns></returns>
public int Update(ReportFilesInfo value)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
UPDATE BANK_ReportFiles SET
MessageFileID = @MessageFileId,
ReportState = @ReportState ,
CreateTime = @CreateTime ,
SendTime = @SendTime ,
ServiceObj = @ServiceObj ,
ReportTextName = @ReportTextName
WHERE FileID = @FileId
");
DHelper.AddInParameter(comm, "@FileId", SqlDbType.Int, value.FileID);
DHelper.AddInParameter(comm, "@MessageFileId", SqlDbType.Int, value.MessageFileId);
DHelper.AddInParameter(comm, "@ReportState", SqlDbType.Int, value.ReportState);
DHelper.AddInParameter(comm, "@CreateTime", SqlDbType.DateTime, value.CreateTime);
DHelper.AddInParameter(comm, "@SendTime", SqlDbType.DateTime, value.SendTime);
DHelper.AddInParameter(comm, "@ServiceObj", SqlDbType.Int, value.ServiceObj);
DHelper.AddInParameter(comm, "@ReportTextName", SqlDbType.NVarChar, value.ReportTextName);
return(Convert.ToInt32(DHelper.ExecuteNonQuery(comm)));
}
/// <summary>
/// 根据数据元编号加载字典
/// </summary>
/// yand 16.07.07
/// <param name="MetaCode"></param>
/// <returns></returns>
public List <ComboInfo> ComboInfoLoad(int MetaCode)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT DISTINCT(bdc.Code),bdc.Name From BANK_DictionaryCode as bdc
LEFT JOIN BANK_DictionaryType AS bdt ON bdt.BDT_ID = bdc.BDT_ID
LEFT JOIN BANK_MetaDicRelation AS bmdr ON bmdr.BDT_ID = bdt.BDT_ID
WHERE bmdr.MetaCode = @MetaCode
");
DHelper.AddInParameter(comm, "@MetaCode", SqlDbType.Int, MetaCode);
DataTable dt = DHelper.ExecuteDataTable(comm);
List <ComboInfo> list = new List <ComboInfo>();
foreach (DataRow dr in dt.Rows)
{
ComboInfo cbi = new ComboInfo(dr["Code"].ToString(), dr["Name"].ToString());
list.Add(cbi);
}
return(list);
}
/// <summary>
/// 通过文件种类ID获取下拉框列表
/// </summary>
/// yaoy 16.05.26
/// <param name="fileId"></param>
/// <returns></returns>
public List <ComboInfo> FindComList(int fileId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT bmt.BMT_ID,bmt.Describe FROM BANK_MessageType AS bmt
LEFT JOIN BANK_ReportFiles AS brf ON brf.MessageFileID = bmt.BMF_ID
WHERE brf.FileID = @FileId
");
DHelper.AddInParameter(comm, "@FileId", SqlDbType.Int, fileId);
DataTable dt = DHelper.ExecuteDataTable(comm);
List <ComboInfo> list = new List <ComboInfo>();
foreach (DataRow dr in dt.Rows)
{
ComboInfo cbi = new ComboInfo(dr["BMT_ID"].ToString(), dr["Describe"].ToString());
list.Add(cbi);
}
return(list);
}
/// <summary>
/// 根据数据元标识符获取该条记录
/// </summary>
/// yand 16.07.04
/// <param name="MetaCode">数据元标识</param>
/// <returns></returns>
public MetaInfo Find(int MetaCode)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT * FROM BANK_Meta WHERE MetaCode = @MetaCode
");
DHelper.AddInParameter(comm, "@MetaCode", SqlDbType.Int, MetaCode);
var dt = DHelper.ExecuteDataTable(comm);
var result = Load(dt);
result.RuleType = new RuleTypeInfo();
if (dt.Rows[0]["RuleTypeId"] is DBNull)
{
result.RuleType.RuleTypeId = Convert.ToInt32(null);
}
else
{
result.RuleType.RuleTypeId = Convert.ToInt32(dt.Rows[0]["RuleTypeId"]);
}
return(result);
}
/// <summary>
/// 获取标识变更段中变更类型的值
/// </summary>
/// yand 16.09.30
/// <param name="metaCode">数据元ID</param>
/// <param name="parentCode">父元素ID</param>
/// <returns></returns>
public List <ComboInfo> GetChangeType(string metaCode, string parentCode)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT distinct( bd.CodeId),Code ,Name ,ParentCode ,bd.BDT_ID FROM BANK_DictionaryCode AS bd
LEFT JOIN BANK_MetaDicRelation AS bm ON bm.BDT_ID = bd.BDT_ID
LEFT JOIN BANK_SegmentRules AS bs ON bs.MetaCode = bm.MetaCode
WHERE bd.ParentCode = @ParentCode AND bm.MetaCode = @Code
");
DHelper.AddInParameter(comm, "@ParentCode", SqlDbType.NVarChar, parentCode);
DHelper.AddInParameter(comm, "@Code", SqlDbType.NVarChar, metaCode);
DataTable dt = DHelper.ExecuteDataTable(comm);
List <ComboInfo> list = new List <ComboInfo>();
foreach (DataRow dr in dt.Rows)
{
ComboInfo cbi = new ComboInfo(dr["Code"].ToString(), dr["Name"].ToString());
list.Add(cbi);
}
return(list);
}
/// <summary>
/// 查询已办列表
/// </summary>
/// yand 15.11.27
/// qiy 16.04.29 重新实现
/// yand 16.07.25 添加主要信息筛选
/// <param name="page"></param>
/// <param name="filters"></param>
/// <returns></returns>
public DataTable FindDoneList(Models.Pagination page, NameValueCollection filters)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT tmp.rownum,tmp.Name,tmp.PlateNo, tmp.VehicleKey,tmp.PlateNo,fi.InstanceId,
fi.FlowId, fw.Name AS FlowName,
fi.CurrentNode, fn.Name AS CurrentNodeName,fi.KeyXML,
fi.CurrentUser, dbo.GetUser(fi.CurrentUser) AS CurrentUserName, fi.ProcessUser, dbo.GetUser(fi.ProcessUser) AS ProcessUserName, fi.ProcessTime,
fi.StartUser, dbo.GetUser(fi.StartUser) AS StartUserName, fi.StartTime, fi.EndTime, fi.Status, dbo.Dic(9, fi.Status) AS StatusDesc
FROM FLOW_Instance AS fi
RIGHT JOIN (
SELECT TOP(@End) ROW_NUMBER() OVER(ORDER BY InstanceId DESC) AS rownum, InstanceId,fai.Name,fvi.PlateNo, fvi.VehicleKey FROM FLOW_Instance AS fi
LEFT JOIN FANC_ApplicantInfo AS fai ON fai.FinanceId = fi.KeyXML.value('FinanceId[1]', 'Int')
LEFT JOIN FANC_VehicleInfo AS fvi ON fvi .FinanceId =fai.FinanceId
WHERE InstanceId IN(SELECT InstanceId FROM FLOW_Log WHERE ProcessUser = @CurrentUser)
AND (fai.Type IS NULL OR fai.Type=1)
AND (@MainInfo IS NULL OR (fai.Name LIKE '%'+ @MainInfo+'%'OR fvi.PlateNo LIKE '%'+ @MainInfo+'%'))
AND(@FlowId IS NULL OR FlowId = @FlowId)
AND(@CurrentNode IS NULL OR CurrentNode = @CurrentNode)
AND(@BeginTime IS NULL OR DATEDIFF(day, StartTime, @BeginTime) <= 0) AND(@EndTime IS NULL OR DATEDIFF(day, StartTime, @EndTime) >= 0)
AND(@Status IS NULL OR Status = @Status)
) AS tmp ON fi.InstanceId = tmp.InstanceId
LEFT JOIN FLOW_WorkFlow AS fw ON fi.FlowId = fw.FlowId
LEFT JOIN FLOW_Node AS fn ON fi.CurrentNode = fn.NodeId
WHERE tmp.rownum > @Begin
");
DHelper.AddParameter(comm, "@Begin", SqlDbType.Int, page.Begin);
DHelper.AddParameter(comm, "@End", SqlDbType.Int, page.End);
DHelper.AddInParameter(comm, "@FlowId", SqlDbType.Int, filters["FlowId"]);
DHelper.AddInParameter(comm, "@CurrentNode", SqlDbType.Int, filters["CurrentNode"]);
DHelper.AddInParameter(comm, "@CurrentUser", SqlDbType.Int, filters["CurrentUser"]);
DHelper.AddInParameter(comm, "@BeginTime", SqlDbType.DateTime, filters["BeginTime"]);
DHelper.AddInParameter(comm, "@EndTime", SqlDbType.DateTime, filters["EndTime"]);
DHelper.AddInParameter(comm, "@Status", SqlDbType.TinyInt, filters["Status"]);
DHelper.AddInParameter(comm, "@MainInfo", SqlDbType.NVarChar, filters["MainInfo"]);
SqlCommand commPage = DHelper.GetSqlCommand(@"
SELECT COUNT(*) FROM FLOW_Instance as fi
LEFT JOIN FANC_ApplicantInfo AS fai ON fai.FinanceId = fi.KeyXML.value('FinanceId[1]', 'Int')
LEFT JOIN FANC_VehicleInfo AS fvi ON fvi .FinanceId =fai.FinanceId
WHERE InstanceId IN(SELECT InstanceId FROM FLOW_Log WHERE ProcessUser = @CurrentUser)
AND (fai.Type IS NULL OR fai.Type=1)
AND (@MainInfo IS NULL OR (fai.Name LIKE '%'+ @MainInfo+'%'OR fvi.PlateNo LIKE '%'+ @MainInfo+'%'))
AND(@FlowId IS NULL OR FlowId = @FlowId)
AND(@CurrentNode IS NULL OR CurrentNode = @CurrentNode)
AND(@BeginTime IS NULL OR DATEDIFF(day, StartTime, @BeginTime) <= 0) AND(@EndTime IS NULL OR DATEDIFF(day, StartTime, @EndTime) >= 0)
AND(@Status IS NULL OR Status = @Status)
" );
DHelper.AddInParameter(commPage, "@FlowId", SqlDbType.Int, filters["FlowId"]);
DHelper.AddInParameter(commPage, "@CurrentNode", SqlDbType.Int, filters["CurrentNode"]);
DHelper.AddInParameter(commPage, "@CurrentUser", SqlDbType.Int, filters["CurrentUser"]);
DHelper.AddInParameter(commPage, "@BeginTime", SqlDbType.DateTime, filters["BeginTime"]);
DHelper.AddInParameter(commPage, "@EndTime", SqlDbType.DateTime, filters["EndTime"]);
DHelper.AddInParameter(commPage, "@Status", SqlDbType.TinyInt, filters["Status"]);
DHelper.AddInParameter(commPage, "@MainInfo", SqlDbType.NVarChar, filters["MainInfo"]);
page.Total = Convert.ToInt32(DHelper.ExecuteScalar(commPage));
return(DHelper.ExecuteDataTable(comm));
}
/// <summary>
/// 查询融资租赁合同信息
/// </summary>
/// wangpf 16.08.03
/// <param name="financeId">融资Id</param>
/// <returns></returns>
public DataTable FindLeaseInfo(int financeId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT rz.OldName AS '[融资租赁合同]', db.OldName AS '[担保合同]',
ma.Name AS '[乙方姓名]', ma.[Identity] AS '[乙方证件号码]',
ja.Name AS '[共同借款人姓名]', ja.[Identity] AS '[共同借款人证件号码]',
vi.PlateNo AS '[车牌号]', vi.FrameNo AS '[车架号]', vi.EngineNo AS '[发动机号]',vi.SallerName AS '[所有权人]',
ch.CarBrand AS '[车辆品牌]', ch.Series AS '[型号]',
'' AS '[融资额大写]',fri.ApprovalPrincipal AS '[融资额]',fi.ProduceId,
pi.FinancingPeriods AS '[融资期限]','' AS '[手续费大写]',pi.CustomerPoundage AS '[手续费]',
'' AS '[保证金大写]',(pi.CustomerBailRatio * fri.ApprovalPrincipal)AS '[保证金]',
fri.RepaymentDate AS '[还款日]','' AS '[月还款金额大写]',fri.Payment AS '[月还款金额]',
YEAR( DATEADD(MM, 1,pi.AddDate)) AS '[首次支付年]',MONTH(DATEADD(MM, 1,pi.AddDate)) AS '[首次支付月]', fri.RepaymentDate AS '[首次支付日]',
bi.Name AS '[户名]', bi.BankName AS '[开户行]',bi.BankCard AS '[账号]'
FROM FANC_FinanceInfo AS fi
LEFT JOIN FANC_ReviewInfo AS fri ON fi.FinanceId = fri.FinanceId
LEFT JOIN PROD_ProduceInfo AS pi ON fi.ProduceId = pi.ProduceId
LEFT JOIN (
SELECT bi.*,fa.Name
FROM FANC_BankInfo AS bi
LEFT JOIN FANC_ApplicantInfo fa ON fa.FinanceId = bi.FinanceId AND bi.ApplicantId = fa.ApplicantId
WHERE fa.FinanceId = @FinanceId
) AS bi on 1=1
LEFT JOIN (
SELECT ai.FinanceId, ai.Name , ai.[Identity]
FROM FANC_ApplicantInfo AS ai
WHERE ai.FinanceId = @FinanceId AND Type = @MainApplicant
) AS ma ON ma.FinanceId = fi.FinanceId
LEFT JOIN (
SELECT ai.FinanceId, ai.Name, ai.[Identity]
FROM FANC_ApplicantInfo AS ai
WHERE ai.FinanceId = FinanceId AND Type = @JointlyApplicant
) AS ja ON ja.FinanceId = fi.FinanceId
LEFT JOIN (
SELECT fl.OldName
FROM SYS_Reference ref
LEFT JOIN SYS_FileList fl ON ref.ReferenceId = fl.ReferenceId
WHERE ReferencedId = @ReferencedId
AND ReferencedModule = @ReferencedModule
AND ReferencedSid = @MainReferenceInfo
)AS rz ON 1=1
LEFT JOIN(
SELECT fl.OldName
FROM SYS_Reference ref
LEFT JOIN SYS_FileList fl ON ref.ReferenceId = fl.ReferenceId
WHERE ReferencedId = @ReferencedId
AND ReferencedModule = @ReferencedModule
AND (ReferencedSid = @GuaranteeReferenceInfo)
) AS db ON 1=1
LEFT JOIN FANC_VehicleInfo AS vi ON fi.FinanceId = vi.FinanceId
LEFT JOIN V_CarHome AS ch ON vi.VehicleKey = ch.VehicleCode
WHERE fi.FinanceId = @FinanceId
");
DHelper.AddInParameter(comm, "@FinanceId", SqlDbType.Int, financeId);
DHelper.AddInParameter(comm, "@MainApplicant", SqlDbType.Int, ApplicantInfo.TypeEnum.主要申请人);
DHelper.AddInParameter(comm, "@JointlyApplicant", SqlDbType.Int, ApplicantInfo.TypeEnum.共同申请人);
DHelper.AddInParameter(comm, "@ReferencedId", SqlDbType.Int, financeId);
DHelper.AddInParameter(comm, "@ReferencedModule", SqlDbType.Int, 4);
DHelper.AddInParameter(comm, "@MainReferenceInfo", SqlDbType.Int, 1);
DHelper.AddInParameter(comm, "@GuaranteeReferenceInfo", SqlDbType.Int, 3);
DataTable dt = DHelper.ExecuteDataTable(comm);
Sys.MoneyToUpper moneyToUpper = new Sys.MoneyToUpper();
// 金额转大写
foreach (DataRow dr in dt.Rows)
{
string approvalPrincipal = String.IsNullOrEmpty(dr["[融资额]"].ToString()) ? "0" : dr["[融资额]"].ToString();
dr["[融资额大写]"] = moneyToUpper.RMBToUpper(Convert.ToDecimal(approvalPrincipal) * 10000, 2);
dr["[融资额]"] = Math.Round(Convert.ToDecimal(approvalPrincipal) * 10000, 2);
string customerPoundage = String.IsNullOrEmpty(dr["[手续费]"].ToString()) ? "0" : dr["[手续费]"].ToString();
dr["[手续费大写]"] = moneyToUpper.RMBToUpper(customerPoundage, 2);
dr["[手续费]"] = Math.Round(Convert.ToDecimal(customerPoundage), 2);
string ensurePrice = String.IsNullOrEmpty(dr["[保证金]"].ToString()) ? "0": dr["[保证金]"].ToString();
dr["[保证金大写]"] = moneyToUpper.RMBToUpper(Convert.ToDecimal(ensurePrice) * 100, 2);
dr["[保证金]"] = Math.Round(Convert.ToDecimal(ensurePrice) * 100, 2);
string payment = String.IsNullOrEmpty(dr["[月还款金额]"].ToString()) ? "0" : dr["[月还款金额]"].ToString();
dr["[月还款金额大写]"] = moneyToUpper.RMBToUpper(payment, 2);
dr["[月还款金额]"] = Math.Round(Convert.ToDecimal(payment), 2);
}
return(dt);
}