Beispiel #1
0
        private static List <string> GetExistingExDatabaseTableNameAndStructure(List <string> exChangeDbTableList, Dictionary <string, List <PdmEntityColumnClrDto> > dictExchangeTableColumnDto)
        {
            using (SqlConnection conn = new SqlConnection(PLM_ExChangeDatabase_ConnectionString))
            {
                conn.Open();

                string QueryExchangeTable = string.Format(@" select distinct Left(  sysobj .Name , 50 ) AS [ExChangeTableName] from sysObjects  as sysobj 
            inner join dbo.sysColumns  as sysColumn on sysColumn.ID =  sysobj .ID
            where   sysobj .name like '{0}%' and sysColumn.Name in ( '{1}', '{2}' ,'{3}' ,'{4}' ,'{5}')",
                                                          PLMConstantString.EX_PLM_Import_Prefix,
                                                          PLMConstantString.ExchangeRowDataERPFlagColumn,
                                                          PLMConstantString.ExchangeRowDataPLMFlagColumn,
                                                          PLMConstantString.ExchangeRowDataERPExportDateTimeColumn,
                                                          PLMConstantString.ExchangeRowDataPLMImportDateTimeColumn,
                                                          PLMConstantString.ExchangeRowDataPLMPrimayKeyColumn

                                                          );

                CLROutput.Output("QueryExchangeTable=" + QueryExchangeTable);

                exChangeDbTableList = DataAcessHelper.GetDataTableQueryResult(conn, QueryExchangeTable).AsEnumerable().Select(o => (o["ExChangeTableName"] as string).Trim().ToLowerInvariant()).ToList();



                foreach (string exchangeTable in exChangeDbTableList)
                {
                    List <PdmEntityColumnClrDto> exEntituyColumn = PLMSEntityClrBL.GetExchangeDatabaseTableColumnDto(conn, exchangeTable);
                    dictExchangeTableColumnDto.Add(exchangeTable, exEntituyColumn);
                }
            }
            return(exChangeDbTableList);
        }
        public static void GetSpecQcSize(int referenceId, int mainQcTabId)
        {
            // Initianize context FROM PLM App,
            using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
            {
                //SqlContext.
                conn.Open();

                DataAcessHelper.ExecuteReadUnCommmited(conn);

                // Get SubitemID
                //int baseSizeIndexFromZero = 0;
                //int totalSizeNumber = 0;
                DataTable returnDataTble = GetOneReferenceSpecQcSelectedSizeTable(referenceId, conn, mainQcTabId);

                CLROutput.SendDataTable(returnDataTble);

                //CLROutput.OutputDebug("baseSizeIndex=" + baseSizeIndexFromZero);
                //CLROutput.OutputDebug("totalSizeNumber=" + totalSizeNumber);



                DataAcessHelper.ExecuteReadCommmited(conn);
            }
        }
        internal static string GetLastChangeCheckSum(SqlConnection conn, string tablname)
        {
            //  string querylastScanTimstmap = @" select master.sys.fn_varbintohexstr(cast( MAX(SystemTimeStamp) as varbinary(8)))as  varbintohexstr   from  " + tablname;

            string querylastScanTimstmap = @" SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM  " + tablname;


            //  SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM tblsketch WITH (NOLOCK);

            try
            {
                object lastScaltime = DataAcessHelper.RetriveSigleValue(conn, querylastScanTimstmap, null);
                if (lastScaltime == null)
                {
                    return(string.Empty);
                }
                else
                {
                    return(lastScaltime.ToString());
                }
            }
            catch (Exception ex)
            {
                CLROutput.OutputDebug(tablname + "missing Timstmap" + ex.ToString());
                return(string.Empty);
            }
        }
Beispiel #4
0
        public static void RestartClr()
        {
            try
            {
                // using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
                using (SqlConnection conn = new SqlConnection("context connection=true"))
                {
                    //SqlContext.
                    conn.Open();

                    string restcmd = @"EXEC SP_CONFIGURE 'clr enabled' , '0'
                                    RECONFIGURE;
                                    EXEC SP_CONFIGURE 'clr enabled' , '1';
                                    RECONFIGURE;
                                   ";

                    try
                    {
                        DataAcessHelper.ExecuteNonQuery(conn, restcmd);
                        //PLMSCacheSystem.TouchCache();
                    }
                    catch { };
                }
            }
            catch { }

            //PLMSDWStoredProcedures.GenerateUserDefinTableScript();
        }
Beispiel #5
0
        internal static string GetConnectionInfoWithCode(int?dataSourceFrom)
        {
            if (dataSourceFrom.HasValue)
            {
                string dataSourceFronQuery       = @"select ConnectionString from PdmDataSource where  DataSourceFrom= @DataSourceFrom";
                List <SqlParameter> listParamter = new List <SqlParameter> ();
                listParamter.Add(new SqlParameter("@DataSourceFrom", dataSourceFrom));

                string dataSourValue = null;
                using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
                {
                    conn.Open();
                    dataSourValue = DataAcessHelper.RetriveSigleValue(conn, dataSourceFronQuery, listParamter) as string;
                }

                if (string.IsNullOrEmpty(dataSourValue))
                {
                    return(PLMConstantString.PLM_APP_ConnectionString);
                }
                else
                {
                    return(dataSourValue.ToString());
                }
            }
            else
            {
                return(PLMConstantString.PLM_APP_ConnectionString);
            }
        }
