Пример #1
0
        private void InsertIntoDailyFiles(DailyFile dailyFile, string root, int newname)
        {
            dailyFile.FilePath = root + newname + ".xls";

            string con =
                (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dailyFile.FilePath +
                 ";Extended Properties='Excel 12.0 Xml; HDR = YES; IMEX = 1';");

            //string con2 =
            //      (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\File\Bank.xls;Extended Properties='Excel 12.0 Xml; HDR = YES; IMEX = 1';");

            BL bl = new BL(con);

            DataTable dtw = bl.GetTable("select * from [Sheet1$]");



            List <DailyFileDetails> list = new List <DailyFileDetails>();

            foreach (DataRow row in dtw.Rows)
            {
                if (row[6].ToString() != "")
                {
                    int empid = Convert.ToInt32(row[4]);

                    if (Convert.ToDecimal(row[6]) > 0)
                    {
                        var firstOrDefault = _db.Employees.FirstOrDefault(x => x.Code == (empid));
                        if (firstOrDefault != null)
                        {
                            dailyFile.DailyFileDetailses.Add(new DailyFileDetails()
                            {
                                Net         = Convert.ToDecimal(row[6]),
                                EmployeeId  = firstOrDefault.Id,
                                DailyFileId = dailyFile.Id,
                                Employee    = firstOrDefault
                            });
                        }
                    }
                }
            }

            dailyFile.EmployeesNumber = dailyFile.DailyFileDetailses.Count;
            dailyFile.FileTotalAmount = dailyFile.DailyFileDetailses.Sum(x => x.Net);
            _db.DailyFiles.Add(dailyFile);
            _db.SaveChanges();
        }
