public QC_HF_ChangeLogModelList GetModelList(string qualityCode)
{
QC_HF_ChangeLogTable table = new QC_HF_ChangeLogTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, table.AllColumns());
if (qualityCode != "")
{
sql.Where(table.QUALITY_CODE == qualityCode);
}
using (SafeDataReader sdr = new SafeDataReader(sql.ToDataReader()))
{
QC_HF_ChangeLogModelList result = new QC_HF_ChangeLogModelList();
while (sdr.Read())
{
QC_HF_ChangeLogModel m = new QC_HF_ChangeLogModel();
m.QualityCode = sdr.GetString(table.QUALITY_CODE);
m.QC_Ref_PPO_Old = sdr.GetString(table.QC_Ref_PPO_Old);
m.QC_Ref_GP_Old = sdr.GetString(table.QC_Ref_GP_Old);
m.HF_Ref_PPO_Old = sdr.GetString(table.HF_Ref_PPO_Old);
m.HF_Ref_GP_Old = sdr.GetString(table.HF_Ref_GP_Old);
m.QC_Ref_PPO_New = sdr.GetString(table.QC_Ref_PPO_New);
m.QC_Ref_GP_New = sdr.GetString(table.QC_Ref_GP_New);
m.HF_Ref_PPO_New = sdr.GetString(table.HF_Ref_PPO_New);
m.HF_Ref_GP_New = sdr.GetString(table.HF_Ref_GP_New);
m.CreateDate = sdr.GetDateTime(table.CREATE_DATE);
m.Creator = sdr.GetString(table.CREATOR);
result.Add(m);
}
return(result);
}
}
public QcfinishdtlModelList GetModelList(QcfinishdtlModel model)
{
QcfinishdtlTable table = new QcfinishdtlTable();
PbknitfinishTable finishTable = new PbknitfinishTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, table.AllColumns(), finishTable.FinishingName);
sql.Join(finishTable, table.FinishingCode == finishTable.FinishingCode);
if (model.QualityCode != "")
{
sql.Where(table.QualityCode == model.QualityCode);
}
using (SafeDataReader sdr = new SafeDataReader(sql.ToDataReader()))
{
QcfinishdtlModelList result = new QcfinishdtlModelList();
while (sdr.Read())
{
QcfinishdtlModel m = new QcfinishdtlModel();
m.Iden = sdr.GetInt32(table.Iden);
m.Description = sdr.GetString(finishTable.FinishingName);
m.QualityCode = sdr.GetString(table.QualityCode);
m.FinishingCode = sdr.GetString(table.FinishingCode);
result.Add(m);
}
return(result);
}
}
public QcyarndtlModelList GetModelList(QcyarndtlModel model)
{
QcyarndtlTable table = new QcyarndtlTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, table.AllColumns());
if (model.QualityCode != "")
{
sql.Where(table.QualityCode == model.QualityCode);
}
using (SafeDataReader sdr = new SafeDataReader(sql.ToDataReader()))
{
QcyarndtlModelList result = new QcyarndtlModelList();
while (sdr.Read())
{
QcyarndtlModel m = new QcyarndtlModel();
m.Iden = sdr.GetInt32(table.Iden);
m.QualityCode = sdr.GetString(table.QualityCode);
m.YarnType = sdr.GetString(table.YarnType);
m.YarnCount = sdr.GetString(table.YarnCount);
m.Threads = sdr.GetInt32(table.Threads);
m.YarnRatio = sdr.GetDecimal(table.YarnRatio);
m.YarnComponent = sdr.GetString(table.YarnComponent);
m.WarpWeft = sdr.GetString(table.WarpWeft);
m.YarnDensity = sdr.GetInt32(table.YarnDensity);
result.Add(m);
}
return(result);
}
}
public QccustomerlibraryModelList GetModelList(QccustomerlibraryModel model)
{
QccustomerlibraryTable table = new QccustomerlibraryTable();
QcmaininfoTable main = new QcmaininfoTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, table.AllColumns(), main.Status);
sql.Join(main, table.QualityCode == main.QualityCode);
if (model.QualityCode != "")
{
sql.Where(table.QualityCode == model.QualityCode);
}
using (SafeDataReader sdr = new SafeDataReader(sql.ToDataReader()))
{
QccustomerlibraryModelList result = new QccustomerlibraryModelList();
while (sdr.Read())
{
QccustomerlibraryModel m = new QccustomerlibraryModel();
m.Status = sdr.GetString(main.Status);
m.QualityCode = sdr.GetString(table.QualityCode);
m.BuyerId = sdr.GetString(table.BuyerId);
m.Brand = sdr.GetString(table.Brand);
m.CustomerQualityId = sdr.GetString(table.CustomerQualityId);
m.Sales = sdr.GetString(table.Sales);
m.SalesGroup = sdr.GetString(table.SalesGroup);
m.MillComments = sdr.GetString(table.MillComments);
m.IsFirstOwner = sdr.GetString(table.IsFirstOwner);
m.CreateDate = sdr.GetDateTime(table.CreateDate);
m.Creator = sdr.GetString(table.Creator);
result.Add(m);
}
return(result);
}
}
public QcavailablewidthModelList GetModelList(QcavailablewidthModel model)
{
QcavailablewidthTable table = new QcavailablewidthTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, table.AllColumns());
if (model.QualityCode != "")
{
sql.Where(table.QualityCode == model.QualityCode);
}
using (SafeDataReader sdr = new SafeDataReader(sql.ToDataReader()))
{
QcavailablewidthModelList result = new QcavailablewidthModelList();
while (sdr.Read())
{
QcavailablewidthModel m = new QcavailablewidthModel();
m.Iden = sdr.GetInt32(table.Iden);
m.QualityCode = sdr.GetString(table.QualityCode);
m.Gauge = sdr.GetInt32(table.Gauge);
m.Diameter = sdr.GetInt32(table.Diameter);
m.TotalNeedles = sdr.GetInt32(table.TotalNeedles);
m.Width = sdr.GetInt32(table.Width);
m.MaxWidth = sdr.GetInt32(table.MaxWidth);
m.UpdatedBy = sdr.GetString(table.UpdatedBy);
m.UpdatedTime = sdr.GetDateTime(table.UpdatedTime);
result.Add(m);
}
return(result);
}
}
protected bool Exists(PbknityarncountModel model, bool isNew)
{
PbknityarncountTable table = new PbknityarncountTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, QueryColumn.All().Count())
.Where(table.YarnCount == model.YarnCount &&
table.IsActive == model.IsActive
);
return(sql.ToScalar <int>() > 0);
}
protected bool Exists(QcfinishdtlModel model, bool isNew)
{
QcfinishdtlTable table = new QcfinishdtlTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, QueryColumn.All().Count())
.Where(table.Iden == model.Iden &&
table.QualityCode == model.QualityCode &&
table.FinishingCode == model.FinishingCode
);
return(sql.ToScalar <int>() > 0);
}
protected bool Exists(PbknitparameterModel model, bool isNew)
{
PbknitparameterTable table = new PbknitparameterTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, QueryColumn.All().Count())
.Where(table.Sort == model.Sort &&
table.Parameter == model.Parameter &&
table.IsActive == model.IsActive
);
return(sql.ToScalar <int>() > 0);
}
protected bool Exists(PbknitdyemethodModel model, bool isNew)
{
PbknitdyemethodTable table = new PbknitdyemethodTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, QueryColumn.All().Count())
.Where(table.DyeMethod == model.DyeMethod &&
table.DyeType == model.DyeType &&
table.Description == model.Description
);
return(sql.ToScalar <int>() > 0);
}
protected bool Exists(PbknitfinishModel model, bool isNew)
{
PbknitfinishTable table = new PbknitfinishTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, QueryColumn.All().Count())
.Where(table.FinishingCode == model.FinishingCode &&
table.FinishingName == model.FinishingName &&
table.Description == model.Description &&
table.WashingFlag == model.WashingFlag &&
table.IsActive == model.IsActive
);
return(sql.ToScalar <int>() > 0);
}
protected bool Exists(PbknityarntypeModel model, bool isNew)
{
PbknityarntypeTable table = new PbknityarntypeTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, QueryColumn.All().Count())
.Where(table.YarnType == model.YarnType &&
table.Description == model.Description &&
table.IeDescription == model.IeDescription &&
table.CustomerDescription == model.CustomerDescription &&
table.IsActive == model.IsActive
);
return(sql.ToScalar <int>() > 0);
}
protected bool Exists(PbknitconstructionModel model, bool isNew)
{
PbknitconstructionTable table = new PbknitconstructionTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, QueryColumn.All().Count())
.Where(table.Construction == model.Construction &&
table.Description == model.Description &&
table.DescriptionCn == model.DescriptionCn &&
table.SingleDouble == model.SingleDouble &&
table.IsSpecial == model.IsSpecial &&
table.IsActive == model.IsActive
);
return(sql.ToScalar <int>() > 0);
}
protected bool Exists(QcmaininfologModel model, bool isNew)
{
QcmaininfologTable table = new QcmaininfologTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, QueryColumn.All().Count())
.Where(table.QualityCode == model.QualityCode &&
table.Status == model.Status &&
table.EditReason == model.EditReason &&
table.EditorContent == model.EditorContent &&
table.Editor == model.Editor &&
table.EditorTime == model.EditorTime
);
return(sql.ToScalar <int>() > 0);
}
protected bool Exists(QcyarndtlModel model, bool isNew)
{
QcyarndtlTable table = new QcyarndtlTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, QueryColumn.All().Count())
.Where(table.Iden == model.Iden &&
table.QualityCode == model.QualityCode &&
table.YarnType == model.YarnType &&
table.YarnCount == model.YarnCount &&
table.Threads == model.Threads &&
table.YarnRatio == model.YarnRatio &&
table.WarpWeft == model.WarpWeft &&
table.YarnDensity == model.YarnDensity
);
return(sql.ToScalar <int>() > 0);
}
protected bool Exists(QcavailablewidthModel model, bool isNew)
{
QcavailablewidthTable table = new QcavailablewidthTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, QueryColumn.All().Count())
.Where(table.Iden == model.Iden &&
table.QualityCode == model.QualityCode &&
table.Gauge == model.Gauge &&
table.Diameter == model.Diameter &&
table.TotalNeedles == model.TotalNeedles &&
table.Width == model.Width &&
table.MaxWidth == model.MaxWidth &&
table.UpdatedBy == model.UpdatedBy &&
table.UpdatedTime == model.UpdatedTime
);
return(sql.ToScalar <int>() > 0);
}
protected bool Exists(QccustomerlibraryModel model, bool isNew)
{
QccustomerlibraryTable table = new QccustomerlibraryTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, QueryColumn.All().Count())
.Where(table.Iden == model.Iden &&
table.QualityCode == model.QualityCode &&
table.BuyerId == model.BuyerId &&
table.Brand == model.Brand &&
table.CustomerQualityId == model.CustomerQualityId &&
table.Sales == model.Sales &&
table.SalesGroup == model.SalesGroup &&
table.MillComments == model.MillComments &&
table.IsFirstOwner == model.IsFirstOwner &&
table.CreateDate == model.CreateDate &&
table.Creator == model.Creator
);
return(sql.ToScalar <int>() > 0);
}
public static int SetRangeSql(ref SelectSqlSection sql, QueryTable table, QueryColumn IkeyColumn, int startPage, int pageSize, string orderByField)
{
string[] origanalStr = sql.ColumnNames;
sql.ColumnNames = new string[] { "COUNT(*)" };
int count = Convert.ToInt32(sql.ToScalar());
if (!string.IsNullOrEmpty(orderByField))
{
string[] orderStr = orderByField.Split(new char[] { '*' });
try
{
QueryColumn qc = TableMapModel.GetTableColumnByProName(orderStr[0], table);
if (orderStr[1].Contains("Asc")) //升序
{
sql.OrderBy(qc.Asc, IkeyColumn.Asc);
}
if (orderStr[1].Contains("Desc")) //降序
{
sql.OrderBy(qc.Desc, IkeyColumn.Asc);
}
}
catch (Exception e)
{
// throw e;
}
}
int wPageSize = pageSize;
if (pageSize * startPage > count)
{
wPageSize = count - pageSize * (startPage - 1);
}
if (pageSize > 0 && startPage > 0 && wPageSize > 0)
{
sql.SetSelectRange(wPageSize, pageSize * (startPage - 1), IkeyColumn);
}
sql.ColumnNames = origanalStr;
return(count);
}
public PbknityarncountModelList GetModelList(PbknityarncountModel model)
{
PbknityarncountTable table = new PbknityarncountTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, table.AllColumns())
;
using (SafeDataReader sdr = new SafeDataReader(sql.ToDataReader()))
{
PbknityarncountModelList result = new PbknityarncountModelList();
while (sdr.Read())
{
PbknityarncountModel m = new PbknityarncountModel();
m.YarnCount = sdr.GetString(table.YarnCount);
m.IsActive = sdr.GetString(table.IsActive);
result.Add(m);
}
return(result);
}
}
public PbknitdyemethodModelList GetModelList(PbknitdyemethodModel model)
{
PbknitdyemethodTable table = new PbknitdyemethodTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, table.AllColumns())
;
using (SafeDataReader sdr = new SafeDataReader(sql.ToDataReader()))
{
PbknitdyemethodModelList result = new PbknitdyemethodModelList();
while (sdr.Read())
{
PbknitdyemethodModel m = new PbknitdyemethodModel();
m.DyeMethod = sdr.GetString(table.DyeMethod);
m.DyeType = sdr.GetString(table.DyeType);
m.Description = sdr.GetString(table.Description);
result.Add(m);
}
return(result);
}
}
public SelectSqlSection Join(SelectSqlSection sqlSectin)
{
bool IsAlias = !string.IsNullOrEmpty(this.AliasName);
switch (this.JoinType)
{
case EJoinType.Join:
if (IsAlias)
{
sqlSectin.Join(this.JoinQueryTable, this.AliasName, this.JoinWhere);
}
else
{
sqlSectin.Join(this.JoinQueryTable, this.JoinWhere);
}
break;
case EJoinType.LeftJoin:
if (IsAlias)
{
sqlSectin.LeftJoin(this.JoinQueryTable, this.AliasName, this.JoinWhere);
}
else
{
sqlSectin.LeftJoin(this.JoinQueryTable, this.JoinWhere);
}
break;
case EJoinType.RightJoin:
if (IsAlias)
{
sqlSectin.RightJoin(this.JoinQueryTable, this.AliasName, this.JoinWhere);
}
else
{
sqlSectin.RightJoin(this.JoinQueryTable, this.JoinWhere);
}
break;
}
return(sqlSectin);
}
public QcfinishdtlModelList GetModelListOne(QcfinishdtlModel model)
{
QcfinishdtlTable table = new QcfinishdtlTable();
PbknitfinishTable table1 = new PbknitfinishTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table1, table1.FinishingName).Join(table, table.FinishingCode == table1.FinishingCode);
if (model.QualityCode != "")
{
sql.Where(table.QualityCode == model.QualityCode);
}
using (SafeDataReader sdr = new SafeDataReader(sql.ToDataReader()))
{
QcfinishdtlModelList result = new QcfinishdtlModelList();
while (sdr.Read())
{
QcfinishdtlModel m = new QcfinishdtlModel();
m.FinishingCode = sdr.GetString(table1.FinishingName);
result.Add(m);
}
return(result);
}
}
public PbknitfinishModelList GetModelList(PbknitfinishModel model)
{
PbknitfinishTable table = new PbknitfinishTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, table.AllColumns())
;
using (SafeDataReader sdr = new SafeDataReader(sql.ToDataReader()))
{
PbknitfinishModelList result = new PbknitfinishModelList();
while (sdr.Read())
{
PbknitfinishModel m = new PbknitfinishModel();
m.FinishingCode = sdr.GetString(table.FinishingCode);
m.FinishingName = sdr.GetString(table.FinishingName);
m.Description = sdr.GetString(table.Description);
m.WashingFlag = sdr.GetString(table.WashingFlag);
m.IsActive = sdr.GetString(table.IsActive);
result.Add(m);
}
return(result);
}
}
public PbknityarntypeModelList GetModelList(PbknityarntypeModel model)
{
PbknityarntypeTable table = new PbknityarntypeTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, table.AllColumns()).Where(table.YarnType == model.YarnType)
;
using (SafeDataReader sdr = new SafeDataReader(sql.ToDataReader()))
{
PbknityarntypeModelList result = new PbknityarntypeModelList();
while (sdr.Read())
{
PbknityarntypeModel m = new PbknityarntypeModel();
m.YarnType = sdr.GetString(table.YarnType);
m.Description = sdr.GetString(table.Description);
m.IeDescription = sdr.GetString(table.IeDescription);
m.CustomerDescription = sdr.GetString(table.CustomerDescription);
m.IsActive = sdr.GetString(table.IsActive);
result.Add(m);
}
return(result);
}
}
/// <summary>
/// 获取总记录条数
/// </summary>
/// <param name="column">主键</param>
/// <returns></returns>
public static int GetTotalRecord(EDatabase db, IQueryTable table, WhereClip where, List <JoinTable> joinTables)
{
SelectSqlSection sql = db.Select(table, table.IDColumn.Count());
if (joinTables != null && joinTables.Count > 0)
{
foreach (JoinTable joinTable in joinTables)
{
joinTable.Join(sql);
}
}
object obj = sql.Where(where).ToScalar();
if (obj != null)
{
return(Convert.ToInt32(obj));
}
else
{
return(0);
}
}
public QcconstructiondtlModelList GetModelList(QcconstructiondtlModel model)
{
QcconstructiondtlTable table = new QcconstructiondtlTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, table.AllColumns());
if (model.QualityCode != "")
{
sql.Where(table.QualityCode == model.QualityCode);
}
using (SafeDataReader sdr = new SafeDataReader(sql.ToDataReader()))
{
QcconstructiondtlModelList result = new QcconstructiondtlModelList();
while (sdr.Read())
{
QcconstructiondtlModel m = new QcconstructiondtlModel();
m.Iden = sdr.GetInt32(table.Iden);
m.QualityCode = sdr.GetString(table.QualityCode);
m.Construction = sdr.GetString(table.Construction);
result.Add(m);
}
return(result);
}
}
public QcmaininfologModelList GetModelList(QcmaininfologModel model)
{
QcmaininfologTable table = new QcmaininfologTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, table.AllColumns())
;
using (SafeDataReader sdr = new SafeDataReader(sql.ToDataReader()))
{
QcmaininfologModelList result = new QcmaininfologModelList();
while (sdr.Read())
{
QcmaininfologModel m = new QcmaininfologModel();
m.QualityCode = sdr.GetString(table.QualityCode);
m.Status = sdr.GetString(table.Status);
m.EditReason = sdr.GetString(table.EditReason);
m.EditorContent = sdr.GetString(table.EditorContent);
m.Editor = sdr.GetString(table.Editor);
m.EditorTime = sdr.GetDateTime(table.EditorTime);
result.Add(m);
}
return(result);
}
}
public PbknitconstructionModelList GetModelList(PbknitconstructionModel model)
{
PbknitconstructionTable table = new PbknitconstructionTable();
SelectSqlSection sql = DataAccess.DefaultDB.Select(table, table.AllColumns())
;
using (SafeDataReader sdr = new SafeDataReader(sql.ToDataReader()))
{
PbknitconstructionModelList result = new PbknitconstructionModelList();
while (sdr.Read())
{
PbknitconstructionModel m = new PbknitconstructionModel();
m.Construction = sdr.GetString(table.Construction);
m.Description = sdr.GetString(table.Description);
m.DescriptionCn = sdr.GetString(table.DescriptionCn);
m.SingleDouble = sdr.GetString(table.SingleDouble);
m.IsSpecial = sdr.GetString(table.IsSpecial);
m.IsActive = sdr.GetString(table.IsActive);
result.Add(m);
}
return(result);
}
}
public SelectSqlSection Select(params ExpressionClip[] columns)
{
SelectSqlSection select = this.Db.Select(new CustomQueryTable(this), columns);
return(select);
}
/// <summary>
/// 初始化查询
/// </summary>
/// <param name="queryColumns">查询的列</param>
/// <returns>ModalAdapter</returns>
public ModalAdapter <T> Select(params ExpressionClip[] queryColumns)
{
this._SelectSection = this._DB.Select(this._ExtendColumns, queryColumns);
return(this);
}
private IEnumerable <IInsqlSection> ParseSections(XElement root)
{
var sqlSections = root.Elements(XName.Get("sql", "")).Select(element =>
{
var id = element.Attribute(XName.Get("id", ""));
if (id == null || string.IsNullOrWhiteSpace(id.Value))
{
throw new Exception("insql sql section `id` is empty !");
}
var section = new SqlInsqlSection(id.Value);
section.Elements.AddRange(this.ParseSqlSectionElements(element));
return(section);
}).Cast <IInsqlSection>();
var selectSqlSections = root.Elements(XName.Get("select", "")).Select(element =>
{
var id = element.Attribute(XName.Get("id", ""));
if (id == null || string.IsNullOrWhiteSpace(id.Value))
{
throw new Exception("insql select insert sql section element `id` is empty !");
}
var section = new SelectSqlSection(id.Value);
section.Elements.AddRange(this.ParseSqlSectionElements(element));
return(section);
}).Cast <IInsqlSection>();
var insertSqlSections = root.Elements(XName.Get("insert", "")).Select(element =>
{
var id = element.Attribute(XName.Get("id", ""));
if (id == null || string.IsNullOrWhiteSpace(id.Value))
{
throw new Exception("insql insert sql section element `id` is empty !");
}
var section = new InsertSqlSection(id.Value);
section.Elements.AddRange(this.ParseSqlSectionElements(element));
return(section);
}).Cast <IInsqlSection>();
var updateSqlSections = root.Elements(XName.Get("update", "")).Select(element =>
{
var id = element.Attribute(XName.Get("id", ""));
if (id == null || string.IsNullOrWhiteSpace(id.Value))
{
throw new Exception("insql update sql section element `id` is empty !");
}
var section = new UpdateSqlSection(id.Value);
section.Elements.AddRange(this.ParseSqlSectionElements(element));
return(section);
}).Cast <IInsqlSection>();
var deleteSqlSections = root.Elements(XName.Get("delete", "")).Select(element =>
{
var id = element.Attribute(XName.Get("id", ""));
if (id == null || string.IsNullOrWhiteSpace(id.Value))
{
throw new Exception("insql delete sql section element `id` is empty !");
}
var section = new DeleteSqlSection(id.Value);
section.Elements.AddRange(this.ParseSqlSectionElements(element));
return(section);
}).Cast <IInsqlSection>();
return(sqlSections
.Concat(selectSqlSections)
.Concat(insertSqlSections)
.Concat(updateSqlSections)
.Concat(deleteSqlSections)
.ToList());
}