private void ShowReplaceWindow()
{
if (txtOldInvoiceNumber.Text.Trim().Length == 0)
{
clsUtility.ShowInfoMessage("Please Enter Old Invoice Number.", clsUtility.strProjectTitle);
return;
}
DataTable dataTable = ObjCon.ExecuteSelectStatement("SELECT ID FROM " + clsUtility.DBName + ".dbo.SalesInvoiceDetails WITH(NOLOCK) WHERE InvoiceNumber='" + txtOldInvoiceNumber.Text + "'");
if (dataTable.Rows.Count == 0)
{
clsUtility.ShowInfoMessage("No Invoice found for Invoice Number: " + txtOldInvoiceNumber.Text, clsUtility.strProjectTitle);
return;
}
else
{
Sales.Sales_Invoice sales_Invoice = new Sales_Invoice();
sales_Invoice.txtInvoiceNumber.Text = this.txtOldInvoiceNumber.Text;
sales_Invoice.Text = "Replace/Return";
sales_Invoice.lblActiveStatus.ForeColor = Color.Maroon;
sales_Invoice.IsReplaceReturnMode = true;
sales_Invoice.Show();
this.Close();
}
}
public Sales_Invoice getSalesInvoice(SqlConnection con, int InvoiceID)
{
Sales_Invoice obSalesInvoice = new Sales_Invoice();
DataTable dt = new DataTable();
try
{
SqlDataAdapter da = new SqlDataAdapter("Select * from T_Sales_Invoice WHERE InvoiceID = @InvoiceID", con);
da.SelectCommand.Parameters.Add("@InvoiceID", SqlDbType.Int).Value = InvoiceID;
da.Fill(dt);
da.Dispose();
if (dt.Rows.Count == 0)
{
return(null);
}
obSalesInvoice.InvoiceID = dt.Rows[0].Field <int>("InvoiceID");
obSalesInvoice.CurrencyID = Convert.ToInt32(dt.Rows[0].Field <int>("CurrencyID"));
obSalesInvoice.InvoiceNo = dt.Rows[0].Field <string>("InvoiceNo");
obSalesInvoice.InvoiceType = dt.Rows[0].Field <string>("InvoiceType");
obSalesInvoice.ChalanNo = dt.Rows[0].Field <string>("ChalanNo");
obSalesInvoice.CustomerAccount = dt.Rows[0].Field <int>("CustomerAccount");
obSalesInvoice.InvoiceDate = dt.Rows[0].Field <DateTime>("InvoiceDate");
obSalesInvoice.SalesAccount = dt.Rows[0].Field <int>("SalesAccount");
obSalesInvoice.SalesAmount = Convert.ToDouble(dt.Rows[0].Field <object>("SalesAmount"));
obSalesInvoice.DiscountRate = Convert.ToDouble(dt.Rows[0].Field <object>("DiscountRate"));
obSalesInvoice.DiscountAmount = Convert.ToDouble(dt.Rows[0].Field <object>("DiscountAmount"));
obSalesInvoice.TransAmount = Convert.ToDouble(dt.Rows[0].Field <object>("TransAmount"));
if (Convert.ToInt32(dt.Rows[0].Field <object>("TransRefID")) == 0)
{
obSalesInvoice.TransRefID = 0;
}
else
{
obSalesInvoice.TransRefID = Convert.ToInt32(dt.Rows[0].Field <object>("TransRefID"));
}
if (Convert.ToInt32(dt.Rows[0].Field <object>("StockRefID")) == 0)
{
obSalesInvoice.StockRefID = 0;
}
else
{
obSalesInvoice.StockRefID = dt.Rows[0].Field <int>("StockRefID");
}
obSalesInvoice.Remarks = dt.Rows[0].Field <string>("Remarks");
obSalesInvoice.CurrencyID = Convert.ToInt32(dt.Rows[0].Field <int>("CurrencyID"));
obSalesInvoice.Rate = Convert.ToDouble(dt.Rows[0].Field <object>("Rate"));
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(obSalesInvoice);
}
protected void lnkGenerateInvoice_Click(object sender, EventArgs e)
{
LinkButton lnk = (LinkButton)sender;
var s = from a in dc.Sales
join ord in dc.Order_By_Medical_Stores on a.OrderIDByMS equals ord.OrderIDByMS
join ords in dc.Order_by_Medical_Store_Per_Medicines on ord.OrderIDByMS equals ords.OrderIDByMS
join medi in dc.Medicine_In_Stocks on ords.MedicineID equals medi.MedicineID
where a.SaleID == Convert.ToDecimal(lnk.CommandArgument) && ords.Discount > 0
select new
{
medi.MedicineID,
medi.MedicineName,
medi.PackSize,
medi.BatchNo,
medi.ExpiryDate,
medi.Price,
ords.Amount,
ords.Discount,
ords.DiscountAmount,
ords.NetAmount
};
var withoutdc = from a in dc.Sales
join ord in dc.Order_By_Medical_Stores on a.OrderIDByMS equals ord.OrderIDByMS
join ords in dc.Order_by_Medical_Store_Per_Medicines on ord.OrderIDByMS equals ords.OrderIDByMS
join medi in dc.Medicine_In_Stocks on ords.MedicineID equals medi.MedicineID
where a.SaleID == Convert.ToDecimal(lnk.CommandArgument) && ords.Discount == 0
select new
{
medi.MedicineID,
medi.MedicineName,
medi.PackSize,
medi.BatchNo,
medi.ExpiryDate,
medi.Price,
ords.Amount,
// ords.Discount,
// ords.DiscountAmount,
ords.NetAmount
};
var chk = from a in dc.Sales_Invoices
where a.SaleID == Convert.ToDecimal(lnk.CommandArgument)
select a.SaleID;
if (!chk.Any())
{
var sp = (from a in dc.Sales
join ord in dc.Order_By_Medical_Stores on a.OrderIDByMS equals ord.OrderIDByMS
join ords in dc.Order_by_Medical_Store_Per_Medicines on ord.OrderIDByMS equals ords.OrderIDByMS
join medi in dc.Medicine_In_Stocks on ords.MedicineID equals medi.MedicineID
where a.SaleID == Convert.ToDecimal(lnk.CommandArgument)
select ords.NetAmount).Sum();
var sa = (from a in dc.Sales
join ord in dc.Order_By_Medical_Stores on a.OrderIDByMS equals ord.OrderIDByMS
join ords in dc.Order_by_Medical_Store_Per_Medicines on ord.OrderIDByMS equals ords.OrderIDByMS
join medi in dc.Medicine_In_Stocks on ords.MedicineID equals medi.MedicineID
where a.SaleID == Convert.ToDecimal(lnk.CommandArgument)
select ords.Amount).Sum();
Sales_Invoice si = new Sales_Invoice
{
SaleID = Convert.ToDecimal(lnk.CommandArgument),
Price = sp,
Quantity = sa,
sInvoiceDate = DateTime.Now
};
dc.Sales_Invoices.InsertOnSubmit(si);
dc.SubmitChanges();
}
if (s.Any())
{
var medicalstore = from a in dc.Sales
join ord in dc.Order_By_Medical_Stores on a.OrderIDByMS equals ord.OrderIDByMS
join ords in dc.Order_by_Medical_Store_Per_Medicines on ord.OrderIDByMS equals ords.OrderIDByMS
join medical in dc.Medical_Stores on ord.MedicalStoreID equals medical.MedicalStoreID
join medi in dc.Medicine_In_Stocks on ords.MedicineID equals medi.MedicineID
where a.SaleID == Convert.ToDecimal(lnk.CommandArgument)
select new { medical.MedicalStoreName, medical.Phone, medical.Address, medical.Email };
lblStoreName.Text = medicalstore.First().MedicalStoreName;
lblPhone.Text = Convert.ToString(medicalstore.First().Phone);
lblAddress.Text = medicalstore.First().Address;
lblemail.Text = medicalstore.First().Email;
var sale = from a in dc.Sales_Invoices
where a.SaleID == Convert.ToInt32(lnk.CommandArgument)
select new { a.Price, a.sInvoiceDate };
var net = (from a in dc.Sales
join ord in dc.Order_By_Medical_Stores on a.OrderIDByMS equals ord.OrderIDByMS
join ords in dc.Order_by_Medical_Store_Per_Medicines on ord.OrderIDByMS equals ords.OrderIDByMS
where a.SaleID == Convert.ToInt32(lnk.CommandArgument)
select ords.NetAmount).Sum();
lblAmount.Text = Convert.ToString(net);//Convert.ToString( NetTotal); //Convert.ToString(sale.First().Price);
lblDate.Text = Convert.ToString(sale.First().sInvoiceDate);
gvInvoice.DataSource = s;
gvInvoice.DataBind();
ModalPopupExtender1.Show();
}
if (withoutdc.Any())
{
var medicalstore = from a in dc.Sales
join ord in dc.Order_By_Medical_Stores on a.OrderIDByMS equals ord.OrderIDByMS
join ords in dc.Order_by_Medical_Store_Per_Medicines on ord.OrderIDByMS equals ords.OrderIDByMS
join medical in dc.Medical_Stores on ord.MedicalStoreID equals medical.MedicalStoreID
join medi in dc.Medicine_In_Stocks on ords.MedicineID equals medi.MedicineID
where a.SaleID == Convert.ToDecimal(lnk.CommandArgument)
select new { medical.MedicalStoreName, medical.Phone, medical.Address, medical.Email };
Label txtBox = (Label)Panel3.FindControl("lblStoreNamewdc");
txtBox.Text = medicalstore.First().MedicalStoreName;
lblPhonewdc.Text = Convert.ToString(medicalstore.First().Phone);
lblStoreAddresswdc.Text = medicalstore.First().Address;
lblEmailwdc.Text = medicalstore.First().Email;
var sale = from a in dc.Sales_Invoices
where a.SaleID == Convert.ToInt32(lnk.CommandArgument)
select new { a.Price, a.sInvoiceDate };
var net = (from a in dc.Sales
join ord in dc.Order_By_Medical_Stores on a.OrderIDByMS equals ord.OrderIDByMS
join ords in dc.Order_by_Medical_Store_Per_Medicines on ord.OrderIDByMS equals ords.OrderIDByMS
where a.SaleID == Convert.ToInt32(lnk.CommandArgument)
select ords.NetAmount).Sum();
lblAmountwdc.Text = Convert.ToString(net);
lblDatewdc.Text = Convert.ToString(sale.First().sInvoiceDate);
gvWithoutdc.DataSource = withoutdc;
gvWithoutdc.DataBind();
ModalPopupExtender2.Show();
}
}
void CreateInvoice(NAV nav)
{
Sales_Invoice invoice = null;
Sales_InvoiceSalesLines line = null;
foreach (var c in customerlst)
{
Sale currentsale = null;
List <Sale> salesline = saleslst.Where(x => x.Customer == c.Customer).ToList();
invoice = new Sales_Invoice()
{
Document_Type = "Invoice",
Sell_to_Customer_No = c.Customer.Trim(),
Salesperson_Code = getSalesPerson(c.SalesPerson.Trim()),
Posting_Date = dateTimePicker.Value,
Shipment_Date = dateTimePicker.Value,
Due_Date = dateTimePicker.Value,
//Currency_Code = "KES",
//live
Shortcut_Dimension_1_Code = routecode,
//other
Shortcut_Dimension_2_Code = getSalesPerson(c.SalesPerson.Trim()),
Location_Code = cmbLocation.Text
};
nav.AddObject("Sales_Invoice", invoice);
foreach (var saleline in salesline)
{
currentsale = saleline;
line = new Sales_InvoiceSalesLines()
{
Description = saleline.Product,
Quantity = (Decimal)saleline.Quantity
};
nav.AddRelatedObject(invoice, "Sales_InvoiceSalesLines", line);
}
try
{
DataServiceResponse response = nav.SaveChanges();
if (response != null)
{
int uploaded = setUploaded(c.Customer, c.SalesPerson.Trim());
if (uploaded < 0)
{
string ex = string.Format("Did not update any records with customer: {0} and salesperson: {1}", c.Customer, c.SalesPerson);
throw new System.InvalidOperationException(ex);
}
}
}
catch (Exception ex)
{
if (currentsale != null)
{
String errorstr = String.Format("Customer :{0} with Product {1} Quantity {2} .Error: ", c.Customer, currentsale.Product, currentsale.Quantity);
Logger.LogThisLine(errorstr + ex.Message);
}
MessageBox.Show(ex.ToString());
var confirmResult = MessageBox.Show("Do you want to exit the application ??",
"Confirm Exit",
MessageBoxButtons.YesNo);
if (confirmResult == DialogResult.Yes)
{
// If 'Yes', do something here.
Application.Exit();
return;
}
else
{
// If 'No', do something here.
}
}
finally
{
}
}
btnUpload.Enabled = true;
btnBBack.Enabled = true;
label1.Text = "Completed Upload";
System.Media.SystemSounds.Exclamation.Play();
MessageBox.Show("Completed Upload");
}
void MockCreateInvoice(NAV nav)
{
Sales_Invoice invoice = null;
Sales_InvoiceSalesLines line = null;
invoice = new Sales_Invoice()
{
Document_Type = "Invoice",
Sell_to_Customer_No = "KE0001340", //"10000",
Salesperson_Code = "AY016",
Posting_Date = dateTimePicker.Value,
//Currency_Code = "KES",
//live
// Shortcut_Dimension_1_Code = "R737",
//other
Shortcut_Dimension_2_Code = "AY016",
Location_Code = "KAJ295 Y",
};
nav.AddObject("Sales_Invoice", invoice);
line = new Sales_InvoiceSalesLines()
{
Description = "IT00004",
//"1025",
Quantity = (Decimal)2
};
nav.AddRelatedObject(invoice, "Sales_InvoiceSalesLines", line);
try
{
DataServiceResponse response = nav.SaveChanges();
if (response != null)
{
int uploaded = setUploaded("KE0001340", "KE0001340");
if (uploaded < 0)
{
string ex = string.Format("Did not update any records with customer: {0} and salesperson: {1}", "KE0001340", "KE0001340");
throw new System.InvalidOperationException(ex);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
var confirmResult = MessageBox.Show("Do you want to exit the application ??",
"Confirm Exit",
MessageBoxButtons.YesNo);
if (confirmResult == DialogResult.Yes)
{
// If 'Yes', do something here.
Application.Exit();
return;
}
else
{
// If 'No', do something here.
}
}
finally
{
}
btnUpload.Enabled = true;
btnBBack.Enabled = true;
label1.Text = "Completed Upload";
}
public int saveUpdateSales_Invoice(Sales_Invoice obSalesInvoice, SqlConnection con, SqlTransaction trans)
{
SqlCommand com = null;
int LastID = 0;
try
{
com = new SqlCommand("spSaveUpdateSalesInvoice", con, trans);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add("@InvoiceID", SqlDbType.Int).Value = obSalesInvoice.InvoiceID;
com.Parameters.Add("@InvoiceType", SqlDbType.VarChar, 50).Value = obSalesInvoice.InvoiceType;
com.Parameters.Add("@InvoiceNo", SqlDbType.VarChar, 100).Value = obSalesInvoice.InvoiceNo;
com.Parameters.Add("@ChalanNo", SqlDbType.VarChar, 100).Value = obSalesInvoice.ChalanNo;
com.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = obSalesInvoice.InvoiceDate;
com.Parameters.Add("@CustomerAccount", SqlDbType.Int).Value = obSalesInvoice.CustomerAccount;
com.Parameters.Add("@SalesAccount", SqlDbType.Int).Value = obSalesInvoice.SalesAccount;
com.Parameters.Add("@SalesAmount", SqlDbType.Money).Value = obSalesInvoice.SalesAmount;
com.Parameters.Add("@DiscountRate", SqlDbType.Money).Value = obSalesInvoice.DiscountRate;
com.Parameters.Add("@DiscountAmount", SqlDbType.Money).Value = obSalesInvoice.DiscountAmount;
com.Parameters.Add("@TransAmount", SqlDbType.Money).Value = obSalesInvoice.TransAmount;
if (obSalesInvoice.TransRefID <= 0)
{
com.Parameters.Add("@TransRefID", SqlDbType.Int).Value = DBNull.Value;
}
else
{
com.Parameters.Add("@TransRefID", SqlDbType.Int).Value = obSalesInvoice.TransRefID;
}
if (obSalesInvoice.StockRefID <= 0)
{
com.Parameters.Add("@StockRefID", SqlDbType.Int).Value = DBNull.Value;
}
else
{
com.Parameters.Add("@StockRefID", SqlDbType.Int).Value = obSalesInvoice.StockRefID;
}
com.Parameters.Add("@Remarks", SqlDbType.VarChar, 1000).Value = obSalesInvoice.Remarks;
if (obSalesInvoice.CurrencyID <= 0)
{
com.Parameters.Add("@CurrencyID", SqlDbType.Int).Value = DBNull.Value;
}
else
{
com.Parameters.Add("@CurrencyID", SqlDbType.Int).Value = obSalesInvoice.CurrencyID;
}
com.Parameters.Add("@CompanyID", SqlDbType.Int).Value = LogInInfo.CompanyID;
com.Parameters.Add("@UserID", SqlDbType.Int).Value = LogInInfo.UserID;
com.Parameters.Add("@Rate", SqlDbType.Money).Value = obSalesInvoice.Rate;
com.ExecuteNonQuery();
//if (obSalesInvoice.InvoiceID == 0)
//{
// LastID = ConnectionHelper.GetID(con, "InvoiceID", "T_Sales_Invoice");
//}
//else
// LastID = obSalesInvoice.InvoiceID;
if (obSalesInvoice.InvoiceID == 0)
{
SqlCommand cmd = new SqlCommand("SELECT ISNULL(MAX(InvoiceID),0) FROM T_Sales_Invoice", con, trans);
LastID = Convert.ToInt32(cmd.ExecuteScalar());
}
else
{
LastID = obSalesInvoice.InvoiceID;
}
return(LastID);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
