Click here to Skip to main content
15,890,438 members
Search within:


I want to display in jqgrid but it's an error of type-Object reference not set to an instance of an object.
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
ASP.NET
jQuery
AllusersSummaryDaterange.aspx code

<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage27.master" AutoEventWireup="true" CodeFile="AllusersSummaryDeteRege.aspx.cs" Inherits="AllusersSummaryDeteRege" %>

<%@ Register Src="WebUserControl.ascx" TagName="WebUserControl" TagPrefix="uc1" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

<link rel="Stylesheet" type="text/css" media="screen" href="Themes/blitzer/jquery-ui.css" />
<link rel="Stylesheet" type="text/css" media="screen" href="css/ui.jqgrid.css" />
<link rel="stylesheet" href="css/jdpicker.css" type="text/css"/>

<script src="js/jquery-1.5.2.min.js" type="text/javascript"></script>

<script src="js/i18n/grid.locale-en.js" type="text/javascript"></script>
<script src="js/jquery-ui-custom.min.js" type="text/javascript"></script>
<script src="js/jquery.jqGrid.min.js" type="text/javascript"></script>
<script type="text/javascript" src="js/jquery.jdpicker.js"></script>

<script type="text/javascript">
$(document).ready(function () {

$("#go").click(function () {
$("#gridId").GridUnload();
gridload();
});
});
function gridload() {
$.ajax({
url: 'AllusersSummaryDeteRege.aspx/MyMethod?fromdate=' + $("#fromdate").val() + '&todate=' + $("#todate").val() + '&extension=' + $("#extension").val() + '&empid=' + $("#empid").val() + '&ContentPlaceHolder1_WebUserControl_txtCombo=' + $("#ContentPlaceHolder1_WebUserControl_txtCombo").val()+ '&number=' + $("#number").val(),
dataType: 'json',
contentType: "application/json; charset=utf-8",
type: 'POST',
success: function (ReportDataNew, textStatus, XMLHttpRequest) {
//debugger;
gridData = JSON.parse(ReportDataNew.d);
//console.log(gridData);
//alert(gridData.length);


$("#gridId").jqGrid({
data: gridData,
datatype: "local",
height: '100%',
// width: '1500',
sortable:true,
hidegrid:true,

autowidth: true,
shrinkToFit: true,
rowNum: 15,
rowList: [15, 50, 100, 200],
colNames: ['wwid','LOCALPRICE','STDPRICE','ISDPRICE','iNETPRICE','STDCALLS','ISDCALLS','LOCALCALLS','INCOMCALLS','iNETCALLS','iNETDUR','INCOMDUR','STDDUR','ISDDUR','LOCALDUR','TOTALDUR','TOTALPRICE'],
colModel: [
{ name: 'wwid', index: 'wwid',key: true,sortable: true,sorttype:'int', align: 'center' },
{ name: 'LOCALPRICE', index: 'LOCALPRICE', editable: true, sortable: true,sorttype:'int', align: 'center' },
{ name: 'STDPRICE', index: 'STDPRICE', width:'110', editable: true,sorttype:'int', sortable: true, align: 'center' },
{ name: 'ISDPRICE', index: 'ISDPRICE', width:'110', editable: true,sorttype:'int', sortable: true, align: 'center' },
{ name: 'iNETPRICE', index: 'iNETPRICE', width:'110', editable: true, sortable: true,sorttype:'int', align: 'center' },
{ name: 'STDCALLS', index: 'STDCALLS', editable: true,search:false, sortable: true,sorttype:'int', align: 'center' },
{ name: 'ISDCALLS', index: 'ISDCALLS', editable: true, sortable: true,sorttype:'int', align: 'center' },
{ name: 'LOCALCALLS', index: 'LOCALCALLS', editable: true, sortable: true,search:false,sorttype:'int', align: 'center' },
{ name: 'INCOMCALLS', index: 'INCOMCALLS', editable: true, sortable: true,sorttype:'int', align: 'center' },
{ name: 'iNETCALLS', index: 'iNETCALLS', editable: true, sortable: true,sorttype:'int', align: 'center' },
{ name: 'iNETDUR', index: 'iNETDUR', editable: true, sortable: true,sorttype:'int', align: 'center' },
{ name: 'INCOMDUR', index: 'INCOMDUR', editable: true, sortable: true,sorttype:'int', align: 'center' },
{ name: 'STDDUR', index: 'STDDUR', editable: true, sortable: true,sorttype:'int', align: 'center' },
{ name: 'ISDDUR', index: 'ISDDUR', editable: true, sortable: true,sorttype:'int', align: 'center' },
{ name: 'LOCALDUR', index: 'LOCALDUR', editable: true, sortable: true,sorttype:'int', align: 'center' },
{ name: 'TOTALDUR', index: 'TOTALDUR', editable: true, sortable: true,sorttype:'int', align: 'center' },
{ name: 'TOTALPRICE', index: 'TOTALPRICE', editable: true, sortable: true,search:false,sorttype:'int', align: 'center'}

],
pager: '#gridpager',
viewrecords: true,
toppager: true,
loadtext: 'Loading...',



});
$('#gridId').jqGrid('navGrid', '#gridpager',
{ edit: false, add: false, del: false,search:false, view: false, searchtext: "Search", cloneToTop: true },
{ url: "editupdel.ashx", closeAfterEdit: true, beforeShowForm: function (formid) { $("#dialdate").datepicker({ dateFormat: 'yy/mm/dd', showButtonPanel: true })} },
{ url: "editupdel.ashx", closeAfteradd: true, beforeShowForm: function (formid) { $("#dialdate").datepicker({ dateFormat: 'yy/mm/dd', showButtonPanel: true })} },
{ url: "editupdel.ashx", closeAfterdel: true },
{ caption: "Search", closeOnEscape: true, multipleSearch: true, closeAfterSearch: true, overlay: false })




.navButtonAdd('#gridId_toppager', {
caption: "Excel",
id: "exceltop",
buttonicon: "sum.png",
onClickButton: function () {
var filterparameter = $("#gridId").jqGrid('getGridParam', 'postData').filters;
var abc1 = new Array();
abc = JSON.parse(filterparameter);
abc1.push(abc);
var query = '';
$.each(abc1, function (index, value) {
$.each(value.rules, function (ind, val) {
query += val.field + "=" + "'" + val.data + "'" + " and ";

})
})

alert(query);
var URL = 'ExportToExcel.ashx?fromdate=' + $("#fromdate").val() + '&todate=' + $("#todate").val() + '&extension=' + $("#extension").val() + '&empid=' + $("#empid").val() + '&ContentPlaceHolder1_WebUserControl_txtCombo=' + $("#ContentPlaceHolder1_WebUserControl_txtCombo").val()+ '&number=' + $("#number").val() + '&query=' + query
window.location = URL;
$.ajax({
url: URL,
type: 'GET',
success: function (data) {
// alert('Exported To Excel');
}

});

}

})

.navButtonAdd('#gridpager', {
caption: "Excel",
id: "excel",
buttonicon: "sum.png",
onClickButton: function () {

var filterparameter = $("#gridId").jqGrid('getGridParam', 'postData').filters;
var abc1 = new Array();
abc = JSON.parse(filterparameter);
abc1.push(abc);
var query = '';
$.each(abc1, function (index, value) {
$.each(value.rules, function (ind, val) {
query += val.field + "=" + "'" + val.data + "'" + " and ";

})
})
alert(query);
var URL = 'ExportToExcel.ashx?fromdate=' + $("#fromdate").val() + '&todate=' + $("#todate").val() + '&extension=' + $("#extension").val() + '&empid=' + $("#empid").val() + '&ContentPlaceHolder1_WebUserControl_txtCombo=' + $("#ContentPlaceHolder1_WebUserControl_txtCombo").val()+ '&number=' + $("#number").val() + '&query=' + query
window.location = URL;
$.ajax({
url: URL,
type: 'GET',
success: function (data) {
// alert('Exported To Excel');
}

});
//alert("ExportToExcel");
}

})


.navButtonAdd('#gridId_toppager', {
caption: "Pdf",
id: "pdftop",
buttonicon: "sum.png",
onClickButton: function () {
var filterparameter = $("#gridId").jqGrid('getGridParam', 'postData').filters;
var abc1 = new Array();
abc = JSON.parse(filterparameter);
abc1.push(abc);
var query = '';
$.each(abc1, function (index, value) {
$.each(value.rules, function (ind, val) {
query += val.field + "=" + "'" + val.data + "'" + " and ";

})
})
alert(query);
var URL = 'ExportToPdf.ashx?fromdate=' + $("#fromdate").val() + '&todate=' + $("#todate").val() + + '&extension=' + $("#extension").val() + '&empid=' + $("#empid").val() + '&sites=' + $("#sites").val() + '&query=' + query;
window.location = URL;
$.ajax({
url: URL,
type: 'GET',
success: function (data) {
// alert('Exported To Excel');
}

});
//alert("ExportToPdf");
}
})

.navButtonAdd('#gridpager', {
caption: "Pdf",
id: "pdf",
buttonicon: "sum.png",
onClickButton: function () {
var filterparameter = $("#gridId").jqGrid('getGridParam', 'postData').filters;
var abc1 = new Array();
abc = JSON.parse(filterparameter);
abc1.push(abc);
var query = '';
$.each(abc1, function (index, value) {
$.each(value.rules, function (ind, val) {
query += val.field + "=" + "'" + val.data + "'" + " and ";

})
})
alert(query);
var URL = 'ExportToPdf.ashx?fromdate=' + $("#fromdate").val() + '&todate=' + $("#todate").val() + + '&extension=' + $("#extension").val() + '&empid=' + $("#empid").val() + '&sites=' + $("#sites").val() + '&query=' + query;
window.location = URL;
$.ajax({
url: URL,
type: 'GET',
success: function (data) {
// alert('Exported To Excel');
}
});
}
});
$("#gridId").jqGrid('navButtonAdd', '#gridpager', { caption: "Columns", title: "Reorder Columns", onClickButton: function () { $("#gridId").jqGrid('columnChooser',{shrinkToFit:false,autowidth: true}); } });


$("#gridId").jqGrid('gridResize', { minWidth: 250, maxWidth: 1800, minHeight: 80, maxHeight: 1350 });

jQuery("#gridId").jqGrid('filterToolbar', { stringResult: true, searchOnEnter: false, defaultSearch: "eq" });
jQuery("#gridId").jqGrid('navButtonAdd', '#gridpager', { caption: "Filter", title: "Toggle Searching Toolbar", buttonicon: 'ui-icon-pin-s', onClickButton: function () { $("#gridId")[0].toggleToolbar(); } });
jQuery("#gridId")[0].toggleToolbar();
},
error: function (xhr, ajaxOptions, thrownError) { alert('statusCode=Error: ' + xhr.status + '&status=Description :' + thrownError); }
});
}


</script>


<style type="text/css">
#todate
{
z-index: 1;
left: 377px;
top: 113px;
position: absolute;
width: 104px;
}
#fromdate
{
z-index: 1;
left: 136px;
top: 113px;
position: absolute;
width: 104px;
}
#extension
{
z-index: 1;
left: 136px;
top: 143px;
position: absolute;
width: 104px;
}
#empid
{
z-index: 1;
left: 377px;
top: 144px;
position: absolute;
width: 104px;
}
#number
{
z-index: 1;
left: 378px;
top: 176px;
position: absolute;
width: 104px;
}
#gridId
{
width: 9px;
}
</style>


<body>


<input type="text" id="todate" name="datum2" class="jdpicker" />
<input type="text" id="fromdate" name="datum1" class="jdpicker" />
<input type="text" id="extension" name="txt1" />
<input type="text" id="empid" name="txt2" /> 
<input type="submit" name="submit"

style="color:#0278AE; z-index: 1; left: 523px; top: 108px; position: absolute; height: 26px; width: 66px;";
id="go" value="GO" önmouseover="true" title="Click here to get details."/>

<asp:ScriptManager ID="ScriptManager1" runat="server" >


<asp:Panel ID="Panel5" runat="server" Height="35px" Font-Bold="True"
Style=" left: 135px; position: absolute; top: 178px; " Width="100px">
<asp:Label ID="Label18" runat="server" Font-Bold="True" Style="font-size: small; left:-90px;
color: #000066; font-family: 'Tahoma'; position: absolute;top: 1px" Text="Sites"
Width="72px">

<uc1:WebUserControl ID="WebUserControl" runat="server" />



