public string GetRZInfoByRZID(string RZID, string UserName) { string reply = null; var sql = string.Format(@"select b.Quota,a.BankIds,e.[Desc],d.[Desc],(case b.HadCollateral when '0' then'无' when '1' then '有' else '无' end) dyw,b.CollateralDesc from RZFlow a left join RZDemandInfo b on a.DemandId = b.Id left join Enterprise c on a.EnterpriseId = c.ID left join (select Id,[Type],[Desc] from Lookup where Type = 9) d on b.PurposeId = d.Id left join (select Id,[Type],[Desc] from Lookup where Type = 8) e on b.TermId = e.Id where a.Id = {0}", RZID); var dt = DBHelper.GetDataSet(sql); if (dt.Rows.Count > 0) { var dr = dt.Rows[0]; var banks = BankInfoService.GetBanks(); var efi = new RzInfoReply { RZED = dr[0].ToString(), //RZYH = string.Join(",", // dr["BankIds"].ToString().Split(',').Select(int.Parse) // .Select(p => banks.ContainsKey(p) ? banks[p] : "")), RZYH = GetBankNamesByBankIds(dr["BankIds"].ToString()), RZQX = dr[2].ToString(), RZQT = dr[3].ToString(), isdyw = dr[4].ToString(), dywdesc = dr[5].ToString() == "" ? "无" : dr[5].ToString() }; reply = JsonHelper.SerializeObject(efi); } else { reply = ""; } return(reply); }
public string GetRZDataTable(string UserName, int page, int rows) { var sql = @"select BankId,RolesID from SysUser where UserName = '******'"; int BankId = 0; int RoleId = 0; int ParentBankId = 0; DataTable dt = DBHelper.GetDataSet(sql); if (dt.Rows.Count > 0) { DataRow dr = dt.Rows[0]; BankId = dr["BankId"] == DBNull.Value ? 0 : int.Parse(dr["BankId"].ToString()); RoleId = dr["RolesID"] == DBNull.Value ? 0 : int.Parse(dr["RolesID"].ToString()); if (BankId != 0) { sql = "select ParentBankId from Bank where Id = '" + BankId + "'"; dt = DBHelper.GetDataSet(sql); dr = dt.Rows[0]; ParentBankId = dr["ParentBankId"] == DBNull.Value ? 0 : int.Parse(dr["ParentBankId"].ToString()); } } else { return(""); } if (ParentBankId != 0) { sql = @"select a.Id,a.EnterpriseId,c.Name,b.Quota,CONVERT(varchar(100), PublishDate, 23) as PublishDate,a.Status,a.BankIds,a.SLBankId,d.Name as SLBankName,b.CreditAmount,CONVERT(varchar(100), b.CreditDate, 23) as CreditDate,a.Status as cz from RZFlow a left join RZDemandInfo b on a.DemandId = b.Id left join Enterprise c on a.EnterpriseId = c.ID left join Bank d on a.SLBankId = d.Id where BankIds like '%" + ParentBankId + "%' and a.IsDeleted=0 order by PublishDate desc"; var dt1 = DBHelper.GetDataSet(sql); if (dt1.Rows.Count == 0) { return(""); } DataTable dtclone = new DataTable(); dtclone = dt1.Copy(); int dt1index = 0; foreach (DataRow dr in dt1.Rows) { string[] bankids = dr["BankIds"].ToString().Split(','); bool iscz = false; if (bankids.Length > 0) { for (int i = 0; i < bankids.Length; i++) { if (int.Parse(bankids[i]) == ParentBankId) { iscz = true; break; } } if (iscz == false) { dtclone.Rows.RemoveAt(dt1index); } } dt1index++; } return(JsonHelper.TableToJson(dtclone.Rows.Count, GetPagedTable(dtclone, page, rows))); } if (RoleId == 1 || RoleId == 2) // 系统用户或政府部门 { sql = @"select a.Id,a.EnterpriseId,c.Name,b.Quota,CONVERT(varchar(100), a.PublishDate, 23) as PublishDate,a.Status,a.BankIds,d.Name as SLBankName,b.CreditAmount,CONVERT(varchar(100), b.CreditDate, 23) as CreditDate from RZFlow a left join RZDemandInfo b on a.DemandId = b.Id left join Enterprise c on a.EnterpriseId = c.ID left join Bank d on a.SLBankId = d.Id where a.IsDeleted=0 order by PublishDate desc"; var banks = BankInfoService.GetBanks(); var dt1 = DBHelper.GetDataSet(sql); if (dt1.Rows.Count == 0) { return(""); } dt1.Columns.Add("Banks"); foreach (DataRow dr in dt1.Rows) { //dr["Banks"] = string.Join(",", // dr["BankIds"].ToString().Split(',').Select(int.Parse) // .Select(p => banks.ContainsKey(p) ? banks[p] : "")); dr["Banks"] = GetBankNamesByBankIds(dr["BankIds"].ToString()); } return(JsonHelper.TableToJson(dt1.Rows.Count, GetPagedTable(dt1, page, rows))); } return(""); }