public List <DownloadDukcapilViewModel> getResultDukcapil(string pGuid)
{
List <DownloadDukcapilViewModel> listData = new List <DownloadDukcapilViewModel>();
bool isSucceed = true;
var connectionString = context.Database.GetDbConnection().ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
//Declare COnnection
var querySstring = @"
select TASK_ID,ID_NO,CUST_NAME,BIRTH_PLACE,BIRTH_DT from WISE_STAGING.dbo.T_MKT_POLO_UPLOAD where UPLOAD_STS=1 and QUEUE_UID='" + pGuid + "'";
SqlCommand command = new SqlCommand(querySstring, connection);
//open Connection
command.Connection.Open();
//PRoses Sp
SqlDataReader rd = command.ExecuteReader();
while (rd.Read())
{
DownloadDukcapilViewModel data = new DownloadDukcapilViewModel();
data.TaskID = rd[0].ToString();
data.NIK = rd[1].ToString();
data.CustomerName = rd[2].ToString();
data.TempatLahir = rd[3].ToString();
data.TglLahir = Convert.ToDateTime(rd[4].ToString()).ToString("dd/MM/yyyy");
listData.Add(data);
}
//Connection Close
command.Connection.Close();
}
return(listData);
}
public List <DownloadDukcapilViewModel> UploadData(DownloadDukcapilViewModel model, string guid)
{
List <DownloadDukcapilViewModel> ListData = new List <DownloadDukcapilViewModel>();
var connectionString = context.Database.GetDbConnection().ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
var querySstring = "spMKT_POLO_UPLOAD_STATUS_DUKCAPIL_TASK";
SqlCommand command = new SqlCommand(querySstring, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
//Define Query Parameter
command.Parameters.AddWithValue("@pTaskID", model.TaskID);
command.Parameters.AddWithValue("@pCustName", model.CustomerName);
command.Parameters.AddWithValue("@pIdNo", model.NIK);
command.Parameters.AddWithValue("@pBirthPlace", model.TempatLahir);
command.Parameters.AddWithValue("@pBirthDt", model.TglLahir);
command.Parameters.AddWithValue("@pQueueUid", guid);
command.Parameters.AddWithValue("@pEmpNo", model.EmpNo);
//open Connection
command.Connection.Open();
//PRoses Sp
SqlDataReader rd = command.ExecuteReader();
while (rd.Read())
{
}
//Connection Close
command.Connection.Close();
}
return(ListData);
}
public List <DownloadDukcapilViewModel> ListDownload(string pRegion,
string pFPName, string pBranchName, string pEmpPosition,
string pTaskID, string pStatProspek, string pAppID, string[] pPriorityLevelarr,
string pCustName, string pStatDukcapil, string pSdate, string pEdate,
string pSource, string pSourceData, string pEmpNo)
{
List <DownloadDukcapilViewModel> ListData = new List <DownloadDukcapilViewModel>();
var connectionString = context.Database.GetDbConnection().ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
var pPriorityLevel = string.Join(",", pPriorityLevelarr);
//Declare COnnection
var querySstring = "spMKT_POLO_TASK_INQUIRY_DUKCAPIL_ALL";
SqlCommand command = new SqlCommand(querySstring, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
//Define Query Parameter
command.Parameters.AddWithValue("@pRegion", pRegion);
command.Parameters.AddWithValue("@pFPName", pFPName);
command.Parameters.AddWithValue("@pBranchName", pBranchName);
command.Parameters.AddWithValue("@pEmpPosition", pEmpPosition);
command.Parameters.AddWithValue("@pTaskID", pTaskID);
command.Parameters.AddWithValue("@pStatProspek", pStatProspek);
command.Parameters.AddWithValue("@pAppID", pAppID);
command.Parameters.AddWithValue("@pPriorityLevel", pPriorityLevel);
command.Parameters.AddWithValue("@pCustName", pCustName);
command.Parameters.AddWithValue("@pStatDukcapil", pStatDukcapil);
command.Parameters.AddWithValue("@pSdate", pSdate);
command.Parameters.AddWithValue("@pEdate", pEdate);
command.Parameters.AddWithValue("@pSourceData", pSourceData);
command.Parameters.AddWithValue("@pEmpNo", pEmpNo);
//open Connection
command.Connection.Open();
//PRoses Sp
SqlDataReader rd = command.ExecuteReader();
while (rd.Read())
{
DownloadDukcapilViewModel data = new DownloadDukcapilViewModel();
data.TaskID = rd[0].ToString();
data.NIK = rd[1].ToString();
data.CustomerName = rd[2].ToString();
data.TempatLahir = rd[3].ToString();
data.TglLahir = rd[4].ToString();
ListData.Add(data);
}
//Connection Close
command.Connection.Close();
}
return(ListData);
}
public List <DownloadDukcapilViewModel> ListDownloadDetail(int pID, string pEmpNo)
{
List <DownloadDukcapilViewModel> ListData = new List <DownloadDukcapilViewModel>();
var connectionString = context.Database.GetDbConnection().ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
//Declare COnnection
var querySstring = "spMKT_POLO_TASK_INQUIRY_DUKCAPIL";
SqlCommand command = new SqlCommand(querySstring, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
//Define Query Parameter
command.Parameters.AddWithValue("@pID", pID);
command.Parameters.AddWithValue("@pEmpNo", pEmpNo);
//open Connection
command.Connection.Open();
//PRoses Sp
SqlDataReader rd = command.ExecuteReader();
while (rd.Read())
{
DownloadDukcapilViewModel data = new DownloadDukcapilViewModel();
data.TaskID = rd[0].ToString();
data.NIK = rd[1].ToString();
data.CustomerName = rd[2].ToString();
data.TempatLahir = rd[3].ToString();
data.TglLahir = rd[4].ToString();
ListData.Add(data);
}
//Connection Close
command.Connection.Close();
}
return(ListData);
}
public async Task <ActionResult> Upload(string empNo)
{
Proccessresult result = new Proccessresult();
string guid = System.Guid.NewGuid().ToString().ToUpper();
string done = string.Empty;
ChangeDukcapilProvider changeDukcapilProvider = new ChangeDukcapilProvider();
try
{
var files = Request.Form.Files;
bool isSucceed = true;
foreach (var uploadFile in files)
{
if (uploadFile != null)
{
Dictionary <string, string> names = new Dictionary <string, string>();
names.Add("labelName1", "Task ID");
names.Add("labelName2", "Nik Ktp");
names.Add("labelName3", "Customer Name");
names.Add("labelName4", "Tempat Lahir");
names.Add("labelName5", "Tanggal Lahir");
string sFileExtension = Path.GetExtension(uploadFile.FileName).ToLower();
if ((uploadFile != null) && !string.IsNullOrEmpty(uploadFile.FileName))
{
string fileName = uploadFile.FileName;
string fileExtension = System.IO.Path.GetExtension(fileName);
var stream = files[0].OpenReadStream();
IExcelDataReader excelReader;
if (fileExtension == ".xls")
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
DataSet dtTable = excelReader.AsDataSet();
var totalRows = dtTable.Tables[0].Rows.Count;
var maxColumn = dtTable.Tables[0].Columns.Count;
for (int j = 0; j <= 4; j++)
{
int i = j + 1;
var data1 = names["labelName" + i].ToString();
var data2 = dtTable.Tables[0].Rows[0][j].ToString();
if (data1 != data2)
{
result.isSucceed = false;
result.message = "Template format file yang diupload tidak sesuai ketentuan";
return(Json(result));
}
}
for (int i = 1; i < totalRows; i++)
{
DownloadDukcapilViewModel model = new DownloadDukcapilViewModel();
model.TaskID = dtTable.Tables[0].Rows[i][0].ToString();
model.NIK = dtTable.Tables[0].Rows[i][1].ToString();
model.CustomerName = dtTable.Tables[0].Rows[i][2].ToString();
model.TempatLahir = dtTable.Tables[0].Rows[i][3].ToString();
try
{
var locDate = dtTable.Tables[0].Rows[i][4].ToString();
model.TglLahir = Convert.ToDateTime(locDate).ToString("yyyy-MM-dd HH:mm:ss.mmm");
}
catch
{
model.TglLahir = dtTable.Tables[0].Rows[i][4].ToString();
}
model.EmpNo = empNo;
var data = changeDukcapilProvider.UploadData(model, guid);
}
//}
}
}
}
}
catch
{
result.isSucceed = true;
result.pguid = guid;
result.message = "Upload Error";
return(Json(result));
}
try
{
//Task sendData = Task.Run(async () => await changeDukcapilProvider.SendApiCekDukcapil(guid));
await changeDukcapilProvider.SendApiCekDukcapil(guid);
//await sendData;
done = await changeDukcapilProvider.getLoopDukcapil(guid);
if (done == "done")
{
await changeDukcapilProvider.SendApiToWiseMSS(guid);
}
}
catch
{
result.isSucceed = true;
result.pguid = guid;
result.message = "Upload Done";
return(Json(result));
}
result.isSucceed = true;
result.pguid = guid;
result.message = "Upload Done";
return(Json(result));
}
