Пример #1
0
        static ScheduleData ExctractScheduleData(string path)
        {
            Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
            var options = new DbfDataReaderOptions
            {
                SkipDeletedRecords = true,
                Encoding           = DbfDataReader.EncodingProvider.GetEncoding(852)
            };

            var list = new List <ScheduleEntry>();

            using (var dbfDataReader = new DbfDataReader.DbfDataReader(path, options))
            {
                while (dbfDataReader.Read())
                {
                    var entry = new ScheduleEntry {
                        Day              = dbfDataReader.GetString(0),
                        Hour             = dbfDataReader.GetString(1),
                        Class            = dbfDataReader.GetString(3),
                        Group            = dbfDataReader.GetString(4),
                        SubjectShortcut  = dbfDataReader.GetString(5),
                        Subject          = dbfDataReader.GetString(6),
                        TeacherFirstName = dbfDataReader.GetString(9),
                        TeacherLastName  = dbfDataReader.GetString(8),
                        SpaceName        = dbfDataReader.GetString(10)
                    };
                    list.Add(entry);
                }
            }

            return(new ScheduleData
            {
                ScheduleEntries = list
            });
        }
Пример #2
0
        private static void DoBulkCopy(SqlConnection connection, Options options)
        {
            Console.WriteLine("Begin bulk copy");

            var stopwatch = new Stopwatch();

            stopwatch.Start();

            var rowsCopied           = 0L;
            var dbfRecordCount       = 0L;
            var dbfDataReaderOptions = new DbfDataReaderOptions
            {
                SkipDeletedRecords = options.SkipDeletedRecords
            };

            using (var dbfDataReader = new DbfDataReader.DbfDataReader(options.Dbf, dbfDataReaderOptions))
            {
                dbfRecordCount = dbfDataReader.DbfTable.Header.RecordCount;

                using (var bulkCopy = new SqlBulkCopy(connection))
                {
                    bulkCopy.BulkCopyTimeout      = options.BulkCopyTimeout;
                    bulkCopy.DestinationTableName = options.Table;

                    try
                    {
                        bulkCopy.WriteToServer(dbfDataReader);
                        rowsCopied = bulkCopy.RowsCopied();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"Error importing: dbf file: '{options.Dbf}', exception: {ex.Message}");
                    }
                }
            }

            stopwatch.Stop();
            Console.WriteLine($"Bulk copy completed in {GetElapsedTime(stopwatch)}s");
            Console.WriteLine($"Copied {rowsCopied} of {dbfRecordCount} rows");
        }
Пример #3
0
        public void LoadKeys(bool Truncate = false)
        {
            string[] zips = Directory.GetFiles(Options.FileDirectory, "*.zip");

            SqlConnection con = Options.MakeConnection();

            SqlBulkCopy bulk = new SqlBulkCopy(con);

            bulk.DestinationTableName = "[ext].[" + Options.TableName + "Keys]";
            bulk.ColumnMappings.Add("key", "key");
            bulk.ColumnMappings.Add("FileSourceId", "FileSourceId");

            con.Open();

            InsertFileSource.Connection = con;

            if (Truncate)
            {
                TruncExt.Connection = con;
                TruncExt.ExecuteNonQuery();
            }

            foreach (string zf in zips)
            {
                Console.WriteLine("Processing: " + Path.GetFileName(zf));
                FileInfo f = new FileInfo(zf);
                InsertFileSource.Parameters["@filename"].Value = zf;
                InsertFileSource.Parameters["@filesize"].Value = f.Length;

                InsertFileSource.ExecuteNonQuery();
                string     dbfname = "";
                ZipArchive za      = ZipFile.OpenRead(zf);

                foreach (ZipArchiveEntry ze in za.Entries)
                {
                    if (ze.FullName.EndsWith(".dbf"))
                    {
                        dbfname = ze.FullName;
                        ze.ExtractToFile("local.dbf", true);
                    }
                }

                parent.currentDBFName = dbfname;

                if (parent.ResetKeyFields != null)
                {
                    parent.ResetKeyFields();
                }

                DbfDataReader.DbfDataReader db = new DbfDataReader.DbfDataReader("local.dbf", new DbfDataReaderOptions()
                {
                    SkipDeletedRecords = true
                });

                while (db.Read())
                {
                    object key = Options.DerivedResumeKey ? parent.DerivedKeyGenerator(db, parent) : db[Options.DbaseResumeId];

                    if (KeyValues.Columns.Count == 1)
                    {
                        DataColumn keyfield  = new DataColumn("keyid", key.GetType());
                        DataColumn filefield = KeyValues.Columns[0];

                        // fail, udt types do not automap to the field ordering in the table. though named the fing same.
                        KeyValues.Columns.Clear();
                        KeyValues.Columns.Add(keyfield);
                        KeyValues.Columns.Add(filefield);
                    }

                    DataRow dr = KeyValues.NewRow();

                    dr["keyid"]        = key;
                    dr["FileSourceId"] = dbfname;


                    KeyValues.Rows.Add(dr);
                }

                db.Close();

                Console.WriteLine("Loaded " + KeyValues.Rows.Count.ToString() + " keys");

                SqlCommand getloaded = new SqlCommand("[ext]." + Options.TableName + "_GetLoaded", con);
                getloaded.CommandType = CommandType.StoredProcedure;


                SqlParameter parm = getloaded.Parameters.AddWithValue("@keysToCheck", KeyValues);

                parm.SqlDbType = SqlDbType.Structured;
                //     parm.TypeName = "[ext]." + Options.TableName + "KeyTableType";

                // replaced sqlbulkcopy with table-valued parameter accepting stored procedure
                // seems to work just as fast and the subquery calculates which records were loaded rather quickly.
                getloaded.ExecuteNonQuery();

                //bulk.WriteToServer(KeyValues);
                Console.WriteLine("Wrote to server");

                KeyValues.Rows.Clear();
            }


            con.Close();
        }
