public string MojoodiBoreshKhorde(MojoodiInput e)
{
con.Open();
var golha = new List <Gol>();
var gols = new SqlCommand("select flower_entry.flower_name + ' / ' + flower_colors.flow_color + ' / ' " +
"+ flower_colortypes.flow_colortype + ' / ' + flower_formats.flow_format AS gol, flower_entry.id " +
"FROM flower_depot.dbo.flower_entry INNER JOIN flower_depot.dbo.flower_colors ON " +
"flower_depot.dbo.flower_entry.flower_color = flower_depot.dbo.flower_colors.flowcolor_id INNER JOIN " +
"flower_depot.dbo.flower_colortypes ON flower_depot.dbo.flower_entry.flower_colortype = " +
"flower_depot.dbo.flower_colortypes.colortype_id INNER JOIN " +
"flower_depot.dbo.flower_formats ON flower_depot.dbo.flower_entry.flower_format = " +
"flower_depot.dbo.flower_formats.flowformat_id " +
"where (flower_entry.customer_name = " + e.Moshtari + " or " + e.Moshtari + " = -1)" +
" and (flower_entry.flower_format = " + e.Format + " or " + e.Format + " = -1) " +
" and (flower_entry.flower_colortype = " + e.Type + " or " + e.Type + " = -1)" +
" and (flower_entry.company_name = " + e.Company + " or " + e.Company + " = -1) " +
" and (flower_entry.enter_date between '" + e.EndDate + "' and '" + e.StartDate + "')" +
" order by flower_entry.flower_name", con);
var r = gols.ExecuteReader();
while (r.Read())
{
golha.Add(new Gol()
{
Id = Convert.ToInt32(r["id"]),
GolName = r["gol"].ToString()
});
}
con.Close();
var list = new List <MojoodiOutput>();
foreach (var i in golha)
{
con.Open();
var j = new MojoodiOutput {
Gol = i.GolName
};
var mojoodi = new SqlCommand("select items.item_name as item, cutted from cutted_and_remain " +
"inner join items on cutted_and_remain.item_name = items.item_id " +
"where flower_id = " + i.Id + " and (item_id in (" + e.Item + ") or '" + e.Item + "' = '-1')", con);
var d = mojoodi.ExecuteReader();
while (d.Read())
{
j.Itemses.Add(new Items()
{
Item = d["item"].ToString(),
Tedad = d["cutted"].ToString()
});
}
list.Add(j);
list.RemoveAll(x => x.Itemses.Count == 0);
con.Close();
}
return(new JavaScriptSerializer().Serialize(list));
}
public string GetGozareshItems(int[] gols, int arrangeType)
{
var listgols = new List <Gol>();
foreach (var gol in gols)
{
con.Open();
var cmd = new SqlCommand("SELECT gol.gol, dbo.flower_entry.id FROM dbo.flower_arrange_items INNER JOIN " +
"dbo.flower_entry ON dbo.flower_arrange_items.flower_id = dbo.flower_entry.id INNER JOIN " +
"dbo.flower_forms_entry ON dbo.flower_entry.id = dbo.flower_forms_entry.flower_id " +
"inner join(select flower_entry.flower_name + ' / ' + flower_colors.flow_color + ' / ' " +
"+ flower_colortypes.flow_colortype + ' / ' + flower_formats.flow_format AS gol, flower_entry.id " +
"FROM flower_depot.dbo.flower_entry INNER JOIN flower_depot.dbo.flower_colors ON " +
"flower_depot.dbo.flower_entry.flower_color = flower_depot.dbo.flower_colors.flowcolor_id INNER JOIN " +
"flower_depot.dbo.flower_colortypes ON flower_depot.dbo.flower_entry.flower_colortype = " +
"flower_depot.dbo.flower_colortypes.colortype_id INNER JOIN " +
"flower_depot.dbo.flower_formats ON flower_depot.dbo.flower_entry.flower_format = " +
"flower_depot.dbo.flower_formats.flowformat_id) as gol on flower_entry.id = gol.id " +
"where flower_entry.id = " + gol + " group by gol.gol, flower_entry.id", con);
var rd = cmd.ExecuteReader();
while (rd.Read())
{
listgols.Add(new Gol()
{
Id = Convert.ToInt32(rd["id"]),
GolName = rd["gol"].ToString()
});
}
con.Close();
}
con.Close();
var listMojoodi = new List <MojoodiOutput>();
foreach (var f in listgols)
{
var item = new MojoodiOutput()
{
Gol = f.GolName
};
CalculateItemsByFlowerId(f.Id);
con.Open();
var cmd = new SqlCommand("if (" + arrangeType + " = 2) begin select items.item_name , falleh + cutted as falleh from cutted_and_remain " +
"inner join items on cutted_and_remain.item_name = items.item_id " +
"where (cutted_and_remain.flower_id = " + f.Id + ") order by items.item_name End " +
"if (" + arrangeType + " = 1) begin select items.item_name , service from cutted_and_remain " +
"inner join items on cutted_and_remain.item_name = items.item_id " +
"where (cutted_and_remain.flower_id = " + f.Id + ") order by items.item_name End " +
"if (" + arrangeType + " = -1) begin select items.item_name , total from cutted_and_remain " +
"inner join items on cutted_and_remain.item_name = items.item_id " +
"where (cutted_and_remain.flower_id = " + f.Id + ") order by items.item_name End ", con);
var d = cmd.ExecuteReader();
while (d.Read())
{
item.Itemses.Add(new Items()
{
Item = d["item_name"].ToString(),
Tedad = arrangeType == 1 ? d["service"].ToString() : arrangeType == 2 ? d["falleh"].ToString() : d["total"].ToString()
});
}
listMojoodi.Add(item);
con.Close();
}
con.Close();
return(new JavaScriptSerializer().Serialize(listMojoodi));
}
public string CustomersItemsCount(MojoodiInput obj)
{
con.Open();
var gols = new List <Gol>();
var gol = new SqlCommand("SELECT gol.gol, dbo.flower_entry.id FROM dbo.flower_arrange_items INNER JOIN " +
"dbo.flower_entry ON dbo.flower_arrange_items.flower_id = dbo.flower_entry.id INNER JOIN " +
"dbo.flower_forms_entry ON dbo.flower_entry.id = dbo.flower_forms_entry.flower_id " +
"inner join(select flower_entry.flower_name + ' / ' + flower_colors.flow_color + ' / ' " +
"+ flower_colortypes.flow_colortype + ' / ' + flower_formats.flow_format AS gol, flower_entry.id " +
"FROM flower_depot.dbo.flower_entry INNER JOIN flower_depot.dbo.flower_colors ON " +
"flower_depot.dbo.flower_entry.flower_color = flower_depot.dbo.flower_colors.flowcolor_id INNER JOIN " +
"flower_depot.dbo.flower_colortypes ON flower_depot.dbo.flower_entry.flower_colortype = " +
"flower_depot.dbo.flower_colortypes.colortype_id INNER JOIN " +
"flower_depot.dbo.flower_formats ON flower_depot.dbo.flower_entry.flower_format = " +
"flower_depot.dbo.flower_formats.flowformat_id) as gol on flower_entry.id = gol.id " +
"WHERE(dbo.flower_arrange_items.item_name IN(" + obj.Item + ") or '" + obj.Item + "' = '-1') " +
"AND(dbo.flower_entry.flower_format = " + obj.Format + " or " + obj.Format + " = -1) " +
"AND(dbo.flower_entry.customer_name = " + obj.Moshtari + " or " + obj.Moshtari + " = -1) " +
"AND(dbo.flower_entry.company_name = " + obj.Company + " or " + obj.Company + " = -1) " +
"and(flower_forms_entry.arrange_type = " + obj.Arrange + " or " + obj.Arrange + " = -1) " +
"GROUP BY dbo.flower_entry.id ,gol.gol", con);
var r = gol.ExecuteReader();
while (r.Read())
{
gols.Add(new Gol()
{
Id = Convert.ToInt32(r["id"]),
GolName = r["gol"].ToString()
});
}
con.Close();
var list = new List <MojoodiOutput>();
foreach (var f in gols)
{
var item = new MojoodiOutput()
{
Gol = f.GolName
};
CalculateItemsByFlowerId(f.Id);
con.Open();
var items = new SqlCommand("if (" + obj.Arrange + " = 2) begin select items.item_name , falleh + cutted as falleh" +
" from cutted_and_remain inner join items on cutted_and_remain.item_name = items.item_id " +
"where (cutted_and_remain.flower_id = " + f.Id + ") and (cutted_and_remain.item_name in" +
" (" + obj.Item + ") or '" + obj.Item + "' = '-1') End " +
"if (" + obj.Arrange + " = 1) begin select items.item_name , service from cutted_and_remain " +
"inner join items on cutted_and_remain.item_name = items.item_id " +
"where (cutted_and_remain.flower_id = " + f.Id + ") and (cutted_and_remain.item_name in" +
" (" + obj.Item + ") or '" + obj.Item + "' = '-1') End " +
"if (" + obj.Arrange + " = -1) begin select items.item_name , total from cutted_and_remain " +
"inner join items on cutted_and_remain.item_name = items.item_id " +
"where (cutted_and_remain.flower_id = " + f.Id + ") and (cutted_and_remain.item_name in" +
" (" + obj.Item + ") or '" + obj.Item + "' = '-1') End ", con);
var d = items.ExecuteReader();
while (d.Read())
{
item.Itemses.Add(new Items()
{
Item = d["item_name"].ToString(),
Tedad = obj.Arrange == 1 ? d["service"].ToString() : obj.Arrange == 2 ? d["falleh"].ToString() : d["total"].ToString()
});
}
list.Add(item);
con.Close();
}
con.Close();
return(new JavaScriptSerializer().Serialize(list));
}
