/// <summary>
/// 按条件从申购单主表获取数据
/// </summary>
public System.Data.DataTable GetSummaryByParam(string INNOFrom, string INNOTo, DateTime DocDateFrom, DateTime DocDateTo)
{
StringBuilder sql = new StringBuilder();
//或者调用存储过程 usp_QuerySO @
if (INNOFrom != string.Empty)
{
sql.Append(" and " + tb_IN.INNO + ">='" + INNOFrom + "'");
}
if (INNOTo != string.Empty)
{
sql.Append(" and " + tb_IN.INNO + "<='" + INNOTo + "'");
}
if (DocDateFrom > DateTime.MinValue)
{
sql.Append(" and Convert(varchar," + tb_IN.DocDate + ",112) >='" + ConvertEx.ToCharYYYYMMDD(DocDateFrom) + "'");
}
if (DocDateTo > DateTime.MinValue)
{
sql.Append(" and Convert(varchar," + tb_IN.DocDate + ",112) <='" + ConvertEx.ToCharYYYYMMDD(DocDateTo) + "'");
}
if (sql.ToString() != "") //有查询条件
{
string query = "select a.*,b.NativeName AS SupplierName from tb_IN a LEFT JOIN tb_Customer b ON a.SupplierCode=b.CustomerCode where 1=1 " + sql.ToString();
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(query);
DataTable dt = DataProvider.Instance.GetTable(_Loginer.DBName, cmd.SqlCommand, tb_IN.__TableName);
return(dt);
}
else //无查询条件不返回数据
{
throw new Exception("没有指定查询条件!");
}
}
public DataTable SearchOutstanding(string invoiceNo, string customer, DateTime dateFrom, DateTime dateTo, DateTime dateEnd, string outstandingType)
{
//outstandingType:AR/AP/ALL
if (outstandingType == "AR")
{
SqlProcedure sp = SqlBuilder.BuildSqlProcedure("sp_QueryOutstandingAR");
sp.AddParam("@InvoiceNo", SqlDbType.VarChar, 20, invoiceNo);
sp.AddParam("@CustomerCode", SqlDbType.VarChar, 20, customer);
sp.AddParam("@FromDate", SqlDbType.VarChar, 8, ConvertEx.ToCharYYYYMMDD(dateFrom));
sp.AddParam("@ToDate", SqlDbType.VarChar, 8, ConvertEx.ToCharYYYYMMDD(dateTo));
sp.AddParam("@EndDate", SqlDbType.VarChar, 8, ConvertEx.ToCharYYYYMMDD(dateEnd));
return(DataProvider.Instance.GetTable(_Loginer.DBName, sp.SqlCommand, "Outstanding" + outstandingType));
}
if (outstandingType == "AP")
{
SqlProcedure sp = SqlBuilder.BuildSqlProcedure("sp_QueryOutstandingAP");
sp.AddParam("@InvoiceNo", SqlDbType.VarChar, 20, invoiceNo);
sp.AddParam("@SupplierCode", SqlDbType.VarChar, 20, customer);
sp.AddParam("@FromDate", SqlDbType.VarChar, 8, ConvertEx.ToCharYYYYMMDD(dateFrom));
sp.AddParam("@ToDate", SqlDbType.VarChar, 8, ConvertEx.ToCharYYYYMMDD(dateTo));
sp.AddParam("@EndDate", SqlDbType.VarChar, 8, ConvertEx.ToCharYYYYMMDD(dateEnd));
return(DataProvider.Instance.GetTable(_Loginer.DBName, sp.SqlCommand, "Outstanding" + outstandingType));
}
return(null);
}
/// <summary>
/// 搜索系统日志数据
/// </summary>
/// <param name="logUser">用户</param>
/// <param name="tableName">数据表名</param>
/// <param name="dateFrom">日志日期：由</param>
/// <param name="dateTo">日志日期：至</param>
/// <returns></returns>
public DataSet SearchLog(string logUser, string tableName, DateTime dateFrom, DateTime dateTo)
{
SqlCommandBase cmd = SqlBuilder.BuildSqlProcedure("sp_SearchLog");
cmd.AddParam("@LogUser", SqlDbType.VarChar, logUser);
cmd.AddParam("@TableName", SqlDbType.VarChar, tableName);
cmd.AddParam("@LogDateFrom", SqlDbType.VarChar, ConvertEx.ToCharYYYYMMDD(dateFrom));
cmd.AddParam("@LogDateTo", SqlDbType.VarChar, ConvertEx.ToCharYYYYMMDD(dateTo));
return(DataProvider.Instance.GetDataSet(_Loginer.DBName, cmd.SqlCommand));
}
public DataSet GetReportData_Checklist(string DocNoFrom, string DocNoTo, DateTime DateFrom, DateTime DateTo)
{
SqlProcedure sp = SqlBuilder.BuildSqlProcedure("sp_RptAP_Checklist");
sp.AddParam("@DocNoFrom", SqlDbType.VarChar, DocNoFrom.Trim());
sp.AddParam("@DocNoTo", SqlDbType.VarChar, DocNoTo.Trim());
sp.AddParam("@DateFrom", SqlDbType.VarChar, ConvertEx.ToCharYYYYMMDD(DateFrom));
sp.AddParam("@DateTo", SqlDbType.VarChar, ConvertEx.ToCharYYYYMMDD(DateTo));
DataSet ds = DataProvider.Instance.GetDataSet(_Loginer.DBName, sp.SqlCommand);
ds.Tables[0].TableName = tb_AP.__TableName;
return(ds);
}
/// <summary>
/// 按条件从申购单主表获取数据
/// </summary>
public System.Data.DataTable GetSummaryByParam(string ICNOFrom, string ICNOTo, DateTime DocDateFrom, DateTime DocDateTo)
{
StringBuilder sql = new StringBuilder();
if (ICNOFrom != string.Empty)
{
sql.Append(" and " + tb_IC.ICNO + ">=@ICNOFrom ");
}
if (ICNOTo != string.Empty)
{
sql.Append(" and " + tb_IC.ICNO + "=@ICNOTo ");
}
if (DocDateFrom > DateTime.MinValue)
{
sql.Append(" and Convert(varchar," + tb_IC.CreationDate + ",112) >=");
sql.Append("'" + ConvertEx.ToCharYYYYMMDD(DocDateFrom) + "'");
}
if (DocDateTo > DateTime.MinValue)
{
sql.Append(" and Convert(varchar," + tb_IC.CreationDate + ",112) <=");
sql.Append("'" + ConvertEx.ToCharYYYYMMDD(DocDateTo) + "'");
}
SqlCommandBase cmd;
if (sql.ToString() != "")
{
cmd = SqlBuilder.BuildSqlCommandBase("select * from " + tb_IC.__TableName + " where 1=1 " + sql.ToString());
if (ICNOFrom != string.Empty)
{
cmd.AddParam("@ICNOFrom", SqlDbType.VarChar, ICNOFrom.Trim());
}
if (ICNOTo != string.Empty)
{
cmd.AddParam("@ICNOTo", SqlDbType.VarChar, ICNOTo.Trim());
}
}
else
{
cmd = SqlBuilder.BuildSqlCommandBase("select top 0 * from " + tb_IC.__TableName + " where 1=0 ");
}
DataTable dt = DataProvider.Instance.GetTable(_Loginer.DBName, cmd.SqlCommand, tb_IC.__TableName);
return(dt);
}
/// <summary>
/// 查询功能，获取主表数据
/// </summary>
public DataSet GetSummaryByParam(string docNoFrom, string docNoTo, DateTime docDateFrom, DateTime docDateTo, string StockCode, string Customer)
{
StringBuilder sql = new StringBuilder();
//
if (docNoFrom != string.Empty)
{
sql.Append(" and " + tb_PO.PONO + ">='" + docNoFrom + "'");
}
if (docNoTo != string.Empty)
{
sql.Append(" and " + tb_PO.PONO + "<='" + docNoTo + "'");
}
if (docDateFrom > DateTime.MinValue)
{
sql.Append(" and Convert(varchar," + tb_PO.PODate + ",112) >='" + ConvertEx.ToCharYYYYMMDD(docDateFrom) + "'");
}
if (docDateTo > DateTime.MinValue)
{
sql.Append(" and Convert(varchar," + tb_PO.PODate + ",112) <='" + ConvertEx.ToCharYYYYMMDD(docDateTo) + "'");
}
if (StockCode != string.Empty)
{
sql.Append(" and PONO IN (SELECT PONO FROM tb_POs WHERE 1=1 AND " + tb_POs.ProductCode + "='" + StockCode + "')");
}
if (Customer != string.Empty)
{
sql.Append(" and " + tb_PO.CustomerCode + "='" + Customer + "'");
}
if (sql.ToString() != "") //有查询条件
{
string query = "select a.*,b.NativeName AS CustomerName from tb_PO a LEFT JOIN tb_Customer b ON a.CustomerCode=b.CustomerCode where 1=1 " + sql.ToString();
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(query);
return(DataProvider.Instance.GetDataSet(_Loginer.DBName, cmd.SqlCommand));
}
else //无查询条件不返回数据
{
throw new Exception("没有指定查询条件!");
}
}
/// <summary>
/// 查询功能，获取主表数据
/// </summary>
public DataTable GetSummaryByParam(string docNoFrom, string docNoTo, DateTime docDateFrom, DateTime docDateTo)
{
StringBuilder sql = new StringBuilder();
if (docNoFrom != string.Empty)
{
sql.Append(" and " + tb_AR.ARNO + ">='" + docNoFrom + "'");
}
if (docNoTo != string.Empty)
{
sql.Append(" and " + tb_AR.ARNO + "<='" + docNoTo + "'");
}
if (docDateFrom > DateTime.MinValue)
{
sql.Append(" and Convert(varchar," + tb_AR.ReceivedDate + ",112) >='" + ConvertEx.ToCharYYYYMMDD(docDateFrom) + "'");
}
if (docDateTo > DateTime.MinValue)
{
sql.Append(" and Convert(varchar," + tb_AR.ReceivedDate + ",112) <='" + ConvertEx.ToCharYYYYMMDD(docDateTo) + "'");
}
if (sql.ToString() != "") //有查询条件
{
string query = "select a.*,b.NativeName AS CustomerName from tb_AR a LEFT JOIN tb_Customer b ON a.CustomerCode=b.CustomerCode where 1=1 " + sql.ToString();
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(query);
DataTable dt = DataProvider.Instance.GetTable(_Loginer.DBName, cmd.SqlCommand, tb_AR.__TableName);
return(dt);
}
else //无查询条件不返回数据
{
throw new Exception("没有指定查询条件!");
return(null);
}
}