public static DataTable findGeneralInfo(string sql)
{
int num = 0;
//DataTable dt = CServerWrapper.findConsInfo(DateTime.Now.AddDays(-30).Date.ToString("u"));
DataTable dt = CServerWrapper.execSqlQuery(sql);
num = dt.Rows.Count;
if (num == 0)
{
return(null);
}
return(dt);
}
public static DataTable findPhotosByProjSNo(string sno)
{
string sql = @"select Top 5 FileName, PhotoTime, ReferenceSerialNo, Person, Remark from PhotoInfo where ReferenceSerialNo = '" + sno + @"' order by PhotoTime desc;";
DataTable dt = null;
if (isConnected)
{
dt = CServerWrapper.execSqlQuery(sql);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
dr["FileName"] = "/ProjectPhoto/" + dr["ReferenceSerialNo"].ToString().Substring(0, 3) + "/" + Convert.ToDateTime(dr["PhotoTime"].ToString()).Year + "/" + dr["FileName"].ToString().Replace("#", "%23"); // HttpUtility.UrlEncode(dr["FileName"].ToString());
}
}
}
return(dt);
}
/// <summary>
/// string sqlstr = @"select FirmName ,a.FirmTypeID, CategoryCode, SerialNo, UpdateTime, Longitude, Latitude from (select * from FirmInfo)a, (select FirmTypeID,FirmTypeCategoryName from FirmTypeInfo)b where a.FirmTypeID=b.FirmTypeID and( FirmTypeCategoryName='单位' or FirmTypeCategoryName='分支机构') and Longitude > 10 AND Latitude > 10 order by a.FirmTypeID asc ;";
/// </summary>
private void initFirmsFromServer() //string projCode, List<CRailwayProject> projList, string fileName
{
#region init Items
System.Data.DataTable dt = null;
string ProjectSQL;
int firmID;
string fileName = null;
string firmType = null;
CRailwayFirm sItem = null;
try
{
ProjectSQL = @"select a.*,b.ShorName,b.Latitude,b.Longitude,b.FirmTypeID, c.FirmTypeCategoryName from
(select count(usrid) as num,firmid from UsrInfo
where idcardno is not null group by firmid)a,
(select latitude,longitude,firmid,ShorName,FirmTypeID from FirmInfo
where Latitude!=0 and Longitude!=0)b,
(select FirmTypeID,FirmTypeCategoryName from FirmTypeInfo
where FirmTypeCategoryName='单位' or FirmTypeCategoryName='分支机构')c
where a.firmid=b.FirmID and b.FirmTypeID=c.FirmTypeID";
//ProjectSQL = @"select FirmName ,a.FirmTypeID, CategoryCode, SerialNo, UpdateTime, Longitude, Latitude from (select * from FirmInfo)a, (select FirmTypeID,FirmTypeCategoryName from FirmTypeInfo)b where a.FirmTypeID=b.FirmTypeID and( FirmTypeCategoryName='单位' or FirmTypeCategoryName='分支机构') and Longitude > 10 AND Latitude > 10 order by a.FirmTypeID asc ;";
//ProjectSQL += CServerWrapper.findProjectCode(projCode) + @"%' order by ProjectID asc;";
//ProjectSQL += projCode + @"%' order by ProjectID asc;";
dt = CServerWrapper.execSqlQuery(ProjectSQL);
//DatabaseWrapper.PrintDataTable(dt);
foreach (DataRow dataReader in dt.Rows)
{
firmID = (int)dataReader["FirmTypeID"];
switch (firmID)
{
case 2:
firmType = "施工单位";
fileName = @"施工单位.png";
break;
case 5:
firmType = "建设单位";
fileName = @"单位.png";
break;
case 7:
firmType = "制梁场";
fileName = @"LC.png";
break;
case 8:
firmType = "监理单位";
fileName = @"监理单位.png";
break;
case 10:
firmType = "项目部";
fileName = @"工程.png";
break;
}
sItem = new CRailwayFirm(this, Convert.ToInt32(dataReader["Num"]), Convert.ToInt32(dataReader["firmid"]), dataReader["ShorName"].ToString(),
Convert.ToDouble(dataReader["Longitude"]), Convert.ToDouble(dataReader["Latitude"]), firmType, fileName);
//sItem = new CRailwayFirm(this, firmType, dataReader["FirmName"].ToString(), dataReader["SerialNo"].ToString(), (DateTime)dataReader["UpdateTime"],
// Convert.ToDouble(dataReader["Longitude"]), Convert.ToDouble(dataReader["Latitude"]), fileName);
mFirmList.Add(sItem);
if (sItem.GlobalMileage > 0)
{
mTotalSpotList.Add(sItem);
}
}
//System.Console.WriteLine("readok");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message.ToString());
}
#endregion
}
private void initProjectsFromServer()
{
#region init Items
//DataSet ds = null;
System.Data.DataTable dt = null;
string ProjectSQL = @"SELECT ProjectID, ProjectName, ProfessionalName, ProfessionalCategoryCode, ShorName,Mileage_Start,Mileage_Mid, Mileage_End,
MileagePrefix , SerialNo, UpdateTime, Direction , avgProgress, ParentID from vw_ProjectInfo where ProfessionalCategoryCode like '-1-42-26-%' OR
ProfessionalCategoryCode like '-1-42-27-%' OR ProfessionalCategoryCode like '-1-42-28-%' order by ProjectID asc;";
//string[] professionalCateCode = { "-1-42-26-", "-1-42-28-", "-1-42-27-" }; // 桥梁，路基，涵洞
string projectName, professionalName, ProfessionalCategoryCode, ShorName;
int projID, parentID;
double Mileage_Start, Mileage_End, Mileage_Mid;
string dkCode, SerialNo;
DateTime UpdateTime;
double Direction, avgProgress;
CRailwayProject sItem = null;
try
{
// if (isTunnel)
// ProjectSQL = @"SELECT ProjectID, ProjectName, ProfessionalName, ProfessionalCategoryCode, ShorName,Mileage_Start,Mileage_Mid, Mileage_End,
// Mileage_Start_Des,Mileage_Mid_Des, Mileage_End_Des, ProjectLenth, SerialNo, UpdateTime, Direction , avgProgress, ParentID from vw_ProjectInfo where ProfessionalCategoryCode like '";
// else
// ProjectSQL = @"SELECT ProjectID, ProjectName, ProfessionalName, ProfessionalCategoryCode, ShorName,Mileage_Start,Mileage_Mid, Mileage_End,
// Mileage_Start_Des,Mileage_Mid_Des, Mileage_End_Des, ProjectLenth, SerialNo, UpdateTime, Direction , avgProgress, ParentID from vw_ProjectInfo where ParentID=0 AND ProfessionalCategoryCode like '";
// //ProjectSQL += CServerWrapper.findProjectCode(projCode) + @"%' order by ProjectID asc;";
// ProjectSQL += projCode + @"%' order by ProjectID asc;";
dt = CServerWrapper.execSqlQuery(ProjectSQL);
//int count = dt.Rows.Count;
foreach (DataRow dataReader in dt.Rows)
{
projID = (int)dataReader["ProjectID"];
Mileage_Start = Convert.ToDouble(dataReader["Mileage_Start"]);
Mileage_Mid = Convert.ToDouble(dataReader["Mileage_Mid"]);
Mileage_End = Convert.ToDouble(dataReader["Mileage_End"]);
avgProgress = Convert.ToDouble(dataReader["avgProgress"]);
Direction = Convert.ToDouble(dataReader["Direction"]);
projectName = (string)dataReader["ProjectName"];
professionalName = (string)dataReader["ProfessionalName"];
ProfessionalCategoryCode = (string)dataReader["ProfessionalCategoryCode"];
ShorName = (string)dataReader["ShorName"];
dkCode = (string)dataReader["MileagePrefix"];
SerialNo = (string)dataReader["SerialNo"];
UpdateTime = (DateTime)dataReader["UpdateTime"]; // (DateTime)dataReader["UpdateTime"];
parentID = (int)dataReader["ProjectID"];
if (ProfessionalCategoryCode.StartsWith("-1-42-26-")) // 桥梁
{
if (ProfessionalCategoryCode.StartsWith("-1-42-26-81-")) // 连续梁
{
sItem = new CContBeam(this, SerialNo, projID, professionalName, projectName, ShorName, dkCode,
Mileage_Start, Mileage_Mid, Mileage_End, UpdateTime, avgProgress, Direction, @"桥梁.png");
mContBeamList.Add((CContBeam)sItem);
if (sItem.GlobalMileage > 0)
{
mTotalSpotList.Add(sItem);
}
}
else
{
sItem = new CRailwayBridge(this, SerialNo, projID, professionalName, projectName, ShorName, dkCode,
Mileage_Start, Mileage_Mid, Mileage_End, UpdateTime, avgProgress, Direction, @"桥梁.png");
mBridgeList.Add((CRailwayBridge)sItem);
}
}
else if (ProfessionalCategoryCode.StartsWith("-1-42-27-")) // 隧道，涵洞
{
sItem = new CRailwayTunnel(this, SerialNo, projID, professionalName, projectName, ShorName, dkCode,
Mileage_Start, Mileage_Mid, Mileage_End, UpdateTime, avgProgress, Direction, @"涵洞.png");
mTunnelList.Add((CRailwayTunnel)sItem);
if (sItem.GlobalMileage > 0)
{
mTotalSpotList.Add(sItem);
}
}
else if (ProfessionalCategoryCode.StartsWith("-1-42-28-")) // 路基
{
sItem = new CRailwayRoad(this, SerialNo, projID, professionalName, projectName, ShorName, dkCode,
Mileage_Start, Mileage_Mid, Mileage_End, UpdateTime, avgProgress, Direction, @"路基.png");
mRoadList.Add((CRailwayRoad)sItem);
if (sItem.GlobalMileage > 0)
{
mTotalSpotList.Add(sItem);
}
}
///没有车站表，暂时以站场路基计算，车站方向暂时位于铁路同一侧
//if (ProfessionalCategoryCode.StartsWith("-1-42-28-84-")) //-1-43-84-
//if (ProfessionalCategoryCode.StartsWith(CServerWrapper.findProjectCode("giscode_station")))
//{
// int ix = projectName.IndexOf('站');
////Console.WriteLine(projectName);
// if (ix >= 0)
// {
// projectName = projectName.Substring(0, ix + 1);
// Direction = 90;
// }
// sItem = new CRailwayProject(this, SerialNo, projID, professionalName, projectName, ShorName,
// Mileage_Start, Mileage_Mid, Mileage_End, Mileage_Start_Des, Mileage_Mid_Des, Mileage_End_Des, UpdateTime, avgProgress, Direction, fileName, projLength);
// mProjectList.Add(sItem);
// //mOtherList.Add(sItem);
// projList.Add(sItem);
//}
//else if (ProfessionalCategoryCode.StartsWith("-1-42-26-"))
//{
// sItem = new CRailwayProject(this, SerialNo, projID, professionalName, projectName, ShorName,
// Mileage_Start, Mileage_Mid, Mileage_End, Mileage_Start_Des, Mileage_Mid_Des, Mileage_End_Des, UpdateTime, avgProgress, Direction, @"桥梁.png", projLength);
// mProjectList.Add(sItem);
// mBridgeList.Add(sItem);
//}
//else if (ProfessionalCategoryCode.StartsWith("-1-42-28-"))
//{
// sItem = new CRailwayProject(this, SerialNo, projID, professionalName, projectName, ShorName,
// Mileage_Start, Mileage_Mid, Mileage_End, Mileage_Start_Des, Mileage_Mid_Des, Mileage_End_Des, UpdateTime, avgProgress, Direction, @"路基.png", projLength);
// mProjectList.Add(sItem);
// mRoadList.Add(sItem);
//}
//else if (ProfessionalCategoryCode.StartsWith("-1-42-31-")) // && projectName.EndsWith("站") // 梁厂
//{
// sItem = new CRailwayProject(this, SerialNo, projID, professionalName, projectName, ShorName,
// Mileage_Start, Mileage_Mid, Mileage_End, Mileage_Start_Des, Mileage_Mid_Des, Mileage_End_Des, UpdateTime, avgProgress, Direction, @"路基.png", projLength);
// mOtherList.Add(sItem);
// //mStationList.Add(sItem);
//}
//else if (ProfessionalCategoryCode.StartsWith("-1-42-27-") || ProfessionalCategoryCode.StartsWith("-1-42-74-"))
//{
// sItem = new CRailwayProject(this, SerialNo, projID, professionalName, projectName, ShorName,
// Mileage_Start, Mileage_Mid, Mileage_End, Mileage_Start_Des, Mileage_Mid_Des, Mileage_End_Des, UpdateTime, avgProgress, Direction, @"涵洞.png", projLength);
// mProjectList.Add(sItem);
// mTunnelList.Add(sItem);
//}
}
//System.Console.WriteLine("readok");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message.ToString());
}
#endregion
}
private void initDWProjects()
{
#region init Items
//DataSet ds = null;
string ProjectSQL;
string dwName, dwModelType, dwDKCode;
int projID;
double dwMileage, dwLength;
//string Mileage_Start_Des, Mileage_End_Des, Mileage_Mid_Des, SerialNo;
DateTime dwUpdateTime;
int curProjIndex = 0;
System.Data.DataTable dt = null;
//double Direction, avgProgress;
//CRailwayProject sItem = null;
//mUseLocalDB = true;
//if (CServerWrapper.isConnected)
//{
// ProjectSQL = @"SELECT Name, ProjectID, Mileage, ProjectLength, UpdateTime, ModelType from [Project_B_DW_Info$] where Name like '%墩' order by ProjectID asc;";
// string fileName = @"D:\GISData\jiqing\Project_B_dw_Info.xls";
// dt = DatabaseWrapper.LoadDataTableFromExcel(fileName, "Project_B_DW_Info", ProjectSQL);
//}
//else
//{
// web service模式
//WS_GISServerData.GisDataWebServiceSoapClient ws = new WS_GISServerData.GisDataWebServiceSoapClient();
//dt = ws.ws_FindDWProjectInfo();
//string sql = @"SELECT Name, ProjectID, Mileage, ProjectLength, UpdateTime, ModelType from Project_B_DW_Info where Name like '%墩' ";
//dt = CServerWrapper.findDWProjectInfo();
//dt = CServerWrapper.execSqlQuery(@"SELECT * from Project_B_DW_Info where not MileagePrefix = 'DK' and Name like '%墩' order by ProjectID asc;");
//}
dt = CServerWrapper.execSqlQuery(@"SELECT Name, ProjectID, MileagePrefix, Mileage, ProjectLength, UpdateTime, ModelType, SerialNo from Project_B_DW_Info where Name like '%墩' order by ProjectID asc");
//DatabaseWrapper.PrintDataTable(dt);
foreach (DataRow dataReader in dt.Rows)
{
try
{
if (!CServerWrapper.isConnected)
{
projID = (int)((double)dataReader["ProjectID"] + 0.1);
}
else
{
projID = (int)dataReader["ProjectID"];
}
dwMileage = Convert.ToDouble(dataReader["Mileage"]);
dwLength = Convert.ToDouble(dataReader["ProjectLength"]);
dwName = (string)dataReader["Name"];
dwModelType = (string)dataReader["ModelType"];
dwUpdateTime = (DateTime)dataReader["UpdateTime"];
while (curProjIndex < mBridgeList.Count && projID > mBridgeList[curProjIndex].mProjectID)
{
curProjIndex++;
}
if (curProjIndex == mBridgeList.Count)
{
Console.WriteLine("DW_input_Error" + dwName + dwMileage);
return;
}
mBridgeList[curProjIndex].mDWProjList.Add(new CRailwayPier(mBridgeList[curProjIndex], dataReader["Mileage"].ToString(), dwName, dwModelType, dwMileage, dwLength, dwUpdateTime, 0, dataReader["MileagePrefix"].ToString()));
// Console.WriteLine(dwName +"\t"+ dwMileage);
//gProType.Add("-1-43-84-", "车站");
//gProType.Add("-1-42-26-", "桥梁");
//gProType.Add("-1-42-27-", "隧道");
//gProType.Add("-1-42-28-", "区间路基");
//gProType.Add("-1-42-74-", "涵洞");
//gProType.Add("-1-42-100-", "轨道");
}//(string)dataReader["UpdateTime"],
catch (System.Exception ex)
{
Console.WriteLine((string)dataReader["Name"] + " invalid");
}
}
#endregion
}