Пример #4
0
        public void LoadZips()
        {
            string summaryfilename = Options.TableName + " Load Summary.txt";

            if (Options.WriteSummaryFile)
            {
                // filename
                // written records
                // skipped records
                // time processing
                // time writing to server
                // records processed / s
                // records written / s
                File.WriteAllText(summaryfilename,
                                  "Input File\tRecord Count\tWrote Records\tSkipped Existing\tTime Processing\tTime Writing To Server\tRetries\tRecords Proc/s\tRecords Wrote/s\n");
            }

            if (Options.ConsoleLogging)
            {
                Console.WriteLine("Processing Table " + Options.TableName);
            }

            #region FilesAndDirectories
            var zipfiles  = Directory.GetFiles(Options.FileDirectory, "*.zip");
            var outputdir = Options.FileDirectory + "\\" + Options.TempDirectoryName;

            if (Directory.Exists(outputdir))
            {
                Directory.Delete(outputdir, true);
            }

            Directory.CreateDirectory(outputdir);
            #endregion FilesAndDirectories

            #region InitialSQL
            // Console.WriteLine("Opening SQL Connection.");

            SqlConnection scon = new SqlConnection(Options.ConnectionString);
            scon.Open();

            if (Options.EmptyTable)
            {
                if (Options.ConsoleLogging)
                {
                    Console.WriteLine("Emptying table of records.");
                }
                SqlCommand scom = new SqlCommand("truncate table dbo." + Options.TableName, scon);
                scom.ExecuteNonQuery();
            }


            List <object> resumeids = new List <object>();

            if (Options.Resume)
            {
                if (Options.ConsoleLogging)
                {
                    Console.WriteLine("Retrieving resume ids. Field " + Options.SqlResumeId + " selected.");
                }
                SqlCommand getresumeids = new SqlCommand("select " + (Options.DerivedSqlKey? Options.DerivedSqlClause: Options.SqlResumeId) + " from dbo." + Options.TableName + " order by " + Options.SqlResumeId, scon);

                var ir = getresumeids.ExecuteReader();

                while (ir.Read())
                {
                    resumeids.Add(ir[Options.SqlResumeId]);
                }

                ir.Close();
            }

            //Console.WriteLine("Closing SQL Connection");

            scon.Close();

            #endregion InitialSQL


            #region ProcessZipFiles

            bool checkresume = resumeids.Count > 0;

            // wanna make me happy stop raping and selling kids
            // and dont make me think things like all women especially workers are f*****g chomos

            foreach (string z in zipfiles)
            {
                currentZipFileName = z;

                if (Options.ConsoleLogging)
                {
                    Console.WriteLine("Extracting contents of archive " + Path.GetFileName(z));
                }

                // extract zipfile contents
                ZipFile.ExtractToDirectory(z, outputdir);

                // retrieve shpfile and dbf file
                currentDBFName = Directory.GetFiles(outputdir, "*.dbf").First();
                currentSHPName = Directory.GetFiles(outputdir, "*.shp").First();

                DbfDataReader.DbfDataReaderOptions ops = new DbfDataReaderOptions()
                {
                    SkipDeletedRecords = true
                };
                DbfDataReader.DbfDataReader dr = new DbfDataReader.DbfDataReader(currentDBFName, ops);

                ShapeUtilities.ShapeFile sfile = null;

                if (Options.LoadShapeFile)
                {
                    if (Options.ConsoleLogging)
                    {
                        Console.WriteLine("Loading shapefile " + Path.GetFileName(currentSHPName));
                    }

                    sfile = new ShapeUtilities.ShapeFile(currentSHPName);
                    sfile.Load();
                }

                sindex = 0;

                // report length back to implementer.
                OnLength(this, currentDBFName, currentSHPName, dr.DbfTable.Header.RecordCount);


                while (dr.Read())
                {
                    DateTime startproc = DateTime.Now;
                    DateTime endproc;

                    IRecordLoader i = GetNewRecord();
                    i.Read(dr);

                    // interesting... c# update anyone ?
                    ShapeUtilities.BaseShapeRecord currshape = sfile?.Records[sindex].Record;

                    if (checkresume)
                    {
                        object key = Options.DerivedResumeKey ? DerivedKeyGenerator(dr, this) : dr[Options.DbaseResumeId];

                        if (resumeids.Contains(key))
                        {
                            resumeids.Remove(dr[Options.DbaseResumeId]);
                            checkresume = resumeids.Count > 0;
                            SkipRecord(this, sindex, i, currshape);
                            skippedrecords++;
                            sindex++;
                            endproc = DateTime.Now;
                            totalsecondsprocessing += startproc.Subtract(endproc).TotalSeconds;

                            continue;
                        }
                    }

                    // allow user specfied code to run which performs actions on
                    // the loaded record
                    ProcessRecord(this, sindex, i, currshape);
                    endproc = DateTime.Now;

                    totalsecondsprocessing += startproc.Subtract(endproc).TotalSeconds;

                    towrite.Add(i);

                    Status(sindex, wrote, 0, totalsecondswriting == 0 ? 0 : wrote / totalsecondswriting);

                    if (Options.RecordLimit == towrite.Count)
                    {
                        DoTableWrite();
                    }


                    sindex++;
                }

                dr.Close();

                if (towrite.Count > 0)
                {
                    DoTableWrite();
                }

                Directory.Delete(outputdir, true);

                recordsprocpersecond = (wrote + skippedrecords) / totalsecondsprocessing;

                if (Options.WriteSummaryFile)
                {
                    // filename
                    // written records
                    // skipped records
                    // time processing
                    // time writing to server
                    // records processed / s
                    // records written / s
                    File.AppendAllText(summaryfilename,
                                       Path.GetFileName(z) + "\t" +
                                       wrote.ToString() + "\t" +
                                       skippedrecords.ToString() + "\t" +
                                       TimeSpan.FromSeconds(totalsecondsprocessing).ToString() + "\t" +
                                       TimeSpan.FromSeconds(totalsecondswriting).ToString() + "\t" +
                                       recordsprocpersecond.ToString() + "\t" +
                                       recordwrotepersecond.ToString() + "\t\n");
                }


                ReportFinalStats(wrote, skippedrecords, totalsecondswriting, recordwrotepersecond);

                BatchRecordsSkipped    += skippedrecords;
                BatchRecordsWrote      += wrote;
                BatchRetries           += retries;
                BatchSecondsProcessing += totalsecondsprocessing;
                BatchSecondsWriting    += totalsecondswriting;


                retries = 0;
                totalsecondsprocessing = 0;
                wrote = 0;
                totalsecondswriting  = 0;
                skippedrecords       = 0;
                recordwrotepersecond = 0;
            }

            #endregion ProcessZipFiles

            BatchRecordsWrotePerSecond = BatchRecordsWrote / BatchSecondsWriting;
            BatchRecordsProcsPerSecond = (BatchRecordsSkipped + BatchRecordsWrote) / BatchSecondsProcessing;



            if (Options.WriteSummaryFile)
            {
                // filename
                // written records
                // skipped records
                // time processing
                // time writing to server
                // records processed / s
                // records written / s
                File.AppendAllText(summaryfilename,
                                   "Batch Totals\t" +
                                   BatchRecordsWrote.ToString() + "\t" +
                                   BatchRecordsSkipped.ToString() + "\t" +
                                   TimeSpan.FromSeconds(BatchSecondsProcessing).ToString() + "\t" +
                                   TimeSpan.FromSeconds(BatchSecondsWriting).ToString() + "\t" +
                                   recordsprocpersecond.ToString() + "\t" +
                                   recordwrotepersecond.ToString() + "\n");
            }

            // let senoir chomo cripple get f****d up some more, getting a little sick of seeing people stare at children.
            // f**k them for now.
            // scon =  Options.MakeConnection();

            scon.Open();
            SqlCommand getcount = new SqlCommand("select count(*) from dbo." + Options.TableName, scon);

            int sqlrecords = getcount.ExecuteNonQuery();

            if (sqlrecords == BatchRecordsSkipped + BatchRecordsWrote)
            {
                Console.WriteLine("Record number in table matches progress thus far. A total of " + sqlrecords.ToString() + " discovered.");
            }
            else
            {
                Console.WriteLine("Differing counts");
                Console.WriteLine("Server returned count: " + sqlrecords.ToString());
                Console.WriteLine("Skipped + Processed: " + (BatchRecordsWrote + BatchRecordsSkipped).ToString());
            }

            scon.Close();

            // whats going to be really funny is when i bury all snarky messages
            // by changing single lines
            // or deleting and burying these comments so github cant pretend its not complicit in burying things.
            // hey heres an idea, how about you people tap the government that is intentionally delaying the world from being a better place
            // and enabling chomo fags like all of you, to in some way improve the way it monetizes public services like cloud storage
            // for honest citizens and keep these fags from doing business ? by say taking services that were rebooted
            // and swapping photos out etc.
        }
        public async Task <List <LabFileUploadErrorMessageDTO> > ValidateLabFileAsync(IFileListEntry fileEntry, MappingDataDTO MappingTemplate)
        {
            var ErrorMessage = new List <LabFileUploadErrorMessageDTO>();
            int row          = 1;

            try
            {
                string path = "";
                List <ParameterDTO> objParamList = new List <ParameterDTO>();
                var searchModel = new ParameterDTO()
                {
                    prm_code_major = "UPLOAD_PATH"
                };

                objParamList = await _apiHelper.GetDataListByModelAsync <ParameterDTO, ParameterDTO>("dropdownlist_api/GetParameterList", searchModel);

                if (objParamList.FirstOrDefault(x => x.prm_code_minor == "PATH") != null)
                {
                    path = objParamList.FirstOrDefault(x => x.prm_code_minor == "PATH").prm_value;
                }
                else
                {
                    ErrorMessage.Add(new LabFileUploadErrorMessageDTO
                    {
                        lfu_status      = 'E',
                        lfu_Err_type    = 'E',
                        lfu_Err_no      = 1,
                        lfu_Err_Column  = "",
                        lfu_Err_Message = "ไม่พบ Config PATH กรุณาติดต่อผู้ดูแลระบบ "
                    });

                    return(ErrorMessage);
                }



                string str_CurrentDate = DateTime.Now.ToString("yyyyMMdd");

                path = Path.Combine(path, str_CurrentDate, MappingTemplate.mp_hos_code);
                bool exists = System.IO.Directory.Exists(path);

                if (!exists)
                {
                    System.IO.Directory.CreateDirectory(path);
                }

                path = Path.Combine(path, fileEntry.Name);

                using (FileStream file = new FileStream(path, FileMode.Create))
                {
                    try
                    {
                        await fileEntry.Data.CopyToAsync(file);
                    }
                    catch (Exception ex)
                    {
                    }
                    finally
                    {
                        file.Flush();
                    }
                }


                WHONetMappingSearch searchWHONet = new WHONetMappingSearch();
                searchWHONet.wnm_mappingid = MappingTemplate.mp_id;
                searchWHONet.wnm_mst_code  = MappingTemplate.mp_mst_code;

                List <WHONetMappingListsDTO> WHONetColumn = await _apiHelper.GetDataListByModelAsync <WHONetMappingListsDTO, WHONetMappingSearch>("mapping_api/Get_WHONetMappingListByModel", searchWHONet);

                var WHONetColumnMandatory = WHONetColumn.Where(x => x.wnm_mandatory == true);
                #region ReadExcel
                if (Path.GetExtension(fileEntry.Name) == ".xls" || Path.GetExtension(fileEntry.Name) == ".xlsx")
                {
                    ExcelDataSetConfiguration option = new ExcelDataSetConfiguration();

                    using (var stream = File.Open(path, FileMode.Open, FileAccess.Read))
                    {
                        using (var reader = ExcelReaderFactory.CreateReader(stream))
                        {
                            DataSet result = new DataSet();

                            //First row is header
                            if (MappingTemplate.mp_firstlineisheader == true)
                            {
                                result = reader.AsDataSet(new ExcelDataSetConfiguration()
                                {
                                    ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
                                    {
                                        UseHeaderRow = true
                                    }
                                }
                                                          );
                            }
                            else
                            {
                                result = reader.AsDataSet();
                            }

                            ErrorMessage.Add(new LabFileUploadErrorMessageDTO
                            {
                                lfu_status      = 'I',
                                lfu_Err_type    = 'I',
                                lfu_Err_no      = 1,
                                lfu_Err_Column  = "Total",
                                lfu_Err_Message = result.Tables[0].Rows.Count.ToString()
                            });

                            foreach (WHONetMappingListsDTO item in WHONetColumnMandatory)
                            {
                                var wnm_originalfield = item.wnm_originalfield;
                                if (MappingTemplate.mp_firstlineisheader == false)
                                {
                                    int index = 0;
                                    Int32.TryParse(item.wnm_originalfield.Replace("Column", ""), out index);

                                    item.wnm_originalfield = "Column" + (index - 1);
                                }

                                Boolean columnExists = result.Tables[0].Columns.Contains(item.wnm_originalfield);
                                if (columnExists == false)
                                {
                                    ErrorMessage.Add(new LabFileUploadErrorMessageDTO
                                    {
                                        lfu_status      = 'E',
                                        lfu_Err_type    = 'C',
                                        lfu_Err_no      = 1,
                                        lfu_Err_Column  = wnm_originalfield,
                                        lfu_Err_Message = "ไม่พบ Column " + wnm_originalfield
                                    });
                                }

                                if (columnExists)
                                {
                                    var chkResult = result.Tables[0].Select("[" + item.wnm_originalfield + "]" + " is null");

                                    if (chkResult.Length > 0)
                                    {
                                        ErrorMessage.Add(new LabFileUploadErrorMessageDTO
                                        {
                                            lfu_status      = 'E',
                                            lfu_Err_type    = 'N',
                                            lfu_Err_no      = 1,
                                            lfu_Err_Column  = wnm_originalfield,
                                            lfu_Err_Message = "กรุณาตรวจสอบข้อมูล Column " + wnm_originalfield + " จะต้องไม่เท่ากับค่าว่าง"
                                        });
                                    }

                                    //if (item.wnm_type == "Date")
                                    //{
                                    //    var FieldDateType = result.Tables[0].Columns[item.wnm_originalfield].DataType.ToString();

                                    //    if(FieldDateType == "System.String")
                                    //    {
                                    //        try
                                    //        {

                                    //        }
                                    //        catch()
                                    //        {

                                    //        }

                                    //    }
                                    //}
                                }
                            }

                            var x = ErrorMessage;
                        }
                    }
                }
                #endregion
                #region ReadCSV
                else if (Path.GetExtension(fileEntry.Name) == ".csv")
                {
                    using (var stream = File.Open(path, FileMode.Open, FileAccess.Read))
                    {
                        var reader = ExcelReaderFactory.CreateCsvReader(stream, new ExcelReaderConfiguration()
                        {
                            FallbackEncoding      = Encoding.GetEncoding(1252),
                            AutodetectSeparators  = new char[] { ',', ';', '\t', '|', '#' },
                            LeaveOpen             = false,
                            AnalyzeInitialCsvRows = 0,
                        });

                        DataSet result = new DataSet();

                        ErrorMessage.Add(new LabFileUploadErrorMessageDTO
                        {
                            lfu_status      = 'I',
                            lfu_Err_type    = 'I',
                            lfu_Err_no      = 1,
                            lfu_Err_Column  = "Total",
                            lfu_Err_Message = result.Tables[0].Rows.Count.ToString()
                        });
                        //First row is header
                        if (MappingTemplate.mp_firstlineisheader == true)
                        {
                            result = reader.AsDataSet(new ExcelDataSetConfiguration()
                            {
                                ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
                                {
                                    UseHeaderRow = true
                                }
                            }
                                                      );
                        }
                        else
                        {
                            result = reader.AsDataSet();
                        }

                        var ee = result.Tables[0];
                        foreach (WHONetMappingListsDTO item in WHONetColumnMandatory)
                        {
                            var wnm_originalfield = item.wnm_originalfield;
                            if (MappingTemplate.mp_firstlineisheader == false)
                            {
                                int index = 0;
                                Int32.TryParse(item.wnm_originalfield.Replace("Column", ""), out index);

                                item.wnm_originalfield = "Column" + (index - 1);
                            }

                            Boolean columnExists = result.Tables[0].Columns.Contains(item.wnm_originalfield);
                            if (columnExists == false)
                            {
                                ErrorMessage.Add(new LabFileUploadErrorMessageDTO
                                {
                                    lfu_status      = 'E',
                                    lfu_Err_type    = 'C',
                                    lfu_Err_no      = 1,
                                    lfu_Err_Column  = wnm_originalfield,
                                    lfu_Err_Message = "ไม่พบ Column " + wnm_originalfield
                                });
                            }

                            if (columnExists)
                            {
                                var chkResult = result.Tables[0].Select(item.wnm_originalfield + " = ''");

                                if (chkResult.Length > 0)
                                {
                                    ErrorMessage.Add(new LabFileUploadErrorMessageDTO
                                    {
                                        lfu_status      = 'E',
                                        lfu_Err_type    = 'N',
                                        lfu_Err_no      = 1,
                                        lfu_Err_Column  = wnm_originalfield,
                                        lfu_Err_Message = "กรุณาตรวจสอบข้อมูล Column " + wnm_originalfield + " จะต้องไม่เท่ากับค่าว่าง"
                                    });
                                }
                            }
                        }

                        var x = ErrorMessage;
                    }
                }
                #endregion
                #region ReadText
                else if (Path.GetExtension(fileEntry.Name) == ".txt")
                {
                    string    line;
                    DataTable dt           = new DataTable();
                    string    tempFilename = Guid.NewGuid().ToString() + ".txt";
                    //var path = Path.Combine(@"D:\Work\02-DMSC ALISS\TEMP\", tempFilename);
                    //var ms = new MemoryStream();
                    //await fileEntry.Data.CopyToAsync(ms);
                    //using (FileStream file = new FileStream(path, FileMode.Create, FileAccess.Write))
                    //{
                    //    ms.WriteTo(file);
                    //}

                    //using (TextReader tr = File.OpenText(@"D:\Work\02-DMSC ALISS\TEMP\" + tempFilename))
                    using (TextReader tr = File.OpenText(path))
                    {
                        while ((line = tr.ReadLine()) != null)
                        {
                            string[] items = line.Split('\t');

                            if (dt.Columns.Count == 0)
                            {
                                if (MappingTemplate.mp_firstlineisheader == false)
                                {
                                    for (int i = 0; i < items.Length; i++)
                                    {
                                        dt.Columns.Add(new DataColumn("Column" + i, typeof(string)));
                                    }
                                }
                                else
                                {
                                    for (int i = 0; i < items.Length; i++)
                                    {
                                        dt.Columns.Add(new DataColumn(items[i].ToString(), typeof(string)));
                                    }
                                }
                            }
                            dt.Rows.Add(items);
                        }
                    }

                    File.Delete(path);
                    ErrorMessage.Add(new LabFileUploadErrorMessageDTO
                    {
                        lfu_status      = 'I',
                        lfu_Err_type    = 'I',
                        lfu_Err_no      = 1,
                        lfu_Err_Column  = "Total",
                        lfu_Err_Message = (dt.Rows.Count - 1).ToString()
                    });

                    foreach (WHONetMappingListsDTO item in WHONetColumnMandatory)
                    {
                        var wnm_originalfield = item.wnm_originalfield;
                        if (MappingTemplate.mp_firstlineisheader == false)
                        {
                            int index = 0;
                            Int32.TryParse(item.wnm_originalfield.Replace("Column", ""), out index);

                            item.wnm_originalfield = "Column" + (index - 1);
                        }

                        Boolean columnExists = dt.Columns.Contains(item.wnm_originalfield);
                        if (columnExists == false)
                        {
                            ErrorMessage.Add(new LabFileUploadErrorMessageDTO
                            {
                                lfu_status      = 'E',
                                lfu_Err_type    = 'C',
                                lfu_Err_no      = 1,
                                lfu_Err_Column  = wnm_originalfield,
                                lfu_Err_Message = "ไม่พบ Column " + wnm_originalfield
                            });
                        }

                        if (columnExists)
                        {
                            var chkResult = dt.Select(item.wnm_originalfield + " = ''");

                            if (chkResult.Length > 0)
                            {
                                ErrorMessage.Add(new LabFileUploadErrorMessageDTO
                                {
                                    lfu_status      = 'E',
                                    lfu_Err_type    = 'N',
                                    lfu_Err_no      = 1,
                                    lfu_Err_Column  = wnm_originalfield,
                                    lfu_Err_Message = "กรุณาตรวจสอบข้อมูล Column " + wnm_originalfield + " จะต้องไม่เท่ากับค่าว่าง"
                                });
                            }
                        }
                    }
                }
                #endregion
                else
                {
                    string tempFilename = Guid.NewGuid().ToString() + ".dbf";

                    //var path = Path.Combine(@"D:\Work\02-DMSC ALISS\TEMP\", tempFilename);

                    //bool exists = System.IO.Directory.Exists(@"D:\Work\02-DMSC ALISS\TEMP\");

                    //if (!exists)
                    //    System.IO.Directory.CreateDirectory(@"D:\Work\02-DMSC ALISS\TEMP\");

                    //using (FileStream file = new FileStream(path, FileMode.Create))
                    //{
                    //    try
                    //    {
                    //        await fileEntry.Data.CopyToAsync(file);
                    //    }
                    //    catch (Exception ex)
                    //    {

                    //    }
                    //    finally
                    //    {
                    //        file.Flush();
                    //    }
                    //}

                    var options = new DbfDataReaderOptions
                    {
                        Encoding = Encoding.GetEncoding(874)
                    };


                    using (var dbfDataReader = new DbfDataReader.DbfDataReader(path, options))
                    //using (var dbfDataReader = new DbfDataReader.DbfDataReader(@"D:\Work\02-DMSC ALISS\TEMP\" + tempFilename, options))
                    {
                        ErrorMessage.Add(new LabFileUploadErrorMessageDTO
                        {
                            lfu_status      = 'I',
                            lfu_Err_type    = 'I',
                            lfu_Err_no      = 1,
                            lfu_Err_Column  = "Total",
                            lfu_Err_Message = dbfDataReader.DbfTable.Header.RecordCount.ToString()
                        });

                        while (dbfDataReader.Read())
                        {
                            //Validate Mandatory Field
                            foreach (WHONetMappingListsDTO item in WHONetColumnMandatory)
                            {
                                var columnExists = dbfDataReader.DbfTable.Columns.FirstOrDefault(x => x.Name == item.wnm_originalfield);
                                //var ll = dbfDataReader.DbfTable.Rea
                                if (columnExists != null)
                                {
                                    if (dbfDataReader[item.wnm_originalfield] == "" || dbfDataReader[item.wnm_originalfield] == null)
                                    {
                                        if (ErrorMessage.FirstOrDefault(x => x.lfu_Err_type == 'N' && x.lfu_Err_Column == item.wnm_originalfield) == null)
                                        {
                                            ErrorMessage.Add(new LabFileUploadErrorMessageDTO
                                            {
                                                lfu_status      = 'E',
                                                lfu_Err_type    = 'N',
                                                lfu_Err_no      = 1,
                                                lfu_Err_Column  = item.wnm_originalfield,
                                                lfu_Err_Message = "กรุณาตรวจสอบข้อมูล Column " + item.wnm_originalfield + " จะต้องไม่เท่ากับค่าว่าง"
                                            });
                                            var ccc = dbfDataReader[item.wnm_originalfield];
                                        }
                                    }
                                }
                                else
                                {
                                    if (ErrorMessage.FirstOrDefault(x => x.lfu_Err_type == 'C' && x.lfu_Err_Column == item.wnm_originalfield) == null)
                                    {
                                        ErrorMessage.Add(new LabFileUploadErrorMessageDTO
                                        {
                                            lfu_status      = 'E',
                                            lfu_Err_type    = 'C',
                                            lfu_Err_no      = 1,
                                            lfu_Err_Column  = item.wnm_originalfield,
                                            lfu_Err_Message = "ไม่พบ Column " + item.wnm_originalfield
                                        });
                                    }
                                }
                            }
                            row++;
                        }
                        var x = ErrorMessage;

                        //using (FileStream file = new FileStream(@"D:\Work\02-DMSC ALISS\TEMP\"+ fileEntry.Name, FileMode.Create))
                        //{
                        //    try
                        //    {
                        //        await fileEntry.Data.CopyToAsync(file);
                        //    }
                        //    catch (Exception ex)
                        //    {

                        //    }
                        //    finally
                        //    {
                        //        file.Flush();
                        //    }
                        //}


                        //var zipStream = new MemoryStream();


                        //using (var compressedFileStream = new MemoryStream())
                        //{
                        //    //compressedFileStream.Seek(0, SeekOrigin.Begin);
                        //    using (var zipArchive = new ZipArchive(compressedFileStream, ZipArchiveMode.Create, false))
                        //    {
                        //        var zipEntry = zipArchive.CreateEntry(fileEntry.Name);
                        //        using (var zipEntryStream = zipEntry.Open())
                        //        {
                        //            try
                        //            {
                        //                await fileEntry.Data.CopyToAsync(zipEntryStream);
                        //                //fileEntry.Data.CopyTo(zipEntryStream);
                        //            }
                        //            catch (Exception ex)
                        //            {

                        //            }
                        //            finally
                        //            {
                        //                zipEntryStream.Flush();
                        //            }
                        //        }
                        //    }

                        //    using (var fileStream = new FileStream(@"D:\Work\02-DMSC ALISS\TEMP\test.zip", FileMode.Create))
                        //    {
                        //        var bytes = compressedFileStream.GetBuffer();
                        //        fileStream.Write(bytes, 0, bytes.Length);
                        //        try
                        //        {
                        //            await compressedFileStream.CopyToAsync(fileStream);
                        //        }
                        //        catch (Exception ex)
                        //        {

                        //        }
                        //        finally
                        //        {
                        //            fileStream.Flush();
                        //        }
                        //    }
                        //}
                        //using (var outStream = new MemoryStream())
                        //{
                        //    using (var archive = new ZipArchive(outStream, ZipArchiveMode.Create, true))
                        //    {
                        //        var fileInArchive = archive.CreateEntry(fileEntry.Name, CompressionLevel.Optimal);
                        //        using (var entryStream = fileInArchive.Open())

                        //            await fileEntry.Data.CopyToAsync(entryStream);

                        //    }
                        //    using (var fileStream = new FileStream(@"D:\test.zip", FileMode.Create))
                        //    {
                        //        outStream.Seek(0, SeekOrigin.Begin);
                        //        outStream.CopyTo(fileStream);
                        //    }
                        //}
                    }
                    //File.Delete(path);
                }


                var chkError = ErrorMessage.FirstOrDefault(x => x.lfu_status == 'E');
                if (chkError != null)
                {
                    File.Delete(path);
                }
                else
                {
                    ErrorMessage.Add(new LabFileUploadErrorMessageDTO
                    {
                        lfu_status      = 'I',
                        lfu_Err_type    = 'P',
                        lfu_Err_no      = 1,
                        lfu_Err_Column  = "path",
                        lfu_Err_Message = path
                    });
                }
            }
            catch (Exception ex)
            {
            }

            return(ErrorMessage);
        }