<form id="form1" runat="server">

<asp:Label ID="Label1" runat="server" Text="To Date"
style="z-index: 1; left: 293px; top: 114px; position: absolute" Font-Bold="True"
ForeColor="#000066">

<asp:Label ID="Label2" runat="server" Text="From Date"
style="z-index: 1; left: 44px; top: 112px; position: absolute; height: 17px;" Font-Bold="True"
ForeColor="#000066">

<asp:Label ID="Label3" runat="server" Text="Extension" Font-Bold="True"
Style="left: 61px; color: #00000;position: absolute; left: 44px; top: 150px; font-family: 'Tahoma';font-size: small; right: -2325px;"
ForeColor="#000066">

<asp:Label ID="Label4" runat="server" Text="Emp ID" Font-Bold="True" Style="color: #00000;position: absolute; left: 293px; top: 150px; font-family: 'Tahoma';font-size: small;"
ForeColor="#000066">

<%-- <asp:CheckBox ID="CheckBox1" runat="server" Visible="false"
Font-Bold="False" Font-Size="Small"
Style="left: 843px;
color: #005A5A; font-family: 'Courier New'; position: absolute; top: 150px; width: 93px;"
Text="Chennai" />
<asp:CheckBox ID="CheckBox3" runat="server" Visible="false"
Font-Bold="False" Font-Size="Small"
Style="left: 523px;
color: #005A5A; font-family: 'Courier New'; position: absolute; top: 150px"
Text="Delhi" Width="73px" />
<asp:CheckBox ID="CheckBox4" runat="server" Visible="false"
Font-Bold="False" Font-Size="Small"
Style="left: 693px;
color: #005A5A; font-family: 'Courier New'; position: absolute; top: 150px"
Text="Mumbai" Width="76px" />
<asp:CheckBox ID="CheckBox20" runat="server" Font-Bold="False"
Font-Size="Small" ForeColor="White"
Style="left: 1013px; position: absolute; top: 150px; color: #005a5a; font-family: 'Courier New'; width: 65px;"
Text="Site2" Visible="False" />
<asp:CheckBox ID="CheckBox5" runat="server" Font-Bold="False"
Font-Size="Small" ForeColor="White"
Style="left: 933px; position: absolute; top: 150px; color: #005a5a; font-family: 'Courier New'; width: 66px;"
Text="Site3" Visible="False" />
<asp:CheckBox ID="CheckBox23" runat="server" Font-Bold="False" Visible="false"
Font-Size="Small" ForeColor="White"
Style="left: 593px; position: absolute; top: 150px; color: #005a5a; font-family: 'Courier New';"
Text="Hyderabad" Width="101px" />
<asp:CheckBox ID="CheckBox24" runat="server" Font-Bold="False" Visible="false"
Font-Size="Small" ForeColor="White"
Style="left: 773px; position: absolute; top: 150px; color: #005a5a; font-family: 'Courier New';"
Text="Pune" Width="62px" />--%>










</form>


</body>
AllusersSummaryDaterange.aspx.cs code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Services;
using MySql.Data.MySqlClient;
using System.Web.Script.Serialization;

public partial class AllusersSummaryDeteRege : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

