public void Prepare(ref Log returnLog)
{
try
{
TraceFile source = new TraceFile();
source.InitializeAsReader(TraceSource);
while (source.Read())
{
foreach (SQLEvent sqlEvent in EventList)
{
if (source.GetString(source.GetOrdinal("EventClass")) == sqlEvent.EventName)
{
SQLStatement newStatement = new SQLStatement();
newStatement.Statement = source.GetString(source.GetOrdinal("TextData"));
newStatement.ConnectionString = ConnectionString == "{PARENT}" ? _config.SourceConnectionString : ConnectionString;
QueryList.Add(newStatement);
}
}
}
returnLog.EndTime = DateTime.Now;
returnLog.Message = "Prepare successful.";
}
catch (Exception ex)
{
returnLog.EndTime = DateTime.Now;
returnLog.Successful = false;
returnLog.Message = "Prepare failed - " + ex.Message;
}
}
public void defaultAll()
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT FORMAT(MEMBER.MemberID,'00000000'), CONCAT(MEMBER.fName,' ',MEMBER.mName,' ',MEMBER.lName), MEMBERTYPE.MemberTypeName, SAVINGSTYPE.savingsTypeName, SAVINGSTYPE.interestRate, SAVINGSTYPE.initialDeposit, SAVINGSTYPE.maintainingBalance, SAVINGSTYPE.balanceToEarn, case SAVINGSTYPE.maxWithdrawAmount when 0 then 'Not Available' else CONCAT( (CONVERT(nvarchar, SAVINGSTYPE.maxWithdrawAmount)), case SAVINGSTYPE.maxWithdrawMode when 0 then ' / Day' when 1 then ' / Week' when 2 then ' / Month' else ' / Year' end) end, CONCAT(DORMANCY.inactivityValue, ' ',(case DORMANCY.inactivityTime when 0 then 'Day/s' when 1 then 'Week/s' when 2 then 'Month/s' else 'Year/s' end)), CONCAT(DORMANCY.deductionAmount, (case DORMANCY.isPercentage when 0 then ' Pesos ' else ' % ' end), (case DORMANCY.deductionMode when 0 then ' / Day' when 1 then ' / Week' when 2 then ' / Month' else ' / Year' end)), DORMANCY.activationFee, FORMAT(SAVINGSACCOUNT.SavingsAccountID,'00000000'), SAVINGSACCOUNT.dateOpened, SAVINGSACCOUNT.currentBalance FROM MEMBER, MEMBERTYPE, APPLICABLESAVINGS, SAVINGSTYPE, DORMANCY, SAVINGSACCOUNT WHERE MEMBER.MemberTypeID = MEMBERTYPE.MemberTypeID and MEMBERTYPE.MemberTypeID = APPLICABLESAVINGS.MemberTypeID and APPLICABLESAVINGS.SavingsTypeID = SAVINGSTYPE.SavingsTypeID and SAVINGSTYPE.SavingsTypeID = DORMANCY.SavingsTypeID and SAVINGSTYPE.SavingsTypeID = SAVINGSACCOUNT.SavingsTypeID and SAVINGSACCOUNT.MemberID = MEMBER.MemberID and SAVINGSACCOUNT.SavingsAccountID = " + SLS.Static.ID + "";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtMemberID.Text = "MEM - " + reader.GetString(0);
txtMemberName.Text = reader.GetString(1);
txtMemberType.Text = reader.GetString(2);
txtSavName.Text = reader.GetString(3);
txtInterest.Text = reader.GetDecimal(4).ToString();
txtInitial.Text = reader.GetDecimal(5).ToString();
txtMainBal.Text = reader.GetDecimal(6).ToString();
txtBalToEarn.Text = reader.GetDecimal(7).ToString();
txtMaxWithdraw.Text = reader.GetString(8);
txtDormancy.Text = reader.GetString(9);
txtDeductDetails.Text = reader.GetString(10);
txtActivationFee.Text = reader.GetDecimal(11).ToString();
txtSavAccount.Text = "SAV - " + reader.GetString(12);
txtDateStarted.Text = reader.GetDateTime(13).ToString();
txtCurrentBalance.Text = reader.GetDecimal(14).ToString();
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT CONCAT('SAV - ', FORMAT(SAVINGSACCOUNT.SavingsAccountID,'00000000')) as [Savings Account ID], CASE SAVINGSTRANSACTION.transactionType when 1 then 'Deposit' when 2 then 'Withdraw' when 3 then 'Annual Increase' else 'Dormancy Deduction' end as [Transaction Type], SAVINGSTRANSACTION.transactionAmount as [Amount], SAVINGSTRANSACTION.transactionDate as [Date], SAVINGSTRANSACTION.transactionRepresentative as [Representative], SAVINGSTRANSACTION.currentBalance as [Balance] FROM SAVINGSACCOUNT, SAVINGSTRANSACTION WHERE SAVINGSACCOUNT.SavingsAccountID = SAVINGSTRANSACTION.SavingsAccountID and SAVINGSACCOUNT.SavingsAccountID = " + SLS.Static.ID + "";
DataSet ds = con.executeDataSet(sql, "Account");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Account";
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
}
protected override void Execute(CodeActivityContext context)
{
using (SqlConnection connection =
new SqlConnection(connectionString.Get <string>(context)))
{
SqlCommand command = new SqlCommand(SQLStatement.Get <string>(context), connection);
SqlDataAdapter da = new SqlDataAdapter(command);
try
{
DataSet ds = new DataSet();
da.Fill(ds);
DataRow[] linhas = new DataRow[ds.Tables[0].Rows.Count];
ds.Tables[0].Rows.CopyTo(linhas, 0);
data.Set(context, linhas);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
connection.Close();
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "INSERT INTO SAVINGSTRANSACTION(transactionDate, transactionType, transactionAmount, transactionRepresentative, currentBalance, SavingsAccountID) VALUES (@transactionDate, @transactionType, @transactionAmount, @transactionRepresentative, @currentBalance, @SavingsAccountID)";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@transactionDate", Convert.ToDateTime(DateTime.Now.ToLongDateString()));
parameters.Add("@transactionType", '2');
parameters.Add("@transactionAmount", Convert.ToDecimal(txtCurrentBalance.Text));
parameters.Add("@transactionRepresentative", txtMemberName.Text);
parameters.Add("@currentBalance", '0');
parameters.Add("@SavingsAccountID", SLS.Static.ID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "UPDATE SAVINGSACCOUNT SET dateClosed = @dateClosed, currentBalance = '0' WHERE SavingsAccountID = @SavingsAccountID";
parameters = new Dictionary<string, object>();
parameters.Add("@dateClosed", Convert.ToDateTime(DateTime.Now.ToLongDateString()));
parameters.Add("@SavingsAccountID", SLS.Static.ID);
result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("Closing a savings account is successful.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
defaultAll();
btnSave.Enabled = false;
}
}
else
{
MessageBox.Show("Closing a savings account is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void addChildrenRelationship(int ind)
{
string relship;
bool childgend;
if (tableChild.Rows[ind][3].ToString() == "1")
{
relship = "Son";
childgend = true;
}
else
{
relship = "Daughter";
childgend = false;
}
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "INSERT INTO FAMILYBACKGROUND (fName,mName, lName, birthDate, gender, relationship, MemberID) VALUES (@fName, @mName, @lName, @birthDate, @gender, @relationship, @MemberID)";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@fName", tableChild.Rows[ind][1].ToString());
parameters.Add("@mName", tableChild.Rows[ind][2].ToString());
parameters.Add("@lName", tableChild.Rows[ind][0].ToString());
parameters.Add("@birthDate", tableChild.Rows[ind][4].ToString());
parameters.Add("@gender", childgend);
parameters.Add("@relationship", relship.ToString());
parameters.Add("@MemberID", MemberID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
}
public void defaultAll()
{
txtFN.Clear();
txtMN.Clear();
txtLN.Clear();
txtPosition.Clear();
er1.Visible = false;
er2.Visible = false;
er3.Visible = false;
er4.Visible = false;
if (SLS.Static.ID != 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT fName, mName, lName, position FROM LOANGRANTOR where lgID = " + SLS.Static.ID + " ";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtFN.Text = Convert.ToString(reader[0]);
txtMN.Text = Convert.ToString(reader[1]);
txtLN.Text = Convert.ToString(reader[2]);
txtPosition.Text = Convert.ToString(reader[3]);
}
}
}
public override void Run(Context ctx, Schedule schedule)
{
SQLStatement = base.GetStatement(PayableSQLObject.Payable);
opearteList = GetOpearteList(PayableSQLObject.Payable);
if (connectionToRemoteDatabase().Sucessd)
{
ExcuteDataBaseResult excuteDataBaseResult = base.remoteExcuteDataBase.excuteStatement(SQLStatement, connectionToRemoteDatabase().Sqlconn);
payableSaveInfoObjList = handleData <PayableInfoSaveObject>(excuteDataBaseResult.Ds);
}
else
{
//打印
}
if (LoginAPI(model))
{
Dictionary <string, Result> statusMap = new Dictionary <string, Result>();
foreach (PayableInfoSaveObject payableSaveInfoObject in payableSaveInfoObjList)
{
InvokeReturnHandle <InvokeResult> callResult = InvokeAPI(opearteList, payableSaveInfoObject, InvokeCloudAPI.Login(model), ctx, formID, model);
statusMap.Add(payableSaveInfoObject.Model.FBillNo, callResult.CustomOpearteObject.Result);
}
List <string> getUpdateSQLStatements = new SQLStatement(model).getUpdateSQLStatement(model, statusMap);
SqlConnection Sqlconn = connectionToRemoteDatabase().Sqlconn;
foreach (string UpdateSQLStatement in getUpdateSQLStatements)
{
updateMiddleDataBase(UpdateSQLStatement, model, "t_kf_payable", Sqlconn);
}
}
}
static void TestTpcds_LoadData()
{
var files = Directory.GetFiles(@"../../../tpcds", "*.sql");
string[] norun = { "q1", "q10" };
Tpcds.CreateTables();
Tpcds.LoadTables("tiny");
Tpcds.AnalyzeTables();
// make sure all queries can generate phase one opt plan
QueryOption option = new QueryOption();
option.optimize_.enable_subquery_unnest_ = true;
option.optimize_.remove_from_ = false;
option.optimize_.use_memo_ = false;
foreach (var v in files)
{
char[] splits = { '.', '/', '\\' };
var tokens = v.Split(splits, StringSplitOptions.RemoveEmptyEntries);
if (norun.Contains(tokens[1]))
{
continue;
}
var sql = File.ReadAllText(v);
var result = SQLStatement.ExecSQL(sql, out string phyplan, out _, option);
}
}
public void ProcessorComplete(TraceData data)
{
_data.ExecutionPath.AddRange(ctx.executionCalls);
_data.AllExecutionCalls.AddRange(ctx.allCalls);
foreach (ExecutionCall call in ctx.allCalls.Where(c => c.Type.HasFlag(ExecutionCallType.SQL)))
{
var lineNumber = call.StartLine;
try
{
var sqlStatement = data.SQLStatements.Where(s => s.LineNumber == lineNumber + 1).First();
call.SQLStatement = sqlStatement;
sqlStatement.ParentCall = call.Parent;
call.Duration = sqlStatement.Duration;
}catch (Exception ex)
{
/* this happens durring loops where there was no SQL statement. We need to find a SQL statement prior and clone it and update binds when we capture those */
var dummyStatement = new SQLStatement("SELECT 'X' FROM DUAL");
call.SQLStatement = dummyStatement;
dummyStatement.ParentCall = call.Parent;
call.Duration = dummyStatement.Duration;
}
}
}
static void RunSQLFromFile(string filename)
{
// Not working as expected, need to investigate.
var option = new QueryOption();
option.explain_.mode_ = ExplainMode.full;
option.optimize_.use_memo_ = true;
option.explain_.show_estCost_ = false;
string allquery = File.ReadAllText(filename);
string[] listquery = allquery.Split(';');
List <string> listoutput = new List <string>();
int linenum = 0;
for (int i = 0; i < listquery.Length; ++i)
{
linenum = i + 1;
string sql = listquery[i].Trim();
if (sql.Length <= 0)
{
continue;
}
else if (sql.StartsWith("--"))
{
continue;
}
try
{
Console.WriteLine(sql);
string outline = linenum.ToString();
outline += ": " + sql + "\n";
listoutput.Add(outline);
var result = SQLStatement.ExecSQL(sql, out string physicplan, out string error_, option);
if (physicplan != null)
{
Console.WriteLine(physicplan);
listoutput.Add(physicplan);
}
if (result != null)
{
Console.WriteLine(result);
listoutput.Add(result.ToString());
}
}
catch (Exception e)
{
Console.WriteLine("SQL: " + sql + "\nEXCEPTION: " + e + "\n");
continue;
}
}
string alloutput = string.Join('\n', listoutput);
string outfile = filename + ".out";
File.WriteAllText(outfile, alloutput);
}
public void defaultAll()
{
txtMemName.Text = "";
txtMinAge.Text = "";
txtMaxAge.Text = "";
txtFee.Text = "";
ckbSavings.Checked = false;
ckbTime.Checked = false;
ckbLoan.Checked = false;
txtShare.Text = "";
er1.Visible = true;
er2.Visible = true;
er3.Visible = true;
er4.Visible = true;
er5.Visible = false;
if (SLS.Static.ID != 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT MemberTypeID, MemberTypeName, MinAgeRequired, MaxAgeRequired, Fee, SavingsApplicable, TimeApplicable, LoanApplicable, ShareRequired FROM MEMBERTYPE where MemberTypeID = " + SLS.Static.ID + " ";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
MemberTypeID = Convert.ToInt32(reader[0]);
txtMemName.Text = Convert.ToString(reader[1]);
txtMinAge.Text = Convert.ToString(reader[2]);
txtMaxAge.Text = Convert.ToString(reader[3]);
txtFee.Text = Convert.ToString(reader[4]);
if (Convert.ToString(reader[5]) == "True")
{
ckbSavings.Checked = true;
}
else
{
ckbSavings.Checked = false;
}
if (Convert.ToString(reader[6]) == "True")
{
ckbTime.Checked = true;
}
else
{
ckbTime.Checked = false;
}
if (Convert.ToString(reader[7]) == "True")
{
ckbLoan.Checked = true;
txtShare.Enabled = true;
txtShare.Text = Convert.ToString(reader[8]);
}
else
{
ckbLoan.Checked = false;
txtShare.Enabled = false;
txtShare.Clear();
}
}
}
}
public SQLBookingInterface()
{
//
// TODO: Add constructor logic here
//
date = new GetMeetingDate();
sql = new SQLStatement();
}
static public void AnalyzeTables()
{
foreach (var v in tabnames_)
{
var sql = $"analyze {v};";
SQLStatement.ExecSQL(sql, out _, out _);
}
}
static void createBuildInTestTables()
{
// create tables
string[] createtables =
{
@"create table test (t1 int, t2 int, t3 int, t4 int);"
,
@"create table a (a1 int, a2 int, a3 int, a4 int);",
@"create table b (b1 int, b2 int, b3 int, b4 int);",
@"create table c (c1 int, c2 int, c3 int, c4 int);",
@"create table d (d1 int, d2 int, d3 int, d4 int);",
// nullable tables
@"create table r (r1 int, r2 int, r3 int, r4 int);",
// distributed tables
@"create table ad (a1 int, a2 int, a3 int, a4 int) distributed by a1;",
@"create table bd (b1 int, b2 int, b3 int, b4 int) distributed by b1;",
@"create table cd (c1 int, c2 int, c3 int, c4 int) distributed by c1;",
@"create table dd (d1 int, d2 int, d3 int, d4 int) distributed by d1;",
@"create table ar (a1 int, a2 int, a3 int, a4 int) replicated;",
@"create table br (b1 int, b2 int, b3 int, b4 int) replicated;",
@"create table arb (a1 int, a2 int, a3 int, a4 int) roundrobin;",
@"create table brb (b1 int, b2 int, b3 int, b4 int) roundrobin;",
// steaming tables
@"create table ast (a0 datetime, a1 int, a2 int, a3 int, a4 int);", // bounded table with ts
@"create stream ainf (a0 datetime, a1 int, a2 int, a3 int, a4 int);", // unbounded table
};
SQLStatement.ExecSQLList(string.Join("", createtables));
// load tables
var appbin_dir = AppContext.BaseDirectory.Substring(0, AppContext.BaseDirectory.LastIndexOf("bin"));
var folder = $@"{appbin_dir}/../data";
var tables = new List <string>()
{
"test", "a", "b", "c", "d", "r", "ad", "bd", "cd", "dd", "ar", "br", "arb", "brb", "ast"
};
foreach (var v in tables)
{
string filename = $@"'{folder}/{v}.tbl'";
var sql = $"copy {v} from {filename};";
var result = SQLStatement.ExecSQL(sql, out _, out _);
}
// create index
string[] createindexes =
{
@"create unique index dd1 on d(d1);",
@"create index dd2 on d(d2);",
};
SQLStatement.ExecSQLList(string.Join("", createindexes));
// analyze tables
foreach (var v in tables)
{
var sql = $"analyze {v};";
var result = SQLStatement.ExecSQL(sql, out _, out _);
}
}
public void loadDatabase()
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT MEMBER.MemberID as [ID], CONCAT(MEMBER.fName,' ',MEMBER.mName,' ',MEMBER.lName) as [Name], MEMBERTYPE.MemberTypeName as [Member Type Name], TIMEDEPOSITRATES.TimeDepositRatesID as [ID], CONCAT(TIMEDEPOSITRATES.noOfDays, ' Days') as [No. Of Days], CONCAT(TIMEDEPOSITRATES.minAmount,' - ', TIMEDEPOSITRATES.maxAmount) as [Amount Range], CONCAT(TIMEDEPOSITRATES.interest, ' %') as [Interest], TIMEDEPOSITACCOUNT.TimeDepositAccountID as [Time Deposit Account ID], TIMEDEPOSITACCOUNT.currentBalance as [Amount] FROM TIMEDEPOSITRATES, TIMEDEPOSITACCOUNT, MEMBER, MEMBERTYPE WHERE TIMEDEPOSITRATES.TimeDepositRatesID = TIMEDEPOSITACCOUNT.TimeDepositRatesID and TIMEDEPOSITACCOUNT.MemberID = MEMBER.MemberID and MEMBER.MemberTypeID = MEMBERTYPE.MemberTypeID and MEMBER.MemberID = " + SLS.Static.ID + "";
DataSet ds = con.executeDataSet(sql, "Account");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Account";
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
static public void CreateIndexes()
{
string curdir = Directory.GetCurrentDirectory();
string folder = $@"{curdir}/../../../../tpch/sql_scripts";
string filename = $@"{folder}/TableIndexes.sql";
var sql = File.ReadAllText(filename);
SQLStatement.ExecSQLList(sql);
}
internal static void Run(Assembly assembly, SQLStatement stmt, ExecContext context)
{
// now we can execute it
var type = assembly.GetType("QueryCode");
var instance = assembly.CreateInstance("QueryCode");
var meth = type.GetMember("Run").First() as MethodInfo;
meth.Invoke(instance, new object[] { stmt, context });
}
public BindContext(SQLStatement current, BindContext parent)
{
stmt_ = current;
parent_ = parent;
if (parent is null)
{
globalSubqCounter_ = 0;
}
}
static public void CreateTables()
{
string curdir = Directory.GetCurrentDirectory();
string folder = $@"{curdir}\..\..\..\jobench\create";
string filename = $@"{folder}\schema.sql";
var sql = File.ReadAllText(filename);
SQLStatement.ExecSQLList(sql);
}
public override void Run(Context ctx, Schedule schedule)
{
//1从中间表中获取当前需要调用的模块的SQL
// 1.2.连接中间数据库
// 1.3.获取数据
//2.调用Kingdee登录API
//3.调用客户API
//4.反写中间数据库
//5.关闭数据库连接
SQLStatement = GetStatement(CustomerSQLObject.Customer);
opearteList = GetOpearteList(CustomerSQLObject.Customer);
//修改的单独处理
BussnessModify customerModify = new BussnessModify();
customerModify.excuteSQL(model, connectionToRemoteDatabase(), ctx);
if (connectionToRemoteDatabase().Sucessd)
{
ExcuteDataBaseResult excuteDataBaseResult = base.remoteExcuteDataBase.excuteStatement(SQLStatement, connectionToRemoteDatabase().Sqlconn);
customSaveInfoObjList = handleData <CustomInfoSaveObject>(excuteDataBaseResult.Ds);
if (!closeConnetction(connectionToRemoteDatabase().Sqlconn))
{
//打印关闭连接
BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表取数后,关闭数据库连接失败! 请联系系统管理员!");
return;
}
}
else
{
if (!closeConnetction(connectionToRemoteDatabase().Sqlconn))
{
BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表取数前打开数据库连接失败! 请联系系统管理员!");
return;
}
}
Dictionary <string, Result> statusMap = new Dictionary <string, Result>();
if (LoginAPI(model))
{
foreach (CustomInfoSaveObject customSaveInfoObj in customSaveInfoObjList)
{
InvokeReturnHandle <InvokeResult> callResult = InvokeAPI(opearteList, customSaveInfoObj, InvokeCloudAPI.Login(model), ctx, formID, model);
statusMap.Add(customSaveInfoObj.Model.Fnumber, callResult.CustomOpearteObject.Result);
}
List <string> getUpdateSQLStatements = new SQLStatement(model).getUpdateSQLStatement(model, statusMap);
SqlConnection Sqlconn = connectionToRemoteDatabase().Sqlconn;
foreach (string UpdateSQLStatement in getUpdateSQLStatements)
{
updateMiddleDataBase(UpdateSQLStatement, model, "t_kf_customer", Sqlconn);
}
if (!closeConnetction(Sqlconn))
{
BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表数据更新后,关闭数据库连接失败! 请联系系统管理员!");
return;
}
}
}
public void loadDatabase()
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = SLS.Static.sqlParams;
DataSet ds = con.executeDataSet(sql,SLS.Static.parameters ,"Member");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Member";
dataGridView1.Columns[0].Visible = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
static public void DropTables()
{
string curdir = Directory.GetCurrentDirectory();
string folder = $@"{curdir}/../../../../tpcds/sql_scripts";
string filename = $@"{folder}/DropTables.sql";
var sql = File.ReadAllText(filename);
SQLStatement.ExecSQLList(sql);
System.GC.Collect();
}
public override void Run(Context ctx, Schedule schedule)
{
//VoucherNumbers.Add("记", "PRE001");
//VoucherNumbers.Add("现收", "PRE002");
//VoucherNumbers.Add("现付", "PRE003");
//VoucherNumbers.Add("转", "PRE004");
//VoucherNumbers.Add("银收", "PZZ1");
//VoucherNumbers.Add("银付", "PZZ2");
SQLStatement = base.GetStatement(VouchSQLObject.Voucher);
opearteList = GetOpearteList(VouchSQLObject.Voucher);
if (connectionToRemoteDatabase().Sucessd)
{
ExcuteDataBaseResult excuteDataBaseResult = base.remoteExcuteDataBase.excuteStatement(SQLStatement, connectionToRemoteDatabase().Sqlconn);
voucherSaveInfoObjList = handleData <VoucherInfoSaveObject>(excuteDataBaseResult.Ds);
if (!closeConnetction(connectionToRemoteDatabase().Sqlconn))
{
//打印关闭连接
BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表取数后,关闭数据库连接失败! 请联系系统管理员!");
return;
}
}
else
{
if (!closeConnetction(connectionToRemoteDatabase().Sqlconn))
{
//打印关闭连接
BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表取数前打开数据库连接失败! 请联系系统管理员!");
return;
}
}
if (LoginAPI(model))
{
Dictionary <string, Result> statusMap = new Dictionary <string, Result>();
foreach (VoucherInfoSaveObject voucherSaveInfoObj in voucherSaveInfoObjList)
{
InvokeReturnHandle <InvokeResult> callResult = InvokeAPI(opearteList, voucherSaveInfoObj, InvokeCloudAPI.Login(model), ctx, formID, model);
statusMap.Add(voucherSaveInfoObj.Model.FVOUCHERGROUPNO, callResult.CustomOpearteObject.Result);
}
List <string> getUpdateSQLStatements = new SQLStatement(model).getUpdateSQLStatement(model, statusMap);
SqlConnection Sqlconn = connectionToRemoteDatabase().Sqlconn;
foreach (string UpdateSQLStatement in getUpdateSQLStatements)
{
updateMiddleDataBase(UpdateSQLStatement, model, "t_kf_voucher", Sqlconn);
}
if (!closeConnetction(Sqlconn))
{
BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表数据更新后,关闭数据库连接失败! 请联系系统管理员!");
return;
}
}
}
internal static void Run(CompilerResults cr, SQLStatement stmt, ExecContext context)
{
// now we can execute it
var assembly = cr.CompiledAssembly;
var queryCode = assembly.GetType("QueryCode");
var runmethod = queryCode.GetMethod("Run");
runmethod.Invoke(null, new object[2] {
stmt, context
});
}
static public void LoadTables(string subfolder)
{
string save_curdir = Directory.GetCurrentDirectory();
string folder = $@"{save_curdir}/../../../../tpch/sql_scripts";
Directory.SetCurrentDirectory(folder);
var sql = File.ReadAllText($@"LoadTables-{subfolder}.sql");
SQLStatement.ExecSQLList(sql);
Directory.SetCurrentDirectory(save_curdir);
}
public CteExpr(string cteName, List <string> colNames, SQLStatement query) : base()
{
Utils.Assumes(query is SelectStmt);
query_ = query as SelectStmt;
Debug.Assert(!query_.isCteDefinition_);
query_.isCteDefinition_ = true;
cteName_ = cteName;
colNames_ = colNames;
refcnt_ = 0;
}
protected override void Execute(CodeActivityContext context)
{
using (SqlConnection connection = new SqlConnection(ConnectionString.Get(context)))
{
using (SqlCommand command = new SqlCommand(SQLStatement.Get(context), connection))
{
connection.Open();
command.ExecuteNonQuery();
}
}
}
static public void CreateTables(bool isdistr = false)
{
DropTables();
string curdir = Directory.GetCurrentDirectory();
string folder = $@"{curdir}/../../../../tpch/sql_scripts";
string postfix = isdistr ? "_d" : "";
string filename = $@"{folder}/tpch{postfix}.sql";
var sql = File.ReadAllText(filename);
SQLStatement.ExecSQLList(sql);
}
static public void LoadTables(string subfolder)
{
string curdir = Directory.GetCurrentDirectory();
string folder = $@"{curdir}\..\..\..\tpch\data\{subfolder}";
foreach (var v in tabnames_)
{
string filename = $@"'{folder}\{v}.tbl'";
var sql = $"copy {v} from {filename};";
SQLStatement.ExecSQL(sql, out _, out _);
}
}
public void loadDatabase()
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT SAVINGSACCOUNT.SavingsAccountID, CONCAT('SAV - ', FORMAT(SAVINGSACCOUNT.SavingsAccountID,'00000000')) as [Savings Account ID], SAVINGSACCOUNT.MemberID as [Member ID], CONCAT(MEMBER.fName,' ' , MEMBER.mName, ' ', MEMBER.lName) as [Member Name], SAVINGSACCOUNT.SavingsTypeID as [Savings Type ID], SAVINGSTYPE.savingsTypeName as [Savings Type Name], SAVINGSTYPE.interestRate as [Interest Rate], SAVINGSTYPE.initialDeposit as [Initial Deposit], SAVINGSTYPE.maintainingBalance as [Maintaining Balance], SAVINGSTYPE.balanceToEarn as [Balance To Earn], case SAVINGSTYPE.maxWithdrawAmount when 0 then 'Not Available' else CONCAT( (CONVERT(nvarchar, SAVINGSTYPE.maxWithdrawAmount)), (case SAVINGSTYPE.maxWithdrawMode when 0 then ' / Day' when 1 then ' / Week' when 2 then ' / Month' else ' / Year' end)) end as [Maximum Withdrawal], CONCAT(DORMANCY.inactivityValue, ' ',(case DORMANCY.inactivityTime when 0 then 'Day/s' when 1 then 'Week/s' when 2 then 'Month/s' else 'Year/s' end)) as [Inactivity Period], CONCAT(DORMANCY.deductionAmount, (case DORMANCY.isPercentage when 0 then ' Pesos ' else ' % ' end), (case DORMANCY.deductionMode when 0 then ' / Day' when 1 then ' / Week' when 2 then ' / Month' else ' / Year' end)) as [Deduction], DORMANCY.activationFee[Activation Fee], SAVINGSACCOUNT.dateOpened as [Date Started], SAVINGSACCOUNT.currentBalance as [Current Balance] FROM SAVINGSACCOUNT, MEMBER, SAVINGSTYPE, DORMANCY WHERE SAVINGSACCOUNT.MemberID = MEMBER.MemberID and SAVINGSACCOUNT.SavingsTypeID = SAVINGSTYPE.SavingsTypeID and DORMANCY.SavingsTypeID = SAVINGSTYPE.SavingsTypeID and MEMBER.MemberID = " + SLS.Static.ID + " and SAVINGSACCOUNT.dateClosed IS NULL";
DataSet ds = con.executeDataSet(sql, "Account");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Account";
dataGridView1.Columns[0].Visible = false;
dataGridView1.Columns[2].Visible = false;
dataGridView1.Columns[3].Visible = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
static public void CreateTables()
{
// hack: drop tpch table customer
SQLStatement.ExecSQL("drop table customer;", out _, out _);
string curdir = Directory.GetCurrentDirectory();
string folder = $@"{curdir}\..\..\..\tpch\create";
string filename = $@"{folder}\tpch.sql";
var sql = File.ReadAllText(filename);
SQLStatement.ExecSQLList(sql);
}
public void loadDatabase()
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT MEMBER.MemberID as [ID], CONCAT(MEMBER.fName,' ',MEMBER.mName,' ',MEMBER.lName) as [Name], DATEDIFF(YEAR, MEMBER.birthDate, @DateNow ) as [Age], MEMBERTYPE.MemberTypeName as [Member Type] FROM MEMBER, MEMBERTYPE WHERE MEMBER.MemberTypeID = MEMBERTYPE.MemberTypeID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@DateNow", Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd")));
DataSet ds = con.executeDataSet(sql, parameters, "Member");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Member";
dataGridView1.Columns[0].Visible = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
static void createBuildInTestTables()
{
// create tables
string[] createtables =
{
@"create table test (a1 int, a2 int, a3 int, a4 int);"
,
@"create table a (a1 int, a2 int, a3 int, a4 int);",
@"create table b (b1 int, b2 int, b3 int, b4 int);",
@"create table c (c1 int, c2 int, c3 int, c4 int);",
@"create table d (d1 int, d2 int, d3 int, d4 int);",
// nullable tables
@"create table r (r1 int, r2 int, r3 int, r4 int);",
// distributed tables
@"create table ad (a1 int, a2 int, a3 int, a4 int) distributed by a1;",
@"create table bd (b1 int, b2 int, b3 int, b4 int) distributed by b1;",
@"create table cd (c1 int, c2 int, c3 int, c4 int) distributed by c1;",
@"create table dd (d1 int, d2 int, d3 int, d4 int) distributed by d1;",
// steaming tables
@"create table ast (a0 datetime, a1 int, a2 int, a3 int, a4 int);",
};
SQLStatement.ExecSQLList(string.Join("", createtables));
// load tables
var curdir = Directory.GetCurrentDirectory();
var folder = $@"{curdir}\..\..\..\data";
var tables = new List <string>()
{
"a", "b", "c", "d", "r", "ad", "bd", "cd", "dd", "ast"
};
foreach (var v in tables)
{
string filename = $@"'{folder}\{v}.tbl'";
var sql = $"copy {v} from {filename};";
var result = SQLStatement.ExecSQL(sql, out _, out _);
}
// create index
string[] createindexes =
{
@"create unique index dd1 on d(d1);",
@"create index dd2 on d(d2);",
};
SQLStatement.ExecSQLList(string.Join("", createindexes));
// analyze tables
foreach (var v in tables)
{
var sql = $"analyze {v};";
var result = SQLStatement.ExecSQL(sql, out _, out _);
}
}
public void defaultAll()
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT MEMBER.MemberID as [ID], CONCAT(MEMBER.fName,' ',MEMBER.mName,' ',MEMBER.lName) as [Name], MEMBERTYPE.MemberTypeName as [Member Type Name], TIMEDEPOSITRATES.TimeDepositRatesID as [ID], CONCAT(TIMEDEPOSITRATES.noOfDays, ' Days') as [No. Of Days], CONCAT(TIMEDEPOSITRATES.minAmount,' - ', TIMEDEPOSITRATES.maxAmount) as [Amount Range], CONCAT(TIMEDEPOSITRATES.interest, ' %') as [Interest], TIMEDEPOSITACCOUNT.TimeDepositAccountID as [Time Deposit Account ID], TIMEDEPOSITACCOUNT.currentBalance as [Amount] FROM TIMEDEPOSITRATES, TIMEDEPOSITACCOUNT, MEMBER, MEMBERTYPE WHERE TIMEDEPOSITRATES.TimeDepositRatesID = TIMEDEPOSITACCOUNT.TimeDepositRatesID and TIMEDEPOSITACCOUNT.MemberID = MEMBER.MemberID and MEMBER.MemberTypeID = MEMBERTYPE.MemberTypeID and MEMBER.MemberID = " + SLS.Static.ID + "";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtMemberID.Text = "MEM - " + Convert.ToString(reader.GetInt32(0));
txtMemberName.Text = reader.GetString(1);
txtMemberType.Text = reader.GetString(2);
}
}
public void addFatherRelationship()
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "INSERT INTO FAMILYBACKGROUND (fName,mName, lName, birthDate, gender, relationship, MemberID) VALUES (@fName, @mName, @lName, @birthDate, @gender, @relationship, @MemberID)";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@fName", txtFNFN.Text);
parameters.Add("@mName", txtFNMN.Text);
parameters.Add("@lName", txtFNLN.Text);
parameters.Add("@birthDate", Convert.ToDateTime(dtFN.Value));
parameters.Add("@gender", "1");
parameters.Add("@relationship", "Father");
parameters.Add("@MemberID", MemberID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
}
public void loadDatabase()
{
if(SLS.Static.hasSearch == 0)
{
if(cobFilter.SelectedIndex == 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT LoanTypeID as [ID], loanTypeName as [Loan Type], minAmount as [Minimum Amount], maxAmount as [Maximum Amount], CONCAT(noOfComaker, ' Members') as [No. of Co-maker], CONCAT(entitlement, ' %') as [Entitlement], CONCAT(eligibility, ' %') as [Eligibility], [status] as [Status] FROM LOANTYPE";
DataSet ds = con.executeDataSet(sql, "Savings Type");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Savings Type";
dataGridView1.Columns[0].Visible = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
else if (cobFilter.SelectedIndex == 1)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT LoanTypeID as [ID], loanTypeName as [Loan Type], minAmount as [Minimum Amount], maxAmount as [Maximum Amount], CONCAT(noOfComaker, ' Members') as [No. of Co-maker], CONCAT(entitlement, ' %') as [Entitlement], CONCAT(eligibility, ' %') as [Eligibility], [status] as [Status] FROM LOANTYPE WHERE [status] = 1";
DataSet ds = con.executeDataSet(sql, "Savings Type");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Savings Type";
dataGridView1.Columns[0].Visible = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
else if (cobFilter.SelectedIndex == 2)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT LoanTypeID as [ID], loanTypeName as [Loan Type], minAmount as [Minimum Amount], maxAmount as [Maximum Amount], CONCAT(noOfComaker, ' Members') as [No. of Co-maker], CONCAT(entitlement, ' %') as [Entitlement], CONCAT(eligibility, ' %') as [Eligibility], [status] as [Status] FROM LOANTYPE WHERE [status] = 0";
DataSet ds = con.executeDataSet(sql, "Savings Type");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Savings Type";
dataGridView1.Columns[0].Visible = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
else
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT LoanTypeID as [ID], loanTypeName as [Loan Type], minAmount as [Minimum Amount], maxAmount as [Maximum Amount], CONCAT(noOfComaker, ' Members') as [No. of Co-maker], CONCAT(entitlement, ' %') as [Entitlement], CONCAT(eligibility, ' %') as [Eligibility], [status] as [Status] FROM LOANTYPE";
DataSet ds = con.executeDataSet(sql, "Savings Type");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "Savings Type";
dataGridView1.Columns[0].Visible = false;
dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
}
}
else
{
}
}
public override void Run(Context ctx, Schedule schedule)
{
SQLStatement = base.GetStatement(SupplierSQLObject.Supplier);
opearteList = GetOpearteList(SupplierSQLObject.Supplier);
//修改的单独处理
BussnessModify customerModify = new BussnessModify();
customerModify.excuteSQL(model, connectionToRemoteDatabase(), ctx);
if (connectionToRemoteDatabase().Sucessd)
{
ExcuteDataBaseResult excuteDataBaseResult = base.remoteExcuteDataBase.excuteStatement(SQLStatement, connectionToRemoteDatabase().Sqlconn);
supplierSaveInfoObjList = handleData <SupplierInfoSaveObject>(excuteDataBaseResult.Ds);
if (!closeConnetction(connectionToRemoteDatabase().Sqlconn))
{
//打印关闭连接
BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表取数后,关闭数据库连接失败! 请联系系统管理员!");
return;
}
}
else
{
BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表取数前打开数据库连接失败! 请联系系统管理员!");
return;
}
if (LoginAPI(model))
{
Dictionary <string, Result> statusMap = new Dictionary <string, Result>();
foreach (SupplierInfoSaveObject supplierSaveInfoObj in supplierSaveInfoObjList)
{
InvokeReturnHandle <InvokeResult> callResult = (InvokeReturnHandle <InvokeResult>)(object) InvokeAPI(opearteList, supplierSaveInfoObj, InvokeCloudAPI.Login(model), ctx, formID, model);
statusMap.Add(supplierSaveInfoObj.Model.Fnumber, callResult.CustomOpearteObject.Result);
}
List <string> getUpdateSQLStatements = new SQLStatement(model).getUpdateSQLStatement(model, statusMap);
// ExcuteDataBaseResult excuteDataBaseResult = remoteExcuteDataBase.excuteStatement(updateSQLStatement, connectionToRemoteDatabase().Sqlconn);
SqlConnection Sqlconn = connectionToRemoteDatabase().Sqlconn;
foreach (string UpdateSQLStatement in getUpdateSQLStatements)
{
updateMiddleDataBase(UpdateSQLStatement, model, "t_kf_supplier", Sqlconn);
}
if (!closeConnetction(Sqlconn))
{
//打印关闭连接信息
BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表数据更新后,关闭数据库连接失败! 请联系系统管理员!");
return;
}
}
}
private void button2_Click(object sender, EventArgs e)
{
var date = DateTime.Now;
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "INSERT INTO LOAN (MemberID, LoanTypeID, applyAmount, TermID, ModeID, dateApplied) VALUES (@MemID, @LTypeID, @ApplyAmt, @TermID, @ModeID, @dateApplied)";
Dictionary < String, Object > parameters = new Dictionary<string, object>();
parameters.Add("@MemId", Convert.ToInt32(textBox1.Text));
parameters.Add("@LTypeID", Convert.ToInt32(textBox6.Text));
parameters.Add("@ApplyAmt", textBox4.Text);
parameters.Add("@TermID", Convert.ToInt32(textBox7.Text));
parameters.Add("@ModeID", Convert.ToInt32(textBox8.Text));
parameters.Add("@dateApplied", date);
SqlDataReader reader = con.executeReader(sql, parameters);
MessageBox.Show("Loan Application Saved", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void btnStatus_Click(object sender, EventArgs e)
{
if (SLS.Static.ID != 0)
{
if (btnStatus.Text == "DELETE")
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "UPDATE SAVINGSTYPE SET [status] = @status WHERE SavingsTypeID = @SavingsTypeID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@status", false);
parameters.Add("@SavingsTypeID", SLS.Static.ID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("A Savings Type is Updated.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
loadDatabase();
btnStatus.Text = "DELETE";
}
else
{
MessageBox.Show("A Savings Type is Not Updated.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "UPDATE SAVINGSTYPE SET [status] = @status WHERE SavingsTypeID = @SavingsTypeID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@status", true);
parameters.Add("@SavingsTypeID", SLS.Static.ID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("A Savings Type is Updated.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
loadDatabase();
btnStatus.Text = "DELETE";
}
else
{
MessageBox.Show("A Savings Type is Not Updated.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
else
{
MessageBox.Show("Please choose a record to be updated.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public override void Run(Context ctx, Schedule schedule)
{
SQLStatement = base.GetStatement(ReceivableSQLObject.Receivable);
opearteList = GetOpearteList(ReceivableSQLObject.Receivable);
if (connectionToRemoteDatabase().Sucessd)
{
ExcuteDataBaseResult excuteDataBaseResult = base.remoteExcuteDataBase.excuteStatement(SQLStatement, connectionToRemoteDatabase().Sqlconn);
receivableSaveInfoObjList = handleData <ReceivableInfoSaveObject>(excuteDataBaseResult.Ds);
if (!closeConnetction(connectionToRemoteDatabase().Sqlconn))
{
//打印关闭连接
BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表取数后,关闭数据库连接失败! 请联系系统管理员!");
return;
}
}
else
{
if (!closeConnetction(connectionToRemoteDatabase().Sqlconn))
{
//打印关闭连接
BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表取数前打开数据库连接失败! 请联系系统管理员!");
return;
}
}
if (LoginAPI(model))
{
Dictionary <string, Result> statusMap = new Dictionary <string, Result>();
foreach (ReceivableInfoSaveObject receivableInfoSaveObject in receivableSaveInfoObjList)
{
InvokeReturnHandle <InvokeResult> callResult = InvokeAPI(opearteList, receivableInfoSaveObject, InvokeCloudAPI.Login(model), ctx, formID, model);
statusMap.Add(receivableInfoSaveObject.Model.FBillNo, callResult.CustomOpearteObject.Result);
}
List <string> getUpdateSQLStatements = new SQLStatement(model).getUpdateSQLStatement(model, statusMap);
SqlConnection Sqlconn = connectionToRemoteDatabase().Sqlconn;
foreach (string UpdateSQLStatement in getUpdateSQLStatements)
{
updateMiddleDataBase(UpdateSQLStatement, model, "t_kf_receivable", Sqlconn);
}
if (!closeConnetction(Sqlconn))
{
BussnessLog.WriteBussnessLog("", "数据库操作错误", model + "中间表数据更新后,关闭数据库连接失败! 请联系系统管理员!");
return;
}
}
}
public Int32 checkValues()
{
//Required Fields Validation
Int32 isValid = 0;
SLS.Validate.Alpha ctrlString = new SLS.Validate.Alpha();
if (ctrlString.checkString(txtModeName.Text) == 1)
{
isValid = 1;
er1.Visible = true;
}
try
{
Convert.ToInt32(txtDaysInterval.Text);
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT daysInterval FROM MODE";
SqlDataReader reader = con.executeReader(sql);
while(reader.Read())
{
if (SLS.Static.ID == 0)
{
if (Convert.ToInt32(txtDaysInterval.Text) == Convert.ToInt32(reader.GetInt32(0)))
{
er2.Visible = true;
isValid = 1;
}
}
else
{
if(daysInterval != Convert.ToInt32(txtDaysInterval.Text))
{
if (Convert.ToInt32(txtDaysInterval.Text) == Convert.ToInt32(reader[0]))
{
er2.Visible = true;
isValid = 1;
}
}
}
}
}
catch (Exception)
{
isValid = 1;
er2.Visible = true;
}
return isValid;
}
public void ProcessorComplete(TraceData data)
{
//throw new System.NotImplementedException();
if (statements.Count == 0)
{
return;
}
/* Group them all by Where */
var sqlByWhereList = data.SQLByWhere;
var byWheres = statements.Where(p => p.Type != SQLType.INSERT).GroupBy(p => p.WhereClause).Select(g => new SQLByWhere {
NumberOfCalls = g.Count(), TotalTime = g.Sum(i => i.Duration), WhereClause = g.Key, HasError = g.Count(p => p.IsError) > 0 ? true : false
});
foreach (var byW in byWheres)
{
sqlByWhereList.Add(byW);
}
var sqlByFromList = data.SQLByFrom;
var byFroms = statements.Where(p => p.Type == SQLType.SELECT || p.Type == SQLType.DELETE).GroupBy(p => p.FromClause).Select(g => new SQLByFrom {
NumberOfCalls = g.Count(), TotalTime = g.Sum(i => i.Duration), FromClause = g.Key, HasError = g.Count(p => p.IsError) > 0 ? true : false
});
foreach (var byF in byFroms)
{
sqlByFromList.Add(byF);
}
data.Statistics.Add(new StatisticItem()
{
Category = "SQL Statements", Label = "Total Count", Value = statements.Count.ToString()
});
SQLStatement longest = statements.OrderBy(s => s.Duration).Reverse().First();
data.Statistics.Add(new StatisticItem()
{
Category = "SQL Statements", Label = "Longest Execution", Value = longest.Duration.ToString(), Tag = longest
});
SQLStatement mostFetches = statements.OrderBy(s => s.FetchCount).Reverse().First();
data.Statistics.Add(new StatisticItem()
{
Category = "SQL Statements", Label = "Most Fetches", Value = mostFetches.FetchCount.ToString(), Tag = mostFetches
});
}
public void defaultAll()
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT FORMAT(MEMBER.MemberID,'00000000') as [ID], CONCAT(MEMBER.fName,' ',MEMBER.mName,' ',MEMBER.lName) as [Name], DATEDIFF(YEAR, MEMBER.birthDate, '" + (DateTime.Now).ToString("yyyy-MM-dd") + "' ) as [Age] , MEMBERTYPE.MemberTypeID, MEMBERTYPE.MemberTypeName FROM MEMBER, MEMBERTYPE WHERE MEMBER.MemberTypeID = MEMBERTYPE.MemberTypeID AND MEMBER.MemberID = " + SLS.Static.ID + " ";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
MemberID = Convert.ToInt32(reader[0]);
txtMemberID.Text = "MEM - " + Convert.ToString(reader[0]);
txtMemberName.Text = Convert.ToString(reader[1]);
txtMemberType.Text = Convert.ToString(reader[4]);
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT SAVINGSTYPE.SavingsTypeID, SAVINGSTYPE.savingsTypeName FROM SAVINGSTYPE, APPLICABLESAVINGS WHERE SAVINGSTYPE.SavingsTypeID = APPLICABLESAVINGS.SavingsTypeID AND APPLICABLESAVINGS.MemberTypeID = " + Convert.ToString(reader[3]) + " and SAVINGSTYPE.hasDormancy = 'true' and SAVINGSTYPE.[status] = 1";
reader = con.executeReader(sql);
cobSavingsType.Items.Clear();
int i = 0;
while (reader.Read())
{
string str = reader[1].ToString();
cobSavingsType.Items.Insert(i, "" + str);
i += 1;
}
cobSavingsType.Text = "";
cobSavingsType.Enabled = true;
er2.Visible = true;
SavingsID = 0;
}
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT MAX(SavingsAccountID) FROM SAVINGSACCOUNT";
reader = con.executeReader(sql);
try
{
if (reader.HasRows)
{
reader.Read();
txtSavAccount.Text = "SAV - " + (reader.GetInt32(0) + 1).ToString("00000000");
}
}
catch(Exception)
{
txtSavAccount.Text = "SAV - 00000001";
}
txtDate.Text = DateTime.Now.ToLongDateString();
}
public void defaultAll()
{
txtNoDays.Clear();
er1.Visible = false;
if (SLS.Static.ID != 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT noOfDays FROM TERM where TermID = " + SLS.Static.ID + " ";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtNoDays.Text = Convert.ToString(reader[0]);
NoDays = Convert.ToInt32(reader[0]);
}
}
}
public string SQLQueryVerify(string sql_dir_fn, string write_dir_fn, string expect_dir_fn, string[] badQueries)
{
QueryOption option = new QueryOption();
option.optimize_.TurnOnAllOptimizations();
option.optimize_.remove_from_ = false;
option.explain_.show_output_ = true;
option.explain_.show_cost_ = option.optimize_.use_memo_;
// get a list of sql query fine names from the sql directory
string[] sqlFiles = Directory.GetFiles(sql_dir_fn);
// execute the query in each file and and verify the result
foreach (string sqlFn in sqlFiles)
{
string dbg_name = Path.GetFileNameWithoutExtension(sqlFn);
if (badQueries.Contains(dbg_name) == true)
{
continue;
}
// execute query
var sql = File.ReadAllText(sqlFn);
var test_result = SQLStatement.ExecSQLList(sql, option);
// construct file name for result file and write result
string f_name = Path.GetFileNameWithoutExtension(sqlFn);
string write_fn = $@"{write_dir_fn}\{f_name}.txt";
File.WriteAllText(write_fn, test_result);
// construct file name of expected result
string expect_fn = $@"{expect_dir_fn}\{f_name}.txt";
// verify query result against the expected result
if (!resultVerify(write_fn, expect_fn))
{
return(write_fn);
}
}
return(null);
}
static void TestDataSet2()
{
Random rand = new Random();
int inside(int d)
{
var x = rand.NextDouble();
var y = rand.NextDouble();
var ret = x * x + y * y <= 1 ? 1 : 0;
return(ret);
}
SQLContext sqlContext = new SQLContext();
SQLContext.Register <int, int>("inside", inside);
var sql = "SELECT 4.0*sum(inside(a1.a1))/count(*) from a a1, a a2, a a3, a a4, a a5, a a6, a a7, a a8, a a9, a a10";
var rows = SQLStatement.ExecSQL(sql, out string plan, out _);
}
static void TestDataSet()
{
// register c#'s sqrt as an external function
string sqroot(double d) => Math.Sqrt(d).ToString("#.###");
SQLContext sqlContext = new SQLContext();
SQLContext.Register <double, string>("sqroot", sqroot);
var a = sqlContext.Read("a");
var b = sqlContext.Read("b");
a.filter("a1>1").join(b, "b2=a2").select("a1", "sqroot(b1*a1+2)").show();
string s = a.physicPlan_.Explain();
Console.WriteLine(s);
var sql = "SELECT a1, sqroot(b1*a1+2) from a join b on b2=a2 where a1>1";
var rows = SQLStatement.ExecSQL(sql, out string plan, out _);
}
public void defaultAll()
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT FORMAT(MEMBER.MemberID,'00000000') as [ID], CONCAT(MEMBER.fName,' ',MEMBER.mName,' ',MEMBER.lName) as [Name], DATEDIFF(YEAR, MEMBER.birthDate, '" + (DateTime.Now).ToString("yyyy-MM-dd") + "' ) as [Age], MEMBERTYPE.MemberTypeName as [Member Type] FROM MEMBER, MEMBERTYPE WHERE MEMBER.MemberTypeID = MEMBERTYPE.MemberTypeID AND MEMBER.MemberID = " + SLS.Static.ID + " ";
SqlDataReader reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
MemberID = Convert.ToInt32(reader[0]);
txtMemberID.Text = "MEM - " + Convert.ToString(reader[0]);
txtMemberName.Text = Convert.ToString(reader[1]);
txtMemberType.Text = Convert.ToString(reader[3]);
}
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT DISTINCT(TIMEDEPOSITRATES.noOfDays) FROM MEMBERTYPE, TIMEDEPOSITRATES WHERE MEMBERTYPE.TimeApplicable = 1 AND TIMEDEPOSITRATES.[status] = 1";
reader = con.executeReader(sql);
cobNoOfDays.Items.Clear();
int i = 0;
while(reader.Read())
{
string str = reader[0].ToString();
cobNoOfDays.Items.Insert(i, "" + str + " Days");
i += 1;
}
cobNoOfDays.Text = "";
cobNoOfDays.Enabled = true;
er1.Visible = true;
try
{
if (reader.HasRows)
{
reader.Read();
txtTimeAccount.Text = "TIME - " + (reader.GetInt32(0) + 1).ToString("00000000");
}
}
catch (Exception)
{
txtTimeAccount.Text = "TIME - 00000001";
}
txtNow.Text = Convert.ToString(DateTime.Now.ToLongDateString());
}
private void btnStatus_Click(object sender, EventArgs e)
{
if (btnStatus.Text == "DELETE")
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "UPDATE TIMEDEPOSITRATES SET [status] = @status WHERE TimeDepositRatesID = @TimeDepositRatesID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@status", false);
parameters.Add("@TimeDepositRatesID", SLS.Static.ID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("Updating a time deposit rate is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
SLS.Static.ID = 0;
loadDatabase();
}
else
{
MessageBox.Show("Updating a time deposit rate is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "UPDATE TIMEDEPOSITRATES SET [status] = @status WHERE TimeDepositRatesID = @TimeDepositRatesID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@status", true);
parameters.Add("@TimeDepositRatesID", SLS.Static.ID);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("Updating a time deposit rate is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
SLS.Static.ID = 0;
loadDatabase();
}
else
{
MessageBox.Show("Updating a time deposit rate is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
static void TestJobench()
{
var files = Directory.GetFiles(@"../../../jobench");
JOBench.CreateTables();
// make sure all queries can generate phase one opt plan
QueryOption option = new QueryOption();
option.optimize_.TurnOnAllOptimizations();
option.optimize_.memo_use_joinorder_solver_ = true;
foreach (var v in files)
{
var sql = File.ReadAllText(v);
var result = SQLStatement.ExecSQL(sql, out string phyplan, out _, option);
Debug.Assert(result != null);
Debug.Assert(phyplan != null);
Console.WriteLine(v);
Console.WriteLine(phyplan);
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if (SLS.Static.ID == 0)
{
if (checkValues() == 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "INSERT INTO CREDITINVESTIGATOR (fName, mName, lName, [status]) VALUES (@fName, @mName, @lName, @status)";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@fName", txtFN.Text);
parameters.Add("@mName", txtMN.Text);
parameters.Add("@lName", txtLN.Text);
parameters.Add("@status", true);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("Adding a credit investigator is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
else
{
MessageBox.Show("Adding a credit investigator is not successful.", "Error", MessageBoxButtons.OK);
}
}
else
{
MessageBox.Show("Some required fields are missing/invalid!", "Error");
}
}
else
{
if (checkValues() == 0)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "UPDATE CREDITINVESTIGATOR SET fName = @fName, mName = @mName, lName = @lName WHERE ciID = " + SLS.Static.ID + " ";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@fName", txtFN.Text);
parameters.Add("@mName", txtMN.Text);
parameters.Add("@lName", txtLN.Text);
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
if (result == 1)
{
MessageBox.Show("Updating a credit investigator is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
defaultAll();
this.Close();
}
else
{
MessageBox.Show("Updating a credit investigator is not successful.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Some required fields are missing/invalid!", "Error");
}
}
}
public void fillTable()
{
if (SLS.Static.ID == 0)
{
try
{
table.Columns.Add("ChargeID");
table.Columns.Add("Charges");
foreach (object modeChecked in cListMode.CheckedItems)
{
foreach (object termChecked in cListTerm.CheckedItems)
{
table.Columns.Add(new DataColumn("" + modeChecked.ToString() + " - " + termChecked.ToString(), typeof(Decimal)));
}
}
}
catch
{
}
try
{
DataRow Row = table.NewRow();
Row["ChargeID"] = 1;
Row["Charges"] = "Interest On Loan";
table.Rows.Add(Row);
foreach (object chargeChecked in cListCharges.CheckedItems)
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT ChargeID, chargeName FROM CHARGES WHERE chargeName = @chargeName";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@chargeName", chargeChecked.ToString());
SqlDataReader reader = con.executeReader(sql, parameters);
if (reader.HasRows)
{
reader.Read();
Row = table.NewRow();
Row["ChargeID"] = reader[0].ToString();
Row["Charges"] = reader[1].ToString();
table.Rows.Add(Row);
}
}
dataGridView1.DataSource = table;
dataGridView1.Columns[0].Visible = false;
}
catch
{
}
}
else
{
}
}
public void defaultAll()
{
txtLoanType.Clear();
ckbMemType.Checked = false;
cListMemType.Items.Clear();
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "SELECT memberTypeName FROM MEMBERTYPE where LoanApplicable = 1 and [status] = 1 ORDER BY membertypename";
SqlDataReader reader = con.executeReader(sql);
int i = 0;
while(reader.Read())
{
string str = reader[0].ToString();
cListMemType.Items.Insert(i, "" + str);
i += 1;
}
txtMinAmount.Clear();
txtMaxAmount.Clear();
txtNoOfComaker.Clear();
txtEntitlement.Clear();
txtEligibility.Clear();
ckbMode.Checked = false;
cListMode.Items.Clear();
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT modeName FROM MODE where [status] = 1 ORDER BY daysInterval";
reader = con.executeReader(sql);
i = 0;
while (reader.Read())
{
cListMode.Items.Insert(i, "" + Convert.ToString(reader.GetString(0)));
i += 1;
}
ckbTerm.Checked = false;
cListTerm.Items.Clear();
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT noOfDays FROM TERM where [status] = 1 ORDER BY noOfDays";
reader = con.executeReader(sql);
i = 0;
while (reader.Read())
{
cListTerm.Items.Insert(i, "" + Convert.ToString(reader.GetInt32(0)));
i += 1;
}
ckbCharges.Checked = false;
cListCharges.Items.Clear();
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT chargeName FROM CHARGES where [status] = 1 ORDER BY chargeName";
reader = con.executeReader(sql);
i = 0;
while (reader.Read())
{
if (reader.GetString(0) != "Interest On Loan")
{
cListCharges.Items.Insert(i, "" + Convert.ToString(reader.GetString(0)));
i += 1;
}
}
if(SLS.Static.ID != 0)
{
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT LoanTypeID as [ID], loanTypeName as [Loan Type], minAmount as [Minimum Amount], maxAmount as [Maximum Amount], noOfComaker as [No. of Co-maker], entitlement as [Entitlement], eligibility as [Eligibility], [status] as [Status] FROM LOANTYPE WHERE LoanTypeID = " + SLS.Static.ID + " ";
reader = con.executeReader(sql);
if (reader.HasRows)
{
reader.Read();
txtLoanType.Text = Convert.ToString(reader[1]);
txtMinAmount.Text = Convert.ToString(reader[2]);
txtMaxAmount.Text = Convert.ToString(reader[3]);
txtNoOfComaker.Text = Convert.ToString(reader[4]);
txtEntitlement.Text = Convert.ToString(reader[5]);
txtEligibility.Text = Convert.ToString(reader[6]);
}
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT MEMBERTYPE.memberTypeName FROM MEMBERTYPE, APPLICABLELOAN WHERE MEMBERTYPE.MemberTypeID = APPLICABLELOAN.MemberTypeID and APPLICABLELOAN.LoanTypeID = @LoanTypeID";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@LoanTypeID", SLS.Static.ID);
reader = con.executeReader(sql, parameters);
for (i = 0; i < cListMemType.Items.Count; i++)
{
cListMemType.SetItemChecked(i, false);
}
while (reader.Read())
{
int index = cListMemType.Items.IndexOf(reader.GetString(0));
cListMemType.SetItemChecked(index, true);
}
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT DISTINCT(MODE.modeName) FROM MODE, LOANRATE, LOANTYPE WHERE MODE.ModeID = LOANRATE.ModeID and LOANRATE.LoanTypeID = LOANTYPE.LoanTypeID and LOANTYPE.LoanTypeID = @LoanTypeID";
parameters = new Dictionary<string, object>();
parameters.Add("@LoanTypeID", SLS.Static.ID);
reader = con.executeReader(sql, parameters);
for (i = 0; i < cListMode.Items.Count; i++)
{
cListMode.SetItemChecked(i, false);
}
while (reader.Read())
{
int index = cListMode.Items.IndexOf(reader.GetString(0));
cListMode.SetItemChecked(index, true);
}
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT DISTINCT(TERM.noOfDays) FROM TERM, LOANRATE, LOANTYPE WHERE TERM.TermID = LOANRATE.TermID and LOANRATE.LoanTypeID = LOANTYPE.LoanTypeID and LOANTYPE.LoanTypeID = @LoanTypeID";
parameters = new Dictionary<string, object>();
parameters.Add("@LoanTypeID", SLS.Static.ID);
reader = con.executeReader(sql, parameters);
for (i = 0; i < cListTerm.Items.Count; i++)
{
cListTerm.SetItemChecked(i, false);
}
while (reader.Read())
{
int index = cListTerm.Items.IndexOf(Convert.ToString(reader.GetInt32(0)));
cListTerm.SetItemChecked(index, true);
}
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "SELECT DISTINCT(CHARGES.chargeName) FROM CHARGES, LOANRATE, LOANTYPE WHERE CHARGES.ChargeID = LOANRATE.ChargeID and LOANRATE.LoanTypeID = LOANTYPE.LoanTypeID and LOANTYPE.LoanTypeID = @LoanTypeID";
parameters = new Dictionary<string, object>();
parameters.Add("@LoanTypeID", SLS.Static.ID);
reader = con.executeReader(sql, parameters);
for (i = 0; i < cListCharges.Items.Count; i++)
{
cListCharges.SetItemChecked(i, false);
}
while (reader.Read())
{
if (reader.GetString(0) != "Interest On Loan")
{
int index = cListCharges.Items.IndexOf(reader.GetString(0));
cListCharges.SetItemChecked(index, true);
}
}
}
}
//By Lhy 20110505 ITEM 6 ADD END
//By ryzhang 20110516 ITEM 6 ADD Start
/// <summary>
/// set value in dropdownlist
/// </summary>
/// <param name="falg">1、region,2、cluster，3、Country</param>
private void SetParent(int falg)
{
String sql = null;
//BY RYZHANG ITEM6 20110525 MODIFY START
switch (falg)
{
//case 1:
// sql = "SELECT distinct ID, Name FROM [Region] WHERE Deleted = 0";
// break;
//case 2:
// sql = "SELECT distinct ID, Name FROM [Cluster] WHERE Deleted = 0";
// break;
//case 3:
// sql = "SELECT distinct ID, Name FROM [Country] WHERE Deleted = 0";
// break;
//default:
// return;
case 1:
sql = "SELECT distinct ID, Name FROM [Region] WHERE Deleted = 0 ORDER BY NAME";
break;
case 2:
sql = "SELECT distinct ID, Name FROM [Cluster] WHERE Deleted = 0 ORDER BY NAME";
break;
case 3:
sql = "SELECT distinct ID, Name FROM [Country] WHERE Deleted = 0 ORDER BY NAME";
break;
default:
return;
}
//BY RYZHANG ITEM6 20110525 MODIFY END
DataSet ds = helper.GetDataSet(sql);
ddlist_parent.Items.Clear();
if (ds.Tables.Count!=0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
ddlist_parent.Items.Add(new ListItem(row[1].ToString(), row[0].ToString()));
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if(SLS.Static.ID == 0)
{
if(checkValues() == 1)
{
MessageBox.Show("Some required fields are missing or invalid.", "Error");
}
else
{
SQLStatement con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
String sql = "INSERT INTO LOANTYPE (loanTypeName, minAmount, maxAmount, noOfComaker, entitlement, eligibility, [status]) VALUES (@loanTypeName, @minAmount, @maxAmount, @noOfComaker, @entitlement, @eligibility, @status); SELECT CAST(scope_identity() AS int)";
Dictionary<String, Object> parameters = new Dictionary<string, object>();
parameters.Add("@loanTypeName", txtLoanType.Text);
parameters.Add("@minAmount", txtMinAmount.Text);
parameters.Add("@maxAmount", txtMaxAmount.Text);
parameters.Add("@noOfComaker", txtNoOfComaker.Text);
parameters.Add("@entitlement", txtEntitlement.Text);
parameters.Add("@eligibility", txtEligibility.Text);
parameters.Add("@status", true);
SqlDataReader reader = con.executeReader(sql, parameters);
if (reader.HasRows)
{
reader.Read();
MessageBox.Show("Adding a loan type is successful.", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
foreach (object itemChecked in cListMemType.CheckedItems)
{
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "INSERT INTO APPLICABLELOAN (LoanTypeID, MemberTypeID) VALUES (@LoanTypeID,(SELECT MemberTypeID FROM MEMBERTYPE WHERE memberTypeName = @memberTypeName))";
parameters = new Dictionary<string, object>();
parameters.Add("@LoanTypeID", reader[0]);
parameters.Add("@memberTypeName", itemChecked.ToString());
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
}
modeArr = new String[cListMode.Items.Count];
int sInd = 0;
for (int i = 0; i < cListMode.Items.Count; i++)
{
if (cListMode.GetItemCheckState(i) == CheckState.Checked)
{
modeArr[sInd] = Convert.ToString(cListMode.Items[i]);
sInd++;
}
}
termArr = new Int32[cListTerm.Items.Count];
sInd = 0;
for (int i = 0; i < cListTerm.Items.Count; i++)
{
if (cListTerm.GetItemCheckState(i) == CheckState.Checked)
{
termArr[sInd] = Convert.ToInt32(cListTerm.Items[i].ToString());
sInd++;
}
}
for (int j = 0; j <= dataGridView1.Rows.Count - 1; j++)
{
for (int k = 2; k <= dataGridView1.Columns.Count - 1; k++)
{
con = new SQLStatement(SLS.Static.Server, SLS.Static.Database);
sql = "INSERT INTO LOANRATE (ModeID, ChargeID, TermID, LoanTypeID, rate) VALUES ((SELECT ModeID FROM MODE WHERE modeName = @modeName AND [status] = 1), @ChargeID, (SELECT TermID FROM TERM WHERE noOfDays = @noOfDays AND [status] = 1), @LoanTypeID, @rate)";
parameters = new Dictionary<string, object>();
parameters.Add("@modeName", modeArr[(k - 2) / cListTerm.CheckedItems.Count]);
parameters.Add("@ChargeID", Convert.ToInt32(dataGridView1.Rows[j].Cells[0].Value.ToString()));
parameters.Add("@noOfDays", termArr[(k - 2) / cListMode.CheckedItems.Count]);
parameters.Add("@LoanTypeID", reader[0]);
parameters.Add("@rate", Convert.ToDecimal(dataGridView1.Rows[j].Cells[k].Value.ToString()));
int result = Convert.ToInt32(con.executeNonQuery(sql, parameters));
}
}
this.Close();
}
else
{
MessageBox.Show("Adding a loan type is not successful.", "Error", MessageBoxButtons.OK);
}
}
}
}
private void delSubRegion(string id, string name)
{
//By Lhy 20110513 ITEM 6 ADD Start
null_input();
//By Lhy 20110513 ITEM 6 ADD Start
string sql = "UPDATE [SubRegion] SET Deleted = 1 WHERE ID = " + id;
int count = helper.ExecuteNonQuery(CommandType.Text, sql, null);
if (count == 1)
{
//By ryzhang 20110516 ITEM 6 ADD Start
sql = "UPDATE [Country_SubRegion] SET Deleted = 1 WHERE SubRegionID = " + id;
helper.ExecuteNonQuery(CommandType.Text, sql, null);
//By ryzhang 20110516 ITEM 6 ADD End
lbl_del.ForeColor = System.Drawing.Color.Green;
lbl_del.Text = info.delLabelInfo(name, true);
}
else
{
lbl_del.ForeColor = System.Drawing.Color.Red;
lbl_del.Text = info.delLabelInfo(name, false);
}
}
private void addSubRegion(string name)
{
//By Lhy 20110513 ITEM 6 ADD Start
null_input();
//By Lhy 20110513 ITEM 6 ADD Start
lbl_add.ForeColor = System.Drawing.Color.Red;
if (name != "")
{
if (!existSubRegion(name))
{
string insert_subregion = "INSERT INTO [SubRegion](Name,Deleted) VALUES('" + name + "',0)";
int count = helper.ExecuteNonQuery(CommandType.Text, insert_subregion, null);
if (count == 1)
{
//By ryzhang 20110516 ITEM 6 ADD Start
string sql = String.Format(@"SELECT ID FROM [SubRegion] WHERE Name = '{0}' AND Deleted = 0", name);
DataSet ds = helper.GetDataSet(sql);
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
return;
}
sql = String.Format(@"INSERT INTO [Country_SubRegion] (CountryID,SubRegionID,Deleted) VALUES ({0},{1},0)", ddlist_parent.SelectedItem.Value, ds.Tables[0].Rows[0][0].ToString());
helper.ExecuteNonQuery(CommandType.Text, sql, null);
//By ryzhang 20110516 ITEM 6 ADD End
lbl_add.ForeColor = System.Drawing.Color.Green;
lbl_add.Text = info.addLabelInfo(name, true);
}
else
lbl_add.Text = info.addLabelInfo(name, false);
}
else
lbl_add.Text = info.addExist(name);
}
else
lbl_add.Text = info.addillegal();
}
//By Lhy 20110505 ITEM 6 DEL Start
/*protected DataSet getOptionInfo(int sel)
{
string sql = "";
DataSet ds = null;
if (sel == 0)
{
sql = "SELECT ID, Name FROM [Region] WHERE Deleted = 0 GROUP BY Name, ID ORDER BY Name, ID ASC";
ds = helper.GetDataSet(sql);
}
else if (sel == 1)
{
sql = " SELECT ID, Name FROM [Cluster] WHERE Deleted = 0 GROUP BY Name, ID ORDER BY Name, ID ASC";
ds = helper.GetDataSet(sql);
}
else if (sel == 2)
{
sql = " SELECT ID, Name, ISO_Code FROM [Country] WHERE Deleted = 0 GROUP BY Name, ISO_Code, ID ORDER BY Name, ISO_Code, ID ASC";
ds = helper.GetDataSet(sql);
}
else if (sel == 3)
{
sql = " SELECT ID, Name FROM [SubRegion] WHERE Deleted = 0 GROUP BY Name, ID ORDER BY Name, ID ASC";
ds = helper.GetDataSet(sql);
}
return ds;
}
*/
//By Lhy 20110505 ITEM 6 DEL Start
//By Lhy 20110505 ITEM 6 ADD Start
protected DataSet getOptionInfo(int sel, string text)
{
string sql = "";
DataSet ds = null;
if (sel == 0)
{
sql = "SELECT ID, Name FROM [Region] WHERE Deleted = 0 AND Name like '%" + text + "%' GROUP BY Name, ID ORDER BY Name, ID ASC ";
ds = helper.GetDataSet(sql);
}
else if (sel == 1)
{
sql = " SELECT ID, Name FROM [Cluster] WHERE Deleted = 0 AND Name like '%" + text + "%'GROUP BY Name, ID ORDER BY Name, ID ASC";
ds = helper.GetDataSet(sql);
}
else if (sel == 2)
{
sql = " SELECT ID, Name, ISO_Code FROM [Country] WHERE Deleted = 0 AND Name like '%" + text + "%'GROUP BY Name, ISO_Code, ID ORDER BY Name, ISO_Code, ID ASC";
ds = helper.GetDataSet(sql);
}
else if (sel == 3)
{
sql = " SELECT ID, Name FROM [SubRegion] WHERE Deleted = 0 AND Name like '%" + text + "%'GROUP BY Name, ID ORDER BY Name, ID ASC";
ds = helper.GetDataSet(sql);
}
return ds;
}
protected void btn_modify_parent_Click(object sender, EventArgs e)
{
this.lbl_del.Text = "";
lbl_modify_parent_error.Text = "";
if (ddlist_modify_parent.SelectedItem.Value=="")
{
lbl_modify_parent_error.ForeColor = System.Drawing.Color.Red;
switch (this.optionName.Value)
{
case "Cluster":
lbl_modify_parent_error.Text = "Please select one Region!";
break;
case "Country":
lbl_modify_parent_error.Text = "Please select one Cluster!";
break;
case "SubRegion":
lbl_modify_parent_error.Text = "Please select one Country!";
break;
default:
break;
}
}
else
{
String sql = null;
switch (this.optionName.Value)
{
case "Cluster":
sql = String.Format(@"UPDATE [Region_Cluster] SET RegionID = {0},Deleted=0 WHERE ClusterID={1}", ddlist_modify_parent.SelectedItem.Value, this.modify_option_id.Value);
break;
case "Country":
sql = String.Format(@"UPDATE [Cluster_Country] SET ClusterID = {0},Deleted=0 WHERE CountryID={1}", ddlist_modify_parent.SelectedItem.Value, this.modify_option_id.Value);
break;
case "SubRegion":
sql = String.Format(@"UPDATE [Country_SubRegion] SET CountryID = {0},Deleted=0 WHERE SubRegionID={1}", ddlist_modify_parent.SelectedItem.Value, this.modify_option_id.Value);
break;
default:
break;
}
helper.ExecuteNonQuery(CommandType.Text, sql, null);
lbl_modify_parent_error.ForeColor = System.Drawing.Color.Green;
lbl_modify_parent_error.Text = @"Successfully modified!";
}
}
protected DataSet getBookingDataByCountry(string str_ClusterID, string str_segmentID)
{
/////////////MONTH 10月份吗
string sql = "";
if (str_segmentID != "-1")
{
sql = "SELECT [Country].Name, "
+ " SUM(CASE WHEN BookingY = '" + yearBeforePre.Substring(2, 2) + "' and YEAR(TimeFlag) = '" + yearBeforePre + "' and MONTH(TimeFlag) = '10' THEN Amount ELSE 0 END) AS 'FY " + yearBeforePre + "',"
+ " SUM(CASE WHEN BookingY = '" + preyear.Substring(2, 2) + "' and YEAR(TimeFlag) = '" + preyear + "' and MONTH(TimeFlag) = '10' THEN Amount ELSE 0 END) AS 'FY " + preyear + "',"
+ " SUM(CASE WHEN BookingY = '" + year.Substring(2, 2) + "' and YEAR(TimeFlag) = '" + year + "' and MONTH(TimeFlag) = '10' THEN Amount ELSE 0 END) AS 'FY " + year + "',"
+ " SUM(CASE WHEN BookingY = '" + nextyear.Substring(2, 2) + "' and YEAR(TimeFlag) = '" + year + "' and MONTH(TimeFlag) = '10' THEN Amount ELSE 0 END) AS 'FY " + nextyear + "' "
+ " FROM [Bookings]"
+ " INNER JOIN [Country] ON [Country].ID = [Bookings].CountryID"
+ " WHERE CountryID IN (SELECT CountryID FROM [Cluster_Country] WHERE ClusterID = " + str_ClusterID + " AND Deleted=0)"
+ " AND SegmentID = " + str_segmentID
+ " AND [Country].Deleted=0 "
+ " GROUP BY [Country].Name"
+ " ORDER BY [Country].Name";
}
else
{
sql = "SELECT [Country].Name, "
+ " SUM(CASE WHEN BookingY = '" + yearBeforePre.Substring(2, 2) + "' and YEAR(TimeFlag) = '" + yearBeforePre + "' and MONTH(TimeFlag) = '10' THEN Amount ELSE 0 END) AS 'FY " + yearBeforePre + "',"
+ " SUM(CASE WHEN BookingY = '" + preyear.Substring(2, 2) + "' and YEAR(TimeFlag) = '" + preyear + "' and MONTH(TimeFlag) = '10' THEN Amount ELSE 0 END) AS 'FY " + preyear + "',"
+ " SUM(CASE WHEN BookingY = '" + year.Substring(2, 2) + "' and YEAR(TimeFlag) = '" + year + "' and MONTH(TimeFlag) = '10' THEN Amount ELSE 0 END) AS 'FY " + year + "',"
+ " SUM(CASE WHEN BookingY = '" + nextyear.Substring(2, 2) + "' and YEAR(TimeFlag) = '" + year + "' and MONTH(TimeFlag) = '10' THEN Amount ELSE 0 END) AS 'FY " + nextyear + "' "
+ " FROM [Bookings]"
+ " INNER JOIN [Country] ON [Country].ID = [Bookings].CountryID"
+ " WHERE CountryID IN (SELECT CountryID FROM [Cluster_Country] WHERE ClusterID = " + str_ClusterID + " AND Deleted=0)"
+ " AND [Country].Deleted=0 "
+ " GROUP BY [Country].Name"
+ " ORDER BY [Country].Name";
}
DataSet ds = helper.GetDataSet(sql);
if (ds.Tables[0].Rows.Count > 0)
return ds;
else
return null;
}
