/// <summary>
/// set Sales Main information in Domain Class
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
public PurchaseReturnMainDTO populate(SqlDataReader reader)
{
try
{
PurchaseReturnMainDTO dto = new PurchaseReturnMainDTO();
PurchaseMainDTO oPurchaseMainDTO = new PurchaseMainDTO();
SupplierInfoDTO oSupplierInfoDTO = new SupplierInfoDTO();
oPurchaseMainDTO.PrimaryKey = (Guid)reader["PU_PK"];
oPurchaseMainDTO.GRN_No = (string)reader["GRN_No"];
oPurchaseMainDTO.GRNDate = (DateTime)reader["GRNDate"];
oPurchaseMainDTO.ReferenceNo = (string)reader["ReferenceNo"];
oPurchaseMainDTO.Sp_PK = (Guid)reader["Sp_PK"];
oPurchaseMainDTO.TotalAmount = (decimal)reader["TotalAmount"];
oPurchaseMainDTO.Discount = (decimal)reader["Discount"];
oPurchaseMainDTO.EntryBy = (string)reader["EntryBy"];
oPurchaseMainDTO.EntryDate = (DateTime)reader["EntryDate"];
dto.PurchaseMainDTO = oPurchaseMainDTO;
oSupplierInfoDTO.PrimaryKey = (Guid)reader["Sp_PK"];
oSupplierInfoDTO.SupplierCode = (string)reader["SupplierCode"];
oSupplierInfoDTO.SupplierName = (string)reader["SupplierName"];
oSupplierInfoDTO.Address = (string)reader["Address"];
dto.SupplierInfoDTO = oSupplierInfoDTO;
return(dto);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// This method used for save and update
/// </summary>
/// <param name="obj"> Purchase Info DTO</param>
///
public override void Save(object obj)
{
StringBuilder strBuilderPurchase = new StringBuilder();
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DPOSConnectionString"].ToString());
SqlCommand objCmd = sqlConn.CreateCommand();
PurchaseMainDTO oPurchaseMainDTO = (PurchaseMainDTO)obj;
strBuilderPurchase.Append("Declare @ID int; Declare @ERR int;Insert Into PurchaseMain(PU_PK,GRN_No,GRNDate,ReferenceNo,Sp_PK,TotalAmount,Discount,EntryBy,EntryDate) values ('");
strBuilderPurchase.Append(oPurchaseMainDTO.PrimaryKey);
strBuilderPurchase.Append("','");
strBuilderPurchase.Append(oPurchaseMainDTO.GRN_No);
strBuilderPurchase.Append("','");
strBuilderPurchase.Append(oPurchaseMainDTO.GRNDate);
strBuilderPurchase.Append("','");
strBuilderPurchase.Append(oPurchaseMainDTO.ReferenceNo);
strBuilderPurchase.Append("','");
strBuilderPurchase.Append(oPurchaseMainDTO.Sp_PK);
strBuilderPurchase.Append("',");
strBuilderPurchase.Append(oPurchaseMainDTO.TotalAmount);
strBuilderPurchase.Append(",");
strBuilderPurchase.Append(oPurchaseMainDTO.Discount);
strBuilderPurchase.Append(",'");
strBuilderPurchase.Append(oPurchaseMainDTO.EntryBy);
strBuilderPurchase.Append("',");
strBuilderPurchase.Append("GETDATE()");
strBuilderPurchase.Append(");");
foreach (PurchaseSubDTO oPurchaseSubDTO in oPurchaseMainDTO.PurchaseSubDTO)
{
strBuilderPurchase.Append("Insert Into PurchaseSub(PU_PK,P_PK,ReceivedQuantity,ItemRate,Discount,Notes,EntryBy,EntryDate) values ('");
strBuilderPurchase.Append(oPurchaseSubDTO.PU_PK);
strBuilderPurchase.Append("','");
strBuilderPurchase.Append(oPurchaseSubDTO.P_PK);
strBuilderPurchase.Append("',");
strBuilderPurchase.Append(oPurchaseSubDTO.ReceivedQuantity);
strBuilderPurchase.Append(",");
strBuilderPurchase.Append(oPurchaseSubDTO.ItemRate);
strBuilderPurchase.Append(",");
strBuilderPurchase.Append(oPurchaseSubDTO.Discount);
strBuilderPurchase.Append(",'");
strBuilderPurchase.Append(oPurchaseSubDTO.Notes);
strBuilderPurchase.Append("','");
strBuilderPurchase.Append(oPurchaseSubDTO.EntryBy);
strBuilderPurchase.Append("',");
strBuilderPurchase.Append("GETDATE()");
strBuilderPurchase.Append(");SELECT @ERR=@ERR+@@ERROR;");
}
sqlConn.Open();
objCmd.Connection = sqlConn;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = strBuilderPurchase.Append("SELECT @ID, ISNULL(@ERR,0)").ToString();
using (SqlDataReader rdr = objCmd.ExecuteReader(CommandBehavior.CloseConnection))
{
// Read the returned @ERR
rdr.Read();
// If the error count is not zero throw an exception
if (rdr.GetInt32(1) != 0)
{
throw new ApplicationException("DATA INTEGRITY ERROR ON ORDER INSERT - ROLLBACK ISSUED");
}
}
}
/// <summary>
/// Get All Information in Sales Main And Sales Sub corresponding Invoice No.
/// </summary>
/// <param name="strCode"></param>
/// <returns></returns>
public PurchaseReturnMainDTO GetPurchaseInfoBYGRNNo(string strCode)
{
string sqlSelectMain = "SELECT PurchaseMain.PU_PK, PurchaseMain.GRN_No, PurchaseMain.GRNDate, PurchaseMain.ReferenceNo, PurchaseMain.Sp_PK, PurchaseMain.TotalAmount, PurchaseMain.Discount, PurchaseMain.EntryBy, PurchaseMain.EntryDate, SupplierInfo.SupplierName, SupplierInfo.Sp_PK, SupplierInfo.SupplierCode, SupplierInfo.Address FROM SupplierInfo INNER JOIN PurchaseMain ON SupplierInfo.Sp_PK = PurchaseMain.Sp_PK WHERE PurchaseMain.GRN_No = @GRN_No";
SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DPOSConnectionString"].ToString());
PurchaseReturnMainDTO oPurchaseReturnMainDTO = new PurchaseReturnMainDTO();
PurchaseMainDTO oPurchaseMainDTO = new PurchaseMainDTO();
List <PurchaseReturnSubDTO> olPurchaseReturnSubDTO = new List <PurchaseReturnSubDTO>();
SqlCommand objCmd = sqlConn.CreateCommand();
objCmd.CommandText = sqlSelectMain;
objCmd.Connection = sqlConn;
try
{
objCmd.Parameters.Add(new SqlParameter("@GRN_No", SqlDbType.VarChar, 20));
objCmd.Parameters["@GRN_No"].Value = strCode;
sqlConn.Open();
SqlDataReader thisReader = objCmd.ExecuteReader();
if (thisReader.Read())
{
oPurchaseReturnMainDTO = populate(thisReader);
}
sqlConn.Close();
thisReader.Close();
thisReader.Dispose();
objCmd.Dispose();
objCmd.Cancel();
//// For Sales Sub dto
//try
//{
// string sqlSelectSalesSub = "Select SalSub_Pk,Sal_Pk,P_PK,SalesRate,SalesQuantity,VatAmount,TaxAmount,Discount,TotalAmount,EntryDate,EntryBy From SalesSub where Sal_Pk=@Sal_Pk";
// objCmd.CommandText = sqlSelectSalesSub;
// objCmd.Connection = sqlConn;
// objCmd.Parameters.Add("@Sal_Pk", SqlDbType.UniqueIdentifier, 16);
// objCmd.Parameters["@Sal_Pk"].Value = oSalesMainInfoDTO.PrimaryKey;
// sqlConn.Open();
// thisReader = objCmd.ExecuteReader();
// while (thisReader.Read())
// {
// SalesSubInfoDTO oSalesSubInfoDTO = populatesub(thisReader);
// loSalesSubInfoDTO.Add(oSalesSubInfoDTO);
// }
// oSalesMainInfoDTO.SalesSubInfoDTO = loSalesSubInfoDTO;
//}
//catch (Exception ex)
//{
// throw ex;
//}
}
catch (Exception ex)
{
throw ex;
}
finally
{
objCmd.Dispose();
objCmd.Cancel();
sqlConn.Dispose();
sqlConn.Close();
}
return(oPurchaseReturnMainDTO);
//return oSalesMainInfoDTO;
}
