コード例 #1
0
        public void prepare_variats()
        {
            search_list = new List <Search_Element>();

            string zapros =
                "SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS where table_name = 'itemtable' and table_schema = 'documentassistant'";
            SQL_Class sqlcl = SQL_Class.Create_class();

            sqlcl.ReadValues(zapros);
            while (sqlcl.SQL_DataReader.Read())
            {
                if (sqlcl.SQL_DataReader.GetString(0) != "ID")
                {
                    search_list.Add(new Search_Element(sqlcl.SQL_DataReader.GetString(0), sqlcl.SQL_DataReader.GetString(1)));
                }
            }

            Refresh();
        }
コード例 #2
0
        public DataTable get_Table(List <string> mas_eng, string str)
        {
            string[] or_like = new string[0];
            if (All_Search_chBox.Checked)
            {
                string[] sv = All_Search_TBox.Text.Split('|');
                or_like = new string[sv.Length];
                for (int i = 0; i < sv.Length; i++)
                {
                    string cur = "";
                    for (int j = 1; j < mas_eng.Count; j++)
                    {
                        if (!"Data_StartIngest_StartIngest_EndTime_Of_Registration".Contains(mas_eng[j]))
                        {
                            if (sv[i][0] == '@')
                            {
                                cur += "  or  " + mas_eng[j] + " like '" + sv[i].Replace("#", "").Replace("!", "") + "' ";
                            }
                            else
                            {
                                cur += "  or  " + mas_eng[j] + " like '%" + sv[i].Replace("#", "").Replace("!", "") + "%' ";
                            }
                            //cur += "  or  " + mas_eng[j] + " like "+ sv[i].Replace("#","").Replace("@","") + " ";
                        }
                    }

                    or_like[i] = " and ( false " + cur + " )";
                    if (sv[i][0] == '#')
                    {
                        or_like[i] = or_like[i].Replace(" and (", " and NOT (");
                    }
                }
            }


            string agregate = str;

            string pre_zapros = "";


            if (agregate.Contains("PrID"))
            {
                pre_zapros = "SELECT MDA.ID,PrID,MDA.ID  " + agregate.Replace("PrID", "St.Name") + " FROM     MetaDataArchive as MDA   left join ingestassistantsettingsbase.settings as St  on St.ID=PrID  where( Deleted=0  ";
            }
            else
            {
                pre_zapros = "SELECT MDA.ID,PrID,MDA.ID  " + agregate + " FROM     MetaDataArchive as MDA   where (Deleted=0  ";
            }

            if (ViPlanner_ChBOX.Checked)
            {
                pre_zapros += "AND ViPlanner='" + Viplanner_TBox.Text + "'  ";
            }

            if (Reel_ID_ChBox.Checked)
            {
                pre_zapros += "AND Reel_ID='" + Reel_Id_TBox + "'  ";
            }


            if (Pr_Filter_ChBox.Checked)
            {
                pre_zapros += "AND PrID=" +
                              SQL_Class.ReadValueInt32(
                    "select ID from settings where Name='" + Pr_Filter_CmBox.SelectedItem + "'",
                    Properties.Settings.Default.Setting_Base_Path) + "  ";
            }

            if (D_Date_ChBox.Checked)
            {
                DateTime d1;
                DateTime d2;
                if (D_One_RBut.Checked)
                {
                    d1 = Date_Filter1.Value.Date;
                    d2 = d1.AddDays(1).Date;
                }
                else
                {
                    d1 = Date_Filter1.Value.Date;
                    d2 = Date_Filter2.Value.AddDays(1).Date;
                }
                pre_zapros += "AND (Time_Of_Registration between '" + d1.ToString("yyyy.MM.dd HH:mm:ss") + "' and  '" +
                              d2.ToString("yyyy.MM.dd HH:mm:ss") + "'  )";
            }

            if (All_Search_chBox.Checked)
            {
                string zapr = "";
                pre_zapros += "  ";
                for (int i = 0; i < or_like.Length; i++)
                {
                    zapr += pre_zapros + or_like[i] + ") union ";
                }
                if (or_like.Length != 0)
                {
                    zapr = zapr.Substring(0, zapr.Length - 7);
                }
                else
                {
                    zapr = pre_zapros + " false)";
                }

                pre_zapros = zapr;
            }
            else
            {
                pre_zapros += ")";
            }



            if (pre_zapros.Contains("UserID"))
            {
                pre_zapros = pre_zapros.Replace("UserID", "L.L_FullName");
                pre_zapros = pre_zapros.Replace("where",
                                                "left join ingestassistantsettingsbase.login as L  on L.L_ID=UserID  where");
            }
            if (pre_zapros.Contains("RegisterID"))
            {
                pre_zapros = pre_zapros.Replace("RegisterID", "LL.L_FullName");
                // if (!pre_zapros.Contains("left join ingestassistantsettingsbase.login as L  on L_ID=UserId"))
                { pre_zapros = pre_zapros.Replace("where",
                                                  "left join ingestassistantsettingsbase.login as LL  on LL.L_ID=RegisterID  where"); }
            }



            pre_zapros = pre_zapros.Replace(" ID", "MDA.ID");

            var sqlcon     = SQL_Class.Create_class(Ingest_Assistant.Properties.Settings.Default.MetaBase_Way);
            int rows_count = 0;


            //vk
            MySqlDataAdapter mad = new MySqlDataAdapter(pre_zapros, sqlcon.SQL_Connection);
            //DataSet set = new DataSet();
            DataTable tb = new DataTable();

            mad.Fill(tb);
            return(tb);
        }