I am currently migrating from asp classic to asp.net. I've built the web form to display a bunch of data from a database which contains a union query and then I have another query adding the totals together.
There are a number of fields within this query and everything displays correctly apart from the final query which seems to be just one record rather than the product from the sum query.
I don't see why it would not be able to pull the value I want from the sum query since the other values are in fact the product of multiple records, it's really weird and the only work around I've managed so far is making tmp tables to put the values into and
call the values from there.
I am pretty new so I am not sure if the guy who did the original page might have added something in generalmethods or data access which might be the reason I am getting such weird results?
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net.Mail;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Manager_CheckProvinceByMonthAll : SessionForAll
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GeneralMethods.SetGridAttributes(GridView1);
BindReport();
}
}
protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
if (ViewState["reportCheckbkkByMonthSessionDT"] == null)
BindReport();
foreach (GridViewRow gRow in GridView1.Rows)
{
LeftToSendDontang += Convert.ToDecimal(((Label)gRow.FindControl("lblLeftToSendDontang") as Label).Text);
LeftToSendBlaytang += Convert.ToDecimal(((Label)gRow.FindControl("lblLeftToSendBlaytang") as Label).Text);
Credit += Convert.ToDecimal(((Label)gRow.FindControl("lblCredit") as Label).Text);
Revenue += Convert.ToDecimal(((Label)gRow.FindControl("lblRevenue") as Label).Text);
Blaytang += Convert.ToDecimal(((Label)gRow.FindControl("lblBlaytang") as Label).Text);
Discounts += Convert.ToDecimal(((Label)gRow.FindControl("lblDiscounts") as Label).Text);
AfterDiscount += Convert.ToDecimal(((Label)gRow.FindControl("lblAfterDiscount") as Label).Text);
Expense += Convert.ToDecimal(((Label)gRow.FindControl("lblExpense") as Label).Text);
TOut += Convert.ToDecimal(((Label)gRow.FindControl("lblTOut") as Label).Text);
TIn += Convert.ToDecimal(((Label)gRow.FindControl("lblTIn") as Label).Text);
PIn += Convert.ToDecimal(((Label)gRow.FindControl("lblPIn") as Label).Text);
Balance += Convert.ToDecimal(((Label)gRow.FindControl("lblBalance") as Label).Text);
}
Label lblTotalLeftToSendDontang = GridView1.FooterRow.FindControl("lblTotalLeftToSendDontang") as Label;
Label lblTotalLeftToSendBlaytang = GridView1.FooterRow.FindControl("lblTotalLeftToSendBlaytang") as Label;
Label lblTotalCredit = GridView1.FooterRow.FindControl("lblTotalCredit") as Label;
Label lblTotalRevenue = GridView1.FooterRow.FindControl("lblTotalRevenue") as Label;
Label lblTotalBlaytang = GridView1.FooterRow.FindControl("lblTotalBlaytang") as Label;
Label lblTotalDiscounts = GridView1.FooterRow.FindControl("lblTotalDiscounts") as Label;
Label lblTotalAfterDiscount = GridView1.FooterRow.FindControl("lblTotalAfterDiscount") as Label;
Label lblTotalExpense = GridView1.FooterRow.FindControl("lblTotalExpense") as Label;
Label lblTotalTOut = GridView1.FooterRow.FindControl("lblTotalTOut") as Label;
Label lblTotalTIn = GridView1.FooterRow.FindControl("lblTotalTIn") as Label;
Label lblTotalPIn = GridView1.FooterRow.FindControl("lblTotalPIn") as Label;
Label lblTotalBalance = GridView1.FooterRow.FindControl("lblTotalBalance") as Label;
public SortDirection dir
{
get
{
if (ViewState["sort"] == null)
{
ViewState["sort"] = SortDirection.Ascending;
}
return (SortDirection)ViewState["sort"];
}
set
{
ViewState["sort"] = value;
}
}
}
The Data Access page:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Globalization;
using System.Configuration;
using System.IO;
using System.Web.Configuration;
using System.Web.UI.WebControls;
using System.Data;
using System.Web.UI;
using System.Text.RegularExpressions;
using System.Net.Mail;
using System.Web.Services;
using System.Web.Script.Services;
public static DataView GetBranches()
{
string query = "SELECT [BranchID], [Branch] FROM [tblBranches] ORDER BY [Branch]";
return SelectDataView_TMS(query);
}
public static DataView GetEmployees(int jobFunction)
{
string query = "SELECT Employee_ID, Nick_Name, First_Name, Last_Name From tbl_Employee_Data Where BRANCHIDE = " + GeneralMethods.GetBranchID() + " ";
if (jobFunction > 0)
query += " And job_function = '" + jobFunction + "' ";
query += " Order By Nick_Name ";
//" From tbl_Employee_Data Where jobfunction = " + System.Configuration.ConfigurationSettings.AppSettings["JobFunction"].ToString() + " AND BRANCHIDE = " + GeneralMethods.GetBranchID() + " AND [current]=NO Order By Nick_Name";
DataView dvsql = SelectDataView_TMS(query);
for (int i = 0; i < dvsql.Count; i++)
{
if (string.IsNullOrEmpty(dvsql[i]["Nick_Name"].ToString()))
{
dvsql[i]["Nick_Name"] = dvsql[i]["First_Name"].ToString() + " " + dvsql[i]["Last_Name"].ToString();
}
}
return dvsql;
}
public static DataView GetPayments()
{
string query = "SELECT [Payment_ID], [Payment_Type_Th], [Payment_Type_En] FROM tbl_Payment_ID Order By Payment_Type_Th";
return SelectDataView_TMS(query);
}
#region Email Setting
public static string SendEmail(string[] receivermails, string emailsubject, string body, string sendfilepath)
{
string result = "Email Sent";
try
{
SmtpClient smtpClient = new SmtpClient(SMTP(), Port());
smtpClient.Credentials = new System.Net.NetworkCredential(Username(), Password());
smtpClient.EnableSsl = false;
smtpClient.Timeout = 20000;
MailMessage mail = new MailMessage();
mail.IsBodyHtml = true;
mail.Subject = emailsubject;
mail.Body = body;
Attachment attachment = null;
if (System.IO.File.Exists(HttpContext.Current.Server.MapPath(sendfilepath)))
{
attachment = new Attachment(HttpContext.Current.Server.MapPath(sendfilepath));
mail.Attachments.Add(attachment);
}
mail.From = new MailAddress(Username());
int j = 0;
string reg = @"^((([\w]+\.[\w]+)+)|([\w]+))@(([\w]+\.)+)([A-Za-z]{1,3})$";
if (GeneralMethods.IsTestMode())
{
mail.To.Add(new MailAddress(TestEmail()));
}
else
{
for (j = 0; j < receivermails.Length; j++)
{
if (Regex.IsMatch(receivermails[j], reg))
{
mail.To.Add(new MailAddress(receivermails[j]));
break;
}
}
}
if (!GeneralMethods.IsTestMode())
{
for (int i = j + 1; i < receivermails.Length; i++)
{
if (Regex.IsMatch(receivermails[i], reg))
{
mail.CC.Add(new MailAddress(receivermails[i]));
}
}
}
mail.ReplyTo = new MailAddress(noReply());
smtpClient.Send(mail);
if (attachment != null)
attachment.Dispose();
mail.Dispose();
smtpClient.Dispose();
}
catch (Exception ex)
{
result = ex.Message;
}
return result;
}
public static string TestEmail()
{
return ConfigurationManager.AppSettings["TestEmail"].ToString().Trim();
}
public static bool IsTestMode()
{
if (ConfigurationManager.AppSettings["IsTestMode"].ToString().Trim() == "1")
return true;
else
return false;
}
public static string SMTP()
{
return ConfigurationManager.AppSettings["smtp"].ToString();
}
public static string Username()
{
return ConfigurationManager.AppSettings["username"].ToString();
}
public static string Password()
{
return ConfigurationManager.AppSettings["password"].ToString();
}
public static int Port()
{
return Convert.ToInt32(ConfigurationManager.AppSettings["port"].ToString());
}
public static bool Ssl()
{
return Convert.ToBoolean(ConfigurationManager.AppSettings["Ssl"].ToString());
}
public static string noReply()
{
return ConfigurationManager.AppSettings["noReply"].ToString();
}
#endregion Email Setting
#region Reports
public static string ReportType_CreditReport = "CreditReports";
public static string ReportType_TruckReports = "TruckReports";
public static string ReportType_BillReports = "BillReports";
public static void CreateDirectory(string folderPath)
{
folderPath = HttpContext.Current.Server.MapPath(folderPath);
if (ReportType == GeneralMethods.ReportType_TruckReports)
{
CreateDirectory("~/Reports/TruckReports/" + folderDate);
}
else if (ReportType == GeneralMethods.ReportType_CreditReport)
{
CreateDirectory("~/Reports/CreditReports/" + folderDate);
}
else if (ReportType == ReportType_BillReports)
{
CreateDirectory("~/Reports/BillReports/" + folderDate);
}
string pdfHtmlToPdfExePath = GeneralMethods.Path_wkhtmltopdf();
string urlsSeparatedBySpaces = string.Empty;
var p = new System.Diagnostics.Process();
//Determine inputs
if ((urls == null) || (urls.Length == 0))
throw new Exception("No input URLs provided for HtmlToPdf");
else
urlsSeparatedBySpaces = String.Join(" ", urls); //Concatenate URLs
string outputFolder = pdfOutputLocation;
string outputFilename = outputFilenamePrefix + ".PDF"; // assemble destination PDF file name
p = new System.Diagnostics.Process()
{
StartInfo =
{
FileName = pdfHtmlToPdfExePath,
Arguments = ((options == null) ? "" : String.Join(" ", options)) + " " + urlsSeparatedBySpaces + " " + outputFilename,
UseShellExecute = false, // needs to be false in order to redirect output
RedirectStandardOutput = true,
RedirectStandardError = true,
RedirectStandardInput = true, // redirect all 3, as it should be all 3 or none
WorkingDirectory = HttpContext.Current.Server.MapPath(outputFolder)
}
};
try
{
p.Start();
// read the output here...
var output = p.StandardOutput.ReadToEnd();
var errorOutput = p.StandardError.ReadToEnd();
// ...then wait n milliseconds for exit (as after exit, it can't read the output)
p.WaitForExit(60000);
// read the exit code, close process
int returnCode = p.ExitCode;
p.Close();
p.Kill();
// if 0 or 2, it worked so return path of pdf
if ((returnCode == 0) || (returnCode == 2))
{
//SUCCESS
}
else
{
}
}
catch (Exception exc)
{
p.Close();
p.Kill();
}
SELECT UnionTotalAccounts.yearpart, UnionTotalAccounts.monthpart, UnionTotalAccounts.pdate, UnionTotalAccounts.branch_id, UnionTotalAccounts.branch, Sum(UnionTotalAccounts.expense) AS SumOfexpense, Sum(UnionTotalAccounts.transferin) AS SumOftransferin, Sum(UnionTotalAccounts.transferout)
AS SumOftransferout, Sum(UnionTotalAccounts.totalbillother) AS SumOftotalbillother, Sum(UnionTotalAccounts.totalbill) AS SumOftotalbill, Sum(UnionTotalAccounts.sumofdiscountpr) AS SumOfsumofdiscountpr, Sum([TotalBill]-[SumofDiscountPr]) AS LeftDis, Sum(UnionTotalAccounts.lefttotransfer)
AS SumOflefttotransfer, Sum(UnionTotalAccounts.productback1) AS SumOfproductback1
FROM UnionTotalAccounts
GROUP BY UnionTotalAccounts.yearpart, UnionTotalAccounts.monthpart, UnionTotalAccounts.pdate, UnionTotalAccounts.branch_id, UnionTotalAccounts.branch;
The SQL from UNIONTOTALACCOUNTS
SELECT yearpart, monthpart, pdate, branch_id, branch, expense, transferin, transferout, totalbillother, totalbill, sumofdiscountpr, lefttotransfer, productback1
FROM QryToCheckAccountsByDay
UNION SELECT yearpart, monthpart, pdate, branch_id, branch, expense, transferin, transferout, totalbillother, totalbill, sumofdiscountpr, lefttotransfer, productback1
FROM QryToCheckAccountsByDay32559
UNION SELECT yearpart, monthpart, pdate, branch_id, branch, expense, transferin, transferout, totalbillother, totalbill, sumofdiscountpr, lefttotransfer, productback1
FROM QryTempQryToCheckAccountsByDay;
It's baffling, getting quite stuck here, can anybody see where I am going wrong?
According to your description, i'm not sure what's your issue. and the code snippet is not complete, and i could not run it on my side, could you please describe your issue in detailed and provide a simple demo about the issue which could reproduce your
issue on my side.
Not quite sure myself lol, I probably fixed the issue, I think one field value wasn't being displayed in asp.net whereas it was being displayed in asp classic or something like that. It's working fine now but I can't remember what I did to resolve the issue.
Member
14 Points
62 Posts
Weird Issue with a union query
Apr 05, 2016 02:41 AM|ben2203|LINK
OK bit of background here,
I am currently migrating from asp classic to asp.net. I've built the web form to display a bunch of data from a database which contains a union query and then I have another query adding the totals together.
There are a number of fields within this query and everything displays correctly apart from the final query which seems to be just one record rather than the product from the sum query.
I don't see why it would not be able to pull the value I want from the sum query since the other values are in fact the product of multiple records, it's really weird and the only work around I've managed so far is making tmp tables to put the values into and call the values from there.
I am pretty new so I am not sure if the guy who did the original page might have added something in generalmethods or data access which might be the reason I am getting such weird results?
The web page:
<%@ Page Title="บัณชีรายเดือน" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="CheckProvinceByMonthAll.aspx.cs" Inherits="Manager_CheckProvinceByMonthAll" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>
<asp:Content ID="Content1" ContentPlaceHolderID="Stylesheets" runat="server">
<link href="/Content/css/template.css" rel="stylesheet" />
<style type="text/css">
th, td {
border: 0;
text-align: center !important;
}
</style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="FeaturedContent" runat="server">
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="MainContent" runat="server">
<div class="body reportGrid">
<asp:UpdatePanel ID="upp1" runat="server">
<ContentTemplate>
<div style="width: 30px; position: absolute; margin-top: 77px; margin-left: 40%;">
<asp:UpdateProgress ID="UpdateProgress1" runat="server">
<ProgressTemplate>
<img src="../Images/Manage/loading.gif" />
</ProgressTemplate>
</asp:UpdateProgress>
</div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
ShowHeaderWhenEmpty="True" EmptyDataText="No records found."
ShowFooter="true" AllowPaging="True" PageSize="1" AllowSorting="True" Width="100%"
OnPageIndexChanging="GridView1_PageIndexChanging" OnSorting="GridView1_Sorting">
<AlternatingRowStyle BorderStyle="None" />
<Columns>
<asp:TemplateField HeaderText="สาขา" SortExpression="Branch">
<ItemTemplate>
<asp:Label ID="hlBranch" runat="server" Text='<%# Bind("Branch")%>' />
</ItemTemplate>
<FooterTemplate>
รวม:
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="ปี" SortExpression="YearPart">
<ItemTemplate>
<asp:HyperLink ID="hlYearPart" runat="server" Text='<%# Bind("YearPart")%>' NavigateUrl='<%# "/Manager/CheckProvinceByDayAll.aspx?YearPart="+Eval("YearPart")+"&MonthPart="+Eval("MonthPart")+"&BranchID="+Eval("Branch_id") %>' CssClass="linkClass" />
</ItemTemplate>
<FooterTemplate>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="ด." SortExpression="MonthPart">
<ItemTemplate>
<asp:HyperLink ID="hlMonthPart" runat="server" Text='<%# Bind("MonthPart")%>' NavigateUrl='<%# "/Manager/CheckProvinceByDayAll.aspx?YearPart="+Eval("YearPart")+"&MonthPart="+Eval("MonthPart")+"&BranchID="+Eval("Branch_id") %>' CssClass="linkClass" />
</ItemTemplate>
<FooterTemplate>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="ค้างส่ง <br>(ต้นทาง)" SortExpression="LeftToSendDontang">
<ItemTemplate>
<a href="<%# "/Manager/CheckUnsent.aspx?YearPart="+Eval("YearPart")+"&MonthPart="+Eval("MonthPart")+"&BranchID="+Eval("Branch_id") %>">
<asp:Label ID="lblLeftToSendDontang" runat="server" Text='<%# GeneralMethods.AmountFormatComa(Eval("DontangLeft").ToString()) %>' />
</a>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalLeftToSendDontang" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="ค้างส่ง <br>(ปลายทาง)" SortExpression="LeftToSendBlaytang">
<ItemTemplate>
<a href="<%# "/Manager/CheckUnsent.aspx?YearPart="+Eval("YearPart")+"&MonthPart="+Eval("MonthPart")+"&BranchID="+Eval("Branch_id") %>">
<asp:Label ID="lblLeftToSendBlaytang" runat="server" Text='<%# GeneralMethods.AmountFormatComa(Eval("BlaytangLeft").ToString()) %>' />
</a>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalLeftToSendBlaytang" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="ค้างจ่าย" SortExpression="Credit">
<ItemTemplate>
<a href="<%# "/Manager/CheckProvCred.aspx?YearPart="+Eval("YearPart")+"&MonthPart="+Eval("MonthPart")+"&BranchID="+Eval("Branch_id") %>">
<asp:Label ID="lblCredit" runat="server" Text='<%# GeneralMethods.AmountFormatComa(Eval("TotalCredit1").ToString()) %>' />
</a>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalCredit" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="รายรับ" SortExpression="Revenue">
<ItemTemplate>
<asp:Label ID="lblRevenue" runat="server" Text='<%# GeneralMethods.AmountFormatComa(Eval("Tot").ToString()) %>' />
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalRevenue" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="ปลายทาง" SortExpression="Blaytang">
<ItemTemplate>
<asp:Label ID="lblBlaytang" runat="server" Text='<%# GeneralMethods.AmountFormatComa(Eval("Blaytang").ToString()) %>' />
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalBlaytang" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="ส่วนลด" SortExpression="Discounts">
<ItemTemplate>
<asp:Label ID="lblDiscounts" runat="server" Text='<%# GeneralMethods.AmountFormatComa(Eval("Discount").ToString()) %>' />
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalDiscounts" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="หลังหัก<br>ส่วนลด" SortExpression="AfterDiscount">
<ItemTemplate>
<asp:Label ID="lblAfterDiscount" runat="server" Text='<%# GeneralMethods.AmountFormatComa(Eval("LeftDis").ToString()) %>' />
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalAfterDiscount" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="รายจ่าย" SortExpression="Expenses">
<ItemTemplate>
<a href="<%# "/Manager/CheckExpenses.aspx?YearPart="+Eval("YearPart")+"&MonthPart="+Eval("MonthPart")+"&BranchID="+Eval("Branch_id") %>">
<asp:Label ID="lblExpense" runat="server" Text='<%# GeneralMethods.AmountFormatComa(Eval("Expense").ToString()) %>' />
</a>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalExpense" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Adj.-" SortExpression="TransferOut">
<ItemTemplate>
<a href="<%# "/Manager/CheckTransfers.aspx?YearPart="+Eval("YearPart")+"&MonthPart="+Eval("MonthPart")+"&BranchID="+Eval("Branch_id") %>">
<asp:Label ID="lblTOut" runat="server" Text='<%# GeneralMethods.AmountFormatComa(Eval("TOut").ToString()) %>' />
</a>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalTOut" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Adj.+" SortExpression="TransferIn">
<ItemTemplate>
<a href="<%# "/Manager/CheckTransfers.aspx?YearPart="+Eval("YearPart")+"&MonthPart="+Eval("MonthPart")+"&BranchID="+Eval("Branch_id") %>">
<asp:Label ID="lblTIn" runat="server" Text='<%# GeneralMethods.AmountFormatComa(Eval("Tin").ToString()) %>' />
</a>
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalTIn" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="ของกลับ" SortExpression="TransferIn">
<ItemTemplate>
<asp:Label ID="lblPIn" runat="server" Text='<%# GeneralMethods.AmountFormatComa(Eval("productback").ToString()) %>' />
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalPIn" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="คงเหลือ" SortExpression="LefttoTransfer1">
<ItemTemplate>
<asp:Label ID="lblBalance" runat="server" Text='<%# GeneralMethods.AmountFormatComa(Eval("Left").ToString()) %>' />
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalBalance" runat="server" />
</FooterTemplate>
</asp:TemplateField>
</Columns>
<RowStyle BorderStyle="None" />
</asp:GridView>
</ContentTemplate>
</asp:UpdatePanel>
</div>
</asp:Content>
The code behind:
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net.Mail;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class Manager_CheckProvinceByMonthAll : SessionForAll
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GeneralMethods.SetGridAttributes(GridView1);
BindReport();
}
}
protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
if (ViewState["reportCheckbkkByMonthSessionDT"] == null)
BindReport();
string SortDir = string.Empty;
if (dir == SortDirection.Ascending)
{
dir = SortDirection.Descending;
SortDir = "Desc";
}
else
{
dir = SortDirection.Ascending;
SortDir = "Asc";
}
DataView sortedView = new DataView((DataTable)ViewState["reportCheckbkkByMonthSessionDT"]);
sortedView.Sort = e.SortExpression + " " + SortDir;
GridView1.DataSource = sortedView;
GridView1.DataBind();
FooterCalculations();
}
public void FooterCalculations()
{
if (GridView1.Rows.Count > 0)
{
decimal LeftToSendDontang = 0;
decimal LeftToSendBlaytang = 0;
decimal Credit = 0;
decimal Revenue = 0;
decimal Blaytang = 0;
decimal Discounts = 0;
decimal AfterDiscount = 0;
decimal Expense = 0;
decimal TOut = 0;
decimal TIn = 0;
decimal PIn = 0;
decimal Balance = 0;
foreach (GridViewRow gRow in GridView1.Rows)
{
LeftToSendDontang += Convert.ToDecimal(((Label)gRow.FindControl("lblLeftToSendDontang") as Label).Text);
LeftToSendBlaytang += Convert.ToDecimal(((Label)gRow.FindControl("lblLeftToSendBlaytang") as Label).Text);
Credit += Convert.ToDecimal(((Label)gRow.FindControl("lblCredit") as Label).Text);
Revenue += Convert.ToDecimal(((Label)gRow.FindControl("lblRevenue") as Label).Text);
Blaytang += Convert.ToDecimal(((Label)gRow.FindControl("lblBlaytang") as Label).Text);
Discounts += Convert.ToDecimal(((Label)gRow.FindControl("lblDiscounts") as Label).Text);
AfterDiscount += Convert.ToDecimal(((Label)gRow.FindControl("lblAfterDiscount") as Label).Text);
Expense += Convert.ToDecimal(((Label)gRow.FindControl("lblExpense") as Label).Text);
TOut += Convert.ToDecimal(((Label)gRow.FindControl("lblTOut") as Label).Text);
TIn += Convert.ToDecimal(((Label)gRow.FindControl("lblTIn") as Label).Text);
PIn += Convert.ToDecimal(((Label)gRow.FindControl("lblPIn") as Label).Text);
Balance += Convert.ToDecimal(((Label)gRow.FindControl("lblBalance") as Label).Text);
}
Label lblTotalLeftToSendDontang = GridView1.FooterRow.FindControl("lblTotalLeftToSendDontang") as Label;
Label lblTotalLeftToSendBlaytang = GridView1.FooterRow.FindControl("lblTotalLeftToSendBlaytang") as Label;
Label lblTotalCredit = GridView1.FooterRow.FindControl("lblTotalCredit") as Label;
Label lblTotalRevenue = GridView1.FooterRow.FindControl("lblTotalRevenue") as Label;
Label lblTotalBlaytang = GridView1.FooterRow.FindControl("lblTotalBlaytang") as Label;
Label lblTotalDiscounts = GridView1.FooterRow.FindControl("lblTotalDiscounts") as Label;
Label lblTotalAfterDiscount = GridView1.FooterRow.FindControl("lblTotalAfterDiscount") as Label;
Label lblTotalExpense = GridView1.FooterRow.FindControl("lblTotalExpense") as Label;
Label lblTotalTOut = GridView1.FooterRow.FindControl("lblTotalTOut") as Label;
Label lblTotalTIn = GridView1.FooterRow.FindControl("lblTotalTIn") as Label;
Label lblTotalPIn = GridView1.FooterRow.FindControl("lblTotalPIn") as Label;
Label lblTotalBalance = GridView1.FooterRow.FindControl("lblTotalBalance") as Label;
lblTotalLeftToSendDontang.Text = GeneralMethods.AmountFormatComa(LeftToSendDontang.ToString());
lblTotalLeftToSendBlaytang.Text = GeneralMethods.AmountFormatComa(LeftToSendBlaytang.ToString());
lblTotalCredit.Text = GeneralMethods.AmountFormatComa(Credit.ToString());
lblTotalRevenue.Text = GeneralMethods.AmountFormatComa(Revenue.ToString());
lblTotalBlaytang.Text = GeneralMethods.AmountFormatComa(Blaytang.ToString());
lblTotalDiscounts.Text = GeneralMethods.AmountFormatComa(Discounts.ToString());
lblTotalAfterDiscount.Text = GeneralMethods.AmountFormatComa(AfterDiscount.ToString());
lblTotalExpense.Text = GeneralMethods.AmountFormatComa(Expense.ToString());
lblTotalTOut.Text = GeneralMethods.AmountFormatComa(TOut.ToString());
lblTotalTIn.Text = GeneralMethods.AmountFormatComa(TIn.ToString());
lblTotalPIn.Text = GeneralMethods.AmountFormatComa(PIn.ToString());
lblTotalBalance.Text = GeneralMethods.AmountFormatComa(Balance.ToString());
}
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
BindReport();
}
public void BindReport()
{
string query = "Select * From tmpTotalAccounts WHERE branch_id = " + Request.QueryString["branchid"] + "";
DataTable dt = DataAccess.SelectDataTable_ACC(query);
GridView1.DataSource = dt;
GridView1.DataBind();
FooterCalculations();
ViewState["reportCheckbkkByMonthSessionDT"] = dt;
}
public SortDirection dir
{
get
{
if (ViewState["sort"] == null)
{
ViewState["sort"] = SortDirection.Ascending;
}
return (SortDirection)ViewState["sort"];
}
set
{
ViewState["sort"] = value;
}
}
}
The Data Access page:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Globalization;
using System.Configuration;
using System.IO;
using System.Web.Configuration;
using System.Web.UI.WebControls;
using System.Data;
using System.Web.UI;
using System.Text.RegularExpressions;
using System.Net.Mail;
using System.Web.Services;
using System.Web.Script.Services;
public static class DataAccess
{
static string providerNameTMS = WebConfigurationManager.ConnectionStrings["tmsconnectionstring"].ProviderName;
static string connectionStringTMS = WebConfigurationManager.ConnectionStrings["tmsconnectionstring"].ConnectionString;
static string providerNameEngconnection = WebConfigurationManager.ConnectionStrings["Engconnectionstring"].ProviderName;
static string connectionStringEngconnection = WebConfigurationManager.ConnectionStrings["Engconnectionstring"].ConnectionString;
static string providerNameACCconnection = WebConfigurationManager.ConnectionStrings["Accountsconnectionstring"].ProviderName;
static string connectionStringACCconnection = WebConfigurationManager.ConnectionStrings["Accountsconnectionstring"].ConnectionString;
static string providerNameDELconnection = WebConfigurationManager.ConnectionStrings["Deliveredconnectionstring"].ProviderName;
static string connectionStringDELconnection = WebConfigurationManager.ConnectionStrings["Deliveredconnectionstring"].ConnectionString;
static SqlDataSource sqlDS;
static DataView dv;
static DataTable dt;
public static DataView SelectDataView_ENG(string query)
{
return SelectDataView(query, providerNameEngconnection, connectionStringEngconnection);
}
public static DataTable SelectDataTable_ENG(string query)
{
dt = new DataTable();
dt = SelectDataView_ENG(query).ToTable();
return dt;
}
public static DataView SelectDataView_ACC(string query)
{
return SelectDataView(query, providerNameACCconnection, connectionStringACCconnection);
}
public static DataTable SelectDataTable_ACC(string query)
{
dt = new DataTable();
dt = SelectDataView_ACC(query).ToTable();
return dt;
}
public static DataView SelectDataView_DEL(string query)
{
return SelectDataView(query, providerNameACCconnection, connectionStringDELconnection);
}
public static DataTable SelectDataTable_DEL(string query)
{
dt = new DataTable();
dt = SelectDataView_DEL(query).ToTable();
return dt;
}
public static void UpdateRecord_ENG(string query)
{
UpdateRecord(query, providerNameEngconnection, connectionStringEngconnection);
}
public static void InsertRecord_ENG(string query)
{
InsertRecord(query, providerNameEngconnection, connectionStringEngconnection);
}
public static DataView SelectDataView_TMS(string query)
{
return SelectDataView(query, providerNameTMS, connectionStringTMS);
}
public static DataTable SelectDataTable_TMS(string query)
{
dt = new DataTable();
dt = SelectDataView_TMS(query).ToTable();
return dt;
}
public static void UpdateRecord_TMS(string query)
{
UpdateRecord(query, providerNameTMS, connectionStringTMS);
}
public static void InsertRecord_TMS(string query)
{
InsertRecord(query, providerNameTMS, connectionStringTMS);
}
public static DataView SelectDataView(string query, string providerName, string connectionString)
{
sqlDS = new SqlDataSource(providerName, connectionString, string.Empty);
sqlDS.SelectCommand = query;
dv = new DataView();
dv = (DataView)sqlDS.Select(DataSourceSelectArguments.Empty);
sqlDS.Dispose();
return dv;
}
public static DataTable SelectDataTable(string query, string providerName, string connectionString)
{
dt = new DataTable();
dt = SelectDataView(query, providerName, connectionString).ToTable();
return dt;
}
public static void UpdateRecord(string query, string providerName, string connectionString)
{
sqlDS = new SqlDataSource(providerName, connectionString, string.Empty);
sqlDS.UpdateCommand = query;
sqlDS.Update();
sqlDS.Dispose();
}
public static void InsertRecord(string query, string providerName, string connectionString)
{
sqlDS = new SqlDataSource(providerName, connectionString, string.Empty);
sqlDS.InsertCommand = query;
sqlDS.Insert();
sqlDS.Dispose();
}
public static void DeleteRecord_TMS(string query)
{
DeleteRecord(query, providerNameTMS, connectionStringTMS);
}
public static void DeleteRecord(string query, string providerName, string connectionString)
{
sqlDS = new SqlDataSource(providerName, connectionString, string.Empty);
sqlDS.DeleteCommand = query;
sqlDS.Delete();
sqlDS.Dispose();
}
public static DropDownList BindDropDownList(DataView dv, DropDownList ddl, string text, string value = "")
{
ddl.DataSource = dv;
ddl.DataTextField = text;
ddl.DataValueField = !string.IsNullOrEmpty(value) ? value : text;
ddl.DataBind();
return ddl;
}
public static DataTable GetFiltersDT()
{
DataTable dt = new DataTable();
dt.Columns.Add("Key", typeof(string));
dt.Columns.Add("Value", typeof(string));
dt.Rows.Add("ทั้งหมด", "");
dt.Rows.Add("เท่ากับ", "=");
dt.Rows.Add("น้อยกว่า", "<");
dt.Rows.Add("น้อกว่ารึเท่ากับ", "<=");
dt.Rows.Add("มากกว่า", ">");
dt.Rows.Add("มากกว่ารึเท่ากับ", ">=");
return dt;
}
public static DataView GetSenderReceiver(string wherClause, string likeClause)
{
string query = "Select [CustID], [Company], [FirstName], [LastName], [FirstName], [LastName] as FullName FROM tbl_Customers Where Company ";
if (!string.IsNullOrEmpty(wherClause))
query += " = '" + wherClause.Trim() + "' ";
else
query += " LIKE \"%" + likeClause.Trim() + "%\" ";
query += " ORDER BY [Company] ";
return SelectDataView_TMS(query);
}
public static DataView GetPackagesType(string wherClause, string likeClause)
{
string query = "SELECT Package_Type FROM [tblPackageTypes] Where Package_Type ";
if (!string.IsNullOrEmpty(wherClause))
query += " = '" + wherClause.Trim() + "' ";
else
query += " LIKE \"%" + likeClause.Trim() + "%\" ";
query += "Order By Package_Type ";
return SelectDataView_TMS(query);
}
public static DataView GetBranches()
{
string query = "SELECT [BranchID], [Branch] FROM [tblBranches] ORDER BY [Branch]";
return SelectDataView_TMS(query);
}
public static DataView GetEmployees(int jobFunction)
{
string query = "SELECT Employee_ID, Nick_Name, First_Name, Last_Name From tbl_Employee_Data Where BRANCHIDE = " + GeneralMethods.GetBranchID() + " ";
if (jobFunction > 0)
query += " And job_function = '" + jobFunction + "' ";
query += " Order By Nick_Name ";
//" From tbl_Employee_Data Where jobfunction = " + System.Configuration.ConfigurationSettings.AppSettings["JobFunction"].ToString() + " AND BRANCHIDE = " + GeneralMethods.GetBranchID() + " AND [current]=NO Order By Nick_Name";
DataView dvsql = SelectDataView_TMS(query);
for (int i = 0; i < dvsql.Count; i++)
{
if (string.IsNullOrEmpty(dvsql[i]["Nick_Name"].ToString()))
{
dvsql[i]["Nick_Name"] = dvsql[i]["First_Name"].ToString() + " " + dvsql[i]["Last_Name"].ToString();
}
}
return dvsql;
}
public static DataView GetPayments()
{
string query = "SELECT [Payment_ID], [Payment_Type_Th], [Payment_Type_En] FROM tbl_Payment_ID Order By Payment_Type_Th";
return SelectDataView_TMS(query);
}
static string path = string.Empty;
static string folder = string.Empty;
public static string DocumentType_Truck = TruckPath();
public static string DocumentType_ReceiptsExpenses = Receipts_ExpensesPath();
public static string DocumentType_Transfers = TransfersPath();
public static string DocumentType_BankTransferSlips = BankTransferSlipsPath();
#region Date Formats
public static string DateFormat(string input)
{
if (!string.IsNullOrEmpty(input))
{
input = Convert.ToDateTime(input).ToString("MM/dd/yyyy");
}
return input;
}
public static string AmountFormat(string input)
{
if (!string.IsNullOrEmpty(input))
{
input = String.Format("{0:0.00}", Convert.ToDecimal(input));
}
return input;
}
public static string DateTimeThai()
{
ThaiBuddhistCalendar cal = new ThaiBuddhistCalendar();
DateTime now = DateTime.Today;
DateTime thai = new DateTime(cal.GetYear(now), cal.GetMonth(now), now.Day);
return thai.ToString("dd/MM/yyyy");
}
public static string DateFormat_Thai(string date)
{
if (!string.IsNullOrEmpty(date))
{
ThaiBuddhistCalendar cal = new ThaiBuddhistCalendar();
DateTime now = Convert.ToDateTime(date);
DateTime thai = new DateTime(cal.GetYear(now), cal.GetMonth(now), now.Day);
date = thai.ToString("dd/MM/yyyy");
}
return date;
}
public static string DateTimeFormat_Thai(string date)
{
if (!string.IsNullOrEmpty(date))
{
ThaiBuddhistCalendar cal = new ThaiBuddhistCalendar();
DateTime now = Convert.ToDateTime(date);
DateTime thai = new DateTime(cal.GetYear(now), cal.GetMonth(now), now.Day, cal.GetHour(now), cal.GetMinute(now), cal.GetSecond(now));
date = thai.ToString("dd/MM/yyyy hh:mm:s tt");
}
return date;
}
public static bool checkDateFormat(string date)
{
DateTime dateTime;
if (DateTime.TryParseExact(date, "MM/dd/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out dateTime))
return true;
else
return false;
}
#endregion Date Formats
#region Common Methods
public static string getBranchinEnglish(string branchid)
{
string providerName = WebConfigurationManager.ConnectionStrings["tmsconnectionstring"].ProviderName;
string connectionString = WebConfigurationManager.ConnectionStrings["tmsconnectionstring"].ConnectionString;
SqlDataSource sss = new SqlDataSource(providerName, connectionString, "SELECT Branch_Eng, Branch FROM [tblBranches] WHERE branchid=" + branchid);
string branch = string.Empty;
DataView dv = (DataView)sss.Select(DataSourceSelectArguments.Empty);
if (dv.Count > 0)
{
branch = dv[0]["Branch_Eng"].ToString();
//branch = dv[0]["Branch"].ToString();
}
sss.Dispose();
return branch;
}
public static string getPaymentMethod(string paymentid)
{
string providerName = WebConfigurationManager.ConnectionStrings["tmsconnectionstring"].ProviderName;
string connectionString = WebConfigurationManager.ConnectionStrings["tmsconnectionstring"].ConnectionString;
SqlDataSource sss = new SqlDataSource(providerName, connectionString, "SELECT * FROM [tbl_ShipOrders]");
sss.SelectCommand = "SELECT Payment_Type_Th FROM tbl_Payment_ID WHERE Payment_ID=" + paymentid;
DataView dv = (DataView)sss.Select(DataSourceSelectArguments.Empty);
String strpayment = "";
if (dv.Count > 0)
strpayment = dv[0]["Payment_Type_Th"].ToString();
sss.Dispose();
return strpayment;
}
public static string getCheckerName(string empid)
{
string providerName = WebConfigurationManager.ConnectionStrings["tmsconnectionstring"].ProviderName;
string connectionString = WebConfigurationManager.ConnectionStrings["tmsconnectionstring"].ConnectionString;
SqlDataSource sss = new SqlDataSource(providerName, connectionString, "SELECT * FROM [tbl_ShipOrders]");
sss.SelectCommand = "SELECT Nick_Name FROM tbl_Employee_Data WHERE Employee_ID=" + empid;
DataView dv = (DataView)sss.Select(DataSourceSelectArguments.Empty);
String strpayment = "";
if (dv.Count > 0)
strpayment = dv[0]["Nick_Name"].ToString();
sss.Dispose();
return strpayment;
}
public static Dictionary<string, string> GetFilters()
{
Dictionary<string, string> filters = new Dictionary<string, string>();
filters.Add("ทั้งหมด", "");
filters.Add("เท่ากับ", "=");
filters.Add("น้อยกว่า", "<");
filters.Add("น้อกว่ารึเท่ากับ", "<=");
filters.Add("มากกว่า", ">");
filters.Add("มากกว่ารึเท่ากับ", ">=");
return filters;
}
public static GridView SetGridAttributes(GridView gv)
{
gv.PageSize = int.Parse(ConfigurationSettings.AppSettings["GridPageSize"].ToString());
gv.PagerSettings.PageButtonCount = int.Parse(ConfigurationSettings.AppSettings["PagerSettings_PageButtonCount"].ToString());
gv.PagerSettings.Position = PagerPosition.TopAndBottom;
//gv.PagerStyle.BackColor = System.Drawing.Color.FromArgb(181, 207, 210);
return gv;
}
#endregion Common Methods
public static string DocumentsPath()
{
return ConfigurationManager.AppSettings["documents_path"].ToString();
}
public static string TruckPath()
{
return DocumentsPath() + ConfigurationManager.AppSettings["trucks"].ToString() + "/";
}
public static string Receipts_ExpensesPath()
{
return DocumentsPath() + ConfigurationManager.AppSettings["receipts_expenses"].ToString() + "/";
}
public static string TransfersPath()
{
return DocumentsPath() + ConfigurationManager.AppSettings["transfers"].ToString() + "/";
}
public static string BankTransferSlipsPath()
{
return DocumentsPath() + ConfigurationManager.AppSettings["banktransferslips"].ToString() + "/";
}
public static string getImageURL(string docType, string ID, string Date)
{
if (string.IsNullOrEmpty(Date))
return string.Empty;
string document = GeneralMethods.GetDocument(docType, ID, Date);
if (!string.IsNullOrEmpty(document))
{
string encodeQueryString = "q=" + ID + "@type=" + docType + "@date=" + Date + "";
encodeQueryString = EncryptDecrypt.Encrypt(encodeQueryString, true);
document = "window.open(\"ShowImage.aspx?q=" + encodeQueryString + "\", \"ShowImage\", \"menubar=0,resizable=0,width=950,height=700,scrollbars=1\", \"\").moveTo(100,100);";
}
return document;
}
public static HyperLink SetImageURL(HyperLink hlImage, string Type)
{
string resutl = GeneralMethods.getImageURL(Type, hlImage.Text, hlImage.CssClass);
if (!string.IsNullOrEmpty(resutl))
{
hlImage.Attributes.Add("onclick", resutl);
hlImage.CssClass = "documentClass";
}
else
{
hlImage.Attributes.Add("onclick", "return false");
hlImage.CssClass = "emptyClass";
}
return hlImage;
}
public static string GetDocumentsPath(string type, string date)
{
if (string.IsNullOrEmpty(date))
return string.Empty;
date = DateFormat_Thai(date);
path = type + (Convert.ToDateTime(date)).Year.ToString() + "/" + Convert.ToDateTime(date).Month.ToString() + "-" + Convert.ToDateTime(date).Year.ToString() + "/";
if (!Directory.Exists(path))
Directory.CreateDirectory(path);
return path;
}
public static string GetDocument(string type, string fileID, string date)
{
if (string.IsNullOrEmpty(date))
return string.Empty;
date = DateFormat_Thai(date);
folder = (Convert.ToDateTime(date)).Year.ToString() + "/" + Convert.ToDateTime(date).Month.ToString() + "-" + Convert.ToDateTime(date).Year.ToString();
fileID = type + folder + "/" + fileID + ".jpg";
if (!File.Exists(fileID))
fileID = string.Empty;
return fileID;
}
public static bool CheckFileExtention(string fileName)
{
string extension = Path.GetExtension(fileName);
if (extension.Trim().ToLower() == ".jpg")
return true;
else
return false;
}
#region Email Setting
public static string SendEmail(string[] receivermails, string emailsubject, string body, string sendfilepath)
{
string result = "Email Sent";
try
{
SmtpClient smtpClient = new SmtpClient(SMTP(), Port());
smtpClient.Credentials = new System.Net.NetworkCredential(Username(), Password());
smtpClient.EnableSsl = false;
smtpClient.Timeout = 20000;
MailMessage mail = new MailMessage();
mail.IsBodyHtml = true;
mail.Subject = emailsubject;
mail.Body = body;
Attachment attachment = null;
if (System.IO.File.Exists(HttpContext.Current.Server.MapPath(sendfilepath)))
{
attachment = new Attachment(HttpContext.Current.Server.MapPath(sendfilepath));
mail.Attachments.Add(attachment);
}
mail.From = new MailAddress(Username());
int j = 0;
string reg = @"^((([\w]+\.[\w]+)+)|([\w]+))@(([\w]+\.)+)([A-Za-z]{1,3})$";
if (GeneralMethods.IsTestMode())
{
mail.To.Add(new MailAddress(TestEmail()));
}
else
{
for (j = 0; j < receivermails.Length; j++)
{
if (Regex.IsMatch(receivermails[j], reg))
{
mail.To.Add(new MailAddress(receivermails[j]));
break;
}
}
}
if (!GeneralMethods.IsTestMode())
{
for (int i = j + 1; i < receivermails.Length; i++)
{
if (Regex.IsMatch(receivermails[i], reg))
{
mail.CC.Add(new MailAddress(receivermails[i]));
}
}
}
mail.ReplyTo = new MailAddress(noReply());
smtpClient.Send(mail);
if (attachment != null)
attachment.Dispose();
mail.Dispose();
smtpClient.Dispose();
}
catch (Exception ex)
{
result = ex.Message;
}
return result;
}
public static string TestEmail()
{
return ConfigurationManager.AppSettings["TestEmail"].ToString().Trim();
}
public static bool IsTestMode()
{
if (ConfigurationManager.AppSettings["IsTestMode"].ToString().Trim() == "1")
return true;
else
return false;
}
public static string SMTP()
{
return ConfigurationManager.AppSettings["smtp"].ToString();
}
public static string Username()
{
return ConfigurationManager.AppSettings["username"].ToString();
}
public static string Password()
{
return ConfigurationManager.AppSettings["password"].ToString();
}
public static int Port()
{
return Convert.ToInt32(ConfigurationManager.AppSettings["port"].ToString());
}
public static bool Ssl()
{
return Convert.ToBoolean(ConfigurationManager.AppSettings["Ssl"].ToString());
}
public static string noReply()
{
return ConfigurationManager.AppSettings["noReply"].ToString();
}
#endregion Email Setting
#region Reports
public static string ReportType_CreditReport = "CreditReports";
public static string ReportType_TruckReports = "TruckReports";
public static string ReportType_BillReports = "BillReports";
public static void CreateDirectory(string folderPath)
{
folderPath = HttpContext.Current.Server.MapPath(folderPath);
if (!Directory.Exists(folderPath))
{
Directory.CreateDirectory(folderPath);
}
}
public static string HtmlToPdf(string pdfOutputLocation, string outputFilenamePrefix, string ReportType, string folderDate, string[] urls, string[] options = null)
{
outputFilenamePrefix = HttpUtility.HtmlDecode(outputFilenamePrefix);
if (string.IsNullOrEmpty(folderDate))
folderDate = DateTime.Now.ToShortDateString();
folderDate = folderDate.Replace("/", "-");
CreateDirectory("~/Reports");
CreateDirectory("~/Reports/" + ReportType + "");
if (ReportType == GeneralMethods.ReportType_TruckReports)
{
CreateDirectory("~/Reports/TruckReports/" + folderDate);
}
else if (ReportType == GeneralMethods.ReportType_CreditReport)
{
CreateDirectory("~/Reports/CreditReports/" + folderDate);
}
else if (ReportType == ReportType_BillReports)
{
CreateDirectory("~/Reports/BillReports/" + folderDate);
}
string pdfHtmlToPdfExePath = GeneralMethods.Path_wkhtmltopdf();
string urlsSeparatedBySpaces = string.Empty;
var p = new System.Diagnostics.Process();
//Determine inputs
if ((urls == null) || (urls.Length == 0))
throw new Exception("No input URLs provided for HtmlToPdf");
else
urlsSeparatedBySpaces = String.Join(" ", urls); //Concatenate URLs
string outputFolder = pdfOutputLocation;
string outputFilename = outputFilenamePrefix + ".PDF"; // assemble destination PDF file name
p = new System.Diagnostics.Process()
{
StartInfo =
{
FileName = pdfHtmlToPdfExePath,
Arguments = ((options == null) ? "" : String.Join(" ", options)) + " " + urlsSeparatedBySpaces + " " + outputFilename,
UseShellExecute = false, // needs to be false in order to redirect output
RedirectStandardOutput = true,
RedirectStandardError = true,
RedirectStandardInput = true, // redirect all 3, as it should be all 3 or none
WorkingDirectory = HttpContext.Current.Server.MapPath(outputFolder)
}
};
try
{
p.Start();
// read the output here...
var output = p.StandardOutput.ReadToEnd();
var errorOutput = p.StandardError.ReadToEnd();
// ...then wait n milliseconds for exit (as after exit, it can't read the output)
p.WaitForExit(60000);
// read the exit code, close process
int returnCode = p.ExitCode;
p.Close();
p.Kill();
// if 0 or 2, it worked so return path of pdf
if ((returnCode == 0) || (returnCode == 2))
{
//SUCCESS
}
else
{
}
}
catch (Exception exc)
{
p.Close();
p.Kill();
}
return outputFolder + "/" + outputFilename;
}
public static string Path_wkhtmltopdf()
{
return ConfigurationManager.AppSettings["Path_wkhtmltopdf"].ToString();
}
#endregion Reports
}
The SQL for the SUM Query
SELECT UnionTotalAccounts.yearpart, UnionTotalAccounts.monthpart, UnionTotalAccounts.pdate, UnionTotalAccounts.branch_id, UnionTotalAccounts.branch, Sum(UnionTotalAccounts.expense) AS SumOfexpense, Sum(UnionTotalAccounts.transferin) AS SumOftransferin, Sum(UnionTotalAccounts.transferout) AS SumOftransferout, Sum(UnionTotalAccounts.totalbillother) AS SumOftotalbillother, Sum(UnionTotalAccounts.totalbill) AS SumOftotalbill, Sum(UnionTotalAccounts.sumofdiscountpr) AS SumOfsumofdiscountpr, Sum([TotalBill]-[SumofDiscountPr]) AS LeftDis, Sum(UnionTotalAccounts.lefttotransfer) AS SumOflefttotransfer, Sum(UnionTotalAccounts.productback1) AS SumOfproductback1
FROM UnionTotalAccounts
GROUP BY UnionTotalAccounts.yearpart, UnionTotalAccounts.monthpart, UnionTotalAccounts.pdate, UnionTotalAccounts.branch_id, UnionTotalAccounts.branch;
The SQL from UNIONTOTALACCOUNTS
SELECT yearpart, monthpart, pdate, branch_id, branch, expense, transferin, transferout, totalbillother, totalbill, sumofdiscountpr, lefttotransfer, productback1
FROM QryToCheckAccountsByDay
UNION SELECT yearpart, monthpart, pdate, branch_id, branch, expense, transferin, transferout, totalbillother, totalbill, sumofdiscountpr, lefttotransfer, productback1
FROM QryToCheckAccountsByDay32559
UNION SELECT yearpart, monthpart, pdate, branch_id, branch, expense, transferin, transferout, totalbillother, totalbill, sumofdiscountpr, lefttotransfer, productback1
FROM QryTempQryToCheckAccountsByDay;
It's baffling, getting quite stuck here, can anybody see where I am going wrong?
Thanks in advance
Ben
Contributor
2570 Points
501 Posts
Re: Weird Issue with a union query
Apr 06, 2016 01:56 PM|Cole Wu|LINK
Hi ben2203,
According to your description, i'm not sure what's your issue. and the code snippet is not complete, and i could not run it on my side, could you please describe your issue in detailed and provide a simple demo about the issue which could reproduce your issue on my side.
Best regards,
Cole Wu
,
Member
14 Points
62 Posts
Re: Weird Issue with a union query
May 15, 2016 03:41 AM|ben2203|LINK
Not quite sure myself lol, I probably fixed the issue, I think one field value wasn't being displayed in asp.net whereas it was being displayed in asp classic or something like that. It's working fine now but I can't remember what I did to resolve the issue.