/// <summary>
/// 功能：执行查询SQL语句
/// </summary>
/// <returns>返回结果集</returns>
public DataTable SearchSql(String sql)
{
JCommand command = new JCommand(daConnect);
command.CommandText = sql;
DataTable dt1 = command.SearchData(-1).Tables[0];
command.Close();
return(dt1);
}
/// <summary>
/// 得到银行的资产
/// </summary>
/// <returns></returns>
private DataSet GetZcForDb(String DbName, bool AllZc)
{
DataSet ds1 = null;
List <SearchField> condition = new List <SearchField>();
WebFrame.Data.JConnect conn1 = WebFrame.Data.JConnect.GetConnect(DbName);
//1－单位名称
if (this.danwei.Text.Trim() != "")
{
condition.Add(new SearchField("u_zc.danwei", this.danwei.Text.Trim(), SearchOperator.Contains));
}
//2－档案编号
if (this.num2.Text.Trim() != "")
{
condition.Add(new SearchField("num2", this.num2.Text.Trim()));
}
if (this.depart.SelectedValue != String.Empty || this.zeren.SelectedValue != String.Empty)
{
//4－责任人
if (this.zeren.SelectedValue != "")
{
condition.Add(new SearchField("zeren", this.zeren.SelectedValue));
}
else
{
//3－责任部门
if (this.depart.SelectedValue != "")
{
condition.Add(new SearchField("depart", this.depart.SelectedValue));
}
}
}
if (condition.Count > 0)
{
JCommand comm1 = new JCommand(conn1);
String sql = null;
if (AllZc == false)
{
sql = @"select distinct u_zc.id,num2,u_zc.danwei,bj,isnull(lx1,0)+isnull(lx2,0)+isnull(lx3,0) lx ,zeren,zeren1,U_ZC2SearchView2.spstatus,
U_ZC2SearchView2.status1,U_ZC2SearchView2.status2,U_ZC2SearchView2.spresult,U_ZC2SearchView2.spdotime,
bj+isnull(lx1,0)+isnull(lx2,0)+isnull(lx3,0) bjlx
from u_zc left outer join U_ZC2SearchView2 on U_ZC.id=U_ZC2SearchView2.zcid where not exists (select * from u_zcbaoinfo where zcid=u_zc.id) ";
}
else
{
sql = @"select distinct u_zc.id,num2,u_zc.danwei,bj,isnull(lx1,0)+isnull(lx2,0)+isnull(lx3,0) lx ,zeren,zeren1,U_ZC2SearchView2.spstatus,
U_ZC2SearchView2.status1,U_ZC2SearchView2.status2,U_ZC2SearchView2.spresult,U_ZC2SearchView2.spdotime,
bj+isnull(lx1,0)+isnull(lx2,0)+isnull(lx3,0) bjlx
from u_zc left outer join U_ZC2SearchView2 on U_ZC.id=U_ZC2SearchView2.zcid where 1=1 ";
}
if (condition != null && condition.Count > 0)
{
String conditionStr = SearchField.GetSearchCondition(condition);
if (String.IsNullOrEmpty(conditionStr) == false)
{
sql = sql + " and " + conditionStr;
}
}
sql = sql + " order by num2 ";
comm1.CommandText = sql;
ds1 = comm1.SearchData(-1);
comm1.Close();
WebFrame.Data.JConnect.CloseConnect();
}
return(ds1);
}
/// <summary>
/// 得到Bug的统计结果
/// tjkind=0 按登记人 tjkind=1 按责任人
/// </summary>
/// <param name="tjkind"></param>
/// <param name="time0"></param>
/// <param name="time1"></param>
/// <returns></returns>
public DataTable GetTjResult(String tjkind, String time0, String time1, String status)
{
DataTable dt1 = new DataTable();
dt1.Columns.Add("doman");
dt1.Columns.Add("A", typeof(int));
dt1.Columns.Add("B", typeof(int));
dt1.Columns.Add("C", typeof(int));
dt1.Columns.Add("TC", typeof(double));
List <SearchField> condition = new List <SearchField>();
if (String.IsNullOrEmpty(status) == false)
{
condition.Add(new SearchField("Status", status));
}
if (String.IsNullOrEmpty(time0) == false)
{
condition.Add(new SearchField(String.Format("AddTime>=cdate('{0}')", time0), "", SearchOperator.UserDefine));
}
if (String.IsNullOrEmpty(time1) == false)
{
condition.Add(new SearchField(String.Format("AddTime<=cdate('{0} 23:59:59')", time1), "", SearchOperator.UserDefine));
}
//统计数据
String commdText = String.Empty;
JCommand comm1 = new JCommand();
if (tjkind == "0")
{
commdText = "select AddUser as domanID,testman as domanName,BugKind,count(*) as count1 from Tb_DebugView";
if (condition.Count > 0)
{
commdText = commdText + " where " + SearchField.GetSearchCondition(condition);
}
commdText = commdText + " group by AddUser,testman,BugKind";
}
else
{
commdText = "select executeMan as domanID,zrr as domanName,BugKind,count(*) as count1 from Tb_DebugView";
if (condition.Count > 0)
{
commdText = commdText + " where " + SearchField.GetSearchCondition(condition);
}
commdText = commdText + " group by executeMan,zrr,BugKind";
}
comm1.CommandText = commdText;
DataTable dt0 = comm1.SearchData(-1).Tables[0];
String doman1 = String.Empty;
DataRow dr1 = null;
foreach (DataRow dr0 in dt0.Rows)
{
if (doman1 != dr0["domanID"].ToString())
{
if (dr1 != null)
{
dt1.Rows.Add(dr1);
}
dr1 = dt1.NewRow();
dr1["doman"] = dr0["domanName"];
dr1["A"] = 0;
dr1["B"] = 0;
dr1["C"] = 0;
dr1[dr0["BugKind"].ToString()] = int.Parse(dr0["count1"].ToString());
}
else
{
dr1[dr0["BugKind"].ToString()] = int.Parse(dr0["count1"].ToString());
}
}
if (dr1 != null)
{
dt1.Rows.Add(dr1);
}
return(dt1);
}
/// <summary>
/// 系统升级
/// </summary>
public static void Upgrade()
{
String time1 = ConfigurationManager.AppSettings["gradeDate"];
if (String.IsNullOrEmpty(time1) == false)
{
DateTime t1 = DateTime.Parse(time1);
if (DateTime.Today <= t1)
{
JConnect conn1 = JConnect.GetConnect("DefaultConnstring");
JCommand comm1 = new JCommand(conn1);
comm1.CommandText = "select * from u_zc1 where 0=1";
DataTable dt1 = comm1.SearchData(-1).Tables[0];
//1--增加保证合同字段
if (dt1.Columns.Contains("bzhtong") == false)
{
String sql = "alter table u_zc1 add bzhtong nvarchar(200)";
comm1.CommandText = sql;
comm1.ExecuteNoQuery();
}
//2--抵押物
if (dt1.Columns.Contains("dyw") == false)
{
String sql = "alter table u_zc1 add dyw nvarchar(200)";
comm1.CommandText = sql;
comm1.ExecuteNoQuery();
}
//3--是否有抵押合同
if (dt1.Columns.Contains("sfydyht") == false)
{
String sql = "alter table u_zc1 add sfydyht nvarchar(200)";
comm1.CommandText = sql;
comm1.ExecuteNoQuery();
}
//4--对应抵押金额
if (dt1.Columns.Contains("dyje") == false)
{
String sql = "alter table u_zc1 add dyje nvarchar(200)";
comm1.CommandText = sql;
comm1.ExecuteNoQuery();
}
//5--抵押是否有效
if (dt1.Columns.Contains("dysfyx") == false)
{
String sql = "alter table u_zc1 add dysfyx nvarchar(200)";
comm1.CommandText = sql;
comm1.ExecuteNoQuery();
}
//6--抵押文件
if (dt1.Columns.Contains("dywj") == false)
{
String sql = "alter table u_zc1 add dywj nvarchar(200)";
comm1.CommandText = sql;
comm1.ExecuteNoQuery();
}
//7--抵押物评估报告
if (dt1.Columns.Contains("dypgbg") == false)
{
String sql = "alter table u_zc1 add dypgbg nvarchar(200)";
comm1.CommandText = sql;
comm1.ExecuteNoQuery();
}
JConnect.CloseConnect();
}
}
}