public DataSet Update()
{
pro_view.fld_DAL.cls_dal dal = new fld_DAL.cls_dal();
NpgsqlParameter[] param = new NpgsqlParameter[6];
DataSet ds = new DataSet();
param[0] = new NpgsqlParameter("@in_id", NpgsqlTypes.NpgsqlDbType.Varchar, (4));
param[0].Value = id;
param[1] = new NpgsqlParameter("@in_aname", NpgsqlTypes.NpgsqlDbType.Varchar, (50));
param[1].Value = aname;
param[2] = new NpgsqlParameter("@in_ename", NpgsqlTypes.NpgsqlDbType.Varchar, (50));
param[2].Value = ename;
param[3] = new NpgsqlParameter("@in_user_id", NpgsqlTypes.NpgsqlDbType.Varchar, (4));
param[3].Value = user_id;
param[4] = new NpgsqlParameter("@in_company_id", NpgsqlTypes.NpgsqlDbType.Varchar, (2));
param[4].Value = company_id;
param[5] = new NpgsqlParameter("@in_stop", NpgsqlTypes.NpgsqlDbType.Boolean);
param[5].Value = stop;
ds = dal.ExecuteAndRetriveDataSet("scl.fnc_section_update", param);
ds.Tables[0].TableName = "tbl_section";
return(ds);
}
public DataSet Insert()
{
pro_view.fld_DAL.cls_dal dal = new fld_DAL.cls_dal();
NpgsqlParameter[] param = new NpgsqlParameter[6];
DataSet ds = new DataSet();
param[0] = new NpgsqlParameter("@in_id", NpgsqlTypes.NpgsqlDbType.Varchar, (4));
param[0].Value = id;
param[1] = new NpgsqlParameter("@in_aname", NpgsqlTypes.NpgsqlDbType.Varchar, (50));
param[1].Value = aname;
param[2] = new NpgsqlParameter("@in_ename", NpgsqlTypes.NpgsqlDbType.Varchar, (50));
param[2].Value = ename;
param[3] = new NpgsqlParameter("@in_user_id", NpgsqlTypes.NpgsqlDbType.Varchar, (4));
param[3].Value = user_id;
param[4] = new NpgsqlParameter("@in_company_id", NpgsqlTypes.NpgsqlDbType.Varchar, (2));
param[4].Value = company_id;
param[5] = new NpgsqlParameter("@in_city_id", NpgsqlTypes.NpgsqlDbType.Varchar, (4));
param[5].Value = city_id;
ds = dal.ExecuteAndRetriveDataSet("grl.fnc_areas_insert", param);;
ds.Tables[0].TableName = "tbl_areas";
return(ds);
}
public DataSet Select()
{
pro_view.fld_DAL.cls_dal dal = new fld_DAL.cls_dal();
DataSet ds = new DataSet();
ds = dal.ExecuteAndRetriveDataSet("Select * From scl.tbl_section order by sequ;");
ds.Tables[0].TableName = "tbl_section";
return(ds);
}
public DataSet Select()
{
pro_view.fld_DAL.cls_dal dal = new fld_DAL.cls_dal();
DataSet ds = new DataSet();
ds = dal.ExecuteAndRetriveDataSet("Select * From grl.tbl_areas order by sequ;"
+ "Select id,aname,ename From grl.tbl_cities order by sequ;");
ds.Tables[0].TableName = "tbl_areas";
ds.Tables[1].TableName = "tbl_cities";
return(ds);
}
void GetAllDatabase()
{
fld_DAL.cls_dal dal = new fld_DAL.cls_dal("postgres");
com_database.Items.Clear();
foreach (DataRow r in dal.ExecuteAndRetriveDataSet("SELECT * FROM pg_database WHERE datistemplate = false;").Tables[0].Rows)
{
com_database.Items.Add(r["datname"].ToString());
}
com_database.Items.Remove("postgres");
com_database.Text = Properties.Settings.Default.Database;
}
public DataSet Select()
{
pro_view.fld_DAL.cls_dal dal = new fld_DAL.cls_dal();
DataSet ds = new DataSet();
string select = "";
switch (selecttype)
{
case "Login":
select = "SELECT * FROM scl.tbl_parents WHERE id = '0';";
break;
case "Select_All":
select = "SELECT * FROM scl.tbl_parents WHERE company_id = '" + company_id + "';";
break;
case "Select":
select = "SELECT * FROM scl.tbl_parents WHERE id = '" + id + "' AND company_id = '" + company_id + "';";
break;
case "Search":
select = "select * from scl.tbl_parents where(f_parent_id = '" + f_parent_id + "'or id like '%" + id + "' or f_mobile1 = '" + f_mobile1 + "' or f_aname like '%" + f_aname + "%' or ff_aname like '%" + ff_aname + " %'or m_parent_id = '" + m_parent_id + "' or m_mobile1 = '" + m_mobile1 + "' or m_aname like '%" + m_aname + "%' or mf_aname like '%" + mf_aname + "%'or f_ename like '" + f_ename + "%' or ff_ename like '%" + ff_ename + "%' or m_ename like '%" + m_ename + "%' or mf_ename like '%" + mf_ename + "%')and company_id = '" + company_id + "';";
break;
case "First":
select = "SELECT * FROM scl.tbl_parents WHERE company_id = '" + company_id + "' ORDER BY ctid ASC LIMIT 1;";
break;
case "Prev":
select = "SELECT * FROM scl.tbl_parents WHERE id = (SELECT id FROM scl.tbl_parents WHERE sequ < (SELECT sequ FROM scl.tbl_parents WHERE id = '" + id + "' AND company_id = '" + company_id + "' LIMIT 1) AND company_id = '" + company_id + "' ORDER BY ctid Desc LIMIT 1) AND company_id = '" + company_id + "';";
break;
case "Next":
select = "SELECT * FROM scl.tbl_parents WHERE id = (SELECT id FROM scl.tbl_parents WHERE sequ > (SELECT sequ FROM scl.tbl_parents WHERE id = '" + id + "' AND company_id = '" + company_id + "' LIMIT 1) AND company_id = '" + company_id + "' ORDER BY ctid ASC LIMIT 1) AND company_id = '" + company_id + "';";
break;
case "Last":
select = "SELECT * FROM scl.tbl_parents WHERE company_id = '" + company_id + "' ORDER BY ctid Desc LIMIT 1;";
break;
}
ds = dal.ExecuteAndRetriveDataSet(select
+ "Select * From grl.tbl_nationality order by ctid;"
+ "Select * From grl.tbl_religion order by ctid;"
);
ds.Tables[0].TableName = "tbl_Parents";
ds.Tables[1].TableName = "tbl_nationality";
ds.Tables[2].TableName = "tbl_religion";
return(ds);
}
public DataSet Delete()
{
pro_view.fld_DAL.cls_dal dal = new fld_DAL.cls_dal();
NpgsqlParameter[] param = new NpgsqlParameter[3];
DataSet ds = new DataSet();
param[0] = new NpgsqlParameter("@in_id", NpgsqlTypes.NpgsqlDbType.Varchar, 4);
param[0].Value = id;
param[1] = new NpgsqlParameter("@in_user_id", NpgsqlTypes.NpgsqlDbType.Varchar, (4));
param[1].Value = user_id;
param[2] = new NpgsqlParameter("@in_company_id", NpgsqlTypes.NpgsqlDbType.Varchar, (2));
param[2].Value = company_id;
ds = dal.ExecuteAndRetriveDataSet("grl.fnc_nationality_delete", param);
ds.Tables[0].TableName = "tbl_nationality";
return(ds);
}
public DataSet Update()
{
pro_view.fld_DAL.cls_dal dal = new fld_DAL.cls_dal();
NpgsqlParameter[] param = new NpgsqlParameter[36];
DataSet ds = new DataSet();
// father parameters
param[0] = new NpgsqlParameter("@in_f_parent_id", NpgsqlTypes.NpgsqlDbType.Varchar, (20));
param[0].Value = f_parent_id;
param[1] = new NpgsqlParameter("@in_f_enddate_id", NpgsqlTypes.NpgsqlDbType.Timestamp);
param[1].Value = f_enddate_id;
param[2] = new NpgsqlParameter("@in_f_aname", NpgsqlTypes.NpgsqlDbType.Varchar, (50));
param[2].Value = f_aname;
param[3] = new NpgsqlParameter("@in_f_ename", NpgsqlTypes.NpgsqlDbType.Varchar, (50));
param[3].Value = f_ename;
param[4] = new NpgsqlParameter("@in_ff_aname", NpgsqlTypes.NpgsqlDbType.Varchar, (20));
param[4].Value = ff_aname;
param[5] = new NpgsqlParameter("@in_ff_ename", NpgsqlTypes.NpgsqlDbType.Varchar, (20));
param[5].Value = ff_ename;
param[6] = new NpgsqlParameter("@in_f_religion", NpgsqlTypes.NpgsqlDbType.Integer);
param[6].Value = f_religion;
param[7] = new NpgsqlParameter("@in_f_country", NpgsqlTypes.NpgsqlDbType.Varchar, (4));
param[7].Value = f_country;
param[8] = new NpgsqlParameter("@in_f_hadress", NpgsqlTypes.NpgsqlDbType.Varchar, (50));
param[8].Value = f_haddress;
param[9] = new NpgsqlParameter("@in_f_wadress", NpgsqlTypes.NpgsqlDbType.Varchar, (50));
param[9].Value = f_waddress;
param[10] = new NpgsqlParameter("@in_f_mobile1", NpgsqlTypes.NpgsqlDbType.Varchar, (20));
param[10].Value = f_mobile1;
param[11] = new NpgsqlParameter("@in_f_hphone", NpgsqlTypes.NpgsqlDbType.Varchar, (20));
param[11].Value = m_hphone;
param[12] = new NpgsqlParameter("@in_f_wphone", NpgsqlTypes.NpgsqlDbType.Varchar, (20));
param[12].Value = f_wphone;
param[13] = new NpgsqlParameter("@in_f_acc", NpgsqlTypes.NpgsqlDbType.Varchar, (50));
param[13].Value = m_acc;
param[14] = new NpgsqlParameter("@in_f_mail", NpgsqlTypes.NpgsqlDbType.Varchar, (50));
param[14].Value = m_mail;
// mother parameters
param[15] = new NpgsqlParameter("@in_m_parent_id", NpgsqlTypes.NpgsqlDbType.Varchar, (20));
param[15].Value = f_parent_id;
param[16] = new NpgsqlParameter("@in_m_enddate_id", NpgsqlTypes.NpgsqlDbType.Timestamp);
param[16].Value = f_enddate_id;
param[17] = new NpgsqlParameter("@in_m_aname", NpgsqlTypes.NpgsqlDbType.Varchar, (50));
param[17].Value = f_aname;
param[18] = new NpgsqlParameter("@in_m_ename", NpgsqlTypes.NpgsqlDbType.Varchar, (50));
param[18].Value = f_ename;
param[19] = new NpgsqlParameter("@in_mf_aname", NpgsqlTypes.NpgsqlDbType.Varchar, (20));
param[19].Value = ff_aname;
param[20] = new NpgsqlParameter("@in_mf_ename", NpgsqlTypes.NpgsqlDbType.Varchar, (20));
param[20].Value = ff_ename;
param[21] = new NpgsqlParameter("@in_m_religion", NpgsqlTypes.NpgsqlDbType.Integer);
param[21].Value = f_religion;
param[22] = new NpgsqlParameter("@in_m_country", NpgsqlTypes.NpgsqlDbType.Varchar, (4));
param[22].Value = f_country;
param[23] = new NpgsqlParameter("@in_m_hadress", NpgsqlTypes.NpgsqlDbType.Varchar, (50));
param[23].Value = f_haddress;
param[24] = new NpgsqlParameter("@in_m_wadress", NpgsqlTypes.NpgsqlDbType.Varchar, (50));
param[24].Value = f_waddress;
param[25] = new NpgsqlParameter("@in_m_mobile1", NpgsqlTypes.NpgsqlDbType.Varchar, (20));
param[25].Value = f_mobile1;
param[26] = new NpgsqlParameter("@in_m_hphone", NpgsqlTypes.NpgsqlDbType.Varchar, (20));
param[26].Value = m_hphone;
param[27] = new NpgsqlParameter("@in_m_wphone", NpgsqlTypes.NpgsqlDbType.Varchar, (20));
param[27].Value = f_wphone;
param[28] = new NpgsqlParameter("@in_m_acc", NpgsqlTypes.NpgsqlDbType.Varchar, (50));
param[28].Value = m_acc;
param[29] = new NpgsqlParameter("@in_m_mail", NpgsqlTypes.NpgsqlDbType.Varchar, (50));
param[29].Value = m_mail;
// other parameters
param[30] = new NpgsqlParameter("@in_id", NpgsqlTypes.NpgsqlDbType.Varchar, (50));
param[30].Value = id;
param[31] = new NpgsqlParameter("@in_user_id", NpgsqlTypes.NpgsqlDbType.Varchar, (4));
param[31].Value = user_id;
param[32] = new NpgsqlParameter("@in_company_id", NpgsqlTypes.NpgsqlDbType.Varchar, (2));
param[32].Value = company_id;
param[33] = new NpgsqlParameter("@in_stop", NpgsqlTypes.NpgsqlDbType.Boolean);
param[33].Value = stop;
param[34] = new NpgsqlParameter("@in_notes", NpgsqlTypes.NpgsqlDbType.Text);
param[34].Value = notes;
param[35] = new NpgsqlParameter("@in_branch_id", NpgsqlTypes.NpgsqlDbType.Varchar, (3));
param[35].Value = branch_id;
ds = dal.ExecuteAndRetriveDataSet("scl.fnc_parents_update", param);;
ds.Tables[0].TableName = "tbl_parents";
return(ds);
}
private void btn_AddDatabase_Click(object sender, EventArgs e)
{
#region Validation
if (txt_databasename.Text.Trim() == "")
{
MessageBox.Show("يجب إدخال أسم لقاعدة البيانات الجديدة", "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txt_databasename.Focus();
return;
}
#endregion
if (rad_EmptyDatabase.Checked)
{
#region Validation
if (txt_databasename.Text.Trim() == "")
{
MessageBox.Show("يجب إدخال أسم قاعدة البيانات الجديدة", "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txt_databasename.Focus();
return;
}
else
{
txt_databasename.Text = txt_databasename.Text.Trim().ToLower();
}
if (txt_UserName.Text.Trim() == "")
{
MessageBox.Show("يجب إدخال أسم مستخدم قاعدة البيانات الجديدة", "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txt_UserName.Focus();
return;
}
if (txt_Password.Text.Trim() == "")
{
MessageBox.Show("يجب إدخال كلمة مرور لمستخدم قاعدة البيانات الجديدة", "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txt_Password.Focus();
return;
}
if (txt_ConfirmPassword.Text.Trim() == "")
{
MessageBox.Show("يجب إدخال تأكيد كلمة مرور لمستخدم قاعدة البيانات الجديدة", "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txt_ConfirmPassword.Focus();
return;
}
if (txt_Password.Text.Trim() != txt_ConfirmPassword.Text.Trim())
{
MessageBox.Show("كلمة المرور غير متطابقة", "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txt_ConfirmPassword.Focus();
return;
}
#endregion
try
{
btn_AddDatabase.Text = "Please Wait ...";
btn_AddDatabase.Enabled = false;
string script = "CREATE DATABASE " + txt_databasename.Text.Trim() + ";";
fld_DAL.cls_dal dal1 = new fld_DAL.cls_dal("postgres");
dal1.ExecuteAndRetriveDataSet(script);
script = Properties.Resources.CreateNewEmptyDatabase;
fld_DAL.cls_dal dal2 = new fld_DAL.cls_dal(txt_databasename.Text.Trim());
dal2.ExecuteAndRetriveDataSet(script);
script = "INSERT INTO grl.tbl_users(id, sequ, aname, ename, password, gender_id, company_id, branch_id, creationtime, createuser_id)"
+ "VALUES('0001', 1, '" + txt_UserName.Text.Trim() + "', '" + txt_UserName.Text.Trim() + "', '" + txt_Password.Text.Trim() + "', 1, '01', '001', '" + DateTime.Now.ToString("MM/dd/yyyy") + "', '0001');\r\n";
script += Properties.Resources.InsertMainDataToNewDatabase;
fld_DAL.cls_dal dal3 = new fld_DAL.cls_dal(txt_databasename.Text.Trim());
dal3.ExecuteAndRetriveDataSet(script);
MessageBox.Show("تم اضافة قاعدة البيانات بنجاح", "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Information);
txt_databasename.Focus();
Close();
}
catch (Exception ex)
{
btn_AddDatabase.Text = "OK";
btn_AddDatabase.Enabled = true;
MessageBox.Show(ex.Message, "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Error);
throw;
}
}
else if (rad_CopyDatabase.Checked)
{
#region Validation
if (com_CopyDatabase.Text == "")
{
MessageBox.Show("يجب تحديد قاعدة بيانات للنسخ منها", "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
com_CopyDatabase.DroppedDown = true;
return;
}
#endregion
try
{
btn_AddDatabase.Text = "Please Wait ...";
btn_AddDatabase.Enabled = false;
string script = "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity "
+ "WHERE pg_stat_activity.datname = 'postgres' AND pid<> pg_backend_pid();";
script += "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity "
+ "WHERE pg_stat_activity.datname = '" + com_CopyDatabase.Text + "' AND pid<> pg_backend_pid();";
fld_DAL.cls_dal dal1 = new fld_DAL.cls_dal("postgres");
dal1.ExecuteAndRetriveDataSet(script);
script = "CREATE DATABASE " + txt_databasename.Text.Trim() + " WITH TEMPLATE " + com_CopyDatabase.Text + " OWNER postgres;";
fld_DAL.cls_dal dal2 = new fld_DAL.cls_dal("postgres");
dal2.ExecuteAndRetriveDataSet(script);
MessageBox.Show("تم اضافة قاعدة البيانات بنجاح", "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Information);
txt_databasename.Focus();
Close();
}
catch (Exception ex)
{
btn_AddDatabase.Text = "OK";
btn_AddDatabase.Enabled = true;
MessageBox.Show(ex.Message, "اضافة قاعدة بيانات", MessageBoxButtons.OK, MessageBoxIcon.Error);
throw;
}
}
}