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);
            }
        }
Exemple #2
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);
        }
Exemple #3
0
        public static void SplitStringToMutipleColumn([SqlFacet(MaxSize = -1)] string referenceIds, int numberOfColumn)
        {
            DataTable datatable = new DataTable();

            for (int i = 1; i <= numberOfColumn; i++)
            {
                datatable.Columns.Add("C" + i.ToString());
            }

            string [] referenceidList = referenceIds.Split(new char[] { ',' });

            DataRow row         = datatable.NewRow();
            int     columncount = 0;

            for (int i = 0; i < referenceidList.Length; i++)
            {
                int remain = i % numberOfColumn;
                if (remain == 0)
                {
                    row = datatable.NewRow();
                    datatable.Rows.Add(row);
                    columncount      = 0;
                    row[columncount] = referenceidList[i];
                }
                else
                {
                    columncount++;
                    row[columncount] = referenceidList[i];
                }
            }


            CLROutput.SendDataTable(datatable);
        }
Exemple #4
0
        private static void ProcessUserDefineEntityColumn(PdmGridClrDto aPdmGridEntity, DataTable toReturnDcuTable, List <SimpleUserDefineEntityRow> listRow, int masterColumnId)
        {
            Dictionary <int, SimpleUserDefineEntityRow> dictRow = listRow.ToDictionary(o => o.RowId, o => o);

            foreach (DataRow dataRow in toReturnDcuTable.Rows)
            {
                string valueId = dataRow[masterColumnId.ToString()] as string;

                CLROutput.OutputDebug("masterColumnId value" + valueId);
                if (!string.IsNullOrEmpty(valueId))
                {
                    // int.Parse (
                    int intValueId = int.Parse(valueId);
                    if (dictRow.ContainsKey(intValueId))
                    {
                        var dtRow = dictRow[intValueId];
                        foreach (var dependentEntityColumn in aPdmGridEntity.MasterEntityDepdentColumn[masterColumnId])
                        {
                            int userDefineColumnId = int.Parse(dependentEntityColumn.InternalCode);

                            //  CLROutput.Output("userDefineColumnId" + userDefineColumnId);

                            object value = ControlTypeValueConverter.ConvertValueToObject(dtRow[userDefineColumnId], dependentEntityColumn.ColumnTypeId);
                            if (value != null)
                            {
                                dataRow[dependentEntityColumn.GridColumnId.ToString()] = value;
                            }
                        }
                    }
                }
            }
        }
        //dataBaseTableName must exsits first
        public static void BulkCopyDatatableToDatabaseTable(SqlConnection connection, DataTable sourceDataTable, string destinationDataBaseTableName, Dictionary <string, string> dictDataTableAndDatabaseTableMappingColumn)
        {
            try
            {
                SqlBulkCopy bulkCopy =
                    new SqlBulkCopy
                    (
                        connection,
                        SqlBulkCopyOptions.TableLock |
                        SqlBulkCopyOptions.FireTriggers |
                        SqlBulkCopyOptions.UseInternalTransaction,
                        null
                    );

                if (dictDataTableAndDatabaseTableMappingColumn != null && dictDataTableAndDatabaseTableMappingColumn.Count > 0)
                {
                    foreach (var pair in dictDataTableAndDatabaseTableMappingColumn)
                    {
                        bulkCopy.ColumnMappings.Add(pair.Key, pair.Value);
                    }
                }

                // bulkCopy.BatchSize = 50;
                bulkCopy.DestinationTableName = destinationDataBaseTableName;
                bulkCopy.WriteToServer(sourceDataTable);
            }
            catch (Exception ex)
            {
                CLROutput.OutputDebug("connection" + connection.Database);
                CLROutput.OutputDebug("Exception" + ex.ToString());
            }
        }
        private static void ProcessSingleTab(SqlInt32 aTabID, SqlConnection conn, string refids)
        {
            string tabQuery = @"select top 1 RootlevelselectSQLscript  from PLM_Dw_tabgridscript where tabID=" + aTabID + " and gridid is null";

            SqlCommand cmdQueryTabNameAndSelectRootlevel = new SqlCommand(tabQuery, conn);
            string     sQLTabLevelSelect = cmdQueryTabNameAndSelectRootlevel.ExecuteScalar() as string;

            CLROutput.OutputDebug("tabQuery=" + tabQuery, conn);
            CLROutput.OutputDebug("sQLTabLevelSelect=" + sQLTabLevelSelect, conn);



            string tabTablename = PLMSDWStoredProcedures.TabTableName[aTabID.Value];



            if (tabTablename != string.Empty)
            {
                string deleteOLDValue = string.Format(" delete {0} WHERE TabID={1} and ProductReferenceID in ({2}) ", tabTablename, aTabID, refids);

                SqlCommand deletcmd = new SqlCommand(deleteOLDValue, conn);


                CLROutput.OutputDebug("before deleteOLDValue=" + deleteOLDValue, conn);



                try
                {
                    deletcmd.ExecuteNonQuery();

                    CLROutput.OutputDebug("after delete ok deleteOLDValue=" + deleteOLDValue, conn);
                }
                catch (Exception ex)
                {
                    CLROutput.OutputDebug("delete failed deleteOLDValue=" + deleteOLDValue + ex.ToString(), conn);
                }

                string addReplaceAndRef = string.Format(" where  ProductReferenceID in ({0}) and  ", refids);

                string inserIntoSelect = " insert  into " + tabTablename + "  " + sQLTabLevelSelect.Replace("WHERE", addReplaceAndRef);;

                CLROutput.OutputDebug("before inserIntoSelect=" + inserIntoSelect, conn);



                try
                {
                    SqlCommand insertcmd = new SqlCommand(inserIntoSelect, conn);
                    insertcmd.ExecuteNonQuery();


                    CLROutput.OutputDebug("after insert ok   inserIntoSelect=" + inserIntoSelect, conn);
                }
                catch (Exception ex)
                {
                    CLROutput.OutputDebug("exception insertcmd failed=" + inserIntoSelect + ex.ToString(), conn);
                }
            }
        }
        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);
            }
        }
