public void SetUp ()
{
_stageMock = MockRepository.GenerateStrictMock<ISqlGenerationStage>();
_commandBuilder = new SqlCommandBuilder();
_leftIntegerExpression = Expression.Constant (1);
_rightIntegerExpression = Expression.Constant (2);
}
public void SetUp ()
{
var sqlTable = SqlStatementModelObjectMother.CreateSqlTable_WithResolvedTableInfo();
_entityExpression = new SqlEntityDefinitionExpression (
typeof (string),
"t",
null,
e => e.GetColumn (typeof (string), "ID", true),
new SqlColumnExpression[]
{
new SqlColumnDefinitionExpression (typeof (string), "t", "ID", true),
new SqlColumnDefinitionExpression (typeof (int), "t", "Name", false),
new SqlColumnDefinitionExpression (typeof (int), "t", "City", false)
});
_sqlStatement = new SqlStatement (
new TestStreamedValueInfo (typeof (int)),
_entityExpression,
new[] { sqlTable },
null,
null,
new Ordering[] { },
null,
false,
null,
null);
_commandBuilder = new SqlCommandBuilder();
}
public void Generate ()
{
var commandBuilder = new SqlCommandBuilder();
var visitor = MockRepository.GenerateMock<ExpressionTreeVisitor>();
var stage = MockRepository.GenerateMock<ISqlGenerationStage>();
_sqlCustomTextExpression.Generate (commandBuilder, visitor, stage);
Assert.That (commandBuilder.GetCommandText(), Is.EqualTo ("test"));
}
/// <summary>
/// 获取选择语句内容
/// </summary>
/// <param name="baseCommand">源选择语句</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择语句内容</returns>
internal static String InternalGetPagerCommand(SelectCommand baseCommand, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(baseCommand.Database);
sb.AppendSelectPrefix();
sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.QueryFields);
if (baseCommand.PageSize > 0 && baseCommand.RecordStart > 0)//分页模式
{
/*
SELECT * FROM
(
SELECT *, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME)
WHERE ROWNUM <= 30
)
WHERE RN > 20
*/
SelectCommand innestCommand = new SelectCommand(baseCommand.Database, baseCommand.TableName);
innestCommand.QueryFields = baseCommand.QueryFields;
innestCommand.SqlJoins = baseCommand.SqlJoins;
innestCommand.SqlWhere = baseCommand.SqlWhere;
innestCommand.GroupByColumns = baseCommand.GroupByColumns;
innestCommand.SqlHaving = baseCommand.SqlHaving;
innestCommand.SqlOrders = baseCommand.SqlOrders;
SelectCommand innerCommand = new SelectCommand(baseCommand.Database, innestCommand, "");
innerCommand.SqlWhere = SqlCondition.LessThanOrEqualColumn(innerCommand, "ROWNUM", (baseCommand.RecordStart + baseCommand.PageSize).ToString());
innerCommand.InternalQuerys(baseCommand.QueryFields.ToArray());
innerCommand.InternalQuerys(SqlQueryField.InternalCreateFromFunction(baseCommand, "ROWNUM", "RN"));
sb.AppendSelectFrom(innerCommand.GetCommandText(), true);
sb.AppendWhere(SqlCondition.GreaterThanColumn(baseCommand, "RN", baseCommand.RecordStart.ToString()));
}
else//正常模式
{
sb.AppendSelectFromAndJoins(baseCommand.TableName, baseCommand.IsFromSql, baseCommand.SqlJoins);
ISqlCondition where = baseCommand.SqlWhere;
if (baseCommand.PageSize > 0)
{
where = SqlCondition.And(baseCommand, where, SqlCondition.LessThanOrEqualColumn(baseCommand, "ROWNUM", baseCommand.PageSize.ToString()));
}
sb.AppendWhere(where);
sb.AppendSelectGroupBys(baseCommand.GroupByColumns);
sb.AppendHaving(baseCommand.SqlHaving);
sb.AppendSelectOrderBys(baseCommand.SqlOrders, orderReverse);
}
return sb.ToString();
}
/// <summary>
/// 获取选择语句内容
/// </summary>
/// <param name="baseCommand">源选择语句</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择语句内容</returns>
internal static String InternalGetPagerCommand(SelectCommand baseCommand, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(baseCommand.Database);
sb.AppendSelectPrefix();
if (baseCommand.PageSize > 0 && baseCommand.RecordStart > 0)//分页模式
{
/*
SELECT * FROM
(
SELECT TOP 30 *, ROW_NUMBER() OVER(ORDER BY ID ASC) AS RN
FROM TABLE_NAME
) AS T
WHERE RN > 20
*/
sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.QueryFields);
SqlCommandBuilder innerBuilder = new SqlCommandBuilder(baseCommand.Database);
innerBuilder.AppendSelectOrderBys(baseCommand.SqlOrders, false);
SelectCommand innerCommand = new SelectCommand(baseCommand.Database, baseCommand.TableName);
innerCommand.InternalQuerys(baseCommand.QueryFields.ToArray());
innerCommand.InternalQuerys(SqlQueryField.InternalCreateFromFunction(baseCommand, "ROW_NUMBER() OVER( " + innerBuilder.ToString() + ")", "RN"));
innerCommand.PageSize = baseCommand.RecordStart + baseCommand.PageSize;
innerCommand.SqlJoins = baseCommand.SqlJoins;
innerCommand.SqlWhere = baseCommand.SqlWhere;
innerCommand.GroupByColumns = baseCommand.GroupByColumns;
innerCommand.SqlHaving = baseCommand.SqlHaving;
sb.AppendSelectFrom(innerCommand.GetCommandText("T"), true);
sb.AppendWhere(SqlCondition.GreaterThanColumn(baseCommand, "RN", baseCommand.RecordStart.ToString()));
}
else//正常模式
{
if (baseCommand.PageSize > 0)
{
sb.AppendSelectTop(baseCommand.PageSize);
}
sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.QueryFields);
sb.AppendSelectFromAndJoins(baseCommand.TableName, baseCommand.IsFromSql, baseCommand.SqlJoins);
sb.AppendWhere(baseCommand.SqlWhere);
sb.AppendSelectGroupBys(baseCommand.GroupByColumns);
sb.AppendHaving(baseCommand.SqlHaving);
sb.AppendSelectOrderBys(baseCommand.SqlOrders, orderReverse);
}
return sb.ToString();
}
public void Generate ()
{
var visitor = MockRepository.GeneratePartialMock<TestableExpressionTreeVisitor>();
var commandBuilder = new SqlCommandBuilder();
visitor
.Expect (mock => mock.VisitExpression (_expression1))
.Return (_expression1);
visitor
.Expect (mock => mock.VisitExpression (_expression2))
.Return (_expression2);
visitor.Replay();
_sqlCompositeCustomTextGeneratorExpression.Generate (commandBuilder, visitor, MockRepository.GenerateStub<ISqlGenerationStage>());
visitor.VerifyAllExpectations();
}
/// <summary>
/// 获取选择语句内容
/// </summary>
/// <param name="cmd">选择语句类</param>
/// <param name="realPageIndex">实际页索引</param>
/// <param name="realPageCount">实际页数量</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择语句内容</returns>
internal static String InternalGetSelectCommand(SelectCommand cmd, Int32 realPageIndex, Int32 realPageCount, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(cmd.DatabaseType);
sb.AppendSelectPrefix();
sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields);
if (cmd.PageSize == 0)//正常模式
{
sb.AppendSelectFromAndJoins(cmd.TableName, cmd.IsFromSql, cmd.SqlJoins);
sb.AppendWhere(cmd.SqlWhere);
sb.AppendSelectGroupBys(cmd.GroupByColumns);
sb.AppendHaving(cmd.SqlHaving);
sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse);
}
else//分页模式
{
/*
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
*/
SelectCommand innestCommand = new SelectCommand(cmd.Database, cmd.TableName);
innestCommand.QueryFields = cmd.QueryFields;
innestCommand.SqlJoins = cmd.SqlJoins;
innestCommand.SqlWhere = cmd.SqlWhere;
innestCommand.GroupByColumns = cmd.GroupByColumns;
innestCommand.SqlHaving = cmd.SqlHaving;
innestCommand.SqlOrders = cmd.SqlOrders;
SelectCommand innerCommand = new SelectCommand(cmd.Database, innestCommand, "T");
innerCommand.SqlWhere = SqlCondition.LessThanOrEqual("ROWNUM", realPageIndex * cmd.PageSize);
innerCommand.InternalQuerys(cmd.QueryFields.ToArray());
innerCommand.InternalQuerys(SqlQueryField.InternalCreateFromFunction("ROWNUM", "RN"));
sb.AppendSelectFrom(innerCommand.ToString("T"), true);
sb.AppendWhere(SqlCondition.GreaterThanOrEqual("RN", (realPageIndex - 1) * cmd.PageSize + 1));
}
return sb.ToString();
}
public void SetUp ()
{
_stageMock = MockRepository.GenerateStrictMock<ISqlGenerationStage> ();
_commandBuilder = new SqlCommandBuilder ();
_visitor = new TestableSqlGeneratingOuterSelectExpressionVisitor (_commandBuilder, _stageMock);
_namedIntExpression = new NamedExpression ("test", Expression.Constant (0));
_nameColumnExpression = new SqlColumnDefinitionExpression (typeof (string), "c", "Name", false);
_entityExpression = new SqlEntityDefinitionExpression (
typeof (Cook),
"c",
"test",
e => e,
new SqlColumnDefinitionExpression (typeof (int), "c", "ID", true),
_nameColumnExpression,
new SqlColumnDefinitionExpression (typeof (string), "c", "FirstName", false)
);
}
public void SetUp ()
{
_sqlTable = SqlStatementModelObjectMother.CreateSqlTable_WithResolvedTableInfo();
_entityExpression = new SqlEntityDefinitionExpression (
typeof(string),
"t", null,
e => e.GetColumn (typeof (int), "ID", true),
new SqlColumnExpression[]
{
new SqlColumnDefinitionExpression (typeof (int), "t", "ID", true),
new SqlColumnDefinitionExpression (typeof (int), "t", "Name", false),
new SqlColumnDefinitionExpression (typeof (int), "t", "City", false)
});
_stageMock = MockRepository.GenerateStrictMock<ISqlGenerationStage>();
_generator = new TestableSqlStatementTextGenerator (_stageMock);
_commandBuilder = new SqlCommandBuilder();
}
/// <summary>
/// 获取选择语句内容
/// </summary>
/// <param name="cmd">选择语句类</param>
/// <param name="realPageIndex">实际页索引</param>
/// <param name="realPageCount">实际页数量</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择语句内容</returns>
internal static String InternalGetSelectCommand(SelectCommand cmd, Int32 realPageIndex, Int32 realPageCount, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(cmd.DatabaseType);
sb.AppendSelectPrefix();
sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields);
sb.AppendSelectFromAndJoins(cmd.TableName, cmd.IsFromSql, cmd.SqlJoins);
sb.AppendWhere(cmd.SqlWhere);
sb.AppendSelectGroupBys(cmd.GroupByColumns);
sb.AppendHaving(cmd.SqlHaving);
sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse);
if (cmd.PageSize > 0 && cmd.PageIndex == 1)
{
sb.AppendSelectLimit(cmd.PageSize);
}
else if (cmd.PageSize > 0 && cmd.PageIndex > 1)
{
sb.AppendSelectLimit(cmd.PageSize * (realPageIndex - 1), cmd.PageSize);
}
return sb.ToString();
}
/// <summary>
/// 获取选择语句内容
/// </summary>
/// <param name="baseCommand">源选择语句</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择语句内容</returns>
internal static String InternalGetPagerCommand(SelectCommand baseCommand, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(baseCommand.Database);
sb.AppendSelectPrefix();
sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.QueryFields);
sb.AppendSelectFromAndJoins(baseCommand.TableName, baseCommand.IsFromSql, baseCommand.SqlJoins);
sb.AppendWhere(baseCommand.SqlWhere);
sb.AppendSelectGroupBys(baseCommand.GroupByColumns);
sb.AppendHaving(baseCommand.SqlHaving);
sb.AppendSelectOrderBys(baseCommand.SqlOrders, orderReverse);
if (baseCommand.PageSize > 0 && baseCommand.RecordStart <= 0)
{
sb.AppendSelectLimit(baseCommand.PageSize);
}
else if (baseCommand.PageSize > 0 && baseCommand.RecordStart > 0)
{
sb.AppendSelectLimit(baseCommand.RecordStart, baseCommand.PageSize);
}
return sb.ToString();
}
//
private static void SqlWrite(string sUnid)
{
System.Text.UTF8Encoding str = new System.Text.UTF8Encoding();
try
{
SqlCommand cmd2;
byte[] sB1;
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
cmd2 = new SqlCommand("usp_Rtv_Parms", cn);
cmd2.CommandType = CommandType.StoredProcedure;
string sXmlDoc;
SqlDataReader dr;
SqlCommandBuilder.DeriveParameters(cmd2);
cmd2.Parameters["@unid"].Value = sUnid;
dr = cmd2.ExecuteReader();
dr.Read();
sXmlDoc = (string)dr.GetString(0);
dr.Close();
dr.Dispose();
string sXmlDoce;
sB1 = str.GetBytes(sXmlDoc);
// sB1 = str.GetBytes(sXmlDoc);
sXmlDoce = Convert.ToBase64String(sB1);
sB1 = str.GetBytes(sXmlDoce);
// sB1 = str.GetBytes(sXmlDoc);
BasicFtp.BasicFTPClient bftp = new
BasicFtp.BasicFTPClient(
ConfigurationManager.AppSettings["webusername"]
, ConfigurationManager.AppSettings["webpassword"]
, ConfigurationManager.AppSettings["webhost"]);
string sPath = @"/XML/" + sUnid;
// if missing build it!
try
{
bftp.MakeDir(sPath);
}
catch
{ }
sPath += @"/Cust.Xml";
bftp.Username = "******";
bftp.Password = "******";
bftp.Host = @"ccisupportsite.com";
bftp.UploadData(sPath, sB1);
}
catch (Exception ex)
{ throw ex; }
}
// Sauvegarde tous les changements effectué dans le dataset
public void SaveDataSet(string tableName, DataSet dataSet)
{
if (dataSet.HasChanges() == false)
{
return;
}
switch (connType)
{
case ConnectionType.DATABASE_MSSQL:
{
try
{
var conn = new SqlConnection(connString);
var adapter = new SqlDataAdapter("SELECT * from " + tableName, conn);
var builder = new SqlCommandBuilder(adapter);
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
lock (dataSet) // lock dataset to prevent changes to it
{
adapter.ContinueUpdateOnError = true;
DataSet changes = dataSet.GetChanges();
adapter.Update(changes, tableName);
PrintDatasetErrors(changes);
dataSet.AcceptChanges();
}
conn.Close();
}
catch (Exception ex)
{
throw new DatabaseException("Can not save table " + tableName, ex);
}
break;
}
case ConnectionType.DATABASE_ODBC:
{
try
{
var conn = new OdbcConnection(connString);
var adapter = new OdbcDataAdapter("SELECT * from " + tableName, conn);
var builder = new OdbcCommandBuilder(adapter);
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
DataSet changes;
lock (dataSet) // lock dataset to prevent changes to it
{
adapter.ContinueUpdateOnError = true;
changes = dataSet.GetChanges();
adapter.Update(changes, tableName);
dataSet.AcceptChanges();
}
PrintDatasetErrors(changes);
conn.Close();
}
catch (Exception ex)
{
throw new DatabaseException("Can not save table ", ex);
}
break;
}
case ConnectionType.DATABASE_MYSQL:
{
return;
}
case ConnectionType.DATABASE_OLEDB:
{
try
{
var conn = new OleDbConnection(connString);
var adapter = new OleDbDataAdapter("SELECT * from " + tableName, conn);
var builder = new OleDbCommandBuilder(adapter);
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
DataSet changes;
lock (dataSet) // lock dataset to prevent changes to it
{
adapter.ContinueUpdateOnError = true;
changes = dataSet.GetChanges();
adapter.Update(changes, tableName);
dataSet.AcceptChanges();
}
PrintDatasetErrors(changes);
conn.Close();
}
catch (Exception ex)
{
throw new DatabaseException("Can not save table", ex);
}
break;
}
}
}
/// <summary>
/// 获取选择语句内容
/// </summary>
/// <param name="baseCommand">源选择语句</param>
/// <param name="recordCount">记录数量</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择语句内容</returns>
internal static String InternalGetPagerCommand(SelectCommand baseCommand, Int32 recordCount, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(baseCommand.Database);
sb.AppendSelectPrefix();
if (baseCommand.PageSize > 0 && baseCommand.RecordStart > 0)//分页模式
{
Int32 realPageCount = (recordCount + baseCommand.PageSize - 1) / baseCommand.PageSize;
Int32 realPageIndex = (baseCommand.RecordStart / baseCommand.PageSize) + 1;
if (realPageIndex >= realPageCount)//最后一页
{
sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.QueryFields);
SelectCommand innerCommand = new SelectCommand(baseCommand.Database, baseCommand.TableName);
innerCommand.QueryFields = baseCommand.QueryFields;
innerCommand.PageSize = recordCount - baseCommand.RecordStart;
innerCommand.SqlJoins = baseCommand.SqlJoins;
innerCommand.SqlWhere = baseCommand.SqlWhere;
innerCommand.GroupByColumns = baseCommand.GroupByColumns;
innerCommand.SqlHaving = baseCommand.SqlHaving;
innerCommand.SqlOrders = baseCommand.SqlOrders;
sb.AppendSelectFrom(innerCommand.GetCommandText("T", !orderReverse), true);
sb.AppendSelectOrderBys(baseCommand.SqlOrders, orderReverse);
}
else if (realPageIndex < (realPageCount / 2 + realPageCount % 2))//前1/2部分页
{
/*
SELECT * FROM
(
SELECT TOP 10 * FROM
(
SELECT TOP 30 *
FROM TABLE_NAME
ORDER BY ID ASC
) AS T1
ORDER BY ID DESC
) AS T2
ORDER BY ID ASC
*/
sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.QueryFields);
SelectCommand innestCommand = new SelectCommand(baseCommand.Database, baseCommand.TableName);
innestCommand.QueryFields = baseCommand.QueryFields;
innestCommand.PageSize = baseCommand.RecordStart + baseCommand.PageSize;
innestCommand.SqlJoins = baseCommand.SqlJoins;
innestCommand.SqlWhere = baseCommand.SqlWhere;
innestCommand.GroupByColumns = baseCommand.GroupByColumns;
innestCommand.SqlHaving = baseCommand.SqlHaving;
innestCommand.SqlOrders = baseCommand.SqlOrders;
SelectCommand innerCommand = new SelectCommand(baseCommand.Database, innestCommand, "T1");
innerCommand.QueryFields = baseCommand.QueryFields;
innerCommand.PageSize = baseCommand.PageSize;
innerCommand.SqlOrders = baseCommand.SqlOrders;
sb.AppendSelectFrom(innerCommand.GetCommandText("T2", !orderReverse), true);
sb.AppendSelectOrderBys(baseCommand.SqlOrders, orderReverse);
}
else//后1/2部分页
{
sb.AppendSelectTop(baseCommand.PageSize);
sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.QueryFields);
SelectCommand innerCommand = new SelectCommand(baseCommand.Database, baseCommand.TableName);
innerCommand.QueryFields = baseCommand.QueryFields;
innerCommand.PageSize = recordCount - baseCommand.RecordStart;
innerCommand.SqlJoins = baseCommand.SqlJoins;
innerCommand.SqlWhere = baseCommand.SqlWhere;
innerCommand.GroupByColumns = baseCommand.GroupByColumns;
innerCommand.SqlHaving = baseCommand.SqlHaving;
innerCommand.SqlOrders = baseCommand.SqlOrders;
sb.AppendSelectFrom(innerCommand.GetCommandText("T", !orderReverse), true);
sb.AppendSelectOrderBys(baseCommand.SqlOrders, orderReverse);
}
}
else//正常模式
{
if (baseCommand.PageSize > 0)
{
sb.AppendSelectTop(baseCommand.PageSize);
}
sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.QueryFields);
sb.AppendSelectFromAndJoins(baseCommand.TableName, baseCommand.IsFromSql, baseCommand.SqlJoins);
sb.AppendWhere(baseCommand.SqlWhere);
sb.AppendSelectGroupBys(baseCommand.GroupByColumns);
sb.AppendHaving(baseCommand.SqlHaving);
sb.AppendSelectOrderBys(baseCommand.SqlOrders, orderReverse);
}
return sb.ToString();
}
public void SetUp()
{
_commandBuilder = new SqlCommandBuilder();
_expressionVisitorMock = MockRepository.GenerateStrictMock <ExpressionVisitor>();
_leftIntegerExpression = Expression.Constant(1);
_expressionVisitorMock
.Stub(stub => stub.Visit(_leftIntegerExpression))
.WhenCalled(mi => _commandBuilder.Append("left"))
.Return(_leftIntegerExpression);
_rightIntegerExpression = Expression.Constant(2);
_expressionVisitorMock
.Stub(stub => stub.Visit(_rightIntegerExpression))
.WhenCalled(mi => _commandBuilder.Append("right"))
.Return(_rightIntegerExpression);
_leftDoubleExpression = Expression.Constant(1D);
_expressionVisitorMock
.Stub(stub => stub.Visit(_leftDoubleExpression))
.WhenCalled(mi => _commandBuilder.Append("leftDouble"))
.Return(_leftDoubleExpression);
_rightDoubleExpression = Expression.Constant(2D);
_expressionVisitorMock
.Stub(stub => stub.Visit(_rightDoubleExpression))
.WhenCalled(mi => _commandBuilder.Append("rightDouble"))
.Return(_rightDoubleExpression);
_leftStringExpression = Expression.Constant("Left");
_expressionVisitorMock
.Stub(stub => stub.Visit(_leftStringExpression))
.WhenCalled(mi => _commandBuilder.Append("leftString"))
.Return(_leftStringExpression);
_rightStringExpression = Expression.Constant("Right");
_expressionVisitorMock
.Stub(stub => stub.Visit(_rightStringExpression))
.WhenCalled(mi => _commandBuilder.Append("rightString"))
.Return(_rightStringExpression);
_nullExpression = Expression.Constant(null, typeof(string));
_expressionVisitorMock
.Stub(stub => stub.Visit(_nullExpression))
.WhenCalled(mi => _commandBuilder.Append("NULL"))
.Return(_rightStringExpression);
_trueExpression = Expression.Constant(true);
_expressionVisitorMock
.Stub(stub => stub.Visit(_trueExpression))
.WhenCalled(mi => _commandBuilder.Append("true"))
.Return(_trueExpression);
_falseExpression = Expression.Constant(false);
_expressionVisitorMock
.Stub(stub => stub.Visit(_falseExpression))
.WhenCalled(mi => _commandBuilder.Append("false"))
.Return(_falseExpression);
_nullableTrueExpression = Expression.Constant(true, typeof(bool?));
_expressionVisitorMock
.Stub(stub => stub.Visit(_nullableTrueExpression))
.WhenCalled(mi => _commandBuilder.Append("true"))
.Return(_nullableTrueExpression);
_nullableFalseExpression = Expression.Constant(false, typeof(bool?));
_expressionVisitorMock
.Stub(stub => stub.Visit(_nullableFalseExpression))
.WhenCalled(mi => _commandBuilder.Append("false"))
.Return(_nullableFalseExpression);
_sqlEntityExpression = SqlStatementModelObjectMother.CreateSqlEntityDefinitionExpression(typeof(Cook));
_expressionVisitorMock
.Stub(stub => stub.Visit(_sqlEntityExpression))
.WhenCalled(mi => _commandBuilder.Append("[c].[ID]"))
.Return(((SqlEntityExpression)_sqlEntityExpression).GetIdentityExpression());
_generator = new BinaryExpressionTextGenerator(_commandBuilder, _expressionVisitorMock);
}
private void ChartsOfAccounts_Load(object sender, EventArgs e)
{
SqlConnection newSqlConnection = new SqlConnection(GeneralFunctions.ConnectionString);
newSqlConnection.Open();
SqlCommand newSqlCommand = new SqlCommand("Select CompanyName From Company", newSqlConnection);
SqlDataReader newSqlRead = newSqlCommand.ExecuteReader();
if (newSqlRead.HasRows)
{
while (newSqlRead.Read())
{
GeneralFunctions.CompanyName = newSqlRead.GetString(0);
}
}
SqlConnection sqlconnect = new SqlConnection(GeneralFunctions.ConnectionString);
sqlconnect.Open();
SqlCommand sqlcommand = new SqlCommand("Select AccountNumberFormat FROM GeneralSetup", sqlconnect);
SqlDataReader sqlread = sqlcommand.ExecuteReader();
LENAccount = 0;
if (sqlread.HasRows)
{
while (sqlread.Read())
{
LENAccount = sqlread.GetString(0).Length;
}
}
sqlread.Close();
sqlconnect.Close();
sqlcon = new SqlConnection(GeneralFunctions.ConnectionString);
adaptertbAccounts = new SqlDataAdapter("Select * from GLAccounts where AccountTypeName <> 'Header'", sqlcon);
adaptertbAccountTypes = new SqlDataAdapter("Select * from GLAccountTypes", sqlcon);
adaptertbAccountsChart = new SqlDataAdapter("Select * from GLAccountsChart", sqlcon);
adaptertbAccounts.Fill(dbAccountingProjectDS.GLAccounts);
if (adaptertbAccountsChart != null)
{
adaptertbAccountsChart.Fill(dbAccountingProjectDS.GLAccountsChart);
}
adaptertbAccountTypes.Fill(dbAccountingProjectDS.GLAccountTypes);
cmdBuilder = new SqlCommandBuilder(adaptertbAccountsChart);
if (!GeneralFunctions.SubTypesloaded)
{
SqlConnection sqlcon10 = new SqlConnection(GeneralFunctions.ConnectionString);
sqlcon10.Open();
SqlCommand command10 = new SqlCommand("Select AccountSubType From GeneralSetup", sqlcon10);
SqlCommand command11 = new SqlCommand("Select FirstSub From GeneralSetup", sqlcon10);
SqlCommand command12 = new SqlCommand("Select SecondSub From GeneralSetup", sqlcon10);
SqlCommand command13 = new SqlCommand("Select ThirdSub From GeneralSetup", sqlcon10);
SqlCommand command14 = new SqlCommand("Select FourthSub From GeneralSetup", sqlcon10);
int AccountSubTypeNumber;
if (command10.ExecuteScalar() != DBNull.Value)
{
AccountSubTypeNumber = Convert.ToInt32(command10.ExecuteScalar());
if (AccountSubTypeNumber == 2)
{
GeneralFunctions.LoadSubtypes(Convert.ToInt32(command11.ExecuteScalar()), Convert.ToInt32(command12.ExecuteScalar()));
GeneralFunctions.SubTypesloaded = true;
}
if (AccountSubTypeNumber == 3)
{
GeneralFunctions.LoadSubtypes(Convert.ToInt32(command11.ExecuteScalar()), Convert.ToInt32(command12.ExecuteScalar()), Convert.ToInt32(command13.ExecuteScalar()));
GeneralFunctions.SubTypesloaded = true;
}
if (AccountSubTypeNumber == 4)
{
GeneralFunctions.LoadSubtypes(Convert.ToInt32(command11.ExecuteScalar()), Convert.ToInt32(command12.ExecuteScalar()), Convert.ToInt32(command13.ExecuteScalar()), Convert.ToInt32(command14.ExecuteScalar()));
GeneralFunctions.SubTypesloaded = true;
}
}
}
cb_AccountTypeName = GeneralFunctions.FillComboBox(dbAccountingProjectDS.GLAccountTypes, cb_AccountTypeName, "AccountTypeName", "AccountTypeID");
cb_AccountTypeName.Items.Insert(0, "Any Type");
cb_AccountTypeName.Items.Remove("<new>");
cb_AccountTypeName.Items.Remove("Header");
cb_AccountTypeName.Text = "Any Type";
LoadTreeView();
//treeView1.ExpandAll();
if (GeneralFunctions.languagechioce != "")
{
this.obj_options = new ClassOptions();
this.obj_options.report_language = GeneralFunctions.languagechioce;
this.update_language_interface();
}
if (GeneralFunctions.Ckecktag("47") == "M")
{
this.Menu1.Enabled = true;
}
else
{
this.Menu1.Enabled = false;
}
if (GeneralFunctions.Ckecktag("18") == "M")
{
this.btn_AddAccount.Enabled = true;
}
else
{
this.btn_AddAccount.Enabled = false;
}
//currcet();
}
public static DataSet ExeDataSetSp(string storedProcedureName, params object[] Params)
{
SqlConnection mySqlConnection = new SqlConnection(_dbConnectionString);
mySqlConnection.Open();
SqlCommand MySqlCommand = new SqlCommand(storedProcedureName, mySqlConnection);
MySqlCommand.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(MySqlCommand);
//SqlParameter sqls = new SqlParameter("ExpertId",Params[0]);
//MySqlCommand.Parameters.Add(sqls);
for (int i = 1; i < MySqlCommand.Parameters.Count; i++)
{
if (Params[i - 1].ToString() == "" || Params[i - 1].ToString() == "null")
{
MySqlCommand.Parameters[i].Value = DBNull.Value;
}
else
{
MySqlCommand.Parameters[i].Value = Params[i - 1].ToString();
}
// MySqlCommand.Parameters.AddWithValue(parameter, Params[0]);
// Set value of ith parameter
}
//for (int i = 0; i < Params.Length; i++)
//{
// string parameter = Params[i].ToString();
// MySqlCommand.Parameters.AddWithValue(MySqlCommand.Parameters[i+1].ToString(), parameter);
//}
//SqlParameter parameterReturnValue = new SqlParameter("ReturnValue", SqlDbType.Int, 4);
//parameterReturnValue.Direction = ParameterDirection.ReturnValue;
//MySqlCommand.Parameters.Add(parameterReturnValue);
//foreach (object item in Params)
//{
// MySqlCommand.Parameters.AddWithValue("ExpertId",item);
//}
SqlDataAdapter MySqlDataAdapter = new SqlDataAdapter(MySqlCommand);
DataSet MyDataSet = new DataSet();
// mySqlConnection.Open();
MySqlDataAdapter.Fill(MyDataSet);
mySqlConnection.Close();
return(MyDataSet);
}
public void Refresh(bool withoutData = false)
{
WhereClauseBuilder whereClauseBuilder = null;
if (!IsSqlEditMode)
{
Filter2Sql(out whereClauseBuilder);
}
else
{
this._sql = this.DisplayedSql;
}
if (withoutData)
{
return;
}
Dispatcher.CurrentDispatcher.Invoke(() => IsBusy = true);
Data = new DataTable();
var tmpData = new DataTable();
var columnsSelect = new SqlCommand(this._sql, _sqlConnection);
if (AdvFilter != null && whereClauseBuilder != null)
{
foreach (var kv in whereClauseBuilder.Parameters)
{
var sqlParam = new SqlParameter
{
ParameterName = kv.Key,
Value = kv.Value
};
columnsSelect.Parameters.Add(sqlParam);
}
}
DataAdapter = new SqlDataAdapter(columnsSelect);
var sqlBuilder = new SqlCommandBuilder(DataAdapter);
try
{
DataAdapter.UpdateCommand = sqlBuilder.GetUpdateCommand();
}
catch (InvalidOperationException e)
{
MessageBox.Show(e.Message);
}
try
{
DataAdapter.InsertCommand = sqlBuilder.GetInsertCommand();
}
catch (InvalidOperationException e)
{
MessageBox.Show(e.Message);
}
try
{
DataAdapter.DeleteCommand = sqlBuilder.GetDeleteCommand();
}
catch (InvalidOperationException e)
{
MessageBox.Show(e.Message);
}
Task.Delay(250).ContinueWith((t) =>
{
DataAdapter.Fill(tmpData);
Dispatcher.CurrentDispatcher.Invoke(new Action(() => {
Data = tmpData;
IsBusy = false;
}));
});
}
public void SetUp ()
{
_stageMock = MockRepository.GenerateStrictMock<ISqlGenerationStage> ();
_commandBuilder = new SqlCommandBuilder ();
}
public void SetUp ()
{
_stageMock = MockRepository.GenerateStrictMock<ISqlGenerationStage>();
_commandBuilder = new SqlCommandBuilder();
_generator = new TestableSqlTableAndJoinTextGenerator (_commandBuilder, _stageMock);
}
/// <summary>
/// 获取选择语句内容
/// </summary>
/// <param name="cmd">选择语句类</param>
/// <param name="realPageIndex">实际页索引</param>
/// <param name="realPageCount">实际页数量</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择语句内容</returns>
internal static String InternalGetSelectCommand(SelectCommand cmd, Int32 realPageIndex, Int32 realPageCount, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(cmd.DatabaseType);
sb.AppendSelectPrefix();
if (realPageIndex == 1)//第一页
{
if (cmd.PageSize > 0)
{
sb.AppendSelectTop(cmd.PageSize);
}
sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields);
sb.AppendSelectFromAndJoins(cmd.TableName, cmd.IsFromSql, cmd.SqlJoins);
sb.AppendWhere(cmd.SqlWhere);
sb.AppendSelectGroupBys(cmd.GroupByColumns);
sb.AppendHaving(cmd.SqlHaving);
sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse);
}
else if (realPageIndex == realPageCount)//最后一页
{
sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields);
SelectCommand innerCommand = new SelectCommand(cmd.Database, cmd.TableName);
innerCommand.QueryFields = cmd.QueryFields;
innerCommand.PageSize = cmd.RecordCount - cmd.PageSize * (realPageIndex - 1);
innerCommand.SqlJoins = cmd.SqlJoins;
innerCommand.SqlWhere = cmd.SqlWhere;
innerCommand.GroupByColumns = cmd.GroupByColumns;
innerCommand.SqlHaving = cmd.SqlHaving;
innerCommand.SqlOrders = cmd.SqlOrders;
sb.AppendSelectFrom(innerCommand.ToString("T", !orderReverse), true);
sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse);
}
else if (realPageIndex < (realPageCount / 2 + realPageCount % 2))//前1/2部分页
{
sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields);
SelectCommand innestCommand = new SelectCommand(cmd.Database, cmd.TableName);
innestCommand.QueryFields = cmd.QueryFields;
innestCommand.PageSize = cmd.PageSize * realPageIndex;
innestCommand.SqlJoins = cmd.SqlJoins;
innestCommand.SqlWhere = cmd.SqlWhere;
innestCommand.GroupByColumns = cmd.GroupByColumns;
innestCommand.SqlHaving = cmd.SqlHaving;
innestCommand.SqlOrders = cmd.SqlOrders;
SelectCommand innerCommand = new SelectCommand(cmd.Database, innestCommand, "T");
innerCommand.QueryFields = cmd.QueryFields;
innerCommand.PageSize = cmd.PageSize;
innerCommand.SqlOrders = cmd.SqlOrders;
sb.AppendSelectFrom(innerCommand.ToString("T", !orderReverse), true);
sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse);
}
else//后1/2部分页
{
sb.AppendSelectTop(cmd.PageSize);
sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields);
SelectCommand innerCommand = new SelectCommand(cmd.Database, cmd.TableName);
innerCommand.QueryFields = cmd.QueryFields;
innerCommand.PageSize = cmd.RecordCount - cmd.PageSize * (realPageIndex - 1);
innerCommand.SqlJoins = cmd.SqlJoins;
innerCommand.SqlWhere = cmd.SqlWhere;
innerCommand.GroupByColumns = cmd.GroupByColumns;
innerCommand.SqlHaving = cmd.SqlHaving;
innerCommand.SqlOrders = cmd.SqlOrders;
sb.AppendSelectFrom(innerCommand.ToString("T", !orderReverse), true);
sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse);
}
return sb.ToString();
}
/// <summary>
/// 获取选择空内容语句
/// </summary>
/// <param name="baseCommand">源选择语句</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择空内容语句</returns>
internal static String InternalGetSelectNoneCommand(SelectCommand baseCommand, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(baseCommand.Database);
sb.AppendSelectPrefix();
sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.UseDistinct, baseCommand.InternalGetQueryFieldList());
sb.AppendSelectFromAndJoins(baseCommand.TableName, baseCommand.IsFromSql, baseCommand.InternalGetJoinList());
if (baseCommand.WhereCondition != null)
{
sb.AppendWhere(baseCommand.ConditionBuilder.And(baseCommand.ConditionBuilder.False(), baseCommand.WhereCondition));
}
else
{
sb.AppendWhere(baseCommand.ConditionBuilder.False());
}
sb.AppendSelectGroupBys(baseCommand.InternalGetGroupByFieldList());
sb.AppendHaving(baseCommand.InternalGetHavingCondition());
sb.AppendSelectOrderBys(baseCommand.InternalGetOrderList(), orderReverse);
return sb.ToString();
}
/// <summary>
/// 获取选择空内容语句
/// </summary>
/// <param name="baseCommand">源选择语句</param>
/// <param name="orderReverse">是否反转排序</param>
/// <returns>选择空内容语句</returns>
internal static String InternalGetSelectNoneCommand(SelectCommand baseCommand, Boolean orderReverse)
{
SqlCommandBuilder sb = new SqlCommandBuilder(baseCommand.Database);
sb.AppendSelectPrefix();
sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.QueryFields);
sb.AppendSelectFromAndJoins(baseCommand.TableName, baseCommand.IsFromSql, baseCommand.SqlJoins);
if (baseCommand.SqlWhere != null)
{
sb.AppendWhere(SqlCondition.And(baseCommand, SqlCondition.False(baseCommand), baseCommand.SqlWhere));
}
else
{
sb.AppendWhere(SqlCondition.False(baseCommand));
}
sb.AppendSelectGroupBys(baseCommand.GroupByColumns);
sb.AppendHaving(baseCommand.SqlHaving);
sb.AppendSelectOrderBys(baseCommand.SqlOrders, orderReverse);
return sb.ToString();
}
private void btnAdd_Click(object sender, EventArgs e)
{
if (rtbDescription.Text == "")
{
MessageBox.Show("Please provide description of the item", "Item Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
rtbDescription.Focus();
return;
}
if (txtItemRate.Text == "")
{
MessageBox.Show("Please enter rate of the item", "Item Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtItemRate.Focus();
return;
}
if (txtItemRate.Text.StartsWith("0"))
{
MessageBox.Show("Please enter valid rate of the item", "Item Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtItemRate.Focus();
return;
}
char[] itemRate = txtItemRate.Text.ToCharArray();
int length = txtItemRate.Text.Length;
int decPoint = 0;
int decDigitCount = 0;
for (int i = 0; i < length; i++)
{
if (!char.IsDigit(itemRate[i]))
{
if (itemRate[i].ToString() == ".")
{
if (decPoint == 0)
{
decPoint = 1;
}
else
{
MessageBox.Show("More than one decimal points are not allowed in the Item Rate text box", "Item Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtItemRate.Focus();
return;
}
}
else
{
MessageBox.Show("Only numeric values along with (or without) two digits after decimal point are allowed for the Item Rate text box",
"Item Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtItemRate.Focus();
return;
}
}
else
{
if (decPoint == 1)
{
decDigitCount++;
if (decDigitCount > 2)
{
MessageBox.Show("Only two digits are allowed after decimal point", "Item Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtItemRate.Focus();
return;
}
}
}
}
if (con.State == ConnectionState.Closed)
{
con.Open();
}
adp = new SqlDataAdapter("Select * from Items", con);
ds.Clear();
adp.Fill(ds, "Items");
DataTable ItemsTable = ds.Tables["Items"];
DataRow MyNewRow = null;
MyNewRow = ItemsTable.NewRow();
MyNewRow[0] = txtItemCode.Text;
MyNewRow[1] = rtbDescription.Text;
MyNewRow[2] = txtItemRate.Text;
ItemsTable.Rows.Add(MyNewRow);
SqlCommandBuilder UpdateDataCommand = new SqlCommandBuilder(adp);
adp.InsertCommand = UpdateDataCommand.GetInsertCommand();
adp.Update(ds, "Items");
ds.AcceptChanges();
MessageBox.Show("The New items details are enterd successfully", "Data Insertion Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
con.Close();
txtItemCode.Text = GenerateId();
txtItemRate.Text = "";
rtbDescription.Text = "";
fillItems();
}
public void SetUp ()
{
_commandBuilder = new SqlCommandBuilder();
_expressionVisitorMock = MockRepository.GenerateStrictMock<ExpressionTreeVisitor>();
_leftIntegerExpression = Expression.Constant (1);
_expressionVisitorMock
.Stub (stub => stub.VisitExpression (_leftIntegerExpression))
.WhenCalled (mi => _commandBuilder.Append ("left"))
.Return (_leftIntegerExpression);
_rightIntegerExpression = Expression.Constant (2);
_expressionVisitorMock
.Stub (stub => stub.VisitExpression (_rightIntegerExpression))
.WhenCalled (mi => _commandBuilder.Append ("right"))
.Return (_rightIntegerExpression);
_leftDoubleExpression = Expression.Constant (1D);
_expressionVisitorMock
.Stub (stub => stub.VisitExpression (_leftDoubleExpression))
.WhenCalled (mi => _commandBuilder.Append ("leftDouble"))
.Return (_leftDoubleExpression);
_rightDoubleExpression = Expression.Constant (2D);
_expressionVisitorMock
.Stub (stub => stub.VisitExpression (_rightDoubleExpression))
.WhenCalled (mi => _commandBuilder.Append ("rightDouble"))
.Return (_rightDoubleExpression);
_leftStringExpression = Expression.Constant ("Left");
_expressionVisitorMock
.Stub (stub => stub.VisitExpression (_leftStringExpression))
.WhenCalled (mi => _commandBuilder.Append ("leftString"))
.Return (_leftStringExpression);
_rightStringExpression = Expression.Constant ("Right");
_expressionVisitorMock
.Stub (stub => stub.VisitExpression (_rightStringExpression))
.WhenCalled (mi => _commandBuilder.Append ("rightString"))
.Return (_rightStringExpression);
_nullExpression = Expression.Constant (null, typeof (string));
_expressionVisitorMock
.Stub (stub => stub.VisitExpression (_nullExpression))
.WhenCalled (mi => _commandBuilder.Append ("NULL"))
.Return (_rightStringExpression);
_trueExpression = Expression.Constant (true);
_expressionVisitorMock
.Stub (stub => stub.VisitExpression (_trueExpression))
.WhenCalled (mi => _commandBuilder.Append ("true"))
.Return (_trueExpression);
_falseExpression = Expression.Constant (false);
_expressionVisitorMock
.Stub (stub => stub.VisitExpression (_falseExpression))
.WhenCalled (mi => _commandBuilder.Append ("false"))
.Return (_falseExpression);
_nullableTrueExpression = Expression.Constant (true, typeof (bool?));
_expressionVisitorMock
.Stub (stub => stub.VisitExpression (_nullableTrueExpression))
.WhenCalled (mi => _commandBuilder.Append ("true"))
.Return (_nullableTrueExpression);
_nullableFalseExpression = Expression.Constant (false, typeof (bool?));
_expressionVisitorMock
.Stub (stub => stub.VisitExpression (_nullableFalseExpression))
.WhenCalled (mi => _commandBuilder.Append ("false"))
.Return (_nullableFalseExpression);
_sqlEntityExpression = SqlStatementModelObjectMother.CreateSqlEntityDefinitionExpression (typeof (Cook));
_expressionVisitorMock
.Stub (stub => stub.VisitExpression (_sqlEntityExpression))
.WhenCalled (mi => _commandBuilder.Append ("[c].[ID]"))
.Return (((SqlEntityExpression) _sqlEntityExpression).GetIdentityExpression());
_generator = new BinaryExpressionTextGenerator (_commandBuilder, _expressionVisitorMock);
}
public void SaveStdUI(string ieID, string iStdID, string heading, string pdesc)
{
string[] _Type = iStdID.Split('_');
int _StdID = Convert.ToInt32(_Type[0]);
string Part = Convert.ToString(_Type[1]);
string _ieMode = "";
long _ieID = Convert.ToInt64(ieID);
//long _StdID = Convert.ToInt64(iStdID);
string sProvider = ConfigurationManager.ConnectionStrings["connString_V3"].ConnectionString;
string SqlStr = "";
oSQLConn.ConnectionString = sProvider;
oSQLConn.Open();
SqlStr = "Select * from tblProceduresDetail WHERE PatientIE_ID = " + ieID + " AND ProcedureDetail_ID = " + _StdID;
SqlDataAdapter sqlAdapt = new SqlDataAdapter(SqlStr, oSQLConn);
SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(sqlAdapt);
DataTable sqlTbl = new DataTable();
sqlAdapt.Fill(sqlTbl);
DataRow TblRow;
//if (sqlTbl.Rows.Count == 0 && StdChecked == true)
// _ieMode = "New";
//else if (sqlTbl.Rows.Count == 0 && StdChecked == false)
// _ieMode = "None";
//else if (sqlTbl.Rows.Count > 0 && StdChecked == false)
// _ieMode = "Delete";
//else
_ieMode = "Update";
if (_ieMode == "New")
{
TblRow = sqlTbl.NewRow();
}
else if (_ieMode == "Update" || _ieMode == "Delete")
{
TblRow = sqlTbl.Rows[0];
TblRow.AcceptChanges();
}
else
{
TblRow = null;
}
if (_ieMode == "Update" || _ieMode == "New")
{
TblRow["ProcedureDetail_ID"] = _StdID;
TblRow["PatientIE_ID"] = _ieID;
if (Part.Equals("R"))
{
TblRow["Heading"] = heading.ToString().Trim();
TblRow["PDesc"] = pdesc.ToString().Trim();
}
else if (Part.Equals("S"))
{
TblRow["S_Heading"] = heading.ToString().Trim();
TblRow["S_PDesc"] = pdesc.ToString().Trim();
}
else if (Part.Equals("E"))
{
TblRow["E_Heading"] = heading.ToString().Trim();
TblRow["E_PDesc"] = pdesc.ToString().Trim();
}
if (_ieMode == "New")
{
TblRow["CreatedBy"] = "Admin";
TblRow["CreatedDate"] = DateTime.Now;
sqlTbl.Rows.Add(TblRow);
}
sqlAdapt.Update(sqlTbl);
}
else if (_ieMode == "Delete")
{
TblRow.Delete();
sqlAdapt.Update(sqlTbl);
}
if (TblRow != null)
{
TblRow.Table.Dispose();
}
sqlTbl.Dispose();
sqlCmdBuilder.Dispose();
sqlAdapt.Dispose();
oSQLConn.Close();
}
private void updateFromDB()
{
try
{
//use helper method to get a connection obj
conn = getConnection();
conn.Open(); //open connection
String sql = "SELECT * FROM [inventory]";
dAdapter = new SqlDataAdapter(sql, conn);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dAdapter);
mainDataSet = new DataSet();
dAdapter.Fill(mainDataSet, "inventory");
conn.Close();
//Initialize DataView
displayView = new DataView(mainDataSet.Tables["inventory"]);
displayView.Sort = "name ASC";
//bind
bindingSource1.DataSource = displayView;
//bindingSource1.DataSource = mainDataSet;
//bindingSource1.DataMember = "inventory"; //specify the table
dg1.DataSource = bindingSource1;
dg1.Columns[COL_PUBLISHER].Visible = false; //hide columns we don't want to see
dg1.Columns[COL_DEVELOPER].Visible = false;
//dg1.Columns[COL_CATEGORY].Visible = false;
//dg1.Columns[COL_RELEASEDATE].Visible = false;
dg1.Columns[COL_RATING].Visible = false;
//Make the column headers nice
dg1.Columns[COL_NAME].HeaderText = "Name";
dg1.Columns[COL_PLATFORM].HeaderText = "Platform";
dg1.Columns[COL_CATEGORY].HeaderText = "Category";
dg1.Columns[COL_RELEASEDATE].HeaderText = "Release Date";
dg1.Columns[COL_COST].HeaderText = "Cost";
dg1.Columns[COL_AVAILABILITY].HeaderText = "Stock";
//Add extra width weight for the name column.
dg1.Columns[COL_NAME].FillWeight = 200f;
/*dg1.Columns[COL_CATEGORY].FillWeight = 100f;
* dg1.Columns[COL_RELEASEDATE].FillWeight = 100f;*/
dg1.Columns[COL_COST].FillWeight = 60f;
dg1.Columns[COL_AVAILABILITY].FillWeight = 60f;
dg1.Columns[COL_COST].DefaultCellStyle.Format = "c";
dg1.ClearSelection();
}
catch (SqlException ex)
{
if (conn != null) //if connected, but had exception
{
conn.Close(); //close connection
MessageBox.Show(ex.Message,
"Exception when trying to open SQL Connection",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
}
}
public string SaveUI(string fuID, string ieMode, bool bpChecked)
{
long _fuID = Convert.ToInt64(fuID);
string _ieMode = "";
string sProvider = ConfigurationManager.ConnectionStrings["connString_V3"].ConnectionString;
string SqlStr = "";
oSQLConn.ConnectionString = sProvider;
oSQLConn.Open();
SqlStr = "Select * from tblFUbpMidBack WHERE PatientFU_ID = " + _fuID;
SqlDataAdapter sqlAdapt = new SqlDataAdapter(SqlStr, oSQLConn);
SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(sqlAdapt);
DataTable sqlTbl = new DataTable();
sqlAdapt.Fill(sqlTbl);
DataRow TblRow;
if (sqlTbl.Rows.Count == 0 && bpChecked == true)
{
_ieMode = "New";
}
else if (sqlTbl.Rows.Count == 0 && bpChecked == false)
{
_ieMode = "None";
}
else if (sqlTbl.Rows.Count > 0 && bpChecked == false)
{
_ieMode = "Delete";
}
else
{
_ieMode = "Update";
}
if (_ieMode == "New")
{
TblRow = sqlTbl.NewRow();
}
else if (_ieMode == "Update" || _ieMode == "Delete")
{
TblRow = sqlTbl.Rows[0];
TblRow.AcceptChanges();
}
else
{
TblRow = null;
}
if (_ieMode == "Update" || _ieMode == "New")
{
TblRow["PatientFU_ID"] = _fuID;
//TblRow["PalpationAt"] = txtPalpationAt.Text.ToString();
//TblRow["Levels"] = cboLevels.Text.ToString();
//TblRow["ROM"] = cboROM.Text.ToString();
//TblRow["TPSide1"] = cboTPSide1.Text.ToString();
//TblRow["TPText1"] = txtTPText1.Text.ToString();
//TblRow["TPSide2"] = cboTPSide2.Text.ToString();
//TblRow["TPText2"] = txtTPText2.Text.ToString();
//TblRow["TPSide3"] = cboTPSide3.Text.ToString();
//TblRow["TPText3"] = txtTPText3.Text.ToString();
//TblRow["TPSide4"] = cboTPSide4.Text.ToString();
//TblRow["TPText4"] = txtTPText4.Text.ToString();
TblRow["FreeForm"] = txtFreeForm.Text.ToString();
//TblRow["FreeFormCC"] = txtFreeFormCC.Text.ToString();
TblRow["FreeFormA"] = txtFreeFormA.Text.ToString();
//TblRow["FreeFormP"] = txtFreeFormP.Text.ToString();
TblRow["CCvalue"] = hdCCvalue.Value;
TblRow["PEvalue"] = hdPEvalue.Value;
TblRow["PEvalueoriginal"] = hdPEvalueoriginal.Value;
TblRow["PESides"] = hdPESides.Value;
TblRow["PESidesText"] = hdPESidesText.Value;
if (_ieMode == "New")
{
TblRow["CreatedBy"] = "Admin";
TblRow["CreatedDate"] = DateTime.Now;
sqlTbl.Rows.Add(TblRow);
}
sqlAdapt.Update(sqlTbl);
}
else if (_ieMode == "Delete")
{
TblRow.Delete();
sqlAdapt.Update(sqlTbl);
}
if (TblRow != null)
{
TblRow.Table.Dispose();
}
sqlTbl.Dispose();
sqlCmdBuilder.Dispose();
sqlAdapt.Dispose();
oSQLConn.Close();
if (_ieMode == "New")
{
return("MidBack has been added...");
}
else if (_ieMode == "Update")
{
return("MidBack has been updated...");
}
else if (_ieMode == "Delete")
{
return("MidBack has been deleted...");
}
else
{
return("");
}
}
private void button1_Click(object sender, EventArgs e)
{
str = textBox1.Text;
SqlConnection connection = new SqlConnection();
connection.ConnectionString = "Data Source= GATEWAY-PC;Initial Catalog=xiaoyuanka;User ID=sa;Password=niit#1234";
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter("select * from consume ", connection);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter);
DataSet ds = new DataSet("consume");
adapter.Fill(ds, "consume");
//adapter.Fill(ds, "student");
DataRow row = ds.Tables["consume"].NewRow();
row["account"] = account;
row["amount"] = textBox1.Text;
row["time"] = Convert.ToInt32(textBox2.Text);
row["address"] = textBox3.Text;
ds.Tables["consume"].Rows.Add(row);
adapter.Update(ds, "consume");
connection.Close();
using (SqlConnection conn = new SqlConnection(@"Data Source= GATEWAY-PC;Initial Catalog=xiaoyuanka;User ID=sa;Password=niit#1234"))
{
conn.Open();//连接数据库
using (SqlCommand cmd = conn.CreateCommand())
{
SqlParameter sq;
cmd.CommandText = "select * from student where account=@account";
sq = cmd.Parameters.Add("@account", SqlDbType.VarChar);
sq.Value = Form9.account;
//查找数据库里是否有该用户名
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
if (@account == reader[0].ToString().Trim())
{
if (reader[6].ToString().Trim() == "挂失")
{
MessageBox.Show("已挂失！");
return;
}
int yue = reader.GetInt32(reader.GetOrdinal("balance"));
if (yue == 0)
{
MessageBox.Show("余额不足，请充值!");
return;
}
int xfe = Int32.Parse(Form9.str);
int bookresultnum = yue - xfe;
if (bookresultnum < 0)
{
MessageBox.Show("余额不足，请充值!");
return;
}
cmd.CommandText = "update student set balance =" + bookresultnum + " where account='" + account + "'";
reader.Close();
cmd.ExecuteNonQuery();
MessageBox.Show("消费成功！");
this.Close();
}
}
}
}
}
}
public void SetUp ()
{
_sqlCommandBuilder = new SqlCommandBuilder();
}
public void SaveStdUI(string ieID, string iDiagID, bool DiagIsChecked, string bp, string dcd, string dc)
{
try
{
string _ieMode = "";
long _ieID = Convert.ToInt64(ieID);
long _DiagID = Convert.ToInt64(iDiagID);
string sProvider = ConfigurationManager.ConnectionStrings["connString_V3"].ConnectionString;
string SqlStr = "";
oSQLConn.ConnectionString = sProvider;
oSQLConn.Open();
SqlStr = "Select * FROM tblDiagCodesDetail WHERE PatientIE_ID = " + ieID + " AND Diag_Master_ID = " + _DiagID;
SqlDataAdapter sqlAdapt = new SqlDataAdapter(SqlStr, oSQLConn);
SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(sqlAdapt);
DataTable sqlTbl = new DataTable();
sqlAdapt.Fill(sqlTbl);
DataRow TblRow;
if (sqlTbl.Rows.Count == 0 && DiagIsChecked == true)
{
_ieMode = "New";
}
else if (sqlTbl.Rows.Count == 0 && DiagIsChecked == false)
{
_ieMode = "None";
}
else if (sqlTbl.Rows.Count > 0 && DiagIsChecked == false)
{
_ieMode = "Delete";
}
else
{
_ieMode = "Update";
}
if (_ieMode == "New")
{
TblRow = sqlTbl.NewRow();
}
else if (_ieMode == "Update" || _ieMode == "Delete")
{
TblRow = sqlTbl.Rows[0];
TblRow.AcceptChanges();
}
else
{
TblRow = null;
}
if (_ieMode == "Update" || _ieMode == "New")
{
TblRow["Diag_Master_ID"] = _DiagID;
TblRow["PatientIE_ID"] = _ieID;
TblRow["BodyPart"] = bp.ToString().Trim();
TblRow["DiagCode"] = dc.ToString().Trim();
TblRow["Description"] = dcd.ToString().Trim();
if (_ieMode == "New")
{
TblRow["CreatedBy"] = "Admin";
TblRow["CreatedDate"] = DateTime.Now;
sqlTbl.Rows.Add(TblRow);
}
sqlAdapt.Update(sqlTbl);
}
else if (_ieMode == "Delete")
{
TblRow.Delete();
sqlAdapt.Update(sqlTbl);
}
if (TblRow != null)
{
TblRow.Table.Dispose();
}
sqlTbl.Dispose();
sqlCmdBuilder.Dispose();
sqlAdapt.Dispose();
oSQLConn.Close();
}
catch (Exception ex)
{
log.Error(ex.Message);
}
}
public static bool InsertColumnDefs(XmlWriter xmlWriter, SqlConnection sqlConn, string sqlGetProc, string dbRowsClassName,
string dbRowClassName, string schema, string primaryTable, string sqlInsertProc, string sqlDeleteProc, MainForm mainForm, out string errMsg)
{
Dictionary <string, ColumnInfo> colInfoList = GetDbColInfo(sqlConn, mainForm, out errMsg);
if (colInfoList == null)
{
return(false);
}
SqlCommand sqlCmd = new SqlCommand();
DataTable schemaTable;
SqlDataReader sqlRdr;
// if specified primary table name has a prefix - strip it, then prefix the specified schema name
int pos = primaryTable.LastIndexOf(".");
string primaryTableName = String.Format("{0}.{1}", schema, (pos >= 0) ? primaryTable.Substring(pos + 1) : primaryTable);
// Check for existence of stored procedures
bool gspExists = (!String.IsNullOrEmpty(sqlGetProc) && checkIfStoredProcExists(sqlConn, sqlGetProc));
bool ispExists = (!String.IsNullOrEmpty(sqlInsertProc) && checkIfStoredProcExists(sqlConn, sqlInsertProc));
bool dspExists = (!String.IsNullOrEmpty(sqlDeleteProc) && checkIfStoredProcExists(sqlConn, sqlDeleteProc));
if (!gspExists)
{
mainForm.warningMsgs.AppendText(String.Format("Stored Procedure: {0} does not exist.\n", sqlGetProc));
}
if (!ispExists)
{
mainForm.warningMsgs.AppendText(String.Format("Stored Procedure: {0} does not exist.\n", sqlInsertProc));
}
if (!dspExists)
{
mainForm.warningMsgs.AppendText(String.Format("Stored Procedure: {0} does not exist.\n", sqlDeleteProc));
}
xmlWriter.WriteStartElement("SqlInterface");
xmlWriter.WriteAttributeString("GetRowsProc", sqlGetProc);
xmlWriter.WriteAttributeString("DbRowsClassName", dbRowsClassName);
xmlWriter.WriteAttributeString("DbRowClassName", dbRowClassName);
xmlWriter.WriteAttributeString("PrimaryTable", primaryTableName);
xmlWriter.WriteAttributeString("InsertRowProc", sqlInsertProc);
xmlWriter.WriteAttributeString("DeleteRowProc", sqlDeleteProc);
xmlWriter.WriteAttributeString("GetRowsProcExists", gspExists ? "T" : "F");
xmlWriter.WriteAttributeString("InsertRowProcExists", ispExists ? "T" : "F");
xmlWriter.WriteAttributeString("DeleteRowProcExists", dspExists ? "T" : "F");
sqlCmd.Connection = sqlConn;
sqlConn.Open();
if (ispExists)
{
// Get 'insert' stored proc info and write dbcolumn xml elements
sqlCmd.CommandText = sqlInsertProc;
sqlCmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(sqlCmd);
foreach (SqlParameter p in sqlCmd.Parameters)
{
if (p.Direction == ParameterDirection.Input || p.Direction == ParameterDirection.InputOutput)
{
xmlWriter.WriteStartElement("IParam");
xmlWriter.WriteAttributeString("ParamName", p.ParameterName);
xmlWriter.WriteAttributeString("ParamType", p.SqlDbType.ToString());
xmlWriter.WriteAttributeString("ParamLen", p.Size.ToString());
xmlWriter.WriteAttributeString("Direction", p.Direction.ToString());
xmlWriter.WriteAttributeString("Nullable", p.IsNullable ? "T" : "F");
xmlWriter.WriteAttributeString("DataColName", p.ParameterName.StartsWith("@") ? p.ParameterName.Substring(1) : String.Empty);
xmlWriter.WriteEndElement();
}
}
}
if (dspExists)
{
// Get 'insert' stored proc info and write dbcolumn xml elements
sqlCmd.CommandText = sqlDeleteProc;
sqlCmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(sqlCmd);
foreach (SqlParameter p in sqlCmd.Parameters)
{
if (p.Direction == ParameterDirection.Input || p.Direction == ParameterDirection.InputOutput)
{
xmlWriter.WriteStartElement("DParam");
xmlWriter.WriteAttributeString("ParamName", p.ParameterName);
xmlWriter.WriteAttributeString("ParamType", p.SqlDbType.ToString());
xmlWriter.WriteAttributeString("ParamLen", p.Size.ToString());
xmlWriter.WriteAttributeString("Direction", p.Direction.ToString());
xmlWriter.WriteAttributeString("Nullable", p.IsNullable ? "T" : "F");
xmlWriter.WriteAttributeString("DataColName", p.ParameterName.StartsWith("@") ? p.ParameterName.Substring(1) : String.Empty);
xmlWriter.WriteEndElement();
}
}
}
if (gspExists)
{
// Get 'get' stored proc info and write dbcolumn xml elements
sqlCmd.CommandText = sqlGetProc;
sqlCmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(sqlCmd);
foreach (SqlParameter p in sqlCmd.Parameters)
{
if (p.Direction == ParameterDirection.Input || p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Output)
{
xmlWriter.WriteStartElement("GParam");
xmlWriter.WriteAttributeString("ParamName", p.ParameterName);
xmlWriter.WriteAttributeString("ParamType", p.SqlDbType.ToString());
xmlWriter.WriteAttributeString("ParamLen", p.Size.ToString());
xmlWriter.WriteAttributeString("Direction", p.Direction.ToString());
xmlWriter.WriteAttributeString("Nullable", p.IsNullable ? "T" : "F");
xmlWriter.WriteAttributeString("DataColName", p.ParameterName.StartsWith("@") ? p.ParameterName.Substring(1) : String.Empty);
xmlWriter.WriteEndElement();
}
}
sqlRdr = sqlCmd.ExecuteReader(CommandBehavior.KeyInfo);
schemaTable = sqlRdr.GetSchemaTable();
List <string> colNames = new List <string>();
foreach (DataRow field in schemaTable.Rows) //For each field in the table...
{
bool isPrKey = false;
bool isForKey = false;
bool isIdentity = false;
bool isJoined = false;
bool isHidden = false;
bool isReadOnly = false;
bool isAliased = false;
bool isExpression = false;
bool isNullable = true;
bool isComputed = false;
string schemaName = string.Empty;
string tableName = string.Empty;
string colName = string.Empty;
string colOrdinal = string.Empty;
string dataType = string.Empty;
string sqlDataType = string.Empty;
string len = string.Empty;
string nullableParam = string.Empty;
string colAttr = string.Empty;
string forKeyTable = string.Empty;
string forKeyCol = string.Empty;
string baseColName = string.Empty;
string baseServerName = string.Empty;
foreach (DataColumn property in schemaTable.Columns) //For each property of the field...
{
switch (property.ColumnName)
{
case "ColumnName":
colName = field[property].ToString();
break;
case "ColumnOrdinal":
colOrdinal = field[property].ToString();
break;
case "IsKey":
isPrKey = (bool)field[property];
break;
case "IsIdentity":
isIdentity = (bool)field[property];
break;
case "IsHidden":
isHidden = (bool)field[property];
break;
case "IsAliased":
isAliased = (bool)field[property];
break;
case "IsExpression":
isExpression = (bool)field[property];
break;
case "IsReadOnly":
isReadOnly = (bool)field[property];
break;
case "BaseSchemaName":
schemaName = field[property].ToString();
break;
case "BaseTableName":
tableName = field[property].ToString();
break;
case "BaseColumnName":
baseColName = field[property].ToString();
break;
case "AllowDBNull":
nullableParam = (((bool)field[property]) ? "T" : "F");
break;
case "DataType":
dataType = field[property].ToString();
break;
case "DataTypeName":
sqlDataType = field[property].ToString();
break;
case "ColumnSize":
len = field[property].ToString();
break;
default:
//xmlWriter.WriteAttributeString("Unknown", field[property].ToString());
break;
}
}
// Add more column attributes if column is in the primary table and we have entry for column in our column dictionary
string colTableName = String.Format("{0}.{1}", schemaName, tableName);
string fullColName = String.Format("{0}.{1}", colTableName, colName);
isJoined = (!String.IsNullOrEmpty(tableName) && !colTableName.Equals(primaryTableName, StringComparison.InvariantCultureIgnoreCase));
if (!isJoined && !isExpression && colInfoList.ContainsKey(fullColName))
{
ColumnInfo colInfo = colInfoList[fullColName];
isNullable = colInfo.IsNullable;
isComputed = colInfo.IsComputed;
isPrKey = colInfo.IsPriKey;
isIdentity = colInfo.IsIdentity;
if (colInfo.IsForKey)
{
isForKey = true;
forKeyTable = colInfo.ForKeyTable;
forKeyCol = colInfo.ForKeyCol;
}
}
if (isComputed || isAliased || isExpression || isJoined)
{
isReadOnly = isNullable = true; // Computed, aliased and joined columns are always read only and nullable
}
// Determine the column attribute
if (isPrKey && !isJoined)
{
colAttr = (isIdentity) ? "AI" : "PK"; // Primary key col is either Auto ID or regular primary key col
}
else if ((dataType == "System.DateTime") && colName.EndsWith("ModDate", StringComparison.InvariantCultureIgnoreCase) && !isReadOnly)
{
colAttr = "MD";
}
else if ((dataType == "System.DateTime") && (colName.EndsWith("CreateDate", StringComparison.InvariantCultureIgnoreCase) ||
colName.EndsWith("EnterDate", StringComparison.InvariantCultureIgnoreCase)) && !isReadOnly)
{
colAttr = "CD";
}
else
{
colAttr = (isReadOnly) ? "RO" : "RW";
}
//bool nameExists = colNames.Exists(x => x == colName);
//if ( !(isPrKey && colNames.Exists(x => x == colName))) // Eliminate foreign key columns and self referenced columns
//if (!(isPrKey && isJoined) && !(isPrKey && colNames.Exists(x => x == colName))) // Eliminate foreign key columns and self referenced columns
if (!isHidden)
{
colNames.Add(colName);
xmlWriter.WriteStartElement("Column");
xmlWriter.WriteAttributeString("ColName", colName);
xmlWriter.WriteAttributeString("ColOrdinal", colOrdinal);
xmlWriter.WriteAttributeString("DataType", dataType);
xmlWriter.WriteAttributeString("SqlType", sqlDataType);
xmlWriter.WriteAttributeString("Len", len);
xmlWriter.WriteAttributeString("Nullable", isNullable ? "T" : "F");
xmlWriter.WriteAttributeString("IsPrKey", isPrKey ? "T" : "F");
xmlWriter.WriteAttributeString("ColAttrib", colAttr);
if (isForKey)
{
xmlWriter.WriteAttributeString("IsForKey", isForKey ? "T" : "F");
xmlWriter.WriteAttributeString("ForKeyTable", forKeyTable);
xmlWriter.WriteAttributeString("ForKeyCol", forKeyCol);
}
xmlWriter.WriteEndElement();
}
}
sqlRdr.Close();
}
sqlConn.Close();
return(true);
}
public override void CheckResult()
{
if (check.ischeckOk)
{
base.CheckResult();
if (Parameter.userflight == "夜班" && dtOuter.Rows[0]["夜班接班员"].ToString() == __待审核)
{
dtOuter.Rows[0]["夜班接班员"] = Parameter.userName;
txb白班异常情况处理.Enabled = false;
}
if (Parameter.userflight == "白班" && dtOuter.Rows[0]["白班接班员"].ToString() == __待审核)
{
dtOuter.Rows[0]["白班接班员"] = Parameter.userName;
txb白班异常情况处理.Enabled = false;
}
//this part to add log
//格式：
// =================================================
// yyyy年MM月dd日，操作员：XXX 审核
string log = "=====================================\n";
log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n操作员：" + mySystem.Parameter.userName + " 审核通过\n";
dtOuter.Rows[0]["日志"] = dtOuter.Rows[0]["日志"].ToString() + log;
bsOuter.EndEdit();
daOuter.Update((DataTable)bsOuter.DataSource);
readOuterData(__生产指令编号, __生产日期);
removeOuterBind();
outerBind();
//to delete the unchecked table
//read from database table and find current record
string checkName = "待审核";
DataTable dtCheck = new DataTable(checkName);
SqlDataAdapter daCheck = new SqlDataAdapter("SELECT * FROM " + checkName + " WHERE 表名='" + tablename1 + "' AND 对应ID = " + searchId + ";", mySystem.Parameter.conn);
BindingSource bsCheck = new BindingSource();
SqlCommandBuilder cbCheck = new SqlCommandBuilder(daCheck);
daCheck.Fill(dtCheck);
//this part will never be run, for there must be a unchecked recird before this button becomes enable
if (0 == dtCheck.Rows.Count)
{
DataRow newrow = dtCheck.NewRow();
newrow["表名"] = tablename1;
newrow["对应ID"] = dtOuter.Rows[0]["ID"];
dtCheck.Rows.Add(newrow);
}
//remove the record
dtCheck.Rows[0].Delete();
bsCheck.DataSource = dtCheck;
daCheck.Update((DataTable)bsCheck.DataSource);
setEnableReadOnly(true);
}
else
{
MessageBox.Show("必须通过");
}
}
/// <summary>
///执行sql,插入数据dataTable,返回bool
/// </summary>
/// <param name="dbType"></param>
/// <param name="connectStr"></param>
/// <param name="sql"></param>
/// <param name="dataTable"></param>
/// <returns></returns>
public static bool ExecuteScalar(string dbType, string connectStr, string sql, DataTable dataTable)
{
var result = false;
var dsNew = new DataTable();
switch (dbType)
{
default: //默认oracle数据库
using (var conn = new OracleConnection(connectStr))
{
try
{
var cmd = new OracleCommand(sql, conn);
var adapter = new OracleDataAdapter(cmd);
var cb = new OracleCommandBuilder(adapter);
var count = adapter.Fill(dsNew);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
DataRow dr = dsNew.NewRow();
foreach (DataColumn col in dataTable.Columns)
{
dr[col.ColumnName] = dataTable.Rows[i][col.ColumnName];
}
dsNew.Rows.Add(dr);
}
count = adapter.Update(dsNew);
adapter.UpdateBatchSize = 200;
result = true;
}
catch (Exception e)
{
e.Message.ErrorMsg();
}
}
break;
case "db2":
//using (var conn = new DB2Connection(connectionString))
//{
// try
// {
// var cmd = new DB2Command(sqlScript, conn);
// var adapter = new DB2DataAdapter(cmd);
// var cb = new DB2CommandBuilder(adapter);
// var count = adapter.Fill(dsNew);
// for (int i = 0; i < dataTable.Rows.Count; i++)
// {
// DataRow dr = dsNew.NewRow();
// foreach (DataColumn col in dataTable.Columns)
// {
// dr[col.ColumnName] = dataTable.Rows[i][col.ColumnName];
// }
// dsNew.Rows.Add(dr);
// }
// count = adapter.Update(dsNew);
// adapter.UpdateBatchSize = 200;
// result = true;
// }
// catch (Exception e)
// {
// e.Message.ErrorMsg();
// }
//}
break;
case "mysql":
//using (var conn = new MySqlConnection(connectionString))
//{
// try
// {
// var cmd = new MySqlCommand(sqlScript, conn);
// var adapter = new MySqlDataAdapter(cmd);
// var cb = new MySqlCommandBuilder(adapter);
// var count = adapter.Fill(dsNew);
// for (int i = 0; i < dataTable.Rows.Count; i++)
// {
// DataRow dr = dsNew.NewRow();
// foreach (DataColumn col in dataTable.Columns)
// {
// dr[col.ColumnName] = dataTable.Rows[i][col.ColumnName];
// }
// dsNew.Rows.Add(dr);
// }
// count = adapter.Update(dsNew);
// adapter.UpdateBatchSize = 200;
// result = true;
// }
// catch (Exception e)
// {
// e.Message.ErrorMsg();
// }
//}
break;
case "sqlserver":
using (var conn = new SqlConnection(connectStr))
{
try
{
var cmd = new SqlCommand(sql, conn);
var adapter = new SqlDataAdapter(cmd);
var cb = new SqlCommandBuilder(adapter);
var count = adapter.Fill(dsNew);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
DataRow dr = dsNew.NewRow();
foreach (DataColumn col in dataTable.Columns)
{
dr[col.ColumnName] = dataTable.Rows[i][col.ColumnName];
}
dsNew.Rows.Add(dr);
}
count = adapter.Update(dsNew);
adapter.UpdateBatchSize = 200;
result = true;
}
catch (Exception e)
{
e.Message.ErrorMsg();
}
}
break;
}
return(result);
}
/// <summary>
/// Method to load service
/// </summary>
/// <returns></returns>
public Service LoadService()
{
var service = new Service();
_connection = new SqlConnection(GetConnectionString(DataSource.CSCMaster));
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_dataTable = new DataTable();
_adapter = new SqlDataAdapter("select * from Variables where Name=\'PostPayEnabled\'", _connection);
_adapter.Fill(_dataTable);
if (_dataTable.Rows.Count != 0)
{
if (!DBNull.Value.Equals(_dataTable.Rows[0]["Value"]))
{
service.PostPayEnabled = CommonUtility.GetDoubleValue(_dataTable.Rows[0]["Value"]) == 1;
}
else
{
service.PostPayEnabled = true;
_dataTable.Rows[0]["Value"] = "1";
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.UpdateCommand = builder.GetUpdateCommand();
}
}
else
{
var rsTmp = GetRecords("select Max(VarIndex) as MaxIndex from Variables", DataSource.CSCMaster);
DataRow rsfields = _dataTable.NewRow();
rsfields["VarIndex"] = DBNull.Value.Equals(rsTmp.Rows[0]["MaxIndex"]) ? 0 : CommonUtility.GetIntergerValue(rsTmp.Rows[0]["MaxIndex"]) + 1;
rsfields["Name"] = "PostPayEnabled";
rsfields["Value"] = "1";
rsfields["Description"] = "To keep the current PostPay status, 1-On/0-Off.";
_dataTable.Rows.Add(rsfields);
service.PostPayEnabled = true;
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.InsertCommand = builder.GetInsertCommand();
}
_adapter.Update(_dataTable);
_dataTable = new DataTable();
_adapter = new SqlDataAdapter("select * from Variables where Name=\'PayPumpEnabled\'", _connection);
_adapter.Fill(_dataTable);
if (_dataTable.Rows.Count != 0)
{
if (!DBNull.Value.Equals(_dataTable.Rows[0]["Value"]))
{
service.PayPumpEnabled = CommonUtility.GetDoubleValue(_dataTable.Rows[0]["Value"]) == 1;
}
else
{
service.PayPumpEnabled = true;
_dataTable.Rows[0]["Value"] = "1";
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.UpdateCommand = builder.GetUpdateCommand();
}
}
else
{
var rsTmp = GetRecords("select Max(VarIndex) as MaxIndex from Variables", DataSource.CSCMaster);
DataRow rsfields = _dataTable.NewRow();
rsfields["VarIndex"] = DBNull.Value.Equals(rsTmp.Rows[0]["MaxIndex"]) ? 0 : CommonUtility.GetIntergerValue(rsTmp.Rows[0]["MaxIndex"]) + 1;
rsfields["Name"] = "PayPumpEnabled";
rsfields["Value"] = "1";
rsfields["Description"] = "To keep the current PayPump status, 1-On/0-Off.";
_dataTable.Rows.Add(rsfields);
service.PayPumpEnabled = true;
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.InsertCommand = builder.GetInsertCommand();
}
_adapter.Update(_dataTable);
_dataTable = new DataTable();
_adapter = new SqlDataAdapter("select * from Variables where Name=\'PrepayEnabled\'", _connection);
_adapter.Fill(_dataTable);
if (_dataTable.Rows.Count != 0)
{
if (!DBNull.Value.Equals(_dataTable.Rows[0]["Value"]))
{
service.PrepayEnabled = CommonUtility.GetDoubleValue(_dataTable.Rows[0]["Value"]) == 1;
}
else
{
service.PrepayEnabled = true;
_dataTable.Rows[0]["Value"] = "1";
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.UpdateCommand = builder.GetUpdateCommand();
}
}
else
{
var rsTmp = GetRecords("select Max(VarIndex) as MaxIndex from Variables", DataSource.CSCMaster);
DataRow rsfields = _dataTable.NewRow();
rsfields["VarIndex"] = DBNull.Value.Equals(rsTmp.Rows[0]["MaxIndex"]) ? 0 : CommonUtility.GetIntergerValue(rsTmp.Rows[0]["MaxIndex"]) + 1;
rsfields["Name"] = "PrepayEnabled";
rsfields["Value"] = "1";
rsfields["Description"] = "To keep the current Prepay status, 1-On/0-Off.";
_dataTable.Rows.Add(rsfields);
service.PrepayEnabled = true;
SqlCommandBuilder builder = new SqlCommandBuilder(_adapter);
_adapter.InsertCommand = builder.GetInsertCommand();
}
_adapter.Update(_dataTable);
_connection.Close();
_adapter?.Dispose();
return(service);
}
public HttpResponseMessage PostLeave([FromBody] object leaveposting)
{
clsLeavePost tPost = JsonConvert.DeserializeObject <clsLeavePost>(leaveposting.ToString());
tPost.ERROR = Utils.LeaveDataValidate(tPost);
if (!string.IsNullOrEmpty(tPost.ERROR))
{
tPost.PostedFlg = false;
return(Request.CreateResponse(HttpStatusCode.BadRequest, tPost));
}
LeaveDaysDetails LeaveDays = Utils.GetLeaveDaysDetails(tPost);
string Location = tPost.Location;
string strEmp = tPost.EmpUnqID.ToString();
string strerr = string.Empty;
string cnstr = string.Empty;
try
{
cnstr = ConfigurationManager.ConnectionStrings["cn" + Location].ConnectionString;
}
catch (Exception ex)
{
tPost.ERROR = "Could not build Location Connection.." + ex.Message;
return(Request.CreateResponse(HttpStatusCode.BadRequest, tPost));
}
//bool trecosile = Utils.ReconsileLeaveBal(strEmp, tPost.LeaveTyp, "LeaveAPI", tPost.FromDate.Year, out strerr);
List <clsLeaveBal> tlstbal = Utils.GetLeaveBal(Convert.ToInt32(tPost.EmpUnqID), tPost.FromDate.Year, tPost.LeaveTyp, Location);
if (tlstbal.Count > 0)
{
clsLeaveBal tBal = tlstbal[0];
if (tBal.IsBalanced && (tBal.Balance - LeaveDays.LeaveDays) < 0)
{
tPost.ERROR += "InSufficient Balance...";
tPost.PostedFlg = false;
return(Request.CreateResponse(HttpStatusCode.BadRequest, tPost));
}
}
else
{
tPost.ERROR += "Leave Balance Record not found...";
tPost.PostedFlg = false;
return(Request.CreateResponse(HttpStatusCode.BadRequest, tPost));
}
#region Chk_WoReqEnt
switch (tPost.LeaveTyp)
{
case "LW":
LeaveDays.WOEntReq = false;
break;
case "AB":
LeaveDays.WOEntReq = false;
break;
case "SP":
LeaveDays.WOEntReq = false;
break;
case "OH":
LeaveDays.WOEntReq = false;
break;
default:
LeaveDays.WOEntReq = true;
break;
}
#endregion
#region MainProc
using (SqlConnection cn = new SqlConnection(cnstr))
{
try
{
cn.Open();
}
catch (Exception ex)
{
tPost.ERROR += ex.Message.ToString();
tPost.PostedFlg = false;
return(Request.CreateResponse(HttpStatusCode.BadRequest, tPost));
}
SqlTransaction tr = cn.BeginTransaction();
string sql = "Select * from LeaveEntry Where " +
" compcode = '01'" +
" and WrkGrp ='COMP'" +
" and LeaveTyp ='" + tPost.LeaveTyp + "'" +
" And tYear ='" + tPost.FromDate.Year + "'" +
" And EmpUnqID='" + tPost.EmpUnqID + "'" +
" And FromDt ='" + tPost.FromDate.ToString("yyyy-MM-dd") + "'" +
" and ToDt ='" + tPost.ToDate.ToString("yyyy-MM-dd") + "'";
string err2 = string.Empty;
DataSet ds = Utils.GetData(sql, cnstr, out err2);
if (!string.IsNullOrEmpty(err2))
{
tPost.ERROR += err2;
tPost.PostedFlg = false;
return(Request.CreateResponse(HttpStatusCode.BadRequest, tPost));
}
err2 = string.Empty;
//advance leave not allowed thru api..
bool hasRows = ds.Tables.Cast <DataTable>().Any(table => table.Rows.Count != 0);
if (!hasRows)
{
try
{
string remark = ((tPost.Remarks.Trim().Length > 100) ? tPost.Remarks.Trim().Substring(0, 100) : tPost.Remarks.Trim());
remark = remark.Replace("'", "");
remark = remark.Replace('"', ' ');
remark = remark.Replace('%', ' ');
remark = remark.Replace('&', ' ');
string insertsql = "insert into LeaveEntry (CompCode,WrkGrp,tYear,EmpUnqID,FromDt,ToDt," +
" LeaveTyp,TotDay,WoDay,PublicHL,LeaveDed,LeaveADV,LeaveHalf,Remark,AddID,AddDt,DelFlg) " +
" Values ('01','COMP','" + tPost.FromDate.Year.ToString() + "','" + tPost.EmpUnqID + "','" + tPost.FromDate.ToString("yyyy-MM-dd") + "','" + tPost.ToDate.ToString("yyyy-MM-dd") + "', " +
" '" + tPost.LeaveTyp + "','" + LeaveDays.TotDays.ToString() + "','" + LeaveDays.WoDays.ToString() + "','" + LeaveDays.HLDays.ToString() + "'," +
" '" + LeaveDays.LeaveDays.ToString() + "','0','" + (LeaveDays.IsHalf ? 1 : 0) + "','" + remark + "'," +
" '" + tPost.AttdUser + "',GetDate(),0)";
SqlCommand cmd = new SqlCommand(insertsql, cn, tr);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
tr.Rollback();
tPost.ERROR += ex.Message.ToString();
tPost.PostedFlg = false;
return(Request.CreateResponse(HttpStatusCode.BadRequest, tPost));
}
}
//gettting timeout
SqlCommand cmd1 = new SqlCommand(sql, cn, tr);
SqlDataReader sdr = cmd1.ExecuteReader();
if (sdr.HasRows)
{
//close datareader
if (sdr != null)
{
sdr.Close();
}
string datestr = "Select * from V_LV_USE where CAL_Date between '" + tPost.FromDate.ToString("yyyy-MM-dd") + "' and '" + tPost.ToDate.ToString("yyyy-MM-dd") + "'";
DataSet trsLV = Utils.GetData(datestr, cnstr, out err2);
//used for leave days count
decimal tmpleavecons = 0;
#region UpdateSchLeave
foreach (DataRow trslr in trsLV.Tables[0].Rows)
{
DateTime CalDt = Convert.ToDateTime(trslr["Cal_Date"]);
string sqlSch = "Select * from MastLeaveSchedule Where EmpUnqID ='" + tPost.EmpUnqID + "' And tdate ='" + CalDt.ToString("yyyy-MM-dd") + "'";
//create data adapter
DataSet dsSchLv = new DataSet();
SqlDataAdapter daSchLv = new SqlDataAdapter(new SqlCommand(sqlSch, cn, tr));
SqlCommandBuilder cmdbSchLv = new SqlCommandBuilder(daSchLv);
daSchLv.InsertCommand = cmdbSchLv.GetInsertCommand();
daSchLv.InsertCommand.Transaction = tr;
daSchLv.UpdateCommand = cmdbSchLv.GetUpdateCommand();
daSchLv.UpdateCommand.Transaction = tr;
daSchLv.DeleteCommand = cmdbSchLv.GetDeleteCommand();
daSchLv.DeleteCommand.Transaction = tr;
daSchLv.AcceptChangesDuringUpdate = false;
daSchLv.Fill(dsSchLv, "MastLeaveSchedule");
hasRows = dsSchLv.Tables.Cast <DataTable>().Any(table => table.Rows.Count != 0);
if (hasRows)
{
try
{
string sqldel = "Delete From MastLeaveSchedule Where " +
" EmpUnqID='" + tPost.EmpUnqID + "' " +
" And tDate ='" + CalDt.ToString("yyyy-MM-dd") + "'" +
" And SchLeave='" + tPost.LeaveTyp + "' and WrkGrp ='COMP' AND ConsInTime is null and ConsOutTime is null " +
" And ConsOverTime is null and ConsShift is null ";
SqlCommand cmd = new SqlCommand(sqldel, cn, tr);
int t = (int)cmd.ExecuteNonQuery();
sqldel = "Update MastLeaveSchedule Set SchLeave = null, SchLeaveHalf = 0,SchLeaveAdv = 0 Where " +
" EmpUnqID='" + tPost.EmpUnqID + "' " +
" And tDate ='" + CalDt.ToString("yyyy-MM-dd") + "'" +
" And SchLeave='" + tPost.LeaveTyp + "' and WrkGrp ='COMP' ";
SqlCommand cmd2 = new SqlCommand(sqldel, cn, tr);
t = (int)cmd2.ExecuteNonQuery();
}
catch (Exception ex)
{
tr.Rollback();
tr.Dispose();
tPost.ERROR += ex.Message.ToString();
tPost.PostedFlg = false;
return(Request.CreateResponse(HttpStatusCode.BadRequest, tPost));
}
}
dsSchLv.Clear();
daSchLv.Fill(dsSchLv, "MastLeaveSchedule");
DataRow drSch;
hasRows = dsSchLv.Tables.Cast <DataTable>().Any(table => table.Rows.Count != 0);
if (!hasRows)
{
try
{
string tsql3 = "Insert into MastLeaveSchedule (EmpUnqId,WrkGrp,tDate,AddDt,AddID) Values (" +
"'" + tPost.EmpUnqID + "','COMP','" + CalDt.ToString("yyyy-MM-dd") + "',GetDate(),'" + tPost.AttdUser + "')";
SqlCommand cmd = new SqlCommand(tsql3, cn, tr);
cmd.ExecuteNonQuery();
dsSchLv.Clear();
daSchLv.Fill(dsSchLv, "MastLeaveSchedule");
drSch = dsSchLv.Tables["MastLeaveSchedule"].Rows[0];
}
catch (Exception ex)
{
tr.Rollback();
tr.Dispose();
tPost.ERROR += ex.Message.ToString();
tPost.PostedFlg = false;
return(Request.CreateResponse(HttpStatusCode.BadRequest, tPost));
}
}
else
{
drSch = dsSchLv.Tables["MastLeaveSchedule"].Rows[0];
}
string PublicHLTyp = string.Empty;
string SchShift = string.Empty;
string tsql = string.Empty;
string err3 = string.Empty;
err2 = string.Empty;
err3 = string.Empty;
tsql = "Select PublicHLTyp from HolidayMast Where WrkGrp ='COMP' and tDate ='" + CalDt.ToString("yyyy-MM-dd") + "'";
PublicHLTyp = Utils.GetDescription(tsql, cnstr, out err2);
if (!string.IsNullOrEmpty(err2))
{
tr.Rollback();
tr.Dispose();
tPost.ERROR += err2;
tPost.PostedFlg = false;
return(Request.CreateResponse(HttpStatusCode.BadRequest, tPost));
}
tsql = "Select ScheduleShift from AttdData where EmpUnqID ='" + tPost.EmpUnqID + "' " +
" And CompCode = '01' And WrkGrp = 'COMP' " +
" And tYear = '" + CalDt.Year.ToString() + "' And tDate ='" + CalDt.ToString("yyyy-MM-dd") + "'";
err2 = string.Empty;
SchShift = Utils.GetDescription(tsql, cnstr, out err2);
if (!string.IsNullOrEmpty(err2))
{
tr.Rollback();
tr.Dispose();
tPost.ERROR += err2;
tPost.PostedFlg = false;
return(Request.CreateResponse(HttpStatusCode.BadRequest, tPost));
}
if (LeaveDays.WOEntReq == false)
{
string sqldel = "Update MastLeaveSchedule Set SchLeave = null, SchLeaveHalf = 0,SchLeaveAdv = 0 Where " +
" EmpUnqID='" + tPost.EmpUnqID + "' " +
" And tDate ='" + CalDt.ToString("yyyy-MM-dd") + "'" +
" And SchLeave in ('WO','HL') and WrkGrp ='COMP' ";
SqlCommand cmd2 = new SqlCommand(sqldel, cn, tr);
int t = (int)cmd2.ExecuteNonQuery();
drSch["schLeave"] = tPost.LeaveTyp;
drSch["AddId"] = tPost.AttdUser;
drSch["AddDt"] = Utils.GetSystemDateTime(cnstr);
drSch["UpdDt"] = Utils.GetSystemDateTime(cnstr);
drSch["UpdId"] = tPost.AttdUser;
tmpleavecons += 1;
}
else if (LeaveDays.WOEntReq && PublicHLTyp != "")
{
drSch["schLeave"] = PublicHLTyp;
drSch["AddId"] = "HLCal";
drSch["AddDt"] = Utils.GetSystemDateTime(cnstr);
drSch["UpdDt"] = Utils.GetSystemDateTime(cnstr);
drSch["UpdId"] = tPost.AttdUser;
}
else if (LeaveDays.WOEntReq && PublicHLTyp == "" && drSch["SchLeave"].ToString() == "WO")
{
drSch["schLeave"] = "WO";
drSch["AddId"] = "ShiftSch";
drSch["UpdDt"] = Utils.GetSystemDateTime(cnstr);
drSch["UpdId"] = tPost.AttdUser;
}
else if (LeaveDays.WOEntReq && PublicHLTyp == "" && SchShift == "WO")
{
drSch["schLeave"] = "WO";
drSch["AddId"] = "ShiftSch";
drSch["UpdDt"] = Utils.GetSystemDateTime(cnstr);
drSch["UpdId"] = tPost.AttdUser;
}
else
{
drSch["schLeave"] = tPost.LeaveTyp;
drSch["AddId"] = tPost.AttdUser;
drSch["AddDt"] = Utils.GetSystemDateTime(cnstr);
drSch["UpdDt"] = Utils.GetSystemDateTime(cnstr);
drSch["UpdId"] = tPost.AttdUser;
tmpleavecons += 1;
}
drSch["SchLeaveHalf"] = (tPost.HalfDay ? 1 : 0);
//dsSchLv.Tables["MastLeaveSchedule"].Rows.Add(drSch);
dsSchLv.AcceptChanges();
try
{
string sql2 = "Update MastLeaveSchedule Set AddId ='" + drSch["AddId"].ToString() + "'," +
" AddDt='" + Convert.ToDateTime(drSch["AddDt"]).ToString("yyyy-MM-dd HH:mm:ss") + "'," +
" UpdDt={0},UpdID={1}, SchLeave='" + drSch["SchLeave"].ToString() + "'," +
" SchLeaveAdv='" + (Convert.ToBoolean(drSch["SchLeaveAdv"]) ? 1 : 0).ToString() + "'," +
" SchLeaveHalf ='" + (tPost.HalfDay ? 1 : 0).ToString() + "' Where SanId = '" + drSch["SanID"].ToString() + "'";
sql2 = string.Format(sql2,
((drSch["UpdDt"] == DBNull.Value) ? " null " : "'" + Convert.ToDateTime(drSch["UpdDt"]).ToString("yyyy-MM-dd HH:mm") + "'"),
(drSch["UpdID"] == DBNull.Value) ? " null " : "'" + tPost.AttdUser + "'"
);
SqlCommand cmd = new SqlCommand(sql2, cn, tr);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
tr.Rollback();
tr.Dispose();
tPost.ERROR += ex.Message;
tPost.PostedFlg = false;
return(Request.CreateResponse(HttpStatusCode.BadRequest, tPost));
}
}//foreach dateloop - trsLV
#endregion
}
try
{
#region UpdateLeaveBal
using (SqlCommand cmd20 = new SqlCommand())
{
try
{
string tsql1 = "Update LeaveBal " +
" Set AVL = AVL + '" + LeaveDays.LeaveDays.ToString() + "'" +
" ,ADV = 0 " +
" ,UPDDT = GetDate(),UPDID = '" + tPost.AttdUser + "' " +
" Where " +
" CompCode ='01'" +
" And WrkGrp='COMP'" +
" And tYear ='" + tPost.FromDate.Year.ToString() + "'" +
" And EmpUnqID ='" + tPost.EmpUnqID + "'" +
" And LeaveTyp='" + tPost.LeaveTyp + "'";
cmd20.CommandType = CommandType.Text;
cmd20.CommandText = tsql1;
cmd20.Connection = new SqlConnection(cnstr);
cmd20.Connection.Open();
cmd20.ExecuteNonQuery();
cmd20.Connection.Close();
}
catch (Exception ex)
{
tr.Rollback();
tr.Dispose();
tPost.ERROR += ex.Message;
tPost.PostedFlg = false;
return(Request.CreateResponse(HttpStatusCode.BadRequest, tPost));
}
}
#endregion
tr.Commit();
tr.Dispose();
}catch (Exception ex) {
tr.Rollback();
tr.Dispose();
tPost.ERROR += ex.Message.ToString();
tPost.PostedFlg = false;
return(Request.CreateResponse(HttpStatusCode.BadRequest, tPost));
}
try
{
//string err = string.Empty;
//bool t = ReconsileLeaveBal(tPost.EmpUnqID, tPost.LeaveTyp, tPost.AttdUser, out err);
#region ProcessData
string sql1 = "Insert into AttdWorker ( EmpUnqId,FromDt,ToDt,WorkerId,DoneFlg,PushFlg,addid,ProcessType ) " +
" values ('" + tPost.EmpUnqID + "','" + tPost.FromDate.ToString("yyyy-MM-dd") + "','" + tPost.ToDate.ToString("yyyy-MM-dd") + "'," +
" '" + tPost.AttdUser + "',0,0,'" + tPost.AttdUser + "','ATTD' )";
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = cn;
cmd.CommandText = sql1;
cmd.CommandTimeout = 0;
cmd.ExecuteNonQuery();
}
sql1 = "Insert into AttdWorker ( EmpUnqId,FromDt,ToDt,WorkerId,DoneFlg,PushFlg,addid,ProcessType ) " +
" values ('" + tPost.EmpUnqID + "','" + tPost.FromDate.ToString("yyyy-MM-dd") + "','" + tPost.ToDate.ToString("yyyy-MM-dd") + "'," +
" '" + tPost.AttdUser + "',0,0,'" + tPost.AttdUser + "','LUNCHINOUT' )";
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = cn;
cmd.CommandText = sql1;
cmd.CommandTimeout = 0;
cmd.ExecuteNonQuery();
}
#endregion
tPost.PostedFlg = true;
}
catch (Exception ex)
{
tPost.ERROR += ex.Message.ToString();
tPost.PostedFlg = false;
return(Request.CreateResponse(HttpStatusCode.BadRequest, tPost));
}
}//end sqlcon using
#endregion
return(Request.CreateResponse(HttpStatusCode.OK, tPost));
}
private void button3_Click(object sender, EventArgs e)
{
try
{
baglantim.Open();
string CariID = "";
string HKodu = "";
// string HAdi;
#region Cari Bilgisi Alınıyor
SqlCommand CariBilgi = new SqlCommand("", baglantim);
// CariBilgi.CommandType = CommandType.StoredProcedure;
CariBilgi = new SqlCommand("SELECT HESAPADI FROM CARIKART where HESAPADI like 'KARE BİLGİSAYAR SANAYİ VE TİCARET A.Ş.%'", baglantim);
SqlDataReader al = CariBilgi.ExecuteReader();
if (al.HasRows == false)
{
SqlConnection baglantim = new SqlConnection("Server=PCYAZ\\SQLEXPRESS,1433;Database=Verim_1;uid=sa;pwd=admin-*741852963");
baglantim.Open();
string st = "INSERT INTO CARIKART (HESAPKODU,HESAPADI,IslemKodu,DurumKodu) VALUES ('000007','KARE BİLGİSAYAR SANAYİ VE TİCARET A.Ş.','CRK_AliciSatici','Aktif')";
SqlCommand CariBilgim = new SqlCommand(st, baglantim);
CariBilgim.ExecuteNonQuery();
baglantim.Close();
}
#endregion
al.Close();
// Fatura Sorgulanıyor
SqlCommand Comm = new SqlCommand("__FaturaInsert", baglantim); //Burada SQL'de oluşturduğumuz procedureun adını yazıyoruz
Comm.CommandType = CommandType.StoredProcedure;
System.Data.SqlClient.SqlCommandBuilder cb;
// System.Data.OleDb.OleDbCommandBuilder cb;
// cb = new OleDbCommandBuilder(adaptor);
cb = new SqlCommandBuilder(adaptor);
komut = new SqlCommand("SELECT CariKart_ID,HESAPKODU FROM CARIKART where HESAPADI like '%KARE BİLGİSAYAR SANAYİ%'", baglantim);
SqlDataReader bilgigetir = komut.ExecuteReader();
while (bilgigetir.Read())
{
if (bilgigetir.GetString(1) != "")
{
CariID = bilgigetir["CariKart_ID"].ToString();
HKodu = bilgigetir["HESAPKODU"].ToString();
// HAdi = bilgigetir["HESAPADI"].ToString() ;
break;
}
}
bilgigetir.Close();
Comm.Parameters.AddWithValue("@SevkAdres_FID", DBNull.Value);
Comm.Parameters.AddWithValue("@PerFatura_FID", DBNull.Value);
Comm.Parameters.AddWithValue("@Plasiyer_FID", DBNull.Value);
Comm.Parameters.AddWithValue("@CariKart_FID", CariID);
Comm.Parameters.AddWithValue("@FATURANO", fatno.Text);
Comm.Parameters.AddWithValue("@ISLEMTIPI", "Alis Faturasi");
Comm.Parameters.AddWithValue("@TARIH", Convert.ToDateTime(fattarih.Text));
Comm.Parameters.AddWithValue("@DONEM", "Ocak");
Comm.Parameters.AddWithValue("@HESAPKODU", HKodu);
Comm.Parameters.AddWithValue("@BELGESERI", DBNull.Value);
Comm.Parameters.AddWithValue("@BELGENO", fatno.Text);
Comm.Parameters.AddWithValue("@OZELKOD", DBNull.Value);
Comm.Parameters.AddWithValue("@KDV", Convert.ToDecimal(kdv.Text));
Comm.Parameters.AddWithValue("@ACIKLAMA", kurbilgisi.Text);
Comm.Parameters.AddWithValue("@MASRAF", 0);
Comm.Parameters.AddWithValue("@INDIRIM", 0);
Comm.Parameters.AddWithValue("@TOPLAM", Convert.ToDecimal(malhiztoplamTL.Text));
Comm.Parameters.AddWithValue("@NETBORC", DBNull.Value);
Comm.Parameters.AddWithValue("@NETALACAK", Convert.ToDecimal(odenecektutar.Text));
Comm.Parameters.AddWithValue("@PARABIRIMI", "$");
Comm.Parameters.AddWithValue("@ISLEMKURU", DBNull.Value);
Comm.Parameters.AddWithValue("@CARIKURU", DBNull.Value);
Comm.Parameters.AddWithValue("@TLBORC", DBNull.Value);
Comm.Parameters.AddWithValue("@TLALACAK", Convert.ToDecimal(odenecektutar.Text));
Comm.Parameters.AddWithValue("@DOVIZBORC", DBNull.Value);
Comm.Parameters.AddWithValue("@DOVIZALACAK", DBNull.Value);
Comm.Parameters.AddWithValue("@KDVDAHIL", DBNull.Value);
Comm.Parameters.AddWithValue("@CARIDOVIZI", "TL");
Comm.Parameters.AddWithValue("@KAPATMA", DBNull.Value);
Comm.Parameters.AddWithValue("@VADESI", DBNull.Value);
Comm.Parameters.AddWithValue("@KAPATMANO", DBNull.Value);
Comm.Parameters.AddWithValue("@SAAT", fatsaati.Text);
Comm.Parameters.AddWithValue("@YAZDIRMA", DBNull.Value);
Comm.Parameters.AddWithValue("@SEVKTARIHI", DBNull.Value);
Comm.Parameters.AddWithValue("@SATICI", DBNull.Value);
Comm.Parameters.AddWithValue("@ACIKLAMA2", DBNull.Value);
Comm.Parameters.AddWithValue("@ACIKLAMA3", DBNull.Value);
Comm.Parameters.AddWithValue("@ACIKLAMA4 ", DBNull.Value);
Comm.Parameters.AddWithValue("@FATURAMALIYETI", 0);
Comm.Parameters.AddWithValue("@ARATOPLAM", Convert.ToDecimal(malhiztoplamTL.Text));
Comm.Parameters.AddWithValue("@OTV", 0);
Comm.Parameters.AddWithValue("@TEVKIFAT_KDVSI", 0);
Comm.Parameters.AddWithValue("@TEVKIFAT", DBNull.Value);
Comm.Parameters.AddWithValue("@HESAPADI", hesapadi.Text);
Comm.Parameters.AddWithValue("@Depolar_FID", 1);
Comm.Parameters.AddWithValue("@SATICIPRIMI", DBNull.Value);
Comm.Parameters.AddWithValue("@SECIM", DBNull.Value);
Comm.Parameters.AddWithValue("@ISLEMKODU", "FT_Alis");
Comm.Parameters.AddWithValue("@DONEMNO", "2019_01");
Comm.Parameters.AddWithValue("@STOPAJORANI", DBNull.Value);
Comm.Parameters.AddWithValue("@STOPAJ", DBNull.Value);
Comm.Parameters.AddWithValue("@SAYFA", DBNull.Value);
Comm.Parameters.AddWithValue("@ONAY", DBNull.Value);
Comm.Parameters.AddWithValue("@KAPANMATRH", DBNull.Value);
Comm.Parameters.AddWithValue("@KdvDurumKodu", "KDV_Dahil");
Comm.Parameters.AddWithValue("@OzelKod_FID", DBNull.Value);
Comm.Parameters.AddWithValue("@ToplamMiktar", DBNull.Value);
Comm.Parameters.AddWithValue("@ToplamReferansMiktar", DBNull.Value);
Comm.Parameters.AddWithValue("@GC_Tipi", 1);
Comm.Parameters.AddWithValue("@FaturaAdres_FID", DBNull.Value);
Comm.Parameters.AddWithValue("@Upt_Flg", 1);
Comm.Parameters.AddWithValue("@Iptal", DBNull.Value);
Comm.Parameters.AddWithValue("@BakiyeBorc", DBNull.Value);
Comm.Parameters.AddWithValue("@BakiyeAlacak", DBNull.Value);
Comm.Parameters.AddWithValue("@Rota", DBNull.Value);
Comm.Parameters.AddWithValue("@Muhasebe", DBNull.Value);
Comm.Parameters.AddWithValue("@Sube_FID", DBNull.Value);
Comm.Parameters.AddWithValue("@DigerGider", DBNull.Value);
Comm.Parameters.AddWithValue("@FaizTarihi", DBNull.Value);
Comm.Parameters.AddWithValue("@VadeUygula", DBNull.Value);
Comm.Parameters.AddWithValue("@KDVeksiTEVKIFAT", DBNull.Value);
Comm.Parameters.AddWithValue("@Tevk_Payi", DBNull.Value);
Comm.Parameters.AddWithValue("@Tevk_Paydasi", DBNull.Value);
Comm.Parameters.AddWithValue("@VFDekontisl_FID", DBNull.Value);
Comm.Parameters.AddWithValue("@UUID", DBNull.Value);
Comm.Parameters.AddWithValue("@EFaturaNO", DBNull.Value);
Comm.Parameters.AddWithValue("@EFaturaDurumu", DBNull.Value);
Comm.Parameters.AddWithValue("@EFaturaAltDurumu", DBNull.Value);
Comm.Parameters.AddWithValue("@EFaturaGIBCode", DBNull.Value);
Comm.Parameters.AddWithValue("@VadeFarki", DBNull.Value);
Comm.Parameters.AddWithValue("@DipIsktOrani", DBNull.Value);
Comm.Parameters.AddWithValue("@KargoGideri", DBNull.Value);
Comm.Parameters.AddWithValue("@KomisyonOrani", DBNull.Value);
Comm.Parameters.AddWithValue("@KomisyonGideri", DBNull.Value);
Comm.Parameters.AddWithValue("@TevkifatKodu", DBNull.Value);
Comm.Parameters.AddWithValue("@EkVergiler", DBNull.Value);
Comm.Parameters.AddWithValue("@Borc ", DBNull.Value);
Comm.Parameters.AddWithValue("@Alacak ", 1);
Comm.Parameters.AddWithValue("@EFaturaTipi", DBNull.Value);
Comm.Parameters.AddWithValue("@EFaturaTuru", DBNull.Value);
Comm.Parameters.AddWithValue("@EFaturaSenaryo ", DBNull.Value);
Comm.Parameters.AddWithValue("@EFaturaDurumKodu", DBNull.Value);
Comm.Parameters.AddWithValue("@YeniIrsaliyeNO ", DBNull.Value);
Comm.Parameters.AddWithValue("@EarsivEmailiGonderildi", DBNull.Value);
Comm.Parameters.AddWithValue("@FaturaLog_FID ", DBNull.Value);
object Fatura_ID = Comm.ExecuteScalar();
MessageBox.Show("Başarıyla Kaydedildi");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
baglantim.Close();
}
// }
// baglantim.Close();
FatraDTKAydet();
}
/// <summary>
/// 获取选择数量语句
/// </summary>
/// <param name="baseCommand">源选择语句</param>
/// <returns>选择数量语句</returns>
internal static String InternalGetCountCommand(SelectCommand baseCommand)
{
SqlCommandBuilder sb = new SqlCommandBuilder(baseCommand.Database);
List<SqlQueryField> queryFields = new List<SqlQueryField>();
queryFields.Add(SqlQueryField.InternalCreateFromAggregateFunction(baseCommand, SqlAggregateFunction.Count));
sb.AppendSelectPrefix();
sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(queryFields);
sb.AppendSelectFromAndJoins(baseCommand.TableName, baseCommand.IsFromSql, baseCommand.SqlJoins);
sb.AppendWhere(baseCommand.SqlWhere);
sb.AppendSelectGroupBys(baseCommand.GroupByColumns);
sb.AppendHaving(baseCommand.SqlHaving);
return sb.ToString();
}
private void button5_Click(object sender, EventArgs e)
{
SCB = new SqlCommandBuilder(SDA);
SDA.Update(DT);
}
public void ORDERAdd(string Meal, string Dish, string OrderBoxed)
{
try
{
InsertsbSql.Clear();
sbSql.Clear();
//ADD COPTC
if (Meal.Equals("10+20"))
{
DataSet ds1 = new DataSet();
//20210902密
Class1 TKID = new Class1();//用new 建立類別實體
SqlConnectionStringBuilder sqlsb = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString);
//資料庫使用者密碼解密
sqlsb.Password = TKID.Decryption(sqlsb.Password);
sqlsb.UserID = TKID.Decryption(sqlsb.UserID);
String connectionString;
sqlConn = new SqlConnection(sqlsb.ConnectionString);
sbSql.Clear();
sbSqlQuery.Clear();
sbSql.AppendFormat(@"SELECT [SERNO],[ID],[NAME],[CARDNO],[DATE],[MEAL],[DISH],[NUM],[EATNUM] FROM [TKBOXEDMEAL].[dbo].[LOCALEMPORDER] WHERE CONVERT(varchar(100),[DATE], 112)=CONVERT(varchar(100),GETDATE(), 112) AND [ID]='{0}' AND [MEAL]='{1}' ", EmployeeID, "10");
adapter = new SqlDataAdapter(@"" + sbSql, sqlConn);
sqlCmdBuilder = new SqlCommandBuilder(adapter);
sqlConn.Open();
ds1.Clear();
adapter.Fill(ds1, "TEMPds1");
sqlConn.Close();
if (ds1.Tables["TEMPds1"].Rows.Count == 0)
{
//InsertsbSql.AppendFormat(" DELETE [TKBOXEDMEAL].[dbo].[EMPORDER] WHERE CONVERT(varchar(100),[DATE], 112)=CONVERT(varchar(100),GETDATE(), 112) AND [ID]='{0}' AND ([MEAL]='10' OR [MEAL]='20') AND [EATNUM]=0", EmployeeID, Meal);
Meal = "10";
InsertsbSql.Append(" ");
InsertsbSql.AppendFormat(" INSERT INTO [TKBOXEDMEAL].[dbo].[LOCALEMPORDER] ([SERNO],[ID],[NAME],[CARDNO],[DATE],[MEAL],[DISH],[NUM]) VALUES ('{0}','{1}','{2}','{3}',GETDATE(),'{4}','{5}',1) ", dateTimePicker1.Value.ToString("yyyyMMdd") + DateTime.Now.ToString("HHmmss"), EmployeeID, Name, CardNo, Meal, Dish);
}
sqlConn = new SqlConnection(sqlsb.ConnectionString);
sbSql.Clear();
sbSqlQuery.Clear();
sbSql.AppendFormat(@"SELECT [SERNO],[ID],[NAME],[CARDNO],[DATE],[MEAL],[DISH],[NUM],[EATNUM] FROM [TKBOXEDMEAL].[dbo].[LOCALEMPORDER] WHERE CONVERT(varchar(100),[DATE], 112)=CONVERT(varchar(100),GETDATE(), 112) AND [ID]='{0}' AND [MEAL]='{1}' ", EmployeeID, "20");
adapter = new SqlDataAdapter(@"" + sbSql, sqlConn);
sqlCmdBuilder = new SqlCommandBuilder(adapter);
sqlConn.Open();
ds1.Clear();
adapter.Fill(ds1, "TEMPds1");
sqlConn.Close();
if (ds1.Tables["TEMPds1"].Rows.Count == 0)
{
Meal = "20";
InsertsbSql.Append(" ");
InsertsbSql.AppendFormat(" INSERT INTO [TKBOXEDMEAL].[dbo].[LOCALEMPORDER] ([SERNO],[ID],[NAME],[CARDNO],[DATE],[MEAL],[DISH],[NUM]) VALUES ('{0}','{1}','{2}','{3}',GETDATE(),'{4}','{5}',1) ", dateTimePicker1.Value.ToString("yyyyMMdd") + DateTime.Now.ToString("HHmmss"), EmployeeID, Name, CardNo, Meal, Dish);
}
}
else
{
DataSet ds1 = new DataSet();
//20210902密
Class1 TKID = new Class1();//用new 建立類別實體
SqlConnectionStringBuilder sqlsb = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString);
//資料庫使用者密碼解密
sqlsb.Password = TKID.Decryption(sqlsb.Password);
sqlsb.UserID = TKID.Decryption(sqlsb.UserID);
String connectionString;
sqlConn = new SqlConnection(sqlsb.ConnectionString);
sbSql.Clear();
sbSqlQuery.Clear();
sbSql.AppendFormat(@"SELECT [SERNO],[ID],[NAME],[CARDNO],[DATE],[MEAL],[DISH],[NUM],[EATNUM] FROM [TKBOXEDMEAL].[dbo].[LOCALEMPORDER] WHERE CONVERT(varchar(100),[DATE], 112)=CONVERT(varchar(100),'{2}', 112) AND [ID]='{0}' AND [MEAL]='{1}' ", EmployeeID, Meal, dateTimePicker1.Value.ToString("yyyyMMdd"));
adapter = new SqlDataAdapter(@"" + sbSql, sqlConn);
sqlCmdBuilder = new SqlCommandBuilder(adapter);
sqlConn.Open();
ds1.Clear();
adapter.Fill(ds1, "TEMPds1");
if (ds1.Tables["TEMPds1"].Rows.Count > 0)
{
Name = ds1.Tables["TEMPds1"].Rows[0][2].ToString();
}
sqlConn.Close();
if (ds1.Tables["TEMPds1"].Rows.Count == 0)
{
InsertsbSql.Append(" ");
//InsertsbSql.AppendFormat(" DELETE [TKBOXEDMEAL].[dbo].[EMPORDER] WHERE CONVERT(varchar(100),[DATE], 112)=CONVERT(varchar(100),GETDATE(), 112) AND [ID]='{0}' AND [MEAL]='{1}' AND [EATNUM]=0 ", EmployeeID, Meal);
InsertsbSql.AppendFormat(" INSERT INTO [TKBOXEDMEAL].[dbo].[LOCALEMPORDER] ([SERNO],[ID],[NAME],[CARDNO],[DATE],[MEAL],[DISH],[NUM]) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}',{7}) ", dateTimePicker1.Value.ToString("yyyyMMdd") + DateTime.Now.ToString("HHmmss"), EmployeeID, Name, CardNo, dateTimePicker1.Value.ToString("yyyyMMdd"), Meal, Dish, textBox3.Text);
}
else
{
//AutoClosingMessageBox.Show("已經訂過餐了!!", "TITLE", messagetime);
SHOWMESSAGE(Name + "已經訂過餐了!!!!");
}
}
if (!string.IsNullOrEmpty(InsertsbSql.ToString()))
{
//20210902密
Class1 TKID = new Class1();//用new 建立類別實體
SqlConnectionStringBuilder sqlsb = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString);
//資料庫使用者密碼解密
sqlsb.Password = TKID.Decryption(sqlsb.Password);
sqlsb.UserID = TKID.Decryption(sqlsb.UserID);
String connectionString;
sqlConn = new SqlConnection(sqlsb.ConnectionString);
sqlConn.Close();
sqlConn.Open();
tran = sqlConn.BeginTransaction();
cmd.Connection = sqlConn;
cmd.CommandTimeout = 60;
cmd.CommandText = InsertsbSql.ToString();
cmd.Transaction = tran;
result = cmd.ExecuteNonQuery();
if (result == 0)
{
tran.Rollback(); //交易取消
SHOWMESSAGE(Name + " 訂餐失敗!!");
}
else
{
tran.Commit(); //執行交易
SHOWMESSAGE(Name + " 訂餐成功!!" + " 訂了: " + OrderBoxed.ToString());
}
sqlConn.Close();
}
}
catch
{
}
finally
{
}
}
private void button1_Click(object sender, EventArgs e)
{
if (textBox1.Text == "")
{
MessageBox.Show("用户名不能为空");
textBox2.Text = textBox3.Text = "";
}
else if (textBox2.Text == "")
{
MessageBox.Show("密码不能为空");
textBox3.Text = "";
}
else if (textBox3.Text == "")
{
MessageBox.Show("确认密码不能为空");
}
else if (textBox2.Text != textBox3.Text)
{
MessageBox.Show("两次密码不一致请重新输入");
textBox2.Text = textBox3.Text = "";
}
else
{
SqlConnection sql = new SqlConnection("server=.;database=XueXi;uid=sa;pwd=abc123*");
sql.Open();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand("select * from T_User", sql);
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
sda.Fill(ds);
SqlCommandBuilder scb = new SqlCommandBuilder(sda);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ds.Tables[0].Rows[i]["Name"].ToString() == textBox1.Text)
{
MessageBox.Show("已存在该用户名");
textBox1.Text = textBox2.Text = textBox3.Text = "";
return;
}
}
string insertstr = "insert into dbo.T_User (Name,Password) values (\'" + textBox1.Text + "\', \'" + textBox2.Text + "\')";
SqlCommand cmd1 = new SqlCommand(insertstr, sql);
int j = cmd1.ExecuteNonQuery();
if (j == 1)
{
DialogResult dialogr = MessageBox.Show("注册成功，点击确定进入登录页面", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
if (dialogr == DialogResult.OK)
{
this.Hide();
Form7 frm = new Form7();
frm.Show();
}
}
else
{
MessageBox.Show("失败，请重新注册");
textBox1.Text = textBox2.Text = textBox3.Text = "";
}
sql.Close();
}
}
public void Search()
{
ds.Clear();
try
{
//20210902密
Class1 TKID = new Class1();//用new 建立類別實體
SqlConnectionStringBuilder sqlsb = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString);
//資料庫使用者密碼解密
sqlsb.Password = TKID.Decryption(sqlsb.Password);
sqlsb.UserID = TKID.Decryption(sqlsb.UserID);
String connectionString;
sqlConn = new SqlConnection(sqlsb.ConnectionString);
sbSql.Clear();
sbSqlQuery.Clear();
sbSql.AppendFormat(@" SELECT [LOCALEMPORDER].[ID] AS '工號',[LOCALEMPORDER].[NAME] AS '姓名',[MEAL].[MEALNAME] AS '餐別',[MEALDISH].[DISHNAME] AS '葷素' ,[LOCALEMPORDER].[NUM] AS '訂餐量'");
sbSql.AppendFormat(@" ,[LOCALEMPORDER].[SERNO],[LOCALEMPORDER].[CARDNO],[LOCALEMPORDER].[DATE],[LOCALEMPORDER].[MEAL],[LOCALEMPORDER].[DISH] ");
sbSql.AppendFormat(@" FROM [TKBOXEDMEAL].[dbo].[LOCALEMPORDER] ");
sbSql.AppendFormat(@" LEFT JOIN [TKBOXEDMEAL].[dbo].[MEAL] ON [MEAL].[MEAL]=[LOCALEMPORDER].[MEAL] ");
sbSql.AppendFormat(@" LEFT JOIN [TKBOXEDMEAL].[dbo].[MEALDISH] ON [MEALDISH].[DISH]=[LOCALEMPORDER].[DISH] ");
sbSql.AppendFormat(@" WHERE CONVERT(NVARCHAR,[LOCALEMPORDER].[DATE],112)='{0}' ", dateTimePicker1.Value.ToString("yyyyMMdd"));
sbSql.AppendFormat(@" ORDER BY [LOCALEMPORDER].[ID] ");
sbSql.AppendFormat(@" ");
sbSql.AppendFormat(@" ");
sbSql.AppendFormat(@" ");
adapter = new SqlDataAdapter(@"" + sbSql, sqlConn);
sqlCmdBuilder = new SqlCommandBuilder(adapter);
sqlConn.Open();
ds.Clear();
adapter.Fill(ds, "TEMPds1");
sqlConn.Close();
if (ds.Tables["TEMPds1"].Rows.Count == 0)
{
dataGridView1.DataSource = null;
}
else
{
if (ds.Tables["TEMPds1"].Rows.Count >= 1)
{
dataGridView1.DataSource = ds.Tables["TEMPds1"];
dataGridView1.AutoResizeColumns();
}
}
}
catch
{
}
finally
{
}
}
public void ClassToSQL(Dictionary <string, string> dic)
{
// SqlConnection sqlConnection = new SqlConnection("Data Source=.;Initial Catalog=hxlh;uid=sa;pwd=12345678;Integrated Security=True");
if (IsExist(dic["ordercode"], dic["ln_no"]) == false)
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("Select * from orders", sqlConnection);
SqlCommandBuilder bldr = new SqlCommandBuilder(sqlDataAdapter);
sqlDataAdapter.InsertCommand = bldr.GetInsertCommand();
//创建DataSet
DataSet dataSet = new DataSet("OrderIn");
//确定现有 DataSet 架构与传入数据不匹配时需要执行的操作。
sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
sqlDataAdapter.Fill(dataSet, "orders");
//DataRow表示DataTable中的一行数据
System.Data.DataRow dataRow1;
dataRow1 = dataSet.Tables["orders"].NewRow();
dataRow1["ordercode"] = dic["ordercode"];
dataRow1["orderstatus"] = dic["orderstatus"];
dataRow1["saler"] = dic["sellerSignerFullname"];
dataRow1["mj_name"] = dic["mj_name"];
dataRow1["mj_email"] = dic["mj_email"];
dataRow1["orderdt"] = dic["orderdt"];
dataRow1["paydt"] = dic["paydt"];
dataRow1["shr"] = dic["contactPerson"];
dataRow1["mjxzwl"] = dic["mjxzwl"];
dataRow1["wlfy"] = dic["wlfy"];
string nation = getCountryFullName(dic["nation"]);
dataRow1["nation"] = nation;
dataRow1["province"] = dic["province"];
dataRow1["city"] = dic["city"];
dataRow1["addr"] = dic["addr"];
dataRow1["code"] = dic["code"];
if (dic.ContainsKey("phone"))
{
string temp;
temp = dic["phone"].ToString().Trim();
temp.Replace("-", "");
if (temp.Length < 6 || temp.Length > 20)
{
dataRow1["phone"] = "";
}
else
{
dataRow1["phone"] = dic["phone"];
}
}
else
{
dataRow1["phone"] = "";
}
if (dic.ContainsKey("mobile"))
{
string temp2 = dic["mobile"].ToString().Trim();
temp2.Replace("-", "");
if (temp2.Length < 6 || temp2.Length > 20)
{
dataRow1["mobile"] = "";
}
else
{
dataRow1["mobile"] = dic["mobile"];
}
}
else
{
dataRow1["mobile"] = "";
}
if (dataRow1["mobile"].ToString() == "" && dataRow1["phone"].ToString() == "")
{
dataRow1["isyc"] = 1;
}
//除了1其他项总金额栏都填写0
if (dic["ln_no"] == "1")
{
dataRow1["all_sum"] = dic["all_sum"];
}
else
{
dataRow1["all_sum"] = 0;
}
dataRow1["projects"] = dic["projects"];
if (dic["skuCode"] == "")
{
dataRow1["item_bh"] = dic["item_bh"];
}
else
{
dataRow1["item_bh"] = dic["skuCode"];
}
if (!IsItemBhExist(dataRow1["item_bh"].ToString()))
{
dataRow1["isyc"] = 1;
}
string itemName = GetItemName(dataRow1["item_bh"].ToString());
if (itemName != "")
{
dataRow1["item_nm"] = itemName;
}
if (dic.ContainsKey("color"))
{
dataRow1["color"] = dic["color"];
}
if (dic.ContainsKey("size"))
{
dataRow1["size"] = dic["size"];
}
if (dic.ContainsKey("cup size"))
{
string size = dic["cup size"];
if (dic.ContainsKey("bands size"))
{
size = size + "/" + dic["bands size"];
}
dataRow1["size"] = size;
}
if (dic.ContainsKey("shoe us size"))
{
string shoetype;
//US码转Eur码
switch (dic["deptid"])
{
case "1012": shoetype = "men"; break; //000
case "1017": shoetype = "men"; break; //500
case "1016": shoetype = "women"; break; //600
case "1013": shoetype = "women"; break; //900
default: shoetype = ""; break; //不在范围内
}
string type = dataRow1["item_bh"].ToString().Substring(0, 2);
if (type == "XW")
{
shoetype = "women";
}
else if (type == "XM")
{
shoetype = "men";
}
if (shoetype != "")
{
string eurSize = ConvertShoeSize(shoetype, dic["shoe us size"]);
if (eurSize != "")
{
dataRow1["size"] = eurSize;
}
else
{
dataRow1["size"] = dic["shoe us size"];
}
}
else
{
dataRow1["size"] = dic["shoe us size"];
}
dataRow1["mem"] = "shoe us size：" + dic["shoe us size"];
}
if (dic["unit"].Trim().ToLower().Contains("lot")) //单位是lot的订单也为异常
{
dataRow1["isyc"] = 1;
dataRow1["mem"] = dic["lotNum"].ToString() + "piece" + "/ Lot ; " + dic["amount"].ToString() + " Lot";
dataRow1["amount"] = (Int32.Parse(dic["lotNum"]) * Int32.Parse(dic["amount"])).ToString();
dataRow1["unit"] = "piece";
}
else
{
dataRow1["amount"] = dic["amount"];
dataRow1["unit"] = dic["unit"];
}
dataRow1["item_sum"] = dic["item_sum"];
if (dic["memo"].Trim() != "")
{
dataRow1["isyc"] = 1;
}
dataRow1["ordermem"] = dic["memo"];
dataRow1["import_dt"] = DateTime.Now;
dataRow1["shaddr"] = dic["shaddr"];
dataRow1["ln_no"] = dic["ln_no"];
dataRow1["deptid"] = dic["deptid"];
dataRow1["operator"] = "自动导入";
dataRow1["status"] = "0";
dataSet.Tables["orders"].Rows.Add(dataRow1);
sqlDataAdapter.Update(dataSet, "orders");
sqlConnection.Close();
count++;
}
else
{
}
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
string user;
user = Session["vuname"].ToString();
if (DropDownList1.SelectedIndex == 1)
{
da = new SqlDataAdapter("select * from products where producttype='" + DropDownList1.SelectedItem.Text + "' and vendorname='" + user + "'", cn);
ds = new DataSet();
da.Fill(ds, "products");
dt = ds.Tables["products"];
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
cmb = new SqlCommandBuilder(da);
DropDownList2.Items.Clear();
for (i = 0; i < dt.Rows.Count; i++)
{
DropDownList2.Items.Add(dt.Rows[i][10].ToString());
}
DropDownList2.DataBind();
}
if (DropDownList1.SelectedIndex == 2)
{
da = new SqlDataAdapter("select * from products where producttype='" + DropDownList1.SelectedItem.Text + "' and vendorname='" + user + "'", cn);
ds = new DataSet();
da.Fill(ds, "products");
dt = ds.Tables["products"];
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
cmb = new SqlCommandBuilder(da);
DropDownList2.Items.Clear();
DropDownList2.Items.Add("Select");
for (i = 0; i < dt.Rows.Count; i++)
{
DropDownList2.Items.Add(dt.Rows[i][10].ToString());
}
DropDownList2.DataBind();
}
if (DropDownList1.SelectedIndex == 3)
{
da = new SqlDataAdapter("select * from products where producttype='" + DropDownList1.SelectedItem.Text + "' and vendorname='" + user + "'", cn);
ds = new DataSet();
da.Fill(ds, "products");
dt = ds.Tables["products"];
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
cmb = new SqlCommandBuilder(da);
DropDownList2.Items.Clear();
for (i = 0; i < dt.Rows.Count; i++)
{
DropDownList2.Items.Add(dt.Rows[i][10].ToString());
}
DropDownList2.DataBind();
}
if (DropDownList1.SelectedIndex == 4)
{
da = new SqlDataAdapter("select * from products where producttype='" + DropDownList1.SelectedItem.Text + "' and vendorname='" + user + "'", cn);
ds = new DataSet();
da.Fill(ds, "products");
dt = ds.Tables["products"];
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
cmb = new SqlCommandBuilder(da);
DropDownList2.Items.Clear();
for (i = 0; i < dt.Rows.Count; i++)
{
DropDownList2.Items.Add(dt.Rows[i][10].ToString());
}
DropDownList2.DataBind();
}
if (DropDownList1.SelectedIndex == 5)
{
da = new SqlDataAdapter("select * from products where producttype='" + DropDownList1.SelectedItem.Text + "' and vendorname='" + user + "'", cn);
ds = new DataSet();
da.Fill(ds, "products");
dt = ds.Tables["products"];
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
cmb = new SqlCommandBuilder(da);
DropDownList2.Items.Clear();
for (i = 0; i < dt.Rows.Count; i++)
{
DropDownList2.Items.Add(dt.Rows[i][10].ToString());
}
DropDownList2.DataBind();
}
if (DropDownList1.SelectedIndex == 6)
{
da = new SqlDataAdapter("select * from products where producttype='" + DropDownList1.SelectedItem.Text + "' and vendorname='" + user + "'", cn);
ds = new DataSet();
da.Fill(ds, "products");
dt = ds.Tables["products"];
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
cmb = new SqlCommandBuilder(da);
DropDownList2.Items.Clear();
for (i = 0; i < dt.Rows.Count; i++)
{
DropDownList2.Items.Add(dt.Rows[i][10].ToString());
}
DropDownList2.DataBind();
}
if (DropDownList1.SelectedIndex == 7)
{
da = new SqlDataAdapter("select * from products where producttype='" + DropDownList1.SelectedItem.Text + "' and vendorname='" + user + "'", cn);
ds = new DataSet();
da.Fill(ds, "products");
dt = ds.Tables["products"];
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
cmb = new SqlCommandBuilder(da);
DropDownList2.Items.Clear();
for (i = 0; i < dt.Rows.Count; i++)
{
DropDownList2.Items.Add(dt.Rows[i][10].ToString());
}
DropDownList2.DataBind();
}
if (DropDownList1.SelectedIndex == 8)
{
da = new SqlDataAdapter("select * from products where producttype='" + DropDownList1.SelectedItem.Text + "' and vendorname='" + user + "'", cn);
ds = new DataSet();
da.Fill(ds, "products");
dt = ds.Tables["products"];
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
cmb = new SqlCommandBuilder(da);
DropDownList2.Items.Clear();
for (i = 0; i < dt.Rows.Count; i++)
{
DropDownList2.Items.Add(dt.Rows[i][10].ToString());
}
DropDownList2.DataBind();
}
}
public void Search()
{
ds.Clear();
try
{
//20210902密
Class1 TKID = new Class1();//用new 建立類別實體
SqlConnectionStringBuilder sqlsb = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString);
//資料庫使用者密碼解密
sqlsb.Password = TKID.Decryption(sqlsb.Password);
sqlsb.UserID = TKID.Decryption(sqlsb.UserID);
String connectionString;
sqlConn = new SqlConnection(sqlsb.ConnectionString);
sbSql.Clear();
sbSqlQuery.Clear();
StringBuilder query = new StringBuilder();
if (comboBox1.Text.Equals("未完成"))
{
query.AppendFormat(@" AND (ISNULL([HREngFrm001OutTime] ,'')='' OR ISNULL([HREngFrm001BakTime] ,'')='')");
}
else if (comboBox1.Text.Equals("全部"))
{
query.AppendFormat(@" ");
}
sbSql.AppendFormat(@" SELECT [HREngFrm001SN] AS '表單編號 ',[HREngFrm001OutDate] AS '預計日期',[HREngFrm001User] AS '申請人',[HREngFrm001UsrDpt] AS '部門',[HREngFrm001Rank] AS '職級',[HREngFrm001Agent] AS '代理人',[HREngFrm001Transp] AS '交通工具',[HREngFrm001Location] AS '外出地點' ,[HREngFrm001Cause] AS '外出原因',[HREngFrm001DefOutTime] AS '預計外出時間',[HREngFrm001OutTime] AS '實際外出時間',[HREngFrm001DefBakTime] AS '預計返廠時間',[HREngFrm001BakTime] AS '實際返廠時間' ");
sbSql.AppendFormat(@" ,[TaskId]");
sbSql.AppendFormat(@" FROM [TKGAFFAIRS].[dbo].[HREngFrm001]");
sbSql.AppendFormat(@" WHERE [HREngFrm001OutDate]>='{0}' AND [HREngFrm001OutDate]<='{1}'", dateTimePicker1.Value.ToString("yyyy/MM/dd"), dateTimePicker2.Value.ToString("yyyy/MM/dd"));
sbSql.AppendFormat(@" {0}", query.ToString());
sbSql.AppendFormat(@" ");
sbSql.AppendFormat(@" ");
adapter = new SqlDataAdapter(@"" + sbSql, sqlConn);
sqlCmdBuilder = new SqlCommandBuilder(adapter);
sqlConn.Open();
ds.Clear();
adapter.Fill(ds, "TEMPds1");
sqlConn.Close();
if (ds.Tables["TEMPds1"].Rows.Count == 0)
{
dataGridView1.DataSource = null;
}
else
{
if (ds.Tables["TEMPds1"].Rows.Count >= 1)
{
dataGridView1.DataSource = ds.Tables["TEMPds1"];
dataGridView1.AutoResizeColumns();
}
}
}
catch
{
}
finally
{
}
}
private void LoadDetails(string ClientCode, string theName)
{
try
{
SqlConnection cnSQL = new SqlConnection(MyModules.strConnect);
SqlCommand cmSQL = new SqlCommand();
cmSQL.Connection = cnSQL;
cnSQL.Open();
cmSQL.Parameters.Clear();
cmSQL.CommandText = "FetchPaymentDetails";
if (RadSchool.Checked)
{
cmSQL.Parameters.AddWithValue("@Source", "SCHOOL");
}
if (RadStudent.Checked)
{
cmSQL.Parameters.AddWithValue("@Source", "STUDENT");
}
if (RadVendor.Checked)
{
cmSQL.Parameters.AddWithValue("@Source", "VENDOR");
}
if (radStaff.Checked)
{
cmSQL.Parameters.AddWithValue("@Source", "STAFF");
}
cmSQL.Parameters.AddWithValue("@RefNo", ClientCode);
cmSQL.Parameters.AddWithValue("@TheName", theName);
cmSQL.Parameters.AddWithValue("@StartDate", (dtpStartDate.Checked == false ? "01-Jan-1900" : dtpStartDate.Value.ToString()));
cmSQL.Parameters.AddWithValue("@Enddate", (dtpEndDate.Checked == false ? "31-Dec-" + (DateTime.Now.Year + 50).ToString() : dtpEndDate.Value.ToString()));
cmSQL.CommandType = CommandType.StoredProcedure;
dataAdapter = new SqlDataAdapter(cmSQL);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(table);
bindingSource.DataSource = table;
//DGrid.Columns(5).Width = 250
DataGridViewCellStyle myStyle = new DataGridViewCellStyle();
DataGridViewCellStyle myStyle1 = new DataGridViewCellStyle();
myStyle.ForeColor = Color.Red;
myStyle.Format = "N2";
myStyle1.Format = "N2";
myStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
myStyle1.ForeColor = Color.Black;
myStyle1.Alignment = DataGridViewContentAlignment.MiddleRight;
DGrid.Columns[2].DefaultCellStyle = myStyle1;
//DGrid.Columns(3).DefaultCellStyle = myStyle1
cmSQL.Dispose();
cnSQL.Close();
return;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
protected void Button10_Click(object sender, EventArgs e)
{
//Create Connection
string connStr = ConfigurationManager.ConnectionStrings["DBS"].ConnectionString;
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
DataTable dt1 = new DataTable();
SqlDataAdapter da1 = new SqlDataAdapter("Select Id from Information where Id='" + TextBoxId.Text + "'", conn);
da1.Fill(dt1);
if (dt1.Rows.Count > 0)
{
Response.Write("<script>alert('Id already exist, try with another one')</script>");
}
else
{
DataTable dt2 = new DataTable();
SqlDataAdapter da2 = new SqlDataAdapter("Select Email from Information where Email='" + TextBoxEmail.Text + "'", conn);
da2.Fill(dt2);
if (dt2.Rows.Count > 0)
{
Response.Write("<script>alert('Email is used, try with another one!')</script>");
}
else
{
DataTable dt3 = new DataTable();
SqlDataAdapter da3 = new SqlDataAdapter("Select Phone from Information where Phone='" + TextBoxPhone.Text + "'", conn);
da3.Fill(dt3);
if (dt3.Rows.Count > 0)
{
Response.Write("<script>alert('Phone is used, try with another one!')</script>");
}
else
{
// Insert into Information Table
string sqlInformation = "SELECT * FROM Information";
SqlDataAdapter adapterInformation = new SqlDataAdapter(sqlInformation, conn);
SqlCommandBuilder builderInformation = new SqlCommandBuilder(adapterInformation);
DataSet dsInformation = new DataSet();
adapterInformation.Fill(dsInformation, "Information");
DataRow drInformation = dsInformation.Tables["Information"].NewRow();
drInformation["Id"] = TextBoxId.Text;
drInformation["Name"] = TextBoxName.Text;
drInformation["Address"] = TextBoxAddress.Text;
drInformation["Email"] = TextBoxEmail.Text;
drInformation["Phone"] = TextBoxPhone.Text;
drInformation["Gender"] = DropDownListGender.SelectedValue.ToString();
drInformation["DOB"] = calendarControl.SelectedDate.ToString();
drInformation["BloodGroup"] = DropDownListBloodGroup.SelectedValue.ToString();
drInformation["Nationality"] = TextBoxNationality.Text;
drInformation["Religion"] = TextBoxReligion.Text;
drInformation["MaritalStatus"] = DropDownListMaritalStatus.SelectedValue.ToString();
dsInformation.Tables["Information"].Rows.Add(drInformation);
adapterInformation.Update(dsInformation.Tables["Information"]);
// Insert Into Admin Table
string sqlAdmin = "SELECT * FROM Admin";
SqlDataAdapter adapterAdmin = new SqlDataAdapter(sqlAdmin, conn);
SqlCommandBuilder builderAdmin = new SqlCommandBuilder(adapterAdmin);
DataSet dsAdmin = new DataSet();
adapterAdmin.Fill(dsAdmin, "Admin");
DataRow drAdmin = dsAdmin.Tables["Admin"].NewRow();
drAdmin["Id"] = TextBoxId.Text;
drAdmin["JoiningDate"] = calendarControl1.SelectedDate.ToString();
dsAdmin.Tables["Admin"].Rows.Add(drAdmin);
adapterAdmin.Update(dsAdmin.Tables["Admin"]);
//Insert into User Table
string sqlUsers = "SELECT * FROM Users";
SqlDataAdapter adapterUsers = new SqlDataAdapter(sqlUsers, conn);
SqlCommandBuilder builderUsers = new SqlCommandBuilder(adapterUsers);
DataSet dsUsers = new DataSet();
adapterUsers.Fill(dsUsers, "Users");
DataRow drUsers = dsUsers.Tables["Users"].NewRow();
drUsers["Id"] = TextBoxId.Text;
drUsers["Password"] = TextBoxConfirmPassword.Text;
dsUsers.Tables["Users"].Rows.Add(drUsers);
adapterUsers.Update(dsUsers.Tables["Users"]);
ScriptManager.RegisterStartupScript(this, this.GetType(), "redirect",
"alert('Added Successfully!'); window.location='" +
Request.ApplicationPath + "adminAddAdmin.aspx';", true);
//Response.Write("<script>alert('Added Successfully!')</script>");
//Response.Redirect("~/admin.aspx");
}
}
}
}
private void LoadClientBalances()
{
try
{
SqlConnection cnSQL = new SqlConnection(MyModules.strConnect);
SqlCommand cmSQL = new SqlCommand();
cmSQL.Connection = cnSQL;
cnSQL.Open();
string strQry1 = "";
cmSQL.Parameters.Clear();
if (RadSchool.Checked == true)
{
cmSQL.CommandText = "SELECT RefNo, Name, 0 AS CR, SUM(Amount) AS DR,0-SUM(Amount) AS Balance FROM Ledger WHERE Source='SCHOOL' GROUP BY RefNo, Name";
}
else if (RadStudent.Checked == true)
{
if (cGrp.Text != "ALL")
{
strQry1 = strQry1 + (string.IsNullOrEmpty(strQry1) ? " " : " AND ") + " SchoolID =" + MyModules.GetIt4Me(cGrp.Text, " - ");
}
cmSQL.CommandText = "SELECT RefNo, Name, 0 AS CR, SUM(Amount) AS DR,0-SUM(Amount) AS Balance FROM Ledger WHERE Source='STUDENT' " + (string.IsNullOrEmpty(strQry1) ? "" : " AND " + strQry1) + " GROUP BY RefNo, Name";
}
else if (RadVendor.Checked == true)
{
cmSQL.CommandText = "SELECT RefNo, Name, 0 AS CR, SUM(Amount) AS DR,0-SUM(Amount) AS Balance FROM Ledger WHERE Source='VENDOR' " + (string.IsNullOrEmpty(strQry1) ? "" : " AND " + strQry1) + " GROUP BY RefNo, Name";
}
else
{
if (cGrp.Text != "ALL")
{
strQry1 = strQry1 + (string.IsNullOrEmpty(strQry1) ? " " : " AND ") + " RegisterVendor.Category ='" + cGrp.Text + "'";
}
cmSQL.CommandText = "SELECT Ledger.RefNo, Ledger.Name, 0 AS CR, SUM(Amount) AS DR,0-SUM(Amount) AS Balance FROM Ledger LEFT OUTER JOIN RegisterVendor ON Ledger.RefNo = RegisterVendor.Sn WHERE Ledger.Source='STAFF' " + (string.IsNullOrEmpty(strQry1) ? "" : " AND " + strQry1) + " GROUP BY Ledger.RefNo, Ledger.Name";
}
cmSQL.CommandType = CommandType.Text;
dataAdapterA = new SqlDataAdapter(cmSQL);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapterA);
DataTable tableA = new DataTable();
tableA.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapterA.Fill(tableA);
bindingSourceA.DataSource = tableA;
DataGridViewCellStyle myStyle = new DataGridViewCellStyle();
DataGridViewCellStyle myStyle1 = new DataGridViewCellStyle();
myStyle.ForeColor = Color.Red;
myStyle.Format = "N2";
myStyle1.Format = "N2";
myStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
myStyle1.ForeColor = Color.Black;
myStyle1.Alignment = DataGridViewContentAlignment.MiddleRight;
cnSQL.Close();
LoadDetails("-899", "sfsfsfs@@");
return;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Loads all the spells.
/// </summary>
/// <returns></returns>
public static bool LoadSpells()
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine("\tLoading Spells...");
using (var spellinfo = new SqlHandler(Program.Config.ReadString("GameConnectionString")))
{
using (var cmd = new SqlCommandBuilder(spellinfo, SqlCommandType.SELECT, false))
{
cmd.Finish("DB_SpellInfo");
}
while (spellinfo.Read())
{
Data.Spell spell = new ProjectX_V3_Game.Data.Spell();
spell.ID = spellinfo.ReadUInt16("SpellID");
if (spell.ID == 0)
{
return(false);
}
spell.SpellID = spellinfo.ReadUInt16("Type");
if (spell.SpellID == 0)
{
return(false);
}
spell.Sort = spellinfo.ReadByte("Sort");
spell.Crime = spellinfo.ReadBoolean("Crime");
spell.Ground = spellinfo.ReadBoolean("Ground");
spell.Multi = spellinfo.ReadBoolean("Multi");
spell.Target = spellinfo.ReadByte("Target");
spell.Level = spellinfo.ReadByte("SpellLevel");
spell.UseMP = spellinfo.ReadUInt16("UseMP");
spell.Power = spellinfo.ReadUInt16("Power");
if (spell.Power == 0)
{
spell.PowerPercentage = 1;
}
else
{
spell.PowerPercentage = (float)(spell.Power % 1000) / 100;
}
spell.IntoneSpeed = spellinfo.ReadUInt16("IntoneSpeed");
spell.Percentage = spellinfo.ReadByte("SpellPercent");
spell.Duration = spellinfo.ReadByte("StepSecs");
spell.Range = spellinfo.ReadUInt16("Range");
spell.Sector = spell.Range * 20;
spell.Distance = spellinfo.ReadUInt16("Distance");
if (spell.Distance >= 4)
{
spell.Distance--;
}
if (spell.Distance > 17)
{
spell.Distance = 17;
}
spell.Status = spellinfo.ReadUInt64("Status");
spell.NeedExp = spellinfo.ReadUInt32("NeedExp");
spell.NeedLevel = spellinfo.ReadByte("NeedLevel");
spell.UseXP = spellinfo.ReadByte("UseXP");
spell.WeaponSubtype = spellinfo.ReadUInt16("WeaponSubtype");
spell.UseEP = spellinfo.ReadByte("UseEP");
spell.NextMagic = spellinfo.ReadUInt16("NextMagic");
spell.UseItem = spellinfo.ReadByte("UseItem");
spell.UseItemNum = spellinfo.ReadByte("UseItemNum");
if (Core.Kernel.SpellInfos.ContainsKey(spell.SpellID))
{
Core.Kernel.SpellInfos[spell.SpellID].TryAdd(spell.Level, spell);
}
else
{
if (!Core.Kernel.SpellInfos.TryAdd(spell.SpellID))
{
return(false);
}
if (!Core.Kernel.SpellInfos[spell.SpellID].TryAdd(spell.Level, spell))
{
return(false);
}
}
switch (spell.SpellID)
{
case 5010:
case 7020:
case 1290:
case 1260:
case 5030:
case 5040:
case 7000:
case 7010:
case 7030:
case 7040:
case 1250:
case 5050:
case 5020:
case 10490:
case 1300:
if (spell.Distance >= 3)
{
spell.Distance = 3;
}
if (spell.Range > 3)
{
spell.Range = 3;
}
if (!Core.Kernel.WeaponSpells.ContainsKey(spell.WeaponSubtype))
{
if (!Core.Kernel.WeaponSpells.TryAdd(spell.WeaponSubtype, spell.SpellID))
{
return(false);
}
}
break;
}
}
}
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("\tLoaded {0} Spells...", Core.Kernel.SpellInfos.Count);
return(true);
}
public async Task <ProcedureInfo> ReadSpInfoAsync(string spFullName, IContext context, IDictionary <string, object> session = null)
{
using (var connection = new SqlConnection(m_connectionString))
using (var command = new SqlCommand(spFullName, connection))
{
command.CommandType = CommandType.StoredProcedure;
await connection.OpenAsync();
SqlCommandBuilder.DeriveParameters(command);
var sp = new ProcedureInfo(spFullName);
foreach (SqlParameter parameter in command.Parameters)
{
var dataType = m_sqlDbTypeInfo.GetDataType(parameter.SqlDbType);
if (dataType == typeof(string))
{
parameter.Value = "a";
}
var parameterInfo = new SqlParameterInfo(parameter, dataType);
sp.AddParameter(parameterInfo);
if (false != string.IsNullOrWhiteSpace(parameter.TypeName))
{
continue;
}
string tableTypeName = new DbObjectInfo(parameter.TypeName).FullName;
parameter.TypeName = tableTypeName;
var tableTypeSchema = GetFromSession <DataTable>(tableTypeName, session);
if (!ReferenceEquals(tableTypeSchema, null))
{
parameter.Value = tableTypeSchema.Clone();
parameterInfo.TableTypeSchema = tableTypeSchema;
continue;
}
using (var loadTableValueParameterSchemaCmd = new SqlCommand())
{
loadTableValueParameterSchemaCmd.Connection = connection;
loadTableValueParameterSchemaCmd.CommandText =
$@"
DECLARE @t AS {tableTypeName}
SELECT * FROM @t
";
using (var reader = await loadTableValueParameterSchemaCmd.ExecuteReaderAsync(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
{
tableTypeSchema = new DataTable(tableTypeName);
tableTypeSchema.Load(reader);
AddToSession(tableTypeName, tableTypeSchema, session);
parameter.Value = tableTypeSchema.Clone();
parameterInfo.TableTypeSchema = tableTypeSchema;
}
}
}
if (context?.IgnoreQueryResult(spFullName) == true)
{
return(sp);
}
try
{
DataSet schema = new DataSet();
var adapter = new SqlDataAdapter(command);
adapter.FillSchema(schema, SchemaType.Source);
for (int i = 0; i < schema.Tables.Count; ++i)
{
schema.Tables[i].TableName = $"{spFullName} > result {i}";
}
sp.AddResultSchemas(schema.Tables.OfType <DataTable>());
}
catch (SqlException ex)
{
var message = new StringBuilder($"Could not retrieve the {spFullName} query result information.");
if (ex.Message.Contains("Invalid object name '#"))
{
var procedureObj = new Mapping.Procedure();
var flagName = nameof(procedureObj.IgnoreQueryResult);
message.Append($" If typed query result is required, avoid using temporary table(s) in the {spFullName} procedure code.");
message.Append($" Otherwise, to suppress this message, mark the procedure with the {flagName} attribute in the mapping.");
m_diagnosticsCallback.Warning(message.ToString());
}
else
{
message.Append($" Error: {ex.Message}");
m_diagnosticsCallback.Error(message.ToString());
}
}
return(sp);
}
}
