// GET: api/Departments
public IHttpActionResult GetDepartments()
{
db= new ERPContext();
var dep= db.Departments.AsQueryable();
var result = from c in dep select new {name = c.Name, value = c.Id};
return Ok(result);
}
public HttpResponseMessage Post([FromBody] Employee employee)
{
_db = new ERPContext();
if (ModelState.IsValid)
{
if (employee.Id == 0)
_db.Employees.Add(employee);
else
{
var emp = _db.Employees.Find(employee.Id);
emp.Name = employee.Name;
emp.Nickname = employee.Nickname;
emp.Gender = employee.Gender;
emp.DepartmentId = employee.DepartmentId;
emp.PositionId = employee.PositionId;
emp.Phone = employee.Phone;
emp.Email = employee.Email;
emp.Code = employee.Code;
emp.NationalId = employee.NationalId;
}
_db.SaveChanges();
return Request.CreateResponse(HttpStatusCode.OK, employee);
}
else
{
return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ModelState);
}
}
//public IHttpActionResult Get()
//{
// return Ok();
//}
public IHttpActionResult Get()
{
_db = new ERPContext();
_db.Configuration.ProxyCreationEnabled = false;
var employees = _db.Employees.Include("Department").Include("Position").AsQueryable();
return Ok(employees.OrderBy(x => x.Name));
}
public IHttpActionResult GetClosed(int Id)
{
_db = new ERPContext();
_db.Configuration.ProxyCreationEnabled = false;
var result = _db.Dailies
.Include("ExpensessType")
.Include("DailyFiles")
.Where(x => x.Open==false)
.FirstOrDefault(x => x.Id == Id);
List<DailyFile> resultfiles = _db.DailyFiles
.Where(x => x.DailyId == Id).ToList();
decimal totalamount = 0;
//foreach (DailyFile data in resultfiles)
//{
// totalamount = data.DailyFileDetailses.Where(x => x.DailyFileId == data.Id).Sum(x => x.Net);
//}
return Ok(new
{
result = result,
totalamount = totalamount
});
}
public IHttpActionResult Get()
{
_db = new ERPContext();
_db.Configuration.ProxyCreationEnabled = false;
var result = _db.Dailies.Include("ExpensessType").OrderByDescending(x => x.CreatedDate).Where(x => x.Open).ToList();
return Ok(result);
}
public IHttpActionResult GetEmployeeInfo(int Id)
{
_db = new ERPContext();
var empInfo = _db.DailyFileDetailses.Include("Employee").FirstOrDefault(x => x.Id == Id);
if (empInfo == null)
{
}
return Ok(empInfo);
}
// GET: api/DailyFiles
public IQueryable<DailyFile> GetDailyFiles(int Id)
{
_db = new ERPContext();
_db.Configuration.ProxyCreationEnabled = false;
var result = _db.DailyFiles.OrderByDescending(x => x.CreatedDate).Where(x => x.DailyId == Id).AsQueryable();
return result;
}
public IHttpActionResult GetExpensessType()
{
_db = new ERPContext();
var result = (from c in _db.ExpensessTypes
select new
{
value = c.Id,
label = c.Name
}).ToList();
if (result != null)
{
return Ok(result);
}
return BadRequest("Error");
}
public IHttpActionResult Get(int Id)
{
_db = new ERPContext();
_db.Configuration.ProxyCreationEnabled = false;
//var daily = _db.Dailies.Find(Id);
//var dailyFiles = _db.DailyFiles.Where(x => x.DailyId == Id);
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,
Details = g.Select(x => x.DailyFile).Count().ToString(),
Description = g.Select(x => x.DailyFile),
Code = emp.FirstOrDefault(x => x.Id == g.Key).Code,
Net = g.Sum(i => i.Net)
}).ToList();
return Ok(dailydetails);
}
public IHttpActionResult GetEmployees(int? Code = 0, string NationalId = "")
{
_db = new ERPContext();
Employee empInfo = new Employee();
if (Code > 0)
{
empInfo = _db.Employees.Include("DailyFileDetails.DailyFile.Daily").FirstOrDefault(x => x.Code == Code);
}
if (!string.IsNullOrEmpty(NationalId))
{
empInfo = _db.Employees.Include("DailyFileDetails.DailyFile.Daily").FirstOrDefault(x => x.NationalId == NationalId);
}
if (empInfo==null)
{
return NotFound();
}
return Ok(empInfo);
}
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);
}
}
public IHttpActionResult DeleteEmployee(int entityId)
{
_db = new ERPContext();
Employee employee = _db.Employees.Find(entityId);
if (employee == null)
{
return NotFound();
}
_db.Employees.Remove(employee);
_db.SaveChanges();
//string con =
// (@"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$]");
//_db = new ERPContext();
//foreach (DataRow row in dtw.Rows)
//{
// Employee EMP = new Employee();
// EMP.NationalId = row[0].ToString();
// EMP.Name = row[5].ToString();
// EMP.Code = Convert.ToInt32( row[4]);
// _db.Employees.Add(EMP);
// _db.SaveChanges();
//}
return Ok(new {msg = "Delete Successfully"});
}
public IHttpActionResult Get(int Id)
{
_db = new ERPContext();
var result = _db.Employees.FirstOrDefault(x => x.Id == (Id));
return Ok(result);
}
public IHttpActionResult Delete(int Id)
{
_db = new ERPContext();
var daily = _db.Dailies.Find(Id);
if (daily != null)
{
_db.Dailies.Remove(daily);
_db.SaveChanges();
var filePath =
HttpContext.Current.Server.MapPath("~/Uploads/DailyFiles/Daily-" + Id);
if (Directory.Exists(filePath))
{
try
{
Directory.Delete(filePath, true);
}
catch (IOException)
{
Thread.Sleep(0);
Directory.Delete(filePath, true);
}
}
return Ok(new { msg = "success" });
}
return BadRequest("Wrong Request");
}
public IHttpActionResult Post([FromBody] Daily model)
{
_db = new ERPContext();
model.TotalAmount = 0;
if (ModelState.IsValid)
{
if (model.Id == 0)
{
model.Open = true;
model.CreatedDate = DateTime.UtcNow;
_db.Dailies.Add(model);
_db.SaveChanges();
var filePath =
HttpContext.Current.Server.MapPath("~/Uploads/DailyFiles/Daily-" + model.Id);
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
}
else
{
var daily = _db.Dailies.Find(model.Id);
if (daily != null)
{
daily.Open = model.Open;
daily.Name = model.Name;
daily.ClosedDate = model.ClosedDate;
daily.CheckGP = model.CheckGP;
daily.DailyDay = model.DailyDay;
daily.ExpensessTypeId = model.ExpensessTypeId;
_db.SaveChanges();
}
}
}
return Ok(new { msg = "success" });
}