Exemple #8
0
        private static object InsertNewExportUserDefineEntity(string folderId, SqlConnection conn, string newEntityCode)
        {
            string sqlInsertPdmEntity = string.Format(@" 

                         Insert into    dbo.PdmEntity
                                  (
                                        EntityCode,
                                        FolderID,
                                        IsImport
                                   )  " +
                                                      @" values 
                                (
                                 '{0}',{1},{2}
 
                                )
                                    ", newEntityCode, folderId, 1);

            using (SqlCommand insertNewentity = new SqlCommand(sqlInsertPdmEntity, conn))
            {
                CLROutput.Output("sqlInsertPdmEntity" + sqlInsertPdmEntity);
                insertNewentity.ExecuteNonQuery();
            }

            string newEntityIdQuery = @"select EntityID  from pdmEntity  where entitycode='" + newEntityCode + "' and EntityID > 3000 ";

            using (SqlCommand cmdGetNewEntityId = new SqlCommand(newEntityIdQuery, conn))
            {
                return(cmdGetNewEntityId.ExecuteScalar());;
            }
        }
Exemple #9
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
            }
        }
Exemple #10
0
        private static void ScanSessionTimer_Elapsed(object sender, ElapsedEventArgs e)
        {
            CLROutput.OutputDebug("ScanSessionTimer_Elapsed??");

            foreach (var sysDefineEntity in SystemDefineEntityList)
            {
                UpdateSystemDefineCache(sysDefineEntity);
            }
            UpdateUserDefineCache();
        }
        public static void SetupAllLookItemCache()
        {
            var        allCacheEntity = PdmCacheManager.DictPdmEntityBlEntity;
            List <int> entityIDs      = allCacheEntity.Keys.ToList();
            Dictionary <int, List <LookupItemDto> > dictEntityLookupItemDto = GetDictEntityLookItemDtoFromDataBase(entityIDs);

            AddEntityToSystemCache(dictEntityLookupItemDto);

            CLROutput.OutputDebug("done: add cache with last scan timestamp");
        }
        public static string GenerateSQlCreateStatmentFromSchmeTable(string tableName, DataTable schema, int[] primaryKeys)
        {
            string sql = "CREATE TABLE [" + tableName + "] (\n";

            // columns
            foreach (DataRow column in schema.Rows)
            {
                bool?isHidden = column["IsHidden"] as bool?;
                if (!(isHidden.HasValue && isHidden.Value))
                {
                    sql += "\t[" + column["ColumnName"].ToString() + "] " + SQLGetType(column);

                    bool?isAllowDBNull = column["AllowDBNull"] as bool?;
                    if (!(isAllowDBNull.HasValue && isAllowDBNull.Value))
                    {
                        sql += " NOT NULL";
                    }



                    sql += ",\n";
                }
            }
            sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n";

            // primary keys
            string pk      = ", CONSTRAINT PK_" + tableName + " PRIMARY KEY CLUSTERED (";
            bool   hasKeys = (primaryKeys != null && primaryKeys.Length > 0);

            if (hasKeys)
            {
                // user defined keys
                foreach (int key in primaryKeys)
                {
                    pk += schema.Rows[key]["ColumnName"].ToString() + ", ";
                }
            }
            else
            {
                // check schema for keys
                string keys = string.Join(", ", GetPrimaryKeys(schema));
                pk     += keys;
                hasKeys = keys.Length > 0;
            }
            pk = pk.TrimEnd(new char[] { ',', ' ', '\n' }) + ")\n";
            if (hasKeys)
            {
                sql += pk;
            }

            sql += ")";

            CLROutput.OutputDebug("SQl" + sql);
            return(sql);
        }
Exemple #13
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);
        }
