public static Result CustNew(CustViewModel model)
{
Result res = new Result();
int cif_id = 0;
try
{
//1. Insert customer data to table cust with status pending
//2. Generate Activation code and send to customer phone
//3. Insert acitvation code to cust_notif table
//4. Send result to back
#region 1. Insert Customer to Database
#region SQL
string sql = @"
BEGIN
SELECT @cif_id= COALESCE(MAX(cif_id),0)+1 from cust;
SELECT @contract_id= COALESCE(MAX(contract_id),0)+1 from cust_contract;
INSERT INTO INSERT INTO [dbo].[cust]
([cif_id]
,[cif_name]
,[cif_middle_name]
,[cif_last_name]
,[sex]
,[cif_address]
,[register_no]
,[birth_date]
,[phone]
,[home_phone]
,[social_id]
,[email]
,[off_address]
,[work_place]
,[work_phone]
,[work_address]
,[work_position]
,[profession]
,[reg_date]
,[reg_user]
,[status])
VALUES
(,@cif_id
,@cif_name
,@cif_middle_name
,@cif_last_name
,@sex
,@cif_address
,@register_no
,@birth_date
,@phone
,@home_phone
,@social_id
,@email
,@off_address
,@work_place
,@work_phone
,@work_address
,@work_position
,@profession
,@reg_date
,@reg_user
,@status
);
insert into cust_contract(contract_id, cif_id, contract_date, status, create_user, create_date)
values (@contract_id,@cif_id,@reg_date, 1, @reg_user,@reg_date);
END;";
#endregion
#region SQL Params
SqlParameter pCif_Id = new SqlParameter("@cif_id", SqlDbType.Int);
SqlParameter pCif_Name = new SqlParameter("@cif_name", SqlDbType.NVarChar, 50);
SqlParameter pCif_Middle_Name = new SqlParameter("@cif_middle_name", SqlDbType.NVarChar, 50);
SqlParameter pCif_Last_Name = new SqlParameter("@cif_last_name", SqlDbType.NVarChar, 50);
SqlParameter pSex = new SqlParameter("@sex", SqlDbType.Char, 1);
SqlParameter pCif_Address = new SqlParameter("@cif_address", SqlDbType.NVarChar, 250);
SqlParameter pRegister_No = new SqlParameter("@register_no", SqlDbType.NVarChar, 10);
SqlParameter pBirth_Date = new SqlParameter("@birth_date", SqlDbType.DateTime);
SqlParameter pPhone = new SqlParameter("@phone", SqlDbType.NVarChar, 50);
SqlParameter pHome_Phone = new SqlParameter("@home_phone", SqlDbType.NVarChar, 50);
SqlParameter pSocial_Id = new SqlParameter("@social_id", SqlDbType.NVarChar, 150);
SqlParameter pEmail = new SqlParameter("@email", SqlDbType.NVarChar, 50);
SqlParameter pOff_Address = new SqlParameter("@off_address", SqlDbType.NVarChar, 250);
SqlParameter pWork_Place = new SqlParameter("@work_place", SqlDbType.NVarChar, 100);
SqlParameter pWork_Phone = new SqlParameter("@work_phone", SqlDbType.NVarChar, 50);
SqlParameter pWork_Address = new SqlParameter("@work_address", SqlDbType.NVarChar, 250);
SqlParameter pWork_Position = new SqlParameter("@work_position", SqlDbType.NVarChar, 50);
SqlParameter pProfession = new SqlParameter("@profession", SqlDbType.NVarChar, 50);
SqlParameter pReg_Date = new SqlParameter("@reg_date", SqlDbType.DateTime);
SqlParameter pReg_User = new SqlParameter("@reg_user", SqlDbType.Int);
SqlParameter pStatus = new SqlParameter("@status", SqlDbType.Char, 1);
SqlParameter pContract_Id = new SqlParameter("@contract_id", SqlDbType.Int);
pCif_Id.Value = model.cif_id;
pCif_Name.Value = model.cif_name;
pCif_Middle_Name.Value = model.cif_middle_name;
pCif_Last_Name.Value = model.cif_last_name;
pSex.Value = model.sex;
pCif_Address.Value = model.cif_address;
pRegister_No.Value = model.register_no;
pBirth_Date.Value = model.birth_date;
pPhone.Value = model.phone;
pHome_Phone.Value = model.home_phone;
pSocial_Id.Value = model.social_id;
pEmail.Value = model.email;
pOff_Address.Value = model.off_address;
pWork_Place.Value = model.work_place;
pWork_Phone.Value = model.work_phone;
pWork_Address.Value = model.work_address;
pWork_Position.Value = model.work_position;
pProfession.Value = model.profession;
pReg_Date.Value = DateTime.Now;
pReg_User.Value = model.reg_user;
pStatus.Value = 1;
pCif_Id.Direction = ParameterDirection.InputOutput;
pContract_Id.Direction = ParameterDirection.InputOutput;
#endregion
res = App.ExecuteNonQuery(sql.ToString(), new SqlParameter[] {
pContract_Id
, pCif_Id
, pCif_Name
, pCif_Middle_Name
, pCif_Last_Name
, pSex
, pCif_Address
, pRegister_No
, pBirth_Date
, pPhone
, pHome_Phone
, pSocial_Id
, pEmail
, pOff_Address
, pWork_Place
, pWork_Phone
, pWork_Address
, pWork_Position
, pProfession
, pReg_Date
, pReg_User
, pStatus
}
, lang);
if (!res.Succeed)
{
res = App.getMsgRes(1002, lang);
return(res);
}
cif_id = Func.ToInt(pCif_Id.Value);
#endregion
res.Data = cif_id;
}
catch (Exception ex)
{
Main.ErrorLog("CustNew", ex);
res = App.getMsgRes(1003, lang);
}
return(res);
}
public static Result CustGet(string id)
{
CustViewModel model = new CustViewModel();
model.DisplayMode = "EditOnly";
try
{
#region SQL
string sql = @"Select c.*, convert(nvarchar, birth_date, 102) birthdate From cust c where status = 1 and cif_id = @cif_id";
#endregion
#region SQL Params
SqlParameter pCif_id = new SqlParameter("@cif_id", SqlDbType.NVarChar, 50);
pCif_id.Value = id;
#endregion
res = App.DataSetExecute(sql.ToString(), new SqlParameter[] { pCif_id }, lang);
if (res.Succeed)
{
DataTable dt = ((DataSet)res.Data).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
model.cif_id = Func.ToInt(dt.Rows[0]["cif_id"]);
model.cif_name = Func.ToStr(dt.Rows[0]["cif_name"]);
model.cif_middle_name = Func.ToStr(dt.Rows[0]["cif_middle_name"]);
model.cif_last_name = Func.ToStr(dt.Rows[0]["cif_last_name"]);
model.sex = Func.ToStr(dt.Rows[0]["sex"]);
model.cif_address = Func.ToStr(dt.Rows[0]["cif_address"]);
model.register_no = Func.ToStr(dt.Rows[0]["register_no"]);
model.birth_date = Func.ToStr(dt.Rows[0]["birthdate"]);
model.phone = Func.ToStr(dt.Rows[0]["phone"]);
model.home_phone = Func.ToStr(dt.Rows[0]["home_phone"]);
model.social_id = Func.ToStr(dt.Rows[0]["social_id"]);
model.email = Func.ToStr(dt.Rows[0]["email"]);
model.off_address = Func.ToStr(dt.Rows[0]["off_address"]);
model.work_place = Func.ToStr(dt.Rows[0]["work_place"]);
model.work_phone = Func.ToStr(dt.Rows[0]["work_phone"]);
model.work_address = Func.ToStr(dt.Rows[0]["work_address"]);
model.work_position = Func.ToStr(dt.Rows[0]["work_position"]);
model.profession = Func.ToStr(dt.Rows[0]["profession"]);
model.reg_date = Func.ToDateTime(dt.Rows[0]["reg_date"]);
model.reg_user = Func.ToInt(dt.Rows[0]["reg_user"]);
model.status = Func.ToStr(dt.Rows[0]["status"]);
model.last_edit_date = Func.ToDateTime(dt.Rows[0]["last_edit_date"]);
model.last_edit_user = Func.ToInt(dt.Rows[0]["last_edit_user"]);
}
else
{
res = App.getMsgRes(1005, lang); // Invoice not found
}
}
else
{
res = App.getMsgRes(1001, lang); // Error occured when get data
}
}
catch (Exception ex)
{
Main.ErrorLog("CustGet", ex);
res = App.getMsgRes(1003, lang);
}
res.Data = model;
return(res);
}
