public int Insert_client(clientdto client, clientRegisterdto clientRegister, UserLoginDTO ulDto)
{
return dangky.Register(client, clientRegister, ulDto);
}
public int Register(clientdto client, clientRegisterdto clientRegister, UserLoginDTO ulDto)
{
SqlTransaction tran = ConnectionData._MyConnection.BeginTransaction();
try
{
string sql = "insert into tblClient (clientName,address,email,phone,status)" +
"values(@clientName,@address,@email,@phone,@status) select @@identity";
SqlCommand cmd = new SqlCommand(sql, ConnectionData._MyConnection);
cmd.Transaction = tran;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@clientName", SqlDbType.NVarChar).Value = client.clientName;
cmd.Parameters.Add("@address", SqlDbType.NVarChar).Value = client.address;
cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = client.email;
cmd.Parameters.Add("@phone", SqlDbType.VarChar).Value = client.phone;
cmd.Parameters.Add("@status", SqlDbType.Int).Value = -1;
object id = cmd.ExecuteScalar();
clientRegister.clientId = Convert.ToInt32(id);
sql = @"set dateformat dmy INSERT INTO [tblClientRegister]
([clientId]
,[packageId]
,[limitId]
,[subAccontCount]
,[emailCount]
,[totalFee]
,[registerType]
,[packageTimeId]
,[from]
,[to]
,[lastRegisterFrom]
,[lastRegisterTo]
,[lastRegisterFee]
,[lastRegisterFeeRemain]
,[registerTime]
,[registerDate])
VALUES
(@clientId
,@packageId
,@limitId
,@subAccontCount
,@emailCount
,@totalFee
,0
,@packageTimeId
,@from
,@to
,null ,null ,0 ,0 ,getdate() ,getdate()) select @@identity";
cmd = new SqlCommand(sql, ConnectionData._MyConnection);
cmd.Transaction = tran;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@clientId", SqlDbType.Int).Value = clientRegister.clientId;
cmd.Parameters.Add("@packageId", SqlDbType.Int).Value = clientRegister.packageId;
cmd.Parameters.Add("@limitId", SqlDbType.Int).Value = clientRegister.limitId;
cmd.Parameters.Add("@subAccontCount", SqlDbType.Int).Value = clientRegister.subAccontCount;
cmd.Parameters.Add("@emailCount", SqlDbType.Int).Value = clientRegister.emailCount;
cmd.Parameters.Add("@totalFee", SqlDbType.Float).Value = clientRegister.totalFee;
cmd.Parameters.Add("@packageTimeId", SqlDbType.Int).Value = clientRegister.packageTimeId;
cmd.Parameters.Add("@from", SqlDbType.VarChar, 12).Value = clientRegister.from.ToString("dd/MM/yyyy");
cmd.Parameters.Add("@to", SqlDbType.VarChar, 12).Value = clientRegister.to.ToString("dd/MM/yyyy");
object registerid = cmd.ExecuteScalar();
sql = "select * from tblPackageFunction where packageid=" + clientRegister.packageId;
cmd = new SqlCommand(sql, ConnectionData._MyConnection);
cmd.Transaction = tran;
cmd.CommandType = CommandType.Text;
DataTable T = new DataTable();
SqlDataAdapter data = new SqlDataAdapter(cmd);
data.Fill(T);
sql = "insert into tblClientFunction(registerId, clientId, functionId) values(@registerId, @clientId, @functionId)";
if (T != null)
foreach (DataRow r in T.Rows)
{
cmd = new SqlCommand(sql, ConnectionData._MyConnection);
cmd.Transaction = tran;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@functionId", SqlDbType.Int).Value = r["functionId"];
cmd.Parameters.Add("@clientId", SqlDbType.Int).Value = clientRegister.clientId;
cmd.Parameters.Add("@registerId", SqlDbType.Int).Value = registerid;
cmd.ExecuteNonQuery();
}
sql = "INSERT INTO tblUserLogin(Username, Password, UserType,Is_Block,DepartmentId) " +
"VALUES(@Email, @Password, @UserType,@Is_Block,@UserType) select @@identity";
cmd = new SqlCommand(sql, ConnectionData._MyConnection);
cmd.Transaction = tran;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = ulDto.Email;
cmd.Parameters.Add("@Password", SqlDbType.NVarChar).Value = ulDto.Password;
cmd.Parameters.Add("@UserType", SqlDbType.Int).Value = ulDto.UserType;
cmd.Parameters.Add("@Is_Block", SqlDbType.Bit).Value = ulDto.Is_Block;
id = cmd.ExecuteScalar();
sql = "set dateformat dmy update tblClient set userid = @userid, registerid=@registerid, activedate=getdate(), expiredate='" + clientRegister.to.ToString("dd/MM/yyyy") + "' where clientid=@clientid";
cmd = new SqlCommand(sql, ConnectionData._MyConnection);
cmd.Transaction = tran;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@userid", SqlDbType.Int).Value = id;
cmd.Parameters.Add("@clientid", SqlDbType.Int).Value = clientRegister.clientId;
cmd.Parameters.Add("@registerid", SqlDbType.Int).Value = registerid;
int i=cmd.ExecuteNonQuery();
tran.Commit();
tran.Dispose();
return i;
}
catch
{
tran.Rollback();
}
return 0;
}
private clientdto getclient()
{
clientdto client = new clientdto();
client.clientName = txtUserName.Text;
client.address = txtDiaChi.Text;
client.email = txtEmail.Text;
client.phone = txtPhone.Text;
return client;
}