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 }));
}
}
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();
}
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 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 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");
}
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();
}