public static DataSet Service_ContractExpiredList(Entity.Service.Contract contract)
{
using (DataSet dt = new DataSet())
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString()))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "usp_Service_ContractExpiredList";
if (contract.MachineId == "")
{
cmd.Parameters.AddWithValue("@MachineId", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@MachineId", contract.MachineId);
}
if (contract.FromDate == DateTime.MinValue)
{
cmd.Parameters.AddWithValue("@FromDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@FromDate", contract.FromDate);
}
if (contract.ToDate == DateTime.MinValue)
{
cmd.Parameters.AddWithValue("@ToDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@ToDate", contract.ToDate);
}
if (contract.AssignEngineer == 0)
{
cmd.Parameters.AddWithValue("@AssignEngineer", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@AssignEngineer", contract.AssignEngineer);
}
cmd.InsertPaging(contract, contract.ContractId);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dt);
}
con.Close();
}
}
return(dt);
}
}
protected void LoadGetAllContractStatus()
{
Entity.Service.Contract contract = new Entity.Service.Contract();
Business.Service.Contract objContract = new Business.Service.Contract();
contract.MachineId = txtMachineId.Text.Trim();
contract.FromDate = (txtFromContractDate.Text == "") ? DateTime.MinValue : Convert.ToDateTime(txtFromContractDate.Text.Trim());
contract.ToDate = (txtToContractDate.Text == "") ? DateTime.MinValue : Convert.ToDateTime(txtToContractDate.Text.Trim());
contract.ProductSerialNo = txtProductSerialNo.Text.Trim();
if (HttpContext.Current.User.IsInRole(Entity.HR.Utility.CUSTOMER_LIST_SHOW_ALL))
{
contract.AssignEngineer = 0;
}
else
{
contract.AssignEngineer = int.Parse(HttpContext.Current.User.Identity.Name);
}
DataSet ds = objContract.Service_GetAllContractStatus(contract);
DataTable dtExport = new DataTable();
if (ddlContractStatus.SelectedValue == ((int)ContractStatusType.None).ToString())
{
dtExport = null;
}
else if (ddlContractStatus.SelectedValue == ((int)ContractStatusType.Expiring).ToString())
{
dtExport = ds.Tables[0];
string csv = dtExport.ExportCSV();
DownloadCsv(csv);
}
else if (ddlContractStatus.SelectedValue == ((int)ContractStatusType.Expired).ToString())
{
dtExport = ds.Tables[1];
string csv = dtExport.ExportCSV();
DownloadCsv(csv);
}
else if (ddlContractStatus.SelectedValue == ((int)ContractStatusType.InContract).ToString())
{
dtExport = ds.Tables[2];
string csv = dtExport.ExportCSV();
DownloadCsv(csv);
}
else if (ddlContractStatus.SelectedValue == ((int)ContractStatusType.NeverContracted).ToString())
{
dtExport = ds.Tables[3];
string csv = dtExport.ExportCSV();
DownloadCsv(csv);
}
else
{
dtExport = null;
}
}
protected void LoadContractStatusList(int pageIndex, int pageSize)
{
Entity.Service.Contract contract = new Entity.Service.Contract();
Business.Service.Contract objContract = new Business.Service.Contract();
contract.PageIndex = pageIndex;
contract.PageSize = pageSize;
contract.MachineId = txtMachineId.Text.Trim();
contract.FromDate = (txtFromContractDate.Text == "") ? DateTime.MinValue : Convert.ToDateTime(txtFromContractDate.Text.Trim());
contract.ToDate = (txtToContractDate.Text == "") ? DateTime.MinValue : Convert.ToDateTime(txtToContractDate.Text.Trim());
contract.ProductSerialNo = txtProductSerialNo.Text.Trim();
if (HttpContext.Current.User.IsInRole(Entity.HR.Utility.CUSTOMER_LIST_SHOW_ALL))
{
contract.AssignEngineer = 0;
}
else
{
contract.AssignEngineer = int.Parse(HttpContext.Current.User.Identity.Name);
}
DataSet ds = objContract.Service_ContractStatusList(contract);
if (ddlContractStatus.SelectedValue == ((int)ContractStatusType.None).ToString())
{
gvContractStatusList.DataSource = null;
}
else if (ddlContractStatus.SelectedValue == ((int)ContractStatusType.Expiring).ToString())
{
gvContractStatusList.DataSource = ds.Tables[0];
gvContractStatusList.VirtualItemCount = (ds.Tables[4].Rows.Count > 0) ? Convert.ToInt32(ds.Tables[4].Rows[0]["TotalCount"].ToString()) : 15;
}
else if (ddlContractStatus.SelectedValue == ((int)ContractStatusType.Expired).ToString())
{
gvContractStatusList.DataSource = ds.Tables[1];
gvContractStatusList.VirtualItemCount = (ds.Tables[5].Rows.Count > 0) ? Convert.ToInt32(ds.Tables[5].Rows[0]["TotalCount"].ToString()) : 15;
}
else if (ddlContractStatus.SelectedValue == ((int)ContractStatusType.InContract).ToString())
{
gvContractStatusList.DataSource = ds.Tables[2];
gvContractStatusList.VirtualItemCount = (ds.Tables[6].Rows.Count > 0) ? Convert.ToInt32(ds.Tables[6].Rows[0]["TotalCount"].ToString()) : 15;
}
else if (ddlContractStatus.SelectedValue == ((int)ContractStatusType.NeverContracted).ToString())
{
gvContractStatusList.DataSource = ds.Tables[3];
gvContractStatusList.VirtualItemCount = (ds.Tables[7].Rows.Count > 0) ? Convert.ToInt32(ds.Tables[7].Rows[0]["TotalCount"].ToString()) : 15;
}
else
{
gvContractStatusList.DataSource = null;
}
gvContractStatusList.DataBind();
}
private void ContractSave()
{
Business.Service.Contract objContract = new Business.Service.Contract();
Entity.Service.Contract contract = new Entity.Service.Contract();
contract.CreatedBy = int.Parse(HttpContext.Current.User.Identity.Name);
contract.ContractId = 0;
contract.CustomerId = CustomerMasterId;
contract.ContractTypeId = int.Parse(ddlContractType.SelectedValue);
contract.ContractStartDate = (!String.IsNullOrEmpty(txtContractStartDate.Text.Trim())) ? Convert.ToDateTime(txtContractStartDate.Text.Trim()) : DateTime.MinValue;
contract.ContractEndDate = (!String.IsNullOrEmpty(txtContractEndDate.Text.Trim())) ? Convert.ToDateTime(txtContractEndDate.Text.Trim()) : DateTime.MinValue;
using (DataTable dtContractDetails = new DataTable())
{
dtContractDetails.Columns.Add("CustomerPurchaseId");
dtContractDetails.Columns.Add("A3BWStartMeter");
dtContractDetails.Columns.Add("A4BWStartMeter");
dtContractDetails.Columns.Add("A3CLStartMeter");
dtContractDetails.Columns.Add("A4CLStartMeter");
dtContractDetails.Columns.Add("A3BWPages");
dtContractDetails.Columns.Add("A4BWPages");
dtContractDetails.Columns.Add("A3CLPages");
dtContractDetails.Columns.Add("A4CLPages");
dtContractDetails.Columns.Add("A3BWRate");
dtContractDetails.Columns.Add("A4BWRate");
dtContractDetails.Columns.Add("A3CLRate");
dtContractDetails.Columns.Add("A4CLRate");
foreach (GridViewRow gvr in gvCustomerPurchaseListForContract.Rows)
{
if (((CheckBox)gvr.FindControl("chkSelect")).Checked)
{
dtContractDetails.Rows.Add();
dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["CustomerPurchaseId"] = gvCustomerPurchaseListForContract.DataKeys[gvr.RowIndex].Values[0].ToString();
dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A3BWStartMeter"] = (!String.IsNullOrEmpty(((TextBox)gvr.FindControl("txtA3BWStartMeter")).Text.Trim())) ? ((TextBox)gvr.FindControl("txtA3BWStartMeter")).Text.Trim() : "";
dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A4BWStartMeter"] = (!String.IsNullOrEmpty(((TextBox)gvr.FindControl("txtA4BWStartMeter")).Text.Trim())) ? ((TextBox)gvr.FindControl("txtA4BWStartMeter")).Text.Trim() : "";
dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A3CLStartMeter"] = (!String.IsNullOrEmpty(((TextBox)gvr.FindControl("txtA3CLStartMeter")).Text.Trim())) ? ((TextBox)gvr.FindControl("txtA3CLStartMeter")).Text.Trim() : "";
dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A4CLStartMeter"] = (!String.IsNullOrEmpty(((TextBox)gvr.FindControl("txtA4CLStartMeter")).Text.Trim())) ? ((TextBox)gvr.FindControl("txtA4CLStartMeter")).Text.Trim() : "";
dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A3BWPages"] = (!String.IsNullOrEmpty(((TextBox)gvr.FindControl("txtA3BWPage")).Text.Trim())) ? ((TextBox)gvr.FindControl("txtA3BWPage")).Text.Trim() : "";
dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A4BWPages"] = (!String.IsNullOrEmpty(((TextBox)gvr.FindControl("txtA4BWPage")).Text.Trim())) ? ((TextBox)gvr.FindControl("txtA4BWPage")).Text.Trim() : "";
dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A3CLPages"] = (!String.IsNullOrEmpty(((TextBox)gvr.FindControl("txtA3CLPage")).Text.Trim())) ? ((TextBox)gvr.FindControl("txtA3CLPage")).Text.Trim() : "";
dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A4CLPages"] = (!String.IsNullOrEmpty(((TextBox)gvr.FindControl("txtA4CLPage")).Text.Trim())) ? ((TextBox)gvr.FindControl("txtA4CLPage")).Text.Trim() : "";
dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A3BWRate"] = (!String.IsNullOrEmpty(((TextBox)gvr.FindControl("txtA3BWRate")).Text.Trim())) ? ((TextBox)gvr.FindControl("txtA3BWRate")).Text.Trim() : "";
dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A4BWRate"] = (!String.IsNullOrEmpty(((TextBox)gvr.FindControl("txtA4BWRate")).Text.Trim())) ? ((TextBox)gvr.FindControl("txtA4BWRate")).Text.Trim() : "";
dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A3CLRate"] = (!String.IsNullOrEmpty(((TextBox)gvr.FindControl("txtA3CLRate")).Text.Trim())) ? ((TextBox)gvr.FindControl("txtA3CLRate")).Text.Trim() : "";
dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A4CLRate"] = (!String.IsNullOrEmpty(((TextBox)gvr.FindControl("txtA4CLRate")).Text.Trim())) ? ((TextBox)gvr.FindControl("txtA4CLRate")).Text.Trim() : "";
dtContractDetails.AcceptChanges();
//updating last meter reading in Customer Purchase
Business.Service.ServiceBook objServiceBook = new Business.Service.ServiceBook();
Entity.Service.ServiceBook serviceBook = new Entity.Service.ServiceBook();
serviceBook.CustomerPurchaseId = Convert.ToInt32(dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["CustomerPurchaseId"]);
serviceBook.A3BWMeterReading = string.IsNullOrEmpty(dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A3BWStartMeter"].ToString()) ? 0
: Convert.ToInt32(dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A3BWStartMeter"]);
serviceBook.A4BWMeterReading = string.IsNullOrEmpty(dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A4BWStartMeter"].ToString()) ? 0
: Convert.ToInt32(dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A4BWStartMeter"]);
serviceBook.A3CLMeterReading = string.IsNullOrEmpty(dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A3CLStartMeter"].ToString()) ? 0
: Convert.ToInt32(dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A3CLStartMeter"]);
serviceBook.A4CLMeterReading = string.IsNullOrEmpty(dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A4CLStartMeter"].ToString()) ? 0
: Convert.ToInt32(dtContractDetails.Rows[dtContractDetails.Rows.Count - 1]["A4CLStartMeter"]);
int response = 0;
response = objServiceBook.Service_MeterReading_Update(serviceBook);
if (response == 0)
{
Message1.IsSuccess = false;
Message1.Text = "Current meter reading unable to update! Please add new entry or contact admin.";
Message1.Show = true;
ModalPopupExtender2.Show();
return;
}
}
}
if (dtContractDetails.Rows.Count > 0)
{
contract.ContractDetails = dtContractDetails;
}
else
{
Message1.IsSuccess = false;
Message1.Text = "Please select atleast 1 machine.";
Message1.Show = true;
ModalPopupExtender2.Show();
return;
}
}
int retVal = objContract.Save(contract);
if (retVal > 0)
{
LoadContractList();
ClearControlForContract();
Message1.IsSuccess = true;
Message1.Text = "Data saved.";
Message1.Show = true;
ModalPopupExtender2.Show();
}
else
{
Message1.IsSuccess = false;
Message1.Text = "Data not saved";
Message1.Show = true;
ModalPopupExtender2.Show();
}
}
public static int Save(Entity.Service.Contract contract)
{
int rowsAffacted = 0;
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString()))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandText = "usp_Service_Contract_Save";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ContractId", SqlDbType.Int);
cmd.Parameters["@ContractId"].Direction = ParameterDirection.InputOutput;
cmd.Parameters["@ContractId"].Value = contract.ContractId;
cmd.Parameters.AddWithValue("@CustomerId", contract.CustomerId);
cmd.Parameters.AddWithValue("@ContractTypeId", contract.ContractTypeId);
if (contract.ContractStartDate == DateTime.MinValue)
{
cmd.Parameters.AddWithValue("@ContractStartDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@ContractStartDate", contract.ContractStartDate);
}
if (contract.ContractEndDate == DateTime.MinValue)
{
cmd.Parameters.AddWithValue("@ContractEndDate", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@ContractEndDate", contract.ContractEndDate);
}
cmd.Parameters.AddWithValue("@CreatedBy", contract.CreatedBy);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
rowsAffacted = cmd.ExecuteNonQuery();
int contractid = int.Parse(cmd.Parameters["@ContractId"].Value.ToString());
con.Close();
if (rowsAffacted > 0)
{
foreach (DataRow dr in contract.ContractDetails.Rows)
{
using (SqlCommand cmdInner = new SqlCommand())
{
cmdInner.Connection = con;
cmdInner.CommandText = "usp_Service_ContractDetails_Save";
cmdInner.CommandType = CommandType.StoredProcedure;
cmdInner.Parameters.AddWithValue("@ContractId", contractid);
cmdInner.Parameters.AddWithValue("@CustomerPurchaseId", dr["CustomerPurchaseId"].ToString());
if (!String.IsNullOrEmpty(dr["A3BWStartMeter"].ToString()))
{
cmdInner.Parameters.AddWithValue("@A3BWStartMeter", dr["A3BWStartMeter"].ToString());
}
else
{
cmdInner.Parameters.AddWithValue("@A3BWStartMeter", DBNull.Value);
}
if (!String.IsNullOrEmpty(dr["A4BWStartMeter"].ToString()))
{
cmdInner.Parameters.AddWithValue("@A4BWStartMeter", dr["A4BWStartMeter"].ToString());
}
else
{
cmdInner.Parameters.AddWithValue("@A4BWStartMeter", DBNull.Value);
}
if (!String.IsNullOrEmpty(dr["A3CLStartMeter"].ToString()))
{
cmdInner.Parameters.AddWithValue("@A3CLStartMeter", dr["A3CLStartMeter"].ToString());
}
else
{
cmdInner.Parameters.AddWithValue("@A3CLStartMeter", DBNull.Value);
}
if (!String.IsNullOrEmpty(dr["A4CLStartMeter"].ToString()))
{
cmdInner.Parameters.AddWithValue("@A4CLStartMeter", dr["A4CLStartMeter"].ToString());
}
else
{
cmdInner.Parameters.AddWithValue("@A4CLStartMeter", DBNull.Value);
}
if (!String.IsNullOrEmpty(dr["A3BWPages"].ToString()))
{
cmdInner.Parameters.AddWithValue("@A3BWPages", dr["A3BWPages"].ToString());
}
else
{
cmdInner.Parameters.AddWithValue("@A3BWPages", DBNull.Value);
}
if (!String.IsNullOrEmpty(dr["A4BWPages"].ToString()))
{
cmdInner.Parameters.AddWithValue("@A4BWPages", dr["A4BWPages"].ToString());
}
else
{
cmdInner.Parameters.AddWithValue("@A4BWPages", DBNull.Value);
}
if (!String.IsNullOrEmpty(dr["A3CLPages"].ToString()))
{
cmdInner.Parameters.AddWithValue("@A3CLPages", dr["A3CLPages"].ToString());
}
else
{
cmdInner.Parameters.AddWithValue("@A3CLPages", DBNull.Value);
}
if (!String.IsNullOrEmpty(dr["A4CLPages"].ToString()))
{
cmdInner.Parameters.AddWithValue("@A4CLPages", dr["A4CLPages"].ToString());
}
else
{
cmdInner.Parameters.AddWithValue("@A4CLPages", DBNull.Value);
}
if (!String.IsNullOrEmpty(dr["A3BWRate"].ToString()))
{
cmdInner.Parameters.AddWithValue("@A3BWRate", dr["A3BWRate"].ToString());
}
else
{
cmdInner.Parameters.AddWithValue("@A3BWRate", DBNull.Value);
}
if (!String.IsNullOrEmpty(dr["A4BWRate"].ToString()))
{
cmdInner.Parameters.AddWithValue("@A4BWRate", dr["A4BWRate"].ToString());
}
else
{
cmdInner.Parameters.AddWithValue("@A4BWRate", DBNull.Value);
}
if (!String.IsNullOrEmpty(dr["A3CLRate"].ToString()))
{
cmdInner.Parameters.AddWithValue("@A3CLRate", dr["A3CLRate"].ToString());
}
else
{
cmdInner.Parameters.AddWithValue("@A3CLRate", DBNull.Value);
}
if (!String.IsNullOrEmpty(dr["A4CLRate"].ToString()))
{
cmdInner.Parameters.AddWithValue("@A4CLRate", dr["A4CLRate"].ToString());
}
else
{
cmdInner.Parameters.AddWithValue("@A4CLRate", DBNull.Value);
}
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmdInner.ExecuteNonQuery();
}
con.Close();
}
}
}
}
return(rowsAffacted);
}
public DataSet Service_GetAllContractStatus(Entity.Service.Contract contract)
{
return(DataAccess.Service.Contract.Service_GetAllContractStatus(contract));
}
public DataSet Service_ContractExpiredList(Entity.Service.Contract contract)
{
return(DataAccess.Service.Contract.Service_ContractExpiredList(contract));
}
public int Save(Entity.Service.Contract contract)
{
return(DataAccess.Service.Contract.Save(contract));
}
