/// <summary>
/// 产品筛选(产品名)
/// </summary>
/// yangj 16.08.02
/// <returns></returns>
public List <ComboInfo> FindByProduceName(int creditId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT DISTINCT Name
FROM PROD_ProduceInfo AS A
LEFT JOIN CRET_BindProduce AS B
ON A.ProduceId = B.ProduceId
WHERE B.CreditId = @CreditId;");
DHelper.AddParameter(comm, "@CreditId", SqlDbType.Int, creditId);
DataTable dt = DHelper.ExecuteDataTable(comm);
List <ComboInfo> list = new List <ComboInfo>();
foreach (DataRow dr in dt.Rows)
{
ComboInfo cbi = new ComboInfo(dr["Name"].ToString(), dr["Name"].ToString());
list.Add(cbi);
}
return(list);
}
/// <summary>
/// 产品列表查询方法
/// </summary>
/// cais 16.03.28
/// <param name="pagination">分页</param>
/// <param name="filter">参数</param>
/// <returns></returns>
public DataTable Find(Models.Pagination pagination, NameValueCollection filter)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT
Produce.ProduceId,Code,Name,(InterestRate * 12) as InterestRate,(Rate * 12) as Rate,MinFinancingRatio,MaxFinancingRatio,FinalRatio,FinancingPeriods,RepaymentInterval,CustomerBailRatio,CustomerPoundage,AddDate,Remarks,AdditionalGPSCost,AdditionalOtherCost, dbo.Dic(3,RepaymentMethod) AS paymothed FROM PROD_ProduceInfo
AS Produce LEFT JOIN (SELECT TOP (@End) ROW_NUMBER() OVER (ORDER BY ProduceId DESC) AS rownum,ProduceId FROM PROD_ProduceInfo
WHERE @Code_Name IS NULL OR (Code like '%'+@Code_Name+'%' OR Name like '%'+@Code_Name+'%')) AS TMP
ON Produce.ProduceId=TMP.ProduceId WHERE TMP.rownum>@Begin
");
DHelper.AddParameter(comm, "@Begin", SqlDbType.Int, pagination.Begin);
DHelper.AddParameter(comm, "@End", SqlDbType.Int, pagination.End);
DHelper.AddParameter(comm, "@Code_Name", SqlDbType.VarChar, filter["ProductCodeOrName"]);
SqlCommand commPage = DHelper.GetSqlCommand(@"SELECT Count(*) FROM PROD_ProduceInfo WHERE @Code_Name IS NULL OR (Code like '%'+@Code_Name+'%' OR Name like '%'+@Code_Name+'%')");
DHelper.AddParameter(commPage, "@Code_Name", SqlDbType.NVarChar, filter["ProductCodeOrName"]);
pagination.Total = Convert.ToInt32(DHelper.ExecuteScalar(commPage));
DataTable dt = DHelper.ExecuteDataTable(comm);
return(dt);
}
/// <summary>
/// 更新
/// </summary>
/// qiy 16.03.29
/// <param name="value">值</param>
/// <returns></returns>
public int Update(PartnerInfo value)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
UPDATE CRET_PartnerInfo SET
Bail = @Bail,
Address = @Address,
ProxyArea = @ProxyArea,
VehicleManage = @VehicleManage,
ControllerName = @ControllerName,
ControllerIdentity = @ControllerIdentity,
ControllerTelephone = @ControllerTelephone,
Province=@Province,
City=@City
WHERE CreditId = @CreditId
" );
DHelper.AddParameter(comm, "@CreditId", SqlDbType.Int, value.CreditId);
DHelper.AddParameter(comm, "@Bail", SqlDbType.Decimal, value.Bail);
DHelper.AddParameter(comm, "@Address", SqlDbType.NVarChar, value.Address);
DHelper.AddParameter(comm, "@ProxyArea", SqlDbType.NVarChar, value.ProxyArea);
DHelper.AddParameter(comm, "@VehicleManage", SqlDbType.NVarChar, value.VehicleManage);
DHelper.AddParameter(comm, "@ControllerName", SqlDbType.NVarChar, value.ControllerName);
DHelper.AddParameter(comm, "@ControllerIdentity", SqlDbType.NVarChar, value.ControllerIdentity);
DHelper.AddParameter(comm, "@ControllerTelephone", SqlDbType.NVarChar, value.ControllerTelephone);
DHelper.AddParameter(comm, "@Province", SqlDbType.NVarChar, value.Province);
DHelper.AddParameter(comm, "@City", SqlDbType.NVarChar, value.City);
return(DHelper.ExecuteNonQuery(comm));
}
/// <summary>
/// 产品筛选
/// </summary>
/// yangj 16.08.02
/// <param name="produceName">产品名</param>
/// <param name="repaymentMethod">还款方式</param>
/// <param name="financingPeriods">融资期限</param>
/// <returns></returns>
public List <ComboInfo> FindProduct(string produceName, string repaymentMethod, string financingPeriods, int creditId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT A.ProduceId,
Code
FROM PROD_ProduceInfo AS A
LEFT JOIN CRET_BindProduce AS B
ON A.ProduceId = B.ProduceId
WHERE B.CreditId = @CreditId
AND (@Name IS NULL OR Name=@Name)
AND (@RepaymentMethod IS NULL OR RepaymentMethod=@RepaymentMethod)
AND (@FinancingPeriods IS NULL OR FinancingPeriods=@FinancingPeriods)");
DHelper.AddParameter(comm, "@CreditId", SqlDbType.Int, creditId);
DHelper.AddParameter(comm, "@Name", SqlDbType.NVarChar, produceName);
DHelper.AddParameter(comm, "@RepaymentMethod", SqlDbType.Int, repaymentMethod);
DHelper.AddParameter(comm, "@FinancingPeriods", SqlDbType.Int, financingPeriods);
DataTable dt = DHelper.ExecuteDataTable(comm);
List <ComboInfo> list = new List <ComboInfo>();
foreach (DataRow dr in dt.Rows)
{
ComboInfo cbi = new ComboInfo(dr["ProduceId"].ToString(), dr["Code"].ToString());
list.Add(cbi);
}
return(list);
}
/// <summary>
/// 分页查询
/// </summary>
/// qiy 16.03.29
/// <param name="page">分页信息</param>
/// <param name="filter">筛选条件</param>
/// <returns></returns>
public DataTable List(Models.Pagination page, NameValueCollection filter)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT tmp.rownum, ci.CreditId,
ci.Name, ci.Type, dbo.Dic(4, ci.Type) AS TypeDesc, ci.LineOfCredit, ci.AddDate, ci.Remarks,
cpi.Bail, cpi.ControllerName, cpi.ControllerTelephone
FROM CRET_CreditInfo AS ci
RIGHT JOIN (
SELECT TOP (@End) ROW_NUMBER() OVER (ORDER BY CreditId DESC) AS rownum, CreditId FROM CRET_CreditInfo
) AS tmp ON ci.CreditId = tmp.CreditId
LEFT JOIN CRET_PartnerInfo AS cpi ON ci.CreditId = cpi.CreditId
WHERE tmp.rownum > @Begin
" );
DHelper.AddParameter(comm, "@Begin", SqlDbType.Int, page.Begin);
DHelper.AddParameter(comm, "@End", SqlDbType.Int, page.End);
SqlCommand commPage = DHelper.GetSqlCommand(@"
SELECT COUNT(*) FROM CRET_CreditInfo
" );
page.Total = Convert.ToInt32(DHelper.ExecuteScalar(commPage));
return(DHelper.ExecuteDataTable(comm));
}
/// <summary>
/// 根据ID更新审核信息
/// </summary>
/// yangj 16.08.30
/// <param name="value">审核实体</param>
/// <returns></returns>
public int Update(ReviewInfo value)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
UPDATE FANC_ReviewInfo SET
RepaymentDate = @RepaymentDate,
FinanceCost = @FinanceCost,
FinalCost = @FinalCost,
Payment = @Payment,
AdvicefinanceMoney = @AdvicefinanceMoney,
ApprovalPrincipal = @ApprovalPrincipal,
ApprovalFinanceRatio = @ApprovalFinanceRatio,
ReviewType = @ReviewType
WHERE FinanceId = @FinanceId");
DHelper.AddParameter(comm, "@FinanceId", SqlDbType.Int, value.FinanceId);
DHelper.AddParameter(comm, "@AdvicefinanceMoney", SqlDbType.Decimal, value.AdvicefinanceMoney);
DHelper.AddParameter(comm, "@ApprovalPrincipal", SqlDbType.Decimal, value.ApprovalPrincipal);
DHelper.AddParameter(comm, "@ApprovalFinanceRatio", SqlDbType.Float, value.ApprovalFinanceRatio);
DHelper.AddParameter(comm, "@FinanceCost", SqlDbType.Decimal, value.FinanceCost);
DHelper.AddParameter(comm, "@FinalCost", SqlDbType.Decimal, value.FinalCost);
DHelper.AddParameter(comm, "@Payment", SqlDbType.Decimal, value.Payment);
DHelper.AddParameter(comm, "@RepaymentDate", SqlDbType.Int, value.RepaymentDate);
DHelper.AddParameter(comm, "@ReviewType", SqlDbType.TinyInt, value.ReviewType);
return(DHelper.ExecuteNonQuery(comm));
}
/// <summary>
/// 查找指定的借贷信息
/// </summary>
/// zouql 16.08.30
/// <param name="financeId">融资标识</param>
/// <returns>借贷信息</returns>
public BorrowInfo Find(int financeId)
{
SqlCommand comm = new SqlCommand(@"
SELECT
BI_ID,
FinanceId,
ApprovalPrincipal,
InterestRate,
FinancingPeriods,
RepaymentInterval,
RepaymentMethod,
RepaymentDate,
FinanceAddDate,
[State],
OncePayMonths,
FinalRatio,
CustomerBailRatio,
FinalCost,
ExtralCost
FROM
FANC_Borrow
WHERE FinanceId=@financeId
");
DHelper.AddParameter(comm, "@financeId", SqlDbType.Int, financeId);
var dt = DHelper.ExecuteDataTable(comm);
return(Load(dt.Rows[0]));
}
/// <summary>
/// 更新[未使用]
/// </summary>
/// qiy 16.03.08
/// <param name="value"></param>
/// <returns></returns>
public bool Update(ActionInfo value)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
UPDATE FLOW_Action SET
Node = @NodeId,
Transfer = @Transfer,
Name = @Name,
Type = @Type,
AllocationType = @AllocationType,
Description = @Description,
Method = @Method
WHERE ActionId = @ActionId
");
DHelper.AddParameter(comm, "@ActionId", SqlDbType.Int, value.ActionId);
DHelper.AddParameter(comm, "@NodeId", SqlDbType.Int, value.NodeId);
DHelper.AddParameter(comm, "@Transfer", SqlDbType.Int, value.Transfer);
DHelper.AddParameter(comm, "@Name", SqlDbType.NVarChar, value.Name);
DHelper.AddParameter(comm, "@Type", SqlDbType.TinyInt, value.AllocationType);
DHelper.AddParameter(comm, "@Description", SqlDbType.NVarChar, value.Description);
DHelper.AddParameter(comm, "@Method", SqlDbType.VarChar, value.Method);
return(DHelper.ExecuteNonQuery(comm) > 0);
}
/// <summary>
/// 更新
/// </summary>
/// qiy 16.03.31
/// qiy 16.05.31
/// yangj 16.07.26 新增融资预估金额字段
/// zouql 16.07.28 新增厂商指导价字段
/// zouql 16.08.04 新增融资实际金额字段
/// <param name="value">值</param>
/// <returns></returns>
public int Update(FinanceInfo value)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
UPDATE FANC_FinanceInfo SET
ProduceId = @ProduceId,
Type = @Type,
IntentionPrincipal = @IntentionPrincipal,
ApprovalValue = @ApprovalValue,
Remarks = @Remarks ,
OncePayMonths=@OncePayMonths,
MarginMoney=@MarginMoney,
PaymonthlyMoney=@PaymonthlyMoney,
OnepayInterestMoney=@OnepayInterestMoney,
ActualcashMoney=@ActualcashMoney
WHERE FinanceId = @FinanceId
" );
DHelper.AddParameter(comm, "@FinanceId", SqlDbType.Int, value.FinanceId);
DHelper.AddParameter(comm, "@ProduceId", SqlDbType.Int, value.ProduceId);
DHelper.AddParameter(comm, "@Type", SqlDbType.TinyInt, value.Type);
// 建议融资金额
DHelper.AddParameter(comm, "@IntentionPrincipal", SqlDbType.Decimal, value.IntentionPrincipal);
DHelper.AddParameter(comm, "@ApprovalValue", SqlDbType.Decimal, value.ApprovalValue);
DHelper.AddParameter(comm, "@Remarks", SqlDbType.NVarChar, value.Remarks);
DHelper.AddParameter(comm, "@OncePayMonths", SqlDbType.Int, value.OncePayMonths);
// 保证金、先付月供金额、一次性付息金额、实际用款金额
DHelper.AddParameter(comm, "@MarginMoney", SqlDbType.Decimal, value.MarginMoney);
DHelper.AddParameter(comm, "@PaymonthlyMoney", SqlDbType.Decimal, value.PaymonthlyMoney);
DHelper.AddParameter(comm, "@OnepayInterestMoney", SqlDbType.Decimal, value.OnepayInterestMoney);
DHelper.AddParameter(comm, "@ActualcashMoney", SqlDbType.Decimal, value.ActualcashMoney);
return(DHelper.ExecuteNonQuery(comm));
}
/// <summary>
/// 列表
/// </summary>
/// yangj 16.07.01
/// <param name="dictionaryTypeId">字典类型ID</param>
/// <returns></returns>
public DataTable List(Models.Pagination page, NameValueCollection filter)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT tmp.rownum, bdt.BDT_ID,bdt.Name, temp.Name AS ParentName
FROM BANK_DictionaryType AS bdt
RIGHT JOIN (SELECT TOP (@End) ROW_NUMBER() OVER (ORDER BY BDT_ID DESC) AS rownum,BDT_ID FROM BANK_DictionaryType
)AS tmp ON bdt.BDT_ID = tmp.BDT_ID
LEFT JOIN BANK_DictionaryType
AS temp ON bdt.ParentType = temp.BDT_ID
WHERE tmp.rownum > @Begin
" );
DHelper.AddParameter(comm, "@Begin", SqlDbType.Int, page.Begin);
DHelper.AddParameter(comm, "@End", SqlDbType.Int, page.End);
SqlCommand commPage = DHelper.GetSqlCommand(
@"SELECT COUNT(*) FROM BANK_DictionaryType
" );
page.Total = Convert.ToInt32(DHelper.ExecuteScalar(commPage));
DataTable dt = DHelper.ExecuteDataTable(comm);
return(dt);
}
/// <summary>
/// 根据ApplicantId 删除相对应的附加申请人（担保人、联系人）
/// </summary>
/// cais 16.03.31
/// <param name="applicantId"></param>
/// <returns></returns>
public int Delete(int applicantId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"DELETE FANC_ApplicantInfo WHERE ApplicantId = @ApplicantId");
DHelper.AddParameter(comm, "@ApplicantId", SqlDbType.Int, applicantId);
return(DHelper.ExecuteNonQuery(comm));
}
/// <summary>
/// 根据一个产品ID ，修改对应的产品
/// </summary>
/// cais 16.03.29
/// yangj 16.07.25(新增融资返范围)
/// <param name="produce"></param>
/// <returns></returns>
public int Update(ProduceInfo produce)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
UPDATE PROD_ProduceInfo SET Code=@Code ,
Name=@Name,
InterestRate=@InterestRate,
Rate=@Rate,
RepaymentMethod=@RepaymentMethod,
MinFinancingRatio=@MinFinancingRatio,
MaxFinancingRatio=@MaxFinancingRatio,
FinalRatio=@FinalRatio,
FinancingPeriods=@FinancingPeriods,
RepaymentInterval=@RepaymentInterval,
CustomerBailRatio=@CustomerBailRatio,
CustomerPoundage=@CustomerPoundage,
AddDate=@AddDate,
Remarks=@Remarks,
AdditionalGPSCost=@AdditionalGPSCost,
AdditionalOtherCost=@AdditionalOtherCost,
IsVehiclePrice=@IsVehiclePrice,
IsPurchaseTax=@IsPurchaseTax,
IsBusinessInsurance=@IsBusinessInsurance,
IsTafficCompulsoryInsurance=@IsTafficCompulsoryInsurance,
IsVehicleVesselTax=@IsVehicleVesselTax,
IsExtendedWarrantyInsurance=@IsExtendedWarrantyInsurance,
IsOther=@IsOther
WHERE ProduceId=@ProduceId");
DHelper.AddParameter(comm, "@Code", SqlDbType.NVarChar, produce.Code);
DHelper.AddParameter(comm, "@Name", SqlDbType.NVarChar, produce.Name);
DHelper.AddParameter(comm, "@InterestRate", SqlDbType.Float, (produce.InterestRate) / 12);
DHelper.AddParameter(comm, "@Rate", SqlDbType.Float, (produce.Rate) / 12);
DHelper.AddParameter(comm, "@RepaymentMethod", SqlDbType.Int, produce.RepaymentMethod);
DHelper.AddParameter(comm, "@MinFinancingRatio", SqlDbType.Int, produce.MinFinancingRatio);
DHelper.AddParameter(comm, "@MaxFinancingRatio", SqlDbType.Int, produce.MaxFinancingRatio);
DHelper.AddParameter(comm, "@FinalRatio", SqlDbType.Int, produce.FinalRatio);
DHelper.AddParameter(comm, "@FinancingPeriods", SqlDbType.Int, produce.FinancingPeriods);
DHelper.AddParameter(comm, "@RepaymentInterval", SqlDbType.Int, produce.RepaymentInterval);
DHelper.AddParameter(comm, "@CustomerBailRatio", SqlDbType.Float, produce.CustomerBailRatio);
DHelper.AddParameter(comm, "@CustomerPoundage", SqlDbType.Decimal, produce.CustomerPoundage);
DHelper.AddParameter(comm, "@AddDate", SqlDbType.DateTime, DateTime.Now);
DHelper.AddParameter(comm, "@Remarks", SqlDbType.NVarChar, produce.Remarks);
DHelper.AddParameter(comm, "@AdditionalGPSCost", SqlDbType.Decimal, produce.AdditionalGPSCost);
DHelper.AddParameter(comm, "@AdditionalOtherCost", SqlDbType.Decimal, produce.AdditionalOtherCost);
// 新增融资范围
DHelper.AddParameter(comm, "@IsVehiclePrice", SqlDbType.Bit, produce.IsVehiclePrice);
DHelper.AddParameter(comm, "@IsPurchaseTax", SqlDbType.Bit, produce.IsPurchaseTax);
DHelper.AddParameter(comm, "@IsBusinessInsurance", SqlDbType.Bit, produce.IsBusinessInsurance);
DHelper.AddParameter(comm, "@IsTafficCompulsoryInsurance", SqlDbType.Bit, produce.IsTafficCompulsoryInsurance);
DHelper.AddParameter(comm, "@IsVehicleVesselTax", SqlDbType.Bit, produce.IsVehicleVesselTax);
DHelper.AddParameter(comm, "@IsExtendedWarrantyInsurance", SqlDbType.Bit, produce.IsExtendedWarrantyInsurance);
DHelper.AddParameter(comm, "@IsOther", SqlDbType.Bit, produce.IsOther);
DHelper.AddParameter(comm, "@ProduceId", SqlDbType.Int, produce.ProduceId);
return(DHelper.ExecuteNonQuery(comm));
}
/// <summary>
/// 插入指定的借贷信息
/// </summary>
/// zouql 16.08.30
/// <param name="borrowInfo">借贷信息</param>
/// <returns>操作结果</returns>
public int Insert(BorrowInfo borrowInfo)
{
SqlCommand comm = new SqlCommand(@"
INSERT INTO FANC_Borrow
(
FinanceId,
ApprovalPrincipal,
InterestRate,
FinancingPeriods,
RepaymentInterval,
RepaymentMethod,
RepaymentDate,
FinanceAddDate,
[State],
OncePayMonths,
FinalRatio,
CustomerBailRatio,
FinalCost,
ExtralCost
)
VALUES
(
@FinanceId,
@ApprovalPrincipal,
@InterestRate,
@FinancingPeriods,
@RepaymentInterval,
@RepaymentMethod,
@RepaymentDate,
@FinanceAddDate,
@State,
@OncePayMonths,
@FinalRatio,
@CustomerBailRatio,
@FinalCost,
@ExtralCost
)
SELECT SCOPE_IDENTITY()
");
DHelper.AddParameter(comm, "@financeId", SqlDbType.Int, borrowInfo.FinanceId);
DHelper.AddParameter(comm, "@ApprovalPrincipal", SqlDbType.Decimal, borrowInfo.ApprovalPrincipal);
DHelper.AddParameter(comm, "@InterestRate", SqlDbType.Float, borrowInfo.InterestRate);
DHelper.AddParameter(comm, "@FinancingPeriods", SqlDbType.Int, borrowInfo.FinancingPeriods);
DHelper.AddParameter(comm, "@RepaymentInterval", SqlDbType.Int, borrowInfo.RepaymentInterval);
DHelper.AddParameter(comm, "@RepaymentMethod", SqlDbType.NVarChar, borrowInfo.RepaymentMethod);
DHelper.AddParameter(comm, "@RepaymentDate", SqlDbType.Int, borrowInfo.RepaymentDate);
DHelper.AddParameter(comm, "@FinanceAddDate", SqlDbType.DateTime, borrowInfo.FinanceAddDate);
DHelper.AddParameter(comm, "@State", SqlDbType.NVarChar, borrowInfo.State);
DHelper.AddParameter(comm, "@OncePayMonths", SqlDbType.Int, borrowInfo.OncePayMonths);
DHelper.AddParameter(comm, "@FinalRatio", SqlDbType.Float, borrowInfo.FinalRatio);
DHelper.AddParameter(comm, "@CustomerBailRatio", SqlDbType.Float, borrowInfo.CustomerBailRatio);
DHelper.AddParameter(comm, "@FinalCost", SqlDbType.Decimal, borrowInfo.FinalCost);
DHelper.AddParameter(comm, "@ExtralCost", SqlDbType.Decimal, borrowInfo.ExtralCost);
return(Convert.ToInt32(DHelper.ExecuteScalar(comm)));
}
/// <summary>
/// 通过FinanceId 查询 产品列表查询方法
/// </summary>
/// cais 16.03.31
/// <param name="financeId"></param>
/// <returns></returns>
public List <ApplicantInfo> Find(int financeId)
{
SqlCommand comm = DHelper.GetSqlCommand(
@"SELECT * FROM FANC_ApplicantInfo WHERE FinanceId = @FinanceId ORDER BY Type");
DHelper.AddParameter(comm, "@FinanceId", SqlDbType.Int, financeId);
return(LoadAll(DHelper.ExecuteDataTable(comm).Rows));
}
/// <summary>
/// 查询XML关系数据
/// </summary>
/// <param name="instanceId"></param>
/// <returns></returns>
public string FindXMLData(int instanceId)
{
SqlCommand comm = DHelper.GetSqlCommand(
"SELECT KeyXML FROM FLOW_Instance WHERE InstanceId = @InstanceId"
);
DHelper.AddParameter(comm, "@InstanceId", SqlDbType.Int, instanceId);
return(Convert.ToString(DHelper.ExecuteScalar(comm)));
}
/// <summary>
/// 获取申请人姓名和ID
/// zouql 16.07.27
/// yangj 16.09.14(还款用户直接默认为主要申请人名字，不用下拉菜单)
/// </summary>
/// <returns></returns>
public ApplicantInfo Option(int financeId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"SELECT ApplicantId,Name FROM FANC_ApplicantInfo WHERE Type = 1 AND FinanceId = @FinanceId");
DHelper.AddParameter(comm, "@FinanceId", SqlDbType.Int, financeId);
DataTable dt = DHelper.ExecuteDataTable(comm);
return(dt.Rows.Count > 0 ? Load(dt.Rows[0]) : null);
}
/// <summary>
/// 通过角色删除
/// </summary>
/// yaoy 15.11.26
/// qiy 16.03.08
/// <param name="roleId"></param>
/// <returns></returns>
public void DeleteByRole(int roleId)
{
SqlCommand comm = DHelper.GetSqlCommand(
"DELETE USER_Permissions WHERE RoleId = @RoleId"
);
DHelper.AddParameter(comm, "@RoleId", SqlDbType.Int, roleId);
DHelper.ExecuteNonQuery(comm);
}
/// <summary>
/// 通过菜单删除
/// </summary>
/// yand 15.11.24
/// qiy 16.03.08
/// <param name="menuId"></param>
/// <returns></returns>
public void DeleteByMenu(int menuId)
{
SqlCommand comm = DHelper.GetSqlCommand(
"DELETE USER_Permissions WHERE MenuId = @MenuId"
);
DHelper.AddParameter(comm, "@MenuId", SqlDbType.Int, menuId);
DHelper.ExecuteNonQuery(comm);
}
/// <summary>
/// 根据节点ID查询行为信息
/// </summary>
/// yand 15.12.03
/// qiy 16.03.08
/// <param name="nodeId">节点ID</param>
/// <returns></returns>
public List <ActionInfo> FindByNode(int nodeId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT ActionId, NodeId, Transfer, Name, Type, AllocationType, Description, Method FROM FLOW_Action WHERE NodeId = @NodeId
");
DHelper.AddParameter(comm, "@NodeId", SqlDbType.Int, nodeId);
return(LoadAll(DHelper.ExecuteDataTable(comm).Rows));
}
/// <summary>
/// 通过授信主体删除
/// </summary>
/// qiy 16.03.30
/// <param name="creditId"></param>
/// <returns></returns>
public void DeleteByCredit(int creditId)
{
SqlCommand comm = DHelper.GetSqlCommand(
"DELETE CRET_BindProduce WHERE CreditId = @CreditId"
);
DHelper.AddParameter(comm, "@CreditId", SqlDbType.Int, creditId);
DHelper.ExecuteNonQuery(comm);
}
/// <summary>
/// 删除文件
/// </summary>
/// qiy 16.04.05
/// <param name="fileId">文件标识</param>
/// <returns></returns>
public int Delete(int fileId)
{
SqlCommand comm = DHelper.GetSqlCommand(
"DELETE SYS_FileList WHERE FL_ID = @FileId"
);
DHelper.AddParameter(comm, "@FileId", SqlDbType.Int, fileId);
return(DHelper.ExecuteNonQuery(comm));
}
/// <summary>
/// 删除菜单
/// </summary>
/// yand 15.11.24
/// <param name="menuId"></param>
/// <returns></returns>
public void Delete(int menuId)
{
SqlCommand comm = DHelper.GetSqlCommand(
"Delete SYS_Menu WHERE MN_ID = @MenuId"
);
DHelper.AddParameter(comm, "@MenuId", SqlDbType.Int, menuId);
DHelper.ExecuteNonQuery(comm);
}
/// <summary>
/// 删除
/// </summary>
/// yaoy 16.08.04
/// <param name="userId"></param>
/// <returns></returns>
public int Delete(int userId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
DELETE FROM CRET_Account WHERE UserId = @UserId
" );
DHelper.AddParameter(comm, "@UserId", SqlDbType.Int, userId);
return(DHelper.ExecuteNonQuery(comm));
}
/// <summary>
/// 查找子菜单的数量
/// </summary>
/// qiy 16.03.09
/// <param name="menuId">菜单标识</param>
/// <returns></returns>
public int CountChildren(int menuId)
{
SqlCommand comm = DHelper.GetSqlCommand(
"SELECT COUNT(*) FROM SYS_Menu WHERE ParentId = @ParentId"
);
DHelper.AddParameter(comm, "@ParentId", SqlDbType.Int, menuId);
return(Convert.ToInt32(DHelper.ExecuteScalar(comm)));
}
/// <summary>
/// 通过用户删除
/// </summary>
/// qiy 16.03.09
/// <param name="userId">用户标识</param>
/// <returns></returns>
public void DeleteByUser(int userId)
{
SqlCommand comm = DHelper.GetSqlCommand(
"DELETE USER_Relation WHERE UserId = @UserId"
);
DHelper.AddParameter(comm, "@UserId", SqlDbType.Int, userId);
DHelper.ExecuteNonQuery(comm);
}
/// <summary>
/// 删除
/// </summary>
/// qiy 16.04.11
/// <param name="financeId">融资标识</param>
/// <returns></returns>
public int Delete(int financeId)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
DELETE FANC_VehicleInfo WHERE FinanceId = @FinanceId
" );
DHelper.AddParameter(comm, "@FinanceId", SqlDbType.Int, financeId);
return(DHelper.ExecuteNonQuery(comm));
}
/// <summary>
/// 删除文件
/// </summary>
/// cais 16.04.08
/// <param name="referenceId">文件引用id</param>
public void Delete(int referenceId)
{
SqlCommand comm = DHelper.GetSqlCommand(
"DELETE SYS_FileList WHERE ReferenceId=@ReferenceId"
);
DHelper.AddParameter(comm, "@ReferenceId", SqlDbType.Int, referenceId);
DHelper.ExecuteNonQuery(comm);
}
/// <summary>
/// 查找用户名
/// </summary>
/// qiy 15.11.25
/// <param name="username">用户名</param>
/// <returns></returns>
public int FindByUsername(string username)
{
SqlCommand comm = DHelper.GetSqlCommand(
"SELECT COUNT(*) FROM USER_UserInfo WHERE Username = @Username"
);
DHelper.AddParameter(comm, "@Username", SqlDbType.NVarChar, username);
return(Convert.ToInt32(DHelper.ExecuteScalar(comm)));
}
/// <summary>
/// 查找主要合同号个数
/// </summary>
/// cais 16.05.09
/// <param name="all">ContractMainCode[主合同号]</param>
/// <returns></returns>
public DataTable Find(int financeid)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT ContractMainCode FROM FANC_Contracts WHERE FinanceId=@FinanceId
");
DHelper.AddParameter(comm, "@FinanceId", SqlDbType.NVarChar, financeid);
return(DHelper.ExecuteDataTable(comm));
}
/// <summary>
/// 查找城市代码，用于生成合同
/// </summary>
/// cais 16.05.09
/// <returns></returns>
public DataTable Find(string citycode)
{
SqlCommand comm = DHelper.GetSqlCommand(@"
SELECT CityCode , CityName FROM CRET_Cities WHERE CityCode=@CityCode
");
DHelper.AddParameter(comm, "@CityCode", SqlDbType.NVarChar, citycode);
return(DHelper.ExecuteDataTable(comm));
}
