public int CreateDataWorking(DataWorking newDataWorking)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO tbDataWorking(DWID,psnCode,dataDate,datatxt)");
sb.Append(" VALUES (@DWID,@psnCode,@dataDate,@datatxt)");
string sqlsave;
sqlsave = sb.ToString();
comm = new SqlCommand();
comm.Connection = conn;
comm.Transaction = tr;
comm.CommandText = sqlsave;
comm.Parameters.Clear();
comm.Parameters.Add("@DWID", SqlDbType.NVarChar).Value = newDataWorking.DWID;
comm.Parameters.Add("@psnCode", SqlDbType.NVarChar).Value = newDataWorking.psnCode;
comm.Parameters.Add("@dataDate", SqlDbType.NVarChar).Value = newDataWorking.dataDate;
comm.Parameters.Add("@datatxt", SqlDbType.NVarChar).Value = newDataWorking.dtText;
comm.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
conn.Close();
return result;
throw ex;
}
finally
{
conn.Close();
}
return result;
}
private List<DataWorking> loadDataWorking()
{
List<DataWorking> dataWorkings = new List<DataWorking>();
DataWorking dataWorking = null;
string fileName = txtfile.Text.Trim();
Dtime dtime = null;
if (File.Exists(fileName))
{
using (System.IO.StreamReader sr = new System.IO.StreamReader(fileName))
{
StreamReader file = null;
String line;
try
{
file = new StreamReader(fileName, Encoding.GetEncoding("windows-874"));
int i = 0;
while ((line = sr.ReadLine()) != null)
{
if (i != 0)
{
dataWorking = new DataWorking();
int year = Convert.ToInt32(DateTime.Now.Year.ToString("0000"));
string _dNow = DateTime.Now.Day.ToString("00") + "" + DateTime.Now.Month.ToString("00") + "" + year.ToString() + DateTime.Now.ToShortTimeString();
char[] splitchar = { ' ' };
string[] words = line.Split(splitchar);
dataWorking.DWID = "D" + _dNow;
dataWorking.psnCode = words[0];
dataWorking.dataDate = words[13];
int count = 0;
List<Dtime> dtimes = new List<Dtime>();
for (count = 0; count <= words.Length - 1; count++)
{
if (count > 13)
{
if (words[count] != "")
{
dtime = new Dtime();
dtime.DWID = _dNow;
dtime.ddTime = words[count];
dtimes.Add(dtime);
if (words[count] == "")
{
break;
}
}
}
}
string txt = "";
int ic = 0;
foreach (Dtime d in dtimes) {
if (ic == dtimes.Count - 1)
{
txt += d.ddTime;
}
else {
txt += d.ddTime + ",";
}
ic++;
}
dataWorking.dtText = txt;
dataWorking.dtimeds = dtimes;
dataWorkings.Add(dataWorking);
}
i++;
}
}
catch (Exception ext)
{
throw ext;
}
}
}
return dataWorkings;
}
public DataWorking getDataWorking(string _psnCode,string _date)
{
DataWorking dataWorking = null;
try
{
conn = db.openConn();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append(" SELECT ID,DWID,psnCode,dataDate,datatxt FROM tbDataWorking");
sb.Append(" WHERE psnCode='"+_psnCode+"' AND dataDate='"+ _date +"'");
string sql;
sql = sb.ToString();
comm = new SqlCommand();
comm.CommandText = sql;
comm.CommandType = CommandType.Text;
comm.Connection = conn;
dr = comm.ExecuteReader();
if (dr.HasRows)
{
DataTable dt = new DataTable();
dt.Load(dr);
foreach (DataRow drw in dt.Rows)
{
dataWorking = new DataWorking();
dataWorking.DWID = drw["DWID"].ToString();
dataWorking.psnCode = drw["psnCode"].ToString();
dataWorking.dataDate = drw["dataDate"].ToString();
dataWorking.dtText = drw["datatxt"].ToString();
}
}
dr.Close();
}
catch (Exception ex)
{
dr.Close();
conn.Close();
return null;
throw ex;
}
finally
{
conn.Close();
}
return dataWorking;
}
public int UpdateDataWorking(DataWorking _dataWorking)
{
int result = -1;
try
{
conn = db.openConn();
tr = conn.BeginTransaction();
sb = new StringBuilder();
sb.Remove(0, sb.Length);
sb.Append(" UPDATE tbDataWorking ");
sb.Append(" SET datatxt='" + _dataWorking.dtText + "'");
sb.Append(" WHERE (psnCode='" + _dataWorking.psnCode + "')");
sb.Append(" AND (dataDate='"+_dataWorking.dataDate+"')");
string sqlUpdate;
sqlUpdate = sb.ToString();
comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = sqlUpdate;
comm.Transaction = tr;
comm.Parameters.Clear();
comm.ExecuteNonQuery();
tr.Commit();
result = 1;
}
catch (Exception ex)
{
tr.Rollback();
conn.Close();
return result;
throw ex;
}
finally
{
conn.Close();
}
return result;
}
