SQL
CREATE PROCEDURE soFoo
(
@PageSize INT = NULL,
@CurrentPage INT,
@firstname VARCHAR(50),
@lastname VARCHAR(50),
@providerid int,
@clientid int ,
@patientChart VARCHAR(50) ,
@status int
)
AS
BEGIN
DECLARE @Skip INT
DECLARE @Take INT
DECLARE @SQL VARCHAR(MAX)
if(@providerid <= 0)
BEGIN
SET @providerid =null
END
if(@status < 0)
BEGIN
SET @status=null
END
IF(LEN(@lastname)=0)
BEGIN
SET @lastname=null
END
else
begin
SET @lastname='%'+@lastname+'%'
end
SET @Skip = (@CurrentPage - 1) * @PageSize
SET @Take = @CurrentPage * @PageSize
SELECT *,(select statename from states where states.stateid=A.stateid) as statename FROM (SELECT ROW_NUMBER() OVER
( ORDER BY IsActive desc) rownumber,*
from Patient
where isnull(firstname,'') like coalesce(@firstname,firstname,'')
and isnull(lastname,'') like coalesce(@lastname,lastname,'')
and patientChart = coalesce(@patientChart,patientChart,'')
and IsActive=coalesce(@status,IsActive,'')
AND Patient.clientid=@clientid
) A
WHERE A.RowNumber > @Skip AND A.RowNumber <= @Take
END
CREATE PROCEDURE soFoo
(
@PageSize INT = NULL,
@CurrentPage INT,
@firstname VARCHAR(50),
@lastname VARCHAR(50),
@providerid int,
@clientid int ,
@patientChart VARCHAR(50) ,
@status int
)
AS
BEGIN
DECLARE @Skip INT
DECLARE @Take INT
DECLARE @SQL VARCHAR(MAX)
if(@providerid <= 0)
BEGIN
SET @providerid =null
END
if(@status < 0)
BEGIN
SET @status=null
END
IF(LEN(@lastname)=0)
BEGIN
SET @lastname=null
END
else
begin
SET @lastname='%'+@lastname+'%'
end
SET @Skip = (@CurrentPage - 1) * @PageSize
SET @Take = @CurrentPage * @PageSize
SELECT *,(select statename from states where states.stateid=A.stateid) as statename FROM (SELECT ROW_NUMBER() OVER
( ORDER BY IsActive desc) rownumber,*
from Patient
where isnull(firstname,'') like coalesce(@firstname,firstname,'')
and isnull(lastname,'') like coalesce(@lastname,lastname,'')
and patientChart = coalesce(@patientChart,patientChart,'')
and IsActive=coalesce(@status,IsActive,'')
AND Patient.clientid=@clientid
) A
WHERE A.RowNumber > @Skip AND A.RowNumber <= @Take
END
Controller
public void Get(Models models)
{
int _currentPage = 1;
DateTime Fromdate = DateTime.Now;
Fromdate = Fromdate.AddDays(-30);
DateTime Todate = DateTime.Now;
string lotno = string.Empty;
int PageSize = 7;
int printInId = 0;
if (!string.IsNullOrEmpty(Request.QueryString["printInId"]))
{
if (IsInt(Request.QueryString["printInId"]))
printInId = Convert.ToInt32(Request.QueryString["printInId"]);
}
if (!int.TryParse(Request.QueryString["pg"], out _currentPage))
{
_currentPage = 1;
}
if (!string.IsNullOrEmpty(Request.QueryString["fromDate"]))
{
Fromdate = Convert.ToDateTime(Request.QueryString["fromdate"]);
}
if (!string.IsNullOrEmpty(Request.QueryString["toDate"]))
{
Todate = Convert.ToDateTime(Request.QueryString["toDate"]);
}
models.CurrentPage = _currentPage;
var relRepository = uow.Repository<RelRepository>();
var result =RelRepository.ExecWithStoreProcedure("spView @PageSize,@CurrentPage,@FromDate,@ToDate,@lotno,@clientid,@printInId",
new SqlParameter("PageSize", SqlDbType.Int) { Value = PageSize },
new SqlParameter("CurrentPage", SqlDbType.Int) { Value = _currentPage },
new SqlParameter("FromDate", SqlDbType.DateTime, 100) { Value = Fromdate },
new SqlParameter("ToDate", SqlDbType.DateTime, 100) { Value = Todate },
new SqlParameter("printInId", SqlDbType.Int) { Value = printInId }
).ToList();
models.TotalRecordCount = (RelRepository.ExecWithStoreProcedure("spViewCount @FromDate,@ToDate,@printInId",
new SqlParameter("FromDate", SqlDbType.DateTime, 100) { Value = Fromdate },
new SqlParameter("ToDate", SqlDbType.DateTime, 100) { Value = Todate },
new SqlParameter("printInId", SqlDbType.Int) { Value = printInId }
).FirstOrDefault().TotalRecordCount);
int pageCount = models.TotalRecordCount / PageSize;
pageCount = models.TotalRecordCount % PageSize > 0 ? pageCount + 1 : pageCount;
models..TotalPageCount = pageCount;
models..lstDrugIn = result;
VIEW
<div class="box">
<!-- /.box-header -->
<div class="box-body no-padding">
<div class="row col-md-12">
<div class="col-md-6 no-padding">
<div class="col-md-3 no-padding" style="padding-left: 5px !important;">
<label>
<input type="text" id="txtDatepickerFrom" class="form-control datePicker" placeholder="From Date" />
</label>
</div>
<div class="col-md-3 no-padding" style="padding-left: 5px !important;">
<label>
<input type="text" id="txtDatepickerTo" class="form-control datePicker" placeholder="To Date" />
</label>
</div>
</div>
<div class="col-md-6 no-padding">
<div class="col-md-8" style="padding-left: 5px !important;">
<label style="width:100%">
@Html.DropDownListFor(m => m.drugid, Model.DrugList, "Select Drug", new { @class = "chosen-select", id = "dropDrugName", name = "drpList" })
</label>
</div>
<div class="col-md-4 no-padding">
<label>
<button class="btn btn-primary" id="btnSearch" onclick="SearchData('search');return false;">Search</button>
<button class="btn btn-primary" id="btnReset" onclick="SearchData('reset');return false;">Reset</button>
</label>
</div>
</div>
</div>
<div class="clearfix"> </div>
<div class="row col-sm-12">
<table id="tableView" class="table tablesorter">
<thead>
<tr>
<th class="textRight">
Price
</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var item in @Model.lstDrugIn)
{
<tr>
<td style="text-align:right">@item.balanceqty</td>
<th class="col-md-2">
</th>
</tr>
}
</tbody>
</table>
</div>
</div>
<div class="box-footer">
<ul class="pagination pagination-sm no-margin pull-right">
@if (Model.CurrentPage > 1)
{
<li>
<a href="?pg=@(Model.CurrentPage > 1 ? (Model.CurrentPage - 1) : Model.CurrentPage)&fromDate=@(Request.QueryString["fromDate"] != null ? Request.QueryString["fromDate"] : "")&toDate=@(Request.QueryString["toDate"] != null ? Request.QueryString["toDate"] : "")">«</a>
</li>
}
else
{
<li class="disabled">
<a href="javascript:void(0);">«</a>
</li>
}
@if (Model.TotalPageCount > 0)
{
for (int i = (Model.CurrentPage > 3 ? (Model.CurrentPage - 2) : 1); i < (Model.CurrentPage > 3 ? (Model.CurrentPage) + 3 : 6); i++)
{
if (Model.TotalPageCount >= i)
{
<li class="@(i == (Model.CurrentPage) ? "active" : "")">
<a class="@(i == (Model.CurrentPage) ? "selected" : "")" href="?pg=@i&fromDate=@(Request.QueryString["fromDate"] != null ? Request.QueryString["fromDate"] : "")&toDate=@(Request.QueryString["toDate"] != null ? Request.QueryString["toDate"] : "")">@(i)</a>
</li>
}
}
}
@if (Model.CurrentPage < Model.TotalPageCount)
{
<li>
<a href="?pg=@(Model.TotalPageCount == Model.CurrentPage ? (Model.TotalPageCount).ToString() : (Model.CurrentPage + 1).ToString())&fromDate=@(Request.QueryString["fromDate"] != null ? Request.QueryString["fromDate"] : "")&toDate=@(Request.QueryString["toDate"] != null ? Request.QueryString["toDate"] : "")">»</a>
</li>
}
else
{
<li class="disabled"><a href="javascript:void(0);">»</a></li>
}
</ul>
</div>
<!-- /.box-body -->
</div>
JQUERY
function getParameterByName(name) {
name = name.replace(/[\[]/, "\\[").replace(/[\]]/, "\\]");
var regex = new RegExp("[\\?&]" + name + "=([^&#]*)"),
results = regex.exec(location.search);
return results === null ? 0 : decodeURIComponent(results[1].replace(/\+/g, " "));
}
var drugName = getParameterByName('drugName');
if (dateFrom.length > 0) {
$('#datepickerFrom').val(dateFrom);
}
$('#tableView').DataTable({
"paging": false,
"ordering": true,
"info": false,
"bFilter": false,
"bInfo": false,
"aaSorting": [[0, 'desc']]
});
$('#txtPrintInId').on('change', function () {
SearchData('Print');
});
