public static List <Customer_Details_Model> RetrieveData(SqlConnection connection, int idCustomer, string Customer_Code)
{
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"SELECT idCustomer
,Customer_Code
,Customer_Name
,Address1
,Address2
,Address3
,Address4
,AddressShipping1
,AddressShipping2
,AddressShipping3
,AddressShipping4
,credit_term
,Company_Name
FROM a_Customer_Details
WHERE idCustomer <> 0
");
if (idCustomer != 0)
{
sQuery.Append(" AND idCustomer = @idCustomer ");
}
if (Customer_Code.ToString() != "")
{
sQuery.Append(" AND Customer_Code = @Customer_Code ");
}
var lmodel = new List <Customer_Details_Model>();
connection.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
if (idCustomer.ToString() != "")
{
SqlParameter parm1 = new SqlParameter
{
ParameterName = "@idCustomer",
SqlDbType = SqlDbType.Int,
Value = idCustomer
};
cmd.Parameters.Add(parm1);
}
if (Customer_Code.ToString() != "")
{
SqlParameter parm1 = new SqlParameter
{
ParameterName = "@Customer_Code",
SqlDbType = SqlDbType.NVarChar,
Value = Customer_Code
};
cmd.Parameters.Add(parm1);
}
var oreader = cmd.ExecuteReader();
while (oreader.Read())
{
Customer_Details_Model oModel = new Customer_Details_Model
{
idCustomer = (int)oreader["idCustomer"],
Customer_Code = (string)oreader["Customer_Code"],
Customer_Name = (string)oreader["Customer_Name"],
Address1 = (string)oreader["Address1"],
Address2 = (string)oreader["Address2"],
Address3 = (string)oreader["Address3"],
Address4 = (string)oreader["Address4"],
credit_term = (string)oreader["credit_term"],
Company_Name = (string)oreader["Company_Name"],
AddressShipping1 = (string)oreader["AddressShipping1"],
AddressShipping2 = (string)oreader["AddressShipping2"],
AddressShipping3 = (string)oreader["AddressShipping3"],
AddressShipping4 = (string)oreader["AddressShipping4"]
};
lmodel.Add(oModel);
}
oreader.Close();
cmd.Dispose();
}
connection.Close();
return(lmodel);
}
public static bool Update(SqlConnection connection, Customer_Details_Model model)
{
bool returnValue = true;
var GUID = SQL_Transact.GenerateGUID();
SQL_Transact.BeginTransaction(connection, GUID);
StringBuilder sQuery = new StringBuilder();
sQuery.Append(@"UPDATE a_Customer_Details SET
Customer_Code = @Customer_Code
,Customer_Name = @Customer_Name
,Address1 = @Address1
,Address2 = @Address2
,Address3 = @Address3
,Address4 = @Address4
,credit_term = @credit_term
,Company_Name = @Company_Name
,Status = @Status
,AddressShipping1 = @AddressShipping1
,AddressShipping2 = @AddressShipping2
,AddressShipping3 = @AddressShipping3
,AddressShipping4 = @AddressShipping4
,user_chg_by = @user_chg_by
,Customer_Type = @Customer_Type
,Credit_Limit = @Credit_Limit
,Position = @Position
,Contact_Number = @Contact_Number
,TIN_Number = @TIN_Number
,Salesman = @Salesman
,Email_Address = @Email_Address
WHERE idCustomer = @idCustomer ");
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = connection;
cmd.CommandText = sQuery.ToString();
cmd.CommandType = CommandType.Text;
SqlParameter parm1 = new SqlParameter
{
ParameterName = "@idCustomer",
SqlDbType = SqlDbType.Int,
Value = model.idCustomer
};
cmd.Parameters.Add(parm1);
SqlParameter parm2 = new SqlParameter
{
ParameterName = "@Customer_Code",
SqlDbType = SqlDbType.NVarChar,
Value = model.Customer_Code
};
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter
{
ParameterName = "@Customer_Name",
SqlDbType = SqlDbType.NVarChar,
Value = model.Customer_Name
};
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter
{
ParameterName = "@Address1",
SqlDbType = SqlDbType.NVarChar,
Value = model.Address1
};
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter
{
ParameterName = "@Address2",
SqlDbType = SqlDbType.NVarChar,
Value = model.Address2
};
cmd.Parameters.Add(parm5);
SqlParameter parm6 = new SqlParameter
{
ParameterName = "@Address3",
SqlDbType = SqlDbType.NVarChar,
Value = model.Address3
};
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter
{
ParameterName = "@Address4",
SqlDbType = SqlDbType.NVarChar,
Value = model.Address4
};
cmd.Parameters.Add(parm7);
SqlParameter parm8 = new SqlParameter
{
ParameterName = "@credit_term",
SqlDbType = SqlDbType.NVarChar,
Value = model.credit_term
};
cmd.Parameters.Add(parm8);
SqlParameter parm9 = new SqlParameter
{
ParameterName = "@Company_Name",
SqlDbType = SqlDbType.NVarChar,
Value = model.Company_Name
};
cmd.Parameters.Add(parm9);
SqlParameter parm10 = new SqlParameter
{
ParameterName = "@Status",
SqlDbType = SqlDbType.NVarChar,
Value = model.Status
};
cmd.Parameters.Add(parm10);
SqlParameter parm11 = new SqlParameter
{
ParameterName = "@AddressShipping1",
SqlDbType = SqlDbType.NVarChar,
Value = model.AddressShipping1
};
cmd.Parameters.Add(parm11);
SqlParameter parm12 = new SqlParameter
{
ParameterName = "@AddressShipping2",
SqlDbType = SqlDbType.NVarChar,
Value = model.AddressShipping2
};
cmd.Parameters.Add(parm12);
SqlParameter parm13 = new SqlParameter
{
ParameterName = "@AddressShipping3",
SqlDbType = SqlDbType.NVarChar,
Value = model.AddressShipping3
};
cmd.Parameters.Add(parm13);
SqlParameter parm14 = new SqlParameter
{
ParameterName = "@AddressShipping4",
SqlDbType = SqlDbType.NVarChar,
Value = model.AddressShipping4
};
cmd.Parameters.Add(parm14);
SqlParameter parm15 = new SqlParameter
{
ParameterName = "@user_chg_by",
SqlDbType = SqlDbType.NVarChar,
Value = model.user_chg_by
};
cmd.Parameters.Add(parm15);
SqlParameter parm16 = new SqlParameter
{
ParameterName = "@Customer_Type",
SqlDbType = SqlDbType.NVarChar,
Value = model.Customer_Type
};
cmd.Parameters.Add(parm16);
SqlParameter parm17 = new SqlParameter
{
ParameterName = "@Credit_Limit",
SqlDbType = SqlDbType.NVarChar,
Value = model.Credit_Limit
};
cmd.Parameters.Add(parm17);
SqlParameter parm18 = new SqlParameter
{
ParameterName = "@Position",
SqlDbType = SqlDbType.NVarChar,
Value = model.Position
};
cmd.Parameters.Add(parm18);
SqlParameter parm19 = new SqlParameter
{
ParameterName = "@Contact_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.Contact_Number
};
cmd.Parameters.Add(parm19);
SqlParameter parm20 = new SqlParameter
{
ParameterName = "@TIN_Number",
SqlDbType = SqlDbType.NVarChar,
Value = model.TIN_Number
};
cmd.Parameters.Add(parm20);
SqlParameter parm21 = new SqlParameter
{
ParameterName = "@Salesman",
SqlDbType = SqlDbType.NVarChar,
Value = model.Salesman
};
cmd.Parameters.Add(parm21);
SqlParameter parm22 = new SqlParameter
{
ParameterName = "@Email_Address",
SqlDbType = SqlDbType.NVarChar,
Value = model.Email_Address
};
cmd.Parameters.Add(parm22);
if (cmd.ExecuteNonQuery() >= 1)
{
returnValue = true;
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.CommitTransaction(connection, GUID);
}
}
catch
{
cmd.Dispose();
cmd.Parameters.Clear();
SQL_Transact.RollbackTransaction(connection, GUID);
}
}
return(returnValue);
}
protected void btnSave_Click(object sender, EventArgs e)
{
var objCustomerDetails = new Customer_Details_Model();
if (txtCompanyName.Text != "")
{
string CustCode = Customer_Details.GetLastCustomerCode(oCon);
if (CustCode == null)
{
CustCode = "CC000000";
}
CustCode = CustCode.Substring(2);
int CustCode_ = int.Parse(CustCode) + 1;
string Customer_Code = "CC" + CustCode_.ToString("000000");
objCustomerDetails.Customer_Name = txtCustomerName.Text;
objCustomerDetails.Address1 = txtAddress1.Text;
objCustomerDetails.Address2 = txtAddress2.Text;
objCustomerDetails.Address3 = txtAddress3.Text;
objCustomerDetails.Address4 = txtAddress4.Text;
objCustomerDetails.credit_term = ddCreditTerm.SelectedItem.Text;
objCustomerDetails.Company_Name = txtCompanyName.Text;
objCustomerDetails.Status = ddStatus.SelectedItem.Text;
objCustomerDetails.AddressShipping1 = txtShipAddress1.Text;
objCustomerDetails.AddressShipping2 = txtShipAddress2.Text;
objCustomerDetails.AddressShipping3 = txtShipAddress3.Text;
objCustomerDetails.AddressShipping4 = txtShipAddress4.Text;
objCustomerDetails.user_chg_by = Session["User_Domain"].ToString();
objCustomerDetails.Customer_Type = ddCustomerType.SelectedItem.Text;
objCustomerDetails.Credit_Limit = txtCreditLimit.Text;
objCustomerDetails.Position = txtPosition.Text;
objCustomerDetails.Contact_Number = txtContactNo.Text;
objCustomerDetails.TIN_Number = txtTINNo.Text;
objCustomerDetails.Salesman = ddSalesman.SelectedItem.Text;
objCustomerDetails.Email_Address = txtEmailAddress.Text;
if (Session["Mode"].ToString() == "A")
{
objCustomerDetails.Customer_Code = Customer_Code;
Customer_Details.Save(oCon, objCustomerDetails);
}
else if (Session["Mode"].ToString() == "E")
{
objCustomerDetails.idCustomer = Convert.ToInt32(Session["idCustomer"]);
objCustomerDetails.Customer_Code = Session["CustomerCode"].ToString();
Customer_Details.Update(oCon, objCustomerDetails);
}
var objLogs = new Logs_Model();
objLogs.idUser = Users.GetUserIDByDomainLogin(oCon, Session["User_Domain"].ToString());
objLogs.Form = "Customer Maintenance";
if (Session["Mode"].ToString() == "A")
{
objLogs.Description = "Save Record: Customer_Code = " + Customer_Code + "";
}
if (Session["Mode"].ToString() == "E")
{
objLogs.Description = "Update Record: Customer_Code = " + Session["CustomerCode"].ToString() + "";
}
Logs.Save(oCon, objLogs);
defaultSettings();
GetCustomerDetails();
if (Session["Mode"].ToString() == "A")
{
HttpContext.Current.Response.Write("<script>alert(" + ControlChars.Quote + "Customer record has been saved. Customer Code : " + Customer_Code + ControlChars.Quote + ");</script>");
}
else if (Session["Mode"].ToString() == "E")
{
HttpContext.Current.Response.Write("<script>alert(" + ControlChars.Quote + "Update Successful. Customer Code : " + Session["CustomerCode"].ToString() + ControlChars.Quote + ");</script>");
}
}
}