Пример #6
0
        static void Main(string[] args)
        {
            DateTime inicio = DateTime.Now;

            string posPath  = ConfigurationManager.AppSettings["posFile"].ToString();
            string prodPath = ConfigurationManager.AppSettings["prodFile"].ToString();

            List <Venta>    ventas    = new List <Venta>();
            List <Producto> productos = new List <Producto>();

            Console.WriteLine(inicio.ToString("HH:mm:ss"));
            int i = 0;

            DbfDataReader.DbfDataReader dbfData = new DbfDataReader.DbfDataReader(posPath);
            // Leer todas las ventas.
            while (dbfData.Read())
            {
                i++;
                string   codigoProd = dbfData.GetValue(4).ToString();
                double   precio     = (double)dbfData.GetValue(10);
                double   importe    = (double)dbfData.GetValue(25);
                DateTime fecha      = DateTime.Parse(dbfData.GetValue(30).ToString());

                ventas.Add(new Venta()
                {
                    codigoProd = codigoProd,
                    precio     = precio,
                    importe    = importe,
                    fecha      = fecha
                });
                Console.WriteLine(" ");

                // Mostrar avances cada 10,000..
                if (i % 10000 == 0)
                {
                    Console.WriteLine(i);
                }
            }
            // leer todos los productos.
            dbfData = new DbfDataReader.DbfDataReader("C:\\workspace\\conectorAdminPAQ\\LATIENDITA\\MGW10005.dbf");
            while (dbfData.Read())
            {
                string id      = dbfData.GetValue(0).ToString();
                string codProd = dbfData.GetValue(1).ToString();
                string nombre  = dbfData.GetValue(2).ToString();
                string precio  = dbfData.GetValue(43).ToString();

                productos.Add(new Producto()
                {
                    id     = id,
                    codigo = codProd,
                    nombre = nombre,
                    precio = precio
                });
            }

            Console.WriteLine(inicio.ToString("HH:mm:ss"));

            List <Venta> result = (from X in ventas where X.fecha > DateTime.Now.AddDays(-50000) && X.importe > 0 select X).ToList();

            DateTime filtro = DateTime.Now;

            List <Venta> ordenada = (from X in result
                                     group X by X.codigoProd into newGroup orderby newGroup.Sum(s => s.importe) descending
                                     select new Venta {
                codigoProd = newGroup.Key, importe = newGroup.Sum(s => s.importe)
            }).Take(200).ToList();


            List <Producto> joinada = (from X in ordenada join p in productos on X.codigoProd equals p.id select new Producto {
                codigo = p.codigo, nombre = p.nombre, precio = p.precio
            }).ToList();

            int pos = 1;

            foreach (Producto producto in joinada)
            {
                Console.WriteLine(producto.codigo + " - " + producto.nombre);
                pos++;
            }

            DateTime finalizacion = DateTime.Now;

            Console.WriteLine($"Proceso terminado. Duracion total {(finalizacion - inicio).TotalSeconds} segundos....");
            Thread.Sleep(160000);
        }