public StreamingDataReaderTokenHandler(TaskCompletionSource <DbDataReader> readerSource, AseDataReader dataReader, IInfoMessageEventNotifier eventNotifier)
{
_readerSource = readerSource;
_dataReader = dataReader;
_eventNotifier = eventNotifier;
_hasFirstResultSet = false;
}
protected List <KeyValueCollection> GetEntityPage(AseDataReader reader, int offset, int limit)
{
List <KeyValueCollection> entitys = new List <KeyValueCollection>();
// 读取列
List <string> columns = new List <string>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
}
// 反射创建实体类
int currentIndex = 0;
while (reader.Read())
{
if (offset > currentIndex++)
{
continue;
}
if (offset + limit == currentIndex)
{
break;
}
KeyValueCollection entity = new KeyValueCollection();
foreach (var col in columns)
{
entity[col] = reader[col];
}
entitys.Add(entity);
}
return(entitys);
}
public DataReaderCallbackTokenHandler(CommandBehavior behavior, IResultsMessageEventNotifier eventNotifier)
{
_eventNotifier = eventNotifier;
_reader = new AseDataReader(_results, behavior);
// Set up dummy result set in Reader so that processing in Handle is simpler
_results.Add(new TableResult());
_results[0].Rows.Add(null);
_reader.Read();
}
/// <summary>
/// 反射实体类信息
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader"></param>
/// <returns></returns>
protected List <T> GetEntityCollection <T>(AseDataReader reader) where T : class, new()
{
PropertyInfo[] properties = typeof(T).GetProperties();
if (properties == null || properties.Length <= 0)
{
throw new ArgumentNullException("实体类{0}不包含任何属性信息！".ToFormat(typeof(T).Name));
}
// 读取列
Dictionary <string, string> columns = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
for (int i = 0; i < reader.FieldCount; i++)
{
string cname = reader.GetName(i);
columns[cname] = cname;
}
// 反射创建实体类
List <T> entitys = new List <T>();
while (reader.Read())
{
T entity = typeof(T).Create <T>();
foreach (PropertyInfo property in properties)
{
string p_name = property.Name;
object[] attributes = property.GetCustomAttributes(typeof(Field), true);
object[] proc_attributes = property.GetCustomAttributes(typeof(Proc), true);
// 存在自定义Field实例处理方法
if (attributes != null && attributes.Length > 0)
{
Field dbfield = attributes[0] as Field;
// 检测自增变量信息
p_name = dbfield.Name.IsNullOrEmpty() ? p_name : dbfield.Name;
}
else if (proc_attributes != null && proc_attributes.Length > 0)
{
Proc dbfield = proc_attributes[0] as Proc;
// 检测自增变量信息
p_name = dbfield.Name.IsNullOrEmpty() ? p_name : dbfield.Name;
}
// 不存在时处理方法
else
{
}
// 读取数据，并赋值
if (columns.ContainsKey(p_name))
{
property.SetValue(entity, reader[columns[p_name]].ChangeTo(property.PropertyType), null);
}
}
entitys.Add(entity);
}
return(entitys);
}
public Account getAccountStatus(string AcctNo)
{
// BVN = "2014225";
Account account = new Account();
//string result = "";
LogWriter logWriter = new LogWriter();
AseCommand cmd = null;
AseConnection conn = null;
string sqlquery = null;
string cnstring = ConfigurationManager.ConnectionStrings["phoenixConnectionString"].ConnectionString;
List <Account> accountDetailList = new List <Account>();
try
{
conn = new AseConnection(cnstring);
sqlquery = " SELECT status as status from phoenix..dp_acct WHERE acct_no = '" + AcctNo + "'";
cmd = new AseCommand(sqlquery, conn);
cmd.CommandTimeout = 0;
conn.Open();
AseDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (reader == null)
{
logWriter.WriteErrorLog(string.Format("Reader empty !", "reader is empty"));
account.AccountStatus = "";
}
while (reader.Read())
{
account.AccountStatus = reader["status"].ToString();
}
}
catch (Exception ex)
{
logWriter.WriteErrorLog(string.Format("Exception Message! / {0}", ex.Message));
}
finally
{
//cmd.Dispose();
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
return(account);
}
public IHttpActionResult GetAccountDetails(string accountNumber)
{
List <AccountDetailsModel> accountDetails = new List <AccountDetailsModel>();
string connectionString = ConfigurationManager.ConnectionStrings["sybaseconnection"].ToString();
string query = "select c.title_1 as acct_name, " +
"b.gsm_no as Mobile_No, " +
"c.class_code as class_code, " +
"a.home_address as home_address " +
"from zenbase..zib_ecustomer b ," +
"zenbase..zib_kyc_corporate_signatories a, " +
"phoenix..dp_acct c " +
"where a.acct_no = b.acct_no " +
"and a.acct_no = c.acct_no " +
"and a.acct_no = '" +
accountNumber + "'";
try
{
logwriter.WriteTolog("In try");
using (AseConnection connection = new AseConnection(connectionString))
{
AseCommand command = new AseCommand(query, connection);
connection.Open();
AseDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var accountDt = new AccountDetailsModel()
{
mobile_no = reader["Mobile_No"].ToString().Trim(),
home_address = reader["home_address"].ToString().Trim(),
class_code = reader["class_code"].ToString().Trim(),
acct_name = reader["acct_name"].ToString().Trim()
};
accountDetails.Add(accountDt);
}
reader.Close();
}
}
catch (Exception ex)
{
logwriter.WriteTolog("Error in inner exception: " + ex.InnerException);
logwriter.WriteTolog("Error in message: " + ex.Message);
}
return(Ok(accountDetails));
}
public Tables Get(Database database)
{
Tables tables = new Tables(database);
double tableCount = GetTablesCount();
double tableIndex = 0;
int stats = 0;
using (AseConnection conn = new AseConnection(connectioString))
{
using (AseCommand command = new AseCommand("select O.sysstat2, O.id, O.name, U.name as Owner FROM sysobjects O INNER JOIN sysusers U ON U.uid = O.uid where type = 'U' ORDER BY O.name", conn))
{
conn.Open();
using (AseDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Table table = new Table(database);
table.Id = (int)reader["id"];
table.Name = reader["name"].ToString();
table.Owner = reader["Owner"].ToString();
stats = (int)reader["sysstat2"];
if ((stats & 8192) == 8192)
{
table.LockType = Table.LockTypeEnum.LockAllPages;
}
if ((stats & 16384) == 16384)
{
table.LockType = Table.LockTypeEnum.LockDataPages;
}
if ((stats & 32768) == 32768)
{
table.LockType = Table.LockTypeEnum.LockDataRows;
}
//table.Constraints = (new GenerateConstraint(connectioString,tableFilter)).Get(table); /*Primero debe ir las constraints*/
//table.Columns = GetColumns(table);
//table.Indexes = (new GenerateIndex(connectioString,tableFilter)).Get(table);
tables.Add(table);
tableIndex++;
OnTableProgress(this, new ProgressEventArgs((tableIndex / tableCount) * 100));
}
}
}
}
if (objectFilter.OptionFilter.FilterTrigger)
{
SetTriggers(tables);
}
SetColumnsDatabase(tables);
//tables.Sort();
tables.ToSQL();
return(tables);
}
/// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/// <summary> GET: Student/Delete/5 </summary>
/// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
public ClaimsSubmission GetAllClaims(ClaimsSubmission claimSub, string tableName)
{
ClaimDataAccessLayer.TableName = tableName;
using (var conn = new AseConnection(HomeController.SybaseConnString))
{
int rowsRead = 0;
try
{
conn.Open();
AseCommand cmd = new AseCommand("SP_XC_CLAIM_REJECTION_READ", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new AseParameter("@TABLE_NAME", TableName));
rowsRead = cmd.ExecuteNonQuery();
if (rowsRead > 0)
{
AseDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string claimid = reader["CLCL_ID"].ToString();
string createdByUser = reader["CREATED_BY_USER"].ToString();
string createdByService = reader["CREATED_BY_SERVICE"].ToString();
string createdDate = reader["CREATED_DATE"].ToString();
string prprNpi = reader["PRPR_NPI"].ToString();
Claim newClaim = new Claim()
{
ClaimId = claimid,
CreatedByUser = createdByUser,
CreatedByService = createdByService,
CreatedDate = createdDate,
PrprNpi = prprNpi
};
claimSub.ClaimList.Add(newClaim);
}
}
}
catch (Exception e)
{
System.Console.WriteLine("**ERROR READING CLAIMS** : {0}", e.ToString());
}
return(claimSub);
}
}
/// <summary>
/// 读取KEY VALUE值
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public override KeyValueCollection ExecuteKVCollection(string sql, KdtParameterCollection parameters)
{
try
{
KeyValueCollection entity = new KeyValueCollection();
// 执行SQL命令
using (AseCommand cmd = new AseCommand(ReplaceSqlText(sql, parameters), _sybaseCn))
{
InitCommand(cmd); // 初始化
// 赋值参数
var hasConvertParams = ConvertToSqlParameter(parameters);
foreach (var item in hasConvertParams)
{
cmd.Parameters.Add(item.Value);
}
// 执行填充数据
using (AseDataReader reader = cmd.ExecuteReader())
{
entity = GetEntity(reader);
// 反射参数值
ReflectParamValue(parameters, hasConvertParams);
reader.Close();
reader.Dispose();
}
cmd.Cancel();
cmd.Dispose();
}
return(entity);
}
catch (AseException me)
{
KdtLoger.Instance.Error(me);
throw new DataException(me.Message);
}
catch (Exception ex)
{
KdtLoger.Instance.Error(ex);
throw new DataException(string.Format("执行SQL查询,返回数据集合错误,原因为:{0}", ex.Message));
}
}
public List <Branch> GetBranchList()
{
List <Branch> brList = new List <Branch>();
AseCommand cmd = null;
AseConnection conn = null;
string cnstring = ConfigurationManager.ConnectionStrings["phoenixConnectionString"].ConnectionString;
try
{
conn = new AseConnection(cnstring);
string sqlquery = "select branch_no,name_1 from phoenix..ad_gb_branch where status = 'Active' order by name_1";
cmd = new AseCommand(sqlquery, conn);
cmd.CommandTimeout = 0;
conn.Open();
AseDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
Branch br = new Branch();
br.BranchNo = Convert.ToInt16(reader["branch_no"].ToString());
br.BranchName = reader["name_1"].ToString();
brList.Add(br);
}
reader.Close();
}
catch (Exception ex)
{
throw;
}
finally
{
cmd.Dispose();
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
return(brList);
}
public void SetTriggers(Tables tables)
{
string text = "";
using (AseConnection conn = new AseConnection(connectioString))
{
conn.Open();
using (AseCommand command = new AseCommand(GetSQLTrigger(), conn))
{
using (AseDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
TableTrigger trigger = new TableTrigger(tables[reader["TableName"].ToString()]);
trigger.Id = (int)reader["id"];
trigger.Name = reader["name"].ToString();
trigger.Owner = reader["owner"].ToString();
tables[reader["TableName"].ToString()].Triggers.Add(trigger);
}
}
}
foreach (Table table in tables)
{
for (int index = 0; index < table.Triggers.Count; index++)
{
using (AseCommand command = new AseCommand("sp_helptext '" + table.Triggers[index].Name + "'", conn))
{
using (AseDataReader reader = command.ExecuteReader())
{
text = "";
reader.NextResult();
while (reader.Read())
{
text += reader["text"].ToString();
}
table.Triggers[index].Text = text;
}
}
}
}
}
}
/// <summary>
/// 反射实体类信息
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader"></param>
/// <returns></returns>
protected KeyValueCollection GetEntity(AseDataReader reader)
{
KeyValueCollection entity = new KeyValueCollection();
// 读取列
List <string> columns = new List <string>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
}
// 反射创建实体类
if (reader.Read())
{
foreach (var col in columns)
{
entity[col] = reader[col];
}
}
return(entity);
}
private void SetColumnsDatabase(Tables tables)
{
using (AseConnection conn = new AseConnection(connectioString))
{
using (AseCommand command = new AseCommand(GetSQLColumns(), conn))
{
conn.Open();
using (AseDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Table table = tables[reader["TableName"].ToString()];
Column col = new Column(table);
col.Id = (short)reader["colid"];
col.Identity = ((int)reader["IsIdentity"]) == 1;
col.Name = reader["Name"].ToString();
col.Nullable = ((int)reader["IsNullable"]) == 1;
col.Precision = (byte)reader["Precision"];
col.Scale = (byte)reader["Scale"];
if (!String.IsNullOrEmpty(reader["Size"].ToString()))
{
col.Size = (int)reader["Size"];
}
col.Type = reader["Type"].ToString();
col.HasIndexDependencies = false;
if (objectFilter.OptionFilter.FilterColumnPosition)
{
col.Position = table.Columns.Count + 1;
}
else
{
col.Position = 0;
}
table.Columns.Add(col);
}
}
}
}
}
public async static Task <Tuple <int, string, string> > getAccountType(string account_number)
{
string acct_type = string.Empty;
string acct_name = string.Empty;
string query = " SELECT a.acct_type AS acct_type, " +
" a.title_1 AS acct_name " +
" FROM phoenix..dp_acct a, " +
" phoenix..rm_acct c " +
" WHERE a.acct_no = '" + account_number + "'" +
" AND a.rim_no = c.rim_no ";
using (AseConnection conn = new AseConnection(ConfigurationManager.ConnectionStrings["sybaseconnection"].ConnectionString))
{
using (AseCommand cmd = new AseCommand(query, conn))
{
try
{
conn.Open();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
cmd.CommandTimeout = 0;
AseDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
acct_name = reader["acct_name"].ToString();
acct_type = reader["acct_type"].ToString();
}
reader.Close();
return(await Task.FromResult(Tuple.Create(0, acct_name, acct_type)));
} catch (Exception ex) {
return(Tuple.Create(-1, string.Empty, ex.Message));
}
};
};
}
public static async Task <Tuple <int, string, string, string> > getStaffPhoenixDetails(int employee_id)
{
string staffStatus = string.Empty;
string employee_number = string.Empty;
string fullname = string.Empty;
string connectionString = ConfigurationManager.ConnectionStrings["sybaseconnection"].ToString();
string queryString = " SELECT a.staff_id AS employee_number, r.status AS status, a.fullname AS fullname " +
" FROM phoenix..ad_gb_rsm r, " +
" ZENBASE..zib_applications_users a " +
" WHERE r.user_name = a.user_id " +
" AND r.employee_id = " + employee_id;
try
{
using (AseConnection connection = new AseConnection(connectionString))
{
using (AseCommand command = new AseCommand(queryString, connection))
{
command.CommandTimeout = 0;
connection.Open();
AseDataReader reader = command.ExecuteReader();
while (reader.Read())
{
staffStatus = reader["status"].ToString().Trim();
employee_number = reader["employee_number"].ToString().Trim();
fullname = reader["fullname"].ToString().Trim();
}
reader.Close();
return(await Task.FromResult(Tuple.Create(0, staffStatus, employee_number, fullname)));
}
}
} catch (Exception ex)
{
return(Tuple.Create(-1, ex.Message, employee_number, fullname));
}
}
static int Main(string[] args)
{
int
Result = -1,
tmpInt;
decimal
tmpDecimal;
object
tmpObject;
StreamWriter
fstr_out = null;
string
tmpString,
OutputFileName = "log.log";
#if WITH_TRACE
string
TraceFileName = "trace.log";
#endif
AseConnection
conn = null;
AseCommand
cmd = null;
AseTransaction
Transaction = null;
AseDataReader
reader = null;
AseParameter
Parameter = null;
AseDataAdapter
da = null;
DataTable
tmpDataTable = null;
try
{
try
{
fstr_out = new StreamWriter(OutputFileName, false, System.Text.Encoding.GetEncoding(1251));
fstr_out.AutoFlush = true;
#if WITH_REFLECTION
Assembly[]
asms = AppDomain.CurrentDomain.GetAssemblies();
Assembly
asm = null;
foreach (Assembly a in asms)
{
if (a.FullName.ToLower().IndexOf("sybase.data.aseclient") > -1)
{
asm = a;
}
fstr_out.WriteLine("Assembly.CodeBase: " + a.CodeBase);
fstr_out.WriteLine("Assembly.EscapedCodeBase: " + a.EscapedCodeBase);
fstr_out.WriteLine("Assembly.FullName: " + a.FullName);
fstr_out.WriteLine("Assembly.GlobalAssemblyCache: " + a.GlobalAssemblyCache.ToString().ToLower());
fstr_out.WriteLine("Assembly.ImageRuntimeVersion: " + a.ImageRuntimeVersion);
fstr_out.WriteLine("Assembly.Location: " + a.Location);
fstr_out.WriteLine();
}
if (asm != null)
{
Type[]
alltypes = asm.GetTypes();
for (tmpInt = 0; tmpInt < alltypes.Length; ++tmpInt)
{
fstr_out.WriteLine("Обнаружено: " + alltypes[tmpInt].Name);
if (alltypes[tmpInt].Name.CompareTo("AseConnection") == 0)
{
Type
t = alltypes[tmpInt];
FieldInfo[]
fi = t.GetFields(BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
fstr_out.WriteLine("Анализ Field, определенных в " + t.Name);
foreach (FieldInfo f in fi)
{
fstr_out.Write(" " + f.Name);
fstr_out.WriteLine();
}
fstr_out.WriteLine();
PropertyInfo[]
//pi_=t.GetProperties(BindingFlags.Instance|BindingFlags.NonPublic|BindingFlags.Static); // AseConnection.Language
pi_ = t.GetProperties(BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
fstr_out.WriteLine("Анализ Property, определенных в " + t.Name);
foreach (PropertyInfo p_ in pi_)
{
fstr_out.Write(" " + p_.Name);
fstr_out.WriteLine();
}
fstr_out.WriteLine();
fstr_out.WriteLine("Поддерживаемые методы:");
ParameterInfo[]
pi;
MethodInfo[]
mi;
//mi=t.GetMethods();
mi = t.GetMethods(BindingFlags.DeclaredOnly
| BindingFlags.Instance
| BindingFlags.Public);
foreach (MethodInfo m in mi)
{
fstr_out.Write(" " + m.ReturnType.Name + " " + m.Name + "(");
pi = m.GetParameters();
for (int i = 0; i < pi.Length; ++i)
{
fstr_out.Write(pi[i].ParameterType.Name + " " + pi[i].Name);
if (i + 1 < pi.Length)
{
fstr_out.Write(", ");
}
}
fstr_out.WriteLine(")");
}
fstr_out.WriteLine();
}
}
fstr_out.WriteLine();
}
#endif
#if WITH_TRACE
_strmWriter = new StreamWriter(TraceFileName, false, System.Text.Encoding.GetEncoding(1251));
_strmWriter.AutoFlush = true;
#endif
if ((tmpString = ConfigurationSettings.AppSettings["connectionString"]) == null ||
tmpString == string.Empty)
{
fstr_out.WriteLine("ConfigurationSettings.AppSettings[\"connectionString\"] is empty!!!");
return(Result);
}
conn = new AseConnection(tmpString);
conn.InfoMessage += new AseInfoMessageEventHandler(conn_InfoMessage);
conn.StateChange += new System.Data.StateChangeEventHandler(conn_StateChange);
#if WITH_TRACE
conn.TraceEnter += new TraceEnterEventHandler(conn_TraceEnter);
conn.TraceExit += new TraceExitEventHandler(conn_TraceExit);
#endif
conn.Open();
fstr_out.WriteLine("AseConnection.ConnectionString: " + conn.ConnectionString);
fstr_out.WriteLine("AseConnection.ConnectionTimeout: " + conn.ConnectionTimeout);
fstr_out.WriteLine("AseConnection.Database: " + conn.Database);
fstr_out.WriteLine("AseConnection.NamedParameters: " + conn.NamedParameters.ToString().ToLower());
fstr_out.WriteLine("AseConnection.State: " + conn.State);
fstr_out.WriteLine("AseConnection.DriverVersion: " + AseConnection.DriverVersion);
//fstr_out.WriteLine("AseConnection.Language: "+AseConnection.Language);
fstr_out.WriteLine();
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select @@spid";
if ((tmpObject = cmd.ExecuteScalar()) != null)
{
tmpString = Convert.ToString(tmpObject);
}
#if TEST_CHARSET
cmd.CommandText = "select cast(val as date) from T4 where GroupId=4 and Id=1";
if (da == null)
{
da = new AseDataAdapter(cmd);
}
else
{
da.SelectCommand = cmd;
}
if (tmpDataTable == null)
{
tmpDataTable = new DataTable();
}
else
{
tmpDataTable.Reset();
}
da.Fill(tmpDataTable);
#endif
#if TEST_EXECUTE_NON_QUERY
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.NamedParameters = false;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
cmd.CommandText = "update Victim set Val = ? where Id = ?";
cmd.Parameters.Add("Val", AseDbType.Integer);
cmd.Parameters.Add("Id", AseDbType.Integer);
for (int Id = 1; Id <= 5; Id += 2)
{
cmd.Parameters["Val"].Value = Id;
cmd.Parameters["Id"].Value = Id;
tmpInt = cmd.ExecuteNonQuery();
}
cmd.NamedParameters = true;
#endif
#if TEST_BLOB
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.CommandType = CommandType.Text;
FileStream
fs;
byte[]
Blob;
#if TEST_BLOB_SAVE
tmpString = "@FImage";
cmd.CommandText = "update TestTypes set FImage = " + tmpString;
cmd.Parameters.Clear();
cmd.Parameters.Add(tmpString, AseDbType.Image);
fs = new FileStream("welcome.bmp", FileMode.Open, FileAccess.Read);
Blob = new byte[fs.Length];
fs.Read(Blob, 0, Blob.Length);
cmd.Parameters[tmpString].Value = Blob;
tmpInt = cmd.ExecuteNonQuery();
#endif
cmd.Parameters.Clear();
cmd.CommandText = "select * from TestTypes";
reader = cmd.ExecuteReader();
do
{
if (reader.HasRows)
{
for (int i = 0; i < reader.FieldCount; ++i)
{
fstr_out.WriteLine(reader.GetName(i) + " GetDataTypeName(): \"" + reader.GetDataTypeName(i) + "\" GetFieldType(): \"" + reader.GetFieldType(i) + "\"");
}
tmpInt = reader.GetOrdinal("FImage");
while (reader.Read())
{
tmpString = "FromBlob.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
Blob = (byte[])reader["FImage"];
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
tmpString = "FromBlob_1.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
Blob = new byte[reader.GetBytes(tmpInt, 0, null, 0, int.MaxValue)];
reader.GetBytes(tmpInt, 0, Blob, 0, Blob.Length);
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
}
}
}while(reader.NextResult());
reader.Close();
#endif
#if TEST_SMTH
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from TestDate order by FDate";
cmd.Parameters.Clear();
if (da == null)
{
da = new AseDataAdapter(cmd);
}
else
{
da.SelectCommand = cmd;
}
if (tmpDataTable != null)
{
tmpDataTable.Reset();
}
else
{
tmpDataTable = new DataTable();
}
da.Fill(tmpDataTable);
tmpString = "";
for (int i = 0; i < tmpDataTable.Rows.Count; ++i)
{
if (tmpString != string.Empty)
{
tmpString += " ";
}
tmpString += Convert.ToDateTime(tmpDataTable.Rows[i]["FDate"]).ToString("yyyy-MM-dd");
}
cmd.NamedParameters = false;
cmd.CommandText = "update TestTypes set FDatetime = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add("FDate", AseDbType.DateTime).Value = DateTime.Now;
tmpInt = cmd.ExecuteNonQuery();
cmd.NamedParameters = true;
cmd.CommandText = "update TestTypes set FDatetime = @FDatetime";
cmd.Parameters.Clear();
cmd.Parameters.Add("@FDatetime", AseDbType.DateTime).Value = DateTime.Now;
tmpInt = cmd.ExecuteNonQuery();
#endif
#if TEST_DATA_ADAPTER_FILL_SCHEMA
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from Staff";
if (da == null)
{
da = new AseDataAdapter(cmd);
}
else
{
da.SelectCommand = cmd;
}
if (tmpDataTable != null)
{
tmpDataTable.Reset();
}
else
{
tmpDataTable = new DataTable();
}
da.FillSchema(tmpDataTable, SchemaType.Source);
#endif
#if TEST_STORED_PROCEDURES
if (cmd == null)
{
cmd = conn.CreateCommand();
}
object[]
tmpObjects = new object[] { 5, 6 };
cmd.CommandType = CommandType.StoredProcedure;
cmd.NamedParameters = false;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "mathtutor";
for (int i = 0; i < tmpObjects.Length; ++i)
{
Parameter = new AseParameter();
Parameter.ParameterName = "Param" + i;
switch (Type.GetTypeCode(tmpObjects[i].GetType()))
{
case System.TypeCode.Int32:
{
Parameter.DbType = DbType.Int32;
break;
}
}
Parameter.Value = tmpObjects[i];
cmd.Parameters.Add(Parameter);
}
Parameter = new AseParameter();
Parameter.ParameterName = "Param3";
Parameter.DbType = DbType.Int32;
Parameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(Parameter);
cmd.ExecuteNonQuery();
tmpInt = !Convert.IsDBNull(cmd.Parameters["Param3"].Value) ? Convert.ToInt32(cmd.Parameters["Param3"].Value) : Int32.MinValue;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_TestTypes_Decimal_10_6";
AseCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["@FDecimal_10_6"].Value = 123.45m;
cmd.ExecuteNonQuery();
tmpDecimal = !Convert.IsDBNull(cmd.Parameters["@FDecimal_10_6_out"].Value) ? Convert.ToDecimal(cmd.Parameters["@FDecimal_10_6_out"].Value) : decimal.MinValue;
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "{? = call sp_TestTypes_Decimal_10_6(? ,?)}";
cmd.NamedParameters = false;
Parameter = new AseParameter("@RETURN_VALUE", AseDbType.Integer);
Parameter.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(Parameter);
Parameter = new AseParameter("@FDecimal_10_6", AseDbType.Decimal); //AseDbType.Numeric
//Parameter=new AseParameter();
//Parameter.ParameterName="@FDecimal_10_6";
//Parameter.DbType=DbType.Decimal;
//Parameter.AseDbType=AseDbType.Decimal; //AseDbType.Numeric
Parameter.Direction = ParameterDirection.Input;
//Parameter.Precision=10;
//Parameter.Scale=6;
Parameter.Value = 123.45m;
cmd.Parameters.Add(Parameter);
Parameter = new AseParameter("@FDecimal_10_6_out", AseDbType.Decimal); //AseDbType.Numeric
Parameter.Direction = ParameterDirection.Output;
Parameter.Precision = 10;
Parameter.Scale = 6;
cmd.Parameters.Add(Parameter);
cmd.ExecuteNonQuery();
tmpDecimal = !Convert.IsDBNull(cmd.Parameters["@FDecimal_10_6_out"].Value) ? Convert.ToDecimal(cmd.Parameters["@FDecimal_10_6_out"].Value) : decimal.MinValue;
tmpDecimal = 2;
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "{call UpdateTestTypes(?)}";
cmd.NamedParameters = false;
Parameter = new AseParameter("@rate", AseDbType.Decimal);
Parameter.Direction = ParameterDirection.Input;
Parameter.Value = tmpDecimal;
cmd.Parameters.Add(Parameter);
/*
* if(cmd==null)
* cmd=conn.CreateCommand();
*
* cmd.CommandType=CommandType.StoredProcedure;
* cmd.CommandText="UpdateTestTypes";
* AseCommandBuilder.DeriveParameters(cmd);
* cmd.Parameters["@Decimal_18_4"].Value=tmpDecimal;
*/
tmpInt = cmd.ExecuteNonQuery();
cmd.CommandType = CommandType.StoredProcedure;
for (int size = 255; size <= 258; ++size)
{
cmd.CommandText = "sp_ReturnAndOutputVarChar" + size;
AseCommandBuilder.DeriveParameters(cmd);
cmd.ExecuteNonQuery();
tmpString = !Convert.IsDBNull(cmd.Parameters["@OutParam"].Value) ? Convert.ToString(cmd.Parameters["@OutParam"].Value) : "NULL";
}
conn.ChangeDatabase("master");
cmd.CommandText = "testdb..sp_ReturnAndOutput";
AseCommandBuilder.DeriveParameters(cmd);
conn.ChangeDatabase("testdb");
#endif
Transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.Transaction = Transaction;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_Staff_Save";
//cmd.CommandText="sp_TestTypes";
fstr_out.WriteLine("AseCommandBuilder.DeriveParameters(\"" + cmd.CommandText + "\")");
AseCommandBuilder.DeriveParameters(cmd);
foreach (AseParameter parameter in cmd.Parameters)
{
fstr_out.WriteLine("\tParameterIndex: " + parameter.ParameterIndex + Environment.NewLine +
"\tParameterName: " + parameter.ParameterName + Environment.NewLine +
"\tDirection: " + parameter.Direction + Environment.NewLine +
"\tDbType: " + parameter.DbType + Environment.NewLine +
"\tAseDbType: " + parameter.AseDbType + Environment.NewLine +
"\tSize: " + parameter.Size + Environment.NewLine +
"\tPrecision: " + parameter.Precision + Environment.NewLine +
"\tScale: " + parameter.Scale + Environment.NewLine +
"\tIsNullable: " + parameter.IsNullable.ToString().ToLower() + Environment.NewLine +
"\tSourceColumn: " + parameter.SourceColumn + Environment.NewLine +
"\tSourceVersion: " + parameter.SourceVersion + Environment.NewLine);
}
fstr_out.WriteLine();
conn.ChangeDatabase("veksel");
cmd.CommandText = "sp_CONTRACT_SAVE";
fstr_out.WriteLine("AseCommandBuilder.DeriveParameters(\"" + cmd.CommandText + "\")");
AseCommandBuilder.DeriveParameters(cmd);
foreach (AseParameter parameter in cmd.Parameters)
{
fstr_out.WriteLine("\tParameterIndex: " + parameter.ParameterIndex + Environment.NewLine +
"\tParameterName: " + parameter.ParameterName + Environment.NewLine +
"\tDirection: " + parameter.Direction + Environment.NewLine +
"\tDbType: " + parameter.DbType + Environment.NewLine +
"\tAseDbType: " + parameter.AseDbType + Environment.NewLine +
"\tSize: " + parameter.Size + Environment.NewLine +
"\tPrecision: " + parameter.Precision + Environment.NewLine +
"\tScale: " + parameter.Scale + Environment.NewLine +
"\tIsNullable: " + parameter.IsNullable.ToString().ToLower() + Environment.NewLine +
"\tSourceColumn: " + parameter.SourceColumn + Environment.NewLine +
"\tSourceVersion: " + parameter.SourceVersion + Environment.NewLine);
}
fstr_out.WriteLine();
Transaction.Rollback();
Transaction = null;
Result = 0;
}
catch (AseException eException)
{
Console.WriteLine(eException.GetType().FullName + Environment.NewLine +
"Errors:" + Environment.NewLine + ErrorsToString(eException.Errors) + Environment.NewLine +
"Message: " + eException.Message + Environment.NewLine +
"Source: " + eException.Source + Environment.NewLine +
"StackTrace:" + Environment.NewLine + eException.StackTrace + Environment.NewLine +
"TargetSite: " + eException.TargetSite + Environment.NewLine);
}
catch (Exception eException)
{
Console.WriteLine(eException.GetType().FullName + Environment.NewLine +
"Message: " + eException.Message + Environment.NewLine +
"Source: " + eException.Source + Environment.NewLine +
"StackTrace:" + Environment.NewLine + eException.StackTrace + Environment.NewLine +
"TargetSite: " + eException.TargetSite + Environment.NewLine +
"InnerException:" + Environment.NewLine + eException.InnerException.GetType().FullName + Environment.NewLine +
"InnerException.Message: " + eException.InnerException.Message + Environment.NewLine +
"InnerException.Source: " + eException.InnerException.Source + Environment.NewLine +
"InnerException.StackTrace:" + Environment.NewLine + eException.InnerException.StackTrace + Environment.NewLine +
"InnerException.TargetSite: " + eException.InnerException.TargetSite);
}
}
finally
{
if (Transaction != null)
{
try
{
Transaction.Rollback();
}
catch
{
;
}
}
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
if (cmd != null)
{
cmd.Dispose();
}
if (conn != null && conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
if (fstr_out != null)
{
fstr_out.Close();
}
#if WITH_TRACE
if (_strmWriter != null)
{
_strmWriter.Close();
}
#endif
}
return(Result);
}
/// <summary>
/// Consulta de CorreoAplicacion, puede ser individual (por ID) o con filtros y órdenes.
/// </summary>
/// <param name="filtro">El filtro indica el modo, página, condiciones y orden de la consulta. Si viene el ID y modo=0, se consulta el registro específico</param>
/// <param name="resultado">Contiene el Código, Mensaje y Número de páginas obtenidos como resultados de la consulta</param>
/// <returns>Retorna la lista , utilizando una lista o arreglo</returns>
public List <DTO.clsCorreoAplicacion> CorreoAplicacionConsultar(DTO.clsFiltro filtro, out DTO.clsResultado resultado)
{
string strConexion = ConfigurationManager.ConnectionStrings["CADENA"].ConnectionString;
List <DTO.clsCorreoAplicacion> datos = new List <DTO.clsCorreoAplicacion>();
resultado = new DTO.clsResultado();
try
{
using (AseConnection con = new AseConnection(strConexion))
{
con.Open();
using (AseCommand cmd = new AseCommand("anda_reporte..CORREOAPLICACION_QRY", con))
{
cmd.CommandType = CommandType.StoredProcedure;
if (filtro.Id != null || filtro.Id > 0)
{
cmd.Parameters.Add(new AseParameter("I_ID", AseDbType.Integer, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Id));
}
cmd.Parameters.Add(new AseParameter("I_MODO", AseDbType.Integer, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Modo));
cmd.Parameters.Add(new AseParameter("I_FILAS", AseDbType.Integer, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Filas));
if (filtro.Filtro.Length > 0)
{
cmd.Parameters.Add(new AseParameter("I_FILTRO", AseDbType.VarChar, filtro.Filtro.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Filtro));
}
if (filtro.Orden.Length > 0)
{
cmd.Parameters.Add(new AseParameter("I_ORDEN", AseDbType.VarChar, filtro.Orden.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Orden));
}
cmd.Parameters.Add(new AseParameter("I_IR_A_PAGINA", AseDbType.Integer, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Pagina));
cmd.Parameters.Add(new AseParameter("@O_ROWS", AseDbType.Integer, 0, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, 0));
cmd.Parameters.Add(new AseParameter("@O_PAGES", AseDbType.Integer, 0, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, 0));
cmd.Parameters.Add(new AseParameter("@O_RETVAL", AseDbType.Integer, 0, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, 0));
cmd.Parameters.Add(new AseParameter("@O_RETMSG", AseDbType.NVarChar, 128, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, ""));
using (AseDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
DTO.clsCorreoAplicacion CorreoAplicacion = new DTO.clsCorreoAplicacion();
CorreoAplicacion.Codigo = dr["CA_CODIGO"] as Int32?;
CorreoAplicacion.CodigoAplicacion = dr["CA_CODIGO_APLICACION"] as String;
CorreoAplicacion.UserOrigen = dr["CA_USER_ORIGEN"] as String;
CorreoAplicacion.ClaveOrigen = dr["CA_CLAVE_ORIGEN"] as String;
CorreoAplicacion.SmtpServer = dr["CA_SMTP_SERVER"] as String;
CorreoAplicacion.SmtpServerPort = dr["CA_SMTP_SERVER_PORT"] as Int32?;
CorreoAplicacion.SmtpServerSsl = dr["CA_SMTP_SERVER_SSL"] as String;
CorreoAplicacion.PlantillaHtml = dr["CA_PLANTILLAHTML"] as String;
CorreoAplicacion.Logotipo = dr["CA_LOGOTIPO"] as String;
CorreoAplicacion.Firma = dr["CA_FIRMA"] as String;
CorreoAplicacion.Destino = dr["CA_DESTINO"] as String;
datos.Add(CorreoAplicacion);
}
}
resultado.Resultado = Convert.ToInt32(cmd.Parameters["@O_RETVAL"].Value);
resultado.Mensaje = cmd.Parameters["@O_RETMSG"].Value.ToString();
resultado.TotalPaginas = (cmd.Parameters["@O_PAGES"].Value != DBNull.Value) ? Convert.ToInt32(cmd.Parameters["@O_PAGES"].Value) : 0;
resultado.TotalRegistros = (cmd.Parameters["@O_ROWS"].Value != DBNull.Value) ? Convert.ToInt32(cmd.Parameters["@O_ROWS"].Value) : 0;
}
con.Close();
}
}
catch (Exception error)
{
string strSource = ConfigurationManager.AppSettings["NombreLog"];
using (EventLog eventLog = new System.Diagnostics.EventLog("Application", Environment.MachineName, strSource))
{
eventLog.WriteEntry("Error en DAL CorreoAplicacionConsultar... " + " Descripción= " + error.Message + " Stack: " + error.StackTrace, EventLogEntryType.Error, 0);
}
resultado.Resultado = -10;
resultado.Mensaje = ConfigurationManager.AppSettings["ErrorInternoMensaje"];
}
return(datos);
}
public List <Account> getAccounts(string BVN)
{
// BVN = "2014225";
LogWriter logWriter = new LogWriter();
AseCommand cmd = null;
AseConnection conn = null;
string sqlquery = null;
string cnstring = ConfigurationManager.ConnectionStrings["phoenixConnectionString"].ConnectionString;
List <Account> accountDetailList = new List <Account>();
try
{
conn = new AseConnection(cnstring);
// sqlquery = "select acct_no as AcctNo, Acct_name as AcctName from zenbase..zib_kyc_cust_information where bvn = '" + BVN + "' union select acct_no as AcctNo , Acct_name as AcctName from zenbase..zib_kyc_cust_information_wk where bvn = '" + BVN + "'";
sqlquery = "select acct_no as AcctNo, Acct_name as AcctName from zenbase..zib_kyc_cust_information where bvn = '" + BVN + "'";
cmd = new AseCommand(sqlquery, conn);
cmd.CommandTimeout = 0;
logWriter.WriteErrorLog(string.Format("Exception Message! / {0}", "before conn open"));
conn.Open();
if (conn.State == System.Data.ConnectionState.Open)
{
logWriter.WriteErrorLog(string.Format("Conection section / {0}", "connection open"));
}
else
{
logWriter.WriteErrorLog(string.Format("Conection section / {0}", "connection closed"));
}
logWriter.WriteErrorLog(string.Format("execute Section / {0}", "Before execute"));
AseDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
logWriter.WriteErrorLog(string.Format("execute Section / {0}", "after execute"));
if (reader == null)
{
logWriter.WriteErrorLog(string.Format("reader section / {0}", "Reader is null"));
}
else
{
logWriter.WriteErrorLog(string.Format("reader section / {0}", "Reader is not null"));
}
while (reader.Read())
{
Account account = new Account();
// account.AccountNo = AcctNo;
account.AccountNumber = reader["AcctNo"].ToString();
account.AccountName = reader["AcctName"].ToString();
if (account.AccountNumber != "" || account.AccountNumber != null)
{
account.AccountStatus = getAccountStatus(account.AccountNumber).AccountStatus;
}
else
{
account.AccountStatus = "";
}
accountDetailList.Add(account);
}
}
catch (Exception ex)
{
logWriter.WriteErrorLog(string.Format("Exception Message! / {0}", ex.Message));
}
finally
{
//cmd.Dispose();
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
return(accountDetailList);
}
/// <summary>
/// Actualización de CorreoAplicacion por ID
/// </summary>
/// <param name="CorreoAplicacion">CorreoAplicacion que se desea actualizar</param>
/// <param name="resultado">Obtiene el código y el mensaje de resultado</param>
/// <returns>Retorna el código de error de la transacción. Si retorna 0 es OK</returns>
public DTO.clsCorreoAplicacion CorreoAplicacionActualizar(DTO.clsCorreoAplicacion CorreoAplicacion, out DTO.clsResultado resultado)
{
string strConexion = ConfigurationManager.ConnectionStrings["CADENA"].ConnectionString;
resultado = new DTO.clsResultado();
try
{
using (AseConnection con = new AseConnection(strConexion))
{
con.Open();
using (AseCommand cmd = new AseCommand("Correo_Aplicacion_UPD", con))
{
cmd.CommandType = CommandType.StoredProcedure;
if (CorreoAplicacion.Codigo != null)
{
cmd.Parameters.Add(new AseParameter("@I_Codigo", AseDbType.Integer, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, CorreoAplicacion.Codigo));
}
if (CorreoAplicacion.CodigoAplicacion != null)
{
cmd.Parameters.Add(new AseParameter("@I_CodigoAplicacion", AseDbType.VarChar, CorreoAplicacion.CodigoAplicacion.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, CorreoAplicacion.CodigoAplicacion));
}
if (CorreoAplicacion.UserOrigen != null)
{
cmd.Parameters.Add(new AseParameter("@I_UserOrigen", AseDbType.VarChar, CorreoAplicacion.UserOrigen.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, CorreoAplicacion.UserOrigen));
}
if (CorreoAplicacion.ClaveOrigen != null)
{
cmd.Parameters.Add(new AseParameter("@I_ClaveOrigen", AseDbType.VarChar, CorreoAplicacion.ClaveOrigen.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, CorreoAplicacion.ClaveOrigen));
}
if (CorreoAplicacion.SmtpServer != null)
{
cmd.Parameters.Add(new AseParameter("@I_SmtpServer", AseDbType.VarChar, CorreoAplicacion.SmtpServer.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, CorreoAplicacion.SmtpServer));
}
if (CorreoAplicacion.SmtpServerPort != null)
{
cmd.Parameters.Add(new AseParameter("@I_SmtpServerPort", AseDbType.Integer, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, CorreoAplicacion.SmtpServerPort));
}
if (CorreoAplicacion.SmtpServerSsl != null)
{
cmd.Parameters.Add(new AseParameter("@I_SmtpServerSsl", AseDbType.Char, CorreoAplicacion.SmtpServerSsl.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, CorreoAplicacion.SmtpServerSsl));
}
cmd.Parameters.Add(new AseParameter("@O_RETVAL", AseDbType.Integer, 0, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, 0));
cmd.Parameters.Add(new AseParameter("@O_RETMSG", AseDbType.NVarChar, 128, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, ""));
using (AseDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
CorreoAplicacion.Codigo = dr["CA_CODIGO"] as Int32?;
CorreoAplicacion.CodigoAplicacion = dr["CA_CODIGO_APLICACION"] as String;
CorreoAplicacion.UserOrigen = dr["CA_USER_ORIGEN"] as String;
CorreoAplicacion.ClaveOrigen = dr["CA_CLAVE_ORIGEN"] as String;
CorreoAplicacion.SmtpServer = dr["CA_SMTP_SERVER"] as String;
CorreoAplicacion.SmtpServerPort = dr["CA_SMTP_SERVER_PORT"] as Int32?;
CorreoAplicacion.SmtpServerSsl = dr["CA_SMTP_SERVER_SSL"] as String;
}
}
resultado.Resultado = Convert.ToInt32(cmd.Parameters["@O_RETVAL"].Value);
resultado.Mensaje = cmd.Parameters["@O_RETMSG"].Value.ToString();
}
con.Close();
}
}
catch (Exception error)
{
string strSource = ConfigurationManager.AppSettings["NombreLog"];
using (EventLog eventLog = new System.Diagnostics.EventLog("Application", Environment.MachineName, strSource))
{
eventLog.WriteEntry("Error en DAL CorreoAplicacionActualizar... " + " Descripción= " + error.Message + " Stack: " + error.StackTrace, EventLogEntryType.Error, 0);
}
resultado.Resultado = -10;
resultado.Mensaje = ConfigurationManager.AppSettings["ErrorInternoMensaje"];
}
return(CorreoAplicacion);
}
public Account getCorporateAccountInfo(string acct_no, string initiator_number)
{
List <Loan> loans = new List <Loan>();
Account acct = null;
AseConnection conn = null;
AseCommand command = null;
AseDataReader reader = null;
StringBuilder s = new StringBuilder();
StringBuilder output = new StringBuilder();
string cnstring = ConfigurationManager.ConnectionStrings["phoenixConnectionString"].ConnectionString;
string sqltext = "zsp_cust_information";
//string sqltext = "select title_1,acct_no,acct_type,a.rsm_id as 'AccountRSMID'";
//sqltext += ",(select name from phoenix..ad_gb_rsm where employee_id = a.rsm_id) as 'AccountRSMName'";
//sqltext += ",b.rsm_id as 'RIMRSMID'";
//sqltext += ",(select name from phoenix..ad_gb_rsm where employee_id = b.rsm_id) as 'RIMRSMName'";
//sqltext += ",(select user_name from phoenix..ad_gb_rsm where employee_id = b.rsm_id) as 'RIMUSerName'";
//sqltext += ",(select d.staff_id from phoenix..ad_gb_rsm c, zib_applications_users d where c.user_name = d.user_id and ";
//sqltext += "employee_id = b.rsm_id) as 'StaffNo' ";
//sqltext += "from phoenix..dp_acct a, phoenix..rm_acct b ";
//sqltext += "where a.acct_no = @corperateacct_no ";// -----'1020041488'
//sqltext += "and a.rim_no = b.rim_no ";
try
{
using (conn = new AseConnection(cnstring))
{
conn.Open();
using (command = new AseCommand(sqltext, conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@psCorperateacct_no", acct_no);
command.Parameters.AddWithValue("@initiator_staffnumber", initiator_number);
reader = command.ExecuteReader();
while (reader.Read())
{
acct = new Account();
acct.account_name = reader["title_1"].ToString();
acct.acct_no = reader["acct_no"].ToString();
acct.acct_type = reader["acct_type"].ToString();
acct.domicile_branch_no = reader["BranchNo"].ToString();
acct.domicile_branch_name = reader["BranchName"].ToString();
acct.rim_no = reader["RIMNO"].ToString();
acct.rsm_id = reader["AccountRSMID"].ToString();
acct.rsm_name = reader["AccountRSMName"].ToString();
acct.rimrsm_id = reader["RIMRSMID"].ToString();
acct.rimrsm_name = reader["RIMRSMName"].ToString();
acct.rimrsm_username = reader["RIMUSerName"].ToString();
acct.rsm_staffno = reader["StaffNo"].ToString();
//New properties to be returned//
acct.sex = reader["sex"].ToString();
//acct.marital_status = reader["familystatus"].ToString();
acct.dob = reader["date_of_birth"].ToString();
acct.home_address = reader["home_address"].ToString();
acct.state_of_origin = reader["state_of_origin"].ToString();
acct.phonenumber = reader["phone_number"].ToString();
acct.email = reader["email_address"].ToString();
acct.next_of_kin = reader["next_of_kin"].ToString();
//New property to be returned - bvn - 03 NOV 2015//
acct.bvn = reader["bvn"].ToString();
acct.acct_type_desc = reader["acct_type_desc"].ToString();
//New property to be returned - bvn - 25 OCT 2016//
acct.initiator_branch = reader["initiator_branch"].ToString();
acct.initiator_branchcode = reader["initiator_branchcode"].ToString();
acct.initiator_phoenix_username = reader["initiator_phoenix_username"].ToString();
acct.initiator_phoenix_employee_id = reader["initiator_phoenix_employee_id"].ToString();
acct.initiator_phoenix_status = reader["initiator_phoenix_status"].ToString();
}
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
command.Dispose();
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
return(acct);
}
public GlobalLimit getRimInfo(string schemerimno, string schemeclasscode)
{
GlobalLimit gLimit = null;
AseConnection conn = null;
AseCommand command = null;
AseDataReader reader = null;
StringBuilder s = new StringBuilder();
StringBuilder output = new StringBuilder();
string cnstring = ConfigurationManager.ConnectionStrings["phoenixConnectionString"].ConnectionString;
string sqltext = "zsp_loans_GlobalLimit";
//string sqltext = "select sum(amt) as 'GlobalLimit'";
//sqltext += ",sum(undisbursed) as 'GlobalBalance'";
//sqltext += ",rsm_id as 'RIMRSMID' ";
//sqltext += "from phoenix..ln_umb ";
//sqltext += "where rim_no = @SchemeRimNo ";
//sqltext += "and class_code = @SchemeClassCode ";
//sqltext += "and status = 'Active' ";
try
{
using (conn = new AseConnection(cnstring))
{
conn.Open();
using (command = new AseCommand(sqltext, conn))
{
//command.Parameters.AddWithValue("@SchemeRimNo", int.Parse(schemerimno));
//command.Parameters.AddWithValue("@SchemeClassCode", int.Parse(schemeclasscode));
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@pnSchemeRimNo", int.Parse(schemerimno));
command.Parameters.AddWithValue("@pnSchemeClassCode", int.Parse(schemeclasscode));
reader = command.ExecuteReader();
while (reader.Read())
{
gLimit = new GlobalLimit();
gLimit.Limit = reader["GlobalLimit"].ToString();
gLimit.GlobalBalance = reader["GlobalBalance"].ToString();
}
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
command.Dispose();
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
return(gLimit);
}
public List <Loan> getApplicantLoans(string acct_no)
{
List <Loan> loans = new List <Loan>();
Loan loan = null;
AseConnection conn = null;
AseCommand command = null;
AseDataReader reader = null;
StringBuilder s = new StringBuilder();
StringBuilder output = new StringBuilder();
string cnstring = ConfigurationManager.ConnectionStrings["phoenixConnectionString"].ConnectionString;
string sqltext = "zsp_cust_exist_loans";
//string sqltext = "select (select description from phoenix..ad_ln_cls where class_code = a.class_code) as 'FacilityType',a.acct_no";
//sqltext += ",a.amt as 'FacilityAmount'";
//sqltext += ",a.period as 'RepaymentFreq'";
//sqltext += ",a.last_pmt_amt as 'LastRepaymentAmount'";
//sqltext += ",a.nxt_pmt_amt as 'NextRepaymentAmount'";
//sqltext += ",a.col_bal as 'CurrentBalance'";
//sqltext += ",a.mat_dt as 'FacilityMaturityDate'";
//sqltext += " from phoenix..ln_display a, phoenix..dp_acct b ";
//sqltext += "where b.acct_no = @applicantacct_no ";// -----'1020041488'
//sqltext += "and a.rim_no = b.rim_no ";
//sqltext += "and a.class_code != 585 ";
//sqltext += "and a.col_bal != 0 ";
//sqltext += "and a.status != 'Closed' ";
try
{
using (conn = new AseConnection(cnstring))
{
conn.Open();
using (command = new AseCommand(sqltext, conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
//command.Parameters.AddWithValue("@applicantacct_no", acct_no);
//AseParameter param = new AseParameter();
//param.ParameterName = "@applicantacct_no";
//param.Value = acct_no;
AseParameter param = new AseParameter();
param.ParameterName = "@psApplicantacct_no";
param.Value = acct_no;
command.Parameters.Add(param);
reader = command.ExecuteReader();
while (reader.Read())
{
loan = new Loan();
loan.FacilityType = reader["FacilityType"].ToString();
loan.acct_no = reader["acct_no"].ToString();
loan.FacilityAmount = reader["FacilityAmount"].ToString();
loan.RepaymentFreq = reader["RepaymentFreq"].ToString();
loan.LastRepaymentAmount = reader["LastRepaymentAmount"].ToString();
loan.NextRepaymentAmount = reader["NextRepaymentAmount"].ToString();
loan.FacilityMaturityDate = reader["FacilityMaturityDate"].ToString();
loan.CurrentBalance = reader["CurrentBalance"].ToString();
loans.Add(loan);
}
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
command.Dispose();
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
return(loans);
}
public static void DataReaderToDataTableTest()
{
PFSybase db = new PFSybase();
string connectionString = string.Empty;
Stopwatch sw = new Stopwatch();
try
{
db.ServerName = _frm.txtServerName.Text;
db.Port = _frm.txtPort.Text;
db.DatabaseName = _frm.txtDatabaseName.Text;
db.Username = _frm.txtUsername.Text;
db.Password = _frm.txtPassword.Text;
connectionString = db.ConnectionString;
_msg.Length = 0;
_msg.Append("Connection string is: \r\n");
_msg.Append(connectionString);
Program._messageLog.WriteLine(_msg.ToString());
if (_frm.txtSQLQuery.Text.Length == 0)
{
throw new System.Exception("You must specify a SQL query to run.");
}
sw.Start();
db.OpenConnection();
db.SQLQuery = _frm.txtSQLQuery.Text;
if (_frm.chkIsStoredProcedure.Checked)
{
db.CommandType = CommandType.StoredProcedure;
}
else
{
db.CommandType = CommandType.Text;
}
sw.Stop();
_msg.Length = 0;
_msg.Append("Open connection time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
sw.Start();
AseDataReader rdr = (AseDataReader)db.RunQueryDataReader();
DataTable tab = db.ConvertDataReaderToDataTable(rdr);
Program._messageLog.WriteLine("Table columns count: " + tab.Columns.Count.ToString());
rdr.Close();
for (int i = 0; i < tab.Rows.Count; i++)
{
DataRow r = tab.Rows[i];
_msg.Length = 0;
int maxColInx = tab.Columns.Count - 1;
for (int ci = 0; ci <= maxColInx; ci++)
{
_msg.Append(tab.Columns[ci].ColumnName);
_msg.Append(": ");
_msg.Append(r[ci].ToString());
if (ci < maxColInx)
{
_msg.Append(", ");
}
}
Program._messageLog.WriteLine(_msg.ToString());
}
sw.Stop();
_msg.Length = 0;
_msg.Append("Table read time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
}
catch (System.Exception ex)
{
_frm.OutputErrorMessageToLog(ex);
}
finally
{
db.CloseConnection();
db = null;
}
}
public static void DataReaderTest()
{
PFSybase db = new PFSybase();
string connectionString = string.Empty;
Stopwatch sw = new Stopwatch();
try
{
db.ServerName = _frm.txtServerName.Text;
db.Port = _frm.txtPort.Text;
db.DatabaseName = _frm.txtDatabaseName.Text;
db.Username = _frm.txtUsername.Text;
db.Password = _frm.txtPassword.Text;
connectionString = db.ConnectionString;
_msg.Length = 0;
_msg.Append("Connection string is: \r\n");
_msg.Append(connectionString);
Program._messageLog.WriteLine(_msg.ToString());
if (_frm.txtSQLQuery.Text.Length == 0)
{
throw new System.Exception("You must specify a SQL query to run.");
}
sw.Start();
db.OpenConnection();
db.SQLQuery = _frm.txtSQLQuery.Text;
if (_frm.chkIsStoredProcedure.Checked)
{
db.CommandType = CommandType.StoredProcedure;
}
else
{
db.CommandType = CommandType.Text;
}
sw.Stop();
_msg.Length = 0;
_msg.Append("Open connection time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
Program._messageLog.WriteLine("\r\nRunning data extract tests ...\r\n");
db.returnResultAsString += new PFSybase.ResultAsStringDelegate(OutputResultsToFile);
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\ReaderDelimitedTestExtract.txt", PFFileOpenOperation.OpenFileForWrite);
sw.Start();
AseDataReader rdr = (AseDataReader)db.RunQueryDataReader();
db.ExtractDelimitedDataFromDataReader(rdr, ",", "\r\n", true);
sw.Stop();
_msg.Length = 0;
_msg.Append("Extract Delimiated Dataset time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
_textFile.OpenFile(@"c:\temp\ReaderFixedLengthTestExtract.txt", PFFileOpenOperation.OpenFileForWrite);
rdr.Close();
sw.Start();
rdr = (AseDataReader)db.RunQueryDataReader();
db.ExtractFixedLengthDataFromDataReader(rdr, true, true, false);
sw.Stop();
_msg.Length = 0;
_msg.Append("Extract Fixed Length Dataset time: ");
_msg.Append(sw.FormattedElapsedTime);
Program._messageLog.WriteLine(_msg.ToString());
if (_textFile.FileIsOpen)
{
_textFile.CloseFile();
}
rdr.Close();
rdr = (AseDataReader)db.RunQueryDataReader();
db.SaveDataReaderToXmlFile(rdr, @"c:\temp\Testrdr.xml");
rdr.Close();
rdr = (AseDataReader)db.RunQueryDataReader();
db.SaveDataReaderWithSchemaToXmlFile(rdr, @"c:\temp\Testrdrplus.xml");
rdr.Close();
rdr = (AseDataReader)db.RunQueryDataReader();
db.SaveDataReaderToXmlSchemaFile(rdr, @"c:\temp\Testrdr.xsd");
rdr.Close();
rdr = (AseDataReader)db.RunQueryDataReader();
PFDataProcessor dataProcessor = new PFDataProcessor();
XmlDocument xmlDoc = dataProcessor.CopyDataTableToXmlDocument(db.ConvertDataReaderToDataTable(rdr));
Program._messageLog.WriteLine("\r\n" + xmlDoc.OuterXml + "\r\n");
rdr.Close();
}
catch (System.Exception ex)
{
_frm.OutputErrorMessageToLog(ex);
}
finally
{
db.CloseConnection();
db = null;
}
}
/// <summary>
/// Resumen de funcionalidad
/// </summary>
/// <param name="Aplicacion">El nuevo requerimiento a ser creado</param>
/// <param name="resultado">Los datos del resultado obtenidos después de su inserción</param>
/// <returns>El resultado de la operación, contiene el código de error generado por la operación de base de datos. Si retorna 0 es OK</returns>
public DTO.clsAplicacion AplicacionCrear(DTO.clsAplicacion Aplicacion, out DTO.clsResultado resultado)
{
string strConexion = ConfigurationManager.ConnectionStrings["CADENA"].ConnectionString;
string strMsg = "OK";
resultado = new DTO.clsResultado();
try
{
using (AseConnection con = new AseConnection(strConexion))
{
con.Open();
using (AseCommand cmd = new AseCommand("Aplicacion_ADD", con))
{
cmd.CommandType = CommandType.StoredProcedure;
if (Aplicacion.Codigo != null)
{
cmd.Parameters.Add(new AseParameter("@I_Codigo", AseDbType.VarChar, Aplicacion.Codigo.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, Aplicacion.Codigo));
}
if (Aplicacion.Nombre != null)
{
cmd.Parameters.Add(new AseParameter("@I_Nombre", AseDbType.VarChar, Aplicacion.Nombre.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, Aplicacion.Nombre));
}
if (Aplicacion.Descripcion != null)
{
cmd.Parameters.Add(new AseParameter("@I_Descripcion", AseDbType.VarChar, Aplicacion.Descripcion.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, Aplicacion.Descripcion));
}
if (Aplicacion.Estado != null)
{
cmd.Parameters.Add(new AseParameter("@I_Estado", AseDbType.Char, Aplicacion.Estado.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, Aplicacion.Estado));
}
cmd.Parameters.Add(new AseParameter("@O_RETVAL", AseDbType.Integer, 0, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, 0));
cmd.Parameters.Add(new AseParameter("@O_RETMSG", AseDbType.NVarChar, 128, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, ""));
using (AseDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
Aplicacion.Codigo = dr["AP_CODIGO"] as String;
Aplicacion.Nombre = dr["AP_NOMBRE"] as String;
Aplicacion.Descripcion = dr["AP_DESCRIPCION"] as String;
Aplicacion.Estado = dr["AP_ESTADO"] as String;
}
}
resultado.Resultado = Convert.ToInt32(cmd.Parameters["@O_RETVAL"].Value);
resultado.Mensaje = strMsg = cmd.Parameters["@O_RETMSG"].Value.ToString();
}
con.Close();
}
}
catch (Exception error)
{
string strSource = ConfigurationManager.AppSettings["NombreLog"];
using (EventLog eventLog = new System.Diagnostics.EventLog("Application", Environment.MachineName, strSource))
{
eventLog.WriteEntry("Error en DAL AplicacionCrear... " + " Descripción= " + error.Message + " Stack: " + error.StackTrace, EventLogEntryType.Error, 16);
}
resultado.Resultado = -10;
resultado.Mensaje = ConfigurationManager.AppSettings["ErrorInternoMensaje"];
}
return(Aplicacion);
}
private void InternalExecuteQueryAsync(AseCommand command, AseTransaction transaction, TaskCompletionSource <DbDataReader> readerSource, CommandBehavior behavior)
{
AssertExecutionStart();
#if ENABLE_SYSTEM_DATA_COMMON_EXTENSIONS
var dataReader = new AseDataReader(command, behavior, EventNotifier);
#else
var dataReader = new AseDataReader(behavior, EventNotifier);
#endif
try
{
SendPacket(new NormalPacket(BuildCommandTokens(command, behavior)));
var envChangeTokenHandler = new EnvChangeTokenHandler(_environment, _parameters.Charset);
var doneHandler = new DoneTokenHandler();
var dataReaderHandler = new StreamingDataReaderTokenHandler(readerSource, dataReader, EventNotifier);
var responseParameterTokenHandler = new ResponseParameterTokenHandler(command.AseParameters);
Logger.Instance?.WriteLine();
Logger.Instance?.WriteLine("---------- Receive Tokens ----------");
try
{
#if ENABLE_ARRAY_POOL
using (var tokenStream = new TokenReceiveStream(_networkStream, _environment, _arrayPool))
#else
using (var tokenStream = new TokenReceiveStream(_networkStream, _environment))
#endif
{
foreach (var receivedToken in _reader.Read(tokenStream, _environment))
{
if (envChangeTokenHandler.CanHandle(receivedToken.Type))
{
envChangeTokenHandler.Handle(receivedToken);
}
if (doneHandler.CanHandle(receivedToken.Type))
{
doneHandler.Handle(receivedToken);
}
if (dataReaderHandler.CanHandle(receivedToken.Type))
{
dataReaderHandler.Handle(receivedToken);
}
if (responseParameterTokenHandler.CanHandle(receivedToken.Type))
{
responseParameterTokenHandler.Handle(receivedToken);
}
}
}
}
finally
{
LastActive = DateTime.UtcNow;
}
// This tells the data reader to stop waiting for more results.
dataReader.CompleteAdding();
AssertExecutionCompletion(doneHandler);
if (transaction != null && doneHandler.TransactionState == TranState.TDS_TRAN_ABORT)
{
transaction.MarkAborted();
}
dataReaderHandler.AssertNoErrors();
if (doneHandler.Canceled)
{
readerSource.TrySetCanceled(); // If we have already begun returning data, then this will get lost.
}
else
{
readerSource.TrySetResult(dataReader); // Catchall - covers cases where no data is returned by the server.
}
}
catch (Exception ex)
{
// If we have already begun returning data, then this will get lost.
if (!readerSource.TrySetException(ex))
{
throw;
}
}
}
public IHttpActionResult GetStaffPhoenixDetails(string employeeID)
{
List <EmployeeInformation> employees = new List <EmployeeInformation>();
string connectionString = ConfigurationManager.ConnectionStrings["sybaseconnection"].ToString();
string queryString = "select b.user_id as user_id," +
"b.status as status," +
"convert(varchar(4), a.branch_no) as branch_no," +
"a.employee_id as employee_id," +
"c.name_1 as name_1," +
"a.empl_class_code as empl_class_code," +
"convert(varchar,a.last_logon_dt) as last_logon_dt," +
"a.state as state," +
"a.user_name as user_name," +
"a.name as name," +
"b.acct_no as acct_no," +
"b.email_address as email_address " +
"FROM phoenix..ad_gb_rsm a ," +
"zenbase..zib_applications_users b ," +
"phoenix..ad_gb_branch c " +
" WHERE b.user_id = a.user_name " +
" AND A.branch_no = c.branch_no " +
" AND b.staff_id = '" +
employeeID +
"'";
try
{
logwriter.WriteTolog("In try");
using (AseConnection connection = new AseConnection(connectionString))
{
AseCommand command = new AseCommand(queryString, connection);
connection.Open();
AseDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var employeeinfo = new EmployeeInformation()
{
user_id = reader["user_id"].ToString(),
status = reader["status"].ToString().Trim(),
branch_no = reader["branch_no"].ToString(),
employee_id = reader["employee_id"].ToString(),
name_1 = reader["name_1"].ToString(),
empl_class_code = reader["empl_class_code"].ToString(),
last_logon_dt = reader["last_logon_dt"].ToString(),
state = reader["state"].ToString(),
user_name = reader["user_name"].ToString(),
name = reader["name"].ToString(),
acct_no = reader["acct_no"].ToString(),
email_address = reader["email_address"].ToString()
};
employees.Add(employeeinfo);
}
reader.Close();
}
}
catch (Exception ex)
{
logwriter.WriteTolog("Error in inner exception: " + ex.InnerException);
logwriter.WriteTolog("Error in message: " + ex.Message);
}
return(Ok(employees));
}
/// <summary>
/// Consulta de Aplicacion, puede ser individual (por ID) o con filtros y órdenes.
/// </summary>
/// <param name="filtro">El filtro indica el modo, página, condiciones y orden de la consulta. Si viene el ID y modo=0, se consulta el registro específico</param>
/// <param name="resultado">Contiene el Código, Mensaje y Número de páginas obtenidos como resultados de la consulta</param>
/// <returns>Retorna la lista , utilizando una lista o arreglo</returns>
public List <DTO.clsDiasFeriados> DiasHabilesConsultar(DTO.clsFiltro filtro, out DTO.clsResultado resultado)
{
string strConexion = ConfigurationManager.ConnectionStrings["BDD_ANDALUCIA"].ConnectionString;
List <DTO.clsDiasFeriados> datos = new List <DTO.clsDiasFeriados>();
resultado = new DTO.clsResultado();
try
{
using (AseConnection con = new AseConnection(strConexion))
{
con.Open();
using (AseCommand cmd = new AseCommand("Aplicacion_QRY", con))
{
cmd.CommandType = CommandType.StoredProcedure;
if (filtro.Id != null || filtro.Id > 0)
{
cmd.Parameters.Add(new AseParameter("I_ID", AseDbType.Integer, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Id));
}
cmd.Parameters.Add(new AseParameter("I_MODO", AseDbType.Integer, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Modo));
cmd.Parameters.Add(new AseParameter("I_FILAS", AseDbType.Integer, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Filas));
if (filtro.Filtro.Length > 0)
{
cmd.Parameters.Add(new AseParameter("I_FILTRO", AseDbType.VarChar, filtro.Filtro.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Filtro));
}
if (filtro.Orden.Length > 0)
{
cmd.Parameters.Add(new AseParameter("I_ORDEN", AseDbType.VarChar, filtro.Orden.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Orden));
}
cmd.Parameters.Add(new AseParameter("I_IR_A_PAGINA", AseDbType.Integer, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Pagina));
cmd.Parameters.Add(new AseParameter("@O_ROWS", AseDbType.Integer, 0, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, 0));
cmd.Parameters.Add(new AseParameter("@O_PAGES", AseDbType.Integer, 0, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, 0));
cmd.Parameters.Add(new AseParameter("@O_RETVAL", AseDbType.Integer, 0, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, 0));
cmd.Parameters.Add(new AseParameter("@O_RETMSG", AseDbType.NVarChar, 128, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, ""));
using (AseDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
DTO.clsDiasFeriados DiasFeriados = new DTO.clsDiasFeriados();
DiasFeriados.Codigo = dr["ID_REGISTRO"] as Int32?;
DiasFeriados.Ciudad = dr["df_ciudad"] as Int32?;
DiasFeriados.Fecha = dr["df_fecha"] as DateTime?;
datos.Add(DiasFeriados);
}
}
resultado.Resultado = Convert.ToInt32(cmd.Parameters["@O_RETVAL"].Value);
resultado.Mensaje = cmd.Parameters["@O_RETMSG"].Value.ToString();
resultado.TotalPaginas = (cmd.Parameters["@o_fecha_sd"].Value != DBNull.Value) ? Convert.ToInt32(cmd.Parameters["@O_PAGES"].Value) : 0;
resultado.TotalRegistros = (cmd.Parameters["@O_ROWS"].Value != DBNull.Value) ? Convert.ToInt32(cmd.Parameters["@O_ROWS"].Value) : 0;
}
con.Close();
}
}
catch (Exception error)
{
string strSource = ConfigurationManager.AppSettings["NombreLog"];
using (EventLog eventLog = new System.Diagnostics.EventLog("Application", Environment.MachineName, strSource))
{
eventLog.WriteEntry("Error en DAL AplicacionConsultar... " + " Descripción= " + error.Message + " Stack: " + error.StackTrace, EventLogEntryType.Error, 0);
}
resultado.Resultado = -10;
resultado.Mensaje = ConfigurationManager.AppSettings["ErrorInternoMensaje"];
}
return(datos);
}
/// <summary>
/// consulta la fecha de saldo diario y la fecha de cierre
/// </summary>
/// <param name="ParametrosIngreso"></param>
/// <param name="resultado"></param>
/// <returns></returns>
public List <DTO.clsDiasFeriados> CargarDataFechas(DTO.clsFiltro filtro, out DTO.clsResultado resultado)
{
string strConexion = ConfigurationManager.ConnectionStrings["BDD_ANDALUCIA"].ConnectionString;
resultado = new DTO.clsResultado();
List <DTO.clsDiasFeriados> datos = new List <DTO.clsDiasFeriados>();
try
{
using (AseConnection con = new AseConnection(strConexion))
{
con.Open();
using (AseCommand cmd = new AseCommand("anda_reporte..sp_cc_carga_datos_fechas", con))
{
cmd.CommandType = CommandType.StoredProcedure;
String strTimeoutComando = ConfigurationManager.AppSettings["TimeoutComandos"];
if (strTimeoutComando != null && strTimeoutComando.Length > 0)
{
cmd.CommandTimeout = int.Parse(strTimeoutComando);
}
cmd.Parameters.Add(new AseParameter("I_MODO", AseDbType.Integer, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Modo));
if (filtro.Filtro.Length > 0)
{
cmd.Parameters.Add(new AseParameter("I_FILTRO", AseDbType.VarChar, filtro.Filtro.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Filtro));
}
cmd.Parameters.Add(new AseParameter("@o_fecha_sd", AseDbType.DateTime, 0, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, 0));
cmd.Parameters.Add(new AseParameter("@o_fecha_cierre", AseDbType.DateTime, 128, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, ""));
cmd.Parameters.Add(new AseParameter("@O_RETVAL", AseDbType.Integer, 0, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, 0));
cmd.Parameters.Add(new AseParameter("@O_RETMSG", AseDbType.NVarChar, 128, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, ""));
using (AseDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
DTO.clsDiasFeriados DiasFeriados = new DTO.clsDiasFeriados();
DiasFeriados.Codigo = dr["ID_REGISTRO"] as Int32?;
DiasFeriados.Ciudad = dr["df_ciudad"] as Int32?;
DiasFeriados.Fecha = dr["df_fecha"] as DateTime?;
datos.Add(DiasFeriados);
}
}
// cmd.ExecuteNonQuery();
resultado.FechaSd = Convert.ToDateTime(cmd.Parameters["@o_fecha_sd"].Value.ToString() == ""?null: cmd.Parameters["@o_fecha_sd"].Value.ToString());
resultado.FechaCierre = Convert.ToDateTime(cmd.Parameters["@o_fecha_cierre"].Value.ToString() == ""?null: cmd.Parameters["@o_fecha_cierre"].Value.ToString());
resultado.Resultado = Convert.ToInt32(cmd.Parameters["@O_RETVAL"].Value);
resultado.Mensaje = cmd.Parameters["@O_RETMSG"].Value.ToString();
}
con.Close();
}
}
catch (Exception error)
{
string strSource = ConfigurationManager.AppSettings["NombreLog"];
using (EventLog eventLog = new System.Diagnostics.EventLog("Application", Environment.MachineName, strSource))
{
eventLog.WriteEntry("Error en DAL CuentaEliminar... " + " Descripción= " + error.Message + " Stack: " + error.StackTrace, EventLogEntryType.Error, 0);
}
resultado.Resultado = -10;
resultado.Mensaje = error.Message;
}
return(datos);
}
public async Task <string> PullNostroVostroData()
{
Library library = new Library();
try
{
LogManager.SaveLog("Task Start in NostroVostroLibrary");
var ReconType = await repoReconTypeRepository.GetAsync(c => c.WSReconName == "NostroVostro");
int ReconTypeId = ReconType.ReconTypeId;
var AccountSources = await repoadmSourceAccountRepository.GetManyAsync(c => c.ReconTypeId == ReconTypeId);
var listofAccountsource1 = AccountSources.Where(c => c.SourceName == "Source 1" && c.AcctNo != null);
string acctlistSource1 = string.Empty;
string acctlistSource2 = string.Empty;
int dt1 = 0;
// int dt2 = 0;
foreach (var li in listofAccountsource1)
{
acctlistSource1 += "'" + li.AcctNo + "'" + ", ";
dt1 = (int)li.DataSourceId;
}
int index = acctlistSource1.LastIndexOf(',');
if (!string.IsNullOrEmpty(acctlistSource1))
{
acctlistSource1 = (acctlistSource1.Remove(index, 1));
}
var dtSouceCon1 = await repoadmReconDataSourcesRepository.GetAsync(c => c.ReconDSId == dt1);
#region Source 1
#region // Sybase Server below
if (dtSouceCon1.DatabaseType == "SYBASE")
{
try
{
LogManager.SaveLog("PullDataNostro for Sybase Start in NostroVostroLibrary");
var value1 = string.Empty;
value1 = string.Empty;
var value2 = string.Empty;
value2 = string.Empty;
int scriptExecTtype = 0;
string FromDateParam = string.Empty;
string ToDateParam = string.Empty;
string LastRecordId = string.Empty;
string CBSRecordCount = string.Empty;
string MaxTransDate = string.Empty;
string dateTest = string.Empty;
string ReconDate = string.Empty;
var controlTable = await repoadmDataPoollingControlRepository.GetAsync(c => c.ReconTypeId == ReconType.ReconTypeId && c.TableName == "CBSNostroTrans");
if (controlTable != null)
{
FromDateParam = controlTable.FromDateParam == null?DateTime.Now.ToString() : string.Format("{0:yyyyMMdd}", Convert.ToDateTime(controlTable.FromDateParam));
FromDateParam = "'" + FromDateParam + "'";
ToDateParam = controlTable.ToDateParam == null?DateTime.Now.ToString() : string.Format("{0:yyyyMMdd}", Convert.ToDateTime(controlTable.ToDateParam));
ToDateParam = "'" + ToDateParam + "'";
ReconDate = controlTable.LastTransDate == null?DateTime.Now.ToString() : string.Format("{0:yyyyMMdd}", Convert.ToDateTime(controlTable.LastTransDate));
ReconDate = "'" + ReconDate + "'";
LastRecordId = controlTable.LastRecordId;
}
else
{
LastRecordId = "0";
FromDateParam = "'20170901'";
ToDateParam = "'20170923'";
ReconDate = "'20170923'";
}
string pdt = "";// parametername.Split(' ')[1];
List <AseParameter> parameterPasses = new List <AseParameter>()
{
new AseParameter()
{
ParameterName = pdt, AseDbType = AseDbType.VarChar, Value = value1
},
new AseParameter()
{
ParameterName = pdt, AseDbType = AseDbType.VarChar, Value = value2
}
};
string connstring = System.Configuration.ConfigurationManager.AppSettings["sybconnection"].ToString();
connstring = connstring.Replace("{{Data Source}}", dtSouceCon1.ipAddress);
connstring = connstring.Replace("{{port}}", dtSouceCon1.PortNumber);
connstring = connstring.Replace("{{database}}", dtSouceCon1.DatabaseName);
connstring = connstring.Replace("{{uid}}", dtSouceCon1.UserName);
connstring = connstring.Replace("{{pwd}}", library.Decrypt(dtSouceCon1.Password));
DataSet dsGetData = new DataSet();
using (AseConnection theCons = new AseConnection(connstring))
{
DataSet ds = new DataSet();
try
{
theCons.Open();
if (theCons.State.ToString() == "Open")
{
LogManager.SaveLog("Sybase Connection open for Ip " + dtSouceCon1.ipAddress);
}
else
{
LogManager.SaveLog("Sybase Connection not Open for Ip " + dtSouceCon1.ipAddress);
}
}
catch (Exception ex)
{
var exErr = ex == null ? ex.InnerException.Message : ex.Message;
var stackTrace = new StackTrace(ex);
var thisasm = Assembly.GetExecutingAssembly();
_methodname = stackTrace.GetFrames().Select(f => f.GetMethod()).First(m => m.Module.Assembly == thisasm).Name;
_lineErrorNumber = ex.StackTrace.Substring(ex.StackTrace.Length - 7, 7);
LogManager.SaveLog("An error occured NostroVostroLibrary Line: " + _lineErrorNumber + " CLASSNAME: " + _classname + " METHOD NAME: [" + _methodname + "] ERROR: " + exErr);
}
try
{
var CurrencyList = AccountSources.Where(c => c.SourceName == "Source 1" && c.Currency != null && c.Currency != string.Empty);
int gocount = 0;
foreach (var cur in CurrencyList)
{
//87572420
string currency = "'" + cur.Currency + "'";;
string Account = "'" + cur.AcctNo + "'";;
string SqlString = ReconType.Source1Script.Replace("{Account}", Account)
.Replace("{Currency}", currency)
.Replace("{LastRecordId}", LastRecordId)
.Replace("{ReconDate}", ReconDate);
AseCommand cmd = new AseCommand(SqlString, theCons);
cmd.Connection = theCons;
cmd.CommandText = SqlString;
cmd.CommandTimeout = 0;
//i.e check if the parameters are not null, if the are null, that means scriptExecTtype = 1,
// would be using CommandText not store procdure
if (!string.IsNullOrWhiteSpace(value1) && !string.IsNullOrWhiteSpace(value1))
{
if (parameterPasses != null)
{
cmd.Parameters.AddRange(parameterPasses.ToArray());
var gggg = (parameterPasses.ToArray());
}
}
else
{
scriptExecTtype = 1;
}
cmd.CommandType = scriptExecTtype == 0 ? CommandType.StoredProcedure : CommandType.Text;
AseDataReader reader = cmd.ExecuteReader();
ds.EnforceConstraints = false;
ds.Load(reader, LoadOption.OverwriteChanges, "Results");
var returndata = ds.Tables["Results"];
LogManager.SaveLog("PullDataCBSNostroTrans for Sybase returndata.Rows.Count " + returndata.Rows.Count);
if (returndata.Rows.Count > 0)
{
var CBSNostroTransTBL = new CBSNostroTran();
var CBSNostroTransTBLErr = new CBSNostroTransError();
int count = 0;
int countTrans = 0;
for (int col = 0; col < returndata.Rows.Count; col++)
{
try
{
CBSNostroTransTBL.AcctNo = returndata.Rows[col][0] == null ? null : returndata.Rows[col][0].ToString();
CBSNostroTransTBL.AcctType = returndata.Rows[col][1] == null ? null : returndata.Rows[col][1].ToString();
CBSNostroTransTBL.TransDate = returndata.Rows[col][2] == null ? (DateTime?)null : Convert.ToDateTime(returndata.Rows[col][2]);;
dateTest = CBSNostroTransTBL.TransDate.ToString();
if (countTrans > 1)
{
MaxTransDate = CBSNostroTransTBL.TransDate.ToString();
if (Convert.ToDateTime(dateTest) > Convert.ToDateTime(MaxTransDate))
{
MaxTransDate = dateTest;
}
else
{
MaxTransDate = MaxTransDate;
}
}
CBSNostroTransTBL.Amount = returndata.Rows[col][3] == null ? 0 : Math.Round(Convert.ToDecimal(returndata.Rows[col][3]), 2);
CBSNostroTransTBL.Description = returndata.Rows[col][4] == null ? null : returndata.Rows[col][4].ToString();
CBSNostroTransTBL.Reference = returndata.Rows[col][5] == null ? null : returndata.Rows[col][5].ToString();
CBSNostroTransTBL.OrigRefNo = CBSNostroTransTBL.Reference;
CBSNostroTransTBL.DebitCredit = returndata.Rows[col][6] == null ? null : returndata.Rows[col][6].ToString();
CBSNostroTransTBL.OriginatingBranch = returndata.Rows[col][7] == null ? null : returndata.Rows[col][7].ToString();
CBSNostroTransTBL.PostedBy = returndata.Rows[col][8] == null ? null : returndata.Rows[col][8].ToString();
CBSNostroTransTBL.Currency = returndata.Rows[col][9] == null ? null : returndata.Rows[col][9].ToString();
CBSNostroTransTBL.PtId = returndata.Rows[col][10] == null ? null : returndata.Rows[col][10].ToString();
int revcode;
CBSNostroTransTBL.ReversalCode = returndata.Rows[col][11] == null ? (int?)null : int.TryParse(returndata.Rows[col][11].ToString(), out revcode) ? revcode : (int?)null;
LastRecordId = CBSNostroTransTBL.PtId;
CBSNostroTransTBL.MatchingStatus = "N";
CBSNostroTransTBL.PullDate = DateTime.Now;
CBSNostroTransTBL.UserId = 1;
countTrans += 1;
if (!string.IsNullOrWhiteSpace(CBSNostroTransTBL.Reference))
{
var exist = await repoCBSNostroVostroTransRepository.GetAsync(c => (c.Reference == CBSNostroTransTBL.Reference && c.Amount == CBSNostroTransTBL.Amount));
var existHis = await repoCBSNostroVostroTransHistoryRepository.GetAsync(c => c.Reference == CBSNostroTransTBL.Reference && c.Amount == CBSNostroTransTBL.Amount);
if (exist != null || existHis != null)
{
CBSNostroTransTBLErr.AcctNo = CBSNostroTransTBL.AcctNo;
CBSNostroTransTBLErr.AcctType = CBSNostroTransTBL.AcctType;
CBSNostroTransTBLErr.TransDate = CBSNostroTransTBL.TransDate;
CBSNostroTransTBLErr.Amount = CBSNostroTransTBL.Amount;
CBSNostroTransTBLErr.Description = CBSNostroTransTBL.Description;
CBSNostroTransTBLErr.Reference = CBSNostroTransTBL.Reference;
CBSNostroTransTBLErr.OrigRefNo = CBSNostroTransTBL.Reference;
CBSNostroTransTBLErr.DebitCredit = CBSNostroTransTBL.DebitCredit;
CBSNostroTransTBLErr.OriginatingBranch = CBSNostroTransTBL.OriginatingBranch;
CBSNostroTransTBLErr.PostedBy = CBSNostroTransTBL.PostedBy;
CBSNostroTransTBLErr.Currency = CBSNostroTransTBL.Currency;
CBSNostroTransTBLErr.PtId = CBSNostroTransTBL.PtId;
LastRecordId = CBSNostroTransTBLErr.PtId;
CBSNostroTransTBLErr.MatchingStatus = CBSNostroTransTBL.MatchingStatus;
CBSNostroTransTBLErr.PullDate = CBSNostroTransTBL.PullDate;
CBSNostroTransTBLErr.ReversalCode = CBSNostroTransTBL.ReversalCode;
CBSNostroTransTBLErr.UserId = 1;
CBSNostroTransTBLErr.ErrorMsg = "Duplicate transaction record";
repoCBSNostroVostroTransErrorRepository.Add(CBSNostroTransTBLErr);
var ret1 = await unitOfWork.Commit(0, null) > 0 ? true : false;
if (ret1)
{
continue;
}
continue;
}
else
{
repoCBSNostroVostroTransRepository.Add(CBSNostroTransTBL);
var ret = await unitOfWork.Commit(0, null) > 0 ? true : false;
if (ret)
{
count += 1;
CBSRecordCount = count.ToString();
continue;
}
}
}
else
{
CBSNostroTransTBLErr.AcctNo = CBSNostroTransTBL.AcctNo;
CBSNostroTransTBLErr.AcctType = CBSNostroTransTBL.AcctType;
CBSNostroTransTBLErr.TransDate = CBSNostroTransTBL.TransDate;
CBSNostroTransTBLErr.Amount = CBSNostroTransTBL.Amount;
CBSNostroTransTBLErr.Description = CBSNostroTransTBL.Description;
CBSNostroTransTBLErr.Reference = CBSNostroTransTBL.Reference;
CBSNostroTransTBLErr.OrigRefNo = CBSNostroTransTBL.Reference;
CBSNostroTransTBLErr.DebitCredit = CBSNostroTransTBL.DebitCredit;
CBSNostroTransTBLErr.OriginatingBranch = CBSNostroTransTBL.OriginatingBranch;
CBSNostroTransTBLErr.PostedBy = CBSNostroTransTBL.PostedBy;
CBSNostroTransTBLErr.Currency = CBSNostroTransTBL.Currency;
CBSNostroTransTBLErr.PtId = CBSNostroTransTBL.PtId;
LastRecordId = CBSNostroTransTBLErr.PtId;
CBSNostroTransTBLErr.MatchingStatus = CBSNostroTransTBL.MatchingStatus;
CBSNostroTransTBLErr.PullDate = CBSNostroTransTBL.PullDate;
CBSNostroTransTBLErr.ReversalCode = CBSNostroTransTBL.ReversalCode;
CBSNostroTransTBLErr.UserId = 1;
CBSNostroTransTBLErr.ErrorMsg = "No ref No.";
repoCBSNostroVostroTransErrorRepository.Add(CBSNostroTransTBLErr);
var ret1 = await unitOfWork.Commit(0, null) > 0 ? true : false;
if (ret1)
{
continue;
}
}
}
catch (Exception ex)
{
var exErr = ex == null ? ex.InnerException.Message : ex.Message;
var stackTrace = new StackTrace(ex);
var thisasm = Assembly.GetExecutingAssembly();
_methodname = stackTrace.GetFrames().Select(f => f.GetMethod()).First(m => m.Module.Assembly == thisasm).Name;
_lineErrorNumber = ex.StackTrace.Substring(ex.StackTrace.Length - 7, 7);
LogManager.SaveLog("An error occured NostroVostroLibrary in Line: " + _lineErrorNumber + " CLASSNAME: " + _classname + " METHOD NAME: [" + _methodname + "] ERROR: " + exErr);
try
{
CBSNostroTransTBLErr.AcctNo = CBSNostroTransTBL.AcctNo;
CBSNostroTransTBLErr.AcctType = CBSNostroTransTBL.AcctType;
CBSNostroTransTBLErr.TransDate = CBSNostroTransTBL.TransDate;
CBSNostroTransTBLErr.Amount = CBSNostroTransTBL.Amount;
CBSNostroTransTBLErr.Description = CBSNostroTransTBL.Description;
CBSNostroTransTBLErr.Reference = CBSNostroTransTBL.Reference;
CBSNostroTransTBLErr.OrigRefNo = CBSNostroTransTBL.Reference;
CBSNostroTransTBLErr.DebitCredit = CBSNostroTransTBL.DebitCredit;
CBSNostroTransTBLErr.OriginatingBranch = CBSNostroTransTBL.OriginatingBranch;
CBSNostroTransTBLErr.PostedBy = CBSNostroTransTBL.PostedBy;
CBSNostroTransTBLErr.Currency = CBSNostroTransTBL.Currency;
CBSNostroTransTBLErr.PtId = CBSNostroTransTBL.PtId;
LastRecordId = CBSNostroTransTBLErr.PtId;
CBSNostroTransTBLErr.MatchingStatus = CBSNostroTransTBL.MatchingStatus;
CBSNostroTransTBLErr.PullDate = CBSNostroTransTBL.PullDate;
CBSNostroTransTBLErr.UserId = 1;
CBSNostroTransTBLErr.ErrorMsg = ex == null ? ex.InnerException.Message : ex.Message;
repoCBSNostroVostroTransErrorRepository.Add(CBSNostroTransTBLErr);
var ret1 = unitOfWork.CommitNonAsync(0, null) > 0 ? true : false;
if (ret1)
{
continue;
}
}
catch (Exception ex1)
{
var exErr2 = ex1 == null ? ex.InnerException.Message : ex.Message;
var stackTrace2 = new StackTrace(ex);
var thisas = Assembly.GetExecutingAssembly();
_methodname = stackTrace.GetFrames().Select(f => f.GetMethod()).First(m => m.Module.Assembly == thisas).Name;
_lineErrorNumber = ex.StackTrace.Substring(ex.StackTrace.Length - 7, 7);
LogManager.SaveLog("An error occured NostroVostroLibrary in Line: " + _lineErrorNumber + " CLASSNAME: " + _classname + " METHOD NAME: [" + _methodname + "] ERROR: " + exErr);
continue;
}
continue;
}
}
if (controlTable != null)
{
//Update with below
string updatScript = "update CBSNostroTrans set VostroAcctNo = b.VostroAcctNo "
+ " from CBSNostroTrans a,admSourceAccount b "
+ " where a.AcctNo = b.AcctNo "
+ " and b.ReconTypeId = " + ReconType.ReconTypeId;
string connectionstring = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection con = new SqlConnection(connectionstring);
con.Open();
SqlCommand commd = new SqlCommand(updatScript, con);
commd.CommandType = CommandType.Text;
commd.ExecuteNonQuery();
con.Close();
controlTable.LastRecordId = LastRecordId;
controlTable.LastRecordTimeStamp = DateTime.Now;
DateTime dt = new DateTime();
controlTable.LastTransDate = MaxTransDate == null ? DateTime.Now : DateTime.TryParse(MaxTransDate, out dt) ? dt : DateTime.Now;
controlTable.PullDate = DateTime.Now;
int recTrack;
controlTable.RecordsCount = CBSRecordCount == null ? (int?)null : int.TryParse(CBSRecordCount, out recTrack) ? recTrack : (int?)null;
repoadmDataPoollingControlRepository.Update(controlTable);
var ret = await unitOfWork.Commit(0, null) > 0 ? true : false;
if (ret)
{
}
}
else
{
string updatScript = "update CBSNostroTrans set VostroAcctNo = b.VostroAcctNo "
+ " from CBSNostroTrans a,admSourceAccount b "
+ " where a.AcctNo = b.AcctNo "
+ " and b.ReconTypeId = " + ReconType.ReconTypeId;
string connectionstring = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection con = new SqlConnection(connectionstring);
con.Open();
SqlCommand commd = new SqlCommand(updatScript, con);
commd.CommandType = CommandType.Text;
commd.ExecuteNonQuery();
con.Close();
var admDataPoollingControlTBL = new admDataPullingControl();
admDataPoollingControlTBL.ReconTypeId = ReconType.ReconTypeId;
admDataPoollingControlTBL.FileType = "Table";
admDataPoollingControlTBL.TableName = "CBSNostroTrans";
admDataPoollingControlTBL.DateCreated = DateTime.Now;
admDataPoollingControlTBL.UserId = 1;
admDataPoollingControlTBL.LastRecordTimeStamp = DateTime.Now;
admDataPoollingControlTBL.LastRecordId = LastRecordId;
admDataPoollingControlTBL.ReconLevel = 1;
DateTime dt = new DateTime();
admDataPoollingControlTBL.LastTransDate = MaxTransDate == null ? DateTime.Now : DateTime.TryParse(MaxTransDate, out dt) ? dt : DateTime.Now;
admDataPoollingControlTBL.PullDate = DateTime.Now;
int recTrack;
admDataPoollingControlTBL.RecordsCount = CBSRecordCount == null ? (int?)null : int.TryParse(CBSRecordCount, out recTrack) ? recTrack : (int?)null;
repoadmDataPoollingControlRepository.Add(admDataPoollingControlTBL);
var ret = await unitOfWork.Commit(0, null) > 0 ? true : false;
if (ret)
{
}
}
}
var lstRec = await repoadmDataPoollingControlRepository.GetAsync(c => c.ReconTypeId == ReconType.ReconTypeId && c.TableName == "CBSNostroTrans");
LastRecordId = lstRec.LastRecordId;
}
// reader.Close();
theCons.Close();
}
catch (Exception ex)
{
var exErr = ex == null ? ex.InnerException.Message : ex.Message;
var stackTrace = new StackTrace(ex);
var thisasm = Assembly.GetExecutingAssembly();
_methodname = stackTrace.GetFrames().Select(f => f.GetMethod()).First(m => m.Module.Assembly == thisasm).Name;
_lineErrorNumber = ex.StackTrace.Substring(ex.StackTrace.Length - 7, 7);
LogManager.SaveLog("An error occured NostroVostroLibrary in Line: " + _lineErrorNumber + " CLASSNAME: " + _classname + " METHOD NAME: [" + _methodname + "] ERROR: " + exErr);
}
}
}
catch (Exception ex)
{
var exErr = ex == null ? ex.InnerException.Message : ex.Message;
var stackTrace = new StackTrace(ex);
var thisasm = Assembly.GetExecutingAssembly();
_methodname = stackTrace.GetFrames().Select(f => f.GetMethod()).First(m => m.Module.Assembly == thisasm).Name;
_lineErrorNumber = ex.StackTrace.Substring(ex.StackTrace.Length - 7, 7);
LogManager.SaveLog("An error occured in CBSNostroVostroTrans Line: " + _lineErrorNumber + " CLASSNAME: " + _classname + " METHOD NAME: [" + _methodname + "] ERROR: " + exErr);
}
}
#endregion
#endregion
#region Source 2
var dtSouceCon2 = await repoadmSourceAccountRepository.GetAsync(c => c.ReconTypeId == ReconTypeId && c.SourceName == "Source 2");
#region //File Directory Below
if (!string.IsNullOrWhiteSpace(dtSouceCon2.FileDirectory))
{
try
{
string LastRecordId = string.Empty;
var controlTable = await repoadmDataPoollingControlRepository.GetAsync(c => c.ReconTypeId == ReconType.ReconTypeId && c.TableName == "VostroMT940950Trans");
if (controlTable != null)
{
LastRecordId = controlTable.LastRecordId;
}
else
{
LastRecordId = "0";
}
string FileDirectory = dtSouceCon2.FileDirectory;
DataTable dTable = new DataTable();
DirectoryInfo d = new DirectoryInfo(FileDirectory);
List <FileInfo> DLIST = null;
DLIST = d.GetFiles("*" + ".out").ToList();
List <FileInfo> ggg = null;
if (controlTable != null)
{
ggg = DLIST;//.Where(c => c.LastWriteTime > controlTable.LastRecordTimeStamp).ToList();
}
else
{
ggg = DLIST;
}
string fileNamenAndType = string.Empty;
string FileLastTime = string.Empty;
int count = 0;
string returnValue = string.Empty;
foreach (var kl in ggg)
{
fileNamenAndType = (from f in DLIST orderby f.LastWriteTime ascending select kl).First().Name;
FileLastTime = (from f in DLIST orderby f.LastAccessTimeUtc descending select kl).First().LastWriteTime.ToString();
int read = await readMT940.Generate940950Message(dtSouceCon2.FileDirectory + "\\" + fileNamenAndType, ReconTypeId, dtSouceCon2.FileDirectory, fileNamenAndType, FileLastTime);
returnValue = read.ToString();
if (!string.IsNullOrWhiteSpace(returnValue))
{
// Move here
var proceTBL = new FileProcessControl();
proceTBL.ReconTypeId = ReconTypeId;
proceTBL.FileDirectory = FileDirectory;
proceTBL.FileName = fileNamenAndType;
proceTBL.NameConvention = ReconType.FileNamingConvention;
proceTBL.DateProcessed = DateTime.Now;
proceTBL.Status = "Processed";
proceTBL.DateCreated = DateTime.Now;
proceTBL.UserId = 1;
repoadmFileProcessedRepository.Add(proceTBL);
var ret1 = await unitOfWork.Commit(0, null) > 0 ? true : false;
if (ret1)
{
LogManager.SaveLog("Move File Start Nostro Vostro");
string MvFile = library.MoveFile(FileDirectory + "\\" + fileNamenAndType, ReconType.ProcessedFileDirectory, fileNamenAndType);
LogManager.SaveLog("Move File in Nostro Vostro status: " + MvFile);
}
}
}
}
catch (Exception ex)
{
var exErr = ex == null ? ex.InnerException.Message : ex.Message;
var stackTrace = new StackTrace(ex);
var thisasm = Assembly.GetExecutingAssembly();
_methodname = stackTrace.GetFrames().Select(f => f.GetMethod()).First(m => m.Module.Assembly == thisasm).Name;
_lineErrorNumber = ex.StackTrace.Substring(ex.StackTrace.Length - 7, 7);
LogManager.SaveLog("An error occured in NostroVostroLibrary Line : " + _lineErrorNumber + " CLASSNAME: " + _classname + " METHOD NAME: [" + _methodname + "] ERROR: " + exErr);
}
}
#endregion
#endregion
}
catch (Exception ex)
{
var exErr = ex == null ? ex.InnerException.Message : ex.Message;
var stackTrace = new StackTrace(ex);
var thisasm = Assembly.GetExecutingAssembly();
_methodname = stackTrace.GetFrames().Select(f => f.GetMethod()).First(m => m.Module.Assembly == thisasm).Name;
_lineErrorNumber = ex.StackTrace.Substring(ex.StackTrace.Length - 7, 7);
LogManager.SaveLog("An error occured in NostroVostroLogManager Line: " + _lineErrorNumber + " CLASSNAME: " + _classname + " METHOD NAME: [" + _methodname + "] ERROR: " + exErr);
throw;
}
return(string.Empty);
}
