private void GvBind()
{
SqlParameter[] p = new SqlParameter[] {
new SqlParameter("@ProjectNo", cbbXM.SelectedValue),
new SqlParameter("@PartNo", cbbLJ.SelectedValue)
};
var dbc = db.GetSqlStringCommand("SELECT [MeasReportID],[ProjectNo],[PartNo],[PartName],[CreateTime] FROM [MeasurementReports] WHERE (''=@ProjectNo OR [ProjectNo]=@ProjectNo) AND (''=@PartNo OR [PartNo]=@PartNo)");
dbc.Parameters.AddRange(p);
var ds = db.ExecuteDataSet(dbc);
GvList.DataSource = ds.Tables[0];
}
private void bind()
{
string status = "";
string PreWwarningTimeStart = "";
string PreWwarningTimeEnd = "";
if (!cbbStatus.SelectedItem.ToString().Contains("请选择"))
{
if (cbbStatus.SelectedItem.ToString().Contains("未处理"))
{
status = "0";
}
else
{
status = "1";
}
}
if (!(dateTimePicker1.CustomFormat == " "))
{
PreWwarningTimeStart = dateTimePicker1.Value.ToString().Trim();
}
if (!(dateTimePicker2.CustomFormat == " "))
{
PreWwarningTimeEnd = dateTimePicker2.Value.ToString().Trim();
}
SqlParameter[] p = new SqlParameter[] {
new SqlParameter("@PreWwarningTimeStart", PreWwarningTimeStart),
new SqlParameter("@PreWwarningTimeEnd", PreWwarningTimeEnd),
new SqlParameter("@status", status)
};
string sql = @"SELECT [id],[Message],[Filed1],[Filed2],[WarningType],[PreWwarningTime],[DoTime],case when status = 0 then '未处理' else '已处理' end as [status] FROM [dbo].[Warnings]
where
(''=@PreWwarningTimeStart or ''=@PreWwarningTimeEnd
or (PreWwarningTime>=@PreWwarningTimeStart and PreWwarningTime<=@PreWwarningTimeEnd)
)
and (''=@status or status=@status)
";
var dbc = db.GetSqlStringCommand(sql);
dbc.Parameters.AddRange(p);
var ds = db.ExecuteDataSet(dbc);
GvList.DataSource = ds.Tables[0];
}
private void bind()
{
string strSqlReport = "select distinct [ProjectNo] text,[ProjectNo] value from [MeasurementReports]";
var ds = db.ExecuteDataSet(CommandType.Text, strSqlReport);
if (null != ds)
{
var dt = ds.Tables[0];
DataRow newRow;
newRow = dt.NewRow();
newRow["text"] = "==请选择项目名称==";
newRow["value"] = "";
dt.Rows.InsertAt(newRow, 0);
cbbXM.ValueMember = "value";
cbbXM.DisplayMember = "text";
cbbXM.DataSource = dt;
}
}
/// <summary>
/// Exceute an sql statement (or multiple) and return a dataset
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataSet ExecuteDataSet(string sql)
{
try
{
Log.Debug("\tSQL: " + sql);
DataSet ret = ODHR.Utils.Caching.CallStackCaching.GetFromCache <DataSet>(sql);
if (ret != null)
{
return(ret);
}
ret = Db.ExecuteDataSet(CommandType.Text, sql);
ODHR.Utils.Caching.CallStackCaching.SaveToCache(sql, ret);
return(ret);
}
catch (Exception e)
{
Log.Fatal("\tExecuteDataSet - " + e.Message + " SQL:[" + (sql ?? "Null") + "]", e);
throw;
}
}
private void bind()
{
SqlParameter[] p = new SqlParameter[] {
new SqlParameter("@MeasReportID", id)
};
var dbc = db.GetSqlStringCommand("SELECT [MeasItemID],[MeasReportID],[MeasItemNO] FROM [MeasurementItems] where MeasReportID=@MeasReportID");
dbc.Parameters.AddRange(p);
var ds = db.ExecuteDataSet(dbc);
GvList.DataSource = ds.Tables[0];
}
private void btnOk_Click(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(txtUser.Text.Trim()) || string.IsNullOrWhiteSpace(txtPwd.Text))
{
MessageBox.Show("用户名，密码不能是空");
}
else
{
SqlParameter[] p = new SqlParameter[] {
new SqlParameter("@username", txtUser.Text.Trim()),
new SqlParameter("@userType", UserType.Application)
};
var dbc = db.GetSqlStringCommand("select * from Users where username=@username and userType=@userType");
dbc.Parameters.AddRange(p);
var ds = db.ExecuteDataSet(dbc);
if (null != ds && ds.Tables[0].Rows.Count > 0)
{
var pwd = ds.Tables[0].Rows[0]["password"].ToString();
if (txtPwd.Text.Trim() != pwd)
{
MessageBox.Show("用户名，密码不正确");
}
else
{
base.DialogResult = DialogResult.OK;
}
}
else
{
MessageBox.Show("用户名，密码不正确");
}
}
}
// GET: api/MeasureDatas
public IQueryable <JsonProject> GetCreateJson(string ids, string username)
{
try
{
string sql = @"
select a.* from [Vw_ProjectSettingWithId] a
left join [Vw_ProjectSettingWithId] b on a.ProjectSetting_ProjectNO=b.ProjectSetting_ProjectNO and a.ProjectSetting_PartsNO=b.ProjectSetting_PartsNO
where b.ProjectSetting_ID in ({0});
select b.* from [PointSetting] b
left join [ProjectSetting] c on b.PointSetting_ProjectSetting_ID=c.ProjectSetting_ID
left join [ProjectSetting] d on c.ProjectSetting_ProjectNO=d.ProjectSetting_ProjectNO and c.ProjectSetting_PartsNO =d.ProjectSetting_PartsNO
where d.ProjectSetting_ID in ({0}) and b.PointSetting_IsDeleted=0 order by b.PointSetting_Index asc ;
select [UserInfo_RealName],[UserInfo_password] from [User_Info] where [UserInfo_Type]=2
";
var ds = _db.ExecuteDataSet(CommandType.Text, string.Format(sql, ids));
var tab1 = ds.Tables[0];
var tab2 = ds.Tables[1];
var tab3 = ds.Tables[2];
var jsonProject = new List <JsonProject>()
{
};
DataTable MeasurementReport = tab1.DefaultView.ToTable(true, "ProjectSetting_ProjectNO", "ProjectId");
foreach (DataRow row in MeasurementReport.Rows)
{
jsonProject.Add(GetJsonProject(tab1, tab2, int.Parse(row["ProjectId"].ToString()), row["ProjectSetting_ProjectNO"].ToString()));
}
List <JsonUsers> jsonUser = new List <JsonUsers>()
{
};
foreach (DataRow row in tab3.Rows)
{
jsonUser.Add(new JsonUsers()
{
user_id = 0,
username = row["UserInfo_RealName"].ToString(),
password = row["UserInfo_password"].ToString()
});
}
JavaScriptSerializer jsonSerialize = new JavaScriptSerializer();
#region project
var TEMP = jsonSerialize.Serialize(jsonProject);
var mappedPath = System.Web.Hosting.HostingEnvironment.MapPath("~/res/" + username + ".json");
if (System.IO.File.Exists(mappedPath))
{
System.IO.File.Delete(mappedPath);
}
FileStream fs = new FileStream(mappedPath, FileMode.Create);
//获得字节数组
//byte[] data = System.Text.Encoding.Default.GetBytes(TEMP.Replace("\\u0026", "&"));
byte[] data = System.Text.Encoding.GetEncoding("UTF-8").GetBytes(TEMP.Replace("\\u0026", "&"));
//开始写入
fs.Write(data, 0, data.Length);
//清空缓冲区、关闭流
fs.Flush();
fs.Close();
#endregion
#region User
var tempUser = jsonSerialize.Serialize(jsonUser);
var userpath = System.Web.Hosting.HostingEnvironment.MapPath("~/res/user.json");
if (System.IO.File.Exists(userpath))
{
System.IO.File.Delete(userpath);
}
FileStream fsUser = new FileStream(userpath, FileMode.Create);
//获得字节数组
//byte[] data = System.Text.Encoding.Default.GetBytes(TEMP.Replace("\\u0026", "&"));
byte[] data1 = System.Text.Encoding.GetEncoding("UTF-8").GetBytes(tempUser.Replace("\\u0026", "&"));
//开始写入
fsUser.Write(data1, 0, data1.Length);
//清空缓冲区、关闭流
fsUser.Flush();
fsUser.Close();
return(jsonProject.AsQueryable());
#endregion
}
catch (Exception ex)
{
throw ex;
}
}
private void btnDown_Click(object sender, EventArgs e)
{
string sql = @"SELECT
[ProjectName] 项目号
,[ProductName] 零件号
,[TargetName] 检测项
,[MeasurePoint] 检测点
,[Direction] 方向
,[UpperTolerance] 上公差
,[LowerTolerance] 下公差
,[Value1] 值1
,[Value2] 值2
,[Value3] 值3
,[Value4] 值4
,[Value5] 值5
,[Value6] 值6
,[Value7] 值7
,[Value8] 值8
,[Value9] 值9
,[Value10] 值10
,[Username] 用户
,CONVERT(varchar(100), DATEADD(s,convert(int,Timestamp/1000),'1970-01-01 08:00:00'), 112) 时间
FROM [MeasureDatas]
where (''=@ProjectName or ProjectName like @ProjectName)
and (''=@ProductName or ProductName like @ProductName)
and (''=@TargetName or TargetName like @TargetName)
"
;
SqlParameter[] p = new SqlParameter[] {
new SqlParameter("@ProjectName", "%" + txtXM.Text.Trim() + "%"),
new SqlParameter("@ProductName", "%" + txtCP.Text.Trim() + "%"),
new SqlParameter("@TargetName", "%" + txtJCX.Text.Trim() + "%")
};
var dbc = db.GetSqlStringCommand(sql);
dbc.Parameters.AddRange(p);
var ds = db.ExecuteDataSet(dbc);
if (null == ds || ds.Tables[0].Rows.Count <= 0)
{
MessageBox.Show("没有可下载文件");
}
FolderBrowserDialog path = new FolderBrowserDialog();
path.ShowDialog();
var dir = path.SelectedPath;
var FillPath = string.Format("{0}/{1}", dir, Guid.NewGuid().ToString() + ".xlsx");
ExcelHelper1 excel = new ExcelHelper1(FillPath);
excel.DataTableToExcel(ds.Tables[0], "检测值", true);
excel.Dispose();
MessageBox.Show("下载成功");
}
/// <summary>
/// 查询
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(comboBox2.SelectedValue.ToString().Trim()))
{
MessageBox.Show("检验项目不能为空！");
return;
}
if (string.IsNullOrEmpty(comboBox1.SelectedValue.ToString()))
{
MessageBox.Show("报告单号不能为空！");
return;
}
try
{
this.dataGridView1.DataSource = null;
this.pictureBox1.Image = null;
//测量报告表
StringBuilder tempSql = new StringBuilder();
tempSql.Append(" select c.MeasPageNo,d.PointNo,d.PointType,d.XAxis,d.YAxis,d.ZAxis,d.UpperTol,d.LowerTol,d.Direct,d.[AVG],d.CorrectDirect");
tempSql.Append(" from ((MeasurementReports a left join MeasurementItems b on a.MeasReportID=b.MeasReportID) ");
tempSql.Append(" left join MeasurementPages c on b.MeasItemID=c.MeasItemID)");
tempSql.Append(" left join MeasurementPoints d on c.MeasPageID=d.MeasPageID");
tempSql.Append(" where 1=1 ");
tempSql.Append(" and a.ProjectNo='" + comboBox1.SelectedValue.ToString() + "'");
tempSql.Append(" and b.MeasItemName='" + comboBox2.SelectedValue.ToString() + "'");
if (!string.IsNullOrEmpty(txtYM.Text.Trim()))
{
tempSql.Append(" and c.MeasPageNo=" + txtYM.Text.Trim());
}
tempSql.Append(" order by a.MeasReportID,b.MeasItemID,c.MeasPageID,d.MeasPointID");
Os.Brain.Common.Debug.WriteLog("./debug.txt", tempSql.ToString());
var ds = db.ExecuteDataSet(CommandType.Text, tempSql.ToString());
if (null == ds && ds.Tables.Count <= 0)
{
MessageBox.Show("查无数据");
return;
}
DataTable dt = ds.Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
this.dataGridView1.DataSource = dt;
}
for (int i = 0; i < this.dataGridView1.Columns.Count; i++)
{
this.dataGridView1.Columns[i].SortMode = DataGridViewColumnSortMode.NotSortable;
}
if (!string.IsNullOrEmpty(txtYM.Text.Trim()))
{
StringBuilder tempSqlimage = new StringBuilder();
tempSqlimage.Append(" select c.MeasPageImage");
tempSqlimage.Append(" from (MeasurementReports a left join MeasurementItems b on a.MeasReportID=b.MeasReportID) ");
tempSqlimage.Append(" left join MeasurementPages c on b.MeasItemID=c.MeasItemID");
tempSqlimage.Append(" where 1=1 ");
tempSqlimage.Append(" and a.ProjectNo='" + comboBox1.SelectedValue.ToString() + "'");
tempSqlimage.Append(" and b.MeasItemName='" + comboBox2.SelectedValue.ToString() + "'");
tempSqlimage.Append(" and c.MeasPageNo=" + txtYM.Text.Trim());
tempSqlimage.Append(" order by a.MeasReportID,b.MeasItemID,c.MeasPageID");
var dsimg = db.ExecuteDataSet(CommandType.Text, tempSqlimage.ToString());
DataTable dtimage = dsimg.Tables[0];
if (dtimage != null && dtimage.Rows.Count > 0)
{
Byte[] bytes = dtimage.Rows[0][0] as Byte[];
if (null != bytes)
{
MemoryStream ms = new MemoryStream(bytes); //核心方法 将图片加载到内存流中
this.pictureBox1.Image = Image.FromStream(ms);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void GvBind()
{
if (this.pid <= 1)
{
this.pid = 1;
}
if (this.pid >= max)
{
this.pid = max;
}
SqlParameter[] p = new SqlParameter[] {
new SqlParameter("@MeasPageNo", pid),
new SqlParameter("@MeasItemID", iid)
};
string sql = @"SELECT a.MeasPageNo,b.MeasItemNO,c.ProjectNo,c.PartNo,c.PartName
FROM [QfbServer].[dbo].[MeasurementPages] a
left join [dbo].[MeasurementItems] b on a.MeasItemID=b.MeasItemID
left join [dbo].[MeasurementReports] c on b.MeasReportID=c.MeasReportID
where a.MeasPageNo=@MeasPageNo and b.MeasItemID=@MeasItemID;
select [MeasPointID],[PointNo],[UpperTol],[LowerTol],[Direct]
from [dbo].[MeasurementPoints] a
left join [dbo].[MeasurementPages] b on a.MeasPageID=b.MeasPageID
where b.MeasPageNo=@MeasPageNo and b.MeasItemID=@MeasItemID;
SELECT count(*) num FROM [MeasurementPages] where [MeasItemID]=@MeasItemID
";
var dbc = db.GetSqlStringCommand(sql);
dbc.Parameters.AddRange(p);
var ds = db.ExecuteDataSet(dbc);
if (null != ds)
{
lbl1.Text = string.Format(_temp,
ds.Tables[0].Rows[0]["ProjectNo"].ToString(),
ds.Tables[0].Rows[0]["PartNo"].ToString(),
ds.Tables[0].Rows[0]["PartName"].ToString(),
ds.Tables[0].Rows[0]["MeasItemNO"].ToString()
);
var _num = ds.Tables[2].Rows[0][0].ToString();
if (!string.IsNullOrEmpty(_num))
{
max = int.Parse(_num);
}
if (pid > 1)
{
btnPre.Enabled = true;
}
else
{
btnPre.Enabled = false;
}
if (pid < max)
{
btnNext.Enabled = true;
}
else
{
btnNext.Enabled = false;
}
lblPage.Text = string.Format("{0} of {1}", pid, max);
GvList.DataSource = ds.Tables[1];
}
}