public int GetCountErrorBdwContact()
{
int count = 0;
string stmt = "SELECT COUNT(*) FROM TB_I_BDW_CONTACT WHERE ERROR IS NOT NULL AND ERROR <> @inError";
string connStr = WebConfig.GetConnectionString("CSMConnectionString");
try
{
using (SqlConnection thisConnection = new SqlConnection(connStr))
{
using (SqlCommand cmdCount = new SqlCommand(stmt, thisConnection))
{
thisConnection.Open();
cmdCount.Parameters.Add(new SqlParameter("@inError", "Duplicate Record"));
count = (int)cmdCount.ExecuteScalar();
}
}
return(count);
}
catch (Exception ex)
{
Logger.Error("Exception occur:\n", ex);
return(0);
}
}
public static string DecryptSqlPwd(string connectionStr)
{
string SqlStr = WebConfig.GetConnectionString(connectionStr);
string[] str = SqlStr.Split(';');
StringBuilder sb = new StringBuilder();
string ss = string.Empty;
foreach (string s in str)
{
if (s.Contains("User ID="))
{
ss = s.Replace("User ID=", "");
ss = Shinfotech.Tools.Encryptions.DecryptDES(ss, "Shunovo20150701");
sb.Append("User ID=" + ss + ";");
}
else if (s.Contains("Password="******"Password="******"");
ss = Shinfotech.Tools.Encryptions.DecryptDES(ss, "Shunovo20150701");
sb.Append("Password="******";");
}
else if (s.Contains("uid="))
{
ss = s.Replace("uid=", "");
ss = Shinfotech.Tools.Encryptions.DecryptDES(ss, "Shunovo20150701");
sb.Append("uid=" + ss + ";");
}
else if (s.Contains("pwd="))
{
ss = s.Replace("pwd=", "");
ss = Shinfotech.Tools.Encryptions.DecryptDES(ss, "Shunovo20150701");
sb.Append("pwd=" + ss + ";");
}
else if (string.IsNullOrEmpty(s))
{
continue;
}
else
{
sb.Append(s + ";");
}
}
return(sb.ToString());
}
public bool SaveBdwContact(List <BdwContactEntity> bdwContacts)
{
SqlConnection con = null;
SqlBulkCopy bc = null;
try
{
if (bdwContacts != null && bdwContacts.Count > 0)
{
_context.Database.ExecuteSqlCommand("DELETE FROM TB_I_BDW_CONTACT");
this.Save();
int pageSize = 5000;
int totalPage = (bdwContacts.Count + pageSize - 1) / pageSize;
Task.Factory.StartNew(() => Parallel.For(0, totalPage, new ParallelOptions {
MaxDegreeOfParallelism = WebConfig.GetTotalCountToProcess()
},
k =>
{
var lstContact = from contact in bdwContacts.Skip(k * pageSize).Take(pageSize)
select
new TB_I_BDW_CONTACT
{
DATA_TYPE = contact.DataType,
DATA_DATE = contact.DataDate,
DATA_SOURCE = contact.DataSource,
CARD_NO = contact.CardNo,
TITILE_TH = contact.TitileTh,
NAME_TH = contact.NameTh,
SURNAME_TH = contact.SurnameTh,
TITILE_EN = contact.TitileEn,
NAME_EN = contact.NameEn,
SURNAME_EN = contact.SurnameEn,
ACCOUNT_NO = contact.AccountNo,
LOAN_MAIN = contact.LoanMain,
PRODUCT_GROUP = contact.ProductGroup,
PRODUCT = contact.Product,
RELATIONSHIP = contact.Relationship,
PHONE = contact.Phone,
ACCOUNT_STATUS = contact.AccountStatus,
CARD_TYPE_CODE = contact.CardTypeCode,
CAMPAIGN = contact.Campaign
};
DataTable dt = DataTableHelpers.ConvertTo(lstContact);
con = new SqlConnection(WebConfig.GetConnectionString("CSMConnectionString"));
bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
bc.DestinationTableName = "TB_I_BDW_CONTACT";
bc.BatchSize = dt.Rows.Count;
con.Open();
bc.WriteToServer(dt);
})).Wait();
bdwContacts = null; // clear
}
return(true);
}
catch (Exception ex)
{
Logger.Error("Exception occur:\n", ex);
}
finally
{
if (bc != null)
{
bc.Close();
}
if (con != null)
{
con.Close();
}
}
return(false);
}
public bool InsertHRTempTable(List <HRIEmployeeEntity> data)
{
SqlConnection con = null;
SqlBulkCopy bc = null;
try
{
if (data != null && data.Any())
{
//_context.Database.ExecuteSqlCommand("TRUNCATE TABLE TB_I_HR_EMPLOYEE;");
_context.Database.ExecuteSqlCommand("DELETE FROM TB_I_HR_EMPLOYEE;");
Save();
int pageSize = 5000;
int totalPage = (data.Count() + pageSize - 1) / pageSize;
Task.Factory.StartNew(() => Parallel.For(0, totalPage, new ParallelOptions {
MaxDegreeOfParallelism = WebConfig.GetTotalCountToProcess()
},
k =>
{
var lst = from d in data.Skip(k * pageSize).Take(pageSize)
select
new TB_I_HR_EMPLOYEE
{
BRANCH = d.Branch,
BRANCHDESC = d.BranchDesc,
EMPLOYEEID = d.EmployeeId,
TITLEID = d.TitleId,
TTITLE = d.Title,
TFNAME = d.FName,
TLNAME = d.LName,
NICKNAME = d.Nickname,
XENGNAME = d.FullNameEng,
ETITLE = d.ETitle,
EFNAME = d.EFName,
ELNAME = d.ELName,
SEX = d.Sex,
BIRTHDAY = d.BirthDay,
EMPTYPE = d.EmpType,
EMPTYPEDESC = d.EmpTypeDesc,
POSITION = d.Position,
POSITONDESC = d.PositionDesc,
BU1 = d.BU1,
BU1DESC = d.BU1Desc,
BU2 = d.BU2,
BU2DESC = d.BU2Desc,
BU3 = d.BU3,
BU3DESC = d.BU3Desc,
BU4 = d.BU4,
BU4DESC = d.BU4Desc,
JOB = d.Job,
JOBPOSITION = d.JobPosition,
STARTDATE = d.StartDate,
FIRSTHIREDATE = d.FirstHireDate,
RESIGNDATE = d.ResignDate,
STATUS = d.Status,
EMAIL = d.Email,
NOTESADDRESS = d.NotesAddress,
WORKAREA = d.WorkArea,
WORKAREADESC = d.WorkAreaDesc,
COSTCENTER = d.CostCenter,
COSTCENTERDESC = d.CostCenterDesc,
TELEXT = d.TelExt,
BOSS = d.Boss,
BOSSNAME = d.BossName,
ASSESSOR1 = d.Assessor1,
ASSESSOR1NAME = d.Assessor1Name,
ASSESSOR2 = d.Assessor2,
ASSESSOR2NAME = d.Assessor2Name,
ASSESSOR3 = d.Assessor3,
ASSESSOR3NAME = d.Assessor3Name,
TELNO = d.TelNo,
MOBILENO = d.MobileNo,
ADUSER = d.ADUser,
OFFICER_ID = d.OfficerId,
OFFICER_DESC = d.OfficerDesc,
ADDITIONJOB = d.AdditionJob,
UNITBOSS = d.UnitBoss,
UNITBOSSNAME = d.UnitBossName,
IDNO = d.IDNO,
ERROR = d.Error
};
DataTable dt = DataTableHelpers.ConvertTo(lst);
con = new SqlConnection(WebConfig.GetConnectionString("CSMConnectionString"));
bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
bc.DestinationTableName = "TB_I_HR_EMPLOYEE";
bc.BatchSize = dt.Rows.Count;
con.Open();
bc.WriteToServer(dt);
})).Wait();
data = null; // clear
}
return(true);
}
catch (Exception ex)
{
Logger.Error("Exception occur:\n", ex);
}
finally
{
if (bc != null)
{
bc.Close();
}
if (con != null)
{
con.Close();
}
}
return(false);
}
