public DataTable getData_ReportJob(string job_id)
{
DataTable dt = null;
string sql = "Select * From vw_JobTrailer where job_id='" + job_id + "' and show_=1";
Class.clsDB db = new Class.clsDB();
dt = db.ExecuteDataTable(sql);
db.Close();
return(dt);
}
public DataTable getData_ReportJobTimestamp(string job_id)
{
DataTable dt = null;
string sql = "Select job_id,job_name,job_date,time(timestamp1) as timestamp1,";
sql += "time(timestamp2) as timestamp2,time(timestamp3) as timestamp3,time(timestamp4) as timestamp4";
sql += " From job_trailer where job_id='" + job_id + "' and show_=1";
Class.clsDB db = new Class.clsDB();
dt = db.ExecuteDataTable(sql);
db.Close();
return(dt);
}
public int Update_AttachFile(Model.Criteria.Document criteria, int val)
{
string sql;
sql = "Update document SET attach_file_name = " + val;
sql += " WHERE doc_id='" + criteria.doc_id + "'";
Class.clsDB db = new Class.clsDB();
int ret;
ret = db.ExecuteNonQuery(sql);
db.Close();
return(ret);
}
public int Update_sign_review_date(Model.Criteria.Document criteria)
{
string sql;
sql = "Update document SET sign_prepare_date = '" + criteria.sign_prepare_date + "',step2=1 ";
sql += " WHERE doc_id='" + criteria.doc_id + "'";
Class.clsDB db = new Class.clsDB();
int ret;
ret = db.ExecuteNonQuery(sql);
db.Close();
return(ret);
}
public int Update_sign_approve_problem(Model.Criteria.Document criteria)
{
string sql;
sql = "Update document SET approve_problem = '" + criteria.approve_problem + "',step4=1 ";
sql += " WHERE doc_id='" + criteria.doc_id + "'";
Class.clsDB db = new Class.clsDB();
int ret;
ret = db.ExecuteNonQuery(sql);
db.Close();
return(ret);
}
public int Update_send_mail_Review(Model.Criteria.Document criteria)
{
string sql;
sql = "Update document SET send_mail_approve_date = '" + criteria.send_mail_approve_date + "',step3=1,secure_approve='" + criteria.secure_approve + "'";
sql += " WHERE doc_id='" + criteria.doc_id + "'";
Class.clsDB db = new Class.clsDB();
int ret;
ret = db.ExecuteNonQuery(sql);
db.Close();
return(ret);
}
public int Update_Upload_date(Model.Criteria.Document criteria)
{
string sql;
sql = "Update document SET upload_date = '" + criteria.upload_date + "',";
sql += "step1='1',page_count = " + criteria.page_count + ",paper_type = '" + criteria.paper_type + "'";
sql += " WHERE doc_id='" + criteria.doc_id + "'";
Class.clsDB db = new Class.clsDB();
int ret;
ret = db.ExecuteNonQuery(sql);
db.Close();
return(ret);
}
public int Delete_Document(Model.Criteria.Document criteria)
{
string sql;
sql = "Delete From document ";
sql += " WHERE doc_id='" + criteria.doc_id + "'";
Class.clsDB db = new Class.clsDB();
int ret;
ret = db.ExecuteNonQuery(sql);
db.Close();
return(ret);
}
public int Insert_Data(MODEL.Data criteria)
{
try
{
criteria.create_date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
criteria.in_time = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
Class.clsDB db = new Class.clsDB();
string sql;
sql = "Insert into vm_visitor ( ";
sql += "vm_id,";
sql += "name,";
sql += "lastname,";
sql += "birthday,";
sql += "address ,";
sql += "company ,";
sql += "contact_person ,";
sql += "dept ,";
sql += "id_card ,";
sql += "license_plate ,";
sql += "create_by ,";
sql += "in_time ,";
sql += "create_date) VALUES(";
sql += "'" + criteria.vm_id + "',";
sql += "'" + criteria.name + "',";
sql += "'" + criteria.lastname + "',";
sql += "'" + criteria.birth + "',";
sql += "'" + criteria.address + "',";
sql += "'" + criteria.company + "',";
sql += "'" + criteria.contact_person + "',";
sql += "'" + criteria.dept + "',";
sql += "'" + criteria.id_card + "',";
sql += "'" + criteria.license_plate + "',";
sql += "'" + criteria.create_by + "',";
sql += "'" + criteria.in_time + "',";
sql += "'" + criteria.create_date + "')";
int ret;
ret = db.ExecuteNonQuery(sql);
db.Close();
return(ret);
}
catch (Exception ex)
{
return(0);
}
}
public bool InsertSupplier(List <Model.Supplier> lstSupp)
{
int k;
k = lstSupp.Count();
for (int i = 0; i < k; i++)
{
string sql = null;
Model.Supplier m_supp = new Model.Supplier();
m_supp = (Model.Supplier)lstSupp[i];
sql += "Insert into supplier(supp_company,";
sql += "supp_code,";
sql += "supp_name,";
sql += "supp_short_name,";
sql += "supp_address_1,";
sql += "supp_address_2,";
sql += "supp_contact_person,";
sql += "supp_contact_position) Values('";
sql += m_supp.supp_company + "','";
sql += m_supp.supp_code + "','";
sql += m_supp.supp_name + "','";
sql += m_supp.supp_short_name + "','";
sql += m_supp.supp_address_1 + "','";
sql += m_supp.supp_address_2 + "','";
sql += m_supp.supp_contact_person + "','";
sql += m_supp.supp_contact_position + "')";
try
{
Class.clsDB db = new Class.clsDB();
int ret;
ret = db.ExecuteNonQuery(sql);
db.Close();
}
catch
{
}
}
return(true);
}
public int Update_stamp2(string appointed_time, string job_id)
{
try
{
Class.clsDB db = new Class.clsDB();
string sql;
sql = "Update job_trailer SET timestamp2='" + appointed_time + "' Where job_id='" + job_id + "'";
int ret;
ret = db.ExecuteNonQuery(sql);
db.Close();
return(ret);
}
catch (Exception ex)
{
return(0);
}
}
public List <Model.Account> getEmail()
{
DataSet ds = new DataSet();
String sql;
sql = "Select * From account";
Class.clsDB db = new Class.clsDB();
ds = db.ExecuteDataSet(sql);
return(ds.Tables[0].AsEnumerable().Select(s => new Model.Account
{
Title = s.Field <string>("Title"),
Name = s.Field <string>("Name"),
Surname = s.Field <string>("Surname"),
Email = s.Field <string>("Email"),
}).ToList());
}
public List <MODEL.jobDetail> getJobDetail(MODEL.Criteria.jobDetail criteria)
{
DataSet ds = new DataSet();
String sql;
sql = "Select * From job_detail where show_=1";
Class.clsDB db = new Class.clsDB();
ds = db.ExecuteDataSet(sql);
db.Close();
return(ds.Tables[0].AsEnumerable().Select(s => new MODEL.jobDetail
{
job_id = s.Field <string>("job_id"),
//place_get_job = s.Field<string>("place_get_job"),
//container_type = s.Field<string>("container_type"),
//container_dim = s.Field<string>("container_dim"),
}).ToList());
}
public int Update_stamp4(string job_id)
{
try
{
Class.clsDB db = new Class.clsDB();
string sql;
string timestamp1 = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
sql = "Update job_trailer SET timestamp4='" + timestamp1 + "' Where job_id='" + job_id + "'";
int ret;
ret = db.ExecuteNonQuery(sql);
db.Close();
return(ret);
}
catch (Exception ex)
{
return(0);
}
}
public int Insert_JobDetail(MODEL.Criteria.jobDetail criteria)
{
try
{
Class.clsDB db = new Class.clsDB();
string sql;
sql = "Insert into job_trailer_detail ( ";
sql += "job_id,";
sql += "job_name,";
sql += "place_get_job ,";
sql += "container_type,";
sql += "container_dim,";
sql += "cust_dest,";
sql += "code_name ,";
sql += "appointed_time,";
sql += "doc_no,";
sql += "place_send_job,";
sql += "send_company,";
sql += "place_type,";
sql += "remark) VALUES('";
sql += criteria.job_id + "',";
sql += "'" + criteria.job_name + "',";
sql += "'" + criteria.place_get_job + "',";
sql += "'" + criteria.container_type + "',";
sql += "'" + criteria.container_dim + "',";
sql += "'" + criteria.cust_dest + "',";
sql += "'" + criteria.code_name + "',";
sql += "'" + criteria.appointed_time + "',";
sql += "'" + criteria.doc_no + "',";
sql += "'" + criteria.place_send_job + "',";
sql += "'" + criteria.send_company + "',";
sql += "'" + criteria.place_type + "',";
sql += "'" + criteria.remark + "')";
int ret;
ret = db.ExecuteNonQuery(sql);
db.Close();
return(ret);
}
catch (Exception ex)
{
return(0);
}
}
public string Get_Paper_type(string doc_id)
{
Class.clsDB db = new Class.clsDB();
string sql;
sql = " Select paper_type From document where doc_id=" + doc_id;
DataTable dt;
dt = db.ExecuteDataTable(sql);
db.Close();
if (dt.Rows.Count > 0)
{
return(dt.Rows[0][0].ToString());
}
else
{
return("");
}
}
public string Get_Signature_By_Email(string email)
{
Class.clsDB db = new Class.clsDB();
string sql;
sql = "select signature From account WHERE email='" + email + "'";
DataTable dt;
dt = db.ExecuteDataTable(sql);
db.Close();
if (dt.Rows.Count > 0)
{
return(dt.Rows[0][0].ToString());
}
else
{
return("Error");
}
}
public int Delete_Job(string job_id)
{
try
{
string nl = System.Environment.NewLine;
Class.clsDB db = new Class.clsDB();
string sql;
sql = "Update job_trailer SET show_ = 0 Where job_id='" + job_id + "';" + nl;
sql += "Update job_trailer_detail SET show_ = 0 Where job_id='" + job_id + "';";
int ret;
ret = db.ExecuteNonQuery(sql);
db.Close();
return(ret);
}
catch (Exception ex)
{
return(0);
}
}
public DataTable GetCustomer()
{
DataTable table;
try
{
DataSet set = new DataSet();
string strSQL = "Select * From supplier order by supp_short_name ";
Class.clsDB sdb1 = new Class.clsDB();
set = sdb1.ExecuteDataSet(strSQL);
sdb1.Close();
table = set.Tables[0];
}
catch (Exception exception1)
{
throw exception1;
}
return(table);
}
public DataTable ExecuteDatatable(string sql)
{
try
{
DataSet ds = new DataSet();
Class.clsDB db = new Class.clsDB();
ds = db.ExecuteDataSet(sql);
db.Close();
return(ds.Tables[0]);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
public DataTable getAccount_By_Email(Model.Criteria.M_AccountCriteria criteria)
{
try
{
DataSet ds = new DataSet();
String sql;
sql = "Select * From account where email='" + criteria.email + "'";
Class.clsDB db = new Class.clsDB();
ds = db.ExecuteDataSet(sql);
db.Close();
return(ds.Tables[0]);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
public DataTable getAccount_Level1()
{
try
{
DataSet ds = new DataSet();
String sql;
sql = "Select * From account where level = '1'";
Class.clsDB db = new Class.clsDB();
ds = db.ExecuteDataSet(sql);
db.Close();
return(ds.Tables[0]);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
public DataTable NotPrice()
{
try
{
DataSet ds = new DataSet();
String sql;
sql = "select no,price from temp where price = 0";
Class.clsDB db = new Class.clsDB();
ds = db.ExecuteDataSet(sql);
db.Close();
return(ds.Tables[0]);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
public DataTable CountUnder()
{
try
{
DataSet ds = new DataSet();
String sql;
sql = "select count(*) as c from temp2under where price > 0";
Class.clsDB db = new Class.clsDB();
ds = db.ExecuteDataSet(sql);
db.Close();
return(ds.Tables[0]);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
public DataTable BindFilename()
{
try
{
DataSet ds = new DataSet();
String sql;
sql = "select * from tb_file_name ";
Class.clsDB db = new Class.clsDB();
ds = db.ExecuteDataSet(sql);
db.Close();
return(ds.Tables[0]);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
public DataTable getDataTemp2under()
{
try
{
DataSet ds = new DataSet();
String sql;
sql = "Select * From Temp2under";
Class.clsDB db = new Class.clsDB();
ds = db.ExecuteDataSet(sql);
db.Close();
return(ds.Tables[0]);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
public DataTable getDocument_By_DocId(string doc_id)
{
try
{
DataSet ds = new DataSet();
String sql;
sql = "Select * From document where doc_id = '" + doc_id + "'";
Class.clsDB db = new Class.clsDB();
ds = db.ExecuteDataSet(sql);
db.Close();
return(ds.Tables[0]);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
public DataTable Select_Data(MODEL.Data criteria)
{
try
{
Class.clsDB db = new Class.clsDB();
string sql;
DataTable dt;
sql = "Select * From vm_visitor Where in_time >= '" + criteria.in_time + "' and out_time <= '" + criteria.out_time + "'";
dt = db.ExecuteDataTable(sql);
return(dt);
return(null);
}
catch (Exception ex)
{
return(null);
}
}
public DataTable Test()
{
try
{
DataSet ds = new DataSet();
DataTable dt;
String sql;
sql = "Select * From provinces ";
Class.clsDB db = new Class.clsDB();
dt = db.ExecuteDataTable(sql);
db.Close();
return(ds.Tables[0]);
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
public DataTable GetCustomerByID(string id)
{
DataTable table;
try
{
if (id == "")
{
id = "0";
}
DataSet set = new DataSet();
string strSQL = "Select * From supplier Where supp_code = " + id + " order by supp_short_name ";
Class.clsDB sdb1 = new Class.clsDB();
set = sdb1.ExecuteDataSet(strSQL);
sdb1.Close();
table = set.Tables[0];
}
catch (Exception exception1)
{
throw exception1;
}
return(table);
}