[WebMethod]
public static string MyMethod()
{
string fromdate = HttpContext.Current.Request.QueryString["fromdate"].ToString();
string todate = HttpContext.Current.Request.QueryString["todate"].ToString();
string extension = HttpContext.Current.Request.QueryString["extension"].ToString();
string empid = HttpContext.Current.Request.QueryString["empid"].ToString();
//string wwid = HttpContext.Current.Request.QueryString["wwid"].ToString();
string sites = HttpContext.Current.Request.QueryString["ContentPlaceHolder1_WebUserControl_txtCombo"].ToString();
string[] site = sites.Split(',');
string site3 = "";
string[] site1 = { "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "" };
for (int i9 = 0; i9 < 99; i9++)
{
if (i9 < site.Length)
{
site1[i9] = site[i9].Trim();
}
else
{
site1[i9] = site3;
}
}
List<gridparam> param = new List<gridparam>();
string conn = "Server=localhost;Port=3306;Database=projecttt;UID=root;Pwd=techsoft;pooling=false";
string s = string.Empty;

if (!string.IsNullOrEmpty(fromdate) && !string.IsNullOrEmpty(todate) && string.IsNullOrEmpty(extension) && string.IsNullOrEmpty(empid) && string.IsNullOrEmpty(sites))
{
//s = "SELECT empid,round(SUM(CASE toc WHEN 'LOCAL' THEN price ELSE 0 END),2) AS LOCALPRICE,round(SUM(CASE toc WHEN 'STD' THEN price ELSE 0 END),2) AS STDPRICE, round(SUM(CASE toc WHEN 'ISD' THEN price ELSE 0 END),2) AS ISDPRICE,round(SUM(CASE toc WHEN 'INTERNAL' THEN price ELSE 0 END),2) AS iNETPRICE, count(case when toc = 'STD' then 1 end) as STDCALLS,count(case when toc = 'ISD' then 1 end) as ISDCALLS, count(case when toc = 'LOCAL' then 1 end) as LOCALCALLS, count(case when toc = 'INCOMING' then 1 end) as INCOMCALLS,count(case when toc = 'INTERNAL' then 1 end) as iNETCALLS, (concat(truncate((floor(sum(CASE toc WHEN 'INTERNAL' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'INTERNAL' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'INTERNAL' THEN duration ELSE 0 END)%60)) as iNETDUR,(concat(truncate((floor(sum(CASE toc WHEN 'INCOMING' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'INCOMING' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'INCOMING' THEN duration ELSE 0 END)%60)) as INCOMDUR,(concat(truncate((floor(sum(CASE toc WHEN 'STD' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'STD' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'STD' THEN duration ELSE 0 END)%60)) as STDDUR ,(concat(truncate((floor(sum(CASE toc WHEN 'ISD' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'ISD' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'ISD' THEN duration ELSE 0 END)%60)) as ISDDUR, (concat(truncate((floor(sum(CASE toc WHEN 'LOCAL' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'LOCAL' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'LOCAL' THEN duration ELSE 0 END)%60)) as LOCALDUR,(concat(truncate((floor(SUM(duration))/3600),0),':',truncate((floor((SUM(duration))/60)%60),0),':',SUM(duration)%60)) as TOTALDUR,round(sum(price),2)as TOTALPRICE FROM processeddata_table where dialdate between '" + fromdate + "' and '" + todate + "' group by empid";
s = "SELECT Distinct toc,count(dialnumber),(concat(truncate((floor(SUM(duration))/3600),0),':',truncate((floor((SUM(duration))/60)%60),0),':',SUM(duration)%60)) ,round(sum(price),2) from processeddata_table WHERE DialDate between '" + fromdate + "' and '" + todate + "' and Duration != '0' group by toc";

}
if (!string.IsNullOrEmpty(fromdate) && !string.IsNullOrEmpty(todate) && !string.IsNullOrEmpty(extension) && string.IsNullOrEmpty(empid) && string.IsNullOrEmpty(sites))
{
//s = " Select * from processeddata_table WHERE DialDate between '" + bg8 + "' and '" + bg9 + "' and Duration != '0' AND (BusinessUnit ='" + site1[0] + "' or BusinessUnit ='" + site1[1] + "' or BusinessUnit ='" + site1[2] + "' or BusinessUnit ='" + site1[3] + "' or BusinessUnit ='" + site1[4] + "' or BusinessUnit ='" + site1[5] + "' or BusinessUnit ='" + site1[6] + "' or BusinessUnit ='" + site1[7] + "' or BusinessUnit ='" + site1[8] + "' or BusinessUnit ='" + site1[9] + "' or BusinessUnit ='" + site1[10] + "' or BusinessUnit ='" + site1[11] + "' or BusinessUnit ='" + site1[12] + "' or BusinessUnit ='" + site1[13] + "' or BusinessUnit ='" + site1[14] + "' or BusinessUnit ='" + site1[15] + "' or BusinessUnit ='" + site1[16] + "' or BusinessUnit ='" + site1[17] + "' or BusinessUnit ='" + site1[18] + "' or BusinessUnit ='" + site1[19] + "' or BusinessUnit ='" + site1[20] + "' or BusinessUnit ='" + site1[21] + "' or BusinessUnit ='" + site1[22] + "' or BusinessUnit ='" + site1[23] + "' or BusinessUnit ='" + site1[24] + "' or BusinessUnit ='" + site1[25] + "' or BusinessUnit ='" + site1[26] + "' or BusinessUnit ='" + site1[27] + "' or BusinessUnit ='" + site1[28] + "' or BusinessUnit ='" + site1[29] + "' or BusinessUnit ='" + site1[30] + "' or BusinessUnit ='" + site1[31] + "' or BusinessUnit ='" + site1[32] + "' or BusinessUnit ='" + site1[33] + "' or BusinessUnit ='" + site1[34] + "' or BusinessUnit ='" + site1[35] + "' or BusinessUnit ='" + site1[36] + "' or BusinessUnit ='" + site1[37] + "' or BusinessUnit ='" + site1[38] + "' or BusinessUnit ='" + site1[39] + "' or BusinessUnit ='" + site1[40] + "' or BusinessUnit ='" + site1[41] + "' or BusinessUnit ='" + site1[42] + "' or BusinessUnit ='" + site1[43] + "' or BusinessUnit ='" + site1[44] + "' or BusinessUnit ='" + site1[45] + "' or BusinessUnit ='" + site1[46] + "' or BusinessUnit ='" + site1[47] + "' or BusinessUnit ='" + site1[48] + "' or BusinessUnit ='" + site1[49] + "' or BusinessUnit ='" + site1[50] + "' or BusinessUnit ='" + site1[51] + "' or BusinessUnit ='" + site1[52] + "' or BusinessUnit ='" + site1[53] + "' or BusinessUnit ='" + site1[54] + "' or BusinessUnit ='" + site1[55] + "' or BusinessUnit ='" + site1[56] + "' or BusinessUnit ='" + site1[57] + "' or BusinessUnit ='" + site1[58] + "' or BusinessUnit ='" + site1[59] + "' or BusinessUnit ='" + site1[60] + "' or BusinessUnit ='" + site1[61] + "' or BusinessUnit ='" + site1[62] + "' or BusinessUnit ='" + site1[63] + "' or BusinessUnit ='" + site1[64] + "' or BusinessUnit ='" + site1[65] + "' or BusinessUnit ='" + site1[66] + "' or BusinessUnit ='" + site1[67] + "' or BusinessUnit ='" + site1[68] + "' or BusinessUnit ='" + site1[69] + "' or BusinessUnit ='" + site1[70] + "' or BusinessUnit ='" + site1[71] + "' or BusinessUnit ='" + site1[72] + "' or BusinessUnit ='" + site1[73] + "' or BusinessUnit ='" + site1[74] + "' or BusinessUnit ='" + site1[75] + "' or BusinessUnit ='" + site1[76] + "' or BusinessUnit ='" + site1[77] + "' or BusinessUnit ='" + site1[78] + "'or BusinessUnit ='" + site1[79] + "' or BusinessUnit ='" + site1[80] + "' or BusinessUnit ='" + site1[81] + "' or BusinessUnit ='" + site1[82] + "' or BusinessUnit ='" + site1[83] + "' or BusinessUnit ='" + site1[84] + "' or BusinessUnit ='" + site1[85] + "' or BusinessUnit ='" + site1[86] + "' or BusinessUnit ='" + site1[87] + "' or BusinessUnit ='" + site1[88] + "' or BusinessUnit ='" + site1[89] + "' or BusinessUnit ='" + site1[90] + "' or BusinessUnit ='" + site1[91] + "' or BusinessUnit ='" + site1[92] + "' or BusinessUnit ='" + site1[93] + "' or BusinessUnit ='" + site1[94] + "' or BusinessUnit ='" + site1[95] + "' or BusinessUnit ='" + site1[96] + "' or BusinessUnit ='" + site1[97] + "' or BusinessUnit ='" + site1[98] + "' or BusinessUnit ='" + site1[99] + "') ORDER BY DialDate,DialTime";
//s = " Select Distinct toc,count(dialnumber),(concat(truncate((floor(SUM(duration))/3600),0),':',truncate((floor((SUM(duration))/60)%60),0),':',SUM(duration)%60)) ,round(sum(price),2) from processeddata_table WHERE DialDate between '" + fromdate + "' and '" + todate + "' and Duration != '0' AND (BusinessUnit ='" + site1[0] + "' or BusinessUnit ='" + site1[1] + "' or BusinessUnit ='" + site1[2] + "' or BusinessUnit ='" + site1[3] + "' or BusinessUnit ='" + site1[4] + "' or BusinessUnit ='" + site1[5] + "' or BusinessUnit ='" + site1[6] + "' or BusinessUnit ='" + site1[7] + "' or BusinessUnit ='" + site1[8] + "' or BusinessUnit ='" + site1[9] + "' or BusinessUnit ='" + site1[10] + "' or BusinessUnit ='" + site1[11] + "' or BusinessUnit ='" + site1[12] + "' or BusinessUnit ='" + site1[13] + "' or BusinessUnit ='" + site1[14] + "' or BusinessUnit ='" + site1[15] + "' or BusinessUnit ='" + site1[16] + "' or BusinessUnit ='" + site1[17] + "' or BusinessUnit ='" + site1[18] + "' or BusinessUnit ='" + site1[19] + "' or BusinessUnit ='" + site1[20] + "' or BusinessUnit ='" + site1[21] + "' or BusinessUnit ='" + site1[22] + "' or BusinessUnit ='" + site1[23] + "' or BusinessUnit ='" + site1[24] + "' or BusinessUnit ='" + site1[25] + "' or BusinessUnit ='" + site1[26] + "' or BusinessUnit ='" + site1[27] + "' or BusinessUnit ='" + site1[28] + "' or BusinessUnit ='" + site1[29] + "' or BusinessUnit ='" + site1[30] + "' or BusinessUnit ='" + site1[31] + "' or BusinessUnit ='" + site1[32] + "' or BusinessUnit ='" + site1[33] + "' or BusinessUnit ='" + site1[34] + "' or BusinessUnit ='" + site1[35] + "' or BusinessUnit ='" + site1[36] + "' or BusinessUnit ='" + site1[37] + "' or BusinessUnit ='" + site1[38] + "' or BusinessUnit ='" + site1[39] + "' or BusinessUnit ='" + site1[40] + "' or BusinessUnit ='" + site1[41] + "' or BusinessUnit ='" + site1[42] + "' or BusinessUnit ='" + site1[43] + "' or BusinessUnit ='" + site1[44] + "' or BusinessUnit ='" + site1[45] + "' or BusinessUnit ='" + site1[46] + "' or BusinessUnit ='" + site1[47] + "' or BusinessUnit ='" + site1[48] + "' or BusinessUnit ='" + site1[49] + "' or BusinessUnit ='" + site1[50] + "' or BusinessUnit ='" + site1[51] + "' or BusinessUnit ='" + site1[52] + "' or BusinessUnit ='" + site1[53] + "' or BusinessUnit ='" + site1[54] + "' or BusinessUnit ='" + site1[55] + "' or BusinessUnit ='" + site1[56] + "' or BusinessUnit ='" + site1[57] + "' or BusinessUnit ='" + site1[58] + "' or BusinessUnit ='" + site1[59] + "' or BusinessUnit ='" + site1[60] + "' or BusinessUnit ='" + site1[61] + "' or BusinessUnit ='" + site1[62] + "' or BusinessUnit ='" + site1[63] + "' or BusinessUnit ='" + site1[64] + "' or BusinessUnit ='" + site1[65] + "' or BusinessUnit ='" + site1[66] + "' or BusinessUnit ='" + site1[67] + "' or BusinessUnit ='" + site1[68] + "' or BusinessUnit ='" + site1[69] + "' or BusinessUnit ='" + site1[70] + "' or BusinessUnit ='" + site1[71] + "' or BusinessUnit ='" + site1[72] + "' or BusinessUnit ='" + site1[73] + "' or BusinessUnit ='" + site1[74] + "' or BusinessUnit ='" + site1[75] + "' or BusinessUnit ='" + site1[76] + "' or BusinessUnit ='" + site1[77] + "' or BusinessUnit ='" + site1[78] + "'or BusinessUnit ='" + site1[79] + "' or BusinessUnit ='" + site1[80] + "' or BusinessUnit ='" + site1[81] + "' or BusinessUnit ='" + site1[82] + "' or BusinessUnit ='" + site1[83] + "' or BusinessUnit ='" + site1[84] + "' or BusinessUnit ='" + site1[85] + "' or BusinessUnit ='" + site1[86] + "' or BusinessUnit ='" + site1[87] + "' or BusinessUnit ='" + site1[88] + "' or BusinessUnit ='" + site1[89] + "' or BusinessUnit ='" + site1[90] + "' or BusinessUnit ='" + site1[91] + "' or BusinessUnit ='" + site1[92] + "' or BusinessUnit ='" + site1[93] + "' or BusinessUnit ='" + site1[94] + "' or BusinessUnit ='" + site1[95] + "' or BusinessUnit ='" + site1[96] + "' or BusinessUnit ='" + site1[97] + "' or BusinessUnit ='" + site1[98] + "' or BusinessUnit ='" + site1[99] + "') group by toc";
s = " Select Distinct toc,count(dialnumber),(concat(truncate((floor(SUM(duration))/3600),0),':',truncate((floor((SUM(duration))/60)%60),0),':',SUM(duration)%60)) ,round(sum(price),2) from processeddata_table WHERE DialDate between '" + fromdate + "' and '" + todate + "' and Duration != '0' AND Extension='" + extension + "' group by toc";

}
if (!string.IsNullOrEmpty(fromdate) && !string.IsNullOrEmpty(todate) && string.IsNullOrEmpty(extension) && !string.IsNullOrEmpty(empid) && string.IsNullOrEmpty(sites))
{
//s = " Select * from processeddata_table WHERE DialDate between '" + bg8 + "' and '" + bg9 + "' and Duration != '0' AND WWID='" + wwid1 + "' ORDER BY DialDate,DialTime";
//s = " Select Distinct toc,count(dialnumber),(concat(truncate((floor(SUM(duration))/3600),0),':',truncate((floor((SUM(duration))/60)%60),0),':',SUM(duration)%60)) ,round(sum(price),2) from processeddata_table WHERE DialDate between '" + fromdate + "' and '" + todate + "' and Duration != '0' AND EMPID='" + empid + "' group by toc";
s = " Select Distinct toc,count(dialnumber),(concat(truncate((floor(SUM(duration))/3600),0),':',truncate((floor((SUM(duration))/60)%60),0),':',SUM(duration)%60)) ,round(sum(price),2) from processeddata_table WHERE DialDate between '" + fromdate + "' and '" + todate + "' and Duration != '0' AND WWID='" + empid + "' group by toc";

}

if (!string.IsNullOrEmpty(fromdate) && !string.IsNullOrEmpty(todate) && !string.IsNullOrEmpty(extension) && !string.IsNullOrEmpty(empid) && string.IsNullOrEmpty(sites))
{
//s = " Select Distinct toc,count(dialnumber),(concat(truncate((floor(SUM(duration))/3600),0),':',truncate((floor((SUM(duration))/60)%60),0),':',SUM(duration)%60)) ,round(sum(price),2) from processeddata_table WHERE DialDate between '" + fromdate + "' and '" + todate + "' and Duration != '0' AND WWID='" + empid + "' AND (BusinessUnit ='" + site1[0] + "' or BusinessUnit ='" + site1[1] + "' or BusinessUnit ='" + site1[2] + "' or BusinessUnit ='" + site1[3] + "' or BusinessUnit ='" + site1[4] + "' or BusinessUnit ='" + site1[5] + "' or BusinessUnit ='" + site1[6] + "' or BusinessUnit ='" + site1[7] + "' or BusinessUnit ='" + site1[8] + "' or BusinessUnit ='" + site1[9] + "' or BusinessUnit ='" + site1[10] + "' or BusinessUnit ='" + site1[11] + "' or BusinessUnit ='" + site1[12] + "' or BusinessUnit ='" + site1[13] + "' or BusinessUnit ='" + site1[14] + "' or BusinessUnit ='" + site1[15] + "' or BusinessUnit ='" + site1[16] + "' or BusinessUnit ='" + site1[17] + "' or BusinessUnit ='" + site1[18] + "' or BusinessUnit ='" + site1[19] + "' or BusinessUnit ='" + site1[20] + "' or BusinessUnit ='" + site1[21] + "' or BusinessUnit ='" + site1[22] + "' or BusinessUnit ='" + site1[23] + "' or BusinessUnit ='" + site1[24] + "' or BusinessUnit ='" + site1[25] + "' or BusinessUnit ='" + site1[26] + "' or BusinessUnit ='" + site1[27] + "' or BusinessUnit ='" + site1[28] + "' or BusinessUnit ='" + site1[29] + "' or BusinessUnit ='" + site1[30] + "' or BusinessUnit ='" + site1[31] + "' or BusinessUnit ='" + site1[32] + "' or BusinessUnit ='" + site1[33] + "' or BusinessUnit ='" + site1[34] + "' or BusinessUnit ='" + site1[35] + "' or BusinessUnit ='" + site1[36] + "' or BusinessUnit ='" + site1[37] + "' or BusinessUnit ='" + site1[38] + "' or BusinessUnit ='" + site1[39] + "' or BusinessUnit ='" + site1[40] + "' or BusinessUnit ='" + site1[41] + "' or BusinessUnit ='" + site1[42] + "' or BusinessUnit ='" + site1[43] + "' or BusinessUnit ='" + site1[44] + "' or BusinessUnit ='" + site1[45] + "' or BusinessUnit ='" + site1[46] + "' or BusinessUnit ='" + site1[47] + "' or BusinessUnit ='" + site1[48] + "' or BusinessUnit ='" + site1[49] + "' or BusinessUnit ='" + site1[50] + "' or BusinessUnit ='" + site1[51] + "' or BusinessUnit ='" + site1[52] + "' or BusinessUnit ='" + site1[53] + "' or BusinessUnit ='" + site1[54] + "' or BusinessUnit ='" + site1[55] + "' or BusinessUnit ='" + site1[56] + "' or BusinessUnit ='" + site1[57] + "' or BusinessUnit ='" + site1[58] + "' or BusinessUnit ='" + site1[59] + "' or BusinessUnit ='" + site1[60] + "' or BusinessUnit ='" + site1[61] + "' or BusinessUnit ='" + site1[62] + "' or BusinessUnit ='" + site1[63] + "' or BusinessUnit ='" + site1[64] + "' or BusinessUnit ='" + site1[65] + "' or BusinessUnit ='" + site1[66] + "' or BusinessUnit ='" + site1[67] + "' or BusinessUnit ='" + site1[68] + "' or BusinessUnit ='" + site1[69] + "' or BusinessUnit ='" + site1[70] + "' or BusinessUnit ='" + site1[71] + "' or BusinessUnit ='" + site1[72] + "' or BusinessUnit ='" + site1[73] + "' or BusinessUnit ='" + site1[74] + "' or BusinessUnit ='" + site1[75] + "' or BusinessUnit ='" + site1[76] + "' or BusinessUnit ='" + site1[77] + "' or BusinessUnit ='" + site1[78] + "'or BusinessUnit ='" + site1[79] + "' or BusinessUnit ='" + site1[80] + "' or BusinessUnit ='" + site1[81] + "' or BusinessUnit ='" + site1[82] + "' or BusinessUnit ='" + site1[83] + "' or BusinessUnit ='" + site1[84] + "' or BusinessUnit ='" + site1[85] + "' or BusinessUnit ='" + site1[86] + "' or BusinessUnit ='" + site1[87] + "' or BusinessUnit ='" + site1[88] + "' or BusinessUnit ='" + site1[89] + "' or BusinessUnit ='" + site1[90] + "' or BusinessUnit ='" + site1[91] + "' or BusinessUnit ='" + site1[92] + "' or BusinessUnit ='" + site1[93] + "' or BusinessUnit ='" + site1[94] + "' or BusinessUnit ='" + site1[95] + "' or BusinessUnit ='" + site1[96] + "' or BusinessUnit ='" + site1[97] + "' or BusinessUnit ='" + site1[98] + "' or BusinessUnit ='" + site1[99] + "') group by toc";
s = " Select Distinct toc,count(dialnumber),(concat(truncate((floor(SUM(duration))/3600),0),':',truncate((floor((SUM(duration))/60)%60),0),':',SUM(duration)%60)) ,round(sum(price),2) from processeddata_table WHERE DialDate between '" + fromdate + "' and '" + todate + "' and Duration != '0' AND WWID='" + empid + "' AND Extension='" + extension + "' group by toc";

//s = "SELECT empid,round(SUM(CASE toc WHEN 'LOCAL' THEN price ELSE 0 END),2) AS LOCALPRICE,round(SUM(CASE toc WHEN 'STD' THEN price ELSE 0 END),2) AS STDPRICE, round(SUM(CASE toc WHEN 'ISD' THEN price ELSE 0 END),2) AS ISDPRICE,round(SUM(CASE toc WHEN 'INTERNAL' THEN price ELSE 0 END),2) AS iNETPRICE, count(case when toc = 'STD' then 1 end) as STDCALLS,count(case when toc = 'ISD' then 1 end) as ISDCALLS, count(case when toc = 'LOCAL' then 1 end) as LOCALCALLS, count(case when toc = 'INCOMING' then 1 end) as INCOMCALLS,count(case when toc = 'INTERNAL' then 1 end) as iNETCALLS, (concat(truncate((floor(sum(CASE toc WHEN 'INTERNAL' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'INTERNAL' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'INTERNAL' THEN duration ELSE 0 END)%60)) as iNETDUR,(concat(truncate((floor(sum(CASE toc WHEN 'INCOMING' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'INCOMING' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'INCOMING' THEN duration ELSE 0 END)%60)) as INCOMDUR,(concat(truncate((floor(sum(CASE toc WHEN 'STD' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'STD' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'STD' THEN duration ELSE 0 END)%60)) as STDDUR ,(concat(truncate((floor(sum(CASE toc WHEN 'ISD' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'ISD' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'ISD' THEN duration ELSE 0 END)%60)) as ISDDUR, (concat(truncate((floor(sum(CASE toc WHEN 'LOCAL' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'LOCAL' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'LOCAL' THEN duration ELSE 0 END)%60)) as LOCALDUR,(concat(truncate((floor(SUM(duration))/3600),0),':',truncate((floor((SUM(duration))/60)%60),0),':',SUM(duration)%60)) as TOTALDUR,round(sum(price),2)as TOTALPRICE FROM processeddata_table where dialdate between '" + fromdate + "' and '" + todate + "' AND extension='" + extension + "' and empid='" + empid + "' group by empid";
}
if (!string.IsNullOrEmpty(fromdate) && !string.IsNullOrEmpty(todate) && string.IsNullOrEmpty(extension) && string.IsNullOrEmpty(empid) && !string.IsNullOrEmpty(sites))
{
s = " Select Distinct toc,count(dialnumber),(concat(truncate((floor(SUM(duration))/3600),0),':',truncate((floor((SUM(duration))/60)%60),0),':',SUM(duration)%60)) ,round(sum(price),2) from processeddata_table WHERE DialDate between '" +fromdate + "' and '" + todate + "' and Duration != '0' AND (BusinessUnit ='" + site1[0] + "' or BusinessUnit ='" + site1[1] + "' or BusinessUnit ='" + site1[2] + "' or BusinessUnit ='" + site1[3] + "' or BusinessUnit ='" + site1[4] + "' or BusinessUnit ='" + site1[5] + "' or BusinessUnit ='" + site1[6] + "' or BusinessUnit ='" + site1[7] + "' or BusinessUnit ='" + site1[8] + "' or BusinessUnit ='" + site1[9] + "' or BusinessUnit ='" + site1[10] + "' or BusinessUnit ='" + site1[11] + "' or BusinessUnit ='" + site1[12] + "' or BusinessUnit ='" + site1[13] + "' or BusinessUnit ='" + site1[14] + "' or BusinessUnit ='" + site1[15] + "' or BusinessUnit ='" + site1[16] + "' or BusinessUnit ='" + site1[17] + "' or BusinessUnit ='" + site1[18] + "' or BusinessUnit ='" + site1[19] + "' or BusinessUnit ='" + site1[20] + "' or BusinessUnit ='" + site1[21] + "' or BusinessUnit ='" + site1[22] + "' or BusinessUnit ='" + site1[23] + "' or BusinessUnit ='" + site1[24] + "' or BusinessUnit ='" + site1[25] + "' or BusinessUnit ='" + site1[26] + "' or BusinessUnit ='" + site1[27] + "' or BusinessUnit ='" + site1[28] + "' or BusinessUnit ='" + site1[29] + "' or BusinessUnit ='" + site1[30] + "' or BusinessUnit ='" + site1[31] + "' or BusinessUnit ='" + site1[32] + "' or BusinessUnit ='" + site1[33] + "' or BusinessUnit ='" + site1[34] + "' or BusinessUnit ='" + site1[35] + "' or BusinessUnit ='" + site1[36] + "' or BusinessUnit ='" + site1[37] + "' or BusinessUnit ='" + site1[38] + "' or BusinessUnit ='" + site1[39] + "' or BusinessUnit ='" + site1[40] + "' or BusinessUnit ='" + site1[41] + "' or BusinessUnit ='" + site1[42] + "' or BusinessUnit ='" + site1[43] + "' or BusinessUnit ='" + site1[44] + "' or BusinessUnit ='" + site1[45] + "' or BusinessUnit ='" + site1[46] + "' or BusinessUnit ='" + site1[47] + "' or BusinessUnit ='" + site1[48] + "' or BusinessUnit ='" + site1[49] + "' or BusinessUnit ='" + site1[50] + "' or BusinessUnit ='" + site1[51] + "' or BusinessUnit ='" + site1[52] + "' or BusinessUnit ='" + site1[53] + "' or BusinessUnit ='" + site1[54] + "' or BusinessUnit ='" + site1[55] + "' or BusinessUnit ='" + site1[56] + "' or BusinessUnit ='" + site1[57] + "' or BusinessUnit ='" + site1[58] + "' or BusinessUnit ='" + site1[59] + "' or BusinessUnit ='" + site1[60] + "' or BusinessUnit ='" + site1[61] + "' or BusinessUnit ='" + site1[62] + "' or BusinessUnit ='" + site1[63] + "' or BusinessUnit ='" + site1[64] + "' or BusinessUnit ='" + site1[65] + "' or BusinessUnit ='" + site1[66] + "' or BusinessUnit ='" + site1[67] + "' or BusinessUnit ='" + site1[68] + "' or BusinessUnit ='" + site1[69] + "' or BusinessUnit ='" + site1[70] + "' or BusinessUnit ='" + site1[71] + "' or BusinessUnit ='" + site1[72] + "' or BusinessUnit ='" + site1[73] + "' or BusinessUnit ='" + site1[74] + "' or BusinessUnit ='" + site1[75] + "' or BusinessUnit ='" + site1[76] + "' or BusinessUnit ='" + site1[77] + "' or BusinessUnit ='" + site1[78] + "'or BusinessUnit ='" + site1[79] + "' or BusinessUnit ='" + site1[80] + "' or BusinessUnit ='" + site1[81] + "' or BusinessUnit ='" + site1[82] + "' or BusinessUnit ='" + site1[83] + "' or BusinessUnit ='" + site1[84] + "' or BusinessUnit ='" + site1[85] + "' or BusinessUnit ='" + site1[86] + "' or BusinessUnit ='" + site1[87] + "' or BusinessUnit ='" + site1[88] + "' or BusinessUnit ='" + site1[89] + "' or BusinessUnit ='" + site1[90] + "' or BusinessUnit ='" + site1[91] + "' or BusinessUnit ='" + site1[92] + "' or BusinessUnit ='" + site1[93] + "' or BusinessUnit ='" + site1[94] + "' or BusinessUnit ='" + site1[95] + "' or BusinessUnit ='" + site1[96] + "' or BusinessUnit ='" + site1[97] + "' or BusinessUnit ='" + site1[98] + "' or BusinessUnit ='" + site1[99] + "') group by toc";

//s = "SELECT empid,round(SUM(CASE toc WHEN 'LOCAL' THEN price ELSE 0 END),2) AS LOCALPRICE,round(SUM(CASE toc WHEN 'STD' THEN price ELSE 0 END),2) AS STDPRICE, round(SUM(CASE toc WHEN 'ISD' THEN price ELSE 0 END),2) AS ISDPRICE,round(SUM(CASE toc WHEN 'INTERNAL' THEN price ELSE 0 END),2) AS iNETPRICE, count(case when toc = 'STD' then 1 end) as STDCALLS,count(case when toc = 'ISD' then 1 end) as ISDCALLS, count(case when toc = 'LOCAL' then 1 end) as LOCALCALLS, count(case when toc = 'INCOMING' then 1 end) as INCOMCALLS,count(case when toc = 'INTERNAL' then 1 end) as iNETCALLS, (concat(truncate((floor(sum(CASE toc WHEN 'INTERNAL' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'INTERNAL' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'INTERNAL' THEN duration ELSE 0 END)%60)) as iNETDUR,(concat(truncate((floor(sum(CASE toc WHEN 'INCOMING' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'INCOMING' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'INCOMING' THEN duration ELSE 0 END)%60)) as INCOMDUR,(concat(truncate((floor(sum(CASE toc WHEN 'STD' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'STD' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'STD' THEN duration ELSE 0 END)%60)) as STDDUR ,(concat(truncate((floor(sum(CASE toc WHEN 'ISD' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'ISD' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'ISD' THEN duration ELSE 0 END)%60)) as ISDDUR, (concat(truncate((floor(sum(CASE toc WHEN 'LOCAL' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'LOCAL' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'LOCAL' THEN duration ELSE 0 END)%60)) as LOCALDUR,(concat(truncate((floor(SUM(duration))/3600),0),':',truncate((floor((SUM(duration))/60)%60),0),':',SUM(duration)%60)) as TOTALDUR,round(sum(price),2)as TOTALPRICE FROM processeddata_table where dialdate between '" + fromdate + "' and '" + todate + "' AND (BusinessUnit ='" + site1[0] + "' or BusinessUnit ='" + site1[1] + "' or BusinessUnit ='" + site1[2] + "' or BusinessUnit ='" + site1[3] + "' or BusinessUnit ='" + site1[4] + "' or BusinessUnit ='" + site1[5] + "' or BusinessUnit ='" + site1[6] + "' or BusinessUnit ='" + site1[7] + "' or BusinessUnit ='" + site1[8] + "' or BusinessUnit ='" + site1[9] + "' or BusinessUnit ='" + site1[10] + "' or BusinessUnit ='" + site1[11] + "' or BusinessUnit ='" + site1[12] + "' or BusinessUnit ='" + site1[13] + "' or BusinessUnit ='" + site1[14] + "' or BusinessUnit ='" + site1[15] + "' or BusinessUnit ='" + site1[16] + "' or BusinessUnit ='" + site1[17] + "' or BusinessUnit ='" + site1[18] + "' or BusinessUnit ='" + site1[19] + "' or BusinessUnit ='" + site1[20] + "' or BusinessUnit ='" + site1[21] + "' or BusinessUnit ='" + site1[22] + "' or BusinessUnit ='" + site1[23] + "' or BusinessUnit ='" + site1[24] + "' or BusinessUnit ='" + site1[25] + "' or BusinessUnit ='" + site1[26] + "' or BusinessUnit ='" + site1[27] + "' or BusinessUnit ='" + site1[28] + "' or BusinessUnit ='" + site1[29] + "' or BusinessUnit ='" + site1[30] + "' or BusinessUnit ='" + site1[31] + "' or BusinessUnit ='" + site1[32] + "' or BusinessUnit ='" + site1[33] + "' or BusinessUnit ='" + site1[34] + "' or BusinessUnit ='" + site1[35] + "' or BusinessUnit ='" + site1[36] + "' or BusinessUnit ='" + site1[37] + "' or BusinessUnit ='" + site1[38] + "' or BusinessUnit ='" + site1[39] + "' or BusinessUnit ='" + site1[40] + "' or BusinessUnit ='" + site1[41] + "' or BusinessUnit ='" + site1[42] + "' or BusinessUnit ='" + site1[43] + "' or BusinessUnit ='" + site1[44] + "' or BusinessUnit ='" + site1[45] + "' or BusinessUnit ='" + site1[46] + "' or BusinessUnit ='" + site1[47] + "' or BusinessUnit ='" + site1[48] + "' or BusinessUnit ='" + site1[49] + "' or BusinessUnit ='" + site1[50] + "' or BusinessUnit ='" + site1[51] + "' or BusinessUnit ='" + site1[52] + "' or BusinessUnit ='" + site1[53] + "' or BusinessUnit ='" + site1[54] + "' or BusinessUnit ='" + site1[55] + "' or BusinessUnit ='" + site1[56] + "' or BusinessUnit ='" + site1[57] + "' or BusinessUnit ='" + site1[58] + "' or BusinessUnit ='" + site1[59] + "' or BusinessUnit ='" + site1[60] + "' or BusinessUnit ='" + site1[61] + "' or BusinessUnit ='" + site1[62] + "' or BusinessUnit ='" + site1[63] + "' or BusinessUnit ='" + site1[64] + "' or BusinessUnit ='" + site1[65] + "' or BusinessUnit ='" + site1[66] + "' or BusinessUnit ='" + site1[67] + "' or BusinessUnit ='" + site1[68] + "' or BusinessUnit ='" + site1[69] + "' or BusinessUnit ='" + site1[70] + "' or BusinessUnit ='" + site1[71] + "' or BusinessUnit ='" + site1[72] + "' or BusinessUnit ='" + site1[73] + "' or BusinessUnit ='" + site1[74] + "' or BusinessUnit ='" + site1[75] + "' or BusinessUnit ='" + site1[76] + "' or BusinessUnit ='" + site1[77] + "' or BusinessUnit ='" + site1[78] + "'or BusinessUnit ='" + site1[79] + "' or BusinessUnit ='" + site1[80] + "' or BusinessUnit ='" + site1[81] + "' or BusinessUnit ='" + site1[82] + "' or BusinessUnit ='" + site1[83] + "' or BusinessUnit ='" + site1[84] + "' or BusinessUnit ='" + site1[85] + "' or BusinessUnit ='" + site1[86] + "' or BusinessUnit ='" + site1[87] + "' or BusinessUnit ='" + site1[88] + "' or BusinessUnit ='" + site1[89] + "' or BusinessUnit ='" + site1[90] + "' or BusinessUnit ='" + site1[91] + "' or BusinessUnit ='" + site1[92] + "' or BusinessUnit ='" + site1[93] + "' or BusinessUnit ='" + site1[94] + "' or BusinessUnit ='" + site1[95] + "' or BusinessUnit ='" + site1[96] + "' or BusinessUnit ='" + site1[97] + "' or BusinessUnit ='" + site1[98] + "' or BusinessUnit ='" + site1[99] + "' ) group by empid";
}
if (!string.IsNullOrEmpty(fromdate) && !string.IsNullOrEmpty(todate) && !string.IsNullOrEmpty(extension) && string.IsNullOrEmpty(empid) && !string.IsNullOrEmpty(sites))
{
s = " Select Distinct toc,count(dialnumber),(concat(truncate((floor(SUM(duration))/3600),0),':',truncate((floor((SUM(duration))/60)%60),0),':',SUM(duration)%60)) ,round(sum(price),2) from processeddata_table WHERE DialDate between '" + fromdate + "' and '" + todate + "' and Duration != '0' AND Extension='" + extension + "' AND (BusinessUnit ='" + site1[0] + "' or BusinessUnit ='" + site1[1] + "' or BusinessUnit ='" + site1[2] + "' or BusinessUnit ='" + site1[3] + "' or BusinessUnit ='" + site1[4] + "' or BusinessUnit ='" + site1[5] + "' or BusinessUnit ='" + site1[6] + "' or BusinessUnit ='" + site1[7] + "' or BusinessUnit ='" + site1[8] + "' or BusinessUnit ='" + site1[9] + "' or BusinessUnit ='" + site1[10] + "' or BusinessUnit ='" + site1[11] + "' or BusinessUnit ='" + site1[12] + "' or BusinessUnit ='" + site1[13] + "' or BusinessUnit ='" + site1[14] + "' or BusinessUnit ='" + site1[15] + "' or BusinessUnit ='" + site1[16] + "' or BusinessUnit ='" + site1[17] + "' or BusinessUnit ='" + site1[18] + "' or BusinessUnit ='" + site1[19] + "' or BusinessUnit ='" + site1[20] + "' or BusinessUnit ='" + site1[21] + "' or BusinessUnit ='" + site1[22] + "' or BusinessUnit ='" + site1[23] + "' or BusinessUnit ='" + site1[24] + "' or BusinessUnit ='" + site1[25] + "' or BusinessUnit ='" + site1[26] + "' or BusinessUnit ='" + site1[27] + "' or BusinessUnit ='" + site1[28] + "' or BusinessUnit ='" + site1[29] + "' or BusinessUnit ='" + site1[30] + "' or BusinessUnit ='" + site1[31] + "' or BusinessUnit ='" + site1[32] + "' or BusinessUnit ='" + site1[33] + "' or BusinessUnit ='" + site1[34] + "' or BusinessUnit ='" + site1[35] + "' or BusinessUnit ='" + site1[36] + "' or BusinessUnit ='" + site1[37] + "' or BusinessUnit ='" + site1[38] + "' or BusinessUnit ='" + site1[39] + "' or BusinessUnit ='" + site1[40] + "' or BusinessUnit ='" + site1[41] + "' or BusinessUnit ='" + site1[42] + "' or BusinessUnit ='" + site1[43] + "' or BusinessUnit ='" + site1[44] + "' or BusinessUnit ='" + site1[45] + "' or BusinessUnit ='" + site1[46] + "' or BusinessUnit ='" + site1[47] + "' or BusinessUnit ='" + site1[48] + "' or BusinessUnit ='" + site1[49] + "' or BusinessUnit ='" + site1[50] + "' or BusinessUnit ='" + site1[51] + "' or BusinessUnit ='" + site1[52] + "' or BusinessUnit ='" + site1[53] + "' or BusinessUnit ='" + site1[54] + "' or BusinessUnit ='" + site1[55] + "' or BusinessUnit ='" + site1[56] + "' or BusinessUnit ='" + site1[57] + "' or BusinessUnit ='" + site1[58] + "' or BusinessUnit ='" + site1[59] + "' or BusinessUnit ='" + site1[60] + "' or BusinessUnit ='" + site1[61] + "' or BusinessUnit ='" + site1[62] + "' or BusinessUnit ='" + site1[63] + "' or BusinessUnit ='" + site1[64] + "' or BusinessUnit ='" + site1[65] + "' or BusinessUnit ='" + site1[66] + "' or BusinessUnit ='" + site1[67] + "' or BusinessUnit ='" + site1[68] + "' or BusinessUnit ='" + site1[69] + "' or BusinessUnit ='" + site1[70] + "' or BusinessUnit ='" + site1[71] + "' or BusinessUnit ='" + site1[72] + "' or BusinessUnit ='" + site1[73] + "' or BusinessUnit ='" + site1[74] + "' or BusinessUnit ='" + site1[75] + "' or BusinessUnit ='" + site1[76] + "' or BusinessUnit ='" + site1[77] + "' or BusinessUnit ='" + site1[78] + "'or BusinessUnit ='" + site1[79] + "' or BusinessUnit ='" + site1[80] + "' or BusinessUnit ='" + site1[81] + "' or BusinessUnit ='" + site1[82] + "' or BusinessUnit ='" + site1[83] + "' or BusinessUnit ='" + site1[84] + "' or BusinessUnit ='" + site1[85] + "' or BusinessUnit ='" + site1[86] + "' or BusinessUnit ='" + site1[87] + "' or BusinessUnit ='" + site1[88] + "' or BusinessUnit ='" + site1[89] + "' or BusinessUnit ='" + site1[90] + "' or BusinessUnit ='" + site1[91] + "' or BusinessUnit ='" + site1[92] + "' or BusinessUnit ='" + site1[93] + "' or BusinessUnit ='" + site1[94] + "' or BusinessUnit ='" + site1[95] + "' or BusinessUnit ='" + site1[96] + "' or BusinessUnit ='" + site1[97] + "' or BusinessUnit ='" + site1[98] + "' or BusinessUnit ='" + site1[99] + "') group by toc";

//s = "SELECT empid,round(SUM(CASE toc WHEN 'LOCAL' THEN price ELSE 0 END),2) AS LOCALPRICE,round(SUM(CASE toc WHEN 'STD' THEN price ELSE 0 END),2) AS STDPRICE, round(SUM(CASE toc WHEN 'ISD' THEN price ELSE 0 END),2) AS ISDPRICE,round(SUM(CASE toc WHEN 'INTERNAL' THEN price ELSE 0 END),2) AS iNETPRICE, count(case when toc = 'STD' then 1 end) as STDCALLS,count(case when toc = 'ISD' then 1 end) as ISDCALLS, count(case when toc = 'LOCAL' then 1 end) as LOCALCALLS, count(case when toc = 'INCOMING' then 1 end) as INCOMCALLS,count(case when toc = 'INTERNAL' then 1 end) as iNETCALLS, (concat(truncate((floor(sum(CASE toc WHEN 'INTERNAL' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'INTERNAL' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'INTERNAL' THEN duration ELSE 0 END)%60)) as iNETDUR,(concat(truncate((floor(sum(CASE toc WHEN 'INCOMING' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'INCOMING' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'INCOMING' THEN duration ELSE 0 END)%60)) as INCOMDUR,(concat(truncate((floor(sum(CASE toc WHEN 'STD' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'STD' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'STD' THEN duration ELSE 0 END)%60)) as STDDUR ,(concat(truncate((floor(sum(CASE toc WHEN 'ISD' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'ISD' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'ISD' THEN duration ELSE 0 END)%60)) as ISDDUR, (concat(truncate((floor(sum(CASE toc WHEN 'LOCAL' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'LOCAL' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'LOCAL' THEN duration ELSE 0 END)%60)) as LOCALDUR,(concat(truncate((floor(SUM(duration))/3600),0),':',truncate((floor((SUM(duration))/60)%60),0),':',SUM(duration)%60)) as TOTALDUR,round(sum(price),2)as TOTALPRICE FROM processeddata_table where dialdate between '" + fromdate + "' and '" + todate + "' AND extension='" + extension + "' AND (BusinessUnit ='" + site1[0] + "' or BusinessUnit ='" + site1[1] + "' or BusinessUnit ='" + site1[2] + "' or BusinessUnit ='" + site1[3] + "' or BusinessUnit ='" + site1[4] + "' or BusinessUnit ='" + site1[5] + "' or BusinessUnit ='" + site1[6] + "' or BusinessUnit ='" + site1[7] + "' or BusinessUnit ='" + site1[8] + "' or BusinessUnit ='" + site1[9] + "' or BusinessUnit ='" + site1[10] + "' or BusinessUnit ='" + site1[11] + "' or BusinessUnit ='" + site1[12] + "' or BusinessUnit ='" + site1[13] + "' or BusinessUnit ='" + site1[14] + "' or BusinessUnit ='" + site1[15] + "' or BusinessUnit ='" + site1[16] + "' or BusinessUnit ='" + site1[17] + "' or BusinessUnit ='" + site1[18] + "' or BusinessUnit ='" + site1[19] + "' or BusinessUnit ='" + site1[20] + "' or BusinessUnit ='" + site1[21] + "' or BusinessUnit ='" + site1[22] + "' or BusinessUnit ='" + site1[23] + "' or BusinessUnit ='" + site1[24] + "' or BusinessUnit ='" + site1[25] + "' or BusinessUnit ='" + site1[26] + "' or BusinessUnit ='" + site1[27] + "' or BusinessUnit ='" + site1[28] + "' or BusinessUnit ='" + site1[29] + "' or BusinessUnit ='" + site1[30] + "' or BusinessUnit ='" + site1[31] + "' or BusinessUnit ='" + site1[32] + "' or BusinessUnit ='" + site1[33] + "' or BusinessUnit ='" + site1[34] + "' or BusinessUnit ='" + site1[35] + "' or BusinessUnit ='" + site1[36] + "' or BusinessUnit ='" + site1[37] + "' or BusinessUnit ='" + site1[38] + "' or BusinessUnit ='" + site1[39] + "' or BusinessUnit ='" + site1[40] + "' or BusinessUnit ='" + site1[41] + "' or BusinessUnit ='" + site1[42] + "' or BusinessUnit ='" + site1[43] + "' or BusinessUnit ='" + site1[44] + "' or BusinessUnit ='" + site1[45] + "' or BusinessUnit ='" + site1[46] + "' or BusinessUnit ='" + site1[47] + "' or BusinessUnit ='" + site1[48] + "' or BusinessUnit ='" + site1[49] + "' or BusinessUnit ='" + site1[50] + "' or BusinessUnit ='" + site1[51] + "' or BusinessUnit ='" + site1[52] + "' or BusinessUnit ='" + site1[53] + "' or BusinessUnit ='" + site1[54] + "' or BusinessUnit ='" + site1[55] + "' or BusinessUnit ='" + site1[56] + "' or BusinessUnit ='" + site1[57] + "' or BusinessUnit ='" + site1[58] + "' or BusinessUnit ='" + site1[59] + "' or BusinessUnit ='" + site1[60] + "' or BusinessUnit ='" + site1[61] + "' or BusinessUnit ='" + site1[62] + "' or BusinessUnit ='" + site1[63] + "' or BusinessUnit ='" + site1[64] + "' or BusinessUnit ='" + site1[65] + "' or BusinessUnit ='" + site1[66] + "' or BusinessUnit ='" + site1[67] + "' or BusinessUnit ='" + site1[68] + "' or BusinessUnit ='" + site1[69] + "' or BusinessUnit ='" + site1[70] + "' or BusinessUnit ='" + site1[71] + "' or BusinessUnit ='" + site1[72] + "' or BusinessUnit ='" + site1[73] + "' or BusinessUnit ='" + site1[74] + "' or BusinessUnit ='" + site1[75] + "' or BusinessUnit ='" + site1[76] + "' or BusinessUnit ='" + site1[77] + "' or BusinessUnit ='" + site1[78] + "'or BusinessUnit ='" + site1[79] + "' or BusinessUnit ='" + site1[80] + "' or BusinessUnit ='" + site1[81] + "' or BusinessUnit ='" + site1[82] + "' or BusinessUnit ='" + site1[83] + "' or BusinessUnit ='" + site1[84] + "' or BusinessUnit ='" + site1[85] + "' or BusinessUnit ='" + site1[86] + "' or BusinessUnit ='" + site1[87] + "' or BusinessUnit ='" + site1[88] + "' or BusinessUnit ='" + site1[89] + "' or BusinessUnit ='" + site1[90] + "' or BusinessUnit ='" + site1[91] + "' or BusinessUnit ='" + site1[92] + "' or BusinessUnit ='" + site1[93] + "' or BusinessUnit ='" + site1[94] + "' or BusinessUnit ='" + site1[95] + "' or BusinessUnit ='" + site1[96] + "' or BusinessUnit ='" + site1[97] + "' or BusinessUnit ='" + site1[98] + "' or BusinessUnit ='" + site1[99] + "' ) group by empid";
}
if (!string.IsNullOrEmpty(fromdate) && !string.IsNullOrEmpty(todate) && string.IsNullOrEmpty(extension) && !string.IsNullOrEmpty(empid) && !string.IsNullOrEmpty(sites))
{
s = " Select Distinct toc,count(dialnumber),(concat(truncate((floor(SUM(duration))/3600),0),':',truncate((floor((SUM(duration))/60)%60),0),':',SUM(duration)%60)) ,round(sum(price),2) from processeddata_table WHERE DialDate between '" + fromdate + "' and '" + todate + "' and Duration != '0' AND WWID='" + empid + "' AND (BusinessUnit ='" + site1[0] + "' or BusinessUnit ='" + site1[1] + "' or BusinessUnit ='" + site1[2] + "' or BusinessUnit ='" + site1[3] + "' or BusinessUnit ='" + site1[4] + "' or BusinessUnit ='" + site1[5] + "' or BusinessUnit ='" + site1[6] + "' or BusinessUnit ='" + site1[7] + "' or BusinessUnit ='" + site1[8] + "' or BusinessUnit ='" + site1[9] + "' or BusinessUnit ='" + site1[10] + "' or BusinessUnit ='" + site1[11] + "' or BusinessUnit ='" + site1[12] + "' or BusinessUnit ='" + site1[13] + "' or BusinessUnit ='" + site1[14] + "' or BusinessUnit ='" + site1[15] + "' or BusinessUnit ='" + site1[16] + "' or BusinessUnit ='" + site1[17] + "' or BusinessUnit ='" + site1[18] + "' or BusinessUnit ='" + site1[19] + "' or BusinessUnit ='" + site1[20] + "' or BusinessUnit ='" + site1[21] + "' or BusinessUnit ='" + site1[22] + "' or BusinessUnit ='" + site1[23] + "' or BusinessUnit ='" + site1[24] + "' or BusinessUnit ='" + site1[25] + "' or BusinessUnit ='" + site1[26] + "' or BusinessUnit ='" + site1[27] + "' or BusinessUnit ='" + site1[28] + "' or BusinessUnit ='" + site1[29] + "' or BusinessUnit ='" + site1[30] + "' or BusinessUnit ='" + site1[31] + "' or BusinessUnit ='" + site1[32] + "' or BusinessUnit ='" + site1[33] + "' or BusinessUnit ='" + site1[34] + "' or BusinessUnit ='" + site1[35] + "' or BusinessUnit ='" + site1[36] + "' or BusinessUnit ='" + site1[37] + "' or BusinessUnit ='" + site1[38] + "' or BusinessUnit ='" + site1[39] + "' or BusinessUnit ='" + site1[40] + "' or BusinessUnit ='" + site1[41] + "' or BusinessUnit ='" + site1[42] + "' or BusinessUnit ='" + site1[43] + "' or BusinessUnit ='" + site1[44] + "' or BusinessUnit ='" + site1[45] + "' or BusinessUnit ='" + site1[46] + "' or BusinessUnit ='" + site1[47] + "' or BusinessUnit ='" + site1[48] + "' or BusinessUnit ='" + site1[49] + "' or BusinessUnit ='" + site1[50] + "' or BusinessUnit ='" + site1[51] + "' or BusinessUnit ='" + site1[52] + "' or BusinessUnit ='" + site1[53] + "' or BusinessUnit ='" + site1[54] + "' or BusinessUnit ='" + site1[55] + "' or BusinessUnit ='" + site1[56] + "' or BusinessUnit ='" + site1[57] + "' or BusinessUnit ='" + site1[58] + "' or BusinessUnit ='" + site1[59] + "' or BusinessUnit ='" + site1[60] + "' or BusinessUnit ='" + site1[61] + "' or BusinessUnit ='" + site1[62] + "' or BusinessUnit ='" + site1[63] + "' or BusinessUnit ='" + site1[64] + "' or BusinessUnit ='" + site1[65] + "' or BusinessUnit ='" + site1[66] + "' or BusinessUnit ='" + site1[67] + "' or BusinessUnit ='" + site1[68] + "' or BusinessUnit ='" + site1[69] + "' or BusinessUnit ='" + site1[70] + "' or BusinessUnit ='" + site1[71] + "' or BusinessUnit ='" + site1[72] + "' or BusinessUnit ='" + site1[73] + "' or BusinessUnit ='" + site1[74] + "' or BusinessUnit ='" + site1[75] + "' or BusinessUnit ='" + site1[76] + "' or BusinessUnit ='" + site1[77] + "' or BusinessUnit ='" + site1[78] + "'or BusinessUnit ='" + site1[79] + "' or BusinessUnit ='" + site1[80] + "' or BusinessUnit ='" + site1[81] + "' or BusinessUnit ='" + site1[82] + "' or BusinessUnit ='" + site1[83] + "' or BusinessUnit ='" + site1[84] + "' or BusinessUnit ='" + site1[85] + "' or BusinessUnit ='" + site1[86] + "' or BusinessUnit ='" + site1[87] + "' or BusinessUnit ='" + site1[88] + "' or BusinessUnit ='" + site1[89] + "' or BusinessUnit ='" + site1[90] + "' or BusinessUnit ='" + site1[91] + "' or BusinessUnit ='" + site1[92] + "' or BusinessUnit ='" + site1[93] + "' or BusinessUnit ='" + site1[94] + "' or BusinessUnit ='" + site1[95] + "' or BusinessUnit ='" + site1[96] + "' or BusinessUnit ='" + site1[97] + "' or BusinessUnit ='" + site1[98] + "' or BusinessUnit ='" + site1[99] + "') group by toc";

//s = "SELECT empid,round(SUM(CASE toc WHEN 'LOCAL' THEN price ELSE 0 END),2) AS LOCALPRICE,round(SUM(CASE toc WHEN 'STD' THEN price ELSE 0 END),2) AS STDPRICE, round(SUM(CASE toc WHEN 'ISD' THEN price ELSE 0 END),2) AS ISDPRICE,round(SUM(CASE toc WHEN 'INTERNAL' THEN price ELSE 0 END),2) AS iNETPRICE, count(case when toc = 'STD' then 1 end) as STDCALLS,count(case when toc = 'ISD' then 1 end) as ISDCALLS, count(case when toc = 'LOCAL' then 1 end) as LOCALCALLS, count(case when toc = 'INCOMING' then 1 end) as INCOMCALLS,count(case when toc = 'INTERNAL' then 1 end) as iNETCALLS, (concat(truncate((floor(sum(CASE toc WHEN 'INTERNAL' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'INTERNAL' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'INTERNAL' THEN duration ELSE 0 END)%60)) as iNETDUR,(concat(truncate((floor(sum(CASE toc WHEN 'INCOMING' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'INCOMING' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'INCOMING' THEN duration ELSE 0 END)%60)) as INCOMDUR,(concat(truncate((floor(sum(CASE toc WHEN 'STD' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'STD' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'STD' THEN duration ELSE 0 END)%60)) as STDDUR ,(concat(truncate((floor(sum(CASE toc WHEN 'ISD' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'ISD' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'ISD' THEN duration ELSE 0 END)%60)) as ISDDUR, (concat(truncate((floor(sum(CASE toc WHEN 'LOCAL' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'LOCAL' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'LOCAL' THEN duration ELSE 0 END)%60)) as LOCALDUR,(concat(truncate((floor(SUM(duration))/3600),0),':',truncate((floor((SUM(duration))/60)%60),0),':',SUM(duration)%60)) as TOTALDUR,round(sum(price),2)as TOTALPRICE FROM processeddata_table where dialdate between '" + fromdate + "' and '" + todate + "' and EMPID='" + empid + "' AND (BusinessUnit ='" + site1[0] + "' or BusinessUnit ='" + site1[1] + "' or BusinessUnit ='" + site1[2] + "' or BusinessUnit ='" + site1[3] + "' or BusinessUnit ='" + site1[4] + "' or BusinessUnit ='" + site1[5] + "' or BusinessUnit ='" + site1[6] + "' or BusinessUnit ='" + site1[7] + "' or BusinessUnit ='" + site1[8] + "' or BusinessUnit ='" + site1[9] + "' or BusinessUnit ='" + site1[10] + "' or BusinessUnit ='" + site1[11] + "' or BusinessUnit ='" + site1[12] + "' or BusinessUnit ='" + site1[13] + "' or BusinessUnit ='" + site1[14] + "' or BusinessUnit ='" + site1[15] + "' or BusinessUnit ='" + site1[16] + "' or BusinessUnit ='" + site1[17] + "' or BusinessUnit ='" + site1[18] + "' or BusinessUnit ='" + site1[19] + "' or BusinessUnit ='" + site1[20] + "' or BusinessUnit ='" + site1[21] + "' or BusinessUnit ='" + site1[22] + "' or BusinessUnit ='" + site1[23] + "' or BusinessUnit ='" + site1[24] + "' or BusinessUnit ='" + site1[25] + "' or BusinessUnit ='" + site1[26] + "' or BusinessUnit ='" + site1[27] + "' or BusinessUnit ='" + site1[28] + "' or BusinessUnit ='" + site1[29] + "' or BusinessUnit ='" + site1[30] + "' or BusinessUnit ='" + site1[31] + "' or BusinessUnit ='" + site1[32] + "' or BusinessUnit ='" + site1[33] + "' or BusinessUnit ='" + site1[34] + "' or BusinessUnit ='" + site1[35] + "' or BusinessUnit ='" + site1[36] + "' or BusinessUnit ='" + site1[37] + "' or BusinessUnit ='" + site1[38] + "' or BusinessUnit ='" + site1[39] + "' or BusinessUnit ='" + site1[40] + "' or BusinessUnit ='" + site1[41] + "' or BusinessUnit ='" + site1[42] + "' or BusinessUnit ='" + site1[43] + "' or BusinessUnit ='" + site1[44] + "' or BusinessUnit ='" + site1[45] + "' or BusinessUnit ='" + site1[46] + "' or BusinessUnit ='" + site1[47] + "' or BusinessUnit ='" + site1[48] + "' or BusinessUnit ='" + site1[49] + "' or BusinessUnit ='" + site1[50] + "' or BusinessUnit ='" + site1[51] + "' or BusinessUnit ='" + site1[52] + "' or BusinessUnit ='" + site1[53] + "' or BusinessUnit ='" + site1[54] + "' or BusinessUnit ='" + site1[55] + "' or BusinessUnit ='" + site1[56] + "' or BusinessUnit ='" + site1[57] + "' or BusinessUnit ='" + site1[58] + "' or BusinessUnit ='" + site1[59] + "' or BusinessUnit ='" + site1[60] + "' or BusinessUnit ='" + site1[61] + "' or BusinessUnit ='" + site1[62] + "' or BusinessUnit ='" + site1[63] + "' or BusinessUnit ='" + site1[64] + "' or BusinessUnit ='" + site1[65] + "' or BusinessUnit ='" + site1[66] + "' or BusinessUnit ='" + site1[67] + "' or BusinessUnit ='" + site1[68] + "' or BusinessUnit ='" + site1[69] + "' or BusinessUnit ='" + site1[70] + "' or BusinessUnit ='" + site1[71] + "' or BusinessUnit ='" + site1[72] + "' or BusinessUnit ='" + site1[73] + "' or BusinessUnit ='" + site1[74] + "' or BusinessUnit ='" + site1[75] + "' or BusinessUnit ='" + site1[76] + "' or BusinessUnit ='" + site1[77] + "' or BusinessUnit ='" + site1[78] + "'or BusinessUnit ='" + site1[79] + "' or BusinessUnit ='" + site1[80] + "' or BusinessUnit ='" + site1[81] + "' or BusinessUnit ='" + site1[82] + "' or BusinessUnit ='" + site1[83] + "' or BusinessUnit ='" + site1[84] + "' or BusinessUnit ='" + site1[85] + "' or BusinessUnit ='" + site1[86] + "' or BusinessUnit ='" + site1[87] + "' or BusinessUnit ='" + site1[88] + "' or BusinessUnit ='" + site1[89] + "' or BusinessUnit ='" + site1[90] + "' or BusinessUnit ='" + site1[91] + "' or BusinessUnit ='" + site1[92] + "' or BusinessUnit ='" + site1[93] + "' or BusinessUnit ='" + site1[94] + "' or BusinessUnit ='" + site1[95] + "' or BusinessUnit ='" + site1[96] + "' or BusinessUnit ='" + site1[97] + "' or BusinessUnit ='" + site1[98] + "' or BusinessUnit ='" + site1[99] + "' ) group by empid";
}
if (!string.IsNullOrEmpty(fromdate) && !string.IsNullOrEmpty(todate) && !string.IsNullOrEmpty(extension) && !string.IsNullOrEmpty(empid) && !string.IsNullOrEmpty(sites))
{
s = " Select Distinct toc,count(dialnumber),(concat(truncate((floor(SUM(duration))/3600),0),':',truncate((floor((SUM(duration))/60)%60),0),':',SUM(duration)%60)) ,round(sum(price),2) from processeddata_table WHERE DialDate between '" + fromdate + "' and '" + todate + "' and Duration != '0' AND Extension='" + extension + "' AND WWID='" + empid + "' AND (BusinessUnit ='" + site1[0] + "' or BusinessUnit ='" + site1[1] + "' or BusinessUnit ='" + site1[2] + "' or BusinessUnit ='" + site1[3] + "' or BusinessUnit ='" + site1[4] + "' or BusinessUnit ='" + site1[5] + "' or BusinessUnit ='" + site1[6] + "' or BusinessUnit ='" + site1[7] + "' or BusinessUnit ='" + site1[8] + "' or BusinessUnit ='" + site1[9] + "' or BusinessUnit ='" + site1[10] + "' or BusinessUnit ='" + site1[11] + "' or BusinessUnit ='" + site1[12] + "' or BusinessUnit ='" + site1[13] + "' or BusinessUnit ='" + site1[14] + "' or BusinessUnit ='" + site1[15] + "' or BusinessUnit ='" + site1[16] + "' or BusinessUnit ='" + site1[17] + "' or BusinessUnit ='" + site1[18] + "' or BusinessUnit ='" + site1[19] + "' or BusinessUnit ='" + site1[20] + "' or BusinessUnit ='" + site1[21] + "' or BusinessUnit ='" + site1[22] + "' or BusinessUnit ='" + site1[23] + "' or BusinessUnit ='" + site1[24] + "' or BusinessUnit ='" + site1[25] + "' or BusinessUnit ='" + site1[26] + "' or BusinessUnit ='" + site1[27] + "' or BusinessUnit ='" + site1[28] + "' or BusinessUnit ='" + site1[29] + "' or BusinessUnit ='" + site1[30] + "' or BusinessUnit ='" + site1[31] + "' or BusinessUnit ='" + site1[32] + "' or BusinessUnit ='" + site1[33] + "' or BusinessUnit ='" + site1[34] + "' or BusinessUnit ='" + site1[35] + "' or BusinessUnit ='" + site1[36] + "' or BusinessUnit ='" + site1[37] + "' or BusinessUnit ='" + site1[38] + "' or BusinessUnit ='" + site1[39] + "' or BusinessUnit ='" + site1[40] + "' or BusinessUnit ='" + site1[41] + "' or BusinessUnit ='" + site1[42] + "' or BusinessUnit ='" + site1[43] + "' or BusinessUnit ='" + site1[44] + "' or BusinessUnit ='" + site1[45] + "' or BusinessUnit ='" + site1[46] + "' or BusinessUnit ='" + site1[47] + "' or BusinessUnit ='" + site1[48] + "' or BusinessUnit ='" + site1[49] + "' or BusinessUnit ='" + site1[50] + "' or BusinessUnit ='" + site1[51] + "' or BusinessUnit ='" + site1[52] + "' or BusinessUnit ='" + site1[53] + "' or BusinessUnit ='" + site1[54] + "' or BusinessUnit ='" + site1[55] + "' or BusinessUnit ='" + site1[56] + "' or BusinessUnit ='" + site1[57] + "' or BusinessUnit ='" + site1[58] + "' or BusinessUnit ='" + site1[59] + "' or BusinessUnit ='" + site1[60] + "' or BusinessUnit ='" + site1[61] + "' or BusinessUnit ='" + site1[62] + "' or BusinessUnit ='" + site1[63] + "' or BusinessUnit ='" + site1[64] + "' or BusinessUnit ='" + site1[65] + "' or BusinessUnit ='" + site1[66] + "' or BusinessUnit ='" + site1[67] + "' or BusinessUnit ='" + site1[68] + "' or BusinessUnit ='" + site1[69] + "' or BusinessUnit ='" + site1[70] + "' or BusinessUnit ='" + site1[71] + "' or BusinessUnit ='" + site1[72] + "' or BusinessUnit ='" + site1[73] + "' or BusinessUnit ='" + site1[74] + "' or BusinessUnit ='" + site1[75] + "' or BusinessUnit ='" + site1[76] + "' or BusinessUnit ='" + site1[77] + "' or BusinessUnit ='" + site1[78] + "'or BusinessUnit ='" + site1[79] + "' or BusinessUnit ='" + site1[80] + "' or BusinessUnit ='" + site1[81] + "' or BusinessUnit ='" + site1[82] + "' or BusinessUnit ='" + site1[83] + "' or BusinessUnit ='" + site1[84] + "' or BusinessUnit ='" + site1[85] + "' or BusinessUnit ='" + site1[86] + "' or BusinessUnit ='" + site1[87] + "' or BusinessUnit ='" + site1[88] + "' or BusinessUnit ='" + site1[89] + "' or BusinessUnit ='" + site1[90] + "' or BusinessUnit ='" + site1[91] + "' or BusinessUnit ='" + site1[92] + "' or BusinessUnit ='" + site1[93] + "' or BusinessUnit ='" + site1[94] + "' or BusinessUnit ='" + site1[95] + "' or BusinessUnit ='" + site1[96] + "' or BusinessUnit ='" + site1[97] + "' or BusinessUnit ='" + site1[98] + "' or BusinessUnit ='" + site1[99] + "') group by toc";

//s = "SELECT empid,round(SUM(CASE toc WHEN 'LOCAL' THEN price ELSE 0 END),2) AS LOCALPRICE,round(SUM(CASE toc WHEN 'STD' THEN price ELSE 0 END),2) AS STDPRICE, round(SUM(CASE toc WHEN 'ISD' THEN price ELSE 0 END),2) AS ISDPRICE,round(SUM(CASE toc WHEN 'INTERNAL' THEN price ELSE 0 END),2) AS iNETPRICE, count(case when toc = 'STD' then 1 end) as STDCALLS,count(case when toc = 'ISD' then 1 end) as ISDCALLS, count(case when toc = 'LOCAL' then 1 end) as LOCALCALLS, count(case when toc = 'INCOMING' then 1 end) as INCOMCALLS,count(case when toc = 'INTERNAL' then 1 end) as iNETCALLS, (concat(truncate((floor(sum(CASE toc WHEN 'INTERNAL' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'INTERNAL' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'INTERNAL' THEN duration ELSE 0 END)%60)) as iNETDUR,(concat(truncate((floor(sum(CASE toc WHEN 'INCOMING' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'INCOMING' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'INCOMING' THEN duration ELSE 0 END)%60)) as INCOMDUR,(concat(truncate((floor(sum(CASE toc WHEN 'STD' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'STD' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'STD' THEN duration ELSE 0 END)%60)) as STDDUR ,(concat(truncate((floor(sum(CASE toc WHEN 'ISD' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'ISD' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'ISD' THEN duration ELSE 0 END)%60)) as ISDDUR, (concat(truncate((floor(sum(CASE toc WHEN 'LOCAL' THEN duration ELSE 0 END))/3600),0),':',truncate((floor((sum(CASE toc WHEN 'LOCAL' THEN duration ELSE 0 END))/60)%60),0),':',sum(CASE toc WHEN 'LOCAL' THEN duration ELSE 0 END)%60)) as LOCALDUR,(concat(truncate((floor(SUM(duration))/3600),0),':',truncate((floor((SUM(duration))/60)%60),0),':',SUM(duration)%60)) as TOTALDUR,round(sum(price),2)as TOTALPRICE FROM processeddata_table where dialdate between '" + fromdate + "' and '" + todate + "' AND extension='" + extension + "' and EMPID='" + empid + "'AND (BusinessUnit ='" + site1[0] + "' or BusinessUnit ='" + site1[1] + "' or BusinessUnit ='" + site1[2] + "' or BusinessUnit ='" + site1[3] + "' or BusinessUnit ='" + site1[4] + "' or BusinessUnit ='" + site1[5] + "' or BusinessUnit ='" + site1[6] + "' or BusinessUnit ='" + site1[7] + "' or BusinessUnit ='" + site1[8] + "' or BusinessUnit ='" + site1[9] + "' or BusinessUnit ='" + site1[10] + "' or BusinessUnit ='" + site1[11] + "' or BusinessUnit ='" + site1[12] + "' or BusinessUnit ='" + site1[13] + "' or BusinessUnit ='" + site1[14] + "' or BusinessUnit ='" + site1[15] + "' or BusinessUnit ='" + site1[16] + "' or BusinessUnit ='" + site1[17] + "' or BusinessUnit ='" + site1[18] + "' or BusinessUnit ='" + site1[19] + "' or BusinessUnit ='" + site1[20] + "' or BusinessUnit ='" + site1[21] + "' or BusinessUnit ='" + site1[22] + "' or BusinessUnit ='" + site1[23] + "' or BusinessUnit ='" + site1[24] + "' or BusinessUnit ='" + site1[25] + "' or BusinessUnit ='" + site1[26] + "' or BusinessUnit ='" + site1[27] + "' or BusinessUnit ='" + site1[28] + "' or BusinessUnit ='" + site1[29] + "' or BusinessUnit ='" + site1[30] + "' or BusinessUnit ='" + site1[31] + "' or BusinessUnit ='" + site1[32] + "' or BusinessUnit ='" + site1[33] + "' or BusinessUnit ='" + site1[34] + "' or BusinessUnit ='" + site1[35] + "' or BusinessUnit ='" + site1[36] + "' or BusinessUnit ='" + site1[37] + "' or BusinessUnit ='" + site1[38] + "' or BusinessUnit ='" + site1[39] + "' or BusinessUnit ='" + site1[40] + "' or BusinessUnit ='" + site1[41] + "' or BusinessUnit ='" + site1[42] + "' or BusinessUnit ='" + site1[43] + "' or BusinessUnit ='" + site1[44] + "' or BusinessUnit ='" + site1[45] + "' or BusinessUnit ='" + site1[46] + "' or BusinessUnit ='" + site1[47] + "' or BusinessUnit ='" + site1[48] + "' or BusinessUnit ='" + site1[49] + "' or BusinessUnit ='" + site1[50] + "' or BusinessUnit ='" + site1[51] + "' or BusinessUnit ='" + site1[52] + "' or BusinessUnit ='" + site1[53] + "' or BusinessUnit ='" + site1[54] + "' or BusinessUnit ='" + site1[55] + "' or BusinessUnit ='" + site1[56] + "' or BusinessUnit ='" + site1[57] + "' or BusinessUnit ='" + site1[58] + "' or BusinessUnit ='" + site1[59] + "' or BusinessUnit ='" + site1[60] + "' or BusinessUnit ='" + site1[61] + "' or BusinessUnit ='" + site1[62] + "' or BusinessUnit ='" + site1[63] + "' or BusinessUnit ='" + site1[64] + "' or BusinessUnit ='" + site1[65] + "' or BusinessUnit ='" + site1[66] + "' or BusinessUnit ='" + site1[67] + "' or BusinessUnit ='" + site1[68] + "' or BusinessUnit ='" + site1[69] + "' or BusinessUnit ='" + site1[70] + "' or BusinessUnit ='" + site1[71] + "' or BusinessUnit ='" + site1[72] + "' or BusinessUnit ='" + site1[73] + "' or BusinessUnit ='" + site1[74] + "' or BusinessUnit ='" + site1[75] + "' or BusinessUnit ='" + site1[76] + "' or BusinessUnit ='" + site1[77] + "' or BusinessUnit ='" + site1[78] + "'or BusinessUnit ='" + site1[79] + "' or BusinessUnit ='" + site1[80] + "' or BusinessUnit ='" + site1[81] + "' or BusinessUnit ='" + site1[82] + "' or BusinessUnit ='" + site1[83] + "' or BusinessUnit ='" + site1[84] + "' or BusinessUnit ='" + site1[85] + "' or BusinessUnit ='" + site1[86] + "' or BusinessUnit ='" + site1[87] + "' or BusinessUnit ='" + site1[88] + "' or BusinessUnit ='" + site1[89] + "' or BusinessUnit ='" + site1[90] + "' or BusinessUnit ='" + site1[91] + "' or BusinessUnit ='" + site1[92] + "' or BusinessUnit ='" + site1[93] + "' or BusinessUnit ='" + site1[94] + "' or BusinessUnit ='" + site1[95] + "' or BusinessUnit ='" + site1[96] + "' or BusinessUnit ='" + site1[97] + "' or BusinessUnit ='" + site1[98] + "' or BusinessUnit ='" + site1[99] + "' ) group by empid";
}





MySqlConnection con = new MySqlConnection(conn);
MySqlCommand comm = new MySqlCommand(s, con);
con.Open();
MySqlDataReader dr = comm.ExecuteReader();
while (dr.Read())
{

gridparam gridpar = new gridparam();
//gridpar.empid = dr["empid"].ToString();
//gridpar.LOCALPRICE = dr["LOCALPRICE"].ToString();
//gridpar.STDPRICE = dr["STDPRICE"].ToString();
//gridpar.ISDPRICE = dr["ISDPRICE"].ToString();
//gridpar.iNETPRICE = dr["iNETPRICE"].ToString();
//gridpar.STDCALLS = dr["STDCALLS"].ToString();
//gridpar.ISDCALLS = dr["ISDCALLS"].ToString();
//gridpar.LOCALCALLS = dr["LOCALCALLS"].ToString();
//gridpar.INCOMCALLS = dr["INCOMCALLS"].ToString();
//gridpar.iNETCALLS = dr["iNETCALLS"].ToString();
//gridpar.iNETDUR = dr["iNETDUR"].ToString();
//gridpar.INCOMDUR = dr["INCOMDUR"].ToString();
//gridpar.STDDUR = dr["STDDUR"].ToString();
//gridpar.ISDDUR = dr["ISDDUR"].ToString();
//gridpar.LOCALDUR = dr["LOCALDUR"].ToString();
gridpar.CALLTYPE = dr["CALLTYPE"].ToString();
gridpar.TOTALNOOFCALLS= dr["TOTAL NO. OF CALLS"].ToString();
gridpar.TOTALDUR = dr["TOTALDUR"].ToString();
gridpar.TOTALPRICE = dr["TOTALPRICE"].ToString();
param.Add(gridpar);
}
JavaScriptSerializer serializer = new JavaScriptSerializer();

serializer.MaxJsonLength = Int32.MaxValue;

return serializer.Serialize(param);
}
}