Пример #2
0
        public IHttpActionResult DeleteEmpInfo(int Id)
        {
            var found  = _db.DailyFileDetailses.Include("DailyFile").Where(x => x.Id == Id).FirstOrDefault();
            var code   = _db.Employees.Find(found.EmployeeId).Code;
            var empnum = _db.DailyFileDetailses.Count(x => x.DailyFileId == found.DailyFileId);

            if (found != null)
            {
                try
                {
                    string path = found.DailyFile.FilePath;
                    string con  =
                        (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path +
                         ";Extended Properties='Excel 12.0 Xml; HDR = YES; IMEX = 1';");



                    BL bl = new BL(con);
                    bl.update(String.Format("update [Sheet1$] set[{1}] =[{1}]-" + found.Net.ToString() + " where  [{0}] = '" + code.ToString() + "'",
                                            "رقم الموظف بجهته الأصلية", "المرتب"));


                    found.DailyFile.FileTotalAmount -= found.Net;
                    found.DailyFile.EmployeesNumber  = empnum - 1;

                    _db.DailyFileDetailses.Remove(found);
                    _db.SaveChanges();


                    return(Ok());
                }
                catch (Exception ex)
                {
                    throw  new Exception(ex.Message);
                    return(Conflict());
                }
            }
            return(Conflict());
        }
        public  IHttpActionResult ExportExcel(int Id)
        {
            _db = new ERPContext();
            var emp = _db.Employees;
            var dailydetails = _db.DailyFileDetailses.Where(x => x.DailyFile.DailyId == Id)
                .GroupBy(p => p.EmployeeId)
                .Select(g => new DailyDetailsInfoVM
                {
                    Id = g.Key,
                    Name = emp.FirstOrDefault(x => x.Id == g.Key).Name,

                    Code = emp.FirstOrDefault(x => x.Id == g.Key).Code,
                    Net = g.Sum(i => i.Net)
                }).ToList();

      

            
            try
            {
                string filePath = HttpContext.Current.Server.MapPath("~/Uploads/SourceFile/output.xls");
              if(!IsFileLocked(new FileInfo(filePath)) 
               && !IsFileLocked(new FileInfo(HttpContext.Current.Server.MapPath("~/Uploads/SourceFile/output2.xls").ToString())))
                {
                    File.Copy(filePath,
                        HttpContext.Current.Server.MapPath("~/Uploads/SourceFile/output2.xls").ToString(), true);
                }
              else
              {
                    return   Ok(new { errorMsg = "الملف مفتوح من قبل برنامج اخر" });


                }
                filePath = HttpContext.Current.Server.MapPath("~/Uploads/SourceFile/output2.xls");
                string filePath2 = HttpContext.Current.Server.MapPath("~/Uploads/SourceFile/ATM.xls");

                BL bl = new BL(@"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + filePath2 + "; Extended Properties = Excel 12.0");
                DataTable dt = new DataTable();

                dt = bl.GetTable("select * from [Sheet1$]");
                dt.PrimaryKey = new DataColumn[] { dt.Columns[4] };
                BL b2 =
                    new BL(@"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + filePath +
                            "; Extended Properties = Excel 12.0");
                DataTable dt2 = new DataTable();



                foreach (var row in dailydetails)
                {
                    //  if(row.Code.ToString()== oSheet.Rows.Find(row.Code))
                    var found = dt.Rows.Find(row.Code).ItemArray;
                    b2.Insert(string.Format("insert into [Sheet1$] values('{0}','{1}','{2}','{3}','{4}','{5}',{6})",
                        found[0], found[1], found[2], found[3], found[4], found[5], row.Net));

                }

                return Ok(new { result = filePath });
            }
            catch (Exception ex)
            {

                return Ok(new {errorMsg = ex.Message});
            }

       
        }
        public IHttpActionResult PostFormData()
        {

            var httpRequest = HttpContext.Current.Request;
            string path = "";
            string filePath = "";


            try
            {
                if (httpRequest.Files.Count > 0)
                {
                    foreach (string file in httpRequest.Files)
                    {
                        long dataSourceId = 1;
                        var postedFile = httpRequest.Files[file];
                        filePath = HttpContext.Current.Server.MapPath("~/Uploads/SourceFile/" + postedFile.FileName);
                        //postedFile.SaveAs(filePath);
                        Stream stream = postedFile.InputStream;
                        byte[] fileData = null;
                        using (var binaryReader = new BinaryReader(postedFile.InputStream))
                        {
                            fileData = binaryReader.ReadBytes(postedFile.ContentLength);
                        }
                        var strrr = new MemoryStream(fileData);
                        var tempfile = Path.GetFileNameWithoutExtension(Path.GetRandomFileName());
                        // Get random file name without 
                        using (var fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                        {
                            // Write content of your memory stream into file stream
                            strrr.WriteTo(fs);
                        }                      
                    }
                }


                string check = "ATM";
                string con =
                    (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml; HDR = YES; IMEX = 1';");

          
                BL bl = new BL(con);

                DataTable dtw = bl.GetTable("select * from [Sheet1$]");
                _db = new ERPContext();
                var employees = _db.Employees.ToList();


               var payingType= dtw.Rows[1].ItemArray[1];
                bool pay = true;
                if (payingType.Equals("3-مرتب تحويلات بنكية"))
                {
                    check = "Bank";
                    pay = false;
                }
                foreach (DataRow row in dtw.Rows)
                {

                 //   Employee emp = (from c in employees where (c.Code == Convert.ToInt32(row[4])) select (c)).FirstOrDefault();
                    int code=0;
                    bool codeparse = int.TryParse(row[4].ToString(), out code);

                    Employee emp = null;
                    if (codeparse)
                    {  emp = _db.Employees.FirstOrDefault(x => x.Code == code);
                    }
                   
                    if (emp != null)

                    {
                        emp.Sallary = pay;
                        emp.Name = row[5].ToString();
                        emp.NationalId = row[0].ToString();

                    }
                    else
                    {
                        Employee empl = new Employee
                        {
                            NationalId = row[0].ToString(),
                            Name = row[5].ToString(),
                            Code = Convert.ToInt32(row[4]),
                            Sallary = pay,
                            Other = true
                        };



                        _db.Employees.Add(empl);
                    }
                  

                          
                }
               _db.SaveChanges();
                _db.Dispose();
                //if (File.Exists(HttpContext.Current.Server.MapPath("~/Uploads/SourceFile/" + check + ".xls")))
                //{
                //    File.Delete(HttpContext.Current.Server.MapPath("~/Uploads/SourceFile/" + check + ".xls"));
                //}

                //File.Move(filePath, HttpContext.Current.Server.MapPath("~/Uploads/SourceFile/" + check + ".xls"));

                return Ok(new { msg = "success" });
            }
            catch (Exception ex)
            {

                return BadRequest(ex.Message);
            }

           
        }
Пример #5
0
        public IHttpActionResult PostFormData()
        {
            var    httpRequest = HttpContext.Current.Request;
            string path        = "";
            string filePath    = "";


            try
            {
                if (httpRequest.Files.Count > 0)
                {
                    foreach (string file in httpRequest.Files)
                    {
                        long dataSourceId = 1;
                        var  postedFile   = httpRequest.Files[file];
                        filePath = HttpContext.Current.Server.MapPath("~/Uploads/SourceFile/" + postedFile.FileName);
                        //postedFile.SaveAs(filePath);
                        Stream stream   = postedFile.InputStream;
                        byte[] fileData = null;
                        using (var binaryReader = new BinaryReader(postedFile.InputStream))
                        {
                            fileData = binaryReader.ReadBytes(postedFile.ContentLength);
                        }
                        var strrr    = new MemoryStream(fileData);
                        var tempfile = Path.GetFileNameWithoutExtension(Path.GetRandomFileName());
                        // Get random file name without
                        using (var fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                        {
                            // Write content of your memory stream into file stream
                            strrr.WriteTo(fs);
                        }
                    }
                }


                string check = "ATM";
                string con   =
                    (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml; HDR = YES; IMEX = 1';");


                BL bl = new BL(con);

                DataTable dtw = bl.GetTable("select * from [Sheet1$]");
                _db = new ERPContext();
                var employees = _db.Employees.ToList();


                var  payingType = dtw.Rows[1].ItemArray[1];
                bool pay        = true;
                if (payingType.Equals("3-مرتب تحويلات بنكية"))
                {
                    check = "Bank";
                    pay   = false;
                }
                foreach (DataRow row in dtw.Rows)
                {
                    //   Employee emp = (from c in employees where (c.Code == Convert.ToInt32(row[4])) select (c)).FirstOrDefault();
                    int  code      = 0;
                    bool codeparse = int.TryParse(row[4].ToString(), out code);

                    Employee emp = null;
                    if (codeparse)
                    {
                        emp = _db.Employees.FirstOrDefault(x => x.Code == code);
                    }

                    if (emp != null)

                    {
                        emp.Sallary    = pay;
                        emp.Name       = row[5].ToString();
                        emp.NationalId = row[0].ToString();
                    }
                    else
                    {
                        Employee empl = new Employee
                        {
                            NationalId = row[0].ToString(),
                            Name       = row[5].ToString(),
                            Code       = Convert.ToInt32(row[4]),
                            Sallary    = pay,
                            Other      = true
                        };



                        _db.Employees.Add(empl);
                    }
                }
                _db.SaveChanges();
                _db.Dispose();
                //if (File.Exists(HttpContext.Current.Server.MapPath("~/Uploads/SourceFile/" + check + ".xls")))
                //{
                //    File.Delete(HttpContext.Current.Server.MapPath("~/Uploads/SourceFile/" + check + ".xls"));
                //}

                //File.Move(filePath, HttpContext.Current.Server.MapPath("~/Uploads/SourceFile/" + check + ".xls"));

                return(Ok(new { msg = "success" }));
            }
            catch (Exception ex)
            {
                return(BadRequest(ex.Message));
            }
        }
Пример #6
0
        public IHttpActionResult UpdateEmpInfo(int DailyFileId, int EmployeeId, decimal Net)
        {
            var dailyfile = _db.DailyFiles.Find(DailyFileId);
            var empnum    = _db.DailyFileDetailses.Count(x => x.DailyFileId == DailyFileId);

            if (dailyfile != null)
            {
                dailyfile.DailyFileDetailses = new List <DailyFileDetails>()
                {
                    new DailyFileDetails()
                    {
                        DailyFileId = DailyFileId,
                        EmployeeId  = EmployeeId,
                        Net         = Net
                    }
                };
                dailyfile.FileTotalAmount += Net;
                dailyfile.EmployeesNumber  = empnum + 1;

                _db.SaveChanges();

                var root = HttpContext.Current.Server.MapPath("~/Uploads/SourceFile/ATM.xls");

                string con1 =
                    (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + root +
                     ";Extended Properties='Excel 12.0 Xml; HDR = YES; IMEX = 1';");


                string con2 = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dailyfile.FilePath +
                               ";Extended Properties='Excel 12.0 Xml; HDR = YES; IMEX = 1';");
                //string con2 =
                //  رقم الموظف بجهته الأصلية
                //string con2 = (@"provider=microsoft.ace.oledb.12.0;data source=c:\file\bank.xls;extended properties='excel 12.0 xml; hdr = yes; imex = 1';");
                var code = _db.Employees.Find(EmployeeId).Code;
                BL  bl   = new BL(con1);

                DataTable dtw = bl.GetTable(String.Format("select * from [Sheet1$] where [{0}] = '" + code.ToString() + "'", "رقم الموظف بجهته الأصلية"));
                BL        bl2 = new BL(con2);

                DataTable dtw2 = bl2.GetTable(String.Format("select * from [Sheet1$] where [{0}] = '" + code.ToString() + "'", "رقم الموظف بجهته الأصلية"));

                if (dtw2.Rows.Count == 0)
                {
                    bl2.Insert(String.Format("insert into [Sheet1$] values ('{0}','{1}','{2}','{3}','{4}','{5}',{6})"
                                             , dtw.Rows[0].ItemArray[0]
                                             , dtw.Rows[0].ItemArray[1]
                                             , dtw.Rows[0].ItemArray[2]
                                             , dtw.Rows[0].ItemArray[3]
                                             , dtw.Rows[0].ItemArray[4]
                                             , dtw.Rows[0].ItemArray[5]
                                             , Net
                                             ));
                }
                else
                {
                    bl2.update(String.Format("update [Sheet1$] set[{1}] =[{1}]+" + Net + " where  [{0}] = '" + code.ToString() + "'",
                                             "رقم الموظف بجهته الأصلية", "المرتب"));
                }
                ///		49	23706.61--145     73,984.58
                //if (EmpInfo != null)
                //{
                //    _db = new ERPContext();
                //    var model = _db.DailyFileDetailses.Find(EmpInfo.Id);
                //    model.Net = EmpInfo.Net;
                //    _db.SaveChanges();

                //}
                return(Ok());
            }
            return(BadRequest("Wrong Request"));
        }
        public IHttpActionResult UpdateEmpInfo(int DailyFileId, int EmployeeId, decimal Net)
        {

            var dailyfile = _db.DailyFiles.Find(DailyFileId);
            var empnum = _db.DailyFileDetailses.Count(x => x.DailyFileId == DailyFileId);

            if (dailyfile != null)
            {
                dailyfile.DailyFileDetailses = new List<DailyFileDetails>()
                {
                    new DailyFileDetails()
                    {
                        DailyFileId = DailyFileId,
                        EmployeeId = EmployeeId,
                        Net = Net
                    }
                };
                dailyfile.FileTotalAmount += Net;
                dailyfile.EmployeesNumber = empnum + 1;

                _db.SaveChanges();

                var root = HttpContext.Current.Server.MapPath("~/Uploads/SourceFile/ATM.xls");

                string con1 =
    (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + root +
     ";Extended Properties='Excel 12.0 Xml; HDR = YES; IMEX = 1';");


                string con2 = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dailyfile.FilePath +
    ";Extended Properties='Excel 12.0 Xml; HDR = YES; IMEX = 1';");
                //string con2 =
                //  رقم الموظف بجهته الأصلية
                //string con2 = (@"provider=microsoft.ace.oledb.12.0;data source=c:\file\bank.xls;extended properties='excel 12.0 xml; hdr = yes; imex = 1';");
                var code = _db.Employees.Find(EmployeeId).Code;
                BL bl = new BL(con1);

                DataTable dtw = bl.GetTable(String.Format("select * from [Sheet1$] where [{0}] = '" +code.ToString()+"'", "رقم الموظف بجهته الأصلية"));
                BL bl2 = new BL(con2);

              DataTable dtw2=  bl2.GetTable(String.Format("select * from [Sheet1$] where [{0}] = '" + code.ToString() + "'", "رقم الموظف بجهته الأصلية"));

                if(dtw2.Rows.Count==0)
                { 
                bl2.Insert(String.Format("insert into [Sheet1$] values ('{0}','{1}','{2}','{3}','{4}','{5}',{6})"
                    , dtw.Rows[0].ItemArray[0]
                    , dtw.Rows[0].ItemArray[1]
                    , dtw.Rows[0].ItemArray[2]
                    , dtw.Rows[0].ItemArray[3]
                    , dtw.Rows[0].ItemArray[4]
                    , dtw.Rows[0].ItemArray[5]
                    ,Net
                    ));
                }
                else
                {
                    bl2.update(String.Format("update [Sheet1$] set[{1}] =[{1}]+" + Net + " where  [{0}] = '" + code.ToString() + "'",
                        "رقم الموظف بجهته الأصلية", "المرتب"));
                }
                ///		49	23706.61--145     73,984.58
                //if (EmpInfo != null)
                //{
                //    _db = new ERPContext();
                //    var model = _db.DailyFileDetailses.Find(EmpInfo.Id);
                //    model.Net = EmpInfo.Net;
                //    _db.SaveChanges();

                //}
                return Ok();
            }
            return BadRequest("Wrong Request");
        }
        public IHttpActionResult DeleteEmpInfo(int Id)
        {
            var found = _db.DailyFileDetailses.Include("DailyFile").Where(x => x.Id == Id).FirstOrDefault();
            var code = _db.Employees.Find(found.EmployeeId).Code;
            var empnum = _db.DailyFileDetailses.Count(x => x.DailyFileId == found.DailyFileId);
            if (found != null)
            {
                try
                {
                    string path = found.DailyFile.FilePath;
                    string con =
               (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path +
                ";Extended Properties='Excel 12.0 Xml; HDR = YES; IMEX = 1';");



                    BL bl = new BL(con);
                    bl.update(String.Format("update [Sheet1$] set[{1}] =[{1}]-"+found.Net.ToString()+" where  [{0}] = '" + code.ToString()+"'",
                        "رقم الموظف بجهته الأصلية", "المرتب"));


                    found.DailyFile.FileTotalAmount -= found.Net;
                    found.DailyFile.EmployeesNumber = empnum - 1;
                   
                    _db.DailyFileDetailses.Remove(found);
                    _db.SaveChanges();

                    
                    return Ok();
                }
                catch (Exception ex)
                {
                    throw  new Exception(ex.Message);
                    return Conflict();
                }

            }
            return Conflict();
        }
        private void InsertIntoDailyFiles(DailyFile dailyFile, string root, int newname)
        {
            dailyFile.FilePath = root + newname + ".xls";

            string con =
                (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dailyFile.FilePath +
                 ";Extended Properties='Excel 12.0 Xml; HDR = YES; IMEX = 1';");

            //string con2 =
            //      (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\File\Bank.xls;Extended Properties='Excel 12.0 Xml; HDR = YES; IMEX = 1';");

            BL bl = new BL(con);

            DataTable dtw = bl.GetTable("select * from [Sheet1$]");

       

            List<DailyFileDetails> list = new List<DailyFileDetails>();
            foreach (DataRow row in dtw.Rows)
            {
                if (row[6].ToString() != "")
                {
                    int empid = Convert.ToInt32(row[4]);

                    if (Convert.ToDecimal(row[6]) > 0)
                    {
                        var firstOrDefault = _db.Employees.FirstOrDefault(x => x.Code == (empid));
                        if (firstOrDefault != null)
                            dailyFile.DailyFileDetailses.Add(new DailyFileDetails()
                            {
                                Net = Convert.ToDecimal(row[6]),
                                EmployeeId = firstOrDefault.Id,
                                DailyFileId = dailyFile.Id,
                                Employee = firstOrDefault
                            });
                    }
                }
            }

            dailyFile.EmployeesNumber = dailyFile.DailyFileDetailses.Count;
            dailyFile.FileTotalAmount = dailyFile.DailyFileDetailses.Sum(x => x.Net);
            _db.DailyFiles.Add(dailyFile);
            _db.SaveChanges();
        }