public abstract void SubmitEditableChange(EmployeeInfo emp, Dictionary <string, object>[] Rows, EditableInfo ei);
public abstract void SubmitEditableChange(EmployeeInfo emp, changeStatus nStatus, bool bCheckChanged, EditableInfo ei, Dictionary <string, object> row);
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)
}
}
}
private void AddFieldValue(changeStatus nStatus, Field Fld, Dictionary <string, object> row, EmployeeInfo emp, string HistoryForeignKey, ref string sSql, ref string sValList)
{
//if (Fld.editor == Editor.none)
// return;
string sVal = QuoteNull;
object val = null;
if ((Fld.editor & Editor.order) != Editor.none)
{
val = row["__index"];
}
else if (row.ContainsKey(Fld) && row[Fld] != null)
{
val = row[Fld];
if (Fld.type == FieldType.Int && Cvt.IsNumerical(val) == false)
{
val = null;
}
}
else if (nStatus == changeStatus.AddHistory)
{
if (sSql.Length > 0)
{
sSql += ",";
}
sSql += QuoteField(Fld);
if (sValList.Length > 0)
{
sValList += ",";
}
if (Fld.name.Equals("Update_Time", StringComparison.OrdinalIgnoreCase) ||
Fld.name.Equals("Create_Time", StringComparison.OrdinalIgnoreCase))
{
sValList += FuncCurrentTimestamp;
}
else
{
sValList += QuoteField(Fld);
}
return;
}
else if (nStatus == changeStatus.Where)//条件语句不关心没有给值的字段
{
return;
}
if (val != null && val.GetType() == typeof(string))
{
if (Cvt.IsNumerical(val))
{
sVal = val.ToString();
}
else
{
sVal = QuoteValue(val.ToString());
}
}
else if (Fld.name.Equals("_Employee_Update", StringComparison.OrdinalIgnoreCase))
{
if (nStatus == changeStatus.Where)
{
return;
}
sVal = emp._Employee.ToString();
}
//else if (val == null && Fld.name.Equals("_Site", StringComparison.OrdinalIgnoreCase))
//{
// sVal = emp._Site_Working.ToString();
//}
//else if (val == null && Fld.name.Equals("_Project", StringComparison.OrdinalIgnoreCase))
//{
// sVal = emp._Project_Working.ToString();
//}
else if (Fld.name.Equals(HistoryForeignKey, StringComparison.OrdinalIgnoreCase) && nStatus == changeStatus.Add)
{
sVal = "@nLastPrimaryID";
}
else
{
try
{
if (val != null)
{
sVal = Convert.ToInt64(val).ToString();
}
else if (nStatus != changeStatus.AddHistory)
{
return;
}
}
catch (Exception)
{
}
}
if (nStatus == changeStatus.Add)
{
if (Fld.name.Equals("Update_Time", StringComparison.OrdinalIgnoreCase) == false &&
Fld.name.Equals("Create_Time", StringComparison.OrdinalIgnoreCase) == false)
{
if (sSql.Length > 0)
{
sSql += ",";
}
sSql += QuoteField(Fld);
if (sValList.Length > 0)
{
sValList += ",";
}
sValList += sVal;
}
}
else if (nStatus == changeStatus.Update)
{
if (sSql.Length > 0)
{
sSql += ",";
}
if (Fld.name.Equals("Update_Time", StringComparison.OrdinalIgnoreCase) ||
Fld.name.Equals("Create_Time", StringComparison.OrdinalIgnoreCase))
{
sSql += QuoteField(Fld) + "=" + FuncCurrentTimestamp;
}
else
{
sSql += QuoteField(Fld) + "=" + sVal;
}
}
else if (nStatus == changeStatus.Where)
{
if (Fld.name.Equals("Update_Time", StringComparison.OrdinalIgnoreCase) ||
Fld.name.Equals("Create_Time", StringComparison.OrdinalIgnoreCase))
{
return;//条件语句不关心创建时间或更新时间
}
else
{
if (sSql.Length > 0)
{
sSql += " AND ";
}
sSql += QuoteField(Fld) + "=" + sVal;
}
}
else if (nStatus == changeStatus.AddHistory)
{
if (sSql.Length > 0)
{
sSql += ",";
}
sSql += QuoteField(Fld);
if (sValList.Length > 0)
{
sValList += ",";
}
if (Fld.name.Equals("Update_Time", StringComparison.OrdinalIgnoreCase) ||
Fld.name.Equals("Create_Time", StringComparison.OrdinalIgnoreCase))
{
sValList += FuncCurrentTimestamp;
}
else
{
sValList += sVal;
}
}
}
public override void ChangeUserPassword(LanguageHelper langHelper, long _Employee, string sHashPassword, EmployeeInfo emp)
{
//检查用户的密码与最近5次的密码不相同
string sSql = "SELECT TOP 5 * FROM ps_Employee_History WHERE _Employee=" + _Employee + " ORDER BY _ID;";
DataTable tbl = ExecuteDataTable(sSql);
foreach (DataRow row in tbl.Rows)
{
if (sHashPassword.Equals(row["Password"] as string, StringComparison.OrdinalIgnoreCase))
{
throw new ArgumentException(langHelper.GetText("Can't Use Same Password within recently 5 times !"));
}
}
SubmitEditableChange(emp, changeStatus.AddHistory, true, "_Employee", new ExDictionary(new KV[] {
new KV("_Employee", emp._Employee)
, new KV("Password", sHashPassword)
, new KV("_Employee_Update", emp._Employee)
, new KV("Change_Pwd_When_Next_Login", false)
}));
}
public abstract void AddLoginHistory(EmployeeInfo emp, LoginResult LoginResult);
public abstract void ChangeUserPassword(LanguageHelper langHelper, long _Employee, string sHashPassword, EmployeeInfo emp);