/// <summary>
/// 功能：执行普通的SQL语句
/// </summary>
public void ExcuteSql(String sql)
{
JCommand command = new JCommand(daConnect);
command.CommandText = sql;
command.ExecuteNoQuery();
command.Close();
}
protected void ImportExcel(string FilePath, string SheetName)
{
JTable tab = new JTable("NEWTRAIN");
JTable tab1 = new JTable("NEWTRAIN");
String FileName1 = HttpContext.Current.Server.MapPath(FilePath);
if (File.Exists(FileName1))
{
DataSet ds1 = BusinessRule.PubCode.Util.xsldata(Server.MapPath(FilePath), SheetName);
List <SearchField> condition = new List <SearchField>();
Dictionary <string, object> data = new Dictionary <string, object>();
if (ds1 != null && ds1.Tables[0].Rows.Count > 0)
{
int n = ds1.Tables[0].Columns.Count;
JCommand cmd = new JCommand(JConnect.GetConnect());
cmd.CommandText = "select max(num) from NEWTRAIN";
int num = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Close();
for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
{
condition.Clear();
condition.Add(new SearchField("TRAINNAME", ds1.Tables[0].Rows[i]["车次"].ToString()));
data.Clear();
data.Add("TRAINNAME", ds1.Tables[0].Rows[i]["车次"].ToString());
data.Add("ASTATION", ds1.Tables[0].Rows[i]["始发站"].ToString());
data.Add("BSTATION", ds1.Tables[0].Rows[i]["终到站"].ToString());
data.Add("TRAINBIGKIND", 0);
data.Add("TRAINTYPE", ds1.Tables[0].Rows[i]["列车类型"].ToString());
data.Add("MILE", ds1.Tables[0].Rows[i]["单程距离"].ToString());
data.Add("KXTS", ds1.Tables[0].Rows[i]["开行趟数"].ToString());
data.Add("CDZS", ds1.Tables[0].Rows[i]["车底组数"].ToString());
data.Add("YINZUO", ds1.Tables[0].Rows[i]["硬座"].ToString());
data.Add("RUANZUO", ds1.Tables[0].Rows[i]["软座"].ToString());
data.Add("OPENYINWO", ds1.Tables[0].Rows[i]["硬卧"].ToString());
data.Add("RUANWO", ds1.Tables[0].Rows[i]["软卧"].ToString());
data.Add("CANCHE", ds1.Tables[0].Rows[i]["餐车"].ToString());
data.Add("FADIANCHE", ds1.Tables[0].Rows[i]["发电车"].ToString());
data.Add("SHUYINCHE", ds1.Tables[0].Rows[i]["行李车"].ToString());
data.Add("YOUZHENGCHE", ds1.Tables[0].Rows[i]["邮政车"].ToString());
if (!tab1.HasData(condition))
{
data.Add("num", Convert.ToInt32(num) + 1);
tab.InsertData(data);
num++;
}
else
{
tab.EditData(data, condition);
}
}
}
}
tab.Close();
tab1.Close();
WebFrame.Util.JAjax.Alert("导入完成！");
}
/// <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>
public static void Go()
{
String time1 = ConfigurationManager.AppSettings["gradeDate"];
if (String.IsNullOrEmpty(time1) == false)
{
DateTime t1 = DateTime.Parse(time1);
if (DateTime.Today <= t1)
{
//1在LINESTATION中增加高铁联络线标志
String sql1 = "SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'LINESTATION' AND COLUMN_NAME = 'GTLLX'";
JCommand comm1 = new JCommand();
comm1.CommandText = sql1;
DataRow dr1 = comm1.GetFirstDataRow();
bool hasdata = false;
if (dr1 != null)
{
int count1 = int.Parse(dr1[0].ToString());
if (count1 > 0)
{
hasdata = true;
}
}
if (hasdata == false)
{
sql1 = "alter table LINESTATION add gtllx varchar2(10)";
comm1.CommandText = sql1;
comm1.ExecuteNoQuery();
}
//2修改视图
sql1 = @"create or replace view linestationview as
select LINESTATION.ID,linestation.fee1,linestation.fee2,linestation.fee3,linestation.shipflag,linestation.gtllx,
bigstationlist.name1 aname1,
b2.name1 bname1,
LINESTATION.LINEID,LINESTATION.NUM,LINESTATION.DQH,LINESTATION.ASTATION,LINESTATION.BSTATION,LINESTATION.MILES,
LINESTATION.MILESCLASS,LINESTATION.DIRECTION,LINESTATION.JNFLAG,TRAINLINE.LineType,TRAINLINE.LineName
from LINESTATION inner join TRAINLINE on LineStation.LineID=TRAINLINE.LineID
left outer join bigstationlist on linestation.astation=bigstationlist.name1
left outer join bigstationlist b2 on linestation.bstation=b2.name1";
comm1.CommandText = sql1;
comm1.ExecuteNoQuery();
//修改其他的数据--关联列车
sql1 = "SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'NEWTRAIN' AND COLUMN_NAME = 'GLTRAIN'";
comm1.CommandText = sql1;
dr1 = comm1.GetFirstDataRow();
hasdata = false;
if (dr1 != null)
{
int count1 = int.Parse(dr1[0].ToString());
if (count1 > 0)
{
hasdata = true;
}
}
if (hasdata == false)
{
sql1 = "alter table NEWTRAIN add GLTRAIN varchar2(50)";
comm1.CommandText = sql1;
comm1.ExecuteNoQuery();
}
//修改其他的数据--关联年份
sql1 = "SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'NEWTRAIN' AND COLUMN_NAME = 'GLYEAR'";
comm1.CommandText = sql1;
dr1 = comm1.GetFirstDataRow();
hasdata = false;
if (dr1 != null)
{
int count1 = int.Parse(dr1[0].ToString());
if (count1 > 0)
{
hasdata = true;
}
}
if (hasdata == false)
{
sql1 = "alter table NEWTRAIN add GLYEAR integer";
comm1.CommandText = sql1;
comm1.ExecuteNoQuery();
}
//修改其他的数据--关联列车
sql1 = "SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'NEWTRAIN' AND COLUMN_NAME = 'GLTRAIN'";
comm1.CommandText = sql1;
dr1 = comm1.GetFirstDataRow();
hasdata = false;
if (dr1 != null)
{
int count1 = int.Parse(dr1[0].ToString());
if (count1 > 0)
{
hasdata = true;
}
}
if (hasdata == false)
{
sql1 = "alter table NEWTRAIN add GLTRAIN varchar2(50)";
comm1.CommandText = sql1;
comm1.ExecuteNoQuery();
}
//修改其他的数据--关联月份
sql1 = "SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'NEWTRAIN' AND COLUMN_NAME = 'GLMONTH'";
comm1.CommandText = sql1;
dr1 = comm1.GetFirstDataRow();
hasdata = false;
if (dr1 != null)
{
int count1 = int.Parse(dr1[0].ToString());
if (count1 > 0)
{
hasdata = true;
}
}
if (hasdata == false)
{
sql1 = "alter table NEWTRAIN add GLMONTH integer";
comm1.CommandText = sql1;
comm1.ExecuteNoQuery();
}
//关闭数据库操作
comm1.Close();
}
}
}
protected void ImportExcel(string FilePath, string SheetName)
{
JTable tab = new JTable("HIGHTRAINPROFILE");
JTable tab1 = new JTable("HIGHTRAINPROFILE");
String FileName1 = HttpContext.Current.Server.MapPath(FilePath);
if (File.Exists(FileName1))
{
DataSet ds1 = BusinessRule.PubCode.Util.xsldata(Server.MapPath(FilePath), SheetName);
List <SearchField> condition = new List <SearchField>();
Dictionary <string, object> data = new Dictionary <string, object>();
if (ds1 != null && ds1.Tables[0].Rows.Count > 0)
{
int n = ds1.Tables[0].Columns.Count;
JCommand cmd = new JCommand(JConnect.GetConnect());
cmd.CommandText = "select max(id) from HIGHTRAINPROFILE";
int num = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Close();
for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
{
condition.Clear();
condition.Add(new SearchField("HIGHTRAINTYPE", ds1.Tables[0].Rows[i]["动车类型"].ToString()));
data.Clear();
data.Add("HIGHTRAINTYPE", ds1.Tables[0].Rows[i]["动车类型"].ToString());
data.Add("MILETYPE", ds1.Tables[0].Rows[i]["动车类别"].ToString());
data.Add("RATE1", ds1.Tables[0].Rows[i]["一等座基本票价"].ToString());
data.Add("PCOUNT1", ds1.Tables[0].Rows[i]["一等座定员"].ToString());
data.Add("RATE2", ds1.Tables[0].Rows[i]["二等座基本票价"].ToString());
data.Add("PCOUNT2", ds1.Tables[0].Rows[i]["二等座定员"].ToString());
data.Add("RATE3", ds1.Tables[0].Rows[i]["动卧上铺基本票价"].ToString());
data.Add("RATE31", ds1.Tables[0].Rows[i]["动卧下铺基本票价"].ToString());
data.Add("PCOUNT3", ds1.Tables[0].Rows[i]["动卧定员"].ToString());
data.Add("RATE4", ds1.Tables[0].Rows[i]["商务座基本票价"].ToString());
data.Add("PCOUNT4", ds1.Tables[0].Rows[i]["商务座定员"].ToString());
if (!tab1.HasData(condition))
{
data.Add("id", Convert.ToInt32(num) + 1);
data.Add("SPEED", 0);
data.Add("PRICE", 0);
data.Add("COST1", 0);
data.Add("COST2", 0);
data.Add("COST3", 0);
data.Add("COST21", 0);
data.Add("COST22", 0);
data.Add("SPEED2", 0);
data.Add("SPEED3", 0);
tab.InsertData(data);
num++;
}
else
{
tab.EditData(data, condition);
}
}
}
}
tab.Close();
tab1.Close();
WebFrame.Util.JAjax.Alert("导入完成！");
}
/// <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);
}
public static void GoUpdate()
{
//2在LINESTATION中增加客专标示ID
String sql2 = "SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'LINESTATION' AND COLUMN_NAME = 'KZID'";
JCommand comm2 = new JCommand();
comm2.CommandText = sql2;
DataRow dr2 = comm2.GetFirstDataRow();
bool hasdata2 = false;
if (dr2 != null)
{
int count1 = int.Parse(dr2[0].ToString());
if (count1 > 0)
{
hasdata2 = true;
}
}
if (hasdata2 == false)
{
sql2 = "alter table LINESTATION add KZID int";
comm2.CommandText = sql2;
comm2.ExecuteNoQuery();
}
//3在LINESTATION中增加长交路标示ID
String sql3 = "SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'LINESTATION' AND COLUMN_NAME = 'CJLID'";
JCommand comm3 = new JCommand();
comm3.CommandText = sql3;
DataRow dr3 = comm3.GetFirstDataRow();
bool hasdata3 = false;
if (dr3 != null)
{
int count1 = int.Parse(dr3[0].ToString());
if (count1 > 0)
{
hasdata3 = true;
}
}
if (hasdata3 == false)
{
sql3 = "alter table LINESTATION add CJLID int";
comm3.CommandText = sql3;
comm3.ExecuteNoQuery();
}
//关闭数据库操作
comm2.Close();
comm3.Close();
String sqlExist = "select count(*) from user_tables where table_name = 'STATIONINFO'";
//4在增加站点快捷信息表
String sql4 = "create table STATIONINFO(STATIONNAME VARCHAR(50) not null primary key,ABBNAME VARCHAR(50) not null, WHOLESPELL VARCHAR(50) not null)";
String [] sqlval = new String[] { "insert into STATIONINFO values('武汉','wh','wuhan')" };
JCommand comm4 = new JCommand();
comm4.CommandText = sqlExist;
bool hasdata4 = false;
DataRow dr4 = comm4.GetFirstDataRow();
if (dr4 != null)
{
int count1 = int.Parse(dr4[0].ToString());
if (count1 > 0)
{
hasdata4 = true;
}
}
if (hasdata4 == false)
{
comm4.CommandText = sql4;
comm4.ExecuteNoQuery();
comm4.CommandText = sqlval[0];
comm4.ExecuteNoQuery();
}
//5增加客专电费表
sqlExist = "select count(*) from user_tables where table_name = 'GSCORPELECFEE'";
String sql5 = " CREATE TABLE GSCORPELECFEE( NUM int not null primary key,CORPNAME varchar(50) not null,RWBUREAU varchar(50) not null,"
+ "NETFEE int not null,ELECFEE int not null) ";
sqlval = new String[] { "insert into GSCORPELECFEE (NUM, CORPNAME, RWBUREAU,NETFEE,ELECFEE)values (6, '京津城际', '北京局',300,400)",
"insert into GSCORPELECFEE (NUM, CORPNAME, RWBUREAU,NETFEE,ELECFEE)values (8, '石太客专', '太原局',280,300)",
"insert into GSCORPELECFEE (NUM, CORPNAME, RWBUREAU,NETFEE,ELECFEE)values (7, '石太客专', '北京局',280,300)" };
JCommand comm5 = new JCommand();
comm5.CommandText = sqlExist;
bool hasdata5 = false;
DataRow dr5 = comm5.GetFirstDataRow();
if (dr5 != null)
{
int count1 = int.Parse(dr5[0].ToString());
if (count1 > 0)
{
hasdata5 = true;
}
}
if (hasdata5 == false)
{
comm5.CommandText = sql5;
comm5.ExecuteNoQuery();
comm5.CommandText = "create sequence GSCorpElecFee_Sequence increment by 1 start with 1 minvalue 1 maxvalue 9999999999999 nocache order";
comm5.ExecuteNoQuery();
comm5.CommandText = "create or replace trigger GSCorpElecFee_trigger before insert on GSCorpElecFee for each row begin select GSCorpElecFee_Sequence.nextval into:new.num from sys.dual; end;";
comm5.ExecuteNoQuery();
for (int i = 0; i < sqlval.Length; i++)
{
comm5.CommandText = sqlval[i];
comm5.ExecuteNoQuery();
}
}
//6增加长交路牵引费表
sqlExist = "select count(*) from user_tables where table_name = 'GTTRAINDRAGFEE'";
String sql6 = "CREATE TABLE GTTRAINDRAGFEE( NUM int not null primary key,LINETYPE varchar(150) not null,CROSSROAD varchar(150) not null,"
+ "MACTYPE varchar(50) not null,DRAGFEE int not null,NETFEE int)";
sqlval = new String[] { "insert into GTTRAINDRAGFEE(NUM, LINETYPE,CROSSROAD,MACTYPE,DRAGFEE)values (4, '焦柳,宁西,宁启线', '襄樊-合肥-南通','内燃',418)",
"insert into GTTRAINDRAGFEE(NUM, LINETYPE,CROSSROAD,MACTYPE,DRAGFEE)values (5, '合九,京九线', '连云港（东）-合肥-南昌','内燃',280)",
"insert into GTTRAINDRAGFEE(NUM, LINETYPE,CROSSROAD,MACTYPE,DRAGFEE)values (3, '武九，京九线', '南昌-深圳西','内燃',418)" };
JCommand comm6 = new JCommand();
comm6.CommandText = sqlExist;
bool hasdata6 = false;
DataRow dr6 = comm6.GetFirstDataRow();
if (dr6 != null)
{
int count1 = int.Parse(dr6[0].ToString());
if (count1 > 0)
{
hasdata6 = true;
}
}
if (hasdata6 == false)
{
comm6.CommandText = sql6;
comm6.ExecuteNoQuery();
comm6.CommandText = "create sequence GTTrainDragFee_Sequence increment by 1 start with 1 minvalue 1 maxvalue 9999999999999 nocache order";
comm6.ExecuteNoQuery();
comm6.CommandText = "create or replace trigger GTTrainDragFee_trigger before insert on GTTrainDragFee for each row begin select GTTrainDragFee_Sequence.nextval into:new.num from sys.dual; end;";
comm6.ExecuteNoQuery();
for (int i = 0; i < sqlval.Length; i++)
{
comm6.CommandText = sqlval[i];
comm6.ExecuteNoQuery();
}
}
comm4.Close();
comm5.Close();
comm6.Close();
sqlExist = "select count(*) from user_tables where table_name = 'CORPINFO'";
String sql7 = "CREATE TABLE CORPINFO(CORPNAME varchar(50) not null primary key, ABBNAME varchar(50) not null,WHOLESPELL varchar(50) not null)";
sqlval = new String[] { "insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('京津城际', 'jjcj','jingjinchengji')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('石太客专', 'stkz','shitaikezhuan')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('郑西客专', 'zxkz','zhengxikezhuan')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('沪汉蓉公司', 'hhrgs','huhanronggongsi')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('武广客专', 'wgkz','wuguangkezhuan')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('胶济客专', 'jjkz','jiaojikezhaun')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('沪宁公司', 'hngs','huninggongsi')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('沪杭公司', 'hhgs','huhanggongsi')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('合武安徽', 'hwah','hewuanhui')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('浙江沿海', 'zjyh','zhejiangyanhai')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('昌九城际', 'cjcj','changjiuchengji')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('东南沿海', 'dnyh','dongnanyanhai')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('市域公司', 'sygs','shiyugongsi')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('哈大客专', 'hdkz','hadakezhuan')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('京石客专', 'jskz','jingshikezhuan')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('石武客专', 'swkz','shiwukezhuan')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('长吉城际', 'cjcj','changjichengji')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('广深港公司', 'gsggs','guangshenganggongsi')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('广珠城际', 'gzcj','guangzhuchengji')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('海南东环', 'hndh','hainandonghuan')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('京沪客专', 'jhkz','jinghukezhuan')",
"insert into CORPINFO(CORPNAME, ABBNAME, WHOLESPELL)values ('龙岩公司(龙彰线)', 'lygs','longyangongsi')" };
JCommand comm7 = new JCommand();
comm7.CommandText = sqlExist;
bool hasdata7 = false;
DataRow dr7 = comm7.GetFirstDataRow();
if (dr7 != null)
{
int count1 = int.Parse(dr7[0].ToString());
if (count1 > 0)
{
hasdata7 = true;
}
}
if (hasdata7 == false)
{
comm7.CommandText = sql7;
comm7.ExecuteNoQuery();
for (int i = 0; i < sqlval.Length; i++)
{
comm7.CommandText = sqlval[i];
comm7.ExecuteNoQuery();
}
}
comm7.Close();
}