WebUserControl.ascx page

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="WebUserControl.ascx.cs" Inherits="WebUserControl" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>
<script type = "text/javascript">
//Script para incluir en el ComboBox1 cada item chekeado del chkListMateriales
function CheckItem(checkBoxList)
{
var options = checkBoxList.getElementsByTagName('input');
var arrayOfCheckBoxLabels= checkBoxList.getElementsByTagName("label");
var s = "";

for(i=0;i<options.length;i++)
{
var opt = options[i];
if(opt.checked)
{
s = s + ", "+ arrayOfCheckBoxLabels[i].innerHTML;
}
}
if(s.length > 0)
{
s = s.substring(2, s.length); //sacar la primer 'coma'
}
var TxtBox = document.getElementById("<%=txtCombo.ClientID%>");
TxtBox.value = s;
document.getElementById('<%=hidVal.ClientID %>').value = s;
}
</script>

<asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
<contenttemplate>
<asp:TextBox ID="txtCombo" runat="server" Width="95px" Font-Size="X-Small"
Height="16px">
<cc1:PopupControlExtender ID="PopupControlExtender111" runat="server"
TargetControlID="txtCombo" PopupControlID="Panel111" Position="Bottom">


<input type="hidden" name="hidVal" id="hidVal" runat="server" />

