public static DataTable LoadSuscriber_EndDate(string Edate)
{
GlobalConnection.PerformConnection();
if (GlobalConnection.ServerAvailable == true)
{
try
{
SqlDataAdapter daVfrm = null;
DataSet dsVfrm = null;
daVfrm = new SqlDataAdapter("select EPahal_form.formID[Form No.],EPahal_form.Invoice_no[Invoice No.],EPahal_form.receipt_no[Receipt No.],EPahal_form.organization_name[Name of Organization],EPahal_form.organization_address[Address of Organization],EPahal_form.organization_phone[Phone No.],EPahal_form.organization_first_persion[First Person of Organization],EPahal_form.organization_first_persion_mobile[First Person Mobile],EPahal_form.contact_person[Contact Person],EPahal_form.contact_person_moble[Mobile No.],EPahal_form.service_email[Service Receiving Email Address],EPahal_form.notice_type[Type of Notice Required],EPahal_form.service_start_date[Service Start Date],EPahal_form.service_period[Service Period],EPahal_form.service_expire_date[Service Expiry Date],EPahal_form.receive_amount[Received Amount],EPahal_form.receive_amount_date[Received Amount Date],EPahal_form.cash[Cash],EPahal_form.cheque_no[Cheque No.],EPahal_form.deposite_by[Bank Deposited By],EPahal_form.bank_name[Bank Name],EPahal_form.refered_by[Refered By],EPahal_form.paid_by[Paid By],EPahal_form.received_by[Received By],EPahal_form.checked_by[Checked By],Epahal_form.feedback[Feedbacks],Epahal_form.remarks[Remarks] from EPahal_form WHERE service_expire_date LIKE '" + Edate + "%' AND estatus = 1 ORDER BY formID DESC", GlobalConnection.cn);
dsVfrm = new DataSet();
daVfrm.Fill(dsVfrm, "subscriber");
return(dsVfrm.Tables[0]);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return(null);
}
}
else
{
return(null);
}
}
public static string AutoSerialNumber(string tableField, string tableName, string FirstLetter, DataSet datasetAutoNumber)
{
if (GlobalConnection.ServerAvailable == true)
{
GlobalConnection.PerformConnection();
daID = new SqlDataAdapter();
daID = new SqlDataAdapter("SELECT substring (MAX(" + tableField + "),2,5) FROM " + tableName + "", GlobalConnection.cn);
dsID = new DataSet();
daID.Fill(dsID, tableName);
string MaxNo = dsID.Tables[0].Rows[0][0].ToString();
AutoNumber = num(MaxNo, FirstLetter);
return(AutoNumber);
}
else if (GlobalConnection.ServerAvailable == false)
{
// creating dataview object using constructor and passing parameters
//It creates dataview from dataset ds of offlinetable with arguments as studentid shouldnt be null, show studentid fields of current rows.
DataView autonumberdv = new DataView(datasetAutoNumber.Tables[tableName], tableField + " is not null", tableField, DataViewRowState.CurrentRows);
//This will filter the dataview by using max function. This dsplay the fiels which have highest id.
autonumberdv.RowFilter = "" + tableField + " = Max(" + tableField + ")";
string MaxValue = autonumberdv[0].Row[tableField].ToString();
string MaxNo = MaxValue.Substring(1, 4);
AutoNumber = num(MaxNo, FirstLetter);
return(AutoNumber);
}
else
{
return(null);
}
}
public static void saveCounter_User(string Userid, DateTime CbDate, double Cash, int Bank_Tr, double Bank, int Card_Tr, double Card, int Credit_Tr, double Credit, string CreateBy, DateTime CreateDate)
{
try
{
GlobalConnection.PerformConnection();
SqlCommand cmd = GlobalConnection.cn.CreateCommand();
cmd.CommandText = "INSERT INTO counter_balance(cb_user, cb_date, cb_cash, cb_bank_no, cb_bank, cb_card_no, cb_card, cb_credit_no, cb_credit, cb_save, cb_save_date) VALUES" + "(@cb_user, @cb_date, @cb_cash, @cb_bank_no, @cb_bank, @cb_card_no, @cb_card, @cb_credit_no, @cb_credit, @cb_save, @cb_save_date)";
cmd.Parameters.AddWithValue("@cb_user", Userid);
cmd.Parameters.AddWithValue("@cb_date", CbDate);
cmd.Parameters.AddWithValue("@cb_cash", Cash);
cmd.Parameters.AddWithValue("@cb_bank_no", Bank_Tr);
cmd.Parameters.AddWithValue("@cb_bank", Bank);
cmd.Parameters.AddWithValue("@cb_card_no", Card_Tr);
cmd.Parameters.AddWithValue("@cb_card", Card);
cmd.Parameters.AddWithValue("@cb_credit_no", Credit_Tr);
cmd.Parameters.AddWithValue("@cb_credit", Credit);
cmd.Parameters.AddWithValue("@cb_save", CreateBy);
cmd.Parameters.AddWithValue("@cb_save_date", CreateDate);
cmd.ExecuteNonQuery();
MessageBox.Show("User:"******"\nSaved Successfully", GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static DataTable Search_Selection()
{
GlobalConnection.PerformConnection();
daStudentInfo = new SqlDataAdapter("select EPahal_form.formID[Form No.],EPahal_form.Invoice_no[Invoice No.],EPahal_form.receipt_no[Receipt No.],EPahal_form.organization_name[Name of Organization],EPahal_form.organization_address[Address of Organization],EPahal_form.organization_phone[Phone No.],EPahal_form.organization_first_persion[First Person of Organization],EPahal_form.organization_first_persion_mobile[First Person Mobile],EPahal_form.contact_person[Contact Person],EPahal_form.contact_person_moble[Mobile No.],EPahal_form.service_email[Service Receiving Email Address],EPahal_form.notice_type[Type of Notice Required],EPahal_form.service_start_date[Service Start Date],EPahal_form.service_period[Service Period],EPahal_form.service_expire_date[Service Expiry Date],EPahal_form.receive_amount[Received Amount],EPahal_form.receive_amount_date[Received Amount Date],EPahal_form.cash[Cash],EPahal_form.cheque_no[Cheque No.],EPahal_form.deposite_by[Bank Deposited By],EPahal_form.bank_name[Bank Name],EPahal_form.refered_by[Refered By],EPahal_form.paid_by[Paid By],EPahal_form.received_by[Received By],EPahal_form.checked_by[Checked By],Epahal_form.feedback[Feedbacks],Epahal_form.remarks[Remarks] from EPahal_form WHERE estatus = 1 ORDER BY formID DESC", GlobalConnection.cn);
dsStudentInfo = new DataSet();
daStudentInfo.Fill(dsStudentInfo, "subscriber_search");
DataTable dt_search = dsStudentInfo.Tables["subscriber_search"];
return(dt_search);
}
public static DataTable Search_Particular(string fno)
{
GlobalConnection.PerformConnection();
daStudentInfo = new SqlDataAdapter("SELECT EPahal_form.formID[Form No.],EPahal_form.Invoice_no[Invoice No.],EPahal_form.receipt_no[Receipt No.],EPahal_form.organization_name[Name of Organization],EPahal_form.organization_address[Address of Organization],EPahal_form.organization_phone[Phone No.],EPahal_form.organization_first_persion[First Person of Organization],EPahal_form.organization_first_persion_mobile[First Person Mobile],EPahal_form.contact_person[Contact Person],EPahal_form.contact_person_moble[Mobile No.],EPahal_form.service_email[Service Receiving Email Address],EPahal_form.notice_type[Type of Notice Required],EPahal_form.service_start_date[Service Start Date],EPahal_form.service_period[Service Period],EPahal_form.service_expire_date[Service Expiry Date],EPahal_form.receive_amount[Received Amount],EPahal_form.receive_amount_date[Received Amount Date],EPahal_form.cash[Cash],EPahal_form.cheque_no[Cheque No.],EPahal_form.deposite_by[Bank Deposited By],EPahal_form.bank_name[Bank Name],EPahal_form.refered_by[Refered By] FROM EPahal_form WHERE formID = '" + fno + "'", GlobalConnection.cn);
dsStudentInfo = new DataSet();
daStudentInfo.Fill(dsStudentInfo, "subscriber_search");
DataTable dt_search = dsStudentInfo.Tables["subscriber_search"];
return(dt_search);
}
public static DataTable Load_(string Name)
{
try
{
GlobalConnection.PerformConnection();
SqlDataAdapter da = new SqlDataAdapter("SELECT DISTINCT " + Name + " FROM user_info WHERE " + Name + " != 'NULL' AND " + Name + " != '' ORDER BY " + Name + " ASC", GlobalConnection.cn);
DataSet ds = new DataSet();
da.Fill(ds, "user_info");
return(ds.Tables[0]);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
public static void deleteUserinfo(string Userid, string updateFirstname, string updateMiddlename, string updateLastname)
{
try
{
GlobalConnection.PerformConnection();
SqlCommand cmd = GlobalConnection.cn.CreateCommand();
cmd.CommandText = "update user_info set user_drop_status=@user_drop_status where user_id='" + Userid + "'";
cmd.Parameters.AddWithValue("@user_drop_status", true);
cmd.ExecuteNonQuery();
MessageBox.Show("User:"******"\n Deleted Successfully", GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static void UpdateForm1(string formNo, string invoice, string receipt, string OrgName, string OrgAdd, string OrgPhone, string OrgFirstPerson, string OrgFirstPersonMobile, string ContactPerson, string ContactMobile, string email, string notice, string startdate, string period, string ExpireDate, string Paidby, string receivedby, string checkedby, string fdbck, string rmark, string amount, string recDate, string cash, string chequeNo, string DepositedBy, string BankName, string ReferedBy)
{
try
{
GlobalConnection.PerformConnection();
SqlCommand cmd = GlobalConnection.cn.CreateCommand();
cmd.CommandText = "UPDATE EPahal_form set formID=@formID,Invoice_no=@Invoice_no,receipt_no=@receipt_no,organization_name=@organization_name,organization_address=@organization_address,organization_phone=@organization_phone,organization_first_persion=@organization_first_persion,organization_first_persion_mobile=@organization_first_persion_mobile,contact_person=@contact_person,contact_person_moble=@contact_person_moble,service_email=@service_email,notice_type=@notice_type,service_start_date=@service_start_date,service_period=@service_period,service_expire_date=@service_expire_date,paid_by=@paid_by,received_by=@received_by,checked_by=@checked_by,feedback=@feedback,remarks=@remarks,receive_amount=@receive_amount,receive_amount_date=@receive_amount_date,cash=@cash,cheque_no=@cheque_no,deposite_by=@deposite_by,bank_name=@bank_name,refered_by=@refered_by where formID= '" + tempUpdateId + "'";
cmd.Parameters.AddWithValue("@formID", formNo);
cmd.Parameters.AddWithValue("@Invoice_no", invoice);
cmd.Parameters.AddWithValue("@receipt_no", receipt);
cmd.Parameters.AddWithValue("@organization_name", OrgName);
cmd.Parameters.AddWithValue("@organization_address", OrgAdd);
cmd.Parameters.AddWithValue("@organization_phone", OrgPhone);
cmd.Parameters.AddWithValue("@organization_first_persion", OrgFirstPerson);
cmd.Parameters.AddWithValue("@organization_first_persion_mobile", OrgFirstPersonMobile);
cmd.Parameters.AddWithValue("@contact_person", ContactPerson);
cmd.Parameters.AddWithValue("@contact_person_moble", ContactMobile);
cmd.Parameters.AddWithValue("@service_email", email);
cmd.Parameters.AddWithValue("@notice_type", notice);
cmd.Parameters.AddWithValue("@service_start_date", startdate);
cmd.Parameters.AddWithValue("@service_period", period);
cmd.Parameters.AddWithValue("@service_expire_date", ExpireDate);
cmd.Parameters.AddWithValue("@paid_by", Paidby);
cmd.Parameters.AddWithValue("@received_by", receivedby);
cmd.Parameters.AddWithValue("@checked_by", checkedby);
cmd.Parameters.AddWithValue("@feedback", fdbck);
cmd.Parameters.AddWithValue("@remarks", rmark);
cmd.Parameters.AddWithValue("@receive_amount", amount);
cmd.Parameters.AddWithValue("@receive_amount_date", recDate);
cmd.Parameters.AddWithValue("@cash", cash);
cmd.Parameters.AddWithValue("@cheque_no", chequeNo);
cmd.Parameters.AddWithValue("@deposite_by", DepositedBy);
cmd.Parameters.AddWithValue("@bank_name", BankName);
cmd.Parameters.AddWithValue("@refered_by", ReferedBy);
//cmd.Parameters.AddWithValue("@exam_createby", GlobalConnection.strUid);
//cmd.Parameters.AddWithValue("@exam_createdate", DateTime.Now);
cmd.ExecuteNonQuery();
MessageBox.Show(GlobalConnection.DataUpdate, GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static DataTable checkUserPassword(string loginName, string passwword)
{
try
{
SqlDataAdapter da = null;
DataSet ds = null;
GlobalConnection.PerformConnection();
da = new SqlDataAdapter("Select user_login_name,user_login_pwd from user_info WHERE user_login_name ='" + loginName + "' AND user_login_pwd='" + passwword + "'", GlobalConnection.cn);
ds = new DataSet();
da.Fill(ds, "user");
return(ds.Tables[0]);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(null);
}
public static DataTable CheckLoginName()
{
try
{
SqlDataAdapter da = null;
DataSet ds = null;
GlobalConnection.PerformConnection();
da = new SqlDataAdapter("Select user_login_name from user_info", GlobalConnection.cn);
ds = new DataSet();
da.Fill(ds, "user");
return(ds.Tables[0]);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
public static DataTable Load_CounterUser_Balance(string UserID)
{
try
{
SqlDataAdapter da = null;
DataSet ds = null;
GlobalConnection.PerformConnection();
da = new SqlDataAdapter("Select cb_user, cb_date, cb_cash, cb_bank_no, cb_bank, cb_card_no, cb_card, cb_credit_no, cb_credit FROM counter_balance WHERE cb_user = '******'", GlobalConnection.cn);
ds = new DataSet();
da.Fill(ds, "user");
return(ds.Tables[0]);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
public static DataTable Load_All_User(string UserID)
{
try
{
SqlDataAdapter da = null;
DataSet ds = null;
GlobalConnection.PerformConnection();
da = new SqlDataAdapter("Select user_id[User ID], user_login_name+ ': ' + user_fname + ' ' + user_mname+ ' ' + user_lname+ ' ' [User Name] from user_info WHERE user_id LIKE '%" + UserID + "%' ORDER BY user_login_name ASC", GlobalConnection.cn);
ds = new DataSet();
da.Fill(ds, "user");
return(ds.Tables[0]);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
public static DataTable Load_UserPhoto(string UserID)
{
SqlDataAdapter da_ItemType = null;
DataSet ds_ItemType = null;
try
{
GlobalConnection.PerformConnection();
da_ItemType = new SqlDataAdapter("SELECT user_photo FROM user_info where user_id = '" + UserID + "'", GlobalConnection.cn);
ds_ItemType = new DataSet();
da_ItemType.Fill(ds_ItemType, "user_photo");
return(ds_ItemType.Tables["user_photo"]);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
public static DataTable LoadUserInfo_Position(string Position)
{
try
{
SqlDataAdapter da = null;
DataSet ds = null;
GlobalConnection.PerformConnection();
da = new SqlDataAdapter("Select user_id[User ID],user_active_status[T],user_fname[First Name],user_mname[Middle Name],user_lname[Last Name],user_gender[Gender],user_martial_status[Martial Status],user_contact_address[Address],user_citizenship_passport_no[Citizenship Passport No],user_issue_date_ad[Issue Date AD],user_issue_date_bs[Issue Date BS],user_issue_place[Issue Place],user_department[Department], user_position[Position],user_contact_no1[Contact No 1],user_contact_no2[Contact No 2],user_contact_no3[Contact No 3],user_email1[Email 1],user_email2[Email 2] from user_info WHERE user_position LIKE '%" + Position + "%' AND user_drop_status = '0' AND user_id !='Admin' ORDER BY user_id DESC", GlobalConnection.cn);
ds = new DataSet();
da.Fill(ds, "user");
return(ds.Tables[0]);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(null);
}
}
public static void AutoID_Admin(string FirstLetter, string TableField, string TableName)
{
try
{
GlobalConnection.PerformConnection();
SqlDataAdapter da = new SqlDataAdapter();
da = new SqlDataAdapter("SELECT substring (MAX(" + TableField + "),2,5) FROM " + TableName + " ", GlobalConnection.cn);
DataSet ds = new DataSet();
da.Fill(ds, "Admin");
string MaxNo = ds.Tables[0].Rows[0][0].ToString();
if (MaxNo == "")
{
MaxPlus_Admin = 1;
}
else
{
MaxPlus_Admin = Convert.ToInt16(MaxNo) + 1;
}
if (MaxPlus_Admin < 10)
{
generated_id_Admin = FirstLetter + "000" + Convert.ToString(MaxPlus_Admin);
}
else if (MaxPlus_Admin >= 10 && MaxPlus_Admin < 100)
{
generated_id_Admin = FirstLetter + "00" + Convert.ToString(MaxPlus_Admin);
}
else if (MaxPlus_Admin >= 100 && MaxPlus_Admin < 1000)
{
generated_id_Admin = FirstLetter + "0" + Convert.ToString(MaxPlus_Admin);
}
else if (MaxPlus_Admin >= 1000 && MaxPlus_Admin < 10000)
{
generated_id_Admin = FirstLetter + Convert.ToString(MaxPlus_Admin);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static void updateUserPassword(string updateLoginname, string updateLoginpassword, string ChangedBy, DateTime updateCreateDate)
{
try
{
GlobalConnection.PerformConnection();
SqlCommand cmd = GlobalConnection.cn.CreateCommand();
cmd.CommandText = "update user_info set user_login_pwd=@user_login_pwd,user_updateby=@user_updateby,user_update_date=@user_update_date where user_login_name='" + updateLoginname + "'";
cmd.Parameters.AddWithValue("@user_login_pwd", updateLoginpassword);
cmd.Parameters.AddWithValue("@user_updateby", ChangedBy);
cmd.Parameters.AddWithValue("@user_update_date", updateCreateDate);
cmd.ExecuteNonQuery();
MessageBox.Show("User: "******"\nPassword Changed Successfully", GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static void updateDeactivated_Userinfo(string Userid, string updateFirstname, string updateMiddlename, string updateLastname, string updateGender, string updateMartialstatus, string ContactAddress, string updateCitizenshipPassport, DateTime updateUserissueDateAD, string updateUserissueDateBS, string updateIssueplace, string updateDepartment, string updatePosition, string updateContact1, string updateContact2, string updateContact3, string updateEmail1, string updateEmail2, string updateCreateBy, DateTime updateCreateDate, string DeActivatedBy, DateTime DeActivatedDate, string ActivatedStatus, byte[] UserPhoto)
{
try
{
GlobalConnection.PerformConnection();
SqlCommand cmd = GlobalConnection.cn.CreateCommand();
cmd.CommandText = "update user_info set user_fname=@user_fname,user_mname=@user_mname,user_lname=@user_lname,user_gender=@user_gender,user_martial_status=@user_martial_status,user_contact_address=@user_contact_address,user_citizenship_passport_no=@user_citizenship_passport_no,user_issue_date_ad=@user_issue_date_ad,user_issue_date_bs=@user_issue_date_bs,user_issue_place=@user_issue_place,user_department=@user_department,user_position=@user_position,user_contact_no1=@user_contact_no1,user_contact_no2=@user_contact_no2,user_contact_no3=@user_contact_no3,user_email1=@user_email1,user_email2=@user_email2,user_updateby=@user_updateby,user_update_date=@user_update_date,user_deactivateby=@user_deactivateby,user_deactivate_date=@user_deactivate_date,user_active_status=@user_active_status,user_photo=@user_photo where user_id='" + Userid + "'";
cmd.Parameters.AddWithValue("@user_fname", updateFirstname);
cmd.Parameters.AddWithValue("@user_mname", updateMiddlename);
cmd.Parameters.AddWithValue("@user_lname", updateLastname);
cmd.Parameters.AddWithValue("@user_gender", updateGender);
cmd.Parameters.AddWithValue("@user_martial_status", updateMartialstatus);
cmd.Parameters.AddWithValue("@user_contact_address", ContactAddress);
cmd.Parameters.AddWithValue("@user_citizenship_passport_no", updateCitizenshipPassport);
cmd.Parameters.AddWithValue("@user_issue_date_ad", updateUserissueDateAD);
cmd.Parameters.AddWithValue("@user_issue_date_bs", updateUserissueDateBS);
cmd.Parameters.AddWithValue("@user_issue_place", updateIssueplace);
cmd.Parameters.AddWithValue("@user_department", updateDepartment);
cmd.Parameters.AddWithValue("@user_position", updatePosition);
cmd.Parameters.AddWithValue("@user_contact_no1", updateContact1);
cmd.Parameters.AddWithValue("@user_contact_no2", updateContact2);
cmd.Parameters.AddWithValue("@user_contact_no3", updateContact3);
cmd.Parameters.AddWithValue("@user_email1", updateEmail1);
cmd.Parameters.AddWithValue("@user_email2", updateEmail2);
cmd.Parameters.AddWithValue("@user_updateby", updateCreateBy);
cmd.Parameters.AddWithValue("@user_update_date", updateCreateDate);
cmd.Parameters.AddWithValue("@user_deactivateby", DeActivatedBy);
cmd.Parameters.AddWithValue("@user_deactivate_date", DeActivatedDate);
cmd.Parameters.AddWithValue("@user_active_status", ActivatedStatus);
cmd.Parameters.AddWithValue("@user_photo", UserPhoto);
cmd.ExecuteNonQuery();
MessageBox.Show("User:"******"\nUpdated Successfully", GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
//public static int MaxPlus;
//public static string AutoNumber;
public static string SerialNumber(string tableField, string tableName, string FirstLetter)
{
GlobalConnection.PerformConnection();
SqlDataAdapter da = new SqlDataAdapter();
da = new SqlDataAdapter("SELECT substring (MAX(" + tableField + "),2,5) FROM " + tableName + "", GlobalConnection.cn);
DataSet ds = new DataSet();
da.Fill(ds, tableName);
string MaxNo = ds.Tables[0].Rows[0][0].ToString();
if (MaxNo == "")
{
MaxPlus = 1;
}
else
{
MaxPlus = Convert.ToInt16(MaxNo) + 1;
}
if (MaxPlus < 10)
{
AutoNumber = FirstLetter + "000" + Convert.ToString(MaxPlus);
}
else if (MaxPlus >= 10 && MaxPlus < 100)
{
AutoNumber = FirstLetter + "00" + Convert.ToString(MaxPlus);
}
else if (MaxPlus >= 100 && MaxPlus < 1000)
{
AutoNumber = FirstLetter + "0" + Convert.ToString(MaxPlus);
}
else if (MaxPlus >= 1000 && MaxPlus < 10000)
{
AutoNumber = FirstLetter + Convert.ToString(MaxPlus);
}
return(AutoNumber);
}
public static void saveActivated_Userinfo(string Userid, string Firstname, string Middlename, string Lastname, string Gender, string Martialstatus, string ContactAddress, string CitizenshipPassport, DateTime UserissueDateAD, string UserissueDateBS, string Issueplace, string Department, string Position, string Contact1, string Contact2, string Contact3, string Email1, string Email2, string Loginname, string Loginpassword, string CreateBy, DateTime CreateDate, string ActivatedBy, DateTime ActivatedDate, string ActivateStatus, byte[] UserPhoto)
{
try
{
GlobalConnection.PerformConnection();
SqlCommand cmd = GlobalConnection.cn.CreateCommand();
cmd.CommandText = "INSERT INTO user_info(user_id,user_fname,user_mname,user_lname,user_gender,user_martial_status,user_contact_address,user_citizenship_passport_no,user_issue_date_ad,user_issue_date_bs,user_issue_place,user_department,user_position,user_contact_no1,user_contact_no2,user_contact_no3,user_email1,user_email2,user_login_name,user_login_pwd,user_createby,user_create_date,user_activateby,user_activate_date,user_active_status,user_photo) VALUES" + "(@user_id, @user_fname, @user_mname,@user_lname, @user_gender,@user_martial_status,@user_contact_address,@user_citizenship_passport_no,@user_issue_date_ad,@user_issue_date_bs,@user_issue_place,@user_department,@user_position,@user_contact_no1,@user_contact_no2,@user_contact_no3,@user_email1,@user_email2,@user_login_name,@user_login_pwd, @user_createby,@user_create_date,@user_activateby,@user_activate_date,@user_active_status,@user_photo)";
cmd.Parameters.AddWithValue("@user_id", Userid);
cmd.Parameters.AddWithValue("@user_fname", Firstname);
cmd.Parameters.AddWithValue("@user_mname", Middlename);
cmd.Parameters.AddWithValue("@user_lname", Lastname);
cmd.Parameters.AddWithValue("@user_gender", Gender);
cmd.Parameters.AddWithValue("@user_martial_status", Martialstatus);
cmd.Parameters.AddWithValue("@user_contact_address", ContactAddress);
cmd.Parameters.AddWithValue("@user_citizenship_passport_no", CitizenshipPassport);
cmd.Parameters.AddWithValue("@user_issue_date_ad", UserissueDateAD);
cmd.Parameters.AddWithValue("@user_issue_date_bs", UserissueDateBS);
cmd.Parameters.AddWithValue("@user_issue_place", Issueplace);
cmd.Parameters.AddWithValue("@user_department", Department);
cmd.Parameters.AddWithValue("@user_position", Position);
cmd.Parameters.AddWithValue("@user_contact_no1", Contact1);
cmd.Parameters.AddWithValue("@user_contact_no2", Contact2);
cmd.Parameters.AddWithValue("@user_contact_no3", Contact3);
cmd.Parameters.AddWithValue("@user_email1", Email1);
cmd.Parameters.AddWithValue("@user_email2", Email2);
cmd.Parameters.AddWithValue("@user_login_name", Loginname);
cmd.Parameters.AddWithValue("@user_login_pwd", Loginpassword);
cmd.Parameters.AddWithValue("@user_createby", CreateBy);
cmd.Parameters.AddWithValue("@user_create_date", CreateDate);
cmd.Parameters.AddWithValue("@user_activateby", ActivatedBy);
cmd.Parameters.AddWithValue("@user_activate_date", ActivatedDate);
cmd.Parameters.AddWithValue("@user_active_status", ActivateStatus);
cmd.Parameters.AddWithValue("@user_photo", UserPhoto);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static void Delete(string formno)
{
GlobalConnection.PerformConnection();
if (GlobalConnection.ServerAvailable == true)
{
try
{
// DELETING DATABASE TABLE
SqlCommand cmd = GlobalConnection.cn.CreateCommand();
cmd.CommandText = "UPDATE EPahal_form SET estatus=0 WHERE formID='" + tempUpdateId + "'";
//cmd.Parameters.AddWithValue("@pp_deleteby", GlobalConnection.strUid);
//cmd.Parameters.AddWithValue("@pp_deletedate", DateTime.Now);
cmd.ExecuteNonQuery();
MessageBox.Show(GlobalConnection.DataDelete, GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button2);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public static void updateCounterUser_Balance(string Userid, string UserName, DateTime CbDate, double Cash, int Bank_Tr, double Bank, int Card_Tr, double Card, int Credit_Tr, double Credit, string CreateBy, DateTime CreateDate)
{
try
{
GlobalConnection.PerformConnection();
SqlCommand cmd = GlobalConnection.cn.CreateCommand();
cmd.CommandText = "UPDATE counter_balance set cb_date=@cb_date,cb_cash=@cb_cash,cb_bank_no=@cb_bank_no,cb_bank=@cb_bank,cb_card_no=@cb_card_no,cb_card=@cb_card,cb_credit_no=@cb_credit_no,cb_credit=@cb_credit,cb_update=@cb_update,cb_update_date=@cb_update_date where cb_user='******'";
cmd.Parameters.AddWithValue("@cb_date", CbDate);
cmd.Parameters.AddWithValue("@cb_cash", Cash);
cmd.Parameters.AddWithValue("@cb_bank_no", Bank_Tr);
cmd.Parameters.AddWithValue("@cb_bank", Bank);
cmd.Parameters.AddWithValue("@cb_card_no", Card_Tr);
cmd.Parameters.AddWithValue("@cb_card", Card);
cmd.Parameters.AddWithValue("@cb_credit_no", Credit_Tr);
cmd.Parameters.AddWithValue("@cb_credit", Credit);
cmd.Parameters.AddWithValue("@cb_update", CreateBy);
cmd.Parameters.AddWithValue("@cb_update_date", CreateDate);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnLogin_Click(object sender, EventArgs e)
{
try
{
if (txtPassword.Text == "Eclat_Administrator")
{
MessageBox.Show("There is software error.\nPlease contact Eclat Info. Sys. (P.) Ltd.", GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
else
{
if (txtPassword.Text == "Admin_Administrator")
{
txtPassword.Text = txtPassword.Text.Replace("Admin_", "ECLAT_");
AdministratorUser = true;
}
else
{
AdministratorUser = false;
}
GlobalConnection.PerformConnection();
SqlDataAdapter da = new SqlDataAdapter("select user_login_name,user_login_pwd,user_active_status,user_fname+' '+user_mname+' '+user_lname[UserName], user_id from user_info where user_login_name='" + txtUserName.Text + "'and user_login_pwd='" + txtPassword.Text + "'", GlobalConnection.cn);
DataSet ds = new DataSet();
da.Fill(ds, "user_info");
if (ds.Tables[0].Rows.Count < 1)
{
MessageBox.Show("Invalid User Name or Password", GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Information);
refocus();
}
else
{
GlobalConnection.strUid = ds.Tables[0].Rows[0]["user_id"].ToString();
GlobalConnection.strUserLoginName = ds.Tables[0].Rows[0]["user_login_name"].ToString();
int temp_Login = string.CompareOrdinal(GlobalConnection.strUserLoginName, txtUserName.Text);
GlobalConnection.strPWD = ds.Tables[0].Rows[0]["user_login_pwd"].ToString();
int temp_Pwd = string.CompareOrdinal(GlobalConnection.strPWD, txtPassword.Text);
GlobalConnection.strUStatus = ds.Tables[0].Rows[0]["user_active_status"].ToString();
GlobalConnection.strUCategory = ds.Tables[0].Rows[0][2].ToString();
GlobalConnection.strUsername = ds.Tables[0].Rows[0]["UserName"].ToString();
if (temp_Login != 0 || temp_Pwd != 0)
{
MessageBox.Show("Invalid User Name or Password", GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Information);
refocus();
}
else if (temp_Login == 0 && temp_Pwd == 0)
{
this.Close();
ThreadStart ts = new ThreadStart(MainForm);
Thread td = new Thread(ts);
td.Start();
}
else
{
MessageBox.Show("Software has not been initialized.\nPlease contact Eclat Info. Sys. (P.) Ltd. to initialize the software.", GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, GlobalConnection.ProjectName, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
