public DataTable SelectAllProduct()
{
return(ToDataTable.LINQResultToDataTable(
from Produ in context.Product_Table
join Catag in context.Category_Table on Produ.Catagory_ID equals Catag.Catagory_ID
join Unite in context.Unite_Table on Produ.Unite_ID equals Unite.Unite_ID
select new { Produ.Product_Name, Produ.BareCode, Produ.Category_Table.Catagory_Name, Produ.Unite_Table.Unite_Name, Produ.Product_Size }));
}
public DataTable SelectAllProductQunatity()
{
return(ToDataTable.LINQResultToDataTable(from Stok in context.Stock_Quantity_Table
join Produ in context.Product_Table
on Stok.Product_ID equals Produ.Product_ID
where Stok.Quantity <= Produ.AlarmQuantity
select new { Stok.Quantity, Produ.AlarmQuantity, Produ.Product_Name, Produ.Category_Table.Catagory_Name }));
}
public DataTable PurchDeatiles(int Purch_ID)
{
return(ToDataTable.LINQResultToDataTable(from purch in context.Purchases_Details_Table
join produ in context.Product_Table
on purch.Product_ID equals produ.Product_ID
join Cat in context.Category_Table
on produ.Catagory_ID equals Cat.Catagory_ID
where purch.Purchases_ID == Purch_ID
select new { purch.Dealer_Price, purch.Expire_Date, purch.ExpireOrNot, purch.Product_Table.Product_Name, purch.Product_Table.Category_Table.Catagory_Name, purch.Purch_Price, purch.Purch_Total, purch.Quantity, purch.User_Price }));
}
public ActionResult FileDW()
{
var data = repo.All();
XLWorkbook workbook = new XLWorkbook();
workbook.AddWorksheet(ToDataTable.CreateDataTable(data), "Sheet1");
workbook.SaveAs(@"C:\Users\user\Downloads\客戶聯絡人.xlsx");
workbook.Dispose();
return(File(@"C:\Users\user\Downloads\客戶聯絡人.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "客戶聯絡人.xlsx"));
}
public DataTable SelectAllRev(string Date, string Date2)
{
DateTime D1 = DateTime.Parse(Date);
DateTime D2 = DateTime.Parse(Date2);
return(ToDataTable.LINQResultToDataTable(from Payment in context.Pay_Rev_Table
join PayType in context.Pay_Rev_Type_Table
on Payment.Pay_Rev_Type_ID equals PayType.Pay_Rev_Type_ID
where Payment.Pay_Rev_Date >= D1 && Payment.Pay_Rev_Date <= D2 && Payment.Flag == "ايرادات"
select new { Payment.Pay_Rev_Amount, Payment.Pay_Rev_Date, Payment.Pay_Rev_Description, Payment.Pay_Rev_Type_Table.Pay_Rev_Type_Name }));
}
public DataTable WillExpire(int Dayes)
{
DateTime D1 = DateTime.Now;
D1.AddDays(Dayes);
return(ToDataTable.LINQResultToDataTable(from PURQun in context.Stock_Purch_Table
join produ in context.Product_Table
on PURQun.Product_ID equals produ.Product_ID
join Cat in context.Category_Table
on produ.Catagory_ID equals Cat.Catagory_ID
join purcdetail in context.Purch_Retern_Details_Table
on produ.Product_ID equals purcdetail.Product_ID
join StockQuntity in context.Stock_Quantity_Table
on produ.Product_ID equals StockQuntity.Product_ID
where purcdetail.ExpireOrNot == "توجد" && purcdetail.Expire_Date <= D1
select new { produ.Product_Name, produ.Category_Table.Catagory_Name, StockQuntity.Quantity, purcdetail.Expire_Date }));
}
public MemoryStream GetReporteDinamico(DTOReporteDinamico reporteDinamico, string fileName)
{
MemoryStream memoryStream = null;
try
{
GenericDataRepository <CatMotivosViaje> motivosViajeRepository = new GenericDataRepository <CatMotivosViaje>();
IList <DTOReporteDinamico> reporteDinamicoList = (from encuesta in _encuestaRepository.GetList(x => x.FechaAplicacion >= reporteDinamico.FechaInicio.Date && x.FechaAplicacion.Date <= reporteDinamico.FechaFin.Date)
from derechohabiente in _derechohabienteRepository.GetList(x => x.IdDerechohabiente == encuesta.IdDerechohabiente).Take(1).DefaultIfEmpty()
from tipoDestino in _tiposDestinoRepository.GetList(x => x.IdTipoDestino == encuesta.IdTipoDestino).Take(1).DefaultIfEmpty()
from temporada in _temporadasRepository.GetList(x => x.IdTemporada == encuesta.IdTemporada).Take(1).DefaultIfEmpty()
from viaje in _tiposViajeRepository.GetList(x => x.IdTipoViaje == encuesta.IdTipoViaje).Take(1).DefaultIfEmpty()
from motivo in motivosViajeRepository.GetList(x => x.IdMotivoViaje == encuesta.IdMotivoViaje).Take(1).DefaultIfEmpty()
from genero in _generoRepository.GetList(x => x.IdGenero == derechohabiente.IdGenero).Take(1).DefaultIfEmpty()
from estado in _estadoRepository.GetList(x => x.IdEstado == derechohabiente.IdEstado).Take(1).DefaultIfEmpty()
select new DTOReporteDinamico
{
Destino = tipoDestino.Nombre,
TemporadaVacacional = temporada.Nombre,
Viaje = viaje.Nombre,
Motivo = motivo.Nombre,
Nombre = derechohabiente.Nombre + " " + derechohabiente.ApellidoPaterno + " " + derechohabiente.ApellidoMaterno,
Genero = genero.Genero,
Edad = DateTime.Now.Year - derechohabiente.FechaNacimiento.Year,
Estado = estado.Nombre,
Derechohabiente = derechohabiente.TipoDerechohabiente,
Afiliacion = derechohabiente.Afiliacion
})
.ToList();
DataTable reporteDinamicoDataTable = ToDataTable.IListToDataTable(new List <DTOReporteDinamico>(reporteDinamicoList));
memoryStream = ToExcel.ExportToExcel(reporteDinamicoDataTable, fileName);
}
catch (Exception ex)
{
throw ex;
}
return(memoryStream);
}
public MemoryStream GetReporteEstatico(DTOReporteEstatico reporteEstatico, string fileName)
{
MemoryStream memoryStream = null;
try
{
IList <Derechohabiente> derechohabientes = _derechohabienteRepository.GetList(x => (reporteEstatico.IdEstado != null ? x.IdEstado == reporteEstatico.IdEstado : x.IdEstado == x.IdEstado) &&
(reporteEstatico.IdGenero != null ? x.IdGenero == reporteEstatico.IdGenero : x.IdGenero == x.IdGenero) &&
(reporteEstatico.RangoInferior != null ? (reporteEstatico.RangoInferior <= (DateTime.Now.Year - x.FechaNacimiento.Year)) : x.FechaNacimiento == x.FechaNacimiento) &&
(reporteEstatico.RangoInferior != null ? (reporteEstatico.RangoSuperior >= (DateTime.Now.Year - x.FechaNacimiento.Year)) : x.FechaNacimiento == x.FechaNacimiento));
IList <DTOReporteEstatico> reporteEstaticoList = (from encuesta in _encuestaRepository.GetList(x => x.FechaAplicacion >= reporteEstatico.FechaInicio.Date && x.FechaAplicacion.Date <= reporteEstatico.FechaFin.Date)
join derechohabiente in derechohabientes on encuesta.IdDerechohabiente equals derechohabiente.IdDerechohabiente
from tipoDestino in _tiposDestinoRepository.GetList(x => x.IdTipoDestino == encuesta.IdTipoDestino).Take(1).DefaultIfEmpty()
from temporada in _temporadasRepository.GetList(x => x.IdTemporada == encuesta.IdTemporada).Take(1).DefaultIfEmpty()
from viaje in _tiposViajeRepository.GetList(x => x.IdTipoViaje == encuesta.IdTipoViaje).Take(1).DefaultIfEmpty()
from genero in _generoRepository.GetList(x => x.IdGenero == derechohabiente.IdGenero).Take(1).DefaultIfEmpty()
from estado in _estadoRepository.GetList(x => x.IdEstado == derechohabiente.IdEstado).Take(1).DefaultIfEmpty()
select new DTOReporteEstatico
{
Destino = tipoDestino.Nombre,
TemporadaVacacional = temporada.Nombre,
Viaje = viaje.Nombre,
Genero = genero.Genero,
Edad = DateTime.Now.Year - derechohabiente.FechaNacimiento.Year,
Estado = estado.Nombre
}).ToList();
DataTable reporteEstaticoDataTable = ToDataTable.IListToDataTable(new List <DTOReporteEstatico>(reporteEstaticoList));
memoryStream = ToExcel.ExportToExcel(reporteEstaticoDataTable, fileName);
}
catch (Exception ex)
{
throw ex;
}
return(memoryStream);
}
public DataTable SellectPurch(int Purch_ID)
{
return(ToDataTable.LINQResultToDataTable(from Purch in context.Purchases_Table
where Purch.Purchases_ID == Purch_ID
select new { Purch.Addation_Date, Purch.Notes, Purch.Purch__Pauid, Purch.Purch_Remin, Purch.Purch_Total, Purch.Purchases_ID }));
}
public DataTable LisSelectCompanyInfo()
{
return(ToDataTable.LINQResultToDataTable(from Com in context.Company_Info_Table
select Com));
}
public DataTable SupplierData(string SupMobile)
{
return(ToDataTable.LINQResultToDataTable(from Supp_O in context.Cus_Sup_Table
where Supp_O.Cus_Sup_Mobile == SupMobile && Supp_O.Flag == 1
select Supp_O));
}
public DataTable ToDataTable(ToDataTableOptions options, DataTable dataTable)
{
var func = new ToDataTable(options, this);
return(func.Execute(dataTable));
}
