public string fnCount(string sCompanyId, string sDateS, string sDateE)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[material].[company_id]", sCompanyId);
if (sDateS.Length > 0)
{
sCondition += " AND CONVERT(varchar(10), [receive_m].[datetime], 126) >= '" + sDateS + "' ";
}
if (sDateS.Length > 0)
{
sCondition += " AND CONVERT(varchar(10), [receive_m].[datetime], 126) <= '" + sDateE + "' ";
}
string sCountSql =
" SELECT COUNT([material].[company_id]) " +
" FROM [MNDTreceive_master] [receive_m] " +
" LEFT JOIN [MNDTreceive_details] [receive_d] " +
" ON [receive_m].[order_id] = [receive_d].[order_id] " +
" LEFT JOIN [MNDTmaterial] [material] " +
" ON [receive_d].[material_id] = [material].[material_id] " +
" WHERE [receive_m].[status] <> 'D' " +
" AND [receive_d].[pay_complete] <> 'Y' " + sCondition;
string sPageSize = PublicApi.fnGetValue(sCountSql, "MNDT");
return(sPageSize);
}
protected void Page_Init(object sender, System.EventArgs e)
{
try
{
string sPath = HttpContext.Current.Request.MapPath("~/Report\\");
//表示一个报表，并且包含定义、格式化、加载、导出和打印该报表的属性和方法。
CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
CrystalDecisions.Shared.ParameterDiscreteValue discreteVal = new CrystalDecisions.Shared.ParameterDiscreteValue();
CrystalDecisions.Shared.ParameterValues paraValues = new CrystalDecisions.Shared.ParameterValues();
//加载你事先做好的Crystal Report报表文件
Report.Load(sPath + Request.QueryString["ReportName"], CrystalDecisions.Shared.OpenReportMethod.OpenReportByTempCopy);
string sData = PublicApi.fnGetConStr("MNDT").Replace(";", ",");
// 設定報表內SQL Server資料庫的登入資訊
CrystalDecisions.Shared.TableLogOnInfo logonInfo = new CrystalDecisions.Shared.TableLogOnInfo();
foreach (CrystalDecisions.CrystalReports.Engine.Table table in Report.Database.Tables)
{
logonInfo = table.LogOnInfo;
var _with1 = logonInfo.ConnectionInfo;
_with1.ServerName = fnGetData(sData, "Data Source");
_with1.DatabaseName = fnGetData(sData, "Initial Catalog");
_with1.UserID = fnGetData(sData, "User ID");
_with1.Password = fnGetData(sData, "Password");
table.ApplyLogOnInfo(logonInfo);
}
Report.SetDataSource(Session["PrintDT"]);
//Session["PrintDT"] = null;
if (Report.ParameterFields["列印人員"] != null)
{
Report.SetParameterValue("列印人員", Session["sId"]);
}
if (Report.ParameterFields["進貨稅額"] != null)
{
string sSql =
" SELECT [parameter] " +
" FROM [mndtkind_details] " +
" WHERE [kind_id] = 'TAX' " +
" AND [code_id] = 'IN' ";
Report.SetParameterValue("進貨稅額", PublicApi.fnGetValue(sSql, "MNDT"));
}
CrystalReportViewer1.PrintMode = CrystalDecisions.Web.PrintMode.ActiveX; //列印顯示列印選項，但只有IE會出現這功能
//Report.PrintToPrinter(1, true, 0, 0); // 直接列印
CrystalReportViewer1.ReportSource = Report;
//CrystalReportViewer1.HasExportButton = false; //將匯出的功能False掉
//CrystalReportViewer1.HasPrintButton = false;
//CrystalReportViewer1.HasToggleGroupTreeButton = false; //將樹狀結構的按鈕False掉
//CrystalReportViewer1.DisplayGroupTree = false; //將樹狀結構狀態False掉
}
catch (Exception e1)
{
//Functions.fnMessageBox(e1.ToString(), this);
}
}
public bool fnIsExist(KindM kindM)
{
string sSql = " SELECT COUNT([kind_id]) " +
" FROM [MNDTkind_master] " +
" WHERE [kind_id] = '" + kindM.kind_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public bool fnIsExist(SReturnM sreturnM)
{
string sSql = " SELECT COUNT([return_id]) " +
" FROM [MNDTSreturn_master] " +
" WHERE [return_id] = '" + sreturnM.return_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public string fnGetReturnId(string sOrderId)
{
string sSql = " SELECT TOP 1 [return_id] " +
" FROM [MNDTSreturn_master] " +
" WHERE [order_id] = '" + sOrderId + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT"));
}
public bool fnIsExist(QuotesM quotesM)
{
string sSql = " SELECT COUNT([order_id]) " +
" FROM [MNDTquotes_master] " +
" WHERE [order_id] = '" + quotesM.order_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public bool fnIsExist(ReceiveM receiveM)
{
string sSql = " SELECT COUNT([order_id]) " +
" FROM [MNDTreceive_master] " +
" WHERE [order_id] = '" + receiveM.order_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public bool fnIsExist(GroupM groupM)
{
string sSql = " SELECT COUNT([group_id]) " +
" FROM [MNDTgroup_master] " +
" WHERE [group_id] = '" + groupM.group_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public bool fnIsExist(ProgramM programM)
{
string sSql = " SELECT COUNT([program_id]) " +
" FROM [MNDTprogram_master] " +
" WHERE [program_id] = '" + programM.program_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public bool fnIsExist(Material material)
{
string sSql = " SELECT COUNT([material_id]) " +
" FROM [MNDTmaterial] " +
" WHERE [status] = 'N' " +
" AND [material_id] = '" + material.material_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public bool fnIsExist(MaterialStock materialStock)
{
string sSql = " SELECT COUNT([material_id]) " +
" FROM [MNDTmaterial_stock] " +
" WHERE [material_id] = '" + materialStock.material_id + "' " +
" AND [warehouse_id] = '" + materialStock.warehouse_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public bool fnIsExist(Carousel carousel)
{
string sSql = " SELECT COUNT([id]) " +
" FROM [MNDTCarousel] " +
" WHERE [status] = 'N' " +
" AND [id] = '" + carousel.carousel_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public string fnSelectAmount(QuotesD quotesD)
{
string sSql = " SELECT ISNULL(SUM([modify_amount]), '0') " +
" FROM [MNDTquotes_details] " +
" WHERE [order_id] = '" + quotesD.order_id + "' " +
" AND [product_id] = '" + quotesD.product_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT"));
}
public string fnSelectAllAmount(SalesD salesD)
{
string sSql = " SELECT ISNULL(SUM([amount]), '0') " +
" FROM [MNDTsales_details] " +
" WHERE [order_id] = '" + salesD.order_id + "' " +
" AND [product_id] = '" + salesD.product_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT"));
}
public string fnSelectAmount(PurchaseD purchaseD)
{
string sSql = " SELECT ISNULL(SUM([modify_amount]), '0') " +
" FROM [MNDTpurchase_details] " +
" WHERE [order_id] = '" + purchaseD.order_id + "' " +
" AND [material_id] = '" + purchaseD.material_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT"));
}
public bool fnIsExist(Company company)
{
string sSql = " SELECT COUNT([company_id]) " +
" FROM [MNDTcompany] " +
" WHERE [status] = 'N' " +
" AND [company_id] = '" + company.company_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public bool fnIsExist(ShopItem shopItem)
{
string sSql = " SELECT COUNT([id]) " +
" FROM [MNDTshop_item] " +
" WHERE [status] = 'N' " +
" AND [id] = '" + shopItem.product_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public bool fnIsExist(User user)
{
string sSql = " SELECT COUNT([id]) " +
" FROM [MNDTuser] " +
" WHERE [status] = 'N' " +
" AND [id] = '" + user.id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public string fnSelectAllAmount(ReceiveD receiveD)
{
string sSql = " SELECT ISNULL(SUM([amount]), '0') " +
" FROM [MNDTreceive_details] " +
" WHERE [order_id] = '" + receiveD.order_id + "' " +
" AND [material_id] = '" + receiveD.material_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT"));
}
public string fnSelectAmount(ProductStock productStock)
{
string sSql = " SELECT ISNULL(SUM([amount]), '0') " +
" FROM [MNDTproduct_stock] " +
" WHERE [warehouse_id] = '" + productStock.warehouse_id + "' " +
" AND [product_id] = '" + productStock.product_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT"));
}
public bool fnIsExist(Client client)
{
string sSql = " SELECT COUNT([client_id]) " +
" FROM [MNDTclient] " +
" WHERE [status] = 'N' " +
" AND [client_id] = '" + client.client_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public bool fnIsExist(ProductStock productStock)
{
string sSql = " SELECT COUNT([product_id]) " +
" FROM [MNDTproduct_stock] " +
" WHERE [product_id] = '" + productStock.product_id + "' " +
" AND [warehouse_id] = '" + productStock.warehouse_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public bool fnIsExist(KindD kindD)
{
string sSql =
" SELECT COUNT([code_id]) " +
" FROM [MNDTkind_details] " +
" WHERE [kind_id] = '" + kindD.kind_id + "' " +
" AND [code_id] = '" + kindD.code_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public bool fnIsExist(PurchaseD purchaseD)
{
string sSql =
" SELECT COUNT([material_id]) " +
" FROM [MNDTpurchase_details] " +
" WHERE [order_id] = '" + purchaseD.order_id + "' " +
" AND [material_id] = '" + purchaseD.material_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public bool fnIsExist(QuotesD quotesD)
{
string sSql =
" SELECT COUNT([product_id]) " +
" FROM [MNDTquotes_details] " +
" WHERE [order_id] = '" + quotesD.order_id + "' " +
" AND [product_id] = '" + quotesD.product_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public bool fnIsExist(ReturnD returnD)
{
string sSql =
" SELECT COUNT([material_id]) " +
" FROM [MNDTreturn_details] " +
" WHERE [return_id] = '" + returnD.return_id + "' " +
" AND [material_id] = '" + returnD.material_id + "' " +
" AND [warehouse_id] = '" + returnD.warehouse_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public bool fnIsExist(SReturnD sreturnD)
{
string sSql =
" SELECT COUNT([product_id]) " +
" FROM [MNDTSreturn_details] " +
" WHERE [return_id] = '" + sreturnD.return_id + "' " +
" AND [product_id] = '" + sreturnD.product_id + "' " +
" AND [warehouse_id] = '" + sreturnD.warehouse_id + "' ";
return(PublicApi.fnGetValue(sSql, "MNDT") == "1");
}
public string fnGetSeq(ReceiveD receiveD)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[order_id]", receiveD.order_id);
string sSql =
" SELECT ISNULL(MAX([seq]), 0) + 1 " +
" FROM [MNDTreceive_details] AS [receive_d] " +
" WHERE 1 = 1 " + sCondition;
return(PublicApi.fnGetValue(sSql, "MNDT"));
}
public string fnGetSeq(string sReturnId)
{
string sCondition = "";
sCondition += PublicApi.fnAddCondition("[return_id]", sReturnId);
string sSql =
" SELECT ISNULL(MAX([seq]), 0) + 1 " +
" FROM [MNDTSreturn_details] AS [return_d] " +
" WHERE 1 = 1 " + sCondition;
return(PublicApi.fnGetValue(sSql, "MNDT"));
}
public string fnDelete(KindM kindM)
{
string sDetailsSql = " SELECT COUNT([kind_id]) FROM [MNDTkind_details] WHERE [kind_id] = '" + kindM.kind_id + "' ";
string sCount = PublicApi.fnGetValue(sDetailsSql, "MNDT");
if (sCount == "0")
{
string sSql = " DELETE [MNDTkind_master] " +
" WHERE [kind_id] = '" + kindM.kind_id + "' ";
return(PublicApi.fnExecuteSQL(sSql, "MNDT"));
}
return("錯誤訊息：明細資料大於一筆。");
}