Exemple #14
0
        private static void UpdateSystemDefineCache(PdmEntityBlClrDto entity)
        {
            int entityId = entity.EntityId;

            CLROutput.OutputDebug("UpdateSystemDefineCache start...  ConnectInfo:" + entity.ConnectInfo);

            // CLROutput.OutputDebug("UpdateSystemDefineCache start...");

            if (!string.IsNullOrEmpty(entity.ConnectInfo))
            {
                //try
                //{
                using (SqlConnection conn = new SqlConnection(entity.ConnectInfo))
                {
                    conn.Open();

                    string lastChange = PdmCacheEntityLookupItem.GetLastChangeCheckSum(conn, entity.SysTableName);

                    //  CLROutput.InsertException(conn, "entity.LastScanCheckSum: " + entity.LastScanCheckSum + "lastChange :" + lastChange);

                    CLROutput.OutputDebug("entity.LastScanCheckSum: " + entity.LastScanCheckSum + "lastChange :" + lastChange);

                    if (lastChange != entity.LastScanCheckSum)
                    {
                        if (DictEntityLookupCache.ContainsKey(entityId))
                        {
                            CLROutput.OutputDebug(" lastChange sysdefine before DictEntityLookupCache " + "__" + entity.EntityCode + "__" + entity.LastScanCheckSum, conn);
                            List <LookupItemDto>        newLookupitem = PdmCacheEntityLookupItem.GetOneSystemDefinTableLookupItems(conn, PLMSEntityClrBL.GetSysDefineQueryIDAndDisplay(entity));
                            Dictionary <object, string> result        = PdmCacheEntityLookupItem.ConvertLookItemToDictionaryItem(newLookupitem);

                            DictEntityLookupCache.Remove(entityId);
                            DictEntityLookupCache.Add(entityId, result);

                            entity.LastScanCheckSum = lastChange;
                            // CLROutput.InsertException(conn, " lastChange sysdefine  after" + "__" + entity.EntityCode + "__" + entity.LastScanCheckSum);
                        }
                    }
                }

                //}
                //catch (Exception ex)
                //{

                //    //using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
                //    //{
                //    //    conn.Open();
                //    //    CLROutput.InsertException(conn, " exception " + entity.EntityId  + "__" + entity.EntityCode   + ex.ToString());

                //    //}

                //}
            }
        }
Exemple #15
0
        public static void GetTabValue(int tabId, string referenceIds, bool isShowLookupitem, bool IsSubItemFullPathName = true)
        {
            if (string.IsNullOrEmpty(referenceIds))
            {
                return;
            }

            System.Data.DataTable tabFieldResultDataTable = GetTabDataTable(tabId, referenceIds, isShowLookupitem, IsSubItemFullPathName);



            CLROutput.SendDataTable(tabFieldResultDataTable);
        }
Exemple #16
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);
        }
        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);
            }
        }
Exemple #18
0
        private static void SetupEntityScanTimer()
        {
            CLROutput.OutputDebug("tiemrestart");
            Timer SessionTimer = new Timer();

            SessionTimer.Enabled   = true;
            SessionTimer.AutoReset = true;

            SessionTimer.Elapsed += new ElapsedEventHandler(ScanSessionTimer_Elapsed);

            // open datasource, edist save,, then open teckpakc print need one 1 minus
            // cause bug 1.5 ???
            SessionTimer.Interval = 1 * 20 * 1000;
            //  SessionTimer.Interval = 5 * 1000;
            SessionTimer.Start();
        }
Exemple #19
0
        internal static List <GridColumnClrUserDefineDto> GetTabGridSelectColumnAndAliasDtoFromCache(int tabId, PdmBlockClrDto dmBlockClrDto)
        {
            string key = tabId.ToString() + "_" + dmBlockClrDto.BlockId.ToString();

            if (DictMainTabBlokGridColumnCache.ContainsKey(key))
            {
                CLROutput.OutputDebug("string key = " + key);
                return(DictMainTabBlokGridColumnCache[key]);
            }
            else
            {
                List <GridColumnClrUserDefineDto> printColumnListDto = GetTabGridBlockColumnAliasName(tabId, dmBlockClrDto);
                DictMainTabBlokGridColumnCache.Add(key, printColumnListDto);
                return(printColumnListDto);
            }
        }
Exemple #20
0
        public static void GetMergeBlockPrintGrid(int tabId, int gridBlockId1, string block1RwValueFilter, int gridBlockId2, string block2RwValueFilter, string referenceIds)
        {
            if (gridBlockId2 == -1) // it is one block
            {
                DataTable gridBlock1 = PLMSGetGridValueHeler.GetOneGridBlockSelectRows(tabId, gridBlockId1, block1RwValueFilter, referenceIds, true, false);

                CLROutput.SendDataTable(gridBlock1);
            }
            else // it is merger gridblcok
            {
                DataTable gridTable1 = PLMSGetGridValueHeler.GetOneGridBlockSelectRows(tabId, gridBlockId1, block1RwValueFilter, referenceIds, true, false);

                DataTable gridTable2 = PLMSGetGridValueHeler.GetOneGridBlockSelectRows(tabId, gridBlockId2, block2RwValueFilter, referenceIds, true, false);;



                if (gridTable2.Columns.Count == gridTable1.Columns.Count)
                {
                    for (int i = 0; i < gridTable2.Columns.Count; i++)
                    {
                        gridTable2.Columns[i].ColumnName = gridTable1.Columns[i].ColumnName;
                    }


                    gridTable1.Merge(gridTable2);
                }



                // int count =1;
                //foreach (DataRow row in gridTable1.Rows)
                //{
                //    if (row.Table.Columns.Contains("Sort"))
                //    {
                //        row["Sort"] = count;
                //        count++;

                //    }

                //}



                CLROutput.SendDataTable(gridTable1);
            }
        }
Exemple #21
0
        private static object InsertNewEntityWithReturnNewIdentityId(SqlConnection conn, string sqlInsertpdmUserDefineEntityRow, SqlCommand cmdInsertNewentity)
        {
            cmdInsertNewentity.Connection  = conn;
            cmdInsertNewentity.CommandText = sqlInsertpdmUserDefineEntityRow;



            CLROutput.Output("sqlInsertpdmUserDefineEntityRow" + sqlInsertpdmUserDefineEntityRow);
            cmdInsertNewentity.ExecuteNonQuery();



            cmdInsertNewentity.CommandText = "SELECT SCOPE_IDENTITY()";
            object rowID = cmdInsertNewentity.ExecuteScalar();

            CLROutput.Output("SELECT SCOPE_IDENTITY( ) RowId=" + rowID);
            return(rowID);
        }
