public MemberInfoDTO populate(SqlDataReader reader)
{
try
{
MemberInfoDTO dto = new MemberInfoDTO();
dto.PrimaryKey = (Guid)reader["Cust_PK"];
dto.CustId = (string)reader["CustId"];
dto.ManualId = (string)reader["ManualId"];
dto.CustName = (string)reader["CustName"];
dto.CustAtten = (string)reader["CustAtten"];
dto.ContactPerson = (string)reader["ContactPerson"];
dto.CustAddr = (string)reader["CustAddr"];
dto.CustPhone = (string)reader["CustPhone"];
dto.CustFax = (string)reader["CustFax"];
dto.CustEmail = (string)reader["CustEmail"];
dto.WebSite = (string)reader["WebSite"];
dto.BirthDay = (DateTime)reader["BirthDay"];
dto.Mobile = (string)reader["Mobile"];
dto.CreditLimit = (Decimal)reader["CreditLimit"];
dto.EntryBy = (string)reader["EntryBy"];
dto.EntryDate = (DateTime)reader["EntryDate"];
return(dto);
}
catch (Exception ex)
{
throw ex;
}
}
// Error Handling developed by Samad
// 07/12/06
private void getPKCode(MemberInfoDTO obj)
{
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DPOSConnectionString"].ToString());
string strPKCode = null;
int PKSL = 0;
string sqlSelect = "Select isnull(Max(CustId),0)+1 From MemberInfo";
SqlCommand objCmd = sqlConn.CreateCommand();
objCmd.CommandText = sqlSelect;
try
{
sqlConn.Open();
PKSL = (int)objCmd.ExecuteScalar();
}
catch (Exception Exp)
{
throw Exp;
}
finally
{
objCmd.Dispose();
objCmd.Cancel();
sqlConn.Dispose();
sqlConn.Close();
}
strPKCode = PKSL.ToString("000000");
obj.CustId = strPKCode;
//return strPKCode;
}
/// <summary>
/// Delete row
/// </summary>
/// <param name="obj"></param>
public override void Delete(object obj)
{
MemberInfoDTO oMemberInfoDTO = (MemberInfoDTO)obj;
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DPOSConnectionString"].ToString());
String sql = "Delete MemberInfo where Cust_PK=@Cust_PK";
SqlCommand objCmdDelete = sqlConn.CreateCommand();
objCmdDelete.CommandText = sql;
try
{
objCmdDelete.Parameters.Add("@Cust_PK", SqlDbType.UniqueIdentifier, 16);
objCmdDelete.Parameters["@Cust_PK"].Value = (Guid)oMemberInfoDTO.PrimaryKey;
sqlConn.Open();
objCmdDelete.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
objCmdDelete.Dispose();
objCmdDelete.Cancel();
sqlConn.Dispose();
sqlConn.Close();
}
}
public MemberInfoDTO populate()
{
try
{
MemberInfoDTO dto = new MemberInfoDTO();
if (this.txtHideFieldPK.Value != "")
{
dto.PrimaryKey = (Guid)TypeDescriptor.GetConverter(dto.PrimaryKey).ConvertFromString(this.txtHideFieldPK.Value);
}
dto.CustId = this.txtCode.Text;
dto.ManualId = this.txtManualCode.Text;
dto.CustName = this.txtName.Text;
dto.CustAtten = this.txtAttention.Text;
dto.ContactPerson = this.txtContactPerson.Text;
dto.CustAddr = this.txtAddress.Text;
dto.CustPhone = this.txtPhone.Text;
dto.Mobile = this.txtMobile.Text;
dto.CustFax = this.txtFax.Text;
dto.CustEmail = this.txtEmail.Text;
dto.WebSite = this.txtWebsite.Text;
if (this.txtBirthday.Text.Length != 0)
{
dto.BirthDay = Convert.ToDateTime(this.txtBirthday.Text);
}
else
{
dto.BirthDay = Convert.ToDateTime("01/01/1900");
}
if (this.txtCreditLimit.Text.Trim().Length == 0)
{
dto.CreditLimit = 0;
}
else
{
dto.CreditLimit = (Decimal)TypeDescriptor.GetConverter(dto.CreditLimit).ConvertFromString(this.txtCreditLimit.Text);
}
dto.EntryBy = "Admin";
dto.EntryDate = DateTime.Now.Date;
return(dto);
}
catch (Exception Exp)
{
throw Exp;
}
}
private MemberInfoDTO VerifyMemberInfo(int memberId, decimal amount, string memberPwd, SqlSugarClient db = null)
{
string connString = Connection;
if (EnabelGroupFlag)
{
connString = ConnentionGroup;
}
bool isOutside = false;
if (db == null)
{
db = new SqlSugarClient(connString);
isOutside = true;
}
MemberInfoDTO member = null;
try
{
//搜索会员信息
string memberSql = string.Format(@"
SELECT krlsxh00 AS Id, krlsvpkh AS MemberCardNo, krlsmm00 AS MemberPwbByte, krlsye00 AS CardBalance,
krlszjhm AS MemberIdentityNo, krlsmm00 AS MemberPwbByte, krlsdh00 AS MemberPhoneNo,
LTRIM(RTRIM(krlszwxm)) AS MemberName, krlsGPID AS MemberGPID, krlsGUID AS MemberGUID, krlsxb00 AS MemberGender, krlsdh00 as MemberPhoneNo
FROM krls WHERE krlsxh00 = {0}", memberId);
var members = db.SqlQuery <MemberInfoDTO>(memberSql);
if (members == null || members.Count == 0)
{
throw new Exception("客户信息无效！");
}
member = members[0];
}
catch (Exception ex)
{
throw ex;
}
if (isOutside)
{
db.Dispose();
}
return(member);
}
/// <summary>
/// This method used for get all data corresponding primary key.
/// </summary>
/// <param name="pk"></param>
/// <returns></returns>
///
public MemberInfoDTO findByPKDTO(Guid pk)
{
MemberInfoDTO oMemberInfoDTO = new MemberInfoDTO();
string sqlSelect = "Select Cust_PK,CustId,ManualId,CustName,CustAtten,ContactPerson,CustAddr," +
" CustPhone,CustFax,CustEmail,WebSite,BirthDay,Mobile,CreditLimit,EntryBy,EntryDate From MemberInfo where Cust_PK=@Cust_PK";
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DPOSConnectionString"].ToString());
SqlCommand objCmd = sqlConn.CreateCommand();
objCmd.CommandText = sqlSelect;
objCmd.Connection = sqlConn;
try
{
objCmd.Parameters.Add("@Cust_PK", SqlDbType.UniqueIdentifier, 16);
objCmd.Parameters["@Cust_PK"].Value = pk;
sqlConn.Open();
SqlDataReader thisReader = objCmd.ExecuteReader();
while (thisReader.Read())
{
oMemberInfoDTO = populate(thisReader);
}
thisReader.Close();
thisReader.Dispose();
}
catch (Exception ex)
{
throw ex;
}
finally
{
objCmd.Dispose();
objCmd.Cancel();
sqlConn.Dispose();
sqlConn.Close();
}
return(oMemberInfoDTO);
}
public List <MemberInfoDTO> getMemberInfoData()
{
string sqlSelect = "Select Cust_PK,CustId,ManualId,CustName,CustAtten,ContactPerson,CustAddr," +
" CustPhone,CustFax,CustEmail,WebSite,BirthDay,Mobile,CreditLimit,EntryBy,EntryDate From MemberInfo order by CustId Asc";
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DPOSConnectionString"].ToString());
List <MemberInfoDTO> oArrayList = new List <MemberInfoDTO>();
SqlCommand objCmd = sqlConn.CreateCommand();
objCmd.CommandText = sqlSelect;
objCmd.Connection = sqlConn;
try
{
sqlConn.Open();
SqlDataReader thisReader = objCmd.ExecuteReader();
while (thisReader.Read())
{
MemberInfoDTO oMemberInfoDTO = populate(thisReader);
oArrayList.Add(oMemberInfoDTO);
}
thisReader.Close();
thisReader.Dispose();
}
catch (Exception ex)
{
throw ex;
}
finally
{
objCmd.Dispose();
objCmd.Cancel();
sqlConn.Dispose();
sqlConn.Close();
}
return(oArrayList);
}
protected void btnSave_Click(object sender, EventArgs e)
{
if (this.lblErrorMessage.Text.Length != 0)
{
this.lblErrorMessage.Text = "";
}
try
{
//string showme = txtBirthday.Text;
MemberInfoDTO dto = populate();
Facade facade = Facade.GetInstance();
IMemberInfoBL oIMemberInfoBL = facade.createMemberBL();
oIMemberInfoBL.addNewMemberRecord(dto);
this.lblErrorMessage.Text = "Data Save Successfully.";
this.txtHideFieldPK.Value = "";
this.txtCode.Text = "";
this.txtManualCode.Text = "";
this.txtName.Text = "";
this.txtAttention.Text = "";
this.txtContactPerson.Text = "";
this.txtAddress.Text = "";
this.txtPhone.Text = "";
this.txtMobile.Text = "";
this.txtFax.Text = "";
this.txtEmail.Text = "";
this.txtWebsite.Text = "";
this.txtBirthday.Text = "";
this.txtCreditLimit.Text = "";
this.btnSave.Text = "Save";
this.GridView1.DataBind();
}
catch (Exception Exp)
{
lblErrorMessage.Text = cls.ErrorString(Exp);
}
}
protected void GridView1_RowEdting(object sender, GridViewCommandEventArgs e)
{
try
{
if (e.CommandName == "Edit")
{
MemberInfoDTO dto = new MemberInfoDTO();
int index = Convert.ToInt32(e.CommandArgument);
GridViewRow row = GridView1.Rows[index];
DataKey xx = this.GridView1.DataKeys[index];
this.txtHideFieldPK.Value = xx.Value.ToString();
Facade facade = Facade.GetInstance();
dto = facade.getMemberInfo((Guid)TypeDescriptor.GetConverter(dto.PrimaryKey).ConvertFromString(this.txtHideFieldPK.Value));
this.txtCode.Text = dto.CustId;
this.txtManualCode.Text = dto.ManualId;
this.txtName.Text = dto.CustName;
this.txtAttention.Text = dto.CustAtten;
this.txtContactPerson.Text = dto.ContactPerson;
this.txtAddress.Text = dto.CustAddr;
this.txtPhone.Text = dto.CustPhone;
this.txtMobile.Text = dto.Mobile;
this.txtFax.Text = dto.CustFax;
this.txtEmail.Text = dto.CustEmail;
this.txtWebsite.Text = dto.WebSite;
this.txtBirthday.Text = dto.BirthDay.ToString("MM/dd/yyyy");
this.txtCreditLimit.Text = dto.CreditLimit.ToString();
this.btnSave.Text = "Update";
}
}
catch (Exception Exp)
{
lblErrorMessage.Text = cls.ErrorString(Exp);
}
}
/// <summary>
/// set Sales Main information in Domain Class
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
public SalesReturnMainDTO populate(SqlDataReader reader)
{
try
{
SalesReturnMainDTO dto = new SalesReturnMainDTO();
SalesMainInfoDTO oSalesMainInfoDTO = new SalesMainInfoDTO();
MemberInfoDTO oMemberInfoDTO = new MemberInfoDTO();
oSalesMainInfoDTO.PrimaryKey = (Guid)reader["Sal_Pk"];
oSalesMainInfoDTO.DeliveryNo = (string)reader["DeliveryNo"];
oSalesMainInfoDTO.DeliveryDate = (DateTime)reader["DeliveryDate"];
oSalesMainInfoDTO.Cust_PK = (Guid)reader["Cust_PK"];
oSalesMainInfoDTO.TotalAmount = (decimal)reader["TotalAmount"];
oSalesMainInfoDTO.PaidAmount = (decimal)reader["PaidAmount"];
oSalesMainInfoDTO.DueAmount = (decimal)reader["DueAmount"];
oSalesMainInfoDTO.TotalDiscount = (decimal)reader["TotalDiscount"];
oSalesMainInfoDTO.PaymentAmount = (decimal)reader["PaymentAmount"];
oSalesMainInfoDTO.PaymentMode = (string)reader["PaymentMode"];
oSalesMainInfoDTO.Bo_PK = (Guid)reader["Bo_PK"];
oSalesMainInfoDTO.Remarks = (string)reader["Remarks"];
oSalesMainInfoDTO.EntryBy = (string)reader["EntryBy"];
oSalesMainInfoDTO.EntryDate = (DateTime)reader["EntryDate"];
dto.SalesMainInfoDTO = oSalesMainInfoDTO;
oMemberInfoDTO.PrimaryKey = (Guid)reader["Cust_PK"];
oMemberInfoDTO.CustId = (string)reader["CustId"];
oMemberInfoDTO.CustName = (string)reader["CustName"];
oMemberInfoDTO.CustAddr = (string)reader["CustAddr"];
oMemberInfoDTO.CreditLimit = (Decimal)reader["CreditLimit"];
dto.MemberInfoDTO = oMemberInfoDTO;
return(dto);
}
catch (Exception ex)
{
throw ex;
}
}
public override void Save(object obj)
{
MemberInfoDTO oMemberInfoDTO = (MemberInfoDTO)obj;
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DPOSConnectionString"].ToString());
SqlCommand objCmd = sqlConn.CreateCommand();
if (oMemberInfoDTO.PrimaryKey.ToString() == "00000000-0000-0000-0000-000000000000")
{
String sql = "Insert Into MemberInfo(CustId,ManualId,CustName,CustAtten,ContactPerson,CustAddr," +
" CustPhone,CustFax,CustEmail,WebSite,BirthDay,Mobile,CreditLimit,EntryBy,EntryDate)" +
" values(@CustId,@ManualId,@CustName,@CustAtten,@ContactPerson,@CustAddr,@CustPhone," +
" @CustFax,@CustEmail,@WebSite,@BirthDay,@Mobile,@CreditLimit,@EntryBy,@EntryDate)";
try
{
getPKCode(oMemberInfoDTO);
objCmd.CommandText = sql;
objCmd.Parameters.Add(new SqlParameter("@CustId", SqlDbType.VarChar, 6));
objCmd.Parameters.Add(new SqlParameter("@ManualId", SqlDbType.VarChar, 20));
objCmd.Parameters.Add(new SqlParameter("@CustName", SqlDbType.VarChar, 100));
objCmd.Parameters.Add(new SqlParameter("@CustAtten", SqlDbType.VarChar, 5));
objCmd.Parameters.Add(new SqlParameter("@ContactPerson", SqlDbType.VarChar, 50));
objCmd.Parameters.Add(new SqlParameter("@CustAddr", SqlDbType.VarChar, 150));
objCmd.Parameters.Add(new SqlParameter("@CustPhone", SqlDbType.VarChar, 50));
objCmd.Parameters.Add(new SqlParameter("@CustFax", SqlDbType.VarChar, 50));
objCmd.Parameters.Add(new SqlParameter("@CustEmail", SqlDbType.VarChar, 50));
objCmd.Parameters.Add(new SqlParameter("@WebSite", SqlDbType.VarChar, 70));
objCmd.Parameters.Add(new SqlParameter("@BirthDay", SqlDbType.DateTime));
objCmd.Parameters.Add(new SqlParameter("@Mobile", SqlDbType.VarChar, 50));
objCmd.Parameters.Add(new SqlParameter("@CreditLimit", SqlDbType.Decimal, 9));
objCmd.Parameters.Add(new SqlParameter("@EntryBy", SqlDbType.VarChar, 10));
objCmd.Parameters.Add(new SqlParameter("@EntryDate", SqlDbType.DateTime));
objCmd.Parameters["@CustId"].Value = Convert.ToString(oMemberInfoDTO.CustId);
objCmd.Parameters["@ManualId"].Value = Convert.ToString(oMemberInfoDTO.ManualId);
objCmd.Parameters["@CustName"].Value = Convert.ToString(oMemberInfoDTO.CustName);
objCmd.Parameters["@CustAtten"].Value = Convert.ToString(oMemberInfoDTO.CustAtten);
objCmd.Parameters["@ContactPerson"].Value = Convert.ToString(oMemberInfoDTO.ContactPerson);
objCmd.Parameters["@CustAddr"].Value = Convert.ToString(oMemberInfoDTO.CustAddr);
objCmd.Parameters["@CustPhone"].Value = Convert.ToString(oMemberInfoDTO.CustPhone);
objCmd.Parameters["@CustFax"].Value = Convert.ToString(oMemberInfoDTO.CustFax);
objCmd.Parameters["@CustEmail"].Value = Convert.ToString(oMemberInfoDTO.CustEmail);
objCmd.Parameters["@WebSite"].Value = Convert.ToString(oMemberInfoDTO.WebSite);
if (oMemberInfoDTO.BirthDay == null)
{
objCmd.Parameters["@BirthDay"].Value = "<NULL>";
}
else
{
objCmd.Parameters["@BirthDay"].Value = Convert.ToString(oMemberInfoDTO.BirthDay);
}
objCmd.Parameters["@Mobile"].Value = Convert.ToString(oMemberInfoDTO.Mobile);
objCmd.Parameters["@CreditLimit"].Value = Convert.ToString(oMemberInfoDTO.CreditLimit);
objCmd.Parameters["@EntryBy"].Value = Convert.ToString(oMemberInfoDTO.EntryBy);
objCmd.Parameters["@EntryDate"].Value = Convert.ToDateTime(oMemberInfoDTO.EntryDate);
sqlConn.Open();
objCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
objCmd.Dispose();
objCmd.Cancel();
sqlConn.Close();
sqlConn.Dispose();
}
}
else
{
String sql = "Update MemberInfo set " +
" CustId=@CustId,ManualId=@ManualId,CustName=@CustName,CustAtten=@CustAtten,ContactPerson=@ContactPerson," +
" CustAddr=@CustAddr,CustPhone=@CustPhone,CustFax=@CustFax,CustEmail=@CustEmail,WebSite=@WebSite," +
" BirthDay=@BirthDay,Mobile=@Mobile,CreditLimit=@CreditLimit,EntryBy=@EntryBy,EntryDate=@EntryDate" +
" where Cust_PK=@Cust_PK";
try
{
objCmd.CommandText = sql;
objCmd.Parameters.Add(new SqlParameter("@Cust_PK", SqlDbType.UniqueIdentifier, 16));
objCmd.Parameters.Add(new SqlParameter("@CustId", SqlDbType.VarChar, 6));
objCmd.Parameters.Add(new SqlParameter("@ManualId", SqlDbType.VarChar, 20));
objCmd.Parameters.Add(new SqlParameter("@CustName", SqlDbType.VarChar, 100));
objCmd.Parameters.Add(new SqlParameter("@CustAtten", SqlDbType.VarChar, 5));
objCmd.Parameters.Add(new SqlParameter("@ContactPerson", SqlDbType.VarChar, 50));
objCmd.Parameters.Add(new SqlParameter("@CustAddr", SqlDbType.VarChar, 150));
objCmd.Parameters.Add(new SqlParameter("@CustPhone", SqlDbType.VarChar, 50));
objCmd.Parameters.Add(new SqlParameter("@CustFax", SqlDbType.VarChar, 50));
objCmd.Parameters.Add(new SqlParameter("@CustEmail", SqlDbType.VarChar, 50));
objCmd.Parameters.Add(new SqlParameter("@WebSite", SqlDbType.VarChar, 70));
objCmd.Parameters.Add(new SqlParameter("@BirthDay", SqlDbType.DateTime));
objCmd.Parameters.Add(new SqlParameter("@Mobile", SqlDbType.VarChar, 50));
objCmd.Parameters.Add(new SqlParameter("@CreditLimit", SqlDbType.Decimal, 9));
objCmd.Parameters.Add(new SqlParameter("@EntryBy", SqlDbType.VarChar, 10));
objCmd.Parameters.Add(new SqlParameter("@EntryDate", SqlDbType.DateTime));
objCmd.Parameters["@Cust_PK"].Value = (Guid)oMemberInfoDTO.PrimaryKey;
objCmd.Parameters["@CustId"].Value = Convert.ToString(oMemberInfoDTO.CustId);
objCmd.Parameters["@ManualId"].Value = Convert.ToString(oMemberInfoDTO.ManualId);
objCmd.Parameters["@CustName"].Value = Convert.ToString(oMemberInfoDTO.CustName);
objCmd.Parameters["@CustAtten"].Value = Convert.ToString(oMemberInfoDTO.CustAtten);
objCmd.Parameters["@ContactPerson"].Value = Convert.ToString(oMemberInfoDTO.ContactPerson);
objCmd.Parameters["@CustAddr"].Value = Convert.ToString(oMemberInfoDTO.CustAddr);
objCmd.Parameters["@CustPhone"].Value = Convert.ToString(oMemberInfoDTO.CustPhone);
objCmd.Parameters["@CustFax"].Value = Convert.ToString(oMemberInfoDTO.CustFax);
objCmd.Parameters["@CustEmail"].Value = Convert.ToString(oMemberInfoDTO.CustEmail);
objCmd.Parameters["@WebSite"].Value = Convert.ToString(oMemberInfoDTO.WebSite);
objCmd.Parameters["@BirthDay"].Value = Convert.ToString(oMemberInfoDTO.BirthDay);
objCmd.Parameters["@Mobile"].Value = Convert.ToString(oMemberInfoDTO.Mobile);
objCmd.Parameters["@CreditLimit"].Value = Convert.ToString(oMemberInfoDTO.CreditLimit);
objCmd.Parameters["@EntryBy"].Value = Convert.ToString(oMemberInfoDTO.EntryBy);
objCmd.Parameters["@EntryDate"].Value = Convert.ToDateTime(oMemberInfoDTO.EntryDate);
objCmd.Parameters["@EntryDate"].Value = Convert.ToDateTime(oMemberInfoDTO.EntryDate);
sqlConn.Open();
objCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
objCmd.Dispose();
objCmd.Cancel();
sqlConn.Dispose();
sqlConn.Close();
}
}
}