Beispiel #6
0
        private static List <string> GetExternalNappingName(int gridTabId, int currentGridBlockId)
        {
            List <string> toReturn = new List <string>();
            string        query    = string.Format(@"SELECT  distinct   dbo.pdmTabGridMetaColumn.GridColumnID,
                        dbo.pdmTabGridMetaColumn.Visible, dbo.pdmTabGridMetaColumn.ExternalMappingName ,

                         dbo.pdmTabGridMetaColumn.BlockID AS GridBlockID
                        FROM         dbo.pdmBlockSubItem INNER JOIN
                                              dbo.PdmTabBlock ON dbo.pdmBlockSubItem.BlockID = dbo.PdmTabBlock.BlockID LEFT OUTER JOIN
                                              dbo.pdmTabGridMetaColumn ON dbo.PdmTabBlock.BlockID = dbo.pdmTabGridMetaColumn.BlockID AND
                                              dbo.PdmTabBlock.TabID = dbo.pdmTabGridMetaColumn.TabID
                        WHERE     (dbo.pdmBlockSubItem.ControlType = 6)
                        AND (dbo.PdmTabBlock.TabID = {0} )
                  AND (dbo.pdmTabGridMetaColumn.BlockID ={1})
				  and pdmTabGridMetaColumn.ExternalMappingName <> ''"                , gridTabId, currentGridBlockId);


            DataTable TabGridColumnResult = new DataTable();

            using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
            {
                conn.Open();

                TabGridColumnResult = DataAcessHelper.GetDataTableQueryResult(conn, query, null);
            }

            if (TabGridColumnResult.Rows.Count > 0)
            {
                toReturn = TabGridColumnResult.AsDataRowEnumerable().Select(o => o["ExternalMappingName"] as string).ToList();
            }

            return(toReturn);
        }
        internal static List <LookupItemDto> GetOneSystemDefinTableLookupItems(SqlConnection conn, string queryTAble)
        {
            List <LookupItemDto> listDto = new List <LookupItemDto> ();

            try
            {
                DataTable datatable = DataAcessHelper.GetDataTableQueryResult(conn, queryTAble);

                foreach (DataRow row in datatable.Rows)
                {
                    LookupItemDto itemDto = new LookupItemDto();
                    // itemDto.Id = (int)row["Id"];
                    itemDto.Id      = row["Id"];
                    itemDto.Display = row["Display"] as string;
                    listDto.Add(itemDto);
                }
            }
            catch (Exception ex)
            {
            }



            return(listDto);
        }
Beispiel #8
0
        //TODO list--------
        public static void GenerateUserDefinTableScript()
        {
            List <int> userDefineEntityIds = null;

            using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
            {
                conn.Open();

                int       userDefineEntityType  = (int)EmEntityType.UserDefineTable;
                string    qeuryUserDefineEntity = @" SELECT EntityID FROM pdmentity WHERE EntityType =  " + userDefineEntityType;
                DataTable resultTabel           = DataAcessHelper.GetDataTableQueryResult(conn, qeuryUserDefineEntity);
                userDefineEntityIds = resultTabel.GetDistinctOneColumnValueIds("EntityID").ToList();
            }


            // List<PdmEntityClrUserDefineDto> listPdmEntityClrDto = null;

            List <PdmClrEntitySimpleStructureDto> listPdmEntityClrDto = PLMSEntityClrBL.GetEntityAndColumnStrcutureInfoList(userDefineEntityIds);

            using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
            {
                conn.Open();



                string aEntiValueScript = string.Empty;
                foreach (PdmClrEntitySimpleStructureDto aEntityDTO in listPdmEntityClrDto)
                {
                    //  int entityId = aEntityDTO.EntityId;
                    //  string entityCode = ;
                    aEntityDTO.EntityCode = DataTableUtility.FilterSQLDBInvalidChar(aEntityDTO.EntityCode);

                    GenetateEntityDTOQuery(aEntityDTO, conn);

                    string deleteOldEntityTable = @"delete  [pdmDWTabGridScriptSetting] where EntityID=" + aEntityDTO.EntityId;


                    // string sqlSelect = aEntityDTO.SQLSelect;
                    string IntoUseDefineTablName = " insert   into " + aEntityDTO.DWUserDefineTableName + aEntityDTO.SQLSelect;


                    // save to dbms

                    string     deleteAndInsert = deleteOldEntityTable + System.Environment.NewLine + @" insert into " + PLMConstantString.PLM_DW_TabGridScripContainerTable + " ( EntityID,EntityName,InserIntoSQLScript,DWTableName,RootLevelSelectSQLScript)    values (@EntityID,@EntityName,@queryResult,@DWTableName,@SQLRootLevelSelect)";
                    SqlCommand cmd             = new  SqlCommand(deleteAndInsert, conn);
                    cmd.Parameters.Add(new SqlParameter("@EntityID", aEntityDTO.EntityId));
                    cmd.Parameters.Add(new SqlParameter("@EntityName", DataTableUtility.FilterSQLDBInvalidChar(aEntityDTO.EntityCode)));
                    cmd.Parameters.Add(new SqlParameter("@queryResult", IntoUseDefineTablName));
                    cmd.Parameters.Add(new SqlParameter("@DWTableName", aEntityDTO.DWUserDefineTableName));
                    cmd.Parameters.Add(new SqlParameter("@SQLRootLevelSelect", aEntityDTO.SQLSelect));
                    CLROutput.OutputDebug("aEntityDTO.entityCode" + aEntityDTO.EntityCode);
                    CLROutput.OutputDebug("aEntityDTO.SQLSelect" + aEntityDTO.SQLSelect);

                    cmd.ExecuteNonQuery();
                } // end of for eahc entity
            }
        }
Beispiel #9
0
        //        private static void InsertManyToManyRelationshipAndData(Dictionary<string, PdmEntityClrDto> dictAllImportEntity, string childEntityEntityName, string masterEntityTableName, int childEntityId, int masterEntityId, SqlConnection conn)
        //        {
        //            string queryRelationEntity = string.Format(@" select EntityID from  dbo.pdmentity where  ChildEntityID={0} and MasterEntityID={1}", childEntityId, masterEntityId);
        //            SqlCommand cmdGetRelationEntityId = new SqlCommand(queryRelationEntity, conn);
        //            object relationEntityId = cmdGetRelationEntityId.ExecuteScalar();

        //            if (relationEntityId != null)
        //            {

        //                string delete1 = @"delete  from  dbo.pdmEntityMasterChildValue  where RelationEntityID = " + relationEntityId;
        //                string delete2 = string.Format(@" delete  from  dbo.pdmentity where ChildEntityID={0} and MasterEntityID={1} and IsRelationEntity=1", childEntityId, masterEntityId);

        //                SqlCommand cmdDeleteRelationEntityId = new SqlCommand(delete1 + System.Environment.NewLine + delete2, conn);
        //                cmdDeleteRelationEntityId.ExecuteNonQuery();
        //            }
        //            string insertNewRaltionEntity = string.Format(@" insert into dbo.pdmentity
        //                    (
        //                        EntityCode,
        //                        IsImport,
        //                        EntityType,
        //
        //                        EntityWithFKEntityID,
        //                        MasterEntityColumnID ,
        //                        ChildEntityColumnID,
        //                        IsRelationEntity
        //                    )
        //                     values
        //                    (
        //                        '{0}',
        //                        {1}
        //                        {2},
        //                        {3},
        //                        {4},
        //                        {5}
        //                    )  ", "Import_Relation_" + dictAllImportEntity[masterEntityTableName].EntityCode + "_" + dictAllImportEntity[childEntityEntityName].EntityCode,
        //                  childEntityId,
        //                  masterEntityId
        //                  );

        //            SqlCommand cmdInsertNewEntity = new SqlCommand();

        //            CLROutput.Output("insertNewRaltionEntity=" + insertNewRaltionEntity);
        //            object entityId = InsertNewEntityWithReturnNewIdentityId(conn, insertNewRaltionEntity, cmdInsertNewEntity);
        //        }

        private static void UpdateModifyRecordFromERPToPLM(List <PdmEntityClrDto> plmImportEntityDtoList)
        {
            foreach (PdmEntityClrDto aPdmEntityClrDto in plmImportEntityDtoList)
            {
                string    exchangeTableName  = aPdmEntityClrDto.EntityCode;
                DataTable modifyRecordsTable = null;

                //Get Ex-change new Row Data
                using (SqlConnection conn = new SqlConnection(PLM_ExChangeDatabase_ConnectionString))
                {
                    conn.Open();

                    string queryERPNewRecord = string.Format(@" select * from  {0} where  {1}={2} ", aPdmEntityClrDto.EntityCode, PLMConstantString.ExchangeRowDataERPFlagColumn, (int)EmExChangeActionType.Modified);

                    modifyRecordsTable = DataAcessHelper.GetDataTableQueryResult(conn, queryERPNewRecord);
                    //  CLROutput.SendDataTable(newRecordsTable);
                }


                using (SqlConnection conn = new SqlConnection(PLMSDWStoredProcedures.PLM_APP_ConnectionString))
                {
                    conn.Open();

                    foreach (DataRow row in modifyRecordsTable.Rows)
                    {
                        int        rowId     = (int)row[PLMConstantString.ExchangeRowDataPLMPrimayKeyColumn];
                        SqlCommand cmdDelete = new SqlCommand();
                        cmdDelete.CommandText = @" delete pdmUserDefineEntityRowValue where RowID=" + rowId;
                        cmdDelete.Connection  = conn;
                        cmdDelete.ExecuteNonQuery();
                        InsertRowCellValue(row, conn, aPdmEntityClrDto, rowId);
                    }
                }

                using (SqlConnection conn = new SqlConnection(PLM_ExChangeDatabase_ConnectionString))
                {
                    conn.Open();


                    string updateExchangeStatus = string.Format(@" 
                                               update  {0} 
                                               set  {1}={2} ,
                                               {3}=CURRENT_TIMESTAMP 
                                               where {4}={5} ",
                                                                aPdmEntityClrDto.EntityCode,
                                                                PLMConstantString.ExchangeRowDataERPFlagColumn, (int)EmExChangeActionType.NoChange,
                                                                PLMConstantString.ExchangeRowDataPLMImportDateTimeColumn,
                                                                PLMConstantString.ExchangeRowDataERPFlagColumn, (int)EmExChangeActionType.Modified);

                    SqlCommand cmdUpdateExTableFromNewToNochnage = new SqlCommand(updateExchangeStatus, conn);
                    cmdUpdateExTableFromNewToNochnage.ExecuteNonQuery();
                }

                // Update Ex-change Tabl
            }
        }
Beispiel #10
0
        public static void GetRichText(int richTectFileId)
        {
            using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
            {
                conn.Open();
                DataAcessHelper.ExecuteReadUnCommmited(conn);

                string query = @"select  tblSketch.OriginalImage    from tblSketch  where SketchID =@richTectFileId";
                List <SqlParameter> listPara = new List <SqlParameter>();
                listPara.Add(new SqlParameter("@richTectFileId", richTectFileId));

                //command.Parameters.Add("@Photo",   SqlDbType.Image, photo.Length).Value = photo;

                DataTable result = DataAcessHelper.GetDataTableQueryResult(conn, query, listPara);

                if (result.Rows.Count > 0)
                {
                    byte[] rowData = result.Rows[0]["OriginalImage"] as byte[];

                    if (rowData != null)
                    {
                        string str = System.Text.Encoding.Default.GetString(rowData);
                        // CLROutput.OutputDebug(str);

                        CLROutput.OutputDebug("------------------");
                        //  string str2 = HtmlToPlainText.StripHTML(str);
                        DataTable datatable = new DataTable();

                        DataColumn column = new DataColumn("Text");
                        datatable.Columns.Add(column);

                        //column.DataType = typeof (String );
                        // column.MaxLength  = 200;



                        var row = datatable.NewRow();
                        row["Text"] = str;
                        datatable.Rows.Add(row);
                        CLROutput.SendDataTable(datatable);



                        //LROutput.OutputDebug(str2);
                    }
                }


                DataAcessHelper.ExecuteReadCommmited(conn);
            }

            //  GetGridValue(tabId, currentGridBlockId, referenceIds, false);
        }
        public static void  CreateDataBaseTableFromQuery(string query, SqlConnection conn, string datbaseTableName)
        {
            string sqlCrateTableStament = CreateSQlCreateTableStatmentFromQuery(query, conn, datbaseTableName);

            string dropTablecommand = DataAcessHelper.GetSQLDropTableCommand(datbaseTableName);

            sqlCrateTableStament = dropTablecommand + System.Environment.NewLine + sqlCrateTableStament;
            using (SqlCommand cmd = new SqlCommand(sqlCrateTableStament, conn))
            {
                cmd.ExecuteNonQuery();
            }
        }
		public static List< PdmEntityMasterChildValueClrDto> GetAllList(SqlConnection conn)
        {
            List<PdmEntityMasterChildValueClrDto> listDto = new List<PdmEntityMasterChildValueClrDto>();
           
                DataTable entityDataTable = DataAcessHelper.GetDataTableQueryResult(conn, QueryAll);
                foreach (DataRow row in entityDataTable.Rows)
                {
                    listDto.Add(PdmEntityMasterChildValueConverter.ConvertDataRowDto(row));
                }
           
      
            return listDto;
        }
Beispiel #13
0
        private static Dictionary <int, List <LookupItemDto> > GetSysDefineEntityDisplayInfoListNew(List <PdmEntityBlClrDto> sysDefineEntityList, SqlConnection conn)
        {
            Dictionary <int, List <LookupItemDto> > toReturn = new Dictionary <int, List <LookupItemDto> >();

            Dictionary <string, string> dictTableNameAndQuery = new Dictionary <string, string>();

            foreach (var aPdmEntityDto in sysDefineEntityList)
            {
                string systemQueryTable = GetSysDefineQueryIDAndDisplay(aPdmEntityDto);
                //  CLROutput.OutputDebug("aPdmEntityDto.SysTableName" + aPdmEntityDto.EntityId + "=" + aPdmEntityDto.EntityCode + aPdmEntityDto.SysTableName);
                if (!dictTableNameAndQuery.ContainsKey(aPdmEntityDto.SysTableName))
                {
                    dictTableNameAndQuery.Add(aPdmEntityDto.SysTableName, systemQueryTable);

                    CLROutput.OutputDebug("aPdmEntityDto.SysTableName" + aPdmEntityDto.EntityId + "=" + aPdmEntityDto.EntityCode + aPdmEntityDto.SysTableName + " systemQueryTable=" + systemQueryTable);
                }
            }

            // SqlContext.Pipe.Send("queryRowValue=" + allSystemQueryTable);
            DataSet result = DataAcessHelper.GetDataSetQueryResult(conn, dictTableNameAndQuery);

            //  var dictTableNameAndDto = sysDefineEntityList.ToDictionary(o => o.EntityId , o => o);

            foreach (var SysEntityDto in sysDefineEntityList)
            {
                string sysTableName = SysEntityDto.SysTableName;
                foreach (DataTable datatable in result.Tables)
                {
                    //  CLROutput.SendDataTable(datatable);

                    if (sysTableName == datatable.TableName)
                    {
                        List <LookupItemDto> listDto = new List <LookupItemDto>();
                        foreach (DataRow row in datatable.Rows)
                        {
                            LookupItemDto itemDto = new LookupItemDto();
                            itemDto.Id      = (int)row["Id"];
                            itemDto.Display = row["Display"] as string;
                            listDto.Add(itemDto);
                        }

                        toReturn.Add(SysEntityDto.EntityId, listDto);
                    }

                    //if(
                    // DataSetUtilities.SendDataTable(datatable);
                }
            }

            return(toReturn);
        }
Beispiel #14
0
        private static List <PdmTabBlockSubItemExtraInfoClrDto> GetAllList(SqlConnection conn)
        {
            List <PdmTabBlockSubItemExtraInfoClrDto> listDto = new List <PdmTabBlockSubItemExtraInfoClrDto>();

            DataTable entityDataTable = DataAcessHelper.GetDataTableQueryResult(conn, QueryAll);

            foreach (DataRow row in entityDataTable.Rows)
            {
                listDto.Add(PdmTabBlockSubItemExtraInfoConverter.ConvertDataRowDto(row));
            }


            return(listDto);
        }
Beispiel #15
0
        public static List <string> GetPrimarykeyList(SqlConnection conn, string databaseTableName)
        {
            string query = string.Format(@"  SELECT  column_name
            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
            WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
            AND table_name = '{0}' ", databaseTableName);

            //  SqlContext.Pipe.Send( query);
            var result = DataAcessHelper.GetDataTableQueryResult(conn, query);

            //CLROutput.SendDataTable(result);

            return(result.AsDataRowEnumerable().Select(row => row["column_name"].ToString()).ToList());
        }
        public static List <PdmTemplateTabLibReferenceSettingClrDto> GetAllList(SqlConnection conn)
        {
            List <PdmTemplateTabLibReferenceSettingClrDto> listDto = new List <PdmTemplateTabLibReferenceSettingClrDto>();

            DataTable entityDataTable = DataAcessHelper.GetDataTableQueryResult(conn, QueryAll);

            foreach (DataRow row in entityDataTable.Rows)
            {
                listDto.Add(PdmTemplateTabLibReferenceSettingConverter.ConvertDataRowDto(row));
            }


            return(listDto);
        }
Beispiel #17
0
        public static List <PdmTabGridMetaColumnClrDto> GetAllList(SqlConnection conn)
        {
            List <PdmTabGridMetaColumnClrDto> listDto = new List <PdmTabGridMetaColumnClrDto>();

            DataTable entityDataTable = DataAcessHelper.GetDataTableQueryResult(conn, QueryAll);

            foreach (DataRow row in entityDataTable.Rows)
            {
                listDto.Add(PdmTabGridMetaColumnConverter.ConvertDataRowDto(row));
            }


            return(listDto);
        }
        private static DataTable GetMutipleBlockGridCellValueTable(int[] referenceIds, int[] blockIds, int[] columnIds)
        {
            var andblockIdclause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(blockIds, GridColumnConstantName.BlockID, false);;

            string referenceInClause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(referenceIds, GridColumnConstantName.ProductReferenceID, true);

            // string referenceInClause = string.Empty;

            var columnIdInclause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(columnIds, GridColumnConstantName.GridColumnID, true);

            StringBuilder aQuery = new StringBuilder();

            //   aQuery.Append( string" SELECT ProductReferenceID,BlockID, GridID,  RowID,  RowValueGUID ,Sort,");

            aQuery.Append(" SELECT " +
                          GridColumnConstantName.ProductReferenceID + ", " +
                          GridColumnConstantName.BlockID + ", " +
                          //  GridColumnConstantName.RowID + ", " +
                          GridColumnConstantName.Sort + ", " +
                          GridColumnConstantName.GridColumnID + ", " +
                          GridColumnConstantName.RowValueGUID + ", " +
                          GridColumnConstantName.ValueText
                          );

            aQuery.Append(" from pdmSearchComplexColumnValue where " + andblockIdclause + referenceInClause + columnIdInclause);

            CLROutput.OutputDebug(aQuery.ToString());

            string queryCell = aQuery.ToString();

            CLROutput.OutputDebug("GetMutipleBlockGridCellValueTable=" + queryCell);

            // test 4 Grid for all cell values forthe perforamce
            // queryCell = @" SELECT ProductReferenceID, BlockID, RowID, Sort, GridColumnID, ValueText from pdmSearchComplexColumnValue where    BlockID in ( 20, 14 ,64,65) ";

            using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
            {
                conn.Open();
                DataAcessHelper.ExecuteReadUnCommmited(conn);

                var dtReferenceGridValue = DataAcessHelper.GetDataTableQueryResult(conn, queryCell);

                DataAcessHelper.ExecuteReadCommmited(conn);

                return(dtReferenceGridValue);

                //CLROutput.SendDataTable(dtReferenceGridValue);
            }
        }
Beispiel #19
0
        public static List <PdmEntityColumnClrUserDefineDto> GetErpExchangeDatabaseTableColumnDto(SqlConnection conn, string databaseTableName)
        {
            string query = string.Format(@"SELECT  Columns.COLUMN_NAME as ColumnName,Columns.DATA_TYPE as DataType,
             Columns.CHARACTER_MAXIMUM_LENGTH as MaxLength, Columns.DATETIME_PRECISION as Precision,  Columns.NUMERIC_SCALE as Scale,
            cast( (case when  PK.COLUMN_NAME Is not null then 1 else 0 end) as bit) as PrimaryKey

            FROM information_schema.columns as Columns
            left join
            (
               SELECT column_name
            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
            WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
            AND table_name = '{0}'
            ) as PK on Pk.COLUMN_NAME =  Columns.COLUMN_NAME
            WHERE table_name = '{0}'  and
             ( Columns.DATA_TYPE <> 'timestamp'  and  Columns.DATA_TYPE <>'image'
                and   Columns.DATA_TYPE <>'xml'
                      and   Columns.DATA_TYPE <>'varbinary'
             )
           and  Columns.COLUMN_NAME  NOT IN ('{1}') ",
                                         databaseTableName,

                                         PLMConstantString.ExchangeRowDataChangeTimeStamp

                                         );

            List <PdmEntityColumnClrUserDefineDto> toreturnList = new List <PdmEntityColumnClrUserDefineDto>();

            // PLMSDataImport.ExchangeRowDataPLMImportDateTimeColumn
            // CLROutput.Output(query);
            var result = DataAcessHelper.GetDataTableQueryResult(conn, query);

            //  CLROutput.SendDataTable(result);

            foreach (DataRow arow in result.Rows)
            {
                PdmEntityColumnClrUserDefineDto fromDataBaseDto = PLMDataRowConverter.ConvertErpExchangeDatabaseTableColumnDataRowToPdmEntityColumnDto(arow);

                toreturnList.Add(fromDataBaseDto);
            }

            var textboxColumn = toreturnList.Where(o => o.UicontrolType == (int)EmControlType.TextBox).FirstOrDefault();

            if (textboxColumn != null)
            {
                textboxColumn.UsedByDropDownList = true;
            }
            return(toreturnList);
        }
Beispiel #20
0
        private static void TestDatatableScheme(SqlConnection conn)
        {
            string aQuery = "select TabID,TabName,FolderID from pdmtab ";

            DataTable result = DataAcessHelper.GetDataTableQueryResult(conn, aQuery);
            Dictionary <string, string> dictDataTableAndDatabaseTableMappingColumn = new Dictionary <string, string>();

            dictDataTableAndDatabaseTableMappingColumn.Add("TabID", "TabID");
            dictDataTableAndDatabaseTableMappingColumn.Add("TabName", "TabName");
            dictDataTableAndDatabaseTableMappingColumn.Add("FolderID", "FolderID");

            aQuery = "select * from pdmgrid ";

            DataTableUtility.CreateDataBaseTableFromQuery(aQuery, conn, "testpdmgrid");
        }
Beispiel #21
0
        // Key: InternalCode, Value: subitemID
        public static Dictionary <string, int> GetDictInteralCodeSubItemId(SqlConnection conn, string blockInterCode)
        {
            string queryString = @" SELECT     pdmBlockSubItem.SubItemID, pdmBlockSubItem.InternalCode 
                      FROM         pdmBlockSubItem INNER JOIN
                      pdmBlock ON pdmBlockSubItem.BlockID = pdmBlock.BlockID 
                      where  pdmBlock.InternalCode =@blockInterCode  and pdmBlockSubItem.BlockID <3000";

            SqlParameter parablockInterCode = new SqlParameter("@blockInterCode", blockInterCode);

            SqlCommand cmd = new SqlCommand(queryString, conn);

            cmd.Parameters.Add(parablockInterCode);

            return(DataAcessHelper.GetDataTableQueryResult(cmd).AsDataRowEnumerable()
                   .ToDictionary(row => row["InternalCode"].ToString(), row => (int)row["SubItemID"]));
        }
        private static int?GetReferencePomOfUnitMeasure(int referenceId)
        {
            string query = @"select PomUnitOfMeasure from pdmProduct where ProductReferenceID =@ProductReferenceID";


            List <SqlParameter> paraList = new List <SqlParameter> ();

            paraList.Add(new SqlParameter("@ProductReferenceID", referenceId));


            using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
            {
                //SqlContext.
                conn.Open();
                object value = DataAcessHelper.RetriveSigleValue(conn, query, paraList);

                return(ControlTypeValueConverter.ConvertValueToInt(value));
            }
        }
Beispiel #23
0
        public static List <PdmClrEntitySimpleStructureDto> GetEntityAndColumnStrcutureInfoList(List <int> entityIDs)
        {
            List <PdmClrEntitySimpleStructureDto> listPdmEntityDto = new List <PdmClrEntitySimpleStructureDto>();

            if (entityIDs != null && entityIDs.Count > 0)
            {
                string entityQuery      = "select distinct EntityID ,EntityCode ,EntityType,SysTableName  from pdmEntity ";
                string entityIdInClause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(entityIDs, "EntityID", false);

                entityQuery = entityQuery + "   where " + entityIdInClause;

                using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
                {
                    conn.Open();

                    DataTable entityDataTable = DataAcessHelper.GetDataTableQueryResult(conn, entityQuery);

                    foreach (DataRow row in entityDataTable.Rows)
                    {
                        listPdmEntityDto.Add(PLMDataRowConverter.ConvertRowToPdmEntityDto(row));
                    }

                    string queryColumn = @" select UserDefineEntityColumnID, EntityId, ColumnName,DataType,UsedByDropDownList,DataRowSort,IsPrimaryKey,IsIdentity,SystemTableColumnName,UicontrolType,Nbdecimal,FkentityId

                                          from PdmUserDefineEntityColumn " + "   where " + entityIdInClause;

                    DataTable columnDataTable = DataAcessHelper.GetDataTableQueryResult(conn, queryColumn);

                    List <PdmEntityColumnClrUserDefineDto> allColumnDto = new List <PdmEntityColumnClrUserDefineDto>();

                    foreach (DataRow row in columnDataTable.Rows)
                    {
                        allColumnDto.Add(PLMDataRowConverter.ConvertUserDefineEntityColumnDataRowToPdmEntityColumnDto(row));
                    }
                    foreach (var entityDto in listPdmEntityDto)
                    {
                        entityDto.Columns = allColumnDto.Where(o => o.EntityId == entityDto.EntityId).ToList();
                    }
                }
            }

            return(listPdmEntityDto);
        }
        // Big size
        private static DataTable GetSizeRotateTableWithSizerunId(int sizerunValueId)
        {
            var sizeEntity = PdmCacheManager.DictCodeKeyPdmEntityBlCache[EmEntityCode.SizeRunDetail.ToString()];

            string connectionInfo = PLMSEntityClrBL.GetConnectionInfoWithCode(sizeEntity.DataSourceFrom);

            string querySizeRotate = @" select SizeRunRotateID ,SizeOrder  ,SizeName  from tblSizeRunRotate where SizeRunId = @sizeRunId order by SizeOrder ";

            // need to filter
            using (SqlConnection conn = new SqlConnection(connectionInfo))
            {
                //SqlContext.
                conn.Open();
                SqlCommand   cmdSize = new SqlCommand(querySizeRotate, conn);
                SqlParameter paraSizeRunrunIdValueId = new SqlParameter("@sizeRunId", sizerunValueId);
                cmdSize.Parameters.Add(paraSizeRunrunIdValueId);
                DataTable sizeRotateTable = DataAcessHelper.GetDataTableQueryResult(cmdSize);
                return(sizeRotateTable);
            }
        }
Beispiel #25
0
        public static void TestExchangeDBConnection()
        {
            //step1: Get all Ex-change Tables from ex-Chang Dababae
            List <string> exChangeDbTableList = null;
            Dictionary <string, List <PdmEntityColumnClrDto> > dictExchangeTableColumnDto = new Dictionary <string, List <PdmEntityColumnClrDto> >();

            exChangeDbTableList = GetExistingExDatabaseTableNameAndStructure(exChangeDbTableList, dictExchangeTableColumnDto);

            //Step2: insert new Ex-change table
            AddNewExchangeTableToPLMSUserDefineEntity(exChangeDbTableList, dictExchangeTableColumnDto);

            // step2-2 update strcutre


            // Get all Refresh Userdefinetable from PLMS

            string queryllEntityImport = @"  select  EntityID from pdmEntity where IsImport =1 and EntityID > 3000 ";

            List <PdmEntityClrDto> plmImportEntityDtoList = null;

            using (SqlConnection conn = new SqlConnection(PLMSDWStoredProcedures.PLM_APP_ConnectionString))
            {
                List <int> enityIds = DataAcessHelper.GetDataTableQueryResult(conn, queryllEntityImport).AsEnumerable().Select(o => (int)o["EntityID"]).ToList();;

                plmImportEntityDtoList = PLMSEntityClrBL.GetEntityAndColumnStrcutureInfoList(enityIds, conn);
            }



            //Step3 Transfer NewRecord FromERP To PLM
            TransferNewRecordFromERPToPLM(plmImportEntityDtoList);

            ////Step4; upadte modfied fileds
            UpdateModifyRecordFromERPToPLM(plmImportEntityDtoList);

            //ste5: Delete--toDO

            //Step 6: update FK relation Ship
            UpdateForeignKeyRelationFromERPToPLM(plmImportEntityDtoList);
        }
Beispiel #26
0
        private static Dictionary <int, List <LookupItemDto> > GetEnumEntityDisplayInfoList(List <int> entityIDs, SqlConnection conn)
        {
            Dictionary <int, List <LookupItemDto> > toReturn = new Dictionary <int, List <LookupItemDto> >();

            string enumquery = "  select distinct  EntityID ,EnumKey ,EnumValue  from PdmEntityEnumValue  ";

            if (entityIDs != null && entityIDs.Count > 0)
            {
                enumquery = enumquery + "   where " + DataAcessHelper.GenerateColumnInClauseWithAndCondition(entityIDs, "EntityID", false);
            }

            //  SqlContext.Pipe.Send("enumquery=" + enumquery);

            DataTable dataTable = DataAcessHelper.GetDataTableQueryResult(conn, enumquery);

            //  var result = dataTable.

            var subItemQuery = from row in dataTable.AsDataRowEnumerable()
                               group row by new
            {
                EntityID = (int)row["EntityID"]
            } into grp

                select new
            {
                EntityID = grp.Key.EntityID,
                EnumKeyIDAndEnumValueList = grp.Select(r => new LookupItemDto {
                    Id = (int)r["EnumKey"], Display = r["EnumValue"] as string
                }).ToList(),
            };

            foreach (var o in subItemQuery)
            {
                toReturn.Add(o.EntityID, o.EnumKeyIDAndEnumValueList);
            }

            return(toReturn);
        }
Beispiel #27
0
        private static void AddNewExchangeTableToPLMSUserDefineEntity(List <string> exChangeDbTableList, Dictionary <string, List <PdmEntityColumnClrDto> > dictExchangeTableColumnDto)
        {
            using (SqlConnection conn = new SqlConnection(PLMSDWStoredProcedures.PLM_APP_ConnectionString))
            {
                conn.Open();

                string     queryGetImportFolderId = " SELECT SetupValue FROM pdmsetup WHERE setupCode='ImportEntityFolder'";
                SqlCommand cmdGetFodlerId         = new SqlCommand(queryGetImportFolderId, conn);
                string     folderId = cmdGetFodlerId.ExecuteScalar().ToString();

                string     querExistingImportTableInPLm = @" select EntityCode  from pdmentity where  IsImport =1  and EntityID > 3000    and EntityCode like 'EX_%'   ";
                SqlCommand cmdGetExistingTable          = new SqlCommand(querExistingImportTableInPLm, conn);
                // var existingEntityCode = DataAcessHelper.GetDataTableQueryResult(conn, querExistingImportTableInPLm).AsEnumerable().Select(o => o["EntityCode"]);
                List <string> existingEntityList = DataAcessHelper.GetDataTableQueryResult(conn, querExistingImportTableInPLm).AsEnumerable().Select(o => o.Field <string>("EntityCode").Trim().ToLowerInvariant()).ToList();

                //  existingEntityList.ForEach(o => CLROutput.Output("PLmexistingEntityList=" + o));

                //exChangeDbTableList.ForEach(o => CLROutput.Output("exChangeDbTableList=" + o));


                var newEntityList = exChangeDbTableList.Except(existingEntityList);

                foreach (string newEntity in newEntityList)
                {
                    object newEntityID = InsertNewExportUserDefineEntity(folderId, conn, newEntity);
                    // need to insert table as well

                    List <PdmEntityColumnClrDto> columnDtoList = dictExchangeTableColumnDto[newEntity];

                    foreach (PdmEntityColumnClrDto pdmEntityColumnClrDto in columnDtoList)
                    {
                        pdmEntityColumnClrDto.EntityId = (int)newEntityID;
                        InsertPdmUserDefineColunmDto(pdmEntityColumnClrDto, conn);
                    }
                }
            }
        }
Beispiel #28
0
        public static DataTable GetMutipleSysDefineEntityRowValue(int entityID, List <string> systemColumNames)
        {
            string systemDefineColumnName = string.Empty;

            foreach (var columnName in systemColumNames)
            {
                if (columnName != string.Empty)
                {
                    systemDefineColumnName = systemDefineColumnName + columnName + ",";
                }
            }

            if (systemDefineColumnName != string.Empty)
            {
                systemDefineColumnName = systemDefineColumnName.Substring(0, systemDefineColumnName.Length - 1);

                // var pdmSystemEntity = GetBLEntityStructureById(entityID);

                var    pdmSystemEntity = PdmCacheManager.DictPdmEntityBlEntity[entityID];
                string connectionInfo  = GetConnectionInfoWithCode(pdmSystemEntity.DataSourceFrom);

                //  string idInclause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(keyValues, pdmSystemEntity.SystemDefinePrimaryKeyColumnName.SystemTableColumnName, false);
                string aQuery = "select distinct " + pdmSystemEntity.SystemDefinePrimaryKeyColumnName.SystemTableColumnName + " as Id, " + systemDefineColumnName
                                + " from " + pdmSystemEntity.SysTableName; ///+ "  where " + idInclause;

                // need to filter
                using (SqlConnection conn = new SqlConnection(connectionInfo))
                {
                    //SqlContext.
                    conn.Open();
                    return(DataAcessHelper.GetDataTableQueryResult(conn, aQuery));
                }
            }

            return(new DataTable());
        }
Beispiel #29
0
        public static Dictionary <int, List <SimpleUserDefineEntityRow> > GetDictEntityUserDefineRows(List <int> entityIDs, List <int> ddlUserDefineEntityColumnIDs)
        {
            Dictionary <int, List <SimpleUserDefineEntityRow> > toReturn = new Dictionary <int, List <SimpleUserDefineEntityRow> >();

            entityIDs = entityIDs.Distinct().ToList();



            string queryRowValue = pdmUserDefineMutipleColumnEntityRowValue;

            if (entityIDs != null && entityIDs.Count > 0)
            {
                //AND (EntityID IN (3031, 3095))
                queryRowValue = queryRowValue + "   where " + DataAcessHelper.GenerateColumnInClauseWithAndCondition(entityIDs, "EntityID", false);
            }

            if (ddlUserDefineEntityColumnIDs != null && ddlUserDefineEntityColumnIDs.Count > 0)
            {
                //and  UserDefineEntityColumnID in                       ( 442,444)
                queryRowValue = queryRowValue + DataAcessHelper.GenerateColumnInClauseWithAndCondition(ddlUserDefineEntityColumnIDs, "UserDefineEntityColumnID", true);
            }

            DataTable RowValuDatatable;

            using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
            {
                conn.Open();
                RowValuDatatable = DataAcessHelper.GetDataTableQueryResult(conn, queryRowValue);
            }

            CLROutput.SendDebugDataTable(RowValuDatatable);


            //  DataSetUtilities.SendDataTable(RowValuDatatable);

            // Entity level group
            var groupByEntityIdQuery = from row in RowValuDatatable.AsDataRowEnumerable()
                                       group row by new
            {
                EntityID = (int)row["EntityID"]
            } into grp

                select new
            {
                EntityID          = grp.Key.EntityID,
                RowIDAndValueList = grp.Select(r => new
                {
                    RowID     = (int)r["RowID"],
                    SortOrder = (r["SortOrder"] as int?).HasValue ? (r["SortOrder"] as int?).Value : 0,
                    UserDefineEntityColumnID = (int)r["UserDefineEntityColumnID"],
                    ValueText = r["ValueText"] as string
                }).ToList(),
            };

            foreach (var o in groupByEntityIdQuery)
            {
                int entityId          = o.EntityID;
                var RowIDAndValueList = o.RowIDAndValueList;

                var simpleUserDefineEntityRowList = from row in RowIDAndValueList
                                                    group row by new
                {
                    RowID     = row.RowID,
                    SortOrder = row.SortOrder
                } into grpRow

                    select new
                {
                    RowId            = grpRow.Key.RowID,
                    SortOrder        = grpRow.Key.SortOrder,
                    RowCellValueList = grpRow.Select
                                       (
                        row => new
                    {
                        ColumnId  = row.UserDefineEntityColumnID,
                        ValueText = row.ValueText
                    }

                                       )
                };

                List <SimpleUserDefineEntityRow> listDto = new List <SimpleUserDefineEntityRow>();
                foreach (var queryItem in simpleUserDefineEntityRowList)
                {
                    SimpleUserDefineEntityRow aSimpleUserDefineEntityRow = new SimpleUserDefineEntityRow();
                    aSimpleUserDefineEntityRow.RowId     = queryItem.RowId;
                    aSimpleUserDefineEntityRow.SortOrder = queryItem.SortOrder;
                    foreach (var column in queryItem.RowCellValueList)
                    {
                        int columnId = column.ColumnId;
                        //int controlType = dictColumnIdControType[columnId];
                        //object value= ControlTypeValueConverter.ConvertValueToObject(column.ValueText, controlType);
                        aSimpleUserDefineEntityRow.Add(columnId, column.ValueText);
                    }

                    listDto.Add(aSimpleUserDefineEntityRow);
                }

                toReturn.Add(o.EntityID, listDto);
            }

            return(toReturn);
        }
Beispiel #30
0
        //TODO List !!!! CLR link to ERP data source direcly !

        public static Dictionary <int, List <LookupItemDto> > GetUserDefineEntityDisplayInfoList(List <PdmEntityBlClrDto> userDefineEntityList, SqlConnection plmConnection)
        {
            Dictionary <int, List <LookupItemDto> > toReturn = new Dictionary <int, List <LookupItemDto> >();

            var entityIDs = userDefineEntityList.Select(o => o.EntityId).Distinct().ToList();

            List <int> ddlUserDefineEntityColumnIDs = new List <int>();

            foreach (var enityDto in userDefineEntityList)
            {
                ddlUserDefineEntityColumnIDs.AddRange
                (
                    enityDto.PdmUserDefineEntityColumnList.Where(o => o.UsedByDropDownList.HasValue && o.UsedByDropDownList.Value).Select(o => o.UserDefineEntityColumnId).ToList()
                );
            }

            string queryRowValue = pdmUserDefineMutipleColumnEntityRowValue;

            if (entityIDs != null && entityIDs.Count > 0)
            {
                //AND (EntityID IN (3031, 3095))
                queryRowValue = queryRowValue + "   where " + DataAcessHelper.GenerateColumnInClauseWithAndCondition(entityIDs, "EntityID", false);
            }

            if (ddlUserDefineEntityColumnIDs != null && ddlUserDefineEntityColumnIDs.Count > 0)
            {
                //and  UserDefineEntityColumnID in                       ( 442,444)
                queryRowValue = queryRowValue + DataAcessHelper.GenerateColumnInClauseWithAndCondition(ddlUserDefineEntityColumnIDs, "UserDefineEntityColumnID", true);
            }

            //   SqlContext.Pipe.Send("queryRowValue=" + queryRowValue);

            DataTable RowValuDatatable = DataAcessHelper.GetDataTableQueryResult(plmConnection, queryRowValue);



            var subItemQuery = from row in RowValuDatatable.AsDataRowEnumerable()
                               group row by new
            {
                EntityID = (int)row["EntityID"]
            } into grp

                select new
            {
                EntityID          = grp.Key.EntityID,
                RowIDAndValueList = grp.Select(r => new
                {
                    RowID     = (int)r["RowID"],
                    ValueText = r["ValueText"] as string
                }).ToList(),
            };

            foreach (var o in subItemQuery)
            {
                int entityId          = o.EntityID;
                var RowIDAndValueList = o.RowIDAndValueList;

                var lookupItemDtoList = from row in RowIDAndValueList
                                        group row by new
                {
                    RowID = row.RowID
                } into grp

                    select new LookupItemDto()
                {
                    Id      = grp.Key.RowID,
                    Display = grp.Select(r => r.ValueText as string).ToList().Aggregate((current, next) => current + "|" + next)
                };

                List <LookupItemDto> listDto = new List <LookupItemDto>();
                foreach (var itme in lookupItemDtoList)
                {
                    listDto.Add(itme);
                }

                toReturn.Add(o.EntityID, listDto);
            }

            return(toReturn);
        }