Exemple #22
0
        private static void InsertPdmUserDefineColunmDto(PdmEntityColumnClrDto pdmEntityColumnClrDto, SqlConnection conn)
        {
            int bitPrimaryKey = 0;

            if (pdmEntityColumnClrDto.IsPrimaryKey.HasValue && pdmEntityColumnClrDto.IsPrimaryKey.Value)
            {
                bitPrimaryKey = 1;
            }



            int NBDecimal = 0;

            if (pdmEntityColumnClrDto.Nbdecimal.HasValue)
            {
                NBDecimal = pdmEntityColumnClrDto.Nbdecimal.Value;
            }


            string sqlInsertPdmEntityColumn = string.Format(@" 

                         Insert into    dbo.pdmUserDefineEntityColumn
                                  (
                                        EntityID,
                                        ColumnName,
                                        IsPrimaryKey,
                                        UIControlType,
                                        NBDecimal
                                        
                                   )  " +
                                                            @" values 
                                (
                                 {0},'{1}',{2},{3},{4}
 
                                )
                                    ", pdmEntityColumnClrDto.EntityId, pdmEntityColumnClrDto.ColumnName, bitPrimaryKey, pdmEntityColumnClrDto.UicontrolType, NBDecimal);

            using (SqlCommand insertNewentity = new SqlCommand(sqlInsertPdmEntityColumn, conn))
            {
                CLROutput.Output("sqlInsertPdmEntity" + sqlInsertPdmEntityColumn);
                insertNewentity.ExecuteNonQuery();
            }
        }
Exemple #23
0
        public static void GetGridExternalColumnValue(int gridTabId, int currentGridBlockId, string referenceIds)
        {
            if (string.IsNullOrEmpty(referenceIds))
            {
                return;
            }

            PdmBlockClrDto dmBlockClrDto = PdmCacheManager.DictBlockCache[currentGridBlockId];

            if (dmBlockClrDto.BlockPdmGridDto == null)
            {
                return;
            }

            bool IsGetAliasname = false;
            // Get Grading Size will call PrepareGetGridDataTable and set
            DataTable gridcolumnResultDataTable = PLMSGetGridValueHeler.LoadVariousGridColumnValue(gridTabId, currentGridBlockId, referenceIds, false, dmBlockClrDto, false, false, IsGetAliasname);

            CLROutput.SendDataTable(gridcolumnResultDataTable);
        }
Exemple #24
0
        internal static List <GridColumnClrUserDefineDto> GetTabGridSelectColumnAndAliasDtoFromCache(int tabId, PdmBlockClrDto dmBlockClrDto, bool IsGetAliasname)
        {
            string key = tabId.ToString() + "_" + dmBlockClrDto.BlockId.ToString();


            if (IsGetAliasname)
            {
                if (DictMainTabBlokGridColumnCache.ContainsKey(key))
                {
                    CLROutput.OutputDebug("string key = " + key);
                    return(DictMainTabBlokGridColumnCache[key]);
                }
                else
                {
                    List <GridColumnClrUserDefineDto> printColumnListDto = GetTabGridBlockColumnAliasName(tabId, dmBlockClrDto, true);
                    DictMainTabBlokGridColumnCache.Add(key, printColumnListDto);
                    return(printColumnListDto);
                }
            }
            else // it is External mapping column
            {
                if (DictMainTabBlokGridColumnExternaMappingCache.ContainsKey(key))
                {
                    //  CLROutput.OutputDebug("string key = " + key);
                    return(DictMainTabBlokGridColumnExternaMappingCache[key]);
                }
                else
                {
                    List <GridColumnClrUserDefineDto> printColumnListDto = GetTabGridBlockColumnAliasName(tabId, dmBlockClrDto, false);
                    DictMainTabBlokGridColumnExternaMappingCache.Add(key, printColumnListDto);
                    return(printColumnListDto);
                }
            }

            // List<GridColumnClrUserDefineDto> printColumnListDto = GetTabGridBlockColumnAliasName(tabId, dmBlockClrDto, IsGetAliasname);
            //  DictMainTabBlokGridColumnCache.Add(key, printColumnListDto);
            // return printColumnListDto;
        }
Exemple #25
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);
        }
        }        // key1: blockId, key2:referecne, value

        public static Dictionary <int, Dictionary <int, object> > RetrieveReferenceSimpleDcutValue(IEnumerable <int> referenceIds, List <PdmBlockSubItemClrDto> subItemList)
        {
            // subitemIds = subitemIds.Distinct().ToArray ();

            if (referenceIds.Count() == 0 || subItemList.Count == 0)
            {
                return(new Dictionary <int, Dictionary <int, object> >());
            }

            Dictionary <int, int> dictSuibtemIdControType = new Dictionary <int, int>();

            foreach (var blockSubitem in subItemList)
            {
                dictSuibtemIdControType.Add(blockSubitem.SubItemId, blockSubitem.ControlType);
            }

            Dictionary <int, Dictionary <int, object> > toReturn = new Dictionary <int, Dictionary <int, object> >();

            if (referenceIds == null || referenceIds.Count() == 0)
            {
                return(toReturn);
            }

            var subitemIds        = subItemList.Select(o => o.SubItemId).ToList();
            var referenceInClause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(referenceIds, GridColumnConstantName.ProductReferenceID, false);
            var subitemIdInclause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(subitemIds, GridColumnConstantName.SubItemID, true);

            StringBuilder aQuery = new StringBuilder();

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

            aQuery.Append(" SELECT " +
                          GridColumnConstantName.ProductReferenceID + ", " +
                          GridColumnConstantName.SubItemID + ", " +
                          GridColumnConstantName.ValueText
                          );

            string queryString = aQuery.ToString() + " from pdmSearchSimpleDcuValue where " + referenceInClause + subitemIdInclause;

            CLROutput.OutputDebug("from pdmSearchSimpleDcuValu" + queryString);

            DataTable dtSimpleValue;

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

                dtSimpleValue = DataAcessHelper.GetDataTableQueryResult(conn, queryString);

                DataAcessHelper.ExecuteReadCommmited(conn);
            }

            var subItemQuery = from row in dtSimpleValue.AsDataRowEnumerable()
                               group row by new
            {
                ProductReferenceID = (int)row[GridColumnConstantName.ProductReferenceID]
            } into grp
            orderby grp.Key.ProductReferenceID
                select new
            {
                Key = grp.Key.ProductReferenceID,

                SubitemIDAndValueList = grp.Select(r => new { id = (int)r[GridColumnConstantName.SubItemID], value = r[GridColumnConstantName.ValueText] }),
            };

            foreach (var o in subItemQuery)
            {
                Dictionary <int, object> dictSubitemValue = new Dictionary <int, object>();

                foreach (var subitem in o.SubitemIDAndValueList)
                {
                    var value = ControlTypeValueConverter.ConvertValueToObject(subitem.value, dictSuibtemIdControType[subitem.id]);
                    dictSubitemValue.Add(subitem.id, value);
                }

                var notInDict = subitemIds.Except(dictSubitemValue.Keys);
                foreach (var subitId in notInDict)
                {
                    dictSubitemValue.Add(subitId, null);
                }

                toReturn.Add(o.Key, dictSubitemValue);
            }

            return(toReturn);
        }        // key1: blockId, key2:referecne, value
        public static DataTable RetrieveDataTableReferenceSimpleDcutValue(IEnumerable <string> referenceIds, List <PdmBlockSubItemClrDto> subItemList, bool isShowLookUpItem = true)
        {
            Dictionary <int, int> dictSuibtemIdControType = new Dictionary <int, int>();

            foreach (var blockSubitem in subItemList)
            {
                dictSuibtemIdControType.Add(blockSubitem.SubItemId, blockSubitem.ControlType);
            }

            Dictionary <int, Dictionary <int, object> > toReturn = new Dictionary <int, Dictionary <int, object> >();

            //if (referenceIds == null || referenceIds.Count() == 0)
            //    return simpleDcuTable;

            var subitemIds = subItemList.Select(o => o.SubItemId).ToList();

            if (subitemIds.Count == 0)
            {
                return(new DataTable());
            }

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

            if (referenceIds.Count() == 1 && referenceIds.ElementAt(0) == "-2")
            {
                referenceInClause = string.Empty;
            }


            string subitemIdInclause = DataAcessHelper.GenerateColumnInClauseWithAndCondition(subitemIds, GridColumnConstantName.SubItemID, false);

            StringBuilder aQuery = new StringBuilder();

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

            aQuery.Append(" SELECT " +
                          GridColumnConstantName.ProductReferenceID + ", " +
                          GridColumnConstantName.SubItemID + ", " +
                          GridColumnConstantName.ValueText
                          );

            string queryString = aQuery.ToString() + " from pdmSearchSimpleDcuValue where " + subitemIdInclause + referenceInClause;



            DataTable dtSimpleValue;

            var allSubitemeEntityIds = subItemList.Where(o => o.EntityId.HasValue).Select(o => o.EntityId.Value).ToList();
            Dictionary <int, Dictionary <object, string> > dictKeyEntityDictKeyIdDisplayString = PLMSEntityClrBL.GetDictEntityDictDisplayString(allSubitemeEntityIds);



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

                dtSimpleValue = DataAcessHelper.GetDataTableQueryResult(conn, queryString);
                // dictKeyEntityDictKeyIdDisplayString = PLMSEntityClrBL.GetDictEntityDictDisplayString(allSubitemeEntityIds, conn);

                DataAcessHelper.ExecuteReadCommmited(conn);
            }

            var subItemQuery = from row in dtSimpleValue.AsDataRowEnumerable()
                               group row by new
            {
                ProductReferenceID = (int)row[GridColumnConstantName.ProductReferenceID]
            } into grp
            orderby grp.Key.ProductReferenceID
                select new
            {
                Key = grp.Key.ProductReferenceID,

                SubitemIDAndValueList = grp.Select(r => new { id = (int)r[GridColumnConstantName.SubItemID], value = r[GridColumnConstantName.ValueText] }),
            };

            DataTable roReturnDcuTable = CreateDataTableStrcutureFromBlockSubitem(subItemList);

            foreach (var o in subItemQuery)
            {
                //Dictionary<int, object> dictSubitemValue = new Dictionary<int, object>();

                DataRow dataRow = roReturnDcuTable.NewRow();
                dataRow[GridColumnConstantName.ProductReferenceID] = o.Key;
                foreach (var subitem in o.SubitemIDAndValueList)
                {
                    object value = ControlTypeValueConverter.ConvertValueToObject(subitem.value, dictSuibtemIdControType[subitem.id]);


                    if (value == null)
                    {
                        dataRow[subitem.id.ToString()] = DBNull.Value;
                    }
                    else
                    {
                        //  CLROutput.Output("convervalue=" + value);
                        dataRow[subitem.id.ToString()] = value;

                        //  CLROutput.Output("dataRow[subitem.id.ToString()]" + dataRow[subitem.id.ToString()]);
                    }
                }

                roReturnDcuTable.Rows.Add(dataRow);
            }

            if (isShowLookUpItem)
            {
                List <PdmBlockSubItemClrDto> dDLSubitemDtoList = subItemList.Where(o => o.ControlType == (int)EmControlType.DDL && o.EntityId.HasValue).ToList();

                foreach (var dictDDLSubitemDto in dDLSubitemDtoList)
                {
                    int    entityId        = dictDDLSubitemDto.EntityId.Value;
                    string stringSubItemId = dictDDLSubitemDto.SubItemId.ToString();
                    if (dictKeyEntityDictKeyIdDisplayString.ContainsKey(entityId))
                    {
                        Dictionary <object, string> lookUpItem = dictKeyEntityDictKeyIdDisplayString[entityId];
                        foreach (DataRow row in roReturnDcuTable.Rows)
                        {
                            int?lookupid = ControlTypeValueConverter.ConvertValueToInt(row[stringSubItemId]);
                            if (lookupid.HasValue)
                            {
                                int idvalue = lookupid.Value;
                                if (lookUpItem.ContainsKey(idvalue))
                                {
                                    row[stringSubItemId] = lookUpItem[idvalue];;

                                    CLROutput.OutputDebug("entityID_idvalue+dislayvalue=" + entityId.ToString() + "_" + idvalue.ToString() + "_" + "_" + lookUpItem[idvalue]);
                                }
                            }
                        }
                    }
                }
            }


            return(roReturnDcuTable);
        }        // key1: blockId, key2:referecne, value
        /// <summary>
        /// Main method for exporting to Excel.
        /// </summary>
        /// <param name="data"></param>
        public void Export(DataSet data)
        {
            string outputFile;

            outputFile = mFilePath + defaultFilename + ".xls";

            XmlWriterSettings settings = new XmlWriterSettings();

            settings.Indent = true;

            CLROutput.OutputDebug("outputFile=" + outputFile);

            using (XmlWriter xw = XmlWriter.Create(outputFile, settings))
            {
                //Required namespaces used for SpreadsheetML standard.
                xw.WriteStartDocument();
                xw.WriteProcessingInstruction("mso-application", "Excel.Sheet");
                xw.WriteStartElement("Workbook", "urn:schemas-microsoft-com:office:Spreadsheet");
                xw.WriteAttributeString("xmlns", "o", null, "urn:schemas-microsoft-com:office:office");
                xw.WriteAttributeString("xmlns", "x", null, "urn:schemas-microsoft-com:office:excel");
                xw.WriteAttributeString("xmlns", "ss", null, "urn:schemas-microsoft-com:office:Spreadsheet");
                xw.WriteAttributeString("xmlns", "html", null, "http://www.w3.org/TR/REC-html40");

                WriteHeaderInfo(xw);

                //Iterate the tables in the dataset.
                //Each table will become a tab or sheet in the workbook.
                foreach (DataTable dt in data.Tables)
                {
                    //Default the tab name to either the table name
                    //or pull tab name from first column of each resultset.
                    string sheetName;
                    if (dt.Rows.Count > 0)
                    {
                        sheetName = dt.Rows[0][sheetNameColumnOrdinal].ToString();
                    }
                    else
                    {
                        sheetName = dt.TableName;
                    }

                    //Start of a tab
                    xw.WriteStartElement("Worksheet");
                    xw.WriteAttributeString("ss", "Name", null, sheetName);

                    xw.WriteStartElement("Table");
                    xw.WriteAttributeString("ss", "DefaultColumnWidth", null, "100");

                    //Write out header data
                    xw.WriteStartElement("Row");
                    //Format column headings
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (dc.Ordinal != sheetNameColumnOrdinal)
                        {
                            xw.WriteStartElement("Cell");
                            xw.WriteAttributeString("ss", "StyleID", null, "Header");
                            xw.WriteStartElement("Data");
                            xw.WriteAttributeString("ss", "Type", null, "String");
                            xw.WriteString(dc.ColumnName);
                            xw.WriteEndElement(); //End Data
                            xw.WriteEndElement(); //End Cell
                        }
                    }
                    xw.WriteEndElement(); //End Row

                    //Write out row data
                    foreach (DataRow dr in dt.Rows)
                    {
                        xw.WriteStartElement("Row");
                        foreach (DataColumn dc in dt.Columns)
                        {
                            if (dc.Ordinal != sheetNameColumnOrdinal)
                            {
                                string dataType;
                                string style;
                                string output;

                                //Set appropriate styling of each cell based on datatype
                                //This depends on how sql server ends up reporting the datatype.
                                switch (dc.DataType.ToString())
                                {
                                case "System.DateTime":
                                    dataType = "DateTime";
                                    style    = "Date";

                                    try
                                    {
                                        output = DateTime.Parse(dr[dc].ToString()).ToString("yyyy-MM-dd");
                                    }
                                    catch (FormatException fe)     //date is null or empty in dataset
                                    {
                                        output = "";
                                    }

                                    break;

                                case "System.Decimal":
                                case "System.Double":
                                case "System.Int16":
                                case "System.Int32":
                                case "System.Int64":
                                case "System.Byte":
                                    dataType = "Number";
                                    style    = "Data";
                                    output   = dr[dc].ToString().Trim();
                                    break;

                                default:
                                    dataType = "String";
                                    style    = "Data";
                                    output   = dr[dc].ToString().Trim();
                                    break;
                                }

                                //if no data then write empty cell node
                                xw.WriteStartElement("Cell");
                                xw.WriteStartAttribute("StyleID", "");
                                xw.WriteString(style);
                                xw.WriteEndAttribute(); //End Style Attribute
                                if (output != "")
                                {
                                    xw.WriteStartElement("Data");
                                    xw.WriteAttributeString("ss", "Type", null, dataType);
                                    xw.WriteString(output);
                                    xw.WriteEndElement(); //End Data
                                }
                                xw.WriteEndElement();     //End Cell
                            }
                        }
                        xw.WriteEndElement(); //End Row
                    }

                    xw.Flush();
                    xw.WriteEndElement(); //End Table
                    xw.WriteEndElement(); //End Worksheet
                }

                xw.WriteEndElement(); //End Workbook
                xw.Flush();
            }
        }
        // [Microsoft.SqlServer.Server.SqlProcedure]
        public static void DoSynchronizeReferenceBlockToTabGridDWTo(string refids, string blockids)
        {
            if (string.IsNullOrEmpty(refids) || string.IsNullOrEmpty(blockids))
            {
                return;
            }

            #region ------------ do Tab sych

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

                //InsertException(conn, "refids=" + refids + " blockids=" + blockids);
                CLROutput.OutputDebug("refids=" + refids + " blockids=" + blockids, conn);



                string processTab = string.Format(@"
                      SELECT DISTINCT pdmTabBlock.TabID
                    FROM         pdmTabBlock INNER JOIN
                     pdmBlock  on pdmTabBlock.BlockID = pdmBlock.BlockID 
                     inner join 
                      pdmDWRequireTabAndGrid ON pdmDWRequireTabAndGrid.TabID = pdmTabBlock.TabID INNER JOIN
                      pdmBlockSubItem ON pdmTabBlock.BlockID = pdmBlockSubItem.BlockID
                    WHERE     (pdmBlockSubItem.GridID IS NULL) and    (pdmTabBlock.BlockID IN ({0}))", blockids);

                CLROutput.OutputDebug("processTabBlock=" + processTab, conn);


                SqlCommand cmd = new SqlCommand(processTab, conn);

                SqlDataAdapter        adapter     = new SqlDataAdapter(cmd);
                System.Data.DataTable resultTabel = new DataTable();
                adapter.Fill(resultTabel);

                foreach (DataRow aRow in resultTabel.Rows)
                {
                    //begin root leve

                    SqlInt32 aTabID = Converter.ToDDLSqlInt32(aRow["TabID"]);
                    ProcessSingleTab(aTabID, conn, refids);
                }
            }

            #endregion

            #region -----------  do Grid Transfer

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

//                string processGrid = string.Format(@"
// SELECT DISTINCT   dbo.pdmBlockSubItem.BlockID, dbo.pdmBlockSubItem.GridID, dbo.pdmTabField.TabID, dbo.pdmTab.ProductCopyTabRootTabID
//                                                                    FROM         dbo.pdmBlockSubItem INNER JOIN
//                                                                    dbo.pdmItem ON dbo.pdmBlockSubItem.BlockID = dbo.pdmItem.BlockID INNER JOIN
//                                                                    dbo.pdmTabField ON dbo.pdmItem.FieldID = dbo.pdmTabField.FieldID INNER JOIN
//                                                                    dbo.pdmTab ON dbo.pdmTabField.TabID = dbo.pdmTab.TabID
//                                                                     INNER JOIN  pdmDWRequireTabAndGrid on pdmDWRequireTabAndGrid.GridID = dbo.pdmBlockSubItem.GridID and pdmDWRequireTabAndGrid.TabID=dbo.pdmTab.TabID
//                                                                WHERE     (dbo.pdmBlockSubItem.GridID IS NOT NULL) and  dbo.pdmTab.ProductCopyTabRootTabID is null
//                                                                  AND      ( dbo.pdmBlockSubItem.BlockID IN ({0}))", blockids);



                string processGrid = string.Format(@"
 SELECT DISTINCT pdmBlockSubItem.BlockID, pdmBlockSubItem.GridID, PdmTabBlock.TabID
FROM         pdmDWRequireTabAndGrid INNER JOIN
                      pdmBlockSubItem ON pdmDWRequireTabAndGrid.GridID = pdmBlockSubItem.GridID INNER JOIN
                      PdmTabBlock ON pdmBlockSubItem.BlockID = PdmTabBlock.BlockID AND pdmDWRequireTabAndGrid.TabID = PdmTabBlock.TabID
               WHERE     (dbo.pdmBlockSubItem.GridID IS NOT NULL)   AND      ( dbo.pdmBlockSubItem.BlockID IN ({0}))", blockids);


                SqlCommand cmd = new SqlCommand(processGrid, conn);

                SqlDataAdapter        adapter     = new SqlDataAdapter(cmd);
                System.Data.DataTable resultTabel = new DataTable();
                adapter.Fill(resultTabel);

                foreach (DataRow aRow in resultTabel.Rows)
                {
                    //dbo.pdmTab.TabID, dbo.pdmTab.ProductCopyTabRootTabID, dbo.pdmTab.ProductReferenceID

                    SqlInt32 aGridID  = Converter.ToDDLSqlInt32(aRow["GridID"]);
                    SqlInt32 aTabID   = Converter.ToDDLSqlInt32(aRow["TabID"]);
                    SqlInt32 aBlockID = Converter.ToDDLSqlInt32(aRow["BlockID"]);

                    string gridTablename = string.Empty;
                    if (PLMSDWStoredProcedures.GridTableName.ContainsKey(aGridID.Value))
                    {
                        gridTablename = PLMSDWStoredProcedures.GridTableName[aGridID.Value];

                        CLROutput.OutputDebug("GridTableName=" + gridTablename, conn);
                    }

                    if (gridTablename != string.Empty)
                    {
                        //TabID,ProductReferenceID, GridID

                        string deleteOLDValue = string.Format(" delete {0} WHERE TabID={1} and ProductReferenceID in ({2}) and GridID={3} and BlockID = {4} ", gridTablename, aTabID, refids, aGridID, aBlockID);

                        try
                        {
                            SqlCommand deletcmd = new SqlCommand(deleteOLDValue, conn);
                            deletcmd.ExecuteNonQuery();
                        }
                        catch
                        {
                            CLROutput.OutputDebug("deleteGridFieldOLDValue=" + deleteOLDValue, conn);
                        }

                        string replaceWHERE = string.Format(" where TabID={0} and ProductReferenceID in ({1})   and BlockID = {2} and ", aTabID, refids, aBlockID);

                        string insertIntoGridTable = string.Empty;

                        string getRootGridSelectQuery = @"select top 1 RootlevelselectSQLscript from PLM_Dw_tabgridscript where gridid=" + aGridID + " and tabid is null";

                        SqlCommand cmdRootGrid    = new SqlCommand(getRootGridSelectQuery, conn);
                        string     rootLevelquery = cmdRootGrid.ExecuteScalar() as string;

                        //WHERE Gridid=7 group by TabID ,ProductReferenceID ,BlockID,GridID,RowID,RowValueGUID,Sort
                        insertIntoGridTable = " insert  into  " + gridTablename + " " + rootLevelquery.Replace("WHERE", replaceWHERE);

                        try
                        {
                            // SqlContext.Pipe.Send("exception insertcmd=" + insertIntoGridTable);
                            SqlCommand insertcmd = new SqlCommand(insertIntoGridTable, conn);

                            insertcmd.ExecuteNonQuery();


                            CLROutput.OutputDebug("insertIntoGridTable=" + insertIntoGridTable, conn);
                        }
                        catch (Exception ex)
                        {
                            // SqlContext.Pipe.Send("exception insertcmd=" + insertIntoGridTable);


                            CLROutput.OutputDebug("exception insertcmd failed=" + insertIntoGridTable + ex.ToString(), conn);
                        }
                    }
                }
            }

            #endregion
        }
        public static void DoSynchronizeUserDefineTableToDW(int entityID)
        {
            using (SqlConnection conn = new SqlConnection(PLMConstantString.PLM_APP_ConnectionString))
            {
                conn.Open();


                string tabQuery = @"select  RootlevelselectSQLscript from " + PLMConstantString.PLM_DW_TabGridScripContainerTable + " where EntityID=" + entityID + " and IsPassValidation =1";

                SqlCommand cmdQueryTabNameAndSelectRootlevel = new SqlCommand(tabQuery, conn);
                string     sQLTabLevelSelect = cmdQueryTabNameAndSelectRootlevel.ExecuteScalar() as string;



                string tabTablename = PLMSDWStoredProcedures.EntityTableName[entityID];



                if (tabTablename != string.Empty)
                {
                    string deleteOLDValue = string.Format(" delete {0}  ", tabTablename);

                    SqlCommand deletcmd = new SqlCommand(deleteOLDValue, conn);


                    CLROutput.OutputDebug("before delete Entity OLDValue=" + deleteOLDValue, conn);



                    try
                    {
                        deletcmd.ExecuteNonQuery();

                        CLROutput.OutputDebug("after delete ok deleteOLDValue=" + deleteOLDValue, conn);
                    }
                    catch (Exception ex)
                    {
                        CLROutput.OutputDebug("delete failed deleteOLDValue=" + deleteOLDValue + ex.ToString(), conn);
                    }



                    string inserIntoSelect = " insert  into " + tabTablename + "  " + sQLTabLevelSelect;

                    CLROutput.OutputDebug("before inserIntoSelect=" + inserIntoSelect, conn);



                    try
                    {
                        SqlCommand insertcmd = new SqlCommand(inserIntoSelect, conn);
                        insertcmd.ExecuteNonQuery();


                        CLROutput.OutputDebug("after insert ok   inserIntoSelect=" + inserIntoSelect, conn);
                    }
                    catch (Exception ex)
                    {
                        CLROutput.OutputDebug("exception insertcmd failed=" + inserIntoSelect + ex.ToString(), conn);
                    }
                }
            }
        }