/// <summary>
/// Prepare sales summary list model
/// </summary>
/// <param name="searchModel">Sales summary search model</param>
/// <returns>Sales summary list model</returns>
public virtual async Task <SalesSummaryListModel> PrepareSalesSummaryListModelAsync(SalesSummarySearchModel searchModel)
{
if (searchModel == null)
{
throw new ArgumentNullException(nameof(searchModel));
}
var salesSummary = await GetSalesSummaryReportAsync(searchModel);
//prepare list model
var model = new SalesSummaryListModel().PrepareToGrid(searchModel, salesSummary, () =>
{
return(salesSummary.Select(sale =>
{
//fill in model values from the entity
var salesSummaryModel = new SalesSummaryModel
{
Summary = sale.Summary,
NumberOfOrders = sale.NumberOfOrders,
ProfitStr = sale.ProfitStr,
Shipping = sale.Shipping,
Tax = sale.Tax,
OrderTotal = sale.OrderTotal
};
return salesSummaryModel;
}));
});
return(model);
}
public HttpResponseMessage BillNoBySale(SalesSummaryModel model)
{
ResponseStatus response = new ResponseStatus();
try
{
if (model.Location != "" || model.Location != null)
{
var data = _repository.BillNoBySale(model);
if (data.Count() > 0)
{
response.isSuccess = true;
response.serverResponseTime = System.DateTime.Now;
return(Request.CreateResponse(HttpStatusCode.OK, new { data, response }));
}
else
{
response.isSuccess = false;
response.serverResponseTime = System.DateTime.Now;
return(Request.CreateResponse(HttpStatusCode.BadRequest, new { response }));
}
}
else
{
return(Request.CreateErrorResponse(HttpStatusCode.BadRequest, "Something Worng !"));
}
}
catch (Exception ex)
{
return(Request.CreateErrorResponse(HttpStatusCode.BadRequest, "Something Worng !", ex));
}
}
public IEnumerable <TodayAndLastWeekSalesModel> TodayAndLastWeekSale(SalesSummaryModel model)
{
List <TodayAndLastWeekSalesModel> data = new List <TodayAndLastWeekSalesModel>();
MultipleDataBase dataAccess = new MultipleDataBase();
using (mom_androidEntities context = new mom_androidEntities())
{
var user = context.userdatabasemasters.Where(u => u.DataBaseId == model.DataBaseId).FirstOrDefault();
data = dataAccess.TodayAndLastWeekSale(model, user.DataBase).ToList();
}
return(data);
}
public IEnumerable <BillNoBySaleModel> BillNoBySale(SalesSummaryModel model, string databasename)
{
string strConnection = "";
strConnection = "Server=localhost;user id=root;password=pari@123;Database=" + databasename + "";
List <BillNoBySaleModel> data = new List <BillNoBySaleModel>();
MySqlConnection myConn = new MySqlConnection();
MySqlCommand myCmd = new MySqlCommand();
MySqlDataReader myRdr = null;
try
{
myConn.ConnectionString = strConnection;
myConn.Open();
myCmd.Connection = myConn;
myCmd.CommandType = CommandType.Text;
myCmd.CommandText = " Select INV_DT,INV_NO,CASH,SUM(AMOUNT_N) as TOTAL FROM tbltotalsales " +
" where LOCATION = '" + model.Location + "' " +
" AND(INV_DT >= '" + model.FromDate.ToString("yyyy-MM-dd") + "' AND INV_DT <= '" + model.ToDate.ToString("yyyy-MM-dd") + "') " +
" AND CASH != 'CANCEL' AND CASH != 'NC' AND CASH != 'CH' " +
" GROUP BY INV_DT,INV_NO,CASH" +
" Order by INV_DT,INV_NO,CASH";
myRdr = myCmd.ExecuteReader();
if (myRdr.HasRows)
{
while (myRdr.Read())
{
BillNoBySaleModel singledata = new BillNoBySaleModel();
singledata.BillDate = Convert.ToDateTime(myRdr["INV_DT"].ToString());
singledata.BillNo = myRdr["INV_NO"].ToString();
singledata.PaymentType = myRdr["CASH"].ToString();
singledata.Total = Convert.ToDouble(myRdr["TOTAL"].ToString());
data.Add(singledata);
}
}
}
catch (Exception ex)
{
}
finally
{
if (myRdr != null)
{
myRdr.Close();
myRdr.Dispose();
myRdr = null;
}
if (myCmd != null)
{
myCmd.Dispose();
myCmd = null;
}
if (myConn != null)
{
myConn.Close();
myConn.Dispose();
myConn = null;
}
}
return(data);
}
public IEnumerable <TodayAndLastWeekSalesModel> TodayAndLastWeekSale(SalesSummaryModel model, string databasename)
{
string strConnection = "";
strConnection = "Server=localhost;user id=root;password=pari@123;Database=" + databasename + "";
List <TodayAndLastWeekSalesModel> data = new List <TodayAndLastWeekSalesModel>();
MySqlConnection myConn = new MySqlConnection();
MySqlCommand myCmd = new MySqlCommand();
MySqlDataReader myRdr = null;
try
{
myConn.ConnectionString = strConnection;
myConn.Open();
myCmd.Connection = myConn;
myCmd.CommandType = CommandType.Text;
myCmd.CommandText = " Select " +
" (Select SUM(AMOUNT_N) from tbltotalsales " +
" where LOCATION = '" + model.Location + "' AND(INV_DT >= '" + model.FromDate.ToString("yyyy-MM-dd") + "' AND INV_DT <= '" + model.FromDate.ToString("yyyy-MM-dd") + "') " +
" AND CASH != 'CANCEL') as TODAYTOTALSALE, " +
" (Select SUM(AMOUNT_N) from tbltotalsales " +
" where LOCATION = '" + model.Location + "' AND(INV_DT >= DATE('" + model.FromDate.ToString("yyyy-MM-dd") + "') - INTERVAL 7 DAY AND INV_DT <= '" + model.FromDate.ToString("yyyy-MM-dd") + "') " +
" AND CASH != 'CANCEL') as LASTWEEKSALE;";
myRdr = myCmd.ExecuteReader();
if (myRdr.HasRows)
{
while (myRdr.Read())
{
TodayAndLastWeekSalesModel singledata = new TodayAndLastWeekSalesModel();
singledata.TodayTotalSales = Convert.ToDouble(myRdr["TODAYTOTALSALE"].ToString());
singledata.LastWeekTotalSales = Convert.ToDouble(myRdr["LASTWEEKSALE"].ToString());
data.Add(singledata);
}
}
}
catch (Exception ex)
{
}
finally
{
if (myRdr != null)
{
myRdr.Close();
myRdr.Dispose();
myRdr = null;
}
if (myCmd != null)
{
myCmd.Dispose();
myCmd = null;
}
if (myConn != null)
{
myConn.Close();
myConn.Dispose();
myConn = null;
}
}
return(data);
}
public IEnumerable <CategoryBySaleModel> CategoryBySale(SalesSummaryModel model, string databasename)
{
string strConnection = "";
strConnection = "Server=localhost;user id=root;password=pari@123;Database=" + databasename + "";
List <CategoryBySaleModel> data = new List <CategoryBySaleModel>();
MySqlConnection myConn = new MySqlConnection();
MySqlCommand myCmd = new MySqlCommand();
MySqlDataReader myRdr = null;
try
{
myConn.ConnectionString = strConnection;
myConn.Open();
myCmd.Connection = myConn;
myCmd.CommandType = CommandType.Text;
myCmd.CommandText = " Select CATAGORY,PRODUCT_NAME,SUM(QTY) as TotalCount,SUM(AMOUNT) as TOTAL FROM tbltotalsales " +
" where LOCATION = '" + model.Location + "' " +
" AND(INV_DT >= '" + model.FromDate.ToString("yyyy-MM-dd") + "' AND INV_DT <= '" + model.ToDate.ToString("yyyy-MM-dd") + "') " +
" GROUP BY CATAGORY,PRODUCT_NAME" +
" Order by CATAGORY,PRODUCT_NAME";
myRdr = myCmd.ExecuteReader();
if (myRdr.HasRows)
{
while (myRdr.Read())
{
CategoryBySaleModel singledata = new CategoryBySaleModel();
singledata.CategoryName = myRdr["CATAGORY"].ToString();
singledata.ProductName = myRdr["PRODUCT_NAME"].ToString();
singledata.Quantity = Convert.ToDouble(myRdr["TotalCount"].ToString());
singledata.Amount = Convert.ToDouble(myRdr["TOTAL"].ToString());
data.Add(singledata);
}
}
}
catch (Exception ex)
{
}
finally
{
if (myRdr != null)
{
myRdr.Close();
myRdr.Dispose();
myRdr = null;
}
if (myCmd != null)
{
myCmd.Dispose();
myCmd = null;
}
if (myConn != null)
{
myConn.Close();
myConn.Dispose();
myConn = null;
}
}
return(data);
}
