예제 #1
0
        public bool processUplodedFile(int BookingId, ArrayList RecordList, XmlDocument xmlMappingDoc)
        {
            List <Cells> cellList = new List <Cells>();
            Cells        cell     = null;

            List <Row> rowList     = null;
            Row        row         = new Row();
            int        cellCounter = 0;

            try
            {
                XMLMapper xmlMapper = PrepareXMLMapper(xmlMappingDoc);
                string[]  csvCells  = null;

                if (RecordList != null && RecordList.Count > 1)
                {
                    rowList = new List <Row>();
                    for (int i = 0; i < RecordList.Count; i++)
                    {
                        if (i >= 4) // xmlMapper.StartingRow - 1
                        {
                            row        = new Row();
                            row.RowNum = Convert.ToString(i + 1);

                            cellList    = new List <Cells>();
                            cellCounter = 1;

                            csvCells = RecordList[i].ToString().Split(',');

                            for (int j = 0; j < csvCells.Length; j++)
                            {
                                cell        = new Cells();
                                cell.ColNum = cellCounter.ToString().Trim();
                                cellCounter++;

                                cell.CellValue = RemoveCSVFormatters(csvCells[j].ToString().Trim());
                                cellList.Add(cell);
                            }
                            row.CellList = cellList;
                            rowList.Add(row);
                            //if (i >= xmlMapper.StartingRow - 1) //Avoiding the Header Row
                            //  prepareQuery(BookingId, cellList, xmlMapper);
                        }
                    }
                }
                prepareQuery(BookingId, rowList, xmlMapper);
            }
            catch (Exception exp)
            {
                throw exp;
            }
            return(true);
        }
