public string GetLineName(string lineid)
{
JTable tab = new JTable("CHANGJIAOQYFEE");
tab.CommandText = "select * from CHANGJIAOQYFEE where LINEID = '" + lineid + "'";
if (tab.SearchData(-1).Tables[0].Rows.Count > 0)
{
return(tab.SearchData(-1).Tables[0].Rows[0]["LINENAME"].ToString().Trim());
}
else
{
return("");
}
}
/// <summary>
/// 得到长交路表示列表
/// </summary>
/// <returns></returns>
public DataTable GetcjlList()
{
JTable tab = new JTable("CHANGJIAOQYFEE");
tab.CommandText = "select * from CHANGJIAOQYFEE";
return(tab.SearchData(-1).Tables[0]);
}
//得到列表数据
public static DataSet GetPersonGwList()
{
DataSet ds1 = null;
JTable tab1 = new JTable("TrainPerson");
tab1.OrderBy = "kind,traintype,num";
ds1 = tab1.SearchData(null, -1, "*");
if (ds1 != null && ds1.Tables[0].Rows.Count > 0)
{
String first = ds1.Tables[0].Rows[0]["traintype"].ToString();
for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
{
DataRow dr1 = ds1.Tables[0].Rows[i];
String Second = dr1["traintype"].ToString();
if (Second == first && i != 0)
{
dr1["traintype"] = "";
}
else
{
first = Second;
}
}
}
tab1.Close();
return(ds1);
}
//设置编制的值
private void SetValue(JTable tab1, String gw, String traintype, double Value, String kind)
{
List <SearchField> condition = new List <SearchField>();
condition.Add(new SearchField("gw", gw));
condition.Add(new SearchField("traintype", traintype));
Dictionary <String, object> data1 = new Dictionary <string, object>();
DataSet ds = tab1.SearchData(condition, -1, "*");
if (ds != null)
{
DataRow dr1 = null;
if (ds.Tables[0].Rows.Count > 0)
{
dr1 = ds.Tables[0].Rows[0];
dr1["pcount"] = Value;
}
else
{
dr1 = ds.Tables[0].NewRow();
dr1["kind"] = kind;
dr1["gw"] = gw;
dr1["traintype"] = traintype;
dr1["pcount"] = Value;
ds.Tables[0].Rows.Add(dr1);
}
tab1.Update(ds.Tables[0]);
}
}
/// <summary>
/// 得到长交路表示列表
/// </summary>
/// <returns></returns>
public DataTable GetPayPrjList()
{
JTable tab = new JTable("PAYPROJ");
tab.CommandText = "select * from PAYPROJ";
return(tab.SearchData(-1).Tables[0]);
}
public static String GetBigList(String num)
{
String result = String.Empty;
if (String.IsNullOrEmpty(num) == false)
{
JTable tab1 = new JTable("BIGSTATIONLIST");
List <SearchField> condition = new List <SearchField>();
condition.Add(new SearchField("parentnum", num, SearchFieldType.NumericType));
DataSet ds1 = tab1.SearchData(condition, -1, "name1");
foreach (DataRow dr1 in ds1.Tables[0].Rows)
{
if (result == String.Empty)
{
result = dr1["name1"].ToString();
}
else
{
result = result + "," + dr1["name1"].ToString();
}
}
ds1.Dispose();
tab1.Close();
}
return(result);
}
/// </summary>
/// <param name="newPassWord"></param>
/// <returns></returns>
public bool UpdatePassWord(String UserName, String oldPassWord, String newPassWord)
{
bool succ = false;
if (String.IsNullOrEmpty(UserName) == false)
{
JTable tab1 = new JTable(TableName);
List <SearchField> condition = new List <SearchField>();
condition.Add(new SearchField("UserName", UserName));
DataSet ds1 = tab1.SearchData(condition, 1, "*");
if (ds1 != null && ds1.Tables[0].Rows.Count > 0)
{
DataRow dr1 = ds1.Tables[0].Rows[0];
if (JString.MD5(oldPassWord) == dr1["password"].ToString())
{
succ = true;
dr1["password"] = JString.MD5(newPassWord);
tab1.Update(ds1.Tables[0]);
}
}
ds1.Dispose();
tab1.Close();
}
return(succ);
}
/// <summary>
/// 根据项目ID，开始时间，结束时间，统计项目的费用
/// </summary>
/// <param name="projectGuid"></param>
/// <param name="Time0"></param>
/// <param name="Time1"></param>
/// <returns></returns>
public DataTable GetTj2Data(String projectGuid, String Time0, String Time1)
{
DataTable dt1 = new DataTable();
String sql1 = @"SELECT UserID, UserName, SUM(ProjectMoney) AS projectMoney
FROM Tb_Project_Money where 1=1 ";
if (String.IsNullOrEmpty(projectGuid) == false)
{
sql1 = sql1 + " and parentGuid='" + projectGuid + "'";
}
if (String.IsNullOrEmpty(Time0) == false)
{
sql1 = sql1 + String.Format(" and MoneyTime>=cdate('{0}') ", Time0);
}
if (String.IsNullOrEmpty(Time1) == false)
{
sql1 = sql1 + String.Format(" and MoneyTime<=cdate('{0} 23:59:59') ", Time1);
}
sql1 = sql1 + "GROUP BY UserID, UserName";
JTable tab1 = new JTable();
tab1.CommandText = sql1;
dt1 = tab1.SearchData(-1).Tables[0];
return(dt1);
}
/// <summary>
/// 从数据库中绑定数据
/// </summary>
public void BindDataFromDB()
{
DataTable dt1 = null;
JTable tab1 = new JTable(this.TableName);
if (String.IsNullOrEmpty(this.ParentID) == false)
{
List <SearchField> condition = new List <SearchField>();
condition.Add(new SearchField(this.ParentField, this.ParentID));
condition.Add(new SearchField("DEL", "0", SearchFieldType.NumericType));
tab1.OrderBy = this.OrderBy;
dt1 = tab1.SearchData(condition, -1, "*").Tables[0];
}
tab1.Close();
//增加数据的标识状态
dt1.Columns.Add("DataStatus");
dt1.PrimaryKey = new DataColumn[] { dt1.Columns["GUIDID"] };
//将数据保存到视图状态
this.AttachmentList = dt1;
//绑定List显示的数据
this.BindList();
}
/// <summary>
/// 得到客专标示列表
/// </summary>
/// <returns></returns>
public DataTable GetkzList(string corpname)
{
JTable tab = new JTable("GSCORPELECFEE");
if (corpname.Trim() == "" || corpname == null)
{
tab.CommandText = "select * from GSCORPELECFEE";
}
else
{
tab.CommandText = "select * from GSCORPELECFEE where CORPNAME = '" + corpname + "'";
}
DataTable dt = new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("Title");
DataTable dttemp = tab.SearchData(-1).Tables[0];
if (dttemp.Rows.Count > 0)
{
foreach (DataRow drtemp in dttemp.Rows)
{
DataRow dr = dt.NewRow();
dr["ID"] = drtemp["Num"];
dr["Title"] = string.Format("{0}({1})", drtemp["CORPNAME"].ToString(), drtemp["RWBUREAU"].ToString());
dt.Rows.Add(dr);
}
return(dt);
}
else
{
return(null);
}
}
public static void SetBigList(String num, String bigList)
{
if (String.IsNullOrEmpty(bigList) == false &&
String.IsNullOrEmpty(num) == false)
{
String[] str1 = bigList.Replace("，", ",").Split(',');
JTable tab1 = new JTable("BIGSTATIONLIST");
List <SearchField> condition = new List <SearchField>();
condition.Add(new SearchField("parentnum", num, SearchFieldType.NumericType));
tab1.DeleteData(condition);
DataSet ds1 = tab1.SearchData(condition, -1, "*");
int i = 1;
bool isEdit = false;
foreach (String m in str1)
{
if (isEdit == false)
{
isEdit = true;
}
DataRow dr1 = ds1.Tables[0].NewRow();
dr1["num"] = i;
dr1["parentnum"] = num;
dr1["name1"] = m;
i++;
ds1.Tables[0].Rows.Add(dr1);
}
if (isEdit)
{
tab1.Update(ds1.Tables[0]);
}
tab1.Close();
}
}
public static void SetData()
{
JTable tab1 = new JTable("TRAINLINEKINDPROFILE");
tab1.OrderBy = "lineid";
DataTable dt1 = tab1.SearchData(null, -1, "*").Tables[0];
if (dt1 != null)
{
foreach (DataRow dr1 in dt1.Rows)
{
String kind1 = dr1["lineID"].ToString();
if (kind1 == "0")
{
JieChuFee0 = double.Parse(dr1["JIECHUFEE"].ToString());
DianFee0 = double.Parse(dr1["DIANFEE"].ToString());
}
else if (kind1 == "1")
{
JieChuFee1 = double.Parse(dr1["JIECHUFEE"].ToString());
DianFee1 = double.Parse(dr1["DIANFEE"].ToString());
}
else if (kind1 == "2")
{
JieChuFee2 = double.Parse(dr1["JIECHUFEE"].ToString());
DianFee2 = double.Parse(dr1["DIANFEE"].ToString());
}
}
}
tab1.Close();
}
//得到列车类型
private static String[] GetTrainType(bool dongche)
{
String[] arr1 = null;
JTable tab1 = new JTable();
String fs = String.Empty;
if (dongche)
{
tab1.TableName = "hightrainprofile";
tab1.OrderBy = "id";
fs = "hightraintype";
}
else
{
tab1.TableName = "commtrainweightprofile";
tab1.OrderBy = "num";
fs = "traintype";
}
DataSet ds1 = tab1.SearchData(null, -1, fs);
if (ds1 != null && ds1.Tables[0].Rows.Count > 0)
{
arr1 = new String[ds1.Tables[0].Rows.Count];
for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
{
DataRow dr1 = ds1.Tables[0].Rows[i];
String text1 = dr1[0].ToString();
arr1[i] = text1;
}
}
tab1.Close();
return(arr1);
}
public static void SetData()
{
JTable tab1 = new JTable("JiaKuaiProfile");
tab1.OrderBy = "id";
DataTable dt = tab1.SearchData(null, -1, "*").Tables[0];
foreach (DataRow dr in dt.Rows)
{
String kind1 = dr["JIAKUAITYPE"].ToString().Trim();
if (kind1 == "jk1")
{
JkFee1 = double.Parse(dr["Fee"].ToString());
}
else if (kind1 == "jk2")
{
JkFee2 = double.Parse(dr["Fee"].ToString());
}
else if (kind1 == "jk3")
{
JkFee3 = double.Parse(dr["Fee"].ToString());
}
else
{
;
}
}
tab1.Close();
}
/// <summary>
/// 得到工单的人员测试比例，A为3B为2C为1
/// </summary>
/// <param name="TaskID"></param>
/// <returns></returns>
public Dictionary <String, double> GetTaskDebugRate(String TaskID)
{
Dictionary <String, double> dic1 = new Dictionary <string, double>();
String sql = "select AddUserID,sum(BugA*3+BugB*2+BugC) from Tb_Task_Bug where parentGuid='" + TaskID + "' group by AddUserID";
JTable tab1 = new JTable(daConnect, TableName);
tab1.CommandText = sql;
DataTable dt1 = tab1.SearchData(-1).Tables[0];
double t1 = 0;
foreach (DataRow dr1 in dt1.Rows)
{
dic1.Add(dr1[0].ToString(), double.Parse(dr1[1].ToString()));
t1 = t1 + double.Parse(dr1[1].ToString());
}
tab1.Close();
//t1>0
if (t1 > 0)
{
String[] keys = dic1.Keys.ToArray();
for (int i = 0; i < keys.Length; i++)
{
String m1 = keys[i];
dic1[m1] = Math.Round(dic1[m1] / t1, 2);
}
}
else
{
dic1.Clear();
}
return(dic1);
}
public static void SetData()
{
JTable tab1 = new JTable("HIGHTRAINPROFILE");
tab1.OrderBy = "id";
HighTrainProfile.Data = tab1.SearchData(null, -1, "*").Tables[0];
tab1.Close();
}
public static void SetData()
{
JTable tab1 = new JTable("PERSONGZ");
tab1.OrderBy = "GW";
PersonGZProfile.Data = tab1.SearchData(null, -1, "*").Tables[0];
tab1.Close();
}
public static void SetData()
{
JTable tab1 = new JTable("A2A3Fee");
tab1.OrderBy = "num";
A2A3FeeProfile.Data = tab1.SearchData(null, -1, "*").Tables[0];
tab1.Close();
}
public static void SetData()
{
JTable tab1 = new JTable("COMMONRICHANGFEE");
tab1.OrderBy = "num";
RiChangFeeProfile.Data = tab1.SearchData(null, -1, "*").Tables[0];
tab1.Close();
}
public static void SetData()
{
JTable tab1 = new JTable("LICHENGJIANRATE");
tab1.OrderBy = "id";
LiChengJianRate.Data = tab1.SearchData(null, -1, "*").Tables[0];
tab1.Close();
}
public static void SetData()
{
JTable tab1 = new JTable("LICHENGPROFILE");
tab1.OrderBy = "id";
LiChengProfile.Data = tab1.SearchData(null, -1, "*").Tables[0];
tab1.Close();
}
/// <summary>
/// 计算线路使用费
/// </summary>
/// <returns></returns>
public double GetLineCost()
{
double cost1 = 0;
JTable tab1 = new JTable("LINEPROFILE");
tab1.OrderBy = "id";
String fs = "Fee5,linetype"; //默认的配置
if (this.TrainBigKind == ETrainBigKind.普通列车)
{
if (this.KongTiaoFee == false)
{
fs = "Fee6,linetype";
}
}
else
{
if (this.HighTrainBianZhu == EHighTrainBianZhu.单组)
{
if (this.HighTrainBigKind == EHighTrainBigKind.动车200公里)
{
fs = "Fee3,linetype";
}
else
{
fs = "Fee1,linetype";
}
}
else
{
if (this.HighTrainBigKind == EHighTrainBigKind.动车200公里)
{
fs = "Fee4,linetype";
}
else
{
fs = "Fee2,linetype";
}
}
}
DataSet ds1 = tab1.SearchData(null, -1, fs);
foreach (DataRow dr1 in ds1.Tables[0].Rows)
{
String LineType = dr1["linetype"].ToString().Trim();
int index1 = int.Parse(LineType.ToLower().Replace("line", ""));
double fee = 0;
if (dr1[0].ToString().Trim() != String.Empty)
{
fee = double.Parse(dr1[0].ToString().Trim());
}
cost1 = cost1 + fee * this.GetLineMile(index1);
}
tab1.Close();
return(cost1 * 2 * 365);
}
public DataSourceResult GetList(String DataSourceID, String pageSize)
{
String OrderBy = String.Empty;
String SearchCondition = String.Empty;
int CurPage = 1;
//当前的排序
if (String.IsNullOrEmpty(System.Web.HttpContext.Current.Request.QueryString[DataSourceID + "OrderBy"]) == false)
{
OrderBy = System.Web.HttpContext.Current.Request.QueryString[DataSourceID + "OrderBy"];
}
//当前查询条件
if (String.IsNullOrEmpty(System.Web.HttpContext.Current.Request.QueryString[DataSourceID + "SearchCondition"]) == false)
{
SearchCondition = System.Web.HttpContext.Current.Request.QueryString[DataSourceID + "SearchCondition"];
}
//当前页
if (String.IsNullOrEmpty(System.Web.HttpContext.Current.Request.QueryString[DataSourceID + "CurPage"]) == false)
{
String CurPage1 = System.Web.HttpContext.Current.Request.QueryString[DataSourceID + "CurPage"];
if (WebFrame.Util.JValidator.IsInt(CurPage1))
{
CurPage = int.Parse(CurPage1);
}
}
/*----------自己的业务处理代码-------------------------------------*/
int totalRow = 0;
JTable tab1 = new JTable(TableName);
tab1.PageSize = int.Parse(pageSize);
if (String.IsNullOrEmpty(OrderBy) == false)
{
tab1.OrderBy = OrderBy;
}
List <SearchField> condition = new List <SearchField>();
if (String.IsNullOrEmpty(SearchCondition) == false)
{
condition.Add(new SearchField(SearchCondition, "", SearchOperator.UserDefine));
}
DataSet ds1 = tab1.SearchData(condition, CurPage, "*");
totalRow = tab1.GetTotalRow();
tab1.Close();
//业务代码处理结束后，返回的结果
DataSourceResult da1 = new DataSourceResult();
da1.DataTable = ds1.Tables[0];
da1.CurPage = CurPage;
da1.TotalRow = totalRow;
return(da1);
}
protected void BtExport_Click(object sender, EventArgs e)
{
JTable tab = new JTable("HIGHTRAINPROFILE");
tab.CommandText = "select * from HIGHTRAINPROFILE order by id";
DataTable dt = tab.SearchData(-1).Tables[0];
tab.Close();
ExportExcel(dt, "动车票价和定员表");
}
/// <summary>
/// 得到所有公司名称
/// </summary>
/// <param name="A"></param>
/// <returns></returns>
public DataTable GetCorpName()
{
DataTable dt = new DataTable();
JTable tab1 = new JTable("CORPINFO");
DataSet ds1 = tab1.SearchData(null, -1, new String[] { "CORPNAME" });
dt = ds1.Tables[0];
return(dt);
}
void button1_Click(object sender, EventArgs e)
{
if (FrameLib.CheckData(button1))
{
JTable tab1 = new JTable("MYUSERNAME");
Control[] con1 = new Control[] { num, password, UserName };
Dictionary <String, object> data1 = JControl.GetControlValuesToDictionary(con1);
List <SearchField> condition = new List <SearchField>();
condition.Add(new SearchField("UserName", this.UserName.Text));
if (this.isAdmin.Checked)
{
data1.Add("isAdmin", "1");
}
else
{
data1.Add("isAdmin", "0");
}
tab1.EditData(data1, condition, true);
tab1.Close();
//更新角色中的相关数据
JTable tab2 = new JTable();
tab2.TableName = "JROLEUSERS";
condition.Clear();
condition.Add(new SearchField("RoleID", "001"));
condition.Add(new SearchField("UserID", this.UserName.Text.Trim()));
DataTable dt1 = tab2.SearchData(condition, -1, "*").Tables[0];
if (this.isAdmin.Checked)
{
if (dt1.Rows.Count == 0)
{
DataRow dr1 = dt1.NewRow();
dr1["num"] = 1;
dr1["id"] = WebFrame.Util.JString.GetUnique32ID();
dr1["userid"] = this.UserName.Text.Trim();
dr1["roleid"] = "001";
dt1.Rows.Add(dr1);
tab2.Update(dt1);
}
}
else
{
if (dt1.Rows.Count > 0)
{
DataRow dr1 = dt1.Rows[0];
dr1.Delete();
tab2.Update(dt1);
}
}
tab2.Close();
JAjax.AlertAndGoUrl("提示：操作成功", button1.UrlReferrer);
}
}
//合并担担车收入
public static int MergeData_SHOUROU()
{
JConnect conn1 = JConnect.GetConnect();
conn1.BeginTrans();
int count = 0;
JTable tab1 = null;
try
{
tab1 = new JTable(conn1, "");
tab1.TableName = "NEWTRAINSHOUROU";
tab1.OrderBy = "byear,bmonth,trainname,astation,bstation";
DataTable dt1 = tab1.SearchData(null, -1, "*").Tables[0];
for (int i = dt1.Rows.Count - 1; i > 0; i--)
{
DataRow dr1 = dt1.Rows[i];
DataRow dr0 = dt1.Rows[i - 1];
if (dr1["byear"].ToString() == dr0["byear"].ToString() &&
dr1["bmonth"].ToString() == dr0["bmonth"].ToString() &&
dr1["trainname"].ToString() == dr0["trainname"].ToString() &&
dr1["astation"].ToString() == dr0["astation"].ToString() &&
dr1["bstation"].ToString() == dr0["bstation"].ToString()
)
{
for (int k = 1; k <= 6; k++)
{
dr0["SHOUROU" + k] = double.Parse(dr0["SHOUROU" + k].ToString())
+ double.Parse(dr1["SHOUROU" + k].ToString());
}
dr1.Delete();
count++;
}
}
if (count > 0)
{
tab1.Update(dt1);
}
conn1.CommitTrans();
}
catch (Exception err)
{
conn1.RollBackTrans();
count = 0;
}
if (tab1 != null)
{
tab1.Close();
}
return(count);
}
/// <summary>
/// 将线路站点数据导入导入到系统中
/// </summary>
/// <param name="FileName"></param>
public static void importTrainLineDataToSystem(String FileName)
{
String FileName1 = HttpContext.Current.Server.MapPath("~/Attachment/" + FileName);
if (File.Exists(FileName1))
{
//得到线路站点的数据
JTable tab1 = new JTable("TrainLine");
tab1.OrderBy = "LineName";
DataTable dt0 = tab1.SearchData(null, -1, "*").Tables[0];
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName1 + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
Conn.Open();
DataTable dt = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
for (int i = 0; i < dt.Rows.Count; i++)
{
String tableName = dt.Rows[i][2].ToString().Trim();
foreach (DataRow dr0 in dt0.Rows)
{
String AStation = dr0["AStation"].ToString();
String BStation = dr0["BStation"].ToString();
int miles0 = int.Parse(dr0["Miles"].ToString());
DataTable dt1 = GetLineStationTable(Conn, tableName, dr0["LineName"].ToString(),
out AStation, out BStation, out miles0);
if (dt1.Rows.Count > 0) //表示有数据
{
dr0["Astation"] = AStation;
dr0["Bstation"] = BStation;
dr0["miles"] = miles0;
//更新线路中的数据
Line.SetMiddleStation(dr0["LineID"].ToString(), dt1);
}
}
tab1.Update(dt0); //更新老站点中的相关数据
}
Conn.Close();
//得到线路的数据
tab1.Close();
}
}
/// <summary>
/// 得到项目的贡献度
/// </summary>
/// <param name="ProjectGuid">项目的Guidid</param>
/// <returns></returns>
public DataTable GetProjectGXDData(String ProjectGuid)
{
String sql1 = @"SELECT Tb_Task.AddUser AS userid, JUser.UserName AS username,Tb_Project.HSMoney,DatePart('yyyy',Tb_Project.FinishDate) as KhYear, Sum(Tb_Task.Workload) AS workload
FROM (Tb_Task INNER JOIN JUser ON Tb_Task.AddUser = JUser.UserID) INNER JOIN Tb_Project ON Tb_Task.ProjectGuidID = Tb_Project.GuidID
WHERE (((Tb_Project.GuidID)='{0}') AND ((Tb_Task.Status)='3'))
GROUP BY Tb_Task.AddUser, JUser.UserName,Tb_Project.HSMoney,DatePart('yyyy',Tb_Project.FinishDate)";
sql1 = String.Format(sql1, ProjectGuid);
JTable tab1 = new JTable();
tab1.CommandText = sql1;
DataTable dt1 = tab1.SearchData(-1).Tables[0];
tab1.Close();
return(dt1);
}
//根据车名称得到车的类型
private static String GetTrainType(String trainName)
{
String result = String.Empty;
JTable tab1 = new JTable("NEWTRAIN");
List <SearchField> condition = new List <SearchField>();
condition.Add(new SearchField("TRAINNAME", trainName));
DataSet ds1 = tab1.SearchData(condition, -1, "distinct TRAINTYPE");
if (ds1 != null && ds1.Tables[0].Rows.Count > 0)
{
result = ds1.Tables[0].Rows[0][0].ToString().Trim();
}
tab1.Close();
return(result);
}