private void btnView_Click(object sender, EventArgs e)
{
try
{
ReportDocument document = new ReportDocument();
string path = string.Empty;
if (VoucherType == "BP" || VoucherType == "BR") // Bank Voucher
{
path = Application.StartupPath + "/rpt/Accounts/rptBankVoucher.rpt";
}
else
{
path = Application.StartupPath + "/rpt/Accounts/rptVoucher.rpt";
}
document.Load(path);
DataTable dtReport = new DataTable();
if (VoucherType == "BP" || VoucherType == "BR") // Bank Voucher
{
dtReport = manageAccount.GetReportBankVoucher(txtFromVoucherNumber.Text, txtToVoucherNumber.Text, dtpFrom.Value, dtpTo.Value, this.VoucherType);
}
else
{
dtReport = manageAccount.GetReportVoucher(txtFromVoucherNumber.Text, txtToVoucherNumber.Text, dtpFrom.Value, dtpTo.Value, this.VoucherType);
}
document.SetDataSource(dtReport);
Utility.SetReportDefaultParameter(ref document);
CrViewer.ReportSource = document;
CrViewer.Refresh();
}
catch (Exception ex)
{
}
}
protected void CrViewer_Navigate(object source, CrystalDecisions.Web.NavigateEventArgs e)
{
if (isNavigated)
{
return;
}
if (e.CurrentPageNumber != e.NewPageNumber)
{
isNavigated = true;
CrViewer.ShowNthPage(e.NewPageNumber);
}
}
protected void CrViewer_ReportRefresh(object source, CrystalDecisions.Web.ViewerEventArgs e)
{
CrViewer.RefreshReport();
}
protected void Page_Load(object sender, EventArgs e)
{
try
{
isNavigated = false;
string UserId = EventFunctions.Functions.GetSessionUserId();
if (UserId == "")
{
return;
}
HideWindow();
string sCmd = ("" + Request.QueryString["cmd"]).ToString();
if ((Request.QueryString["ReportName"] != null) && (Request.QueryString["DataSource"] != null) && sCmd != "finished")
{
//BL.Report repObj = new BL.Report();
string _reportname = null;
DataTable _datasource = null;
string _reportselectionformula = null;
bool _grouptree = false;
string _reporttitle = null;
string _printType = null;
DataTable[] _dt = new DataTable[10];
_reportname = Request.QueryString["ReportName"];
if (Request.QueryString["ReportSelectionFormula"] != null)
{
_reportselectionformula = Request.QueryString["ReportSelectionFormula"];
_reportselectionformula = _reportselectionformula.Replace("~", "'");
}
_grouptree = Convert.ToBoolean(Request.QueryString["GroupTree"]);
_reporttitle = Request.QueryString["ReportTitle"];
_printType = Request.QueryString["PrintType"];
/*repObj.DateParameter1 = Request.QueryString["DateParameter1"];
* repObj.DateParameter2 = Request.QueryString["DateParameter2"];
* repObj.DateParameter3 = Request.QueryString["DateParameter3"];
* repObj.DateParameter4 = Request.QueryString["DateParameter4"];
* repObj.DecimalParameter1 = Request.QueryString["DecimalParameter1"];
* repObj.DecimalParameter2 = Request.QueryString["DecimalParameter2"];
* repObj.StringParameter1 = "" + Request.QueryString["sP1"];
* repObj.StringParameter2 = "" + Request.QueryString["sP2"];
* repObj.StringParameter3 = "" + Request.QueryString["sP3"];
* repObj.StringParameter4 = "" + Request.QueryString["sP4"];
* repObj.StringParameter5 = "" + Request.QueryString["sP5"];
* repObj.StringParameter6 = "" + Request.QueryString["sP6"];
* repObj.BoolParameter1 = Request.QueryString["BoolParameter1"];
* repObj.BoolParameter2 = Request.QueryString["BoolParameter2"];
* if (("" + Request.QueryString["StringArray"]).ToString().Trim() != "")
* repObj.StringArray = Request.QueryString["StringArray"].Trim().Split(',');
* int iSubRptCnt = System.Common.Functions.ToInt(Request.QueryString["SubReports"]);
* if (iSubRptCnt > 0)
* _dt = repObj.LoadReportDatasourceArray(Request.QueryString["DataSource"]);
* else
* _datasource = repObj.LoadReportDatasource(Request.QueryString["DataSource"]);*/
int SalesId = Functions.ToInt(Request.QueryString["SalesId"]);
int iSubRptCnt = 0;
ParameterValues myparameterValues = new ParameterValues();
ParameterDiscreteValue myparamDiscreteValue = new ParameterDiscreteValue();
DBConnection DBConn = new DBConnection();
switch (_reportname)
{
case "Receipt":
string sql = "select count(*) as cnt from ProductDetail P, SalesDetails S where P.SalesDetailId = S.SalesDetailId and status = 3 and S.SalesMasterId = " + SalesId;
string returnCnt = DBConn.RetData(sql, CommandType.Text);
_datasource = DBConn.ExecuteDataSet("SELECT SalesMaster.SalesMasterId SalesId,DATE_FORMAT(SalesDate,'%m/%d/%Y') SalesDate,CustomerName,DATE_FORMAT(SalesDate,'%h:%i %p') as `SalesTime`,concat(IFNULL((SELECT Name FROM ProductMaster WHERE ProductMaster.ProductId = SalesDetails.ProductId),''), IFNULL(SalesDetails.ProductName,''), IFNULL(concat(' (SD Order #: ',SDOrderNo,')'),'')) as Description,Count as Quantity,SalesAmount as Price,'' as PayType,0 as PayAmount, " + returnCnt + " as ReturnCount FROM SalesMaster,SalesDetails where SalesMaster.SalesMasterId = SalesDetails.SalesMasterId and SalesMaster.SalesMasterId= " + SalesId, CommandType.Text).Tables[0];
_reportname = "Receipt.rpt";
break;
case "Sales":
string sFrom = "" + Request.QueryString["fromd"];
string sTo = "" + Request.QueryString["tod"];
DateTime dt1;
sql = "SELECT '0' as type, 0 as SRID, SalesMasterId ID, DATE_FORMAT(SalesDate,'%m/%d/%Y %h:%i %p') SalesDate, SalesDate sdt, PurchaseAmount as Amount, GrandTotal GT, (SELECT Name FROM LocationMaster WHERE LocationMaster.LocationId = SalesMaster.LocationId) as Location, (SELECT UserName FROM UserMaster WHERE UserMaster.UserId = SalesMaster.ClerkId) as Clerk, CustomerName, (SELECT SUM(COUNT) FROM SalesDetails WHERE SalesMasterId = SalesMaster.SalesMasterId) ticket, ";
if (sFrom.Trim() != "" && sTo.Trim() != "")
{
sql += "'" + sFrom + "' sFrom,'" + sTo + "' sTo FROM SalesMaster where 1=1";
dt1 = DateTime.Parse(sFrom);
sFrom = dt1.ToString("yyyy-MM-dd");
dt1 = DateTime.Parse(sTo);
sTo = dt1.ToString("yyyy-MM-dd");
sql += " and (SalesDate between '" + sFrom + " 00:00:01' and '" + sTo + " 23:59:59')";
}
else if (sFrom.Trim() != "")
{
sql += "'" + sFrom + "' sFrom,'' sTo FROM SalesMaster where 1=1";
dt1 = DateTime.Parse(sFrom);
sFrom = dt1.ToString("yyyy-MM-dd");
sql += " and SalesDate >= '" + sFrom + " 00:00:01'";
}
else if (sTo.Trim() != "")
{
sql += "'' sFrom,'" + sTo + "' sTo FROM SalesMaster where 1=1";
dt1 = DateTime.Parse(sTo);
sTo = dt1.ToString("yyyy-MM-dd");
sql += " and SalesDate <= '" + sTo + " 23:59:59'";
}
else
{
sql += "'' sFrom,'' sTo FROM SalesMaster where 1=1";
}
//sql += " order by sdt";
sql += " union SELECT '1' as type, SalesReturnMasterId SRID, (SELECT SalesMasterId from SalesDetails, SalesReturnDetails WHERE SalesDetails.SalesDetailId = SalesReturnDetails.SalesDetailId and SalesReturnDetails.SalesReturnMasterId = SalesReturnMaster.SalesReturnMasterId limit 1) as ID, DATE_FORMAT(SalesReturnDate,'%m/%d/%Y %h:%i %p') SalesDate, SalesReturnDate sdt, 0 as Amount, Amount GT, (SELECT Name FROM LocationMaster WHERE LocationMaster.LocationId = SalesReturnMaster.LocationId) as Location, (SELECT UserName FROM UserMaster WHERE UserMaster.UserId = SalesReturnMaster.ClerkId) as Clerk, '' as CustomerName, (SELECT SUM(Qty) FROM SalesReturnDetails WHERE SalesReturnMasterId = SalesReturnMaster.SalesReturnMasterId) ticket, ";
if (sFrom.Trim() != "" && sTo.Trim() != "")
{
sql += "'" + sFrom + "' sFrom,'" + sTo + "' sTo FROM SalesReturnMaster where 1=1";
dt1 = DateTime.Parse(sFrom);
sFrom = dt1.ToString("yyyy-MM-dd");
dt1 = DateTime.Parse(sTo);
sTo = dt1.ToString("yyyy-MM-dd");
sql += " and (SalesReturnDate between '" + sFrom + " 00:00:01' and '" + sTo + " 23:59:59')";
}
else if (sFrom.Trim() != "")
{
sql += "'" + sFrom + "' sFrom,'' sTo FROM SalesReturnMaster where 1=1";
dt1 = DateTime.Parse(sFrom);
sFrom = dt1.ToString("yyyy-MM-dd");
sql += " and SalesReturnDate >= '" + sFrom + " 00:00:01'";
}
else if (sTo.Trim() != "")
{
sql += "'' sFrom,'" + sTo + "' sTo FROM SalesReturnMaster where 1=1";
dt1 = DateTime.Parse(sTo);
sTo = dt1.ToString("yyyy-MM-dd");
sql += " and SalesReturnDate <= '" + sTo + " 23:59:59'";
}
else
{
sql += "'' sFrom,'' sTo FROM SalesReturnMaster where 1=1";
}
sql += " order by type, sdt";
_datasource = DBConn.ExecuteDataSet(sql, CommandType.Text).Tables[0];
_reportname = "Sales.rpt";
break;
case "SalesDetails":
sFrom = "" + Request.QueryString["fromd"];
sTo = "" + Request.QueryString["tod"];
sql = @"SELECT '0' as type, 0 as SalesMasterId, SalesMaster.SalesMasterId SalesId,DATE_FORMAT(SalesDate,'%m/%d/%Y %h:%i %p') SalesDate,PurchaseAmount AS Amount,
DATE_FORMAT(SalesDate,'%m/%d/%Y') SD,SalesDetails.SerialNo,
(SELECT NAME FROM LocationMaster WHERE LocationMaster.LocationId = SalesMaster.LocationId) AS Location,
(SELECT UserName FROM UserMaster WHERE UserMaster.UserId = SalesMaster.ClerkId) AS Clerk,CustomerName,
concat(IFNULL((SELECT Name FROM ProductMaster WHERE ProductMaster.ProductId = SalesDetails.ProductId),''), IFNULL(SalesDetails.ProductName,'')) AS Description,
COUNT AS Quantity,SalesAmount AS Price,
(SELECT CASE WHEN PaymentType=1 THEN 'Cash' WHEN PaymentType=2 THEN 'Card' Else 'Vouher' END FROM PaymentDetails WHERE SalesMasterId = SalesMaster.SalesMasterId) paytype,
(SELECT CardAuthorizationId FROM PaymentDetails WHERE SalesMasterId = SalesMaster.SalesMasterId) authcode,";
if (sFrom.Trim() != "" && sTo.Trim() != "")
{
sql += "'" + sFrom + "' sFrom,'" + sTo + "' sTo FROM SalesMaster,SalesDetails WHERE SalesMaster.SalesMasterId = SalesDetails.SalesMasterId ";
dt1 = DateTime.Parse(sFrom);
sFrom = dt1.ToString("yyyy-MM-dd");
dt1 = DateTime.Parse(sTo);
sTo = dt1.ToString("yyyy-MM-dd");
sql += " and (SalesMaster.SalesDate between '" + sFrom + " 00:00:01' and '" + sTo + " 23:59:59')";
}
else if (sFrom.Trim() != "")
{
sql += "'" + sFrom + "' sFrom,'' sTo FROM SalesMaster,SalesDetails WHERE SalesMaster.SalesMasterId = SalesDetails.SalesMasterId";
dt1 = DateTime.Parse(sFrom);
sFrom = dt1.ToString("yyyy-MM-dd");
sql += " and SalesMaster.SalesDate >= '" + sFrom + " 00:00:01'";
}
else if (sTo.Trim() != "")
{
sql += "'' sFrom,'" + sTo + "' sTo FROM SalesMaster,SalesDetails WHERE SalesMaster.SalesMasterId = SalesDetails.SalesMasterId";
dt1 = DateTime.Parse(sTo);
sTo = dt1.ToString("yyyy-MM-dd");
sql += " and SalesMaster.SalesDate <= '" + sTo + " 23:59:59'";
}
else
{
sql += "'' sFrom,'' sTo FROM SalesMaster,SalesDetails WHERE SalesMaster.SalesMasterId = SalesDetails.SalesMasterId";
}
//sql += " order by SalesMaster.SalesDate";
sql += @" union SELECT '1' as type, SalesReturnMaster.SalesReturnMasterId SalesMasterId, (select SalesMasterId from SalesDetails where SalesDetailId = SalesReturnDetails.SalesDetailId limit 1) as SalesId, DATE_FORMAT(SalesReturnDate,'%m/%d/%Y %h:%i %p') SalesDate, SalesReturnMaster.Amount AS Amount,
DATE_FORMAT(SalesReturnDate,'%m/%d/%Y') SD, SalesReturnDetails.SerialNo,
(SELECT NAME FROM LocationMaster WHERE LocationMaster.LocationId = SalesReturnMaster.LocationId) AS Location,
(SELECT UserName FROM UserMaster WHERE UserMaster.UserId = SalesReturnMaster.ClerkId) AS Clerk, '' as CustomerName,
(SELECT NAME FROM ProductMaster WHERE ProductMaster.ProductId = SalesReturnDetails.ProductId) AS Description,
Qty AS Quantity, SalesReturnDetails.Amount AS Price,
(SELECT CASE WHEN PaymentType=1 THEN 'Cash' WHEN PaymentType=2 THEN 'Card' Else 'Vouher' END FROM PaymentDetails WHERE SalesMasterId = SalesReturnMaster.SalesReturnMasterId) paytype,
'' as authcode,";
if (sFrom.Trim() != "" && sTo.Trim() != "")
{
sql += "'" + sFrom + "' sFrom,'" + sTo + "' sTo FROM SalesReturnMaster, SalesReturnDetails WHERE SalesReturnMaster.SalesReturnMasterId = SalesReturnDetails.SalesReturnMasterId ";
dt1 = DateTime.Parse(sFrom);
sFrom = dt1.ToString("yyyy-MM-dd");
dt1 = DateTime.Parse(sTo);
sTo = dt1.ToString("yyyy-MM-dd");
sql += " and (SalesReturnMaster.SalesReturnDate between '" + sFrom + " 00:00:01' and '" + sTo + " 23:59:59')";
}
else if (sFrom.Trim() != "")
{
sql += "'" + sFrom + "' sFrom,'' sTo FROM SalesReturnMaster,SalesReturnDetails WHERE SalesReturnMaster.SalesReturnMasterId = SalesReturnDetails.SalesReturnMasterId";
dt1 = DateTime.Parse(sFrom);
sFrom = dt1.ToString("yyyy-MM-dd");
sql += " and SalesReturnMaster.SalesReturnDate >= '" + sFrom + " 00:00:01'";
}
else if (sTo.Trim() != "")
{
sql += "'' sFrom,'" + sTo + "' sTo FROM SalesReturnMaster,SalesReturnDetails WHERE SalesReturnMaster.SalesReturnMasterId = SalesReturnDetails.SalesReturnMasterId";
dt1 = DateTime.Parse(sTo);
sTo = dt1.ToString("yyyy-MM-dd");
sql += " and SalesReturnMaster.SalesReturnDate <= '" + sTo + " 23:59:59'";
}
else
{
sql += "'' sFrom,'' sTo FROM SalesReturnMaster,SalesReturnDetails WHERE SalesReturnMaster.SalesReturnMasterId = SalesReturnDetails.SalesReturnMasterId";
}
sql += " order by SalesDate";
_datasource = DBConn.ExecuteDataSet(sql, CommandType.Text).Tables[0];
_reportname = "SalesDetails.rpt";
break;
case "EventSoldCount":
sFrom = "" + Request.QueryString["fromd"];
sTo = "" + Request.QueryString["tod"];
sql = @"select '0' as type, L.Name as location_name, count(*) as product_count, P.Name as product_name, '" + sFrom + "' as from_date, '" + sTo + @"' as to_date from SalesMaster S, SalesDetails D, LocationMaster L, ProductMaster P, ProductDetail PD
where S.LocationId = L.LocationId and S.SalesMasterId = D.SalesMasterId and D.ProductId = P.ProductId and P.ProductId = PD.ProductId and PD.SalesDetailId = D.SalesDetailId";
if (sFrom != "")
{
sql += " and S.SalesDate >= '" + Convert.ToDateTime(sFrom).ToString("yyyy-MM-dd") + " 00:00:00' ";
}
if (sTo != "")
{
sql += " and S.SalesDate <= '" + Convert.ToDateTime(sTo).ToString("yyyy-MM-dd") + @" 23:59:59'";
}
sql += " group by L.LocationId, L.Name, P.ProductId, P.Name ";
sql += @" union select '1' as type, L.Name as location_name, count(*) as product_count, P.Name as product_name, '" + sFrom + "' as from_date, '" + sTo + @"' as to_date from SalesReturnMaster S, SalesReturnDetails D, LocationMaster L, ProductMaster P, ProductDetail PD
where S.LocationId = L.LocationId and S.SalesReturnMasterId = D.SalesReturnMasterId and D.ProductId = P.ProductId and P.ProductId = PD.ProductId and PD.SalesReturnDetailId = D.SalesReturnDetailId";
if (sFrom != "")
{
sql += " and S.SalesReturnDate >= '" + Convert.ToDateTime(sFrom).ToString("yyyy-MM-dd") + " 00:00:00' ";
}
if (sTo != "")
{
sql += " and S.SalesReturnDate <= '" + Convert.ToDateTime(sTo).ToString("yyyy-MM-dd") + @" 23:59:59'";
}
sql += " group by type, L.LocationId, L.Name, P.ProductId, P.Name order by type, location_name, product_name";
_datasource = DBConn.ExecuteDataSet(sql, CommandType.Text).Tables[0];
_reportname = "EventSoldCount.rpt";
break;
case "CategorySummary":
string CategoryId = "" + Request.QueryString["category_id"];
string CategoryName = "" + Request.QueryString["category_name"];
sFrom = "" + Request.QueryString["fromd"];
sTo = "" + Request.QueryString["tod"];
sql = "select * from Category where CategoryId = " + CategoryId;
DataTable dt = DBConn.ExecuteDataSet(sql, CommandType.Text).Tables[0];
if (("" + dt.Rows[0]["IsPhysicalInventory"].ToString()).ToString() == "1")
{
sql = @"select '0' as type, '" + CategoryName + " Pass Summary by Date' as Title, '" + sFrom + "' as from_date, '" + sTo + @"' as to_date, S.SalesDate as purchase_date, P.Name as product_name, 1 as product_count, '' as voucher_no, PD.SerialNo as serial_no, D.SalesAmount as purchase_price
from SalesMaster S, SalesDetails D, ProductMaster P, ProductDetail PD
where S.SalesMasterId = D.SalesMasterId and D.ProductId = P.ProductId and P.ProductId = PD.ProductId and PD.SalesDetailId = D.SalesDetailId and P.CategoryId = " + CategoryId;
if (sFrom != "")
{
sql += " and S.SalesDate >= '" + Convert.ToDateTime(sFrom).ToString("yyyy-MM-dd") + " 00:00:00' ";
}
if (sTo != "")
{
sql += " and S.SalesDate <= '" + Convert.ToDateTime(sTo).ToString("yyyy-MM-dd") + @" 23:59:59'";
}
sql += @" union select '1' as type, '" + CategoryName + " Pass Summary by Date' as Title, '" + sFrom + "' as from_date, '" + sTo + @"' as to_date, S.SalesReturnDate as purchase_date, P.Name as product_name, 1 as product_count, '' as voucher_no, PD.SerialNo as serial_no, D.Amount as purchase_price
from SalesReturnMaster S, SalesReturnDetails D, ProductMaster P, ProductDetail PD
where S.SalesReturnMasterId = D.SalesReturnMasterId and D.ProductId = P.ProductId and P.ProductId = PD.ProductId and PD.SalesReturnDetailId = D.SalesReturnDetailId and P.CategoryId = " + CategoryId;
if (sFrom != "")
{
sql += " and S.SalesReturnDate >= '" + Convert.ToDateTime(sFrom).ToString("yyyy-MM-dd") + " 00:00:00' ";
}
if (sTo != "")
{
sql += " and S.SalesReturnDate <= '" + Convert.ToDateTime(sTo).ToString("yyyy-MM-dd") + @" 23:59:59'";
}
sql += " order by type, purchase_date, product_name, serial_no";
}
else
{
sql = @"select '0' as type, '" + CategoryName + " Pass Summary by Date' as Title, '" + sFrom + "' as from_date, '" + sTo + @"' as to_date, S.SalesDate as purchase_date,
concat(IFNULL((SELECT Name FROM ProductMaster WHERE ProductMaster.ProductId = D.ProductId),''), IFNULL(D.ProductName,'')) as product_name,
1 as product_count, '' as voucher_no,
case when IsSmartDest = 0 then (select SerialNo from ProductDetail where ProductId = D.ProductId and SalesDetailId = D.SalesDetailId) else '' as serial_no, D.SalesAmount as purchase_price
from SalesMaster S, SalesDetails D, ProductDetail PD
where S.SalesMasterId = D.SalesMasterId and D.ProductId = PD.ProductId and PD.SalesDetailId = D.SalesDetailId and P.CategoryId = " + CategoryId;
if (sFrom != "")
{
sql += " and S.SalesDate >= '" + Convert.ToDateTime(sFrom).ToString("yyyy-MM-dd") + " 00:00:00' ";
}
if (sTo != "")
{
sql += " and S.SalesDate <= '" + Convert.ToDateTime(sTo).ToString("yyyy-MM-dd") + @" 23:59:59'";
}
sql += @" union select '1' as type, '" + CategoryName + " Pass Summary by Date' as Title, '" + sFrom + "' as from_date, '" + sTo + @"' as to_date, S.SalesReturnDate as purchase_date, concat(IFNULL((SELECT Name FROM ProductMaster WHERE ProductMaster.ProductId = D.ProductId),''), IFNULL(D.ProductName,'')) as product_name, 1 as product_count, '' as voucher_no, PD.SerialNo as serial_no, D.Amount as purchase_price
from SalesReturnMaster S, SalesReturnDetails D, ProductDetail PD
where S.SalesReturnMasterId = D.SalesReturnMasterId and D.ProductId = P.ProductId and P.ProductId = PD.ProductId and PD.SalesReturnDetailId = D.SalesReturnDetailId and P.CategoryId = " + CategoryId;
if (sFrom != "")
{
sql += " and S.SalesReturnDate >= '" + Convert.ToDateTime(sFrom).ToString("yyyy-MM-dd") + " 00:00:00' ";
}
if (sTo != "")
{
sql += " and S.SalesReturnDate <= '" + Convert.ToDateTime(sTo).ToString("yyyy-MM-dd") + @" 23:59:59'";
}
sql += " order by purchase_date, product_name, serial_no";
}
_datasource = DBConn.ExecuteDataSet(sql, CommandType.Text).Tables[0];
_reportname = "CategorySummary.rpt";
break;
case "ClerkSummary":
string LocationId = "" + Request.QueryString["location_id"];
sFrom = "" + Request.QueryString["fromd"];
//sTo = "" + Request.QueryString["tod"];
sql = @"SELECT '0' as type, '" + sFrom + @"' as from_date, '' as to_date, L.Name as location_name, U.UserName as user_name,
Sum(case when PaymentType = 1 then 1 else 0 end) as cash_count, Sum(case when PaymentType = 1 then P.Amount else 0 end) as cash_total,
Sum(case when PaymentType = 2 then 1 else 0 end) as credit_count, Sum(case when PaymentType = 2 then P.Amount else 0 end) as credit_total,
Sum(case when PaymentType = 3 then 1 else 0 end) as voucher_count, Sum(case when PaymentType = 3 then P.Amount else 0 end) as voucher_total
FROM SalesMaster S, PaymentDetails P, LocationMaster L, UserMaster U
WHERE S.SalesMasterId = P.SalesMasterId and P.TransType = 0 and S.LocationId = L.LocationId and S.ClerkId = U.UserId and S.LocationId = " + LocationId;
if (sFrom != "")
{
sql += " and S.SalesDate between '" + Convert.ToDateTime(sFrom).ToString("yyyy-MM-dd") + " 00:00:00' and '" + Convert.ToDateTime(sFrom).ToString("yyyy-MM-dd") + " 23:59:59'";
}
sql += " group by L.Name, U.UserName ";
sql += " union ";
sql += @"SELECT '1' as type, '" + sFrom + @"' as from_date, '' as to_date, L.Name as location_name, U.UserName as user_name,
Sum(case when PaymentType = 1 then 1 else 0 end) as cash_count, Sum(case when PaymentType = 1 then P.Amount else 0 end) as cash_total,
Sum(case when PaymentType = 2 then 1 else 0 end) as credit_count, Sum(case when PaymentType = 2 then P.Amount else 0 end) as credit_total,
Sum(case when PaymentType = 3 then 1 else 0 end) as voucher_count, Sum(case when PaymentType = 3 then P.Amount else 0 end) as voucher_total
FROM SalesReturnMaster S, PaymentDetails P, LocationMaster L, UserMaster U
WHERE S.SalesReturnMasterId = P.SalesMasterId and P.TransType = 1 and S.LocationId = L.LocationId and S.ClerkId = U.UserId and S.LocationId = " + LocationId;
if (sFrom != "")
{
sql += " and S.SalesReturnDate between '" + Convert.ToDateTime(sFrom).ToString("yyyy-MM-dd") + " 00:00:00' and '" + Convert.ToDateTime(sFrom).ToString("yyyy-MM-dd") + " 23:59:59'";
}
sql += " group by L.Name, U.UserName ";
_datasource = DBConn.ExecuteDataSet(sql, CommandType.Text).Tables[0];
_reportname = "ClerkSummary.rpt";
break;
case "VoucherSummary":
CategoryId = "" + Request.QueryString["category_id"];
CategoryName = "" + Request.QueryString["category_name"];
sFrom = "" + Request.QueryString["fromd"];
sTo = "" + Request.QueryString["tod"];
if (sTo == "")
{
sTo = sFrom;
}
sql = @"select '" + CategoryName + "' as Title, '" + sFrom + "' FromDate, '" + sTo + @"' ToDate, SM.SalesMasterId, SM.SalesDate as `Date`, PD.CardCheckNo as VoucherNumber, PM.Name, SD.SerialNo as SerialNumber from
SalesMaster SM, PaymentDetails PD, ProductMaster PM, SalesDetails SD where
SM.SalesMasterId = PD.SalesMasterId AND SD.SalesMasterId = SM.SalesMasterId AND
SD.ProductId = PM.ProductId AND PD.PaymentType = 3 AND PM.CategoryId = " + CategoryId;
if (sFrom != "")
{
sql += " and SM.SalesDate between '" + Convert.ToDateTime(sFrom).ToString("yyyy-MM-dd") + " 00:00:00' and '" + Convert.ToDateTime(sTo).ToString("yyyy-MM-dd") + @" 23:59:59'";
}
_datasource = DBConn.ExecuteDataSet(sql, CommandType.Text).Tables[0];
_reportname = "VoucherSummary.rpt";
break;
case "SDReturns":
sFrom = "" + Request.QueryString["fromd"];
sTo = "" + Request.QueryString["tod"];
sql = @"SELECT 'Smart Destination Returns' as title, '" + sFrom + @"' as from_date, '" + sTo + @"' as to_date, SD.SalesMasterId as sales_id, date_format(SR.SalesReturnDate,'%m/%d/%Y') as return_date,
SD.ProductName as product_name, SRD.qty, SRD.amount, UM.UserName as user_name, concat(' ',SDOrderNo) as SDOrderNo
FROM SalesReturnMaster SR, SalesReturnDetails SRD, SalesMaster SM, SalesDetails SD, UserMaster UM
WHERE SR.SalesReturnMasterId = SRD.SalesReturnMasterId and SRD.SalesDetailId = SD.SalesDetailId and SM.SalesMasterId = SD.SalesMasterId and SD.IsSmartDest = 1 and SRD.ProductId = SD.ProductId and SR.ClerkId = UM.UserId and SR.LocationId = " + Session["LocationId"].ToString();
if (sFrom != "")
{
sql += " and SR.SalesReturnDate >= '" + Convert.ToDateTime(sFrom).ToString("yyyy-MM-dd") + " 00:00:00' ";
}
if (sTo != "")
{
sql += " and SR.SalesReturnDate <= '" + Convert.ToDateTime(sTo).ToString("yyyy-MM-dd") + @" 23:59:59'";
}
sql += "";
sql += " order by SR.SalesReturnDate";
_datasource = DBConn.ExecuteDataSet(sql, CommandType.Text).Tables[0];
_reportname = "SDReturns.rpt";
break;
case "UserLog":
sFrom = "" + Request.QueryString["fromd"];
sTo = "" + Request.QueryString["tod"];
sql = @"SELECT username as user_name, LoginDateTime AS login_date, LogoutDatetime AS logout_date, IP
FROM UserLog, UserMaster
WHERE UserLog.UserId = UserMaster.UserId ";
if (sFrom != "")
{
sql += " and LoginDateTime >= '" + Convert.ToDateTime(sFrom).ToString("yyyy-MM-dd") + " 00:00:00' ";
}
if (sTo != "")
{
sql += " and LoginDateTime <= '" + Convert.ToDateTime(sTo).ToString("yyyy-MM-dd") + @" 23:59:59'";
}
sql += " ORDER BY UserName, LoginDateTime";
_datasource = DBConn.ExecuteDataSet(sql, CommandType.Text).Tables[0];
_reportname = "UserLog.rpt";
break;
case "SalesReturn":
sFrom = "" + Request.QueryString["fromd"];
sTo = "" + Request.QueryString["tod"];
sql = " SELECT SalesReturnMasterId SRID, (SELECT SalesMasterId from SalesDetails, SalesReturnDetails WHERE SalesDetails.SalesDetailId = SalesReturnDetails.SalesDetailId and SalesReturnDetails.SalesReturnMasterId = SalesReturnMaster.SalesReturnMasterId limit 1) as ID, (select DATE_FORMAT(SalesDate,'%m/%d/%Y %h:%i %p') from SalesDetails, SalesReturnDetails, SalesMaster WHERE SalesMaster.`SalesMasterId` = SalesDetails.`SalesMasterId` AND SalesDetails.SalesDetailId = SalesReturnDetails.SalesDetailId and SalesReturnDetails.SalesReturnMasterId = SalesReturnMaster.SalesReturnMasterId limit 1) SalesDate, DATE_FORMAT(SalesReturnDate,'%m/%d/%Y %h:%i %p') SalesReturnDate, Amount, (SELECT Name FROM LocationMaster WHERE LocationMaster.LocationId = SalesReturnMaster.LocationId) as Location, (SELECT UserName FROM UserMaster WHERE UserMaster.UserId = SalesReturnMaster.ClerkId) as Clerk, '' as CustomerName, (SELECT SUM(Qty) FROM SalesReturnDetails WHERE SalesReturnMasterId = SalesReturnMaster.SalesReturnMasterId) ticket, ";
if (sFrom.Trim() != "" && sTo.Trim() != "")
{
sql += "'" + sFrom + "' sFrom,'" + sTo + "' sTo FROM SalesReturnMaster where 1=1";
dt1 = DateTime.Parse(sFrom);
sFrom = dt1.ToString("yyyy-MM-dd");
dt1 = DateTime.Parse(sTo);
sTo = dt1.ToString("yyyy-MM-dd");
sql += " and (SalesReturnDate between '" + sFrom + " 00:00:01' and '" + sTo + " 23:59:59')";
}
else if (sFrom.Trim() != "")
{
sql += "'" + sFrom + "' sFrom,'' sTo FROM SalesReturnMaster where 1=1";
dt1 = DateTime.Parse(sFrom);
sFrom = dt1.ToString("yyyy-MM-dd");
sql += " and SalesReturnDate >= '" + sFrom + " 00:00:01'";
}
else if (sTo.Trim() != "")
{
sql += "'' sFrom,'" + sTo + "' sTo FROM SalesReturnMaster where 1=1";
dt1 = DateTime.Parse(sTo);
sTo = dt1.ToString("yyyy-MM-dd");
sql += " and SalesReturnDate <= '" + sTo + " 23:59:59'";
}
else
{
sql += "'' sFrom,'' sTo FROM SalesReturnMaster where 1=1";
}
sql += " order by SalesReturnDate";
_datasource = DBConn.ExecuteDataSet(sql, CommandType.Text).Tables[0];
_reportname = "SalesReturn.rpt";
break;
case "SalesReturnDetails":
sFrom = "" + Request.QueryString["fromd"];
sTo = "" + Request.QueryString["tod"];
sql = @" SELECT '1' as type, SalesReturnMaster.SalesReturnMasterId SalesMasterId, (select SalesMasterId from SalesDetails where SalesDetailId = SalesReturnDetails.SalesDetailId limit 1) as SalesId, DATE_FORMAT(SalesReturnDate,'%m/%d/%Y %h:%i %p') SalesReturnDate, SalesReturnMaster.Amount AS Amount,
(select DATE_FORMAT(SalesDate,'%m/%d/%Y %h:%i %p') from SalesDetails, SalesReturnDetails, SalesMaster WHERE SalesMaster.`SalesMasterId` = SalesDetails.`SalesMasterId` AND SalesDetails.SalesDetailId = SalesReturnDetails.SalesDetailId and SalesReturnDetails.SalesReturnMasterId = SalesReturnMaster.SalesReturnMasterId limit 1) SalesDate, SalesReturnDetails.SerialNo,
(SELECT NAME FROM LocationMaster WHERE LocationMaster.LocationId = SalesReturnMaster.LocationId) AS Location,
(SELECT UserName FROM UserMaster WHERE UserMaster.UserId = SalesReturnMaster.ClerkId) AS Clerk, '' as CustomerName,
(SELECT NAME FROM ProductMaster WHERE ProductMaster.ProductId = SalesReturnDetails.ProductId) AS Description,
Qty AS Quantity, SalesReturnDetails.Amount AS Price,
(SELECT CASE WHEN PaymentType=1 THEN 'Cash' WHEN PaymentType=2 THEN 'Card' Else 'Vouher' END FROM PaymentDetails WHERE SalesMasterId = SalesReturnMaster.SalesReturnMasterId) paytype,
'' as authcode,";
if (sFrom.Trim() != "" && sTo.Trim() != "")
{
sql += "'" + sFrom + "' sFrom,'" + sTo + "' sTo FROM SalesReturnMaster, SalesReturnDetails WHERE SalesReturnMaster.SalesReturnMasterId = SalesReturnDetails.SalesReturnMasterId ";
dt1 = DateTime.Parse(sFrom);
sFrom = dt1.ToString("yyyy-MM-dd");
dt1 = DateTime.Parse(sTo);
sTo = dt1.ToString("yyyy-MM-dd");
sql += " and (SalesReturnMaster.SalesReturnDate between '" + sFrom + " 00:00:01' and '" + sTo + " 23:59:59')";
}
else if (sFrom.Trim() != "")
{
sql += "'" + sFrom + "' sFrom,'' sTo FROM SalesReturnMaster,SalesReturnDetails WHERE SalesReturnMaster.SalesReturnMasterId = SalesReturnDetails.SalesReturnMasterId";
dt1 = DateTime.Parse(sFrom);
sFrom = dt1.ToString("yyyy-MM-dd");
sql += " and SalesReturnMaster.SalesReturnDate >= '" + sFrom + " 00:00:01'";
}
else if (sTo.Trim() != "")
{
sql += "'' sFrom,'" + sTo + "' sTo FROM SalesReturnMaster,SalesReturnDetails WHERE SalesReturnMaster.SalesReturnMasterId = SalesReturnDetails.SalesReturnMasterId";
dt1 = DateTime.Parse(sTo);
sTo = dt1.ToString("yyyy-MM-dd");
sql += " and SalesReturnMaster.SalesReturnDate <= '" + sTo + " 23:59:59'";
}
else
{
sql += "'' sFrom,'' sTo FROM SalesReturnMaster,SalesReturnDetails WHERE SalesReturnMaster.SalesReturnMasterId = SalesReturnDetails.SalesReturnMasterId";
}
sql += " order by SalesDate";
_datasource = DBConn.ExecuteDataSet(sql, CommandType.Text).Tables[0];
_reportname = "SalesReturnDetails.rpt";
break;
case "Users":
Users objUsers = new Users();
_datasource = objUsers.GetUsers();
_reportname = "Users.rpt";
break;
}
doc.Load(Server.MapPath("") + @"\" + _reportname);
if (_datasource != null)
{
doc.SetDataSource(_datasource);
if (_reportname == "Receipt.rpt")
{
string sql = "SELECT SalesDate, CustomerName,'' as `SalesTime`,CASE PaymentType WHEN 1 THEN 'Cash' WHEN 2 THEN CONCAT(CardType, ' ....',CardCheckNo) ELSE concat('Voucher No. ',CardCheckNo) END as PayType,case when CardAuthorizationId != 0 then CONCAT('Authorization:',CardAuthorizationId) else '' end as Authorization,Amount as PayAmount FROM SalesMaster,PaymentDetails where SalesMaster.SalesMasterId = PaymentDetails.SalesMasterId and SalesMaster.SalesMasterId= " + SalesId;
_datasource = DBConn.ExecuteDataSet(sql, CommandType.Text).Tables[0];
doc.Subreports[0].SetDataSource(_datasource);
sql = @"SELECT concat(IFNULL((SELECT Name FROM ProductMaster WHERE ProductMaster.ProductId = SD.ProductId),''), IFNULL(SD.ProductName,'')) as Description, SUM(D.Qty) as Quantity, D.Amount as Price
FROM SalesReturnDetails D, SalesDetails SD
where D.SalesDetailId = SD.SalesDetailId and SD.SalesMasterId = " + SalesId + " GROUP by D.SalesDetailId";
_datasource = DBConn.ExecuteDataSet(sql, CommandType.Text).Tables[0];
doc.Subreports[1].SetDataSource(_datasource);
if (Request.QueryString["Dup"] == "yes")
{
doc.Subreports[0].ReportDefinition.Sections["ReportFooterSection1"].SectionFormat.EnableSuppress = true;
}
}
}
doc.SummaryInfo.ReportTitle = _reporttitle;
doc.RecordSelectionFormula = _reportselectionformula;
if (_reportname == "CategorySummary.rpt" || _reportname == "VoucherSummary.rpt")
{
((TextObject)doc.ReportDefinition.Sections["Section1"].ReportObjects["title"]).Text = "" + Request.QueryString["category_name"] + " Voucher Summary by Date";
}
CrViewer.HyperlinkTarget = "_blank";
//repObj.LoadParameterFields(doc, Request.QueryString["DataSource"]);
MaximizeWindow();
if (_printType == "DirectPrint")// Report.ReportOutPutEnum.DirectPrint.ToString())
{
CrViewer.SeparatePages = false;
//CrViewer.PrintMode = CrystalDecisions.Web.PrintMode.ActiveX;
}
/*else if (_printType == Report.ReportOutPutEnum.ExportToPDF.ToString())
* {
* MemoryStream oStream; // using System.IO
* oStream = (MemoryStream)
* doc.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
* Response.Clear();
* Response.Buffer = true;
* if ("" + Request.QueryString["DataSource"] == "SIBREPORT")
* {
* string sFileName = "";
* if ("" + ConfigurationManager.AppSettings["ExchId"] == "LULU")
* sFileName = "LNF";
* else
* sFileName = "ANF";
* sFileName = sFileName + DateTime.Parse(repObj.DateParameter1).ToString("ddMMyyyy") + repObj.StringParameter2; //objFgen.GetSIBFileName(tFun.Text);
* sFileName = sFileName.Replace("/", "");
* string fileName = Server.MapPath("files") + "\\" + sFileName;
* Response.AddHeader("content-disposition", "attachment; filename=" + sFileName);
* }
* Response.ContentType = "application/pdf";
* Response.BinaryWrite(oStream.ToArray());
* Response.End();
* }
* else if (_printType == Report.ReportOutPutEnum.ExportToExcel.ToString())
* {
* MemoryStream oStream; // using System.IO
* oStream = (MemoryStream)
* doc.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);
* Response.Clear();
* Response.Buffer = true;
* Response.ContentType = "application/vnd.ms-excel";
* Response.BinaryWrite(oStream.ToArray());
* Response.End();
* }
* else if (_printType == Report.ReportOutPutEnum.ExportToDoc.ToString())
* {
* MemoryStream oStream; // using System.IO
* oStream = (MemoryStream)
* doc.ExportToStream(CrystalDecisions.Shared.ExportFormatType.WordForWindows);
* Response.Clear();
* Response.Buffer = true;
* Response.ContentType = "application/vnd.ms-word";
* Response.BinaryWrite(oStream.ToArray());
* Response.End();
* }*/
CrViewer.ReportSource = doc;
CrViewer.DataBind();
}
}
catch (Exception E)
{
Response.Write(E.ToString());
}
}
