public static DataSet GetUserGroupFromLogin(int iUserID)
{
string strSQL = "";
string strGroupList = "";
try
{
if (iUserID == 1)
{
strSQL = "SELECT UserGroupID,UserGroupName "
+ " FROM UserGroupMain where UserGroupName != '车辆登录专用' and DelFlag = 0";
}
else
{
strSQL = "SELECT UserGroupMain.UserGroupID,UserGroupMain.UserGroupName "
+ " FROM UserGroupMain inner JOIN "
+ " UserGroupDetail ON "
+ " UserGroupMain.UserGroupID = UserGroupDetail.UserGroupID "
+ "WHERE UserGroupDetail.UserID = " + iUserID.ToString() + " and UserGroupName != '车辆登录专用' and DelFlag = 0 ";
}
DataSet ds = BllSql.RunSqlSelect(strSQL);
return(ds);
}
catch (Exception ex)
{
return(null);
}
}
public static DataSet GetUserFromLogin(int iUserID, int iGroupID)
{
string strSQL = "";
string strGroupList = "";
try
{
if (iUserID == 1)
{
strSQL = "SELECT UserMain.UserID,UserMain.UserName,UserGroupDetail.UserGroupID,UserMain.UserTypeID "
+ " FROM UserMain inner join UserGroupDetail on UserGroupDetail.UserID = UserMain.UserID where DelPurview = 0";
}
else
{
strSQL = "SELECT UserMain.UserID,UserMain.UserName,UserGroupDetail.UserGroupID,UserMain.UserTypeID "
+ " FROM UserMain inner join UserGroupDetail on UserGroupDetail.UserID = UserMain.UserID where UserMain.UserID > 1 and DelPurview = 0 and UserGroupDetail.UserGroupID = " + iGroupID.ToString();
}
DataSet ds = BllSql.RunSqlSelect(strSQL);
return(ds);
}
catch (Exception ex)
{
return(null);
}
}
public static DataSet GetVehicleProvice(int iUserID)
{
string strSQL = "";
string strGroupList = "";
try
{
string sTime = DateTime.Now.AddDays(-30).ToString("yyyy-MM-dd HH:mm:ss");
if (iUserID == 1)
{
strSQL = "SELECT VehID,Province,City from VehProvince where [Date] >= '" + sTime + "' ";
}
else
{
//取所有列表ID
strGroupList = GetAllVehGroupByUserID(iUserID);
if (strGroupList.Trim().Length == 0)
{
strGroupList = "0";
}
strSQL = "SELECT VehProvince.VehID,Province,City from VehProvince INNER JOIN VehicleDetail ON VehProvince.VehId = VehicleDetail.VehID where [Date] >= '" + sTime + "' and VehicleDetail.VehGroupID in (" + strGroupList + ") ";
}
DataSet ds = BllSql.RunSqlSelect(strSQL);
return(ds);
}
catch (Exception ex)
{
return(null);
}
}
//递归遍历一个车组下的所有子车组
public static bool RecursiveVehParentGroup(int iGroupID, ref string strGroupID)
{
string strSQL;
int n;
System.Collections.Generic.List <Models.CVehGroup> db = new System.Collections.Generic.List <Models.CVehGroup>();
try
{
//一次性查出所有的车组关系
strSQL = "SELECT VehGroupID,fVehGroupID FROM VehGroupDetail ";
DataSet ds = BllSql.RunSqlSelect(strSQL);
n = ds.Tables[0].Rows.Count;
//如果有数据
if (n >= 1)
{
//复制下来
string id;
string pid;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
id = ds.Tables[0].Rows[i]["VehGroupID"].ToString();
pid = ds.Tables[0].Rows[i]["fVehGroupID"].ToString();
Models.CVehGroup model = new Models.CVehGroup();
model.PID = pid;
model.id = id;
db.Add(model);
}
string sGroupIDTemp = iGroupID.ToString();
for (int i = 0; i < db.Count; i++)
{
//找到它的子节点
if (db[i].id == sGroupIDTemp)
{
if (db[i].PID == "-1")
{
break;
}
strGroupID += db[i].PID + ",";
getParentVehGroup(db, db[i].PID, ref strGroupID);
}
}
if (strGroupID.Length >= 1)
{
if (strGroupID.Substring(strGroupID.Length - 1, 1) == ",")
{
strGroupID = strGroupID.Substring(0, strGroupID.Length - 1);
}
}
}
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public static DataSet GetVehGroupFromLogin(int iUserID)
{
string strSQL = "";
string strGroupList = "";
try
{
if (iUserID == 1)
{
strSQL = "SELECT VehGroupMain.VehGroupID, VehGroupMain.VehGroupName, "
+ "VehGroupDetail.fVehGroupID "
+ " FROM VehGroupMain left JOIN "
+ " VehGroupDetail ON "
+ " VehGroupMain.VehGroupID = VehGroupDetail.VehGroupID ";
}
else
{
//取所有列表ID
//If sVersion = "SZHZ" Then
// strGroupList = GetAllVehGroupByGroup_UserID(adoConn, iUserID, sUserName)
strGroupList = GetAllVehGroupByUserID(iUserID);
if (strGroupList.Trim().Length == 0)
{
strGroupList = "0";
}
strSQL = "SELECT VehGroupMain.VehGroupID, VehGroupMain.VehGroupName, "
+ "VehGroupDetail.fVehGroupID "
+ " FROM VehGroupMain left JOIN "
+ " VehGroupDetail ON "
+ " VehGroupMain.VehGroupID = VehGroupDetail.VehGroupID "
+ "WHERE (VehGroupMain.VehGroupID IN (" + strGroupList + ")) ";
}
if (iUserID == 1)
{
strSQL = strSQL + " where (VehGroupMain.delflag Is null Or VehGroupMain.delflag = 0)";
}
else
{
strSQL = strSQL + " and (VehGroupMain.delflag Is null Or VehGroupMain.delflag = 0)";
}
DataSet ds = BllSql.RunSqlSelect(strSQL);
return(ds);
}
catch (Exception ex)
{
return(null);
}
}
public static DataSet GetAllVehicleByTeamID(int iTeamID)
{
string strSQL = "";
try
{
strSQL = "SELECT Id,Deviceid, IpAddress, Cph, OwnNo,TaxiNo,ProductCode,Decph,OwnerName,Contact1,AlarmLinkTel,Contact2,LinkTel2,Contact3,ContactPhone3,YyZh,ByZd,FrameNo,EngineNo,VehicleType,Color,PurchaseDate,ServerEndTime,EnrolDate,ServerMoney,Seller,LogOutCause,InstallPerson,InstallAddress,RecordPerson,BusinessPerson,PowerType,Marks,RepairTime,InsuranceTime,DistrictCode,WebPass,InsertTime from Vehicle inner join VehicleDetail on VehicleDetail.VehID = Vehicle.Id where VehGroupID = " + iTeamID.ToString();
DataSet ds = BllSql.RunSqlSelect(strSQL);
return(ds);
}
catch (Exception ex)
{
return(null);
}
}
public static DataSet GetVehicleFromLogin(int iUserID)
{
string strSQL = "";
string strGroupList = "";
try
{
if (iUserID == 1)
{
strSQL = "SELECT Id,Deviceid, IpAddress, Cph,OwnNo, TaxiNo,VehGroupID,ProductCode,OwnerName,Contact3,Seller from Vehicle INNER JOIN VehicleDetail ON Vehicle.Id = VehicleDetail.VehID ";
}
else
{
//取所有列表ID
//If sVersion = "SZHZ" Then
// strGroupList = GetAllVehGroupByGroup_UserID(adoConn, iUserID, sUserName)
strGroupList = GetAllVehGroupByUserID(iUserID);
if (strGroupList.Trim().Length == 0)
{
strGroupList = "0";
}
strSQL = "SELECT Id,Deviceid, IpAddress, Cph, OwnNo,TaxiNo,VehGroupID,ProductCode,OwnerName,Contact3,Seller from Vehicle INNER JOIN VehicleDetail ON Vehicle.Id = VehicleDetail.VehID where VehicleDetail.VehGroupID in (" + strGroupList + ") ";
}
if (iUserID == 1)
{
strSQL = strSQL + " where (Vehicle.delflag is null) or (Vehicle.delflag=0)";
}
else
{
strSQL = strSQL + " and ((Vehicle.delflag is null) or (Vehicle.delflag=0))";
}
DataSet ds = BllSql.RunSqlSelect(strSQL);
return(ds);
}
catch (Exception ex)
{
return(null);
}
}
public static DataSet GetAnnual(int iUserID, DateTime dt)
{
string strSQL = "";
string strGroupList = "";
try
{
if (iUserID == 1)
{
strSQL = "SELECT Id,RepairTime from Vehicle INNER JOIN VehicleDetail ON Vehicle.Id = VehicleDetail.VehID ";
}
else
{
//取所有列表ID
strGroupList = GetAllVehGroupByUserID(iUserID);
if (strGroupList.Trim().Length == 0)
{
strGroupList = "0";
}
strSQL = "SELECT Id,RepairTime from Vehicle INNER JOIN VehicleDetail ON Vehicle.Id = VehicleDetail.VehID where VehicleDetail.VehGroupID in (" + strGroupList + ") ";
}
if (iUserID == 1)
{
strSQL = strSQL + " where RepairTime <= '" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "' and (Vehicle.delflag is null) or (Vehicle.delflag=0) order by RepairTime";
}
else
{
strSQL = strSQL + " and RepairTime <= '" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "' and ((Vehicle.delflag is null) or (Vehicle.delflag=0)) order by RepairTime";
}
DataSet ds = BllSql.RunSqlSelect(strSQL);
return(ds);
}
catch (Exception ex)
{
return(null);
}
}
public static string GetChildUserID(int iUserID)
{
try
{
string strSQL = "select UserID from UserGroupDetail where usergroupid=" + iUserID.ToString();
string result = iUserID.ToString();
DataSet ds = BllSql.RunSqlSelect(strSQL);
if (ds == null || ds.Tables.Count == 0)
{
return(result);
}
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
result += "," + ds.Tables[0].Rows[i]["UserID"].ToString();
}
return(result);
}
catch (Exception ex)
{
return(iUserID.ToString());
}
}
//<summary>
//按用户ID取
//</summary>
//<param name="iUserID"></param>
//<returns></returns>
//<remarks></remarks>
public static string GetAllVehGroupByUserID(int iUserID)
{
string strSQL = "";
string strGroupIDList = "";
StringBuilder sb = new StringBuilder();
try
{
//查出用户名下的所有根车组
if (iUserID == 1)
{
strSQL = "SELECT DISTINCT(VehGroupID) FROM VehGroupmain ";
}
else
{
strSQL = "exec GetAllVehGroupByUserID " + iUserID.ToString();
}
DataSet ds = BllSql.RunSqlSelect(strSQL);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
sb.Append(ds.Tables[0].Rows[i][0].ToString() + ",");
}
strGroupIDList = sb.ToString();
strGroupIDList = strGroupIDList.Substring(0, strGroupIDList.Length - 1);
}
else
{
return("");
}
return(strGroupIDList);
}
catch (Exception ex)
{
return("");
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request.Cookies["username"] != null)
{
sUserName = Request.Cookies["username"].Value;
}
try
{
object sUserID = Session["userid"];
if (sUserID == null)
{
Response.Write(BllCommon.TransferMobilelocation());
return;
}
if (!IsPostBack)
{
//获取车组
DataSet ds = BllVehicle.GetVehGroupFromLogin(Convert.ToInt32(sUserID));
List <CVehGroup> lstVehGroup = new List <CVehGroup>();
Hashtable htGroupPID = new Hashtable();
Hashtable htGroupID = new Hashtable();
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
string id = "G" + dr["VehGroupID"];
string PID = "G" + dr["fVehGroupID"];
if (!htGroupPID.ContainsKey(PID))
{
htGroupPID.Add(PID, id);
}
if (!htGroupID.ContainsKey(id))
{
htGroupID.Add(id, PID);
}
}
foreach (DataRow dr in ds.Tables[0].Rows)
{
CVehGroup vehGroup = new CVehGroup();
vehGroup.id = "G" + dr["VehGroupID"];
vehGroup.name = dr["VehGroupName"].ToString();
vehGroup.PID = "G" + dr["fVehGroupID"];
vehGroup.HasChild = 0;
vehGroup.Root = 0;
if (htGroupPID.ContainsKey(vehGroup.id))
{
vehGroup.HasChild = 1;
}
if (!htGroupID.ContainsKey(vehGroup.PID))
{
vehGroup.Root = 1;
}
lstVehGroup.Add(vehGroup);
}
}
string json5 = JsonHelper.SerializeObject(lstVehGroup);
//ViewState["VehGroup"] = json5;
sVehGroup = json5;
//if (Request.Cookies["VehGroup"] == null)
//{
// AddCookie("VehGroup", json5);
//}
//else
//{
// ModifyCookie("VehGroup", json5);
//}
if (sUserID.ToString() == "1")
{
}
else
{
DataSet dsPermission = BllSql.RunSqlSelect("select FuncID from UserPermission where UserID = " + sUserID.ToString());
if (dsPermission != null && dsPermission.Tables.Count > 0 && dsPermission.Tables[0].Rows.Count > 0)
{
sPermission = dsPermission.Tables[0].Rows[0][0].ToString();
}
}
}
}
catch (Exception Exception)
{
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request.Cookies["username"] != null)
{
sUserName = Request.Cookies["username"].Value;
sUserName = System.Web.HttpUtility.UrlDecode(sUserName);
}
try
{
string sLoginType = "1";
if (Request.Cookies["logintype"] != null)
{
sLoginType = Request.Cookies["logintype"].Value;
}
object sUserID = Session["userid"];
if (sUserID == null)
{
Response.Write(BllCommon.TransferMobilelocation());
return;
}
string sGroups = "0";
if (!IsPostBack)
{
//获取车组
if (sLoginType == "1")
{
DataSet ds = BllVehicle.GetVehGroupFromLogin(Convert.ToInt32(sUserID));
List <CVehGroup> lstVehGroup = new List <CVehGroup>();
Hashtable htGroupPID = new Hashtable();
Hashtable htGroupID = new Hashtable();
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
if (sUserID != "1")
{
sGroups = sGroups + "," + dr["VehGroupID"].ToString();
}
string id = "G" + dr["VehGroupID"];
string PID = "G" + dr["fVehGroupID"];
if (!htGroupPID.ContainsKey(PID))
{
htGroupPID.Add(PID, id);
}
if (!htGroupID.ContainsKey(id))
{
htGroupID.Add(id, PID);
}
}
foreach (DataRow dr in ds.Tables[0].Rows)
{
CVehGroup vehGroup = new CVehGroup();
vehGroup.id = "G" + dr["VehGroupID"];
vehGroup.name = dr["VehGroupName"].ToString();
vehGroup.PID = "G" + dr["fVehGroupID"];
vehGroup.HasChild = 0;
vehGroup.Root = 0;
if (htGroupPID.ContainsKey(vehGroup.id))
{
vehGroup.HasChild = 1;
}
if (!htGroupID.ContainsKey(vehGroup.PID))
{
vehGroup.Root = 1;
}
lstVehGroup.Add(vehGroup);
}
}
string json5 = JsonHelper.SerializeObject(lstVehGroup);
//ViewState["VehGroup"] = json5;
sVehGroup = json5;
}
else
{
List <CVehGroup> lstVehGroup = new List <CVehGroup>();
CVehGroup vehGroup = new CVehGroup();
vehGroup.id = "G0";
vehGroup.name = "我的车辆";
vehGroup.PID = "G-1";
vehGroup.HasChild = 0;
vehGroup.Root = 1;
lstVehGroup.Add(vehGroup);
string json5 = JsonHelper.SerializeObject(lstVehGroup);
//ViewState["VehGroup"] = json5;
sVehGroup = json5;
}
//if (Request.Cookies["VehGroup"] == null)
//{
// AddCookie("VehGroup", json5);
//}
//else
//{
// ModifyCookie("VehGroup", json5);
//}
if (sLoginType == "1")
{
if (sUserID.ToString() == "1")
{
}
else
{
DataSet dsPermission = BllSql.RunSqlSelect("select FuncID from UserPermission where UserID = " + sUserID.ToString());
if (dsPermission != null && dsPermission.Tables.Count > 0 && dsPermission.Tables[0].Rows.Count > 0)
{
sPermission = dsPermission.Tables[0].Rows[0][0].ToString();
}
}
}
else
{
sPermission = "5006";
}
List <COil> lstOil = new List <COil>();
if (sLoginType == "1")
{
DataSet dsOil = null;
if (sUserID.ToString() == "1")
{
dsOil = BllSql.RunSqlSelect("SELECT Vehyh_Table.r_id, Vehyh_Table.VehID, Vehyh_Table.Veh_Cph, Vehyh_Table.YH_Scale, Vehyh_Table.YH_Number, Vehyh_Table.oilminu, Vehyh_Table.oilpercent FROM Vehyh_Table INNER JOIN VehicleDetail ON Vehyh_Table.VehID = VehicleDetail.VehID");
}
else
{
dsOil = BllSql.RunSqlSelect("SELECT Vehyh_Table.r_id, Vehyh_Table.VehID, Vehyh_Table.Veh_Cph, Vehyh_Table.YH_Scale, Vehyh_Table.YH_Number, Vehyh_Table.oilminu, Vehyh_Table.oilpercent FROM Vehyh_Table INNER JOIN VehicleDetail ON Vehyh_Table.VehID = VehicleDetail.VehID where VehicleDetail.VehGroupID in(" + sGroups + ")");
}
if (dsOil != null && dsOil.Tables.Count > 0 && dsOil.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in dsOil.Tables[0].Rows)
{
string sVeh = "V" + dr["VehID"].ToString();
COil cOil = new COil();
foreach (COil item in lstOil)
{
if (item.VehID == sVeh)
{
cOil = item;
break;
}
}
cOil.Cph = dr["Veh_Cph"].ToString();
cOil.id = Convert.ToInt32(dr["r_id"]);
COilDetail cDetail = new COilDetail();
cDetail.OilValue = Convert.ToDouble(dr["YH_Number"]);
cDetail.Scale = Convert.ToDouble(dr["YH_Scale"]);
cOil.lstDetail.Add(cDetail);
cOil.StealOil = Convert.ToInt32(dr["oilpercent"]);
cOil.VehID = "V" + dr["VehID"].ToString();
lstOil.Add(cOil);
}
}
}
else
{
DataSet dsOil = BllSql.RunSqlSelect("SELECT Vehyh_Table.r_id, Vehyh_Table.VehID, Vehyh_Table.Veh_Cph, Vehyh_Table.YH_Scale, Vehyh_Table.YH_Number, Vehyh_Table.oilminu, Vehyh_Table.oilpercent FROM Vehyh_Table INNER JOIN VehicleDetail ON Vehyh_Table.VehID = VehicleDetail.VehID where Vehyh_Table.VehID =" + sUserID);
if (dsOil != null && dsOil.Tables.Count > 0 && dsOil.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in dsOil.Tables[0].Rows)
{
string sVeh = "V" + dr["VehID"].ToString();
COil cOil = new COil();
foreach (COil item in lstOil)
{
if (item.VehID == sVeh)
{
cOil = item;
break;
}
}
cOil.Cph = dr["Veh_Cph"].ToString();
cOil.id = Convert.ToInt32(dr["r_id"]);
COilDetail cDetail = new COilDetail();
cDetail.OilValue = Convert.ToDouble(dr["YH_Number"]);
cDetail.Scale = Convert.ToDouble(dr["YH_Scale"]);
cOil.lstDetail.Add(cDetail);
cOil.StealOil = Convert.ToInt32(dr["oilpercent"]);
cOil.VehID = "V" + dr["VehID"].ToString();
lstOil.Add(cOil);
}
}
}
string jsonOil = JsonHelper.SerializeObject(lstOil);
sOil = jsonOil;
}
}
catch (Exception Exception)
{
}
}