public void getinvoicetest()
{
DataInvoice.SOLUTIONS.INVOICES.INVOICE.InvoiceProvider invoiceprovide = new DataInvoice.SOLUTIONS.INVOICES.INVOICE.InvoiceProvider(env.ConnectorSgbd);
DataInvoice.SOLUTIONS.INVOICES.INVOICE.Invoice invoice = null;
invoice = invoiceprovide.GetInvoice(1);
Console.WriteLine(invoice.InvoiceTitle);
InvoiceResults invoices = invoiceprovide.SearchInvoice(new DataInvoice.SOLUTIONS.INVOICES.INVOICE.FORM.InvoiceSearchForm());
foreach (var invoiceitem in invoices)
{
Console.WriteLine(invoiceitem.ToString());
}
}
public INVOICE.InvoiceResults SearchInvoice(InvoiceSearchForm form)
{
// string sql = "SELECT * FROM invoices where 1=1";
Dictionary <string, object> ins = new Dictionary <string, object>();
if (form == null)
{
form = new InvoiceSearchForm();
}
List <string> wheres = new List <string>();
try
{
if (!string.IsNullOrWhiteSpace(form.CustomerRef))
{
ins.Add("CustomerRef", form.CustomerRef); wheres.Add(" CustomerRef=@CustomerRef");
}
if (!string.IsNullOrWhiteSpace(form.InvoiceTitle))
{
ins.Add("InvoiceTitle", form.InvoiceTitle); wheres.Add(" InvoiceTitle=@InvoiceTitle");
}
if (!string.IsNullOrWhiteSpace(form.InvoiceType))
{
ins.Add("InvoiceType", form.InvoiceType); wheres.Add(" InvoiceType=@InvoiceType");
}
if (!string.IsNullOrWhiteSpace(form.ProviderRef))
{
ins.Add("ProviderRef", form.ProviderRef); wheres.Add(" ProviderRef=@ProviderRef");
}
if (!string.IsNullOrWhiteSpace(form.RefInvoice))
{
ins.Add("RefInvoice", form.RefInvoice); wheres.Add(" RefInvoice=@RefInvoice");
}
//if (form.DateAcq.HasValue) {ins.Add("DateAcq", form.DateAcq.Value); wheres.Add(" and InvoiceTitle=@InvoiceTitle"); }
//if (form.DateCreate.HasValue) {ins.Add("DateCreate", form.DateCreate.Value); wheres.Add(" and InvoiceTitle=@InvoiceTitle"); }
if (form.DateInvoice.HasValue)
{
ins.Add("DateInvoice", form.DateInvoice.Value); wheres.Add(" DateInvoice=@DateInvoice");
}
//if (form.DatePaid.HasValue) {ins.Add("DatePaid", form.DatePaid.Value); wheres.Add(" and InvoiceTitle=@InvoiceTitle"); }
//if (form.DateSend.HasValue) { ins.Add("DateSend", form.DateSend.Value); wheres.Add(" and InvoiceTitle=@InvoiceTitle"); }
//if (form.DateValidate.HasValue) { ins.Add("DateValidate", form.DateValidate.Value); wheres.Add( " and InvoiceTitle=@InvoiceTitle"); }
string sql = " FROM invoices ";
if (wheres.Count > 0)
{
sql += string.Join(" AND ", wheres.ToArray());
}
InvoiceResults retour = new InvoiceResults();
int nb = Convert.ToInt32(this.Connector.QueryScalar("SELECT COUNT(*) as nb " + sql, ins));
retour.TotalCountResults = nb;
System.Data.DataTable ret = this.Connector.Query("SELECT * " + sql, ins);
retour.LoadFromDataTable(ret);
return(retour);
}
catch (Exception ex)
{
throw new Exception("SearchInvoice " + ex.Message, ex);
}
}
public IActionResult ViewInvoices(InvoiceSearchViewModel model1)
{
DateTime from = DateTime.MinValue;
DateTime to = DateTime.MinValue;
if (model1.Status == null || model1.Status == string.Empty)
{
model1.Status = "pending";
}
if (model1.From != null)
{
if (model1.From != "" || model1.From != string.Empty)
{
from = DateTime.Parse(model1.From, new CultureInfo("zh-SG"));
}
}
if (model1.To != null)
{
if (model1.To != "" || model1.To != string.Empty)
{
to = DateTime.Parse(model1.To, new CultureInfo("zh-SG"));
}
}
var userRole = "";
var role = User.FindFirst(ClaimTypes.Role).Value;
if (role == "1")
{
userRole = "CheckingAuthority";
}
else if (role == "2")
{
userRole = "ApproverAuthority";
}
ViewBag.Name = User.FindFirst(ClaimTypes.NameIdentifier).Value;
IDBService service = new DBservice(_helper);
var fetchedResults = service.GetAllInvoices(from, to, model1.Status, userRole);
var results = fetchedResults.Skip(model1.Results * 5).Take(5).OrderBy(e => e.DueDate);
InvoicePagingResults pagingResults = new InvoicePagingResults();
List <InvoiceResults> list = new List <InvoiceResults>();
foreach (var invoice in results)
{
InvoiceResults model = new InvoiceResults();
model.InvoiceId = invoice.InvoiceId;
model.InvoiceNo = invoice.InvoiceNo;
model.AccountDate = invoice.AccountDate.ToString("dd/M/yyyy", CultureInfo.InvariantCulture);
model.DueDate = invoice.DueDate.ToString("dd/M/yyyy", CultureInfo.InvariantCulture);
model.CurrencyCode = invoice.CurrencyCode;
model.CustomerName = invoice.CustomerName;
model.VesselName = invoice.VesselName;
model.TotalAmt = invoice.TotalLocalAmt.ToString("N",
CultureInfo.CreateSpecificCulture("en-IN"));
list.Add(model);
}
pagingResults.currentPageNumber = model1.Results;
int records = fetchedResults.Count();
int remainder = records % 5;
pagingResults.totalNumberOfRecords = remainder > 0 ? (records / 5) + 1 : (records / 5);
pagingResults.invoiceResults = list;
pagingResults.modelInvoice = model1;
return(View(pagingResults));
}
