public List <CmpProject> GetMemberlistByUnionSearch(String guest, string suppl, string gm)
{
string sql = "select * from CmpProject where mcustomer like '%'||@cust||'%' and mSupplier like '%'||@supplier||'%' and pjManager like '%'||@pjgm||'%' ;";
SQLiteParameter[] pms =
{
new SQLiteParameter("@cust", guest),
new SQLiteParameter("@supplier", suppl),
new SQLiteParameter("@pjgm", gm)
};
List <CmpProject> list = null;
try
{
DataTable da = SQLiteHelper.ExecteDataTable(sql, pms);
if (da.Rows.Count > 0)
{
list = new List <CmpProject>();
CmpProject memberinfo = null;
foreach (DataRow row in da.Rows)
{
memberinfo = new CmpProject();
LoadEntity(memberinfo, row);
list.Add(memberinfo);
}
}
return(list);
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
return(list);
}
}
public int ModifyMemberInfobyid(CmpProject cmppj, int id)
{
try
{
string sql = "update CmpProject set ProjectID=@ProjectID,mcustomer=@customer,mSupplier=@Supplier,CreateDate=@CreateDate,pjDiscription=@pjDiscription,pjManager=@pjManager,note=@note,pjStatus=@pjStatus where id=@id";
SQLiteParameter[] pms =
{
new SQLiteParameter("@ProjectID", cmppj.ProjectID),
new SQLiteParameter("@customer", cmppj.customer),
new SQLiteParameter("@Supplier", cmppj.Supplier),
new SQLiteParameter("@CreateDate", cmppj.CreateDate),
new SQLiteParameter("@pjDiscription", cmppj.pjDiscription),
new SQLiteParameter("@pjManager", cmppj.pjManager),
new SQLiteParameter("@note", cmppj.note),
new SQLiteParameter("@pjStatus", cmppj.pjStatus),
new SQLiteParameter("@id", id)
};
return(SQLiteHelper.ExecuteNonQuery(sql, pms));
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
return(0);
}
}
private void LoadEntity(CmpProject memberinfo, DataRow row, int id)
{
memberinfo.id = id;
memberinfo.ProjectID = row[0].ToString();
memberinfo.customer = row[1].ToString();
memberinfo.Supplier = row[2].ToString();
memberinfo.CreateDate = Convert.ToDateTime(row[3].ToString()).ToShortDateString().ToString();
memberinfo.pjDiscription = row[4].ToString();
memberinfo.pjManager = row[5].ToString();
memberinfo.note = row[6].ToString();
memberinfo.pjStatus = row[7].ToString();
}
private void LoadEntity(CmpProject memberinfo, DataRow row)
{
memberinfo.id = Convert.ToInt32(row["id"]);
memberinfo.ProjectID = row["ProjectID"].ToString();
memberinfo.customer = row["mcustomer"].ToString();
memberinfo.Supplier = row["mSupplier"].ToString();
memberinfo.CreateDate = Convert.ToDateTime(row["CreateDate"].ToString()).ToShortDateString().ToString();
memberinfo.pjDiscription = row["pjDiscription"].ToString();
memberinfo.pjManager = row["pjManager"].ToString();
memberinfo.note = row["note"].ToString();
memberinfo.pjStatus = row["pjStatus"].ToString();
}
private void LoadData()
{
cmppj = new CmpProject();
cmppj.ProjectID = txt_Projectid.Text;
cmppj.customer = txt_pjcustomer.Text;
cmppj.Supplier = txt_pjsupplier.Text;
cmppj.CreateDate = dtpicker.Text;
cmppj.pjDiscription = txt_pjdiscription.Text;
cmppj.pjManager = txt_pjmanager.Text;
cmppj.note = txt_pjnote.Text;
cmppj.pjStatus = txt_pjstatus.Text;
}
public List <CmpProject> GetMemberListByPjManager(string Manager)
{
string sql = "select * from CmpProject where pjManager like '%'||@xxx||'%';";
DataTable da = SQLiteHelper.ExecteDataTable(sql, new SQLiteParameter("@xxx", Manager));
List <CmpProject> list = null;
if (da.Rows.Count > 0)
{
list = new List <CmpProject>();
CmpProject memberinfo = null;
foreach (DataRow row in da.Rows)
{
memberinfo = new CmpProject();
LoadEntity(memberinfo, row);
list.Add(memberinfo);
}
}
return(list);
}
public List <CmpProject> GetMemberList()
{
string sql = "select * from CmpProject";
DataTable da = SQLiteHelper.ExecteDataTable(sql);
List <CmpProject> list = null;
if (da.Rows.Count > 0)
{
list = new List <CmpProject>();
CmpProject memberinfo = null;
foreach (DataRow row in da.Rows)
{
memberinfo = new CmpProject();
LoadEntity(memberinfo, row);
list.Add(memberinfo);
}
}
return(list);
}
public List <CmpProject> GetMemberListBySupplier(string suuplier)
{
string sql = "select * from CmpProject where mSupplier=@suplier";
DataTable da = SQLiteHelper.ExecteDataTable(sql, new SQLiteParameter("@suplier", suuplier));
List <CmpProject> list = null;
if (da.Rows.Count > 0)
{
list = new List <CmpProject>();
CmpProject memberinfo = null;
foreach (DataRow row in da.Rows)
{
memberinfo = new CmpProject();
LoadEntity(memberinfo, row);
list.Add(memberinfo);
}
}
return(list);
}
public List <CmpProject> LoadExcel2Datatable(string filePath)
{
DataTable dt = CommonHelper.ToDataTable(filePath);
List <CmpProject> list = null;
if (dt.Rows.Count > 0)
{
int id = 1;
list = new List <CmpProject>();
CmpProject memberinfo = null;
foreach (DataRow row in dt.Rows)
{
memberinfo = new CmpProject();
LoadEntity(memberinfo, row, id);
list.Add(memberinfo);
id++;
}
}
return(list);
}
public int InsertMemberInfo(CmpProject cmppj)
{
try
{
string sql = "insert into CmpProject values(null,@PID,@cust,@Supp ,@CDate,@pjDis,@pjMa,@nt,@pjSta)";
SQLiteParameter[] pms =
{
new SQLiteParameter("@PID", cmppj.ProjectID),
new SQLiteParameter("@cust", cmppj.customer),
new SQLiteParameter("@Supp", cmppj.Supplier),
new SQLiteParameter("@CDate", cmppj.CreateDate),
new SQLiteParameter("@pjDis", cmppj.pjDiscription),
new SQLiteParameter("@pjMa", cmppj.pjManager),
new SQLiteParameter("@nt", cmppj.note),
new SQLiteParameter("@pjSta", cmppj.pjStatus)
};
return(SQLiteHelper.ExecuteNonQuery(sql, pms));
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
return(0);
}
}
public bool ModifyMemberInfobyid(CmpProject cmppj, int id)
{
return(dal.ModifyMemberInfobyid(cmppj, id) > 0);
}
public int InsertMemberInfo(CmpProject cmppj)
{
return(dal.InsertMemberInfo(cmppj));
}