private int GetMatchingWorkDescription(SalesQuotationItem model, IDbConnection connection, IDbTransaction txn)
{
try
{
string query = @"SELECT
WorkDescriptionId
FROM WorkDescription
WHERE FreezerUnitId " + (model.FreezerUnitId == null ? "IS NULL" : "= @FreezerUnitId") + @"
AND BoxId " + (model.BoxId == null ? "IS NULL" : "= @BoxId") + @"
AND VehicleModelId = @VehicleModelId";
return(connection.Query <int>(query, new
{
BoxId = model.BoxId,
FreezerUnitId = model.FreezerUnitId,
VehicleModelId = model.VehicleModelId
}, txn).First());
}
catch (InvalidOperationException)
{
//when there is no matching work description
return(CreateMatchingWorkDescription(connection, txn, model));
}
catch (Exception)
{
throw;
}
}
public int InsertSalesQuotationItem(SalesQuotationItem objSalesQuotationItem, IDbConnection connection, IDbTransaction trn)
{
try
{
objSalesQuotationItem.WorkDescriptionId = GetMatchingWorkDescription(objSalesQuotationItem, connection, trn);
string sql = @"insert into SalesQuotationItem(SalesQuotationId,SlNo,WorkDescriptionId,Remarks,PartNo,Quantity,Rate,Discount,Amount,OrganizationId,RateType) Values (@SalesQuotationId,@SlNo,@WorkDescriptionId,@Remarks,@PartNo,@Quantity,@Rate,@Discount,@Amount,@OrganizationId,@RateType);
SELECT CAST(SCOPE_IDENTITY() as int)";
var id = connection.Query <int>(sql, objSalesQuotationItem, trn).Single();
return(id);
}
catch (Exception)
{
throw;
}
}
public ActionResult Print(int Id)
{
ReportDocument rd = new ReportDocument();
rd.Load(Path.Combine(Server.MapPath("~/Reports"), "SalesQuotation.rpt"));
DataSet ds = new DataSet();
ds.Tables.Add("Head");
ds.Tables.Add("Items");
//-------HEAD
ds.Tables["Head"].Columns.Add("QuotationRefNo");
ds.Tables["Head"].Columns.Add("QuotationDate");
ds.Tables["Head"].Columns.Add("CustomerName");
ds.Tables["Head"].Columns.Add("CustomerAddress");
ds.Tables["Head"].Columns.Add("ContactPerson");
ds.Tables["Head"].Columns.Add("SalesExecutive");
ds.Tables["Head"].Columns.Add("PredictedClosingDate");
ds.Tables["Head"].Columns.Add("QuotationValidToDate");
ds.Tables["Head"].Columns.Add("ExpectedDeliveryDate");
ds.Tables["Head"].Columns.Add("Remarks");
ds.Tables["Head"].Columns.Add("PaymentTerms");
ds.Tables["Head"].Columns.Add("SalesQuotationStatusName");
ds.Tables["Head"].Columns.Add("QuotationStage");
ds.Tables["Head"].Columns.Add("Competitors");
ds.Tables["Head"].Columns.Add("DiscountRemarks");
ds.Tables["Head"].Columns.Add("Discount");
ds.Tables["Head"].Columns.Add("DoorNo");
ds.Tables["Head"].Columns.Add("Street");
ds.Tables["Head"].Columns.Add("State");
ds.Tables["Head"].Columns.Add("CountryName");
ds.Tables["Head"].Columns.Add("Zip");
ds.Tables["Head"].Columns.Add("Fax");
ds.Tables["Head"].Columns.Add("Email");
ds.Tables["Head"].Columns.Add("Phone");
ds.Tables["Head"].Columns.Add("CurrencyName");
ds.Tables["Head"].Columns.Add("OrganizationName");
ds.Tables["Head"].Columns.Add("Designation");
ds.Tables["Head"].Columns.Add("Image1");
ds.Tables["Head"].Columns.Add("UserName");
// ds.Tables["Head"].Columns.Add("EmpDesignation");
ds.Tables["Head"].Columns.Add("Sign");
//-------DT
ds.Tables["Items"].Columns.Add("WorkDescr");
ds.Tables["Items"].Columns.Add("Quantity");
ds.Tables["Items"].Columns.Add("Rate");
ds.Tables["Items"].Columns.Add("UOM");
ds.Tables["Items"].Columns.Add("Discount");
ds.Tables["Items"].Columns.Add("Amount");
ds.Tables["Items"].Columns.Add("TotalAmount");
ds.Tables["Items"].Columns.Add("WorkDescription");
SalesQuotationRepository repo = new SalesQuotationRepository();
var Head = repo.GetSalesQuotationHD(Id, OrganizationId);
DataRow dr = ds.Tables["Head"].NewRow();
dr["QuotationRefNo"] = Head.QuotationRefNo;
dr["QuotationDate"] = Head.QuotationDate.ToString("dd-MMM-yyyy");
dr["CustomerName"] = Head.CustomerName;
dr["CustomerAddress"] = Head.CustomerAddress;
dr["ContactPerson"] = Head.ContactPerson;
dr["SalesExecutive"] = Head.SalesExecutiveName;
dr["PredictedClosingDate"] = Head.PredictedClosingDate;
dr["QuotationValidToDate"] = Head.QuotationValidToDate.ToString("dd-MMM-yyyy");
dr["ExpectedDeliveryDate"] = Head.ExpectedDeliveryDate;
dr["Remarks"] = Head.Remarks;
dr["PaymentTerms"] = Head.PaymentTerms;
dr["SalesQuotationStatusName"] = Head.SalesQuotationStatusName;
dr["QuotationStage"] = Head.QuotationStage;
dr["Competitors"] = Head.Competitors;
dr["DiscountRemarks"] = Head.DiscountRemarks;
dr["Discount"] = Head.Discount;
dr["DoorNo"] = Head.DoorNo;
dr["Street"] = Head.Street;
dr["State"] = Head.State;
dr["CountryName"] = Head.CountryName;
dr["Zip"] = Head.Zip;
dr["Fax"] = Head.Fax;
dr["Email"] = Head.Email;
dr["Phone"] = Head.Phone;
dr["CurrencyName"] = Head.CurrencyName;
dr["OrganizationName"] = Head.OrganizationName;
dr["Designation"] = Head.DesignationName;
dr["Image1"] = Server.MapPath("~/App_images/") + Head.Image1;
dr["UserName"] = Head.EmpNmae;
//dr["EmpDesignation"] = Head.EmpDesignation;
dr["Sign"] = Server.MapPath("~/App_images/") + Head.ApprovedUsersig;
ds.Tables["Head"].Rows.Add(dr);
SalesQuotationRepository repo1 = new SalesQuotationRepository();
var Items = repo1.GetSalesQuotationItemsPrint(Id);
foreach (var item in Items)
{
var pritem = new SalesQuotationItem
{
WorkDescr = item.WorkDescr,
Quantity = item.Quantity,
Rate = item.Rate,
UnitName = item.UnitName,
Discount = item.Discount,
Amount = item.Amount,
WorkDescription = item.WorkDescription,
TotalAmount = item.TotalAmount
};
DataRow dri = ds.Tables["Items"].NewRow();
dri["WorkDescr"] = pritem.WorkDescr;
dri["Quantity"] = pritem.Quantity;
dri["Rate"] = pritem.Rate;
dri["Discount"] = pritem.Discount;
dri["Amount"] = pritem.Amount;
dri["UOM"] = pritem.UnitName;
dri["WorkDescription"] = pritem.WorkDescription;
dri["TotalAmount"] = pritem.TotalAmount;
ds.Tables["Items"].Rows.Add(dri);
}
ds.WriteXml(Path.Combine(Server.MapPath("~/XML"), "SalesQuotation.xml"), XmlWriteMode.WriteSchema);
rd.SetDataSource(ds);
Response.Buffer = false;
Response.ClearContent();
Response.ClearHeaders();
try
{
Stream stream = rd.ExportToStream(CrystalDecisions.Shared.ExportFormatType.WordForWindows);
stream.Seek(0, SeekOrigin.Begin);
return(File(stream, "application/ms-word", String.Format("SalesQuotation{0}.doc", Id.ToString())));
}
catch (Exception ex)
{
throw;
}
}
private int CreateMatchingWorkDescription(IDbConnection connection, IDbTransaction txn, SalesQuotationItem model)
{
try
{
string freezerName = model.FreezerUnitId == null ? String.Empty : new ItemRepository().GetItem(model.FreezerUnitId ?? 0).ItemName,
boxName = model.BoxId == null ? String.Empty : new ItemRepository().GetItem(model.BoxId ?? 0).ItemName,
ref_no = "WD/" + DatabaseCommonRepository.GetInternalIDFromDatabase(connection, txn, typeof(WorkDescription).Name, "0", 1),
query = @"INSERT INTO WorkDescription (WorkDescriptionRefNo, FreezerUnitId, BoxId, WorkDescr,
WorkDescrShortName, isNewInstallation, CreatedDate, OrganizationId, isActive,
isProjectBased, VehicleModelId)
VALUES
(
@WorkDescriptionRefNo, @FreezerUnitId, @BoxId, @WorkDescr,
@WorkDescrShortName, @isNewInstallation, @CreatedDate, @OrganizationId, @isActive,
@isProjectBased, @VehicleModelId
)
SELECT CAST(SCOPE_IDENTITY() AS INT)";
int?id = connection.Query <int>(query,
new
{
WorkDescriptionRefNo = ref_no,
FreezerUnitId = model.FreezerUnitId,
BoxId = model.BoxId,
WorkDescr = freezerName + (freezerName == String.Empty || boxName == String.Empty ? String.Empty : " + ") + boxName,
WorkDescrShortName = freezerName + (freezerName == String.Empty || boxName == String.Empty ? String.Empty : " + ") + boxName,
isNewInstallation = 1,
CreatedDate = System.DateTime.Today,
OrganizationId = model.OrganizationId,
isActive = 1,
isProjectBased = 0,
VehicleModelId = model.VehicleModelId
}, txn).FirstOrDefault();
if (id == null)
{
throw new Exception();
}
else
{
return(id ?? 0);
}
}
catch (Exception)
{
throw;
}
}
