private void LoadList()
{
POReturns clsPOReturns = new POReturns();
DataClass clsDataClass = new DataClass();
Common Common = new Common();
string SortField = "DebitMemoID";
if (Request.QueryString["sortfield"] != null)
{
SortField = Common.Decrypt(Request.QueryString["sortfield"].ToString(), Session.SessionID);
}
SortOption sortoption = SortOption.Ascending;
if (Request.QueryString["sortoption"] != null)
{
sortoption = (SortOption)Enum.Parse(typeof(SortOption), Common.Decrypt(Request.QueryString["sortoption"], Session.SessionID), true);
}
DateTime dteOrderStartDate = DateTime.MinValue;
try { if (txtOrderStartDate.Text != string.Empty)
{
dteOrderStartDate = Convert.ToDateTime(txtOrderStartDate.Text + " " + txtOrderStartTime.Text);
}
}
catch { }
DateTime dteOrderEndDate = DateTime.MinValue;
try { if (txtOrderEndDate.Text != string.Empty)
{
dteOrderEndDate = Convert.ToDateTime(txtOrderEndDate.Text + " " + txtOrderEndTime.Text);
}
}
catch { }
DateTime dtePostingStartDate = DateTime.MinValue;
try { if (txtPostingStartDate.Text != string.Empty)
{
dtePostingStartDate = Convert.ToDateTime(txtPostingStartDate.Text + " " + txtPostingStartTime.Text);
}
}
catch { }
DateTime dtePostingEndDate = DateTime.MinValue;
try { if (txtPostingEndDate.Text != string.Empty)
{
dtePostingEndDate = Convert.ToDateTime(txtPostingEndDate.Text + " " + txtPostingEndTime.Text);
}
}
catch { }
eSalesFilter clseSalesFilter = new eSalesFilter();
clseSalesFilter.FilterIncludeIneSales = true;
clseSalesFilter.IncludeIneSales = true;
string SearchKey = txtSearch.Text;
POReturnStatus status = (POReturnStatus)Enum.Parse(typeof(POReturnStatus), cboStatus.SelectedItem.Value);
PageData.DataSource = clsPOReturns.SearchAsDataTable(status, dteOrderStartDate, dteOrderEndDate, dtePostingStartDate, dtePostingEndDate, SearchKey, SortField, sortoption, 0, clseSalesFilter).DefaultView;
clsPOReturns.CommitAndDispose();
int iPageSize = Convert.ToInt16(Session["PageSize"]);
PageData.AllowPaging = true;
PageData.PageSize = iPageSize;
try
{
PageData.CurrentPageIndex = Convert.ToInt16(cboCurrentPage.SelectedItem.Value) - 1;
lstItem.DataSource = PageData;
lstItem.DataBind();
}
catch
{
PageData.CurrentPageIndex = 1;
lstItem.DataSource = PageData;
lstItem.DataBind();
}
cboCurrentPage.Items.Clear();
for (int i = 0; i < PageData.PageCount; i++)
{
int iValue = i + 1;
cboCurrentPage.Items.Add(new ListItem(iValue.ToString(), iValue.ToString()));
if (PageData.CurrentPageIndex == i)
{
cboCurrentPage.Items[i].Selected = true;
}
else
{
cboCurrentPage.Items[i].Selected = false;
}
}
lblDataCount.Text = " of " + " " + PageData.PageCount;
}
private void LoadList()
{
PO clsPO = new PO();
DataClass clsDataClass = new DataClass();
Common Common = new Common();
string SortField = "POID";
if (Request.QueryString["sortfield"]!=null)
{ SortField = Common.Decrypt(Request.QueryString["sortfield"].ToString(), Session.SessionID); }
SortOption sortoption = SortOption.Desscending;
if (Request.QueryString["sortoption"]!=null)
{ sortoption = (SortOption) Enum.Parse(typeof(SortOption), Common.Decrypt(Request.QueryString["sortoption"], Session.SessionID), true); }
DateTime dteOrderStartDate = DateTime.MinValue;
try { if (txtOrderStartDate.Text != string.Empty) dteOrderStartDate = Convert.ToDateTime(txtOrderStartDate.Text + " " + txtOrderStartTime.Text); }
catch { }
DateTime dteOrderEndDate = DateTime.MinValue;
try { if (txtOrderEndDate.Text != string.Empty) dteOrderEndDate = Convert.ToDateTime(txtOrderEndDate.Text + " " + txtOrderEndTime.Text); }
catch { }
DateTime dtePostingStartDate = DateTime.MinValue;
try { if (txtPostingStartDate.Text != string.Empty) dtePostingStartDate = Convert.ToDateTime(txtPostingStartDate.Text + " " + txtPostingStartTime.Text); }
catch { }
DateTime dtePostingEndDate = DateTime.MinValue;
try { if (txtPostingEndDate.Text != string.Empty) dtePostingEndDate = Convert.ToDateTime(txtPostingEndDate.Text + " " + txtPostingEndTime.Text); }
catch { }
eSalesFilter clseSalesFilter = new eSalesFilter();
clseSalesFilter.FilterIncludeIneSales = true;
clseSalesFilter.IncludeIneSales = true;
string SearchKey = txtSearch.Text;
POStatus status = (POStatus)Enum.Parse(typeof(POStatus), cboStatus.SelectedItem.Value);
PageData.DataSource = clsPO.SearchAsDataTable(status, dteOrderStartDate, dteOrderEndDate, dtePostingStartDate, dtePostingEndDate, SearchKey, SortField, sortoption, 0, clseSalesFilter).DefaultView;
clsPO.CommitAndDispose();
int iPageSize = Convert.ToInt16(Session["PageSize"]) ;
PageData.AllowPaging = true;
PageData.PageSize = iPageSize;
try
{
PageData.CurrentPageIndex = Convert.ToInt16(cboCurrentPage.SelectedItem.Value) - 1;
lstItem.DataSource = PageData;
lstItem.DataBind();
}
catch
{
PageData.CurrentPageIndex = 1;
lstItem.DataSource = PageData;
lstItem.DataBind();
}
cboCurrentPage.Items.Clear();
for (int i=0; i < PageData.PageCount;i++)
{
int iValue = i + 1;
cboCurrentPage.Items.Add(new ListItem(iValue.ToString(),iValue.ToString()));
if (PageData.CurrentPageIndex == i)
{ cboCurrentPage.Items[i].Selected = true;}
else
{ cboCurrentPage.Items[i].Selected = false;}
}
lblDataCount.Text = " of " + " " + PageData.PageCount;
}
public System.Data.DataTable SearchAsDataTable(DebitMemoStatus status, DateTime OrderStartDate, DateTime OrderEndDate, DateTime PostingStartDate, DateTime PostingEndDate, string SearchKey, string SortField, SortOption SortOrder, Int32 limit = 0, eSalesFilter clseSalesFilter = new eSalesFilter())
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string SQL = SQLSelect() + "AND DebitMemoStatus = @Status " +
"AND (MemoNo LIKE @SearchKey or MemoDate LIKE @SearchKey or SupplierCode LIKE @SearchKey " +
"or SupplierContact LIKE @SearchKey or BranchCode LIKE @SearchKey or RequiredPostingDate LIKE @SearchKey) ";
if (OrderStartDate != DateTime.MinValue) SQL += "AND MemoDate >= @OrderStartDate ";
if (OrderEndDate != DateTime.MinValue) SQL += "AND MemoDate <= @OrderEndDate ";
if (PostingStartDate != DateTime.MinValue) SQL += "AND PostingDate >= @PostingStartDate ";
if (PostingEndDate != DateTime.MinValue) SQL += "AND PostingDate <= @PostingEndDate ";
if (clseSalesFilter.FilterIncludeIneSales)
{
SQL += "AND a.IncludeIneSales = @IncludeIneSales ";
cmd.Parameters.AddWithValue("@IncludeIneSales", clseSalesFilter.IncludeIneSales);
}
SQL += "ORDER BY " + (!string.IsNullOrEmpty(SortField) ? SortField : "DebitMemoID") + " ";
SQL += SortOrder == SortOption.Ascending ? "ASC " : "DESC ";
SQL += limit == 0 ? "" : "LIMIT " + limit.ToString() + " ";
cmd.Parameters.AddWithValue("@Status", status.ToString("d"));
cmd.Parameters.AddWithValue("@SearchKey", "%" + SearchKey + "%");
if (OrderStartDate != DateTime.MinValue) cmd.Parameters.AddWithValue("@OrderStartDate", OrderStartDate.ToString("yyyy-MM-dd HH:mm:ss"));
if (OrderEndDate != DateTime.MinValue) cmd.Parameters.AddWithValue("@OrderEndDate", OrderEndDate.ToString("yyyy-MM-dd HH:mm:ss"));
if (PostingStartDate != DateTime.MinValue) cmd.Parameters.AddWithValue("@PostingStartDate", PostingStartDate.ToString("yyyy-MM-dd HH:mm:ss"));
if (PostingEndDate != DateTime.MinValue) cmd.Parameters.AddWithValue("@PostingEndDate", PostingEndDate.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.CommandText = SQL;
string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
base.MySqlDataAdapterFill(cmd, dt);
return dt;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
public System.Data.DataTable ListAsDataTable(DebitMemoStatus DebitMemoStatus = DebitMemoStatus.All, DebitMemoDetails searchKey = new DebitMemoDetails(), DateTime? MemoStartDate = null, DateTime? MemoEndDate = null, DateTime? PostingStartDate = null, DateTime? PostingEndDate = null, string SortField = "DebitMemoID", SortOption SortOrder = SortOption.Ascending, Int32 limit = 0, Int64 SupplierID = 0, Int64 DebitMemoID = 0, eSalesFilter clseSalesFilter = new eSalesFilter())
{
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
// Note WHERE is already included in SQLSelect()
string SQL = SQLSelect();
if (DebitMemoStatus != DebitMemoStatus.All)
{
SQL += "AND DebitMemoStatus = @DebitMemoStatus ";
cmd.Parameters.AddWithValue("@DebitMemoStatus", DebitMemoStatus.ToString("d"));
}
if (DebitMemoID != 0)
{
SQL += "AND DebitMemoID = @DebitMemoID ";
cmd.Parameters.AddWithValue("@DebitMemoID", DebitMemoID);
}
if (SupplierID != 0)
{
SQL += "AND SupplierID >= @SupplierID ";
cmd.Parameters.AddWithValue("@SupplierID", SupplierID);
}
if ((MemoStartDate.GetValueOrDefault() == DateTime.MinValue ? Constants.C_DATE_MIN_VALUE : MemoStartDate) != Constants.C_DATE_MIN_VALUE)
{
SQL += "AND MemoDate >= @MemoStartDate ";
cmd.Parameters.AddWithValue("@MemoStartDate", MemoStartDate.Value.ToString("yyyy-MM-dd HH:mm:ss"));
}
if ((MemoEndDate.GetValueOrDefault() == DateTime.MinValue ? Constants.C_DATE_MIN_VALUE : MemoEndDate) != Constants.C_DATE_MIN_VALUE)
{
SQL += "AND MemoDate <= @MemoEndDate ";
cmd.Parameters.AddWithValue("@MemoEndDate", MemoEndDate.Value.ToString("yyyy-MM-dd HH:mm:ss"));
}
if ((PostingStartDate.GetValueOrDefault() == DateTime.MinValue ? Constants.C_DATE_MIN_VALUE : PostingStartDate) != Constants.C_DATE_MIN_VALUE)
{
SQL += "AND PostingDate >= @PostingStartDate ";
cmd.Parameters.AddWithValue("@PostingStartDate", PostingStartDate.Value.ToString("yyyy-MM-dd HH:mm:ss"));
}
if ((PostingEndDate.GetValueOrDefault() == DateTime.MinValue ? Constants.C_DATE_MIN_VALUE : PostingEndDate) != Constants.C_DATE_MIN_VALUE)
{
SQL += "AND PostingDate <= @PostingEndDate ";
cmd.Parameters.AddWithValue("@PostingEndDate", PostingEndDate.Value.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (clseSalesFilter.FilterIncludeIneSales)
{
SQL += "AND a.IncludeIneSales = @IncludeIneSales ";
cmd.Parameters.AddWithValue("@IncludeIneSales", clseSalesFilter.IncludeIneSales);
}
SQL += "ORDER BY " + (!string.IsNullOrEmpty(SortField) ? SortField : "DebitMemoID") + " ";
SQL += SortOrder == SortOption.Ascending ? "ASC " : "DESC ";
SQL += limit == 0 ? "" : "LIMIT " + limit.ToString() + " ";
cmd.CommandText = SQL;
string strDataTableName = "tbl" + this.GetType().FullName.Split(new Char[] { '.' })[this.GetType().FullName.Split(new Char[] { '.' }).Length - 1]; System.Data.DataTable dt = new System.Data.DataTable(strDataTableName);
base.MySqlDataAdapterFill(cmd, dt);
return dt;
}
catch (Exception ex)
{
throw base.ThrowException(ex);
}
}
private void SetDataSource(ReportDocument Report)
{
ReportDataset rptds = new ReportDataset();
DateTime PostingDateFrom = DateTime.MinValue;
try
{ PostingDateFrom = Convert.ToDateTime(txtStartDate.Text + " " + txtStartTime.Text); }
catch {}
DateTime PostingDateTo = DateTime.MinValue;
try
{ PostingDateTo = Convert.ToDateTime(txtEndDate.Text + " " + txtEndTime.Text); }
catch {}
eSalesFilter clseSalesFilter = new eSalesFilter();
if (lblTask.Text == "esales")
{
clseSalesFilter.FilterIncludeIneSales = true;
clseSalesFilter.IncludeIneSales = true;
}
DataClass clsDataClass = new DataClass();
System.Data.DataTable dt;
string ReportType = cboReportType.SelectedItem.Text;
long lngSupplierID = long.Parse(cboSupplier.SelectedValue);
switch (ReportType)
{
case "Posted PO":
Data.PO clsPO = new Data.PO();
dt = clsPO.ListAsDataTable(POStatus.Posted, new Data.PODetails(), Constants.C_DATE_MIN_VALUE, Constants.C_DATE_MIN_VALUE, PostingDateFrom, PostingDateTo, SupplierID: lngSupplierID, clseSalesFilter: clseSalesFilter);
clsPO.CommitAndDispose();
foreach (System.Data.DataRow dr in dt.Rows)
{
DataRow drPO = rptds.PO.NewRow();
foreach (DataColumn dc in rptds.PO.Columns)
{
drPO[dc] = dr[dc.ColumnName];
}
rptds.PO.Rows.Add(drPO);
}
break;
case "Posted PO Returns":
Data.POReturns clsPOReturns = new Data.POReturns();
dt = clsPOReturns.ListAsDataTable(POReturnStatus.Posted, PostingStartDate: PostingDateFrom, PostingEndDate: PostingDateTo, SupplierID: lngSupplierID, clseSalesFilter: clseSalesFilter);
clsPOReturns.CommitAndDispose();
foreach (System.Data.DataRow dr in dt.Rows)
{
DataRow drPOReturns = rptds.POReturns.NewRow();
foreach (DataColumn dc in rptds.POReturns.Columns)
{
drPOReturns[dc] = dr[dc.ColumnName];
}
rptds.POReturns.Rows.Add(drPOReturns);
}
break;
case "Posted Debit Memo":
Data.DebitMemos clsDebitMemos = new Data.DebitMemos();
dt = clsDebitMemos.ListAsDataTable(DebitMemoStatus.Posted, PostingStartDate: PostingDateFrom, PostingEndDate: PostingDateTo, SupplierID: lngSupplierID, clseSalesFilter: clseSalesFilter);
clsDebitMemos.CommitAndDispose();
foreach (System.Data.DataRow dr in dt.Rows)
{
DataRow drDebitMemos = rptds.DebitMemo.NewRow();
foreach (DataColumn dc in rptds.DebitMemo.Columns)
{
drDebitMemos[dc] = dr[dc.ColumnName];
}
rptds.DebitMemo.Rows.Add(drDebitMemos);
}
break;
case "By Vendor":
Data.PurchaseAnalysis clsPurchaseAnalysis = new Data.PurchaseAnalysis();
dt = clsPurchaseAnalysis.ByVendor(PostingDateFrom, PostingDateTo);
clsPurchaseAnalysis.CommitAndDispose();
foreach (System.Data.DataRow dr in dt.Rows)
{
DataRow drPAByVendor = rptds.PurchaseAnalysisPerVendor.NewRow();
foreach (DataColumn dc in rptds.PurchaseAnalysisPerVendor.Columns)
{
drPAByVendor[dc] = dr[dc.ColumnName];
}
rptds.PurchaseAnalysisPerVendor.Rows.Add(drPAByVendor);
}
break;
default:
break;
}
Report.SetDataSource(rptds);
SetParameters(Report);
}
private void SetDataSource(ReportDocument Report)
{
ReportDataset rptds = new ReportDataset();
DateTime PostingDateFrom = DateTime.MinValue;
try
{ PostingDateFrom = Convert.ToDateTime(txtStartDate.Text + " " + txtStartTime.Text); }
catch{}
DateTime PostingDateTo = DateTime.MinValue;
try
{ PostingDateTo = Convert.ToDateTime(txtEndDate.Text + " " + txtEndTime.Text); }
catch{}
eSalesFilter clseSalesFilter = new eSalesFilter();
if (lblTask.Text == "esales")
{
clseSalesFilter.FilterIncludeIneSales = true;
clseSalesFilter.IncludeIneSales = true;
}
DataClass clsDataClass = new DataClass();
System.Data.DataTable dt;
string ReportType = cboReportType.SelectedItem.Text;
long lngSupplierID = long.Parse(cboSupplier.SelectedValue);
switch (ReportType)
{
case "Posted PO":
Data.PO clsPO = new Data.PO();
dt = clsPO.ListAsDataTable(POStatus.Posted, new Data.PODetails(), Constants.C_DATE_MIN_VALUE, Constants.C_DATE_MIN_VALUE, PostingDateFrom, PostingDateTo, SupplierID: lngSupplierID, clseSalesFilter: clseSalesFilter);
clsPO.CommitAndDispose();
foreach(System.Data.DataRow dr in dt.Rows)
{
DataRow drPO = rptds.PO.NewRow();
foreach (DataColumn dc in rptds.PO.Columns)
drPO[dc] = dr[dc.ColumnName];
rptds.PO.Rows.Add(drPO);
}
break;
case "Posted PO Returns":
Data.POReturns clsPOReturns = new Data.POReturns();
dt = clsPOReturns.ListAsDataTable(POReturnStatus.Posted, PostingStartDate: PostingDateFrom, PostingEndDate: PostingDateTo, SupplierID: lngSupplierID, clseSalesFilter: clseSalesFilter);
clsPOReturns.CommitAndDispose();
foreach(System.Data.DataRow dr in dt.Rows)
{
DataRow drPOReturns = rptds.POReturns.NewRow();
foreach (DataColumn dc in rptds.POReturns.Columns)
drPOReturns[dc] = dr[dc.ColumnName];
rptds.POReturns.Rows.Add(drPOReturns);
}
break;
case "Posted Debit Memo":
Data.DebitMemos clsDebitMemos = new Data.DebitMemos();
dt = clsDebitMemos.ListAsDataTable(DebitMemoStatus.Posted, PostingStartDate: PostingDateFrom, PostingEndDate: PostingDateTo, SupplierID: lngSupplierID, clseSalesFilter: clseSalesFilter);
clsDebitMemos.CommitAndDispose();
foreach(System.Data.DataRow dr in dt.Rows)
{
DataRow drDebitMemos = rptds.DebitMemo.NewRow();
foreach (DataColumn dc in rptds.DebitMemo.Columns)
drDebitMemos[dc] = dr[dc.ColumnName];
rptds.DebitMemo.Rows.Add(drDebitMemos);
}
break;
case "By Vendor":
Data.PurchaseAnalysis clsPurchaseAnalysis = new Data.PurchaseAnalysis();
dt = clsPurchaseAnalysis.ByVendor(PostingDateFrom, PostingDateTo);
clsPurchaseAnalysis.CommitAndDispose();
foreach(System.Data.DataRow dr in dt.Rows)
{
DataRow drPAByVendor = rptds.PurchaseAnalysisPerVendor.NewRow();
foreach (DataColumn dc in rptds.PurchaseAnalysisPerVendor.Columns)
drPAByVendor[dc] = dr[dc.ColumnName];
rptds.PurchaseAnalysisPerVendor.Rows.Add(drPAByVendor);
}
break;
default:
break;
}
Report.SetDataSource(rptds);
SetParameters(Report);
}
