private void boxbind()
{
try
{
// dbc.InsertLogs("start find data");
Date();
string where = "";
string where1 = "";
string wherec = "";
string join = string.Empty;
if (todate != null && fromdate != null && todate != "" && fromdate != "")
{
// dbc.InsertLogs("start find data 1");
where = " where [Order].createdOnUtc <= '" + todate + " 23:59:59' and [Order].createdOnUtc >= '" + fromdate + " 00:00:00' ";
if (IsAdmin == "False")
{
if (UserType == "2")
{
where += " and JurisdictionID=" + sJurisdictionId;
}
else if (UserType == "3")
{
join = " Inner Join CustomerAddress ca ON ca.Id = [Order].AddressId ";
where += " and ca.AreaId = (select distinct areaid from DeliveryDetail where DeliveryID =" + DeliveryId + ") and ca.BuildingId in (select BuildingId from DeliveryDetail where DeliveryID = " + DeliveryId + ")";
}
}
where1 = " AND [Order].createdOnUtc <= '" + todate + " 23:59:59' and [Order].createdOnUtc >= '" + fromdate + " 00:00:00'";
wherec = " where DOC <= '" + todate + " 23:59:59' AND DOC >= '" + fromdate + " 00:00:00' ";
}
else
{
where = " where [Order].createdOnUtc <='" + dbc.getindiantime().ToString("dd/MMM/yyyy") + " 23:59:59' and [Order].createdOnUtc>='" + dbc.getindiantime().ToString("dd/MMM/yyyy") + " 00:00:00' ";
if (IsAdmin == "False")
{
if (UserType == "2")
{
where += " and JurisdictionID=" + sJurisdictionId;
}
else if (UserType == "3")
{
join = " Inner Join CustomerAddress ca ON ca.Id = [Order].AddressId ";
where += " and ca.AreaId = (select distinct areaid from DeliveryDetail where DeliveryID =" + DeliveryId + ") and ca.BuildingId in (select BuildingId from DeliveryDetail where DeliveryID = " + DeliveryId + ")";
}
}
where1 = " AND [Order].createdOnUtc <= '" + dbc.getindiantime().ToString("dd/MMM/yyyy") + " 23:59:59' and [Order].createdOnUtc >= '" + dbc.getindiantime().ToString("dd/MMM/yyyy") + " 00:00:00'";
wherec = " where DOC <= '" + dbc.getindiantime().ToString("dd/MMM/yyyy") + " 23:59:59' AND DOC >= '" + dbc.getindiantime().ToString("dd/MMM/yyyy") + " 00:00:00' ";
}
// dbc.InsertLogs("start find data2");
string franchiseeQry = string.Empty;
if (UserType == "4")
{
franchiseeQry = "SELECT C.Id " +
" FROM [customer_franchise_link] FL " +
" INNER JOIN Franchisee F ON F.FranchiseeCustomerCode = FL.fcode" +
" INNER JOIN Customer C ON C.Mobile = FL.Mobile " +
" WHERE F.FranchiseeId = " + FranchiseeId;
//" AND FL.CreatedOn <= '" + todate + " 23:59:59' AND FL.CreatedOn >= '" + fromdate + " 00:00:00' ";
}
else if (UserType == "5")
{
franchiseeQry = "SELECT C.Id " +
" FROM [customer_franchise_link] FL " +
" INNER JOIN Customer C ON C.Mobile = FL.Mobile " +
" AND (FL.fcode in (select M.MasterFranchiseeCustomerCode from MasterFranchisee M where M.MasterFranchiseeId = " + FranchiseeId + ") " +
" OR FL.fcode in (select F.FranchiseeCustomerCode from Franchisee F where F.MasterFranchiseeId = " + FranchiseeId + ")) ";
//" WHERE F.MasterFranchiseeId = " + FranchiseeId +
// " AND FL.CreatedOn <= '" + todate + " 23:59:59' AND FL.CreatedOn >= '" + fromdate + " 00:00:00' ";
}
else if (UserType == "6")
{
franchiseeQry = "SELECT C.Id " +
" FROM [customer_franchise_link] FL " +
" INNER JOIN Customer C ON C.Mobile = FL.Mobile " +
" WHERE (FL.fcode in (select S.SuperFranchiseeCustomerCode from SuperFranchisee S where S.SuperFranchiseeId = " + FranchiseeId + ") " +
" OR FL.fcode in (select M.MasterFranchiseeCustomerCode from MasterFranchisee M where M.SuperFranchiseeId = " + FranchiseeId + ") " +
" OR FL.fcode in (select FL.FranchiseeCustomerCode from Franchisee FL where FL.MasterFranchiseeId IN (select ML.MasterFranchiseeId from MasterFranchisee ML where ML.SuperFranchiseeId = " + FranchiseeId + ")) " +
" OR FL.fcode in (select F.FranchiseeCustomerCode from Franchisee F where F.SuperFranchiseeId = " + FranchiseeId + ")) ";
}
string customerIds = string.Empty;
DataTable dtFranchisees = dbc.GetDataTable(franchiseeQry);
if (dtFranchisees.Rows.Count > 0)
{
int iCtr = 0;
foreach (DataRow item in dtFranchisees.Rows)
{
if (iCtr > 0)
{
customerIds += ",";
}
++iCtr;
customerIds += item.ItemArray[0];
}
}
string qry = string.Empty;
if (UserType == "4" || UserType == "5" || UserType == "6")
{
qry = "Select * from [Order] " + join + where + " and CustomerId in(" + customerIds + ") and OrderStatusId<>90";
}
else
{
qry = "Select * from [Order] " + join + where;
}
AlertMsg(qry);
DataTable dt = dbc.GetDataTable(qry);
dbc.InsertLogs(qry);
if (dt != null && dt.Rows.Count > 0)
{
ltrReqVsGrn.Text = dt.Rows.Count.ToString();
}
else
{
ltrReqVsGrn.Text = "0";
}
//dbc.InsertLogs("start find data4");
string q = string.Empty;
if (UserType == "4")
{
q = " Select Count(FL.Id) as totalCust FROM [customer_franchise_link] FL INNER JOIN Franchisee F ON F.FranchiseeCustomerCode = FL.fcode" +
" INNER JOIN Customer C ON C.Mobile = FL.Mobile " +
" where FL.CreatedOn <= '" + todate + " 23:59:59' AND FL.CreatedOn >= '" + fromdate + " 00:00:00' " +
" AND F.FranchiseeId = " + FranchiseeId;
}
else if (UserType == "5")
{
q = " Select Count(FL.Id) as totalCust FROM [customer_franchise_link] FL" +
" INNER JOIN Customer C ON C.Mobile = FL.Mobile " +
" where FL.CreatedOn <= '" + todate + " 23:59:59' AND FL.CreatedOn >= '" + fromdate + " 00:00:00' " +
" AND (FL.fcode in (select M.MasterFranchiseeCustomerCode from MasterFranchisee M where M.MasterFranchiseeId = " + FranchiseeId + ") " +
" OR FL.fcode in (select F.FranchiseeCustomerCode from Franchisee F where F.MasterFranchiseeId = " + FranchiseeId + ")) ";
}
else if (UserType == "6")
{
q = " Select Count(FL.Id) as totalCust FROM [customer_franchise_link] FL " +
" INNER JOIN Customer C ON C.Mobile = FL.Mobile " +
" where FL.CreatedOn <= '" + todate + " 23:59:59' AND FL.CreatedOn >= '" + fromdate + " 00:00:00' " +
" AND (FL.fcode in (select S.SuperFranchiseeCustomerCode from SuperFranchisee S where S.SuperFranchiseeId = " + FranchiseeId + ") " +
" OR FL.fcode in (select M.MasterFranchiseeCustomerCode from MasterFranchisee M where M.SuperFranchiseeId = " + FranchiseeId + ") " +
" OR FL.fcode in (select FL.FranchiseeCustomerCode from Franchisee FL where FL.MasterFranchiseeId IN (select ML.MasterFranchiseeId from MasterFranchisee ML where ML.SuperFranchiseeId = " + FranchiseeId + ")) " +
" OR FL.fcode in (select F.FranchiseeCustomerCode from Franchisee F where F.SuperFranchiseeId = " + FranchiseeId + ")) ";
}
else
{
q = "Select Count(Id) as totalCust FROM Customer " + wherec;
}
AlertMsg(q);
DataTable d = dbc.GetDataTable(q);
if (d != null && d.Rows.Count > 0)
{
ltrcustomer.Text = d.Rows[0]["totalCust"].ToString();
}
else
{
ltrcustomer.Text = "0";
}
// dbc.InsertLogs("start find data5");
string qr = "Select Count(BuyWith) as totalorder FROM [Order] where BuyWith=1 " + where1;
AlertMsg(qr);
DataTable dr = dbc.GetDataTable(qr);
if (dr != null && dr.Rows.Count > 0)
{
Ltr1.Text = dr.Rows[0]["totalorder"].ToString();
// dbc.InsertLogs(dr.Rows[0]["totalorder"].ToString());
}
else
{
Ltr1.Text = "0";
}
string s = "Select Count(BuyWith) as totc FROM [Order]where BuyWith=2 " + where1;
AlertMsg(s);
DataTable drt = dbc.GetDataTable(s);
if (drt != null && drt.Rows.Count > 0)
{
Literal2.Text = drt.Rows[0]["totc"].ToString();
}
else
{
Literal2.Text = "0";
}
string r = "Select Count(BuyWith) as toc FROM [Order]where BuyWith=6 " + where1;
AlertMsg(r);
DataTable t = dbc.GetDataTable(r);
if (t != null && t.Rows.Count > 0)
{
Ltr3.Text = t.Rows[0]["toc"].ToString();
}
else
{
Ltr3.Text = "0";
}
}
catch (Exception ee)
{
ltrErr.Text = ee.Message;
//throw;
}
}
private void boxbind()
{
try
{
Date();
string where = "";
if (todate != null && fromdate != null && todate != "" && fromdate != "")
{
where = " where Cr_date <= '" + todate + " 23:59:59' and Cr_date >= '" + fromdate + " 00:00:00' ";
if (IsAdmin == "False")
{
where += " and JurisdictionID=" + sJurisdictionId;
}
}
else
{
where = " where Cr_date <='" + dbc.getindiantime().ToString("dd/MMM/yyyy") + " 23:59:59' and Cr_date >='" + dbc.getindiantime().ToString("dd/MMM/yyyy") + " 00:00:00' ";
if (IsAdmin == "False")
{
where += " and JurisdictionID=" + sJurisdictionId;
}
}
string qry = "SELECT Count(Id) AS NumberOfCount FROM tblWalletCustomerHistory " + where;
DataTable dt = dbc.GetDataTable(qry);
dbc.InsertLogs(qry);
if (dt != null && dt.Rows.Count > 0)
{
ltrCreatedTransaction.Text = dt.Rows[0]["NumberOfCount"].ToString();
}
else
{
ltrCreatedTransaction.Text = "0";
}
string debitqry = "SELECT Count(Id) AS NumberOfCount FROM tblWalletCustomerHistory " + where +
" AND Dr_amount > 0 ";
DataTable debitdt = dbc.GetDataTable(debitqry);
dbc.InsertLogs(debitqry);
if (debitdt != null && debitdt.Rows.Count > 0)
{
ltrHasgoneCount.Text = debitdt.Rows[0]["NumberOfCount"].ToString();
}
else
{
ltrHasgoneCount.Text = "0";
}
string creditqry = "SELECT Count(Id) AS NumberOfCount FROM tblWalletCustomerHistory " + where +
" AND Cr_amount > 0 AND Dr_amount = 0 ";
DataTable creditdt = dbc.GetDataTable(creditqry);
dbc.InsertLogs(creditqry);
if (creditdt != null && creditdt.Rows.Count > 0)
{
ltrhascame.Text = creditdt.Rows[0]["NumberOfCount"].ToString();
}
else
{
ltrhascame.Text = "0";
}
string successqry = "SELECT Count(Id) AS NumberOfCount FROM tblWalletCustomerHistory " + where +
" AND balance = 0 ";
DataTable successdt = dbc.GetDataTable(successqry);
dbc.InsertLogs(successqry);
if (successdt != null && successdt.Rows.Count > 0)
{
ltrissuccess.Text = successdt.Rows[0]["NumberOfCount"].ToString();
}
else
{
ltrissuccess.Text = "0";
}
}
catch (Exception ee)
{
ltrErr.Text = ee.Message;
}
}