public bool Accept(string voucherID, out string msg)
{
msg = "";
try
{
Console.WriteLine("DocRequisitionService--Accept " + DateTime.Now.ToString());
Console.WriteLine(voucherID);
DataTable dt = gate.SelectDataSet(@"SELECT * FROM Requisition where VoucherID=@VoucherID", new object[] { voucherID }).Tables[0];
if (dt.Rows.Count <= 0)
{
Console.WriteLine("DraftID");
dt = gate.SelectDataSet(@"SELECT TOP 1 SampleName,r.LOT_NO FROM Requisition r INNER JOIN ReqInDraft rd ON r.VoucherID=rd.VoucherID WHERE rd.DraftID=@VoucherID", new object[] { voucherID }).Tables[0];
}
if (dt.Rows.Count > 0)
{
return(Accept(dt, voucherID));
}
}
catch (Exception e)
{
msg = e.Message;
log.Error(e);
Console.WriteLine(e.Message);
}
return(false);
}
public IHttpActionResult GetMaterial(string AB)
{
try
{
DataTable dt = gate.SelectDataSet(@"SELECT * FROM MATERIAL WHERE AB=@AB", new object[] { AB }).Tables[0];
return(Ok(dt));
}
catch (Exception ex)
{
throw;
}
}
public string IsExsitGrade(string sampleName, string lotNo, string grades)
{
DataTable dt = acQC.SelectDataSet(@"SELECT TOP 1 ID FROM GradeMaterial WHERE SampleName = @SampleName
AND LOT_NO = @LOT_NO and Grades=@grades and Status='N'",
new object[] { sampleName, lotNo, grades }).Tables[0];
if (dt.Rows.Count > 0)
{
return(dt.Rows[0]["ID"].ToString());;
}
return("");
}
public IHttpActionResult getDeviceData()
{
try
{
string sqlQuery = @"SELECT * FROM TimeKeepperDevice";
DataTable dt = gateHRM.SelectDataSet(sqlQuery, new object[] { }).Tables[0];
return(Ok(dt));
}
catch (Exception ex)
{
return(BadRequest(ex.Message + "-" + ex.StackTrace));
}
}
public IHttpActionResult getActiveNotify()
{
try
{
var dtActive = oAC.SelectDataSet("SELECT NotifyName FROM ContractorNotify WHERE Status = @Status"
, new object[] { "Active" }).Tables[0];
return(Ok(_helper.ConvertJson(dtActive)));
}
catch (Exception e)
{
Loger.Error(e);
return(BadRequest(e.Message + e.StackTrace));
}
}
public Profile SelectProfile(string voucherID, string propertyName)
{
DataTable dt = gate.SelectDataSet(@"SELECT* FROM [Profile] WHERE VoucherID = @VoucherID and PropertyName=@PropertyName",
new object[] { voucherID, propertyName }).Tables[0];
var item = 0;
if (dt.Rows.Count > 0)
{
item = int.Parse(dt.Rows[0]["Item"].ToString());
}
return(db.Select <Profile>(new Profile {
VoucherID = voucherID, Item = item
}));
}
public DataTable QueryPlans(string sampleName, string lot_no, string line, DateTime b, DateTime e)
{
try
{
if (string.IsNullOrEmpty(lot_no.Trim()))
{
lot_no = "%";
}
if (string.IsNullOrEmpty(line.Trim()))
{
line = "%";
}
b = b.Date;
e = e.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
DataTable dt = new DataTable();
string[] parameters = new string[] { "UserID", "SampleName", "LOT_NO", "LINE", "B", "E" };
// Step 1
dt.Merge(acQC.DbHelper.ExecuteStoredProcedure("Q_GetPlansStep1", parameters, new object[] {
"", sampleName, lot_no, line, b, e
}).Tables[0]);
// Step 2
DataTable dt2 = acQC.DbHelper.ExecuteStoredProcedure("Q_GetPlansStep2", parameters, new object[] {
"", sampleName, lot_no, line, b, e
}).Tables[0];
if (dt2 != null && dt2.Rows.Count != 0)
{
for (int i = 0; i < dt2.Rows.Count; i++)
{
dt.Columns.Add(dt2.Rows[i]["PropertyName"].ToString(), typeof(string));
}
}
//Step 3
DataTable dt3 = acQC.DbHelper.ExecuteStoredProcedure("Q_GetPlansStep3", parameters, new object[] {
null, sampleName, lot_no, line, b, e
}).Tables[0];
if (dt3 != null && dt3.Rows.Count != 0)
{
for (int i = 0; i < dt3.Rows.Count; i++)
{
string voucherId = dt3.Rows[i]["VoucherID"].ToString();
string propertyName = dt3.Rows[i]["PropertyName"].ToString();
string overrange = dt3.Rows[i]["OverRange"].ToString();
if (dt.Rows.Count != 0)
{
foreach (DataRow iRow in dt.Rows)
{
if ((string)iRow["VoucherID"] == voucherId)
{
foreach (DataColumn dc in dt.Columns)
{
if (dc.ColumnName == propertyName)
{
iRow[propertyName] = dt3.Rows[i]["Result"].ToString();
if ((bool)dt3.Rows[i]["OverRange"])
{
if (!iRow[propertyName].ToString().Contains("$"))
{
iRow[propertyName] = "$" + iRow[propertyName];
}
if (!iRow["Grade"].ToString().Contains("#"))
{
iRow["Grade"] = "#" + iRow["Grade"];
}
}
break;
}
}
}
}
}
}
}
if (dt != null && dt.Rows.Count != 0)
{
string sql4 = @"SELECT
p.VoucherID,p.filePath
FROM Plans p
WHERE ISNULL(p.filePath,'') <> ''
AND p.[State] NOT IN ('X','U')
AND p.SampleName = @SampleName
AND p.LOT_NO LIKE @LOT_NO
AND p.LINE LIKE @LINE
AND p.ProdDate >= @B
AND p.ProdDate <= @E
";
string filePath = string.Empty;
DataTable dt4 = acQC.SelectDataSet(sql4, new object[] { sampleName, lot_no, line, b, e }).Tables[0];
if (dt4 != null && dt4.Rows.Count != 0)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (dt.Columns[i].ColumnName == "Melting point")
{
foreach (DataRow row in dt.Rows)
{
DataRow[] drs = dt4.Select("VoucherID = '" + row["VoucherID"] + "'", "VoucherID ASC");
if (drs != null && drs.Length != 0)
{
filePath = drs.ToList().FirstOrDefault()["filePath"].ToString();
//string fileName = filePath.Split('/')[filePath.Split('/').Length - 1];
string html = string.Format(" <a href='{0}' target='_blank' download><span class='glyphicon glyphicon-file'></span></a>{1}", filePath, row["Melting point"].ToString());
row["Melting point"] = html.Replace("'", "\"");
}
}
}
}
}
}
dt.AcceptChanges();
dt.Columns.Remove("VoucherID");
dt.Columns.Remove("SampleName");
//dt.Columns.Remove("LOT_NO");
string tmpStr = "";
foreach (DataRow LotRow in dt.Rows)
{
if ((string)LotRow["LOT_NO"] == tmpStr)
{
LotRow["LOT_NO"] = "";
}
else
{
tmpStr = LotRow["LOT_NO"].ToString();
}
}
return(dt);
}
catch (Exception ex)
{
log.Error(e);
throw new Exception(ex.Message);
}
}
/// GET QUERY PLAN LIMS
/// <param name="sampleName"></param>
/// <param name="lot_no"></param>
/// <param name="line"></param>
/// <param name="b"></param>
/// <param name="e"></param>
/// <returns></returns>
public DataTable QueryPlans(string sampleName, string lot_no, string line, DateTime b, DateTime e)
{
try
{
if (string.IsNullOrEmpty(lot_no.Trim()))
{
lot_no = "%";
}
if (string.IsNullOrEmpty(line.Trim()))
{
line = "%";
}
b = b.Date;
e = e.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
string[] parameters = new string[] { "UserID", "SampleName", "LOT_NO", "LINE", "B", "E" };
DataTable dt = acQC.DbHelper.ExecuteStoredProcedure("Q_GetPlans",
parameters, new object[] { "", sampleName, lot_no, line, b, e }).Tables[0];
string str1, str2;
dt.Columns.Remove("VoucherID");
dt.Columns.Remove("SampleName");
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 4; j < dt.Columns.Count; j++)
{
var grade = dt.Rows[i]["Grade"].ToString();
if (dt.Rows[i][j].ToString().Contains("$"))
{
dt.Rows[i]["Grade"] = "#" + grade.Replace("^", "");
break;
}
if (dt.Rows[i][j].ToString().Contains("~"))
{
if (!grade.Contains("^"))
{
dt.Rows[i]["Grade"] = "^" + grade;
}
}
}
if (i == 0)
{
continue;
}
str1 = dt.Rows[i - 1]["LINE"].ToString();
str2 = dt.Rows[i]["LINE"].ToString();
if (str1 != str2 && str1 != "**")
{
DataRow myRow = dt.NewRow();
myRow["LOT_NO"] = "xxx";
dt.Rows.InsertAt(myRow, i);
i++;
}
}
#region Unknow function
if (dt != null && dt.Rows.Count != 0)
{
string sql4 = @"SELECT p.VoucherID,p.filePath
FROM Plans p
WHERE ISNULL(p.filePath,'') <> ''
AND p.[State] = '2'
AND p.SampleName = @SampleName
AND p.LOT_NO LIKE '%'+@LOT_NO+'%'
AND p.LINE LIKE '%'+@LINE+'%'
AND p.ProdDate >= @B
AND p.ProdDate <= @E
";
string filePath = string.Empty;
DataTable dt4 = acQC.SelectDataSet(sql4, new object[] { sampleName, lot_no, line, b, e }).Tables[0];
if (dt4 != null && dt4.Rows.Count != 0)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (dt.Columns[i].ColumnName == "Melting point")
{
foreach (DataRow row in dt.Rows)
{
DataRow[] drs = dt4.Select("VoucherID = '" + row["VoucherID"] + "'", "VoucherID ASC");
if (drs != null && drs.Length != 0)
{
filePath = drs.ToList().FirstOrDefault()["filePath"].ToString();
//string fileName = filePath.Split('/')[filePath.Split('/').Length - 1];
string html = string.Format(" <a href='{0}' target='_blank' download><span class='glyphicon glyphicon-file'></span></a>{1}", filePath, row["Melting point"].ToString());
row["Melting point"] = html.Replace("'", "\"");
}
}
}
}
}
}
#endregion
dt.AcceptChanges();
return(dt);
}
catch (Exception ex)
{
log.Error(e);
throw new Exception(ex.Message);
}
}
public int GetDataFromVnResourceService()
{
try
{
DbTransaction tran = gateFEPVOA.BeginTransaction();
string EmployeeID = "";
var UpdateFrom = DateTime.Now.AddDays(-7).ToString("yyyy-MM-dd");
var UpdateTO = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd");
HRMSInterfaceDAL.Hr_SuZhou_Service.WebServiceHR _webService = new HRMSInterfaceDAL.Hr_SuZhou_Service.WebServiceHR();
List <HRMSInterfaceDAL.Hr_SuZhou_Service.HREmpInfoBean> _profiles = _webService.GetListProfile(null, null, UpdateFrom, UpdateTO, null, "IT", "fepv123").ToList();
//string _JsonProfiles = JsonConvert.SerializeObject(_listProfileData);
//DataTable _dtProfiles = JsonConvert.DeserializeObject<DataTable>(_JsonProfiles);
foreach (HRMSInterfaceDAL.Hr_SuZhou_Service.HREmpInfoBean _profile in _profiles)
{
if (!string.IsNullOrEmpty(_profile.WorkNo))
{
if (_profile.WorkNo.Length > 6)
{
bool isExists = _profile.WorkNo.Contains("NN.");
if (!isExists)
{
EmployeeID = _profile.WorkNo.Substring(1, _profile.WorkNo.Length - 1);
}
else
{
EmployeeID = _profile.WorkNo;
}
}
else
{
EmployeeID = _profile.WorkNo;
}
string[] formats = { "dd/MM/yyyy", "dd-MMM-yyyy", "yyyy-MM-dd",
"dd-MM-yyyy", "M/d/yyyy", "M/dd/yyyy", "dd MMM yyyy" };
string birthdayAfterConverted = DateTime.ParseExact(_profile.Birthday.Substring(0, _profile.Birthday.IndexOf(' ')).Trim(), formats, CultureInfo.InvariantCulture, DateTimeStyles.None).ToString("yyyy-MM-dd");
FEPV_Employees_Data _FEPVED = new FEPV_Employees_Data();
_FEPVED.EmployeeID = "FEPV" + EmployeeID;
_FEPVED.EmployeeName = string.IsNullOrEmpty(_profile.NameVN.Trim()) ? (string.IsNullOrEmpty(_profile.NameEN) ? _profile.NameCN : _profile.NameEN) : _profile.NameVN;
_FEPVED.EmployeeName_Eng = string.IsNullOrEmpty(_profile.NameEN.Trim()) ? (string.IsNullOrEmpty(_profile.NameVN) ? _profile.NameCN : _profile.NameVN) : _profile.NameEN;
_FEPVED.CostCenter = _profile.CostCentreCode;
_FEPVED.CardNo = _profile.CardNo;
_FEPVED.State = Convert.ToBoolean(_profile.Status == "X" ? true : false);
_FEPVED.DepartmentName_CN = _profile.CostCentreNameCN;
_FEPVED.DepartmentName_EN = _profile.CostCentreName;
_FEPVED.PositionName = _profile.PositionName;
_FEPVED.JoinDate = Convert.ToDateTime(_profile.JoinDate);
_FEPVED.Gender = _profile.SexType;
_FEPVED.CompanyShortName = _profile.CompanyShortName;
_FEPVED.CompanyFullName = _profile.CompanyName;
_FEPVED.CompanyCode = _profile.CompanyCode;
_FEPVED.Birthday = Convert.ToDateTime(birthdayAfterConverted);
_FEPVED.LastUpdateDate = Convert.ToDateTime(string.IsNullOrEmpty(_profile.DateUpdate) ? DateTime.Now : Convert.ToDateTime(_profile.DateUpdate));
_FEPVED.Email = _profile.Email;
_FEPVED.Workplace = _profile.WorkPlaceName;
_FEPVED.NumberOfUpdate += 1;
string sql = @"SELECT * FROM FEPV_Employees_Data WHERE EmployeeID=@EmployeeID ";
DataTable dt = gateFEPVOA.SelectDataSet(sql, new object[] { _FEPVED.EmployeeID }).Tables[0];
if (dt.Rows.Count >= 1)
{
UpdateEmployee(_FEPVED);
}
else
{
InsertEmployee(_FEPVED);
}
}
}
return(1);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message + ex.StackTrace);
return(2);
}
}
public IHttpActionResult GetCompany()
{
var data = oAC.SelectDataSet("SELECT * FROM Company", null).Tables[0];
return(Ok(data));
}