public override DataTable GetEditableTable(EditableInfo ei, bool bOnlyArray, string sNameFilter)
{
string sSql = "";
if (ei.Sql != null)
{
sSql = ei.Sql;
}
else if (ei.BuildSelectSql != null)
{
sSql = ei.BuildSelectSql(ei, bOnlyArray, sNameFilter);
}
else
{
sSql = BuildCommonSelectSql(ei, bOnlyArray, sNameFilter);
}
return(ExecuteDataTable(sSql));
}
private string BuildCommonSelectSql(EditableInfo ei, bool bOnlyArray, string sNameFilter)
{
string sSql = "";
//if (bForRender || bOnlyArray)
//{
// sSql = "";
// if (bFirstIsNA)
// sSql = "SELECT -1 AS _ID,'<N/A>' AS Name,-1 AS _Status UNION \n";
// sSql += "SELECT tblMaster._ID,tblHistory.`Name`,tblHistory._Status \n";
//}
//else
// sSql = "SELECT tblHistory.*,tblEmp.`Name` AS UserName " + (ei.HasOwner ? ",tblOwner.`Name` AS OwnerName \n" : "\n");
//sSql += " FROM `" + ei.PrimaryTable + "` tblMaster INNER JOIN `" + ei.HistoryTable + "_History` tblHistory ON tblMaster._Last_History=tblHistory._ID \n"
// + (string.IsNullOrEmpty(sNameFilter) ? "" : " AND tblHistory.`Name` LIKE '%" + sNameFilter + "%'")
// + " ps_Employee tblEmp ON tblHistory._Employee_Update=tblEmp.ID \n"
// + (ei.HasOwner ? " INNER JOIN ps_Employee tblOwner ON tblHistory._Employee_Owner=tblOwner._ID \n" : "\n")
// + " ORDER BY tblHistory.`Name`";
return(sSql);
}
public override void SubmitEditableChange(EmployeeInfo emp, changeStatus nStatus, bool bCheckChanged, EditableInfo ei, Dictionary <string, object> row)
{
string sSql = "";
BuildSubmitSql(emp, nStatus, ei, row, bCheckChanged, ref sSql);
ExecCommandWithTransaction(sSql);
}
public override void SubmitEditableChange(EmployeeInfo emp, Dictionary <string, object>[] Rows, EditableInfo ei)
{
string sSql = "";
foreach (Dictionary <string, object> row in Rows)
{
string sStatus = Cvt.ToString(row["__status"]);
if (!string.IsNullOrEmpty(sStatus))
{
changeStatus nStatus = (changeStatus)Enum.Parse(typeof(changeStatus), sStatus, true);
BuildSubmitSql(emp, nStatus, ei, row, true, ref sSql);
}
}
ExecCommandWithTransaction(sSql);
}
private string BuildCommonSelectSql(EditableInfo ei, bool bOnlyArray, string sNameFilter)
{
string sSql = "";
//if (bForRender || bOnlyArray)
//{
// sSql = "";
// if (bFirstIsNA)
// sSql = "SELECT -1 AS _ID,'<N/A>' AS Name,-1 AS _Status UNION \n";
// sSql += "SELECT tblMaster._ID,tblHistory.`Name`,tblHistory._Status \n";
//}
//else
// sSql = "SELECT tblHistory.*,tblEmp.`Name` AS UserName " + (ei.HasOwner ? ",tblOwner.`Name` AS OwnerName \n" : "\n");
//sSql += " FROM `" + ei.PrimaryTable + "` tblMaster INNER JOIN `" + ei.HistoryTable + "_History` tblHistory ON tblMaster._Last_History=tblHistory._ID \n"
// + (string.IsNullOrEmpty(sNameFilter) ? "" : " AND tblHistory.`Name` LIKE '%" + sNameFilter + "%'")
// + " ps_Employee tblEmp ON tblHistory._Employee_Update=tblEmp.ID \n"
// + (ei.HasOwner ? " INNER JOIN ps_Employee tblOwner ON tblHistory._Employee_Owner=tblOwner._ID \n" : "\n")
// + " ORDER BY tblHistory.`Name`";
sSql += "SELECT tblMaster._ID";
string sFromSql = " FROM " + QuoteField(ei.MasterTable) + " tblMaster \n";
foreach (Field fld in ei.MasterTable.Fields)
{
if (bOnlyArray == false || (bOnlyArray && fld.name.Equals("Name", StringComparison.OrdinalIgnoreCase))) //bOnlyArray时只取名字
{
sSql += ",tblMaster." + QuoteField(fld);
}
}
int nSlaveIdx = 0;
string sNameInSlave = "", sOrderFld = "";
foreach (EditableTable tbl in ei.SlaveTables)
{
string sForeignKeyInMaster = ei.ForeignKeyInMaster(tbl);
if (sForeignKeyInMaster != "")
{
nSlaveIdx++;
string sSlaveName = "tblSlave" + nSlaveIdx;
sFromSql += " " + EditableTable.JoinName[(int)tbl.Join] + QuoteField(tbl) + " " + sSlaveName + " ON tblMaster." + QuoteField(sForeignKeyInMaster) + "=" + sSlaveName + "._ID \n";
sSql += " ";
foreach (Field fld in tbl.Fields)
{
if (bOnlyArray == false ||
(bOnlyArray && (fld.name.Equals("Name", StringComparison.OrdinalIgnoreCase) || fld.name.Equals("_Status", StringComparison.OrdinalIgnoreCase)))) //bOnlyArray时只取名字和状态
{
if (!fld.name.Equals(ei.sSlaveKey, StringComparison.OrdinalIgnoreCase))
{
sSql += "," + sSlaveName + "." + QuoteField(fld);
}
}
if (sNameInSlave == "" && fld.name.Equals("Name", StringComparison.OrdinalIgnoreCase))
{
sNameInSlave = sSlaveName + "." + QuoteField(fld);
}
if ((fld.editor & Editor.order) != Editor.none)
{
sOrderFld = sSlaveName + "." + QuoteField(fld);
}
}
sSql += "\n";
}
}
sSql += sFromSql;
if (sNameInSlave != "")
{
if (!string.IsNullOrEmpty(sNameFilter))
{
sSql += " WHERE " + sNameInSlave + " LIKE '%" + sSql.Replace("'", "''") + "%' \n";
}
if (sOrderFld != "")
{
sOrderFld = sOrderFld + "," + sNameInSlave;
}
else
{
sOrderFld = sNameInSlave;
}
}
if (sOrderFld != "")
{
sSql += " ORDER BY " + sOrderFld;
}
return(sSql);
}
private void BuildSubmitSql(EmployeeInfo emp, changeStatus nStatus, EditableInfo ei, Dictionary <string, object> row, bool bCheckChanged, ref string sSql)
{
//if (ei.PrimaryFields == null || ei.PrimaryFields.Length == 0)
// return;
//string sName = "";
//if (row.ContainsKey(ei.PrimaryFields[0]))//第一个Name字段不能为空白
// sName = Cvt.ToString(row[ei.PrimaryFields[0]]);
//sName = sName.Trim();
//if (sName.Length > 0)
{
string sValList = "";
string sSqlRow = "";
if (nStatus == changeStatus.Add)
{
foreach (string sFld in ei.MasterTable.Fields)
{
AddFieldValue(nStatus, sFld, row, emp, "", ref sSqlRow, ref sValList);
}
if (sSqlRow.Length <= 0)
{
sSql += BuildInsert(ei.MasterTable.sName, sSqlRow, sValList);
sSql += "SET @nLastPrimaryID=last_insert_id();\n";
}
else
{
sSql += "SET @nLastPrimaryID=" + row[ei.sSlaveKey] + ";\n";
}
if (ei.SlaveTables != null)
{
foreach (EditableTable slave in ei.SlaveTables)
{
sValList = "";
sSqlRow = "";
foreach (string sFld in slave.Fields)
{
AddFieldValue(nStatus, sFld, row, emp, ei.sSlaveKey, ref sSqlRow, ref sValList);
}
if (sSqlRow.Length > 0)
{
sSql += BuildInsert(slave.sName, sSqlRow, sValList);
string sForeignKeyInMaster = ei.ForeignKeyInMaster(slave.sName);
if (sForeignKeyInMaster.Length > 0)
{
sSql += "UPDATE " + QuoteField(ei.MasterTable.sName) + " SET " + QuoteField(sForeignKeyInMaster) + "=last_insert_id() WHERE " + QuoteField("_ID") + "=@nLastPrimaryID;\n";
}
}
}
}
}
//else if (nStatus == changeStatus.Update)//没有列出的值的字段将置为NULL
//{
// foreach (string sFld in ei.PrimaryFields)
// AddFieldValue(nStatus, sFld, row, emp, "", ref sSqlRow, ref sValList);
// sSql += "UPDATE " + QuoteField(ei.PrimaryTable) + " SET " + sSqlRow + " WHERE " + QuoteField("_ID") + "=" + row[ei.HistoryForeignKey] + ";\n";
// if (!string.IsNullOrEmpty(ei.HistoryTable))
// {
// //再构造添加到Histry表中的语句
// sValList = "";
// sSqlRow = "";
// foreach (string sFld in ei.HistoryFileds)
// AddFieldValue(changeStatus.Add, sFld, row, emp, ei.HistoryForeignKey, ref sSqlRow, ref sValList);
// string sInsertSql = BuildInsert(ei.HistoryTable, sSqlRow, sValList);
// //先检查是否确实有更改
// if (!bCheckChanged)
// sSql += sInsertSql;
// else
// {
// sValList = "";
// sSqlRow = "";
// foreach (string sFld in ei.HistoryFileds)
// AddFieldValue(nStatus, sFld, row, emp, ei.HistoryForeignKey, ref sSqlRow, ref sValList);
// sSql += "IF NOT EXISTS(SELECT * FROM " + QuoteField(ei.HistoryTable) + " WHERE " + QuoteField("_ID") + "=" + row["_ID"] + " AND " + sValList + ") THEN \n"
// + sInsertSql
// + "UPDATE " + QuoteField(ei.PrimaryTable) + " SET " + QuoteField("_Last_History") + "=last_insert_id() WHERE " + QuoteField("_ID") + "=" + row[ei.HistoryForeignKey] + ";\n"
// + "END IF;\n";
// }
// }
//}
//else if (nStatus == changeStatus.AddHistory)//没有列出的值的字段将置最后一次的值
else if (nStatus == changeStatus.Update || nStatus == changeStatus.AddHistory)//没有列出的值的字段将置最后一次的值
{
//foreach (string sFld in ei.PrimaryFields)
//{
// if (row.ContainsKey(sFld))
// AddFieldValue(changeStatus.Update, sFld, row, emp, "", ref sSqlRow, ref sValList);
//}
//if (sSqlRow.Length > 0)
// sSql += "UPDATE " + QuoteField(ei.PrimaryTable) + " SET " + sSqlRow + " WHERE " + QuoteField("_ID") + "=" + row[ei.HistoryForeignKey] + ";\n";
//if (!string.IsNullOrEmpty(ei.HistoryTable))
//{
// //先构造添加到Histry表中的语句
// sValList = "";
// sSqlRow = "";
// foreach (string sFld in ei.HistoryFileds)
// AddFieldValue(changeStatus.AddHistory, sFld, row, emp, ei.HistoryForeignKey, ref sSqlRow, ref sValList);
// string sInsertSql = " SELECT " + QuoteField(ei.ForeignKeyInPrimary) + " INTO @nLastID FROM " + QuoteField(ei.PrimaryTable) + " WHERE " + QuoteField("_ID") + "=" + row[ei.HistoryForeignKey] + " ;\n"
// + " INSERT INTO " + QuoteField(ei.HistoryTable) + "(" + sSqlRow + ") \n"
// + " SELECT " + sValList + " FROM " + QuoteField(ei.HistoryTable) + " WHERE " + QuoteField("_ID") + "=@nLastID;\n";
// //再决定是是否需要检查确实有更改
// if (!bCheckChanged)
// sSql += sInsertSql;
// else
// {
// sValList = "";
// sSqlRow = "";
// foreach (string sFld in ei.HistoryFileds)
// {
// if (row.ContainsKey(sFld))
// AddFieldValue(changeStatus.Where, sFld, row, emp, ei.HistoryForeignKey, ref sSqlRow, ref sValList);
// }
// if (sSqlRow.Length > 0)
// {
// string sUpdateSql ="";
// if (!string.IsNullOrEmpty(ei.ForeignKeyInPrimary))
// sUpdateSql = " UPDATE " + QuoteField(ei.PrimaryTable) + " SET "
// + QuoteField(ei.ForeignKeyInPrimary) + "=last_insert_id() WHERE " + QuoteField("_ID") + "=" + row[ei.HistoryForeignKey] + ";\n";
// sSql += "IF NOT EXISTS(SELECT * FROM " + QuoteField(ei.HistoryTable) + " WHERE " + QuoteField("_ID") + "=" + row[ei.HistoryForeignKey] + " AND " + sSqlRow + ") THEN \n"
// + sInsertSql
// + sUpdateSql
// + "END IF;\n";
// }
// }
//}
}
}
}
public abstract void SubmitEditableChange(EmployeeInfo emp, changeStatus nStatus, bool bCheckChanged, EditableInfo ei, Dictionary <string, object> row);
public abstract void SubmitEditableChange(EmployeeInfo emp, Dictionary <string, object>[] Rows, EditableInfo ei);
public abstract DataTable GetEditableTable(EditableInfo ei, bool bOnlyArray, string sNameFilter);
private void BuildSubmitSql(EmployeeInfo emp, changeStatus nStatus, EditableInfo ei, Dictionary <string, object> row, bool bCheckChanged, ref string sSql)
{
//if (ei.PrimaryFields == null || ei.PrimaryFields.Length == 0)
// return;
//string sName = "";
//if (row.ContainsKey(ei.PrimaryFields[0]))//第一个Name字段不能为空白
// sName = Cvt.ToString(row[ei.PrimaryFields[0]]);
//sName = sName.Trim();
//if (sName.Length > 0)
{
string sValList = "";
string sSqlRow = "";
if (string.IsNullOrEmpty(sSql))
{
sSql = "DECLARE @nLastPrimaryID BIGINT,@nLastID BIGINT; \n";
}
if (nStatus == changeStatus.Add)
{
foreach (Field fld in ei.MasterTable.Fields)
{
//if (fld.editor != Editor.none)
AddFieldValue(nStatus, fld, row, emp, "", ref sSqlRow, ref sValList);
}
if (sSqlRow.Length > 0)
{
sSql += BuildInsert(ei.MasterTable.sName, sSqlRow, sValList);
sSql += "SET @nLastPrimaryID=" + FuncLastInsertId + ";\n";
}
else
{
sSql += "SET @nLastPrimaryID=" + row[ei.sSlaveKey] + ";\n";
}
if (ei.SlaveTables != null)
{
foreach (EditableTable slave in ei.SlaveTables)
{
sValList = "";
sSqlRow = "";
foreach (Field fld in slave.Fields)
{
AddFieldValue(nStatus, fld, row, emp, ei.sSlaveKey, ref sSqlRow, ref sValList);
}
if (sSqlRow.Length > 0)
{
sSql += BuildInsert(slave.sName, sSqlRow, sValList);
string sForeignKeyInMaster = ei.ForeignKeyInMaster(slave.sName);
if (sForeignKeyInMaster.Length > 0)
{
sSql += "UPDATE " + QuoteField(ei.MasterTable.sName) + " SET " + QuoteField(sForeignKeyInMaster) + "=" + FuncLastInsertId + " WHERE " + QuoteField("_ID") + "=@nLastPrimaryID;\n";
}
}
}
}
}
//else if (nStatus == changeStatus.Update)//没有列出的值的字段将置为NULL
//{
// foreach (string sFld in ei.PrimaryFields)
// AddFieldValue(nStatus, sFld, row, emp, "", ref sSqlRow, ref sValList);
// sSql += "UPDATE " + QuoteField(ei.PrimaryTable) + " SET " + sSqlRow + " WHERE " + QuoteField("_ID") + "=" + row[ei.HistoryForeignKey] + ";\n";
// if (!string.IsNullOrEmpty(ei.HistoryTable))
// {
// //再构造添加到Histry表中的语句
// sValList = "";
// sSqlRow = "";
// foreach (string sFld in ei.HistoryFileds)
// AddFieldValue(changeStatus.Add, sFld, row, emp, ei.HistoryForeignKey, ref sSqlRow, ref sValList);
// string sInsertSql = BuildInsert(ei.HistoryTable, sSqlRow, sValList);
// //先检查是否确实有更改
// if (!bCheckChanged)
// sSql += sInsertSql;
// else
// {
// sValList = "";
// sSqlRow = "";
// foreach (string sFld in ei.HistoryFileds)
// AddFieldValue(nStatus, sFld, row, emp, ei.HistoryForeignKey, ref sSqlRow, ref sValList);
// sSql += "IF NOT EXISTS(SELECT * FROM " + QuoteField(ei.HistoryTable) + " WHERE " + QuoteField("_ID") + "=" + row["_ID"] + " AND " + sValList + ") THEN \n"
// + sInsertSql
// + "UPDATE " + QuoteField(ei.PrimaryTable) + " SET " + QuoteField("_Last_History") + "=last_insert_id() WHERE " + QuoteField("_ID") + "=" + row[ei.HistoryForeignKey] + ";\n"
// + "END IF;\n";
// }
// }
//}
//else if (nStatus == changeStatus.AddHistory)//没有列出的值的字段将置最后一次的值
else if (nStatus == changeStatus.Update || nStatus == changeStatus.AddHistory)//没有列出的值的字段将置最后一次的值
{
foreach (Field Fld in ei.MasterTable.Fields)
{
if (row.ContainsKey(Fld))
{
AddFieldValue(changeStatus.Update, Fld, row, emp, "", ref sSqlRow, ref sValList);
}
}
string sPrimaryKey = ei.sSlaveKey;
if (!row.ContainsKey(sPrimaryKey))
{
sPrimaryKey = "_ID";
}
if (sSqlRow.Length > 0)
{
sSql += "UPDATE " + QuoteField(ei.MasterTable.sName) + " SET " + sSqlRow + " WHERE " + QuoteField("_ID") + "=" + row[sPrimaryKey] + ";\n";
}
if (ei.SlaveTables != null)
{
foreach (EditableTable slave in ei.SlaveTables)
{
string sForeignKeyInMaster = ei.ForeignKeyInMaster(slave.sName);
if (sForeignKeyInMaster.Length == 0)
{
continue;
}
//先构造添加到Histry表中的语句
sValList = "";
sSqlRow = "";
foreach (Field Fld in slave.Fields)
{
AddFieldValue(changeStatus.AddHistory, Fld, row, emp, ei.sSlaveKey, ref sSqlRow, ref sValList);
}
string sSelectPrimary = "SELECT @nLastID=" + QuoteField(sForeignKeyInMaster) + " FROM " + QuoteField(ei.MasterTable.sName) + " WHERE " + QuoteField("_ID") + "=" + row[sPrimaryKey] + " ;\n";
string sInsertSql = " INSERT INTO " + QuoteField(slave.sName) + "(" + sSqlRow + ") \n"
+ " SELECT " + sValList + " FROM " + QuoteField(slave.sName) + " WHERE " + QuoteField("_ID") + "=@nLastID;\n";
//再决定是是否需要检查确实有更改
if (!bCheckChanged)
{
sSql += sSelectPrimary + sInsertSql;
}
else
{
sValList = "";
sSqlRow = "";
foreach (Field Fld in slave.Fields)
{
if (row.ContainsKey(Fld) && ei.sSlaveKey.Equals(Fld, StringComparison.OrdinalIgnoreCase) == false)
{
AddFieldValue(changeStatus.Where, Fld, row, emp, ei.sSlaveKey, ref sSqlRow, ref sValList);
}
}
if (sSqlRow.Length > 0)
{
string sUpdateSql = "";
if (!string.IsNullOrEmpty(sForeignKeyInMaster))
{
sUpdateSql = " UPDATE " + QuoteField(ei.MasterTable.sName) + " SET "
+ QuoteField(sForeignKeyInMaster) + "=" + FuncLastInsertId + " WHERE " + QuoteField("_ID") + "=" + row[sPrimaryKey] + ";\n";
}
sSql += sSelectPrimary;
sSql += "IF NOT EXISTS(SELECT * FROM " + QuoteField(slave.sName) + " WHERE " + QuoteField("_ID") + "=@nLastID AND " + sSqlRow + ") \n"
+ "BEGIN\n"
+ sInsertSql
+ sUpdateSql
+ "END;\n";
}
}
}
}//END OF if (ei.SlaveTables != null)
}
}
}