예제 #2
0
        private XMLMapper PrepareXMLMapper(XmlDocument xmlMappingDoc)
        {
            string    xPathTable       = @"//table";
            string    xPathStartRow    = @"//startRow";
            string    xPathReadPattern = @"//readPattern";
            string    xPathFieldMapper;
            XMLMapper xmlMapper = null;

            List <ReadPattern> readPatternList = new List <ReadPattern>();
            ReadPattern        readPattern;

            List <XMLFieldMapper> xmlFieldMapperList = new List <XMLFieldMapper>();
            XMLFieldMapper        xmlFieldMapper;

            XmlNodeList nodeList;
            XmlNodeList fieldNodeList;
            string      tableName   = string.Empty;
            string      startingRow = string.Empty;

            try
            {
                #region Getting Table Name
                nodeList = xmlMappingDoc.SelectNodes(xPathTable);
                if (nodeList != null && nodeList.Count > 0)
                {
                    tableName = !String.IsNullOrEmpty(nodeList.Item(0).Attributes["value"].Value) ? nodeList.Item(0).Attributes["value"].Value : String.Empty;
                }
                #endregion

                nodeList = xmlMappingDoc.SelectNodes(xPathStartRow);
                if (nodeList != null && nodeList.Count > 0)
                {
                    startingRow = !String.IsNullOrEmpty(nodeList.Item(0).Attributes["value"].Value) ? nodeList.Item(0).Attributes["value"].Value : String.Empty;
                }

                nodeList = xmlMappingDoc.SelectNodes(xPathReadPattern);
                if (nodeList != null && nodeList.Count > 0)
                {
                    xmlFieldMapperList = new List <XMLFieldMapper>();
                    foreach (XmlNode node in nodeList)
                    {
                        xPathFieldMapper = xPathReadPattern + @"[@value='READPATTERN']//mapper";
                        #region GetReadPattern
                        xmlMapper             = new XMLMapper();
                        xmlMapper.TableName   = tableName;
                        xmlMapper.StartingRow = Convert.ToInt32(startingRow);

                        readPattern = new ReadPattern();
                        readPattern.ReadPatternValue = node.Attributes["value"].Value;
                        readPattern.StartRow         = node.Attributes["startRow"].Value;
                        readPattern.StartCol         = node.Attributes["startcol"].Value;
                        readPattern.EndCol           = node.Attributes["endcol"].Value;
                        #endregion

                        #region Getting FieldMapper

                        xPathFieldMapper = xPathFieldMapper.Replace("READPATTERN", readPattern.ReadPatternValue);

                        fieldNodeList = xmlMappingDoc.SelectNodes(xPathFieldMapper);
                        if (nodeList != null && fieldNodeList.Count > 0)
                        {
                            xmlFieldMapperList = new List <XMLFieldMapper>();
                            foreach (XmlNode fieldNode in fieldNodeList)
                            {
                                xmlFieldMapper = new XMLFieldMapper();
                                if (fieldNode.Attributes.GetNamedItem("dbField") != null)
                                {
                                    xmlFieldMapper.DbField = !String.IsNullOrEmpty(fieldNode.Attributes["dbField"].Value) ? fieldNode.Attributes["dbField"].Value : String.Empty;
                                }
                                if (fieldNode.Attributes.GetNamedItem("col") != null)
                                {
                                    xmlFieldMapper.ExcelColumn = !String.IsNullOrEmpty(fieldNode.Attributes["col"].Value) ? fieldNode.Attributes["col"].Value : String.Empty;
                                }
                                if (fieldNode.Attributes.GetNamedItem("row") != null)
                                {
                                    xmlFieldMapper.ExcelRow = !String.IsNullOrEmpty(fieldNode.Attributes["row"].Value) ? fieldNode.Attributes["row"].Value : String.Empty;
                                }
                                if (fieldNode.Attributes.GetNamedItem("idField") != null)
                                {
                                    xmlFieldMapper.IsIdField = !String.IsNullOrEmpty(fieldNode.Attributes["idField"].Value) ? Boolean.Parse(fieldNode.Attributes["idField"].Value) : false;
                                }
                                if (fieldNode.Attributes.GetNamedItem("Alpha") != null)
                                {
                                    xmlFieldMapper.IsAlpha = !String.IsNullOrEmpty(fieldNode.Attributes["Alpha"].Value) ? Boolean.Parse(fieldNode.Attributes["Alpha"].Value) : false;
                                }
                                if (fieldNode.Attributes.GetNamedItem("MasterLookUpTable") != null)
                                {
                                    xmlFieldMapper.MasterLookUpTable = !String.IsNullOrEmpty(fieldNode.Attributes["MasterLookUpTable"].Value) ? fieldNode.Attributes["MasterLookUpTable"].Value : String.Empty;
                                }
                                if (fieldNode.Attributes.GetNamedItem("MasterLookUpField") != null)
                                {
                                    xmlFieldMapper.MasterLookUpField = !String.IsNullOrEmpty(fieldNode.Attributes["MasterLookUpField"].Value) ? fieldNode.Attributes["MasterLookUpField"].Value : String.Empty;
                                }
                                if (fieldNode.Attributes.GetNamedItem("MasterIdField") != null)
                                {
                                    xmlFieldMapper.MasterIdField = !String.IsNullOrEmpty(fieldNode.Attributes["MasterIdField"].Value) ? fieldNode.Attributes["MasterIdField"].Value : String.Empty;
                                }
                                if (fieldNode.Attributes.GetNamedItem("DateField") != null)
                                {
                                    xmlFieldMapper.IsDateField = !String.IsNullOrEmpty(fieldNode.Attributes["DateField"].Value) ? Boolean.Parse(fieldNode.Attributes["DateField"].Value) : false;
                                }
                                if (fieldNode.Attributes.GetNamedItem("TimeField") != null)
                                {
                                    xmlFieldMapper.TimeField = !String.IsNullOrEmpty(fieldNode.Attributes["TimeField"].Value) ? Boolean.Parse(fieldNode.Attributes["TimeField"].Value) : false;
                                }
                                if (fieldNode.Attributes.GetNamedItem("JoinToPrev") != null)
                                {
                                    xmlFieldMapper.JoinToPrev = !String.IsNullOrEmpty(fieldNode.Attributes["JoinToPrev"].Value) ? Boolean.Parse(fieldNode.Attributes["JoinToPrev"].Value) : false;
                                }
                                if (fieldNode.Attributes.GetNamedItem("BoolField") != null)
                                {
                                    xmlFieldMapper.IsBool = !String.IsNullOrEmpty(fieldNode.Attributes["BoolField"].Value) ? Boolean.Parse(fieldNode.Attributes["BoolField"].Value) : false;
                                }
                                if (fieldNode.Attributes.GetNamedItem("FetchTouristNo") != null)
                                {
                                    xmlFieldMapper.FetchTouristNo = !String.IsNullOrEmpty(fieldNode.Attributes["FetchTouristNo"].Value) ? Boolean.Parse(fieldNode.Attributes["FetchTouristNo"].Value) : false;
                                }
                                if (fieldNode.Attributes.GetNamedItem("Encrypt") != null)
                                {
                                    xmlFieldMapper.Encrypt = !String.IsNullOrEmpty(fieldNode.Attributes["Encrypt"].Value) ? Boolean.Parse(fieldNode.Attributes["Encrypt"].Value) : false;
                                }
                                xmlFieldMapperList.Add(xmlFieldMapper);
                            }
                        }
                        #endregion

                        readPattern.XmlFieldMapperList = xmlFieldMapperList;
                        readPatternList.Add(readPattern);
                        xmlMapper.ReadPatternList = readPatternList;
                    }
                }
            }
            catch (Exception exp)
            {
                GF.LogError("PrepareXMLMapper", exp.Message);
            }
            return(xmlMapper);
        }
