public DataTable GetTableDatas()
{
try
{
int day = GetSpen(m_Bdt, m_Edt);
string strB, strE;
if (IFAllStation(m_Name))
{
strB = "select * from v_AddPumpDatas where time = '" + m_Bdt + "' order by team desc"; //+"' and '"+m_Bdt.AddDays(1)+"'";
strE = "select * from v_AddPumpDatas where time = '" + m_Edt + "' order by team desc"; //+"' and '"+m_Edt.AddDays(1)+"'";
}
else
{
strB = "select * from v_AddPumpDatas where name='" + m_Name + "' and time = '" + m_Bdt + "'"; // and '"+m_Bdt.AddDays(1)+"'";
strE = "select * from v_AddPumpDatas where name='" + m_Name + "' and time = '" + m_Edt + "'"; // and '"+m_Edt.AddDays(1)+"'";
}
DataTable dt = CreatTable();
da = new SqlDataAdapter(strE, con.GetConnection());
DataSet ds = new DataSet();
da.Fill(ds, "End");
da.Dispose();
for (int i = 0; i < ds.Tables["End"].Rows.Count; i++)
{
DataRow newrow = dt.NewRow();
dt.Rows.Add(newrow);
dt.Rows[i]["name"] = ds.Tables["End"].Rows[i]["name"].ToString();
dt.Rows[i]["team"] = ds.Tables["End"].Rows[i]["team"].ToString();
dt.Rows[i]["ValueEnd"] = System.Convert.ToDecimal(ds.Tables["End"].Rows[i]["addPumpValue"]);
}
da = new SqlDataAdapter(strB, con.GetConnection());
ds = new DataSet();
da.Fill(ds, "Begin");
da.Dispose();
for (int j = 0; j < ds.Tables["Begin"].Rows.Count; j++)
{
for (int z = 0; z < dt.Rows.Count; z++)
{
if (dt.Rows[z]["name"].ToString() == ds.Tables["Begin"].Rows[j]["name"].ToString())
{
string bnn = ds.Tables["Begin"].Rows[j]["addPumpValue"].ToString();
dt.Rows[z]["ValueBegin"] = System.Convert.ToDecimal(ds.Tables["Begin"].Rows[j]["addPumpValue"]);
dt.Rows[z]["AddWat"] = System.Convert.ToDecimal(dt.Rows[z]["ValueEnd"]) - System.Convert.ToDecimal(dt.Rows[z]["ValueBegin"]);
dt.Rows[z]["AverageWat"] = Math.Round(System.Convert.ToDecimal(dt.Rows[z]["AddWat"]) / day, 2);
if (GetFluxJob(dt.Rows[z]["name"].ToString()) == 0)
{
continue;
}
dt.Rows[z]["MissWat"] = Math.Round(System.Convert.ToDecimal(dt.Rows[z]["AddWat"]) / GetFluxJob(dt.Rows[z]["name"].ToString()), 2);
}
}
}
return(dt);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return(null);
}
}
/// <summary>
///
/// </summary>
private void LoadTeamName()
{
string str = "select team from tbl_gprs_station group by team order by team desc";
SqlCommand cmd = new SqlCommand(str, con.GetConnection());
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
cmbTeam.Items.Add(dr.GetValue(0).ToString().Trim());
}
dr.Close();
cmbTeam.Text = cmbTeam.Items[0].ToString();
}
private void InsertGatherValue()
{
try
{
string strName = "select name,team from tbl_gprs_station order by team desc";
SqlDataAdapter daName = new SqlDataAdapter(strName, con.GetConnection());
DataSet dsName = new DataSet();
daName.Fill(dsName, "Name");
daName.Dispose();
int z = 0;
for (int i = 0; i < dsName.Tables["Name"].Rows.Count; i++)
{
string str = @"select top 1 name,team,oneGiveTemp,oneBackTemp,twoGiveTemp,twoBackTemp,
oneGivePress,oneBackPress,twoGivePress,twoBackPress,oneAccum,heatArea, teamorder
from v_HeatDatas where name='";
str = str + dsName.Tables["Name"].Rows[i]["name"].ToString();
str = str + "' and time between '";
str = str + m_DTime;
str = str + "' and '";
str = str + m_DTime.AddHours(12);
str = str + "' order by time asc";
SqlDataAdapter da = new SqlDataAdapter(str, con.GetConnection());
DataSet ds = new DataSet();
da.Fill(ds, "T");
da.Dispose();
for (int j = 0; j < ds.Tables["T"].Rows.Count; j++)
{
DataRow newrow = m_dt.NewRow();
m_dt.Rows.Add(newrow);
m_dt.Rows[z]["name"] = ds.Tables["T"].Rows[j]["name"].ToString();
m_dt.Rows[z]["name1"] = ds.Tables["T"].Rows[j]["name"].ToString();
m_dt.Rows[z]["team"] = ds.Tables["T"].Rows[j]["team"].ToString();
m_dt.Rows[z]["oneGiveTemp"] = System.Convert.ToDecimal(ds.Tables["T"].Rows[j]["oneGiveTemp"]);
m_dt.Rows[z]["oneBackTemp"] = System.Convert.ToDecimal(ds.Tables["T"].Rows[j]["oneBackTemp"]);
m_dt.Rows[z]["twoGiveTemp"] = System.Convert.ToDecimal(ds.Tables["T"].Rows[j]["twoGiveTemp"]);
m_dt.Rows[z]["twoBackTemp"] = System.Convert.ToDecimal(ds.Tables["T"].Rows[j]["twoBackTemp"]);
m_dt.Rows[z]["oneGivePress"] = System.Convert.ToDecimal(ds.Tables["T"].Rows[j]["oneGivePress"]);
m_dt.Rows[z]["oneBackPress"] = System.Convert.ToDecimal(ds.Tables["T"].Rows[j]["oneBackPress"]);
m_dt.Rows[z]["twoGivePress"] = System.Convert.ToDecimal(ds.Tables["T"].Rows[j]["twoGivePress"]);
m_dt.Rows[z]["twoBackPress"] = System.Convert.ToDecimal(ds.Tables["T"].Rows[j]["twoBackPress"]);
m_dt.Rows[z]["oneAccum"] = System.Convert.ToDecimal(ds.Tables["T"].Rows[j]["oneAccum"]);
// m_dt.Rows[z]["heatArea"]=System.Convert.ToDecimal(ds.Tables["T"].Rows[j]["heatArea"]);
m_dt.Rows[z]["teamorder"] = Convert.ToInt32(ds.Tables["T"].Rows[j]["teamorder"]);
z = z + 1;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
/// <summary>
///
/// </summary>
private void UpdateRecord()
{
try
{
int grstid = GetStationID(cmbPoint.Text.Trim());
if (grstid == -1)
{
MessageBox.Show(
"GetStationId error: " + cmbPoint.Text,
"错误",
MessageBoxButtons.OK,
MessageBoxIcon.Error
);
return;
}
con = new DBcon();
SqlCommand cmd = new SqlCommand("addPumpUpdate", con.GetConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@p_EditID", m_DataID);
cmd.Parameters.Add("@p_grStationID", grstid);
cmd.Parameters.Add("@p_Time", dtDate.Value.Date);
cmd.Parameters.Add("@p_addPumpValue", m_Value);
cmd.Parameters.Add("@p_Description", txtDescription.Text.Trim());
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
// 2007.05.30
//
//MessageBox.Show("数据编辑失败!","错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
ExceptionHandler.Handle("数据编辑失败!", ex);
}
}
/// <summary>
///
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
private int GetStationID(string name)
{
// int sID;
// string str="select gprs_station_id,name from tbl_gprs_station";
string sql = string.Format(
"select grstation_id from v_gprs_gr_xg where name = '{0}'",
name
);
con = new DBcon();
SqlCommand cmd = new SqlCommand(sql, con.GetConnection());
SqlDataReader dr = cmd.ExecuteReader();
int grstationId = -1;
while (dr.Read())
{
// if(dr.GetValue(1).ToString().Trim()==name)
// {
// sID=System.Convert.ToInt32(dr.GetValue(0));
// dr.Close();
// return sID;
// }
grstationId = int.Parse(dr[0].ToString());
}
dr.Close();
return(grstationId);
}
/// <summary>
///
/// </summary>
private void cmbStationLoad()
{
try
{
cmbStation.Items.Add("<全部站>");
string str = "select name from tbl_gprs_station order by team";
con = new DBcon();
SqlCommand cmd = new SqlCommand(str, con.GetConnection());
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
cmbStation.Items.Add(dr.GetValue(0).ToString().Trim());
}
dr.Close();
cmd.Dispose();
cmbStation.Text = cmbStation.Items[0].ToString();
}
catch (Exception ex)
{
// 2007.05.30
//
//MessageBox.Show("站点信息加载失败!","错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
ExceptionHandler.Handle("站点信息加载失败!", ex);
}
}
/// <summary>
///
/// </summary>
private void InitQueryBar()
{
try
{
this.panel1.Visible = false;
this.splitter1.Visible = false;
UnQuery();
cmbStation.Items.Add("<全部站>");
string str = "select Name from v_AddPumpDatas Group by name";
con = new DBcon();
SqlCommand cmd = new SqlCommand(str, con.GetConnection());
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
cmbStation.Items.Add(dr.GetValue(0).ToString().Trim());
}
dr.Close();
cmd.Dispose();
cmbStation.Text = cmbStation.Items[0].ToString();
}
catch (Exception ex)
{
// 2007.05.30
//
//MessageBox.Show("站点数据读取失败!","错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
ExceptionHandler.Handle("站点数据读取失败!", ex);
}
}
private void AddRecord()
{
try
{
Decimal i = System.Convert.ToDecimal(4.1868);
AddW = GiveW - BackW;
MissH = Math.Round((GiveW * (GiveT - BackT) + (GiveW - BackW) * BackT) * i / 1000, 2);
con = new DBcon();
SqlCommand cmd = new SqlCommand("addfactoryDatas", con.GetConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@p_Time", dtDate.Value.Date);
cmd.Parameters.Add("@p_GiveTemp", GiveT);
cmd.Parameters.Add("@p_BackTemp", BackT);
cmd.Parameters.Add("@p_GiveWat", GiveW);
cmd.Parameters.Add("@p_BackWat", BackW);
cmd.Parameters.Add("@p_AddWat", AddW);
cmd.Parameters.Add("@p_MissHeat", MissH);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("数据添加失败!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
ExceptionHandler.Handle("数据添加失败!", ex);
}
}
/// <summary>
///
/// </summary>
private void LoadData()
{
try
{
con = new DBcon();
if (TimeBeginEnd())
{
string str = "select * from v_AddPumpDatas where time between '" + m_dtBegin.Date + "' and '" + m_dtEnd.Date + "' order by team desc";
SqlDataAdapter da = new SqlDataAdapter(str, con.GetConnection());
DataSet ds = new DataSet();
da.Fill(ds, "addPumpDatas");
da.Dispose();
m_dataGrid.DataSource = ds.Tables["addPumpDatas"];
}
}
catch (Exception ex)
{
// 2007.05.30
//
//MessageBox.Show("数据读取失败!","错误",
//MessageBoxButtons.OK,
//MessageBoxIcon.Error);
ExceptionHandler.Handle("数据读取失败!", ex);
}
}
/// <summary>
///
/// </summary>
private void InsertDatas()
{
try
{
DataTable InsertDT = (DataTable)m_dataGrid.DataSource;
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < InsertDT.Rows.Count; j++)
{
if (InsertDT.Rows[j]["name"].ToString() == dt.Rows[i]["name"].ToString())
{
if (InsertDT.Rows[j]["addPumpValue"] != System.DBNull.Value)
{
con = new DBcon();
SqlCommand cmd = new SqlCommand("addPumpAdd", con.GetConnection());
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add("@p_grStationID",StationID(InsertDT.Rows[j]["name"].ToString()));
string stname = InsertDT.Rows[j]["name"].ToString().Trim();
int grstid = GetStationID(stname);
if (grstid == -1)
{
MessageBox.Show(
"未找到相关站名: " + stname,
"错误",
MessageBoxButtons.OK,
MessageBoxIcon.Error
);
continue;
}
else
{
cmd.Parameters.Add("@p_grStationId", grstid);
}
cmd.Parameters.Add("@p_Time", dtDate.Value.Date);
cmd.Parameters.Add("@p_addPumpValue", System.Convert.ToDecimal(InsertDT.Rows[j]["addPumpValue"]));
cmd.Parameters.Add("@p_Description", " ");
cmd.ExecuteNonQuery();
}
}
}
}
}
catch (Exception ex)
{
// 2007.05.30
//
//MessageBox.Show("数据录入错误，请检查数据格式是否输入错误");
ExceptionHandler.Handle("数据录入错误，请检查数据格式是否输入错误", ex);
}
}
private void InitializeCon(int index)
{
try
{
DBcon con = new DBcon();
string str = "select * from V_UserFunction where userID=" + index + " order by ID";
SqlDataAdapter da = new SqlDataAdapter(str, con.GetConnection());
ds = new DataSet();
da.Fill(ds, "UserFunction");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
public Decimal GetFlux(string StationName, DateTime dt)
{
decimal ValueFlux;
string str = GetQuestion(StationName, dt);
SqlCommand cmd = new SqlCommand(str, con.GetConnection());
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ValueFlux = System.Convert.ToDecimal(dr.GetValue(0));
dr.Close();
return(ValueFlux);
}
dr.Close();
return(0);
}
/// <summary>
///
/// </summary>
private void QueryData()
{
try
{
//ReadGridDataTitle();
con = new DBcon();
SqlDataAdapter da = new SqlDataAdapter(strQuery(), con.GetConnection());
DataSet ds = new DataSet();
da.Fill(ds, "addPumpDatas");
da.Dispose();
m_dataGrid.DataSource = ds.Tables["addPumpDatas"];
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());//"数据查询失败!","错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
}
}
/// <summary>
///
/// </summary>
/// <param name="Begin"></param>
/// <param name="End"></param>
private void LoadDatas(DateTime Begin, DateTime End)
{
try
{
string str = sqlStr(Begin, End);
SqlDataAdapter da = new SqlDataAdapter(str, con.GetConnection());
ds = new DataSet();
da.Fill(ds, "factory");
m_dataGrid.DataSource = ds.Tables["factory"];
}
catch (Exception ex)
{
// 2007.05.30
//
//MessageBox.Show("数据读取失败!","错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
ExceptionHandler.Handle("数据读取失败!", ex);
}
}
/// <summary>
///
/// </summary>
/// <param name="index"></param>
private void InitializeCon(int index)
{
try
{
DBcon con = new DBcon();
string str = "select * from V_UserFunction where userID=" + index + " order by ID";
SqlDataAdapter da = new SqlDataAdapter(str, con.GetConnection());
ds = new DataSet();
da.Fill(ds, "UserFunction");
}
catch (Exception ex)
{
// 2007.05.30
//
//MessageBox.Show("用户权限读取失败!","错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
ExceptionHandler.Handle("用户权限读取失败!", ex);
}
}
/// <summary>
///
/// </summary>
private void RefreshData()
{
try
{
con = new DBcon();
string str = "select * from v_AddPumpDatas where time between '" + m_dtBegin.Date + "' and '" + m_dtEnd.Date + "' order by team desc";
SqlDataAdapter da = new SqlDataAdapter(str, con.GetConnection());
DataSet ds = new DataSet();
da.Fill(ds, "addPumpDatasNew");
da.Dispose();
RefreshTitle();
m_dataGrid.DataSource = ds.Tables["addPumpDatasNew"];
b_New = true;
}
catch
{
MessageBox.Show("数据读取失败!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//#region StationID
///// <summary>
/////
///// </summary>
///// <param name="name"></param>
///// <returns></returns>
//private int StationID(string name)
//{
// int sID;
// string str="select gprs_station_id,name from tbl_gprs_station";
// con=new DBcon();
// SqlCommand cmd=new SqlCommand(str,con.GetConnection());
// SqlDataReader dr=cmd.ExecuteReader();
// while(dr.Read())
// {
// if(dr.GetValue(1).ToString().Trim()==name)
// {
// sID=System.Convert.ToInt32(dr.GetValue(0));
// dr.Close();
// return sID;
// }
// }
// dr.Close();
// return 0;
//}
//#endregion //
#region StationID
/// <summary>
///
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
private int GetStationID(string name)
{
string sql = string.Format(
"select grstation_id from v_gprs_gr_xg where name = '{0}'",
name
);
con = new DBcon();
SqlCommand cmd = new SqlCommand(sql, con.GetConnection());
SqlDataReader dr = cmd.ExecuteReader();
int grstationId = -1;
while (dr.Read())
{
grstationId = int.Parse(dr[0].ToString());
}
dr.Close();
return(grstationId);
}
/// <summary>
///
/// </summary>
private void LoadStation()
{
try
{
// fill station name combobox
//
string str = "select name from tbl_gprs_station";
con = new DBcon();
SqlCommand cmd = new SqlCommand(str, con.GetConnection());
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
cmbPoint.Items.Add(dr.GetValue(0).ToString().Trim());
}
dr.Close();
}
catch (Exception ex)
{
// 2007.05.30
//
//MessageBox.Show("站点数据读取失败!","错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
ExceptionHandler.Handle("站点数据读取失败!", ex);
}
}
/// <summary>
///
/// </summary>
private void AddRecord()
{
try
{
con = new DBcon();
SqlCommand cmd = new SqlCommand("addPumpAdd", con.GetConnection());
cmd.CommandType = CommandType.StoredProcedure;
int grStationId = GetStationID(cmbPoint.Text.Trim());
if (grStationId == -1)
{
MessageBox.Show(
"error grstation id: " + grStationId + ", grstation name: " + cmbPoint.Text,
"错误",
MessageBoxButtons.OK,
MessageBoxIcon.Error
);
return;
}
else
{
cmd.Parameters.Add("@p_grStationID",//GetStationID(cmbPoint.Text.ToString().Trim()));
grStationId);
}
cmd.Parameters.Add("@p_Time", dtDate.Value.Date);
cmd.Parameters.Add("@p_addPumpValue", m_Value);
cmd.Parameters.Add("@p_Description", txtDescription.Text.Trim());
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
// 2007.05.30
//
//MessageBox.Show("数据添加失败!","错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
ExceptionHandler.Handle("数据添加失败!", ex);
}
}
/// <summary>
///
/// </summary>
private void Delete()
{
if (NoRecordDataGrid())
{
return;
}
int row = m_dataGrid.CurrentCell.RowNumber;
int col = 1;
string UserName = m_dataGrid[row, col].ToString().ToString().Trim();
if (UserName == null)
{
MessageBox.Show("请正确选择需要删除的数据行!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (MessageBox.Show("确定要删除该行数据?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) != DialogResult.Yes)
{
return;
}
row = m_dataGrid.CurrentCell.RowNumber;
col = 0;
DBcon con = new DBcon();
int id = Convert.ToInt32(m_dataGrid[row, col].ToString());
SqlCommand sqlCmd = new SqlCommand("DELETE FROM tbl_addPumpDatas WHERE ID=" + id, con.GetConnection());
sqlCmd.ExecuteNonQuery();
sqlCmd.Dispose();
RefreshData();
}
private void EditLoad()
{
DateTime dt;
try
{
con = new DBcon();
SqlCommand cmd = new SqlCommand("select * from tbl_factorydatas where id=@m_id", con.GetConnection());
cmd.Parameters.Add("@m_id", m_DataID);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
dt = System.Convert.ToDateTime(dr["time"]);
dtDate.Value = dt.Date;
txtBackT.Text = dr["BackTemp"].ToString();
txtGiveT.Text = dr["GiveTemp"].ToString();
txtBackW.Text = dr["BackWat"].ToString();
txtGiveW.Text = dr["GiveWat"].ToString();
}
dr.Close();
}
catch (Exception ex)
{
// 2007.05.30
//
//MessageBox.Show("数据读取失败!","错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
ExceptionHandler.Handle("数据读取失败!", ex);
}
}
/// <summary>
///
/// </summary>
private void EditLoad()
{
DateTime dt;
try
{
con = new DBcon();
SqlCommand cmd = new SqlCommand("select * from v_AddPumpDatas where id=@m_id", con.GetConnection());
cmd.Parameters.Add("@m_id", m_DataID);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
dt = System.Convert.ToDateTime(dr["time"]);
dtDate.Value = dt.Date;
cmbPoint.Text = dr["Name"].ToString();
txtDescription.Text = dr["Description"].ToString();
txtValue.Text = dr["addPumpValue"].ToString();
}
dr.Close();
}
catch (Exception ex)
{
// 2007.05.30
//
//MessageBox.Show("数据读取失败!","错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
ExceptionHandler.Handle("数据读取失败!", ex);
}
}