private void NewRecord()
{
ClearRecord();
int maxid = DBClass.GetIdByQuery("SELECT IDENT_CURRENT('OrderMain')+1");
txtOrderID.Text = maxid.ToString();
txtOrderDate.Text = System.DateTime.Now.ToShortDateString();
if (dsForCombo.Tables["Customer"].Rows.Count > 0)
{
//txtCustmorid.Text = dsForCombo.Tables["Customer"].Rows[0]["CustomerID"].ToString();
//cmbcustomername.SelectedValue = txtCustmorid.Text;
}
dsMain.Tables["OrderDetail"].DefaultView.RowFilter = "";
dsMain.Tables["OrderDetail"].DefaultView.RowFilter = " Orderid=" + int.Parse(txtOrderID.Text);
dgvOrderDetail.DataSource = dsMain.Tables["OrderDetail"].DefaultView;
Enable_Disable_Controls(false);
cmbcustomername.Focus();
}
private void LoadChartForPayment()
{
string query = @"select o.OrderId,
(select sum(Qty*UnitPrice) from OrderDetail where OrderId =o.OrderID) as 'TotalAmt',
o.Discount,o.Advance_Amt,
(select sum(Qty*UnitPrice) from OrderDetail where OrderId =o.OrderID) -o.Discount-o.Advance_Amt as 'NetAmt',
(select sum(Paid_Amt) from PaymentDetail where OrderId =o.OrderID) as 'PaidAmount'
from OrderMain o
where o.Deliverydate='" + monthCalendar1.SelectionRange.Start.ToString("MM/dd/yyyy") + @"'";
DataTable dt = new DataTable();
dt = DBClass.GetTableByQuery(query);
//if (dt.Rows.Count > 0)
//{
chartPayment.DataSource = dt;
chartPayment.Series["NetAmt"].XValueMember = "OrderId";
chartPayment.Series["NetAmt"].YValueMembers = "NetAmt";
chartPayment.Series["PaidAmt"].XValueMember = "OrderId";
chartPayment.Series["PaidAmt"].YValueMembers = "PaidAmount";
chartPayment.DataBind();
this.chartPayment.Titles.Clear();
this.chartPayment.Titles.Add("Payment Chart for Order Booked");
// chart2.Series["NetAmt"].ChartType = SeriesChartType.Pie;
chartPayment.Series["NetAmt"].IsValueShownAsLabel = true;
//chart2.Series["PaidAmt"].ChartType = SeriesChartType.Bar;
chartPayment.Series["PaidAmt"].IsValueShownAsLabel = true;
//}
}
private void Filter_OrderDetail()
{
if (dsMain.Tables.Contains("Order"))
{
dsMain.Tables.Remove("Order");
_OrderAdapter = DBClass.GetAdapterByQuery(Query);
DataTable dt = new DataTable("Order");
_OrderAdapter.Fill(dt);
dsMain.Tables.Add(dt);
dsMain.Tables["Order"].DefaultView.RowFilter = "";
if (cmbCustomerName.Text != "")
{
dsMain.Tables["Order"].DefaultView.RowFilter = " CustomerID=" + cmbCustomerName.SelectedValue.ToString();
}
DataTable DT = new DataTable();
DT = dsMain.Tables["Order"].DefaultView.ToTable();
if (cmbSelect.SelectedItem != null && cmbSelect.SelectedItem.ToString() != "All")
{
if (cmbCustomerName.Text != "")
{
dsMain.Tables["Order"].DefaultView.RowFilter = " CustomerID=" + cmbCustomerName.SelectedValue.ToString() + " AND Select='" + cmbSelect.SelectedItem.ToString() + "'";
}
}
else
{
if (cmbCustomerName.Text != "")
{
dsMain.Tables["Order"].DefaultView.RowFilter = " CustomerID=" + cmbCustomerName.SelectedValue.ToString();
}
}
dgvOrder.DataSource = dsMain.Tables["Order"].DefaultView;
/////// Calculate Total///////////////
//dt.DefaultView.RowFilter = " CustomerID=" + cmbCustomerName.SelectedValue.ToString();
decimal TotalNetAmt = 0;
if (DT.Rows.Count > 0)
{
TotalNetAmt = DT.AsEnumerable().Sum(r => r.Field <decimal?>("NetAmt") ?? 0);
lblTotalNetAmount.Text = TotalNetAmt.ToString();
}
else
{
lblTotalNetAmount.Text = "0.0";
TotalNetAmt = 0;
}
if ((cmbSelect.SelectedItem != null) && (cmbSelect.SelectedItem.ToString() == "All"))
{
DT = new DataTable();
dsMain.Tables["PaymentDetail"].DefaultView.RowFilter = "";
if (cmbCustomerName.Text != "")
{
dsMain.Tables["PaymentDetail"].DefaultView.RowFilter = " CustomerId=" + int.Parse(cmbCustomerName.SelectedValue.ToString());
}
DT = dsMain.Tables["PaymentDetail"].DefaultView.ToTable();
decimal TotalPaidAmt = 0;
if (DT.Rows.Count > 0)
{
TotalPaidAmt = DT.AsEnumerable().Sum(r => r.Field <decimal?>("Paid_Amt") ?? 0);
lblTotalPaidAmt.Text = TotalPaidAmt.ToString();
}
else
{
lblTotalPaidAmt.Text = "0.0";
TotalPaidAmt = 0;
}
decimal PaymentDueAmt = TotalNetAmt - TotalPaidAmt;
lblPaymentDueAmt.Text = PaymentDueAmt.ToString();
if (PaymentDueAmt == 0)
{
lblPaymentDueAmt.ForeColor = System.Drawing.Color.Green;
}
else if (TotalPaidAmt == 0)
{
lblPaymentDueAmt.ForeColor = System.Drawing.Color.Red;
}
else
{
// lblPaymentDueAmt.ForeColor = System.Drawing.Color.Maroon;
}
}
}
}
public DataTable ConstructSqlTable(string SelectColumns, string TableName, string JoinTables = "",
string ItemFieldName = "", string ItemFieldValue = "", string CustomerFieldName = "", string CustomerFieldValue = "",
string PlaceFieldName = "", string PlaceFieldValue = "",
string OrderIdFieldName = "", string OrderIdFromFieldValue = "", string OrderIdToFieldValue = "",
string DeliveryDateFieldName = "", string DeliveryFromDateFieldValue = "", string DeliveryToDateFieldValue = "",
string OrderDateFieldName = "", string OrderFromDateFieldValue = "", string OrderToDateFieldValue = "",
string PaymentdateFieldName = "", string PaymentFromdateFieldValue = "", string PaymentTodateFieldValue = "",
string GroupBy = "", string OrderBy = "")
{
string strSql;
strSql = @"select " + SelectColumns + " From " + TableName;
if (JoinTables != "")
{
strSql = strSql + " " + JoinTables;
}
strSql = strSql + " Where 1=1";
string strCriteria = "";
if (ItemFieldName != "" && ItemFieldValue != "")
{
strCriteria = strCriteria + " And " + ItemFieldName + "='" + ItemFieldValue + "'";
}
if (CustomerFieldName != "" && CustomerFieldValue != "")
{
strCriteria = strCriteria + " And " + CustomerFieldName + "='" + CustomerFieldValue + "'";
}
if (PlaceFieldName != "" && PlaceFieldValue != "")
{
strCriteria = strCriteria + " And " + PlaceFieldName + "='" + PlaceFieldValue + "'";
}
if (OrderIdFieldName != "" && OrderIdFromFieldValue != "")
{
strCriteria = strCriteria + " And " + OrderIdFieldName + ">='" + OrderIdFromFieldValue + "'";
}
if (OrderIdFieldName != "" && OrderIdToFieldValue != "")
{
strCriteria = strCriteria + " And " + OrderIdFieldName + "<='" + OrderIdToFieldValue + "'";
}
if (DeliveryDateFieldName != "" && DeliveryFromDateFieldValue != "")
{
strCriteria = strCriteria + " And " + DeliveryDateFieldName + ">='" + DeliveryFromDateFieldValue + "'";
}
if (DeliveryDateFieldName != "" && DeliveryToDateFieldValue != "")
{
strCriteria = strCriteria + " And " + DeliveryDateFieldName + "<='" + DeliveryToDateFieldValue + "'";
}
if (OrderDateFieldName != "" && OrderFromDateFieldValue != "")
{
strCriteria = strCriteria + " And " + OrderDateFieldName + ">='" + OrderFromDateFieldValue + "'";
}
if (OrderDateFieldName != "" && OrderToDateFieldValue != "")
{
strCriteria = strCriteria + " And " + OrderDateFieldName + "<='" + OrderToDateFieldValue + "'";
}
if (PaymentdateFieldName != "" && PaymentFromdateFieldValue != "")
{
strCriteria = strCriteria + " And " + PaymentdateFieldName + ">='" + PaymentFromdateFieldValue + "'";
}
if (PaymentdateFieldName != "" && PaymentTodateFieldValue != "")
{
strCriteria = strCriteria + " And " + PaymentdateFieldName + "<='" + PaymentTodateFieldValue + "'";
}
strSql = strSql + strCriteria;
if (GroupBy != "")
{
strSql = strSql + " " + GroupBy;
}
if (OrderBy != "")
{
strSql = strSql + " " + OrderBy;
}
dt = DBClass.GetTableByQuery(strSql);
return(dt);
}
private void SaveRedord()
{
DataRow[] row = dsMain.Tables["OrderMain"].Select("Orderid=" + int.Parse(txtOrderID.Text));
if (row.Length == 0)
{
_MainAdapter.InsertCommand = new SqlCommand(@"insert into OrderMain(OrderDate,Deliverydate,CustomerID,CustomerName,Contactno,Place,Discount,Advance_Amt)
output inserted.Orderid
Values(@OrderDate,@Deliverydate,@CustomerID,@CustomerName,@Contactno,@Place,@Discount,@Advance_Amt)", DBClass.connection);
DBClass.connection.Open();
_MainAdapter.InsertCommand.Parameters.AddWithValue("@OrderDate", txtOrderDate.Text);
_MainAdapter.InsertCommand.Parameters.AddWithValue("@Deliverydate", txtDeliveryDate.Text);
_MainAdapter.InsertCommand.Parameters.AddWithValue("@CustomerID", int.Parse(txtCustmorid.Text));
_MainAdapter.InsertCommand.Parameters.AddWithValue("@CustomerName", cmbcustomername.Text);
_MainAdapter.InsertCommand.Parameters.AddWithValue("@Contactno", txtContectNo.Text);
_MainAdapter.InsertCommand.Parameters.AddWithValue("@Place", txtAdders.Text);
string DiscAmt = (txtDiscount.Text == "") ? "0" : txtDiscount.Text;
_MainAdapter.InsertCommand.Parameters.AddWithValue("@Discount", Convert.ToDecimal(DiscAmt));
string AdvAmt = (txtDiscount.Text == "") ? "0" : txtAdvance.Text;
_MainAdapter.InsertCommand.Parameters.AddWithValue("@Advance_Amt", decimal.Parse(AdvAmt));
int id = (int)_MainAdapter.InsertCommand.ExecuteScalar();
txtOrderID.Text = id.ToString();
DBClass.connection.Close();
}
else
{
DBClass.connection.Open();
_MainAdapter.UpdateCommand = new SqlCommand(@"update OrderMain set OrderDate=@OrderDate,Deliverydate=@Deliverydate,CustomerID=@CustomerID,CustomerName=@CustomerName,
Contactno=@Contactno,Place=@Place,Discount=@Discount,Advance_Amt=@Advance_Amt
where OrderID= @OrderID ", DBClass.connection);
_MainAdapter.UpdateCommand.Parameters.AddWithValue("@OrderID", txtOrderID.Text);
_MainAdapter.UpdateCommand.Parameters.AddWithValue("@OrderDate", txtOrderDate.Text);
_MainAdapter.UpdateCommand.Parameters.AddWithValue("@Deliverydate", txtDeliveryDate.Text);
_MainAdapter.UpdateCommand.Parameters.AddWithValue("@CustomerID", int.Parse(txtCustmorid.Text));
_MainAdapter.UpdateCommand.Parameters.AddWithValue("@CustomerName", cmbcustomername.Text);
_MainAdapter.UpdateCommand.Parameters.AddWithValue("@Contactno", txtContectNo.Text);
_MainAdapter.UpdateCommand.Parameters.AddWithValue("@Place", txtAdders.Text);
string DiscAmt = (txtDiscount.Text == "") ? "0" : txtDiscount.Text;
_MainAdapter.UpdateCommand.Parameters.AddWithValue("@Discount", Convert.ToDecimal(DiscAmt));
string AdvAmt = (txtDiscount.Text == "") ? "0" : txtAdvance.Text;
_MainAdapter.UpdateCommand.Parameters.AddWithValue("@Advance_Amt", decimal.Parse(AdvAmt));
_MainAdapter.UpdateCommand.ExecuteNonQuery();
DBClass.connection.Close();
}
dsMain.Tables["OrderMain"].Clear();
_MainAdapter.Fill(dsMain.Tables["OrderMain"]);
//////////////// Save Order Detail ///////////
//foreach (DataRow row1 in dsMain.Tables["OrderDetail"].Rows)
//{
// row1["OrderId"] = txtOrderID.Text;
//}
//dsMain.Tables["OrderDetail"].Columns["OrderId"].DefaultValue = txtOrderID.Text;
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(_MainDTAdapter);
_MainDTAdapter.UpdateCommand = cmdBuilder.GetUpdateCommand();
_MainDTAdapter.InsertCommand = cmdBuilder.GetInsertCommand();
_MainDTAdapter.Update(dsMain.Tables["OrderDetail"]);
dsMain.Tables["OrderDetail"].Clear();
_MainDTAdapter = DBClass.GetAdaptor("OrderDetail");
_MainDTAdapter.Fill(dsMain.Tables["OrderDetail"]);
dsForCombo.Tables["Customer"].Clear();
_CustomerAdapter = DBClass.GetAdapterByQuery("Select distinct CustomerID,CustomerName from OrderMain");
_CustomerAdapter.Fill(dsForCombo.Tables["Customer"]);
FillCustomerDetail(txtCustmorid.Text);
FillOrders(dsMain.Tables["OrderMain"].Rows.Count - 1);
Enable_Disable_Controls(true);
}