public Task <titipankapal> GetTitipanKapal(int ManifestId)
{
using (var db = new OcphDbContext())
{
try
{
var sp = string.Format("TitipanKapal");
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = sp;
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("ManifestId", ManifestId));
var dr = cmd.ExecuteReader();
var ent = new EntityInfo(typeof(ModelsShared.Models.titipankapal));
var list = new MappingColumn(ent).MappingWithoutInclud <titipankapal>(dr);
dr.Close();
titipankapal titip = null;
if (list.Count > 0)
{
titip = list.FirstOrDefault();
titip.Jumlah = list.Count;
}
return(Task.FromResult(titip));
}
catch (Exception ex)
{
throw new SystemException(ex.Message);
}
}
}
public invoice Get(int Id)
{
using (var db = new OcphDbContext())
{
try
{
var inv = db.Invoices.Where(O => O.Id == Id).FirstOrDefault();
var cmd = db.CreateCommand();
cmd.CommandText = "InvoiceDetail";
cmd.Parameters.Add(new MySqlParameter("invoiceId", Id));
cmd.CommandType = System.Data.CommandType.StoredProcedure;
var reader = cmd.ExecuteReader();
var result = MappingProperties <invoicedetail> .MappingTable(reader);
reader.Close();
if (inv != null)
{
inv.Details = result;
}
return(inv);
}
catch (Exception ex)
{
throw new SystemException(ex.Message);
}
}
}
public List <PenjualanReportModel> GetPenjualanFromTo(DateTime startDate, DateTime ended)
{
using (var db = new OcphDbContext())
{
try
{
var sp = string.Format("GetPenjualanFromTo");
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = sp;
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("startDate", startDate));
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("ended", ended));
var dr = cmd.ExecuteReader();
var list = MappingProperties <PenjualanReportModel> .MappingTable(dr);
dr.Close();
foreach (var item in list)
{
item.From = startDate;
item.To = ended;
}
return(list);
}
catch (Exception ex)
{
throw new SystemException(ex.Message);
}
}
}
public Task <List <PackingListPrintModel> > GetPackingList(int ManifestId)
{
using (var db = new OcphDbContext())
{
try
{
var sp = string.Format("PackingList");
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = sp;
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("ManifestId", ManifestId));
var dr = cmd.ExecuteReader();
var ent = new EntityInfo(typeof(ModelsShared.Models.PackingListPrintModel));
var list = new MappingColumn(ent).MappingWithoutInclud <PackingListPrintModel>(dr);
dr.Close();
return(Task.FromResult(list));
}
catch (Exception ex)
{
throw new SystemException(ex.Message);
}
}
}
private Task <List <PenjualanOfMonth> > GetPenjualanOfMonth(int tahun)
{
var list = new List <ModelsShared.Models.PenjualanOfMonth>();
using (var db = new OcphDbContext())
{
var sp = string.Format("PenjualanOfamount");
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = sp;
for (int i = 1; i <= 12; i++)
{
var date = new DateTime(tahun, i, 1);
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("Tanggal", date));
var dr = cmd.ExecuteReader();
var ent = new EntityInfo(typeof(ModelsShared.Models.PenjualanOfMonth));
var result = new MappingColumn(ent).MappingWithoutInclud <PenjualanOfMonth>(dr);
if (result.Count > 0)
{
var item = result.FirstOrDefault();
list.Add(item);
}
cmd.Parameters.RemoveAt("Tanggal");
dr.Close();
}
}
return(Task.FromResult(list));
}
public IEnumerable <penjualan> Get(DateTime start, DateTime end)
{
using (var db = new OcphDbContext())
{
var sp = string.Format("GetPenjualans");
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = sp;
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("StartDate", start));
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("EndDate", end));
var dr = cmd.ExecuteReader();
var list = MappingProperties <penjualan> .MappingTable(dr);
dr.Close();
foreach (var item in list)
{
item.Details = db.Collies.Where(O => O.PenjualanId == item.Id).ToList();
item.Shiper = db.Customers.Where(O => O.Id == item.ShiperID).FirstOrDefault();
if (item.Shiper != null)
{
item.Shiper.City = db.Cities.Where(O => O.Id == item.Shiper.CityID).FirstOrDefault();
}
item.Reciver = db.Customers.Where(O => O.Id == item.ReciverID).FirstOrDefault();
if (item.Reciver != null)
{
item.Reciver.City = db.Cities.Where(O => O.Id == item.Reciver.CityID).FirstOrDefault();
}
item.DeliveryStatus = db.DeliveryStatusses.Where(O => O.PenjualanId == item.Id).FirstOrDefault();
}
return(list);
}
}
public async Task <List <PenjualanOfYear> > GetPenjualanThreeYear()
{
var list = new List <ModelsShared.Models.PenjualanOfYear>();
using (var db = new OcphDbContext())
{
var thisyear = DateTime.Now.Year;
var sp = string.Format("PenjualanOfaYear");
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = sp;
for (int i = thisyear; i > thisyear - 3; i--)
{
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("tahun", i));
var dr = cmd.ExecuteReader();
var ent = new EntityInfo(typeof(ModelsShared.Models.PenjualanOfYear));
list = new MappingColumn(ent).MappingWithoutInclud <PenjualanOfYear>(dr);
if (list.Count > 0)
{
var item = list.FirstOrDefault();
item.Months = await GetPenjualanOfMonth(item.Tahun);
list.Add(item);
}
cmd.Parameters.RemoveAt("tahun");
dr.Close();
}
}
return(list);
}
public IEnumerable <invoice> Get(DateTime start, DateTime end)
{
using (var db = new OcphDbContext())
{
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "GetAllInvoice";
cmd.Parameters.Add(new MySqlParameter("StartDate", start));
cmd.Parameters.Add(new MySqlParameter("EndDate", end));
var reader = cmd.ExecuteReader();
var result = MappingProperties <invoice> .MappingTable(reader);
return(result);
}
}
public Task <List <invoice> > GetInvoiceNotYetPaid()
{
using (var db = new OcphDbContext())
{
var sp = string.Format("InvoiceNotYetPaid");
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = sp;
var dr = cmd.ExecuteReader();
var ent = new EntityInfo(typeof(ModelsShared.Models.invoice));
var list = new MappingColumn(ent).MappingWithoutInclud <invoice>(dr);
dr.Close();
return(Task.FromResult(list));
}
}
public Task <List <invoice> > GetInvoiceJatuhTempo()
{
using (var db = new OcphDbContext())
{
var sp = string.Format("InvoiceJatuhTempo");
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = sp;
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("Tanggal", DateTime.Now));
var dr = cmd.ExecuteReader();
var ent = new EntityInfo(typeof(ModelsShared.Models.invoice));
var list = new MappingColumn(ent).MappingWithoutInclud <invoice>(dr);
dr.Close();
return(Task.FromResult(list));
}
}
public TracingModel GetPenjualan(int STT)
{
using (var db = new OcphDbContext())
{
try
{
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "GetPenjualan";
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("STT", STT));
var reader = cmd.ExecuteReader();
var mapp = new MappingColumn(new EntityInfo(typeof(TracingModel)));
var result = mapp.MappingWithoutInclud <TracingModel>(reader);
reader.Close();
var tracing = result.FirstOrDefault();
if (result != null)
{
var packlists = db.PackingLists.Where(O => O.PenjualanId == tracing.Id).GroupBy(O => O.ManifestID).ToList();
tracing.Manifests = new List <manifestoutgoing>();
foreach (var item in packlists)
{
var manifest = db.Outgoing.Where(O => O.Id == item.Key).FirstOrDefault();
if (manifest != null)
{
tracing.Manifests.Add(manifest);
}
}
return(tracing);
}
else
{
throw new SystemException(MessageCollection.Message(MessageType.NotFound));
}
}
catch (Exception ex)
{
throw new SystemException(ex.Message);
}
}
}
private Task <List <PenjualanReportModel> > GetPenjualan(string sp)
{
using (var db = new OcphDbContext())
{
try
{
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = sp;
var dr = cmd.ExecuteReader();
var list = MappingProperties <PenjualanReportModel> .MappingTable(dr);
dr.Close();
return(Task.FromResult(list));
}
catch (Exception ex)
{
throw new SystemException(ex.Message);
}
}
}
public IEnumerable <manifestoutgoing> ManifestsByPenjualanId(int penjualanId)
{
try
{
using (var db = new OcphDbContext())
{
var sp = string.Format("GetManifestsByPenjualan");
var cmd = db.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = sp;
cmd.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("penjualanId", penjualanId));
var dr = cmd.ExecuteReader();
var ent = new EntityInfo(typeof(ModelsShared.Models.manifestoutgoing));
var list = new MappingColumn(ent).MappingWithoutInclud <manifestoutgoing>(dr);
dr.Close();
return(list);
}
}
catch (Exception ex)
{
throw new SystemException(ex.Message);
}
}