public static DC_ImportDataFromXLITE GetDC_ImportDataFromXLITE(string ID)
{
string PROCEDURE = "p_SelectDC_ImportDataFromXLITE";
SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = new SqlParameter();
parameters[0].ParameterName = "@ID";
parameters[0].Value = ID;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, System.Data.CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DC_ImportDataFromXLITE
{
ID = row["ID"].ToString(),
Date = Convert.ToDateTime(row["Date"].ToString()),
Time = Convert.ToDateTime(row["Time"].ToString()),
Source = row["Source"].ToString(),
Destination = row["Destination"].ToString(),
Duration = row["Duration"].ToString(),
Type = row["Type"].ToString(),
File = row["File"].ToString(),
LastUpdatedTime = Convert.ToDateTime(row["LastUpdatedTime"].ToString()),
LastUpdatedUser = row["LastUpdatedUser"].ToString()
}).ToList().FirstOrDefault());
}
public static DC_Customer_Resigned GetDC_Customer_Resigned(string CustomerID)
{
string PROCEDURE = "p_SelectDC_Customer_Resigned";
SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = new SqlParameter();
parameters[0].ParameterName = "@CustomerID";
parameters[0].Value = CustomerID;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DC_Customer_Resigned
{
KeyCustomerID = Helpers.Locdau.convertToUnSign3(row["CustomerID"].ToString()),
CustomerID = row["CustomerID"].ToString(),
ResignedDate = Convert.ToDateTime(row["ResignedDate"].ToString()),
InformedDate = Convert.ToDateTime(row["InformedDate"].ToString()),
ResignedReason = row["ResignedReason"].ToString(),
Reference = row["Reference"].ToString(),
Note = row["Note"].ToString(),
Active = Convert.ToBoolean(row["Active"]),
RowID = Convert.ToInt32(row["RowID"].ToString()),
RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString()
}).ToList().FirstOrDefault());
}
public static List <DC_Location_Region> GetDC_Location_Regions(string whereCondition, string orderByExpression)
{
string PROCEDURE = "p_SelectDC_Location_RegionsDynamic";
SqlParameter[] parameters = new SqlParameter[2];
int i = 0;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@WhereCondition";
parameters[i].Value = whereCondition;
i++;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@OrderByExpression";
parameters[i].Value = orderByExpression;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DC_Location_Region
{
RegionID = row["RegionID"].ToString(),
RegionName = row["RegionName"].ToString(),
Active = Convert.ToBoolean(row["Active"].ToString()),
RowID = Convert.ToInt32(row["RowID"].ToString()),
RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString(),
CountryID = row["CountryID"].ToString(),
CountryName = row["CountryName"].ToString(),
AliasName = row["AliasName"].ToString(),
}).ToList());
}
public static List <DC_Seniority> GetDC_Senioritys(string whereCondition, string orderByExpression)
{
string PROCEDURE = "p_SelectDC_SenioritiesDynamic";
SqlParameter[] parameters = new SqlParameter[2];
int i = 0;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@WhereCondition";
parameters[i].Value = whereCondition;
i++;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@OrderByExpression";
parameters[i].Value = orderByExpression;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DC_Seniority
{
SeniorityID = Convert.ToInt32(row["SeniorityID"].ToString()),
Seniority = row["Seniority"].ToString(),
Quit = Convert.ToDouble(row["Quit"].ToString()),
Status = Convert.ToBoolean(row["Status"].ToString()),
RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString(),
MounthTo = Convert.ToInt32(row["MounthTo"].ToString()),
MounthFrom = Convert.ToInt32(row["MounthFrom"].ToString())
}).ToList());
}
public static DC_Tele_Hint_Meta GetDC_Tele_Hint_Meta(string ArticleID, string Factor)
{
string PROCEDURE = "p_SelectDC_Tele_Hint_Meta";
SqlParameter[] parameters = new SqlParameter[2];
int i = 0;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@ArticleID";
parameters[i].Value = ArticleID;
i++;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@Factor";
parameters[i].Value = Factor;
i++;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, System.Data.CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DC_Tele_Hint_Meta
{
ArticleID = row["ArticleID"].ToString(),
Factor = row["Factor"].ToString(),
Value = row["Value"].ToString(),
Active = Convert.ToBoolean(row["Active"].ToString()),
RowID = Convert.ToInt16(row["RowID"].ToString()),
RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString()
}).ToList().FirstOrDefault());
}
public static List <DC_Potential_Customer> GetDC_Potential_Customers(string whereCondition, string orderByExpression)
{
string PROCEDURE = "p_SelectDC_Potential_CustomersDynamic";
SqlParameter[] parameters = new SqlParameter[2];
int i = 0;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@WhereCondition";
parameters[i].Value = whereCondition;
i++;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@OrderByExpression";
parameters[i].Value = orderByExpression;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DC_Potential_Customer
{
OrganizationID = row["OrganizationID"].ToString(),
EmployeeID = row["EmployeeID"].ToString(),
FullName = row["FullName"].ToString(),
IndentityNumber = row["IndentityNumber"].ToString(),
Phone = row["Phone"].ToString(),
Active = Convert.ToBoolean(row["Active"].ToString()),
RowID = Convert.ToInt32(row["RowID"].ToString()),
RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString(),
}).ToList());
}
public static DC_Potential_Customer checkPhone(string Phone, string OrganizationID, string EmployeeID)
{
string PROCEDURE = "select top 1 EmployeeID,OrganizationID from DC_Potential_Customer where Phone = @Phone and organizationID = @OrganizationID and EmployeeID <> @EmployeeID";
SqlParameter[] parameters = new SqlParameter[3];
int i = 0;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@Phone";
parameters[i].Value = Phone;
i++;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@OrganizationID";
parameters[i].Value = OrganizationID;
i++;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@EmployeeID";
parameters[i].Value = EmployeeID;
i++;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.Text, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DC_Potential_Customer
{
EmployeeID = row["EmployeeID"].ToString(),
OrganizationID = row["OrganizationID"].ToString(),
}).ToList().FirstOrDefault());
}
public static DC_Company_Result GetDC_Company_Result(string ResultID)
{
string PROCEDURE = "p_SelectDC_Company_Result";
SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = new SqlParameter();
parameters[0].ParameterName = "@ResultID";
parameters[0].Value = ResultID;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DC_Company_Result
{
ResultID = row["ResultID"].ToString(),
ResultName = row["ResultName"].ToString(),
Active = Convert.ToBoolean(row["Active"].ToString()),
XMLString = row["XMLString"].ToString(),
RowID = Convert.ToInt16(row["RowID"].ToString()),
RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString(),
Recommand = row["Recommand"].ToString()
}).ToList().FirstOrDefault());
}
public static List <DC_HomeNetwork> GetDC_HomeNetworks(string whereCondition, string orderByExpression)
{
string PROCEDURE = "p_SelectDC_HomeNetworksDynamic";
SqlParameter[] parameters = new SqlParameter[2];
int i = 0;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@WhereCondition";
parameters[i].Value = whereCondition;
i++;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@OrderByExpression";
parameters[i].Value = orderByExpression;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DC_HomeNetwork
{
ID = Convert.ToInt32(row["ID"].ToString()),
NetworkName = row["NetworkName"].ToString(),
Decription = row["Decription"].ToString(),
Active = Convert.ToBoolean(row["Active"].ToString()),
CreatedDatetime = Convert.ToDateTime(row["CreatedDatetime"].ToString()),
CreatedUser = row["CreatedUser"].ToString(),
LastUpdatedDateTime = Convert.ToDateTime(row["LastUpdatedDateTime"].ToString()),
LastUpdatedUser = row["LastUpdatedUser"].ToString(),
ValidText = row["ValidText"].ToString()
}).ToList());
}
public static UserInOrg GetUserInOrg(string UserID, string OrgID)
{
string PROCEDURE = "p_SelectUserInOrg";
SqlParameter[] parameters = new SqlParameter[2];
int i = 0;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@UserID";
parameters[i].Value = UserID;
i++;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@OrgID";
parameters[i].Value = OrgID;
i++;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new UserInOrg
{
UserID = row["UserID"].ToString(),
OrgID = row["OrgID"].ToString(),
CreatedDatetime = Convert.ToDateTime(row["CreatedDatetime"].ToString()),
CreatedUser = row["CreatedUser"].ToString()
}).ToList().FirstOrDefault());
}
public static List <UserInOrg> GetUserInOrgs(string whereCondition, string orderByExpression)
{
string PROCEDURE = "p_SelectUserInOrgsDynamic";
SqlParameter[] parameters = new SqlParameter[2];
int i = 0;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@WhereCondition";
parameters[i].Value = whereCondition;
i++;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@OrderByExpression";
parameters[i].Value = orderByExpression;
i++;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new UserInOrg
{
UserID = row["UserID"].ToString(),
OrgID = row["OrgID"].ToString(),
CreatedDatetime = Convert.ToDateTime(row["CreatedDatetime"].ToString()),
CreatedUser = row["CreatedUser"].ToString()
}).ToList());
}
public static DataSourceResult GetAllDC_ImportDataFromXLITEDynamicRead(string WhereCondition, [DataSourceRequest] DataSourceRequest request)
{
var from1 = (request.Page - 1) * request.PageSize;
var to = (request.Page - 1) * request.PageSize + request.PageSize;
string PROCEDURE = "p_SelectDC_ImportDataFromXLITEsAllDynamic";
SqlParameter[] parameters = new SqlParameter[2];
int i = 0;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@WhereCondition";
parameters[i].Value = WhereCondition;
i++;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@WhereCondition1";
parameters[i].Value = "RowNum BETWEEN " + from1 + " AND " + to;
i++;
var data = new DataSourceResult();
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, System.Data.CommandType.StoredProcedure, PROCEDURE, parameters);
data.Data = dt.AsEnumerable().Select(row => new DC_ImportDataFromXLITE
{
ID = row["ID"].ToString(),
Date = Convert.ToDateTime(row["Date"].ToString()),
Time = Convert.ToDateTime(row["Date"].ToString()),
Source = row["Source"].ToString(),
Destination = row["Destination"].ToString(),
Duration = row["Duration"].ToString(),
Type = row["Type"].ToString(),
File = row["File"].ToString(),
LastUpdatedTime = Convert.ToDateTime(row["LastUpdatedTime"].ToString()),
LastUpdatedUser = row["LastUpdatedUser"].ToString(),
CustomerID = row["CustomerID"].ToString(),
EmployeeID = row["EmployeeID"].ToString(),
FullName = row["FullName"].ToString(),
Position = row["Position"].ToString(),
PhysicalID = row["PhysicalID"].ToString(),
BD = row["BD"].ToString(),
OrganizationID = row["OrganizationID"].ToString(),
CreditLimit = double.Parse(row["CreditLimit"].ToString()),
DCStatus = row["DCStatus"].ToString(),
Gender = row["Gender"].ToString(),
MobilePhone = row["MobilePhone"].ToString(),
Email = row["Email"].ToString(),
DueLimit = double.Parse(row["DueLimit"].ToString()),
}).ToList();
var total = dt.AsEnumerable().Select(row => new DC_ImportDataFromXLITE
{
TotalRows = Int32.Parse(row["TotalRows"].ToString())
}).ToList().FirstOrDefault();
data.Total = total != null ? total.TotalRows : 0;
return(data);
}
public static List <DC_ImportDataFromXLITE> GetAllDC_ImportDataFromXLITEs()
{
string PROCEDURE = "p_SelectDC_ImportDataFromXLITEsAll";
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, System.Data.CommandType.StoredProcedure, PROCEDURE, null);
return(dt.AsEnumerable().Select(row => new DC_ImportDataFromXLITE
{
ID = row["ID"].ToString(),
Date = Convert.ToDateTime(row["Date"].ToString()),
Time = Convert.ToDateTime(row["Time"].ToString()),
Source = row["Source"].ToString(),
Destination = row["Destination"].ToString(),
Duration = row["Duration"].ToString(),
Type = row["Type"].ToString(),
File = row["File"].ToString(),
LastUpdatedTime = Convert.ToDateTime(row["LastUpdatedTime"].ToString()),
LastUpdatedUser = row["LastUpdatedUser"].ToString(),
CustomerID = row["CustomerID"].ToString(),
EmployeeID = row["EmployeeID"].ToString(),
FullName = row["FullName"].ToString(),
Position = row["Position"].ToString(),
PhysicalID = row["PhysicalID"].ToString(),
BD = row["BD"].ToString(),
OrganizationID = row["OrganizationID"].ToString(),
CreditLimit = double.Parse(row["CreditLimit"].ToString()),
DCStatus = row["DCStatus"].ToString(),
Gender = row["Gender"].ToString(),
MobilePhone = row["MobilePhone"].ToString(),
Email = row["Email"].ToString(),
DueLimit = double.Parse(row["DueLimit"].ToString()),
}).ToList());
}
public static List <DC_ImportDataFromXLITE> GetDC_ImportDataFromXLITEs(string whereCondition, string orderByExpression)
{
string PROCEDURE = "p_SelectDC_ImportDataFromXLITEsDynamic";
SqlParameter[] parameters = new SqlParameter[2];
int i = 0;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@WhereCondition";
parameters[i].Value = whereCondition;
i++;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@OrderByExpression";
parameters[i].Value = orderByExpression;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, System.Data.CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DC_ImportDataFromXLITE
{
ID = row["ID"].ToString(),
Date = Convert.ToDateTime(row["Date"].ToString()),
Time = Convert.ToDateTime(row["Time"].ToString()),
Source = row["Source"].ToString(),
Destination = row["Destination"].ToString(),
Duration = row["Duration"].ToString(),
Type = row["Type"].ToString(),
File = row["File"].ToString(),
LastUpdatedTime = Convert.ToDateTime(row["LastUpdatedTime"].ToString()),
LastUpdatedUser = row["LastUpdatedUser"].ToString()
}).ToList());
}
public static List <DC_CheckList_ApplyFor> GetDC_CheckList_Definition_Orgs(string whereCondition, string orderByExpression)
{
string PROCEDURE = "p_SelectDC_CheckList_Definition_OrgsDynamic";
SqlParameter[] parameters = new SqlParameter[2];
int i = 0;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@WhereCondition";
parameters[i].Value = whereCondition;
i++;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@OrderByExpression";
parameters[i].Value = orderByExpression;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DC_CheckList_ApplyFor
{
ChecklistSubID = row["ChecklistSubID"].ToString(),
ChecklistID = row["ChecklistID"].ToString(),
OrganizationID = row["OrganizationID"].ToString(),
CompanyID = row["CompanyID"].ToString(),
RowID = Convert.ToInt32(row["RowID"].ToString()),
RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString()
}).ToList());
}
public static List <DC_TeleSale_KPI_Actual> GetDataKPI_ActualTeleSaleForFF(string AgentID)
{
string PROCEDURE = "p_SelectTeleSale_GetKPIAndActualForFF";
SqlParameter[] parameters = new SqlParameter[1];
int i = 0;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@AgentID";
parameters[i].Value = AgentID;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DC_TeleSale_KPI_Actual
{
AgentID = row["AgentID"].ToString(),
SO = Convert.ToDouble(row["SO"]),
SOKPI = Convert.ToDouble(row["SOKPI"]),
SOPercentColor = row["SOPercentColor"].ToString(),
SOKPercent = float.Parse(row["SOKPercent"].ToString()),
Rev = Convert.ToDouble(row["Rev"]),
RevKPI = Convert.ToDouble(row["RevKPI"]),
RevPercentColor = row["RevPercentColor"].ToString(),
RevPercent = float.Parse(row["RevPercent"].ToString()),
}).ToList());
}
public static DC_Potential_Customer GetSingleDC_Potential_Customer(string OrganizationID, string EmployeeID)
{
string text = @"SELECT OrganizationID,EmployeeID,RowCreatedTime,RowCreatedUser,IndentityNumber,FullName,
ISNULL(XMLString.value('(/Root/StartWorkDate/text())[1]', 'nvarchar(max)'),'1900-01-01') AS 'StartWorkDate'
FROM DC_Potential_Customer Where OrganizationID = @OrganizationID AND EmployeeID = @EmployeeID " ;
SqlParameter[] parameters = new SqlParameter[2];
parameters[0] = new SqlParameter();
parameters[0].ParameterName = "@OrganizationID";
parameters[0].Value = OrganizationID;
parameters[1] = new SqlParameter();
parameters[1].ParameterName = "@EmployeeID";
parameters[1].Value = EmployeeID;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.Text, text, parameters);
return(dt.AsEnumerable().Select(row => new DC_Potential_Customer
{
OrganizationID = row["OrganizationID"].ToString(),
EmployeeID = row["EmployeeID"].ToString(),
RowCreatedTime = DateTime.Parse(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
FullName = row["FullName"].ToString(),
IndentityNumber = row["IndentityNumber"].ToString(),
StartWorkDate = Convert.ToDateTime(row["StartWorkDate"].ToString()),
}).ToList().FirstOrDefault());
}
public static DW_SMSMT GetDW_SMSMT(long RowID)
{
string PROCEDURE = "p_SelectDW_SMSMT";
SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = new SqlParameter();
parameters[0].ParameterName = "@RowID";
parameters[0].Value = RowID;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, System.Data.CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DW_SMSMT
{
RowID = long.Parse(row["RowID"].ToString()),
AgentID = row["AgentID"].ToString(),
RequestID = row["RequestID"].ToString(),
ServiceID = row["ServiceID"].ToString(),
CommandCode = row["CommandCode"].ToString(),
UserID = row["UserID"].ToString(),
Message = row["Message"].ToString(),
MessageType = long.Parse(row["MessageType"].ToString()),
CreatedAt = Convert.ToDateTime(row["CreatedAt"].ToString()),
Status = row["Status"].ToString(),
DWID = long.Parse(row["DWID"].ToString()),
Active = Convert.ToBoolean(row["Active"].ToString()),
RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString()
}).ToList().FirstOrDefault());
}
public static List <DC_Potential_Customer> GetDC_Potential_Customer_ForCSTool(string CustomerID)
{
string PROCEDURE = "p_DC_Potential_Customer_SelectForCSTool";
SqlParameter[] parameters = new SqlParameter[1];
int i = 0;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@CustomerID";
parameters[i].Value = CustomerID;
i++;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DC_Potential_Customer
{
EmployeeID = row["EmployeeID"].ToString(),
FullName = row["FullName"].ToString(),
IndentityNumber = row["IndentityNumber"].ToString(),
Phone = row["Phone"].ToString(),
Salary = double.Parse(row["Salary"].ToString()),
Title = row["Title"].ToString(),
Department = row["Department"].ToString(),
Address = row["Address"].ToString(),
Birthday = Convert.ToDateTime(row["Birthday"].ToString()),
StartWorkDate = Convert.ToDateTime(row["StartWorkDate"].ToString()),
PlaceOfIssuance = row["PlaceOfIssuance"].ToString(),
DateOfIssuance = Convert.ToDateTime(row["DateOfIssuance"].ToString()),
}).ToList());
}
public static List <DW_SMSMT> GetAllDW_SMSMTs()
{
string PROCEDURE = "p_SelectDW_SMSMTsAll";
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, System.Data.CommandType.StoredProcedure, PROCEDURE, null);
return(dt.AsEnumerable().Select(row => new DW_SMSMT
{
RowID = long.Parse(row["RowID"].ToString()),
AgentID = row["AgentID"].ToString(),
RequestID = row["RequestID"].ToString(),
ServiceID = row["ServiceID"].ToString(),
CommandCode = row["CommandCode"].ToString(),
UserID = row["UserID"].ToString(),
Message = row["Message"].ToString(),
MessageType = long.Parse(row["MessageType"].ToString()),
CreatedAt = Convert.ToDateTime(row["CreatedAt"].ToString()),
Status = row["Status"].ToString(),
StatusColor = row["Status"].ToString().Contains("Fail") ? "background-color:#D81F44;color:#FFF;" : row["Status"].ToString().Contains("Success") ? "background-color:#3E9BCE;color:#FFF;" : "background-color:#F1E377;color:#AF6B15;",
DWID = long.Parse(row["DWID"].ToString()),
Active = Convert.ToBoolean(row["Active"].ToString()),
RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString(),
Telco = row["Telco"].ToString()
}).ToList());
}
public static DC_Seniority GetDC_Seniority(int SeniorityID)
{
string PROCEDURE = "p_SelectDC_Seniority";
SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = new SqlParameter();
parameters[0].ParameterName = "@SeniorityID";
parameters[0].Value = SeniorityID;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DC_Seniority
{
SeniorityID = Convert.ToInt32(row["SeniorityID"].ToString()),
Seniority = row["Seniority"].ToString(),
Quit = Convert.ToDouble(row["Quit"].ToString()),
Status = Convert.ToBoolean(row["Status"].ToString()),
RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString(),
MounthTo = Convert.ToInt32(row["MounthTo"].ToString()),
MounthFrom = Convert.ToInt32(row["MounthFrom"].ToString())
}).ToList().FirstOrDefault());
}
public static UserProfile GetUser(string UserName)
{
string PROCEDURE = "p_SelectUserProfile";
SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = new SqlParameter();
parameters[0].ParameterName = "@UserName";
parameters[0].Value = UserName;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new UserProfile
{
UserName = row["UserName"].ToString(),
Email = row["Email"].ToString(),
CreatedUser = row["CreatedUser"].ToString(),
LastUpdatedUser = row["LastUpdatedUser"].ToString(),
CompanyID = string.IsNullOrEmpty(row["CompanyID"].ToString()) ? "0".ToString() : row["CompanyID"].ToString(),
DepartmentID = string.IsNullOrEmpty(row["DepartmentID"].ToString()) ? 0 : Convert.ToInt32(row["DepartmentID"].ToString()),
Phone = string.IsNullOrEmpty(row["Phone"].ToString()) ? "".ToString() : row["Phone"].ToString(),
Team = string.IsNullOrEmpty(row["Team"].ToString()) ? "0".ToString() : row["Team"].ToString(),
Position = string.IsNullOrEmpty(row["Position"].ToString()) ? "0".ToString() : row["Position"].ToString(),
Gender = row["Gender"].ToString(),
EmployeeID = row["EmployeeID"].ToString(),
RefID = row["RefID"].ToString(),
Avatar = row["Avatar"].ToString(),
LevelName = row["Level"].ToString(),
LevelID = row["LevelID"].ToString(),
PositionID = row["PositionID"].ToString(),
Department = row["Department"].ToString(),
BranchID = row["BranchID"].ToString(),
RegionID = row["RegionID"].ToString(),
}).ToList().FirstOrDefault());
}
public static DC_Customer_Resigned_Activity_CusInfo_Meta GetDC_Customer_Resigned_Activity_CusInfo_Meta(string CustomerID, string Factor)
{
string PROCEDURE = "p_SelectDC_Customer_Resigned_Activity_CusInfo_Meta";
SqlParameter[] parameters = new SqlParameter[2];
parameters[0] = new SqlParameter();
parameters[0].ParameterName = "@CustomerID";
parameters[0].Value = CustomerID;
parameters[1] = new SqlParameter();
parameters[1].ParameterName = "@Factor";
parameters[1].Value = Factor;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DC_Customer_Resigned_Activity_CusInfo_Meta
{
CustomerID = row["CustomerID"].ToString(),
Factor = row["Factor"].ToString(),
Value = row["Value"].ToString(),
RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString()
}).ToList().FirstOrDefault());
}
public static Deca_RT_Type GetDeca_RT_Type(string TypeID)
{
string PROCEDURE = "p_SelectDeca_RT_Type";
SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = new SqlParameter();
parameters[0].ParameterName = "@TypeID";
parameters[0].Value = TypeID;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new Deca_RT_Type
{
TypeID = row["TypeID"].ToString(),
Name = row["Name"].ToString(),
Description = row["Description"].ToString(),
Category = row["Category"].ToString(),
Owner = row["Owner"].ToString(),
ResponeTime = Convert.ToInt32(row["ResponeTime"].ToString()),
ResolveTime = Convert.ToInt32(row["ResolveTime"].ToString()),
ClosedTime = Convert.ToInt32(row["ClosedTime"].ToString()),
QueueList = row["QueueList"].ToString(),
RequireCustomerID = row["RequireCustomerID"].ToString(),
RequireOrderID = row["RequireOrderID"].ToString(),
Status = row["Status"].ToString(),
Priority = Convert.ToInt32(row["Priority"].ToString()),
RowID = Convert.ToInt32(row["RowID"].ToString()),
RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString()
}).ToList().FirstOrDefault());
}
public static List <DC_Tele_Hint_Meta> GetDC_Tele_Hint_Metas(string whereCondition, string orderByExpression)
{
string PROCEDURE = "p_SelectDC_Tele_Hint_MetasDynamic";
SqlParameter[] parameters = new SqlParameter[2];
int i = 0;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@WhereCondition";
parameters[i].Value = whereCondition;
i++;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@OrderByExpression";
parameters[i].Value = orderByExpression;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, System.Data.CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DC_Tele_Hint_Meta
{
ArticleID = row["ArticleID"].ToString(),
Factor = row["Factor"].ToString(),
Value = row["Value"].ToString(),
Active = Convert.ToBoolean(row["Active"].ToString()),
RowID = Convert.ToInt16(row["RowID"].ToString()),
RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString()
}).ToList());
}
//public static List<Deca_RT_Type> GetDeca_RT_Types(string whereCondition, string orderByExpression)
//{
// string PROCEDURE = "p_SelectDeca_RT_TypesDynamic";
// SqlParameter[] parameters = new SqlParameter[2];
// int i = 0;
// parameters[i] = new SqlParameter();
// parameters[i].ParameterName = "@WhereCondition";
// parameters[i].Value = whereCondition;
// i++;
// parameters[i] = new SqlParameter();
// parameters[i].ParameterName = "@OrderByExpression";
// parameters[i].Value = orderByExpression;
// DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
// return dt.AsEnumerable().Select(row => new Deca_RT_Type
// {
// TypeID = row["TypeID"].ToString(),
// Name = row["Name"].ToString(),
// Description = row["Description"].ToString(),
// Category = row["Category"].ToString(),
// Owner = row["Owner"].ToString(),
// Priority = row["Priority"].ToString(),
// Impact = row["Impact"].ToString(),
// ResponeTime = Convert.ToInt32(row["ResponeTime"].ToString()),
// ResolveTime = Convert.ToInt32(row["ResolveTime"].ToString()),
// ClosedTime = Convert.ToInt32(row["ClosedTime"].ToString()),
// QueueList = row["QueueList"].ToString(),
// Status = row["Status"].ToString(),
// RowID = Convert.ToInt32(row["RowID"].ToString()),
// RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
// RowCreatedUser = row["RowCreatedUser"].ToString(),
// RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
// RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString()
// }).ToList();
//}
public static List <Deca_RT_Type> GetAllDeca_RT_Types()
{
string PROCEDURE = "p_SelectDeca_RT_TypesAll";
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, null);
return(dt.AsEnumerable().Select(row => new Deca_RT_Type
{
TypeID = row["TypeID"].ToString(),
Name = row["Name"].ToString(),
Description = row["Description"].ToString(),
Category = row["Category"].ToString(),
Owner = row["Owner"].ToString(),
ResponeTime = Convert.ToInt32(row["ResponeTime"].ToString()),
ResolveTime = Convert.ToInt32(row["ResolveTime"].ToString()),
ClosedTime = Convert.ToInt32(row["ClosedTime"].ToString()),
QueueList = row["QueueList"].ToString(),
RequireCustomerID = row["RequireCustomerID"].ToString(),
RequireOrderID = row["RequireOrderID"].ToString(),
Status = row["Status"].ToString(),
Priority = Convert.ToInt32(row["Priority"].ToString()),
RowID = Convert.ToInt32(row["RowID"].ToString()),
RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString()
}).ToList());
}
public static List <DC_Customer_Resigned> GetDC_Customer_Resigneds(string whereCondition, string orderByExpression)
{
string PROCEDURE = "p_SelectDC_Customer_ResignedsDynamic";
SqlParameter[] parameters = new SqlParameter[2];
int i = 0;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@WhereCondition";
parameters[i].Value = whereCondition;
i++;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@OrderByExpression";
parameters[i].Value = orderByExpression;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DC_Customer_Resigned
{
KeyCustomerID = Helpers.Locdau.convertToUnSign3(row["CustomerID"].ToString()),
CustomerID = row["CustomerID"].ToString(),
ResignedDate = Convert.ToDateTime(row["ResignedDate"].ToString()),
InformedDate = Convert.ToDateTime(row["InformedDate"].ToString()),
ResignedReason = row["ResignedReason"].ToString(),
Note = row["Note"].ToString(),
Active = Convert.ToBoolean(row["Active"].ToString()),
RowID = Convert.ToInt32(row["RowID"].ToString()),
RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString()
}).ToList());
}
public static CRM_Ticket_Follower GetCRM_Ticket_Follower(string TicketID, string UserName)
{
string PROCEDURE = "p_SelectCRM_Ticket_Follower";
SqlParameter[] parameters = new SqlParameter[2];
parameters[0] = new SqlParameter();
parameters[0].ParameterName = "@TicketID";
parameters[0].Value = TicketID;
parameters[1] = new SqlParameter();
parameters[1].ParameterName = "@UserID";
parameters[1].Value = UserName;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new CRM_Ticket_Follower
{
TicketID = row["TicketID"].ToString(),
UserName = row["UserID"].ToString(),
RowID = Convert.ToInt32(row["RowID"].ToString()),
RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString()
}).ToList().FirstOrDefault());
}
public static List <CRM_Ticket_Type> GetAllCRM_Ticket_Types_Category(string categoryid)
{
string PROCEDURE = "p_SelectCRM_Ticket_TypeAll_Category";
SqlParameter[] parameters = new SqlParameter[1];
int i = 0;
parameters[i] = new SqlParameter();
parameters[i].ParameterName = "@categoryid";
parameters[i].Value = categoryid;
i++;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, CommandType.StoredProcedure, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new CRM_Ticket_Type
{
TypeID = row["TypeID"].ToString(),
Name = row["Name"].ToString(),
Description = row["Description"].ToString(),
Category = row["Category"].ToString(),
Owner = row["Owner"].ToString(),
ResponeTime = Convert.ToInt32(row["ResponeTime"].ToString()),
ResolveTime = Convert.ToInt32(row["ResolveTime"].ToString()),
ClosedTime = Convert.ToInt32(row["ClosedTime"].ToString()),
QueueList = row["QueueList"].ToString(),
RequireCustomerID = row["RequireCustomerID"].ToString(),
RequireOrderID = row["RequireOrderID"].ToString(),
Status = row["Status"].ToString(),
Priority = Convert.ToInt32(row["Priority"].ToString()),
RowID = Convert.ToInt32(row["RowID"].ToString()),
RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString()
}).ToList());
}
//public static List<DC_Ticket_Holiday> GetDC_Ticket_Holidays(string whereCondition, string orderByExpression)
//{
// string PROCEDURE = "p_SelectDC_Ticket_HolidaysDynamic";
// SqlParameter[] parameters = new SqlParameter[2];
// int i = 0;
// parameters[i] = new SqlParameter();
// parameters[i].ParameterName = "@WhereCondition";
// parameters[i].Value = whereCondition;
// i++;
// parameters[i] = new SqlParameter();
// parameters[i].ParameterName = "@OrderByExpression";
// parameters[i].Value = orderByExpression;
// DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, System.Data.CommandType.Text, PROCEDURE, parameters);
// return dt.AsEnumerable().Select(row => new DC_Ticket_Holiday
// {
// HolidayID = row["HolidayID"].ToString(),
// Name = row["Name"].ToString(),
// Description = row["Description"].ToString(),
// Note = row["Note"].ToString(),
// StartDate = Convert.ToDateTime(row["StartDate"].ToString()),
// EndDate = Convert.ToDateTime(row["EndDate"].ToString()),
// Department = row["Department"].ToString(),
// Status = Convert.ToBoolean(row["Status"].ToString()),
// RowID = Convert.ToInt32(row["RowID"].ToString()),
// RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
// RowCreatedUser = row["RowCreatedUser"].ToString(),
// RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
// RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString()
// }).ToList();
//}
//get holiday later than CreateAtDate
public static List <DC_Ticket_Holiday> GetDC_Ticket_Holiday_ByDate(DateTime CreateAtDate)
{
string PROCEDURE = "p_SelectDC_Ticket_Holiday_ByDate";
SqlParameter[] parameters = new SqlParameter[1];
parameters[0] = new SqlParameter();
parameters[0].ParameterName = "@CreateAtDate";
parameters[0].Value = CreateAtDate;
DataTable dt = SqlHelperAsync.ExecuteDataTable(Constants.AllConstants().CONNECTION_STRING, System.Data.CommandType.Text, PROCEDURE, parameters);
return(dt.AsEnumerable().Select(row => new DC_Ticket_Holiday
{
HolidayID = row["HolidayID"].ToString(),
Name = row["Name"].ToString(),
Description = row["Description"].ToString(),
Note = row["Note"].ToString(),
StartDate = Convert.ToDateTime(row["StartDate"].ToString()),
EndDate = Convert.ToDateTime(row["EndDate"].ToString()),
Department = row["Department"].ToString(),
Status = row["Status"].ToString(),
RowID = Convert.ToInt32(row["RowID"].ToString()),
RowCreatedTime = Convert.ToDateTime(row["RowCreatedTime"].ToString()),
RowCreatedUser = row["RowCreatedUser"].ToString(),
RowLastUpdatedTime = Convert.ToDateTime(row["RowLastUpdatedTime"].ToString()),
RowLastUpdatedUser = row["RowLastUpdatedUser"].ToString()
}).ToList());
}