<asp:Panel ID="Panel111" runat="server" Width="95px" BackColor="#ffffff" BorderColor="#696969" BorderWidth="1" ForeColor="#000000" >
<asp:CheckBox ID="CheckBox1" AutoPostBack ="true" Checked ="true" runat="server" Font-Bold="true" Font-Size="Small" Font-Names="Tahoma" ForeColor="#000000" OnCheckedChanged="CheckBox1_CheckedChanged" Text="Select All" />
<asp:CheckBoxList ID="chkList"
runat="server"
Height="1px" onclick="CheckItem(this)" Font-Bold="True"
Font-Names="Tahoma" Font-Size="Small" Width="100px" style="color:#000000">




Posted
Add a Solution

1 solution

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Web.Services;
using System.Web.Script.Services;
using MySql.Data.MySqlClient;
using System.Web.Script.Serialization;
using System.Net;
using System.IO;

public partial class getdata : System.Web.UI.Page
{

    protected void Page_Load(object sender, EventArgs e)
    {

    }

    public struct s_GridResult
    {

     
        public int page;
        public int total;
        public int record;
        public s_RowData[] rows;

    }
    public struct s_RowData
    {

        public int id;
        public string[] cell;
    }
    [WebMethod]
    public static s_GridResult GetDataTable(string _search, string nd, int rows, int page, string sidx, string sord)
    {

        string sql = "SELECT * FROM processeddata_table  order by " + sidx + @" " + sord + @" ";
        int startindex = (page - 1);
        int endindex = page;

        if (_search == "False" && page >= 2)
        {

            string sql1 = "SELECT * FROM processeddata_table  order by " + sidx + @" " + sord + @"  limit " + page * rows + "," + 2000000 + "";
            DataTable dt1 = new DataTable();
            MySqlConnection conn1 = new MySqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
            MySqlDataAdapter adapter1 = new MySqlDataAdapter(sql1, conn1);
            adapter1.Fill(dt1);
            s_GridResult result1 = new s_GridResult();
            List<s_RowData> rowsadded1 = new List<s_RowData>();
            int idx1 = 1;

            foreach (DataRow row in dt1.Rows)
            {
                s_RowData newrow1 = new s_RowData();
                newrow1.id = idx1++;
                newrow1.cell = new string[12];  //total number of columns  
                newrow1.cell[0] = row[0].ToString();
                newrow1.cell[1] = row[1].ToString();
                newrow1.cell[2] = row[2].ToString();
                newrow1.cell[3] = row[3].ToString();
                newrow1.cell[4] = row[4].ToString();
                newrow1.cell[5] = row[5].ToString();
                newrow1.cell[6] = row[6].ToString();
                newrow1.cell[7] = row[7].ToString();
                newrow1.cell[8] = row[8].ToString();
                newrow1.cell[9] = row[9].ToString();
                newrow1.cell[10] = row[10].ToString();
                newrow1.cell[11] = row[11].ToString();

                rowsadded1.Add(newrow1);

            }
            result1.rows = rowsadded1.ToArray();
            result1.page = page;
            result1.record = rowsadded1.Count;
            result1.total = (result1.record) / (rows) + page;
            return result1;
        }


        if (_search == "True")
        {
            

    //        string getTextValues = Page.Request.Form["jqg1"].ToString(); 
   //string myStringFromTheInput = jqg1.Value;
     
            string sql11 = "SELECT * FROM processeddata_table where code = ";
            DataTable dt11 = new DataTable();
            MySqlConnection conn11 = new MySqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
            MySqlDataAdapter adapter1 = new MySqlDataAdapter(sql11, conn11);
            adapter1.Fill(dt11);
            s_GridResult result11 = new s_GridResult();
            List<s_RowData> rowsadded11 = new List<s_RowData>();
            int idx11 = 1;

            foreach (DataRow row in dt11.Rows)
            {
                s_RowData newrow11 = new s_RowData();
                newrow11.id = idx11++;
                newrow11.cell = new string[12];  //total number of columns  
                newrow11.cell[0] = row[0].ToString();
                newrow11.cell[1] = row[1].ToString();
                newrow11.cell[2] = row[2].ToString();
                newrow11.cell[3] = row[3].ToString();
                newrow11.cell[4] = row[4].ToString();
                newrow11.cell[5] = row[5].ToString();
                newrow11.cell[6] = row[6].ToString();
                newrow11.cell[7] = row[7].ToString();
                newrow11.cell[8] = row[8].ToString();
                newrow11.cell[9] = row[9].ToString();
                newrow11.cell[10] = row[10].ToString();
                newrow11.cell[11] = row[11].ToString();
                rowsadded11.Add(newrow11);

            }
            result11.rows = rowsadded11.ToArray();
            result11.page = page;
            result11.record = rowsadded11.Count;
            result11.total = (result11.record) / (rows) + page;

            // JavaScriptSerializer js = new JavaScriptSerializer();
            // string json = js.Serialize(result);
            return result11;
        }
        DataTable dt = new DataTable();
        MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
        MySqlDataAdapter adapter = new MySqlDataAdapter(sql, conn);
        adapter.Fill(dt);
        s_GridResult result = new s_GridResult();
        List<s_RowData> rowsadded = new List<s_RowData>();
        int idx = 1;

        foreach (DataRow row in dt.Rows)
        {
            s_RowData newrow = new s_RowData();
            newrow.id = idx++;
            newrow.cell = new string[12];  //total number of columns  
            newrow.cell[0] = row[0].ToString();
            newrow.cell[1] = row[1].ToString();
            newrow.cell[2] = row[2].ToString();
            newrow.cell[3] = row[3].ToString();
            newrow.cell[4] = row[4].ToString();
            newrow.cell[5] = row[5].ToString();
            newrow.cell[6] = row[6].ToString();
            newrow.cell[7] = row[7].ToString();
            newrow.cell[8] = row[8].ToString();
            newrow.cell[9] = row[9].ToString();
            newrow.cell[10] = row[10].ToString();
            newrow.cell[11] = row[11].ToString();

            rowsadded.Add(newrow);

        }

        result.rows = rowsadded.ToArray();
        result.page = page;


        result.record = rowsadded.Count;
        result.total = (result.record) / (rows);
        return result;

    }
}
 
Share this answer
 
Posted
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise
Privacy
Cookies
Terms of Use
Last Updated 13 Sep 2013
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web01 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900