/// <summary>
/// This function fetch iSmart status from the Database based on PReqNo
/// </summary>
/// <param name="PReqNo"></param>
/// <returns>
/// <paramref name="status"/>
/// </returns>
/// <history>
/// Hari haran 08/05/2012 created
/// </history>
public string getISmartStatus(string PReqNo)
{
try
{
logger.Debug("iSmart_DAL: getISmartStatus() called");
logger.Debug("PReqNo value : " + PReqNo);
databaseLayer dbConStr = new databaseLayer();
string connStr = dbConStr.connectionInfo;
//logger.Debug("Connection string : " + connStr);
SqlConnection conn = new SqlConnection(connStr);
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
logger.Debug("Connection Status opened ");
}
logger.Debug("Connetion to the database established");
string validateQuery = "SELECT dbo.fnpendingwith (@PReqNo)";
SqlCommand validate_cmd = new SqlCommand(validateQuery, conn);
validate_cmd.Parameters.AddWithValue("@PReqNo", PReqNo);
QueryLog.CmdInfo(validate_cmd);
object result = validate_cmd.ExecuteScalar();
logger.Debug("iSmart_DAL: getISmartStatus() Stop");
return(result.ToString());
}
catch (SqlException dbEx)
{
logger.Error("Exception Occured At iSmart_DAL - getISmartStatus : " + dbEx.Message.ToString());
logger.Error("iSmart_DAL:getISmartStatus() returning error");
throw dbEx;
}
catch (Exception Ex)
{
logger.Error("Exception Occured At iSmart_DAL - getISmartStatus : " + Ex.Message.ToString());
logger.Error("iSmart_DAL:getISmartStatus() returning error");
throw Ex;
}
}
/// <summary>
/// This function Updates iSmart Details into the Database
/// </summary>
/// <param name="is_entry_DAL"></param>
/// <returns>
/// <paramref name="result"/>
/// </returns>
/// <history>
/// Hari haran 08/05/2012 created
/// </history>
public iSmart_UpdateOutputEntity updatePurchaseRequest_DAL(iSmart_UpdateInputEntity[] is_entry_DAL)
{
logger.Debug("iSmart_DAL: updatePurchaseRequest_DAL() called");
iSmart_UpdateOutputEntity result = new iSmart_UpdateOutputEntity();
databaseLayer dbConStr = new databaseLayer();
string connStr = dbConStr.connectionInfo;
//logger.Debug("Connection string : " + connStr);
SqlConnection conn = new SqlConnection(connStr);
SqlTransaction Trans = null;
int updateOpflag = 0;
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
logger.Debug("Connection Status opened ");
}
logger.Debug("Connetion to the database established");
int objCount = 0;
SqlCommand update_cmd = new SqlCommand();
update_cmd.Connection = conn;
update_cmd.CommandType = CommandType.StoredProcedure;
Trans = conn.BeginTransaction("UpdateTransaction");
update_cmd.Transaction = Trans;
SqlDataReader dr;
string updateQuery = "ITRMSMobileUpdateData";
update_cmd.Parameters.Add("@PREQNo", SqlDbType.VarChar);
update_cmd.Parameters.Add("@Status", SqlDbType.Int);
update_cmd.Parameters.Add("@EmpID", SqlDbType.VarChar);
update_cmd.Parameters.Add("@PurGrpMember", SqlDbType.VarChar);
update_cmd.Parameters.Add("@ItemTransactionid", SqlDbType.VarChar);
update_cmd.Parameters.Add("@AucNo", SqlDbType.VarChar);
update_cmd.Parameters.Add("@CustomerPONum", SqlDbType.VarChar);
update_cmd.Parameters.Add("@Remarks", SqlDbType.VarChar);
update_cmd.Parameters.Add("@RejectedTo", SqlDbType.VarChar);
update_cmd.Parameters.Add("@AdditionalApprover", SqlDbType.VarChar);
update_cmd.Parameters.Add("@Flag", SqlDbType.VarChar);
update_cmd.Parameters.Add("@Mobilerole", SqlDbType.VarChar);
try
{
foreach (iSmart_UpdateInputEntity isEntry_value in is_entry_DAL)
{
logger.DebugFormat("Object Count in Input array is_entry_DAL : {0}",objCount.ToString());
update_cmd.CommandText = updateQuery;
update_cmd.Parameters["@PREQNo"].Value = isEntry_value.PReqNo;
update_cmd.Parameters["@Status"].Value = isEntry_value.Status;
update_cmd.Parameters["@EmpID"].Value = isEntry_value.EmpID;
update_cmd.Parameters["@PurGrpMember"].Value = isEntry_value.PurGrpMember;
update_cmd.Parameters["@ItemTransactionid"].Value = isEntry_value.ItemTransactionId;
update_cmd.Parameters["@AucNo"].Value = isEntry_value.AucNo;
update_cmd.Parameters["@CustomerPONum"].Value = isEntry_value.CustomerPONum;
update_cmd.Parameters["@Remarks"].Value = isEntry_value.PReqRemarks;
update_cmd.Parameters["@RejectedTo"].Value = isEntry_value.RejectedTo;
update_cmd.Parameters["@AdditionalApprover"].Value = isEntry_value.AssignTo;
update_cmd.Parameters["@Flag"].Value = "Details";
update_cmd.Parameters["@Mobilerole"].Value = isEntry_value.ActionByRole;
QueryLog.CmdInfo(update_cmd);
dr = update_cmd.ExecuteReader();
dr.NextResult();
if (dr.HasRows)
{
dr.Read();
if (dr["StatusFlag"].Equals(0))
{
dr.Close();
updateOpflag = 0;
objCount++;
}
else
{
dr.Close();
updateOpflag = 1;
break;
}
}
else
{
dr.Close();
updateOpflag = 1;
break;
}
}
if (updateOpflag == 0)
{
update_cmd.CommandText = updateQuery;
update_cmd.Parameters["@PREQNo"].Value = is_entry_DAL[0].PReqNo;
update_cmd.Parameters["@Status"].Value = is_entry_DAL[0].Status;
update_cmd.Parameters["@EmpID"].Value = is_entry_DAL[0].EmpID;
update_cmd.Parameters["@PurGrpMember"].Value = is_entry_DAL[0].PurGrpMember;
update_cmd.Parameters["@ItemTransactionid"].Value = is_entry_DAL[0].ItemTransactionId;
update_cmd.Parameters["@AucNo"].Value = is_entry_DAL[0].AucNo;
update_cmd.Parameters["@CustomerPONum"].Value = is_entry_DAL[0].CustomerPONum;
update_cmd.Parameters["@Remarks"].Value = is_entry_DAL[0].PReqRemarks;
update_cmd.Parameters["@RejectedTo"].Value = is_entry_DAL[0].RejectedTo;
update_cmd.Parameters["@AdditionalApprover"].Value = is_entry_DAL[0].AssignTo;
update_cmd.Parameters["@Mobilerole"].Value = is_entry_DAL[0].ActionByRole;
update_cmd.Parameters["@Flag"].Value = "Header";
logger.Debug("Updating the Header Details of iSmart UpdateInput Entity");
QueryLog.CmdInfo(update_cmd);
dr = update_cmd.ExecuteReader();
dr.NextResult();
if (dr.HasRows)
{
dr.Read();
if (dr["StatusFlag"].Equals(0))
{
dr.Close();
Trans.Commit();
result.StatusFlag = 0;
result.Message = iSmart_Constants.Success;
logger.Info("Operation : Update operation executed successfully");
}
else
{
dr.Close();
Trans.Rollback("UpdateTransaction");
logger.Error("Transaction Rollback Executed");
result.StatusFlag = 1;
result.Message = iSmart_Constants.Error;
logger.Error("Operation : Update operation Failed");
}
}
else
{
dr.Close();
Trans.Rollback("UpdateTransaction");
logger.Error("Transaction Rollback Executed");
result.StatusFlag = 1;
result.Message = iSmart_Constants.Error;
logger.Error("Operation : Update operation Failed");
}
}
else
{
Trans.Rollback("UpdateTransaction");
logger.Error("Transaction Rollback Executed");
result.StatusFlag = 1;
result.Message = iSmart_Constants.Error;
logger.Error("Operation : Update Error - Invalid parameter values");
}
logger.Info("iSmart_DAL: updatePurchaseRequest_DAL() Stop");
return result;
}
catch (SqlException dbEx)
{
logger.Error("SQL Exception Occured At iSmart_DAL - updatePurchaseRequest_DAL : ");
logger.Error("Exception Code : " + dbEx.Number.ToString());
logger.Error("Exception Description : " + dbEx.Message.ToString());
logger.Error("iSmart_DAL: updatePurchaseRequest_DAL() returning error");
try
{
logger.Error("Transaction Rollback Executed");
Trans.Rollback("UpdateTransaction");
}
catch (Exception ex2)
{
logger.Error("Tranasction Rollback Failed : " + ex2.Message.ToString());
//throw ex2;
}
throw dbEx;
}
catch (Exception ex)
{
logger.Error("Exception Occured At iSmart_DAL - updatePurchaseRequest_DAL : " + ex.Message.ToString());
logger.Error("iSmart_DAL: updatePurchaseRequest_DAL() returning error");
try
{
logger.Error("Transaction Rollback Executed");
Trans.Rollback("UpdateTransaction");
}
catch (Exception ex2)
{
logger.Error("Transaction Rollback Failed : "+ex2.Message.ToString());
//throw ex2;
}
//throw new myCustomException(32, ex.Message);
throw ex;
}
finally
{
logger.Debug("Connection Status Closed ");
conn.Dispose();
}
}
/// <summary>
/// This function fetches the iSmart Details from the database based on PReqNo
/// </summary>
/// <param name="PReqNo"></param>
/// <returns>
/// <paramref name="iSmartEntity"/>
/// </returns>
/// <history>
/// Hari haran 07/05/2012 created
/// </history>
public iSmartEntity getPurchaseRequestDetails_DAL(string PReqNo)
{
logger.Debug("iSmart_DAL:getPurchaseRequestDetails_DAL() called");
logger.Debug("PReqNo value : " + PReqNo);
databaseLayer dbConStr = new databaseLayer();
string connStr = dbConStr.connectionInfo;
//logger.Debug("Connection string : " + connStr);
SqlConnection conn = new SqlConnection(connStr);
// SqlTransaction tsTrans = null;
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
logger.Debug("Connection Status opened ");
}
logger.Debug("Connetion to the database established");
try
{
//tsTrans = conn.BeginTransaction();
string Query = "ITRMSMobileGetData";
SqlCommand cmd_iSmartDetails = new SqlCommand();
cmd_iSmartDetails.Connection = conn;
cmd_iSmartDetails.CommandText = Query;
cmd_iSmartDetails.CommandType = CommandType.StoredProcedure;
cmd_iSmartDetails.Parameters.AddWithValue("@PReqNo", PReqNo);
// cmd_iSmartDetails.Transaction = tsTrans;
logger.Debug("cmd_iSmartDetails Query parameters initialised");
QueryLog.CmdInfo(cmd_iSmartDetails);
SqlDataAdapter da_iSmartDetails = new SqlDataAdapter();
da_iSmartDetails.SelectCommand = cmd_iSmartDetails;
DataSet dSet_iSmartDetails = new DataSet();
da_iSmartDetails.Fill(dSet_iSmartDetails);
logger.Debug("cmd_iSmartDetails excuted by SqlDataAdapter()");
int header_count = dSet_iSmartDetails.Tables[iSmart_Constants.headerTbNo].Rows.Count;
int itemDetails_count = dSet_iSmartDetails.Tables[iSmart_Constants.itemTbNo].Rows.Count;
int historyDetails_count = dSet_iSmartDetails.Tables[iSmart_Constants.historyTbNo].Rows.Count;
int purchaseGp_count = dSet_iSmartDetails.Tables[iSmart_Constants.purchaseGpTbNo].Rows.Count;
int sendForClr_count = dSet_iSmartDetails.Tables[iSmart_Constants.sendForClrTbNo].Rows.Count;
logger.Info("Records returned from Database");
logger.InfoFormat("Row Count : Header table {0}, ItemDetails table {1}, HisatoryDetails table {2}, PurchaseGrp table {3} ,SendForClarifiaction table {4}", header_count.ToString(), itemDetails_count.ToString(), historyDetails_count.ToString(), purchaseGp_count.ToString(), sendForClr_count.ToString());
iSmartEntity is_Details = new iSmartEntity(itemDetails_count, purchaseGp_count, historyDetails_count, sendForClr_count);
if (header_count == 0 || itemDetails_count == 0 || purchaseGp_count == 0 || sendForClr_count == 0)
{
string ErrMessage = string.Empty;
if (header_count == 0)
{
ErrMessage = " IS_headerDetails Row Count : 0" + ",";
}
if (itemDetails_count == 0)
{
ErrMessage += " IS_childDetails - Item Details Row count : 0" + ",";
}
if (purchaseGp_count == 0)
{
ErrMessage += " IS_childDetails - purchase Group Row count : 0" + ",";
}
if (sendForClr_count == 0)
{
ErrMessage += " IS_childDetails - sendForClr Row count : 0" + ",";
}
string mailBody = string.Format(iSmart_Constants.mail_BodyFormat, System.DateTime.Now.ToString("F"), PReqNo, "getPurchaseRequestDetails", ErrMessage);
webServiceExHandling.Send_Email(iSmart_Constants.Email_Dic, mailBody);
}
if (header_count > 0)
{
DataRow header_dr = dSet_iSmartDetails.Tables[iSmart_Constants.headerTbNo].Rows[0];
is_Details.IS_headerDetails.PReqNo = PReqNo;
is_Details.IS_headerDetails.Capex_Revenue = header_dr["CapexRevenue"].ToString();
is_Details.IS_headerDetails.BorneBy = header_dr["borneby"].ToString();
is_Details.IS_headerDetails.ProjectID = header_dr["MirrorProjectid"].ToString();
is_Details.IS_headerDetails.TotalUSDValue = Convert.ToDouble(header_dr["TotalUSDcost"]);
is_Details.IS_headerDetails.PReqRemarks = header_dr["PREQReqRemarks"].ToString();
is_Details.IS_headerDetails.RequestedBy = header_dr["Requestedby"].ToString();
is_Details.IS_headerDetails.ActionByEmpNum = getISmartStatus(PReqNo);
is_Details.IS_headerDetails.ActionByRole = header_dr["ActionByRole"].ToString();
if (!Convert.IsDBNull(header_dr["Requesteddate"]))
{
is_Details.IS_headerDetails.RequestedDate = Convert.ToDateTime(header_dr["Requesteddate"]).ToString("U", CultureInfo.CreateSpecificCulture("en-US"));
}
if (!Convert.IsDBNull(header_dr["EndDate"]))
{
is_Details.IS_headerDetails.ProjectEndDate = Convert.ToDateTime(header_dr["EndDate"]).ToString("U", CultureInfo.CreateSpecificCulture("en-US"));
}
is_Details.IS_headerDetails.CompanyCode = header_dr["Companycode"].ToString();
is_Details.IS_headerDetails.BU = header_dr["BUdesc"].ToString();
is_Details.IS_headerDetails.AcceptanceCriteria = header_dr["acceptancecriteria"].ToString();
}
else
{
//Error handling : For Invaild PReqNo
is_Details.IS_headerDetails.ErrorCode = 31;
is_Details.IS_headerDetails.ErrorMessage = "Invalid PReqNo";
logger.ErrorFormat("No rows returned from the Header table for the PreqNo : {0}", PReqNo.ToString());
logger.Error("ErrorCode = " + is_Details.IS_headerDetails.ErrorCode.ToString());
logger.Error("ErrorMessage = " + is_Details.IS_headerDetails.ErrorMessage);
logger.Error("iSmart_DAL:getPurchaseRequestDetails_DAL() returning error");
return is_Details;
}
int objCounter_item = 0;
foreach (DataRow item_dr in dSet_iSmartDetails.Tables[iSmart_Constants.itemTbNo].Rows)
{
iSmartEntity.itemDetails is_Item = new iSmartEntity.itemDetails();
is_Item.ItemTransactionId = Convert.ToInt32(item_dr["ItemLineNo"]);
is_Item.ItemDescription = item_dr["ItemDescription"].ToString();
is_Item.ItemSpecification = item_dr["itemspecification"].ToString();
is_Item.Quantity = Convert.ToInt32(item_dr["requiredquantity"]);
is_Item.UOM = item_dr["UOMDesc"].ToString();
is_Item.UnitRate = Convert.ToDouble(item_dr["unitrate"]);
is_Item.TotalCost = Convert.ToDouble(item_dr["Cost"]);
is_Item.TotalCost_USD = Convert.ToDouble(item_dr["USDcost"]);
if (!Convert.IsDBNull(item_dr["Reqbydate"]))
{
is_Item.RequiredFromDate = Convert.ToDateTime(item_dr["Reqbydate"]);
}
if (!Convert.IsDBNull(item_dr["Reqtilldate"]))
{
is_Item.RequiredTillDate = Convert.ToDateTime(item_dr["Reqtilldate"]);
}
is_Item.Plant = item_dr["PlantDesc"].ToString();
is_Item.CostCenter = item_dr["CostCenterDesc"].ToString();
is_Item.GLAccount = item_dr["GLAccountNoDesc"].ToString();
is_Details.IS_child_itemDetails[objCounter_item++] = is_Item;
}
logger.Info("Item Details Row Count = " + objCounter_item.ToString());
int objCounter_history = 0;
foreach (DataRow history_dr in dSet_iSmartDetails.Tables[iSmart_Constants.historyTbNo].Rows)
{
iSmartEntity.historyDetails is_history = new iSmartEntity.historyDetails();
is_history.EmpName = history_dr["updatedby"].ToString();
is_history.Role = history_dr["updatedByRole"].ToString();
if (!Convert.IsDBNull(history_dr["UpdatedDate"]))
{
is_history.UpdatedDate = Convert.ToDateTime(history_dr["UpdatedDate"]).ToString("U", CultureInfo.CreateSpecificCulture("en-US"));
}
is_history.ActionPerformed = history_dr["ActionPerformedbyUser"].ToString();
is_history.Remarks = history_dr["remarks"].ToString();
is_Details.IS_child_historyDetails[objCounter_history++] = is_history;
}
logger.Info("History Details Row Count = " + objCounter_history.ToString());
int objCounter_purcahseGp = 0;
foreach (DataRow puchaseGrp_dr in dSet_iSmartDetails.Tables[iSmart_Constants.purchaseGpTbNo].Rows)
{
iSmartEntity.purchaseGroupMembers is_purchaseGrp = new iSmartEntity.purchaseGroupMembers();
is_purchaseGrp.EmpID = Convert.ToDouble(puchaseGrp_dr["userid"]);
is_purchaseGrp.EmpName = puchaseGrp_dr["employeename"].ToString();
is_Details.IS_child_purchaseGroupMembers[objCounter_purcahseGp++] = is_purchaseGrp;
}
logger.Info("PurchaseGrp Memember Details Row Count = " + objCounter_purcahseGp.ToString());
int objCounter_SendForClr = 0;
foreach (DataRow sendForClr in dSet_iSmartDetails.Tables[iSmart_Constants.sendForClrTbNo].Rows)
{
iSmartEntity.SendForClarification is_sendForClr = new iSmartEntity.SendForClarification();
is_sendForClr.RoleCode = sendForClr["rolecode"].ToString();
is_sendForClr.RoleDescription = sendForClr["RoleDescription"].ToString();
is_Details.IS_child_sendForClrDetails[objCounter_SendForClr++] = is_sendForClr;
}
logger.Info("Send For Clarification Details Row Count = " + objCounter_SendForClr.ToString());
logger.Info("In Success case : ErrorCode = " + is_Details.IS_headerDetails.ErrorCode.ToString());
logger.Info("In Success case : ErrorMessage = " + is_Details.IS_headerDetails.ErrorMessage);
//tsTrans.Commit();
return is_Details;
}
catch (SqlException dbEx)
{
logger.Error("Exception Occured At iSmart_DAL - getPurchaseRequestDetails_DAL");
logger.Error("Exception Code : " + dbEx.Number.ToString());
logger.Error("Exception Description : " + dbEx.Message.ToString());
logger.Error("iSmart_DAL:getPurchaseRequestDetails_DAL() returning error");
//tsTrans.Rollback();
throw dbEx;
}
catch (Exception ex)
{
logger.Error("Exception Occured At iSmart_DAL - getPurchaseRequestDetails_DAL : " + ex.Message.ToString());
logger.Error("iSmart_DAL:getPurchaseRequestDetails_DAL() returning error");
// tsTrans.Rollback();
//throw new myCustomException(31, ex.Message);
throw ex;
}
finally
{
logger.Debug("Connection Status Closed ");
conn.Dispose();
}
}