学习总结

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()

End Sub

这两个函数名是固定的，表示打开报表或者刷新报表要执行的代码
========================================================================================
13 关于连接查询效率感受：
select * from a,b where a.id=b.id > join...on > left(right) join on

   这个规律是自己在测试几个sql语句时发现的。在需要完全匹配来连接两张表时最好选择where语句进行连接。left(right) join在
   不得不使用的情况下才使用。
========================================================================================
14.