public DataTable ExecuteSQL(string execSql)
{
string sql = execSql;
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql);
return(DataProvider.Instance.GetTable(_Loginer.DBName, cmd.SqlCommand, tb_WMS_Task.__TableName));
}
public DataTable GetTask(string conditional)
{
string sql = "select * from " + tb_WMS_Task.__TableName + " Where ISDELETED=0 And " + conditional;
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql);
return(DataProvider.Instance.GetTable(_Loginer.DBName, cmd.SqlCommand, tb_WMS_Task.__TableName));
}
public DataTable SearchBy(string CustomerFrom, string CustomerTo, string Name, string Attribute)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM tb_Customer WHERE 1=1 ");
if (String.IsNullOrEmpty(CustomerFrom) == false)
{
sb.Append(" AND CustomerCode>='" + CustomerFrom + "'");
}
if (String.IsNullOrEmpty(CustomerTo) == false)
{
sb.Append(" AND CustomerCode<='" + CustomerTo + "'");
}
if (String.IsNullOrEmpty(Name) == false)
{
sb.Append(" AND (NativeName LIKE '%" + Name + "%' OR NativeName LIKE '%" + Name + "%')");
}
if (String.IsNullOrEmpty(Attribute) == false)
{
sb.Append(" AND CHARINDEX('," + Attribute + ",',AttributeCodes)>0 ");
}
sb.Append(" ORDER BY CustomerCode ");
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sb.ToString());
return(DataProvider.Instance.GetTable(_Loginer.DBName, cmd.SqlCommand, tb_Customer.__TableName));
}
/// <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 GetTaskDtlByTaskId(int TaskId)
{
string sql = "select * from " + tb_WMS_TaskDtl.__TableName + " where TaskDtl_TaskId= " + TaskId;
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql);
return(DataProvider.Instance.GetTable(_Loginer.DBName, cmd.SqlCommand, tb_WMS_TaskDtl.__TableName));
}
public DataTable SearchBy(string code, string Name, string type_CFG)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM [SUPPLIERCUSTOMER] WHERE ISDELETED=0 ");
if (String.IsNullOrEmpty(code) == false)
{
sb.Append(" AND CODE ='" + code + "'");
}
if (String.IsNullOrEmpty(Name) == false)
{
sb.Append(" AND Name LIKE '%" + Name + "%'");
}
if (String.IsNullOrEmpty(type_CFG) == false)
{
sb.Append(" AND type_CFG=" + type_CFG);
}
sb.Append(" ORDER BY CODE ");
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sb.ToString());
return(DataProvider.Instance.GetTable(_Loginer.DBName, cmd.SqlCommand, tb_SUPPLIERCUSTOMER.__TableName));
}
/// <summary>
/// 创建业务单据的版本历史记录
/// </summary>
/// <param name="key">单据号码</param>
/// <param name="account">当前用户</param>
/// <param name="newVerNo">返回新的版本号</param>
public void CreateBusinessLog(string key, string account, out string newVerNo)
{
string sql = "sp_Log_WMS_BillDtl'" + key + "','" + account + "'";
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql);
object o = DataProvider.Instance.ExecuteScalar(_Loginer.DBName, cmd.SqlCommand);
newVerNo = ConvertEx.ToString(o);
}
/// <summary>
/// 获取指定主键的数据
/// </summary>
/// <param name="key">主键</param>
/// <returns></returns>
public virtual DataTable GetDataByKey(string key)
{
string sql = string.Format("SELECT * FROM [{0}] WHERE [{1}]=@KEY", _TableName, _KeyName);
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql);
cmd.AddParam("@KEY", SqlDbType.VarChar, key);
return(DataProvider.Instance.GetTable(_Loginer.DBName, cmd.SqlCommand, _TableName));
}
/// <summary>
/// 检查数据是否存在
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="Condition">条件</param>
/// <returns></returns>
public virtual bool CheckNoExists(string TableName, string SqlCondition)
{
string sql = string.Format("SELECT COUNT(*) C FROM [{0}] WHERE ISDELETED=0 {1}", TableName, SqlCondition);
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql);
//cmd.AddParam("@KEY", SqlDbType.VarChar, SqlCondition);
object o = DataProvider.Instance.ExecuteScalar(_Loginer.DBName, cmd.SqlCommand);
return(ConvertEx.ToInt(o) > 0);
}
/// <summary>
/// 删除数据类型
/// </summary>
/// <param name="code">数据类型编号</param>
/// <returns></returns>
public bool DeleteCommonType(int code)
{
string sql = "DELETE tb_CommDataDictType WHERE DataType=@DataType";
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql);
cmd.AddParam("@DataType", SqlDbType.Int, code);
int i = DataProvider.Instance.ExecuteNoQuery(_Loginer.DBName, cmd.SqlCommand);
return(i > 0);
}
public virtual void ClearAppInfo(string keyValue)
{
string sql = "UPDATE [{0}] SET FlagApp=null,AppUser=null,AppDate=null WHERE {1}=@DocNo";
sql = string.Format(sql, _SummaryTableName, _SummaryKeyName);
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql);
cmd.AddParam("@DocNo", SqlDbType.VarChar, keyValue);
int i = DataProvider.Instance.ExecuteNoQuery(_Loginer.DBName, cmd.SqlCommand);
}
/// <summary>
/// 删除一条记录
/// </summary>
/// <param name="keyValue">主键</param>
/// <returns></returns>
public virtual bool Delete(string keyValue)
{
string sql = string.Format("Delete [{0}] where [{1}]=@KEY", _TableName, _KeyName);
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql);
cmd.AddParam("@KEY", SqlDbType.VarChar, keyValue);
int i = DataProvider.Instance.ExecuteNoQuery(_Loginer.DBName, cmd.SqlCommand);
return(i != 0);
}
/// <summary>
/// 检查数据是否存在
/// </summary>
/// <param name="keyValue">主键</param>
/// <returns></returns>
public virtual bool CheckNoExists(string keyValue)
{
string sql = string.Format("SELECT COUNT(*) C FROM [{0}] WHERE [{1}]=@KEY", _TableName, _KeyName);
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql);
cmd.AddParam("@KEY", SqlDbType.VarChar, keyValue);
object o = DataProvider.Instance.ExecuteScalar(_Loginer.DBName, cmd.SqlCommand);
return(ConvertEx.ToInt(o) > 0);
}
/// <summary>
/// 增加一项数据类型
/// </summary>
/// <param name="code">类型编号</param>
/// <param name="name">类型名称</param>
/// <returns></returns>
public bool AddCommonType(int code, string name)
{
string sql = "INSERT INTO tb_CommDataDictType (DataType,TypeName) VALUES (@DataType,@TypeName)";
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql);
cmd.AddParam("@DataType", SqlDbType.Int, code);
cmd.AddParam("@TypeName", SqlDbType.NVarChar, name);
int i = DataProvider.Instance.ExecuteNoQuery(_Loginer.DBName, cmd.SqlCommand);
return(i > 0);
}
/// <summary>
///删除一张单据:只删除明细,主表金额清零!!!
/// </summary>
public bool Delete(string docNo)
{
string sql1 = "DELETE [WMS_BillIn] where [" + tb_WMS_BillIn.__KeyName + "]=@DocNo1 ";
string sql2 = "DELETE [WMS_BillInDtl] where [" + tb_WMS_BillInDtl.__KeyName + "]=@DocNo2 ";
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql1 + sql2);
cmd.AddParam("@DocNo1", SqlDbType.VarChar, docNo.Trim());
cmd.AddParam("@DocNo2", SqlDbType.VarChar, docNo.Trim());
int i = DataProvider.Instance.ExecuteNoQuery(_Loginer.DBName, cmd.SqlCommand);
return(i != 0);
}
/// <summary>
/// 单据审核/批准功能
/// </summary>
/// <param name="keyValue">业务主键</param>
/// <param name="flagApp">审核标记:Y/N</param>
/// <param name="appUser">审核人</param>
/// <param name="appDate">审核日期</param>
public virtual void ApprovalBusiness(string keyValue, string flagApp, string appUser, DateTime appDate)
{
string sql = "UPDATE {0} SET FlagApp=@FlagApp,AppUser=@AppUser,AppDate=@AppDate WHERE {1}=@DocNo";
sql = string.Format(sql, _SummaryTableName, _SummaryKeyName);
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql);
cmd.AddParam("@FlagApp", SqlDbType.VarChar, flagApp);
cmd.AddParam("@AppUser", SqlDbType.VarChar, appUser);
cmd.AddParam("@AppDate", SqlDbType.DateTime, appDate);
cmd.AddParam("@DocNo", SqlDbType.VarChar, keyValue);
int i = DataProvider.Instance.ExecuteNoQuery(_Loginer.DBName, cmd.SqlCommand);
}
/// <summary>
///删除一张单据:只删除明细,主表金额清零!!!
/// </summary>
public bool Delete(string docNo)
{
//删除单据:只删除明细,主表金额清零!!!
string sql1 = "UPDATE [tb_WMS_BillDtl] SET Amount=0 where [" + tb_WMS_BillDtl.__KeyName + "]=@DocNo1 ";
string sql2 = "delete [tb_WMS_BillDtl] where [" + tb_WMS_BillDtl.__KeyName + "]=@DocNo2 ";
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql1 + sql2);
cmd.AddParam("@DocNo1", SqlDbType.VarChar, docNo.Trim());
cmd.AddParam("@DocNo2", SqlDbType.VarChar, docNo.Trim());
int i = DataProvider.Instance.ExecuteNoQuery(_Loginer.DBName, cmd.SqlCommand);
return(i != 0);
}
/// <summary>
/// 获取一张业务单据的数据
/// </summary>
/// <param name="docNo">单据号码</param>
/// <returns></returns>
public System.Data.DataSet GetBusinessByKey(string docNo)
{
string sql1 = "select a.*,b.NativeName AS SupplierName from tb_AP a LEFT JOIN tb_Customer b ON a.SupplierCode=b.CustomerCode where [APNO]=@DocNo1 ";
string sql2 = " select * from [vw_APs] where [" + tb_AP.__KeyName + "]=@DocNo2 ORDER BY Queue "; //明细表排序
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql1 + sql2);
cmd.AddParam("@DocNo1", SqlDbType.VarChar, docNo.Trim());
cmd.AddParam("@DocNo2", SqlDbType.VarChar, docNo.Trim());
DataSet ds = DataProvider.Instance.GetDataSet(_Loginer.DBName, cmd.SqlCommand);
ds.Tables[0].TableName = tb_AP.__TableName;
ds.Tables[1].TableName = tb_APs.__TableName;//明细表
return(ds);
}
/// <summary>
/// 获取一张业务单据的数据
/// </summary>
/// <param name="docNo">单据号码</param>
/// <returns></returns>
public System.Data.DataSet GetBusinessByKey(string docNo)
{
string sql1 = " select * from [tb_WMS_BillDtl] where [" + tb_WMS_BillDtl.__KeyName + "]=@DocNo1 ";
string sql2 = " select * from [vw_WMS_BillDtls] where [" + tb_WMS_BillDtl.__KeyName + "]=@DocNo2 ORDER BY Queue "; //明细表排序
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql1 + sql2);
cmd.AddParam("@DocNo1", SqlDbType.VarChar, docNo.Trim());
cmd.AddParam("@DocNo2", SqlDbType.VarChar, docNo.Trim());
DataSet ds = DataProvider.Instance.GetDataSet(_Loginer.DBName, cmd.SqlCommand);
ds.Tables[0].TableName = tb_WMS_BillDtl.__TableName;
//ds.Tables[1].TableName =tb_WMS_BillDtls.__TableName;//明细表
return(ds);
}
/// <summary>
/// 删除单据方法. 单据不能全部删除否则单号会断号. 将主表的金额清零并删除所有明细数据。
/// </summary>
public bool Delete(string INNO)
{
//删除单据:只删除明细及主表关键信息!!!
string sql1 = "UPDATE [tb_IN] SET FlagApp='N' where [INNO]=@INNO ";
string sql2 = "delete [tb_INs] where [INNO]=@INNO_1 ";
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql1 + sql2);
cmd.AddParam("@INNO", SqlDbType.VarChar, INNO.Trim());
cmd.AddParam("@INNO_1", SqlDbType.VarChar, INNO.Trim());
int i = DataProvider.Instance.ExecuteNoQuery(_Loginer.DBName, cmd.SqlCommand);
return(i != 0);
}
/// <summary>
/// 删除单据
/// </summary>
/// <param name="takeNo">单据号码</param>
/// <returns></returns>
public bool Delete(string docNo)
{
//删除单据:只删除明细,主表金额清零!!!
string sql1 = "UPDATE [tb_SO] SET Amount=0 where [SONO]=@DocNo1 ";
string sql2 = "delete [tb_SOs] where [SONO]=@DocNo2 ";
string sql3 = "delete [tb_AttachFile] where [DocID]=@DocNo3 ";
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql1 + sql2 + sql3);
cmd.AddParam("@DocNo1", SqlDbType.VarChar, docNo.Trim());
cmd.AddParam("@DocNo2", SqlDbType.VarChar, docNo.Trim());
cmd.AddParam("@DocNo3", SqlDbType.VarChar, docNo.Trim());
int i = DataProvider.Instance.ExecuteNoQuery(_Loginer.DBName, cmd.SqlCommand);
return(i != 0);
}
/// <summary>
/// 获取单据
/// </summary>
public System.Data.DataSet GetBusinessByKey(string INNO)
{
string sql1 = "select a.*,b.NativeName AS SupplierName from tb_IN a LEFT JOIN tb_Customer b ON a.SupplierCode=b.CustomerCode where [INNO]=@INNO ";
string sql2 = " select * from [vw_INs] where [INNO]=@INNO1 order by ProductCode ";
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql1 + sql2);
cmd.AddParam("@INNO", SqlDbType.VarChar, INNO.Trim());
cmd.AddParam("@INNO1", SqlDbType.VarChar, INNO.Trim());
DataSet ds = DataProvider.Instance.GetDataSet(_Loginer.DBName, cmd.SqlCommand);
ds.Tables[0].TableName = tb_IN.__TableName;
ds.Tables[1].TableName = tb_INs.__TableName;
return(ds);
}
/// <summary>
/// 获取单据
/// </summary>
public System.Data.DataSet GetBusinessByKey(string ICNO)
{
string sql1 = " select * from [tb_IC] where [ICNO]=@ICNO ";
string sql2 = " select * from [vw_ICs] where [ICNO]=@ICNO1 order by ProductCode ";
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql1 + sql2);
cmd.AddParam("@ICNO", SqlDbType.VarChar, ICNO.Trim());
cmd.AddParam("@ICNO1", SqlDbType.VarChar, ICNO.Trim());
DataSet ds = DataProvider.Instance.GetDataSet(_Loginer.DBName, cmd.SqlCommand);
ds.Tables[0].TableName = tb_IC.__TableName;
ds.Tables[1].TableName = tb_ICs.__TableName;
return(ds);
}
///// <summary>
///// 数据字典：手动控制事务及自动生成流水号
///// </summary>
///// <param name="data">用户提交的数据</param>
///// <returns></returns>
//public override SaveResultEx UpdateEx(DataSet data)
//{
// SaveResultEx result = new SaveResultEx((int)ResultID.SUCCESS, "");
// try
// {
// this.BeginTransaction();//启动事务
// DataTable summary = data.Tables[tb_CommonDataDict.__TableName];//取出主表数据
// string dataType = ConvertEx.ToString(summary.Rows[0][tb_CommonDataDict.DataType]);//取数据类型
// string dataCode = DocNoTool.GetDataSN(_CurrentTrans, dataType, true);//在同一事务内取流水号
// summary.Rows[0][tb_CommonDataDict.DataCode] = dataCode;
// result = base.UpdateEx(data);//提交数据
// result.PrimaryKey = dataCode;//返回自动生成的主键
// this.CommitTransaction();//提交事务
// }
// catch
// {
// this.RollbackTransaction();//回滚
// }
// return result;
//}
/// <summary>
/// 搜索指定类型的数据
/// </summary>
/// <param name="DataType">数据类型</param>
/// <returns></returns>
public DataTable SearchBy(string DataType)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM CONFIG_DETAIL WHERE 1=1 ");
if (DataType != "")
{
sb.Append("AND ISDELETED=0 AND CONFIG_HEADERID =" + DataType);
}
sb.Append(" ORDER BY [SEQUENCE] ");
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sb.ToString());
return(DataProvider.Instance.GetTable(_Loginer.DBName, cmd.SqlCommand, tb_CONFIG_DETAIL.__TableName));
}
/// <summary>
/// 搜索指定类型的数据
/// </summary>
/// <param name="DataType">数据类型</param>
/// <returns></returns>
public DataTable SearchBy(int DataType)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM tb_CommonDataDict WHERE 1=1 ");
if (DataType > 0)
{
sb.Append(" AND DataType=" + DataType.ToString());
}
sb.Append(" ORDER BY DataType,DataCode ");
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sb.ToString());
return(DataProvider.Instance.GetTable(_Loginer.DBName, cmd.SqlCommand, tb_CommonDataDict.__TableName));
}
/// <summary>
/// 获取一张业务单据的数据
/// </summary>
/// <param name="docNo">单据号码</param>
/// <returns></returns>
public System.Data.DataSet GetBusinessByKey(string docNo)
{
string sql1 = " select a.*,b.NativeName AS CustomerNativeName from [tb_SO] a LEFT JOIN tb_Customer b ON a.CustomerCode=b.CustomerCode where [SONO]=@DocNo1 ";
string sql2 = " select * from [vw_SOs] where [SONO]=@DocNo2 ORDER BY Queue "; //明细表排序
string sql3 = " select * from [tb_AttachFile] where [DocID] =@DocNo3 ";
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(sql1 + sql2 + sql3);
cmd.AddParam("@DocNo1", SqlDbType.VarChar, docNo.Trim());
cmd.AddParam("@DocNo2", SqlDbType.VarChar, docNo.Trim());
cmd.AddParam("@DocNo3", SqlDbType.VarChar, docNo.Trim());
DataSet ds = DataProvider.Instance.GetDataSet(_Loginer.DBName, cmd.SqlCommand);
ds.Tables[0].TableName = tb_SO.__TableName;
ds.Tables[1].TableName = tb_SOs.__TableName;
ds.Tables[2].TableName = tb_AttachFile.__TableName;
return(ds);
}
/// <summary>
/// 查询功能，获取主表数据
/// </summary>
public DataTable GetSummaryByParam(string docNoFrom, string docNoTo, DateTime docDateFrom, DateTime docDateTo)
{
StringBuilder sql = new StringBuilder();
//
//在这里生成SQL
//
if (sql.ToString() != "") //有查询条件
{
string query = "select * from " + _SummaryTableName + " where 1=1 " + sql.ToString();
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(query);
DataTable dt = DataProvider.Instance.GetTable(_Loginer.DBName, cmd.SqlCommand, tb_WMS_BillDtl.__TableName);
return(dt);
}
else //无查询条件不返回数据
{
throw new Exception("没有指定查询条件!");
return(null);
}
}
/// <summary>
/// 查询功能，获取主表数据
/// </summary>
public DataTable GetSummaryByParam(string Condition)
{
StringBuilder sql = new StringBuilder();
//
sql = sql.Append(Condition);
//
if (sql.ToString() != "") //有查询条件
{
string query = "select * from " + _SummaryTableName + " where ISDELETED=0 " + sql.ToString();
SqlCommandBase cmd = SqlBuilder.BuildSqlCommandBase(query);
DataTable dt = DataProvider.Instance.GetTable(_Loginer.DBName, cmd.SqlCommand, tb_WMS_BillIn.__TableName);
return(dt);
}
else //无查询条件不返回数据
{
throw new Exception("没有指定查询条件!");
//return null;
}
}
/// <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);
}
}