예제 #3
0
        private void prepareQuery(int BookingId, List <Row> rowList, XMLMapper xmlMapper)
        {
            StringBuilder insertQuery1 = new StringBuilder();
            StringBuilder insertQuery2 = new StringBuilder();

            StringBuilder updateQuery         = new StringBuilder();
            StringBuilder existQuery          = new StringBuilder();
            StringBuilder fetchTouristNoQuery = new StringBuilder();
            bool          gotSomeCells        = false;

            string tablename = xmlMapper.TableName;

            try
            {
                foreach (Row row in rowList)
                {
                    #region Intializing Queries
                    insertQuery1.Remove(0, insertQuery1.Length);
                    insertQuery2.Remove(0, insertQuery2.Length);
                    updateQuery.Remove(0, updateQuery.Length);
                    existQuery.Remove(0, existQuery.Length);
                    fetchTouristNoQuery.Remove(0, fetchTouristNoQuery.Length);

                    insertQuery1        = new StringBuilder();
                    insertQuery2        = new StringBuilder();
                    updateQuery         = new StringBuilder();
                    existQuery          = new StringBuilder();
                    fetchTouristNoQuery = new StringBuilder();


                    insertQuery1.Append("insert into " + tablename + "(");
                    insertQuery2.Append(" values (");
                    updateQuery.Append("update " + tablename + " set ");
                    existQuery.Append("select * from " + tablename + " where 1=1");
                    #endregion

                    foreach (ReadPattern readPattern in xmlMapper.ReadPatternList)
                    {
                        #region ColumnWise
                        if (readPattern.ReadPatternValue == "columnwise")
                        {
                            foreach (XMLFieldMapper xmlFieldMapper in readPattern.XmlFieldMapperList)
                            {
                                Cells cell = row.CellList.Find(delegate(Cells c) { return(c.ColNum == xmlFieldMapper.ExcelColumn); });
                                if (cell != null)
                                {
                                    #region Formatting to Date if it date field
                                    if (xmlFieldMapper.IsDateField)
                                    {
                                        DateTime dt;
                                        DateTime.TryParse(cell.CellValue, out dt);
                                        if (dt != DateTime.MinValue)
                                        {
                                            cell.CellValue = dt.Year.ToString("0000") + "-" + dt.Month.ToString("00") + "-" + dt.Day.ToString("00");
                                        }
                                        else
                                        {
                                            cell.CellValue = "null";
                                        }
                                    }
                                    #endregion

                                    #region Get Master Id Field
                                    if (xmlFieldMapper.MasterLookUpTable.Trim() != string.Empty)
                                    {
                                        cell.CellValue = getValueFromMaster(xmlFieldMapper.MasterLookUpTable, xmlFieldMapper.MasterIdField, xmlFieldMapper.MasterLookUpField, cell.CellValue);
                                    }
                                    #endregion

                                    #region Preparing the Exist Query
                                    if (xmlFieldMapper.IsIdField)
                                    {
                                        if (cell.CellValue == "null")
                                        {
                                            existQuery.Append(" and " + xmlFieldMapper.DbField + " = " + cell.CellValue);
                                        }
                                        else if (xmlFieldMapper.IsAlpha)
                                        {
                                            if (xmlFieldMapper.IsDateField)
                                            {
                                                string newVal = "cast('" + cell.CellValue + "' as DateTime)";
                                                existQuery.Append(" and " + xmlFieldMapper.DbField + " = " + newVal);
                                            }
                                            else
                                            {
                                                existQuery.Append(" and " + xmlFieldMapper.DbField + " = '" + cell.CellValue + "'");
                                            }
                                        }
                                        else
                                        {
                                            existQuery.Append(" and " + xmlFieldMapper.DbField + " = " + cell.CellValue);
                                        }
                                    }
                                    #endregion

                                    #region Preparing the Fetch Tourist No. Query
                                    if (xmlFieldMapper.FetchTouristNo)
                                    {
                                        if (cell.CellValue == "null")
                                        {
                                            fetchTouristNoQuery.Append(" and " + xmlFieldMapper.DbField + " = " + cell.CellValue);
                                        }
                                        else if (xmlFieldMapper.IsAlpha)
                                        {
                                            fetchTouristNoQuery.Append(" and " + xmlFieldMapper.DbField + " = '" + cell.CellValue + "'");
                                        }
                                        else
                                        {
                                            fetchTouristNoQuery.Append(" and " + xmlFieldMapper.DbField + " = " + cell.CellValue);
                                        }
                                    }
                                    #endregion

                                    insertQuery1.Append(xmlFieldMapper.DbField + ", ");

                                    #region Adding '', if field is Alpha
                                    if (xmlFieldMapper.IsAlpha)
                                    {
                                        if (cell.CellValue == "null")
                                        {
                                            insertQuery2.Append(cell.CellValue + ", ");
                                            updateQuery.Append(xmlFieldMapper.DbField + " = " + cell.CellValue + ", ");
                                        }
                                        else
                                        {
                                            string val = xmlFieldMapper.Encrypt ? DataSecurityManager.Encrypt(cell.CellValue) : cell.CellValue;
                                            insertQuery2.Append("'" + val + "', ");
                                            updateQuery.Append(xmlFieldMapper.DbField + " = '" + val + "', ");
                                        }
                                    }
                                    else
                                    {
                                        insertQuery2.Append(cell.CellValue + ", ");
                                        updateQuery.Append(xmlFieldMapper.DbField + " = " + cell.CellValue + ", ");
                                    }
                                    #endregion

                                    gotSomeCells = true;
                                }
                            }
                        }
                        #endregion

                        #region RowWise
                        if (readPattern.ReadPatternValue == "rowwise")
                        {
                            string prevValue = string.Empty;
                            foreach (Row columnarRow in rowList)
                            {
                                XMLFieldMapper xmlFieldMapper = readPattern.XmlFieldMapperList.Find(delegate(XMLFieldMapper fm) { return(fm.ExcelRow == columnarRow.RowNum); });
                                if (xmlFieldMapper != null)
                                {
                                    Cells cell = columnarRow.CellList.Find(delegate(Cells c) { return(c.ColNum == xmlFieldMapper.ExcelColumn); });
                                    if (cell != null)
                                    {
                                        #region Formatting to Date if it is date field
                                        if (xmlFieldMapper.IsDateField)
                                        {
                                            DateTime dt;
                                            DateTime.TryParse(cell.CellValue, out dt);
                                            if (dt != DateTime.MinValue)
                                            {
                                                cell.CellValue = dt.Year.ToString("0000") + "-" + dt.Month.ToString("00") + "-" + dt.Day.ToString("00");
                                            }
                                            else
                                            {
                                                cell.CellValue = "null";
                                            }
                                        }
                                        #endregion

                                        #region Formatting to Date if it is Time field
                                        if (xmlFieldMapper.TimeField)
                                        {
                                            cell.CellValue = cell.CellValue.Replace("HRS", "").Trim();

                                            DateTime dt;
                                            DateTime.TryParse(cell.CellValue, out dt);
                                            cell.CellValue = dt.ToShortTimeString();

                                            //if (cell.CellValue.Length == 4 && cell.CellValue != "null")
                                            //{
                                            //    string hh = cell.CellValue.Substring(0, 2);
                                            //    string mm = cell.CellValue.Substring(2);
                                            //    cell.CellValue = hh + ":" + mm;
                                            //}
                                            //else
                                            //    cell.CellValue = "null";
                                        }
                                        #endregion

                                        #region Formatting to Bool if it is Boolean field
                                        if (xmlFieldMapper.IsBool)
                                        {
                                            if (cell.CellValue.StartsWith("Y"))
                                            {
                                                cell.CellValue = "1";
                                            }
                                            else if (cell.CellValue.StartsWith("N"))
                                            {
                                                cell.CellValue = "0";
                                            }
                                        }
                                        #endregion

                                        #region Get Master Id Field
                                        if (xmlFieldMapper.MasterLookUpTable.Trim() != string.Empty)
                                        {
                                            cell.CellValue = getValueFromMaster(xmlFieldMapper.MasterLookUpTable, xmlFieldMapper.MasterIdField, xmlFieldMapper.MasterLookUpField, cell.CellValue);
                                        }
                                        #endregion

                                        #region Preparing the Exist Query
                                        if (xmlFieldMapper.IsIdField)
                                        {
                                            if (cell.CellValue == "null")
                                            {
                                                existQuery.Append(" and " + xmlFieldMapper.DbField + " = " + cell.CellValue);
                                            }
                                            else if (xmlFieldMapper.IsAlpha)
                                            {
                                                existQuery.Append(" and " + xmlFieldMapper.DbField + " = '" + cell.CellValue + "'");
                                            }
                                            else
                                            {
                                                existQuery.Append(" and " + xmlFieldMapper.DbField + " = " + cell.CellValue);
                                            }
                                        }
                                        #endregion

                                        if (!xmlFieldMapper.JoinToPrev)
                                        {
                                            insertQuery1.Append(xmlFieldMapper.DbField + ", ");
                                            #region Adding '', if field is Alpha
                                            if (xmlFieldMapper.IsAlpha)
                                            {
                                                if (cell.CellValue == "null")
                                                {
                                                    insertQuery2.Append(cell.CellValue + ", ");
                                                    updateQuery.Append(xmlFieldMapper.DbField + " = " + cell.CellValue + ", ");
                                                }
                                                else
                                                {
                                                    insertQuery2.Append("'" + cell.CellValue + "', ");
                                                    updateQuery.Append(xmlFieldMapper.DbField + " = '" + cell.CellValue + "', ");
                                                }
                                            }
                                            else
                                            {
                                                insertQuery2.Append(cell.CellValue + ", ");
                                                updateQuery.Append(xmlFieldMapper.DbField + " = " + cell.CellValue + ", ");
                                            }
                                            #endregion
                                        }
                                        else
                                        {
                                            if (prevValue != "null" && cell.CellValue != "null")
                                            {
                                                int lastindex = insertQuery2.ToString().LastIndexOf("'");
                                                insertQuery2.Insert(lastindex, " " + cell.CellValue);

                                                lastindex = updateQuery.ToString().LastIndexOf("'");
                                                updateQuery.Insert(lastindex, " " + cell.CellValue);
                                            }
                                        }

                                        prevValue    = cell.CellValue;
                                        gotSomeCells = true;
                                    }
                                }
                            }
                        }
                        #endregion
                    }

                    #region DB OPerations
                    if (gotSomeCells)
                    {
                        #region Get Tourist No.
                        fetchTouristNoQuery.Append(" and bookingId = " + BookingId.ToString());
                        int TouristNo = getTouristNo(fetchTouristNoQuery.ToString());

                        existQuery.Append(" and bookingId = " + BookingId.ToString());

                        insertQuery1.Append("TouristNo, ");
                        insertQuery2.Append(TouristNo.ToString() + ",");
                        updateQuery.Append("TouristNo = " + TouristNo.ToString());

                        #endregion

                        #region Adding Booking Id
                        insertQuery1.Append("BookingId) ");
                        insertQuery2.Append(BookingId.ToString() + ")");

                        if (updateQuery.ToString().EndsWith(","))
                        {
                            updateQuery = updateQuery.Remove(updateQuery.ToString().Trim().LastIndexOf(','), 1);
                        }

                        //updateQuery = updateQuery.Remove(updateQuery.Length - 1, 1);
                        updateQuery.Append(" where BookingId = " + BookingId.ToString());
                        updateQuery.Append(" and TouristNo = " + TouristNo.ToString());
                        #endregion

                        insertQuery1.Append(insertQuery2);

                        if (IsExists(existQuery.ToString()))
                        {
                            UpdateRecord(updateQuery.ToString());
                        }
                        else
                        {
                            InsertRecord(insertQuery1.ToString());
                        }
                    }
                    #endregion
                }

                RemoveExtraInsertedRecords(BookingId);
            }
            catch (Exception exp)
            {
                throw exp;
            }
        }