private void LoadChartForItem()
{
string query = @"select I.ItemCode,I.ItemName +' OrderNo->'+ CONVERT(varchar(10),(D.OrderId)) as 'ItemName',Sum(D.Qty) as 'Counter'
from ItemMaster I
inner join OrderDetail D on D.ItemCode=I.ItemCode
and D.OrderId in(select OrderId from OrderMain M where M.Deliverydate='" + monthCalendar1.SelectionRange.Start.ToString("MM/dd/yyyy") + @"')
group by I.ItemCode,I.ItemName,D.OrderId
order by I.ItemCode,I.ItemName";
DataTable dt = new DataTable();
dt = DBClass.GetTableByQuery(query);
ChartOrder.Update();
//if (dt.Rows.Count > 0)
//{
ChartOrder.Series["Item"].XValueMember = dt.Columns[1].ColumnName;
ChartOrder.Series["Item"].YValueMembers = dt.Columns[2].ColumnName;
ChartOrder.DataSource = dt;
ChartOrder.DataBind();
this.ChartOrder.Titles.Clear();
this.ChartOrder.Titles.Add("Item Chart for Order Booked");
ChartOrder.Series["Item"].ChartType = SeriesChartType.Doughnut;
//chart1.Series["Item"].
ChartOrder.Series["Item"].IsValueShownAsLabel = true;
//}
}
private void DisplayView()
{
string Query = @"select o.Deliverydate,o.CustomerName,o.Place,i.ItemName,d.Qty from OrderMain o
inner join OrderDetail d on o.OrderID=d.OrderId
left join ItemMaster i on d.ItemCode=i.ItemCode
where o.Deliverydate>=GETDATE()-1
order by o.Deliverydate";
DataTable dtview = new DataTable();
dtview = DBClass.GetTableByQuery(Query);
dgvView.DataSource = null;
dgvView.DataSource = dtview;
}
private void LoadChartForMonthlyPayment()
{
DateTime d = monthCalendar1.SelectionRange.Start;
string month = d.Month.ToString();
// string query = @"select Month(o.Deliverydate) as 'Order',sum(d.Qty*d.UnitPrice) as 'TotalAmt',
// sum((d.Qty*d.UnitPrice)-o.Discount-o.Advance_Amt) as 'NetAmt',SUM(p.Paid_Amt) as 'PaidAmt'
// from OrderMain o
// inner join OrderDetail d on o.OrderID=d.OrderId
// left join PaymentDetail p on o.OrderID=p.OrderId
// where Month(o.Deliverydate)='" + month + @"'
// group by Month(o.Deliverydate)";
string query = @"select '" + month + @"' as 'Month',
(select sum(Qty*UnitPrice) from OrderDetail
where OrderId in (select OrderID from OrderMain where month(Deliverydate)='" + month + @"')) as 'TotalAmt',
((select sum(Qty*UnitPrice) from OrderDetail
where OrderId in (select OrderID from OrderMain where month(Deliverydate)='" + month + @"'))
-(select SUM(M.Discount+M.Advance_Amt) from OrderMain M where month(Deliverydate)='" + month + @"')) as 'NetAmt',
(select sum(Paid_Amt) from PaymentDetail where OrderId in (select OrderID from OrderMain where month(Deliverydate)='" + month + @"')) as 'PaidAmt'";
DataTable dt = new DataTable();
dt = DBClass.GetTableByQuery(query);
//if (dt.Rows.Count > 0)
//{
chartMonthlyPayment.DataSource = dt;
chartMonthlyPayment.Series["NetAmt"].XValueMember = "Month";
chartMonthlyPayment.Series["NetAmt"].YValueMembers = "NetAmt";
chartMonthlyPayment.Series["PaidAmt"].XValueMember = "Month";
chartMonthlyPayment.Series["PaidAmt"].YValueMembers = "PaidAmt";
chartMonthlyPayment.DataBind();
this.chartMonthlyPayment.Titles.Clear();
this.chartMonthlyPayment.Titles.Add("Monthly Payment Chart");
// chart2.Series["NetAmt"].ChartType = SeriesChartType.Pie;
chartMonthlyPayment.Series["NetAmt"].IsValueShownAsLabel = true;
//chart2.Series["PaidAmt"].ChartType = SeriesChartType.Bar;
chartMonthlyPayment.Series["PaidAmt"].IsValueShownAsLabel = true;
//}
}
private void DeleteRecord()
{
if (MessageBox.Show("Are you Sure to delete this record ? ", "Delete Record", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
{
DataTable dt = DBClass.GetTableByQuery("Select count(*) from PaymentDetail where OrderId=" + Convert.ToInt16(txtOrderID.Text));
if (dt.Rows.Count > 0)
{
if (dt.Rows[0][0].ToString() != "0")
{
MessageBox.Show("Payment Detail Exist of this order,Can't be delete!");
return;
}
}
DBClass.connection.Open();
_MainDTAdapter.DeleteCommand = new SqlCommand(@"Delete From OrderDetail where OrderID= @OrderID ", DBClass.connection);
_MainDTAdapter.DeleteCommand.Parameters.AddWithValue("@OrderID", txtOrderID.Text);
_MainDTAdapter.DeleteCommand.ExecuteNonQuery();
dsMain.Tables["OrderDetail"].Clear();
_MainDTAdapter.Fill(dsMain.Tables["OrderDetail"]);
_MainAdapter.DeleteCommand = new SqlCommand(@"Delete From OrderMain where OrderID= @OrderID ", DBClass.connection);
_MainAdapter.DeleteCommand.Parameters.AddWithValue("@OrderID", txtOrderID.Text);
_MainAdapter.DeleteCommand.ExecuteNonQuery();
dsMain.Tables["OrderMain"].Clear();
_MainAdapter.Fill(dsMain.Tables["OrderMain"]);
DBClass.connection.Close();
if (dsMain.Tables["OrderMain"].Rows.Count > 0)
{
FillOrders(dsMain.Tables["OrderMain"].Rows.Count - 1);
}
else
{
ClearRecord();
}
DisplayView();
}
}
private void LoadGrid()
{
string query = @"select o.CustomerName,o.OrderID,o.Deliverydate,
(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',
'Paid' as 'Select'
from OrderMain o
where (select sum(Qty*UnitPrice) from OrderDetail where OrderId =o.OrderID) -o.Discount-o.Advance_Amt-(select sum(Paid_Amt) from PaymentDetail where OrderId =o.OrderID)=0
and O.Deliverydate='" + monthCalendar1.SelectionRange.Start.ToString("MM/dd/yyyy") + @"'
union
select o.CustomerName,o.OrderID,o.Deliverydate,
(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',
'Payment Due' as 'Select'
from OrderMain o
where (select sum(Qty*UnitPrice) from OrderDetail where OrderId =o.OrderID) -o.Discount-o.Advance_Amt-(select sum(Paid_Amt) from PaymentDetail where OrderId =o.OrderID)>0
and O.Deliverydate='" + monthCalendar1.SelectionRange.Start.ToString("MM/dd/yyyy") + @"'
union
select o.CustomerName,o.OrderID,o.Deliverydate,
(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',
'InComplete' as 'Select'
from OrderMain o
where o.OrderId NOT IN (select OrderId from PaymentDetail)
and O.Deliverydate='" + monthCalendar1.SelectionRange.Start.ToString("MM/dd/yyyy") + @"'";
DataTable dt = new DataTable();
dt = DBClass.GetTableByQuery(query);
dataGridView1.DataSource = null;
if (dt.Rows.Count > 0)
{
dataGridView1.DataSource = dt;
dataGridView1.ReadOnly = true;
}
}
private void BindMonthCalendar()
{
string query = "select Deliverydate,YEAR(Deliverydate) as 'Year',MONTH(Deliverydate) as 'Month',DAY(Deliverydate) as 'Day' from OrderMain order by Deliverydate";
DataTable dt = new DataTable();
dt = DBClass.GetTableByQuery(query);
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
DateTime DeliveryDate = new DateTime(int.Parse(row["Year"].ToString()), int.Parse(row["Month"].ToString()), int.Parse(row["Day"].ToString()));
monthCalendar1.AddBoldedDate(DeliveryDate);
monthCalendar1.UpdateBoldedDates();
}
}
monthCalendar1.TitleBackColor = System.Drawing.Color.Blue;
monthCalendar1.TrailingForeColor = System.Drawing.Color.Red;
monthCalendar1.TitleForeColor = System.Drawing.Color.Yellow;
}
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;
//}
}
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);
}
