/// <summary>
/// 按条件检索数据
/// </summary>
/// <param name="customerID"></param>
/// <param name="customerName"></param>
/// <param name="zjm"></param>
/// <returns></returns>
public DataTable search(string customerID, string customerName, string zjm)
{
string sql = "SELECT * FROM dbo.dt_Data_Customer WHERE 1=1 ";
SqlParameterProvider p = new SqlParameterProvider();
StringBuilder where = new StringBuilder();
if (!String.IsNullOrEmpty(customerID))
{
where.Append(" AND CustomerID LIKE '%'+@CustomerID+'%'");
p.AddParameter("@CustomerID", SqlDbType.VarChar, 20, customerID);
}
if (!String.IsNullOrEmpty(customerName))
{
where.Append(" AND CustomerName LIKE '%'+ @CustomerName+'%'");
p.AddParameter("@CustomerName", SqlDbType.VarChar, 20, customerName);
}
if (!String.IsNullOrEmpty(zjm))
{
where.Append(" AND ZJM LIKE '%'+@ZJM+'%'");
p.AddParameter("@ZJM", SqlDbType.VarChar, 20, zjm);
}
//if (where.Length > 0)//去掉第一个AND
// where.Remove(0, 4);
return(dal.DBHelper.GetTable(sql + where.ToString(), dt_Data_Customer._TableName, p));
}
public DataTable Search(string productID, string material, string productNmae, string zjm)
{
string sql = "SELECT * FROM dbo.dt_Data_Product WHERE 1=1 ";
StringBuilder where = new StringBuilder();
SqlParameterProvider p = new SqlParameterProvider();
if (!String.IsNullOrEmpty(productID))
{
where.Append(" AND ProductID LIKE '%'+@ProductID+'%' ");
p.AddParameter("@ProductID", SqlDbType.VarChar, 20, productID);
}
if (!String.IsNullOrEmpty(material))
{
where.Append(" AND Material = @Material ");
p.AddParameter("@Material", SqlDbType.VarChar, 20, material);
}
if (!String.IsNullOrEmpty(productNmae))
{
where.Append(" AND ProductName LIKE '%'+@ProductName+'%' ");
p.AddParameter("@ProductName", SqlDbType.VarChar, 20, productNmae);
}
if (!String.IsNullOrEmpty(zjm))
{
where.Append(" AND ZJM LIKE '%'+@ZJM+'%' ");
p.AddParameter("@ZJM", SqlDbType.VarChar, 20, zjm);
}
return(dal.DBHelper.GetTable(sql + where.ToString(), Models.Business.dt_Data_Product._TableName, p));
}
private bool AddDocSNRule(IDatabase db)
{
string sql = "INSERT INTO sys_DataSN(DocCode,DocName,DocHeader,Separate,DocType,[Length]) VALUES(@DocCode,@DocName,@DocHeader,@Seperate,@DocType,@Length)";
SqlParameterProvider p = new SqlParameterProvider();
p.AddParameter("@DocCode", SqlDbType.VarChar, DocSNProvider.DocCode);
p.AddParameter("@DocName", SqlDbType.VarChar, DocSNProvider.DocName);
p.AddParameter("@DocHeader", SqlDbType.VarChar, DocSNProvider.DocHeader ?? "");
p.AddParameter("@Seperate", SqlDbType.VarChar, DocSNProvider.Separate ?? "");
p.AddParameter("@DocType", SqlDbType.VarChar, DocSNProvider.DocRule);
p.AddParameter("@Length", SqlDbType.Int, DocSNProvider.Length);
return(db.ExecuteNonQuery(sql, p) > 0);
}
private static ISqlRuntimeConfigurationProvider BuildRuntimeConfigurationProvider()
{
IEnumerable <ISqlResourceConfiguration> resourceConfigurations =
ResourceConfigurationBuilder.Build <ISqlPropertyConfiguration, ISqlResourceConfiguration, ISqlModelConfiguration>(typeof(SqlResourceConfigurationBuilder <>));
// No need to register these with the DI container, as they are only used during startup
ISqlParamaterProvider sqlParamaterProvider = new SqlParameterProvider();
ISqlGenerator generator = new SqlGenerator(sqlParamaterProvider);
ISqlExpressionBuilder sqlExpressionBuilder = new SqlExpressionBuilder(sqlParamaterProvider, new SqlClassProvider());
Dictionary <Type, ISqlRuntimeConfiguration <IRestResource> > runtimeConfigurations = new Dictionary <Type, ISqlRuntimeConfiguration <IRestResource> >();
foreach (ISqlResourceConfiguration configuration in resourceConfigurations)
{
string selectAll = generator.SelectAll(configuration);
string selectById = generator.SelectById(configuration);
string deleteById = generator.DeleteById(configuration);
string updateById = generator.Update(configuration);
string insert = generator.Insert(configuration);
var createObjectRelationalMapFunc = typeof(ISqlExpressionBuilder)
.GetMethod(nameof(ISqlExpressionBuilder.CreateObjectRelationalMap))
.MakeGenericMethod(configuration.ResourceType)
.Invoke(sqlExpressionBuilder, new[] { configuration }) as Func <IDatabaseResultReader, IRestResource>;
var getPrimaryKeySqlParameterFunc = sqlExpressionBuilder.GetPrimaryKeySqlParameter(configuration.PrimaryIdentifier);
var getSqlParametersForCreationFunc = sqlExpressionBuilder.GetSqlParametersForCreation(configuration);
var getSqlParametersForUpdatingFunc = sqlExpressionBuilder.GetSqlParametersForUpdating(configuration);
var runtimeConfiguration = Activator.CreateInstance(
typeof(SqlRuntimeConfiguration <>).MakeGenericType(configuration.ResourceType),
configuration,
createObjectRelationalMapFunc,
getPrimaryKeySqlParameterFunc,
getSqlParametersForCreationFunc,
getSqlParametersForUpdatingFunc,
selectAll,
selectById,
deleteById,
insert,
updateById) as ISqlRuntimeConfiguration <IRestResource>;
runtimeConfigurations.Add(configuration.ResourceType, runtimeConfiguration);
}
return(new SqlRuntimeConfigurationProvider(runtimeConfigurations));
}
public override DataSet DoGetDocData(string DocNo)
{
string sql = "SELECT* FROM dbo.tb_SamplePO WHERE PONO = @PONO; " +
"SELECT* FROM dbo.tb_SamplePODetail WHERE PONO = @PONO";
SqlParameterProvider p = new SqlParameterProvider();
p.AddParameter("@PONO", SqlDbType.VarChar, 20, DocNo);
DataSet ds = dal.DBHelper.GetDataSet(sql, p);
ds.Tables[0].TableName = Models.tb_SamplePO._TableName;
ds.Tables[1].TableName = Models.tb_SamplePODetail._TableName;
ds.Tables[0].Columns["isid"].AutoIncrement = true; //设置该列为自增长，
ds.Tables[0].Columns["isid"].AutoIncrementSeed = -1; //新增列的初始值。
ds.Tables[0].Columns["isid"].AutoIncrementStep = -1; //列的值自动递增的数值。默认为 1。
ds.Tables[1].Columns["isid"].AutoIncrement = true; //设置该列为自增长，
ds.Tables[1].Columns["isid"].AutoIncrementSeed = -1; //新增列的初始值。
ds.Tables[1].Columns["isid"].AutoIncrementStep = -1; //列的值自动递增的数值。默认为 1。
return(ds);
}
public string DoGetDocSN(IDatabase db, ModelDocNo Model)
{
SqlParameterProvider p = new SqlParameterProvider();
p.AddParameter("@DocCode", SqlDbType.VarChar, 50, Model.DocCode);
string sql = "SELECT * FROM sys_DataSN WHERE DocCode=@DocCode";
DataTable dt = db.GetTable(sql, "", p);
string DocHeader = "";
string DocType = "";
string Separate = "";
int Length = 0;
if (dt.Rows.Count > 0)
{
DocHeader = ConvertLib.ToString(dt.Rows[0][sys_DataSN.DocHeader]);
DocType = ConvertLib.ToString(dt.Rows[0][sys_DataSN.DocType]);
Separate = ConvertLib.ToString(dt.Rows[0][sys_DataSN.Separate]);
Length = ConvertLib.ToInt(dt.Rows[0][sys_DataSN.Length]);
}
else
{
AddDocSNRule(db, Model);
DocHeader = Model.DocHeader;
DocType = Model.DocRule;
Separate = Model.Separate;
Length = Model.Length;
}
string DocSeed = "";
switch (DocType.ToUpper())
{
case "YEAR":
DocSeed = DateTime.Now.Year.ToString();
DocHeader = DocHeader + DocSeed;
break;
case "YEAR-MONTH":
DocSeed = DateTime.Now.ToString("yyyyMM");
DocHeader = DocHeader + DocSeed;
break;
case "YEAR-MONTH-DD":
DocSeed = DateTime.Now.ToString("yyyyMMdd");
DocHeader = DocHeader + DocSeed;
break;
case "UP":
DocSeed = Model.DocCode;
break;
case "CUSTOMER":
DocSeed = Model.CustomerSeed;
break;
}
int snindex = 0;
string sql2 = "SELECT * FROM sys_DataSNDetail WHERE DocCode=@DocCode AND Seed=@Seed";
SqlParameterProvider p2 = new SqlParameterProvider();
p2.AddParameter("@DocCode", SqlDbType.VarChar, 50, Model.DocCode);
p2.AddParameter("@Seed", SqlDbType.VarChar, 50, DocSeed);
DataTable datasn = db.GetTable(sql2, "t", p2);
if (datasn.Rows.Count > 0)
{
snindex = ConvertLib.ToInt(datasn.Rows[0]["MaxID"]);
snindex = ValidateSN(snindex + 1);
string sql3 = "UPDATE sys_DataSNDetail SET MaxID=@Value WHERE DocCode=@DocCode AND Seed=@Seed";
SqlParameterProvider p3 = new SqlParameterProvider();
p3.AddParameter("@Value", SqlDbType.Int, snindex);
p3.AddParameter("@DocCode", SqlDbType.VarChar, 50, Model.DocCode);
p3.AddParameter("@Seed", SqlDbType.VarChar, 50, DocSeed);
db.ExecuteNonQuery(sql3, p3);
}
else
{
snindex = ValidateSN(1);
string sql4 = "INSERT INTO sys_DataSNDetail(DocCode,Seed,MaxID) VALUES(@DocCode, @Seed, @Value)";
SqlParameterProvider p4 = new SqlParameterProvider();
p4.AddParameter("@Value", SqlDbType.Int, snindex);
p4.AddParameter("@DocCode", SqlDbType.VarChar, 50, Model.DocCode);
p4.AddParameter("@Seed", SqlDbType.VarChar, 50, DocSeed);
db.ExecuteNonQuery(sql4, p4);
}
string snvalue = snindex.ToString().PadLeft(Length, '0');
if (DocType.ToUpper() == "CUSTOMER")
{
return(DocHeader + snvalue);
}
else
{
return(DocHeader + Separate + snvalue);
}
}
