学习总结
1，安装simatic it的报表服务器事，注意要配置ODBC连接。否则连接服务器时出错。
===============================================================
2，@Prompt('选择班次','A',{'全部','制丝上半夜班','制丝白班'} ,MULTI, FREE)
=================================================================
3，Designer:语意层
--@Prompt('用户姓名：','A',{'刘新','开发','老王'} ,MULTI, FREE)
select * from Bas_user where NM='刘新'
================================================================
4，左连接，右连接，完全连接的区别：
LEFT JOIN 或 LEFT OUTER JOIN。
左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
RIGHT JOIN 或 RIGHT OUTER JOIN。
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
FULL JOIN 或 FULL OUTER JOIN。
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
==================================================================
5：union 查询： 见我的博客。
===================================================================
6：看看这种使用方法
SELECT N'全部'as NM,'-1' as ID union SELECT a.shift_name_cn AS NM,a.shift_id AS ID FROM dbo.uv_SITMesDb_SHC_Shift a WHERE a.area_id='S_QZFACTORY.A_PRIMARY' ORDER BY ID
======================================================================
7： select ID,date,shift_id,shift_name,team_id,team,yt_quantity,yg_quantity,uom,op_name,op_time from Loc_Dottle_Data where date >=cast('2009-09-11' as datetime) and date <= cast('2009-12-11' as datetime) and shift_id like '%' and team_id like '%'
看看这种搜索查询方法，不用写if语句夜可以实现
====================================================================
8：cast(b.MESOrderID AS varchar) 将某个字段的数据类型进行转换
=====================================================================
9：看看这个SQL语句的查询方式
select a.material_id,b.defname as material_name,a.quantity,a.uom,a.Get_info_time from
(select material_id,sum(quantity) as quantity,uom,Get_info_time from Loc_material
group by material_id,uom,Get_info_time) a,
uv_SITMesDB_MM_Definitions b
where a.material_id=b.defid and a.Get_info_time >=cast('2009-11-1' AS datetime) AND a.Get_info_time <=cast('2009-12-20' AS datetime)
order by a.material_id
====================================================================
10 FTP文件上传
private void button3_Click(object sender, EventArgs e)
{
if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
{
string uri = "http://www.cnblogs.com/wantingqiang/admin/ftp://192.168.31.10/";
string username = "admin";
string password = "admin";
string fileName = this.openFileDialog1.FileName.ToString().Substring(this.openFileDialog1.FileName.ToString().LastIndexOf("\\") + 1);
if (Upload(this.openFileDialog1.FileName.ToString(), uri + fileName, username, password))
{
MessageBox.Show("文件" + fileName + "已经成功上传到服务器" + uri);
}
else
{
MessageBox.Show("上传失败");
}
}
}
#region 上传文件
private bool Upload(string fileName, string uploadUrl, string UserName, string Password)
{
Stream requestStream = null;
FileStream fileStream = null;
FtpWebResponse uploadResponse = null;
try
{
FtpWebRequest uploadRequest = (FtpWebRequest)WebRequest.Create(uploadUrl);
uploadRequest.Method = WebRequestMethods.Ftp.UploadFile;// 设置method
uploadRequest.Proxy = null;
if (UserName.Length > 0)//添加登陆凭据
{
NetworkCredential nc = new NetworkCredential(UserName, Password);
uploadRequest.Credentials = nc;
}
requestStream = uploadRequest.GetRequestStream();
fileStream = File.Open(fileName, FileMode.Open);
byte[] buffer = new byte[1024];
int bytesRead;
while (true) //开始上传
{
bytesRead = fileStream.Read(buffer, 0, buffer.Length);
if (bytesRead == 0)
break;
requestStream.Write(buffer, 0, bytesRead);
}
requestStream.Close();
uploadResponse = (FtpWebResponse)uploadRequest.GetResponse();
return true;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (uploadResponse != null)
{
uploadResponse.Close();
}
if (fileStream != null)
{
fileStream.Close();
}
if (requestStream != null)
{
requestStream.Close();
}
}
}
#endregion
=====================================================================================
11
private DataTable _UserFunOperTable;
DataRow[] dr = _UserFunOperTable.Select("Fun_cd='"+funClass+"' and Oper_cd='"+operid+"' and User_id='"+userid+"'");
对于一个直接含有数据的DataTable 可以直接使用Select方法进行数据的查询，返回满足条件的数据行集合。
=====================================================================================
12 在Simatic IT 的报表制作中可以使用以下的VB代码对@variable("XXXX")赋初值。
Private Sub Document_Activate()
ActiveDocument.Variables.Item("开始时间").Value = CStr(Year(Now)) + "-" + CStr(Month(Now)) + "-1"
ActiveDocument.Variables.Item("结束时间").Value = CStr(Year(Now)) + "-" + CStr(Month(Now)) + "-" + CStr(Day(Now))
End Sub
Private Sub Document_AfterRefresh()
ActiveDocument.Variables.Item("开始时间").Value = CStr(Year(Now)) + "-" + CStr(Month(Now)) + "-1"
ActiveDocument.Variables.Item("结束时间").Value = CStr(Year(Now)) + "-" + CStr(Month(Now)) + "-" + CStr(Day(Now))
End Sub
这两个函数名是固定的，表示打开报表或者刷新报表要执行的代码
========================================================================================
13 关于连接查询效率感受：
select * from a,b where a.id=b.id > join...on > left(right) join on
这个规律是自己在测试几个sql语句时发现的。在需要完全匹配来连接两张表时最好选择where语句进行连接。left(right) join在
不得不使用的情况下才使用。
========================================================================================
14.
