public ActionResult Index(int?pageNo, int?flag)
{
var ts = SessionStore.GetTravelSession();
var agents = defaultProvider.GetAgentList().Where(x => x.CreatedBy == ts.AppUserId);
SalesReportModel model = new SalesReportModel();
ViewData["AgentList"] = new SelectList(agents, "AgentId", "AgentName");
ViewData["AirlineTypes"] = new SelectList(ser.GetAirlineTypesList(), "AirineTypeId", "TypeName");
ViewData["Currency"] = defaultProvider.GetCurrencyList();
model.FromDate = DateTime.Now.AddDays(-15);
model.ToDate = DateTime.Now;
var detailsSummary = ser.GetSalesReportSummary(model.AgentId, model.FromDate, model.ToDate, model.AirlineTypesId, model.Currency);
model.salesReportSummary = detailsSummary;
var details = ser.GetSalesReportSummaryDetails(model.AgentId, model.FromDate, model.ToDate, model.AirlineTypesId, model.Currency);
model.salesReportDetails = details;
return(View(model));
}
public IActionResult SalesReport()
{
using (var context = new ProductsDatabaseContext())
{
var result = from sales in context.Sales
join products in context.Products on sales.ProductId equals products.ProductId
join salespeople in context.SalesPeoples on sales.SellerId equals salespeople.SellerId
select new SalesReportItemModel()
{
TransactionId = sales.TransactionId,
TransactionDate = sales.TransactionDate,
ProductId = sales.ProductId,
ProductName = products.ProductName,
SellerId = sales.SellerId,
SellerName = salespeople.SellerName,
Amount = sales.Amount,
Price = sales.Price,
Total = sales.Amount * sales.Price
};
var model = new SalesReportModel()
{
SalesItems = result.ToList()
};
return(View(model));
}
}
public List <SalesReportModel> GetInformation(string reportOf, int?currencyId, DateTime?fromDate, DateTime?toDate)
{
var Data = entity.Core_GetSalesReport(reportOf, currencyId, fromDate, toDate);
List <SalesReportModel> informationcollection = new List <SalesReportModel>();
foreach (var item in Data)
{
SalesReportModel singleinformation = new SalesReportModel()
{
LedgerId = item.LedgerId,
FromDate = fromDate,
CurrencyId = currencyId,
ToDate = toDate,
ReportOf = reportOf,
Name = item.Name,
Airline = item.Airline,
Hotel = item.Hotel,
Mobile = item.Mobile,
Bus = item.Bus,
Train = item.Train,
Total = item.Total
};
informationcollection.Add(singleinformation);
}
return(informationcollection);
}
public List <SalesReportModel> GetSalesReport(int?AgentId, DateTime?fromdate, DateTime?todate, int AirlinesTypeId, int?currencyId)
{
//var data = ent.Air_GetAgentBillingStatement(AgentId, fromdate, todate, AirlinesTypeId);
var data = ent.Air_GetAgentBillingStatement(AgentId, fromdate, todate, currencyId);
List <SalesReportModel> model = new List <SalesReportModel>();
foreach (var item in data.Select(x => x))
{
var SalesReportModel = new SalesReportModel();
SalesReportModel.AirlineId = item.AirlineId;
SalesReportModel.AirlineCode = item.Code;
SalesReportModel.AirlineName = item.Name;
SalesReportModel.Cash = item.Cash;
TotalAgentBillingStatement_Cash = TotalAgentBillingStatement_Cash + (Decimal)item.Cash;
SalesReportModel.SumAgentBillingStatement_Cash = TotalAgentBillingStatement_Cash;
SalesReportModel.Tax = (decimal)item.Tax;
TotalAgentBillingStatement_Tax = TotalAgentBillingStatement_Tax + (decimal)item.Tax;
SalesReportModel.SumAgentBillingStatement_Tax = TotalAgentBillingStatement_Tax;
SalesReportModel.Commission = (decimal)item.Commission;
TotalAgentBillingStatement_Commission = TotalAgentBillingStatement_Commission + (decimal)item.Commission;
SalesReportModel.SumAgentBillingStatement_Commission = TotalAgentBillingStatement_Commission;
SalesReportModel.Payable = (decimal)item.Payable;
TotalAgentBillingStatement_Payable = TotalAgentBillingStatement_Payable + (decimal)item.Payable;
SalesReportModel.SumAgentBillingStatement_Payable = TotalAgentBillingStatement_Payable;
SalesReportModel.AgentId = item.Agentid;
model.Add(SalesReportModel);
}
return(model);
}
public JsonResult SalesGatherReports(string DateFrom, string DateTo)
{
string constr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
DataTable dt = new DataTable();
List <SalesReportModel> SlaesReport = new List <SalesReportModel>();
SqlCommand cmd = new SqlCommand("sal_Report_Gather", con);
cmd.Parameters.AddWithValue("@DateFrom", Convert.ToDateTime(DateFrom));
cmd.Parameters.AddWithValue("@DateTo", Convert.ToDateTime(DateTo));
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
SalesReportModel obj = new SalesReportModel();
obj.ItemName = dr["ItemName"].ToString();
obj.ItemTotalSaleAmount = decimal.Parse(dr["ItemTotalSaleAmount"].ToString());
SlaesReport.Add(obj);
}
ViewBag.SlaesReportTemp = SlaesReport;
return(Json(new { data = SlaesReport }, JsonRequestBehavior.AllowGet));
}
}
public JsonResult LoadSalesData(DateTime dateFrom, DateTime dateTo) {
try {
SalesReportModel model = GetSalesModel(dateFrom, dateTo);
var jsonData = new {
total = (Int32)Math.Ceiling((float)model.Records.Count),
records = model.Records.Count,
rows = model.Records.ToArray(),
graph = (
from record in model.Records
let date = Convert.ToDateTime(record.DateModified).Date
let total = record.Total
group new { DateModified = date, Total = total } by date into g
select new {
Date = g.Key.ToShortDateString(),
Sale = g.Sum(p => p.Total).ToString()
}
).ToArray()
};
return new JsonResult() { Data = jsonData, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
}
catch (Exception ex) {
base.Log(ex);
}
finally {
}
return new JsonResult() { Data = String.Empty, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
}
//for pagination//
public IQueryable <SalesReportModel> GetSalesReportByPagination(SalesReportModel m, int pageNo, out int currentPageNo, out int numberOfPages, int?flag)
{
int pageSize = 30; //(int)AirLines.Helpers.PageSize.JePageSize;
int rowCount = m.salesReportDetails.Count();
numberOfPages = (int)Math.Ceiling((decimal)rowCount / (decimal)pageSize);
if (flag != null)//Checking for next/Previous
{
if (flag == 1)
{
if (pageNo != 1) //represents previous
{
pageNo -= 1;
}
}
if (flag == 2)
{
if (pageNo != numberOfPages)//represents next
{
pageNo += 1;
}
}
}
currentPageNo = pageNo;
int rowsToSkip = (pageSize * currentPageNo) - pageSize;
IQueryable <SalesReportModel> pagingdata = m.salesReportDetails.Skip(rowsToSkip).Take(pageSize).AsQueryable();
return(pagingdata.AsQueryable());
}
public JsonResult GetStocktaking()
{
string constr = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
//List<string> ItemsList = new List<string>();
IEnumerable <string> ItemsList;
List <DataRow> list = new List <DataRow>();
using (SqlConnection con = new SqlConnection(constr))
{
DataSet ds = new DataSet();
using (SqlCommand cmd = new SqlCommand("inv_Stocktaking"))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter(cmd);
cmd.Connection = con;
con.Open();
adp.Fill(ds);
var dt1 = ds.Tables[0];
// ItemsList = dt1.ToList();
// list = dt1ToList();
//var dt2 = ds.Tables[1];
List <SalesReportModel> SlaesReport = new List <SalesReportModel>();
decimal ItemTotalSaleAmount = 0;
decimal TotalSaleQuantity = 0;
if (dt1.Rows.Count > 0)
{
foreach (DataRow item in dt1.Rows)
{
SalesReportModel obj = new SalesReportModel();
obj.ItemName = item["ItemNameAr"].ToString();
obj.ItemSalePrice = decimal.Parse(item["ItemSalePrice"].ToString());
obj.SaleQuantity = decimal.Parse(item["ItemQuantity"].ToString());
obj.ItemTotalSaleAmount = decimal.Parse(item["TotslItemPrice"].ToString());
SlaesReport.Add(obj);
ItemTotalSaleAmount += decimal.Parse(item["TotalItemPrice"].ToString());
TotalSaleQuantity += decimal.Parse(item["ItemQuantity"].ToString());
}
}
Session["TotalSaleQuantity"] = TotalSaleQuantity;
Session["ItemTotalSaleAmount"] = ItemTotalSaleAmount;
// ViewBag.TotalSaleQuantity = TotalSaleQuantity;
return(Json(new { data = SlaesReport, TotalSaleQuantity = TotalSaleQuantity, ItemTotalSaleAmount = ItemTotalSaleAmount }, JsonRequestBehavior.AllowGet));
}
}
}
}//end of display
public static async Task <List <SalesReportModel> > GetSalesReport(SalesReportModel sales)
{
if (sales.SearchFromDate == null)
{
sales.SearchFromDate = "";
}
if (sales.SearchToDate == null)
{
sales.SearchToDate = "";
}
List <SalesReportModel> Sales = new List <SalesReportModel>();
using (SqlConnection dbConn = new SqlConnection(connectionString))
{
SqlDataReader reader;
try
{
dbConn.Open();
SqlCommand cmd = new SqlCommand("SP_GetSalesReports", dbConn);
cmd.CommandType = CommandType.StoredProcedure;
//passing values from the model to the SP
cmd.Parameters.AddWithValue("@PatientID", sales.PatientID);
cmd.Parameters.AddWithValue("@ProductID", sales.ProductID);
cmd.Parameters.AddWithValue("@SearchFromDate", sales.SearchFromDate);
cmd.Parameters.AddWithValue("@SearchToDate", sales.SearchToDate);
reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
while (reader.Read())
{
SalesReportModel reportItem = new SalesReportModel();
reportItem.ID = reader.GetInt32(0);
//reportItem.CartHeaderID = reader.GetInt32(1);
//reportItem.UserID = reader.GetInt32(2);
reportItem.PatientName = reader.GetString(1);
reportItem.TotalPrice = reader.GetDecimal(2);
reportItem.TotalBill = reader.GetDecimal(3);
reportItem.SalesDate = reader.GetDateTime(4);
Sales.Add(reportItem);
}
}
}
catch (Exception ex)
{
reader = null;
Console.WriteLine(ex);
}
finally
{
dbConn.Close();
}
return(Sales);
}
}//end of display
public ActionResult Sales()
{
var userRepo = UserRepositoryFactory.GetRepository();
var model = new SalesReportModel();
model.Users = userRepo.GetAll();
return(View(model));
}
public List <SalesReportModel> GetDistributorSalesReportSummary(int?AgentId, DateTime?fromdate, DateTime?todate, int AirlinesTypeId, int?currencyId)
{
ATLTravelPortal.Areas.Administrator.Repository.DistributorManagementProvider distributorManagementProvider = new ATLTravelPortal.Areas.Administrator.Repository.DistributorManagementProvider();
ATLTravelPortal.Areas.Administrator.Repository.BranchOfficeManagementProvider branchOfficeManagementProvider = new ATLTravelPortal.Areas.Administrator.Repository.BranchOfficeManagementProvider();
List <Air_GetAgentBillingStatement_Result> data = ent.Air_GetAgentBillingStatement(null, fromdate, todate, currencyId).ToList();
var ts = SessionStore.GetTravelSession();
var agentsByBranchOffice = branchOfficeManagementProvider.GetAllAgentsByBranchOfficeId(ts.LoginTypeId);
List <Air_GetAgentBillingStatement_Result> result = new List <Air_GetAgentBillingStatement_Result>();
if (AgentId != null)
{
int appUserId = branchOfficeManagementProvider.GetAppUserIdByDistributorId(AgentId.Value);
var agentsByDistributor = distributorManagementProvider.GetAllAgentsByDistributorId(appUserId);
result = agentsByDistributor.SelectMany(b => data.Where(x => x.Agentid == b.AgentId)).ToList();
}
else
{
result = agentsByBranchOffice.SelectMany(b => data.Where(x => x.Agentid == b.AgentId)).ToList();
}
List <SalesReportModel> model = new List <SalesReportModel>();
foreach (var item in result)
{
var SalesReportModel = new SalesReportModel();
SalesReportModel.AirlineId = item.AirlineId;
SalesReportModel.AirlineCode = item.Code;
SalesReportModel.AirlineName = item.Name;
SalesReportModel.Cash = item.Cash;
TotalAgentBillingStatement_Cash = TotalAgentBillingStatement_Cash + (Decimal)item.Cash;
SalesReportModel.SumAgentBillingStatement_Cash = TotalAgentBillingStatement_Cash;
SalesReportModel.Tax = (decimal)item.Tax;
TotalAgentBillingStatement_Tax = TotalAgentBillingStatement_Tax + (decimal)item.Tax;
SalesReportModel.SumAgentBillingStatement_Tax = TotalAgentBillingStatement_Tax;
SalesReportModel.Commission = (decimal)item.Commission;
TotalAgentBillingStatement_Commission = TotalAgentBillingStatement_Commission + (decimal)item.Commission;
SalesReportModel.SumAgentBillingStatement_Commission = TotalAgentBillingStatement_Commission;
SalesReportModel.Payable = (decimal)item.Payable;
TotalAgentBillingStatement_Payable = TotalAgentBillingStatement_Payable + (decimal)item.Payable;
SalesReportModel.SumAgentBillingStatement_Payable = TotalAgentBillingStatement_Payable;
SalesReportModel.AgentId = item.Agentid;
model.Add(SalesReportModel);
}
return(model);
}
public ActionResult Index(ExportModel Expmodel, SalesReportModel model, FormCollection frm, int?pageNo, int?flag)
{
var ts = (TravelSession)Session["TravelPortalSessionInfo"];
model.salesReportSummary = ser.GetIndianLccSalesReport(model.AgentId, model.FromDate, model.ToDate, model.AirlineTypesId, 1);
model.salesReportDetails = ser.GetIndianLccSalesReportDetails(model.AgentId, model.FromDate, model.ToDate, model.AirlineTypesId, 1);
//export
ctrlBKT.GetExportTypeClicked(Expmodel, frm);
if (Expmodel != null &&
(Expmodel.ExportTypeExcel != null ||
Expmodel.ExportTypeWord != null ||
Expmodel.ExportTypeCSV != null ||
Expmodel.ExportTypePdf != null))
{
try
{
if (Expmodel.ExportTypeExcel != null)
{
Expmodel.ExportTypeExcel = Expmodel.ExportTypeExcel;
}
else if (Expmodel.ExportTypeWord != null)
{
Expmodel.ExportTypeWord = Expmodel.ExportTypeWord;
}
else if (Expmodel.ExportTypePdf != null)
{
Expmodel.ExportTypePdf = Expmodel.ExportTypePdf;
}
var exportData = model.salesReportSummary.Select(m => new
{
Code = m.AirlineCode,
Cash = m.Cash,
Tax = m.Tax,
Comm = m.Commission,
Payable = m.Payable
});
App_Class.AppCollection.Export(Expmodel, exportData, "SalesReport");
}
catch (Exception ex)
{
TempData["ActionResponse"] = ex.Message;
}
}
ViewData["AgentList"] = new SelectList(defaultProvider.GetAgentList(), "AgentId", "AgentName");
ViewData["AirlineTypes"] = new SelectList(ser.GetAirlineTypesList(), "AirineTypeId", "TypeName");
return(View(model));
}
public ActionResult Index(DateTime?FromDate, DateTime?ToDate)
{
SalesReportModel model = new SalesReportModel();
model.FromDate = DateTime.Now.AddDays(-15);
model.ToDate = DateTime.Now;
model.ReportsOfOption = salesReportProvider.GetReportof();
model.CurrencyOption = salesReportProvider.Getcurrencylist();
return(View(model));
}
public List <SalesReportModel> GetSalesReportDetails(int?AgentId, DateTime?fromdate, DateTime?todate, int AirlinesTypeId, int?currencyId)
{
var data = ent.Air_GetAgentBillingStatementDetails(AgentId, fromdate, todate, currencyId);
List <SalesReportModel> model = new List <SalesReportModel>();
foreach (var item in data.Select(x => x))
{
var SalesReportModel = new SalesReportModel();
SalesReportModel.AirlineCode = item.Code;
SalesReportModel.AirlineName = item.Name;
SalesReportModel.TicketNumber = item.TicketNumber;
SalesReportModel.IssuedDate = item.IssueDate;
SalesReportModel.Cash = item.Cash;
TotalAgentBillingStatementDetails_Cash = TotalAgentBillingStatementDetails_Cash + (decimal)item.Cash;
SalesReportModel.SumAgentBillingStatementDetails_Cash = TotalAgentBillingStatementDetails_Cash;
SalesReportModel.Tax = (decimal)item.Tax;
TotalAgentBillingStatementDetails_Tax = TotalAgentBillingStatementDetails_Tax + (decimal)item.Tax;
SalesReportModel.SumAgentBillingStatementDetails_Tax = TotalAgentBillingStatementDetails_Tax;
SalesReportModel.Commission = (decimal)item.Commission;
TotalAgentBillingStatementDetails_Commission = TotalAgentBillingStatementDetails_Commission + (decimal)item.Commission;
SalesReportModel.SumAgentBillingStatementDetails_Commission = TotalAgentBillingStatementDetails_Commission;
SalesReportModel.Payable = (decimal)item.Payable;
TotalAgentBillingStatementDetails_Payable = TotalAgentBillingStatementDetails_Payable + (decimal)item.Payable;
SalesReportModel.SumAgentBillingStatementDetails_Payable = TotalAgentBillingStatementDetails_Payable;
SalesReportModel.AgentName = item.Agent;
SalesReportModel.serviceProviderName = item.ServiceProviderName;
SalesReportModel.issueFrom = item.IssueFrom;
SalesReportModel.AgentId = item.AgentId;
var agentinfo = agencyProvider.GetAgentInfo(item.AgentId);
SalesReportModel.AgentCode = agentinfo.AgentCode;
var distributor = distributorManagementProvider.GetDistributorByDistributorId(agentinfo.DistributorId);
if (distributor != null)
{
SalesReportModel.DistributorName = distributor.DistributorName + "(" + distributor.DistributorCode + ")";
}
var branchOffice = branchOfficeManagementProvider.GetBranchOfficeInfo(agentinfo.BranchOfficeId);
if (branchOffice != null)
{
SalesReportModel.BranchOfficeName = branchOffice.BranchOfficeName + "(" + branchOffice.BranchOfficeCode + ")";
}
model.Add(SalesReportModel);
}
return(model);
}
public ActionResult UploadFile()
{
if (Request.Files.Count != 1)
{
return(Json(new SalesReportModel()));
}
var file = new StreamReader(Request.Files[0].InputStream, System.Text.Encoding.Default, true);
var rawlines = file.ReadToEnd();
return(Json(SalesReportModel.CreateFromFile(Request.Files[0].FileName, rawlines)));
}
public IEnumerable <SalesReportModel> GetSales(SalesReportParametersModel parameters)
{
List <SalesReportModel> _sales = new List <SalesReportModel>();
using (var conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
string query = "SELECT Salesperson, SUM(PurchasePrice) AS SalesTotal, Count(SaleID) AS VehicleTotal" +
" FROM Sale" +
" WHERE 1 = 1";
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
if (!string.IsNullOrEmpty(parameters.Salesperson))
{
query += " AND Salesperson >= @User ";
cmd.Parameters.AddWithValue("@User", parameters.Salesperson);
}
if (parameters.FromDate.HasValue)
{
query += " AND Date >= @FromDate ";
cmd.Parameters.AddWithValue("@FromDate", parameters.FromDate.Value);
}
if (parameters.ToDate.HasValue)
{
query += " AND Date <= @ToDate ";
cmd.Parameters.AddWithValue("@ToDate", parameters.ToDate.Value);
}
query += " GROUP BY Salesperson ORDER BY Salesperson";
cmd.CommandText = query;
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
SalesReportModel row = new SalesReportModel();
row.Salesperson = reader["Salesperson"].ToString();
row.SalesTotal = (decimal)reader["SalesTotal"];
row.VehicleTotal = (int)reader["VehicleTotal"];
_sales.Add(row);
}
}
}
return(_sales.AsEnumerable());
}
public List <SalesReportModel> GetSalesReportSummaryDetails(int?AgentId, DateTime?fromdate, DateTime?todate, int AirlinesTypeId, int?currencyId)
{
ATLTravelPortal.Areas.Administrator.Repository.DistributorManagementProvider distributorManagementProvider = new ATLTravelPortal.Areas.Administrator.Repository.DistributorManagementProvider();
List <Air_GetAgentBillingStatementDetails_Result> data = ent.Air_GetAgentBillingStatementDetails(AgentId, fromdate, todate, currencyId).ToList();
var ts = SessionStore.GetTravelSession();
var agentsByDistributor = distributorManagementProvider.GetAllAgentsByDistributorId(ts.AppUserId);
var resultSummary = agentsByDistributor.SelectMany(b => data.Where(x => x.AgentId == b.AgentId));
List <SalesReportModel> model = new List <SalesReportModel>();
foreach (var item in resultSummary)
{
var SalesReportModel = new SalesReportModel();
SalesReportModel.AirlineCode = item.Code;
SalesReportModel.AirlineName = item.Name;
SalesReportModel.TicketNumber = item.TicketNumber;
SalesReportModel.IssuedDate = item.IssueDate;
SalesReportModel.Cash = item.Cash;
TotalAgentBillingStatementDetails_Cash = TotalAgentBillingStatementDetails_Cash + (decimal)item.Cash;
SalesReportModel.SumAgentBillingStatementDetails_Cash = TotalAgentBillingStatementDetails_Cash;
SalesReportModel.Tax = (decimal)item.Tax;
TotalAgentBillingStatementDetails_Tax = TotalAgentBillingStatementDetails_Tax + (decimal)item.Tax;
SalesReportModel.SumAgentBillingStatementDetails_Tax = TotalAgentBillingStatementDetails_Tax;
SalesReportModel.Commission = (decimal)item.Commission;
TotalAgentBillingStatementDetails_Commission = TotalAgentBillingStatementDetails_Commission + (decimal)item.Commission;
SalesReportModel.SumAgentBillingStatementDetails_Commission = TotalAgentBillingStatementDetails_Commission;
SalesReportModel.Payable = (decimal)item.Payable;
TotalAgentBillingStatementDetails_Payable = TotalAgentBillingStatementDetails_Payable + (decimal)item.Payable;
SalesReportModel.SumAgentBillingStatementDetails_Payable = TotalAgentBillingStatementDetails_Payable;
SalesReportModel.AgentName = item.Agent;
SalesReportModel.serviceProviderName = item.ServiceProviderName;
SalesReportModel.issueFrom = item.IssueFrom;
SalesReportModel.AgentId = item.AgentId;
SalesReportModel.AgentCode = new ATLTravelPortal.Areas.Airline.Repository.BookedTicketReportProvider().GetAgentCodeById(item.AgentId);
model.Add(SalesReportModel);
}
return(model);
}
private SalesReportModel GetSalesModel(DateTime dateFrom, DateTime dateTo) {
SalesReportModel model = null;
try {
model = new SalesReportModel();
model.From = dateFrom;
model.To = dateTo;
model.Records = _reportsService.SalesDataSet(dateFrom, dateTo);
return model;
}
catch (Exception ex) {
base.Log(ex);
}
finally {
}
return null;
}
public ActionResult Index(int?pageNo, int?flag)
{
var ts = (TravelSession)Session["TravelPortalSessionInfo"];
SalesReportModel model = new SalesReportModel();
ViewData["AgentList"] = new SelectList(defaultProvider.GetAgentList(), "AgentId", "AgentName");
ViewData["AirlineTypes"] = new SelectList(ser.GetAirlineTypesList(), "AirineTypeId", "TypeName");
model.FromDate = DateTime.Now.AddDays(-15);
model.ToDate = DateTime.Now;
model.salesReportSummary = ser.GetIndianLccSalesReport(model.AgentId, model.FromDate, model.ToDate, model.AirlineTypesId, 1);
model.salesReportDetails = ser.GetIndianLccSalesReportDetails(model.AgentId, model.FromDate, model.ToDate, model.AirlineTypesId, 1);
return(View(model));
}
public ActionResult Details(SalesReportModel model, string reportOf, int?currencyid, DateTime?fromdate, DateTime?todate, int?ledgerid, string name, int reportType)
{
model.ReportHeading = name;
model.FromDate = fromdate;
if (model.CurrencyId == 1)
{
model.Currency = "NPR";
}
if (model.CurrencyId == 2)
{
model.Currency = "USD";
}
model.ToDate = todate;
model.ReportType = reportType;
model.InformationList = salesReportProvider.GetLedgerInformation(ledgerid, reportOf, currencyid, fromdate, todate, reportType);
return(View(model));
}
public async Task <IHttpActionResult> GetSalesReport(SalesReportModel sales)
{
CommonResponse validatedResponse = await AuthService.ValidateUserAndToken();
if (!validatedResponse.IsError)
{
var Sales = await SalesService.GetSalesReport(sales);
if (Sales.Count > 0)
{
return(Ok(Sales));
}
else
{
return(BadRequest("No Sales Exists!"));
}
}
else
{
return(Unauthorized());
}
}//end of get
public List <SalesReportModel> GetIndianLccSalesReportDetails(int?AgentId, DateTime?fromdate, DateTime?todate, int AirlinesTypeId, int?currencyId)
{
var data = ent.Air_GetAgentBillingStatementDetails(AgentId, fromdate, todate, currencyId).Where(x => x.ServiceProviderName == "TBO");
List <SalesReportModel> model = new List <SalesReportModel>();
foreach (var item in data.Select(x => x))
{
var SalesReportModel = new SalesReportModel();
SalesReportModel.AirlineCode = item.Code;
SalesReportModel.AirlineName = item.Name;
SalesReportModel.TicketNumber = item.TicketNumber;
SalesReportModel.IssuedDate = item.IssueDate;
SalesReportModel.Cash = item.Cash;
TotalAgentBillingStatementDetails_Cash = TotalAgentBillingStatementDetails_Cash + (decimal)item.Cash;
SalesReportModel.SumAgentBillingStatementDetails_Cash = TotalAgentBillingStatementDetails_Cash;
SalesReportModel.Tax = (decimal)item.Tax;
TotalAgentBillingStatementDetails_Tax = TotalAgentBillingStatementDetails_Tax + (decimal)item.Tax;
SalesReportModel.SumAgentBillingStatementDetails_Tax = TotalAgentBillingStatementDetails_Tax;
SalesReportModel.Commission = (decimal)item.Commission;
TotalAgentBillingStatementDetails_Commission = TotalAgentBillingStatementDetails_Commission + (decimal)item.Commission;
SalesReportModel.SumAgentBillingStatementDetails_Commission = TotalAgentBillingStatementDetails_Commission;
SalesReportModel.Payable = (decimal)item.Payable;
TotalAgentBillingStatementDetails_Payable = TotalAgentBillingStatementDetails_Payable + (decimal)item.Payable;
SalesReportModel.SumAgentBillingStatementDetails_Payable = TotalAgentBillingStatementDetails_Payable;
SalesReportModel.AgentName = item.Agent;
SalesReportModel.serviceProviderName = item.ServiceProviderName;
SalesReportModel.issueFrom = item.IssueFrom;
model.Add(SalesReportModel);
}
return(model);
}
public ActionResult Index(int?pageNo, int?flag)
{
var ts = SessionStore.GetTravelSession();
var agentsByBranchOffice = branchOfficeManagementProvider.GetAllAgentsByBranchOfficeId(ts.LoginTypeId);
SalesReportModel model = new SalesReportModel();
ViewData["AgentList"] = new SelectList(defaultProvider.GetDistributorList(ts.LoginTypeId), "DistributorId", "DistributorName");
ViewData["AirlineTypes"] = new SelectList(ser.GetAirlineTypesList(), "AirineTypeId", "TypeName");
ViewData["Currency"] = defaultProvider.GetCurrencyList();
model.FromDate = DateTime.Now.AddDays(-15);
model.ToDate = DateTime.Now;
model.salesReportSummary = ser.GetDistributorSalesReportSummary(model.AgentId, model.FromDate, model.ToDate, model.AirlineTypesId, model.Currency);
model.salesReportDetails = ser.GetDistributorSalesReportSummaryDetails(model.AgentId, model.FromDate, model.ToDate, model.AirlineTypesId, model.Currency);;
return(View(model));
}
public ActionResult Index(ExportModel Expmodel, SalesReportModel model, FormCollection frm, int?pageNo, int?flag)
{
var ts = SessionStore.GetTravelSession();
var detailsSummary = ser.GetSalesReportSummary(model.AgentId, model.FromDate, model.ToDate, model.AirlineTypesId, model.Currency);
model.salesReportSummary = detailsSummary;
var details = ser.GetSalesReportSummaryDetails(model.AgentId, model.FromDate, model.ToDate, model.AirlineTypesId, model.Currency);
model.salesReportDetails = details;
//export
ctrlBKT.GetExportTypeClicked(Expmodel, frm);
if (Expmodel != null &&
(Expmodel.ExportTypeExcel != null ||
Expmodel.ExportTypeWord != null ||
Expmodel.ExportTypeCSV != null ||
Expmodel.ExportTypePdf != null))
{
try
{
if (Expmodel.ExportTypeExcel != null)
{
Expmodel.ExportTypeExcel = Expmodel.ExportTypeExcel;
}
else if (Expmodel.ExportTypeWord != null)
{
Expmodel.ExportTypeWord = Expmodel.ExportTypeWord;
}
else if (Expmodel.ExportTypePdf != null)
{
Expmodel.ExportTypePdf = Expmodel.ExportTypePdf;
}
var exportData = model.salesReportSummary.Select(m => new
{
Code = m.AirlineCode,
Cash = m.Cash,
Tax = m.Tax,
Comm = m.Commission,
Payable = m.Payable
});
App_Class.AppCollection.Export(Expmodel, exportData, "SalesReport");
}
catch (Exception ex)
{
TempData["ActionResponse"] = ex.Message;
}
}
var agents = defaultProvider.GetAgentList().Where(x => x.CreatedBy == ts.AppUserId);
ViewData["AgentList"] = new SelectList(agents, "AgentId", "AgentName");
ViewData["AirlineTypes"] = new SelectList(ser.GetAirlineTypesList(), "AirineTypeId", "TypeName");
ViewData["Currency"] = defaultProvider.GetCurrencyList();
return(View(model));
}
public SalesAndShiftInfo()
{
InitializeComponent();
BindingContext = ViewModel = new SalesReportModel(Navigation);
}
public async Task <SalesTaxDetailsReportDto> GetSalesTaxReportAsync(SalesReportModel model)
{
var pageResult = await _reportManager.GetSalesTaxReportAsync(model);
return(pageResult);
}
public SalesReportDataModel GetSalesReport(int yearFromNumber, int yearToNumber,
int monthFromNumber, int monthToNumber,
EntityEnum.DocumentTypeEnum docType, EntityEnum.ReportTypeEnum reportType, int employeeId)
{
var result = new List <SalesReportModel>();
var data = new SalesReportDataModel();
List <SalesReportPeriodModel> periods = new List <SalesReportPeriodModel>();
if (monthFromNumber > 12 || monthToNumber > 12 || monthToNumber < 1 || monthFromNumber < 1 ||
yearFromNumber < 1901 || yearToNumber < 1901)
{
throw new ArgumentException();
}
string yearMonthFrom = SqlHelper.GetDateString(monthFromNumber, yearFromNumber);
string yearMonthTo = SqlHelper.GetDateString(monthToNumber, yearToNumber);
using (SqlConnection connection = SqlHelper.GetConnection(ConnectionString))
{
string datePartInSelect = " ", datePartInGroupBy = " ", datePartInOrderBy = " ",
datePartCreatedOf = ", [CreatedOf] " /*, employeeStr = ""*/;
if (reportType == EntityEnum.ReportTypeEnum.ByMonth)
{
datePartInSelect = " DATEPART(Year, CreatedOf) Year, DATEPART(Month, CreatedOf) Month, ";
datePartInGroupBy = " DATEPART(Year, CreatedOf), DATEPART(Month, CreatedOf), ";
datePartInOrderBy = ", Year, Month ";
datePartCreatedOf = " ";
}
using (SqlCommand command = connection.GetCommand(" " +
"SELECT " + datePartInSelect + " [Document].[ContractorId] AS DocumentContractorId, " +
"[Contractor].Code AS ContractorCode " + datePartCreatedOf + ", SUM([Sum]) AS PurchaseSum, " +
"SUM([SaleSum]) AS SaleSum " +
"FROM [Document] " +
"INNER JOIN Contractor on Document.ContractorId = Contractor.ContractorId " +
"WHERE DocumentTypeId = " + (int)docType + " AND IsCommitted = 1 " + GetEmployeeIdWhereString(employeeId) +
"AND CreatedOf >= '" + yearMonthFrom + "' AND CreatedOf < '" + yearMonthTo + "' " +
"GROUP BY " + datePartInGroupBy + " [Contractor].Code " + datePartCreatedOf + ",[Document].[ContractorId] " +
"ORDER BY [Document].[ContractorId] ASC " + datePartInOrderBy + " " + datePartCreatedOf
, CommandType.Text))
{
using (SqlDataReader reader = command.ExecuteReader())
{
SalesReportModel item = null;
while (reader.Read())
{
DateTime createdOf = GetDate(reader, reportType);
decimal purchaseSum = Convert.ToDecimal(reader["PurchaseSum"]);
decimal saleSum = Convert.ToDecimal(reader["SaleSum"]);
int contractorId = Int32.Parse(reader["DocumentContractorId"].ToString());
string contractorCode = reader["ContractorCode"].ToString();
if (periods.Where(x => x.Period == createdOf).FirstOrDefault() == null)
{
periods.Add(
new SalesReportPeriodModel
{
Period = createdOf,
ReportItem = GetReportByDate(createdOf, docType, reportType, employeeId),
});
}
if (item == null)
{
item = new SalesReportModel {
ContractorModel = new ContractorCodeIdModel {
ContractorCode = contractorCode,
ContractorId = contractorId,
},
Refunds = GetTotalReport(contractorId, yearMonthFrom, yearMonthTo, EntityEnum.DocumentTypeEnum.Refunds, employeeId),
SubTotal = GetTotalReport(contractorId, yearMonthFrom, yearMonthTo, EntityEnum.DocumentTypeEnum.Shipment, employeeId),
}
}
;
if (item.ContractorModel.ContractorId != contractorId)
{
result.Add(item);
item = new SalesReportModel
{
ContractorModel = new ContractorCodeIdModel
{
ContractorCode = contractorCode,
ContractorId = contractorId
},
Refunds = GetTotalReport(contractorId, yearMonthFrom, yearMonthTo, EntityEnum.DocumentTypeEnum.Refunds, employeeId),
SubTotal = GetTotalReport(contractorId, yearMonthFrom, yearMonthTo, EntityEnum.DocumentTypeEnum.Shipment, employeeId),
ReportItems = new List <SalesReportModelItem>()
};
}
if (item.ReportItems == null)
{
item.ReportItems = new List <SalesReportModelItem>();
}
item.ReportItems.Add(new SalesReportModelItem
{
CreatedOf = createdOf,
PurchaseSum = purchaseSum,
SaleSum = saleSum
});
}
// Check if contractor already exists
result.Add(item);
}
}
}
/*
* string yearMonthFrom = SqlHelper.GetDateString(monthFromNumber,yearFromNumber);
* string yearMonthTo = SqlHelper.GetDateString(monthToNumber, yearToNumber);
*/
data.RefundsGrandTotal = GetTotalReport(null, yearMonthFrom, yearMonthTo, EntityEnum.DocumentTypeEnum.Refunds, employeeId);
data.GrandTotal = GetTotalReport(null, yearMonthFrom, yearMonthTo, EntityEnum.DocumentTypeEnum.Shipment, employeeId);
data.ReportModel = result;
//List<DateTime> datesList = dates.ToList();
//datesList.Sort();
data.Periods = periods.OrderBy(x => x.Period).ToList();
return(data);
}
}
// GET: Reports/ReportIndex
public ActionResult Index(string selectedState = "California")
{
//Let's work with ADO.Net - High Ceremony, but high-level of control
SalesReportModel model = new SalesReportModel();
//Look up a connection string by name out of web.config
string connectionString = ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
//Open the doorway to the server
connection.Open();
//Commands are used to send a statement to SQL
DbCommand quantityCommand = connection.CreateCommand();
quantityCommand.CommandText = "SELECT * FROM vw_BillingStates";
//If you're doing an update, delete, or insert, just send the query over:
//command.ExecuteNonQuery()
//Execute scalar is used if your query gives back one single value (e.g. one row with one columns)
//command.ExecuteScalar()
//Use execute reader to read data in, record by record
List <string> states = new List <string>();
using (DbDataReader reader = quantityCommand.ExecuteReader())
{
while (reader.Read())
{
states.Add(reader.GetString(0));
}
}
SqlCommand command1 = connection.CreateCommand();
quantityCommand.CommandText = @"select top 5 ProductID, SUM(LineTotal) from salesorderdetail JOIN SalesOrderHeader
ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
JOIN[Address] ON SalesOrderHeader.BillToAddressID = Address.AddressID
WHERE Address.StateProvince = '" + selectedState + "' group by ProductID";
SqlCommand command2 = connection.CreateCommand();
quantityCommand.CommandText = @"select top 5 ProductID, SUM(OrderQty) from salesorderdetail JOIN SalesOrderHeader
ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
JOIN [Address] ON SalesOrderHeader.BillToAddressID = Address.AddressID
WHERE Address.StateProvince = '" + selectedState + "' group by ProductID";
List <string> Quantity = new List <string>();
model.States = states.ToArray();
//TODO: Get these using ADO.Net
model.TopSalesByDollar = new TopSaleByDollar[0];
model.TopSalesByQuantity = new TopSaleByQuantity[0];
//Advantage of Stored Procedures : parameters cannot be used for SQL Injection attacks!
//Other advantage - SQL will cache the execution plan, as opposed to figuring it out each time you run dynamic T-SQL
quantityCommand.CommandText = "sp_TopSalesByQuantity";
quantityCommand.CommandType = System.Data.CommandType.StoredProcedure;
quantityCommand.Parameters.Add(new SqlParameter("@stateProvince", selectedState));
List <TopSaleByQuantity> quantities = new List <TopSaleByQuantity>();
//Another advantage of stored procedures - they're securable! (meaning I have to explicitly grant access to this stored procedure to the SalesUser login)
//GRANT EXEC ON SalesLT.sp_TopSalesByQuantity TO SalesUser
using (DbDataReader reader = quantityCommand.ExecuteReader())
{
int totalQuantityOrdinal = reader.GetOrdinal("TotalQuantity");
int productNameOrdinal = reader.GetOrdinal("Name");
int productIdOrdinal = reader.GetOrdinal("ProductID");
while (reader.Read())
{
quantities.Add(new TopSaleByQuantity
{
TotalQuantity = reader.GetInt32(totalQuantityOrdinal),
ProductName = reader.GetString(productNameOrdinal),
ProductID = reader.GetInt32(productIdOrdinal)
});
}
}
model.TopSalesByQuantity = quantities.ToArray();
//Make sure you close the connection when you're finished
connection.Close();
}
return(View(model));
}
public List <SalesReportModel> GetLedgerInformation(int?ledgerid, string reportof, int?currenctid, DateTime?fromdate, DateTime?todate, int reportType)
{
List <SalesReportModel> collection = new List <SalesReportModel>();
SalesReportModel model = new SalesReportModel();
if (reportType == 1)
{
var Data = entity.Air_GetSalesReport(ledgerid, reportof, currenctid, fromdate, todate);
foreach (var item in Data)
{
SalesReportModel singleone = new SalesReportModel()
{
AirlineName = item.Airline,
Amount = item.Amount,
IssuedDate = item.IssuedDate,
IssuedFrom = item.IssueFrom,
MpnrId = item.MPNRId,
Sector = item.Sector,
ServiceProviderName = item.ServiceProviderName,
TicketNumber = item.TicketNumber,
};
collection.Add(singleone);
}
}
if (reportType == 2)
{
var Data = entity.HTL_GetSalesReport(ledgerid, reportof, currenctid, fromdate, todate);
foreach (var item in Data)
{
SalesReportModel singleone = new SalesReportModel()
{
HotelName = item.HotelName,
CityName = item.CityName,
CountryName = item.CountryName,
IssuedDate = item.IssuedDate,
NOofNight = item.NoOfNights,
NoofRoom = item.NoOfRoom,
ServiceProviderName = item.ServiceProviderName,
Amount = item.Amount
};
singleone.TotalAmount = model.TotalAmount;
collection.Add(singleone);
}
}
if (reportType == 3)
{
var Data = entity.MRC_GetSalesReport(ledgerid, reportof, currenctid, fromdate, todate);
foreach (var item in Data)
{
SalesReportModel singleone = new SalesReportModel()
{
SalesTranId = item.SalesTranId,
ServiceType = item.ServiceType,
ServiceProviderName = item.ServiceProviderName,
CreatedDate = item.CreatedDate,
CustomerMobileNo = item.CustomerMobileNo,
Amount = item.Amount
};
collection.Add(singleone);
}
}
if (reportType == 4)
{
var Data = entity.Bus_GetSalesReport(ledgerid, reportof, currenctid, fromdate, todate);
foreach (var item in Data)
{
SalesReportModel singleone = new SalesReportModel()
{
Amount = item.Amount,
BusMasterName = item.BusMasterName,
BusPNRId = item.BusPNRId,
IssuedDate = item.IssuedDate,
PassengerName = item.PassengerName,
Sector = item.Sector,
ServiceProviderName = item.ServiceProviderName
};
collection.Add(singleone);
}
}
if (reportType == 5)
{
var Data = entity.Train_GetSalesReport(ledgerid, reportof, currenctid, fromdate, todate);
foreach (var item in Data)
{
SalesReportModel singleone = new SalesReportModel()
{
TrainName = item.TrainName,
PassengerName = item.PassengerName,
TrainNo = item.TrainNo,
TrainPNRId = item.TrainPNRId,
IssuedDate = item.IssuedDate,
NoOfSeat = item.NoOfSeat,
Sector = item.Sector,
Amount = item.Amount
};
collection.Add(singleone);
}
}
return(collection);
}
public ActionResult Index1(ExportModel Expmodel, SalesReportModel model, FormCollection frm, int?pageNo, int?flag)
{
var ts = SessionStore.GetTravelSession();
var agentId = frm["SalesAgentId"];
if (agentId != "")
{
model.AgentId = int.Parse(frm["SalesAgentId"]);
}
else
{
model.AgentId = null;
}
var fromdate = frm["SalesFromDate"];
if (fromdate != "")
{
model.FromDate = DateTime.Parse(frm["SalesFromDate"]);
}
else
{
model.FromDate = null;
}
var todate = frm["SalesToDate"];
if (todate != "")
{
model.ToDate = DateTime.Parse(frm["SalesToDate"]);
}
else
{
model.ToDate = null;
}
var currency = frm["SalesCurrency"];
if (currency != "")
{
model.Currency = int.Parse(frm["SalesCurrency"]);
}
else
{
model.Currency = null;
}
var detailsSummary = ser.GetSalesReportSummary(model.AgentId, model.FromDate, model.ToDate, model.AirlineTypesId, model.Currency);
model.salesReportSummary = detailsSummary;
var details = ser.GetSalesReportSummaryDetails(model.AgentId, model.FromDate, model.ToDate, model.AirlineTypesId, model.Currency);
model.salesReportDetails = details;
//export
ctrlBKT.GetExportTypeClicked(Expmodel, frm);
if (Expmodel != null &&
(Expmodel.ExportTypeExcel != null ||
Expmodel.ExportTypeWord != null ||
Expmodel.ExportTypeCSV != null ||
Expmodel.ExportTypePdf != null))
{
try
{
if (Expmodel.ExportTypeExcel != null)
{
Expmodel.ExportTypeExcel = Expmodel.ExportTypeExcel;
}
else if (Expmodel.ExportTypeWord != null)
{
Expmodel.ExportTypeWord = Expmodel.ExportTypeWord;
}
else if (Expmodel.ExportTypePdf != null)
{
Expmodel.ExportTypePdf = Expmodel.ExportTypePdf;
}
var exportData = model.salesReportDetails.Select(m => new
{
Code = m.AirlineCode,
Agent_Name = m.AgentName,
Ticket_No = m.TicketNumber,
Issued_Date = TimeFormat.DateFormat(m.IssuedDate.ToString()),
Cash = m.Cash,
Tax = m.Tax,
Comm = m.Commission,
Payable = m.Payable,
Service_Provider = m.serviceProviderName,
Issued_From = m.issueFrom
});
App_Class.AppCollection.Export(Expmodel, exportData, "SalesReport");
}
catch (Exception ex)
{
TempData["ActionResponse"] = ex.Message;
}
}
var agents = defaultProvider.GetAgentList().Where(x => x.CreatedBy == ts.AppUserId);
ViewData["AgentList"] = new SelectList(agents, "AgentId", "AgentName");
ViewData["AirlineTypes"] = new SelectList(ser.GetAirlineTypesList(), "AirineTypeId", "TypeName");
return(View(model));
}
